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