<?php include('mod/_mod_security.php'); ?>
<?php
	$criteria = '';
	$filter = '';
	if (!empty($_POST['hsr_date_start']) && is_date($_POST['hsr_date_start'])) :
		$criteria .= " AND c.update_date >= " . nzdate($_POST['hsr_date_start']) . " ";
		$filter .= '<br> Updated start date: ' . $_POST['hsr_date_start'];
	endif;
	if (!empty($_POST['hsr_date_end']) && is_date($_POST['hsr_date_end'])) :
		$criteria .= " AND c.update_date <= " . nzdatetime($_POST['hsr_date_end'] . ' 23:59:59') . " ";
		$filter .= '<br> Updated end date: ' . $_POST['hsr_date_end'];
	endif;
	if (!empty($_POST['hsr_neighborhood'])) :
		$criteria .= " AND h.neighborhood_id = " . nz($_POST['hsr_neighborhood'], '0') . " ";
		$filter .= '<br> Neighborhood: ' . lookup_db_field('neighborhoods', 'neighborhood_id', $_POST['hsr_neighborhood'], 'neighborhood_name');
	endif;
	if (!empty($_POST['hsr_route'])) :
		$criteria .= " AND h.route = " . $db->quote($_POST['hsr_route']) . " ";
		$filter .= '<br> Route: ' . $_POST['hsr_route'];
	endif;
	if (!empty($_POST['hsr_category'])) :
		$criteria .= " AND h.category = " . $db->quote($_POST['hsr_category']) . " ";
		$filter .= '<br> Hotel category: ' . $_POST['hsr_category'];
	endif;
	if (!empty($_POST['hsr_zip'])) :
		$criteria .= " AND h.zip = " . $db->quote($_POST['hsr_zip']) . " ";
		$filter .= '<br> Zip Code: ' . $_POST['hsr_zip'];
	endif;
	if (!empty($_POST['hsr_sort'])) :
		$sort_criteria .= " " . trim($db->quote($_POST['hsr_sort']), "'") . " ";
		//$filter .= '<br> Sort by: ' . $_POST['hsr_sort'];
	else :
		$sort_criteria = " h.hotel_name, h.hotel_id ";
	endif;
	$sql = "SELECT h.*, n.neighborhood_name, COUNT(c.concierge_id) AS concierge_count
				FROM hotels h 
				LEFT JOIN concierges c ON (h.hotel_id = c.hotel_id AND c.status = 1) 
				LEFT JOIN neighborhoods n ON (h.neighborhood_id = n.neighborhood_id) 
				WHERE h.status = 1
				$criteria
				GROUP BY h.hotel_id
				ORDER BY $sort_criteria
	";
	//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 Status Report');
	$rpt->report_start();
	
	$rpt->header_start();
	if (!empty($filter)) :
		$rpt->header_row_start();
		$rpt->header_cell_add('Filters: '.$filter, ['colspan'=>'12', '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('Route', ['width'=>'20']);
	$rpt->header_cell_add('Category', ['width'=>'20']);
	$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('# Rooms', ['width'=>'10']);
	$rpt->header_cell_add('Last Updated', ['width'=>'70']);
	$rpt->header_cell_add('# Active Concierges', ['width'=>'20']);
	$rpt->header_row_end();
	$rpt->header_end();
	
	$rpt->data_start();
	$tot_hotel_count = 0;
	$tot_concierge_count = 0;
	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->route);
		$rpt->data_cell_add($row->category);
		$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->nbr_of_rooms, ['align'=>'right']);
		$rpt->data_cell_add(nzdate_display($row->update_date));
		$rpt->data_cell_add($row->concierge_count, ['align'=>'right']);
		$tot_hotel_count += 1;
		$tot_concierge_count += $row->concierge_count;
		$rpt->data_row_end();
	endwhile;
	$rpt->data_row_start();
	$rpt->data_cell_add('Totals', ['colspan'=>12, 'align'=>'center']);
	$rpt->data_row_end();
	$rpt->data_row_start();
	$rpt->data_cell_add('Total hotels', ['colspan'=>3, 'align'=>'right']);
	$rpt->data_cell_add(number_format($tot_hotel_count, 0), ['align'=>'right']);
	$rpt->data_row_end();
	$rpt->data_row_start();
	$rpt->data_cell_add('Total active concierges', ['colspan'=>3, 'align'=>'right']);
	$rpt->data_cell_add(number_format($tot_concierge_count, 0), ['align'=>'right']);
	$rpt->data_row_end();
	$rpt->data_end();
	
	$rpt->report_end();
	$rpt->output_report();

?>