X-Git-Url: https://git.xonotic.org/?a=blobdiff_plain;f=xonstat%2Fviews%2Fmain.py;h=465b7478a75168d06772846dbe0e514ff22badde;hb=40c7131a7da4087c1fa07d7f369a12765eddb497;hp=10505b4a6d54220476a798566cb7a29cf2940de2;hpb=5e33d230ecd8a3db4a94dabbfee18fa7ffd19951;p=xonotic%2Fxonstat.git diff --git a/xonstat/views/main.py b/xonstat/views/main.py index 10505b4..465b747 100644 --- a/xonstat/views/main.py +++ b/xonstat/views/main.py @@ -1,89 +1,225 @@ import logging import sqlalchemy.sql.functions as func import sqlalchemy.sql.expression as expr +from beaker.cache import cache_regions, cache_region +from collections import namedtuple 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 webhelpers.paginate import Page log = logging.getLogger(__name__) -def _main_index_data(request): - try: - leaderboard_lifetime = int( - request.registry.settings['xonstat.leaderboard_lifetime']) - except: - leaderboard_lifetime = 30 +@cache_region('hourly_term') +def get_summary_stats(): + """ + 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) - leaderboard_count = 10 - recent_games_count = 20 + 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. + """ + 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() - # 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() + return summary_stats - 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() +@cache_region('hourly_term') +def get_ranks(game_type_cd): + """ + 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 - ctf_ranks = [(player_id, html_colors(nick), elo) \ - for (player_id, nick, elo) in ctf_ranks] + # only a few game modes are actually ranked + if game_type_cd not in 'duel' 'dm' 'ctf' 'tdm': + return None - # top ranked DM-ers - dm_ranks = DBSession.query(PlayerRank.player_id, PlayerRank.nick, - PlayerRank.elo).\ - filter(PlayerRank.game_type_cd=='dm').\ + ranks = DBSession.query(PlayerRank).\ + filter(PlayerRank.game_type_cd==game_type_cd).\ order_by(PlayerRank.rank).\ limit(leaderboard_count).all() - dm_ranks = [(player_id, html_colors(nick), elo) \ - for (player_id, nick, elo) in dm_ranks] + return ranks + + +def top_players_by_time_q(cutoff_days): + """ + Query for the top players by the amount of time 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() - back_then = datetime.utcnow() - timedelta(days=leaderboard_lifetime) + cutoff_dt = right_now - timedelta(days=cutoff_days) - # top players by playing time - top_players = DBSession.query(Player.player_id, Player.nick, + top_players_q = 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)).\ + filter(expr.between(PlayerGameStat.create_dt, cutoff_dt, right_now)).\ order_by(expr.desc(func.sum(PlayerGameStat.alivetime))).\ group_by(Player.nick).\ - group_by(Player.player_id).limit(leaderboard_count).all() + group_by(Player.player_id) + + return top_players_q + + +@cache_region('hourly_term') +def get_top_players_by_time(cutoff_days): + """ + The top players by the amount of time played 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_players_q = top_players_by_time_q(cutoff_days) + + top_players = top_players_q.limit(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() + return top_players - # top maps by total times played - top_maps = DBSession.query(Game.map_id, Map.name, + +@cache_region('hourly_term') +def top_servers_by_players(cutoff_days): + """ + 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, + 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() + + return top_servers + + +@cache_region('hourly_term') +def 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 + + # 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, func.count()).\ filter(Map.map_id==Game.map_id).\ - filter(expr.between(Game.create_dt, back_then, right_now)).\ + 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(leaderboard_count).all() + group_by(Map.name).limit(count).all() + + return top_maps + + +def _main_index_data(request): + try: + leaderboard_lifetime = int( + request.registry.settings['xonstat.leaderboard_lifetime']) + except: + leaderboard_lifetime = 30 + + leaderboard_count = 10 + recent_games_count = 20 + + # summary statistics for the tagline + try: + summary_stats = get_summary_stats() + except: + summary_stats = None + + # 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 = get_top_players_by_time(leaderboard_lifetime) + + # top servers by number of total players played + top_servers = top_servers_by_players(leaderboard_lifetime) + + # top maps by total times played + top_maps = 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() @@ -93,9 +229,8 @@ def _main_index_data(request): 'top_servers':top_servers, 'top_maps':top_maps, 'recent_games':recent_games, - 'duel_ranks':duel_ranks, - 'ctf_ranks':ctf_ranks, - 'dm_ranks':dm_ranks, + 'ranks':ranks, + 'summary_stats':summary_stats, } @@ -109,15 +244,6 @@ def main_index(request): leaderboard_count = 10 recent_games_count = 20 - for i in range(leaderboard_count-len(mainindex_data['duel_ranks'])): - mainindex_data['duel_ranks'].append(('-', '-', '-')) - - for i in range(leaderboard_count-len(mainindex_data['ctf_ranks'])): - mainindex_data['ctf_ranks'].append(('-', '-', '-')) - - for i in range(leaderboard_count-len(mainindex_data['dm_ranks'])): - mainindex_data['dm_ranks'].append(('-', '-', '-')) - for i in range(leaderboard_count-len(mainindex_data['top_players'])): mainindex_data['top_players'].append(('-', '-', '-')) @@ -135,3 +261,19 @@ def main_index_json(request): JSON output of the main page information. """ return [{'status':'not implemented'}] + + +def top_players_by_time(request): + current_page = request.params.get('page', 1) + + cutoff_days = int(request.registry.settings.\ + get('xonstat.leaderboard_lifetime', 30)) + + top_players_q = top_players_by_time_q(cutoff_days) + + top_players = Page(top_players_q, current_page, items_per_page=25, url=page_url) + + top_players.items = [(player_id, html_colors(nick), score) \ + for (player_id, nick, score) in top_players.items] + + return {'top_players':top_players}