project_material_report.py 3.4 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384
  1. # Author renyupeng
  2. # coding=utf-8
  3. # @Time : 2023/8/9 上午11:22
  4. # @Site :
  5. # @File : project_material_report.py
  6. # @Software: PyCharm
  7. # @contact: renyupeng@c-top.com.cn
  8. # @Tel 1501435553
  9. import datetime
  10. from urllib import parse
  11. import pandas as pd
  12. from sqlalchemy import create_engine
  13. from Apietl.conn.TidbConn import TidbConn
  14. class project_material_report:
  15. def __init__(self):
  16. self.conn = TidbConn.get_connection()
  17. self.OuterStr = "mysql+pymysql://%s:%s@%s:%d/%s" % (
  18. "hcst", parse.quote_plus("hcst@2024"), "192.168.0.101", 3390, "application")
  19. self.OuterConn = create_engine(self.OuterStr,
  20. pool_size=10,
  21. max_overflow=5,
  22. pool_timeout=10,
  23. pool_recycle=3600)
  24. def Project_Handler(self):
  25. sql = """
  26. select project_id,replace(project_name,'/','-') from application.kuaishou_account_report_daily_dw where stat_date>=20240101 and stat_date<=20240331
  27. and charge>0 and project_id is not null
  28. group by project_id
  29. """
  30. project_info_list = TidbConn.quary(self.conn, sql)
  31. for project_info in project_info_list:
  32. project_id = project_info[0]
  33. project_name = project_info[1]
  34. sql = """
  35. select (CASE
  36. WHEN a.clip_company_id = 'd57fecdcf7a94d009736d9c850731582' THEN
  37. '北京汇创思拓数字科技有限公司'
  38. WHEN a.clip_company_id = '6608ed13c0dd42de93c790dbdf124234' THEN
  39. '广州汇创思拓数字科技有限公司'
  40. WHEN a.clip_company_id = '4b10089775c040119e139087517aed88' THEN
  41. '上海汇创思拓数字科技有限公司'
  42. WHEN a.clip_company_id = '5e46b5dea3d740eeb8ff1a2895015a4b' THEN
  43. '北京骄阳数字科技有限公司'
  44. WHEN a.clip_company_id = '1648c7ceb4d449a9a13fbed7a8d9976c' THEN
  45. '杭州妙构思数字科技有限公司'
  46. WHEN a.clip_company_id = '3321d5893bcd430cab1238a99bc7db11' THEN
  47. '北京次元像素'
  48. WHEN a.clip_company_id='ace886fa51a244818e918b5d6d1bcc62' then
  49. '北京聚意创量文化传媒有限公司'
  50. when a.clip_company_id='3f362f527a804e8a9db6552ea12f7574'
  51. then '石家庄汇创思拓数字科技有限公司'
  52. else '-'
  53. END) as '公司',
  54. a.project_name AS '项目',
  55. a.signature AS '素材标识',
  56. b.frast_cost_time AS '上线时间',
  57. channel_name 渠道,
  58. a.charge AS '消耗',
  59. trim(a.clip_name) AS '剪辑',
  60. trim(a.shot_name) AS '拍摄',
  61. trim(a.plan_name) AS '编导',
  62. trim(a.leader_name) AS '负责人',
  63. a.video_name AS '素材名称',
  64. a.photo_url AS '链接' from
  65. (select clip_company_id,account_id,company_name,project_name,signature,channel_name,sum(charge)charge,clip_name,shot_name,plan_name,a.leader_name,video_name,photo_url,b.realname user_name from
  66. application.kuaishou_material_video_report_daily_dw a
  67. left join `jeecg-boot`.sys_user b
  68. on a.user_id=b.id
  69. where stat_date >= 20240101 and stat_date<=20240331 and project_id={project_id}
  70. group by signature) a
  71. left join
  72. application.material_farst_cost_time b
  73. on a.signature=b.signature
  74. """.format(project_id=project_id)
  75. path = "/data/excel/ks/{project_name}.xls".format(project_name=project_name)
  76. pd.read_sql_query(sql, self.OuterConn).to_excel(path, index=False, engine='openpyxl')
  77. if __name__ == '__main__':
  78. project_material_report().Project_Handler()