本文共 125603 字，大约阅读时间需要 418 分钟。
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() 转载地址：http://cjpab.baihongyu.com/