2 import sqlalchemy.sql.expression as expr
3 from datetime import datetime
4 from sqlalchemy.orm import aliased
5 from xonstat.models import *
6 from xonstat.util import *
8 log = logging.getLogger(__name__)
10 class RecentGame(object):
12 This is a helper class to facilitate showing recent games
13 data within mako. The idea is to take the results of a query
14 and transform it into class members easily accessible later.
15 It is like a namedtuple but a little easier to form.
17 The constructor takes a query row that has been fetched, and
18 it requires the following columns to be present in the row:
20 game_id, game_type_cd, game_type_descr, winner, start_dt,
21 server_id, server_name, map_id, map_name, player_id, nick,
24 The following columns are optional:
28 This class is meant to be used in conjunction with recent_games_q,
29 which will return rows matching this specification.
31 def __init__(self, row):
32 self.game_id = row.game_id
33 self.game_type_cd = row.game_type_cd
34 self.game_type_descr = row.game_type_descr
35 self.winner = row.winner
36 self.start_dt = row.start_dt
37 self.fuzzy_date = pretty_date(row.start_dt)
38 self.epoch = timegm(row.start_dt.timetuple())
39 self.server_id = row.server_id
40 self.server_name = row.server_name
41 self.map_id = row.map_id
42 self.map_name = row.map_name
43 self.player_id = row.player_id
45 self.nick_html_colors = html_colors(row.nick)
50 self.elo_delta = row.elo_delta
56 "game_id": self.game_id,
57 "game_type_cd": self.game_type_cd,
58 "game_type_descr": self.game_type_descr,
59 "winner": self.winner,
60 "start_dt": self.start_dt,
61 "fuzzy_dt": self.fuzzy_date,
63 "server_id": self.server_id,
64 "server_name": self.server_name,
65 "map_id": self.map_id,
66 "map_name": self.map_name,
67 "player_id": self.player_id,
69 "nick_html_colors": self.nick_html_colors,
72 "elo_delta": self.elo_delta,
76 return "<RecentGame(id=%s, gametype=%s, server=%s, map=%s)>" % (self.game_id, self.game_type_cd, self.server_name, self.map_name)
79 def recent_games_q(server_id=None, map_id=None, player_id=None,
80 game_type_cd=None, cutoff=None, force_player_id=False,
81 start_game_id=None, end_game_id=None):
83 Returns a SQLA query of recent game data. Parameters filter
84 the results returned if they are provided. If not, it is
85 assumed that results from all servers and maps is desired.
87 The cutoff parameter provides a way to limit how far back to
88 look when querying. Only games that happened on or after the
89 cutoff (which is a datetime object) will be returned.
91 pgstat_alias = aliased(PlayerGameStat, name='pgstat_alias')
93 recent_games_q = DBSession.query(Game.game_id, GameType.game_type_cd,
94 Game.winner, Game.start_dt, GameType.descr.label('game_type_descr'),
95 Server.server_id, Server.name.label('server_name'), Map.map_id,
96 Map.name.label('map_name'), PlayerGameStat.player_id,
97 PlayerGameStat.nick, PlayerGameStat.rank, PlayerGameStat.team,
98 PlayerGameStat.elo_delta).\
99 filter(Game.server_id==Server.server_id).\
100 filter(Game.map_id==Map.map_id).\
101 filter(Game.game_id==PlayerGameStat.game_id).\
102 filter(Game.game_type_cd==GameType.game_type_cd).\
103 order_by(expr.desc(Game.create_dt))
105 # the various filters provided get tacked on to the query
106 if server_id is not None:
107 recent_games_q = recent_games_q.\
108 filter(Server.server_id==server_id)
110 if map_id is not None:
111 recent_games_q = recent_games_q.\
112 filter(Map.map_id==map_id)
114 # Note: force_player_id makes the pgstat row returned be from the
115 # specified player_id. Otherwise it will just look for a game
116 # *having* that player_id, but returning the #1 player's pgstat row
117 if player_id is not None:
119 recent_games_q = recent_games_q.\
120 filter(PlayerGameStat.player_id==player_id).\
121 filter(Game.players.contains([player_id]))
123 recent_games_q = recent_games_q.\
124 filter(PlayerGameStat.scoreboardpos==1).\
125 filter(Game.game_id==pgstat_alias.game_id).\
126 filter(Game.players.contains([player_id])).\
127 filter(pgstat_alias.player_id==player_id)
129 recent_games_q = recent_games_q.\
130 filter(PlayerGameStat.scoreboardpos==1)
132 if game_type_cd is not None:
133 recent_games_q = recent_games_q.\
134 filter(Game.game_type_cd==game_type_cd.lower())
136 if cutoff is not None:
137 right_now = datetime.utcnow()
138 recent_games_q = recent_games_q.\
139 filter(expr.between(Game.create_dt, cutoff, right_now))
141 if start_game_id is not None:
142 recent_games_q = recent_games_q.filter(Game.game_id <= start_game_id)
144 if end_game_id is not None:
145 recent_games_q = recent_games_q.filter(Game.game_id >= end_game_id)
147 return recent_games_q