TodayMaterialUploadNum.py 5.4 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142
  1. # Author zhaohailiang
  2. # coding=utf-8
  3. # @Time : 2022/01/19 21:10 下午
  4. # @Site :
  5. # @File : TodayMaterialUploadNum.py
  6. # @Software: PyCharm
  7. # @contact: renyupeng@c-top.com.cn
  8. # @Tel 15011435553
  9. import datetime
  10. import logging
  11. from Apietl.conn.TidbConn import TidbConn
  12. from Apietl.utlis.Utils import Utils
  13. class TodayMaterialUploadNum:
  14. def __init__(self):
  15. self.conn = TidbConn.get_connection()
  16. self.date_time = (datetime.date.today() + datetime.timedelta(0)).strftime("%Y%m%d")
  17. def taskHandler(self):
  18. try:
  19. sql = """
  20. replace into application.today_material_upload_num(stat_date, hour, type_id, dimension, dimension_name,
  21. material_upload_adsp_num, material_upload_media_num)
  22. select stat_date,
  23. hour,
  24. type_id,
  25. project_id,
  26. project_name,
  27. sum(material_upload_adsp_num) as material_upload_adsp_num,
  28. sum(material_upload_media_num) as material_upload_media_num
  29. from (
  30. select date_format(create_time, '%Y%m%d') as stat_date,
  31. hour(create_time) as hour,
  32. 1 as type_id,
  33. project_id,
  34. if(status = 1, 1, 0) as material_upload_adsp_num,
  35. if(sync_kuaishou = 2 or sync_bytedance=2, 1, 0) as material_upload_media_num
  36. from `jeecg-boot`.ctop_material_info
  37. where date_format(create_time, '%Y%m%d') = {stat_date}
  38. and status = 1
  39. ) t
  40. left join `jeecg-boot`.ctop_project pro
  41. on t.project_id = pro.id
  42. group by stat_date, hour, project_id
  43. union all
  44. select stat_date,
  45. hour,
  46. type_id,
  47. shot_id,
  48. realname as shot_name,
  49. sum(material_upload_adsp_num) as material_upload_adsp_num,
  50. sum(material_upload_media_num) as material_upload_media_num
  51. from (
  52. select date_format(info.create_time, '%Y%m%d') as stat_date,
  53. hour(info.create_time) as hour,
  54. 2 as type_id,
  55. shot_id,
  56. if(status = 1, 1, 0) as material_upload_adsp_num,
  57. if(sync_kuaishou = 2, 1, 0) as material_upload_media_num
  58. from `jeecg-boot`.ctop_material_info info
  59. left join `jeecg-boot`.ctop_material_ascription design
  60. on info.code = design.material_id
  61. where date_format(info.create_time, '%Y%m%d') = {stat_date}
  62. and status = 1
  63. ) t
  64. left join `jeecg-boot`.sys_user user
  65. on t.shot_id = user.id
  66. where shot_id != ''
  67. group by stat_date, hour, shot_id
  68. union all
  69. select stat_date,
  70. hour,
  71. type_id,
  72. plan_id,
  73. realname as plan_name,
  74. sum(material_upload_adsp_num) as material_upload_adsp_num,
  75. sum(material_upload_media_num) as material_upload_media_num
  76. from (
  77. select date_format(info.create_time, '%Y%m%d') as stat_date,
  78. hour(info.create_time) as hour,
  79. 3 as type_id,
  80. plan_id,
  81. if(status = 1, 1, 0) as material_upload_adsp_num,
  82. if(sync_kuaishou = 2, 1, 0) as material_upload_media_num
  83. from `jeecg-boot`.ctop_material_info info
  84. left join `jeecg-boot`.ctop_material_ascription design
  85. on info.code = design.material_id
  86. where date_format(info.create_time, '%Y%m%d') = {stat_date}
  87. and status = 1
  88. ) t
  89. left join `jeecg-boot`.sys_user user
  90. on t.plan_id = user.id
  91. where plan_id != ''
  92. group by stat_date, hour, plan_id
  93. union all
  94. select stat_date,
  95. hour,
  96. type_id,
  97. clip_id,
  98. realname as clip_name,
  99. sum(material_upload_adsp_num) as material_upload_adsp_num,
  100. sum(material_upload_media_num) as material_upload_media_num
  101. from (
  102. select date_format(info.create_time, '%Y%m%d') as stat_date,
  103. hour(info.create_time) as hour,
  104. 4 as type_id,
  105. clip_id,
  106. if(status = 1, 1, 0) as material_upload_adsp_num,
  107. if(sync_kuaishou = 2, 1, 0) as material_upload_media_num
  108. from `jeecg-boot`.ctop_material_info info
  109. left join `jeecg-boot`.ctop_material_ascription design
  110. on info.code = design.material_id
  111. where date_format(info.create_time, '%Y%m%d') = {stat_date}
  112. and status = 1
  113. ) t
  114. left join `jeecg-boot`.sys_user user
  115. on t.clip_id = user.id
  116. where clip_id != ''
  117. group by stat_date, hour, clip_id
  118. """.format(stat_date=self.date_time)
  119. print(sql)
  120. TidbConn.upsert(conn=self.conn, sql_buff=sql)
  121. self.conn.commit()
  122. TidbConn.conn_close(conn=self.conn)
  123. return 0
  124. except Exception as e:
  125. logging.error(e)
  126. return -1
  127. if __name__ == '__main__':
  128. status = TodayMaterialUploadNum().taskHandler()
  129. if status == 0:
  130. print("作业执行成功")
  131. exit(0)
  132. else:
  133. print("作业执行失败")
  134. exit(-1)