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