<?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='".$db->escapeSting($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='".$db->escapeSting($state)."' AND City='".$db->escapeSting($city)."'");
			//echo "SELECT ZipCode FROM  `zip-codes` WHERE BINARY StateFullName='".mysqli_escape_string($state)."' AND PrimaryRecord = 'P' AND City='".mysqli_escape_string($city)."'";
			//$ZipCodes = $db->select("SELECT ZipCode FROM  `zip-codes` WHERE StateFullName='".mysqli_escape_string($state)."' AND PrimaryRecord = 'P' AND City='".mysqli_escape_string($city)."'");
			if($city_info){
				$template_file = 'city.tpl.html';
				$tpl->assign("city",   $city);
				$where_house .= " AND h.true_city='".$db2->escapeSting($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='".$db->escapeSting($state)."' AND PrimaryRecord = 'P'  and ZipCode='".$db->escapeSting($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='".$db->escapeSting(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='".$db2->escapeSting($_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 ('".$db2->escapeSting(trim($_GET['Address']))." ".$db2->escapeSting(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='".$db2->escapeSting($home_url)."'";
	$house = $db2->select_row($sql);

	if($house){

		$sql = "select * from images where house_id='".$db2->escapeSting($home_url)."' and state = 4";
		$images = $db2->select($sql);

		$sql = "select * from agents where house_id='".$db2->escapeSting($home_url)."'";
		$agents = $db2->select($sql);

		$sql = "select * from houses_spintext where house_id='".$db2->escapeSting($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='".$db->escapeSting($house['state'])."' or State='".$db->escapeSting($house['true_state'])."') AND PrimaryRecord = 'P'  and ZipCode='".$db->escapeSting($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(isset($spin_text['spin_text']) && $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",  isset($spin_text['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 '".$db->escapeSting(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='".$db->escapeSting(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 = '';
	}

}

$seo_text = '';
if(isset($_GET['q'])){
	$httpcode = 200;

	if($not_found){
		$httpcode = 404;
	}
	//$res = $db->query("INSERT IGNORE INTO urlstore (query,code,keywords,date) values('".mysqli_escape_string($_GET['q'])."','".$httpcode."','".$query_words."','".date("Y-m-d H:i")."')");
	$sql = "SELECT * FROM seo_content WHERE path='".$db->escapeSting($_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);
?>