CanalEmail.py 7.0 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132
  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
  36. t5.reg_name as `团名称`,
  37. t1.user_id as 'collectSampleId',
  38. t1.user_name as '绑定人名称',
  39. ifnull(count(t2.oid),'-') as '订单数',
  40. sum(case when order_status != 80 then 1 else 0 end) as '有效出单数',
  41. round(sum(case when order_status != 80 then 1 else 0 end) / count(oid) * 100,
  42. 2) as '有效率',
  43. round(sum(case when order_status != 80 then pay_amount else 0 end) / 100, 2) as 'Gvm',
  44. ifnull(round(sum(t2.order_amount) / 100, 2),'-') as '金额',
  45. ifnull(round(sum(t2.regimental_promotion_amount) / 100, 2) ,'-') as '预估服务费收入',
  46. ifnull( round(sum(t2.total_regimental_settle_amount) / 100, 2) ,'-') as '结算服务费收入'
  47. from {database}.promoter_bind_channel_date t1
  48. left join {database}.kuaishou_supply_chain t2
  49. on t1.promoter_id = t2.promoter_id and t1.stat_date = t2.stat_date
  50. left join (select id,reg_name from {database}.kuaishou_supply_chain_cookie union all
  51. select id,reg_name from miaogousi.kuaishou_supply_chain_cookie union all
  52. select id,reg_name from yufu.kuaishou_supply_chain_cookie union all
  53. select id,reg_name from rocket.kuaishou_supply_chain_cookie union all
  54. select id,reg_name from zhishui.kuaishou_supply_chain_cookie union all
  55. select id,reg_name from yuxiu.kuaishou_supply_chain_cookie
  56. group by id, reg_name)t5
  57. on t2.regimental_id=t5.id
  58. where 1 = 1
  59. and t1.media_id = 2
  60. and t1.stat_date >= DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%%Y%%m01')
  61. and t1.stat_date <= DATE_FORMAT(LAST_DAY(NOW() - INTERVAL 1 MONTH), '%%Y%%m%%d')
  62. group by t1.user_id) t
  63. left join (select user_id,
  64. count(distinct promoter_id) as 'promoterCount'
  65. from {database}.kuaishou_promoter
  66. group by user_id) tt on t.collectSampleId = tt.user_id
  67. left join (select collect_sample_id as 'user_id',
  68. count(distinct promoter_id) as 'sampleCount'
  69. from {database}.kuaishou_item_collect_samples
  70. where collect_sample_status > 2
  71. group by collect_sample_id) ttt on t.collectSampleId = ttt.user_id
  72. left join (select collect_sample_id as 'user_id',
  73. count(1) as 'sendTotalCount',
  74. count(distinct promoter_id) as 'sendPromoterCount',
  75. count(distinct item_id) as 'sendItemCount'
  76. from {database}.kuaishou_item_collect_samples
  77. where collect_sample_status > 2
  78. and create_time >= DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%%Y-%%m-01')
  79. and create_time < date_sub(DATE_FORMAT(LAST_DAY(NOW() - INTERVAL 1 MONTH), '%%Y-%%m-%%d'),interval -1 day)
  80. group by collect_sample_id) tttt on t.collectSampleId = tttt.user_id
  81. left join (select user_id, promoters_count
  82. from {database}.user_promoters_count
  83. where media_id = 2) ttttt
  84. on t.collectSampleId = ttttt.user_id
  85. where t.订单数 > 0
  86. order by t.订单数 desc
  87. """.format(database=keys)
  88. print(sql)
  89. path = """/data/excel/渠道-{database}-{stat_date}.xls""".format(database=values, stat_date=self.stat_date)
  90. print(path)
  91. pd.read_sql(sql, self.OuterConn).to_excel(path, index=False, engine='openpyxl')
  92. my_sender = 'renyupeng@c-top.com.cn' # 发件人邮箱账号
  93. my_pass = 'fcyJKkHUyPo6Zztw' # 发件人邮箱授权码 / 腾讯企业邮箱请使用登陆密码
  94. recipients = ['niuxiaoyu@c-top.com.cn', 'lishiwei@c-top.com.cn'] # 收件人邮箱账号
  95. # content = "Please check the attachment. "
  96. msg = MIMEMultipart()
  97. # [发件人的邮箱昵称、发件人邮箱账号], 昵称随便
  98. msg['From'] = my_sender
  99. # [收件人邮箱昵称、收件人邮箱账号], 昵称随便
  100. msg['To'] = ",".join(recipients)
  101. # 邮件的主题,也就是邮件的标题
  102. msg['Subject'] = "渠道-{}".format(values)
  103. att1 = MIMEText(
  104. open(path, 'rb').read(), 'base64', 'utf-8')
  105. att1["Content-Type"] = 'application/octet-stream'
  106. filename = '{key}-{stat_date}.xlsx'.format(key=keys, stat_date=self.stat_date)
  107. encoded_filename = urllib.parse.quote(filename)
  108. att1["Content-Disposition"] = 'attachment; filename="{encoded_filename}"'.format(
  109. encoded_filename=encoded_filename)
  110. msg.attach(att1)
  111. server = smtplib.SMTP_SSL("smtp.exmail.qq.com", port=465)
  112. server.login(my_sender, my_pass)
  113. try:
  114. server.sendmail(my_sender, recipients, msg.as_string())
  115. print("发送成功")
  116. except Exception as e:
  117. print("发送失败", e)
  118. if __name__ == '__main__':
  119. database_mapping = {'yufu': '渔夫', 'miaogousi': '食品', 'ruixuan': '服装', 'rocket': '年轻', 'yuxiu': '玉秀','zhishui':'止水'}
  120. for key, value in database_mapping.items():
  121. CanalEmail().handler(key, value)