<?
require_once('phplib/phplib.php');
require_once('system.php');
require_once('db.php');
check_personeel();
$db = mdb2_open($dsn);
require_once('common.php');

$stuff = array(
	'schooljaar' => array('rel' => 'llsj'),
	'leerjaar' => array('rel' => 'llsj'),
	'opleiding' => array('rel' => 'llsj'),
	'examenjaar' => array( 'rel' => 'llsj', 'magnegeer' => true),
	'profiel' => array('rel' => 'pakket')
);

get_stuff($stuff);
$sgj = get_sgj($stuff);

$bril = array('select' => array (), 'concat' => array (), 'sum' => array(), 'join' => array (), 'group_by' => array ());

if (!isset($_GET['cenisentl'])) {
	$entliscen='';
	bril_add_vakken($bril, 'g_en', 'entl,cen');
} else {
	$entliscen='-entliscen';
}

$bril_type = get_bril_type();

bril_add($bril, $brillen[$bril_type]);

$bril_concat = implode(", '_', ", $bril['concat']);
$bril_select = implode(', ', $bril['select']);
$bril_group_by = implode(', ', $bril['group_by']);
$bril_sum = implode(' + ', $bril['sum']).' aantal';
$bril_join = implode("\n", $bril['join']);

$res = mdb2_query($db, <<<EOQ
SELECT COUNT(llsj_id) llj, COUNT(DISTINCT leerlingnummer) ll{$sgj['select']}, $bril_select, $bril_sum
FROM llsj
JOIN (
	SELECT llsj_id, eq_weergave AS leerlingnummer FROM llsj2eq
	JOIN eq USING (eq_id)
	JOIN eq_type USING (eq_type_id)
	WHERE eq_type = 'leerlingnummer'
) AS leerlingnummer USING (llsj_id)
JOIN (
	SELECT llsj_id, pakket_id FROM llsj2eq
	JOIN eq USING (eq_id)
	JOIN eq_type USING (eq_type_id)
	JOIN pakket2eq USING (eq_id)
	WHERE eq_type = 'pakket'
) AS pakket USING (llsj_id)
{$sgj['join']}
$bril_join
GROUP BY $bril_group_by{$sgj['group_by']}
ORDER BY COUNT(llsj_id) DESC
EOQ
);

mdb2_res_export_excel($res, 'export-'.get_hide_set($stuff).'-'.get_group_set($stuff).'-'.get_conditions_set($stuff).$entliscen.'.csv');


?>