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             all()
 137
 138     fav_map = []
 139     for map_e in raw_fav_map:
 140         entry = {}
 141         entry['name'] = map_e[0]
 142         entry['id']   = map_e[1]
 143         fav_map.append(entry)
 144
 145     return fav_map
 146
 147
 148 def _get_fav_weapon(player_id):
 149     """
 150     Get the player's favorite weapon. The favorite weapon is defined
 151     as the weapon that he or she has employed the most in the past
 152     90 days.
 153
 154     Returns a sequence of dictionaries with keys for the weapon's name and id.
 155     The sequence holds the most-used weapons in decreasing order.
 156     """
 157     # 90 day window
 158     back_then = datetime.datetime.utcnow() - datetime.timedelta(days=90)
 159
 160     raw_fav_weapon = DBSession.query(Weapon.descr, Weapon.weapon_cd).\
 161             filter(Game.game_id == PlayerGameStat.game_id).\
 162             filter(PlayerWeaponStat.weapon_cd == Weapon.weapon_cd).\
 163             filter(PlayerGameStat.player_id == player_id).\
 164             filter(PlayerGameStat.create_dt > back_then).\
 165             group_by(Weapon.descr, Weapon.weapon_cd).\
 166             order_by(func.count().desc()).\
 167             all()
 168
 169     fav_weapon = []
 170     for wpn_e in raw_fav_weapon:
 171         entry = {}
 172         entry['name'] = wpn_e[0]
 173         entry['id']   = wpn_e[1]
 174         fav_weapon.append(entry)
 175
 176     return fav_weapon
 177
 178
 179 def _get_rank(player_id):
 180     """
 181     Get the player's rank as well as the total number of ranks.
 182     """
 183     rank = DBSession.query("game_type_cd", "rank", "max_rank").\
 184             from_statement(
 185                 "select pr.game_type_cd, pr.rank, overall.max_rank "
 186                 "from player_ranks pr,  "
 187                    "(select game_type_cd, max(rank) max_rank "
 188                     "from player_ranks  "
 189                     "group by game_type_cd) overall "
 190                 "where pr.game_type_cd = overall.game_type_cd  "
 191                 "and player_id = :player_id "
 192                 "order by rank").\
 193             params(player_id=player_id).all()
 194
 195     return rank;
 196
 197
 198 def get_accuracy_stats(player_id, weapon_cd, games):
 199     """
 200     Provides accuracy for weapon_cd by player_id for the past N games.
 201     """
 202     # Reaching back 90 days should give us an accurate enough average
 203     # We then multiply this out for the number of data points (games) to
 204     # create parameters for a flot graph
 205     try:
 206         raw_avg = DBSession.query(func.sum(PlayerWeaponStat.hit),
 207                 func.sum(PlayerWeaponStat.fired)).\
 208                 filter(PlayerWeaponStat.player_id == player_id).\
 209                 filter(PlayerWeaponStat.weapon_cd == weapon_cd).\
 210                 one()
 211
 212         avg = round(float(raw_avg[0])/raw_avg[1]*100, 2)
 213
 214         # Determine the raw accuracy (hit, fired) numbers for $games games
 215         # This is then enumerated to create parameters for a flot graph
 216         raw_accs = DBSession.query(PlayerWeaponStat.game_id,
 217             PlayerWeaponStat.hit, PlayerWeaponStat.fired).\
 218                 filter(PlayerWeaponStat.player_id == player_id).\
 219                 filter(PlayerWeaponStat.weapon_cd == weapon_cd).\
 220                 order_by(PlayerWeaponStat.game_id.desc()).\
 221                 limit(games).\
 222                 all()
 223
 224         # they come out in opposite order, so flip them in the right direction
 225         raw_accs.reverse()
 226
 227         accs = []
 228         for i in range(len(raw_accs)):
 229             accs.append((raw_accs[i][0], round(float(raw_accs[i][1])/raw_accs[i][2]*100, 2)))
 230     except:
 231         accs = []
 232         avg = 0.0
 233
 234     return (avg, accs)
 235
 236
 237 def get_damage_stats(player_id, weapon_cd, games):
 238     """
 239     Provides damage info for weapon_cd by player_id for the past N games.
 240     """
 241     try:
 242         raw_avg = DBSession.query(func.sum(PlayerWeaponStat.actual),
 243                 func.sum(PlayerWeaponStat.hit)).\
 244                 filter(PlayerWeaponStat.player_id == player_id).\
 245                 filter(PlayerWeaponStat.weapon_cd == weapon_cd).\
 246                 one()
 247
 248         avg = round(float(raw_avg[0])/raw_avg[1], 2)
 249
 250         # Determine the damage efficiency (hit, fired) numbers for $games games
 251         # This is then enumerated to create parameters for a flot graph
 252         raw_dmgs = DBSession.query(PlayerWeaponStat.game_id,
 253             PlayerWeaponStat.actual, PlayerWeaponStat.hit).\
 254                 filter(PlayerWeaponStat.player_id == player_id).\
 255                 filter(PlayerWeaponStat.weapon_cd == weapon_cd).\
 256                 order_by(PlayerWeaponStat.game_id.desc()).\
 257                 limit(games).\
 258                 all()
 259
 260         # they come out in opposite order, so flip them in the right direction
 261         raw_dmgs.reverse()
 262
 263         dmgs = []
 264         for i in range(len(raw_dmgs)):
 265             # try to derive, unless we've hit nothing then set to 0!
 266             try:
 267                 dmg = round(float(raw_dmgs[i][1])/raw_dmgs[i][2], 2)
 268             except:
 269                 dmg = 0.0
 270
 271             dmgs.append((raw_dmgs[i][0], dmg))
 272     except Exception as e:
 273         dmgs = []
 274         avg = 0.0
 275
 276     return (avg, dmgs)
 277
 278
 279 def player_info_data(request):
 280     player_id = int(request.matchdict['id'])
 281     if player_id <= 2:
 282         player_id = -1;
 283
 284     try:
 285         player = DBSession.query(Player).filter_by(player_id=player_id).\
 286                 filter(Player.active_ind == True).one()
 287
 288         # games played, alivetime, wins, kills, deaths
 289         total_stats = _get_total_stats(player.player_id)
 290
 291         # games breakdown - N games played (X ctf, Y dm) etc
 292         (total_games, games_breakdown) = _get_games_played(player.player_id)
 293
 294         # favorite map from the past 90 days
 295         try:
 296             fav_map = _get_fav_map(player.player_id)
 297         except:
 298             fav_map = None
 299
 300         # favorite weapon from the past 90 days
 301         try:
 302             fav_weapon = _get_fav_weapon(player.player_id)
 303         except:
 304             fav_weapon = None
 305
 306         # friendly display of elo information and preliminary status
 307         elos = DBSession.query(PlayerElo).filter_by(player_id=player_id).\
 308                 filter(PlayerElo.game_type_cd.in_(['ctf','duel','dm'])).\
 309                 order_by(PlayerElo.elo.desc()).all()
 310
 311         elos_display = []
 312         for elo in elos:
 313             if elo.games > 32:
 314                 str = "{0} ({1})"
 315             else:
 316                 str = "{0}* ({1})"
 317
 318             elos_display.append(str.format(round(elo.elo, 3),
 319                 elo.game_type_cd))
 320
 321         # get current rank information
 322         ranks = _get_rank(player_id)
 323         ranks_display = ', '.join(["{1} of {2} ({0})".format(gtc, rank,
 324             max_rank) for gtc, rank, max_rank in ranks])
 325
 326
 327         # which weapons have been used in the past 90 days
 328         # and also, used in 5 games or more?
 329         back_then = datetime.datetime.utcnow() - datetime.timedelta(days=90)
 330         recent_weapons = []
 331         for weapon in DBSession.query(PlayerWeaponStat.weapon_cd, func.count()).\
 332                 filter(PlayerWeaponStat.player_id == player_id).\
 333                 filter(PlayerWeaponStat.create_dt > back_then).\
 334                 group_by(PlayerWeaponStat.weapon_cd).\
 335                 having(func.count() > 4).\
 336                 all():
 337                     recent_weapons.append(weapon[0])
 338
 339         # recent games table, all data
 340         recent_games = DBSession.query(PlayerGameStat, Game, Server, Map).\
 341                 filter(PlayerGameStat.player_id == player_id).\
 342                 filter(PlayerGameStat.game_id == Game.game_id).\
 343                 filter(Game.server_id == Server.server_id).\
 344                 filter(Game.map_id == Map.map_id).\
 345                 order_by(Game.game_id.desc())[0:10]
 346
 347     except Exception as e:
 348         player = None
 349         elos_display = None
 350         total_stats = None
 351         recent_games = None
 352         total_games = None
 353         games_breakdown = None
 354         recent_weapons = []
 355         fav_map = None
 356         fav_weapon = None
 357         ranks_display = None;
 358
 359     return {'player':player,
 360             'elos_display':elos_display,
 361             'recent_games':recent_games,
 362             'total_stats':total_stats,
 363             'total_games':total_games,
 364             'games_breakdown':games_breakdown,
 365             'recent_weapons':recent_weapons,
 366             'fav_map':fav_map,
 367             'fav_weapon':fav_weapon,
 368             'ranks_display':ranks_display,
 369             }
 370
 371
 372 def player_info(request):
 373     """
 374     Provides detailed information on a specific player
 375     """
 376     return player_info_data(request)
 377
 378
 379 def player_info_json(request):
 380     """
 381     Provides detailed information on a specific player. JSON.
 382     """
 383     return [{'status':'not implemented'}]
 384
 385
 386 def player_game_index_data(request):
 387     player_id = request.matchdict['player_id']
 388
 389     if request.params.has_key('page'):
 390         current_page = request.params['page']
 391     else:
 392         current_page = 1
 393
 394     try:
 395         games_q = DBSession.query(Game, Server, Map).\
 396             filter(PlayerGameStat.game_id == Game.game_id).\
 397             filter(PlayerGameStat.player_id == player_id).\
 398             filter(Game.server_id == Server.server_id).\
 399             filter(Game.map_id == Map.map_id).\
 400             order_by(Game.game_id.desc())
 401
 402         games = Page(games_q, current_page, items_per_page=10, url=page_url)
 403
 404         pgstats = {}
 405         for (game, server, map) in games:
 406             pgstats[game.game_id] = DBSession.query(PlayerGameStat).\
 407                     filter(PlayerGameStat.game_id == game.game_id).\
 408                     order_by(PlayerGameStat.rank).\
 409                     order_by(PlayerGameStat.score).all()
 410
 411     except Exception as e:
 412         player = None
 413         games = None
 414
 415     return {'player_id':player_id,
 416             'games':games,
 417             'pgstats':pgstats}
 418
 419
 420 def player_game_index(request):
 421     """
 422     Provides an index of the games in which a particular
 423     player was involved. This is ordered by game_id, with
 424     the most recent game_ids first. Paginated.
 425     """
 426     return player_game_index_data(request)
 427
 428
 429 def player_game_index_json(request):
 430     """
 431     Provides an index of the games in which a particular
 432     player was involved. This is ordered by game_id, with
 433     the most recent game_ids first. Paginated. JSON.
 434     """
 435     return [{'status':'not implemented'}]
 436
 437
 438 def player_accuracy_data(request):
 439     player_id = request.matchdict['id']
 440     allowed_weapons = ['nex', 'rifle', 'shotgun', 'uzi', 'minstanex']
 441     weapon_cd = 'nex'
 442     games = 20
 443
 444     if request.params.has_key('weapon'):
 445         if request.params['weapon'] in allowed_weapons:
 446             weapon_cd = request.params['weapon']
 447
 448     if request.params.has_key('games'):
 449         try:
 450             games = request.params['games']
 451
 452             if games < 0:
 453                 games = 20
 454             if games > 50:
 455                 games = 50
 456         except:
 457             games = 20
 458
 459     (avg, accs) = get_accuracy_stats(player_id, weapon_cd, games)
 460
 461     # if we don't have enough data for the given weapon
 462     if len(accs) < games:
 463         games = len(accs)
 464
 465     return {
 466             'player_id':player_id,
 467             'player_url':request.route_url('player_info', id=player_id),
 468             'weapon':weapon_cd,
 469             'games':games,
 470             'avg':avg,
 471             'accs':accs
 472             }
 473
 474
 475 def player_accuracy(request):
 476     """
 477     Provides the accuracy for the given weapon. (JSON only)
 478     """
 479     return player_accuracy_data(request)
 480
 481
 482 def player_accuracy_json(request):
 483     """
 484     Provides a JSON response representing the accuracy for the given weapon.
 485
 486     Parameters:
 487        weapon = which weapon to display accuracy for. Valid values are 'nex',
 488                 'shotgun', 'uzi', and 'minstanex'.
 489        games = over how many games to display accuracy. Can be up to 50.
 490     """
 491     return player_accuracy_data(request)
 492
 493
 494 def player_damage_data(request):
 495     player_id = request.matchdict['id']
 496     allowed_weapons = ['grenadelauncher', 'electro', 'crylink', 'hagar',
 497             'rocketlauncher', 'laser']
 498     weapon_cd = 'rocketlauncher'
 499     games = 20
 500
 501     if request.params.has_key('weapon'):
 502         if request.params['weapon'] in allowed_weapons:
 503             weapon_cd = request.params['weapon']
 504
 505     if request.params.has_key('games'):
 506         try:
 507             games = request.params['games']
 508
 509             if games < 0:
 510                 games = 20
 511             if games > 50:
 512                 games = 50
 513         except:
 514             games = 20
 515
 516     (avg, dmgs) = get_damage_stats(player_id, weapon_cd, games)
 517
 518     # if we don't have enough data for the given weapon
 519     if len(dmgs) < games:
 520         games = len(dmgs)
 521
 522     return {
 523             'player_id':player_id,
 524             'player_url':request.route_url('player_info', id=player_id),
 525             'weapon':weapon_cd,
 526             'games':games,
 527             'avg':avg,
 528             'dmgs':dmgs
 529             }
 530
 531
 532 def player_damage_json(request):
 533     """
 534     Provides a JSON response representing the damage for the given weapon.
 535
 536     Parameters:
 537        weapon = which weapon to display damage for. Valid values are
 538          'grenadelauncher', 'electro', 'crylink', 'hagar', 'rocketlauncher',
 539          'laser'.
 540        games = over how many games to display damage. Can be up to 50.
 541     """
 542     return player_damage_data(request)