<?php include('mod/_mod_security.php'); ?>
<?php
	$sql = "SELECT DISTINCT ".
			"(SELECT GROUP_CONCAT(DISTINCT et.event_type SEPARATOR '/') from event_types et WHERE et.event_type_id = e.event_type_1 OR et.event_type_id = e.event_type_2 ORDER BY et.event_type) AS venue_type ".
			"FROM events e INNER JOIN events_concierges ec ON e.event_id = ec.event_id INNER JOIN concierges c ON c.concierge_id = ec.concierge_id LEFT OUTER JOIN hotels h ON c.hotel_id = h.hotel_id ".
			"WHERE (ec.rsvp_1 = 1 OR ec.rsvp_2 = 1) AND event_date_time >= ".nzdate($_GET['date_start'])." AND DATE(event_date_time) <= ".nzdate($_GET['date_end'])." ".
			"ORDER BY (SELECT GROUP_CONCAT(DISTINCT et.event_type SEPARATOR '/') from event_types et WHERE et.event_type_id = e.event_type_1 OR et.event_type_id = e.event_type_2 ORDER BY et.event_type) ASC ";
	$result_prep = $db->query($sql) or die('Database Error!');
	$sql_crosstab = " ,CONVERT(SUM(1), SIGNED) AS total ";
	while($row_prep = $result_prep->fetch(PDO::FETCH_OBJ)) :
		$sql_crosstab .= " ,CONVERT(SUM(IF((SELECT GROUP_CONCAT(DISTINCT et.event_type SEPARATOR '/') from event_types et WHERE et.event_type_id = e.event_type_1 OR et.event_type_id = e.event_type_2 ORDER BY et.event_type) = '".$row_prep->venue_type."',1,0)), SIGNED) AS `".$row_prep->venue_type."` "; 
	endwhile;
	/*
	$sql = "SELECT c.first_name, c.last_name, h.hotel_name, h.category, ".
			"(SELECT GROUP_CONCAT(DISTINCT et.event_type SEPARATOR '/') from event_types et WHERE et.event_type_id = e.event_type_1 OR et.event_type_id = e.event_type_2 ORDER BY et.event_type) AS venue_type ".
			"FROM events e INNER JOIN events_concierges ec ON e.event_id = ec.event_id INNER JOIN concierges c ON c.concierge_id = ec.concierge_id LEFT OUTER JOIN hotels h ON c.hotel_id = h.hotel_id ".
			"WHERE (ec.rsvp_1 = 1 OR ec.rsvp_2 = 1) AND event_date_time >= ".nzdate($_GET['date_start'])." AND DATE(event_date_time) <= ".nzdate($_GET['date_end'])." ".
			"ORDER BY c.first_name, c.last_name ";
	*/
	$sql = "SELECT c.first_name, c.last_name, h.hotel_name, h.category ".
			$sql_crosstab.
			"FROM events e INNER JOIN events_concierges ec ON e.event_id = ec.event_id INNER JOIN concierges c ON c.concierge_id = ec.concierge_id LEFT OUTER JOIN hotels h ON c.hotel_id = h.hotel_id ".
			"WHERE (ec.rsvp_1 = 1 OR ec.rsvp_2 = 1) AND event_date_time >= ".nzdate($_GET['date_start'])." AND DATE(event_date_time) <= ".nzdate($_GET['date_end'])." ".
			"GROUP BY c.first_name, c.last_name, h.hotel_name, h.category ";
	//echo " === ".$sql." === ";
	$result = $db->query($sql) or die('Database Error!');
	$margin_left = 0.5;
	$margin_right = 0.5;
	$margin_top = 1.0;
	$margin_bottom = 0.75;
	$margin_header = 0.5;
	$margin_footer = 0.5;
	if ($_GET['fmt'] == 'excel') :
		echo '<script>';
		echo "window.location.href = 'ajax.php?call=_export_excel&sql=".urlencode(sys_encrypt(sys_compress($sql), $_SESSION['rand_key']))."';";
		echo '</script>';
	endif;
?>