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_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_gold_rank():
user_type = request.args.get('user_type')
channel_id = session['select_channel']
backend_agent_sql = "SELECT pid FROM admin_agent_list"
agent_tup = LogQry(channel_id).qry(backend_agent_sql)
agent_id_list = list()
for agent_id in agent_tup:
agent_id_list.append(agent_id[0])
if user_type == '0':
user_type_str = "1=1"
elif user_type == '1':
if len(agent_id_list) == 0:
user_type_str = '1=1'
else:
user_type_str = 'id NOT IN %s' % str(tuple(agent_id_list))
else:
if len(agent_id_list) == 0:
user_type_str = '1=0'
else:
user_type_str = 'id IN %s' % str(tuple(agent_id_list))
search_sql = """SELECT id, nick, total_recharge_rmb, total_withdraw, coin, counter, last_login_ip, delivery_address
FROM player
WHERE %s
ORDER BY coin DESC LIMIT 100;""" % user_type_str
game_db_qrl = LogQry(channel_id)
coin_rank_datas = game_db_qrl.qry(search_sql)
player_rank_list = list()
pid_list = list()
player_dict = dict()
i = 1 # 排名
for pid, nick, total_recharge, total_withdraw, coin, counter, last_login_ip, delivery_address in coin_rank_datas:
pid_list.append(pid)
player_dict['pid'] = pid
player_dict['nick'] = nick
player_dict['total_recharge'] = total_recharge
player_dict['total_withdraw'] = total_withdraw
player_dict['coin'] = coin
player_dict['last_login_ip'] = last_login_ip
player_dict['counter'] = get_bank_coin(counter)
player_dict['game_count'] = 0
player_dict['rank'] = i
player_rank_list.append(player_dict)
player_dict = dict()
i += 1
if len(pid_list) == 1:
pid_str = '(' + str(pid_list[0]) + ')'
elif len(pid_list) == 0:
pid_str = '(0)'
else:
pid_str = str(tuple(pid_list))
game_count_sql = """SELECT pid, time, game_count
FROM t_player_subgame
WHERE pid IN %s
ORDER BY time desc""" \
% pid_str
game_count_datas = game_db_qrl.qry(game_count_sql)
game_count_dict = dict()
for pid, time_stamp, game_count in game_count_datas:
if not game_count_dict.has_key(pid):
game_count_dict[pid] = game_count
game_count_dict[pid] += game_count
for pid, game_count in game_count_dict.items():
for value_dict in player_rank_list:
if value_dict['pid'] == pid:
value_dict['game_count'] = game_count
return jsonify(result='ok', data=player_rank_list)
def search_withdraw_topup_rank():
start = request.args.get('beginDate')
end = request.args.get('endDate')
sort = request.args.get('recharge_withdraw')
channel_id = session['select_channel']
start_date = time_util.start(start)
end_date = time_util.end(end)
if start_date > end_date:
return jsonify(result='failed', msg=u'终止日期不能小于起始日期！')
if sort == '0':
sort_str = 'total_withdraw'
else:
sort_str = 'total_recharge'
search_sql = """SELECT * FROM (SELECT pid, (SELECT nick FROM player WHERE pid=id), (SELECT last_login_ip FROM player WHERE pid=id),
sum(coin) as total_withdraw, sum(cost) as total_recharge
FROM admin_recharge
WHERE time>=%d
AND time<%d
GROUP BY pid)t
ORDER BY t.%s desc LIMIT 100""" \
% (start_date, end_date, sort_str)
game_db_qrl = LogQry(int(channel_id))
give_search_sql = game_db_qrl.qry(search_sql)
player_rank_list = list()
pid_list = list()
i = 1 # 排名
for pid, nick, last_login_ip, total_withdraw, total_recharge in give_search_sql:
pid_list.append(pid)
player_dict = dict()
player_dict['pid'] = pid
player_dict['nick'] = nick
player_dict['last_login_ip'] = last_login_ip
player_dict['game_count'] = 0
player_dict['withdraw'] = float(total_withdraw)
player_dict['recharge'] = float(total_recharge)
player_dict['rank'] = i
player_rank_list.append(player_dict)
i += 1
if len(pid_list) == 1:
pid_str = '(' + str(pid_list[0]) + ')'
elif len(pid_list) == 0:
pid_str = '(0)'
else:
pid_str = str(tuple(pid_list))
start_ymd = int(time_util.formatTimeWithDesc(start_date, "%Y%m%d"))
end_ymd = int(time_util.formatTimeWithDesc(end_date + 86400, "%Y%m%d"))
game_count_sql = """SELECT pid, time, game_count
FROM t_player_subgame
WHERE time>=%d
AND time<%d
AND pid IN %s
ORDER BY time desc""" \
% (start_ymd, end_ymd, pid_str)
game_count_datas = game_db_qrl.qry(game_count_sql)
game_count_dict = dict()
for pid, time_stamp, game_count in game_count_datas:
if not game_count_dict.has_key(pid):
game_count_dict[pid] = game_count
game_count_dict[pid] += game_count
for pid, game_count in game_count_dict.items():
for value_dict in player_rank_list:
if value_dict['pid'] == pid:
value_dict['game_count'] = game_count
return jsonify(result='ok', data=player_rank_list)
def search_sell_buy_point_rank():
start = request.args.get('beginDate', '')
end = request.args.get('endDate', '')
user_type = request.args.get('user_type', '')
sort = request.args.get('point', '')
channel_id = session['select_channel']
start_date = time_util.start(start)
end_date = time_util.end(end)
if start_date > end_date:
return jsonify(result='failed', msg=u'终止日期不能小于起始日期！')
# 卖分排行榜
if sort == '1':
if user_type == '1':
user_type_str = " AND give_agent=0"
elif user_type == '2':
user_type_str = " AND give_agent=1"
else:
user_type_str = ""
give_search_sql = """SELECT * FROM (SELECT give_id, sum(money) as total_money,
(SELECT nick FROM player WHERE give_id=id), (SELECT last_login_ip FROM player WHERE give_id=id)
FROM log_bank_give
WHERE time>=%d
AND time<%d
AND ((give_agent=1 AND recv_agent=0) OR (give_agent=0 AND recv_agent=1))
%s
GROUP BY give_id) as t
ORDER BY t.total_money desc LIMIT 100""" \
% (start_date, end_date, user_type_str)
game_db_qrl = LogQry(int(channel_id))
bank_search_data = game_db_qrl.qry(give_search_sql)
player_rank_list = list()
player_dict = dict()
gid_list = list()
i = 1 # 排名
for give_id, total_money, nick, last_login_ip in bank_search_data:
gid_list.append(give_id)
player_dict['pid'] = give_id
player_dict['nick'] = nick
player_dict['last_login_ip'] = last_login_ip
player_dict['total_down_coin'] = float(total_money)
player_dict['game_count'] = 0
player_dict['total_up_coin'] = 0
player_dict['rank'] = i
player_rank_list.append(player_dict)
player_dict = dict()
i += 1
if len(gid_list) == 1:
pid_str = '(' + str(gid_list[0]) + ')'
elif len(gid_list) == 0:
pid_str = '(0)'
else:
pid_str = str(tuple(gid_list))
recv_search_sql = """SELECT recv_id, sum(money)
FROM log_bank_give
WHERE time>=%d
AND time<%d
AND recv_id IN %s
GROUP BY recv_id""" \
% (start_date, end_date, pid_str)
recv_player_datas = game_db_qrl.qry(recv_search_sql)
for rid, total_money in recv_player_datas:
for value_dict in player_rank_list:
if value_dict['pid'] == rid:
value_dict['total_up_coin'] = float(total_money)
start_ymd = int(time_util.formatTimeWithDesc(start_date, "%Y%m%d"))
end_ymd = int(time_util.formatTimeWithDesc(end_date + 86400, "%Y%m%d"))
game_count_sql = """SELECT pid, time, game_count
FROM t_player_subgame
WHERE time>=%d
AND time<%d
AND pid IN %s
ORDER BY time desc""" \
% (start_ymd, end_ymd, pid_str)
game_count_datas = game_db_qrl.qry(game_count_sql)
game_count_dict = dict()
for pid, time_stamp, game_count in game_count_datas:
if not game_count_dict.has_key(pid):
game_count_dict[pid] = game_count
game_count_dict[pid] += game_count
for pid, game_count in game_count_dict.items():
for value_dict in player_rank_list:
if value_dict['pid'] == pid:
value_dict['game_count'] = game_count
# 买分排行榜
else:
if user_type == '1':
user_type_str = " AND recv_agent=0"
elif user_type == '2':
user_type_str = " AND recv_agent=1"
else:
user_type_str = ""
recv_search_sql = """SELECT * FROM (SELECT recv_id, sum(money) as total_money,
(SELECT nick FROM player WHERE recv_id=id), (SELECT last_login_ip FROM player WHERE recv_id=id)
FROM log_bank_give
WHERE time>=%d
AND time<%d
AND ((give_agent=1 AND recv_agent=0) OR (give_agent=0 AND recv_agent=1))
%s
GROUP BY recv_id)t
ORDER BY t.total_money desc LIMIT 100""" \
% (start_date, end_date + 86400, user_type_str)
game_db_qrl = LogQry(int(channel_id))
bank_search_data = game_db_qrl.qry(recv_search_sql)
player_rank_list = list()
player_dict = dict()
rid_list = list()
i = 1 # 排名
for recv_id, total_money, nick, last_login_ip in bank_search_data:
rid_list.append(recv_id)
player_dict['pid'] = recv_id
player_dict['nick'] = nick
player_dict['last_login_ip'] = last_login_ip
player_dict['total_up_coin'] = float(total_money)
player_dict['game_count'] = 0
player_dict['total_down_coin'] = 0
player_dict['rank'] = i
player_rank_list.append(player_dict)
player_dict = dict()
i += 1
if len(rid_list) == 1:
pid_str = '(' + str(rid_list[0]) + ')'
elif len(rid_list) == 0:
pid_str = '(0)'
else:
pid_str = str(tuple(rid_list))
give_search_sql = """SELECT give_id, sum(money)
FROM log_bank_give
WHERE time>=%d
AND time<%d
AND give_id IN %s
GROUP BY give_id""" \
% (start_date, end_date, pid_str)
recv_player_datas = game_db_qrl.qry(give_search_sql)
for gid, total_money in recv_player_datas:
for value_dict in player_rank_list:
if value_dict['pid'] == gid:
value_dict['total_down_coin'] = float(total_money)
start_ymd = int(time_util.formatTimeWithDesc(start_date, "%Y%m%d"))
end_ymd = int(time_util.formatTimeWithDesc(end_date + 86400, "%Y%m%d"))
game_count_sql = """SELECT pid, time, game_count
FROM t_player_subgame
WHERE time>=%d
AND time<%d
AND pid IN %s
ORDER BY time desc""" \
% (start_ymd, end_ymd, pid_str)
game_count_datas = game_db_qrl.qry(game_count_sql)
game_count_dict = dict()
for pid, time_stamp, game_count in game_count_datas:
if not game_count_dict.has_key(pid):
game_count_dict[pid] = game_count
game_count_dict[pid] += game_count
for pid, game_count in game_count_dict.items():
for value_dict in player_rank_list:
if value_dict['pid'] == pid:
value_dict['game_count'] = game_count
return jsonify(result=0, data=player_rank_list)
def search_coin_change_data():
player_id = request.args.get('PlayerID', '')
Account = request.args.get('Account', '')
gameid = request.args.get('game')
start = request.args.get('beginDate', '')
end = request.args.get('endDate', '')
channel = session['select_channel']
size = int(request.args.get('size', ''))
offset = int(request.args.get('offset', ''))
start_date = time_util.formatTimestamp(start)
end_date = time_util.formatTimestamp(end)
use_index = "force index(time)"
if player_id:
use_index = "force index(pid)"
play_id_str = " AND pid=%s" % player_id
else:
play_id_str = ''
if Account:
use_index = "force index(pid)"
play_id_str = " AND pid = (select id from player where account_id = '%s') " % Account
if gameid == '0':
subgame_str = ""
else:
subgame_ename = game_parameter.get_subgame_enname(int(gameid))
subgame_str = " AND subgame LIKE '%s%%'" % subgame_ename
search_sql = """
SELECT time, pid, (SELECT nick FROM player WHERE id=pid), log_type, subgame, val, rest
FROM %s
%s
WHERE time>=%d
AND time<%d%s%s
ORDER BY time DESC LIMIT %s,%s;
""" % (get_table_log_coin(start_date), use_index, start_date, end_date, play_id_str, subgame_str, offset, size)
count_sql = """
SELECT count(1), ifnull(sum(val) , 0)
FROM %s
%s
WHERE time >= %d
AND time < %d%s%s
""" % (get_table_log_coin(start_date), use_index, start_date, end_date, play_id_str, subgame_str)
log_db = LogQry(channel)
item_change_data = log_db.qry(search_sql)
total_count = log_db.qry(count_sql)[0][0]
total_val = int(log_db.qry(count_sql)[0][1])
page_datas = list()
log_coin_dict = game_parameter.get_coin_log_define()
subgame_dict = game_parameter.get_subgame_name_enname_d()
for timeStamp, pid, nick, log_type, subgame, \
val, rest in item_change_data:
item_change_dict = dict()
item_change_dict['timeStamp'] = time_util.formatDateTime(timeStamp)
item_change_dict['pid'] = pid
item_change_dict['nick'] = nick
item_change_dict['log_type'] = game_parameter.get_coin_log_name(log_coin_dict, log_type)
if subgame:
subgame_k = subgame.split('-')[0]
room = subgame.split('-')[1]
subgame_name = game_parameter.get_subgame_name_by_ename(subgame_dict, subgame_k)
item_change_dict['subgame'] = "%s(%s)" % (subgame_name, room)
else:
subgame_k = ""
room = ""
item_change_dict['subgame'] = ""
item_change_dict['val'] = val
item_change_dict['rest'] = rest
page_datas.append(item_change_dict)
## 做下特殊处理 排序下
page_datas.sort(key=lambda x: [x['timeStamp'], x['val']], reverse=True)
return jsonify({"errcode": 0, "dataLength": total_count, "rowDatas": page_datas, "total_val": total_val})
def search_item_change_data():
player_id = request.args.get('PlayerID', '')
item_id = request.args.get('game_item', '')
start = request.args.get('beginDate', '')
end = request.args.get('endDate', '')
offset = request.args.get('offset')
size = request.args.get('size')
channel = session['select_channel']
start_date = time_util.start(start)
end_date = time_util.end(end)
if start_date > end_date:
return jsonify(result='fail', msg=u'结束日期不能小于开始日期！')
if player_id:
play_id_str = " AND pid='%s'" % player_id
else:
play_id_str = ''
if item_id:
item_id_str = " AND itemid='%s'" % item_id
else:
item_id_str = ''
search_sql = """SELECT time, pid, (SELECT nick FROM player WHERE id=pid), log_type, itemid, rest
FROM log_item
WHERE time>=%d
AND time<%d%s%s
ORDER BY time DESC LIMIT %s,%s;""" \
% (start_date, end_date, play_id_str, item_id_str, offset, size)
search_count = """SELECT count(1)
FROM log_item
WHERE time>=%d
AND time<%d%s;""" \
% (start_date, end_date, play_id_str)
log_db = LogQry(channel)
item_change_data = log_db.qry(search_sql)
page_count = log_db.qry(search_count)[0][0]
log_coin_dict = game_parameter.get_coin_log_define()
page_datas = list()
item_change_dict = dict()
for timeStamp, pid, nick, log_type, itemid, rest in item_change_data:
item_change_dict['timeStamp'] = timeStamp
item_change_dict['pid'] = pid
item_change_dict['nick'] = nick
item_change_dict['log_type'] = game_parameter.get_coin_log_name(log_coin_dict, log_type)
item_change_dict['itemid'] = items_dict.get(itemid)
item_change_dict['rest'] = rest
page_datas.append(item_change_dict)
item_change_dict = dict()
pages_dict = dict()
pages_dict['page_count'] = page_count
pages_dict['page_datas'] = page_datas
return jsonify(result='ok', data=pages_dict)
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 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)