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)