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()