Add separate views for the top servers on a map.
[xonotic/xonstat.git] / xonstat / views / map.py
1 import logging
2 from collections import namedtuple
3 from datetime import datetime, timedelta
4
5 import sqlalchemy.sql.expression as expr
6 import sqlalchemy.sql.functions as func
7 from pyramid.httpexceptions import HTTPNotFound
8 from sqlalchemy import func as fg
9 from webhelpers.paginate import Page
10 from xonstat.models import DBSession, Server, Map, Game, PlayerGameStat, Player, PlayerCaptime
11 from xonstat.models.map import MapCapTime
12 from xonstat.util import page_url, html_colors
13 from xonstat.views.helpers import RecentGame, recent_games_q
14
15 log = logging.getLogger(__name__)
16
17 # Defaults
18 INDEX_COUNT = 20
19 LEADERBOARD_LIFETIME = 30
20
21
22 class MapIndex(object):
23     """Returns a list of maps."""
24
25     def __init__(self, request):
26         """Common parameter parsing."""
27         self.request = request
28         self.last = request.params.get("last", None)
29
30         # all views share this data, so we'll pre-calculate
31         self.maps = self.map_index()
32
33     def map_index(self):
34         """Returns the raw data shared by all renderers."""
35         try:
36             map_q = DBSession.query(Map)
37
38             if self.last:
39                 map_q = map_q.filter(Map.map_id < self.last)
40
41             map_q = map_q.order_by(Map.map_id.desc()).limit(INDEX_COUNT)
42             maps = map_q.all()
43
44         except Exception as e:
45             log.debug(e)
46             raise HTTPNotFound
47
48         return maps
49
50     def html(self):
51         """For rendering this data using something HTML-based."""
52         # build the query string
53         query = {}
54         if len(self.maps) > 1:
55             query['last'] = self.maps[-1].map_id
56
57         return {
58             'maps': self.maps,
59             'query': query,
60         }
61
62     def json(self):
63         """For rendering this data using JSON."""
64         return {
65             'maps': [m.to_dict() for m in self.maps],
66             'last': self.last,
67         }
68
69
70 class MapInfoBase(object):
71     """Base class for all map-based views with a map_id parameter in them."""
72
73     def __init__(self, request, limit=None, last=None):
74         """Common parameter parsing."""
75         self.request = request
76         self.map_id = request.matchdict.get("id", None)
77
78         raw_lifetime = request.registry.settings.get('xonstat.leaderboard_lifetime',
79                                                      LEADERBOARD_LIFETIME)
80         self.lifetime = int(raw_lifetime)
81
82         self.limit = request.params.get("limit", limit)
83         self.last = request.params.get("last", last)
84         self.now = datetime.utcnow()
85
86
87 class MapTopScorers(MapInfoBase):
88     """Returns the top scorers on a given map."""
89
90     def __init__(self, request, limit=INDEX_COUNT, last=None):
91         """Common parameter parsing."""
92         super(MapTopScorers, self).__init__(request, limit, last)
93         self.top_scorers = self.get_top_scorers()
94
95     def get_top_scorers(self):
96         """Top players by score. Shared by all renderers."""
97         cutoff = self.now - timedelta(days=self.lifetime)
98
99         top_scorers_q = DBSession.query(
100             fg.row_number().over(order_by=expr.desc(func.sum(PlayerGameStat.score))).label("rank"),
101             Player.player_id, Player.nick, func.sum(PlayerGameStat.score).label("total_score"))\
102             .filter(Player.player_id == PlayerGameStat.player_id)\
103             .filter(Game.game_id == PlayerGameStat.game_id)\
104             .filter(Game.map_id == self.map_id)\
105             .filter(Player.player_id > 2)\
106             .filter(PlayerGameStat.create_dt > cutoff)\
107             .order_by(expr.desc(func.sum(PlayerGameStat.score)))\
108             .group_by(Player.nick)\
109             .group_by(Player.player_id)
110
111         if self.last:
112             top_scorers_q = top_scorers_q.offset(self.last)
113
114         if self.limit:
115             top_scorers_q = top_scorers_q.limit(self.limit)
116
117         top_scorers = top_scorers_q.all()
118
119         return top_scorers
120
121     def html(self):
122         """Returns an HTML-ready representation."""
123         TopScorer = namedtuple("TopScorer", ["rank", "player_id", "nick", "total_score"])
124
125         top_scorers = [TopScorer(ts.rank, ts.player_id, html_colors(ts.nick), ts.total_score)
126                        for ts in self.top_scorers]
127
128         # build the query string
129         query = {}
130         if len(top_scorers) > 1:
131             query['last'] = top_scorers[-1].rank
132
133         return {
134             "map_id": self.map_id,
135             "top_scorers": top_scorers,
136             "lifetime": self.lifetime,
137             "query": query,
138         }
139
140     def json(self):
141         """For rendering this data using JSON."""
142         top_scorers = [{
143             "rank": ts.rank,
144             "player_id": ts.player_id,
145             "nick": ts.nick,
146             "score": ts.total_score,
147         } for ts in self.top_scorers]
148
149         return {
150             "map_id": self.map_id,
151             "top_scorers": top_scorers,
152         }
153
154
155 class MapTopPlayers(MapInfoBase):
156     """Returns the top players by time on a given map."""
157
158     def __init__(self, request, limit=INDEX_COUNT, last=None):
159         """Common parameter parsing."""
160         super(MapTopPlayers, self).__init__(request, limit, last)
161         self.top_players = self.get_top_players()
162
163     def get_top_players(self):
164         """Top players by score. Shared by all renderers."""
165         cutoff = self.now - timedelta(days=self.lifetime)
166
167         top_players_q = DBSession.query(
168             fg.row_number().over(order_by=expr.desc(func.sum(PlayerGameStat.alivetime))).label("rank"),
169             Player.player_id, Player.nick, func.sum(PlayerGameStat.alivetime).label("alivetime"))\
170             .filter(Player.player_id == PlayerGameStat.player_id)\
171             .filter(Game.game_id == PlayerGameStat.game_id)\
172             .filter(Game.map_id == self.map_id)\
173             .filter(Player.player_id > 2)\
174             .filter(PlayerGameStat.create_dt > cutoff)\
175             .order_by(expr.desc(func.sum(PlayerGameStat.alivetime)))\
176             .group_by(Player.nick)\
177             .group_by(Player.player_id)
178
179         if self.last:
180             top_players_q = top_players_q.offset(self.last)
181
182         if self.limit:
183             top_players_q = top_players_q.limit(self.limit)
184
185         top_players = top_players_q.all()
186
187         return top_players
188
189     def html(self):
190         """Returns the HTML-ready representation."""
191         TopPlayer = namedtuple("TopPlayer", ["rank", "player_id", "nick", "alivetime"])
192
193         top_players = [TopPlayer(tp.rank, tp.player_id, html_colors(tp.nick), tp.alivetime)
194                        for tp in self.top_players]
195
196         # build the query string
197         query = {}
198         if len(top_players) > 1:
199             query['last'] = top_players[-1].rank
200
201         return {
202             "map_id": self.map_id,
203             "top_players": top_players,
204             "lifetime": self.lifetime,
205             "last": query.get("last", None),
206             "query": query,
207         }
208
209     def json(self):
210         """For rendering this data using JSON."""
211         top_players = [{
212             "rank": ts.rank,
213             "player_id": ts.player_id,
214             "nick": ts.nick,
215             "time": ts.alivetime.total_seconds(),
216         } for ts in self.top_players]
217
218         return {
219             "map_id": self.map_id,
220             "top_players": top_players,
221         }
222
223
224 class MapTopServers(MapInfoBase):
225     """Returns the top servers by the number of times they've played a given map."""
226
227     def __init__(self, request, limit=INDEX_COUNT, last=None):
228         """Common parameter parsing."""
229         super(MapTopServers, self).__init__(request, limit, last)
230         self.top_servers = self.get_top_servers()
231
232     def get_top_servers(self):
233         """Top servers by the number of times they have played the map. Shared by all renderers."""
234         cutoff = self.now - timedelta(days=self.lifetime)
235
236         top_servers_q = DBSession.query(
237             fg.row_number().over(order_by=expr.desc(func.count(Game.game_id))).label("rank"),
238             Server.server_id, Server.name, func.count(Game.game_id).label("games"))\
239             .filter(Game.server_id == Server.server_id)\
240             .filter(Game.map_id == self.map_id)\
241             .filter(Game.create_dt > cutoff)\
242             .order_by(expr.desc(func.count(Game.game_id)))\
243             .group_by(Server.name)\
244             .group_by(Server.server_id)
245
246         if self.last:
247             top_servers_q = top_servers_q.offset(self.last)
248
249         if self.limit:
250             top_servers_q = top_servers_q.limit(self.limit)
251
252         top_servers = top_servers_q.all()
253
254         return top_servers
255
256     def html(self):
257         """Returns the HTML-ready representation."""
258         TopServer = namedtuple("TopServer", ["rank", "server_id", "server_name", "games"])
259
260         top_servers = [TopServer(ts.rank, ts.server_id, ts.name, ts.games)
261                        for ts in self.top_servers]
262
263         # build the query string
264         query = {}
265         if len(top_servers) > 1:
266             query['last'] = top_servers[-1].rank
267
268         return {
269             "map_id": self.map_id,
270             "top_servers": top_servers,
271             "lifetime": self.lifetime,
272             "last": query.get("last", None),
273             "query": query,
274         }
275
276     def json(self):
277         """For rendering this data using JSON."""
278         top_servers = [{
279             "rank": ts.rank,
280             "server_id": ts.server_id,
281             "server_name": ts.server_name,
282             "games": ts.games,
283         } for ts in self.top_servers]
284
285         return {
286             "map_id": self.map_id,
287             "top_servers": top_servers,
288         }
289
290
291 def _map_info_data(request):
292     map_id = int(request.matchdict['id'])
293
294     try:
295         leaderboard_lifetime = int(
296                 request.registry.settings['xonstat.leaderboard_lifetime'])
297     except:
298         leaderboard_lifetime = 30
299
300     leaderboard_count = 10
301     recent_games_count = 20
302
303     # captime tuples
304     Captime = namedtuple('Captime', ['player_id', 'nick_html_colors',
305         'fastest_cap', 'game_id'])
306
307     try:
308         gmap = DBSession.query(Map).filter_by(map_id=map_id).one()
309
310         # recent games played in descending order
311         rgs = recent_games_q(map_id=map_id).limit(recent_games_count).all()
312         recent_games = [RecentGame(row) for row in rgs]
313
314         # top players by score
315         top_scorers = DBSession.query(Player.player_id, Player.nick,
316                 func.sum(PlayerGameStat.score)).\
317                 filter(Player.player_id == PlayerGameStat.player_id).\
318                 filter(Game.game_id == PlayerGameStat.game_id).\
319                 filter(Game.map_id == map_id).\
320                 filter(Player.player_id > 2).\
321                 filter(PlayerGameStat.create_dt >
322                         (datetime.utcnow() - timedelta(days=leaderboard_lifetime))).\
323                 order_by(expr.desc(func.sum(PlayerGameStat.score))).\
324                 group_by(Player.nick).\
325                 group_by(Player.player_id).all()[0:leaderboard_count]
326
327         top_scorers = [(player_id, html_colors(nick), score) \
328                 for (player_id, nick, score) in top_scorers]
329
330         # top players by playing time
331         top_players = DBSession.query(Player.player_id, Player.nick,
332                 func.sum(PlayerGameStat.alivetime)).\
333                 filter(Player.player_id == PlayerGameStat.player_id).\
334                 filter(Game.game_id == PlayerGameStat.game_id).\
335                 filter(Game.map_id == map_id).\
336                 filter(Player.player_id > 2).\
337                 filter(PlayerGameStat.create_dt >
338                         (datetime.utcnow() - timedelta(days=leaderboard_lifetime))).\
339                 order_by(expr.desc(func.sum(PlayerGameStat.alivetime))).\
340                 group_by(Player.nick).\
341                 group_by(Player.player_id).all()[0:leaderboard_count]
342
343         top_players = [(player_id, html_colors(nick), score) \
344                 for (player_id, nick, score) in top_players]
345
346         # top servers using/playing this map
347         top_servers = DBSession.query(Server.server_id, Server.name,
348                 func.count(Game.game_id)).\
349                 filter(Game.server_id == Server.server_id).\
350                 filter(Game.map_id == map_id).\
351                 filter(Game.create_dt >
352                         (datetime.utcnow() - timedelta(days=leaderboard_lifetime))).\
353                 order_by(expr.desc(func.count(Game.game_id))).\
354                 group_by(Server.name).\
355                 group_by(Server.server_id).all()[0:leaderboard_count]
356
357         # TODO make this a configuration parameter to be set in the settings
358         # top captimes
359         captimes_raw = DBSession.query(Player.player_id, Player.nick,
360             PlayerCaptime.fastest_cap, PlayerCaptime.game_id).\
361                 filter(PlayerCaptime.map_id == map_id).\
362                 filter(Player.player_id == PlayerCaptime.player_id).\
363                 order_by(PlayerCaptime.fastest_cap).\
364                 limit(10).\
365                 all()
366
367         captimes = [Captime(c.player_id, html_colors(c.nick),
368             c.fastest_cap, c.game_id) for c in captimes_raw]
369
370     except Exception as e:
371         gmap = None
372     return {'gmap':gmap,
373             'recent_games':recent_games,
374             'top_scorers':top_scorers,
375             'top_players':top_players,
376             'top_servers':top_servers,
377             'captimes':captimes,
378             }
379
380
381 def map_info(request):
382     """
383     List the information stored about a given map.
384     """
385     mapinfo_data =  _map_info_data(request)
386
387     # FIXME: code clone, should get these from _map_info_data
388     leaderboard_count = 10
389     recent_games_count = 20
390
391     for i in range(leaderboard_count-len(mapinfo_data['top_scorers'])):
392         mapinfo_data['top_scorers'].append(('-', '-', '-'))
393
394     for i in range(leaderboard_count-len(mapinfo_data['top_players'])):
395         mapinfo_data['top_players'].append(('-', '-', '-'))
396
397     for i in range(leaderboard_count-len(mapinfo_data['top_servers'])):
398         mapinfo_data['top_servers'].append(('-', '-', '-'))
399
400     return mapinfo_data
401
402
403 def map_info_json(request):
404     """
405     List the information stored about a given map. JSON.
406     """
407     return [{'status':'not implemented'}]
408
409
410 def map_captimes_data(request):
411     map_id = int(request.matchdict['id'])
412
413     current_page = request.params.get('page', 1)
414
415     try:
416         mmap = DBSession.query(Map).filter_by(map_id=map_id).one()
417
418         mct_q = DBSession.query(PlayerCaptime.fastest_cap, PlayerCaptime.create_dt,
419                 PlayerCaptime.player_id, PlayerCaptime.game_id,
420                 Game.server_id, Server.name.label('server_name'),
421                 PlayerGameStat.nick.label('player_nick')).\
422                 filter(PlayerCaptime.map_id==map_id).\
423                 filter(PlayerCaptime.game_id==Game.game_id).\
424                 filter(PlayerCaptime.map_id==Map.map_id).\
425                 filter(Game.server_id==Server.server_id).\
426                 filter(PlayerCaptime.player_id==PlayerGameStat.player_id).\
427                 filter(PlayerCaptime.game_id==PlayerGameStat.game_id).\
428                 order_by(expr.asc(PlayerCaptime.fastest_cap))
429
430     except Exception as e:
431         raise HTTPNotFound
432
433     map_captimes = Page(mct_q, current_page, items_per_page=20, url=page_url)
434
435     map_captimes.items = [MapCapTime(row) for row in map_captimes.items]
436
437     return {
438             'map_id':map_id,
439             'map':mmap,
440             'captimes':map_captimes,
441         }
442
443 def map_captimes(request):
444     return map_captimes_data(request)
445
446 def map_captimes_json(request):
447     current_page = request.params.get('page', 1)
448     data = map_captimes_data(request)
449
450     return {
451             "map": data["map"].to_dict(),
452             "captimes": [e.to_dict() for e in data["captimes"].items],
453             "page": current_page,
454             }