2 import sqlalchemy.sql.functions as func
3 import sqlalchemy.sql.expression as expr
4 from collections import namedtuple
5 from datetime import datetime, timedelta
6 from pyramid.httpexceptions import HTTPNotFound
7 from sqlalchemy import func as fg
8 from webhelpers.paginate import Page
9 from xonstat.models import DBSession, Player, Server, Map, Game, PlayerGameStat
10 from xonstat.util import page_url, html_colors
11 from xonstat.views.helpers import RecentGame, recent_games_q
13 log = logging.getLogger(__name__)
17 LEADERBOARD_LIFETIME = 30
18 LEADERBOARD_COUNT = 10
20 RECENT_GAMES_COUNT = 20
23 class ServerIndex(object):
24 """Returns a list of servers."""
26 def __init__(self, request):
27 """Common parameter parsing."""
28 self.request = request
29 self.page = request.params.get("page", 1)
30 self.servers = self.raw()
33 """Returns the raw data shared by all renderers."""
35 server_q = DBSession.query(Server).order_by(Server.server_id.desc())
36 servers = Page(server_q, self.page, items_per_page=25, url=page_url)
44 """For rendering this data using something HTML-based."""
46 'servers': self.servers,
50 """For rendering this data using JSON."""
52 'servers': [s.to_dict() for s in self.servers],
56 class ServerInfoBase(object):
57 """Baseline parameter parsing for Server URLs with a server_id in them."""
59 def __init__(self, request, limit=None, last=None):
60 """Common parameter parsing."""
61 self.request = request
62 self.server_id = request.matchdict.get("id", None)
64 raw_lifetime = request.registry.settings.get('xonstat.leaderboard_lifetime',
66 self.lifetime = int(raw_lifetime)
68 self.limit = request.params.get("limit", limit)
69 self.last = request.params.get("last", last)
70 self.now = datetime.utcnow()
73 class ServerTopMaps(ServerInfoBase):
74 """Returns the top maps played on a given server."""
76 def __init__(self, request, limit=INDEX_COUNT, last=None):
77 """Common parameter parsing."""
78 super(ServerTopMaps, self).__init__(request, limit, last)
80 self.top_maps = self.raw()
83 """Returns the raw data shared by all renderers."""
85 top_maps_q = DBSession.query(
86 fg.row_number().over(order_by=expr.desc(func.count())).label("rank"),
87 Game.map_id, Map.name, func.count().label("times_played"))\
88 .filter(Map.map_id==Game.map_id)\
89 .filter(Game.server_id==self.server_id)\
90 .filter(Game.create_dt > (self.now - timedelta(days=self.lifetime)))\
91 .group_by(Game.map_id)\
93 .order_by(expr.desc(func.count()))
96 top_maps_q = top_maps_q.offset(self.last)
99 top_maps_q = top_maps_q.limit(self.limit)
101 top_maps = top_maps_q.all()
102 except Exception as e:
109 """Returns the HTML-ready representation."""
111 # build the query string
113 if len(self.top_maps) > 1:
114 query['last'] = self.top_maps[-1].rank
117 "server_id": self.server_id,
118 "top_maps": self.top_maps,
123 """For rendering this data using JSON."""
128 "times_played": tm.times_played,
129 } for tm in self.top_maps]
134 class ServerTopScorers(ServerInfoBase):
135 """Returns the top scorers on a given server."""
137 def __init__(self, request, limit=INDEX_COUNT, last=None):
138 """Common parameter parsing."""
139 super(ServerTopScorers, self).__init__(request, limit, last)
140 self.top_scorers = self.raw()
143 """Top scorers on this server by total score."""
145 top_scorers_q = DBSession.query(
146 fg.row_number().over(
147 order_by=expr.desc(func.sum(PlayerGameStat.score))).label("rank"),
148 Player.player_id, Player.nick,
149 func.sum(PlayerGameStat.score).label("total_score"))\
150 .filter(Player.player_id == PlayerGameStat.player_id)\
151 .filter(Game.game_id == PlayerGameStat.game_id)\
152 .filter(Game.server_id == self.server_id)\
153 .filter(Player.player_id > 2)\
154 .filter(PlayerGameStat.create_dt >
155 (self.now - timedelta(days=LEADERBOARD_LIFETIME)))\
156 .order_by(expr.desc(func.sum(PlayerGameStat.score)))\
157 .group_by(Player.nick)\
158 .group_by(Player.player_id)
161 top_scorers_q = top_scorers_q.offset(self.last)
164 top_scorers_q = top_scorers_q.limit(self.limit)
166 top_scorers = top_scorers_q.all()
168 except Exception as e:
175 """Returns an HTML-ready representation."""
176 TopScorer = namedtuple("TopScorer", ["rank", "player_id", "nick", "total_score"])
178 top_scorers = [TopScorer(ts.rank, ts.player_id, html_colors(ts.nick), ts.total_score)
179 for ts in self.top_scorers]
181 # build the query string
183 if len(top_scorers) > 1:
184 query['last'] = top_scorers[-1].rank
187 "server_id": self.server_id,
188 "top_scorers": top_scorers,
193 """For rendering this data using JSON."""
196 "player_id": ts.player_id,
198 "score": ts.total_score,
199 } for ts in self.top_scorers]
204 class ServerTopPlayers(ServerInfoBase):
205 """Returns the top players by playing time on a given server."""
207 def __init__(self, request, limit=INDEX_COUNT, last=None):
208 """Common parameter parsing."""
209 super(ServerTopPlayers, self).__init__(request, limit, last)
210 self.top_players = self.raw()
213 """Top players on this server by total playing time."""
215 top_players_q = DBSession.query(
216 fg.row_number().over(
217 order_by=expr.desc(func.sum(PlayerGameStat.alivetime))).label("rank"),
218 Player.player_id, Player.nick,
219 func.sum(PlayerGameStat.alivetime).label("alivetime"))\
220 .filter(Player.player_id == PlayerGameStat.player_id)\
221 .filter(Game.game_id == PlayerGameStat.game_id)\
222 .filter(Game.server_id == self.server_id)\
223 .filter(Player.player_id > 2)\
224 .filter(PlayerGameStat.create_dt > (self.now - timedelta(days=self.lifetime)))\
225 .order_by(expr.desc(func.sum(PlayerGameStat.alivetime)))\
226 .group_by(Player.nick)\
227 .group_by(Player.player_id)
230 top_players_q = top_players_q.offset(self.last)
233 top_players_q = top_players_q.limit(self.limit)
235 top_players = top_players_q.all()
237 except Exception as e:
244 """Returns the HTML-ready representation."""
245 TopPlayer = namedtuple("TopPlayer", ["rank", "player_id", "nick", "alivetime"])
247 top_players = [TopPlayer(tp.rank, tp.player_id, html_colors(tp.nick), tp.alivetime)
248 for tp in self.top_players]
250 # build the query string
252 if len(top_players) > 1:
253 query['last'] = top_players[-1].rank
256 "server_id": self.server_id,
257 "top_players": top_players,
262 """For rendering this data using JSON."""
265 "player_id": ts.player_id,
267 "time": ts.alivetime.total_seconds(),
268 } for ts in self.top_players]
273 class ServerInfo(ServerInfoBase):
274 """Returns detailed information about a particular server."""
276 def __init__(self, request):
277 """Common data and parameters."""
278 super(ServerInfo, self).__init__(request)
280 # this view uses data from other views, so we'll save the data at that level
282 self.server = DBSession.query(Server).filter_by(server_id=self.server_id).one()
283 self.top_maps_v = ServerTopMaps(self.request, limit=LEADERBOARD_COUNT)
284 self.top_scorers_v = ServerTopScorers(self.request, limit=LEADERBOARD_COUNT)
285 self.top_players_v = ServerTopPlayers(self.request, limit=LEADERBOARD_COUNT)
287 rgs = recent_games_q(server_id=self.server_id).limit(RECENT_GAMES_COUNT).all()
288 self.recent_games = [RecentGame(row) for row in rgs]
293 """Returns the raw data shared by all renderers."""
295 'server': self.server,
296 'top_players': self.top_players_v.top_players,
297 'top_scorers': self.top_scorers_v.top_scorers,
298 'top_maps': self.top_maps_v.top_maps,
299 'recent_games': self.recent_games,
303 """For rendering this data using something HTML-based."""
305 'server': self.server,
306 'top_players': self.top_players_v.html()["top_players"],
307 'top_scorers': self.top_scorers_v.html()["top_scorers"],
308 'top_maps': self.top_maps_v.html()["top_maps"],
309 'recent_games': self.recent_games,
310 'lifetime': self.lifetime,
314 """For rendering this data using JSON."""
316 'server': self.server.to_dict(),
317 'top_players': self.top_players_v.json(),
318 'top_scorers': self.top_scorers_v.json(),
319 'top_maps': self.top_maps_v.json(),
320 'recent_games': [rg.to_dict() for rg in self.recent_games],