<?
ini_set('memory_limit', '256M');
require_once('phplib/phplib.php');
require_once('phplib/upload.php');
require_once('common.php');
mysqli_report(MYSQLI_REPORT_OFF);

$db = mdb2_open($dsn);

check_admin();

header('Content-type: text/plain');

print_r($_FILES);

$upload = check_and_extract($_FILES, "uploadedfile");

$name =  check_and_extract($upload, "name");
$type = check_and_extract($upload, "type");
$tmp_name = check_and_extract($upload, "tmp_name");
$error = check_and_extract($upload, "error");
$size = check_and_extract($upload, "size");

if ($error) error_system($error);

if ($size == 0) error_system('uploaded file has size 0');

if ($type != 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
	error_system("declared file type by broweser is \"type\" while we expected \"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet\"");

$sha256 = hash_file('sha256', $tmp_name);

if (!$sha256) error_system("unable to calculate sha256 hash of uploaded file in tmp dir");

echo("sha256=$sha256\n");
$filename = $data.$sha256;

$pto_import_file_id = mdb2_single_val($db, "SELECT pto_import_file_id FROM pto_import_files WHERE pto_import_file_sha256 = '%q'", $sha256);
if (!$pto_import_file_id) {
	// this file is new
	if (!move_uploaded_file($tmp_name, $filename)) {
		error_system("unable to move uploaded file to $filename");
	}
	mdb2_exec($db, <<<EOQ
INSERT INTO pto_import_files
	( pto_import_file_name, pto_import_file_size,
	pto_import_file_type, pto_import_file_sha256 )
VALUES ( '%q', %i, '%q', '%q' )
EOQ
	, $name, $size, $type, $sha256);

	$pto_import_file_id = mdb2_single_val($db, 'SELECT LAST_INSERT_ID()');
}

$info = mdb2_single_assoc($db,
	"SELECT * FROM pto_import_files WHERE pto_import_file_id = $pto_import_file_id");

if ($info['year_id']) error_system("file was previously uploaded and was already imported");
else  {
	mdb2_exec($db, "DELETE FROM pto_import WHERE pto_import_file_id = $pto_import_file_id");
	mdb2_exec($db, "UPDATE pto_import_files SET pto_import_scanned = 0 AND pto_import_stored = 0 WHERE pto_import_file_id = $pto_import_file_id");
}

if ($info['pto_import_file_sha256'] != $sha256)
	error_system("mismatch of calculated sha256 and requested sha256 of uploaded file");

$sha256 = hash_file('sha256', $filename);
if (!$sha256) error_system("unable to calculate sha256 hash of uploaded file in storage dir");

if ($sha256 != $info['pto_import_file_sha256'])
	error_system("hash of file in storage directory is different than expected");

print_r($info);
echo("pto_import_file_id=$pto_import_file_id\n");

$output = array();
$result_code = 0;

exec("/usr/bin/ssconvert $filename $filename.csv", $output, $result_code);

if ($result_code != 0) {
	print_r($output);
	error_system("unable to convert uploaded file to csv");
}

echo("uploaded file successfully converted to csv\n");

if (!($csv = fopen("$filename.csv", 'r'))) error_system("unable to open $filename.csv");

if (feof($csv)) error_system("$filename.csv does not contain data");

$legenda = fgetcsv($csv);

if ($legenda == false) error_system("error reading first line from $filename.csv");

$idx = array();
foreach ($legenda as $i => $col) {
	if (isset($idx[$col])) error_system("column name \"$col\" appears more than once");
	$idx[$col] = $i;
}

print_r($idx);

$lichting_idx = check_and_extract($idx, 'Lichting');
$leerjaar_idx = check_and_extract($idx, 'Leerjaar');
$periode_idx = check_and_extract($idx, 'Periode');
$vak_idx = check_and_extract($idx, 'Vak afkorting');
$id_idx = check_and_extract($idx, 'Toets id');
$code_idx = check_and_extract($idx, 'Toetscode');
$longdesc_idx = check_and_extract($idx, 'Toetsomschrijving');
$htype_idx = check_and_extract($idx, 'Herkansingtype');

$no = 1;

$year_id_compare = NULL;

while (!feof($csv)) {
	$no++;
	if (!($row = fgetcsv($csv))) {
		if (feof($csv)) break;
		else error_system("unable to read line $no of $filename.csv");
	}
	mdb2_exec($db, "UPDATE pto_import_files SET pto_import_scanned = pto_import_scanned + 1 WHERE pto_import_file_id = $pto_import_file_id");
	print_r($row);
	if (!preg_match('/^(.*) (\d) (\d\d\d\d)-(\d\d\d\d)$/', $row[$lichting_idx], $matches))
		error_system("unable to match value of Lichting: {$row[$lichting_idx]}");
	$pto_target = $matches[1];
	$pto_leerjr = $matches[2];
	$year_long = $matches[3].'/'.$matches[4];
	echo("year_long = $year_long\n");
	if (!($year_id = mdb2_single_val($db, "SELECT year_id FROM $kldv.years WHERE year_long = '%q'", $year_long))) error_system("schooljaar $year_long undefined in database $kldv");
	if (!$year_id_compare) $year_id_compare = $year_id;
	else if ($year_id_compare != $year_id) error_system("toetsen uit meerdere schooljaren in PTO import, kan niet");
	echo("year_id=$year_id\n");
	if ($pto_leerjr != $row[$leerjaar_idx]) error_system("Mismatch of leerjr between {$row[$lichting_idx]} and specified Leerjaar {$row[$leerjaar_idx]}");
	if (!($subj_id = mdb2_single_val($db, "SELECT subj_id FROM $kldv.subj WHERE subj_abbrev = '%q'", $row[$vak_idx]))) error_system("subj {$row[$vak_idx]} not found in database $kldv");
	echo("subj_id=$subj_id\n");
	if (mdb2_exec_native_errors($db, array(1062), <<<EOQ
INSERT INTO pto_import ( pto_import_file_id, pto_target, pto_leerjr, pto_per,
	subj_id, som_toets_id, pto_code, pto_longdesc, pto_herkansingtype )
VALUES ( $pto_import_file_id, '%q', %i, %i, $subj_id, '%q', '%q', '%q', '%q' )
EOQ
			, $pto_target, $pto_leerjr, $row[$periode_idx], $row[$id_idx],
			$row[$code_idx], $row[$longdesc_idx], $row[$htype_idx])) {
		$dupl = mdb2_single_assoc($db, "SELECT * FROM pto_import WHERE pto_import_file_id = $pto_import_file_id AND pto_target = '%q' AND pto_leerjr = %i AND pto_per = %i AND subj_id = $subj_id AND pto_code = '%q'",  $pto_target, $pto_leerjr, $row[$periode_idx], $row[$code_idx]);
		print_r($dupl);
		error_system("duplicate entry");
	} else mdb2_exec($db, "UPDATE pto_import_files SET pto_import_stored = pto_import_stored + 1 WHERE pto_import_file_id = $pto_import_file_id");
}

if (!fclose($csv)) error_system("error closing $filename.csv");

echo("lines=$no\n");
$num_rows = mdb2_single_val($db, "SELECT COUNT(*) FROM pto_import WHERE pto_import_file_id = $pto_import_file_id");
echo("num_rows=$num_rows\n");
if ($no == $num_rows + 2) {
	echo("Success.\n");
} else {
	echo("At least one PTO rule could not be stored");
}

mdb2_exec($db, "UPDATE pto_import_files SET year_id = $year_id_compare WHERE pto_import_file_id = $pto_import_file_id");

?>