#!/usr/bin/php
<?
function apache_note() {
}

include('config.php');
include('phplib/phplib.php');
include('system.php');
include('db.php');

if (count($argv) != 2) die("een argument nodig\n");

if (!preg_match('/^Vakkenmatrix (\d\d)-(\d\d) (HAVO 45|VWO 456) .xlsx.csv$/', $argv[1], $matches)) die("filename heeft verkeerde format\n");

//print_r($matches);

$year = '20'.$matches[1].'/20'.$matches[2];

if ($matches[3] == 'HAVO 45') {
	$soort = 'HAVO';
	$short_soort = 'H';
} else if ($matches[3] == 'VWO 456') {
	$soort = 'VWO';
	$short_soort = 'V';
} else die("Impossible!\n");

$year_id = db_get_id('year_id', $kldv.'.years', 'year_long', $year);
$year_header = $matches[1].'-'.$matches[2];
$year_header2 = $matches[1].$matches[2];
//echo("year=$year\n");
//echo("year_id=$year_id\n");

if (!($fp = fopen($argv[1], 'r'))) die("unable to open {$argv[1]}\n");

// eerste twee regels zijn niet belangrijk

// eerste regel is leeg
if (fgets($fp) !== "\n") die("eerste regel is niet leeg\n");

$legenda1 = fgetcsv($fp);
$legenda2 = fgetcsv($fp);
//print_r($legenda1);
//print_r($legenda2);

$profielvakken = array();
$keuzevakken = array();
$leerlingnummer = NULL;
$jaaropl = NULL;
$opleiding = NULL;
//$leerlingnummer_gevonden = 0;

$vak = 'niks';
foreach ($legenda1 as $idx => $l1) {
	//echo("$idx {$legenda1[$idx]} {$legenda2[$idx]}\n");
	if ($legenda1[$idx] == 'Profielvak' && $vak == 'niks') $vak = 'profielvak';
	else if ($legenda1[$idx] == 'Keuzevak' && $vak == 'profielvak') $vak = 'keuzevak';
	else if ($legenda1[$idx] == 'Beroepsgericht vak' && $vak == 'keuzevak') $vak = 'keuzevak';
	else if ($legenda1[$idx] == 'Aantal vakken' && $vak == 'keuzevak') $vak = 'aantallen';
	else if (($legenda1[$idx] == $year_header || $legenda1[$idx] == $year_header2) && $vak = 'niks') $leerlingnummer = $idx;
	else if ($legenda1[$idx] == 'opl' && $vak = 'niks') $opleiding = $idx;
	else if ($legenda1[$idx] == 'jr' && $vak = 'niks') $jaaropl = $idx;
	//echo("vak=$vak\n");

	if ($vak == 'profielvak') $profielvakken[$legenda2[$idx]] = $idx;
	else if ($vak == 'keuzevak') $keuzevakken[$legenda2[$idx]] = $idx;
	/*
	 * e
	if ($idx == 0 && $legenda1[$idx] == '' && $legenda2[$idx] == 'Leerlingcode')
		$leerlingnummer_gevonden = 1;

	if ($legenda1[$idx] == 'Vakken') {
		$legenda[$idx] = array( 'vak', $legenda2[$idx]);
		//echo("vak ${legenda2[$idx]}\n");
		$vakken[$legenda2[$idx]] = $idx;
	} else if ($legenda1[$idx] == 'Profielen') {
		$legenda[$idx] = array( 'profiel', $legenda2[$idx]);
		$paketten[$legenda2[$idx]] = $idx;
		//echo("profiel ${legenda2[$idx]}\n");
	}
	 */
}

if ($vak != 'aantallen') die("onverwachte structuur in input, stuck at: $vak\n");
if ($leerlingnummer == NULL || $opleiding == NULL || $jaaropl == NULL) die("onverwachte structuur in input\n");

//echo("leerlingnummer=$leerlingnummer\n");
//echo("opleiding=$opleiding\n");
//echo("jaaropl=$jaaropl\n");
//print_r($profielvakken);
//print_r($keuzevakken);

while (($row = fgetcsv($fp))) {
	$vakken = array();
	$vakken_simple = array();
	$ll = $row[$leerlingnummer];
	if (preg_match('/^(\w+)-(\w+)$/', $row[$opleiding], $somprofiel)) {
		if ($soort != $somprofiel[1] && ($soort != 'VWO' || ($somprofiel[1] != 'ATH' && $somprofiel[1] != 'GYM')))
		       	die("ongeldige data in kolom opl {$row[$opleiding]}, $soort, {$somprofiel[1]}\n");
	} else die("ongeldig format in kolom opl {$row[$opleiding]}\n");
	
	if ($soort == $somprofiel[1]) $short = $short_soort;
	else if ($somprofiel[1] == 'ATH') $short = 'A';
	else if ($somprofiel[1] == 'GYM') $short = 'G';
	else die("impossible\n");
	//print_r($somprofiel);
	//echo($ll."\n");
	//echo($short."\n");
	//echo($year."\n");
	//echo($somprofiel[2]."\n");
	$somprofiel_id = db_get_id("somprofiel_id", "somprofielen", "somprofiel", $somprofiel[2]);
	$jaar = $row[$jaaropl];
	//echo("jaar=$jaar\n");
	if ($jaar != 4 && $jaar != 5 && !($soort == 'VWO' && $jaar == 6)) die("ongeldige data\n");
	//echo("somprofiel_id=$somprofiel_id\n");
	$niveau = $jaar.$short;
	//echo("niveau=$niveau\n");
	$niveau_id = db_get_id('niveau_id', 'niveaus', 'niveau', $niveau);
	//echo("niveau_id=$niveau_id\n");
	foreach ($profielvakken as $vak => $idx) {
		if ($vak == 'RT3F') continue;
		if ($vak == 're') continue;
		if ($vak == 'kualg') continue;
		if ($row[$idx] == 'x') {
			$vakken[] = 'P-'.$vak;
			$vakken_simple[] = $vak;
		}
	}
	foreach ($keuzevakken as $vak => $idx) {
		if ($vak == 'RT3F') continue;
		if ($vak == 're') continue;
		if ($vak == 'kualg') continue;
		if ($row[$idx] == 'x') {
			$vakken[] = 'Z-'.$vak;
			$vakken_simple[] = $vak;
		}
	}
	natcasesort($vakken);
	$vakken_simple_ordered = array_unique($vakken_simple);
	natcasesort($vakken_simple_ordered);
	natcasesort($vakken_simple);
	$pakket_simple = implode(',', $vakken_simple_ordered);
	$pakket = implode(',', $vakken);
	//echo($pakket."\n");
	//echo($pakket_simple."\n");
	//$llsjn_id = get_llsjn_id($row[0], $sj, $n);
	//echo($row[0].' '.$sj.' '.$n.' ('.$llsjn_id.')'."\n");
	/*
	foreach ($vakken as $vak => $idx) {
		if ($row[$idx]) {
			$subj_id = get_subj_id($vak, $row[$idx]);
			echo("vak $vak \"{$row[$idx]}\"\n");
			db_exec("INSERT INTO llsjn2subj ( llsjn_id, subj_id ) VALUES ( ?, ? )", $llsjn_id, $subj_id);
		}
	}
	foreach ($paketten as $pakket => $idx) {
		if ($row[$idx]) {
			$prof_id = get_prof_id($pakket, $row[$idx]);
			echo("pakket $pakket \"{$row[$idx]}\"\n");
			db_exec("INSERT INTO llsjn2prof ( llsjn_id, prof_id ) VALUES ( ?, ? )", $llsjn_id, $prof_id);
		}
	}
	 */
	$pakket_id = db_get_id('pakket_id', 'paketten', 'pakket', $pakket, 'pakket_simple', $pakket_simple);
	$lj_id = db_get_id('lj_id', 'lj', 'lj', $jaar);
	$opl_id = db_get_id('opl_id', 'opl', 'opl', $short);
	$llsjnpp_id = db_get_id('llsjnpp_id', 'llsjnpp', 'll', $ll, 'year_id', $year_id, 'niveau_id', $niveau_id, 'somprofiel_id', $somprofiel_id, 'pakket_id', $pakket_id, 'lj_id', $lj_id, 'opl_id', $opl_id);

	$count = db_single_field("SELECT COUNT(*) FROM pakket2subj WHERE pakket_id = ?", $pakket_id);
	if ($count) continue;

	// pakket niet gevonden
	
	foreach ($vakken as $vak_and_type) {
		$info = explode('-', $vak_and_type);
		//print_r($info);
		$subj_id = db_get_id('subj_id', $kldv.'.subj', 'subj_abbrev', $info[1]);
		db_exec("INSERT INTO pakket2subj ( pakket_id, subj_id, type ) VALUES ( ?, ?, ? )", $pakket_id, $subj_id, $info[0]);
	}
	

	//break;
}

//print_r(fgetcsv($fp));
fclose($fp);
?>