send_material_report_weekly.py 5.4 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109
  1. # Author renyupeng
  2. # coding=utf-8
  3. # @Time : 2021/12/20 11:18 上午
  4. # @Site :
  5. # @File : send_material_report_weekly.py.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. import smtplib
  13. from email.mime.text import MIMEText
  14. from email.mime.multipart import MIMEMultipart
  15. from sqlalchemy import create_engine
  16. class send_material_report_weekly:
  17. def __init__(self):
  18. self.OuterStr = "mysql+pymysql://%s:%s@%s:%d/%s" % (
  19. "hcst", parse.quote_plus("hcst@2024"), "192.168.0.249", 3390, "application")
  20. self.OuterConn = create_engine(self.OuterStr,
  21. pool_size=10,
  22. max_overflow=5,
  23. pool_timeout=10,
  24. pool_recycle=3600)
  25. self.stat_date = (datetime.date.today() + datetime.timedelta(-1)).strftime("%Y%m%d")
  26. def handler(self):
  27. sql = """select (CASE
  28. WHEN t1.clip_company_id = 'd57fecdcf7a94d009736d9c850731582' THEN '北京汇创思拓数字科技有限公司'
  29. WHEN t1.clip_company_id = '6608ed13c0dd42de93c790dbdf124234' THEN
  30. '广州汇创思拓数字科技有限公司'
  31. WHEN t1.clip_company_id = '4b10089775c040119e139087517aed88' THEN
  32. '上海汇创思拓数字科技有限公司'
  33. WHEN t1.clip_company_id = '5e46b5dea3d740eeb8ff1a2895015a4b' THEN
  34. '北京骄阳数字科技有限公司'
  35. WHEN t1.clip_company_id = '1648c7ceb4d449a9a13fbed7a8d9976c' THEN
  36. '杭州妙构思数字科技有限公司'
  37. WHEN t1.clip_company_id = '3321d5893bcd430cab1238a99bc7db11' THEN
  38. '北京次元像素'
  39. WHEN t1.clip_company_id = 'ace886fa51a244818e918b5d6d1bcc62' then
  40. '北京聚意创量文化传媒有限公司'
  41. when t1.clip_company_id='3f362f527a804e8a9db6552ea12f7574' then '石家庄汇创思拓数字科技有限公司'
  42. when t1.clip_company_id is null and pro.agent_code = 1 then '北京汇创思拓数字科技有限公司'
  43. when t1.clip_company_id is null and pro.agent_code = 2 then '北京骄阳数字科技有限公司'
  44. when t1.clip_company_id is null and pro.agent_code = 3 then '上海汇创思拓数字科技有限公司'
  45. else '-'
  46. END) as '公司',
  47. ifnull(t1.project_name, '-') AS '项目',
  48. ifnull(t1.signature, '-') AS '素材标识',
  49. ifnull(t3.media_time, '-') AS '上线时间',
  50. ifnull(t1.channel_name, '-') AS '渠道',
  51. round(sum(t1.charge), 3) AS '消耗',
  52. ifnull(t1.clip_name, '-') AS '剪辑',
  53. ifnull(t1.shot_name, '-') AS '拍摄',
  54. ifnull(t1.plan_name, '-') AS '编导',
  55. ifnull(t1.leader_name, '-') AS '负责人',
  56. case
  57. when t3.material_innovate = 1 then '非创新素材'
  58. when t3.material_innovate = 2 then '创新素材'
  59. else '其它' end AS '是否创新素材',
  60. ifnull(t1.video_name, '-') AS '素材名称',
  61. ifnull(t1.photo_url, '-') AS '链接'
  62. from application.kuaishou_material_video_report_daily_dw t1
  63. left join (select material_id, media_time, company_name, material_innovate
  64. from application.app_kuaishou_material_info) t3
  65. on t1.signature = t3.material_id
  66. left join `jeecg-boot`.user_company t2
  67. on t1.user_id = t2.user_id
  68. left join `jeecg-boot`.ctop_project pro
  69. on t1.project_id = pro.id
  70. where t1.stat_date >= date_format(date_sub({stat_date}, INTERVAL 6 DAY), '%%Y%%m%%d') and t1.stat_date<={stat_date}
  71. group by t1.signature;""".format(stat_date=self.stat_date)
  72. print(sql)
  73. path = """/data/excel/kuaishou_material_week_{stat_date}.xls""".format(stat_date=self.stat_date)
  74. print(path)
  75. pd.read_sql(sql, self.OuterConn).to_excel(path, index=False, engine='openpyxl')
  76. my_sender = 'renyupeng@c-top.com.cn' # 发件人邮箱账号
  77. my_pass = 'fcyJKkHUyPo6Zztw' # 发件人邮箱授权码 / 腾讯企业邮箱请使用登陆密码
  78. recipients = ['wanglei@c-top.com.cn',
  79. 'shiyuke@s-hcst.com','fengxue@c-top.com.cn',
  80. 'lihuan@c-top.com.cn','kashipin@s-hcst.com'] # 收件人邮箱账号
  81. # content = "Please check the attachment. "
  82. msg = MIMEMultipart()
  83. # [发件人的邮箱昵称、发件人邮箱账号], 昵称随便
  84. msg['From'] = my_sender
  85. # [收件人邮箱昵称、收件人邮箱账号], 昵称随便
  86. msg['To'] = ",".join(recipients)
  87. # 邮件的主题,也就是邮件的标题
  88. msg['Subject'] = "快手-近一周素材消耗全明细"
  89. att1 = MIMEText(
  90. open(path, 'rb').read(), 'base64', 'utf-8')
  91. att1["Content-Type"] = 'application/octet-stream'
  92. att1["Content-Disposition"] = 'attachment; filename="kuaishou_material_report_weekly.xls"'
  93. msg.attach(att1)
  94. server = smtplib.SMTP_SSL("smtp.exmail.qq.com", port=465)
  95. server.login(my_sender, my_pass)
  96. try:
  97. server.sendmail(my_sender, recipients, msg.as_string())
  98. print("发送成功")
  99. except Exception as e:
  100. print("发送失败", e)
  101. if __name__ == '__main__':
  102. send_material_report_weekly().handler()