5 import sqlalchemy as sa
\r
6 import sqlalchemy.sql.functions as func
\r
8 from pyramid.response import Response
\r
9 from pyramid.url import current_route_url
\r
10 from sqlalchemy import desc, distinct
\r
11 from webhelpers.paginate import Page, PageURL
\r
12 from xonstat.models import *
\r
13 from xonstat.util import page_url
\r
15 log = logging.getLogger(__name__)
\r
18 def _player_index_data(request):
\r
19 if request.params.has_key('page'):
\r
20 current_page = request.params['page']
\r
25 player_q = DBSession.query(Player).\
\r
26 filter(Player.player_id > 2).\
\r
27 filter(Player.active_ind == True).\
\r
28 filter(sa.not_(Player.nick.like('Anonymous Player%'))).\
\r
29 order_by(Player.player_id.desc())
\r
31 players = Page(player_q, current_page, items_per_page=10, url=page_url)
\r
33 except Exception as e:
\r
37 return {'players':players
\r
41 def player_index(request):
\r
43 Provides a list of all the current players.
\r
45 return _player_index_data(request)
\r
48 def _get_games_played(player_id):
\r
50 Provides a breakdown by gametype of the games played by player_id.
\r
52 Returns a tuple containing (total_games, games_breakdown), where
\r
53 total_games is the absolute number of games played by player_id
\r
54 and games_breakdown is an array containing (game_type_cd, # games)
\r
56 games_played = DBSession.query(Game.game_type_cd, func.count()).\
\r
57 filter(Game.game_id == PlayerGameStat.game_id).\
\r
58 filter(PlayerGameStat.player_id == player_id).\
\r
59 group_by(Game.game_type_cd).\
\r
60 order_by(func.count().desc()).all()
\r
63 for (game_type_cd, games) in games_played:
\r
66 return (total, games_played)
\r
69 # TODO: should probably factor the above function into this one such that
\r
70 # total_stats['ctf_games'] is the count of CTF games and so on...
\r
71 def _get_total_stats(player_id):
\r
73 Provides aggregated stats by player_id.
\r
75 Returns a dict with the keys 'kills', 'deaths', 'alivetime'.
\r
77 kills = how many kills a player has over all games
\r
78 deaths = how many deaths a player has over all games
\r
79 alivetime = how long a player has played over all games
\r
81 If any of the above are None, they are set to 0.
\r
84 (total_stats['kills'], total_stats['deaths'], total_stats['alivetime']) = DBSession.\
\r
85 query("total_kills", "total_deaths", "total_alivetime").\
\r
87 "select sum(kills) total_kills, "
\r
88 "sum(deaths) total_deaths, "
\r
89 "sum(alivetime) total_alivetime "
\r
90 "from player_game_stats "
\r
91 "where player_id=:player_id"
\r
92 ).params(player_id=player_id).one()
\r
94 (total_stats['wins'],) = DBSession.\
\r
95 query("total_wins").\
\r
97 "select count(*) total_wins "
\r
98 "from games g, player_game_stats pgs "
\r
99 "where g.game_id = pgs.game_id "
\r
100 "and player_id=:player_id "
\r
101 "and (g.winner = pgs.team or pgs.rank = 1)"
\r
102 ).params(player_id=player_id).one()
\r
104 for (key,value) in total_stats.items():
\r
106 total_stats[key] = 0
\r
111 def get_accuracy_stats(player_id, weapon_cd, games):
\r
113 Provides accuracy for weapon_cd by player_id for the past N games.
\r
115 # Reaching back 90 days should give us an accurate enough average
\r
116 # We then multiply this out for the number of data points (games) to
\r
117 # create parameters for a flot graph
\r
119 raw_avg = DBSession.query(func.sum(PlayerWeaponStat.hit),
\r
120 func.sum(PlayerWeaponStat.fired)).\
\r
121 filter(PlayerWeaponStat.player_id == player_id).\
\r
122 filter(PlayerWeaponStat.weapon_cd == weapon_cd).\
\r
125 avg = round(float(raw_avg[0])/raw_avg[1]*100, 2)
\r
127 # Determine the raw accuracy (hit, fired) numbers for $games games
\r
128 # This is then enumerated to create parameters for a flot graph
\r
129 raw_accs = DBSession.query(PlayerWeaponStat.game_id,
\r
130 PlayerWeaponStat.hit, PlayerWeaponStat.fired).\
\r
131 filter(PlayerWeaponStat.player_id == player_id).\
\r
132 filter(PlayerWeaponStat.weapon_cd == weapon_cd).\
\r
133 order_by(PlayerWeaponStat.game_id.desc()).\
\r
137 # they come out in opposite order, so flip them in the right direction
\r
141 for i in range(len(raw_accs)):
\r
142 accs.append((raw_accs[i][0], round(float(raw_accs[i][1])/raw_accs[i][2]*100, 2)))
\r
150 def get_damage_stats(player_id, weapon_cd, games):
\r
152 Provides damage info for weapon_cd by player_id for the past N games.
\r
155 raw_avg = DBSession.query(func.sum(PlayerWeaponStat.actual),
\r
156 func.sum(PlayerWeaponStat.hit)).\
\r
157 filter(PlayerWeaponStat.player_id == player_id).\
\r
158 filter(PlayerWeaponStat.weapon_cd == weapon_cd).\
\r
161 avg = round(float(raw_avg[0])/raw_avg[1], 2)
\r
163 # Determine the damage efficiency (hit, fired) numbers for $games games
\r
164 # This is then enumerated to create parameters for a flot graph
\r
165 raw_dmgs = DBSession.query(PlayerWeaponStat.game_id,
\r
166 PlayerWeaponStat.actual, PlayerWeaponStat.hit).\
\r
167 filter(PlayerWeaponStat.player_id == player_id).\
\r
168 filter(PlayerWeaponStat.weapon_cd == weapon_cd).\
\r
169 order_by(PlayerWeaponStat.game_id.desc()).\
\r
173 # they come out in opposite order, so flip them in the right direction
\r
177 for i in range(len(raw_dmgs)):
\r
178 # try to derive, unless we've hit nothing then set to 0!
\r
180 dmg = round(float(raw_dmgs[i][1])/raw_dmgs[i][2], 2)
\r
184 dmgs.append((raw_dmgs[i][0], dmg))
\r
185 except Exception as e:
\r
192 def _player_info_data(request):
\r
193 player_id = int(request.matchdict['id'])
\r
198 player = DBSession.query(Player).filter_by(player_id=player_id).\
\r
199 filter(Player.active_ind == True).one()
\r
201 # games played, alivetime, wins, kills, deaths
\r
202 total_stats = _get_total_stats(player.player_id)
\r
204 # games breakdown - N games played (X ctf, Y dm) etc
\r
205 (total_games, games_breakdown) = _get_games_played(player.player_id)
\r
208 # friendly display of elo information and preliminary status
\r
209 elos = DBSession.query(PlayerElo).filter_by(player_id=player_id).\
\r
210 filter(PlayerElo.game_type_cd.in_(['ctf','duel','dm'])).\
\r
211 order_by(PlayerElo.elo.desc()).all()
\r
220 elos_display.append(str.format(round(elo.elo, 3),
\r
223 # which weapons have been used in the past 90 days
\r
224 # and also, used in 5 games or more?
\r
225 back_then = datetime.datetime.utcnow() - datetime.timedelta(days=90)
\r
226 recent_weapons = []
\r
227 for weapon in DBSession.query(PlayerWeaponStat.weapon_cd, func.count()).\
\r
228 filter(PlayerWeaponStat.player_id == player_id).\
\r
229 filter(PlayerWeaponStat.create_dt > back_then).\
\r
230 group_by(PlayerWeaponStat.weapon_cd).\
\r
231 having(func.count() > 4).\
\r
233 recent_weapons.append(weapon[0])
\r
235 # recent games table, all data
\r
236 recent_games = DBSession.query(PlayerGameStat, Game, Server, Map).\
\r
237 filter(PlayerGameStat.player_id == player_id).\
\r
238 filter(PlayerGameStat.game_id == Game.game_id).\
\r
239 filter(Game.server_id == Server.server_id).\
\r
240 filter(Game.map_id == Map.map_id).\
\r
241 order_by(Game.game_id.desc())[0:10]
\r
243 except Exception as e:
\r
245 elos_display = None
\r
247 recent_games = None
\r
249 games_breakdown = None
\r
250 recent_weapons = []
\r
252 return {'player':player,
\r
253 'elos_display':elos_display,
\r
254 'recent_games':recent_games,
\r
255 'total_stats':total_stats,
\r
256 'total_games':total_games,
\r
257 'games_breakdown':games_breakdown,
\r
258 'recent_weapons':recent_weapons,
\r
262 def player_info(request):
\r
264 Provides detailed information on a specific player
\r
266 return _player_info_data(request)
\r
269 def _player_game_index_data(request):
\r
270 player_id = request.matchdict['player_id']
\r
272 if request.params.has_key('page'):
\r
273 current_page = request.params['page']
\r
278 games_q = DBSession.query(Game, Server, Map).\
\r
279 filter(PlayerGameStat.game_id == Game.game_id).\
\r
280 filter(PlayerGameStat.player_id == player_id).\
\r
281 filter(Game.server_id == Server.server_id).\
\r
282 filter(Game.map_id == Map.map_id).\
\r
283 order_by(Game.game_id.desc())
\r
285 games = Page(games_q, current_page, items_per_page=10, url=page_url)
\r
288 for (game, server, map) in games:
\r
289 pgstats[game.game_id] = DBSession.query(PlayerGameStat).\
\r
290 filter(PlayerGameStat.game_id == game.game_id).\
\r
291 order_by(PlayerGameStat.rank).\
\r
292 order_by(PlayerGameStat.score).all()
\r
294 except Exception as e:
\r
298 return {'player_id':player_id,
\r
303 def player_game_index(request):
\r
305 Provides an index of the games in which a particular
\r
306 player was involved. This is ordered by game_id, with
\r
307 the most recent game_ids first. Paginated.
\r
309 return _player_game_index_data(request)
\r
312 def _player_accuracy_data(request):
\r
313 player_id = request.matchdict['id']
\r
314 allowed_weapons = ['nex', 'rifle', 'shotgun', 'uzi', 'minstanex']
\r
318 if request.params.has_key('weapon'):
\r
319 if request.params['weapon'] in allowed_weapons:
\r
320 weapon_cd = request.params['weapon']
\r
322 if request.params.has_key('games'):
\r
324 games = request.params['games']
\r
333 (avg, accs) = get_accuracy_stats(player_id, weapon_cd, games)
\r
335 # if we don't have enough data for the given weapon
\r
336 if len(accs) < games:
\r
340 'player_id':player_id,
\r
341 'player_url':request.route_url('player_info', id=player_id),
\r
342 'weapon':weapon_cd,
\r
349 def player_accuracy_json(request):
\r
351 Provides a JSON response representing the accuracy for the given weapon.
\r
354 weapon = which weapon to display accuracy for. Valid values are 'nex',
\r
355 'shotgun', 'uzi', and 'minstanex'.
\r
356 games = over how many games to display accuracy. Can be up to 50.
\r
358 return _player_accuracy_data(request)
\r
361 def _player_damage_data(request):
\r
362 player_id = request.matchdict['id']
\r
363 allowed_weapons = ['grenadelauncher', 'electro', 'crylink', 'hagar',
\r
364 'rocketlauncher', 'laser']
\r
365 weapon_cd = 'laser'
\r
368 if request.params.has_key('weapon'):
\r
369 if request.params['weapon'] in allowed_weapons:
\r
370 weapon_cd = request.params['weapon']
\r
372 if request.params.has_key('games'):
\r
374 games = request.params['games']
\r
383 (avg, dmgs) = get_damage_stats(player_id, weapon_cd, games)
\r
385 # if we don't have enough data for the given weapon
\r
386 if len(dmgs) < games:
\r
390 'player_id':player_id,
\r
391 'player_url':request.route_url('player_info', id=player_id),
\r
392 'weapon':weapon_cd,
\r
399 def player_damage_json(request):
\r
401 Provides a JSON response representing the damage for the given weapon.
\r
404 weapon = which weapon to display damage for. Valid values are
\r
405 'grenadelauncher', 'electro', 'crylink', 'hagar', 'rocketlauncher',
\r
407 games = over how many games to display damage. Can be up to 50.
\r
409 return _player_damage_data(request)
\r