report_push.py 40 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050105110521053105410551056105710581059106010611062106310641065106610671068106910701071107210731074107510761077107810791080108110821083108410851086
  1. from mysql_db import MysqlDB
  2. import datetime
  3. from itertools import groupby
  4. from xlwt_util import XlwtUtil
  5. from file_util import FileUtil
  6. from email_util import EmailUtil
  7. class ReportPush(object):
  8. """
  9. 报表推送功能实现类
  10. """
  11. pass
  12. # 1:总浏览量
  13. #
  14. # 集团部分+项目部分!!!
  15. # ----项目PV--权限项目范围内,求和
  16. # 参数:时间区间 和 项目列表
  17. sql_1_1 = """
  18. SELECT SUM(pv) AS pv1 FROM a_idfa_behavior_sum
  19. WHERE report_d >= %s and report_d <= %s AND house_id IN %s
  20. """
  21. # ----集团PV--权限项目范围内,求和
  22. # 参数:数据区间和项目列表
  23. sql_1_2 = """
  24. SELECT SUM(pv) AS pav2 FROM a_behavior_brand_mini_day
  25. WHERE report_d >= %s and report_d <= %s AND house_id IN %s
  26. """
  27. # 2:总浏览人数
  28. #
  29. # 参数,数据区间, 项目列表
  30. sql_1_3 = """
  31. SELECT
  32. count(
  33. DISTINCT IFNULL(mobile, user_id)
  34. ) as people
  35. FROM
  36. (
  37. SELECT
  38. A.user_id,
  39. B.mobile
  40. FROM
  41. a_idfa_behavior_sum A
  42. LEFT JOIN d_user B ON A.user_id = B.user_id
  43. WHERE
  44. A.report_d >= %s and A.report_d <= %s
  45. AND A.house_id IN %s
  46. UNION
  47. SELECT
  48. A.brand_user_id AS user_id,
  49. B.mobile
  50. FROM
  51. a_behavior_brand_mini_day A
  52. LEFT JOIN a_brand_app_customer B ON A.brand_user_id = B.brand_customer_id
  53. WHERE
  54. A.report_d >= %s and A.report_d <= %s
  55. AND A.house_id IN %s
  56. ) t1
  57. """
  58. # 3:新增获客
  59. sql_1_4 = """
  60. SELECT
  61. count(
  62. DISTINCT IFNULL(mobile, user_id)
  63. )
  64. FROM
  65. (
  66. SELECT
  67. user_id,
  68. mobile,
  69. created
  70. FROM
  71. d_user
  72. WHERE
  73. created >= %s
  74. AND created <= %s
  75. AND house_id IN %s
  76. UNION
  77. SELECT
  78. brand_customer_id AS user_id,
  79. mobile,
  80. rlat_created
  81. FROM
  82. a_brand_app_customer_house_rlat
  83. WHERE
  84. rlat_created >= %s
  85. AND rlat_created <= %s
  86. AND rlat_house_id IN %s
  87. ) t1
  88. """
  89. # 4:新增获电
  90. sql_1_5 = """
  91. SELECT
  92. COUNT(DISTINCT mobile)
  93. FROM
  94. (
  95. SELECT
  96. user_id,
  97. mobile,
  98. wx_phone_time AS created
  99. FROM
  100. d_user
  101. WHERE
  102. wx_phone_time >= %s
  103. AND wx_phone_time <= %s
  104. AND house_id IN %s
  105. UNION
  106. SELECT
  107. brand_customer_id,
  108. mobile,
  109. houdian_time AS created
  110. FROM
  111. (
  112. SELECT
  113. *,
  114. CASE
  115. WHEN rlat_created > shouquan_time THEN
  116. rlat_created
  117. ELSE
  118. shouquan_time
  119. END AS houdian_time
  120. FROM
  121. a_brand_app_customer_house_rlat
  122. WHERE
  123. mobile IS NOT NULL
  124. AND rlat_house_id IN %s
  125. AND cust_house_flag = '1'
  126. ) t1
  127. WHERE
  128. houdian_time >= %s
  129. AND houdian_time < %s
  130. ) t1
  131. """
  132. # 5:集团小程序总浏览量(针对香港置地要命名为【CNC小程序总浏览量】)
  133. # 集团部分
  134. # 6:集团小程序总浏览人数(针对香港置地要命名为【CNC小程序总浏览人数】)
  135. #
  136. # 集团小程序整体UV(见SQL3)
  137. sql_1_6 = """
  138. SELECT
  139. COUNT(DISTINCT brand_user_id) AS UV4
  140. FROM
  141. a_behavior_brand_mini_day
  142. WHERE
  143. report_d > %s
  144. AND report_d < %s
  145. AND house_id IN %s
  146. """
  147. # 7: 集团小程序新增获客(针对香港置地要命名为【CNC小程序新增获客】)
  148. #
  149. # 权限项目范围内,集团维度的获客
  150. # 8: 集团小程序新增获电(针对香港置地要命名为【CNC小程序新增获电】)
  151. #
  152. # 权限项目范围内,集团维度的获电
  153. # 9.单项目小程序总浏览量
  154. #
  155. # 项目部分
  156. #
  157. # 10.单项目小程序总浏览人数
  158. #
  159. # 权限项目未授权部分求和+去重的授权部分(见SQL2)
  160. #
  161. # 11.单项目小程序新增获客
  162. #
  163. # 权限项目未授权部分求和+去重的授权部分(见SQL7)
  164. #
  165. # 12.单项目小程序新增获电
  166. #
  167. # 权限项目所有授权手机号去重(见SQL8)
  168. # 1.默认值/001_大麦/项目排行榜/小程序排行榜TOP_N
  169. sql_2_1 = """
  170. SELECT
  171. a.house_id,
  172. a.house_name,
  173. ifnull(SUM(a.pv), 0),
  174. SUM(a.uv),
  175. SUM(a.new_cust_num),
  176. SUM(a.wx_num)
  177. from
  178. (
  179. SELECT
  180. a.*, b.house_name,
  181. c.interested_num,
  182. d.wx_num,
  183. e.new_cust_num
  184. FROM
  185. (
  186. SELECT
  187. house_id,
  188. count(
  189. DISTINCT ifnull(user_id, idfa)
  190. ) uv,
  191. sum(session_times) session_times,
  192. sum(sum_session_time) sum_session_time,
  193. sum(pv) pv,
  194. sum(page_num) page_num
  195. FROM
  196. a_idfa_behavior_sum
  197. WHERE
  198. report_d >= %s
  199. AND report_d <= %s
  200. GROUP BY
  201. house_id
  202. ) a
  203. JOIN d_house b ON a.house_id = b.house_id
  204. LEFT JOIN (
  205. SELECT
  206. house_id,
  207. count(DISTINCT customer_id) interested_num
  208. FROM
  209. f_interested_custlist
  210. WHERE
  211. report_d >= %s
  212. AND report_d <= %s
  213. GROUP BY
  214. house_id
  215. ) c ON a.house_id = c.house_id
  216. LEFT JOIN (
  217. SELECT
  218. house_id,
  219. count(DISTINCT mobile) wx_num
  220. FROM
  221. f_customer_dynamic
  222. WHERE
  223. dynamic IN (1, 2, 4)
  224. AND report_d >= %s
  225. AND report_d <= %s
  226. GROUP BY
  227. house_id
  228. ) d ON a.house_id = d.house_id
  229. LEFT JOIN (
  230. SELECT
  231. house_id,
  232. count(DISTINCT user_id) new_cust_num
  233. FROM
  234. d_user
  235. WHERE
  236. source IN (1, 2, 3, 4, 10)
  237. AND created >= %s
  238. AND created < DATE_ADD(
  239. %s, INTERVAL 1 DAY
  240. )
  241. GROUP BY
  242. house_id
  243. ) e ON a.house_id = e.house_id)
  244. a
  245. GROUP BY
  246. a.house_id,
  247. a.house_name
  248. order by a.pv desc
  249. """
  250. # 2.默认值/006_大麦(集团)/集团项目排行榜v1.3/集团排行榜
  251. sql_2_2 = """
  252. select
  253. x.brand_id,
  254. x.house_id,
  255. x.house_name,
  256. ifnull(x.pv, 0),
  257. x.uv,
  258. x.new_cust,
  259. x.shouquan_cust
  260. from (
  261. SELECT
  262. c.pv,
  263. c.uv,
  264. a.brand_id,
  265. a.house_id,
  266. a.house_name,
  267. a.brand_name,
  268. ifnull(b.house_layout_num, 0) house_layout_num,
  269. ifnull(d.launch_time, '--') launch_time,
  270. c.new_cust,
  271. c.shouquan_cust,
  272. c.revisit_cust
  273. FROM
  274. (
  275. SELECT
  276. brand_id,
  277. ifnull(house_id, '0') house_id,
  278. sum(pv) pv,
  279. count(DISTINCT brand_user_id) uv,
  280. count(
  281. DISTINCT CASE
  282. WHEN is_new_user = 1 THEN
  283. brand_user_id
  284. END
  285. ) new_cust,
  286. count(
  287. DISTINCT CASE
  288. WHEN is_shouquan_user = 1 THEN
  289. brand_user_id
  290. END
  291. ) shouquan_cust,
  292. count(
  293. DISTINCT CASE
  294. WHEN is_new_user = 0 THEN
  295. brand_user_id
  296. END
  297. ) revisit_cust
  298. FROM
  299. a_behavior_brand_mini_day
  300. WHERE
  301. report_d >= %s
  302. AND report_d <= %s
  303. GROUP BY
  304. brand_id,
  305. ifnull(house_id, '0')
  306. ) c
  307. LEFT JOIN (
  308. SELECT
  309. house_id,
  310. count(1) house_layout_num
  311. FROM
  312. d_content_layout
  313. WHERE
  314. `status` = '1'
  315. AND house_id <> 1
  316. GROUP BY
  317. house_id
  318. UNION ALL
  319. SELECT
  320. bb.brand_id house_id,
  321. count(1) house_layout_num
  322. FROM
  323. d_content_layout aa
  324. JOIN d_house bb ON aa.house_id = bb.house_id
  325. WHERE
  326. aa.`status` = '1'
  327. AND bb. STATUS = '1'
  328. AND aa.house_id <> 1
  329. GROUP BY
  330. bb.brand_id
  331. ) b ON c.house_id = b.house_id
  332. JOIN d_house a ON a.house_id = c.house_id
  333. AND a.brand_id = c.brand_id
  334. LEFT JOIN d_house_attr d ON c.house_id = d.house_id
  335. AND c.brand_id = d.brand_id ) x
  336. """
  337. # 默认值/001_大麦/场景_用户来源渠道/用户来源渠道—明细
  338. sql_3_1 = """
  339. SELECT
  340. house_id,
  341. house_name,
  342. label_wx,
  343. COUNT(a.id) as counts
  344. FROM
  345. d_user_attr a
  346. LEFT JOIN d_scene b ON a.scene = b. CODE
  347. WHERE
  348. a.source IN (1, 2, 3, 4, 10)
  349. AND a.report_d >= %s
  350. AND a.report_d <= %s
  351. GROUP BY
  352. house_id,
  353. house_name,
  354. label_wx
  355. """
  356. # 默认值/006_大麦(集团)/场景(集团)_用户来源渠道_v1.1/用户来源渠道—明细
  357. sql_3_2 = """
  358. select
  359. brand_id,
  360. x.brand_name,
  361. house_id,
  362. house_name,
  363. label_wx,
  364. COUNT(1)
  365. from
  366. (SELECT
  367. a.scene,
  368. a.brand_id,
  369. b.*, a.share_brand_customer_id,
  370. '2' adviser_agent,
  371. a.house_id house_id,
  372. c.house_name house_name,
  373. c.brand_name
  374. FROM
  375. (
  376. SELECT
  377. scene,
  378. brand_id,
  379. share_brand_customer_id,
  380. house_id
  381. FROM
  382. d_brand_app_customer
  383. WHERE
  384. created >= %s
  385. AND created < DATE_ADD(
  386. %s, INTERVAL 1 DAY
  387. )
  388. UNION ALL
  389. SELECT
  390. scene,
  391. brand_id,
  392. share_brand_customer_id,
  393. brand_id house_id
  394. FROM
  395. d_brand_app_customer
  396. WHERE
  397. created >= %s
  398. AND created < DATE_ADD(
  399. %s, INTERVAL 1 DAY
  400. )
  401. ) a
  402. LEFT JOIN d_scene b ON a.scene = b. CODE
  403. JOIN d_house c ON a.house_id = c.house_id
  404. AND a.brand_id = c.brand_id) x
  405. group by x.brand_id, x.brand_name, x.house_id, x.house_name, x.label_wx
  406. """
  407. # 根据任务id获取推送客户信息
  408. sql_4 = """
  409. select a.task_key, a.customer_id, b.customer_type, b.name, b.mail, GROUP_CONCAT(c.house_or_brand_id) as ids
  410. from report_task_info a left join report_push_customer_info b on b.id = a.customer_id
  411. left join report_customer_authority_info c on b.id = c.customer_id
  412. where a.task_key = %s and a.status = b.status = c.status = 1
  413. group by a.task_key, a.customer_id, b.customer_type, b.name, b.mail
  414. """
  415. # 根据集团id获取项目id
  416. sql_5 = """
  417. select house_id, house_name from d_house where brand_id = %s
  418. """
  419. sql_5_1 = """
  420. select house_id from d_house where brand_id in %s
  421. """
  422. sql_6 = """insert into report_push_log(name, mail, report_name, push_time, send_status, status) values(%s, %s,
  423. %s, now(), %s, 1) """
  424. # 根据项目id获取集团id和名称
  425. sql_7 = """
  426. select a.brand_id, a.brand_name from d_house a where a.house_id = %s
  427. """
  428. sql_8 = """
  429. select DISTINCT a.brand_id from d_house a where a.house_id in %s
  430. """
  431. def __init__(self, db_name):
  432. self.db = MysqlDB(db_name)
  433. mails = ['1285211525@qq.com', 'lijm@elab-plus.com', 'xuanxc@elab-plus.com']
  434. def report_push(self, task_key):
  435. message = {}
  436. try:
  437. report_data = self.report_data_query(task_key)
  438. message[1] = '数据查询成功:{}'.format(len(report_data))
  439. time_rang = self.get_time_range(task_key)
  440. xu = XlwtUtil()
  441. send_info = xu.create_excel(report_data, time_rang, task_key)
  442. message[2] = '报表文件生成成功'
  443. email_util = EmailUtil()
  444. title, content = self.get_title_content(task_key, time_rang)
  445. logs = []
  446. for value in send_info:
  447. log_data = []
  448. try:
  449. if value[2]:
  450. email_util.send_mail(title, content, self.mails, value[2])
  451. log_data = [value[0], value[1], value[2], 1]
  452. except Exception as e:
  453. log_data = [value[0], value[1], value[2], -1]
  454. print(str(e))
  455. logs.append(log_data)
  456. message[3] = '邮件发送成功'
  457. message['data'] = send_info
  458. self.db.add_some(self.sql_6, logs)
  459. self.db.close()
  460. email_util.quit_mail()
  461. FileUtil.remove_files(7, xu.save_path)
  462. except Exception as e:
  463. print(str(e))
  464. message['error'] = str(e)
  465. pass
  466. finally:
  467. return message
  468. def get_title_content(self, task_key, time_range):
  469. if task_key == 1:
  470. return '移动案场订阅日报[{}]'.format(time_range[0]), '本期间内【{}】至【{}】的数据报告已经准备完成。请点击附件查阅。'.format(time_range[0], time_range[1])
  471. elif task_key in (2, 3):
  472. return '移动案场订阅周报[{}]至[{}]'.format(time_range[0], time_range[1]), '本期间内【{}】至【{}】的数据报告已经准备完成。请点击附件查阅.'.format(time_range[0], time_range[1])
  473. def report_data_query(self, task_key):
  474. """
  475. 定时任务推送数据准备
  476. :param task_key:
  477. :return:
  478. """
  479. result = {}
  480. try:
  481. # 根据任务key获取需要推送的客户以及可以的权限
  482. customers = self.db.select(self.sql_4, [task_key])
  483. # a.task_key, a.customer_id, b.customer_type, b.`name`, b.mail, GROUP_CONCAT(c.house_or_brand_id)
  484. time_range = self.get_time_range(task_key)
  485. all_time_rang = self.get_time_range(4)
  486. # 有限时间范围内的数据
  487. xcx_top_data = self.xcx_top(time_range)
  488. brand_top_data = self.brand_top(time_range)
  489. customer_channel_details_data = self.customer_channel_details(time_range)
  490. brand_customer_channel_details = self.brand_customer_channel_details(time_range)
  491. # 所有历史数据
  492. xcx_top_data_all = self.xcx_top(all_time_rang)
  493. brand_top_data_all = self.brand_top(all_time_rang)
  494. for customer in customers:
  495. customer_data = {}
  496. name = customer[3]
  497. mail = customer[4]
  498. customer_type = customer[2]
  499. house_ids = []
  500. brand_id_list = []
  501. if customer_type == 1:
  502. # 项目
  503. ids = customer[5]
  504. if str(ids).find(',') != -1:
  505. house_ids = [x for x in str(ids).split(',')]
  506. else:
  507. house_ids = [ids]
  508. brand_id_list = self.get_brand_ids_by_house_ids(house_ids)
  509. pass
  510. elif customer_type == 2:
  511. # 集团
  512. brand_ids = customer[5]
  513. if str(brand_ids).find(',') != -1:
  514. brands = [x for x in str(brand_ids).split(',')]
  515. else:
  516. brands = [brand_ids]
  517. for id in brands:
  518. house_ids.extend([x[0] for x in self.get_house_ids_by_brand_id(id)])
  519. brand_id_list = brands
  520. result_data_1 = []
  521. result_data_2 = []
  522. result_data_3 = []
  523. result_data_4 = []
  524. result_data_5 = []
  525. result_data_7 = []
  526. result_data_8 = []
  527. all_house_ids = self.get_house_ids_by_brand_ids(brand_id_list)
  528. xcx_top_data_part = self.filter_by_house_ids(xcx_top_data, all_house_ids)
  529. brand_top_data_part = self.filter_by_brand_ids(brand_top_data, brand_id_list)
  530. xcx_top_data_all_part = self.filter_by_house_ids(xcx_top_data_all, all_house_ids)
  531. brand_top_data_all_part = self.filter_by_brand_ids(brand_top_data_all, brand_id_list)
  532. # 1 数据总览 12个统计指标
  533. data_overview = self.data_overview(time_range, house_ids, xcx_top_data_part, brand_top_data_part)
  534. result_data_1.extend(data_overview)
  535. # 4:单个项目小程序数据排行榜
  536. # 排名 项目名称 总浏览量 总浏览人数 新增获客 新增获电
  537. for index, x in enumerate(xcx_top_data_part):
  538. if x[0] in house_ids:
  539. result_data_4.append([index, x[1], x[2], x[3], x[4], x[5]])
  540. # 5: 集团项目数据排行榜
  541. # 排名 项目名称 总浏览量 总浏览人数 新增获客 新增获电
  542. if customer_type == 2:
  543. for index, x2 in enumerate(brand_top_data_part):
  544. if x2[1] in house_ids or x2[0] in brand_id_list:
  545. result_data_5.append([index, x2[2], x2[3], x2[4], x2[5], x2[6]])
  546. pass
  547. pass
  548. elif customer_type == 1:
  549. for index, x1 in enumerate(brand_top_data_part):
  550. if x1[1] in house_ids:
  551. result_data_5.append([index, x1[2], x1[3], x1[4], x1[5], x1[6]])
  552. else:
  553. pass
  554. # 2: 项目数据排行榜
  555. # 排名 项目名称 总浏览量 总浏览人数 新增获客 新增获电
  556. house_with_brand_data = self.house_with_brand(xcx_top_data_part, brand_top_data_part)
  557. for x in house_with_brand_data:
  558. if x[2] in house_ids:
  559. x.pop(1)
  560. x.pop(1)
  561. result_data_2.append(x)
  562. # 3: 项目历史累计总数
  563. # 排名 项目名称 总浏览量 总浏览人数 新增获客 新增获电
  564. if customer_type == 2:
  565. all_data_history = self.house_with_brand(xcx_top_data_all_part, brand_top_data_all_part, brand_id_list)
  566. for index, x in enumerate(all_data_history):
  567. if x[2] in house_ids or x[1] in brand_id_list:
  568. x.pop(1)
  569. x.pop(1)
  570. result_data_3.append(x)
  571. else:
  572. all_data_history = self.house_with_brand(xcx_top_data_all_part, brand_top_data_all_part)
  573. for index, x in enumerate(all_data_history):
  574. if x[2] in house_ids:
  575. x.pop(1)
  576. x.pop(1)
  577. result_data_3.append(x)
  578. # 7: 单个项目小程序获客来源场景分析
  579. # 项目 合计 长按识别二维码 会话 公众号菜单 公众号文章 小程序历史列表 扫一扫二维码 搜索 相册选取二维码 其他小程序 其他
  580. for x in customer_channel_details_data:
  581. if x[0] in house_ids:
  582. result_data_7.append(x)
  583. # 8: 集团项目获客来源场景分析
  584. if customer_type == 2:
  585. for x in brand_customer_channel_details:
  586. if x[2] in house_ids or x[0] in brand_id_list:
  587. result_data_8.append(x)
  588. elif customer_type == 1:
  589. for x in brand_customer_channel_details:
  590. if x[2] in house_ids:
  591. result_data_8.append(x)
  592. # 6: 项目获客来源场景分析
  593. result_data_6 = self.house_with_brand_for_share(result_data_7, result_data_8)
  594. customer_data[1] = result_data_1
  595. result_data_2.sort(key=lambda obj: obj[0])
  596. customer_data[2] = result_data_2
  597. result_data_3.sort(key=lambda obj: obj[0])
  598. customer_data[3] = result_data_3
  599. result_data_4.sort(key=lambda obj: obj[0])
  600. customer_data[4] = result_data_4
  601. result_data_5.sort(key=lambda obj: obj[0])
  602. customer_data[5] = result_data_5
  603. self.sort(result_data_6, 1)
  604. customer_data[6] = result_data_6
  605. result_data_7_format = []
  606. for x in result_data_7:
  607. house_name = x[1]
  608. ele = [house_name]
  609. data = x[2:]
  610. total = sum(data)
  611. ele.append(total)
  612. ele.extend(data)
  613. result_data_7_format.append(ele)
  614. self.sort(result_data_7_format, 1)
  615. customer_data[7] = result_data_7_format
  616. result_data_8_format = []
  617. for x in result_data_8:
  618. ele = []
  619. house_name = x[3]
  620. data = x[4:]
  621. total = sum(data)
  622. ele.append(house_name)
  623. ele.append(total)
  624. ele.extend(data)
  625. result_data_8_format.append(ele)
  626. self.sort(result_data_8_format, 1)
  627. customer_data[8] = result_data_8_format
  628. customer_data[0] = mail
  629. result[name] = customer_data
  630. # return result
  631. except Exception as e:
  632. result['error'] = str(e)
  633. pass
  634. finally:
  635. return result
  636. def sort(self, data, idnex):
  637. data.sort(key=lambda obj: obj[idnex])
  638. data.reverse()
  639. def house_with_brand_for_share(self, house_data, brand_data):
  640. house_ids = []
  641. result = []
  642. for x in house_data:
  643. if x[0] not in [a[0] for a in house_ids]:
  644. house_ids.append([x[0], x[1]])
  645. for x in brand_data:
  646. if x[2] not in [a[0] for a in house_ids]:
  647. house_ids.append([x[2], x[3]])
  648. for id in house_ids:
  649. house_id = id[0]
  650. house_name = id[1]
  651. house_result = [house_name]
  652. equal_house = self.equal_by_house_id(house_data, house_id, 0)
  653. equal_brand = self.equal_by_house_id(brand_data, house_id, 2)
  654. house_result_part = []
  655. if equal_house and equal_brand:
  656. equal_house = equal_house[2:]
  657. equal_brand = equal_brand[4:]
  658. for i in range(0, 10):
  659. house_result_part.append(self.add(equal_house[i], equal_brand[i]))
  660. elif equal_house and not equal_brand:
  661. equal_house = equal_house[2:]
  662. for i in range(0, 10):
  663. house_result_part.append(equal_house[i])
  664. elif not equal_house and equal_brand:
  665. equal_brand = equal_brand[4:]
  666. for i in range(0, 10):
  667. house_result_part.append(equal_brand[i])
  668. house_result.append(sum(house_result_part))
  669. house_result.extend(house_result_part)
  670. result.append(house_result)
  671. result.sort(key=lambda obj: obj[1])
  672. result.reverse()
  673. return result
  674. def equal_by_house_id(self, data, house_id, index):
  675. for x in data:
  676. if x[index] == house_id:
  677. return x
  678. def filter_by_brand_ids(self, data, brand_ids):
  679. result = []
  680. for x in data:
  681. if x[0] in brand_ids:
  682. result.append(x)
  683. result.sort(key=lambda obj: obj[3])
  684. result.reverse()
  685. return result
  686. def filter_by_house_ids(self, data, house_ids):
  687. result = []
  688. for x in data:
  689. if x[0] in house_ids:
  690. result.append(x)
  691. result.sort(key=lambda obj: obj[2])
  692. result.reverse()
  693. return result
  694. def data_overview(self, time_range, house_ids, xcx_top_data, brand_top_data):
  695. """
  696. 统计数据总览
  697. :param time_range:
  698. :param house_ids:
  699. :return:
  700. """
  701. result = []
  702. # 1:总浏览量
  703. data_1_1 = self.db.select(self.sql_1_1, [time_range[0], time_range[1], house_ids])
  704. number_1_1 = data_1_1[0][0]
  705. data_1_2 = self.db.select(self.sql_1_2, [time_range[0], time_range[1], house_ids])
  706. number_1_2 = data_1_2[0][0]
  707. number_1 = self.add(number_1_1, number_1_2)
  708. result.append(number_1)
  709. # 2: 总浏览人数
  710. 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])
  711. number_2 = data_2[0][0]
  712. result.append(number_2)
  713. # 3:新增获客
  714. data_3 = self.db.select(self.sql_1_4, [time_range[0], time_range[1], house_ids, time_range[0], time_range[1], house_ids])
  715. number_3 = data_3[0][0]
  716. result.append(number_3)
  717. # 4:新增获电
  718. data_4 = self.db.select(self.sql_1_5, [time_range[0], time_range[1], house_ids, house_ids, time_range[0], time_range[1]])
  719. number_4 = data_4[0][0]
  720. result.append(number_4)
  721. xcx_top_data_part = [x for x in xcx_top_data if x[0] in house_ids]
  722. brand_top_data_part = [x for x in brand_top_data if x[1] in house_ids]
  723. # 5 6 7 8
  724. number_5 = 0
  725. number_6 = 0
  726. number_7 = 0
  727. number_8 = 0
  728. for x in brand_top_data_part:
  729. number_5 = self.add(number_5, x[3])
  730. number_6 = self.add(number_6, x[4])
  731. number_7 = self.add(number_7, x[5])
  732. number_8 = self.add(number_8, x[6])
  733. result.append(number_5)
  734. result.append(number_6)
  735. result.append(number_7)
  736. result.append(number_8)
  737. # 9 10 11 12
  738. number_9 = 0
  739. number_10 = 0
  740. number_11 = 0
  741. number_12 = 0
  742. for x in xcx_top_data_part:
  743. number_9 = self.add(number_9, x[2])
  744. number_10 = self.add(number_10, x[3])
  745. number_11 = self.add(number_11, x[4])
  746. number_12 = self.add(number_12, x[5])
  747. result.append(number_9)
  748. result.append(number_10)
  749. result.append(number_11)
  750. result.append(number_12)
  751. return result
  752. def house_with_brand(self, xcx_top_data, brand_top_data, brands=None):
  753. """
  754. 项目数据和集团数据的求和
  755. :param brands:
  756. :param xcx_top_data:
  757. :param brand_top_data:
  758. :return:
  759. """
  760. result = []
  761. house_ids = []
  762. for x in xcx_top_data:
  763. if x[0] not in house_ids:
  764. house_ids.append(x[0])
  765. for x in brand_top_data:
  766. if x[1] not in house_ids and x[1] is not None and int(x[1]) > 300:
  767. house_ids.append(x[1])
  768. for house_id in house_ids:
  769. a = []
  770. a_order = 0
  771. for index, x in enumerate(xcx_top_data):
  772. if str(house_id) == str(x[0]):
  773. a.extend(x)
  774. a_order = index + 1
  775. b = []
  776. b_order = 0
  777. for index, y in enumerate(brand_top_data):
  778. if str(house_id) == str(y[1]):
  779. b.extend(y)
  780. b_order = index + 1
  781. order = b_order if b_order > 0 else a_order
  782. if len(a) > 0 and len(b) > 0:
  783. 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])])
  784. elif len(a) > 0 and len(b) == 0:
  785. _a = [order, 1]
  786. for x in a:
  787. _a.append(x)
  788. result.append(_a)
  789. elif len(a) == 0 and len(b) > 0:
  790. _b = [order]
  791. _b.extend(b)
  792. result.append(_b)
  793. else:
  794. pass
  795. if brands:
  796. for index, x in enumerate(brand_top_data):
  797. if x[0] in brands and x[1] is not None and int(x[1]) < 300:
  798. _x = [index]
  799. _x.extend(x)
  800. result.append(_x)
  801. result.sort(key=lambda obj: obj[3])
  802. result.reverse()
  803. return result
  804. def add(self, a=None, b=None):
  805. if a and b:
  806. return a + b
  807. elif a and not b:
  808. return a
  809. elif b and not a:
  810. return b
  811. return 0
  812. def xcx_top(self, time_range):
  813. """
  814. 获取 1.默认值/001_大麦/项目排行榜/小程序排行榜TOP_N
  815. :return:
  816. """
  817. params = []
  818. params.extend(time_range)
  819. params.extend(time_range)
  820. params.extend(time_range)
  821. params.extend(time_range)
  822. xcx_top_data = self.db.select(self.sql_2_1, params)
  823. result = []
  824. for x in xcx_top_data:
  825. result.append([n for n in x])
  826. # xcx_top_data的结果结构
  827. # 0 a.house_id, 项目id
  828. # 1 a.house_name, 项目名称
  829. # 2 SUM(a.pv), 浏览总量
  830. # 3 SUM(a.uv), 浏览人数
  831. # 4 SUM(a.new_cust_num), 新增获客
  832. # 5 SUM(a.wx_num) 授权手机号
  833. result.sort(key=lambda obj: obj[2])
  834. result.reverse()
  835. return result
  836. def brand_top(self, time_range):
  837. """
  838. 2.默认值/006_大麦(集团)/集团项目排行榜v1.3/集团排行榜
  839. :param task_key:
  840. :return:
  841. """
  842. params = [time_range[0], time_range[1]]
  843. brand_top_data = self.db.select(self.sql_2_2, params)
  844. result = []
  845. for x in brand_top_data:
  846. result.append([n for n in x])
  847. # brand_top_data结果的结构
  848. # 0 a.brand_id, 集团id
  849. # 1 a.house_id, 项目id
  850. # 2 a.house_name, 项目名称
  851. # 3 SUM(a.pv), 浏览总量
  852. # 4 SUM(a.uv), 浏览人数
  853. # 5 SUM(a.new_cust), 新增获客
  854. # 6 SUM(a.shouquan_cust) 授权手机号
  855. result.sort(key=lambda obj: obj[3])
  856. result.reverse()
  857. return result
  858. """
  859. 数据分享类别
  860. 1:长按识别二维码 2:会话 3:公众号菜单 4:公众号文章 5:小程序历史列表 6:扫一扫二维码
  861. 7:搜索 8:相册选取二维码 9:其他小程序 10:其他
  862. """
  863. share_way = {
  864. "长按识别二维码": 1,
  865. "会话": 2,
  866. "公众号菜单": 3,
  867. '公众号文章': 4,
  868. '小程序历史列表': 5,
  869. '扫一扫二维码': 6,
  870. '搜索': 7,
  871. '相册选取二维码': 8,
  872. '其他小程序': 9,
  873. '其他': 10
  874. }
  875. def customer_channel_details(self, time_range):
  876. """
  877. 1.默认值/001_大麦/场景_用户来源渠道/用户来源渠道—明细
  878. :param task_key:
  879. :return:
  880. """
  881. params = []
  882. params.extend(time_range)
  883. customer_channel_details_data = self.db.select(self.sql_3_1, params)
  884. result = []
  885. for x in customer_channel_details_data:
  886. ele = []
  887. order = self.share_way.get(x[2])
  888. if order:
  889. ele.append(x[0])
  890. ele.append(x[1])
  891. ele.append(order)
  892. ele.append(x[3])
  893. result.append(ele)
  894. result.sort(key=lambda obj: obj[0])
  895. end_data = []
  896. for key, data in groupby(result, key=lambda obj: obj[0]):
  897. others_data = []
  898. for ot in data:
  899. others_data.append([x for x in ot])
  900. lable_data = []
  901. if len(others_data) > 0:
  902. lable_data.append(others_data[0][0])
  903. lable_data.append(others_data[0][1])
  904. for i in range(1, 11):
  905. number = 0
  906. for od in others_data:
  907. if i == od[2]:
  908. number = od[3]
  909. else:
  910. pass
  911. lable_data.append(number)
  912. pass
  913. end_data.append(lable_data)
  914. # customer_channel_details_data数据结构
  915. # house_id, 项目id
  916. # house_name, 项目名称
  917. # label_wx, 分享类别
  918. # COUNT(a.id) as counts, 数量
  919. return end_data
  920. def brand_customer_channel_details(self, time_range):
  921. """
  922. 2.默认值/006_大麦(集团)/场景(集团)_用户来源渠道_v1.1/用户来源渠道—明细
  923. :param frequency:
  924. :return:
  925. """
  926. params = [time_range[0], time_range[1], time_range[0], time_range[1]]
  927. brand_customer_channel_details_data = self.db.select(self.sql_3_2, params)
  928. # brand_customer_channel_details_data数据结构
  929. # 0 brand_id, 集团id
  930. # 1 x.brand_name, 集团名称
  931. # 2 house_id, 项目id
  932. # 3 house_name, 项目名称
  933. # 4 label_wx, 分享类别
  934. # 5 COUNT(1) 数量
  935. result = []
  936. for x in brand_customer_channel_details_data:
  937. ele = []
  938. order = self.share_way.get(x[4])
  939. if order:
  940. ele.append(x[0])
  941. ele.append(x[1])
  942. ele.append(x[2])
  943. ele.append(x[3])
  944. ele.append(order)
  945. ele.append(x[5])
  946. result.append(ele)
  947. result.sort(key=lambda obj: obj[2])
  948. end_data = []
  949. for key, data in groupby(result, key=lambda obj: obj[2]):
  950. others_data = []
  951. for ot in data:
  952. others_data.append([x for x in ot])
  953. lable_data = []
  954. if len(others_data) > 0:
  955. lable_data.extend(others_data[0][0: 4])
  956. for i in range(1, 11):
  957. number = 0
  958. for od in others_data:
  959. if i == od[4]:
  960. number = od[5]
  961. else:
  962. pass
  963. lable_data.append(number)
  964. pass
  965. end_data.append(lable_data)
  966. return end_data
  967. def push_log_recording(self, push_message):
  968. """
  969. 报表推送日志记录
  970. :param push_message:
  971. :return:
  972. """
  973. self.db.add_some(self.sql_6, push_message)
  974. def get_house_ids_by_brand_id(self, brand_id):
  975. return self.db.select(self.sql_5, [brand_id])
  976. def get_brand_info_by_house_id(self, house_id):
  977. """
  978. 根据项目id或者相应的集团信息
  979. :param house_id:
  980. :return:
  981. """
  982. brand_info = self.db.select(self.sql_7, [house_id])
  983. if len(brand_info) == 1:
  984. return brand_info[0][0]
  985. return
  986. def get_brand_ids_by_house_ids(self, house_ids):
  987. brand_ids = self.db.select(self.sql_8, [house_ids])
  988. ids = []
  989. for x in brand_ids:
  990. if x and x[0]:
  991. ids.append(x[0])
  992. return ids
  993. def get_house_ids_by_brand_ids(self, brand_ids):
  994. result = []
  995. ids = self.db.select(self.sql_5_1, [brand_ids])
  996. for x in ids:
  997. if x[0] not in result:
  998. result.append(x[0])
  999. return result
  1000. def get_time_range(self, task_key):
  1001. """
  1002. 根据定时任务id获取时间区间
  1003. 时间格式 yyyy-mm-dd
  1004. :param task_key:1: 日报,2:周报, 3:all
  1005. :return:
  1006. """
  1007. now_time = datetime.datetime.now()
  1008. pre_time = None
  1009. if task_key in (2, 3):
  1010. # 上周,上周一到上周天
  1011. pre_time = now_time + datetime.timedelta(days=-7)
  1012. now_time = now_time + datetime.timedelta(days=-1)
  1013. pass
  1014. elif task_key in (1, 1):
  1015. # 昨天
  1016. pre_time = now_time + datetime.timedelta(days=-1)
  1017. now_time = now_time + datetime.timedelta(days=-1)
  1018. pass
  1019. elif task_key in (4, 4):
  1020. # 不限时间
  1021. pre_time = now_time + datetime.timedelta(days=-2999)
  1022. return [pre_time.strftime('%Y-%m-%d'), now_time.strftime('%Y-%m-%d')]
  1023. if __name__ == '__main__':
  1024. rp = ReportPush('linshi')
  1025. print(rp.get_time_range(2))