431 lines
12 KiB
Python
431 lines
12 KiB
Python
"""
|
|
Gestion des utilisateurs, notes, matières et UE.
|
|
Compatible SQLite et MariaDB via la couche d'abstraction.
|
|
"""
|
|
|
|
from typing import Dict, List, Optional, Tuple
|
|
|
|
from bdd.database import get_database
|
|
|
|
##############################
|
|
# #
|
|
# Gestion des utilisateurs #
|
|
# #
|
|
##############################
|
|
|
|
|
|
def verifierUtilisateur(login: str, password: str) -> Optional[Dict]:
|
|
"""
|
|
Vérifie les identifiants d'un utilisateur.
|
|
Retourne un dict avec les infos de l'utilisateur si OK, None sinon.
|
|
"""
|
|
with get_database() as db:
|
|
ph = db.backend.get_placeholder()
|
|
result = db.fetchone(
|
|
f"SELECT id, login, role, nom, prenom FROM Users WHERE login={ph} AND password={ph}",
|
|
(login, password),
|
|
)
|
|
|
|
if result:
|
|
return {
|
|
"id": result[0],
|
|
"login": result[1],
|
|
"role": result[2],
|
|
"nom": result[3],
|
|
"prenom": result[4],
|
|
}
|
|
return None
|
|
|
|
|
|
def isLoginAvalaible(login: str) -> bool:
|
|
"""Vérifie si un login est disponible"""
|
|
with get_database() as db:
|
|
ph = db.backend.get_placeholder()
|
|
result = db.fetchone(f"SELECT id FROM Users WHERE login={ph}", (login,))
|
|
return result is None
|
|
|
|
|
|
def creerUtilisateur(
|
|
login: str, password: str, role: str = "eleve", nom: str = "", prenom: str = ""
|
|
) -> bool:
|
|
"""
|
|
Crée un nouvel utilisateur.
|
|
Role: 'prof' ou 'eleve'
|
|
"""
|
|
if role not in ["prof", "eleve"]:
|
|
return False
|
|
|
|
with get_database() as db:
|
|
ph = db.backend.get_placeholder()
|
|
db.execute(
|
|
f"INSERT INTO Users (login, password, role, nom, prenom) VALUES ({ph}, {ph}, {ph}, {ph}, {ph})",
|
|
(login, password, role, nom, prenom),
|
|
)
|
|
db.commit()
|
|
return True
|
|
|
|
|
|
def getUserByLogin(login: str) -> Optional[Dict]:
|
|
"""Récupère les informations d'un utilisateur par son login"""
|
|
with get_database() as db:
|
|
ph = db.backend.get_placeholder()
|
|
result = db.fetchone(
|
|
f"SELECT id, login, role, nom, prenom FROM Users WHERE login={ph}", (login,)
|
|
)
|
|
|
|
if result:
|
|
return {
|
|
"id": result[0],
|
|
"login": result[1],
|
|
"role": result[2],
|
|
"nom": result[3],
|
|
"prenom": result[4],
|
|
}
|
|
return None
|
|
|
|
|
|
def getUserById(user_id: int) -> Optional[Dict]:
|
|
"""Récupère les informations d'un utilisateur par son ID"""
|
|
with get_database() as db:
|
|
ph = db.backend.get_placeholder()
|
|
result = db.fetchone(
|
|
f"SELECT id, login, role, nom, prenom FROM Users WHERE id={ph}", (user_id,)
|
|
)
|
|
|
|
if result:
|
|
return {
|
|
"id": result[0],
|
|
"login": result[1],
|
|
"role": result[2],
|
|
"nom": result[3],
|
|
"prenom": result[4],
|
|
}
|
|
return None
|
|
|
|
|
|
##############################
|
|
# #
|
|
# Fonctionnalités Élèves #
|
|
# #
|
|
##############################
|
|
|
|
|
|
def afficherNotes(login: str) -> List[Dict]:
|
|
"""
|
|
Affiche toutes les notes d'un élève avec les informations de matière et UE
|
|
"""
|
|
user = getUserByLogin(login)
|
|
if not user:
|
|
return []
|
|
|
|
with get_database() as db:
|
|
ph = db.backend.get_placeholder()
|
|
results = db.fetchall(
|
|
f"""
|
|
SELECT n.id, n.note, m.nom as matiere, m.code as matiere_code,
|
|
m.coefficient, ue.nom as ue_nom, ue.code as ue_code, ue.id as ue_id,
|
|
n.date_creation
|
|
FROM Notes n
|
|
JOIN Matieres m ON n.matiere_id = m.id
|
|
JOIN UE ue ON m.ue_id = ue.id
|
|
WHERE n.eleve_id = {ph}
|
|
ORDER BY ue.code, m.nom
|
|
""",
|
|
(user["id"],),
|
|
)
|
|
|
|
notes = []
|
|
for row in results:
|
|
notes.append(
|
|
{
|
|
"id": row[0],
|
|
"note": float(row[1]),
|
|
"matiere": row[2],
|
|
"matiere_code": row[3],
|
|
"coefficient": float(row[4]),
|
|
"ue_nom": row[5],
|
|
"ue_code": row[6],
|
|
"ue_id": row[7],
|
|
"date": row[8],
|
|
}
|
|
)
|
|
|
|
return notes
|
|
|
|
|
|
def calculerMoyennesParUE(login: str) -> Dict[str, Dict]:
|
|
"""
|
|
Calcule les moyennes par UE pour un élève.
|
|
Retourne un dict: {ue_code: {'nom': ..., 'moyenne': ..., 'nb_notes': ...}}
|
|
"""
|
|
notes = afficherNotes(login)
|
|
|
|
if not notes:
|
|
return {}
|
|
|
|
# Regrouper par UE
|
|
ue_data = {}
|
|
for note in notes:
|
|
ue_code = note["ue_code"]
|
|
if ue_code not in ue_data:
|
|
ue_data[ue_code] = {"nom": note["ue_nom"], "notes": [], "coefficients": []}
|
|
ue_data[ue_code]["notes"].append(note["note"])
|
|
ue_data[ue_code]["coefficients"].append(note["coefficient"])
|
|
|
|
# Calculer les moyennes pondérées
|
|
moyennes = {}
|
|
for ue_code, data in ue_data.items():
|
|
notes_list = data["notes"]
|
|
coeffs = data["coefficients"]
|
|
|
|
somme_ponderee = sum(n * c for n, c in zip(notes_list, coeffs))
|
|
somme_coeffs = sum(coeffs)
|
|
|
|
moyenne = somme_ponderee / somme_coeffs if somme_coeffs > 0 else 0
|
|
|
|
moyennes[ue_code] = {
|
|
"nom": data["nom"],
|
|
"moyenne": round(moyenne, 2),
|
|
"nb_notes": len(notes_list),
|
|
}
|
|
|
|
return moyennes
|
|
|
|
|
|
##############################
|
|
# #
|
|
# Fonctionnalités Profs #
|
|
# #
|
|
##############################
|
|
|
|
|
|
def listerEleves(search: str = "") -> List[Dict]:
|
|
"""
|
|
Liste tous les élèves. Si search est fourni, filtre par nom/prénom/login.
|
|
"""
|
|
with get_database() as db:
|
|
ph = db.backend.get_placeholder()
|
|
|
|
if search:
|
|
search_pattern = f"%{search}%"
|
|
results = db.fetchall(
|
|
f"""
|
|
SELECT id, login, nom, prenom
|
|
FROM Users
|
|
WHERE role = 'eleve'
|
|
AND (login LIKE {ph} OR nom LIKE {ph} OR prenom LIKE {ph})
|
|
ORDER BY nom, prenom
|
|
""",
|
|
(search_pattern, search_pattern, search_pattern),
|
|
)
|
|
else:
|
|
results = db.fetchall("""
|
|
SELECT id, login, nom, prenom
|
|
FROM Users
|
|
WHERE role = 'eleve'
|
|
ORDER BY nom, prenom
|
|
""")
|
|
|
|
eleves = []
|
|
for row in results:
|
|
eleves.append(
|
|
{"id": row[0], "login": row[1], "nom": row[2], "prenom": row[3]}
|
|
)
|
|
|
|
return eleves
|
|
|
|
|
|
def listerMatieres(search: str = "") -> List[Dict]:
|
|
"""
|
|
Liste toutes les matières avec leur UE. Si search est fourni, filtre par nom/code.
|
|
"""
|
|
with get_database() as db:
|
|
ph = db.backend.get_placeholder()
|
|
|
|
if search:
|
|
search_pattern = f"%{search}%"
|
|
results = db.fetchall(
|
|
f"""
|
|
SELECT m.id, m.code, m.nom, m.coefficient, ue.id, ue.code, ue.nom
|
|
FROM Matieres m
|
|
JOIN UE ue ON m.ue_id = ue.id
|
|
WHERE m.nom LIKE {ph} OR m.code LIKE {ph}
|
|
ORDER BY ue.code, m.nom
|
|
""",
|
|
(search_pattern, search_pattern),
|
|
)
|
|
else:
|
|
results = db.fetchall("""
|
|
SELECT m.id, m.code, m.nom, m.coefficient, ue.id, ue.code, ue.nom
|
|
FROM Matieres m
|
|
JOIN UE ue ON m.ue_id = ue.id
|
|
ORDER BY ue.code, m.nom
|
|
""")
|
|
|
|
matieres = []
|
|
for row in results:
|
|
matieres.append(
|
|
{
|
|
"id": row[0],
|
|
"code": row[1],
|
|
"nom": row[2],
|
|
"coefficient": float(row[3]),
|
|
"ue_id": row[4],
|
|
"ue_code": row[5],
|
|
"ue_nom": row[6],
|
|
}
|
|
)
|
|
|
|
return matieres
|
|
|
|
|
|
def listerUE() -> List[Dict]:
|
|
"""Liste toutes les UE"""
|
|
with get_database() as db:
|
|
results = db.fetchall("""
|
|
SELECT id, code, nom, description
|
|
FROM UE
|
|
ORDER BY code
|
|
""")
|
|
|
|
ues = []
|
|
for row in results:
|
|
ues.append(
|
|
{"id": row[0], "code": row[1], "nom": row[2], "description": row[3]}
|
|
)
|
|
|
|
return ues
|
|
|
|
|
|
def creerEleve(login: str, password: str, nom: str, prenom: str) -> bool:
|
|
"""Crée un compte élève (réservé aux profs)"""
|
|
return creerUtilisateur(login, password, "eleve", nom, prenom)
|
|
|
|
|
|
def creerUE(code: str, nom: str, description: str = "") -> bool:
|
|
"""Crée une nouvelle UE"""
|
|
with get_database() as db:
|
|
ph = db.backend.get_placeholder()
|
|
try:
|
|
db.execute(
|
|
f"INSERT INTO UE (code, nom, description) VALUES ({ph}, {ph}, {ph})",
|
|
(code, nom, description),
|
|
)
|
|
db.commit()
|
|
return True
|
|
except:
|
|
return False
|
|
|
|
|
|
def creerMatiere(code: str, nom: str, ue_id: int, coefficient: float = 1.0) -> bool:
|
|
"""Crée une nouvelle matière"""
|
|
with get_database() as db:
|
|
ph = db.backend.get_placeholder()
|
|
try:
|
|
db.execute(
|
|
f"INSERT INTO Matieres (code, nom, ue_id, coefficient) VALUES ({ph}, {ph}, {ph}, {ph})",
|
|
(code, nom, ue_id, coefficient),
|
|
)
|
|
db.commit()
|
|
return True
|
|
except:
|
|
return False
|
|
|
|
|
|
def ajouterNote(eleve_login: str, matiere_code: str, note: float) -> bool:
|
|
"""
|
|
Ajoute une note pour un élève dans une matière.
|
|
Compatible avec l'ancienne version (pour la transition)
|
|
"""
|
|
# Récupérer l'ID de l'élève
|
|
eleve = getUserByLogin(eleve_login)
|
|
if not eleve or eleve["role"] != "eleve":
|
|
return False
|
|
|
|
# Récupérer l'ID de la matière
|
|
with get_database() as db:
|
|
ph = db.backend.get_placeholder()
|
|
matiere = db.fetchone(
|
|
f"SELECT id FROM Matieres WHERE code = {ph}", (matiere_code,)
|
|
)
|
|
|
|
if not matiere:
|
|
return False
|
|
|
|
matiere_id = matiere[0]
|
|
|
|
# Vérifier que la note est valide
|
|
if note < 0 or note > 20:
|
|
return False
|
|
|
|
# Ajouter la note
|
|
db.execute(
|
|
f"INSERT INTO Notes (eleve_id, matiere_id, note) VALUES ({ph}, {ph}, {ph})",
|
|
(eleve["id"], matiere_id, note),
|
|
)
|
|
db.commit()
|
|
return True
|
|
|
|
|
|
def ajouterNoteById(eleve_id: int, matiere_id: int, note: float) -> bool:
|
|
"""Ajoute une note pour un élève dans une matière (version avec IDs)"""
|
|
if note < 0 or note > 20:
|
|
return False
|
|
|
|
with get_database() as db:
|
|
ph = db.backend.get_placeholder()
|
|
try:
|
|
db.execute(
|
|
f"INSERT INTO Notes (eleve_id, matiere_id, note) VALUES ({ph}, {ph}, {ph})",
|
|
(eleve_id, matiere_id, note),
|
|
)
|
|
db.commit()
|
|
return True
|
|
except:
|
|
return False
|
|
|
|
|
|
def getNotesEleve(eleve_id: int) -> List[Dict]:
|
|
"""Récupère toutes les notes d'un élève par ID"""
|
|
with get_database() as db:
|
|
ph = db.backend.get_placeholder()
|
|
results = db.fetchall(
|
|
f"""
|
|
SELECT n.id, n.note, m.nom as matiere, m.code,
|
|
ue.nom as ue_nom, ue.code as ue_code
|
|
FROM Notes n
|
|
JOIN Matieres m ON n.matiere_id = m.id
|
|
JOIN UE ue ON m.ue_id = ue.id
|
|
WHERE n.eleve_id = {ph}
|
|
ORDER BY ue.code, m.nom
|
|
""",
|
|
(eleve_id,),
|
|
)
|
|
|
|
notes = []
|
|
for row in results:
|
|
notes.append(
|
|
{
|
|
"id": row[0],
|
|
"note": float(row[1]),
|
|
"matiere": row[2],
|
|
"matiere_code": row[3],
|
|
"ue_nom": row[4],
|
|
"ue_code": row[5],
|
|
}
|
|
)
|
|
|
|
return notes
|
|
|
|
|
|
def supprimerNote(note_id: int) -> bool:
|
|
"""Supprime une note"""
|
|
with get_database() as db:
|
|
ph = db.backend.get_placeholder()
|
|
try:
|
|
db.execute(f"DELETE FROM Notes WHERE id = {ph}", (note_id,))
|
|
db.commit()
|
|
return True
|
|
except:
|
|
return False
|