| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160 | # Author zhaohailiang# coding=utf-8# @Time    : 2021/12/20 2:22 下午# @Site    :# @File    : AppKuaishouAgentReport.py# @Software: PyCharm# @contact: zhaohailiang@c-top.com.cn# @Tel 18201631162import loggingfrom Apietl.conn.TidbConn import TidbConnfrom Apietl.utlis.Utils import Utilsclass AppKuaishouAgentReport:    def __init__(self):        self.conn = TidbConn.get_connection()        self.parm = Utils.get_task_args()        self.date_time = self.parm[0]    def taskHandler(self):        try:            sql = """    SET sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';replace into `application`.`app_kuaishou_agent_report_d`(account_id,agent_id,kuaishou_id,stat_date,project_id,product_id,advertiser_id,company_id,account_name,project_name,advertiser_name,company_name,sale_id,sale_name,user_id,user_name,leader_id,leader_name,org_code,total_charged_in_yuan,total_balance_in_yuan,real_charged_in_yuan,total_rebate_real_charged_in_yuan,contract_rebate_real_charged_in_yuan,direct_rebate_real_charged_in_yuan,credit_real_charged_in_yuan,charge_day_on_day_percent,ad_photo_impression,ad_photo_click,ad_item_impression,ad_item_click,photo_click_ratio,item_click_ratio,charged_campaign_count,product_name,corporation_name,first_cost_day,industry,second_industry,union_event_pay_purchase_amount_7d,union_event_pay_purchase_amount_7d_roi)selectreport.advertiser_id account_id,report.agent_id,report.user_id kuaishou_id,report.stat_date,bei.project_id,bei.product_id,bei.advertiser_id,bei.company_id,report.account_name,bei.project_name,bei.advertiser_name,bei.company_name,bei.sale_id,bei.sale_name,bei.user_id,bei.user_name,bei.leader_id,bei.leader_name,bei.org_code,report.total_charged_in_yuan,report.total_balance_in_yuan,report.real_charged_in_yuan,report.total_rebate_real_charged_in_yuan,report.contract_rebate_real_charged_in_yuan,report.direct_rebate_real_charged_in_yuan,report.credit_real_charged_in_yuan,report.charge_day_on_day_percent,report.ad_photo_impression,report.ad_photo_click,report.ad_item_impression,report.ad_item_click,report.photo_click_ratio,report.item_click_ratio,report.charged_campaign_count,report.product_name,report.corporation_name,report.first_cost_day,report.industry,report.second_industry,report.union_event_pay_purchase_amount_7d,report.union_event_pay_purchase_amount_7d_roifrom (select * from media_api.kuaishou_agent_report_daily where stat_date={date_time}) reportLEFT JOIN (SELECTcua.account_id id,cua.account_id account_id,cp.id project_id,product.id product_id,ca.id advertiser_id,uc.company_id company_id,cua.auth_name account_name,cp.project_name project_name,ca.`name` advertiser_name,uc.company_name company_name,cp.sale_id sale_id,sale.realname sale_name,su.id user_id,su.realname user_name,su.leader_id,lea.realname leader_name,su.org_code org_codeFROM`jeecg-boot`.ctop_user_allocation cuaLEFT JOIN `jeecg-boot`.ctop_project cp ON cua.project_id = cp.idLEFT JOIN `jeecg-boot`.ctop_advertiser ca ON ca.id = cua.advertiser_idLEFT JOIN `jeecg-boot`.sys_user su ON su.id = cua.user_idLEFT JOIN `jeecg-boot`.sys_user sale ON sale.id = cp.sale_idLEFT JOIN `jeecg-boot`.sys_user lea ON lea.id = su.leader_idLEFT JOIN `jeecg-boot`.user_company uc ON uc.user_id = cua.user_idLEFT JOIN `jeecg-boot`.ctop_product product ON product.id = cp.product_idWHEREcua.account_id IS NOT NULL) beiON report.advertiser_id = bei.account_id; """.format(date_time=self.date_time)            print(sql)            TidbConn.upsert(conn=self.conn, sql_buff=sql)            self.conn.commit()            TidbConn.conn_close(conn=self.conn)            return 0        except Exception as e:            logging.error(e)            return -1if __name__ == '__main__':    status = AppKuaishouAgentReport().taskHandler()    if status == 0:        print("作业执行成功")        exit(0)    else:        print("作业执行失败")
 |