def load_logged_in_user():
user_id = session.get('user_id')
if user_id is None:
g.user = None
else:
g.user = get_db().execute('select * from users where id = ?', (user_id,)).fetchone()
def get_pick_details(event_id: int):
db = get_db()
pick_details = db.execute(
'''select pick_order, currently_picking from events where id = ?''',
[event_id]).fetchone()
return PickDetails(pick_order=json.loads(pick_details[0]),
currently_picking=pick_details[1])
def game_vote(game, vote):
vote = int(vote)
game = int(game)
db = get_db()
if abs(vote) != 1 or not db.execute('select id from games where id = ?',
[game]).fetchone():
flash(utilities.NAVY_SEAL, STYLE.warning)
return redirect(url_for('landing.landing'))
user = session.get('user_id')
cursor = db.execute('select vote from votes where user = ? and game = ?',
[user, game])
result = cursor.fetchone()
if result:
if result[0] == vote:
flash("You already voted for that!", STYLE.error)
return redirect(url_for('landing.landing'))
else:
db.execute('update votes set vote = ? where user = ? and game = ?',
[vote, user, game])
db.commit()
flash("Your vote has been changed :)!", STYLE.success)
return redirect(
url_for('landing.landing', _anchor=f"gameRow-{game}"))
else:
db.execute('insert into votes (user, game, vote) values (?, ?, ?)',
[user, game, vote])
db.commit()
flash("Thanks for voting :)", STYLE.success)
return redirect(url_for('landing.landing', _anchor=f"gameRow-{game}"))
def create_tournament(game: str):
db = get_db()
games_row = db.execute('select * from games where slug = ?',
[game]).fetchone()
if not games_row:
abort(404)
fetch_date = None
if request.method == 'POST':
response = events.handle_event_post(games_row[0])
if response:
flash(*response)
if response[1] == STYLE.success:
fetch_date = dateutil.parser.parse(request.form['start'])
prepared_schedule = events.prepare_schedule(fetch_date)
flash("ALPHA: Use only in chrome and with activated JS!", STYLE.error)
return render_template('tournament_creation.html',
page_title="Tournament erstellen",
game_row=games_row,
creators=prepared_schedule.event_creators,
tournaments=prepared_schedule.formatted_events,
party_start=get_party_start_date(),
party_end=get_party_end_date(),
fetched_date=prepared_schedule.fetch_date,
next_day=prepared_schedule.next_day,
previous_day=prepared_schedule.previous_day)
def increase_skips_for(event_id: int) -> int:
db = get_db()
db.execute('''update events set skips = skips + 1 where id = ?''',
[event_id])
db.commit()
return db.execute('select skips from events where id = ?',
[event_id]).fetchone()[0]
def administration():
db = get_db()
if request.method == 'POST':
if dateutil.parser.parse(
request.form['date_start']) >= dateutil.parser.parse(
request.form['date_end']):
flash(
Markup(
'<a href="https://www.youtube.com/watch?v=HGpr4r9X8DE">Ehh... No</a>. Choose a valid time span.'
), STYLE.error)
else:
for key, value in request.form.items():
db.execute('delete from config where key = ?', [key])
db.execute(f'insert into config (key, value) values (?, ?) ',
[key, value])
db.commit()
flash("Settings updated.", STYLE.message)
config_rows = db.execute('select * from config').fetchall()
event_start_date = lol9k1.database.get_party_start_date()
event_end_date = lol9k1.database.get_party_end_date()
return render_template('admin.html',
page_title="Administration",
config_rows=config_rows,
event_start_date=event_start_date,
event_end_date=event_end_date)
def delete_invite(token):
db = get_db()
db.execute('delete from invites where token = ? and added_by = ?',
[token, session.get('user_id')])
db.commit()
flash(Markup(f'Key <code>{token}</code> deleted.'), STYLE.message)
return redirect(url_for('invite.invite'))
def administration():
handle_form_posts()
config_rows = database.get_db().execute('select * from config').fetchall()
start_date = database.get_party_start_date()
end_date = database.get_party_end_date()
return render_template('admin.html', config_rows=config_rows,
event_start_date=start_date, event_end_date=end_date)
def get_user_by_name(name) -> Optional[User]:
try:
cursor = database.get_db().execute('select * from users where name = (?)', [name])
except sqlite3.Error:
flash(utilities.NAVY_SEAL, STYLE.warning)
return None
return User(*cursor.fetchone())
def email_already_registered():
db = get_db()
if request.form['email']:
cursor = db.execute('select email from users where email = ?', [request.form['email']])
if cursor.fetchone():
return True
return False
def __add_games_vote_row():
db = get_db()
game = db.execute('select id from games where name = ?',
[request.form['title']]).fetchone()[0]
db.execute('insert into votes (user, game, vote) values (?, ?, ?)',
[session.get('user_id'), game, 1])
db.commit()
return game
def advance_currently_picking(event_id, currently_picking_team):
db = get_db()
pick_order = get_pick_order(event_id)
next_up_team = pick_order[(pick_order.index(currently_picking_team) + 1) %
len(pick_order)]
db.execute('''update events set currently_picking = ? where id = ?''',
(next_up_team, event_id))
db.commit()
def get_possible_teammates_for(event_id):
db = get_db()
possible_teammates = db.execute(
'''
select id, name, gender from users where id not in
(select user from tournament_participants where tournament = ?)''',
[event_id]).fetchall()
return possible_teammates
def get_details(event_id) -> Details:
row = get_db().execute('''
select u.name, e.created_by, e.game, g.name, g.slug, e.mode, e.state, e.description, e.external_url from events e
join users u on e.created_by = u.id
join games g on e.game = g.id
where e.id = ?
''', [event_id]).fetchone()
event_details = Details(*row)
return event_details
def game_detail(game):
db = get_db()
game_row = db.execute('select * from games where slug = ?',
[game]).fetchone()
games_events = events.get_all_by_game(game_row[0])
return render_template('game.html',
game_row=game_row,
page_title=game_row[1],
events=games_events)
def get_all_by_game(game_id):
tournaments = get_db().execute('''
select t.id, games.name, t.start_datetime, t.end_datetime, users.name, t.mode, t.state
from events t
join games on t.game = games.id
join users on t.created_by = users.id
where t.game = ?
''', (game_id,)).fetchall()
return tournaments
def initialize_pick_phase(event_id: int):
teams = get_teams_for_json(event_id)
pick_order = [team['team_id'] for team in teams]
random.shuffle(pick_order)
db = get_db()
db.execute('''update events set state = ?, pick_order = ?, currently_picking = ? where id = ?''',
("pickphase", json.dumps(pick_order), pick_order[0], event_id))
db.commit()
return True
def get_all_participants_for(event_id):
participants_rows = get_db().execute('''
select u.name, t.name, t.id, (select u.name where u.id = tp.user and tp.is_team_captain = 1)
from tournament_participants tp
join users u on tp.user = u.id
join teams t on tp.team = t.id
where tp.tournament = ?
''', [event_id]).fetchall()
return participants_rows
def get_possible_captains_api(event_id):
db = get_db()
eligible_users = db.execute('''
select id, name, gender from users where id not in
(select user from tournament_participants where is_team_captain = 1 and tournament = ?)
''', [event_id]).fetchall()
possible_captains = []
for row in eligible_users:
possible_captains.append(row[1])
return json.dumps(possible_captains)
def delete_captains(captains: str, tournament: int):
if valid_captains_json(captains):
captains = json.loads(captains)
for captain in captains:
db = get_db()
db.execute('''
delete from tournament_participants where tournament = ? and user = ? and is_team_captain = 1
''', (tournament, captain))
else:
return utilities.NAVY_SEAL
def delete_with_all_dependencies_in_database(event_id):
db = get_db()
game = db.execute('''
select games.slug from events e join games on e.game = games.id where e.id = ?
''', [event_id]).fetchone()[0]
delete_all_teams_associated_with(event_id)
db.execute('''delete from tournament_participants where tournament = ?''', [event_id])
db.execute('''delete from events where id = ?''', [event_id])
db.commit()
return game
def get_all_by_date(fetch_date: datetime) -> List[tuple]:
tournaments = get_db().execute('''
select t.id, games.name, t.start_datetime, t.end_datetime, users.name, t.mode, t.state
from events t
join games on t.game = games.id
join users on t.created_by = users.id
where date(start_datetime) = date(?)
or date(end_datetime) = date(?)
or (date(start_datetime) < date(?) and date(end_datetime) > date(?))
''', (fetch_date, fetch_date, fetch_date, fetch_date)).fetchall()
return tournaments
def get_captains_from_db(event_id: int):
db = get_db()
captain_rows = db.execute('''
select u.name, t.user, teams.name, teams.id from tournament_participants t
join users u on t.user = u.id
join teams on t.team = teams.id
where tournament = ? and is_team_captain = 1
''', [event_id]).fetchall()
captains = []
for row in captain_rows:
captains.append(Captain(*row))
return captains
def invite():
db = get_db()
cursor = db.execute('select count(token) from invites where added_by = ? and used = 0', [session.get('user_id')])
if authentication.current_user_is_admin():
tokens_left = "∞"
else:
tokens_left = current_app.config.get('MAX_INVITE_TOKENS') - int(cursor.fetchall()[0][0])
if tokens_left < 1:
tokens_left = None
cursor = db.execute('select token, used from invites where added_by = ?', [session.get('user_id')])
invites = cursor.fetchall()
return render_template('invite.html', invites=invites, tokens_left=tokens_left)
def __add_games_row(available, description, low_spec, max_players, no_drm,
title):
db = get_db()
db.execute(
'insert into games (name, slug, max_players, description, no_drm, available, low_spec, added_by) '
'values (?, ?, ?, ?, ?, ?, ?, ?)', [
title,
slugify(title), max_players, description, no_drm, available,
low_spec,
session.get('user_id')
])
db.commit()
def event(event_id):
event_details = get_details(event_id) # type: Details
if not event_details:
# user tries to call nonexistent event
flash("The selected event does not exist (anymore).", STYLE.error)
return redirect(url_for('landing.landing'))
page_title = f"{event_details.game} Event #{event_id} - by {event_details.creator}"
if event_details.state:
return render_template('pick.html',
page_title=page_title,
event_id=event_id,
event=event_details,
user_id=session.get('user_id'))
if request.method == 'POST':
handle_url_post(event_id)
handle_description_post(event_id)
handle_captains_post(event_id)
event_details = get_details(event_id)
participants_rows = get_all_participants_for(event_id)
teams = build_teams(participants_rows)
if request.method == 'POST' and 'initiate-picking' in request.form:
if not teams:
flash(
'Picking teams does only make sense when teams exist :> '
'You need to assign team captains for that.', STYLE.message)
return redirect(url_for('.event', event_id=event_id))
initialize_pick_phase(event_id)
return render_template('pick.html',
page_title=page_title,
event_id=event_id,
event=event_details)
db = get_db()
users = db.execute(
'''select id, name, gender from users order by name asc''').fetchall()
captain_rows = db.execute(
'''select user from tournament_participants where is_team_captain = 1 and tournament = ?''',
[event_id]).fetchall()
captains = [row[0] for row in captain_rows]
has_participants = False
if teams:
for team in teams.values():
if len(team.players) > 1:
has_participants = True
break
return render_template('event.html',
page_title=page_title,
event_id=event_id,
event=event_details,
teams=teams,
users=users,
captains=captains,
hasParticipants=has_participants)
def generate_invite():
db = get_db()
cursor = db.execute('select count(token) from invites where added_by = ? and used = 0', [session.get('user_id')])
tokens = cursor.fetchall()[0][0]
if tokens < 3 or authentication.current_user_is_admin():
token = uuid.uuid4().hex[:12]
db.execute('insert into invites (token, used, added_by) values (?, ?, ?)',
[token, 0, int(session.get('user_id'))])
db.commit()
else:
return abort(403)
flash(Markup(f'Key <code style="user-select: all;">{token}</code> created.'), STYLE.message)
return redirect(url_for('invite.invite'))
def add_user(form: RegistrationForm) -> None:
is_admin = is_admin_token(form.token.data)
db = database.get_db()
try:
# add user
db.execute('insert into users (name, password, email, gender, is_admin, token_used) '
'values (?, ?, ?, ?, ?, ?)',
[request.form['name'], generate_password_hash(request.form['password']),
request.form['email'], request.form['gender'], is_admin, request.form['token']])
except sqlite3.IntegrityError:
raise RegistrationError("Registration failed.")
db.execute('update invites set used = 1 where token = ?', [request.form['token']])
db.commit()
def delete_game(game_id: int):
db = get_db()
game = db.execute('select name from games where id = ?',
[game_id]).fetchone()[0]
try:
db.execute('delete from votes where game = ?', [game_id])
db.execute('delete from events where game = ?', [game_id])
db.execute('delete from games where id = ?', [game_id])
db.commit()
except sqlite3.Error as e:
flash(f'Something went wrong during deletion of game "{game}": {e}',
STYLE.error)
flash(f"Game {game} deleted.", STYLE.message)
return redirect(url_for('landing.landing'))
def update_captains(event_id, captains):
# reset captains and teams
db = get_db()
delete_all_teams_associated_with(event_id)
db.execute('''delete from tournament_participants where tournament = ?''', [event_id])
captains_teams = []
for captain in captains:
cur = db.execute('''insert into teams (captain) values (?)''', [int(captain)])
captains_teams.append((captain, cur.lastrowid))
# set captains
for captain_team in captains_teams:
db.execute('''
insert into tournament_participants (user, tournament, team, is_team_captain)
values (?, ?, ?, 1)
''', (captain_team[0], event_id, captain_team[1]))
db.commit()
