def __init__(self, row): self.fastest_cap = row.fastest_cap self.create_dt = row.create_dt self.create_dt_epoch = timegm(row.create_dt.timetuple()) self.create_dt_fuzzy = pretty_date(row.create_dt) self.player_id = row.player_id self.player_nick = row.player_nick self.player_nick_stripped = strip_colors(row.player_nick) self.player_nick_html = html_colors(row.player_nick) self.game_id = row.game_id self.server_id = row.server_id self.server_name = row.server_name
def html(self): """Returns an HTML-ready representation.""" TopScorer = namedtuple("TopScorer", ["rank", "player_id", "nick", "total_score"]) top_scorers = [TopScorer(ts.rank, ts.player_id, html_colors(ts.nick), ts.total_score) for ts in self.top_scorers] # build the query string query = {} if len(top_scorers) > 1: query['last'] = top_scorers[-1].rank return { "map_id": self.map_id, "top_scorers": top_scorers, "lifetime": self.lifetime, "query": query, }
def html(self): """Returns the HTML-ready representation.""" TopPlayer = namedtuple("TopPlayer", ["rank", "player_id", "nick", "alivetime"]) top_players = [TopPlayer(tp.rank, tp.player_id, html_colors(tp.nick), tp.alivetime) for tp in self.top_players] # build the query string query = {} if len(top_players) > 1: query['last'] = top_players[-1].rank return { "server_id": self.server_id, "top_players": top_players, "lifetime": self.lifetime, "query": query, }
def html(self): """Returns the HTML-ready representation.""" TopPlayer = namedtuple("TopPlayer", ["rank", "player_id", "nick", "alivetime"]) top_players = [TopPlayer(tp.rank, tp.player_id, html_colors(tp.nick), tp.alivetime) for tp in self.top_players] # build the query string query = {} if len(top_players) > 1: query['last'] = top_players[-1].rank return { "map_id": self.map_id, "top_players": top_players, "lifetime": self.lifetime, "last": query.get("last", None), "query": query, }
def __init__(self, row): self.game_id = row.game_id self.game_type_cd = row.game_type_cd self.game_type_descr = row.game_type_descr self.winner = row.winner self.start_dt = row.start_dt self.fuzzy_date = pretty_date(row.start_dt) self.epoch = timegm(row.start_dt.timetuple()) self.server_id = row.server_id self.server_name = row.server_name self.map_id = row.map_id self.map_name = row.map_name self.player_id = row.player_id self.nick = row.nick self.nick_html_colors = html_colors(row.nick) self.rank = row.rank self.team = row.team try: self.elo_delta = row.elo_delta except: self.elo_delta = None
def nick_html_colors(self): return html_colors(self.nick)
def _server_info_data(request): server_id = request.matchdict['id'] try: leaderboard_lifetime = int( request.registry.settings['xonstat.leaderboard_lifetime']) except: leaderboard_lifetime = 30 leaderboard_count = 10 recent_games_count = 20 try: server = DBSession.query(Server).filter_by(server_id=server_id).one() # top maps by total times played top_maps = DBSession.query(Game.map_id, Map.name, func.count()).\ filter(Map.map_id==Game.map_id).\ filter(Game.server_id==server.server_id).\ filter(Game.create_dt > (datetime.utcnow() - timedelta(days=leaderboard_lifetime))).\ order_by(expr.desc(func.count())).\ group_by(Game.map_id).\ group_by(Map.name).all()[0:leaderboard_count] # top players by score top_scorers = DBSession.query(Player.player_id, Player.nick, func.sum(PlayerGameStat.score)).\ filter(Player.player_id == PlayerGameStat.player_id).\ filter(Game.game_id == PlayerGameStat.game_id).\ filter(Game.server_id == server.server_id).\ filter(Player.player_id > 2).\ filter(PlayerGameStat.create_dt > (datetime.utcnow() - timedelta(days=leaderboard_lifetime))).\ order_by(expr.desc(func.sum(PlayerGameStat.score))).\ group_by(Player.nick).\ group_by(Player.player_id).all()[0:leaderboard_count] top_scorers = [(player_id, html_colors(nick), score) \ for (player_id, nick, score) in top_scorers] # top players by playing time top_players = DBSession.query(Player.player_id, Player.nick, func.sum(PlayerGameStat.alivetime)).\ filter(Player.player_id == PlayerGameStat.player_id).\ filter(Game.game_id == PlayerGameStat.game_id).\ filter(Game.server_id == server.server_id).\ filter(Player.player_id > 2).\ filter(PlayerGameStat.create_dt > (datetime.utcnow() - timedelta(days=leaderboard_lifetime))).\ order_by(expr.desc(func.sum(PlayerGameStat.alivetime))).\ group_by(Player.nick).\ group_by(Player.player_id).all()[0:leaderboard_count] top_players = [(player_id, html_colors(nick), score) \ for (player_id, nick, score) in top_players] # recent games played in descending order recent_games = DBSession.query(Game, Server, Map, PlayerGameStat).\ filter(Game.server_id==Server.server_id).\ filter(Game.map_id==Map.map_id).\ filter(PlayerGameStat.game_id==Game.game_id).\ filter(PlayerGameStat.rank==1).\ filter(Server.server_id==server.server_id).\ order_by(expr.desc(Game.start_dt)).all()[0:recent_games_count] except Exception as e: server = None recent_games = None top_players = None raise e return { 'server': server, 'recent_games': recent_games, 'top_players': top_players, 'top_scorers': top_scorers, 'top_maps': top_maps, }
def server_info(request): """ List the stored information about a given server. """ server_id = request.matchdict['id'] try: leaderboard_lifetime = int( request.registry.settings['xonstat.leaderboard_lifetime']) except: leaderboard_lifetime = 30 leaderboard_count = 10 recent_games_count = 20 try: server = DBSession.query(Server).filter_by(server_id=server_id).one() # top maps by total times played top_maps = DBSession.query(Game.map_id, Map.name, func.count()).\ filter(Map.map_id==Game.map_id).\ filter(Game.server_id==server.server_id).\ filter(Game.create_dt > (datetime.utcnow() - timedelta(days=leaderboard_lifetime))).\ order_by(expr.desc(func.count())).\ group_by(Game.map_id).\ group_by(Map.name).all()[0:10] for i in range(leaderboard_count-len(top_maps)): top_maps.append(('-', '-', '-')) # top players by score top_scorers = DBSession.query(Player.player_id, Player.nick, func.sum(PlayerGameStat.score)).\ filter(Player.player_id == PlayerGameStat.player_id).\ filter(Game.game_id == PlayerGameStat.game_id).\ filter(Game.server_id == server.server_id).\ filter(Player.player_id > 2).\ filter(PlayerGameStat.create_dt > (datetime.utcnow() - timedelta(days=leaderboard_lifetime))).\ order_by(expr.desc(func.sum(PlayerGameStat.score))).\ group_by(Player.nick).\ group_by(Player.player_id).all()[0:10] top_scorers = [(player_id, html_colors(nick), score) \ for (player_id, nick, score) in top_scorers] for i in range(leaderboard_count-len(top_scorers)): top_scorers.append(('-', '-', '-')) # top players by playing time top_players = DBSession.query(Player.player_id, Player.nick, func.sum(PlayerGameStat.alivetime)).\ filter(Player.player_id == PlayerGameStat.player_id).\ filter(Game.game_id == PlayerGameStat.game_id).\ filter(Game.server_id == server.server_id).\ filter(Player.player_id > 2).\ filter(PlayerGameStat.create_dt > (datetime.utcnow() - timedelta(days=leaderboard_lifetime))).\ order_by(expr.desc(func.sum(PlayerGameStat.alivetime))).\ group_by(Player.nick).\ group_by(Player.player_id).all()[0:10] top_players = [(player_id, html_colors(nick), score) \ for (player_id, nick, score) in top_players] for i in range(leaderboard_count-len(top_players)): top_players.append(('-', '-', '-')) # recent games played in descending order recent_games = DBSession.query(Game, Server, Map, PlayerGameStat).\ filter(Game.server_id==Server.server_id).\ filter(Game.map_id==Map.map_id).\ filter(PlayerGameStat.game_id==Game.game_id).\ filter(PlayerGameStat.rank==1).\ filter(Server.server_id==server.server_id).\ order_by(expr.desc(Game.start_dt)).all()[0:recent_games_count] for i in range(recent_games_count-len(recent_games)): recent_games.append(('-', '-', '-', '-')) except Exception as e: server = None recent_games = None top_players = None raise e return {'server':server, 'recent_games':recent_games, 'top_players': top_players, 'top_scorers': top_scorers, 'top_maps': top_maps, }
def _map_info_data(request): map_id = request.matchdict['id'] try: leaderboard_lifetime = int( request.registry.settings['xonstat.leaderboard_lifetime']) except: leaderboard_lifetime = 30 leaderboard_count = 10 recent_games_count = 20 # captime tuples Captime = namedtuple( 'Captime', ['player_id', 'nick_html_colors', 'fastest_cap', 'game_id']) try: if re.match(r"\d+$", map_id): map_id = int(map_id) gmap = DBSession.query(Map).filter_by(map_id=map_id).one() else: gmap = DBSession.query(Map).filter_by(name=map_id).one() map_id = gmap.map_id # recent games played in descending order rgs = recent_games_q(map_id=map_id).limit(recent_games_count).all() recent_games = [RecentGame(row) for row in rgs] # top players by score top_scorers = DBSession.query(Player.player_id, Player.nick, func.sum(PlayerGameStat.score)).\ filter(Player.player_id == PlayerGameStat.player_id).\ filter(Game.game_id == PlayerGameStat.game_id).\ filter(Game.map_id == map_id).\ filter(Player.player_id > 2).\ filter(PlayerGameStat.create_dt > (datetime.utcnow() - timedelta(days=leaderboard_lifetime))).\ order_by(expr.desc(func.sum(PlayerGameStat.score))).\ group_by(Player.nick).\ group_by(Player.player_id).all()[0:leaderboard_count] top_scorers = [(player_id, html_colors(nick), score) \ for (player_id, nick, score) in top_scorers] # top players by playing time top_players = DBSession.query(Player.player_id, Player.nick, func.sum(PlayerGameStat.alivetime)).\ filter(Player.player_id == PlayerGameStat.player_id).\ filter(Game.game_id == PlayerGameStat.game_id).\ filter(Game.map_id == map_id).\ filter(Player.player_id > 2).\ filter(PlayerGameStat.create_dt > (datetime.utcnow() - timedelta(days=leaderboard_lifetime))).\ order_by(expr.desc(func.sum(PlayerGameStat.alivetime))).\ group_by(Player.nick).\ group_by(Player.player_id).all()[0:leaderboard_count] top_players = [(player_id, html_colors(nick), score) \ for (player_id, nick, score) in top_players] # top servers using/playing this map top_servers = DBSession.query(Server.server_id, Server.name, func.count(Game.game_id)).\ filter(Game.server_id == Server.server_id).\ filter(Game.map_id == map_id).\ filter(Game.create_dt > (datetime.utcnow() - timedelta(days=leaderboard_lifetime))).\ order_by(expr.desc(func.count(Game.game_id))).\ group_by(Server.name).\ group_by(Server.server_id).all()[0:leaderboard_count] # top captimes captimes_raw = DBSession.query(Player.player_id, Player.nick, PlayerCaptime.fastest_cap, PlayerCaptime.game_id).\ filter(PlayerCaptime.map_id == map_id).\ filter(Player.player_id == PlayerCaptime.player_id).\ order_by(PlayerCaptime.fastest_cap).\ limit(25).\ all() captimes = [ Captime(c.player_id, html_colors(c.nick), c.fastest_cap, c.game_id) for c in captimes_raw ] except Exception as e: gmap = None return { 'gmap': gmap, 'recent_games': recent_games, 'top_scorers': top_scorers, 'top_players': top_players, 'top_servers': top_servers, 'captimes': captimes, }
def map_captimes_data(request): map_id = int(request.matchdict['id']) if request.params.has_key('page'): current_page = request.params['page'] else: current_page = 1 MapCaptimes = namedtuple('PlayerCaptimes', [ 'fastest_cap', 'create_dt', 'create_dt_epoch', 'create_dt_fuzzy', 'player_id', 'player_nick', 'player_nick_stripped', 'player_nick_html', 'game_id', 'server_id', 'server_name' ]) mmap = DBSession.query(Map).filter_by(map_id=map_id).one() #mct_q = DBSession.query('fastest_cap', 'create_dt', 'player_id', 'game_id', # 'server_id', 'server_name', 'player_nick').\ # from_statement( # "SELECT ct.fastest_cap, " # "ct.create_dt, " # "ct.player_id, " # "ct.game_id, " # "g.server_id, " # "s.name server_name, " # "pgs.nick player_nick " # "FROM player_map_captimes ct, " # "games g, " # "maps m, " # "servers s, " # "player_game_stats pgs " # "WHERE ct.map_id = :map_id " # "AND g.game_id = ct.game_id " # "AND g.server_id = s.server_id " # "AND m.map_id = ct.map_id " # "AND pgs.player_id = ct.player_id " # "AND pgs.game_id = ct.game_id " # "ORDER BY ct.fastest_cap " # "LIMIT 25" # ).params(map_id=map_id) #try: if True: mct_q = DBSession.query(PlayerCaptime.fastest_cap, PlayerCaptime.create_dt, PlayerCaptime.player_id, PlayerCaptime.game_id, Game.server_id, Server.name.label('server_name'), PlayerGameStat.nick.label('player_nick')).\ filter(PlayerCaptime.map_id==map_id).\ filter(PlayerCaptime.game_id==Game.game_id).\ filter(PlayerCaptime.map_id==Map.map_id).\ filter(Game.server_id==Server.server_id).\ filter(PlayerCaptime.player_id==PlayerGameStat.player_id).\ filter(PlayerCaptime.game_id==PlayerGameStat.game_id).\ order_by(expr.asc(PlayerCaptime.fastest_cap)) map_captimes = Page(mct_q, current_page, items_per_page=20, url=page_url) map_captimes.items = [ MapCaptimes( fastest_cap=row.fastest_cap, create_dt=row.create_dt, create_dt_epoch=timegm(row.create_dt.timetuple()), create_dt_fuzzy=pretty_date(row.create_dt), player_id=row.player_id, player_nick=row.player_nick, player_nick_stripped=strip_colors(row.player_nick), player_nick_html=html_colors(row.player_nick), game_id=row.game_id, server_id=row.server_id, server_name=row.server_name, ) for row in map_captimes.items ] #except Exception as e: else: map = None map_captimes = None return { 'map_id': map_id, 'map': mmap, 'captimes': map_captimes, }
def nick_html_colors(self): if self.nick is None: return "Anonymous Player" else: return html_colors(self.nick)
def nick_html_colors(self, limit=None): if self.nick is None: return "Anonymous Player" else: return html_colors(self.nick, limit)
def map_captimes_data(request): map_id = int(request.matchdict['id']) if request.params.has_key('page'): current_page = request.params['page'] else: current_page = 1 MapCaptimes = namedtuple('PlayerCaptimes', ['fastest_cap', 'create_dt', 'create_dt_epoch', 'create_dt_fuzzy', 'player_id', 'player_nick', 'player_nick_stripped', 'player_nick_html', 'game_id', 'server_id', 'server_name']) mmap = DBSession.query(Map).filter_by(map_id=map_id).one() #mct_q = DBSession.query('fastest_cap', 'create_dt', 'player_id', 'game_id', # 'server_id', 'server_name', 'player_nick').\ # from_statement( # "SELECT ct.fastest_cap, " # "ct.create_dt, " # "ct.player_id, " # "ct.game_id, " # "g.server_id, " # "s.name server_name, " # "pgs.nick player_nick " # "FROM player_map_captimes ct, " # "games g, " # "maps m, " # "servers s, " # "player_game_stats pgs " # "WHERE ct.map_id = :map_id " # "AND g.game_id = ct.game_id " # "AND g.server_id = s.server_id " # "AND m.map_id = ct.map_id " # "AND pgs.player_id = ct.player_id " # "AND pgs.game_id = ct.game_id " # "ORDER BY ct.fastest_cap " # "LIMIT 25" # ).params(map_id=map_id) #try: if True: mct_q = DBSession.query(PlayerCaptime.fastest_cap, PlayerCaptime.create_dt, PlayerCaptime.player_id, PlayerCaptime.game_id, Game.server_id, Server.name.label('server_name'), PlayerGameStat.nick.label('player_nick')).\ filter(PlayerCaptime.map_id==map_id).\ filter(PlayerCaptime.game_id==Game.game_id).\ filter(PlayerCaptime.map_id==Map.map_id).\ filter(Game.server_id==Server.server_id).\ filter(PlayerCaptime.player_id==PlayerGameStat.player_id).\ filter(PlayerCaptime.game_id==PlayerGameStat.game_id).\ order_by(expr.asc(PlayerCaptime.fastest_cap)) map_captimes = Page(mct_q, current_page, items_per_page=20, url=page_url) map_captimes.items = [MapCaptimes( fastest_cap=row.fastest_cap, create_dt=row.create_dt, create_dt_epoch=timegm(row.create_dt.timetuple()), create_dt_fuzzy=pretty_date(row.create_dt), player_id=row.player_id, player_nick=row.player_nick, player_nick_stripped=strip_colors(row.player_nick), player_nick_html=html_colors(row.player_nick), game_id=row.game_id, server_id=row.server_id, server_name=row.server_name, ) for row in map_captimes.items] #except Exception as e: else: map = None map_captimes = None return { 'map_id':map_id, 'map':mmap, 'captimes':map_captimes, }
def _map_info_data(request): map_id = int(request.matchdict["id"]) try: leaderboard_lifetime = int(request.registry.settings["xonstat.leaderboard_lifetime"]) except: leaderboard_lifetime = 30 leaderboard_count = 10 recent_games_count = 20 # captime tuples Captime = namedtuple("Captime", ["player_id", "nick_html_colors", "fastest_cap", "game_id"]) try: gmap = DBSession.query(Map).filter_by(map_id=map_id).one() # recent games played in descending order rgs = recent_games_q(map_id=map_id).limit(recent_games_count).all() recent_games = [RecentGame(row) for row in rgs] # top players by score top_scorers = ( DBSession.query(Player.player_id, Player.nick, func.sum(PlayerGameStat.score)) .filter(Player.player_id == PlayerGameStat.player_id) .filter(Game.game_id == PlayerGameStat.game_id) .filter(Game.map_id == map_id) .filter(Player.player_id > 2) .filter(PlayerGameStat.create_dt > (datetime.utcnow() - timedelta(days=leaderboard_lifetime))) .order_by(expr.desc(func.sum(PlayerGameStat.score))) .group_by(Player.nick) .group_by(Player.player_id) .all()[0:leaderboard_count] ) top_scorers = [(player_id, html_colors(nick), score) for (player_id, nick, score) in top_scorers] # top players by playing time top_players = ( DBSession.query(Player.player_id, Player.nick, func.sum(PlayerGameStat.alivetime)) .filter(Player.player_id == PlayerGameStat.player_id) .filter(Game.game_id == PlayerGameStat.game_id) .filter(Game.map_id == map_id) .filter(Player.player_id > 2) .filter(PlayerGameStat.create_dt > (datetime.utcnow() - timedelta(days=leaderboard_lifetime))) .order_by(expr.desc(func.sum(PlayerGameStat.alivetime))) .group_by(Player.nick) .group_by(Player.player_id) .all()[0:leaderboard_count] ) top_players = [(player_id, html_colors(nick), score) for (player_id, nick, score) in top_players] # top servers using/playing this map top_servers = ( DBSession.query(Server.server_id, Server.name, func.count(Game.game_id)) .filter(Game.server_id == Server.server_id) .filter(Game.map_id == map_id) .filter(Game.create_dt > (datetime.utcnow() - timedelta(days=leaderboard_lifetime))) .order_by(expr.desc(func.count(Game.game_id))) .group_by(Server.name) .group_by(Server.server_id) .all()[0:leaderboard_count] ) # top captimes captimes_raw = ( DBSession.query(Player.player_id, Player.nick, PlayerCaptime.fastest_cap, PlayerCaptime.game_id) .filter(PlayerCaptime.map_id == map_id) .filter(Player.player_id == PlayerCaptime.player_id) .order_by(PlayerCaptime.fastest_cap) .limit(25) .all() ) captimes = [Captime(c.player_id, html_colors(c.nick), c.fastest_cap, c.game_id) for c in captimes_raw] except Exception as e: gmap = None return { "gmap": gmap, "recent_games": recent_games, "top_scorers": top_scorers, "top_players": top_players, "top_servers": top_servers, "captimes": captimes, }
def _map_info_data(request): map_id = request.matchdict['id'] try: leaderboard_lifetime = int( request.registry.settings['xonstat.leaderboard_lifetime']) except: leaderboard_lifetime = 30 leaderboard_count = 10 recent_games_count = 20 # captime tuples Captime = namedtuple('Captime', ['player_id', 'nick_html_colors', 'fastest_cap', 'game_id']) try: if re.match(r"\d+$", map_id): map_id = int(map_id) gmap = DBSession.query(Map).filter_by(map_id=map_id).one() else: gmap = DBSession.query(Map).filter_by(name=map_id).one() map_id = gmap.map_id here = os.path.dirname(__file__) if os.path.exists(here + "/../static/images/levelshots/" + gmap.name + ".jpg"): gmap.image = "/static/images/levelshots/" + gmap.name + ".jpg" else: gmap.image = "" # recent games played in descending order rgs = recent_games_q(map_id=map_id, limit=recent_games_count).all() recent_games = [RecentGame(row) for row in rgs] # top players by score top_scorers = DBSession.query(Player.player_id, Player.nick, func.sum(PlayerGameStat.score)).\ filter(Player.player_id == PlayerGameStat.player_id).\ filter(Game.game_id == PlayerGameStat.game_id).\ filter(Game.map_id == map_id).\ filter(Player.player_id > 2).\ filter(PlayerGameStat.create_dt > (datetime.utcnow() - timedelta(days=leaderboard_lifetime))).\ order_by(expr.desc(func.sum(PlayerGameStat.score))).\ group_by(Player.nick).\ group_by(Player.player_id).all()[0:leaderboard_count] top_scorers = [(player_id, html_colors(nick), score) \ for (player_id, nick, score) in top_scorers] # top players by playing time top_players = DBSession.query(Player.player_id, Player.nick, func.sum(PlayerGameStat.alivetime)).\ filter(Player.player_id == PlayerGameStat.player_id).\ filter(Game.game_id == PlayerGameStat.game_id).\ filter(Game.map_id == map_id).\ filter(Player.player_id > 2).\ filter(PlayerGameStat.create_dt > (datetime.utcnow() - timedelta(days=leaderboard_lifetime))).\ order_by(expr.desc(func.sum(PlayerGameStat.alivetime))).\ group_by(Player.nick).\ group_by(Player.player_id).all()[0:leaderboard_count] top_players = [(player_id, html_colors(nick), score) \ for (player_id, nick, score) in top_players] # top servers using/playing this map top_servers = DBSession.query(Server.server_id, Server.name, func.count(Game.game_id)).\ filter(Game.server_id == Server.server_id).\ filter(Game.map_id == map_id).\ filter(Game.create_dt > (datetime.utcnow() - timedelta(days=leaderboard_lifetime))).\ order_by(expr.desc(func.count(Game.game_id))).\ group_by(Server.name).\ group_by(Server.server_id).all()[0:leaderboard_count] # top captimes captimes_raw = DBSession.query(Player.player_id, Player.nick, PlayerCaptime.fastest_cap, PlayerCaptime.game_id).\ filter(PlayerCaptime.map_id == map_id).\ filter(Player.player_id == PlayerCaptime.player_id).\ order_by(PlayerCaptime.fastest_cap).\ limit(25).\ all() captimes = [Captime(c.player_id, html_colors(c.nick), c.fastest_cap, c.game_id) for c in captimes_raw] except Exception as e: gmap = None return {'gmap':gmap, 'recent_games':recent_games, 'top_scorers':top_scorers, 'top_players':top_players, 'top_servers':top_servers, 'captimes':captimes, }
def _map_info_data(request): map_id = int(request.matchdict['id']) try: leaderboard_lifetime = int( request.registry.settings['xonstat.leaderboard_lifetime']) except: leaderboard_lifetime = 30 leaderboard_count = 10 recent_games_count = 20 # captime tuples Captime = namedtuple('Captime', ['player_id', 'nick_html_colors', 'fastest_cap', 'game_id']) try: gmap = DBSession.query(Map).filter_by(map_id=map_id).one() # recent games played in descending order rgs = recent_games_q(map_id=map_id).limit(recent_games_count).all() recent_games = [RecentGame(row) for row in rgs] # top players by score top_scorers = DBSession.query(Player.player_id, Player.nick, func.sum(PlayerGameStat.score)).\ filter(Player.player_id == PlayerGameStat.player_id).\ filter(Game.game_id == PlayerGameStat.game_id).\ filter(Game.map_id == map_id).\ filter(Player.player_id > 2).\ filter(PlayerGameStat.create_dt > (datetime.utcnow() - timedelta(days=leaderboard_lifetime))).\ order_by(expr.desc(func.sum(PlayerGameStat.score))).\ group_by(Player.nick).\ group_by(Player.player_id).all()[0:leaderboard_count] top_scorers = [(player_id, html_colors(nick), score) \ for (player_id, nick, score) in top_scorers] # top players by playing time top_players = DBSession.query(Player.player_id, Player.nick, func.sum(PlayerGameStat.alivetime)).\ filter(Player.player_id == PlayerGameStat.player_id).\ filter(Game.game_id == PlayerGameStat.game_id).\ filter(Game.map_id == map_id).\ filter(Player.player_id > 2).\ filter(PlayerGameStat.create_dt > (datetime.utcnow() - timedelta(days=leaderboard_lifetime))).\ order_by(expr.desc(func.sum(PlayerGameStat.alivetime))).\ group_by(Player.nick).\ group_by(Player.player_id).all()[0:leaderboard_count] top_players = [(player_id, html_colors(nick), score) \ for (player_id, nick, score) in top_players] # top servers using/playing this map top_servers = DBSession.query(Server.server_id, Server.name, func.count(Game.game_id)).\ filter(Game.server_id == Server.server_id).\ filter(Game.map_id == map_id).\ filter(Game.create_dt > (datetime.utcnow() - timedelta(days=leaderboard_lifetime))).\ order_by(expr.desc(func.count(Game.game_id))).\ group_by(Server.name).\ group_by(Server.server_id).all()[0:leaderboard_count] # TODO make this a configuration parameter to be set in the settings # top captimes captimes_raw = DBSession.query(Player.player_id, Player.nick, PlayerCaptime.fastest_cap, PlayerCaptime.game_id).\ filter(PlayerCaptime.map_id == map_id).\ filter(Player.player_id == PlayerCaptime.player_id).\ order_by(PlayerCaptime.fastest_cap).\ limit(10).\ all() captimes = [Captime(c.player_id, html_colors(c.nick), c.fastest_cap, c.game_id) for c in captimes_raw] except Exception as e: gmap = None return {'gmap':gmap, 'recent_games':recent_games, 'top_scorers':top_scorers, 'top_players':top_players, 'top_servers':top_servers, 'captimes':captimes, }
def _server_info_data(request): server_id = request.matchdict['id'] try: leaderboard_lifetime = int( request.registry.settings['xonstat.leaderboard_lifetime']) except: leaderboard_lifetime = 30 leaderboard_count = 10 recent_games_count = 20 try: server = DBSession.query(Server).filter_by(server_id=server_id).one() # top maps by total times played top_maps = DBSession.query(Game.map_id, Map.name, func.count()).\ filter(Map.map_id==Game.map_id).\ filter(Game.server_id==server.server_id).\ filter(Game.create_dt > (datetime.utcnow() - timedelta(days=leaderboard_lifetime))).\ order_by(expr.desc(func.count())).\ group_by(Game.map_id).\ group_by(Map.name).all()[0:leaderboard_count] # top players by score top_scorers = DBSession.query(Player.player_id, Player.nick, func.sum(PlayerGameStat.score)).\ filter(Player.player_id == PlayerGameStat.player_id).\ filter(Game.game_id == PlayerGameStat.game_id).\ filter(Game.server_id == server.server_id).\ filter(Player.player_id > 2).\ filter(PlayerGameStat.create_dt > (datetime.utcnow() - timedelta(days=leaderboard_lifetime))).\ order_by(expr.desc(func.sum(PlayerGameStat.score))).\ group_by(Player.nick).\ group_by(Player.player_id).all()[0:leaderboard_count] top_scorers = [(player_id, html_colors(nick), score) \ for (player_id, nick, score) in top_scorers] # top players by playing time top_players = DBSession.query(Player.player_id, Player.nick, func.sum(PlayerGameStat.alivetime)).\ filter(Player.player_id == PlayerGameStat.player_id).\ filter(Game.game_id == PlayerGameStat.game_id).\ filter(Game.server_id == server.server_id).\ filter(Player.player_id > 2).\ filter(PlayerGameStat.create_dt > (datetime.utcnow() - timedelta(days=leaderboard_lifetime))).\ order_by(expr.desc(func.sum(PlayerGameStat.alivetime))).\ group_by(Player.nick).\ group_by(Player.player_id).all()[0:leaderboard_count] top_players = [(player_id, html_colors(nick), score) \ for (player_id, nick, score) in top_players] # recent games played in descending order rgs = recent_games_q(server_id=server_id).limit(recent_games_count).all() recent_games = [RecentGame(row) for row in rgs] except Exception as e: server = None recent_games = None top_players = None raise e return {'server':server, 'recent_games':recent_games, 'top_players': top_players, 'top_scorers': top_scorers, 'top_maps': top_maps, }