huaDongBytedanceMaterialReport.py 4.2 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110
  1. # Author renyupeng
  2. # coding=utf-8
  3. # @Time : 2021/12/20 11:18 上午
  4. # @Site :
  5. # @File : huaDongBytedanceMaterialReport
  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@2021"), "192.168.0.184", 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
  28. ifnull(stat_datetime,0) as '日期',
  29. ifnull(video.company_name,report.company_name) as '公司',
  30. ifnull(report.project_name,'-') AS '项目',
  31. ifnull(report.signature,'-') AS '素材标识',
  32. ifnull(video.farst_cost_time,'-') AS '上线时间',
  33. ifnull(report.channel_name,'-') AS '渠道',
  34. case when video.material_innovate=1 then '非创新素材'
  35. when video.material_innovate=2 then '创新素材'
  36. else '其它' end AS '是否创新素材',
  37. ifnull(sum(report.cost),0) AS '消耗',
  38. ifnull(report.clip_name,'-') AS '剪辑',
  39. ifnull(report.shot_name,'-') AS '拍摄',
  40. ifnull(report.plan_name,'-') AS '编导',
  41. ifnull(report.leader_name,'-') AS '负责人',
  42. ifnull(report.video_name,'-') AS '素材名称',
  43. ifnull(concat('http://i.snssdk.com/video/code/1/toutiao/',info.id),'-') AS '素材'
  44. from
  45. (select
  46. t1.stat_datetime,
  47. t1.account_id,
  48. t2.company_name,
  49. t1.material_id,
  50. t1.project_name ,
  51. t1.signature,
  52. t1.channel_name,
  53. t1.cost,
  54. t1.clip_name,
  55. t1.shot_name,
  56. t1.plan_name,
  57. t1.leader_name,
  58. t1.video_name,
  59. t1.video_url
  60. from application.bytedance_material_video_report_daily_dw t1
  61. left join `jeecg-boot`.user_company t2
  62. on t1.user_id=t2.user_id
  63. WHERE stat_datetime between concat(date_format(date_sub(now(),interval 1 day),'%%Y%%m'),'01') and date_format(date_sub(now(),interval 1 day),'%%Y%%m%%d')
  64. AND t2.company_id = '4b10089775c040119e139087517aed88') report
  65. left join
  66. application.app_bytedance_material_info video
  67. on report.signature=video.material_id
  68. left join (
  69. select id, signature from media_api.bytedance_file_video_get
  70. group by signature
  71. )info
  72. on report.signature=info.signature
  73. group by
  74. report.signature,stat_datetime;""".format(stat_date=self.stat_date)
  75. print(sql)
  76. path = """/data/excel/huaDongBytedanceMaterial.xls""".format(stat_date=self.stat_date)
  77. print(path)
  78. pd.read_sql(sql, self.OuterConn).to_excel(path, index=False, engine='openpyxl')
  79. my_sender = 'renyupeng@c-top.com.cn' # 发件人邮箱账号
  80. my_pass = 'fcyJKkHUyPo6Zztw' # 发件人邮箱授权码 / 腾讯企业邮箱请使用登陆密码
  81. recipients = ['panyuxia@c-top.com.cn', 'daipeng@c-top.com.cn'] # 收件人邮箱账号
  82. # content = "Please check the attachment. "
  83. msg = MIMEMultipart()
  84. # [发件人的邮箱昵称、发件人邮箱账号], 昵称随便
  85. msg['From'] = my_sender
  86. # [收件人邮箱昵称、收件人邮箱账号], 昵称随便
  87. msg['To'] = ",".join(recipients)
  88. # 邮件的主题,也就是邮件的标题
  89. msg['Subject'] = "头条-全量素材周报"
  90. att1 = MIMEText(
  91. open(path, 'rb').read(), 'base64', 'utf-8')
  92. att1["Content-Type"] = 'application/octet-stream'
  93. att1["Content-Disposition"] = 'attachment; filename="HuaDongMaterial.xls"'
  94. msg.attach(att1)
  95. server = smtplib.SMTP_SSL("smtp.exmail.qq.com", port=465)
  96. server.login(my_sender, my_pass)
  97. try:
  98. server.sendmail(my_sender, recipients, msg.as_string())
  99. print("发送成功")
  100. except Exception as e:
  101. print("发送失败", e)
  102. if __name__ == '__main__':
  103. send_material_report_weekly().handler()