# -*- mode: python -*- # -*- coding: utf-8 -*- ############################################################################## # # Gestion scolarite IUT # # Copyright (c) 1999 - 2024 Emmanuel Viennet. All rights reserved. # # This program is free software; you can redistribute it and/or modify # it under the terms of the GNU General Public License as published by # the Free Software Foundation; either version 2 of the License, or # (at your option) any later version. # # This program is distributed in the hope that it will be useful, # but WITHOUT ANY WARRANTY; without even the implied warranty of # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the # GNU General Public License for more details. # # You should have received a copy of the GNU General Public License # along with this program; if not, write to the Free Software # Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA # # Emmanuel Viennet emmanuel.viennet@viennet.net # ############################################################################## """ Accès donnees etudiants """ # Ancien module "scolars" import os import time from operator import itemgetter from flask import url_for, g from app import db, email from app import log from app.models import Admission, Identite from app.models.etudiants import ( check_etud_duplicate_code, input_civilite, input_civilite_etat_civil, make_etud_args, pivot_year, ) import app.scodoc.sco_utils as scu from app.scodoc.sco_utils import ( format_civilite, format_nom, format_nomprenom, format_prenom, ) import app.scodoc.notesdb as ndb from app.scodoc.sco_exceptions import ScoGenError, ScoValueError from app.scodoc import safehtml from app.scodoc import sco_preferences from app.scodoc.scolog import logdb def format_etud_ident(etud: dict): """Format identite de l'étudiant (modifié en place) nom, prénom et formes associees. Note: par rapport à Identite.to_dict_bul(), ajoute les champs: 'nom_disp', 'nom_usuel', 'civilite_etat_civil_str', 'ne', 'civilite_str' """ etud["nom"] = format_nom(etud["nom"]) if "nom_usuel" in etud: etud["nom_usuel"] = format_nom(etud["nom_usuel"]) else: etud["nom_usuel"] = "" etud["prenom"] = format_prenom(etud["prenom"]) if "prenom_etat_civil" in etud: etud["prenom_etat_civil"] = format_prenom(etud["prenom_etat_civil"]) else: etud["prenom_etat_civil"] = "" etud["civilite_str"] = format_civilite(etud["civilite"]) etud["civilite_etat_civil_str"] = ( format_civilite(etud["civilite_etat_civil"]) if etud["civilite_etat_civil"] else etud["civilite_str"] ) # Nom à afficher: if etud["nom_usuel"]: etud["nom_disp"] = etud["nom_usuel"] if etud["nom"]: etud["nom_disp"] += " (" + etud["nom"] + ")" else: etud["nom_disp"] = etud["nom"] etud["nomprenom"] = format_nomprenom(etud) # M. Pierre DUPONT etud["etat_civil"] = _format_etat_civil(etud) if etud["civilite"] == "M": etud["ne"] = "" elif etud["civilite"] == "F": etud["ne"] = "e" else: # 'X' etud["ne"] = "(e)" def force_uppercase(s): return s.upper() if s else s def _format_etat_civil(etud: dict) -> str: "Mme Béatrice DUPONT, en utilisant les données d'état civil si indiquées." if etud["prenom_etat_civil"] or etud["civilite_etat_civil"]: return f"""{etud["civilite_etat_civil_str"]} { etud["prenom_etat_civil"] or etud["prenom"] } {etud["nom"]}""" return etud["nomprenom"] def format_pays(s): "laisse le pays seulement si != FRANCE" if s.upper() != "FRANCE": return s else: return "" def etud_sort_key(etud: dict) -> str: """Clé de tri pour les étudiants représentés par des dict (anciens codes). Equivalent moderne: identite.sort_key """ return scu.sanitize_string( (etud.get("nom_usuel") or etud["nom"] or "") + ";" + (etud["prenom"] or ""), remove_spaces=False, ).lower() _identiteEditor = ndb.EditableTable( "identite", "etudid", ( "admission_id", "boursier", "cas_allow_login", "cas_allow_scodoc_login", "cas_id", "civilite_etat_civil", "civilite", # 'M", "F", or "X" "code_ine", "code_nip", "date_naissance", "dept_naissance", "etudid", "foto", "lieu_naissance", "nationalite", "nom_usuel", "nom", "photo_filename", "prenom_etat_civil", "prenom", "statut", ), filter_dept=True, sortkey="nom", input_formators={ "nom": force_uppercase, "nom_usuel": force_uppercase, "prenom": force_uppercase, "prenom_etat_civil": force_uppercase, "civilite": input_civilite, "civilite_etat_civil": input_civilite_etat_civil, "date_naissance": ndb.DateDMYtoISO, "boursier": bool, }, output_formators={"date_naissance": ndb.DateISOtoDMY}, convert_null_outputs_to_empty=True, # allow_set_id=True, # car on specifie le code Apogee a la creation #sco8 ) identite_delete = _identiteEditor.delete def identite_list(cnx, *a, **kw): """List, adding on the fly 'annee_naissance' and 'civilite_str' (M., Mme, "").""" objs = _identiteEditor.list(cnx, *a, **kw) for o in objs: if o["date_naissance"]: o["annee_naissance"] = int(o["date_naissance"].split("/")[2]) else: o["annee_naissance"] = o["date_naissance"] o["civilite_str"] = format_civilite(o["civilite"]) o["civilite_etat_civil_str"] = ( format_civilite(o["civilite_etat_civil"]) if o["civilite_etat_civil"] else "" ) return objs def identite_edit_nocheck(cnx, args): """Modifie les champs mentionnes dans args, sans verification ni notification.""" etud = db.session.get(Identite, args["etudid"]) etud.from_dict(args) db.session.commit() def check_nom_prenom_homonyms( nom: str = "", prenom: str = "", etudid=None ) -> tuple[bool, list[Identite]]: """Check if nom and prenom are valid. Also check for duplicates (homonyms), excluding etudid : in general, homonyms are allowed, but it may be useful to generate a warning. Returns: True | False, homonyms """ if not nom or (not prenom and not scu.CONFIG.ALLOW_NULL_PRENOM): return False, [] nom = nom.lower().strip() if prenom: prenom = prenom.lower().strip() # Don't allow some special cars (eg used in sql regexps) if scu.FORBIDDEN_CHARS_EXP.search(nom) or scu.FORBIDDEN_CHARS_EXP.search(prenom): return False, [] # Liste homonymes (dans tous les départements): query = Identite.query.filter( Identite.nom.ilike(nom + "%"), Identite.prenom.ilike(prenom + "%") ) if etudid is not None: query = query.filter(Identite.id != etudid) return True, query.all() def identite_edit(cnx, args, disable_notify=False): """Modifie l'identite d'un étudiant. Si pref notification et difference, envoie message notification, sauf si disable_notify """ check_etud_duplicate_code(args, "code_nip", edit=True) check_etud_duplicate_code(args, "code_ine", edit=True) notify_to = None if not disable_notify: try: notify_to = sco_preferences.get_preference("notify_etud_changes_to") except: pass if notify_to: # etat AVANT edition pour envoyer diffs before = identite_list(cnx, {"etudid": args["etudid"]})[0] identite_edit_nocheck(cnx, args) # Notification du changement par e-mail: if notify_to: etud = get_etud_info(etudid=args["etudid"], filled=True)[0] after = identite_list(cnx, {"etudid": args["etudid"]})[0] notify_etud_change( notify_to, etud, before, after, "Modification identite %(nomprenom)s" % etud, ) def identite_create(cnx, args): "check unique etudid, then create" check_etud_duplicate_code(args, "code_nip", edit=False) check_etud_duplicate_code(args, "code_ine", edit=False) if "etudid" in args: etudid = args["etudid"] r = identite_list(cnx, {"etudid": etudid}) if r: raise ScoValueError(f"Code identifiant (etudid) déjà utilisé ! ({etudid})") return _identiteEditor.create(cnx, args) def notify_etud_change(email_addr, etud, before, after, subject): """Send email notifying changes to etud before and after are two dicts, with values before and after the change. """ txt = [ "Code NIP:" + etud["code_nip"], "Civilité: " + etud["civilite_str"], "Nom: " + etud["nom"], "Prénom: " + etud["prenom"], "Etudid: " + str(etud["etudid"]), "\n", "Changements effectués:", ] n = 0 for key in after.keys(): if before[key] != after[key]: txt.append('%s: %s (auparavant: "%s")' % (key, after[key], before[key])) n += 1 if not n: return # pas de changements txt = "\n".join(txt) # build mail log(f"notify_etud_change: sending notification to {email_addr}") log(f"notify_etud_change: subject: {subject}") log(txt) email.send_email("[ScoDoc] " + subject, email.get_from_addr(), [email_addr], txt) return txt # -------- # Note: la table adresse n'est pas dans dans la table "identite" # car on prevoit plusieurs adresses par etudiant (ie domicile, entreprise) _adresseEditor = ndb.EditableTable( "adresse", "adresse_id", ( "adresse_id", "etudid", "email", "emailperso", "domicile", "codepostaldomicile", "villedomicile", "paysdomicile", "telephone", "telephonemobile", "fax", "typeadresse", "description", ), convert_null_outputs_to_empty=True, ) adresse_create = _adresseEditor.create adresse_delete = _adresseEditor.delete adresse_list = _adresseEditor.list def adresse_edit(cnx, args, disable_notify=False): """Modifie l'adresse d'un étudiant. Si pref notification et difference, envoie message notification, sauf si disable_notify """ notify_to = None if not disable_notify: try: notify_to = sco_preferences.get_preference("notify_etud_changes_to") except: pass if notify_to: # etat AVANT edition pour envoyer diffs before = adresse_list(cnx, {"etudid": args["etudid"]})[0] _adresseEditor.edit(cnx, args) # Notification du changement par e-mail: if notify_to: etud = get_etud_info(etudid=args["etudid"], filled=True)[0] after = adresse_list(cnx, {"etudid": args["etudid"]})[0] notify_etud_change( notify_to, etud, before, after, "Modification adresse %(nomprenom)s" % etud, ) def getEmail(cnx, etudid): "get email institutionnel etudiant (si plusieurs adresses, prend le premier non null" adrs = adresse_list(cnx, {"etudid": etudid}) for adr in adrs: if adr["email"]: return adr["email"] return "" # --------- _admissionEditor = ndb.EditableTable( "admissions", "adm_id", ( "adm_id", "annee", "bac", "specialite", "annee_bac", "math", "physique", "anglais", "francais", "rang", "qualite", "rapporteur", "decision", "score", "classement", "apb_groupe", "apb_classement_gr", "commentaire", "nomlycee", "villelycee", "codepostallycee", "codelycee", "type_admission", "boursier_prec", ), input_formators={ "annee": pivot_year, "bac": force_uppercase, "specialite": force_uppercase, "annee_bac": pivot_year, "classement": ndb.int_null_is_null, "apb_classement_gr": ndb.int_null_is_null, "boursier_prec": bool, }, output_formators={"type_admission": lambda x: x or scu.TYPE_ADMISSION_DEFAULT}, convert_null_outputs_to_empty=True, ) admission_create = _admissionEditor.create admission_delete = _admissionEditor.delete admission_list = _admissionEditor.list admission_edit = _admissionEditor.edit # Edition simultanee de identite et admission class EtudIdentEditor: def create(self, cnx, args): admission_id = admission_create(cnx, args) args["admission_id"] = admission_id etudid = identite_create(cnx, args) return etudid def list(self, *args, **kw) -> list[dict]: etuds_dict = identite_list(*args, **kw) res = [] for etud_dict in etuds_dict: res.append(etud_dict) adms_dict = ( admission_list(args[0], args={"id": etud_dict["admission_id"]}) if etud_dict["admission_id"] else [] ) if adms_dict: # merge adms_dict[0].pop("id", None) adms_dict[0].pop("etudid", None) res[-1] |= adms_dict[0] else: # pas d'etudiant trouve void_adm = { k: None for k in _admissionEditor.dbfields if k not in ("id", "etudid", "adm_id") } res[-1] |= void_adm # tri par nom res.sort(key=itemgetter("nom", "prenom")) return res def edit(self, cnx, args, disable_notify=False): identite_edit(cnx, args, disable_notify=disable_notify) if "adm_id" in args: # safety net admission_edit(cnx, args) _etudidentEditor = EtudIdentEditor() etudident_list = _etudidentEditor.list etudident_edit = _etudidentEditor.edit def log_unknown_etud(): """Log request: cas ou getEtudInfo n'a pas ramene de resultat""" etud_args = make_etud_args(raise_exc=False) log(f"unknown student: args={etud_args}") def get_etud_info(etudid=False, code_nip=False, filled=False) -> list[dict]: """infos sur un etudiant. If not found, returns empty list. On peut spécifier etudid ou code_nip ou bien cherche dans les arguments de la requête courante: etudid, code_nip, code_ine (dans cet ordre). """ if etudid is None: return [] cnx = ndb.GetDBConnexion() args = make_etud_args(etudid=etudid, code_nip=code_nip) etud = etudident_list(cnx, args=args) if filled: fill_etuds_info(etud) return etud def create_etud(cnx, args: dict = None): """Création d'un étudiant. Génère aussi évenement et "news". Args: args: dict avec les attributs de l'étudiant Returns: etud, l'étudiant créé. """ from app.models import ScolarNews # creation d'un etudiant args_dict = Identite.convert_dict_fields(args) args_dict["dept_id"] = g.scodoc_dept_id etud = Identite.create_etud(**args_dict) db.session.add(etud) db.session.commit() admission = etud.admission admission.from_dict(args) db.session.add(admission) db.session.commit() etudid = etud.id # log logdb( cnx, method="etudident_edit_form", etudid=etudid, msg="creation initiale", ) etud_dict = etudident_list(cnx, {"etudid": etudid})[0] fill_etuds_info([etud_dict]) etud_dict["url"] = url_for( "scolar.fiche_etud", scodoc_dept=g.scodoc_dept, etudid=etudid ) ScolarNews.add( typ=ScolarNews.NEWS_INSCR, text=f"Nouvel étudiant {etud.html_link_fiche()}", url=etud_dict["url"], max_frequency=0, ) return etud_dict # ---------- "EVENTS" _scolar_eventsEditor = ndb.EditableTable( "scolar_events", "event_id", ( "event_id", "etudid", "event_date", "formsemestre_id", "ue_id", "event_type", "comp_formsemestre_id", ), sortkey="event_date", convert_null_outputs_to_empty=True, output_formators={"event_date": ndb.DateISOtoDMY}, input_formators={"event_date": ndb.DateDMYtoISO}, ) # scolar_events_create = _scolar_eventsEditor.create scolar_events_delete = _scolar_eventsEditor.delete scolar_events_list = _scolar_eventsEditor.list scolar_events_edit = _scolar_eventsEditor.edit def scolar_events_create(cnx, args): # several "events" may share the same values _scolar_eventsEditor.create(cnx, args) # -------- _etud_annotationsEditor = ndb.EditableTable( "etud_annotations", "id", ( "id", "date", "etudid", "author", "comment", "author", ), sortkey="date desc", convert_null_outputs_to_empty=True, output_formators={"comment": safehtml.html_to_safe_html, "date": ndb.DateISOtoDMY}, ) etud_annotations_create = _etud_annotationsEditor.create etud_annotations_delete = _etud_annotationsEditor.delete etud_annotations_list = _etud_annotationsEditor.list etud_annotations_edit = _etud_annotationsEditor.edit def add_annotations_to_etud_list(etuds): """Add key 'annotations' describing annotations of etuds (used to list all annotations of a group) """ cnx = ndb.GetDBConnexion() for etud in etuds: l = [] for a in etud_annotations_list(cnx, args={"etudid": etud["etudid"]}): l.append("%(comment)s (%(date)s)" % a) etud["annotations_str"] = ", ".join(l) # -------- Noms des Lycées à partir du code def read_etablissements(): filename = os.path.join(scu.SCO_TOOLS_DIR, scu.CONFIG.ETABL_FILENAME) log("reading %s" % filename) with open(filename) as f: L = [x[:-1].split(";") for x in f] E = {} for l in L[1:]: E[l[0]] = { "name": l[1], "address": l[2], "codepostal": l[3], "commune": l[4], "position": l[5] + "," + l[6], } return E ETABLISSEMENTS = None def get_etablissements(): global ETABLISSEMENTS if ETABLISSEMENTS is None: ETABLISSEMENTS = read_etablissements() return ETABLISSEMENTS def get_lycee_infos(codelycee): etablissements = get_etablissements() return etablissements.get(codelycee, None) def format_lycee_from_code(codelycee: str) -> str: "Description lycee à partir du code" etablissements = get_etablissements() if codelycee in etablissements: e = etablissements[codelycee] nomlycee = e["name"] return f"{nomlycee} ({e['commune']})" return f"{codelycee} (établissement inconnu)" def format_lycee(nomlycee: str) -> str: "mise en forme nom de lycée" nomlycee = nomlycee.strip() s = nomlycee.lower() if s[:5] == "lycee" or s[:5] == "lycée": return nomlycee[5:] else: return nomlycee def etud_add_lycee_infos(etud): """Si codelycee est renseigné, ajout les champs au dict""" if etud["codelycee"]: il = get_lycee_infos(etud["codelycee"]) if il: if not etud["codepostallycee"]: etud["codepostallycee"] = il["codepostal"] if not etud["nomlycee"]: etud["nomlycee"] = il["name"] if not etud["villelycee"]: etud["villelycee"] = il["commune"] if not etud.get("positionlycee", None): if il["position"] != "0.0,0.0": etud["positionlycee"] = il["position"] return etud """ Conversion fichier original: f = open('etablissements.csv') o = open('etablissements2.csv', 'w') o.write( f.readline() ) for l in f: fs = l.split(';') nom = ' '.join( [ x.capitalize() for x in fs[1].split() ] ) adr = ' '.join( [ x.capitalize() for x in fs[2].split() ] ) ville=' '.join( [ x.capitalize() for x in fs[4].split() ] ) o.write( '%s;%s;%s;%s;%s\n' % (fs[0], nom, adr, fs[3], ville)) o.close() """ def list_scolog(etudid): "liste des operations effectuees sur cet etudiant" cnx = ndb.GetDBConnexion() cursor = cnx.cursor(cursor_factory=ndb.ScoDocCursor) cursor.execute( "SELECT * FROM scolog WHERE etudid=%(etudid)s ORDER BY DATE DESC", {"etudid": etudid}, ) return cursor.dictfetchall() def fill_etuds_info(etuds: list[dict], add_admission=True): """etuds est une liste d'etudiants (mappings) Pour chaque etudiant, ajoute ou formatte les champs -> informations pour fiche etudiant ou listes diverses Si add_admission: ajoute au dict le schamps "admission" s'il n'y sont pas déjà. """ cnx = ndb.GetDBConnexion() for etud in etuds: etudid = etud["etudid"] etud["dept"] = g.scodoc_dept # Admission if add_admission and "nomlycee" not in etud: admission = ( Admission.query.filter_by(id=etud["admission_id"]) .first() .to_dict(no_nulls=True) ) del admission["id"] # pour garder id == etudid dans etud etud.update(admission) # adrs = adresse_list(cnx, {"etudid": etudid}) if not adrs: # certains "vieux" etudiants n'ont pas d'adresse adr = {}.fromkeys(_adresseEditor.dbfields, "") adr["etudid"] = etudid else: adr = adrs[0] if len(adrs) > 1: log("fill_etuds_info: etudid=%s a %d adresses" % (etudid, len(adrs))) adr.pop("id", None) etud.update(adr) format_etud_ident(etud) etud.update(etud_inscriptions_infos(etudid, etud["ne"])) # nettoyage champs souvent vides etud["codepostallycee"] = etud.get("codepostallycee", "") or "" etud["nomlycee"] = etud.get("nomlycee", "") or "" # voir Identite.inscription_descr et Identite.to_dict_scodoc7(with_inscriptions=True) def etud_inscriptions_infos(etudid: int, ne="") -> dict: """Dict avec les informations sur les semestres passés et courant. { "sems" : , # trie les semestres par date de debut, le plus recent d'abord "ins" : , "cursem" : , "inscription" : , # cursem["titremois"] "inscriptionstr" : , # "Inscrit en " + cursem["titremois"] "inscription_formsemestre_id" : , # cursem["formsemestre_id"] "etatincursem" : , # curi["etat"] "situation" : , # descr_situation_etud(etudid, ne) } """ from app.scodoc import sco_formsemestre from app.scodoc import sco_formsemestre_inscriptions infos = {} # Semestres dans lesquel il est inscrit ins = sco_formsemestre_inscriptions.do_formsemestre_inscription_list( {"etudid": etudid} ) infos["ins"] = ins sems = [] cursem = None # semestre "courant" ou il est inscrit for i in ins: sem = sco_formsemestre.get_formsemestre(i["formsemestre_id"]) if sco_formsemestre.sem_est_courant(sem): cursem = sem curi = i sem["ins"] = i sems.append(sem) # trie les semestres par date de debut, le plus recent d'abord # (important, ne pas changer (suivi cohortes)) sems.sort(key=itemgetter("dateord"), reverse=True) infos["sems"] = sems infos["cursem"] = cursem if cursem: infos["inscription"] = cursem["titremois"] infos["inscriptionstr"] = "Inscrit en " + cursem["titremois"] infos["inscription_formsemestre_id"] = cursem["formsemestre_id"] infos["etatincursem"] = curi["etat"] infos["situation"] = descr_situation_etud(etudid, ne) else: if infos["sems"]: if infos["sems"][0]["dateord"] > time.strftime( "%Y-%m-%d", time.localtime() ): infos["inscription"] = "futur" infos["situation"] = "futur élève" else: infos["inscription"] = "ancien" infos["situation"] = "ancien élève" else: infos["inscription"] = "non inscrit" infos["situation"] = infos["inscription"] infos["inscriptionstr"] = infos["inscription"] infos["inscription_formsemestre_id"] = None infos["etatincursem"] = "?" return infos def descr_situation_etud(etudid: int, ne="") -> str: """Chaîne décrivant la situation actuelle de l'étudiant XXX Obsolete, utiliser Identite.descr_situation_etud() dans les nouveaux codes """ from app.scodoc import sco_formsemestre cnx = ndb.GetDBConnexion() cursor = cnx.cursor(cursor_factory=ndb.ScoDocCursor) cursor.execute( """SELECT I.formsemestre_id, I.etat FROM notes_formsemestre_inscription I, notes_formsemestre S WHERE etudid=%(etudid)s and S.id = I.formsemestre_id and date_debut < now() and date_fin > now() ORDER BY S.date_debut DESC;""", {"etudid": etudid}, ) r = cursor.dictfetchone() if not r: situation = "non inscrit" + ne else: sem = sco_formsemestre.get_formsemestre(r["formsemestre_id"]) if r["etat"] == scu.INSCRIT: situation = "inscrit%s en %s" % (ne, sem["titremois"]) # Cherche la date d'inscription dans scolar_events: events = scolar_events_list( cnx, args={ "etudid": etudid, "formsemestre_id": sem["formsemestre_id"], "event_type": "INSCRIPTION", }, ) if not events: log( "*** situation inconsistante pour %s (inscrit mais pas d'event)" % etudid ) date_ins = "???" # ??? else: date_ins = events[0]["event_date"] situation += " le " + str(date_ins) else: situation = "démission de %s" % sem["titremois"] # Cherche la date de demission dans scolar_events: events = scolar_events_list( cnx, args={ "etudid": etudid, "formsemestre_id": sem["formsemestre_id"], "event_type": "DEMISSION", }, ) if not events: log( "*** situation inconsistante pour %s (demission mais pas d'event)" % etudid ) date_dem = "???" # ??? else: date_dem = events[0]["event_date"] situation += " le " + str(date_dem) return situation