<?
require_once('phplib/phplib.php');
require_once('common.php');

check_personeel();

$db = mdb2_open($dsn);
$herinfo = get_herinfo($db);
if ($herinfo === NULL) {
	unset($_SESSION['type']);
	error_user('Geen aankomende herkansingsperiode', 'Het is momenteel niet mogelijk om te zien welke leerlingen zich voor welke herkansing heeft opgegeven. Klopt dit niet? Neem contact op met de beheerder, '.$sysop.' <'.$sysop_email.'>');
}

if ($_GET['ppl_id'] != '') {
	$afkorting = mdb2_single_val($db, <<<EOQ
SELECT ppl_login
FROM period2grp
JOIN $kldv.grp2subj ON grp2subj.grp_id = period2grp.grp_id
JOIN $kldv.ppl2grp2subj ON ppl2grp2subj.grp2subj_id = grp2subj.grp2subj_id
AND ppl2grp2subj.ppl_enter <= {$herinfo['period_deadline']}
AND ppl2grp2subj.ppl_exit > {$herinfo['period_deadline']}
JOIN $kldv.ppl ON ppl.ppl_id = ppl2grp2subj.ppl_id
WHERE period_id = {$herinfo['period_id']}
AND ppl.ppl_id = '%q'
ORDER BY ppl_login
EOQ
, $_GET['ppl_id']);
	if (!$afkorting) error_user('Docent niet gevonden', 'Geselecteerde docent geeft geen les aan klassen die herkansingen opgeven via deze pagina');
	$or = '1=0';
} else $or = '1=1';

$idx2vakken = mdb2_col($db, 0, <<<EOQ
SELECT DISTINCT subj_abbrev FROM $kldv.subj
JOIN rex ON rex.subj_id = subj.subj_id
ORDER BY subj_abbrev
EOQ
);

$idx2pta = mdb2_col($db, 0, <<<EOQ
SELECT DISTINCT $concat_begin subj_abbrev $concat_cont '/TP' $concat_cont CAST(((pta_period - 1)%%3) + 1 $as_varchar) $concat_end
FROM $kldv.subj
JOIN rex ON rex.subj_id = subj.subj_id AND rex.period_id = {$herinfo['period_id']}
JOIN ptas ON ptas.pta_id = rex.pta_id
EOQ
);

//print_r($idx2pta);
//exit;

//$vakken2idx = array();
//foreach ($idx2vakken as $idx => $vak) {
//	$vakken2idx[$vak] = $idx;
//}

//$pta2idx = array();
//foreach ($idx2pta as $idx => $vak) {
//	$pta2idx[$vak] = $idx;
//}

$hers = mdb2_query($db, <<<EOQ
SELECT $concat_begin ppl.ppl_forename $concat_cont ' ' $concat_cont
	 ppl.ppl_prefix $concat_cont ' ' $concat_cont ppl.ppl_surname $concat_cont
	 ' (' $concat_cont ppl.ppl_login $concat_cont '/' $concat_cont grp_name $concat_cont ')' $concat_end leerling,
	$group_concat($concat_begin subj_abbrev $concat_cont '/TP' $concat_cont CAST(((pta_period - 1)%%3) + 1 $as_varchar) $concat_end) vakken, SUBSTRING(grp_name, 1, 2) afdeling
FROM rex
JOIN $kldv.ppl ON ppl.ppl_id = rex.ppl_id
JOIN $kldv.ppl2grp ON ppl2grp.ppl_id = rex.ppl_id
AND ppl2grp.ppl_enter <= {$herinfo['period_deadline']}
AND ppl2grp.ppl_exit > {$herinfo['period_deadline']}
JOIN $kldv.grp2subj ON grp2subj.grp_id = ppl2grp.grp_id AND grp2subj.subj_id = rex.subj_id
JOIN $kldv.ppl2grp2subj ON ppl2grp2subj.grp2subj_id = grp2subj.grp2subj_id
AND ppl2grp2subj.ppl_enter <= {$herinfo['period_deadline']}
AND ppl2grp2subj.ppl_exit > {$herinfo['period_deadline']}
JOIN $kldv.ppl2grp AS ppl2grp2 ON ppl2grp2.ppl_id = ppl.ppl_id
AND ppl2grp2.ppl_enter <= {$herinfo['period_deadline']}
AND ppl2grp2.ppl_exit > {$herinfo['period_deadline']}
JOIN $kldv.grp ON grp.grp_id = ppl2grp2.grp_id
JOIN $kldv.ppl AS doc ON ppl2grp2subj.ppl_id = doc.ppl_id
JOIN $kldv.subj ON subj.subj_id = rex.subj_id
JOIN ptas ON ptas.pta_id = rex.pta_id
WHERE period_id = {$herinfo['period_id']}
AND grp_home = 1
AND (doc.ppl_id = '%q' OR $or)
GROUP BY ppl.ppl_forename, ppl.ppl_prefix, ppl.ppl_surname, ppl.ppl_login, grp_name, ppl.ppl_id
ORDER BY grp_name, ppl.ppl_surname, ppl.ppl_forename, ppl.ppl_prefix
EOQ
, $_GET['ppl_id']);

//header("Content-type: text/html");

//$no_vakken = count($vakken2idx);
//echo("no vakken $no_vakken");
//print_r($vakken2idx);

//mdb2_res_table($hers);
//exit;

if (!($row = $hers->fetchRow(MDB2_FETCHMODE_ORDERED))) {
	if ($afkorting) error_user('Geen herkansingen gevonden', htmlenc($afkorting).' heeft op dit moment geen herkansers.');
	else error_user('Geen herkansingen gevonden', 'Geen enkele leerling heeft op dit moment een herkansing opgegeven');
}

/* we'll send a .csv file */
header("Content-type: text/csv");

/* some red tape to avoid bugs and weird errormessages in IE */
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Cache-Control: public");
header('Content-Disposition: attachment; filename=export.csv;');

$file = fopen('php://output', 'w');
if (!$file) fatal_error('unable to open output');
$legenda = array();
$legenda[] = 'leerling (nr/klas)';
$legenda[] = 'afdeling';
foreach ($idx2pta as $vak) {
	$legenda[] = $vak;
}
fputcsv($file, $legenda, ';', '"');
do {
	$leerling = array();
	$leerling[] = iconv('UTF-8', 'ISO-8859-1', htmldec($row[0]));
	$leerling[] = $row[2];
	$deze_vakken = explode(',', $row[1]);
	foreach ($idx2pta as $vak) {
		if (in_array($vak, $deze_vakken)) $leerling[] = 1;
		else $leerling[] = '';
	}
	fputcsv($file, $leerling, ';', '"');
	$labelno++;
} while (($row = $hers->fetchRow(MDB2_FETCHMODE_ORDERED)));

?>