| 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 15011435553import datetimeimport loggingfrom Apietl.conn.TidbConn import TidbConnfrom Apietl.utlis.Utils import Utilsclass 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_numfrom (         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.idgroup by stat_date, hour, project_idunion allselect 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_numfrom (         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.idwhere shot_id != ''group by stat_date, hour, shot_idunion allselect 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_numfrom (         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.idwhere plan_id != ''group by stat_date, hour, plan_idunion allselect 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_numfrom (         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.idwhere 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 -1if __name__ == '__main__':    status = TodayMaterialUploadNum().taskHandler()    if status == 0:        print("作业执行成功")        exit(0)    else:        print("作业执行失败")        exit(-1)
 |