1 写出它的方法
2 可以查找方法在那里调用了,注释掉调用就好,不用注释全部代码
3 先运行,再查找问题.
三个部分: 1 数据库表里的表,写进去.
2 按顺序,写入EXCEL
3 发送邮件.
其中最主要的部分只有一个,准备好一张表,写好具体的字段,然后取数填充,然后填充进Mabao51 BI 库里去.
新增一张表,在pycharm 项目代码里,写好SQL , 配置好外链接到一张表里. 直接操作SQL ,写好.然后循环写入EXCEL,线上订单表里.
最后发送邮件即可了.
其实还有一个步骤,就是每天晚上的定时执行.很重要.
现在重新写一个表.没办法.只有这样了.线上订单表.
分类思维导图方法:
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 = [ ] 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")