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

check_admin();

$db = mdb2_open($dsn);

if (isset($_GET['period_id'])) {
	$period_id = $_GET['period_id'];
	$herinfo = get_herinfo($db, $period_id);
} else {
	$herinfo = get_herinfo($db);
       	$period_id = $herinfo['period_id'];
}

//$res = mdb2_query($db, <<<EOQ
$stuff = mdb2_all_assoc($db, <<<EOQ
SELECT moment_id, grp_id, pta_id, DATE_FORMAT(datum, "%%Y%%m%%d") datum,
	DATE_FORMAT(moment_time_start, "%%H:%%i") van,	
DATE_FORMAT(moment_time_end, "%%H:%%i") tot, CONCAT(REPLACE(REPLACE(toets, '/', ' '), '.', ' '), '_', grp_id, '_', pta_id, '_', moment_id) toets, CONCAT('#Toets.', REPLACE(lesgroep, '.', ''), '_', grp_id, '_', pta_id, '_', moment_id) grp, CONCAT(REPLACE(lesgroep, '.', ''), '_', grp_id, '_', pta_id, '_', moment_id) grpkaal, aantal, lln, duur, docs, afdvak, lok, surv FROM (
	SELECT moment_id, grp_id, pta_id,
		moment_date datum, moment_time_start, moment_time_end, PTA(pta_id)
		toets, grp_name lesgroep,
		COUNT(rex_id) aantal, GROUP_CONCAT(CONCAT(SUBSTRING(grp_name, 1, 2), '.', lln.ppl_login)) lln, TIME_TO_SEC(TIMEDIFF(moment_time_end, moment_time_start)) DIV 60 duur, docs, CONCAT((pta_period - 1) DIV 3 + 4,  pta_target, '.', subj_abbrev) afdvak, momentlocation_lokaal lok, momentlocation_surveillant surv, 0 xtra
	FROM rooster
	JOIN moments USING (moment_id, period_id)
	JOIN ptas USING (pta_id)
	JOIN rex USING (period_id, pta_id)
	LEFT JOIN (
		SELECT grp.grp_id, ppl2grp.ppl_id, subj_id, grp_name, GROUP_CONCAT(DISTINCT doc.ppl_login) docs
		FROM $kldv.grp2subj
		JOIN $kldv.ppl2grp
		ON ppl2grp.grp_id = grp2subj.grp_id
		AND ppl2grp.ppl_enter <= {$herinfo['period_deadline']}
		AND ppl2grp.ppl_exit > {$herinfo['period_deadline']}
		JOIN $kldv.grp ON grp.grp_id = ppl2grp.grp_id
		JOIN $kldv.year2grp2subj ON year2grp2subj.grp2subj_id = grp2subj.grp2subj_id AND year_id = {$herinfo['year_id']} AND grp2subj_enter <= {$herinfo['period_deadline']} AND grp2subj_exit > {$herinfo['period_deadline']}
		LEFT JOIN $kldv.ppl2grp2subj ON grp2subj.grp2subj_id = ppl2grp2subj.grp2subj_id
		AND ppl2grp2subj.ppl_enter <= {$herinfo['period_deadline']}
		AND ppl2grp2subj.ppl_exit > {$herinfo['period_deadline']}
		LEFT JOIN $kldv.ppl AS doc ON doc.ppl_id = ppl2grp2subj.ppl_id 
		GROUP BY grp2subj.grp2subj_id, grp.grp_id, ppl2grp.ppl_id
	) AS grp ON grp.ppl_id = rex.ppl_id AND grp.subj_id = ptas.subj_id
	LEFT JOIN newdetailrooster USING (grp_id, rooster_id)
	LEFT JOIN momentlocations USING (momentlocation_id, moment_id)
	JOIN $kldv.ppl AS lln ON rex.ppl_id = lln.ppl_id
	JOIN $kldv.subj ON subj.subj_id = ptas.subj_id
	WHERE period_id = %i
	GROUP BY pta_id, grp_name
	UNION 
	SELECT moment_id, NULL, NULL, moment_date, moment_time_start, moment_time_end,  IF(reserve_order = 0, 'gangwacht', CONCAT('reserve ', reserve_order)), '', NULL, '', '', TIME_TO_SEC(TIMEDIFF(moment_time_end, moment_time_start)) DIV 60, '', '', reserve, reserve_order
	FROM reserves
	JOIN moments USING (moment_id)
	WHERE period_id = %i
) AS bla
ORDER BY datum, moment_time_start, IF(grp_id IS NULL, 1, 0), xtra, surv, toets, lesgroep
EOQ
, $period_id, $period_id);

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

//print_r($stuff);
if (isset($_GET['groep'])) {
	$filename = "transport-groepdomein-herkansingen-TP{$herinfo['period_num']}-".date("YmdHis").'.txt';
} else {
	$filename = "transport-toetsdomein-herkansingen-TP{$herinfo['period_num']}-".date("YmdHis").'.txt';
}
//echo($filename);
//exit;

/* 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='.$filename.';');

//print_r($stuff);
if (isset($_GET['groep'])) {
	$legenda = array('Niveau 1', 'Object', ' ', 'Formule', '#Taak', '#Kernteam', '#STAMIDENTIEKGEWICHT', '#RENDEMENTLEERJARENHISTORIE', '#RENDEMENTNIVEAUINDEX', 'Aantal elementen', 'Type', 'verzameling', 'Ingeroosterd', '#PORTALID');
} else {
	//$legenda = array('Object', ' ', 'Formule', 'XVakdocent', 'afdvak', 'Grp', 'Doc', 'Lok', 'Totaal aantal minuten', 'Aantal minuten', 'Datum', 'Van', 'Tot', 'Aantal lln', 'Aantal surv', 'Vakdoc', 'Fixed', 'Te roosteren');
	$legenda = array('Object', ' ', 'Formule', 'XVakdocent', '#Toetslokalen', '#Verval', '#TijdelijkeInstances', 'afdvak',  'Grp', 'Lok', 'Totaal aantal minuten',  'Aantal minuten', 'Datum',  'Van', 'Tot', 'Aantal lln', 'Aantal surv', '#INSTANCESNAPSHOT', '');
}
//echo($filename);
//exit;

echo(implode("\t", $legenda)."\r\n");

$out = array();
foreach ($legenda as $key) {
	if ($key != '') $out[$key] = NULL;
}
if (isset($_GET['groep'])) {
	$out['Niveau 1'] = '#Toets';
	$out[' '] = '     ';
	$out['Type'] = 'Geen stamklas';

	foreach ($stuff as $row) {
		$out['Object'] = $row['grpkaal'];
		$out['verzameling'] = $row['lln'];
		$out['Aantal elementen'] = $row['aantal'].'   ('.$row['aantal'].')';
		echo(implode("\t", $out)."\r\n");
	}
} else {

	$out['Object'] = 100;
	$out[' '] = '     ';

	foreach ($stuff as $row) {
		$out['Formule'] = $row['toets'];
		$out['XVakdocent'] = ''; //$row['docs'];
		$out['#Toetslokalen'] = '';
		$out['afdvak'] = $row['afdvak'];
		$out['Grp'] = $row['grp'];
		$out['Doc'] = $row['surv'];
		$out['Lok'] = $row['lok'];
		$out['Totaal aantal minuten'] = $row['duur'];
		$out['Aantal minuten'] = $row['duur'];
		$out['Datum'] = $row['datum'];
		$out['Van'] = $row['van'];
		$out['Tot'] = $row['tot'];
		$out['Aantal lln'] = $row['aantal'];
		$out['Aantal surv'] = 1;
		//$out['Vakdoc'] = '';
		//$out['Fixed'] = '';
		//$out['Te roosteren'] = 0;
		//$out[''] = '';

		echo(implode("\t", $out)."\r\n");
		$out['Object']++;
	}
}
?>