# Author renyupeng # coding=utf-8 # @Time : 2022/3/24 9:53 上午 # @Site : # @File : EtlFirstDeliveryValidMaterials.py # @Software: PyCharm # @contact: renyupeng@c-top.com.cn # @Tel 1501435553 import datetime import logging from utils.mysql_utils import MysqlUtils class EtlFirstDeliveryValidMaterials: def __init__(self): self.stat_date = (datetime.date.today() + datetime.timedelta(0)).strftime("%Y%m%d") def handler(self): try: sql = """ replace into `jeecg-boot`.`etl_first_delivery_valid_materials` select `delivery_month`, `operation_label`, a.`material_id`, e.signature, `shot_leader_id`, `shot_leader_name`, f.`shot_id`, f.`plan_id`, f.`leader_id`, f.`clip_name`, f.`shot_name`, f.`plan_name`, f.`leader_name`, c.id as `project_id`, c.project_name, c.responsible_id project_leader_id, d.realname as project_leader_name, `stat_date`, `customer_name`, `rebate_type`, `settlement_1l`, `settlement_2l`, `first_valid`, `cost_7day`, `cost`, date_format(stat_date,'%Y') as `delivery_year`, c.design_responsible_id as project_design_leader_id, h.realname as project_design_leader_name, f.clip_id as clip_id from `jeecg-boot`.first_delivery_valid_materials a left join `jeecg-boot`.ctop_user_allocation b on a.account_id = b.account_id left join `jeecg-boot`.ctop_project c on b.project_id = c.id left join `jeecg-boot`.sys_user d on c.responsible_id = d.id left join application.bytedance_video_get e on a.material_id = e.material_id left join application.app_bytedance_material_info f on e.signature = f.material_id left join `jeecg-boot`.ctop_material_ascription g on e.signature = g.material_id left join `jeecg-boot`.sys_user h on c.design_responsible_id = h.id where c.id is not null group by a.delivery_month, a.material_id, signature, b.project_id, settlement_2l, delivery_year """.format(stat_date=self.stat_date) print(sql) MysqlUtils().Operate(sql=sql) truncate_sql = "truncate table `jeecg-boot`.first_delivery_valid_materials" MysqlUtils().Operate(sql=truncate_sql) return 0 except Exception as e: logging.error(e) return -1 if __name__ == '__main__': EtlFirstDeliveryValidMaterials().handler()