| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205 | 
							- # 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)
 
 
  |