import logging
+import sqlalchemy as sa
import sqlalchemy.sql.functions as func
import sqlalchemy.sql.expression as expr
from beaker.cache import cache_regions, cache_region
@cache_region('hourly_term')
-def get_summary_stats():
+def get_summary_stats(cutoff_days=None):
"""
- 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 the following aggregate statistics about the past cutoff_days days:
+ - the number of active players
+ - the number of games per game type
+ If cutoff_days is None, the above stats are calculated for all time.
+
+ This information is then summarized into a string which is passed
+ directly to the template.
"""
- 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
+ try:
+ if cutoff_days is not None:
+ # only games played during this range are considered
+ right_now = datetime.now()
+ cutoff_dt = right_now - timedelta(days=cutoff_days)
+
+ games = DBSession.query(Game.game_type_cd, func.count()).\
+ filter(expr.between(Game.create_dt, cutoff_dt, right_now)).\
+ group_by(Game.game_type_cd).\
+ order_by(expr.desc(func.count())).all()
+
+ active_players = DBSession.query(func.count(sa.distinct(PlayerGameStat.player_id))).\
+ filter(PlayerGameStat.player_id > 2).\
+ filter(expr.between(PlayerGameStat.create_dt, cutoff_dt, right_now)).\
+ one()[0]
+ else:
+ games = DBSession.query(Game.game_type_cd, func.count()).\
+ group_by(Game.game_type_cd).\
+ order_by(expr.desc(func.count())).all()
+
+ active_players = DBSession.query(func.count(sa.distinct(PlayerGameStat.player_id))).\
+ filter(PlayerGameStat.player_id > 2).\
+ one()[0]
+
+ total_games = 0
+ for total in games:
+ total_games += total[1]
+
+ i = 1
+ other_games = 0
+ for total in games:
+ if i > 5:
+ other_games += total[1]
+ 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[1],
+ "<a href='/games?type={0}'>{0}</a>".format(g[0])
+ ) for g in games[:5]])
+
+ if len(games) > 5:
+ in_paren += "; {:2,d} other".format(other_games)
+
+ stat_line = "{:2,d} active players and {:2,d} games ({})".format(
+ active_players,
+ total_games,
+ in_paren
+ )
+
+ except Exception as e:
+ stat_line = None
+
+ return stat_line
@cache_region('hourly_term')
return top_players
-@cache_region('hourly_term')
-def top_servers_by_players(cutoff_days):
+def top_servers_by_players_q(cutoff_days):
"""
- The top servers by the amount of players active during a date range.
+ Query to get the top servers by the amount of players active
+ during a date range.
Games older than cutoff_days days old are ignored.
"""
- # how many to retrieve
- count = 10
-
# only games played during this range are considered
right_now = datetime.utcnow()
cutoff_dt = right_now - timedelta(days=cutoff_days)
- top_servers = DBSession.query(Server.server_id, Server.name,
+ top_servers_q = DBSession.query(Server.server_id, Server.name,
func.count()).\
filter(Game.server_id==Server.server_id).\
filter(expr.between(Game.create_dt, cutoff_dt, right_now)).\
order_by(expr.desc(func.count(Game.game_id))).\
group_by(Server.server_id).\
- group_by(Server.name).limit(count).all()
+ group_by(Server.name)
- return top_servers
+ return top_servers_q
@cache_region('hourly_term')
-def top_maps_by_times_played(cutoff_days):
+def get_top_servers_by_players(cutoff_days):
"""
- The top maps by the amount of times it was played during a date range.
+ The top servers by the amount of players active during a date range.
Games older than cutoff_days days old are ignored.
"""
# how many to retrieve
count = 10
+ top_servers = top_servers_by_players_q(cutoff_days).limit(count).all()
+
+ return top_servers
+
+
+def top_maps_by_times_played_q(cutoff_days):
+ """
+ Query to retrieve the top maps by the amount of times it was played
+ during a date range.
+
+ Games older than cutoff_days days old are ignored.
+ """
# only games played during this range are considered
right_now = datetime.utcnow()
cutoff_dt = right_now - timedelta(days=cutoff_days)
- top_maps = DBSession.query(Game.map_id, Map.name,
+ top_maps_q = DBSession.query(Game.map_id, Map.name,
func.count()).\
filter(Map.map_id==Game.map_id).\
filter(expr.between(Game.create_dt, cutoff_dt, right_now)).\
order_by(expr.desc(func.count())).\
group_by(Game.map_id).\
- group_by(Map.name).limit(count).all()
+ group_by(Map.name)
+
+ return top_maps_q
+
+
+@cache_region('hourly_term')
+def get_top_maps_by_times_played(cutoff_days):
+ """
+ The top maps by the amount of times it was played during a date range.
+
+ Games older than cutoff_days days old are ignored.
+ """
+ # how many to retrieve
+ count = 10
+
+ top_maps = top_maps_by_times_played_q(cutoff_days).limit(count).all()
return top_maps
recent_games_count = 20
# summary statistics for the tagline
- try:
- summary_stats = get_summary_stats()
- except:
- summary_stats = None
+ stat_line = get_summary_stats()
+ day_stat_line = get_summary_stats(1)
+
# the three top ranks tables
ranks = []
top_players = get_top_players_by_time(leaderboard_lifetime)
# top servers by number of total players played
- top_servers = top_servers_by_players(leaderboard_lifetime)
+ top_servers = get_top_servers_by_players(leaderboard_lifetime)
# top maps by total times played
- top_maps = top_maps_by_times_played(leaderboard_lifetime)
+ top_maps = get_top_maps_by_times_played(leaderboard_lifetime)
# recent games played in descending order
rgs = recent_games_q(cutoff=back_then).limit(recent_games_count).all()
'top_maps':top_maps,
'recent_games':recent_games,
'ranks':ranks,
- 'summary_stats':summary_stats,
+ 'stat_line':stat_line,
+ 'day_stat_line':day_stat_line,
}
for (player_id, nick, score) in top_players.items]
return {'top_players':top_players}
+
+
+def top_servers_by_players(request):
+ current_page = request.params.get('page', 1)
+
+ cutoff_days = int(request.registry.settings.\
+ get('xonstat.leaderboard_lifetime', 30))
+
+ top_servers_q = top_servers_by_players_q(cutoff_days)
+
+ top_servers = Page(top_servers_q, current_page, items_per_page=25, url=page_url)
+
+ return {'top_servers':top_servers}
+
+
+def top_maps_by_times_played(request):
+ current_page = request.params.get('page', 1)
+
+ cutoff_days = int(request.registry.settings.\
+ get('xonstat.leaderboard_lifetime', 30))
+
+ top_maps_q = top_maps_by_times_played_q(cutoff_days)
+
+ top_maps = Page(top_maps_q, current_page, items_per_page=25, url=page_url)
+
+ return {'top_maps':top_maps}
projects / xonotic / xonstat.git / blobdiff