python工作实例

红太狼 2022-06-06 08:17 205阅读 0赞

\#!/usr/bin/env python  
\# encoding=gbk  
\#-\*-coding:gbk-\*-  
\# 查询用户的手机号码  
  
  
import sys  
import codecs  
import mysql.connector  
import pandas as pd  
import numpy as np  
  
  
optmap = \{   
    'dbuser' : 'haoren',  
    'dbpass' : 'hjkdhskjsh',  
    'dbhost' : '172.16.1.8',  
    'dbport' : 3306,  
    'dbname' : 'FMDB'  
\}  
  
  
def sql\_select(reqsql):  
    ret = ''  
    try:  
        db\_conn = mysql.connector.connect(user=optmap\['dbuser'\], password=optmap\['dbpass'\], host=optmap\['dbhost'\], port=optmap\['dbport'\], database=optmap\['dbname'\])  
        db\_cursor=db\_conn.cursor()  
        count = db\_cursor.execute(reqsql)  
        ret = db\_cursor.fetchall()  
    except mysql.connector.Error as e:  
        print ('Error : \{\}'.format(e))  
    finally:  
        db\_cursor.close()  
        db\_conn.close  
        return ret  
  
  
\#批量查询用户的昵称  
def getnickname(uid):  
    id = int(uid)%10  
    reqsql = "select PHONE,CHANNELNICKNAME from  CHARBASE%d where ID=%s" %(id,uid)  
    \#reqsql = "select NICKNAME from  CHARBASE%d where ID=%d" %(id,uid)  
    ret = sql\_select(reqsql)  
    print ret  
     if ret\[0\]\[0\] is not None:  
        try:  
             phone = ret\[0\]\[0\].encode('unicode-escape').decode('string\_escape').decode('gbk')  
            ss = ret\[0\]\[1\]  
             ss = ss.encode('unicode-escape').decode('string\_escape')  
             nick\_name = ss.decode('gbk')  
        except:  
            nick\_name = ''  
            phone = ''  
    else:  
        phone = ''  
        nick\_name = ''  
     return uid+','+phone+','+nick\_name  
  
  
fout = codecs.open('singerphone.csv', 'w', 'gbk')  
f = open('PERFORMERID.txt', 'r')  
f\_list = f.readlines()  
\#print f\_list  
for f\_line in f\_list:  
    id = f\_line.split()  
    print "这里id" ,id  
     if id\[0\] is not None:  
        uid = id\[0\]  
        ret = getnickname(uid)  
        print "这里uid",uid  
        fout.write(ret)  
        fout.write('\\n')  
        \#break  
fout.close()  
f.close()  
      
  
\#userlist = np.loadtxt('PERFORMERID.txt')  
\#for id in userlist:  
\#    print id  
\#print userlist   
  
===================================================================================================================  
  
cat billserver.py  
\#!/usr/bin/env python  
\# encoding=gbk  
\#-\*-coding:gbk-\*-  
\# 人民币结算处理  
  
import os,sys  
import glob  
import re  
import datetime  
import time  
import mysql.connector  
import pandas as pd  
import numpy as np  
  
  
\#160125-11:00:14 Bill\[40268\]  INFO: \[结算统计\]时间(1453690814)类别(1)名称(物品收入)频道(3977962)等级(2)用户(65147500)赠送(1)个物品(39)给客户(65147500),客户等级(28),签约(1), 消耗人民币(100), 客户获得人民币(8000), 频道获得人民币(2000),客户 当前金币(1320960)频道当前人民币(335560)  
reg='^(\\S+) Bill\\\[\\d+\\\]  INFO: \\\[结算统计\\\]时间\$(\\d+)\$类别\$(\\d+)\$名称\$(.\*?)\$频道\$(\\d+)\$等级\$(\\d+)\$用户\$(\\d+)\$赠送\$(\\d+)\$个物品\$(\\d+)\$给客户\$(\\d+)\$,客户等级\$(\\d+)\$,签约\$(\\d+)\$, 消耗人民币\$(\\d+)\$, 客户获得人民币 \$(\\d+)\$, 频道获得人民币\$(\\d+)\$,客户当前人民币\$(\\d+)\$频道当前人民币\$(\\d+)\$'  
  
def parse\_line(line):  
    if line.find('\[结算统计\]') == -1:  
        return None  
    \#print line  
    m = re.match(reg,line)  
    if m:  
         ret=','.join(\[ x.strip() for x in list(m.groups()) \])  
        \#print ret  
        return ret  
    else:  
        return None  
  
\#testline='160125-11:00:14 Bill\[40268\]  INFO: \[结算统计\]时间(1453690814)类别(1)名称(物品收入)频道(3977962)等级(2)用户(65147500)赠送(1)个物品(39)给客户(65147500),客户等级(28),签约(1), 消耗人民币(100), 客户获得人民币(8000), 频道获得人民币 (2000),客户当前人民币(1320960)频道当前人民币(335560)'  
\#parse\_line(testline)  
\#exit()  
  
  
def dayjiesuan(startday,endday):  
    dates = startday.strftime("%y%m%d")  
    \#dates = "171018"  
    print dates  
    serverip = "67"  
    servername = "billserver"  
     url = "/tmp/logdir/"+dates+"\_"+serverip+"/"+servername+".log."+dates+"-\*"  
    file\_list=glob.glob(url)  
    \#print file\_list  
     temp\_file='/tmp/haoren/'+servername+"\_"+dates+"\_"+serverip+".csv"  
    \#print temp\_file  
    if os.path.exists(temp\_file):  
        os.system("rm -f "+temp\_file)  
      
     outfile = open(temp\_file, 'w')  
    for filename in file\_list:  
        print filename  
        file = open(filename, 'r')  
        while 1:  
            lines = file.readlines(100000)  
            if not lines:  
                break  
            for line in lines:  
                ret = parse\_line(line)  
                \#break  
                if ret is not None:  
                    outfile.write(ret+'\\n')  
        file.close()  
        \#break  
    outfile.close()  
    \#print file  
    \#command = "cat %s | grep -h -w  结算统计 |grep 频道当前人民币  >> %s"%(file, temp\_file)  
    \#os.system(command)  
  
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 getJieSuanData():  
    \# 获取结算统计数据  
    startday = datetime.date(2017, 10, 3)  
    endday = datetime.date(2017, 10, 17)  
    td = endday - startday  
    datelen = td.days + 1  
    fromDayToEndDay(startday, datelen, endday, dayjiesuan)  
  
getJieSuanData()  
  
===================================================================================================================  
  
  
cat packagedata.py  
\#!/usr/bin/env python  
\# encoding=gbk  
  
  
\#from pyspark.sql import Row  
import MySQLdb  
import mysql\_op  
import datetime  
import time  
from mysql\_op import MySQL  
import pandas as pd  
import numpy as np  
from fastparquet import ParquetFile  
from fastparquet import write  
  
  
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 dayPackageData(startday, endday):  
    \#数据库连接参数    
    dbconfig = \{'host':'172.27.6.12',   
                        'port': 3306,   
                        'user':'haoren',   
                        'passwd':'hjkdhskjsh',   
                        'db':'JIESUANDB',   
                        'charset':'utf8'\}  
      
    \#连接数据库，创建这个类的实例  
    mysql\_cn= MySQLdb.connect(host=dbconfig\['host'\], port=dbconfig\['port'\],user=dbconfig\['user'\], passwd=dbconfig\['passwd'\], db=dbconfig\['db'\])  
    strday = startday.strftime("%Y%m%d")  
    tsstart=time.mktime(startday.timetuple())  
    tsend=time.mktime(endday.timetuple())  
    strdate = startday.strftime("%y%m%d")  
      
    sql = "SELECT \`INDEX\`,FUNCTION,IDX,BACKROLL,UID,OLD,OP,NUM,NEW 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()  
        \#for i in range(0,dflen):  
        \#    if i < 10:  
        \#        print pddf.values\[i\]\[2\]  
        \#    try:  
        \#        newdata = pddf.values\[i\]\[2\].decode('gbk').encode('utf8')  
        \#        pddf.values\[i\]\[2\] = newdata  
        \#    except:  
        \#        print "gbk2utf8 error:" + str(i) + " data:" + pddf.values\[i\]\[2\]  
        \#        pass  
        write("/home/haoren/logstatis/packagedata"+strday+".parq", pddf)  
    \#df = spark.createDataFrame(pddf)  
    \#df.createOrReplaceTempView('packagedata')  
    return  
      
def getPackageData():  
    startday = datetime.date(2016, 11, 23)  
    endday = datetime.date(2016, 11, 23)  
    td = endday - startday  
    datelen = td.days + 1  
    \# 获取购物车数据  
    fromDayToDay(startday, datelen, dayPackageData)  
      
getPackageData()   
  
  
  
  
  
  
  
  
  
  
==================================================================================================================================================  
  
  
  
  
cat specialflowerforsinger.py  
\#!/usr/bin/env python  
\#-\*-coding:utf-8-\*-  
\#特殊食用油客户  
  
  
import MySQLdb  
import os, sys, re,string  
import time, tarfile,getopt  
  
  
optmap = \{   
                'dbuser' : 'haoren',  
                'dbpass' : 'hjkdhskjsh',  
                'dbhost' : '172.16.1.8',  
                'dbport' : 3306,  
                'dbname' : 'AMDB'  
                 \}  
  
  
def main():  
  
  
        cur\_day = time.strftime("%Y%m%d", time.localtime(time.time()-86400))  
        opts, args = getopt.getopt(sys.argv\[1:\], 'd:')  
        for op, value in opts:  
                if op == '-d':  
                        m = re.search('\[0-9\]\{8\}', value)  
                        if m:  
                                cur\_day = value  
                        else:  
                                print "请输入8位日期(比如：20130215)"  
                                return 'no'  
  
  
        fmt\_day = time.strftime('%Y-%m-%d', time.localtime(time.mktime(time.strptime(cur\_day, '%Y%m%d'))))  
        print '客户食用油统计日期:',fmt\_day  
  
  
        db\_conn = MySQLdb.connect(user=optmap\['dbuser'\], passwd=optmap\['dbpass'\], host=optmap\['dbhost'\], port=optmap\['dbport'\], db=optmap\['dbname'\])  
        db\_cursor=db\_conn.cursor()  
        db\_conn.query("use %s"%optmap\['dbname'\])  
  
  
        sql="CREATE TABLE IF NOT EXISTS \`SPECIALFLOWERSINGERHISTORY\` (\`singer\` int(10) NOT NULL DEFAULT '0',\`all\_history\_bunch\` int(10) NOT NULL DEFAULT '0',\`all\_history\_flower\` int(10) NOT NULL DEFAULT '0')"  
        db\_conn.query(sql)  
  
  
        tabletime = time.strftime("%Y%m%d", time.localtime(time.mktime(time.strptime(cur\_day, "%Y%m%d"))))  
  
  
        \#sql="CREATE TABLE IF NOT EXISTS \`SPECIALFLOWERHANDSELFORSINGER\_%s\` (\`ID\` int(10) unsigned NOT NULL auto\_increment,\`singer\` int(10) NOT NULL DEFAULT '0',\`num\_99\` int(10) NOT NULL DEFAULT '0',\`num\_365\` int(10) NOT NULL DEFAULT   
  
  
'0',\`num\_520\` int(10) NOT NULL DEFAULT '0',\`num\_999\` int(10) NOT NULL DEFAULT '0',\`num\_all\` int(10) NOT NULL DEFAULT '0',\`all\_flower\_num\` int(10) NOT NULL DEFAULT '0',\`all\_history\_bunch\` int(10) NOT NULL DEFAULT '0',\`all\_history\_flower\`   
  
  
int(10) NOT NULL DEFAULT '0',primary key(ID))"%tabletime  
        sql="CREATE TABLE IF NOT EXISTS \`SPECIALFLOWERHANDSELFORSINGER\_%s\` like SPECIALFLOWERHANDSELFORSINGER\_SAMPLE"%tabletime  
  
  
        db\_conn.query(sql)  
        \#多次执行回退历史  
  
  
        sql ="SELECT singer,num\_all,all\_flower\_num from SPECIALFLOWERHANDSELFORSINGER\_%s"%tabletime  
        db\_cursor.execute(sql)  
        while(True):  
                data=db\_cursor.fetchone()  
                if(data == None):  
                        break  
                db\_conn.query("update SPECIALFLOWERSINGERHISTORY set all\_history\_bunch=all\_history\_bunch-%d where singer=%d"%(int(data\[1\]),int(data\[0\])))  
                db\_conn.query("update SPECIALFLOWERSINGERHISTORY set all\_history\_flower=all\_history\_flower-%d where singer=%d"%(int(data\[2\]),int(data\[0\])))  
        \#清空当天  
        db\_conn.query("delete from SPECIALFLOWERHANDSELFORSINGER\_%s"%tabletime)  
        sql ="SELECT DISTINCT singer from SPECIALFLOWERHANDSELDETAILS\_%s "%tabletime  
        db\_cursor.execute(sql)  
        singerlist=\[\]  
        while(True):  
                temp=db\_cursor.fetchone()  
                if(temp == None):  
                        break  
                singerlist.append(int(temp\[0\]))  
        for singer in singerlist:  
                flower=\{\}  
                sql ="SELECT \* from SPECIALFLOWERHANDSELDETAILS\_%s where singer=%s and flowernum=999"%(tabletime,singer)  
                db\_cursor.execute(sql)  
                flower\['999'\] = db\_cursor.rowcount  
                sql ="SELECT \* from SPECIALFLOWERHANDSELDETAILS\_%s where singer=%s and flowernum=99"%(tabletime,singer)  
                db\_cursor.execute(sql)  
                flower\['99'\] = db\_cursor.rowcount  
                sql ="SELECT \* from SPECIALFLOWERHANDSELDETAILS\_%s where singer=%s and flowernum=365"%(tabletime,singer)  
                db\_cursor.execute(sql)  
                flower\['365'\] = db\_cursor.rowcount  
                sql ="SELECT \* from SPECIALFLOWERHANDSELDETAILS\_%s where singer=%s and flowernum=520"%(tabletime,singer)  
                db\_cursor.execute(sql)  
                flower\['520'\] = db\_cursor.rowcount  
                flower\['num\_all'\]= flower\['999'\]+flower\['99'\]+flower\['365'\]+flower\['520'\]  
                flower\['all\_flower'\]= flower\['999'\]\*999 + flower\['99'\]\*99 +flower\['365'\]\*365 + flower\['520'\]\*520  
                db\_conn.query("insert into SPECIALFLOWERHANDSELFORSINGER\_%s (singer,num\_99,num\_365,num\_520,num\_999,num\_all,all\_flower\_num)values (%d,%d,%d,%d,%d,%d,%d)"%(tabletime,singer,flower\['99'\],flower\['365'\],flower\['520'\],flower  
  
  
\['999'\],flower\['num\_all'\],flower\['all\_flower'\]))  
  
  
                sql ="SELECT all\_history\_bunch,all\_history\_flower from SPECIALFLOWERSINGERHISTORY where singer=%d"%singer  
                db\_cursor.execute(sql)  
                temp=db\_cursor.fetchone()  
                if(temp == None):  
                        flower\['all\_history\_bunch'\]= flower\['num\_all'\]  
                        flower\['all\_history\_flower'\]=flower\['all\_flower'\]  
                        db\_conn.query(" insert into SPECIALFLOWERSINGERHISTORY(singer,all\_history\_bunch,all\_history\_flower)values(%d,%d,%d)"%(singer,flower\['all\_history\_bunch'\],flower\['all\_history\_flower'\]))  
                else:  
                        flower\['all\_history\_bunch'\]= flower\['num\_all'\]+int(temp\[0\])  
                        flower\['all\_history\_flower'\]=flower\['all\_flower'\]+int(temp\[1\])  
                        db\_conn.query("update SPECIALFLOWERSINGERHISTORY set all\_history\_bunch= %d,all\_history\_flower= %d where \`singer\`= %d"%(flower\['all\_history\_bunch'\],flower\['all\_history\_flower'\],singer))  
                db\_conn.query("update SPECIALFLOWERHANDSELFORSINGER\_%s set all\_history\_bunch= %d,all\_history\_flower= %d where \`singer\`= %d"%(tabletime,flower\['all\_history\_bunch'\],flower\['all\_history\_flower'\],singer))  
  
  
        db\_conn.commit()  
        db\_cursor.close()  
        db\_conn.close()  
main()  
\#if \_\_name\_\_ == "\_\_main\_\_":  
\#        main()  
  
  
  
  
  
  
=================================================================================================  
cat specialflowerforsender.py  
\#!/usr/bin/env python  
\#-\*-coding:utf-8-\*-  
\#特殊食用油送花者  
  
  
import MySQLdb  
import os, sys, re,string  
import time, tarfile,getopt  
  
  
optmap = \{   
                'dbuser' : 'haoren',  
                'dbpass' : 'hjkdhskjsh',  
                'dbhost' : '172.16.1.8',  
                'dbport' : 3306,  
                'dbname' : 'AMDB'  
                 \}  
  
  
def main():  
  
  
        cur\_day = time.strftime("%Y%m%d", time.localtime(time.time()-86400))  
        opts, args = getopt.getopt(sys.argv\[1:\], 'd:')  
        for op, value in opts:  
                if op == '-d':  
                        m = re.search('\[0-9\]\{8\}', value)  
                        if m:  
                                cur\_day = value  
                        else:  
                                print "请输入8位日期(比如：20130215)"  
                                return 'no'  
  
  
        fmt\_day = time.strftime('%Y-%m-%d', time.localtime(time.mktime(time.strptime(cur\_day, '%Y%m%d'))))  
        print '送花者统计日期:',fmt\_day  
  
  
        db\_conn = MySQLdb.connect(user=optmap\['dbuser'\], passwd=optmap\['dbpass'\], host=optmap\['dbhost'\], port=optmap\['dbport'\], db=optmap\['dbname'\])  
        db\_cursor=db\_conn.cursor()  
        db\_conn.query("use %s"%optmap\['dbname'\])  
  
  
        sql="CREATE TABLE IF NOT EXISTS \`SPECIALFLOWERSENDERHISTORY\` (\`userid\` int(10) NOT NULL DEFAULT '0',\`all\_history\_bunch\` int(10) NOT NULL DEFAULT '0',\`all\_history\_flower\` int(10) NOT NULL DEFAULT '0')"  
        db\_conn.query(sql)  
  
  
        tabletime = time.strftime("%Y%m%d", time.localtime(time.mktime(time.strptime(cur\_day, "%Y%m%d"))))  
  
  
        \#sql="CREATE TABLE IF NOT EXISTS \`SPECIALFLOWERHANDSELFORSENDER\_%s\` (\`ID\` int(10) unsigned NOT NULL auto\_increment,\`userid\` int(10) NOT NULL DEFAULT '0',\`num\_99\` int(10) NOT NULL DEFAULT '0',\`num\_365\` int(10) NOT NULL DEFAULT   
  
  
'0',\`num\_520\` int(10) NOT NULL DEFAULT '0',\`num\_999\` int(10) NOT NULL DEFAULT '0',\`num\_all\` int(10) NOT NULL DEFAULT '0',\`all\_flower\_num\` int(10) NOT NULL DEFAULT '0',\`all\_history\_bunch\` int(10) NOT NULL DEFAULT '0',\`all\_history\_flower\`   
  
  
int(10) NOT NULL DEFAULT '0',primary key(ID))"%tabletime  
        sql="CREATE TABLE IF NOT EXISTS \`SPECIALFLOWERHANDSELFORSENDER\_%s\` like SPECIALFLOWERHANDSELFORSENDER\_SAMPLE"%tabletime  
  
  
        db\_conn.query(sql)  
  
  
        \#多次执行需回退之前执行的历史记录  
        sql ="SELECT userid,num\_all,all\_flower\_num from SPECIALFLOWERHANDSELFORSENDER\_%s"%tabletime  
        db\_cursor.execute(sql)  
        while(True):  
                data=db\_cursor.fetchone()  
                if(data == None):  
                        break  
                db\_conn.query("update SPECIALFLOWERSENDERHISTORY set all\_history\_bunch=all\_history\_bunch-%d where userid=%d"%(int(data\[1\]),int(data\[0\])))  
                db\_conn.query("update SPECIALFLOWERSENDERHISTORY set all\_history\_flower=all\_history\_flower-%d where userid=%d"%(int(data\[2\]),int(data\[0\])))  
        \#清空当天  
        db\_conn.query("delete from SPECIALFLOWERHANDSELFORSENDER\_%s"%tabletime)  
        sql ="SELECT DISTINCT userid from SPECIALFLOWERHANDSELDETAILS\_%s "%tabletime  
        db\_cursor.execute(sql)  
        userlist=\[\]  
        while(True):  
                temp=db\_cursor.fetchone()  
                if(temp == None):  
                        break  
                userlist.append(int(temp\[0\]))  
        for user in userlist:  
                flower=\{\}  
                sql ="SELECT \* from SPECIALFLOWERHANDSELDETAILS\_%s where userid=%s and flowernum=999"%(tabletime,user)  
                db\_cursor.execute(sql)  
                flower\['999'\] = db\_cursor.rowcount  
                sql ="SELECT \* from SPECIALFLOWERHANDSELDETAILS\_%s where userid=%s and flowernum=99"%(tabletime,user)  
                db\_cursor.execute(sql)  
                flower\['99'\] = db\_cursor.rowcount  
                sql ="SELECT \* from SPECIALFLOWERHANDSELDETAILS\_%s where userid=%s and flowernum=365"%(tabletime,user)  
                db\_cursor.execute(sql)  
                flower\['365'\] = db\_cursor.rowcount  
                sql ="SELECT \* from SPECIALFLOWERHANDSELDETAILS\_%s where userid=%s and flowernum=520"%(tabletime,user)  
                db\_cursor.execute(sql)  
                flower\['520'\] = db\_cursor.rowcount  
                flower\['num\_all'\]= flower\['999'\]+flower\['99'\]+flower\['365'\]+flower\['520'\]  
                flower\['all\_flower'\]= flower\['999'\]\*999 + flower\['99'\]\*99 +flower\['365'\]\*365 + flower\['520'\]\*520  
                db\_conn.query("insert into SPECIALFLOWERHANDSELFORSENDER\_%s (userid,num\_99,num\_365,num\_520,num\_999,num\_all,all\_flower\_num)values (%d,%d,%d,%d,%d,%d,%d)"%(tabletime,user,flower\['99'\],flower\['365'\],flower\['520'\],flower  
  
  
\['999'\],flower\['num\_all'\],flower\['all\_flower'\]))  
  
  
                sql ="SELECT all\_history\_bunch,all\_history\_flower from SPECIALFLOWERSENDERHISTORY where userid=%d"%user  
                db\_cursor.execute(sql)  
                temp=db\_cursor.fetchone()  
                if(temp == None):  
                        flower\['all\_history\_bunch'\]= flower\['num\_all'\]  
                        flower\['all\_history\_flower'\]=flower\['all\_flower'\]  
                        db\_conn.query(" insert into SPECIALFLOWERSENDERHISTORY(userid,all\_history\_bunch,all\_history\_flower)values(%d,%d,%d)"%(user,flower\['all\_history\_bunch'\],flower\['all\_history\_flower'\]))  
                else:  
                        flower\['all\_history\_bunch'\]= flower\['num\_all'\]+int(temp\[0\])  
                        flower\['all\_history\_flower'\]=flower\['all\_flower'\]+int(temp\[1\])  
                        db\_conn.query("update SPECIALFLOWERSENDERHISTORY set all\_history\_bunch= %d,all\_history\_flower= %d where \`userid\`= %d"%(flower\['all\_history\_bunch'\],flower\['all\_history\_flower'\],user))  
                db\_conn.query("update SPECIALFLOWERHANDSELFORSENDER\_%s set all\_history\_bunch= %d,all\_history\_flower= %d where \`userid\`= %d"%(tabletime,flower\['all\_history\_bunch'\],flower\['all\_history\_flower'\],user))  
  
  
        db\_conn.commit()  
        db\_cursor.close()  
        db\_conn.close()  
main()  
\#if \_\_name\_\_ == "\_\_main\_\_":  
\#        main()  
  
  
  
  
==========================================================================================  
  
  
cat rewardInfo.py  
\#!/usr/bin/env python  
\#-\*-coding:utf-8-\*-  
\#分析日志，获得每天用户中奖信息  
\#每天一张表  
  
  
import MySQLdb  
import os, sys, re,string  
import time, tarfile,getopt  
import datetime  
import operator  
import copy  
  
  
optmap = \{   
        'dbuser' : 'haoren',  
        'dbpass' : 'hjkdhskjsh',  
        'dbhost' : '172.16.1.8',  
        'dbhost\_gm' : '172.16.1.8',  
        'dbport' : 3306,  
        'dbname' : 'FMDB',  
        'dbname\_gm' : 'AMDB'  
\}  
reward = \{   
        'userID':0,  
        'type':0,  
        'level':0,  
        'num':0,  
        'date':0  
\}  
  
  
  
  
def get\_files(dir, pattern):  
        res\_file\_list =\[\]  
        if os.path.exists(dir):  
                cur\_file\_list = os.listdir(dir)  
                for file\_name in cur\_file\_list:  
                        if re.search(pattern, file\_name):  
                                res\_file\_list.append(file\_name)  
                return res\_file\_list  
        else:  
                return 'no'                                
  
  
  
  
def main():   
  
  
        cur\_day = time.strftime("%Y%m%d", time.localtime(time.time()-86400))  
        log\_day = time.strftime('%y%m%d', time.localtime(time.mktime(time.strptime(cur\_day, '%Y%m%d'))))  
        if len(sys.argv)>1:  
                cur\_day = sys.argv\[1\]  
                log\_day = sys.argv\[1\]\[2:\]  
  
  
        dirname = "/home/haoren/logdir/%s\_19"%log\_day  
        reward\_list = \[\]  
        for fileName in os.listdir(dirname):  
                if (fileName.find("loginrewardserver.log") == -1):  
                        continue  
                a\_file = open(dirname+"/"+fileName,'r')  
                \#140522-00:31:16 LoginRewardServer\[16600\] DEBUG: 用户\[54011503\]在第\[1\]关中奖,中奖类型\[1\],中奖数量\[100\],是否广播给客户端\[0\]  
                for a\_line in a\_file.readlines():  
                        reward\_info = re.search("^(\\S+) LoginRewardServer\\\[\\d+\\\] DEBUG: 用户\\\[(\\d+)\\\]在第\\\[(\\d+)\\\]关中奖,中奖类型\\\[(\\d+)\\\],中奖数量\\\[(\\d+)\\\],是否广播给客户端\\\[0\\\]",a\_line)  
                        if reward\_info:  
  
  
                                reward\_ = \{\}  
                                reward\_\['userID'\] = reward\_info.group(2)  
                                reward\_\['type'\] = reward\_info.group(4)  
                                reward\_\['level'\] = reward\_info.group(3)  
                                reward\_\['num'\] = reward\_info.group(5)  
                                reward\_\['date'\] = str("20") + str(reward\_info.group(1)\[0:2\]) + "-" + str(reward\_info.group(1)\[2:4\]) + "-" + str(reward\_info.group(1)\[4:6\]) + " " + str(reward\_info.group(1)\[7:\])   
                                reward\_list.append(reward\_)  
                        a\_file.close()  
  
  
  
  
\#       for reward in reward\_list:  
\#               print "uid:" + reward\['userID'\] + " type:" + reward\['type'\] + " level:" + reward\['level'\] + " num:" + reward\['num'\] + " date:" + reward\['date'\]  
   
  
  
        db\_conn = MySQLdb.connect(user=optmap\['dbuser'\], passwd=optmap\['dbpass'\], host=optmap\['dbhost\_gm'\], port=optmap\['dbport'\], db=optmap\['dbname\_gm'\])  
        db\_conn.query("use %s" %optmap\['dbname\_gm'\])  
        db\_cursor = db\_conn.cursor()  
        tabletime = time.strftime("%Y%m%d", time.localtime(time.mktime(time.strptime(cur\_day, "%Y%m%d"))))  
        \#print "数据库表时间后缀%s"%tabletime  
        tabletime = cur\_day  
        print '用户中奖信息，统计日期:',tabletime  
        sql="CREATE TABLE IF NOT EXISTS \`REWARDINFO\_%s\` like REWARDINFO\_SAMPLE"%tabletime  
        \#delete  
        try:  
                db\_conn.query("drop table REWARDINFO\_%s"%tabletime)  
        except:  
                pass \#print "The first statistics."  
        \#create  
        db\_conn.query(sql)  
  
  
  
  
        \#update yesterday data  
        total = range(10)  
        for reward in reward\_list:  
                timeArray = time.strptime(reward\['date'\],"%Y-%m-%d %H:%M:%S")  
                timeStamp = int(time.mktime(timeArray))  
                sql = "insert into REWARDINFO\_%s(userID,type,level,num,timestamp)value(%d,%d,%d,%d,%d)"%(tabletime,int(reward\['userID'\]),int(reward\['type'\]),int(reward\['level'\]),int(reward\['num'\]),timeStamp)  
                db\_conn.query(sql)  
  
  
                \#update rewardTotal  
                for i in range(1,10):  
                        if( int(reward\['level'\]) == int(i) ):  
                                total\[i\] = int(total\[i\]) + int(reward\['num'\])  
  
  
        for i in range(1,10):  
                sql = "insert into SIGNEDCARD(level%d,timestamp)value(%d,%d)"%(int(i),total\[i\],timeStamp)  
                db\_conn.query(sql)  
  
  
  
  
        db\_conn.commit()  
        db\_cursor.close()  
        db\_conn.close()  
  
  
        print "SUCCESS."  
  
  
  
  
main()  
\#if \_\_name\_\_ == "\_\_main\_\_":  
\#        main()  
  
  
===============================================================================================================  
cat vipactiveusernum.py  
\#!/usr/bin/env python  
\#-\*-coding:utf-8-\*-  
\#会员信息统计  
import MySQLdb  
import os, sys, re, string  
import time, getopt  
  
  
optmap = \{   
                'dbuser' : 'haoren',  
                'dbpass' : 'hjkdhskjsh',  
                'dbhost' : '172.16.1.8',  
                'dbport' : 3306,  
                'dbname' : 'AMDB',  
                \#'logdir' : '/home/haoren/log/',                         \#内网环境日志目录  
                'logdir' : '/home/haoren/logdir/',                      \#外网环境日志目录  
                \#'logpattern' : '^chat\_sessionserver.log.',     \#内网环境日志名称前缀  
                'logpattern' : '^sessionserver.log.'            \#外网环境日志名称前缀  
                \}  
  
  
def get\_files(dir, pattern):  
        print dir, pattern  
        match\_file\_list = \[\]  
        if os.path.exists(dir):  
                cur\_file\_list = os.listdir(dir)  
                for file\_name in cur\_file\_list:  
                        if re.search(pattern, file\_name):  
                                match\_file\_list.append(file\_name)  
                return match\_file\_list  
        else:  
                return 'no'  
  
  
def main():  
        one\_day = time.strftime("%Y%m%d", time.localtime(time.time() - 24\*60\*60))       \#默认日期为脚本运行的上一天  
        opts, args = getopt.getopt(sys.argv\[1:\], 'd:')  
        for op, value in opts:  
                if op == '-d':  
                        m = re.search('\[0-9\]\{8\}', value)  
                        if m:  
                                one\_day = value  
                        else:  
                                print "请输入8位日期(比如：20130215)"  
                                return 'no'  
  
  
        print "正在读取VIP用户数据(%s)..." %one\_day  
        db\_conn = MySQLdb.connect(user=optmap\['dbuser'\], passwd=optmap\['dbpass'\], host=optmap\['dbhost'\], port=optmap\['dbport'\], db=optmap\['dbname'\])  
        db\_cursor = db\_conn.cursor()  
  
  
        temp\_vip\_active\_user\_num\_file\_name = '/tmp/vipactiveusernumtemp.txt'  
        command = "cat /dev/null > %s" %(temp\_vip\_active\_user\_num\_file\_name)  
        os.system(command)  
  
  
        if re.search('haoren', optmap\['logdir'\]):  
                print '外网环境'  
                log\_dir\_name\_list = get\_files(optmap\['logdir'\], one\_day\[2:\])  
                for log\_dir\_name\_item in log\_dir\_name\_list:  
                        log\_dir\_full\_path = optmap\['logdir'\]+log\_dir\_name\_item+'/'  
                        log\_file\_name\_list = get\_files(log\_dir\_full\_path, optmap\['logpattern'\] + one\_day\[2:\])  
                        for log\_file\_name\_item in log\_file\_name\_list:  
                                print log\_file\_name\_item  
                                command = "cat %s%s |awk '/用户登录/' |awk '/vip状态/' >> %s" % (log\_dir\_full\_path, log\_file\_name\_item, temp\_vip\_active\_user\_num\_file\_name)  
                                os.system(command)  
        else:  
                print '内网环境'  
                log\_file\_name\_list = get\_files(optmap\['logdir'\], optmap\['logpattern'\] + one\_day\[2:\])  
                for log\_file\_name\_item in log\_file\_name\_list:  
                        command = "cat %s%s |awk '/用户登录/' |awk '/vip状态/' >> %s" % (optmap\['logdir'\], log\_file\_name\_item, temp\_vip\_active\_user\_num\_file\_name)  
                        os.system(command)  
  
  
        command = "cat %s |wc -l" %temp\_vip\_active\_user\_num\_file\_name  
        os.system(command)  
  
  
        \#一天当中用户可能从月会员降级到周会员，造成不同会员状态的同一帐号统计两次，所以总会员!=年会员+月会员+周会员)  
        \#不同状态的会员用同一计算机登录，所以总mac/ip!=年mac/ip+月mac/ip+周mac/ip  
        total\_account\_map = \{\}  
        total\_mac\_map = \{\}  
        total\_ip\_map = \{\}  
        before\_account\_map = \{\}  
        before\_mac\_map = \{\}  
        before\_ip\_map = \{\}  
  
  
        account\_map = \{1:\{\}, 2:\{\}, 3:\{\}, 11:\{\}, 12:\{\}, 13:\{\}\}  
        mac\_map = \{1:\{\}, 2:\{\}, 3:\{\}, 11:\{\}, 12:\{\}, 13:\{\}\}  
        ip\_map = \{1:\{\}, 2:\{\}, 3:\{\}, 11:\{\}, 12:\{\}, 13:\{\}\}  
  
  
        temp\_vip\_active\_user\_num\_file = open(temp\_vip\_active\_user\_num\_file\_name)  
        for one\_line in temp\_vip\_active\_user\_num\_file.readlines():  
                match = re.search("^(\\S+) SS\\\[\\d+\\\] TRACE: 用户登录:imid:(\\d+),mac地址:(\\d+),ip地址:(\\d+),vip状态:(\\d+),登录时间:(\\d+)(\\S+)", one\_line)  
                if match:  
                        if string.atoi(match.group(5)) in (1, 2, 3):  
                                total\_account\_map\[string.atoi(match.group(2))\] = string.atoi(match.group(5))  
                                total\_mac\_map\[string.atoi(match.group(3))\] = string.atoi(match.group(5))  
                                total\_ip\_map\[string.atoi(match.group(4))\] = string.atoi(match.group(5))  
                        elif string.atoi(match.group(5)) in (11, 12, 13):  
                                before\_account\_map\[string.atoi(match.group(2))\] = string.atoi(match.group(5))  
                                before\_mac\_map\[string.atoi(match.group(3))\] = string.atoi(match.group(5))  
                                before\_ip\_map\[string.atoi(match.group(4))\] = string.atoi(match.group(5))  
                        account\_map\[string.atoi(match.group(5))\]\[string.atoi(match.group(2))\] = string.atoi(match.group(3))  
                        mac\_map\[string.atoi(match.group(5))\]\[string.atoi(match.group(3))\] = string.atoi(match.group(2))  
                        ip\_map\[string.atoi(match.group(5))\]\[string.atoi(match.group(4))\] = string.atoi(match.group(2))  
        temp\_vip\_active\_user\_num\_file.close()  
  
  
        dword\_time = time.mktime(time.strptime(one\_day, '%Y%m%d'))  
        db\_conn.query("use %s" %optmap\['dbname'\])  
        sql = "delete from VIPACTIVEUSERNUM where active\_time='%d'" %dword\_time  
        print sql  
        db\_conn.query(sql)  
  
  
        sql = "insert into VIPACTIVEUSERNUM (active\_time) values('%d')" %(dword\_time)  
        print sql  
        db\_conn.query(sql)  
  
  
        sql = "update VIPACTIVEUSERNUM set year\_account\_num=%d, year\_mac\_num=%d, year\_ip\_num=%d, month\_account\_num=%d, month\_mac\_num=%d, month\_ip\_num=%d, week\_account\_num=%d, week\_mac\_num=%d, week\_ip\_num=%d, total\_mac\_num=%d,   
  
  
total\_ip\_num=%d, before\_account\_num=%d, before\_mac\_num=%d, before\_ip\_num=%d where active\_time='%d'" %(len(account\_map\[3\]), len(mac\_map\[3\]), len(ip\_map\[3\]), len(account\_map\[2\]), len(mac\_map\[2\]), len(ip\_map\[2\]), len(account\_map\[1\]), len  
  
  
(mac\_map\[1\]), len(ip\_map\[1\]), len(total\_mac\_map), len(total\_ip\_map), len(before\_account\_map), len(before\_mac\_map), len(before\_ip\_map), dword\_time)  
        print sql  
        db\_conn.query(sql)  
  
  
        db\_conn.commit()  
  
  
        db\_cursor.close()  
        db\_conn.close()  
  
  
main()  
\#if \_\_name\_\_ == "\_\_main\_\_"  
\#                main()  
  
  
===================================================================================================================  
cat  specialflowerdetails.py  
\#!/usr/bin/env python  
\#-\*-coding:utf-8-\*-  
\#特殊食用油明细  
  
  
import MySQLdb  
import os, sys, re,string  
import time, tarfile,getopt  
  
  
optmap = \{   
                'dbuser' : 'haoren',  
                'dbpass' : 'hjkdhskjsh',  
                'dbhost' : '172.16.1.8',  
                'dbport' : 3306,  
                'dbname' : 'AMDB'  
                 \}  
def get\_files(dir, pattern):  
        res\_file\_list =\[\]  
        if os.path.exists(dir):  
                cur\_file\_list = os.listdir(dir)  
                for file\_name in cur\_file\_list:  
                        if re.search(pattern, file\_name):  
                                res\_file\_list.append(file\_name)  
                return res\_file\_list  
        else:  
                return 'no'  
  
  
def main():  
  
  
        cur\_day = time.strftime("%Y%m%d", time.localtime(time.time()-86400))  
  
  
        opts, args = getopt.getopt(sys.argv\[1:\], 'd:')  
        for op, value in opts:  
                if op == '-d':  
                        m = re.search('\[0-9\]\{8\}', value)  
                        if m:  
                                cur\_day = value  
                        else:  
                                print "请输入8位日期(比如：20130215)"  
                                return 'no'  
  
  
        log\_day = time.strftime('%y%m%d', time.localtime(time.mktime(time.strptime(cur\_day, '%Y%m%d'))))  
  
  
        fmt\_day = time.strftime('%Y-%m-%d', time.localtime(time.mktime(time.strptime(cur\_day, '%Y%m%d'))))  
        print '食用油明细统计日期:',fmt\_day  
  
  
        \#log\_day = time.strftime("%y%m%d", time.localtime(time.time()-86400))  
        dirname="/home/haoren/logdir/%s\_138"%log\_day  
        print dirname  
  
  
        \#log\_day = time.strftime("%y%m%d", time.localtime(time.time()-86400))  
        dirname1="/home/haoren/logdir/%s\_139"%log\_day  
        print dirname1  
  
  
        db\_conn = MySQLdb.connect(user=optmap\['dbuser'\], passwd=optmap\['dbpass'\], host=optmap\['dbhost'\], port=optmap\['dbport'\], db=optmap\['dbname'\])  
        db\_cursor=db\_conn.cursor()  
        db\_conn.query("use %s"%optmap\['dbname'\])  
        tabletime = time.strftime("%Y%m%d", time.localtime(time.mktime(time.strptime(cur\_day, "%Y%m%d"))))  
  
  
        \#sql="CREATE TABLE IF NOT EXISTS \`SPECIALFLOWERHANDSELDETAILS\_%s\` (\`ID\` int(10) unsigned NOT NULL auto\_increment,\`occur\_time\` varchar(24) NOT NULL DEFAULT '', \`userid\` int(10) NOT NULL DEFAULT '0',\`singer\` int(10) NOT NULL   
  
  
DEFAULT '0',\`flowernum\` int(10) NOT NULL DEFAULT '0',primary key(ID))"%tabletime  
        sql="CREATE TABLE IF NOT EXISTS \`SPECIALFLOWERHANDSELDETAILS\_%s\` like SPECIALFLOWERHANDSELDETAILS\_SAMPLE"%tabletime  
  
  
        db\_conn.query(sql)  
        db\_conn.query("delete from SPECIALFLOWERHANDSELDETAILS\_%s"%tabletime)  
  
  
        if os.path.exists("/tmp/specialflower.txt"):  
                os.system("rm -f /tmp/specialflower.txt")  
  
  
        \#dirname="/home/xuxiu/log/"  
        \#dirname="/tmp/"  
  
  
        file\_list2=get\_files(dirname,'flowerserver')  
        for file2 in file\_list2:  
                command = "cat %s/%s | awk '/特殊食用油/'>> /tmp/specialflower.txt"%(dirname,file2)  
                os.system(command)  
  
  
        file\_list3=get\_files(dirname1,'flowerserver')  
        for file3 in file\_list3:  
                command = "cat %s/%s | awk '/特殊食用油/'>> /tmp/specialflower.txt"%(dirname1,file3)  
                os.system(command)  
        \#特殊食用油  
        filename='/tmp/specialflower.txt'  
        record = \{\}  
        a\_file = open(filename, 'r')  
        \#130307-16:13:43 Show\[980\] TRACE: \[特殊食用油\]送花者:21001435,接受者:21000127,食用油:999  
        for a\_line in a\_file.readlines():  
                m = re.search("^(\\S+) FlowerServer\\\[\\d+\\\] TRACE: \\\[特殊食用油\\\]送花者:(\\d+),接受者:(\\d+),食用油:(\\d+)", a\_line)  
                if m:  
                        db\_conn.query("insert into SPECIALFLOWERHANDSELDETAILS\_%s(occur\_time,userid,singer,flowernum) values('%s',%d,%d,%d)"%(tabletime,str(m.group(1)),int(m.group(2)),int(m.group(3)),int(m.group(4))))  
        a\_file.close()  
  
  
        db\_conn.commit()  
        db\_cursor.close()  
        db\_conn.close()  
main()  
\#if \_\_name\_\_ == "\_\_main\_\_":  
\#        main()  
  
  
  
  
  
  
  
  
  
  
  
  
========================================================================================================  
cat racingincomemonthly.py  
\#!/usr/bin/env python  
\#-\*-coding:utf-8-\*-  
\#人民赛车日报  
\#\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*使用说明\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
\# 内网测试通过命令行参数  
\# -d 指定统计时间  
\# 示例如下:  
\# \[haoren@localhost tongji\]$ ./racingincomemonthly.py -d 20141112  
\# 外网无需加参数,使用默认值  
\#\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*使用说明\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
  
  
import MySQLdb  
import os, sys, re,string  
import time, tarfile,getopt  
\#import argparse  
  
  
optmap = \{   
                'dbuser' : 'haoren',  
                'dbpass' : 'hjkdhskjsh',  
                'dbhost' : '172.16.1.8',  
                'dbport' : 3306,  
                'dbname' : 'AMDB'  
        \}  
def get\_files(dir, pattern):  
        res\_file\_list =\[\]  
        if os.path.exists(dir):  
                cur\_file\_list = os.listdir(dir)  
                cur\_file\_list.sort()  
                for file\_name in cur\_file\_list:  
                        if re.search(pattern, file\_name):  
                                res\_file\_list.append(file\_name)  
                return res\_file\_list  
        else:  
                return 'no'  
  
  
def main():  
  
  
        \#外网环境默认参数  
        cur\_day = time.strftime("%Y%m%d", time.localtime(time.time()-86400))  
        tmpdirname = "/tmp/"  
        logname = "billserver"  
  
  
        \#内网测试指定参数  
        opts, args = getopt.getopt(sys.argv\[1:\], 'd:p:t:')  
        for op, value in opts:  
                if op == '-d':  
                        m = re.search('\[0-9\]\{8\}', value)  
                        if m:  
                                cur\_day = value  
                                log\_suffix = time.strftime('%y%m%d', time.localtime(time.mktime(time.strptime(cur\_day, '%Y%m%d'))))  
                                logname = "billserver.log"  
  
  
                        else:  
                                print "请输入8位日期(比如：20130215)"  
                                return 'no'  
  
  
        log\_day = time.strftime('%y%m%d', time.localtime(time.mktime(time.strptime(cur\_day, '%Y%m%d'))))  
        logdirname = "/home/haoren/logdir/%s\_67"%log\_day  
  
  
        fmt\_day = time.strftime('%Y-%m-%d', time.localtime(time.mktime(time.strptime(cur\_day, '%Y%m%d'))))  
        d\_time = time.mktime(time.strptime(fmt\_day,'%Y-%m-%d'))  
        print '统计日期:',fmt\_day  
        print '日志名称:',logname  
        print '日志路径:',logdirname  
        print '临时文本:',tmpdirname  
  
  
        \#赛车收入  
        if os.path.exists("%sracingincome.txt"%tmpdirname):  
                os.system("rm -f %sracingincome.txt"%tmpdirname)  
  
  
        file\_list1=get\_files(logdirname, logname)  
        for file1 in file\_list1:  
                command = "cat %s/%s | awk '/人民赛车/' >> %sracingincome.txt"%(logdirname,file1,tmpdirname)  
                os.system(command)  
  
  
        \#赛车收入  
        filename='%sracingincome.txt'%tmpdirname  
        a\_file = open(filename, 'r')  
        \#赛车消耗用户总人民币  
        allcoin = 0  
        \#赛车用户赢取人民币  
        playerwin = 0  
        \#参与人数   
        playernum = 0  
        \#机器人数  
        robotnum = 0  
        \#下注次数  
        betnum = 0  
        \#中奖次数  
        winnum = 0  
        \#参与用户  
        userlist = \[\]  
  
  
        for a\_line in a\_file.readlines():  
                \#150226-05:11:23 Bill\[990\]  INFO: \[人民赛车\]用户(21235)押注，扣除人民币(1000)成功  
                m = re.search("^(\\S+) Bill\\\[\\d+\\\]  INFO: \\\[人民赛车\]用户\$(\\d+)\$押注，扣除人民币\$(\\d+)\$成功", a\_line)  
                if m:  
                        allcoin += int(m.group(3))  
                        betnum += 1  
                        userid = int(m.group(2))  
                        \#参与用户列表  
                        if (userid not in userlist):  
                                userlist.append(userid)  
                                playernum += 1  
  
  
                \#150226-05:17:22 Bill\[990\]  INFO: \[人民赛车\]用户(21235)增加人民币(10)成功  
                m1 = re.search("^(\\S+) Bill\\\[\\d+\\\]  INFO: \\\[人民赛车\]用户\$(\\d+)\$增加人民币\$(\\d+)\$成功", a\_line)  
                if m1:  
                        playerwin += int(m1.group(3))  
                        winnum += 1  
  
  
        print '总金额(人民币):',allcoin/100  
        print '用户赢的人民币:',playerwin/100  
        print '官方赢的人民币:',(allcoin-playerwin)/100  
        print '参与人数:',playernum  
        print '用户列表:',len(userlist)  
        print '下注次数:',betnum  
        print '中奖次数:',winnum  
  
  
        a\_file.close()  
  
  
        db\_conn = MySQLdb.connect(user=optmap\['dbuser'\], passwd=optmap\['dbpass'\], host=optmap\['dbhost'\], port=optmap\['dbport'\], db=optmap\['dbname'\])  
        db\_cursor=db\_conn.cursor()  
        db\_conn.query("use %s"%optmap\['dbname'\])  
  
  
        d\_time = time.mktime(time.strptime(cur\_day,'%Y%m%d'))  
        db\_conn.query("delete from RACINGINCOMEMONTHLY where TIME=%u"%d\_time)  
        db\_conn.query("insert into RACINGINCOMEMONTHLY(TIME,TOTALCOIN,PLAYERWINCOIN,PLAYERNUM,BETNUM,LUCKYNUM) values(%d,%d,%d,%d,%d,%d)"%(d\_time, allcoin, playerwin, playernum, betnum, winnum))  
  
  
        db\_conn.commit()  
        db\_cursor.close()  
        db\_conn.close()  
main()  
\#if \_\_name\_\_ == "\_\_main\_\_":  
\#                main()  
  
  
  
  
  
  
======================================================================================================================================  
cat charge.py  
\#!/usr/bin/env python  
\#-\*-coding:utf-8-\*-  
\#消费相关  
import MySQLdb  
import os, sys, re,string  
import time, tarfile,getopt  
  
  
optmap = \{   
                'dbuser' : 'haoren',  
                'dbpass' : 'hjkdhskjsh',  
                'dbhost' : '172.16.1.8',  
                'dbport' : 3306,  
                'dbname' : 'AMDB'  
        \}  
def get\_files(dir, pattern):  
        res\_file\_list =\[\]  
        if os.path.exists(dir):  
                cur\_file\_list = os.listdir(dir)  
                for file\_name in cur\_file\_list:  
                        if re.search(pattern, file\_name):  
                                res\_file\_list.append(file\_name)  
                return res\_file\_list  
        else:  
                return 'no'  
  
  
def main():  
  
  
        cur\_day = time.strftime("%Y%m%d", time.localtime(time.time()-86400))  
  
  
        opts, args = getopt.getopt(sys.argv\[1:\], 'd:')  
        for op, value in opts:  
                if op == '-d':  
                        m = re.search('\[0-9\]\{8\}', value)  
                        if m:  
                                cur\_day = value  
                        else:  
                                print "请输入8位日期(比如：20130215)"  
                                return 'no'  
  
  
        fmt\_day = time.strftime('%Y-%m-%d', time.localtime(time.mktime(time.strptime(cur\_day, '%Y%m%d'))))  
        print '消费统计日期:',fmt\_day  
        \#log\_day = time.strftime("%y%m%d", time.localtime(time.time()-86400))  
        log\_day = time.strftime('%y%m%d', time.localtime(time.mktime(time.strptime(cur\_day, '%Y%m%d'))))  
        \#dirname="/home/xuxiu/log/%s/"%log\_day  
        \#dirname="/home/xuxiu/log/1/"  
        dirname="/home/haoren/logdir/%s\_67"%log\_day  
        print dirname  
        \#会员消耗点数  
        if os.path.exists("/tmp/vippoint.txt"):  
                os.system("rm -f /tmp/vippoint.txt")  
        \#人民币消耗点数  
        if os.path.exists("/tmp/coinpoint.txt"):  
                os.system("rm -f /tmp/coinpoint.txt")  
        \#充值  
        if os.path.exists("/tmp/recharge.txt"):  
                os.system("rm -f /tmp/recharge.txt")  
        \#人民币消耗  
        if os.path.exists("/tmp/coinreduce.txt"):  
                os.system("rm -f /tmp/coinreduce.txt")  
        \#人民币补偿  
        if os.path.exists("/tmp/coingmadd.txt"):  
                os.system("rm -f /tmp/coingmadd.txt")  
        \#dirname="/tmp/"  
        \#物品消耗  
        if os.path.exists("/tmp/props.txt"):  
                os.system("rm -f /tmp/props.txt")  
  
  
        file\_list2=get\_files(dirname,'billserver')  
        for file2 in file\_list2:  
                command = "cat %s/%s | awk '/点数统计/'|awk '/会员消耗点数/'>> /tmp/vippoint.txt"%(dirname,file2)  
                os.system(command)  
                command = "cat %s/%s | awk '/点数统计/'|awk '/人民币消耗点数/'>> /tmp/coinpoint.txt"%(dirname,file2)  
                os.system(command)  
                command = "cat %s/%s | awk '/点数统计/'|awk '/充值点数/'>> /tmp/recharge.txt"%(dirname,file2)  
                os.system(command)  
                command = "cat %s/%s | awk '/人民币统计/'|awk '/会员消耗人民币/'>> /tmp/coinreduce.txt"%(dirname,file2)  
                os.system(command)  
                command = "cat %s/%s | awk '/人民币统计/'|awk '/GM增加人民币/'>> /tmp/coingmadd.txt"%(dirname,file2)  
                os.system(command)  
                command = "cat %s/%s | awk '/物品统计/'>> /tmp/props.txt"%(dirname,file2)  
                os.system(command)  
        \#会员消耗点数  
        filename='/tmp/vippoint.txt'  
        record = \{\}  
        record\['vippoint'\]=0  
        a\_file = open(filename, 'r')  
        for a\_line in a\_file.readlines():  
                m = re.search("^(\\S+) Bill\\\[\\d+\\\] TRACE: \\\[点数统计\\\]会员消耗点数,用户:(\\d+),点数:(\\d+)", a\_line)  
                if m:  
                        vfind = 0  
                        for mykey in record:  
                                if mykey == 'vippoint':  
                                        vfind = 1  
                                        record\[mykey\] += int(m.group(3))  
                        if(0==vfind):  
                                record\['vippoint'\] = int(m.group(3))  
  
  
        print '会员总消耗点数:',record\['vippoint'\]  
  
  
        a\_file.close()  
        \#人民币消耗点数 人民币产出  
        filename='/tmp/coinpoint.txt'  
        a\_file = open(filename, 'r')  
        record\['coinpoint'\]=0  
        record\['coinexchange'\]=0  
        for a\_line in a\_file.readlines():  
                m = re.search("^(\\S+) Bill\\\[\\d+\\\] TRACE: \\\[点数统计\\\]人民币消耗点数,用户:(\\d+),点数:(\\d+),产生人民币:(\\d+)", a\_line)  
                if m:  
                        cfind = 0  
                        for mykey in record:  
                                if mykey == 'coinpoint':  
                                        cfind = 1  
                                        record\[mykey\] += int(m.group(3))  
                        if(0==cfind):  
                                record\['coinpoint'\] = int(m.group(3))  
                        \#兑换的人民币  
                        find1 = 0  
                        for mykey in record:  
                                if mykey == 'coinexchange':  
                                        find1 = 1  
                                        record\[mykey\] += int(m.group(4))  
                        if(0==find1):  
                                record\['coinexchange'\] = int(m.group(4))  
  
  
        print '人民币总消耗点数:',record\['coinpoint'\]  
  
  
        record\['allconsumepoint'\]= record\['coinpoint'\]+record\['vippoint'\]   
  
  
        print '总消耗点数',record\['allconsumepoint'\]  
  
  
        print '兑换产生人民币',record\['coinexchange'\]  
  
  
        a\_file.close()  
        \#总充值点数  
        filename='/tmp/recharge.txt'  
        a\_file = open(filename, 'r')  
        record\['recharge'\]=0  
        for a\_line in a\_file.readlines():  
                m = re.search("^(\\S+) Bill\\\[\\d+\\\] TRACE: \\\[点数统计\\\]充值点数,用户:(\\d+),点数:(\\d+)", a\_line)  
                if m:  
                        rfind = 0  
                        for mykey in record:  
                                if mykey == 'recharge':  
                                        rfind = 1  
                                        record\[mykey\] += int(m.group(3))  
                        if(0==rfind):  
                                record\['recharge'\] = int(m.group(3))  
  
  
        print '总充值点数:',record\['recharge'\]  
        a\_file.close()  
  
  
        \#人民币消耗(会员)  
        filename='/tmp/coinreduce.txt'  
        a\_file = open(filename, 'r')  
        record\['coinreduce'\]=0  
        for a\_line in a\_file.readlines():  
                m = re.search("^(\\S+) Bill\\\[\\d+\\\] TRACE: \\\[人民币统计\\\]会员消耗人民币,用户:(\\d+),人民币:(\\d+)", a\_line)  
                if m:  
                        crfind = 0  
                        for mykey in record:  
                                if mykey == 'coinreduce':  
                                        crfind = 1  
                                        record\[mykey\] += int(m.group(3))  
                        if(0==crfind):  
                                record\['coinreduce'\] = int(m.group(3))  
  
  
        print '会员消耗人民币总量:',record\['coinreduce'\]  
  
  
        a\_file.close()  
  
  
        \#人民币消耗(物品)  
        filename='/tmp/props.txt'  
        a\_file = open(filename, 'r')  
        record\['coinprops'\]=0  
        \#130815-15:01:06 Bill\[990\]  INFO: \[物品统计\]频道(210043)等级(9)用户(21000162)赠送(1)个物品(3)给客户(21000264),客户等级(1),签约(0), 消耗人民币(10), 客户获得人民币(470), 频道获得人民币(30)  
        for a\_line in a\_file.readlines():  
                m = re.search("^(\\S+) Bill\\\[\\d+\\\]  INFO: \\\[物品统计\\\]频道\$(\\d+)\$等级\$(\\d+)\$用户\$(\\d+)\$赠送\$(\\d+)\$个物品\$(\\d+)\$给客户\$(\\d+)\$,客户等级\$(\\d+)\$,签约\$(\\d+)\$, 消耗人民币\$(\\d+)\$, 客户获得人民币\$(\\d+)\$, 频道获  
  
  
得金币\$(\\d+)\$", a\_line)  
                if m:  
                        crfind = 0  
                        for mykey in record:  
                                if mykey == 'coinprops':  
                                        crfind = 1  
                                        record\[mykey\] += int(m.group(10))  
                        if(0==crfind):  
                                record\['coinprops'\] = int(m.group(10))  
  
  
        print '物品消耗人民币总量:',record\['coinprops'\]  
        a\_file.close()  
        record\['allconsumecoin'\] = record\['coinprops'\] + record\['coinreduce'\]  
  
  
        print '人民币消耗总量:',record\['allconsumecoin'\]  
        \#GM增加人民币  
        filename='/tmp/coingmadd.txt'  
        a\_file = open(filename, 'r')  
        record\['coingmadd'\]=0  
        for a\_line in a\_file.readlines():  
                m = re.search("^(\\S+) Bill\\\[\\d+\\\] TRACE: \\\[人民币统计\\\]GM增加人民币,用户:(\\d+),人民币:(\\d+)", a\_line)  
                if m:  
                        cafind = 0  
                        for mykey in record:  
                                if mykey == 'coingmadd':  
                                        cafind = 1  
                                        record\[mykey\] += int(m.group(3))  
                        if(0==cafind):  
                                record\['coingmadd'\] = int(m.group(3))  
        a\_file.close()  
        print '补偿产出人民币总量:',record\['coingmadd'\]  
  
  
        record\['cointodaylast'\] = record\['coinexchange'\] - record\['coinreduce'\] - record\['coinprops'\] + record\['coingmadd'\]  
  
  
        print '人民币今天结存:',record\['cointodaylast'\]  
  
  
        db\_conn = MySQLdb.connect(user=optmap\['dbuser'\], passwd=optmap\['dbpass'\], host=optmap\['dbhost'\], port=optmap\['dbport'\], db=optmap\['dbname'\])  
        db\_cursor=db\_conn.cursor()  
        db\_conn.query("use %s"%optmap\['dbname'\])  
        \#先对表初始化  
        \#cur\_day = time.strftime("%Y%m%d", time.localtime(time.time()-86400))  
        \#fmt\_day = time.strftime('%Y-%m-%d', time.localtime(time.mktime(time.strptime(cur\_day, '%Y%m%d'))))  
        db\_conn.query("delete from TRANSACTIONSUMMARY where tongji\_time='%s'"%fmt\_day)  
        db\_conn.query("insert into TRANSACTIONSUMMARY (tongji\_time) values('%s')"%fmt\_day)  
  
  
        db\_conn.query("update TRANSACTIONSUMMARY set all\_recharge\_point=%d,all\_consume\_point=%d,coin\_consume\_point=%d,vip\_consume\_point=%d,gm\_add\_coin=%d,exchange\_add\_coin=%d,all\_consume\_coin=%d,today\_balance\_coin=%d,VIP\_CONSUME\_COIN=  
  
  
%d,PROPS\_CONSUME\_COIN=%d where tongji\_time='%s'"%(record\['recharge'\],record\['allconsumepoint'\],record\['coinpoint'\],record\['vippoint'\],record\['coingmadd'\],record\['coinexchange'\],record\['allconsumecoin'\],record\['cointodaylast'\],record  
  
  
\['coinreduce'\],record\['coinprops'\],fmt\_day))  
  
  
        yesterday = time.strftime('%Y%m%d', time.localtime(time.mktime(time.strptime(cur\_day, '%Y%m%d'))-86400))  
  
  
        last\_day = time.strftime('%Y-%m-%d', time.localtime(time.mktime(time.strptime(yesterday, '%Y%m%d'))))  
  
  
        last\_day\_coin\_last = 0  
        sql ="SELECT all\_balance\_coin from TRANSACTIONSUMMARY where tongji\_time='%s'"%last\_day  
        db\_cursor.execute(sql)  
        temp=db\_cursor.fetchone()  
        if(temp != None):  
                last\_day\_coin\_last=int(temp\[0\])  
        print '昨天:%s 人民币历史结余:'%last\_day,last\_day\_coin\_last  
        record\['coinhistorylast'\]= last\_day\_coin\_last+record\['cointodaylast'\]  
        print '人民币历史结余:',record\['coinhistorylast'\]  
  
  
        db\_conn.query("update TRANSACTIONSUMMARY set all\_balance\_coin=%d where tongji\_time='%s'"%(record\['coinhistorylast'\],fmt\_day))  
        db\_conn.commit()  
  
  
        \#创建交易明细表  
  
  
        tabletime = time.strftime("%Y%m%d", time.localtime(time.mktime(time.strptime(cur\_day, "%Y%m%d"))))  
  
  
        sql="CREATE TABLE IF NOT EXISTS \`TRANSACTIONDETAILS\_%s\` like TRANSACTIONDETAILS\_SAMPLE"%tabletime  
  
  
        db\_conn.query(sql)  
        db\_conn.query("delete from TRANSACTIONDETAILS\_%s"%tabletime)  
  
  
        \#充值明细  
        filename='/tmp/recharge.txt'  
        a\_file = open(filename, 'r')  
        for a\_line in a\_file.readlines():  
                m = re.search("^(\\S+) Bill\\\[\\d+\\\] TRACE: \\\[点数统计\\\]充值点数,用户:(\\d+),点数:(\\d+)", a\_line)  
                if m:  
                        db\_conn.query("insert into TRANSACTIONDETAILS\_%s(userid,occur\_time,type,point\_add) values(%d,'%s',0,%d)"%(tabletime,int(m.group(2)),str(m.group(1)),int(m.group(3))))  
  
  
        a\_file.close()  
  
  
        \#点数兑换人民币明细  
        filename='/tmp/coinpoint.txt'  
        a\_file = open(filename, 'r')  
        for a\_line in a\_file.readlines():  
                m = re.search("^(\\S+) Bill\\\[\\d+\\\] TRACE: \\\[点数统计\\\]人民币消耗点数,用户:(\\d+),点数:(\\d+),产生人民币:(\\d+)", a\_line)  
                if m:  
                        db\_conn.query("insert into TRANSACTIONDETAILS\_%s (userid,occur\_time,type,point\_reduce,coin\_add) values(%d,'%s',1,%d,%d)"%(tabletime,int(m.group(2)),str(m.group(1)),int(m.group(3)),int(m.group(4))))  
  
  
        a\_file.close()  
  
  
        \#点数兑换会员明细  
        filename='/tmp/vippoint.txt'  
        a\_file = open(filename, 'r')  
        for a\_line in a\_file.readlines():  
                m = re.search("^(\\S+) Bill\\\[\\d+\\\] TRACE: \\\[点数统计\\\]会员消耗点数,用户:(\\d+),点数:(\\d+)", a\_line)  
                if m:  
                        db\_conn.query("insert into TRANSACTIONDETAILS\_%s (userid,occur\_time,type,point\_reduce) values(%d,'%s',2,%d)"%(tabletime,int(m.group(2)),str(m.group(1)),int(m.group(3))))  
        a\_file.close()  
  
  
        \#人民币兑换会员明细  
        filename='/tmp/coinreduce.txt'  
        a\_file = open(filename, 'r')  
        for a\_line in a\_file.readlines():  
                m = re.search("^(\\S+) Bill\\\[\\d+\\\] TRACE: \\\[人民币统计\\\]会员消耗人民币,用户:(\\d+),人民币:(\\d+)", a\_line)  
                if m:  
                        db\_conn.query("insert into TRANSACTIONDETAILS\_%s (userid,occur\_time,type,coin\_reduce) values(%d,'%s',3,%d)"%(tabletime,int(m.group(2)),str(m.group(1)),int(m.group(3))))  
        a\_file.close()  
  
  
        \#GM增加人民币明细  
        filename='/tmp/coingmadd.txt'  
        a\_file = open(filename, 'r')  
        for a\_line in a\_file.readlines():  
                m = re.search("^(\\S+) Bill\\\[\\d+\\\] TRACE: \\\[人民币统计\\\]GM增加人民币,用户:(\\d+),人民币:(\\d+)", a\_line)  
                if m:  
                        db\_conn.query("insert into TRANSACTIONDETAILS\_%s (userid,occur\_time,type,coin\_add) values(%d,'%s',4,%d)"%(tabletime,int(m.group(2)),str(m.group(1)),int(m.group(3))))  
  
  
        a\_file.close()  
        db\_conn.commit()  
        db\_cursor.close()  
        db\_conn.close()  
main()  
\#if \_\_name\_\_ == "\_\_main\_\_":  
\#        main()  
  
  
  
  
  
  
==========================================================================================================================================================  
cat vipuserflowermonthly.py  
\#!/usr/bin/env python  
\#-\*-coding:utf-8-\*-  
\#会员数据统计  
import MySQLdb  
import os, sys, re, string  
import time, getopt  
  
  
optmap = \{   
                'dbuser' : 'haoren',  
                'dbpass' : 'hjkdhskjsh',  
                'dbhost' : '172.16.1.8',  
                'dbport' : 3306,  
                'dbname' : 'AMDB',  
                \#'logdir' : '/home/haoren/log/',                         \#内网环境日志目录  
                'logdir' : '/home/haoren/logdir/',                      \#外网环境日志目录  
                \#'logpattern' : '^chat\_flowerserver.log.',      \#内网环境日志名称前缀  
                'logpattern' : '^flowerserver.log.'             \#外网环境日志名称前缀  
                \}  
  
  
def get\_files(dir, pattern):  
        print dir, pattern  
        match\_file\_list = \[\]  
        if os.path.exists(dir):  
                cur\_file\_list = os.listdir(dir)  
                for file\_name in cur\_file\_list:  
                        if re.search(pattern, file\_name):  
                                match\_file\_list.append(file\_name)  
                return match\_file\_list  
        else:  
                return 'no'  
  
  
def main():  
        one\_day = time.strftime("%Y%m%d", time.localtime(time.time() - 24\*60\*60))       \#默认日期为脚本运行的上一天  
        opts, args = getopt.getopt(sys.argv\[1:\], 'd:')  
        for op, value in opts:  
                if op == '-d':  
                        m = re.search('\[0-9\]\{8\}', value)  
                        if m:  
                                one\_day = value  
                        else:  
                                print "请输入8位日期(比如：20130215)"  
                                return 'no'  
  
  
        print "正在读取VIP用户食用油数据(%s)..." %one\_day  
        db\_conn = MySQLdb.connect(user=optmap\['dbuser'\], passwd=optmap\['dbpass'\], host=optmap\['dbhost'\], port=optmap\['dbport'\], db=optmap\['dbname'\])  
        db\_cursor = db\_conn.cursor()  
  
  
        temp\_vip\_user\_flower\_file\_name = '/tmp/vipuserflowermonthlytemp.txt'  
        command = "cat /dev/null > %s" %(temp\_vip\_user\_flower\_file\_name)  
        os.system(command)  
  
  
        if re.search('haoren', optmap\['logdir'\]):  
                print '外网环境'  
                log\_dir\_name\_list = get\_files(optmap\['logdir'\], one\_day\[2:\])  
                for log\_dir\_name\_item in log\_dir\_name\_list:  
                        log\_dir\_full\_path = optmap\['logdir'\]+log\_dir\_name\_item+'/'  
                        log\_file\_name\_list = get\_files(log\_dir\_full\_path, optmap\['logpattern'\] + one\_day\[2:\])  
                        for log\_file\_name\_item in log\_file\_name\_list:  
                                print log\_file\_name\_item  
                                command = "cat %s%s |awk '/\\\[flower统计\\\]/' >> %s" % (log\_dir\_full\_path, log\_file\_name\_item, temp\_vip\_user\_flower\_file\_name)  
                                os.system(command)  
        else:  
                print '内网环境'  
                log\_file\_name\_list = get\_files(optmap\['logdir'\], optmap\['logpattern'\] + one\_day\[2:\])  
                for log\_file\_name\_item in log\_file\_name\_list:  
                        command = "cat %s%s |awk '/\\\[flower统计\\\]/' >> %s" % (optmap\['logdir'\], log\_file\_name\_item, temp\_vip\_user\_flower\_file\_name)  
                        os.system(command)  
  
  
        command = "cat %s |wc -l" %temp\_vip\_user\_flower\_file\_name  
        os.system(command)  
  
  
        today\_flower\_new\_num\_map = \{\}  
        today\_flower\_new\_num\_map\[0\] = 0  
        today\_flower\_new\_num\_map\[1\] = 0  
        today\_flower\_new\_num\_map\[2\] = 0  
        today\_flower\_new\_num\_map\[3\] = 0  
        today\_flower\_new\_num\_map\[11\] = 0  
        today\_flower\_new\_num\_map\[12\] = 0  
        today\_flower\_new\_num\_map\[13\] = 0  
  
  
        today\_flower\_spent\_num\_map = \{\}  
        today\_flower\_spent\_num\_map\[0\] = 0  
        today\_flower\_spent\_num\_map\[1\] = 0  
        today\_flower\_spent\_num\_map\[2\] = 0  
        today\_flower\_spent\_num\_map\[3\] = 0  
        today\_flower\_spent\_num\_map\[11\] = 0  
        today\_flower\_spent\_num\_map\[12\] = 0  
        today\_flower\_spent\_num\_map\[13\] = 0  
  
  
        temp\_vip\_user\_flower\_file = open(temp\_vip\_user\_flower\_file\_name)  
        for one\_line in temp\_vip\_user\_flower\_file.readlines():  
\#新增食用油  
                match = re.search("^(\\S+) FlowerServer\\\[\\d+\\\]  INFO: \\\[flower统计\\\]\\\[basketflower\\\]用户\$(\\d+),(\\d+)\$累积食用油: basketflower=(\\d+)", one\_line)  
                if match:  
                        today\_flower\_new\_num\_map\[string.atoi(match.group(2))\] += 1  
                        continue  
  
  
                match = re.search("^(\\S+) FlowerServer\\\[\\d+\\\]  INFO: \\\[flower统计\\\]\\\[basketflower\\\]\\\[vip\\\]用户\$(\\d+),(\\d+)\$花篮信息更新导致填满花篮: (\\d+)->(\\d+)", one\_line)  
                if match:  
                        today\_flower\_new\_num\_map\[string.atoi(match.group(2))\] += string.atoi(match.group(5)) - string.atoi(match.group(4))  
                        continue  
  
  
                match = re.search("^(\\S+) FlowerServer\\\[\\d+\\\]  INFO: \\\[flower统计\\\]\\\[flowerload\\\]增加用户\$(\\d+),(\\d+)\$的花篮食用油:实际增加(\\d+)朵", one\_line)  
                if match:  
                        today\_flower\_new\_num\_map\[string.atoi(match.group(2))\] += string.atoi(match.group(4))  
                        continue  
  
  
                match = re.search("^(\\S+) FlowerServer\\\[\\d+\\\]  INFO: \\\[flower统计\\\]\\\[present\\\]用户\$(\\d+),(\\d+)\$赠送(\\d+)朵食用油(\\S+)给(\\d+)失败...\$实际补偿(\\d+)朵,补偿后共(\\d+)朵\$", one\_line)  
                if match:  
                        today\_flower\_new\_num\_map\[string.atoi(match.group(2))\] += string.atoi(match.group(7))  
                        continue  
  
  
                match = re.search("^(\\S+) FlowerServer\\\[\\d+\\\]  INFO: \\\[flower统计\\\]\\\[gm\\\]增加用户\$(\\d+),(\\d+)\$花篮(\\d+)朵食用油: basketflower=(\\d+)", one\_line)  
                if match:  
                        today\_flower\_new\_num\_map\[string.atoi(match.group(2))\] += string.atoi(match.group(4))  
                        continue  
  
  
\#消耗食用油  
                match = re.search("^(\\S+) FlowerServer\\\[\\d+\\\]  INFO: \\\[flower统计\\\]\\\[present\\\]用户\$(\\d+),(\\d+)\$赠送(\\d+)朵花给(\\d+)(\\S+).\$剩余basketflower=(\\d+)\$", one\_line)  
                if match:  
                        today\_flower\_spent\_num\_map\[string.atoi(match.group(2))\] += string.atoi(match.group(4))  
                        continue  
  
  
                match = re.search("^(\\S+) FlowerServer\\\[\\d+\\\]  INFO: \\\[flower统计\\\]\\\[basketflower\\\]\\\[vip\\\]用户\$(\\d+),(\\d+)\$花篮信息更新导致花篮食用油下调为花篮上限: (\\d+)->(\\d+)", one\_line)  
                if match:  
                        today\_flower\_spent\_num\_map\[string.atoi(match.group(2))\] += string.atoi(match.group(4)) - string.atoi(match.group(5))  
                        continue  
  
  
                match = re.search("^(\\S+) FlowerServer\\\[\\d+\\\]  INFO: \\\[flower统计\\\]\\\[gm\\\]减少用户\$(\\d+),(\\d+)\$花篮(\\d+)朵食用油: basketflower=(\\d+)", one\_line)  
                if match:  
                        today\_flower\_spent\_num\_map\[string.atoi(match.group(2))\] += string.atoi(match.group(4))  
                        continue  
  
  
        temp\_vip\_user\_flower\_file.close()  
  
  
        dword\_time = time.mktime(time.strptime(one\_day, '%Y%m%d'))  
        db\_conn.query("use %s" %optmap\['dbname'\])  
  
  
        sql = "delete from VIPUSERFLOWERMONTHLY where count\_time='%d'" %dword\_time  
        print sql  
        db\_conn.query(sql)  
  
  
        sql = "insert into VIPUSERFLOWERMONTHLY (count\_time) values('%d')" %(dword\_time)  
        print sql  
        db\_conn.query(sql)  
  
  
        sql = "update VIPUSERFLOWERMONTHLY set today\_year\_flower\_new\_num=%d, today\_month\_flower\_new\_num=%d, today\_week\_flower\_new\_num=%d, today\_other\_flower\_new\_num=%d, today\_year\_flower\_spent\_num=%d, today\_month\_flower\_spent\_num=%d,   
  
  
today\_week\_flower\_spent\_num=%d, today\_other\_flower\_spent\_num=%d where count\_time='%d'" %(today\_flower\_new\_num\_map\[3\], today\_flower\_new\_num\_map\[2\], today\_flower\_new\_num\_map\[1\], today\_flower\_new\_num\_map\[0\] + today\_flower\_new\_num\_map\[11\] +   
  
  
today\_flower\_new\_num\_map\[12\] + today\_flower\_new\_num\_map\[13\], today\_flower\_spent\_num\_map\[3\], today\_flower\_spent\_num\_map\[2\], today\_flower\_spent\_num\_map\[1\], today\_flower\_spent\_num\_map\[0\] + today\_flower\_spent\_num\_map\[11\] +   
  
  
today\_flower\_spent\_num\_map\[12\] + today\_flower\_spent\_num\_map\[13\], dword\_time)  
        print sql  
        db\_conn.query(sql)  
  
  
        db\_conn.commit()  
  
  
        db\_cursor.close()  
        db\_conn.close()  
  
  
main()  
\#if \_\_name\_\_ == "\_\_main\_\_"  
\#        main()  
  
  
  
  
  
  
===================================================================================================  
cat UserGoldConsumitemDaily.py  
\#!/usr/bin/env python  
\#-\*-coding:utf-8-\*-  
\#用户消费物品日报  
\#\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*使用说明\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
\# 内网测试通过命令行参数  
\# -d 指定统计时间  
\# -p 指定日志所在目录  
\# -t 指定临时文件存放目录  
\# 示例如下:  
\# \[haoren@localhost tongji\]$ ./userconsumitemDaily.py -d 20141112 -p /home/haoren/log/ -t /home/haoren/tongji/  
\# 外网无需加参数,使用默认值  
\#\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*使用说明\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
  
  
import MySQLdb  
import os, sys, re,string  
import time, tarfile,getopt  
  
  
optmap = \{   
                'dbuser' : 'haoren',  
                'dbpass' : 'hjkdhskjsh',  
                'dbhost' : '172.16.1.8',  
                'dbport' : 3306,  
                'dbname' : 'AMDB'  
                 \}  
def get\_files(dir, pattern):  
        res\_file\_list =\[\]  
        if os.path.exists(dir):  
                cur\_file\_list = os.listdir(dir)  
                for file\_name in cur\_file\_list:  
                        if re.search(pattern, file\_name):  
                                res\_file\_list.append(file\_name)  
                return res\_file\_list  
        else:  
                return 'no'  
  
  
def main():  
  
  
        \#外网环境默认参数  
        cur\_day = time.strftime("%Y%m%d", time.localtime(time.time()-86400))  
        log\_day = time.strftime('%y%m%d', time.localtime(time.mktime(time.strptime(cur\_day, '%Y%m%d'))))  
        logdirname = "/home/haoren/logdir/%s\_67"%log\_day  
        tmpdirname = "/tmp/"  
        logname = "billserver"  
  
  
        \#内网测试指定参数  
        opts, args = getopt.getopt(sys.argv\[1:\], 'd:p:t:')  
        for op, value in opts:  
                if op == '-d':  
                        m = re.search('\[0-9\]\{8\}', value)  
                        if m:  
                                cur\_day = value  
                                log\_suffix = time.strftime('%y%m%d', time.localtime(time.mktime(time.strptime(cur\_day, '%Y%m%d'))))  
                                logname = "billserver.log"  
                                log\_day = time.strftime('%y%m%d', time.localtime(time.mktime(time.strptime(cur\_day, '%Y%m%d'))))  
                                logdirname = "/home/haoren/logdir/%s\_67"%log\_day  
                        else:  
                                print "请输入8位日期(比如：20130215)"  
                                return 'no'  
                elif op == '-p':  
                        logdirname = str(value)  
                elif op == '-t':  
                        tmpdirname = str(value)  
  
  
        fmt\_day = time.strftime('%Y-%m-%d', time.localtime(time.mktime(time.strptime(cur\_day, '%Y%m%d'))))  
        d\_time = time.mktime(time.strptime(fmt\_day,'%Y-%m-%d'))  
        print '统计日期:',fmt\_day  
        print '日志名称:',logname  
        print '日志路径:',logdirname  
        print '临时文本:',tmpdirname  
  
  
        \#用户物品消费  
        if os.path.exists("%suserconsumeitem.txt"%tmpdirname):  
                os.system("rm -f %suserconsumeitem.txt"%tmpdirname)  
  
  
        file\_list2=get\_files(logdirname,logname)  
        for file2 in file\_list2:  
                command = "cat %s/%s | awk '/物品统计/'>> %suserconsumeitem.txt"%(logdirname,file2,tmpdirname)  
                os.system(command)  
  
  
        \#用户物品消费  
        filename='%suserconsumeitem.txt'%tmpdirname  
        a\_file = open(filename, 'r')  
        \#用户购买物品消耗总人民币  
        allcoin=\{\}  
        \#用户消费物品  
        userprops = \{\}  
        \#130815-15:01:06 Bill\[990\]  INFO: \[物品统计\]频道(210043)等级(9)用户(21000162)赠送(1)个物品(3)给客户(21000264),客户等级(1),签约(0), 消耗人民币(10), 客户获得人民币(470), 频道获得人民币(30)  
        for a\_line in a\_file.readlines():  
                m = re.search("^(\\S+) Bill\\\[\\d+\\\]  INFO: \\\[人民币消费物品统计\\\]频道\$(\\d+)\$等级\$(\\d+)\$用户\$(\\d+)\$赠送\$(\\d+)\$个物品\$(\\d+)\$给客户\$(\\d+)\$,客户等级\$(\\d+)\$,签约\$(\\d+)\$, 消耗人民币\$(\\d+)\$, 客户获得人民币\$(\\d  
  
  
\+)\$, 频道获得人民币\$(\\d+)\$", a\_line)  
                if m:  
                        userid = int(m.group(4))  
                        propnum = int(m.group(5))  
                        propid = int(m.group(6))  
                        coin = int(m.group(10))  
                        if (userid in userprops):  
                                if (propid in userprops\[userid\]):  
                                        userprops\[userid\]\[propid\]\['num'\] += propnum  
                                        userprops\[userid\]\[propid\]\['coin'\] += coin  
                                else:  
                                        userprops\[userid\]\[propid\] = \{\}  
                                        userprops\[userid\]\[propid\]\['num'\] = propnum  
                                        userprops\[userid\]\[propid\]\['coin'\] = coin  
  
  
                        else:  
                                userprops\[userid\]=\{\}  
                                userprops\[userid\]\[propid\] = \{\}  
                                userprops\[userid\]\[propid\]\['num'\] = propnum  
                                userprops\[userid\]\[propid\]\['coin'\] = coin  
  
  
        for key in userprops:  
                print '用户:',key  
                for prop in userprops\[key\]:  
                        print '物品id:', prop  
                        print '物品num:', userprops\[key\]\[prop\]\['num'\]  
                        print '物品消费人民币:', userprops\[key\]\[prop\]\['coin'\]  
  
  
        a\_file.close()  
  
  
        db\_conn = MySQLdb.connect(user=optmap\['dbuser'\], passwd=optmap\['dbpass'\], host=optmap\['dbhost'\], port=optmap\['dbport'\], db=optmap\['dbname'\])  
        db\_cursor=db\_conn.cursor()  
        db\_conn.query("use %s"%optmap\['dbname'\])  
        tabletime = time.strftime("%Y%m%d", time.localtime(time.mktime(time.strptime(cur\_day, "%Y%m%d"))))  
  
  
        sql="CREATE TABLE IF NOT EXISTS \`USERGOLDCONSUMEITEMDAILY\_%s\` like USERGOLDCONSUMEITEMDAILY\_SAMPLE"%tabletime  
  
  
        db\_conn.query(sql)  
        db\_conn.query("delete from USERGOLDCONSUMEITEMDAILY\_%s"%tabletime)  
        for key in userprops:  
                for kitem in userprops\[key\]:  
                        db\_conn.query("insert into USERGOLDCONSUMEITEMDAILY\_%s(USERID,CONSUMEITEMTOTALGOLD,ITEMID,ITEMNUM) values(%d,%d,%d,%d)"%(tabletime,int(key),int(userprops\[key\]\[kitem\]\['coin'\]),int(kitem),int(userprops\[key\]\[kitem\]  
  
  
\['num'\])))  
  
  
        db\_conn.commit()  
        db\_cursor.close()  
        db\_conn.close()  
main()  
\#if \_\_name\_\_ == "\_\_main\_\_":  
\#        main()  
  
  
  
  
====================================================================================================  
cat familyConsumeDaily.py  
\#!/usr/bin/env python  
\#-\*-coding:utf-8-\*-  
\#家族消费，集结统计脚本(修改为外网配置后，每天定时执行，统计数据)  
import MySQLdb  
import os, sys, re,string  
import time, tarfile,getopt  
  
  
optmap = \{   
                'dbuser' : 'haoren',  
                'dbpass' : 'hjkdhskjsh',  
                'dbhost' : '172.16.1.8',  
                'dbport' : 3306,  
                'dbname' : 'AMDB'  
                 \}  
                   
def get\_files(dir, pattern):  
        res\_file\_list =\[\]  
        if os.path.exists(dir):  
                cur\_file\_list = os.listdir(dir)  
                cur\_file\_list.sort()  
                for file\_name in cur\_file\_list:  
                        if re.search(pattern, file\_name):  
                                res\_file\_list.append(file\_name)  
                return res\_file\_list  
        else:  
                return 'no'  
  
  
def main():  
  
  
        cur\_day = time.strftime("%Y%m%d", time.localtime(time.time()-86400))  
        \# cur\_day = time.strftime("%Y%m%d", time.localtime(time.time()))  
  
  
        opts, args = getopt.getopt(sys.argv\[1:\], 'd:')  
        for op, value in opts:  
                if op == '-d':  
                        m = re.search('\[0-9\]\{8\}', value)  
                        if m:  
                                cur\_day = value  
                        else:  
                                print "请输入8位日期(比如：20130215)"  
                                return 'no'  
  
  
        fmt\_day = time.strftime('%Y-%m-%d', time.localtime(time.mktime(time.strptime(cur\_day, '%Y%m%d'))))  
        print '统计日期:',fmt\_day  
        log\_day = time.strftime('%y%m%d', time.localtime(time.mktime(time.strptime(cur\_day, '%Y%m%d'))))  
        dirname="/home/haoren/logdir/%s\_124/"%log\_day  
        print dirname  
        dirname1="/home/haoren/logdir/%s\_26/"%log\_day  
        print dirname1  
  
  
        \# 删除存在的临时文件  
        if os.path.exists("/tmp/familyConsumeDaily.txt"):  
                os.system("rm -f /tmp/familyConsumeDaily.txt")  
  
  
        if os.path.exists("/tmp/familyGatherDaily.txt"):  
                os.system("rm -f /tmp/familyGatherDaily.txt")  
  
  
        file\_list=get\_files(dirname,'groupserver')  
        for file in file\_list:  
                command = "cat %s/%s | grep 家族集结|grep 发起家族|grep 集结成功>> /tmp/familyGatherDaily.txt"%(dirname,file)  
                os.system(command)  
                command = "cat %s/%s | grep 家族消费|grep 同步家族|grep 成员|grep 消费>> /tmp/familyConsumeDaily.txt"%(dirname,file)  
                os.system(command)  
  
  
        file\_list1=get\_files(dirname1,'groupserver')  
        for file1 in file\_list1:  
                command = "cat %s/%s | grep 家族集结|grep 发起家族|grep 集结成功>> /tmp/familyGatherDaily.txt"%(dirname1,file1)  
                os.system(command)  
                command = "cat %s/%s | grep 家族消费|grep 同步家族|grep 成员|grep 消费>> /tmp/familyConsumeDaily.txt"%(dirname1,file1)  
                os.system(command)  
  
  
        \#家族消费  
        filename='/tmp/familyConsumeDaily.txt'  
        a\_file = open(filename, 'r')  
        \#家族消费统计  
        familyConsume=\{\}  
        \#150215-22:47:18 GROUPSERVER\[4401\]  INFO: \[家族消费\]同步家族(200105)成员(21001367)消费(2000)  
        for a\_line in a\_file.readlines():  
                m = re.search("^(\\S+) GROUPSERVER\\\[\\d+\\\]  INFO: \\\[家族消费\\\]同步家族\$(\\d+)\$成员\$(\\d+)\$消费\$(\\d+)\$", a\_line)  
                if(m and int(m.group(2))!=0):  
                        \# print m.group(2), m.group(4)  
                        if(familyConsume.has\_key(int(m.group(2)))):  
                                familyConsume\[int(m.group(2))\] += int(m.group(4))  
                        else:  
                                familyConsume\[int(m.group(2))\] = int(m.group(4))  
  
  
        a\_file.close()  
  
  
        \#家族集结  
        filename='/tmp/familyGatherDaily.txt'  
        a\_file = open(filename, 'r')  
        \#家族集结统计  
        familyGather=\{\}  
        for a\_line in a\_file.readlines():  
                \#150117-23:29:23 GROUPSERVER\[4400\]  INFO: \[家族集结\]用户(%u)发起家族(%u)集结成功，免费  
                \#150117-23:29:23 GROUPSERVER\[4400\]  INFO: \[家族集结\]用户(%u)发起家族(%u)集结成功，扣费成功  
                m = re.search("^(\\S+) GROUPSERVER\\\[\\d+\\\]  INFO: \\\[家族集结\\\]用户\$(\\d+)\$发起家族\$(\\d+)\$集结成功", a\_line)  
                if(m):  
                        \# print m.group(2), m.group(3)  
                        if(familyGather.has\_key(int(m.group(3)))):  
                                familyGather\[int(m.group(3))\] += 1  
                        else:  
                                familyGather\[int(m.group(3))\] = 1  
  
  
        a\_file.close()  
  
  
        db\_conn = MySQLdb.connect(user=optmap\['dbuser'\], passwd=optmap\['dbpass'\], host=optmap\['dbhost'\], port=optmap\['dbport'\], db=optmap\['dbname'\])  
        db\_cursor=db\_conn.cursor()  
        db\_conn.query("use %s"%optmap\['dbname'\])  
        timeday = int(time.mktime(time.strptime(cur\_day, "%Y%m%d")))  
  
  
        for key in familyConsume:  
                \# print key, familyConsume\[key\]  
                db\_conn.query("insert into FAMILY\_DAILYCONSUMESTAT(FAMILYID, CONSUME, TIME) values(%d, %d, %d)"%(int(key), int(familyConsume\[key\]), int(string.atoi(cur\_day, 10))))  
  
  
        for key in familyGather:  
                \# print key, familyGather\[key\]  
                db\_conn.query("insert into FAMILY\_DAILYGATHERTIMES(FAMILYID, GATHERTIMES, TIME) values(%d, %d, %d)"%(int(key), int(familyGather\[key\]), int(string.atoi(cur\_day, 10))))  
  
  
        db\_conn.commit()  
        db\_cursor.close()  
        db\_conn.close()  
main()  
  
  
=========================================================================================================  
 cat platformincomemonthly.py  
\#!/usr/bin/env python  
\#-\*-coding:utf-8-\*-  
\#平台收入月报  
\#\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*使用说明\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
\# 内网测试通过命令行参数  
\# -d 指定统计时间  
\# -p 指定日志所在目录  
\# -t 指定临时文件存放目录  
\# 示例如下:  
\# \[haoren@localhost tongji\]$ ./newplatformincomemonthly.py -d 20141112 -p /home/haoren/log/ -t /home/haoren/tongji/  
\# 外网无需加参数,使用默认值  
\#\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*使用说明\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
  
  
import MySQLdb  
import os, sys, re,string  
import time, tarfile,getopt  
  
  
optmap = \{   
                'dbuser' : 'haoren',  
                'dbpass' : 'hjkdhskjsh',  
                'dbhost' : '172.16.1.8',  
                'dbport' : 3306,  
                'dbname' : 'AMDB'  
                 \}  
def get\_files(dir, pattern):  
        res\_file\_list =\[\]  
        if os.path.exists(dir):  
                cur\_file\_list = os.listdir(dir)  
                for file\_name in cur\_file\_list:  
                        if re.search(pattern, file\_name):  
                                res\_file\_list.append(file\_name)  
                return res\_file\_list  
        else:  
                return 'no'  
  
  
def main():  
  
  
        \#外网环境默认参数  
        cur\_day = time.strftime("%Y%m%d", time.localtime(time.time()-86400))  
        log\_day = time.strftime('%y%m%d', time.localtime(time.mktime(time.strptime(cur\_day, '%Y%m%d'))))  
        logdirname = "/home/haoren/logdir/%s\_67"%log\_day  
        tmpdirname = "/tmp/"  
        logname = "billserver"  
  
  
        \#内网测试指定参数  
        opts, args = getopt.getopt(sys.argv\[1:\], 'd:p:t:')  
        for op, value in opts:  
                if op == '-d':  
                        m = re.search('\[0-9\]\{8\}', value)  
                        if m:  
                                cur\_day = value  
                                log\_suffix = time.strftime('%y%m%d', time.localtime(time.mktime(time.strptime(cur\_day, '%Y%m%d'))))  
                                logname = "billserver.log.%s"%log\_suffix  
                                log\_day = time.strftime('%y%m%d', time.localtime(time.mktime(time.strptime(cur\_day, '%Y%m%d'))))  
                                logdirname = "/home/haoren/logdir/%s\_67"%log\_day  
                        else:  
                                print "请输入8位日期(比如：20130215)"  
                                return 'no'  
                elif op == '-p':  
                        logdirname = str(value)  
                elif op == '-t':  
                        tmpdirname = str(value)  
  
  
        fmt\_day = time.strftime('%Y-%m-%d', time.localtime(time.mktime(time.strptime(cur\_day, '%Y%m%d'))))  
        d\_time = time.mktime(time.strptime(fmt\_day,'%Y-%m-%d'))  
        print '统计日期:',fmt\_day  
        print '日志名称:',logname  
        print '日志路径:',logdirname  
        print '临时文本:',tmpdirname  
  
  
        \#客户收入  
        if os.path.exists("%splatformincome.txt"%tmpdirname):  
                os.system("rm -f %splatformincome.txt"%tmpdirname)  
  
  
        \#提现统计  
        if os.path.exists("%swithdraw.txt"%tmpdirname):  
                os.system("rm -f %swithdraw.txt"%tmpdirname)  
  
  
        file\_list2=get\_files(logdirname, logname)  
        for file2 in file\_list2:  
                command = "cat %s/%s | awk '/物品统计/'>> %splatformincome.txt"%(logdirname,file2,tmpdirname)  
                os.system(command)  
                command = "cat %s/%s | awk '/提现统计/'>> %swithdraw.txt"%(logdirname,file2,tmpdirname)  
                os.system(command)  
  
  
        \#客户收入  
        filename='%splatformincome.txt'%tmpdirname  
        a\_file = open(filename, 'r')  
        \#客户消耗用户总人民币  
        record=\{\}  
        \#物品  
        props = \{\}  
  
  
        \#送物品人  
        people = \{\}  
  
  
        \#130815-15:01:06 Bill\[990\]  INFO: \[物品统计\]频道(210043)等级(9)用户(21000162)赠送(1)个物品(3)给客户(21000264),客户等级(1),签约(0), 消耗人民币(10), 客户获得人民币(470), 频道获得人民币(30)  
        for a\_line in a\_file.readlines():  
                m = re.search("^(\\S+) Bill\\\[\\d+\\\]  INFO: \\\[物品统计\\\]频道\$(\\d+)\$等级\$(\\d+)\$用户\$(\\d+)\$赠送\$(\\d+)\$个物品\$(\\d+)\$给客户\$(\\d+)\$,客户等级\$(\\d+)\$,签约\$(\\d+)\$, 消耗人民币\$(\\d+)\$, 客户获得人民币\$(\\d+)\$, 频道获  
  
  
得金币\$(\\d+)\$", a\_line)  
                if m:  
                        userid = int(m.group(4))  
                        propnum = int(m.group(5))  
                        propid = int(m.group(6))  
                        coin = int(m.group(10))  
                        singergold = int(m.group(11))  
                        channelgold = int(m.group(12))  
  
  
                        if (propid in props):  
                                props\[propid\]\['allcoin'\] += coin                        \#总人民币  
                                props\[propid\]\['allgold'\] = props\[propid\]\['allgold'\] + singergold + channelgold          \#总人民币  
                                props\[propid\]\['num'\] += propnum  
                        else:  
                                props\[propid\] = \{\}  
                                props\[propid\]\['allcoin'\] = coin  
                                props\[propid\]\['allgold'\] = singergold + channelgold  
                                props\[propid\]\['num'\] = propnum  
  
  
                        if(userid not in people):                       \#送物品人数  
                                people\[userid\] = 1  
  
  
        \#当日消耗人民币  
        totalgold = 0  
        for key in props:  
                totalgold += props\[key\]\['allgold'\]  
                print '物品Id:', key  
                print '物品收入:',props\[key\]\['allcoin'\]  
                print '人民币产出:',props\[key\]\['allgold'\]  
                print '物品数量:',props\[key\]\['num'\]  
        print '赠送物品用户:',len(people)  
  
  
        a\_file.close()  
  
  
  
  
        \#提现统计  
        filename='%swithdraw.txt'%tmpdirname  
        a\_file = open(filename, 'r')  
        \#客户总人民币  
        record\['withdraw'\] = 0  
        record\['cash'\] = 0  
        \#130816-17:22:14 Bill\[990\]  INFO: \[提现统计\]用户(21001359)客户等级(4)签约频道(0)频道等级(0)提现(121)元消耗人民币(50), 剩余人民币(100)  
        for a\_line in a\_file.readlines():  
                m = re.search("^(\\S+) Bill\\\[\\d+\\\]  INFO: \\\[提现统计\\\]用户\$(\\d+)\$客户等级\$(\\d+)\$签约频道\$(\\d+)\$频道等级\$(\\d+)\$提现\$(\\d+)\$元消耗人民币\$(\\d+)\$, 剩余人民币\$(\\d+)\$", a\_line)  
                if m:  
                        record\['withdraw'\] += int(m.group(7))  
                        record\['cash'\] += int(m.group(6))  
  
  
        print'客户提现人民币:%u'%record\['withdraw'\]  
        print'客户提现人民币:%u'%record\['cash'\]  
  
  
        a\_file.close()  
        print '今日结余:%d'%(totalgold-record\['withdraw'\])  
  
  
  
  
        db\_conn = MySQLdb.connect(user=optmap\['dbuser'\], passwd=optmap\['dbpass'\], host=optmap\['dbhost'\], port=optmap\['dbport'\], db=optmap\['dbname'\])  
        db\_cursor=db\_conn.cursor()  
        db\_conn.query("use %s"%optmap\['dbname'\])  
  
  
        d\_time = time.mktime(time.strptime(cur\_day,'%Y%m%d'))  
        last\_day = d\_time-86400  
  
  
        db\_conn.query("delete from PLATFORMINCOMEMONTHLY where TIME=%u"%d\_time)  
  
  
        sql ="SELECT CURGOLD from PLATFORMINCOMEMONTHLY where TIME=%u limit 1"%last\_day  
        last\_day\_curgold = 0  
        db\_cursor.execute(sql)  
        temp=db\_cursor.fetchone()  
        if(temp != None):  
                last\_day\_curgold = int(temp\[0\])  
        else:  
                sql ="SELECT CURGOLD from ITEMEARNINGMONTHLY where TIME=%u"%last\_day  
                db\_cursor.execute(sql)  
                temp=db\_cursor.fetchone()  
                if(temp != None):  
                        last\_day\_curgold = int(temp\[0\])  
  
  
  
  
        print '昨天:%d 人民币结余:%d'%(last\_day,last\_day\_curgold)  
  
  
        record\['curgold'\]= last\_day\_curgold + totalgold -record\['withdraw'\]  
  
  
        print '平台累加人民币结余:',record\['curgold'\]  
  
  
        for kitem in props:  
                db\_conn.query("insert into PLATFORMINCOMEMONTHLY(TIME,CONSUMEITEMTOTALCOIN,ADDGOLD,CURGOLD,SINGERCASH,SINGERCONSUMEGOLD,PRESENTUSERNUM,ITEMID,ITEMNUM) values(%d,%d,%d,%d,%d,%d,%d,%d,%d)"%(d\_time, props\[kitem\]  
  
  
\['allcoin'\],props\[kitem\]\['allgold'\],record\['curgold'\],record\['cash'\],record\['withdraw'\],len(people),int(kitem), int(props\[kitem\]\['num'\])))  
  
  
        db\_conn.commit()  
        db\_cursor.close()  
        db\_conn.close()  
main()  
\#if \_\_name\_\_ == "\_\_main\_\_":  
\#        main()  
  
  
  
  
  
  
  
  
  
  
=============================================================================================================  
 cat channelincomeDaily.py  
\#!/usr/bin/env python  
\#-\*-coding:utf-8-\*-  
\#频道收入日报  
\#\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*使用说明\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
\# 内网测试通过命令行参数  
\# -d 指定统计时间  
\# -p 指定日志所在目录  
\# -t 指定临时文件存放目录  
\# 示例如下:  
\# \[haoren@localhost tongji\]$ ./channelincomeDaily.py -d 20141112 -p /home/haoren/log/ -t /home/haoren/tongji/  
\# 外网无需加参数,使用默认值  
\#\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*使用说明\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
  
  
  
  
import MySQLdb  
import os, sys, re,string  
import time, tarfile,getopt  
  
  
optmap = \{   
                'dbuser' : 'haoren',  
                'dbpass' : 'hjkdhskjsh',  
                'dbhost' : '172.16.1.8',  
                'dbport' : 3306,  
                'dbname' : 'AMDB'  
                 \}  
                   
def get\_files(dir, pattern):  
        res\_file\_list =\[\]  
        if os.path.exists(dir):  
                cur\_file\_list = os.listdir(dir)  
                cur\_file\_list.sort()  
                for file\_name in cur\_file\_list:  
                        if re.search(pattern, file\_name):  
                                res\_file\_list.append(file\_name)  
                return res\_file\_list  
        else:  
                return 'no'  
  
  
def main():  
  
  
        \#外网环境默认参数  
        cur\_day = time.strftime("%Y%m%d", time.localtime(time.time()-86400))  
        log\_day = time.strftime('%y%m%d', time.localtime(time.mktime(time.strptime(cur\_day, '%Y%m%d'))))  
        logdirname = "/home/haoren/logdir/%s\_67"%log\_day  
        tmpdirname = "/tmp/"  
        logname = "billserver"  
  
  
        \#内网测试指定参数  
        opts, args = getopt.getopt(sys.argv\[1:\], 'd:p:t:')  
        for op, value in opts:  
                if op == '-d':  
                        m = re.search('\[0-9\]\{8\}', value)  
                        if m:  
                                cur\_day = value  
                                print cur\_day  
                                log\_suffix = time.strftime('%y%m%d', time.localtime(time.mktime(time.strptime(cur\_day, '%Y%m%d'))))  
                                logname = "billserver.log"  
                                log\_day = time.strftime('%y%m%d', time.localtime(time.mktime(time.strptime(cur\_day, '%Y%m%d'))))  
                                logdirname = "/home/haoren/logdir/%s\_67"%log\_day  
                        else:  
                                print "请输入8位日期(比如：20130215)"  
                                return 'no'  
                elif op == '-p':  
                        logdirname = str(value)  
                elif op == '-t':  
                        tmpdirname = str(value)  
  
  
        fmt\_day = time.strftime('%Y-%m-%d', time.localtime(time.mktime(time.strptime(cur\_day, '%Y%m%d'))))  
        d\_time = time.mktime(time.strptime(fmt\_day,'%Y-%m-%d'))  
        print '统计日期:',fmt\_day  
        print '日志名称:',logname  
        print '日志路径:',logdirname  
        print '临时文本:',tmpdirname  
  
  
        \#频道收入  
        if os.path.exists("%schannelincome.txt"%tmpdirname):  
                os.system("rm -f %schannelincome.txt"%tmpdirname)  
  
  
        \#频道人民币总量  
        if os.path.exists("%schannelallgold.txt"%tmpdirname):  
                os.system("rm -f %schannelallgold.txt"%tmpdirname)  
  
  
        file\_list=get\_files(logdirname, logname)  
        for file in file\_list:  
                command = "cat %s/%s | awk '/物品统计/'>> %schannelincome.txt"%(logdirname,file,tmpdirname)  
                os.system(command)  
                command = "cat %s/%s | awk '/频道人民币统计/'>> %schannelallgold.txt"%(logdirname,file,tmpdirname)  
                os.system(command)  
  
  
        \#频道收入  
        filename='%schannelincome.txt'%tmpdirname  
        a\_file = open(filename, 'r')  
        \#频道消耗用户总人民币  
        allcoin=\{\}  
        \#频道人民币收入  
        goldincome=\{\}  
        \#频道总人民币  
        channelallgold = \{\}  
        \#频道物品  
        channelprops = \{\}  
        \#130815-15:01:06 Bill\[990\]  INFO: \[物品统计\]频道(210043)等级(9)用户(21000162)赠送(1)个物品(3)给客户(21000264),客户等级(1),签约(0), 消耗人民币(10), 客户获得人民币(470), 频道获得人民币(30)  
        for a\_line in a\_file.readlines():  
                m = re.search("^(\\S+) Bill\\\[\\d+\\\]  INFO: \\\[物品统计\\\]频道\$(\\d+)\$等级\$(\\d+)\$用户\$(\\d+)\$赠送\$(\\d+)\$个物品\$(\\d+)\$给客户\$(\\d+)\$,客户等级\$(\\d+)\$,签约\$(\\d+)\$, 消耗人民币\$(\\d+)\$, 客户获得人民币\$(\\d+)\$, 频道获  
  
  
得金币\$(\\d+)\$", a\_line)  
                if m:  
                        channelid = int(m.group(2))  
                        propnum = int(m.group(5))  
                        propid = int(m.group(6))  
                        signflag = int(m.group(9))  
                        coin = int(m.group(10))  
                        gold = int(m.group(12))  
                        \#离线用户signflag传入的是频道号  
                        if (signflag != 0 and signflag != 1):  
                                if (signflag == int(m.group(2))):  
                                        signflag = 1  
                                else:  
                                        signflag = 0  
                        if (channelid in channelprops):  
                                if (propid in channelprops\[channelid\]):  
                                        channelprops\[channelid\]\[propid\] += propnum  
                                        allcoin\[channelid\]\[propid\] += coin  
                                        goldincome\[channelid\]\[propid\]\[signflag\] += gold  
                                else:  
                                        channelprops\[channelid\]\[propid\] = propnum  
                                        allcoin\[channelid\]\[propid\] = coin  
                                        goldincome\[channelid\]\[propid\] = \{\}  
                                        goldincome\[channelid\]\[propid\]\[0\] = 0  
                                        goldincome\[channelid\]\[propid\]\[1\] = 0  
                                        goldincome\[channelid\]\[propid\]\[signflag\] = gold  
  
  
                        else:  
                                channelprops\[channelid\]=\{\}  
                                channelprops\[channelid\]\[propid\] = propnum  
                                allcoin\[channelid\] = \{\}  
                                allcoin\[channelid\]\[propid\] = coin  
                                goldincome\[channelid\]=\{\}  
                                goldincome\[channelid\]\[propid\]=\{\}  
                                goldincome\[channelid\]\[propid\]\[0\]=0  
                                goldincome\[channelid\]\[propid\]\[1\]=0  
                                goldincome\[channelid\]\[propid\]\[signflag\]=int(m.group(12))  
  
  
        for key in channelprops:  
                print '频道:',key  
  
  
        a\_file.close()  
  
  
        \#频道总人民币统计  
  
  
        \#频道总人民币  
        filename='%schannelallgold.txt'%tmpdirname  
        a\_file = open(filename, 'r')  
        \#频道总人民币  
        allgold=\{\}  
        \#130816-15:28:45 Bill\[990\]  INFO: \[频道人民币统计\]频道(210043)当前人民币(308864653)  
        for a\_line in a\_file.readlines():  
                m = re.search("^(\\S+) Bill\\\[\\d+\\\]  INFO: \\\[频道人民币统计\\\]频道\$(\\d+)\$当前人民币\$(\\d+)\$", a\_line)  
                if m:  
                        allgold\[int(m.group(2))\] = int(m.group(3))  
  
  
        for key in allgold:  
                print'频道:%lu,人民币总量:%u'%(key,allgold\[key\])  
  
  
        a\_file.close()  
  
  
        db\_conn = MySQLdb.connect(user=optmap\['dbuser'\], passwd=optmap\['dbpass'\], host=optmap\['dbhost'\], port=optmap\['dbport'\], db=optmap\['dbname'\])  
        db\_cursor=db\_conn.cursor()  
        db\_conn.query("use %s"%optmap\['dbname'\])  
        tabletime = time.strftime("%Y%m%d", time.localtime(time.mktime(time.strptime(cur\_day, "%Y%m%d"))))  
  
  
        sql="CREATE TABLE IF NOT EXISTS \`CHANNELEARNINGDAILY\_%s\` like CHANNELEARNINGDAILY\_SAMPLE"%tabletime  
  
  
        db\_conn.query(sql)  
        db\_conn.query("delete from CHANNELEARNINGDAILY\_%s"%tabletime)  
        for key in channelprops:  
                if(key not in allgold):  
                        allgold\[key\]=0  
                for kitem in channelprops\[key\]:  
                        db\_conn.query("insert into CHANNELEARNINGDAILY\_%s(CHANNELID,RECVITEMTOTALCOIN,SIGNGOLD,NONSIGNGOLD,CURGOLD,ITEMID,ITEMNUM ) values(%d,%d,%d,%d,%d,%d,%d)"%(tabletime,int(key),int(allcoin\[key\]\[kitem\]),int  
  
  
(goldincome\[key\]\[kitem\]\[1\]),int(goldincome\[key\]\[kitem\]\[0\]),int(allgold\[key\]),int(kitem),int(channelprops\[key\]\[kitem\])))  
  
  
        db\_conn.commit()  
        db\_cursor.close()  
        db\_conn.close()  
main()  
\#if \_\_name\_\_ == "\_\_main\_\_":  
\#        main()  
  
  
  
  
  
  
=====================================================================================================================  
cat userconsumitemDaily.py  
\#!/usr/bin/env python  
\#-\*-coding:utf-8-\*-  
\#用户消费物品日报  
\#\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*使用说明\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
\# 内网测试通过命令行参数  
\# -d 指定统计时间  
\# -p 指定日志所在目录  
\# -t 指定临时文件存放目录  
\# 示例如下:  
\# \[haoren@localhost tongji\]$ ./userconsumitemDaily.py -d 20141112 -p /home/haoren/log/ -t /home/haoren/tongji/  
\# 外网无需加参数,使用默认值  
\#\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*使用说明\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
  
  
import MySQLdb  
import os, sys, re,string  
import time, tarfile,getopt  
  
  
optmap = \{   
                'dbuser' : 'haoren',  
                'dbpass' : 'hjkdhskjsh',  
                'dbhost' : '172.16.1.8',  
                'dbport' : 3306,  
                'dbname' : 'AMDB'  
                 \}  
def get\_files(dir, pattern):  
        res\_file\_list =\[\]  
        if os.path.exists(dir):  
                cur\_file\_list = os.listdir(dir)  
                for file\_name in cur\_file\_list:  
                        if re.search(pattern, file\_name):  
                                res\_file\_list.append(file\_name)  
                return res\_file\_list  
        else:  
                return 'no'  
  
  
def main():  
  
  
        \#外网环境默认参数  
        cur\_day = time.strftime("%Y%m%d", time.localtime(time.time()-86400))  
        log\_day = time.strftime('%y%m%d', time.localtime(time.mktime(time.strptime(cur\_day, '%Y%m%d'))))  
        logdirname = "/home/haoren/logdir/%s\_67"%log\_day  
        tmpdirname = "/tmp/"  
        logname = "billserver"  
  
  
        \#内网测试指定参数  
        opts, args = getopt.getopt(sys.argv\[1:\], 'd:p:t:')  
        for op, value in opts:  
                if op == '-d':  
                        m = re.search('\[0-9\]\{8\}', value)  
                        if m:  
                                cur\_day = value  
                                log\_suffix = time.strftime('%y%m%d', time.localtime(time.mktime(time.strptime(cur\_day, '%Y%m%d'))))  
                                logname = "billserver.log"  
                                log\_day = time.strftime('%y%m%d', time.localtime(time.mktime(time.strptime(cur\_day, '%Y%m%d'))))  
                                logdirname = "/home/haoren/logdir/%s\_67"%log\_day  
                        else:  
                                print "请输入8位日期(比如：20130215)"  
                                return 'no'  
                elif op == '-p':  
                        logdirname = str(value)  
                elif op == '-t':  
                        tmpdirname = str(value)  
  
  
        fmt\_day = time.strftime('%Y-%m-%d', time.localtime(time.mktime(time.strptime(cur\_day, '%Y%m%d'))))  
        d\_time = time.mktime(time.strptime(fmt\_day,'%Y-%m-%d'))  
        print '统计日期:',fmt\_day  
        print '日志名称:',logname  
        print '日志路径:',logdirname  
        print '临时文本:',tmpdirname  
  
  
        \#用户物品消费  
        if os.path.exists("%suserconsumeitem.txt"%tmpdirname):  
                os.system("rm -f %suserconsumeitem.txt"%tmpdirname)  
  
  
        file\_list2=get\_files(logdirname,logname)  
        for file2 in file\_list2:  
                command = "cat %s/%s | awk '/物品统计/'>> %suserconsumeitem.txt"%(logdirname,file2,tmpdirname)  
                os.system(command)  
  
  
        \#用户物品消费  
        filename='%suserconsumeitem.txt'%tmpdirname  
        a\_file = open(filename, 'r')  
        \#用户购买物品消耗总人民币  
        allcoin=\{\}  
        \#用户消费物品  
        userprops = \{\}  
        \#130815-15:01:06 Bill\[990\]  INFO: \[物品统计\]频道(210043)等级(9)用户(21000162)赠送(1)个物品(3)给客户(21000264),客户等级(1),签约(0), 消耗人民币(10), 客户获得人民币(470), 频道获得人民币(30)  
        for a\_line in a\_file.readlines():  
                m = re.search("^(\\S+) Bill\\\[\\d+\\\]  INFO: \\\[物品统计\\\]频道\$(\\d+)\$等级\$(\\d+)\$用户\$(\\d+)\$赠送\$(\\d+)\$个物品\$(\\d+)\$给客户\$(\\d+)\$,客户等级\$(\\d+)\$,签约\$(\\d+)\$, 消耗人民币\$(\\d+)\$, 客户获得人民币\$(\\d+)\$, 频道获  
  
  
得金币\$(\\d+)\$", a\_line)  
                if m:  
                        userid = int(m.group(4))  
                        propnum = int(m.group(5))  
                        propid = int(m.group(6))  
                        coin = int(m.group(10))  
                        if (userid in userprops):  
                                if (propid in userprops\[userid\]):  
                                        userprops\[userid\]\[propid\]\['num'\] += propnum  
                                        userprops\[userid\]\[propid\]\['coin'\] += coin  
                                else:  
                                        userprops\[userid\]\[propid\] = \{\}  
                                        userprops\[userid\]\[propid\]\['num'\] = propnum  
                                        userprops\[userid\]\[propid\]\['coin'\] = coin  
  
  
                        else:  
                                userprops\[userid\]=\{\}  
                                userprops\[userid\]\[propid\] = \{\}  
                                userprops\[userid\]\[propid\]\['num'\] = propnum  
                                userprops\[userid\]\[propid\]\['coin'\] = coin  
  
  
        for key in userprops:  
                print '用户:',key  
                for prop in userprops\[key\]:  
                        print '物品id:', prop  
                        print '物品num:', userprops\[key\]\[prop\]\['num'\]  
                        print '物品消费人民币:', userprops\[key\]\[prop\]\['coin'\]  
  
  
        a\_file.close()  
  
  
        db\_conn = MySQLdb.connect(user=optmap\['dbuser'\], passwd=optmap\['dbpass'\], host=optmap\['dbhost'\], port=optmap\['dbport'\], db=optmap\['dbname'\])  
        db\_cursor=db\_conn.cursor()  
        db\_conn.query("use %s"%optmap\['dbname'\])  
        tabletime = time.strftime("%Y%m%d", time.localtime(time.mktime(time.strptime(cur\_day, "%Y%m%d"))))  
  
  
        sql="CREATE TABLE IF NOT EXISTS \`USERCONSUMEITEMDAILY\_%s\` like USERCONSUMEITEMDAILY\_SAMPLE"%tabletime  
  
  
        db\_conn.query(sql)  
        db\_conn.query("delete from USERCONSUMEITEMDAILY\_%s"%tabletime)  
        for key in userprops:  
                for kitem in userprops\[key\]:  
                        db\_conn.query("insert into USERCONSUMEITEMDAILY\_%s(USERID,CONSUMEITEMTOTALCOIN,ITEMID,ITEMNUM) values(%d,%d,%d,%d)"%(tabletime,int(key),int(userprops\[key\]\[kitem\]\['coin'\]),int(kitem),int(userprops\[key\]\[kitem\]  
  
  
\['num'\])))  
  
  
        db\_conn.commit()  
        db\_cursor.close()  
        db\_conn.close()  
main()  
\#if \_\_name\_\_ == "\_\_main\_\_":  
\#        main()  
  
  
  
  
  
  
==============================================================================================================================  
cat checkchannelsettle.py  
\#!/usr/bin/env python  
\#-\*-coding:utf-8-\*-  
\#check channel settle data VS performer settle data  
\#\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*使用说明\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
\# 内网测试通过命令行参数  
\# -d 指定统计时间  
\# -p 指定日志所在目录  
\# -t 指定临时文件存放目录  
\# 示例如下:  
\# \[haoren@localhost tongji\]$ ./channelsettlement.py -b 20141225 -e 20150125  
\# 外网无需加参数,使用默认值  
\#\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*使用说明\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
  
  
  
  
import MySQLdb  
import os, sys, re,string  
import time, tarfile,getopt  
import datetime  
  
  
optmap = \{   
                'dbuser' : 'haoren',  
                'dbpass' : 'hjkdhskjsh',  
                'dbhost\_im' : '172.16.1.8',  
                'dbhost\_gm' : '172.16.1.8',  
                'dbport' : 3306,  
                'dbname\_im' : 'FMDB',  
                'dbname\_gm' : 'AMDB'  
                 \}  
                   
def get\_files(dir, pattern):  
        res\_file\_list =\[\]  
        if os.path.exists(dir):  
                cur\_file\_list = os.listdir(dir)  
                cur\_file\_list.sort()  
                for file\_name in cur\_file\_list:  
                        if re.search(pattern, file\_name):  
                                res\_file\_list.append(file\_name)  
                return res\_file\_list  
        else:  
                return 'no'  
  
  
def main():  
  
  
        \#获取频道结算起始时间,每月25日结算  
        cur = datetime.datetime.now()  
        cur\_day = cur.day  
        cur\_month = cur.month  
        cur\_year = cur.year  
        print '\*\*\*\*\*当前时间: %d-%d-%d'%(cur\_year, cur\_month, cur\_day)  
  
  
        begin\_day = cur\_day  
        begin\_month = cur\_month  
        begin\_year = cur\_year  
        if cur\_month == 1:  
                begin\_month = 12  
                begin\_year = cur\_year - 1  
        else:  
                begin\_month = cur\_month - 1  
  
  
        beginTime = datetime.datetime(begin\_year,begin\_month,begin\_day,0,0,0)  
        endTime = datetime.datetime(cur\_year,cur\_month,cur\_day,0,0,0)  
  
  
        \#内网测试指定参数  
        opts, args = getopt.getopt(sys.argv\[1:\], 'b:e:')  
        for op, value in opts:  
                if op == '-b':  
                        m = re.search('\[0-9\]\{8\}', value)  
                        if m:  
                                beginTime = datetime.datetime.strptime(value, '%Y%m%d')  
                elif op == '-e':  
                        m = re.search('\[0-9\]\{8\}', value)  
                        if m:  
                                endTime = datetime.datetime.strptime(value, '%Y%m%d')  
  
  
        beginTimeStamp = int(time.mktime(beginTime.timetuple()))  
        endTimeStamp = int(time.mktime(endTime.timetuple()))  
        print '\*\*\*\*\*结算开始时间 %s (%d)'%(beginTime,beginTimeStamp)  
        print '\*\*\*\*\*结算结束时间 %s (%d)'%(endTime,endTimeStamp)  
  
  
  
  
        \#获取公司化频道列表  
        db\_conn = MySQLdb.connect(user=optmap\['dbuser'\], passwd=optmap\['dbpass'\], host=optmap\['dbhost\_im'\], port=optmap\['dbport'\], db=optmap\['dbname\_im'\])  
        db\_cursor=db\_conn.cursor()  
        db\_conn.query("use %s"%optmap\['dbname\_im'\])  
        sql = "select \`CHANNELID\` from \`SPECIALCHANNEL\`"  
        channellist = \{\}  
        try:  
                db\_cursor.execute(sql)  
                db\_rows = db\_cursor.fetchall()  
                for row in db\_rows:  
                        channellist\[row\[0\]\] = 0  
        except:  
                print '查询失败:%s'%sql  
        \#获得各个频道的流水,以人民币结算  
        for channelid in channellist:  
                channellist\[channelid\] = \{\}  
                channellist\[channelid\]\['coin'\] = 0  
                channellist\[channelid\]\['rev'\] = 0  
                sql = "select SUM(CONSUMECOIN), SUM(CHANNELRECVGOLD) from ITEMCONSUMERECORD where CHANNELID=%d and OPTIME>=%d and OPTIME<%d"%(channelid,beginTimeStamp,endTimeStamp)  
                try:  
                        db\_cursor.execute(sql)  
                        consumecoin1 = db\_cursor.fetchone()  
                        if (consumecoin1 != None):  
                                channellist\[channelid\]\['coin'\] += consumecoin1\[0\]  
                                channellist\[channelid\]\['rev'\] += consumecoin1\[1\]  
                        else:  
                                print '没有符合条件的数据:%s'%sql  
                except:  
                        print '查询失败:%s'%sql  
  
  
                sql = "select SUM(CONSUMECOIN), SUM(CHANNELRECVGOLD) from REQSONGCONSUMERECORD where CHANNELID=%d and OPTIME>=%d and OPTIME<%d"%(channelid, beginTimeStamp, endTimeStamp)  
                try:  
                        db\_cursor.execute(sql)  
                        consumecoin2 = db\_cursor.fetchone()  
                        if (consumecoin2 != None):  
                                channellist\[channelid\]\['coin'\] += consumecoin2\[0\]  
                                channellist\[channelid\]\['rev'\] += consumecoin2\[1\]  
                        else:  
                                print '没有符合条件的数据:%s'%sql  
                except:  
                        print '查询失败:%s'%sql  
  
  
                print '\[频道(%d)流水(%d)收入(%d)\]'%(channelid,channellist\[channelid\]\['coin'\],channellist\[channelid\]\['rev'\])  
  
  
        singerlist = \{\}  
        for channelid in channellist:  
                sql = "select PERFORMERID from VCHANNELPERFORMER where CHANNELID=%u"%channelid  
                try:  
                        db\_cursor.execute(sql)  
                        result = db\_cursor.fetchall()  
                        for singer in result:  
                                singerlist\[singer\[0\]\] = \{\}  
                                singerlist\[singer\[0\]\]\[channelid\] = \{\}  
                                singerlist\[singer\[0\]\]\[channelid\]\['rev'\] = 0  
                                singerlist\[singer\[0\]\]\[channelid\]\['exp'\] = 0  
                except:  
                        print '查询失败:%s'%sql  
  
  
        for key in singerlist:  
                print key  
                for channel in singerlist\[key\]:  
                        sql = "select SUM(SINGERRECVGOLD) from ITEMCONSUMERECORD where SINGERID=%d and CHANNELID=%d and OPTIME>=%d and OPTIME<%d"%(key, channel, beginTimeStamp, endTimeStamp)  
                        try:  
                                db\_cursor.execute(sql)  
                                singerrev1 = db\_cursor.fetchone()  
                                if (singerrev1 != None):  
                                        singerlist\[key\]\[channel\]\['rev'\] += singerrev1\[0\]  
                                else:  
                                        print '没有符合条件的数据:%s'%sql  
                        except:  
                                print '查询失败:%s'%sql  
  
  
                        sql = "select SUM(SINGERRECVGOLD) from REQSONGCONSUMERECORD where SINGERID=%d and CHANNELID=%d and OPTIME>=%d and OPTIME<%d"%(key, channel, beginTimeStamp, endTimeStamp)  
                        try:  
                                db\_cursor.execute(sql)  
                                singerrev2 = db\_cursor.fetchone()  
                                if (singerrev2 != None):  
                                        singerlist\[key\]\[channel\]\['rev'\] += singerrev2\[0\]  
                                else:  
                                        print '没有符合条件的数据:%s'%sql  
                        except:  
                                print '查询失败:%s'%sql  
  
  
                        sql = "select SUM(CONSUMEGOLD) from ITEMCONSUMERECORD\_GOLD where PRESENTERID=%d and OPTIME>=%d and OPTIME<%d"%(key, beginTimeStamp, endTimeStamp)  
                        try:  
                                db\_cursor.execute(sql)  
                                singerexp = db\_cursor.fetchone()  
                                if (singerrev != None):  
                                        singerlist\[key\]\[channel\]\['exp'\] += singerexp\[0\]  
                                else:  
                                        print '没有符合条件的数据:%s'%sql  
                        except:  
                                print '查询失败:%s'%sql  
  
  
                        print '\[客户(%d)收入(%d)支出(%d)\]'%(key, singerlist\[key\]\[channel\]\['rev'\], singerlist\[key\]\[channel\]\['exp'\])  
        db\_conn.commit()  
        db\_cursor.close()  
        db\_conn.close()  
        print '\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*begin check\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*'  
        for channel in channellist:  
                singersincome = 0;  
                for singer in singerlist:  
                        if channel in singerlist\[singer\]:  
                                singersincome += singerlist\[singer\]\[channel\]\['rev'\]  
                command = "echo channelid: %d channelrev: %d singersrev: %d >> /home/haoren/logdir/checksettle.txt"%(channel, channellist\[channel\]\['rev'\], singersincome)  
                os.system(command)  
        print '\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*end check\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*'  
main()  
\#if \_\_name\_\_ == "\_\_main\_\_":  
\#        main()  
  
  
====================================================================================================  
cat questionstatistic.py  
\#!/usr/bin/env python  
\#-\*-coding:utf-8-\*-  
\#问题应答统计  
  
  
import MySQLdb  
import os, sys, re,string  
import time, tarfile,getopt  
  
  
optmap = \{   
                'dbuser' : 'haoren',  
                'dbpass' : 'hjkdhskjsh',  
                'dbhost' : '172.16.1.8',  
                'dbport' : 3306,  
                'dbname' : 'OTHERDB'  
                 \}  
                   
def get\_files(dir, pattern):  
        res\_file\_list =\[\]  
        if os.path.exists(dir):  
                cur\_file\_list = os.listdir(dir)  
                for file\_name in cur\_file\_list:  
                        if re.search(pattern, file\_name):  
                                res\_file\_list.append(file\_name)  
                return res\_file\_list  
        else:  
                return 'no'  
  
  
def main():  
  
  
        cur\_day = time.strftime("%Y%m%d", time.localtime(time.time()-86400))  
        opts, args = getopt.getopt(sys.argv\[1:\], 'd:')  
        for op, value in opts:  
                if op == '-d':  
                        m = re.search('\[0-9\]\{8\}', value)  
                        if m:  
                                cur\_day = value  
                        else:  
                                print "请输入8位日期(比如：20130215)"  
                                return 'no'  
  
  
        fmt\_day = time.strftime('%Y-%m-%d', time.localtime(time.mktime(time.strptime(cur\_day, '%Y%m%d'))))  
        d\_time = time.mktime(time.strptime(fmt\_day,'%Y-%m-%d'))   
        print '问题应答情况统计日期:',fmt\_day  
  
  
        \#log\_day = time.strftime("%y%m%d", time.localtime(time.time()-86400))  
        log\_day = time.strftime('%y%m%d', time.localtime(time.mktime(time.strptime(cur\_day, '%Y%m%d'))))  
        dirname="/home/haoren/logdir/%s\_6"%log\_day  
        \#dirname="/home/haoren/log/%s/"%log\_day  
        print dirname  
  
  
        db\_conn = MySQLdb.connect(user=optmap\['dbuser'\], passwd=optmap\['dbpass'\], host=optmap\['dbhost'\], port=optmap\['dbport'\], db=optmap\['dbname'\])  
        db\_cursor=db\_conn.cursor()  
        db\_conn.query("use %s"%optmap\['dbname'\])  
        db\_conn.query("DELETE from QUESTIONSTATISTIC where time='%s'"%fmt\_day)  
        print d\_time  
        if os.path.exists("/tmp/questionstatistic.txt"):  
                os.system("rm -f /tmp/questionstatistic.txt")  
  
  
  
  
        file\_list2=get\_files(dirname,'instructserver')  
        for file2 in file\_list2:  
                command = "cat %s/%s | awk '/问题应答统计/'>> /tmp/questionstatistic.txt"%(dirname,file2)  
                os.system(command)  
  
  
        filename='/tmp/questionstatistic.txt'  
        record = \{\}  
        a\_file = open(filename, 'r')  
        record\['all\_question\_num'\] = 0  
        record\['all\_answer\_num'\] = 0  
        record\['avg\_answer\_time'\] = 0  
        \#130806-17:58:19 InstructServer\[14100\]  INFO: \[问题应答统计\]初始化问题:4221,提问时间:1375783099,提问者:21000146,不喜欢玩dota,解答者:0,解答时间:0,分值:1,游戏:1  
        \#130806-17:58:51 InstructServer\[14100\]  INFO: \[问题应答统计\]应答问题,响应时间:32,问题ID:4221,提问时间:1375783099,提问者:21000146,不喜欢玩dota,解答者:21000278,解答时间:1375783131,分值:1,游戏:1  
        for a\_line in a\_file.readlines():  
                m = re.search("^(\\S+) InstructServer\\\[\\d+\\\]  INFO: \\\[问题应答统计\\\]初始化问题:(\\d+),提问时间:(\\d+),提问者:(\\d+),(\\S+),解答者:(\\d+),解答时间:(\\d+),分值:(\\d+),游戏:(\\d+)", a\_line)   
                if m:  
                        record\['all\_question\_num'\] +=1;  
                        continue  
                m1 = re.search("^(\\S+) InstructServer\\\[\\d+\\\]  INFO: \\\[问题应答统计\\\]应答问题,响应时间:(\\d+),问题ID:(\\d+),提问时间:(\\d+),提问者:(\\d+),(\\S+),解答者:(\\d+),解答时间:(\\d+),分值:(\\d+),游戏:(\\d+)", a\_line)  
                if m1:  
                        record\['all\_answer\_num'\] +=1;  
                        record\['avg\_answer\_time'\] +=int(m1.group(2));  
      
        if (record\['all\_answer\_num'\]!=0):  
                record\['avg\_answer\_time'\] = record\['avg\_answer\_time'\]/record\['all\_answer\_num'\]  
        print '今日提问总数:',record\['all\_question\_num'\]  
        print '今日应答总数:',record\['all\_answer\_num'\]  
        print '今日平均应答时间:',record\['avg\_answer\_time'\]  
        db\_conn.query("insert into QUESTIONSTATISTIC(TIME, QUESTIONNUM, ANSWERNUM, AVGANSWERTIME) values('%s',%d,%d,%d)"%(fmt\_day, record\['all\_question\_num'\], record\['all\_answer\_num'\], record\['avg\_answer\_time'\]))  
        a\_file.close()  
  
  
        db\_conn.commit()  
        db\_cursor.close()  
        db\_conn.close()  
main()  
\#if \_\_name\_\_ == "\_\_main\_\_":  
\#        main()  
  
  
  
  
  
  
  
  
  
  
=================================================================================================  
cat   channelsettlement.py  
\#!/usr/bin/env python  
\#-\*-coding:utf-8-\*-  
\#公司化频道结算月报  
\#\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*使用说明\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
\# 内网测试通过命令行参数  
\# -d 指定统计时间  
\# -p 指定日志所在目录  
\# -t 指定临时文件存放目录  
\# 示例如下:  
\# \[haoren@localhost tongji\]$ ./channelsettlement.py -b 20141225 -e 20150125  
\# 外网无需加参数,使用默认值  
\#\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*使用说明\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
  
  
  
  
import MySQLdb  
import os, sys, re,string  
import time, tarfile,getopt  
import datetime  
  
  
optmap = \{   
                'dbuser' : 'haoren',  
                'dbpass' : 'hjkdhskjsh',  
                'dbhost\_im' : '172.16.1.8',  
                'dbhost\_gm' : '172.16.1.8',  
                'dbport' : 3306,  
                'dbname\_im' : 'FMDB',  
                'dbname\_gm' : 'AMDB'  
                 \}  
                   
def get\_files(dir, pattern):  
        res\_file\_list =\[\]  
        if os.path.exists(dir):  
                cur\_file\_list = os.listdir(dir)  
                cur\_file\_list.sort()  
                for file\_name in cur\_file\_list:  
                        if re.search(pattern, file\_name):  
                                res\_file\_list.append(file\_name)  
                return res\_file\_list  
        else:  
                return 'no'  
  
  
def main():  
  
  
        \#获取频道结算起始时间,每月25日结算  
        cur = datetime.datetime.now()  
        cur\_day = cur.day  
        cur\_month = cur.month  
        cur\_year = cur.year  
        print '\*\*\*\*\*当前时间: %d-%d-%d'%(cur\_year, cur\_month, cur\_day)  
        if (cur.day != 25):  
                print '\*\*\*\*\*未到结算日!\*\*\*\*\*'  
                return  
  
  
        begin\_day = cur\_day  
        begin\_month = cur\_month  
        begin\_year = cur\_year  
        if cur\_month == 1:  
                begin\_month = 12  
                begin\_year = cur\_year - 1  
        else:  
                begin\_month = cur\_month - 1  
  
  
        beginTime = datetime.datetime(begin\_year,begin\_month,begin\_day,0,0,0)  
        endTime = datetime.datetime(cur\_year,cur\_month,cur\_day,0,0,0)  
  
  
        \#内网测试指定参数  
        opts, args = getopt.getopt(sys.argv\[1:\], 'b:e:')  
        for op, value in opts:  
                if op == '-b':  
                        m = re.search('\[0-9\]\{8\}', value)  
                        if m:  
                                beginTime = datetime.datetime.strptime(value, '%Y%m%d')  
                elif op == '-e':  
                        m = re.search('\[0-9\]\{8\}', value)  
                        if m:  
                                endTime = datetime.datetime.strptime(value, '%Y%m%d')  
  
  
        beginTimeStamp = int(time.mktime(beginTime.timetuple()))  
        endTimeStamp = int(time.mktime(endTime.timetuple()))  
        print '\*\*\*\*\*结算开始时间 %s (%d)'%(beginTime,beginTimeStamp)  
        print '\*\*\*\*\*结算结束时间 %s (%d)'%(endTime,endTimeStamp)  
  
  
  
  
        \#获取公司化频道列表  
        db\_conn = MySQLdb.connect(user=optmap\['dbuser'\], passwd=optmap\['dbpass'\], host=optmap\['dbhost\_im'\], port=optmap\['dbport'\], db=optmap\['dbname\_im'\])  
        db\_cursor=db\_conn.cursor()  
        db\_conn.query("use %s"%optmap\['dbname\_im'\])  
        sql = "select \`CHANNELID\` from \`SPECIALCHANNEL\`"  
        channellist = \{\}  
        try:  
                db\_cursor.execute(sql)  
                db\_rows = db\_cursor.fetchall()  
                for row in db\_rows:  
                        channellist\[row\[0\]\] = 0  
        except:  
                print '查询失败:%s'%sql  
        \#获得各个频道的流水,以人民币结算  
        for channelid in channellist:  
                channellist\[channelid\] = \{\}  
                channellist\[channelid\]\['coin'\] = 0  
                channellist\[channelid\]\['rev'\] = 0  
                sql = "select SUM(CONSUMECOIN), SUM(CHANNELRECVGOLD) from ITEMCONSUMERECORD where CHANNELID=%d and OPTIME>=%d and OPTIME<%d"%(channelid,beginTimeStamp,endTimeStamp)  
                try:  
                        db\_cursor.execute(sql)  
                        consumecoin1 = db\_cursor.fetchone()  
                        if (consumecoin1 != None):  
                                channellist\[channelid\]\['coin'\] += consumecoin1\[0\]  
                                channellist\[channelid\]\['rev'\] += consumecoin1\[1\]  
                        else:  
                                print '没有符合条件的数据:%s'%sql  
                except:  
                        print '查询失败:%s'%sql  
  
  
                sql = "select SUM(CONSUMECOIN), SUM(CHANNELRECVGOLD) from REQSONGCONSUMERECORD where CHANNELID=%d and OPTIME>=%d and OPTIME<%d"%(channelid, beginTimeStamp, endTimeStamp)  
                try:  
                        db\_cursor.execute(sql)  
                        consumecoin2 = db\_cursor.fetchone()  
                        if (consumecoin2 != None):  
                                channellist\[channelid\]\['coin'\] += consumecoin2\[0\]  
                                channellist\[channelid\]\['rev'\] += consumecoin2\[1\]  
                        else:  
                                print '没有符合条件的数据:%s'%sql  
                except:  
                        print '查询失败:%s'%sql  
  
  
                print '\[频道(%d)流水(%d)收入(%d)\]'%(channelid,channellist\[channelid\]\['coin'\],channellist\[channelid\]\['rev'\])  
  
  
        singerlist = \{\}  
        for channelid in channellist:  
                sql = "select PERFORMERID from VCHANNELPERFORMER where CHANNELID=%u"%channelid  
                try:  
                        db\_cursor.execute(sql)  
                        result = db\_cursor.fetchall()  
                        for singer in result:  
                                singerlist\[singer\[0\]\] = \{\}  
                                singerlist\[singer\[0\]\]\[channelid\] = \{\}  
                                singerlist\[singer\[0\]\]\[channelid\]\['rev'\] = 0  
                                singerlist\[singer\[0\]\]\[channelid\]\['exp'\] = 0  
                except:  
                        print '查询失败:%s'%sql  
  
  
        for key in singerlist:  
                print key  
                for channel in singerlist\[key\]:  
                        sql = "select SUM(SINGERRECVGOLD) from ITEMCONSUMERECORD where SINGERID=%d and CHANNELID=%d and OPTIME>=%d and OPTIME<%d"%(key, channel, beginTimeStamp, endTimeStamp)  
                        try:  
                                db\_cursor.execute(sql)  
                                singerrev1 = db\_cursor.fetchone()  
                                if (singerrev1 != None):  
                                        singerlist\[key\]\[channel\]\['rev'\] += singerrev1\[0\]  
                                else:  
                                        print '没有符合条件的数据:%s'%sql  
                        except:  
                                print '查询失败:%s'%sql  
  
  
                        sql = "select SUM(SINGERRECVGOLD) from REQSONGCONSUMERECORD where SINGERID=%d and CHANNELID=%d and OPTIME>=%d and OPTIME<%d"%(key, channel, beginTimeStamp, endTimeStamp)  
                        try:  
                                db\_cursor.execute(sql)  
                                singerrev2 = db\_cursor.fetchone()  
                                if (singerrev2 != None):  
                                        singerlist\[key\]\[channel\]\['rev'\] += singerrev2\[0\]  
                                else:  
                                        print '没有符合条件的数据:%s'%sql  
                        except:  
                                print '查询失败:%s'%sql  
  
  
                        sql = "select SUM(CONSUMEGOLD) from ITEMCONSUMERECORD\_GOLD where PRESENTERID=%d and OPTIME>=%d and OPTIME<%d"%(key, beginTimeStamp, endTimeStamp)  
                        try:  
                                db\_cursor.execute(sql)  
                                singerexp = db\_cursor.fetchone()  
                                if (singerrev != None):  
                                        singerlist\[key\]\[channel\]\['exp'\] += singerexp\[0\]  
                                else:  
                                        print '没有符合条件的数据:%s'%sql  
                        except:  
                                print '查询失败:%s'%sql  
  
  
                        print '\[客户(%d)收入(%d)支出(%d)\]'%(key, singerlist\[key\]\[channel\]\['rev'\], singerlist\[key\]\[channel\]\['exp'\])  
        db\_conn.commit()  
        db\_cursor.close()  
        db\_conn.close()  
  
  
        AMDB\_conn = MySQLdb.connect(user=optmap\['dbuser'\], passwd=optmap\['dbpass'\], host=optmap\['dbhost\_gm'\], port=optmap\['dbport'\], db=optmap\['dbname\_gm'\])  
        AMDB\_cursor=AMDB\_conn.cursor()  
        AMDB\_conn.query("use %s"%optmap\['dbname\_gm'\])  
        for channelid in channellist:  
                AMDB\_conn.query("delete from CHANNELSETTLELOG where CHANNELID=%d and BEGINTIME=%d and ENDTIME=%d"%(channelid, beginTimeStamp, endTimeStamp-1))  
                AMDB\_conn.query("insert into CHANNELSETTLELOG (CHANNELID,BEGINTIME,ENDTIME,GOLD,RECVGOLD) values(%d,%d,%d,%d,%d)"%(channelid,beginTimeStamp,endTimeStamp-1,channellist\[channelid\]\['coin'\],channellist\[channelid\]\['rev'\]))  
  
  
        for singerid in singerlist:  
                for channelid in singerlist\[singerid\]:  
                        AMDB\_conn.query("delete from PERFORMERSETTLELOG where CHANNELID=%d and USERID=%d and BEGINTIME=%d and ENDTIME=%d"%(channelid, singerid, beginTimeStamp, endTimeStamp-1))  
                        AMDB\_conn.query("insert into PERFORMERSETTLELOG(CHANNELID,USERID,BEGINTIME,ENDTIME,RECVGOLD,EXPENSE) values(%d,%d,%d,%d,%d,%d)"%(channelid,singerid,beginTimeStamp,endTimeStamp-1,singerlist\[singerid\]\[channelid\]  
  
  
\['rev'\],singerlist\[singerid\]\[channelid\]\['exp'\]))  
  
  
        AMDB\_conn.commit()  
        AMDB\_cursor.close()  
        AMDB\_conn.close()  
main()  
\#if \_\_name\_\_ == "\_\_main\_\_":  
\#        main()  
  
  
  
  
  
  
=====================================================================================================  
cat userLogOutInfo.py  
\#!/usr/bin/env python  
\#-\*-coding:utf-8-\*-  
\#分析日志，将登陆退出信息插入到数据库  
\#每天一张表  
  
  
import MySQLdb  
import os, sys, re,string  
import time, tarfile,getopt  
import datetime  
import operator  
import copy  
  
  
optmap = \{   
        'dbuser' : 'haoren',  
        'dbpass' : 'hjkdhskjsh',  
        'dbhost' : '172.16.1.8',  
        'dbhost\_gm' : '172.16.1.8',  
        'dbport' : 3306,  
        'dbname' : 'FMDB',  
        'dbname\_gm' : 'AMDB'  
\}  
user = \{   
        'userID':0,  
        'channelID':0,  
        'beginDate':0,  
        'endDate':0  
\}  
  
  
  
  
def get\_files(dir, pattern):  
        res\_file\_list =\[\]  
        if os.path.exists(dir):  
                cur\_file\_list = os.listdir(dir)  
                for file\_name in cur\_file\_list:  
                        if re.search(pattern, file\_name):  
                                res\_file\_list.append(file\_name)  
                return res\_file\_list  
        else:  
                return 'no'                                
  
  
def updateEndDate(beginDate,endDate,uid,cid):   
        \#140520-20:57:54   
        if(beginDate!='0'):   
                beginDate = str("20") + str(beginDate\[0:2\]) + "-" + str(beginDate\[2:4\]) + "-" + str(beginDate\[4:6\]) + " " + str(beginDate\[7:\])  
        if(endDate!='0'):   
                endDate = str("20") + str(endDate\[0:2\]) + "-" + str(endDate\[2:4\]) + "-" + str(endDate\[4:6\]) + " " + str(endDate\[7:\])  
        if ( beginDate =='' or beginDate ==0 ):   
                t+=1   
                before\_day = time.strftime("%Y%m%d",time.localtime(time.time()-86400))   
                sql = "select id from USERLOGOUTINFO\_%s where userID=%d and channelID=%d and (endDate='' or endDate='0000-00-00 00:00:00')"%(befor\_day,uid,cid)  
                db\_cursor.execute(sql)   
                db\_rows = db\_cursor.fetchall()   
                if(db\_rows>0):   
                        for id in db\_rows:   
                                sql="update USERLOGOUTINFO\_%s set beginDate='%s' where id=%d"%(befor\_day,endDate,id)  
                else:   
                        updateEndDate(beginDate,endDate,uid,cid)   
        else:   
                cur\_day = time.strftime("%Y%m%d", time.localtime(time.time()-86400))   
                tabletime = time.strftime("%Y%m%d", time.localtime(time.mktime(time.strptime(cur\_day, "%Y%m%d"))))  
                sql = "insert into USERLOGOUTINFO\_%s(userID,channelID,beginDate,endDate)value(%d,%d,'%s','%s')"%(tabletime,int(uid),int(cid),beginDate,endDate)  
                   
        return sql   
  
  
def cmp\_datetime(a,b):  
        a\_datetime = datetime.datetime.strptime(a, '%Y-%m-%d %H:%M:%S')  
        b\_datetime = datetime.datetime.strptime(b, '%Y-%m-%d %H:%M:%S')  
  
  
        if a\_datetime > b\_datetime:  
                return 1  
        elif a\_datetime < b\_datetime:  
                return -1  
        else:  
                return 0  
  
  
  
  
def main():   
  
  
        cur\_day = time.strftime("%Y%m%d", time.localtime(time.time()-86400))  
        log\_day = time.strftime('%y%m%d', time.localtime(time.mktime(time.strptime(cur\_day, '%Y%m%d'))))  
        if len(sys.argv)>1:  
                cur\_day = sys.argv\[1\]  
                log\_day = sys.argv\[1\]\[2:\]  
  
  
        dirname12="/home/haoren/logdir/%s\_12/"%log\_day  
        \#dirname29="/home/haoren/logdir/%s\_29/"%log\_day  
        dirname76="/home/haoren/logdir/%s\_76/"%log\_day  
        dirname111="/home/haoren/logdir/%s\_111/"%log\_day  
        dirname113="/home/haoren/logdir/%s\_113/"%log\_day  
        user\_begin\_list = \[\]  
        user\_end\_list = \[\]  
        dirnames = \[dirname12,dirname76,dirname111,dirname113\]  
        for dirname in dirnames:  
                for fileName in os.listdir(dirname):  
                        a\_file = open(dirname+fileName, 'r')  
                        count = 0  
                        \#140518-14:00:30 VChannelServer\[15000\]  INFO: \[miccard\]频道(3644080)用户(52776729)开始表演...请求相关信息  
                        \#140518-14:00:07 VChannelServer\[15000\]  INFO: \[miccard\]频道(3644080)用户(52776729)结束表演...  
                        \#140518-14:00:07 VChannelServer\[15000\]  INFO: \[miccard\]频道(3644080)用户(52776729)被管理员(123456)强制结束表演...  
  
  
                        \#140521-00:00:47 VChannelServer\[15011\] DEBUG: \[observer\]onUserUnperform(channelid=3728463,userid=53543825)  
                        \#140521-12:01:38 VChannelServer\[15000\]  INFO: \[client\]\[mic\]用户(53543825)请求加入麦序(3644079)成功  
                        for a\_line in a\_file.readlines():  
                                begin\_ = re.search("^(\\S+) VChannelServer\\\[\\d+\\\] DEBUG: \\\[observer\\\]onUserPerform\$channelid=(\\d+),userid=(\\d+)\$", a\_line)  
                                end\_ =  re.search("^(\\S+) VChannelServer\\\[\\d+\\\] DEBUG: \\\[observer\\\]onUserUnperform\$channelid=(\\d+),userid=(\\d+)\$", a\_line)  
                                if begin\_:  
                                        user\_begin = \{\}  
                                        user\_begin\['userID'\] = begin\_.group(3)  
                                        user\_begin\['channelID'\] = begin\_.group(2)  
                                        user\_begin\['beginDate'\] = str("20") + str(begin\_.group(1)\[0:2\]) + "-" + str(begin\_.group(1)\[2:4\]) + "-" + str(begin\_.group(1)\[4:6\]) + " " + str(begin\_.group(1)\[7:\])  
                                        user\_begin\['endDate'\] = 0  
                                        user\_begin\_list.append(user\_begin)  
                                if end\_:  
                                        user\_end = \{\}  
                                        user\_end\['userID'\] = end\_.group(3)  
                                        user\_end\['channelID'\] = end\_.group(2)  
                                        user\_end\['beginDate'\] = 0  
                                        user\_end\['endDate'\] = str("20") + str(end\_.group(1)\[0:2\]) + "-" + str(end\_.group(1)\[2:4\]) + "-" + str(end\_.group(1)\[4:6\]) + " " + str(end\_.group(1)\[7:\])  
                                        user\_end\_list.append(user\_end)  
                        a\_file.close()  
  
  
        user\_begin\_list.sort(cmp=cmp\_datetime, key=operator.itemgetter('beginDate'))  
        user\_end\_list.sort(cmp=cmp\_datetime, key=operator.itemgetter('endDate'))  
   
   
        \#match  
        beforelist = \[\]  
        for user\_begin in user\_begin\_list:  
                num = 0  
                for user\_end in user\_end\_list:  
                        begin\_datetime = datetime.datetime.strptime(user\_begin\['beginDate'\], '%Y-%m-%d %H:%M:%S')  
                        end\_datetime = datetime.datetime.strptime(user\_end\['endDate'\], '%Y-%m-%d %H:%M:%S')  
                        if (int(user\_begin\['userID'\])==int(user\_end\['userID'\]) and int(user\_begin\['channelID'\])==int(user\_end\['channelID'\]) and begin\_datetime <= end\_datetime):  
                                user\_begin\['endDate'\] = user\_end\['endDate'\]  
                                del user\_end\_list\[num\]  
                                break  
                        else:  
                                num += 1  
  
  
\#       for list in user\_begin\_list:  
\#               print "userID:" + str(list\['userID'\]) + ",channelID:" + str(list\['channelID'\]) + ",begin:" + str(list\['beginDate'\]) + ",end:" + str(list\['endDate'\])  
                \#sys.exit(1)  
\#       for list in user\_end\_list:  
\#               print "userID:" + str(list\['userID'\]) + ",channelID:" + str(list\['channelID'\]) + ",begin:" + str(list\['beginDate'\]) + ",end:" + str(list\['endDate'\])  
  
  
  
  
  
  
  
  
        db\_conn = MySQLdb.connect(user=optmap\['dbuser'\], passwd=optmap\['dbpass'\], host=optmap\['dbhost\_gm'\], port=optmap\['dbport'\], db=optmap\['dbname\_gm'\])  
        db\_conn.query("use %s" %optmap\['dbname\_gm'\])  
        db\_cursor = db\_conn.cursor()  
        tabletime = time.strftime("%Y%m%d", time.localtime(time.mktime(time.strptime(cur\_day, "%Y%m%d"))))  
       \#print "数据库表时间后缀%s"%tabletime  
        tabletime = cur\_day  
        print '用户表演时间，统计日期:',tabletime  
        sql="CREATE TABLE IF NOT EXISTS \`USERLOGOUTINFO\_%s\` like USERLOGOUTINFO\_SAMPLE"%tabletime  
        \#delete  
        try:  
                db\_conn.query("drop table USERLOGOUTINFO\_%s"%tabletime)  
        except:  
                \#print "USERLOGOUTINFO\_%s not exist."%tabletime  
                print "The first statistics."  
        \#create  
        db\_conn.query(sql)  
  
  
  
  
  
  
        \#update before yesterday data  
        for list in user\_end\_list:  
\#               for i in range(0,5):  
                        before\_day = cur\_day  
                        d1 = datetime.datetime(string.atoi(before\_day\[0:4\]),string.atoi(before\_day\[4:6\]),string.atoi(before\_day\[6:8\]))  
                        d2 = d1 + datetime.timedelta(-1)  
                        before\_day = d2.strftime("%Y%m%d")  
                        \#before\_day = time.strftime("%Y%m%d",time.localtime(time.time()-86400\*(i+2)))   
                        try:  
                                sql = "select id from USERLOGOUTINFO\_%s where userID=%d and channelID=%d and ENDTIME=0"%(before\_day,int(list\['userID'\]),int(list\['channelID'\]))  
                                db\_cursor.execute(sql)   
                                print sql   
                                \#db\_rows = db\_cursor.fetchall()   
                                temp=db\_cursor.fetchone()  
                                if(temp != None):  
                                        timeArray = time.strptime(list\['endDate'\],"%Y-%m-%d %H:%M:%S")  
                                        timeStamp = int(time.mktime(timeArray))  
                                        sql = "update USERLOGOUTINFO\_%s set ENDTIME=%d where id=%d"%(before\_day,timeStamp,temp\[0\])  
                                        print sql  
                                        db\_conn.query(sql)  
                                \#       del user\_end\_list\[i\]  
                        except:  
                                pass  
                                \#print "log not exist."  
  
  
        \#update yesterday data  
        for list in user\_begin\_list:  
                \#cur\_day = time.strftime("%Y%m%d", time.localtime(time.time()-864::  
                \#tabletime = time.strftime("%Y%m%d", time.localtime(time.mktime(ti:iime.strptime(cur\_day, "%Y%m%d")))  
                timeArray = time.strptime(str(list\['beginDate'\]),"%Y-%m-%d %H:%M:%S")  
                beginTimestamp = int(time.mktime(timeArray))  
                if (list\['endDate'\] == 0 or list\['endDate'\] =='') :  
                        endTimestamp = 0  
                else:  
                        timeArray2 = time.strptime(str(list\['endDate'\]),"%Y-%m-%d %H:%M:%S")  
                        endTimestamp = int(time.mktime(timeArray2))  
                sql = "insert into USERLOGOUTINFO\_%s(userID,channelID,BEGINTIME,ENDTIME)value(%d,%d,%d,%d)"%(tabletime,int(list\['userID'\]),int(list\['channelID'\]),beginTimestamp,endTimestamp)  
                db\_conn.query(sql)  
  
  
        db\_conn.commit()  
        db\_cursor.close()  
        db\_conn.close()  
  
  
        print "SUCCESS."  
  
  
  
  
main()  
\#if \_\_name\_\_ == "\_\_main\_\_":  
\#        main()  
  
  
=======================================================================================================  
cat vfulinmenDaily.py  
\#!/usr/bin/env python  
\#-\*-coding:utf-8-\*-  
\#视频直播间福临门开启日报  
import MySQLdb  
import os, sys, re,string  
import time, tarfile,getopt  
  
  
                   
optmap = \{   
                'dbuser' : 'haoren',  
                'dbpass' : 'hjkdhskjsh',  
                'dbhost' : '172.16.1.8',  
                'dbport' : 3306,      
                'dbname' : 'AMDB'  
        \}  
  
  
fulinmen = \{   
        'performerID' : 0,  
        'channelID' : 0,  
        'subchannelID' : 0,  
        'menID' : 0,  
        'dubi' : 0,  
        'time' : 0  
        \}  
  
  
hongbao = \{   
        'userID' : 0,  
        'channelID' : 0,  
        'subchannelID' : 0,  
        'menID' : 0,  
        'dubi' : 0,  
        'time' : 0  
        \}  
  
  
def get\_files(dir, pattern):  
        res\_file\_list =\[\]  
        if os.path.exists(dir):  
                cur\_file\_list = os.listdir(dir)  
                cur\_file\_list.sort()  
                for file\_name in cur\_file\_list:  
                        if re.search(pattern, file\_name):  
                                res\_file\_list.append(file\_name)  
                return res\_file\_list  
        else:  
                return 'no'  
  
  
def main():  
  
  
        cur\_day = time.strftime("%Y%m%d", time.localtime(time.time()-86400))      
  
  
        opts, args = getopt.getopt(sys.argv\[1:\], 'd:')  
        for op, value in opts:  
                if op == '-d':  
                        m = re.search('\[0-9\]\{8\}', value)  
                        if m:  
                                cur\_day = value  
                        else:  
                                print "请输入8位日期(比如：20130215)"  
                                return 'no'  
  
  
        fmt\_day = time.strftime('%Y-%m-%d', time.localtime(time.mktime(time.strptime(cur\_day, '%Y%m%d'))))  
  
  
        d\_time = time.mktime(time.strptime(fmt\_day,'%Y-%m-%d'))   
        print '统计日期:',fmt\_day  
        log\_day = time.strftime('%y%m%d', time.localtime(time.mktime(time.strptime(cur\_day, '%Y%m%d'))))  
  
  
        dirname\_v1="/home/haoren/logdir/%s\_12"%log\_day  
        \#dirname\_v2="/home/haoren/logdir/%s\_29"%log\_day  
        dirname\_v3="/home/haoren/logdir/%s\_76"%log\_day  
        dirname\_v4="/home/haoren/logdir/%s\_111"%log\_day  
        dirname\_v5="/home/haoren/logdir/%s\_113"%log\_day  
        print dirname\_v1  
        \#print dirname\_v2  
        print dirname\_v3  
        print dirname\_v4  
        print dirname\_v5  
  
  
        \#开启门统计   
        if os.path.exists("/tmp/openfulinmendaily.txt"):  
                os.system("rm -f /tmp/openfulinmendaily.txt")  
  
  
        \#用户获取红包统计  
        if os.path.exists("/tmp/hongbaodaily.txt"):  
                os.system("rm -f /tmp/hongbaodaily.txt")  
  
  
        file\_list1=get\_files(dirname\_v1,'vchannelserver')  
        for file1 in file\_list1:  
                command = "cat %s/%s | awk '/门统计/'>> /tmp/openfulinmendaily.txt"%(dirname\_v1,file1)  
                os.system(command)  
                command = "cat %s/%s | awk '/兔子统计/'>> /tmp/hongbaodaily.txt"%(dirname\_v1,file1)  
                os.system(command)  
  
  
        file\_list2=get\_files(dirname\_v2,'vchannelserver')  
        for file2 in file\_list2:  
                command = "cat %s/%s | awk '/门统计/'>> /tmp/openfulinmendaily.txt"%(dirname\_v2,file2)  
                os.system(command)  
                command = "cat %s/%s | awk '/兔子统计/'>> /tmp/hongbaodaily.txt"%(dirname\_v2,file2)  
                os.system(command)  
  
  
        file\_list3=get\_files(dirname\_v3,'vchannelserver')  
        for file3 in file\_list3:  
                command = "cat %s/%s | awk '/门统计/'>> /tmp/openfulinmendaily.txt"%(dirname\_v3,file3)  
                os.system(command)  
                command = "cat %s/%s | awk '/兔子统计/'>> /tmp/hongbaodaily.txt"%(dirname\_v3,file3)  
                os.system(command)  
  
  
        \#开启福临门  
        filename='/tmp/openfulinmendaily.txt'  
        a\_file = open(filename, 'r')  
        fulinmen\_list = \[\]  
    \#140912-15:42:21 VChannelServer\[15000\]  INFO: \[福临门统计\]用户(21001043)在频道(2001241)子频道(0)开启福临门(4),开门人民币(299900)时间(1410507741)  
        for a\_line in a\_file.readlines():  
                m = re.search("^\\S+ \\S+\\\[(\\d+)\\\]  INFO: \\\[福临门统计\\\]用户\$(\\d+)\$在频道\$(\\d+)\$子频道\$(\\d+)\$开启福临门\$(\\d+)\$,开门人民币\$(\\d+)\$时间\$(\\d+)\$", a\_line)  
                if m:  
                        fulinmen\_ = \{\}  
                        fulinmen\_\['performerID'\] = int(m.group(2))  
                        fulinmen\_\['channelID'\]   = int(m.group(3))  
                        fulinmen\_\['subchannelID'\] = int(m.group(4))  
                        fulinmen\_\['menID'\] = int(m.group(5))  
                        fulinmen\_\['dubi'\] = int(m.group(6))  
                        fulinmen\_\['time'\] = int(m.group(7))  
                        fulinmen\_list.append(fulinmen\_)  
                a\_file.close()  
  
  
        \#抓兔子统计  
        filename='/tmp/hongbaodaily.txt'  
        a\_file = open(filename, 'r')  
        hongbao\_list = \[\]  
    \#140912-15:42:42 VChannelServer\[15000\]  INFO: \[抓兔子统计\]用户(21001034)在频道(2001241)子频道(0)用户(21001043),领取福临门(4),红包(110),成功时间(1410507762)  
        for a\_line in a\_file.readlines():  
                m = re.search("^\\S+ \\S+\\\[(\\d+)\\\]  INFO: \\\[抓兔子统计\\\]用户\$(\\d+)\$在频道\$(\\d+)\$子频道\$(\\d+)\$用户\$(\\d+)\$,领取福临门\$(\\d+)\$,红包\$(\\d+)\$,成功时间\$(\\d+)\$", a\_line)  
                if m:  
                        hongbao\_ = \{\}  
                        hongbao\_\['userID'\] = int(m.group(2))  
                        hongbao\_\['performerID'\] = int(m.group(5))  
                        hongbao\_\['channelID'\]   = int(m.group(3))  
                        hongbao\_\['subchannelID'\] = int(m.group(4))  
                        hongbao\_\['menID'\] = int(m.group(6))  
                        hongbao\_\['dubi'\] = int(m.group(7))  
                        hongbao\_\['time'\] = int(m.group(8))  
                        hongbao\_list.append(hongbao\_)  
                a\_file.close()  
  
  
        db\_conn = MySQLdb.connect(user=optmap\['dbuser'\], passwd=optmap\['dbpass'\], host=optmap\['dbhost'\], port=optmap\['dbport'\], db=optmap\['dbname'\])  
        db\_cursor=db\_conn.cursor()  
        db\_conn.query("use %s"%optmap\['dbname'\])  
        tabletime = time.strftime("%Y%m%d", time.localtime(time.mktime(time.strptime(cur\_day, "%Y%m%d"))))  
  
  
        sql="CREATE TABLE IF NOT EXISTS \`FULINMENDAILY\_%s\` like FULINMENDAILY\_SAMPLE"%tabletime  
  
  
        db\_conn.query(sql)  
        db\_conn.query("delete from FULINMENDAILY\_%s"%tabletime)  
        for fulinmen in fulinmen\_list:  
                db\_conn.query("insert into FULINMENDAILY\_%s(PERFORMERID,CHANNELID,SUBCHANNELID,MENID,GOALCOIN,OPENTIME) values(%d,%d,%d,%d,%d,%d)"%(tabletime, int(fulinmen\['performerID'\]), int(fulinmen\['channelID'\]), int(fulinmen  
  
  
\['subchannelID'\]), int(fulinmen\['menID'\]),int(fulinmen\['dubi'\]), int(fulinmen\['time'\])))  
  
  
        sql="CREATE TABLE IF NOT EXISTS \`HONGBAODAILY\_%s\` like HONGBAODAILY\_SAMPLE"%tabletime  
  
  
        db\_conn.query(sql)  
        db\_conn.query("delete from HONGBAODAILY\_%s"%tabletime)  
  
  
        for hongbao in hongbao\_list:  
                db\_conn.query("insert into HONGBAODAILY\_%s(USERID,PERFORMERID,CHANNELID,SUBCHANNELID,MENID,DUBI,TIME) values(%d,%d,%d,%d,%d,%d,%d)"%(tabletime, int(hongbao\['userID'\]),int(hongbao\['performerID'\]), int(hongbao  
  
  
\['channelID'\]), int(hongbao\['subchannelID'\]), int(hongbao\['menID'\]),int(hongbao\['dubi'\]), int(hongbao\['time'\])))  
  
  
        db\_conn.commit()  
        db\_cursor.close()  
        db\_conn.close()  
main()  
\#if \_\_name\_\_ == "\_\_main\_\_":  
\#        main()  
  
  
  
  
========================================================================  
cat JieSuanTongJi20160126.py  
\#!/usr/bin/env python  
\#-\*-coding:utf-8-\*-  
\#特殊食用油明细  
  
  
import MySQLdb  
import os, sys, re,string  
import time, tarfile,getopt  
  
  
optmap = \{   
                'dbuser' : 'haoren',  
                'dbpass' : 'euwdlcmdwkd',  
                'dbhost' : '172.27.6.12',  
                'dbport' : 3306,  
                'dbname' : 'JSDB'  
                 \}  
def get\_files(dir, pattern):  
        res\_file\_list =\[\]  
        if os.path.exists(dir):  
                cur\_file\_list = os.listdir(dir)  
                for file\_name in cur\_file\_list:  
                        if re.search(pattern, file\_name):  
                                res\_file\_list.append(file\_name)  
                return res\_file\_list  
        else:  
                return 'no'  
def main():  
  
  
        cur\_day = time.strftime("%Y%m%d", time.localtime(time.time()-86400))  
  
  
        opts, args = getopt.getopt(sys.argv\[1:\], 'd:')  
        for op, value in opts:  
                if op == '-d':  
                        m = re.search('\[0-9\]\{8\}', value)  
                        if m:  
                                cur\_day = value  
                        else:  
                                print "请输入8位日期(比如：20130215)"  
                                return 'no'  
  
  
        log\_day = time.strftime('%y%m%d', time.localtime(time.mktime(time.strptime(cur\_day, '%Y%m%d'))))  
  
  
        fmt\_day = time.strftime('%Y-%m-%d', time.localtime(time.mktime(time.strptime(cur\_day, '%Y%m%d'))))  
        print '结算统计日期:',fmt\_day  
  
  
        \#log\_day = time.strftime("%y%m%d", time.localtime(time.time()-86400))  
        dirname="/home/haoren/logdir/%s\_67"%log\_day  
        print dirname  
  
  
  
  
        db\_conn = MySQLdb.connect(user=optmap\['dbuser'\], passwd=optmap\['dbpass'\], host=optmap\['dbhost'\], port=optmap\['dbport'\], db=optmap\['dbname'\])  
        db\_cursor=db\_conn.cursor()  
        db\_conn.query("use %s"%optmap\['dbname'\])  
        tabletime = time.strftime("%y%m%d", time.localtime(time.mktime(time.strptime(cur\_day, "%Y%m%d"))))  
  
  
        sql="CREATE TABLE IF NOT EXISTS \`JIESUANTONGJI\_%s\` like JIESUANTONGJISAMPLE"%tabletime  
  
  
        db\_conn.query(sql)  
        db\_conn.query("delete from JIESUANTONGJI\_%s"%tabletime)  
  
  
        if os.path.exists("/tmp/JieSuanTongJi2016.txt"):  
                os.system("rm -f /tmp/JieSuanTongJi2016.txt")  
  
  
  
  
        file\_list2=get\_files(dirname,'billserver')  
        for file2 in file\_list2:  
                command = "cat %s/%s | grep -h -w  结算统计 |grep -v 人民币消费结算统计  >> /tmp/JieSuanTongJi2016.txt"%(dirname,file2)  
                os.system(command)  
  
  
        \#结算统计记录放在txt文档里面  
        filename='/tmp/JieSuanTongJi2016.txt'  
        record = \{\}  
        a\_file = open(filename, 'r')  
        \#160125-11:00:14 Bill\[40268\]  INFO: \[结算统计\]时间(1453690814)类别(1)名称(物品收入)频道(3977962)等级(2)用户(65147500)赠送(1)个物品(39)给客户(65147500),客户等级(28),签约(1), 消耗人民币(100), 客户获得人民币(8000), 频道获得人民币  
  
  
(2000),歌手当前人民币(1320960)频道当前人民币(335560)  
        for a\_line in a\_file.readlines():  
                        m = re.search("^(\\S+) Bill\\\[\\d+\\\]  INFO: \\\[结算统计\\\]时间\$(\\d+)\$类别\$(\\d+)\$名称\$(\\S+)\$频道\$(\\d+)\$等级\$(\\d+)\$用户\$(\\d+)\$赠送\$(\\d+)\$个物品\$(\\d+)\$给客户\$(\\d+)\$,客户等级\$(\\d+)\$,签约\$(\\d+)\$, 消耗  
  
  
人民币\$(\\d+)\$, 客户获得人民币\$(\\d+)\$, 频道获得人民币\$(\\d+)\$,客户当前人民币\$(\\d+)\$频道当前人民币\$(\\d+)\$", a\_line)  
                        if m:  
                    \#print "第一项:"+m.group(1)  
                    \#print "第二项:"+m.group(2)  
                    \#print "第三项:"+m.group(3)  
                    \#print "第四项:"+m.group(4)  
                    \#print "第五项:"+m.group(5)  
                    \#print "第六项:"+m.group(6)  
                    \#print "第七项:"+m.group(7)  
                    \#print "第八项:"+m.group(8)  
                    \#print "第九项:"+m.group(9)  
                    \#print "第十项:"+m.group(10)  
                    \#print "第十一项:"+m.group(11)  
                    \#print "第十二项:"+m.group(12)  
                    \#print "第十三项:"+m.group(13)  
                    \#print "第十四项:"+m.group(14)  
                    \#print "第十五项:"+m.group(15)  
                    \#print "第十六项:"+m.group(16)  
                    \#print "第十七项:"+m.group(17)  
                    \#print "第十八项:"+m.group(18)  
                                if int(m.group(14)) >0 or int(m.group(15)) >0 :  
                                                         db\_conn.query("insert into JIESUANTONGJI\_%s  
  
  
(OPTIME,TYPE,ITEMNAME,CHANNELID,CHANNELLEVEL,PRESENTERID,ITEMNUM,ITEMID,SINGERID,SINGERLEVEL,SIGN,CONSUMECOIN,SINGERRECVGOLD,CHANNELRECVGOLD,CURRENTSINGERGOLD,CURRENTCHANNELGOLD) values(%d,%d,'%s',%d,%d,%d,%d,%d,%d,%d,%d,%d,%d,%d,%d,  
  
  
%d)"%(tabletime,int(m.group(2)),int(m.group(3)),str(m.group(4)),int(m.group(5)),int(m.group(6)),int(m.group(7)),int(m.group(8)),int(m.group(9)),int(m.group(10)),int(m.group(11)),int(m.group(12)),int(m.group(13)),int(m.group(14)),int  
  
  
(m.group(15)),int(m.group(16)),int(m.group(17))))  
  
  
        a\_file.close()  
        db\_conn.commit()  
        db\_cursor.close()  
        db\_conn.close()  
main()  
\#if \_\_name\_\_ == "\_\_main\_\_":  
\#        main()  
  
  
  
  
  
  
  
  
  
  
===========================================================================================================  
cat BaoGuoTongJi20160202.py  
\#!/usr/bin/env python  
\#-\*-coding:utf-8-\*-  
\#特殊食用油明细  
  
  
import MySQLdb  
import os, sys, re,string  
import time, tarfile,getopt  
  
  
optmap = \{   
                'dbuser' : 'haoren',  
                'dbpass' : 'euwdlcmdwkd',  
                'dbhost' : '172.27.6.12',  
                'dbport' : 3306,  
                'dbname' : 'JIESUANDB'  
                 \}  
def get\_files(dir, pattern):  
        res\_file\_list =\[\]  
        if os.path.exists(dir):  
                cur\_file\_list = os.listdir(dir)  
                for file\_name in cur\_file\_list:  
                        if re.search(pattern, file\_name):  
                                res\_file\_list.append(file\_name)  
                return res\_file\_list  
        else:  
                return 'no'  
def main():  
  
  
        cur\_day = time.strftime("%Y%m%d", time.localtime(time.time()-86400))  
  
  
        opts, args = getopt.getopt(sys.argv\[1:\], 'd:')  
        for op, value in opts:  
                if op == '-d':  
                        m = re.search('\[0-9\]\{8\}', value)  
                        if m:  
                                cur\_day = value  
                        else:  
                                print "请输入8位日期(比如：20130215)"  
                                return 'no'  
  
  
        log\_day = time.strftime('%y%m%d', time.localtime(time.mktime(time.strptime(cur\_day, '%Y%m%d'))))  
  
  
        fmt\_day = time.strftime('%Y-%m-%d', time.localtime(time.mktime(time.strptime(cur\_day, '%Y%m%d'))))  
        print '结算购物车日期:',fmt\_day  
  
  
        \#log\_day = time.strftime("%y%m%d", time.localtime(time.time()-86400))  
        dirname="/home/haoren/logdir/%s\_21"%log\_day  
        print dirname  
  
  
        db\_conn = MySQLdb.connect(user=optmap\['dbuser'\], passwd=optmap\['dbpass'\], host=optmap\['dbhost'\], port=optmap\['dbport'\], db=optmap\['dbname'\])  
        db\_cursor=db\_conn.cursor()  
        db\_conn.query("use %s"%optmap\['dbname'\])  
        tabletime = time.strftime("%y%m%d", time.localtime(time.mktime(time.strptime(cur\_day, "%Y%m%d"))))  
  
  
        sql="CREATE TABLE IF NOT EXISTS \`BAOGUOTONGJI\_%s\` like BAOGUOSAMPLE"%tabletime  
  
  
        db\_conn.query(sql)  
        db\_conn.query("delete from BAOGUOTONGJI\_%s"%tabletime)  
  
  
        if os.path.exists("/tmp/BaoGuoTongJi2016.txt"):  
                os.system("rm -f /tmp/BaoGuoTongJi2016.txt")  
                
  
  
        file\_list2=get\_files(dirname,'userpackageserver')  
        for file2 in file\_list2:  
                command = "cat %s/%s | grep -h -w  购物车结算  >> /tmp/BaoGuoTongJi2016.txt"%(dirname,file2)  
                os.system(command)  
  
  
        \#结算统计记录放在txt文档里面  
        filename='/tmp/BaoGuoTongJi2016.txt'  
        record = \{\}  
        a\_file = open(filename, 'r')  
        \#160201-00:00:38 UserPackage\[17501\]  INFO: \[购物车结算\]Function(17)OPName(\[爬榜宝箱\])Idx(86139)BackRoll(N)UID(22836501)Old(0)OP(1)(2)New(2)ID(39)Name(大豆)expireTime(1454947200)Type(0)  
        \#160201-00:00:28 UserPackage\[17501\]  INFO: \[购物车结算\]Function(6)OPName(运自行)Idx(0)BackRoll(N)UID(21993135)Old(2129)OP(1)(1)New(2130)ID(11)Name(飙车券)expireTime(1454342428)Type(0)  
  
  
        \#160418-12:04:31 UserPackage\[17501\]  INFO: \[购物车结算\]Function(6)OPName(运自行)Idx(8206)BackRoll(N)UID(32253058)Old(34)OP(2)减少(1)New(33)ID(41)Name(自行车令)expireTime(0)Type(0)  
        \#160418-12:04:37 UserPackage\[17501\]  INFO: \[购物车结算\]Function(6)OPName(运自行)Idx(8207)BackRoll(N)UID(32253058)Old(33)OP(2)减少(1)New(32)ID(41)Name(自行车令)expireTime(0)Type(0)  
        for a\_line in a\_file.readlines():  
                        \#m = re.search("^(\\S+) UserPackage\\\[\\d+\\\]  INFO: \\\[购物车结算\\\]Function\$(\\d+)\$OPName\$(\\S+)\$Idx\$(\\d+)\$BackRoll\$(\\S+)\$UID\$(\\d+)\$Old\$(\\d+)\$OP\$(\\d+)\$\$(\\d+)\$New\$(\\d+)\$ID\$(\\d+)\$Name\$(\\S+)\$ expireTime\$(\\d+)\$Type\$(\\d+)\$", a\_line)  
                        m = re.search("^(\\S+) UserPackage\\\[\\d+\\\]  INFO: \\\[购物车结算\\\]Function\$(\\d+)\$OPName\$(\\S+)\$Idx\$(\\d+)\$BackRoll\$(\\S+)\$UID\$(\\d+)\$Old\$(\\d+)\$OP\$(\\d+)\$(\\S+)\$(\\d+)\$New\$(\\d+)\$ID\$(\\d+)\$Name\$(\\S+)\$ expireTime\$(\\d+)\$Type\$(\\d+)\$", a\_line)  
                        if m:  
                          print "第一项,时间:"+m.group(1)  
                          print "第二项,Function:"+m.group(2)  
                          print "第三项,OPName:"+m.group(3)  
                          print "第四项,Idx:"+m.group(4)  
                          print "第五项,BackRoll:"+m.group(5)  
                          print "第六项,UID:"+m.group(6)  
                          print "第七项,Old:"+m.group(7)  
                          print "第八项,OP:"+m.group(8)  
                          print "第九项,"+m.group(9)  
                          print "第十项,New:"+m.group(10)  
                          print "第十一项,ID:"+m.group(11)  
                          print "第十二项,Name:"+m.group(12)  
                          print "第十三项,expireTime:"+m.group(13)  
                          print "第十四项,Type:"+m.group(14)  
                          \#print "第十五项:"+m.group(15)  
                          \#print "第十六项:"+m.group(16)  
                          \#print "第十七项:"+m.group(17)  
                          \#print "第十八项:"+m.group(18)  
                          opname=m.group(3).replace('\[','')  
                          opname=opname.replace('\]','')  
                          \#print "第三项,OPName:"+opname  
                          \#if (int(m.group(7)) < int(m.group(10))) :  
                          \#    op=1  
                          \#else :  
                          \#    op=2  
                          db\_conn.query("insert into BAOGUOTONGJI\_%s(FUNCTION,OPNAME,IDX,BACKROLL,UID,OLD,OP,NUM,NEW,ID,NAME,EXPIRETIME,TYPE) values(%d,'%s',%d,'%s',%d,%d,%d,%d,%d,%d,'%s',%d,%d)"%(tabletime,int(m.group(2)),opname,int  
  
  
(m.group(4)),str(m.group(5)),int(m.group(6)),int(m.group(7)),int(m.group(8)),int(m.group(10)),int(m.group(11)),int(m.group(12)),str(m.group(13)),int(m.group(14)),int(m.group(15))))  
  
  
        a\_file.close()  
        db\_conn.commit()  
        db\_cursor.close()  
        db\_conn.close()  
main()  
\#if \_\_name\_\_ == "\_\_main\_\_":  
\#        main()  
  
=====================================================================================================================  
cat DuBiJieSuan20160325.py  
\#!/usr/bin/env python  
\#-\*-coding:utf-8-\*-  
\#特殊食用油明细  
  
  
import MySQLdb  
import os, sys, re,string  
import time, tarfile,getopt  
  
  
optmap = \{   
                'dbuser' : 'haoren',  
                'dbpass' : 'euwdlcmdwkd',  
                'dbhost' : '172.27.6.12',  
                'dbport' : 3306,  
                'dbname' : 'JIESUANDB'  
                 \}  
def get\_files(dir, pattern):  
        res\_file\_list =\[\]  
        if os.path.exists(dir):  
                cur\_file\_list = os.listdir(dir)  
                for file\_name in cur\_file\_list:  
                        if re.search(pattern, file\_name):  
                                res\_file\_list.append(file\_name)  
                return res\_file\_list  
        else:  
                return 'no'  
def main():  
  
  
        cur\_day = time.strftime("%Y%m%d", time.localtime(time.time()-86400))  
  
  
        opts, args = getopt.getopt(sys.argv\[1:\], 'd:')  
        for op, value in opts:  
                if op == '-d':  
                        m = re.search('\[0-9\]\{8\}', value)  
                        if m:  
                                cur\_day = value  
                        else:  
                                print "请输入8位日期(比如：20130215)"  
                                return 'no'  
  
  
        log\_day = time.strftime('%y%m%d', time.localtime(time.mktime(time.strptime(cur\_day, '%Y%m%d'))))  
  
  
        fmt\_day = time.strftime('%Y-%m-%d', time.localtime(time.mktime(time.strptime(cur\_day, '%Y%m%d'))))  
        print '结算统计日期:',fmt\_day  
  
  
        \#log\_day = time.strftime("%y%m%d", time.localtime(time.time()-86400))  
        dirname="/home/haoren/logdir/%s\_67"%log\_day  
        print dirname  
  
  
  
  
        db\_conn = MySQLdb.connect(user=optmap\['dbuser'\], passwd=optmap\['dbpass'\], host=optmap\['dbhost'\], port=optmap\['dbport'\], db=optmap\['dbname'\])  
        db\_cursor=db\_conn.cursor()  
        db\_conn.query("use %s"%optmap\['dbname'\])  
        tabletime = time.strftime("%y%m%d", time.localtime(time.mktime(time.strptime(cur\_day, "%Y%m%d"))))  
  
  
        sql="CREATE TABLE IF NOT EXISTS \`DUBIJIESUANTONGJI\_%s\` like DUBIJIESUANTONGJISAMPLE"%tabletime  
  
  
        db\_conn.query(sql)  
        db\_conn.query("delete from DUBIJIESUANTONGJI\_%s"%tabletime)  
  
  
        if os.path.exists("/tmp/DuBiJieSuanTongJi2016.txt"):  
                os.system("rm -f /tmp/DuBiJieSuanTongJi2016.txt")  
  
        file\_list2=get\_files(dirname,'billserver')  
        for file2 in file\_list2:  
                command = "cat %s/%s | grep -h -w  人民币结算 |grep 原来人民币  >> /tmp/DuBiJieSuanTongJi2016.txt"%(dirname,file2)  
                os.system(command)  
  
  
        \#结算统计记录放在txt文档里面  
        filename='/tmp/DuBiJieSuanTongJi2016.txt'  
        record = \{\}  
        a\_file = open(filename, 'r')  
        \#160125-11:00:14 Bill\[40268\]  INFO: \[结算统计\]时间(1453690814)类别(1)名称(物品收入)频道(3977962)等级(2)用户(65147500)赠送(1)个物品(39)给客户(65147500),客户等级(28),签约(1), 消耗人民币(100), 客户获得人民币(8000), 频道获得人民币 (2000),歌手当前人民币(1320960)频道当前人民币(335560)  
        for a\_line in a\_file.readlines():  
                        m = re.search("^(\\S+) Bill\\\[\\d+\\\]  INFO: \\\[人民币结算\\\]时间\$(\\d+)\$,用户ID\$(\\d+)\$,原来人民币\$(\\d+)\$,现在人民币\$(\\d+)\$,人民币操作\$(\\d+)\$,(\\S+)\$(\\d+)\$,操作类型\$(\\d+)\$,操作详情\$(\\d+)\$,操作数量\$(\\d  
  
  
\+)\$.描述:(\\S+)", a\_line)  
                        if m:  
                            print "第一项:"+m.group(1)  
                            print "第二项:"+m.group(2)  
                            print "第三项:"+m.group(3)  
                            print "第四项:"+m.group(4)  
                            print "第五项:"+m.group(5)  
                            print "第六项:"+m.group(6)  
                            print "第七项:"+m.group(7)  
                            print "第八项:"+m.group(8)  
                            print "第九项:"+m.group(9)  
                            print "第十项:"+m.group(10)  
                            print "第十一项:"+m.group(11)  
                            print "第十二项:"+m.group(12)  
                            \#print "第十三项:"+m.group(13)  
                            \#print "第十四项:"+m.group(14)  
                            \#print "第十五项:"+m.group(15)  
                            \#print "第十六项:"+m.group(16)  
                            \#print "第十七项:"+m.group(17)  
                            \#print "第十八项:"+m.group(18)  
                            db\_conn.query("insert into DUBIJIESUANTONGJI\_%s(OPTIME,CONSUMERID,PRESERVENUM,CURRENTNUM,DUBIOPTYPE,DUBIOPNUM,OPTYPE,OPDETAIL,OPNUM,NAME) values(%d,%d,%d,%d,%d,%d,%d,%d,%d,'%s')"%(tabletime,int(m.group  
  
  
(2)),int(m.group(3)),int(m.group(4)),int(m.group(5)),int(m.group(6)),int(m.group(8)),int(m.group(9)),int(m.group(10)),int(m.group(11)),str(m.group(12))))  
                            \#print  'insert into DUBIJIESUANTONGJI\_%s(OPTIME,CONSUMERID,PRESERVENUM,CURRENTNUM,DUBIOPTYPE,DUBIOPNUM,OPTYPE,OPDETAIL,OPNUM,NAME) values(%d,%d,%d,%d,%d,%d,%d,%d,%d,%s)'%(tabletime,int(m.group(2)),int  
  
  
(m.group(3)),int(m.group(4)),int(m.group(5)),int(m.group(6)),int(m.group(7)),int(m.group(8)),int(m.group(9)),int(m.group(10)),str(m.group(11)))  
  
  
        a\_file.close()  
        \#db\_conn.commit()  
        \#db\_cursor.close()  
        db\_conn.close()  
main()  
\#if \_\_name\_\_ == "\_\_main\_\_":  
        \#main()  
  
  
======================================================================================================================  
main20160331.py  
\#!/usr/bin/env python  
\#coding=gbk  
import sys  
import MySQLdb  
import datetime  
import time  
optmap = \{   
                'dbuser' : 'haoren',  
                'dbpass' : 'euwdlcmdwkd',  
                'dbhost' : '172.27.6.12',  
                'dbport' : 3306,  
                'dbname' : 'JIESUANDB'  
                 \}  
  
  
global connect  
global cursor  
def mysql\_connect():  
        try:  
                global connect  
                global cursor  
                connect = MySQLdb.connect(user=optmap\['dbuser'\], passwd=optmap\['dbpass'\], host=optmap\['dbhost'\], port=optmap\['dbport'\], db=optmap\['dbname'\])  
                cursor=connect.cursor()  
                \#gdb\_conn.query("use %s"%optmap\['dbname'\])  
                print "Mysql Connect Success:%s %s=%s %s" %(optmap\['dbhost'\], optmap\['dbuser'\], optmap\['dbpass'\], optmap\['dbname'\])  
                return  
        except MySQLdb.Error,e:  
                print "Mysql ERROR %d:%s"  %(e.args\[0\], e.args\[1\])  
        return  
  
  
def mysql\_close():  
        global connect  
        global cursor  
        connect.commit()  
        cursor.close()  
        connect.close()  
        return  
  
  
def checkOne(table,userID):  
        global cursor  
        \#mysql\_connect()  
        sql="SELECT \`INDEX\`,\`PRESERVENUM\`,\`CURRENTNUM\`,\`OPTYPE\` FROM \`%s\` WHERE \`CONSUMERID\`=%d ORDER BY \`OPTIME\`,\`INDEX\`"%(table,userID)  
        cursor.execute(sql);  
        i=0  
        right=0  
        increment\_total = 0  
        consume\_total = 0  
        charge\_coins = 0  
        first=-10086  
        last=0  
        increment\_abs=0  
        for (index,pre,cur,type) in cursor:  
                \#print(index,pre,cur)  
                if(first==-10086):  
                        first=pre  
                last = cur  
                if(cur-pre>0):  
                        increment\_total+= cur - pre  
                else:  
                        consume\_total+= cur - pre  
                if(type==1002 or type==1016):  
                        charge\_coins+=cur-pre  
                if(right!=pre and i!=0):  
                        print("Error Index:%d"%i)  
                        print(index,pre,cur)  
                i=index  
                right=cur  
        increment\_abs = last-first-charge\_coins  
        one = (userID,increment\_total,consume\_total,charge\_coins,last-first,increment\_abs,first,last)  
  
  
        return one;  
  
  
def checkAll(tableName):  
        global cursor  
        sql = "SELECT \`CONSUMERID\` FROM \`%s\` GROUP BY \`CONSUMERID\`;"%(tableName)  
        cursor.execute(sql)  
        print("doing...")  
        all =\[\]  
        for userId in cursor:  
                all.append(checkOne(tableName,userId\[0\]))  
                \#print(userId)  
        print("finish.")  
        all.sort(key=lambda x:x\[5\],reverse=True)  
        fileOut = open("./%s.csv"%(tableName),"w+")  
        fileOut.writelines("%-12s,%-12s,%-12s,%-12s,%-12s,%-12s,%-12s,%-12s\\n"%("用户ID","总增加","总消耗","总充值","实际增加","绝对增加","昨日结余","今日结余"))  
        for one in all:  
                \#str="%12d,%12d,%12d,%12d,%12d,%12d,%12d,%12d\\n"%(one\[0\],one\[1\]/100,one\[2\]/100,one\[3\]/100,one\[4\]/100,one\[5\]/100,one\[6\]/100,one\[7\]/100);  
                str="%d,%d,%d,%d,%d,%d,%d,%d\\n"%(one\[0\],one\[1\]/100,one\[2\]/100,one\[3\]/100,one\[4\]/100,one\[5\]/100,one\[6\]/100,one\[7\]/100);  
                \#str="%d,%.2f,%.2f,%.2f,%.2f,%.2f,%.2f,%.2f\\n"%(one\[0\],one\[1\]/100,one\[2\]/100,one\[3\]/100,one\[4\]/100,one\[5\]/100,one\[6\]/100,one\[7\]/100);  
  
  
                fileOut.writelines(str)  
        fileOut.close()  
        return  
  
  
def main():  
        mysql\_connect()  
        cur\_day = time.strftime("%Y%m%d", time.localtime(time.time()-86400))  
        log\_day = time.strftime('%y%m%d', time.localtime(time.mktime(time.strptime(cur\_day, '%Y%m%d'))))  
        logTableName = "DUBIJIESUANTONGJI\_%s" %log\_day  
        checkAll(logTableName)  
        mysql\_close()  
  
  
main()  
\#SELECT \* FROM USERCOINSSTATISTICS\_160327  
  
=========================================================================  
cat  transfercoin.py  
\#!/usr/bin/env python  
\#encoding=gb2312  
import sys  
import urllib2  
import socket  
import json  
  
  
def geturl(id):  
    \#  
    urltemple = 'http://172.27.6.27:8080/dudu.php?cmd=145&json=\{"userid":%s,"op":0,"coin":%d,"orderId":"","accounttype":3\}'  
    url= urltemple % (id, 0)  
    print url  
    try:  
        res=urllib2.urlopen(url).read()  
        print res  
        hjson = json.loads(res)  
        print url,res, hjson\["usercoin"\]  
        \#\{"accounttype":3,"coin":100,"coinpool":100,"op":0,"orderId":"","retcode":0,"usercoin":998906970,"userid":21001035\}  
        url= urltemple % (id, hjson\["usercoin"\])  
        \#url= urltemple % (id, 0)  
        res = urllib2.urlopen(url).read()  
        print url,res, hjson\["usercoin"\]  
    except:  
        print 'open url error %s' % id  
      
socket.setdefaulttimeout(1)  
\#geturl(21001035)  
  
  
f = open("imid.txt")             \# 返回一个文件对象  
line = f.readline()             \# 调用文件的 readline()方法  
while line:  
    print line,                 \# 后面跟 ',' 将忽略换行符  
    \# print(line, end = '')　　　\# 在 Python 3中使用  
    userid=line.rstrip()  
    for i in range(1,2):  
        geturl(userid)  
    line = f.readline()  
  
  
f.close()  
\#print "\\n按任意键结束"  
\#sys.stdin.readline()  
  
  
======================================================================================================  
  
批量查询用户昵称  
  
  
%pyspark  
  
  
\#encoding=gbk  
\#-\*-coding:gbk-\*-  
  
  
import sys  
\#import MySQLdb  
import mysql.connector  
import pandas as pd  
import datetime  
import time  
  
  
  
  
optmap = \{   
                'dbuser' : 'haoren',  
                'dbpass' : 'frewqasd',  
                'dbhost' : '172.26.2108',  
                'dbport' : 3306,  
                'dbname' : 'lsdb'  
                 \}  
  
  
optmap1 = \{   
                'dbuser' : 'haoren',  
                'dbpass' : 'frewqasd',  
                'dbhost' : '172.26.2105',  
                'dbport' : 3306,  
                'dbname' : 'PKDB'  
                 \}  
  
def sql\_select(reqsql):  
    ret = ''  
    try:  
        db\_conn = mysql.connector.connect(user=optmap\['dbuser'\], password=optmap\['dbpass'\], host=optmap\['dbhost'\], port=optmap\['dbport'\], database=optmap\['dbname'\])  
        db\_cursor=db\_conn.cursor()  
        count = db\_cursor.execute(reqsql)  
        ret = db\_cursor.fetchall()  
    except mysql.connector.Error as e:  
        print ('Error : \{\}'.format(e))  
    finally:  
        db\_cursor.close()  
        db\_conn.close  
        return ret  
  
  
  
  
def sql\_select1(reqsql1):  
    ret = ''  
    try:  
        db\_conn1 = mysql.connector.connect(user=optmap1\['dbuser'\], password=optmap1\['dbpass'\], host=optmap1\['dbhost'\], port=optmap1\['dbport'\], database=optmap1\['dbname'\])  
        db\_cursor1=db\_conn1.cursor()  
        count = db\_cursor1.execute(reqsql1)  
        ret1 = db\_cursor1.fetchall()  
    except mysql.connector.Error as e:  
        print ('Error : \{\}'.format(e))  
    finally:  
        db\_cursor1.close()  
        db\_conn1.close  
        return ret1  
  
  
\#批量查询用户的昵称  
def getnickname(uid):  
    id = int(uid)%10  
    reqsql = "select CHANNELNICKNAME from  CHARBASE%d where ID=%d" %(id,uid)  
    \#reqsql = "select NICKNAME from  CHARBASE%d where ID=%d" %(id,uid)  
    ret = sql\_select(reqsql)  
    return ret  
  
\#中括号内填写用户内部ID，用逗号隔开  
  
userlist = \[67110207, 90528820,90489527\]  
  
  
for i in userlist:  
    \#(功能1)查询用户昵称  
    ret1 = getnickname(i)  
    ss = ret1\[0\]\[0\]  
    ss =  ss.encode('unicode-escape').decode('string\_escape')  
     nick\_name = ss.decode('gbk')  
    print i,",",nick\_name  
  
  
===============================================================================================================  
  
所有用户充值统计邮件  
  
%pyspark  
\#用户消费查询  
  
  
import sys  
\#import MySQLdb  
import mysql.connector  
import pandas as pd  
import datetime  
import time  
import urllib  
import urllib2  
  
  
optmap = \{   
                'dbuser' : 'haoren',  
                'dbpass' : 'frewqasd',  
                'dbhost' : '172.26.2104',  
                'dbport' : 3306,  
                'dbname' : 'lsdb'  
                 \}  
  
  
\#另外一种配色方案 ff9900 00B2EE FF33FF EEEEEE  
def json2html(title, data):  
    \#str = '<html><head><meta charset="GBK"></head><body>'  
    str = '<table border=1 borderColor=\#D3D8E0 width=1000 align=left >'  
    if title:  
        str += '<tr>'  
        for col in range(len(title)):  
            str += '<td align=center bgColor=\#00B2EE>' + title\[col\] + '</td>'  
        str += '</tr>'  
      
    rowcount = 0  
    for row in range(len(data)):  
        rowcount+=1  
        str += '<tr>'  
        for col in range(len(data\[row\])):  
            if rowcount % 2 == 0:  
                str += '<td align=center bgColor=\#e3efff>' + data\[row\]\[col\] + '</td>'  
            else:  
                str += '<td align=center bgColor=\#f5f9ff>' + data\[row\]\[col\] + '</td>'  
          
        str += '</tr>'  
    \#str += '</table></body></html>'  
    str += '</table>'  
    return str;  
  
  
def sendmail(mailto, subject, body):  
    \#发起请求的url  
    post\_url = 'http://10.13.4.5/sendmail/send.php';  
       
    postData = 'mail\_to=' + mailto + '&smtp\_subject=' + subject + '&mailtype=HTML&body=' + body  
    req = urllib2.Request(post\_url)  
    response = urllib2.urlopen(req, postData)  
    \#打印返回值  
    print response.read()  
  
  
def sql\_select(reqsql):  
    ret = ''  
    try:  
        db\_conn = mysql.connector.connect(user=optmap\['dbuser'\], password=optmap\['dbpass'\], host=optmap\['dbhost'\], port=optmap\['dbport'\], database=optmap\['dbname'\])  
        db\_cursor=db\_conn.cursor()  
        count = db\_cursor.execute(reqsql)  
        ret = db\_cursor.fetchall()  
    except mysql.connector.Error as e:  
        print ('Error : \{\}'.format(e))  
    finally:  
        db\_cursor.close()  
        db\_conn.close  
        return ret  
      
\#用户充值  
def getcharge(startday, endday):  
    strdate = startday.strftime("%y%m%d")  
    enddate = endday.strftime("%y%m%d")  
    tsstart=time.mktime(startday.timetuple())  
    tsend=time.mktime(endday.timetuple())  
      
    daycomsume = 0.0  
    pc = 0.0  
    wifi = 0.0  
    apple = 0.0  
    bag = 0.0  
    dayuser = 0  
    dayusersum = 0  
    pcuser = 0  
    wifiuser = 0  
    appleuser = 0  
    baguser = 0  
    \#type= 22-wifi充值 23-用户直充|充值豪礼 0-点数消耗 19-苹果充值 -1-所有  
    \#对应产品日报邮件中 0-用户pc版 22-用户app版 23-用户直充  
    type = -1  
    for n in range(0,20):  
        if type == -1:  
            \#总计  
            reqsql = "select TYPE,SUM(CONSUME/100) AS ALLCOMSUME from \`USERCONSUMPTIONRECORD%d\` where TIME>=%d AND TIME < %d GROUP BY TYPE" % (n, tsstart, tsend)  
            print reqsql  
            ret = sql\_select(reqsql)  
            print ret  
            \#print ret\[0\]\[0\]  
            if ret\[0\]\[0\] is not None:  
                for i in range(len(ret)):  
                    ctype = int(ret\[i\]\[0\])  
                    if ctype == 0:  
                        pc = pc + float(ret\[i\]\[1\])  
                    elif ctype == 19:  
                        apple = apple + float(ret\[i\]\[1\])   
                    elif ctype == 22:  
                        wifi = wifi + float(ret\[i\]\[1\])  
                    elif ctype == 23:  
                        bag = bag + float(ret\[i\]\[1\])  
                    daycomsume = daycomsume + float(ret\[i\]\[1\])  
        else:  
            \#充值  
            reqsql = "select SUM(CONSUME/100) AS ALLCOMSUME from \`USERCONSUMPTIONRECORD%d\` where TYPE=%d AND TIME>=%d AND TIME < %d" % (n, type, tsstart, tsend)  
            print reqsql  
            ret = sql\_select(reqsql)  
            print ret  
            \#print ret\[0\]\[0\]  
            if ret\[0\]\[0\] is not None:  
                daycomsume = daycomsume + float(ret\[0\]\[0\])  
        \#人数  
        if type == -1:  
            reqsql = "select TYPE, COUNT(DISTINCT USERID) from \`USERCONSUMPTIONRECORD%d\` where TIME>=%d AND TIME < %d GROUP BY TYPE" % (n, tsstart, tsend)  
            ret = sql\_select(reqsql)  
            if ret\[0\]\[0\] is not None:  
                for i in range(len(ret)):  
                    ctype = int(ret\[i\]\[0\])  
                    if ctype == 0:  
                        pcuser = pcuser + int(ret\[i\]\[1\])  
                    elif ctype == 19:  
                        appleuser = appleuser + int(ret\[i\]\[1\])   
                    elif ctype == 22:  
                        wifiuser = wifiuser + int(ret\[i\]\[1\])  
                    elif ctype == 23:  
                        baguser = baguser + int(ret\[i\]\[1\])  
                    dayusersum = dayusersum + int(ret\[i\]\[1\])  
          
        reqsql = "select COUNT(DISTINCT USERID) from \`USERCONSUMPTIONRECORD%d\` where TIME>=%d AND TIME < %d" % (n, tsstart, tsend)  
        ret = sql\_select(reqsql)  
        if ret\[0\]\[0\] is not None:  
            dayuser = dayuser + int(ret\[0\]\[0\])  
    print "strdate, dayuser, daycomsume, pc, wifi, bag, apple, pcuser, wifiuser, baguser, appleuser"  
    print strdate, dayuser, daycomsume, pc, wifi, bag, apple, pcuser, wifiuser, baguser, appleuser  
      
    title = ("项目", "合计", "用户pc版", "用户app版","用户直充", "苹果充值", "平台核对数值")  
    pt = round(pc+ wifi+ bag)  
    ptuser = int(pcuser + wifiuser + baguser)  
    \#data = (("充值金额", daycomsume, pc, wifi, bag, apple, pt), ("付费账号", dayuser, pcuser, wifiuser, baguser, appleuser, ptuser))  
    data = (("充值金额", str(daycomsume), str(pc), str(wifi), str(bag), str(apple), str(pt)),   
            ("付费账号", str(dayuser), str(pcuser), str(wifiuser), str(baguser), str(appleuser), str(ptuser)))  
    print title  
    print data  
    html = json2html(title, data)  
    \#print html  
    sendmail("hli@haoren.com,we@haoren.com", strdate + "用户充值日报", html)  
    \#sendmail("h@haoren.com", strdate + "用户充值日报", html)  
  
  
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  
  
  
\#测试  
\#html = '<html><body><table border=1 borderColor=\#D3D8E0 width=1000 align=left ><tr><td align=center bgColor=\#00B2EE>项目</td><td align=center bgColor=\#00B2EE>合计</td><td align=center bgColor=\#00B2EE>用户pc版</td><td align=center   
  
  
bgColor=\#00B2EE>用户app版</td><td align=center bgColor=\#00B2EE>用户直充</td><td align=center bgColor=\#00B2EE>苹果充值</td></tr><tr><td align=center bgColor=\#f5f9ff>充值金额</td><td align=center bgColor=\#f5f9ff>167765.5</td><td   
  
  
align=center bgColor=\#f5f9ff>114150.5</td><td align=center bgColor=\#f5f9ff>3928.0</td><td align=center bgColor=\#f5f9ff>49582.0</td><td align=center bgColor=\#f5f9ff>105.0</td></tr><tr><td align=center bgColor=\#e3efff>付费账号</td><td   
  
  
align=center bgColor=\#e3efff>430</td><td align=center bgColor=\#e3efff>348</td><td align=center bgColor=\#e3efff>48</td><td align=center bgColor=\#e3efff>74</td><td align=center bgColor=\#e3efff>2</td></tr></table></body></html>'  
\#sendmail("h@haoren.com", "人民币日报", html)  
  
  
today = datetime.date.today()  
\#today = datetime.date(2017,7,24)  
yesterday = today - datetime.timedelta(days=1)  
fromDayToDay(yesterday, 1, getcharge)  
  
  
  
%pyspark  
import sys  
\#import MySQLdb  
import mysql.connector  
import pandas as pd  
import datetime  
import time  
  
  
optmap = \{   
    'dbuser' : 'haoren',  
    'dbpass' : 'frewqasd',  
    'dbhost' : '172.26.212',  
    'dbport' : 3306,  
    'dbname' : 'JIESUANDB',  
    \}  
  
  
def sql\_select(reqsql):  
    ret = ''  
    try:  
        db\_conn = mysql.connector.connect(user=optmap\['dbuser'\], password=optmap\['dbpass'\], host=optmap\['dbhost'\], port=optmap\['dbport'\], database=optmap\['dbname'\])  
        db\_cursor=db\_conn.cursor()  
        \#db\_conn.query("use %s"%optmap\['dbname'\])  
          
        print db\_cursor  
        count = db\_cursor.execute(reqsql)  
        ret = db\_cursor.fetchall()  
    except mysql.connector.Error as e:  
        print ('Error : \{\}'.format(e))  
    finally:  
        db\_cursor.close()  
        db\_conn.close  
        return ret  
      
def getusercharge(userid, startday):  
    strdate = startday.strftime("%y%m%d")  
    reqsql = "select CONSUMERID,SUM(DUBIOPNUM) from \`DUBIJIESUANTONGJI\_%s\` where CONSUMERID=%u AND DUBIOPTYPE=1 AND (OPTYPE=1016 OR OPTYPE=1020 OR OPTYPE=1021)" % (strdate, int(userid))  
    print reqsql  
    ret = sql\_select(reqsql)  
    print ret  
    if ret\[0\]\[0\] is not None:  
        return float(ret\[0\]\[1\])/100.0  
    else:  
        return 0  
          
startdate = datetime.date(2017, 8, 1)  
getusercharge(21001034, startdate)  
  
  
=================================================================================================================  
%pyspark  
  
\#用户金币查询  
  
import sys  
\#import MySQLdb  
import mysql.connector  
import pandas as pd  
import datetime  
import time  
  
optmap = \{   
                'dbuser' : 'haoren',  
                'dbpass' : 'frewqasd',  
                'dbhost' : '172.26.2105',  
                'dbport' : 3306,  
                'dbname' : 'PKDB'  
                 \}  
  
  
\#def sql\_select(reqsql):  
\#    try:  
\#        db\_conn = MySQLdb.connect(user=optmap\['dbuser'\], passwd=optmap\['dbpass'\], host=optmap\['dbhost'\], port=optmap\['dbport'\], db=optmap\['dbname'\])  
\#        db\_cursor=db\_conn.cursor()  
\#        db\_conn.query("use %s"%optmap\['dbname'\])  
\#        count = db\_cursor.execute(reqsql)  
\#        ret = db\_cursor.fetchall()  
\#  
\#        db\_cursor.close()  
\#        db\_conn.close  
\#        return ret  
\#    except MySQLdb.Error,e:  
\#        print "Mysql ERROR %d:%s"  %(e.args\[0\], e.args\[1\])  
\#    return ''  
  
  
  
  
def sql\_select(reqsql):  
    ret = ''  
    try:  
        db\_conn = mysql.connector.connect(user=optmap\['dbuser'\], password=optmap\['dbpass'\], host=optmap\['dbhost'\], port=optmap\['dbport'\], database=optmap\['dbname'\])  
        db\_cursor=db\_conn.cursor()  
        count = db\_cursor.execute(reqsql)  
        ret = db\_cursor.fetchall()  
    except mysql.connector.Error as e:  
        print ('Error : \{\}'.format(e))  
    finally:  
        db\_cursor.close()  
        db\_conn.close  
        return ret  
  
  
  
  
\#查询某天此用户的收入  
  
def getincome(startday,endday,userid):  
         begin = datetime.datetime.strptime(startday,'%Y-%m-%d')  
         end = datetime.datetime.strptime(endday,'%Y-%m-%d')  
        goldsum = 0  
        i = int(userid) % 10  
        \#reqsql = "select sum(DURATION)/3600  from PERFORMERSHOWTIMERECORD%u  where PERFORMERID=%u and STARTTIME>=unix\_timestamp('%s') and STARTTIME <unix\_timestamp('%s')" % (i, int(userid),begin,end)  
        reqsql = "select from\_unixtime(STARTTIME,'%%Y-%%m-%%d') as daytime ,sum(DURATION)/3600  from PERFORMERSHOWTIMERECORD%u  where PERFORMERID=%u and STARTTIME>=unix\_timestamp('%s') and STARTTIME <unix\_timestamp('%s') group by  daytime" % (i, int(userid),begin,end)  
        \#print reqsql  
        ret = sql\_select(reqsql)  
        \#print ret  
        for m in ret:  
            print m\[0\] ,m\[1\]  
            if m\[1\] is not None:  
                    goldsum = goldsum + float(m\[1\])  
        print goldsum  
  
  
userlist = \[93706458\]  
  
for p in userlist:  
    print "\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#下面是 %s 的数据\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#" %p  
    getincome('2017-08-14','2017-09-10',p)   
  
  
=============================================================================================================  
%pyspark  
  
\#查询认证用户  
  
import sys  
\#import MySQLdb  
import mysql.connector  
import pandas as pd  
import datetime  
import time  
  
optmap = \{   
                'dbuser' : 'haoren',  
                'dbpass' : 'frewqasd',  
                'dbhost' : '172.26.2108',  
                'dbport' : 3306,  
                'dbname' : 'lsdb'  
                 \}  
  
optmap1 = \{   
                'dbuser' : 'haoren',  
                'dbpass' : 'frewqasd',  
                'dbhost' : '172.26.2105',  
                'dbport' : 3306,  
                'dbname' : 'PKDB'  
                 \}  
  
  
def sql\_select(reqsql):  
    ret = ''  
    try:  
        db\_conn = mysql.connector.connect(user=optmap\['dbuser'\], password=optmap\['dbpass'\], host=optmap\['dbhost'\], port=optmap\['dbport'\], database=optmap\['dbname'\])  
        db\_cursor=db\_conn.cursor()  
        count = db\_cursor.execute(reqsql)  
        ret = db\_cursor.fetchall()  
    except mysql.connector.Error as e:  
        print ('Error : \{\}'.format(e))  
    finally:  
        db\_cursor.close()  
        db\_conn.close  
        return ret  
  
  
  
def sql\_select1(reqsql1):  
    ret = ''  
    try:  
        db\_conn1 = mysql.connector.connect(user=optmap1\['dbuser'\], password=optmap1\['dbpass'\], host=optmap1\['dbhost'\], port=optmap1\['dbport'\], database=optmap1\['dbname'\])  
        db\_cursor1=db\_conn1.cursor()  
        count = db\_cursor1.execute(reqsql1)  
        ret1 = db\_cursor1.fetchall()  
    except mysql.connector.Error as e:  
        print ('Error : \{\}'.format(e))  
    finally:  
        db\_cursor1.close()  
        db\_conn1.close  
        return ret1  
          
\#定义查询所有用户  
  
  
def getuser():  
        reqsql = "select PERFORMERID from PERFORMERINFO where PERFORMERTYPE=1"  
        \#print reqsql  
        ret = sql\_select(reqsql)   
        return ret  
  
\#查询用户的当前金币  
def getgold(userid):  
    i = int(userid) % 10  
    reqsql1 = "select GOLDREFER/100 from CHARCOIN%u where ID=%u" %(i, int(userid))  
    \#print reqsql  
    ret1 = sql\_select(reqsql1)   
    \#print ret1  
    if len(ret1) > 0:  
        return ret1\[0\]\[0\]  
    else:  
        return None  
  
  
\#定义查询用户最后上麦时间  
  
def getlasttime(uid):  
    m = int(uid) % 10  
    reqsql2="select from\_unixtime(STARTTIME,'%%Y-%%m-%%d') from  PERFORMERSHOWTIMERECORD%u  where PERFORMERID=%u order by STARTTIME desc limit 1 " %(m,uid)  
    ret2 = sql\_select1(reqsql2)  
    if len(ret2) >0:  
        return ret2\[0\]\[0\]  
    else:  
        return None  
      
   
ret = getuser()  
print "用户ID，留存金币，最后上麦时间"  
for p in ret:  
        uid = p\[0\]  
        \#print uid  
        ret3 = getgold(uid)  
        \#print ret3  
        ret4 = getlasttime(uid)  
        \#print ret4   
        print uid,",",ret3,",",ret4  
  
  
  
  
======================================================================================================================  
%pyspark  
\#用户人民 币查询  
  
import sys  
\#import MySQLdb  
import mysql.connector  
import pandas as pd  
import datetime  
import time  
  
optmap = \{   
                'dbuser' : 'haoren',  
                'dbpass' : 'frewqasd',  
                'dbhost' : '172.26.212',  
                'dbport' : 3306,  
                'dbname' : 'JIESUANDB'  
                 \}  
  
def sql\_select(reqsql):  
    ret = ''  
    try:  
        db\_conn = mysql.connector.connect(user=optmap\['dbuser'\], password=optmap\['dbpass'\], host=optmap\['dbhost'\], port=optmap\['dbport'\], database=optmap\['dbname'\])  
        db\_cursor=db\_conn.cursor()  
        count = db\_cursor.execute(reqsql)  
        ret = db\_cursor.fetchall()  
    except mysql.connector.Error as e:  
        print ('Error : \{\}'.format(e))  
    finally:  
        db\_cursor.close()  
        db\_conn.close  
        return ret  
  
 \#查询某天此用户的收入  
  
def getincome(startday,endday,userid):  
         begin = datetime.datetime.strptime(startday,'%Y-%m-%d')  
         end = datetime.datetime.strptime(endday,'%Y-%m-%d')  
        goldsum = 0  
        for i in range((end - begin).days+1):  
                \#print i  
                day = begin + datetime.timedelta(days=i)   
                day1 = day.strftime('%y%m%d')  
                \#下面一句用于查询分成后用户的收入  
                reqsql = "select sum(SINGERRECVGOLD)/100 from \`JIESUANTONGJI\_%s\` where SINGERID=%u" % (day1, int(userid))  
                \#下面一句用于查询分成前用户消费多少人民币  
                \#reqsql = "select sum(CONSUMECOIN)/100 from \`JIESUANTONGJI\_%s\` where SINGERID=%u" % (day1, int(userid))  
                \#print reqsql  
                ret = sql\_select(reqsql)  
                  
                print day1, ret\[0\]\[0\]  
                if ret\[0\]\[0\] is not None:  
                    goldsum = goldsum + float(ret\[0\]\[0\])  
        print goldsum  
                \#return  float(ret\[0\]\[0\])/100  
  
getincome('2017-09-1','2017-09-28',93454519)  
  
==================================================================================================================  
%pyspark  
  
\#查询认证用户  
  
import sys  
\#import MySQLdb  
import mysql.connector  
import pandas as pd  
import datetime  
import time  
  
optmap = \{   
                'dbuser' : 'haoren',  
                'dbpass' : 'frewqasd',  
                'dbhost' : '172.26.2.8',  
                'dbport' : 3306,  
                'dbname' : 'lsdb'  
                 \}  
  
optmap1 = \{   
                'dbuser' : 'haoren',  
                'dbpass' : 'frewqasd',  
                'dbhost' : '172.26.2.5',  
                'dbport' : 3306,  
                'dbname' : 'PKDB'  
                 \}  
  
def sql\_select(reqsql):  
    ret = ''  
    try:  
        db\_conn = mysql.connector.connect(user=optmap\['dbuser'\], password=optmap\['dbpass'\], host=optmap\['dbhost'\], port=optmap\['dbport'\], database=optmap\['dbname'\])  
        db\_cursor=db\_conn.cursor()  
        count = db\_cursor.execute(reqsql)  
        ret = db\_cursor.fetchall()  
    except mysql.connector.Error as e:  
        print ('Error : \{\}'.format(e))  
    finally:  
        db\_cursor.close()  
        db\_conn.close  
        return ret  
  
  
def sql\_select1(reqsql1):  
    ret = ''  
    try:  
        db\_conn1 = mysql.connector.connect(user=optmap1\['dbuser'\], password=optmap1\['dbpass'\], host=optmap1\['dbhost'\], port=optmap1\['dbport'\], database=optmap1\['dbname'\])  
        db\_cursor1=db\_conn1.cursor()  
        count = db\_cursor1.execute(reqsql1)  
        ret1 = db\_cursor1.fetchall()  
    except mysql.connector.Error as e:  
        print ('Error : \{\}'.format(e))  
    finally:  
        db\_cursor1.close()  
        db\_conn1.close  
        return ret1  
  
\#定义查询认证用户函数  
def renzhengsingger(startday,endday):  
     t1 = int(time.mktime(time.strptime(startday,'%Y-%m-%d %H:%M:%S')) )  
     t2 = int(time.mktime(time.strptime(endday,'%Y-%m-%d %H:%M:%S')))  
    reqsql = "select PERFORMERID,from\_unixtime(ADDTIME) from PERFORMERINFO where ADDTIME >=%s and ADDTIME < %s" %(t1,t2)  
    ret = sql\_select(reqsql)  
    for i in ret:  
        \#print i\[0\]  
        id = int(i\[0\])%10  
        reqsql1 = "select sum(DURATION)/3600 from PERFORMERSHOWTIMERECORD%d where STARTTIME >=%s and STARTTIME <%s and PERFORMERID=%d" %(id,t1,t2,i\[0\])  
        \#print reqsql1  
        ret1 = sql\_select1(reqsql1)  
        print i\[0\],",",i\[1\],",",ret1\[0\]\[0\]  
          
renzhengsingger('2017-11-01 00:00:00','2017-11-2 23:00:00')  
  
  
==================================================================================================================  
%pyspark  
\#频道签约用户信息    
  
  
import sys  
\#import MySQLdb  
import mysql.connector  
import pandas as pd  
import datetime  
import time  
import urllib  
import urllib2  
  
optmap = \{   
                'dbuser' : 'haoren',  
                'dbpass' : 'frewqasd',  
                'dbhost' : '172.26.2.8',  
               
                'dbport' : 3306,  
                'dbname' : 'lsdb'  
                \}  
                   
\#def sql\_select(reqsql):  
\#   try:  
\#       db\_conn = MySQLdb.connect(user=optmap\['dbuser'\], passwd=optmap\['dbpass'\], host=optmap\['dbhost'\], port=optmap\['dbport'\], db=optmap\['dbname'\])  
\#       db\_cursor=db\_conn.cursor()  
\#       db\_conn.query("use %s"%optmap\['dbname'\])  
\#       count = db\_cursor.execute(reqsql)  
\#       ret = db\_cursor.fetchall()  
\#       db\_cursor.close()  
\#       db\_conn.close  
\#       return ret  
\#   except MySQLdb.Error,e:  
\#       print "Mysql ERROR %d:%s"  %(e.args\[0\], e.args\[1\])  
\#   return ''  
  
  
def sql\_select(reqsql):  
    ret = ''  
    try:  
        db\_conn = mysql.connector.connect(user=optmap\['dbuser'\], password=optmap\['dbpass'\], host=optmap\['dbhost'\], port=optmap\['dbport'\], database=optmap\['dbname'\])  
        db\_cursor=db\_conn.cursor()  
        count = db\_cursor.execute(reqsql)  
        ret = db\_cursor.fetchall()  
    except mysql.connector.Error as e:  
        print ('Error : \{\}'.format(e))  
    finally:  
        db\_cursor.close()  
        db\_conn.close  
        return ret  
  
  
\#查询一个的 签约用户  
  
def getuser(channelid):  
        reqsql = "select \* from PERFORMERINFO where CHANNELID=%u" %(int(channelid))  
        print reqsql  
        ret = sql\_select(reqsql)   
        \#print ret  
        \#print ret\[0\],ret\[1\]  
        \#print ret\[0\]\[0\] ,ret\[1\]\[0\] ,ret\[2\]\[0\] ,ret\[3\]\[0\]  
        \#print len(ret)  
        return ret  
          
\#查询用户的当前人民币  
def gettime(userid):  
    i = int(userid) % 10  
    \#reqsql = "select sum(DURATION)/3600 from PERFORMERSHOWTIMERECORD%u where  STARTTIME>=unix\_timestamp('2017-07-14 00:00:00') and STARTTIME <=unix\_timestamp('2017-08-14 00:00:00') and  PERFORMERID=%u" %(i, int(userid))  
    reqsql = "select GOLDREFER/100 from CHARCOIN%u where ID=%u" %(i, int(userid))  
    \#print reqsql  
    ret1 = sql\_select(reqsql)   
    \#print ret1  
    if len(ret1) > 0:  
        return ret1\[0\]\[0\]  
    else:  
        return None  
  
  
  
  
ret2 = getuser(3644080)    
  
  
for i in ret2 :  
    \#print "i===" ,i  
    userid = i\[0\]  
    print userid  
    ret1 = gettime(userid)  
    print userid ,str(ret1)  
  
=============================================================================================================  
%pyspark  
  
  
\#查询认证用户  
import sys  
\#import MySQLdb  
import mysql.connector  
import pandas as pd  
import datetime  
import time  
  
optmap = \{   
                'dbuser' : 'haoren',  
                'dbpass' : 'frewqasd',  
                'dbhost' : '172.26.2.15',  
                'dbport' : 3306,  
                'dbname' : 'PKDB'  
                 \}  
  
  
def sql\_select(reqsql):  
    ret = ''  
    try:  
        db\_conn = mysql.connector.connect(user=optmap\['dbuser'\], password=optmap\['dbpass'\], host=optmap\['dbhost'\], port=optmap\['dbport'\], database=optmap\['dbname'\])  
        db\_cursor=db\_conn.cursor()  
        count = db\_cursor.execute(reqsql)  
        ret = db\_cursor.fetchall()  
    except mysql.connector.Error as e:  
        print ('Error : \{\}'.format(e))  
    finally:  
        db\_cursor.close()  
        db\_conn.close  
        return ret  
  
  
userlist = \[\]  
def renzhengsingger(startday,endday):  
     t1 = int(time.mktime(time.strptime(startday,'%Y-%m-%d %H:%M:%S')) )  
     t2 = int(time.mktime(time.strptime(endday,'%Y-%m-%d %H:%M:%S')))  
    for n in range(0,10):  
        reqsql = "select PERFORMERID,sum(DURATION)/3600 from PERFORMERSHOWTIMERECORD%d where STARTTIME >=%s and STARTTIME <%s group by PERFORMERID ;" %(n,t1,t2)  
        ret = sql\_select(reqsql)  
          
        userlist.append(ret)  
    \#print userlist      
    for i in range(0,10):  
        for p in userlist\[i\]:  
            print p\[0\],p\[1\]  
              
renzhengsingger('2017-08-28 00:00:00','2017-09-20 00:00:00')  
  
================================================================================================================  
%pyspark  
\#查询用户余额  
  
  
import sys  
\#import MySQLdb  
import mysql.connector  
import pandas as pd  
  
  
optmap = \{   
                'dbuser' : 'haoren',  
                'dbpass' : 'frewqasd',  
                'dbhost' : '172.26.2.14',  
                'dbport' : 3306,  
                'dbname' : 'lsdb'  
                 \}  
  
  
def sql\_select(reqsql):  
    ret = ''  
    try:  
        db\_conn = mysql.connector.connect(user=optmap\['dbuser'\], password=optmap\['dbpass'\], host=optmap\['dbhost'\], port=optmap\['dbport'\], database=optmap\['dbname'\])  
        db\_cursor=db\_conn.cursor()  
        count = db\_cursor.execute(reqsql)  
        ret = db\_cursor.fetchall()  
    except mysql.connector.Error as e:  
        print ('Error : \{\}'.format(e))  
    finally:  
        db\_cursor.close()  
        db\_conn.close  
        return ret  
  
\#def sql\_select(reqsql):  
\#    try:  
\#        db\_conn = MySQLdb.connect(user=optmap\['dbuser'\], passwd=optmap\['dbpass'\], host=optmap\['dbhost'\], port=optmap\['dbport'\], db=optmap\['dbname'\])  
\#        db\_cursor=db\_conn.cursor()  
\#        db\_conn.query("use %s"%optmap\['dbname'\])  
\#        count = db\_cursor.execute(reqsql)  
\#        ret = db\_cursor.fetchall()  
\#  
\#        db\_cursor.close()  
\#        db\_conn.close  
\#        return ret  
\#    except MySQLdb.Error,e:  
\#        print "Mysql ERROR %d:%s"  %(e.args\[0\], e.args\[1\])  
\#    return ''  
      
def getusercoin(userid):  
    i = int(userid) % 10  
    reqsql = "select ID,COINREFER from CHARCOIN%u where ID=%u" % (int(i), int(userid))  
    \#print reqsql  
    ret = sql\_select(reqsql)  
    \#print ret  
    return ret\[0\]  
      
def getall(userlist):  
    userdata = pd.DataFrame(columns=('userid', 'coin'))  
    index = 0  
    for userid in userlist:  
        coins = getusercoin(userid)  
        \#print coins\[0\],coins\[1\]/100.0  
        if coins\[0\] is not None:  
            userdata.loc\[index\] = (str(coins\[0\]), coins\[1\]/100.0)  
        else:  
            userdata.loc\[index\] = (str(userid), 0)  
        index += 1  
        \#print userdata.tail(10)  
          
    df = spark.createDataFrame(userdata)  
    \#df.createOrReplaceTempView('userdata')  
    df.show(50)  
     
\#这里填写用户ID   
userlist = \[  
23443660,  
70461470  
\]  
  
getall(userlist)  
  
===================================================================================================  
%pyspark  
\#查询用户机器ID   
  
  
import sys  
import MySQLdb  
import pandas as pd  
import datetime  
  
  
optmap = \{   
                'dbuser' : 'haoren',  
                'dbpass' : 'frewqasd',  
                'dbhost' : '172.26.2105',  
                'dbport' : 3306,  
                'dbname' : 'PKDB'  
                 \}  
  
  
def sql\_select(reqsql):  
    try:  
        db\_conn = MySQLdb.connect(user=optmap\['dbuser'\], passwd=optmap\['dbpass'\], host=optmap\['dbhost'\], port=optmap\['dbport'\], db=optmap\['dbname'\])  
        db\_cursor=db\_conn.cursor()  
        db\_conn.query("use %s"%optmap\['dbname'\])  
        count = db\_cursor.execute(reqsql)  
        ret = db\_cursor.fetchall()  
  
  
        db\_cursor.close()  
        db\_conn.close  
        return ret  
    except MySQLdb.Error,e:  
        print "Mysql ERROR %d:%s"  %(e.args\[0\], e.args\[1\])  
    return ''  
      
def getusermid(userid, months):  
    i = int(userid) % 50  
    reqsql = "select USERID,MACHINEID from LOGINHISTORY%s%u where USERID=%u group by MACHINEID" % (months,int(i), int(userid))  
    print reqsql  
    ret = sql\_select(reqsql)  
    \#print ret  
    \#print ret\[0\]  
    return ret  
      
def getall(userlist):  
    today = datetime.date.today()  
    months = today.strftime("%Y%m")  
    userdata = pd.DataFrame(columns=('USERID', 'MACHINEID'))  
    index = 0  
    for userid in userlist:  
        coins = getusermid(userid, months)  
        for i in range(len(coins)):  
            \#print coins\[i\]  
            userdata.loc\[index\] = (str(coins\[i\]\[0\]), str(coins\[i\]\[1\]))  
            index += 1  
          
        \#print coins\[0\],coins\[1\]/100.0  
        \#userdata.loc\[index\] = (str(coins\[0\]), coins\[1\]/100.0)  
        \#index += 1  
        \#print userdata.tail(10)  
          
    df = spark.createDataFrame(userdata)  
    \#df.createOrReplaceTempView('userdata')  
    df.show(1000)  
     
\#这里填写用户ID   
userlist = \[  
21896345,  
25806997,  
\]  
getall(userlist)  
  
  
=================================================================================================  
%pyspark  
  
  
from pyspark.sql import Row  
from pyspark.sql.types import \*  
from pyspark.sql.functions import udf  
import MySQLdb  
import mysql\_op  
import datetime  
import time  
from mysql\_op import MySQL  
import pandas as pd  
import numpy as np  
from fastparquet import ParquetFile  
from fastparquet import write  
  
  
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 saveDayBillData(startday, endday):  
    strday = startday.strftime("%Y%m%d")  
    if os.path.exists("/home/haoren/logstatis/billdata"+strday+".parq"):  
        return  
    \#数据库连接参数    
    dbconfig = \{'host':'172.26.212',  
                        'port': 3306,  
                        'user':'haoren',  
                        'passwd':'frewqasd',  
                        'db':'JIESUANDB',  
                        'charset':'utf8'\}  
  
  
    \#连接数据库，创建这个类的实例  
    mysql\_cn= MySQLdb.connect(host=dbconfig\['host'\], port=dbconfig\['port'\],user=dbconfig\['user'\], passwd=dbconfig\['passwd'\], db=dbconfig\['db'\])  
    tsstart=time.mktime(startday.timetuple())  
    tsend=time.mktime(endday.timetuple())  
    strdate = startday.strftime("%y%m%d")  
  
  
    sql = "SELECT OPTIME,CONSUMERID,PRESERVENUM,CURRENTNUM,DUBIOPTYPE,DUBIOPNUM,OPTYPE,OPDETAIL,OPNUM FROM \`DUBIJIESUANTONGJI\_%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/billdata"+strday+".parq", pddf)  
    return  
  
  
def saveBillData():  
    startday = datetime.date(2017, 2, 28)  
    endday = datetime.date(2017, 2, 28)  
    td = endday - startday  
    datelen = td.days + 1  
    \# 获取购物车数据  
    fromDayToDay(startday, datelen, saveDayBillData)  
      
\# 获取Wifi注册数据  
def saveDayWifiPhoneRegData(startday, endday):  
    \#数据库连接参数    
    dbconfig = \{'host':'172.26.2105',  
                        'port': 3306,  
                        'user':'haoren',  
                        'passwd':'frewqasd',  
                        'db':'OTHERDB',  
                        'charset':'utf8'\}  
  
  
    \#连接数据库，创建这个类的实例  
    mysql\_cn= MySQLdb.connect(host=dbconfig\['host'\], port=dbconfig\['port'\],user=dbconfig\['user'\], passwd=dbconfig\['passwd'\], db=dbconfig\['db'\])  
    strday = startday.strftime("%Y%m%d")  
    tsstart=time.mktime(startday.timetuple())  
    tsend=time.mktime(endday.timetuple())  
    strdate = startday.strftime("%y%m%d")  
  
  
    sql = "select USERID from NEW\_WEB\_USER where TIME< %d AND TYPE=17" % (tsend)  
    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/wifiphonereg"+strday+".parq", pddf)  
    return  
  
  
def saveWifiPhoneReg():  
    startday = datetime.date(2016, 12, 1)  
    endday = datetime.date(2016, 12, 1)  
    td = endday - startday  
    datelen = td.days + 1  
    \# 获取购物车数据  
    fromDayToDay(startday, datelen, saveDayWifiPhoneRegData)  
  
  
def getOpTypeName(func):  
    name = OPTypeName.get(func)  
    if name == None:  
        return ""  
    else:  
        return name.decode('utf8')  
      
def getOpDetailName(func, detail):  
    if func == 19:  
        if detail > 10000 and detail < 20000:  
            return "购物车用物品扣人民币".decode('utf8')  
        elif detail > 20000 and detail < 30000:  
            return "购物车回滚".decode('utf8')  
        elif detail > 50000 and detail < 60000:  
            return "红包接龙".decode('utf8')  
        else:  
            name = OpDetailName19.get(detail)  
            if name == None:  
                return ""  
            else:  
                return name.decode('utf8')  
    elif func == 22:  
            name = OpDetailName22.get(detail)  
            if name == None:  
                return ""  
            else:  
                return name.decode('utf8')  
    elif func == 23:  
            name = OpDetailName23.get(detail)  
            if name == None:  
                return ""  
            else:  
                return name.decode('utf8')  
    else:  
        return ""  
  
  
def getDayBillData(startday, endday):  
    global allBillData  
    strday = startday.strftime("%Y%m%d")  
    print strday + '人民币数据'  
    df = spark.read.load("/home/haoren/logstatis/billdata"+strday+".parq")  
    df.show(10)  
    if allBillData == None:  
        allBillData = df  
    else:  
        allBillData = allBillData.unionAll(df)  
    return  
    """  
    \#df.createOrReplaceTempView('billdata')  
    \#df.registerTempTable("billdata")  
    \#sqlret = sqlc.sql("SELECT count(\*) from billdata")  
    \#sqlret.show(1)  
    df2 = df.withColumn('OPTYPENAME', udf(getOpTypeName)(df.OPTYPE))  
    df2.show(10)  
    df = df2.withColumn('DETAILNAME', udf(getOpDetailName)(df2.OPTYPE, df2.OPDETAIL))  
    df.show(10)  
    df.createOrReplaceTempView('billdata')  
    return  
    """  
      
allBillData=None  
def getBillData():  
    \#startday = datetime.date(2016, 12, 1)  
    \#endday = datetime.date(2016, 12, 31)  
    startday = datetime.date(2017, 2, 1)  
    endday = datetime.date(2017, 2, 28)  
    td = endday - startday  
    datelen = td.days + 1  
    \# 获取购物车数据  
    fromDayToDay(startday, datelen, getDayBillData)  
    global allBillData  
    allBillData.registerTempTable('billdata')  
    """  
    \#保存合并后的数据  
    strmonth = startday.strftime("%Y%m")  
    allBillData.write.parquet("/home/haoren/logstatis/billdata"+strmonth+".parq")  
    allBillData = spark.read.load("/home/haoren/logstatis/billdata"+strmonth+".parq")  
    allBillData.registerTempTable("billdata")  
      
    df=spark.sql("select OPTYPE,DUBIOPTYPE,OPDETAIL, sum(DUBIOPNUM) as sumnum from billdata group by OPTYPE,DUBIOPTYPE,OPDETAIL order by sumnum desc")  
    df.show(5)  
    df2 = df.withColumn('OPTYPENAME', udf(getOpTypeName)(df.OPTYPE))  
    df2.show(5)  
    df = df2.withColumn('DETAILNAME', udf(getOpDetailName)(df2.OPTYPE, df2.OPDETAIL))  
    df.show(5)  
    df.registerTempTable("billdatastatis")  
    """  
    print 'getBillData finish'  
  
  
\# 获取充值数据  
def getChargeInfo(startday, endday):  
    \#数据库连接参数    
    dbconfig = \{'host':'172.26.2104',   
      'port': 3306,   
      'user':'haoren',   
      'passwd':'frewqasd',   
      'db':'lsdb',   
      'charset':'utf8'\}  
       
    \#连接数据库，创建这个类的实例  
    mysql\_cn= MySQLdb.connect(host=dbconfig\['host'\], port=dbconfig\['port'\],user=dbconfig\['user'\], passwd=dbconfig\['passwd'\], db=dbconfig\['db'\])  
    strday = startday.strftime("%Y%m%d")  
    tsstart=time.mktime(startday.timetuple())  
    tsend=time.mktime(endday.timetuple())  
    regdata = pd.DataFrame()  
    for i in range(0, 20):   
        sql = "SELECT \* FROM \`USERCONSUMPTIONRECORD%d\` where TIME > %d AND TIME < %d" % (i, tsstart, tsend)  
        print sql  
        \#pddf = pd.DataFrame()  
        pddf = pd.read\_sql(sql, con=mysql\_cn)  
        \#print pddf.head(5)  
        if len(pddf.index) > 0:  
            regdata = regdata.append(pddf,ignore\_index=True)  
            print regdata.tail(5)  
      
    if len(regdata.index) > 0:  
        print regdata.describe()  
        write("/home/haoren/logstatis/register"+strday+".parq", regdata)  
    mysql\_cn.close()  
    return  
      
def pudf(x):  
    return getOpTypeName(x.OPTYPE)  
      
def getMergeData(strday):  
    dfbill = ParquetFile("/home/haoren/logstatis/billdata"+strday+".parq").to\_pandas()  
    dfwifireg = ParquetFile("/home/haoren/logstatis/wifiphonereg"+strday+".parq").to\_pandas()  
    tempdf = pd.merge(dfbill, dfwifireg, left\_on='CONSUMERID', right\_on='USERID')  
    \#write("/home/haoren/logstatis/analyze"+strday+".parq", tempdf)  
    \#print tempdf.head(10)  
    tempdf\['OPTYPENAME'\] = tempdf.apply(lambda x:getOpTypeName(x.OPTYPE), axis=1)  
    \#print tempdf.head(10)  
    tempdf\['DETAILNAME'\] = tempdf.apply(lambda x:getOpDetailName(x.OPTYPE,x.OPDETAIL), axis=1)  
    df = spark.createDataFrame(tempdf)  
    df.show(10)  
    return df  
      
def analyzeDayBillData(startday, endday):  
    strday = startday.strftime("%Y%m%d")  
    print strday + '人民币数据'  
  
  
    df = spark.read.load("/home/haoren/logstatis/billdata"+strday+".parq")  
    dfwifireg = spark.read.load("/home/haoren/logstatis/wifiphonereg"+strday+".parq")  
    df3 = df.join(dfwifireg, df.CONSUMERID == dfwifireg.USERID)  
    df3.show(10)  
    df3.write.parquet("/home/haoren/logstatis/analyze"+strday+".parq")  
      
    \#df2 = df3.withColumn('OPTYPENAME', udf(getOpTypeName)(df3.OPTYPE))  
    \#df2.show(10)  
    \#df = df2.withColumn('DETAILNAME', udf(getOpDetailName)(df2.OPTYPE, df2.OPDETAIL))  
    \#df.show(10)  
    \#df.createOrReplaceTempView('analyzebilldata')  
    return  
      
def analyzeDayBillData2(startday, endday):  
    strday = startday.strftime("%Y%m%d")  
    print strday + '人民币数据'  
    \#df = spark.read.load("/home/haoren/logstatis/analyze"+strday+".parq")  
    df = getMergeData(strday)  
    return  
    df2 = df.withColumn('OPTYPENAME', udf(getOpTypeName)(df.OPTYPE))  
    df2.show(10)  
    df = df2.withColumn('DETAILNAME', udf(getOpDetailName)(df2.OPTYPE, df2.OPDETAIL))  
    df.show(10)  
    df.createOrReplaceTempView('analyzebilldata')  
    return  
      
def analyzeBillData():  
    startday = datetime.date(2016, 12, 28)  
    endday = datetime.date(2016, 12, 28)  
    td = endday - startday  
    datelen = td.days + 1  
    \# 获取购物车数据  
    fromDayToDay(startday, datelen, analyzeDayBillData2)  
    print 'analyzeBillData finish'  
      
\#saveBillData()  
getBillData()  
\#saveWifiPhoneReg()  
\#analyzeBillData()