#!/usr/bin/env python# encoding=gbk#-*-coding:gbk-*-# 查询用户的手机号码import sysimport codecsimport mysql.connectorimport pandas as pdimport numpy as npoptmap = { 'dbuser' : 'haoren', 'dbpass' : 'hjkdhskjsh', 'dbhost' : '172.16.1.8', 'dbport' : 3306, 'dbname' : 'FMDB'}def sql_select(reqsql): ret = '' try: db_conn = mysql.connector.connect(user=optmap['dbuser'], password=optmap['dbpass'], host=optmap['dbhost'], port=optmap['dbport'], database=optmap['dbname']) db_cursor=db_conn.cursor() count = db_cursor.execute(reqsql) ret = db_cursor.fetchall() except mysql.connector.Error as e: print ('Error : {}'.format(e)) finally: db_cursor.close() db_conn.close return ret#批量查询用户的昵称def getnickname(uid): id = int(uid)%10 reqsql = "select PHONE,CHANNELNICKNAME from CHARBASE%d where ID=%s" %(id,uid) #reqsql = "select NICKNAME from CHARBASE%d where ID=%d" %(id,uid) ret = sql_select(reqsql) print ret if ret[0][0] is not None: try: phone = ret[0][0].encode('unicode-escape').decode('string_escape').decode('gbk') ss = ret[0][1] ss = ss.encode('unicode-escape').decode('string_escape') nick_name = ss.decode('gbk') except: nick_name = '' phone = '' else: phone = '' nick_name = '' return uid+','+phone+','+nick_namefout = codecs.open('singerphone.csv', 'w', 'gbk')f = open('PERFORMERID.txt', 'r')f_list = f.readlines()#print f_listfor f_line in f_list: id = f_line.split() print "这里id" ,id if id[0] is not None: uid = id[0] ret = getnickname(uid) print "这里uid",uid fout.write(ret) fout.write('\n') #breakfout.close()f.close() #userlist = np.loadtxt('PERFORMERID.txt')#for id in userlist:# print id#print userlist ===================================================================================================================cat billserver.py#!/usr/bin/env python# encoding=gbk#-*-coding:gbk-*-# 人民币结算处理import os,sysimport globimport reimport datetimeimport timeimport mysql.connectorimport pandas as pdimport numpy as np#160125-11:00:14 Bill[40268] INFO: [结算统计]时间(1453690814)类别(1)名称(物品收入)频道(3977962)等级(2)用户(65147500)赠送(1)个物品(39)给客户(65147500),客户等级(28),签约(1), 消耗人民币(100), 客户获得人民币(8000), 频道获得人民币(2000),客户当前金币(1320960)频道当前人民币(335560)reg='^(\S+) Bill\[\d+\] INFO: \[结算统计\]时间\((\d+)\)类别\((\d+)\)名称\((.*?)\)频道\((\d+)\)等级\((\d+)\)用户\((\d+)\)赠送\((\d+)\)个物品\((\d+)\)给客户\((\d+)\),客户等级\((\d+)\),签约\((\d+)\), 消耗人民币\((\d+)\), 客户获得人民币\((\d+)\), 频道获得人民币\((\d+)\),客户当前人民币\((\d+)\)频道当前人民币\((\d+)\)'def parse_line(line): if line.find('[结算统计]') == -1: return None #print line m = re.match(reg,line) if m: ret=','.join([ x.strip() for x in list(m.groups()) ]) #print ret return ret else: return None#testline='160125-11:00:14 Bill[40268] INFO: [结算统计]时间(1453690814)类别(1)名称(物品收入)频道(3977962)等级(2)用户(65147500)赠送(1)个物品(39)给客户(65147500),客户等级(28),签约(1), 消耗人民币(100), 客户获得人民币(8000), 频道获得人民币(2000),客户当前人民币(1320960)频道当前人民币(335560)'#parse_line(testline)#exit()def dayjiesuan(startday,endday): dates = startday.strftime("%y%m%d") #dates = "171018" print dates serverip = "67" servername = "billserver" url = "/tmp/logdir/"+dates+"_"+serverip+"/"+servername+".log."+dates+"-*" file_list=glob.glob(url) #print file_list temp_file='/tmp/haoren/'+servername+"_"+dates+"_"+serverip+".csv" #print temp_file if os.path.exists(temp_file): os.system("rm -f "+temp_file) outfile = open(temp_file, 'w') for filename in file_list: print filename file = open(filename, 'r') while 1: lines = file.readlines(100000) if not lines: break for line in lines: ret = parse_line(line) #break if ret is not None: outfile.write(ret+'\n') file.close() #break outfile.close() #print file #command = "cat %s | grep -h -w 结算统计 |grep 频道当前人民币 >> %s"%(file, temp_file) #os.system(command)def fromDayToDay(startdate, datelen, func): delta = datetime.timedelta(days=1) for i in range(0,datelen): startday = startdate + delta * i endday = startdate + delta * (i + 1) func(startday, endday) returndef fromDayToEndDay(startdate, datelen, endday, func): delta = datetime.timedelta(days=1) for i in range(0,datelen): startday = startdate + delta * i #endday = startdate + delta * (i + 1) func(startday, endday) returndef getJieSuanData(): # 获取结算统计数据 startday = datetime.date(2017, 10, 3) endday = datetime.date(2017, 10, 17) td = endday - startday datelen = td.days + 1 fromDayToEndDay(startday, datelen, endday, dayjiesuan)getJieSuanData()===================================================================================================================cat packagedata.py#!/usr/bin/env python# encoding=gbk#from pyspark.sql import Rowimport MySQLdbimport mysql_opimport datetimeimport timefrom mysql_op import MySQLimport pandas as pdimport numpy as npfrom fastparquet import ParquetFilefrom fastparquet import writedef fromDayToDay(startdate, datelen, func): delta = datetime.timedelta(days=1) for i in range(0,datelen): startday = startdate + delta * i endday = startdate + delta * (i + 1) func(startday, endday) returndef fromDayToEndDay(startdate, datelen, endday, func): delta = datetime.timedelta(days=1) for i in range(0,datelen): startday = startdate + delta * i #endday = startdate + delta * (i + 1) func(startday, endday) return # 获取购物车数据def dayPackageData(startday, endday): #数据库连接参数 dbconfig = {'host':'172.27.6.12', 'port': 3306, 'user':'haoren', 'passwd':'hjkdhskjsh', 'db':'JIESUANDB', 'charset':'utf8'} #连接数据库,创建这个类的实例 mysql_cn= MySQLdb.connect(host=dbconfig['host'], port=dbconfig['port'],user=dbconfig['user'], passwd=dbconfig['passwd'], db=dbconfig['db']) strday = startday.strftime("%Y%m%d") tsstart=time.mktime(startday.timetuple()) tsend=time.mktime(endday.timetuple()) strdate = startday.strftime("%y%m%d") sql = "SELECT `INDEX`,FUNCTION,IDX,BACKROLL,UID,OLD,OP,NUM,NEW FROM `BAOGUOTONGJI_%s`" % (strdate) print sql pddf = pd.read_sql(sql, con=mysql_cn) mysql_cn.close() print pddf.head(5) dflen = len(pddf.index) if dflen > 0: print pddf.describe() #for i in range(0,dflen): # if i # print pddf.values[i][2] # try: # newdata = pddf.values[i][2].decode('gbk').encode('utf8') # pddf.values[i][2] = newdata # except: # print "gbk2utf8 error:" + str(i) + " data:" + pddf.values[i][2] # pass write("/home/haoren/logstatis/packagedata"+strday+".parq", pddf) #df = spark.createDataFrame(pddf) #df.createOrReplaceTempView('packagedata') return def getPackageData(): startday = datetime.date(2016, 11, 23) endday = datetime.date(2016, 11, 23) td = endday - startday datelen = td.days + 1 # 获取购物车数据 fromDayToDay(startday, datelen, dayPackageData) getPackageData() ==================================================================================================================================================cat specialflowerforsinger.py#!/usr/bin/env python#-*-coding:utf-8-*-#特殊食用油客户import MySQLdbimport os, sys, re,stringimport time, tarfile,getoptoptmap = { 'dbuser' : 'haoren', 'dbpass' : 'hjkdhskjsh', 'dbhost' : '172.16.1.8', 'dbport' : 3306, 'dbname' : 'AMDB' }def main(): cur_day = time.strftime("%Y%m%d", time.localtime(time.time()-86400)) opts, args = getopt.getopt(sys.argv[1:], 'd:') for op, value in opts: if op == '-d': m = re.search('[0-9]{8}', value) if m: cur_day = value else: print "请输入8位日期(比如:20130215)" return 'no' fmt_day = time.strftime('%Y-%m-%d', time.localtime(time.mktime(time.strptime(cur_day, '%Y%m%d')))) print '客户食用油统计日期:',fmt_day db_conn = MySQLdb.connect(user=optmap['dbuser'], passwd=optmap['dbpass'], host=optmap['dbhost'], port=optmap['dbport'], db=optmap['dbname']) db_cursor=db_conn.cursor() db_conn.query("use %s"%optmap['dbname']) sql="CREATE TABLE IF NOT EXISTS `SPECIALFLOWERSINGERHISTORY` (`singer` int(10) NOT NULL DEFAULT '0',`all_history_bunch` int(10) NOT NULL DEFAULT '0',`all_history_flower` int(10) NOT NULL DEFAULT '0')" db_conn.query(sql) tabletime = time.strftime("%Y%m%d", time.localtime(time.mktime(time.strptime(cur_day, "%Y%m%d")))) #sql="CREATE TABLE IF NOT EXISTS `SPECIALFLOWERHANDSELFORSINGER_%s` (`ID` int(10) unsigned NOT NULL auto_increment,`singer` int(10) NOT NULL DEFAULT '0',`num_99` int(10) NOT NULL DEFAULT '0',`num_365` int(10) NOT NULL DEFAULT '0',`num_520` int(10) NOT NULL DEFAULT '0',`num_999` int(10) NOT NULL DEFAULT '0',`num_all` int(10) NOT NULL DEFAULT '0',`all_flower_num` int(10) NOT NULL DEFAULT '0',`all_history_bunch` int(10) NOT NULL DEFAULT '0',`all_history_flower` int(10) NOT NULL DEFAULT '0',primary key(ID))"%tabletime sql="CREATE TABLE IF NOT EXISTS `SPECIALFLOWERHANDSELFORSINGER_%s` like SPECIALFLOWERHANDSELFORSINGER_SAMPLE"%tabletime db_conn.query(sql) #多次执行回退历史 sql ="SELECT singer,num_all,all_flower_num from SPECIALFLOWERHANDSELFORSINGER_%s"%tabletime db_cursor.execute(sql) while(True): data=db_cursor.fetchone() if(data == None): break db_conn.query("update SPECIALFLOWERSINGERHISTORY set all_history_bunch=all_history_bunch-%d where singer=%d"%(int(data[1]),int(data[0]))) db_conn.query("update SPECIALFLOWERSINGERHISTORY set all_history_flower=all_history_flower-%d where singer=%d"%(int(data[2]),int(data[0]))) #清空当天 db_conn.query("delete from SPECIALFLOWERHANDSELFORSINGER_%s"%tabletime) sql ="SELECT DISTINCT singer from SPECIALFLOWERHANDSELDETAILS_%s "%tabletime db_cursor.execute(sql) singerlist=[] while(True): temp=db_cursor.fetchone() if(temp == None): break singerlist.append(int(temp[0])) for singer in singerlist: flower={} sql ="SELECT * from SPECIALFLOWERHANDSELDETAILS_%s where singer=%s and flowernum=999"%(tabletime,singer) db_cursor.execute(sql) flower['999'] = db_cursor.rowcount sql ="SELECT * from SPECIALFLOWERHANDSELDETAILS_%s where singer=%s and flowernum=99"%(tabletime,singer) db_cursor.execute(sql) flower['99'] = db_cursor.rowcount sql ="SELECT * from SPECIALFLOWERHANDSELDETAILS_%s where singer=%s and flowernum=365"%(tabletime,singer) db_cursor.execute(sql) flower['365'] = db_cursor.rowcount sql ="SELECT * from SPECIALFLOWERHANDSELDETAILS_%s where singer=%s and flowernum=520"%(tabletime,singer) db_cursor.execute(sql) flower['520'] = db_cursor.rowcount flower['num_all']= flower['999']+flower['99']+flower['365']+flower['520'] flower['all_flower']= flower['999']*999 + flower['99']*99 +flower['365']*365 + flower['520']*520 db_conn.query("insert into SPECIALFLOWERHANDSELFORSINGER_%s (singer,num_99,num_365,num_520,num_999,num_all,all_flower_num)values (%d,%d,%d,%d,%d,%d,%d)"%(tabletime,singer,flower['99'],flower['365'],flower['520'],flower['999'],flower['num_all'],flower['all_flower'])) sql ="SELECT all_history_bunch,all_history_flower from SPECIALFLOWERSINGERHISTORY where singer=%d"%singer db_cursor.execute(sql) temp=db_cursor.fetchone() if(temp == None): flower['all_history_bunch']= flower['num_all'] flower['all_history_flower']=flower['all_flower'] db_conn.query(" insert into SPECIALFLOWERSINGERHISTORY(singer,all_history_bunch,all_history_flower)values(%d,%d,%d)"%(singer,flower['all_history_bunch'],flower['all_history_flower'])) else: flower['all_history_bunch']= flower['num_all']+int(temp[0]) flower['all_history_flower']=flower['all_flower']+int(temp[1]) db_conn.query("update SPECIALFLOWERSINGERHISTORY set all_history_bunch= %d,all_history_flower= %d where `singer`= %d"%(flower['all_history_bunch'],flower['all_history_flower'],singer)) db_conn.query("update SPECIALFLOWERHANDSELFORSINGER_%s set all_history_bunch= %d,all_history_flower= %d where `singer`= %d"%(tabletime,flower['all_history_bunch'],flower['all_history_flower'],singer)) db_conn.commit() db_cursor.close() db_conn.close()main()#if __name__ == "__main__":# main()=================================================================================================cat specialflowerforsender.py#!/usr/bin/env python#-*-coding:utf-8-*-#特殊食用油送花者import MySQLdbimport os, sys, re,stringimport time, tarfile,getoptoptmap = { 'dbuser' : 'haoren', 'dbpass' : 'hjkdhskjsh', 'dbhost' : '172.16.1.8', 'dbport' : 3306, 'dbname' : 'AMDB' }def main(): cur_day = time.strftime("%Y%m%d", time.localtime(time.time()-86400)) opts, args = getopt.getopt(sys.argv[1:], 'd:') for op, value in opts: if op == '-d': m = re.search('[0-9]{8}', value) if m: cur_day = value else: print "请输入8位日期(比如:20130215)" return 'no' fmt_day = time.strftime('%Y-%m-%d', time.localtime(time.mktime(time.strptime(cur_day, '%Y%m%d')))) print '送花者统计日期:',fmt_day db_conn = MySQLdb.connect(user=optmap['dbuser'], passwd=optmap['dbpass'], host=optmap['dbhost'], port=optmap['dbport'], db=optmap['dbname']) db_cursor=db_conn.cursor() db_conn.query("use %s"%optmap['dbname']) sql="CREATE TABLE IF NOT EXISTS `SPECIALFLOWERSENDERHISTORY` (`userid` int(10) NOT NULL DEFAULT '0',`all_history_bunch` int(10) NOT NULL DEFAULT '0',`all_history_flower` int(10) NOT NULL DEFAULT '0')" db_conn.query(sql) tabletime = time.strftime("%Y%m%d", time.localtime(time.mktime(time.strptime(cur_day, "%Y%m%d")))) #sql="CREATE TABLE IF NOT EXISTS `SPECIALFLOWERHANDSELFORSENDER_%s` (`ID` int(10) unsigned NOT NULL auto_increment,`userid` int(10) NOT NULL DEFAULT '0',`num_99` int(10) NOT NULL DEFAULT '0',`num_365` int(10) NOT NULL DEFAULT '0',`num_520` int(10) NOT NULL DEFAULT '0',`num_999` int(10) NOT NULL DEFAULT '0',`num_all` int(10) NOT NULL DEFAULT '0',`all_flower_num` int(10) NOT NULL DEFAULT '0',`all_history_bunch` int(10) NOT NULL DEFAULT '0',`all_history_flower` int(10) NOT NULL DEFAULT '0',primary key(ID))"%tabletime sql="CREATE TABLE IF NOT EXISTS `SPECIALFLOWERHANDSELFORSENDER_%s` like SPECIALFLOWERHANDSELFORSENDER_SAMPLE"%tabletime db_conn.query(sql) #多次执行需回退之前执行的历史记录 sql ="SELECT userid,num_all,all_flower_num from SPECIALFLOWERHANDSELFORSENDER_%s"%tabletime db_cursor.execute(sql) while(True): data=db_cursor.fetchone() if(data == None): break db_conn.query("update SPECIALFLOWERSENDERHISTORY set all_history_bunch=all_history_bunch-%d where userid=%d"%(int(data[1]),int(data[0]))) db_conn.query("update SPECIALFLOWERSENDERHISTORY set all_history_flower=all_history_flower-%d where userid=%d"%(int(data[2]),int(data[0]))) #清空当天 db_conn.query("delete from SPECIALFLOWERHANDSELFORSENDER_%s"%tabletime) sql ="SELECT DISTINCT userid from SPECIALFLOWERHANDSELDETAILS_%s "%tabletime db_cursor.execute(sql) userlist=[] while(True): temp=db_cursor.fetchone() if(temp == None): break userlist.append(int(temp[0])) for user in userlist: flower={} sql ="SELECT * from SPECIALFLOWERHANDSELDETAILS_%s where userid=%s and flowernum=999"%(tabletime,user) db_cursor.execute(sql) flower['999'] = db_cursor.rowcount sql ="SELECT * from SPECIALFLOWERHANDSELDETAILS_%s where userid=%s and flowernum=99"%(tabletime,user) db_cursor.execute(sql) flower['99'] = db_cursor.rowcount sql ="SELECT * from SPECIALFLOWERHANDSELDETAILS_%s where userid=%s and flowernum=365"%(tabletime,user) db_cursor.execute(sql) flower['365'] = db_cursor.rowcount sql ="SELECT * from SPECIALFLOWERHANDSELDETAILS_%s where userid=%s and flowernum=520"%(tabletime,user) db_cursor.execute(sql) flower['520'] = db_cursor.rowcount flower['num_all']= flower['999']+flower['99']+flower['365']+flower['520'] flower['all_flower']= flower['999']*999 + flower['99']*99 +flower['365']*365 + flower['520']*520 db_conn.query("insert into SPECIALFLOWERHANDSELFORSENDER_%s (userid,num_99,num_365,num_520,num_999,num_all,all_flower_num)values (%d,%d,%d,%d,%d,%d,%d)"%(tabletime,user,flower['99'],flower['365'],flower['520'],flower['999'],flower['num_all'],flower['all_flower'])) sql ="SELECT all_history_bunch,all_history_flower from SPECIALFLOWERSENDERHISTORY where userid=%d"%user db_cursor.execute(sql) temp=db_cursor.fetchone() if(temp == None): flower['all_history_bunch']= flower['num_all'] flower['all_history_flower']=flower['all_flower'] db_conn.query(" insert into SPECIALFLOWERSENDERHISTORY(userid,all_history_bunch,all_history_flower)values(%d,%d,%d)"%(user,flower['all_history_bunch'],flower['all_history_flower'])) else: flower['all_history_bunch']= flower['num_all']+int(temp[0]) flower['all_history_flower']=flower['all_flower']+int(temp[1]) db_conn.query("update SPECIALFLOWERSENDERHISTORY set all_history_bunch= %d,all_history_flower= %d where `userid`= %d"%(flower['all_history_bunch'],flower['all_history_flower'],user)) db_conn.query("update SPECIALFLOWERHANDSELFORSENDER_%s set all_history_bunch= %d,all_history_flower= %d where `userid`= %d"%(tabletime,flower['all_history_bunch'],flower['all_history_flower'],user)) db_conn.commit() db_cursor.close() db_conn.close()main()#if __name__ == "__main__":# main()==========================================================================================cat rewardInfo.py#!/usr/bin/env python#-*-coding:utf-8-*-#分析日志,获得每天用户中奖信息#每天一张表import MySQLdbimport os, sys, re,stringimport time, tarfile,getoptimport datetimeimport operatorimport copyoptmap = { 'dbuser' : 'haoren', 'dbpass' : 'hjkdhskjsh', 'dbhost' : '172.16.1.8', 'dbhost_gm' : '172.16.1.8', 'dbport' : 3306, 'dbname' : 'FMDB', 'dbname_gm' : 'AMDB'}reward = { 'userID':0, 'type':0, 'level':0, 'num':0, 'date':0}def get_files(dir, pattern): res_file_list =[] if os.path.exists(dir): cur_file_list = os.listdir(dir) for file_name in cur_file_list: if re.search(pattern, file_name): res_file_list.append(file_name) return res_file_list else: return 'no' def main(): cur_day = time.strftime("%Y%m%d", time.localtime(time.time()-86400)) log_day = time.strftime('%y%m%d', time.localtime(time.mktime(time.strptime(cur_day, '%Y%m%d')))) if len(sys.argv)>1: cur_day = sys.argv[1] log_day = sys.argv[1][2:] dirname = "/home/haoren/logdir/%s_19"%log_day reward_list = [] for fileName in os.listdir(dirname): if (fileName.find("loginrewardserver.log") == -1): continue a_file = open(dirname+"/"+fileName,'r') #140522-00:31:16 LoginRewardServer[16600] DEBUG: 用户[54011503]在第[1]关中奖,中奖类型[1],中奖数量[100],是否广播给客户端[0] for a_line in a_file.readlines(): reward_info = re.search("^(\S+) LoginRewardServer\[\d+\] DEBUG: 用户\[(\d+)\]在第\[(\d+)\]关中奖,中奖类型\[(\d+)\],中奖数量\[(\d+)\],是否广播给客户端\[0\]",a_line) if reward_info: reward_ = {} reward_['userID'] = reward_info.group(2) reward_['type'] = reward_info.group(4) reward_['level'] = reward_info.group(3) reward_['num'] = reward_info.group(5) reward_['date'] = str("20") + str(reward_info.group(1)[0:2]) + "-" + str(reward_info.group(1)[2:4]) + "-" + str(reward_info.group(1)[4:6]) + " " + str(reward_info.group(1)[7:]) reward_list.append(reward_) a_file.close()# for reward in reward_list:# print "uid:" + reward['userID'] + " type:" + reward['type'] + " level:" + reward['level'] + " num:" + reward['num'] + " date:" + reward['date'] db_conn = MySQLdb.connect(user=optmap['dbuser'], passwd=optmap['dbpass'], host=optmap['dbhost_gm'], port=optmap['dbport'], db=optmap['dbname_gm']) db_conn.query("use %s" %optmap['dbname_gm']) db_cursor = db_conn.cursor() tabletime = time.strftime("%Y%m%d", time.localtime(time.mktime(time.strptime(cur_day, "%Y%m%d")))) #print "数据库表时间后缀%s"%tabletime tabletime = cur_day print '用户中奖信息,统计日期:',tabletime sql="CREATE TABLE IF NOT EXISTS `REWARDINFO_%s` like REWARDINFO_SAMPLE"%tabletime #delete try: db_conn.query("drop table REWARDINFO_%s"%tabletime) except: pass #print "The first statistics." #create db_conn.query(sql) #update yesterday data total = range(10) for reward in reward_list: timeArray = time.strptime(reward['date'],"%Y-%m-%d %H:%M:%S") timeStamp = int(time.mktime(timeArray)) sql = "insert into REWARDINFO_%s(userID,type,level,num,timestamp)value(%d,%d,%d,%d,%d)"%(tabletime,int(reward['userID']),int(reward['type']),int(reward['level']),int(reward['num']),timeStamp) db_conn.query(sql) #update rewardTotal for i in range(1,10): if( int(reward['level']) == int(i) ): total[i] = int(total[i]) + int(reward['num']) for i in range(1,10): sql = "insert into SIGNEDCARD(level%d,timestamp)value(%d,%d)"%(int(i),total[i],timeStamp) db_conn.query(sql) db_conn.commit() db_cursor.close() db_conn.close() print "SUCCESS."main()#if __name__ == "__main__":# main()===============================================================================================================cat vipactiveusernum.py#!/usr/bin/env python#-*-coding:utf-8-*-#会员信息统计import MySQLdbimport os, sys, re, stringimport time, getoptoptmap = { 'dbuser' : 'haoren', 'dbpass' : 'hjkdhskjsh', 'dbhost' : '172.16.1.8', 'dbport' : 3306, 'dbname' : 'AMDB', #'logdir' : '/home/haoren/log/', #内网环境日志目录 'logdir' : '/home/haoren/logdir/', #外网环境日志目录 #'logpattern' : '^chat_sessionserver.log.', #内网环境日志名称前缀 'logpattern' : '^sessionserver.log.' #外网环境日志名称前缀 }def get_files(dir, pattern): print dir, pattern match_file_list = [] if os.path.exists(dir): cur_file_list = os.listdir(dir) for file_name in cur_file_list: if re.search(pattern, file_name): match_file_list.append(file_name) return match_file_list else: return 'no'def main(): one_day = time.strftime("%Y%m%d", time.localtime(time.time() - 24*60*60)) #默认日期为脚本运行的上一天 opts, args = getopt.getopt(sys.argv[1:], 'd:') for op, value in opts: if op == '-d': m = re.search('[0-9]{8}', value) if m: one_day = value else: print "请输入8位日期(比如:20130215)" return 'no' print "正在读取VIP用户数据(%s)..." %one_day db_conn = MySQLdb.connect(user=optmap['dbuser'], passwd=optmap['dbpass'], host=optmap['dbhost'], port=optmap['dbport'], db=optmap['dbname']) db_cursor = db_conn.cursor() temp_vip_active_user_num_file_name = '/tmp/vipactiveusernumtemp.txt' command = "cat /dev/null > %s" %(temp_vip_active_user_num_file_name) os.system(command) if re.search('haoren', optmap['logdir']): print '外网环境' log_dir_name_list = get_files(optmap['logdir'], one_day[2:]) for log_dir_name_item in log_dir_name_list: log_dir_full_path = optmap['logdir']+log_dir_name_item+'/' log_file_name_list = get_files(log_dir_full_path, optmap['logpattern'] + one_day[2:]) for log_file_name_item in log_file_name_list: print log_file_name_item command = "cat %s%s |awk '/用户登录/' |awk '/vip状态/' >> %s" % (log_dir_full_path, log_file_name_item, temp_vip_active_user_num_file_name) os.system(command) else: print '内网环境' log_file_name_list = get_files(optmap['logdir'], optmap['logpattern'] + one_day[2:]) for log_file_name_item in log_file_name_list: command = "cat %s%s |awk '/用户登录/' |awk '/vip状态/' >> %s" % (optmap['logdir'], log_file_name_item, temp_vip_active_user_num_file_name) os.system(command) command = "cat %s |wc -l" %temp_vip_active_user_num_file_name os.system(command) #一天当中用户可能从月会员降级到周会员,造成不同会员状态的同一帐号统计两次,所以总会员!=年会员+月会员+周会员) #不同状态的会员用同一计算机登录,所以总mac/ip!=年mac/ip+月mac/ip+周mac/ip total_account_map = {} total_mac_map = {} total_ip_map = {} before_account_map = {} before_mac_map = {} before_ip_map = {} account_map = {1:{}, 2:{}, 3:{}, 11:{}, 12:{}, 13:{}} mac_map = {1:{}, 2:{}, 3:{}, 11:{}, 12:{}, 13:{}} ip_map = {1:{}, 2:{}, 3:{}, 11:{}, 12:{}, 13:{}} temp_vip_active_user_num_file = open(temp_vip_active_user_num_file_name) for one_line in temp_vip_active_user_num_file.readlines(): match = re.search("^(\S+) SS\[\d+\] TRACE: 用户登录:imid:(\d+),mac地址:(\d+),ip地址:(\d+),vip状态:(\d+),登录时间:(\d+)(\S+)", one_line) if match: if string.atoi(match.group(5)) in (1, 2, 3): total_account_map[string.atoi(match.group(2))] = string.atoi(match.group(5)) total_mac_map[string.atoi(match.group(3))] = string.atoi(match.group(5)) total_ip_map[string.atoi(match.group(4))] = string.atoi(match.group(5)) elif string.atoi(match.group(5)) in (11, 12, 13): before_account_map[string.atoi(match.group(2))] = string.atoi(match.group(5)) before_mac_map[string.atoi(match.group(3))] = string.atoi(match.group(5)) before_ip_map[string.atoi(match.group(4))] = string.atoi(match.group(5)) account_map[string.atoi(match.group(5))][string.atoi(match.group(2))] = string.atoi(match.group(3)) mac_map[string.atoi(match.group(5))][string.atoi(match.group(3))] = string.atoi(match.group(2)) ip_map[string.atoi(match.group(5))][string.atoi(match.group(4))] = string.atoi(match.group(2)) temp_vip_active_user_num_file.close() dword_time = time.mktime(time.strptime(one_day, '%Y%m%d')) db_conn.query("use %s" %optmap['dbname']) sql = "delete from VIPACTIVEUSERNUM where active_time='%d'" %dword_time print sql db_conn.query(sql) sql = "insert into VIPACTIVEUSERNUM (active_time) values('%d')" %(dword_time) print sql db_conn.query(sql) sql = "update VIPACTIVEUSERNUM set year_account_num=%d, year_mac_num=%d, year_ip_num=%d, month_account_num=%d, month_mac_num=%d, month_ip_num=%d, week_account_num=%d, week_mac_num=%d, week_ip_num=%d, total_mac_num=%d, total_ip_num=%d, before_account_num=%d, before_mac_num=%d, before_ip_num=%d where active_time='%d'" %(len(account_map[3]), len(mac_map[3]), len(ip_map[3]), len(account_map[2]), len(mac_map[2]), len(ip_map[2]), len(account_map[1]), len(mac_map[1]), len(ip_map[1]), len(total_mac_map), len(total_ip_map), len(before_account_map), len(before_mac_map), len(before_ip_map), dword_time) print sql db_conn.query(sql) db_conn.commit() db_cursor.close() db_conn.close()main()#if __name__ == "__main__"# main()===================================================================================================================cat specialflowerdetails.py#!/usr/bin/env python#-*-coding:utf-8-*-#特殊食用油明细import MySQLdbimport os, sys, re,stringimport time, tarfile,getoptoptmap = { 'dbuser' : 'haoren', 'dbpass' : 'hjkdhskjsh', 'dbhost' : '172.16.1.8', 'dbport' : 3306, 'dbname' : 'AMDB' }def get_files(dir, pattern): res_file_list =[] if os.path.exists(dir): cur_file_list = os.listdir(dir) for file_name in cur_file_list: if re.search(pattern, file_name): res_file_list.append(file_name) return res_file_list else: return 'no'def main(): cur_day = time.strftime("%Y%m%d", time.localtime(time.time()-86400)) opts, args = getopt.getopt(sys.argv[1:], 'd:') for op, value in opts: if op == '-d': m = re.search('[0-9]{8}', value) if m: cur_day = value else: print "请输入8位日期(比如:20130215)" return 'no' log_day = time.strftime('%y%m%d', time.localtime(time.mktime(time.strptime(cur_day, '%Y%m%d')))) fmt_day = time.strftime('%Y-%m-%d', time.localtime(time.mktime(time.strptime(cur_day, '%Y%m%d')))) print '食用油明细统计日期:',fmt_day #log_day = time.strftime("%y%m%d", time.localtime(time.time()-86400)) dirname="/home/haoren/logdir/%s_138"%log_day print dirname #log_day = time.strftime("%y%m%d", time.localtime(time.time()-86400)) dirname1="/home/haoren/logdir/%s_139"%log_day print dirname1 db_conn = MySQLdb.connect(user=optmap['dbuser'], passwd=optmap['dbpass'], host=optmap['dbhost'], port=optmap['dbport'], db=optmap['dbname']) db_cursor=db_conn.cursor() db_conn.query("use %s"%optmap['dbname']) tabletime = time.strftime("%Y%m%d", time.localtime(time.mktime(time.strptime(cur_day, "%Y%m%d")))) #sql="CREATE TABLE IF NOT EXISTS `SPECIALFLOWERHANDSELDETAILS_%s` (`ID` int(10) unsigned NOT NULL auto_increment,`occur_time` varchar(24) NOT NULL DEFAULT '', `userid` int(10) NOT NULL DEFAULT '0',`singer` int(10) NOT NULL DEFAULT '0',`flowernum` int(10) NOT NULL DEFAULT '0',primary key(ID))"%tabletime sql="CREATE TABLE IF NOT EXISTS `SPECIALFLOWERHANDSELDETAILS_%s` like SPECIALFLOWERHANDSELDETAILS_SAMPLE"%tabletime db_conn.query(sql) db_conn.query("delete from SPECIALFLOWERHANDSELDETAILS_%s"%tabletime) if os.path.exists("/tmp/specialflower.txt"): os.system("rm -f /tmp/specialflower.txt") #dirname="/home/xuxiu/log/" #dirname="/tmp/" file_list2=get_files(dirname,'flowerserver') for file2 in file_list2: command = "cat %s/%s | awk '/特殊食用油/'>> /tmp/specialflower.txt"%(dirname,file2) os.system(command) file_list3=get_files(dirname1,'flowerserver') for file3 in file_list3: command = "cat %s/%s | awk '/特殊食用油/'>> /tmp/specialflower.txt"%(dirname1,file3) os.system(command) #特殊食用油 filename='/tmp/specialflower.txt' record = {} a_file = open(filename, 'r') #130307-16:13:43 Show[980] TRACE: [特殊食用油]送花者:21001435,接受者:21000127,食用油:999 for a_line in a_file.readlines(): m = re.search("^(\S+) FlowerServer\[\d+\] TRACE: \[特殊食用油\]送花者:(\d+),接受者:(\d+),食用油:(\d+)", a_line) if m: db_conn.query("insert into SPECIALFLOWERHANDSELDETAILS_%s(occur_time,userid,singer,flowernum) values('%s',%d,%d,%d)"%(tabletime,str(m.group(1)),int(m.group(2)),int(m.group(3)),int(m.group(4)))) a_file.close() db_conn.commit() db_cursor.close() db_conn.close()main()#if __name__ == "__main__":# main()========================================================================================================cat racingincomemonthly.py#!/usr/bin/env python#-*-coding:utf-8-*-#人民赛车日报#****************************************使用说明****************************************************# 内网测试通过命令行参数# -d 指定统计时间# 示例如下:# [haoren@localhost tongji]$ ./racingincomemonthly.py -d 20141112# 外网无需加参数,使用默认值#****************************************使用说明****************************************************import MySQLdbimport os, sys, re,stringimport time, tarfile,getopt#import argparseoptmap = { 'dbuser' : 'haoren', 'dbpass' : 'hjkdhskjsh', 'dbhost' : '172.16.1.8', 'dbport' : 3306, 'dbname' : 'AMDB' }def get_files(dir, pattern): res_file_list =[] if os.path.exists(dir): cur_file_list = os.listdir(dir) cur_file_list.sort() for file_name in cur_file_list: if re.search(pattern, file_name): res_file_list.append(file_name) return res_file_list else: return 'no'def main(): #外网环境默认参数 cur_day = time.strftime("%Y%m%d", time.localtime(time.time()-86400)) tmpdirname = "/tmp/" logname = "billserver" #内网测试指定参数 opts, args = getopt.getopt(sys.argv[1:], 'd:p:t:') for op, value in opts: if op == '-d': m = re.search('[0-9]{8}', value) if m: cur_day = value log_suffix = time.strftime('%y%m%d', time.localtime(time.mktime(time.strptime(cur_day, '%Y%m%d')))) logname = "billserver.log" else: print "请输入8位日期(比如:20130215)" return 'no' log_day = time.strftime('%y%m%d', time.localtime(time.mktime(time.strptime(cur_day, '%Y%m%d')))) logdirname = "/home/haoren/logdir/%s_67"%log_day fmt_day = time.strftime('%Y-%m-%d', time.localtime(time.mktime(time.strptime(cur_day, '%Y%m%d')))) d_time = time.mktime(time.strptime(fmt_day,'%Y-%m-%d')) print '统计日期:',fmt_day print '日志名称:',logname print '日志路径:',logdirname print '临时文本:',tmpdirname #赛车收入 if os.path.exists("%sracingincome.txt"%tmpdirname): os.system("rm -f %sracingincome.txt"%tmpdirname) file_list1=get_files(logdirname, logname) for file1 in file_list1: command = "cat %s/%s | awk '/人民赛车/' >> %sracingincome.txt"%(logdirname,file1,tmpdirname) os.system(command) #赛车收入 filename='%sracingincome.txt'%tmpdirname a_file = open(filename, 'r') #赛车消耗用户总人民币 allcoin = 0 #赛车用户赢取人民币 playerwin = 0 #参与人数 playernum = 0 #机器人数 robotnum = 0 #下注次数 betnum = 0 #中奖次数 winnum = 0 #参与用户 userlist = [] for a_line in a_file.readlines(): #150226-05:11:23 Bill[990] INFO: [人民赛车]用户(21235)押注,扣除人民币(1000)成功 m = re.search("^(\S+) Bill\[\d+\] INFO: \[人民赛车]用户\((\d+)\)押注,扣除人民币\((\d+)\)成功", a_line) if m: allcoin += int(m.group(3)) betnum += 1 userid = int(m.group(2)) #参与用户列表 if (userid not in userlist): userlist.append(userid) playernum += 1 #150226-05:17:22 Bill[990] INFO: [人民赛车]用户(21235)增加人民币(10)成功 m1 = re.search("^(\S+) Bill\[\d+\] INFO: \[人民赛车]用户\((\d+)\)增加人民币\((\d+)\)成功", a_line) if m1: playerwin += int(m1.group(3)) winnum += 1 print '总金额(人民币):',allcoin/100 print '用户赢的人民币:',playerwin/100 print '官方赢的人民币:',(allcoin-playerwin)/100 print '参与人数:',playernum print '用户列表:',len(userlist) print '下注次数:',betnum print '中奖次数:',winnum a_file.close() db_conn = MySQLdb.connect(user=optmap['dbuser'], passwd=optmap['dbpass'], host=optmap['dbhost'], port=optmap['dbport'], db=optmap['dbname']) db_cursor=db_conn.cursor() db_conn.query("use %s"%optmap['dbname']) d_time = time.mktime(time.strptime(cur_day,'%Y%m%d')) db_conn.query("delete from RACINGINCOMEMONTHLY where TIME=%u"%d_time) db_conn.query("insert into RACINGINCOMEMONTHLY(TIME,TOTALCOIN,PLAYERWINCOIN,PLAYERNUM,BETNUM,LUCKYNUM) values(%d,%d,%d,%d,%d,%d)"%(d_time, allcoin, playerwin, playernum, betnum, winnum)) db_conn.commit() db_cursor.close() db_conn.close()main()#if __name__ == "__main__":# main()======================================================================================================================================cat charge.py#!/usr/bin/env python#-*-coding:utf-8-*-#消费相关import MySQLdbimport os, sys, re,stringimport time, tarfile,getoptoptmap = { 'dbuser' : 'haoren', 'dbpass' : 'hjkdhskjsh', 'dbhost' : '172.16.1.8', 'dbport' : 3306, 'dbname' : 'AMDB' }def get_files(dir, pattern): res_file_list =[] if os.path.exists(dir): cur_file_list = os.listdir(dir) for file_name in cur_file_list: if re.search(pattern, file_name): res_file_list.append(file_name) return res_file_list else: return 'no'def main(): cur_day = time.strftime("%Y%m%d", time.localtime(time.time()-86400)) opts, args = getopt.getopt(sys.argv[1:], 'd:') for op, value in opts: if op == '-d': m = re.search('[0-9]{8}', value) if m: cur_day = value else: print "请输入8位日期(比如:20130215)" return 'no' fmt_day = time.strftime('%Y-%m-%d', time.localtime(time.mktime(time.strptime(cur_day, '%Y%m%d')))) print '消费统计日期:',fmt_day #log_day = time.strftime("%y%m%d", time.localtime(time.time()-86400)) log_day = time.strftime('%y%m%d', time.localtime(time.mktime(time.strptime(cur_day, '%Y%m%d')))) #dirname="/home/xuxiu/log/%s/"%log_day #dirname="/home/xuxiu/log/1/" dirname="/home/haoren/logdir/%s_67"%log_day print dirname #会员消耗点数 if os.path.exists("/tmp/vippoint.txt"): os.system("rm -f /tmp/vippoint.txt") #人民币消耗点数 if os.path.exists("/tmp/coinpoint.txt"): os.system("rm -f /tmp/coinpoint.txt") #充值 if os.path.exists("/tmp/recharge.txt"): os.system("rm -f /tmp/recharge.txt") #人民币消耗 if os.path.exists("/tmp/coinreduce.txt"): os.system("rm -f /tmp/coinreduce.txt") #人民币补偿 if os.path.exists("/tmp/coingmadd.txt"): os.system("rm -f /tmp/coingmadd.txt") #dirname="/tmp/" #物品消耗 if os.path.exists("/tmp/props.txt"): os.system("rm -f /tmp/props.txt") file_list2=get_files(dirname,'billserver') for file2 in file_list2: command = "cat %s/%s | awk '/点数统计/'|awk '/会员消耗点数/'>> /tmp/vippoint.txt"%(dirname,file2) os.system(command) command = "cat %s/%s | awk '/点数统计/'|awk '/人民币消耗点数/'>> /tmp/coinpoint.txt"%(dirname,file2) os.system(command) command = "cat %s/%s | awk '/点数统计/'|awk '/充值点数/'>> /tmp/recharge.txt"%(dirname,file2) os.system(command) command = "cat %s/%s | awk '/人民币统计/'|awk '/会员消耗人民币/'>> /tmp/coinreduce.txt"%(dirname,file2) os.system(command) command = "cat %s/%s | awk '/人民币统计/'|awk '/GM增加人民币/'>> /tmp/coingmadd.txt"%(dirname,file2) os.system(command) command = "cat %s/%s | awk '/物品统计/'>> /tmp/props.txt"%(dirname,file2) os.system(command) #会员消耗点数 filename='/tmp/vippoint.txt' record = {} record['vippoint']=0 a_file = open(filename, 'r') for a_line in a_file.readlines(): m = re.search("^(\S+) Bill\[\d+\] TRACE: \[点数统计\]会员消耗点数,用户:(\d+),点数:(\d+)", a_line) if m: vfind = 0 for mykey in record: if mykey == 'vippoint': vfind = 1 record[mykey] += int(m.group(3)) if(0==vfind): record['vippoint'] = int(m.group(3)) print '会员总消耗点数:',record['vippoint'] a_file.close() #人民币消耗点数 人民币产出 filename='/tmp/coinpoint.txt' a_file = open(filename, 'r') record['coinpoint']=0 record['coinexchange']=0 for a_line in a_file.readlines(): m = re.search("^(\S+) Bill\[\d+\] TRACE: \[点数统计\]人民币消耗点数,用户:(\d+),点数:(\d+),产生人民币:(\d+)", a_line) if m: cfind = 0 for mykey in record: if mykey == 'coinpoint': cfind = 1 record[mykey] += int(m.group(3)) if(0==cfind): record['coinpoint'] = int(m.group(3)) #兑换的人民币 find1 = 0 for mykey in record: if mykey == 'coinexchange': find1 = 1 record[mykey] += int(m.group(4)) if(0==find1): record['coinexchange'] = int(m.group(4)) print '人民币总消耗点数:',record['coinpoint'] record['allconsumepoint']= record['coinpoint']+record['vippoint'] print '总消耗点数',record['allconsumepoint'] print '兑换产生人民币',record['coinexchange'] a_file.close() #总充值点数 filename='/tmp/recharge.txt' a_file = open(filename, 'r') record['recharge']=0 for a_line in a_file.readlines(): m = re.search("^(\S+) Bill\[\d+\] TRACE: \[点数统计\]充值点数,用户:(\d+),点数:(\d+)", a_line) if m: rfind = 0 for mykey in record: if mykey == 'recharge': rfind = 1 record[mykey] += int(m.group(3)) if(0==rfind): record['recharge'] = int(m.group(3)) print '总充值点数:',record['recharge'] a_file.close() #人民币消耗(会员) filename='/tmp/coinreduce.txt' a_file = open(filename, 'r') record['coinreduce']=0 for a_line in a_file.readlines(): m = re.search("^(\S+) Bill\[\d+\] TRACE: \[人民币统计\]会员消耗人民币,用户:(\d+),人民币:(\d+)", a_line) if m: crfind = 0 for mykey in record: if mykey == 'coinreduce': crfind = 1 record[mykey] += int(m.group(3)) if(0==crfind): record['coinreduce'] = int(m.group(3)) print '会员消耗人民币总量:',record['coinreduce'] a_file.close() #人民币消耗(物品) filename='/tmp/props.txt' a_file = open(filename, 'r') record['coinprops']=0 #130815-15:01:06 Bill[990] INFO: [物品统计]频道(210043)等级(9)用户(21000162)赠送(1)个物品(3)给客户(21000264),客户等级(1),签约(0), 消耗人民币(10), 客户获得人民币(470), 频道获得人民币(30) for a_line in a_file.readlines(): m = re.search("^(\S+) Bill\[\d+\] INFO: \[物品统计\]频道\((\d+)\)等级\((\d+)\)用户\((\d+)\)赠送\((\d+)\)个物品\((\d+)\)给客户\((\d+)\),客户等级\((\d+)\),签约\((\d+)\), 消耗人民币\((\d+)\), 客户获得人民币\((\d+)\), 频道获得金币\((\d+)\)", a_line) if m: crfind = 0 for mykey in record: if mykey == 'coinprops': crfind = 1 record[mykey] += int(m.group(10)) if(0==crfind): record['coinprops'] = int(m.group(10)) print '物品消耗人民币总量:',record['coinprops'] a_file.close() record['allconsumecoin'] = record['coinprops'] + record['coinreduce'] print '人民币消耗总量:',record['allconsumecoin'] #GM增加人民币 filename='/tmp/coingmadd.txt' a_file = open(filename, 'r') record['coingmadd']=0 for a_line in a_file.readlines(): m = re.search("^(\S+) Bill\[\d+\] TRACE: \[人民币统计\]GM增加人民币,用户:(\d+),人民币:(\d+)", a_line) if m: cafind = 0 for mykey in record: if mykey == 'coingmadd': cafind = 1 record[mykey] += int(m.group(3)) if(0==cafind): record['coingmadd'] = int(m.group(3)) a_file.close() print '补偿产出人民币总量:',record['coingmadd'] record['cointodaylast'] = record['coinexchange'] - record['coinreduce'] - record['coinprops'] + record['coingmadd'] print '人民币今天结存:',record['cointodaylast'] db_conn = MySQLdb.connect(user=optmap['dbuser'], passwd=optmap['dbpass'], host=optmap['dbhost'], port=optmap['dbport'], db=optmap['dbname']) db_cursor=db_conn.cursor() db_conn.query("use %s"%optmap['dbname']) #先对表初始化 #cur_day = time.strftime("%Y%m%d", time.localtime(time.time()-86400)) #fmt_day = time.strftime('%Y-%m-%d', time.localtime(time.mktime(time.strptime(cur_day, '%Y%m%d')))) db_conn.query("delete from TRANSACTIONSUMMARY where tongji_time='%s'"%fmt_day) db_conn.query("insert into TRANSACTIONSUMMARY (tongji_time) values('%s')"%fmt_day) db_conn.query("update TRANSACTIONSUMMARY set all_recharge_point=%d,all_consume_point=%d,coin_consume_point=%d,vip_consume_point=%d,gm_add_coin=%d,exchange_add_coin=%d,all_consume_coin=%d,today_balance_coin=%d,VIP_CONSUME_COIN=%d,PROPS_CONSUME_COIN=%d where tongji_time='%s'"%(record['recharge'],record['allconsumepoint'],record['coinpoint'],record['vippoint'],record['coingmadd'],record['coinexchange'],record['allconsumecoin'],record['cointodaylast'],record['coinreduce'],record['coinprops'],fmt_day)) yesterday = time.strftime('%Y%m%d', time.localtime(time.mktime(time.strptime(cur_day, '%Y%m%d'))-86400)) last_day = time.strftime('%Y-%m-%d', time.localtime(time.mktime(time.strptime(yesterday, '%Y%m%d')))) last_day_coin_last = 0 sql ="SELECT all_balance_coin from TRANSACTIONSUMMARY where tongji_time='%s'"%last_day db_cursor.execute(sql) temp=db_cursor.fetchone() if(temp != None): last_day_coin_last=int(temp[0]) print '昨天:%s 人民币历史结余:'%last_day,last_day_coin_last record['coinhistorylast']= last_day_coin_last+record['cointodaylast'] print '人民币历史结余:',record['coinhistorylast'] db_conn.query("update TRANSACTIONSUMMARY set all_balance_coin=%d where tongji_time='%s'"%(record['coinhistorylast'],fmt_day)) db_conn.commit() #创建交易明细表 tabletime = time.strftime("%Y%m%d", time.localtime(time.mktime(time.strptime(cur_day, "%Y%m%d")))) sql="CREATE TABLE IF NOT EXISTS `TRANSACTIONDETAILS_%s` like TRANSACTIONDETAILS_SAMPLE"%tabletime db_conn.query(sql) db_conn.query("delete from TRANSACTIONDETAILS_%s"%tabletime) #充值明细 filename='/tmp/recharge.txt' a_file = open(filename, 'r') for a_line in a_file.readlines(): m = re.search("^(\S+) Bill\[\d+\] TRACE: \[点数统计\]充值点数,用户:(\d+),点数:(\d+)", a_line) if m: db_conn.query("insert into TRANSACTIONDETAILS_%s(userid,occur_time,type,point_add) values(%d,'%s',0,%d)"%(tabletime,int(m.group(2)),str(m.group(1)),int(m.group(3)))) a_file.close() #点数兑换人民币明细 filename='/tmp/coinpoint.txt' a_file = open(filename, 'r') for a_line in a_file.readlines(): m = re.search("^(\S+) Bill\[\d+\] TRACE: \[点数统计\]人民币消耗点数,用户:(\d+),点数:(\d+),产生人民币:(\d+)", a_line) if m: db_conn.query("insert into TRANSACTIONDETAILS_%s (userid,occur_time,type,point_reduce,coin_add) values(%d,'%s',1,%d,%d)"%(tabletime,int(m.group(2)),str(m.group(1)),int(m.group(3)),int(m.group(4)))) a_file.close() #点数兑换会员明细 filename='/tmp/vippoint.txt' a_file = open(filename, 'r') for a_line in a_file.readlines(): m = re.search("^(\S+) Bill\[\d+\] TRACE: \[点数统计\]会员消耗点数,用户:(\d+),点数:(\d+)", a_line) if m: db_conn.query("insert into TRANSACTIONDETAILS_%s (userid,occur_time,type,point_reduce) values(%d,'%s',2,%d)"%(tabletime,int(m.group(2)),str(m.group(1)),int(m.group(3)))) a_file.close() #人民币兑换会员明细 filename='/tmp/coinreduce.txt' a_file = open(filename, 'r') for a_line in a_file.readlines(): m = re.search("^(\S+) Bill\[\d+\] TRACE: \[人民币统计\]会员消耗人民币,用户:(\d+),人民币:(\d+)", a_line) if m: db_conn.query("insert into TRANSACTIONDETAILS_%s (userid,occur_time,type,coin_reduce) values(%d,'%s',3,%d)"%(tabletime,int(m.group(2)),str(m.group(1)),int(m.group(3)))) a_file.close() #GM增加人民币明细 filename='/tmp/coingmadd.txt' a_file = open(filename, 'r') for a_line in a_file.readlines(): m = re.search("^(\S+) Bill\[\d+\] TRACE: \[人民币统计\]GM增加人民币,用户:(\d+),人民币:(\d+)", a_line) if m: db_conn.query("insert into TRANSACTIONDETAILS_%s (userid,occur_time,type,coin_add) values(%d,'%s',4,%d)"%(tabletime,int(m.group(2)),str(m.group(1)),int(m.group(3)))) a_file.close() db_conn.commit() db_cursor.close() db_conn.close()main()#if __name__ == "__main__":# main()==========================================================================================================================================================cat vipuserflowermonthly.py#!/usr/bin/env python#-*-coding:utf-8-*-#会员数据统计import MySQLdbimport os, sys, re, stringimport time, getoptoptmap = { 'dbuser' : 'haoren', 'dbpass' : 'hjkdhskjsh', 'dbhost' : '172.16.1.8', 'dbport' : 3306, 'dbname' : 'AMDB', #'logdir' : '/home/haoren/log/', #内网环境日志目录 'logdir' : '/home/haoren/logdir/', #外网环境日志目录 #'logpattern' : '^chat_flowerserver.log.', #内网环境日志名称前缀 'logpattern' : '^flowerserver.log.' #外网环境日志名称前缀 }def get_files(dir, pattern): print dir, pattern match_file_list = [] if os.path.exists(dir): cur_file_list = os.listdir(dir) for file_name in cur_file_list: if re.search(pattern, file_name): match_file_list.append(file_name) return match_file_list else: return 'no'def main(): one_day = time.strftime("%Y%m%d", time.localtime(time.time() - 24*60*60)) #默认日期为脚本运行的上一天 opts, args = getopt.getopt(sys.argv[1:], 'd:') for op, value in opts: if op == '-d': m = re.search('[0-9]{8}', value) if m: one_day = value else: print "请输入8位日期(比如:20130215)" return 'no' print "正在读取VIP用户食用油数据(%s)..." %one_day db_conn = MySQLdb.connect(user=optmap['dbuser'], passwd=optmap['dbpass'], host=optmap['dbhost'], port=optmap['dbport'], db=optmap['dbname']) db_cursor = db_conn.cursor() temp_vip_user_flower_file_name = '/tmp/vipuserflowermonthlytemp.txt' command = "cat /dev/null > %s" %(temp_vip_user_flower_file_name) os.system(command) if re.search('haoren', optmap['logdir']): print '外网环境' log_dir_name_list = get_files(optmap['logdir'], one_day[2:]) for log_dir_name_item in log_dir_name_list: log_dir_full_path = optmap['logdir']+log_dir_name_item+'/' log_file_name_list = get_files(log_dir_full_path, optmap['logpattern'] + one_day[2:]) for log_file_name_item in log_file_name_list: print log_file_name_item command = "cat %s%s |awk '/\[flower统计\]/' >> %s" % (log_dir_full_path, log_file_name_item, temp_vip_user_flower_file_name) os.system(command) else: print '内网环境' log_file_name_list = get_files(optmap['logdir'], optmap['logpattern'] + one_day[2:]) for log_file_name_item in log_file_name_list: command = "cat %s%s |awk '/\[flower统计\]/' >> %s" % (optmap['logdir'], log_file_name_item, temp_vip_user_flower_file_name) os.system(command) command = "cat %s |wc -l" %temp_vip_user_flower_file_name os.system(command) today_flower_new_num_map = {} today_flower_new_num_map[0] = 0 today_flower_new_num_map[1] = 0 today_flower_new_num_map[2] = 0 today_flower_new_num_map[3] = 0 today_flower_new_num_map[11] = 0 today_flower_new_num_map[12] = 0 today_flower_new_num_map[13] = 0 today_flower_spent_num_map = {} today_flower_spent_num_map[0] = 0 today_flower_spent_num_map[1] = 0 today_flower_spent_num_map[2] = 0 today_flower_spent_num_map[3] = 0 today_flower_spent_num_map[11] = 0 today_flower_spent_num_map[12] = 0 today_flower_spent_num_map[13] = 0 temp_vip_user_flower_file = open(temp_vip_user_flower_file_name) for one_line in temp_vip_user_flower_file.readlines():#新增食用油 match = re.search("^(\S+) FlowerServer\[\d+\] INFO: \[flower统计\]\[basketflower\]用户\((\d+),(\d+)\)累积食用油: basketflower=(\d+)", one_line) if match: today_flower_new_num_map[string.atoi(match.group(2))] += 1 continue match = re.search("^(\S+) FlowerServer\[\d+\] INFO: \[flower统计\]\[basketflower\]\[vip\]用户\((\d+),(\d+)\)花篮信息更新导致填满花篮: (\d+)->(\d+)", one_line) if match: today_flower_new_num_map[string.atoi(match.group(2))] += string.atoi(match.group(5)) - string.atoi(match.group(4)) continue match = re.search("^(\S+) FlowerServer\[\d+\] INFO: \[flower统计\]\[flowerload\]增加用户\((\d+),(\d+)\)的花篮食用油:实际增加(\d+)朵", one_line) if match: today_flower_new_num_map[string.atoi(match.group(2))] += string.atoi(match.group(4)) continue match = re.search("^(\S+) FlowerServer\[\d+\] INFO: \[flower统计\]\[present\]用户\((\d+),(\d+)\)赠送(\d+)朵食用油(\S+)给(\d+)失败...\(实际补偿(\d+)朵,补偿后共(\d+)朵\)", one_line) if match: today_flower_new_num_map[string.atoi(match.group(2))] += string.atoi(match.group(7)) continue match = re.search("^(\S+) FlowerServer\[\d+\] INFO: \[flower统计\]\[gm\]增加用户\((\d+),(\d+)\)花篮(\d+)朵食用油: basketflower=(\d+)", one_line) if match: today_flower_new_num_map[string.atoi(match.group(2))] += string.atoi(match.group(4)) continue#消耗食用油 match = re.search("^(\S+) FlowerServer\[\d+\] INFO: \[flower统计\]\[present\]用户\((\d+),(\d+)\)赠送(\d+)朵花给(\d+)(\S+).\(剩余basketflower=(\d+)\)", one_line) if match: today_flower_spent_num_map[string.atoi(match.group(2))] += string.atoi(match.group(4)) continue match = re.search("^(\S+) FlowerServer\[\d+\] INFO: \[flower统计\]\[basketflower\]\[vip\]用户\((\d+),(\d+)\)花篮信息更新导致花篮食用油下调为花篮上限: (\d+)->(\d+)", one_line) if match: today_flower_spent_num_map[string.atoi(match.group(2))] += string.atoi(match.group(4)) - string.atoi(match.group(5)) continue match = re.search("^(\S+) FlowerServer\[\d+\] INFO: \[flower统计\]\[gm\]减少用户\((\d+),(\d+)\)花篮(\d+)朵食用油: basketflower=(\d+)", one_line) if match: today_flower_spent_num_map[string.atoi(match.group(2))] += string.atoi(match.group(4)) continue temp_vip_user_flower_file.close() dword_time = time.mktime(time.strptime(one_day, '%Y%m%d')) db_conn.query("use %s" %optmap['dbname']) sql = "delete from VIPUSERFLOWERMONTHLY where count_time='%d'" %dword_time print sql db_conn.query(sql) sql = "insert into VIPUSERFLOWERMONTHLY (count_time) values('%d')" %(dword_time) print sql db_conn.query(sql) sql = "update VIPUSERFLOWERMONTHLY set today_year_flower_new_num=%d, today_month_flower_new_num=%d, today_week_flower_new_num=%d, today_other_flower_new_num=%d, today_year_flower_spent_num=%d, today_month_flower_spent_num=%d, today_week_flower_spent_num=%d, today_other_flower_spent_num=%d where count_time='%d'" %(today_flower_new_num_map[3], today_flower_new_num_map[2], today_flower_new_num_map[1], today_flower_new_num_map[0] + today_flower_new_num_map[11] + today_flower_new_num_map[12] + today_flower_new_num_map[13], today_flower_spent_num_map[3], today_flower_spent_num_map[2], today_flower_spent_num_map[1], today_flower_spent_num_map[0] + today_flower_spent_num_map[11] + today_flower_spent_num_map[12] + today_flower_spent_num_map[13], dword_time) print sql db_conn.query(sql) db_conn.commit() db_cursor.close() db_conn.close()main()#if __name__ == "__main__"# main()===================================================================================================cat UserGoldConsumitemDaily.py#!/usr/bin/env python#-*-coding:utf-8-*-#用户消费物品日报#****************************************使用说明****************************************************# 内网测试通过命令行参数# -d 指定统计时间# -p 指定日志所在目录# -t 指定临时文件存放目录# 示例如下:# [haoren@localhost tongji]$ ./userconsumitemDaily.py -d 20141112 -p /home/haoren/log/ -t /home/haoren/tongji/# 外网无需加参数,使用默认值#****************************************使用说明****************************************************import MySQLdbimport os, sys, re,stringimport time, tarfile,getoptoptmap = { 'dbuser' : 'haoren', 'dbpass' : 'hjkdhskjsh', 'dbhost' : '172.16.1.8', 'dbport' : 3306, 'dbname' : 'AMDB' }def get_files(dir, pattern): res_file_list =[] if os.path.exists(dir): cur_file_list = os.listdir(dir) for file_name in cur_file_list: if re.search(pattern, file_name): res_file_list.append(file_name) return res_file_list else: return 'no'def main(): #外网环境默认参数 cur_day = time.strftime("%Y%m%d", time.localtime(time.time()-86400)) log_day = time.strftime('%y%m%d', time.localtime(time.mktime(time.strptime(cur_day, '%Y%m%d')))) logdirname = "/home/haoren/logdir/%s_67"%log_day tmpdirname = "/tmp/" logname = "billserver" #内网测试指定参数 opts, args = getopt.getopt(sys.argv[1:], 'd:p:t:') for op, value in opts: if op == '-d': m = re.search('[0-9]{8}', value) if m: cur_day = value log_suffix = time.strftime('%y%m%d', time.localtime(time.mktime(time.strptime(cur_day, '%Y%m%d')))) logname = "billserver.log" log_day = time.strftime('%y%m%d', time.localtime(time.mktime(time.strptime(cur_day, '%Y%m%d')))) logdirname = "/home/haoren/logdir/%s_67"%log_day else: print "请输入8位日期(比如:20130215)" return 'no' elif op == '-p': logdirname = str(value) elif op == '-t': tmpdirname = str(value) fmt_day = time.strftime('%Y-%m-%d', time.localtime(time.mktime(time.strptime(cur_day, '%Y%m%d')))) d_time = time.mktime(time.strptime(fmt_day,'%Y-%m-%d')) print '统计日期:',fmt_day print '日志名称:',logname print '日志路径:',logdirname print '临时文本:',tmpdirname #用户物品消费 if os.path.exists("%suserconsumeitem.txt"%tmpdirname): os.system("rm -f %suserconsumeitem.txt"%tmpdirname) file_list2=get_files(logdirname,logname) for file2 in file_list2: command = "cat %s/%s | awk '/物品统计/'>> %suserconsumeitem.txt"%(logdirname,file2,tmpdirname) os.system(command) #用户物品消费 filename='%suserconsumeitem.txt'%tmpdirname a_file = open(filename, 'r') #用户购买物品消耗总人民币 allcoin={} #用户消费物品 userprops = {} #130815-15:01:06 Bill[990] INFO: [物品统计]频道(210043)等级(9)用户(21000162)赠送(1)个物品(3)给客户(21000264),客户等级(1),签约(0), 消耗人民币(10), 客户获得人民币(470), 频道获得人民币(30) for a_line in a_file.readlines(): m = re.search("^(\S+) Bill\[\d+\] INFO: \[人民币消费物品统计\]频道\((\d+)\)等级\((\d+)\)用户\((\d+)\)赠送\((\d+)\)个物品\((\d+)\)给客户\((\d+)\),客户等级\((\d+)\),签约\((\d+)\), 消耗人民币\((\d+)\), 客户获得人民币\((\d+)\), 频道获得人民币\((\d+)\)", a_line) if m: userid = int(m.group(4)) propnum = int(m.group(5)) propid = int(m.group(6)) coin = int(m.group(10)) if (userid in userprops): if (propid in userprops[userid]): userprops[userid][propid]['num'] += propnum userprops[userid][propid]['coin'] += coin else: userprops[userid][propid] = {} userprops[userid][propid]['num'] = propnum userprops[userid][propid]['coin'] = coin else: userprops[userid]={} userprops[userid][propid] = {} userprops[userid][propid]['num'] = propnum userprops[userid][propid]['coin'] = coin for key in userprops: print '用户:',key for prop in userprops[key]: print '物品id:', prop print '物品num:', userprops[key][prop]['num'] print '物品消费人民币:', userprops[key][prop]['coin'] a_file.close() db_conn = MySQLdb.connect(user=optmap['dbuser'], passwd=optmap['dbpass'], host=optmap['dbhost'], port=optmap['dbport'], db=optmap['dbname']) db_cursor=db_conn.cursor() db_conn.query("use %s"%optmap['dbname']) tabletime = time.strftime("%Y%m%d", time.localtime(time.mktime(time.strptime(cur_day, "%Y%m%d")))) sql="CREATE TABLE IF NOT EXISTS `USERGOLDCONSUMEITEMDAILY_%s` like USERGOLDCONSUMEITEMDAILY_SAMPLE"%tabletime db_conn.query(sql) db_conn.query("delete from USERGOLDCONSUMEITEMDAILY_%s"%tabletime) for key in userprops: for kitem in userprops[key]: db_conn.query("insert into USERGOLDCONSUMEITEMDAILY_%s(USERID,CONSUMEITEMTOTALGOLD,ITEMID,ITEMNUM) values(%d,%d,%d,%d)"%(tabletime,int(key),int(userprops[key][kitem]['coin']),int(kitem),int(userprops[key][kitem]['num']))) db_conn.commit() db_cursor.close() db_conn.close()main()#if __name__ == "__main__":# main()====================================================================================================cat familyConsumeDaily.py#!/usr/bin/env python#-*-coding:utf-8-*-#家族消费,集结统计脚本(修改为外网配置后,每天定时执行,统计数据)import MySQLdbimport os, sys, re,stringimport time, tarfile,getoptoptmap = { 'dbuser' : 'haoren', 'dbpass' : 'hjkdhskjsh', 'dbhost' : '172.16.1.8', 'dbport' : 3306, 'dbname' : 'AMDB' } def get_files(dir, pattern): res_file_list =[] if os.path.exists(dir): cur_file_list = os.listdir(dir) cur_file_list.sort() for file_name in cur_file_list: if re.search(pattern, file_name): res_file_list.append(file_name) return res_file_list else: return 'no'def main(): cur_day = time.strftime("%Y%m%d", time.localtime(time.time()-86400)) # cur_day = time.strftime("%Y%m%d", time.localtime(time.time())) opts, args = getopt.getopt(sys.argv[1:], 'd:') for op, value in opts: if op == '-d': m = re.search('[0-9]{8}', value) if m: cur_day = value else: print "请输入8位日期(比如:20130215)" return 'no' fmt_day = time.strftime('%Y-%m-%d', time.localtime(time.mktime(time.strptime(cur_day, '%Y%m%d')))) print '统计日期:',fmt_day log_day = time.strftime('%y%m%d', time.localtime(time.mktime(time.strptime(cur_day, '%Y%m%d')))) dirname="/home/haoren/logdir/%s_124/"%log_day print dirname dirname1="/home/haoren/logdir/%s_26/"%log_day print dirname1 # 删除存在的临时文件 if os.path.exists("/tmp/familyConsumeDaily.txt"): os.system("rm -f /tmp/familyConsumeDaily.txt") if os.path.exists("/tmp/familyGatherDaily.txt"): os.system("rm -f /tmp/familyGatherDaily.txt") file_list=get_files(dirname,'groupserver') for file in file_list: command = "cat %s/%s | grep 家族集结|grep 发起家族|grep 集结成功>> /tmp/familyGatherDaily.txt"%(dirname,file) os.system(command) command = "cat %s/%s | grep 家族消费|grep 同步家族|grep 成员|grep 消费>> /tmp/familyConsumeDaily.txt"%(dirname,file) os.system(command) file_list1=get_files(dirname1,'groupserver') for file1 in file_list1: command = "cat %s/%s | grep 家族集结|grep 发起家族|grep 集结成功>> /tmp/familyGatherDaily.txt"%(dirname1,file1) os.system(command) command = "cat %s/%s | grep 家族消费|grep 同步家族|grep 成员|grep 消费>> /tmp/familyConsumeDaily.txt"%(dirname1,file1) os.system(command) #家族消费 filename='/tmp/familyConsumeDaily.txt' a_file = open(filename, 'r') #家族消费统计 familyConsume={} #150215-22:47:18 GROUPSERVER[4401] INFO: [家族消费]同步家族(200105)成员(21001367)消费(2000) for a_line in a_file.readlines(): m = re.search("^(\S+) GROUPSERVER\[\d+\] INFO: \[家族消费\]同步家族\((\d+)\)成员\((\d+)\)消费\((\d+)\)", a_line) if(m and int(m.group(2))!=0): # print m.group(2), m.group(4) if(familyConsume.has_key(int(m.group(2)))): familyConsume[int(m.group(2))] += int(m.group(4)) else: familyConsume[int(m.group(2))] = int(m.group(4)) a_file.close() #家族集结 filename='/tmp/familyGatherDaily.txt' a_file = open(filename, 'r') #家族集结统计 familyGather={} for a_line in a_file.readlines(): #150117-23:29:23 GROUPSERVER[4400] INFO: [家族集结]用户(%u)发起家族(%u)集结成功,免费 #150117-23:29:23 GROUPSERVER[4400] INFO: [家族集结]用户(%u)发起家族(%u)集结成功,扣费成功 m = re.search("^(\S+) GROUPSERVER\[\d+\] INFO: \[家族集结\]用户\((\d+)\)发起家族\((\d+)\)集结成功", a_line) if(m): # print m.group(2), m.group(3) if(familyGather.has_key(int(m.group(3)))): familyGather[int(m.group(3))] += 1 else: familyGather[int(m.group(3))] = 1 a_file.close() db_conn = MySQLdb.connect(user=optmap['dbuser'], passwd=optmap['dbpass'], host=optmap['dbhost'], port=optmap['dbport'], db=optmap['dbname']) db_cursor=db_conn.cursor() db_conn.query("use %s"%optmap['dbname']) timeday = int(time.mktime(time.strptime(cur_day, "%Y%m%d"))) for key in familyConsume: # print key, familyConsume[key] db_conn.query("insert into FAMILY_DAILYCONSUMESTAT(FAMILYID, CONSUME, TIME) values(%d, %d, %d)"%(int(key), int(familyConsume[key]), int(string.atoi(cur_day, 10)))) for key in familyGather: # print key, familyGather[key] db_conn.query("insert into FAMILY_DAILYGATHERTIMES(FAMILYID, GATHERTIMES, TIME) values(%d, %d, %d)"%(int(key), int(familyGather[key]), int(string.atoi(cur_day, 10)))) db_conn.commit() db_cursor.close() db_conn.close()main()========================================================================================================= cat platformincomemonthly.py#!/usr/bin/env python#-*-coding:utf-8-*-#平台收入月报#****************************************使用说明****************************************************# 内网测试通过命令行参数# -d 指定统计时间# -p 指定日志所在目录# -t 指定临时文件存放目录# 示例如下:# [haoren@localhost tongji]$ ./newplatformincomemonthly.py -d 20141112 -p /home/haoren/log/ -t /home/haoren/tongji/# 外网无需加参数,使用默认值#****************************************使用说明****************************************************import MySQLdbimport os, sys, re,stringimport time, tarfile,getoptoptmap = { 'dbuser' : 'haoren', 'dbpass' : 'hjkdhskjsh', 'dbhost' : '172.16.1.8', 'dbport' : 3306, 'dbname' : 'AMDB' }def get_files(dir, pattern): res_file_list =[] if os.path.exists(dir): cur_file_list = os.listdir(dir) for file_name in cur_file_list: if re.search(pattern, file_name): res_file_list.append(file_name) return res_file_list else: return 'no'def main(): #外网环境默认参数 cur_day = time.strftime("%Y%m%d", time.localtime(time.time()-86400)) log_day = time.strftime('%y%m%d', time.localtime(time.mktime(time.strptime(cur_day, '%Y%m%d')))) logdirname = "/home/haoren/logdir/%s_67"%log_day tmpdirname = "/tmp/" logname = "billserver" #内网测试指定参数 opts, args = getopt.getopt(sys.argv[1:], 'd:p:t:') for op, value in opts: if op == '-d': m = re.search('[0-9]{8}', value) if m: cur_day = value log_suffix = time.strftime('%y%m%d', time.localtime(time.mktime(time.strptime(cur_day, '%Y%m%d')))) logname = "billserver.log.%s"%log_suffix log_day = time.strftime('%y%m%d', time.localtime(time.mktime(time.strptime(cur_day, '%Y%m%d')))) logdirname = "/home/haoren/logdir/%s_67"%log_day else: print "请输入8位日期(比如:20130215)" return 'no' elif op == '-p': logdirname = str(value) elif op == '-t': tmpdirname = str(value) fmt_day = time.strftime('%Y-%m-%d', time.localtime(time.mktime(time.strptime(cur_day, '%Y%m%d')))) d_time = time.mktime(time.strptime(fmt_day,'%Y-%m-%d')) print '统计日期:',fmt_day print '日志名称:',logname print '日志路径:',logdirname print '临时文本:',tmpdirname #客户收入 if os.path.exists("%splatformincome.txt"%tmpdirname): os.system("rm -f %splatformincome.txt"%tmpdirname) #提现统计 if os.path.exists("%swithdraw.txt"%tmpdirname): os.system("rm -f %swithdraw.txt"%tmpdirname) file_list2=get_files(logdirname, logname) for file2 in file_list2: command = "cat %s/%s | awk '/物品统计/'>> %splatformincome.txt"%(logdirname,file2,tmpdirname) os.system(command) command = "cat %s/%s | awk '/提现统计/'>> %swithdraw.txt"%(logdirname,file2,tmpdirname) os.system(command) #客户收入 filename='%splatformincome.txt'%tmpdirname a_file = open(filename, 'r') #客户消耗用户总人民币 record={} #物品 props = {} #送物品人 people = {} #130815-15:01:06 Bill[990] INFO: [物品统计]频道(210043)等级(9)用户(21000162)赠送(1)个物品(3)给客户(21000264),客户等级(1),签约(0), 消耗人民币(10), 客户获得人民币(470), 频道获得人民币(30) for a_line in a_file.readlines(): m = re.search("^(\S+) Bill\[\d+\] INFO: \[物品统计\]频道\((\d+)\)等级\((\d+)\)用户\((\d+)\)赠送\((\d+)\)个物品\((\d+)\)给客户\((\d+)\),客户等级\((\d+)\),签约\((\d+)\), 消耗人民币\((\d+)\), 客户获得人民币\((\d+)\), 频道获得金币\((\d+)\)", a_line) if m: userid = int(m.group(4)) propnum = int(m.group(5)) propid = int(m.group(6)) coin = int(m.group(10)) singergold = int(m.group(11)) channelgold = int(m.group(12)) if (propid in props): props[propid]['allcoin'] += coin #总人民币 props[propid]['allgold'] = props[propid]['allgold'] + singergold + channelgold #总人民币 props[propid]['num'] += propnum else: props[propid] = {} props[propid]['allcoin'] = coin props[propid]['allgold'] = singergold + channelgold props[propid]['num'] = propnum if(userid not in people): #送物品人数 people[userid] = 1 #当日消耗人民币 totalgold = 0 for key in props: totalgold += props[key]['allgold'] print '物品Id:', key print '物品收入:',props[key]['allcoin'] print '人民币产出:',props[key]['allgold'] print '物品数量:',props[key]['num'] print '赠送物品用户:',len(people) a_file.close() #提现统计 filename='%swithdraw.txt'%tmpdirname a_file = open(filename, 'r') #客户总人民币 record['withdraw'] = 0 record['cash'] = 0 #130816-17:22:14 Bill[990] INFO: [提现统计]用户(21001359)客户等级(4)签约频道(0)频道等级(0)提现(121)元消耗人民币(50), 剩余人民币(100) for a_line in a_file.readlines(): m = re.search("^(\S+) Bill\[\d+\] INFO: \[提现统计\]用户\((\d+)\)客户等级\((\d+)\)签约频道\((\d+)\)频道等级\((\d+)\)提现\((\d+)\)元消耗人民币\((\d+)\), 剩余人民币\((\d+)\)", a_line) if m: record['withdraw'] += int(m.group(7)) record['cash'] += int(m.group(6)) print'客户提现人民币:%u'%record['withdraw'] print'客户提现人民币:%u'%record['cash'] a_file.close() print '今日结余:%d'%(totalgold-record['withdraw']) db_conn = MySQLdb.connect(user=optmap['dbuser'], passwd=optmap['dbpass'], host=optmap['dbhost'], port=optmap['dbport'], db=optmap['dbname']) db_cursor=db_conn.cursor() db_conn.query("use %s"%optmap['dbname']) d_time = time.mktime(time.strptime(cur_day,'%Y%m%d')) last_day = d_time-86400 db_conn.query("delete from PLATFORMINCOMEMONTHLY where TIME=%u"%d_time) sql ="SELECT CURGOLD from PLATFORMINCOMEMONTHLY where TIME=%u limit 1"%last_day last_day_curgold = 0 db_cursor.execute(sql) temp=db_cursor.fetchone() if(temp != None): last_day_curgold = int(temp[0]) else: sql ="SELECT CURGOLD from ITEMEARNINGMONTHLY where TIME=%u"%last_day db_cursor.execute(sql) temp=db_cursor.fetchone() if(temp != None): last_day_curgold = int(temp[0]) print '昨天:%d 人民币结余:%d'%(last_day,last_day_curgold) record['curgold']= last_day_curgold + totalgold -record['withdraw'] print '平台累加人民币结余:',record['curgold'] for kitem in props: db_conn.query("insert into PLATFORMINCOMEMONTHLY(TIME,CONSUMEITEMTOTALCOIN,ADDGOLD,CURGOLD,SINGERCASH,SINGERCONSUMEGOLD,PRESENTUSERNUM,ITEMID,ITEMNUM) values(%d,%d,%d,%d,%d,%d,%d,%d,%d)"%(d_time, props[kitem]['allcoin'],props[kitem]['allgold'],record['curgold'],record['cash'],record['withdraw'],len(people),int(kitem), int(props[kitem]['num']))) db_conn.commit() db_cursor.close() db_conn.close()main()#if __name__ == "__main__":# main()============================================================================================================= cat channelincomeDaily.py#!/usr/bin/env python#-*-coding:utf-8-*-#频道收入日报#****************************************使用说明****************************************************# 内网测试通过命令行参数# -d 指定统计时间# -p 指定日志所在目录# -t 指定临时文件存放目录# 示例如下:# [haoren@localhost tongji]$ ./channelincomeDaily.py -d 20141112 -p /home/haoren/log/ -t /home/haoren/tongji/# 外网无需加参数,使用默认值#****************************************使用说明****************************************************import MySQLdbimport os, sys, re,stringimport time, tarfile,getoptoptmap = { 'dbuser' : 'haoren', 'dbpass' : 'hjkdhskjsh', 'dbhost' : '172.16.1.8', 'dbport' : 3306, 'dbname' : 'AMDB' } def get_files(dir, pattern): res_file_list =[] if os.path.exists(dir): cur_file_list = os.listdir(dir) cur_file_list.sort() for file_name in cur_file_list: if re.search(pattern, file_name): res_file_list.append(file_name) return res_file_list else: return 'no'def main(): #外网环境默认参数 cur_day = time.strftime("%Y%m%d", time.localtime(time.time()-86400)) log_day = time.strftime('%y%m%d', time.localtime(time.mktime(time.strptime(cur_day, '%Y%m%d')))) logdirname = "/home/haoren/logdir/%s_67"%log_day tmpdirname = "/tmp/" logname = "billserver" #内网测试指定参数 opts, args = getopt.getopt(sys.argv[1:], 'd:p:t:') for op, value in opts: if op == '-d': m = re.search('[0-9]{8}', value) if m: cur_day = value print cur_day log_suffix = time.strftime('%y%m%d', time.localtime(time.mktime(time.strptime(cur_day, '%Y%m%d')))) logname = "billserver.log" log_day = time.strftime('%y%m%d', time.localtime(time.mktime(time.strptime(cur_day, '%Y%m%d')))) logdirname = "/home/haoren/logdir/%s_67"%log_day else: print "请输入8位日期(比如:20130215)" return 'no' elif op == '-p': logdirname = str(value) elif op == '-t': tmpdirname = str(value) fmt_day = time.strftime('%Y-%m-%d', time.localtime(time.mktime(time.strptime(cur_day, '%Y%m%d')))) d_time = time.mktime(time.strptime(fmt_day,'%Y-%m-%d')) print '统计日期:',fmt_day print '日志名称:',logname print '日志路径:',logdirname print '临时文本:',tmpdirname #频道收入 if os.path.exists("%schannelincome.txt"%tmpdirname): os.system("rm -f %schannelincome.txt"%tmpdirname) #频道人民币总量 if os.path.exists("%schannelallgold.txt"%tmpdirname): os.system("rm -f %schannelallgold.txt"%tmpdirname) file_list=get_files(logdirname, logname) for file in file_list: command = "cat %s/%s | awk '/物品统计/'>> %schannelincome.txt"%(logdirname,file,tmpdirname) os.system(command) command = "cat %s/%s | awk '/频道人民币统计/'>> %schannelallgold.txt"%(logdirname,file,tmpdirname) os.system(command) #频道收入 filename='%schannelincome.txt'%tmpdirname a_file = open(filename, 'r') #频道消耗用户总人民币 allcoin={} #频道人民币收入 goldincome={} #频道总人民币 channelallgold = {} #频道物品 channelprops = {} #130815-15:01:06 Bill[990] INFO: [物品统计]频道(210043)等级(9)用户(21000162)赠送(1)个物品(3)给客户(21000264),客户等级(1),签约(0), 消耗人民币(10), 客户获得人民币(470), 频道获得人民币(30) for a_line in a_file.readlines(): m = re.search("^(\S+) Bill\[\d+\] INFO: \[物品统计\]频道\((\d+)\)等级\((\d+)\)用户\((\d+)\)赠送\((\d+)\)个物品\((\d+)\)给客户\((\d+)\),客户等级\((\d+)\),签约\((\d+)\), 消耗人民币\((\d+)\), 客户获得人民币\((\d+)\), 频道获得金币\((\d+)\)", a_line) if m: channelid = int(m.group(2)) propnum = int(m.group(5)) propid = int(m.group(6)) signflag = int(m.group(9)) coin = int(m.group(10)) gold = int(m.group(12)) #离线用户signflag传入的是频道号 if (signflag != 0 and signflag != 1): if (signflag == int(m.group(2))): signflag = 1 else: signflag = 0 if (channelid in channelprops): if (propid in channelprops[channelid]): channelprops[channelid][propid] += propnum allcoin[channelid][propid] += coin goldincome[channelid][propid][signflag] += gold else: channelprops[channelid][propid] = propnum allcoin[channelid][propid] = coin goldincome[channelid][propid] = {} goldincome[channelid][propid][0] = 0 goldincome[channelid][propid][1] = 0 goldincome[channelid][propid][signflag] = gold else: channelprops[channelid]={} channelprops[channelid][propid] = propnum allcoin[channelid] = {} allcoin[channelid][propid] = coin goldincome[channelid]={} goldincome[channelid][propid]={} goldincome[channelid][propid][0]=0 goldincome[channelid][propid][1]=0 goldincome[channelid][propid][signflag]=int(m.group(12)) for key in channelprops: print '频道:',key a_file.close() #频道总人民币统计 #频道总人民币 filename='%schannelallgold.txt'%tmpdirname a_file = open(filename, 'r') #频道总人民币 allgold={} #130816-15:28:45 Bill[990] INFO: [频道人民币统计]频道(210043)当前人民币(308864653) for a_line in a_file.readlines(): m = re.search("^(\S+) Bill\[\d+\] INFO: \[频道人民币统计\]频道\((\d+)\)当前人民币\((\d+)\)", a_line) if m: allgold[int(m.group(2))] = int(m.group(3)) for key in allgold: print'频道:%lu,人民币总量:%u'%(key,allgold[key]) a_file.close() db_conn = MySQLdb.connect(user=optmap['dbuser'], passwd=optmap['dbpass'], host=optmap['dbhost'], port=optmap['dbport'], db=optmap['dbname']) db_cursor=db_conn.cursor() db_conn.query("use %s"%optmap['dbname']) tabletime = time.strftime("%Y%m%d", time.localtime(time.mktime(time.strptime(cur_day, "%Y%m%d")))) sql="CREATE TABLE IF NOT EXISTS `CHANNELEARNINGDAILY_%s` like CHANNELEARNINGDAILY_SAMPLE"%tabletime db_conn.query(sql) db_conn.query("delete from CHANNELEARNINGDAILY_%s"%tabletime) for key in channelprops: if(key not in allgold): allgold[key]=0 for kitem in channelprops[key]: db_conn.query("insert into CHANNELEARNINGDAILY_%s(CHANNELID,RECVITEMTOTALCOIN,SIGNGOLD,NONSIGNGOLD,CURGOLD,ITEMID,ITEMNUM ) values(%d,%d,%d,%d,%d,%d,%d)"%(tabletime,int(key),int(allcoin[key][kitem]),int(goldincome[key][kitem][1]),int(goldincome[key][kitem][0]),int(allgold[key]),int(kitem),int(channelprops[key][kitem]))) db_conn.commit() db_cursor.close() db_conn.close()main()#if __name__ == "__main__":# main()=====================================================================================================================cat userconsumitemDaily.py#!/usr/bin/env python#-*-coding:utf-8-*-#用户消费物品日报#****************************************使用说明****************************************************# 内网测试通过命令行参数# -d 指定统计时间# -p 指定日志所在目录# -t 指定临时文件存放目录# 示例如下:# [haoren@localhost tongji]$ ./userconsumitemDaily.py -d 20141112 -p /home/haoren/log/ -t /home/haoren/tongji/# 外网无需加参数,使用默认值#****************************************使用说明****************************************************import MySQLdbimport os, sys, re,stringimport time, tarfile,getoptoptmap = { 'dbuser' : 'haoren', 'dbpass' : 'hjkdhskjsh', 'dbhost' : '172.16.1.8', 'dbport' : 3306, 'dbname' : 'AMDB' }def get_files(dir, pattern): res_file_list =[] if os.path.exists(dir): cur_file_list = os.listdir(dir) for file_name in cur_file_list: if re.search(pattern, file_name): res_file_list.append(file_name) return res_file_list else: return 'no'def main(): #外网环境默认参数 cur_day = time.strftime("%Y%m%d", time.localtime(time.time()-86400)) log_day = time.strftime('%y%m%d', time.localtime(time.mktime(time.strptime(cur_day, '%Y%m%d')))) logdirname = "/home/haoren/logdir/%s_67"%log_day tmpdirname = "/tmp/" logname = "billserver" #内网测试指定参数 opts, args = getopt.getopt(sys.argv[1:], 'd:p:t:') for op, value in opts: if op == '-d': m = re.search('[0-9]{8}', value) if m: cur_day = value log_suffix = time.strftime('%y%m%d', time.localtime(time.mktime(time.strptime(cur_day, '%Y%m%d')))) logname = "billserver.log" log_day = time.strftime('%y%m%d', time.localtime(time.mktime(time.strptime(cur_day, '%Y%m%d')))) logdirname = "/home/haoren/logdir/%s_67"%log_day else: print "请输入8位日期(比如:20130215)" return 'no' elif op == '-p': logdirname = str(value) elif op == '-t': tmpdirname = str(value) fmt_day = time.strftime('%Y-%m-%d', time.localtime(time.mktime(time.strptime(cur_day, '%Y%m%d')))) d_time = time.mktime(time.strptime(fmt_day,'%Y-%m-%d')) print '统计日期:',fmt_day print '日志名称:',logname print '日志路径:',logdirname print '临时文本:',tmpdirname #用户物品消费 if os.path.exists("%suserconsumeitem.txt"%tmpdirname): os.system("rm -f %suserconsumeitem.txt"%tmpdirname) file_list2=get_files(logdirname,logname) for file2 in file_list2: command = "cat %s/%s | awk '/物品统计/'>> %suserconsumeitem.txt"%(logdirname,file2,tmpdirname) os.system(command) #用户物品消费 filename='%suserconsumeitem.txt'%tmpdirname a_file = open(filename, 'r') #用户购买物品消耗总人民币 allcoin={} #用户消费物品 userprops = {} #130815-15:01:06 Bill[990] INFO: [物品统计]频道(210043)等级(9)用户(21000162)赠送(1)个物品(3)给客户(21000264),客户等级(1),签约(0), 消耗人民币(10), 客户获得人民币(470), 频道获得人民币(30) for a_line in a_file.readlines(): m = re.search("^(\S+) Bill\[\d+\] INFO: \[物品统计\]频道\((\d+)\)等级\((\d+)\)用户\((\d+)\)赠送\((\d+)\)个物品\((\d+)\)给客户\((\d+)\),客户等级\((\d+)\),签约\((\d+)\), 消耗人民币\((\d+)\), 客户获得人民币\((\d+)\), 频道获得金币\((\d+)\)", a_line) if m: userid = int(m.group(4)) propnum = int(m.group(5)) propid = int(m.group(6)) coin = int(m.group(10)) if (userid in userprops): if (propid in userprops[userid]): userprops[userid][propid]['num'] += propnum userprops[userid][propid]['coin'] += coin else: userprops[userid][propid] = {} userprops[userid][propid]['num'] = propnum userprops[userid][propid]['coin'] = coin else: userprops[userid]={} userprops[userid][propid] = {} userprops[userid][propid]['num'] = propnum userprops[userid][propid]['coin'] = coin for key in userprops: print '用户:',key for prop in userprops[key]: print '物品id:', prop print '物品num:', userprops[key][prop]['num'] print '物品消费人民币:', userprops[key][prop]['coin'] a_file.close() db_conn = MySQLdb.connect(user=optmap['dbuser'], passwd=optmap['dbpass'], host=optmap['dbhost'], port=optmap['dbport'], db=optmap['dbname']) db_cursor=db_conn.cursor() db_conn.query("use %s"%optmap['dbname']) tabletime = time.strftime("%Y%m%d", time.localtime(time.mktime(time.strptime(cur_day, "%Y%m%d")))) sql="CREATE TABLE IF NOT EXISTS `USERCONSUMEITEMDAILY_%s` like USERCONSUMEITEMDAILY_SAMPLE"%tabletime db_conn.query(sql) db_conn.query("delete from USERCONSUMEITEMDAILY_%s"%tabletime) for key in userprops: for kitem in userprops[key]: db_conn.query("insert into USERCONSUMEITEMDAILY_%s(USERID,CONSUMEITEMTOTALCOIN,ITEMID,ITEMNUM) values(%d,%d,%d,%d)"%(tabletime,int(key),int(userprops[key][kitem]['coin']),int(kitem),int(userprops[key][kitem]['num']))) db_conn.commit() db_cursor.close() db_conn.close()main()#if __name__ == "__main__":# main()==============================================================================================================================cat checkchannelsettle.py#!/usr/bin/env python#-*-coding:utf-8-*-#check channel settle data VS performer settle data#****************************************使用说明****************************************************# 内网测试通过命令行参数# -d 指定统计时间# -p 指定日志所在目录# -t 指定临时文件存放目录# 示例如下:# [haoren@localhost tongji]$ ./channelsettlement.py -b 20141225 -e 20150125# 外网无需加参数,使用默认值#****************************************使用说明****************************************************import MySQLdbimport os, sys, re,stringimport time, tarfile,getoptimport datetimeoptmap = { 'dbuser' : 'haoren', 'dbpass' : 'hjkdhskjsh', 'dbhost_im' : '172.16.1.8', 'dbhost_gm' : '172.16.1.8', 'dbport' : 3306, 'dbname_im' : 'FMDB', 'dbname_gm' : 'AMDB' } def get_files(dir, pattern): res_file_list =[] if os.path.exists(dir): cur_file_list = os.listdir(dir) cur_file_list.sort() for file_name in cur_file_list: if re.search(pattern, file_name): res_file_list.append(file_name) return res_file_list else: return 'no'def main(): #获取频道结算起始时间,每月25日结算 cur = datetime.datetime.now() cur_day = cur.day cur_month = cur.month cur_year = cur.year print '*****当前时间: %d-%d-%d'%(cur_year, cur_month, cur_day) begin_day = cur_day begin_month = cur_month begin_year = cur_year if cur_month == 1: begin_month = 12 begin_year = cur_year - 1 else: begin_month = cur_month - 1 beginTime = datetime.datetime(begin_year,begin_month,begin_day,0,0,0) endTime = datetime.datetime(cur_year,cur_month,cur_day,0,0,0) #内网测试指定参数 opts, args = getopt.getopt(sys.argv[1:], 'b:e:') for op, value in opts: if op == '-b': m = re.search('[0-9]{8}', value) if m: beginTime = datetime.datetime.strptime(value, '%Y%m%d') elif op == '-e': m = re.search('[0-9]{8}', value) if m: endTime = datetime.datetime.strptime(value, '%Y%m%d') beginTimeStamp = int(time.mktime(beginTime.timetuple())) endTimeStamp = int(time.mktime(endTime.timetuple())) print '*****结算开始时间 %s (%d)'%(beginTime,beginTimeStamp) print '*****结算结束时间 %s (%d)'%(endTime,endTimeStamp) #获取公司化频道列表 db_conn = MySQLdb.connect(user=optmap['dbuser'], passwd=optmap['dbpass'], host=optmap['dbhost_im'], port=optmap['dbport'], db=optmap['dbname_im']) db_cursor=db_conn.cursor() db_conn.query("use %s"%optmap['dbname_im']) sql = "select `CHANNELID` from `SPECIALCHANNEL`" channellist = {} try: db_cursor.execute(sql) db_rows = db_cursor.fetchall() for row in db_rows: channellist[row[0]] = 0 except: print '查询失败:%s'%sql #获得各个频道的流水,以人民币结算 for channelid in channellist: channellist[channelid] = {} channellist[channelid]['coin'] = 0 channellist[channelid]['rev'] = 0 sql = "select SUM(CONSUMECOIN), SUM(CHANNELRECVGOLD) from ITEMCONSUMERECORD where CHANNELID=%d and OPTIME>=%d and OPTIME try: db_cursor.execute(sql) consumecoin1 = db_cursor.fetchone() if (consumecoin1 != None): channellist[channelid]['coin'] += consumecoin1[0] channellist[channelid]['rev'] += consumecoin1[1] else: print '没有符合条件的数据:%s'%sql except: print '查询失败:%s'%sql sql = "select SUM(CONSUMECOIN), SUM(CHANNELRECVGOLD) from REQSONGCONSUMERECORD where CHANNELID=%d and OPTIME>=%d and OPTIME try: db_cursor.execute(sql) consumecoin2 = db_cursor.fetchone() if (consumecoin2 != None): channellist[channelid]['coin'] += consumecoin2[0] channellist[channelid]['rev'] += consumecoin2[1] else: print '没有符合条件的数据:%s'%sql except: print '查询失败:%s'%sql print '[频道(%d)流水(%d)收入(%d)]'%(channelid,channellist[channelid]['coin'],channellist[channelid]['rev']) singerlist = {} for channelid in channellist: sql = "select PERFORMERID from VCHANNELPERFORMER where CHANNELID=%u"%channelid try: db_cursor.execute(sql) result = db_cursor.fetchall() for singer in result: singerlist[singer[0]] = {} singerlist[singer[0]][channelid] = {} singerlist[singer[0]][channelid]['rev'] = 0 singerlist[singer[0]][channelid]['exp'] = 0 except: print '查询失败:%s'%sql for key in singerlist: print key for channel in singerlist[key]: sql = "select SUM(SINGERRECVGOLD) from ITEMCONSUMERECORD where SINGERID=%d and CHANNELID=%d and OPTIME>=%d and OPTIME try: db_cursor.execute(sql) singerrev1 = db_cursor.fetchone() if (singerrev1 != None): singerlist[key][channel]['rev'] += singerrev1[0] else: print '没有符合条件的数据:%s'%sql except: print '查询失败:%s'%sql sql = "select SUM(SINGERRECVGOLD) from REQSONGCONSUMERECORD where SINGERID=%d and CHANNELID=%d and OPTIME>=%d and OPTIME try: db_cursor.execute(sql) singerrev2 = db_cursor.fetchone() if (singerrev2 != None): singerlist[key][channel]['rev'] += singerrev2[0] else: print '没有符合条件的数据:%s'%sql except: print '查询失败:%s'%sql sql = "select SUM(CONSUMEGOLD) from ITEMCONSUMERECORD_GOLD where PRESENTERID=%d and OPTIME>=%d and OPTIME try: db_cursor.execute(sql) singerexp = db_cursor.fetchone() if (singerrev != None): singerlist[key][channel]['exp'] += singerexp[0] else: print '没有符合条件的数据:%s'%sql except: print '查询失败:%s'%sql print '[客户(%d)收入(%d)支出(%d)]'%(key, singerlist[key][channel]['rev'], singerlist[key][channel]['exp']) db_conn.commit() db_cursor.close() db_conn.close() print '**************************begin check***********************' for channel in channellist: singersincome = 0; for singer in singerlist: if channel in singerlist[singer]: singersincome += singerlist[singer][channel]['rev'] command = "echo channelid: %d channelrev: %d singersrev: %d >> /home/haoren/logdir/checksettle.txt"%(channel, channellist[channel]['rev'], singersincome) os.system(command) print '**************************end check***********************'main()#if __name__ == "__main__":# main()====================================================================================================cat questionstatistic.py#!/usr/bin/env python#-*-coding:utf-8-*-#问题应答统计import MySQLdbimport os, sys, re,stringimport time, tarfile,getoptoptmap = { 'dbuser' : 'haoren', 'dbpass' : 'hjkdhskjsh', 'dbhost' : '172.16.1.8', 'dbport' : 3306, 'dbname' : 'OTHERDB' } def get_files(dir, pattern): res_file_list =[] if os.path.exists(dir): cur_file_list = os.listdir(dir) for file_name in cur_file_list: if re.search(pattern, file_name): res_file_list.append(file_name) return res_file_list else: return 'no'def main(): cur_day = time.strftime("%Y%m%d", time.localtime(time.time()-86400)) opts, args = getopt.getopt(sys.argv[1:], 'd:') for op, value in opts: if op == '-d': m = re.search('[0-9]{8}', value) if m: cur_day = value else: print "请输入8位日期(比如:20130215)" return 'no' fmt_day = time.strftime('%Y-%m-%d', time.localtime(time.mktime(time.strptime(cur_day, '%Y%m%d')))) d_time = time.mktime(time.strptime(fmt_day,'%Y-%m-%d')) print '问题应答情况统计日期:',fmt_day #log_day = time.strftime("%y%m%d", time.localtime(time.time()-86400)) log_day = time.strftime('%y%m%d', time.localtime(time.mktime(time.strptime(cur_day, '%Y%m%d')))) dirname="/home/haoren/logdir/%s_6"%log_day #dirname="/home/haoren/log/%s/"%log_day print dirname db_conn = MySQLdb.connect(user=optmap['dbuser'], passwd=optmap['dbpass'], host=optmap['dbhost'], port=optmap['dbport'], db=optmap['dbname']) db_cursor=db_conn.cursor() db_conn.query("use %s"%optmap['dbname']) db_conn.query("DELETE from QUESTIONSTATISTIC where time='%s'"%fmt_day) print d_time if os.path.exists("/tmp/questionstatistic.txt"): os.system("rm -f /tmp/questionstatistic.txt") file_list2=get_files(dirname,'instructserver') for file2 in file_list2: command = "cat %s/%s | awk '/问题应答统计/'>> /tmp/questionstatistic.txt"%(dirname,file2) os.system(command) filename='/tmp/questionstatistic.txt' record = {} a_file = open(filename, 'r') record['all_question_num'] = 0 record['all_answer_num'] = 0 record['avg_answer_time'] = 0 #130806-17:58:19 InstructServer[14100] INFO: [问题应答统计]初始化问题:4221,提问时间:1375783099,提问者:21000146,不喜欢玩dota,解答者:0,解答时间:0,分值:1,游戏:1 #130806-17:58:51 InstructServer[14100] INFO: [问题应答统计]应答问题,响应时间:32,问题ID:4221,提问时间:1375783099,提问者:21000146,不喜欢玩dota,解答者:21000278,解答时间:1375783131,分值:1,游戏:1 for a_line in a_file.readlines(): m = re.search("^(\S+) InstructServer\[\d+\] INFO: \[问题应答统计\]初始化问题:(\d+),提问时间:(\d+),提问者:(\d+),(\S+),解答者:(\d+),解答时间:(\d+),分值:(\d+),游戏:(\d+)", a_line) if m: record['all_question_num'] +=1; continue m1 = re.search("^(\S+) InstructServer\[\d+\] INFO: \[问题应答统计\]应答问题,响应时间:(\d+),问题ID:(\d+),提问时间:(\d+),提问者:(\d+),(\S+),解答者:(\d+),解答时间:(\d+),分值:(\d+),游戏:(\d+)", a_line) if m1: record['all_answer_num'] +=1; record['avg_answer_time'] +=int(m1.group(2)); if (record['all_answer_num']!=0): record['avg_answer_time'] = record['avg_answer_time']/record['all_answer_num'] print '今日提问总数:',record['all_question_num'] print '今日应答总数:',record['all_answer_num'] print '今日平均应答时间:',record['avg_answer_time'] db_conn.query("insert into QUESTIONSTATISTIC(TIME, QUESTIONNUM, ANSWERNUM, AVGANSWERTIME) values('%s',%d,%d,%d)"%(fmt_day, record['all_question_num'], record['all_answer_num'], record['avg_answer_time'])) a_file.close() db_conn.commit() db_cursor.close() db_conn.close()main()#if __name__ == "__main__":# main()=================================================================================================cat channelsettlement.py#!/usr/bin/env python#-*-coding:utf-8-*-#公司化频道结算月报#****************************************使用说明****************************************************# 内网测试通过命令行参数# -d 指定统计时间# -p 指定日志所在目录# -t 指定临时文件存放目录# 示例如下:# [haoren@localhost tongji]$ ./channelsettlement.py -b 20141225 -e 20150125# 外网无需加参数,使用默认值#****************************************使用说明****************************************************import MySQLdbimport os, sys, re,stringimport time, tarfile,getoptimport datetimeoptmap = { 'dbuser' : 'haoren', 'dbpass' : 'hjkdhskjsh', 'dbhost_im' : '172.16.1.8', 'dbhost_gm' : '172.16.1.8', 'dbport' : 3306, 'dbname_im' : 'FMDB', 'dbname_gm' : 'AMDB' } def get_files(dir, pattern): res_file_list =[] if os.path.exists(dir): cur_file_list = os.listdir(dir) cur_file_list.sort() for file_name in cur_file_list: if re.search(pattern, file_name): res_file_list.append(file_name) return res_file_list else: return 'no'def main(): #获取频道结算起始时间,每月25日结算 cur = datetime.datetime.now() cur_day = cur.day cur_month = cur.month cur_year = cur.year print '*****当前时间: %d-%d-%d'%(cur_year, cur_month, cur_day) if (cur.day != 25): print '*****未到结算日!*****' return begin_day = cur_day begin_month = cur_month begin_year = cur_year if cur_month == 1: begin_month = 12 begin_year = cur_year - 1 else: begin_month = cur_month - 1 beginTime = datetime.datetime(begin_year,begin_month,begin_day,0,0,0) endTime = datetime.datetime(cur_year,cur_month,cur_day,0,0,0) #内网测试指定参数 opts, args = getopt.getopt(sys.argv[1:], 'b:e:') for op, value in opts: if op == '-b': m = re.search('[0-9]{8}', value) if m: beginTime = datetime.datetime.strptime(value, '%Y%m%d') elif op == '-e': m = re.search('[0-9]{8}', value) if m: endTime = datetime.datetime.strptime(value, '%Y%m%d') beginTimeStamp = int(time.mktime(beginTime.timetuple())) endTimeStamp = int(time.mktime(endTime.timetuple())) print '*****结算开始时间 %s (%d)'%(beginTime,beginTimeStamp) print '*****结算结束时间 %s (%d)'%(endTime,endTimeStamp) #获取公司化频道列表 db_conn = MySQLdb.connect(user=optmap['dbuser'], passwd=optmap['dbpass'], host=optmap['dbhost_im'], port=optmap['dbport'], db=optmap['dbname_im']) db_cursor=db_conn.cursor() db_conn.query("use %s"%optmap['dbname_im']) sql = "select `CHANNELID` from `SPECIALCHANNEL`" channellist = {} try: db_cursor.execute(sql) db_rows = db_cursor.fetchall() for row in db_rows: channellist[row[0]] = 0 except: print '查询失败:%s'%sql #获得各个频道的流水,以人民币结算 for channelid in channellist: channellist[channelid] = {} channellist[channelid]['coin'] = 0 channellist[channelid]['rev'] = 0 sql = "select SUM(CONSUMECOIN), SUM(CHANNELRECVGOLD) from ITEMCONSUMERECORD where CHANNELID=%d and OPTIME>=%d and OPTIME try: db_cursor.execute(sql) consumecoin1 = db_cursor.fetchone() if (consumecoin1 != None): channellist[channelid]['coin'] += consumecoin1[0] channellist[channelid]['rev'] += consumecoin1[1] else: print '没有符合条件的数据:%s'%sql except: print '查询失败:%s'%sql sql = "select SUM(CONSUMECOIN), SUM(CHANNELRECVGOLD) from REQSONGCONSUMERECORD where CHANNELID=%d and OPTIME>=%d and OPTIME try: db_cursor.execute(sql) consumecoin2 = db_cursor.fetchone() if (consumecoin2 != None): channellist[channelid]['coin'] += consumecoin2[0] channellist[channelid]['rev'] += consumecoin2[1] else: print '没有符合条件的数据:%s'%sql except: print '查询失败:%s'%sql print '[频道(%d)流水(%d)收入(%d)]'%(channelid,channellist[channelid]['coin'],channellist[channelid]['rev']) singerlist = {} for channelid in channellist: sql = "select PERFORMERID from VCHANNELPERFORMER where CHANNELID=%u"%channelid try: db_cursor.execute(sql) result = db_cursor.fetchall() for singer in result: singerlist[singer[0]] = {} singerlist[singer[0]][channelid] = {} singerlist[singer[0]][channelid]['rev'] = 0 singerlist[singer[0]][channelid]['exp'] = 0 except: print '查询失败:%s'%sql for key in singerlist: print key for channel in singerlist[key]: sql = "select SUM(SINGERRECVGOLD) from ITEMCONSUMERECORD where SINGERID=%d and CHANNELID=%d and OPTIME>=%d and OPTIME try: db_cursor.execute(sql) singerrev1 = db_cursor.fetchone() if (singerrev1 != None): singerlist[key][channel]['rev'] += singerrev1[0] else: print '没有符合条件的数据:%s'%sql except: print '查询失败:%s'%sql sql = "select SUM(SINGERRECVGOLD) from REQSONGCONSUMERECORD where SINGERID=%d and CHANNELID=%d and OPTIME>=%d and OPTIME try: db_cursor.execute(sql) singerrev2 = db_cursor.fetchone() if (singerrev2 != None): singerlist[key][channel]['rev'] += singerrev2[0] else: print '没有符合条件的数据:%s'%sql except: print '查询失败:%s'%sql sql = "select SUM(CONSUMEGOLD) from ITEMCONSUMERECORD_GOLD where PRESENTERID=%d and OPTIME>=%d and OPTIME try: db_cursor.execute(sql) singerexp = db_cursor.fetchone() if (singerrev != None): singerlist[key][channel]['exp'] += singerexp[0] else: print '没有符合条件的数据:%s'%sql except: print '查询失败:%s'%sql print '[客户(%d)收入(%d)支出(%d)]'%(key, singerlist[key][channel]['rev'], singerlist[key][channel]['exp']) db_conn.commit() db_cursor.close() db_conn.close() AMDB_conn = MySQLdb.connect(user=optmap['dbuser'], passwd=optmap['dbpass'], host=optmap['dbhost_gm'], port=optmap['dbport'], db=optmap['dbname_gm']) AMDB_cursor=AMDB_conn.cursor() AMDB_conn.query("use %s"%optmap['dbname_gm']) for channelid in channellist: AMDB_conn.query("delete from CHANNELSETTLELOG where CHANNELID=%d and BEGINTIME=%d and ENDTIME=%d"%(channelid, beginTimeStamp, endTimeStamp-1)) AMDB_conn.query("insert into CHANNELSETTLELOG (CHANNELID,BEGINTIME,ENDTIME,GOLD,RECVGOLD) values(%d,%d,%d,%d,%d)"%(channelid,beginTimeStamp,endTimeStamp-1,channellist[channelid]['coin'],channellist[channelid]['rev'])) for singerid in singerlist: for channelid in singerlist[singerid]: AMDB_conn.query("delete from PERFORMERSETTLELOG where CHANNELID=%d and USERID=%d and BEGINTIME=%d and ENDTIME=%d"%(channelid, singerid, beginTimeStamp, endTimeStamp-1)) AMDB_conn.query("insert into PERFORMERSETTLELOG(CHANNELID,USERID,BEGINTIME,ENDTIME,RECVGOLD,EXPENSE) values(%d,%d,%d,%d,%d,%d)"%(channelid,singerid,beginTimeStamp,endTimeStamp-1,singerlist[singerid][channelid]['rev'],singerlist[singerid][channelid]['exp'])) AMDB_conn.commit() AMDB_cursor.close() AMDB_conn.close()main()#if __name__ == "__main__":# main()=====================================================================================================cat userLogOutInfo.py#!/usr/bin/env python#-*-coding:utf-8-*-#分析日志,将登陆退出信息插入到数据库#每天一张表import MySQLdbimport os, sys, re,stringimport time, tarfile,getoptimport datetimeimport operatorimport copyoptmap = { 'dbuser' : 'haoren', 'dbpass' : 'hjkdhskjsh', 'dbhost' : '172.16.1.8', 'dbhost_gm' : '172.16.1.8', 'dbport' : 3306, 'dbname' : 'FMDB', 'dbname_gm' : 'AMDB'}user = { 'userID':0, 'channelID':0, 'beginDate':0, 'endDate':0}def get_files(dir, pattern): res_file_list =[] if os.path.exists(dir): cur_file_list = os.listdir(dir) for file_name in cur_file_list: if re.search(pattern, file_name): res_file_list.append(file_name) return res_file_list else: return 'no' def updateEndDate(beginDate,endDate,uid,cid): #140520-20:57:54 if(beginDate!='0'): beginDate = str("20") + str(beginDate[0:2]) + "-" + str(beginDate[2:4]) + "-" + str(beginDate[4:6]) + " " + str(beginDate[7:]) if(endDate!='0'): endDate = str("20") + str(endDate[0:2]) + "-" + str(endDate[2:4]) + "-" + str(endDate[4:6]) + " " + str(endDate[7:]) if ( beginDate =='' or beginDate ==0 ): t+=1 before_day = time.strftime("%Y%m%d",time.localtime(time.time()-86400)) sql = "select id from USERLOGOUTINFO_%s where userID=%d and channelID=%d and (endDate='' or endDate='0000-00-00 00:00:00')"%(befor_day,uid,cid) db_cursor.execute(sql) db_rows = db_cursor.fetchall() if(db_rows>0): for id in db_rows: sql="update USERLOGOUTINFO_%s set beginDate='%s' where id=%d"%(befor_day,endDate,id) else: updateEndDate(beginDate,endDate,uid,cid) else: cur_day = time.strftime("%Y%m%d", time.localtime(time.time()-86400)) tabletime = time.strftime("%Y%m%d", time.localtime(time.mktime(time.strptime(cur_day, "%Y%m%d")))) sql = "insert into USERLOGOUTINFO_%s(userID,channelID,beginDate,endDate)value(%d,%d,'%s','%s')"%(tabletime,int(uid),int(cid),beginDate,endDate) return sql def cmp_datetime(a,b): a_datetime = datetime.datetime.strptime(a, '%Y-%m-%d %H:%M:%S') b_datetime = datetime.datetime.strptime(b, '%Y-%m-%d %H:%M:%S') if a_datetime > b_datetime: return 1 elif a_datetime return -1 else: return 0def main(): cur_day = time.strftime("%Y%m%d", time.localtime(time.time()-86400)) log_day = time.strftime('%y%m%d', time.localtime(time.mktime(time.strptime(cur_day, '%Y%m%d')))) if len(sys.argv)>1: cur_day = sys.argv[1] log_day = sys.argv[1][2:] dirname12="/home/haoren/logdir/%s_12/"%log_day #dirname29="/home/haoren/logdir/%s_29/"%log_day dirname76="/home/haoren/logdir/%s_76/"%log_day dirname111="/home/haoren/logdir/%s_111/"%log_day dirname113="/home/haoren/logdir/%s_113/"%log_day user_begin_list = [] user_end_list = [] dirnames = [dirname12,dirname76,dirname111,dirname113] for dirname in dirnames: for fileName in os.listdir(dirname): a_file = open(dirname+fileName, 'r') count = 0 #140518-14:00:30 VChannelServer[15000] INFO: [miccard]频道(3644080)用户(52776729)开始表演...请求相关信息 #140518-14:00:07 VChannelServer[15000] INFO: [miccard]频道(3644080)用户(52776729)结束表演... #140518-14:00:07 VChannelServer[15000] INFO: [miccard]频道(3644080)用户(52776729)被管理员(123456)强制结束表演... #140521-00:00:47 VChannelServer[15011] DEBUG: [observer]onUserUnperform(channelid=3728463,userid=53543825) #140521-12:01:38 VChannelServer[15000] INFO: [client][mic]用户(53543825)请求加入麦序(3644079)成功 for a_line in a_file.readlines(): begin_ = re.search("^(\S+) VChannelServer\[\d+\] DEBUG: \[observer\]onUserPerform\(channelid=(\d+),userid=(\d+)\)", a_line) end_ = re.search("^(\S+) VChannelServer\[\d+\] DEBUG: \[observer\]onUserUnperform\(channelid=(\d+),userid=(\d+)\)", a_line) if begin_: user_begin = {} user_begin['userID'] = begin_.group(3) user_begin['channelID'] = begin_.group(2) user_begin['beginDate'] = str("20") + str(begin_.group(1)[0:2]) + "-" + str(begin_.group(1)[2:4]) + "-" + str(begin_.group(1)[4:6]) + " " + str(begin_.group(1)[7:]) user_begin['endDate'] = 0 user_begin_list.append(user_begin) if end_: user_end = {} user_end['userID'] = end_.group(3) user_end['channelID'] = end_.group(2) user_end['beginDate'] = 0 user_end['endDate'] = str("20") + str(end_.group(1)[0:2]) + "-" + str(end_.group(1)[2:4]) + "-" + str(end_.group(1)[4:6]) + " " + str(end_.group(1)[7:]) user_end_list.append(user_end) a_file.close() user_begin_list.sort(cmp=cmp_datetime, key=operator.itemgetter('beginDate')) user_end_list.sort(cmp=cmp_datetime, key=operator.itemgetter('endDate')) #match beforelist = [] for user_begin in user_begin_list: num = 0 for user_end in user_end_list: begin_datetime = datetime.datetime.strptime(user_begin['beginDate'], '%Y-%m-%d %H:%M:%S') end_datetime = datetime.datetime.strptime(user_end['endDate'], '%Y-%m-%d %H:%M:%S') if (int(user_begin['userID'])==int(user_end['userID']) and int(user_begin['channelID'])==int(user_end['channelID']) and begin_datetime user_begin['endDate'] = user_end['endDate'] del user_end_list[num] break else: num += 1# for list in user_begin_list:# print "userID:" + str(list['userID']) + ",channelID:" + str(list['channelID']) + ",begin:" + str(list['beginDate']) + ",end:" + str(list['endDate']) #sys.exit(1)# for list in user_end_list:# print "userID:" + str(list['userID']) + ",channelID:" + str(list['channelID']) + ",begin:" + str(list['beginDate']) + ",end:" + str(list['endDate']) db_conn = MySQLdb.connect(user=optmap['dbuser'], passwd=optmap['dbpass'], host=optmap['dbhost_gm'], port=optmap['dbport'], db=optmap['dbname_gm']) db_conn.query("use %s" %optmap['dbname_gm']) db_cursor = db_conn.cursor() tabletime = time.strftime("%Y%m%d", time.localtime(time.mktime(time.strptime(cur_day, "%Y%m%d")))) #print "数据库表时间后缀%s"%tabletime tabletime = cur_day print '用户表演时间,统计日期:',tabletime sql="CREATE TABLE IF NOT EXISTS `USERLOGOUTINFO_%s` like USERLOGOUTINFO_SAMPLE"%tabletime #delete try: db_conn.query("drop table USERLOGOUTINFO_%s"%tabletime) except: #print "USERLOGOUTINFO_%s not exist."%tabletime print "The first statistics." #create db_conn.query(sql) #update before yesterday data for list in user_end_list:# for i in range(0,5): before_day = cur_day d1 = datetime.datetime(string.atoi(before_day[0:4]),string.atoi(before_day[4:6]),string.atoi(before_day[6:8])) d2 = d1 + datetime.timedelta(-1) before_day = d2.strftime("%Y%m%d") #before_day = time.strftime("%Y%m%d",time.localtime(time.time()-86400*(i+2))) try: sql = "select id from USERLOGOUTINFO_%s where userID=%d and channelID=%d and ENDTIME=0"%(before_day,int(list['userID']),int(list['channelID'])) db_cursor.execute(sql) print sql #db_rows = db_cursor.fetchall() temp=db_cursor.fetchone() if(temp != None): timeArray = time.strptime(list['endDate'],"%Y-%m-%d %H:%M:%S") timeStamp = int(time.mktime(timeArray)) sql = "update USERLOGOUTINFO_%s set ENDTIME=%d where id=%d"%(before_day,timeStamp,temp[0]) print sql db_conn.query(sql) # del user_end_list[i] except: pass #print "log not exist." #update yesterday data for list in user_begin_list: #cur_day = time.strftime("%Y%m%d", time.localtime(time.time()-864:: #tabletime = time.strftime("%Y%m%d", time.localtime(time.mktime(ti:iime.strptime(cur_day, "%Y%m%d"))) timeArray = time.strptime(str(list['beginDate']),"%Y-%m-%d %H:%M:%S") beginTimestamp = int(time.mktime(timeArray)) if (list['endDate'] == 0 or list['endDate'] =='') : endTimestamp = 0 else: timeArray2 = time.strptime(str(list['endDate']),"%Y-%m-%d %H:%M:%S") endTimestamp = int(time.mktime(timeArray2)) sql = "insert into USERLOGOUTINFO_%s(userID,channelID,BEGINTIME,ENDTIME)value(%d,%d,%d,%d)"%(tabletime,int(list['userID']),int(list['channelID']),beginTimestamp,endTimestamp) db_conn.query(sql) db_conn.commit() db_cursor.close() db_conn.close() print "SUCCESS."main()#if __name__ == "__main__":# main()=======================================================================================================cat vfulinmenDaily.py#!/usr/bin/env python#-*-coding:utf-8-*-#视频直播间福临门开启日报import MySQLdbimport os, sys, re,stringimport time, tarfile,getopt optmap = { 'dbuser' : 'haoren', 'dbpass' : 'hjkdhskjsh', 'dbhost' : '172.16.1.8', 'dbport' : 3306, 'dbname' : 'AMDB' }fulinmen = { 'performerID' : 0, 'channelID' : 0, 'subchannelID' : 0, 'menID' : 0, 'dubi' : 0, 'time' : 0 }hongbao = { 'userID' : 0, 'channelID' : 0, 'subchannelID' : 0, 'menID' : 0, 'dubi' : 0, 'time' : 0 }def get_files(dir, pattern): res_file_list =[] if os.path.exists(dir): cur_file_list = os.listdir(dir) cur_file_list.sort() for file_name in cur_file_list: if re.search(pattern, file_name): res_file_list.append(file_name) return res_file_list else: return 'no'def main(): cur_day = time.strftime("%Y%m%d", time.localtime(time.time()-86400)) opts, args = getopt.getopt(sys.argv[1:], 'd:') for op, value in opts: if op == '-d': m = re.search('[0-9]{8}', value) if m: cur_day = value else: print "请输入8位日期(比如:20130215)" return 'no' fmt_day = time.strftime('%Y-%m-%d', time.localtime(time.mktime(time.strptime(cur_day, '%Y%m%d')))) d_time = time.mktime(time.strptime(fmt_day,'%Y-%m-%d')) print '统计日期:',fmt_day log_day = time.strftime('%y%m%d', time.localtime(time.mktime(time.strptime(cur_day, '%Y%m%d')))) dirname_v1="/home/haoren/logdir/%s_12"%log_day #dirname_v2="/home/haoren/logdir/%s_29"%log_day dirname_v3="/home/haoren/logdir/%s_76"%log_day dirname_v4="/home/haoren/logdir/%s_111"%log_day dirname_v5="/home/haoren/logdir/%s_113"%log_day print dirname_v1 #print dirname_v2 print dirname_v3 print dirname_v4 print dirname_v5 #开启门统计 if os.path.exists("/tmp/openfulinmendaily.txt"): os.system("rm -f /tmp/openfulinmendaily.txt") #用户获取红包统计 if os.path.exists("/tmp/hongbaodaily.txt"): os.system("rm -f /tmp/hongbaodaily.txt") file_list1=get_files(dirname_v1,'vchannelserver') for file1 in file_list1: command = "cat %s/%s | awk '/门统计/'>> /tmp/openfulinmendaily.txt"%(dirname_v1,file1) os.system(command) command = "cat %s/%s | awk '/兔子统计/'>> /tmp/hongbaodaily.txt"%(dirname_v1,file1) os.system(command) file_list2=get_files(dirname_v2,'vchannelserver') for file2 in file_list2: command = "cat %s/%s | awk '/门统计/'>> /tmp/openfulinmendaily.txt"%(dirname_v2,file2) os.system(command) command = "cat %s/%s | awk '/兔子统计/'>> /tmp/hongbaodaily.txt"%(dirname_v2,file2) os.system(command) file_list3=get_files(dirname_v3,'vchannelserver') for file3 in file_list3: command = "cat %s/%s | awk '/门统计/'>> /tmp/openfulinmendaily.txt"%(dirname_v3,file3) os.system(command) command = "cat %s/%s | awk '/兔子统计/'>> /tmp/hongbaodaily.txt"%(dirname_v3,file3) os.system(command) #开启福临门 filename='/tmp/openfulinmendaily.txt' a_file = open(filename, 'r') fulinmen_list = [] #140912-15:42:21 VChannelServer[15000] INFO: [福临门统计]用户(21001043)在频道(2001241)子频道(0)开启福临门(4),开门人民币(299900)时间(1410507741) for a_line in a_file.readlines(): m = re.search("^\S+ \S+\[(\d+)\] INFO: \[福临门统计\]用户\((\d+)\)在频道\((\d+)\)子频道\((\d+)\)开启福临门\((\d+)\),开门人民币\((\d+)\)时间\((\d+)\)", a_line) if m: fulinmen_ = {} fulinmen_['performerID'] = int(m.group(2)) fulinmen_['channelID'] = int(m.group(3)) fulinmen_['subchannelID'] = int(m.group(4)) fulinmen_['menID'] = int(m.group(5)) fulinmen_['dubi'] = int(m.group(6)) fulinmen_['time'] = int(m.group(7)) fulinmen_list.append(fulinmen_) a_file.close() #抓兔子统计 filename='/tmp/hongbaodaily.txt' a_file = open(filename, 'r') hongbao_list = [] #140912-15:42:42 VChannelServer[15000] INFO: [抓兔子统计]用户(21001034)在频道(2001241)子频道(0)用户(21001043),领取福临门(4),红包(110),成功时间(1410507762) for a_line in a_file.readlines(): m = re.search("^\S+ \S+\[(\d+)\] INFO: \[抓兔子统计\]用户\((\d+)\)在频道\((\d+)\)子频道\((\d+)\)用户\((\d+)\),领取福临门\((\d+)\),红包\((\d+)\),成功时间\((\d+)\)", a_line) if m: hongbao_ = {} hongbao_['userID'] = int(m.group(2)) hongbao_['performerID'] = int(m.group(5)) hongbao_['channelID'] = int(m.group(3)) hongbao_['subchannelID'] = int(m.group(4)) hongbao_['menID'] = int(m.group(6)) hongbao_['dubi'] = int(m.group(7)) hongbao_['time'] = int(m.group(8)) hongbao_list.append(hongbao_) a_file.close() db_conn = MySQLdb.connect(user=optmap['dbuser'], passwd=optmap['dbpass'], host=optmap['dbhost'], port=optmap['dbport'], db=optmap['dbname']) db_cursor=db_conn.cursor() db_conn.query("use %s"%optmap['dbname']) tabletime = time.strftime("%Y%m%d", time.localtime(time.mktime(time.strptime(cur_day, "%Y%m%d")))) sql="CREATE TABLE IF NOT EXISTS `FULINMENDAILY_%s` like FULINMENDAILY_SAMPLE"%tabletime db_conn.query(sql) db_conn.query("delete from FULINMENDAILY_%s"%tabletime) for fulinmen in fulinmen_list: db_conn.query("insert into FULINMENDAILY_%s(PERFORMERID,CHANNELID,SUBCHANNELID,MENID,GOALCOIN,OPENTIME) values(%d,%d,%d,%d,%d,%d)"%(tabletime, int(fulinmen['performerID']), int(fulinmen['channelID']), int(fulinmen['subchannelID']), int(fulinmen['menID']),int(fulinmen['dubi']), int(fulinmen['time']))) sql="CREATE TABLE IF NOT EXISTS `HONGBAODAILY_%s` like HONGBAODAILY_SAMPLE"%tabletime db_conn.query(sql) db_conn.query("delete from HONGBAODAILY_%s"%tabletime) for hongbao in hongbao_list: db_conn.query("insert into HONGBAODAILY_%s(USERID,PERFORMERID,CHANNELID,SUBCHANNELID,MENID,DUBI,TIME) values(%d,%d,%d,%d,%d,%d,%d)"%(tabletime, int(hongbao['userID']),int(hongbao['performerID']), int(hongbao['channelID']), int(hongbao['subchannelID']), int(hongbao['menID']),int(hongbao['dubi']), int(hongbao['time']))) db_conn.commit() db_cursor.close() db_conn.close()main()#if __name__ == "__main__":# main()========================================================================cat JieSuanTongJi20160126.py#!/usr/bin/env python#-*-coding:utf-8-*-#特殊食用油明细import MySQLdbimport os, sys, re,stringimport time, tarfile,getoptoptmap = { 'dbuser' : 'haoren', 'dbpass' : 'euwdlcmdwkd', 'dbhost' : '172.27.6.12', 'dbport' : 3306, 'dbname' : 'JSDB' }def get_files(dir, pattern): res_file_list =[] if os.path.exists(dir): cur_file_list = os.listdir(dir) for file_name in cur_file_list: if re.search(pattern, file_name): res_file_list.append(file_name) return res_file_list else: return 'no'def main(): cur_day = time.strftime("%Y%m%d", time.localtime(time.time()-86400)) opts, args = getopt.getopt(sys.argv[1:], 'd:') for op, value in opts: if op == '-d': m = re.search('[0-9]{8}', value) if m: cur_day = value else: print "请输入8位日期(比如:20130215)" return 'no' log_day = time.strftime('%y%m%d', time.localtime(time.mktime(time.strptime(cur_day, '%Y%m%d')))) fmt_day = time.strftime('%Y-%m-%d', time.localtime(time.mktime(time.strptime(cur_day, '%Y%m%d')))) print '结算统计日期:',fmt_day #log_day = time.strftime("%y%m%d", time.localtime(time.time()-86400)) dirname="/home/haoren/logdir/%s_67"%log_day print dirname db_conn = MySQLdb.connect(user=optmap['dbuser'], passwd=optmap['dbpass'], host=optmap['dbhost'], port=optmap['dbport'], db=optmap['dbname']) db_cursor=db_conn.cursor() db_conn.query("use %s"%optmap['dbname']) tabletime = time.strftime("%y%m%d", time.localtime(time.mktime(time.strptime(cur_day, "%Y%m%d")))) sql="CREATE TABLE IF NOT EXISTS `JIESUANTONGJI_%s` like JIESUANTONGJISAMPLE"%tabletime db_conn.query(sql) db_conn.query("delete from JIESUANTONGJI_%s"%tabletime) if os.path.exists("/tmp/JieSuanTongJi2016.txt"): os.system("rm -f /tmp/JieSuanTongJi2016.txt") file_list2=get_files(dirname,'billserver') for file2 in file_list2: command = "cat %s/%s | grep -h -w 结算统计 |grep -v 人民币消费结算统计 >> /tmp/JieSuanTongJi2016.txt"%(dirname,file2) os.system(command) #结算统计记录放在txt文档里面 filename='/tmp/JieSuanTongJi2016.txt' record = {} a_file = open(filename, 'r') #160125-11:00:14 Bill[40268] INFO: [结算统计]时间(1453690814)类别(1)名称(物品收入)频道(3977962)等级(2)用户(65147500)赠送(1)个物品(39)给客户(65147500),客户等级(28),签约(1), 消耗人民币(100), 客户获得人民币(8000), 频道获得人民币(2000),歌手当前人民币(1320960)频道当前人民币(335560) for a_line in a_file.readlines(): m = re.search("^(\S+) Bill\[\d+\] INFO: \[结算统计\]时间\((\d+)\)类别\((\d+)\)名称\((\S+)\)频道\((\d+)\)等级\((\d+)\)用户\((\d+)\)赠送\((\d+)\)个物品\((\d+)\)给客户\((\d+)\),客户等级\((\d+)\),签约\((\d+)\), 消耗人民币\((\d+)\), 客户获得人民币\((\d+)\), 频道获得人民币\((\d+)\),客户当前人民币\((\d+)\)频道当前人民币\((\d+)\)", a_line) if m: #print "第一项:"+m.group(1) #print "第二项:"+m.group(2) #print "第三项:"+m.group(3) #print "第四项:"+m.group(4) #print "第五项:"+m.group(5) #print "第六项:"+m.group(6) #print "第七项:"+m.group(7) #print "第八项:"+m.group(8) #print "第九项:"+m.group(9) #print "第十项:"+m.group(10) #print "第十一项:"+m.group(11) #print "第十二项:"+m.group(12) #print "第十三项:"+m.group(13) #print "第十四项:"+m.group(14) #print "第十五项:"+m.group(15) #print "第十六项:"+m.group(16) #print "第十七项:"+m.group(17) #print "第十八项:"+m.group(18) if int(m.group(14)) >0 or int(m.group(15)) >0 : db_conn.query("insert into JIESUANTONGJI_%s(OPTIME,TYPE,ITEMNAME,CHANNELID,CHANNELLEVEL,PRESENTERID,ITEMNUM,ITEMID,SINGERID,SINGERLEVEL,SIGN,CONSUMECOIN,SINGERRECVGOLD,CHANNELRECVGOLD,CURRENTSINGERGOLD,CURRENTCHANNELGOLD) values(%d,%d,'%s',%d,%d,%d,%d,%d,%d,%d,%d,%d,%d,%d,%d,%d)"%(tabletime,int(m.group(2)),int(m.group(3)),str(m.group(4)),int(m.group(5)),int(m.group(6)),int(m.group(7)),int(m.group(8)),int(m.group(9)),int(m.group(10)),int(m.group(11)),int(m.group(12)),int(m.group(13)),int(m.group(14)),int(m.group(15)),int(m.group(16)),int(m.group(17)))) a_file.close() db_conn.commit() db_cursor.close() db_conn.close()main()#if __name__ == "__main__":# main()===========================================================================================================cat BaoGuoTongJi20160202.py#!/usr/bin/env python#-*-coding:utf-8-*-#特殊食用油明细import MySQLdbimport os, sys, re,stringimport time, tarfile,getoptoptmap = { 'dbuser' : 'haoren', 'dbpass' : 'euwdlcmdwkd', 'dbhost' : '172.27.6.12', 'dbport' : 3306, 'dbname' : 'JIESUANDB' }def get_files(dir, pattern): res_file_list =[] if os.path.exists(dir): cur_file_list = os.listdir(dir) for file_name in cur_file_list: if re.search(pattern, file_name): res_file_list.append(file_name) return res_file_list else: return 'no'def main(): cur_day = time.strftime("%Y%m%d", time.localtime(time.time()-86400)) opts, args = getopt.getopt(sys.argv[1:], 'd:') for op, value in opts: if op == '-d': m = re.search('[0-9]{8}', value) if m: cur_day = value else: print "请输入8位日期(比如:20130215)" return 'no' log_day = time.strftime('%y%m%d', time.localtime(time.mktime(time.strptime(cur_day, '%Y%m%d')))) fmt_day = time.strftime('%Y-%m-%d', time.localtime(time.mktime(time.strptime(cur_day, '%Y%m%d')))) print '结算购物车日期:',fmt_day #log_day = time.strftime("%y%m%d", time.localtime(time.time()-86400)) dirname="/home/haoren/logdir/%s_21"%log_day print dirname db_conn = MySQLdb.connect(user=optmap['dbuser'], passwd=optmap['dbpass'], host=optmap['dbhost'], port=optmap['dbport'], db=optmap['dbname']) db_cursor=db_conn.cursor() db_conn.query("use %s"%optmap['dbname']) tabletime = time.strftime("%y%m%d", time.localtime(time.mktime(time.strptime(cur_day, "%Y%m%d")))) sql="CREATE TABLE IF NOT EXISTS `BAOGUOTONGJI_%s` like BAOGUOSAMPLE"%tabletime db_conn.query(sql) db_conn.query("delete from BAOGUOTONGJI_%s"%tabletime) if os.path.exists("/tmp/BaoGuoTongJi2016.txt"): os.system("rm -f /tmp/BaoGuoTongJi2016.txt") file_list2=get_files(dirname,'userpackageserver') for file2 in file_list2: command = "cat %s/%s | grep -h -w 购物车结算 >> /tmp/BaoGuoTongJi2016.txt"%(dirname,file2) os.system(command) #结算统计记录放在txt文档里面 filename='/tmp/BaoGuoTongJi2016.txt' record = {} a_file = open(filename, 'r') #160201-00:00:38 UserPackage[17501] INFO: [购物车结算]Function(17)OPName([爬榜宝箱])Idx(86139)BackRoll(N)UID(22836501)Old(0)OP(1)(2)New(2)ID(39)Name(大豆)expireTime(1454947200)Type(0) #160201-00:00:28 UserPackage[17501] INFO: [购物车结算]Function(6)OPName(运自行)Idx(0)BackRoll(N)UID(21993135)Old(2129)OP(1)(1)New(2130)ID(11)Name(飙车券)expireTime(1454342428)Type(0) #160418-12:04:31 UserPackage[17501] INFO: [购物车结算]Function(6)OPName(运自行)Idx(8206)BackRoll(N)UID(32253058)Old(34)OP(2)减少(1)New(33)ID(41)Name(自行车令)expireTime(0)Type(0) #160418-12:04:37 UserPackage[17501] INFO: [购物车结算]Function(6)OPName(运自行)Idx(8207)BackRoll(N)UID(32253058)Old(33)OP(2)减少(1)New(32)ID(41)Name(自行车令)expireTime(0)Type(0) for a_line in a_file.readlines(): #m = re.search("^(\S+) UserPackage\[\d+\] INFO: \[购物车结算\]Function\((\d+)\)OPName\((\S+)\)Idx\((\d+)\)BackRoll\((\S+)\)UID\((\d+)\)Old\((\d+)\)OP\((\d+)\)\((\d+)\)New\((\d+)\)ID\((\d+)\)Name\((\S+)\)expireTime\((\d+)\)Type\((\d+)\)", a_line) m = re.search("^(\S+) UserPackage\[\d+\] INFO: \[购物车结算\]Function\((\d+)\)OPName\((\S+)\)Idx\((\d+)\)BackRoll\((\S+)\)UID\((\d+)\)Old\((\d+)\)OP\((\d+)\)(\S+)\((\d+)\)New\((\d+)\)ID\((\d+)\)Name\((\S+)\)expireTime\((\d+)\)Type\((\d+)\)", a_line) if m: print "第一项,时间:"+m.group(1) print "第二项,Function:"+m.group(2) print "第三项,OPName:"+m.group(3) print "第四项,Idx:"+m.group(4) print "第五项,BackRoll:"+m.group(5) print "第六项,UID:"+m.group(6) print "第七项,Old:"+m.group(7) print "第八项,OP:"+m.group(8) print "第九项,"+m.group(9) print "第十项,New:"+m.group(10) print "第十一项,ID:"+m.group(11) print "第十二项,Name:"+m.group(12) print "第十三项,expireTime:"+m.group(13) print "第十四项,Type:"+m.group(14) #print "第十五项:"+m.group(15) #print "第十六项:"+m.group(16) #print "第十七项:"+m.group(17) #print "第十八项:"+m.group(18) opname=m.group(3).replace('[','') opname=opname.replace(']','') #print "第三项,OPName:"+opname #if (int(m.group(7)) # op=1 #else : # op=2 db_conn.query("insert into BAOGUOTONGJI_%s(FUNCTION,OPNAME,IDX,BACKROLL,UID,OLD,OP,NUM,NEW,ID,NAME,EXPIRETIME,TYPE) values(%d,'%s',%d,'%s',%d,%d,%d,%d,%d,%d,'%s',%d,%d)"%(tabletime,int(m.group(2)),opname,int(m.group(4)),str(m.group(5)),int(m.group(6)),int(m.group(7)),int(m.group(8)),int(m.group(10)),int(m.group(11)),int(m.group(12)),str(m.group(13)),int(m.group(14)),int(m.group(15)))) a_file.close() db_conn.commit() db_cursor.close() db_conn.close()main()#if __name__ == "__main__":# main()=====================================================================================================================cat DuBiJieSuan20160325.py#!/usr/bin/env python#-*-coding:utf-8-*-#特殊食用油明细import MySQLdbimport os, sys, re,stringimport time, tarfile,getoptoptmap = { 'dbuser' : 'haoren', 'dbpass' : 'euwdlcmdwkd', 'dbhost' : '172.27.6.12', 'dbport' : 3306, 'dbname' : 'JIESUANDB' }def get_files(dir, pattern): res_file_list =[] if os.path.exists(dir): cur_file_list = os.listdir(dir) for file_name in cur_file_list: if re.search(pattern, file_name): res_file_list.append(file_name) return res_file_list else: return 'no'def main(): cur_day = time.strftime("%Y%m%d", time.localtime(time.time()-86400)) opts, args = getopt.getopt(sys.argv[1:], 'd:') for op, value in opts: if op == '-d': m = re.search('[0-9]{8}', value) if m: cur_day = value else: print "请输入8位日期(比如:20130215)" return 'no' log_day = time.strftime('%y%m%d', time.localtime(time.mktime(time.strptime(cur_day, '%Y%m%d')))) fmt_day = time.strftime('%Y-%m-%d', time.localtime(time.mktime(time.strptime(cur_day, '%Y%m%d')))) print '结算统计日期:',fmt_day #log_day = time.strftime("%y%m%d", time.localtime(time.time()-86400)) dirname="/home/haoren/logdir/%s_67"%log_day print dirname db_conn = MySQLdb.connect(user=optmap['dbuser'], passwd=optmap['dbpass'], host=optmap['dbhost'], port=optmap['dbport'], db=optmap['dbname']) db_cursor=db_conn.cursor() db_conn.query("use %s"%optmap['dbname']) tabletime = time.strftime("%y%m%d", time.localtime(time.mktime(time.strptime(cur_day, "%Y%m%d")))) sql="CREATE TABLE IF NOT EXISTS `DUBIJIESUANTONGJI_%s` like DUBIJIESUANTONGJISAMPLE"%tabletime db_conn.query(sql) db_conn.query("delete from DUBIJIESUANTONGJI_%s"%tabletime) if os.path.exists("/tmp/DuBiJieSuanTongJi2016.txt"): os.system("rm -f /tmp/DuBiJieSuanTongJi2016.txt") file_list2=get_files(dirname,'billserver') for file2 in file_list2: command = "cat %s/%s | grep -h -w 人民币结算 |grep 原来人民币 >> /tmp/DuBiJieSuanTongJi2016.txt"%(dirname,file2) os.system(command) #结算统计记录放在txt文档里面 filename='/tmp/DuBiJieSuanTongJi2016.txt' record = {} a_file = open(filename, 'r') #160125-11:00:14 Bill[40268] INFO: [结算统计]时间(1453690814)类别(1)名称(物品收入)频道(3977962)等级(2)用户(65147500)赠送(1)个物品(39)给客户(65147500),客户等级(28),签约(1), 消耗人民币(100), 客户获得人民币(8000), 频道获得人民币(2000),歌手当前人民币(1320960)频道当前人民币(335560) for a_line in a_file.readlines(): m = re.search("^(\S+) Bill\[\d+\] INFO: \[人民币结算\]时间\((\d+)\),用户ID\((\d+)\),原来人民币\((\d+)\),现在人民币\((\d+)\),人民币操作\((\d+)\),(\S+)\((\d+)\),操作类型\((\d+)\),操作详情\((\d+)\),操作数量\((\d+)\).描述:(\S+)", a_line) if m: print "第一项:"+m.group(1) print "第二项:"+m.group(2) print "第三项:"+m.group(3) print "第四项:"+m.group(4) print "第五项:"+m.group(5) print "第六项:"+m.group(6) print "第七项:"+m.group(7) print "第八项:"+m.group(8) print "第九项:"+m.group(9) print "第十项:"+m.group(10) print "第十一项:"+m.group(11) print "第十二项:"+m.group(12) #print "第十三项:"+m.group(13) #print "第十四项:"+m.group(14) #print "第十五项:"+m.group(15) #print "第十六项:"+m.group(16) #print "第十七项:"+m.group(17) #print "第十八项:"+m.group(18) db_conn.query("insert into DUBIJIESUANTONGJI_%s(OPTIME,CONSUMERID,PRESERVENUM,CURRENTNUM,DUBIOPTYPE,DUBIOPNUM,OPTYPE,OPDETAIL,OPNUM,NAME) values(%d,%d,%d,%d,%d,%d,%d,%d,%d,'%s')"%(tabletime,int(m.group(2)),int(m.group(3)),int(m.group(4)),int(m.group(5)),int(m.group(6)),int(m.group(8)),int(m.group(9)),int(m.group(10)),int(m.group(11)),str(m.group(12)))) #print 'insert into DUBIJIESUANTONGJI_%s(OPTIME,CONSUMERID,PRESERVENUM,CURRENTNUM,DUBIOPTYPE,DUBIOPNUM,OPTYPE,OPDETAIL,OPNUM,NAME) values(%d,%d,%d,%d,%d,%d,%d,%d,%d,%s)'%(tabletime,int(m.group(2)),int(m.group(3)),int(m.group(4)),int(m.group(5)),int(m.group(6)),int(m.group(7)),int(m.group(8)),int(m.group(9)),int(m.group(10)),str(m.group(11))) a_file.close() #db_conn.commit() #db_cursor.close() db_conn.close()main()#if __name__ == "__main__": #main()======================================================================================================================main20160331.py#!/usr/bin/env python#coding=gbkimport sysimport MySQLdbimport datetimeimport timeoptmap = { 'dbuser' : 'haoren', 'dbpass' : 'euwdlcmdwkd', 'dbhost' : '172.27.6.12', 'dbport' : 3306, 'dbname' : 'JIESUANDB' }global connectglobal cursordef mysql_connect(): try: global connect global cursor connect = MySQLdb.connect(user=optmap['dbuser'], passwd=optmap['dbpass'], host=optmap['dbhost'], port=optmap['dbport'], db=optmap['dbname']) cursor=connect.cursor() #gdb_conn.query("use %s"%optmap['dbname']) print "Mysql Connect Success:%s %s=%s %s" %(optmap['dbhost'], optmap['dbuser'], optmap['dbpass'], optmap['dbname']) return except MySQLdb.Error,e: print "Mysql ERROR %d:%s" %(e.args[0], e.args[1]) returndef mysql_close(): global connect global cursor connect.commit() cursor.close() connect.close() returndef checkOne(table,userID): global cursor #mysql_connect() sql="SELECT `INDEX`,`PRESERVENUM`,`CURRENTNUM`,`OPTYPE` FROM `%s` WHERE `CONSUMERID`=%d ORDER BY `OPTIME`,`INDEX`"%(table,userID) cursor.execute(sql); i=0 right=0 increment_total = 0 consume_total = 0 charge_coins = 0 first=-10086 last=0 increment_abs=0 for (index,pre,cur,type) in cursor: #print(index,pre,cur) if(first==-10086): first=pre last = cur if(cur-pre>0): increment_total+= cur - pre else: consume_total+= cur - pre if(type==1002 or type==1016): charge_coins+=cur-pre if(right!=pre and i!=0): print("Error Index:%d"%i) print(index,pre,cur) i=index right=cur increment_abs = last-first-charge_coins one = (userID,increment_total,consume_total,charge_coins,last-first,increment_abs,first,last) return one;def checkAll(tableName): global cursor sql = "SELECT `CONSUMERID` FROM `%s` GROUP BY `CONSUMERID`;"%(tableName) cursor.execute(sql) print("doing...") all =[] for userId in cursor: all.append(checkOne(tableName,userId[0])) #print(userId) print("finish.") all.sort(key=lambda x:x[5],reverse=True) fileOut = open("./%s.csv"%(tableName),"w+") fileOut.writelines("%-12s,%-12s,%-12s,%-12s,%-12s,%-12s,%-12s,%-12s\n"%("用户ID","总增加","总消耗","总充值","实际增加","绝对增加","昨日结余","今日结余")) for one in all: #str="%12d,%12d,%12d,%12d,%12d,%12d,%12d,%12d\n"%(one[0],one[1]/100,one[2]/100,one[3]/100,one[4]/100,one[5]/100,one[6]/100,one[7]/100); str="%d,%d,%d,%d,%d,%d,%d,%d\n"%(one[0],one[1]/100,one[2]/100,one[3]/100,one[4]/100,one[5]/100,one[6]/100,one[7]/100); #str="%d,%.2f,%.2f,%.2f,%.2f,%.2f,%.2f,%.2f\n"%(one[0],one[1]/100,one[2]/100,one[3]/100,one[4]/100,one[5]/100,one[6]/100,one[7]/100); fileOut.writelines(str) fileOut.close() returndef main(): mysql_connect() cur_day = time.strftime("%Y%m%d", time.localtime(time.time()-86400)) log_day = time.strftime('%y%m%d', time.localtime(time.mktime(time.strptime(cur_day, '%Y%m%d')))) logTableName = "DUBIJIESUANTONGJI_%s" %log_day checkAll(logTableName) mysql_close()main()#SELECT * FROM USERCOINSSTATISTICS_160327=========================================================================cat transfercoin.py#!/usr/bin/env python#encoding=gb2312import sysimport urllib2import socketimport jsondef geturl(id): # urltemple = 'http://172.27.6.27:8080/dudu.php?cmd=145&json={"userid":%s,"op":0,"coin":%d,"orderId":"","accounttype":3}' url= urltemple % (id, 0) print url try: res=urllib2.urlopen(url).read() print res hjson = json.loads(res) print url,res, hjson["usercoin"] #{"accounttype":3,"coin":100,"coinpool":100,"op":0,"orderId":"","retcode":0,"usercoin":998906970,"userid":21001035} url= urltemple % (id, hjson["usercoin"]) #url= urltemple % (id, 0) res = urllib2.urlopen(url).read() print url,res, hjson["usercoin"] except: print 'open url error %s' % id socket.setdefaulttimeout(1)#geturl(21001035)f = open("imid.txt") # 返回一个文件对象line = f.readline() # 调用文件的 readline()方法while line: print line, # 后面跟 ',' 将忽略换行符 # print(line, end = '') # 在 Python 3中使用 userid=line.rstrip() for i in range(1,2): geturl(userid) line = f.readline()f.close()#print "\n按任意键结束"#sys.stdin.readline()======================================================================================================批量查询用户昵称%pyspark#encoding=gbk#-*-coding:gbk-*-import sys#import MySQLdbimport mysql.connectorimport pandas as pdimport datetimeimport timeoptmap = { 'dbuser' : 'haoren', 'dbpass' : 'frewqasd', 'dbhost' : '172.26.2108', 'dbport' : 3306, 'dbname' : 'lsdb' }optmap1 = { 'dbuser' : 'haoren', 'dbpass' : 'frewqasd', 'dbhost' : '172.26.2105', 'dbport' : 3306, 'dbname' : 'PKDB' }def sql_select(reqsql): ret = '' try: db_conn = mysql.connector.connect(user=optmap['dbuser'], password=optmap['dbpass'], host=optmap['dbhost'], port=optmap['dbport'], database=optmap['dbname']) db_cursor=db_conn.cursor() count = db_cursor.execute(reqsql) ret = db_cursor.fetchall() except mysql.connector.Error as e: print ('Error : {}'.format(e)) finally: db_cursor.close() db_conn.close return retdef sql_select1(reqsql1): ret = '' try: db_conn1 = mysql.connector.connect(user=optmap1['dbuser'], password=optmap1['dbpass'], host=optmap1['dbhost'], port=optmap1['dbport'], database=optmap1['dbname']) db_cursor1=db_conn1.cursor() count = db_cursor1.execute(reqsql1) ret1 = db_cursor1.fetchall() except mysql.connector.Error as e: print ('Error : {}'.format(e)) finally: db_cursor1.close() db_conn1.close return ret1#批量查询用户的昵称def getnickname(uid): id = int(uid)%10 reqsql = "select CHANNELNICKNAME from CHARBASE%d where ID=%d" %(id,uid) #reqsql = "select NICKNAME from CHARBASE%d where ID=%d" %(id,uid) ret = sql_select(reqsql) return ret#中括号内填写用户内部ID,用逗号隔开userlist = [67110207,90528820,90489527]for i in userlist: #(功能1)查询用户昵称 ret1 = getnickname(i) ss = ret1[0][0] ss = ss.encode('unicode-escape').decode('string_escape') nick_name = ss.decode('gbk') print i,",",nick_name===============================================================================================================所有用户充值统计邮件%pyspark#用户消费查询import sys#import MySQLdbimport mysql.connectorimport pandas as pdimport datetimeimport timeimport urllibimport urllib2optmap = { 'dbuser' : 'haoren', 'dbpass' : 'frewqasd', 'dbhost' : '172.26.2104', 'dbport' : 3306, 'dbname' : 'lsdb' }#另外一种配色方案 ff9900 00B2EE FF33FF EEEEEEdef json2html(title, data): #str = '' str = '' if title: str += '' for col in range(len(title)): str += '' + title[col] + '' str += '' rowcount = 0 for row in range(len(data)): rowcount+=1 str += '' for col in range(len(data[row])): if rowcount % 2 == 0: str += '' + data[row][col] + '' else: str += '' + data[row][col] + '' str += '' #str += '' str += '' return str;def sendmail(mailto, subject, body): #发起请求的url post_url = 'http://10.13.4.5/sendmail/send.php'; postData = 'mail_to=' + mailto + '&smtp_subject=' + subject + '&mailtype=HTML&body=' + body req = urllib2.Request(post_url) response = urllib2.urlopen(req, postData) #打印返回值 print response.read()def sql_select(reqsql): ret = '' try: db_conn = mysql.connector.connect(user=optmap['dbuser'], password=optmap['dbpass'], host=optmap['dbhost'], port=optmap['dbport'], database=optmap['dbname']) db_cursor=db_conn.cursor() count = db_cursor.execute(reqsql) ret = db_cursor.fetchall() except mysql.connector.Error as e: print ('Error : {}'.format(e)) finally: db_cursor.close() db_conn.close return ret #用户充值def getcharge(startday, endday): strdate = startday.strftime("%y%m%d") enddate = endday.strftime("%y%m%d") tsstart=time.mktime(startday.timetuple()) tsend=time.mktime(endday.timetuple()) daycomsume = 0.0 pc = 0.0 wifi = 0.0 apple = 0.0 bag = 0.0 dayuser = 0 dayusersum = 0 pcuser = 0 wifiuser = 0 appleuser = 0 baguser = 0 #type= 22-wifi充值 23-用户直充|充值豪礼 0-点数消耗 19-苹果充值 -1-所有 #对应产品日报邮件中 0-用户pc版 22-用户app版 23-用户直充 type = -1 for n in range(0,20): if type == -1: #总计 reqsql = "select TYPE,SUM(CONSUME/100) AS ALLCOMSUME from `USERCONSUMPTIONRECORD%d` where TIME>=%d AND TIME print reqsql ret = sql_select(reqsql) print ret #print ret[0][0] if ret[0][0] is not None: for i in range(len(ret)): ctype = int(ret[i][0]) if ctype == 0: pc = pc + float(ret[i][1]) elif ctype == 19: apple = apple + float(ret[i][1]) elif ctype == 22: wifi = wifi + float(ret[i][1]) elif ctype == 23: bag = bag + float(ret[i][1]) daycomsume = daycomsume + float(ret[i][1]) else: #充值 reqsql = "select SUM(CONSUME/100) AS ALLCOMSUME from `USERCONSUMPTIONRECORD%d` where TYPE=%d AND TIME>=%d AND TIME print reqsql ret = sql_select(reqsql) print ret #print ret[0][0] if ret[0][0] is not None: daycomsume = daycomsume + float(ret[0][0]) #人数 if type == -1: reqsql = "select TYPE, COUNT(DISTINCT USERID) from `USERCONSUMPTIONRECORD%d` where TIME>=%d AND TIME ret = sql_select(reqsql) if ret[0][0] is not None: for i in range(len(ret)): ctype = int(ret[i][0]) if ctype == 0: pcuser = pcuser + int(ret[i][1]) elif ctype == 19: appleuser = appleuser + int(ret[i][1]) elif ctype == 22: wifiuser = wifiuser + int(ret[i][1]) elif ctype == 23: baguser = baguser + int(ret[i][1]) dayusersum = dayusersum + int(ret[i][1]) reqsql = "select COUNT(DISTINCT USERID) from `USERCONSUMPTIONRECORD%d` where TIME>=%d AND TIME ret = sql_select(reqsql) if ret[0][0] is not None: dayuser = dayuser + int(ret[0][0]) print "strdate, dayuser, daycomsume, pc, wifi, bag, apple, pcuser, wifiuser, baguser, appleuser" print strdate, dayuser, daycomsume, pc, wifi, bag, apple, pcuser, wifiuser, baguser, appleuser title = ("项目", "合计", "用户pc版", "用户app版","用户直充", "苹果充值", "平台核对数值") pt = round(pc+ wifi+ bag) ptuser = int(pcuser + wifiuser + baguser) #data = (("充值金额", daycomsume, pc, wifi, bag, apple, pt), ("付费账号", dayuser, pcuser, wifiuser, baguser, appleuser, ptuser)) data = (("充值金额", str(daycomsume), str(pc), str(wifi), str(bag), str(apple), str(pt)), ("付费账号", str(dayuser), str(pcuser), str(wifiuser), str(baguser), str(appleuser), str(ptuser))) print title print data html = json2html(title, data) #print html sendmail("[email protected],[email protected]", strdate + "用户充值日报", html) #sendmail("[email protected]", strdate + "用户充值日报", html)def fromDayToDay(startdate, datelen, func): delta = datetime.timedelta(days=1) for i in range(0,datelen): startday = startdate + delta * i endday = startdate + delta * (i + 1) func(startday, endday) return#测试#html = '项目合计用户pc版bgColor=#00B2EE>用户app版用户直充苹果充值充值金额167765.5align=center bgColor=#f5f9ff>114150.53928.049582.0105.0付费账号align=center bgColor=#e3efff>43034848742'#sendmail("[email protected]", "人民币日报", html)today = datetime.date.today()#today = datetime.date(2017,7,24)yesterday = today - datetime.timedelta(days=1)fromDayToDay(yesterday, 1, getcharge)%pysparkimport sys#import MySQLdbimport mysql.connectorimport pandas as pdimport datetimeimport timeoptmap = { 'dbuser' : 'haoren', 'dbpass' : 'frewqasd', 'dbhost' : '172.26.212', 'dbport' : 3306, 'dbname' : 'JIESUANDB', }def sql_select(reqsql): ret = '' try: db_conn = mysql.connector.connect(user=optmap['dbuser'], password=optmap['dbpass'], host=optmap['dbhost'], port=optmap['dbport'], database=optmap['dbname']) db_cursor=db_conn.cursor() #db_conn.query("use %s"%optmap['dbname']) print db_cursor count = db_cursor.execute(reqsql) ret = db_cursor.fetchall() except mysql.connector.Error as e: print ('Error : {}'.format(e)) finally: db_cursor.close() db_conn.close return ret def getusercharge(userid, startday): strdate = startday.strftime("%y%m%d") reqsql = "select CONSUMERID,SUM(DUBIOPNUM) from `DUBIJIESUANTONGJI_%s` where CONSUMERID=%u AND DUBIOPTYPE=1 AND (OPTYPE=1016 OR OPTYPE=1020 OR OPTYPE=1021)" % (strdate, int(userid)) print reqsql ret = sql_select(reqsql) print ret if ret[0][0] is not None: return float(ret[0][1])/100.0 else: return 0 startdate = datetime.date(2017, 8, 1)getusercharge(21001034, startdate)=================================================================================================================%pyspark#用户金币查询import sys#import MySQLdbimport mysql.connectorimport pandas as pdimport datetimeimport timeoptmap = { 'dbuser' : 'haoren', 'dbpass' : 'frewqasd', 'dbhost' : '172.26.2105', 'dbport' : 3306, 'dbname' : 'PKDB' }#def sql_select(reqsql):# try:# db_conn = MySQLdb.connect(user=optmap['dbuser'], passwd=optmap['dbpass'], host=optmap['dbhost'], port=optmap['dbport'], db=optmap['dbname'])# db_cursor=db_conn.cursor()# db_conn.query("use %s"%optmap['dbname'])# count = db_cursor.execute(reqsql)# ret = db_cursor.fetchall()## db_cursor.close()# db_conn.close# return ret# except MySQLdb.Error,e:# print "Mysql ERROR %d:%s" %(e.args[0], e.args[1])# return ''def sql_select(reqsql): ret = '' try: db_conn = mysql.connector.connect(user=optmap['dbuser'], password=optmap['dbpass'], host=optmap['dbhost'], port=optmap['dbport'], database=optmap['dbname']) db_cursor=db_conn.cursor() count = db_cursor.execute(reqsql) ret = db_cursor.fetchall() except mysql.connector.Error as e: print ('Error : {}'.format(e)) finally: db_cursor.close() db_conn.close return ret#查询某天此用户的收入def getincome(startday,endday,userid): begin = datetime.datetime.strptime(startday,'%Y-%m-%d') end = datetime.datetime.strptime(endday,'%Y-%m-%d') goldsum = 0 i = int(userid) % 10 #reqsql = "select sum(DURATION)/3600 from PERFORMERSHOWTIMERECORD%u where PERFORMERID=%u and STARTTIME>=unix_timestamp('%s') and STARTTIME reqsql = "select from_unixtime(STARTTIME,'%%Y-%%m-%%d') as daytime ,sum(DURATION)/3600 from PERFORMERSHOWTIMERECORD%u where PERFORMERID=%u and STARTTIME>=unix_timestamp('%s') and STARTTIME #print reqsql ret = sql_select(reqsql) #print ret for m in ret: print m[0] ,m[1] if m[1] is not None: goldsum = goldsum + float(m[1]) print goldsumuserlist = [93706458]for p in userlist: print "################下面是 %s 的数据##################" %p getincome('2017-08-14','2017-09-10',p) =============================================================================================================%pyspark#查询认证用户import sys#import MySQLdbimport mysql.connectorimport pandas as pdimport datetimeimport timeoptmap = { 'dbuser' : 'haoren', 'dbpass' : 'frewqasd', 'dbhost' : '172.26.2108', 'dbport' : 3306, 'dbname' : 'lsdb' }optmap1 = { 'dbuser' : 'haoren', 'dbpass' : 'frewqasd', 'dbhost' : '172.26.2105', 'dbport' : 3306, 'dbname' : 'PKDB' }def sql_select(reqsql): ret = '' try: db_conn = mysql.connector.connect(user=optmap['dbuser'], password=optmap['dbpass'], host=optmap['dbhost'], port=optmap['dbport'], database=optmap['dbname']) db_cursor=db_conn.cursor() count = db_cursor.execute(reqsql) ret = db_cursor.fetchall() except mysql.connector.Error as e: print ('Error : {}'.format(e)) finally: db_cursor.close() db_conn.close return retdef sql_select1(reqsql1): ret = '' try: db_conn1 = mysql.connector.connect(user=optmap1['dbuser'], password=optmap1['dbpass'], host=optmap1['dbhost'], port=optmap1['dbport'], database=optmap1['dbname']) db_cursor1=db_conn1.cursor() count = db_cursor1.execute(reqsql1) ret1 = db_cursor1.fetchall() except mysql.connector.Error as e: print ('Error : {}'.format(e)) finally: db_cursor1.close() db_conn1.close return ret1 #定义查询所有用户def getuser(): reqsql = "select PERFORMERID from PERFORMERINFO where PERFORMERTYPE=1" #print reqsql ret = sql_select(reqsql) return ret#查询用户的当前金币def getgold(userid): i = int(userid) % 10 reqsql1 = "select GOLDREFER/100 from CHARCOIN%u where ID=%u" %(i, int(userid)) #print reqsql ret1 = sql_select(reqsql1) #print ret1 if len(ret1) > 0: return ret1[0][0] else: return None#定义查询用户最后上麦时间def getlasttime(uid): m = int(uid) % 10 reqsql2="select from_unixtime(STARTTIME,'%%Y-%%m-%%d') from PERFORMERSHOWTIMERECORD%u where PERFORMERID=%u order by STARTTIME desc limit 1 " %(m,uid) ret2 = sql_select1(reqsql2) if len(ret2) >0: return ret2[0][0] else: return None ret = getuser()print "用户ID,留存金币,最后上麦时间"for p in ret: uid = p[0] #print uid ret3 = getgold(uid) #print ret3 ret4 = getlasttime(uid) #print ret4 print uid,",",ret3,",",ret4======================================================================================================================%pyspark#用户人民币查询import sys#import MySQLdbimport mysql.connectorimport pandas as pdimport datetimeimport timeoptmap = { 'dbuser' : 'haoren', 'dbpass' : 'frewqasd', 'dbhost' : '172.26.212', 'dbport' : 3306, 'dbname' : 'JIESUANDB' }def sql_select(reqsql): ret = '' try: db_conn = mysql.connector.connect(user=optmap['dbuser'], password=optmap['dbpass'], host=optmap['dbhost'], port=optmap['dbport'], database=optmap['dbname']) db_cursor=db_conn.cursor() count = db_cursor.execute(reqsql) ret = db_cursor.fetchall() except mysql.connector.Error as e: print ('Error : {}'.format(e)) finally: db_cursor.close() db_conn.close return ret#查询某天此用户的收入def getincome(startday,endday,userid): begin = datetime.datetime.strptime(startday,'%Y-%m-%d') end = datetime.datetime.strptime(endday,'%Y-%m-%d') goldsum = 0 for i in range((end - begin).days+1): #print i day = begin + datetime.timedelta(days=i) day1 = day.strftime('%y%m%d') #下面一句用于查询分成后用户的收入 reqsql = "select sum(SINGERRECVGOLD)/100 from `JIESUANTONGJI_%s` where SINGERID=%u" % (day1, int(userid)) #下面一句用于查询分成前用户消费多少人民币 #reqsql = "select sum(CONSUMECOIN)/100 from `JIESUANTONGJI_%s` where SINGERID=%u" % (day1, int(userid)) #print reqsql ret = sql_select(reqsql) print day1, ret[0][0] if ret[0][0] is not None: goldsum = goldsum + float(ret[0][0]) print goldsum #return float(ret[0][0])/100getincome('2017-09-1','2017-09-28',93454519)==================================================================================================================%pyspark#查询认证用户import sys#import MySQLdbimport mysql.connectorimport pandas as pdimport datetimeimport timeoptmap = { 'dbuser' : 'haoren', 'dbpass' : 'frewqasd', 'dbhost' : '172.26.2.8', 'dbport' : 3306, 'dbname' : 'lsdb' }optmap1 = { 'dbuser' : 'haoren', 'dbpass' : 'frewqasd', 'dbhost' : '172.26.2.5', 'dbport' : 3306, 'dbname' : 'PKDB' }def sql_select(reqsql): ret = '' try: db_conn = mysql.connector.connect(user=optmap['dbuser'], password=optmap['dbpass'], host=optmap['dbhost'], port=optmap['dbport'], database=optmap['dbname']) db_cursor=db_conn.cursor() count = db_cursor.execute(reqsql) ret = db_cursor.fetchall() except mysql.connector.Error as e: print ('Error : {}'.format(e)) finally: db_cursor.close() db_conn.close return retdef sql_select1(reqsql1): ret = '' try: db_conn1 = mysql.connector.connect(user=optmap1['dbuser'], password=optmap1['dbpass'], host=optmap1['dbhost'], port=optmap1['dbport'], database=optmap1['dbname']) db_cursor1=db_conn1.cursor() count = db_cursor1.execute(reqsql1) ret1 = db_cursor1.fetchall() except mysql.connector.Error as e: print ('Error : {}'.format(e)) finally: db_cursor1.close() db_conn1.close return ret1#定义查询认证用户函数def renzhengsingger(startday,endday): t1 = int(time.mktime(time.strptime(startday,'%Y-%m-%d %H:%M:%S')) ) t2 = int(time.mktime(time.strptime(endday,'%Y-%m-%d %H:%M:%S'))) reqsql = "select PERFORMERID,from_unixtime(ADDTIME) from PERFORMERINFO where ADDTIME >=%s and ADDTIME ret = sql_select(reqsql) for i in ret: #print i[0] id = int(i[0])%10 reqsql1 = "select sum(DURATION)/3600 from PERFORMERSHOWTIMERECORD%d where STARTTIME >=%s and STARTTIME #print reqsql1 ret1 = sql_select1(reqsql1) print i[0],",",i[1],",",ret1[0][0] renzhengsingger('2017-11-01 00:00:00','2017-11-2 23:00:00')==================================================================================================================%pyspark#频道签约用户信息 import sys#import MySQLdbimport mysql.connectorimport pandas as pdimport datetimeimport timeimport urllibimport urllib2optmap = { 'dbuser' : 'haoren', 'dbpass' : 'frewqasd', 'dbhost' : '172.26.2.8', 'dbport' : 3306, 'dbname' : 'lsdb' } #def sql_select(reqsql):# try:# db_conn = MySQLdb.connect(user=optmap['dbuser'], passwd=optmap['dbpass'], host=optmap['dbhost'], port=optmap['dbport'], db=optmap['dbname'])# db_cursor=db_conn.cursor()# db_conn.query("use %s"%optmap['dbname'])# count = db_cursor.execute(reqsql)# ret = db_cursor.fetchall()# db_cursor.close()# db_conn.close# return ret# except MySQLdb.Error,e:# print "Mysql ERROR %d:%s" %(e.args[0], e.args[1])# return ''def sql_select(reqsql): ret = '' try: db_conn = mysql.connector.connect(user=optmap['dbuser'], password=optmap['dbpass'], host=optmap['dbhost'], port=optmap['dbport'], database=optmap['dbname']) db_cursor=db_conn.cursor() count = db_cursor.execute(reqsql) ret = db_cursor.fetchall() except mysql.connector.Error as e: print ('Error : {}'.format(e)) finally: db_cursor.close() db_conn.close return ret#查询一个的签约用户def getuser(channelid): reqsql = "select * from PERFORMERINFO where CHANNELID=%u" %(int(channelid)) print reqsql ret = sql_select(reqsql) #print ret #print ret[0],ret[1] #print ret[0][0] ,ret[1][0] ,ret[2][0] ,ret[3][0] #print len(ret) return ret #查询用户的当前人民币def gettime(userid): i = int(userid) % 10 #reqsql = "select sum(DURATION)/3600 from PERFORMERSHOWTIMERECORD%u where STARTTIME>=unix_timestamp('2017-07-14 00:00:00') and STARTTIME reqsql = "select GOLDREFER/100 from CHARCOIN%u where ID=%u" %(i, int(userid)) #print reqsql ret1 = sql_select(reqsql) #print ret1 if len(ret1) > 0: return ret1[0][0] else: return Noneret2 = getuser(3644080) for i in ret2 : #print "i===" ,i userid = i[0] print userid ret1 = gettime(userid) print userid ,str(ret1)=============================================================================================================%pyspark#查询认证用户import sys#import MySQLdbimport mysql.connectorimport pandas as pdimport datetimeimport timeoptmap = { 'dbuser' : 'haoren', 'dbpass' : 'frewqasd', 'dbhost' : '172.26.2.15', 'dbport' : 3306, 'dbname' : 'PKDB' }def sql_select(reqsql): ret = '' try: db_conn = mysql.connector.connect(user=optmap['dbuser'], password=optmap['dbpass'], host=optmap['dbhost'], port=optmap['dbport'], database=optmap['dbname']) db_cursor=db_conn.cursor() count = db_cursor.execute(reqsql) ret = db_cursor.fetchall() except mysql.connector.Error as e: print ('Error : {}'.format(e)) finally: db_cursor.close() db_conn.close return retuserlist = []def renzhengsingger(startday,endday): t1 = int(time.mktime(time.strptime(startday,'%Y-%m-%d %H:%M:%S')) ) t2 = int(time.mktime(time.strptime(endday,'%Y-%m-%d %H:%M:%S'))) for n in range(0,10): reqsql = "select PERFORMERID,sum(DURATION)/3600 from PERFORMERSHOWTIMERECORD%d where STARTTIME >=%s and STARTTIME ret = sql_select(reqsql) userlist.append(ret) #print userlist for i in range(0,10): for p in userlist[i]: print p[0],p[1] renzhengsingger('2017-08-28 00:00:00','2017-09-20 00:00:00')================================================================================================================%pyspark#查询用户余额import sys#import MySQLdbimport mysql.connectorimport pandas as pdoptmap = { 'dbuser' : 'haoren', 'dbpass' : 'frewqasd', 'dbhost' : '172.26.2.14', 'dbport' : 3306, 'dbname' : 'lsdb' }def sql_select(reqsql): ret = '' try: db_conn = mysql.connector.connect(user=optmap['dbuser'], password=optmap['dbpass'], host=optmap['dbhost'], port=optmap['dbport'], database=optmap['dbname']) db_cursor=db_conn.cursor() count = db_cursor.execute(reqsql) ret = db_cursor.fetchall() except mysql.connector.Error as e: print ('Error : {}'.format(e)) finally: db_cursor.close() db_conn.close return ret#def sql_select(reqsql):# try:# db_conn = MySQLdb.connect(user=optmap['dbuser'], passwd=optmap['dbpass'], host=optmap['dbhost'], port=optmap['dbport'], db=optmap['dbname'])# db_cursor=db_conn.cursor()# db_conn.query("use %s"%optmap['dbname'])# count = db_cursor.execute(reqsql)# ret = db_cursor.fetchall()## db_cursor.close()# db_conn.close# return ret# except MySQLdb.Error,e:# print "Mysql ERROR %d:%s" %(e.args[0], e.args[1])# return '' def getusercoin(userid): i = int(userid) % 10 reqsql = "select ID,COINREFER from CHARCOIN%u where ID=%u" % (int(i), int(userid)) #print reqsql ret = sql_select(reqsql) #print ret return ret[0] def getall(userlist): userdata = pd.DataFrame(columns=('userid', 'coin')) index = 0 for userid in userlist: coins = getusercoin(userid) #print coins[0],coins[1]/100.0 if coins[0] is not None: userdata.loc[index] = (str(coins[0]), coins[1]/100.0) else: userdata.loc[index] = (str(userid), 0) index += 1 #print userdata.tail(10) df = spark.createDataFrame(userdata) #df.createOrReplaceTempView('userdata') df.show(50) #这里填写用户ID userlist = [23443660,70461470]getall(userlist)===================================================================================================%pyspark#查询用户机器ID import sysimport MySQLdbimport pandas as pdimport datetimeoptmap = { 'dbuser' : 'haoren', 'dbpass' : 'frewqasd', 'dbhost' : '172.26.2105', 'dbport' : 3306, 'dbname' : 'PKDB' }def sql_select(reqsql): try: db_conn = MySQLdb.connect(user=optmap['dbuser'], passwd=optmap['dbpass'], host=optmap['dbhost'], port=optmap['dbport'], db=optmap['dbname']) db_cursor=db_conn.cursor() db_conn.query("use %s"%optmap['dbname']) count = db_cursor.execute(reqsql) ret = db_cursor.fetchall() db_cursor.close() db_conn.close return ret except MySQLdb.Error,e: print "Mysql ERROR %d:%s" %(e.args[0], e.args[1]) return '' def getusermid(userid, months): i = int(userid) % 50 reqsql = "select USERID,MACHINEID from LOGINHISTORY%s%u where USERID=%u group by MACHINEID" % (months,int(i), int(userid)) print reqsql ret = sql_select(reqsql) #print ret #print ret[0] return ret def getall(userlist): today = datetime.date.today() months = today.strftime("%Y%m") userdata = pd.DataFrame(columns=('USERID', 'MACHINEID')) index = 0 for userid in userlist: coins = getusermid(userid, months) for i in range(len(coins)): #print coins[i] userdata.loc[index] = (str(coins[i][0]), str(coins[i][1])) index += 1 #print coins[0],coins[1]/100.0 #userdata.loc[index] = (str(coins[0]), coins[1]/100.0) #index += 1 #print userdata.tail(10) df = spark.createDataFrame(userdata) #df.createOrReplaceTempView('userdata') df.show(1000) #这里填写用户ID userlist = [21896345,25806997,]getall(userlist)=================================================================================================%pysparkfrom pyspark.sql import Rowfrom pyspark.sql.types import *from pyspark.sql.functions import udfimport MySQLdbimport mysql_opimport datetimeimport timefrom mysql_op import MySQLimport pandas as pdimport numpy as npfrom fastparquet import ParquetFilefrom fastparquet import writedef fromDayToDay(startdate, datelen, func): delta = datetime.timedelta(days=1) for i in range(0,datelen): startday = startdate + delta * i endday = startdate + delta * (i + 1) func(startday, endday) returndef fromDayToEndDay(startdate, datelen, endday, func): delta = datetime.timedelta(days=1) for i in range(0,datelen): startday = startdate + delta * i #endday = startdate + delta * (i + 1) func(startday, endday) return# 获取人民币数据def saveDayBillData(startday, endday): strday = startday.strftime("%Y%m%d") if os.path.exists("/home/haoren/logstatis/billdata"+strday+".parq"): return #数据库连接参数 dbconfig = {'host':'172.26.212', 'port': 3306, 'user':'haoren', 'passwd':'frewqasd', 'db':'JIESUANDB', 'charset':'utf8'} #连接数据库,创建这个类的实例 mysql_cn= MySQLdb.connect(host=dbconfig['host'], port=dbconfig['port'],user=dbconfig['user'], passwd=dbconfig['passwd'], db=dbconfig['db']) tsstart=time.mktime(startday.timetuple()) tsend=time.mktime(endday.timetuple()) strdate = startday.strftime("%y%m%d") sql = "SELECT OPTIME,CONSUMERID,PRESERVENUM,CURRENTNUM,DUBIOPTYPE,DUBIOPNUM,OPTYPE,OPDETAIL,OPNUM FROM `DUBIJIESUANTONGJI_%s`" % (strdate) print sql pddf = pd.read_sql(sql, con=mysql_cn) mysql_cn.close() print pddf.head(5) dflen = len(pddf.index) if dflen > 0: print pddf.describe() write("/home/haoren/logstatis/billdata"+strday+".parq", pddf) returndef saveBillData(): startday = datetime.date(2017, 2, 28) endday = datetime.date(2017, 2, 28) td = endday - startday datelen = td.days + 1 # 获取购物车数据 fromDayToDay(startday, datelen, saveDayBillData) # 获取Wifi注册数据def saveDayWifiPhoneRegData(startday, endday): #数据库连接参数 dbconfig = {'host':'172.26.2105', 'port': 3306, 'user':'haoren', 'passwd':'frewqasd', 'db':'OTHERDB', 'charset':'utf8'} #连接数据库,创建这个类的实例 mysql_cn= MySQLdb.connect(host=dbconfig['host'], port=dbconfig['port'],user=dbconfig['user'], passwd=dbconfig['passwd'], db=dbconfig['db']) strday = startday.strftime("%Y%m%d") tsstart=time.mktime(startday.timetuple()) tsend=time.mktime(endday.timetuple()) strdate = startday.strftime("%y%m%d") sql = "select USERID from NEW_WEB_USER where TIME print sql pddf = pd.read_sql(sql, con=mysql_cn) mysql_cn.close() print pddf.head(5) dflen = len(pddf.index) if dflen > 0: print pddf.describe() write("/home/haoren/logstatis/wifiphonereg"+strday+".parq", pddf) returndef saveWifiPhoneReg(): startday = datetime.date(2016, 12, 1) endday = datetime.date(2016, 12, 1) td = endday - startday datelen = td.days + 1 # 获取购物车数据 fromDayToDay(startday, datelen, saveDayWifiPhoneRegData)def getOpTypeName(func): name = OPTypeName.get(func) if name == None: return "" else: return name.decode('utf8') def getOpDetailName(func, detail): if func == 19: if detail > 10000 and detail return "购物车用物品扣人民币".decode('utf8') elif detail > 20000 and detail return "购物车回滚".decode('utf8') elif detail > 50000 and detail return "红包接龙".decode('utf8') else: name = OpDetailName19.get(detail) if name == None: return "" else: return name.decode('utf8') elif func == 22: name = OpDetailName22.get(detail) if name == None: return "" else: return name.decode('utf8') elif func == 23: name = OpDetailName23.get(detail) if name == None: return "" else: return name.decode('utf8') else: return ""def getDayBillData(startday, endday): global allBillData strday = startday.strftime("%Y%m%d") print strday + '人民币数据' df = spark.read.load("/home/haoren/logstatis/billdata"+strday+".parq") df.show(10) if allBillData == None: allBillData = df else: allBillData = allBillData.unionAll(df) return """ #df.createOrReplaceTempView('billdata') #df.registerTempTable("billdata") #sqlret = sqlc.sql("SELECT count(*) from billdata") #sqlret.show(1) df2 = df.withColumn('OPTYPENAME', udf(getOpTypeName)(df.OPTYPE)) df2.show(10) df = df2.withColumn('DETAILNAME', udf(getOpDetailName)(df2.OPTYPE, df2.OPDETAIL)) df.show(10) df.createOrReplaceTempView('billdata') return """ allBillData=Nonedef getBillData(): #startday = datetime.date(2016, 12, 1) #endday = datetime.date(2016, 12, 31) startday = datetime.date(2017, 2, 1) endday = datetime.date(2017, 2, 28) td = endday - startday datelen = td.days + 1 # 获取购物车数据 fromDayToDay(startday, datelen, getDayBillData) global allBillData allBillData.registerTempTable('billdata') """ #保存合并后的数据 strmonth = startday.strftime("%Y%m") allBillData.write.parquet("/home/haoren/logstatis/billdata"+strmonth+".parq") allBillData = spark.read.load("/home/haoren/logstatis/billdata"+strmonth+".parq") allBillData.registerTempTable("billdata") df=spark.sql("select OPTYPE,DUBIOPTYPE,OPDETAIL, sum(DUBIOPNUM) as sumnum from billdata group by OPTYPE,DUBIOPTYPE,OPDETAIL order by sumnum desc") df.show(5) df2 = df.withColumn('OPTYPENAME', udf(getOpTypeName)(df.OPTYPE)) df2.show(5) df = df2.withColumn('DETAILNAME', udf(getOpDetailName)(df2.OPTYPE, df2.OPDETAIL)) df.show(5) df.registerTempTable("billdatastatis") """ print 'getBillData finish'# 获取充值数据def getChargeInfo(startday, endday): #数据库连接参数 dbconfig = {'host':'172.26.2104', 'port': 3306, 'user':'haoren', 'passwd':'frewqasd', 'db':'lsdb', 'charset':'utf8'} #连接数据库,创建这个类的实例 mysql_cn= MySQLdb.connect(host=dbconfig['host'], port=dbconfig['port'],user=dbconfig['user'], passwd=dbconfig['passwd'], db=dbconfig['db']) strday = startday.strftime("%Y%m%d") tsstart=time.mktime(startday.timetuple()) tsend=time.mktime(endday.timetuple()) regdata = pd.DataFrame() for i in range(0, 20): sql = "SELECT * FROM `USERCONSUMPTIONRECORD%d` where TIME > %d AND TIME print sql #pddf = pd.DataFrame() pddf = pd.read_sql(sql, con=mysql_cn) #print pddf.head(5) if len(pddf.index) > 0: regdata = regdata.append(pddf,ignore_index=True) print regdata.tail(5) if len(regdata.index) > 0: print regdata.describe() write("/home/haoren/logstatis/register"+strday+".parq", regdata) mysql_cn.close() return def pudf(x): return getOpTypeName(x.OPTYPE) def getMergeData(strday): dfbill = ParquetFile("/home/haoren/logstatis/billdata"+strday+".parq").to_pandas() dfwifireg = ParquetFile("/home/haoren/logstatis/wifiphonereg"+strday+".parq").to_pandas() tempdf = pd.merge(dfbill, dfwifireg, left_on='CONSUMERID', right_on='USERID') #write("/home/haoren/logstatis/analyze"+strday+".parq", tempdf) #print tempdf.head(10) tempdf['OPTYPENAME'] = tempdf.apply(lambda x:getOpTypeName(x.OPTYPE), axis=1) #print tempdf.head(10) tempdf['DETAILNAME'] = tempdf.apply(lambda x:getOpDetailName(x.OPTYPE,x.OPDETAIL), axis=1) df = spark.createDataFrame(tempdf) df.show(10) return df def analyzeDayBillData(startday, endday): strday = startday.strftime("%Y%m%d") print strday + '人民币数据' df = spark.read.load("/home/haoren/logstatis/billdata"+strday+".parq") dfwifireg = spark.read.load("/home/haoren/logstatis/wifiphonereg"+strday+".parq") df3 = df.join(dfwifireg, df.CONSUMERID == dfwifireg.USERID) df3.show(10) df3.write.parquet("/home/haoren/logstatis/analyze"+strday+".parq") #df2 = df3.withColumn('OPTYPENAME', udf(getOpTypeName)(df3.OPTYPE)) #df2.show(10) #df = df2.withColumn('DETAILNAME', udf(getOpDetailName)(df2.OPTYPE, df2.OPDETAIL)) #df.show(10) #df.createOrReplaceTempView('analyzebilldata') return def analyzeDayBillData2(startday, endday): strday = startday.strftime("%Y%m%d") print strday + '人民币数据' #df = spark.read.load("/home/haoren/logstatis/analyze"+strday+".parq") df = getMergeData(strday) return df2 = df.withColumn('OPTYPENAME', udf(getOpTypeName)(df.OPTYPE)) df2.show(10) df = df2.withColumn('DETAILNAME', udf(getOpDetailName)(df2.OPTYPE, df2.OPDETAIL)) df.show(10) df.createOrReplaceTempView('analyzebilldata') return def analyzeBillData(): startday = datetime.date(2016, 12, 28) endday = datetime.date(2016, 12, 28) td = endday - startday datelen = td.days + 1 # 获取购物车数据 fromDayToDay(startday, datelen, analyzeDayBillData2) print 'analyzeBillData finish' #saveBillData()getBillData()#saveWifiPhoneReg()#analyzeBillData() 11-10 12:54