#coding:utf-8

'''

Created on 2019年2月18日

@author: Administrator

'''

import ConfigParser

import os

import smtplib

from email.mime.text import MIMEText

from email.mime.multipart import MIMEMultipart

from email.header import Header

import MySQLdb

import csv

import codecs

import datetime

import sys

reload(sys)

sys.setdefaultencoding('utf8')

datenow = datetime.datetime.now().strftime("%Y%m%d")

def read_mysql_to_csv(srcDbConn, fileName):

    with codecs.open(filename=fileName, mode='w', encoding='GB2312') as f: #encoding='GB2312' 保证写入csv的中文不是乱码

        write = csv.writer(f, dialect='excel')

        srcCursor = srcDbConn.cursor()

        sql = "SELECT gscode,ymd,gpcode,f1,f2,f3 FROM quant_stk_calc_d_wxcpnew WHERE gscode = 'wxcpnew' AND ymd = {0} and (f1 > 0 or f2 > 0)".format(datenow)

        count = srcCursor.execute(sql)

        print count

        data = srcCursor.fetchall()

        gscode = '指标名称'

        ymd = '信号日期'

        gpcode = '股票代码'

        B = 'B点'

        S = 'S点'

        BStar = 'B点星级'

        title = (gscode, ymd, gpcode, B, S, BStar)

        write.writerow(title)

        for row in data:

            print row

            write.writerow(row)          

def send_mail(fileName):

    #读取配置文件

    config = ConfigParser.SafeConfigParser()

    batRunningPath = os.path.dirname(os.sys.executable)

    batPath = os.path.dirname(os.sys.path[0])

    if os.path.exists(batPath + "\SendEmai\config.ini"): #配置文件config.ini的路径

        config.read(batPath + "\SendEmai\config.ini")

    else:

        config.read(batRunningPath + "\config.ini")

    mail_host    = config.get("email", "mail_host")

    receveAddr   = config.get("email", "mail_to_list")

    ccAddr       = config.get("email", "mail_cc_list")

    mail_to_list = receveAddr.split(",") #收件人

    mail_cc_list = ccAddr.split(",")     #抄送

    mail_user    = config.get("email", "mail_user") #发件人

    mail_pass    = config.get("email", "mail_pass") #发件人密码

    mail_sub = "附件测试"  #邮件标题

    mail_content = ""    #正文内容

    #创建一个带附件的实例

    msg = MIMEMultipart()

    #构造附件1，传送当前目录下 test.txt 文件

    att1 = MIMEText(open(fileName, 'rb').read(), 'base64', 'utf-8')

    att1["Content-Type"] = 'application/octet-stream'

    att1["Content-Disposition"] = 'attachment; filename="{0}"'.format(fileName)

    msg.attach(att1)

    #邮件正文内容

    msg.attach(MIMEText(mail_content,'plain', 'utf-8'))

    msg['Subject'] = Header(mail_sub, 'utf-8')

    msg['From'] = mail_user

    msg['To'] = ",".join(mail_to_list)

    msg['Cc'] = ",".join(mail_cc_list)

    receive = mail_to_list

    receive.extend(mail_cc_list)

    try:

        server = smtplib.SMTP_SSL(mail_host)

        server.login(mail_user,mail_pass)

        server.sendmail(mail_user,receive,msg.as_string())

        server.close()

    except Exception, e:

        print str(e)

if __name__ == '__main__':

    charset = "utf8"

    #源数据库

    #quant 的数据库

    srcHost_Quant = "172.16.8.110"

    srcUse_Quant = "root"

    srcPasswd_Quant = "123456"

    srcDb_Quant = "quant_test"

    srcDbConn_Quant = MySQLdb.connect(srcHost_Quant, srcUse_Quant, srcPasswd_Quant, srcDb_Quant, charset=charset)

    fileName = "WXCP_BS_{0}.csv".format(datenow)

    read_mysql_to_csv(srcDbConn_Quant, fileName)

    send_mail(fileName)