2 import sqlalchemy.sql.functions as func
3 import sqlalchemy.sql.expression as expr
4 from beaker.cache import cache_regions, cache_region
5 from collections import namedtuple
6 from datetime import datetime, timedelta
7 from pyramid.response import Response
8 from xonstat.models import *
9 from xonstat.util import *
10 from xonstat.views.helpers import RecentGame, recent_games_q
11 from webhelpers.paginate import Page
14 log = logging.getLogger(__name__)
17 @cache_region('hourly_term')
18 def get_summary_stats():
20 Gets the following aggregate or "summary" statistics about stats:
21 - the total number of players (total_players)
22 - the total number of servers (total_servers)
23 - the total number of games (total_games)
24 - the total number of dm games (dm_games)
25 - the total number of duel games (duel_games)
26 - the total number of ctf games (ctf_games)
28 It is worth noting that there is also a table built to house these
29 stats in case the query in this function becomes too long for the
30 one time it runs per hour. In that case there is a script in the
31 xonstatdb repo - update_summary_stats.sql - that can be used via
32 cron to update the data offline.
34 summary_stats = DBSession.query("total_players", "total_servers",
35 "total_games", "dm_games", "duel_games", "ctf_games").\
39 select game_type_cd, count(*) total_games
41 where game_type_cd in ('duel', 'dm', 'ctf')
45 select count(*) total_players
47 where active_ind = true
50 select count(*) total_servers
52 where active_ind = true
54 select tp.total_players, ts.total_servers, dm.total_games+
55 duel.total_games+ctf.total_games total_games,
56 dm.total_games dm_games, duel.total_games duel_games,
57 ctf.total_games ctf_games
58 from total_games dm, total_games duel, total_games ctf,
59 total_players tp, total_servers ts
60 where dm.game_type_cd = 'dm'
61 and ctf.game_type_cd = 'ctf'
62 and duel.game_type_cd = 'duel'
69 @cache_region('hourly_term')
70 def get_ranks(game_type_cd):
72 Gets a set number of the top-ranked people for the specified game_type_cd.
74 The game_type_cd parameter is the type to fetch. Currently limited to
75 duel, dm, ctf, and tdm.
77 # how many ranks we want to fetch
78 leaderboard_count = 10
80 # only a few game modes are actually ranked
81 if game_type_cd not in 'duel' 'dm' 'ctf' 'tdm':
84 ranks = DBSession.query(PlayerRank).\
85 filter(PlayerRank.game_type_cd==game_type_cd).\
86 order_by(PlayerRank.rank).\
87 limit(leaderboard_count).all()
92 def top_players_by_time_q(cutoff_days):
94 Query for the top players by the amount of time played during a date range.
96 Games older than cutoff_days days old are ignored.
99 # only games played during this range are considered
100 right_now = datetime.utcnow()
101 cutoff_dt = right_now - timedelta(days=cutoff_days)
103 top_players_q = DBSession.query(Player.player_id, Player.nick,
104 func.sum(PlayerGameStat.alivetime)).\
105 filter(Player.player_id == PlayerGameStat.player_id).\
106 filter(Player.player_id > 2).\
107 filter(expr.between(PlayerGameStat.create_dt, cutoff_dt, right_now)).\
108 order_by(expr.desc(func.sum(PlayerGameStat.alivetime))).\
109 group_by(Player.nick).\
110 group_by(Player.player_id)
115 @cache_region('hourly_term')
116 def get_top_players_by_time(cutoff_days):
118 The top players by the amount of time played during a date range.
120 Games older than cutoff_days days old are ignored.
122 # how many to retrieve
125 # only games played during this range are considered
126 right_now = datetime.utcnow()
127 cutoff_dt = right_now - timedelta(days=cutoff_days)
129 top_players_q = top_players_by_time_q(cutoff_days)
131 top_players = top_players_q.limit(count).all()
133 top_players = [(player_id, html_colors(nick), score) \
134 for (player_id, nick, score) in top_players]
139 def top_servers_by_players_q(cutoff_days):
141 Query to get the top servers by the amount of players active
144 Games older than cutoff_days days old are ignored.
146 # only games played during this range are considered
147 right_now = datetime.utcnow()
148 cutoff_dt = right_now - timedelta(days=cutoff_days)
150 top_servers_q = DBSession.query(Server.server_id, Server.name,
152 filter(Game.server_id==Server.server_id).\
153 filter(expr.between(Game.create_dt, cutoff_dt, right_now)).\
154 order_by(expr.desc(func.count(Game.game_id))).\
155 group_by(Server.server_id).\
156 group_by(Server.name)
161 @cache_region('hourly_term')
162 def get_top_servers_by_players(cutoff_days):
164 The top servers by the amount of players active during a date range.
166 Games older than cutoff_days days old are ignored.
168 # how many to retrieve
171 top_servers = top_servers_by_players_q(cutoff_days).limit(count).all()
176 def top_maps_by_times_played_q(cutoff_days):
178 Query to retrieve the top maps by the amount of times it was played
181 Games older than cutoff_days days old are ignored.
183 # only games played during this range are considered
184 right_now = datetime.utcnow()
185 cutoff_dt = right_now - timedelta(days=cutoff_days)
187 top_maps_q = DBSession.query(Game.map_id, Map.name,
189 filter(Map.map_id==Game.map_id).\
190 filter(expr.between(Game.create_dt, cutoff_dt, right_now)).\
191 order_by(expr.desc(func.count())).\
192 group_by(Game.map_id).\
198 @cache_region('hourly_term')
199 def get_top_maps_by_times_played(cutoff_days):
201 The top maps by the amount of times it was played during a date range.
203 Games older than cutoff_days days old are ignored.
205 # how many to retrieve
208 top_maps = top_maps_by_times_played_q(cutoff_days).limit(count).all()
213 def _main_index_data(request):
215 leaderboard_lifetime = int(
216 request.registry.settings['xonstat.leaderboard_lifetime'])
218 leaderboard_lifetime = 30
220 leaderboard_count = 10
221 recent_games_count = 20
223 # summary statistics for the tagline
225 summary_stats = get_summary_stats()
229 # the three top ranks tables
231 for gtc in ['duel', 'ctf', 'dm', 'tdm']:
232 rank = get_ranks(gtc)
236 right_now = datetime.utcnow()
237 back_then = datetime.utcnow() - timedelta(days=leaderboard_lifetime)
239 # top players by playing time
240 top_players = get_top_players_by_time(leaderboard_lifetime)
242 # top servers by number of total players played
243 top_servers = get_top_servers_by_players(leaderboard_lifetime)
245 # top maps by total times played
246 top_maps = get_top_maps_by_times_played(leaderboard_lifetime)
248 # recent games played in descending order
249 rgs = recent_games_q(cutoff=back_then).limit(recent_games_count).all()
250 recent_games = [RecentGame(row) for row in rgs]
252 return {'top_players':top_players,
253 'top_servers':top_servers,
255 'recent_games':recent_games,
257 'summary_stats':summary_stats,
261 def main_index(request):
263 Display the main page information.
265 mainindex_data = _main_index_data(request)
267 # FIXME: code clone, should get these from _main_index_data
268 leaderboard_count = 10
269 recent_games_count = 20
271 for i in range(leaderboard_count-len(mainindex_data['top_players'])):
272 mainindex_data['top_players'].append(('-', '-', '-'))
274 for i in range(leaderboard_count-len(mainindex_data['top_servers'])):
275 mainindex_data['top_servers'].append(('-', '-', '-'))
277 for i in range(leaderboard_count-len(mainindex_data['top_maps'])):
278 mainindex_data['top_maps'].append(('-', '-', '-'))
280 return mainindex_data
283 def main_index_json(request):
285 JSON output of the main page information.
287 return [{'status':'not implemented'}]
290 def top_players_by_time(request):
291 current_page = request.params.get('page', 1)
293 cutoff_days = int(request.registry.settings.\
294 get('xonstat.leaderboard_lifetime', 30))
296 top_players_q = top_players_by_time_q(cutoff_days)
298 top_players = Page(top_players_q, current_page, items_per_page=25, url=page_url)
300 top_players.items = [(player_id, html_colors(nick), score) \
301 for (player_id, nick, score) in top_players.items]
303 return {'top_players':top_players}
306 def top_servers_by_players(request):
307 current_page = request.params.get('page', 1)
309 cutoff_days = int(request.registry.settings.\
310 get('xonstat.leaderboard_lifetime', 30))
312 top_servers_q = top_servers_by_players_q(cutoff_days)
314 top_servers = Page(top_servers_q, current_page, items_per_page=25, url=page_url)
316 return {'top_servers':top_servers}
319 def top_maps_by_times_played(request):
320 current_page = request.params.get('page', 1)
322 cutoff_days = int(request.registry.settings.\
323 get('xonstat.leaderboard_lifetime', 30))
325 top_maps_q = top_maps_by_times_played_q(cutoff_days)
327 top_maps = Page(top_maps_q, current_page, items_per_page=25, url=page_url)
329 return {'top_maps':top_maps}