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
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 Provides a breakdown by gametype of the games played by player_id.
59 Returns a tuple containing (total_games, games_breakdown), where
60 total_games is the absolute number of games played by player_id
61 and games_breakdown is an array containing (game_type_cd, # games)
63 games_played = DBSession.query(Game.game_type_cd, func.count()).\
64 filter(Game.game_id == PlayerGameStat.game_id).\
65 filter(PlayerGameStat.player_id == player_id).\
66 group_by(Game.game_type_cd).\
67 order_by(func.count().desc()).all()
70 for (game_type_cd, games) in games_played:
73 return (total, games_played)
76 # TODO: should probably factor the above function into this one such that
77 # total_stats['ctf_games'] is the count of CTF games and so on...
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 kills = how many kills a player has over all games
85 deaths = how many deaths a player has over all games
86 alivetime = how long a player has played over all games
88 If any of the above are None, they are set to 0.
91 (total_stats['kills'], total_stats['deaths'], total_stats['alivetime']) = DBSession.\
92 query("total_kills", "total_deaths", "total_alivetime").\
94 "select sum(kills) total_kills, "
95 "sum(deaths) total_deaths, "
96 "sum(alivetime) total_alivetime "
97 "from player_game_stats "
98 "where player_id=:player_id"
99 ).params(player_id=player_id).one()
101 (total_stats['wins'],) = DBSession.\
102 query("total_wins").\
104 "select count(*) total_wins "
105 "from games g, player_game_stats pgs "
106 "where g.game_id = pgs.game_id "
107 "and player_id=:player_id "
108 "and (g.winner = pgs.team or pgs.rank = 1)"
109 ).params(player_id=player_id).one()
111 for (key,value) in total_stats.items():
118 def _get_fav_map(player_id):
120 Get the player's favorite map. The favorite map is defined
121 as the map that he or she has played the most in the past
124 Returns a dictionary with keys for the map's name and id.
127 back_then = datetime.datetime.utcnow() - datetime.timedelta(days=90)
129 raw_fav_map = DBSession.query(Map.name, Map.map_id).\
130 filter(Game.game_id == PlayerGameStat.game_id).\
131 filter(Game.map_id == Map.map_id).\
132 filter(PlayerGameStat.player_id == player_id).\
133 filter(PlayerGameStat.create_dt > back_then).\
134 group_by(Map.name, Map.map_id).\
135 order_by(func.count().desc()).\
139 fav_map['name'] = raw_fav_map[0]
140 fav_map['id'] = raw_fav_map[1]
145 def get_accuracy_stats(player_id, weapon_cd, games):
147 Provides accuracy for weapon_cd by player_id for the past N games.
149 # Reaching back 90 days should give us an accurate enough average
150 # We then multiply this out for the number of data points (games) to
151 # create parameters for a flot graph
153 raw_avg = DBSession.query(func.sum(PlayerWeaponStat.hit),
154 func.sum(PlayerWeaponStat.fired)).\
155 filter(PlayerWeaponStat.player_id == player_id).\
156 filter(PlayerWeaponStat.weapon_cd == weapon_cd).\
159 avg = round(float(raw_avg[0])/raw_avg[1]*100, 2)
161 # Determine the raw accuracy (hit, fired) numbers for $games games
162 # This is then enumerated to create parameters for a flot graph
163 raw_accs = DBSession.query(PlayerWeaponStat.game_id,
164 PlayerWeaponStat.hit, PlayerWeaponStat.fired).\
165 filter(PlayerWeaponStat.player_id == player_id).\
166 filter(PlayerWeaponStat.weapon_cd == weapon_cd).\
167 order_by(PlayerWeaponStat.game_id.desc()).\
171 # they come out in opposite order, so flip them in the right direction
175 for i in range(len(raw_accs)):
176 accs.append((raw_accs[i][0], round(float(raw_accs[i][1])/raw_accs[i][2]*100, 2)))
184 def get_damage_stats(player_id, weapon_cd, games):
186 Provides damage info for weapon_cd by player_id for the past N games.
189 raw_avg = DBSession.query(func.sum(PlayerWeaponStat.actual),
190 func.sum(PlayerWeaponStat.hit)).\
191 filter(PlayerWeaponStat.player_id == player_id).\
192 filter(PlayerWeaponStat.weapon_cd == weapon_cd).\
195 avg = round(float(raw_avg[0])/raw_avg[1], 2)
197 # Determine the damage efficiency (hit, fired) numbers for $games games
198 # This is then enumerated to create parameters for a flot graph
199 raw_dmgs = DBSession.query(PlayerWeaponStat.game_id,
200 PlayerWeaponStat.actual, PlayerWeaponStat.hit).\
201 filter(PlayerWeaponStat.player_id == player_id).\
202 filter(PlayerWeaponStat.weapon_cd == weapon_cd).\
203 order_by(PlayerWeaponStat.game_id.desc()).\
207 # they come out in opposite order, so flip them in the right direction
211 for i in range(len(raw_dmgs)):
212 # try to derive, unless we've hit nothing then set to 0!
214 dmg = round(float(raw_dmgs[i][1])/raw_dmgs[i][2], 2)
218 dmgs.append((raw_dmgs[i][0], dmg))
219 except Exception as e:
226 def _player_info_data(request):
227 player_id = int(request.matchdict['id'])
232 player = DBSession.query(Player).filter_by(player_id=player_id).\
233 filter(Player.active_ind == True).one()
235 # games played, alivetime, wins, kills, deaths
236 total_stats = _get_total_stats(player.player_id)
238 # games breakdown - N games played (X ctf, Y dm) etc
239 (total_games, games_breakdown) = _get_games_played(player.player_id)
241 # favorite map from the past 90 days
242 fav_map = _get_fav_map(player.player_id)
244 # friendly display of elo information and preliminary status
245 elos = DBSession.query(PlayerElo).filter_by(player_id=player_id).\
246 filter(PlayerElo.game_type_cd.in_(['ctf','duel','dm'])).\
247 order_by(PlayerElo.elo.desc()).all()
256 elos_display.append(str.format(round(elo.elo, 3),
259 # which weapons have been used in the past 90 days
260 # and also, used in 5 games or more?
261 back_then = datetime.datetime.utcnow() - datetime.timedelta(days=90)
263 for weapon in DBSession.query(PlayerWeaponStat.weapon_cd, func.count()).\
264 filter(PlayerWeaponStat.player_id == player_id).\
265 filter(PlayerWeaponStat.create_dt > back_then).\
266 group_by(PlayerWeaponStat.weapon_cd).\
267 having(func.count() > 4).\
269 recent_weapons.append(weapon[0])
271 # recent games table, all data
272 recent_games = DBSession.query(PlayerGameStat, Game, Server, Map).\
273 filter(PlayerGameStat.player_id == player_id).\
274 filter(PlayerGameStat.game_id == Game.game_id).\
275 filter(Game.server_id == Server.server_id).\
276 filter(Game.map_id == Map.map_id).\
277 order_by(Game.game_id.desc())[0:10]
279 except Exception as e:
285 games_breakdown = None
289 return {'player':player,
290 'elos_display':elos_display,
291 'recent_games':recent_games,
292 'total_stats':total_stats,
293 'total_games':total_games,
294 'games_breakdown':games_breakdown,
295 'recent_weapons':recent_weapons,
300 def player_info(request):
302 Provides detailed information on a specific player
304 return _player_info_data(request)
307 def player_info_json(request):
309 Provides detailed information on a specific player. JSON.
311 return [{'status':'not implemented'}]
314 def _player_game_index_data(request):
315 player_id = request.matchdict['player_id']
317 if request.params.has_key('page'):
318 current_page = request.params['page']
323 games_q = DBSession.query(Game, Server, Map).\
324 filter(PlayerGameStat.game_id == Game.game_id).\
325 filter(PlayerGameStat.player_id == player_id).\
326 filter(Game.server_id == Server.server_id).\
327 filter(Game.map_id == Map.map_id).\
328 order_by(Game.game_id.desc())
330 games = Page(games_q, current_page, items_per_page=10, url=page_url)
333 for (game, server, map) in games:
334 pgstats[game.game_id] = DBSession.query(PlayerGameStat).\
335 filter(PlayerGameStat.game_id == game.game_id).\
336 order_by(PlayerGameStat.rank).\
337 order_by(PlayerGameStat.score).all()
339 except Exception as e:
343 return {'player_id':player_id,
348 def player_game_index(request):
350 Provides an index of the games in which a particular
351 player was involved. This is ordered by game_id, with
352 the most recent game_ids first. Paginated.
354 return _player_game_index_data(request)
357 def player_game_index_json(request):
359 Provides an index of the games in which a particular
360 player was involved. This is ordered by game_id, with
361 the most recent game_ids first. Paginated. JSON.
363 return [{'status':'not implemented'}]
366 def _player_accuracy_data(request):
367 player_id = request.matchdict['id']
368 allowed_weapons = ['nex', 'rifle', 'shotgun', 'uzi', 'minstanex']
372 if request.params.has_key('weapon'):
373 if request.params['weapon'] in allowed_weapons:
374 weapon_cd = request.params['weapon']
376 if request.params.has_key('games'):
378 games = request.params['games']
387 (avg, accs) = get_accuracy_stats(player_id, weapon_cd, games)
389 # if we don't have enough data for the given weapon
390 if len(accs) < games:
394 'player_id':player_id,
395 'player_url':request.route_url('player_info', id=player_id),
403 def player_accuracy(request):
405 Provides the accuracy for the given weapon. (JSON only)
407 return _player_accuracy_data(request)
410 def player_accuracy_json(request):
412 Provides a JSON response representing the accuracy for the given weapon.
415 weapon = which weapon to display accuracy for. Valid values are 'nex',
416 'shotgun', 'uzi', and 'minstanex'.
417 games = over how many games to display accuracy. Can be up to 50.
419 return _player_accuracy_data(request)
422 def _player_damage_data(request):
423 player_id = request.matchdict['id']
424 allowed_weapons = ['grenadelauncher', 'electro', 'crylink', 'hagar',
425 'rocketlauncher', 'laser']
426 weapon_cd = 'rocketlauncher'
429 if request.params.has_key('weapon'):
430 if request.params['weapon'] in allowed_weapons:
431 weapon_cd = request.params['weapon']
433 if request.params.has_key('games'):
435 games = request.params['games']
444 (avg, dmgs) = get_damage_stats(player_id, weapon_cd, games)
446 # if we don't have enough data for the given weapon
447 if len(dmgs) < games:
451 'player_id':player_id,
452 'player_url':request.route_url('player_info', id=player_id),
460 def player_damage_json(request):
462 Provides a JSON response representing the damage for the given weapon.
465 weapon = which weapon to display damage for. Valid values are
466 'grenadelauncher', 'electro', 'crylink', 'hagar', 'rocketlauncher',
468 games = over how many games to display damage. Can be up to 50.
470 return _player_damage_data(request)