def register(): if request.method == 'POST': username = request.form['username'] password = request.form['password'] con = get_con() error = None db = con.cursor() if not username: error = 'Username is required.' elif not password: error = 'Password is required.' res = select_user(username) if res is not None: error = 'User {} is already registered.'.format(username) if error is None: create_user(username, password, status='admin') return redirect(url_for('auth.user_page')) flash(error) return render_template('auth/register.html')
def get_games_team(team_id): con = get_con() curs = con.cursor() curs.prepare(""" SELECT g.game_id, g.result, LISTAGG (s.ResultSet_1 | | ':' | | s.ResultSet_2, ', ') WITHIN GROUP (ORDER BY g.game_id) sets, t1.TeamName | | '-' | | t2.TeamName teams FROM Games g JOIN Teams t1 ON g.Team1_Id = t1.Team_id JOIN teams t2 ON g.Team2_id = t2.Team_id JOIN sets_m s ON g.game_id = s.game_id WHERE t1.team_id = :team_id OR t2.team_id = :team_id GROUP BY g.GAME_ID, g.Result, t1.TeamName, t2.TeamName """) games = curs.execute(None, {'team_id': team_id}) return get_column_name(games.fetchall(), curs)
def create_user(username, password, status='admin'): con = get_con() db = con.cursor() db.prepare(""" INSERT INTO Users (username, password, status) VALUES (:username, :password, :status) """) db.execute(None, {'username': username, 'password': generate_password_hash(password), 'status': status}) con.commit()
def get_all_tournaments(): con = get_con() curs = con.cursor() tournaments = curs.execute(""" SELECT * FROM Tournament t """) return get_column_name(tournaments.fetchall(), curs)
def create_team(team_name): con = get_con() db = con.cursor() db.prepare(""" INSERT INTO Teams (TeamName) VALUES (:team_name) """) db.execute(None, {'team_name': team_name}) con.commit()
def get_teams(): con = get_con() curs = con.cursor() teams = curs.execute(""" SELECT * FROM Teams ORDER BY Teams.Team_ID """).fetchall() return get_column_name(teams, curs)
def add_player_to_game(game_id, player_id): con = get_con() db = con.cursor() db.prepare(""" INSERT INTO WhoPlays (Game_ID, Player_ID) VALUES (:game_id, :player_id) """) db.execute(None, {'game_id': game_id, 'player_id': player_id}) con.commit()
def set_score_to_game(game_id, result): con = get_con() db = con.cursor() db.prepare(""" UPDATE Games SET result = :result WHERE game_id = :game_id """) db.execute(None, {'game_id': game_id, 'result': result}) con.commit()
def game_players_added(game_id, team_id): con = get_con() curs = con.cursor() curs.prepare(""" SELECT * FROM WhoPlays wp JOIN Players p ON wp.Player_ID = p.Player_ID WHERE game_id = :game_id and p.team_id = :team_id """) players = curs.execute(None, {'game_id': game_id, 'team_id': team_id}) return get_column_name(players.fetchall(), curs)
def get_teams_players(team_id): con = get_con() curs = con.cursor() curs.prepare(""" SELECT * FROM Teams t JOIN Players p ON p.Team_ID = t.Team_ID WHERE t.TEAM_ID = :team_id """) players = curs.execute(None, {'team_id': team_id}) return get_column_name(players.fetchall(), curs)
def select_user(username): con = get_con() cur = con.cursor() cur.prepare(""" SELECT * FROM Users WHERE username = :username """) res = cur.execute( None, {'username': username} ).fetchall() return get_column_name(res, cur)
def create_application(team_id, tournament_id, date): con = get_con() db = con.cursor() db.prepare(""" INSERT INTO Tournament_Application(Team_ID, Tournament_ID, Date_application) VALUES (:team_id, :tournament_id, to_date(:date_s, 'YYYY-MM-DD')) """) db.execute(None, { 'team_id': team_id, 'tournament_id': tournament_id, 'date_s': date }) con.commit()
def create_tournament(name, date_start, date_end): con = get_con() db = con.cursor() db.prepare(""" INSERT INTO Tournament(Tournament_NAME, Date_start, Date_end) VALUES (:name, to_date(:date_start, 'YYYY-MM-DD'), to_date(:date_end, 'YYYY-MM-DD')) """) db.execute(None, { 'name': name, 'date_start': date_start, 'date_end': date_end }) con.commit()
def get_games_by_tournament(tour_id): con = get_con() curs = con.cursor() curs.prepare(""" SELECT * FROM Tournament_Application ta JOIN Tournament t ON ta.Tournament_ID = t.Tournament_ID JOIN Teams team ON team.Team_ID = ta.Team_ID WHERE t.Tournament_ID = :tournament_id """) games = curs.execute(None, {'tournament_id': tour_id}) return get_column_name(games.fetchall(), curs)
def get_who_plays(team_id): con = get_con() curs = con.cursor() curs.prepare(""" SELECT * FROM WhoPlays who JOIN Players p ON p.Player_ID = who.Player_ID JOIN Teams t ON t.TEAM_ID = p.TEAM_ID WHERE who.Game_ID = :team_id """) players = curs.execute(None, {'team_id': team_id}) return get_column_name(players.fetchall(), curs)
def select_user_by_id(user_id): con = get_con() cur = con.cursor() cur.prepare(""" SELECT * FROM Users WHERE User_ID = :user_id """) res = cur.execute( None, {'user_id': user_id} ).fetchall() return get_column_name(res, cur)
def create_game(tournament_id, team1, team2, date): con = get_con() db = con.cursor() db.prepare(""" INSERT INTO Games(Date_game, Result, Team1_ID, Team2_ID, Tournament) VALUES (to_date(:date_s, 'YYYY-MM-DD'), '0:0', :team1, :team2, :tournament_id) """) db.execute( None, { 'tournament_id': tournament_id, 'team1': team1, 'team2': team2, 'date_s': date }) con.commit()
def get_game(game_id): con = get_con() curs = con.cursor() curs.prepare("""SELECT g.Game_ID, g.Result, t1.TeamName | | '-' | | t2.TeamName "VS" FROM Games g JOIN Teams t1 ON Team1_Id = t1.Team_id JOIN Teams t2 ON Team2_id = t2.Team_id WHERE g.Game_ID = :game_id """) game = curs.execute(None, {'game_id': game_id}) return get_column_name(game.fetchall(), curs)
def get_games(): con = get_con() curs = con.cursor() games = curs.execute(""" SELECT t1.Team_ID t1, t2.Team_ID t2, g.Game_ID, g.Result, t1.TeamName | | '-' | | t2.TeamName "VS" FROM Games g JOIN Teams t1 ON Team1_Id = t1.Team_id JOIN Teams t2 ON Team2_id = t2.Team_id """) return get_column_name(games.fetchall(), curs)
def get_tournaments(): con = get_con() curs = con.cursor() tournaments = curs.execute("""SELECT t.date_end, t.Tournament_ID, t.Tournament_NAME, SUM(CASE WHEN apli.Tournament_ID IS NOT NULL THEN 1 ELSE 0 END) sum_teams FROM Tournament t LEFT JOIN Tournament_Application apli ON t.Tournament_ID = apli.Tournament_ID GROUP BY t.Tournament_ID, t.Tournament_NAME, t.date_end """).fetchall() return get_column_name(tournaments, curs)