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

function read_all_sections(&$sections, $filename) {
	$section_name = 'PREAMBULE';
	if (!($fp = fopen($filename, 'r'))) die('unable to open '.$filename."\n");

	$sections = array();
	$sections[$section_name] = array();

	while (!feof($fp)) {
		$line = fgets($fp);
		if ($line === NULL) die('read error at line '.$no.' of '.$filename."\n");

		$tmp = explode("\t", trim($line));
		if ($tmp[0]== '########') {
			if (isset($sections[$tmp[1]])) die('multiple sections of same name?'."\n");

			$section_name = $tmp[1];
			$sections[$section_name] = array();
		} else {
			$sections[$section_name][] = $tmp;
		}
	}

	if (!fclose($fp)) die('error at fclose of '.$filename."\n");
}

read_all_sections($sections, $argv[1]);

foreach ($sections as $name => $data) {
	echo($name.' ('.count($data).")\n");
}

$db = mdb2_open($dsn);

$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");
$ppl2categorie = array();

echo('fetched stuff from DB'."\n");

// 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

// eerste pass leerlingen, we hebben de leerlingnummers en de ppl_id's nodig om
// de stamklassen te kunnen parsen
foreach ($sections['LEERLINGEN'] as $leerling) {
	$naam0 = htmlenc(iconv($input_encoding, "UTF-8", trim($leerling[3])));
	$naam1 = htmlenc(iconv($input_encoding, "UTF-8", trim($leerling[1])));
	$naam2 = htmlenc(iconv($input_encoding, "UTF-8", trim($leerling[2])));
	$login = htmlenc(iconv($input_encoding, "UTF-8", trim($leerling[0])));
	//print_r($leerling);
	//echo($naam2."\n");

	//exit;
	//continue;

	// 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 ($sections['LEERLINGVERZAMELINGEN'] as $leerlingverzameling) {
	list($key, $grp) = each($leerlingverzameling);
	if (isset($grp2grp_id[$grp])) continue;

	mdb2_exec($db, "INSERT INTO grp ( grp_type_id, schooljaar, naam, stamklas ) VALUES ( 2, '$schooljaar', '%q', 1 )", htmlenc($grp));
	$grp2grp_id[$grp] = mdb2_single_val($db, "SELECT LAST_INSERT_ID()");

	while (list($key, $leerling) = each($leerlingverzameling)) {
		if (!isset($ll2ppl_id[$leerling])) die("leerling $leerling is onbekend?!?!?");
		mdb2_exec($db, "INSERT INTO ppl2grp ( ppl_id, grp_id ) VALUES ( {$ll2ppl_id[$leerling][0]}, {$grp2grp_id[$grp]} )");
	}
}

foreach ($sections['LEERLINGEN'] as $leerling) {
	list($key, $login) = each($leerling);

	$ppl_id = $ll2ppl_id[$login][0];

	// skip naam, die hebben we al
	each($leerling);
	each($leerling);
	each($leerling);

	if ($leerling[4] == '0') continue; // deze leerling heeft geen rooster

	while (list($key, $entry) = each($leerling)) {
		$les = explode(',', $entry);
		$lesgroep = $les[1];
		$vak = $les[4];
		$docent = $les[2];

		if (strpos($lesgroep, '/') !== false) {
			echo('lesgroep te moeilijk '.$lesgroep.' ll '.$login."\n");
			continue;
		}
		if ($lesgroep == '') continue;

		if (preg_match('/^(.*?)\.(.*)$/', $lesgroep, $match)) {
			if (isset($ppl2categorie[$ppl_id]) && $ppl2categorie[$ppl_id] != $match[1]) die('impossible '.$match[1].' '.$ppl2categorie[$ppl_id]);
			$ppl2categorie[$ppl_id] = $match[1];
			//echo("lblabla {$match[2]}\n");
			if (isset($grp2grp_id[$match[2]])) $grp_id = $grp2grp_id[$match[2]]; // stamklas, hebben we al
			else {
				if (isset($grp2grp_id[$lesgroep])) $grp_id = $grp2grp_id[$lesgroep];
				else {
					mdb2_exec($db, "INSERT INTO grp ( grp_type_id, schooljaar, naam, stamklas ) VALUES ( 2, '$schooljaar', '%q', 0 )", htmlenc($lesgroep));
					$grp_id = $grp2grp_id[$lesgroep] = mdb2_single_val($db, "SELECT LAST_INSERT_ID()");

				}
				mdb2_exec($db, "INSERT IGNORE INTO ppl2grp ( ppl_id, grp_id ) VALUES ( $ppl_id, $grp_id )");
			}

		} else {
			if (!isset($grp2grp_id[$lesgroep])) die("impossible");
			$grp_id = $grp2grp_id[$lesgroep];
		}
		
		//echo("grp_id=$grp_id\n");

		if (strpos($vak, '/') !== false) {
			echo('vak te moeilijk '.$vak.' ll '.$login."\n");
			continue;
		}
		if ($vak == '') continue;

		if (!isset($vak2vak_id[$vak])) {
			//echo("nieuw vak $vak\n");
			mdb2_exec($db, "INSERT INTO vak ( afkorting ) VALUES ( '%q' )", $vak);
			$vak_id = $vak2vak_id[$vak] = mdb2_single_val($db, "SELECT LAST_INSERT_ID()");
		} else $vak_id = $vak2vak_id[$vak];

		if (!isset($grp2vak2grp2vak_id[$lesgroep.'/'.$vak])) {
			//echo("nieuwe grp2vak !\n");
			//echo('lesgroep='.$lesgroep.' vak='.$vak."\n");
			//print_r($grp2vak2grp2vak_id);
			mdb2_exec($db, "INSERT IGNORE INTO grp2vak ( grp_id, vak_id ) VALUES ( $grp_id, $vak_id )");
			$grp2vak_id = $grp2vak2grp2vak_id[$lesgroep.'/'.$vak] = mdb2_single_val($db, "SELECT LAST_INSERT_ID()");
		} else $grp2vak_id = $grp2vak2grp2vak_id[$lesgroep.'/'.$vak];

		if (strpos($docent, '/') !== false) {
			echo('docent te moeilijk '.$docent.' ll '.$login."\n");
			continue;
		}
		if ($docent == '') continue;
		
		if (!isset($doc2ppl_id[$docent])) {
			//echo("nieuwe docent $docent\n");
			mdb2_exec($db, "INSERT INTO ppl ( naam0, naam1, naam2, login, type ) VALUES ( '<i>%q</i>', '<i>nieuwe docent</i>', '', '%q', 'personeel' )",
					$docent, $docent);
			$doc2ppl_id[$docent] = array(mdb2_single_val($db, "SELECT LAST_INSERT_ID()"));
			$doc_id = $doc2ppl_id[$docent][0];
		} else $doc_id = $doc2ppl_id[$docent][0];

		mdb2_exec($db, "INSERT IGNORE doc2grp2vak ( ppl_id, grp2vak_id ) VALUES ( $doc_id, $grp2vak_id )");
		//print_r($entry);
	}
//		exit;

}
?>