#!/usr/bin/env python
#coding=utf8
import os
import xlwt
import MySQLdb
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.image import MIMEImage
from datetime import datetime, timedelta
yesterday = (datetime.now()).strftime('%Y-%m-%d')
sql1 = '''SELECT lp.`dueDate` ,l.`planned` ,l.`rincipal` ,l.`rmInterest` ,l.`mount` ,l.`ncipal` ,l.`terest` , o.`Name` ,o.`MPN` ,lp.`loanId`  from `ayback` as l  INNER JOIN `iev` as o on l.`tid` =o.`Id` AND l.`Type` +1 = o.`Type` AND l.`id` =o.`Id` INNER JOIN `phase` AS lp ON lp.`loa` =o.`loa` AND lp.`mber` =l.`term`where lp.`ate` LIKE  CONCAT (LEFT (NOW(),10), '%');'''
sql_1 = sql1.replace('test', yesterday)
excevey=xlwt.Workbook(encoding = 'utf-8')
excel_date_fmt = 'YY/M/D h:mm:mm'
style = xlwt.XFStyle()
style.num_format_str = excel_date_fmt
font = xlwt.Font()
font.name = 'SimSun'
style.font = font
#sql1zhixing
sheet=excevey.add_sheet('数据表-%s' % yesterday)
sheet.write(0,0,'dte')
sheet.write(0,1,'rmAmount')
sheet.write(0,2,'rincipal')
sheet.write(0,3,'nterest')
sheet.write(0,4,'ount')
sheet.write(0,5,'incipal')
sheet.write(0,6,'nterest')
sheet.write(0,7,'ame')
sheet.write(0,8,'buye')
sheet.write(0,9,'loa')
row=1
conn = MySQLdb.connect(host = "mysql.rds.aliyuncs.com", user = "only", passwd = "#Ii_AG", db = "weiyin",charset='utf8')
cursor = conn.cursor()
cursor.execute(sql_1)
for a01, a02, a03, a04, a05, a06, a07, a08, a09, a10 in cursor.fetchall():
    sheet.write(row,0,a01)
    sheet.write(row,1,a02)
    sheet.write(row,2,a03)
    sheet.write(row,3,a04)
    sheet.write(row,4,a05)
    sheet.write(row,5,a06)
    sheet.write(row,6,a07)
    sheet.write(row,7,a08)
    sheet.write(row,8,a09)
    sheet.write(row,9,a10)
    row+=1
cursor.close()
#daochubaobiao
excevey.save('数据表-%s.xls' % yesterday)
cursor.close()
conn.close()
####SEND THE MAIL
sender = 'jun@163.com'
receiver = 'liu@ank.com'
smtpserver = 'c2.icoremail.net'
username = 'xuexiaojun@zhsoftbank.com'
password = '1234'
smtp = smtplib.SMTP()

def send_email(msg,file_name):
    msgRoot = MIMEMultipart('related')
    msgRoot['Subject'] = file_name
    msgRoot['From'] = 'jun@163.com'
    msgText = MIMEText('%s'% msg,'html','utf-8')
    msgRoot.attach(msgText)
    att = MIMEText(open('%s'% file_name, 'rb').read(), 'base64', 'utf-8')
    att["Content-Type"] = 'application/octet-stream'
    att["Content-Disposition"] = 'attachment; filename="%s"' % file_name
    msgRoot.attach(att)
    while 1:
        try:
            smtp.sendmail(sender, receiver, msgRoot.as_string())
            break
        except:
            try:
                smtp=smtplib.SMTP_SSL(smtpserver, 465)
                #smtp.connect(smtpserver)
                smtp.login(username, password)
            except:
                print "failed to login to smtp server"

if __name__ == "__main__":
    MSG="<html><h2>运营数据:&nbsp;</h2><p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;附件内容为%s日报表,系统每日生成,如有疑问,请联系技术部门.&nbsp;</p></html>" % yesterday
    FILE= '运营数据表-%s.xls' % yesterday
    send_email(MSG,FILE)
    os.system("rm -f /root/data/*.xls")
    os.system("rm -f /root/*.xls")
