#!/usr/bin/php array( mktime(0, 0, 0, 8, 1, 2008), mktime(0, 0, 0, 8, 1, 2009) ), '0910' => array( mktime(0, 0, 0, 8, 1, 2009), mktime(0, 0, 0, 8, 1, 2010) ), '1011' => array( mktime(0, 0, 0, 8, 1, 2010), mktime(0, 0, 0, 8, 1, 2011) ), '1112' => array( mktime(0, 0, 0, 8, 1, 2011), mktime(0, 0, 0, 8, 1, 2012) ), '1213' => array( mktime(0, 0, 0, 8, 1, 2012), mktime(0, 0, 0, 8, 1, 2013) ), '1314' => array( mktime(0, 0, 0, 8, 1, 2013), mktime(0, 0, 0, 8, 1, 2014) ), '1415' => array( mktime(0, 0, 0, 8, 1, 2014), mktime(0, 0, 0, 8, 1, 2015) ), '1516' => array( mktime(0, 0, 0, 8, 1, 2015), mktime(0, 0, 0, 8, 1, 2016) ), '1617' => array( mktime(0, 0, 0, 8, 1, 2016), mktime(0, 0, 0, 8, 1, 2017) ), '1718' => array( mktime(0, 0, 0, 8, 1, 2017), mktime(0, 0, 0, 8, 1, 2018) ), '1819' => array( mktime(0, 0, 0, 8, 1, 2018), mktime(0, 0, 0, 8, 1, 2019) ), '1920' => array( mktime(0, 0, 0, 8, 1, 2019), mktime(0, 0, 0, 8, 1, 2020) ), '2021' => array( mktime(0, 0, 0, 8, 1, 2020), mktime(0, 0, 0, 8, 1, 2021) ), '2122' => array( mktime(0, 0, 0, 8, 1, 2021), mktime(0, 0, 0, 8, 1, 2022) ), '2223' => array( mktime(0, 0, 0, 8, 1, 2022), mktime(0, 0, 0, 8, 1, 2023) ), '2324' => array( mktime(0, 0, 0, 8, 1, 2023), mktime(0, 0, 0, 8, 1, 2024) ), '2425' => array( mktime(0, 0, 0, 8, 1, 2024), mktime(0, 0, 0, 8, 1, 2025) ), '2526' => array( mktime(0, 0, 0, 8, 1, 2025), mktime(0, 0, 0, 8, 1, 2026) ), '2627' => array( mktime(0, 0, 0, 8, 1, 2026), mktime(0, 0, 0, 8, 1, 2027) ), '2728' => array( mktime(0, 0, 0, 8, 1, 2027), mktime(0, 0, 0, 8, 1, 2028) ), ); if ($year_id != 14) error("wrong year_id"); $dit_schooljaar = '2122'; // PAS OOK year_id aan! in config_common.php #if (!preg_match('/Week 10 dag en toets_(\d+).udmz/', $argv[1], $matches)) error_system("wrong file name"); if (!preg_match('/Rooster_OVC_(\d+).udmz/', $argv[1], $matches)) error_system("wrong file name"); //if (!preg_match('/Week 19_(\d+).udmz/', $argv[1], $matches)) error_system("wrong file name"); //if (!preg_match('/Basis 2019-2020_(\d+).udmz/', $argv[1], $matches)) error_system("wrong file name"); //if (!preg_match('/Week 27_(\d+).udmz/', $argv[1], $matches)) error_system("wrong file name"); //if (!preg_match('/Week 9_(\d+).udmz/', $argv[1], $matches)) error_system("wrong file name"); //if (!preg_match('/Week 46 \+ TP_(\d+).udmz/', $argv[1], $matches)) error_system("wrong file name"); $week = $matches[1]; //echo("week=$week"); //exit; // rev 289 is het basisrooster van week 46 // rev 314 is het basisrooster van week 24 if ($week != 223) error_system("verkeerde week"); //if ($week != 107) error_system("verkeerde week"); //$peilmoment = $years['1516'][0]; //$peilmoment = mktime(0,0,0,11,29,2021); // 29 nov 2021 $peilmoment = mktime(0,0,0,3,21,2022); // 29 nov 2021 //$peilmoment = mktime(0,0,0,8,30,2021); // 30 aug 2021 //$peilmoment = mktime(0,0,0,5,11,2021); // 11 mei 2021 //$peilmoment = mktime(0,0,0,3,15,2021); // 15 maart 2021 //$peilmoment = $years['2021'][0]; //$peilmoment = mktime(0,0,0,3,9,2020); // 9 maart 2020 //$peilmoment = mktime(0,0,0,8,1,2019); // 1 augustus 2019 //$peilmoment = mktime(0,0,0,6,24,2019); // 24 juni 2019 //$peilmoment = mktime(0,0,0,2,25,2019); // 25 februari 2019 //$peilmoment = mktime(0,0,0,11,19,2018); // 19 november 2018 //$peilmoment = $years['1819'][0]; //$peilmoment = mktime(0, 0, 0, 7, 3, 2017); // 3 juli 2017 //$peilmoment = mktime(0, 0, 0, 9, 4, 2017); // 4 september //$peilmoment = mktime(0, 0, 0, 11, 13, 2017); // 13 november 2017 //$peilmoment = mktime(0, 0, 0, 3, 5, 2018); // 5 maart 2018 //$peilmoment = mktime(0, 0, 0, 4, 9, 2018); // 9 april 2018 //$peilmoment = mktime(0, 0, 0, 4, 3, 2017); // 3 april 2017 //$peilmoment = mktime(0, 0, 0, 3, 6, 2017); // 6 maart 2017 //$peilmoment = mktime(0, 0, 0, 1, 1, 2016); // 1 jan 2016 echo("peilmoment $peilmoment\n"); //print_r($matches); //exit; $index = array('PREAMBULE' => 0); for ($i = 0; $i < count($lines); $i++) { $lines[$i] = trim($lines[$i]); if ($lines[$i] == '########') { $index[trim($lines[$i+1])] = $i + 2; } } //echo('hoi!'."\n"); $stamz = array(); $oudennieuw = array(); function find_idx($key, $legenda, $section) { if (($idx = array_search($key, $legenda)) === FALSE) error_system("key '$key' not found in legenda of $section"); return $idx; } function find_idxs($keys, $legenda, $section) { $ret = array(); foreach ($keys as $key) $ret[$key] = find_idx($key, $legenda, $section); return $ret; } foreach ($index as $section => $idx) { if (!preg_match('/^Leerling\./', $section)) continue; $idxs = find_idxs(array('ID', 'LASTNAME', 'FIRSTNAME', 'BETWEENNAME', 'BASICCLASS'), explode("\t", $lines[$idx++]), $section); while (($line = $lines[$idx++]) != '########') { $fields = explode("\t", $line); $login = htmlenc(iconv($input_encoding, "UTF-8", trim($fields[$idxs['ID']]))); $naam0 = htmlenc(iconv($input_encoding, "UTF-8", trim($fields[$idxs['LASTNAME']]))); $naam1 = htmlenc(iconv($input_encoding, "UTF-8", trim($fields[$idxs['FIRSTNAME']]))); $naam2 = htmlenc(iconv($input_encoding, "UTF-8", trim($fields[$idxs['BETWEENNAME']]))); $ppl_id = mdb2_single_val($db, "SELECT ppl_id FROM ppl WHERE ppl_login = '%q'", $login); if (!$ppl_id) { mdb2_exec($db, "INSERT INTO ppl ( ppl_login, ppl_surname, ppl_forename, ppl_prefix ) VALUES ( '%q', '%q', '%q', '%q' )", $login, $naam0, $naam1, $naam2); $ppl_id = mdb2_single_val($db, $last_insert_id); } // we willen geen BHC stamklassen importeren vanuit // het rooster van het OVC, dit conflicteert met het // herkansingssysteem van het BHC if (preg_match('/^BHC_(.*)/', $fields[$idxs['BASICCLASS']])) { echo("BHC stamklas gevonden voor $login, negeer -> {$fields[$idxs['BASICCLASS']]}\n"); continue; } echo("leerling + stamklas gevonden $naam1 $naam2 $naam0 $login {$fields[$idxs['BASICCLASS']]}\n"); $tmp = array("login" => $login, "groepen" => array()); $stamz[$fields[$idxs['BASICCLASS']]] = 1; // stamklas $grp_id = mdb2_single_val($db, "SELECT grp_id FROM grp WHERE grp_name = '%q'", $fields[$idxs['BASICCLASS']]); if (!$grp_id) { mdb2_exec($db, "INSERT INTO grp ( grp_name, grp_home ) VALUES ( '%q', 1 )", $fields[$idxs['BASICCLASS']]); $grp_id = mdb2_single_val($db, $last_insert_id); } $tmp['groepen'][$grp_id] = array("grp_name" => $fields[$idxs['BASICCLASS']], "new" => true); $oudennieuw[$ppl_id] = $tmp; if (!($year_info = $years[$dit_schooljaar])) fatal_error("onbekend schooljaar"); } } // we skippen de 'OVERIG' groepen, hier zitten stamklassen van het BHC in!?!?! foreach ($index as $section => $idx) { if (!preg_match('/^Groep\.(.*)/', $section, $matches)) continue; if ($matches[1] == '#uit') continue; if ($matches[1] == 'OVERIG') continue; if ($matches[1] == '#Toets') continue; $idxs = find_idxs(array('ID', 'SET'), explode("\t", $lines[$idx++]), $section); while (($line = $lines[$idx++]) != '########') { $fields = explode("\t", $line); if ($fields[$idxs['ID']] == '#allen') continue; if (isset($stamz[$fields[$idxs['ID']]])) continue; // stamklas, hebben we al $grp_naam = $matches[1].'.'.$fields[$idxs['ID']]; //echo("grp_naam $grp_naam\n"); $grp_id = mdb2_single_val($db, "SELECT grp_id FROM grp WHERE grp_name = '%q'", $grp_naam); if (!$grp_id) { mdb2_exec($db, "INSERT INTO grp ( grp_name, grp_home ) VALUES ( '%q', 0 )", $grp_naam); $grp_id = mdb2_single_val($db, $last_insert_id); } if (!($year_info = $years[$dit_schooljaar])) fatal_error("onbekend schooljaar"); $lln = explode(',', $fields[$idxs['SET']]); if (count($lln) == 1 && $lln[0] === '') continue; foreach ($lln as $ll) { $exploded = explode('.', $ll); if (count($exploded) == 2) $ll = $exploded[1]; //$ll = explode('.', $ll)[0]; $ppl_id = mdb2_single_val($db, "SELECT ppl_id FROM ppl WHERE ppl_login = '%q'", $ll); if (!$ppl_id) { echo("onbekende leerling $ll?!?!?!"); continue; } $oudennieuw[$ppl_id]['groepen'][$grp_id] = array("grp_name" => $grp_naam, "new" => true); } } } if (!($year_info = $years[$dit_schooljaar])) fatal_error("onbekend schooljaar"); foreach ($oudennieuw as $ppl_id => $value) { $result = mdb2_all_ordered_rekey($db, "SELECT grp.grp_id, ppl2grp_id, grp_name FROM ppl2grp JOIN grp ON grp.grp_id = ppl2grp.grp_id WHERE ppl_id = $ppl_id AND ppl_enter <= $peilmoment AND ppl_exit > $peilmoment"); //print_r($result); //exit; //mdb2_res_table($result); foreach ($result as $grp_id => $data) { $oudennieuw[$ppl_id]['groepen'][$grp_id]['old'] = $data[0]; $oudennieuw[$ppl_id]['groepen'][$grp_id]['grp_name'] = $data[1]; } } #print_r($oudennieuw); echo("Mutaties:\n"); foreach ($oudennieuw as $ppl_id => $value) { //echo("leerling: {$value['login']}\n"); foreach ($value['groepen'] as $grp_id => $info) { if (isset($info['new']) && $info['new'] && isset($info['old']) && $info['old']) continue; if (isset($info['new']) && $info['new']) { $timeweird = mdb2_single_val($db, "SELECT ppl_enter FROM ppl2grp WHERE ppl_id = $ppl_id AND grp_id = $grp_id AND ppl_enter > $peilmoment AND ppl_exit <= {$year_info[1]}"); if ($timeweird) error_system("{$value['login']} leerling stuitert naar {$info['grp_name']}!?!?!"); mdb2_exec($db, "INSERT INTO ppl2grp ( ppl_id, grp_id, ppl_enter, ppl_exit ) VALUES ( $ppl_id, $grp_id, $peilmoment, {$year_info[1]} )"); echo("{$value['login']} geplaatst in {$info['grp_name']}\n"); } if (isset($info['old']) && $info['old']) { mdb2_exec($db, "UPDATE ppl2grp SET ppl_exit = $peilmoment WHERE ppl2grp_id = {$info['old']}"); echo("{$value['login']} verwijderd uit {$info['grp_name']}\n"); } } } $idx = $index['Docent']; $idxs = find_idxs(array('ID'), //, 'Achternaam', 'Voornaam', 'Tussenvoegsel'), explode("\t", $lines[$idx++]), $section); while (($line = $lines[$idx++]) != '########') { $fields = explode("\t", $line); $login = htmlenc(iconv($input_encoding, "UTF-8", trim($fields[$idxs['ID']]))); $naam0 = ''; //htmlenc(iconv($input_encoding, "UTF-8", trim($fields[$idxs['Achternaam']]))); $naam1 = ''; //htmlenc(iconv($input_encoding, "UTF-8", trim($fields[$idxs['Voornaam']]))); $naam2 = ''; //htmlenc(iconv($input_encoding, "UTF-8", trim($fields[$idxs['Tussenvoegsel']]))); $ppl_id = mdb2_single_val($db, "SELECT ppl_id FROM ppl WHERE ppl_login = '%q'", $login); if (!$ppl_id) { mdb2_exec($db, "INSERT INTO ppl ( ppl_login, ppl_surname, ppl_forename, ppl_prefix ) VALUES ( '%q', '%q', '%q', '%q' )", $login, $naam0, $naam1, $naam2); $ppl_id = mdb2_single_val($db, $last_insert_id); } } $idx = $index['Les']; $idxs = find_idxs(array('Grp', 'Doc', 'Vak'), explode("\t", $lines[$idx++]), $section); $stamklasvak = array(); while (($line = $lines[$idx++]) != '########') { $fields = explode("\t", $line); if ($fields[$idxs['Grp']] == '') continue; if ($fields[$idxs['Doc']] == '') continue; if ($fields[$idxs['Vak']] == '') continue; $lesgroepen = explode(',', $fields[$idxs['Grp']]); $docenten = explode(',', $fields[$idxs['Doc']]); $vakken = explode(',', $fields[$idxs['Vak']]); if (count($vakken) == 1) { $vak = $vakken[0]; $subj_id = mdb2_single_val($db, "SELECT subj_id FROM subj WHERE subj_abbrev = '%q'", $vak); if (!$subj_id) { mdb2_exec($db, "INSERT INTO subj ( subj_abbrev ) VALUES ( '%q' )", $vak); $subj_id = mdb2_single_val($db, $last_insert_id); } foreach ($docenten as $docent) { $ppl_id = mdb2_single_val($db, "SELECT ppl_id FROM ppl WHERE ppl_login = '%q'", $docent); if (!$ppl_id) error_system("docent niet gevonden"); foreach ($lesgroepen as $lesgroep) { // stamklas? if (preg_match('/\.(.*)$/', $lesgroep, $matches)) { //print_r($matches); if (isset($stamz[$matches[1]])) { $lesgroep = $matches[1]; if (!isset($stamklasvak[$matches[1]])) $stamklasvak[$matches[1]] = array(); $stamklasvak[$matches[1]][$subj_id] = 1; } } // else: lesgroep heeft geen punt en is bovenbouw stamklas $grp_id = mdb2_single_val($db, "SELECT grp_id FROM grp WHERE grp_name = '%q'", $lesgroep); if (!$grp_id) error_system("lesgroep $lesgroep bestaat niet?!!?"); $grp2subj_id = mdb2_single_val($db, "SELECT grp2subj_id FROM grp2subj WHERE grp_id = $grp_id AND subj_id = $subj_id"); if (!$grp2subj_id) { mdb2_exec($db, "INSERT INTO grp2subj ( grp_id, subj_id ) VALUES ( $grp_id, $subj_id )"); $grp2subj_id = mdb2_single_val($db, $last_insert_id); } $year2grp2subj_id = mdb2_single_val($db, "SELECT year2grp2subj_id FROM year2grp2subj WHERE year_id = $year_id AND grp2subj_id = $grp2subj_id AND grp2subj_enter <= $peilmoment AND grp2subj_exit > $peilmoment"); if (!$year2grp2subj_id) { // koppel deze grp2subj aan dit schooljaar mdb2_exec($db, "INSERT INTO year2grp2subj ( year_id, grp2subj_id, grp2subj_enter, grp2subj_exit ) VALUES ( $year_id, $grp2subj_id, $peilmoment, {$year_info[1]} )"); } if (!($year_info = $years[$dit_schooljaar])) fatal_error("onbekend schooljaar"); $ppl2grp2subj_id = mdb2_single_val($db, "SELECT ppl2grp2subj_id FROM ppl2grp2subj WHERE ppl_id = $ppl_id AND grp2subj_id = $grp2subj_id AND ppl_enter = {$year_info[0]} AND ppl_exit = {$year_info[1]}"); if (!$ppl2grp2subj_id) { echo("add $docent on $lesgroep / $vak\n"); mdb2_exec($db, "INSERT INTO ppl2grp2subj ( ppl_id, grp2subj_id, ppl_enter, ppl_exit ) VALUES ( $ppl_id, $grp2subj_id, {$year_info[0]}, {$year_info[1]} )"); } } } continue; } echo("lesgroepen:\n"); print_r($lesgroepen); echo("docenten:\n"); print_r($docenten); echo("vakken:\n"); print_r($vakken); //exit; } //print_r($index); //print_r($stamklasvak); echo("controleer alle stamklassen of ze bepaalde vakken opeens niet meer klassikaal hebben\n"); $res = mdb2_query($db, << $peilmoment ) AS actualppl2grp USING (grp_id) WHERE grp_home = 1 GROUP BY grp_id ORDER BY grp_name EOQ ); while ($row = $res->fetchRow(MDB2_FETCHMODE_ASSOC)) { if (!isset($stamklasvak[$row['grp_name']])) continue; $vakken = mdb2_all_assoc_rekey($db, << $peilmoment ) AS actualgrp2subj USING (grp2subj_id) WHERE grp_id = {$row['grp_id']} EOQ ); //echo("considering stamklas {$row['grp_name']}\n"); foreach ($vakken as $subj_id => $info) { if (!isset($stamklasvak[$row['grp_name']][$subj_id])) { echo("stamklas {$row['grp_name']} heeft {$info['subj_abbrev']} niet meer klassikaal\n"); mdb2_exec($db, "UPDATE year2grp2subj SET grp2subj_exit = $peilmoment WHERE year2grp2subj_id = {$info['year2grp2subj_id']}"); //echo("UPDATE year2grp2subj SET grp2subj_exit = $peilmoment WHERE year2grp2subj_id = {$info['year2grp2subj_id']}\n"); } } //print_r($vakken); //print_r($stamklasvak[$row['grp_name']]); } exit; header('Location: index.php'); ?>