2 import sqlalchemy.sql.functions as func
3 import sqlalchemy.sql.expression as expr
4 from datetime import datetime, timedelta
5 from pyramid.config import get_current_registry
6 from pyramid.response import Response
7 from xonstat.models import *
8 from xonstat.util import *
10 log = logging.getLogger(__name__)
12 def main_index(request):
13 settings = get_current_registry().settings
15 leaderboard_lifetime = int(
16 settings['xonstat.leaderboard_lifetime'])
18 leaderboard_lifetime = 30
20 leaderboard_count = 10
21 recent_games_count = 32
23 # top players by score
24 top_players = DBSession.query(Player.player_id, Player.nick,
25 func.sum(PlayerGameStat.score)).\
26 filter(Player.player_id == PlayerGameStat.player_id).\
27 filter(Player.player_id > 2).\
28 filter(PlayerGameStat.create_dt >
29 (datetime.now() - timedelta(days=leaderboard_lifetime))).\
30 order_by(expr.desc(func.sum(PlayerGameStat.score))).\
31 group_by(Player.nick).\
32 group_by(Player.player_id).all()[0:10]
34 top_players = [(player_id, html_colors(nick), score) \
35 for (player_id, nick, score) in top_players]
37 for i in range(leaderboard_count-len(top_players)):
38 top_players.append(('-', '-', '-'))
40 # top servers by number of total players played
41 top_servers = DBSession.query(Server.server_id, Server.name,
43 filter(Game.server_id==Server.server_id).\
44 filter(Game.create_dt >
45 (datetime.now() - timedelta(days=leaderboard_lifetime))).\
46 order_by(expr.desc(func.count(Game.game_id))).\
47 group_by(Server.server_id).\
48 group_by(Server.name).all()[0:10]
50 for i in range(leaderboard_count-len(top_servers)):
51 top_servers.append(('-', '-', '-'))
53 # top maps by total times played
54 top_maps = DBSession.query(Game.map_id, Map.name,
56 filter(Map.map_id==Game.map_id).\
57 filter(Game.create_dt >
58 (datetime.now() - timedelta(days=leaderboard_lifetime))).\
59 order_by(expr.desc(func.count())).\
60 group_by(Game.map_id).\
61 group_by(Map.name).all()[0:10]
63 for i in range(leaderboard_count-len(top_maps)):
64 top_maps.append(('-', '-', '-'))
66 recent_games = DBSession.query(Game, Server, Map).\
67 filter(Game.server_id==Server.server_id).\
68 filter(Game.map_id==Map.map_id).\
69 order_by(expr.desc(Game.start_dt)).all()[0:recent_games_count]
71 for i in range(recent_games_count-len(recent_games)):
72 recent_games.append(('-', '-', '-'))
74 return {'top_players':top_players,
75 'top_servers':top_servers,
77 'recent_games':recent_games,