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

$db = mdb2_open($dsn);

$fp = fopen_or_fail($_POST['file']);

$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

while (($line = fgetcsv($fp, 0, ',')) !== false) {
	$debug = 0;
	if (!$line) continue;

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

	// geen echte klassen
	if ($line[1] == 'RT') continue;
	// in de zin van herkansingen zijn dit geen 'echte' vakken
	if ($line[3] == 'inhalen' || $line[3] == 'coach' || $line[3] == '' || $line[3] == 'stage') continue;

	if (strpos($line[3], '_') !== FALSE) {
		$grp_home = 0;
		$grp_name = substr($line[1], 0, 1).$line[3];
		$subj_abbrev = strstr($line[3], '_', TRUE);
	/*} if (preg_match('/^([a-zA-Z]+)(\d+)$/', $line[3], $matches)) {
		$grp_home = 0;
		$grp_name = substr($line[1], 0, 1).$matches[1].'_'.$matches[2];
		echo("gevonden $grp_name");
	 */} else {
		$grp_name = $line[1];
		$grp_home = 1;
		if (strpos($line[3], ' ') !== FALSE) $subj_abbrev = strstr($line[3], ' ', TRUE);
		else $subj_abbrev = $line[3];
	}

	//echo("grp_home $grp_home, grp_name $grp_name docent {$line[2]} vak {$line[3]} $subj_abbrev\n");
	//continue;

	$grp_id = mdb2_single_val($db, "SELECT grp_id FROM grp WHERE grp_name = '%q'", $grp_name);
	if (!$grp_id) {
		//if ($grp_home) error_system("wtf!, stamklas bestaat niet $grp_name");
		mdb2_exec($db, "INSERT INTO grp ( grp_name, grp_home ) VALUES ( '%q', $grp_home )", $grp_name);
		$grp_id = mdb2_single_val($db, $last_insert_id);
	}

	//if ($grp_id == 119) $debug = 1;

	$subj_id = mdb2_single_val($db, "SELECT subj_id FROM subj  WHERE subj_abbrev = '%q'", $subj_abbrev);
	if (!$subj_id) {
		mdb2_exec($db, "INSERT INTO subj ( subj_abbrev ) VALUES ( '%q' )", $subj_abbrev);
		$subj_id = mdb2_single_val($db, $last_insert_id);
	}

	//if ($debug) echo("grp_id=$grp_id subj_id=$subj_id\n");
	$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);
	}

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

	if ($line[2] == '?') continue; // geen docent
	if ($line[2] == 'STAGE') continue; // geen docent

	$ppl_id = mdb2_single_val($db, "SELECT ppl_id FROM ppl WHERE ppl_login = '%q'", $line[2]);
	if (!$ppl_id) { // docent nog onbekend
		mdb2_exec($db, "INSERT INTO ppl ( ppl_login, ppl_surname, ppl_forename ) VALUES ( '%q', '%q', '%q' )", $line[2], '<i>'.$line[2].'</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]} )");
		$ppl2grp2subj_id = mdb2_single_val($db, $last_insert_id);
	}
	echo("grp_id=$grp_id subj_id=$subj_id grp2subj_id=$grp2subj_id year2grp2subj_id=$year2grp2subj_id ppl_id=$ppl_id ppl2grp2subj_id=$ppl2grp2subj_id grp_name=$grp_name\n");
}
exit;
header('Location: index.php');
?>