KuaishouOperationPerformanceBackChargeBase.py 81 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145114611471148114911501151115211531154115511561157115811591160116111621163116411651166116711681169117011711172
  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. user_com.company_id,
  25. user_com.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(date_sub({start_date},interval 1 day),'%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(date_sub({start_date},interval 1 day),'%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(date_sub({start_date},interval 1 day),'%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(date_sub({start_date},interval 1 day),'%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(date_sub({start_date},interval 1 day),'%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(date_sub({start_date},interval 1 day),'%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(date_sub({start_date},interval 1 day),'%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(date_sub({start_date},interval 1 day),'%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(date_sub({start_date},interval 1 day),'%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(date_sub({start_date},interval 1 day),'%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. left join `jeecg-boot`.user_company user_com
  1109. on user.id =user_com.user_id
  1110. where user.id is not null
  1111. group by stat_date, account_id, contract_rebate_real_charged_in_yuan,direct_rebate_real_charged_in_yuan
  1112. """.format(start_date=start_date, enddate=end_date)
  1113. print(sql)
  1114. TidbProConn.upsert(conn=self.conn, sql_buff=sql)
  1115. self.conn.commit()
  1116. TidbProConn.conn_close(conn=self.conn)
  1117. except Exception as e:
  1118. logging.error(e)
  1119. return -1
  1120. if __name__ == '__main__':
  1121. KuaishouOperationPerformanceBackChargeBase().handler()