python读写文件file写入到mysql

cat UserGoldConsumitemDaily.py
#!/usr/bin/env python
#-*-coding:utf-8-*-
#用户消费物品日报
#****************************************使用说明****************************************************
# 内网测试通过命令行参数
# -d 指定统计时间
# -p 指定日志所在目录
# -t 指定临时文件存放目录
# 示例如下:
# [meinv@localhost tongji]$ ./userconsumitemDaily.py -d 20141112 -p /home/meinv/log/ -t /home/meinv/tongji/
# 外网无需加参数,使用默认值
#****************************************使用说明****************************************************


import MySQLdb
import os, sys, re,string
import time, tarfile,getopt


optmap = {
                'dbuser' : 'haoren',
                'dbpass' : '123456789',
                'dbhost' : '172.10.1.103',
                'dbport' : 3306,
                'dbname' : 'MGDB'
                 }
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 DaoJuTongJi20161226.py
#!/usr/bin/env python
#-*-coding:utf-8-*-
#特殊物品明细


import MySQLdb
import os, sys, re,string
import time, tarfile,getopt


optmap = {
                'dbuser' : 'haoren',
                'dbpass' : '123456789',
                'dbhost' : '172.10.1.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 `DAOJUTONGJI_%s` like DAOJUTONGJISAMPLE"%tabletime


        db_conn.query(sql)
        db_conn.query("delete from DAOJUTONGJI_%s"%tabletime)


        if os.path.exists("/tmp/DaoJuTongJi20161226.txt"):
                os.system("rm -f /tmp/DaoJuTongJi20161226.txt")




        file_list2=get_files(dirname,'billserver')
        for file2 in file_list2:
                command = "cat %s/%s | grep -h -w  消耗人民币 |grep -v 人民币消费物品统计 |grep 渠道类型  >> /tmp/DaoJuTongJi20161226.txt"%(dirname,file2)
                os.system(command)


        #结算统计记录放在txt文档里面
        filename='/tmp/DaoJuTongJi20161226.txt'
        record = {}
        a_file = open(filename, 'r')
        #161226-16:23:06 Bill[40268]  INFO: [物品统计]渠道(3839247)等级(2)用户(60303642)赠送(10)个物品(95)给用户(82147138),用户等级(38)认证(1)消耗人民币(100)用户获得人民币(8000)渠道获得人民币(2000)当前礼物最大值(52000)当前渠道(4023568)渠道类型(150)
        #170410-23:01:30 Bill[40268]  INFO: [BillTask.cpp:1771] [物品统计]渠道(3977962)等级(3)用户(90537796)赠送(1)个物品(14)给用户(60062097),用户等级(53)认证(1)消耗人民币(100)用户获得人民币(8000)渠道获得人民币(2000)当前渠道(4001362)渠道类型(150)
        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)
                        #m = re.search("^(\S+) Bill\[\d+\]  INFO: \[BillTask.cpp:1771\] \[物品统计\]渠道\((\d+)\)等级\((\d+)\)用户\((\S+)\)赠送\((\d+)\)个物品\((\d+)\)给用户\((\d+)\),用户等级\((\d+)\)认证\((\d+)\)消耗人民币\((\d+)\)用户获得人民币\((\d+)\)渠道获得人民币\((\d+)\)当前礼物最大值\((\d+)\)当前渠道\((\d+)\)渠道类型\((\d+)\)", a_line)
                        m = re.search("^(\S+) Bill\[\d+\]  INFO: \[BillTask.cpp:1771\] \[物品统计\]渠道\((\d+)\)等级\((\d+)\)用户\((\S+)\)赠送\((\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)
                                #if int(m.group(14)) >0 or int(m.group(15)) >0 :
                          db_conn.query("insert into DAOJUTONGJI_%s(CHANNELID,CHANELLEVEL,PRESENTERID,PRESENTERNUM,ITEMID,SINGERID,SINGERLEVEL,SIGNTYPE,CONSUMECOIN,SINGERRECVGOLD,CHANNELRECVGOLD,CURRENTCHANNEL,CHANNELTYPE) values(%d,%d,%d,%d,%d,%d,%d,%d,%d,%d,%d,%d,%d)"%(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)),int(m.group(11)),int(m.group(12)),int(m.group(13)),int(m.group(14))))


        a_file.close()
        db_conn.commit()
        db_cursor.close()
        db_conn.close()
main()
#if __name__ == "__main__":
#        main()




cat vipactiveusernumtemp.py
#!/usr/bin/env python
#-*-coding:utf-8-*-
#会员登录趋势
import MySQLdb
import os, sys, re, string
import time, tarfile, getopt


optmap = {
                'dbuser' : 'haoren',
                'dbpass' : '123456789',
                'dbhost' : '172.29.1.106',
                'dbport' : 3306,
                'dbname' : 'GMServerDB',
                'logdir' : '/home/haoren/logdir/',
                '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
        temp_simple_active_file_name = '/tmp/vipactiveusernum_%s.txt' %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()


        command = "cat /dev/null > %s" %(temp_simple_active_file_name)
        os.system(command)
        dir_list = get_files(optmap['logdir'], one_day[2:])
        for dir_item in dir_list:
                dir_path = optmap['logdir']+dir_item+'/'+one_day[2:]+'/'
                file_list = get_files(dir_path, optmap['logpattern']+one_day[2:])
                for file_item in file_list:
                        print file_item
                        command = "cat %s%s |awk '/用户登录/' |awk '/vip状态/' >> %s" %(dir_path, file_item, temp_simple_active_file_name)
                        os.system(command)


        db_conn.query("use %s" %optmap['dbname'])
        sql = "drop table if exists VIPACTIVEUSERNUM_%s" %one_day
        print sql
        db_conn.query(sql)


        sql = "create table VIPACTIVEUSERNUM_%s like VIPACTIVEUSERNUM_TEMP_SAMPLE" %one_day
        print sql
        db_conn.query(sql)


        temp_vip_user_login_count = 0
        temp_simple_active_file = open(temp_simple_active_file_name)
        for one_line in temp_simple_active_file.readlines():
                record = {}
                match = re.search("^(\S+) SS\[\d+\] TRACE: 用户登录:imid:(\d+),mac地址:(\d+),ip地址:(\d+),vip状态:(\d+),登录时间:(\d+)(\S+)", one_line)
                if match:
                        record['imid'] = string.atoi(match.group(2))
                        record['mac'] = string.atoi(match.group(3))
                        record['ip'] = string.atoi(match.group(4))
                        record['vip_state'] = string.atoi(match.group(5))
                        record['login_time'] = string.atoi(match.group(6))


                        sql = "insert into VIPACTIVEUSERNUM_%s(ACCOUNT, MAC, IP, VIP_STATE, LOGIN_TIME) values('%d', '%d', '%d', '%d', '%d')" %(one_day, record['imid'], record['mac'], record['ip'], record['vip_state'], record['login_time'])
                        db_conn.query(sql)
                        temp_vip_user_login_count += 1


        print temp_vip_user_login_count
        temp_simple_active_file.close()


#提交事务
        db_conn.commit()


        db_cursor.close()
        db_conn.close()


main()
#if __name__ == "__main__":
#        main()




cat vipuserflowermonthlyold.py
#!/usr/bin/env python
#-*-coding:utf-8-*-
#会员物品库存统计
import MySQLdb
import os, sys, re, string
import time, getopt


optmap = {
                'dbuser' : 'haoren',
                'dbpass' : '123456789',
                'dbhost' : '172.29.1.92',
                'dbhost_gm': '172.29.1.106',
                'dbport' : 3306,
                'dbname' : 'MIDB',
                'dbname_gm' : 'GMServerDB'
                }


def main():
        today = time.strftime("%Y%m%d", time.localtime(time.time()))
        print "正在统计会员物品库存(%s)..." %today


        db_conn = MySQLdb.connect(user=optmap['dbuser'], passwd=optmap['dbpass'], host=optmap['dbhost'], port=optmap['dbport'], db=optmap['dbname'])
        db_conn.query("use %s" %optmap['dbname'])
        db_cursor = db_conn.cursor()


        vip_user_list = {}
        for i in range(10):
                sql = "select USERID, VIPSTATE from VIPUSER%s" %i
                print sql
                db_cursor.execute(sql)
                db_rows = db_cursor.fetchall()
                for USERID, VIPSTATE in db_rows:
                        vip_user_list[USERID] = VIPSTATE


        vip_user_flower_list = {}
        for i in range(10):
                sql = "select USERID, FLOWER from VIPUSERFLOWER%s" %i
                print sql
                db_cursor.execute(sql)
                db_rows = db_cursor.fetchall()
                for USERID, FLOWER in db_rows:
                        vip_user_flower_list[USERID] = FLOWER


        db_cursor.close()
        db_conn.close()




        vip_state_flower_list = {}
        vip_state_flower_list[1] = 0;
        vip_state_flower_list[2] = 0;
        vip_state_flower_list[3] = 0;
        for key in vip_user_list:
                if key in vip_user_flower_list:
                        if vip_user_list[key] in vip_state_flower_list:
                                vip_state_flower_list[vip_user_list[key]] += vip_user_flower_list[key]


        for key in vip_state_flower_list:
                print key, vip_state_flower_list[key]


        db_conn_gm = MySQLdb.connect(user=optmap['dbuser'], passwd=optmap['dbpass'], host=optmap['dbhost_gm'], port=optmap['dbport'], db=optmap['dbname_gm'])
        db_conn_gm.query("use %s" %optmap['dbname_gm'])


        dword_time = time.mktime(time.strptime(today, '%Y%m%d'))
        sql = "delete from VIPUSERFLOWERMONTHLY where count_time='%d'" %dword_time
        print sql
        db_conn_gm.query(sql)


        sql = "insert into VIPUSERFLOWERMONTHLY (count_time) values('%d')" %(dword_time)
        print sql
        db_conn_gm.query(sql)


        sql = "update VIPUSERFLOWERMONTHLY set year_flower_left_num=%d, month_flower_left_num=%d, week_flower_left_num=%d where count_time='%d'" %(vip_state_flower_list[3], vip_state_flower_list[2], vip_state_flower_list[1], dword_time)
        print sql
        db_conn_gm.query(sql)
        db_conn_gm.commit()


        db_conn_gm.close()
main()
#if __name__ == "__main__":
#        main()




cat vipactiveusernumold.py
#!/usr/bin/env python
#-*-coding:utf-8-*-


import MySQLdb
import os, sys, re
import time, getopt


optmap = {
                        'dbuser' : 'haoren',
                        'dbpass' : '123456789',
                        'dbhost' : '172.29.1.106',
                        'dbport' : 3306,
                        'dbname' : 'GMServerDB'
                }


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


        dword_time = time.mktime(time.strptime(one_day, '%Y%m%d'))
        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 = "delete from VIPACTIVEUSERNUM where active_time='%s'" %dword_time
        db_conn.query(sql)
        sql = "insert into VIPACTIVEUSERNUM (active_time) values('%s')" %dword_time
        db_conn.query(sql)


#年会员(活跃帐号 活跃机器 活跃IP)
        sql = "select count(distinct account) as account_num, count(distinct mac) as mac_num, count(distinct ip) as ip_num from VIPACTIVEUSERNUM_%s where vip_state=3" %one_day
        print sql
        db_cursor.execute(sql)
        temp = db_cursor.fetchone()
        print temp


        temp_account_num = int(temp[0])
        temp_mac_num = int(temp[1])
        temp_ip_num = int(temp[2])
        sql = "update VIPACTIVEUSERNUM set year_account_num=%d, year_mac_num=%d, year_ip_num=%d where active_time='%s'" %(temp_account_num, temp_mac_num, temp_ip_num, dword_time)
        db_conn.query(sql)


#月会员(活跃帐号 活跃机器 活跃IP)
        sql = "select count(distinct account) as account_num, count(distinct mac) as mac_num, count(distinct ip) as ip_num from VIPACTIVEUSERNUM_%s where vip_state=2" %one_day
        print sql
        db_cursor.execute(sql)
        temp = db_cursor.fetchone()
        print temp


        temp_account_num = int(temp[0])
        temp_mac_num = int(temp[1])
        temp_ip_num = int(temp[2])
        sql = "update VIPACTIVEUSERNUM set month_account_num=%d, month_mac_num=%d, month_ip_num=%d where active_time='%s'" %(temp_account_num, temp_mac_num, temp_ip_num, dword_time)
        db_conn.query(sql)


#周会员(活跃帐号 活跃机器 活跃IP)
        sql = "select count(distinct account) as account_num, count(distinct mac) as mac_num, count(distinct ip) as ip_num from VIPACTIVEUSERNUM_%s where vip_state=1" %one_day
        print sql
        db_cursor.execute(sql)
        temp = db_cursor.fetchone()
        print temp


        temp_account_num = int(temp[0])
        temp_mac_num = int(temp[1])
        temp_ip_num = int(temp[2])
        sql = "update VIPACTIVEUSERNUM set week_account_num=%d, week_mac_num=%d, week_ip_num=%d where active_time='%s'" %(temp_account_num, temp_mac_num, temp_ip_num, dword_time)
        db_conn.query(sql)


#总共未过期会员(活跃机器 活跃IP)
        sql = "select count(distinct account) as total_account_num, count(distinct mac) as total_mac_num, count(distinct ip) as total_ip_num from VIPACTIVEUSERNUM_%s" %one_day
        print sql
        db_cursor.execute(sql)
        temp = db_cursor.fetchone()
        print temp


        temp_mac_num = int(temp[1])
        temp_ip_num = int(temp[2])
        sql = "update VIPACTIVEUSERNUM set total_mac_num=%d, total_ip_num=%d where active_time='%s'" %(temp_mac_num, temp_ip_num, dword_time)
        db_conn.query(sql)


#过期会员(活跃帐号 活跃机器 活跃IP)
        sql = "select count(distinct account) as account_num, count(distinct mac) as mac_num, count(distinct ip) as ip_num from VIPACTIVEUSERNUM_%s where vip_state in(11,12,13)" %one_day
        print sql
        db_cursor.execute(sql)
        temp = db_cursor.fetchone()
        print temp


        temp_account_num = int(temp[0])
        temp_mac_num = int(temp[1])
        temp_ip_num = int(temp[2])
        sql = "update VIPACTIVEUSERNUM set before_account_num=%d, before_mac_num=%d, before_ip_num=%d where active_time='%s'" %(temp_account_num, temp_mac_num, temp_ip_num, dword_time)
        db_conn.query(sql)


#提交事务
        db_conn.commit()
main()
#if __name__ == "__main":
#               main()





cat DelVipUserData.py
#!/usr/bin/env python
#-*-coding:utf-8-*-
#会员数据统计
import MySQLdb
import os, sys, re, string
import time, getopt


optmap = {
                'dbuser' : 'haoren',
                'dbpass' : '123456789',
                'dbhost' : '172.29.1.106',
                'dbport' : 3306,
                'dbname' : 'GMServerDB',
                #'logdir' : '/home/haoren/log/',                         #内网环境日志目录
                'logdir' : '/home/haoren/logdir/',                      #外网环境日志目录
                #'logpattern' : '^chat_vipuserserver.log.',     #内网环境日志名称前缀
                'logpattern' : '^vipuserserver.log.'            #外网环境日志名称前缀
                }


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()


        dword_time = time.mktime(time.strptime(one_day, '%Y%m%d'))
        db_conn.query("use %s" %optmap['dbname'])
        sql = "delete from VIPUSERDATAMONTHLY where count_time='%d'" %dword_time
        print sql
        db_conn.query(sql)


        db_conn.commit()


        db_cursor.close()
        db_conn.close()


main()
#if __name__ == "__main__"
#        main()




cat zhaomuinviteinfo.py
#!/usr/bin/env python
#-*-coding:utf-8-*-
#招募邀请信息统计
import MySQLdb
import os, sys, re,string
import time, tarfile,getopt


optmap = {
                'dbuser' : 'haoren',
                'dbpass' : '123456789',
                'dbhost' : '172.10.1.103',
                'dbport' : 3306,
                'dbname' : 'MGDB'
                 }
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'


#dirname="/home/haoren/log/"
def main():


        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'))))
        print '统计日期:',fmt_day


        log_day = time.strftime("%y%m%d", time.localtime(time.time()-86400))
        #dirname="/home/haoren/log/%s/"%log_day
        dirname="/home/haoren/logdir/%s_83"%log_day
        print dirname


        db_conn.query("DELETE from ZHAOMUINVITEINFO where tongji_time='%s'"%fmt_day)


        if os.path.exists("/tmp/zhaomuhasinvitenum.txt"):
                os.system("rm -f /tmp/zhaomuhasinvitenum.txt")


        if os.path.exists("/tmp/zhaomucancelnum.txt"):
                os.system("rm -f /tmp/zhaomucancelnum.txt")


        if os.path.exists("/tmp/zhaomusuccessnum.txt"):
                os.system("rm -f /tmp/zhaomusuccessnum.txt")


        if os.path.exists("/tmp/zhaomucaninvitenum.txt"):
                os.system("rm -f /tmp/zhaomucaninvitenum.txt")


        file_list2=get_files(dirname,'vipuserserver')
        for file2 in file_list2:
                command = "cat %s/%s | awk '/招募邀请统计/'>> /tmp/zhaomuhasinvitenum.txt"%(dirname,file2)
                os.system(command)
                command = "cat %s/%s | awk '/招募取消统计/'>> /tmp/zhaomucancelnum.txt"%(dirname,file2)
                os.system(command)
                command = "cat %s/%s | awk '/招募成功统计/'>> /tmp/zhaomusuccessnum.txt"%(dirname,file2)
                os.system(command)
                command = "cat %s/%s | awk '/邀请名额统计/'>> /tmp/zhaomucaninvitenum.txt"%(dirname,file2)
                os.system(command)


        record = {}
        hasinvite = -1
        for hasinvite,line in enumerate(open('/tmp/zhaomuhasinvitenum.txt')):
                pass
        hasinvite+=1
        record['has_invite_num'] = hasinvite
        print '发出邀请次数:',record['has_invite_num']


        cancelinvite = -1
        for cancelinvite,line in enumerate(open('/tmp/zhaomucancelnum.txt')):
                pass
        cancelinvite+=1
        record['cancel_invite_num'] = cancelinvite
        print '取消邀请次数:',record['cancel_invite_num']


        successinvite = -1
        for successinvite,line in enumerate(open('/tmp/zhaomusuccessnum.txt')):
                pass
        successinvite+=1
        record['success_invite_num'] = successinvite
        print '成功邀请次数:',record['success_invite_num']


        filename='/tmp/zhaomucaninvitenum.txt'
        a_file = open(filename, 'r')
        record['all_can_invite_num'] = 0
        #130329-23:57:43 VipUserServer[12900] TRACE: [招募统计]当前邀请名额总数:140
        for a_line in a_file.readlines():
                m = re.search("^(\S+) VipUserServer\[\d+\] TRACE: \[邀请名额统计\]当前邀请名额总数:(\d+)", a_line)
                if m:
                        record['all_can_invite_num'] = int(m.group(2))
        a_file.close()


        print '邀请名额总数:',record['all_can_invite_num']


        db_conn.query("insert into ZHAOMUINVITEINFO (tongji_time,has_invite_num,cancel_invite_num,all_can_invite_num,success_invite_num) values('%s',%d,%d,%d,%d)"%(fmt_day,record['has_invite_num'],record['cancel_invite_num'],record['all_can_invite_num'],record['success_invite_num']))


        db_conn.commit()
        db_cursor.close()
        db_conn.close()


main()
#if __name__ == "__main__":
#        main()







cat zhaomuvipinfo.py
#!/usr/bin/env python
#-*-coding:utf-8-*-
#招募会员统计
import MySQLdb
import os, sys, re,string
import time, tarfile,getopt


optmap = {
                'dbuser' : 'haoren',
                'dbpass' : '123456789',
                'dbhost' : '172.10.1.103',
                'dbport' : 3306,
                'dbname' : 'MGDB'
                 }


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'


#dirname="/home/haoren/log/"
def main():


        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'))))
        print '统计日期:',fmt_day


        log_day = time.strftime("%y%m%d", time.localtime(time.time()-86400))
        #dirname="/home/haoren/log/%s/"%log_day
        dirname="/home/haoren/logdir/%s_83"%log_day
        print dirname


        db_conn.query("DELETE from ZHAOMUVIPINFO where tongji_time='%s'"%fmt_day)


        if os.path.exists("/tmp/zhaomuvipinfo.txt"):
                os.system("rm -f /tmp/zhaomuvipinfo.txt")


        file_list2=get_files(dirname,'vipuserserver')
        for file2 in file_list2:
                command = "cat %s/%s | awk '/招募统计/'>> /tmp/zhaomuvipinfo.txt"%(dirname,file2)
                os.system(command)


        filename='/tmp/zhaomuvipinfo.txt'
        record = {}
        a_file = open(filename, 'r')
        record['all_vip_dredge'] = 0
        record['all_vip_renewals'] = 0
        record['all_dredge_month'] = 0
        record['all_dredge_year'] = 0
        #130330-13:44:56 VipUserServer[12900] TRACE: [招募统计]用户:21002928充值会员,充值模式:2,开通类型:0
        for a_line in a_file.readlines():
                m = re.search("^(\S+) VipUserServer\[\d+\] TRACE: \[招募统计\]用户:(\d+)充值会员,充值模式:(\d+),开通类型:(\d+)", a_line)
                if m:
                        if(int(m.group(4)) == 1):
                                if(int(m.group(3)) == 1):
                                        record['all_dredge_month'] += 1;
                                elif(int(m.group(3)) == 2):
                                        record['all_dredge_year'] += 1;
                        else:
                                record['all_vip_renewals'] += 1;


        record['all_vip_dredge'] = record['all_dredge_year'] + record['all_dredge_month']


        print '邀请开通会员总人数:',record['all_vip_dredge']
        print '邀请续费会员总人数:',record['all_vip_renewals']
        print '邀请开通月会员总人数:',record['all_dredge_month']
        print '邀请开通年会员总人数:',record['all_dredge_year']


        db_conn.query("insert into ZHAOMUVIPINFO (tongji_time,all_vip_dredge,all_vip_renewals,all_dredge_month,all_dredge_year) values('%s',%d,%d,%d,%d)"%(fmt_day,record['all_vip_dredge'],record['all_vip_renewals'],record['all_dredge_month'],record['all_dredge_year']))
        db_conn.commit()
        db_cursor.close()
        db_conn.close()


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' : '123456789',
                'dbhost' : '172.10.1.103',
                'dbport' : 3306,
                'dbname' : 'MGDB',
                #'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 vipuserdatamonthly.py
#!/usr/bin/env python
#-*-coding:utf-8-*-
#会员数据统计
import MySQLdb
import os, sys, re, string
import time, getopt


optmap = {
                'dbuser' : 'haoren',
                'dbpass' : '123456789',
                'dbhost' : '172.10.1.103',
                'dbport' : 3306,
                'dbname' : 'MGDB',
                #'logdir' : '/home/haoren/log/',                         #内网环境日志目录
                'logdir' : '/home/haoren/logdir/',                      #外网环境日志目录
                #'logpattern' : '^chat_vipuserserver.log.',     #内网环境日志名称前缀
                'logpattern' : '^vipuserserver.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_data_file_name = '/tmp/vipuserdatatemp.txt'
        command = "cat /dev/null > %s" %(temp_vip_user_data_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 '/\[VIP统计\]/' >> %s" % (log_dir_full_path, log_file_name_item, temp_vip_user_data_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 '/\[VIP统计\]/' >> %s" % (optmap['logdir'], log_file_name_item, temp_vip_user_data_file_name)
                        os.system(command)


        command = "cat %s |wc -l" %temp_vip_user_data_file_name
        os.system(command)


        new_vip_user_num_map = {}
        new_vip_user_num_map[1] = 0
        new_vip_user_num_map[2] = 0
        new_vip_user_num_map[3] = 0


        before_vip_user_num_map = {}
        before_vip_user_num_map[1] = 0
        before_vip_user_num_map[2] = 0
        before_vip_user_num_map[3] = 0
        before_vip_user_id_map = {}


        renew_before_vip_user_num_map = {}
        renew_before_vip_user_num_map[1] = 0
        renew_before_vip_user_num_map[2] = 0
        renew_before_vip_user_num_map[3] = 0


        today_renew_vip_user_num = 0
        year_vip_user_num = 0
        month_vip_user_num = 0
        week_vip_user_num = 0
        before_year_vip_user_num = 0
        before_month_vip_user_num = 0
        before_week_vip_user_num = 0


        temp_vip_user_data_file = open(temp_vip_user_data_file_name)
        for one_line in temp_vip_user_data_file.readlines():
                match = re.search("^(\S+) VipUserServer\[\d+\]  INFO: \[VIP统计\]新增会员:(\d+),会员状态:(\d+)", one_line)
                if match:
                        new_vip_user_num_map[string.atoi(match.group(3))] += 1
                        continue


                match = re.search("^(\S+) VipUserServer\[\d+\]  INFO: \[VIP统计\]年会员到期未续费:(\d+)", one_line)
                if match:
                        before_vip_user_num_map[3] += 1
                        before_vip_user_id_map[string.atoi(match.group(2))] = 3
                        continue


                match = re.search("^(\S+) VipUserServer\[\d+\]  INFO: \[VIP统计\]月会员到期未续费:(\d+)", one_line)
                if match:
                        before_vip_user_num_map[2] += 1
                        before_vip_user_id_map[string.atoi(match.group(2))] = 2
                        continue


                match = re.search("^(\S+) VipUserServer\[\d+\]  INFO: \[VIP统计\]周会员到期未续费:(\d+)", one_line)
                if match:
                        before_vip_user_num_map[1] += 1
                        before_vip_user_id_map[string.atoi(match.group(2))] = 1
                        continue


                match = re.search("^(\S+) VipUserServer\[\d+\]  INFO: \[VIP统计\]会员续费:(\d+),会员状态\((\d+)-(\d+)\)", one_line)
                if match:
                        if string.atoi(match.group(3)) in (1, 2 , 3):
                                today_renew_vip_user_num += 1
                        else:
                                if string.atoi(match.group(2)) in before_vip_user_id_map:
                                        before_vip_user_num_map[before_vip_user_id_map[string.atoi(match.group(2))]] -= 1
                                        today_renew_vip_user_num += 1
                                else:
                                        renew_before_vip_user_num_map[string.atoi(match.group(4))] += 1
                        continue


                match = re.search("^(\S+) VipUserServer\[\d+\]  INFO: \[VIP统计\]当前VIP人数,会员总数:(\d+),年会员:(\d+),月会员:(\d+),周会员:(\d+),过期会员总数:(\d+),过期年会员:(\d+),过期月会员:(\d+),过期周会员:(\d+)", one_line)
                if match:
                        year_vip_user_num = string.atoi(match.group(3))
                        month_vip_user_num = string.atoi(match.group(4))
                        week_vip_user_num = string.atoi(match.group(5))
                        before_year_vip_user_num = string.atoi(match.group(7))
                        before_month_vip_user_num = string.atoi(match.group(8))
                        before_week_vip_user_num = string.atoi(match.group(9))
                        continue


        temp_vip_user_data_file.close()
        dword_time = time.mktime(time.strptime(one_day, '%Y%m%d'))
        db_conn.query("use %s" %optmap['dbname'])
        sql = "delete from VIPUSERDATAMONTHLY where count_time='%d'" %dword_time
        print sql
        db_conn.query(sql)


        sql = "insert into VIPUSERDATAMONTHLY (count_time) values('%d')" %(dword_time)
        print sql
        db_conn.query(sql)


        sql = "update VIPUSERDATAMONTHLY set year_vip_user_num=%d, month_vip_user_num=%d, week_vip_user_num=%d, today_new_year_vip_user_num=%d, today_new_month_vip_user_num=%d, today_new_week_vip_user_num=%d, today_renew_vip_user_num=%d, today_renew_before_year_vip_user_num=%d, today_renew_before_month_vip_user_num=%d, today_renew_before_week_vip_user_num=%d, today_before_year_vip_user_num=%d, today_before_month_vip_user_num=%d, today_before_week_vip_user_num=%d, before_year_vip_user_num=%d, before_month_vip_user_num=%d, before_week_vip_user_num=%d where count_time='%d'" %(year_vip_user_num, month_vip_user_num, week_vip_user_num, new_vip_user_num_map[3], new_vip_user_num_map[2], new_vip_user_num_map[1], today_renew_vip_user_num, renew_before_vip_user_num_map[3], renew_before_vip_user_num_map[2], renew_before_vip_user_num_map[1], before_vip_user_num_map[3], before_vip_user_num_map[2], before_vip_user_num_map[1], before_year_vip_user_num, before_month_vip_user_num, before_week_vip_user_num, dword_time)
        print sql
        db_conn.query(sql)


        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' : '123456789',
                'dbhost' : '172.10.1.103',
                'dbport' : 3306,
                'dbname' : 'MGDB',
                #'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 zhaomuconsume.py
#!/usr/bin/env python
#-*-coding:utf-8-*-
#招募会员消耗
import MySQLdb
import os, sys, re,string
import time, tarfile,getopt


optmap = {
                'dbuser' : 'haoren',
                'dbpass' : '123456789',
                'dbhost' : '172.10.1.103',
                'dbport' : 3306,
                'dbname' : 'MGDB'
                 }


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'


#dirname="/home/haoren/log/"
def main():


        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'))))
        print '统计日期:',fmt_day


        log_day = time.strftime("%y%m%d", time.localtime(time.time()-86400))
        #dirname="/home/haoren/log/%s/"%log_day
        dirname="/home/haoren/logdir/%s_84"%log_day
        print dirname
        db_conn.query("DELETE from ZHAOMUCONSUME where tongji_time='%s'"%fmt_day)


        if os.path.exists("/tmp/zhaomuconsumepoint.txt"):
                os.system("rm -f /tmp/zhaomuconsumepoint.txt")


        if os.path.exists("/tmp/zhaomuconsumecoin.txt"):
                os.system("rm -f /tmp/zhaomuconsumecoin.txt")


        file_list2=get_files(dirname,'billserver')
        for file2 in file_list2:
                command = "cat %s/%s | awk '/点数招募统计/'>> /tmp/zhaomuconsumepoint.txt"%(dirname,file2)
                os.system(command)
                command = "cat %s/%s | awk '/人民币招募统计/'>> /tmp/zhaomuconsumecoin.txt"%(dirname,file2)
                os.system(command)


        filename='/tmp/zhaomuconsumepoint.txt'
        record = {}
        a_file = open(filename, 'r')
        record['all_consume_point'] = 0
        for a_line in a_file.readlines():
                m = re.search("^(\S+) Bill\[\d+\] TRACE: \[点数招募统计\]会员消耗点数,用户:(\d+),点数:(\d+)", a_line)
                if m:
                        record['all_consume_point'] += int(m.group(3))
        a_file.close()


        filename='/tmp/zhaomuconsumecoin.txt'
        a_file = open(filename, 'r')
        record['all_consume_coin'] = 0
        for a_line in a_file.readlines():
                m = re.search("^(\S+) Bill\[\d+\] TRACE: \[人民币招募统计\]会员消耗人民币,用户:(\d+),人民币:(\d+)", a_line)
                if m:
                        record['all_consume_coin'] += int(m.group(3))
        a_file.close()


        print '招募会员消费点数:',record['all_consume_point']
        print '招募会员消费人民币:',record['all_consume_coin']


        db_conn.query("insert into ZHAOMUCONSUME (tongji_time,all_consume_point,all_consume_coin) values('%s',%d,%d)"%(fmt_day,record['all_consume_point'],record['all_consume_coin']))


        db_conn.commit()
        db_cursor.close()
        db_conn.close()


main()
#if __name__ == "__main__":
#        main()





cat vip_monthly_bulletin.py
#!/usr/bin/env python
#-*-coding:utf-8-*-
#会员月报
import MySQLdb
import os, sys, re,string
import time, tarfile,getopt


optmap = {
                'dbuser' : 'haoren',
                'dbpass' : '123456789',
                'dbhost' : '172.10.1.103',
                'dbport' : 3306,
                'dbname' : 'MGDB'
                 }


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():


        log_day = time.strftime("%y%m%d", time.localtime(time.time()-86400-86400-86400))
        dirname="/home/haoren/logdir/%s_111/%s"%(log_day,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'])


        sql ="create table if not exists VIPTABLE (`ID` int(10) unsigned NOT NULL auto_increment,`tongji_time` varchar(24) not null default '',`all_vip` int(10) not null default '0',`new_vip` int(10) not null default '0',`renewals_vip` int(10) not null default '0',`today_old_vip` int(10) not null default '0',`all_old_vip` int(10) not null default '0',`year_vip` int(10) not null default '0',`month_vip` int(10) not null default '0',`week_vip` int(10) not null default '0',primary key (ID))"
        db_conn.query(sql)


        cur_day = time.strftime("%Y%m%d", time.localtime(time.time()-86400-86400-86400))


        fmt_day = time.strftime('%Y-%m-%d', time.localtime(time.mktime(time.strptime(cur_day, '%Y%m%d'))))
        print 'VIP统计日期:',fmt_day


        log_day = time.strftime("%y%m%d", time.localtime(time.time()-86400-86400-86400))
        dirname="/home/haoren/logdir/%s_111/%s"%(log_day,log_day)
        print dirname
        db_conn.query("DELETE from VIPTABLE where tongji_time='%s'"%fmt_day)
        db_conn.query("insert into VIPTABLE (tongji_time) values('%s')"%fmt_day)


        if os.path.exists("/tmp/vip.txt"):
                os.system("rm -f /tmp/vip.txt")


        if os.path.exists("/tmp/newvip.txt"):
                os.system("rm -f /tmp/newvip.txt")


        if os.path.exists("/tmp/renewvip.txt"):
                os.system("rm -f /tmp/renewvip.txt")


        if os.path.exists("/tmp/oldvip.txt"):
                os.system("rm -f /tmp/oldvip.txt")


        #dirname="/home/haoren/log/"
        #dirname="/tmp/"


        file_list2=get_files(dirname,'vipuserserver')
        for file2 in file_list2:
                command = "cat %s/%s | awk '/VIP统计/'|awk '/新增会员/'>> /tmp/newvip.txt"%(dirname,file2)
                os.system(command)
                command = "cat %s/%s | awk '/VIP统计/'|awk '/会员续费/'>> /tmp/renewvip.txt"%(dirname,file2)
                os.system(command)
                command = "cat %s/%s | awk '/VIP统计/'|awk '/到期未续费/'>> /tmp/oldvip.txt"%(dirname,file2)
                os.system(command)
                command = "cat %s/%s | awk '/VIP统计/'|awk '/当前VIP人数/'>> /tmp/vip.txt" %(dirname,file2)
                os.system(command)


        filename='/tmp/vip.txt'
        record = {}
        a_file = open(filename, 'r')
        record['all_vip']=0
        record['year_vip']=0
        record['month_vip']=0
        record['week_vip']=0
        record['old_vip']=0
        for a_line in a_file.readlines():
                m = re.search("^(\S+) VipUserServer\[\d+\]  INFO: \[VIP统计\]当前VIP人数,会员总数:(\d+),年会员:(\d+),月会员:(\d+),周会员:(\d+),过期会员总数:(\d+)", a_line)
                if m:
                        record['all_vip'] = int(m.group(2))
                        record['year_vip'] = int(m.group(3))
                        record['month_vip'] = int(m.group(4))
                        record['week_vip'] = int(m.group(5))
                        record['old_vip'] = int(m.group(6))


        db_conn.query("update VIPTABLE set all_vip=%d,all_old_vip=%d,year_vip=%d,month_vip=%d,week_vip=%d where tongji_time='%s'"%(record['all_vip'],record['old_vip'],record['year_vip'],record['month_vip'],record['week_vip'],fmt_day))


        a_file.close()




        newcount = -1
        for newcount,line in enumerate(open('/tmp/newvip.txt')):
                pass
        newcount+=1


        renewcount = -1
        for renewcount,line in enumerate(open('/tmp/renewvip.txt')):
                pass
        renewcount+=1


        oldcount = -1
        for oldcount,line in enumerate(open('/tmp/oldvip.txt')):
                pass
        oldcount+=1


        print '总会员人数:',record['all_vip']
        print '今日新增会员:',newcount
        print '今日续费会员:',renewcount
        print '今日到期未续费会员人数:',oldcount
        print '历史到期未续费总人数:',record['old_vip']
        print '年费会员总人数:',record['year_vip']
        print '月费会员总人数:',record['month_vip']
        print '周费会员总人数:',record['week_vip']


        db_conn.query("update VIPTABLE set new_vip=%d,renewals_vip=%d,today_old_vip=%d where tongji_time='%s'"%(newcount,renewcount,oldcount,fmt_day))
        db_conn.commit()
        db_cursor.close()
        db_conn.close()


main()
#if __name__ == "__main__":
#        main()
















cat singerlevelmonthly.py
#!/usr/bin/env python
#-*-coding:utf-8-*-
#用户等级月报
import MySQLdb
import os, sys, re,string
import time, tarfile,getopt


optmap = {
                'dbuser' : 'haoren',
                'dbpass' : '123456789',
                'dbhost' : '172.10.1.104',
                'dbhost_gm' : '172.10.1.103',
                'dbport' : 3306,
                'dbname' : 'MIDB',
                'dbname_gm' : 'MGDB'
                 }


def main():


        db_conn = MySQLdb.connect(user=optmap['dbuser'], passwd=optmap['dbpass'], host=optmap['dbhost'], port=optmap['dbport'], db=optmap['dbname'])
        db_conn.query("use %s" %optmap['dbname'])
        db_cursor = db_conn.cursor()


#取数据
        singer_dict = {}
        for i in range(10):
                sql ="SELECT ID, SINGERLEVEL from SINGERSHOW%s"%i
                print sql
                db_cursor.execute(sql)
                db_rows = db_cursor.fetchall()
                for ID, SINGERLEVEL in db_rows:
                        singer_dict[ID] = SINGERLEVEL 


        db_conn.commit()
        db_cursor.close()
        db_conn.close()


        level_dict = {}
        for key in singer_dict:
                if (singer_dict[key] in level_dict):
                        level_dict[singer_dict[key]] += 1
                else:
                        level_dict[singer_dict[key]] = 1 




        for key in level_dict:
                print 'key=%s, value=%s' % (key, level_dict[key])


#写入MGDB
        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()


        one_day = time.strftime("%Y%m%d", time.localtime(time.time() - 24*60*60))       #默认日期为脚本运行的上一天
        print "统计(%s)用户等级数据..." %one_day
        dword_time = time.mktime(time.strptime(one_day, '%Y%m%d'))
        db_conn.query("DELETE from SINGERLEVELMONTHLY where time='%d'"%dword_time)
        for key in level_dict:
                sql = "insert into SINGERLEVELMONTHLY (time, level, count) values(%d,%d,%d)" %(dword_time, key, level_dict[key])
                print sql
                db_cursor.execute(sql)


        db_conn.commit()
        db_cursor.close()
        db_conn.close()
main()
#if __name__ == "__main__":
#        main()
















cat clientinstall.py
#!/usr/bin/env python
#-*-coding:utf-8-*-
#客户端下载卸载数据统计


import MySQLdb
import os, sys, re,string
import time, tarfile,getopt


optmap = {
                'dbuser' : 'root',
                'dbpass' : '123456',
                'dbhost' : '172.17.90.15',
                'dbport' : 3306,
                'dbname' : 'zebra_gmtool_haoren'
                 }


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'))))
        dirnameinstall="/home/haoren/log/DuDuInstall_%s.log"%fmt_day
        dirnameuninstall="/home/haoren/log/DuDuUnInstall_%s.log"%fmt_day
        #dirname="/home/haoren/log/"
        #print dirname
        #ACU、PCU
        if os.path.exists("/tmp/clientinstall1.txt"):
                os.system("rm -f /tmp/clientinstall1.txt")
        if os.path.exists("/tmp/clientuninstall1.txt"):
                os.system("rm -f /tmp/clientuninstall1.txt")


        #此处只统计了渠道1，后续增加再在后面增加
        command = "cat %s|gawk -F ' ' '{print $4}'|grep 1 >> /tmp/clientinstall1.txt"%dirnameinstall
        os.system(command)
        command = "cat %s|gawk -F ' ' '{print $4}'|grep 1 >> /tmp/clientuninstall1.txt"%dirnameuninstall
        os.system(command)


        install1 = -1
        for install1,line in enumerate(open('/tmp/clientinstall1.txt')):
                pass
        install1 += 1


        uninstall1 = -1
        for uninstall1,line in enumerate(open('/tmp/clientuninstall1.txt')):
                pass
        uninstall1 += 1


        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("update CLIENTSOURCERESULT set INSTALL=%d,UNINSTALL=%d where `CLIENT`=1 and `TIME`=%d"%(install1,uninstall1,d_time))


        db_conn.commit()
        db_cursor.close()
        db_conn.close()


main()
#if __name__ == "__main__":
#        main()


























cat  clientsource.py
#!/usr/bin/env python
#-*-coding:utf-8-*-
#客户端来源相关数据统计


import MySQLdb
import os, sys, re,string
import time, tarfile,getopt


optmap = {
                'dbuser' : 'haoren',
                'dbpass' : '123456789',
                'dbhost' : '172.10.1.103',
                'dbport' : 3306,
                'dbname' : 'MGDB'
                 }


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_44"%log_day
        #dirname="/home/haoren/log/"
        print dirname
        #ACU、PCU
        if os.path.exists("/tmp/clientsourceacu.txt"):
                os.system("rm -f /tmp/clientsourceacu.txt")
        if os.path.exists("/tmp/clientsourceip.txt"):
                os.system("rm -f /tmp/clientsourceip.txt")


        file_list2=get_files(dirname,'toolgmserver')
        for file2 in file_list2:
                command = "cat %s/%s | awk '/人数统计/'>> /tmp/clientsourceacu.txt"%(dirname,file2)
                os.system(command)
        for file2 in file_list2:
                command = "cat %s/%s | awk '/客户端统计/'>> /tmp/clientsourceip.txt"%(dirname,file2)
                os.system(command)


        db_conn = MySQLdb.connect(user=optmap['dbuser'], passwd=optmap['dbpass'], host=optmap['dbhost'], port=optmap['dbport'], db=optmap['dbname'])
        db_cursor=db_conn.cursor()
        sql="delete from CLIENTSOURCETEMP"
        db_conn.query(sql)


        #ACU、PCU
        filename='/tmp/clientsourceacu.txt'
        pcu = {}
        acu = {}
        acunum = {}
        a_file = open(filename, 'r')
        for a_line in a_file.readlines():
                m = re.search("^(\S+) ToolGM\[\d+\] TRACE: \[人数统计\]CLIENT:(\d+),总人数:(\d+)", a_line)
                if m:
                        vfind = 0
                        for mykey in pcu:
                                if mykey == int(m.group(2)):
                                        vfind = 1
                                        acu[mykey] += int(m.group(3))
                                        acunum[mykey] += 1
                                        if(pcu[mykey] < int(m.group(3))):
                                                pcu[mykey] = int(m.group(3))
                        if(0==vfind):
                                pcu[int(m.group(2))] = int(m.group(3))
                                acu[int(m.group(2))] = int(m.group(3))
                                acunum[int(m.group(2))] = 1
        for mykey in acu:
                acu[mykey] = acu[mykey]/acunum[mykey]
        for mykey in acunum:
                print 'NUM',mykey,acunum[mykey]
        db_conn.query("delete from CLIENTSOURCERESULT where TIME=%d"%d_time)


        for mykey in pcu:
                print 'PCU:',mykey,pcu[mykey]
                db_conn.query("insert into CLIENTSOURCERESULT(ACU,PCU,TIME,CLIENT) values(%d,%d,%d,%d)"%(acu[mykey],pcu[mykey],d_time,mykey))
        for mykey in acu:
                print 'ACU:',mykey,acu[mykey]


        a_file.close()




        #活跃用户数、活跃IP、活跃机器数、新登录用户数
        filename='/tmp/clientsourceip.txt'
        record = {}
        a_file = open(filename, 'r')
        for a_line in a_file.readlines():
                m = re.search("^(\S+) ToolGM\[\d+\] TRACE: \[客户端统计\]用户:(\d+),IP:(\d+),MAC:(\d+),CLIENT:(\d+),NEW:(\d+)", a_line)
                if m:
                        record['user']=int(m.group(2))
                        record['ip']=int(m.group(3))
                        record['mac']=int(m.group(4))
                        record['client']=int(m.group(5))
                        record['new']=int(m.group(6))
                        db_conn.query("insert into CLIENTSOURCETEMP values(%d,%d,%d,%d,%d)"%(record['user'],record['ip'],record['mac'],record['client'],record['new']))
        a_file.close()
        db_conn.commit()


        sql ="SELECT  count(distinct USERID) as ACTIVEUSER,count(distinct IP) as ACTIVEIP,count(distinct MAC) as ACTIVEMAC FROM CLIENTSOURCETEMP where CLIENT=1"
        db_cursor.execute(sql)
        temp=db_cursor.fetchone()
        print temp
        ACTIVEUSER=int(temp[0])
        ACTIVEIP=int(temp[1])
        ACTIVEMAC=int(temp[2])


        db_conn.query("update CLIENTSOURCERESULT set ACTIVEUSER=%d,ACTIVEIP=%d,ACTIVEMAC=%d where `CLIENT`=1 and `TIME`=%d"%(ACTIVEUSER,ACTIVEIP,ACTIVEMAC,d_time))


        sql ="SELECT  count(NEWUSER) as ACTIVENEW FROM CLIENTSOURCETEMP where CLIENT=1 and NEWUSER=1"
        db_cursor.execute(sql)
        temp=db_cursor.fetchone()
        print temp
        ACTIVENEW=int(temp[0])


        db_conn.query("update CLIENTSOURCERESULT set ACTIVENEW=%d where `CLIENT`=1 and `TIME`=%d"%(ACTIVENEW,d_time))
        db_conn.commit()
        db_cursor.close()
        db_conn.close()


main()
#if __name__ == "__main__":
#        main()










cat individualtransactions.py
#!/usr/bin/env python
#-*-coding:utf-8-*-
#账号交易日报


import MySQLdb
import os, sys, re,string
import time, tarfile,getopt


optmap = {
                'dbuser' : 'haoren',
                'dbpass' : '123456789',
                'dbhost' : '172.10.1.103',
                'dbport' : 3306,
                'dbname' : 'MGDB'
                 }


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 `USERCONSUMEHISTORY` (`userid` int(10) NOT NULL DEFAULT '0',`all_consume_point` int(10) NOT NULL DEFAULT '0',`all_coin` 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 `INDIVIDUALTRANSACTIONS_%s` (`ID` int(10) unsigned NOT NULL auto_increment,`userid` int(10) NOT NULL DEFAULT '0',`today_point_add` int(10) NOT NULL DEFAULT '0',`today_point_consume` int(10) NOT NULL DEFAULT '0',`history_consume_point` int(10) NOT NULL DEFAULT '0',`today_exchange_coin` int(10) NOT NULL DEFAULT '0',`today_consume_coin` int(10) NOT NULL DEFAULT '0',`today_coin_balance` int(10) NOT NULL DEFAULT '0',`history_coin_balance` int(10) NOT NULL DEFAULT '0',primary key(ID))"%tabletime
        sql="CREATE TABLE IF NOT EXISTS `INDIVIDUALTRANSACTIONS_%s` like INDIVIDUALTRANSACTIONS_SAMPLE"%tabletime


        db_conn.query(sql)
        #多次执行历史值判断
        sql ="SELECT userid,today_point_consume,today_coin_balance from INDIVIDUALTRANSACTIONS_%s"%tabletime
        db_cursor.execute(sql)
        while(True):
                data=db_cursor.fetchone()
                if(data == None):
                        break
                db_conn.query("update USERCONSUMEHISTORY set all_consume_point=all_consume_point-%d where userid=%d"%(int(data[1]),int(data[0])))
                db_conn.query("update USERCONSUMEHISTORY set all_coin=all_coin-%d where userid=%d"%(int(data[2]),int(data[0])))


        #清空当天
        db_conn.query("delete from INDIVIDUALTRANSACTIONS_%s"%tabletime)


        #个人充值点数
        sql ="SELECT DISTINCT userid from TRANSACTIONDETAILS_%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:
                sql ="SELECT point_add,point_reduce,coin_add,coin_reduce from TRANSACTIONDETAILS_%s where userid=%d"%(tabletime,user)
                userchargeinfo={}
                userchargeinfo['point_add']=0
                userchargeinfo['point_reduce']=0
                userchargeinfo['coin_add']=0
                userchargeinfo['coin_reduce']=0
                db_cursor.execute(sql)
                while(True):
                        temp=db_cursor.fetchone()
                        if(temp == None):
                                break
                        userchargeinfo['point_add'] += int(temp[0])
                        userchargeinfo['point_reduce']+= int(temp[1])
                        userchargeinfo['coin_add'] +=int(temp[2])
                        userchargeinfo['coin_reduce']+=int(temp[3])
                userchargeinfo['today_coin_balance']= userchargeinfo['coin_add']-userchargeinfo['coin_reduce']
                sql ="SELECT all_consume_point,all_coin from USERCONSUMEHISTORY where userid=%d"%user
                db_cursor.execute(sql)
                temp01=db_cursor.fetchone()
                if(temp01 != None):
                        userchargeinfo['history_consume_point']= userchargeinfo['point_reduce']+int(temp01[0])
                        userchargeinfo['history_coin_balance'] = userchargeinfo['today_coin_balance']+int(temp01[1])
                        db_conn.query("update USERCONSUMEHISTORY set all_consume_point=%d,all_coin=%d where userid=%d"%(userchargeinfo['history_consume_point'],userchargeinfo['history_coin_balance'],user))
                else:
                        userchargeinfo['history_consume_point']=userchargeinfo['point_reduce']
                        userchargeinfo['history_coin_balance']= userchargeinfo['today_coin_balance']
                        db_conn.query("insert into USERCONSUMEHISTORY(userid,all_consume_point,all_coin) values(%d,%d,%d)"%(user,userchargeinfo['history_consume_point'],userchargeinfo['history_coin_balance']))


                db_conn.query("insert into INDIVIDUALTRANSACTIONS_%s(userid,today_point_add,today_point_consume,history_consume_point,today_exchange_coin,today_consume_coin,today_coin_balance,history_coin_balance) values(%d,%d,%d,%d,%d,%d,%d,%d)"%(tabletime,user,userchargeinfo['point_add'],userchargeinfo['point_reduce'],userchargeinfo['history_consume_point'],userchargeinfo['coin_add'],userchargeinfo['coin_reduce'],userchargeinfo['today_coin_balance'],userchargeinfo['history_coin_balance']))




        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' : '123456789',
                'dbhost' : '172.10.1.103',
                'dbport' : 3306,
                'dbname' : 'MGDB'
                 }
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/haoren/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 specialflowerforsender.py
#!/usr/bin/env python
#-*-coding:utf-8-*-
#特殊物品送花者


import MySQLdb
import os, sys, re,string
import time, tarfile,getopt


optmap = {
                'dbuser' : 'haoren',
                'dbpass' : '123456789',
                'dbhost' : '172.10.1.103',
                'dbport' : 3306,
                'dbname' : 'MGDB'
                 }


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 specialflowerforsinger.py
#!/usr/bin/env python
#-*-coding:utf-8-*-
#特殊物品用户


import MySQLdb
import os, sys, re,string
import time, tarfile,getopt


optmap = {
                'dbuser' : 'haoren',
                'dbpass' : '123456789',
                'dbhost' : '172.10.1.103',
                'dbport' : 3306,
                'dbname' : 'MGDB'
                 }


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 vipuserflowermonthlyex.py
#!/usr/bin/env python
#-*-coding:utf-8-*-
#会员物品库存统计
import MySQLdb
import os, sys, re, string
import time, getopt


optmap = {
                'dbuser' : 'haoren',
                'dbpass' : '123456789',
                'dbhost' : '172.10.1.104',
                'dbhost_gm' : '172.10.1.103',
                'dbport' : 3306,
                'dbname' : 'MIDB',
                'dbname_gm' : 'MGDB'
                }


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 "正在统计会员物品库存(%s)..." %one_day


        db_conn = MySQLdb.connect(user=optmap['dbuser'], passwd=optmap['dbpass'], host=optmap['dbhost'], port=optmap['dbport'], db=optmap['dbname'])
        db_conn.query("use %s" %optmap['dbname'])
        db_cursor = db_conn.cursor()


        vip_user_list = {}
        for i in range(10):
                sql = "select USERID, VIPSTATE from VIPUSER%s" %i
                print sql
                db_cursor.execute(sql)
                db_rows = db_cursor.fetchall()
                for USERID, VIPSTATE in db_rows:
                        vip_user_list[USERID] = VIPSTATE


        vip_user_flower_list = {}
        for i in range(10):
                sql = "select USERID, FLOWER from VIPUSERFLOWER%s" %i
                print sql
                db_cursor.execute(sql)
                db_rows = db_cursor.fetchall()
                for USERID, FLOWER in db_rows:
                        vip_user_flower_list[USERID] = FLOWER


        vip_state_flower_list = {}
        vip_state_flower_list[1] = 0;
        vip_state_flower_list[2] = 0;
        vip_state_flower_list[3] = 0;
        for key in vip_user_list:
                if key in vip_user_flower_list:
                        if vip_user_list[key] in vip_state_flower_list:
                                vip_state_flower_list[vip_user_list[key]] += vip_user_flower_list[key]


        for key in vip_state_flower_list:
                print key, vip_state_flower_list[key]


        db_cursor.close()
        db_conn.close()




        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()


        dword_time = time.mktime(time.strptime(one_day, '%Y%m%d'))


        sql = "update VIPUSERFLOWERMONTHLY set year_flower_left_num=%d, month_flower_left_num=%d, week_flower_left_num=%d where count_time='%d'" %(vip_state_flower_list[3], vip_state_flower_list[2], vip_state_flower_list[1], dword_time)
        print sql
        db_conn.query(sql)


        db_conn.commit()


        db_cursor.close()
        db_conn.close()


main()
#if __name__ == "__main__":
#        main()










cat singergrowthdaily.py
#!/usr/bin/env python
#-*-coding:utf-8-*-
#用户经验值月报


import MySQLdb
import os, sys, re,string
import time, tarfile,getopt


optmap = {
                'dbuser' : 'haoren',
                'dbpass' : '123456789',
                'dbhost' : '172.10.1.104',
                'dbhost_gm' : '172.10.1.103',
                'dbport' : 3306,
                'dbname' : 'MIDB',
                'dbname_gm' : 'MGDB'
                 }


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():


        # 先取MIDB中用户等级 成长值数据
        db_conn = MySQLdb.connect(user=optmap['dbuser'], passwd=optmap['dbpass'], host=optmap['dbhost'], port=optmap['dbport'], db=optmap['dbname'])
        db_conn.query("use %s" %optmap['dbname'])
        db_cursor = db_conn.cursor()


        singerlevel_dict = {}
        singergrowth_dict = {}


        for i in range(10):
                sql ="SELECT ID, SINGERLEVEL, SINGERGROWTH from SINGERSHOW%s"%i
                print sql
                db_cursor.execute(sql)
                db_rows = db_cursor.fetchall()
                for ID, SINGERLEVEL, SINGERGROWTH in db_rows:
                        singerlevel_dict[ID] = SINGERLEVEL 
                        singergrowth_dict[ID] = SINGERGROWTH 


        db_conn.commit()
        db_cursor.close()
        db_conn.close()


        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'
        dword_time = 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


        if os.path.exists("/tmp/singergrowth.txt"):
                os.system("rm -f /tmp/singergrowth.txt")


        file_list = get_files(dirname,'flowerserver')
        for file in file_list:
                command = "cat %s/%s | awk '/singer统计/'>> /tmp/singergrowth.txt"%(dirname,file)
                os.system(command)
        file_list = get_files(dirname1,'flowerserver')
        for file in file_list:
                command = "cat %s/%s | awk '/singer统计/'>> /tmp/singergrowth.txt"%(dirname1,file)
                os.system(command)


        #成长值记录
        filename='/tmp/singergrowth.txt'
        a_file = open(filename, 'r')


        flowergrowth_dict = {}
        candrawgrowth_dict = {}
        drawedgrowth_dict = {}
        for a_line in a_file.readlines():
                m = re.search("^\S+ FlowerServer\[\d+\]  INFO: \[singer统计\]用户\((\d+)\)收到物品\(\d+\)朵，获得成长值\((\d+)\)，当前总成长值为\(\d+\)", a_line)
                if m:
                        if (int(m.group(1)) in flowergrowth_dict):
                                flowergrowth_dict[int(m.group(1))] += int(m.group(2))
                        else:
                                flowergrowth_dict[int(m.group(1))] = int(m.group(2)) 


                        if (int(m.group(1)) not in candrawgrowth_dict):
                                candrawgrowth_dict[int(m.group(1))] = 0 
                        if (int(m.group(1)) not in drawedgrowth_dict):
                                drawedgrowth_dict[int(m.group(1))] = 0 


#130625-17:47:21 FlowerServer[13500]  INFO: [singer统计]用户(21002047)物品达到(198)朵可以领取每日成长值(100)
                m = re.search("^\S+ FlowerServer\[\d+\]  INFO: \[singer统计\]用户\((\d+)\)物品达到\(\d+\)朵可以领取每日成长值\((\d+)\)", a_line)
                if m:
                        #print int(m.group(1)),int(m.group(2))
                        if (int(m.group(1)) in candrawgrowth_dict):
                                candrawgrowth_dict[int(m.group(1))] += int(m.group(2))
                        else:
                                candrawgrowth_dict[int(m.group(1))] = int(m.group(2)) 


                        if (int(m.group(1)) not in flowergrowth_dict):
                                flowergrowth_dict[int(m.group(1))] = 0 
                        if (int(m.group(1)) not in drawedgrowth_dict):
                                drawedgrowth_dict[int(m.group(1))] = 0 


#130625-17:47:24 FlowerServer[13500]  INFO: [singer统计]用户(21002047)用户等级(7)成长值(315), 成功领取每日成长值(100)
                m = re.search("^\S+ FlowerServer\[\d+\]  INFO: \[singer统计\]用户\((\d+)\)用户等级\(\d+\)成长值\(\d+\), 成功领取每日成长值\((\d+)\)", a_line)
                if m:
                        #print int(m.group(1)),int(m.group(2))
                        if (int(m.group(1)) in drawedgrowth_dict):
                                drawedgrowth_dict[int(m.group(1))] += int(m.group(2))
                        else:
                                drawedgrowth_dict[int(m.group(1))] = int(m.group(2)) 


        a_file.close()


        #创建数据库表
        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


        sql="CREATE TABLE IF NOT EXISTS `SINGERGROWTHDAILY_%s` like SINGERGROWTHDAILY_SAMPLE"%tabletime
        print sql
        db_conn.query(sql)
        db_conn.query("delete from SINGERGROWTHDAILY_%s"%tabletime)


        for key in flowergrowth_dict:
                num = key
                sql = "insert into SINGERGROWTHDAILY_%s(userid,drawedgrowth,candrawgrowth,growthfromflower,singerlevel,curgrowth) values(%d,%d,%d,%d,%d,%d)"%(tabletime, num, drawedgrowth_dict[num], candrawgrowth_dict[num],flowergrowth_dict[num],singerlevel_dict[num],singergrowth_dict[num])
                print sql
                db_conn.query(sql)
        db_conn.commit()
        db_cursor.close()
        db_conn.close()
main()
#if __name__ == "__main__":
#        main()






















cat  platformincomemonthly.py
#!/usr/bin/env python
#-*-coding:utf-8-*-
#平台收入月报
#****************************************使用说明****************************************************
# 内网测试通过命令行参数
# -d 指定统计时间
# -p 指定日志所在目录
# -t 指定临时文件存放目录
# 示例如下:
# [meinv@localhost tongji]$ ./newplatformincomemonthly.py -d 20141112 -p /home/meinv/log/ -t /home/meinv/tongji/
# 外网无需加参数,使用默认值
#****************************************使用说明****************************************************


import MySQLdb
import os, sys, re,string
import time, tarfile,getopt


optmap = {
                'dbuser' : 'haoren',
                'dbpass' : '123456789',
                'dbhost' : '172.10.1.103',
                'dbport' : 3306,
                'dbname' : 'MGDB'
                 }
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  racingincomemonthly.py
#!/usr/bin/env python
#-*-coding:utf-8-*-
#购物平台赛车日报
#****************************************使用说明****************************************************
# 内网测试通过命令行参数
# -d 指定统计时间
# 示例如下:
# [meinv@localhost tongji]$ ./racingincomemonthly.py -d 20141112
# 外网无需加参数,使用默认值
#****************************************使用说明****************************************************


import MySQLdb
import os, sys, re,string
import time, tarfile,getopt
#import argparse


optmap = {
                'dbuser' : 'haoren',
                'dbpass' : '123456789',
                'dbhost' : '172.10.1.103',
                'dbport' : 3306,
                'dbname' : 'MGDB'
        }
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' : '123456789',
                'dbhost' : '172.10.1.103',
                'dbport' : 3306,
                'dbname' : 'MGDB'
        }
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/haoren/log/%s/"%log_day
        #dirname="/home/haoren/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  channelincomeDaily.py
#!/usr/bin/env python
#-*-coding:utf-8-*-
#渠道收入日报
#****************************************使用说明****************************************************
# 内网测试通过命令行参数
# -d 指定统计时间
# -p 指定日志所在目录
# -t 指定临时文件存放目录
# 示例如下:
# [meinv@localhost tongji]$ ./channelincomeDaily.py -d 20141112 -p /home/meinv/log/ -t /home/meinv/tongji/
# 外网无需加参数,使用默认值
#****************************************使用说明****************************************************




import MySQLdb
import os, sys, re,string
import time, tarfile,getopt


optmap = {
                'dbuser' : 'haoren',
                'dbpass' : '123456789',
                'dbhost' : '172.10.1.103',
                'dbport' : 3306,
                'dbname' : 'MGDB'
                 }
                 
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 指定临时文件存放目录
# 示例如下:
# [meinv@localhost tongji]$ ./userconsumitemDaily.py -d 20141112 -p /home/meinv/log/ -t /home/meinv/tongji/
# 外网无需加参数,使用默认值
#****************************************使用说明****************************************************


import MySQLdb
import os, sys, re,string
import time, tarfile,getopt


optmap = {
                'dbuser' : 'haoren',
                'dbpass' : '123456789',
                'dbhost' : '172.10.1.103',
                'dbport' : 3306,
                'dbname' : 'MGDB'
                 }
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  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' : '123456789',
        'dbhost' : '172.10.1.104',
        'dbhost_gm' : '172.10.1.103',
        'dbport' : 3306,
        'dbname' : 'MIDB',
        'dbname_gm' : 'MGDB'
}
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 channelgrowthdaily.py
#!/usr/bin/env python
#-*-coding:utf-8-*-
#渠道成长值月报
#请勿模仿 小心被误导


import MySQLdb
import os, sys, re,string
import time, tarfile,getopt


optmap = {
                'dbuser' : 'haoren',
                'dbpass' : '123456789',
                'dbhost' : '172.10.1.104',
                'dbhost_gm' : '172.10.1.103',
                'dbport' : 3306,
                'dbname' : 'MIDB',
                'dbname_gm' : 'MGDB'
                 }


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():


        # 先取MIDB中渠道等级 成长值数据
        db_conn = MySQLdb.connect(user=optmap['dbuser'], passwd=optmap['dbpass'], host=optmap['dbhost'], port=optmap['dbport'], db=optmap['dbname'])
        db_conn.query("use %s" %optmap['dbname'])
        db_cursor = db_conn.cursor()


        channellevel_dict = {}
        channelgrowth_dict = {}


        for i in range(10):
                sql ="SELECT ID, LEVEL, GROWTH from CHANNELGROW%s"%i
                print sql
                db_cursor.execute(sql)
                db_rows = db_cursor.fetchall()
                for ID, LEVEL, GROWTH in db_rows:
                        channellevel_dict[ID] = LEVEL 
                        channelgrowth_dict[ID] = GROWTH 


        db_conn.commit()
        db_cursor.close()
        db_conn.close()


        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'
        dword_time = 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.mktime(time.strptime(cur_day, '%Y%m%d'))))
        #log_day = time.strftime("%y%m%d", time.localtime(time.time()-86400))
        dirname="/home/haoren/logdir/%s_67/"%log_day
        print '日志路径',dirname
        #log_day = time.strftime("%y%m%d", time.localtime(time.time()-86400))
        dirname1="/home/haoren/logdir/%s_138/"%log_day
        print '日志路径',dirname1
        #log_day = time.strftime("%y%m%d", time.localtime(time.time()-86400))
        dirname2="/home/haoren/logdir/%s_139/"%log_day
        print '日志路径',dirname2


        if os.path.exists("/tmp/channelgrowth.txt"):
                os.system("rm -f /tmp/channelgrowth.txt")


        file_list = get_files(dirname1,'flowerserver')
        for file in file_list:
                command = "cat %s/%s | awk '/channelgrow统计/'>> /tmp/channelgrowth.txt"%(dirname1,file)
                os.system(command)
        file_list = get_files(dirname2,'flowerserver')
        for file in file_list:
                command = "cat %s/%s | awk '/channelgrow统计/'>> /tmp/channelgrowth.txt"%(dirname2,file)
                os.system(command)
        file_list = get_files(dirname,'channelgrowserver')
        for file in file_list:
                command = "cat %s/%s | awk '/channelgrow统计/'>> /tmp/channelgrowth.txt"%(dirname,file)
                os.system(command)


        #成长值记录
        filename='/tmp/channelgrowth.txt'
        a_file = open(filename, 'r')


        floweramount_dict = {}
        flower999_dict = {}
        flower520_dict = {}
        flower365_dict = {}
        flower99_dict = {}
        growthfloweramount_dict = {}
        growthonline_dict = {}
        growthspecialflower_dict = {}
        for a_line in a_file.readlines():
                #130719-12:09:44 ChannelGrowServer[14400]  INFO: [channelgrow统计]服务器(13500)通知渠道(210018)增加物品(99)
                m = re.search("^\S+ ChannelGrowServer\[\d+\]  INFO: \[channelgrow统计\]服务器\(\d+\)通知渠道\((\d+)\)增加物品\((\d+)\)", a_line)
                if m:
                        if (int(m.group(1)) in floweramount_dict):
                                floweramount_dict[int(m.group(1))] += int(m.group(2))
                        else:
                                floweramount_dict[int(m.group(1))] = int(m.group(2)) 


                #130719-12:11:01 FlowerServer[13500]  INFO: [channelgrow统计]渠道(210018)收到物品(99)朵，获得成长值(1)
                m = re.search("^\S+ FlowerServer\[\d+\]  INFO: \[channelgrow统计\]渠道\((\d+)\)收到物品\((\d+)\)朵，获得成长值\(\d+\)", a_line)
                if m:
                        if (int(m.group(2)) == 99):
                                if (int(m.group(1)) in flower99_dict):
                                        flower99_dict[int(m.group(1))] += 1 
                                else:
                                        flower99_dict[int(m.group(1))] = 1 
                        if (int(m.group(2)) == 365):
                                if (int(m.group(1)) in flower365_dict):
                                        flower365_dict[int(m.group(1))] += 1 
                                else:
                                        flower365_dict[int(m.group(1))] = 1 
                        if (int(m.group(2)) == 520):
                                if (int(m.group(1)) in flower520_dict):
                                        flower520_dict[int(m.group(1))] += 1 
                                else:
                                        flower520_dict[int(m.group(1))] = 1 
                        if (int(m.group(2)) == 999):
                                if (int(m.group(1)) in flower999_dict):
                                        flower999_dict[int(m.group(1))] += 1 
                                else:
                                        flower999_dict[int(m.group(1))] = 1 




#130718-16:04:05 ChannelGrowServer[14400]  INFO: [channelgrow统计]渠道(210018)由于在线人数增加成长值(10)
                m = re.search("^\S+ ChannelGrowServer\[\d+\]  INFO: \[channelgrow统计\]渠道\((\d+)\)由于在线人数增加成长值\((\d+)\)", a_line)
                if m:
                        #print int(m.group(1)),int(m.group(2))
                        if (int(m.group(1)) in growthonline_dict):
                                growthonline_dict[int(m.group(1))] += int(m.group(2))
                        else:
                                growthonline_dict[int(m.group(1))] = int(m.group(2)) 


                        if (int(m.group(1)) not in floweramount_dict):
                                floweramount_dict[int(m.group(1))] = 0 


#130718-16:04:27 ChannelGrowServer[14400]  INFO: [channelgrow统计]渠道(210018)由于特殊物品增加成长值(1)
                m = re.search("^\S+ ChannelGrowServer\[\d+\]  INFO: \[channelgrow统计\]渠道\((\d+)\)由于特殊物品增加成长值\((\d+)\)", a_line)
                if m:
                        #print int(m.group(1)),int(m.group(2))
                        if (int(m.group(1)) in growthspecialflower_dict):
                                growthspecialflower_dict[int(m.group(1))] += int(m.group(2))
                        else:
                                growthspecialflower_dict[int(m.group(1))] = int(m.group(2)) 


#130718-16:05:22 ChannelGrowServer[14400]  INFO: [channelgrow统计]渠道(210018)物品总量达到(1890)，当前总物品成长值(0), 要增加成长值(10)
                m = re.search("^\S+ ChannelGrowServer\[\d+\]  INFO: \[channelgrow统计\]渠道\((\d+)\)物品总量达到\(\d+\)，当前总物品成长值\(\d+\), 要增加成长值\((\d+)\)", a_line)
                if m:
                        #print int(m.group(1)),int(m.group(2))
                        if (int(m.group(1)) in growthfloweramount_dict):
                                growthfloweramount_dict[int(m.group(1))] += int(m.group(2))
                        else:
                                growthfloweramount_dict[int(m.group(1))] = int(m.group(2)) 


        a_file.close()


        #创建数据库表
        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


        sql="CREATE TABLE IF NOT EXISTS `CHANNELGROWTHDAILY_%s` like CHANNELGROWTHDAILY_SAMPLE"%tabletime
        print sql
        db_conn.query(sql)
        db_conn.query("delete from CHANNELGROWTHDAILY_%s"%tabletime)


        for key in floweramount_dict:
                num = key
                if (num in channellevel_dict):
                        channellevel_ = channellevel_dict[num]
                else:
                        channellevel_ = 0;
                if (num in channelgrowth_dict):
                        channelgrowth_ = channelgrowth_dict[num]
                else:
                        channelgrowth_ = 0;
                if (num in growthspecialflower_dict):
                        growthfromspecialflower_ = growthspecialflower_dict[num]
                else:
                        growthfromspecialflower_ = 0;
                if (num in growthfloweramount_dict):
                        growthfromfloweramount_ = growthfloweramount_dict[num]
                else:
                        growthfromfloweramount_ = 0;
                if (num in growthonline_dict):
                        growthfromonlinenum_ = growthonline_dict[num]
                else:
                        growthfromonlinenum_ = 0;
                if (num in flower999_dict):
                        flower999_ = flower999_dict[num]
                else:
                        flower999_ = 0;
                if (num in flower520_dict):
                        flower520_ = flower520_dict[num]
                else:
                        flower520_ = 0;
                if (num in flower365_dict):
                        flower365_ = flower365_dict[num]
                else:
                        flower365_ = 0;
                if (num in flower99_dict):
                        flower99_ = flower99_dict[num]
                else:
                        flower99_ = 0;
                sql = "insert into CHANNELGROWTHDAILY_%s(id,growthfromspecialflower,growthfromfloweramount,growthfromonlinenum,flower999,flower520,flower365,flower99,floweramount,level,curgrowth) values(%d,%d,%d,%d,%d,%d,%d,%d,%d,%d,%d)"%(tabletime, num, growthfromspecialflower_, growthfromfloweramount_,growthfromonlinenum_,flower999_,flower520_,flower365_, flower99_, floweramount_dict[num],channellevel_,channelgrowth_)
                print sql
                db_conn.query(sql)
        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' : '123456789',
                'dbhost' : '172.10.1.103',
                'dbport' : 3306,
                'dbname' : 'MGDB'
                 }
                 
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()