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