| 1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980 | # 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 Apietl.conn.TidbConn import TidbConnclass EtlFirstDeliveryValidMaterials:    def __init__(self):        self.conn = TidbConn.get_connection()        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)            TidbConn.upsert(conn=self.conn, sql_buff=sql)            truncate_sql = "truncate table `jeecg-boot`.first_delivery_valid_materials"            TidbConn.exec_sql(conn=self.conn, sql_buff=truncate_sql)            self.conn.commit()            TidbConn.conn_close(conn=self.conn)            return 0        except Exception as e:            logging.error(e)            return -1
 |