def stat(Db,
start_time,
end_time,
cursor=None,
Host="localhost",
User="******",
Passwd="123456"):
## 日期 用户ID 自己抽水 1级代理抽水 2级代理抽水 3级代理抽水
TJ_PUMP = {}
## 如果是传统代理模式 那就不用再跑数据了
sql = 'select val from admin_system_parameter where name = "agent_pattern" '
stype = base_stat.fetchall(cursor, sql)[0][0]
if int(stype) != 1:
return
sql = '''
select pid, sum(pump), sum(stake_coin - output_coin)
from %s
where time >= %d and time < %d
group by pid
''' % (base_stat.get_table_log_player_subgame(start_time), start_time,
end_time)
Result = base_stat.fetchall(cursor, sql)
for pid, pump, win in Result:
if pump == 0 and win == 0:
continue
if not TJ_PUMP.has_key(pid):
TJ_PUMP[pid] = init_player_agent(cursor, pid)
## 遍历该玩家的所有上级代理 给他的每个代理增加业绩
for agent_id, agent_coin in TJ_PUMP[pid]["agent"].items():
if not TJ_PUMP.has_key(agent_id):
TJ_PUMP[agent_id] = init_player_agent(cursor, agent_id)
TJ_PUMP[agent_id]["pump"] += pump
TJ_PUMP[agent_id]["win"] += win
tjdate = int(time.strftime("%Y%m%d", time.localtime(start_time)))
cursor.execute("delete from t_agent_day where time = %d" % tjdate)
for pid, data in TJ_PUMP.items():
sql = '''
insert into t_agent_day
(`time`, `pid`, `pump`, `win`)
values
(%d, %d, %d, %d)
''' % (tjdate, pid, data["pump"], data["win"])
cursor.execute(sql)
if cursor._result.warning_count > 0:
print "sql err...", sql
raise "sql err"
print "agent_stat succ：", time.strftime("%Y-%m-%d %H:%M:%S",
time.localtime(int(time.time())))
def stat(Db,
start_time,
end_time,
cursor=None,
Host="localhost",
User="******",
Passwd="123456"):
D = {}
sql = '''
select gameid, roomtype, count(1), sum(pump), sum(stake_coin), sum(output_coin)
from %s
where time >= %d and time < %d
group by gameid, roomtype
''' % (base_stat.get_table_log_subgame(start_time), start_time,
end_time)
tjdate = int(time.strftime("%Y%m%d", time.localtime(start_time)))
cursor.execute("delete from t_subgame where time = %d" % tjdate)
for gameid, roomtype, game_times, pump, stake_coin, \
output_coin in base_stat.fetchall(cursor, sql):
## 查询该游戏活跃人数
sql = '''
select count(distinct pid)
from %s
where gameid = %d
and time >= %d and time < %d
''' % (base_stat.get_table_log_player_subgame(start_time), gameid,
start_time, end_time)
print sql
active_count = base_stat.fetchall(cursor, sql)[0][0]
sql = '''
insert into t_subgame
(time, gameid, active_count, total_game_times, total_stake_coin,
total_output_coin, pump, roomtype)
values
(%d, %d, %d, %d, %d,
%d, %d, %d)
''' % (tjdate, gameid, active_count, game_times, stake_coin,
output_coin, pump, roomtype)
cursor.execute(sql)
if cursor._result.warning_count > 0:
print "sql err...", sql
raise "sql err"
print "subgame_stat succ：", time.strftime("%Y-%m-%d %H:%M:%S",
time.localtime(int(time.time())))
def check_player_stake_coin(cursor, alarm_val, last_check_time):
tab = base_stat.get_table_log_player_subgame(last_check_time)
sql = '''
select pid, gameid, time, stake_coin, (select ifnull(max(auto_id), 0) from %s)
from %s a
where time >= %d
and stake_coin >= %d
and auto_id > (select ifnull(max(relate_id), 0) from admin_game_alarm where stype = %d)
''' % (tab, tab, last_check_time, alarm_val, ALARM_PLAYER_STAKE_COIN)
for pid, gameid, time, stake_coin, auto_id in base_stat.fetchall(
cursor, sql):
if not pid:
continue
sql = '''
insert into admin_game_alarm (id, pid, time, stype, val, state, relate_id)
values (0, %d, %d, %d, '%s', %d, %d)
''' % (pid, time, ALARM_PLAYER_STAKE_COIN,
join([gameid, stake_coin, alarm_val]), STATE_UNREAD, auto_id)
cursor.execute(sql)
def check_player_output_coin_today(cursor, alarm_val, last_check_time):
sql = '''
select pid, val
from
(select pid, sum(output_coin - stake_coin) as val
from %s
where time >= %d
and pid not in
(select distinct pid from admin_game_alarm where time >= %d and stype == %d)
group by pid) t
where val >= %d
''' % (base_stat.get_table_log_player_subgame(
now_sec()), base_stat.today0(), base_stat.today0(),
ALARM_PLAYER_OUTPUT_COIN_TODAY, alarm_val)
for pid, win_coin in base_stat.fetchall(cursor, sql):
sql = '''
insert into admin_game_alarm (id, pid, time, stype, val, state, relate_id)
values (0, %d, %d, %d, '%s', %d, 0)
''' % (pid, time, ALARM_PLAYER_OUTPUT_COIN_TODAY,
join([win_coin, alarm_val]), STATE_UNREAD)
cursor.execute(sql)
def stat(Db, start_time, end_time, cursor=None, Host="localhost", User="******", Passwd="123456"):
# 日期 平台
# 注册人数 活跃人数 登陆次数 充值人数 大额充值次数
# 日充值人数 新增充值人数 今日总充值 大额充值总额 充值产出金币
# 今日提现 破产人数 破产次数 游戏总盈利 游戏总抽水
# 日人均线时长 最高在线人数 赠送次数 赠送总金币 赠送总抽水
# 赠送人数 收到赠送人数 2日留存 3日留存 7日留存
# 15日留存 30日留存
D = {}
##点击数统计
# d_click = get_click_num(Db)
# up_data(D, 1, "url_click", d_click["android"])
# up_data(D, 2, "url_click", d_click["ios"])
up_data(D, 1, "url_click", 0)
up_data(D, 2, "url_click", 0)
# 注册人数
regCountSql = '''
select os, count(1)
from log_role_reg
where time >= %d and time < %d
group by os
''' % (start_time, end_time)
Result = base_stat.fetchall(cursor, regCountSql)
for os, num in Result:
up_data(D, os, "reg_count", num)
## 新增设备数
sql = '''
select ifnull(sum(if(device = "ios", 1, 0)), 0), ifnull(sum(if(device = "ios", 0, 1)), 0)
from player
where reg_time >= %d and reg_time < %d
and did not in
(select distinct did from player where reg_time < %d)
''' % (start_time, end_time, start_time)
ios, android = base_stat.fetchall(cursor, sql)[0]
up_data(D, 2, "new_device", ios)
up_data(D, 1, "new_device", android)
# 活跃人数 登录次数统计
activeCountSql = '''
select os, count(distinct pid), count(pid)
from log_account_login
where time >= %d and time < %d
group by os
''' % (start_time, end_time)
Result = base_stat.fetchall(cursor, activeCountSql)
for os, activenum, loginnum in Result:
up_data(D, os, "active_count", activenum)
up_data(D, os, "login_count", loginnum)
## 游戏活跃人数统计
sql = '''
select count(distinct pid)
from %s
where time >= %d and time < %d
''' % (base_stat.get_table_log_player_subgame(start_time), start_time, end_time)
Result = base_stat.fetchall(cursor, sql)
for count, in Result:
up_data(D, 1, "game_active_count", int(count))
up_data(D, 2, "game_active_count", 0)
# todo 日破产人数 日破产次数
## 提现总额
sql = '''
select ifnull(sum(withdraw_deposit_money), 0), count(distinct pid)
from admin_withdraw
where application_time >= %d and application_time < %d
and status = 1
''' % (start_time, end_time)
Result = base_stat.fetchall(cursor, sql)
for total, count in Result:
up_data(D, 1, "withdraw_count", int(count))
up_data(D, 1, "withdraw", int(total))
up_data(D, 2, "withdraw_count", 0)
up_data(D, 2, "withdraw", 0)
## 赠送
sql = '''
select if(give_agent = 0, 'p', 'a'), if(recv_agent = 0, 'p', 'a'), count(1),
ifnull(sum(money), 0), ifnull(sum(pump), 0),
count(distinct give_id), count(distinct recv_id)
from log_bank_give
where time >= %d and time < %d
group by give_agent, recv_agent
''' % (start_time, end_time)
Result = base_stat.fetchall(cursor, sql)
for give_agent, recv_agent, give_times, give_coin, give_pump, \
give_player_num, recv_coin_player_num in Result:
Data = {
"give_times":int(give_times), "give_coin":int(give_coin),
"give_pump":int(give_pump), "give_player_num":int(give_player_num),
"recv_coin_player_num":int(recv_coin_player_num)
}
up_data(D, 1, "give_coin_%s2%s" % (give_agent, recv_agent), json.dumps(Data))
# 游戏抽水
gameWinSql = '''
select ifnull(sum(pump), 0)
from %s
where time >= %d and time < %d
''' % (base_stat.get_table_log_subgame(start_time), start_time, end_time)
Result = base_stat.fetchall(cursor, gameWinSql)
for count, in Result:
up_data(D, 1, "pump", int(count))
up_data(D, 2, "pump", 0)
## 赠送抽水
sql = '''
select ifnull(sum(pump), 0)
from log_bank_give
where time >= %d and time < %d
''' % (start_time, end_time)
Result = base_stat.fetchall(cursor, sql)
for count, in Result:
up_data(D, 1, "give_pump", int(count))
## 新增充值人数
newRechargeSql = '''
select os, count(distinct pid)
from admin_recharge
where time >= %d and time < %d
and state = 1
and pid not in
(
select distinct pid
from admin_recharge
where time < %d
and state = 1
)
group by os
''' % (start_time, end_time, start_time)
Result = base_stat.fetchall(cursor, regCountSql)
for os, num in Result:
up_data(D, os, "new_recharge_count", num)
# 大额充值金额
rechargeSql = '''
select os, count(1), sum(cost), sum(coin), count(distinct pid),
sum(if(cost > %d, 1, 0)), sum(if(cost > %d, cost, 0))
from admin_recharge
where time >= %d and time < %d
and state = 1
group by os
''' % (BIGGER_RECHARGE, BIGGER_RECHARGE, start_time, end_time)
Result = base_stat.fetchall(cursor, rechargeSql)
for os, rechargenum, rechargemoney, rechargecoin, recharge_player_num, \
bigger_times, bigger_recharge in Result:
up_data(D, os, "recharge_count", rechargenum)
up_data(D, os, "total_recharge", rechargemoney)
up_data(D, os, "recharge_coin", rechargecoin)
up_data(D, os, "recharge_player_count", recharge_player_num)
up_data(D, os, "bigger_recharge_count", bigger_times)
up_data(D, os, "bigger_recharge", bigger_recharge)
## 当日注册充值情况统计
recharge_count_reg = 0
total_recharge_reg = 0
recharge_count2_reg = 0
sql = '''
select pid, sum(cost), count(1)
from admin_recharge a, player p
where time >= %d and time < %d
and state = 1
and a.pid = p.id
and p.reg_time >= %d
''' % (start_time, end_time, start_time)
for pid, cost, num in base_stat.fetchall(cursor, sql):
if not pid:
continue
recharge_count_reg += 1
total_recharge_reg += cost
if num > 1:
recharge_count2_reg += 1
up_data(D, 1, "recharge_count_reg", recharge_count_reg)
up_data(D, 1, "total_recharge_reg", total_recharge_reg)
up_data(D, 1, "recharge_count2_reg", recharge_count2_reg)
up_data(D, 2, "recharge_count_reg", 0)
up_data(D, 2, "total_recharge_reg", 0)
up_data(D, 2, "recharge_count2_reg", 0)
# 游戏总盈亏
gameWinSql = '''
select os, -sum(val)
from %s
where time >= %d and time < %d
and log_type >= 80000
group by os
''' % (base_stat.get_table_log_coin(start_time), start_time, end_time)
Result = base_stat.fetchall(cursor, gameWinSql)
for os, count in Result:
up_data(D, os, "game_win", int(count))
# 2日留存 3日留存 7日留存 15日留存 30日留存
Day2Sql = '''
select os, count(distinct pid)
from log_account_login
where time >= %d and time < %d
and pid in
(select distinct pid
from log_role_reg
where time >= %d and time < %d)
group by os
''' % (start_time, end_time, start_time - 86400, start_time)
Result = base_stat.fetchall(cursor, Day2Sql)
for os, count in Result:
up_data(D, os, "day2", count)
Day3Sql = '''
select os, count(distinct pid)
from log_account_login
where time >= %d and time < %d
and pid in
(select distinct pid
from log_role_reg
where time >= %d and time < %d)
group by os
''' % (start_time, end_time, start_time - 2 * 86400, start_time - 86400)
Result = base_stat.fetchall(cursor, Day3Sql)
for os, count in Result:
up_data(D, os, "day3", count)
Day7Sql = '''
select os, count(distinct pid)
from log_account_login
where time >= %d and time < %d
and pid in
(select distinct pid
from log_role_reg
where time >= %d and time < %d)
group by os
''' % (start_time, end_time, start_time - 6 * 86400, start_time - 86400 * 5)
Result = base_stat.fetchall(cursor, Day7Sql)
for os, count in Result:
up_data(D, os, "day7", count)
Day15Sql = '''
select os, count(distinct pid)
from log_account_login
where time >= %d and time < %d
and pid in
(select distinct pid
from log_role_reg
where time >= %d and time < %d)
group by os
''' % (start_time, end_time, start_time - 14 * 86400, start_time - 86400 * 13)
Result = base_stat.fetchall(cursor, Day15Sql)
for os, count in Result:
up_data(D, os, "day15", count)
Day30Sql = '''
select os, count(distinct pid)
from log_account_login
where time >= %d and time < %d
and pid in
(select distinct pid
from log_role_reg
where time >= %d and time < %d)
group by os
''' % (start_time, end_time, start_time - 29 * 86400, start_time - 86400 * 28)
Result = base_stat.fetchall(cursor, Day30Sql)
for os, count in Result:
up_data(D, os, "day30", count)
#日人均线时长
onlineTimeSql = '''
select os, sum(online_time)
from log_account_login
where time >= %d and time < %d
and opt = "account_logout"
group by os
''' % (start_time, end_time)
Result = base_stat.fetchall(cursor, onlineTimeSql)
for os, totaltime in Result:
up_data(D, os, "total_online_time", int(totaltime))
# 最高在线人数
onlineMaxSql = '''
select os, max(num)
from log_online
where time >= %d and time < %d
group by os
''' % (start_time, end_time)
Result = base_stat.fetchall(cursor, onlineMaxSql)
for os, max_player_num in Result:
up_data(D, os, "max_online_num", max_player_num)
tjdate = int(time.strftime("%Y%m%d", time.localtime(start_time)))
cursor.execute("delete from t_system where time = %d" % tjdate)
for os, v in D.items():
v["time"] = tjdate
v["os"] = os
sql = '''
replace into t_system
(`time`, `os`, `reg_count`, `active_count`, `login_count`,
`recharge_count`, `bigger_recharge_count`, `recharge_player_count`,
`new_recharge_count`, `total_recharge`,
`bigger_recharge`, `recharge_coin`, `withdraw`, `bankrupt_player_count`, `bankrupt_count`,
`game_win`, `pump`, `total_online_time`, `max_online_num`, `give_coin_a2a`,
`give_coin_a2p`, `give_coin_p2a`, `give_coin_p2p`, `day2`, `day3`,
`day7`, `day15`, `day30`, `give_pump`, `game_active_count`,
`new_device`, `url_click`, `withdraw_count`, `recharge_count_reg`,`total_recharge_reg`,
`recharge_count2_reg`)
values
(%(time)d, %(os)d, %(reg_count)d, %(active_count)d, %(login_count)d,
%(recharge_count)d, %(bigger_recharge_count)d, %(recharge_player_count)d,
%(new_recharge_count)d, %(total_recharge)d,
%(bigger_recharge)d, %(recharge_coin)d, %(withdraw)d,
%(bankrupt_player_count)d, %(bankrupt_count)d,
%(game_win)d, %(pump)d, %(total_online_time)d, %(max_online_num)d, '%(give_coin_a2a)s',
'%(give_coin_a2p)s', '%(give_coin_p2a)s', '%(give_coin_p2p)s', %(day2)d, %(day3)d,
%(day7)d, %(day15)d, %(day30)d, %(give_pump)d, %(game_active_count)d,
%(new_device)d, %(url_click)d, %(withdraw_count)d, %(recharge_count_reg)d, %(total_recharge_reg)d,
%(recharge_count2_reg)d)
''' % v
print sql
cursor.execute(sql)
if cursor._result.warning_count > 0:
print "sql err...", sql
raise "sql err"
print "game_system succ:", time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(int(time.time())))
def stat(Db,
start_time,
end_time,
cursor=None,
Host="localhost",
User="******",
Passwd="123456"):
## 日期 用户ID 自己抽水 1级代理抽水 2级代理抽水 3级代理抽水
TJ_PUMP = {}
## 如果是传统代理模式 那就不用再跑数据了
sql = 'select val from admin_system_parameter where name = "agent_pattern" '
stype = base_stat.fetchall(cursor, sql)[0][0]
if int(stype) != 2:
return
## 查询有多少级返利
sql = 'select commission_section from admin_distribution_config'
commission_len = len(json.loads(base_stat.fetchall(cursor, sql)[0][0]))
sql = '''
select pid, sum(pump), sum(stake_coin - output_coin)
from %s
where time >= %d and time < %d
group by pid
''' % (base_stat.get_table_log_player_subgame(start_time), start_time,
end_time)
Result = base_stat.fetchall(cursor, sql)
for pid, pump, win in Result:
pump = int(pump)
win = int(win)
if pump == 0 and win == 0:
continue
if not TJ_PUMP.has_key(pid):
TJ_PUMP[pid] = init_player_data(cursor, pid, commission_len)
## 遍历该玩家可以享受分佣的上级代理 给该玩家的各个上级加上该玩家的业绩
for k, pre_pid in TJ_PUMP[pid]["agent"].items():
## 判断是否已经有该玩家上线的数据 如果没有则初始化该上线玩家的数据
if not TJ_PUMP.has_key(pre_pid):
TJ_PUMP[pre_pid] = init_player_data(cursor, pre_pid,
commission_len)
## 给该上线玩家加上该玩家的贡献度
TJ_PUMP[pre_pid]["pump_detail"][k][pid] = pump
TJ_PUMP[pre_pid]["win_detail"][k][pid] = win
TJ_PUMP[pre_pid]["pump"][k] += pump
TJ_PUMP[pre_pid]["win"][k] += win
tjdate = int(time.strftime("%Y%m%d", time.localtime(start_time)))
cursor.execute("delete from t_distribution_day where time = %d" % tjdate)
for pid, data in TJ_PUMP.items():
if pid == 0:
continue
sql = '''
insert into t_distribution_day
(`time`, `pid`, `pump`, `pump_detail`, `win`,
`win_detail`)
values
(%d, %d, '%s', '%s', '%s', '%s')
''' % (tjdate, pid, json.dumps(data["pump"]),
json.dumps(data["pump_detail"]), json.dumps(
data["win"]), json.dumps(data["win_detail"]))
cursor.execute(sql)
if cursor._result.warning_count > 0:
print "sql err...", sql
raise "sql err"
print "player_pump_stat succ：", time.strftime(
"%Y-%m-%d %H:%M:%S", time.localtime(int(time.time())))