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 1501435553
- import datetime
- import logging
- from Apietl.conn.TidbConn import TidbConn
- class 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
|