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()
阅读(1411) | 评论(0) | 转发(0) |
