<?php include('mod/_mod_security.php'); ?>
<?php
	$criteria = '';
	$filter = '';
	if (!empty($_POST['hvr_date_start']) && is_date($_POST['hvr_date_start'])) :
		$criteria .= " AND v.create_date >= " . nzdate($_POST['hvr_date_start']) . " ";
		$filter .= '<br> Last verification start date: ' . $_POST['hvr_date_start'];
	endif;
	if (!empty($_POST['hvr_date_end']) && is_date($_POST['hvr_date_end'])) :
		$criteria .= " AND v.create_date <= " . nzdatetime($_POST['hvr_date_end'] . ' 23:59:59') . " ";
		$filter .= '<br> Last verification end date: ' . $_POST['hvr_date_end'];
	endif;
	if (!empty($_POST['hvr_neighborhood'])) :
		$criteria .= " AND h.neighborhood_id = " . nz($_POST['hvr_neighborhood'], '0') . " ";
		$filter .= '<br> Neighborhood: ' . lookup_db_field('neighborhoods', 'neighborhood_id', $_POST['hvr_neighborhood'], 'neighborhood_name');
	endif;
	if (!empty($_POST['hvr_verifier'])) :
		$criteria .= " AND v.create_by = " . nz($_POST['hvr_verifier'], '0') . " ";
		$filter .= '<br> Verifier: ' . lookup_db_field('sys_users', 'user_id', $_POST['hvr_verifier'], 'full_name');
	endif;
	$sql = "SELECT h.hotel_name, h.bldg_nbr, h.street, h.city, h.state, h.zip, n.neighborhood_name, v.*, MAX(v.create_date) AS verify_date, MAX(v3.update_date) AS previous_verify_date, su.full_name AS verifier, MAX(v3.create_date) AS prev_verify_date,
				CASE
					WHEN v.magazines_delivered = 1 THEN 'Y'
					WHEN v.magazines_delivered = 0 THEN 'N'
					WHEN v.magazines_delivered = 2 THEN 'NR'
				END AS delivery_status
				FROM hotels h 
				INNER JOIN verifications v ON (v.hotel_id = h.hotel_id AND v.verification_id = (
						SELECT MAX(v2.verification_id) FROM verifications v2 WHERE v2.hotel_id = h.hotel_id
					))
				INNER JOIN sys_users su ON (v.create_by = su.user_id)
				LEFT JOIN verifications v3 ON (v3.hotel_id = h.hotel_id AND v3.verification_id = (
						SELECT MAX(v4.verification_id) FROM verifications v4 WHERE v4.hotel_id = h.hotel_id AND
						v4.verification_id < (SELECT MAX(v5.verification_id) FROM verifications v5 WHERE v5.hotel_id = h.hotel_id)
					))
				LEFT JOIN neighborhoods n ON (h.neighborhood_id = n.neighborhood_id) 
				WHERE h.status = 1
				$criteria
				GROUP BY h.hotel_name, h.neighborhood_id
				ORDER BY h.hotel_name, h.hotel_id
	";
	//error_log($sql);
	$result = $db->query($sql) or die('Database Error!');
	$row_count = $result->rowCount();
	if ($row_count <= 0) :
		echo 'No data for report!';
		exit();
	endif;
	
	$rpt = get_report_class($_POST['output_format']);
	$rpt->set_report_title('Hotel Verification Report');
	$rpt->report_start();
	
	$rpt->header_start();
	if (!empty($filter)) :
		$rpt->header_row_start();
		$rpt->header_cell_add('Filters: '.$filter, ['colspan'=>'14', 'class'=>'large', 'align'=>'left']);
		$rpt->header_row_end();
	endif;
	$rpt->header_row_start();
	$rpt->header_cell_add('Hotel', ['width'=>'70']);
	$rpt->header_cell_add('Neighborhood', ['width'=>'70']);
	$rpt->header_cell_add('Bldg #', ['width'=>'20']);
	$rpt->header_cell_add('Street', ['width'=>'40']);
	$rpt->header_cell_add('City', ['width'=>'50']);
	$rpt->header_cell_add('State', ['width'=>'20']);
	$rpt->header_cell_add('Zip', ['width'=>'15']);
	$rpt->header_cell_add('Delivered', ['width'=>'70']);
	$rpt->header_cell_add('Rem Qty', ['width'=>'70']);
	$rpt->header_cell_add('Spoke To', ['width'=>'70']);
	$rpt->header_cell_add('Occupancy', ['width'=>'70']);
	$rpt->header_cell_add('Verifier', ['width'=>'70']);
	$rpt->header_cell_add('Last Verify Date', ['width'=>'70']);
	$rpt->header_cell_add('Prev Verify Date', ['width'=>'70']);
	$rpt->header_row_end();
	$rpt->header_end();
	
	$rpt->data_start();
	$gt = [];
	$vt = [];
	while($row = $result->fetch(PDO::FETCH_OBJ)) :
		$rpt->data_row_start();
		$rpt->data_cell_add($row->hotel_name);
		$rpt->data_cell_add($row->neighborhood_name);
		$rpt->data_cell_add($row->bldg_nbr);
		$rpt->data_cell_add($row->street);
		$rpt->data_cell_add($row->city);
		$rpt->data_cell_add($row->state);
		$rpt->data_cell_add($row->zip);
		$rpt->data_cell_add($row->delivery_status);
		$rpt->data_cell_add($row->remaining_qty, ['align'=>'right']);
		$rpt->data_cell_add($row->spoke_to);
		$rpt->data_cell_add($row->occupancy, ['align'=>'right']);
		$rpt->data_cell_add($row->verifier);
		$rpt->data_cell_add(nzdate_display($row->verify_date));
		$rpt->data_cell_add(nzdate_display($row->prev_verify_date));
		$gt['Total'] += 1;
		$gt[$row->delivery_status] += 1;
		$vt[$row->verifier]['Total'] += 1;
		$vt[$row->verifier][$row->delivery_status] += 1;
		$rpt->data_row_end();
	endwhile;
	$rpt->data_row_start();
	$rpt->data_cell_add('Totals', ['colspan'=>14, 'align'=>'center', 'class'=>'large']);
	$rpt->data_row_end();
	$rpt->data_row_start();
	$rpt->data_cell_add('By Verifier', ['colspan'=>5, 'align'=>'center', 'class'=>'large']);
	$rpt->data_row_end();
	foreach ($vt as $key => $value) :
		$rpt->data_row_start();
		$rpt->data_cell_add('');
		$rpt->data_cell_add($key, ['colspan'=>4]);
		$rpt->data_row_end();
		foreach ($value as $key2 => $value2) :
			$rpt->data_row_start();
			$rpt->data_cell_add('', ['colspan'=>2]);
			$rpt->data_cell_add($key2);
			$rpt->data_cell_add(number_format($value2, 0), ['align'=>'right']);
			$rpt->data_cell_add(number_format($value2 / $value['Total'] * 100, 2) . '%', ['align'=>'right']);
			$rpt->data_row_end();
		endforeach;
	endforeach;
	$rpt->data_row_start();
	$rpt->data_cell_add('Grand Totals', ['colspan'=>4, 'align'=>'center', 'class'=>'large']);
	$rpt->data_row_end();
	foreach ($gt as $key => $value) :
		$rpt->data_row_start();
		$rpt->data_cell_add('', ['colspan'=>1]);
		$rpt->data_cell_add($key);
		$rpt->data_cell_add(number_format($value, 0), ['align'=>'right']);
		$rpt->data_cell_add(number_format($value / $gt['Total'] * 100, 2) . '%', ['align'=>'right']);
		$rpt->data_row_end();
	endforeach;
	$rpt->data_end();
	
	$rpt->report_end();
	$rpt->output_report();

?>