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, 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)
|