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