AttractInvestmentEmail.py 7.8 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128
  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
  31. t5.reg_name as '团名称',
  32. t1.user_id as 'userId',
  33. t1.user_name as '名称',
  34. ifnull(count(distinct t2.item_id),'-') as '商品量',
  35. ifnull(count(t2.oid) ,'-') as '订单数',
  36. ifnull(sum(case when t2.order_status != 80 then 1 else 0 end) ,'-') as '有效订单数',
  37. ifnull(round(sum(case when t2.order_status != 80 then 1 else 0 end) / count(t2.oid), 4),'-') as '有效率',
  38. ifnull(sum(case when t2.send_status = 1 then 1 else 0 end),'-') as '发货数',
  39. ifnull(round(sum(case when t2.send_status = 1 and t2.order_status != 80 then 1 else 0 end) / sum(case
  40. when
  41. t2.order_status != 80
  42. then 1
  43. else 0 end),
  44. 4),'-') as '发货率',
  45. ifnull(sum(case when t2.send_status = 1 and t2.order_status != 80 then 1 else 0 end),'-') as '有效发货数',
  46. ifnull(sum(case when t2.send_status = 0 and t2.order_status != 80 then 1 else 0 end),'-') as '未发货数',
  47. ifnull(round(sum(case when t2.send_status = 1 and t2.order_status != 80 then 1 else 0 end) / sum(case
  48. when send_status = 1
  49. then 1
  50. else 0 end),
  51. 4) ,'-') as '有效发货率',
  52. ifnull(sum(case when t2.order_status = 80 then 1 else 0 end),'-') as '失效订单数',
  53. ifnull(sum(case when t2.order_status != 80 then t2.pay_amount/100 else 0 end) ,'-') as 'GMV',
  54. ifnull(sum(case
  55. when t2.order_status != 80 then t2.regimental_promotion_amount/100
  56. else 0 end),'-') as '预估服务费',
  57. ifnull(sum(case when t2.order_status != 80 then t2.total_regimental_settle_amount/100 else 0 end),'-') as
  58. '结算服务费'
  59. from {database}.kuaishou_item_bind_user t1
  60. left join {database}.kuaishou_supply_chain t2
  61. on t1.item_id = t2.item_id
  62. left join (select id,reg_name from {database}.kuaishou_supply_chain_cookie union all
  63. select id,reg_name from miaogousi.kuaishou_supply_chain_cookie union all
  64. select id,reg_name from yufu.kuaishou_supply_chain_cookie union all
  65. select id,reg_name from rocket.kuaishou_supply_chain_cookie union all
  66. select id,reg_name from zhishui.kuaishou_supply_chain_cookie union all
  67. select id,reg_name from yuxiu.kuaishou_supply_chain_cookie
  68. group by id, reg_name)t5
  69. on t2.regimental_id=t5.id
  70. where 1 = 1
  71. and t2.stat_date >= DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%%Y%%m01')
  72. and t2.stat_date <= DATE_FORMAT(LAST_DAY(NOW() - INTERVAL 1 MONTH), '%%Y%%m%%d')
  73. group by t1.user_id) t
  74. left join (select item_create_id, count(1) as 'sendSampleCount'
  75. from {database}.kuaishou_item_collect_samples
  76. 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')
  77. 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')
  78. group by item_create_id) t1 on t.userId = t1.item_create_id
  79. where 1 = 1
  80. order by t.订单数 desc
  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. AttractInvestmentEmail().handler(key, value)