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