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