| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142 | 
							- # Author zhaohailiang
 
- # coding=utf-8
 
- # @Time    : 2022/01/19 21:10 下午
 
- # @Site    :
 
- # @File    : TodayMaterialUploadNum.py
 
- # @Software: PyCharm
 
- # @contact: renyupeng@c-top.com.cn
 
- # @Tel 15011435553
 
- import datetime
 
- import logging
 
- from Apietl.conn.TidbConn import TidbConn
 
- from Apietl.utlis.Utils import Utils
 
- class TodayMaterialUploadNum:
 
-     def __init__(self):
 
-         self.conn = TidbConn.get_connection()
 
-         self.date_time = (datetime.date.today() + datetime.timedelta(0)).strftime("%Y%m%d")
 
-     def taskHandler(self):
 
-         try:
 
-             sql = """
 
- replace into application.today_material_upload_num(stat_date, hour, type_id, dimension, dimension_name,
 
-                                                    material_upload_adsp_num, material_upload_media_num)
 
- select stat_date,
 
-        hour,
 
-        type_id,
 
-        project_id,
 
-        project_name,
 
-        sum(material_upload_adsp_num)  as material_upload_adsp_num,
 
-        sum(material_upload_media_num) as material_upload_media_num
 
- from (
 
-          select date_format(create_time, '%Y%m%d') as stat_date,
 
-                 hour(create_time)                  as hour,
 
-                 1                                  as type_id,
 
-                 project_id,
 
-                 if(status = 1, 1, 0)               as material_upload_adsp_num,
 
-                 if(sync_kuaishou = 2 or sync_bytedance=2, 1, 0)        as material_upload_media_num
 
-          from `jeecg-boot`.ctop_material_info
 
-          where date_format(create_time, '%Y%m%d') = {stat_date}
 
-            and status = 1
 
-      ) t
 
-          left join `jeecg-boot`.ctop_project pro
 
-                    on t.project_id = pro.id
 
- group by stat_date, hour, project_id
 
- union all
 
- select stat_date,
 
-        hour,
 
-        type_id,
 
-        shot_id,
 
-        realname                       as shot_name,
 
-        sum(material_upload_adsp_num)  as material_upload_adsp_num,
 
-        sum(material_upload_media_num) as material_upload_media_num
 
- from (
 
-          select date_format(info.create_time, '%Y%m%d') as stat_date,
 
-                 hour(info.create_time)                  as hour,
 
-                 2                                       as type_id,
 
-                 shot_id,
 
-                 if(status = 1, 1, 0)                    as material_upload_adsp_num,
 
-                 if(sync_kuaishou = 2, 1, 0)             as material_upload_media_num
 
-          from `jeecg-boot`.ctop_material_info info
 
-                   left join `jeecg-boot`.ctop_material_ascription design
 
-                             on info.code = design.material_id
 
-          where date_format(info.create_time, '%Y%m%d') = {stat_date}
 
-            and status = 1
 
-      ) t
 
-          left join `jeecg-boot`.sys_user user
 
-                    on t.shot_id = user.id
 
- where shot_id != ''
 
- group by stat_date, hour, shot_id
 
- union all
 
- select stat_date,
 
-        hour,
 
-        type_id,
 
-        plan_id,
 
-        realname                       as plan_name,
 
-        sum(material_upload_adsp_num)  as material_upload_adsp_num,
 
-        sum(material_upload_media_num) as material_upload_media_num
 
- from (
 
-          select date_format(info.create_time, '%Y%m%d') as stat_date,
 
-                 hour(info.create_time)                  as hour,
 
-                 3                                       as type_id,
 
-                 plan_id,
 
-                 if(status = 1, 1, 0)                    as material_upload_adsp_num,
 
-                 if(sync_kuaishou = 2, 1, 0)             as material_upload_media_num
 
-          from `jeecg-boot`.ctop_material_info info
 
-                   left join `jeecg-boot`.ctop_material_ascription design
 
-                             on info.code = design.material_id
 
-          where date_format(info.create_time, '%Y%m%d') = {stat_date}
 
-            and status = 1
 
-      ) t
 
-          left join `jeecg-boot`.sys_user user
 
-                    on t.plan_id = user.id
 
- where plan_id != ''
 
- group by stat_date, hour, plan_id
 
- union all
 
- select stat_date,
 
-        hour,
 
-        type_id,
 
-        clip_id,
 
-        realname                       as clip_name,
 
-        sum(material_upload_adsp_num)  as material_upload_adsp_num,
 
-        sum(material_upload_media_num) as material_upload_media_num
 
- from (
 
-          select date_format(info.create_time, '%Y%m%d') as stat_date,
 
-                 hour(info.create_time)                  as hour,
 
-                 4                                       as type_id,
 
-                 clip_id,
 
-                 if(status = 1, 1, 0)                    as material_upload_adsp_num,
 
-                 if(sync_kuaishou = 2, 1, 0)             as material_upload_media_num
 
-          from `jeecg-boot`.ctop_material_info info
 
-                   left join `jeecg-boot`.ctop_material_ascription design
 
-                             on info.code = design.material_id
 
-          where date_format(info.create_time, '%Y%m%d') = {stat_date}
 
-            and status = 1
 
-      ) t
 
-          left join `jeecg-boot`.sys_user user
 
-                    on t.clip_id = user.id
 
- where clip_id != ''
 
- group by stat_date, hour, clip_id
 
-             """.format(stat_date=self.date_time)
 
-             print(sql)
 
-             TidbConn.upsert(conn=self.conn, sql_buff=sql)
 
-             self.conn.commit()
 
-             TidbConn.conn_close(conn=self.conn)
 
-             return 0
 
-         except Exception as e:
 
-             logging.error(e)
 
-             return -1
 
- if __name__ == '__main__':
 
-     status = TodayMaterialUploadNum().taskHandler()
 
-     if status == 0:
 
-         print("作业执行成功")
 
-         exit(0)
 
-     else:
 
-         print("作业执行失败")
 
-         exit(-1)
 
 
  |