KuaishouOperationPerformanceBackChargeBase.py 81 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050105110521053105410551056105710581059106010611062106310641065106610671068106910701071107210731074107510761077107810791080108110821083108410851086108710881089109010911092109310941095109610971098109911001101110211031104110511061107110811091110111111121113111411151116111711181119112011211122112311241125112611271128112911301131113211331134113511361137113811391140114111421143114411451146114711481149115011511152115311541155115611571158115911601161116211631164116511661167116811691170
  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_one = 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. user_id,
  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_one = 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_one = 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_one = 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. user_id,
  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_one = 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_one = 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_one = 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_one = 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. user_id,
  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. from (select account_id,
  985. contract_rebate_real_charged_in_yuan,
  986. direct_rebate_real_charged_in_yuan,
  987. stat_date,
  988. user_id,
  989. project_id,
  990. project_name,
  991. company_id,
  992. company_name
  993. from application.app_kuaishou_agent_report_d
  994. where stat_date between {start_date} and {enddate}) report
  995. left join(
  996. select account_id,
  997. transferor_one,
  998. if(transferor_one = transferor_two, '', transferor_two) as transferor_two,
  999. if(transferor_one = transferor_three, '', transferor_three) as transferor_three,
  1000. transferor_date_one,
  1001. if(transferor_date_one = transferor_date_two, '', transferor_date_two) transferor_date_two,
  1002. if(transferor_date_one = transferor_date_three, '',
  1003. transferor_date_three) as transferor_date_three,
  1004. assignee_one,
  1005. if(assignee_one = assignee_two, '', assignee_two) assignee_two,
  1006. if(assignee_one = assignee_three, '', assignee_three) assignee_three,
  1007. assignee_date_one,
  1008. if(assignee_date_one = assignee_date_two, '', assignee_date_two) assignee_date_two,
  1009. if(assignee_date_one = assignee_date_three, '', assignee_date_three) assignee_date_three
  1010. from (
  1011. select account_id,
  1012. substring_index(transferor, ':', 1) as transferor_one,
  1013. replace(
  1014. substring_index(substring_index(transferor, ':', 2), ':', -1),
  1015. ',',
  1016. '') as transferor_two,
  1017. replace(
  1018. substring_index(substring_index(transferor, ':', 3), ':', -1),
  1019. ',',
  1020. '') as transferor_three,
  1021. replace(substring_index(transferor_date, ':', 1), ',', '') as transferor_date_one,
  1022. replace(
  1023. substring_index(substring_index(transferor_date, ':', 2), ':', -1),
  1024. ',',
  1025. '') as transferor_date_two,
  1026. replace(
  1027. substring_index(substring_index(transferor_date, ':', 3), ':', -1),
  1028. ',',
  1029. '') as transferor_date_three,
  1030. replace(substring_index(assignee, ':', 1), ',', '') as assignee_one,
  1031. replace(
  1032. substring_index(substring_index(assignee, ':', 2), ':', -1),
  1033. ',',
  1034. '') as assignee_two,
  1035. replace(
  1036. substring_index(substring_index(assignee, ':', 3), ':', -1),
  1037. ',',
  1038. '') as assignee_three,
  1039. substring_index(assignee_date, ':', 1) as assignee_date_one,
  1040. replace(
  1041. substring_index(substring_index(assignee_date, ':', 2), ':', -1),
  1042. ',',
  1043. '') as assignee_date_two,
  1044. replace(
  1045. substring_index(substring_index(assignee_date, ':', 3), ':', -1),
  1046. ',',
  1047. '') as assignee_date_three
  1048. from (
  1049. select account_id,
  1050. group_concat(transferor, ':') as transferor,
  1051. group_concat(transferor_date, ':') as transferor_date,
  1052. group_concat(assignee, ':') as assignee,
  1053. group_concat(assignee_date, ':') as assignee_date
  1054. from (select account_id,transferor,transferor_date,assignee,assignee_date from
  1055. `jeecg-boot`.ctop_transfer_account
  1056. 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
  1057. and status != 2
  1058. order by transferor_date
  1059. )t
  1060. group by account_id) t
  1061. ) t
  1062. ) b
  1063. on report.account_id = b.account_id
  1064. where b.account_id is not null
  1065. and transferor_date_three != ''
  1066. order by stat_date) t
  1067. where stat_date > date_format(transferor_date_three, '%Y%m%d')
  1068. and stat_date <= {enddate}) t
  1069. group by stat_date, account_id, direct_rebate_real_charged_in_yuan,contract_rebate_real_charged_in_yuan
  1070. -- 无转户
  1071. union all
  1072. select stat_date,
  1073. a.account_id,
  1074. contract_rebate_real_charged_in_yuan,
  1075. direct_rebate_real_charged_in_yuan,
  1076. user_id,
  1077. project_id,
  1078. project_name,
  1079. company_id,
  1080. company_name
  1081. from (select stat_date,
  1082. account_id,
  1083. contract_rebate_real_charged_in_yuan,
  1084. direct_rebate_real_charged_in_yuan,
  1085. user_id,
  1086. project_id,
  1087. project_name,
  1088. company_id,
  1089. company_name
  1090. from application.app_kuaishou_agent_report_d
  1091. where stat_date between {start_date} and {enddate}) a
  1092. left join
  1093. (
  1094. select account_id
  1095. from `jeecg-boot`.ctop_transfer_account
  1096. where transferor_date between date_format({start_date}, '%Y-%m-%d') and date_format(date_sub({enddate}, interval -2 day), '%Y-%m-%d')
  1097. and status != 2
  1098. and status = 1
  1099. ) b
  1100. on a.account_id = b.account_id
  1101. where b.account_id is null
  1102. ) t
  1103. left join `jeecg-boot`.sys_user user
  1104. on transferor_one = user.id
  1105. left join `jeecg-boot`.sys_user leader
  1106. on user.leader_id = leader.id
  1107. where user.id is not null
  1108. group by stat_date, account_id, contract_rebate_real_charged_in_yuan,direct_rebate_real_charged_in_yuan
  1109. """.format(start_date=start_date, enddate=end_date)
  1110. print(sql)
  1111. TidbProConn.upsert(conn=self.conn, sql_buff=sql)
  1112. self.conn.commit()
  1113. TidbProConn.conn_close(conn=self.conn)
  1114. except Exception as e:
  1115. logging.error(e)
  1116. return -1
  1117. if __name__ == '__main__':
  1118. KuaishouOperationPerformanceBackChargeBase().handler()