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

check_admin();

$db = mdb2_open($dsn);

/*
$res = mdb2_query($db, <<<EOQ
SELECT grp_name stamklas, 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)
LEFT JOIN quotes USING (ppl_id)
LEFT JOIN photos USING (ppl_id)
-- WHERE quote_id IS NULL OR photo_id IS NULL OR quote = ''
ORDER BY grp_name, ppl_surname, ppl_forename, ppl_prefix
EOQ
);
 */

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


mdb2_res_export_excel($res);

logit("excel export klaar, nu zip file schrijven naar {$data}export.zip");
$zip = new ZipArchive();
$zipfile = $data.'export.zip';
unlink($zipfile);

if ($zip->open($zipfile, ZipArchive::CREATE) !== true) {
	error_sytem("cannot create $zipfile");
}

$res->seek(); // reset row pointe

while (($row = $res->fetchRow(MDB2_FETCHMODE_ASSOC))) {
	if ($row['sha256'] == '') continue;
	switch ($row['mimetype']) {
	case 'image/jpeg':
		$ext = 'jpeg';
		break;
	case 'image/png':
		$ext = 'png';
		break;
	case 'image/webp':
		$ext = 'webp';
		break;
	default:
		print_r($row);
		error_system("unsupported mimetype {$row['mimetype']}");
	}
	$name = $row['mentorgroep'].' '.$row['stamklas'].' '.$row['onlynaam'].' '.$row['ppl_login'].'.'.$ext;

	//echo($name."\n");
	if ($zip->addFile($data.$row['sha256'], $name) == FALSE) error_system("error adding $name to zip");
}

$zip->close();