KuaishouOperationPerformanceBackChargeBase.py 79 KB

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