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.server_index()
32 def server_index(self):
33 """Returns the raw data shared by all renderers."""
35 server_q = DBSession.query(Server)\
36 .filter(Server.active_ind)\
37 .order_by(Server.server_id.desc())
38 servers = Page(server_q, self.page, items_per_page=25, url=page_url)
40 except Exception as e:
47 """For rendering this data using something HTML-based."""
49 'servers': self.servers,
53 """For rendering this data using JSON."""
55 'servers': [s.to_dict() for s in self.servers],
59 class ServerInfoBase(object):
60 """Baseline parameter parsing for Server URLs with a server_id in them."""
62 def __init__(self, request, limit=None, last=None):
63 """Common parameter parsing."""
64 self.request = request
65 self.server_id = request.matchdict.get("id", None)
67 raw_lifetime = LEADERBOARD_LIFETIME
69 self.lifetime = int(raw_lifetime)
71 self.limit = request.params.get("limit", limit)
72 self.last = request.params.get("last", last)
73 self.now = datetime.utcnow()
76 class ServerTopMaps(ServerInfoBase):
77 """Returns the top maps played on a given server."""
79 def __init__(self, request, limit=INDEX_COUNT, last=None):
80 """Common parameter parsing."""
81 super(ServerTopMaps, self).__init__(request, limit, last)
83 self.top_maps = self.top_maps()
86 """Returns the raw data shared by all renderers."""
88 top_maps_q = DBSession.query(
89 fg.row_number().over(order_by=expr.desc(func.count())).label("rank"),
90 Game.map_id, Map.name, func.count().label("times_played"))\
91 .filter(Map.map_id == Game.map_id)\
92 .filter(Game.server_id == self.server_id)\
93 .filter(Game.create_dt > (self.now - timedelta(days=self.lifetime)))\
94 .group_by(Game.map_id)\
96 .order_by(expr.desc(func.count()))
99 top_maps_q = top_maps_q.offset(self.last)
102 top_maps_q = top_maps_q.limit(self.limit)
104 top_maps = top_maps_q.all()
105 except Exception as e:
112 """Returns the HTML-ready representation."""
114 # build the query string
116 if len(self.top_maps) > 1:
117 query['last'] = self.top_maps[-1].rank
120 "server_id": self.server_id,
121 "top_maps": self.top_maps,
122 "lifetime": self.lifetime,
127 """For rendering this data using JSON."""
132 "times_played": tm.times_played,
133 } for tm in self.top_maps]
136 "server_id": self.server_id,
137 "top_maps": top_maps,
141 class ServerTopScorers(ServerInfoBase):
142 """Returns the top scorers on a given server."""
144 def __init__(self, request, limit=INDEX_COUNT, last=None):
145 """Common parameter parsing."""
146 super(ServerTopScorers, self).__init__(request, limit, last)
147 self.top_scorers = self.top_scorers()
149 def top_scorers(self):
150 """Top scorers on this server by total score."""
152 top_scorers_q = DBSession.query(
153 fg.row_number().over(
154 order_by=expr.desc(func.sum(PlayerGameStat.score))).label("rank"),
155 Player.player_id, Player.nick,
156 func.sum(PlayerGameStat.score).label("total_score"))\
157 .filter(Player.player_id == PlayerGameStat.player_id)\
158 .filter(Game.game_id == PlayerGameStat.game_id)\
159 .filter(Game.server_id == self.server_id)\
160 .filter(Player.player_id > 2)\
161 .filter(PlayerGameStat.create_dt >
162 (self.now - timedelta(days=self.lifetime)))\
163 .order_by(expr.desc(func.sum(PlayerGameStat.score)))\
164 .group_by(Player.nick)\
165 .group_by(Player.player_id)
168 top_scorers_q = top_scorers_q.offset(self.last)
171 top_scorers_q = top_scorers_q.limit(self.limit)
173 top_scorers = top_scorers_q.all()
175 except Exception as e:
182 """Returns an HTML-ready representation."""
183 TopScorer = namedtuple("TopScorer", ["rank", "player_id", "nick", "total_score"])
185 top_scorers = [TopScorer(ts.rank, ts.player_id, html_colors(ts.nick), ts.total_score)
186 for ts in self.top_scorers]
188 # build the query string
190 if len(top_scorers) > 1:
191 query['last'] = top_scorers[-1].rank
194 "server_id": self.server_id,
195 "top_scorers": top_scorers,
196 "lifetime": self.lifetime,
201 """For rendering this data using JSON."""
204 "player_id": ts.player_id,
206 "score": ts.total_score,
207 } for ts in self.top_scorers]
210 "server_id": self.server_id,
211 "top_scorers": top_scorers,
215 class ServerTopPlayers(ServerInfoBase):
216 """Returns the top players by playing time on a given server."""
218 def __init__(self, request, limit=INDEX_COUNT, last=None):
219 """Common parameter parsing."""
220 super(ServerTopPlayers, self).__init__(request, limit, last)
221 self.top_players = self.top_players()
223 def top_players(self):
224 """Top players on this server by total playing time."""
226 top_players_q = DBSession.query(
227 fg.row_number().over(
228 order_by=expr.desc(func.sum(PlayerGameStat.alivetime))).label("rank"),
229 Player.player_id, Player.nick,
230 func.sum(PlayerGameStat.alivetime).label("alivetime"))\
231 .filter(Player.player_id == PlayerGameStat.player_id)\
232 .filter(Game.game_id == PlayerGameStat.game_id)\
233 .filter(Game.server_id == self.server_id)\
234 .filter(Player.player_id > 2)\
235 .filter(PlayerGameStat.create_dt > (self.now - timedelta(days=self.lifetime)))\
236 .order_by(expr.desc(func.sum(PlayerGameStat.alivetime)))\
237 .group_by(Player.nick)\
238 .group_by(Player.player_id)
241 top_players_q = top_players_q.offset(self.last)
244 top_players_q = top_players_q.limit(self.limit)
246 top_players = top_players_q.all()
248 except Exception as e:
255 """Returns the HTML-ready representation."""
256 TopPlayer = namedtuple("TopPlayer", ["rank", "player_id", "nick", "alivetime"])
258 top_players = [TopPlayer(tp.rank, tp.player_id, html_colors(tp.nick), tp.alivetime)
259 for tp in self.top_players]
261 # build the query string
263 if len(top_players) > 1:
264 query['last'] = top_players[-1].rank
267 "server_id": self.server_id,
268 "top_players": top_players,
269 "lifetime": self.lifetime,
274 """For rendering this data using JSON."""
277 "player_id": ts.player_id,
279 "time": ts.alivetime.total_seconds(),
280 } for ts in self.top_players]
283 "server_id": self.server_id,
284 "top_players": top_players,
288 class ServerInfo(ServerInfoBase):
289 """Returns detailed information about a particular server."""
291 def __init__(self, request):
292 """Common data and parameters."""
293 super(ServerInfo, self).__init__(request)
295 # this view uses data from other views, so we'll save the data at that level
297 self.server = DBSession.query(Server)\
298 .filter(Server.active_ind)\
299 .filter(Server.server_id == self.server_id)\
302 self.top_maps_v = ServerTopMaps(self.request, limit=LEADERBOARD_COUNT)
303 self.top_scorers_v = ServerTopScorers(self.request, limit=LEADERBOARD_COUNT)
304 self.top_players_v = ServerTopPlayers(self.request, limit=LEADERBOARD_COUNT)
306 rgs = recent_games_q(server_id=self.server_id).limit(RECENT_GAMES_COUNT).all()
307 self.recent_games = [RecentGame(row) for row in rgs]
312 """For rendering this data using something HTML-based."""
314 'server': self.server,
315 'top_players': self.top_players_v.html().get("top_players", None),
316 'top_scorers': self.top_scorers_v.html().get("top_scorers", None),
317 'top_maps': self.top_maps_v.html().get("top_maps", None),
318 'recent_games': self.recent_games,
319 'lifetime': self.lifetime,
323 """For rendering this data using JSON."""
325 'server': self.server.to_dict(),
326 'top_players': self.top_players_v.json(),
327 'top_scorers': self.top_scorers_v.json(),
328 'top_maps': self.top_maps_v.json(),
329 'recent_games': [rg.to_dict() for rg in self.recent_games],