2 import sqlalchemy.sql.functions as func
\r
3 import sqlalchemy.sql.expression as expr
\r
4 from datetime import datetime, timedelta
\r
5 from pyramid.response import Response
\r
6 from sqlalchemy import desc
\r
7 from webhelpers.paginate import Page, PageURL
\r
8 from xonstat.models import *
\r
9 from xonstat.util import page_url
\r
11 log = logging.getLogger(__name__)
\r
13 def map_index(request):
\r
15 Provides a list of all the current maps.
\r
17 if 'page' in request.matchdict:
\r
18 current_page = request.matchdict['page']
\r
23 map_q = DBSession.query(Map).\
\r
24 order_by(Map.map_id.desc())
\r
26 maps = Page(map_q, current_page, url=page_url)
\r
29 except Exception as e:
\r
32 return {'maps':maps, }
\r
35 def map_info(request):
\r
37 List the information stored about a given map.
\r
39 map_id = request.matchdict['id']
\r
42 leaderboard_lifetime = int(
\r
43 request.registry.settings['xonstat.leaderboard_lifetime'])
\r
45 leaderboard_lifetime = 30
\r
47 leaderboard_count = 10
\r
48 recent_games_count = 20
\r
51 gmap = DBSession.query(Map).filter_by(map_id=map_id).one()
\r
53 # recent games on this map
\r
54 recent_games = DBSession.query(Game, Server, Map, PlayerGameStat).\
\r
55 filter(Game.server_id==Server.server_id).\
\r
56 filter(Game.map_id==Map.map_id).\
\r
57 filter(Game.map_id==map_id).\
\r
58 filter(PlayerGameStat.game_id==Game.game_id).\
\r
59 filter(PlayerGameStat.rank==1).\
\r
60 order_by(expr.desc(Game.start_dt)).all()[0:recent_games_count]
\r
62 for i in range(recent_games_count-len(recent_games)):
\r
63 recent_games.append(('-', '-', '-', '-'))
\r
66 # top players by score
\r
67 top_scorers = DBSession.query(Player.player_id, Player.nick,
\r
68 func.sum(PlayerGameStat.score)).\
\r
69 filter(Player.player_id == PlayerGameStat.player_id).\
\r
70 filter(Game.game_id == PlayerGameStat.game_id).\
\r
71 filter(Game.map_id == map_id).\
\r
72 filter(Player.player_id > 2).\
\r
73 filter(PlayerGameStat.create_dt >
\r
74 (datetime.utcnow() - timedelta(days=leaderboard_lifetime))).\
\r
75 order_by(expr.desc(func.sum(PlayerGameStat.score))).\
\r
76 group_by(Player.nick).\
\r
77 group_by(Player.player_id).all()[0:10]
\r
79 top_scorers = [(player_id, html_colors(nick), score) \
\r
80 for (player_id, nick, score) in top_scorers]
\r
82 for i in range(leaderboard_count-len(top_scorers)):
\r
83 top_scorers.append(('-', '-', '-'))
\r
85 # top players by playing time
\r
86 top_players = DBSession.query(Player.player_id, Player.nick,
\r
87 func.sum(PlayerGameStat.alivetime)).\
\r
88 filter(Player.player_id == PlayerGameStat.player_id).\
\r
89 filter(Game.game_id == PlayerGameStat.game_id).\
\r
90 filter(Game.map_id == map_id).\
\r
91 filter(Player.player_id > 2).\
\r
92 filter(PlayerGameStat.create_dt >
\r
93 (datetime.utcnow() - timedelta(days=leaderboard_lifetime))).\
\r
94 order_by(expr.desc(func.sum(PlayerGameStat.alivetime))).\
\r
95 group_by(Player.nick).\
\r
96 group_by(Player.player_id).all()[0:10]
\r
98 top_players = [(player_id, html_colors(nick), score) \
\r
99 for (player_id, nick, score) in top_players]
\r
101 for i in range(leaderboard_count-len(top_players)):
\r
102 top_players.append(('-', '-', '-'))
\r
104 # top servers using/playing this map
\r
105 top_servers = DBSession.query(Server.server_id, Server.name,
\r
106 func.count(Game.game_id)).\
\r
107 filter(Game.server_id == Server.server_id).\
\r
108 filter(Game.map_id == map_id).\
\r
109 filter(Game.create_dt >
\r
110 (datetime.utcnow() - timedelta(days=leaderboard_lifetime))).\
\r
111 order_by(expr.desc(func.count(Game.game_id))).\
\r
112 group_by(Server.name).\
\r
113 group_by(Server.server_id).all()[0:10]
\r
115 for i in range(leaderboard_count-len(top_servers)):
\r
116 top_servers.append(('-', '-', '-'))
\r
118 except Exception as e:
\r
120 return {'gmap':gmap,
\r
121 'recent_games':recent_games,
\r
122 'top_scorers':top_scorers,
\r
123 'top_players':top_players,
\r
124 'top_servers':top_servers,
\r