# 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, user_com.company_id, user_com.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 left join `jeecg-boot`.user_company user_com on user.id =user_com.user_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()