def games_rank_profit_json():
"""返回盈利亏损json数据"""
# 获取参数
start_time = request.args.get('start_time')
end_time = request.args.get('end_time')
channel_id = session['select_channel']
# 处理参数
start_time2 = time_util.formatDatestamp(start_time)
end_time2 = time_util.formatDatestamp(end_time) + 86400
start_time = start_time.replace('-', '')
end_time = end_time.replace('-', '')
channel_id = int(channel_id)
# 获取数据
if start_time2 == time_util.today0():
retrieve_sql = '''
SELECT (SELECT nick FROM player WHERE pid=id),sum(stake_coin),(sum(output_coin)-sum(stake_coin)),pid
FROM %s
WHERE time>=%s
GROUP BY pid
ORDER BY (sum(output_coin)-sum(stake_coin)) DESC
LIMIT 100;
''' % (get_table_log_player_subgame(
time_util.now_sec()), time_util.today0())
else:
retrieve_sql = """SELECT (SELECT nick FROM player WHERE pid=id),sum(stake_coin),(sum(output_coin)-sum(stake_coin)),pid
FROM t_player_subgame
WHERE time>=%s
AND time<=%s
GROUP BY pid
ORDER BY (sum(output_coin)-sum(stake_coin)) DESC
LIMIT 100;""" \
% (start_time, end_time)
data = LogQry(int(channel_id)).qry(retrieve_sql)
# 处理数据
data_list = list()
for nick, stake_coin, total_win, pid in data:
data_dict = dict()
data_dict['nick'] = nick
data_dict['stake_coin'] = int(stake_coin)
data_dict['total_win'] = int(total_win)
data_dict['pid'] = pid
data_list.append(data_dict)
# 返回数据
return jsonify(data=data_list)
def search_daily_presentation_today():
present_type = int(request.args.get('present_type'))
channel = session['select_channel']
start = request.args.get('beginDate')
today0 = time_util.today0()
pre_line = dict()
pre_line['date_text'] = time_util.formatDate(today0)
# 注册人数
sql = '''
select count(1)
from log_role_reg
where time >= %d
''' % (today0)
pre_line['reg_count'] = LogQry(channel).qry(sql)[0][0]
# 活跃人数
sql = '''
select count(distinct pid)
from log_account_login
where time >= %d
''' % (today0)
pre_line['active_count'] = LogQry(channel).qry(sql)[0][0]
pre_line['bankrupt_player_count'] = 0
pre_line['bankrupt_count'] = 0
## 查询赠送情况
sql0 = '''
select 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
%s
'''
if present_type == 0:
sql = sql0 % (today0, "")
elif present_type == 1:
sql = sql0 % (today0, "and give_agent = 0 and recv_agent = 0")
elif present_type == 2:
sql = sql0 % (today0, "and give_agent = 0 and recv_agent = 1")
elif present_type == 3:
sql = sql0 % (today0, "and give_agent = 1 and recv_agent = 1")
elif present_type == 4:
sql = sql0 % (today0, "and give_agent = 1 and recv_agent = 0")
[total_give_times, total_give_coin, total_give_pump, total_give_player_num, total_recv_player_num] = \
LogQry(channel).qry(sql)[0]
pre_line['money'] = float(total_give_coin)
pre_line['pump'] = float(total_give_pump)
pre_line['give_count'] = total_give_player_num
pre_line['recv_count'] = total_recv_player_num
pre_line['present_count'] = total_give_times
pre_line['averge_presentation'] = 0
if total_give_times > 0:
pre_line['averge_presentation'] = total_give_coin / total_give_times
return jsonify(result='ok', data=[pre_line])
def get_today_topup():
channel_id = session['select_channel']
today0 = time_util.today0()
pre_line = {}
pre_line['date_text'] = time_util.formatDate(today0)
# 注册人数
sql = '''
select count(1)
from log_role_reg
where time >= %d
''' % (today0)
pre_line['reg_count'] = LogQry(channel_id).qry(sql)[0][0]
# 活跃人数
sql = '''
select count(distinct pid)
from log_account_login
where time >= %d
''' % (today0)
pre_line['active_count'] = LogQry(channel_id).qry(sql)[0][0]
# 充值相关
sql = '''
select count(1), ifnull(sum(cost), 0), count(distinct pid)
from admin_recharge
where time >= %d
and state = 1
''' % (today0)
Result = LogQry(channel_id).qry(sql)[0]
pre_line['recharge_count'] = Result[0]
pre_line['total_recharge'] = float(Result[1])
pre_line['recharge_player_num'] = Result[2]
## 当日新增充值情况
pre_line['recharge_count_reg'] = 0
pre_line['total_recharge_reg'] = 0
pre_line['recharge_count2_reg'] = 0
sql = '''
select pid, sum(cost), count(1)
from admin_recharge a, player p
where time >= %d
and state = 1
and a.pid = p.id
and p.reg_time >= %d
''' % (today0, today0)
for pid, cost, num in LogQry(channel_id).qry(sql):
if not pid:
continue
## 当日注册充值人数
pre_line['recharge_count_reg'] += 1
## 当日注册充值总额
pre_line['total_recharge_reg'] += cost
## 当日注册多次充值人数
if num > 1:
pre_line['recharge_count2_reg'] += 1
return jsonify(result='ok', data=[pre_line])
def search_profit_loss_rank():
start = request.args.get('beginDate')
end = request.args.get('endDate')
game = request.args.get('game')
sort = request.args.get('profit_loss')
channel_id = session['select_channel']
if game == '0':
game_str = ''
else:
game_str = ' AND t_player_subgame.gameid=%s' % game
start_date = time_util.formatDatestamp(start)
end_date = time_util.formatDatestamp(end)
today0 = time_util.today0()
if start_date == today0:
return search_profit_loss_rank_today()
if start_date > end_date:
return jsonify(result='failed', msg=u"终止日期不能小于起始日期！")
start_date = int(time_util.formatTimeWithDesc(start_date, "%Y%m%d"))
end_date = int(time_util.formatTimeWithDesc(end_date + 86400, "%Y%m%d"))
sort_str = ''
if sort == '0':
sort_str = "DESC"
elif sort == '1':
sort_str = "ASC"
search_sql = """SELECT * FROM (SELECT pid, (SELECT nick FROM player WHERE pid=id), (SELECT last_login_ip FROM player WHERE pid=id),
sum(game_count), sum(stake_coin), sum(output_coin), (sum(output_coin) - sum(stake_coin)) total_win
FROM t_player_subgame
WHERE time>=%d
AND time<%d%s
GROUP BY pid ORDER BY time DESC) t
ORDER BY total_win %s LIMIT 100""" \
% (start_date, end_date, game_str, sort_str)
game_db_qrl = LogQry(int(channel_id))
alltime_search_datas = game_db_qrl.qry(search_sql)
player_rank_list = []
i = 1 # 用于记录排名
for pid, nick, last_login_ip, game_count, stake_coin, output_coin, total_win in alltime_search_datas:
player_dict = dict()
player_dict['pid'] = pid
player_dict['nick'] = nick
player_dict['last_login_ip'] = last_login_ip
player_dict['game_count'] = float(game_count)
player_dict['stake_coin'] = float(stake_coin)
player_dict['output_coin'] = float(output_coin)
player_dict['total_win'] = float(total_win)
player_dict['rank'] = i
player_rank_list.append(player_dict)
i += 1
return jsonify(result='ok', data=player_rank_list)
def subgame_data_json():
"""返回子游戏数据json数据"""
# 获取参数
start_time = request.args.get('start_time')
end_time = request.args.get('end_time')
channel_id = session['select_channel']
# 处理参数
start_time2 = time_util.formatDatestamp(start_time)
end_time2 = time_util.formatDatestamp(end_time) + 86400
start_time = start_time.replace('-', '')
end_time = end_time.replace('-', '')
channel_id = int(channel_id)
if start_time2 == time_util.today0():
retrieve_sql = '''
select gameid, sum(stake_coin), sum(output_coin)
from %s
where time >= %d
group by gameid
''' % (get_table_log_subgame(time_util.now_sec()), time_util.today0())
else:
# 从数据库获取数据
retrieve_sql = """
SELECT gameid, sum(total_stake_coin), sum(total_output_coin)
FROM t_subgame
WHERE time>=%s
AND time<=%s
GROUP BY gameid;
""" % (start_time, end_time)
data = LogQry(channel_id).qry(retrieve_sql)
# 处理数据
data_list = list()
SubGameList = game_parameter.get_subgame_list()
for one in data:
data_dict = dict()
data_dict['game_name'] = game_parameter.get_subgame_by_id(SubGameList, one[0])
data_dict['total_stake_coin'] = int(one[1])
data_dict['ai_win'] = int(one[1] - one[2])
data_list.append(data_dict)
# 返回数据
return jsonify(data=data_list)
def search_daily_topup():
start = request.args.get('beginDate', '')
end = request.args.get('endDate', '')
start_date = time_util.formatDatestamp(start)
end_date = time_util.formatDatestamp(end)
today0 = time_util.today0()
if start_date == today0:
return get_today_topup()
channel_id = session['select_channel']
if start_date > end_date:
return jsonify(result='fail', msg=u'结束日期不能小于开始日期！')
start_date = int(time_util.formatTimeWithDesc(start_date, "%Y%m%d"))
end_date = int(time_util.formatTimeWithDesc(end_date, "%Y%m%d"))
search_sql = '''
SELECT time, ifnull(sum(reg_count), 0), ifnull(sum(active_count), 0),
ifnull(sum(recharge_player_count), 0), ifnull(sum(recharge_count), 0),
ifnull(sum(total_recharge), 0), ifnull(sum(withdraw), 0), ifnull(sum(withdraw_count), 0),
ifnull(sum(recharge_count_reg), 0), ifnull(sum(total_recharge_reg), 0),
ifnull(sum(recharge_count2_reg), 0)
FROM t_system
WHERE time>=%d
AND time<=%d
group by time
ORDER BY time;
''' % (start_date, end_date)
game_db_qrl = LogQry(int(channel_id))
print search_sql
alltime_search_datas = game_db_qrl.qry(search_sql)
# 最终展示的数据列表
allday_datas = []
for line in alltime_search_datas:
if not line[0]:
continue
pre_line = dict()
pre_line['date_text'] = time_util.date_str(line[0])
pre_line['reg_count'] = int(line[1])
pre_line['active_count'] = int(line[2])
pre_line['recharge_player_num'] = int(line[3])
pre_line['recharge_count'] = int(line[4])
pre_line['total_recharge'] = int(line[5])
pre_line['withdraw'] = int(line[6])
pre_line['withdraw_count'] = int(line[7])
pre_line['recharge_count_reg'] = int(line[8])
pre_line['total_recharge_reg'] = int(line[9])
pre_line['recharge_count2_reg'] = int(line[10])
allday_datas.append(pre_line)
return jsonify(result='ok', data=allday_datas)
def search_profit_loss_rank_today():
game = request.args.get('game')
sort = request.args.get('profit_loss')
channel_id = session['select_channel']
today0 = time_util.today0()
if game == '0':
game_str = ''
else:
game_str = ' AND gameid=%s' % game
sort_str = ''
if sort == '0':
sort_str = "DESC"
elif sort == '1':
sort_str = "ASC"
search_sql = """
SELECT * FROM
(SELECT pid, (SELECT nick FROM player WHERE pid=id), (SELECT last_login_ip FROM player WHERE pid=id),
count(1), sum(stake_coin), sum(output_coin), (sum(output_coin) - sum(stake_coin)) total_win
FROM %s
WHERE time>=%d
%s
GROUP BY pid
ORDER BY time DESC) t
ORDER BY total_win %s
LIMIT 100
""" % (get_table_log_player_subgame(today0), today0, game_str,
sort_str)
game_db_qrl = LogQry(int(channel_id))
alltime_search_datas = game_db_qrl.qry(search_sql)
player_rank_list = []
i = 1 # 用于记录排名
for pid, nick, last_login_ip, game_count, stake_coin, output_coin, total_win in alltime_search_datas:
player_dict = dict()
player_dict['pid'] = pid
player_dict['nick'] = nick
player_dict['last_login_ip'] = last_login_ip
player_dict['game_count'] = float(game_count)
player_dict['stake_coin'] = float(stake_coin)
player_dict['output_coin'] = float(output_coin)
player_dict['total_win'] = float(total_win)
player_dict['rank'] = i
player_rank_list.append(player_dict)
i += 1
return jsonify(result='ok', data=player_rank_list)
def search_subgame_data():
start = request.args.get('beginDate', '')
end = request.args.get('endDate', '')
channel = session['select_channel']
start_date = time_util.formatDatestamp(start)
end_date = time_util.formatDatestamp(end)
today0 = time_util.today0()
if start_date == today0:
return search_subgame_data_today()
if start_date > end_date:
return jsonify(result='fail', msg=u'结束日期不能大于开始日期！')
start_date = int(time_util.formatTimeWithDesc(start_date, "%Y%m%d"))
end_date = int(time_util.formatTimeWithDesc(end_date, "%Y%m%d"))
search_sql = """
select gameid, roomtype, sum(total_game_times), sum(total_stake_coin), sum(total_output_coin),
sum(pump)
FROM t_subgame
WHERE time>=%d AND time<=%d
group by gameid, roomtype
""" % (start_date, end_date)
allgame_search_datas = []
SubGameList = game_parameter.get_subgame_list()
room_define = game_parameter.room_define()
for search_data in LogQry(channel).qry(search_sql):
try:
roomname = room_define[search_data[0]][search_data[1]]
except:
roomname = search_data[1]
pre_record = {
'gameid': search_data[0],
'game_name': game_parameter.get_subgame_by_id(SubGameList, search_data[0]),
'roomid': roomname,
'active_count': 0,
'total_game_times': float(search_data[2]),
'total_stake_coin': float(search_data[3]),
'total_get_coin': float(search_data[4]),
'win_player_num': 0,
'lose_player_num': 0,
'total_win': 0,
'total_lose': 0,
'average_game_count': 0,
'pump': float(search_data[5]),
'ai_win': float(search_data[3] - search_data[4])
}
## 查询这段时间玩家在该游戏里边的总体输赢情况
sql = '''
select pid, sum(stake_coin), sum(output_coin)
from t_player_subgame
where time>=%d AND time<=%d
and gameid = %d
and roomtype = %d
group by pid
''' % (start_date, end_date, search_data[0], search_data[1])
for line in LogQry(channel).qry(sql):
pre_record["active_count"] += 1
if line[1] > line[2]:
pre_record["lose_player_num"] += 1
pre_record["total_lose"] += (line[2] - line[1])
else:
pre_record["win_player_num"] += 1
pre_record["total_win"] += (line[2] - line[1])
if pre_record["active_count"] > 0:
pre_record['average_game_count'] = int(search_data[2] / pre_record["active_count"])
pre_record["total_lose"] = float(pre_record["total_lose"])
pre_record["total_win"] = float(pre_record["total_win"])
allgame_search_datas.append(pre_record)
return jsonify(result='ok', data=allgame_search_datas)
def search_daily_data():
## 计算留存
def calc_liucun(d, t, reg_num, offset):
nt = time_util.date_add(t, offset - 1)
if nt in d and reg_num > 0:
return int(d[nt][offset] * 100 / reg_num)
return 0
start = request.args.get('beginDate', '')
end = request.args.get('endDate', '')
channel = session['select_channel']
start_date = time_util.formatDatestamp(start)
end_date = time_util.formatDatestamp(end)
today0 = time_util.today0()
if start_date == today0 and end_date == today0:
return search_daily_data_today()
if start_date > end_date:
return jsonify(result='fail', msg=u'结束时间不能小于开始时间！')
start_date = int(time_util.formatTimeWithDesc(start_date, "%Y%m%d"))
end_date = int(time_util.formatTimeWithDesc(end_date, "%Y%m%d"))
sql = '''
select time, sum(day2), sum(day3), sum(day7), sum(day15), sum(day30)
FROM t_system
group by time;
'''
reg_count_d = {}
for time, day2, day3, day7, day15, day30 in LogQry(channel).qry(sql):
reg_count_d[time] = {2: float(day2), 3: float(day3), 7: float(day7), 15: float(day15), 30: float(day30)}
search_sql = """
SELECT time, sum(reg_count), sum(active_count), sum(login_count), sum(recharge_count),
sum(bankrupt_player_count), sum(bankrupt_count), sum(total_recharge), sum(game_win), sum(pump),
sum(recharge_coin), sum(withdraw), sum(day2), sum(day3), sum(day7),
sum(day15), sum(day30), sum(total_online_time), sum(max_online_num), sum(give_pump),
sum(new_device), sum(game_active_count), give_coin_a2p, give_coin_p2a
FROM t_system
WHERE time>=%d AND time<=%d
group by time;
""" % (start_date, end_date)
allday_search_data = []
pre_date = 0
pre_os = 0
pre_record = {}
for search_data in LogQry(channel).qry(search_sql):
pre_record = dict()
pre_record['date_text'] = time_util.date_str(search_data[0])
pre_record['reg_count'] = float(search_data[1])
pre_record['active_count'] = float(search_data[2])
pre_record['login_count'] = float(search_data[3])
pre_record['recharge_count'] = float(search_data[4])
pre_record['bankrupt_player_count'] = float(search_data[5])
pre_record['bankrupt_count'] = float(search_data[6])
pre_record['total_recharge'] = float(search_data[7])
pre_record['game_win'] = float(search_data[8])
pre_record['pump'] = float(search_data[9] + search_data[19])
pre_record['recharge_coin'] = float(search_data[10])
pre_record['withdraw'] = float(search_data[11])
pre_record['day2'] = calc_liucun(reg_count_d, search_data[0], float(search_data[1]), 2)
pre_record['day3'] = calc_liucun(reg_count_d, search_data[0], float(search_data[1]), 3)
pre_record['day7'] = calc_liucun(reg_count_d, search_data[0], float(search_data[1]), 7)
pre_record['day15'] = calc_liucun(reg_count_d, search_data[0], float(search_data[1]), 15)
pre_record['day30'] = calc_liucun(reg_count_d, search_data[0], float(search_data[1]), 30)
pre_record['avg_online_time'] = 0
if search_data[2] > 0:
pre_record['avg_online_time'] = int(search_data[17] / search_data[2])
pre_record['max_online_num'] = float(search_data[18])
pre_record['active_arpu'] = 0
pre_record['active_arppu'] = 0
if search_data[2] > 0:
pre_record['active_arpu'] = pre_record['total_recharge'] / float(search_data[2])
if search_data[4] > 0:
pre_record['active_arpu'] = pre_record['total_recharge'] / float(search_data[4])
pre_record["new_device"] = float(search_data[20])
pre_record["game_active_count"] = float(search_data[21])
pre_record["give_coin_a2p"] = search_data[22]
pre_record["give_coin_p2a"] = search_data[23]
allday_search_data.append(pre_record)
return jsonify(result='ok', data=allday_search_data)
def rt():
channel = session['select_channel']
today0 = time_util.today0()
## 今日充值
sql = '''
select ifnull(sum(cost), 0)
from admin_recharge
where time >= %d and state = 1
''' % today0
recharge = LogQry(channel).qry(sql)[0][0]
## todo 今日提现
withdraw = 0
## 在线人数
onlinenum = GameWeb(channel).post("/api/online_num", {})['result']
## 注册人数
sql = '''
select count(1)
from log_role_reg
where time >= %d
''' % today0
reg_count = LogQry(channel).qry(sql)[0][0]
## 活跃人数
sql = '''
select count(distinct pid)
from log_account_login
where time >= %d
''' % today0
active_count = LogQry(channel).qry(sql)[0][0]
## ##玩家总押注 玩家总产出 全服总税收
sql = '''
select ifnull(sum(pump), 0), ifnull(sum(stake_coin), 0), ifnull(sum(output_coin), 0)
from %s
where time >= %d
''' % (get_table_log_subgame(today0), today0)
(pump1, stake, output) = LogQry(channel).qry(sql)[0]
sql = '''
select ifnull(sum(pump), 0)
from log_bank_give
where time >= %d
''' % today0
pump2 = LogQry(channel).qry(sql)[0][0]
pump = pump1 + pump2
## 查询代理
sql = '''
select pid
from admin_agent_list
'''
agentlist = [x[0] for x in LogQry(channel).qry(sql)]
## 统计玩家身上金币 银行金币
sql = 'select ifnull(sum(coin + lottery), 0) from player'
total_coin = int(LogQry(channel).qry(sql)[0][0])
sql = 'select ifnull(sum(coin + lottery), 0) from player where id in (select pid from admin_agent_list) '
agent_coin = int(LogQry(channel).qry(sql)[0][0])
player_coin = total_coin - agent_coin
## 查询代理卖分
sql = '''
select ifnull(sum(money), 0)
from log_bank_give
where give_agent = 1 and recv_agent = 0
and time > %d
''' % today0
agent_sell = LogQry(channel).qry(sql)[0][0]
## 代理买分
sql = '''
select ifnull(sum(money), 0)
from log_bank_give
where give_agent = 0 and recv_agent = 1
and time > %d
''' % today0
agent_buy = LogQry(channel).qry(sql)[0][0]
## 游戏活跃人数
sql = '''
select count(distinct pid)
from %s
where time >= %d
''' % (get_table_log_player_subgame(today0), today0)
game_active_count = LogQry(channel).qry(sql)[0][0]
data = {"recharge": int(recharge), "withdraw": int(withdraw), "onlinenum": onlinenum,
"reg_count": int(reg_count), "active_count": int(active_count),
"stake": int(stake), "output": int(output), "player_coin": int(player_coin),
"agent_coin": int(agent_coin), "agent_sell": int(agent_sell),
"agent_buy": int(agent_buy), "pump": int(pump),
"game_active_count": game_active_count}
return jsonify(data)
def search_daily_data_today():
start = request.args.get('beginDate', '')
channel = session['select_channel']
today0 = time_util.today0()
pre_record = {}
pre_record['date_text'] = start
# 注册人数
sql = '''
select count(1)
from log_role_reg
where time >= %d
''' % (today0)
pre_record['reg_count'] = LogQry(channel).qry(sql)[0][0]
# 活跃人数 登录次数统计
sql = '''
select count(distinct pid), count(pid)
from log_account_login
where time >= %d
''' % (today0)
pre_record['active_count'], pre_record['login_count'] = LogQry(channel).qry(sql)[0]
sql = '''
select count(1), ifnull(sum(cost), 0), ifnull(sum(coin), 0)
from admin_recharge
where time >= %d
and state = 1
''' % (today0)
pre_record['recharge_count'], pre_record['total_recharge'], pre_record['recharge_coin'] = \
LogQry(channel).qry(sql)[0]
pre_record['total_recharge'], pre_record['recharge_coin'] = float(pre_record['total_recharge']), float(
pre_record['recharge_coin'])
# todo 日破产人数 日破产次数 提现总额
pre_record['bankrupt_player_count'] = 0
pre_record['bankrupt_count'] = 0
pre_record['withdraw'] = 0
# 游戏抽水 游戏总盈亏
sql = '''
select ifnull(sum(pump), 0), ifnull(sum(ai_coin), 0)
from %s
force index(time)
where time >= %d
''' % (get_table_log_subgame(today0), today0)
pre_record['pump'], pre_record['game_win'] = LogQry(channel).qry(sql)[0]
pre_record['pump'], pre_record['game_win'] = float(pre_record['pump']), float(pre_record['game_win'])
## 赠送抽水
sql = '''
select ifnull(sum(pump), 0)
from log_bank_give
force index(time)
where time >= %d
''' % today0
give_pump = LogQry(channel).qry(sql)[0][0]
pre_record['pump'] += give_pump
pre_record['pump'] = float(pre_record['pump'])
## 2日留存
pre_record['day2'] = 0
## 3日留存
pre_record['day3'] = 0
## 7日留存
pre_record['day7'] = 0
## 15日留存
pre_record['day15'] = 0
## 30日留存
pre_record['day30'] = 0
# 日人均线时长
pre_record['avg_online_time'] = 0
sql = '''
select ifnull(sum(online_time), 0)
from log_account_login
where time >= %d
and opt = "account_logout"
''' % (today0)
if pre_record['active_count'] > 0:
pre_record['avg_online_time'] = int(LogQry(channel).qry(sql)[0][0] / pre_record['active_count'])
# 最高在线人数
sql = '''
select ifnull(max(num), 0)
from log_online
where time >= %d
''' % (today0)
pre_record['max_online_num'] = LogQry(channel).qry(sql)[0][0]
pre_record['active_arpu'] = 0
pre_record['active_arppu'] = 0
if pre_record['active_count'] > 0:
pre_record['active_arpu'] = pre_record['total_recharge'] / pre_record['active_count']
if pre_record['recharge_count'] > 0:
pre_record['active_arpu'] = pre_record['total_recharge'] / pre_record['recharge_count']
## 游戏活跃人数
sql = '''
select count(distinct pid)
from %s
where time >= %d
''' % (get_table_log_player_subgame(today0), today0)
game_active_count = LogQry(channel).qry(sql)[0][0]
pre_record['game_active_count'] = game_active_count
## 新增设备数
sql = '''
select count(distinct did)
from player
where reg_time >= %d
and did not in
(select distinct did from player where reg_time < %d)
''' % (today0, today0)
pre_record['new_device'] = LogQry(channel).qry(sql)[0][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
''' % (today0, today0)
Result = LogQry(channel).qry(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(give_player_num)
}
pre_record["give_coin_%s2%s" % (give_agent, recv_agent)] = json.dumps(Data)
return jsonify(result='ok', data=[pre_record])
def player_tj():
# 获取参数
start_time = request.args.get('start_time')
end_time = request.args.get('end_time')
channel_id = session['select_channel']
# 处理参数
start_time = time_util.formatDatestamp(start_time)
end_time = time_util.formatDatestamp(end_time)
channel_id = int(channel_id)
## 查询注册人数
sql = '''
select count(1)
from player
force index(reg_time)
where reg_time >= %d
and reg_time <= %d
''' % (start_time, end_time)
reg_count = LogQry(channel_id).qry(sql)[0][0]
## 查询游戏人数
sql = '''
select distinct pid
from t_player_subgame
force index(time)
where time >= %s
and time <= %s
''' % (time_util.formatTimeWithDesc(start_time, "%Y%m%d"), time_util.formatTimeWithDesc(end_time, "%Y%m%d"))
pids = list(LogQry(channel_id).qry(sql))
today0 = time_util.today0()
if start_time >= today0 and end_time >= today0:
sql = '''
select distinct pid
from %s
force index(time)
where time >= %d
and time <= %d
''' % (get_table_log_player_subgame(today0), start_time, end_time)
pids.extend(list(LogQry(channel_id).qry(sql)))
player_count = len(set(pids))
## 查询活跃人数
sql = '''
select count(distinct pid)
from log_account_login
force index(time)
where time >= %d
and time <= %d
''' % (start_time, end_time)
active_count = LogQry(channel_id).qry(sql)[0][0]
## 充值
sql = '''
select count(distinct pid)
from admin_recharge
where state = 1
and time >= %d
and time <= %d
''' % (start_time, end_time)
recharge = LogQry(channel_id).qry(sql)[0][0]
## 提现
sql = '''
select count(distinct pid)
from admin_withdraw
where status = 1
and application_time >= %d
and application_time <= %d
''' % (start_time, end_time)
withdraw = LogQry(channel_id).qry(sql)[0][0]
data = {
"reg_count": int(reg_count),
"player_count": int(player_count),
"active_count": int(active_count),
"recharge": int(recharge),
"withdraw": int(withdraw),
}
return jsonify(data)
def fiance_tj():
# 获取参数
start_time = request.args.get('start_time')
end_time = request.args.get('end_time')
channel_id = session['select_channel']
# 处理参数
start_time2 = start_time.replace('-', '')
end_time2 = end_time.replace('-', '')
start_time = time_util.formatDatestamp(start_time)
end_time = time_util.formatDatestamp(end_time) + 86400
## 充值
sql = '''
select ifnull(sum(cost), 0)
from admin_recharge
where state = 1
and time >= %d
and time < %d
''' % (start_time, end_time)
recharge = LogQry(channel_id).qry(sql)[0][0]
## 提现
sql = '''
select ifnull(sum(withdraw_deposit_money), 0)
from admin_withdraw
where status = 1
and application_time >= %d
and application_time < %d
''' % (start_time, end_time)
withdraw = LogQry(channel_id).qry(sql)[0][0]
## 查询代理卖分
sql = '''
select ifnull(sum(money), 0)
from log_bank_give
where give_agent = 1 and recv_agent = 0
and time >= %d
and time < %d
''' % (start_time, end_time)
agent_sell = LogQry(channel_id).qry(sql)[0][0]
## 代理买分
sql = '''
select ifnull(sum(money), 0)
from log_bank_give
where give_agent = 0 and recv_agent = 1
and time >= %d
and time < %d
''' % (start_time, end_time)
agent_buy = LogQry(channel_id).qry(sql)[0][0]
## todo 返水
fanshui = 0
## todo 返佣
fanyong = 0
# 查询游戏盈利
sql = """
SELECT ifnull(sum(total_stake_coin - total_output_coin), 0)
FROM t_subgame
WHERE time>={}
AND time<={};
"""
sql = sql.format(start_time2, end_time2)
data2 = LogQry(channel_id).qry(sql)
try:
ai_win1 = int(data2[0][0])
except TypeError:
ai_win1 = 0
sql = """
SELECT ifnull(sum(stake_coin - output_coin), 0)
FROM {}
WHERE time>={}
AND time<={};
"""
sql = sql.format(get_table_log_player_subgame(time_util.now_sec()), time_util.today0(), end_time)
data2 = LogQry(channel_id).qry(sql)
try:
ai_win2 = int(data2[0][0])
except TypeError:
ai_win2 = 0
data = {
"recharge": int(recharge),
"withdraw": int(withdraw),
"agent_sell": int(agent_sell),
"agent_buy": int(agent_buy),
"fanyong": fanyong,
"fanshui": fanshui,
"ai_win": ai_win1 + ai_win2,
}
return jsonify(data)
def search_daily_presentation():
start = request.args.get('beginDate', '')
end = request.args.get('endDate', '')
present_type = int(request.args.get('present_type'))
channel = session['select_channel']
start_date = time_util.formatDatestamp(start)
end_date = time_util.formatDatestamp(end)
today0 = time_util.today0()
if start_date == today0:
return search_daily_presentation_today()
if start_date > end_date:
return jsonify(result='fail', msg=u'结束时间不能小于开始时间！')
start_date = int(time_util.formatTimeWithDesc(start_date, "%Y%m%d"))
end_date = int(time_util.formatTimeWithDesc(end_date, "%Y%m%d"))
search_t_sql = """
SELECT time, sum(reg_count), sum(active_count), sum(recharge_player_count), sum(bankrupt_player_count),
sum(bankrupt_count), give_coin_a2a, give_coin_a2p, give_coin_p2a, give_coin_p2p
FROM t_system
WHERE time>=%d
AND time<=%d
group by time
""" % (start_date, end_date)
## 最终数据
allday_datas = []
for line in LogQry(channel).qry(search_t_sql):
if not line[0]:
## 防止没有数据时报错
continue
pre_line = dict()
pre_line['date_text'] = time_util.date_str(line[0])
pre_line['reg_count'] = float(line[1])
pre_line['active_count'] = float(line[2])
pre_line['recharge_player_num'] = float(line[3])
pre_line['bankrupt_player_count'] = float(line[4])
pre_line['bankrupt_count'] = float(line[5])
if present_type == 0: ## 查询全部赠送情况
D0 = json.loads(line[6])
D1 = json.loads(line[7])
D2 = json.loads(line[8])
D3 = json.loads(line[9])
total_give_times = D0["give_times"] + D1["give_times"] + D2[
"give_times"] + D3["give_times"]
total_give_coin = D0["give_coin"] + D1["give_coin"] + D2[
"give_coin"] + D3["give_coin"]
total_give_pump = D0["give_pump"] + D1["give_pump"] + D2[
"give_pump"] + D3["give_pump"]
total_give_player_num = D0["give_player_num"] + D1["give_player_num"] + D2["give_player_num"] \
+ D3["give_player_num"]
total_recv_player_num = D0["recv_coin_player_num"] + D1["recv_coin_player_num"] + \
D2["recv_coin_player_num"] + D3["recv_coin_player_num"]
elif present_type == 1:
D0 = json.loads(line[9])
total_give_times = D0["give_times"]
total_give_coin = D0["give_coin"]
total_give_pump = D0["give_pump"]
total_give_player_num = D0["give_player_num"]
total_recv_player_num = D0["recv_coin_player_num"]
elif present_type == 2:
D0 = json.loads(line[8])
total_give_times = D0["give_times"]
total_give_coin = D0["give_coin"]
total_give_pump = D0["give_pump"]
total_give_player_num = D0["give_player_num"]
total_recv_player_num = D0["recv_coin_player_num"]
elif present_type == 3:
D0 = json.loads(line[6])
total_give_times = D0["give_times"]
total_give_coin = D0["give_coin"]
total_give_pump = D0["give_pump"]
total_give_player_num = D0["give_player_num"]
total_recv_player_num = D0["recv_coin_player_num"]
elif present_type == 4:
D0 = json.loads(line[7])
total_give_times = D0["give_times"]
total_give_coin = D0["give_coin"]
total_give_pump = D0["give_pump"]
total_give_player_num = D0["give_player_num"]
total_recv_player_num = D0["recv_coin_player_num"]
pre_line['money'] = total_give_coin
pre_line['pump'] = total_give_pump
pre_line['give_count'] = total_give_player_num
pre_line['recv_count'] = total_recv_player_num
pre_line['present_count'] = total_give_times
pre_line['averge_presentation'] = 0
if total_give_times > 0:
pre_line[
'averge_presentation'] = total_give_coin / total_give_times
allday_datas.append(pre_line)
return jsonify(result='ok', data=allday_datas)
def search_bar(Action,
beginDate=True,
endDate=False,
PT=False,
SelectChannel=None,
Channels=True,
ChannelSize=1,
PlayerID=False,
NickName=False,
Account=False,
QueryType=2,
OThers=[],
Method="post",
PrecisionSecond=False):
Html = u'''
<script src="/static/js/jquery-2.1.1.min.js"></script>
<script src="/static/js/bootstrap.min.js"></script>
<script src="/static/js/my97date/WdatePicker.js"></script>
<script src="/static/js/all.js?%f"></script>
<form id="query_form" action="%s" method="%s" accept-charset="UTF-8">
<div class='search'>
%s
</div>
</form>
'''
QueryButtonHtml = u""
if QueryType == 3:
QueryButtonHtml = u'''
<input type="button" id="query_btn" class="btn btn-primary btn-sm" value="查询"/>
'''
else:
QueryButtonHtml = u'''
<input type="submit" id="query_btn" class="btn btn-primary btn-sm" value="查询"/>
'''
## 日期选择
DaterStr = u""
if beginDate != False:
if beginDate == True:
beginDate = time_util.Monday0()
elif beginDate == 7:
beginDate = time_util.formatTimestampFormat(
str(
time_util.date_add(
datetime.date.today().strftime('%Y%m%d'), -7)),
'%Y%m%d')
elif beginDate == 11:
beginDate = int(
time.mktime(datetime.datetime.now().date().timetuple()))
elif beginDate == 1:
beginDate = time_util.today0()
if not PrecisionSecond:
DaterStr = u'''<span>
日期：<input class="Wdate" type="text" readonly onClick="WdatePicker({firstDayOfWeek:1, isShowClear:false, isShowOK:false, isShowToday:false, autoPickDate:true})" id="beginDate" name="beginDate" value="%s" required>
''' % time_util.formatTimeWithDesc(beginDate, '%Y-%m-%d')
else:
DaterStr = u'''<span>
日期：<input class="Wdate" type="text" readonly onClick="WdatePicker({dateFmt:'yyyy-MM-dd HH:mm:ss', firstDayOfWeek:1, isShowClear:false, isShowOK:false, isShowToday:false, autoPickDate:true})" id="beginDate" name="beginDate" value="%s" required>
''' % time_util.formatTimeWithDesc(beginDate, '%Y-%m-%d %H:%M:%S')
if endDate != False:
if endDate == True:
endDate = time_util.now_sec()
elif endDate == 11:
endDate = int(
time.mktime(
datetime.datetime.now().date().timetuple())) + 86400
if not PrecisionSecond:
DaterStr += u'''
--<input class="Wdate" type="text" readonly onClick="WdatePicker({firstDayOfWeek:1, isShowClear:false, isShowOK:false, isShowToday:false, autoPickDate:true})" id="endDate" name="endDate" value="%s" required>
''' % time_util.formatTimeWithDesc(endDate, '%Y-%m-%d')
else:
DaterStr += u'''
--<input class="Wdate" type="text" readonly onClick="WdatePicker({dateFmt:'yyyy-MM-dd HH:mm:ss', firstDayOfWeek:1, isShowClear:false, isShowOK:false, isShowToday:false, autoPickDate:true})" id="endDate" name="endDate" value="%s" required>
''' % time_util.formatTimeWithDesc(endDate, '%Y-%m-%d %H:%M:%S')
if DaterStr:
DaterStr += u'%s</span>' % QueryButtonHtml
else:
DaterStr = u'<span>%s</span>' % QueryButtonHtml
## 平台选择
PTStr = u""
## 渠道选择
ChannelStr = u""
## 玩家选择
PlayerStr = u""
if PlayerID != False:
PlayerStr = u'''
<span> 玩家ID：<input type="text" id="PlayerID" name="PlayerID" value="%s" placeholder=""></span>
''' % PlayerID
if NickName != False:
PlayerStr += u'''
<span> 玩家昵称：<input type="text" id="NickName" name="NickName" value="%s" placeholder=""> </span>
''' % NickName
if Account != False:
PlayerStr += u'''
<span> 玩家账号：<input type="text" id="Account" name="Account" value="%s" placeholder=""> </span>
''' % Account
## 日期快速查询
dateFast = u""
if endDate != False:
dateFast = u'''
<ul class="date_fast">
<li time="last_month">上月</li>
<li time="month">本月</li>
<li time="last_week">上周</li>
<li time="week">本周</li>
<li time="yesterday">昨日</li>
<li time="today">今日</li>
</ul>
'''
if OThers:
SubL = [PTStr, ChannelStr] + OThers + [PlayerStr, DaterStr, dateFast]
else:
SubL = [PTStr, ChannelStr, PlayerStr, DaterStr, dateFast]
return Html % (random.random(), Action, Method, "".join(SubL))