###################################START ALL THE POSSIBLE COMBINATIONS####################################
	
// When the STATUS of the form is "SEARCH ALL"
if(isset($_POST['form']['criteria'][0])){
 
	//assign shortcuts to variables
	$criteria = $_POST['form']['criteria'][0];
	$keyword = $_POST['form']['keyword'];
	$to = $_POST['form']['to'];
	$from = $_POST['form']['from'];
	$status = $_POST['form']['status'][0];
 
if ($status == '0')
{
	if (empty($keyword))
	{
		//No keywords input, so only search criteria is the date
		if(isset($from)&&($to))
		{
			//SQL to select ALL from DATE RANGE only
			########################################################
			########## START CUSTOMIZED SQL ########################
				
				//to show ALL EXHIBITING we need to add three weeks to today's date. We assume the user will not think to do this.
				$userdate = date_create($to);
				date_modify($userdate, '+3 weeks');
				$exhibit_to = date_format($userdate, 'Y-m-d');
 
 
	$db =& JFactory::getDBO(); 
	$db->setQuery("
	SELECT d.apno, d.address, d.dp, d.description, d.exhibit_to, d.determined, COALESCE( d.exhibit_to, d.determined ) AS date , c.title
					FROM #__devapps AS d, #__categories AS c 
					WHERE 
						(SELECT (d.exhibit_to BETWEEN '$from' AND '$exhibit_to')
						OR (d.determined BETWEEN '$from' AND '$to'))
					AND d.catid = c.id
					AND c.extension = 'com_devapps'
					ORDER BY date DESC");
 
		if (!$db->query()) {
			JError::raiseError(500, $db->getErrorMsg());
		}else{
			$result = $db->loadObjectList(); 
		}
        if($result){
		$thankYouMessage .= '<h2>Search results:</h2>
        <h4>For further information please contact the Environmental Services Department on 6549 3745. </h4>
<table class="mcs" border="0" cellpadding="0" cellspacing="0" width="100%">
<tbody><tr>
<th>App. no</th>
<th>Description of land</th>
<th>DP</th>
<th width="30%">Work proposed</th>
<th>Status</th>
</tr>';
$today = date("Y-m-d");
foreach ($result as $r)
if  (  ( $r->title == 'Exhibiting') &&  ($today > $r->exhibit_to )  ){
$thankYouMessage .= '<tr><td>'. $r->apno . '</td><td>' . $r->address . '</td><td>' . $r->dp . '</td><td>' . $r->description. '</td><td>Awaiting determination</td></tr>';
} elseif (( $r->title != 'Exhibiting')) {
$thankYouMessage .= '<tr><td>'. $r->apno . '</td><td>' . $r->address . '</td><td>' . $r->dp . '</td><td>' . $r->description. '</td><td>'.  $r->title. ' ' . $datestatment .' '.  $r->determined. '</td></tr>';
} else {
$thankYouMessage .= '<tr><td>'. $r->apno . '</td><td>' . $r->address . '</td><td>' . $r->dp . '</td><td>' . $r->description. '</td><td>'.  $r->title. ' ' . $datestatment .' '.  $r->exhibit_to . '</td></tr>';
}
 
$thankYouMessage .='</table>';	
        }else{
         	$thankYouMessage .= 'No query match.';
		}
 
				########## END CUSTOMIZED SQL ########################
				########################################################

		}// end search for date range only
 
		else 
		{
			//no specified STATUS, KEYWORD or DATE RANGE had been selected. The search NEEDS to be narrowed down 
			echo "<p class=\"error\">Please narrrow down your search by inputing a keyword, selecting a date range and/or chosing a status.</p>";
		}