| 1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048104910501051105210531054105510561057105810591060106110621063106410651066106710681069107010711072107310741075107610771078107910801081108210831084108510861087108810891090109110921093109410951096109710981099110011011102110311041105110611071108110911101111111211131114111511161117111811191120112111221123112411251126112711281129113011311132113311341135113611371138113911401141114211431144114511461147114811491150115111521153115411551156115711581159116011611162116311641165116611671168116911701171 | 
							- # Author renyupeng
 
- # coding=utf-8
 
- # @Time    : 2022/9/19 5:11 下午
 
- # @Site    : 
 
- # @File    : KuaishouOperationPerformanceBackChargeBase.py
 
- # @Software: PyCharm
 
- # @contact: renyupeng@c-top.com.cn
 
- # @Tel 1501435553
 
- import logging
 
- from Apietl.conn.TidbProConn import TidbProConn
 
- class KuaishouOperationPerformanceBackChargeBase:
 
-     def __init__(self):
 
-         self.conn = TidbProConn.get_connection()
 
-     def handler(self):
 
-         start_date_sql = """select concat(date_format(date_sub(now(), interval 1 month),'%Y%m'),'01')"""
 
-         end_date_sql = """select date_format( last_day(date_format(date_sub(now(), interval 1 month),'%Y%m%d')),
 
-         '%Y%m%d') """
 
-         start_date = TidbProConn.quary(self.conn, start_date_sql)[0][0]
 
-         end_date = TidbProConn.quary(self.conn, end_date_sql)[0][0]
 
-         try:
 
-             sql = """
 
- replace into application.kuaishou_operation_performance_back_charge_base
 
- select stat_date,
 
-        company_id,
 
-        company_name,
 
-        t.project_id,
 
-        project_name,
 
-        user.id                                        as user_id,
 
-        user.realname                                  as user_name,
 
-        leader.id                                      as leader_id,
 
-        leader.realname                                   leader_name,
 
-        account_id,
 
-        case
 
-            when user.status = 1 then '在职'
 
-            else '离职' end                              as user_status,
 
-        round(contract_rebate_real_charged_in_yuan, 2) as contract_rebate_real_charged_in_yuan,
 
-        round(direct_rebate_real_charged_in_yuan, 2)   as direct_rebate_real_charged_in_yuan
 
- from (
 
-          select stat_date,
 
-                 account_id,
 
-                 contract_rebate_real_charged_in_yuan,
 
-                 direct_rebate_real_charged_in_yuan,
 
-                 transferor_one,
 
-                 project_id,
 
-                 project_name,
 
-                 company_id,
 
-                 company_name
 
-          from (
 
-                   select stat_date,
 
-                          account_id,
 
-                          contract_rebate_real_charged_in_yuan,
 
-                          direct_rebate_real_charged_in_yuan,
 
-                          transferor_one,
 
-                          project_id,
 
-                          project_name,
 
-                          company_id,
 
-                          company_name
 
-                   from (
 
-                            select report.account_id as account_id,
 
-                                   contract_rebate_real_charged_in_yuan,
 
-                                   direct_rebate_real_charged_in_yuan,
 
-                                   stat_date,
 
-                                   user_id,
 
-                                   b.transferor_one,
 
-                                   transferor_date_one,
 
-                                   assignee_one,
 
-                                   assignee_date_one,
 
-                                   project_id,
 
-                                   project_name,
 
-                                   company_id,
 
-                                   company_name
 
-                            from (select account_id,
 
-                                         contract_rebate_real_charged_in_yuan,
 
-                                         direct_rebate_real_charged_in_yuan,
 
-                                         stat_date,
 
-                                         user_id,
 
-                                         project_id,
 
-                                         project_name,
 
-                                         company_id,
 
-                                         company_name
 
-                                  from application.app_kuaishou_agent_report_d
 
-                                  where stat_date between {start_date} and {enddate}) report
 
-                                     left join(
 
-                                select account_id,
 
-                                       transferor_one,
 
-                                       if(transferor_one = transferor_two, '', transferor_two)     as         transferor_two,
 
-                                       if(transferor_one = transferor_three, '', transferor_three) as         transferor_three,
 
-                                       transferor_date_one,
 
-                                       if(transferor_date_one = transferor_date_two, '', transferor_date_two) transferor_date_two,
 
-                                       if(transferor_date_one = transferor_date_three, '',
 
-                                          transferor_date_three)                                   as         transferor_date_three,
 
-                                       assignee_one,
 
-                                       if(assignee_one = assignee_two, '', assignee_two)                      assignee_two,
 
-                                       if(assignee_two = assignee_three, '', assignee_three)                  assignee_three,
 
-                                       assignee_date_one,
 
-                                       if(assignee_date_one = assignee_date_two, '', assignee_date_two)       assignee_date_two,
 
-                                       if(assignee_date_one = assignee_date_three, '', assignee_date_three)   assignee_date_three
 
-                                from (
 
-                                         select account_id,
 
-                                                substring_index(transferor, ':', 1)                        as transferor_one,
 
-                                                replace(
 
-                                                        substring_index(substring_index(transferor, ':', 2), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as transferor_two,
 
-                                                replace(
 
-                                                        substring_index(substring_index(transferor, ':', 3), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as transferor_three,
 
-                                                replace(substring_index(transferor_date, ':', 1), ',', '') as transferor_date_one,
 
-                                                replace(
 
-                                                        substring_index(substring_index(transferor_date, ':', 2), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as transferor_date_two,
 
-                                                replace(
 
-                                                        substring_index(substring_index(transferor_date, ':', 3), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as transferor_date_three,
 
-                                                replace(substring_index(assignee, ':', 1), ',', '')        as assignee_one,
 
-                                                replace(
 
-                                                        substring_index(substring_index(assignee, ':', 2), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as assignee_two,
 
-                                                replace(
 
-                                                        substring_index(substring_index(assignee, ':', 3), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as assignee_three,
 
-                                                substring_index(assignee_date, ':', 1)                     as assignee_date_one,
 
-                                                replace(
 
-                                                        substring_index(substring_index(assignee_date, ':', 2), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as assignee_date_two,
 
-                                                replace(
 
-                                                        substring_index(substring_index(assignee_date, ':', 3), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as assignee_date_three
 
-                                         from (
 
-                                                    select account_id,
 
- 	                                                        group_concat(transferor, ':')      as transferor,
 
- 	                                                        group_concat(transferor_date, ':') as transferor_date,
 
- 	                                                        group_concat(assignee, ':')        as assignee,
 
- 	                                                        group_concat(assignee_date, ':')   as assignee_date
 
- 	                                                 from (select account_id,transferor,transferor_date,assignee,assignee_date from
 
- 	                                                      `jeecg-boot`.ctop_transfer_account
 
- 	                                                 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
 
- 	                                                   and status != 2
 
- 	                                                order by transferor_date
 
- 	                                           )t
 
- 	                                        group by account_id) t
 
-                                     ) t
 
-                            ) b
 
-                                              on report.account_id = b.account_id
 
-                            where b.account_id is not null
 
-                              and transferor_date_two = ''
 
-                            order by stat_date) t
 
-                   where stat_date >= {start_date}
 
-                     and stat_date <= date_format(transferor_date_one, '%Y%m%d')
 
-                   union all
 
-                   select stat_date,
 
-                          account_id,
 
-                          contract_rebate_real_charged_in_yuan,
 
-                          direct_rebate_real_charged_in_yuan,
 
-                          assignee_one,
 
-                          project_id,
 
-                          project_name,
 
-                          company_id,
 
-                          company_name
 
-                   from (
 
-                            select report.account_id as account_id,
 
-                                   contract_rebate_real_charged_in_yuan,
 
-                                   direct_rebate_real_charged_in_yuan,
 
-                                   stat_date,
 
-                                   user_id,
 
-                                   b.transferor_one,
 
-                                   transferor_date_one,
 
-                                   assignee_one,
 
-                                   assignee_date_one,
 
-                                   project_id,
 
-                                   project_name,
 
-                                   company_id,
 
-                                   company_name
 
-                            from (select account_id,
 
-                                         contract_rebate_real_charged_in_yuan,
 
-                                         direct_rebate_real_charged_in_yuan,
 
-                                         stat_date,
 
-                                         user_id,
 
-                                         project_id,
 
-                                         project_name,
 
-                                         company_id,
 
-                                         company_name
 
-                                  from application.app_kuaishou_agent_report_d
 
-                                  where stat_date between {start_date} and {enddate}) report
 
-                                     left join(
 
-                                select account_id,
 
-                                       transferor_one,
 
-                                       if(transferor_one = transferor_two, '', transferor_two)     as         transferor_two,
 
-                                       if(transferor_one = transferor_three, '', transferor_three) as         transferor_three,
 
-                                       transferor_date_one,
 
-                                       if(transferor_date_one = transferor_date_two, '', transferor_date_two) transferor_date_two,
 
-                                       if(transferor_date_one = transferor_date_three, '',
 
-                                          transferor_date_three)                                   as         transferor_date_three,
 
-                                       assignee_one,
 
-                                       if(assignee_one = assignee_two, '', assignee_two)                      assignee_two,
 
-                                       if(assignee_two = assignee_three, '', assignee_three)                  assignee_three,
 
-                                       assignee_date_one,
 
-                                       if(assignee_date_one = assignee_date_two, '', assignee_date_two)       assignee_date_two,
 
-                                       if(assignee_date_one = assignee_date_three, '', assignee_date_three)   assignee_date_three
 
-                                from (
 
-                                         select account_id,
 
-                                                substring_index(transferor, ':', 1)                        as transferor_one,
 
-                                                replace(
 
-                                                        substring_index(substring_index(transferor, ':', 2), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as transferor_two,
 
-                                                replace(
 
-                                                        substring_index(substring_index(transferor, ':', 3), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as transferor_three,
 
-                                                replace(substring_index(transferor_date, ':', 1), ',', '') as transferor_date_one,
 
-                                                replace(
 
-                                                        substring_index(substring_index(transferor_date, ':', 2), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as transferor_date_two,
 
-                                                replace(
 
-                                                        substring_index(substring_index(transferor_date, ':', 3), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as transferor_date_three,
 
-                                                replace(substring_index(assignee, ':', 1), ',', '')        as assignee_one,
 
-                                                replace(
 
-                                                        substring_index(substring_index(assignee, ':', 2), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as assignee_two,
 
-                                                replace(
 
-                                                        substring_index(substring_index(assignee, ':', 3), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as assignee_three,
 
-                                                substring_index(assignee_date, ':', 1)                     as assignee_date_one,
 
-                                                replace(
 
-                                                        substring_index(substring_index(assignee_date, ':', 2), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as assignee_date_two,
 
-                                                replace(
 
-                                                        substring_index(substring_index(assignee_date, ':', 3), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as assignee_date_three
 
-                                         from (
 
-                                                     select account_id,
 
- 	                                                        group_concat(transferor, ':')      as transferor,
 
- 	                                                        group_concat(transferor_date, ':') as transferor_date,
 
- 	                                                        group_concat(assignee, ':')        as assignee,
 
- 	                                                        group_concat(assignee_date, ':')   as assignee_date
 
- 	                                                 from (select account_id,transferor,transferor_date,assignee,assignee_date from
 
- 	                                                      `jeecg-boot`.ctop_transfer_account
 
- 	                                                 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
 
- 	                                                   and status != 2
 
- 	                                                order by transferor_date
 
- 	                                           )t
 
- 	                                        group by account_id) t
 
-                                     ) t
 
-                            ) b
 
-                                              on report.account_id = b.account_id
 
-                            where b.account_id is not null
 
-                              and transferor_date_two = ''
 
-                            order by stat_date) t
 
-                   where stat_date > date_format(transferor_date_one, '%Y%m%d')
 
-                     and stat_date <= {enddate}
 
-                   union all
 
- -- 二次转户
 
-                   select stat_date,
 
-                          account_id,
 
-                          contract_rebate_real_charged_in_yuan,
 
-                          direct_rebate_real_charged_in_yuan,
 
-                          transferor_one,
 
-                          project_id,
 
-                          project_name,
 
-                          company_id,
 
-                          company_name
 
-                   from (
 
-                            select report.account_id as account_id,
 
-                                   contract_rebate_real_charged_in_yuan,
 
-                                   direct_rebate_real_charged_in_yuan,
 
-                                   stat_date,
 
-                                   user_id,
 
-                                   transferor_one,
 
-                                   transferor_date_one,
 
-                                   transferor_two,
 
-                                   transferor_date_two,
 
-                                   assignee_one,
 
-                                   assignee_date_one,
 
-                                   assignee_two,
 
-                                   assignee_date_two,
 
-                                   project_id,
 
-                                   project_name,
 
-                                   company_id,
 
-                                   company_name
 
-                            from (select account_id,
 
-                                         contract_rebate_real_charged_in_yuan,
 
-                                         direct_rebate_real_charged_in_yuan,
 
-                                         stat_date,
 
-                                         user_id,
 
-                                         project_id,
 
-                                         project_name,
 
-                                         company_id,
 
-                                         company_name
 
-                                  from application.app_kuaishou_agent_report_d
 
-                                  where stat_date between {start_date} and {enddate}) report
 
-                                     left join(
 
-                                select account_id,
 
-                                       transferor_one,
 
-                                       if(transferor_one = transferor_two, '', transferor_two)     as         transferor_two,
 
-                                       if(transferor_one = transferor_three, '', transferor_three) as         transferor_three,
 
-                                       transferor_date_one,
 
-                                       if(transferor_date_one = transferor_date_two, '', transferor_date_two) transferor_date_two,
 
-                                       if(transferor_date_one = transferor_date_three, '',
 
-                                          transferor_date_three)                                   as         transferor_date_three,
 
-                                       assignee_one,
 
-                                       if(assignee_one = assignee_two, '', assignee_two)                      assignee_two,
 
-                                       if(assignee_two = assignee_three, '', assignee_three)                  assignee_three,
 
-                                       assignee_date_one,
 
-                                       if(assignee_date_one = assignee_date_two, '', assignee_date_two)       assignee_date_two,
 
-                                       if(assignee_date_one = assignee_date_three, '', assignee_date_three)   assignee_date_three
 
-                                from (
 
-                                         select account_id,
 
-                                                substring_index(transferor, ':', 1)                        as transferor_one,
 
-                                                replace(
 
-                                                        substring_index(substring_index(transferor, ':', 2), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as transferor_two,
 
-                                                replace(
 
-                                                        substring_index(substring_index(transferor, ':', 3), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as transferor_three,
 
-                                                replace(substring_index(transferor_date, ':', 1), ',', '') as transferor_date_one,
 
-                                                replace(
 
-                                                        substring_index(substring_index(transferor_date, ':', 2), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as transferor_date_two,
 
-                                                replace(
 
-                                                        substring_index(substring_index(transferor_date, ':', 3), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as transferor_date_three,
 
-                                                replace(substring_index(assignee, ':', 1), ',', '')        as assignee_one,
 
-                                                replace(
 
-                                                        substring_index(substring_index(assignee, ':', 2), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as assignee_two,
 
-                                                replace(
 
-                                                        substring_index(substring_index(assignee, ':', 3), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as assignee_three,
 
-                                                substring_index(assignee_date, ':', 1)                     as assignee_date_one,
 
-                                                replace(
 
-                                                        substring_index(substring_index(assignee_date, ':', 2), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as assignee_date_two,
 
-                                                replace(
 
-                                                        substring_index(substring_index(assignee_date, ':', 3), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as assignee_date_three
 
-                                         from (
 
-                                                   select account_id,
 
- 	                                                        group_concat(transferor, ':')      as transferor,
 
- 	                                                        group_concat(transferor_date, ':') as transferor_date,
 
- 	                                                        group_concat(assignee, ':')        as assignee,
 
- 	                                                        group_concat(assignee_date, ':')   as assignee_date
 
- 	                                                 from (select account_id,transferor,transferor_date,assignee,assignee_date from
 
- 	                                                      `jeecg-boot`.ctop_transfer_account
 
- 	                                                 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
 
- 	                                                   and status != 2
 
- 	                                                order by transferor_date
 
- 	                                           )t
 
- 	                                        group by account_id) t
 
-                                     ) t
 
-                            ) b
 
-                                              on report.account_id = b.account_id
 
-                            where b.account_id is not null
 
-                              and transferor_date_two != ''
 
-                              and transferor_date_three = ''
 
-                            order by stat_date) t
 
-                   where stat_date >= {start_date}
 
-                     and stat_date <= date_format(transferor_date_one, '%Y%m%d')
 
-                   union all
 
-                   select stat_date,
 
-                          account_id,
 
-                          contract_rebate_real_charged_in_yuan,
 
-                          direct_rebate_real_charged_in_yuan,
 
-                          transferor_two,
 
-                          project_id,
 
-                          project_name,
 
-                          company_id,
 
-                          company_name
 
-                   from (
 
-                            select report.account_id as account_id,
 
-                                   contract_rebate_real_charged_in_yuan,
 
-                                   direct_rebate_real_charged_in_yuan,
 
-                                   stat_date,
 
-                                   user_id,
 
-                                   transferor_one,
 
-                                   transferor_date_one,
 
-                                   transferor_two,
 
-                                   transferor_date_two,
 
-                                   assignee_one,
 
-                                   assignee_date_one,
 
-                                   assignee_two,
 
-                                   assignee_date_two,
 
-                                   project_id,
 
-                                   project_name,
 
-                                   company_id,
 
-                                   company_name
 
-                            from (select account_id,
 
-                                         contract_rebate_real_charged_in_yuan,
 
-                                         direct_rebate_real_charged_in_yuan,
 
-                                         stat_date,
 
-                                         user_id,
 
-                                         project_id,
 
-                                         project_name,
 
-                                         company_id,
 
-                                         company_name
 
-                                  from application.app_kuaishou_agent_report_d
 
-                                  where stat_date between {start_date} and {enddate}) report
 
-                                     left join(
 
-                                select account_id,
 
-                                       transferor_one,
 
-                                       if(transferor_one = transferor_two, '', transferor_two)     as         transferor_two,
 
-                                       if(transferor_one = transferor_three, '', transferor_three) as         transferor_three,
 
-                                       transferor_date_one,
 
-                                       if(transferor_date_one = transferor_date_two, '', transferor_date_two) transferor_date_two,
 
-                                       if(transferor_date_one = transferor_date_three, '',
 
-                                          transferor_date_three)                                   as         transferor_date_three,
 
-                                       assignee_one,
 
-                                       if(assignee_one = assignee_two, '', assignee_two)                      assignee_two,
 
-                                       if(assignee_two = assignee_three, '', assignee_three)                  assignee_three,
 
-                                       assignee_date_one,
 
-                                       if(assignee_date_one = assignee_date_two, '', assignee_date_two)       assignee_date_two,
 
-                                       if(assignee_date_one = assignee_date_three, '', assignee_date_three)   assignee_date_three
 
-                                from (
 
-                                         select account_id,
 
-                                                substring_index(transferor, ':', 1)                        as transferor_one,
 
-                                                replace(
 
-                                                        substring_index(substring_index(transferor, ':', 2), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as transferor_two,
 
-                                                replace(
 
-                                                        substring_index(substring_index(transferor, ':', 3), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as transferor_three,
 
-                                                replace(substring_index(transferor_date, ':', 1), ',', '') as transferor_date_one,
 
-                                                replace(
 
-                                                        substring_index(substring_index(transferor_date, ':', 2), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as transferor_date_two,
 
-                                                replace(
 
-                                                        substring_index(substring_index(transferor_date, ':', 3), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as transferor_date_three,
 
-                                                replace(substring_index(assignee, ':', 1), ',', '')        as assignee_one,
 
-                                                replace(
 
-                                                        substring_index(substring_index(assignee, ':', 2), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as assignee_two,
 
-                                                replace(
 
-                                                        substring_index(substring_index(assignee, ':', 3), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as assignee_three,
 
-                                                substring_index(assignee_date, ':', 1)                     as assignee_date_one,
 
-                                                replace(
 
-                                                        substring_index(substring_index(assignee_date, ':', 2), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as assignee_date_two,
 
-                                                replace(
 
-                                                        substring_index(substring_index(assignee_date, ':', 3), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as assignee_date_three
 
-                                         from (
 
-                                                    select account_id,
 
- 	                                                        group_concat(transferor, ':')      as transferor,
 
- 	                                                        group_concat(transferor_date, ':') as transferor_date,
 
- 	                                                        group_concat(assignee, ':')        as assignee,
 
- 	                                                        group_concat(assignee_date, ':')   as assignee_date
 
- 	                                                 from (select account_id,transferor,transferor_date,assignee,assignee_date from
 
- 	                                                      `jeecg-boot`.ctop_transfer_account
 
- 	                                                 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
 
- 	                                                   and status != 2
 
- 	                                                order by transferor_date
 
- 	                                           )t
 
- 	                                        group by account_id) t
 
-                                     ) t
 
-                            ) b
 
-                                              on report.account_id = b.account_id
 
-                            where b.account_id is not null
 
-                              and transferor_date_two != ''
 
-                              and transferor_date_three = ''
 
-                            order by stat_date) t
 
-                   where stat_date > date_format(transferor_date_one, '%Y%m%d')
 
-                     and stat_date <= date_format(transferor_date_two, '%Y%m%d')
 
-                   union all
 
-                   select stat_date,
 
-                          account_id,
 
-                          contract_rebate_real_charged_in_yuan,
 
-                          direct_rebate_real_charged_in_yuan,
 
-                          assignee_two,
 
-                          project_id,
 
-                          project_name,
 
-                          company_id,
 
-                          company_name
 
-                   from (
 
-                            select report.account_id as account_id,
 
-                                   contract_rebate_real_charged_in_yuan,
 
-                                   direct_rebate_real_charged_in_yuan,
 
-                                   stat_date,
 
-                                   user_id,
 
-                                   transferor_one,
 
-                                   transferor_date_one,
 
-                                   transferor_two,
 
-                                   transferor_date_two,
 
-                                   assignee_one,
 
-                                   assignee_date_one,
 
-                                   assignee_two,
 
-                                   assignee_date_two,
 
-                                   project_id,
 
-                                   project_name,
 
-                                   company_id,
 
-                                   company_name
 
-                            from (select account_id,
 
-                                         contract_rebate_real_charged_in_yuan,
 
-                                         direct_rebate_real_charged_in_yuan,
 
-                                         stat_date,
 
-                                         user_id,
 
-                                         project_id,
 
-                                         project_name,
 
-                                         company_id,
 
-                                         company_name
 
-                                  from application.app_kuaishou_agent_report_d
 
-                                  where stat_date between {start_date} and {enddate}) report
 
-                                     left join(
 
-                                select account_id,
 
-                                       transferor_one,
 
-                                       if(transferor_one = transferor_two, '', transferor_two)     as         transferor_two,
 
-                                       if(transferor_one = transferor_three, '', transferor_three) as         transferor_three,
 
-                                       transferor_date_one,
 
-                                       if(transferor_date_one = transferor_date_two, '', transferor_date_two) transferor_date_two,
 
-                                       if(transferor_date_one = transferor_date_three, '',
 
-                                          transferor_date_three)                                   as         transferor_date_three,
 
-                                       assignee_one,
 
-                                       if(assignee_one = assignee_two, '', assignee_two)                      assignee_two,
 
-                                       if(assignee_two = assignee_three, '', assignee_three)                  assignee_three,
 
-                                       assignee_date_one,
 
-                                       if(assignee_date_one = assignee_date_two, '', assignee_date_two)       assignee_date_two,
 
-                                       if(assignee_date_one = assignee_date_three, '', assignee_date_three)   assignee_date_three
 
-                                from (
 
-                                         select account_id,
 
-                                                substring_index(transferor, ':', 1)                        as transferor_one,
 
-                                                replace(
 
-                                                        substring_index(substring_index(transferor, ':', 2), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as transferor_two,
 
-                                                replace(
 
-                                                        substring_index(substring_index(transferor, ':', 3), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as transferor_three,
 
-                                                replace(substring_index(transferor_date, ':', 1), ',', '') as transferor_date_one,
 
-                                                replace(
 
-                                                        substring_index(substring_index(transferor_date, ':', 2), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as transferor_date_two,
 
-                                                replace(
 
-                                                        substring_index(substring_index(transferor_date, ':', 3), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as transferor_date_three,
 
-                                                replace(substring_index(assignee, ':', 1), ',', '')        as assignee_one,
 
-                                                replace(
 
-                                                        substring_index(substring_index(assignee, ':', 2), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as assignee_two,
 
-                                                replace(
 
-                                                        substring_index(substring_index(assignee, ':', 3), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as assignee_three,
 
-                                                substring_index(assignee_date, ':', 1)                     as assignee_date_one,
 
-                                                replace(
 
-                                                        substring_index(substring_index(assignee_date, ':', 2), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as assignee_date_two,
 
-                                                replace(
 
-                                                        substring_index(substring_index(assignee_date, ':', 3), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as assignee_date_three
 
-                                         from (
 
-                                                    select account_id,
 
- 	                                                        group_concat(transferor, ':')      as transferor,
 
- 	                                                        group_concat(transferor_date, ':') as transferor_date,
 
- 	                                                        group_concat(assignee, ':')        as assignee,
 
- 	                                                        group_concat(assignee_date, ':')   as assignee_date
 
- 	                                                 from (select account_id,transferor,transferor_date,assignee,assignee_date from
 
- 	                                                      `jeecg-boot`.ctop_transfer_account
 
- 	                                                 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
 
- 	                                                   and status != 2
 
- 	                                                order by transferor_date
 
- 	                                           )t
 
- 	                                        group by account_id) t
 
-                                     ) t
 
-                            ) b
 
-                                              on report.account_id = b.account_id
 
-                            where b.account_id is not null
 
-                              and transferor_date_two != ''
 
-                              and transferor_date_three = ''
 
-                            order by stat_date) t
 
-                   where stat_date > date_format(transferor_date_two, '%Y%m%d')
 
-                     and stat_date <= {enddate}
 
- -- 三次转户
 
-                   union all
 
-                   select stat_date,
 
-                          account_id,
 
-                          contract_rebate_real_charged_in_yuan,
 
-                          direct_rebate_real_charged_in_yuan,
 
-                          transferor_one,
 
-                          project_id,
 
-                          project_name,
 
-                          company_id,
 
-                          company_name
 
-                   from (
 
-                            select report.account_id as account_id,
 
-                                   contract_rebate_real_charged_in_yuan,
 
-                                   direct_rebate_real_charged_in_yuan,
 
-                                   stat_date,
 
-                                   user_id,
 
-                                   transferor_one,
 
-                                   transferor_date_one,
 
-                                   transferor_two,
 
-                                   transferor_date_two,
 
-                                   assignee_one,
 
-                                   assignee_date_one,
 
-                                   assignee_two,
 
-                                   assignee_date_two,
 
-                                   transferor_three,
 
-                                   transferor_date_three,
 
-                                   project_id,
 
-                                   project_name,
 
-                                   company_id,
 
-                                   company_name
 
-                            from (select account_id,
 
-                                         contract_rebate_real_charged_in_yuan,
 
-                                         direct_rebate_real_charged_in_yuan,
 
-                                         stat_date,
 
-                                         user_id,
 
-                                         project_id,
 
-                                         project_name,
 
-                                         company_id,
 
-                                         company_name
 
-                                  from application.app_kuaishou_agent_report_d
 
-                                  where stat_date between {start_date} and {enddate}) report
 
-                                     left join(
 
-                                select account_id,
 
-                                       transferor_one,
 
-                                       if(transferor_one = transferor_two, '', transferor_two)     as         transferor_two,
 
-                                       if(transferor_one = transferor_three, '', transferor_three) as         transferor_three,
 
-                                       transferor_date_one,
 
-                                       if(transferor_date_one = transferor_date_two, '', transferor_date_two) transferor_date_two,
 
-                                       if(transferor_date_one = transferor_date_three, '',
 
-                                          transferor_date_three)                                   as         transferor_date_three,
 
-                                       assignee_one,
 
-                                       if(assignee_one = assignee_two, '', assignee_two)                      assignee_two,
 
-                                       if(assignee_two = assignee_three, '', assignee_three)                  assignee_three,
 
-                                       assignee_date_one,
 
-                                       if(assignee_date_one = assignee_date_two, '', assignee_date_two)       assignee_date_two,
 
-                                       if(assignee_date_one = assignee_date_three, '', assignee_date_three)   assignee_date_three
 
-                                from (
 
-                                         select account_id,
 
-                                                substring_index(transferor, ':', 1)                        as transferor_one,
 
-                                                replace(
 
-                                                        substring_index(substring_index(transferor, ':', 2), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as transferor_two,
 
-                                                replace(
 
-                                                        substring_index(substring_index(transferor, ':', 3), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as transferor_three,
 
-                                                replace(substring_index(transferor_date, ':', 1), ',', '') as transferor_date_one,
 
-                                                replace(
 
-                                                        substring_index(substring_index(transferor_date, ':', 2), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as transferor_date_two,
 
-                                                replace(
 
-                                                        substring_index(substring_index(transferor_date, ':', 3), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as transferor_date_three,
 
-                                                replace(substring_index(assignee, ':', 1), ',', '')        as assignee_one,
 
-                                                replace(
 
-                                                        substring_index(substring_index(assignee, ':', 2), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as assignee_two,
 
-                                                replace(
 
-                                                        substring_index(substring_index(assignee, ':', 3), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as assignee_three,
 
-                                                substring_index(assignee_date, ':', 1)                     as assignee_date_one,
 
-                                                replace(
 
-                                                        substring_index(substring_index(assignee_date, ':', 2), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as assignee_date_two,
 
-                                                replace(
 
-                                                        substring_index(substring_index(assignee_date, ':', 3), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as assignee_date_three
 
-                                         from (   select account_id,
 
- 	                                                        group_concat(transferor, ':')      as transferor,
 
- 	                                                        group_concat(transferor_date, ':') as transferor_date,
 
- 	                                                        group_concat(assignee, ':')        as assignee,
 
- 	                                                        group_concat(assignee_date, ':')   as assignee_date
 
- 	                                                 from (select account_id,transferor,transferor_date,assignee,assignee_date from
 
- 	                                                      `jeecg-boot`.ctop_transfer_account
 
- 	                                                 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
 
- 	                                                   and status != 2
 
- 	                                                order by transferor_date
 
- 	                                           )t
 
- 	                                        group by account_id) t
 
-                                     ) t
 
-                            ) b
 
-                                              on report.account_id = b.account_id
 
-                            where b.account_id is not null
 
-                              and transferor_date_three != ''
 
-                            order by stat_date) t
 
-                   where stat_date >= {start_date}
 
-                     and stat_date <= date_format(transferor_date_one, '%Y%m%d')
 
-                   union all
 
-                   select stat_date,
 
-                          account_id,
 
-                          contract_rebate_real_charged_in_yuan,
 
-                          direct_rebate_real_charged_in_yuan,
 
-                          transferor_two,
 
-                          project_id,
 
-                          project_name,
 
-                          company_id,
 
-                          company_name
 
-                   from (
 
-                            select report.account_id as account_id,
 
-                                   contract_rebate_real_charged_in_yuan,
 
-                                   direct_rebate_real_charged_in_yuan,
 
-                                   stat_date,
 
-                                   user_id,
 
-                                   transferor_one,
 
-                                   transferor_date_one,
 
-                                   transferor_two,
 
-                                   transferor_date_two,
 
-                                   assignee_one,
 
-                                   assignee_date_one,
 
-                                   assignee_two,
 
-                                   assignee_date_two,
 
-                                   transferor_three,
 
-                                   transferor_date_three,
 
-                                   project_id,
 
-                                   project_name,
 
-                                   company_id,
 
-                                   company_name
 
-                            from (select account_id,
 
-                                         contract_rebate_real_charged_in_yuan,
 
-                                         direct_rebate_real_charged_in_yuan,
 
-                                         stat_date,
 
-                                         user_id,
 
-                                         project_id,
 
-                                         project_name,
 
-                                         company_id,
 
-                                         company_name
 
-                                  from application.app_kuaishou_agent_report_d
 
-                                  where stat_date between {start_date} and {enddate}) report
 
-                                     left join(
 
-                                select account_id,
 
-                                       transferor_one,
 
-                                       if(transferor_one = transferor_two, '', transferor_two)     as         transferor_two,
 
-                                       if(transferor_one = transferor_three, '', transferor_three) as         transferor_three,
 
-                                       transferor_date_one,
 
-                                       if(transferor_date_one = transferor_date_two, '', transferor_date_two) transferor_date_two,
 
-                                       if(transferor_date_one = transferor_date_three, '',
 
-                                          transferor_date_three)                                   as         transferor_date_three,
 
-                                       assignee_one,
 
-                                       if(assignee_one = assignee_two, '', assignee_two)                      assignee_two,
 
-                                       if(assignee_two = assignee_three, '', assignee_three)                  assignee_three,
 
-                                       assignee_date_one,
 
-                                       if(assignee_date_one = assignee_date_two, '', assignee_date_two)       assignee_date_two,
 
-                                       if(assignee_date_one = assignee_date_three, '', assignee_date_three)   assignee_date_three
 
-                                from (
 
-                                         select account_id,
 
-                                                substring_index(transferor, ':', 1)                        as transferor_one,
 
-                                                replace(
 
-                                                        substring_index(substring_index(transferor, ':', 2), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as transferor_two,
 
-                                                replace(
 
-                                                        substring_index(substring_index(transferor, ':', 3), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as transferor_three,
 
-                                                replace(substring_index(transferor_date, ':', 1), ',', '') as transferor_date_one,
 
-                                                replace(
 
-                                                        substring_index(substring_index(transferor_date, ':', 2), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as transferor_date_two,
 
-                                                replace(
 
-                                                        substring_index(substring_index(transferor_date, ':', 3), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as transferor_date_three,
 
-                                                replace(substring_index(assignee, ':', 1), ',', '')        as assignee_one,
 
-                                                replace(
 
-                                                        substring_index(substring_index(assignee, ':', 2), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as assignee_two,
 
-                                                replace(
 
-                                                        substring_index(substring_index(assignee, ':', 3), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as assignee_three,
 
-                                                substring_index(assignee_date, ':', 1)                     as assignee_date_one,
 
-                                                replace(
 
-                                                        substring_index(substring_index(assignee_date, ':', 2), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as assignee_date_two,
 
-                                                replace(
 
-                                                        substring_index(substring_index(assignee_date, ':', 3), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as assignee_date_three
 
-                                         from (
 
-                                                    select account_id,
 
- 	                                                        group_concat(transferor, ':')      as transferor,
 
- 	                                                        group_concat(transferor_date, ':') as transferor_date,
 
- 	                                                        group_concat(assignee, ':')        as assignee,
 
- 	                                                        group_concat(assignee_date, ':')   as assignee_date
 
- 	                                                 from (select account_id,transferor,transferor_date,assignee,assignee_date from
 
- 	                                                      `jeecg-boot`.ctop_transfer_account
 
- 	                                                 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
 
- 	                                                   and status != 2
 
- 	                                                order by transferor_date
 
- 	                                           )t
 
- 	                                        group by account_id) t
 
-                                     ) t
 
-                            ) b
 
-                                              on report.account_id = b.account_id
 
-                            where b.account_id is not null
 
-                              and transferor_date_three != ''
 
-                            order by stat_date) t
 
-                   where stat_date > date_format(transferor_date_one, '%Y%m%d')
 
-                     and stat_date <= date_format(transferor_date_two, '%Y%m%d')
 
-                   union all
 
-                   select stat_date,
 
-                          account_id,
 
-                          contract_rebate_real_charged_in_yuan,
 
-                          direct_rebate_real_charged_in_yuan,
 
-                          transferor_three,
 
-                          project_id,
 
-                          project_name,
 
-                          company_id,
 
-                          company_name
 
-                   from (
 
-                            select report.account_id as account_id,
 
-                                   contract_rebate_real_charged_in_yuan,
 
-                                   direct_rebate_real_charged_in_yuan,
 
-                                   stat_date,
 
-                                   user_id,
 
-                                   transferor_one,
 
-                                   transferor_date_one,
 
-                                   transferor_two,
 
-                                   transferor_date_two,
 
-                                   assignee_one,
 
-                                   assignee_date_one,
 
-                                   assignee_two,
 
-                                   assignee_date_two,
 
-                                   transferor_three,
 
-                                   transferor_date_three,
 
-                                   project_id,
 
-                                   project_name,
 
-                                   company_id,
 
-                                   company_name
 
-                            from (select account_id,
 
-                                         contract_rebate_real_charged_in_yuan,
 
-                                         direct_rebate_real_charged_in_yuan,
 
-                                         stat_date,
 
-                                         user_id,
 
-                                         project_id,
 
-                                         project_name,
 
-                                         company_id,
 
-                                         company_name
 
-                                  from application.app_kuaishou_agent_report_d
 
-                                  where stat_date between {start_date} and {enddate}) report
 
-                                     left join(
 
-                                select account_id,
 
-                                       transferor_one,
 
-                                       if(transferor_one = transferor_two, '', transferor_two)     as         transferor_two,
 
-                                       if(transferor_one = transferor_three, '', transferor_three) as         transferor_three,
 
-                                       transferor_date_one,
 
-                                       if(transferor_date_one = transferor_date_two, '', transferor_date_two) transferor_date_two,
 
-                                       if(transferor_date_one = transferor_date_three, '',
 
-                                          transferor_date_three)                                   as         transferor_date_three,
 
-                                       assignee_one,
 
-                                       if(assignee_one = assignee_two, '', assignee_two)                      assignee_two,
 
-                                       if(assignee_two = assignee_three, '', assignee_three)                  assignee_three,
 
-                                       assignee_date_one,
 
-                                       if(assignee_date_one = assignee_date_two, '', assignee_date_two)       assignee_date_two,
 
-                                       if(assignee_date_one = assignee_date_three, '', assignee_date_three)   assignee_date_three
 
-                                from (
 
-                                         select account_id,
 
-                                                substring_index(transferor, ':', 1)                        as transferor_one,
 
-                                                replace(
 
-                                                        substring_index(substring_index(transferor, ':', 2), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as transferor_two,
 
-                                                replace(
 
-                                                        substring_index(substring_index(transferor, ':', 3), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as transferor_three,
 
-                                                replace(substring_index(transferor_date, ':', 1), ',', '') as transferor_date_one,
 
-                                                replace(
 
-                                                        substring_index(substring_index(transferor_date, ':', 2), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as transferor_date_two,
 
-                                                replace(
 
-                                                        substring_index(substring_index(transferor_date, ':', 3), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as transferor_date_three,
 
-                                                replace(substring_index(assignee, ':', 1), ',', '')        as assignee_one,
 
-                                                replace(
 
-                                                        substring_index(substring_index(assignee, ':', 2), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as assignee_two,
 
-                                                replace(
 
-                                                        substring_index(substring_index(assignee, ':', 3), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as assignee_three,
 
-                                                substring_index(assignee_date, ':', 1)                     as assignee_date_one,
 
-                                                replace(
 
-                                                        substring_index(substring_index(assignee_date, ':', 2), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as assignee_date_two,
 
-                                                replace(
 
-                                                        substring_index(substring_index(assignee_date, ':', 3), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as assignee_date_three
 
-                                         from (
 
-                                                    select account_id,
 
- 	                                                        group_concat(transferor, ':')      as transferor,
 
- 	                                                        group_concat(transferor_date, ':') as transferor_date,
 
- 	                                                        group_concat(assignee, ':')        as assignee,
 
- 	                                                        group_concat(assignee_date, ':')   as assignee_date
 
- 	                                                 from (select account_id,transferor,transferor_date,assignee,assignee_date from
 
- 	                                                      `jeecg-boot`.ctop_transfer_account
 
- 	                                                 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
 
- 	                                                   and status != 2
 
- 	                                                order by transferor_date
 
- 	                                           )t
 
- 	                                        group by account_id) t
 
-                                     ) t
 
-                            ) b
 
-                                              on report.account_id = b.account_id
 
-                            where b.account_id is not null
 
-                              and transferor_date_three != ''
 
-                            order by stat_date) t
 
-                   where stat_date > date_format(transferor_date_two, '%Y%m%d')
 
-                     and stat_date <= date_format(transferor_date_three, '%Y%m%d')
 
-                   union all
 
-                   select stat_date,
 
-                          account_id,
 
-                          contract_rebate_real_charged_in_yuan,
 
-                          direct_rebate_real_charged_in_yuan,
 
-                          assignee_three,
 
-                          project_id,
 
-                          project_name,
 
-                          company_id,
 
-                          company_name
 
-                   from (
 
-                            select report.account_id as account_id,
 
-                                   contract_rebate_real_charged_in_yuan,
 
-                                   direct_rebate_real_charged_in_yuan,
 
-                                   stat_date,
 
-                                   user_id,
 
-                                   transferor_one,
 
-                                   transferor_date_one,
 
-                                   transferor_two,
 
-                                   transferor_date_two,
 
-                                   assignee_one,
 
-                                   assignee_date_one,
 
-                                   assignee_two,
 
-                                   assignee_date_two,
 
-                                   transferor_three,
 
-                                   transferor_date_three,
 
-                                   project_id,
 
-                                   project_name,
 
-                                   company_id,
 
-                                   company_name,
 
-                                   assignee_three
 
-                            from (select account_id,
 
-                                         contract_rebate_real_charged_in_yuan,
 
-                                         direct_rebate_real_charged_in_yuan,
 
-                                         stat_date,
 
-                                         user_id,
 
-                                         project_id,
 
-                                         project_name,
 
-                                         company_id,
 
-                                         company_name
 
-                                  from application.app_kuaishou_agent_report_d
 
-                                  where stat_date between {start_date} and {enddate}) report
 
-                                     left join(
 
-                                select account_id,
 
-                                       transferor_one,
 
-                                       if(transferor_one = transferor_two, '', transferor_two)     as         transferor_two,
 
-                                       if(transferor_one = transferor_three, '', transferor_three) as         transferor_three,
 
-                                       transferor_date_one,
 
-                                       if(transferor_date_one = transferor_date_two, '', transferor_date_two) transferor_date_two,
 
-                                       if(transferor_date_one = transferor_date_three, '',
 
-                                          transferor_date_three)                                   as         transferor_date_three,
 
-                                       assignee_one,
 
-                                       if(assignee_one = assignee_two, '', assignee_two)                      assignee_two,
 
-                                       if(assignee_two = assignee_three, '', assignee_three)                  assignee_three,
 
-                                       assignee_date_one,
 
-                                       if(assignee_date_one = assignee_date_two, '', assignee_date_two)       assignee_date_two,
 
-                                       if(assignee_date_one = assignee_date_three, '', assignee_date_three)   assignee_date_three
 
-                                from (
 
-                                         select account_id,
 
-                                                substring_index(transferor, ':', 1)                        as transferor_one,
 
-                                                replace(
 
-                                                        substring_index(substring_index(transferor, ':', 2), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as transferor_two,
 
-                                                replace(
 
-                                                        substring_index(substring_index(transferor, ':', 3), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as transferor_three,
 
-                                                replace(substring_index(transferor_date, ':', 1), ',', '') as transferor_date_one,
 
-                                                replace(
 
-                                                        substring_index(substring_index(transferor_date, ':', 2), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as transferor_date_two,
 
-                                                replace(
 
-                                                        substring_index(substring_index(transferor_date, ':', 3), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as transferor_date_three,
 
-                                                replace(substring_index(assignee, ':', 1), ',', '')        as assignee_one,
 
-                                                replace(
 
-                                                        substring_index(substring_index(assignee, ':', 2), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as assignee_two,
 
-                                                replace(
 
-                                                        substring_index(substring_index(assignee, ':', 3), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as assignee_three,
 
-                                                substring_index(assignee_date, ':', 1)                     as assignee_date_one,
 
-                                                replace(
 
-                                                        substring_index(substring_index(assignee_date, ':', 2), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as assignee_date_two,
 
-                                                replace(
 
-                                                        substring_index(substring_index(assignee_date, ':', 3), ':', -1),
 
-                                                        ',',
 
-                                                        '')                                                as assignee_date_three
 
-                                         from (
 
-                                                    select account_id,
 
- 	                                                        group_concat(transferor, ':')      as transferor,
 
- 	                                                        group_concat(transferor_date, ':') as transferor_date,
 
- 	                                                        group_concat(assignee, ':')        as assignee,
 
- 	                                                        group_concat(assignee_date, ':')   as assignee_date
 
- 	                                                 from (select account_id,transferor,transferor_date,assignee,assignee_date from
 
- 	                                                      `jeecg-boot`.ctop_transfer_account
 
- 	                                                 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
 
- 	                                                   and status != 2
 
- 	                                                order by transferor_date
 
- 	                                           )t
 
- 	                                        group by account_id) t
 
-                                     ) t
 
-                            ) b
 
-                                              on report.account_id = b.account_id
 
-                            where b.account_id is not null
 
-                              and transferor_date_three != ''
 
-                            order by stat_date) t
 
-                   where stat_date > date_format(transferor_date_three, '%Y%m%d')
 
-                     and stat_date <= {enddate}) t
 
-          group by stat_date, account_id, direct_rebate_real_charged_in_yuan,contract_rebate_real_charged_in_yuan
 
- -- 无转户
 
-          union all
 
-          select stat_date,
 
-                 a.account_id,
 
-                 contract_rebate_real_charged_in_yuan,
 
-                 direct_rebate_real_charged_in_yuan,
 
-                 user_id,
 
-                 project_id,
 
-                 project_name,
 
-                 company_id,
 
-                 company_name
 
-          from (select stat_date,
 
-                       account_id,
 
-                       contract_rebate_real_charged_in_yuan,
 
-                       direct_rebate_real_charged_in_yuan,
 
-                       user_id,
 
-                       project_id,
 
-                       project_name,
 
-                       company_id,
 
-                       company_name
 
-                from application.app_kuaishou_agent_report_d
 
-                where stat_date between {start_date} and {enddate}) a
 
-                   left join
 
-               (
 
-                   select account_id
 
-                   from `jeecg-boot`.ctop_transfer_account
 
-                   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
 
-                     and status = 1
 
-               ) b
 
-               on a.account_id = b.account_id
 
-          where b.account_id is null
 
-      ) t
 
-          left join `jeecg-boot`.sys_user user
 
-                    on transferor_one = user.id
 
-          left join `jeecg-boot`.sys_user leader
 
-                    on user.leader_id = leader.id
 
- where user.id is not null
 
- group by stat_date, account_id, contract_rebate_real_charged_in_yuan,direct_rebate_real_charged_in_yuan
 
-             """.format(start_date=start_date, enddate=end_date)
 
-             print(sql)
 
-             TidbProConn.upsert(conn=self.conn, sql_buff=sql)
 
-             self.conn.commit()
 
-             TidbProConn.conn_close(conn=self.conn)
 
-         except Exception as e:
 
-             logging.error(e)
 
-             return -1
 
- if __name__ == '__main__':
 
-     KuaishouOperationPerformanceBackChargeBase().handler()
 
 
  |