#encoding=utf-8

 import cx_Oracle as oracle

 import MYSQLdb as mysql

 from datetime import datetime

 import xlsxwriter

 import sys

 '''

 author:poleaxe

 function:实现统计应用业务量

 原理：读取按照格式的yewu.sql，再将SQL分别查询并返回结果到列表，

       最后创建xlsx文件并写入列表结果

 yewu.sql格式：

 --系统

 --业务指标

 --数据库

 select count(1)/#day from dual

 where createdate > #starttime

 and createdate < #endtime

 --end

 依此类推

 '''

 reload(sys)

 sys.setdefaultencoding('gbk')

 username = ''

 password = ''

 epcis = ''

 epods = ''

 cxcc = ''

 nba4ot = ''

 claim = ''

 sqlfile = 'yewu.sql'

 day=''

 starttime = '2017-05-01'

 endtime = '2017-06-01'

 #格式化yewu.sql文件，按照系统、业务、数据库、SQL分割为一个二维列表

 def sql_file(sqlfile):

     a = open(sqlfile).read.split('--end')

     b = []

     for i in a:

         if i=='' or i =="\n\n":

             continue

         for j in  i.split('--'):

             b.append(j.strip()) #清理列表元素空格

     arr1 = []

     arr2 = []

     flag = 0

     for k in b:

         while flag >3: #创建有4个元素的arr2加入arr1列表

             arr1.append(arr2)

             #初始化arr2

             flag = 0

             arr2 = []

             print arr1

         else:

             if  k!='' and k!='\n\n' and k!='\n\n\n':

                 arr2.append(k)

                 flag += 1

     #替换列表里的#day、#starttime及#endtime

     for i in range(len(arr1)):

         arr1[i][3] = arr1[i][3].replace("#day",day)

         arr1[i][3] = arr1[i][3].replace("#starttime",starttime)

         arr1[i][3] = arr1[i][3].replace("#endtime", endtime)

     return arr1

 #查询二维列表的oracle SQL语句

 def oracle_select(db,sqlstr):

     listnew = ''

     sql = ''

     try:

         conn = oralce.connect(username,password,db)

         cur = conn.cusor()

         sql = cur.excecute(sqlstr)

     except Exception, e:

         print e.message

     else:

         listnew = sql.fetchone()

     cur.close()

     conn.close

     return listnew

 #查询没有mysql数据库的，果断pass哈

 def mysql_select(db,sql):

     pass

 #创建EXCEL表格

 def write_excel(info):

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

     xlsxfilename = '产险IT经营情况报表'.decode('utf-8').encode('gbk')

     workbook = xlsxwriter.Workbook(xlsxfilename,{'constant_memory':True})

     worksheet = workbook.add_worksheet()

     title = ["关键系统名称","业务类别","数据库","日均业务量"]

     for i in range(len(title):

         worksheet.write(0,i,title[i].decode('uft-8').encode('gbk'))

     for i in range(len(info)):

         for row, row_date in enumerate(info[i]):

             print row,row_date

             worksheet.write(i+1,row,row_date)

     workbook.close()

     return xlsxfilename

 def main():

     info = sql_file(sqlfile)

     list_result = []

     for sql in info:

         list_result.append(oracle_select(sql[2],sql[3])

     listnew = []

     for i in list_result:

         listnew.append(i[0])

     for j in range(len(info)):

         info[j][3] = listnew[j]

     print info

     write_excel(info)

 if __name__ == '__main__':

     main()