KuaishouOperationPerformanceBackChargeBase.py 81 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048104910501051105210531054105510561057105810591060106110621063106410651066106710681069107010711072107310741075107610771078107910801081108210831084108510861087108810891090109110921093109410951096109710981099110011011102110311041105110611071108110911101111111211131114111511161117111811191120112111221123112411251126112711281129113011311132113311341135113611371138113911401141114211431144114511461147114811491150115111521153115411551156115711581159116011611162116311641165116611671168116911701171
  1. # Author renyupeng
  2. # coding=utf-8
  3. # @Time : 2022/9/19 5:11 下午
  4. # @Site :
  5. # @File : KuaishouOperationPerformanceBackChargeBase.py
  6. # @Software: PyCharm
  7. # @contact: renyupeng@c-top.com.cn
  8. # @Tel 1501435553
  9. import logging
  10. from Apietl.conn.TidbProConn import TidbProConn
  11. class KuaishouOperationPerformanceBackChargeBase:
  12. def __init__(self):
  13. self.conn = TidbProConn.get_connection()
  14. def handler(self):
  15. start_date_sql = """select concat(date_format(date_sub(now(), interval 1 month),'%Y%m'),'01')"""
  16. end_date_sql = """select date_format( last_day(date_format(date_sub(now(), interval 1 month),'%Y%m%d')),
  17. '%Y%m%d') """
  18. start_date = TidbProConn.quary(self.conn, start_date_sql)[0][0]
  19. end_date = TidbProConn.quary(self.conn, end_date_sql)[0][0]
  20. try:
  21. sql = """
  22. replace into application.kuaishou_operation_performance_back_charge_base
  23. select stat_date,
  24. company_id,
  25. company_name,
  26. t.project_id,
  27. project_name,
  28. user.id as user_id,
  29. user.realname as user_name,
  30. leader.id as leader_id,
  31. leader.realname leader_name,
  32. account_id,
  33. case
  34. when user.status = 1 then '在职'
  35. else '离职' end as user_status,
  36. round(contract_rebate_real_charged_in_yuan, 2) as contract_rebate_real_charged_in_yuan,
  37. round(direct_rebate_real_charged_in_yuan, 2) as direct_rebate_real_charged_in_yuan
  38. from (
  39. select stat_date,
  40. account_id,
  41. contract_rebate_real_charged_in_yuan,
  42. direct_rebate_real_charged_in_yuan,
  43. transferor_one,
  44. project_id,
  45. project_name,
  46. company_id,
  47. company_name
  48. from (
  49. select stat_date,
  50. account_id,
  51. contract_rebate_real_charged_in_yuan,
  52. direct_rebate_real_charged_in_yuan,
  53. transferor_one,
  54. project_id,
  55. project_name,
  56. company_id,
  57. company_name
  58. from (
  59. select report.account_id as account_id,
  60. contract_rebate_real_charged_in_yuan,
  61. direct_rebate_real_charged_in_yuan,
  62. stat_date,
  63. user_id,
  64. b.transferor_one,
  65. transferor_date_one,
  66. assignee_one,
  67. assignee_date_one,
  68. project_id,
  69. project_name,
  70. company_id,
  71. company_name
  72. from (select account_id,
  73. contract_rebate_real_charged_in_yuan,
  74. direct_rebate_real_charged_in_yuan,
  75. stat_date,
  76. user_id,
  77. project_id,
  78. project_name,
  79. company_id,
  80. company_name
  81. from application.app_kuaishou_agent_report_d
  82. where stat_date between {start_date} and {enddate}) report
  83. left join(
  84. select account_id,
  85. transferor_one,
  86. if(transferor_one = transferor_two, '', transferor_two) as transferor_two,
  87. if(transferor_one = transferor_three, '', transferor_three) as transferor_three,
  88. transferor_date_one,
  89. if(transferor_date_one = transferor_date_two, '', transferor_date_two) transferor_date_two,
  90. if(transferor_date_one = transferor_date_three, '',
  91. transferor_date_three) as transferor_date_three,
  92. assignee_one,
  93. if(assignee_one = assignee_two, '', assignee_two) assignee_two,
  94. if(assignee_two = assignee_three, '', assignee_three) assignee_three,
  95. assignee_date_one,
  96. if(assignee_date_one = assignee_date_two, '', assignee_date_two) assignee_date_two,
  97. if(assignee_date_one = assignee_date_three, '', assignee_date_three) assignee_date_three
  98. from (
  99. select account_id,
  100. substring_index(transferor, ':', 1) as transferor_one,
  101. replace(
  102. substring_index(substring_index(transferor, ':', 2), ':', -1),
  103. ',',
  104. '') as transferor_two,
  105. replace(
  106. substring_index(substring_index(transferor, ':', 3), ':', -1),
  107. ',',
  108. '') as transferor_three,
  109. replace(substring_index(transferor_date, ':', 1), ',', '') as transferor_date_one,
  110. replace(
  111. substring_index(substring_index(transferor_date, ':', 2), ':', -1),
  112. ',',
  113. '') as transferor_date_two,
  114. replace(
  115. substring_index(substring_index(transferor_date, ':', 3), ':', -1),
  116. ',',
  117. '') as transferor_date_three,
  118. replace(substring_index(assignee, ':', 1), ',', '') as assignee_one,
  119. replace(
  120. substring_index(substring_index(assignee, ':', 2), ':', -1),
  121. ',',
  122. '') as assignee_two,
  123. replace(
  124. substring_index(substring_index(assignee, ':', 3), ':', -1),
  125. ',',
  126. '') as assignee_three,
  127. substring_index(assignee_date, ':', 1) as assignee_date_one,
  128. replace(
  129. substring_index(substring_index(assignee_date, ':', 2), ':', -1),
  130. ',',
  131. '') as assignee_date_two,
  132. replace(
  133. substring_index(substring_index(assignee_date, ':', 3), ':', -1),
  134. ',',
  135. '') as assignee_date_three
  136. from (
  137. select account_id,
  138. group_concat(transferor, ':') as transferor,
  139. group_concat(transferor_date, ':') as transferor_date,
  140. group_concat(assignee, ':') as assignee,
  141. group_concat(assignee_date, ':') as assignee_date
  142. from (select account_id,transferor,transferor_date,assignee,assignee_date from
  143. `jeecg-boot`.ctop_transfer_account
  144. where transferor_date between date_format({start_date},'%Y-%m-%d') and date_format(date_sub({enddate},interval -2 day),'%Y-%m-%d') and status != 2
  145. and status != 2
  146. order by transferor_date
  147. )t
  148. group by account_id) t
  149. ) t
  150. ) b
  151. on report.account_id = b.account_id
  152. where b.account_id is not null
  153. and transferor_date_two = ''
  154. order by stat_date) t
  155. where stat_date >= {start_date}
  156. and stat_date <= date_format(transferor_date_one, '%Y%m%d')
  157. union all
  158. select stat_date,
  159. account_id,
  160. contract_rebate_real_charged_in_yuan,
  161. direct_rebate_real_charged_in_yuan,
  162. assignee_one,
  163. project_id,
  164. project_name,
  165. company_id,
  166. company_name
  167. from (
  168. select report.account_id as account_id,
  169. contract_rebate_real_charged_in_yuan,
  170. direct_rebate_real_charged_in_yuan,
  171. stat_date,
  172. user_id,
  173. b.transferor_one,
  174. transferor_date_one,
  175. assignee_one,
  176. assignee_date_one,
  177. project_id,
  178. project_name,
  179. company_id,
  180. company_name
  181. from (select account_id,
  182. contract_rebate_real_charged_in_yuan,
  183. direct_rebate_real_charged_in_yuan,
  184. stat_date,
  185. user_id,
  186. project_id,
  187. project_name,
  188. company_id,
  189. company_name
  190. from application.app_kuaishou_agent_report_d
  191. where stat_date between {start_date} and {enddate}) report
  192. left join(
  193. select account_id,
  194. transferor_one,
  195. if(transferor_one = transferor_two, '', transferor_two) as transferor_two,
  196. if(transferor_one = transferor_three, '', transferor_three) as transferor_three,
  197. transferor_date_one,
  198. if(transferor_date_one = transferor_date_two, '', transferor_date_two) transferor_date_two,
  199. if(transferor_date_one = transferor_date_three, '',
  200. transferor_date_three) as transferor_date_three,
  201. assignee_one,
  202. if(assignee_one = assignee_two, '', assignee_two) assignee_two,
  203. if(assignee_two = assignee_three, '', assignee_three) assignee_three,
  204. assignee_date_one,
  205. if(assignee_date_one = assignee_date_two, '', assignee_date_two) assignee_date_two,
  206. if(assignee_date_one = assignee_date_three, '', assignee_date_three) assignee_date_three
  207. from (
  208. select account_id,
  209. substring_index(transferor, ':', 1) as transferor_one,
  210. replace(
  211. substring_index(substring_index(transferor, ':', 2), ':', -1),
  212. ',',
  213. '') as transferor_two,
  214. replace(
  215. substring_index(substring_index(transferor, ':', 3), ':', -1),
  216. ',',
  217. '') as transferor_three,
  218. replace(substring_index(transferor_date, ':', 1), ',', '') as transferor_date_one,
  219. replace(
  220. substring_index(substring_index(transferor_date, ':', 2), ':', -1),
  221. ',',
  222. '') as transferor_date_two,
  223. replace(
  224. substring_index(substring_index(transferor_date, ':', 3), ':', -1),
  225. ',',
  226. '') as transferor_date_three,
  227. replace(substring_index(assignee, ':', 1), ',', '') as assignee_one,
  228. replace(
  229. substring_index(substring_index(assignee, ':', 2), ':', -1),
  230. ',',
  231. '') as assignee_two,
  232. replace(
  233. substring_index(substring_index(assignee, ':', 3), ':', -1),
  234. ',',
  235. '') as assignee_three,
  236. substring_index(assignee_date, ':', 1) as assignee_date_one,
  237. replace(
  238. substring_index(substring_index(assignee_date, ':', 2), ':', -1),
  239. ',',
  240. '') as assignee_date_two,
  241. replace(
  242. substring_index(substring_index(assignee_date, ':', 3), ':', -1),
  243. ',',
  244. '') as assignee_date_three
  245. from (
  246. select account_id,
  247. group_concat(transferor, ':') as transferor,
  248. group_concat(transferor_date, ':') as transferor_date,
  249. group_concat(assignee, ':') as assignee,
  250. group_concat(assignee_date, ':') as assignee_date
  251. from (select account_id,transferor,transferor_date,assignee,assignee_date from
  252. `jeecg-boot`.ctop_transfer_account
  253. where transferor_date between date_format({start_date},'%Y-%m-%d') and date_format(date_sub({enddate},interval -2 day),'%Y-%m-%d') and status != 2
  254. and status != 2
  255. order by transferor_date
  256. )t
  257. group by account_id) t
  258. ) t
  259. ) b
  260. on report.account_id = b.account_id
  261. where b.account_id is not null
  262. and transferor_date_two = ''
  263. order by stat_date) t
  264. where stat_date > date_format(transferor_date_one, '%Y%m%d')
  265. and stat_date <= {enddate}
  266. union all
  267. -- 二次转户
  268. select stat_date,
  269. account_id,
  270. contract_rebate_real_charged_in_yuan,
  271. direct_rebate_real_charged_in_yuan,
  272. transferor_one,
  273. project_id,
  274. project_name,
  275. company_id,
  276. company_name
  277. from (
  278. select report.account_id as account_id,
  279. contract_rebate_real_charged_in_yuan,
  280. direct_rebate_real_charged_in_yuan,
  281. stat_date,
  282. user_id,
  283. transferor_one,
  284. transferor_date_one,
  285. transferor_two,
  286. transferor_date_two,
  287. assignee_one,
  288. assignee_date_one,
  289. assignee_two,
  290. assignee_date_two,
  291. project_id,
  292. project_name,
  293. company_id,
  294. company_name
  295. from (select account_id,
  296. contract_rebate_real_charged_in_yuan,
  297. direct_rebate_real_charged_in_yuan,
  298. stat_date,
  299. user_id,
  300. project_id,
  301. project_name,
  302. company_id,
  303. company_name
  304. from application.app_kuaishou_agent_report_d
  305. where stat_date between {start_date} and {enddate}) report
  306. left join(
  307. select account_id,
  308. transferor_one,
  309. if(transferor_one = transferor_two, '', transferor_two) as transferor_two,
  310. if(transferor_one = transferor_three, '', transferor_three) as transferor_three,
  311. transferor_date_one,
  312. if(transferor_date_one = transferor_date_two, '', transferor_date_two) transferor_date_two,
  313. if(transferor_date_one = transferor_date_three, '',
  314. transferor_date_three) as transferor_date_three,
  315. assignee_one,
  316. if(assignee_one = assignee_two, '', assignee_two) assignee_two,
  317. if(assignee_two = assignee_three, '', assignee_three) assignee_three,
  318. assignee_date_one,
  319. if(assignee_date_one = assignee_date_two, '', assignee_date_two) assignee_date_two,
  320. if(assignee_date_one = assignee_date_three, '', assignee_date_three) assignee_date_three
  321. from (
  322. select account_id,
  323. substring_index(transferor, ':', 1) as transferor_one,
  324. replace(
  325. substring_index(substring_index(transferor, ':', 2), ':', -1),
  326. ',',
  327. '') as transferor_two,
  328. replace(
  329. substring_index(substring_index(transferor, ':', 3), ':', -1),
  330. ',',
  331. '') as transferor_three,
  332. replace(substring_index(transferor_date, ':', 1), ',', '') as transferor_date_one,
  333. replace(
  334. substring_index(substring_index(transferor_date, ':', 2), ':', -1),
  335. ',',
  336. '') as transferor_date_two,
  337. replace(
  338. substring_index(substring_index(transferor_date, ':', 3), ':', -1),
  339. ',',
  340. '') as transferor_date_three,
  341. replace(substring_index(assignee, ':', 1), ',', '') as assignee_one,
  342. replace(
  343. substring_index(substring_index(assignee, ':', 2), ':', -1),
  344. ',',
  345. '') as assignee_two,
  346. replace(
  347. substring_index(substring_index(assignee, ':', 3), ':', -1),
  348. ',',
  349. '') as assignee_three,
  350. substring_index(assignee_date, ':', 1) as assignee_date_one,
  351. replace(
  352. substring_index(substring_index(assignee_date, ':', 2), ':', -1),
  353. ',',
  354. '') as assignee_date_two,
  355. replace(
  356. substring_index(substring_index(assignee_date, ':', 3), ':', -1),
  357. ',',
  358. '') as assignee_date_three
  359. from (
  360. select account_id,
  361. group_concat(transferor, ':') as transferor,
  362. group_concat(transferor_date, ':') as transferor_date,
  363. group_concat(assignee, ':') as assignee,
  364. group_concat(assignee_date, ':') as assignee_date
  365. from (select account_id,transferor,transferor_date,assignee,assignee_date from
  366. `jeecg-boot`.ctop_transfer_account
  367. where transferor_date between date_format({start_date},'%Y-%m-%d') and date_format(date_sub({enddate},interval -2 day),'%Y-%m-%d') and status != 2
  368. and status != 2
  369. order by transferor_date
  370. )t
  371. group by account_id) t
  372. ) t
  373. ) b
  374. on report.account_id = b.account_id
  375. where b.account_id is not null
  376. and transferor_date_two != ''
  377. and transferor_date_three = ''
  378. order by stat_date) t
  379. where stat_date >= {start_date}
  380. and stat_date <= date_format(transferor_date_one, '%Y%m%d')
  381. union all
  382. select stat_date,
  383. account_id,
  384. contract_rebate_real_charged_in_yuan,
  385. direct_rebate_real_charged_in_yuan,
  386. transferor_two,
  387. project_id,
  388. project_name,
  389. company_id,
  390. company_name
  391. from (
  392. select report.account_id as account_id,
  393. contract_rebate_real_charged_in_yuan,
  394. direct_rebate_real_charged_in_yuan,
  395. stat_date,
  396. user_id,
  397. transferor_one,
  398. transferor_date_one,
  399. transferor_two,
  400. transferor_date_two,
  401. assignee_one,
  402. assignee_date_one,
  403. assignee_two,
  404. assignee_date_two,
  405. project_id,
  406. project_name,
  407. company_id,
  408. company_name
  409. from (select account_id,
  410. contract_rebate_real_charged_in_yuan,
  411. direct_rebate_real_charged_in_yuan,
  412. stat_date,
  413. user_id,
  414. project_id,
  415. project_name,
  416. company_id,
  417. company_name
  418. from application.app_kuaishou_agent_report_d
  419. where stat_date between {start_date} and {enddate}) report
  420. left join(
  421. select account_id,
  422. transferor_one,
  423. if(transferor_one = transferor_two, '', transferor_two) as transferor_two,
  424. if(transferor_one = transferor_three, '', transferor_three) as transferor_three,
  425. transferor_date_one,
  426. if(transferor_date_one = transferor_date_two, '', transferor_date_two) transferor_date_two,
  427. if(transferor_date_one = transferor_date_three, '',
  428. transferor_date_three) as transferor_date_three,
  429. assignee_one,
  430. if(assignee_one = assignee_two, '', assignee_two) assignee_two,
  431. if(assignee_two = assignee_three, '', assignee_three) assignee_three,
  432. assignee_date_one,
  433. if(assignee_date_one = assignee_date_two, '', assignee_date_two) assignee_date_two,
  434. if(assignee_date_one = assignee_date_three, '', assignee_date_three) assignee_date_three
  435. from (
  436. select account_id,
  437. substring_index(transferor, ':', 1) as transferor_one,
  438. replace(
  439. substring_index(substring_index(transferor, ':', 2), ':', -1),
  440. ',',
  441. '') as transferor_two,
  442. replace(
  443. substring_index(substring_index(transferor, ':', 3), ':', -1),
  444. ',',
  445. '') as transferor_three,
  446. replace(substring_index(transferor_date, ':', 1), ',', '') as transferor_date_one,
  447. replace(
  448. substring_index(substring_index(transferor_date, ':', 2), ':', -1),
  449. ',',
  450. '') as transferor_date_two,
  451. replace(
  452. substring_index(substring_index(transferor_date, ':', 3), ':', -1),
  453. ',',
  454. '') as transferor_date_three,
  455. replace(substring_index(assignee, ':', 1), ',', '') as assignee_one,
  456. replace(
  457. substring_index(substring_index(assignee, ':', 2), ':', -1),
  458. ',',
  459. '') as assignee_two,
  460. replace(
  461. substring_index(substring_index(assignee, ':', 3), ':', -1),
  462. ',',
  463. '') as assignee_three,
  464. substring_index(assignee_date, ':', 1) as assignee_date_one,
  465. replace(
  466. substring_index(substring_index(assignee_date, ':', 2), ':', -1),
  467. ',',
  468. '') as assignee_date_two,
  469. replace(
  470. substring_index(substring_index(assignee_date, ':', 3), ':', -1),
  471. ',',
  472. '') as assignee_date_three
  473. from (
  474. select account_id,
  475. group_concat(transferor, ':') as transferor,
  476. group_concat(transferor_date, ':') as transferor_date,
  477. group_concat(assignee, ':') as assignee,
  478. group_concat(assignee_date, ':') as assignee_date
  479. from (select account_id,transferor,transferor_date,assignee,assignee_date from
  480. `jeecg-boot`.ctop_transfer_account
  481. where transferor_date between date_format({start_date},'%Y-%m-%d') and date_format(date_sub({enddate},interval -2 day),'%Y-%m-%d') and status != 2
  482. and status != 2
  483. order by transferor_date
  484. )t
  485. group by account_id) t
  486. ) t
  487. ) b
  488. on report.account_id = b.account_id
  489. where b.account_id is not null
  490. and transferor_date_two != ''
  491. and transferor_date_three = ''
  492. order by stat_date) t
  493. where stat_date > date_format(transferor_date_one, '%Y%m%d')
  494. and stat_date <= date_format(transferor_date_two, '%Y%m%d')
  495. union all
  496. select stat_date,
  497. account_id,
  498. contract_rebate_real_charged_in_yuan,
  499. direct_rebate_real_charged_in_yuan,
  500. assignee_two,
  501. project_id,
  502. project_name,
  503. company_id,
  504. company_name
  505. from (
  506. select report.account_id as account_id,
  507. contract_rebate_real_charged_in_yuan,
  508. direct_rebate_real_charged_in_yuan,
  509. stat_date,
  510. user_id,
  511. transferor_one,
  512. transferor_date_one,
  513. transferor_two,
  514. transferor_date_two,
  515. assignee_one,
  516. assignee_date_one,
  517. assignee_two,
  518. assignee_date_two,
  519. project_id,
  520. project_name,
  521. company_id,
  522. company_name
  523. from (select account_id,
  524. contract_rebate_real_charged_in_yuan,
  525. direct_rebate_real_charged_in_yuan,
  526. stat_date,
  527. user_id,
  528. project_id,
  529. project_name,
  530. company_id,
  531. company_name
  532. from application.app_kuaishou_agent_report_d
  533. where stat_date between {start_date} and {enddate}) report
  534. left join(
  535. select account_id,
  536. transferor_one,
  537. if(transferor_one = transferor_two, '', transferor_two) as transferor_two,
  538. if(transferor_one = transferor_three, '', transferor_three) as transferor_three,
  539. transferor_date_one,
  540. if(transferor_date_one = transferor_date_two, '', transferor_date_two) transferor_date_two,
  541. if(transferor_date_one = transferor_date_three, '',
  542. transferor_date_three) as transferor_date_three,
  543. assignee_one,
  544. if(assignee_one = assignee_two, '', assignee_two) assignee_two,
  545. if(assignee_two = assignee_three, '', assignee_three) assignee_three,
  546. assignee_date_one,
  547. if(assignee_date_one = assignee_date_two, '', assignee_date_two) assignee_date_two,
  548. if(assignee_date_one = assignee_date_three, '', assignee_date_three) assignee_date_three
  549. from (
  550. select account_id,
  551. substring_index(transferor, ':', 1) as transferor_one,
  552. replace(
  553. substring_index(substring_index(transferor, ':', 2), ':', -1),
  554. ',',
  555. '') as transferor_two,
  556. replace(
  557. substring_index(substring_index(transferor, ':', 3), ':', -1),
  558. ',',
  559. '') as transferor_three,
  560. replace(substring_index(transferor_date, ':', 1), ',', '') as transferor_date_one,
  561. replace(
  562. substring_index(substring_index(transferor_date, ':', 2), ':', -1),
  563. ',',
  564. '') as transferor_date_two,
  565. replace(
  566. substring_index(substring_index(transferor_date, ':', 3), ':', -1),
  567. ',',
  568. '') as transferor_date_three,
  569. replace(substring_index(assignee, ':', 1), ',', '') as assignee_one,
  570. replace(
  571. substring_index(substring_index(assignee, ':', 2), ':', -1),
  572. ',',
  573. '') as assignee_two,
  574. replace(
  575. substring_index(substring_index(assignee, ':', 3), ':', -1),
  576. ',',
  577. '') as assignee_three,
  578. substring_index(assignee_date, ':', 1) as assignee_date_one,
  579. replace(
  580. substring_index(substring_index(assignee_date, ':', 2), ':', -1),
  581. ',',
  582. '') as assignee_date_two,
  583. replace(
  584. substring_index(substring_index(assignee_date, ':', 3), ':', -1),
  585. ',',
  586. '') as assignee_date_three
  587. from (
  588. select account_id,
  589. group_concat(transferor, ':') as transferor,
  590. group_concat(transferor_date, ':') as transferor_date,
  591. group_concat(assignee, ':') as assignee,
  592. group_concat(assignee_date, ':') as assignee_date
  593. from (select account_id,transferor,transferor_date,assignee,assignee_date from
  594. `jeecg-boot`.ctop_transfer_account
  595. where transferor_date between date_format({start_date},'%Y-%m-%d') and date_format(date_sub({enddate},interval -2 day),'%Y-%m-%d') and status != 2
  596. and status != 2
  597. order by transferor_date
  598. )t
  599. group by account_id) t
  600. ) t
  601. ) b
  602. on report.account_id = b.account_id
  603. where b.account_id is not null
  604. and transferor_date_two != ''
  605. and transferor_date_three = ''
  606. order by stat_date) t
  607. where stat_date > date_format(transferor_date_two, '%Y%m%d')
  608. and stat_date <= {enddate}
  609. -- 三次转户
  610. union all
  611. select stat_date,
  612. account_id,
  613. contract_rebate_real_charged_in_yuan,
  614. direct_rebate_real_charged_in_yuan,
  615. transferor_one,
  616. project_id,
  617. project_name,
  618. company_id,
  619. company_name
  620. from (
  621. select report.account_id as account_id,
  622. contract_rebate_real_charged_in_yuan,
  623. direct_rebate_real_charged_in_yuan,
  624. stat_date,
  625. user_id,
  626. transferor_one,
  627. transferor_date_one,
  628. transferor_two,
  629. transferor_date_two,
  630. assignee_one,
  631. assignee_date_one,
  632. assignee_two,
  633. assignee_date_two,
  634. transferor_three,
  635. transferor_date_three,
  636. project_id,
  637. project_name,
  638. company_id,
  639. company_name
  640. from (select account_id,
  641. contract_rebate_real_charged_in_yuan,
  642. direct_rebate_real_charged_in_yuan,
  643. stat_date,
  644. user_id,
  645. project_id,
  646. project_name,
  647. company_id,
  648. company_name
  649. from application.app_kuaishou_agent_report_d
  650. where stat_date between {start_date} and {enddate}) report
  651. left join(
  652. select account_id,
  653. transferor_one,
  654. if(transferor_one = transferor_two, '', transferor_two) as transferor_two,
  655. if(transferor_one = transferor_three, '', transferor_three) as transferor_three,
  656. transferor_date_one,
  657. if(transferor_date_one = transferor_date_two, '', transferor_date_two) transferor_date_two,
  658. if(transferor_date_one = transferor_date_three, '',
  659. transferor_date_three) as transferor_date_three,
  660. assignee_one,
  661. if(assignee_one = assignee_two, '', assignee_two) assignee_two,
  662. if(assignee_two = assignee_three, '', assignee_three) assignee_three,
  663. assignee_date_one,
  664. if(assignee_date_one = assignee_date_two, '', assignee_date_two) assignee_date_two,
  665. if(assignee_date_one = assignee_date_three, '', assignee_date_three) assignee_date_three
  666. from (
  667. select account_id,
  668. substring_index(transferor, ':', 1) as transferor_one,
  669. replace(
  670. substring_index(substring_index(transferor, ':', 2), ':', -1),
  671. ',',
  672. '') as transferor_two,
  673. replace(
  674. substring_index(substring_index(transferor, ':', 3), ':', -1),
  675. ',',
  676. '') as transferor_three,
  677. replace(substring_index(transferor_date, ':', 1), ',', '') as transferor_date_one,
  678. replace(
  679. substring_index(substring_index(transferor_date, ':', 2), ':', -1),
  680. ',',
  681. '') as transferor_date_two,
  682. replace(
  683. substring_index(substring_index(transferor_date, ':', 3), ':', -1),
  684. ',',
  685. '') as transferor_date_three,
  686. replace(substring_index(assignee, ':', 1), ',', '') as assignee_one,
  687. replace(
  688. substring_index(substring_index(assignee, ':', 2), ':', -1),
  689. ',',
  690. '') as assignee_two,
  691. replace(
  692. substring_index(substring_index(assignee, ':', 3), ':', -1),
  693. ',',
  694. '') as assignee_three,
  695. substring_index(assignee_date, ':', 1) as assignee_date_one,
  696. replace(
  697. substring_index(substring_index(assignee_date, ':', 2), ':', -1),
  698. ',',
  699. '') as assignee_date_two,
  700. replace(
  701. substring_index(substring_index(assignee_date, ':', 3), ':', -1),
  702. ',',
  703. '') as assignee_date_three
  704. from ( select account_id,
  705. group_concat(transferor, ':') as transferor,
  706. group_concat(transferor_date, ':') as transferor_date,
  707. group_concat(assignee, ':') as assignee,
  708. group_concat(assignee_date, ':') as assignee_date
  709. from (select account_id,transferor,transferor_date,assignee,assignee_date from
  710. `jeecg-boot`.ctop_transfer_account
  711. where transferor_date between date_format({start_date},'%Y-%m-%d') and date_format(date_sub({enddate},interval -2 day),'%Y-%m-%d') and status != 2
  712. and status != 2
  713. order by transferor_date
  714. )t
  715. group by account_id) t
  716. ) t
  717. ) b
  718. on report.account_id = b.account_id
  719. where b.account_id is not null
  720. and transferor_date_three != ''
  721. order by stat_date) t
  722. where stat_date >= {start_date}
  723. and stat_date <= date_format(transferor_date_one, '%Y%m%d')
  724. union all
  725. select stat_date,
  726. account_id,
  727. contract_rebate_real_charged_in_yuan,
  728. direct_rebate_real_charged_in_yuan,
  729. transferor_two,
  730. project_id,
  731. project_name,
  732. company_id,
  733. company_name
  734. from (
  735. select report.account_id as account_id,
  736. contract_rebate_real_charged_in_yuan,
  737. direct_rebate_real_charged_in_yuan,
  738. stat_date,
  739. user_id,
  740. transferor_one,
  741. transferor_date_one,
  742. transferor_two,
  743. transferor_date_two,
  744. assignee_one,
  745. assignee_date_one,
  746. assignee_two,
  747. assignee_date_two,
  748. transferor_three,
  749. transferor_date_three,
  750. project_id,
  751. project_name,
  752. company_id,
  753. company_name
  754. from (select account_id,
  755. contract_rebate_real_charged_in_yuan,
  756. direct_rebate_real_charged_in_yuan,
  757. stat_date,
  758. user_id,
  759. project_id,
  760. project_name,
  761. company_id,
  762. company_name
  763. from application.app_kuaishou_agent_report_d
  764. where stat_date between {start_date} and {enddate}) report
  765. left join(
  766. select account_id,
  767. transferor_one,
  768. if(transferor_one = transferor_two, '', transferor_two) as transferor_two,
  769. if(transferor_one = transferor_three, '', transferor_three) as transferor_three,
  770. transferor_date_one,
  771. if(transferor_date_one = transferor_date_two, '', transferor_date_two) transferor_date_two,
  772. if(transferor_date_one = transferor_date_three, '',
  773. transferor_date_three) as transferor_date_three,
  774. assignee_one,
  775. if(assignee_one = assignee_two, '', assignee_two) assignee_two,
  776. if(assignee_two = assignee_three, '', assignee_three) assignee_three,
  777. assignee_date_one,
  778. if(assignee_date_one = assignee_date_two, '', assignee_date_two) assignee_date_two,
  779. if(assignee_date_one = assignee_date_three, '', assignee_date_three) assignee_date_three
  780. from (
  781. select account_id,
  782. substring_index(transferor, ':', 1) as transferor_one,
  783. replace(
  784. substring_index(substring_index(transferor, ':', 2), ':', -1),
  785. ',',
  786. '') as transferor_two,
  787. replace(
  788. substring_index(substring_index(transferor, ':', 3), ':', -1),
  789. ',',
  790. '') as transferor_three,
  791. replace(substring_index(transferor_date, ':', 1), ',', '') as transferor_date_one,
  792. replace(
  793. substring_index(substring_index(transferor_date, ':', 2), ':', -1),
  794. ',',
  795. '') as transferor_date_two,
  796. replace(
  797. substring_index(substring_index(transferor_date, ':', 3), ':', -1),
  798. ',',
  799. '') as transferor_date_three,
  800. replace(substring_index(assignee, ':', 1), ',', '') as assignee_one,
  801. replace(
  802. substring_index(substring_index(assignee, ':', 2), ':', -1),
  803. ',',
  804. '') as assignee_two,
  805. replace(
  806. substring_index(substring_index(assignee, ':', 3), ':', -1),
  807. ',',
  808. '') as assignee_three,
  809. substring_index(assignee_date, ':', 1) as assignee_date_one,
  810. replace(
  811. substring_index(substring_index(assignee_date, ':', 2), ':', -1),
  812. ',',
  813. '') as assignee_date_two,
  814. replace(
  815. substring_index(substring_index(assignee_date, ':', 3), ':', -1),
  816. ',',
  817. '') as assignee_date_three
  818. from (
  819. select account_id,
  820. group_concat(transferor, ':') as transferor,
  821. group_concat(transferor_date, ':') as transferor_date,
  822. group_concat(assignee, ':') as assignee,
  823. group_concat(assignee_date, ':') as assignee_date
  824. from (select account_id,transferor,transferor_date,assignee,assignee_date from
  825. `jeecg-boot`.ctop_transfer_account
  826. where transferor_date between date_format({start_date},'%Y-%m-%d') and date_format(date_sub({enddate},interval -2 day),'%Y-%m-%d') and status != 2
  827. and status != 2
  828. order by transferor_date
  829. )t
  830. group by account_id) t
  831. ) t
  832. ) b
  833. on report.account_id = b.account_id
  834. where b.account_id is not null
  835. and transferor_date_three != ''
  836. order by stat_date) t
  837. where stat_date > date_format(transferor_date_one, '%Y%m%d')
  838. and stat_date <= date_format(transferor_date_two, '%Y%m%d')
  839. union all
  840. select stat_date,
  841. account_id,
  842. contract_rebate_real_charged_in_yuan,
  843. direct_rebate_real_charged_in_yuan,
  844. transferor_three,
  845. project_id,
  846. project_name,
  847. company_id,
  848. company_name
  849. from (
  850. select report.account_id as account_id,
  851. contract_rebate_real_charged_in_yuan,
  852. direct_rebate_real_charged_in_yuan,
  853. stat_date,
  854. user_id,
  855. transferor_one,
  856. transferor_date_one,
  857. transferor_two,
  858. transferor_date_two,
  859. assignee_one,
  860. assignee_date_one,
  861. assignee_two,
  862. assignee_date_two,
  863. transferor_three,
  864. transferor_date_three,
  865. project_id,
  866. project_name,
  867. company_id,
  868. company_name
  869. from (select account_id,
  870. contract_rebate_real_charged_in_yuan,
  871. direct_rebate_real_charged_in_yuan,
  872. stat_date,
  873. user_id,
  874. project_id,
  875. project_name,
  876. company_id,
  877. company_name
  878. from application.app_kuaishou_agent_report_d
  879. where stat_date between {start_date} and {enddate}) report
  880. left join(
  881. select account_id,
  882. transferor_one,
  883. if(transferor_one = transferor_two, '', transferor_two) as transferor_two,
  884. if(transferor_one = transferor_three, '', transferor_three) as transferor_three,
  885. transferor_date_one,
  886. if(transferor_date_one = transferor_date_two, '', transferor_date_two) transferor_date_two,
  887. if(transferor_date_one = transferor_date_three, '',
  888. transferor_date_three) as transferor_date_three,
  889. assignee_one,
  890. if(assignee_one = assignee_two, '', assignee_two) assignee_two,
  891. if(assignee_two = assignee_three, '', assignee_three) assignee_three,
  892. assignee_date_one,
  893. if(assignee_date_one = assignee_date_two, '', assignee_date_two) assignee_date_two,
  894. if(assignee_date_one = assignee_date_three, '', assignee_date_three) assignee_date_three
  895. from (
  896. select account_id,
  897. substring_index(transferor, ':', 1) as transferor_one,
  898. replace(
  899. substring_index(substring_index(transferor, ':', 2), ':', -1),
  900. ',',
  901. '') as transferor_two,
  902. replace(
  903. substring_index(substring_index(transferor, ':', 3), ':', -1),
  904. ',',
  905. '') as transferor_three,
  906. replace(substring_index(transferor_date, ':', 1), ',', '') as transferor_date_one,
  907. replace(
  908. substring_index(substring_index(transferor_date, ':', 2), ':', -1),
  909. ',',
  910. '') as transferor_date_two,
  911. replace(
  912. substring_index(substring_index(transferor_date, ':', 3), ':', -1),
  913. ',',
  914. '') as transferor_date_three,
  915. replace(substring_index(assignee, ':', 1), ',', '') as assignee_one,
  916. replace(
  917. substring_index(substring_index(assignee, ':', 2), ':', -1),
  918. ',',
  919. '') as assignee_two,
  920. replace(
  921. substring_index(substring_index(assignee, ':', 3), ':', -1),
  922. ',',
  923. '') as assignee_three,
  924. substring_index(assignee_date, ':', 1) as assignee_date_one,
  925. replace(
  926. substring_index(substring_index(assignee_date, ':', 2), ':', -1),
  927. ',',
  928. '') as assignee_date_two,
  929. replace(
  930. substring_index(substring_index(assignee_date, ':', 3), ':', -1),
  931. ',',
  932. '') as assignee_date_three
  933. from (
  934. select account_id,
  935. group_concat(transferor, ':') as transferor,
  936. group_concat(transferor_date, ':') as transferor_date,
  937. group_concat(assignee, ':') as assignee,
  938. group_concat(assignee_date, ':') as assignee_date
  939. from (select account_id,transferor,transferor_date,assignee,assignee_date from
  940. `jeecg-boot`.ctop_transfer_account
  941. where transferor_date between date_format({start_date},'%Y-%m-%d') and date_format(date_sub({enddate},interval -2 day),'%Y-%m-%d') and status != 2
  942. and status != 2
  943. order by transferor_date
  944. )t
  945. group by account_id) t
  946. ) t
  947. ) b
  948. on report.account_id = b.account_id
  949. where b.account_id is not null
  950. and transferor_date_three != ''
  951. order by stat_date) t
  952. where stat_date > date_format(transferor_date_two, '%Y%m%d')
  953. and stat_date <= date_format(transferor_date_three, '%Y%m%d')
  954. union all
  955. select stat_date,
  956. account_id,
  957. contract_rebate_real_charged_in_yuan,
  958. direct_rebate_real_charged_in_yuan,
  959. assignee_three,
  960. project_id,
  961. project_name,
  962. company_id,
  963. company_name
  964. from (
  965. select report.account_id as account_id,
  966. contract_rebate_real_charged_in_yuan,
  967. direct_rebate_real_charged_in_yuan,
  968. stat_date,
  969. user_id,
  970. transferor_one,
  971. transferor_date_one,
  972. transferor_two,
  973. transferor_date_two,
  974. assignee_one,
  975. assignee_date_one,
  976. assignee_two,
  977. assignee_date_two,
  978. transferor_three,
  979. transferor_date_three,
  980. project_id,
  981. project_name,
  982. company_id,
  983. company_name,
  984. assignee_three
  985. from (select account_id,
  986. contract_rebate_real_charged_in_yuan,
  987. direct_rebate_real_charged_in_yuan,
  988. stat_date,
  989. user_id,
  990. project_id,
  991. project_name,
  992. company_id,
  993. company_name
  994. from application.app_kuaishou_agent_report_d
  995. where stat_date between {start_date} and {enddate}) report
  996. left join(
  997. select account_id,
  998. transferor_one,
  999. if(transferor_one = transferor_two, '', transferor_two) as transferor_two,
  1000. if(transferor_one = transferor_three, '', transferor_three) as transferor_three,
  1001. transferor_date_one,
  1002. if(transferor_date_one = transferor_date_two, '', transferor_date_two) transferor_date_two,
  1003. if(transferor_date_one = transferor_date_three, '',
  1004. transferor_date_three) as transferor_date_three,
  1005. assignee_one,
  1006. if(assignee_one = assignee_two, '', assignee_two) assignee_two,
  1007. if(assignee_two = assignee_three, '', assignee_three) assignee_three,
  1008. assignee_date_one,
  1009. if(assignee_date_one = assignee_date_two, '', assignee_date_two) assignee_date_two,
  1010. if(assignee_date_one = assignee_date_three, '', assignee_date_three) assignee_date_three
  1011. from (
  1012. select account_id,
  1013. substring_index(transferor, ':', 1) as transferor_one,
  1014. replace(
  1015. substring_index(substring_index(transferor, ':', 2), ':', -1),
  1016. ',',
  1017. '') as transferor_two,
  1018. replace(
  1019. substring_index(substring_index(transferor, ':', 3), ':', -1),
  1020. ',',
  1021. '') as transferor_three,
  1022. replace(substring_index(transferor_date, ':', 1), ',', '') as transferor_date_one,
  1023. replace(
  1024. substring_index(substring_index(transferor_date, ':', 2), ':', -1),
  1025. ',',
  1026. '') as transferor_date_two,
  1027. replace(
  1028. substring_index(substring_index(transferor_date, ':', 3), ':', -1),
  1029. ',',
  1030. '') as transferor_date_three,
  1031. replace(substring_index(assignee, ':', 1), ',', '') as assignee_one,
  1032. replace(
  1033. substring_index(substring_index(assignee, ':', 2), ':', -1),
  1034. ',',
  1035. '') as assignee_two,
  1036. replace(
  1037. substring_index(substring_index(assignee, ':', 3), ':', -1),
  1038. ',',
  1039. '') as assignee_three,
  1040. substring_index(assignee_date, ':', 1) as assignee_date_one,
  1041. replace(
  1042. substring_index(substring_index(assignee_date, ':', 2), ':', -1),
  1043. ',',
  1044. '') as assignee_date_two,
  1045. replace(
  1046. substring_index(substring_index(assignee_date, ':', 3), ':', -1),
  1047. ',',
  1048. '') as assignee_date_three
  1049. from (
  1050. select account_id,
  1051. group_concat(transferor, ':') as transferor,
  1052. group_concat(transferor_date, ':') as transferor_date,
  1053. group_concat(assignee, ':') as assignee,
  1054. group_concat(assignee_date, ':') as assignee_date
  1055. from (select account_id,transferor,transferor_date,assignee,assignee_date from
  1056. `jeecg-boot`.ctop_transfer_account
  1057. where transferor_date between date_format({start_date},'%Y-%m-%d') and date_format(date_sub({enddate},interval -2 day),'%Y-%m-%d') and status != 2
  1058. and status != 2
  1059. order by transferor_date
  1060. )t
  1061. group by account_id) t
  1062. ) t
  1063. ) b
  1064. on report.account_id = b.account_id
  1065. where b.account_id is not null
  1066. and transferor_date_three != ''
  1067. order by stat_date) t
  1068. where stat_date > date_format(transferor_date_three, '%Y%m%d')
  1069. and stat_date <= {enddate}) t
  1070. group by stat_date, account_id, direct_rebate_real_charged_in_yuan,contract_rebate_real_charged_in_yuan
  1071. -- 无转户
  1072. union all
  1073. select stat_date,
  1074. a.account_id,
  1075. contract_rebate_real_charged_in_yuan,
  1076. direct_rebate_real_charged_in_yuan,
  1077. user_id,
  1078. project_id,
  1079. project_name,
  1080. company_id,
  1081. company_name
  1082. from (select stat_date,
  1083. account_id,
  1084. contract_rebate_real_charged_in_yuan,
  1085. direct_rebate_real_charged_in_yuan,
  1086. user_id,
  1087. project_id,
  1088. project_name,
  1089. company_id,
  1090. company_name
  1091. from application.app_kuaishou_agent_report_d
  1092. where stat_date between {start_date} and {enddate}) a
  1093. left join
  1094. (
  1095. select account_id
  1096. from `jeecg-boot`.ctop_transfer_account
  1097. where transferor_date between date_format({start_date}, '%Y-%m-%d') and date_format(date_sub({enddate}, interval -2 day), '%Y-%m-%d')
  1098. and status != 2
  1099. and status = 1
  1100. ) b
  1101. on a.account_id = b.account_id
  1102. where b.account_id is null
  1103. ) t
  1104. left join `jeecg-boot`.sys_user user
  1105. on transferor_one = user.id
  1106. left join `jeecg-boot`.sys_user leader
  1107. on user.leader_id = leader.id
  1108. where user.id is not null
  1109. group by stat_date, account_id, contract_rebate_real_charged_in_yuan,direct_rebate_real_charged_in_yuan
  1110. """.format(start_date=start_date, enddate=end_date)
  1111. print(sql)
  1112. TidbProConn.upsert(conn=self.conn, sql_buff=sql)
  1113. self.conn.commit()
  1114. TidbProConn.conn_close(conn=self.conn)
  1115. except Exception as e:
  1116. logging.error(e)
  1117. return -1
  1118. if __name__ == '__main__':
  1119. KuaishouOperationPerformanceBackChargeBase().handler()