]> git.xonotic.org Git - xonotic/xonstat.git/blob - xonstat/views/player.py
Restructured player_info page to now include more detailed stats for specific gametyp...
[xonotic/xonstat.git] / xonstat / views / player.py
1 import datetime
2 import json
3 import logging
4 import re
5 import sqlalchemy as sa
6 import sqlalchemy.sql.functions as func
7 import time
8 from pyramid.response import Response
9 from pyramid.url import current_route_url
10 from sqlalchemy import desc, distinct
11 from webhelpers.paginate import Page, PageURL
12 from xonstat.models import *
13 from xonstat.util import page_url
14
15 log = logging.getLogger(__name__)
16
17
18 def player_index_data(request):
19     if request.params.has_key('page'):
20         current_page = request.params['page']
21     else:
22         current_page = 1
23
24     try:
25         player_q = DBSession.query(Player).\
26                 filter(Player.player_id > 2).\
27                 filter(Player.active_ind == True).\
28                 filter(sa.not_(Player.nick.like('Anonymous Player%'))).\
29                 order_by(Player.player_id.desc())
30
31         players = Page(player_q, current_page, items_per_page=10, url=page_url)
32
33     except Exception as e:
34         players = None
35         raise e
36
37     return {'players':players
38            }
39
40
41 def player_index(request):
42     """
43     Provides a list of all the current players.
44     """
45     return player_index_data(request)
46
47
48 def player_index_json(request):
49     """
50     Provides a list of all the current players. JSON.
51     """
52     return [{'status':'not implemented'}]
53
54
55 def _get_games_played(player_id):
56     """
57     DEPRECATED: Now included in _get_total_stats()
58     
59     Provides a breakdown by gametype of the games played by player_id.
60
61     Returns a tuple containing (total_games, games_breakdown), where
62     total_games is the absolute number of games played by player_id
63     and games_breakdown is an array containing (game_type_cd, # games)
64     """
65     games_played = DBSession.query(Game.game_type_cd, func.count()).\
66             filter(Game.game_id == PlayerGameStat.game_id).\
67             filter(PlayerGameStat.player_id == player_id).\
68             group_by(Game.game_type_cd).\
69             order_by(func.count().desc()).all()
70
71     total = 0
72     for (game_type_cd, games) in games_played:
73         total += games
74
75     return (total, games_played)
76
77
78 def _get_total_stats(player_id):
79     """
80     Provides aggregated stats by player_id.
81
82     Returns a dict with the keys 'kills', 'deaths', 'alivetime'.
83
84     games = how many games a player has played
85     games_breakdown = how many games of given type a player has played (dictionary)
86     kills = how many kills a player has over all games
87     deaths = how many deaths a player has over all games
88     suicides = how many suicides a player has over all games
89     alivetime = how long a player has played over all games
90     alivetime_week = how long a player has played over all games in the last week
91     alivetime_month = how long a player has played over all games in the last month
92     wins = how many games a player has won
93
94     If any of the above are None, they are set to 0.
95     """
96     # 7 and 30 day windows
97     one_week_ago  = datetime.datetime.utcnow() - datetime.timedelta(days=7)
98     one_month_ago = datetime.datetime.utcnow() - datetime.timedelta(days=30)
99
100     total_stats = {}
101
102     games_played = DBSession.query(
103             Game.game_type_cd, func.count()).\
104             filter(Game.game_id == PlayerGameStat.game_id).\
105             filter(PlayerGameStat.player_id == player_id).\
106             group_by(Game.game_type_cd).\
107             order_by(func.count().desc()).\
108             all()
109
110     total_stats['games'] = 0
111     total_stats['games_breakdown'] = {}  # this is a dictionary inside a dictionary .. dictception?
112     for (game_type_cd, games) in games_played:
113         total_stats['games'] += games
114         total_stats['games_breakdown'][game_type_cd] = games
115
116      # more fields can be added here, e.g. 'collects' for kh games
117     (total_stats['kills'], total_stats['deaths'], total_stats['suicides'],
118      total_stats['alivetime'],) = DBSession.query(
119             func.sum(PlayerGameStat.kills),
120             func.sum(PlayerGameStat.deaths),
121             func.sum(PlayerGameStat.suicides),
122             func.sum(PlayerGameStat.alivetime)).\
123             filter(PlayerGameStat.player_id == player_id).\
124             one()
125
126     (total_stats['alivetime_week'],) = DBSession.query(
127             func.sum(PlayerGameStat.alivetime)).\
128             filter(PlayerGameStat.player_id == player_id).\
129             filter(PlayerGameStat.create_dt > one_week_ago).\
130             one()
131
132     (total_stats['alivetime_month'],) = DBSession.query(
133             func.sum(PlayerGameStat.alivetime)).\
134             filter(PlayerGameStat.player_id == player_id).\
135             filter(PlayerGameStat.create_dt > one_month_ago).\
136             one()
137
138     (total_stats['wins'],) = DBSession.query(
139             func.count("*")).\
140             filter(Game.game_id == PlayerGameStat.game_id).\
141             filter(PlayerGameStat.player_id == player_id).\
142             filter(Game.winner == PlayerGameStat.team or PlayerGameStat.rank == 1).\
143             one()
144     
145     (total_stats['duel_wins'],) = DBSession.query(
146             func.count("*")).\
147             filter(Game.game_id == PlayerGameStat.game_id).\
148             filter(Game.game_type_cd == "duel").\
149             filter(PlayerGameStat.player_id == player_id).\
150             filter(PlayerGameStat.rank == 1).\
151             one()
152
153     (total_stats['duel_kills'], total_stats['duel_deaths'], total_stats['duel_suicides'],) = DBSession.query(
154             func.sum(PlayerGameStat.kills),
155             func.sum(PlayerGameStat.deaths),
156             func.sum(PlayerGameStat.suicides)).\
157             filter(Game.game_id == PlayerGameStat.game_id).\
158             filter(Game.game_type_cd == "duel").\
159             filter(PlayerGameStat.player_id == player_id).\
160             one()
161
162     (total_stats['dm_wins'],) = DBSession.query(
163             func.count("*")).\
164             filter(Game.game_id == PlayerGameStat.game_id).\
165             filter(Game.game_type_cd == "dm" or Game.game_type_cd == "tdm").\
166             filter(PlayerGameStat.player_id == player_id).\
167             filter(PlayerGameStat.rank == 1).\
168             one()
169
170     (total_stats['dm_kills'], total_stats['dm_deaths'], total_stats['dm_suicides'],) = DBSession.query(
171             func.sum(PlayerGameStat.kills),
172             func.sum(PlayerGameStat.deaths),
173             func.sum(PlayerGameStat.suicides)).\
174             filter(Game.game_id == PlayerGameStat.game_id).\
175             filter(Game.game_type_cd == "dm" or Game.game_type_cd == "tdm").\
176             filter(PlayerGameStat.player_id == player_id).\
177             one()
178
179     (total_stats['ctf_wins'],) = DBSession.query(
180             func.count("*")).\
181             filter(Game.game_id == PlayerGameStat.game_id).\
182             filter(Game.game_type_cd == "ctf").\
183             filter(PlayerGameStat.player_id == player_id).\
184             filter(PlayerGameStat.rank == 1).\
185             one()
186
187     (total_stats['ctf_caps'], total_stats['ctf_pickups'], total_stats['ctf_drops'],
188      total_stats['ctf_returns'], total_stats['ctf_fckills'],) = DBSession.query(
189             func.sum(PlayerGameStat.captures),
190             func.sum(PlayerGameStat.pickups),
191             func.sum(PlayerGameStat.drops),
192             func.sum(PlayerGameStat.returns),
193             func.sum(PlayerGameStat.carrier_frags)).\
194             filter(Game.game_id == PlayerGameStat.game_id).\
195             filter(Game.game_type_cd == "ctf").\
196             filter(PlayerGameStat.player_id == player_id).\
197             one()
198
199     for (key,value) in total_stats.items():
200         if value == None:
201             total_stats[key] = 0
202
203     return total_stats
204
205
206 def _get_fav_map(player_id):
207     """
208     Get the player's favorite map. The favorite map is defined
209     as the map that he or she has played the most in the past 
210     90 days.
211
212     Returns a dictionary with keys for the map's name and id.
213     """
214     # 90 day window
215     back_then = datetime.datetime.utcnow() - datetime.timedelta(days=90)
216
217     raw_fav_map = DBSession.query(Map.name, Map.map_id).\
218             filter(Game.game_id == PlayerGameStat.game_id).\
219             filter(Game.map_id == Map.map_id).\
220             filter(PlayerGameStat.player_id == player_id).\
221             filter(PlayerGameStat.create_dt > back_then).\
222             group_by(Map.name, Map.map_id).\
223             order_by(func.count().desc()).\
224             all()
225
226     fav_map = []
227     for map_e in raw_fav_map:
228         entry = {}
229         entry['name'] = map_e[0]
230         entry['id']   = map_e[1]
231         fav_map.append(entry)
232
233     return fav_map
234
235
236 def _get_fav_weapon(player_id):
237     """
238     Get the player's favorite weapon. The favorite weapon is defined
239     as the weapon that he or she has employed the most in the past 
240     90 days.
241
242     Returns a sequence of dictionaries with keys for the weapon's name and id.
243     The sequence holds the most-used weapons in decreasing order.
244     """
245     # 90 day window
246     back_then = datetime.datetime.utcnow() - datetime.timedelta(days=90)
247
248     raw_fav_weapon = DBSession.query(Weapon.descr, Weapon.weapon_cd).\
249             filter(Game.game_id == PlayerGameStat.game_id).\
250             filter(PlayerWeaponStat.weapon_cd == Weapon.weapon_cd).\
251             filter(PlayerGameStat.player_id == player_id).\
252             filter(PlayerGameStat.create_dt > back_then).\
253             group_by(Weapon.descr, Weapon.weapon_cd).\
254             order_by(func.count().desc()).\
255             all()
256
257     fav_weapon = []
258     for wpn_e in raw_fav_weapon:
259         entry = {}
260         entry['name'] = wpn_e[0]
261         entry['id']   = wpn_e[1]
262         fav_weapon.append(entry)
263
264     return fav_weapon
265
266
267 def _get_fav_server(player_id):
268     """
269     Get the player's favorite server. The favorite server is defined
270     as the server that he or she has played on the most in the past 
271     90 days.
272
273     Returns a sequence of dictionaries with keys for the server's name and id.
274     The sequence holds the most-used servers in decreasing order.
275     """
276     # 90 day window
277     back_then = datetime.datetime.utcnow() - datetime.timedelta(days=90)
278
279     raw_fav_server = DBSession.query(Server.name, Server.server_id).\
280             filter(Game.game_id == PlayerGameStat.game_id).\
281             filter(Game.server_id == Server.server_id).\
282             filter(PlayerGameStat.player_id == player_id).\
283             filter(PlayerGameStat.create_dt > back_then).\
284             group_by(Server.name, Server.server_id).\
285             order_by(func.count().desc()).\
286             all()
287
288     fav_server = []
289     for srv_e in raw_fav_server:
290         entry = {}
291         entry['name'] = srv_e[0]
292         entry['id']   = srv_e[1]
293         fav_server.append(entry)
294
295     return fav_server
296
297
298 def _get_rank(player_id):
299     """
300     Get the player's rank as well as the total number of ranks.
301     """
302     rank = DBSession.query("game_type_cd", "rank", "max_rank").\
303             from_statement(
304                 "select pr.game_type_cd, pr.rank, overall.max_rank "
305                 "from player_ranks pr,  "
306                    "(select game_type_cd, max(rank) max_rank "
307                     "from player_ranks  "
308                     "group by game_type_cd) overall "
309                 "where pr.game_type_cd = overall.game_type_cd  "
310                 "and player_id = :player_id "
311                 "order by rank").\
312             params(player_id=player_id).all()
313
314     return rank;
315
316
317 def get_accuracy_stats(player_id, weapon_cd, games):
318     """
319     Provides accuracy for weapon_cd by player_id for the past N games.
320     """
321     # Reaching back 90 days should give us an accurate enough average
322     # We then multiply this out for the number of data points (games) to
323     # create parameters for a flot graph
324     try:
325         raw_avg = DBSession.query(func.sum(PlayerWeaponStat.hit),
326                 func.sum(PlayerWeaponStat.fired)).\
327                 filter(PlayerWeaponStat.player_id == player_id).\
328                 filter(PlayerWeaponStat.weapon_cd == weapon_cd).\
329                 one()
330
331         avg = round(float(raw_avg[0])/raw_avg[1]*100, 2)
332
333         # Determine the raw accuracy (hit, fired) numbers for $games games
334         # This is then enumerated to create parameters for a flot graph
335         raw_accs = DBSession.query(PlayerWeaponStat.game_id, 
336             PlayerWeaponStat.hit, PlayerWeaponStat.fired).\
337                 filter(PlayerWeaponStat.player_id == player_id).\
338                 filter(PlayerWeaponStat.weapon_cd == weapon_cd).\
339                 order_by(PlayerWeaponStat.game_id.desc()).\
340                 limit(games).\
341                 all()
342
343         # they come out in opposite order, so flip them in the right direction
344         raw_accs.reverse()
345
346         accs = []
347         for i in range(len(raw_accs)):
348             accs.append((raw_accs[i][0], round(float(raw_accs[i][1])/raw_accs[i][2]*100, 2)))
349     except:
350         accs = []
351         avg = 0.0
352
353     return (avg, accs)
354
355
356 def get_damage_stats(player_id, weapon_cd, games):
357     """
358     Provides damage info for weapon_cd by player_id for the past N games.
359     """
360     try:
361         raw_avg = DBSession.query(func.sum(PlayerWeaponStat.actual),
362                 func.sum(PlayerWeaponStat.hit)).\
363                 filter(PlayerWeaponStat.player_id == player_id).\
364                 filter(PlayerWeaponStat.weapon_cd == weapon_cd).\
365                 one()
366
367         avg = round(float(raw_avg[0])/raw_avg[1], 2)
368
369         # Determine the damage efficiency (hit, fired) numbers for $games games
370         # This is then enumerated to create parameters for a flot graph
371         raw_dmgs = DBSession.query(PlayerWeaponStat.game_id, 
372             PlayerWeaponStat.actual, PlayerWeaponStat.hit).\
373                 filter(PlayerWeaponStat.player_id == player_id).\
374                 filter(PlayerWeaponStat.weapon_cd == weapon_cd).\
375                 order_by(PlayerWeaponStat.game_id.desc()).\
376                 limit(games).\
377                 all()
378
379         # they come out in opposite order, so flip them in the right direction
380         raw_dmgs.reverse()
381
382         dmgs = []
383         for i in range(len(raw_dmgs)):
384             # try to derive, unless we've hit nothing then set to 0!
385             try:
386                 dmg = round(float(raw_dmgs[i][1])/raw_dmgs[i][2], 2)
387             except:
388                 dmg = 0.0
389
390             dmgs.append((raw_dmgs[i][0], dmg))
391     except Exception as e:
392         dmgs = []
393         avg = 0.0
394
395     return (avg, dmgs)
396
397
398 def player_info_data(request):
399     player_id = int(request.matchdict['id'])
400     if player_id <= 2:
401         player_id = -1;
402
403     try:
404         player = DBSession.query(Player).filter_by(player_id=player_id).\
405                 filter(Player.active_ind == True).one()
406
407         # games played, alivetime, wins, kills, deaths
408         total_stats = _get_total_stats(player.player_id)
409
410         # games breakdown - N games played (X ctf, Y dm) etc
411         # DEPRECATED: included in total_stats, see above
412         # (total_games, games_breakdown) = _get_games_played(player.player_id)
413
414         # favorite map from the past 90 days
415         try:
416             fav_map = _get_fav_map(player.player_id)
417         except:
418             fav_map = None
419
420         # favorite weapon from the past 90 days
421         try:
422             fav_weapon = _get_fav_weapon(player.player_id)
423         except:
424             fav_weapon = None
425
426         # favorite server from the past 90 days
427         try:
428             fav_server = _get_fav_server(player.player_id)
429         except:
430             fav_server = None
431
432         # friendly display of elo information and preliminary status
433         elos = DBSession.query(PlayerElo).filter_by(player_id=player_id).\
434                 filter(PlayerElo.game_type_cd.in_(['ctf','duel','dm'])).\
435                 order_by(PlayerElo.elo.desc()).all()
436
437         elos_display = []
438         elos_dict    = {}
439         for elo in elos:
440             if elo.games > 32:
441                 str = "{0} ({1})"
442             else:
443                 str = "{0}* ({1})"
444
445             elos_display.append(str.format(round(elo.elo, 3),
446                 elo.game_type_cd))
447             elos_dict[elo.game_type_cd] = round(elo.elo, 3)
448         elos_display = ', '.join(elos_display)
449
450         # get current rank information
451         ranks = _get_rank(player_id)
452         
453         ranks_display = []
454         ranks_dict    = {}
455         for gtc,rank,max_rank in ranks:
456             ranks_display.append("{1} of {2} ({0})".format(gtc, rank, max_rank))
457             ranks_dict[gtc] = (rank, max_rank)
458         ranks_display = ', '.join(ranks_display)
459
460
461         # which weapons have been used in the past 90 days
462         # and also, used in 5 games or more?
463         back_then = datetime.datetime.utcnow() - datetime.timedelta(days=90)
464         recent_weapons = []
465         for weapon in DBSession.query(PlayerWeaponStat.weapon_cd, func.count()).\
466                 filter(PlayerWeaponStat.player_id == player_id).\
467                 filter(PlayerWeaponStat.create_dt > back_then).\
468                 group_by(PlayerWeaponStat.weapon_cd).\
469                 having(func.count() > 4).\
470                 all():
471                     recent_weapons.append(weapon[0])
472
473         # recent games table, all data
474         recent_games = DBSession.query(PlayerGameStat, Game, Server, Map).\
475                 filter(PlayerGameStat.player_id == player_id).\
476                 filter(PlayerGameStat.game_id == Game.game_id).\
477                 filter(Game.server_id == Server.server_id).\
478                 filter(Game.map_id == Map.map_id).\
479                 order_by(Game.game_id.desc())[0:10]
480
481     except Exception as e:
482         player = None
483         elos = None
484         elos_display = None
485         total_stats = None
486         recent_games = None
487         # DEPRECATED: included in total_stats, see above
488         #total_games = None
489         #games_breakdown = None
490         recent_weapons = []
491         fav_map = None
492         fav_weapon = None
493         fav_server = None
494         ranks = None
495         ranks_display = None;
496
497     return {'player':player,
498             'elos':elos_dict,
499             'elos_display':elos_display,
500             'recent_games':recent_games,
501             'total_stats':total_stats,
502             # DEPRECATED: included in total_stats, see above
503             #'total_games':total_games,
504             #'games_breakdown':games_breakdown,
505             'recent_weapons':recent_weapons,
506             'fav_map':fav_map,
507             'fav_weapon':fav_weapon,
508             'fav_server':fav_server,
509             'ranks':ranks_dict,
510             'ranks_display':ranks_display,
511             }
512
513
514 def player_info(request):
515     """
516     Provides detailed information on a specific player
517     """
518     return player_info_data(request)
519
520
521 def player_info_json(request):
522     """
523     Provides detailed information on a specific player. JSON.
524     """
525     return [{'status':'not implemented'}]
526
527
528 def player_game_index_data(request):
529     player_id = request.matchdict['player_id']
530
531     if request.params.has_key('page'):
532         current_page = request.params['page']
533     else:
534         current_page = 1
535
536     try:
537         games_q = DBSession.query(Game, Server, Map).\
538             filter(PlayerGameStat.game_id == Game.game_id).\
539             filter(PlayerGameStat.player_id == player_id).\
540             filter(Game.server_id == Server.server_id).\
541             filter(Game.map_id == Map.map_id).\
542             order_by(Game.game_id.desc())
543
544         games = Page(games_q, current_page, items_per_page=10, url=page_url)
545
546         pgstats = {}
547         for (game, server, map) in games:
548             pgstats[game.game_id] = DBSession.query(PlayerGameStat).\
549                     filter(PlayerGameStat.game_id == game.game_id).\
550                     order_by(PlayerGameStat.rank).\
551                     order_by(PlayerGameStat.score).all()
552
553     except Exception as e:
554         player = None
555         games = None
556
557     return {'player_id':player_id,
558             'games':games,
559             'pgstats':pgstats}
560
561
562 def player_game_index(request):
563     """
564     Provides an index of the games in which a particular
565     player was involved. This is ordered by game_id, with
566     the most recent game_ids first. Paginated.
567     """
568     return player_game_index_data(request)
569
570
571 def player_game_index_json(request):
572     """
573     Provides an index of the games in which a particular
574     player was involved. This is ordered by game_id, with
575     the most recent game_ids first. Paginated. JSON.
576     """
577     return [{'status':'not implemented'}]
578
579
580 def player_accuracy_data(request):
581     player_id = request.matchdict['id']
582     allowed_weapons = ['nex', 'rifle', 'shotgun', 'uzi', 'minstanex']
583     weapon_cd = 'nex'
584     games = 20
585
586     if request.params.has_key('weapon'):
587         if request.params['weapon'] in allowed_weapons:
588             weapon_cd = request.params['weapon']
589
590     if request.params.has_key('games'):
591         try:
592             games = request.params['games']
593
594             if games < 0:
595                 games = 20
596             if games > 50:
597                 games = 50
598         except:
599             games = 20
600
601     (avg, accs) = get_accuracy_stats(player_id, weapon_cd, games)
602
603     # if we don't have enough data for the given weapon
604     if len(accs) < games:
605         games = len(accs)
606
607     return {
608             'player_id':player_id, 
609             'player_url':request.route_url('player_info', id=player_id), 
610             'weapon':weapon_cd, 
611             'games':games, 
612             'avg':avg, 
613             'accs':accs
614             }
615
616
617 def player_accuracy(request):
618     """
619     Provides the accuracy for the given weapon. (JSON only)
620     """
621     return player_accuracy_data(request)
622
623
624 def player_accuracy_json(request):
625     """
626     Provides a JSON response representing the accuracy for the given weapon.
627
628     Parameters:
629        weapon = which weapon to display accuracy for. Valid values are 'nex',
630                 'shotgun', 'uzi', and 'minstanex'.
631        games = over how many games to display accuracy. Can be up to 50.
632     """
633     return player_accuracy_data(request)
634
635
636 def player_damage_data(request):
637     player_id = request.matchdict['id']
638     allowed_weapons = ['grenadelauncher', 'electro', 'crylink', 'hagar',
639             'rocketlauncher', 'laser']
640     weapon_cd = 'rocketlauncher'
641     games = 20
642
643     if request.params.has_key('weapon'):
644         if request.params['weapon'] in allowed_weapons:
645             weapon_cd = request.params['weapon']
646
647     if request.params.has_key('games'):
648         try:
649             games = request.params['games']
650
651             if games < 0:
652                 games = 20
653             if games > 50:
654                 games = 50
655         except:
656             games = 20
657
658     (avg, dmgs) = get_damage_stats(player_id, weapon_cd, games)
659
660     # if we don't have enough data for the given weapon
661     if len(dmgs) < games:
662         games = len(dmgs)
663
664     return {
665             'player_id':player_id, 
666             'player_url':request.route_url('player_info', id=player_id), 
667             'weapon':weapon_cd, 
668             'games':games, 
669             'avg':avg, 
670             'dmgs':dmgs
671             }
672
673
674 def player_damage_json(request):
675     """
676     Provides a JSON response representing the damage for the given weapon.
677
678     Parameters:
679        weapon = which weapon to display damage for. Valid values are
680          'grenadelauncher', 'electro', 'crylink', 'hagar', 'rocketlauncher',
681          'laser'.
682        games = over how many games to display damage. Can be up to 50.
683     """
684     return player_damage_data(request)