report_push.py 39 KB

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