xonstat/views/player.py

   1 import datetime
   2 import json
   3 import logging
   4 import re
   5 import sqlalchemy as sa
   6 import sqlalchemy.sql.functions as func
   7 import time
   8 from pyramid.response import Response
   9 from pyramid.url import current_route_url
  10 from sqlalchemy import desc, distinct
  11 from webhelpers.paginate import Page, PageURL
  12 from xonstat.models import *
  13 from xonstat.util import page_url
  14
  15 log = logging.getLogger(__name__)
  16
  17
  18 def _player_index_data(request):
  19     if request.params.has_key('page'):
  20         current_page = request.params['page']
  21     else:
  22         current_page = 1
  23
  24     try:
  25         player_q = DBSession.query(Player).\
  26                 filter(Player.player_id > 2).\
  27                 filter(Player.active_ind == True).\
  28                 filter(sa.not_(Player.nick.like('Anonymous Player%'))).\
  29                 order_by(Player.player_id.desc())
  30
  31         players = Page(player_q, current_page, items_per_page=10, url=page_url)
  32
  33     except Exception as e:
  34         players = None
  35         raise e
  36
  37     return {'players':players
  38            }
  39
  40
  41 def player_index(request):
  42     """
  43     Provides a list of all the current players.
  44     """
  45     return _player_index_data(request)
  46
  47
  48 def player_index_json(request):
  49     """
  50     Provides a list of all the current players. JSON.
  51     """
  52     return [{'status':'not implemented'}]
  53
  54
  55 def _get_games_played(player_id):
  56     """
  57     Provides a breakdown by gametype of the games played by player_id.
  58
  59     Returns a tuple containing (total_games, games_breakdown), where
  60     total_games is the absolute number of games played by player_id
  61     and games_breakdown is an array containing (game_type_cd, # games)
  62     """
  63     games_played = DBSession.query(Game.game_type_cd, func.count()).\
  64             filter(Game.game_id == PlayerGameStat.game_id).\
  65             filter(PlayerGameStat.player_id == player_id).\
  66             group_by(Game.game_type_cd).\
  67             order_by(func.count().desc()).all()
  68
  69     total = 0
  70     for (game_type_cd, games) in games_played:
  71         total += games
  72
  73     return (total, games_played)
  74
  75
  76 # TODO: should probably factor the above function into this one such that
  77 # total_stats['ctf_games'] is the count of CTF games and so on...
  78 def _get_total_stats(player_id):
  79     """
  80     Provides aggregated stats by player_id.
  81
  82     Returns a dict with the keys 'kills', 'deaths', 'alivetime'.
  83
  84     kills = how many kills a player has over all games
  85     deaths = how many deaths a player has over all games
  86     alivetime = how long a player has played over all games
  87
  88     If any of the above are None, they are set to 0.
  89     """
  90     total_stats = {}
  91     (total_stats['kills'], total_stats['deaths'], total_stats['alivetime']) = DBSession.\
  92             query("total_kills", "total_deaths", "total_alivetime").\
  93             from_statement(
  94                 "select sum(kills) total_kills, "
  95                 "sum(deaths) total_deaths, "
  96                 "sum(alivetime) total_alivetime "
  97                 "from player_game_stats "
  98                 "where player_id=:player_id"
  99             ).params(player_id=player_id).one()
 100
 101     (total_stats['wins'],) = DBSession.\
 102             query("total_wins").\
 103             from_statement(
 104                 "select count(*) total_wins "
 105                 "from games g, player_game_stats pgs "
 106                 "where g.game_id = pgs.game_id "
 107                 "and player_id=:player_id "
 108                 "and (g.winner = pgs.team or pgs.rank = 1)"
 109             ).params(player_id=player_id).one()
 110
 111     for (key,value) in total_stats.items():
 112         if value == None:
 113             total_stats[key] = 0
 114
 115     return total_stats
 116
 117
 118 def _get_fav_map(player_id):
 119     """
 120     Get the player's favorite map. The favorite map is defined
 121     as the map that he or she has played the most in the past
 122     90 days.
 123
 124     Returns a dictionary with keys for the map's name and id.
 125     """
 126     # 90 day window
 127     back_then = datetime.datetime.utcnow() - datetime.timedelta(days=90)
 128
 129     raw_fav_map = DBSession.query(Map.name, Map.map_id).\
 130             filter(Game.game_id == PlayerGameStat.game_id).\
 131             filter(Game.map_id == Map.map_id).\
 132             filter(PlayerGameStat.player_id == player_id).\
 133             filter(PlayerGameStat.create_dt > back_then).\
 134             group_by(Map.name, Map.map_id).\
 135             order_by(func.count().desc()).\
 136             limit(1).one()
 137
 138     fav_map = {}
 139     fav_map['name'] = raw_fav_map[0]
 140     fav_map['id'] = raw_fav_map[1]
 141
 142     return fav_map
 143
 144
 145 def _get_rank(player_id):
 146     """
 147     Get the player's rank as well as the total number of ranks.
 148     """
 149     rank = DBSession.query("game_type_cd", "rank", "max_rank").\
 150             from_statement(
 151                 "select pr.game_type_cd, pr.rank, overall.max_rank "
 152                 "from player_ranks pr,  "
 153                    "(select game_type_cd, max(rank) max_rank "
 154                     "from player_ranks  "
 155                     "group by game_type_cd) overall "
 156                 "where pr.game_type_cd = overall.game_type_cd  "
 157                 "and player_id = :player_id "
 158                 "order by rank").\
 159             params(player_id=player_id).all()
 160
 161     return rank;
 162
 163
 164 def get_accuracy_stats(player_id, weapon_cd, games):
 165     """
 166     Provides accuracy for weapon_cd by player_id for the past N games.
 167     """
 168     # Reaching back 90 days should give us an accurate enough average
 169     # We then multiply this out for the number of data points (games) to
 170     # create parameters for a flot graph
 171     try:
 172         raw_avg = DBSession.query(func.sum(PlayerWeaponStat.hit),
 173                 func.sum(PlayerWeaponStat.fired)).\
 174                 filter(PlayerWeaponStat.player_id == player_id).\
 175                 filter(PlayerWeaponStat.weapon_cd == weapon_cd).\
 176                 one()
 177
 178         avg = round(float(raw_avg[0])/raw_avg[1]*100, 2)
 179
 180         # Determine the raw accuracy (hit, fired) numbers for $games games
 181         # This is then enumerated to create parameters for a flot graph
 182         raw_accs = DBSession.query(PlayerWeaponStat.game_id,
 183             PlayerWeaponStat.hit, PlayerWeaponStat.fired).\
 184                 filter(PlayerWeaponStat.player_id == player_id).\
 185                 filter(PlayerWeaponStat.weapon_cd == weapon_cd).\
 186                 order_by(PlayerWeaponStat.game_id.desc()).\
 187                 limit(games).\
 188                 all()
 189
 190         # they come out in opposite order, so flip them in the right direction
 191         raw_accs.reverse()
 192
 193         accs = []
 194         for i in range(len(raw_accs)):
 195             accs.append((raw_accs[i][0], round(float(raw_accs[i][1])/raw_accs[i][2]*100, 2)))
 196     except:
 197         accs = []
 198         avg = 0.0
 199
 200     return (avg, accs)
 201
 202
 203 def get_damage_stats(player_id, weapon_cd, games):
 204     """
 205     Provides damage info for weapon_cd by player_id for the past N games.
 206     """
 207     try:
 208         raw_avg = DBSession.query(func.sum(PlayerWeaponStat.actual),
 209                 func.sum(PlayerWeaponStat.hit)).\
 210                 filter(PlayerWeaponStat.player_id == player_id).\
 211                 filter(PlayerWeaponStat.weapon_cd == weapon_cd).\
 212                 one()
 213
 214         avg = round(float(raw_avg[0])/raw_avg[1], 2)
 215
 216         # Determine the damage efficiency (hit, fired) numbers for $games games
 217         # This is then enumerated to create parameters for a flot graph
 218         raw_dmgs = DBSession.query(PlayerWeaponStat.game_id,
 219             PlayerWeaponStat.actual, PlayerWeaponStat.hit).\
 220                 filter(PlayerWeaponStat.player_id == player_id).\
 221                 filter(PlayerWeaponStat.weapon_cd == weapon_cd).\
 222                 order_by(PlayerWeaponStat.game_id.desc()).\
 223                 limit(games).\
 224                 all()
 225
 226         # they come out in opposite order, so flip them in the right direction
 227         raw_dmgs.reverse()
 228
 229         dmgs = []
 230         for i in range(len(raw_dmgs)):
 231             # try to derive, unless we've hit nothing then set to 0!
 232             try:
 233                 dmg = round(float(raw_dmgs[i][1])/raw_dmgs[i][2], 2)
 234             except:
 235                 dmg = 0.0
 236
 237             dmgs.append((raw_dmgs[i][0], dmg))
 238     except Exception as e:
 239         dmgs = []
 240         avg = 0.0
 241
 242     return (avg, dmgs)
 243
 244
 245 def _player_info_data(request):
 246     player_id = int(request.matchdict['id'])
 247     if player_id <= 2:
 248         player_id = -1;
 249
 250     try:
 251         player = DBSession.query(Player).filter_by(player_id=player_id).\
 252                 filter(Player.active_ind == True).one()
 253
 254         # games played, alivetime, wins, kills, deaths
 255         total_stats = _get_total_stats(player.player_id)
 256
 257         # games breakdown - N games played (X ctf, Y dm) etc
 258         (total_games, games_breakdown) = _get_games_played(player.player_id)
 259
 260         # favorite map from the past 90 days
 261         fav_map = _get_fav_map(player.player_id)
 262
 263         # friendly display of elo information and preliminary status
 264         elos = DBSession.query(PlayerElo).filter_by(player_id=player_id).\
 265                 filter(PlayerElo.game_type_cd.in_(['ctf','duel','dm'])).\
 266                 order_by(PlayerElo.elo.desc()).all()
 267
 268         elos_display = []
 269         for elo in elos:
 270             if elo.games > 32:
 271                 str = "{0} ({1})"
 272             else:
 273                 str = "{0}* ({1})"
 274
 275             elos_display.append(str.format(round(elo.elo, 3),
 276                 elo.game_type_cd))
 277
 278         # get current rank information
 279         ranks = _get_rank(player_id)
 280         ranks_display = ', '.join(["{1} of {2} ({0})".format(gtc, rank,
 281             max_rank) for gtc, rank, max_rank in ranks])
 282
 283
 284         # which weapons have been used in the past 90 days
 285         # and also, used in 5 games or more?
 286         back_then = datetime.datetime.utcnow() - datetime.timedelta(days=90)
 287         recent_weapons = []
 288         for weapon in DBSession.query(PlayerWeaponStat.weapon_cd, func.count()).\
 289                 filter(PlayerWeaponStat.player_id == player_id).\
 290                 filter(PlayerWeaponStat.create_dt > back_then).\
 291                 group_by(PlayerWeaponStat.weapon_cd).\
 292                 having(func.count() > 4).\
 293                 all():
 294                     recent_weapons.append(weapon[0])
 295
 296         # recent games table, all data
 297         recent_games = DBSession.query(PlayerGameStat, Game, Server, Map).\
 298                 filter(PlayerGameStat.player_id == player_id).\
 299                 filter(PlayerGameStat.game_id == Game.game_id).\
 300                 filter(Game.server_id == Server.server_id).\
 301                 filter(Game.map_id == Map.map_id).\
 302                 order_by(Game.game_id.desc())[0:10]
 303
 304     except Exception as e:
 305         player = None
 306         elos_display = None
 307         total_stats = None
 308         recent_games = None
 309         total_games = None
 310         games_breakdown = None
 311         recent_weapons = []
 312         fav_map = None
 313         ranks_display = None;
 314
 315     return {'player':player,
 316             'elos_display':elos_display,
 317             'recent_games':recent_games,
 318             'total_stats':total_stats,
 319             'total_games':total_games,
 320             'games_breakdown':games_breakdown,
 321             'recent_weapons':recent_weapons,
 322             'fav_map':fav_map,
 323             'ranks_display':ranks_display,
 324             }
 325
 326
 327 def player_info(request):
 328     """
 329     Provides detailed information on a specific player
 330     """
 331     return _player_info_data(request)
 332
 333
 334 def player_info_json(request):
 335     """
 336     Provides detailed information on a specific player. JSON.
 337     """
 338     return [{'status':'not implemented'}]
 339
 340
 341 def _player_game_index_data(request):
 342     player_id = request.matchdict['player_id']
 343
 344     if request.params.has_key('page'):
 345         current_page = request.params['page']
 346     else:
 347         current_page = 1
 348
 349     try:
 350         games_q = DBSession.query(Game, Server, Map).\
 351             filter(PlayerGameStat.game_id == Game.game_id).\
 352             filter(PlayerGameStat.player_id == player_id).\
 353             filter(Game.server_id == Server.server_id).\
 354             filter(Game.map_id == Map.map_id).\
 355             order_by(Game.game_id.desc())
 356
 357         games = Page(games_q, current_page, items_per_page=10, url=page_url)
 358
 359         pgstats = {}
 360         for (game, server, map) in games:
 361             pgstats[game.game_id] = DBSession.query(PlayerGameStat).\
 362                     filter(PlayerGameStat.game_id == game.game_id).\
 363                     order_by(PlayerGameStat.rank).\
 364                     order_by(PlayerGameStat.score).all()
 365
 366     except Exception as e:
 367         player = None
 368         games = None
 369
 370     return {'player_id':player_id,
 371             'games':games,
 372             'pgstats':pgstats}
 373
 374
 375 def player_game_index(request):
 376     """
 377     Provides an index of the games in which a particular
 378     player was involved. This is ordered by game_id, with
 379     the most recent game_ids first. Paginated.
 380     """
 381     return _player_game_index_data(request)
 382
 383
 384 def player_game_index_json(request):
 385     """
 386     Provides an index of the games in which a particular
 387     player was involved. This is ordered by game_id, with
 388     the most recent game_ids first. Paginated. JSON.
 389     """
 390     return [{'status':'not implemented'}]
 391
 392
 393 def _player_accuracy_data(request):
 394     player_id = request.matchdict['id']
 395     allowed_weapons = ['nex', 'rifle', 'shotgun', 'uzi', 'minstanex']
 396     weapon_cd = 'nex'
 397     games = 20
 398
 399     if request.params.has_key('weapon'):
 400         if request.params['weapon'] in allowed_weapons:
 401             weapon_cd = request.params['weapon']
 402
 403     if request.params.has_key('games'):
 404         try:
 405             games = request.params['games']
 406
 407             if games < 0:
 408                 games = 20
 409             if games > 50:
 410                 games = 50
 411         except:
 412             games = 20
 413
 414     (avg, accs) = get_accuracy_stats(player_id, weapon_cd, games)
 415
 416     # if we don't have enough data for the given weapon
 417     if len(accs) < games:
 418         games = len(accs)
 419
 420     return {
 421             'player_id':player_id,
 422             'player_url':request.route_url('player_info', id=player_id),
 423             'weapon':weapon_cd,
 424             'games':games,
 425             'avg':avg,
 426             'accs':accs
 427             }
 428
 429
 430 def player_accuracy(request):
 431     """
 432     Provides the accuracy for the given weapon. (JSON only)
 433     """
 434     return _player_accuracy_data(request)
 435
 436
 437 def player_accuracy_json(request):
 438     """
 439     Provides a JSON response representing the accuracy for the given weapon.
 440
 441     Parameters:
 442        weapon = which weapon to display accuracy for. Valid values are 'nex',
 443                 'shotgun', 'uzi', and 'minstanex'.
 444        games = over how many games to display accuracy. Can be up to 50.
 445     """
 446     return _player_accuracy_data(request)
 447
 448
 449 def _player_damage_data(request):
 450     player_id = request.matchdict['id']
 451     allowed_weapons = ['grenadelauncher', 'electro', 'crylink', 'hagar',
 452             'rocketlauncher', 'laser']
 453     weapon_cd = 'rocketlauncher'
 454     games = 20
 455
 456     if request.params.has_key('weapon'):
 457         if request.params['weapon'] in allowed_weapons:
 458             weapon_cd = request.params['weapon']
 459
 460     if request.params.has_key('games'):
 461         try:
 462             games = request.params['games']
 463
 464             if games < 0:
 465                 games = 20
 466             if games > 50:
 467                 games = 50
 468         except:
 469             games = 20
 470
 471     (avg, dmgs) = get_damage_stats(player_id, weapon_cd, games)
 472
 473     # if we don't have enough data for the given weapon
 474     if len(dmgs) < games:
 475         games = len(dmgs)
 476
 477     return {
 478             'player_id':player_id,
 479             'player_url':request.route_url('player_info', id=player_id),
 480             'weapon':weapon_cd,
 481             'games':games,
 482             'avg':avg,
 483             'dmgs':dmgs
 484             }
 485
 486
 487 def player_damage_json(request):
 488     """
 489     Provides a JSON response representing the damage for the given weapon.
 490
 491     Parameters:
 492        weapon = which weapon to display damage for. Valid values are
 493          'grenadelauncher', 'electro', 'crylink', 'hagar', 'rocketlauncher',
 494          'laser'.
 495        games = over how many games to display damage. Can be up to 50.
 496     """
 497     return _player_damage_data(request)