python脚本工作实例

迷南。 2022-06-08 14:51 259阅读 0赞

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 <GIFT\_INCOM\_L1:  
                                        Reward\_amount = 0  
                                elif giftincom >=GIFT\_INCOM\_L1 and giftincom<GIFT\_INCOM\_L2:  
                                        Reward\_amount = REWARD\_AMOUNT\_L1  
                                elif giftincom >=GIFT\_INCOM\_L2 and giftincom<GIFT\_INCOM\_L3:  
                                        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()