<?
include('config.php');
include('phplib/phplib.php');

$db = mdb2_open($dsn);

$lines = gzfile($_POST['file']);

if ($lines === false) error_system("file {$_POST['file']} does not exist");

$input_encoding = 'ISO-8859-1';

header('Content-Type: text/plain');
header('Content-Disposition: inline; filename=info.txt');

$years = array (
	'0809' => 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) )
);

$dit_schooljaar = '1415';
if (!preg_match('/Schooljaar 2014-2015_(\d+).udmz/', $_POST['file'], $matches)) error_system("wrong file name");
$week = $matches[1];
//echo("week=$week");
//exit;
// rev 257 is het basisrooster van week 46
if ($week != 257) error_system("verkeerde week");
$peilmoment = mktime(0, 0, 0, 11, 10, 2014); //maandag in week 46
echo("peilmoment $peilmoment\n");
//print_r($matches);

$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;
	$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) {
			$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 ($info['new'] && $info['old']) continue;
		if ($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 ($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");
		}
	}
}
//exit;

$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);
	}
}

$year_id = 7; // 2014/2015

$idx = $index['Les'];
$idxs = find_idxs(array('Grp', 'Doc', 'Vak'),
	explode("\t", $lines[$idx++]), $section);

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];
				}  // 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");
				if (!$year2grp2subj_id) {
					// koppel deze grp2subj aan dit schooljaar
					mdb2_exec($db, "INSERT INTO year2grp2subj ( year_id, grp2subj_id ) VALUES ( $year_id, $grp2subj_id )");
				}

				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) {
					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);

exit;
header('Location: index.php');
?>