AttractInvestmentEmail.py 7.0 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118
  1. # Author renyupeng
  2. # coding=utf-8
  3. # @Time : 2024/9/26 16:07
  4. # @Site :
  5. # @File : AttractInvestmentEmail.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 AttractInvestmentEmail:
  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. from (select t1.user_id as 'userId',
  31. t1.user_name as '名称',
  32. ifnull(count(distinct t2.item_id),'-') as '商品量',
  33. ifnull(count(t2.oid) ,'-') as '订单数',
  34. ifnull(sum(case when t2.order_status != 80 then 1 else 0 end) ,'-') as '有效订单数',
  35. ifnull(round(sum(case when t2.order_status != 80 then 1 else 0 end) / count(t2.oid), 4),'-') as '有效率',
  36. ifnull(sum(case when t2.send_status = 1 then 1 else 0 end),'-') as '发货数',
  37. ifnull(round(sum(case when t2.send_status = 1 and t2.order_status != 80 then 1 else 0 end) / sum(case
  38. when
  39. t2.order_status != 80
  40. then 1
  41. else 0 end),
  42. 4),'-') as '发货率',
  43. ifnull(sum(case when t2.send_status = 1 and t2.order_status != 80 then 1 else 0 end),'-') as '有效发货数',
  44. ifnull(sum(case when t2.send_status = 0 and t2.order_status != 80 then 1 else 0 end),'-') as '未发货数',
  45. ifnull(round(sum(case when t2.send_status = 1 and t2.order_status != 80 then 1 else 0 end) / sum(case
  46. when send_status = 1
  47. then 1
  48. else 0 end),
  49. 4) ,'-') as '有效发货率',
  50. ifnull(sum(case when t2.order_status = 80 then 1 else 0 end),'-') as '失效订单数',
  51. ifnull(sum(case when t2.order_status != 80 then t2.pay_amount/100 else 0 end) ,'-') as 'GMV',
  52. ifnull(sum(case
  53. when t2.order_status != 80 then t2.regimental_promotion_amount/100
  54. else 0 end),'-') as '预估服务费',
  55. ifnull(sum(case when t2.order_status != 80 then t2.total_regimental_settle_amount/100 else 0 end),'-') as
  56. '结算服务费'
  57. from {database}.kuaishou_item_bind_user t1
  58. left join {database}.kuaishou_supply_chain t2
  59. on t1.item_id = t2.item_id
  60. where 1 = 1
  61. and t2.stat_date >= DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%%Y%%m01')
  62. and t2.stat_date <= DATE_FORMAT(LAST_DAY(NOW() - INTERVAL 1 MONTH), '%%Y%%m%%d')
  63. group by t1.user_id) t
  64. left join (select item_create_id, count(1) as 'sendSampleCount'
  65. from {database}.kuaishou_item_collect_samples
  66. where courier_number_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 %%H:%%i:%%s')
  67. and courier_number_time <= 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')
  68. group by item_create_id) t1 on t.userId = t1.item_create_id
  69. where 1 = 1
  70. order by t.订单数 desc
  71. """.format(database=keys)
  72. print(sql)
  73. path = """/data/excel/招商-{database}-{stat_date}.xls""".format(database=values, 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 = ['niuxiaoyu@c-top.com.cn', 'lishiwei@c-top.com.cn'] # 收件人邮箱账号
  79. # content = "Please check the attachment. "
  80. msg = MIMEMultipart()
  81. # [发件人的邮箱昵称、发件人邮箱账号], 昵称随便
  82. msg['From'] = my_sender
  83. # [收件人邮箱昵称、收件人邮箱账号], 昵称随便
  84. msg['To'] = ",".join(recipients)
  85. # 邮件的主题,也就是邮件的标题
  86. msg['Subject'] = "招商-{}".format(values)
  87. att1 = MIMEText(
  88. open(path, 'rb').read(), 'base64', 'utf-8')
  89. att1["Content-Type"] = 'application/octet-stream'
  90. filename = '{key}-{stat_date}.xlsx'.format(key=keys, stat_date=self.stat_date)
  91. encoded_filename = urllib.parse.quote(filename)
  92. att1["Content-Disposition"] = 'attachment; filename="{encoded_filename}"'.format(
  93. encoded_filename=encoded_filename)
  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. database_mapping = {'yufu': '渔夫', 'miaogousi': '食品', 'ruixuan': '服装', 'rocket': '年轻'}
  104. for key, value in database_mapping.items():
  105. AttractInvestmentEmail().handler(key, value)