#!/usr/bin/php
<?
function apache_note() {
}

$year_id = 8; // year_id from kldv.years
require_once('common.php');
require_once('phplib/phplib.php');

$db = mdb2_open($dsn);

$peildatum = strtotime('2020-03-01 17:00 CET');

$res = mdb2_query($db, <<<EOQ
SELECT ppl_login llnr, cat, CONCAT(ppl_forename, IF(ppl_prefix = '', '', CONCAT(' ', ppl_prefix)), ' ', ppl_surname) name, COUNT(st), GROUP_CONCAT(st ORDER BY st) FROM (
	SELECT CONCAT(((pta_period - 1) DIV 3) + 4, pta_target) cat, CONCAT(subj_abbrev, ' TP', ((pta_period - 1) MOD 3) + 1, ' ', stvt, IFNULL(shortdesc, '')) st, grp_name, grp_id
	FROM ptas
	JOIN $kldv.subj USING (subj_id)
	JOIN $kldv.grp2subj USING (subj_id)
	JOIN $kldv.year2grp2subj USING (year_id, grp2subj_id)
	JOIN $kldv.grp USING (grp_id)
	WHERE year_id = 8
	AND ((pta_target = 'V' AND pta_period >= 7 AND pta_period <= 9)
		 OR (pta_target = 'H' AND pta_period >= 4 AND pta_period <= 6))
	AND CONCAT(((pta_period - 1) DIV 3) + 4, pta_target) =
		SUBSTRING(grp_name, 1, 2)
) bla
JOIN $kldv.ppl2grp ON ppl2grp.grp_id = bla.grp_id
AND ppl2grp.ppl_enter <= $peildatum
AND ppl2grp.ppl_exit > $peildatum
JOIN $kldv.ppl USING (ppl_id)
GROUP BY ppl_login
ORDER BY cat, ppl_surname, ppl_forename, ppl_prefix
EOQ
);

$count = 0;
while (($row = $res->fetchRow(MDB2_FETCHMODE_ASSOC))) {
	$count++;
	//print_r($row);
	echo(implode("\t", $row)."\n");

}
//echo($peildatum."\n");
//echo($count."\n");

?>