python怎么生成每日报表数据并邮件发送

#coding:utf-8 from __future__ import division import pymssql,sys,datetime,xlwt import smtplib from email.mime.text import MIMEText from email.mime.multipart import MIMEMultipart from email.header import Header reload(sys) sys.setdefaultencoding("utf-8") class MSSQL: def __init__(self,host,user,pwd,db): self.host = host self.user = user self.pwd = pwd self.db = db def __GetConnect(self): if not self.db: raise(NameError,"") self.conn = pymssql.connect(host=self.host,user=self.user,password=self.pwd,database=self.db,charset="utf8") cur = self.conn.cursor() if not cur: raise(NameError,"") else: return cur def ExecQuery(self,sql): cur = self.__GetConnect() cur.execute(sql) resList = cur.fetchall() # self.conn.close() return resList def ExecNonQuery(self,sql): cur = self.__GetConnect() cur.execute(sql) self.conn.commit() self.conn.close() def write_data_to_excel(self,name,sql): # 将sql作为参数传递调用get_data并将结果赋值给result,(result为一个嵌套元组) result = self.ExecQuery(sql) # 实例化一个Workbook()对象(即excel文件) wbk = xlwt.Workbook() # 新建一个名为Sheet1的excel sheet。此处的cell_overwrite_ok =True是为了能对同一个单元格重复操作。 sheet = wbk.add_sheet('Sheet1',cell_overwrite_ok=True) # 获取当前日期，得到一个datetime对象如：(2016, 8, 9, 23, 12, 23, 424000) today = datetime.date.today() yesterday = today - datetime.timedelta(days=1) # 将获取到的datetime对象仅取日期如：2016-8-9 yesterdaytime = yesterday.strftime("%Y-%m-%d") # 遍历result中的没个元素。 for i in xrange(len(result)): #对result的每个子元素作遍历， for j in xrange(len(result[i])): #将每一行的每个元素按行号i,列号j,写入到excel中。 sheet.write(i,j,result[i][j]) # 以传递的name+当前日期作为excel名称保存。 filename = name+str(yesterdaytime)+'.xls' wbk.save(filename) return filename ms = MSSQL(host="122.229.*.*",user="root",pwd="root",db="test") today = datetime.date.today() yesterday = today - datetime.timedelta(days=1) yesterdayStart = yesterday.strftime("%Y-%m-%d") + ' 00:00:00' yesterdayEnd = yesterday.strftime("%Y-%m-%d") + ' 23:59:59' print yesterdayStart preCheckCountSuccesSql = "select count(1) FROM tb_crmorders WHERE type =1 and result = 'true' and addtime BETWEEN '" + yesterdayStart +"' and '"+yesterdayEnd + "'"; preCheckUseridSuccesSql = "select count(DISTINCT userid) FROM tb_crmorders WHERE type =1 and result = 'true' and addtime BETWEEN '" + yesterdayStart +"' and '"+yesterdayEnd + "'"; preCheckCountErrorSql = "select count(1) FROM tb_crmorders WHERE type =1 and result = 'false' and addtime BETWEEN '" + yesterdayStart +"' and '"+yesterdayEnd + "'"; preCheckUseridErrorSql = "select count(DISTINCT userid) FROM tb_crmorders WHERE type =1 and result = 'false' and addtime BETWEEN '" + yesterdayStart +"' and '"+yesterdayEnd + "'"; orderSucessCountSql = "select count(1) FROM tb_crmorders WHERE type =2 and action =1 and result = 'true' and notifyresult = 0 and addtime BETWEEN '" + yesterdayStart +"' and '"+yesterdayEnd + "'"; orderErrorCountSql = "select count(1) FROM tb_crmorders WHERE type =2 and action =1 and result = 'true' and notifyresult IS NOT NULL and notifyresult != 0 and addtime BETWEEN '" + yesterdayStart +"' and '"+yesterdayEnd + "'"; unsubscribeSucessCountSql = "select count(1) FROM tb_crmorders WHERE type =2 and action =2 and result = 'true' and notifyresult = 0 and addtime BETWEEN '" + yesterdayStart +"' and '"+yesterdayEnd + "'"; unsubscribeErrorCountSql = "select count(1) FROM tb_crmorders WHERE type =2 and action =2 and result = 'true' and notifyresult IS NOT NULL and notifyresult != 0 and addtime BETWEEN '" + yesterdayStart +"' and '"+yesterdayEnd + "'"; orderKadanSql = "select count(1) FROM tb_crmorders WHERE type =2 and action =1 and result = 'true' and notifyresult IS NULL and addtime BETWEEN '" + yesterdayStart +"' and '"+yesterdayEnd + "'"; unsubscribeKadanSql = "select count(1) FROM tb_crmorders WHERE type =2 and action =2 and result = 'true' and notifyresult IS NULL and addtime BETWEEN '" + yesterdayStart +"' and '"+yesterdayEnd + "'"; preCherkKeyList =['CRM预校验成功单子数量：','CRM预校验成功账号数量：','CRM预校验失败单子数量：','CRM预校验失败账号数量：','订购的订单数成功:','订购的订单数失败:','订购卡单数:','退订的订单数成功:','退订的订单数失败:','退订卡单数:'] preCherkL = {'CRM预校验成功单子数量：' :preCheckCountSuccesSql ,'CRM预校验成功账号数量：' :preCheckUseridSuccesSql ,'CRM预校验失败单子数量：' :preCheckCountErrorSql ,'CRM预校验失败账号数量：' :preCheckUseridErrorSql} preCherkL['订购的订单数成功:'] = orderSucessCountSql preCherkL['订购的订单数失败:'] = orderErrorCountSql preCherkL['订购卡单数:'] = orderKadanSql preCherkL['退订的订单数成功:'] = unsubscribeSucessCountSql preCherkL['退订的订单数失败:'] = unsubscribeErrorCountSql preCherkL['退订卡单数:'] = unsubscribeKadanSql mailMessageText ='' for key in preCherkKeyList: reslist = ms.ExecQuery(preCherkL[key]) for i in reslist: for n in i: mailMessageText = mailMessageText + key + bytes(n) + '\n' crmOrderHandleTimeSql = "select addtime , notifytime FROM tb_crmorders WHERE type =2 and action =1 and result = 'true' and notifyresult =0 and addtime BETWEEN '" + yesterdayStart +"' and '"+yesterdayEnd + "'"; crmunsubscribeHandleTimeSql = "select addtime , notifytime FROM tb_crmorders WHERE type =2 and action =2 and result = 'true' and notifyresult =0 and addtime BETWEEN '" + yesterdayStart +"' and '"+yesterdayEnd + "'"; crmOrderHandle = ms.ExecQuery(crmOrderHandleTimeSql) orderCount = len(crmOrderHandle) if orderCount != 0: totleTime = 0 for temp in crmOrderHandle: addtime = temp[0] notifytime = temp[1] # adddate = datetime.datetime.strptime(addtime,"%Y-%m-%d %H:%M:%S") # notifydate =datetime.datetime.strptime(notifytime, "%Y-%m-%d %H:%M:%S") chazhi = (notifytime - addtime).seconds / 60 totleTime = float(totleTime) + float(chazhi) mailMessageText = mailMessageText + '订购平均处理时长:' + bytes(float(totleTime)/orderCount) + '分' + '\n' crmunsubscribeHandle = ms.ExecQuery(crmunsubscribeHandleTimeSql) subscribeCount = len(crmunsubscribeHandle) if subscribeCount != 0: subscribetotleTime = 0 for temp in crmunsubscribeHandle: addtime = temp[0] notifytime = temp[1] # adddate = datetime.datetime.strptime(addtime, "%Y-%m-%d %H:%M:%S") # notifydate = datetime.datetime.strptime(notifytime, "%Y-%m-%d %H:%M:%S") chazhi = (notifytime - addtime).seconds / 60 subscribetotleTime = float(subscribetotleTime) + float(chazhi) mailMessageText = mailMessageText + '退订平均处理时长:' + bytes(float(subscribetotleTime)/subscribeCount) + '分' + '\n' mailMessageText = mailMessageText + '附件为：预校验失败订单，订购/退订失败订单，卡单订单' + '\n' print mailMessageText #生成excel文件 preCheckErrorname = 'preCheckError' preCerroeFile = ms.write_data_to_excel(preCheckErrorname, "select ordercode,userid,productid,action,msg FROM tb_crmorders WHERE type =1 and result = 'false' and addtime BETWEEN '" + yesterdayStart +"' and '"+yesterdayEnd + "'") orderErrorname = 'orderFalse' ordererroeFile = ms.write_data_to_excel(orderErrorname, "select ordercode,userid,productid,action,sg,notifyresult,notifymsg FROM tb_crmorders WHERE type =2 and result = 'true' and notifyresult IS NOT NULL and notifyresult != 0 and addtime BETWEEN '" + yesterdayStart +"' and '"+yesterdayEnd + "'") kadanname = 'noSynchMsg' kadanFile = ms.write_data_to_excel(kadanname, "select ordercode,userid,productid,sg,action FROM tb_crmorders WHERE type =2 and result = 'true' and notifyresult IS NULL and addtime BETWEEN '" + yesterdayStart +"' and '"+yesterdayEnd + "'") # 第三方 SMTP 服务 mail_host="###@163.com" #设置服务器 mail_user=##" #用户名 mail_pass="##" #口令 sender = '###@163.com' receivers = ['##@qq.com'] # 接收邮件，可设置为你的QQ邮箱或者其他邮箱 #创建一个带附件的实例 message = MIMEMultipart() message['From'] = Header("测试", 'utf-8') message['To'] = Header(" , ".join(receivers), 'utf-8') subject = 'CRM订单日数据' + yesterday.strftime('%Y-%m-%d') message['Subject'] = Header(subject, 'utf-8') #邮件正文内容 message.attach(MIMEText(mailMessageText, 'plain', 'utf-8')) #设置邮件名片（html格式） # html = file('qianming.html').read().decode("utf-8") # message.attach(MIMEText(html, 'html', 'utf-8')) # 构造附件1，传送当前目录下的preCerroeFile 文件 att1 = MIMEText(open(preCerroeFile, 'rb').read(), 'base64', 'utf-8') att1["Content-Type"] = 'application/octet-stream' # 这里的filename可以任意写，写什么名字，邮件中显示什么名字 att1["Content-Disposition"] = 'attachment; filename=' + preCerroeFile message.attach(att1) att2 = MIMEText(open(ordererroeFile, 'rb').read(), 'base64', 'utf-8') att2["Content-Type"] = 'application/octet-stream' att2["Content-Disposition"] = 'attachment; filename='+ordererroeFile message.attach(att2) att3 = MIMEText(open(kadanFile, 'rb').read(), 'base64', 'utf-8') att3["Content-Type"] = 'application/octet-stream' att3["Content-Disposition"] = 'attachment; filename='+kadanFile message.attach(att3) try: smtpObj = smtplib.SMTP() smtpObj.connect(mail_host, 25) # 25 为 SMTP 端口号 smtpObj.login(mail_user,mail_pass) smtpObj.sendmail(sender, receivers, message.as_string()) print "邮件发送成功" except smtplib.SMTPException,e: print "Error: 无法发送邮件" + repr(e)

相关阅读