EtlFirstDeliveryValidMaterials.py 3.1 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980
  1. # Author renyupeng
  2. # coding=utf-8
  3. # @Time : 2022/3/24 9:53 上午
  4. # @Site :
  5. # @File : EtlFirstDeliveryValidMaterials.py
  6. # @Software: PyCharm
  7. # @contact: renyupeng@c-top.com.cn
  8. # @Tel 1501435553
  9. import datetime
  10. import logging
  11. from Apietl.conn.TidbConn import TidbConn
  12. class EtlFirstDeliveryValidMaterials:
  13. def __init__(self):
  14. self.conn = TidbConn.get_connection()
  15. self.stat_date = (datetime.date.today() + datetime.timedelta(0)).strftime("%Y%m%d")
  16. def handler(self):
  17. try:
  18. sql = """
  19. replace into `jeecg-boot`.`etl_first_delivery_valid_materials`
  20. select `delivery_month`,
  21. `operation_label`,
  22. a.`material_id`,
  23. e.signature,
  24. `shot_leader_id`,
  25. `shot_leader_name`,
  26. f.`shot_id`,
  27. f.`plan_id`,
  28. f.`leader_id`,
  29. f.`clip_name`,
  30. f.`shot_name`,
  31. f.`plan_name`,
  32. f.`leader_name`,
  33. c.id as `project_id`,
  34. c.project_name,
  35. c.responsible_id project_leader_id,
  36. d.realname as project_leader_name,
  37. `stat_date`,
  38. `customer_name`,
  39. `rebate_type`,
  40. `settlement_1l`,
  41. `settlement_2l`,
  42. `first_valid`,
  43. `cost_7day`,
  44. `cost`,
  45. date_format(stat_date,'%Y') as `delivery_year`,
  46. c.design_responsible_id as project_design_leader_id,
  47. h.realname as project_design_leader_name,
  48. f.clip_id as clip_id
  49. from `jeecg-boot`.first_delivery_valid_materials a
  50. left join `jeecg-boot`.ctop_user_allocation b
  51. on a.account_id = b.account_id
  52. left join `jeecg-boot`.ctop_project c
  53. on b.project_id = c.id
  54. left join `jeecg-boot`.sys_user d
  55. on c.responsible_id = d.id
  56. left join application.bytedance_video_get e
  57. on a.material_id = e.material_id
  58. left join application.app_bytedance_material_info f
  59. on e.signature = f.material_id
  60. left join `jeecg-boot`.ctop_material_ascription g
  61. on e.signature = g.material_id
  62. left join `jeecg-boot`.sys_user h
  63. on c.design_responsible_id = h.id
  64. where c.id is not null
  65. group by a.delivery_month, a.material_id, signature, b.project_id, settlement_2l, delivery_year
  66. """.format(stat_date=self.stat_date)
  67. print(sql)
  68. TidbConn.upsert(conn=self.conn, sql_buff=sql)
  69. truncate_sql = "truncate table `jeecg-boot`.first_delivery_valid_materials"
  70. TidbConn.exec_sql(conn=self.conn, sql_buff=truncate_sql)
  71. self.conn.commit()
  72. TidbConn.conn_close(conn=self.conn)
  73. return 0
  74. except Exception as e:
  75. logging.error(e)
  76. return -1