#!/usr/bin/php
<?
require 'config.php';
require 'common.php';

function read_semicolonsv($filename) {
	$lines = array();
	if (!($handle = fopen($filename, 'r'))) die("unable to open $filename\n");

	while (true) {
		$tmp = fgetcsv($handle, 0, ';');
		if ($tmp === NULL) die("invalid handle, impossible!\n");
		if (feof($handle)) break;
		if ($tmp === false) die("some error that is not EOF?");
		
		// was the line empty?
		if (count($tmp) == 1 && $tmp[0] === NULL) continue;

		$lines[] = $tmp;
	}

	fclose($handle);

	return $lines;
}

$db = mdb2_open($dsn);

$ll = read_semicolonsv('dbo_Leerling.txt');
echo(count($ll)." leerlingen ingelezen\n");
$lg = read_semicolonsv('dbo_Lesgroepen.txt');
echo(count($lg)." lesgroepen ingelezen\n");
$lg2ll = read_semicolonsv('dbo_LesgroepLeerling.txt');
echo(count($lg2ll)." koppelingen ingelezen\n");

foreach ($ll as $leerling) {
	// in de eerste en tweede kolom staat altijd hetzelfde?
	if ($leerling[0] != $leerling[1]) die('mismatch '.$leerling[0].' '.$leerling[1]."\n");
}

$ll2ppl_id = mdb2_all_ordered_rekey($db, "SELECT login, ppl_id, naam0, naam1, naam2 FROM ppl WHERE type = 'leerling'");
$doc2ppl_id = mdb2_all_ordered_rekey($db, "SELECT login, ppl_id, naam0, naam1, naam2 FROM ppl WHERE type = 'personeel'");
$vak2vak_id = mdb2_all_ordered_rekey($db, "SELECT afkorting, vak_id FROM vak");
$grp2grp_id = mdb2_all_ordered_rekey($db, "SELECT naam, grp_id FROM grp WHERE schooljaar = '$schooljaar' AND grp_type_id = 2");
$grp2vak2grp2vak_id = mdb2_all_ordered_rekey($db, "SELECT CONCAT(grp.naam, '/', afkorting), grp2vak_id FROM grp2vak JOIN grp USING (grp_id) JOIN vak USING (vak_id) WHERE schooljaar = '$schooljaar' AND grp_type_id = 2");

// we lopen langs alle leerlingen in ll
// - als we een leerling nog niet hebben, dan voegen we die toe
// - als we een leerling wel hebben, dan updaten we de naam en maken daar melding van

foreach ($ll as $leerling) {
	$naam0 = htmlenc(iconv($input_encoding, "UTF-8", trim($leerling[2])));
	$naam1 = htmlenc(iconv($input_encoding, "UTF-8", trim($leerling[4])));
	$naam2 = htmlenc(iconv($input_encoding, "UTF-8", trim($leerling[3])));
	$login = htmlenc(iconv($input_encoding, "UTF-8", trim($leerling[0])));

	// hier zorgen we dat gangbare nederlandse tussenvoegsels
	// in kleine letters komen
	if (!strcasecmp($naam2, 'van')) $naam2 = 'van';
	else if (!strcasecmp($naam2, 'van der')) $naam2 = 'van der';
	else if (!strcasecmp($naam2, 'van den')) $naam2 = 'van den';
	else if (!strcasecmp($naam2, 'van de')) $naam2 = 'van de';
	else if (!strcasecmp($naam2, 'ter')) $naam2 = 'ter';
	else if (!strcasecmp($naam2, 'ten')) $naam2 = 'ten';
	else if (!strcasecmp($naam2, 'de')) $naam2 = 'de';
	else if (!strcasecmp($naam2, 'den')) $naam2 = 'den';
	else if (!strcasecmp($naam2, 'van &#039;t')) $naam2 = 'van &#039;t';

	if (!isset($ll2ppl_id[$login])) {
		echo("nieuwe leerling $login $naam0 $naam1 $naam2\n");
		mdb2_exec($db, "INSERT INTO ppl ( naam0, naam1, naam2, login, type ) VALUES ( '%q', '%q', '%q', '%q', 'leerling' )",
				$naam0, $naam1, $naam2, $login);
		$ppl_id = mdb2_single_val($db, "SELECT LAST_INSERT_ID()");
		$ll2ppl_id[$login] = array($ppl_id, $naam0, $naam1, $naam2);
	} else {
		// deze leerling hebben we al, kijken of de naam gewijzigd is
		if (strcmp($ll2ppl_id[$login][1], $naam0) || strcmp($ll2ppl_id[$login][2], $naam1) || strcmp($ll2ppl_id[$login][3], $naam2)) {
			echo("naamswijziging $login '{$ll2ppl_id[$login][2]} {$ll2ppl_id[$login][3]} {$ll2ppl_id[$login][1]}' -> '$naam1 $naam2 $naam0'\n");
			mdb2_exec($db, "UPDATE ppl SET naam0 = '%q', naam1 = '%q', naam2 = '%q' WHERE login = '%q'",
				$naam0, $naam1, $naam2, $login);
			$ll2ppl_id[$login][1]  = $naam0;
			$ll2ppl_id[$login][2]  = $naam1;
			$ll2ppl_id[$login][3]  = $naam2;
		}
	}
}

foreach ($lg as $lesgroep) {
	$grp = $lesgroep[0];
	$vak = $lesgroep[1];
	$grp2vak = $grp.'/'.$vak;
	$doc = $lesgroep[3];
	if (!isset($grp2vak2grp2vak_id[$grp2vak])) {
		// deze grp2vak hebben we nog niet
		echo('grp2vak '.$grp2vak.' is nieuw'."\n");
		if (!isset($vak2vak_id[$vak])) {
			echo('vak '.$vak.' is nieuw'."\n");
			mdb2_exec($db, "INSERT INTO vak ( afkorting ) VALUES ( '%q' )", htmlenc($vak));
			$vak2vak_id[$vak] = mdb2_single_val($db, "SELECT LAST_INSERT_ID()");
		}
		if (!isset($grp2grp_id[$grp])) {
			echo('grp '.$grp.' is nieuw'."\n");
			mdb2_exec($db, "INSERT INTO grp ( grp_type_id, schooljaar, naam ) VALUES ( 2, '$schooljaar', '%q' )", htmlenc($grp));
			$grp2grp_id[$grp] = mdb2_single_val($db, "SELECT LAST_INSERT_ID()");
		}
		mdb2_exec($db, "INSERT INTO grp2vak ( grp_id, vak_id ) VALUES ( {$grp2grp_id[$grp]}, {$vak2vak_id[$vak]} )");
		$grp2vak2grp2vak_id[$grp2vak] = mdb2_single_val($db, "SELECT LAST_INSERT_ID()");
	}
	if (!preg_match('/[A-Z]{4}/', $doc)) {
		echo('in deze regel over '.$grp2vak.' is geen geldige docent'."\n");
		continue;
	}
	$achternaam = htmlenc(iconv($input_encoding, "UTF-8", trim($lesgroep[5], " \t\n\r\0\x0B,")));
	$voornaamprefix = htmlenc(iconv($input_encoding, "UTF-8", trim($lesgroep[6])));
	if (!isset($doc2ppl_id[$doc])) {
		echo('docent '.$doc.' is nieuw'."\n");
		mdb2_exec($db, "INSERT INTO ppl ( naam0, naam1, naam2, login, type ) VALUES ( '%q', '%q', '', '%q', 'personeel' )",
			$achternaam, $voornaamprefix, htmlenc(iconv($input_encoding, "UTF-8", trim($doc))));
		$ppl_id = mdb2_single_val($db, "SELECT LAST_INSERT_ID()");
		$doc2ppl_id[$doc] = array($ppl_id, $achternaam, $voornaamprefix, '');
	} else { // deze docent hebben we al, naam controleren....
		if (strcmp($doc2ppl_id[$doc][1], $achternaam) || strcmp($doc2ppl_id[$doc][2], $voornaamprefix) || strcmp($doc2ppl_id[$doc][3], '')) {
			echo("naamswijziging $doc '{$doc2ppl_id[$doc][2]} {$doc2ppl_id[$doc][3]} {$doc2ppl_id[$doc][1]}' -> '$voornaamprefix $achternaam'\n");
			mdb2_exec($db, "UPDATE ppl SET naam0 = '%q', naam1 = '%q', naam2 = '' WHERE login = '%q' AND active = 0",
				$achternaam, $voornaamprefix, $doc);
			$doc2ppl_id[$doc][1] = $achternaam;
			$doc2ppl_id[$doc][2] = $voornaamprefix;
			$doc2ppl_id[$doc][3] = '';
		}
	}
	$ppl_id = $doc2ppl_id[$doc][0];
	$grp2vak_id = $grp2vak2grp2vak_id[$grp2vak];
	$doc2grp2vak[$ppl_id][] = $grp2vak_id;
	$grp2vak2doc[$grp2vak_id][] = $ppl_id;
}

//print_r($doc2grp2vak);
$ppl2grp = array();

foreach ($lg2ll as $link) {
	assert(isset($ll2ppl_id[$link[0]]));
	if (!isset($grp2grp_id[$link[1]])) { // deze groepen zijn we nog niet tegengekomen
		$weird[$link[1]] = 1;
		continue;
	}
	$ppl_id = $ll2ppl_id[$link[0]][0];
	$grp_id = $grp2grp_id[$link[1]];
	if (!isset($ppl2grp[$ppl_id]) || !in_array($grp_id, $ppl2grp[$ppl_id])) {
	       	$ppl2grp[$ppl_id][] = $grp_id;
	       	$grp2ppl[$grp_id][] = $ppl_id;
	}
}

//print_r($weird);

// we doorlopen alle docenten die minstens 1 grp2vak hebben
// en we updaten de links
foreach ($doc2grp2vak as $ppl_id => $grp2vak_ids) {
	$old_grp2vak_ids = mdb2_col($db, "SELECT grp2vak_id FROM doc2grp2vak JOIN grp2vak USING (grp2vak_id) JOIN grp USING (grp_id) WHERE ppl_id = $ppl_id AND grp_type_id = 2 AND schooljaar = '$schooljaar'");
	$to_delete = array_diff($old_grp2vak_ids, $grp2vak_ids);
	$to_add = array_diff($grp2vak_ids, $old_grp2vak_ids);
	foreach ($to_delete as $grp2vak_id) mdb2_exec($db, "DELETE FROM doc2grp2vak WHERE ppl_id = $ppl_id AND grp2vak_id = $grp2vak_id");
	foreach ($to_add as $grp2vak_id) mdb2_exec($db, "INSERT INTO doc2grp2vak ( ppl_id, grp2vak_id ) VALUES ( $ppl_id, $grp2vak_id )");
}

// we doorlopen alle grp2vak en we wissen links met docenten die
// we niet tegen zijn gekomen
foreach ($grp2vak2doc as $grp2vak_id => $ppl_ids) {
	$ppl_id_list = implode(',', $ppl_ids);
	mdb2_exec($db, "DELETE FROM doc2grp2vak WHERE grp2vak_id = $grp2vak_id AND ppl_id NOT IN ( $ppl_id_list )");
}

foreach ($ppl2grp as $ppl_id => $grp_ids) {
	$old_grp_ids = mdb2_col($db, "SELECT grp_id FROM ppl2grp JOIN grp USING (grp_id) WHERE ppl_id = $ppl_id AND grp_type_id = 2 AND schooljaar = '$schooljaar'");
	$to_delete = array_diff($old_grp_ids, $grp_ids);
	$to_add = array_diff($grp_ids, $old_grp_ids);
	foreach ($to_delete as $grp_id) mdb2_exec($db, "DELETE FROM ppl2grp WHERE ppl_id = $ppl_id AND grp_id = $grp_id");
	foreach ($to_add as $grp_id) mdb2_exec($db, "INSERT INTO ppl2grp ( ppl_id, grp_id ) VALUES ( $ppl_id, $grp_id )");
}

// we doorlopen alle grp en we wissen links met leerlingen die
// we niet tegen zijn gekomen
foreach ($grp2ppl as $grp_id => $ppl_ids) {
	$ppl_id_list = implode(',', $ppl_ids);
	mdb2_exec($db, "DELETE FROM ppl2grp WHERE grp_id = $grp_id AND ppl_id NOT IN ( $ppl_id_list )");
	echo($grp_id.': '.$ppl_id_list."\n");
}
?>