| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081 | # Author renyupeng# coding=utf-8# @Time    : 2022/3/24 9:53 上午# @Site    : # @File    : EtlFirstDeliveryValidMaterials.py# @Software: PyCharm# @contact: renyupeng@c-top.com.cn# @Tel 1501435553import datetimeimport loggingfrom utils.mysql_utils import MysqlUtilsclass 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 -1if __name__ == '__main__':    EtlFirstDeliveryValidMaterials().handler()
 |