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_day_summary_stats():
  71     """
  72     Gets the following aggregate statistics about the past 24 hours:
  73         - the number of active players (day_active_players)
  74         - the number of games (day_games)
  75         - the total number of dm games (day_dm_games)
  76         - the total number of duel games (day_duel_games)
  77         - the total number of ctf games (day_ctf_games)
  78     """
  79     try:
  80         day_stats = DBSession.query("day_active_players",
  81                 "day_games", "day_dm_games", "day_duel_games", "day_ctf_games").\
  82             from_statement(
  83             """
  84             with day_games as (
  85                 select game_type_cd, count(*) day_games
  86                 from games
  87                 where game_type_cd in ('duel', 'dm', 'ctf')
  88                 and create_dt > now() - interval '1 day'
  89                 group by game_type_cd
  90             ),
  91             day_active_players as (
  92                 select count(distinct player_id) day_active_players
  93                 from player_game_stats
  94                 where create_dt > now() - interval '1 day'
  95             )
  96             select tap.day_active_players, dm.day_games+
  97                    duel.day_games+ctf.day_games day_games,
  98                    dm.day_games day_dm_games, duel.day_games day_duel_games,
  99                    ctf.day_games day_ctf_games
 100             from   day_games dm, day_games duel, day_games ctf,
 101                    day_active_players tap
 102             where  dm.game_type_cd = 'dm'
 103             and    ctf.game_type_cd = 'ctf'
 104             and    duel.game_type_cd = 'duel'
 105             """
 106             ).one()
 107
 108         # don't show anything if we don't have any activity
 109         if day_stats.day_active_players is None or \
 110            day_stats.day_active_players == 0:
 111            day_stats = None
 112
 113     except Exception as e:
 114         day_stats = None
 115
 116     return day_stats
 117
 118 @cache_region('hourly_term')
 119 def get_ranks(game_type_cd):
 120     """
 121     Gets a set number of the top-ranked people for the specified game_type_cd.
 122
 123     The game_type_cd parameter is the type to fetch. Currently limited to
 124     duel, dm, ctf, and tdm.
 125     """
 126     # how many ranks we want to fetch
 127     leaderboard_count = 10
 128
 129     # only a few game modes are actually ranked
 130     if game_type_cd not in 'duel' 'dm' 'ctf' 'tdm':
 131         return None
 132
 133     ranks = DBSession.query(PlayerRank).\
 134             filter(PlayerRank.game_type_cd==game_type_cd).\
 135             order_by(PlayerRank.rank).\
 136             limit(leaderboard_count).all()
 137
 138     return ranks
 139
 140
 141 def top_players_by_time_q(cutoff_days):
 142     """
 143     Query for the top players by the amount of time played during a date range.
 144
 145     Games older than cutoff_days days old are ignored.
 146     """
 147
 148     # only games played during this range are considered
 149     right_now = datetime.utcnow()
 150     cutoff_dt = right_now - timedelta(days=cutoff_days)
 151
 152     top_players_q = DBSession.query(Player.player_id, Player.nick,
 153             func.sum(PlayerGameStat.alivetime)).\
 154             filter(Player.player_id == PlayerGameStat.player_id).\
 155             filter(Player.player_id > 2).\
 156             filter(expr.between(PlayerGameStat.create_dt, cutoff_dt, right_now)).\
 157             order_by(expr.desc(func.sum(PlayerGameStat.alivetime))).\
 158             group_by(Player.nick).\
 159             group_by(Player.player_id)
 160
 161     return top_players_q
 162
 163
 164 @cache_region('hourly_term')
 165 def get_top_players_by_time(cutoff_days):
 166     """
 167     The top players by the amount of time 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_players_q = top_players_by_time_q(cutoff_days)
 179
 180     top_players = top_players_q.limit(count).all()
 181
 182     top_players = [(player_id, html_colors(nick), score) \
 183             for (player_id, nick, score) in top_players]
 184
 185     return top_players
 186
 187
 188 def top_servers_by_players_q(cutoff_days):
 189     """
 190     Query to get the top servers by the amount of players active
 191     during a date range.
 192
 193     Games older than cutoff_days days old are ignored.
 194     """
 195     # only games played during this range are considered
 196     right_now = datetime.utcnow()
 197     cutoff_dt = right_now - timedelta(days=cutoff_days)
 198
 199     top_servers_q = DBSession.query(Server.server_id, Server.name,
 200         func.count()).\
 201         filter(Game.server_id==Server.server_id).\
 202         filter(expr.between(Game.create_dt, cutoff_dt, right_now)).\
 203         order_by(expr.desc(func.count(Game.game_id))).\
 204         group_by(Server.server_id).\
 205         group_by(Server.name)
 206
 207     return top_servers_q
 208
 209
 210 @cache_region('hourly_term')
 211 def get_top_servers_by_players(cutoff_days):
 212     """
 213     The top servers by the amount of players active during a date range.
 214
 215     Games older than cutoff_days days old are ignored.
 216     """
 217     # how many to retrieve
 218     count = 10
 219
 220     top_servers = top_servers_by_players_q(cutoff_days).limit(count).all()
 221
 222     return top_servers
 223
 224
 225 def top_maps_by_times_played_q(cutoff_days):
 226     """
 227     Query to retrieve the top maps by the amount of times it was played
 228     during a date range.
 229
 230     Games older than cutoff_days days old are ignored.
 231     """
 232     # only games played during this range are considered
 233     right_now = datetime.utcnow()
 234     cutoff_dt = right_now - timedelta(days=cutoff_days)
 235
 236     top_maps_q = DBSession.query(Game.map_id, Map.name,
 237             func.count()).\
 238             filter(Map.map_id==Game.map_id).\
 239             filter(expr.between(Game.create_dt, cutoff_dt, right_now)).\
 240             order_by(expr.desc(func.count())).\
 241             group_by(Game.map_id).\
 242             group_by(Map.name)
 243
 244     return top_maps_q
 245
 246
 247 @cache_region('hourly_term')
 248 def get_top_maps_by_times_played(cutoff_days):
 249     """
 250     The top maps by the amount of times it was played during a date range.
 251
 252     Games older than cutoff_days days old are ignored.
 253     """
 254     # how many to retrieve
 255     count = 10
 256
 257     top_maps = top_maps_by_times_played_q(cutoff_days).limit(count).all()
 258
 259     return top_maps
 260
 261
 262 def _main_index_data(request):
 263     try:
 264         leaderboard_lifetime = int(
 265                 request.registry.settings['xonstat.leaderboard_lifetime'])
 266     except:
 267         leaderboard_lifetime = 30
 268
 269     leaderboard_count = 10
 270     recent_games_count = 20
 271
 272     # summary statistics for the tagline
 273     try:
 274         summary_stats = get_summary_stats()
 275         day_stats = get_day_summary_stats()
 276
 277     except:
 278         summary_stats = None
 279         day_stats = None
 280
 281     # the three top ranks tables
 282     ranks = []
 283     for gtc in ['duel', 'ctf', 'dm', 'tdm']:
 284         rank = get_ranks(gtc)
 285         if len(rank) != 0:
 286             ranks.append(rank)
 287
 288     right_now = datetime.utcnow()
 289     back_then = datetime.utcnow() - timedelta(days=leaderboard_lifetime)
 290
 291     # top players by playing time
 292     top_players = get_top_players_by_time(leaderboard_lifetime)
 293
 294     # top servers by number of total players played
 295     top_servers = get_top_servers_by_players(leaderboard_lifetime)
 296
 297     # top maps by total times played
 298     top_maps = get_top_maps_by_times_played(leaderboard_lifetime)
 299
 300     # recent games played in descending order
 301     rgs = recent_games_q(cutoff=back_then).limit(recent_games_count).all()
 302     recent_games = [RecentGame(row) for row in rgs]
 303
 304     return {'top_players':top_players,
 305             'top_servers':top_servers,
 306             'top_maps':top_maps,
 307             'recent_games':recent_games,
 308             'ranks':ranks,
 309             'summary_stats':summary_stats,
 310             'day_stats':day_stats,
 311             }
 312
 313
 314 def main_index(request):
 315     """
 316     Display the main page information.
 317     """
 318     mainindex_data =  _main_index_data(request)
 319
 320     # FIXME: code clone, should get these from _main_index_data
 321     leaderboard_count = 10
 322     recent_games_count = 20
 323
 324     for i in range(leaderboard_count-len(mainindex_data['top_players'])):
 325         mainindex_data['top_players'].append(('-', '-', '-'))
 326
 327     for i in range(leaderboard_count-len(mainindex_data['top_servers'])):
 328         mainindex_data['top_servers'].append(('-', '-', '-'))
 329
 330     for i in range(leaderboard_count-len(mainindex_data['top_maps'])):
 331         mainindex_data['top_maps'].append(('-', '-', '-'))
 332
 333     return mainindex_data
 334
 335
 336 def main_index_json(request):
 337     """
 338     JSON output of the main page information.
 339     """
 340     return [{'status':'not implemented'}]
 341
 342
 343 def top_players_by_time(request):
 344     current_page = request.params.get('page', 1)
 345
 346     cutoff_days = int(request.registry.settings.\
 347         get('xonstat.leaderboard_lifetime', 30))
 348
 349     top_players_q = top_players_by_time_q(cutoff_days)
 350
 351     top_players = Page(top_players_q, current_page, items_per_page=25, url=page_url)
 352
 353     top_players.items = [(player_id, html_colors(nick), score) \
 354             for (player_id, nick, score) in top_players.items]
 355
 356     return {'top_players':top_players}
 357
 358
 359 def top_servers_by_players(request):
 360     current_page = request.params.get('page', 1)
 361
 362     cutoff_days = int(request.registry.settings.\
 363         get('xonstat.leaderboard_lifetime', 30))
 364
 365     top_servers_q = top_servers_by_players_q(cutoff_days)
 366
 367     top_servers = Page(top_servers_q, current_page, items_per_page=25, url=page_url)
 368
 369     return {'top_servers':top_servers}
 370
 371
 372 def top_maps_by_times_played(request):
 373     current_page = request.params.get('page', 1)
 374
 375     cutoff_days = int(request.registry.settings.\
 376         get('xonstat.leaderboard_lifetime', 30))
 377
 378     top_maps_q = top_maps_by_times_played_q(cutoff_days)
 379
 380     top_maps = Page(top_maps_q, current_page, items_per_page=25, url=page_url)
 381
 382     return {'top_maps':top_maps}