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         try:
 262             fav_map = _get_fav_map(player.player_id)
 263         except:
 264             fav_map = None
 265
 266         # friendly display of elo information and preliminary status
 267         elos = DBSession.query(PlayerElo).filter_by(player_id=player_id).\
 268                 filter(PlayerElo.game_type_cd.in_(['ctf','duel','dm'])).\
 269                 order_by(PlayerElo.elo.desc()).all()
 270
 271         elos_display = []
 272         for elo in elos:
 273             if elo.games > 32:
 274                 str = "{0} ({1})"
 275             else:
 276                 str = "{0}* ({1})"
 277
 278             elos_display.append(str.format(round(elo.elo, 3),
 279                 elo.game_type_cd))
 280
 281         # get current rank information
 282         ranks = _get_rank(player_id)
 283         ranks_display = ', '.join(["{1} of {2} ({0})".format(gtc, rank,
 284             max_rank) for gtc, rank, max_rank in ranks])
 285
 286
 287         # which weapons have been used in the past 90 days
 288         # and also, used in 5 games or more?
 289         back_then = datetime.datetime.utcnow() - datetime.timedelta(days=90)
 290         recent_weapons = []
 291         for weapon in DBSession.query(PlayerWeaponStat.weapon_cd, func.count()).\
 292                 filter(PlayerWeaponStat.player_id == player_id).\
 293                 filter(PlayerWeaponStat.create_dt > back_then).\
 294                 group_by(PlayerWeaponStat.weapon_cd).\
 295                 having(func.count() > 4).\
 296                 all():
 297                     recent_weapons.append(weapon[0])
 298
 299         # recent games table, all data
 300         recent_games = DBSession.query(PlayerGameStat, Game, Server, Map).\
 301                 filter(PlayerGameStat.player_id == player_id).\
 302                 filter(PlayerGameStat.game_id == Game.game_id).\
 303                 filter(Game.server_id == Server.server_id).\
 304                 filter(Game.map_id == Map.map_id).\
 305                 order_by(Game.game_id.desc())[0:10]
 306
 307     except Exception as e:
 308         player = None
 309         elos_display = None
 310         total_stats = None
 311         recent_games = None
 312         total_games = None
 313         games_breakdown = None
 314         recent_weapons = []
 315         fav_map = None
 316         ranks_display = None;
 317
 318     return {'player':player,
 319             'elos_display':elos_display,
 320             'recent_games':recent_games,
 321             'total_stats':total_stats,
 322             'total_games':total_games,
 323             'games_breakdown':games_breakdown,
 324             'recent_weapons':recent_weapons,
 325             'fav_map':fav_map,
 326             'ranks_display':ranks_display,
 327             }
 328
 329
 330 def player_info(request):
 331     """
 332     Provides detailed information on a specific player
 333     """
 334     return player_info_data(request)
 335
 336
 337 def player_info_json(request):
 338     """
 339     Provides detailed information on a specific player. JSON.
 340     """
 341     return [{'status':'not implemented'}]
 342
 343
 344 def player_game_index_data(request):
 345     player_id = request.matchdict['player_id']
 346
 347     if request.params.has_key('page'):
 348         current_page = request.params['page']
 349     else:
 350         current_page = 1
 351
 352     try:
 353         games_q = DBSession.query(Game, Server, Map).\
 354             filter(PlayerGameStat.game_id == Game.game_id).\
 355             filter(PlayerGameStat.player_id == player_id).\
 356             filter(Game.server_id == Server.server_id).\
 357             filter(Game.map_id == Map.map_id).\
 358             order_by(Game.game_id.desc())
 359
 360         games = Page(games_q, current_page, items_per_page=10, url=page_url)
 361
 362         pgstats = {}
 363         for (game, server, map) in games:
 364             pgstats[game.game_id] = DBSession.query(PlayerGameStat).\
 365                     filter(PlayerGameStat.game_id == game.game_id).\
 366                     order_by(PlayerGameStat.rank).\
 367                     order_by(PlayerGameStat.score).all()
 368
 369     except Exception as e:
 370         player = None
 371         games = None
 372
 373     return {'player_id':player_id,
 374             'games':games,
 375             'pgstats':pgstats}
 376
 377
 378 def player_game_index(request):
 379     """
 380     Provides an index of the games in which a particular
 381     player was involved. This is ordered by game_id, with
 382     the most recent game_ids first. Paginated.
 383     """
 384     return player_game_index_data(request)
 385
 386
 387 def player_game_index_json(request):
 388     """
 389     Provides an index of the games in which a particular
 390     player was involved. This is ordered by game_id, with
 391     the most recent game_ids first. Paginated. JSON.
 392     """
 393     return [{'status':'not implemented'}]
 394
 395
 396 def player_accuracy_data(request):
 397     player_id = request.matchdict['id']
 398     allowed_weapons = ['nex', 'rifle', 'shotgun', 'uzi', 'minstanex']
 399     weapon_cd = 'nex'
 400     games = 20
 401
 402     if request.params.has_key('weapon'):
 403         if request.params['weapon'] in allowed_weapons:
 404             weapon_cd = request.params['weapon']
 405
 406     if request.params.has_key('games'):
 407         try:
 408             games = request.params['games']
 409
 410             if games < 0:
 411                 games = 20
 412             if games > 50:
 413                 games = 50
 414         except:
 415             games = 20
 416
 417     (avg, accs) = get_accuracy_stats(player_id, weapon_cd, games)
 418
 419     # if we don't have enough data for the given weapon
 420     if len(accs) < games:
 421         games = len(accs)
 422
 423     return {
 424             'player_id':player_id,
 425             'player_url':request.route_url('player_info', id=player_id),
 426             'weapon':weapon_cd,
 427             'games':games,
 428             'avg':avg,
 429             'accs':accs
 430             }
 431
 432
 433 def player_accuracy(request):
 434     """
 435     Provides the accuracy for the given weapon. (JSON only)
 436     """
 437     return player_accuracy_data(request)
 438
 439
 440 def player_accuracy_json(request):
 441     """
 442     Provides a JSON response representing the accuracy for the given weapon.
 443
 444     Parameters:
 445        weapon = which weapon to display accuracy for. Valid values are 'nex',
 446                 'shotgun', 'uzi', and 'minstanex'.
 447        games = over how many games to display accuracy. Can be up to 50.
 448     """
 449     return player_accuracy_data(request)
 450
 451
 452 def player_damage_data(request):
 453     player_id = request.matchdict['id']
 454     allowed_weapons = ['grenadelauncher', 'electro', 'crylink', 'hagar',
 455             'rocketlauncher', 'laser']
 456     weapon_cd = 'rocketlauncher'
 457     games = 20
 458
 459     if request.params.has_key('weapon'):
 460         if request.params['weapon'] in allowed_weapons:
 461             weapon_cd = request.params['weapon']
 462
 463     if request.params.has_key('games'):
 464         try:
 465             games = request.params['games']
 466
 467             if games < 0:
 468                 games = 20
 469             if games > 50:
 470                 games = 50
 471         except:
 472             games = 20
 473
 474     (avg, dmgs) = get_damage_stats(player_id, weapon_cd, games)
 475
 476     # if we don't have enough data for the given weapon
 477     if len(dmgs) < games:
 478         games = len(dmgs)
 479
 480     return {
 481             'player_id':player_id,
 482             'player_url':request.route_url('player_info', id=player_id),
 483             'weapon':weapon_cd,
 484             'games':games,
 485             'avg':avg,
 486             'dmgs':dmgs
 487             }
 488
 489
 490 def player_damage_json(request):
 491     """
 492     Provides a JSON response representing the damage for the given weapon.
 493
 494     Parameters:
 495        weapon = which weapon to display damage for. Valid values are
 496          'grenadelauncher', 'electro', 'crylink', 'hagar', 'rocketlauncher',
 497          'laser'.
 498        games = over how many games to display damage. Can be up to 50.
 499     """
 500     return player_damage_data(request)