# -*- coding: utf-8 -*- # Author: dukun import pymysql #Python3的mysql模块，Python2 是mysqldb import os import datetime #定时发送，以及日期 import shutil #文件操作 import smtplib #邮件模块 from email.mime.text import MIMEText from email.mime.multipart import MIMEMultipart from email.header import Header import time import xlwt #连接数据库 def conMysql(mysql_host,mysql_user,mysql_password,mysql_db): db = pymysql.connect(host=mysql_host,user=mysql_user, password=mysql_password,db=mysql_db,charset = 'utf8') print("数据库连接成功") return db #获取数据 def getPassData(sql,db): #db = conMysql(mysql_host,mysql_user,mysql_password,mysql_db) cursor = db.cursor() try: # 执行SQL语句 cursor.execute(sql) # 获取所有记录列表 results = cursor.fetchall() #print(results) cursor.scroll(0,mode='absolute') #cursor.description获取表格的字段信息 fields=cursor.description return results,fields except: print ("Error: unable to fetch data") return 0 cursor.scroll(0,mode='absolute') #cursor.description获取表格的字段信息 fields=cursor.description #print(fields) cursor.close() db.close() #入表格 def writeToExcel(fLocate,file_sheet,style1,style2,style3): results,fields = getPassData(sql,db)#通道数据 cus_results,cus_fields = getPassData(cus_sql,db) #客户数据 ord_results,ord_fields = getPassData(ord_sql,db) #订单时间统计 mob_results,mob_fields = getPassData(mob_sql,db) #满帆移动次数统计 uni_results,uni_fields = getPassData(uni_sql,db) #满帆联通次数统计 tel_results,tel_fields = getPassData(tel_sql,db) #满帆电信次数统计 ys_results,ys_fields = getPassData(ys_sql,db) #亚杉移动次数统计 zr_results,zr_fields = getPassData(zr_sql,db) #兆荣移动WT次数统计 zr_uni_results,zr_uni_fields = getPassData(zr_uni_sql,db) #兆荣联通WT次数统计 zr_tel_results,zr_tel_fields = getPassData(zr_tel_sql,db) #兆荣电信WT次数统计 if(results != 0): if os.path.exists(fLocate): os.remove(fLocate) # 如果文件存在，则删除 #将查询结果写入到excel workbook = xlwt.Workbook(encoding = 'utf-8') #通道数据表 sheet = workbook.add_sheet(file_sheet,cell_overwrite_ok=True) #通道成功次数表 sheet2 = workbook.add_sheet("通道成功次数统计",cell_overwrite_ok=True) #sheet1 列宽 for i in range(0,100): if i == 1: sheet.col(i).width = 256 * 25 sheet2.col(i).width = 256 * 25 else: sheet.col(i).width = style1.width #sheet2 列宽 for i in range(0,100): sheet2.col(i).width = 256 * 15 #sheet.col(2).width = 256 * 100 #将通道数据表头写入excel sheet.write(0,0,"序号",style1) sheet.write(0,1,"通道",style1) sheet.write(0,2,"面额",style1) sheet.write(0,3,"订单数",style1) sheet.write(0,4,"金额百分比",style1) #订单时间统计表头 sheet2.write_merge(0, 0, 0, 6, 'First Merge',style1) sheet2.write(0,0,"订单时间百分比",style1) sheet2.write(1,0,"小于10分钟",style1) sheet2.write(1,1,"10-20分钟",style1) sheet2.write(1,2,"20-30分钟",style1) sheet2.write(1,3,"30-40分钟",style1) sheet2.write(1,4,"40-50分钟",style1) sheet2.write(1,5,"50-60分钟",style1) sheet2.write(1,6,"大于60分钟",style1) #通道成功次数统计 sheet2.write_merge(5, 5, 0, 10, 'Second Merge',style1) sheet2.write(5,0,"通道成功次数统计",style1) sheet2.write(6,0,"通道",style1) sheet2.write(6,1,"1-5次",style1) sheet2.write(6,2,"6-10次",style1) sheet2.write(6,3,"11-15次",style1) sheet2.write(6,4,"16-20次",style1) sheet2.write(6,5,"21-30次",style1) sheet2.write(6,6,"31-40次",style1) sheet2.write(6,7,"41-50次",style1) sheet2.write(6,8,"51-60次",style1) sheet2.write(6,9,"大于60次",style1) sheet2.write(6,10,"总订单数",style1) sheet2.write(7,0,"满帆移动",style1) sheet2.write(8,0,"满帆联通",style1) sheet2.write(9,0,"满帆电信",style1) sheet2.write(10,0,"亚杉移动",style1) sheet2.write(11,0,"兆蓉移动WT",style1) sheet2.write(12,0,"兆蓉联通WT",style1) sheet2.write(13,0,"兆蓉电信WT",style1) sheet2.write(14,0,"总计",style1) sheet2.write(15,0,"百分比",style1) #订单时间统计写入excle ord_col = len(ord_fields) for j in range(ord_col): sheet2.write(2,j,ord_results[0][j],style1) #通道成功次数统计写入excle mob_col = len(mob_fields) for j in range(1,mob_col+1): sheet2.write(7,j,mob_results[0][j-1],style1) sheet2.write(8,j,uni_results[0][j-1],style1) sheet2.write(9,j,tel_results[0][j-1],style1) sheet2.write(10,j,ys_results[0][j-1],style1) sheet2.write(11,j,zr_results[0][j-1],style1) sheet2.write(12,j,zr_uni_results[0][j-1],style1) sheet2.write(13,j,zr_tel_results[0][j-1],style1) c_row = 14 #通道成功次数总计栏 sheet2.write(c_row,1,xlwt.Formula("SUM($B$8:$B${})".format(c_row)),style1) sheet2.write(c_row,2,xlwt.Formula("SUM($C$8:$C${})".format(c_row)),style1) sheet2.write(c_row,3,xlwt.Formula("SUM($D$8:$D${})".format(c_row)),style1) sheet2.write(c_row,4,xlwt.Formula("SUM($E$8:$E${})".format(c_row)),style1) sheet2.write(c_row,5,xlwt.Formula("SUM($F$8:$F${})".format(c_row)),style1) sheet2.write(c_row,6,xlwt.Formula("SUM($G$8:$G${})".format(c_row)),style1) sheet2.write(c_row,7,xlwt.Formula("SUM($H$8:$H${})".format(c_row)),style1) sheet2.write(c_row,8,xlwt.Formula("SUM($I$8:$I${})".format(c_row)),style1) sheet2.write(c_row,9,xlwt.Formula("SUM($J$8:$J${})".format(c_row)),style1) sheet2.write(c_row,10,xlwt.Formula("SUM($K$8:$K${})".format(c_row)),style1) #通道成功次数百分比 sheet2.write(c_row+1,1,xlwt.Formula("$B${}/$K${}".format(c_row+1,c_row+1)),style2) sheet2.write(c_row+1,2,xlwt.Formula("$C${}/$K${}".format(c_row+1,c_row+1)),style2) sheet2.write(c_row+1,3,xlwt.Formula("$D${}/$K${}".format(c_row+1,c_row+1)),style2) sheet2.write(c_row+1,4,xlwt.Formula("$E${}/$K${}".format(c_row+1,c_row+1)),style2) sheet2.write(c_row+1,5,xlwt.Formula("$F${}/$K${}".format(c_row+1,c_row+1)),style2) sheet2.write(c_row+1,6,xlwt.Formula("$G${}/$K${}".format(c_row+1,c_row+1)),style2) sheet2.write(c_row+1,7,xlwt.Formula("$H${}/$K${}".format(c_row+1,c_row+1)),style2) sheet2.write(c_row+1,8,xlwt.Formula("$I${}/$K${}".format(c_row+1,c_row+1)),style2) sheet2.write(c_row+1,9,xlwt.Formula("$J${}/$K${}".format(c_row+1,c_row+1)),style2) #通道数据写入excle num = 1 row = len(results) col = len(fields) for i in range(1,row+1): for j in range(col): if j == 0: sheet.write(i,j,num,style1) num += 1 else: sheet.write(i,j,results[i-1][j],style1) #客户数据表头 sheet.write(row+4,0,"序号",style1) sheet.write(row+4,1,"客户",style1) sheet.write(row+4,2,"面额",style1) sheet.write(row+4,3,"订单数",style1) sheet.write(row+4,4,"金额百分比",style1) #客户数据 income_money = 0; num = 1 cus_row = len(cus_results) cus_col = len(cus_fields) for i in range(row+5,row+5 + cus_row): for j in range(cus_col): if cus_results[i-row-5][0] >= 57: #cus_num += 1 #print('cus_num:{}'.format(cus_num)) continue else: if j == 0: sheet.write(i,j,num,style1) num += 1 else: sheet.write(i,j,cus_results[i-row-5][j],style1) #总收入 for i in range(1,cus_row): if cus_results[i-1][0] >= 57: continue else: if ('身边科技' in cus_results[i-1][1]): income_money = income_money + 0.978 * cus_results[i-1][2] #print('身边科技：%d'%income_money) elif ('十荟' in cus_results[i-1][1]): income_money = income_money + 0.985 * cus_results[i-1][2] #print('十荟：%d'%income_money) elif ('脉粒' in cus_results[i-1][1]): income_money = income_money + 0.984 * cus_results[i-1][2] #print('脉粒:%d'%income_money) elif ('小米' in cus_results[i-1][1]): income_money = income_money + 0.985 * cus_results[i-1][2] #print('小米:%d'%income_money) elif ('挖财' in cus_results[i-1][1]): income_money = income_money + 0.985 * cus_results[i-1][2] #print('挖财：%d'%income_money) elif ('H5' in cus_results[i-1][1]): income_money = income_money + 0.989 * cus_results[i-1][2] #print('H5：%d'%income_money) elif ('折800' in cus_results[i-1][1]): income_money = income_money + 0.985 * cus_results[i-1][2] #print('折800:%d'%income_money) #合计栏 for i in range(0,5): #if cus_results[i-row-6][0] >= 57: # cus_num += 1 if i == 0: sheet.write(row+1,i,row+1,style1) sheet.write(row+num+4,i,num,style1) elif i == 1: sheet.write(row+1,i,'合计',style1) sheet.write(row+num+4,i,'合计',style1) elif i == 2: sheet.write(row+1,i,xlwt.Formula("SUM($C$%d:$C$%d)"%(2,row+1)),style1) sheet.write(row+num+4,i,xlwt.Formula("SUM($C$%d:$C$%d)"%(row+6,row+num+4)),style1) elif i == 3: sheet.write(row+1,i,xlwt.Formula("SUM($D$%d:$D$%d)"%(2,row+1)),style1) sheet.write(row+num+4,i,xlwt.Formula("SUM($D$%d:$D$%d)"%(row+6,row+num+4)),style1) else: #sheet.write(row+1,i,' ') #sheet.write(row+1,i,' ') pass #print(row) #print(col) #通道金额百分比 for i in range(1,row+1): j = i + 1 sheet.write(i,col,xlwt.Formula("$C$%d/$C$%d"%(j,row+2)),style2) #客户金额百分比 for i in range(row+5,row+num+4): j = i + 1 sheet.write(i,col,xlwt.Formula("$C$%d/$C$%d"%(j,row+num+5)),style2) sheet.write(0,col+2,"网厅百分比",style2) sheet.write(0,col+3,"补单面额",style1) sheet.write(0,col+4,"均单值[995预估]",style3) sheet.write(0,col+5,"均单面值",style3) supOrder_money = 0 #补单面额 cost_money = 0 #成本 net_money = 0 #网厅金额 sup = 0 for i in range(1,row+1): #for j in rang(col+1): #print(results[i-1][1]) if('补单' in results[i-1][1]): supOrder_money += results[i-1][2] #print(supOrder_money) cost_money += 1.0 * results[i-1][2] elif('人工' in results[i-1][1]): supOrder_money += results[i-1][2] cost_money += results[i-1][2] elif('亚杉' in results[i-1][1]): net_money += results[i-1][2] cost_money = cost_money + 0.979 * results[i-1][2] elif('满帆移动' in results[i-1][1]): net_money += results[i-1][2] cost_money = cost_money + 0.979 * results[i-1][2] #print(results[i-1][2]) #print(cost_money) elif('满帆联通' in results[i-1][1]): net_money += results[i-1][2] cost_money = cost_money + 0.978 * results[i-1][2] #print(cost_money) elif('满帆电信' in results[i-1][1]): net_money += results[i-1][2] cost_money = cost_money + 0.974 * results[i-1][2] #print(cost_money) elif('兆蓉移动WT' == results[i-1][1]): net_money += results[i-1][2] cost_money = cost_money + 0.975 * results[i-1][2] #print(cost_money) elif('兆蓉联通WT' == results[i-1][1]): net_money += results[i-1][2] cost_money = cost_money + 0.972 * results[i-1][2] elif('兆蓉电信WT' == results[i-1][1]): net_money += results[i-1][2] cost_money = cost_money + 0.970 * results[i-1][2] elif('兆蓉移动' == results[i-1][1]): #print(results[i-1][2]) cost_money = cost_money + 0.9972 * results[i-1][2] #print(cost_money) elif('兆蓉联通' == results[i-1][1]): cost_money = cost_money + 1.01 * results[i-1][2] #print(cost_money) elif('兆蓉电信' == results[i-1][1]): cost_money = cost_money + 1.0 * results[i-1][2] #print(cost_money) #print('=========================') sheet.write(1,col+2,xlwt.Formula("%d / SUM($C$%d:$C$%d) "%(net_money,2,row+1)),style2) sheet.write(1,col+3,supOrder_money,style1) sheet.write(1,col+4,xlwt.Formula("0.995 * SUM($C$%d:$C$%d)/SUM($D$%d:$D$%d) "%(2,row+1,2,row+1)),style3) sheet.write(1,col+5,xlwt.Formula("SUM($C$%d:$C$%d)/SUM($D$%d:$D$%d) "%(2,row+1,2,row+1)),style3) sheet.write(5,col+2,'总成本') sheet.write(6,col+2,'总收入') sheet.write(7,col+2,'总利润') sheet.write(8,col+2,'利润率') style4 = xlwt.XFStyle()#设置利润率小数点保留位数 style4.num_format_str='0.000000000' sheet.write(5,col+3,cost_money) sheet.write(6,col+3,income_money) sheet.write(7,col+3,xlwt.Formula("H7 - H6")) sheet.write(8,col+3,xlwt.Formula("H8 / H7 "),style4) workbook.save(fLocate) #设置表格样式 def setStyle(): #初始化样式 style1 = xlwt.XFStyle() style2 = xlwt.XFStyle() style3 = xlwt.XFStyle() font = xlwt.Font() # 为样式创建字体 font.name = '宋体' # 设置字体颜色 #font.colour_index = color # 字体大小 font.height = 20 * 11 #设置边框 color = 1 borders = xlwt.Borders() borders.left = color borders.left = xlwt.Borders.THIN borders.right = color borders.top = color borders.bottom = color #对齐方式 al = xlwt.Alignment() al.horz = 0x02 # 设置水平居中 al.vert = 0x01 # 设置垂直居中 #al.wrap = 1 # 自动换行 #列宽 width = 256 * 18 # 定义格式 style1.borders = borders style1.alignment = al style1.font = font style1.width = width style2.borders = borders style2.alignment = al style2.font = font style2.width = width style2.num_format_str='0%' style3.borders = borders style3.alignment = al style3.font = font style3.width = width style3.num_format_str='0.00' return style1,style2,style3 def sendMail(sender,receiver,mail_user,mail_pass,mail_host,subject,content,fLocate,file_name): try: #邮件头 message = MIMEMultipart() message['From'] = "{}".format(sender)#发送 message['To'] = ",".join(receiver)#收件 message['Subject'] = Header(subject, 'utf-8') message.attach(MIMEText(content, 'plain', 'utf-8'))# 邮件正文 # 构造附件 att1 = MIMEText(open(fLocate,'rb').read(), 'plain', 'utf-8') #att1 = MIMEApplication(open(fLocate)) att1["Content-Type"] = 'application/octet-stream' #att1["Content-Disposition"] = "attachment; filename=" + file_name att1.add_header('Content-Disposition', 'attachment', filename=file_name) message.attach(att1) #执行 smtp = smtplib.SMTP() #smtp.connect(smtpserver) #连接服务器 smtp = smtplib.SMTP_SSL(mail_host, 465) smtp.login(mail_user, mail_pass) #登录 smtp.sendmail(sender, receiver, message.as_string()) #发送 smtp.quit() print("SEND SUCCES") except: print("SEND FALSE") if __name__ == '__main__': #数据库连接信息 mysql_host = '47.95.217.180' mysql_user = 'rtech' mysql_password = 'S5RE4dQ65MphYk7F' mysql_db = 'fmp' #数据库连接 db = conMysql(mysql_host,mysql_user,mysql_password,mysql_db) #获取时间 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' #通道sql语句 sql = """SELECT t1.channel_id, access_channel_info.channel_name,t1.flow_amount_sum,t1.count_num FROM (SELECT channel_id,SUM(flow_amount) flow_amount_sum,count(*) count_num FROM flow_order_info WHERE apply_date BETWEEN ' """ + yesterdayStart + """ ' AND ' """ + yesterdayEnd + """ ' AND status = 6 AND channel_id NOT IN ( 101,102,103,110,111,112 ) GROUP BY channel_id) t1 LEFT JOIN access_channel_info ON t1.channel_id = access_channel_info.channel_seq_id; """ #客户sql cus_sql = """SELECT t1.enterprise_id,customer_name,t1.flow_amount_sum,t1.count_num FROM (SELECT enterprise_id,SUM(flow_amount) flow_amount_sum,count(*) count_num FROM flow_order_info WHERE apply_date BETWEEN ' """ + yesterdayStart + """ ' AND ' """ + yesterdayEnd + """ ' AND status = 6 GROUP BY enterprise_id)t1 LEFT JOIN customer_info ON t1.enterprise_id = customer_id; """ #订单时间比例统计sql ord_sql = """ SELECT CONCAT(CAST(round((A/H)*100,2) AS CHAR),'%') as 'AA[<10min]', CONCAT(CAST(round((B/H)*100,2) AS CHAR),'%') as 'BB[10-20min]', CONCAT(CAST(round((C/H)*100,2) AS CHAR),'%') as 'CC[20-30min]', CONCAT(CAST(round((D/H)*100,2) AS CHAR),'%') as 'DD[30-40min]', CONCAT(CAST(round((E/H)*100,2) AS CHAR),'%') as 'EE[40-50min]', CONCAT(CAST(round((F/H)*100,2) AS CHAR),'%') as 'FF[50-60min]', CONCAT(CAST(round((G/H)*100,2) AS CHAR),'%') as 'GG[>60min]' FROM (SELECT SUM(CASE WHEN diff_time BETWEEN 0 AND 600 THEN 1 ELSE 0 END) AS A, SUM(CASE WHEN diff_time BETWEEN 601 AND 1200 THEN 1 ELSE 0 END) AS B, SUM(CASE WHEN diff_time BETWEEN 1201 AND 1800 THEN 1 ELSE 0 END) AS C, SUM(CASE WHEN diff_time BETWEEN 1801 AND 2400 THEN 1 ELSE 0 END) AS D, SUM(CASE WHEN diff_time BETWEEN 2401 AND 3000 THEN 1 ELSE 0 END) AS E, SUM(CASE WHEN diff_time BETWEEN 3001 AND 3600 THEN 1 ELSE 0 END) AS F, SUM(CASE WHEN diff_time > 3600 THEN 1 ELSE 0 END) AS G, COUNT(*) AS H FROM (SELECT (UNIX_TIMESTAMP(check_time) - UNIX_TIMESTAMP(apply_date)) AS diff_time FROM flow_order_info WHERE apply_date BETWEEN ' """ + yesterdayStart + """ ' AND ' """ + yesterdayEnd + """ ' AND status = 6 AND channel_id NOT IN ( 101,102,103,110,111,112 ) ) AS t1 ) AS t2 """ #满帆移动通道成功次数统计 mob_sql = """SELECT SUM(CASE WHEN cnt BETWEEN 0 AND 5 THEN 1 ELSE 0 END) AS 'A[1-5]', SUM(CASE WHEN cnt BETWEEN 6 AND 10 THEN 1 ELSE 0 END) AS 'A[6-10]', SUM(CASE WHEN cnt BETWEEN 11 AND 15 THEN 1 ELSE 0 END) AS 'A[11-15]', SUM(CASE WHEN cnt BETWEEN 16 AND 20 THEN 1 ELSE 0 END) AS 'B[16-20]', SUM(CASE WHEN cnt BETWEEN 21 AND 30 THEN 1 ELSE 0 END) AS 'C[21-30]', SUM(CASE WHEN cnt BETWEEN 31 AND 40 THEN 1 ELSE 0 END) AS 'D[31-40]', SUM(CASE WHEN cnt BETWEEN 41 AND 50 THEN 1 ELSE 0 END) AS 'E[41-50]', SUM(CASE WHEN cnt BETWEEN 51 AND 60 THEN 1 ELSE 0 END) AS 'F[51-60]', SUM(CASE WHEN cnt > 61 THEN 1 ELSE 0 END) AS 'G[>60]', COUNT(*) AS 'H[总订单数]' FROM ( SELECT t1.order_id, t1.cnt FROM (SELECT order_id, COUNT(*) AS cnt FROM mobile_flow_dispatch_rec WHERE trans_id = 83 AND create_date BETWEEN ' """ + yesterdayStart + """ ' AND ' """ + yesterdayEnd + """ ' GROUP BY order_id ORDER BY cnt ASC) t1 LEFT JOIN flow_order_info ON t1.order_id = flow_order_info.order_id WHERE flow_order_info.channel_id = 83 AND flow_order_info.status = 6 )t2; """ #满帆联通通道成功次数统计 uni_sql = """SELECT SUM(CASE WHEN cnt BETWEEN 0 AND 5 THEN 1 ELSE 0 END) AS 'A[1-5]', SUM(CASE WHEN cnt BETWEEN 6 AND 10 THEN 1 ELSE 0 END) AS 'A[6-10]', SUM(CASE WHEN cnt BETWEEN 11 AND 15 THEN 1 ELSE 0 END) AS 'A[11-15]', SUM(CASE WHEN cnt BETWEEN 16 AND 20 THEN 1 ELSE 0 END) AS 'B[16-20]', SUM(CASE WHEN cnt BETWEEN 21 AND 30 THEN 1 ELSE 0 END) AS 'C[21-30]', SUM(CASE WHEN cnt BETWEEN 31 AND 40 THEN 1 ELSE 0 END) AS 'D[31-40]', SUM(CASE WHEN cnt BETWEEN 41 AND 50 THEN 1 ELSE 0 END) AS 'E[41-50]', SUM(CASE WHEN cnt BETWEEN 51 AND 60 THEN 1 ELSE 0 END) AS 'F[51-60]', SUM(CASE WHEN cnt > 61 THEN 1 ELSE 0 END) AS 'G[>60]', COUNT(*) AS 'H[总订单数]' FROM ( SELECT t1.order_id, t1.cnt FROM (SELECT order_id, COUNT(*) AS cnt FROM mobile_flow_dispatch_rec WHERE trans_id = 84 AND create_date BETWEEN ' """ + yesterdayStart + """ ' AND ' """ + yesterdayEnd + """ ' GROUP BY order_id ORDER BY cnt ASC) t1 LEFT JOIN flow_order_info ON t1.order_id = flow_order_info.order_id WHERE flow_order_info.channel_id = 84 AND flow_order_info.status = 6 )t2; """ #满帆电信通道成功次数统计 tel_sql = """SELECT SUM(CASE WHEN cnt BETWEEN 0 AND 5 THEN 1 ELSE 0 END) AS 'A[1-5]', SUM(CASE WHEN cnt BETWEEN 6 AND 10 THEN 1 ELSE 0 END) AS 'A[6-10]', SUM(CASE WHEN cnt BETWEEN 11 AND 15 THEN 1 ELSE 0 END) AS 'A[11-15]', SUM(CASE WHEN cnt BETWEEN 16 AND 20 THEN 1 ELSE 0 END) AS 'B[16-20]', SUM(CASE WHEN cnt BETWEEN 21 AND 30 THEN 1 ELSE 0 END) AS 'C[21-30]', SUM(CASE WHEN cnt BETWEEN 31 AND 40 THEN 1 ELSE 0 END) AS 'D[31-40]', SUM(CASE WHEN cnt BETWEEN 41 AND 50 THEN 1 ELSE 0 END) AS 'E[41-50]', SUM(CASE WHEN cnt BETWEEN 51 AND 60 THEN 1 ELSE 0 END) AS 'F[51-60]', SUM(CASE WHEN cnt > 61 THEN 1 ELSE 0 END) AS 'G[>60]', COUNT(*) AS 'H[总订单数]' FROM ( SELECT t1.order_id, t1.cnt FROM (SELECT order_id, COUNT(*) AS cnt FROM mobile_flow_dispatch_rec WHERE trans_id = 85 AND create_date BETWEEN ' """ + yesterdayStart + """ ' AND ' """ + yesterdayEnd + """ ' GROUP BY order_id ORDER BY cnt ASC) t1 LEFT JOIN flow_order_info ON t1.order_id = flow_order_info.order_id WHERE flow_order_info.channel_id = 85 AND flow_order_info.status = 6 )t2; """ #亚杉移动通道成功次数统计 ys_sql = """SELECT SUM(CASE WHEN cnt BETWEEN 0 AND 5 THEN 1 ELSE 0 END) AS 'A[1-5]', SUM(CASE WHEN cnt BETWEEN 6 AND 10 THEN 1 ELSE 0 END) AS 'A[6-10]', SUM(CASE WHEN cnt BETWEEN 11 AND 15 THEN 1 ELSE 0 END) AS 'A[11-15]', SUM(CASE WHEN cnt BETWEEN 16 AND 20 THEN 1 ELSE 0 END) AS 'B[16-20]', SUM(CASE WHEN cnt BETWEEN 21 AND 30 THEN 1 ELSE 0 END) AS 'C[21-30]', SUM(CASE WHEN cnt BETWEEN 31 AND 40 THEN 1 ELSE 0 END) AS 'D[31-40]', SUM(CASE WHEN cnt BETWEEN 41 AND 50 THEN 1 ELSE 0 END) AS 'E[41-50]', SUM(CASE WHEN cnt BETWEEN 51 AND 60 THEN 1 ELSE 0 END) AS 'F[51-60]', SUM(CASE WHEN cnt > 61 THEN 1 ELSE 0 END) AS 'G[>60]', COUNT(*) AS 'H[总订单数]' FROM ( SELECT t1.order_id, t1.cnt FROM (SELECT order_id, COUNT(*) AS cnt FROM mobile_flow_dispatch_rec WHERE trans_id = 104 AND create_date BETWEEN ' """ + yesterdayStart + """ ' AND ' """ + yesterdayEnd + """ ' GROUP BY order_id ORDER BY cnt ASC) t1 LEFT JOIN flow_order_info ON t1.order_id = flow_order_info.order_id WHERE flow_order_info.channel_id = 104 AND flow_order_info.status = 6 )t2; """ #兆蓉移动WT通道成功次数统计 zr_sql = """SELECT SUM(CASE WHEN cnt BETWEEN 0 AND 5 THEN 1 ELSE 0 END) AS 'A[1-5]', SUM(CASE WHEN cnt BETWEEN 6 AND 10 THEN 1 ELSE 0 END) AS 'A[6-10]', SUM(CASE WHEN cnt BETWEEN 11 AND 15 THEN 1 ELSE 0 END) AS 'A[11-15]', SUM(CASE WHEN cnt BETWEEN 16 AND 20 THEN 1 ELSE 0 END) AS 'B[16-20]', SUM(CASE WHEN cnt BETWEEN 21 AND 30 THEN 1 ELSE 0 END) AS 'C[21-30]', SUM(CASE WHEN cnt BETWEEN 31 AND 40 THEN 1 ELSE 0 END) AS 'D[31-40]', SUM(CASE WHEN cnt BETWEEN 41 AND 50 THEN 1 ELSE 0 END) AS 'E[41-50]', SUM(CASE WHEN cnt BETWEEN 51 AND 60 THEN 1 ELSE 0 END) AS 'F[51-60]', SUM(CASE WHEN cnt > 61 THEN 1 ELSE 0 END) AS 'G[>60]', COUNT(*) AS 'H[总订单数]' FROM ( SELECT t1.order_id, t1.cnt FROM (SELECT order_id, COUNT(*) AS cnt FROM mobile_flow_dispatch_rec WHERE trans_id = 95 AND create_date BETWEEN ' """ + yesterdayStart + """ ' AND ' """ + yesterdayEnd + """ ' GROUP BY order_id ORDER BY cnt ASC) t1 LEFT JOIN flow_order_info ON t1.order_id = flow_order_info.order_id WHERE flow_order_info.channel_id = 95 AND flow_order_info.status = 6 )t2; """ #兆蓉联通WT通道成功次数统计 zr_uni_sql = """SELECT SUM(CASE WHEN cnt BETWEEN 0 AND 5 THEN 1 ELSE 0 END) AS 'A[1-5]', SUM(CASE WHEN cnt BETWEEN 6 AND 10 THEN 1 ELSE 0 END) AS 'A[6-10]', SUM(CASE WHEN cnt BETWEEN 11 AND 15 THEN 1 ELSE 0 END) AS 'A[11-15]', SUM(CASE WHEN cnt BETWEEN 16 AND 20 THEN 1 ELSE 0 END) AS 'B[16-20]', SUM(CASE WHEN cnt BETWEEN 21 AND 30 THEN 1 ELSE 0 END) AS 'C[21-30]', SUM(CASE WHEN cnt BETWEEN 31 AND 40 THEN 1 ELSE 0 END) AS 'D[31-40]', SUM(CASE WHEN cnt BETWEEN 41 AND 50 THEN 1 ELSE 0 END) AS 'E[41-50]', SUM(CASE WHEN cnt BETWEEN 51 AND 60 THEN 1 ELSE 0 END) AS 'F[51-60]', SUM(CASE WHEN cnt > 61 THEN 1 ELSE 0 END) AS 'G[>60]', COUNT(*) AS 'H[总订单数]' FROM ( SELECT t1.order_id, t1.cnt FROM (SELECT order_id, COUNT(*) AS cnt FROM mobile_flow_dispatch_rec WHERE trans_id = 116 AND create_date BETWEEN ' """ + yesterdayStart + """ ' AND ' """ + yesterdayEnd + """ ' GROUP BY order_id ORDER BY cnt ASC) t1 LEFT JOIN flow_order_info ON t1.order_id = flow_order_info.order_id WHERE flow_order_info.channel_id = 116 AND flow_order_info.status = 6 )t2; """ #兆蓉电信WT通道成功次数统计 zr_tel_sql = """SELECT SUM(CASE WHEN cnt BETWEEN 0 AND 5 THEN 1 ELSE 0 END) AS 'A[1-5]', SUM(CASE WHEN cnt BETWEEN 6 AND 10 THEN 1 ELSE 0 END) AS 'A[6-10]', SUM(CASE WHEN cnt BETWEEN 11 AND 15 THEN 1 ELSE 0 END) AS 'A[11-15]', SUM(CASE WHEN cnt BETWEEN 16 AND 20 THEN 1 ELSE 0 END) AS 'B[16-20]', SUM(CASE WHEN cnt BETWEEN 21 AND 30 THEN 1 ELSE 0 END) AS 'C[21-30]', SUM(CASE WHEN cnt BETWEEN 31 AND 40 THEN 1 ELSE 0 END) AS 'D[31-40]', SUM(CASE WHEN cnt BETWEEN 41 AND 50 THEN 1 ELSE 0 END) AS 'E[41-50]', SUM(CASE WHEN cnt BETWEEN 51 AND 60 THEN 1 ELSE 0 END) AS 'F[51-60]', SUM(CASE WHEN cnt > 61 THEN 1 ELSE 0 END) AS 'G[>60]', COUNT(*) AS 'H[总订单数]' FROM ( SELECT t1.order_id, t1.cnt FROM (SELECT order_id, COUNT(*) AS cnt FROM mobile_flow_dispatch_rec WHERE trans_id = 105 AND create_date BETWEEN ' """ + yesterdayStart + """ ' AND ' """ + yesterdayEnd + """ ' GROUP BY order_id ORDER BY cnt ASC) t1 LEFT JOIN flow_order_info ON t1.order_id = flow_order_info.order_id WHERE flow_order_info.channel_id = 105 AND flow_order_info.status = 6 )t2; """ #表格信息 file_sheet = '通道总数' file_name = '通道数据' + str(yesterday) + '.xls' fLocate = r'/home/dukun/work/passData/' + file_name #邮件信息 #sender = 'd2759360205@163.com' #发件人邮箱 sender = 'youzixifeng@bluefire.top' #收件人邮箱，可以多个（列表形式）群发 receiver = ['fengfei@bluefire.top','steven@bluefire.top','skyzyk@bluefire.top','miaomiao@bluefire.top'] mail_user = 'youzixifeng@bluefire.top' #发件人姓名 #mail_pass = 'YCHFLABGLFGSLSJZ' #smtp授权163 mail_pass = 'Dukun864633403' mail_host = 'smtp.exmail.qq.com' #邮箱服务器 subject = "通道数据统计" #邮件标题 content = ' ' #邮件正文 #邮件正文 #__file_____ #file_subject='Gave', 'you', 'a', 'piece', 'of', 'shit.' #sheet标题 style1,style2,style3 = setStyle() writeToExcel(fLocate,file_sheet,style1,style2,style3) sendMail(sender,receiver,mail_user,mail_pass,mail_host,subject,content,fLocate,file_name) print(fLocate) """ while True: now = datetime.datetime.now() if now.hour == 8 and now.minute == 46: style = setStyle() writeToExcel(fLocate,file_sheet,style) sendMail(sender,receiver,mail_user,mail_pass,mail_host,subject,content,fLocate,file_name) #每隔60秒检测一次 time.sleep(60) """