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

check_admin();

$db = mdb2_open($dsn);

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

$period_id = $herinfo['period_id'];

if (isset($_GET['period_id'])) {
	$tail = "?period_id=$period_id";
}

header('Content-type: text/plain;charset="UTF-8"');
print_r($_POST);
print_r($_GET);

mdb2_exec($db, "INSERT INTO rooster_ids ( period_id, rooster_version ) SELECT  $period_id, IFNULL(MAX(rooster_version), 0) + 1 FROM rooster_ids WHERE period_id = $period_id");
$rooster_id = mdb2_single_val($db, "SELECT LAST_INSERT_ID()");
$info = mdb2_single_assoc($db, "SELECT * FROM rooster_ids WHERE rooster_id = $rooster_id");
print_r($info);

mdb2_exec($db, <<<EOQ
INSERT INTO roosterdetail ( rooster_id, moment_id, momentlocation_id, date, time_start, time_end, pta_id, grp_id, activiteit, lok, surv )
SELECT $rooster_id, moment_id, momentlocation_id, moment_date, moment_time_start, moment_time_end, pta_id, grp_id, toets, lok, surv FROM (
        SELECT moment_id, grp_id, pta_id, momentlocation_id,
                moment_date, moment_time_start, moment_time_end,
		PTA(pta_id) toets, grp_name lesgroep,
                COUNT(rex_id) aantal, momentlocation_lokaal lok, momentlocation_surveillant surv, 0 xtra
        FROM rooster
        JOIN moments USING (moment_id, period_id)
        JOIN ptas USING (pta_id)
        JOIN rex USING (period_id, pta_id)
        LEFT JOIN (
                SELECT grp.grp_id, ppl_id, subj_id, grp_name
                FROM $kldv.grp2subj
                JOIN $kldv.ppl2grp
                ON ppl2grp.grp_id = grp2subj.grp_id
                AND ppl2grp.ppl_enter <= {$herinfo['period_deadline']}
                AND ppl2grp.ppl_exit > {$herinfo['period_deadline']}
                JOIN $kldv.grp ON grp.grp_id = ppl2grp.grp_id
		JOIN $kldv.year2grp2subj ON year2grp2subj.grp2subj_id = grp2subj.grp2subj_id AND year_id = {$herinfo['year_id']} AND grp2subj_enter <= {$herinfo['period_deadline']} AND grp2subj_exit > {$herinfo['period_deadline']}
        ) AS grp ON grp.ppl_id = rex.ppl_id AND grp.subj_id = ptas.subj_id
        LEFT JOIN newdetailrooster USING (grp_id, rooster_id)
        LEFT JOIN momentlocations USING (momentlocation_id, moment_id)
        WHERE period_id = %i
        GROUP BY pta_id, grp_name
        UNION
        SELECT moment_id, NULL, NULL, NULL, moment_date, moment_time_start, moment_time_end,  IF(reserve_order = 0, 'gangwacht', CONCAT('reserve ', reserve_order)), '', NULL, '', reserve, reserve_order
        FROM reserves
        JOIN moments USING (moment_id)
        WHERE period_id = %i
) AS bla
ORDER BY moment_date, moment_time_start, IF(grp_id IS NULL, 1, 0), xtra, surv, toets, lesgroep
EOQ
, $period_id, $period_id);

mdb2_exec($db, "UPDATE rooster_ids SET rooster_visible = 1 WHERE rooster_id = $rooster_id");

$_SESSION['notice'][] = 'Success';
$_SESSION['notice'][] = "Rooster is gepubliceerd als versie {$info['rooster_version']}, het gepubliceerde rooster is zichtbaar op de envelopfrontjes, op de pagina voor de insgeschreven leerlingen en in het overzicht voor docenten (klik op 'home' en dan op 'herkansingsrooster')";

header("Location: roostervriend.php$tail");
?>