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