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