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 get_all_tournaments():
con = get_con()
curs = con.cursor()
tournaments = curs.execute("""
SELECT
*
FROM Tournament t
""")
return get_column_name(tournaments.fetchall(), curs)
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 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 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 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 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 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 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 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_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_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)
