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

check_personeel();

$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'];
}

$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");

//print_r($rooster_info);
//print_r($herinfo);
//exit;

//$res = mdb2_query($db, <<<EOQ
mdb2_export_excel($db, 'verheuvelexport-her-TP'.$herinfo['period_num'].'-'.substr($herinfo['year_long'], 2, 2).substr($herinfo['year_long'], 7, 2).'-v'.$rooster_info['rooster_version'].'-'.date("YmdHis").'.csv', <<<EOQ
SELECT date, CONCAT(TIME_FORMAT(time_start, '%%H:%%i'), ' - ', TIME_FORMAT(time_end, '%%H:%%i')) tijd, activiteit, IFNULL(grp_name, '-') grp, IFNULL(lln, '-') lln, IFNULL(IF(lok = '', '-', UPPER(lok)), '-') lok, IFNULL(IF(surv = '', '-', surv), '-') surv FROM roosterdetail
LEFT JOIN ptas USING (pta_id)
LEFT JOIN (
	SELECT grp.grp_id, rex.pta_id, grp_name, COUNT(rex_id) lln
	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 rex ON rex.ppl_id = ppl2grp.ppl_id AND period_id = {$herinfo['period_id']} AND rex.subj_id  = grp2subj.subj_id
	GROUP BY grp_id, pta_id
) AS bla USING (grp_id, pta_id)
WHERE rooster_id = $rooster_id 
ORDER BY date, tijd, CASE WHEN grp_id = 0 THEN 1 ELSE 0 END, pta_target, activiteit, lok
EOQ
);
?>