python脚本实例

扫码查看
cat DBConnector.py#!/usr/bin/env python# -*- coding: utf-8 -*-# haoren @ 2017-08-21 21:33:05import mysql.connectorfrom logger import loggingclass 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 Truecat DBPool.py#!/usr/bin/env python# -*- coding: utf-8 -*-# haoren @ 2017-08-21 21:33:05import copyfrom DBConnector import DBConnectordef 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)            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 Truecat logger.py#!/usr/bin/env python# -*- coding: utf-8 -*-# haoren @ 2017-08-22 21:55:42import 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 argvfrom logger import loggingfrom DBPool import DBPoolimport timeimport datetimecfgmap = {                                     #定义大字典,字典进行嵌套    "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    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 TIME0 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 = 9channelCorpIncomIdx = 3#非结算平台收入singerNoCorpIncomIdx = 10#表演者消费singerConsumeIdx = 12#表演者守护singerGuardIncomIdx = 13#平台守护channelGuardIncomIdx = 4#表演者的结算人民币singerCorpSettleIdx = 8#表演者结算差值singerSubIdx = 17#表演者上次结余singerYestodayCurIdx = 14#表演者外部IDsingerOutIDIdx = 7#平台收入数据表coinTable = ""goldTable = ""def GetListOneCol(t, i):                    #定义函数    if len(t)        return False    for row in t:        if len(row)            return False        listTemp.append(row[i])    return listTempdef 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 valuedef 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 retStrdef 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    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            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                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 infoTempdef 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 destStrdef 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 TIME1;" % (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        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    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]] = valuedef 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:37from sys import argvfrom logger import loggingimport osimport timeimport xlwtimport LoadDataimport 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 Truedef 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 datadef 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-runStartTimeMakeExcel()
11-10 12:33
查看更多