xonstat/views/main.py

   1 import logging
   2 import sqlalchemy as sa
   3 import sqlalchemy.sql.functions as func
   4 import sqlalchemy.sql.expression as expr
   5 from beaker.cache import cache_regions, cache_region
   6 from collections import namedtuple
   7 from datetime import datetime, timedelta
   8 from pyramid.response import Response
   9 from xonstat.models import *
  10 from xonstat.util import *
  11 from xonstat.views.helpers import RecentGame, recent_games_q
  12 from webhelpers.paginate import Page
  13
  14
  15 log = logging.getLogger(__name__)
  16
  17
  18 @cache_region('hourly_term')
  19 def get_summary_stats():
  20     """
  21     Gets the following aggregate or "summary" statistics about stats:
  22         - the total number of players (total_players)
  23         - the total number of servers (total_servers)
  24         - the total number of games (total_games)
  25         - the total number of dm games (dm_games)
  26         - the total number of duel games (duel_games)
  27         - the total number of ctf games (ctf_games)
  28
  29     It is worth noting that there is also a table built to house these
  30     stats in case the query in this function becomes too long for the
  31     one time it runs per hour. In that case there is a script in the
  32     xonstatdb repo - update_summary_stats.sql - that can be used via
  33     cron to update the data offline.
  34     """
  35     summary_stats = DBSession.query("total_players", "total_servers",
  36             "total_games", "dm_games", "duel_games", "ctf_games").\
  37         from_statement(
  38         """
  39         with total_games as (
  40             select game_type_cd, count(*) total_games
  41             from games
  42             where game_type_cd in ('duel', 'dm', 'ctf')
  43             group by game_type_cd
  44         ),
  45         total_players as (
  46             select count(*) total_players
  47             from players
  48             where active_ind = true
  49         ),
  50         total_servers as (
  51             select count(*) total_servers
  52             from servers
  53             where active_ind = true
  54         )
  55         select tp.total_players, ts.total_servers, dm.total_games+
  56                duel.total_games+ctf.total_games total_games,
  57                dm.total_games dm_games, duel.total_games duel_games,
  58                ctf.total_games ctf_games
  59         from   total_games dm, total_games duel, total_games ctf,
  60                total_players tp, total_servers ts
  61         where  dm.game_type_cd = 'dm'
  62         and    ctf.game_type_cd = 'ctf'
  63         and    duel.game_type_cd = 'duel'
  64         """
  65         ).one()
  66
  67     return summary_stats
  68
  69
  70 @cache_region('hourly_term')
  71 def get_day_summary_stats():
  72     """
  73     Gets the following aggregate statistics about the past 24 hours:
  74         - the number of active players (day_active_players)
  75         - the number of games per game type (day_games)
  76
  77     This information is then summarized into a string which is passed
  78     directly to the template.
  79     """
  80     try:
  81         # only games played during this range are considered
  82         right_now = datetime.now()
  83         cutoff_dt = right_now - timedelta(days=1)
  84
  85         games = DBSession.query(Game.game_type_cd, func.count()).\
  86             filter(expr.between(Game.create_dt, cutoff_dt, right_now)).\
  87             group_by(Game.game_type_cd).\
  88             order_by(expr.desc(func.count())).all()
  89
  90         total_games = 0
  91         for total in games:
  92             total_games += total[1]
  93
  94         i = 1
  95         other_games = 0
  96         for total in games:
  97             if i > 5:
  98                 other_games += total[1]
  99
 100             i += 1
 101
 102         active_players = DBSession.query(func.count(sa.distinct(PlayerGameStat.player_id))).\
 103             filter(PlayerGameStat.player_id > 2).\
 104             filter(expr.between(PlayerGameStat.create_dt, cutoff_dt, right_now)).\
 105             one()[0]
 106
 107         # don't send anything if we don't have any activity
 108         if total_games == 0:
 109             day_stat_line = None
 110         else:
 111             in_paren = ", ".join(["{} {}".format(
 112                 g[1], g[0]) for g in games[:5]]
 113             )
 114             if len(games) > 5:
 115                 in_paren += ", {} other".format(other_games)
 116
 117             day_stat_line = "{} active players and {} games ({}) in the past 24 hours.".format(
 118                 active_players,
 119                 total_games,
 120                 in_paren
 121             )
 122
 123     except Exception as e:
 124         day_stat_line = None
 125
 126     return day_stat_line
 127
 128 @cache_region('hourly_term')
 129 def get_ranks(game_type_cd):
 130     """
 131     Gets a set number of the top-ranked people for the specified game_type_cd.
 132
 133     The game_type_cd parameter is the type to fetch. Currently limited to
 134     duel, dm, ctf, and tdm.
 135     """
 136     # how many ranks we want to fetch
 137     leaderboard_count = 10
 138
 139     # only a few game modes are actually ranked
 140     if game_type_cd not in 'duel' 'dm' 'ctf' 'tdm':
 141         return None
 142
 143     ranks = DBSession.query(PlayerRank).\
 144             filter(PlayerRank.game_type_cd==game_type_cd).\
 145             order_by(PlayerRank.rank).\
 146             limit(leaderboard_count).all()
 147
 148     return ranks
 149
 150
 151 def top_players_by_time_q(cutoff_days):
 152     """
 153     Query for the top players by the amount of time played during a date range.
 154
 155     Games older than cutoff_days days old are ignored.
 156     """
 157
 158     # only games played during this range are considered
 159     right_now = datetime.utcnow()
 160     cutoff_dt = right_now - timedelta(days=cutoff_days)
 161
 162     top_players_q = DBSession.query(Player.player_id, Player.nick,
 163             func.sum(PlayerGameStat.alivetime)).\
 164             filter(Player.player_id == PlayerGameStat.player_id).\
 165             filter(Player.player_id > 2).\
 166             filter(expr.between(PlayerGameStat.create_dt, cutoff_dt, right_now)).\
 167             order_by(expr.desc(func.sum(PlayerGameStat.alivetime))).\
 168             group_by(Player.nick).\
 169             group_by(Player.player_id)
 170
 171     return top_players_q
 172
 173
 174 @cache_region('hourly_term')
 175 def get_top_players_by_time(cutoff_days):
 176     """
 177     The top players by the amount of time played during a date range.
 178
 179     Games older than cutoff_days days old are ignored.
 180     """
 181     # how many to retrieve
 182     count = 10
 183
 184     # only games played during this range are considered
 185     right_now = datetime.utcnow()
 186     cutoff_dt = right_now - timedelta(days=cutoff_days)
 187
 188     top_players_q = top_players_by_time_q(cutoff_days)
 189
 190     top_players = top_players_q.limit(count).all()
 191
 192     top_players = [(player_id, html_colors(nick), score) \
 193             for (player_id, nick, score) in top_players]
 194
 195     return top_players
 196
 197
 198 def top_servers_by_players_q(cutoff_days):
 199     """
 200     Query to get the top servers by the amount of players active
 201     during a date range.
 202
 203     Games older than cutoff_days days old are ignored.
 204     """
 205     # only games played during this range are considered
 206     right_now = datetime.utcnow()
 207     cutoff_dt = right_now - timedelta(days=cutoff_days)
 208
 209     top_servers_q = DBSession.query(Server.server_id, Server.name,
 210         func.count()).\
 211         filter(Game.server_id==Server.server_id).\
 212         filter(expr.between(Game.create_dt, cutoff_dt, right_now)).\
 213         order_by(expr.desc(func.count(Game.game_id))).\
 214         group_by(Server.server_id).\
 215         group_by(Server.name)
 216
 217     return top_servers_q
 218
 219
 220 @cache_region('hourly_term')
 221 def get_top_servers_by_players(cutoff_days):
 222     """
 223     The top servers by the amount of players active during a date range.
 224
 225     Games older than cutoff_days days old are ignored.
 226     """
 227     # how many to retrieve
 228     count = 10
 229
 230     top_servers = top_servers_by_players_q(cutoff_days).limit(count).all()
 231
 232     return top_servers
 233
 234
 235 def top_maps_by_times_played_q(cutoff_days):
 236     """
 237     Query to retrieve the top maps by the amount of times it was played
 238     during a date range.
 239
 240     Games older than cutoff_days days old are ignored.
 241     """
 242     # only games played during this range are considered
 243     right_now = datetime.utcnow()
 244     cutoff_dt = right_now - timedelta(days=cutoff_days)
 245
 246     top_maps_q = DBSession.query(Game.map_id, Map.name,
 247             func.count()).\
 248             filter(Map.map_id==Game.map_id).\
 249             filter(expr.between(Game.create_dt, cutoff_dt, right_now)).\
 250             order_by(expr.desc(func.count())).\
 251             group_by(Game.map_id).\
 252             group_by(Map.name)
 253
 254     return top_maps_q
 255
 256
 257 @cache_region('hourly_term')
 258 def get_top_maps_by_times_played(cutoff_days):
 259     """
 260     The top maps by the amount of times it was played during a date range.
 261
 262     Games older than cutoff_days days old are ignored.
 263     """
 264     # how many to retrieve
 265     count = 10
 266
 267     top_maps = top_maps_by_times_played_q(cutoff_days).limit(count).all()
 268
 269     return top_maps
 270
 271
 272 def _main_index_data(request):
 273     try:
 274         leaderboard_lifetime = int(
 275                 request.registry.settings['xonstat.leaderboard_lifetime'])
 276     except:
 277         leaderboard_lifetime = 30
 278
 279     leaderboard_count = 10
 280     recent_games_count = 20
 281
 282     # summary statistics for the tagline
 283     try:
 284         summary_stats = get_summary_stats()
 285         day_stat_line = get_day_summary_stats()
 286
 287     except:
 288         summary_stats = None
 289         day_stat_line = None
 290
 291     # the three top ranks tables
 292     ranks = []
 293     for gtc in ['duel', 'ctf', 'dm', 'tdm']:
 294         rank = get_ranks(gtc)
 295         if len(rank) != 0:
 296             ranks.append(rank)
 297
 298     right_now = datetime.utcnow()
 299     back_then = datetime.utcnow() - timedelta(days=leaderboard_lifetime)
 300
 301     # top players by playing time
 302     top_players = get_top_players_by_time(leaderboard_lifetime)
 303
 304     # top servers by number of total players played
 305     top_servers = get_top_servers_by_players(leaderboard_lifetime)
 306
 307     # top maps by total times played
 308     top_maps = get_top_maps_by_times_played(leaderboard_lifetime)
 309
 310     # recent games played in descending order
 311     rgs = recent_games_q(cutoff=back_then).limit(recent_games_count).all()
 312     recent_games = [RecentGame(row) for row in rgs]
 313
 314     return {'top_players':top_players,
 315             'top_servers':top_servers,
 316             'top_maps':top_maps,
 317             'recent_games':recent_games,
 318             'ranks':ranks,
 319             'summary_stats':summary_stats,
 320             'day_stat_line':day_stat_line,
 321             }
 322
 323
 324 def main_index(request):
 325     """
 326     Display the main page information.
 327     """
 328     mainindex_data =  _main_index_data(request)
 329
 330     # FIXME: code clone, should get these from _main_index_data
 331     leaderboard_count = 10
 332     recent_games_count = 20
 333
 334     for i in range(leaderboard_count-len(mainindex_data['top_players'])):
 335         mainindex_data['top_players'].append(('-', '-', '-'))
 336
 337     for i in range(leaderboard_count-len(mainindex_data['top_servers'])):
 338         mainindex_data['top_servers'].append(('-', '-', '-'))
 339
 340     for i in range(leaderboard_count-len(mainindex_data['top_maps'])):
 341         mainindex_data['top_maps'].append(('-', '-', '-'))
 342
 343     return mainindex_data
 344
 345
 346 def main_index_json(request):
 347     """
 348     JSON output of the main page information.
 349     """
 350     return [{'status':'not implemented'}]
 351
 352
 353 def top_players_by_time(request):
 354     current_page = request.params.get('page', 1)
 355
 356     cutoff_days = int(request.registry.settings.\
 357         get('xonstat.leaderboard_lifetime', 30))
 358
 359     top_players_q = top_players_by_time_q(cutoff_days)
 360
 361     top_players = Page(top_players_q, current_page, items_per_page=25, url=page_url)
 362
 363     top_players.items = [(player_id, html_colors(nick), score) \
 364             for (player_id, nick, score) in top_players.items]
 365
 366     return {'top_players':top_players}
 367
 368
 369 def top_servers_by_players(request):
 370     current_page = request.params.get('page', 1)
 371
 372     cutoff_days = int(request.registry.settings.\
 373         get('xonstat.leaderboard_lifetime', 30))
 374
 375     top_servers_q = top_servers_by_players_q(cutoff_days)
 376
 377     top_servers = Page(top_servers_q, current_page, items_per_page=25, url=page_url)
 378
 379     return {'top_servers':top_servers}
 380
 381
 382 def top_maps_by_times_played(request):
 383     current_page = request.params.get('page', 1)
 384
 385     cutoff_days = int(request.registry.settings.\
 386         get('xonstat.leaderboard_lifetime', 30))
 387
 388     top_maps_q = top_maps_by_times_played_q(cutoff_days)
 389
 390     top_maps = Page(top_maps_q, current_page, items_per_page=25, url=page_url)
 391
 392     return {'top_maps':top_maps}