# Author renyupeng # coding=utf-8 # @Time : 2021/9/15 5:10 下午 # @Site : # @File : AdqAdvertiserReport.py # @Software: PyCharm # @contact: renyupeng@c-top.com.cn import logging from Apietl.conn.TidbConn import TidbConn class AdqAdvertiserReport: def __init__(self): self.conn = TidbConn.get_connection() def daily_report(self, account_id, start_date, end_date): try: sql = """ replace into application.adq_advertiser_report_daily(account_id, stat_date, project_id, product_id, account_name, project_name, advertiser_name, company_name, sale_id, user_id, org_code, cost, view_count, valid_click_count, conversions_count, deep_conversions_count, activated_count, reg_pv, retention_count, video_outer_play_count, video_outer_play100_count, key_behavior_conversions_count, purchase_pv, purchase_amount, first_pay_count, income_pv_1d_pla, ad_paying_users_d1, income_val_1, income_pv_pla, app_ad_paying_users, ad_monetization_amount, create_time, update_time) select report.advertiser_id as account_id, stat_time_day as stat_date, project_id, product_id, account_name, project_name, advertiser_name, company_name, sale_id, user_id, org_code, cost/100 as cost, view_count, valid_click_count, conversions_count, deep_conversions_count, activated_count, reg_pv, retention_count, video_outer_play_count, video_outer_play100_count, key_behavior_conversions_count, purchase_pv, purchase_amount/100 as purchase_amount, first_pay_count, income_pv_1d_pla, ad_paying_users_d1, income_val_1/100 as income_val_1, income_pv_pla, app_ad_paying_users, ad_monetization_amount/100 as ad_monetization_amount, create_time, update_time from media_api.adq_advertiser_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, su.id user_id, su.leader_id, 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`.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) sys on report.advertiser_id = sys.account_id where report.stat_time_day >= {start_date} and report.stat_time_day<={end_date} and report.advertiser_id = {account_id} """.format(start_date=start_date, end_date=end_date, account_id=account_id) TidbConn.upsert(conn=self.conn, sql_buff=sql) self.conn.commit() TidbConn.conn_close(conn=self.conn) print(account_id, 'adq账户日报执行完成') except Exception as e: logging.error(account_id, '执行失败') def hourly_report(self, account_id, start_date, end_date): try: sql = """ replace into application.adq_advertiser_report_hourly(account_id, stat_date,stat_hour, project_id, product_id, account_name, project_name, advertiser_name, company_name, sale_id, user_id, org_code, cost, view_count, valid_click_count, conversions_count, deep_conversions_count, activated_count, reg_pv, retention_count, video_outer_play_count, video_outer_play100_count, key_behavior_conversions_count, purchase_pv, purchase_amount, first_pay_count, income_pv_1d_pla, ad_paying_users_d1, income_val_1, income_pv_pla, app_ad_paying_users, ad_monetization_amount, create_time, update_time) select report.advertiser_id as account_id, stat_time_day as stat_date, `hour` as stat_hour, project_id, product_id, account_name, project_name, advertiser_name, company_name, sale_id, user_id, org_code, cost/100 as cost, view_count, valid_click_count, conversions_count, deep_conversions_count, activated_count, reg_pv, retention_count, video_outer_play_count, video_outer_play100_count, key_behavior_conversions_count, purchase_pv, purchase_amount/100 as purchase_amount, first_pay_count, income_pv_1d_pla, ad_paying_users_d1, income_val_1/100 as income_val_1, income_pv_pla, app_ad_paying_users, ad_monetization_amount/100 as ad_monetization_amount, create_time, update_time from media_api.adq_advertiser_report_hour 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, su.id user_id, su.leader_id, 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`.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) sys on report.advertiser_id = sys.account_id where report.stat_time_day >= {start_date} and report.stat_time_day<={end_date} and report.advertiser_id = {account_id} """.format(start_date=start_date, end_date=end_date, account_id=account_id) TidbConn.upsert(conn=self.conn, sql_buff=sql) self.conn.commit() TidbConn.conn_close(conn=self.conn) print(account_id, 'adq账户时报执行完成') except Exception as e: logging.error(account_id, '执行失败') def handler(self, date_type, account_id, start_date, end_date): if date_type == "daily": self.daily_report(account_id, start_date, end_date) else: self.hourly_report(account_id, start_date, end_date)