| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205 | # Author renyupeng# coding=utf-8# @Time    : 2021/9/15 5:10 下午# @Site    :# @File    : NewMaterialVideoDailyReport.py# @Software: PyCharm# @contact: renyupeng@c-top.com.cnimport loggingfrom Apietl.utlis.Utils import Utilsfrom Apietl.conn.TidbConn import TidbConnclass 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_namefrom 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_idwhere 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 -1if __name__ == '__main__':    status = NewMaterialVideoDailyReport().daily_report()    if status == 0:        print("作业执行成功")        exit(0)    else:        print("作业执行失败")        exit(-1)
 |