def get(self):
with db.getCur() as cur:
playerFields = db.table_field_names('Players')
cur.execute(("SELECT {}, QuarterId FROM Players"
" LEFT OUTER JOIN Memberships"
" ON Players.Id = Memberships.PlayerId"
" WHERE Id != ?"
" ORDER BY Name ASC, QuarterId ASC").format(
', '.join(playerFields)),
(scores.getUnusedPointsPlayerID(), ))
rows = cur.fetchall()
players = collections.OrderedDict({})
last_player = None
for row in rows: # Build dictionary for each player
if row[0] != last_player:
players[row[0]] = collections.defaultdict(
lambda: list(), zip(playerFields, row))
last_player = row[0]
players[row[0]]['GameCounts'] = {}
memberQtr = row[len(playerFields)]
if memberQtr is not None: # Memberships is a list of qtrs
players[row[0]]['Memberships'].append(memberQtr)
cur.execute(("SELECT Players.Id, Quarter, COUNT(Scores.Id)"
" FROM Players"
" LEFT OUTER JOIN Scores"
" ON Players.Id = Scores.PlayerId"
" WHERE Players.Id != ?"
" GROUP BY Players.Id, Scores.Quarter"
" ORDER BY Players.Id ASC, Quarter ASC").format(
', '.join(playerFields)),
(scores.getUnusedPointsPlayerID(), ))
# Update player dictionary with game counts
for row in cur.fetchall():
player = players[row[0]]
player['GameCounts'][row[1]] = int(row[2])
cur.execute("SELECT DISTINCT Quarter FROM Scores UNION"
" SELECT DISTINCT Quarter FROM Quarters"
" ORDER BY Quarter ASC") # Get all known quarters
quarters = [row[0] for row in cur.fetchall()]
initialQtrsShown = [scores.quarterString()] # Current quarter
if initialQtrsShown[0] in quarters: # Start at current qtr
index = quarters.index(initialQtrsShown[0]) # and go forward
initialQtrsShown = quarters[index:index +
settings.MEMBERSHIPQUARTERS]
elif len(quarters) > 0: # Else take most recent qtrs
initialQtrsShown = quarters[-settings.MEMBERSHIPQUARTERS:]
else:
initialQtrsShown = []
self.render("players.html",
message="No players found" if len(rows) == 0 else "",
players=players,
quarters=quarters,
visibleQtrs=initialQtrsShown)
def get_eligible(quarter=None):
"""Return a nested dictionary structure indexed by quarter and player ID
that returns a dictionary with the following flags:
'Member' for whether they were a member that quarter,
'Played' for whether they played any games that quarter, and
'Eligible' indicating whether or not the player qualified for the
end-of-quarter tournament in that quarter.
"""
eligible = collections.defaultdict(lambda: collections.defaultdict(
lambda: collections.defaultdict(lambda: False)))
# Get unused points playerID before opening cursor to avoid db deadlock
unusedPointsPlayerID = scores.getUnusedPointsPlayerID()
with db.getCur() as cur:
cur.execute(
"SELECT Scores.Quarter AS Qtr, Scores.PlayerId AS Plr,"
" QualifyingGames, QualifyingDistinctDates, COUNT(Score), "
" COUNT(DISTINCT Date), Memberships.QuarterId IS NOT NULL"
" FROM Scores LEFT OUTER JOIN Quarters ON"
" Scores.Quarter = Quarters.Quarter"
" LEFT OUTER JOIN Memberships ON"
" Scores.PlayerId = Memberships.PlayerID AND"
" Scores.Quarter = Memberships.QuarterId"
" WHERE Scores.PlayerId != ?"
" GROUP BY Qtr, Plr "
"UNION SELECT Quarters.Quarter AS Qtr, Players.Id Plr,"
" QualifyingGames, QualifyingDistinctDates, 0, 0,"
" Memberships.QuarterId IS NOT NULL"
" FROM Quarters LEFT OUTER JOIN Players"
" LEFT OUTER JOIN Memberships ON"
" Players.Id = Memberships.PlayerID AND"
" Quarters.Quarter = Memberships.QuarterId"
" WHERE Players.Id != ?"
" ORDER BY Qtr, Plr", (unusedPointsPlayerID, unusedPointsPlayerID))
rows = cur.fetchall()
previousQtr = None
for row in rows:
Quarter, PlayerId, QGames, QDistinctDates, Games, Dates, Memb = row
if Quarter not in eligible:
if previousQtr is None:
eligible[Quarter]['QGames'] = (QGames
or settings.QUALIFYINGGAMES)
eligible[Quarter]['QDistinctDates'] = (
QDistinctDates or settings.QUALIFYINGDISTINCTDATES)
else:
eligible[Quarter]['QGames'] = (QGames or
eligible[previousQtr]['QGames'])
eligible[Quarter]['QDistinctDates'] = (
QDistinctDates or eligible[previousQtr]['QDistinctDates'])
previousQtr = Quarter
eligible[Quarter][PlayerId]['Member'] = Memb
eligible[Quarter][PlayerId]['Played'] = Games > 0
eligible[Quarter][PlayerId]['Eligible'] = Memb and (
Games >= eligible[Quarter]['QGames']
or Dates >= eligible[Quarter]['QDistinctDates'])
return eligible
def get(self):
columns = ["name", "rating", "count"]
query = """SELECT
Players.Name,
ROUND((SUM(Scores.DeltaRating) + {DEFAULT_RATING}) * 100) / 100 AS Rating,
COUNT(*) AS GameCount
FROM Scores LEFT JOIN Players ON Players.Id = Scores.PlayerId
WHERE Players.Id != ?
GROUP BY Players.Id
ORDER BY Rating DESC;""".format(DEFAULT_RATING=settings.DEFAULT_RATING)
with db.getCur() as cur:
cur.execute(query, (scores.getUnusedPointsPlayerID(),))
players = [dict(zip(columns, row)) for row in cur.fetchall()]
self.write(json.dumps({'players':players}))
def get(self, q):
with db.getCur() as cur:
cur.execute(
"SELECT Rank, Players.Name, Scores.RawScore, Scores.Chombos, Scores.Date, Players.Id FROM Scores INNER JOIN Players ON Players.Id = Scores.PlayerId WHERE GameId = ? ORDER BY Rank",
(q, ))
rows = cur.fetchall()
if len(rows) == 0:
self.render("message.html",
message="Game not found",
title="Edit Game")
else:
unusedPoints = None
# UnusedPointsPlayer always sorted last in rank
if rows[-1][5] == scores.getUnusedPointsPlayerID():
unusedPoints = rows[-1][2]
rows = rows[:-1]
unusedPointsIncrement, perPlayer = scores.getPointSettings(
date=rows[0][4])
self.render("editgame.html",
id=q,
scores=json.dumps(rows).replace(
"'", "\\'").replace("\\\"", "\\\\\""),
unusedPoints=unusedPoints,
unusedPointsIncrement=unusedPointsIncrement)
def genLeaderboard(leaderDate=None):
"""Recalculates the leaderboard for the given datetime object.
If leaderDate is None, then recalculates all leaderboards."""
# Get unused points playerID before opening cursor to change leaderboard
# records to avoid db deadlock if no unused player is yet defined
unusedPointsPlayerID = scores.getUnusedPointsPlayerID()
with db.getCur() as cur:
leadercols = ['Place'] + LBDcolumns
leaderrows = []
for periodname, period in periods.items():
rows = []
queries = period['queries']
datefmt = period['datefmt']
sql = "DELETE FROM Leaderboards WHERE Period = ? "
if leaderDate is not None:
datetest = "(" + datefmt + ") = (" + datefmt.format(
date="?") + ")"
bindings = [scores.dateString(leaderDate)
] * datefmt.count("{date}")
sql += " AND Date = " + datefmt.format(date="?")
else:
datetest = "1"
bindings = []
cur.execute(sql, [periodname] + bindings)
for query in queries:
sql = query.format(datetest=datetest,
datefmt=datefmt,
DEFDROPGAMES=settings.DROPGAMECOUNT).format(
date="Scores.Date")
cur.execute(sql, [unusedPointsPlayerID] + bindings)
for row in cur.fetchall():
record = dict(zip(LBDcolumns, row))
# For Quarterly Leaderboards, compute dropped game average
if periodname == 'quarter' and int(
record['DropGames']) > 0:
record['DropGames'] = min(settings.MAXDROPGAMES,
record['DropGames'])
cur.execute(
"SELECT Score FROM Scores"
" WHERE PlayerId = ? AND Quarter = ?"
" ORDER BY Score ASC LIMIT -1 OFFSET ?",
(record['PlayerId'], record['Date'],
record['DropGames']))
total = 0.0
count = 0
for score in cur.fetchall():
total += score[0]
count += 1
if count > 0:
record['AvgScore'] = round(total / count, 2)
record['GameCount'] -= record['DropGames']
rows += [record]
rows.sort(key=lambda row: row['AvgScore'],
reverse=True) # sort by score
places = {}
for row in rows:
if row['Date'] not in places:
places[row['Date']] = 1
row['Place'] = places[row['Date']]
places[row['Date']] += 1
leaderrow = [row[col] for col in leadercols]
leaderrows += [leaderrow]
query = "INSERT INTO Leaderboards({columns}) VALUES({colvals})".format(
columns=",".join(leadercols),
colvals=",".join(["?"] * len(leadercols)))
cur.executemany(query, leaderrows)
def get(self):
with db.getCur() as cur:
self.set_header('Content-Type', 'application/json')
cur.execute("SELECT Name FROM Players WHERE Id != ? ORDER BY Name",
(scores.getUnusedPointsPlayerID(), ))
self.write(json.dumps(list(map(lambda x: x[0], cur.fetchall()))))
def get(self, page):
if page is None:
page = 0
else:
page = int(page[1:]) - 1
PERPAGE = 5
with db.getCur() as cur:
cur.execute("SELECT DISTINCT Date FROM Scores ORDER BY Date DESC")
dates = cur.fetchall()
gamecount = len(dates)
if gamecount > 0:
cur.execute(
"SELECT Scores.GameId,"
" strftime('%Y-%m-%d', Scores.Date), Rank,"
" Players.Name, Scores.RawScore / 1000.0,"
" Scores.Score, Scores.Chombos, Players.Id"
" FROM Scores INNER JOIN Players ON"
" Players.Id = Scores.PlayerId"
" WHERE Scores.Date BETWEEN ? AND ?;",
(dates[min(page * PERPAGE + PERPAGE - 1,
gamecount - 1)][0], dates[min(
page * PERPAGE, gamecount - 1)][0]))
rows = cur.fetchall()
games = {}
for row in rows:
gID, date, rank, name, rawscore, points, chombos, pID = row
if gID not in games:
games[gID] = {
'date': date,
'scores': [],
'id': gID,
'unusedPoints': 0
}
if pID == scores.getUnusedPointsPlayerID():
games[gID]['unusedPoints'] = rawscore
else:
games[gID]['scores'].append(
(rank, name, rawscore, round(points, 2), chombos))
maxpage = math.ceil(gamecount * 1.0 / PERPAGE)
pages = range(max(1, page + 1 - 10),
int(min(maxpage, page + 1 + 10) + 1))
games = sorted(games.values(),
key=lambda x: (x['date'], x['id']),
reverse=True)
if page != 0:
prev = page
else:
prev = None
if page + 1 < maxpage:
nex = page + 2
else:
nex = None
self.render("history.html",
error=None,
games=games,
curpage=page + 1,
pages=pages,
gamecount=gamecount,
nex=nex,
prev=prev,
ChomboPenalty=settings.CHOMBOPENALTY)
else:
self.render("message.html",
message="No games entered thusfar",
title="Game History")
def get(self, player, page):
if page is None:
page = 0
else:
page = int(page[1:]) - 1
PERPAGE = 10
with db.getCur() as cur:
cur.execute("SELECT Id,Name FROM Players WHERE Id = ? OR Name = ?",
(player, player))
player = cur.fetchone()
if len(player) == 0:
self.render("message.html",
message="Couldn't find that player",
title="User Game History")
return
name = player[1]
player = player[0]
cur.execute(
"SELECT DISTINCT GameId FROM Scores WHERE PlayerId = ?"
" ORDER BY Date DESC", (player, ))
games = [i[0] for i in cur.fetchall()]
gamecount = len(games)
if gamecount > 0:
thesegames = games[min(page * PERPAGE, gamecount -
1):min(page * PERPAGE +
PERPAGE, gamecount)]
placeholder = '?' # For SQLite. See DBAPI paramstyle
placeholders = ', '.join(placeholder
for i in range(len(thesegames)))
cur.execute(
"SELECT Scores.GameId,"
" strftime('%Y-%m-%d', Scores.Date), Rank, Players.Name,"
" Scores.RawScore / 1000.0, Scores.Score, Scores.Chombos,"
" Players.Id"
" FROM Scores INNER JOIN Players"
" ON Players.Id = Scores.PlayerId"
" WHERE Scores.GameId IN (" + placeholders + ")"
" ORDER BY Scores.GameId, Rank ASC;", thesegames)
rows = cur.fetchall()
games = {}
for row in rows:
gID, date, rank, name, rawScore, score, chombos, pID = row
if gID not in games:
games[gID] = {
'date': date,
'scores': [],
'id': gID,
'unusedPoints': 0
}
if pID == scores.getUnusedPointsPlayerID():
games[gID]['unusedPoints'] = rawScore
else:
games[gID]['scores'].append(
(rank, name, rawScore, round(score, 2), chombos))
maxpage = math.ceil(gamecount * 1.0 / PERPAGE)
pages = range(max(1, page + 1 - 10),
int(min(maxpage, page + 1 + 10) + 1))
games = sorted(games.values(),
key=lambda x: (x["date"], x["id"]),
reverse=True)
if page != 0:
prev = page
else:
prev = None
if page + 1 < maxpage:
nex = page + 2
else:
nex = None
self.render("userhistory.html",
error=None,
games=games,
curpage=page + 1,
pages=pages,
gamecount=gamecount,
ChomboPenalty=settings.CHOMBOPENALTY,
nex=nex,
prev=prev,
user=name,
player=player)
else:
self.render("message.html",
message="No games entered thusfar",
title="Game History",
user=name)