2 import sqlalchemy as sa
3 import sqlalchemy.sql.functions as func
4 import sqlalchemy.sql.expression as expr
5 from beaker.cache import cache_regions, cache_region
6 from collections import namedtuple
7 from datetime import datetime, timedelta
8 from pyramid.response import Response
9 from xonstat.models import *
10 from xonstat.util import *
11 from xonstat.views.helpers import RecentGame, recent_games_q
12 from webhelpers.paginate import Page
15 log = logging.getLogger(__name__)
18 @cache_region('hourly_term')
19 def get_summary_stats(request, cutoff_days=None):
21 Gets the following aggregate statistics about the past cutoff_days days:
22 - the number of active players
23 - the number of games per game type
24 If cutoff_days is None, the above stats are calculated for all time.
26 This information is then summarized into a string which is passed
27 directly to the template.
30 if cutoff_days is not None:
31 # only games played during this range are considered
32 right_now = datetime.now()
33 cutoff_dt = right_now - timedelta(days=cutoff_days)
35 games = DBSession.query(Game.game_type_cd, func.count()).\
36 filter(expr.between(Game.create_dt, cutoff_dt, right_now)).\
37 group_by(Game.game_type_cd).\
38 order_by(expr.desc(func.count())).all()
40 active_players = DBSession.query(func.count(sa.distinct(PlayerGameStat.player_id))).\
41 filter(PlayerGameStat.player_id > 2).\
42 filter(expr.between(PlayerGameStat.create_dt, cutoff_dt, right_now)).\
45 games = DBSession.query(Game.game_type_cd, func.count()).\
46 group_by(Game.game_type_cd).\
47 order_by(expr.desc(func.count())).all()
49 active_players = DBSession.query(func.count(sa.distinct(PlayerGameStat.player_id))).\
50 filter(PlayerGameStat.player_id > 2).\
55 total_games += total[1]
61 other_games += total[1]
64 # don't send anything if we don't have any activity
68 # This is ugly because we're doing template-like stuff within the
69 # view code. The alternative isn't any better, though: we would
70 # have to assemble the string inside the template by using a Python
71 # code block. For now I'll leave it like this since it is the lesser
73 in_paren = ", ".join(["{} {}".format(
75 "<a href='{}'>{}</a>".format(
76 request.route_url("game_index", _query={'type':g[0]}),
79 ) for g in games[:5]])
82 in_paren += ", {} other".format(other_games)
84 stat_line = "{} active players and {} games ({})".format(
90 except Exception as e:
96 @cache_region('hourly_term')
97 def get_ranks(game_type_cd):
99 Gets a set number of the top-ranked people for the specified game_type_cd.
101 The game_type_cd parameter is the type to fetch. Currently limited to
102 duel, dm, ctf, and tdm.
104 # how many ranks we want to fetch
105 leaderboard_count = 10
107 # only a few game modes are actually ranked
108 if game_type_cd not in 'duel' 'dm' 'ctf' 'tdm':
111 ranks = DBSession.query(PlayerRank).\
112 filter(PlayerRank.game_type_cd==game_type_cd).\
113 order_by(PlayerRank.rank).\
114 limit(leaderboard_count).all()
119 def top_players_by_time_q(cutoff_days):
121 Query for the top players by the amount of time played during a date range.
123 Games older than cutoff_days days old are ignored.
126 # only games played during this range are considered
127 right_now = datetime.utcnow()
128 cutoff_dt = right_now - timedelta(days=cutoff_days)
130 top_players_q = DBSession.query(Player.player_id, Player.nick,
131 func.sum(PlayerGameStat.alivetime)).\
132 filter(Player.player_id == PlayerGameStat.player_id).\
133 filter(Player.player_id > 2).\
134 filter(expr.between(PlayerGameStat.create_dt, cutoff_dt, right_now)).\
135 order_by(expr.desc(func.sum(PlayerGameStat.alivetime))).\
136 group_by(Player.nick).\
137 group_by(Player.player_id)
142 @cache_region('hourly_term')
143 def get_top_players_by_time(cutoff_days):
145 The top players by the amount of time played during a date range.
147 Games older than cutoff_days days old are ignored.
149 # how many to retrieve
152 # only games played during this range are considered
153 right_now = datetime.utcnow()
154 cutoff_dt = right_now - timedelta(days=cutoff_days)
156 top_players_q = top_players_by_time_q(cutoff_days)
158 top_players = top_players_q.limit(count).all()
160 top_players = [(player_id, html_colors(nick), score) \
161 for (player_id, nick, score) in top_players]
166 def top_servers_by_players_q(cutoff_days):
168 Query to get the top servers by the amount of players active
171 Games older than cutoff_days days old are ignored.
173 # only games played during this range are considered
174 right_now = datetime.utcnow()
175 cutoff_dt = right_now - timedelta(days=cutoff_days)
177 top_servers_q = DBSession.query(Server.server_id, Server.name,
179 filter(Game.server_id==Server.server_id).\
180 filter(expr.between(Game.create_dt, cutoff_dt, right_now)).\
181 order_by(expr.desc(func.count(Game.game_id))).\
182 group_by(Server.server_id).\
183 group_by(Server.name)
188 @cache_region('hourly_term')
189 def get_top_servers_by_players(cutoff_days):
191 The top servers by the amount of players active during a date range.
193 Games older than cutoff_days days old are ignored.
195 # how many to retrieve
198 top_servers = top_servers_by_players_q(cutoff_days).limit(count).all()
203 def top_maps_by_times_played_q(cutoff_days):
205 Query to retrieve the top maps by the amount of times it was played
208 Games older than cutoff_days days old are ignored.
210 # only games played during this range are considered
211 right_now = datetime.utcnow()
212 cutoff_dt = right_now - timedelta(days=cutoff_days)
214 top_maps_q = DBSession.query(Game.map_id, Map.name,
216 filter(Map.map_id==Game.map_id).\
217 filter(expr.between(Game.create_dt, cutoff_dt, right_now)).\
218 order_by(expr.desc(func.count())).\
219 group_by(Game.map_id).\
225 @cache_region('hourly_term')
226 def get_top_maps_by_times_played(cutoff_days):
228 The top maps by the amount of times it was played during a date range.
230 Games older than cutoff_days days old are ignored.
232 # how many to retrieve
235 top_maps = top_maps_by_times_played_q(cutoff_days).limit(count).all()
240 def _main_index_data(request):
242 leaderboard_lifetime = int(
243 request.registry.settings['xonstat.leaderboard_lifetime'])
245 leaderboard_lifetime = 30
247 leaderboard_count = 10
248 recent_games_count = 20
250 # summary statistics for the tagline
251 stat_line = get_summary_stats(request, None)
252 day_stat_line = get_summary_stats(request, 7)
255 # the three top ranks tables
257 for gtc in ['duel', 'ctf', 'dm', 'tdm']:
258 rank = get_ranks(gtc)
262 right_now = datetime.utcnow()
263 back_then = datetime.utcnow() - timedelta(days=leaderboard_lifetime)
265 # top players by playing time
266 top_players = get_top_players_by_time(leaderboard_lifetime)
268 # top servers by number of total players played
269 top_servers = get_top_servers_by_players(leaderboard_lifetime)
271 # top maps by total times played
272 top_maps = get_top_maps_by_times_played(leaderboard_lifetime)
274 # recent games played in descending order
275 rgs = recent_games_q(cutoff=back_then).limit(recent_games_count).all()
276 recent_games = [RecentGame(row) for row in rgs]
278 return {'top_players':top_players,
279 'top_servers':top_servers,
281 'recent_games':recent_games,
283 'stat_line':stat_line,
284 'day_stat_line':day_stat_line,
288 def main_index(request):
290 Display the main page information.
292 mainindex_data = _main_index_data(request)
294 # FIXME: code clone, should get these from _main_index_data
295 leaderboard_count = 10
296 recent_games_count = 20
298 for i in range(leaderboard_count-len(mainindex_data['top_players'])):
299 mainindex_data['top_players'].append(('-', '-', '-'))
301 for i in range(leaderboard_count-len(mainindex_data['top_servers'])):
302 mainindex_data['top_servers'].append(('-', '-', '-'))
304 for i in range(leaderboard_count-len(mainindex_data['top_maps'])):
305 mainindex_data['top_maps'].append(('-', '-', '-'))
307 return mainindex_data
310 def main_index_json(request):
312 JSON output of the main page information.
314 return [{'status':'not implemented'}]
317 def top_players_by_time(request):
318 current_page = request.params.get('page', 1)
320 cutoff_days = int(request.registry.settings.\
321 get('xonstat.leaderboard_lifetime', 30))
323 top_players_q = top_players_by_time_q(cutoff_days)
325 top_players = Page(top_players_q, current_page, items_per_page=25, url=page_url)
327 top_players.items = [(player_id, html_colors(nick), score) \
328 for (player_id, nick, score) in top_players.items]
330 return {'top_players':top_players}
333 def top_servers_by_players(request):
334 current_page = request.params.get('page', 1)
336 cutoff_days = int(request.registry.settings.\
337 get('xonstat.leaderboard_lifetime', 30))
339 top_servers_q = top_servers_by_players_q(cutoff_days)
341 top_servers = Page(top_servers_q, current_page, items_per_page=25, url=page_url)
343 return {'top_servers':top_servers}
346 def top_maps_by_times_played(request):
347 current_page = request.params.get('page', 1)
349 cutoff_days = int(request.registry.settings.\
350 get('xonstat.leaderboard_lifetime', 30))
352 top_maps_q = top_maps_by_times_played_q(cutoff_days)
354 top_maps = Page(top_maps_q, current_page, items_per_page=25, url=page_url)
356 return {'top_maps':top_maps}