from mysql_db import MysqlDB import datetime from itertools import groupby from xlwt_util import XlwtUtil from file_util import FileUtil from email_util import EmailUtil class ReportPush(object): """ 报表推送功能实现类 """ pass # 1:总浏览量 # # 集团部分+项目部分!!! # ----项目PV--权限项目范围内，求和 # 参数：时间区间 和 项目列表 sql_1_1 = """ SELECT SUM(pv) AS pv1 FROM a_idfa_behavior_sum WHERE report_d >= %s and report_d <= %s AND house_id IN %s """ # ----集团PV--权限项目范围内，求和 # 参数：数据区间和项目列表 sql_1_2 = """ SELECT SUM(pv) AS pav2 FROM a_behavior_brand_mini_day WHERE report_d >= %s and report_d <= %s AND house_id IN %s """ # 2:总浏览人数 # # 参数，数据区间， 项目列表 sql_1_3 = """ SELECT count( DISTINCT IFNULL(mobile, user_id) ) as people FROM ( SELECT A.user_id, B.mobile FROM a_idfa_behavior_sum A LEFT JOIN d_user B ON A.user_id = B.user_id WHERE A.report_d >= %s and A.report_d <= %s AND A.house_id IN %s UNION SELECT A.brand_user_id AS user_id, B.mobile FROM a_behavior_brand_mini_day A LEFT JOIN a_brand_app_customer B ON A.brand_user_id = B.brand_customer_id WHERE A.report_d >= %s and A.report_d <= %s AND A.house_id IN %s ) t1 """ # 3：新增获客 sql_1_4 = """ SELECT count( DISTINCT IFNULL(mobile, user_id) ) FROM ( SELECT user_id, mobile, created FROM d_user WHERE created >= %s AND created <= %s AND house_id IN %s AND source ='3' UNION SELECT brand_customer_id AS user_id, mobile, rlat_created FROM a_brand_app_customer_house_rlat WHERE rlat_created >= %s AND rlat_created <= %s AND rlat_house_id IN %s AND cust_house_flag ='1' ) t1 """ # 4:新增获电 sql_1_5 = """ SELECT COUNT(DISTINCT mobile) FROM ( SELECT user_id, mobile, wx_phone_time AS created FROM d_user WHERE wx_phone_time >= %s AND wx_phone_time <= %s AND house_id IN %s and source = '3' UNION SELECT brand_customer_id, mobile, houdian_time AS created FROM ( SELECT *, CASE WHEN rlat_created > shouquan_time THEN rlat_created ELSE shouquan_time END AS houdian_time FROM a_brand_app_customer_house_rlat WHERE mobile IS NOT NULL AND rlat_house_id IN %s AND cust_house_flag = '1' ) t1 WHERE houdian_time >= %s AND houdian_time <= %s ) t1 """ # 5:集团小程序总浏览量（针对香港置地要命名为【CNC小程序总浏览量】） # 集团部分 # 6:集团小程序总浏览人数（针对香港置地要命名为【CNC小程序总浏览人数】） # # 集团小程序整体UV（见SQL3） sql_1_6 = """ SELECT COUNT(DISTINCT brand_user_id) AS UV4 FROM a_behavior_brand_mini_day WHERE report_d > %s AND report_d < %s AND house_id IN %s """ # 7: 集团小程序新增获客（针对香港置地要命名为【CNC小程序新增获客】） # # 权限项目范围内，集团维度的获客 # 8: 集团小程序新增获电（针对香港置地要命名为【CNC小程序新增获电】） # # 权限项目范围内，集团维度的获电 # 9.单项目小程序总浏览量 # # 项目部分 # # 10.单项目小程序总浏览人数 # # 权限项目未授权部分求和+去重的授权部分（见SQL2） # # 11.单项目小程序新增获客 # # 权限项目未授权部分求和+去重的授权部分（见SQL7） # # 12.单项目小程序新增获电 # # 权限项目所有授权手机号去重（见SQL8） # 1.默认值/001_大麦/项目排行榜/小程序排行榜TOP_N sql_2_1 = """ SELECT a.house_id, a.house_name, ifnull(SUM(a.pv), 0), SUM(a.uv), SUM(a.new_cust_num), SUM(a.wx_num) from ( SELECT a.*, b.house_name, c.interested_num, d.wx_num, e.new_cust_num FROM ( SELECT house_id, count( DISTINCT ifnull(user_id, idfa) ) uv, sum(session_times) session_times, sum(sum_session_time) sum_session_time, sum(pv) pv, sum(page_num) page_num FROM a_idfa_behavior_sum WHERE report_d >= %s AND report_d <= %s GROUP BY house_id ) a JOIN d_house b ON a.house_id = b.house_id LEFT JOIN ( SELECT house_id, count(DISTINCT customer_id) interested_num FROM f_interested_custlist WHERE report_d >= %s AND report_d <= %s GROUP BY house_id ) c ON a.house_id = c.house_id LEFT JOIN ( SELECT house_id, count(DISTINCT mobile) wx_num FROM f_customer_dynamic WHERE dynamic IN (1, 2, 4) AND report_d >= %s AND report_d <= %s GROUP BY house_id ) d ON a.house_id = d.house_id LEFT JOIN ( SELECT house_id, count(DISTINCT user_id) new_cust_num FROM d_user WHERE source IN (1, 2, 3, 4, 10) AND created >= %s AND created < DATE_ADD( %s, INTERVAL 1 DAY ) GROUP BY house_id ) e ON a.house_id = e.house_id) a GROUP BY a.house_id, a.house_name order by a.pv desc """ # 2.默认值/006_大麦（集团）/集团项目排行榜v1.3/集团排行榜 sql_2_2 = """ select x.brand_id, x.house_id, x.house_name, ifnull(x.pv, 0), x.uv, x.new_cust, x.shouquan_cust from ( SELECT c.pv, c.uv, a.brand_id, a.house_id, a.house_name, a.brand_name, ifnull(b.house_layout_num, 0) house_layout_num, ifnull(d.launch_time, '--') launch_time, c.new_cust, c.shouquan_cust, c.revisit_cust FROM ( SELECT brand_id, ifnull(house_id, '0') house_id, sum(pv) pv, count(DISTINCT brand_user_id) uv, count( DISTINCT CASE WHEN is_new_user = 1 THEN brand_user_id END ) new_cust, count( DISTINCT CASE WHEN is_shouquan_user = 1 THEN brand_user_id END ) shouquan_cust, count( DISTINCT CASE WHEN is_new_user = 0 THEN brand_user_id END ) revisit_cust FROM a_behavior_brand_mini_day WHERE report_d >= %s AND report_d <= %s GROUP BY brand_id, ifnull(house_id, '0') ) c LEFT JOIN ( SELECT house_id, count(1) house_layout_num FROM d_content_layout WHERE `status` = '1' AND house_id <> 1 GROUP BY house_id UNION ALL SELECT bb.brand_id house_id, count(1) house_layout_num FROM d_content_layout aa JOIN d_house bb ON aa.house_id = bb.house_id WHERE aa.`status` = '1' AND bb. STATUS = '1' AND aa.house_id <> 1 GROUP BY bb.brand_id ) b ON c.house_id = b.house_id JOIN d_house a ON a.house_id = c.house_id AND a.brand_id = c.brand_id LEFT JOIN d_house_attr d ON c.house_id = d.house_id AND c.brand_id = d.brand_id ) x """ # 默认值/001_大麦/场景_用户来源渠道/用户来源渠道—明细 sql_3_1 = """ SELECT house_id, house_name, label_wx, COUNT(a.id) as counts FROM d_user_attr a LEFT JOIN d_scene b ON a.scene = b. CODE WHERE a.source IN (1, 2, 3, 4, 10) AND a.report_d >= %s AND a.report_d <= %s GROUP BY house_id, house_name, label_wx """ # 默认值/006_大麦（集团）/场景(集团)_用户来源渠道_v1.1/用户来源渠道—明细 sql_3_2 = """ select brand_id, x.brand_name, house_id, house_name, label_wx, COUNT(1) from (SELECT a.scene, a.brand_id, b.*, a.share_brand_customer_id, '2' adviser_agent, a.house_id house_id, c.house_name house_name, c.brand_name FROM ( SELECT scene, brand_id, share_brand_customer_id, house_id FROM d_brand_app_customer WHERE created >= %s AND created < DATE_ADD( %s, INTERVAL 1 DAY ) UNION ALL SELECT scene, brand_id, share_brand_customer_id, brand_id house_id FROM d_brand_app_customer WHERE created >= %s AND created < DATE_ADD( %s, INTERVAL 1 DAY ) ) a LEFT JOIN d_scene b ON a.scene = b. CODE JOIN d_house c ON a.house_id = c.house_id AND a.brand_id = c.brand_id) x group by x.brand_id, x.brand_name, x.house_id, x.house_name, x.label_wx """ # 根据任务id获取推送客户信息 sql_4 = """ select a.task_key, a.customer_id, b.customer_type, b.name, b.mail, GROUP_CONCAT(c.house_or_brand_id) as ids from report_task_info a left join report_push_customer_info b on b.id = a.customer_id left join report_customer_authority_info c on b.id = c.customer_id where a.task_key = %s and a.status = b.status = c.status = 1 group by a.task_key, a.customer_id, b.customer_type, b.name, b.mail """ # 根据集团id获取项目id sql_5 = """ select house_id, house_name from d_house where brand_id = %s and house_id > 300 """ sql_5_1 = """ select house_id from d_house where brand_id in %s """ sql_6 = """insert into report_push_log(name, mail, report_name, push_time, send_status, status, error_message) values(%s, %s, %s, now(), %s, 1, %s) """ # 根据项目id获取集团id和名称 sql_7 = """ select a.brand_id, a.brand_name from d_house a where a.house_id = %s """ sql_8 = """ select DISTINCT a.brand_id from d_house a where a.house_id in %s """ def __init__(self, db_name): self.db = MysqlDB(db_name) pass mails = ['zhangbr@elab-plus.com'] def report_push(self, task_key): message = {} try: report_data = self.report_data_query(task_key) message[1] = '数据查询成功:{}'.format(len(report_data)) time_rang = self.get_time_range(task_key) xu = XlwtUtil() send_info = xu.create_excel(report_data, time_rang, task_key) message[2] = '报表文件生成成功' email_util = EmailUtil() title, content = self.get_title_content(task_key, time_rang) logs = [] for value in send_info: log_data = [] try: if value[2]: result = email_util.send_mail_by_admin(title, content, value[1], value[2], value[3]) if result: log_data = [value[0], value[1], value[2], 1, 'success!!!'] else: log_data = [value[0], value[1], value[2], -1, '失败'] except Exception as e: log_data = [value[0], value[1], value[2], -1, str(e)] print(str(e)) logs.append(log_data) message[3] = '邮件发送成功' message['data'] = send_info self.db.add_some(self.sql_6, logs) self.db.close() FileUtil.remove_files(7, xu.save_path) except Exception as e: print(str(e)) message['error'] = str(e) pass finally: return message def report_push_test(self, task_key): message = {} try: report_data = self.report_data_query(task_key) message['data'] = report_data message[1] = '数据查询成功:{}'.format(len(report_data)) time_rang = self.get_time_range(task_key) xu = XlwtUtil() send_info = xu.create_excel(report_data, time_rang, task_key) message[2] = '报表文件生成成功' email_util = EmailUtil() title, content = self.get_title_content(task_key, time_rang) logs = [] for value in send_info: log_data = [] try: if value[2]: for mail in self.mails: result = email_util.send_mail_by_admin(title, content, mail, value[2], value[3]) if result: log_data = [value[0], value[1], value[2], 1, 'success!!!'] else: log_data = [value[0], value[1], value[2], -1, 'fail'] except Exception as e: log_data = [value[0], value[1], value[2], -1, str(e)] print(str(e)) logs.append(log_data) message[3] = '邮件发送成功' message['data'] = send_info self.db.add_some(self.sql_6, logs) self.db.close() FileUtil.remove_files(7, xu.save_path) except Exception as e: print(str(e)) message['error'] = str(e) pass finally: return message def get_title_content(self, task_key, time_range): if task_key in (1, 4): return '移动案场订阅日报[{}]'.format(time_range[0]), '本期间内【{}】至【{}】的数据报告已经准备完成。请点击附件查阅。'.format(time_range[0], time_range[1]) elif task_key in (2, 3, 21): return '移动案场订阅周报[{}]至[{}]'.format(time_range[0], time_range[1]), '本期间内【{}】至【{}】的数据报告已经准备完成。请点击附件查阅.'.format(time_range[0], time_range[1]) def report_data_query(self, task_key): """ 定时任务推送数据准备 :param task_key: :return: """ result = {} try: # 根据任务key获取需要推送的客户以及可以的权限 customers = self.db.select(self.sql_4, [task_key]) # a.task_key, a.customer_id, b.customer_type, b.`name`, b.mail, GROUP_CONCAT(c.house_or_brand_id) time_range = self.get_time_range(task_key) all_time_rang = self.get_time_range(9999) # 有限时间范围内的数据 xcx_top_data = self.xcx_top(time_range) brand_top_data = self.brand_top(time_range) customer_channel_details_data = self.customer_channel_details(time_range) brand_customer_channel_details = self.brand_customer_channel_details(time_range) # 所有历史数据 xcx_top_data_all = self.xcx_top(all_time_rang) brand_top_data_all = self.brand_top(all_time_rang) for customer in customers: customer_data = {} name = customer[3] mail = customer[4] customer_type = customer[2] house_ids = [] brand_id_list = [] if customer_type == 1: # 项目 ids = customer[5] if str(ids).find(',') != -1: house_ids = [x for x in str(ids).split(',')] else: house_ids = [ids] brand_id_list = self.get_brand_ids_by_house_ids(house_ids) pass elif customer_type == 2: # 集团 brand_ids = customer[5] if str(brand_ids).find(',') != -1: brands = [x for x in str(brand_ids).split(',')] else: brands = [brand_ids] for id in brands: house_ids.extend([x[0] for x in self.get_house_ids_by_brand_id(id)]) brand_id_list = brands result_data_1 = [] result_data_2 = [] result_data_3 = [] result_data_4 = [] result_data_5 = [] result_data_7 = [] result_data_8 = [] all_house_ids = self.get_house_ids_by_brand_ids(brand_id_list) xcx_top_data_part = self.filter_by_house_ids(xcx_top_data, all_house_ids) brand_top_data_part = self.filter_by_brand_ids(brand_top_data, brand_id_list) xcx_top_data_all_part = self.filter_by_house_ids(xcx_top_data_all, all_house_ids) brand_top_data_all_part = self.filter_by_brand_ids(brand_top_data_all, brand_id_list) # 1 数据总览 12个统计指标 data_overview = self.data_overview(time_range, house_ids, xcx_top_data_part, brand_top_data_part) result_data_1.extend(data_overview) # 4：单个项目小程序数据排行榜 # 排名 项目名称 总浏览量 总浏览人数 新增获客 新增获电 for index, x in enumerate(xcx_top_data_part): if x[0] in house_ids: result_data_4.append([index + 1, x[1], x[2], x[3], x[4], x[5]]) # 5: 集团项目数据排行榜 # 排名 项目名称 总浏览量 总浏览人数 新增获客 新增获电 if customer_type == 2: for index, x2 in enumerate(brand_top_data_part): if x2[1] in house_ids or x2[0] in brand_id_list: result_data_5.append([index + 1, x2[2], x2[3], x2[4], x2[5], x2[6]]) pass pass elif customer_type == 1: for index, x1 in enumerate(brand_top_data_part): if x1[1] in house_ids: result_data_5.append([index + 1, x1[2], x1[3], x1[4], x1[5], x1[6]]) else: pass # 2: 项目数据排行榜 # 排名 项目名称 总浏览量 总浏览人数 新增获客 新增获电 house_with_brand_data = self.house_with_brand(xcx_top_data_part, brand_top_data_part) for x in house_with_brand_data: if x[2] in house_ids: x.pop(1) x.pop(1) result_data_2.append(x) # 3: 项目历史累计总数 # 排名 项目名称 总浏览量 总浏览人数 新增获客 新增获电 if customer_type == 2: all_data_history = self.house_with_brand(xcx_top_data_all_part, brand_top_data_all_part, brand_id_list) for x in all_data_history: if x[2] in house_ids: x.pop(1) x.pop(1) result_data_3.append(x) else: all_data_history = self.house_with_brand(xcx_top_data_all_part, brand_top_data_all_part) for x in all_data_history: if x[2] in house_ids: x.pop(1) x.pop(1) result_data_3.append(x) # 7: 单个项目小程序获客来源场景分析 # 项目 合计 长按识别二维码 会话 公众号菜单 公众号文章 小程序历史列表 扫一扫二维码 搜索 相册选取二维码 其他小程序 其他 for x in customer_channel_details_data: if x[0] in house_ids: result_data_7.append(x) # 8: 集团项目获客来源场景分析 if customer_type == 2: for x in brand_customer_channel_details: if x[2] in house_ids or x[0] in brand_id_list: result_data_8.append(x) elif customer_type == 1: for x in brand_customer_channel_details: if x[2] in house_ids: result_data_8.append(x) # 6: 项目获客来源场景分析 result_data_6 = self.house_with_brand_for_share(result_data_7, result_data_8) customer_data[1] = result_data_1 result_data_2.sort(key=lambda obj: obj[0]) customer_data[2] = result_data_2 result_data_3.sort(key=lambda obj: obj[0]) customer_data[3] = result_data_3 result_data_4.sort(key=lambda obj: obj[0]) customer_data[4] = result_data_4 result_data_5.sort(key=lambda obj: obj[0]) customer_data[5] = result_data_5 self.sort(result_data_6, 1) customer_data[6] = result_data_6 result_data_7_format = [] for x in result_data_7: house_name = x[1] ele = [house_name] data = x[2:] total = sum(data) ele.append(total) ele.extend(data) result_data_7_format.append(ele) self.sort(result_data_7_format, 1) customer_data[7] = result_data_7_format result_data_8_format = [] for x in result_data_8: ele = [] house_name = x[3] data = x[4:] total = sum(data) ele.append(house_name) ele.append(total) ele.extend(data) result_data_8_format.append(ele) self.sort(result_data_8_format, 1) customer_data[8] = result_data_8_format customer_data[0] = mail result[name] = customer_data # return result except Exception as e: result['error'] = str(e) pass finally: return result def sort(self, data, idnex): data.sort(key=lambda obj: obj[idnex]) data.reverse() def house_with_brand_for_share(self, house_data, brand_data): house_ids = [] result = [] for x in house_data: if x[0] not in [a[0] for a in house_ids]: house_ids.append([x[0], x[1]]) for x in brand_data: if x[2] not in [a[0] for a in house_ids]: house_ids.append([x[2], x[3]]) for id in house_ids: house_id = id[0] house_name = id[1] house_result = [house_name] equal_house = self.equal_by_house_id(house_data, house_id, 0) equal_brand = self.equal_by_house_id(brand_data, house_id, 2) house_result_part = [] if equal_house and equal_brand: equal_house = equal_house[2:] equal_brand = equal_brand[4:] for i in range(0, 10): house_result_part.append(self.add(equal_house[i], equal_brand[i])) elif equal_house and not equal_brand: equal_house = equal_house[2:] for i in range(0, 10): house_result_part.append(equal_house[i]) elif not equal_house and equal_brand: equal_brand = equal_brand[4:] for i in range(0, 10): house_result_part.append(equal_brand[i]) house_result.append(sum(house_result_part)) house_result.extend(house_result_part) result.append(house_result) result.sort(key=lambda obj: obj[1]) result.reverse() return result def equal_by_house_id(self, data, house_id, index): for x in data: if x[index] == house_id: return x def filter_by_brand_ids(self, data, brand_ids): result = [] for x in data: if x[0] in brand_ids: result.append(x) result.sort(key=lambda obj: obj[3]) result.reverse() return result def filter_by_house_ids(self, data, house_ids): result = [] for x in data: if x[0] in house_ids: result.append(x) result.sort(key=lambda obj: obj[2]) result.reverse() return result def data_overview(self, time_range, house_ids, xcx_top_data, brand_top_data): """ 统计数据总览 :param time_range: :param house_ids: :return: """ result = [] # 1:总浏览量 data_1_1 = self.db.select(self.sql_1_1, [time_range[0], time_range[1], house_ids]) number_1_1 = data_1_1[0][0] data_1_2 = self.db.select(self.sql_1_2, [time_range[0], time_range[1], house_ids]) number_1_2 = data_1_2[0][0] number_1 = self.add(number_1_1, number_1_2) result.append(number_1) # 2: 总浏览人数 data_2 = self.db.select(self.sql_1_3, [time_range[0], time_range[1], house_ids, time_range[0], time_range[1], house_ids]) number_2 = data_2[0][0] result.append(number_2) # 3:新增获客 time_1 = time_range[0] + ' 00:00:00' time_2 = time_range[1] + ' 23:59:59' data_3 = self.db.select(self.sql_1_4, [time_1, time_2, house_ids, time_1, time_2, house_ids]) number_3 = data_3[0][0] result.append(number_3) # 4:新增获电 data_4 = self.db.select(self.sql_1_5, [time_1, time_2, house_ids, house_ids, time_1, time_2]) number_4 = data_4[0][0] result.append(number_4) xcx_top_data_part = [x for x in xcx_top_data if x[0] in house_ids] brand_top_data_part = [x for x in brand_top_data if x[1] in house_ids] # 5 6 7 8 number_5 = 0 number_6 = 0 number_7 = 0 number_8 = 0 for x in brand_top_data_part: number_5 = self.add(number_5, x[3]) number_6 = self.add(number_6, x[4]) number_7 = self.add(number_7, x[5]) number_8 = self.add(number_8, x[6]) result.append(number_5) result.append(number_6) result.append(number_7) result.append(number_8) # 9 10 11 12 number_9 = 0 number_10 = 0 number_11 = 0 number_12 = 0 for x in xcx_top_data_part: number_9 = self.add(number_9, x[2]) number_10 = self.add(number_10, x[3]) number_11 = self.add(number_11, x[4]) number_12 = self.add(number_12, x[5]) result.append(number_9) result.append(number_10) result.append(number_11) result.append(number_12) return result def house_with_brand(self, xcx_top_data, brand_top_data, brands=None): """ 项目数据和集团数据的求和 :param brands: :param xcx_top_data: :param brand_top_data: :return: """ result = [] house_ids = [] for x in xcx_top_data: if x[0] not in house_ids: house_ids.append(x[0]) for x in brand_top_data: if x[1] not in house_ids and x[1] is not None and int(x[1]) > 300: house_ids.append(x[1]) for house_id in house_ids: a = [] a_order = 0 for index, x in enumerate(xcx_top_data): if str(house_id) == str(x[0]): a.extend(x) a_order = index + 1 b = [] b_order = 0 for index, y in enumerate(brand_top_data): if str(house_id) == str(y[1]): b.extend(y) b_order = index + 1 order = b_order if b_order > 0 else a_order if len(a) > 0 and len(b) > 0: result.append([order, b[0], a[0], a[1], self.add(a[2], b[3]), self.add(a[3], b[4]), self.add(a[4], b[5]), self.add(a[5], b[6])]) elif len(a) > 0 and len(b) == 0: _a = [order, 1] for x in a: _a.append(x) result.append(_a) elif len(a) == 0 and len(b) > 0: _b = [order] _b.extend(b) result.append(_b) else: pass if brands: for index, x in enumerate(brand_top_data): if x[0] in brands and x[1] is not None and int(x[1]) == 0: _x = [index + 1] _x.extend(x) result.append(_x) result.sort(key=lambda obj: obj[3]) result.reverse() return result def add(self, a=None, b=None): if a and b: return a + b elif a and not b: return a elif b and not a: return b return 0 def xcx_top(self, time_range): """ 获取 1.默认值/001_大麦/项目排行榜/小程序排行榜TOP_N :return: """ params = [] params.extend(time_range) params.extend(time_range) params.extend(time_range) params.extend(time_range) xcx_top_data = self.db.select(self.sql_2_1, params) result = [] for x in xcx_top_data: result.append([n for n in x]) # xcx_top_data的结果结构 # 0 a.house_id, 项目id # 1 a.house_name, 项目名称 # 2 SUM(a.pv), 浏览总量 # 3 SUM(a.uv), 浏览人数 # 4 SUM(a.new_cust_num), 新增获客 # 5 SUM(a.wx_num) 授权手机号 result.sort(key=lambda obj: obj[2]) result.reverse() return result def brand_top(self, time_range): """ 2.默认值/006_大麦（集团）/集团项目排行榜v1.3/集团排行榜 :param task_key: :return: """ params = [time_range[0], time_range[1]] brand_top_data = self.db.select(self.sql_2_2, params) result = [] for x in brand_top_data: result.append([n for n in x]) # brand_top_data结果的结构 # 0 a.brand_id, 集团id # 1 a.house_id, 项目id # 2 a.house_name, 项目名称 # 3 SUM(a.pv), 浏览总量 # 4 SUM(a.uv), 浏览人数 # 5 SUM(a.new_cust), 新增获客 # 6 SUM(a.shouquan_cust) 授权手机号 result.sort(key=lambda obj: obj[3]) result.reverse() return result """ 数据分享类别 1:长按识别二维码 2:会话 3:公众号菜单 4:公众号文章 5:小程序历史列表 6:扫一扫二维码 7:搜索 8:相册选取二维码 9:其他小程序 10:其他 """ share_way = { "长按识别二维码": 1, "会话": 2, "公众号菜单": 3, '公众号文章': 4, '小程序历史列表': 5, '扫一扫二维码': 6, '搜索': 7, '相册选取二维码': 8, '其他小程序': 9, '其他': 10 } def customer_channel_details(self, time_range): """ 1.默认值/001_大麦/场景_用户来源渠道/用户来源渠道—明细 :param task_key: :return: """ params = [] params.extend(time_range) customer_channel_details_data = self.db.select(self.sql_3_1, params) result = [] for x in customer_channel_details_data: ele = [] order = self.share_way.get(x[2]) if order: ele.append(x[0]) ele.append(x[1]) ele.append(order) ele.append(x[3]) result.append(ele) result.sort(key=lambda obj: obj[0]) end_data = [] for key, data in groupby(result, key=lambda obj: obj[0]): others_data = [] for ot in data: others_data.append([x for x in ot]) lable_data = [] if len(others_data) > 0: lable_data.append(others_data[0][0]) lable_data.append(others_data[0][1]) for i in range(1, 11): number = 0 for od in others_data: if i == od[2]: number = od[3] else: pass lable_data.append(number) pass end_data.append(lable_data) # customer_channel_details_data数据结构 # house_id, 项目id # house_name, 项目名称 # label_wx, 分享类别 # COUNT(a.id) as counts， 数量 return end_data def brand_customer_channel_details(self, time_range): """ 2.默认值/006_大麦（集团）/场景(集团)_用户来源渠道_v1.1/用户来源渠道—明细 :param frequency: :return: """ params = [time_range[0], time_range[1], time_range[0], time_range[1]] brand_customer_channel_details_data = self.db.select(self.sql_3_2, params) # brand_customer_channel_details_data数据结构 # 0 brand_id, 集团id # 1 x.brand_name, 集团名称 # 2 house_id, 项目id # 3 house_name, 项目名称 # 4 label_wx, 分享类别 # 5 COUNT(1) 数量 result = [] for x in brand_customer_channel_details_data: ele = [] order = self.share_way.get(x[4]) if order: ele.append(x[0]) ele.append(x[1]) ele.append(x[2]) ele.append(x[3]) ele.append(order) ele.append(x[5]) result.append(ele) result.sort(key=lambda obj: obj[2]) end_data = [] for key, data in groupby(result, key=lambda obj: obj[2]): others_data = [] for ot in data: others_data.append([x for x in ot]) lable_data = [] if len(others_data) > 0: lable_data.extend(others_data[0][0: 4]) for i in range(1, 11): number = 0 for od in others_data: if i == od[4]: number = od[5] else: pass lable_data.append(number) pass end_data.append(lable_data) return end_data def push_log_recording(self, push_message): """ 报表推送日志记录 :param push_message: :return: """ self.db.add_some(self.sql_6, push_message) def get_house_ids_by_brand_id(self, brand_id): return self.db.select(self.sql_5, [brand_id]) def get_brand_info_by_house_id(self, house_id): """ 根据项目id或者相应的集团信息 :param house_id: :return: """ brand_info = self.db.select(self.sql_7, [house_id]) if len(brand_info) == 1: return brand_info[0][0] return def get_brand_ids_by_house_ids(self, house_ids): brand_ids = self.db.select(self.sql_8, [house_ids]) ids = [] for x in brand_ids: if x and x[0]: ids.append(x[0]) return ids def get_house_ids_by_brand_ids(self, brand_ids): result = [] ids = self.db.select(self.sql_5_1, [brand_ids]) for x in ids: if x[0] not in result: result.append(x[0]) return result def get_time_range(self, task_key): """ 根据定时任务id获取时间区间 时间格式 yyyy-mm-dd :param task_key:1: 日报，2：周报, 3:all :return: """ now_time = datetime.datetime.now() pre_time = None if task_key in (2, 3, 21): # 上周，上周一到上周天 pre_time = now_time + datetime.timedelta(days=-7) now_time = now_time + datetime.timedelta(days=-1) pass elif task_key in (1, 4): # 昨天 pre_time = now_time + datetime.timedelta(days=-1) now_time = now_time + datetime.timedelta(days=-1) pass elif task_key in (9999, 9999): # 不限时间 pre_time = now_time + datetime.timedelta(days=-2999) return [pre_time.strftime('%Y-%m-%d'), now_time.strftime('%Y-%m-%d')] if __name__ == '__main__': rp = ReportPush('linshi') times = rp.get_time_range(1)