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 from webhelpers.paginate import Page
  12
  13
  14 log = logging.getLogger(__name__)
  15
  16
  17 @cache_region('hourly_term')
  18 def get_summary_stats():
  19     """
  20     Gets the following aggregate or "summary" statistics about stats:
  21         - the total number of players (total_players)
  22         - the total number of servers (total_servers)
  23         - the total number of games (total_games)
  24         - the total number of dm games (dm_games)
  25         - the total number of duel games (duel_games)
  26         - the total number of ctf games (ctf_games)
  27
  28     It is worth noting that there is also a table built to house these
  29     stats in case the query in this function becomes too long for the
  30     one time it runs per hour. In that case there is a script in the
  31     xonstatdb repo - update_summary_stats.sql - that can be used via
  32     cron to update the data offline.
  33     """
  34     summary_stats = DBSession.query("total_players", "total_servers",
  35             "total_games", "dm_games", "duel_games", "ctf_games").\
  36         from_statement(
  37         """
  38         with total_games as (
  39             select game_type_cd, count(*) total_games
  40             from games
  41             where game_type_cd in ('duel', 'dm', 'ctf')
  42             group by game_type_cd
  43         ),
  44         total_players as (
  45             select count(*) total_players
  46             from players
  47             where active_ind = true
  48         ),
  49         total_servers as (
  50             select count(*) total_servers
  51             from servers
  52             where active_ind = true
  53         )
  54         select tp.total_players, ts.total_servers, dm.total_games+
  55                duel.total_games+ctf.total_games total_games,
  56                dm.total_games dm_games, duel.total_games duel_games,
  57                ctf.total_games ctf_games
  58         from   total_games dm, total_games duel, total_games ctf,
  59                total_players tp, total_servers ts
  60         where  dm.game_type_cd = 'dm'
  61         and    ctf.game_type_cd = 'ctf'
  62         and    duel.game_type_cd = 'duel'
  63         """
  64         ).one()
  65
  66     return summary_stats
  67
  68
  69 @cache_region('hourly_term')
  70 def get_ranks(game_type_cd):
  71     """
  72     Gets a set number of the top-ranked people for the specified game_type_cd.
  73
  74     The game_type_cd parameter is the type to fetch. Currently limited to
  75     duel, dm, ctf, and tdm.
  76     """
  77     # how many ranks we want to fetch
  78     leaderboard_count = 10
  79
  80     # only a few game modes are actually ranked
  81     if game_type_cd not in 'duel' 'dm' 'ctf' 'tdm':
  82         return None
  83
  84     ranks = DBSession.query(PlayerRank).\
  85             filter(PlayerRank.game_type_cd==game_type_cd).\
  86             order_by(PlayerRank.rank).\
  87             limit(leaderboard_count).all()
  88
  89     return ranks
  90
  91
  92 def top_players_by_time_q(cutoff_days):
  93     """
  94     Query for 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
  99     # only games played during this range are considered
 100     right_now = datetime.utcnow()
 101     cutoff_dt = right_now - timedelta(days=cutoff_days)
 102
 103     top_players_q = DBSession.query(Player.player_id, Player.nick,
 104             func.sum(PlayerGameStat.alivetime)).\
 105             filter(Player.player_id == PlayerGameStat.player_id).\
 106             filter(Player.player_id > 2).\
 107             filter(expr.between(PlayerGameStat.create_dt, cutoff_dt, right_now)).\
 108             order_by(expr.desc(func.sum(PlayerGameStat.alivetime))).\
 109             group_by(Player.nick).\
 110             group_by(Player.player_id)
 111
 112     return top_players_q
 113
 114
 115 @cache_region('hourly_term')
 116 def get_top_players_by_time(cutoff_days):
 117     """
 118     The top players by the amount of time played during a date range.
 119
 120     Games older than cutoff_days days old are ignored.
 121     """
 122     # how many to retrieve
 123     count = 10
 124
 125     # only games played during this range are considered
 126     right_now = datetime.utcnow()
 127     cutoff_dt = right_now - timedelta(days=cutoff_days)
 128
 129     top_players_q = top_players_by_time_q(cutoff_days)
 130
 131     top_players = top_players_q.limit(count).all()
 132
 133     top_players = [(player_id, html_colors(nick), score) \
 134             for (player_id, nick, score) in top_players]
 135
 136     return top_players
 137
 138
 139 @cache_region('hourly_term')
 140 def top_servers_by_players(cutoff_days):
 141     """
 142     The top servers by the amount of players active during a date range.
 143
 144     Games older than cutoff_days days old are ignored.
 145     """
 146     # how many to retrieve
 147     count = 10
 148
 149     # only games played during this range are considered
 150     right_now = datetime.utcnow()
 151     cutoff_dt = right_now - timedelta(days=cutoff_days)
 152
 153     top_servers = DBSession.query(Server.server_id, Server.name,
 154         func.count()).\
 155         filter(Game.server_id==Server.server_id).\
 156         filter(expr.between(Game.create_dt, cutoff_dt, right_now)).\
 157         order_by(expr.desc(func.count(Game.game_id))).\
 158         group_by(Server.server_id).\
 159         group_by(Server.name).limit(count).all()
 160
 161     return top_servers
 162
 163
 164 @cache_region('hourly_term')
 165 def top_maps_by_times_played(cutoff_days):
 166     """
 167     The top maps by the amount of times it was played during a date range.
 168
 169     Games older than cutoff_days days old are ignored.
 170     """
 171     # how many to retrieve
 172     count = 10
 173
 174     # only games played during this range are considered
 175     right_now = datetime.utcnow()
 176     cutoff_dt = right_now - timedelta(days=cutoff_days)
 177
 178     top_maps = DBSession.query(Game.map_id, Map.name,
 179             func.count()).\
 180             filter(Map.map_id==Game.map_id).\
 181             filter(expr.between(Game.create_dt, cutoff_dt, right_now)).\
 182             order_by(expr.desc(func.count())).\
 183             group_by(Game.map_id).\
 184             group_by(Map.name).limit(count).all()
 185
 186     return top_maps
 187
 188
 189 def _main_index_data(request):
 190     try:
 191         leaderboard_lifetime = int(
 192                 request.registry.settings['xonstat.leaderboard_lifetime'])
 193     except:
 194         leaderboard_lifetime = 30
 195
 196     leaderboard_count = 10
 197     recent_games_count = 20
 198
 199     # summary statistics for the tagline
 200     try:
 201         summary_stats = get_summary_stats()
 202     except:
 203         summary_stats = None
 204
 205     # the three top ranks tables
 206     ranks = []
 207     for gtc in ['duel', 'ctf', 'dm', 'tdm']:
 208         rank = get_ranks(gtc)
 209         if len(rank) != 0:
 210             ranks.append(rank)
 211
 212     right_now = datetime.utcnow()
 213     back_then = datetime.utcnow() - timedelta(days=leaderboard_lifetime)
 214
 215     # top players by playing time
 216     top_players = get_top_players_by_time(leaderboard_lifetime)
 217
 218     # top servers by number of total players played
 219     top_servers = top_servers_by_players(leaderboard_lifetime)
 220
 221     # top maps by total times played
 222     top_maps = top_maps_by_times_played(leaderboard_lifetime)
 223
 224     # recent games played in descending order
 225     rgs = recent_games_q(cutoff=back_then).limit(recent_games_count).all()
 226     recent_games = [RecentGame(row) for row in rgs]
 227
 228     return {'top_players':top_players,
 229             'top_servers':top_servers,
 230             'top_maps':top_maps,
 231             'recent_games':recent_games,
 232             'ranks':ranks,
 233             'summary_stats':summary_stats,
 234             }
 235
 236
 237 def main_index(request):
 238     """
 239     Display the main page information.
 240     """
 241     mainindex_data =  _main_index_data(request)
 242
 243     # FIXME: code clone, should get these from _main_index_data
 244     leaderboard_count = 10
 245     recent_games_count = 20
 246
 247     for i in range(leaderboard_count-len(mainindex_data['top_players'])):
 248         mainindex_data['top_players'].append(('-', '-', '-'))
 249
 250     for i in range(leaderboard_count-len(mainindex_data['top_servers'])):
 251         mainindex_data['top_servers'].append(('-', '-', '-'))
 252
 253     for i in range(leaderboard_count-len(mainindex_data['top_maps'])):
 254         mainindex_data['top_maps'].append(('-', '-', '-'))
 255
 256     return mainindex_data
 257
 258
 259 def main_index_json(request):
 260     """
 261     JSON output of the main page information.
 262     """
 263     return [{'status':'not implemented'}]
 264
 265
 266 def top_players_by_time(request):
 267     current_page = request.params.get('page', 1)
 268
 269     cutoff_days = int(request.registry.settings.\
 270         get('xonstat.leaderboard_lifetime', 30))
 271
 272     top_players_q = top_players_by_time_q(cutoff_days)
 273
 274     top_players = Page(top_players_q, current_page, items_per_page=25, url=page_url)
 275
 276     top_players.items = [(player_id, html_colors(nick), score) \
 277             for (player_id, nick, score) in top_players.items]
 278
 279     return {'top_players':top_players}