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
\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(request):
\r
20 Provides a list of all the current players.
\r
22 if 'page' in request.matchdict:
\r
23 current_page = int(request.matchdict['page'])
\r
28 player_q = DBSession.query(Player).\
\r
29 filter(Player.player_id > 2).\
\r
30 filter(Player.active_ind == True).\
\r
31 filter(sa.not_(Player.nick.like('Anonymous Player%'))).\
\r
32 order_by(Player.player_id.desc())
\r
34 players = Page(player_q, current_page, items_per_page=10, url=page_url)
\r
36 last_linked_page = current_page + 4
\r
37 if last_linked_page > players.last_page:
\r
38 last_linked_page = players.last_page
\r
40 pages_to_link = range(current_page+1, last_linked_page+1)
\r
42 except Exception as e:
\r
46 return {'players':players,
\r
47 'pages_to_link':pages_to_link,
\r
51 def get_games_played(player_id):
\r
53 Provides a breakdown by gametype of the games played by player_id.
\r
55 Returns a tuple containing (total_games, games_breakdown), where
\r
56 total_games is the absolute number of games played by player_id
\r
57 and games_breakdown is an array containing (game_type_cd, # games)
\r
59 games_played = DBSession.query(Game.game_type_cd, func.count()).\
\r
60 filter(Game.game_id == PlayerGameStat.game_id).\
\r
61 filter(PlayerGameStat.player_id == player_id).\
\r
62 group_by(Game.game_type_cd).\
\r
63 order_by(func.count().desc()).all()
\r
66 for (game_type_cd, games) in games_played:
\r
69 return (total, games_played)
\r
72 # TODO: should probably factor the above function into this one such that
\r
73 # total_stats['ctf_games'] is the count of CTF games and so on...
\r
74 def get_total_stats(player_id):
\r
76 Provides aggregated stats by player_id.
\r
78 Returns a dict with the keys 'kills', 'deaths', 'alivetime'.
\r
80 kills = how many kills a player has over all games
\r
81 deaths = how many deaths a player has over all games
\r
82 alivetime = how long a player has played over all games
\r
84 If any of the above are None, they are set to 0.
\r
87 (total_stats['kills'], total_stats['deaths'], total_stats['alivetime']) = DBSession.\
\r
88 query("total_kills", "total_deaths", "total_alivetime").\
\r
90 "select sum(kills) total_kills, "
\r
91 "sum(deaths) total_deaths, "
\r
92 "sum(alivetime) total_alivetime "
\r
93 "from player_game_stats "
\r
94 "where player_id=:player_id"
\r
95 ).params(player_id=player_id).one()
\r
97 (total_stats['wins'],) = DBSession.\
\r
98 query("total_wins").\
\r
100 "select count(*) total_wins "
\r
101 "from games g, player_game_stats pgs "
\r
102 "where g.game_id = pgs.game_id "
\r
103 "and player_id=:player_id "
\r
104 "and (g.winner = pgs.team or pgs.rank = 1)"
\r
105 ).params(player_id=player_id).one()
\r
107 for (key,value) in total_stats.items():
\r
109 total_stats[key] = 0
\r
114 def get_accuracy_stats(player_id, weapon_cd, games):
\r
116 Provides accuracy for weapon_cd by player_id for the past N games.
\r
118 # Reaching back 90 days should give us an accurate enough average
\r
119 # We then multiply this out for the number of data points (games) to
\r
120 # create parameters for a flot graph
\r
122 raw_avg = DBSession.query(func.sum(PlayerWeaponStat.hit),
\r
123 func.sum(PlayerWeaponStat.fired)).\
\r
124 filter(PlayerWeaponStat.player_id == player_id).\
\r
125 filter(PlayerWeaponStat.weapon_cd == weapon_cd).\
\r
128 raw_avg = round(float(raw_avg[0])/raw_avg[1]*100, 2)
\r
131 for i in range(games):
\r
132 avg.append((i, raw_avg))
\r
134 # Determine the raw accuracy (hit, fired) numbers for $games games
\r
135 # This is then enumerated to create parameters for a flot graph
\r
136 raw_accs = DBSession.query(PlayerWeaponStat.hit, PlayerWeaponStat.fired).\
\r
137 filter(PlayerWeaponStat.player_id == player_id).\
\r
138 filter(PlayerWeaponStat.weapon_cd == weapon_cd).\
\r
139 order_by(PlayerWeaponStat.create_dt.desc()).\
\r
144 for i in range(len(raw_accs)):
\r
145 accs.append((i, round(float(raw_accs[i][0])/raw_accs[i][1]*100, 2)))
\r
153 def player_info(request):
\r
155 Provides detailed information on a specific player
\r
157 player_id = int(request.matchdict['id'])
\r
162 player = DBSession.query(Player).filter_by(player_id=player_id).\
\r
163 filter(Player.active_ind == True).one()
\r
165 # games played, alivetime, wins, kills, deaths
\r
166 total_stats = get_total_stats(player.player_id)
\r
168 # games breakdown - N games played (X ctf, Y dm) etc
\r
169 (total_games, games_breakdown) = get_games_played(player.player_id)
\r
172 # friendly display of elo information and preliminary status
\r
173 elos = DBSession.query(PlayerElo).filter_by(player_id=player_id).\
\r
174 filter(PlayerElo.game_type_cd.in_(['ctf','duel','dm'])).\
\r
175 order_by(PlayerElo.elo.desc()).all()
\r
184 elos_display.append(str.format(round(elo.elo, 3),
\r
187 # data for the accuracy graph, which is converted into a JSON array for
\r
189 (avg, accs) = get_accuracy_stats(player_id, 'nex', 20)
\r
191 avg = json.dumps(avg)
\r
192 accs = json.dumps(accs)
\r
195 # recent games table, all data
\r
196 recent_games = DBSession.query(PlayerGameStat, Game, Server, Map).\
\r
197 filter(PlayerGameStat.player_id == player_id).\
\r
198 filter(PlayerGameStat.game_id == Game.game_id).\
\r
199 filter(Game.server_id == Server.server_id).\
\r
200 filter(Game.map_id == Map.map_id).\
\r
201 order_by(Game.game_id.desc())[0:10]
\r
203 except Exception as e:
\r
205 elos_display = None
\r
207 recent_games = None
\r
209 games_breakdown = None
\r
214 return {'player':player,
\r
215 'elos_display':elos_display,
\r
216 'recent_games':recent_games,
\r
217 'total_stats':total_stats,
\r
218 'total_games':total_games,
\r
219 'games_breakdown':games_breakdown,
\r
225 def player_game_index(request):
\r
227 Provides an index of the games in which a particular
\r
228 player was involved. This is ordered by game_id, with
\r
229 the most recent game_ids first. Paginated.
\r
231 player_id = request.matchdict['player_id']
\r
233 if 'page' in request.matchdict:
\r
234 current_page = request.matchdict['page']
\r
239 games_q = DBSession.query(Game, Server, Map).\
\r
240 filter(PlayerGameStat.game_id == Game.game_id).\
\r
241 filter(PlayerGameStat.player_id == player_id).\
\r
242 filter(Game.server_id == Server.server_id).\
\r
243 filter(Game.map_id == Map.map_id).\
\r
244 order_by(Game.game_id.desc())
\r
246 games = Page(games_q, current_page, url=page_url)
\r
249 for (game, server, map) in games:
\r
250 pgstats[game.game_id] = DBSession.query(PlayerGameStat).\
\r
251 filter(PlayerGameStat.game_id == game.game_id).\
\r
252 order_by(PlayerGameStat.rank).\
\r
253 order_by(PlayerGameStat.score).all()
\r
255 except Exception as e:
\r
259 return {'player_id':player_id,
\r