python脚本实例

╰半橙微兮° 2022-06-09 05:20 178阅读 0赞

cat DBConnector.py  
  
\#!/usr/bin/env python  
\# -\*- coding: utf-8 -\*-  
\# haoren @ 2017-08-21 21:33:05  
  
import mysql.connector  
from logger import logging  
  
class DBConnector:          \#定义一个类  
    "数据库抽象类"  
  
     def \_\_init\_\_(self, dbHost, dbPort, dbUser, dbPass, dbName):         \#定义函数  
        self.\_dbHost = dbHost  
        self.\_dbPort = dbPort  
        self.\_dbUser = dbUser  
        self.\_dbPass = dbPass  
        self.\_dbName = dbName  
  
     def connect(self):                \#定义方法  
        try:  
            self.\_dbConn =  mysql.connector.connect (host=self.\_dbHost, port=self.\_dbPort, user=self.\_dbUser, passwd=self.\_dbPass, db=self.\_dbName)  
            self.\_dbCursor =  self.\_dbConn.cursor()  
        except  Exception,e:  
             logging.info ("connect failed(%s) : %s %s=%s %s", e, self.\_dbHost, self.\_dbUser, self.\_dbPass, self.\_dbName)  
            return False  
        else:  
             logging.info ("connect successed : %s %s=%s %s", self.\_dbHost, self.\_dbUser, self.\_dbPass, self.\_dbName)  
            return True  
      
     def execSqlStep(self, sql):               \#定义方法  
        try:  
            count = self.\_dbCursor.execute(sql)  
            results = self.\_dbCursor.fetchall()  
        except:  
            \#self.close()  
            return False  
        else:  
            return results  
      
     def execSql(self, sql):                       \#定义方法  
        results = self.execSqlStep(sql)  
        if results==False:  
            if self.connect()==False:  
                return False  
            else:  
                results =  self.execSqlStep(sql)            \#调用前面定义的方法  
        return results  
      
     def close(self):                                                  \#定义方法  
        try:  
            self.\_dbCursor.close()  
            self.\_dbConn.close()  
        except:  
            return False  
        else:  
            return True  
  
cat DBPool.py  
  
\#!/usr/bin/env python  
\# -\*- coding: utf-8 -\*-  
\# haoren @ 2017-08-21 21:33:05  
  
import copy  
from DBConnector import DBConnector  
  
def MakeResultNone2Zero(data):                       \#定义函数  
    if type(data)==tuple:  
        data = list(data)                                             \#将元祖转为列表  
    r = 0  
    for row in data:  
        if not (type(row)==list or type(row)==tuple):  
            if row==None or type(row)=="NoneType":  
                data\[r\] = 0  
            continue  
        if type(row)==tuple:  
            data\[r\] = list(row)  
            row = list(row)  
        c = 0  
        for col in row:  
            if col==None or type(col)=="NoneType":  
                data\[r\]\[c\] = 0  
                row\[c\] = 0  
            c += 1  
        if type(row)==list:  
            data\[r\] = tuple(row)  
        r += 1  
    if type(data)==list:  
        data = tuple(data)  
  
  
class DBPool:                                     \#定义一个类  
    "数据库抽象类"  
    \_dbCfg = \{\}  
    \_dbMap = \{\}  
  
     def \_\_init\_\_(self, dbCfg):             \#定义一个方法  
        DBPool.\_dbCfg = copy.deepcopy(dbCfg)  
  
     def connect(self):                           \#定义一个方法  
        if len(DBPool.\_dbCfg)<=0:  
            return False  
        self.close()  
        for key in DBPool.\_dbCfg:  
            cfg = DBPool.\_dbCfg\[key\]  
            handler = DBConnector(cfg\["dbHost"\], cfg\["dbPort"\], cfg\["dbUser"\], cfg\["dbPass"\], cfg\["dbName"\])  
            if handler.connect():  
                DBPool.\_dbMap\[key\] = handler  
                print "connect successed(%s)" % key  
            else:  
                print "connect failed(%s)" % key  
        return True  
  
     def execSql(self, dbKey, sql):              \#定义一个方法  
        if not DBPool.\_dbMap.has\_key(dbKey):  
            print "invalid dbKey(%s) | sql(%s)" % (dbKey, sql)  
            \#return False  
            return ""  
        handler = DBPool.\_dbMap\[dbKey\]  
        results = handler.execSql(sql)  
        if results==False:  
            \#del DBPool.\_dbCfg\[dbKey\]  
            \#del DBPool.\_dbMap\[dbKey\]  
            print "execSql failed(%s)" % (sql)  
            return ""  
        \#MakeResultNone2Zero(results)  
        return results  
      
     def close(self):                      \#定义方法  
        for key in DBPool.\_dbMap:  
            handler = DBPool.\_dbMap\[key\]  
            if handler.close():  
                print "close successed(%s)" % key  
            else:  
                print "close failed(%s)" % key  
        DBPool.\_dbMap.clear()  
        return True  
  
  
cat logger.py  
\#!/usr/bin/env python  
\# -\*- coding: utf-8 -\*-  
\# haoren @ 2017-08-22 21:55:42  
import logging    
  
logging. basicConfig (level=logging.INFO,   
                    filename='./AutoSettle.log',   
                    filemode='w',   
                    format='%(asctime)s - %(filename)s:%(lineno)s\[%(funcName)s\] - %(levelname)s: %(message)s')  
  
  
cat LoadData.py  
  
\#!/usr/bin/env python  
\# -\*- coding: gbk -\*-  
\# haoren @ 2017-08-22 15:51:24  
  
\#from sys import argv  
from logger import logging  
from DBPool import DBPool  
import time  
import datetime  
  
cfgmap = \{                                        \#定义大字典，字典进行嵌套  
    "IMDB" : \{   
        "dbHost" : "172.17.112.3",  
        "dbPort" : 3306,  
        "dbUser" : "haoren",  
        "dbPass" : "skjsdhfklsadhf",  
        "dbName" : "RIMDB"  
        \},   
    "GMDB" : \{   
        "dbHost" : "172.17.112.2",  
        "dbPort" : 3306,  
        "dbUser" : "haoren",  
        "dbPass" : "skjsdhfklsadhf",  
        "dbName" : "PGMDB"  
        \},   
    "JIESUANDB" : \{   
        "dbHost" : "172.17.112.1",  
        "dbPort" : 3306,  
        "dbUser" : "haoren",  
        "dbPass" : "skjsdhfklsadhf",  
        "dbName" : "JIESUANDB"  
        \}  
    \}  
  
  
\#公司化结算平台列表  
def MakeChannelCorpList(dbKey, startTime, endTime):           \#定义函数  
    sql = "select CHANNELID from CORPSETTLELOG where TIME>=%d and TIME<%d group by CHANNELID;" % (startTime, endTime)  
    return db.execSql(dbKey, sql)  
  
  
\#公司化设置平台列表  
def MakeChannelSettleList(dbKey, startTime, endTime):         \#定义函数  
    sql = "select CHANNELID from SPECIALCHANNEL group by CHANNELID;"  
    return db.execSql(dbKey, sql)  
  
  
\#平台外部ID列表  
def MakeChannelOutIDList(dbKey, startTime, endTime):       \#定义函数  
    sql = "select OLDID,NEWID from CHANNELIDCHANGELOG;"  
    return db.execSql(dbKey, sql)  
  
  
\#表演者外部ID列表  
def MakeSingerOutIDList(dbKey, startTime, endTime):          \#定义函数  
    sql = "select INNERID,OUTERID from IMIDCHANGELOG;"  
    return db.execSql(dbKey, sql)  
  
  
\#公司化老板设置平台列表  
def MakeChannelOwnerList(dbKey, startTime, endTime):      \#定义函数  
    sql = "select OWNERID,CHANNELID from SPECIALCHANNEL group by CHANNELID;"  
    return db.execSql(dbKey, sql)  
  
  
\#表演者结算列表  
def MakeSingerCorpList(dbKey, startTime, endTime, channelID):    \#定义函数  
    sql = "select USERID from CORPSETTLELOG where TIME>=%d and TIME<%d AND CHANNELID=%d AND USERID<>0 group by USERID;" % (startTime, endTime, channelID)  
    return db.execSql(dbKey, sql)  
  
  
\#表演者收入列表  
def MakeSingerIncomList(dbKey, channelID):                     \#定义函数  
    sql = "select SINGERID from %s where CHANNELID=%d group by SINGERID;" % (coinTable, channelID)  
    return db.execSql(dbKey, sql)  
  
  
\#表演者签约列表  
def MakeSingerSignList(dbKey, startTime, endTime, channelID):        \#定义函数  
    sql = "select PERFORMERID from VCHANNELPERFORMER where CHANNELID=%d group by PERFORMERID;" % (channelID)  
    return db.execSql(dbKey, sql)  
  
  
\#表演者收入详细信息  
\#结算平台收入  
singerCorpIncomIdx = 9  
channelCorpIncomIdx = 3  
\#非结算平台收入  
singerNoCorpIncomIdx = 10  
\#表演者消费  
singerConsumeIdx = 12  
\#表演者守护  
singerGuardIncomIdx = 13  
\#平台守护  
channelGuardIncomIdx = 4  
\#表演者的结算人民币  
singerCorpSettleIdx = 8  
\#表演者结算差值  
singerSubIdx = 17  
\#表演者上次结余  
singerYestodayCurIdx = 14  
\#表演者外部ID  
singerOutIDIdx = 7  
\#平台收入数据表  
coinTable = ""  
goldTable = ""  
  
  
def GetListOneCol(t, i):                    \#定义函数  
    if len(t)<=0:  
        return False  
    for row in t:  
        if len(row)<=i:  
            return False  
        listTemp.append(row\[i\])  
    return listTemp  
  
  
def printResult(t):                            \#定义函数  
    print type(t)  
    for row in t:  
        if not (type(row)==list or type(row)==tuple):  
            print row  
        else:  
            print "meet a table"  
            printResult(row)  
  
  
def MakeNone2Default(value, defaultValue=0):             \#定义函数  
    if value==None or type(value)=="NoneType":  
        return defaultValue  
    else:  
        return value  
  
  
def MakeStrLastMonth(srcStr):                          \#定义函数  
    firstStr = srcStr\[0:-6\]  
    monthStr = srcStr\[-6:\]  
    timeSettle = time.strptime(monthStr, '%Y%m')  
    lastMonth = timeSettle.tm\_mon - 1  
    lastYear = timeSettle.tm\_year  
    if lastMonth==0:  
        lastMonth = 12  
        lastYear -= 1  
    retTime = datetime.datetime(month=lastMonth, year=lastYear, day=timeSettle.tm\_mday)  
    timeStr = retTime.strftime("%Y%m")  
    retStr = firstStr + timeStr  
    return retStr  
  
  
def MakeSingerInfo(startTime, endTime, singerID, channelID, sign):        \#定义函数  
    \#结算平台  
    if channelOutIDDict.has\_key(channelID):  
        channelOutID = channelOutIDDict\[channelID\]  
    else:  
        channelOutID = channelID  
    if singerOutIDDict.has\_key(singerID):  
        singerOutID = singerOutIDDict\[singerID\]  
    else:  
        singerOutID = singerID  
      
    \#表演者昵称  
    tableNickName = "CHARBASE%d" % (singerID%10)  
    sql="select CHANNELNICKNAME from %s where ID=%s LIMIT 1;" % (tableNickName, singerID)  
    singerNickNameTemp = db.execSql("IMDB", sql)  
    if len(singerNickNameTemp)>0:  
        singerNickName = MakeNone2Default(singerNickNameTemp\[0\]\[0\], "")  
        try:  
            singerNickName = singerNickName.encode('unicode-escape').decode('string\_escape')  
            singerNickName = singerNickName.decode('gbk')  
        except Exception as e:  
            logging.info("singer(%s) nick name", singerID)  
    else:  
        singerNickName = ""  
  
  
     \#结算平台收入  
    sql1="select sum(SINGERRECVGOLD)/100,sum(CHANNELRECVGOLD)/100 from %s where SINGERID=%s AND CHANNELID=%s;" % (coinTable, singerID, channelID)  
    listTemp = list(db.execSql("JIESUANDB", sql1))  
    if len(listTemp)>0:  
        singerCorpIncom = MakeNone2Default(listTemp\[0\]\[0\])  
        channelCorpIncom = MakeNone2Default(listTemp\[0\]\[1\])  
    else:  
        singerCorpIncom = 0  
        channelCorpIncom = 0  
  
  
     \#非结算平台收入  
    sql2="select sum(SINGERRECVGOLD)/100 from %s where SINGERID=%s AND CHANNELID<>%s;" % (coinTable, singerID, channelID)  
    singerNoCorpIncomTemp = db.execSql("JIESUANDB", sql2)  
    if len(singerNoCorpIncomTemp)>0:  
        singerNoCorpIncom = MakeNone2Default(singerNoCorpIncomTemp\[0\]\[0\])  
    else:  
        singerNoCorpIncom = 0  
  
  
     \#非结算平台  
    sql6="select CHANNELID from %s where SINGERID=%s AND CHANNELID<>%s GROUP BY CHANNELID;" % (coinTable, singerID, channelID)  
    singerNoCorpChannelTemp = db.execSql("JIESUANDB", sql6)  
    if len(singerNoCorpChannelTemp)>0:  
        singerNoCorpChannel = MakeNone2Default(singerNoCorpChannelTemp\[0\]\[0\])  
    else:  
        singerNoCorpChannel = 0  
  
  
     \#表演者消费  
    sql3="select sum(CONSUMEGOLD)/100 from %s where PRESENTERID=%s;" % (goldTable, singerID)  
    singerConsumeTemp = db.execSql("JIESUANDB", sql3)  
    if len(singerConsumeTemp)>0:  
        singerConsume = MakeNone2Default(singerConsumeTemp\[0\]\[0\])  
    else:  
        singerConsume = 0  
  
  
     \#平台内的平台守护收入和表演者守护收入  
    sql4="select sum(SINGERRECVGOLD)/100,sum(CHANNELRECVGOLD)/100 from %s where SINGERID=%s AND CHANNELID=%s AND TYPE=2;" % (coinTable, singerID, channelID)  
    listTemp = list(db.execSql("JIESUANDB", sql4))  
    if len(listTemp)>0:  
        singerGuardIncom = MakeNone2Default(listTemp\[0\]\[0\])  
        channelGuardIncom = MakeNone2Default(listTemp\[0\]\[1\])  
    else:  
        singerGuardIncom = 0  
        channelGuardIncom = 0  
  
  
     \#表演者的结算人民币  
    sql5="select sum(GOLD)/100 from CORPSETTLELOG where TIME>=%s AND TIME<%s AND USERID=%s AND CHANNELID=%s;" % (startTime, endTime, singerID, channelID)  
    singerCorpSettleTemp = db.execSql("IMDB", sql5)  
    if len(singerCorpSettleTemp)>0:  
        singerCorpSettle = MakeNone2Default(singerCorpSettleTemp\[0\]\[0\])  
    else:  
        singerCorpSettle = 0  
  
  
     \#表演者昨天结余人民币  
    singerYestodayCur = 0  
    singerYestodayTime = ""  
    monthStr = coinTable  
    for num in range(0, 3):  
        if num==0:  
            sql7="select (CURRENTSINGERGOLD-SINGERRECVGOLD)/100,OPTIME from %s where SINGERID=%s ORDER BY OPTIME,'INDEX' LIMIT 1;" % (monthStr, singerID)  
        elif singerCorpSettle<=0:  
            break  
        else:  
            sql7="select (CURRENTSINGERGOLD-SINGERRECVGOLD)/100,OPTIME from %s where SINGERID=%s ORDER BY OPTIME DESC,'INDEX' DESC LIMIT 1;" % (monthStr, singerID)  
        listTemp = db.execSql("JIESUANDB", sql7)  
        if len(listTemp)>0:  
            singerYestodayCur = MakeNone2Default(listTemp\[0\]\[0\])  
            singerYestodayTime = MakeNone2Default(listTemp\[0\]\[1\])  
            break  
        monthStr = MakeStrLastMonth(monthStr)  
  
  
     \#表演者今天结余人民币  
    sql8="select (CURRENTSINGERGOLD)/100,OPTIME from %s where SINGERID=%s ORDER BY OPTIME DESC, 'INDEX' DESC LIMIT 1;" % (coinTable, singerID)  
    listTemp = db.execSql("JIESUANDB", sql8)  
    if len(listTemp)>0:  
        singerTodayCur = MakeNone2Default(listTemp\[0\]\[0\])  
        singerTodayTime = MakeNone2Default(listTemp\[0\]\[1\])  
    else:  
        singerTodayCur = 0  
        singerTodayTime = ""  
  
  
     \#表演者消费不为0，结余取当天最小和最大值  
    if singerConsume>0:  
        \#当天消费第一笔结余  
        sql9="select (CURRENTPRESENTERGOLD+CONSUMEGOLD)/100,OPTIME from %s where PRESENTERID=%s ORDER BY OPTIME,'INDEX' LIMIT 1;" % (goldTable, singerID)  
        listTemp = db.execSql("JIESUANDB", sql9)  
        if len(listTemp)>0:  
            singerGoldYestodayCur = MakeNone2Default(listTemp\[0\]\[0\])  
            singerGoldYestodayTime = MakeNone2Default(listTemp\[0\]\[1\])  
        else:  
            singerGoldYestodayCur = 0  
            singerGoldYestodayTime = ""  
          
         \#当天消费最后一笔结余  
        sql10="select (CURRENTPRESENTERGOLD)/100,OPTIME from %s where PRESENTERID=%s ORDER BY OPTIME DESC,'INDEX' DESC LIMIT 1;" % (goldTable, singerID)  
        listTemp = db.execSql("JIESUANDB", sql10)  
        if len(listTemp)>0:  
            singerGoldTodayCur = MakeNone2Default(listTemp\[0\]\[0\])  
            singerGoldTodayTime = MakeNone2Default(listTemp\[0\]\[1\])  
        else:  
            singerGoldTodayCur = 0  
            singerGoldTodayTime = ""  
  
  
         \#都有值，取时间最小的  
        if singerYestodayTime and singerTodayTime:  
            \#昨天结余人民币消费时间小泽取齐值  
            if singerYestodayTime>singerGoldYestodayTime:  
                singerYestodayCur = singerGoldYestodayCur  
            \#今日结余，人民币消费时间大取齐值  
            if singerTodayTime<=singerGoldTodayTime:  
                singerTodayCur = singerGoldTodayCur  
        else:  
            singerYestodayCur = singerGoldYestodayCur  
            singerTodayCur = singerGoldTodayCur  
              
     \#表演者实际结算数据  
    if singerTodayCur and singerYestodayCur:  
        singerCurSub = singerTodayCur - singerYestodayCur  
    else:  
        singerCurSub=0  
  
  
     \#表演者数据核算  
    if sign:  
        singerSub = singerCorpSettle - (singerCorpIncom+singerNoCorpIncom-singerConsume)  
    else:  
        singerSub = 0  
  
  
    if sign:  
        signInfo="签约表演者"  
    else:  
        signInfo="非签约表演者"  
  
  
    singerCorpSettleRMB = singerCorpSettle / 200  
  
  
    infoTemp = \[signInfo, channelID, channelOutID, channelCorpIncom, channelGuardIncom, singerNickName, singerID, singerOutID, singerCorpSettle, singerCorpIncom, singerNoCorpIncom, singerNoCorpChannel, singerConsume, singerGuardIncom, singerYestodayCur, singerTodayCur, singerCurSub, singerSub, singerCorpSettleRMB\]  
    return infoTemp  
  
  
def ListDifference(listA, listB):             \#定义函数  
    listTempA = \[\]  
    listTempB = \[\]  
    for row in listA:  
        listTempA.append(row\[0\])  
    for row in listB:  
        listTempB.append(row\[0\])  
    tupleA = tuple(listTempA)  
    tupleB = tuple(listTempB)                                            \# 将列表转换为元组   
     tRet = set(tupleA).difference(set(tupleB))            \#元祖比较 ，set() 函数创建一个无序不重复元素集，可进行关系测试，删除重复数据，还可以计算交集、差集、并集等。  
    return list(tRet)  
  
\# python的set和其他语言类似, 是一个无序不重复元素集, 基本功能包括关系测试和消除重复元素. 集合对象还支持union(联合), intersection(交), difference(差)和sysmmetric difference(对称差集)等数学运算.    
  
\# sets 支持 x in set, len(set),和 for x in set。作为一个无序的集合，sets不记录元素位置或者插入点。因此，sets不支持 indexing, slicing, 或其它类序列（sequence-like）的操作。   
  
 def MakeNameStr(srcStr, defaultStr=""):              \#定义函数  
    if srcStr=="":  
        return defaultStr  
    try:  
        strTemp = srcStr.encode('unicode-escape').decode('string\_escape')  
        destStr = strTemp.decode('gbk')  
    except Exception as e:  
        destStr = defaultStr  
    return destStr  
  
  
def MakeSingerWithdrawRecoed(startTime, endTime, singerID, channelID):            \#定义函数  
    time1 = 0  
    time2 = startTime  
    timeTemp = time.localtime(time2)  
    lastMonth = timeTemp.tm\_mon - 1  
    lastYear = timeTemp.tm\_year  
    if lastMonth==0:  
        lastMonth = 12  
        lastYear -= 1  
    retTime = datetime.datetime(month=lastMonth, year=lastYear, day=timeTemp.tm\_mday)  
    time1 = time.mktime(retTime.timetuple())  
    sql = "select USERID,CASH,BANKCARD,NAME,IDENTITYCARD,PHONE,PROVINCE,CITY,REGION,BANKBRANCH,SERIALNUM,TIME from SINGERWITHDRAWRECORD where TIME>=%s and TIME<%s and USERID=%s and WITHDRAWTYPE=0 and TRANFERSTATE<>1;" % (time1, time2, singerID)  
    singerWithdrawRecord = \[\]  
    singerWithdrawRecordTemp = db.execSql("IMDB", sql)  
    channelSingerWithdrawRecordTemp = \[\]  
    if len(singerWithdrawRecordTemp)>0:  
        r = 0  
        for rowTemp in singerWithdrawRecordTemp:  
            row = list(rowTemp)  
            strTemp = MakeNone2Default(row\[3\], "")  
            row\[3\] = MakeNameStr(strTemp)  
            strTemp = MakeNone2Default(row\[6\], "")  
            row\[6\] = MakeNameStr(strTemp)  
            strTemp = MakeNone2Default(row\[7\], "")  
            row\[7\] = MakeNameStr(strTemp)  
            strTemp = MakeNone2Default(row\[8\], "")  
            row\[8\] = MakeNameStr(strTemp)  
            strTemp = MakeNone2Default(row\[9\], "")  
            row\[9\] = MakeNameStr(strTemp)  
            singerWithdrawRecord.append(row)  
            r += 1  
        print "have a record singer(%s)" % singerID  
        if channelSingerWithdrawRecordDict.has\_key(channelID):  
            channelSingerWithdrawRecordTemp = channelSingerWithdrawRecordDict\[channelID\]  
            channelSingerWithdrawRecordTemp += singerWithdrawRecord  
            channelSingerWithdrawRecordDict\[channelID\] = channelSingerWithdrawRecordTemp  
        else:  
            colName = \["用户ID", "金额", "银行卡号", "用户名", "身份证号", "手机号", "省份", "城市", "区县", "银行", "序列号", "时间"\]  
            channelSingerWithdrawRecordTemp.append(colName)  
            channelSingerWithdrawRecordTemp += singerWithdrawRecord  
            channelSingerWithdrawRecordDict\[channelID\] = channelSingerWithdrawRecordTemp  
  
  
  
  
\#产生并合并表演者名单  
def MergerSingerList(startTime, endTime, channelID):           \#定义函数  
    singerCorpList = MakeSingerCorpList("IMDB", startTime, endTime, channelID)  
    singerIncomList = MakeSingerIncomList("JIESUANDB", channelID)  
    \#print type(singerCorpList), type(singerIncomList)  
  
  
    \#singerNoSignList = tuple( singerIncomList.difference(singerCorpList) )  
     singerNoSignList = ListDifference(singerIncomList, singerCorpList)   \#调用函数  
  
  
    channelInfoTemp = \[\]  
    colName = \["类别", "平台内部ID", "平台外部ID", "平台收入", "平台守护收入", "表演者昵称", "表演者内部ID", "表演者外部ID", "表演者结算", "表演者在结算平台收入", "表演者在非结算平台收入", "非结算平台", "表演者消费", "表演者守护收入", "上次结余", "本次结余", "结余差额", "表演者结算差值", "表演者结算金额"\]  
    channelInfoTemp.append(colName)  
      
    sumChannelCorpIncom = 0  
    sumSingerCorpIncom = 0  
    sumSingerCorpSettle = 0  
    sumSingerSub = 0  
    \#守护  
    sumSingerGuardIncom = 0  
    sumChannelGuardIncom = 0  
    \#表演者消费  
    sumSingerConsume = 0  
    \#所有表演者非结算平台收入  
    sumSingerNoCorpIncom = 0  
    \#表演者上次结余  
    sumSingerYestodayCur = 0  
  
  
    for singerID in singerCorpList:  
        print "sign singer(%s) in channel(%s)" % (singerID\[0\], channelID)  
        infoTemp = MakeSingerInfo(startTime, endTime, singerID\[0\], channelID, True)  
        channelInfoTemp.append(infoTemp)  
        sumChannelCorpIncom += infoTemp\[channelCorpIncomIdx\]  
        sumSingerCorpIncom += infoTemp\[singerCorpIncomIdx\]  
        sumSingerCorpSettle += infoTemp\[singerCorpSettleIdx\]  
        sumSingerSub += infoTemp\[singerSubIdx\]  
        sumSingerGuardIncom += infoTemp\[singerGuardIncomIdx\]  
        sumChannelGuardIncom += infoTemp\[channelGuardIncomIdx\]  
        sumSingerConsume += infoTemp\[singerConsumeIdx\]  
        sumSingerNoCorpIncom += infoTemp\[singerNoCorpIncomIdx\]  
        sumSingerYestodayCur += infoTemp\[singerYestodayCurIdx\]  
        MakeSingerWithdrawRecoed(startTime, endTime, singerID\[0\], channelID)  
  
  
    channelInfoTemp.append(\[\])  
  
  
    channelNoSignSingerInfoTemp = \[\]  
    for singerID in singerNoSignList:  
        print "not sign singer(%s) in channel(%s)" % (singerID, channelID)  
        infoTemp = MakeSingerInfo(startTime, endTime, singerID, channelID, False)  
        channelInfoTemp.append(infoTemp)  
        channelNoSignSingerInfoTemp.append(infoTemp)  
        sumChannelCorpIncom += infoTemp\[channelCorpIncomIdx\]  
        sumSingerCorpIncom += infoTemp\[singerCorpIncomIdx\]  
        sumSingerGuardIncom += infoTemp\[singerGuardIncomIdx\]  
        sumChannelGuardIncom += infoTemp\[channelGuardIncomIdx\]  
        MakeSingerWithdrawRecoed(startTime, endTime, singerID, channelID)  
    channelNoSignSingerInfoDict\[channelID\] = channelNoSignSingerInfoTemp  
  
  
    channelInfoTemp.append(\[\])  
  
  
    if sumChannelCorpIncom<=0:  
        singerChannelRadio = 0  
        guardSingerChannelRadio = 0  
    else:  
        singerChannelRadio = sumSingerCorpIncom / sumChannelCorpIncom  
        guardSingerChannelRadio = (sumSingerCorpIncom+sumChannelGuardIncom) / (sumChannelCorpIncom)  
    sumName = \["总计", "平台收入总计", "签约表演者结算总计", "所有表演者此平台收入总计", "表演者平台收入比值", "补偿守护比值", "签约表演者结算差值总计", "表演者守护收入总计", "表演者消费总计", "表演者其他平台收入", "表演者上次结余"\]  
    channelInfoTemp.append(sumName)  
    sumValue = \["", sumChannelCorpIncom, sumSingerCorpSettle, sumSingerCorpIncom, singerChannelRadio, guardSingerChannelRadio, sumSingerSub, sumSingerGuardIncom, sumSingerConsume, sumSingerNoCorpIncom, sumSingerYestodayCur\]  
    channelInfoTemp.append(sumValue)  
  
  
     \#平台的结算人民币  
    sql5="select sum(GOLD)/100 from CORPSETTLELOG where TIME>=%s and TIME<%s AND CHANNELID=%s AND USERID=0;" % (startTime, endTime, channelID)  
    channelCorpSettleTemp = db.execSql("IMDB", sql5)  
    if len(channelCorpSettleTemp)>0:  
        channelCorpSettle = channelCorpSettleTemp\[0\]\[0\]  
    else:  
        channelCorpSettle = 0  
  
  
     \#平台昨天结余人民币  
    sql7="select (CURRENTCHANNELGOLD-CHANNELRECVGOLD)/100 from %s where CHANNELID=%s ORDER BY OPTIME LIMIT 1;" % (coinTable, channelID)  
    channelYestodayCurTemp = db.execSql("JIESUANDB", sql7)  
    if len(channelYestodayCurTemp)>0:  
        channelYestodayCur = channelYestodayCurTemp\[0\]\[0\]  
    else:  
        channelYestodayCur = 0  
  
  
     \#平台今天结余人民币  
    sql8="select (CURRENTCHANNELGOLD)/100 from %s where CHANNELID=%s ORDER BY OPTIME DESC LIMIT 1;" % (coinTable, channelID)  
    channelTodayCurTemp = db.execSql("JIESUANDB", sql8)  
    if len(channelTodayCurTemp)>0:  
        channelTodayCur = channelTodayCurTemp\[0\]\[0\]  
    else:  
        channelTodayCur = 0  
      
     \#表演者实际结算数据  
    if channelTodayCur and channelYestodayCur:  
        channelCurSub = float(channelTodayCur) - float(channelYestodayCur)  
    else:  
        channelCurSub = 0  
      
    \#平台结算差值  
    channelSub = channelCorpSettle - sumChannelCorpIncom  
    subName = \["平台核算", "平台结算人民币", "平台上次结余", "平台本次结余", "平台结余差额", "平台结算差值"\]  
    channelInfoTemp.append(subName)  
    subValue = \["", channelCorpSettle, channelYestodayCur, channelTodayCur, channelCurSub, channelSub\]  
    channelInfoTemp.append(subValue)  
    channelInfoDict\[channelID\] = channelInfoTemp  
  
  
\#每平台  
def EveryChannel(startTime, endTime):  
    channelCorpList = MakeChannelCorpList("IMDB", startTime, endTime)  
    \#no use channelsettlelist  
    \#平台列表  
    \#printResult(channelCorpList)  
    for channelID in channelCorpList:  
        MergerSingerList(startTime, endTime, channelID\[0\])  
        logging.info("channelID:%d is Deal", channelID\[0\])  
  
  
channelOutIDDict = \{\}  
singerOutIDDict = \{\}  
channelOwnerDict = \{\}  
channelInfoDict = \{\}  
channelNoSignSingerInfoDict = \{\}  
channelSingerWithdrawRecordDict = \{\}  
  
  
def MakeList2Dict(t, d):  
    if type(t)==list or type(t)==tuple:  
        for row in t:  
            d\[row\[0\]\] = row\[1\]  
  
  
def MakeChannelOwnerDict(t, d):  
    if type(t)==list or type(t)==tuple:  
        for row in t:  
            if d.has\_key(row\[0\]):  
                value = d\[row\[0\]\]  
            else:  
                value = \[\]  
            value.append(row\[1\])  
            d\[row\[0\]\] = value  
  
  
def LoadData(timeSettle):  
    """  
    if len(argv)>1:  
        timeSettle = time.strptime(argv\[1\], '%Y%m%d')  
    else:  
        print "Please enter the date."  
        exit()  
    """  
    global db  
    global coinTable, goldTable  
    global channelOutIDDict, singerOutIDDict, channelOwnerDict, channelInfoDict  
    db = DBPool(cfgmap)  
    logging.info(" === load data start === ")  
    timeStr = time.strftime("%Y%m", timeSettle)  
    coinTable = "JIESUANTONGJI\_ZONG\_" + timeStr  
    goldTable = "JIESUANTONGJIGOLD\_ZONG\_" + timeStr  
    timeStamp = time.mktime(timeSettle)  
    timeStamp -= 28800  
    startTime = timeStamp  
    endTime = timeStamp + 86400  
      
    logging.info(" === connect DB start === ")  
    if not db.connect():  
        exit()  
    logging.info(" === connect DB complete === ")  
  
  
    channelOutIDList = MakeChannelOutIDList("IMDB", startTime, endTime)  
    MakeList2Dict(channelOutIDList, channelOutIDDict)  
    singerOutIDList = MakeSingerOutIDList("IMDB", startTime, endTime)  
    MakeList2Dict(singerOutIDList, singerOutIDDict)  
    channelOwnerList = MakeChannelOwnerList("IMDB", startTime, endTime)  
    MakeChannelOwnerDict(channelOwnerList, channelOwnerDict)  
  
  
    EveryChannel(startTime, endTime)  
  
  
    db.close()  
    logging.info(" === load data complete === ")  
    print "channel info (len:%s) | channel outid (len:%s) | owner (len:%s)" % (len(channelInfoDict), len(channelOutIDDict), len(channelOwnerDict))  
    return channelInfoDict  
  
  
\#db = DBPool(cfgmap)  
\#LoadData()  
  
 cat    MakeExcel.py   
   
\#!/usr/bin/env python  
\# -\*- coding: gbk -\*-  
\# haoren @ 2017-08-24 07:07:37  
  
from sys import argv  
from logger import logging  
import os  
import time  
import xlwt  
import LoadData  
import sys  
  
\#reload(sys)  
\#sys.setdefaultencoding('utf-8')  
  
channelOwnerName = \{   
    "69999" : "92公司",   
    "515" : "92公司",   
    "49338" : "98公司",   
    "51820" : "91公司",   
    "54577" : "91公司",   
    "55794" : "99公司-CC"'   
    "5685" : "92公司-秋寻",   
    "6040" : "91公司",   
    "6067" : "90公司",   
    "6395" : "997公司",   
    "63915" : "955公司",   
    "65935" : "92公司",   
    "75475" : "917公司",   
    "76141" : "91公司",   
    "78420" : "90公司",   
    "81538" : "99公司",   
    "8172" : "99公司",   
    "88909" : "95公司",   
    "87856" : "90公司",   
    "874" : "99公司",   
    "91486" : "60公司",   
    "91618" : "600公司",   
    "9108" : "99公司"  
    \}  
  
def WriteExcelDataOneSheet(excelHandler, name, data):  
    sheet = excelHandler.add\_sheet(name, True)  
    tallStyle = xlwt.easyxf("font: height 250;")  
    cMax = 0  
    r = 0  
    for row in data:  
        \#设置行高  
        sheet.row(r).set\_style(tallStyle)  
        c = 0  
        for col in row:  
            try:  
                sheet.write(r, c, row\[c\])  
            except:  
                print r, c, row\[c\]  
            c += 1  
        r += 1  
        cMax = max(cMax, c)  
    \#设置列宽  
    for cIdx in range(0, cMax):  
        sheet.col(cIdx).width = 4000  
    return True  
  
  
def Data2Number(data):  
    r = 0  
    for row in data:  
        c = 0  
        for col in row:  
            try:  
                number = float(row\[c\])  
                data\[r\]\[c\] = number  
            except ValueError:  
                data\[r\]\[c\] = row\[c\]  
            c += 1  
        r += 1  
    return data  
  
  
def MakeExcel():  
    if len(argv)>1:  
        timeSettle = time.strptime(argv\[1\], '%Y%m%d')  
    else:  
        print "Please enter the date."  
        exit()  
    runStartTime = time.time()  
    LoadData.LoadData(timeSettle)  
    print "====== make excel start ======"  
    curDir = os.getcwd() + "/month/gongsi/"  
    channelInfoDict = LoadData.channelInfoDict  
    channelOutIDDict = LoadData.channelOutIDDict  
    ownerDict = LoadData.channelOwnerDict  
    singerWithdrawRecordDict = LoadData.channelSingerWithdrawRecordDict  
    print "channel info (len:%s) | channel outid (len:%s) | owner (len:%s) | singer withdraw (len:%s)" % (len(channelInfoDict), len(channelOutIDDict), len(ownerDict), len(singerWithdrawRecordDict))  
    for owner in ownerDict:  
        ownerChannelIDList = ownerDict\[owner\]  
        \#表演者结算  
        singerSum = 0  
        \#平台结算  
        channelSum = 0  
        \#表演者消费  
        sumSingerConsume = 0  
        \#表演者守护  
        sumSingerGuardIncom = 0  
        \#表演者非结算平台收入  
        sumSingerNoCorpIncom = 0  
        \#表演者上次结余  
        sumSingerYestodayCur = 0  
        \#表演者结算差值  
        sumSingerSub = 0  
        \#平台上次结余  
        sumChannelYestodayCur = 0  
        channelList = \[\]  
        NoSignSingerList = \[\]  
        channelSingerWithdrawRecordList = \[\]  
        channelList.append(\["平台内部ID", "平台外部ID", "表演者结算人民币", "平台结算人民币"\])  
        excelHandler = xlwt.Workbook(encoding="gbk")  
        for channelID in ownerChannelIDList:  
            print "channel(%s) in owner(%s)" % (channelID, owner)  
            if channelOutIDDict.has\_key(channelID):  
                outID = channelOutIDDict\[channelID\]  
            else:  
                outID = channelID  
            if not channelInfoDict.has\_key(channelID):  
                WriteExcelDataOneSheet(excelHandler, str(outID)+"平台明细", \[\[\]\])  
                continue  
            channelData = channelInfoDict\[channelID\]  
            Data2Number(channelData)  
            WriteExcelDataOneSheet(excelHandler, str(outID)+"平台明细", channelData)  
            \#获取平台结算人民币和表演者结算人民币  
            singerGold = channelData\[-3\]\[2\]  
            channelGold = channelData\[-1\]\[1\]  
            singerSum += singerGold  
            channelSum += channelGold  
            \#获取表演者消费  
            singerConsume = channelData\[-3\]\[8\]  
            sumSingerConsume += singerConsume  
            \#获取表演者守护  
            singerGuardIncom = channelData\[-3\]\[7\]  
            sumSingerGuardIncom += singerGuardIncom  
            \#获取表演者非结算平台收入  
            singerNoCorpIncom = channelData\[-3\]\[9\]  
            sumSingerNoCorpIncom += singerNoCorpIncom  
            \#获取表演者上次结余  
            singerYestodayCur = channelData\[-3\]\[10\]  
            sumSingerYestodayCur += singerYestodayCur  
            \#获取表演者结算差值  
            singerSub = channelData\[-3\]\[6\]  
            sumSingerSub += singerSub  
            \#获取平台上次结余  
            channelYestodayCur = channelData\[-1\]\[2\]  
            sumChannelYestodayCur += channelYestodayCur  
            \#加入相应的结算列表  
            channelList.append(\[channelID, outID, singerGold, channelGold\])  
            for NoSignSingerInfo in LoadData.channelNoSignSingerInfoDict\[channelID\]:  
                NoSignSingerList.append(NoSignSingerInfo)  
            if singerWithdrawRecordDict.has\_key(channelID):  
                channelSingerWithdrawRecordList += singerWithdrawRecordDict\[channelID\]  
        channelList.append(\["人民币合计", "", singerSum, channelSum\])  
        channelList.append(\["劳务费合计(RMB)", "", singerSum/200, channelSum/200\])  
        WriteExcelDataOneSheet(excelHandler, "平台结算汇总", channelList)  
  
  
        Data2Number(NoSignSingerList)  
         ownerGold = \[\]  
        ownerGold.append(\["公司化主播结算", singerSum/200\])  
        ownerGold.append(\["公司化平台结算", channelSum/200\])  
        bonus = singerSum/0.4\*0.05  
        ownerGold.append(\["公司化平台补贴", bonus/200\])  
        ownerGold.append(\["公司化平台激励", 0\])  
        ownerGold.append(\["合计", (singerSum+channelSum+bonus)/200\])  
        ownerGold.append(\[\])  
  
        sumNoSignSingerIncom = 0  
        sumNoSignSingerGuardIncom = 0  
        ownerGold.append(\["平台非签约表演者", "主播ID", "主播收入", "表演者守护"\])  
        for NoSignSingerInfo in NoSignSingerList:  
            sumNoSignSingerIncom += NoSignSingerInfo\[9\]  
            sumNoSignSingerGuardIncom += NoSignSingerInfo\[13\]  
            ownerGold.append(\[NoSignSingerInfo\[2\], NoSignSingerInfo\[7\], NoSignSingerInfo\[9\], NoSignSingerInfo\[13\]\])  
        ownerGold.append(\["合计人民币", "", sumNoSignSingerIncom, sumNoSignSingerGuardIncom\])  
        ownerGold.append(\["合计RMB", "", sumNoSignSingerIncom/200, sumNoSignSingerGuardIncom/200\])  
        ownerGold.append(\[\])  
  
        ownerGold.append(\["表演者消费", sumSingerConsume/200\])  
        ownerGold.append(\["守护反推1:4", (sumSingerGuardIncom/200)/0.3\*0.1\])  
        ownerGold.append(\["表演者非结算平台收入", sumSingerNoCorpIncom/200\])  
        ownerGold.append(\["表演者上次结余", sumSingerYestodayCur/200\])  
        ownerGold.append(\["表演者结算差值", sumSingerSub/200\])  
        ownerGold.append(\["平台上次结余", sumChannelYestodayCur/200\])  
        ownerGold.append(\["未结算表演者收入", sumNoSignSingerIncom/200\])  
        ownerGold.append(\["未结算表演者守护", sumNoSignSingerGuardIncom/200\])  
        ownerGold.append(\[\])  
  
  
        formula = ( channelSum/200 - sumChannelYestodayCur/200 )/0.1\*0.4 + sumSingerNoCorpIncom/200  
        singer = singerSum/200 + sumSingerConsume/200 + (sumSingerGuardIncom/200)/0.3\*0.1 - sumSingerYestodayCur/200 + sumNoSignSingerIncom/200 + sumNoSignSingerGuardIncom/200  
        deviation = float(formula - singer)  
        ownerGold.append(\["公式", formula\])  
        ownerGold.append(\["主播", singer\])  
        ownerGold.append(\["差值", deviation\])  
        ownerGold.append(\[\])  
  
  
        ownerGold += channelSingerWithdrawRecordList  
          
        WriteExcelDataOneSheet(excelHandler, "汇总", ownerGold)  
        if channelOwnerName.has\_key(str(owner)):  
            ownerName = channelOwnerName\[str(owner)\] + "\_" + str(owner)  
        else:  
            ownerName = str(owner)  
        fileName = curDir + ownerName + "\_" + str(timeSettle.tm\_mon) + "月" + ".xls"  
        print fileName  
        excelHandler.save(fileName)  
    print "====== make excel complete ======"  
    runEndTime = time.time()  
    print runEndTime-runStartTime  
  
  
MakeExcel()