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 datetime import datetime, timedelta
6 from pyramid.response import Response
7 from xonstat.models import *
8 from xonstat.util import *
9 from xonstat.views.helpers import RecentGame, recent_games_q
12 log = logging.getLogger(__name__)
15 @cache_region('hourly_term')
16 def get_summary_stats():
18 Gets the following aggregate or "summary" statistics about stats:
19 - the total number of players (total_players)
20 - the total number of servers (total_servers)
21 - the total number of games (total_games)
22 - the total number of dm games (dm_games)
23 - the total number of duel games (duel_games)
24 - the total number of ctf games (ctf_games)
26 It is worth noting that there is also a table built to house these
27 stats in case the query in this function becomes too long for the
28 one time it runs per hour. In that case there is a script in the
29 xonstatdb repo - update_summary_stats.sql - that can be used via
30 cron to update the data offline.
32 summary_stats = DBSession.query("total_players", "total_servers",
33 "total_games", "dm_games", "duel_games", "ctf_games").\
37 select game_type_cd, count(*) total_games
39 where game_type_cd in ('duel', 'dm', 'ctf')
43 select count(*) total_players
45 where active_ind = true
48 select count(*) total_servers
50 where active_ind = true
52 select tp.total_players, ts.total_servers, dm.total_games+
53 duel.total_games+ctf.total_games total_games,
54 dm.total_games dm_games, duel.total_games duel_games,
55 ctf.total_games ctf_games
56 from total_games dm, total_games duel, total_games ctf,
57 total_players tp, total_servers ts
58 where dm.game_type_cd = 'dm'
59 and ctf.game_type_cd = 'ctf'
60 and duel.game_type_cd = 'duel'
67 def _main_index_data(request):
69 leaderboard_lifetime = int(
70 request.registry.settings['xonstat.leaderboard_lifetime'])
72 leaderboard_lifetime = 30
74 leaderboard_count = 10
75 recent_games_count = 20
77 # summary statistics for the tagline
79 summary_stats = get_summary_stats()
84 duel_ranks = DBSession.query(PlayerRank.player_id, PlayerRank.nick,
86 filter(PlayerRank.game_type_cd=='duel').\
87 order_by(PlayerRank.rank).\
88 limit(leaderboard_count).all()
90 duel_ranks = [(player_id, html_colors(nick), elo) \
91 for (player_id, nick, elo) in duel_ranks]
94 ctf_ranks = DBSession.query(PlayerRank.player_id, PlayerRank.nick,
96 filter(PlayerRank.game_type_cd=='ctf').\
97 order_by(PlayerRank.rank).\
98 limit(leaderboard_count).all()
100 ctf_ranks = [(player_id, html_colors(nick), elo) \
101 for (player_id, nick, elo) in ctf_ranks]
104 dm_ranks = DBSession.query(PlayerRank.player_id, PlayerRank.nick,
106 filter(PlayerRank.game_type_cd=='dm').\
107 order_by(PlayerRank.rank).\
108 limit(leaderboard_count).all()
110 dm_ranks = [(player_id, html_colors(nick), elo) \
111 for (player_id, nick, elo) in dm_ranks]
113 right_now = datetime.utcnow()
114 back_then = datetime.utcnow() - timedelta(days=leaderboard_lifetime)
116 # top players by playing time
117 top_players = DBSession.query(Player.player_id, Player.nick,
118 func.sum(PlayerGameStat.alivetime)).\
119 filter(Player.player_id == PlayerGameStat.player_id).\
120 filter(Player.player_id > 2).\
121 filter(expr.between(PlayerGameStat.create_dt, back_then, right_now)).\
122 order_by(expr.desc(func.sum(PlayerGameStat.alivetime))).\
123 group_by(Player.nick).\
124 group_by(Player.player_id).limit(leaderboard_count).all()
126 top_players = [(player_id, html_colors(nick), score) \
127 for (player_id, nick, score) in top_players]
129 # top servers by number of total players played
130 top_servers = DBSession.query(Server.server_id, Server.name,
132 filter(Game.server_id==Server.server_id).\
133 filter(expr.between(Game.create_dt, back_then, right_now)).\
134 order_by(expr.desc(func.count(Game.game_id))).\
135 group_by(Server.server_id).\
136 group_by(Server.name).limit(leaderboard_count).all()
138 # top maps by total times played
139 top_maps = DBSession.query(Game.map_id, Map.name,
141 filter(Map.map_id==Game.map_id).\
142 filter(expr.between(Game.create_dt, back_then, right_now)).\
143 order_by(expr.desc(func.count())).\
144 group_by(Game.map_id).\
145 group_by(Map.name).limit(leaderboard_count).all()
147 # recent games played in descending order
148 rgs = recent_games_q(cutoff=back_then).limit(recent_games_count).all()
149 recent_games = [RecentGame(row) for row in rgs]
151 return {'top_players':top_players,
152 'top_servers':top_servers,
154 'recent_games':recent_games,
155 'duel_ranks':duel_ranks,
156 'ctf_ranks':ctf_ranks,
158 'summary_stats':summary_stats,
162 def main_index(request):
164 Display the main page information.
166 mainindex_data = _main_index_data(request)
168 # FIXME: code clone, should get these from _main_index_data
169 leaderboard_count = 10
170 recent_games_count = 20
172 for i in range(leaderboard_count-len(mainindex_data['duel_ranks'])):
173 mainindex_data['duel_ranks'].append(('-', '-', '-'))
175 for i in range(leaderboard_count-len(mainindex_data['ctf_ranks'])):
176 mainindex_data['ctf_ranks'].append(('-', '-', '-'))
178 for i in range(leaderboard_count-len(mainindex_data['dm_ranks'])):
179 mainindex_data['dm_ranks'].append(('-', '-', '-'))
181 for i in range(leaderboard_count-len(mainindex_data['top_players'])):
182 mainindex_data['top_players'].append(('-', '-', '-'))
184 for i in range(leaderboard_count-len(mainindex_data['top_servers'])):
185 mainindex_data['top_servers'].append(('-', '-', '-'))
187 for i in range(leaderboard_count-len(mainindex_data['top_maps'])):
188 mainindex_data['top_maps'].append(('-', '-', '-'))
190 return mainindex_data
193 def main_index_json(request):
195 JSON output of the main page information.
197 return [{'status':'not implemented'}]