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

check_personeel();

define('LEERLING', 1);
define('DOCENT', 2);
define('LOKAAL', 3);
define('LESGROEP', 4);
define('STAMKLAS', 5);
define('VAK', 6);
define('CATEGORIE',7);

$file_id = mdb2_single_val($db, <<<EOQ
SELECT file_id FROM $roosterdb.files
WHERE file_type = 1 AND file_status = 1
ORDER BY file_time DESC
LIMIT 1
EOQ
);

$date = date('Ymd');

$and = '';
$toets_tp = '';
if (!isset($_GET['toets_tp'])) error_system("impossible");

$toets_tp = $_GET['toets_tp'];
if ($toets_tp == 'TP3') {
	$and = ' AND toets_tp = \''.$toets_tp.'\'';
} else error_system("impossible value for toets_tp");

$toetsen = mdb2_query($db, <<<EOQ
SELECT CONCAT(toets_target, ' ', subj_abbrev, ' ',
		IF(toets_soort = 'HD', 'HD', IF(toets_soort = 'VT', 'VT', IF(toets_soort = 'PO', 'PO', 'ST'))),
		toets_nummer,
		IF(toets_omschrijving = '', '', CONCAT(' ', toets_omschrijving))) formule,
	CONCAT(toets_target, '.', subj_abbrev) afdvak, toets_lokaal,
TIME_FORMAT(DATE_ADD(MAKETIME(7,50,0), INTERVAL toets_duur MINUTE), "%%H:%%i") tot, toets_duur
FROM toets
JOIN toets2subj USING (toets_id)
JOIN $kldv.subj USING (subj_id)
WHERE year_id = $year_id$and
AND toets_soort != 'STALLEENHER' AND toets_soort != 'VT'
ORDER BY formule
EOQ
);

$filename = "transport-toetsdomein-TP0-".date("YmdHis").'.txt';
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='.$filename.';');

$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', 'XLokaaleis');
echo(implode("\t", $legenda)."\r\n");

$out = array();
foreach ($legenda as $key) {
	$out[$key] = NULL;
}

$out['Object'] = 1;
$out[' '] = '     ';
$out['Datum'] = $date;
$out['Van'] = '07:50'; // Tot wordt automatisch berekend
$out['Te roosteren'] = 0; // 0 doet het ook ?, wel bij de herkansingen, waarde was 100;


//print_r($out);

while (($row = $toetsen->fetchRow(MDB2_FETCHMODE_ASSOC))) {
	$afdvak = explode('.', $row['afdvak']);
	$afd = $afdvak[0];
	$vak = $afdvak[1];
	// upgrade afdeling
	switch ($afd) {
	case '4H':
		$row['afdvak'] = '5H.'.$vak;
		$afd = '5H';
		break;
	case '4V':
		$row['afdvak'] = '5V.'.$vak;
		$afd = '5V';
		break;
	case '5V':
		$row['afdvak'] = '6V.'.$vak;
		$afd = '6V';
		break;
	default:
		error_system("impossible afdeling in TP0");
	}

	$bla = mdb2_single_assoc($db, <<<EOQ
SELECT GROUP_CONCAT(DISTINCT groepen.entity_name ORDER BY groepen.entity_name) groepen, GROUP_CONCAT(DISTINCT docenten.entity_name ORDER BY docenten.entity_name) docenten
FROM $roosterdb.entities AS categorieen
JOIN $roosterdb.entities AS vakken
JOIN $roosterdb.grp2grp AS categorieen2groepen
ON categorieen2groepen.lesgroep_id = categorieen.entity_id
JOIN $roosterdb.entities AS groepen
ON categorieen2groepen.lesgroep2_id = groepen.entity_id AND groepen.entity_type != %i
JOIN $roosterdb.entities2lessen AS vakken2lessen ON vakken.entity_id = vakken2lessen.entity_id
JOIN $roosterdb.entities2lessen AS groepen2lessen ON groepen.entity_id = groepen2lessen.entity_id AND vakken2lessen.les_id = groepen2lessen.les_id
JOIN $roosterdb.files2lessen ON files2lessen.les_id = vakken2lessen.les_id AND files2lessen.file_id = %i
JOIN $roosterdb.entities2lessen AS docenten2lessen ON docenten2lessen.les_id = files2lessen.les_id
JOIN $roosterdb.entities AS docenten ON docenten.entity_id = docenten2lessen.entity_id AND docenten.entity_type = %i
WHERE categorieen.entity_name = '%q' AND vakken.entity_name = '/%q'
EOQ
, CATEGORIE, $file_id, DOCENT, $afd, $vak);
	/*$bla = mdb2_query($db, <<<EOQ
SELECT les_id, dag, uur, docenten, vakken.entity_name vak, groepen.entity_name groep, categorieen.entity_name categorie
FROM $roosterdb.files2lessen
JOIN $roosterdb.lessen USING (les_id)
JOIN $roosterdb.entities2lessen AS vakken2lessen USING (les_id)
JOIN $roosterdb.entities AS vakken ON vakken.entity_id = vakken2lessen.entity_id AND vakken.entity_type = %i
JOIN $roosterdb.entities2lessen AS groepen2lessen USING (les_id)
JOIN $roosterdb.entities AS groepen ON groepen.entity_id = groepen2lessen.entity_id AND ( groepen.entity_type = %i OR groepen.entity_type = %i )
JOIN $roosterdb.grp2grp AS groep2categorie ON groep2categorie.lesgroep_id = groepen.entity_id
JOIN $roosterdb.entities AS categorieen ON categorieen.entity_id = groep2categorie.lesgroep2_id AND categorieen.entity_type = %i
WHERE file_id = $file_id AND vakken.entity_name = CONCAT('/', '%q') AND categorieen.entity_name = '%q'
EOQ
	, VAK, STAMKLAS, LESGROEP, CATEGORIE, $vak, $afd);*/
	//print_r($bla);
	//while ($check = $bla->fetchRow(MDB2_FETCHMODE_ASSOC)) {
//		print_r($check);
//	}

	//print_r($row);
	$lln = 0;
	$surv = 0;
	foreach (explode(',', $bla['groepen']) as $grp) {
		//echo("count $grp\n");
		$lln += mdb2_single_val($db, "SELECT COUNT(ppl_id) FROM $roosterdb.entities JOIN $roosterdb.grp2ppl ON entities.entity_id = grp2ppl.lesgroep_id AND file_id_basis = %i WHERE entity_name = '%q'", $file_id, $grp);
		$surv += 1;
	}
	$out['Aantal lln'] = $lln;
	$out['Aantal surv'] = $surv;
	$out['Formule'] = $row['formule'];
	$out['afdvak'] = $row['afdvak'];
	$out['Tot'] = $row['tot']; //'09:30';
	$out['Totaal aantal minuten'] = $row['toets_duur'];
	$out['Aantal minuten'] = $row['toets_duur'];
	$out['XVakdocent'] = $bla['docenten'];
	$out['Grp'] = $bla['groepen'];
	$out['XLokaaleis'] = $row['toets_lokaal'];
	echo(implode("\t", $out)."\r\n");
	$out['Object']++;
	//exit;
}