| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127 | # Author zhaohailiang# coding=utf-8# @Time    : 2021/12/20 2:22 下午# @Site    :# @File    : AppBytedanceAgentReport.py# @Software: PyCharm# @contact: zhaohailiang@c-top.com.cn# @Tel 18201631162import loggingfrom Apietl.conn.TidbConn import TidbConnfrom Apietl.utlis.Utils import Utilsclass AppBytedanceAgentReport:    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_bytedance_agent_report_d(account_id, stat_datetime, project_id, product_id, advertiser_id, company_id, account_name, project_name, advertiser_name, company_name, sale_id, user_id, org_code, agent_id, agent_name, second_industry, first_industry, account_source, audit_pass_time, account_status, account_company_id, account_company_name, register_time, cost, cash_cost, bid_cost, brand_cost, grants_cost, cpc_cost, cpm_cost, ocpc_cost, cpa_cost, ocpm_cost, cpv_cost, cpt_cost, gd_cost, `show`, cpm, click, ctr, cpc, transfer_count, today_cost, transfer_amount, history_cost, register_days, first_cost_time)select report.advertiser_id as account_id,       report.stat_datetime,       project_id,       product_id,       bei.advertiser_id,       bei.company_id,       account_name,       project_name,       bei.advertiser_name,       bei.company_name,       sale_id,       user_id,       org_code,       agent_id,       agent_name,       second_industry,       first_industry,       account_source,       audit_pass_time,       account_status,       report.company_id    as account_company_id,       report.company_name  as account_company_name,       register_time,       cost,       cash_cost,       bid_cost,       brand_cost,       grants_cost,       cpc_cost,       cpm_cost,       ocpc_cost,       cpa_cost,       ocpm_cost,       cpv_cost,       cpt_cost,       gd_cost,       `show`,       cpm,       click,       ctr,       cpc,       transfer_count,       today_cost,       transfer_amount,       history_cost,       register_days,       first_cost_timefrom media_api.bytedance_agent_report_daily report         LEFT JOIN (    SELECT cua.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_code    FROM `jeecg-boot`.ctop_user_allocation cua             LEFT JOIN `jeecg-boot`.ctop_project cp ON cua.project_id = cp.id             LEFT JOIN `jeecg-boot`.ctop_advertiser ca ON ca.id = cua.advertiser_id             LEFT JOIN `jeecg-boot`.sys_user su ON su.id = cua.user_id             LEFT JOIN `jeecg-boot`.sys_user sale ON sale.id = cp.sale_id             LEFT JOIN `jeecg-boot`.sys_user lea ON lea.id = su.leader_id             LEFT JOIN `jeecg-boot`.user_company uc ON uc.user_id = cua.user_id             LEFT JOIN `jeecg-boot`.ctop_product product ON product.id = cp.product_id    WHERE cua.account_id IS NOT NULL) bei                   on report.advertiser_id = bei.account_idwhere report.stat_datetime={date_time} """.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 = AppBytedanceAgentReport().taskHandler()    if status == 0:        print("作业执行成功")        exit(0)    else:        print("作业执行失败")
 |