X-Git-Url: http://de.git.xonotic.org/?p=xonotic%2Fxonstat.git;a=blobdiff_plain;f=xonstat%2Fviews%2Fplayer.py;h=41af4f11e2d2b4a9bb177de4474c4ece67dfbaea;hp=ce43fdf6252f93b680abfc8272bad41ef4eb296a;hb=ea20f202cc0525006d3dcc013f905840a7345998;hpb=8a0cfb47ee3f03476ba0301ebcfe7b41a1e67e08 diff --git a/xonstat/views/player.py b/xonstat/views/player.py index ce43fdf..41af4f1 100644 --- a/xonstat/views/player.py +++ b/xonstat/views/player.py @@ -1,14 +1,19 @@ import datetime import logging +from calendar import timegm +from collections import namedtuple +from urllib import unquote + import pyramid.httpexceptions import sqlalchemy as sa +import sqlalchemy.sql.expression as expr import sqlalchemy.sql.functions as func -from calendar import timegm -from collections import namedtuple from webhelpers.paginate import Page -from xonstat.models import * -from xonstat.util import page_url, to_json, pretty_date, datetime_seconds +from xonstat.models import DBSession, Server, Map, Game, PlayerWeaponStat, Player, Hashkey +from xonstat.models import PlayerElo, PlayerCaptime, PlayerMedal, GameType +from xonstat.models.player import PlayerCapTime from xonstat.util import is_cake_day, verify_request +from xonstat.util import page_url, to_json, pretty_date, datetime_seconds from xonstat.views.helpers import RecentGame, recent_games_q log = logging.getLogger(__name__) @@ -87,7 +92,8 @@ def get_games_played(player_id): "FROM games g, " "player_game_stats pgs " "WHERE g.game_id = pgs.game_id " - "AND pgs.player_id = :player_id) win_loss " + "AND pgs.player_id = :player_id " + "AND g.players @> ARRAY[:player_id]) win_loss " "GROUP BY game_type_cd " ).params(player_id=player_id).all() @@ -116,6 +122,7 @@ def get_games_played(player_id): # sort the resulting list by # of games played games_played = sorted(games_played, key=lambda x:x.games) games_played.reverse() + return games_played @@ -165,6 +172,7 @@ def get_overall_stats(player_id): "player_game_stats pgs " "WHERE g.game_id = pgs.game_id " "AND g.game_type_cd = gt.game_type_cd " + "AND g.players @> ARRAY[:player_id] " "AND pgs.player_id = :player_id " "GROUP BY g.game_type_cd, game_type_descr " "UNION " @@ -287,6 +295,7 @@ def get_fav_maps(player_id, game_type_cd=None): "maps m " "WHERE g.game_id = pgs.game_id " "AND g.map_id = m.map_id " + "AND g.players @> ARRAY[:player_id]" "AND pgs.player_id = :player_id " "GROUP BY g.game_type_cd, " "m.map_id, " @@ -343,6 +352,7 @@ def get_ranks(player_id): "from player_ranks " "group by game_type_cd) overall " "where pr.game_type_cd = overall.game_type_cd " + "and max_rank > 1 " "and player_id = :player_id " "order by rank").\ params(player_id=player_id).all() @@ -406,24 +416,6 @@ def get_recent_games(player_id, limit=10): return recent_games -def get_recent_weapons(player_id): - """ - Returns the weapons that have been used in the past 90 days - and also used in 5 games or more. - """ - cutoff = datetime.datetime.utcnow() - datetime.timedelta(days=90) - recent_weapons = [] - for weapon in DBSession.query(PlayerWeaponStat.weapon_cd, func.count()).\ - filter(PlayerWeaponStat.player_id == player_id).\ - filter(PlayerWeaponStat.create_dt > cutoff).\ - group_by(PlayerWeaponStat.weapon_cd).\ - having(func.count() > 4).\ - all(): - recent_weapons.append(weapon[0]) - - return recent_weapons - - def get_accuracy_stats(player_id, weapon_cd, games): """ Provides accuracy for weapon_cd by player_id for the past N games. @@ -505,6 +497,23 @@ def get_damage_stats(player_id, weapon_cd, games): return (avg, dmgs) +def get_player_medals(player_id): + """Retrieves the list of medals the player has received from tournaments or + other contests.""" + try: + medals = DBSession.query(PlayerMedal)\ + .filter(PlayerMedal.player_id==player_id)\ + .order_by(PlayerMedal.place)\ + .order_by(PlayerMedal.create_dt)\ + .all() + + return medals + + except Exception as e: + log.debug(e) + return [] + + def player_info_data(request): player_id = int(request.matchdict['id']) if player_id <= 2: @@ -519,21 +528,14 @@ def player_info_data(request): fav_maps = get_fav_maps(player_id) elos = get_elos(player_id) ranks = get_ranks(player_id) + medals = get_player_medals(player_id) recent_games = get_recent_games(player_id) - recent_weapons = get_recent_weapons(player_id) cake_day = is_cake_day(player.create_dt) except Exception as e: - player = None - games_played = None - overall_stats = None - fav_maps = None - elos = None - ranks = None - recent_games = None - recent_weapons = [] - cake_day = False - ## do not raise exceptions here (only for debugging) + raise pyramid.httpexceptions.HTTPNotFound + + ## do not raise application exceptions here (only for debugging) # raise e return {'player':player, @@ -542,8 +544,8 @@ def player_info_data(request): 'fav_maps':fav_maps, 'elos':elos, 'ranks':ranks, + 'medals':medals, 'recent_games':recent_games, - 'recent_weapons':recent_weapons, 'cake_day':cake_day, } @@ -585,11 +587,7 @@ def player_info_json(request): for gt,mapinfo in player_info['fav_maps'].items(): fav_maps[gt] = to_json(mapinfo) - recent_games = [] - for game in player_info['recent_games']: - recent_games.append(to_json(game)) - - #recent_weapons = player_info['recent_weapons'] + recent_games = [g.to_dict() for g in player_info['recent_games']] return [{ 'player': player, @@ -599,14 +597,14 @@ def player_info_json(request): 'elos': elos, 'ranks': ranks, 'recent_games': recent_games, - # 'recent_weapons': recent_weapons, - 'recent_weapons': ['not implemented'], }] - #return [{'status':'not implemented'}] def player_game_index_data(request): - player_id = request.matchdict['player_id'] + try: + player_id = int(request.matchdict['player_id']) + except: + player_id = -1 game_type_cd = None game_type_descr = None @@ -646,6 +644,7 @@ def player_game_index_data(request): games_played = get_games_played(player_id) except Exception as e: + raise e player = None games = None game_type_cd = None @@ -788,7 +787,17 @@ def player_damage_json(request): def player_hashkey_info_data(request): + # hashkey = request.matchdict['hashkey'] + + # the incoming hashkey is double quoted, and WSGI unquotes once... + # hashkey = unquote(hashkey) + + # if using request verification to obtain the hashkey (idfp, status) = verify_request(request) + log.debug("d0_blind_id verification: idfp={0} status={1}\n".format(idfp, status)) + + log.debug("\n----- BEGIN REQUEST BODY -----\n" + request.body + + "----- END REQUEST BODY -----\n\n") # if config is to *not* verify requests and we get nothing back, this # query will return nothing and we'll 404. @@ -809,7 +818,7 @@ def player_hashkey_info_data(request): raise pyramid.httpexceptions.HTTPNotFound return {'player':player, - 'hashkey':hashkey, + 'hashkey':idfp, 'games_played':games_played, 'overall_stats':overall_stats, 'fav_maps':fav_maps, @@ -912,8 +921,17 @@ def player_elo_info_data(request): """ Provides elo information on a specific player. Raw data is returned. """ + (idfp, status) = verify_request(request) + log.debug("d0_blind_id verification: idfp={0} status={1}\n".format(idfp, status)) + + log.debug("\n----- BEGIN REQUEST BODY -----\n" + request.body + + "----- END REQUEST BODY -----\n\n") + hashkey = request.matchdict['hashkey'] - print "player_elo_info_data [hashkey={0}]".format(hashkey) + + # the incoming hashkey is double quoted, and WSGI unquotes once... + hashkey = unquote(hashkey) + try: player = DBSession.query(Player).\ filter(Player.player_id == Hashkey.player_id).\ @@ -976,98 +994,76 @@ def player_elo_info_text(request): def player_captimes_data(request): - player_id = int(request.matchdict['id']) + player_id = int(request.matchdict['player_id']) if player_id <= 2: player_id = -1; - PlayerCaptimes = namedtuple('PlayerCaptimes', ['fastest_cap', 'create_dt', 'create_dt_epoch', 'create_dt_fuzzy', - 'player_id', 'game_id', 'map_id', 'map_name', 'server_id', 'server_name']) + page = request.params.get("page", 1) - dbquery = DBSession.query('fastest_cap', 'create_dt', 'player_id', 'game_id', 'map_id', - 'map_name', 'server_id', 'server_name').\ - from_statement( - "SELECT ct.fastest_cap, " - "ct.create_dt, " - "ct.player_id, " - "ct.game_id, " - "ct.map_id, " - "m.name map_name, " - "g.server_id, " - "s.name server_name " - "FROM player_map_captimes ct, " - "games g, " - "maps m, " - "servers s " - "WHERE ct.player_id = :player_id " - "AND g.game_id = ct.game_id " - "AND g.server_id = s.server_id " - "AND m.map_id = ct.map_id " - #"ORDER BY ct.fastest_cap " - "ORDER BY ct.create_dt desc" - ).params(player_id=player_id).all() + sort = request.params.get("sort", "create_dt") + + try: + player = DBSession.query(Player).filter_by(player_id=player_id).one() + + pct_q = DBSession.query(PlayerCaptime.fastest_cap, PlayerCaptime.create_dt, + PlayerCaptime.player_id, PlayerCaptime.game_id, PlayerCaptime.map_id, + Map.name.label('map_name'), Game.server_id, Server.name.label('server_name')).\ + filter(PlayerCaptime.player_id==player_id).\ + filter(PlayerCaptime.game_id==Game.game_id).\ + filter(PlayerCaptime.map_id==Map.map_id).\ + filter(Game.server_id==Server.server_id) + + if sort == "fastest": + pct_q = pct_q.order_by(PlayerCaptime.fastest_cap) + else: + sort = "create_dt" + pct_q = pct_q.order_by(expr.desc(PlayerCaptime.create_dt)) - player = DBSession.query(Player).filter_by(player_id=player_id).one() - - player_captimes = [] - for row in dbquery: - player_captimes.append(PlayerCaptimes( - fastest_cap=row.fastest_cap, - create_dt=row.create_dt, - create_dt_epoch=timegm(row.create_dt.timetuple()), - create_dt_fuzzy=pretty_date(row.create_dt), - player_id=row.player_id, - game_id=row.game_id, - map_id=row.map_id, - map_name=row.map_name, - server_id=row.server_id, - server_name=row.server_name, - )) + except Exception as e: + raise pyramid.httpexceptions.HTTPNotFound + + captimes = Page(pct_q, page, items_per_page=20, url=page_url) + + # replace the items in the canned pagination class with more rich ones + captimes.items = [PlayerCapTime(row) for row in captimes.items] return { - 'captimes':player_captimes, - 'player_id':player_id, - 'player_url':request.route_url('player_info', id=player_id), - 'player':player, + "player_id" : player_id, + "player" : player, + "captimes" : captimes, + "page" : page, + "sort" : sort, } -def player_captimes(request): - return player_captimes_data(request) -def player_captimes_json(request): +def player_captimes(request): return player_captimes_data(request) -def player_nvd3_damage(request): - player_id = int(request.matchdict['id']) - if player_id <= 2: - player_id = -1; - - game_type_cd = None - if request.params.has_key("game_type"): - game_type_cd = request.params["game_type"] +def player_captimes_json(request): + data = player_captimes_data(request) + page = request.params.get("page", 1) - limit = 20 - if request.params.has_key("limit"): - limit = int(request.params["limit"]) + # perform any necessary JSON conversions + player_id = data["player_id"] + player = data["player"].to_dict() + captimes = [ct.to_dict() for ct in data["captimes"].items] - if limit < 0: - limit = 20 - if limit > 50: - limit = 50 + return { + "player" : player, + "captimes" : captimes, + "page" : page, + } - return { "player_id": player_id, - "game_type_cd": game_type_cd, - "limit": limit, - } -def player_damage_data_v2(request): - player_id = request.matchdict["id"] +def player_weaponstats_data_json(request): + player_id = int(request.matchdict["id"]) if player_id <= 2: player_id = -1; - game_type_cd = None - if request.params.has_key("game_type"): - game_type_cd = request.params["game_type"] + game_type_cd = request.params.get("game_type", None) + if game_type_cd == "overall": + game_type_cd = None limit = 20 if request.params.has_key("limit"): @@ -1078,52 +1074,140 @@ def player_damage_data_v2(request): if limit > 50: limit = 50 - games_raw = DBSession.query(sa.distinct(Game.game_id)).\ - filter(Game.game_id == PlayerWeaponStat.game_id).\ - filter(PlayerWeaponStat.player_id == player_id) + + # the game_ids of the most recently played ones + # of the given game type is used for a subquery + games_list = DBSession.query(Game.game_id).\ + filter(Game.players.contains([player_id])) if game_type_cd is not None: - games_raw = games_raw.filter(Game.game_type_cd == game_type_cd) + games_list = games_list.filter(Game.game_type_cd == game_type_cd) - games_raw = games_raw.order_by(Game.game_id.desc()).limit(limit).all() + games_list = games_list.order_by(Game.game_id.desc()).limit(limit) weapon_stats_raw = DBSession.query(PlayerWeaponStat).\ filter(PlayerWeaponStat.player_id == player_id).\ - filter(PlayerWeaponStat.game_id.in_(games_raw)).all() + filter(PlayerWeaponStat.game_id.in_(games_list)).\ + all() - # NVD3 expects data points for all weapons used across the - # set of games *for each* point on the x axis. This means populating - # zero-valued weapon stat entries for games where a weapon was not - # used in that game, but was used in another game for the set games_to_weapons = {} - weapons_used = [] + weapons_used = {} + sum_avgs = {} for ws in weapon_stats_raw: if ws.game_id not in games_to_weapons: games_to_weapons[ws.game_id] = [ws.weapon_cd] else: games_to_weapons[ws.game_id].append(ws.weapon_cd) - if ws.weapon_cd not in weapons_used: - weapons_used.append(ws.weapon_cd) + weapons_used[ws.weapon_cd] = weapons_used.get(ws.weapon_cd, 0) + 1 + sum_avgs[ws.weapon_cd] = sum_avgs.get(ws.weapon_cd, 0) + float(ws.hit)/float(ws.fired) + # Creating zero-valued weapon stat entries for games where a weapon was not + # used in that game, but was used in another game for the set. This makes + # the charts look smoother for game_id in games_to_weapons.keys(): - for weapon_cd in set(weapons_used) - set(games_to_weapons[game_id]): - log.debug("Inserting zero value for game_id {0} weapon {1}".format(game_id, weapon_cd)) + for weapon_cd in set(weapons_used.keys()) - set(games_to_weapons[game_id]): weapon_stats_raw.append(PlayerWeaponStat(player_id=player_id, game_id=game_id, weapon_cd=weapon_cd)) + # averages for the weapons used in the range + avgs = {} + for w in weapons_used.keys(): + avgs[w] = round(sum_avgs[w]/float(weapons_used[w])*100, 2) - weapon_stats_raw = sorted(weapon_stats_raw, key=lambda x: x.game_id) - games = sorted(games_to_weapons.keys()) - weapon_stats = [ws.to_dict() for ws in weapon_stats_raw] - - log.debug(games_to_weapons) - log.debug(weapons_used) - log.debug(games) + weapon_stats_raw = sorted(weapon_stats_raw, key = lambda x: x.game_id) + games = sorted(games_to_weapons.keys()) + weapon_stats = [ws.to_dict() for ws in weapon_stats_raw] return { "weapon_stats": weapon_stats, - "weapons_used": weapons_used, + "weapons_used": weapons_used.keys(), "games": games, + "averages": avgs, } + +def player_versus_data(request): + try: + p1_id = int(request.params.get("p1", None)) + p2_id = int(request.params.get("p2", None)) + + p1_wins = 0 + p2_wins = 0 + + players = DBSession.query(Player).filter(sa.or_(Player.player_id == + p1_id, Player.player_id == p2_id)).order_by(Player.player_id).all() + + + if len(players) < 2: + raise Exception("Not enough players found.") + + # assign the players from the array retrieved above + if players[0].player_id == p1_id: + p1 = players[0] + p2 = players[1] + else: + p1 = players[1] + p2 = players[0] + + # note that wins and losses are from p1's perspective + win_loss_sql = """select win_loss, count(1) + from ( + select case + when pgsp1.score >= pgsp2.score then 'win' + else 'loss' + end win_loss + from games g join player_game_stats pgsp1 + on g.game_id = pgsp1.game_id and pgsp1.player_id = :p1 + join player_game_stats pgsp2 + on g.game_id = pgsp2.game_id and pgsp2.player_id = :p2 + where g.players @> ARRAY[:p1,:p2] + and g.game_type_cd = 'duel' + and pgsp1.create_dt between g.create_dt - interval '1 hour' + and g.create_dt + interval '1 hour' + and pgsp2.create_dt between g.create_dt - interval '1 hour' + and g.create_dt + interval '1 hour' + ) wl + group by win_loss + """ + + wins_losses = DBSession.query("win_loss", "count").\ + from_statement(win_loss_sql).\ + params(p1=p1_id, p2=p2_id).all() + + for row in wins_losses: + if row.win_loss == "win": + p1_wins = row.count + elif row.win_loss == "loss": + p2_wins = row.count + + # grab the 20 most recent games between the two + rgs_raw = recent_games_q(player_id=p1_id, player_id_2=p2_id, + game_type_cd="duel").limit(20).all() + + rgs = [RecentGame(row) for row in rgs_raw] + + except Exception as e: + log.debug(e) + + p1_id = None + p2_id = None + p1 = None + p2 = None + p1_wins = None + p2_wins = None + rgs = None + + return { + "p1_id" : p1_id, + "p2_id" : p2_id, + "p1" : p1, + "p2" : p2, + "p1_wins" : p1_wins, + "p2_wins" : p2_wins, + "recent_games" : rgs, + } + + +def player_versus(request): + return player_versus_data(request)