5 import sqlalchemy as sa
6 import sqlalchemy.sql.functions as func
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, datetime_seconds
15 log = logging.getLogger(__name__)
18 def player_index_data(request):
19 if request.params.has_key('page'):
20 current_page = request.params['page']
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())
31 players = Page(player_q, current_page, items_per_page=10, url=page_url)
33 except Exception as e:
37 return {'players':players
41 def player_index(request):
43 Provides a list of all the current players.
45 return player_index_data(request)
48 def player_index_json(request):
50 Provides a list of all the current players. JSON.
52 return [{'status':'not implemented'}]
55 def _get_games_played(player_id):
57 DEPRECATED: Now included in _get_total_stats()
59 Provides a breakdown by gametype of the games played by player_id.
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)
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()
72 for (game_type_cd, games) in games_played:
75 return (total, games_played)
78 def _get_total_stats(player_id):
80 Provides aggregated stats by player_id.
82 Returns a dict with the keys 'kills', 'deaths', 'alivetime'.
84 games = how many games a player has played
85 games_breakdown = how many games of given type a player has played (dictionary)
86 games_alivetime = how many time a player has spent in a give game type (dictionary)
87 kills = how many kills a player has over all games
88 deaths = how many deaths a player has over all games
89 suicides = how many suicides a player has over all games
90 alivetime = how long a player has played over all games
91 alivetime_week = how long a player has played over all games in the last week
92 alivetime_month = how long a player has played over all games in the last month
93 wins = how many games a player has won
95 If any of the above are None, they are set to 0.
97 # 7 and 30 day windows
98 one_week_ago = datetime.datetime.utcnow() - datetime.timedelta(days=7)
99 one_month_ago = datetime.datetime.utcnow() - datetime.timedelta(days=30)
103 games_played = DBSession.query(
104 Game.game_type_cd, func.count(), func.sum(PlayerGameStat.alivetime)).\
105 filter(Game.game_id == PlayerGameStat.game_id).\
106 filter(PlayerGameStat.player_id == player_id).\
107 group_by(Game.game_type_cd).\
108 order_by(func.count().desc()).\
111 total_stats['games'] = 0
112 total_stats['games_breakdown'] = {} # this is a dictionary inside a dictionary .. dictception?
113 total_stats['games_alivetime'] = {}
114 for (game_type_cd, games, alivetime) in games_played:
115 total_stats['games'] += games
116 total_stats['games_breakdown'][game_type_cd] = games
117 total_stats['games_alivetime'][game_type_cd] = alivetime
119 # more fields can be added here, e.g. 'collects' for kh games
120 (total_stats['kills'], total_stats['deaths'], total_stats['suicides'],
121 total_stats['alivetime'],) = DBSession.query(
122 func.sum(PlayerGameStat.kills),
123 func.sum(PlayerGameStat.deaths),
124 func.sum(PlayerGameStat.suicides),
125 func.sum(PlayerGameStat.alivetime)).\
126 filter(PlayerGameStat.player_id == player_id).\
129 (total_stats['alivetime_week'],) = DBSession.query(
130 func.sum(PlayerGameStat.alivetime)).\
131 filter(PlayerGameStat.player_id == player_id).\
132 filter(PlayerGameStat.create_dt > one_week_ago).\
135 (total_stats['alivetime_month'],) = DBSession.query(
136 func.sum(PlayerGameStat.alivetime)).\
137 filter(PlayerGameStat.player_id == player_id).\
138 filter(PlayerGameStat.create_dt > one_month_ago).\
141 (total_stats['wins'],) = DBSession.\
142 query("total_wins").\
144 "select count(*) total_wins "
145 "from games g, player_game_stats pgs "
146 "where g.game_id = pgs.game_id "
147 "and player_id=:player_id "
148 "and (g.winner = pgs.team or pgs.rank = 1)"
149 ).params(player_id=player_id).one()
151 # (total_stats['wins'],) = DBSession.query(
153 # filter(Game.game_id == PlayerGameStat.game_id).\
154 # filter(PlayerGameStat.player_id == player_id).\
155 # filter(Game.winner == PlayerGameStat.team or PlayerGameStat.rank == 1).\
158 (total_stats['duel_wins'],) = DBSession.query(
160 filter(Game.game_id == PlayerGameStat.game_id).\
161 filter(Game.game_type_cd == "duel").\
162 filter(PlayerGameStat.player_id == player_id).\
163 filter(PlayerGameStat.rank == 1).\
166 (total_stats['duel_kills'], total_stats['duel_deaths'], total_stats['duel_suicides'],) = DBSession.query(
167 func.sum(PlayerGameStat.kills),
168 func.sum(PlayerGameStat.deaths),
169 func.sum(PlayerGameStat.suicides)).\
170 filter(Game.game_id == PlayerGameStat.game_id).\
171 filter(Game.game_type_cd == "duel").\
172 filter(PlayerGameStat.player_id == player_id).\
175 (total_stats['dm_wins'],) = DBSession.query(
177 filter(Game.game_id == PlayerGameStat.game_id).\
178 filter(Game.game_type_cd == "dm").\
179 filter(PlayerGameStat.player_id == player_id).\
180 filter(PlayerGameStat.rank == 1).\
183 (total_stats['dm_kills'], total_stats['dm_deaths'], total_stats['dm_suicides'],) = DBSession.query(
184 func.sum(PlayerGameStat.kills),
185 func.sum(PlayerGameStat.deaths),
186 func.sum(PlayerGameStat.suicides)).\
187 filter(Game.game_id == PlayerGameStat.game_id).\
188 filter(Game.game_type_cd == "dm").\
189 filter(PlayerGameStat.player_id == player_id).\
192 (total_stats['tdm_kills'], total_stats['tdm_deaths'], total_stats['tdm_suicides'],) = DBSession.query(
193 func.sum(PlayerGameStat.kills),
194 func.sum(PlayerGameStat.deaths),
195 func.sum(PlayerGameStat.suicides)).\
196 filter(Game.game_id == PlayerGameStat.game_id).\
197 filter(Game.game_type_cd == "tdm").\
198 filter(PlayerGameStat.player_id == player_id).\
201 (total_stats['tdm_wins'],) = DBSession.query(
203 filter(Game.game_id == PlayerGameStat.game_id).\
204 filter(Game.game_type_cd == "tdm").\
205 filter(PlayerGameStat.player_id == player_id).\
206 filter(PlayerGameStat.rank == 1).\
209 (total_stats['ctf_wins'],) = DBSession.query(
211 filter(Game.game_id == PlayerGameStat.game_id).\
212 filter(Game.game_type_cd == "ctf").\
213 filter(PlayerGameStat.player_id == player_id).\
214 filter(PlayerGameStat.rank == 1).\
217 (total_stats['ctf_caps'], total_stats['ctf_pickups'], total_stats['ctf_drops'],
218 total_stats['ctf_returns'], total_stats['ctf_fckills'],) = DBSession.query(
219 func.sum(PlayerGameStat.captures),
220 func.sum(PlayerGameStat.pickups),
221 func.sum(PlayerGameStat.drops),
222 func.sum(PlayerGameStat.returns),
223 func.sum(PlayerGameStat.carrier_frags)).\
224 filter(Game.game_id == PlayerGameStat.game_id).\
225 filter(Game.game_type_cd == "ctf").\
226 filter(PlayerGameStat.player_id == player_id).\
229 for (key,value) in total_stats.items():
236 def _get_fav_map(player_id):
238 Get the player's favorite map. The favorite map is defined
239 as the map that he or she has played the most in the past
242 Returns a dictionary with keys for the map's name and id.
245 back_then = datetime.datetime.utcnow() - datetime.timedelta(days=90)
247 raw_fav_map = DBSession.query(Map.name, Map.map_id).\
248 filter(Game.game_id == PlayerGameStat.game_id).\
249 filter(Game.map_id == Map.map_id).\
250 filter(PlayerGameStat.player_id == player_id).\
251 filter(PlayerGameStat.create_dt > back_then).\
252 group_by(Map.name, Map.map_id).\
253 order_by(func.count().desc()).\
257 for map_e in raw_fav_map:
259 entry['name'] = map_e[0]
260 entry['id'] = map_e[1]
261 fav_map.append(entry)
266 def _get_fav_weapon(player_id):
268 Get the player's favorite weapon. The favorite weapon is defined
269 as the weapon that he or she has employed the most in the past
272 Returns a sequence of dictionaries with keys for the weapon's name and id.
273 The sequence holds the most-used weapons in decreasing order.
276 back_then = datetime.datetime.utcnow() - datetime.timedelta(days=90)
278 raw_fav_weapon = DBSession.query(Weapon.descr, Weapon.weapon_cd).\
279 filter(Game.game_id == PlayerWeaponStat.game_id).\
280 filter(PlayerWeaponStat.player_id == player_id).\
281 filter(PlayerWeaponStat.weapon_cd == Weapon.weapon_cd).\
282 filter(PlayerWeaponStat.create_dt > back_then).\
283 group_by(Weapon.descr, Weapon.weapon_cd).\
284 order_by(func.count().desc()).\
288 for wpn_e in raw_fav_weapon:
290 entry['name'] = wpn_e[0]
291 entry['id'] = wpn_e[1]
292 fav_weapon.append(entry)
297 def _get_fav_server(player_id):
299 Get the player's favorite server. The favorite server is defined
300 as the server that he or she has played on the most in the past
303 Returns a sequence of dictionaries with keys for the server's name and id.
304 The sequence holds the most-used servers in decreasing order.
307 back_then = datetime.datetime.utcnow() - datetime.timedelta(days=90)
309 raw_fav_server = DBSession.query(Server.name, Server.server_id).\
310 filter(Game.game_id == PlayerGameStat.game_id).\
311 filter(Game.server_id == Server.server_id).\
312 filter(PlayerGameStat.player_id == player_id).\
313 filter(PlayerGameStat.create_dt > back_then).\
314 group_by(Server.name, Server.server_id).\
315 order_by(func.count().desc()).\
319 for srv_e in raw_fav_server:
321 entry['name'] = srv_e[0]
322 entry['id'] = srv_e[1]
323 fav_server.append(entry)
328 def _get_rank(player_id):
330 Get the player's rank as well as the total number of ranks.
332 rank = DBSession.query("game_type_cd", "rank", "max_rank").\
334 "select pr.game_type_cd, pr.rank, overall.max_rank "
335 "from player_ranks pr, "
336 "(select game_type_cd, max(rank) max_rank "
338 "group by game_type_cd) overall "
339 "where pr.game_type_cd = overall.game_type_cd "
340 "and player_id = :player_id "
342 params(player_id=player_id).all()
347 def get_accuracy_stats(player_id, weapon_cd, games):
349 Provides accuracy for weapon_cd by player_id for the past N games.
351 # Reaching back 90 days should give us an accurate enough average
352 # We then multiply this out for the number of data points (games) to
353 # create parameters for a flot graph
355 raw_avg = DBSession.query(func.sum(PlayerWeaponStat.hit),
356 func.sum(PlayerWeaponStat.fired)).\
357 filter(PlayerWeaponStat.player_id == player_id).\
358 filter(PlayerWeaponStat.weapon_cd == weapon_cd).\
361 avg = round(float(raw_avg[0])/raw_avg[1]*100, 2)
363 # Determine the raw accuracy (hit, fired) numbers for $games games
364 # This is then enumerated to create parameters for a flot graph
365 raw_accs = DBSession.query(PlayerWeaponStat.game_id,
366 PlayerWeaponStat.hit, PlayerWeaponStat.fired).\
367 filter(PlayerWeaponStat.player_id == player_id).\
368 filter(PlayerWeaponStat.weapon_cd == weapon_cd).\
369 order_by(PlayerWeaponStat.game_id.desc()).\
373 # they come out in opposite order, so flip them in the right direction
377 for i in range(len(raw_accs)):
378 accs.append((raw_accs[i][0], round(float(raw_accs[i][1])/raw_accs[i][2]*100, 2)))
386 def get_damage_stats(player_id, weapon_cd, games):
388 Provides damage info for weapon_cd by player_id for the past N games.
391 raw_avg = DBSession.query(func.sum(PlayerWeaponStat.actual),
392 func.sum(PlayerWeaponStat.hit)).\
393 filter(PlayerWeaponStat.player_id == player_id).\
394 filter(PlayerWeaponStat.weapon_cd == weapon_cd).\
397 avg = round(float(raw_avg[0])/raw_avg[1], 2)
399 # Determine the damage efficiency (hit, fired) numbers for $games games
400 # This is then enumerated to create parameters for a flot graph
401 raw_dmgs = DBSession.query(PlayerWeaponStat.game_id,
402 PlayerWeaponStat.actual, PlayerWeaponStat.hit).\
403 filter(PlayerWeaponStat.player_id == player_id).\
404 filter(PlayerWeaponStat.weapon_cd == weapon_cd).\
405 order_by(PlayerWeaponStat.game_id.desc()).\
409 # they come out in opposite order, so flip them in the right direction
413 for i in range(len(raw_dmgs)):
414 # try to derive, unless we've hit nothing then set to 0!
416 dmg = round(float(raw_dmgs[i][1])/raw_dmgs[i][2], 2)
420 dmgs.append((raw_dmgs[i][0], dmg))
421 except Exception as e:
428 def player_info_data(request):
429 player_id = int(request.matchdict['id'])
434 player = DBSession.query(Player).filter_by(player_id=player_id).\
435 filter(Player.active_ind == True).one()
437 # games played, alivetime, wins, kills, deaths
438 total_stats = _get_total_stats(player.player_id)
440 # games breakdown - N games played (X ctf, Y dm) etc
441 # DEPRECATED: included in total_stats, see above
442 # (total_games, games_breakdown) = _get_games_played(player.player_id)
444 # favorite map from the past 90 days
446 fav_map = _get_fav_map(player.player_id)
450 # favorite weapon from the past 90 days
452 fav_weapon = _get_fav_weapon(player.player_id)
456 # favorite server from the past 90 days
458 fav_server = _get_fav_server(player.player_id)
462 # friendly display of elo information and preliminary status
463 elos = DBSession.query(PlayerElo).filter_by(player_id=player_id).\
464 filter(PlayerElo.game_type_cd.in_(['ctf','duel','dm'])).\
465 order_by(PlayerElo.elo.desc()).all()
475 elos_display.append(str.format(round(elo.elo, 3),
477 elos_dict[elo.game_type_cd] = round(elo.elo, 3)
478 elos_display = ', '.join(elos_display)
480 # get current rank information
481 ranks = _get_rank(player_id)
485 for gtc,rank,max_rank in ranks:
486 ranks_display.append("{1} of {2} ({0})".format(gtc, rank, max_rank))
487 ranks_dict[gtc] = (rank, max_rank)
488 ranks_display = ', '.join(ranks_display)
491 # which weapons have been used in the past 90 days
492 # and also, used in 5 games or more?
493 back_then = datetime.datetime.utcnow() - datetime.timedelta(days=90)
495 for weapon in DBSession.query(PlayerWeaponStat.weapon_cd, func.count()).\
496 filter(PlayerWeaponStat.player_id == player_id).\
497 filter(PlayerWeaponStat.create_dt > back_then).\
498 group_by(PlayerWeaponStat.weapon_cd).\
499 having(func.count() > 4).\
501 recent_weapons.append(weapon[0])
503 # recent games table, all data
504 recent_games = DBSession.query(PlayerGameStat, Game, Server, Map).\
505 filter(PlayerGameStat.player_id == player_id).\
506 filter(PlayerGameStat.game_id == Game.game_id).\
507 filter(Game.server_id == Server.server_id).\
508 filter(Game.map_id == Map.map_id).\
509 order_by(Game.game_id.desc())[0:10]
511 except Exception as e:
517 # DEPRECATED: included in total_stats, see above
519 #games_breakdown = None
525 ranks_display = None;
527 return {'player':player,
529 'elos_display':elos_display,
530 'recent_games':recent_games,
531 'total_stats':total_stats,
532 # DEPRECATED: included in total_stats, see above
533 #'total_games':total_games,
534 #'games_breakdown':games_breakdown,
535 'recent_weapons':recent_weapons,
537 'fav_weapon':fav_weapon,
538 'fav_server':fav_server,
540 'ranks_display':ranks_display,
544 def player_info(request):
546 Provides detailed information on a specific player
548 return player_info_data(request)
551 def player_info_json(request):
553 Provides detailed information on a specific player. JSON.
555 player_info = player_info_data(request)
558 'id': player_info['player'].player_id,
559 'nick': player_info['player'].nick.encode('utf-8'),
560 'stripped_nick': player_info['player'].nick_strip_colors(),
561 'joined': player_info['player'].create_dt.isoformat(),
563 'elos': player_info['elos'],
564 'ranks': player_info['ranks'],
566 'games': player_info['total_stats']['games'],
567 'games_breakdown': player_info['total_stats']['games_breakdown'],
568 'alivetime': datetime_seconds(player_info['total_stats']['alivetime']),
569 'kills': player_info['total_stats']['kills'],
570 'deaths': player_info['total_stats']['deaths'],
571 'suicides': player_info['total_stats']['suicides'],
572 'wins': player_info['total_stats']['wins'],
573 # FIXME - current "wins" query is flawed!
574 #'losses': player_info['total_stats']['loses'],
578 'game_id': game.game_id,
579 'game_type': game.game_type_cd,
580 'server': server.name,
582 'team': gamestat.team,
583 'rank': gamestat.rank,
584 'win': ((gamestat.team != None and gamestat.team == game.winner)
585 or (gamestat.team == None and gamestat.rank == 1)),
586 'time': game.create_dt.isoformat(),
588 for (gamestat, game, server, map) in player_info['recent_games'][:5]
594 def player_game_index_data(request):
595 player_id = request.matchdict['player_id']
597 if request.params.has_key('page'):
598 current_page = request.params['page']
603 games_q = DBSession.query(Game, Server, Map).\
604 filter(PlayerGameStat.game_id == Game.game_id).\
605 filter(PlayerGameStat.player_id == player_id).\
606 filter(Game.server_id == Server.server_id).\
607 filter(Game.map_id == Map.map_id).\
608 order_by(Game.game_id.desc())
610 games = Page(games_q, current_page, items_per_page=10, url=page_url)
613 for (game, server, map) in games:
614 pgstats[game.game_id] = DBSession.query(PlayerGameStat).\
615 filter(PlayerGameStat.game_id == game.game_id).\
616 order_by(PlayerGameStat.rank).\
617 order_by(PlayerGameStat.score).all()
619 except Exception as e:
623 return {'player_id':player_id,
628 def player_game_index(request):
630 Provides an index of the games in which a particular
631 player was involved. This is ordered by game_id, with
632 the most recent game_ids first. Paginated.
634 return player_game_index_data(request)
637 def player_game_index_json(request):
639 Provides an index of the games in which a particular
640 player was involved. This is ordered by game_id, with
641 the most recent game_ids first. Paginated. JSON.
643 return [{'status':'not implemented'}]
646 def player_accuracy_data(request):
647 player_id = request.matchdict['id']
648 allowed_weapons = ['nex', 'rifle', 'shotgun', 'uzi', 'minstanex']
652 if request.params.has_key('weapon'):
653 if request.params['weapon'] in allowed_weapons:
654 weapon_cd = request.params['weapon']
656 if request.params.has_key('games'):
658 games = request.params['games']
667 (avg, accs) = get_accuracy_stats(player_id, weapon_cd, games)
669 # if we don't have enough data for the given weapon
670 if len(accs) < games:
674 'player_id':player_id,
675 'player_url':request.route_url('player_info', id=player_id),
683 def player_accuracy(request):
685 Provides the accuracy for the given weapon. (JSON only)
687 return player_accuracy_data(request)
690 def player_accuracy_json(request):
692 Provides a JSON response representing the accuracy for the given weapon.
695 weapon = which weapon to display accuracy for. Valid values are 'nex',
696 'shotgun', 'uzi', and 'minstanex'.
697 games = over how many games to display accuracy. Can be up to 50.
699 return player_accuracy_data(request)
702 def player_damage_data(request):
703 player_id = request.matchdict['id']
704 allowed_weapons = ['grenadelauncher', 'electro', 'crylink', 'hagar',
705 'rocketlauncher', 'laser']
706 weapon_cd = 'rocketlauncher'
709 if request.params.has_key('weapon'):
710 if request.params['weapon'] in allowed_weapons:
711 weapon_cd = request.params['weapon']
713 if request.params.has_key('games'):
715 games = request.params['games']
724 (avg, dmgs) = get_damage_stats(player_id, weapon_cd, games)
726 # if we don't have enough data for the given weapon
727 if len(dmgs) < games:
731 'player_id':player_id,
732 'player_url':request.route_url('player_info', id=player_id),
740 def player_damage_json(request):
742 Provides a JSON response representing the damage for the given weapon.
745 weapon = which weapon to display damage for. Valid values are
746 'grenadelauncher', 'electro', 'crylink', 'hagar', 'rocketlauncher',
748 games = over how many games to display damage. Can be up to 50.
750 return player_damage_data(request)