CanalEmail.py 6.2 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118
  1. # Author renyupeng
  2. # coding=utf-8
  3. # @Time : 2024/9/26 16:08
  4. # @Site :
  5. # @File : CanalEmail.py
  6. # @Software: PyCharm
  7. # @contact: renyupeng@c-top.com.cn
  8. # @Tel 1501435553
  9. import datetime
  10. import urllib
  11. from urllib import parse
  12. import pandas as pd
  13. import smtplib
  14. from email.mime.text import MIMEText
  15. from email.mime.multipart import MIMEMultipart
  16. from sqlalchemy import create_engine
  17. class CanalEmail:
  18. def __init__(self):
  19. self.OuterStr = "mysql+pymysql://%s:%s@%s:%d/%s" % (
  20. "data", parse.quote_plus("hcst@2021"), "139.186.27.96", 3390, "application")
  21. self.OuterConn = create_engine(self.OuterStr,
  22. pool_size=10,
  23. max_overflow=5,
  24. pool_timeout=10,
  25. pool_recycle=3600)
  26. self.stat_date = (datetime.date.today() + datetime.timedelta(0)).strftime("%Y%m%d")
  27. def handler(self, keys, values):
  28. sql = """
  29. select t.*,
  30. ifnull(tt.promoterCount,'-') as '(总)达人数',
  31. ifnull(ttt.sampleCount,'-') as '(总)领样达人数',
  32. ifnull(tttt.sendTotalCount,'-') as '发样数',
  33. ifnull(tttt.sendPromoterCount,'-') as '发样商品数',
  34. ifnull(tttt.sendItemCount,'-') as '发样商品数'
  35. from (select t1.user_id as 'collectSampleId',
  36. t1.user_name as '绑定人名称',
  37. ifnull(sum(t2.order_num),'-') as '订单数',
  38. ifnull(sum(t2.valid_order_num),'-') as '有效订单数',
  39. ifnull(concat(round(sum(t2.valid_order_num) / sum(t2.order_num) * 100, 2), '%'),'-') as '有效率',
  40. ifnull(round(sum(t2.order_amount) / 100, 2),'-') as '金额',
  41. ifnull(round(sum(t2.regimental_promotion_amount) / 100, 2) ,'-') as '预估服务费收入',
  42. ifnull( round(sum(t2.total_regimental_settle_amount) / 100, 2) ,'-') as '结算服务费收入'
  43. from {database}promoter_bind_channel_date t1
  44. left join {database}promoter_item_order_count t2
  45. on t1.promoter_id = t2.promoter_id and t1.stat_date = t2.stat_date
  46. where 1 = 1
  47. and t1.media_id = 2
  48. and t1.stat_date >= DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y%m01')
  49. and t1.stat_date <= DATE_FORMAT(LAST_DAY(NOW() - INTERVAL 1 MONTH), '%Y%m%d')
  50. group by t1.user_id) t
  51. left join (select user_id,
  52. count(distinct promoter_id) as 'promoterCount'
  53. from {database}kuaishou_promoter
  54. group by user_id) tt on t.collectSampleId = tt.user_id
  55. left join (select collect_sample_id as 'user_id',
  56. count(distinct promoter_id) as 'sampleCount'
  57. from {database}kuaishou_item_collect_samples
  58. where collect_sample_status > 2
  59. group by collect_sample_id) ttt on t.collectSampleId = ttt.user_id
  60. left join (select collect_sample_id as 'user_id',
  61. count(1) as 'sendTotalCount',
  62. count(distinct promoter_id) as 'sendPromoterCount',
  63. count(distinct item_id) as 'sendItemCount'
  64. from {database}kuaishou_item_collect_samples
  65. where collect_sample_status > 2
  66. and create_time >= str_to_date(concat(date_format(DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-01'), '%Y-%m-%d'), '00:00:01'), '%Y-%m-%d
  67. %H:%i:%s')
  68. and create_time <=
  69. str_to_date(concat(date_format(DATE_FORMAT(LAST_DAY(NOW() - INTERVAL 1 MONTH), '%Y-%m-%d'), '%Y-%m-%d'), '23:59:59'), '%Y-%m-%d %H:%i:%s')
  70. group by collect_sample_id) tttt on t.collectSampleId = tttt.user_id
  71. left join (select user_id, promoters_count
  72. from {database}user_promoters_count
  73. where media_id = 2) ttttt
  74. on t.collectSampleId = ttttt.user_id
  75. where t.订单数 > 0
  76. order by t.订单数 desc
  77. """.format(database=keys)
  78. print(sql)
  79. path = """/data/excel/渠道-{database}-{stat_date}.xls""".format(database=values, stat_date=self.stat_date)
  80. print(path)
  81. pd.read_sql(sql, self.OuterConn).to_excel(path, index=False, engine='openpyxl')
  82. my_sender = 'renyupeng@c-top.com.cn' # 发件人邮箱账号
  83. my_pass = 'fcyJKkHUyPo6Zztw' # 发件人邮箱授权码 / 腾讯企业邮箱请使用登陆密码
  84. recipients = ['niuxiaoyu@c-top.com.cn','lishiwei@c-top.com.cn'] # 收件人邮箱账号
  85. # content = "Please check the attachment. "
  86. msg = MIMEMultipart()
  87. # [发件人的邮箱昵称、发件人邮箱账号], 昵称随便
  88. msg['From'] = my_sender
  89. # [收件人邮箱昵称、收件人邮箱账号], 昵称随便
  90. msg['To'] = ",".join(recipients)
  91. # 邮件的主题,也就是邮件的标题
  92. msg['Subject'] = "渠道-{}".format(values)
  93. att1 = MIMEText(
  94. open(path, 'rb').read(), 'base64', 'utf-8')
  95. att1["Content-Type"] = 'application/octet-stream'
  96. filename = '{key}-{stat_date}.xlsx'.format(key=keys, stat_date=self.stat_date)
  97. encoded_filename = urllib.parse.quote(filename)
  98. att1["Content-Disposition"] = 'attachment; filename="{encoded_filename}"'.format(
  99. encoded_filename=encoded_filename)
  100. msg.attach(att1)
  101. server = smtplib.SMTP_SSL("smtp.exmail.qq.com", port=465)
  102. server.login(my_sender, my_pass)
  103. try:
  104. server.sendmail(my_sender, recipients, msg.as_string())
  105. print("发送成功")
  106. except Exception as e:
  107. print("发送失败", e)
  108. if __name__ == '__main__':
  109. database_mapping = {'yufu': '渔夫', 'miaogousi': '食品', 'ruixuan': '服装', 'rocket': '年轻'}
  110. for key, value in database_mapping.items():
  111. CanalEmail().handler(key, value)