def pay_channel_tj_show():
# 获取参数
channel = session['select_channel']
begin_time = request.args.get('beginDate')
end_time = request.args.get('endDate')
start_date = time_util.formatDatestamp(begin_time)
end_date = time_util.formatDatestamp(end_time) + 86400
pc = {}
sql = 'select id, name from admin_pay_channel'
for idx, name in LogQry(channel).qry(sql):
pc[idx] = name
sql = 'select id, api_name from admin_online_payment'
for idx, name in LogQry(channel).qry(sql):
pc[idx] = name
datas = []
sql = '''
select pay_channel, FROM_UNIXTIME(time,'%%Y-%%m-%%d'), ifnull(sum(cost), 0)
from admin_recharge
where time >= %d
and time <= %d
and state = %d
group by pay_channel, FROM_UNIXTIME(time,'%%Y-%%m-%%d')
''' % (start_date, end_date, PAY_STATE_SUCC)
for pid, date, money in LogQry(channel).qry(sql):
datas.append({
"pay_channel":pid,
"date":date,
"money":int(money)
})
return jsonify(result = "ok", datas = datas, pay_channel = pc)
def online_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 time, sum(num)
from log_online
force index(time)
where time >= %d
and time < %d
group by time
''' % (start_time, start_time + 86400)
data1 = LogQry(channel_id).qry(sql)
sql = '''
select time, sum(num)
from log_online
force index(time)
where time >= %d
and time < %d
group by time
''' % (end_time, end_time + 86400)
data2 = LogQry(channel_id).qry(sql)
return jsonify(data1=[(i, int(j)) for i, j in data1], data2=[(i, int(j)) for i, j in data2])
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 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_sub_detail():
start = request.args.get('beginDate')
end = request.args.get('endDate')
channel = session['select_channel']
status_msg = dict()
status_msg['beginDate'] = time_util.formatDatestamp(start)
status_msg['endDate'] = time_util.formatDatestamp(end)
status_msg["channel"] = channel
return render_template('data_single_game.html', status_msg=status_msg, datas=[])
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 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 tj_player_device():
# 获取参数
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) + 86400
channel_id = int(channel_id)
sql = 'select distinct pid from log_account_login where time >= %d and time < %d' % (start_time, end_time)
pids = ",".join([str(i[0]) for i in LogQry(channel_id).qry(sql)])
## 计算客户端分步
d = {}
if pids:
sql = 'select device, count(1) from player where id in (%s) group by device' % pids
for device, count in LogQry(channel_id).qry(sql):
d[device] = int(count)
## 计算新老玩家分步
old_p, new_p = 0, 0
if pids:
sql = '''
select ifnull(sum(if(reg_time < %d, 1, 0)), 0) as old, ifnull(sum(if(reg_time >= %d, 1, 0)), 0) as new
from player
where id in (%s)
''' % (start_time, start_time, pids)
old_p, new_p = LogQry(channel_id).qry(sql)[0]
data = {
"device": d,
"player_count": {"old": int(old_p), "new": int(new_p)},
}
return jsonify(data)
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_subgame_data_today():
start = request.args.get('beginDate', '')
channel = session['select_channel']
start_date = time_util.formatDatestamp(start)
sql = '''
select gameid, count(1), ifnull(sum(pump), 0), ifnull(sum(stake_coin), 0), ifnull(sum(output_coin), 0),
roomtype
from %s
force index(time)
where time >= %d
group by gameid, roomtype
''' % (get_table_log_subgame(start_date), start_date)
datas = []
SubGameList = game_parameter.get_subgame_list()
room_define = game_parameter.room_define()
for search_data in LogQry(channel).qry(sql):
try:
roomname = room_define[search_data[0]][search_data[5]]
except:
roomname = search_data[5]
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': int(search_data[1]),
'total_stake_coin': int(search_data[3]),
'total_get_coin': int(search_data[4]),
'win_player_num': 0,
'lose_player_num': 0,
'total_win': 0,
'total_lose': 0,
'average_game_count': 0,
'pump': int(search_data[2]),
'ai_win': int(search_data[3] - search_data[4])
}
## 查询当日赢钱玩家数 输钱玩家数
pre_record["active_count"] = 0
sql = '''
select pid, sum(stake_coin), sum(output_coin)
from %s
where time >= %d
and gameid = %d
and roomtype = %d
group by pid
''' % (get_table_log_player_subgame(start_date), start_date, search_data[0], search_data[5])
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])
pre_record["total_lose"] = float(pre_record["total_lose"])
pre_record["total_win"] = float(pre_record["total_win"])
if pre_record["active_count"] > 0:
pre_record['average_game_count'] = int(pre_record["total_game_times"] / pre_record["active_count"])
datas.append(pre_record)
return jsonify(result='ok', data=datas)
def search_daily_data_detail():
start1 = request.args.get('beginDate')
start2 = request.args.get('endDate')
channel = session['select_channel']
start_timeStamp_1 = time_util.formatDatestamp(start1)
end_timeStamp_1 = start_timeStamp_1 + 86400
datas1 = []
while start_timeStamp_1 < end_timeStamp_1:
day_data_dict = {}
# 时间
day_data_dict['time'] = time_util.formatTimeWithDesc(start_timeStamp_1, "%H")
# 注册人数
retrieve_sql = """SELECT count(1)
FROM log_role_reg
WHERE time>=%s
AND time<%s;""" \
% (start_timeStamp_1, start_timeStamp_1 + 3600)
day_data_dict['reg_count'] = LogQry(channel).qry(retrieve_sql)[0][0]
# 登录
retrieve_sql = """SELECT count(distinct pid),count(1)
FROM log_account_login
force index(time)
WHERE time>=%s
AND time<%s;""" \
% (start_timeStamp_1, start_timeStamp_1 + 3600)
day_data_dict['active_count'], day_data_dict['login_count'] = LogQry(channel).qry(retrieve_sql)[0]
# 充值
retrieve_sql = """SELECT count(distinct pid),ifnull(sum(cost),0)
FROM admin_recharge
WHERE time>=%s
AND time<=%s
AND state=1;""" \
% (start_timeStamp_1, start_timeStamp_1 + 3600)
day_data_dict['recharge_count'], day_data_dict['total_recharge'] = LogQry(channel).qry(retrieve_sql)[0]
day_data_dict['total_recharge'] = float(day_data_dict['total_recharge'])
# 新增充值
retrieve_sql = """SELECT count(distinct pid)
FROM admin_recharge
WHERE time >=%s
AND time<=%s
AND state=1
AND pid NOT IN (select pid from admin_recharge where state=1 and time<%s);""" \
% (start_timeStamp_1, start_timeStamp_1 + 3600, start_timeStamp_1)
day_data_dict['new_recharge_count'] = LogQry(channel).qry(retrieve_sql)[0][0]
# 游戏盈亏
day_data_dict['game_win'] = 0
day_data_dict['pump'] = 0
retrieve_sql = """SELECT ifnull(sum(ai_coin),0),ifnull(sum(pump),0),ifnull(sum(stake_coin),0),ifnull(sum(output_coin),0)
FROM %s
force index(time)
WHERE time>=%s
AND time<=%s;""" \
% (get_table_log_subgame(start_timeStamp_1), start_timeStamp_1, start_timeStamp_1 + 3600)
day_data_dict['game_win'], day_data_dict['pump'], day_data_dict['total_stake'], day_data_dict['total_output'] = \
LogQry(channel).qry(retrieve_sql)[0]
day_data_dict['game_win'] = float(day_data_dict['game_win'])
day_data_dict['pump'] = float(day_data_dict['pump'])
day_data_dict['total_stake'] = float(day_data_dict['total_stake'])
day_data_dict['total_output'] = float(day_data_dict['total_output'])
# TODO 提现
day_data_dict['withdraw'] = 0
# 最高在线
retrieve_sql = """SELECT ifnull(max(num),0)
FROM (select sum(num) as num from log_online where time>=%s and time<%s group by time) AS a;""" \
% (start_timeStamp_1, start_timeStamp_1 + 3600)
day_data_dict['max_online_num'] = LogQry(channel).qry(retrieve_sql)[0][0]
day_data_dict['max_online_num'] = float(day_data_dict['max_online_num'])
datas1.append(day_data_dict)
start_timeStamp_1 += 3600
start_timeStamp_2 = time_util.formatDatestamp(start2)
end_timeStamp_2 = start_timeStamp_2 + 86400
datas2 = []
while start_timeStamp_2 < end_timeStamp_2:
day_data_dict = {}
# 时间
day_data_dict['time'] = time_util.formatTimeWithDesc(start_timeStamp_2, "%H")
# 注册人数
retrieve_sql = """SELECT count(1)
FROM log_role_reg
WHERE time>=%s
AND time<%s;""" \
% (start_timeStamp_2, start_timeStamp_2 + 3600)
day_data_dict['reg_count'] = LogQry(channel).qry(retrieve_sql)[0][0]
# 登录
retrieve_sql = """SELECT count(distinct pid),count(1)
FROM log_account_login
force index(time)
WHERE time>=%s
AND time<%s;""" \
% (start_timeStamp_2, start_timeStamp_2 + 3600)
day_data_dict['active_count'], day_data_dict['login_count'] = LogQry(channel).qry(retrieve_sql)[0]
# 充值
retrieve_sql = """SELECT count(distinct pid),ifnull(sum(cost),0)
FROM admin_recharge
WHERE time>=%s
AND time<=%s
AND state=1;""" \
% (start_timeStamp_2, start_timeStamp_2 + 3600)
day_data_dict['recharge_count'], day_data_dict['total_recharge'] = LogQry(channel).qry(retrieve_sql)[0]
day_data_dict['total_recharge'] = float(day_data_dict['total_recharge'])
# 新增充值
retrieve_sql = """SELECT count(distinct pid)
FROM admin_recharge
WHERE time >=%s
AND time<=%s
AND state=1
AND pid NOT IN (select pid from admin_recharge where state=1 and time<%s);""" \
% (start_timeStamp_2, start_timeStamp_2 + 3600, start_timeStamp_2)
day_data_dict['new_recharge_count'] = LogQry(channel).qry(retrieve_sql)[0][0]
# 游戏盈亏
day_data_dict['game_win'] = 0
day_data_dict['pump'] = 0
retrieve_sql = """SELECT ifnull(sum(ai_coin),0),ifnull(sum(pump),0),ifnull(sum(stake_coin),0),ifnull(sum(output_coin),0)
FROM %s
force index(time)
WHERE time>=%s
AND time<=%s;""" \
% (get_table_log_subgame(start_timeStamp_2), start_timeStamp_2, start_timeStamp_2 + 3600)
day_data_dict['game_win'], day_data_dict['pump'], day_data_dict['total_stake'], day_data_dict['total_output'] = \
LogQry(channel).qry(retrieve_sql)[0]
day_data_dict['game_win'] = float(day_data_dict['game_win'])
day_data_dict['pump'] = float(day_data_dict['pump'])
day_data_dict['total_stake'] = float(day_data_dict['total_stake'])
day_data_dict['total_output'] = float(day_data_dict['total_output'])
# TODO 提现
day_data_dict['withdraw'] = 0
# 最高在线
retrieve_sql = """SELECT ifnull(max(num), 0)
FROM (select sum(num) as num from log_online where time>=%s and time<%s group by time) AS a;""" \
% (start_timeStamp_2, start_timeStamp_2 + 3600)
day_data_dict['max_online_num'] = LogQry(channel).qry(retrieve_sql)[0][0]
day_data_dict['max_online_num'] = float(day_data_dict['max_online_num'])
datas2.append(day_data_dict)
start_timeStamp_2 += 3600
return jsonify(result='ok', data1=datas1, data2=datas2)
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 search_presentation_detail():
"""赠送订单详情查询"""
date = request.args.get('date')
start = request.args.get('beginDate', '')
end = request.args.get('endDate', '')
player_id = request.args.get('PlayerID', '')
nick = request.args.get('NickName', '')
money = request.args.get('present_amount', '')
present_type = request.args.get('present_type', '')
channel = session['select_channel']
size = request.args.get('size')
offset = request.args.get('offset')
start_date = time_util.start(start)
end_date = time_util.end(end)
if date:
start_date = time_util.formatDatestamp(date)
end_date = time_util.formatDatestamp(date)
if start_date > end_date:
return jsonify(result=0, msg=u'开始时间不能大于结束时间！')
if nick and player_id:
return jsonify(result=0, msg=u'玩家昵称与玩家ID只能输入其一！')
game_log_db = LogQry(channel)
if nick:
nick_sql = "SELECT id FROM player WHERE nick='%s'" % nick
if len(game_log_db.qry(nick_sql)) != 0:
player_id = game_log_db.qry(nick_sql)[0][0]
else:
player_id = -1
if player_id:
play_id_str = " AND (give_id=%s or recv_id=%s)" % (player_id,
player_id)
else:
play_id_str = ''
if money:
try:
money = int(money)
money_str = " AND money>=%s" % money
except Exception as e:
money_str = ''
else:
money_str = ''
present_type_str = ''
if present_type == '0':
present_type_str = ''
elif present_type == '1':
present_type_str = ' AND (give_agent=0 AND recv_agent=0)'
elif present_type == '2':
present_type_str = ' AND (give_agent=0 AND recv_agent=1)'
elif present_type == '3':
present_type_str = ' AND (give_agent=1 AND recv_agent=1)'
elif present_type == '4':
present_type_str = ' AND (give_agent=1 AND recv_agent=0)'
# 总数据条数、总赠送金币、总缴纳税收
retrieve_sql = """SELECT count(*),sum(money),sum(pump)
FROM log_bank_give
WHERE time>=%s AND time<%s
AND 1=1 %s%s%s;""" \
% (start_date, end_date + 86399, play_id_str, money_str, present_type_str)
total_data = LogQry(channel).qry(retrieve_sql)[0]
total_dict = dict()
total_dict['total_data'] = total_data[0]
try:
total_dict['total_money'] = float(total_data[1])
except TypeError:
total_dict['total_money'] = 0
try:
total_dict['total_pump'] = float(total_data[2])
except TypeError:
total_dict['total_pump'] = 0
give_sql = '''
SELECT time, give_id, give_agent,
(select nick from player where id = give_id),
(select last_login_ip from player where id=give_id),
recv_id, recv_agent, (select nick from player where id = recv_id),
(select last_login_ip from player where id=recv_id), money,
pump
FROM log_bank_give
WHERE time>=%d AND time<%d
AND 1=1 %s%s%s
ORDER BY time DESC
LIMIT %s,%s;
''' % (start_date, end_date + 86399, play_id_str, money_str,
present_type_str, offset, size)
alltime_datas = LogQry(channel).qry(give_sql)
time_search_data = []
recvid_list = []
for time_stamp, give_id, give_agent, give_nick, give_last_login_ip, \
recv_id, recv_agent, recv_nick, recv_last_login_ip, money, \
pump in alltime_datas:
recvid_list.append(recv_id)
present_dict = dict()
present_dict['time'] = time_util.formatDateTime(time_stamp)
present_dict['give_id'] = give_id
present_dict['give_nick'] = give_nick
present_dict['give_last_login_ip'] = give_last_login_ip
present_dict['recv_id'] = recv_id
present_dict['recv_nick'] = recv_nick
present_dict['recv_last_login_ip'] = recv_last_login_ip
present_dict['money'] = money
present_dict['pump'] = pump
if give_agent == 0:
if recv_agent == 0:
present_dict['present_type'] = u"玩家与玩家"
elif recv_agent == 1:
present_dict['present_type'] = u"玩家与代理"
elif give_agent == 1:
if recv_agent == 0:
present_dict['present_type'] = u"代理与玩家"
elif recv_agent == 1:
present_dict['present_type'] = u"代理与代理"
time_search_data.append(present_dict)
return jsonify(result=1, data=time_search_data, total=total_dict)
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 prestented_data_tj():
PID = request.args.get('PlayerID', '')
# 接收渠道id
channel = session['select_channel']
NickName = request.args.get('NickName', '')
date1 = time_util.formatDatestamp(request.args.get('date1'))
date2 = time_util.formatDatestamp(request.args.get('date2')) + 86400
## 查询玩家ID
Where = ""
if NickName:
Where += "and nick = '%s'" % NickName
if PID:
Where += " and id = %s" % PID
print "WHERE:", Where
if not Where:
return jsonify([])
PID = LogQry(channel).qry("select id from player where 1 =1 %s" %
Where)[0][0]
sql = '''
select time, give_id, recv_id, money, pump
from log_bank_give
where time >= %d and time <= %d
and (give_id = %d or recv_id = %d)
order by time
''' % (date1, date2, PID, PID)
datas = []
pre_date = 0
pre_record = {}
print sql
for search_data in LogQry(channel).qry(sql):
cur_date = time_util.formatDate(search_data[0])
if cur_date == pre_date:
if search_data[1] == PID:
## 卖分
pre_record[
"down_coin"] = pre_record["down_coin"] + search_data[3]
if search_data[2] == PID:
## 卖分
pre_record["up_coin"] = pre_record["up_coin"] + search_data[3]
pre_record["pump"] = pre_record["pump"] + search_data[4]
pre_record["count"] = pre_record["count"] + 1
continue
if pre_record:
datas.append(pre_record)
pre_record = {}
pre_date = cur_date
pre_record['time'] = cur_date
pre_record["up_coin"] = 0
pre_record["down_coin"] = 0
if search_data[1] == PID:
## 卖分
pre_record["down_coin"] = search_data[3]
if search_data[2] == PID:
## 卖分
pre_record["up_coin"] = search_data[3]
pre_record["pump"] = search_data[4]
pre_record["count"] = 1
if pre_record:
datas.append(pre_record)
total_count = len(datas)
return jsonify({
"errcode": 0,
"dataLength": total_count,
"rowDatas": datas
})
def search_game_user_data_tj():
channel = session['select_channel']
PID = request.args.get('PlayerID', '')
NickName = request.args.get('NickName', '')
Account = request.args.get('Account', '')
date1 = time_util.formatTimeWithDesc(
time_util.formatDatestamp(request.args.get('date1')), "%Y%m%d")
date2 = time_util.formatTimeWithDesc(
time_util.formatDatestamp(request.args.get('date2')), "%Y%m%d")
# 校验参数
if not PID and not NickName and not Account:
return jsonify(result='failed', msg=u'请输入玩家账号或玩家ID或玩家昵称！')
if (PID and NickName) or (PID and Account) or (NickName and Account):
return jsonify(result='failed', msg=u'玩家账号、玩家ID、玩家昵称只能输入其一！')
# 玩家账号、玩家昵称转换成玩家ID
if NickName:
retrieve_sql = """SELECT id FROM player WHERE nick='{}';""".format(
NickName)
try:
PID = LogQry(channel).qry(retrieve_sql)[0][0]
except IndexError:
return jsonify(result='failed', msg=u'该玩家不存在！')
elif Account:
retrieve_sql = """SELECT id FROM player WHERE account_id='{}';""".format(
Account)
try:
PID = LogQry(channel).qry(retrieve_sql)[0][0]
except IndexError:
return jsonify(result='failed', msg=u'该玩家不存在！')
subgame_list = game_parameter.get_subgame_list()
sql = '''
select time, gameid, sum(game_count), sum(stake_coin), sum(output_coin),
(select ifnull(sum(today_recharge), 0) from t_player_general where time = a.time and pid = a.pid),
(select ifnull(sum(today_withdraw), 0) from t_player_general where time = a.time and pid = a.pid),
(select ifnull(sum(bankrupt_times), 0) from t_player_general where time = a.time and pid = a.pid)
from t_player_subgame a
where time >= %s and time <= %s
and pid = %s
group by time, gameid
order by time desc
''' % (date1, date2, PID)
datas = []
for search_data in LogQry(channel).qry(sql):
pre_record = {}
pre_record['time'] = search_data[0]
pre_record['game_id'] = game_parameter.get_subgame_by_id(
subgame_list, search_data[1])
pre_record['game_count'] = int(search_data[2])
pre_record['stake_coin'] = int(search_data[3])
pre_record['win_coin'] = int(search_data[4])
pre_record[
'total_win'] = pre_record['stake_coin'] - pre_record['win_coin']
pre_record['today_recharge'] = int(search_data[5])
pre_record['today_withdraw'] = int(search_data[6])
pre_record['bankrupt_count'] = int(search_data[7])
datas.append(pre_record)
total_count = len(datas)
return jsonify({
"errcode": 0,
"dataLength": total_count,
"rowDatas": datas
})
def search_distribution_detail():
date = request.args.get('date')
start = request.args.get('beginDate')
end = request.args.get('endDate')
agent_id = request.args.get('agent_id', '')
channel_id = session['select_channel']
if date:
start_date = time_util.formatDatestamp(date)
end_date = time_util.formatDatestamp(date)
elif start and end:
start_date = time_util.formatDatestamp(start)
end_date = time_util.formatDatestamp(end)
status_msg = init_status_msg(begin=start_date,
end=end_date,
channel=channel_id,
OThers_list=[{
'agent_id': agent_id
}])
if start_date > end_date:
status_msg['errmsg'] = u"终止日期不能小于起始日期"
return render_template('distribution_detail.html',
status_msg=status_msg)
if agent_id:
agent_str = ' AND pid=%s' % agent_id
else:
status_msg['errmsg'] = u"请输入代理游戏ID查询"
return render_template('distribution_detail.html',
status_msg=status_msg)
search_t_sql = """SELECT time, pid, pump, pump1, pump2, pump3, nick
FROM t_distribution_day
LEFT JOIN
player ON pid=id
WHERE time>=%d
AND time<%d
%s;""" \
% (start_date, end_date + 86400, agent_str)
print search_t_sql
game_db_qrl = LogQry(int(channel_id))
t_pump_datas = game_db_qrl.qry(search_t_sql)
print t_pump_datas
allday_datas = []
for day_stamp, pid, pump, pump1, pump2, pump3, nick in t_pump_datas:
day_dict = dict()
day_dict['date'] = time_util.formatDate(day_stamp)
day_dict['channel_id'] = channel_id
day_dict['pid'] = pid
day_dict['nick'] = nick
day_dict['pump'] = pump
day_dict['pump1'] = 0
day_dict['pump2'] = 0
day_dict['pump3'] = 0
day_dict['pump1_list'] = []
day_dict['pump2_list'] = []
day_dict['pump3_list'] = []
for pid_1, pump11 in json.loads(pump1).items():
pump1_dict = dict()
day_dict['pump1'] += pump11
select_sql = """SELECT nick from player WHERE id=%s""" % pid_1
player_nick = game_db_qrl.qry(select_sql)[0][0]
pump1_dict['pid'] = pid_1
pump1_dict['nick'] = player_nick
pump1_dict['pump'] = pump11
day_dict['pump1_list'].append(pump1_dict)
for pid_2, pump22 in json.loads(pump2).items():
pump2_dict = dict()
day_dict['pump2'] += pump22
select_sql = """SELECT nick from player WHERE id=%s""" % pid_2
player_nick = game_db_qrl.qry(select_sql)[0][0]
pump2_dict['pid'] = pid_2
pump2_dict['nick'] = player_nick
pump2_dict['pump'] = pump22
day_dict['pump2_list'].append(pump2_dict)
for pid_3, pump33 in json.loads(pump3).items():
pump3_dict = dict()
day_dict['pump3'] += pump33
select_sql = """SELECT nick from player WHERE id=%s""" % pid_3
player_nick = game_db_qrl.qry(select_sql)[0][0]
pump3_dict['pid'] = pid_3
pump3_dict['nick'] = player_nick
pump3_dict['pump'] = pump33
day_dict['pump3_list'].append(pump3_dict)
allday_datas.append(day_dict)
return render_template('distribution_detail.html',
status_msg=status_msg,
datas=allday_datas)
def search_distribution_daily():
start = request.args.get('beginDate')
end = request.args.get('endDate')
agent_id = request.args.get('agent_id', '')
channel_id = session['select_channel']
if agent_id:
agent_str = ' AND pid=%s' % agent_id
else:
agent_str = ''
start_date = time_util.formatDatestamp(start)
end_date = time_util.formatDatestamp(end)
status_msg = init_status_msg(begin=start_date,
end=end_date,
channel=channel_id,
OThers_list=[{
'agent_id': agent_id
}])
if start_date > end_date:
status_msg['errmsg'] = u"终止日期不能小于起始日期"
return render_template('distribution_daily.html',
status_msg=status_msg)
search_t_sql = """SELECT time, pid, pump, pump1, pump2, pump3, nick
FROM t_distribution_day
LEFT JOIN player
ON pid=id
WHERE time>=%d
AND time<%d
%s ORDER BY time;""" \
% (start_date, end_date + 86400, agent_str)
game_db_qrl = LogQry(int(channel_id))
t_pump_datas = game_db_qrl.qry(search_t_sql)
allday_datas = []
day_dict = dict()
for day_stamp, pid, pump, pump1, pump2, pump3, nick in t_pump_datas:
day_dict['date'] = time_util.formatDate(day_stamp)
day_dict['channel_id'] = channel_id
day_dict['pid'] = pid
day_dict['nick'] = nick
day_dict['pump'] = pump
day_dict['pump1'] = 0
day_dict['pump2'] = 0
day_dict['pump3'] = 0
for pid_1, pump11 in json.loads(pump1).items():
day_dict['pump1'] += pump11
for pid_2, pump22 in json.loads(pump2).items():
day_dict['pump2'] += pump22
for pid_3, pump33 in json.loads(pump3).items():
day_dict['pump3'] += pump33
allday_datas.append(day_dict)
day_dict = {}
return render_template('distribution_daily.html',
status_msg=status_msg,
datas=allday_datas)
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 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_users_manage_log():
start = time_util.formatDatestamp(request.args.get('beginDate'))
end = time_util.formatDatestamp(request.args.get('endDate'))
role_str = session['role_str']
highest_role = get_highest_role_id(role_str)
if highest_role == 1:
sql = '''
select log_type, operator, obj, val, timestamp
from admin_opt_log
where channel=0 and timestamp >= %d and timestamp <= %d and maintype = 1
order by timestamp desc limit 30;
''' % (start, end + 86400)
else:
sql = '''
select log_type, operator, obj, val, timestamp
from admin_opt_log
where channel=0 and val=%s and timestamp >= %d and timestamp <= %d and maintype = 1
order by timestamp desc limit 30;
''' % (highest_role, start, end + 86400)
print sql
page = deepcopy(init_page)
page["beginDate"] = start
page["endDate"] = end
for log_type, operator, obj, val, timestamp in SqlOperate().select(sql):
print obj
OperatorName = SqlOperate().select(
"select name from user where id = %d" % operator)[0][0]
if log_type == log_type_d["delete_staff"]:
obj_sql = "select name from user where id = %d" % obj
if SqlOperate().select(obj_sql):
name = SqlOperate().select(obj_sql)[0][0]
else:
name = ''
obj_name = name + "(ID:%d)" % obj
log_content = u"管理员%s删除员工%s" % \
(blue_html(OperatorName), red_html(obj_name))
if log_type == log_type_d["add_staff"]:
obj_sql = "select name from user where id = %d" % obj
print obj_sql
if SqlOperate().select(obj_sql):
name = SqlOperate().select(obj_sql)[0][0]
else:
name = ''
obj_name = name + "(ID:%d)" % obj
print 'obj_name', obj_name
log_content = u"管理员%s添加员工%s" % \
(blue_html(OperatorName), red_html(obj_name))
if log_type == log_type_d["edit_staff"]:
obj_sql = "select name from user where id = %d" % obj
if SqlOperate().select(obj_sql):
name = SqlOperate().select(obj_sql)[0][0]
else:
name = ''
obj_name = name + "(ID:%d)" % obj
log_content = u"管理员%s修改员工%s" % \
(blue_html(OperatorName), red_html(obj_name))
if log_type == log_type_d["add_channel"]:
obj_sql = "select name from channel where id = %d" % obj
if SqlOperate().select(obj_sql):
name = SqlOperate().select(obj_sql)[0][0]
else:
name = ''
obj_name = name + "(ID:%d)" % obj
log_content = u"管理员%s添加渠道%s" % \
(blue_html(OperatorName), red_html(obj_name))
if log_type == log_type_d["edit_channel"]:
obj_sql = "select name from channel where id = %d" % obj
if SqlOperate().select(obj_sql):
name = SqlOperate().select(obj_sql)[0][0]
else:
name = ''
obj_name = name + "(ID:%d)" % obj
log_content = u"管理员%s修改渠道%s" % \
(blue_html(OperatorName), red_html(obj_name))
if log_type == log_type_d["delete_channel"]:
obj_sql = "select name from channel where id = %d" % obj
if SqlOperate().select(obj_sql):
name = SqlOperate().select(obj_sql)[0][0]
else:
name = ''
obj_name = name + "(ID:%d)" % obj
log_content = u"管理员%s删除渠道%s" % \
(blue_html(OperatorName), red_html(obj_name))
page["list"].append([OperatorName, obj_name, log_content, timestamp])
return render_template('user_managed_log.html', page=page)
def search_agent_presentation():
start = request.args.get('beginDate', '')
end = request.args.get('endDate', '')
player_id = request.args.get('player_id', '')
channel = session['select_channel']
start_date = formatDatestamp(start)
end_date = formatDatestamp(end)
if start_date > end_date:
return jsonify(result='fail', msg=u'结束时间不能小于开始时间！')
where1 = ""
where2 = ""
if player_id:
where1 = "and give_id = %s" % player_id
where2 = "and recv_id = %s" % player_id
datas = {}
PIDS = {}
start_date1 = start_date
while start_date1 <= end_date:
## 查询当日赠送情况
date = time_util.formatDate(start_date1)
sql = '''
SELECT give_id, sum(if(recv_agent = 0, money, 0)), sum(if(recv_agent = 0, pump, 0)),
sum(if(recv_agent = 1, money, 0)), sum(if(recv_agent = 1, pump, 0))
FROM log_bank_give
WHERE time>=%d
AND time<=%d
AND give_agent = 1
%s
group by give_id
''' % (start_date1, start_date1 + 86400, where1)
for line in LogQry(channel).qry(sql):
PIDS[str(line[0])] = True
r = init_agent_daily_data()
r["dateStamp"] = date
r["total_down_coin"] = float(line[1])
r["total_down_coin_pump"] = float(line[2])
r["total_agent_present"] = float(line[3])
r["total_agent_present_pump"] = float(line[4])
datas[line[0]] = r
## 查询当日被赠送情况
sql = '''
SELECT recv_id, sum(if(give_agent = 0, money, 0)), sum(if(give_agent = 0, pump, 0)),
sum(if(give_agent = 1, money, 0)), sum(if(give_agent = 1, pump, 0))
FROM log_bank_give
WHERE time>=%d
AND time<=%d
AND recv_agent = 1
%s
group by recv_id
''' % (start_date1, start_date1 + 86400, where2)
for line in LogQry(channel).qry(sql):
PIDS[str(line[0])] = True
if datas.has_key(line[0]):
r = datas[line[0]]
else:
r = init_agent_daily_data()
r["dateStamp"] = date
r["total_up_coin"] = float(line[1])
r["total_up_coin_pump"] = float(line[2])
r["total_agent_recv"] = float(line[3])
r["total_agent_recv_pump"] = float(line[4])
datas[line[0]] = r
start_date1 += 86400
## 查询这些代理的昵称
pdatas = {}
if PIDS:
sql = '''
select id, nick
from player
where id in (%s)
''' % ",".join(PIDS.keys())
for line in LogQry(channel).qry(sql):
pdatas[line[0]] = line[1]
return jsonify(result='ok', data=datas, pdatas=pdatas)