查看新的类方法代码时

import datetime

from openpyxl import load_workbook

from app import app, db
from app.config.mail import MailConfig
from app.utils.row_2_dictionary import rows_2_list, row_2_dictionary
from app.utils.x_logger import logger
from app.utils.xdo import XDO


# 门店销售日报
class OrgSalesDaily:
    sales_date = ""

    # 获取sql语句，By sql文件
    @staticmethod
    def _get_sql(name, params=None):
        return XDO.get_sql_by_file(‘org_sales_daily/‘ + name, params)

    # 获取list, By sql文件
    def _read_list_by_sql(self, name, params4sql=None, params4exec=None):
        sql = self._get_sql(name, params4sql)
        rows = db.session.execute(sql, params=params4exec)
        return rows_2_list(rows)

    # 保存列表数据
    def _save_daily_field_base_list(self, obj_list, field_name):
        for obj_dict in obj_list:
            sql = XDO().get_update_sql(‘szq.org_sales_daily‘, {
                field_name: obj_dict[field_name],
            }, ‘org_id = "‘ + str(obj_dict[‘org_id‘]) + ‘" AND sales_date = "‘ + self.sales_date + ‘"‘)
            db.session.execute(sql)

    # 获取：生成门店销售日报
    def make_org_sales_daily(self, sales_date):
        self.sales_date = sales_date

        # 删除：清除此日的数据
        sql = ‘DELETE FROM szq.org_sales_daily WHERE sales_date = "‘ + sales_date + ‘"‘
        db.session.execute(sql)

        # 获取：获取门店此日销售数据并写入
        obj_list = self._read_list_by_sql(‘1‘, {"sales_date": sales_date})
        if obj_list:
            sql = XDO().get_inserts_sql(‘szq.org_sales_daily‘, obj_list)
            db.session.execute(sql)

        # 获取：充值金额销售数据
        obj_list = self._read_list_by_sql(‘2‘, {"sales_date": sales_date})

        for obj_dict in obj_list:
            # 获取：对应组织此日销售数据
            sql = ‘SELECT * FROM szq.org_sales_daily WHERE org_id = "‘ + str(
                obj_dict[‘org_id‘]) + ‘" AND sales_date = "‘ + sales_date + ‘"‘
            row = db.session.execute(sql).fetchone()
            info_dict = row_2_dictionary(row)

            # 计算实际销售额
            sales_amount = int(info_dict[‘sales_amount‘]) - int(obj_dict[‘charge_pay‘]) + int(
                obj_dict[‘charge_pay_discount‘])
            # 计算实际毛利额
            sales_gross_amount = sales_amount - int(info_dict[‘cost_amount‘])

            sql = XDO().get_update_sql(‘szq.org_sales_daily‘, {
                "charge_pay": obj_dict[‘charge_pay‘],
                "charge_pay_discount": obj_dict[‘charge_pay_discount‘],
                "sales_amount": sales_amount,
                "sales_gross_amount": sales_gross_amount,
                "sales_gross_rate": round((sales_gross_amount / sales_amount), 4),
            }, ‘id = "‘ + str(info_dict[‘id‘]) + ‘"‘)
            db.session.execute(sql)

        # 获取：当日通货销售额
        obj_list = self._read_list_by_sql(‘12‘, {"sales_date": sales_date})
        self._save_daily_field_base_list(obj_list, ‘sales_currency_amount‘)

        # 获取：当日充值额
        obj_list = self._read_list_by_sql(‘3‘, {"sales_date": sales_date})
        self._save_daily_field_base_list(obj_list, ‘charge_amount‘)

        # 获取：新超级会员
        obj_list = self._read_list_by_sql(‘4‘, {"sales_date": sales_date})
        self._save_daily_field_base_list(obj_list, ‘svip_count‘)

        # 获取：新奶粉超级会员
        obj_list = self._read_list_by_sql(‘5‘, {"sales_date": sales_date}, {‘nf‘: ‘奶粉%‘})
        self._save_daily_field_base_list(obj_list, ‘svip_nf_count‘)

        # 获取：新纸品超级会员
        obj_list = self._read_list_by_sql(‘6‘, {"sales_date": sales_date}, {‘nb‘: ‘纸品%‘})
        self._save_daily_field_base_list(obj_list, ‘svip_nb_count‘)

        # 获取：购买小票数
        obj_list = self._read_list_by_sql(‘11‘, {"sales_date": sales_date})
        self._save_daily_field_base_list(obj_list, ‘ticket_count‘)

        # 获取：购买客户数
        obj_list = self._read_list_by_sql(‘7‘, {"sales_date": sales_date})
        self._save_daily_field_base_list(obj_list, ‘member_count‘)

        # 获取：新会员数
        obj_list = self._read_list_by_sql(‘13‘, {"sales_date": sales_date})
        self._save_daily_field_base_list(obj_list, ‘new_member_count‘)

        # 补全：客单价
        sql = ‘‘‘UPDATE szq.org_sales_daily SET member_average = ROUND(sales_amount / member_count)
              WHERE sales_date = "%(sales_date)s" AND member_count > 0‘‘‘ % {"sales_date": sales_date}
        db.session.execute(sql)

        db.session.commit()

    # 获取：生成门店销售月报
    def make_org_sales_monthly(self, sales_date):
        # 计算月份：本月，格式：0000-00
        this_month = sales_date[0:7]
        # 计算日期：本月首日，格式：0000-00-00
        this_month_begin = this_month + "-01"
        # 计算时间：本月首日，格式：datetime
        this_month_begin_datetime = datetime.datetime.strptime(this_month_begin, "%Y-%m-%d")
        this_month_today_datetime = datetime.datetime.strptime(sales_date, "%Y-%m-%d")
        # 计算时间：上月末日，格式：datetime
        last_month_end_datetime = this_month_begin_datetime - datetime.timedelta(days=1)
        # 计算月份：上月，格式：0000-00
        last_month = last_month_end_datetime.strftime("%Y-%m")
        # 计算日期：上月首日，格式：0000-00-00
        last_month_begin = last_month + "-01"

        # 生成月度数据，如果存在则替换
        sql = self._get_sql(‘9‘, {
            "begin_date": this_month_begin,
            "sales_date": sales_date
        })
        db.session.execute(sql)

        # 计算日期：上月今天
        if int(last_month_end_datetime.strftime("%d")) < int(sales_date[8:10]):
            last_month_today = last_month_end_datetime.strftime("%Y-%m-%d")
        else:
            last_month_today = last_month + "-" + sales_date[8:10]

        # 完善月度数据
        sql = self._get_sql(‘15‘, {
            "last_month": last_month,
            "last_month_begin": last_month_begin,
            "last_month_end": last_month_today,
            "sales_month": this_month
        })
        db.session.execute(sql)

        sql = self._get_sql(‘17‘, {
            "last_month": last_month,
            "last_month_begin": last_month_begin,
            "last_month_end": last_month_today,
            "sales_month": this_month
        })
        db.session.execute(sql)

        sql = self._get_sql(‘18‘, {
            "last_month": last_month,
            "last_month_begin": last_month_begin,
            "last_month_end": last_month_today,
            "sales_month": this_month
        })
        db.session.execute(sql)

        sql = self._get_sql(‘19‘, {
            "last_month": last_month,
            "last_month_begin": last_month_begin,
            "last_month_end": last_month_today,
            "sales_month": this_month
        })
        db.session.execute(sql)

        # 计算时间：去年开始，格式：datetime
        last_year = str(int(sales_date[0:4]) - 1)
        last_year_begin = last_year + this_month_begin[4:]
        last_year_begin_datetime = datetime.datetime.strptime(last_year_begin, "%Y-%m-%d")
        last_year_end_datetime = last_year_begin_datetime + datetime.timedelta(days=int(sales_date[8:10]) - 1)
        last_year_end = last_year_end_datetime.strftime("%Y-%m-%d")

        # 增加同比
        sql = self._get_sql(‘20‘, {
            "begin": last_year_begin,
            "end": last_year_end,
            "sales_month": this_month
        })
        db.session.execute(sql)

        next_date = this_month_begin_datetime
        step_date = datetime.timedelta(days=1)

        while next_date <= this_month_today_datetime:
            this_date = next_date.strftime("%Y-%m-%d")
            next_date += step_date

            sql = self._get_sql(‘16‘, {
                "sales_date": this_date,
                "sales_month": this_month,
                "day": str(int(this_date[8:10]))
            })
            db.session.execute(sql)

        # 更新上月数据
        sql = ‘‘‘UPDATE szq.org_sales_daily SET member_average = ROUND(sales_amount / member_count)
              WHERE sales_date = "%(sales_date)s" AND member_count > 0‘‘‘ % {"sales_date": sales_date}
        db.session.execute(sql)

    # 生成单门店excel
    def make_excel(self, sales_date):
        # 获取数据
        sql = self._get_sql(‘8‘, {
            "sales_date": sales_date
        })

        rows = db.session.execute(sql)
        obj_list = rows_2_list(rows)

        # 指定Excel模板
        file_path = app.root_path + ‘/xlsx/shop_sales_daily_20181016.xlsx‘

        # 打开模板
        wb = load_workbook(file_path)

        # 获取第一个sheet
        ws = wb[wb.sheetnames[0]]

        ws[‘D1‘] = sales_date

        # 定义起始行、列
        x = 1
        y = 4

        # 循环数据写入sheet
        for obj_dict in obj_list:
            for key in obj_dict:
                value = obj_dict[key]

                if value and hasattr(value, ‘isdigit‘) and value.replace(".", ‘‘).isdigit():
                    value = eval(value)

                ws.cell(row=y, column=x, value=value)
                x += 1

            x = 1
            y += 1

        # 获取当月累计数据
        sql = self._get_sql(‘14‘, {
            "start_date": sales_date[0:8] + "01",
            "sales_date": sales_date
        })

        rows = db.session.execute(sql)
        obj_list = rows_2_list(rows)

        # 获取第二个sheet
        ws = wb[wb.sheetnames[1]]

        ws[‘D1‘] = sales_date

        # 定义起始行、列
        x = 1
        y = 4

        # 循环数据写入sheet
        for obj_dict in obj_list:
            for key in obj_dict:
                value = obj_dict[key]

                if value and hasattr(value, ‘isdigit‘) and value.replace(".", ‘‘).isdigit():
                    value = eval(value)

                ws.cell(row=y, column=x, value=value)
                x += 1

            x = 1
            y += 1

        # 保存excel文件
        wb.save(app.root_path + ‘/../tmp/org_sales_daily_‘ + sales_date + ‘.xlsx‘)
        # wb.save(app.root_path + ‘/../tmp/门店看板_日报_‘ + sales_date + ‘_V2.3.xlsx‘)

    def send_mail(self, sales_date):
        from app.utils.mail import MailUtils

        mail_utils = MailUtils()
        mail_utils.server_host = MailConfig.server_host
        mail_utils.server_port = MailConfig.server_port
        mail_utils.login_user = MailConfig.login_user
        mail_utils.login_pass = MailConfig.login_pass
        mail_utils.mail_from = MailConfig.mail_from

    
        to = [

            ‘何柏喜<hebaixi@mabao51.net>‘,

        ]

        cc = [
            # ‘孙振强163<13488155595@163.com>‘,
        ]

        subject = "门店看板_日报_" + sales_date + "_V2.3"

        content = """您好！<br>
        <br>
        附件是%(sales_date)s门店看板_日报，请查收。<br>
        <br>
        说明：<br>
        1、由于华创系统问题导致财务核算出错，2018年12月27日起将超级会员账本进行合并，合并后的新办超级会员统计数据存在不准确的情况。<br>
        2、门店销售额已对充值赠送消费金额进行折扣计算；<br>
        3、门店销售额中包含服务类（游泳等）销售额，目前尚未剔除；<br>
        4、如有问题，请联系孙振强（13488155595）。<br>
        <br>
        祝工作顺利！<br>
        """ % {"sales_date": sales_date}

        attachments = [
            {
                "file_path": app.root_path + ‘/../tmp/org_sales_daily_‘ + sales_date + ‘.xlsx‘,
                "file_name": ‘门店看板_日报_‘ + sales_date + ‘.xlsx‘,
            }
        ]

        mail_utils.send_mail_by_cfg(to, subject, content, cc, attachments)

    def todo(self, sales_date):
        logger.debug(‘Start: make_org_sales_daily ‘ + sales_date)

        self.make_org_sales_daily(sales_date)
        self.make_org_sales_monthly(sales_date)
        self.make_excel(sales_date)
        self.send_mail(sales_date)

        logger.debug(‘Finish: make_org_sales_daily ‘ + sales_date)

    def todo_batch(self, begin_date, end_date):
        begin = datetime.datetime.strptime(begin_date, "%Y-%m-%d")
        end = datetime.datetime.strptime(end_date, "%Y-%m-%d")

        next_date = begin
        step_date = datetime.timedelta(days=1)

        while next_date <= end:
            sales_date = next_date.strftime("%Y-%m-%d")
            next_date += step_date

            # 打印日期
            logger.debug(‘Start: make_org_sales_daily ‘ + sales_date)

            self.make_org_sales_daily(sales_date)
            self.make_org_sales_monthly(sales_date)
            self.make_excel(sales_date)


if __name__ == ‘__main__‘:
    # OrgSalesDaily().todo("2019-03-31")
    # A= 7
    OrgSalesDaily().todo_batch("2019-03-04", "2019-03-05")