def getTournaments(tournamentID=None):
"""Get list of tournament dictionaries. If a tournament ID is provided
the list will either have 1 dictionary, or be empty if the ID is bad.
The fields in the dictionary are all the tournament fields in the
Tournaments table + Code and Flag_Image for the country, a field
for players that has a dictionary that maps keys of each type of player
to lists of player dictionaries with Id, Name, Association, and
NumberScores fields
"""
tmt_fields = db.table_field_names('Tournaments')
country_fields = ['Code', 'Flag_Image']
with db.getCur() as cur:
sql = """
SELECT {} FROM Tournaments JOIN Countries ON Country = Countries.ID
""".format(','.join(['Tournaments.{}'.format(f)
for f in tmt_fields] + country_fields))
if isinstance(tournamentID, int):
sql += "WHERE Tournaments.ID = {}".format(tournamentID)
sql += " ORDER BY End DESC"
cur.execute(sql)
tournaments = [
dict(zip(tmt_fields + country_fields, row))
for row in cur.fetchall()
]
for tmt in tournaments:
tmt['Dates'] = '{} - {}'.format(tmt['Start'], tmt['End'])
tmt['players'] = dict((t, list()) for t in db.playertypes)
for compete in getCompetitors(tmt['Id'], tmt['Owner']):
tmt['players'][db.playertypes[compete['Type']]].append(compete)
return tournaments
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 playerColumns():
player_fields = [
f for f in db.table_field_names('Players') if f not in ('ReplacedBy')
]
columns = ["Players.{}".format(f) for f in player_fields] + [
"Flag_Image", "COUNT(DISTINCT Tournaments.Id)",
"MAX(Tournaments.Start)", "COUNT(Scores.Id)"
]
colnames = player_fields + ["Flag", "Tournaments", "Latest", "Scores"]
colheads = [{
"Name": util.prettyWords(col)
} for col in colnames if col not in ('Id', 'Flag_Image', 'Scores')]
colheads[len(player_fields) - 1]["Attrs"] = "colspan=2"
return columns, colnames, colheads
def get(self, playerspec):
with db.getCur() as cur:
columns = db.table_field_names('Players')
cur.execute(
"SELECT {} FROM Players WHERE Id = ? OR Name = ?".format(
','.join(columns)), (playerspec, playerspec))
player = cur.fetchone()
if player is None or len(player) == 0:
self.write(
json.dumps({
'status': 1,
'error': "Couldn't find player " + name
}))
return
player = dict(zip(columns), player)
playerID = player['Id']
playerTourneys = playersTournaments(playerID, cur)
tourneys = getPlayerTournamentData(playerID, playerTourneys, cur)
self.write({
'status': 0,
'playerstats': tourneys,
})
def combine_player_records(self, players, colnames):
res = {}
playerFields = [
f for f in db.table_field_names('Players')
if f not in ('Id', 'ReplacedBy')
]
for col in colnames: # For each column, histogram values
if col not in playerFields:
continue
hist = defaultdict(lambda: 0)
for player in players:
val = "" if player[col] is None else player[col]
hist[val] += 1
# Sort possible values by non-null flag, number of occurrences,
# string length or numeric value, # of uppercase letters
metrics = [(val, 1 if len(str(val)) > 0 else 0, hist[val],
val if isinstance(val, int) else len(val),
sum(1 if c.isupper() else 0
for c in val) if isinstance(val, str) else 0)
for val in hist]
metrics.sort(key=lambda tupl: tupl[1:])
res[col] = metrics[-1][0] # Take highest after sort
return res
def getCompetitors(tournamentID, tournamentOwner=0):
competing_player_fields = ['Name', 'Association', 'Country']
cFields = [
'Compete.{}'.format(f) for f in db.table_field_names('Compete')
] + ['Players.{}'.format(f) for f in competing_player_fields]
sql = """
SELECT {}, COUNT(DISTINCT Scores.Id)
FROM Compete JOIN Players on Player = Players.Id
LEFT OUTER JOIN Rounds ON Rounds.Tournament = Compete.Tournament
LEFT OUTER JOIN Scores
ON Scores.Round = Rounds.Id AND Scores.PlayerId = Players.Id
WHERE Compete.Tournament = ?
GROUP BY Player
ORDER BY Type ASC, Players.Name ASC
""".format(','.join(cFields))
args = (tournamentID, )
with db.getCur() as cur:
cur.execute(sql, args)
competitors = [
dict(
zip(map(db.fieldname, cFields + ['NumberScores', 'Owner']),
row + (tournamentOwner, ))) for row in cur.fetchall()
]
return competitors
def post(self):
global sql, args
encoded_item = self.get_argument('item', None)
item = json.loads(encoded_item)
result = {'status': 0, 'message': ''}
if item.get('Id', None) is None or not isinstance(item['Id'], int):
item['Flag_Image'] = ''
result['message'] = 'Invalid Id field for player, {}'.format(item)
result['status'] = -1
self.write(result)
return
if not isinstance(item.get('Country', None), int) and item['Id'] >= 0:
item['Flag_Image'] = ''
result['message'] = 'Invalid Country for Player, {}'.format(item)
result['status'] = -2
self.write(result)
return
id = abs(item['Id'])
result['message'] = 'Item {}'.format(
'inserted' if id ==
0 else 'deleted' if item['Id'] < 0 else 'updated')
if item['Id'] < 0:
db.make_backup()
player_fields = [
f for f in db.table_field_names('Players')
if f not in ('ReplacedBy')
]
columns = [f for f in player_fields if f in item and f not in ['Id']]
cleanPlayerItem(item, columns)
try:
with db.getCur() as cur:
if id > 0:
sql, args = 'SELECT Id FROM Players WHERE Id = ?', (id, )
cur.execute(sql, args)
matches = len(cur.fetchall())
if matches == 0:
result['message'] = 'No player with Id {}'.format(id)
result['status'] = -3
elif matches > 1:
result['message'] = (
'Multiple players with Id {}'.format(id))
result['status'] = -4
if item['Id'] >= 0:
sql = 'SELECT Id FROM Countries WHERE Id = ?'
args = (item['Country'], )
cur.execute(sql, args)
matches = len(cur.fetchall())
if matches != 1:
result['message'] = (
'Invalid Country for Player {}'.format(item))
result['status'] = -5
if result['status'] == 0:
values = [item[f] for f in columns]
if item['Id'] < 0:
sql = 'DELETE FROM Players WHERE Id = ?'
args = (id, )
elif item['Id'] == 0:
sql = 'INSERT INTO Players ({}) VALUES ({})'.format(
', '.join(columns),
', '.join(['?' for v in values]))
args = values
else:
sql = 'UPDATE Players SET {} WHERE Id = ?'.format(
', '.join('{} = ?'.format(f) for f in columns))
args = values + [id]
log.info('Executing "{}" on {}'.format(sql, args))
cur.execute(sql, args)
if item['Id'] == 0:
item['Id'] = cur.lastrowid
log.info('Last Player Row ID is now {}'.format(
item['Id']))
item['Id'] = abs(item['Id']) # Put cleaned item record
result['item'] = item # with correct Id in rsespone
except sqlite3.DatabaseError as e:
result['message'] = (
'Exception in database change. SQL = {}. Args = {}. {}'.format(
sql, args, e))
log.error(result['message'])
result['status'] = -10
self.write(result)
def getTourney(self, tournamentid):
stage_fields = db.table_field_names('Stages')
with db.getCur() as cur:
cur.execute(
"""SELECT {} FROM Stages WHERE Tournament = ?
ORDER BY SortOrder, Id""".format(
",".join(stage_fields)), (tournamentid, ))
stages = dict(
(row[0], dict(zip(stage_fields, row))) for row in cur.fetchall())
roots = [id for id in stages if not stages[id]['PreviousStage']]
if len(roots) > 1:
raise Exception("Tournament {} has multiple initial stages".format(
tournamentid))
elif len(roots) == 0 and len(stages) > 0:
raise Exception("Tournament {} has no initial stage but a cycle "
"of others".format(tournamentid))
elif len(roots) == 0:
stage = {
'Tournament': tournamentid,
'Name': 'Round Robin',
'SortOrder': 0,
'PreviousStage': None,
'Ranks': None,
'Cumulative': 0,
}
cur.execute("""INSERT INTO Stages ({}) VALUES ({})""".format(
','.join(f for f in stage if stage[f] is not None), ','.join(
repr(stage[f]) for f in stage if stage[f] is not None)))
stage['Id'] = cur.lastrowid
stages = {cur.lastrowid: stage}
roots = [cur.lastrowid]
# List stages starting with root and then successor children, in order
stagelist = [stages[roots[0]]]
todo = stages.copy()
del todo[roots[0]]
while len(todo) > 0:
children = [
id for id in stages if stages[id]['PreviousStage'] in roots
]
for child in sorted(children, key=lambda s: s['SortOrder']):
stages[child]['previousName'] = stages[
stages[child]['PreviousStage']]['Name']
stagelist.append(stages[child])
del todo[child]
roots.append(child)
cols = [
"id", "number", "name", "ordering", "algorithm", "seed", "cut",
"softcut", "cutsize", "cutmobility", "combinelastcut",
"duplicates", "diversity", "usepools", "winds", "games"
]
for stage in stagelist[:1]:
cur.execute(
"""SELECT Id, Number, Name, COALESCE(Ordering, 0),
COALESCE(Algorithm, 0), Seed,
Cut, SoftCut, CutSize, CutMobility, CombineLastCut,
Duplicates, Diversity, UsePools, Winds, Games
FROM Rounds WHERE Tournament = ?
ORDER BY Number""", (tournamentid, ))
stage['rounds'] = []
for row in cur.fetchall():
roundDict = dict(zip(cols, row))
roundDict["orderingname"] = seating.ORDERINGS[
roundDict["ordering"]][0]
roundDict["algname"] = seating.ALGORITHMS[
roundDict["algorithm"]].name
roundDict["seed"] = roundDict["seed"] or ""
stage['rounds'] += [roundDict]
cur.execute(
'SELECT COALESCE(ScorePerPlayer, {}) FROM Tournaments'
' WHERE Id = ?'.format(settings.DEFAULTSCOREPERPLAYER),
(tournamentid, ))
result = cur.fetchone()
if result is None:
return None
scorePerPlayer = result[0]
return {
'stages': stagelist,
'scoreperplayer': scorePerPlayer,
'unusedscoreincrement': settings.UNUSEDSCOREINCREMENT,
'cutsize': settings.DEFAULTCUTSIZE
}
def get(self, id=None):
ctry_fields = db.table_field_names('Countries')
tmt_fields = EditTournamentHandler.tmt_fields
tmt_attr_fields = [f for f in tmt_fields if f != 'Id']
with db.getCur() as cur:
cur.execute("SELECT {} FROM Countries".format(
",".join(ctry_fields)))
ctry_mapping = [(row[0], dict(zip(ctry_fields, row)))
for row in cur.fetchall()]
countries = dict(ctry_mapping)
def_country_id = ctry_mapping[0][1]["Id"]
cur.execute("SELECT Id, Email FROM Users ORDER BY Email")
users = dict(cur.fetchall())
if id:
cur.execute(
"SELECT {} FROM TOURNAMENTS WHERE Id = ?".format(
",".join(tmt_fields)), (id, ))
results = cur.fetchall()
if len(results) == 0:
return self.render("message.html",
message="Invalid tournament.",
next="Return to Tournament List",
next_url=settings.PROXYPREFIX)
elif len(results) > 1:
return self.render(
"message.html",
message="ERROR multiple tournaments with ID {}."
" Contact site administrator.".format(id),
next="Return to Tournament List",
next_url=settings.PROXYPREFIX)
tournament = dict(zip(tmt_fields, results[0]))
cur.execute(
"SELECT Name FROM Players"
" JOIN Compete on Compete.Player = Players.Id"
" WHERE Tournament = ?"
" ORDER BY Name", (id, ))
tournament['Players'] = [row[0] for row in cur.fetchall()]
cur.execute(
"SELECT COUNT(Scores.Id) FROM Scores"
" JOIN Rounds ON Scores.Round = Rounds.Id"
" WHERE Rounds.Tournament = ?", (id, ))
tournament['ScoreCount'] = cur.fetchone()[0]
else:
tournament = dict(zip(tmt_fields, [''] * len(tmt_fields)))
today = date.today()
tomorrow = today + timedelta(days=1)
tournament['Start'] = today.strftime(settings.DATEFORMAT)
tournament['End'] = tomorrow.strftime(settings.DATEFORMAT)
tournament['Country'] = def_country_id
tournament['Owner'] = int(self.current_user)
tournament['Players'] = []
tournament['ScorePerPlayer'] = settings.DEFAULTSCOREPERPLAYER
tournament['ScoreCount'] = 0
sql = "INSERT INTO Tournaments ({}) VALUES ({})".format(
', '.join(tmt_attr_fields),
', '.join(['?'] * len(tmt_attr_fields)))
try:
cur.execute(sql, [tournament[f] for f in tmt_attr_fields])
print('Created tournament', cur.lastrowid)
tournament['Id'] = cur.lastrowid
except sqlite3.DatabaseError as e:
print('Error creating tournament ({}): {}'.format(sql, e))
return self.render(
'message.html',
message='Unable to create tournament: {}'.format(e))
tournament['OwnerName'] = users[int(tournament['Owner'])]
tournament['CountryCode'] = countries[
tournament['Country']]['Code']
tournament['CountryName'] = countries[
tournament['Country']]['Name']
tournament['Flag_Image'] = countries[
tournament['Country']]['Flag_Image']
if tournament['Owner'] == int(
self.current_user) or self.get_is_admin():
return self.render("edittournament.html",
tournament=tournament,
users=users)
else:
return self.render(
"message.html",
message="ERROR only the tournament owner or administrators"
" may edit tournament attributes".format(id),
next="Return to Tournaments Home",
next_url=settings.PROXYPREFIX)
def get(self, player):
HISTORY_COOKIE = "stats_history"
with db.getCur() as cur:
name = player
cols = [
'Players.{}'.format(f) for f in db.table_field_names('Players')
] + ['Code', 'Flag_Image'] + [
f for f in db.table_field_names('Compete') if f not in ('Id', )
]
sql = """
SELECT {}
FROM Players LEFT OUTER JOIN Countries
ON Countries.Id = Players.Country
LEFT OUTER JOIN Compete ON Compete.Player = Players.Id
WHERE Players.Id = ? OR Players.Name = ?""".format(
','.join(cols))
cur.execute(sql, (player, player))
player = cur.fetchone()
if player is None or len(player) == 0:
return self.render(
"playerstats.html",
player={'Name': name},
error="Couldn't find player {}".format(name),
tourneys=[],
history=[],
playertypes=db.playertypes,
selectedTournament='all')
player = dict(zip(map(db.fieldname, cols), player))
playerTourneys = playersTournaments(player['Id'], cur)
selectedTournament = self.get_argument("tournament", None)
if selectedTournament and isinstance(selectedTournament, str):
if selectedTournament != 'all' and selectedTournament.isdigit(
):
if not int(selectedTournament) in [
t['Id'] for t in playerTourneys
]:
log.error('Request for stats on tournament ID {} '
'but player ID {} did not compete in that '
'tournament'.format(selectedTournament,
playerID))
selectedTournament = None
tourneys = getPlayerTournamentData(player['Id'], playerTourneys,
cur)
history = stringify(self.get_secure_cookie(HISTORY_COOKIE))
if history is None:
history = []
else:
history = json.loads(history)
playerKey = [player['Id'], player['Name']]
if playerKey in history:
history.remove(playerKey)
history.insert(0, playerKey)
history = history[0:settings.STATSHISTORYSIZE]
self.set_secure_cookie(HISTORY_COOKIE, json.dumps(history))
return self.render("playerstats.html",
error=None,
player=player,
tourneys=tourneys,
history=history,
playertypes=db.playertypes,
selectedTournament=selectedTournament)
def post(self):
encoded_item = self.get_argument('item', None)
item = json.loads(encoded_item)
result = {'status': 0, 'message': ''}
ID_fields = ('Id', 'Player', 'Tournament')
update_fields = [
f for f in db.table_field_names('Compete') if f not in ID_fields
]
if not all(
isinstance(item.get(field, None), int) for field in ID_fields):
result['message'] = 'Invalid item in request'
result['status'] = -1
return self.write(result)
id = abs(item['Id'])
if id != 0 and not handler.valid_ID(
id,
'Compete',
response=result,
msg='Invalid competitor in tournament.'):
return self.write(result)
if not handler.valid_ID(item['Player'], 'Players', response=result):
return self.write(result)
if not handler.valid_ID(
item['Tournament'], 'Tournaments', response=result):
return self.write(result)
id = abs(item['Id'])
result['message'] = 'Competitor {}'.format(
'inserted' if id ==
0 else 'deleted' if item['Id'] < 0 else 'updated')
try:
with db.getCur() as cur:
sql = """SELECT Id FROM Compete WHERE Player = ? AND
Tournament = ?"""
args = (item['Player'], item['Tournament'])
cur.execute(sql, args)
matches = cur.fetchall()
if len(matches) != 1 and id != 0:
result['message'] = 'No player {} in tournament {}'.format(
args)
result['status'] = -6
elif len(matches) == 1 and id == 0:
result['message'] = (
'Player {} already in tournament {}'.format(args))
result['status'] = -6
elif len(matches) == 1 and id != matches[0][0]:
result['message'] = (
'Internal error: Player {} already in tournament {} '
'with a different compete ID {}'.format(args +
matches[0]))
result['status'] = -6
elif len(matches) > 1:
result['message'] = (
'Internal error: multiple compete records {}-{}'.
format(args))
result['status'] = -7
if result['status'] == 0:
if item['Id'] < 0:
sql = 'DELETE FROM Compete WHERE Id = ?'
args = (id, )
else:
fields = ['Player', 'Tournament'] + [
f for f in update_fields
if f in item and item[f] is not None
]
if id > 0:
fields.append('Id')
values = [item[f] for f in fields]
sql = '''
INSERT OR REPLACE INTO Compete ({}) VALUES ({})
'''.format(', '.join(fields),
', '.join(['?' for v in values]))
args = values
log.debug('Executing "{}" on {}'.format(sql.strip(), args))
cur.execute(sql, args)
if id == 0:
item['Id'] = cur.lastrowid
log.debug('Last Compete row ID is now {}'.format(
item['Id']))
item['Id'] = abs(item['Id']) # Put cleaned item record
result['item'] = item # with correct Compete Id in respone
except sqlite3.DatabaseError as e:
result['message'] = (
'Exception in database change. SQL = {}. Args = {}. {}'.format(
sql, args, e))
log.error(result['message'])
result['status'] = -10
return self.write(result)
def post(self):
encoded_item = self.get_argument('item', None)
item = json.loads(encoded_item)
result = {'status': 0, 'message': ''}
tmt_fields = db.table_field_names('Tournaments')
cleanTournamentItem(item, tmt_fields, self.current_user)
columns = [f for f in tmt_fields if f in item and f not in ['Id']]
if item.get('Id', None) is None or not isinstance(item['Id'], int):
result['message'] = 'Invalid Id field for tournament, {}'.format(
item)
result['status'] = -1
return self.write(result)
if (item.get('Name', None) is None
or not isinstance(item['Name'], str)
or len(item['Name']) == 0):
result['message'] = 'Missing name for tournament'
result['status'] = -1
return self.write(result)
if (not self.get_is_admin() and item['Id'] != 0
and str(item.get('Owner', -1)) != self.current_user):
result['message'] = 'Only owners and admins may edit tournaments'
result['status'] = -2
return self.write(result)
if not handler.valid_ID(
item.get('Country', None), 'Countries', response=result):
return self.write(result)
id = abs(item['Id'])
if id != 0 and not handler.valid_ID(id, 'Tournaments',
response=result):
return self.write(result)
if not handler.valid_ID(item.get('Owner', -1),
'Users',
response=result,
msg='Invalid tournament owner'):
return self.write(result)
if item['Id'] >= 0:
if len(item['End']) == 0 or len(item['Start']) == 0:
result['message'] = 'Missing Start or End date'
result['status'] = -7
return self.write(result)
elif item['End'] < item['Start']:
result[
'message'] = 'End date must follow start date {}'.format(
item['Start'])
result['status'] = -7
return self.write(result)
result['message'] = 'Item {}'.format(
'inserted' if id ==
0 else 'deleted' if item['Id'] < 0 else 'updated')
if item['Id'] < 0:
db.make_backup()
try:
with db.getCur() as cur:
values = [item[f] for f in columns]
if item['Id'] < 0:
sql = 'DELETE FROM Tournaments WHERE Id = ?'
args = (id, )
elif item['Id'] == 0:
sql = 'INSERT INTO Tournaments ({}) VALUES ({})'.format(
', '.join(columns), ', '.join(['?' for v in values]))
args = values
else:
sql = 'UPDATE Tournaments SET {} WHERE Id = ?'.format(
', '.join('{} = ?'.format(f) for f in columns))
args = values + [id]
log.debug('Executing "{}" on {}'.format(sql, args))
cur.execute(sql, args)
if item['Id'] == 0:
item['Id'] = cur.lastrowid
log.info('Last Tournament Row ID is now {}'.format(
item['Id']))
item['Id'] = abs(item['Id']) # Put cleaned item record
result['item'] = item # with correct Id in rsespone
except sqlite3.DatabaseError as e:
result['message'] = (
'Exception in database change. SQL = {}. Args = {}. {}'.format(
sql, args, e))
log.error(result['message'])
result['status'] = -10
return self.write(result)
def makeSettingsSheet(book, tournamentID, tournamentName, sheet=None):
if sheet is None:
sheet = book.create_sheet()
sheet.title = 'Settings'
tmt_fields = [
f for f in db.table_field_names('Tournaments')
if f not in ('Id', 'Name', 'Country', 'Owner')
]
rounds_fields = [
f for f in db.table_field_names('Rounds')
if f not in ('Id', 'Tournament')
]
query_fields = ['Countries.Code', 'Email'] + [
'Tournaments.{}'.format(f) for f in tmt_fields
] + ['Rounds.{}'.format(f) for f in rounds_fields]
round_display_fields = [
'Name', 'Number', 'Ordering', 'Algorithm', 'CutSize', 'Games'
]
sql = """
SELECT {} FROM Tournaments
LEFT OUTER JOIN Countries ON Countries.Id = Tournaments.Country
LEFT OUTER JOIN Users ON Users.Id = Tournaments.Owner
LEFT OUTER JOIN Rounds ON Rounds.Tournament = Tournaments.Id
WHERE Tournaments.Id = ?
""".strip().format(','.join(query_fields))
args = (tournamentID, )
with db.getCur() as cur:
cur.execute(sql, args)
rounds = [
dict(zip(map(db.fieldname, query_fields), row))
for row in cur.fetchall()
]
header_row = 3
first_column = 1
row = header_row
merge_cells(sheet,
header_row - 2,
first_column,
1,
max(2, len(round_display_fields)),
font=title_font,
border=thin_outline,
value='{} Settings'.format(tournamentName))
sheet.row_dimensions[header_row - 2].height = title_font.size * 3 // 2
top_left = first_column + (len(round_display_fields) - 2) // 2
for field in tmt_fields + ['Code']:
if field not in ('Name', ):
namecell = sheet.cell(row,
top_left,
value='Country ' +
field if field == 'Code' else 'Owner ' +
field if field == 'Email' else field)
valuecell = sheet.cell(row, top_left + 1, value=rounds[0][field])
row += 1
row += 2
for i, field in enumerate(round_display_fields):
cell = sheet.cell(
row,
first_column + i,
value='Seating Algorithm' if field == 'Algortihm' else
'Cut Size' if field == 'CutSize' else 'Round Name' if field ==
'Name' else 'Round Number' if field == 'Number' else field)
cell.font = column_header_font
cell.alignment = top_center_align
if len(rounds) == 1 and not rounds[0]['Name']:
row += 1
merge_cells(sheet,
row,
first_column,
1,
len(round_display_fields),
font=default_font,
value='No rounds defined')
else:
for round in rounds:
row += 1
for i, field in enumerate(round_display_fields):
cell = sheet.cell(
row,
first_column + i,
value=seating.ALGORITHMS[round[field] or 0].name
if field == 'Algorithm' else seating.ORDERINGS[round[field]
or 0][0]
if field == 'Orderings' else round[field])
for col in range(first_column,
first_column + max(2, len(round_display_fields))):
resizeColumn(sheet, col, min_row=header_row)
return sheet
prefix='{}_'.format(util.makeFilename(
self.tournamentname))) as outf:
book.save(outf.name)
outf.seek(0)
self.write(outf.read())
self.set_header('Content-type', excel_mime_type)
self.set_header(
'Content-Disposition', 'attachment; filename="{}"'.format(
os.path.basename(outf.name)))
log.debug('Temporary file: {}'.format(outf.name))
return
player_columns = [c.lower() for c in Player_Columns]
player_ID_fields = [
f.capitalize() for f in db.table_field_names('Players')
if f not in ('Id', 'ReplacedBy')
]
competition_fields = [
f for f in db.table_field_names('Compete') if f not in ('Id', )
]
class UploadPlayersHandler(handler.BaseHandler):
@handler.is_authenticated_ajax
def post(self):
tournament.initializeCountryLookup()
encoded_players = self.get_argument('players', None)
players = json.loads(encoded_players)
response = {
'status': 0,
gamescores = json.loads(gamescores)
with db.getCur() as cur:
cur.execute("SELECT GameId FROM Scores WHERE GameId = ?", (q, ))
row = cur.fetchone()
if len(row) == 0:
self.write('{"status":1, "error":"Game not found"}')
return
gameid = row[0]
db.make_backup()
self.write(json.dumps(scores.addGame(gamescores, gamedate, gameid)))
quarterFields = db.table_field_names('Quarters')
class EditQuarterHandler(handler.BaseHandler):
@handler.is_admin
def get(self, q):
settingsDescriptions = getSettingsDescriptions()
helptext = '<ul>'
for field in ['DropGameCount'] + quarterFields:
if field.upper() in settingsDescriptions:
helptext += '<li><b><em>{}</em></b>'.format(
splitCamelCase(field))
fieldhelp = settingsDescriptions[field.upper()]
if fieldhelp.startswith(field.upper()):
helptext += fieldhelp[len(field):]
else:
#!/usr/bin/env python3
import json
import collections
import db
import handler
import settings
import scores
LBDcolumns = [
col for col in db.table_field_names('Leaderboards')
if col not in ['Place']
]
periods = {
"annual": {
"queries": [
"""SELECT
'annual',
{datefmt},
PlayerId,
ROUND(SUM(Scores.Score) * 1.0 / COUNT(Scores.Score) * 100)
/ 100 AS AvgScore,
COUNT(Scores.Score) AS GameCount,
0,
COUNT(DISTINCT Date) AS DateCount
FROM Scores
WHERE PlayerId != ? AND {datetest}
GROUP BY {datefmt},PlayerId
ORDER BY AvgScore DESC;"""
],
class EditTournamentHandler(handler.BaseHandler):
tmt_fields = db.table_field_names('Tournaments')
@tornado.web.authenticated
def get(self, id=None):
ctry_fields = db.table_field_names('Countries')
tmt_fields = EditTournamentHandler.tmt_fields
tmt_attr_fields = [f for f in tmt_fields if f != 'Id']
with db.getCur() as cur:
cur.execute("SELECT {} FROM Countries".format(
",".join(ctry_fields)))
ctry_mapping = [(row[0], dict(zip(ctry_fields, row)))
for row in cur.fetchall()]
countries = dict(ctry_mapping)
def_country_id = ctry_mapping[0][1]["Id"]
cur.execute("SELECT Id, Email FROM Users ORDER BY Email")
users = dict(cur.fetchall())
if id:
cur.execute(
"SELECT {} FROM TOURNAMENTS WHERE Id = ?".format(
",".join(tmt_fields)), (id, ))
results = cur.fetchall()
if len(results) == 0:
return self.render("message.html",
message="Invalid tournament.",
next="Return to Tournament List",
next_url=settings.PROXYPREFIX)
elif len(results) > 1:
return self.render(
"message.html",
message="ERROR multiple tournaments with ID {}."
" Contact site administrator.".format(id),
next="Return to Tournament List",
next_url=settings.PROXYPREFIX)
tournament = dict(zip(tmt_fields, results[0]))
cur.execute(
"SELECT Name FROM Players"
" JOIN Compete on Compete.Player = Players.Id"
" WHERE Tournament = ?"
" ORDER BY Name", (id, ))
tournament['Players'] = [row[0] for row in cur.fetchall()]
cur.execute(
"SELECT COUNT(Scores.Id) FROM Scores"
" JOIN Rounds ON Scores.Round = Rounds.Id"
" WHERE Rounds.Tournament = ?", (id, ))
tournament['ScoreCount'] = cur.fetchone()[0]
else:
tournament = dict(zip(tmt_fields, [''] * len(tmt_fields)))
today = date.today()
tomorrow = today + timedelta(days=1)
tournament['Start'] = today.strftime(settings.DATEFORMAT)
tournament['End'] = tomorrow.strftime(settings.DATEFORMAT)
tournament['Country'] = def_country_id
tournament['Owner'] = int(self.current_user)
tournament['Players'] = []
tournament['ScorePerPlayer'] = settings.DEFAULTSCOREPERPLAYER
tournament['ScoreCount'] = 0
sql = "INSERT INTO Tournaments ({}) VALUES ({})".format(
', '.join(tmt_attr_fields),
', '.join(['?'] * len(tmt_attr_fields)))
try:
cur.execute(sql, [tournament[f] for f in tmt_attr_fields])
print('Created tournament', cur.lastrowid)
tournament['Id'] = cur.lastrowid
except sqlite3.DatabaseError as e:
print('Error creating tournament ({}): {}'.format(sql, e))
return self.render(
'message.html',
message='Unable to create tournament: {}'.format(e))
tournament['OwnerName'] = users[int(tournament['Owner'])]
tournament['CountryCode'] = countries[
tournament['Country']]['Code']
tournament['CountryName'] = countries[
tournament['Country']]['Name']
tournament['Flag_Image'] = countries[
tournament['Country']]['Flag_Image']
if tournament['Owner'] == int(
self.current_user) or self.get_is_admin():
return self.render("edittournament.html",
tournament=tournament,
users=users)
else:
return self.render(
"message.html",
message="ERROR only the tournament owner or administrators"
" may edit tournament attributes".format(id),
next="Return to Tournaments Home",
next_url=settings.PROXYPREFIX)
@tornado.web.authenticated
def post(self, id):
tmt_fields = EditTournamentHandler.tmt_fields
tmt_attr_fields = [f for f in tmt_fields if f != 'Id']
state = {}
for f in tmt_fields:
state[f] = self.get_argument(f, None)
if self.get_is_admin() or int(state['Owner']) == int(
self.current_user):
msg = 'Updated tournament {}'.format(state['Id'])
if id == state['Id']:
with db.getCur() as cur:
try:
for f in tmt_attr_fields:
sql = ("UPDATE Tournaments SET {} = ?"
" WHERE Id = ?").format(f)
cur.execute(sql, (state[f], id))
return self.write({'status': 'success', 'message': ''})
except sqlite3.DatabaseError as e:
print('Error updating tournament ({}): {}'.format(
sql, e))
return self.write({
'status':
'Error',
'message':
'Unable to update tournament {}: {}'.format(id, e)
})
elif id and int(state['Id']) < 0:
with db.getCur() as cur:
try:
sql = "DELETE FROM Tournaments WHERE Id = ?"
cur.execute(sql, (id, ))
return self.write({
'status': 'load',
'message': 'Tournament Deleted',
'URL': settings.PROXYPREFIX
})
except sqlite3.DatabaseError as e:
print('Error deleting tournament ({}): {}'.format(
sql, e))
return self.write({
'status':
'Error',
'message':
'Unable to update tournament {}: {}'.format(id, e)
})
else:
return self.write({
'status':
'Error',
'message':
'Inconsistent IDs {} and {}'.format(id, state['Id'])
})
else:
self.write({
'status':
'Error',
'message':
'You are not authorized to edit or create '
'that tournament.'
})