<?php include('mod/_mod_security.php'); ?>
<?php
	$sql = "SELECT g.id, g.last_name, COUNT(g2.primary_guest_id) + 1 AS family_size FROM guests g LEFT JOIN guests g2 ON (g.id = g2.primary_guest_id AND g.user_id = g2.user_id AND g.event_id = g2.event_id) WHERE ".
			" (g.guest_type = 'P' OR (g.guest_type = 'C' AND g.primary_guest_id = 0)) AND ".
			"g.user_id = ".$_SESSION['user_id']." AND g.event_id = ".$_SESSION['active_event']." GROUP BY g.last_name, g.id ORDER BY g.last_name ASC ";
	$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>
			<td width="120" align="center">
				Last (Family) Name
			</td>
			<td width="200" align="center">
				Family Members
			</td>
			<td width="120" align="center">
				Count
			</td>
		</tr>
	</thead>
	<tfoot>
		<?php report_fixed_footer(); ?>
	</tfoot>
	<tbody>
		<?php
			$count_mambers = 0;
			$count_p = 0;
			$count_s = 0;
			$count_c = 0;
			$count_g = 0;
			while($row = mysqli_fetch_object($result)) :
				$count_members = $count_members + $row->family_size;
		?>
				<tr>
					<td>
						<?php echo $row->last_name; ?>
					</td>
					<td >
						<?php
							$sql = "SELECT id, guest_type from guests WHERE id = ".$row->id." UNION SELECT id, guest_type from guests WHERE primary_guest_id = ".$row->id;
							$result_family = mysqli_query($GLOBALS['con'], $sql) or die('Database error!');
							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;
										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;
							endwhile;
						?>
					</td>
					<td align="right">
						<?php echo $row->family_size; ?>
					</td>
				</tr>
		<?php
			endwhile;
		?>
		<tr>
			<td>
				Total Families:&nbsp;&nbsp;<?php echo mysqli_num_rows($result); ?>
			</td>
			<td>&nbsp;
			</td>
			<td>
				Total Family Members:&nbsp;&nbsp;<?php echo $count_members; ?><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>
		</tr>
	</tbody>
</table>