# encoding=utf-8
import pymysql
import openpyxl
import datetime
import  pandas  as pd
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.header import Header
import smtplib
import  pandas  as pd
#从数据库中读取数据
conn =pymysql.connect(host='106.75.62.200',port=3306,user='UserSelect',passwd='1q2w3e4r5t',db='bingofzd',charset="utf8")
sql1='\
SELECT c.serverid, b.channelname, a.roleid ,a.rolename ,a.registertime ,COUNT(c.roleid)  as paytimes ,SUM(c.money)  as pay ,a.userid \
          ,a.device ,a.registerip,MAX(paytime) as lastpaytime \
  FROM  createrole as a ,  recharge as c,allchannel as b \
   WHERE  a.roleid=c.roleid  AND a.serverid=c.serverid   AND  a.gamechannel=b.channelid  \
     AND  c.paytime  >="2018-09-14 00:00:00" \
   AND  a.registertime >= "2018-09-14 00:00:00" \
   AND  a.registertime <now() \
   AND a.roleid NOT in (SELECT roleid FROM  createrole_optimize)  \
GROUP BY   a.gamechannel, a.roleid ,a.rolename,a.registertime \
HAVING  SUM(c.money)>500  \
ORDER BY   SUM(c.money) DESC   \
'
sql2='\
SELECT a.roleid,MAX(b.logintime) as lasttime \
FROM recharge  as a ,rolelogin as b  \
WHERE a.paytime> "2018-09-14 00:00:00" \
AND b.logintime> "2018-09-14 00:00:00" \
AND a.roleid=b.roleid   \
GROUP BY   a.roleid  \
'
df1=pd.read_sql(sql1,conn)
df2=pd.read_sql(sql2,conn)
#print(df1.head(10))
#print(df2.head(10))
dfu=pd.merge(df1,df2,on="roleid",how="left")
print(dfu.head(10))

#生成excel文件
def get_excel(df,file):
    #新建一个工作薄对象
    new = openpyxl.Workbook()
    sheet = new.active
    sheet.title = '数据展示'  #数据标题
    field=list(df);data=df.values #获取列表和数据
    print(field)
    #将字段名称循环写入excel第一行，因为字段格式列表里包含列表，每个列表的第一元素才是字段名称
    for col in range(len(field)):
        #row代表行数，column代表列数，value代表单元格输入的值，行数和列数都是从1开始，这点于python不同要注意
        _ = sheet.cell(row=1, column=col+1, value=u'%s' % field[col])
     #将数据循环写入excel的每个单元格中
    for row in range(len(data)):
        for col in range(len(field)):
            #因为第一行写了字段名称，所以要从第二行开始写入
            _ = sheet.cell(row=row+2, column=col + 1, value=u'%s' % data[row][col])
            #将生成的excel保存，这步是必不可少的
    newworkbook = new.save(file)#返回生成的excel
    return newworkbook

get_excel(dfu,r'C:\Users\mei-huang\Desktop\明日决胜玩家付费数据(500以上).xlsx')  #保存文件到指定位置

#获取昨天日期
def getYesterday():
    # 获取昨天日期的字符串格式的函数
    #获取今天的日期
    today = datetime.date.today()
    #获取一天的日期格式数据
    oneday = datetime.timedelta(days=1)
    #昨天等于今天减去一天
    yesterday = today - oneday
    #获取昨天日期的格式化字符串
    yesterdaystr = yesterday.strftime('%Y-%m-%d')
    #返回昨天的字符串
    return yesterdaystr

#生成邮件的函数
def create_email(email_from, email_to, email_Subject, email_text, annex_path, annex_name):
    #生成一个空的带附件的邮件实例
    message = MIMEMultipart()
    #将正文以text的形式插入邮件中
    message.attach(MIMEText(email_text, 'plain', 'utf-8'))
    #生成发件人名称（这个跟发送的邮件没有关系）
    message['From'] = Header(email_from, 'utf-8')
    #生成收件人名称（这个跟接收的邮件也没有关系）
    message['To'] = Header(email_to, 'utf-8')
    #生成邮件主题
    message['Subject'] = Header(email_Subject, 'utf-8')
    #读取附件的内容
    att1 = MIMEText(open(annex_path, 'rb').read(), 'base64', 'utf-8')
    att1["Content-Type"] = 'application/octet-stream'
    #生成附件的名称
    att1["Content-Disposition"] = 'attachment; filename=' + annex_name
    #将附件内容插入邮件中
    message.attach(att1)
    #返回邮件
    return message
# 输入发件人昵称、收件人昵称、主题，正文，附件地址,附件名称生成一封邮件
z=create_email("szl", "黑川", "明日决胜付费玩家数据","来自小姐姐的礼物", r'C:\Users\mei-huang\Desktop\明日决胜玩家付费数据(500以上).xlsx', "zhang.xlsx")
print(type(z))
#定义发送邮件的函数
def send_email(sender, password, receiver, msg):
    # 一个输入邮箱、密码、收件人、邮件内容发送邮件的函数
    try:
        #找到你的发送邮箱的服务器地址，已加密的形式发送
        server = smtplib.SMTP_SSL("smtp.exmail.qq.com", 465)  # 发件人邮箱中的SMTP服务器
        print(1)
        server.ehlo()
        #登录你的账号
        print(2)
        server.login(sender, password)  # 括号中对应的是发件人邮箱账号、邮箱密码
        #发送邮件
        server.sendmail(sender, receiver, msg.as_string())  # 括号中对应的是发件人邮箱账号、收件人邮箱账号（是一个列表）、邮件内容
        print("邮件发送成功")
        server.quit()  # 关闭连接
    except Exception:
        #print(traceback.print_exc())
        print("邮件发送失败")


send_email("zhilong.shi@yingxiong.com", "147258369stST",["zhilong.shi@yingxiong.com","512415688@qq.com"],z)