""" Beheer › Database › Eigendom: toekenningen toestel ↔ leerling (tabel Eigendom) aanvullen vanuit een Excel-bestand. Zelfde werkwijze als de toestellen-import (services/toestellen_import.py): 1. lees_excel() : het geüploade bestand → ruwe rijen (enkel tekst / ISO-datums) 2. maak_plan() : ruwe rijen → per rij 'nieuw', 'bijwerken', 'ongewijzigd' of 'fout' (niets wordt bewaard) 3. voer_uit() : het plan in één transactie uitvoeren (het plan wordt opnieuw gemaakt, nooit blind vertrouwd) Elke rij is één toekenning: een leerling (internnummer, Smartschool-nummer of gebruikersnaam) en een toestel (toestelnummer of serienummer) die al in PIVOT bestaan, met een datum van toekenning en eventueel van inlevering. - Zonder datum inlevering heeft de leerling het toestel nu (toestel → 'Uitgeleend'). - Met datum inlevering is het geschiedenis (de status van het toestel verandert niet). - Bestaat dezelfde toekenning al (zelfde leerling, toestel en datum toekenning; of de huidige als die datum leeg is), dan wordt enkel een lege datum inlevering aangevuld (= inleveren, toestel → 'Beschikbaar'). - Een toestel kan nooit tegelijk bij twee leerlingen zijn. Staat het nu bij een andere leerling, dan is dat een fout, of (keuze 'overschrijven') wordt die toekenning eerst afgesloten op de nieuwe datum toekenning. Er wordt nooit iets verwijderd. """ import io from datetime import date, datetime from database import execute_query, get_db_connection from services.toestellen_import import ImportFout, _norm, _cel, _datum_tekst, _toon MAX_RIJEN = 5000 # (sleutel, kolomkop in het sjabloon, max. lengte, uitleg, andere namen die ook herkend worden) KOLOMMEN = [ ('leerling', 'Leerling', 255, "Internnummer, Smartschool-nummer of gebruikersnaam van een leerling die al in PIVOT staat. Verplicht.", ('internnummer', 'intern nummer', 'leerlingnummer', 'ss nummer', 'smartschool nummer', 'gebruikersnaam')), ('toestel', 'Toestel', 100, "Toestelnummer (Schooleigen_ID) of serienummer van een toestel dat al in PIVOT staat. Verplicht.", ('toestelnummer', 'schooleigen id', 'kamxm nummer', 'kamxm', 'serienummer', 'serial', 'serial number')), ('datum_toekenning', 'Datum toekenning', None, "Datum (dd-mm-jjjj) waarop de leerling het toestel kreeg. Leeg = vandaag " "(verplicht als de datum inlevering ingevuld is).", ('toekenning', 'datum uitdeling', 'uitgedeeld', 'uitgedeeld op', 'toegekend op')), ('datum_inlevering', 'Datum inlevering', None, "Datum (dd-mm-jjjj) waarop het toestel terug ingeleverd werd. Leeg = de leerling heeft het toestel nu.", ('inlevering', 'ingeleverd', 'ingeleverd op', 'datum inleveren')), ] SLEUTELS = [k[0] for k in KOLOMMEN] KOPPEN = {k[0]: k[1] for k in KOLOMMEN} _HERKEN = {} for _k, _kop, _m, _u, _alias in KOLOMMEN: for _naam in (_k, _kop) + tuple(_alias): _HERKEN[_norm(_naam)] = _k # --------------------------------------------------------------------------- # Sjabloon # --------------------------------------------------------------------------- def maak_sjabloon(schoolnaam=''): """Excel-sjabloon (bytes): tabblad Eigendom (in te vullen) en Uitleg.""" from openpyxl import Workbook from openpyxl.styles import Font, PatternFill, Alignment from openpyxl.worksheet.datavalidation import DataValidation wb = Workbook() ws = wb.active ws.title = 'Eigendom' ws.append([k[1] for k in KOLOMMEN]) kop_vulling = PatternFill('solid', fgColor='1E6FA5') for c in ws[1]: c.font = Font(bold=True, color='FFFFFF') c.fill = kop_vulling c.alignment = Alignment(vertical='center') breedtes = {'Leerling': 20, 'Toestel': 20, 'Datum toekenning': 18, 'Datum inlevering': 18} for i, k in enumerate(KOLOMMEN): letter = ws.cell(row=1, column=i + 1).column_letter ws.column_dimensions[letter].width = breedtes.get(k[1], 16) ws.freeze_panes = 'A2' ws.row_dimensions[1].height = 22 kol = {k[0]: ws.cell(row=1, column=i + 1).column_letter for i, k in enumerate(KOLOMMEN)} # Nummers als tekst (anders maakt Excel van 00123 → 123) for sleutel in ('leerling', 'toestel'): for r in range(2, MAX_RIJEN + 2): ws[f"{kol[sleutel]}{r}"].number_format = '@' for sleutel in ('datum_toekenning', 'datum_inlevering'): for r in range(2, MAX_RIJEN + 2): ws[f"{kol[sleutel]}{r}"].number_format = 'DD-MM-YYYY' dv_datum = DataValidation(type='date', operator='greaterThan', formula1='1', allow_blank=True, showErrorMessage=True, errorTitle=KOPPEN[sleutel], error='Vul een datum in, bv. 01-09-2026.') dv_datum.add(f"{kol[sleutel]}2:{kol[sleutel]}{MAX_RIJEN + 1}") ws.add_data_validation(dv_datum) # Uitleg wu = wb.create_sheet('Uitleg') wu.column_dimensions['A'].width = 18 wu.column_dimensions['B'].width = 95 wu.append([f"Eigendom importeren in PIVOT{' – ' + schoolnaam if schoolnaam else ''}"]) wu['A1'].font = Font(bold=True, size=14) regels = [ "Vul het tabblad 'Eigendom' in: één toekenning (leerling + toestel) per rij, vanaf rij 2. " "Laat de kolomkoppen in rij 1 staan.", "De leerling en het toestel moeten al in PIVOT staan. Nieuwe toestellen voeg je eerst toe via " "Database › Toestellen importeren.", "Zonder datum inlevering heeft de leerling het toestel nu: het toestel krijgt de status Uitgeleend.", "Met een datum inlevering wordt de toekenning als geschiedenis bewaard.", "Bestaat de toekenning al, dan wordt enkel een lege datum inlevering aangevuld (= het toestel inleveren).", "Een toestel kan niet tegelijk bij twee leerlingen zijn. Staat het nu bij een andere leerling, dan kies je bij " "het uploaden of dat een fout is of dat die toekenning eerst afgesloten wordt.", f"Er wordt nooit iets verwijderd. Je krijgt eerst een voorbeeld te zien. Maximaal {MAX_RIJEN} rijen per bestand.", ] for t in regels: wu.append(['', t]) wu.append([]) wu.append(['Kolom', 'Uitleg']) for c in wu[wu.max_row]: c.font = Font(bold=True) for _k, kop, maxlen, uitleg, _a in KOLOMMEN: wu.append([kop, uitleg]) for rij in wu.iter_rows(min_row=2): for c in rij: c.alignment = Alignment(wrap_text=True, vertical='top') wu.append([]) wu.append(['Voorbeeld', 'Leerling 12345 · Toestel CB-0123 · Datum toekenning 01-09-2026 · Datum inlevering leeg']) wb.active = 0 buf = io.BytesIO() wb.save(buf) return buf.getvalue() # --------------------------------------------------------------------------- # Excel lezen # --------------------------------------------------------------------------- def lees_excel(bestand): """Leest een .xlsx (bestandsobject of bytes). Geeft (rijen, onbekende_kolommen). rijen = [{'rij': , 'leerling': '...', ...}] (enkel niet-lege rijen).""" from openpyxl import load_workbook data = bestand if isinstance(bestand, (bytes, bytearray)) else bestand.read() if not data: raise ImportFout("Het bestand is leeg.") try: wb = load_workbook(io.BytesIO(data), read_only=True, data_only=True) except Exception: raise ImportFout("Dit bestand kan niet gelezen worden. Gebruik een Excel-bestand (.xlsx), " "bv. het sjabloon van deze pagina.") try: ws = wb['Eigendom'] if 'Eigendom' in wb.sheetnames else wb.worksheets[0] rijen_iter = ws.iter_rows(values_only=True) # Kopregel zoeken in de eerste 10 rijen: de eerste rij met een herkende kolom kop_index, onbekend, rijnr = None, [], 0 for rij in rijen_iter: rijnr += 1 gevonden = {i: _HERKEN[_norm(v)] for i, v in enumerate(rij or ()) if _norm(v) in _HERKEN} if gevonden: kop_index = {} for i, sleutel in gevonden.items(): kop_index.setdefault(sleutel, i) # dubbele kolom: de eerste telt onbekend = [_cel(v) for i, v in enumerate(rij) if _cel(v) and i not in gevonden] break if rijnr >= 10: break if not kop_index: raise ImportFout("Geen kolomkoppen gevonden. Rij 1 moet de kolomnamen van het sjabloon bevatten " f"({', '.join(KOPPEN.values())}).") ontbreekt = [KOPPEN[s] for s in ('leerling', 'toestel') if s not in kop_index] if ontbreekt: raise ImportFout(f"De kolom {' en '.join(ontbreekt)} ontbreekt.") uit = [] for rij in rijen_iter: rijnr += 1 waarden = {s: _cel(rij[i]) if rij and i < len(rij) else '' for s, i in kop_index.items()} if not any(waarden.values()): continue if len(uit) >= MAX_RIJEN: raise ImportFout(f"Het bestand bevat meer dan {MAX_RIJEN} rijen. Splits het op in kleinere bestanden.") uit.append({'rij': rijnr, **{s: waarden.get(s, '') for s in SLEUTELS}}) return uit, onbekend finally: wb.close() # --------------------------------------------------------------------------- # Plan maken (niets bewaren) # --------------------------------------------------------------------------- def _d(w): return w.date() if isinstance(w, datetime) else w def _overlapt(begin1, einde1, begin2, einde2): """Overlappen twee periodes? Einde None = nog niet ingeleverd. Inleveren en opnieuw uitdelen op dezelfde dag mag.""" return (einde2 is None or begin1 < einde2) and (einde1 is None or begin2 < einde1) def _naam(l): naam = ' '.join(x for x in (l.get('Voornaam'), l.get('Naam')) if x) return f"{naam} ({l['Internnummer']})" if naam else l['Internnummer'] def _toestel_label(t): return t.get('Schooleigen_ID') or t.get('Serienummer') or str(t['Toestel_ID']) def maak_plan(rijen, modus='aanvullen', vandaag=None): """Bepaalt per rij wat er zou gebeuren. modus: 'aanvullen' (toestel bij een andere leerling = fout) of 'overschrijven' (die toekenning eerst afsluiten; ook een andere datum inlevering wordt dan overschreven). Geeft {'rijen': [...], 'tellers': {...}}; elke rij heeft 'actie', 'meldingen', 'wijzigingen' en wat er in de database moet gebeuren ('waarden', 'eigendom_id', 'sluit', 'toestel_status').""" overschrijven = modus == 'overschrijven' vandaag = vandaag or date.today() # Leerlingen: internnummer gaat voor Smartschool-nummer, dat gaat voor gebruikersnaam leerlingen = execute_query("SELECT Internnummer, SS_nummer, Gebruikersnaam, Naam, Voornaam, Vertrokken_op " "FROM Leerlingen") or [] op_leerling = {} for veld in ('Gebruikersnaam', 'SS_nummer', 'Internnummer'): for l in leerlingen: w = str(l[veld] or '').strip().upper() if w: op_leerling[w] = l toestellen = [dict(t) for t in execute_query("SELECT * FROM Toestellen") or []] op_nummer = {(t['Schooleigen_ID'] or '').strip().upper(): t for t in toestellen if (t['Schooleigen_ID'] or '').strip()} op_serie = {} for t in toestellen: s = (t['Serienummer'] or '').strip().upper() if s: op_serie.setdefault(s, t) toestel_op_id = {t['Toestel_ID']: t for t in toestellen} # Bestaande toekenningen per toestel (kopie: het plan past ze aan zodat latere rijen ermee rekening houden) per_toestel, actief_per_leerling = {}, {} for e in execute_query("SELECT Eigendom_ID, Internnummer, Toestel_ID, Datum_toekenning, Datum_inlevering " "FROM Eigendom") or []: e = {**e, 'Datum_toekenning': _d(e['Datum_toekenning']), 'Datum_inlevering': _d(e['Datum_inlevering']), 'rij': None} per_toestel.setdefault(e['Toestel_ID'], []).append(e) if e['Datum_inlevering'] is None: actief_per_leerling.setdefault(e['Internnummer'], set()).add(e['Toestel_ID']) uit = [] tellers = {'nieuw': 0, 'bijwerken': 0, 'ongewijzigd': 0, 'fout': 0} for invoer in rijen: r = {'rij': invoer.get('rij'), 'invoer': {s: str(invoer.get(s) or '').strip() for s in SLEUTELS}, 'meldingen': [], 'wijzigingen': [], 'actie': None, 'label': None, 'waarden': {}, 'toestel_id': None, 'eigendom_id': None, 'sluit': None, 'toestel_status': None} v = r['invoer'] fouten = [] # --- waarden controleren --- leerling = toestel = None if not v['leerling']: fouten.append("Leerling is verplicht.") else: leerling = op_leerling.get(v['leerling'].upper()) if not leerling: fouten.append(f"Leerling '{v['leerling']}' staat niet in PIVOT.") if not v['toestel']: fouten.append("Toestel is verplicht.") else: toestel = op_nummer.get(v['toestel'].upper()) or op_serie.get(v['toestel'].upper()) if not toestel: fouten.append(f"Toestel '{v['toestel']}' staat niet in PIVOT. Voeg het eerst toe via " f"Database › Toestellen.") begin = einde = None for s in ('datum_toekenning', 'datum_inlevering'): if v[s]: try: d = _datum_tekst(v[s]) except ValueError: fouten.append(f"{KOPPEN[s]} '{v[s]}' is geen geldige datum (gebruik dd-mm-jjjj).") continue if d > vandaag: fouten.append(f"{KOPPEN[s]} {_toon(d)} ligt in de toekomst.") elif s == 'datum_toekenning': begin = d else: einde = d if v['datum_inlevering'] and not v['datum_toekenning']: fouten.append("Datum toekenning is verplicht als de datum inlevering ingevuld is.") if begin and einde and einde < begin: fouten.append("Datum inlevering ligt vóór de datum toekenning.") if not fouten: intern = leerling['Internnummer'] tid = r['toestel_id'] = toestel['Toestel_ID'] r['label'] = f"{_naam(leerling)} · toestel {_toestel_label(toestel)}" lijst = per_toestel.setdefault(tid, []) # --- bestaat deze toekenning al? --- if begin: bestaand = next((e for e in lijst if e['Internnummer'] == intern and e['Datum_toekenning'] == begin), None) else: bestaand = next((e for e in lijst if e['Internnummer'] == intern and e['Datum_inlevering'] is None), None) if not bestaand and not einde: # de leerling heeft dit toestel al (sinds een andere datum) bestaand = next((e for e in lijst if e['Internnummer'] == intern and e['Datum_inlevering'] is None), None) if bestaand: if bestaand['rij']: fouten.append(f"Deze toekenning staat al op rij {bestaand['rij']}.") elif einde and einde != bestaand['Datum_inlevering'] and (bestaand['Datum_inlevering'] is None or overschrijven): andere = [e for e in lijst if e is not bestaand] botsing = next((e for e in andere if _overlapt(bestaand['Datum_toekenning'], einde, e['Datum_toekenning'], e['Datum_inlevering'])), None) if botsing: fouten.append(f"Het toestel was vanaf {_toon(botsing['Datum_toekenning'])} al bij " f"leerling {botsing['Internnummer']}.") else: r['actie'] = 'bijwerken' r['eigendom_id'] = bestaand['Eigendom_ID'] r['waarden'] = {'Datum_inlevering': einde} r['wijzigingen'].append(f"Datum inlevering: {_toon(bestaand['Datum_inlevering']) or '–'} " f"→ {_toon(einde)}") if bestaand['Datum_inlevering'] is None: actief_per_leerling.get(intern, set()).discard(tid) if toestel['Status'] == 'Uitgeleend': r['toestel_status'] = 'Beschikbaar' r['wijzigingen'].append("Toestel: Uitgeleend → Beschikbaar") toestel['Status'] = 'Beschikbaar' bestaand['Datum_inlevering'] = einde bestaand['rij'] = r['rij'] else: r['actie'] = 'ongewijzigd' if einde and bestaand['Datum_inlevering'] and einde != bestaand['Datum_inlevering']: r['meldingen'].append(f"Niet overschreven: datum inlevering " f"({_toon(bestaand['Datum_inlevering'])}).") elif bestaand['Datum_inlevering'] is None: r['meldingen'].append(f"Heeft dit toestel al sinds {_toon(bestaand['Datum_toekenning'])}.") else: r['meldingen'].append("Deze toekenning staat al in PIVOT.") # --- nieuwe toekenning --- else: begin = begin or vandaag sluit = None botsingen = [e for e in lijst if _overlapt(begin, einde, e['Datum_toekenning'], e['Datum_inlevering'])] if (overschrijven and not einde and len(botsingen) == 1 and botsingen[0]['Datum_inlevering'] is None and botsingen[0]['Datum_toekenning'] <= begin and botsingen[0]['rij'] is None): sluit = botsingen.pop() for e in botsingen: waar = f"op rij {e['rij']}" if e['rij'] else "in PIVOT" if e['Datum_inlevering'] is None: tip = '' if overschrijven or e['rij'] else ' Kies "Vorige toekenning afsluiten" of lever het eerst in.' fouten.append(f"Het toestel staat {waar} sinds {_toon(e['Datum_toekenning'])} bij leerling " f"{e['Internnummer']}.{tip}") else: fouten.append(f"Het toestel was {waar} van {_toon(e['Datum_toekenning'])} tot " f"{_toon(e['Datum_inlevering'])} bij leerling {e['Internnummer']}.") break if not fouten: r['actie'] = 'nieuw' r['waarden'] = {'Internnummer': intern, 'Toestel_ID': tid, 'Status': 1 if int(toestel.get('Geblokkeerd') or 0) else 0, 'Datum_toekenning': begin, 'Datum_inlevering': einde} if not v['datum_toekenning']: r['wijzigingen'].append(f"Datum toekenning: vandaag ({_toon(begin)})") if sluit: r['sluit'] = {'eigendom_id': sluit['Eigendom_ID'], 'datum': begin} r['wijzigingen'].append(f"Vorige toekenning bij leerling {sluit['Internnummer']} afgesloten " f"op {_toon(begin)}") actief_per_leerling.get(sluit['Internnummer'], set()).discard(tid) sluit['Datum_inlevering'] = begin sluit['rij'] = r['rij'] if not einde: andere = [toestel_op_id[t] for t in actief_per_leerling.get(intern, ()) if t in toestel_op_id] if andere: r['meldingen'].append("Heeft ook nog toestel " + ', '.join(_toestel_label(t) for t in andere) + '.') actief_per_leerling.setdefault(intern, set()).add(tid) if toestel['Status'] != 'Uitgeleend': r['toestel_status'] = 'Uitgeleend' r['wijzigingen'].append(f"Toestel: {toestel['Status'] or '–'} → Uitgeleend") toestel['Status'] = 'Uitgeleend' lijst.append({'Eigendom_ID': None, 'Internnummer': intern, 'Toestel_ID': tid, 'Datum_toekenning': begin, 'Datum_inlevering': einde, 'rij': r['rij']}) if not fouten and leerling.get('Vertrokken_op'): r['meldingen'].append(f"Let op: leerling vertrokken op {_toon(_d(leerling['Vertrokken_op']))}.") if fouten: r['actie'] = 'fout' r['meldingen'] = fouten r['wijzigingen'] = [] r['waarden'], r['sluit'], r['toestel_status'], r['eigendom_id'] = {}, None, None, None tellers[r['actie']] += 1 uit.append(r) return {'rijen': uit, 'tellers': tellers, 'modus': 'overschrijven' if overschrijven else 'aanvullen'} # --------------------------------------------------------------------------- # Uitvoeren # --------------------------------------------------------------------------- def voer_uit(rijen, modus, gebruiker=None): """Maakt het plan opnieuw en voert 'nieuw' en 'bijwerken' uit in één transactie. Geeft (plan, aantal_nieuw, aantal_bijgewerkt). Bij een databasefout wordt niets bewaard.""" plan = maak_plan(rijen, modus) conn = get_db_connection() cur = conn.cursor() nieuw = bijgewerkt = 0 try: conn.start_transaction() for r in plan['rijen']: if r['actie'] not in ('nieuw', 'bijwerken'): continue if r['sluit']: cur.execute("UPDATE Eigendom SET Datum_inlevering = %s WHERE Eigendom_ID = %s", (r['sluit']['datum'], r['sluit']['eigendom_id'])) w = r['waarden'] if r['actie'] == 'nieuw': kolommen = list(w.keys()) cur.execute(f"INSERT INTO Eigendom ({', '.join(kolommen)}) " f"VALUES ({', '.join(['%s'] * len(kolommen))})", tuple(w[k] for k in kolommen)) nieuw += 1 else: cur.execute("UPDATE Eigendom SET Datum_inlevering = %s WHERE Eigendom_ID = %s", (w['Datum_inlevering'], r['eigendom_id'])) bijgewerkt += 1 if r['toestel_status']: cur.execute("UPDATE Toestellen SET Status = %s WHERE Toestel_ID = %s", (r['toestel_status'], r['toestel_id'])) conn.commit() except Exception: conn.rollback() raise finally: cur.close() conn.close() return plan, nieuw, bijgewerkt