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)
