cat MakeExcel.py
#!/usr/bin/env python
# -*- coding: gbk -*-
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 = {
"69299" : "张三网络信息咨询有限公司",
"5225" : "李四文化传播有限公司",
"93338" : "安徽王五文化传媒有限公司",
"5180820" : "河南兄弟文化传媒有限公司",
"9865" : "上海广告有限公司",
}
def mkdir(path):
# 去除首位空格
path=path.strip()
# 去除尾部 \ 符号
path=path.rstrip("\\")
# 判断路径是否存在
# 存在 True
# 不存在 False
isExists=os.path.exists(path)
# 判断结果
if not isExists:
# 如果不存在则创建目录
os.makedirs(path)
print path + " 创建成功"
return True
else:
# 如果目录存在则不创建，并提示目录已存在
print path + " 目录已存在"
return False
def WriteExcelDataOneSheet(excelHandler, name, data):
sheet = excelHandler.add_sheet(name, True)
tallStyle = xlwt.easyxf("font: height 220;")
colorStyle = xlwt.easyxf("font: height 220, color-index custom_colour, bold on; pattern: pattern solid, fore_colour custom_bg_colour;")
cMax = 0
r = 0
for row in data:
#设置行高
#style = tallStyle
#sheet.row(r).set_style(style)
c = 0
cIdx = 0
for col in row:
try:
cIdx = c
style = tallStyle
if type(row[0])==list or type(row[0])==tuple:
cIdx = c - 1
if c==0:
c += 1
continue
if cIdx in row[0]:
style = colorStyle
elif type(row[0])==bool:
cIdx = c - 1
if c==0:
c += 1
continue
if row[0]==True:
style = colorStyle
sheet.write(r, cIdx, row[c], style)
except:
print r, cIdx, row[c]
c += 1
r += 1
cIdx += 1
cMax = max(cMax, cIdx)
#设置列宽
for cIdx in range(0, cMax):
sheet.col(cIdx).width = 4400
return True
def Data2Number(data):
r = 0
for row in data:
c = 0
for col in row:
try:
if type(row[c])==bool:
raise ValueError
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:
try:
timeSettle = time.strptime(argv[1], '%Y%m%d')
except Exception, e:
print "Invalid date"
print e.args
exit()
else:
print "Please enter the date."
exit()
print "=============================="
timeStr = time.strftime("%Y%m%d", timeSettle)
print timeStr
print "=============================="
runStartTime = time.time()
LoadData.LoadData(timeSettle)
print "====== make excel start ======"
curDir = os.getcwd() + "/month/gongsi/"
mkdir(curDir)
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")
xlwt.add_palette_colour("custom_colour", 0x21)
xlwt.add_palette_colour("custom_bg_colour", 0x22)
excelHandler.set_colour_RGB(0x21, 156, 0, 6)
excelHandler.set_colour_RGB(0x22, 255, 199, 206)
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][LoadData.sumSingerCorpSettleIdx]
channelGold = channelData[-1][LoadData.channelCorpSettleIdx]
singerSum += singerGold
channelSum += channelGold
#获取演员消费
singerConsume = channelData[-3][LoadData.sumSingerConsumeIdx]
sumSingerConsume += singerConsume
#获取演员守护
singerGuardIncom = channelData[-3][LoadData.sumSingerGuardIncomIdx]
sumSingerGuardIncom += singerGuardIncom
#获取演员非结算平台收入
singerNoCorpIncom = channelData[-3][LoadData.sumSingerNoCorpIncomIdx]
sumSingerNoCorpIncom += singerNoCorpIncom
#获取演员上次结余
singerYestodayCur = channelData[-3][LoadData.sumSingerYestodayCurIdx]
sumSingerYestodayCur += singerYestodayCur
#获取演员结算差值
singerSub = channelData[-3][LoadData.sumSingerSubIdx]
sumSingerSub += singerSub
#获取平台上次结余
channelYestodayCur = channelData[-1][LoadData.channelYestodayCurIdx]
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([True, "劳务费合计(RMB)", "", singerSum/200, channelSum/200])
WriteExcelDataOneSheet(excelHandler, "平台结算汇总", channelList)
Data2Number(NoSignSingerList)
ownerGold = []
ownerGold.append(["项目", "结算金额(RMB)"])
ownerGold.append(["公司化歌唱家结算", singerSum/200])
ownerGold.append(["公司化平台结算", channelSum/200])
bonus = singerSum/0.4*0.05
ownerGold.append(["公司化平台补贴", bonus/200])
ownerGold.append(["公司化平台激励", 0])
ownerGold.append([True, "合计", (singerSum+channelSum+bonus)/200])
ownerGold.append([])
sumNoSignSingerIncom = 0
sumNoSignSingerGuardIncom = 0
ownerGold.append(["平台非签约演员", "歌唱家ID", "歌唱家收入", "演员守护"])
for NoSignSingerInfo in NoSignSingerList:
sumNoSignSingerIncom += NoSignSingerInfo[LoadData.mSingerCorpIncomIdx]
sumNoSignSingerGuardIncom += NoSignSingerInfo[LoadData.mSingerGuardIncomIdx]
ownerGold.append([NoSignSingerInfo[LoadData.channelOutIDIdx], NoSignSingerInfo[LoadData.singerOutIDIdx], NoSignSingerInfo[LoadData.mSingerCorpIncomIdx], NoSignSingerInfo[LoadData.mSingerGuardIncomIdx]])
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([True, "差值", deviation])
ownerGold.append([])
ownerGold += channelSingerWithdrawRecordList
moneyList = LoadData.GetListOneCol(channelSingerWithdrawRecordList, 1)
if type(moneyList)==list and len(moneyList)>0:
del moneyList[0]
sumMoney = sum(moneyList)
ownerGold.append([[1], "提现合计", sumMoney])
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()
cat config.py
#!/usr/bin/env python
# -*- coding: utf-8 -*-
# haoren
import ConfigParser
Cfg = ConfigParser.ConfigParser()
def MakeList2Dict(t):
d = {}
if (type(t)==list or type(t)==tuple):
for row in t:
if (type(row)==list or type(row)==tuple) and len(t)>0:
d[str(row[0])] = str(row[1])
return d
def ReadConfig(fileName):
Cfg.read(fileName)
DBList = Cfg.sections()
cfgMap = {}
for db in DBList:
cfgMap[db] = {}
listTemp = Cfg.items(db)
cfgMap[db] = MakeList2Dict(listTemp)
print cfgMap
return cfgMap
cat logger.py
#!/usr/bin/env python
# -*- coding: utf-8 -*-
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 DBPool.py
#!/usr/bin/env python
# -*- coding: utf-8 -*-
import copy
#单连接的数据库类
from DBConnector import DBConnector
#支持多连接的数据库连接类
#from DBTT 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 DBConnector.py
#!/usr/bin/env python
# -*- coding: utf-8 -*-
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 DBTT.py
#!/usr/bin/env python
# -*- coding: utf-8 -*-
import mysql.connector.pooling
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
cfg = {}
cfg["host"] = self._dbHost
cfg["port"] = self._dbPort
cfg["user"] = self._dbUser
cfg["password"] = self._dbPass
cfg["database"] = self._dbName
self._dbCfg = cfg
def connect(self):
try:
self.pool = mysql.connector.pooling.MySQLConnectionPool(
pool_name="myPool",
pool_size=4,
#pool_reset_session=True,
**self._dbCfg)
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, args=None, commit=False):
try:
conn = self.pool.get_connection()
cursor = conn.cursor()
if args:
cursor.execute(sql, args)
else:
cursor.execute(sql)
if commit==True:
results = conn.commit()
else:
results = cursor.fetchall()
except:
results = False
finally:
self.close(conn, cursor)
return results
def execSql(self, sql, args=None, commit=False):
results = self.execSqlStep(sql, args, commit)
if results==False:
if self.connect()==False:
return False
else:
results = self.execSqlStep(sql)
return results
def close(self, conn, cursor):
try:
cursor.close()
conn.close()
except:
return False
else:
return True
cat LoadData.py
#!/usr/bin/env python
# -*- coding: gbk -*-
#from sys import argv
from logger import logging
from DBPool import DBPool
import time
import datetime
import config
import threading
"""
cfgMap = {
"EIMDB" : {
"dbHost" : "172.17.1050.234",
"dbPort" : 3306,
"dbUser" : "root",
"dbPass" : "123456",
"dbName" : "IMDB"
},
"DGMDB" : {
"dbHost" : "172.17.1050.234",
"dbPort" : 3306,
"dbUser" : "root",
"dbPass" : "123456",
"dbName" : "GMDB"
},
"JIESUANDB" : {
"dbHost" : "172.17.1050.234",
"dbPort" : 3306,
"dbUser" : "root",
"dbPass" : "123456",
"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):
sql = "select CHANNELID from SPECIALCHANNEL group by CHANNELID;"
return db.execSql(dbKey, sql)
#平台外部ID列表
def MakeChannelOutIDList(dbKey):
sql = "select OLDID,NEWID from CHANNELIDCHANGELOG;"
return db.execSql(dbKey, sql)
#演员外部ID列表
def MakeSingerOutIDList(dbKey):
sql = "select INNERID,OUTERID from IMIDCHANGELOG;"
return db.execSql(dbKey, sql)
#公司化老板设置平台列表
def MakeChannelOwnerList(dbKey):
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)
#演员收入详细信息
#结算平台收入
mSingerCorpIncomIdx = 9
mChannelCorpIncomIdx = 3
#非结算平台收入
mSingerNoCorpIncomIdx = 10
#演员消费
mSingerConsumeIdx = 12
#演员守护
mSingerGuardIncomIdx = 13
#平台守护
mChannelGuardIncomIdx = 4
#演员的结算人民币
mSingerCorpSettleIdx = 8
#演员结算差值
mSingerSubIdx = 17
#演员上次结余
mSingerYestodayCurIdx = 14
#演员外部ID
singerOutIDIdx = 7
#平台外部ID
channelOutIDIdx = 2
#平台收入数据表
coinTable = ""
goldTable = ""
def GetListOneCol(t, i):
if len(t)<=0:
return False
listTemp = []
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 MakeNameStr(value, defaultStr=""):
srcStr = MakeNone2Default(value, defaultStr)
if srcStr=="":
return srcStr
try:
strTemp = srcStr.encode('unicode-escape').decode('string_escape')
destStr = strTemp.decode('gbk')
except Exception as e:
destStr = srcStr
return destStr
def GetLastMonthDateTimeWithTime(timeTemp):
lastMonth = timeTemp.tm_mon - 1
lastYear = timeTemp.tm_year
if lastMonth==0:
lastMonth = 12
lastYear -= 1
return datetime.datetime(month=lastMonth, year=lastYear, day=timeTemp.tm_mday)
def MakeStrLastMonth(srcStr):
firstStr = srcStr[0:-6]
monthStr = srcStr[-6:]
timeTemp = time.strptime(monthStr, '%Y%m')
retTime = GetLastMonthDateTimeWithTime(timeTemp)
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 = MakeNameStr(singerNickNameTemp[0][0])
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))
return list(tRet)
#演员提现记录
def MakeSingerWithdrawRecoed(startTime, endTime, singerID, channelID):
time1 = 0
time2 = startTime
timeTemp = time.localtime(time2)
retTime = GetLastMonthDateTimeWithTime(timeTemp)
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)
row[3] = MakeNameStr(row[3])
row[6] = MakeNameStr(row[6])
row[7] = MakeNameStr(row[7])
row[8] = MakeNameStr(row[8])
row[9] = MakeNameStr(row[9])
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)
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[mChannelCorpIncomIdx]
sumSingerCorpIncom += infoTemp[mSingerCorpIncomIdx]
sumSingerCorpSettle += infoTemp[mSingerCorpSettleIdx]
sumSingerSub += infoTemp[mSingerSubIdx]
sumSingerGuardIncom += infoTemp[mSingerGuardIncomIdx]
sumChannelGuardIncom += infoTemp[mChannelGuardIncomIdx]
sumSingerConsume += infoTemp[mSingerConsumeIdx]
sumSingerNoCorpIncom += infoTemp[mSingerNoCorpIncomIdx]
sumSingerYestodayCur += infoTemp[mSingerYestodayCurIdx]
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[mChannelCorpIncomIdx]
sumSingerCorpIncom += infoTemp[mSingerCorpIncomIdx]
sumSingerGuardIncom += infoTemp[mSingerGuardIncomIdx]
sumChannelGuardIncom += infoTemp[mChannelGuardIncomIdx]
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
#签约演员结算总计
sumSingerCorpSettleIdx = 2
#签约演员结算差值总计
sumSingerSubIdx = 6
#演员守护收入总计
sumSingerGuardIncomIdx = 7
#演员消费总计
sumSingerConsumeIdx = 8
#演员其他平台收入总计
sumSingerNoCorpIncomIdx = 9
#演员上月结余
sumSingerYestodayCurIdx = 10
#平台结算人民币
channelCorpSettleIdx = 1
#平台上次结余
channelYestodayCurIdx = 2
#每平台
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])
"""
#多线程方法
#注意:多线程方法要更换数据库连接类为支持多连接
for i in xrange(4):
t = myThread((startTime, endTime), i)
t.setDaemon(True)
t.start()
threads.append(t)
for t in threads:
t.join()
del threads[:]
"""
threadLock = threading.Lock()
threads = []
def GetTaskInListWithLock(taskList):
threadLock.acquire()
if len(taskList)>0:
task = taskList.pop(0)
else:
task = None
threadLock.release()
return task
def runTask(args):
task = GetTaskInListWithLock(channelCorpList)
while task<>None:
if not ( (type(task)==list or type(task)==tuple) and len(task)>0 ):
task = GetTaskInListWithLock(channelCorpList)
continue
channelID = task[0]
MergerSingerList(args[0], args[1], channelID)
task = GetTaskInListWithLock(channelCorpList)
class myThread (threading.Thread):
"线程类 继承父类threading.Thread"
def __init__(self, args, threadID):
threading.Thread.__init__(self)
self.args = args
self.threadID = threadID
def run(self):
print "Starting thread_%s" % self.threadID
runTask(self.args)
print "Exiting thread_%s" % self.threadID
channelCorpList = []
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, channelCorpList
cfgMap = config.ReadConfig("jiesuan.cfg")
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")
MakeList2Dict(channelOutIDList, channelOutIDDict)
singerOutIDList = MakeSingerOutIDList("IMDB")
MakeList2Dict(singerOutIDList, singerOutIDDict)
channelOwnerList = MakeChannelOwnerList("IMDB")
MakeChannelOwnerDict(channelOwnerList, channelOwnerDict)
channelCorpList = MakeChannelCorpList("IMDB", startTime, endTime)
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
#LoadData()
cat jiesuan.cfg
[EIMDB]
dbhost = 172.17.10.108
dbport = 3306
dbuser = haoren
dbpass = 123456
dbname = IMDB
[DGMDB]
dbhost = 172.17.10.105
dbport = 3306
dbuser = haoren
dbpass = 123456
dbname = GMDB
[JIESUANDB]
dbhost = 172.17.10.12
dbport = 3306
dbuser = haoren
dbpass = 123456
dbname = JIESUANDB
=========================================================================================================
cat run.py
# encoding=gbk
from app import app
import sys
if __name__ == '__main__':
app.run(host='0.0.0.0',port=1600,debug = True)
cat manage.py
from flask.ext.script import Manager, Server
from app import app
manager = Manager(app)
manager.add_command("server", Server())
@manager.shell
def make_shell_context():
return dict(app=app)
if __name__ == "__main__":
cat xinren.py
# -*- coding: gbk -*-
import os
import sys
import time
import traceback
from mconfig import *
from mloger import log
from common import *
from execlparser import *
LIVE_SHOW_CONTINU_DAY = mmconfig.getint("XINRENFUCHI","show_continue_day") #结算期内的满足条件天数
LIVE_SHOW_CONTINU_TIME_H = mmconfig.getint("XINRENFUCHI","show_continue_time") #每天至少演唱时长
LIVE_SHOW_CONTINU_TIME_H_LAST_Month = mmconfig.getint("XINRENFUCHI","show_continue_time_last_m")#最后一个月发放欠款-演唱时长
LIVE_SHOW_CONTINU_DAY_LAST_Month = mmconfig.getint("XINRENFUCHI","show_continue_day_last_m") #最后一个月发放欠款-满足天数
REWARD_AMOUNT_ONE_DAY = mmconfig.getint("XINRENFUCHI","reward_amount") #一个有效天奖励金额
GIFT_INCOM = mmconfig.getint("XINRENFUCHI","gift_incom")
def XinRenEentrance(dudu_id_list):
log.info("Enter func XinRenEentrance")
zhubo_msg = ListTransToDictBy_DUDUID_xinrenfuchi(dudu_id_list)
log.debug("XinRenEentrance:zhubo_msg'slen:[%d]",len(zhubo_msg))
#log.info("zhubo_msg:[%s]",zhubo_msg)
#获取歌唱家的开播时间
GetSingerLaunchTime(zhubo_msg)
msg_to_execl = {} #需要转化为EXECL的数据
execl_data_yes = {}#有奖金的人员信息
execl_data_no = {}#无奖金的人员信息
sanyue_user = {}
tmp_all_user = {}
try:
#遍历所有的身份证号码分别进行处理
for key, value in zhubo_msg.items():
set_schedule_xinren()
dudu_id = int(key[0])
old_Arrears = value["Arrears"]#之前欠款金额
nick_name = value["nick_name"]#昵称
out_channel_id = value["out_channel_id"]
insideID = value["inside_dudu_id"]
first_Settlement = value["first_Settlement"]
Reward_amount = 0 #奖励金额
Reward_amount_actual = 0 #实际奖励金额
Arrears = 0 #欠款金额
giftincom = 0 #礼物收入
#开始&结束时间戳
start_timestamp = value["start_time"]
end_timestamp = value["end_time"]
add_timestamp = value["add_time"]
#2、根据内部ID在结算表中获取该歌唱家在时间段内的满足条件的天数
live_show_history_s = GetLiveShowHistoryBy_Time_Duration_InsiteID(
start_timestamp,end_timestamp,
LIVE_SHOW_CONTINU_TIME_H,insideID,False)
log.debug("删除相同一天的之前,演唱天数:dudu_id:[%d]---[%d]",dudu_id,len(live_show_history_s))
#3、去除同一天的数据
live_show_history = live_show_history_s
live_show_day = len(live_show_history)
log.debug("歌唱家:[%d] 满足条件天数:[%d]要求天数:[%d]",dudu_id,live_show_day,LIVE_SHOW_CONTINU_DAY)
#获取指定时间内的礼物收入
giftincom =GetSingerGiftIncomBy_TimeLlimt_InsideID(start_timestamp,end_timestamp,insideID)
log.debug("歌唱家:[%d]获得的总礼物收入:[%d],要求:[%d]",dudu_id,giftincom,GIFT_INCOM)
if live_show_day >=LIVE_SHOW_CONTINU_DAY:#满足演唱天数
if giftincom >=GIFT_INCOM:#嘟币满足条件
Reward_amount = live_show_day * REWARD_AMOUNT_ONE_DAY
else:
Reward_amount = 0
else:#不满足要求，奖金为0记录数据
Reward_amount = 0
#判断当前月份是该歌唱家开播的第几月份
current_timestamp = time.time()
month =GetSingerStartShowMonth(current_timestamp,add_timestamp)
log.debug("结算时间是歌唱家开播的第:[%d]月 first_Settlement:[%d]",month,first_Settlement)
#时间超多1小时的天数
live_show_history_1h = GetLiveShowHistoryBy_Time_Duration_InsiteID(
start_timestamp,end_timestamp,
LIVE_SHOW_CONTINU_TIME_H_LAST_Month,insideID,False)
duration_1H_day = len(live_show_history_1h)
log.debug("dudid:[%d]month show_continue_day 1H:[%d]",dudu_id,duration_1H_day)
#根据是不是初次结算，更新时长奖励
if first_Settlement:#初次结算
pass
else:#历史结算,时长奖励都是零
Reward_amount = 0
threed_moth_duration_1H_day = 0
#如果开播时间大于或者等于三个月，则判断出开播第三月，内每天演唱时长到1小时的天数
if month >=3:
#log.debug("uuid:[%d] add 3 month,get he 3_month's show day 1 H")
thred_month_timestamp_s = add_timestamp + (30*24*60*60*2)#开播第三个月时间 月初
thred_month_timestamp_e = add_timestamp + (30*24*60*60*3)#开播第三个月时间 月末
live_show_history_1h = GetLiveShowHistoryBy_Time_Duration_InsiteID(
thred_month_timestamp_s,thred_month_timestamp_e,
LIVE_SHOW_CONTINU_TIME_H_LAST_Month,insideID,False)
threed_moth_duration_1H_day = len(live_show_history_1h)
log.debug("dudu_id:[%d]threed_moth_duration_1H_day:[%d]",dudu_id,threed_moth_duration_1H_day)
#存储中间信息:身份证号、歌唱家名称、时长奖励、礼物收入总数、分成、满足条件天数、,开始参加活动时间、活动结算时间，欠款
tmp_dict ={}
tmp_dict["dudu_id"] = dudu_id
tmp_dict["nick_name"] = nick_name
tmp_dict["out_channel_id"] = out_channel_id
tmp_dict["Reward_amount"] = Reward_amount
tmp_dict["giftincom"] = giftincom
tmp_dict["live_show_day"] = live_show_day
tmp_dict["duration_1H_day"] = duration_1H_day
tmp_dict["start_timestamp"] = start_timestamp
tmp_dict["end_timestamp"] = end_timestamp
tmp_dict["old_Arrears"] = old_Arrears
tmp_dict["month"] = month
tmp_dict["add_timestamp"] = add_timestamp
tmp_dict["first_Settlement"] = first_Settlement
tmp_dict["threed_moth_duration_1H_day"] = threed_moth_duration_1H_day
if tmp_all_user.has_key(dudu_id):
tmp_all_user[dudu_id].append(tmp_dict)
else:
tmp_list = []
tmp_list.append(tmp_dict)
tmp_all_user[dudu_id] = tmp_list
#逐个判断
for dudu_id, zhubo_list in tmp_all_user.items():
log.debug("dudu_id:[%d] all_recode:[%s]",dudu_id,zhubo_list)
for tmp_zhubo in zhubo_list:
Reward_amount_actual = 0 #实际奖励金额
Arrears = 0 #欠款金额
if tmp_zhubo["first_Settlement"]:#初次结算
#本次结算-满三月-最后月满15天---->本次奖励全部发放
if tmp_zhubo["month"]>=3 and (tmp_zhubo["threed_moth_duration_1H_day"]>=LIVE_SHOW_CONTINU_DAY_LAST_Month):
Reward_amount_actual = tmp_zhubo["Reward_amount"]
Arrears = 0
else:#本次结算-没有满足三月，发60% 扣除 40%
Reward_amount_actual = float(str(tmp_zhubo["Reward_amount"])) * 0.6 #发放%60
Arrears = float(str(tmp_zhubo["Reward_amount"])) * 0.4 #扣除%40
else:#历史
#历史结算-满三月-最后月满15天---->欠款全部发放
if tmp_zhubo["month"]>=3:
if tmp_zhubo["threed_moth_duration_1H_day"]>=LIVE_SHOW_CONTINU_DAY_LAST_Month:
Reward_amount_actual = tmp_zhubo["old_Arrears"]
Arrears = 0
else:#满三月，但是第三月不足15天的，历史欠款，清零
Reward_amount_actual = 0
Arrears = 0
else:#历史结算--未满三月----->历史欠款继承
Reward_amount_actual = 0
Arrears = tmp_zhubo["old_Arrears"]
#整理信息存储用来生成表
tmp_list = []
tmp_list.append(tmp_zhubo["dudu_id"]) #身份证号
tmp_list.append(tmp_zhubo["nick_name"]) #歌唱家昵称
tmp_list.append(tmp_zhubo["out_channel_id"]) #外部平台ID
tmp_list.append(Reward_amount_actual) #时长奖励
tmp_list.append(tmp_zhubo["giftincom"]) #礼物总收入
ticheng = float(str(tmp_zhubo["giftincom"]))*0.4
tmp_list.append(ticheng) #礼物收入提成%40
tmp_list.append(tmp_zhubo["live_show_day"]) #满足条件天数(3H)
tmp_list.append(tmp_zhubo["duration_1H_day"]) #满足条件天数(1H)
tmp_list.append(time.strftime("%Y/%m/%d", time.localtime(tmp_zhubo["start_timestamp"]))) #开始参加活动时间
tmp_list.append(time.strftime("%Y/%m/%d", time.localtime(tmp_zhubo["end_timestamp"]))) #活动结算时间:本月的24号23:59:59
tmp_list.append(Arrears) #欠款
tmp_list.append(tmp_zhubo["month"]) #开播时长
tmp_list.append(tmp_zhubo["threed_moth_duration_1H_day"]) #第三月演唱满1小时天数
tmp_list.append(time.strftime("%Y/%m/%d", time.localtime(tmp_zhubo["add_timestamp"])))#开播日期
tmp_list.append(tmp_zhubo["first_Settlement"]) #初次结算
key1 = dudu_id
key2 = tmp_zhubo["start_timestamp"]
key3 = tmp_zhubo["end_timestamp"]
if Reward_amount_actual >0:
execl_data_yes[key1,key2,key3] = tmp_list #存储有奖金的歌唱家数据
else:
execl_data_no[key1,key2,key3] = tmp_list#存储无奖金的歌唱家数据
msg_to_execl["YES"] = execl_data_yes
msg_to_execl["NO"] = execl_data_no
except Exception as e:
exstr = traceback.format_exc()
log.error("GaoDiXinEentrance throw exception :[%s]",exstr)
#遍历处理好所有参加活动人员数据，生成EXECL文件，文件名称为 活动奖励报表.xlsx
log.info("creeate execl file!")
sheet_name = '新人扶持报表'
sheet_title = ['身份证号','歌唱家昵称','签约平台','时长奖励','歌唱家流水','歌唱家提成','满足条件天数(2H)','满足条件天数(1H)','结算开始时间','结算结束时间','欠款','开播时长(月)','第三月演唱天数(1H)','首次演唱日期','初次结算']
CreateSheetAndInsertData(sheet_title,sheet_name,msg_to_execl)
cat gaodixin.py
# -*- coding: gbk -*-
import os
import sys
import time
import xlrd
import xlwt
import traceback
from mconfig import *
from mloger import log
from common import *
from execlparser import *
LIVE_SHOW_CONTINU_TIME= mmconfig.getint("GAODIXIN","show_continue_time")
LIVE_SHOW_CONTINU_DAY = mmconfig.getint("GAODIXIN","show_continue_day")
GIFT_INCOM_L1 = mmconfig.getint("GAODIXIN","gift_incom_level_1")
GIFT_INCOM_L2 = mmconfig.getint("GAODIXIN","gift_incom_level_2")
GIFT_INCOM_L3 = mmconfig.getint("GAODIXIN","gift_incom_level_3")
REWARD_AMOUNT_L1 = mmconfig.getint("GAODIXIN","reward_amount_level_1")
REWARD_AMOUNT_L2 = mmconfig.getint("GAODIXIN","reward_amount_level_2")
REWARD_AMOUNT_L3 = mmconfig.getint("GAODIXIN","reward_amount_level_3")
def GaoDiXinEentrance(dudu_id_list):
global schedule_gaodixin
schedule_gaodixin = 0
log.info("Enter func GaoDiXinEentrance")
log.debug(dudu_id_list)
zhubo_msg = ListTransToDictBy_DUDUID(dudu_id_list)
log.debug("GaoDiXinEentrance:zhubo_msg'slen:[%d]",len(zhubo_msg))
#log.debug("zhubo_msg:[%s]",zhubo_msg)
msg_to_execl = {} #需要转化为EXECL的数据
execl_data_yes = {}#有奖金的人员信息
execl_data_no = {}#无奖金的人员信息
try:
#遍历所有需要处理的身份证号，从数据库获取结算需要的信息
for key, value in zhubo_msg.items():
set_schedule_gaodixin()
dudu_id = int(key[0])
Reward_amount = 0 #奖励金额
giftincom = 0#礼物收入
nick_name = value["nick_name"]#昵称
out_channel_id = value["out_channel_id"]
insideID = value["inside_dudu_id"]
#结束开始时间戳
start_timestamp = value["start_time"]
end_timestamp = value["end_time"]
#1、根据身份证ID获取内部ID
#insideID = GetInsideIDBy_DUDUID(dudu_id)
#2、根据内部ID在结算表中获取该歌唱家在时间段内的满足条件的天数
live_show_history_s = GetLiveShowHistoryBy_Time_Duration_InsiteID(
start_timestamp,end_timestamp,
LIVE_SHOW_CONTINU_TIME,insideID,False)
#3、去除同一天的数据
# live_show_history = DeleteSameDay(live_show_history_s)
live_show_history = live_show_history_s
live_show_day = len(live_show_history)
log.debug("歌唱家:[%d] 满足条件天数:[%d]要求天数:[%d]",dudu_id,live_show_day,LIVE_SHOW_CONTINU_DAY)
if live_show_day >=LIVE_SHOW_CONTINU_DAY:#满足演唱天数
#获取指定时间内的礼物收入
# time_local = time.localtime(end_timestamp)
# #转换成新的时间格式(201605)
# year_month = time.strftime("%Y%m",time_local)
giftincom =GetSingerGiftIncomBy_TimeLlimt_InsideID(start_timestamp,end_timestamp,insideID)
log.debug("歌唱家:[%d]获得的总礼物收入:[%d]",dudu_id,giftincom)
if giftincom
Reward_amount = 0
elif giftincom >=GIFT_INCOM_L1 and giftincom
Reward_amount = REWARD_AMOUNT_L1
elif giftincom >=GIFT_INCOM_L2 and giftincom
Reward_amount = REWARD_AMOUNT_L2
elif giftincom >=GIFT_INCOM_L3:
Reward_amount = REWARD_AMOUNT_L3
else:#不满足要求，奖金为0记录数据
Reward_amount = 0
#记录该歌唱家的相关消息：身份证号、昵称、礼物收入总数（嘟币）、满足条件天数、奖励金额,开始时间、结束时间
tmp_list = []
tmp_list.append(dudu_id)
tmp_list.append(nick_name) #歌唱家昵称
tmp_list.append(out_channel_id) #外部平台ID
tmp_list.append(giftincom)
tmp_list.append(live_show_day)
tmp_list.append(Reward_amount)
tmp_list.append(time.strftime("%Y/%m/%d", time.localtime(start_timestamp)))
tmp_list.append(time.strftime("%Y/%m/%d", time.localtime(end_timestamp)))
if Reward_amount >0:
execl_data_yes[key] = tmp_list #存储有奖金的歌唱家数据
else:
execl_data_no[key] = tmp_list#存储无奖金的歌唱家数据
msg_to_execl["YES"] = execl_data_yes
msg_to_execl["NO"] = execl_data_no
except Exception,e:
exstr = traceback.format_exc()
log.error("GaoDiXinEentrance throw exception :[%s]",exstr)
log.info("creeate execl file!")
sheet_name = '高底薪扶持报表'
sheet_title = ['身份证号','歌唱家昵称','签约平台','歌唱家流水','满足条件天数','奖励金额','开始时间','结束时间']
CreateSheetAndInsertData(sheet_title,sheet_name,msg_to_execl)
cat xiaomifeng.py
# -*- coding: gbk -*-
import os
import sys
import time
import traceback
from mconfig import cf
from mloger import log
from common import *
from execlparser import *
SHEET_NAME_XIAOMIFENG = "活动报表"
LIVE_SHOW_CONTINU_TIME_L1 = mmconfig.getint("XIAOMIFENG","continue_time_level_1") #每天奖励演唱时长1
LIVE_SHOW_CONTINU_TIME_L2 = mmconfig.getint("XIAOMIFENG","continue_time_level_2") #每天奖励演唱时长2
REWARD_AMOUNT_L1 = mmconfig.getint("XIAOMIFENG","reward_amount_level_1") #奖励金额每天---对于演唱时长1
REWARD_AMOUNT_L2 = mmconfig.getint("XIAOMIFENG","reward_amount_level_2") #奖励金额媒体---对应演唱时长2
LIVE_SHOW_CONTINU_DAY =mmconfig.getint("XIAOMIFENG","continue_day") #每个活动周期内的最少达标天数
LIVE_SHOW_GIFT_INCOM =mmconfig.getint("XIAOMIFENG","gift_incom") #每个活动周期内的最少达标收入
def XiaoMiFengEentrance(dudu_id_list):
log.info("Enter func XiaoMiFengEentrance")
# log.info("dudu_id_list:[%s]",dudu_id_list)
zhubo_msg = ListTransToDictBy_DUDUID_xiaomifeng(dudu_id_list)
log.debug("XiaoMiFengEentrance:zhubo_msg'slen:[%d]",len(zhubo_msg))
log.info("zhubo_msg:[%s]",zhubo_msg)
msg_to_execl = {} #需要转化为EXECL的数据
execl_data_yes = {} #有奖金的人员信息
execl_data_no = {} #无奖金的人员信息
try:
#遍历所有需要处理的身份证号，从数据库获取结算需要的信息
for key, value in zhubo_msg.items():
set_schedule_xiaomifeng()
dudu_id = int(key[0])
Reward_amount = 0 #奖励金额
giftincom = 0#礼物收入
front_giftincom = 0#上个月礼物收入
nick_name = value["nick_name"]#昵称
out_channel_id = value["out_channel_id"]
insideID = value["inside_dudu_id"]
#结束开始时间戳
start_timestamp = value["start_time"]
end_timestamp = value["end_time"]
#1、根据身份证ID获取内部ID
#insideID = GetInsideIDBy_DUDUID(dudu_id)
#2、根据内部ID，在结算数据库中获取在时间段内满足条件的演唱数据
live_show_history_s = GetLiveShowHistoryBy_Time_Duration_InsiteID(
start_timestamp,end_timestamp,
LIVE_SHOW_CONTINU_TIME_L1,insideID,True)
#获取指定时间内的礼物收入
giftincom =GetSingerGiftIncomBy_TimeLlimt_InsideID(start_timestamp,end_timestamp,insideID)
log.debug("歌唱家:[%d]本次阶段礼物收入:[%f]活动要求最低收入:[%d]",dudu_id,giftincom,LIVE_SHOW_GIFT_INCOM)
#获取上一个月时间段的礼物收入数据
f_s,f_e=getFrontStage_timeStamp(start_timestamp,end_timestamp)
front_giftincom = GetSingerGiftIncomBy_TimeLlimt_InsideID(f_s,f_e,insideID)
log.debug("歌唱家:[%d]上一阶段礼物收入:[%f]",dudu_id,front_giftincom)
#去除同一天数据
# live_show_history = DeleteSameDay(live_show_history_s)
live_show_history =live_show_history_s
live_show_day = len(live_show_history)
H3 =0 #满三小时天数
H4 =0 #满四小时天数
#计算满三小时和满4小时的各自天数
log.debug("live_show_history:[%s]",live_show_history)
for tmp_t in live_show_history:
if tmp_t["DURATION"] >=LIVE_SHOW_CONTINU_TIME_L2:
H4+=1
elif tmp_t["DURATION"] >=LIVE_SHOW_CONTINU_TIME_L1:
H3+=1
log.debug("歌唱家:[%d]满足条件演唱天数:[%d]活动要求天数:[%d]",dudu_id,live_show_day,LIVE_SHOW_CONTINU_DAY)
if live_show_day >=LIVE_SHOW_CONTINU_DAY:#演唱天数达标
if giftincom >= LIVE_SHOW_GIFT_INCOM:
# #计算满三小时和满4小时的各自天数
# log.debug("live_show_history:[%s]",live_show_history)
# for tmp_t in live_show_history:
# if tmp_t["DURATION"] >=LIVE_SHOW_CONTINU_TIME_L2:
# H4+=1
# elif tmp_t["DURATION"] >=LIVE_SHOW_CONTINU_TIME_L1:
# H3+=1
#计算奖金
Reward_amount = H3*35 + H4*50
else:
Reward_amount = 0
else:#演唱天数不达标，奖金为0
Reward_amount = 0
#记录歌唱家相关信息，用来写入EXECL文件
#顺序：身份证号、歌唱家名称、时长奖励、礼物总收入、收入提成、满三小时天数、满四小时天数，参加活动开始时间,结束时间
tmp_list = []
tmp_list.append(dudu_id) #身份证号
tmp_list.append(nick_name) #歌唱家昵称
tmp_list.append(out_channel_id) #外部平台ID
tmp_list.append(Reward_amount) #时长奖励
tmp_list.append(H3) #满三小时天数
tmp_list.append(H4) #满四小时天数
tmp_list.append(giftincom) #本月流水
tixian_1 = float(str(giftincom))*0.4
tmp_list.append(tixian_1) #提现收入
tmp_list.append(front_giftincom) #上月流水
tixian_2 = float(str(front_giftincom))*0.4
tmp_list.append(tixian_2) #提现收入
if(int(tixian_2) == 0):
zengfu = 0
else:
zengfu = (tixian_1/tixian_2)-1
tmp_list.append( '%.2f'%zengfu) #增幅
maoli = float(str(giftincom))*0.45
tmp_list.append(maoli) #毛利润
jingli = float(str(giftincom))*0.15
tmp_list.append(jingli) #净利润
shiji_maoli = maoli-Reward_amount
tmp_list.append(shiji_maoli) #实际毛利
shiji_jingli = jingli-Reward_amount
tmp_list.append(shiji_jingli) #实际净利
tmp_list.append(time.strftime("%Y/%m/%d", time.localtime(start_timestamp))) #开始参加活动时间
tmp_list.append(time.strftime("%Y/%m/%d", time.localtime(end_timestamp))) #活动结算时间:本月的24号23:59:59
if Reward_amount >0:
execl_data_yes[key] = tmp_list #存储有奖金的歌唱家数据
else:
execl_data_no[key] = tmp_list#存储无奖金的歌唱家数据
msg_to_execl["YES"] = execl_data_yes
msg_to_execl["NO"] = execl_data_no
except Exception as e:
exstr = traceback.format_exc()
log.error("GaoDiXinEentrance throw exception :[%s]",exstr)
log.info("creeate execl file:[%s] type:[%s]",SHEET_NAME_XIAOMIFENG,type(SHEET_NAME_XIAOMIFENG))
sheet_title = ['身份证号','歌唱家昵称','签约平台','时长奖励','满三小时天数','满四小时天数','本月流水','提现收入','上月流水','提现收入',
'增幅','毛利润','净利润','实际毛利','实际净利','开始参加活动时间','活动结算时间']
CreateSheetAndInsertData(sheet_title,SHEET_NAME_XIAOMIFENG,msg_to_execl)
def getFrontStage_timeStamp(start_timestamp,end_timestamp):
timeArray_s= time.localtime(start_timestamp)
timeArray_e= time.localtime(end_timestamp)
today_datetime_s = datetime.datetime(timeArray_s.tm_year, timeArray_s.tm_mon-2,timeArray_s.tm_mday,0,0,0)
today_datetime_e = datetime.datetime(timeArray_e.tm_year, timeArray_e.tm_mon-2,timeArray_s.tm_mday,23,59,59)
ans_time_s = time.mktime(today_datetime_s.timetuple())
ans_time_e = time.mktime(today_datetime_e.timetuple())
return ans_time_s,ans_time_e
cat common.py
# -*- coding: gbk -*-
import os
import sys
import xlrd
import xlwt
import time
import datetime
import chardet
from decimal import *
from mloger import log
from mconfig import *
from mysqlpool import MySQLPoolManage,getDBPoolByDBName
#--------------------------------------
#把从EXECL中的数据list重新整存储成用身份证号为
#键值的字典，值是一个字典存储：
#dudu_id:身份证号
#nick_name:歌唱家昵称
#start_time:参加活动日志-(时间字符串)
#活动时间31天== 高底薪&新人扶持
#--------------------------------------
def ListTransToDictBy_DUDUID(list_line):
log.debug("Enter func ListTransToDictBy_DUDUID")
global_dict = {}
for tmp_line in list_line:
zhubo_msg = {}#字典的值为另一个字典，存储身份证号和歌唱家昵称，参加活动开始时间
key = int(tmp_line[u"身份证号"])#dudu号码
zhubo_msg["dudu_id"] = int(tmp_line[u"身份证号"])
# zhubo_msg["nick_name"] = tmp_line[u"歌唱家名称"]
dtime = xlrd.xldate.xldate_as_datetime(tmp_line[u"结算开始时间"], 0)
ans_time = time.mktime(dtime.timetuple())
zhubo_msg["start_time"] =ans_time
key2 = zhubo_msg["start_time"]
dtime = xlrd.xldate.xldate_as_datetime(tmp_line[u"结算结束时间"], 0)
#----定位到结束日期的23点59分59秒
timeArray = dtime.timetuple()
today_datetime = datetime.datetime(timeArray.tm_year, timeArray.tm_mon,timeArray.tm_mday,23,59,59)
ans_time = time.mktime(today_datetime.timetuple())
# ans_time = time.mktime(dtime.timetuple())
zhubo_msg["end_time"] =ans_time
key3 = zhubo_msg["end_time"]
inside_dudu_id = GetInsideIDBy_DUDUID(key)
zhubo_msg["inside_dudu_id"] = inside_dudu_id
zhubo_msg["in_channel_id"] = GetinChannelIDBy_dudinsideid(inside_dudu_id)
zhubo_msg["out_channel_id"] = GetOutChannelIDBy_InChanleID(zhubo_msg["in_channel_id"])
zhubo_msg["nick_name"] = GetSingerNickname(inside_dudu_id)
global_dict[key,key2,key3] = zhubo_msg#使用身份证号进行存储，方便后面使用
return global_dict
#------------------------------
#EXECL中的数据转换成字典存储，身份证号为键值
#存储：身份证号，歌唱家名称，参加活动开始时间，推导结束时间到本月的24号的晚间
def ListTransToDictBy_DUDUID_xiaomifeng(list_line):
log.debug("Enter func ListTransToDictBy_DUDUID_xiaomifeng")
global_dict = {}
for tmp_line in list_line:
zhubo_msg = {}#字典的值为另一个字典，存储身份证号和歌唱家昵称，参加活动开始时间
key = tmp_line[u"身份证号"]#dudu号码
zhubo_msg["dudu_id"] = tmp_line[u"身份证号"]
# zhubo_msg["nick_name"] = tmp_line[u"歌唱家名称"]
dtime = xlrd.xldate.xldate_as_datetime(tmp_line[u"结算开始时间"], 0)
ans_time = time.mktime(dtime.timetuple())
zhubo_msg["start_time"] =ans_time
key2 = zhubo_msg["start_time"]
dtime = xlrd.xldate.xldate_as_datetime(tmp_line[u"结算结束时间"], 0)
timeArray = dtime.timetuple()
today_datetime = datetime.datetime(timeArray.tm_year, timeArray.tm_mon,timeArray.tm_mday,23,59,59)
ans_time = time.mktime(today_datetime.timetuple())
# ans_time = time.mktime(dtime.timetuple())
zhubo_msg["end_time"] =ans_time
key3 = zhubo_msg["end_time"]
inside_dudu_id = GetInsideIDBy_DUDUID(key)
zhubo_msg["inside_dudu_id"] = inside_dudu_id
zhubo_msg["in_channel_id"] = GetinChannelIDBy_dudinsideid(inside_dudu_id)
zhubo_msg["out_channel_id"] = GetOutChannelIDBy_InChanleID(zhubo_msg["in_channel_id"])
zhubo_msg["nick_name"] = GetSingerNickname(inside_dudu_id)
#zhubo_msg["nick_name"] = tmp_line[u"昵称"]
global_dict[key,key2,key3] = zhubo_msg#使用身份证号进行存储，方便后面使用
return global_dict
#--------------------------------------
#把EXECL中的数据转化成字典
#键值：身份证号
#存储内容：身份证号、歌唱家名称、参加活动开始时间、欠款
#活动时间31天
#--------------------------------------
def ListTransToDictBy_DUDUID_xinrenfuchi(list_line):
log.debug("Enter func ListTransToDictBy_DUDUID")
global_dict = {}
for tmp_line in list_line:
zhubo_msg = {}#字典的值为另一个字典，存储身份证号和歌唱家昵称，参加活动开始时间
key = tmp_line[u"身份证号"] #dudu号码
zhubo_msg["dudu_id"] = tmp_line[u"身份证号"]
# zhubo_msg["nick_name"] = tmp_line[u"歌唱家名称"]
dtime = xlrd.xldate.xldate_as_datetime(tmp_line[u"结算开始时间"], 0)
ans_time = time.mktime(dtime.timetuple())
zhubo_msg["start_time"] =ans_time
key2 = zhubo_msg["start_time"]
log.debug("dudu_id:[%d] 开始结算时间:[%s]",
zhubo_msg["dudu_id"],
time.strftime("%Y-%m-%d", time.localtime(ans_time)))
dtime = xlrd.xldate.xldate_as_datetime(tmp_line[u"结算结束时间"], 0)
timeArray = dtime.timetuple()
today_datetime = datetime.datetime(timeArray.tm_year, timeArray.tm_mon,timeArray.tm_mday,23,59,59)
ans_time = time.mktime(today_datetime.timetuple())
# ans_time = time.mktime(dtime.timetuple())
zhubo_msg["end_time"] =ans_time
key3 = zhubo_msg["end_time"]
zhubo_msg["Arrears"] = tmp_line[u"欠款"] #历史欠款
zhubo_msg["first_Settlement"] = int(tmp_line[u"初次结算"]) #初次结算
inside_dudu_id = GetInsideIDBy_DUDUID(key)
zhubo_msg["inside_dudu_id"] = inside_dudu_id
zhubo_msg["in_channel_id"] = GetinChannelIDBy_dudinsideid(inside_dudu_id)
zhubo_msg["out_channel_id"] = GetOutChannelIDBy_InChanleID(zhubo_msg["in_channel_id"])
zhubo_msg["nick_name"] = GetSingerNickname(inside_dudu_id)
global_dict[key,key2,key3] = zhubo_msg #使用身份证号进行存储，方便后面使用
return global_dict
def GetinChannelIDBy_dudinsideid(dudu_id):
log.debug("Enter func GetinChannelIDBy_dudinsideid")
IMPOLL = getDBPoolByDBName("IMDB")
if IMPOLL == None:
log.error("getDBPoolByDBName return None")
return 0
in_channel_id = 0
# sql = "SELECT * from CHANNELSINGER WHERE SINGER=%d ORDER BY UPDATETIME DESC LIMIT 1;"%(int(dudu_id))
sql = "SELECT * from PERFORMERINFO WHERE PERFORMERID=%d;"%(int(dudu_id))
log.debug("sql:[%s]",sql)
res = IMPOLL.execute(sql)
if res:
for tmp_row in res:
in_channel_id = tmp_row[1]
else:
log.error("cant't get in_channel_id by dudu_id:[%d]",dudu_id)
log.debug("dudu_id[%d]----->in_channel_id:[%d]",dudu_id,in_channel_id)
return in_channel_id
#---------------------------------------
#根据身份证ID从数据获取对应的内部ID
#---------------------------------------
def GetInsideIDBy_DUDUID(dudu_id):
log.debug("Enter func GetInsideIDBy_DUDUID")
IMPOLL = getDBPoolByDBName("IMDB")
if IMPOLL == None:
log.error("getDBPoolByDBName return None")
return None
insideID = 0
sql = "SELECT * from IMIDCHANGELOG WHERE OUTERID=%d ORDER BY TIME DESC LIMIT 1;"%(int(dudu_id))
log.debug("sql:[%s]",sql)
res = IMPOLL.execute(sql)
if res:
for tmp_row in res:
insideID = tmp_row[0]
log.debug("outsideID[%d]--changed--->insideID:[%d]",dudu_id,insideID)
return int(insideID)
else:
log.debug("outsideID[%d]---no-changed-->insideID:[%d]",dudu_id,dudu_id)
return int(dudu_id)
#---------------------------------------
#根据内部channel ID 从数据获取对应的外部ID
#---------------------------------------
def GetOutChannelIDBy_InChanleID(in_channel_id):
log.debug("Enter func GetOutChannelIDBy_InChanleID")
IMPOLL = getDBPoolByDBName("IMDB")
if IMPOLL == None:
log.error("getDBPoolByDBName return None")
return None
out_channel_id = 0
sql = "SELECT * from CHANNELIDCHANGELOG WHERE OLDID=%d ORDER BY TIME DESC LIMIT 1;"%(in_channel_id)
log.debug("sql:[%s]",sql)
res = IMPOLL.execute(sql)
if res:
for tmp_row in res:
out_channel_id = tmp_row[1]
log.debug("outsidechannelID[%d]----->insidechannelID:[%d]",out_channel_id,in_channel_id)
return out_channel_id
else:
log.error("can't get out_channel_id by:[%d]",in_channel_id)
return out_channel_id
#-----------------------------------------
#时间转换成时间戳
#-----------------------------------------
def TransDateToTimesTamp(dt):
log.debug("Enter func TransDateToTimesTamp")
timeArray = time.strptime(dt, "%Y-%m-%d %H:%M:%S")
#转换成时间戳
timestamp = time.mktime(timeArray)
return timestamp
#Modify by Quanjie.Deng
#演唱记录表中存储的是一个人的所有的演唱的记录，包括同一天的，
#针对满足的时间表达的含义是一个人一天的演唱时间总和只要超过要求时间
#就可以
def GetLiveShowHistoryBy_Time_Duration_InsiteID(start_timestamp,end_timestamp,duration,insiteID,is_xiaomifeng = False):
log.debug("Enter func GetLiveShowHistoryBy_Time_Duration_InsiteID,insiteID:[%d]is_xiaomifeng:[%d]",insiteID,is_xiaomifeng)
GMDBPool = getDBPoolByDBName("GMDB")
if GMDBPool == None:
log.error("getDBPoolByDBName return None")
return return_msg
TABLE = 'PERFORMERSHOWTIMERECORD'+str(insiteID%10)#获得表名
sql = "SELECT * from %s WHERE PERFORMERID=%d and STARTTIME>=%d and ENDTIME<=%d ORDER BY STARTTIME;;"%(TABLE,insiteID,start_timestamp,end_timestamp)
log.debug("GetLiveShowHistoryBy_Time_Duration_InsiteID:SQL:[%s]",sql)
show_history_dict = {} #使用日期作为字典存储每一天的所有的演唱数据总和
return_msg = []
res = GMDBPool.execute(sql)
if res:#有演唱记录
in_channel_id = 0
for tmp_row in res:#满足条件的记录临时字典存储：CHANNELID:平台ID，PERFORMERID=表演者ID，STARTTIME开始时间，ENDTIME结束时间，DURATION：持续时间
in_channel_id = tmp_row[1]
t_duration = tmp_row[5]
t_start_timp = tmp_row[3]
t_end_timp = tmp_row[4]
timeArray_s = time.localtime(t_start_timp)
begin_date = time.strftime("%Y-%m-%d", timeArray_s)
#log.debug("start process data:[%s]",begin_date)
#分别记录在1--9:30之间的数据 和其他时间的数据tmp_show_data["one_nine_thirty"] tmp_show_data["other_time"]
tmp_show_data = {"one_nine_thirty":0,"other_time":0}
one_timestamp_data = datetime.datetime(timeArray_s.tm_year, timeArray_s.tm_mon,timeArray_s.tm_mday,1,0,0)#当天1点时间戳
one_timestamp = time.mktime(one_timestamp_data.timetuple())
nine_half_timestamp_data = datetime.datetime(timeArray_s.tm_year, timeArray_s.tm_mon,timeArray_s.tm_mday,9,30,59)#当天九点半时间戳
nine_half_timestamp = time.mktime(nine_half_timestamp_data.timetuple())
if t_start_timp>= one_timestamp and t_end_timp<= nine_half_timestamp:
tmp_show_data["one_nine_thirty"] = t_duration
else:
tmp_show_data["other_time"] = t_duration
if show_history_dict.has_key(begin_date):
show_history_dict[begin_date]["one_nine_thirty"] += tmp_show_data["one_nine_thirty"]
show_history_dict[begin_date]["other_time"] += tmp_show_data["other_time"]
else:
# log.debug("日期:[%s] 第一次进入,内容:[%s]",begin_date,tmp_show_data)
show_history_dict[begin_date] = tmp_show_data
#遍历该歌唱家每一天的演唱数据，对小蜜蜂的用户在1点到9:30之间的演唱，播再久还是1小时，不足1小时，照实记录
for key,values in show_history_dict.items():
if is_xiaomifeng:
if values["one_nine_thirty"] >= 3599:
values["one_nine_thirty"] = 3599
t_duration = values["one_nine_thirty"] + values["other_time"]
log.debug("data:[%s] show_time:[%d]by_duration:[%d]",key,t_duration,duration)
if t_duration>=duration:
data = {}
data["DURATION"] = t_duration
data["in_channel_id"] = in_channel_id
return_msg.append(data)
else:
log.debug("the:[%d] no LIVE-SHOW history!",insiteID)
return return_msg
#---------------------------------------------
#根据开始个结束时间获取在指定时间段内的演唱记录
#---------------------------------------------
# def GetLiveShowHistoryBy_Time_Duration_InsiteID(start_timestamp,end_timestamp,duration,insiteID):
# log.debug("Enter func GetLiveShowHistoryBy_Time_Duration_InsiteID")
# GMDBPool = getDBPoolByDBName("GMDB")
# return_msg = []
# if GMDBPool == None:
# log.error("getDBPoolByDBName return None")
# return return_msg
# TABLE = 'PERFORMERSHOWTIMERECORD'+str(insiteID%10)#获得表名
# sql = "SELECT * from %s WHERE PERFORMERID=%d AND DURATION >= %d and STARTTIME>=%d and ENDTIME<=%d;"%(TABLE,insiteID,duration,start_timestamp,end_timestamp)
# log.debug("GetLiveShowHistoryBy_Time_Duration_InsiteID:SQL:[%s]",sql)
# res = GMDBPool.execute(sql)
# if res:#有演唱记录
# for tmp_row in res:#满足条件的记录存储,字典存储：CHANNELID:平台ID，PERFORMERID=表演者ID，STARTTIME开始时间，ENDTIME结束时间，DURATION：持续时间
# data = {}
# data["CHANNELID"] = tmp_row[1]
# data["PERFORMERID"] = tmp_row[2]
# data["STARTTIME"] = tmp_row[3]
# data["ENDTIME"] = tmp_row[4]
# data["DURATION"] = tmp_row[5]
# return_msg.append(data)
# else:
# log.debug("the:[%d] no LIVE-SHOW history!",insiteID)
# return return_msg
#去除同一天的多条满足条件的演唱记录，并且保存最长演唱时长
def DeleteSameDay(history_list):
log.debug("Enter func DeleteSameDay")
tmp_dict = {}
i = 0
while i < len(history_list):
tmp_data = history_list[i]
end_timestamp = tmp_data["ENDTIME"]
time_local = time.localtime(end_timestamp)
#转换成新的时间格式(2016-05-05 20:28:54)
dt = time.strftime("%Y-%m-%d",time_local)
if tmp_dict.has_key(dt):#当天已经存在，删除,更新演唱时长为最长
max_duration_time = tmp_dict[dt]["DURATION"] if tmp_dict[dt]["DURATION"]>tmp_data["DURATION"] else tmp_data["DURATION"]
tmp_dict[dt]["DURATION"] = max_duration_time
history_list.pop(i)
i -=1
else:#当天没有记录保留，存储演唱时长
tmp_dict[dt] = tmp_data
i += 1
#重新声称新的history_list
new_list = []
for key,value in tmp_dict.items():
new_list.append(value)
return new_list
#获取歌唱家的礼物收入总数量数量----嘟币 根据月份以及身份证号
def GetSingerGiftIncomBy_Month_InsideID(month,insideID,start_timestamp,end_timestamp):
log.debug("Enter func GetSingerGiftIncomBy_Month_InsideID")
jiesuan_table = 'JIESUANTONGJI_ZONG_'+str(month)
JIESUANDBPool = getDBPoolByDBName("JIESUANDB")
gift_incom = 0
if JIESUANDBPool == None:
log.error("getDBPoolByDBName return None")
return gift_incom
sql = "SELECT SUM(CONSUMECOIN )/100 as sum_SINGERRECVGOLD from %s where SINGERID=%d and OPTIME>=%d and OPTIME <=%d;"%(jiesuan_table,insideID,start_timestamp,end_timestamp)
# sql = "SELECT SUM(SINGERRECVGOLD )/200 as sum_SINGERRECVGOLD from %s where SINGERID=%d and OPTIME>=%d and OPTIME <=%d;"%(jiesuan_table,insideID,start_timestamp,end_timestamp)
log.debug("sql:[%s]",sql)
res = JIESUANDBPool.execute(sql)
if res:
for tmp_row in res:
gift_incom = tmp_row[0] if tmp_row[0] else gift_incom
else:
pass
return gift_incom
#获取在指定范围内歌手的收入，直接从每月的统计表中查询
def GetSingerGiftIncomBy_TimeLlimt_InsideID(start_timestamp,end_timestamp,insideID):
log.debug("Enter func GetSingerGiftIncomBy_TimeLlimt_InsideID")
def get_table_list(start_timestamp,end_timestamp):
timeArray= time.localtime(end_timestamp)
end_date = time.strftime("%Y%m", timeArray)
moth_list = []
moth_list.append(end_date)
if timeArray.tm_mday > 25:
today_datetime = datetime.datetime(timeArray.tm_year, timeArray.tm_mon+1,1)
ss = today_datetime.strftime("%Y%m")
moth_list.append(ss)
#-----
timeArray= time.localtime(start_timestamp)
if(timeArray.tm_mday <25):
end_date = time.strftime("%Y%m", timeArray)
moth_list.append(end_date)
return moth_list
#获取需要查询的日结算表
date_list = get_table_list(start_timestamp,end_timestamp)
gift_incom = 0
#遍历所有需要查询的日计算表得出该歌唱家在时间范围内的全部礼物收入
log.debug("需要查询的月统计表后缀:[%s]",date_list)
for tmp_date in date_list:
gift_incom = gift_incom + GetSingerGiftIncomBy_Month_InsideID(tmp_date,insideID,start_timestamp,end_timestamp)
return gift_incom
#获取歌手的礼物收入总数量数量---根据时间范围和身份证号
# def GetSingerGiftIncomBy_TimeLlimt_InsideID(start_timestamp,end_timestamp,insideID):
# log.debug("Enter func GetSingerGiftIncomBy_Month_InsideID")
# def get_table_list(start_timestamp,end_timestamp):
# timeArray_s = time.localtime(start_timestamp)
# year = str(timeArray_s.tm_year-2000)#获取当前年的后两位
# timeArray_e = time.localtime(end_timestamp)
# begin_date = time.strftime("%Y-%m-%d", timeArray_s)
# end_date = time.strftime("%Y-%m-%d", timeArray_e)
# begin_date = datetime.datetime.strptime(begin_date, "%Y-%m-%d")
# end_date = datetime.datetime.strptime(end_date,"%Y-%m-%d")
# date_list = []
# while begin_date <= end_date:
# date_str = begin_date.strftime("%m%d")
# date_str = year + date_str
# date_list.append(date_str)
# begin_date += datetime.timedelta(days=1)
# return date_list
# #获取需要查询的日结算表
# date_list = get_table_list(start_timestamp,end_timestamp)
# gift_incom = 0
# #遍历所有需要查询的日计算表得出该歌唱家在时间范围内的全部礼物收入
# log.debug("需要查询的日期:[%s]",date_list)
# for tmp_date in date_list:
# table_name = "JIESUANTONGJI_"+str(tmp_date)
# JIESUANDBPool = getDBPoolByDBName("JIESUANDB")
# sql = "SELECT SUM(SINGERRECVGOLD )/200 as sum_SINGERRECVGOLD from %s WHERE SINGERID=%s;"%(table_name,insideID)
# log.debug("sql:[%s]",sql)
# res = JIESUANDBPool.execute(sql)
# if res:
# log.debug("res:[%s]type:[%s]",res,type(res))
# for tmp_row in res:
# gift_incom = gift_incom+ tmp_row[0] if tmp_row[0] else gift_incom
# else:
# pass
# return gift_incom
#-----------------------------------
#根据身份证ID获取歌唱家的开播时间
#-----------------------------------
def GetSingerLaunchTime(zhubo_msg):
log.debug("Enter func GetSingerLaunchTime")
for key, value in zhubo_msg.items():
dudu_id = int(key[0])
#1、根据身份证ID获取内部ID
insideID = GetInsideIDBy_DUDUID(dudu_id)
table_name = "PERFORMERSHOWTIMERECORD"+str(int(insideID)%10)
#查询该歌唱家的开播时间
IMDBPool = getDBPoolByDBName("GMDB")
if IMDBPool == None:
log.error("getDBPoolByDBName IMDBPool return None")
sql = "select STARTTIME from %s where PERFORMERID=%d ORDER BY STARTTIME limit 1;"%(table_name,insideID)
# sql = "SELECT ADDTIME FROM PERFORMERINFO where PERFORMERID=%d;"%(insideID)
log.debug("sql:[%s]",sql)
res = IMDBPool.execute(sql)
lanch_time = 0
if res:
for tmp_row in res:
lanch_time = tmp_row[0]
else:
pass
#保存开播时间
value["add_time"] = lanch_time
def wrote_file(data):
file_object = open('/home/haoren/dengquanjie/xiaomifeng_f/app/tmpfile.txt','a')
file_object.write(data)
file_object.write('\n')
file_object.close( )
#-----------------------------------
#根据身份证ID获取歌唱家的昵称
#-----------------------------------
def GetSingerNickname(in_dudu_id):
log.debug("Enter func GetSingerNickname")
table_name = "CHARBASE"+str(int(in_dudu_id)%10)
IMPOLL = getDBPoolByDBName("IMDB")
if IMPOLL == None:
log.error("getDBPoolByDBName return None")
nick_name="".decode('gbk')
sql = "SELECT CHANNELNICKNAME FROM %s WHERE ID=%d;"%(table_name,in_dudu_id)
res = IMPOLL.execute(sql)
if res:
for tmp_row in res:
try:
ss = tmp_row[0]
ss = ss.encode('unicode-escape').decode('string_escape')
nick_name = ss.decode('gbk')
except Exception as e:
log.error("dudu_id:[%d]get nick_name error",in_dudu_id)
continue
raise e
else:
pass
log.debug("dudu_id:[%d]nick_name:[%s]",in_dudu_id,nick_name)
return nick_name
#---------------------------------------
#根绝时间戳和身份证号获取当前月是
#歌唱家开始演唱的第几个月
#---------------------------------------
def GetSingerStartShowMonth(current_timestamp,add_timestamp):
log.debug("Enter func GetSingerStartShowMonth,current_timestamp:[%s]add_timestamp:[%s]",
time.localtime(current_timestamp),
time.localtime(add_timestamp))
timeArray_s = time.localtime(add_timestamp)
timeArray_e = time.localtime(current_timestamp)
days = timeArray_e.tm_yday - timeArray_s.tm_yday
return days/30
# timeArray_e1 = time.localtime(current_timestamp)
# timeArray_e2 = time.localtime(add_timestamp)
# tmp = timeArray_e1.tm_mon - timeArray_e2.tm_mon
# if tmp <=1:
# return 1
# else:
# return tmp
def Charge_Local_Month(current_timestamp,end_timestamp):
log.debug("Enter func Charge_Local_Month,current_timestamp:[%d]add_timestamp:[%d]",current_timestamp,end_timestamp)
timeArray_e1 = time.localtime(current_timestamp)
timeArray_e2 = time.localtime(end_timestamp)
tmp = timeArray_e1.tm_mon - timeArray_e2.tm_mon
if tmp <=0:
return True
else:
return False
class GlobalVar:
schedule_xinren = 0
schedule_xiaomifeng = 0
schedule_gaodixin = 0
sum_process = 0
def set_schedule_xinren(num=1):
GlobalVar.schedule_xinren += num
def set_schedule_xiaomifeng(num=1):
GlobalVar.schedule_xiaomifeng += num
def set_schedule_gaodixin(num=1):
GlobalVar.schedule_gaodixin += num
def set_sum_process(num):
GlobalVar.sum_process = num
def get_schedule_xinren():
return GlobalVar.schedule_xinren
def get_schedule_xiaomifeng():
return GlobalVar.schedule_xiaomifeng
def get_sum_process():
return GlobalVar.sum_process
def get_schedule_gaodixin():
return GlobalVar.schedule_gaodixin
def clear_all_schedule():
GlobalVar.schedule_xinren =0
GlobalVar.schedule_xiaomifeng = 0
GlobalVar.schedule_gaodixin = 0
GlobalVar.sum_process=0
=========================================================================================================
%pyspark
from pyspark.sql import Row
from pyspark.sql.types import *
from pyspark.sql.functions import udf
#import MySQLdb
import mysql.connector
import datetime
import time
import pandas as pd
import numpy as np
from fastparquet import ParquetFile
from fastparquet import write
import os
def fromDayToDay(startdate, datelen, func):
delta = datetime.timedelta(days=1)
for i in range(0,datelen):
startday = startdate + delta * i
endday = startdate + delta * (i + 1)
func(startday, endday)
return
def fromDayToEndDay(startdate, datelen, endday, func):
delta = datetime.timedelta(days=1)
for i in range(0,datelen):
startday = startdate + delta * i
#endday = startdate + delta * (i + 1)
func(startday, endday)
return
# 获取包裹数据
def saveDayPackageData(startday, endday):
strday = startday.strftime("%Y%m%d")
if os.path.exists("/home/haoren/logstatis/packagedata"+strday+".parq"):
return
#数据库连接参数
optmap = { 'dbhost':'172.18.10.2',
'dbport': 3306,
'dbuser':'haoren',
'dbpass':'123456',
'dbname':'JIESUANDB'
}
#连接数据库，创建这个类的实例
#mysql_cn= MySQLdb.connect(host=dbconfig['host'], port=dbconfig['port'],user=dbconfig['user'], passwd=dbconfig['passwd'], db=dbconfig['db'])
mysql_cn= mysql.connector.connect(user=optmap['dbuser'], password=optmap['dbpass'], host=optmap['dbhost'], port=optmap['dbport'], database=optmap['dbname'])
#strday = startday.strftime("%Y%m%d")
tsstart=time.mktime(startday.timetuple())
tsend=time.mktime(endday.timetuple())
strdate = startday.strftime("%y%m%d")
sql = "SELECT FUNCTION,IDX,UID,OLD,OP,NUM,NEW,`ID` FROM `BAOGUOTONGJI_%s`" % (strdate)
print sql
pddf = pd.read_sql(sql, con=mysql_cn)
mysql_cn.close()
print pddf.head(5)
dflen = len(pddf.index)
if dflen > 0:
print pddf.describe()
write("/home/haoren/logstatis/packagedata"+strday+".parq", pddf)
return
PackageFuncName = {
0:"包裹过期",
1:"赚票",
2:"砸蛋",
6:"开车",
}
PackageItemName = {
1:"激活星级喊话卡",
2:"免费棒棒糖",
3:"个人积分卡",
4:"赞",
5:"真好听",
6:"牛B",
7:"high起来",
8:"红心",
}
def getPackageFuncName(func):
name = PackageFuncName.get(func)
if name == None:
return str(func)
else:
return name.decode('utf8')
def savePackageData():
startday = datetime.date(2017, 5, 17)
endday = datetime.date(2017, 6, 14)
td = endday - startday
datelen = td.days + 1
# 获取包裹数据
fromDayToDay(startday, datelen, saveDayPackageData)
def getDayPackageData(startday, endday):
strday = startday.strftime("%Y%m%d")
print strday + '包裹数据'
df = spark.read.load("/home/haoren/logstatis/packagedata"+strday+".parq")
#df.show(10)
#df.createOrReplaceTempView('packagedata')
#df.registerTempTable("packagedata")
#sqlret = sqlc.sql("SELECT count(*) from packagedata")
#sqlret.show(1)
df2 = df.withColumn('FUNCNAME', udf(getPackageFuncName)(df.FUNCTION))
#df2.show(10)
df = df2.withColumn('ITEMNAME', udf(getPackageItemName)(df2.ID))
df.show(10)
df.createOrReplaceTempView('packagedata')
return
def getPackageData():
startday = datetime.date(2017, 5, 17)
endday = datetime.date(2017, 6, 14)
td = endday - startday
datelen = td.days + 1
# 获取包裹数据
fromDayToDay(startday, datelen, getDayPackageData)
print 'getPackageData finish'
def todayPackageData():
today = datetime.date.today()
# today = datetime.date(2017,8,17)
yesterday = today - datetime.timedelta(days=1)
fromDayToDay(yesterday, 1, saveDayPackageData)
fromDayToDay(yesterday, 1, getDayPackageData)
#savePackageData()
#getPackageData()
todayPackageData()
阅读(1094) | 评论(0) | 转发(0) |
