import logging
-import sqlalchemy.sql.functions as func
-import sqlalchemy.sql.expression as expr
-from beaker.cache import cache_regions, cache_region
from datetime import datetime, timedelta
-from pyramid.response import Response
-from xonstat.models import *
-from xonstat.util import *
-from xonstat.views.helpers import RecentGame, recent_games_q
+from beaker.cache import cache_region
+from xonstat.models import DBSession, PlayerRank, ActivePlayer, ActiveServer, ActiveMap
+from xonstat.views.helpers import RecentGame, recent_games_q
log = logging.getLogger(__name__)
@cache_region('hourly_term')
-def get_summary_stats():
+def get_summary_stats(scope="all"):
+ """
+ Gets the following aggregate statistics according to the provided scope:
+
+ - the number of active players
+ - the number of games per game type
+
+ Scope can be "all" or "day".
+
+ The fetched information is summarized into a string which is passed
+ directly to the template.
+ """
+ if scope not in ["all", "day"]:
+ scope = "all"
+
+ try:
+ ss = DBSession.query("num_players", "game_type_cd", "num_games").\
+ from_statement(
+ "SELECT num_players, game_type_cd, num_games "
+ "FROM summary_stats_mv "
+ "WHERE scope = :scope "
+ "ORDER BY sort_order "
+ ).params(scope=scope).all()
+
+ i = 1
+ total_games = 0
+ other_games = 0
+ for row in ss:
+ # the number of players is constant in each row
+ total_players = row.num_players
+
+ total_games += row.num_games
+
+ # we can't show all game types on the single summary line, so any
+ # past the fifth one will get bundled in to an "other" count
+ if i > 5:
+ other_games += row.num_games
+
+ i += 1
+
+ # don't send anything if we don't have any activity
+ if total_games == 0:
+ stat_line = None
+ else:
+ # This is ugly because we're doing template-like stuff within the
+ # view code. The alternative isn't any better, though: we would
+ # have to assemble the string inside the template by using a Python
+ # code block. For now I'll leave it like this since it is the lesser
+ # of two evils IMO.
+ # Also we need to hard-code the URL structure in here to allow caching,
+ # which also sucks.
+ in_paren = "; ".join(["{:2,d} {}".format(
+ g.num_games,
+ "<a href='/games?type={0}'>{0}</a>".format(g.game_type_cd)
+ ) for g in ss[:5]])
+
+ if other_games > 0:
+ in_paren += "; {:2,d} other".format(other_games)
+
+ stat_line = "{:2,d} players and {:2,d} games ({})".format(
+ total_players,
+ total_games,
+ in_paren
+ )
+
+ except Exception as e:
+ stat_line = None
+ raise e
+
+ return stat_line
+
+
+@cache_region('hourly_term')
+def get_ranks(game_type_cd):
"""
- Gets the following aggregate or "summary" statistics about stats:
- - the total number of players (total_players)
- - the total number of servers (total_servers)
- - the total number of games (total_games)
- - the total number of dm games (dm_games)
- - the total number of duel games (duel_games)
- - the total number of ctf games (ctf_games)
-
- It is worth noting that there is also a table built to house these
- stats in case the query in this function becomes too long for the
- one time it runs per hour. In that case there is a script in the
- xonstatdb repo - update_summary_stats.sql - that can be used via
- cron to update the data offline.
+ Gets a set number of the top-ranked people for the specified game_type_cd.
+
+ The game_type_cd parameter is the type to fetch. Currently limited to
+ duel, dm, ctf, and tdm.
+ """
+ # how many ranks we want to fetch
+ leaderboard_count = 10
+
+ # only a few game modes are actually ranked
+ if game_type_cd not in 'duel' 'dm' 'ctf' 'tdm':
+ return None
+
+ ranks = DBSession.query(PlayerRank).\
+ filter(PlayerRank.game_type_cd==game_type_cd).\
+ order_by(PlayerRank.rank).\
+ limit(leaderboard_count).all()
+
+ return ranks
+
+
+@cache_region('hourly_term')
+def get_top_players_by_time(limit=None, start=None):
"""
- summary_stats = DBSession.query("total_players", "total_servers",
- "total_games", "dm_games", "duel_games", "ctf_games").\
- from_statement(
- """
- with total_games as (
- select game_type_cd, count(*) total_games
- from games
- where game_type_cd in ('duel', 'dm', 'ctf')
- group by game_type_cd
- ),
- total_players as (
- select count(*) total_players
- from players
- where active_ind = true
- ),
- total_servers as (
- select count(*) total_servers
- from servers
- where active_ind = true
- )
- select tp.total_players, ts.total_servers, dm.total_games+
- duel.total_games+ctf.total_games total_games,
- dm.total_games dm_games, duel.total_games duel_games,
- ctf.total_games ctf_games
- from total_games dm, total_games duel, total_games ctf,
- total_players tp, total_servers ts
- where dm.game_type_cd = 'dm'
- and ctf.game_type_cd = 'ctf'
- and duel.game_type_cd = 'duel'
- """
- ).one()
-
- return summary_stats
+ The top players by the amount of time played during a date range.
+ """
+ q = DBSession.query(ActivePlayer)
+
+ if start is not None:
+ q = q.filter(ActivePlayer.sort_order >= start)
+
+ q = q.order_by(ActivePlayer.sort_order)
+
+ if limit is not None:
+ q = q.limit(limit)
+
+ return q.all()
+
+
+@cache_region('hourly_term')
+def get_top_servers_by_games(limit=None, start=None):
+ """
+ The top servers by the number of games played during a date range.
+ """
+ q = DBSession.query(ActiveServer)
+
+ if start is not None:
+ q = q.filter(ActiveServer.sort_order >= start)
+
+ q = q.order_by(ActiveServer.sort_order)
+
+ if limit is not None:
+ q = q.limit(limit)
+
+ return q.all()
+
+
+@cache_region('hourly_term')
+def get_top_maps_by_games(limit=None, start=None):
+ """
+ The top maps by the number of games played during a date range.
+ """
+ q = DBSession.query(ActiveMap)
+
+ if start is not None:
+ q = q.filter(ActiveMap.sort_order >= start)
+
+ q = q.order_by(ActiveMap.sort_order)
+
+ if limit is not None:
+ q = q.limit(limit)
+
+ return q.all()
def _main_index_data(request):
recent_games_count = 20
# summary statistics for the tagline
- try:
- summary_stats = get_summary_stats()
- except:
- summary_stats = None
-
- # top ranked duelers
- duel_ranks = DBSession.query(PlayerRank.player_id, PlayerRank.nick,
- PlayerRank.elo).\
- filter(PlayerRank.game_type_cd=='duel').\
- order_by(PlayerRank.rank).\
- limit(leaderboard_count).all()
-
- duel_ranks = [(player_id, html_colors(nick), elo) \
- for (player_id, nick, elo) in duel_ranks]
-
- # top ranked CTF-ers
- ctf_ranks = DBSession.query(PlayerRank.player_id, PlayerRank.nick,
- PlayerRank.elo).\
- filter(PlayerRank.game_type_cd=='ctf').\
- order_by(PlayerRank.rank).\
- limit(leaderboard_count).all()
-
- ctf_ranks = [(player_id, html_colors(nick), elo) \
- for (player_id, nick, elo) in ctf_ranks]
+ stat_line = get_summary_stats("all")
+ day_stat_line = get_summary_stats("day")
- # top ranked DM-ers
- dm_ranks = DBSession.query(PlayerRank.player_id, PlayerRank.nick,
- PlayerRank.elo).\
- filter(PlayerRank.game_type_cd=='dm').\
- order_by(PlayerRank.rank).\
- limit(leaderboard_count).all()
- dm_ranks = [(player_id, html_colors(nick), elo) \
- for (player_id, nick, elo) in dm_ranks]
+ # the three top ranks tables
+ ranks = []
+ for gtc in ['duel', 'ctf', 'dm', 'tdm']:
+ rank = get_ranks(gtc)
+ if len(rank) != 0:
+ ranks.append(rank)
right_now = datetime.utcnow()
back_then = datetime.utcnow() - timedelta(days=leaderboard_lifetime)
# 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(Player.player_id > 2).\
- filter(expr.between(PlayerGameStat.create_dt, back_then, right_now)).\
- order_by(expr.desc(func.sum(PlayerGameStat.alivetime))).\
- group_by(Player.nick).\
- group_by(Player.player_id).limit(leaderboard_count).all()
-
- top_players = [(player_id, html_colors(nick), score) \
- for (player_id, nick, score) in top_players]
-
- # top servers by number of total players played
- top_servers = DBSession.query(Server.server_id, Server.name,
- func.count()).\
- filter(Game.server_id==Server.server_id).\
- filter(expr.between(Game.create_dt, back_then, right_now)).\
- order_by(expr.desc(func.count(Game.game_id))).\
- group_by(Server.server_id).\
- group_by(Server.name).limit(leaderboard_count).all()
+ top_players = get_top_players_by_time(10)
+
+ # top servers by number of games
+ top_servers = get_top_servers_by_games(10)
# 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(expr.between(Game.create_dt, back_then, right_now)).\
- order_by(expr.desc(func.count())).\
- group_by(Game.map_id).\
- group_by(Map.name).limit(leaderboard_count).all()
+ top_maps = get_top_maps_by_games(10)
# recent games played in descending order
rgs = recent_games_q(cutoff=back_then).limit(recent_games_count).all()
'top_servers':top_servers,
'top_maps':top_maps,
'recent_games':recent_games,
- 'duel_ranks':duel_ranks,
- 'ctf_ranks':ctf_ranks,
- 'dm_ranks':dm_ranks,
- 'summary_stats':summary_stats,
+ 'ranks':ranks,
+ 'stat_line':stat_line,
+ 'day_stat_line':day_stat_line,
}
"""
Display the main page information.
"""
- mainindex_data = _main_index_data(request)
+ return _main_index_data(request)
- # FIXME: code clone, should get these from _main_index_data
- leaderboard_count = 10
- recent_games_count = 20
- for i in range(leaderboard_count-len(mainindex_data['duel_ranks'])):
- mainindex_data['duel_ranks'].append(('-', '-', '-'))
+def main_index_json(request):
+ """
+ JSON output of the main page information.
+ """
+ return [{'status':'not implemented'}]
+
- for i in range(leaderboard_count-len(mainindex_data['ctf_ranks'])):
- mainindex_data['ctf_ranks'].append(('-', '-', '-'))
+def top_players_index(request):
+ try:
+ start = int(request.params.get('start', None))
+ except:
+ start = None
- for i in range(leaderboard_count-len(mainindex_data['dm_ranks'])):
- mainindex_data['dm_ranks'].append(('-', '-', '-'))
+ top_players = get_top_players_by_time(20, start)
- for i in range(leaderboard_count-len(mainindex_data['top_players'])):
- mainindex_data['top_players'].append(('-', '-', '-'))
+ # building a query string
+ query = {}
+ if len(top_players) > 1:
+ query['start'] = top_players[-1].sort_order + 1
- for i in range(leaderboard_count-len(mainindex_data['top_servers'])):
- mainindex_data['top_servers'].append(('-', '-', '-'))
+ return {
+ 'top_players':top_players,
+ 'query':query,
+ 'start':start,
+ }
- for i in range(leaderboard_count-len(mainindex_data['top_maps'])):
- mainindex_data['top_maps'].append(('-', '-', '-'))
- return mainindex_data
+def top_servers_index(request):
+ try:
+ start = int(request.params.get('start', None))
+ except:
+ start = None
+ top_servers = get_top_servers_by_games(20, start)
-def main_index_json(request):
- """
- JSON output of the main page information.
- """
- return [{'status':'not implemented'}]
+ # building a query string
+ query = {}
+ if len(top_servers) > 1:
+ query['start'] = top_servers[-1].sort_order + 1
+
+ return {
+ 'top_servers':top_servers,
+ 'query':query,
+ 'start':start,
+ }
+
+
+def top_maps_index(request):
+ try:
+ start = int(request.params.get('start', None))
+ except:
+ start = None
+
+ top_maps = get_top_maps_by_games(20, start)
+
+ # building a query string
+ query = {}
+ if len(top_maps) > 1:
+ query['start'] = top_maps[-1].sort_order + 1
+
+ return {
+ 'top_maps':top_maps,
+ 'query':query,
+ 'start':start,
+ }
projects / xonotic / xonstat.git / blobdiff