Make ranks an empty dictionary instead.
[xonotic/xonstat.git] / 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          = {}
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             = {}
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)