# Author renyupeng # coding=utf-8 # @Time : 2021/9/15 5:10 下午 # @Site : # @File : NewMaterialVideoDailyReport.py # @Software: PyCharm # @contact: renyupeng@c-top.com.cn import logging from Apietl.utlis.Utils import Utils from Apietl.conn.TidbConn import TidbConn class NewMaterialVideoDailyReport: def __init__(self): self.conn = TidbConn.get_connection() self.parm = Utils.get_live_args() self.start_time = self.parm def daily_report(self): try: sql = """select advertiser_id from media_api.bytedance_material_video_daily where stat_datetime = {start_time} group by advertiser_id""".format( start_time=self.start_time) account_list = TidbConn.quary(self.conn, sql) for account_id in account_list: try: sql = """ replace into application.bytedance_material_video_report_daily_dw(account_id, material_id, image_mode, inventory, stat_datetime, 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, signature, channel_type, clip_name, shot_name, plan_name, leader_name, video_name, channel_name, clip_company_id, cost, `show`, click, ctr, `convert`, convert_cost, convert_rate, deep_convert, deep_convert_cost, deep_convert_rate, download_start, download_start_cost, download_start_rate, download_finish, download_finish_cost, download_finish_rate, install_finish, install_finish_cost, install_finish_rate, active, active_cost, active_rate, register, active_register_rate, game_addiction, game_addiction_cost, game_addiction_rate, attribution_next_day_open_cnt, attribution_next_day_open_cost, attribution_next_day_open_rate, next_day_open, pay_count, active_pay_cost, active_pay_rate, game_pay_count, game_pay_cost, attribution_game_pay_7d_count, attribution_game_pay_7d_cost, in_app_uv, in_app_detail_uv, in_app_cart, in_app_pay, in_app_order, video_url, cover_url,shot_leader_id,shot_leader_name) select report.advertiser_id account_id, #'账户ID' report.material_id, #头条素材id '' as image_mode, #素材类型 '' as inventory, #广告位 stat_datetime, #数据起始时间 cua.project_id, #项目id product.id product_id, #项目中产品id cua.advertiser_id, #广告主id uc.company_id, #运营人的公司id cua.auth_name account_name, #账户名称 cp.project_name, #项目名称 ca.name advertiser_name, #广告主名称 uc.company_name, #运营人的公司名称 cp.sale_id, #项目里的销售id cua.user_id, #运营id cma.clip_id, #剪辑id cma.shot_id, #拍摄id cma.plan_id, #编导id cma.leader_id, #负责人id leader.org_code, #机构编码 video.signature, #素材id null chl_type, #渠道类型 clip.realname clip_name, #剪辑名称 shot.realname shot_name, #拍摄名称 plan.realname plan_name, #编导名称 leader.realname lead_name, #负责人名称 case when cmi.material_name is not null then cmi.material_name else video.filename end as video_name, ##视频名称 '' chl_name, #渠道名称 up.company_id clip_company_id, #剪辑公司 stat_cost as cost, show_cnt as `show`, click_cnt as click, ctr, convert_cnt as `convert`, conversion_cost as convert_cost, conversion_rate as convert_rate, deep_convert_cnt as deep_convert, deep_convert_cost, deep_convert_rate, click_start_cnt as download_start, click_start_cost as download_start_cost, click_start_rate as download_start_rate, download_finish_cnt as download_finish, download_finish_cost, download_finish_rate, install_finish_cnt as install_finish, install_finish_cost, install_finish_rate, active, active_cost, active_rate, active_register as register, active_register_rate, game_addiction, game_addiction_cost, game_addiction_rate, attribution_next_day_open_cnt, attribution_next_day_open_cost, attribution_next_day_open_rate, next_day_open, active_pay as pay_count, active_pay_cost, active_pay_rate, game_pay_count, game_pay_cost, attribution_game_pay_7d_count, attribution_game_pay_7d_cost, in_app_uv, in_app_detail_uv, in_app_cart, in_app_pay, in_app_order, ifnull(concat('http://i.snssdk.com/video/code/1/toutiao/',video.id),'-') as video_url , cmi.cover_url, cma.shot_leader_id, cma.shot_leader_name from media_api.bytedance_material_video_daily report left join `media_api`.bytedance_file_video_get video on report.advertiser_id = video.advertiser_id and report.material_id = video.material_id left join (select code, material_name, cover_url from `jeecg-boot`.ctop_material_info where status = 1) cmi on video.signature = cmi.code left join `jeecg-boot`.ctop_material_ascription cma on video.signature = cma.material_id left join `jeecg-boot`.ctop_user_allocation cua on report.advertiser_id = cua.account_id left join `jeecg-boot`.ctop_advertiser ca on cua.advertiser_id = ca.id left join `jeecg-boot`.ctop_project cp on cua.project_id = cp.id left join `jeecg-boot`.user_company uc on cua.user_id = uc.user_id left join `jeecg-boot`.ctop_product product on cp.product_id = product.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 up on up.user_id = cma.clip_id where report.stat_datetime ={start_time} and report.advertiser_id = {account_id} """.format(start_time=self.start_time, account_id=account_id[0]) print(sql) TidbConn.upsert(conn=self.conn, sql_buff=sql) self.conn.commit() except Exception as e: logging.error(e) return -1 TidbConn.conn_close(conn=self.conn) return 0 except Exception as e: logging.error(e) return -1 if __name__ == '__main__': status = NewMaterialVideoDailyReport().daily_report() if status == 0: print("作业执行成功") exit(0) else: print("作业执行失败") exit(-1)