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

$year_id = 16; // year_id from kldv.years
require_once('common.php');
require_once('phplib/phplib.php');

$db = mdb2_open($dsn);

$years = explode('/', mdb2_single_val($db, "SELECT year_long FROM $kldv.years WHERE year_id = %i", $year_id));

print_r($years);
$lines = file($argv[1]);

//$count = mdb2_single_val($db, "SELECT COUNT(*) FROM ptas WHERE year_id = $year_id AND pta_tp = %i AND inhaalregime = 0 AND stvt != 'ET'", $tp);
//if ($count) error_system("reeds ingelezen");

$idx = array();
$legends = str_getcsv(array_shift($lines), ",", '"');
foreach ($legends as $i => $legend) {
	$idx[$legend] = $i;
}

print_r($legends);
function deref($row, $i) {
	global $idx;
	if (!isset($idx[$i])) error_system("invalid column name $i");
	return $row[$idx[$i]];
}

foreach ($lines as $line) {
	$csv = str_getcsv($line, ",", "\"");
	if (!preg_match('/^([A-Z-\/]+) (\d) (\d{4})-(\d{4})$/', deref($csv, 'Lichting'), $matches)) error_system("unmatched {$csv[0]}");
	//print_r($csv);
	//print_r($matches);
	switch ($matches[1]) {
	case 'HAVO':
		$pta_target = 'H';
		break;
	case 'VWO':
		$pta_target = 'A';
		break;
	case 'VWO-TT':
		$pta_target = 'AP';
		break;
	case 'HAVO/VWO-TT':
		echo("ignore AH for now\n");
		continue 2;
	default:
		error_system("unsupported target {$matches[1]}");
		break;
	}
	$pta_leerjr = $matches[2];
	if ($pta_leerjr != 1 && $pta_leerjr != 2 && $pta_leerjr != 3) error_system("impossible leerjr");
	if ($matches[3] != $years[0] || $matches[4] != $years[1]) error_system("wrong year_id");

	$subj = deref($csv, 'Vak afkorting');

	$subj_id = mdb2_single_val($db, "SELECT subj_id FROM $kldv.subj WHERE subj_abbrev = '%q'", $subj);
	if (!$subj_id) error_system("subj $subj not found");
	$pta_tp = deref($csv, 'Periode');
	if ($pta_tp != 1 && $pta_tp != 2 && $pta_tp != 3) error_system("ongeldige pta_tp $pta_tp");
	$stvt = 'Toets';
	switch (deref($csv, 'Herkansingtype')) {
	case 'Geen':
		$stdher = 0;
		break;
	case 'Hoogste, 1 herkansing':
		$stdher = 1;
		break;
	case 'Laatste, 1 herkansing':
		$stdher = 1;
		break;
	default:
		error_system("onbekend herkansingsstype ".deref($csv, 'Herkansingtype'));
	}

	
	$id = deref($csv, 'Toets id');
	echo("ID: $id\n");
	$info = mdb2_single_assoc($db, "SELECT * FROM ptas WHERE som_toets_id = '%q'", $id);
	if (!$info) {
	mdb2_exec($db, "INSERT INTO ptas ( pta_tp, pta_leerjr, pta_target, year_id, subj_id, shortdesc, stvt, inhaalregime, toetsduur, stdher, som_toets_id ) VALUES ( %i, %i, '%q', $year_id, $subj_id, ' %q', '%q', 1, 50, %i, '%q' )", $pta_tp, $pta_leerjr, $pta_target, deref($csv, 'Toetscode'), $stvt, $stdher, deref($csv, 'Toets id'));
	if ($pta_target == 'AP') {
		mdb2_exec($db, "INSERT INTO ptas ( pta_tp, pta_leerjr, pta_target, year_id, subj_id, shortdesc, stvt, inhaalregime, toetsduur, stdher, som_toets_id ) VALUES ( %i, %i, '%q', $year_id, $subj_id, ' %q', '%q', 1, 50, %i, '%q' )", $pta_tp, $pta_leerjr, 'GP', deref($csv, 'Toetscode'), $stvt, $stdher, deref($csv, 'Toets id'));

	}

		continue;
	}
	print_r($info);
	if ($info['stdher'] == 0 && $stdher == 1) {
		mdb2_exec($db, "UPDATE ptas SET stdher = 1 WHERE pta_id = %i", $info['pta_id']);
		echo("jottem\n");
	}
	if ($info['shortdesc'] !=  ' '.deref($csv, 'Toetscode')) echo("opvallend {$info['shortdesc']} = ".deref($csv, 'Toetscode')." ".deref($csv, 'Vak afkorting')."\n");
}
?>