IncentiveSettlementEmail.py 5.9 KB

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