def _server_info_data(request):
try:
leaderboard_lifetime = int(request.registry.settings['xonstat.leaderboard_lifetime'])
except:
leaderboard_lifetime = 30
leaderboard_count = 10
recent_games_count = 20
server_id = request.matchdict['id']
try:
# if a "." is in the id, lookup server table by ip address to get the real id
if "." in server_id:
server = DBSession.query(Server).filter_by(hashkey=server_id).first()
if server:
server_id = server.server_id
else:
server = DBSession.query(Server).filter_by(server_id=server_id).first()
if server == None:
return { 'server': None, 'recent_games': [], 'top_players': [], 'top_maps': [] }
# top players by score
#top_scorers = DBSession.query(Player.player_id, Player.nick,
#func.sum(PlayerGameStat.score)).\
#filter(Player.player_id == PlayerGameStat.player_id).\
#filter(Game.game_id == PlayerGameStat.game_id).\
#filter(Game.server_id == server.server_id).\
#filter(Game.create_dt > (datetime.utcnow() - timedelta(days=leaderboard_lifetime))).\
#filter(PlayerGameStat.player_id > 2).\
#order_by(expr.desc(func.sum(PlayerGameStat.score))).\
#group_by(Player.player_id).\
#group_by(Player.nick).\
#limit(leaderboard_count).all()
# filter(PlayerGameStat.create_dt > (datetime.utcnow() - timedelta(days=leaderboard_lifetime))).\
#top_scorers = [(player_id, html_colors(nick), score) \
# for (player_id, nick, score) in top_scorers]
# top players by playing time
top_players = get_top_players_by_time(server_id)
# top maps by total times played
top_maps = get_top_maps(server_id)
# recent games played in descending order
rgs = recent_games_q(server_id=server_id).limit(recent_games_count).all()
recent_games = [RecentGame(row) for row in rgs]
except Exception as e:
server = None
recent_games = None
top_players = None
raise e
return {'server':server,
'recent_games':recent_games,
'top_players': top_players,
'top_maps': top_maps,
}
def player_game_index_data(request):
try:
player_id = int(request.matchdict['player_id'])
except:
player_id = -1
game_type_cd = None
game_type_descr = None
if request.params.has_key('type'):
game_type_cd = request.params['type']
try:
game_type_descr = DBSession.query(GameType.descr).\
filter(GameType.game_type_cd == game_type_cd).\
one()[0]
except Exception as e:
pass
else:
game_type_cd = None
game_type_descr = None
if request.params.has_key('page'):
current_page = request.params['page']
else:
current_page = 1
try:
player = DBSession.query(Player).\
filter_by(player_id=player_id).\
filter(Player.active_ind == True).\
one()
rgs_q = recent_games_q(player_id=player.player_id,
force_player_id=True,
game_type_cd=game_type_cd)
games = Page(rgs_q, current_page, items_per_page=20, url=page_url)
# replace the items in the canned pagination class with more rich ones
games.items = [RecentGame(row) for row in games.items]
games_played = get_games_played(player_id)
except Exception as e:
raise e
player = None
games = None
game_type_cd = None
game_type_descr = None
games_played = None
return {
'player_id': player.player_id,
'player': player,
'games': games,
'game_type_cd': game_type_cd,
'game_type_descr': game_type_descr,
'games_played': games_played,
}
def player_game_index_data(request):
try:
player_id = int(request.matchdict['player_id'])
except:
player_id = -1
game_type_cd = None
game_type_descr = None
if request.params.has_key('type'):
game_type_cd = request.params['type']
try:
game_type_descr = DBSession.query(GameType.descr).\
filter(GameType.game_type_cd == game_type_cd).\
one()[0]
except Exception as e:
pass
else:
game_type_cd = None
game_type_descr = None
if request.params.has_key('page'):
current_page = request.params['page']
else:
current_page = 1
try:
player = DBSession.query(Player).\
filter_by(player_id=player_id).\
filter(Player.active_ind == True).\
one()
rgs_q = recent_games_q(player_id=player.player_id,
force_player_id=True, game_type_cd=game_type_cd)
games = Page(rgs_q, current_page, items_per_page=20, url=page_url)
# replace the items in the canned pagination class with more rich ones
games.items = [RecentGame(row) for row in games.items]
games_played = get_games_played(player_id)
except Exception as e:
raise e
player = None
games = None
game_type_cd = None
game_type_descr = None
games_played = None
return {
'player_id':player.player_id,
'player':player,
'games':games,
'game_type_cd':game_type_cd,
'game_type_descr':game_type_descr,
'games_played':games_played,
}
def get_recent_games(player_id, limit=10):
"""
Provides a list of recent games for a player. Uses the recent_games_q helper.
"""
# recent games played in descending order
rgs = recent_games_q(player_id=player_id, force_player_id=True).limit(limit).all()
recent_games = [RecentGame(row) for row in rgs]
return recent_games
def get_recent_games(player_id, limit=10):
"""
Provides a list of recent games for a player. Uses the recent_games_q helper.
"""
# recent games played in descending order
rgs = recent_games_q(player_id=player_id, force_player_id=True).limit(limit).all()
recent_games = [RecentGame(row) for row in rgs]
return recent_games
def get_recent_games(player_id, limit=20, game_type_cd=None):
"""
Provides a list of recent games for a player. Uses the recent_games_q helper.
"""
# recent games played in descending order
q = recent_games_q(player_id=player_id, force_player_id=True, game_type_cd=game_type_cd, limit=limit)
rgs = q.all()
recent_games = [RecentGame(row) for row in rgs]
return recent_games
def get_recent_games(player_id, limit=20, game_type_cd=None):
"""
Provides a list of recent games for a player. Uses the recent_games_q helper.
"""
# recent games played in descending order
q = recent_games_q(player_id=player_id, force_player_id=True, game_type_cd=game_type_cd, limit=limit)
rgs = q.all()
recent_games = [RecentGame(row) for row in rgs]
return recent_games
def game_finder_data(request):
if request.params.has_key('page'):
current_page = request.params['page']
else:
current_page = 1
query = {}
server_id, map_id, player_id = None, None, None
range_start, range_end, game_type_cd = None, None, None
game_type_descr = None
# these become WHERE clauses when present
if request.params.has_key('server_id'):
server_id = request.params['server_id']
query['server_id'] = server_id
if request.params.has_key('map_id'):
map_id = request.params['map_id']
query['map_id'] = map_id
if request.params.has_key('player_id'):
player_id = request.params['player_id']
query['player_id'] = player_id
if request.params.has_key('range_start'):
range_start = request.params['range_start']
query['range_start'] = range_start
if request.params.has_key('range_end'):
range_end = request.params['range_end']
query['range_end'] = range_end
if request.params.has_key('type'):
game_type_cd = request.params['type']
query['type'] = game_type_cd
try:
game_type_descr = DBSession.query(GameType.descr).\
filter(GameType.game_type_cd == game_type_cd).\
one()[0]
except Exception as e:
game_type_cd = None
rgs_q = recent_games_q(server_id=server_id, map_id=map_id,
player_id=player_id, game_type_cd=game_type_cd)
recent_games = Page(rgs_q, current_page, url=page_url)
recent_games.items = [RecentGame(row) for row in recent_games.items]
return {
'recent_games':recent_games,
'query':query,
'game_type_cd':game_type_cd,
}
def get_recent_games(player_id, limit=20, game_type_cd=None):
"""
Provides a list of recent games for a player. Uses the recent_games_q helper.
"""
# recent games played in descending order
q = recent_games_q(player_id=player_id, force_player_id=True)
if game_type_cd is not None:
q = q.filter(Game.game_type_cd == game_type_cd)
rgs = q.limit(limit).all()
recent_games = [RecentGame(row) for row in rgs]
return recent_games
def get_recent_games(player_id, limit=20, game_type_cd=None):
"""
Provides a list of recent games for a player. Uses the recent_games_q helper.
"""
# recent games played in descending order
q = recent_games_q(player_id=player_id, force_player_id=True)
if game_type_cd is not None:
q = q.filter(Game.game_type_cd == game_type_cd)
rgs = q.limit(limit).all()
recent_games = [RecentGame(row) for row in rgs]
return recent_games
def _main_index_data(request):
try:
leaderboard_lifetime = int(
request.registry.settings['xonstat.leaderboard_lifetime'])
except:
leaderboard_lifetime = 30
leaderboard_count = 10
recent_games_count = 20
# summary statistics for the tagline
stat_line = summary_stats_string("all")
day_stat_line = summary_stats_string("day")
# the three top ranks tables
ranks = []
for gtc in ['duel', 'ctf', 'dm', 'tdm']:
rank = get_ranks(gtc)
if len(rank) != 0:
ranks.append(rank)
right_now = datetime.utcnow()
back_then = datetime.utcnow() - timedelta(days=leaderboard_lifetime)
# top players by playing time
top_players = get_top_players_by_time(10)
# top servers by number of games
top_servers = get_top_servers_by_play_time(10)
# top maps by total times played
top_maps = get_top_maps_by_games(10)
# recent games played in descending order
rgs = recent_games_q(cutoff=back_then).limit(recent_games_count).all()
recent_games = [RecentGame(row) for row in rgs]
return {'top_players':top_players,
'top_servers':top_servers,
'top_maps':top_maps,
'recent_games':recent_games,
'ranks':ranks,
'stat_line':stat_line,
'day_stat_line':day_stat_line,
}
def __init__(self, request):
"""Common data and parameters."""
super(ServerInfo, self).__init__(request)
# this view uses data from other views, so we'll save the data at that level
try:
self.server = DBSession.query(Server)\
.filter(Server.active_ind)\
.filter(Server.server_id == self.server_id)\
.one()
self.top_maps_v = ServerTopMaps(self.request, limit=LEADERBOARD_COUNT)
self.top_scorers_v = ServerTopScorers(self.request, limit=LEADERBOARD_COUNT)
self.top_players_v = ServerTopPlayers(self.request, limit=LEADERBOARD_COUNT)
rgs = recent_games_q(server_id=self.server_id).limit(RECENT_GAMES_COUNT).all()
self.recent_games = [RecentGame(row) for row in rgs]
except:
raise HTTPNotFound
def __init__(self, request):
"""Common data and parameters."""
super(ServerInfo, self).__init__(request)
# this view uses data from other views, so we'll save the data at that level
try:
self.server = DBSession.query(Server)\
.filter(Server.active_ind)\
.filter(Server.server_id == self.server_id)\
.one()
self.top_maps_v = ServerTopMaps(self.request,
limit=LEADERBOARD_COUNT)
self.top_scorers_v = ServerTopScorers(self.request,
limit=LEADERBOARD_COUNT)
self.top_players_v = ServerTopPlayers(self.request,
limit=LEADERBOARD_COUNT)
rgs = recent_games_q(
server_id=self.server_id).limit(RECENT_GAMES_COUNT).all()
self.recent_games = [RecentGame(row) for row in rgs]
except:
raise HTTPNotFound
def game_finder_data(request):
if request.params.has_key('page'):
current_page = request.params['page']
else:
current_page = 1
query = {}
server_id, map_id, player_id = None, None, None
game_type_cd, start_game_id, end_game_id = None, None, None
game_type_descr = None
# these become WHERE clauses when present
if request.params.has_key('server_id'):
server_id = request.params['server_id']
query['server_id'] = server_id
if request.params.has_key('map_id'):
map_id = request.params['map_id']
query['map_id'] = map_id
if request.params.has_key('player_id'):
player_id = request.params['player_id']
query['player_id'] = player_id
if request.params.has_key('start_game_id'):
start_game_id = request.params['start_game_id']
query['start_game_id'] = start_game_id
if request.params.has_key('end_game_id'):
end_game_id = request.params['end_game_id']
query['end_game_id'] = end_game_id
if request.params.has_key('type'):
game_type_cd = request.params['type']
query['type'] = game_type_cd
try:
game_type_descr = DBSession.query(GameType.descr).\
filter(GameType.game_type_cd == game_type_cd).\
one()[0]
except Exception as e:
game_type_cd = None
rgs_q = recent_games_q(server_id=server_id,
map_id=map_id,
player_id=player_id,
game_type_cd=game_type_cd,
start_game_id=start_game_id,
end_game_id=end_game_id)
try:
recent_games = [RecentGame(row) for row in rgs_q.limit(20).all()]
if len(recent_games) > 0:
query['start_game_id'] = recent_games[-1].game_id + 1
# build the list of links for the stripe across the top
game_type_links = []
# clear out the game_id window
gt_query = query.copy()
if 'start_game_id' in gt_query:
del gt_query['start_game_id']
if 'end_game_id' in gt_query:
del gt_query['end_game_id']
for gt in ('overall', 'duel', 'ctf', 'dm', 'tdm', 'ca', 'kh', 'ft',
'lms', 'as', 'dom', 'nb', 'cts', 'rc'):
gt_query['type'] = gt
url = request.route_url("game_index", _query=gt_query)
game_type_links.append((gt, url))
except:
raise httpexceptions.HTTPBadRequest("Malformed Query")
return {
'recent_games': recent_games,
'query': query,
'game_type_cd': game_type_cd,
'game_type_links': game_type_links,
}
def _map_info_data(request):
map_id = request.matchdict['id']
try:
leaderboard_lifetime = int(
request.registry.settings['xonstat.leaderboard_lifetime'])
except:
leaderboard_lifetime = 30
leaderboard_count = 10
recent_games_count = 20
# captime tuples
Captime = namedtuple('Captime', ['player_id', 'nick_html_colors',
'fastest_cap', 'game_id'])
try:
if re.match(r"\d+$", map_id):
map_id = int(map_id)
gmap = DBSession.query(Map).filter_by(map_id=map_id).one()
else:
gmap = DBSession.query(Map).filter_by(name=map_id).one()
map_id = gmap.map_id
here = os.path.dirname(__file__)
if os.path.exists(here + "/../static/images/levelshots/" + gmap.name + ".jpg"):
gmap.image = "/static/images/levelshots/" + gmap.name + ".jpg"
else:
gmap.image = ""
# recent games played in descending order
rgs = recent_games_q(map_id=map_id, limit=recent_games_count).all()
recent_games = [RecentGame(row) for row in rgs]
# top players by score
top_scorers = DBSession.query(Player.player_id, Player.nick,
func.sum(PlayerGameStat.score)).\
filter(Player.player_id == PlayerGameStat.player_id).\
filter(Game.game_id == PlayerGameStat.game_id).\
filter(Game.map_id == map_id).\
filter(Player.player_id > 2).\
filter(PlayerGameStat.create_dt >
(datetime.utcnow() - timedelta(days=leaderboard_lifetime))).\
order_by(expr.desc(func.sum(PlayerGameStat.score))).\
group_by(Player.nick).\
group_by(Player.player_id).all()[0:leaderboard_count]
top_scorers = [(player_id, html_colors(nick), score) \
for (player_id, nick, score) in top_scorers]
# top players by playing time
top_players = DBSession.query(Player.player_id, Player.nick,
func.sum(PlayerGameStat.alivetime)).\
filter(Player.player_id == PlayerGameStat.player_id).\
filter(Game.game_id == PlayerGameStat.game_id).\
filter(Game.map_id == map_id).\
filter(Player.player_id > 2).\
filter(PlayerGameStat.create_dt >
(datetime.utcnow() - timedelta(days=leaderboard_lifetime))).\
order_by(expr.desc(func.sum(PlayerGameStat.alivetime))).\
group_by(Player.nick).\
group_by(Player.player_id).all()[0:leaderboard_count]
top_players = [(player_id, html_colors(nick), score) \
for (player_id, nick, score) in top_players]
# top servers using/playing this map
top_servers = DBSession.query(Server.server_id, Server.name,
func.count(Game.game_id)).\
filter(Game.server_id == Server.server_id).\
filter(Game.map_id == map_id).\
filter(Game.create_dt >
(datetime.utcnow() - timedelta(days=leaderboard_lifetime))).\
order_by(expr.desc(func.count(Game.game_id))).\
group_by(Server.name).\
group_by(Server.server_id).all()[0:leaderboard_count]
# top captimes
captimes_raw = DBSession.query(Player.player_id, Player.nick,
PlayerCaptime.fastest_cap, PlayerCaptime.game_id).\
filter(PlayerCaptime.map_id == map_id).\
filter(Player.player_id == PlayerCaptime.player_id).\
order_by(PlayerCaptime.fastest_cap).\
limit(25).\
all()
captimes = [Captime(c.player_id, html_colors(c.nick),
c.fastest_cap, c.game_id) for c in captimes_raw]
except Exception as e:
gmap = None
return {'gmap':gmap,
'recent_games':recent_games,
'top_scorers':top_scorers,
'top_players':top_players,
'top_servers':top_servers,
'captimes':captimes,
}
def _map_info_data(request):
map_id = int(request.matchdict["id"])
try:
leaderboard_lifetime = int(request.registry.settings["xonstat.leaderboard_lifetime"])
except:
leaderboard_lifetime = 30
leaderboard_count = 10
recent_games_count = 20
# captime tuples
Captime = namedtuple("Captime", ["player_id", "nick_html_colors", "fastest_cap", "game_id"])
try:
gmap = DBSession.query(Map).filter_by(map_id=map_id).one()
# recent games played in descending order
rgs = recent_games_q(map_id=map_id).limit(recent_games_count).all()
recent_games = [RecentGame(row) for row in rgs]
# top players by score
top_scorers = (
DBSession.query(Player.player_id, Player.nick, func.sum(PlayerGameStat.score))
.filter(Player.player_id == PlayerGameStat.player_id)
.filter(Game.game_id == PlayerGameStat.game_id)
.filter(Game.map_id == map_id)
.filter(Player.player_id > 2)
.filter(PlayerGameStat.create_dt > (datetime.utcnow() - timedelta(days=leaderboard_lifetime)))
.order_by(expr.desc(func.sum(PlayerGameStat.score)))
.group_by(Player.nick)
.group_by(Player.player_id)
.all()[0:leaderboard_count]
)
top_scorers = [(player_id, html_colors(nick), score) for (player_id, nick, score) in top_scorers]
# top players by playing time
top_players = (
DBSession.query(Player.player_id, Player.nick, func.sum(PlayerGameStat.alivetime))
.filter(Player.player_id == PlayerGameStat.player_id)
.filter(Game.game_id == PlayerGameStat.game_id)
.filter(Game.map_id == map_id)
.filter(Player.player_id > 2)
.filter(PlayerGameStat.create_dt > (datetime.utcnow() - timedelta(days=leaderboard_lifetime)))
.order_by(expr.desc(func.sum(PlayerGameStat.alivetime)))
.group_by(Player.nick)
.group_by(Player.player_id)
.all()[0:leaderboard_count]
)
top_players = [(player_id, html_colors(nick), score) for (player_id, nick, score) in top_players]
# top servers using/playing this map
top_servers = (
DBSession.query(Server.server_id, Server.name, func.count(Game.game_id))
.filter(Game.server_id == Server.server_id)
.filter(Game.map_id == map_id)
.filter(Game.create_dt > (datetime.utcnow() - timedelta(days=leaderboard_lifetime)))
.order_by(expr.desc(func.count(Game.game_id)))
.group_by(Server.name)
.group_by(Server.server_id)
.all()[0:leaderboard_count]
)
# top captimes
captimes_raw = (
DBSession.query(Player.player_id, Player.nick, PlayerCaptime.fastest_cap, PlayerCaptime.game_id)
.filter(PlayerCaptime.map_id == map_id)
.filter(Player.player_id == PlayerCaptime.player_id)
.order_by(PlayerCaptime.fastest_cap)
.limit(25)
.all()
)
captimes = [Captime(c.player_id, html_colors(c.nick), c.fastest_cap, c.game_id) for c in captimes_raw]
except Exception as e:
gmap = None
return {
"gmap": gmap,
"recent_games": recent_games,
"top_scorers": top_scorers,
"top_players": top_players,
"top_servers": top_servers,
"captimes": captimes,
}
def game_finder_data(request):
if request.params.has_key('page'):
current_page = request.params['page']
else:
current_page = 1
query = {}
server_id, map_id, player_id = None, None, None
game_type_cd, start_game_id, end_game_id = None, None, None
game_type_descr = None
# these become WHERE clauses when present
if request.params.has_key('server_id'):
server_id = request.params['server_id']
query['server_id'] = server_id
if request.params.has_key('map_id'):
map_id = request.params['map_id']
query['map_id'] = map_id
if request.params.has_key('player_id'):
player_id = request.params['player_id']
query['player_id'] = player_id
if request.params.has_key('start_game_id'):
start_game_id = request.params['start_game_id']
query['start_game_id'] = start_game_id
if request.params.has_key('end_game_id'):
end_game_id = request.params['end_game_id']
query['end_game_id'] = end_game_id
game_type_cd = request.params.get('type') or request.cookies.get('gametype')
if game_type_cd:
query['type'] = game_type_cd
try:
game_type_descr = DBSession.query(GameType.descr).\
filter(GameType.game_type_cd == game_type_cd).\
one()[0]
except Exception as e:
game_type_cd = None
rgs_q = recent_games_q(server_id=server_id, map_id=map_id,
player_id=player_id, game_type_cd=game_type_cd,
start_game_id=start_game_id, end_game_id=end_game_id)
recent_games = [RecentGame(row) for row in rgs_q.limit(20).all()]
if len(recent_games) > 0:
query['start_game_id'] = recent_games[-1].game_id + 1
# build the list of links for the stripe across the top
game_type_links = []
# clear out the game_id window
gt_query = query.copy()
if 'start_game_id' in gt_query:
del gt_query['start_game_id']
if 'end_game_id' in gt_query:
del gt_query['end_game_id']
gameList = games_q();
gameList.insert(0, 'overall');
for gt in gameList:
gt_query['type'] = gt
url = request.route_path("game_index", _query=gt_query)
game_type_links.append((gt, url))
return {
'recent_games':recent_games,
'query':query,
'game_type_cd':game_type_cd,
'game_type_links':game_type_links,
}
def player_versus_data(request):
try:
p1_id = int(request.params.get("p1", None))
p2_id = int(request.params.get("p2", None))
p1_wins = 0
p2_wins = 0
players = DBSession.query(Player).filter(sa.or_(Player.player_id ==
p1_id, Player.player_id == p2_id)).order_by(Player.player_id).all()
if len(players) < 2:
raise Exception("Not enough players found.")
# assign the players from the array retrieved above
if players[0].player_id == p1_id:
p1 = players[0]
p2 = players[1]
else:
p1 = players[1]
p2 = players[0]
# note that wins and losses are from p1's perspective
win_loss_sql = text("""select win_loss, count(1)
from (
select case
when pgsp1.score >= pgsp2.score then 'win'
else 'loss'
end win_loss
from games g join player_game_stats pgsp1
on g.game_id = pgsp1.game_id and pgsp1.player_id = :p1
join player_game_stats pgsp2
on g.game_id = pgsp2.game_id and pgsp2.player_id = :p2
where g.players @> ARRAY[:p1,:p2]
and g.game_type_cd = 'duel'
and pgsp1.create_dt between g.create_dt - interval '1 hour'
and g.create_dt + interval '1 hour'
and pgsp2.create_dt between g.create_dt - interval '1 hour'
and g.create_dt + interval '1 hour'
) wl
group by win_loss
""")
wins_losses = DBSession.query("win_loss", "count").\
from_statement(win_loss_sql).\
params(p1=p1_id, p2=p2_id).all()
for row in wins_losses:
if row.win_loss == "win":
p1_wins = row.count
elif row.win_loss == "loss":
p2_wins = row.count
# grab the 20 most recent games between the two
rgs_raw = recent_games_q(player_id=p1_id, player_id_2=p2_id,
game_type_cd="duel").limit(20).all()
rgs = [RecentGame(row) for row in rgs_raw]
except Exception as e:
log.debug(e)
p1_id = None
p2_id = None
p1 = None
p2 = None
p1_wins = None
p2_wins = None
rgs = None
return {
"p1_id" : p1_id,
"p2_id" : p2_id,
"p1" : p1,
"p2" : p2,
"p1_wins" : p1_wins,
"p2_wins" : p2_wins,
"recent_games" : rgs,
}
def _map_info_data(request):
map_id = int(request.matchdict['id'])
try:
leaderboard_lifetime = int(
request.registry.settings['xonstat.leaderboard_lifetime'])
except:
leaderboard_lifetime = 30
leaderboard_count = 10
recent_games_count = 20
# captime tuples
Captime = namedtuple('Captime', ['player_id', 'nick_html_colors',
'fastest_cap', 'game_id'])
try:
gmap = DBSession.query(Map).filter_by(map_id=map_id).one()
# recent games played in descending order
rgs = recent_games_q(map_id=map_id).limit(recent_games_count).all()
recent_games = [RecentGame(row) for row in rgs]
# top players by score
top_scorers = DBSession.query(Player.player_id, Player.nick,
func.sum(PlayerGameStat.score)).\
filter(Player.player_id == PlayerGameStat.player_id).\
filter(Game.game_id == PlayerGameStat.game_id).\
filter(Game.map_id == map_id).\
filter(Player.player_id > 2).\
filter(PlayerGameStat.create_dt >
(datetime.utcnow() - timedelta(days=leaderboard_lifetime))).\
order_by(expr.desc(func.sum(PlayerGameStat.score))).\
group_by(Player.nick).\
group_by(Player.player_id).all()[0:leaderboard_count]
top_scorers = [(player_id, html_colors(nick), score) \
for (player_id, nick, score) in top_scorers]
# top players by playing time
top_players = DBSession.query(Player.player_id, Player.nick,
func.sum(PlayerGameStat.alivetime)).\
filter(Player.player_id == PlayerGameStat.player_id).\
filter(Game.game_id == PlayerGameStat.game_id).\
filter(Game.map_id == map_id).\
filter(Player.player_id > 2).\
filter(PlayerGameStat.create_dt >
(datetime.utcnow() - timedelta(days=leaderboard_lifetime))).\
order_by(expr.desc(func.sum(PlayerGameStat.alivetime))).\
group_by(Player.nick).\
group_by(Player.player_id).all()[0:leaderboard_count]
top_players = [(player_id, html_colors(nick), score) \
for (player_id, nick, score) in top_players]
# top servers using/playing this map
top_servers = DBSession.query(Server.server_id, Server.name,
func.count(Game.game_id)).\
filter(Game.server_id == Server.server_id).\
filter(Game.map_id == map_id).\
filter(Game.create_dt >
(datetime.utcnow() - timedelta(days=leaderboard_lifetime))).\
order_by(expr.desc(func.count(Game.game_id))).\
group_by(Server.name).\
group_by(Server.server_id).all()[0:leaderboard_count]
# TODO make this a configuration parameter to be set in the settings
# top captimes
captimes_raw = DBSession.query(Player.player_id, Player.nick,
PlayerCaptime.fastest_cap, PlayerCaptime.game_id).\
filter(PlayerCaptime.map_id == map_id).\
filter(Player.player_id == PlayerCaptime.player_id).\
order_by(PlayerCaptime.fastest_cap).\
limit(10).\
all()
captimes = [Captime(c.player_id, html_colors(c.nick),
c.fastest_cap, c.game_id) for c in captimes_raw]
except Exception as e:
gmap = None
return {'gmap':gmap,
'recent_games':recent_games,
'top_scorers':top_scorers,
'top_players':top_players,
'top_servers':top_servers,
'captimes':captimes,
}
def player_versus_data(request):
try:
p1_id = int(request.params.get("p1", None))
p2_id = int(request.params.get("p2", None))
p1_wins = 0
p2_wins = 0
players = DBSession.query(Player).filter(sa.or_(Player.player_id ==
p1_id, Player.player_id == p2_id)).order_by(Player.player_id).all()
if len(players) < 2:
raise Exception("Not enough players found.")
# assign the players from the array retrieved above
if players[0].player_id == p1_id:
p1 = players[0]
p2 = players[1]
else:
p1 = players[1]
p2 = players[0]
# note that wins and losses are from p1's perspective
win_loss_sql = """select win_loss, count(1)
from (
select case
when pgsp1.score >= pgsp2.score then 'win'
else 'loss'
end win_loss
from games g join player_game_stats pgsp1
on g.game_id = pgsp1.game_id and pgsp1.player_id = :p1
join player_game_stats pgsp2
on g.game_id = pgsp2.game_id and pgsp2.player_id = :p2
where g.players @> ARRAY[:p1,:p2]
and g.game_type_cd = 'duel'
and pgsp1.create_dt between g.create_dt - interval '1 hour'
and g.create_dt + interval '1 hour'
and pgsp2.create_dt between g.create_dt - interval '1 hour'
and g.create_dt + interval '1 hour'
) wl
group by win_loss
"""
wins_losses = DBSession.query("win_loss", "count").\
from_statement(win_loss_sql).\
params(p1=p1_id, p2=p2_id).all()
for row in wins_losses:
if row.win_loss == "win":
p1_wins = row.count
elif row.win_loss == "loss":
p2_wins = row.count
# grab the 20 most recent games between the two
rgs_raw = recent_games_q(player_id=p1_id, player_id_2=p2_id,
game_type_cd="duel").limit(20).all()
rgs = [RecentGame(row) for row in rgs_raw]
except Exception as e:
log.debug(e)
p1_id = None
p2_id = None
p1 = None
p2 = None
p1_wins = None
p2_wins = None
rgs = None
return {
"p1_id" : p1_id,
"p2_id" : p2_id,
"p1" : p1,
"p2" : p2,
"p1_wins" : p1_wins,
"p2_wins" : p2_wins,
"recent_games" : rgs,
}
def game_finder_data(request):
if request.params.has_key('page'):
current_page = request.params['page']
else:
current_page = 1
query = {}
server_id, map_id, player_id = None, None, None
game_type_cd, start_game_id, end_game_id = None, None, None
game_type_descr = None
# these become WHERE clauses when present
if request.params.has_key('server_id'):
server_id = request.params['server_id']
query['server_id'] = server_id
if request.params.has_key('map_id'):
map_id = request.params['map_id']
query['map_id'] = map_id
if request.params.has_key('player_id'):
player_id = request.params['player_id']
query['player_id'] = player_id
if request.params.has_key('start_game_id'):
start_game_id = request.params['start_game_id']
query['start_game_id'] = start_game_id
if request.params.has_key('end_game_id'):
end_game_id = request.params['end_game_id']
query['end_game_id'] = end_game_id
if request.params.has_key('type'):
game_type_cd = request.params['type']
query['type'] = game_type_cd
try:
game_type_descr = DBSession.query(GameType.descr).\
filter(GameType.game_type_cd == game_type_cd).\
one()[0]
except Exception as e:
game_type_cd = None
rgs_q = recent_games_q(server_id=server_id, map_id=map_id,
player_id=player_id, game_type_cd=game_type_cd,
start_game_id=start_game_id, end_game_id=end_game_id)
try:
recent_games = [RecentGame(row) for row in rgs_q.limit(20).all()]
if len(recent_games) > 0:
query['start_game_id'] = recent_games[-1].game_id + 1
# build the list of links for the stripe across the top
game_type_links = []
# clear out the game_id window
gt_query = query.copy()
if 'start_game_id' in gt_query:
del gt_query['start_game_id']
if 'end_game_id' in gt_query:
del gt_query['end_game_id']
for gt in ('overall','duel','ctf','dm','tdm','ca','kh','ft',
'lms','as','dom','nb','cts','rc'):
gt_query['type'] = gt
url = request.route_url("game_index", _query=gt_query)
game_type_links.append((gt, url))
except:
raise httpexceptions.HTTPBadRequest("Malformed Query")
return {
'recent_games':recent_games,
'query':query,
'game_type_cd':game_type_cd,
'game_type_links':game_type_links,
}
def _server_info_data(request):
server_id = request.matchdict['id']
try:
leaderboard_lifetime = int(
request.registry.settings['xonstat.leaderboard_lifetime'])
except:
leaderboard_lifetime = 30
leaderboard_count = 10
recent_games_count = 20
try:
server = DBSession.query(Server).filter_by(server_id=server_id).one()
# top maps by total times played
top_maps = DBSession.query(Game.map_id, Map.name,
func.count()).\
filter(Map.map_id==Game.map_id).\
filter(Game.server_id==server.server_id).\
filter(Game.create_dt >
(datetime.utcnow() - timedelta(days=leaderboard_lifetime))).\
order_by(expr.desc(func.count())).\
group_by(Game.map_id).\
group_by(Map.name).all()[0:leaderboard_count]
# top players by score
top_scorers = DBSession.query(Player.player_id, Player.nick,
func.sum(PlayerGameStat.score)).\
filter(Player.player_id == PlayerGameStat.player_id).\
filter(Game.game_id == PlayerGameStat.game_id).\
filter(Game.server_id == server.server_id).\
filter(Player.player_id > 2).\
filter(PlayerGameStat.create_dt >
(datetime.utcnow() - timedelta(days=leaderboard_lifetime))).\
order_by(expr.desc(func.sum(PlayerGameStat.score))).\
group_by(Player.nick).\
group_by(Player.player_id).all()[0:leaderboard_count]
top_scorers = [(player_id, html_colors(nick), score) \
for (player_id, nick, score) in top_scorers]
# top players by playing time
top_players = DBSession.query(Player.player_id, Player.nick,
func.sum(PlayerGameStat.alivetime)).\
filter(Player.player_id == PlayerGameStat.player_id).\
filter(Game.game_id == PlayerGameStat.game_id).\
filter(Game.server_id == server.server_id).\
filter(Player.player_id > 2).\
filter(PlayerGameStat.create_dt >
(datetime.utcnow() - timedelta(days=leaderboard_lifetime))).\
order_by(expr.desc(func.sum(PlayerGameStat.alivetime))).\
group_by(Player.nick).\
group_by(Player.player_id).all()[0:leaderboard_count]
top_players = [(player_id, html_colors(nick), score) \
for (player_id, nick, score) in top_players]
# recent games played in descending order
rgs = recent_games_q(server_id=server_id).limit(recent_games_count).all()
recent_games = [RecentGame(row) for row in rgs]
except Exception as e:
server = None
recent_games = None
top_players = None
raise e
return {'server':server,
'recent_games':recent_games,
'top_players': top_players,
'top_scorers': top_scorers,
'top_maps': top_maps,
}
def game_finder_data(request):
if request.params.has_key("page"):
current_page = request.params["page"]
else:
current_page = 1
query = {}
server_id, map_id, player_id = None, None, None
game_type_cd, start_game_id, end_game_id = None, None, None
game_type_descr = None
# these become WHERE clauses when present
if request.params.has_key("server_id"):
server_id = request.params["server_id"]
query["server_id"] = server_id
if request.params.has_key("map_id"):
map_id = request.params["map_id"]
query["map_id"] = map_id
if request.params.has_key("player_id"):
player_id = request.params["player_id"]
query["player_id"] = player_id
if request.params.has_key("start_game_id"):
start_game_id = request.params["start_game_id"]
query["start_game_id"] = start_game_id
if request.params.has_key("end_game_id"):
end_game_id = request.params["end_game_id"]
query["end_game_id"] = end_game_id
if request.params.has_key("type"):
game_type_cd = request.params["type"]
query["type"] = game_type_cd
try:
game_type_descr = DBSession.query(GameType.descr).filter(GameType.game_type_cd == game_type_cd).one()[0]
except Exception as e:
game_type_cd = None
rgs_q = recent_games_q(
server_id=server_id,
map_id=map_id,
player_id=player_id,
game_type_cd=game_type_cd,
start_game_id=start_game_id,
end_game_id=end_game_id,
)
try:
recent_games = [RecentGame(row) for row in rgs_q.limit(20).all()]
if len(recent_games) > 0:
query["start_game_id"] = recent_games[-1].game_id + 1
# build the list of links for the stripe across the top
game_type_links = []
# clear out the game_id window
gt_query = query.copy()
if "start_game_id" in gt_query:
del gt_query["start_game_id"]
if "end_game_id" in gt_query:
del gt_query["end_game_id"]
for gt in ("overall", "duel", "ctf", "dm", "tdm", "ca", "kh", "ft", "lms", "as", "dom", "nb", "cts", "rc"):
gt_query["type"] = gt
url = request.route_url("game_index", _query=gt_query)
game_type_links.append((gt, url))
except:
raise httpexceptions.HTTPBadRequest("Malformed Query")
return {
"recent_games": recent_games,
"query": query,
"game_type_cd": game_type_cd,
"game_type_links": game_type_links,
}
def get_recent_games(limit):
return recent_games_q().limit(limit).all()
def _map_info_data(request):
map_id = request.matchdict['id']
try:
leaderboard_lifetime = int(
request.registry.settings['xonstat.leaderboard_lifetime'])
except:
leaderboard_lifetime = 30
leaderboard_count = 10
recent_games_count = 20
# captime tuples
Captime = namedtuple(
'Captime', ['player_id', 'nick_html_colors', 'fastest_cap', 'game_id'])
try:
if re.match(r"\d+$", map_id):
map_id = int(map_id)
gmap = DBSession.query(Map).filter_by(map_id=map_id).one()
else:
gmap = DBSession.query(Map).filter_by(name=map_id).one()
map_id = gmap.map_id
# recent games played in descending order
rgs = recent_games_q(map_id=map_id).limit(recent_games_count).all()
recent_games = [RecentGame(row) for row in rgs]
# top players by score
top_scorers = DBSession.query(Player.player_id, Player.nick,
func.sum(PlayerGameStat.score)).\
filter(Player.player_id == PlayerGameStat.player_id).\
filter(Game.game_id == PlayerGameStat.game_id).\
filter(Game.map_id == map_id).\
filter(Player.player_id > 2).\
filter(PlayerGameStat.create_dt >
(datetime.utcnow() - timedelta(days=leaderboard_lifetime))).\
order_by(expr.desc(func.sum(PlayerGameStat.score))).\
group_by(Player.nick).\
group_by(Player.player_id).all()[0:leaderboard_count]
top_scorers = [(player_id, html_colors(nick), score) \
for (player_id, nick, score) in top_scorers]
# top players by playing time
top_players = DBSession.query(Player.player_id, Player.nick,
func.sum(PlayerGameStat.alivetime)).\
filter(Player.player_id == PlayerGameStat.player_id).\
filter(Game.game_id == PlayerGameStat.game_id).\
filter(Game.map_id == map_id).\
filter(Player.player_id > 2).\
filter(PlayerGameStat.create_dt >
(datetime.utcnow() - timedelta(days=leaderboard_lifetime))).\
order_by(expr.desc(func.sum(PlayerGameStat.alivetime))).\
group_by(Player.nick).\
group_by(Player.player_id).all()[0:leaderboard_count]
top_players = [(player_id, html_colors(nick), score) \
for (player_id, nick, score) in top_players]
# top servers using/playing this map
top_servers = DBSession.query(Server.server_id, Server.name,
func.count(Game.game_id)).\
filter(Game.server_id == Server.server_id).\
filter(Game.map_id == map_id).\
filter(Game.create_dt >
(datetime.utcnow() - timedelta(days=leaderboard_lifetime))).\
order_by(expr.desc(func.count(Game.game_id))).\
group_by(Server.name).\
group_by(Server.server_id).all()[0:leaderboard_count]
# top captimes
captimes_raw = DBSession.query(Player.player_id, Player.nick,
PlayerCaptime.fastest_cap, PlayerCaptime.game_id).\
filter(PlayerCaptime.map_id == map_id).\
filter(Player.player_id == PlayerCaptime.player_id).\
order_by(PlayerCaptime.fastest_cap).\
limit(25).\
all()
captimes = [
Captime(c.player_id, html_colors(c.nick), c.fastest_cap, c.game_id)
for c in captimes_raw
]
except Exception as e:
gmap = None
return {
'gmap': gmap,
'recent_games': recent_games,
'top_scorers': top_scorers,
'top_players': top_players,
'top_servers': top_servers,
'captimes': captimes,
}
def _server_info_data(request):
try:
leaderboard_lifetime = int(
request.registry.settings['xonstat.leaderboard_lifetime'])
except:
leaderboard_lifetime = 30
leaderboard_count = 10
recent_games_count = 20
server_id = request.matchdict['id']
try:
# if a "." is in the id, lookup server table by ip address to get the real id
if "." in server_id:
server = DBSession.query(Server).filter_by(
hashkey=server_id).first()
if server:
server_id = server.server_id
else:
server = DBSession.query(Server).filter_by(
server_id=server_id).first()
if server == None:
return {
'server': None,
'recent_games': [],
'top_players': [],
'top_maps': []
}
# top players by score
#top_scorers = DBSession.query(Player.player_id, Player.nick,
#func.sum(PlayerGameStat.score)).\
#filter(Player.player_id == PlayerGameStat.player_id).\
#filter(Game.game_id == PlayerGameStat.game_id).\
#filter(Game.server_id == server.server_id).\
#filter(Game.create_dt > (datetime.utcnow() - timedelta(days=leaderboard_lifetime))).\
#filter(PlayerGameStat.player_id > 2).\
#order_by(expr.desc(func.sum(PlayerGameStat.score))).\
#group_by(Player.player_id).\
#group_by(Player.nick).\
#limit(leaderboard_count).all()
# filter(PlayerGameStat.create_dt > (datetime.utcnow() - timedelta(days=leaderboard_lifetime))).\
#top_scorers = [(player_id, html_colors(nick), score) \
# for (player_id, nick, score) in top_scorers]
# top players by playing time
top_players = get_top_players_by_time(server_id)
# top maps by total times played
top_maps = get_top_maps(server_id)
# recent games played in descending order
rgs = recent_games_q(server_id=server_id,
limit=recent_games_count).all()
recent_games = [RecentGame(row) for row in rgs]
except Exception as e:
server = None
recent_games = None
top_players = None
raise e
return {
'server': server,
'recent_games': recent_games,
'top_players': top_players,
'top_maps': top_maps,
}
def get_recent_games(limit):
return recent_games_q().limit(limit).all()
