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

check_admin();

$db = mdb2_open($dsn);

$res = mdb2_query($db, <<<EOQ
SELECT examenstamklassen.grp_name stamklas, examenmentorgroepen.grp_name mentorgroep, examenmentorgroepen.docs,
	CONCAT(ppl_forename, IF(ppl_prefix != '', CONCAT(' ', ppl_prefix), ''), ' ', ppl_surname, ' (', ppl_login, ')') naam, quote,
	sha256, mimetype, ppl_login, CONCAT(ppl_surname, ', ', ppl_forename, IF(ppl_prefix != '', CONCAT(' ', ppl_prefix), '')) onlynaam,
	toestemming
FROM $kldv.ppl
JOIN (
	SELECT *
	FROM $kldv.ppl2grp
	WHERE ppl_enter <= UNIX_TIMESTAMP()
	AND ppl_exit > UNIX_TIMESTAMP()
) AS filtered USING (ppl_id)
JOIN (
	SELECT * FROM $kldv.grp WHERE grp_home = 1 AND ( SUBSTRING(grp_name, 1, 2) = '5H' OR SUBSTRING(grp_name, 1, 2) = '6V' OR SUBSTRING(grp_name, 1, 2) = '4M')
) AS examenstamklassen USING (grp_id)
JOIN (
	SELECT *
	FROM $kldv.ppl2grp
	WHERE ppl_enter <= UNIX_TIMESTAMP()
        AND ppl_exit > UNIX_TIMESTAMP()
) AS filtered2 USING (ppl_id)
JOIN (
	SELECT * FROM $kldv.grp
	JOIN $kldv.grp2subj USING (grp_id)
	JOIN (
		SELECT grp2subj_id FROM $kldv.year2grp2subj WHERE year_id = $year_id
	) AS yr USING (grp2subj_id)
	JOIN (
		SELECT subj_id FROM $kldv.subj WHERE subj_abbrev = 'mt'
	) AS mt USING (subj_id)
	JOIN (
		SELECT grp2subj_id, GROUP_CONCAT(ppl_login) docs FROM $kldv.ppl2grp2subj JOIN $kldv.ppl USING (ppl_id) WHERE ppl_enter <= UNIX_TIMESTAMP() AND ppl_exit > UNIX_TIMESTAMP() GROUP BY grp2subj_id
	) AS docs USING (grp2subj_id)
) AS examenmentorgroepen ON filtered2.grp_id = examenmentorgroepen.grp_id
LEFT JOIN quotes USING (ppl_id)
LEFT JOIN photos USING (ppl_id)
ORDER BY examenstamklassen.grp_name, ppl_surname, ppl_forename, ppl_prefix
EOQ
);


html_start();
mdb2_res_table($res);
html_end();