# Author renyupeng # coding=utf-8 # @Time : 2021/9/15 5:10 下午 # @Site : # @File : MateirialVideoDailyReport.py # @Software: PyCharm # @contact: renyupeng@c-top.com.cn import logging from Apietl.conn.TidbConn import TidbConn from Apietl.utlis.Utils import Utils class AdqMaterialReportDaily(object): def __init__(self): self.conn = TidbConn.get_connection() self.parm = Utils.get_args() self.start_time = self.parm[0] self.end_time = self.parm[1] def daily_report(self): try: sql = """ select advertiser_id from media_api.adq_material_report_daily where stat_time_day >= {start_time} and stat_time_day<={end_time} group by advertiser_id """.format(start_time=self.start_time, end_time=self.end_time) account_list = TidbConn.quary(self.conn, sql) for account_id in account_list: sql = """ replace into application.adq_material_report_daily_dw(account_id, project_id, product_id, advertiser_id, company_id, account_name, project_name, advertiser_name, company_name, sale_id, user_id, clip_id, shot_id, plan_id, leader_id, org_code, photo_url, material_id, stat_date, material_type, 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, ad_monetization_amount, app_ad_paying_users, income_pv_pla, income_val_1, ad_paying_users_d1, income_pv_1d_pla, income_val_3, signature, channel_type, clip_name, shot_name, plan_name, leader_name, video_name, channel_name, clip_company_id, play_3s_count, shot_leader_id, shot_leader_name) select report.advertiser_id as account_id, cp.id as project_id, product.id as product_id, ca.id as advertiser_id, uc.company_id, cua.auth_name as account_name, cp.project_name as project_name, ca.name as advertiser_name, uc.company_name, cp.sale_id, cua.user_id, clip_id, shot_id, plan_id, cma.leader_id, leader.org_code, video.preview_url as photo_url, report.material_id, report.stat_time_day as stat_date, report.material_type, report.cost / 100 as cost, report.view_count, report.valid_click_count, report.conversions_count, report.deep_conversions_count, report.activated_count, report.reg_pv, report.retention_count, report.video_outer_play_count, report.video_outer_play100_count, report.key_behavior_conversions_count, report.purchase_pv, report.purchase_amount / 100 as purchase_amount, report.first_pay_count, report.ad_monetization_amount, report.app_ad_paying_users, report.income_pv_pla, report.income_val_1 / 100 as income_val_1, report.ad_paying_users_d1, report.income_pv_1d_pla, report.income_val_3 / 100 as income_val_3, video.signature, null as channel_type, clip.realname, shot.realname, plan.realname, leader.realname, video.description as video_name, null channel_name, uc.company_id, 0 as play_3s_count, cma.shot_leader_id, cma.shot_leader_name from media_api.adq_material_report_daily report left join media_api.adq_video_info video on report.material_id = video.video_id left join (select code, material_name from `jeecg-boot`.ctop_material_info where status = 1) cmi on video.signature = cmi.code left join `jeecg-boot`.ctop_material_ascription cma on cma.material_id = video.signature left join `jeecg-boot`.ctop_user_allocation cua on cua.account_id = report.advertiser_id 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 clip on clip.id = cma.clip_id left join `jeecg-boot`.sys_user shot on shot.id = cma.shot_id left join `jeecg-boot`.sys_user plan on plan.id = cma.plan_id left join `jeecg-boot`.sys_user leader on leader.id = cma.leader_id left join `jeecg-boot`.user_company uc on uc.user_id = clip.id left join `jeecg-boot`.ctop_product product on product.id = cp.product_id where report.stat_time_day >= {start_time} and stat_time_day <= {end_time} and report.advertiser_id = {account_id} """.format(start_time=self.start_time, end_time=self.end_time, account_id=account_id) TidbConn.execute(self.conn, sql) except Exception as e: logging.error(e)