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