<?php include('mod/_mod_security.php'); ?>
<?php
	//var_dump($_GET);
	//exit;
	$search_str = '';
	$title_filter = '';
	if (!empty($_GET['hrv'])) :
		$search_str .= "  AND h.hotel_retail_visitor_center = ".$db->quote($_GET['hrv'])." ";
		$title_filter .= ' - HRV: '.$_GET['hrv'];
	endif;
	if (!empty($_GET['cs'])) :
		$search_str .= "  AND h.concierge_service = ".$db->quote($_GET['cs'])." ";
		$title_filter .= ' - Concierge Svc: '.$_GET['cs'];
	endif;
	if (!empty($_GET['category'])) :
		$search_str .= "  AND h.category = ".$db->quote($_GET['category'])." ";
		$title_filter .= ' - Category: '.$_GET['category'];
	endif;
	if (!empty($_GET['route'])) :
		$search_str .= "  AND h.route_id = ".nz($_GET['route'], '0')." ";
		$sql_route = "SELECT * from routes WHERE route_id = ".nz($_GET['route'], '0')." ";
		$result_route = $db->query($sql_route) or die('Database Error!');
		if ($result_route->rowCount() > 0) :
			$row_route = $result_route->fetch(PDO::FETCH_OBJ);
		else :
			die('Invalid route!');
		endif;
		$title_filter .= ' - Route: '.$row_route->route_code;
	endif;
	if (empty($title_filter)) :
		$title_filter = 'None';
	endif;
	$sql = "SELECT h.hotel_name, CONCAT(h.bldg_nbr, ' ', h.street) AS address, r.route, h.sequence_nbr, h.concierge_service, h.nbr_of_rooms, v.create_date AS verification_date, u.full_name, old_qty as draw ".
			//",(CASE v.change_draw WHEN 0 THEN '' WHEN 1 THEN new_qty END) AS new_draw ".
			",(CASE v.verification_type WHEN 1 THEN 'Phone' WHEN 2 THEN 'In Person' END) AS verification_type ".
			",(CASE v.magazines_delivered WHEN 0 THEN 'N' WHEN 1 THEN 'Y' WHEN 2 THEN 'NR' END) AS delivered ".
			",(CASE v.all_distributed WHEN 0 THEN 'N' WHEN 1 THEN 'Y' END) AS all_distributed ".
			",(CASE v.remaining_na WHEN 0 THEN v.remaining_qty WHEN 1 THEN 'N/A' ELSE v.remaining_qty END) AS rem_qty ".
			", p.description AS product, v.drop_location, v.spoke_to, v.comments ". 
			"FROM hotels h LEFT OUTER JOIN verifications v ON (v.hotel_id = h.hotel_id AND v.create_date = (SELECT MAX(create_date) FROM verifications v2 WHERE v2.hotel_id = v.hotel_id)) ".
			"LEFT OUTER JOIN sys_users u ON (v.create_by = u.user_id AND u.hidden <> 1) LEFT JOIN routes r ON (h.route_id = r.route_id) LEFT OUTER JOIN products p ON (v.product_id = p.product_id) ".
			"WHERE h.status = 1 ".$search_str.
			"ORDER BY h.hotel_name ";
	//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>';
	elseif ($_GET['fmt'] == 'pdf') :
		$mpdf = new \Mpdf\Mpdf([
			'format' => 'Letter-L',
			'margin_left' => $margin_left * 25.4,
			'margin_right' => $margin_right * 25.4,
			'margin_top' => $margin_top * 25.4,
			'margin_bottom' => $margin_bottom * 25.4,
			'margin_header' => $margin_header * 25.4,
			'margin_footer' => $margin_footer * 25.4,
		]);
		$mpdf->debug = false;
		$mpdf->keep_table_proportions = true;
		$mpdf->SetProtection(array('copy','print'));
		$mpdf->SetTitle("Master Hotel Database - Report");
		$mpdf->SetAuthor("Davler Media Group");
		$mpdf->SetHTMLHeader('<table width="100%" style="vertical-align: bottom; font-size: 11pt; color: #000000;"><tr>'.
							'<td width="25%" style="text-align: left;" valign="top">Last Verified Report</td>'.
							'<td width="75%" style="text-align: right;" valign="top">Filters: '.$title_user.$title_filter.'</td>'.
							'</tr></table><br /><br />');
		$mpdf->SetHTMLFooter('<table width="100%" style="vertical-align: bottom; font-family: serif; font-size: 8pt; color: #000000; font-weight: bold; font-style: italic;"><tr>'.
							'<td width="33%"><span style="font-weight: bold; font-style: italic;">{DATE m/j/Y}</span></td>'.
							'<td width="33%" align="center" style="font-weight: bold; font-style: italic;">Page {PAGENO} of {nbpg}</td>'.
							'<td width="33%" style="text-align: right; ">Davler Media Group</td>'.
							'</tr></table>');
		$mpdf->SetDisplayMode('fullpage');
		$html = '<html><body style="font-family: Arial; font-size: 11pt; line-height: 12pt; ">';
		$html .= '<table width="100%" style="vertical-align: top; text-align:left; font-size: 10pt; color: #000000; font-weight: normal; border-collapse:collapse;">';
		$html .= '<thead style="font-weight:bold;">';
		$thead_row = '<tr>';
		$thead_row .= '<td width="17%" align="center">Hotel Name<hr style="width:90%; height:1px; margin:0px;" /></td>';
		$thead_row .= '<td width="13%" align="center">Address<hr style="width:90%; height:1px; margin:0px;" /></td>';
		$thead_row .= '<td width="7%" align="center">Rooms<hr style="width:90%; height:1px; margin:0px;" /></td>';
		$thead_row .= '<td width="10%" align="center">Service<hr style="width:90%; height:1px; margin:0px;" /></td>';
		$thead_row .= '<td width="12%" align="center">Route<hr style="width:90%; height:1px; margin:0px;" /></td>';
		$thead_row .= '<td width="6%" align="center">Seq #<hr style="width:90%; height:1px; margin:0px;" /></td>';
		$thead_row .= '<td width="5%" align="center">&nbsp;</td>';
		$thead_row .= '<td width="11%" align="center">&nbsp;</td>';
		$thead_row .= '<td width="19%" align="center">&nbsp;</td>';
		$thead_row .= '</tr>';
		$thead_row .= '<tr>';
		$thead_row .= '<td align="center">Verification Date</td>';
		$thead_row .= '<td align="center">Full Name</td>';
		$thead_row .= '<td align="center">Type</td>';
		$thead_row .= '<td align="center">Delivered</td>';
		$thead_row .= '<td align="center">All Distributed</td>';
		$thead_row .= '<td align="center">Rem Qty</td>';
		$thead_row .= '<td align="center">Draw</td>';
		$thead_row .= '<td align="center">Spoke To</td>';
		$thead_row .= '<td align="center">Comments</td>';
		$thead_row .= '</tr>';
		$html .= $thead_row.'</thead><tbody>';
		$first_row = true;
		$verified = 0;
		$unverified = 0;
		while($row = $result->fetch(PDO::FETCH_OBJ)) :
			$calls = $calls + 1;
			if (empty($row->verification_date)) :
				$unverified = $unverified + 1;
			else :
				$verified = $verified + 1;
			endif;
			$html .= '<tr>';
			$html .= '<td style="border-top:1px solid #000000;">'.$row->hotel_name.'</td>';
			$html .= '<td style="border-top:1px solid #000000;">'.$row->address.'</td>';
			$html .= '<td style="border-top:1px solid #000000;" align="right">'.$row->nbr_of_rooms.'</td>';
			$html .= '<td style="border-top:1px solid #000000;">'.$row->concierge_service.'</td>';
			$html .= '<td style="border-top:1px solid #000000;">'.$row->route.'</td>';
			$html .= '<td style="border-top:1px solid #000000;" align="right">'.$row->sequence_nbr.'</td>';
			$html .= '<td style="border-top:1px solid #000000;">&nbsp;</td>';
			$html .= '<td style="border-top:1px solid #000000;">&nbsp;</td>';
			$html .= '<td style="border-top:1px solid #000000;">&nbsp;</td>';
			$html .= '</tr>';
			$html .= '<tr>';
			$html .= '<td>'.(empty($row->verification_date) ? '--UNVERIFIED--' : nzdate_display_datetime($row->verification_date)).'</td>';
			$html .= '<td>'.$row->full_name.'</td>';
			$html .= '<td>'.$row->verification_type.'</td>';
			$html .= '<td align="center">'.$row->delivered.'</td>';
			$html .= '<td align="center">'.$row->all_distributed.'</td>';
			$html .= '<td align="center">'.$row->rem_qty.'</td>';
			$html .= '<td align="right">'.$row->draw.'</td>';
			$html .= '<td>'.$row->spoke_to.'</td>';
			$html .= '<td>'.$row->comments.'</td>';
			$html .= '</tr>';
		endwhile;
		$html .= '<tr>';
		$html .= '<td>Total:</td>';
		$html .= '<td>'.$result->rowCount().'</td>';
		$html .= '<td>Verified:</td>';
		$html .= '<td>'.$verified.'</td>';
		$html .= '<td colspan="2">Unverified:</td>';
		$html .= '<td>'.$unverified.'</td>';
		$html .= '</tr>';
		$html .= '</tbody></table>';
		$html .= '</body></html>';
		$html = utf8_encode($html);
		$mpdf->WriteHTML($html);
		$mpdf->Output(); 
	endif;
?>