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 def top_servers_by_players_q(cutoff_days):
 140     """
 141     Query to get the top servers by the amount of players active
 142     during a date range.
 143
 144     Games older than cutoff_days days old are ignored.
 145     """
 146     # only games played during this range are considered
 147     right_now = datetime.utcnow()
 148     cutoff_dt = right_now - timedelta(days=cutoff_days)
 149
 150     top_servers_q = DBSession.query(Server.server_id, Server.name,
 151         func.count()).\
 152         filter(Game.server_id==Server.server_id).\
 153         filter(expr.between(Game.create_dt, cutoff_dt, right_now)).\
 154         order_by(expr.desc(func.count(Game.game_id))).\
 155         group_by(Server.server_id).\
 156         group_by(Server.name)
 157
 158     return top_servers_q
 159
 160
 161 @cache_region('hourly_term')
 162 def get_top_servers_by_players(cutoff_days):
 163     """
 164     The top servers by the amount of players active during a date range.
 165
 166     Games older than cutoff_days days old are ignored.
 167     """
 168     # how many to retrieve
 169     count = 10
 170
 171     top_servers = top_servers_by_players_q(cutoff_days).limit(count).all()
 172
 173     return top_servers
 174
 175
 176 @cache_region('hourly_term')
 177 def top_maps_by_times_played(cutoff_days):
 178     """
 179     The top maps by the amount of times it was played during a date range.
 180
 181     Games older than cutoff_days days old are ignored.
 182     """
 183     # how many to retrieve
 184     count = 10
 185
 186     # only games played during this range are considered
 187     right_now = datetime.utcnow()
 188     cutoff_dt = right_now - timedelta(days=cutoff_days)
 189
 190     top_maps = DBSession.query(Game.map_id, Map.name,
 191             func.count()).\
 192             filter(Map.map_id==Game.map_id).\
 193             filter(expr.between(Game.create_dt, cutoff_dt, right_now)).\
 194             order_by(expr.desc(func.count())).\
 195             group_by(Game.map_id).\
 196             group_by(Map.name).limit(count).all()
 197
 198     return top_maps
 199
 200
 201 def _main_index_data(request):
 202     try:
 203         leaderboard_lifetime = int(
 204                 request.registry.settings['xonstat.leaderboard_lifetime'])
 205     except:
 206         leaderboard_lifetime = 30
 207
 208     leaderboard_count = 10
 209     recent_games_count = 20
 210
 211     # summary statistics for the tagline
 212     try:
 213         summary_stats = get_summary_stats()
 214     except:
 215         summary_stats = None
 216
 217     # the three top ranks tables
 218     ranks = []
 219     for gtc in ['duel', 'ctf', 'dm', 'tdm']:
 220         rank = get_ranks(gtc)
 221         if len(rank) != 0:
 222             ranks.append(rank)
 223
 224     right_now = datetime.utcnow()
 225     back_then = datetime.utcnow() - timedelta(days=leaderboard_lifetime)
 226
 227     # top players by playing time
 228     top_players = get_top_players_by_time(leaderboard_lifetime)
 229
 230     # top servers by number of total players played
 231     top_servers = get_top_servers_by_players(leaderboard_lifetime)
 232
 233     # top maps by total times played
 234     top_maps = top_maps_by_times_played(leaderboard_lifetime)
 235
 236     # recent games played in descending order
 237     rgs = recent_games_q(cutoff=back_then).limit(recent_games_count).all()
 238     recent_games = [RecentGame(row) for row in rgs]
 239
 240     return {'top_players':top_players,
 241             'top_servers':top_servers,
 242             'top_maps':top_maps,
 243             'recent_games':recent_games,
 244             'ranks':ranks,
 245             'summary_stats':summary_stats,
 246             }
 247
 248
 249 def main_index(request):
 250     """
 251     Display the main page information.
 252     """
 253     mainindex_data =  _main_index_data(request)
 254
 255     # FIXME: code clone, should get these from _main_index_data
 256     leaderboard_count = 10
 257     recent_games_count = 20
 258
 259     for i in range(leaderboard_count-len(mainindex_data['top_players'])):
 260         mainindex_data['top_players'].append(('-', '-', '-'))
 261
 262     for i in range(leaderboard_count-len(mainindex_data['top_servers'])):
 263         mainindex_data['top_servers'].append(('-', '-', '-'))
 264
 265     for i in range(leaderboard_count-len(mainindex_data['top_maps'])):
 266         mainindex_data['top_maps'].append(('-', '-', '-'))
 267
 268     return mainindex_data
 269
 270
 271 def main_index_json(request):
 272     """
 273     JSON output of the main page information.
 274     """
 275     return [{'status':'not implemented'}]
 276
 277
 278 def top_players_by_time(request):
 279     current_page = request.params.get('page', 1)
 280
 281     cutoff_days = int(request.registry.settings.\
 282         get('xonstat.leaderboard_lifetime', 30))
 283
 284     top_players_q = top_players_by_time_q(cutoff_days)
 285
 286     top_players = Page(top_players_q, current_page, items_per_page=25, url=page_url)
 287
 288     top_players.items = [(player_id, html_colors(nick), score) \
 289             for (player_id, nick, score) in top_players.items]
 290
 291     return {'top_players':top_players}
 292
 293
 294 def top_servers_by_players(request):
 295     current_page = request.params.get('page', 1)
 296
 297     cutoff_days = int(request.registry.settings.\
 298         get('xonstat.leaderboard_lifetime', 30))
 299
 300     top_servers_q = top_servers_by_players_q(cutoff_days)
 301
 302     top_servers = Page(top_servers_q, current_page, items_per_page=25, url=page_url)
 303
 304     return {'top_servers':top_servers}