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     Gets a set number of the top-ranked people for the specified game_type_cd.
  72
  73     The game_type_cd parameter is the type to fetch. Currently limited to
  74     duel, dm, ctf, and tdm.
  75     """
  76     # how many ranks we want to fetch
  77     leaderboard_count = 10
  78
  79     # only a few game modes are actually ranked
  80     if game_type_cd not in 'duel' 'dm' 'ctf' 'tdm':
  81         return None
  82
  83     ranks = DBSession.query(PlayerRank).\
  84             filter(PlayerRank.game_type_cd==game_type_cd).\
  85             order_by(PlayerRank.rank).\
  86             limit(leaderboard_count).all()
  87
  88     return ranks
  89
  90
  91 @cache_region('hourly_term')
  92 def top_players_by_time(cutoff_days):
  93     """
  94     The top players by the amount of time played during a date range.
  95
  96     Games older than cutoff_days days old are ignored.
  97     """
  98     # how many to retrieve
  99     count = 10
 100
 101     # only games played during this range are considered
 102     right_now = datetime.utcnow()
 103     cutoff_dt = right_now - timedelta(days=cutoff_days)
 104
 105     top_players = DBSession.query(Player.player_id, Player.nick,
 106             func.sum(PlayerGameStat.alivetime)).\
 107             filter(Player.player_id == PlayerGameStat.player_id).\
 108             filter(Player.player_id > 2).\
 109             filter(expr.between(PlayerGameStat.create_dt, cutoff_dt, right_now)).\
 110             order_by(expr.desc(func.sum(PlayerGameStat.alivetime))).\
 111             group_by(Player.nick).\
 112             group_by(Player.player_id).limit(count).all()
 113
 114     top_players = [(player_id, html_colors(nick), score) \
 115             for (player_id, nick, score) in top_players]
 116
 117     return top_players
 118
 119
 120 @cache_region('hourly_term')
 121 def top_servers_by_players(cutoff_days):
 122     """
 123     The top servers by the amount of players active during a date range.
 124
 125     Games older than cutoff_days days old are ignored.
 126     """
 127     # how many to retrieve
 128     count = 10
 129
 130     # only games played during this range are considered
 131     right_now = datetime.utcnow()
 132     cutoff_dt = right_now - timedelta(days=cutoff_days)
 133
 134     top_servers = DBSession.query(Server.server_id, Server.name,
 135         func.count()).\
 136         filter(Game.server_id==Server.server_id).\
 137         filter(expr.between(Game.create_dt, cutoff_dt, right_now)).\
 138         order_by(expr.desc(func.count(Game.game_id))).\
 139         group_by(Server.server_id).\
 140         group_by(Server.name).limit(count).all()
 141
 142     return top_servers
 143
 144
 145 @cache_region('hourly_term')
 146 def top_maps_by_times_played(cutoff_days):
 147     """
 148     The top maps by the amount of times it was played during a date range.
 149
 150     Games older than cutoff_days days old are ignored.
 151     """
 152     # how many to retrieve
 153     count = 10
 154
 155     # only games played during this range are considered
 156     right_now = datetime.utcnow()
 157     cutoff_dt = right_now - timedelta(days=cutoff_days)
 158
 159     top_maps = DBSession.query(Game.map_id, Map.name,
 160             func.count()).\
 161             filter(Map.map_id==Game.map_id).\
 162             filter(expr.between(Game.create_dt, cutoff_dt, right_now)).\
 163             order_by(expr.desc(func.count())).\
 164             group_by(Game.map_id).\
 165             group_by(Map.name).limit(count).all()
 166
 167     return top_maps
 168
 169
 170 def _main_index_data(request):
 171     try:
 172         leaderboard_lifetime = int(
 173                 request.registry.settings['xonstat.leaderboard_lifetime'])
 174     except:
 175         leaderboard_lifetime = 30
 176
 177     leaderboard_count = 10
 178     recent_games_count = 20
 179
 180     # summary statistics for the tagline
 181     try:
 182         summary_stats = get_summary_stats()
 183     except:
 184         summary_stats = None
 185
 186     # the three top ranks tables
 187     ranks = []
 188     for gtc in ['duel', 'ctf', 'dm', 'tdm']:
 189         rank = get_ranks(gtc)
 190         if len(rank) != 0:
 191             ranks.append(rank)
 192
 193     right_now = datetime.utcnow()
 194     back_then = datetime.utcnow() - timedelta(days=leaderboard_lifetime)
 195
 196     # top players by playing time
 197     top_players = top_players_by_time(leaderboard_lifetime)
 198
 199     # top servers by number of total players played
 200     top_servers = top_servers_by_players(leaderboard_lifetime)
 201
 202     # top maps by total times played
 203     top_maps = top_maps_by_times_played(leaderboard_lifetime)
 204
 205     # recent games played in descending order
 206     rgs = recent_games_q(cutoff=back_then).limit(recent_games_count).all()
 207     recent_games = [RecentGame(row) for row in rgs]
 208
 209     return {'top_players':top_players,
 210             'top_servers':top_servers,
 211             'top_maps':top_maps,
 212             'recent_games':recent_games,
 213             'ranks':ranks,
 214             'summary_stats':summary_stats,
 215             }
 216
 217
 218 def main_index(request):
 219     """
 220     Display the main page information.
 221     """
 222     mainindex_data =  _main_index_data(request)
 223
 224     # FIXME: code clone, should get these from _main_index_data
 225     leaderboard_count = 10
 226     recent_games_count = 20
 227
 228     for i in range(leaderboard_count-len(mainindex_data['top_players'])):
 229         mainindex_data['top_players'].append(('-', '-', '-'))
 230
 231     for i in range(leaderboard_count-len(mainindex_data['top_servers'])):
 232         mainindex_data['top_servers'].append(('-', '-', '-'))
 233
 234     for i in range(leaderboard_count-len(mainindex_data['top_maps'])):
 235         mainindex_data['top_maps'].append(('-', '-', '-'))
 236
 237     return mainindex_data
 238
 239
 240 def main_index_json(request):
 241     """
 242     JSON output of the main page information.
 243     """
 244     return [{'status':'not implemented'}]