def uninitDb():
statement="""DROP TABLE PLAYERSTATISTICS, MATCHES, PLAYERS, COACHES, REFEREES, TEAMS, TOURNAMENTS, USERS, MATCHSTATISTICS, TECHNICMEMBERS, STADIUMS CASCADE"""
cursor = create_connection()
cursor.execute(statement)
cursor.connection.commit()
close_connection(cursor)
return render_template('home.html')
def update_referee(id, name_update, gender_update, nationality_update, birth_date_update, times_match_update):
cursor = create_connection()
statement = """UPDATE REFEREES SET NAME = '{}', GENDER = '{}', NATIONALITY = '{}', BIRTH_DATE = '{}', TIMES_MATCH = '{}' WHERE ID = {}""".format(name_update, gender_update, nationality_update, birth_date_update, times_match_update,id)
cursor.execute(statement)
cursor.connection.commit()
close_connection(cursor)
def update_matchstatistic(id, home_team_update, away_team_update, match_date_update, score_update, referee_update):
cursor = create_connection()
statement = """UPDATE MATCHSTATISTICS SET HOME_TEAM = '{}', AWAY_TEAM = '{}', MATCH_DATE = '{}', SCORE = '{}', REFEREE = '{}' WHERE ID = {}""".format( home_team_update, away_team_update, match_date_update, score_update, referee_update, id)
cursor.execute(statement)
cursor.connection.commit()
close_connection(cursor)
def delete_coach(id):
cursor = create_connection()
statement = """DELETE FROM COACHES WHERE ID={}""".format(id)
cursor.execute(statement)
cursor.connection.commit()
close_connection(cursor)
def update_coach(id, name_update, gender_update, nationality_update, birth_date_update, current_team_update):
cursor = create_connection()
statement = """UPDATE COACHES SET NAME = '{}', GENDER = '{}', NATIONALITY = '{}', BIRTH_DATE = '{}', CURRENT_TEAM = '{}' WHERE ID = {}""".format(name_update, gender_update, nationality_update, birth_date_update, current_team_update,id)
cursor.execute(statement)
cursor.connection.commit()
close_connection(cursor)
def update_tournament(id, nameUpdate, yearUpdate, winnerUpdate, best_playerUpdate):
cursor = create_connection()
statement = """UPDATE TOURNAMENTS SET NAME = '{}', YEAR = '{}', WINNER = '{}', BEST_PLAYER = {} WHERE ID={} """.format(nameUpdate, yearUpdate, winnerUpdate, best_playerUpdate, id)
cursor.execute(statement)
cursor.connection.commit()
close_connection(cursor)
def update_match(id, tournamentUpdate, team1Update, team2Update, scoreUpdate):
cursor = create_connection()
statement = """UPDATE MATCHES SET TOURNAMENT = '{}', TEAM1 = '{}', TEAM2 = '{}', SCORE = '{}' WHERE ID={} """.format(tournamentUpdate, team1Update, team2Update, scoreUpdate, id)
cursor.execute(statement)
cursor.connection.commit()
close_connection(cursor)
def delete_stadium(id):
cursor = create_connection()
statement = """DELETE FROM STADIUMS WHERE ID={}""".format(id)
cursor.execute(statement)
cursor.connection.commit()
close_connection(cursor)
def create(nom, prenom, adresse, tel, email, mot_de_passe, role):
try:
con = open_connection()
cur = con.cursor()
sql = '''
INSERT INTO utilisateur(nom, prenom, adresse, tel, email, mot_de_passe, role)
VALUES(%s, %s, %s, %s, %s, %s, %s)
RETURNING _id
'''
cur.execute(sql,
[nom, prenom, adresse, tel, email, mot_de_passe, role])
con.commit()
id_utilisateur = cur.fetchone()[0]
return id_utilisateur
except Exception as e:
print(e)
finally:
close_connection(con, cur)
def delete_matchstatistic(id):
cursor = create_connection()
statement = """DELETE FROM MATCHSTATISTICS WHERE ID={}""".format(id)
cursor.execute(statement)
cursor.connection.commit()
close_connection(cursor)
def update_user(id, username_update, password_update):
cursor = create_connection()
statement = """UPDATE USERS SET USERNAME = '******', PASSWORD = '******' WHERE ID = {}""".format(username_update, password_update,id)
cursor.execute(statement)
cursor.connection.commit()
close_connection(cursor)
def delete_tournament(id):
cursor = create_connection()
statement = """DELETE FROM TOURNAMENTS WHERE ID={}""".format(id)
cursor.execute(statement)
cursor.connection.commit()
close_connection(cursor)
def edit(poste, type_contrat, date_fin_offre, salaire, niveau_d_etude,
experiences, description_poste, exigences, avantages, id):
try:
con = open_connection()
cur = con.cursor(buffered=True)
sql = '''
UPDATE offre_emploi
SET poste = %s,
type_contrat = %s,
date_fin_offre = %s,
salaire = %s,
niveau_d_etude = %s,
experiences = %s,
description_poste = %s,
exigences = %s,
avantages = %s
WHERE _id = %s
'''
cur.execute(sql, [
poste, type_contrat, date_fin_offre, salaire, niveau_d_etude,
experiences, description_poste, exigences, avantages, id
])
con.commit()
except Exception as e:
print('Error: ', e)
finally:
close_connection(con, cur)
def delete_playerstatistic(id):
cursor = create_connection()
statement = """DELETE FROM playerstatistics WHERE ID={}""".format(id)
cursor.execute(statement)
cursor.connection.commit()
close_connection(cursor)
def update_playerstatistic(id, matches_played_update, matches_won_update, win_rate_update, average_score_update, player_update):
cursor = create_connection()
statement = """UPDATE playerstatistics SET matches_played = '{}', matches_won = '{}', win_rate = '{}', average_score = '{}', player = '{}' WHERE ID = {}""".format( matches_played_update, matches_won_update, win_rate_update, average_score_update, player_update, id)
cursor.execute(statement)
cursor.connection.commit()
close_connection(cursor)
def get_users():
cursor = create_connection()
cursor.execute("SELECT * FROM USERS;")
users = cursor.fetchall()
close_connection(cursor)
return users
def update_stadium(id, name_update, capacity_update, city_update, country_update):
cursor = create_connection()
statement = """UPDATE STADIUMS SET NAME = '{}', CAPACITY = '{}', CITY = '{}', COUNTRY = '{}' WHERE ID = {}""".format(
name_update, capacity_update, city_update, country_update, id
)
cursor.execute(statement)
cursor.connection.commit()
close_connection(cursor)
def add_new_referee(name, gender, nationality, birth_date, times_match):
cursor = create_connection()
cursor.execute("INSERT INTO referees (name, gender, nationality, birth_date, times_match) VALUES (%s, %s, %s, %s, %s)", (name, gender, nationality, birth_date, times_match))
cursor.connection.commit()
close_connection(cursor)
return True
def get_referees():
cursor = create_connection()
cursor.execute("SELECT * FROM referees;")
referees = cursor.fetchall()
close_connection(cursor)
return referees
def join_tables():
cursor = create_connection()
statement = """ SELECT STADIUMS.ID, STADIUMS.NAME, STADIUMS.CAPACITY, CITY, TEAMS.NATION FROM STADIUMS INNER JOIN TEAMS ON TEAMS.ID=STADIUMS.COUNTRY """
cursor.execute(statement)
stadiums = cursor.fetchall()
cursor.connection.commit()
close_connection(cursor)
return stadiums
def join_tables():
cursor = create_connection()
statement= """ SELECT playerstatistics.ID, matches_played, matches_won, win_rate, average_score, players.NAME FROM playerstatistics INNER JOIN players ON players.ID=playerstatistics.player """
cursor.execute(statement)
playerstatistics = cursor.fetchall()
cursor.connection.commit()
close_connection(cursor)
return playerstatistics
def add_new_playerstatistic(matches_played, matches_won, win_rate, average_score, player):
cursor = create_connection()
cursor.execute("INSERT INTO playerstatistics (matches_played, matches_won, win_rate, average_score, player) VALUES (%s, %s, %s, %s, %s)", (matches_played, matches_won, win_rate, average_score, player))
cursor.connection.commit()
close_connection(cursor)
return True
def get_playerstatistics():
cursor = create_connection()
cursor.execute("SELECT * FROM playerstatistics;")
playerstatistics = cursor.fetchall()
close_connection(cursor)
return playerstatistics
def showJointTables():
cursor = create_connection()
statement= """ SELECT TOURNAMENTS.ID, TOURNAMENTS.NAME, YEAR, TEAMS.NATION , PLAYERS.NAME FROM TOURNAMENTS INNER JOIN PLAYERS ON PLAYERS.ID=TOURNAMENTS.BEST_PLAYER INNER JOIN TEAMS ON TEAMS.ID=TOURNAMENTS.WINNER """
cursor.execute(statement)
tournaments = cursor.fetchall()
cursor.connection.commit()
close_connection(cursor)
return tournaments
def add_new_match(tournament, team1, team2, score):
cursor = create_connection()
cursor.execute("INSERT INTO matches (tournament, team1, team2, score) VALUES (%s, %s, %s, %s)", (tournament, team1, team2, score))
cursor.connection.commit()
close_connection(cursor)
return True
def add_new_tournament(name, year, winner, best_player):
cursor = create_connection()
cursor.execute("INSERT INTO tournaments (name, year, winner, best_player) VALUES (%s, %s, %s, %s)", (name, year, winner, best_player))
cursor.connection.commit()
close_connection(cursor)
return True
def showJointTables():
cursor = create_connection()
statement= """ SELECT MATCHES.ID, TOURNAMENTS.NAME, t1.NATION, t2.NATION, MATCHES.SCORE FROM MATCHES INNER JOIN TOURNAMENTS ON TOURNAMENTS.ID=MATCHES.TOURNAMENT INNER JOIN TEAMS t1 ON t1.ID = MATCHES.TEAM1 INNER JOIN TEAMS t2 ON t2.ID=MATCHES.TEAM2 """
cursor.execute(statement)
matches = cursor.fetchall()
cursor.connection.commit()
close_connection(cursor)
return matches
def get_stadiums():
cursor = create_connection()
cursor.execute("SELECT * FROM stadiums;")
stadiums = cursor.fetchall()
close_connection(cursor)
return stadiums
def join_tables():
cursor = create_connection()
statement= """ SELECT COACHES.ID, COACHES.NAME, COACHES.GENDER, NATIONALITY, BIRTH_DATE, TEAMS.NATION FROM COACHES INNER JOIN TEAMS ON TEAMS.ID=COACHES.CURRENT_TEAM """
cursor.execute(statement)
coaches = cursor.fetchall()
cursor.connection.commit()
close_connection(cursor)
return coaches
def add_new_coach(name, gender, nationality, birth_date, current_team):
cursor = create_connection()
cursor.execute("INSERT INTO coaches (name, gender, nationality, birth_date, current_team) VALUES (%s, %s, %s, %s, %s)", (name, gender, nationality, birth_date, current_team))
cursor.connection.commit()
close_connection(cursor)
return True
def get_coaches():
cursor = create_connection()
cursor.execute("SELECT * FROM coaches;")
coaches = cursor.fetchall()
close_connection(cursor)
return coaches
def add_new_user(username, password):
cursor = create_connection()
cursor.execute("INSERT INTO USERS (USERNAME, PASSWORD) VALUES (%s, %s)", (username, password))
cursor.connection.commit()
close_connection(cursor)
return True
def delete(id):
try:
con = open_connection()
cur = con.cursor(buffered=True)
sql = '''
DELETE FROM demande WHERE _id = %s
'''
cur.execute(sql, [id])
con.commit()
except Exception as e:
print('Error: ', e)
finally:
close_connection(con, cur)
def afficher_tous():
try:
con = open_connection()
cur = con.cursor(buffered=True)
sql = '''
SELECT demande._id, utilisateur.nom,utilisateur.prenom ,offre_emploi.poste,demande.date_creation FROM `demande`,`candidat`,`utilisateur`,`offre_emploi` WHERE candidat._id = demande._id_candidat AND demande._id_offre_emploi=offre_emploi._id AND utilisateur._id=candidat._id_utilisateur '''
cur.execute(sql)
demande_emploi = cur.fetchall()
return demande_emploi
except Exception as e:
print('Error: ', e)
finally:
close_connection(con, cur)
def afficher_tous():
try:
con = open_connection()
cur = con.cursor(buffered=True)
sql = '''
SELECT * FROM offre_emploi ORDER BY date_creation DESC
'''
cur.execute(sql)
offre_emploi = cur.fetchall()
return offre_emploi
except Exception as e:
print('Error: ', e)
finally:
close_connection(con, cur)
def new(poste, type_contrat, date_fin_offre, salaire,
niveau_d_etude, experiences, description_poste, exigences, avantages):
try:
con = open_connection()
cur = con.cursor(buffered=True)
sql = '''
INSERT INTO demande(poste, utilisateur_id,offre_emploi_id, niveau_d_etude, experiences, description, formations,competences)
VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s)
'''
cur.execute(sql, [poste, _id_utilisateur,_id_offre_emploi, niveau_d_etude, experiences, description, formations,competences])
con.commit()
except Exception as e:
print('Error: ', e)
finally:
close_connection(con, cur)
def create(nom, prenom, adresse, tel, email, mot_de_passe, role):
try:
con = open_connection()
cur = con.cursor()
id_utilisateur = Utilisateur.create(nom, prenom, adresse, tel, email, mot_de_passe, role)
sql = '''
INSERT INTO recruteur(_id_utilisateur)
VALUES(%s)
'''
cur.execute(sql, [id_utilisateur])
con.commit()
except Exception as e:
print(e)
finally:
close_connection(con, cur)
def authentification(email, mot_d_passe):
try:
con = open_connection()
cur = con.cursor(buffered=True)
sql = '''
SELECT * FROM utilisateur WHERE email = %s AND mot_de_passe = %s
'''
cur.execute(sql, [email, mot_d_passe])
if cur.rowcount > 0:
utilisateur = cur.fetchone()
return utilisateur
return None
except Exception as e:
print('Error: ', e)
finally:
close_connection(con, cur)
def create(nom, prenom, adresse, tel, email, mot_de_passe, role):
try:
con = open_connection()
cur = con.cursor(buffered=True)
sql = '''
INSERT INTO utilisateur(nom, prenom, adresse, tel, email, mot_de_passe, role)
VALUES(%s, %s, %s, %s, %s, %s, %s)
'''
cur.execute(sql, [nom, prenom, adresse, tel, email, mot_de_passe, role])
con.commit()
except Exception as e:
print(e)
finally:
close_connection(con, cur)
