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

$db = mdb2_open($dsn);

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

$year_info = $years['1415'];

$year_id = 7; // FIXME

function read_gpu001($filename, &$gpu) {
	global $year_id;
	$fp = fopen_or_fail($filename);
	while (($line = fgetcsv($fp, 0, ',')) !== false) {
		if (count($line) != 9) error_system('we verwachten 9 kolommen in GPU001.TXT, we hebben er nu '.count($line));

		if ($line[1] == 'b' || $line[3] == 'b' || $line[1] == 'o' || $line[3] == 'o' || $line[1] == '') continue;

		if ($line[1] == 'RT') continue;

		if ($line[3] == 'inhalen' || $line[3] == '' || $line[3] == 'stage') continue;

		// coach is mt
		if ($line[3] == 'coach') $line[3] = 'mt';

		if ($year_id != 7) error_system('year specific stuff');

		// we vertalen bha_1 en bte_2 naar bhabte_1 en
		//             bha_2 en bte_1 naar bhabte_2
		if ($line[3] == 'bha_1' || $line[3] == 'bte_2')
			$line[3] = 'bhabte_1';
		if ($line[3] == 'bha_2' || $line[3] == 'bte_1')
			$line[3] = 'bhabte_2';

		// we willen alleen klas 3 en 4
		if ($line[1][0] != '3' && $line[1][0] != 4) continue;

		$id = $line[0];
		if (isset($gpu[$id])) {
			if (!in_array($line[1], $gpu[$id]['stamklassen'])) {
				$gpu[$id]['stamklassen'][] = $line[1];
			}
			if (!in_array($line[2], $gpu[$id]['docenten'])) {
				$gpu[$id]['docenten'][] = $line[2];
			}
			if (!in_array($line[3], $gpu[$id]['vakken'])) {
				$gpu[$id]['vakken'][] = $line[3];
			}
		} else {
			$gpu[$id] = array('stamklassen' => array($line[1]),
				'docenten' => array($line[2]),
				'vakken' => array($line[3]),
				'leerlingen' => array());
		}
	}
}

function read_gpu015($filename, &$gpu) {
	global $year_id;
	$fp = fopen_or_fail($filename) ;
	while (($line = fgetcsv($fp, 0, ',')) !== false) {
		if (count($line) != 14) error_system('we verwachten 14 kolommen in GPU001.TXT, we hebben er nu '.count($line));

		if ($line[2] == 'inhalen' || $line[2] == 'stage') continue;

		//soms is er geen vak ingevuld
		if ($line[2] == '') continue;

		// coach is mt
		if ($line[2] == 'coach') $line[2] = 'mt';

		if ($year_id != 7) error_system('year specific stuff');

		// we vertalen bha_1 en bte_2 naar bhabte_1 en
		//             bha_2 en bte_1 naar bhabte_2
		if ($line[2] == 'bha_1' || $line[2] == 'bte_2')
			$line[2] = 'bhabte_1';
		if ($line[2] == 'bha_2' || $line[2] == 'bte_1')
			$line[2] = 'bhabte_2';

		// we willen alleen klas 3 en 4
		if ($line[4][0] != '3' && $line[4][0] != 4) continue;

		if (!isset($gpu[$line[1]])) {
			echo('niet bestaande id: '.$line[1].' in '.$line[0].' '.$line[2].' '.$line[4]."\n");
			continue;
		}

		if (!in_array($line[2], $gpu[$line[1]]['vakken'])) error_system('vak is niet genoemd in GPU001 maar wel in GPU015');

		$gpu[$line[1]]['leerlingen'][] = $line[0];
	}
}

function read_gpu010($filename, &$stamklassen) {
	$fp = fopen_or_fail($filename) ;
	while (($line = fgetcsv($fp, 0, ',')) !== false) {
		if (count($line) != 15) error_system('we verwachten 15 kolommen in GPU001.TXT, we hebben er nu '.count($line));
		if (!isset($stamklassen[$line[9]])) $stamklassen[$line[9]] = array($line[0]);
		else $stamklassen[$line[9]][] = $line[0];
		//echo("leerlingnummer {$line[0]} stamklas {$line[9]}\n");
	}
}
	
$gpu = array();
$stamklassen = array();
$clusters = array(); // checkt uniciteit van clusternamen
$misleidende_clusters = array();
$tochnietklassikaal = array();
$nieuwe_clusters = array();

read_gpu001('GPU001.TXT', $gpu);
read_gpu015('GPU015.TXT', $gpu);
read_gpu010('GPU010.TXT', $stamklassen);

foreach ($gpu as &$row) {
	if (count($row['vakken']) != 1) {
		print_r($row['vakken']);
		error_system('lesgroep met meerdere vakken');
	}
	if (count($row['stamklassen']) == 1) {
		// mmmm, een les in stamklasverband?
		// we zulen zien
		if (count($stamklassen[$row['stamklassen'][0]]) == count($row['leerlingen'])) $row['is_stamklas'] = 1;
		else $row['is_stamklas'] = 0;
		//echo($row['is_stamklas'].':'.implode(',', $row['stamklassen']).'/'.implode(',', $row['docenten']).'/'.implode(',', $row['vakken']).':'.count($row['leerlingen'])."\n"); //*/implode(',', $row['leerlingen'])."\n");
	} else $row['is_stamklas'] = 0;
}

$clusterletters = 'abcdefghijklmnopqrstuvwxyz';

foreach ($gpu as $key => &$row) {
	if ($row['is_stamklas']) continue;
	//echo($key.':'.implode(',', $row['vakken'])."\n");
	$lesjaar = $row['stamklassen'][0][0];
	foreach ($row['stamklassen'] as $stamklas) {
		if ($stamklas[0] != $lesjaar) error_system("gemengd lesjaar");

	}

	if (strpos($row['vakken'][0], '_') !== FALSE) {
		// dit cluster ziet eruit als een cluster
		$clusternaam = $lesjaar.$row['vakken'][0];
		$row['vakken'][0] = strstr($row['vakken'][0], '_', TRUE);
		if (isset($clusters[$clusternaam])) {
			$misleidende_clusters[$clusternaam] = 1;
			$clusternaam .= $clusterletters[$clusters[$clusternaam]++];
			$nieuwe_clusters[] = $key;
		} else $clusters[$clusternaam] = 1;

	} else {
		// dit is een cryptocluster :(
		$tochnietklassikaal[$row['stamklassen'][0].'/'.$row['vakken'][0]] = 1;
		$clusternaam = $lesjaar.$row['vakken'][0].'_';
		if (isset($clusters[$clusternaam])) {
			$clusternaam .= $clusterletters[$clusters[$clusternaam]++];
		} else {$clusters[$clusternaam] = 1;
			$clusternaam .= 'a';
		}
		$nieuwe_clusters[] = $key;
	}
	$row['clusternaam'] = $clusternaam;
}

// pas besmette clusters aan
foreach ($gpu as $key => &$row) {
	if ($row['is_stamklas']) continue;
	if (isset($misleidende_clusters[$row['clusternaam']])) {
		$row['clusternaam'] .= 'a';
		$nieuwe_clusters[] = $key;
	}
}


// $tochnietklassikaal
//   - verwijder uit grp2subj2year (year_id = 7)
//   - verwijder uit ppl2grp2subj  (timestamp dit schooljaar)
// 
// $misleidende_clusters
//   - verwijder uit grp2subj2year (year_id = 7)
//   - verwijder uit ppl2grp2subj  (timestamp dit schooljaar)
//   - verwijder leerlingen (timestamp dit schooljaar)
//
// $nieuwe clusters
//   - voeg toe en link alles

$affected = -1;

echo("behandelen: tochnietklassikaal\n");
print_r($tochnietklassikaal);
foreach ($tochnietklassikaal as $klasvak => $tmp) {
	$kv = explode('/', $klasvak);
	echo("--------\n");
	print_r($kv);
	$grp_id = mdb2_single_val($db, "SELECT grp_id FROM grp WHERE grp_name = '%q'", $kv[0]);
	if (!$grp_id) error_system("groep {$kv[0]} niet gevonden");
	echo("grp_id=$grp_id\n");
	$subj_id = mdb2_single_val($db, "SELECT subj_id FROM subj WHERE subj_abbrev = '%q'", $kv[1]);
	if (!$subj_id) error_system("subj {$kv[1]} niet gevonden");
	echo("subj_id=$subj_id\n");
	$grp2subj_id = mdb2_single_val($db, "SELECT grp2subj_id FROM grp2subj WHERE grp_id = $grp_id AND subj_id = $subj_id");
	if (!$grp2subj_id) {
		echo("grp2subj niet gevonden voor {$kv[0]} {$kv[1]}\n");
		continue;
	}
	echo("grp2subj_id=$subj_id\n");
	//$affected = mdb2_exec($db, "DELETE FROM year2grp2subj WHERE year_id = $year_id AND grp2subj_id = $grp2subj_id");
	echo("deleted year2grp2subj $affected\n");
	//$affected = mdb2_exec($db, "DELETE FROM ppl2grp2subj WHERE grp2subj_id = $grp2subj_id AND ppl_enter = {$year_info[0]} AND ppl_exit = {$year_info[1]}");
	echo("deleted ppl2grp2subj $affected\n");
}

echo("behandelen: misleidende clusters\n");
print_r($misleidende_clusters);
foreach ($misleidende_clusters as $grp_name => $tmp) {
	echo("-------\n");
	echo("$grp_name\n");
	$grp_id = mdb2_single_val($db, "SELECT grp_id FROM grp WHERE grp_name = '%q'", $grp_name);
	if (!$grp_id) error_system("groep $grp_name niet gevonden");
	$subj_abbrev = strstr(substr($grp_name, 1), '_', TRUE);
	echo("grp_id=$grp_id\n");
	$subj_id = mdb2_single_val($db, "SELECT subj_id FROM subj WHERE subj_abbrev = '%q'", $subj_abbrev);
	if (!$subj_id) error_system("subj $subj_abbrev niet gevonden");
	echo("subj_id=$subj_id\n");
	$grp2subj_id = mdb2_single_val($db, "SELECT grp2subj_id FROM grp2subj WHERE grp_id = $grp_id AND subj_id = $subj_id");
	if (!$grp2subj_id) {
		error_system("grp2subj niet gevonden voor $grp_name\n");
	}
	echo("grp2subj_id=$subj_id\n");
	//$affected = mdb2_exec($db, "DELETE FROM year2grp2subj WHERE year_id = $year_id AND grp2subj_id = $grp2subj_id");
	echo("deleted year2grp2subj $affected\n");
	//$affected = mdb2_exec($db, "DELETE FROM ppl2grp2subj WHERE grp2subj_id = $grp2subj_id AND ppl_enter = {$year_info[0]} AND ppl_exit = {$year_info[1]}");
	echo("deleted ppl2grp2subj $affected\n");
	//$affected = mdb2_exec($db, "DELETE FROM ppl2grp WHERE grp_id = $grp_id AND ppl_enter = {$year_info[0]} AND ppl_exit = {$year_info[1]}");
	echo("deleted ppl2grp $affected\n");
}

echo("behandelen: nieuwe clusters\n");
print_r($nieuwe_clusters);
/*
foreach ($nieuwe_clusters as $cluster_id) {
	echo("--------\n");
	$cluster = $gpu[$cluster_id];
	$grp_name = $cluster['clusternaam'];
	$subj_abbrev = $cluster['vakken'][0];
	echo("$cluster_id $subj_abbrev $grp_name ".implode(',', $cluster['docenten'])."\n");

	$grp_id = mdb2_single_val($db, "SELECT grp_id FROM grp WHERE grp_name = '%q'", $grp_name);
	if (!$grp_id) {
		mdb2_exec($db, "INSERT INTO grp ( grp_name, grp_home ) VALUES ( '%q', 0 )", $grp_name);
		$grp_id = mdb2_single_val($db, $last_insert_id);
	} else error_system("deze groep kan niet bestaan $grp_name");

	$subj_id = mdb2_single_val($db, "SELECT subj_id FROM subj  WHERE subj_abbrev = '%q'", $subj_abbrev);
	if (!$subj_id) error_system("val $subj_abbrev zou al moeten bestaan");
      // 	{
//		mdb2_exec($db, "INSERT INTO subj ( subj_abbrev ) VALUES ( '%q' )", $subj_abbrev);
//		$subj_id = mdb2_single_val($db, $last_insert_id);
//	}

	$grp2subj_id = mdb2_single_val($db, "SELECT grp2subj_id FROM grp2subj WHERE subj_id = $subj_id AND grp_id = $grp_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);
	} else error_system("deze grp2subj_id kan niet bestaan $grp2subj_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) {
		mdb2_exec($db, "INSERT INTO year2grp2subj ( year_id, grp2subj_id ) VALUES ( $year_id, $grp2subj_id )");
		$year2grp2subj_id = mdb2_single_val($db, $last_insert_id);
	} else error_system("deze year2grp2subj_id kan niet bestaan $year2grp2subj_id");
	
	foreach ($cluster['docenten'] as $afk) {
		$ppl_id = mdb2_single_val($db, "SELECT ppl_id FROM ppl WHERE ppl_login = '%q'", $afk);
		if (!$ppl_id) fatal_error("docent $afk onbekend");
	//	{ // docent nog onbekend
	//		mdb2_exec($db, "INSERT INTO ppl ( ppl_login, ppl_surname, ppl_forename ) VALUES ( '%q', '%q', '%q' )", $afk, '<i>'.$afk.'</i>', '<i>docent</i>');
	//		$ppl_id = mdb2_single_val($db, $last_insert_id);
	//	}

		$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 error_systen("docent was al gelinkt?");
	}

	foreach ($cluster['leerlingen'] as $llnr) {
		$ppl_id = mdb2_single_val($db, "SELECT ppl_id FROM ppl WHERE ppl_login = '%q'", $llnr);
		if (!$ppl_id) error_system("leerling $llnr niet gevonden");

		$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]} )");
		} else error("leerling was al gelinkt?");

	}
}
*/
?>