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
13 log = logging.getLogger(__name__)
16 @cache_region('hourly_term')
17 def get_summary_stats():
19 Gets the following aggregate or "summary" statistics about stats:
20 - the total number of players (total_players)
21 - the total number of servers (total_servers)
22 - the total number of games (total_games)
23 - the total number of dm games (dm_games)
24 - the total number of duel games (duel_games)
25 - the total number of ctf games (ctf_games)
27 It is worth noting that there is also a table built to house these
28 stats in case the query in this function becomes too long for the
29 one time it runs per hour. In that case there is a script in the
30 xonstatdb repo - update_summary_stats.sql - that can be used via
31 cron to update the data offline.
33 summary_stats = DBSession.query("total_players", "total_servers",
34 "total_games", "dm_games", "duel_games", "ctf_games").\
38 select game_type_cd, count(*) total_games
40 where game_type_cd in ('duel', 'dm', 'ctf')
44 select count(*) total_players
46 where active_ind = true
49 select count(*) total_servers
51 where active_ind = true
53 select tp.total_players, ts.total_servers, dm.total_games+
54 duel.total_games+ctf.total_games total_games,
55 dm.total_games dm_games, duel.total_games duel_games,
56 ctf.total_games ctf_games
57 from total_games dm, total_games duel, total_games ctf,
58 total_players tp, total_servers ts
59 where dm.game_type_cd = 'dm'
60 and ctf.game_type_cd = 'ctf'
61 and duel.game_type_cd = 'duel'
68 @cache_region('hourly_term')
69 def get_ranks(game_type_cd):
71 Gets a set number of the top-ranked people for the specified game_type_cd.
73 The game_type_cd parameter is the type to fetch. Currently limited to
74 duel, dm, ctf, and tdm.
76 # how many ranks we want to fetch
77 leaderboard_count = 10
79 # only a few game modes are actually ranked
80 if game_type_cd not in 'duel' 'dm' 'ctf' 'tdm':
83 ranks = DBSession.query(PlayerRank).\
84 filter(PlayerRank.game_type_cd==game_type_cd).\
85 order_by(PlayerRank.rank).\
86 limit(leaderboard_count).all()
91 @cache_region('hourly_term')
92 def top_players_by_time(cutoff_days):
94 The top players by the amount of time played during a date range.
96 Games older than cutoff_days days old are ignored.
98 # how many to retrieve
101 # only games played during this range are considered
102 right_now = datetime.utcnow()
103 cutoff_dt = right_now - timedelta(days=cutoff_days)
105 top_players = DBSession.query(Player.player_id, Player.nick,
106 func.sum(PlayerGameStat.alivetime)).\
107 filter(Player.player_id == PlayerGameStat.player_id).\
108 filter(Player.player_id > 2).\
109 filter(expr.between(PlayerGameStat.create_dt, cutoff_dt, right_now)).\
110 order_by(expr.desc(func.sum(PlayerGameStat.alivetime))).\
111 group_by(Player.nick).\
112 group_by(Player.player_id).limit(count).all()
114 top_players = [(player_id, html_colors(nick), score) \
115 for (player_id, nick, score) in top_players]
120 @cache_region('hourly_term')
121 def top_servers_by_players(cutoff_days):
123 The top servers by the amount of players active during a date range.
125 Games older than cutoff_days days old are ignored.
127 # how many to retrieve
130 # only games played during this range are considered
131 right_now = datetime.utcnow()
132 cutoff_dt = right_now - timedelta(days=cutoff_days)
134 top_servers = DBSession.query(Server.server_id, Server.name,
136 filter(Game.server_id==Server.server_id).\
137 filter(expr.between(Game.create_dt, cutoff_dt, right_now)).\
138 order_by(expr.desc(func.count(Game.game_id))).\
139 group_by(Server.server_id).\
140 group_by(Server.name).limit(count).all()
145 @cache_region('hourly_term')
146 def top_maps_by_times_played(cutoff_days):
148 The top maps by the amount of times it was played during a date range.
150 Games older than cutoff_days days old are ignored.
152 # how many to retrieve
155 # only games played during this range are considered
156 right_now = datetime.utcnow()
157 cutoff_dt = right_now - timedelta(days=cutoff_days)
159 top_maps = DBSession.query(Game.map_id, Map.name,
161 filter(Map.map_id==Game.map_id).\
162 filter(expr.between(Game.create_dt, cutoff_dt, right_now)).\
163 order_by(expr.desc(func.count())).\
164 group_by(Game.map_id).\
165 group_by(Map.name).limit(count).all()
170 def _main_index_data(request):
172 leaderboard_lifetime = int(
173 request.registry.settings['xonstat.leaderboard_lifetime'])
175 leaderboard_lifetime = 30
177 leaderboard_count = 10
178 recent_games_count = 20
180 # summary statistics for the tagline
182 summary_stats = get_summary_stats()
186 # the three top ranks tables
188 for gtc in ['duel', 'ctf', 'dm', 'tdm']:
189 rank = get_ranks(gtc)
193 right_now = datetime.utcnow()
194 back_then = datetime.utcnow() - timedelta(days=leaderboard_lifetime)
196 # top players by playing time
197 top_players = top_players_by_time(leaderboard_lifetime)
199 # top servers by number of total players played
200 top_servers = top_servers_by_players(leaderboard_lifetime)
202 # top maps by total times played
203 top_maps = top_maps_by_times_played(leaderboard_lifetime)
205 # recent games played in descending order
206 rgs = recent_games_q(cutoff=back_then).limit(recent_games_count).all()
207 recent_games = [RecentGame(row) for row in rgs]
209 return {'top_players':top_players,
210 'top_servers':top_servers,
212 'recent_games':recent_games,
214 'summary_stats':summary_stats,
218 def main_index(request):
220 Display the main page information.
222 mainindex_data = _main_index_data(request)
224 # FIXME: code clone, should get these from _main_index_data
225 leaderboard_count = 10
226 recent_games_count = 20
228 for i in range(leaderboard_count-len(mainindex_data['top_players'])):
229 mainindex_data['top_players'].append(('-', '-', '-'))
231 for i in range(leaderboard_count-len(mainindex_data['top_servers'])):
232 mainindex_data['top_servers'].append(('-', '-', '-'))
234 for i in range(leaderboard_count-len(mainindex_data['top_maps'])):
235 mainindex_data['top_maps'].append(('-', '-', '-'))
237 return mainindex_data
240 def main_index_json(request):
242 JSON output of the main page information.
244 return [{'status':'not implemented'}]