<?php
include_once "config.php";
include_once "lib/common.lib.php";
require_once "lib/main.php";
require_once "lib/dbmysql.class.php";
require_once 'lib/awt_tpl.class.php';

$template_file = 'main.tpl.html';
$error_message = '';
$content_type = '';
$where_house="";
$add_fulltext_sql = "";
$default_category = 'forsale';

$categories = array('foreclosed'=>array('name'=>'foreclosed','description'=>'Foreclosed Homes'),
		          'mobile'=>array('name'=>'mobile','description'=>'Mobile Homes'),
		          'forrent'=>array('name'=>'forrent','description'=>'Homes For Rent'),
		          'forsale'=>array('name'=>'forsale','description'=>'Homes For Sale'));

$category = $categories['forsale'];

$not_found = 0;

$tpl = new AwtTpl();
$db = new DbMySQL(DB_HOST, DB_NAME, DB_USER, DB_PWD);
if(!$db->connect()){
	header('HTTP/1.0 500 Internal Server Error');
	exit;
}

$db2 = new DbMySQL(DB_HOST2, DB_NAME2, DB_USER2, DB_PWD2);
if(!$db2->connect()){
	header('HTTP/1.0 500 Internal Server Error');
	exit;
}

if(isset($_GET['q'])){
	$q = explode("/",$_GET['q']);
}
if (isset($q[1])&&trim($q[1])==''){
	unset($q[1]);
}

if(isset($q[0])){
	if(isset($categories[$q[0]])){
		$category = $categories[$q[0]];
	}else{
		unset($category);
	}
}

if(isset($category['name'])){
	switch ($category['name']){
		case "mobile":
			$where_house .= " AND h.site_index = '3000' ";
			$where_house1 = " AND h.site_index = '3000' ";
			break;
		case "foreclosed":
			$where_house .= " AND h.site_index in (2,4,1000,1002,1003,1006,1009,1020,1022,3001) ";
			$where_house1 = " AND h.site_index in (2,4,1000,1002,1003,1006,1009,1020,1022,3001) ";
			break;
		case "forsale":
			$where_house .= " AND status='For Sale' ";
			$where_house1 = " AND status='For Sale' ";
			break;
		case "forrent":
			$where_house .= " AND status='For Rent' ";
			$where_house1 = " AND status='For Rent' ";
			break;
		default:
		break;
	}
}

if(isset($q)&&isset($category['name'])&&count($q)==1){
	$content_type = "category";
	$template_file = 'category.tpl.html';
}

if(isset($q)&&isset($q[1])&&$q[1]=='property'){
	$content_type = "housepage";
    $template_file = 'housepage.tpl.html';
    
}

if(isset($q[0])&&preg_match("/(.*)\.html$/",$q[0])){
	$category = $categories['forsale'];
	$content_type = "page";
	$template_file = 'page.tpl.html';
}	

if(isset($q[0])&&isset($categories[$q[0]])&&count($q)>1){
	$content_type = "listing";
	$template_file = 'housepage.tpl.html';
}

if(isset($q[0])&&$q[0]=='property'){
	$content_type = "housepage";
	$template_file = 'housepage.tpl.html';
	$category = $categories['forsale'];
}


//echo $content_type;
if(isset($q)&&count($q)>1&&$content_type == "listing"){
    $state = str_replace("-"," ",$q[1]);
    $state_info = $db->select("SELECT state_name,state_desc FROM states WHERE state_desc='".mysql_escape_string($state)."'  limit 1");
    //print_r($state_info);
    if($state_info!=array()){
        $state_info = $state_info[0];
	    $cat=$q[0];
	    $template_file = 'inner.tpl.html';
	    //echo "select lower(true_city) as true_city,count(*) as count_houses from houses where true_state='".$state_info['state_name']."' and true_city is not null and true_city!='' ".$where_house." group by true_city";
		$cities =   $db2->select("select lower(true_city) as true_city,count(*) as count_houses from houses h where true_state='".$state_info['state_name']."' and true_city is not null and true_city!='' ".$where_house." group by true_city");
		$where_house .= " AND h.true_state='".$state_info['state_name']."' ";
		
		if(isset($q[2])){
	    	$city = str_replace('-'," ",$q[2]);
	    	$city_info =   $db->select("SELECT City,CountyName FROM `zip-codes` WHERE StateFullName='".mysql_escape_string($state)."' AND City='".mysql_escape_string($city)."'");
	    	//echo "SELECT ZipCode FROM  `zip-codes` WHERE BINARY StateFullName='".mysql_escape_string($state)."' AND PrimaryRecord = 'P' AND City='".mysql_escape_string($city)."'";
	    	//$ZipCodes = $db->select("SELECT ZipCode FROM  `zip-codes` WHERE StateFullName='".mysql_escape_string($state)."' AND PrimaryRecord = 'P' AND City='".mysql_escape_string($city)."'");
	    	if($city_info){
		        $template_file = 'city.tpl.html';
		        $tpl->assign("city",   $city);
		        $where_house .= " AND h.true_city='".mysql_escape_string($city)."'";
	    	}else{
	    		$not_found = 1;
	    	}
	    	$ZipCodes = $db2->select("SELECT distinct h.zip as ZipCode FROM houses h WHERE 1 ".$where_house);
	    	$tpl->assign("city",     $city);
	    	$tpl->assign("ZipCodes",   $ZipCodes);
	    }
        if(isset($q[3])){
            $ZipCode = str_replace('-'," ",$q[3]);
            
            $sql = "SELECT * FROM `zip-codes` WHERE StateFullName='".mysql_escape_string($state)."' AND PrimaryRecord = 'P'  and ZipCode='".mysql_escape_string($ZipCode)."'";
            $ZipCodes = $db->select_row($sql);
            if($ZipCodes){
                $template_file = 'zipcode.tpl.html';
                
				$where_house .= " AND h.true_zip='".$ZipCode."'";
                
                $tpl->assign("ZipCode",   $ZipCode);
                $tpl->assign("ZipCodes",   $ZipCodes);
            }else{
                $not_found = 1;
            }
           // echo "<pre>";
           // print_r($ZipCodes);
           // echo "</pre>";
        }
	    if($content_type == "rss"){
	    	$template_file = 'rss.tpl.html';
	    }
		if(!$category){
		    $error_message = "Category is not available!";
		}
		
		$tpl->assign("state",     $state);
	    $tpl->assign("cities",    $cities);
//	    $tpl->assign("counties",  $counties);
	    $tpl->assign("state_info",   $state_info);
    }else{
    	$not_found =1;
    }
}

if(isset($_GET['Search'])){
	$content_type='listing';
	$state_info = $db->select("SELECT state_name,state_desc FROM states WHERE state_name='".mysql_escape_string(getvar('States',$_GET))."' limit 1");
	//print_r($state_info);
	$state_info = $state_info[0];
	if(isset($_GET['States'])&&$_GET['States']!=''){
		$where_house = " AND h.true_state='".mysql_escape_string($_GET['States'])."'";
    }
    if(isset($_GET['City'])&&$_GET['City']!=''||isset($_GET['Address'])&&$_GET['Address']!=''){
        $where_house .= " AND match(ft.address,ft.city,ft.zip,ft.state) against ('".mysql_escape_string(trim($_GET['Address']))." ".mysql_escape_string(trim(getvar('City',$_GET,"")))."') ";
    }
	//$where_house = "";
    $add_fulltext_sql = " INNER JOIN houses_fulltext ft ON h.ID = ft.houses_id ";

	//print_r($state_info );
	if($state_info){
		$state = $state_info['state_desc'];
        $template_file = 'searchresults.tpl.html';
       // $search_params = array('States' => strip_tags($_GET['States']),'City'=>strip_tags($_GET['City']),'Address'=>strip_tags($_GET['Address']),'PropertyTypes'=>strip_tags($_GET['PropertyTypes']),);
       
            $search_params = array( 'Address' =>     urldecode(strip_tags(getvar('Address',$_GET))),
							    'MlsId' =>       strip_tags(getvar('MlsId',$_GET)),
							    'City' =>        strip_tags(getvar('City',$_GET)),
							    'States' =>      strip_tags(getvar('States',$_GET)));
	   
        //print_r($search_params); 
        $tpl->assign("search", 1);
        $tpl->assign("search_params", $search_params);
        $tpl->assign("state",  $state_info["state_name"]);		
	}else{
		$search_error_message = "Please select State!";
		$tpl->assign("search_error_message",     $search_error_message);
	}
	$tpl->assign("state_info",   $state_info);
}

//GET HOUSES LISTING
if($content_type=='housepage'){
	$where_house="";
}
if($where_house!=""&&($content_type=='listing'||$content_type=='category')){
    $houses = array();
    
    $fields = "COUNT(*)";
    //$sql = "SELECT COUNT(*) as count FROM houses WHERE 1 ".$where_house." AND houses_state='".$state_info['state_name']."' AND houses_valid=4 AND tid='".$category['tid']."'";
     $sql = "SELECT COUNT(*) AS count FROM houses h ".$add_fulltext_sql." WHERE 1 ".$where_house." AND true_state is not NULL AND true_state!='' ";
    
    $houses_count = $db2->select($sql);
    
    $sql = str_replace("COUNT(*) AS count","*",$sql);
    
    //$sql = "SELECT *  FROM houses WHERE 1 ".$where_house." AND houses_state='".$state_info['state_name']."' AND houses_valid=4 AND tid='".$category['tid']."'";   
    
    //	print_r($houses);
    $i=0;
    $previous = '';
    $next = '';
    
    $num = $houses_count[0]['count'];
    
    if (isset($_GET['p'])){
        $pagenum = (int) $_GET['p'];
    } else {
        $pagenum = 1;
    }
    $rows = $num;


    $page_rows = 10;
    
    if($num<$page_rows){
        $next = "";
    }
    $last = ceil($rows/$page_rows);
    
    $pagenum;
    if ($pagenum <= 1){
       $pagenum = 1;
    }elseif ($pagenum > $last){
       $pagenum = $last;
    }
    //echo $pagenum;
    $max = 'LIMIT ' .($pagenum-1) * $page_rows.',' .$page_rows;
    $SqlQuery=$sql." order by h.last_update DESC LIMIT ". ($pagenum-1) * $page_rows.",$page_rows";
    if($content_type == "category"){
        $SqlQuery=$sql." order by h.last_update DESC LIMIT 50";
    }
    $houses = $db2->select($SqlQuery);
 //  echo $SqlQuery;
    ////////////////////////////////////////////

    if ($pagenum == 1){
    }else{
        $previous = $pagenum-1;
    }
    if ($pagenum == $last)  {
    
    }else {
       $next = $pagenum+1;
    }
    
    if($pagenum>8){
        $start = $pagenum-5;
        $loop = $pagenum+5;
        if($last-5<=$pagenum){
            $loop = $last+1;
            $start = $last-10;
        }
        
    }else{
        $start = 1;
        $loop = 11;
    }
    if($last<11){
            $loop = $last+1;
    }
//     echo "<pre>";
    
//     print_r($houses);
//     echo "</pre>";
    $tpl->assign("startpage", $start);
    $tpl->assign("looparr",   $loop);
    $tpl->assign("housesrows",    $rows);
    $tpl->assign("pagenum",   $pagenum);
    $tpl->assign("previous",  $previous);
    $tpl->assign("last",      $last);
    $tpl->assign("next",      $next);   
    //$cities = $db->select($sql);
    if($houses){
    	$tpl->assign("houses",    $houses);
    }else{
    	//echo str_replace($where_house1, "", $SqlQuery);
    	$houses = $db2->select(str_replace($where_house1, "", $SqlQuery));
    	$tpl->assign("houses_related",    $houses);
    	if(!$houses){
    		//header("HTTP/1.1 410 Gone");
    	}
    }
}

if(isset($q[1])&&$content_type=='housepage'){
    $home_url = (int) str_replace('ID','',urldecode($q[1]));
	$sql = "select * from houses where ID='".mysql_escape_string($home_url)."'";
	$house = $db2->select_row($sql);

	if($house){

	   $sql = "select * from images where house_id='".mysql_escape_string($home_url)."' and state = 4";
	   $images = $db2->select($sql);
	   
	   $sql = "select * from agents where house_id='".mysql_escape_string($home_url)."'";
	   $agents = $db2->select($sql);
	   
	   $sql = "select * from houses_spintext where house_id='".mysql_escape_string($home_url)."'";
	   $spin_text = $db2->select_row($sql);
	   
// 	   echo "<pre>";
// 	   print_r($agents);
// 	   echo "</pre>";
	   
	   if($agents){
	   	$tpl->assign("agent",  $agents[0]);
	   }
	   
	   $category = array();
	   
	   if($house['site_index']==3000){
	   		$category = $categories['mobile'];
	   }
	   
	   if($house['status']=="For Sale"&&!$category){
	   		$category = $categories['forsale'];
	   }
	   if($house['status']=="For Rent"&&!$category){
	   	$category = $categories['forrent'];
	   }
	   
	   if($house['site_index']==1003||$house['site_index']==1020){
	   	$house['additional_info']='';
	   	$category = $categories['foreclosed'];
	   }
	   if($house['site_index']==3001||$house['site_index']==1000||$house['site_index']==1002||$house['site_index']==1003||$house['site_index']==1006||$house['site_index']==1022){ //2,4,1000,1002,1003,1006,1009,1020,1022,3001
	   	$category = $categories['foreclosed'];
	   }
	   $sql = "SELECT * FROM `zip-codes` WHERE (State='".mysql_escape_string($house['state'])."' or State='".mysql_escape_string($house['true_state'])."') AND PrimaryRecord = 'P'  and ZipCode='".mysql_escape_string($house['true_zip'])."'";
	   $ZipCode = $db->select_row($sql);
	   
	   $sql = "SELECT * FROM houses WHERE state='".$house['true_state']."' AND true_zip='".$house['zip']."' AND ID<>'".$house['ID']."' AND true_state is not null AND true_state!=''   ORDER BY last_update DESC LIMIT 4";
	   $related_houses = $db2->select($sql);
//print_r($related_houses);
	  
           if($spin_text['spin_text']&&$house['sale_status']==1){
                $new_spin = explode("<h4>",$spin_text['spin_text']);
                $spin_text['spin_text'] = $new_spin[0];
           } 
	   $tpl->assign("related_houses",  $related_houses);
	   $tpl->assign("spin_text",  $spin_text['spin_text']);
	   $tpl->assign("ZipCodes",   $ZipCode);
	   $tpl->assign("images",  $images);
	   $tpl->assign("house",  $house);
	} else {
	    $not_found = 1;
        //echo "<!--";
        //echo $where_house;
        $sql = "SELECT * FROM houses WHERE url_path LIKE '".mysql_escape_string(str_replace(" ","%",str_replace("Unit","",preg_replace("|[^[:alpha:]\s]|","",str_replace("-"," ",$home_url)))))."' order by last_update DESC LIMIT 10";
        $houses = $db->select($sql);
        //echo "-->";
        $template_file = 'suggestion.tpl.html';
        $tpl->assign("houses",    $houses);
        $tpl->assign("request",    str_replace("-"," ",$home_url)); 	
	}
}
//print_r($houses);
// if($not_found){
// 	ob_start();
// 	header("HTTP/1.0 404 Not Found");
// 	header("Status: 404 Not Found");
// 	$template_file = 'main.tpl.html';
// 	$error_message = "404 Page Not Found";
// }
if(!isset($q)&&!isset($_GET['Search'])){
	$content_type = 'front';
}

if($not_found||!$content_type){
	$content_type = 'suggestion';
	$template_file = 'suggestion.tpl.html';
	$error_message = "this category doesn't exist on our website.";
	$tpl->assign("request", strip_tags(preg_replace("|[^[:alnum:]\s]|"," ",$_SERVER['REDIRECT_QUERY_STRING'])));
	//print_r($_SERVER);
	
}

if($content_type=='front'||$content_type=='listing'){
    $landing_pages = $db->select("SELECT Page_Name,Metatag_Description FROM additional_pages2 WHERE on_home=1");
    $tpl->assign("landing_pages",$landing_pages);
}

if($content_type=='page'){
	$page_name = str_replace(".html", "", $q[0]);
	$sql = "SELECT * FROM additional_pages2 WHERE Page_Name='".mysql_escape_string(str_replace(".html", "", $q[0]))."'";
	$page = $db->select_row($sql);
	//print_r($page);
    if(!$page){

		  $page = array ( 
		  "Page_Name" => "hud-foreclosures", 
		  "Header_Image" => "", 
		  "Metatag_Keywords" => preg_replace("|[^[:alnum:]\s]|"," ",$page_name).", free real estate listings, realestate, reo", 
		  "Page_Title" => "Search results by: ".preg_replace("|[^[:alnum:]\s]|"," ",$page_name)." request on freerealestateresources.com", 
		  "Metatag_Description" => "Search results by: ".preg_replace("|[^[:alnum:]\s]|"," ",$page_name)." request on freerealestateresources.com", 
		  "Text_at_top" => "Search results by: ".preg_replace("|[^[:alnum:]\s]|"," ",$page_name)." request on freerealestateresources.com",
		  "Text_at_bottom" => "",
		  "text_area" => "",
		  "double_listing" => "",
		  "published" => 0,
		  "on_home" => 1
		  );
  
    }
	$landing_pages = $db->select("SELECT Page_Name,Metatag_Description FROM additional_pages2 WHERE on_home=1");
    
	$tpl->assign("landing_pages",$landing_pages);
	$tpl->assign("page",   $page);
}
//print_r($landing_pages);
//echo $content_type;
if($content_type=='page'||$content_type=='front'||$content_type == 'suggestion'){
//	echo $content_type;
	
	$sql = "SELECT *
	FROM houses h  WHERE 1  AND h.true_state is not null AND true_state!='' ORDER BY id  DESC LIMIT 20";
	$houses = $db2->select($sql);
	/*
	if(!$houses){
		$sql = "SELECT *
		FROM houses h  WHERE DATE(h.created_date)=DATE(h.last_update) AND h.true_state is not null AND true_state!='' ORDER BY last_update DESC LIMIT 20";
		$houses = $db2->select($sql);
	}
	*/
	$tpl->assign("houses",    $houses);
}

$query_words = '';
if(isset($_SERVER['HTTP_REFERER'])){
    $referer_link = $_SERVER['HTTP_REFERER'];
    if(strpos($referer_link, 'google')){
       $info = parse_url($referer_link);
       parse_str($info['query'], $params);
       $query_words = $params['q'];
    }else{
       $query_words = '';
    }

}
if(isset($_GET['q'])){
	$httpcode = 200;
	
	if($not_found){
	    $httpcode = 404;
	}
	//$res = $db->query("INSERT IGNORE INTO urlstore (query,code,keywords,date) values('".mysql_escape_string($_GET['q'])."','".$httpcode."','".$query_words."','".date("Y-m-d H:i")."')");
	$sql = "SELECT * FROM seo_content WHERE path='".mysql_escape_string($_GET['q'])."' and status=1";
	$seo_text = $db->select_row($sql);
	
}
if($query_words){
      //$res = $db->query("INSERT INTO keywords (query,code,keywords,date) values('".mysql_escape_string($_GET['q'])."','".$httpcode."','".$query_words."','".date("Y-m-d H:i")."')");
} 

$states = $db->select("SELECT state_name,state_desc FROM states order by state_name");
//print_r($category);

//echo $template_file;
$tpl->assign("content_type",   $content_type);
$tpl->assign("seo_content",   $seo_text);
$tpl->assign("states",   $states); 
$tpl->assign("error_message", $error_message);
$tpl->assign("category",   $category);
$tpl->assign("categories",   $categories);
//$tpl->assign("default_category",   $default_category);
$tpl->display($template_file);
?>