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