Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1809187
  • 博文数量: 636
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 3950
  • 用 户 组: 普通用户
  • 注册时间: 2014-08-06 21:58
个人简介

博客是我工作的好帮手,遇到困难就来博客找资料

文章分类

全部博文(636)

文章存档

2024年(5)

2022年(2)

2021年(4)

2020年(40)

2019年(4)

2018年(78)

2017年(213)

2016年(41)

2015年(183)

2014年(66)

我的朋友

分类: 系统运维

2017-11-03 16:17:25

#!/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 = ''
    str = ''
    if title:
        str += ''
        for col in range(len(title)):
            str += ''
        str += ''
    
    rowcount = 0
    for row in range(len(data)):
        rowcount+=1
        str += ''
        for col in range(len(data[row])):
            if rowcount % 2 == 0:
                str += ''
            else:
                str += ''
        
        str += ''
    #str += '
' + title[col] + '
' + data[row][col] + '' + data[row][col] + '
'
    str += ''
    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 = '

align=center bgColor=#f5f9ff>114150.5


align=center bgColor=#e3efff>430
项目合计用户pc版

bgColor=#00B2EE>用户app版
用户直充苹果充值
充值金额167765.53928.049582.0105.0
付费账号34848742
'
#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         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         #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()

阅读(1646) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~