spb/uqo_cours

1#!/usr/bin/env python32# -*- coding: utf-8 -*-3"""Grille de notes Excel par cours : étudiants + évaluations lus sur Moodle (moodle_grades.py), notes déjà saisies,4cases vides pour le reste, total, note littérale UQO, statistiques. Usage : generer_grilles_notes.py [IMM1003 IMM1033]"""5import json, re, sys, unicodedata6from datetime import datetime7from pathlib import Path8from openpyxl import Workbook9from openpyxl.drawing.image import Image as XLImage10from openpyxl.styles import Alignment, Border, Font, PatternFill, Side11from openpyxl.utils import get_column_letter as L12from openpyxl.worksheet.datavalidation import DataValidation13from openpyxl.formatting.rule import FormulaRule14from donnees_cours import BAREME, COURS1516HERE = Path(__file__).resolve().parent17BLEU, BLEU_F, GRIS, VERT, ORANGE = "005F85", "003F5A", "F3F4F6", "DCFCE7", "FEF3C7"18MOIS_C = ["", "janv.", "févr.", "mars", "avr.", "mai", "juin", "juil.", "août", "sept.", "oct.", "nov.", "déc."]19thin = Side(style="thin", color="CBD5E1"); med = Side(style="medium", color=BLEU)20BORD = Border(left=thin, right=thin, top=thin, bottom=thin)21FONT = "Avenir Next"2223def strip_acc(s):24    return "".join(ch for ch in unicodedata.normalize("NFD", s) if unicodedata.category(ch) != "Mn")2526def split_name(raw):27    """'BDDavid Bissonnette' → (prénom 'David', nom 'Bissonnette') grâce aux initiales Moodle (Nom, Prénom)."""28    init, full = raw[:2], raw[2:].strip()29    toks = full.split()30    li = strip_acc(init[0]).upper()31    for i in range(1, len(toks)):32        if strip_acc(toks[i])[0].upper() == li:33            return " ".join(toks[:i]), " ".join(toks[i:])34    return " ".join(toks[:-1]), toks[-1]3536def fdate(d): return f"{d.day if d.day != 1 else '1er'} {MOIS_C[d.month]}"3738def val(v):39    v = (v or "").strip()40    if v in ("", "-", "–"): return None41    try: return float(v.replace(",", ".").replace(" ", "").replace(" ", ""))42    except ValueError: return v4344def build(code):45    c = COURS[code]46    raw = json.loads((HERE / f"{code.lower()}_moodle_notes.json").read_text(encoding="utf-8"))47    heads = raw["heads"]; evals = c["evaluations"]48    # correspondance colonne Moodle ↔ évaluation du plan (par préfixe du libellé)49    col_of = {}50    for k, (key, court, long_, pct, ouv, rem) in enumerate(evals):51        idx = next(i for i, h in enumerate(heads) if h["text"].startswith(key))52        col_of[k] = heads[idx]["id"]53    students = []54    for r in raw["rows"]:55        pre, nom = split_name(r["name"])56        grades = {cell["id"]: val(cell["v"]) for cell in r["cells"]}57        students.append((nom, pre, r["email"], grades))58    students.sort(key=lambda s: (strip_acc(s[0]).lower(), strip_acc(s[1]).lower()))5960    wb = Workbook(); ws = wb.active; ws.title = "Notes"61    ws.sheet_view.showGridLines = False62    ne = len(evals)63    C0 = 4                      # première colonne d'évaluation (D)64    CT = C0 + ne                # Total65    CP = CT + 1                 # % sur le corrigé66    CL = CP + 1                 # lettre67    CR = CL + 1                 # remarques68    # --- en-tête69    img = XLImage(str(HERE / "uqo-logo.png")); img.width, img.height = 125, 6070    ws.add_image(img, "A1")71    ws.row_dimensions[1].height = 24; ws.row_dimensions[2].height = 20; ws.row_dimensions[3].height = 1672    ws["C1"] = f"{c['code']} — {c['titre']}"; ws["C1"].font = Font(name=FONT, size=16, bold=True, color=BLEU_F)73    ws["C2"] = f"Grille de notes · Automne 2026 · Groupe {c['groupe']} · {c['horaire']} · Professeur : Simon-Pierre Boucher, Ph. D."74    ws["C2"].font = Font(name=FONT, size=10, color="475569")75    ws["C3"] = (f"Notes déjà saisies lues sur Moodle (rapport de l'évaluateur, cours {c['moodle_id']}) le "76                f"{datetime.now():%d/%m/%Y à %H h %M} · cases blanches = à saisir · barème des points = pondération du plan de cours (13 oct. 2026)")77    ws["C3"].font = Font(name=FONT, size=8.5, italic=True, color="6B7280")78    # --- lignes d'en-tête du tableau (5-7)79    H1, H2, H3 = 5, 6, 780    hdr_fill = PatternFill("solid", fgColor=BLEU); hdr_font = Font(name=FONT, bold=True, color="FFFFFF", size=10)81    sub_fill = PatternFill("solid", fgColor="E0F2FE"); sub_font = Font(name=FONT, size=8.5, color="0F3B52")82    for col, txt in ((1, "N°"), (2, "Nom"), (3, "Prénom")):83        ws.merge_cells(start_row=H1, start_column=col, end_row=H3, end_column=col)84        cell = ws.cell(H1, col, txt); cell.fill = hdr_fill; cell.font = hdr_font85        cell.alignment = Alignment(horizontal="center" if col == 1 else "left", vertical="center", indent=0 if col == 1 else 1)86    for k, (key, court, long_, pct, ouv, rem) in enumerate(evals):87        col = C0 + k88        t = ws.cell(H1, col, court); t.fill = hdr_fill; t.font = hdr_font; t.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)89        m = ws.cell(H2, col, pct); m.number_format = '"/ "0'; m.fill = sub_fill; m.font = Font(name=FONT, bold=True, size=9, color="0F3B52"); m.alignment = Alignment(horizontal="center")90        d = ws.cell(H3, col, ("Remise " if court not in ("Final",) else "≤ ") + fdate(rem)); d.fill = sub_fill; d.font = sub_font; d.alignment = Alignment(horizontal="center", wrap_text=True)91    for col, txt, sub in ((CT, "Total", "/ 100"), (CP, "% du corrigé", "sur les pts évalués"), (CL, "Lettre", "sur % du corrigé"), (CR, "Remarques", "")):92        t = ws.cell(H1, col, txt); t.fill = hdr_fill; t.font = hdr_font; t.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)93        s2 = ws.cell(H2, col, sub); s2.fill = sub_fill; s2.font = sub_font; s2.alignment = Alignment(horizontal="center")94        s3 = ws.cell(H3, col); s3.fill = sub_fill95        if col == CR:96            ws.merge_cells(start_row=H1, start_column=col, end_row=H3, end_column=col)97    ws.row_dimensions[H1].height = 30; ws.row_dimensions[H3].height = 2698    # --- lignes étudiants99    R0 = H3 + 1100    thresholds = ",".join(str(b[0]) for b in sorted(BAREME)); letters = ",".join(f'"{b[1]}"' for b in sorted(BAREME))101    rng_pts = f"${L(C0)}${H2}:${L(CT-1)}${H2}"102    done_fill = PatternFill("solid", fgColor=VERT)103    for i, (nom, pre, email, grades) in enumerate(students):104        r = R0 + i105        ws.cell(r, 1, i + 1).alignment = Alignment(horizontal="center")106        ws.cell(r, 2, nom).font = Font(name=FONT, bold=True, size=10)107        ws.cell(r, 3, pre)108        for k in range(ne):109            g = grades.get(col_of[k]); cell = ws.cell(r, C0 + k)110            if g is not None:111                cell.value = g; cell.fill = done_fill112            cell.number_format = "0.00"; cell.alignment = Alignment(horizontal="center")113        rg = f"{L(C0)}{r}:{L(CT-1)}{r}"114        ws.cell(r, CT, f'=IF(COUNT({rg})=0,"",SUM({rg}))').number_format = "0.00"115        ws.cell(r, CP, f'=IF(COUNT({rg})=0,"",SUM({rg})/SUMPRODUCT(--ISNUMBER({rg}),{rng_pts})*100)').number_format = '0.0" %"'116        ws.cell(r, CL, f'=IF({L(CP)}{r}="","",LOOKUP(ROUND({L(CP)}{r},0),{{{thresholds}}},{{{letters}}}))')117        for col in (CT, CP, CL):118            ws.cell(r, col).alignment = Alignment(horizontal="center"); ws.cell(r, col).font = Font(name=FONT, bold=(col != CP), size=10)119        ws.cell(r, CT).fill = PatternFill("solid", fgColor=GRIS); ws.cell(r, CL).fill = PatternFill("solid", fgColor=GRIS)120        if i % 2: 121            for col in (1, 2, 3, CR): ws.cell(r, col).fill = PatternFill("solid", fgColor="F8FAFC")122        for col in range(1, CR + 1):123            ws.cell(r, col).border = BORD124            if ws.cell(r, col).font.name != FONT: ws.cell(r, col).font = Font(name=FONT, size=10)125        ws.row_dimensions[r].height = 20126    RN = R0 + len(students) - 1127    # --- statistiques128    RS = RN + 2129    ws.cell(RS - 1, 2, "Statistiques (sur les notes saisies)").font = Font(name=FONT, bold=True, size=9, color=BLEU_F)130    for j, (lbl, fn) in enumerate((("Notes saisies", "COUNT"), ("Moyenne", "AVERAGE"), ("Médiane", "MEDIAN"), ("Minimum", "MIN"), ("Maximum", "MAX"))):131        r = RS + j132        ws.cell(r, 2, lbl).font = Font(name=FONT, size=9, color="475569"); ws.cell(r, 2).border = BORD133        ws.cell(r, 3).border = BORD134        for col in list(range(C0, CT)) + [CT, CP]:135            rg = f"{L(col)}${R0}:{L(col)}${RN}"136            f = f"=COUNT({rg})" if fn == "COUNT" else f'=IF(COUNT({rg})=0,"",{fn}({rg}))'137            cell = ws.cell(r, col, f); cell.number_format = "0" if fn == "COUNT" else ("0.0" if col != CP else '0.0" %"')138            cell.alignment = Alignment(horizontal="center"); cell.font = Font(name=FONT, size=9); cell.border = BORD139            cell.fill = PatternFill("solid", fgColor=GRIS)140    # --- validation (0 … max) et surlignage des cases à saisir141    for k, (key, court, long_, pct, ouv, rem) in enumerate(evals):142        col = L(C0 + k)143        dv = DataValidation(type="decimal", operator="between", formula1="0", formula2=str(pct), allow_blank=True,144                            error=f"Note entre 0 et {pct}", errorTitle=court)145        ws.add_data_validation(dv); dv.add(f"{col}{R0}:{col}{RN}")146    ws.conditional_formatting.add(f"{L(C0)}{R0}:{L(CT-1)}{RN}",147        FormulaRule(formula=[f"ISNUMBER({L(C0)}{R0})"], fill=done_fill, font=Font(name=FONT, bold=True, color="14532D")))148    # --- largeurs, figer, impression149    widths = {1: 5, 2: 24, 3: 20, CT: 10, CP: 15, CL: 14, CR: 34}150    for k in range(ne): widths[C0 + k] = 13151    for col, w in widths.items(): ws.column_dimensions[L(col)].width = w152    ws.freeze_panes = ws.cell(R0, C0)153    ws.print_title_rows = f"{H1}:{H3}"; ws.page_setup.orientation = "landscape"; ws.page_setup.fitToWidth = 1; ws.page_setup.fitToHeight = 0154    ws.sheet_properties.pageSetUpPr.fitToPage = True155    ws.oddFooter.left.text = f"{c['code']} · Grille de notes · Automne 2026"; ws.oddFooter.right.text = "Page &P / &N"156    # légende157    RL = RS + 6158    ws.cell(RL, 2, "Légende").font = Font(name=FONT, bold=True, size=9, color=BLEU_F)159    ws.cell(RL + 1, 2).fill = done_fill; ws.cell(RL + 1, 3, "Note déjà saisie sur Moodle").font = Font(name=FONT, size=9)160    ws.cell(RL + 2, 2).border = BORD; ws.cell(RL + 2, 3, "Case à remplir (évaluation à venir ou à corriger)").font = Font(name=FONT, size=9)161    ws.cell(RL + 3, 3, "Total = somme des points ; % du corrigé = points obtenus ÷ points des évaluations déjà notées ; lettre = barème UQO appliqué au % du corrigé (provisoire tant que tout n'est pas noté ; = total à la fin)").font = Font(name=FONT, size=8.5, italic=True, color="6B7280")162163    # --- feuille Évaluations164    we = wb.create_sheet("Évaluations")165    we.sheet_view.showGridLines = False166    for col, txt in enumerate(("Évaluation", "Description", "Points", "Énoncé / ouverture", "Remise", "Nb notes saisies"), 1):167        cell = we.cell(1, col, txt); cell.fill = hdr_fill; cell.font = hdr_font; cell.alignment = Alignment(vertical="center")168    for k, (key, court, long_, pct, ouv, rem) in enumerate(evals):169        r = 2 + k170        we.cell(r, 1, court).font = Font(name=FONT, bold=True); we.cell(r, 2, long_); we.cell(r, 3, pct)171        we.cell(r, 4, f"{fdate(ouv)} 2026"); we.cell(r, 5, f"{fdate(rem)} 2026" + (" (au plus tard)" if court == "Final" else " · 23 h 59"))172        we.cell(r, 6, f"=COUNT(Notes!{L(C0+k)}{R0}:{L(C0+k)}{RN})")173        for col in range(1, 7): we.cell(r, col).border = BORD; we.cell(r, col).font = Font(name=FONT, size=10, bold=(col == 1))174    we.cell(2 + ne, 1, "Total").font = Font(name=FONT, bold=True); we.cell(2 + ne, 3, f"=SUM(C2:C{1+ne})").font = Font(name=FONT, bold=True)175    for col, w in ((1, 12), (2, 62), (3, 8), (4, 20), (5, 26), (6, 16)): we.column_dimensions[L(col)].width = w176177    # --- feuille Barème178    wbm = wb.create_sheet("Barème UQO")179    wbm.sheet_view.showGridLines = False180    for col, txt in enumerate(("Note (N)", "Lettre", "Appréciation", "Valeur numérique"), 1):181        cell = wbm.cell(1, col, txt); cell.fill = hdr_fill; cell.font = hdr_font182    prev = 101183    for i, (lo, let, app, num) in enumerate(BAREME):184        r = 2 + i185        wbm.cell(r, 1, f"{lo} - {prev - 1}" if lo else "0 - 54"); wbm.cell(r, 2, let); wbm.cell(r, 3, app); wbm.cell(r, 4, num)186        for col in range(1, 5): wbm.cell(r, col).border = BORD; wbm.cell(r, col).font = Font(name=FONT, size=10, bold=(col == 2))187        prev = lo188    wbm.cell(2 + len(BAREME), 1, "S"); wbm.cell(2 + len(BAREME), 3, "Exigence satisfaite")189    wbm.cell(4 + len(BAREME), 1, "La notation doit être arrondie à l'entier supérieur lorsque la première décimale est égale ou supérieure à cinq. "190             "Barème officiel — Règlement des études de premier cycle de l'UQO.").font = Font(name=FONT, size=9, italic=True, color="6B7280")191    for col, w in ((1, 12), (2, 8), (3, 18), (4, 16)): wbm.column_dimensions[L(col)].width = w192193    # --- feuille Source Moodle194    wsm = wb.create_sheet("Source Moodle")195    wsm.append(["Lu le", datetime.now().strftime("%Y-%m-%d %H:%M"), "Cours Moodle", c["moodle_id"], raw.get("title", "")])196    wsm.append(["Nom Moodle (brut)", "Courriel"] + [h["text"] for h in heads])197    for r in raw["rows"]:198        wsm.append([r["name"], r["email"]] + [cell["v"] for cell in r["cells"]])199    for col in range(1, 3 + len(heads)): wsm.column_dimensions[L(col)].width = 28200    wsm.column_dimensions["A"].width = 34201202    for sh in (we, wbm, wsm):203        sh.page_setup.orientation = "landscape"; sh.page_setup.fitToWidth = 1; sh.page_setup.fitToHeight = 1204        sh.sheet_properties.pageSetUpPr.fitToPage = True205    ws.print_area = f"A1:{L(CR)}{RL + 3}"206    out = HERE / f"Grille_de_notes_{code}_A26.xlsx"207    wb.save(out); print("→", out, f"({len(students)} étudiants, {sum(1 for s in students for v in s[3].values() if v is not None and True) } notes saisies incl. total)")208    return students209210if __name__ == "__main__":211    for code in (sys.argv[1:] or COURS):212        build(code)213