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

$db = mdb2_open($dsn);

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

$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) ),
	'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) )
);

// dit allebei aanpassen bij het inlezen van een nieuw schooljaar!!!!
$schooljaar = '1617';
$year_id = 9; // 2015/2016
$year = $years[$schooljaar];

mdb2_exec($db, "DELETE FROM year2grp2subj WHERE year_id = $year_id");
mdb2_exec($db, "DELETE FROM ppl2grp WHERE ppl_enter >= {$year[0]} AND ppl_exit <= {$year[1]}");
mdb2_exec($db, "DELETE FROM ppl2grp2subj WHERE ppl_enter >= {$year[0]} AND ppl_exit <= {$year[1]}");

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

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

$stamz = 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;
}


// meh, het format is gewijzigd, stamklas staat niet meer op plaats 19...
// systeem aangepast, zodat het zelf zoekt naar indices van gegevens
foreach ($index as $section => $idx) {
	if (!preg_match('/^Leerling\./', $section)) continue;
	
	$legenda = explode("\t", $lines[$idx++]);

	$login_idx = find_idx('ID', $legenda, $section);
	$naam0_idx = find_idx('LASTNAME', $legenda, $section);
	$naam1_idx = find_idx('FIRSTNAME', $legenda, $section);
	$naam2_idx = find_idx('BETWEENNAME', $legenda, $section);
	$stamklas_idx = find_idx('BASICCLASS', $legenda, $section);

	while (($line = $lines[$idx++]) != '########') {
		$fields = explode("\t", $line);

		$login = htmlenc(iconv($input_encoding, "UTF-8", trim($fields[$login_idx])));
		$naam0 = htmlenc(iconv($input_encoding, "UTF-8", trim($fields[$naam0_idx])));
		$naam1 = htmlenc(iconv($input_encoding, "UTF-8", trim($fields[$naam1_idx])));
		$naam2 = htmlenc(iconv($input_encoding, "UTF-8", trim($fields[$naam2_idx])));

		$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[$stamklas_idx])) {
	//		echo("BHC stamklas gevonden voor $login, negeer -> {$fields[$stamklas_idx]}\n");
			continue;
		}
		//echo("leerling + stamklas geveonden $naam1 $naam2 $naam0 $login {$fields[$stamklas_idx]}\n");

		$stamz[$fields[$stamklas_idx]] = 1;
		
		// stamklas
		$grp_id = mdb2_single_val($db, "SELECT grp_id FROM grp WHERE grp_name = '%q'", $fields[$stamklas_idx]);
		if (!$grp_id) {
			mdb2_exec($db, "INSERT INTO grp ( grp_name, grp_home ) VALUES ( '%q', 1 )", $fields[$stamklas_idx]);
			$grp_id = mdb2_single_val($db, $last_insert_id);
		}

		if (!($year_info = $years[$schooljaar])) fatal_error("onbekend schooljaar");

		$ppl2grp_id = mdb2_single_val($db, "SELECT ppl2grp_id FROM ppl2grp WHERE ppl_id = $ppl_id AND grp_id = $grp_id AND ppl_enter = {$year_info[0]} AND ppl_exit = {$year_info[1]}");
		if (!$ppl2grp_id) {
			mdb2_exec($db, "INSERT INTO ppl2grp ( ppl_id, grp_id, ppl_enter, ppl_exit ) VALUES ( $ppl_id, $grp_id, {$year_info[0]}, {$year_info[1]} )");
		}
		//echo("$naam1 $naam0 ($login) ppl_id=$ppl_id, in klas {$fields[$stamklas_idx]} grp_id=$grp_id\n");

	}
}

// 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;
	$legenda = explode("\t", $lines[$idx++]);
	$id_idx = find_idx('ID', $legenda, $section);
	$set_idx = find_idx('SET', $legenda, $section);
	while (($line = $lines[$idx++]) != '########') {
		$fields = explode("\t", $line);
		if ($fields[$id_idx] == '#allen') continue;
		if (isset($stamz[$fields[$id_idx]])) continue; // stamklas, hebben we al
		$grp_naam = $matches[1].'.'.$fields[$id_idx];
//		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[$schooljaar])) fatal_error("onbekend schooljaar");

		$lln = explode(',', $fields[$set_idx]);
		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;
			}

			$ppl2grp_id = mdb2_single_val($db, "SELECT ppl2grp_id FROM ppl2grp WHERE ppl_id = $ppl_id AND grp_id = $grp_id AND ppl_enter = {$year_info[0]} AND ppl_exit = {$year_info[1]}");
			if (!$ppl2grp_id) {
				mdb2_exec($db, "INSERT INTO ppl2grp ( ppl_id, grp_id, ppl_enter, ppl_exit ) VALUES ( $ppl_id, $grp_id, {$year_info[0]}, {$year_info[1]} )");
			}
		}
	}
}

$idx = $index['Docent'];
$legenda = explode("\t", $lines[$idx++]);
$login_idx = find_idx('ID', $legenda, 'Docent');
$naam0_idx = find_idx('Achternaam', $legenda, 'Docent');
$naam1_idx = find_idx('Voornaam', $legenda, 'Docent');
$naam2_idx = find_idx('Tussenvoegsel', $legenda, 'Docent');
//echo("login_idx=$login_idx\n");
//echo("naam0_idx=$naam0_idx\n");
//echo("naam1_idx=$naam1_idx\n");
//echo("naam2_idx=$naam2_idx\n");
//$example = explode("\t", $lines[$idx]);
//print_r($example);
//echo("{$example[$naam0_idx]} {$example[$naam1_idx]} {$example[$naam2_idx]} {$example[$login_idx]}\n");
//exit;
while (($line = $lines[$idx++]) != '########') {
	$fields = explode("\t", $line);

	$login = htmlenc(iconv($input_encoding, "UTF-8", trim($fields[$login_idx])));
	$naam0 = htmlenc(iconv($input_encoding, "UTF-8", trim($fields[$naam0_idx])));
	$naam1 = htmlenc(iconv($input_encoding, "UTF-8", trim($fields[$naam1_idx])));
	$naam2 = htmlenc(iconv($input_encoding, "UTF-8", trim($fields[$naam2_idx])));

	$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'];
$legenda = explode("\t", $lines[$idx++]);
$grp_idx = find_idx('Grp', $legenda, 'Les');
$doc_idx = find_idx('Doc', $legenda, 'Les');
$vak_idx = find_idx('Vak', $legenda, 'Les');

// geef (indien cluster) het vak trug dat in de clusternaam staat
// anders false
function is_cluster($lesgroep) {
	global $stamz;
	if (preg_match('/\.(.*)$/', $lesgroep, $matches)) {
		if (isset($stamz[$matches[1]])) return false;
		return substr($matches[1], 0, -1);
	}
	return false;
}

function get_stamklas($lesgroep) {
	global $stamz;
	if (preg_match('/\.(.*)$/', $lesgroep, $matches)) {
		if (isset($stamz[$matches[1]])) return $matches[1];
		return false;
	}
	return $lesgroep;
}

while (($line = $lines[$idx++]) != '########') {
	$fields = explode("\t", $line);

	if (!isset($fields[$grp_idx]) || $fields[$grp_idx] == '') continue;
	if (!isset($fields[$doc_idx]) || $fields[$doc_idx] == '') continue;
	if (!isset($fields[$vak_idx]) || $fields[$vak_idx] == '') continue;
	$lesgroepen = explode(',', $fields[$grp_idx]);
	$docenten = explode(',', $fields[$doc_idx]);
	$vakken = explode(',', $fields[$vak_idx]);

	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[$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;
	} else if (count($vakken) == count($lesgroepen) && count($docenten) == 1) {
		$ppl_id = mdb2_single_val($db, "SELECT ppl_id FROM ppl WHERE ppl_login = '%q'", $docenten[0]);
		if (!$ppl_id) error_system("docent niet gevonden");

		echo("mildly interesting\n");
		echo("vakken:\n");
		print_r($vakken);
		echo("docenten:\n");
		print_r($docenten);

		$stamklassen = array();

		foreach ($lesgroepen as $lesgroep) {
			$vak = is_cluster($lesgroep);
			if (!$vak) {
				$stamklassen[] = get_stamklas($lesgroep);
				continue;
			}

			if (($idx_vak = array_search($vak, $vakken)) === false) 
				error_system("vak horende bij lesgroep $lesgroep niet gevonden in vakken array");
	
			unset($vakken[$idx_vak]);

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

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

		}
		//echo("done:\n");
		//print_r($stamklassen);
		//print_r($vakken);
		if (count($stamklassen) != count($vakken)) error_system("impossible");
		if (count($stamklassen) > 1) {
			echo('FAAL');
		} else if (count($stamklassen) == 1) {
			// stamklas over
			$lesgroep = $stamklassen[0];
			$vak = array_shift($vakken);
			echo("stamklas=$lesgroep vak=$vak\n");

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

			$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[$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]} )");
			}
		}
		
	} else {
		echo("faal\n"); 
		echo("lesgroepen:\n");
		print_r($lesgroepen);
		echo("docenten:\n");
		print_r($docenten);
		echo("vakken:\n");
		print_r($vakken);
	}
}


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