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()