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
19 RECENT_GAMES_COUNT = 20
22 class ServerIndex(object):
23 """Returns a list of servers."""
25 def __init__(self, request):
26 """Common parameter parsing."""
27 self.request = request
28 self.page = request.params.get("page", 1)
29 self.servers = self.raw()
32 """Returns the raw data shared by all renderers."""
34 server_q = DBSession.query(Server).order_by(Server.server_id.desc())
35 servers = Page(server_q, self.page, items_per_page=25, url=page_url)
43 """For rendering this data using something HTML-based."""
45 'servers': self.servers,
49 """For rendering this data using JSON."""
51 'servers': [s.to_dict() for s in self.servers],
55 class ServerInfoBase(object):
56 """Baseline parameter parsing for Server URLs with a server_id in them."""
58 def __init__(self, request, limit=None, last=None):
59 """Common parameter parsing."""
60 self.request = request
61 self.server_id = request.matchdict.get("id", None)
63 raw_lifetime = request.registry.settings.get('xonstat.leaderboard_lifetime',
65 self.lifetime = int(raw_lifetime)
67 self.limit = request.params.get("limit", limit)
68 self.last = request.params.get("last", last)
69 self.now = datetime.utcnow()
72 class ServerTopMaps(ServerInfoBase):
73 """Returns the top maps played on a given server."""
75 def __init__(self, request, limit=LEADERBOARD_COUNT, last=None):
76 """Common parameter parsing."""
77 super(ServerTopMaps, self).__init__(request, limit, last)
79 self.top_maps = self.raw()
82 """Returns the raw data shared by all renderers."""
84 top_maps_q = DBSession.query(
85 fg.row_number().over(order_by=expr.desc(func.count())).label("rank"),
86 Game.map_id, Map.name, func.count().label("times_played"))\
87 .filter(Map.map_id==Game.map_id)\
88 .filter(Game.server_id==self.server_id)\
89 .filter(Game.create_dt > (self.now - timedelta(days=self.lifetime)))\
90 .group_by(Game.map_id)\
92 .order_by(expr.desc(func.count()))
95 top_maps_q = top_maps_q.offset(self.last)
98 top_maps_q = top_maps_q.limit(self.limit)
100 top_maps = top_maps_q.all()
101 except Exception as e:
108 """Returns the HTML-ready representation."""
112 """For rendering this data using JSON."""
117 "times_played": tm.times_played,
118 } for tm in self.top_maps]
123 class ServerTopScorers(ServerInfoBase):
124 """Returns the top scorers on a given server."""
126 def __init__(self, request, limit=LEADERBOARD_COUNT, last=None):
127 """Common parameter parsing."""
128 super(ServerTopScorers, self).__init__(request, limit, last)
129 self.top_scorers = self.raw()
132 """Top scorers on this server by total score."""
134 top_scorers_q = DBSession.query(
135 fg.row_number().over(
136 order_by=expr.desc(func.sum(PlayerGameStat.score))).label("rank"),
137 Player.player_id, Player.nick,
138 func.sum(PlayerGameStat.score).label("total_score"))\
139 .filter(Player.player_id == PlayerGameStat.player_id)\
140 .filter(Game.game_id == PlayerGameStat.game_id)\
141 .filter(Game.server_id == self.server_id)\
142 .filter(Player.player_id > 2)\
143 .filter(PlayerGameStat.create_dt >
144 (self.now - timedelta(days=LEADERBOARD_LIFETIME)))\
145 .order_by(expr.desc(func.sum(PlayerGameStat.score)))\
146 .group_by(Player.nick)\
147 .group_by(Player.player_id)
150 top_scorers_q = top_scorers_q.offset(self.last)
153 top_scorers_q = top_scorers_q.limit(self.limit)
155 top_scorers = top_scorers_q.all()
157 except Exception as e:
164 """Returns an HTML-ready representation."""
165 TopScorer = namedtuple("TopScorer", ["rank", "player_id", "nick", "total_score"])
167 top_scorers = [TopScorer(ts.rank, ts.player_id, html_colors(ts.nick), ts.total_score)
168 for ts in self.top_scorers]
172 """For rendering this data using JSON."""
175 "player_id": ts.player_id,
177 "score": ts.total_score,
178 } for ts in self.top_scorers]
183 class ServerTopPlayers(ServerInfoBase):
184 """Returns the top players by playing time on a given server."""
186 def __init__(self, request, limit=LEADERBOARD_COUNT, last=None):
187 """Common parameter parsing."""
188 super(ServerTopPlayers, self).__init__(request, limit, last)
189 self.top_players = self.raw()
192 """Top players on this server by total playing time."""
194 top_players_q = DBSession.query(
195 fg.row_number().over(
196 order_by=expr.desc(func.sum(PlayerGameStat.alivetime))).label("rank"),
197 Player.player_id, Player.nick,
198 func.sum(PlayerGameStat.alivetime).label("alivetime"))\
199 .filter(Player.player_id == PlayerGameStat.player_id)\
200 .filter(Game.game_id == PlayerGameStat.game_id)\
201 .filter(Game.server_id == self.server_id)\
202 .filter(Player.player_id > 2)\
203 .filter(PlayerGameStat.create_dt > (self.now - timedelta(days=self.lifetime)))\
204 .order_by(expr.desc(func.sum(PlayerGameStat.alivetime)))\
205 .group_by(Player.nick)\
206 .group_by(Player.player_id)
209 top_players_q = top_players_q.offset(self.last)
212 top_players_q = top_players_q.limit(self.limit)
214 top_players = top_players_q.all()
216 except Exception as e:
223 """Returns the HTML-ready representation."""
224 TopPlayer = namedtuple("TopPlayer", ["rank", "player_id", "nick", "alivetime"])
226 top_players = [TopPlayer(tp.rank, tp.player_id, html_colors(tp.nick), tp.alivetime)
227 for tp in self.top_players]
232 """For rendering this data using JSON."""
235 "player_id": ts.player_id,
237 "time": ts.alivetime.total_seconds(),
238 } for ts in self.top_players]
243 class ServerInfo(ServerInfoBase):
244 """Returns detailed information about a particular server."""
246 def __init__(self, request):
247 """Common data and parameters."""
248 super(ServerInfo, self).__init__(request)
250 # this view uses data from other views, so we'll save the data at that level
252 self.server = DBSession.query(Server).filter_by(server_id=self.server_id).one()
253 self.top_maps_v = ServerTopMaps(self.request, limit=LEADERBOARD_COUNT)
254 self.top_scorers_v = ServerTopScorers(self.request, limit=LEADERBOARD_COUNT)
255 self.top_players_v = ServerTopPlayers(self.request, limit=LEADERBOARD_COUNT)
257 rgs = recent_games_q(server_id=self.server_id).limit(RECENT_GAMES_COUNT).all()
258 self.recent_games = [RecentGame(row) for row in rgs]
263 """Returns the raw data shared by all renderers."""
265 'server': self.server,
266 'top_players': self.top_players_v.top_players,
267 'top_scorers': self.top_scorers_v.top_scorers,
268 'top_maps': self.top_maps_v.top_maps,
269 'recent_games': self.recent_games,
273 """For rendering this data using something HTML-based."""
275 'server': self.server,
276 'top_players': self.top_players_v.html(),
277 'top_scorers': self.top_scorers_v.html(),
278 'top_maps': self.top_maps_v.html(),
279 'recent_games': self.recent_games,
283 """For rendering this data using JSON."""
285 'server': self.server.to_dict(),
286 'top_players': self.top_players_v.json(),
287 'top_scorers': self.top_scorers_v.json(),
288 'top_maps': self.top_maps_v.json(),
289 'recent_games': [rg.to_dict() for rg in self.recent_games],