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 @cache_region('hourly_term')
140 def top_servers_by_players(cutoff_days):
142 The top servers by the amount of players active during a date range.
144 Games older than cutoff_days days old are ignored.
146 # how many to retrieve
149 # only games played during this range are considered
150 right_now = datetime.utcnow()
151 cutoff_dt = right_now - timedelta(days=cutoff_days)
153 top_servers = DBSession.query(Server.server_id, Server.name,
155 filter(Game.server_id==Server.server_id).\
156 filter(expr.between(Game.create_dt, cutoff_dt, right_now)).\
157 order_by(expr.desc(func.count(Game.game_id))).\
158 group_by(Server.server_id).\
159 group_by(Server.name).limit(count).all()
164 @cache_region('hourly_term')
165 def top_maps_by_times_played(cutoff_days):
167 The top maps by the amount of times it was 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_maps = DBSession.query(Game.map_id, Map.name,
180 filter(Map.map_id==Game.map_id).\
181 filter(expr.between(Game.create_dt, cutoff_dt, right_now)).\
182 order_by(expr.desc(func.count())).\
183 group_by(Game.map_id).\
184 group_by(Map.name).limit(count).all()
189 def _main_index_data(request):
191 leaderboard_lifetime = int(
192 request.registry.settings['xonstat.leaderboard_lifetime'])
194 leaderboard_lifetime = 30
196 leaderboard_count = 10
197 recent_games_count = 20
199 # summary statistics for the tagline
201 summary_stats = get_summary_stats()
205 # the three top ranks tables
207 for gtc in ['duel', 'ctf', 'dm', 'tdm']:
208 rank = get_ranks(gtc)
212 right_now = datetime.utcnow()
213 back_then = datetime.utcnow() - timedelta(days=leaderboard_lifetime)
215 # top players by playing time
216 top_players = get_top_players_by_time(leaderboard_lifetime)
218 # top servers by number of total players played
219 top_servers = top_servers_by_players(leaderboard_lifetime)
221 # top maps by total times played
222 top_maps = top_maps_by_times_played(leaderboard_lifetime)
224 # recent games played in descending order
225 rgs = recent_games_q(cutoff=back_then).limit(recent_games_count).all()
226 recent_games = [RecentGame(row) for row in rgs]
228 return {'top_players':top_players,
229 'top_servers':top_servers,
231 'recent_games':recent_games,
233 'summary_stats':summary_stats,
237 def main_index(request):
239 Display the main page information.
241 mainindex_data = _main_index_data(request)
243 # FIXME: code clone, should get these from _main_index_data
244 leaderboard_count = 10
245 recent_games_count = 20
247 for i in range(leaderboard_count-len(mainindex_data['top_players'])):
248 mainindex_data['top_players'].append(('-', '-', '-'))
250 for i in range(leaderboard_count-len(mainindex_data['top_servers'])):
251 mainindex_data['top_servers'].append(('-', '-', '-'))
253 for i in range(leaderboard_count-len(mainindex_data['top_maps'])):
254 mainindex_data['top_maps'].append(('-', '-', '-'))
256 return mainindex_data
259 def main_index_json(request):
261 JSON output of the main page information.
263 return [{'status':'not implemented'}]
266 def top_players_by_time(request):
267 current_page = request.params.get('page', 1)
269 cutoff_days = int(request.registry.settings.\
270 get('xonstat.leaderboard_lifetime', 30))
272 top_players_q = top_players_by_time_q(cutoff_days)
274 top_players = Page(top_players_q, current_page, items_per_page=25, url=page_url)
276 top_players.items = [(player_id, html_colors(nick), score) \
277 for (player_id, nick, score) in top_players.items]
279 return {'top_players':top_players}