xonstat/views/player.py

   1 import datetime
   2 import logging
   3 from calendar import timegm
   4 from collections import namedtuple
   5 from urllib import unquote
   6
   7 import pyramid.httpexceptions
   8 import sqlalchemy as sa
   9 import sqlalchemy.sql.expression as expr
  10 import sqlalchemy.sql.functions as func
  11 from webhelpers.paginate import Page
  12 from xonstat.models import DBSession, Server, Map, Game, PlayerWeaponStat, Player, Hashkey
  13 from xonstat.models import PlayerElo, PlayerCaptime, PlayerMedal, GameType
  14 from xonstat.models.player import PlayerCapTime
  15 from xonstat.util import is_cake_day, verify_request
  16 from xonstat.util import page_url, to_json, pretty_date, datetime_seconds
  17 from xonstat.views.helpers import RecentGame, recent_games_q
  18
  19 log = logging.getLogger(__name__)
  20
  21
  22 def player_index_data(request):
  23     if request.params.has_key('page'):
  24         current_page = request.params['page']
  25     else:
  26         current_page = 1
  27
  28     try:
  29         player_q = DBSession.query(Player).\
  30                 filter(Player.player_id > 2).\
  31                 filter(Player.active_ind == True).\
  32                 filter(sa.not_(Player.nick.like('Anonymous Player%'))).\
  33                 order_by(Player.player_id.desc())
  34
  35         players = Page(player_q, current_page, items_per_page=25, url=page_url)
  36
  37     except Exception as e:
  38         players = None
  39         raise e
  40
  41     return {'players':players
  42            }
  43
  44
  45 def player_index(request):
  46     """
  47     Provides a list of all the current players.
  48     """
  49     return player_index_data(request)
  50
  51
  52 def player_index_json(request):
  53     """
  54     Provides a list of all the current players. JSON.
  55     """
  56     return [{'status':'not implemented'}]
  57
  58
  59 def get_games_played(player_id):
  60     """
  61     Provides a breakdown by gametype of the games played by player_id.
  62
  63     Returns a list of namedtuples with the following members:
  64         - game_type_cd
  65         - games
  66         - wins
  67         - losses
  68         - win_pct
  69
  70     The list itself is ordered by the number of games played
  71     """
  72     GamesPlayed = namedtuple('GamesPlayed', ['game_type_cd', 'games', 'wins',
  73         'losses', 'win_pct'])
  74
  75     raw_games_played = DBSession.query('game_type_cd', 'wins', 'losses').\
  76             from_statement(
  77                 "SELECT game_type_cd, "
  78                        "SUM(win) wins, "
  79                        "SUM(loss) losses "
  80                 "FROM   (SELECT g.game_id, "
  81                                "g.game_type_cd, "
  82                                "CASE "
  83                                  "WHEN g.winner = pgs.team THEN 1 "
  84                                  "WHEN pgs.scoreboardpos = 1 THEN 1 "
  85                                  "ELSE 0 "
  86                                "END win, "
  87                                "CASE "
  88                                  "WHEN g.winner = pgs.team THEN 0 "
  89                                  "WHEN pgs.scoreboardpos = 1 THEN 0 "
  90                                  "ELSE 1 "
  91                                "END loss "
  92                         "FROM   games g, "
  93                                "player_game_stats pgs "
  94                         "WHERE  g.game_id = pgs.game_id "
  95                         "AND pgs.player_id = :player_id "
  96                         "AND g.players @> ARRAY[:player_id]) win_loss "
  97                 "GROUP  BY game_type_cd "
  98             ).params(player_id=player_id).all()
  99
 100     games_played = []
 101     overall_games = 0
 102     overall_wins = 0
 103     overall_losses = 0
 104     for row in raw_games_played:
 105         games = row.wins + row.losses
 106         overall_games += games
 107         overall_wins += row.wins
 108         overall_losses += row.losses
 109         win_pct = float(row.wins)/games * 100
 110
 111         games_played.append(GamesPlayed(row.game_type_cd, games, row.wins,
 112             row.losses, win_pct))
 113
 114     try:
 115         overall_win_pct = float(overall_wins)/overall_games * 100
 116     except:
 117         overall_win_pct = 0.0
 118
 119     games_played.append(GamesPlayed('overall', overall_games, overall_wins,
 120         overall_losses, overall_win_pct))
 121
 122     # sort the resulting list by # of games played
 123     games_played = sorted(games_played, key=lambda x:x.games)
 124     games_played.reverse()
 125
 126     return games_played
 127
 128
 129 def get_overall_stats(player_id):
 130     """
 131     Provides a breakdown of stats by gametype played by player_id.
 132
 133     Returns a dictionary of namedtuples with the following members:
 134         - total_kills
 135         - total_deaths
 136         - k_d_ratio
 137         - last_played (last time the player played the game type)
 138         - last_played_epoch (same as above, but in seconds since epoch)
 139         - last_played_fuzzy (same as above, but in relative date)
 140         - total_playing_time (total amount of time played the game type)
 141         - total_playing_time_secs (same as the above, but in seconds)
 142         - total_pickups (ctf only)
 143         - total_captures (ctf only)
 144         - cap_ratio (ctf only)
 145         - total_carrier_frags (ctf only)
 146         - game_type_cd
 147         - game_type_descr
 148
 149     The key to the dictionary is the game type code. There is also an
 150     "overall" game_type_cd which sums the totals and computes the total ratios.
 151     """
 152     OverallStats = namedtuple('OverallStats', ['total_kills', 'total_deaths',
 153         'k_d_ratio', 'last_played', 'last_played_epoch', 'last_played_fuzzy',
 154         'total_playing_time', 'total_playing_time_secs', 'total_pickups', 'total_captures', 'cap_ratio',
 155         'total_carrier_frags', 'game_type_cd', 'game_type_descr'])
 156
 157     raw_stats = DBSession.query('game_type_cd', 'game_type_descr',
 158             'total_kills', 'total_deaths', 'last_played', 'total_playing_time',
 159             'total_pickups', 'total_captures', 'total_carrier_frags').\
 160             from_statement(
 161                 "SELECT g.game_type_cd, "
 162                        "gt.descr game_type_descr, "
 163                        "Sum(pgs.kills)         total_kills, "
 164                        "Sum(pgs.deaths)        total_deaths, "
 165                        "Max(pgs.create_dt)     last_played, "
 166                        "Sum(pgs.alivetime)     total_playing_time, "
 167                        "Sum(pgs.pickups)       total_pickups, "
 168                        "Sum(pgs.captures)      total_captures, "
 169                        "Sum(pgs.carrier_frags) total_carrier_frags "
 170                 "FROM   games g, "
 171                        "cd_game_type gt, "
 172                        "player_game_stats pgs "
 173                 "WHERE  g.game_id = pgs.game_id "
 174                   "AND  g.game_type_cd = gt.game_type_cd "
 175                   "AND  g.players @> ARRAY[:player_id] "
 176                   "AND  pgs.player_id = :player_id "
 177                 "GROUP  BY g.game_type_cd, game_type_descr "
 178                 "UNION "
 179                 "SELECT 'overall'              game_type_cd, "
 180                        "'Overall'              game_type_descr, "
 181                        "Sum(pgs.kills)         total_kills, "
 182                        "Sum(pgs.deaths)        total_deaths, "
 183                        "Max(pgs.create_dt)     last_played, "
 184                        "Sum(pgs.alivetime)     total_playing_time, "
 185                        "Sum(pgs.pickups)       total_pickups, "
 186                        "Sum(pgs.captures)      total_captures, "
 187                        "Sum(pgs.carrier_frags) total_carrier_frags "
 188                 "FROM   player_game_stats pgs "
 189                 "WHERE  pgs.player_id = :player_id "
 190             ).params(player_id=player_id).all()
 191
 192     # to be indexed by game_type_cd
 193     overall_stats = {}
 194
 195     for row in raw_stats:
 196         # individual gametype ratio calculations
 197         try:
 198             k_d_ratio = float(row.total_kills)/row.total_deaths
 199         except:
 200             k_d_ratio = None
 201
 202         try:
 203             cap_ratio = float(row.total_captures)/row.total_pickups
 204         except:
 205             cap_ratio = None
 206
 207         # everything else is untouched or "raw"
 208         os = OverallStats(total_kills=row.total_kills,
 209                 total_deaths=row.total_deaths,
 210                 k_d_ratio=k_d_ratio,
 211                 last_played=row.last_played,
 212                 last_played_epoch=timegm(row.last_played.timetuple()),
 213                 last_played_fuzzy=pretty_date(row.last_played),
 214                 total_playing_time=row.total_playing_time,
 215                 total_playing_time_secs=int(datetime_seconds(row.total_playing_time)),
 216                 total_pickups=row.total_pickups,
 217                 total_captures=row.total_captures,
 218                 cap_ratio=cap_ratio,
 219                 total_carrier_frags=row.total_carrier_frags,
 220                 game_type_cd=row.game_type_cd,
 221                 game_type_descr=row.game_type_descr)
 222
 223         overall_stats[row.game_type_cd] = os
 224
 225     # We have to edit "overall" stats to exclude deaths in CTS.
 226     # Although we still want to record deaths, they shouldn't
 227     # count towards the overall K:D ratio.
 228     if 'cts' in overall_stats:
 229         os = overall_stats['overall']
 230
 231         try:
 232             k_d_ratio = float(os.total_kills)/(os.total_deaths - overall_stats['cts'].total_deaths)
 233         except:
 234             k_d_ratio = None
 235
 236         non_cts_deaths = os.total_deaths - overall_stats['cts'].total_deaths
 237
 238
 239         overall_stats['overall'] = OverallStats(
 240                 total_kills             = os.total_kills,
 241                 total_deaths            = non_cts_deaths,
 242                 k_d_ratio               = k_d_ratio,
 243                 last_played             = os.last_played,
 244                 last_played_epoch       = os.last_played_epoch,
 245                 last_played_fuzzy       = os.last_played_fuzzy,
 246                 total_playing_time      = os.total_playing_time,
 247                 total_playing_time_secs = os.total_playing_time_secs,
 248                 total_pickups           = os.total_pickups,
 249                 total_captures          = os.total_captures,
 250                 cap_ratio               = os.cap_ratio,
 251                 total_carrier_frags     = os.total_carrier_frags,
 252                 game_type_cd            = os.game_type_cd,
 253                 game_type_descr         = os.game_type_descr)
 254
 255     return overall_stats
 256
 257
 258 def get_fav_maps(player_id, game_type_cd=None):
 259     """
 260     Provides a breakdown of favorite maps by gametype.
 261
 262     Returns a dictionary of namedtuples with the following members:
 263         - game_type_cd
 264         - map_name (map name)
 265         - map_id
 266         - times_played
 267
 268     The favorite map is defined as the map you've played the most
 269     for the given game_type_cd.
 270
 271     The key to the dictionary is the game type code. There is also an
 272     "overall" game_type_cd which is the overall favorite map. This is
 273     defined as the favorite map of the game type you've played the
 274     most. The input parameter game_type_cd is for this.
 275     """
 276     FavMap = namedtuple('FavMap', ['map_name', 'map_id', 'times_played', 'game_type_cd'])
 277
 278     raw_favs = DBSession.query('game_type_cd', 'map_name',
 279             'map_id', 'times_played').\
 280             from_statement(
 281                 "SELECT game_type_cd, "
 282                        "name map_name, "
 283                        "map_id, "
 284                        "times_played "
 285                 "FROM   (SELECT g.game_type_cd, "
 286                                "m.name, "
 287                                "m.map_id, "
 288                                "Count(*) times_played, "
 289                                "Row_number() "
 290                                  "OVER ( "
 291                                    "partition BY g.game_type_cd "
 292                                    "ORDER BY Count(*) DESC, m.map_id ASC) rank "
 293                         "FROM   games g, "
 294                                "player_game_stats pgs, "
 295                                "maps m "
 296                         "WHERE  g.game_id = pgs.game_id "
 297                                "AND g.map_id = m.map_id "
 298                                "AND g.players @> ARRAY[:player_id]"
 299                                "AND pgs.player_id = :player_id "
 300                         "GROUP  BY g.game_type_cd, "
 301                                   "m.map_id, "
 302                                   "m.name) most_played "
 303                 "WHERE  rank = 1 "
 304                 "ORDER BY  times_played desc "
 305             ).params(player_id=player_id).all()
 306
 307     fav_maps = {}
 308     overall_fav = None
 309     for row in raw_favs:
 310         fv = FavMap(map_name=row.map_name,
 311             map_id=row.map_id,
 312             times_played=row.times_played,
 313             game_type_cd=row.game_type_cd)
 314
 315         # if we aren't given a favorite game_type_cd
 316         # then the overall favorite is the one we've
 317         # played the most
 318         if overall_fav is None:
 319             fav_maps['overall'] = fv
 320             overall_fav = fv.game_type_cd
 321
 322         # otherwise it is the favorite map from the
 323         # favorite game_type_cd (provided as a param)
 324         # and we'll overwrite the first dict entry
 325         if game_type_cd == fv.game_type_cd:
 326             fav_maps['overall'] = fv
 327
 328         fav_maps[row.game_type_cd] = fv
 329
 330     return fav_maps
 331
 332
 333 def get_ranks(player_id):
 334     """
 335     Provides a breakdown of the player's ranks by game type.
 336
 337     Returns a dictionary of namedtuples with the following members:
 338         - game_type_cd
 339         - rank
 340         - max_rank
 341
 342     The key to the dictionary is the game type code. There is also an
 343     "overall" game_type_cd which is the overall best rank.
 344     """
 345     Rank = namedtuple('Rank', ['rank', 'max_rank', 'percentile', 'game_type_cd'])
 346
 347     raw_ranks = DBSession.query("game_type_cd", "rank", "max_rank").\
 348             from_statement(
 349                 "select pr.game_type_cd, pr.rank, overall.max_rank "
 350                 "from player_ranks pr,  "
 351                    "(select game_type_cd, max(rank) max_rank "
 352                     "from player_ranks  "
 353                     "group by game_type_cd) overall "
 354                 "where pr.game_type_cd = overall.game_type_cd  "
 355                 "and max_rank > 1 "
 356                 "and player_id = :player_id "
 357                 "order by rank").\
 358             params(player_id=player_id).all()
 359
 360     ranks = {}
 361     found_top_rank = False
 362     for row in raw_ranks:
 363         rank = Rank(rank=row.rank,
 364             max_rank=row.max_rank,
 365             percentile=100 - 100*float(row.rank-1)/(row.max_rank-1),
 366             game_type_cd=row.game_type_cd)
 367
 368
 369         if not found_top_rank:
 370             ranks['overall'] = rank
 371             found_top_rank = True
 372         elif rank.percentile > ranks['overall'].percentile:
 373             ranks['overall'] = rank
 374
 375         ranks[row.game_type_cd] = rank
 376
 377     return ranks;
 378
 379
 380 def get_elos(player_id):
 381     """
 382     Provides a breakdown of the player's elos by game type.
 383
 384     Returns a dictionary of namedtuples with the following members:
 385         - player_id
 386         - game_type_cd
 387         - games
 388         - elo
 389
 390     The key to the dictionary is the game type code. There is also an
 391     "overall" game_type_cd which is the overall best rank.
 392     """
 393     raw_elos = DBSession.query(PlayerElo).filter_by(player_id=player_id).\
 394             order_by(PlayerElo.elo.desc()).all()
 395
 396     elos = {}
 397     found_max_elo = False
 398     for row in raw_elos:
 399         if not found_max_elo:
 400             elos['overall'] = row
 401             found_max_elo = True
 402
 403         elos[row.game_type_cd] = row
 404
 405     return elos
 406
 407
 408 def get_recent_games(player_id, limit=10):
 409     """
 410     Provides a list of recent games for a player. Uses the recent_games_q helper.
 411     """
 412     # recent games played in descending order
 413     rgs = recent_games_q(player_id=player_id, force_player_id=True).limit(limit).all()
 414     recent_games = [RecentGame(row) for row in rgs]
 415
 416     return recent_games
 417
 418
 419 def get_accuracy_stats(player_id, weapon_cd, games):
 420     """
 421     Provides accuracy for weapon_cd by player_id for the past N games.
 422     """
 423     # Reaching back 90 days should give us an accurate enough average
 424     # We then multiply this out for the number of data points (games) to
 425     # create parameters for a flot graph
 426     try:
 427         raw_avg = DBSession.query(func.sum(PlayerWeaponStat.hit),
 428                 func.sum(PlayerWeaponStat.fired)).\
 429                 filter(PlayerWeaponStat.player_id == player_id).\
 430                 filter(PlayerWeaponStat.weapon_cd == weapon_cd).\
 431                 one()
 432
 433         avg = round(float(raw_avg[0])/raw_avg[1]*100, 2)
 434
 435         # Determine the raw accuracy (hit, fired) numbers for $games games
 436         # This is then enumerated to create parameters for a flot graph
 437         raw_accs = DBSession.query(PlayerWeaponStat.game_id,
 438             PlayerWeaponStat.hit, PlayerWeaponStat.fired).\
 439                 filter(PlayerWeaponStat.player_id == player_id).\
 440                 filter(PlayerWeaponStat.weapon_cd == weapon_cd).\
 441                 order_by(PlayerWeaponStat.game_id.desc()).\
 442                 limit(games).\
 443                 all()
 444
 445         # they come out in opposite order, so flip them in the right direction
 446         raw_accs.reverse()
 447
 448         accs = []
 449         for i in range(len(raw_accs)):
 450             accs.append((raw_accs[i][0], round(float(raw_accs[i][1])/raw_accs[i][2]*100, 2)))
 451     except:
 452         accs = []
 453         avg = 0.0
 454
 455     return (avg, accs)
 456
 457
 458 def get_damage_stats(player_id, weapon_cd, games):
 459     """
 460     Provides damage info for weapon_cd by player_id for the past N games.
 461     """
 462     try:
 463         raw_avg = DBSession.query(func.sum(PlayerWeaponStat.actual),
 464                 func.sum(PlayerWeaponStat.hit)).\
 465                 filter(PlayerWeaponStat.player_id == player_id).\
 466                 filter(PlayerWeaponStat.weapon_cd == weapon_cd).\
 467                 one()
 468
 469         avg = round(float(raw_avg[0])/raw_avg[1], 2)
 470
 471         # Determine the damage efficiency (hit, fired) numbers for $games games
 472         # This is then enumerated to create parameters for a flot graph
 473         raw_dmgs = DBSession.query(PlayerWeaponStat.game_id,
 474             PlayerWeaponStat.actual, PlayerWeaponStat.hit).\
 475                 filter(PlayerWeaponStat.player_id == player_id).\
 476                 filter(PlayerWeaponStat.weapon_cd == weapon_cd).\
 477                 order_by(PlayerWeaponStat.game_id.desc()).\
 478                 limit(games).\
 479                 all()
 480
 481         # they come out in opposite order, so flip them in the right direction
 482         raw_dmgs.reverse()
 483
 484         dmgs = []
 485         for i in range(len(raw_dmgs)):
 486             # try to derive, unless we've hit nothing then set to 0!
 487             try:
 488                 dmg = round(float(raw_dmgs[i][1])/raw_dmgs[i][2], 2)
 489             except:
 490                 dmg = 0.0
 491
 492             dmgs.append((raw_dmgs[i][0], dmg))
 493     except Exception as e:
 494         dmgs = []
 495         avg = 0.0
 496
 497     return (avg, dmgs)
 498
 499
 500 def get_player_medals(player_id):
 501     """Retrieves the list of medals the player has received from tournaments or
 502     other contests."""
 503     try:
 504         medals = DBSession.query(PlayerMedal)\
 505                 .filter(PlayerMedal.player_id==player_id)\
 506                 .order_by(PlayerMedal.place)\
 507                 .order_by(PlayerMedal.create_dt)\
 508                 .all()
 509
 510         return medals
 511
 512     except Exception as e:
 513         log.debug(e)
 514         return []
 515
 516
 517 def player_info_data(request):
 518     player_id = int(request.matchdict['id'])
 519     if player_id <= 2:
 520         player_id = -1;
 521
 522     try:
 523         player = DBSession.query(Player).filter_by(player_id=player_id).\
 524                 filter(Player.active_ind == True).one()
 525
 526         games_played   = get_games_played(player_id)
 527         overall_stats  = get_overall_stats(player_id)
 528         fav_maps       = get_fav_maps(player_id)
 529         elos           = get_elos(player_id)
 530         ranks          = get_ranks(player_id)
 531         medals         = get_player_medals(player_id)
 532         recent_games   = get_recent_games(player_id)
 533         cake_day       = is_cake_day(player.create_dt)
 534
 535     except Exception as e:
 536         raise pyramid.httpexceptions.HTTPNotFound
 537
 538         ## do not raise application exceptions here (only for debugging)
 539         # raise e
 540
 541     return {'player':player,
 542             'games_played':games_played,
 543             'overall_stats':overall_stats,
 544             'fav_maps':fav_maps,
 545             'elos':elos,
 546             'ranks':ranks,
 547             'medals':medals,
 548             'recent_games':recent_games,
 549             'cake_day':cake_day,
 550             }
 551
 552
 553 def player_info(request):
 554     """
 555     Provides detailed information on a specific player
 556     """
 557     return player_info_data(request)
 558
 559
 560 def player_info_json(request):
 561     """
 562     Provides detailed information on a specific player. JSON.
 563     """
 564
 565     # All player_info fields are converted into JSON-formattable dictionaries
 566     player_info = player_info_data(request)
 567
 568     player = player_info['player'].to_dict()
 569
 570     games_played = {}
 571     for game in player_info['games_played']:
 572         games_played[game.game_type_cd] = to_json(game)
 573
 574     overall_stats = {}
 575     for gt,stats in player_info['overall_stats'].items():
 576         overall_stats[gt] = to_json(stats)
 577
 578     elos = {}
 579     for gt,elo in player_info['elos'].items():
 580         elos[gt] = to_json(elo.to_dict())
 581
 582     ranks = {}
 583     for gt,rank in player_info['ranks'].items():
 584         ranks[gt] = to_json(rank)
 585
 586     fav_maps = {}
 587     for gt,mapinfo in player_info['fav_maps'].items():
 588         fav_maps[gt] = to_json(mapinfo)
 589
 590     recent_games = [g.to_dict() for g in player_info['recent_games']]
 591
 592     return [{
 593         'player':           player,
 594         'games_played':     games_played,
 595         'overall_stats':    overall_stats,
 596         'fav_maps':         fav_maps,
 597         'elos':             elos,
 598         'ranks':            ranks,
 599         'recent_games':     recent_games,
 600     }]
 601
 602
 603 def player_game_index_data(request):
 604     try:
 605         player_id = int(request.matchdict['player_id'])
 606     except:
 607         player_id = -1
 608
 609     game_type_cd = None
 610     game_type_descr = None
 611
 612     if request.params.has_key('type'):
 613         game_type_cd = request.params['type']
 614         try:
 615             game_type_descr = DBSession.query(GameType.descr).\
 616                 filter(GameType.game_type_cd == game_type_cd).\
 617                 one()[0]
 618         except Exception as e:
 619             pass
 620
 621     else:
 622         game_type_cd = None
 623         game_type_descr = None
 624
 625     if request.params.has_key('page'):
 626         current_page = request.params['page']
 627     else:
 628         current_page = 1
 629
 630     try:
 631         player = DBSession.query(Player).\
 632                 filter_by(player_id=player_id).\
 633                 filter(Player.active_ind == True).\
 634                 one()
 635
 636         rgs_q = recent_games_q(player_id=player.player_id,
 637             force_player_id=True, game_type_cd=game_type_cd)
 638
 639         games = Page(rgs_q, current_page, items_per_page=20, url=page_url)
 640
 641         # replace the items in the canned pagination class with more rich ones
 642         games.items = [RecentGame(row) for row in games.items]
 643
 644         games_played = get_games_played(player_id)
 645
 646     except Exception as e:
 647         raise e
 648         player = None
 649         games = None
 650         game_type_cd = None
 651         game_type_descr = None
 652         games_played = None
 653
 654     return {
 655             'player_id':player.player_id,
 656             'player':player,
 657             'games':games,
 658             'game_type_cd':game_type_cd,
 659             'game_type_descr':game_type_descr,
 660             'games_played':games_played,
 661            }
 662
 663
 664 def player_game_index(request):
 665     """
 666     Provides an index of the games in which a particular
 667     player was involved. This is ordered by game_id, with
 668     the most recent game_ids first. Paginated.
 669     """
 670     return player_game_index_data(request)
 671
 672
 673 def player_game_index_json(request):
 674     """
 675     Provides an index of the games in which a particular
 676     player was involved. This is ordered by game_id, with
 677     the most recent game_ids first. Paginated. JSON.
 678     """
 679     return [{'status':'not implemented'}]
 680
 681
 682 def player_accuracy_data(request):
 683     player_id = request.matchdict['id']
 684     allowed_weapons = ['nex', 'rifle', 'shotgun', 'uzi', 'minstanex']
 685     weapon_cd = 'nex'
 686     games = 20
 687
 688     if request.params.has_key('weapon'):
 689         if request.params['weapon'] in allowed_weapons:
 690             weapon_cd = request.params['weapon']
 691
 692     if request.params.has_key('games'):
 693         try:
 694             games = request.params['games']
 695
 696             if games < 0:
 697                 games = 20
 698             if games > 50:
 699                 games = 50
 700         except:
 701             games = 20
 702
 703     (avg, accs) = get_accuracy_stats(player_id, weapon_cd, games)
 704
 705     # if we don't have enough data for the given weapon
 706     if len(accs) < games:
 707         games = len(accs)
 708
 709     return {
 710             'player_id':player_id,
 711             'player_url':request.route_url('player_info', id=player_id),
 712             'weapon':weapon_cd,
 713             'games':games,
 714             'avg':avg,
 715             'accs':accs
 716             }
 717
 718
 719 def player_accuracy(request):
 720     """
 721     Provides the accuracy for the given weapon. (JSON only)
 722     """
 723     return player_accuracy_data(request)
 724
 725
 726 def player_accuracy_json(request):
 727     """
 728     Provides a JSON response representing the accuracy for the given weapon.
 729
 730     Parameters:
 731        weapon = which weapon to display accuracy for. Valid values are 'nex',
 732                 'shotgun', 'uzi', and 'minstanex'.
 733        games = over how many games to display accuracy. Can be up to 50.
 734     """
 735     return player_accuracy_data(request)
 736
 737
 738 def player_damage_data(request):
 739     player_id = request.matchdict['id']
 740     allowed_weapons = ['grenadelauncher', 'electro', 'crylink', 'hagar',
 741             'rocketlauncher', 'laser']
 742     weapon_cd = 'rocketlauncher'
 743     games = 20
 744
 745     if request.params.has_key('weapon'):
 746         if request.params['weapon'] in allowed_weapons:
 747             weapon_cd = request.params['weapon']
 748
 749     if request.params.has_key('games'):
 750         try:
 751             games = request.params['games']
 752
 753             if games < 0:
 754                 games = 20
 755             if games > 50:
 756                 games = 50
 757         except:
 758             games = 20
 759
 760     (avg, dmgs) = get_damage_stats(player_id, weapon_cd, games)
 761
 762     # if we don't have enough data for the given weapon
 763     if len(dmgs) < games:
 764         games = len(dmgs)
 765
 766     return {
 767             'player_id':player_id,
 768             'player_url':request.route_url('player_info', id=player_id),
 769             'weapon':weapon_cd,
 770             'games':games,
 771             'avg':avg,
 772             'dmgs':dmgs
 773             }
 774
 775
 776 def player_damage_json(request):
 777     """
 778     Provides a JSON response representing the damage for the given weapon.
 779
 780     Parameters:
 781        weapon = which weapon to display damage for. Valid values are
 782          'grenadelauncher', 'electro', 'crylink', 'hagar', 'rocketlauncher',
 783          'laser'.
 784        games = over how many games to display damage. Can be up to 50.
 785     """
 786     return player_damage_data(request)
 787
 788
 789 def player_hashkey_info_data(request):
 790     # hashkey = request.matchdict['hashkey']
 791
 792     # the incoming hashkey is double quoted, and WSGI unquotes once...
 793     # hashkey = unquote(hashkey)
 794
 795     # if using request verification to obtain the hashkey
 796     (idfp, status) = verify_request(request)
 797     log.debug("d0_blind_id verification: idfp={0} status={1}\n".format(idfp, status))
 798
 799     log.debug("\n----- BEGIN REQUEST BODY -----\n" + request.body +
 800             "----- END REQUEST BODY -----\n\n")
 801
 802     # if config is to *not* verify requests and we get nothing back, this
 803     # query will return nothing and we'll 404.
 804     try:
 805         player = DBSession.query(Player).\
 806                 filter(Player.player_id == Hashkey.player_id).\
 807                 filter(Player.active_ind == True).\
 808                 filter(Hashkey.hashkey == idfp).one()
 809
 810         games_played      = get_games_played(player.player_id)
 811         overall_stats     = get_overall_stats(player.player_id)
 812         fav_maps          = get_fav_maps(player.player_id)
 813         elos              = get_elos(player.player_id)
 814         ranks             = get_ranks(player.player_id)
 815         most_recent_game  = get_recent_games(player.player_id, 1)[0]
 816
 817     except Exception as e:
 818         raise pyramid.httpexceptions.HTTPNotFound
 819
 820     return {'player':player,
 821             'hashkey':idfp,
 822             'games_played':games_played,
 823             'overall_stats':overall_stats,
 824             'fav_maps':fav_maps,
 825             'elos':elos,
 826             'ranks':ranks,
 827             'most_recent_game':most_recent_game,
 828             }
 829
 830
 831 def player_hashkey_info_json(request):
 832     """
 833     Provides detailed information on a specific player. JSON.
 834     """
 835
 836     # All player_info fields are converted into JSON-formattable dictionaries
 837     player_info = player_hashkey_info_data(request)
 838
 839     player = player_info['player'].to_dict()
 840
 841     games_played = {}
 842     for game in player_info['games_played']:
 843         games_played[game.game_type_cd] = to_json(game)
 844
 845     overall_stats = {}
 846     for gt,stats in player_info['overall_stats'].items():
 847         overall_stats[gt] = to_json(stats)
 848
 849     elos = {}
 850     for gt,elo in player_info['elos'].items():
 851         elos[gt] = to_json(elo.to_dict())
 852
 853     ranks = {}
 854     for gt,rank in player_info['ranks'].items():
 855         ranks[gt] = to_json(rank)
 856
 857     fav_maps = {}
 858     for gt,mapinfo in player_info['fav_maps'].items():
 859         fav_maps[gt] = to_json(mapinfo)
 860
 861     most_recent_game = to_json(player_info['most_recent_game'])
 862
 863     return [{
 864         'version':          1,
 865         'player':           player,
 866         'games_played':     games_played,
 867         'overall_stats':    overall_stats,
 868         'fav_maps':         fav_maps,
 869         'elos':             elos,
 870         'ranks':            ranks,
 871         'most_recent_game': most_recent_game,
 872     }]
 873
 874
 875 def player_hashkey_info_text(request):
 876     """
 877     Provides detailed information on a specific player. Plain text.
 878     """
 879     # UTC epoch
 880     now = timegm(datetime.datetime.utcnow().timetuple())
 881
 882     # All player_info fields are converted into JSON-formattable dictionaries
 883     player_info = player_hashkey_info_data(request)
 884
 885     # gather all of the data up into aggregate structures
 886     player = player_info['player']
 887     games_played = player_info['games_played']
 888     overall_stats = player_info['overall_stats']
 889     elos = player_info['elos']
 890     ranks = player_info['ranks']
 891     fav_maps = player_info['fav_maps']
 892     most_recent_game = player_info['most_recent_game']
 893
 894     # one-offs for things needing conversion for text/plain
 895     player_joined = timegm(player.create_dt.timetuple())
 896     player_joined_dt = player.create_dt
 897     alivetime = int(datetime_seconds(overall_stats['overall'].total_playing_time))
 898
 899     # this is a plain text response, if we don't do this here then
 900     # Pyramid will assume html
 901     request.response.content_type = 'text/plain'
 902
 903     return {
 904         'version':          1,
 905         'now':              now,
 906         'player':           player,
 907         'hashkey':          player_info['hashkey'],
 908         'player_joined':    player_joined,
 909         'player_joined_dt': player_joined_dt,
 910         'games_played':     games_played,
 911         'overall_stats':    overall_stats,
 912         'alivetime':        alivetime,
 913         'fav_maps':         fav_maps,
 914         'elos':             elos,
 915         'ranks':            ranks,
 916         'most_recent_game': most_recent_game,
 917     }
 918
 919
 920 def player_elo_info_data(request):
 921     """
 922     Provides elo information on a specific player. Raw data is returned.
 923     """
 924     (idfp, status) = verify_request(request)
 925     log.debug("d0_blind_id verification: idfp={0} status={1}\n".format(idfp, status))
 926
 927     log.debug("\n----- BEGIN REQUEST BODY -----\n" + request.body +
 928             "----- END REQUEST BODY -----\n\n")
 929
 930     hashkey = request.matchdict['hashkey']
 931
 932     # the incoming hashkey is double quoted, and WSGI unquotes once...
 933     hashkey = unquote(hashkey)
 934
 935     try:
 936         player = DBSession.query(Player).\
 937                 filter(Player.player_id == Hashkey.player_id).\
 938                 filter(Player.active_ind == True).\
 939                 filter(Hashkey.hashkey == hashkey).one()
 940
 941         elos = get_elos(player.player_id)
 942
 943     except Exception as e:
 944         log.debug(e)
 945         raise pyramid.httpexceptions.HTTPNotFound
 946
 947     return {
 948         'hashkey':hashkey,
 949         'player':player,
 950         'elos':elos,
 951     }
 952
 953
 954 def player_elo_info_json(request):
 955     """
 956     Provides elo information on a specific player. JSON.
 957     """
 958     elo_info = player_elo_info_data(request)
 959
 960     player = player_info['player'].to_dict()
 961
 962     elos = {}
 963     for gt, elo in elo_info['elos'].items():
 964         elos[gt] = to_json(elo.to_dict())
 965
 966     return [{
 967         'version':          1,
 968         'player':           player,
 969         'elos':             elos,
 970     }]
 971
 972
 973 def player_elo_info_text(request):
 974     """
 975     Provides elo information on a specific player. Plain text.
 976     """
 977     # UTC epoch
 978     now = timegm(datetime.datetime.utcnow().timetuple())
 979
 980     # All player_info fields are converted into JSON-formattable dictionaries
 981     elo_info = player_elo_info_data(request)
 982
 983     # this is a plain text response, if we don't do this here then
 984     # Pyramid will assume html
 985     request.response.content_type = 'text/plain'
 986
 987     return {
 988         'version':          1,
 989         'now':              now,
 990         'hashkey':          elo_info['hashkey'],
 991         'player':           elo_info['player'],
 992         'elos':             elo_info['elos'],
 993     }
 994
 995
 996 def player_captimes_data(request):
 997     player_id = int(request.matchdict['player_id'])
 998     if player_id <= 2:
 999         player_id = -1;
1000
1001     page = request.params.get("page", 1)
1002
1003     sort = request.params.get("sort", "create_dt")
1004
1005     try:
1006         player = DBSession.query(Player).filter_by(player_id=player_id).one()
1007
1008         pct_q = DBSession.query(PlayerCaptime.fastest_cap, PlayerCaptime.create_dt,
1009                 PlayerCaptime.player_id, PlayerCaptime.game_id, PlayerCaptime.map_id,
1010                 Map.name.label('map_name'), Game.server_id, Server.name.label('server_name')).\
1011                 filter(PlayerCaptime.player_id==player_id).\
1012                 filter(PlayerCaptime.game_id==Game.game_id).\
1013                 filter(PlayerCaptime.map_id==Map.map_id).\
1014                 filter(Game.server_id==Server.server_id)
1015
1016         if sort == "fastest":
1017             pct_q = pct_q.order_by(PlayerCaptime.fastest_cap)
1018         else:
1019             sort = "create_dt"
1020             pct_q = pct_q.order_by(expr.desc(PlayerCaptime.create_dt))
1021
1022     except Exception as e:
1023         raise pyramid.httpexceptions.HTTPNotFound
1024
1025     captimes = Page(pct_q, page, items_per_page=20, url=page_url)
1026
1027     # replace the items in the canned pagination class with more rich ones
1028     captimes.items = [PlayerCapTime(row) for row in captimes.items]
1029
1030     return {
1031             "player_id" : player_id,
1032             "player"    : player,
1033             "captimes"  : captimes,
1034             "page"      : page,
1035             "sort"      : sort,
1036         }
1037
1038
1039 def player_captimes(request):
1040     return player_captimes_data(request)
1041
1042
1043 def player_captimes_json(request):
1044     data = player_captimes_data(request)
1045     page = request.params.get("page", 1)
1046
1047     # perform any necessary JSON conversions
1048     player_id = data["player_id"]
1049     player = data["player"].to_dict()
1050     captimes = [ct.to_dict() for ct in data["captimes"].items]
1051
1052     return {
1053             "player"    : player,
1054             "captimes"  : captimes,
1055             "page"      : page,
1056             }
1057
1058
1059 def player_weaponstats_data_json(request):
1060     player_id = int(request.matchdict["id"])
1061     if player_id <= 2:
1062         player_id = -1;
1063
1064     game_type_cd = request.params.get("game_type", None)
1065     if game_type_cd == "overall":
1066         game_type_cd = None
1067
1068     limit = 20
1069     if request.params.has_key("limit"):
1070         limit = int(request.params["limit"])
1071
1072         if limit < 0:
1073             limit = 20
1074         if limit > 50:
1075             limit = 50
1076
1077
1078     # the game_ids of the most recently played ones
1079     # of the given game type is used for a subquery
1080     games_list = DBSession.query(Game.game_id).\
1081             filter(Game.players.contains([player_id]))
1082
1083     if game_type_cd is not None:
1084         games_list = games_list.filter(Game.game_type_cd == game_type_cd)
1085
1086     games_list = games_list.order_by(Game.game_id.desc()).limit(limit)
1087
1088     weapon_stats_raw = DBSession.query(PlayerWeaponStat).\
1089         filter(PlayerWeaponStat.player_id == player_id).\
1090         filter(PlayerWeaponStat.game_id.in_(games_list)).\
1091         all()
1092
1093     games_to_weapons = {}
1094     weapons_used = {}
1095     sum_avgs = {}
1096     for ws in weapon_stats_raw:
1097         if ws.game_id not in games_to_weapons:
1098             games_to_weapons[ws.game_id] = [ws.weapon_cd]
1099         else:
1100             games_to_weapons[ws.game_id].append(ws.weapon_cd)
1101
1102         weapons_used[ws.weapon_cd] = weapons_used.get(ws.weapon_cd, 0) + 1
1103         sum_avgs[ws.weapon_cd] = sum_avgs.get(ws.weapon_cd, 0) + float(ws.hit)/float(ws.fired)
1104
1105     # Creating zero-valued weapon stat entries for games where a weapon was not
1106     # used in that game, but was used in another game for the set. This makes
1107     # the charts look smoother
1108     for game_id in games_to_weapons.keys():
1109         for weapon_cd in set(weapons_used.keys()) - set(games_to_weapons[game_id]):
1110             weapon_stats_raw.append(PlayerWeaponStat(player_id=player_id,
1111                 game_id=game_id, weapon_cd=weapon_cd))
1112
1113     # averages for the weapons used in the range
1114     avgs = {}
1115     for w in weapons_used.keys():
1116         avgs[w] = round(sum_avgs[w]/float(weapons_used[w])*100, 2)
1117
1118     weapon_stats_raw = sorted(weapon_stats_raw, key = lambda x: x.game_id)
1119     games            = sorted(games_to_weapons.keys())
1120     weapon_stats     = [ws.to_dict() for ws in weapon_stats_raw]
1121
1122     return {
1123         "weapon_stats": weapon_stats,
1124         "weapons_used": weapons_used.keys(),
1125         "games": games,
1126         "averages": avgs,
1127     }
1128
1129
1130 def player_versus_data(request):
1131     try:
1132         p1_id = int(request.params.get("p1", None))
1133         p2_id = int(request.params.get("p2", None))
1134
1135         p1_wins = 0
1136         p2_wins = 0
1137
1138         players = DBSession.query(Player).filter(sa.or_(Player.player_id ==
1139             p1_id, Player.player_id == p2_id)).order_by(Player.player_id).all()
1140
1141
1142         if len(players) < 2:
1143             raise Exception("Not enough players found.")
1144
1145         # assign the players from the array retrieved above
1146         if players[0].player_id == p1_id:
1147             p1 = players[0]
1148             p2 = players[1]
1149         else:
1150             p1 = players[1]
1151             p2 = players[0]
1152
1153         # note that wins and losses are from p1's perspective
1154         win_loss_sql = """select win_loss, count(1)
1155             from (
1156               select case
1157                 when pgsp1.score >= pgsp2.score then 'win'
1158                 else 'loss'
1159               end win_loss
1160               from games g join player_game_stats pgsp1
1161                 on g.game_id = pgsp1.game_id and pgsp1.player_id = :p1
1162               join player_game_stats pgsp2
1163                 on g.game_id = pgsp2.game_id and pgsp2.player_id = :p2
1164               where g.players @> ARRAY[:p1,:p2]
1165               and g.game_type_cd = 'duel'
1166               and pgsp1.create_dt between g.create_dt - interval '1 hour'
1167                 and g.create_dt + interval '1 hour'
1168               and pgsp2.create_dt between g.create_dt - interval '1 hour'
1169                 and g.create_dt + interval '1 hour'
1170             ) wl
1171             group by win_loss
1172             """
1173
1174         wins_losses = DBSession.query("win_loss", "count").\
1175                 from_statement(win_loss_sql).\
1176                 params(p1=p1_id, p2=p2_id).all()
1177
1178         for row in wins_losses:
1179             if row.win_loss == "win":
1180                 p1_wins = row.count
1181             elif row.win_loss == "loss":
1182                 p2_wins = row.count
1183
1184         # grab the 20 most recent games between the two
1185         rgs_raw = recent_games_q(player_id=p1_id, player_id_2=p2_id,
1186                 game_type_cd="duel").limit(20).all()
1187
1188         rgs = [RecentGame(row) for row in rgs_raw]
1189
1190     except Exception as e:
1191         log.debug(e)
1192
1193         p1_id = None
1194         p2_id = None
1195         p1 = None
1196         p2 = None
1197         p1_wins = None
1198         p2_wins = None
1199         rgs = None
1200
1201     return {
1202             "p1_id" : p1_id,
1203             "p2_id" : p2_id,
1204             "p1" : p1,
1205             "p2" : p2,
1206             "p1_wins" : p1_wins,
1207             "p2_wins" : p2_wins,
1208             "recent_games" : rgs,
1209         }
1210
1211
1212 def player_versus(request):
1213     return player_versus_data(request)