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_day_summary_stats():
72 Gets the following aggregate statistics about the past 24 hours:
73 - the number of active players (day_active_players)
74 - the number of games (day_games)
75 - the total number of dm games (day_dm_games)
76 - the total number of duel games (day_duel_games)
77 - the total number of ctf games (day_ctf_games)
80 day_stats = DBSession.query("day_active_players",
81 "day_games", "day_dm_games", "day_duel_games", "day_ctf_games").\
85 select game_type_cd, count(*) day_games
87 where game_type_cd in ('duel', 'dm', 'ctf')
88 and create_dt > now() - interval '1 day'
91 day_active_players as (
92 select count(distinct player_id) day_active_players
93 from player_game_stats
94 where create_dt > now() - interval '1 day'
96 select tap.day_active_players, dm.day_games+
97 duel.day_games+ctf.day_games day_games,
98 dm.day_games day_dm_games, duel.day_games day_duel_games,
99 ctf.day_games day_ctf_games
100 from day_games dm, day_games duel, day_games ctf,
101 day_active_players tap
102 where dm.game_type_cd = 'dm'
103 and ctf.game_type_cd = 'ctf'
104 and duel.game_type_cd = 'duel'
108 # don't show anything if we don't have any activity
109 if day_stats.day_active_players is None or \
110 day_stats.day_active_players == 0:
113 except Exception as e:
118 @cache_region('hourly_term')
119 def get_ranks(game_type_cd):
121 Gets a set number of the top-ranked people for the specified game_type_cd.
123 The game_type_cd parameter is the type to fetch. Currently limited to
124 duel, dm, ctf, and tdm.
126 # how many ranks we want to fetch
127 leaderboard_count = 10
129 # only a few game modes are actually ranked
130 if game_type_cd not in 'duel' 'dm' 'ctf' 'tdm':
133 ranks = DBSession.query(PlayerRank).\
134 filter(PlayerRank.game_type_cd==game_type_cd).\
135 order_by(PlayerRank.rank).\
136 limit(leaderboard_count).all()
141 def top_players_by_time_q(cutoff_days):
143 Query for the top players by the amount of time played during a date range.
145 Games older than cutoff_days days old are ignored.
148 # only games played during this range are considered
149 right_now = datetime.utcnow()
150 cutoff_dt = right_now - timedelta(days=cutoff_days)
152 top_players_q = DBSession.query(Player.player_id, Player.nick,
153 func.sum(PlayerGameStat.alivetime)).\
154 filter(Player.player_id == PlayerGameStat.player_id).\
155 filter(Player.player_id > 2).\
156 filter(expr.between(PlayerGameStat.create_dt, cutoff_dt, right_now)).\
157 order_by(expr.desc(func.sum(PlayerGameStat.alivetime))).\
158 group_by(Player.nick).\
159 group_by(Player.player_id)
164 @cache_region('hourly_term')
165 def get_top_players_by_time(cutoff_days):
167 The top players by the amount of time played during a date range.
169 Games older than cutoff_days days old are ignored.
171 # how many to retrieve
174 # only games played during this range are considered
175 right_now = datetime.utcnow()
176 cutoff_dt = right_now - timedelta(days=cutoff_days)
178 top_players_q = top_players_by_time_q(cutoff_days)
180 top_players = top_players_q.limit(count).all()
182 top_players = [(player_id, html_colors(nick), score) \
183 for (player_id, nick, score) in top_players]
188 def top_servers_by_players_q(cutoff_days):
190 Query to get the top servers by the amount of players active
193 Games older than cutoff_days days old are ignored.
195 # only games played during this range are considered
196 right_now = datetime.utcnow()
197 cutoff_dt = right_now - timedelta(days=cutoff_days)
199 top_servers_q = DBSession.query(Server.server_id, Server.name,
201 filter(Game.server_id==Server.server_id).\
202 filter(expr.between(Game.create_dt, cutoff_dt, right_now)).\
203 order_by(expr.desc(func.count(Game.game_id))).\
204 group_by(Server.server_id).\
205 group_by(Server.name)
210 @cache_region('hourly_term')
211 def get_top_servers_by_players(cutoff_days):
213 The top servers by the amount of players active during a date range.
215 Games older than cutoff_days days old are ignored.
217 # how many to retrieve
220 top_servers = top_servers_by_players_q(cutoff_days).limit(count).all()
225 def top_maps_by_times_played_q(cutoff_days):
227 Query to retrieve the top maps by the amount of times it was played
230 Games older than cutoff_days days old are ignored.
232 # only games played during this range are considered
233 right_now = datetime.utcnow()
234 cutoff_dt = right_now - timedelta(days=cutoff_days)
236 top_maps_q = DBSession.query(Game.map_id, Map.name,
238 filter(Map.map_id==Game.map_id).\
239 filter(expr.between(Game.create_dt, cutoff_dt, right_now)).\
240 order_by(expr.desc(func.count())).\
241 group_by(Game.map_id).\
247 @cache_region('hourly_term')
248 def get_top_maps_by_times_played(cutoff_days):
250 The top maps by the amount of times it was played during a date range.
252 Games older than cutoff_days days old are ignored.
254 # how many to retrieve
257 top_maps = top_maps_by_times_played_q(cutoff_days).limit(count).all()
262 def _main_index_data(request):
264 leaderboard_lifetime = int(
265 request.registry.settings['xonstat.leaderboard_lifetime'])
267 leaderboard_lifetime = 30
269 leaderboard_count = 10
270 recent_games_count = 20
272 # summary statistics for the tagline
274 summary_stats = get_summary_stats()
275 day_stats = get_day_summary_stats()
281 # the three top ranks tables
283 for gtc in ['duel', 'ctf', 'dm', 'tdm']:
284 rank = get_ranks(gtc)
288 right_now = datetime.utcnow()
289 back_then = datetime.utcnow() - timedelta(days=leaderboard_lifetime)
291 # top players by playing time
292 top_players = get_top_players_by_time(leaderboard_lifetime)
294 # top servers by number of total players played
295 top_servers = get_top_servers_by_players(leaderboard_lifetime)
297 # top maps by total times played
298 top_maps = get_top_maps_by_times_played(leaderboard_lifetime)
300 # recent games played in descending order
301 rgs = recent_games_q(cutoff=back_then).limit(recent_games_count).all()
302 recent_games = [RecentGame(row) for row in rgs]
304 return {'top_players':top_players,
305 'top_servers':top_servers,
307 'recent_games':recent_games,
309 'summary_stats':summary_stats,
310 'day_stats':day_stats,
314 def main_index(request):
316 Display the main page information.
318 mainindex_data = _main_index_data(request)
320 # FIXME: code clone, should get these from _main_index_data
321 leaderboard_count = 10
322 recent_games_count = 20
324 for i in range(leaderboard_count-len(mainindex_data['top_players'])):
325 mainindex_data['top_players'].append(('-', '-', '-'))
327 for i in range(leaderboard_count-len(mainindex_data['top_servers'])):
328 mainindex_data['top_servers'].append(('-', '-', '-'))
330 for i in range(leaderboard_count-len(mainindex_data['top_maps'])):
331 mainindex_data['top_maps'].append(('-', '-', '-'))
333 return mainindex_data
336 def main_index_json(request):
338 JSON output of the main page information.
340 return [{'status':'not implemented'}]
343 def top_players_by_time(request):
344 current_page = request.params.get('page', 1)
346 cutoff_days = int(request.registry.settings.\
347 get('xonstat.leaderboard_lifetime', 30))
349 top_players_q = top_players_by_time_q(cutoff_days)
351 top_players = Page(top_players_q, current_page, items_per_page=25, url=page_url)
353 top_players.items = [(player_id, html_colors(nick), score) \
354 for (player_id, nick, score) in top_players.items]
356 return {'top_players':top_players}
359 def top_servers_by_players(request):
360 current_page = request.params.get('page', 1)
362 cutoff_days = int(request.registry.settings.\
363 get('xonstat.leaderboard_lifetime', 30))
365 top_servers_q = top_servers_by_players_q(cutoff_days)
367 top_servers = Page(top_servers_q, current_page, items_per_page=25, url=page_url)
369 return {'top_servers':top_servers}
372 def top_maps_by_times_played(request):
373 current_page = request.params.get('page', 1)
375 cutoff_days = int(request.registry.settings.\
376 get('xonstat.leaderboard_lifetime', 30))
378 top_maps_q = top_maps_by_times_played_q(cutoff_days)
380 top_maps = Page(top_maps_q, current_page, items_per_page=25, url=page_url)
382 return {'top_maps':top_maps}