xonstat/views/main.py

   1 import logging
   2 import sqlalchemy.sql.functions as func
   3 import sqlalchemy.sql.expression as expr
   4 from beaker.cache import cache_regions, cache_region
   5 from collections import namedtuple
   6 from datetime import datetime, timedelta
   7 from pyramid.response import Response
   8 from xonstat.models import *
   9 from xonstat.util import *
  10 from xonstat.views.helpers import RecentGame, recent_games_q
  11
  12
  13 log = logging.getLogger(__name__)
  14
  15
  16 @cache_region('hourly_term')
  17 def get_summary_stats():
  18     """
  19     Gets the following aggregate or "summary" statistics about stats:
  20         - the total number of players (total_players)
  21         - the total number of servers (total_servers)
  22         - the total number of games (total_games)
  23         - the total number of dm games (dm_games)
  24         - the total number of duel games (duel_games)
  25         - the total number of ctf games (ctf_games)
  26
  27     It is worth noting that there is also a table built to house these
  28     stats in case the query in this function becomes too long for the
  29     one time it runs per hour. In that case there is a script in the
  30     xonstatdb repo - update_summary_stats.sql - that can be used via
  31     cron to update the data offline.
  32     """
  33     summary_stats = DBSession.query("total_players", "total_servers",
  34             "total_games", "dm_games", "duel_games", "ctf_games").\
  35         from_statement(
  36         """
  37         with total_games as (
  38             select game_type_cd, count(*) total_games
  39             from games
  40             where game_type_cd in ('duel', 'dm', 'ctf')
  41             group by game_type_cd
  42         ),
  43         total_players as (
  44             select count(*) total_players
  45             from players
  46             where active_ind = true
  47         ),
  48         total_servers as (
  49             select count(*) total_servers
  50             from servers
  51             where active_ind = true
  52         )
  53         select tp.total_players, ts.total_servers, dm.total_games+
  54                duel.total_games+ctf.total_games total_games,
  55                dm.total_games dm_games, duel.total_games duel_games,
  56                ctf.total_games ctf_games
  57         from   total_games dm, total_games duel, total_games ctf,
  58                total_players tp, total_servers ts
  59         where  dm.game_type_cd = 'dm'
  60         and    ctf.game_type_cd = 'ctf'
  61         and    duel.game_type_cd = 'duel'
  62         """
  63         ).one()
  64
  65     return summary_stats
  66
  67
  68 @cache_region('hourly_term')
  69 def get_ranks(game_type_cd):
  70     """
  71     """
  72     # how many ranks we want to fetch
  73     leaderboard_count = 10
  74
  75     # only a few game modes are actually ranked
  76     if game_type_cd not in 'duel' 'dm' 'ctf' 'tdm':
  77         return None
  78
  79     ranks = DBSession.query(PlayerRank).\
  80             filter(PlayerRank.game_type_cd==game_type_cd).\
  81             order_by(PlayerRank.rank).\
  82             limit(leaderboard_count).all()
  83
  84     return ranks
  85
  86 def _main_index_data(request):
  87     try:
  88         leaderboard_lifetime = int(
  89                 request.registry.settings['xonstat.leaderboard_lifetime'])
  90     except:
  91         leaderboard_lifetime = 30
  92
  93     leaderboard_count = 10
  94     recent_games_count = 20
  95
  96     # summary statistics for the tagline
  97     try:
  98         summary_stats = get_summary_stats()
  99     except:
 100         summary_stats = None
 101
 102     # the three top ranks tables
 103     ranks = []
 104     for gtc in ['duel', 'ctf', 'dm']:
 105         rank = get_ranks(gtc)
 106         if len(rank) != 0:
 107             ranks.append(rank)
 108
 109     right_now = datetime.utcnow()
 110     back_then = datetime.utcnow() - timedelta(days=leaderboard_lifetime)
 111
 112     # top players by playing time
 113     top_players = DBSession.query(Player.player_id, Player.nick,
 114             func.sum(PlayerGameStat.alivetime)).\
 115             filter(Player.player_id == PlayerGameStat.player_id).\
 116             filter(Player.player_id > 2).\
 117             filter(expr.between(PlayerGameStat.create_dt, back_then, right_now)).\
 118             order_by(expr.desc(func.sum(PlayerGameStat.alivetime))).\
 119             group_by(Player.nick).\
 120             group_by(Player.player_id).limit(leaderboard_count).all()
 121
 122     top_players = [(player_id, html_colors(nick), score) \
 123             for (player_id, nick, score) in top_players]
 124
 125     # top servers by number of total players played
 126     top_servers = DBSession.query(Server.server_id, Server.name,
 127             func.count()).\
 128             filter(Game.server_id==Server.server_id).\
 129             filter(expr.between(Game.create_dt, back_then, right_now)).\
 130             order_by(expr.desc(func.count(Game.game_id))).\
 131             group_by(Server.server_id).\
 132             group_by(Server.name).limit(leaderboard_count).all()
 133
 134     # top maps by total times played
 135     top_maps = DBSession.query(Game.map_id, Map.name,
 136             func.count()).\
 137             filter(Map.map_id==Game.map_id).\
 138             filter(expr.between(Game.create_dt, back_then, right_now)).\
 139             order_by(expr.desc(func.count())).\
 140             group_by(Game.map_id).\
 141             group_by(Map.name).limit(leaderboard_count).all()
 142
 143     # recent games played in descending order
 144     rgs = recent_games_q(cutoff=back_then).limit(recent_games_count).all()
 145     recent_games = [RecentGame(row) for row in rgs]
 146
 147     return {'top_players':top_players,
 148             'top_servers':top_servers,
 149             'top_maps':top_maps,
 150             'recent_games':recent_games,
 151             'ranks':ranks,
 152             'summary_stats':summary_stats,
 153             }
 154
 155
 156 def main_index(request):
 157     """
 158     Display the main page information.
 159     """
 160     mainindex_data =  _main_index_data(request)
 161
 162     # FIXME: code clone, should get these from _main_index_data
 163     leaderboard_count = 10
 164     recent_games_count = 20
 165
 166     for i in range(leaderboard_count-len(mainindex_data['top_players'])):
 167         mainindex_data['top_players'].append(('-', '-', '-'))
 168
 169     for i in range(leaderboard_count-len(mainindex_data['top_servers'])):
 170         mainindex_data['top_servers'].append(('-', '-', '-'))
 171
 172     for i in range(leaderboard_count-len(mainindex_data['top_maps'])):
 173         mainindex_data['top_maps'].append(('-', '-', '-'))
 174
 175     return mainindex_data
 176
 177
 178 def main_index_json(request):
 179     """
 180     JSON output of the main page information.
 181     """
 182     return [{'status':'not implemented'}]