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

check_admin();

$db = mdb2_open($dsn);

$herinfo = get_better_herinfo($db, $_GET);

$period_id = $herinfo['period_id'];

$rooster_id = get_rooster_id($db, $period_id);
if ($rooster_id) {
	$rooster_info = mdb2_single_assoc($db, "SELECT * FROM rooster_ids WHERE rooster_id = $rooster_id");
} else error_user("Rooster niet gepubliceerd", "Het herkansingsrooster is nog niet beschikbaar");

$sort_activiteit = '';
if (isset($_GET['gdpr'])) {
	$sort_leerling= ', llnr';
} else {
	$sort_leerling= ', ppl_surname, ppl_forename, ppl_prefix';
}

$where = " -- %q";

if ($_GET['moment']) {
	$where = " AND CONCAT(date, '/', time_start) = '%q'";
}

$rooster2 =mdb2_query($db, <<<EOQ
SELECT
	CONCAT(CASE
                WHEN DATE_FORMAT(date, '%%w') = 0 THEN 'zondag'
                WHEN DATE_FORMAT(date, '%%w') = 1 THEN 'maandag'
                WHEN DATE_FORMAT(date, '%%w') = 2 THEN 'dinsdag'
                WHEN DATE_FORMAT(date, '%%w') = 3 THEN 'woensdag'
                WHEN DATE_FORMAT(date, '%%w') = 4 THEN 'donderdag'
                WHEN DATE_FORMAT(date, '%%w') = 5 THEN 'vrijdag'
                WHEN DATE_FORMAT(date, '%%w') = 6 THEN 'zaterdag'
        END, ' ', DATE_FORMAT(date, '%%e'), ' ',
        CASE
                WHEN DATE_FORMAT(date, '%%c') = 1 THEN 'januari'
                WHEN DATE_FORMAT(date, '%%c') = 2 THEN 'februari'
                WHEN DATE_FORMAT(date, '%%c') = 3 THEN 'maart'
                WHEN DATE_FORMAT(date, '%%c') = 4 THEN 'april'
                WHEN DATE_FORMAT(date, '%%c') = 5 THEN 'mei'
                WHEN DATE_FORMAT(date, '%%c') = 6 THEN 'juni'
                WHEN DATE_FORMAT(date, '%%c') = 7 THEN 'juli'
                WHEN DATE_FORMAT(date, '%%c') = 8 THEN 'augustus'
                WHEN DATE_FORMAT(date, '%%c') = 9 THEN 'september'
                WHEN DATE_FORMAT(date, '%%c') = 10 THEN 'oktober'
                WHEN DATE_FORMAT(date, '%%c') = 11 THEN 'november'
                WHEN DATE_FORMAT(date, '%%c') = 12 THEN 'december'
        END, ' ', DATE_FORMAT(date, '%%Y')) dag, CONCAT(TIME_FORMAT(time_start, '%%H:%%i'), ' - ', TIME_FORMAT(time_end, '%%H:%%i')) tijd, TIME_FORMAT(time_start, '%%H:%%i') start, TIME_FORMAT(time_end, '%%H:%%i') end,
	UPPER(lok) lokaal,  surv, activiteit, ppl_login llnr, stamklas, lgv, docs, CONCAT(ppl_forename, ' ', ppl_prefix, ' ', ppl_surname, ' (', ppl_login, '/', stamklas, ')') leerling
FROM roosterdetail
JOIN (
        SELECT grp.grp_id, rex.pta_id, stamklassen.grp_name stamklas, IF(grp.grp_home = 1, CONCAT(stamklassen.grp_name, '/', subj_abbrev), grp.grp_name) lgv, docs, rex_id, ppl_login, ppl_forename, ppl_prefix, ppl_surname
        FROM $kldv.grp
        JOIN $kldv.ppl2grp ON ppl2grp.grp_id = grp.grp_id
        AND ppl_enter <= {$herinfo['period_deadline']}
        AND ppl_exit > {$herinfo['period_deadline']}
        JOIN $kldv.grp2subj ON grp2subj.grp_id = ppl2grp.grp_id 
	JOIN $kldv.year2grp2subj
	ON year2grp2subj.grp2subj_id = grp2subj.grp2subj_id
	AND year2grp2subj.year_id = {$herinfo['year_id']}  AND grp2subj_enter <= {$herinfo['period_deadline']} AND grp2subj_exit > {$herinfo['period_deadline']}
	JOIN (
		SELECT grp2subj_id, GROUP_CONCAT(UPPER(ppl_login) ORDER BY ppl_login) docs FROM $kldv.ppl2grp2subj JOIN $kldv.ppl AS doc USING (ppl_id)
		WHERE ppl_enter <= {$herinfo['period_deadline']} AND ppl_exit > {$herinfo['period_deadline']}
		GROUP BY grp2subj_id
	) AS docs ON docs.grp2subj_id = grp2subj.grp2subj_id
	JOIN rex
	ON rex.ppl_id = ppl2grp.ppl_id
	AND period_id = {$herinfo['period_id']}
	AND rex.subj_id  = grp2subj.subj_id
	JOIN $kldv.subj ON grp2subj.subj_id = subj.subj_id
	JOIN $kldv.ppl ON rex.ppl_id = ppl.ppl_id
	JOIN $kldv.ppl2grp AS ppl2stamklas
	ON ppl2stamklas.ppl_id = ppl.ppl_id
        AND ppl2stamklas.ppl_enter <= {$herinfo['period_deadline']}
        AND ppl2stamklas.ppl_exit > {$herinfo['period_deadline']}
	JOIN (
		SELECT * FROM $kldv.grp WHERE grp_home = 1
	) AS stamklassen ON stamklassen.grp_id = ppl2stamklas.grp_id
        -- GROUP BY grp_id, pta_id
) AS bla USING (grp_id, pta_id)
WHERE rooster_id = $rooster_id$where
ORDER BY date, tijd, lok$sort_activiteit$sort_leerling
EOQ
, $_GET['moment']);

if (!($rooster2->numRows())) {
	error_user('Geen gegevens beschikbaar', 'Er zijn geen lln die voldoen aan de gegeven criteria');
}

$year = mdb2_single_val($db,
        "SELECT year_long FROM $kldv.years WHERE year_id = %i", $herinfo['year_id']);
$header = $herinfo['period_desc'].' '.$year;

$row = $rooster2->fetchRow(MDB2_FETCHMODE_ASSOC);

$out = array();

$date = $row['dag'];
$tijd = $row['tijd'];
$start = $row['start'];
$end = $row['end'];
$surv = $row['surv'];
$lok = $row['lokaal'];
$lln = array();

$first = true;

do {
	if ($date != $row['dag'] || $lok != $row['lokaal'] || $tijd != $row['tijd'] || $surv != $row['surv']) {
		$out[] = array(
			'surv' => $surv,
			'lijst' => $lln,
			'lokaal' => $lok,
			'date' => $date,
			'tijd' => $tijd,
			'start' => $start,
			'end' => $end
		);
	
		$date = $row['dag'];
		$tijd = $row['tijd'];
		$start = $row['start'];
		$end = $row['end'];
		$surv = $row['surv'];
		$lok = $row['lokaal'];
		$lln = array();
	}
	$first = false;
	$lln[] = array(
		'llnr' => $row['llnr'],
		'activiteit' => $row['activiteit'],
		'naam' => $row['leerling'],
		'stamklas' => $row['stamklas'],
		'docs' => $row['docs'],
		'lgv' => $row['lgv']
	);
} while (($row = $rooster2->fetchRow(MDB2_FETCHMODE_ASSOC)));

if (!$first) {
$out[] = array(
	'surv' => $surv,
	'lijst' => $lln,
	'lokaal' => $lok,
	'date' => $date,
	'tijd' => $tijd,
	'start' => $start,
	'end' => $end
);
}

$a3 = false;
$landscape = false;
$toetsweek = true;
$lege_hokjes = 0;
$gewenst_aantal_lijsten = 1;

?>
<!DOCTYPE html>
<html translate="no">
<head>
<meta name="google" content="notranslate">
<meta charset="UTF-8">
<title>Presentielijsten voor <?=$header?></title>
<style>
body {
        font-family: 'Times New Roman', Times, serif;
        font-size: 0.33cm;
}
.footer {
        font-size: 0.3cm;
}
div.header {
        font-size: 0.7cm;
        font-family: Arial, Helvetica, sans-serif;
        font-weight: bold;
        text-align: center;
}
div.small {
        font-size: 0.3cm;
        font-family: Arial, Helvetica, sans-serif;
        font-weight: normal;
        width: 100%;
}
table.frontje {
        font-size: 0.63cm;
        font-family: Arial, Helvetica, sans-serif;
        font-weight: bold;
        width: 100%;
        table-layout: fixed;
        border-collapse: collapse;
        overflow: hidden;
        border: 0.2cm solid black;
}
tr > table.frontje {
        vertical-align: top;
}
table.frontje * td, table.frontje * th {
        padding-top: 5.1mm;
        padding-bottom: 5.1mm;
        padding-left: 3mm;
        padding-right: 3mm;
        border: 0.2cm solid black;
        width: 65%;
}
table.frontje * td:first-child, table.frontje * th:first-child {
        padding-top: 5.1mm;
        padding-bottom: 5.1mm;
        padding-left: 3mm;
        padding-right: 3mm;
        border: 0.2cm solid black;
        width: 35%;
}

table.klassenlijst {
        font-size: <? if ($landscape && !$a3) { ?>0.19cm<? } else { ?>0.33cm<? } ?>;
        width: 100%;
        border-collapse: collapse;
        border: 1px solid black;
}
tr > table.klassenlijst {
        vertical-align: top;
}
table.klassenlijst * td, table.klassenlijst * th {
        padding-top: 1.1mm;
        padding-bottom: 1.1mm;
        padding-left: 1mm;
        padding-right: 1mm;
        border: 1px solid black;
}
th.nr {
        width: <? if ($landscape && !$a3) { ?>2%<? } else { ?>3%<? } ?>;
}
table.klassenlijst * td:first-child {
        text-align: right;
        left: 0.5cm;
}
th.naam {
        width: <? if ($landscape) { ?>25%<? } else { ?>40%<? } ?>;
}
th.bijz {
        width: 45%;
}
th.present {
        width: 4%;
}
th.tijd {
        width: 10%;
}
</style>
<style media="screen">
body {
        background-color: #E6E6FA;
}
div.page {
        background-color: white;
        padding: 1.5cm;
<? if ($landscape && !$a3) { ?>
        height: 180mm;
        width: 267mm;
<? } else if ($landscape && $a3) { ?>
        height: 267mm;
        width: 390mm;
<? } else { ?>
        width: 180mm;
        height: 267mm;
<? } ?>
        margin: .5cm auto;
        border: 1px solid;
        box-shadow: 10px 10px 5px #888888;
}
</style>
<style media="print">
@page {
        size: <? if ($a3) { ?>a3<? } else { ?>a4<? } ?> <? if ($landscape) { ?>landscape<? } else { ?>portrait<? } ?>;
        margin: 1.5cm;
}
div.page+div.page {
        page-break-before: always;
}
.hint {
        display: none;
}
</style>
</head>
<body>
<? //$hers->seek(); mdb2_res_table($hers); ?>
<div class="hint" style="text-align: center;"><button onclick="window.print()">Print</button> Wil je enkelzijdig printen? Let dan op je printerinstellingen.'.</div>
<? foreach ($out as $elt) { ?>
<div style="contain: size" class="page">
<h4><?=$herinfo['period_desc']?> <?=$herinfo['year_long']?> <?=$elt['date']?> <?=$elt['start']?>-<?=$elt['end']?> <?=$elt['lokaal']?>/<?=$elt['surv']?></h4>
<p><table class="klassenlijst">
<? if (isset($_GET['gdpr'])) { ?>
<tr><th class="nr">nr</th><th>llnr</th><th>stamklas</th><th>lesgroep</th><th>vakdocent(en)</th><th>toets</th></tr>
<? } else { ?>
<tr><th class="nr">nr</th><th class="naam">naam (llnr)</th><th>grp/doc</th><th>toets</th><th>present</th></tr>
<? } ?>
<? $i = 0; foreach ($elt['lijst'] as $ll) { ?>
<? if (isset($_GET['gdpr'])) { ?>
<tr><td><?=++$i?></td><td><?=$ll['llnr']?></td><td><?=$ll['stamklas']?></td><td><?=$ll['lgv']?></td><td><?=$ll['docs']?></td><td><?=$ll['activiteit']?></td></tr>
<? } else { ?>
<tr><td><?=++$i?></td><td><?=$ll['naam']?></td><td><?=$ll['lgv']?>/<?=$ll['docs']?></td><td><?=$ll['activiteit']?></td><td></td></tr>
<? } ?>
<? } ?>
</table>
<span class="footer">Gebaseerd op herkansingsrooster versie <?=$rooster_info['rooster_version']?> (<?=$rooster_info['rooster_timestamp']?>)<?
 ?>.</span>
</div>
<? } ?>
</body>
</html>