<?
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;

$wiscollect_file_id = mdb2_single_val($db, "SELECT wiscollect_file_id FROM wiscollect_files WHERE wiscollect_file_sha256 = '%q'", $sha256);
if (!$wiscollect_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 wiscollect_files
	( wiscollect_file_name, wiscollect_file_size,
	wiscollect_file_type, wiscollect_file_sha256 )
VALUES ( '%q', %i, '%q', '%q' )
EOQ
	, $name, $size, $type, $sha256);

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

$info = mdb2_single_assoc($db,
	"SELECT * FROM wiscollect_files WHERE wiscollect_file_id = $wiscollect_file_id");

print_r($info);

if ($info['wiscollect_ok'] == 1) error_system("file was previously uploaded and was already successfully imported");
else  {
	mdb2_exec($db, "DELETE FROM wiscollect WHERE wiscollect_file_id = $wiscollect_file_id");
	mdb2_exec($db, "UPDATE wiscollect_files SET wiscollect_scanned = 0, wiscollect_stored = 0, wiscollect_skipped = 0 WHERE wiscollect_file_id = $wiscollect_file_id");
}

if ($info['wiscollect_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['wiscollect_file_sha256'])
	error_system("hash of file in storage directory is different than expected");

echo("wiscollect_file_id=$wiscollect_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);

$barcode_idx = check_and_extract($idx, 'Leerlingnummer');
$betaald_idx = check_and_extract($idx, 'Is factuur betaald');
$betaalwijze_idx = check_and_extract($idx, 'Betaalwijze');
$factuurnummer_idx = check_and_extract($idx, 'Factuurnummer');
$bedrag_idx = check_and_extract($idx, 'Totaal factuurbedrag');

$no = 1;


$skipped = 0;
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 wiscollect_files SET wiscollect_scanned = wiscollect_scanned + 1 WHERE wiscollect_file_id = $wiscollect_file_id");
	print_r($row);
	mdb2_exec($db, "INSERT INTO wiscollect ( wiscollect_file_id, barcode, betaalwijze, betaald, factuurnummer ) VALUE ( %i, '%q', '%q', %i, '%q' )", $wiscollect_file_id, $row[$barcode_idx], $row[$betaalwijze_idx], $row[$betaald_idx] == 'ja'?1:0, $row[$factuurnummer_idx]);
	$wiscollect_id = mdb2_single_val($db, 'SELECT LAST_INSERT_ID()');
	$bedrag = $row[$bedrag_idx];
	if ($bedrag != '') mdb2_exec($db, "UPDATE wiscollect SET bedrag = '%q' WHERE wiscollect_id = $wiscollect_id", $bedrag);
	mdb2_exec($db, "UPDATE wiscollect_files SET wiscollect_stored = wiscollect_stored + 1 WHERE wiscollect_file_id = $wiscollect_file_id");
}

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

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

mdb2_exec($db, "UPDATE wiscollect_files SET wiscollect_ok = TRUE WHERE wiscollect_file_id = $wiscollect_file_id");

?>