data-verification/EverydayBill/count_data.py

# -*- 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)
"""