<?php include('mod/_mod_security.php'); ?>
<?php
	if ($_POST['hotel'] == '-All Hotels-') :
		$hotel_selector = '';
	else :
		//$hotel_selector = "(g.hotel = '".mysqli_real_escape_string($GLOBALS['con'], trim($_POST['hotel']))."' OR g2.hotel = '".mysqli_real_escape_string($GLOBALS['con'], trim($_POST['hotel']))."') AND ";
		$hotel_selector = "(g.hotel = '".mysqli_real_escape_string($GLOBALS['con'], trim($_POST['hotel']))."' ) AND ";
	endif;
	$sql = "SELECT g.id, g.last_name, g.hotel, SUM(IF(g2.rsvp_temple = 1 OR g2.rsvp_av = 1 OR g2.rsvp_kv = 1 OR g2.rsvp_bv = 1 OR g2.rsvp_dv = 1,1,0)) + IF(g.rsvp_temple = 1 OR g.rsvp_av = 1 OR g.rsvp_kv = 1 OR g.rsvp_bv = 1 OR g.rsvp_dv = 1,1,0) AS nbr_guests ".
			"FROM guests g LEFT OUTER JOIN guests g2 ON (g2.primary_guest_id = g.id AND g2.user_id = g.user_id AND g2.event_id = g.event_id) WHERE ".
			"( (g.guest_type = 'P' AND (g.rsvp_temple = 1 OR g.rsvp_av = 1 OR g.rsvp_kv = 1 OR g.rsvp_bv = 1 OR g.rsvp_dv = 1)) ".
			" OR (g.guest_type = 'C' AND (g.rsvp_temple = 1 OR g.rsvp_av = 1 OR g.rsvp_kv = 1 OR g.rsvp_bv = 1 OR g.rsvp_dv = 1) AND g.primary_guest_id = 0) ".
			" OR (g.guest_type = 'P' AND (g.rsvp_temple <> 1 AND g.rsvp_av <> 1 AND g.rsvp_kv <> 1 AND g.rsvp_bv <> 1 AND g.rsvp_dv <> 1) AND (SELECT COUNT(*) FROM guests g3 WHERE g3.primary_guest_id = g.id AND (g3.rsvp_temple = 1 OR g3.rsvp_av = 1 OR g3.rsvp_kv = 1 OR g3.rsvp_bv = 1 OR g3.rsvp_dv = 1) ) > 0) ) ".
			//"AND (g.hotel_stay = 1 OR g2.hotel_stay = 1) AND ".$hotel_selector.
			"AND (g.hotel_stay = 1) AND ".$hotel_selector.
			"g.user_id = ".$_SESSION['user_id']." AND g.event_id = ".$_SESSION['active_event']." GROUP BY g.id, g.last_name, g.hotel ORDER BY g.last_name ASC ";
	//echo "===".$sql."===";
	$result = mysqli_query($GLOBALS['con'], $sql) or die('Database error!');
	//$result = mysqli_query($GLOBALS['con'], $sql) or die(mysqli_error($GLOBALS['con']).' ==='.$sql.'===');
	if (mysqli_num_rows($result) <= 0) :
		echo 'No data for report!';
		exit();
	endif;
?>
<table align="center" cellpadding="0" cellspacing="2" border="0">
	<thead>
		<?php report_fixed_header(); ?>
		<tr class="page_header">
			<td colspan="4"style="font-size:18px;">
				<?php echo mysqli_real_escape_string($GLOBALS['con'], trim($_POST['hotel'])); ?>
			</td>
		</tr>
		<tr>
			<td width="120" align="center">
				Family Name
			</td>
			<td width="200" align="center">
				Family Members
			</td>
			<td width="120" align="center">
				Number of guests
			</td>
			<?php
				if ($hotel_selector == '') :
			?>
				<td width="150" align="center">
					Hotel
				</td>
			<?php
				endif;
			?>
		</tr>
	</thead>
	<tfoot>
		<?php report_fixed_footer(); ?>
	</tfoot>
	<tbody>
		<?php
			$total_guests = 0;
			$count_mambers = 0;
			$count_p = 0;
			$count_s = 0;
			$count_c = 0;
			$count_g = 0;
			while($row = mysqli_fetch_object($result)) :
				//$total_guests = $total_guests + $row->nbr_guests;
		?>
				<tr>
					<td>
						<?php echo $row->last_name; ?>
					</td>
					<td >
						<?php
							if ($_POST['hotel'] == '-All Hotels-') :
								$hotel_selector_sub = '';
							else :
								//$hotel_selector_sub = " AND (hotel = '".mysqli_real_escape_string($GLOBALS['con'], trim($_POST['hotel']))."' OR hotel = '' OR hotel IS NULL) ";
								$hotel_selector_sub = '';
							endif;
							$sql = "SELECT id, guest_type, hotel from guests WHERE id = ".$row->id." AND (rsvp_temple = 1 OR rsvp_av = 1 OR rsvp_kv = 1 OR rsvp_bv = 1 OR rsvp_dv = 1) UNION ".
									"SELECT id, guest_type, hotel from guests WHERE primary_guest_id = ".$row->id." ".$hotel_selector_sub." AND (rsvp_temple = 1 OR rsvp_av = 1 OR rsvp_kv = 1 OR rsvp_bv = 1 OR rsvp_dv = 1)";
							$result_family = mysqli_query($GLOBALS['con'], $sql) or die('Database error!');
							$hotel_fam = '';
							while($row_family = mysqli_fetch_object($result_family)) :
								if ($row_family->guest_type == 'C') :
									echo '<i>';
								endif;
								echo format_guest_name($row_family->id).'<br />'; 
								if ($row_family->guest_type == 'C') :
									echo '</i>';
								endif;
								switch ($row_family->guest_type) :
									case "P" :
										$count_p = $count_p + 1;
										$hotel_fam = $row->hotel;
										break;
									case "S" :
										$count_s = $count_s + 1;
										break;
									case "C" :
										$count_c = $count_c + 1;
										break;
									case "G" :
										$count_g = $count_g + 1;
										break;
								endswitch;
								if (empty($hotel_fam) && !empty($row_family->hotel)) :
									$hotel_fam = $row_family->hotel;
								endif;
							endwhile;
							$total_guests = $total_guests + mysqli_num_rows($result_family);
						?>
					</td>
					<td align="right">
						<?php /*  echo $row->nbr_guests; */ echo mysqli_num_rows($result_family); ?>
					</td>
					<?php
						if ($hotel_selector == '') :
					?>
						<td>
							<?php echo $row->hotel; /* echo $hotel_fam */ ?>
						</td>
					<?php
						endif;
					?>
				</tr>
		<?php
			endwhile;
		?>
		<tr>
			<td>
				Total families:&nbsp;&nbsp;<?php echo mysqli_num_rows($result); ?>
			</td>
			<td>&nbsp;
			</td>
			<td>
				Total guests:&nbsp;&nbsp;<?php echo $total_guests; ?><br />
				Primary - <?php echo $count_p; ?><br />
				Significant Other - <?php echo $count_s; ?><br />
				Child - <?php echo $count_c; ?><br />
				Gift Only - <?php echo $count_g; ?><br />
			</td>
			<?php
				if ($hotel_selector == '') :
			?>
				<td>&nbsp;
				</td>
			<?php
				endif;
			?>
		</tr>
	</tbody>
</table>