def retrieve_activity():
"""查询活动"""
# 获取参数
channel_id = session['select_channel']
activity_content = request.args.get('activity_content')
user_id = request.args.get('user_id')
begin_time = request.args.get('beginDate')
end_time = request.args.get('endDate')
# 处理时间
begin_time = time_util.start(begin_time)
end_time = time_util.end(end_time)
# 校验参数
if begin_time >= end_time:
return jsonify(result='fail', msg=u'结束时间不能小于开始时间！')
# 从数据库获取并处理数据
activity_content_str = ''
user_id_str = ''
if activity_content:
activity_content_str = ' AND activity_content LIKE "%%%s%%"' % activity_content
if user_id:
user_id_str = ' AND user_id="%s"' % user_id
retrieve_activity_sql = """SELECT priority,id,user_id,activity_content,activity_type,
entry_fee,begin_time,end_time,status
FROM admin_activity
WHERE ((begin_time>=%s AND begin_time<=%s)
OR (end_time>=%s AND end_time<=%s))
%s%s
ORDER BY status DESC;""" \
% (begin_time, end_time, begin_time, end_time, activity_content_str, user_id_str)
activity_datas = LogQry(channel_id).qry(retrieve_activity_sql)
datas = []
for priority, activity_id, user_id, activity_content, activity_type, \
entry_fee, begin_time, end_time, status in activity_datas:
activity_dict = dict()
activity_dict['priority'] = priority
activity_dict['activity_id'] = activity_id
activity_dict['user_id'] = user_id
activity_dict['activity_content'] = activity_content.replace(
'\n', '<br>')
activity_dict['activity_type'] = activity_type_map[activity_type]
activity_dict['entry_fee'] = '' if entry_fee is None else entry_fee
activity_dict['begin_time'] = time_util.formatDateTime(begin_time)
activity_dict['end_time'] = time_util.formatDateTime(end_time)
if status == TAKE_EFFECT: # 如果状态为生效，再判断一下当前时间与活动实际时间是否生效，若失效则修改状态
if not begin_time < time_util.now_sec() < end_time:
status = LOSE_EFFICACY
update_sql = """UPDATE admin_activity
SET status=%s
WHERE id=%s;""" \
% (status, activity_id)
LogQry(channel_id).execute(update_sql)
activity_dict['status'] = activity_status_map[status]
datas.append(activity_dict)
# 返回模版和数据
return jsonify(result='ok', data=datas)
def retrieve_mail():
"""查询邮件"""
# 获取参数
mail_title = request.args.get('mail_title')
user_id = request.args.get('user_id')
channel_id = int(session['select_channel'])
begin_time = request.args.get('beginDate')
end_time = request.args.get('endDate')
# 处理时间
begin_time = time_util.start(begin_time)
end_time = time_util.end(end_time)
# 校验参数
if begin_time >= end_time:
return jsonify(result='fail', msg=u'结束时间不能小于开始时间！')
# 从数据库获取并处理数据
mail_title_str = ''
user_id_str = ''
if mail_title:
mail_title_str = ' AND mail_title LIKE "%%%s%%"' % mail_title
if user_id:
user_id_str = " AND user_id='%s'" % user_id
retrieve_mail_sql = """SELECT id,user_id,push_player_id,mail_title,mail_content,
mail_accessories,status,push_time,expire
FROM admin_mail
WHERE (push_time>=%s AND push_time<=%s)
OR push_time=0
%s%s
ORDER BY status;""" \
% (begin_time, end_time, mail_title_str, user_id_str)
mail_datas = LogQry(channel_id).qry(retrieve_mail_sql)
datas = []
status_num = {0: u'待发送', 1: u'已发送'}
for mail_id, user_id, push_player_id, mail_title, mail_content, \
mail_accessories, status, push_time, expire in mail_datas:
mails_dict = dict()
mails_dict['mail_id'] = mail_id
mails_dict['user_id'] = user_id
mails_dict['push_player_id'] = push_player_id
mails_dict['mail_title'] = mail_title
mails_dict['mail_content'] = mail_content.replace('\n', '<br>')
mails_dict['push_time'] = time_util.formatDateTime(
push_time) if push_time > 0 else ''
mails_dict['mail_accessories'] = id_map_property(mail_accessories)
mails_dict['expire'] = expire if expire else ''
mails_dict['status'] = status_num[status]
datas.append(mails_dict)
# 高级用户可以操作邮件的发送按钮
is_high_level_user = False
if session.get('access_level') == 1:
is_high_level_user = True
# 返回数据
return jsonify(result='ok', data=datas, is_high=is_high_level_user)
def query_marquee():
"""查询跑马灯"""
# 获取参数
marquee_content = request.args.get('marquee_content')
user_id = request.args.get('user_id')
channel_id = session['select_channel']
begin_time = request.args.get('beginDate')
end_time = request.args.get('endDate')
# 处理日期
begin_time = time_util.start(begin_time)
end_time = time_util.end(end_time)
# 校验参数
if begin_time >= end_time:
return jsonify(result='fail', msg=u'结束时间不能小于开始时间！')
# 从数据库获取数据，并组织参数
marquee_content_str = ""
user_id_str = ""
if marquee_content:
marquee_content_str = " AND marquee_content LIKE '%%%s%%'" % marquee_content
if user_id:
user_id_str = " AND user_id='%s'" % user_id
query_sql = """SELECT id,user_id,marquee_content,push_times,begin_time,
end_time,status
FROM admin_marquee
WHERE ((begin_time>=%s AND begin_time<=%s)
OR (end_time>=%s AND end_time<=%s))
%s%s
ORDER BY status DESC;""" \
% (begin_time, end_time, begin_time, end_time, marquee_content_str, user_id_str)
marquee_datas = LogQry(channel_id).qry(query_sql)
datas = []
for marquee_id, user_id, marquee_content, push_times, begin_time, \
end_time, status in marquee_datas:
marquee_dict = dict()
marquee_dict['marquee_id'] = marquee_id
marquee_dict['user_id'] = user_id
marquee_dict['marquee_content'] = marquee_content.replace('\n', '<br>')
marquee_dict['push_times'] = u'不限' if push_times == -1 else push_times
marquee_dict['begin_time'] = time_util.formatDateTime(begin_time)
marquee_dict['end_time'] = time_util.formatDateTime(end_time)
marquee_dict['status'] = status_map[status]
datas.append(marquee_dict)
# 返回数据
return jsonify(result='ok', data=datas)
def show_manipulate_log_data():
start = request.form.get('beginDate')
end = request.form.get('endDate')
channel = session['select_channel']
start = time_util.start(start)
end = time_util.end(end)
sql = '''
select log_type, operator, obj, val, timestamp
from admin_opt_log
where channel = %d and timestamp >= %d and timestamp <= %d and maintype = %d
order by timestamp desc limit 30
''' % (channel, start, end, log_main_type_d["win_ctl"])
page = deepcopy(init_page)
page["beginDate"] = start
page["endDate"] = end
page["SelectChannel"] = channel
## 查询子游戏列表
SubGameList = game_parameter.get_subgame_list()
for log_type, operator, obj, val, timestamp in LogQry(channel).qry(sql):
OperatorName = SqlOperate().select(
"select name from user where id = %d" % operator)[0][0]
if log_type == log_type_d["single_ctl"]:
log_type_str = u"单控"
obj_sql = "select nick from player where id = %d" % obj
try:
obj_name = LogQry(channel).qry(obj_sql)[0][0]
except:
obj_name = ""
log_content = u"管理员%s对玩家%s(%s)设置了保护值：%s" % \
(blue_html(OperatorName), blue_html(obj_name), blue_html(obj), red_html(val))
elif log_type == log_type_d["full_game_ctl"]:
log_type_str = u"控大盘"
[room_name, water] = val.split("_")
log_content = u"管理员%s对%s(%s)水池调整成：%s" % \
(blue_html(OperatorName), blue_html(game_parameter.get_subgame_by_id(SubGameList, obj)),
blue_html(room_name), red_html(water))
page["list"].append(
[OperatorName, log_type_str, log_content, timestamp])
return jsonify(result='ok', data=page)
def search_annouce_game():
title = request.args.get('announce_title')
start_date = request.args.get('beginDate')
end_date = request.args.get('endDate')
channel = int(session['select_channel'])
start_date = time_util.start(start_date)
end_date = time_util.end(end_date)
if start_date >= end_date:
return jsonify(result='fail', msg=u'结束时间不能小于开始时间！')
if title:
title_str = " AND title='%s'" % title
else:
title_str = ""
select_sql = """
SELECT id, channel, title, priority, content_image_url, push_times, start_date, end_date, status
FROM admin_announcement
WHERE channel=%s%s
AND status=1
AND ((start_date>=%s and start_date<=%s)
OR (end_date>=%s and end_date<=%s));
""" % (channel, title_str, start_date, end_date, start_date, end_date)
game_announce_datas = LogQry(channel).qry(select_sql)
datas = list()
Now = time_util.now_sec()
for ann_id, channel, title, priority, content_image_url, push_times, start_date, end_date, status in game_announce_datas:
announce_dict = dict()
announce_dict['id'] = ann_id
announce_dict['title'] = title
announce_dict['priority'] = priority
announce_dict['content_image_url'] = content_image_url
announce_dict['push_times'] = push_times
announce_dict['start_date'] = time_util.formatDateTime(start_date)
announce_dict['end_date'] = time_util.formatDateTime(end_date)
announce_dict['status'] = status
if status == 1:
## 如果状态是1 得判断是否生效状态
if Now >= start_date and Now <= end_date:
announce_dict['status'] = 2
if Now >= end_date:
announce_dict['status'] = 3
datas.append(announce_dict)
return jsonify(result='ok', data=datas)
def show_manage_log_data():
start = request.args.get('beginDate')
end = request.args.get('endDate')
pid = request.args.get('PlayerID')
nick = request.args.get('NickName')
channel = session['select_channel']
size = int(request.args.get('size', ''))
offset = int(request.args.get('offset', ''))
start = time_util.start(start)
end = time_util.end(end)
# 校验参数
if pid:
try:
int(pid)
except ValueError:
return jsonify(result='fail', msg=u'玩家ID为整数纯数字！')
if pid and nick:
return jsonify(result='fail', msg=u'玩家ID与玩家昵称只能输入其一！')
if start >= end:
return jsonify(result='fail', mag=u'结束日期不能小于开始日期！')
# 转换昵称
if nick:
retrieve_sql = """SELECT id FROM player WHERE nick='%s';""" % nick
pid = LogQry(channel).qry(retrieve_sql)[0][0]
if pid or nick:
sql = '''
select log_type, operator, obj, val, timestamp
from admin_opt_log
where channel = %d and timestamp >= %d and timestamp <= %d and maintype = %d and obj = %s
order by timestamp desc
LIMIT %d,%d
''' % (channel, start, end, log_main_type_d["player"], pid, offset,
size)
count_sql = '''
select count(1)
from admin_opt_log
where channel = %d and timestamp >= %d and timestamp <= %d and maintype = %d and obj = %s
order by timestamp desc;
''' % (channel, start, end, log_main_type_d["player"], pid)
else:
sql = '''
select log_type, operator, obj, val, timestamp
from admin_opt_log
where channel = %d and timestamp >= %d and timestamp <= %d and maintype = %d
order by timestamp desc
LIMIT %d,%d
''' % (channel, start, end, log_main_type_d["player"], offset,
size)
count_sql = '''
select count(1)
from admin_opt_log
where channel = %d and timestamp >= %d and timestamp <= %d and maintype = %d
order by timestamp desc;
''' % (channel, start, end, log_main_type_d["player"])
total_count = LogQry(channel).qry(count_sql)
page = deepcopy(init_page)
page["beginDate"] = start
page["endDate"] = end
page["SelectChannel"] = channel
for log_type, operator, obj, val, timestamp in LogQry(channel).qry(sql):
OperatorName = SqlOperate().select(
"select name from user where id = %d" % operator)[0][0]
if log_type == log_type_d["white"]:
obj_sql = "select nick from player where id = %d" % obj
if LogQry(channel).qry(obj_sql):
nick = LogQry(channel).qry(obj_sql)[0][0]
else:
nick = ''
obj_name = nick + "(ID:%d)" % obj
log_content = u"管理员%s对玩家%s加入白名单" % \
(blue_html(OperatorName), red_html_link(obj, nick, channel, obj_name))
elif log_type == log_type_d["black"]:
obj_sql = "select nick from player where id = %d" % obj
if LogQry(channel).qry(obj_sql):
nick = LogQry(channel).qry(obj_sql)[0][0]
else:
nick = ''
obj_name = nick + "(ID:%d)" % obj
log_content = u"管理员%s对玩家%s加入黑名单" % \
(blue_html(OperatorName), red_html_link(obj, nick, channel, obj_name))
elif log_type == log_type_d["forbid"]:
obj_sql = "select nick from player where id = %d" % obj
if LogQry(channel).qry(obj_sql):
nick = LogQry(channel).qry(obj_sql)[0][0]
else:
nick = ''
obj_name = nick + "(ID:%d)" % obj
log_content = u"管理员%s对玩家%s封号,原因为:%s" % \
(blue_html(OperatorName), red_html_link(obj, nick, channel, obj_name), red_html(val))
elif log_type == log_type_d["cancel_forbid"]:
obj_sql = "select nick from player where id = %d" % obj
if LogQry(channel).qry(obj_sql):
nick = LogQry(channel).qry(obj_sql)[0][0]
else:
nick = ''
obj_name = nick + "(ID:%d)" % obj
log_content = u"管理员%s对玩家%s解封" % \
(blue_html(OperatorName), red_html_link(obj, nick, channel, obj_name))
elif log_type == log_type_d["cold"]:
obj_sql = "select nick from player where id = %d" % obj
if LogQry(channel).qry(obj_sql):
nick = LogQry(channel).qry(obj_sql)[0][0]
else:
nick = ''
obj_name = nick + "(ID:%d)" % obj
log_content = u"管理员%s对玩家%s资金冻结" % \
(blue_html(OperatorName), red_html_link(obj, nick, channel, obj_name))
elif log_type == log_type_d["cancel_cold"]:
obj_sql = "select nick from player where id = %d" % obj
if LogQry(channel).qry(obj_sql):
nick = LogQry(channel).qry(obj_sql)[0][0]
else:
nick = ''
obj_name = nick + "(ID:%d)" % obj
log_content = u"管理员%s对玩家%s资金解冻" % \
(blue_html(OperatorName), red_html_link(obj, nick, channel, obj_name))
elif log_type == log_type_d["alter_pass"]:
obj_sql = "select nick from player where id = %d" % obj
if LogQry(channel).qry(obj_sql):
nick = LogQry(channel).qry(obj_sql)[0][0]
else:
nick = ''
obj_name = nick + "(ID:%d)" % obj
log_content = u"管理员%s对玩家%s修改登录密码" % \
(blue_html(OperatorName), red_html_link(obj, nick, channel, obj_name))
elif log_type == log_type_d["send_coin"]:
obj_sql = "select nick from player where id = %d" % obj
if LogQry(channel).qry(obj_sql):
nick = LogQry(channel).qry(obj_sql)[0][0]
else:
nick = ''
obj_name = nick + "(ID:%d)" % obj
log_content = u"管理员%s对玩家%s赠送金币%s" % \
(blue_html(OperatorName), red_html_link(obj, nick, channel, obj_name), red_html(val))
elif log_type == log_type_d["send_item"]:
obj_sql = "select nick from player where id = %d" % obj
if LogQry(channel).qry(obj_sql):
nick = LogQry(channel).qry(obj_sql)[0][0]
else:
nick = ''
obj_name = nick + "(ID:%d)" % obj
log_content = u"管理员%s对玩家%s赠送喇叭%s" % \
(blue_html(OperatorName), red_html_link(obj, nick, channel, obj_name), red_html(val))
elif log_type == log_type_d["del_white"]:
obj_sql = "select nick from player where id = %d" % obj
if LogQry(channel).qry(obj_sql):
nick = LogQry(channel).qry(obj_sql)[0][0]
else:
nick = ''
obj_name = nick + "(ID:%d)" % obj
log_content = u"管理员%s对玩家%s移除白名单" % \
(blue_html(OperatorName), red_html_link(obj, nick, channel, obj_name))
elif log_type == log_type_d["del_black"]:
obj_sql = "select nick from player where id = %d" % obj
if LogQry(channel).qry(obj_sql):
nick = LogQry(channel).qry(obj_sql)[0][0]
else:
nick = ''
obj_name = nick + "(ID:%d)" % obj
log_content = u"管理员%s对玩家%s移除黑名单" % \
(blue_html(OperatorName), red_html_link(obj, nick, channel, obj_name))
elif log_type == log_type_d["alter_bank_pwd"]:
obj_sql = "select nick from player where id = %d" % obj
if LogQry(channel).qry(obj_sql):
nick = LogQry(channel).qry(obj_sql)[0][0]
else:
nick = ''
obj_name = nick + "(ID:%d)" % obj
log_content = u"管理员%s对玩家%s修改保险柜密码" % \
(blue_html(OperatorName), red_html_link(obj, nick, channel, obj_name))
elif log_type == log_type_d["alter_nick"]:
obj_sql = "select nick from player where id = %d" % obj
if LogQry(channel).qry(obj_sql):
nick = LogQry(channel).qry(obj_sql)[0][0]
else:
nick = ''
obj_name = nick + "(ID:%d)" % obj
log_content = u"管理员%s对玩家%s修改昵称为：%s" % \
(blue_html(OperatorName), red_html_link(obj, nick, channel, obj_name), blue_html(val))
elif log_type == log_type_d["alter_vip"]:
obj_sql = "select nick from player where id = %d" % obj
if LogQry(channel).qry(obj_sql):
nick = LogQry(channel).qry(obj_sql)[0][0]
else:
nick = ''
obj_name = nick + "(ID:%d)" % obj
log_content = u"管理员%s对玩家%s调整了会员层级为%s" % \
(blue_html(OperatorName), red_html_link(obj, nick, channel, obj_name), red_html(val))
elif log_type == log_type_d["alter_zfb"]:
obj_sql = "select nick from player where id = %d" % obj
if LogQry(channel).qry(obj_sql):
nick = LogQry(channel).qry(obj_sql)[0][0]
else:
nick = ''
obj_name = nick + "(ID:%d)" % obj
log_content = u"管理员%s对玩家%s绑定了支付宝%s" % \
(blue_html(OperatorName), red_html_link(obj, nick, channel, obj_name), red_html(val))
elif log_type == log_type_d["alter_bank"]:
obj_sql = "select nick from player where id = %d" % obj
if LogQry(channel).qry(obj_sql):
nick = LogQry(channel).qry(obj_sql)[0][0]
else:
nick = ''
obj_name = nick + "(ID:%d)" % obj
log_content = u"管理员%s对玩家%s绑定了银行卡%s" % \
(blue_html(OperatorName), red_html_link(obj, nick, channel, obj_name), red_html(val))
elif log_type == log_type_d["alter_code"]:
obj_sql = "select nick from player where id = %d" % obj
if LogQry(channel).qry(obj_sql):
nick = LogQry(channel).qry(obj_sql)[0][0]
else:
nick = ''
obj_name = nick + "(ID:%d)" % obj
log_content = u"管理员%s对玩家%s修改了受邀码为%s" % \
(blue_html(OperatorName), red_html_link(obj, nick, channel, obj_name), red_html(val))
page["list"].append({
"OperatorName": OperatorName,
"obj_name": obj_name,
"log_content": log_content,
"timestamp": timestamp
})
return jsonify({
"result": "ok",
"errcode": 0,
"dataLength": total_count,
"rowDatas": page["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_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_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 search_game_data_compare():
start1 = request.args.get('beginDate1', '')
end1 = request.args.get('endDate1', '')
start2 = request.args.get('beginDate2', '')
end2 = request.args.get('endDate2', '')
channel = session['select_channel']
start_date1 = time_util.start(start1)
end_date1 = time_util.end(end1)
start_date2 = time_util.start(start2)
end_date2 = time_util.end(end2)
if start_date1 > end_date1 or start_date2 > end_date2:
return jsonify(result='fail', msg=u'结束时间不能小于开始时间！')
# --------------------------
# 页面左边日期输入框所查数据
start_time1 = int(time_util.formatTimeWithDesc(start_date1, "%Y%m%d"))
end_time1 = int(time_util.formatTimeWithDesc(end_date1, "%Y%m%d"))
game_normal_sql_1 = """
SELECT time, os, reg_count, active_count, total_recharge, game_win, withdraw
FROM t_system
WHERE time>=%d AND time<%d
order by time desc;
""" % (start_time1, end_time1)
print 'game_normal_sql_1', game_normal_sql_1
print LogQry(channel).qry(game_normal_sql_1)
left_record = {
"reg_count": 0,
"active_count": 0,
"game_win": 0,
"up_coin": 0,
"down_coin": 0,
"recharge": 0,
"withdraw": 0
}
left_pre_date = 0
for time_int, platform, reg_count, active_count, total_recharge, game_win, withdraw in LogQry(channel).qry(
game_normal_sql_1):
left_record['reg_count'] += reg_count
left_record['active_count'] += active_count
left_record['recharge'] += total_recharge
left_record['game_win'] += game_win
left_record['withdraw'] += withdraw
bank_give_sql_1 = """SELECT give_agent, recv_agent, money
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));
""" % (start_date1, end_date1 + 86400)
for give_agent, recv_agent, money in LogQry(channel).qry(bank_give_sql_1):
if give_agent == 1:
left_record['down_coin'] += money
elif recv_agent == 1:
left_record['up_coin'] += money
print left_record, "left_record"
# --------------------------
# 页面右边日期输入框所查数据
start_time2 = int(time_util.formatTimeWithDesc(start_date2, "%Y%m%d"))
end_time2 = int(time_util.formatTimeWithDesc(end_date2, "%Y%m%d"))
game_normal_sql_2 = """
SELECT time, reg_count, active_count, total_recharge, game_win, withdraw
FROM t_system
WHERE time>=%d AND time<%d
order by time desc;
""" % (start_time2, end_time2)
print game_normal_sql_2, start_time2
right_record = {
"reg_count": 0,
"active_count": 0,
"game_win": 0,
"up_coin": 0,
"down_coin": 0,
"recharge": 0,
"withdraw": 0
}
right_pre_date = 0
for time_int, reg_count, active_count, total_recharge, game_win, withdraw in LogQry(channel).qry(
game_normal_sql_2):
right_record['reg_count'] += reg_count
right_record['active_count'] += active_count
right_record['recharge'] += total_recharge
right_record['game_win'] += game_win
right_record['withdraw'] += withdraw
bank_give_sql_2 = """SELECT give_agent, recv_agent, money
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));
""" % (start_date2, end_date2 + 86400)
for give_agent, recv_agent, money in LogQry(channel).qry(bank_give_sql_2):
if give_agent == 1:
right_record['down_coin'] += money
elif recv_agent == 1:
right_record['up_coin'] += money
return jsonify(result='ok', pre=left_record, cur=right_record)
def recharge_discounts_retrieve():
"""充值优惠设置查询"""
# 获取参数
channel_id = session['select_channel']
activity_content = request.args.get('activity_title')
user_id = request.args.get('user_id')
begin_time = request.args.get('beginDate')
end_time = request.args.get('endDate')
# 处理时间
begin_time = time_util.start(begin_time)
end_time = time_util.end(end_time)
# 校验并处理参数
if begin_time >= end_time:
return jsonify(result='fail', msg=u'结束时间不能小于开始时间！')
where = ''
if user_id:
where += " AND user_id='%s'" % user_id
if activity_content:
where += " AND activity_content like '%%%s%%'" % activity_content
# 从数据库获取数据
retrieve_sql = """SELECT priority,id,user_id,activity_content,activity_type,
participation_member,request_times,max_add_recharge,journal_require,begin_time,
end_time,status
FROM admin_recharge_discounts
WHERE ((begin_time>=%s AND begin_time<=%s)
OR (end_time>=%s AND end_time<=%s))
%s
ORDER BY status DESC;""" \
% (begin_time, end_time, begin_time, end_time, where)
data = LogQry(channel_id).qry(retrieve_sql)
# 处理数据
data_list = list()
for priority, activity_id, user_id, activity_content, activity_type, \
participation_member, request_times, max_add_recharge, journal_require, begin_time, \
end_time, status in data:
data_dict = dict()
data_dict['priority'] = priority
data_dict['activity_id'] = activity_id
data_dict['user_id'] = user_id
data_dict['activity_content'] = activity_content.replace('\n', '<br>')
data_dict['activity_type'] = activity_type_map[activity_type]
data_dict['participation_member'] = participation_member_map[
participation_member]
data_dict['request_times'] = request_times
data_dict[
'max_add_recharge'] = max_add_recharge if max_add_recharge else ''
data_dict['journal_require'] = journal_require
data_dict['begin_time'] = time_util.formatDateTime(begin_time)
data_dict['end_time'] = time_util.formatDateTime(end_time)
if status == TAKE_EFFECT: # 如果状态为生效，再判断一下当前时间与活动实际时间是否生效，若失效则修改状态
if not begin_time < time_util.now_sec() < end_time:
status = LOSE_EFFICACY
update_sql = """UPDATE admin_recharge_discounts
SET status=%s
WHERE id=%s;""" \
% (status, activity_id)
LogQry(channel_id).execute(update_sql)
data_dict['status'] = status_map[status]
data_dict['status_num'] = status # 用于调字体颜色
data_list.append(data_dict)
# 返回模版与数据
return jsonify(result='ok', data=data_list)