<?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/dbpgsql.class.php";
require_once 'lib/awt_tpl.class.php';

$template_file = 'main.tpl.html';
$error_message = '';
$content_type = '';
$where_house="";
$default_category = 'commercial-property-for-rent';
$category = array();
$not_found = 0;
$seo_text = array();

$tpl = new AwtTpl();
$db = new DbMySQL(DB_HOST, DB_NAME, DB_USER, DB_PWD);
$db->connect();

//if(isset($_GET['q'])){
//	$q = explode("/",$_GET['q']);
//}
//if(isset($q[1])&&preg_match("/(.*)\.htm$/",$q[1])){
//    $q2 = explode("_",str_replace(".htm","",$q[1]));
//    $q = array_merge(array($q[0]),$q2);
//}
function is_email($email){
  if (function_exists("filter_var")){
    $s=filter_var($email, FILTER_VALIDATE_EMAIL);
    return !empty($s);
  }
  $p = '/^[a-z0-9!#$%&*+-=?^_`{|}~]+(\.[a-z0-9!#$%&*+-=?^_`{|}~]+)*';
  $p.= '@([-a-z0-9]+\.)+([a-z]{2,3}';
  $p.= '|info|arpa|aero|coop|name|museum|mobi)$/ix';
  return preg_match($p, $email);
}

// subscribtion
if (isset($_POST['recive_realty_email']) && isset($_GET['q'])) {
	$page = trim(htmlspecialchars($_GET['q']));
	$email = trim(htmlspecialchars($_POST['recive_realty_email']));
	if (is_email($_POST['recive_realty_email'])) {
		$query = sprintf("SELECT * FROM subscribtion WHERE email = '%s' AND page = '%s'", mysqli_real_escape_string($email), mysqli_real_escape_string($page));
		$row = $db->select($query);
		if ($row) {
			$_SESSION['error'] = 'You have already subscribed.';
		} else {
			$query = sprintf("INSERT INTO subscribtion (email, page) VALUES ('%s', '%s')", mysqli_real_escape_string($email), mysqli_real_escape_string($page));
			$db->insert($query);
			$_SESSION['ok'] = 'You have successfully subscribed to receive new properties.';
		}
	} else {
		$_SESSION['error'] = 'Entered email is not valid.';
	}
}

// broker contact
if (isset($_POST['contact_broker_email']) && isset($_GET['q'])) {
	$page = trim(htmlspecialchars($_GET['q']));
	$email = trim(htmlspecialchars($_POST['contact_broker_email']));
	$name = trim(htmlspecialchars($_POST['contact_broker_name']));
	$phone = trim(htmlspecialchars($_POST['contact_broker_phone']));
	if ($phone == 'Phone') {
		$phone = '';
	}
	if ($name == 'Name') {
		$name = '';
	}
	if (is_email($email)) {
		$query = sprintf("SELECT * FROM broker_contact WHERE email = '%s' AND page = '%s'", mysqli_real_escape_string($email), mysqli_real_escape_string($page));
		$row = $db->select($query);
		if ($row) {
			$_SESSION['error'] = 'You have already sent inquiry to broker.';
		} else {
			$query = sprintf("INSERT INTO broker_contact (name, phone, email, page) VALUES ('%s', '%s', '%s', '%s')", mysqli_real_escape_string($name), mysqli_real_escape_string($phone), mysqli_real_escape_string($email), mysqli_real_escape_string($page));
			$db->insert($query);
			$_SESSION['ok'] = 'You have successfully sent inquiry to broker.';
		}
	} else {
		$_SESSION['error'] = 'Entered email is not valid.';
	}
}

if(isset($_GET['q'])){
    $q = explode("_",$_GET['q']);
    if(end($q)==""){
        unset($q[count($q)-1]);
    }
    if(end(explode("/",end($q)))=="rss"){
        $q[count($q)-1] = str_replace("/rss","",end($q));
        $q[]='rss';
    }
    
    $sql = "SELECT * FROM seo_content WHERE path='".$db->escapeSting($_GET['q'])."' and status=1";
    $seo_text = $db->select_row($sql);
    //print_r($seo_text);
}
$states = $db->select("SELECT DISTINCT State,StateFullName FROM `zip-codes` WHERE StateFullName!='' order by StateFullName ASC");

//Check Page TYPE
if(isset($q[0])&&preg_match("/[A-Za-z0-9\-]$/",$q[0])&&count($q)==1){
    $content_type = "category";
    $template_file = 'category.tpl.html';
}elseif(isset($q[1])&&preg_match("/(.*)\.html$/",$q[1])){
	$content_type = "housepage";
    $template_file = 'housepage.tpl.html';
}else{
    $content_type = "listing";
}
if(isset($q[0])&&preg_match("/(.*)\.html$/",$q[0])){
	$content_type = "page";
	$template_file = 'page.tpl.html';
}

if(isset($q)&&end($q)=='rss'){
    $content_type = "rss";
}
//Navigation Category
if(isset($q[0])){
    $category = $db->select("SELECT * FROM term_data WHERE vid=1 and site='".SITE_ID."' and name = '".$db->escapeSting($q[0])."'");
}

if($category){
    $category = $category[0];
    $where_house = " AND c.tid='".$category['related']."'";
}else{
	$category = $db->select("SELECT * FROM term_data WHERE vid=1  and site='".SITE_ID."' and name = '".$db->escapeSting($default_category)."'");
	$category = $category[0];
	if($content_type == "category"){
	   $not_found = 1;
	}
}


if($content_type == "page"){
    $page = str_replace(".html","",$q[0]);
	$page_info = $db->select("SELECT * FROM landing_pages WHERE Page_Name='".$db->escapeSting($page)."' and site='".SITE_ID."' LIMIT 1");
	//print_r($page_info);
	if($page_info){
		$tpl->assign("page",   $page_info[0]);
		if($page_info[0]['filter_state']){
			$where_house = " AND h.state='".$page_info[0]['filter_state']."'  AND c.tid='1' ";
		}
	    if($page_info[0]['filter_tid']){
	        $where_house = " AND c.tid='".$page_info[0]['filter_tid']."'";        
	    }
	}else{
		 $not_found = 1;
	}
	
}

//print_r($category);
if(isset($q)&&count($q)>1&&($content_type == "listing"||$content_type == "rss")){
    $state = str_replace("-"," ",$q[1]);
    $state_info = $db->select("SELECT state_name,state_desc FROM states WHERE state_desc='".$db->escapeSting($state)."' limit 1");
    if($state_info!=array()){
        $state_info = $state_info[0];
	    $cat=$q[0];
	    $template_file = 'inner.tpl.html';
		$counties = $db->select("SELECT DISTINCT CountyName FROM `zip-codes` WHERE StateFullName='".$db->escapeSting($state)."' order by CountyName");
		$cities =   $db->select("SELECT DISTINCT City,CountyName FROM `zip-codes` WHERE StateFullName='".$db->escapeSting($state)."'");
		$where_house = " AND h.state='".$state_info['state_name']."' AND c.tid='".$category['tid']."'";
		
		if(isset($q[2])&&$q[2]!='rss'){
			$county = str_replace('-'," ",$q[2]);
			$ZipCodes = $db->select("SELECT DISTINCT ZipCode FROM  `zip-codes` WHERE StateFullName='".$db->escapeSting($state)."' AND CountyName='".$db->escapeSting($county)."'");
			$sql = "SELECT DISTINCT City FROM `zip-codes` WHERE StateFullName='".$db->escapeSting($state)."' AND CountyName='".$db->escapeSting($county)."' order by City";
			$cities = $db->select($sql);
			if($cities){
				
				$cities_list = [];
				foreach($cities as $key=>$value){
					$cities_list[]= "'".$value['City']."'";
				}
				$template_file = 'county.tpl.html';
				if($cities_list){
		            $where_house.= " AND city in (".implode(",", $cities_list).") ";
				}
				//$tpl->assign("ZipCodes",   $ZipCodes);
				$tpl->assign("county",   $county);
			}else{
	            $not_found = 1;
			}
		}
	    if(isset($q[3])&&$q[3]!='rss'){
	        $city = str_replace('-'," ",$q[3]);
	    	$city_info =   $db->select("SELECT City,CountyName FROM `zip-codes` WHERE StateFullName='".$db->escapeSting($state)."' AND CountyName='".$db->escapeSting($county)."' AND (City='".$db->escapeSting($city)."' OR CityAliasName='".$db->escapeSting($city)."')");
	    	$ZipCodes = $db->select("SELECT DISTINCT ZipCode FROM  `zip-codes` WHERE StateFullName='".$db->escapeSting($state)."' AND CountyName='".$db->escapeSting($county)."' AND City='".$db->escapeSting($city)."'");
	    	if($city_info){
		        $template_file = 'city.tpl.html';
		        $tpl->assign("city",   $city);
		        $where_house .= " AND city='".$db->escapeSting($city)."'";
	    	}else{
	    		$not_found = 1;
	    	}
	    	$tpl->assign("ZipCodes",   $ZipCodes);
	    }
        if(isset($q[4])&&$q[4]!='rss'){
            $ZipCode = str_replace('-'," ",$q[4]);
            $sql = "SELECT DISTINCT ZipCode FROM `zip-codes` WHERE StateFullName='".$db->escapeSting($state)."' AND CountyName='".$db->escapeSting($county)."' and ZipCode='".$db->escapeSting($ZipCode)."'";
            $ZipCodes = $db->select($sql);
            $sql = "SELECT DISTINCT ZipCode FROM `zip-codes` WHERE StateFullName='".$db->escapeSting($state)."' AND CountyName='".$db->escapeSting($county)."' and ZipCode='".$db->escapeSting($ZipCode)."' AND City!=CityAliasName";
            $ZipAreaCities = $db->select($sql);
            if($ZipCodes){
                $template_file = 'zipcode.tpl.html';
                if($cities_list){
                    $where_house .= " AND h.zip='".$ZipCodes[0]['ZipCode']."'";
                }
                $tpl->assign("ZipCode",   $ZipCode);
                $tpl->assign("AreaCities",   $ZipAreaCities);
            }else{
                $not_found = 1;
            }
        }
	    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;
    	$home_url = strip_tags(str_replace('.html','',urldecode($q[1])));
    	 
    	$sql = "SELECT * FROM realty WHERE url_path LIKE '".$db->escapeSting(str_replace(" ","%",str_replace("Unit","",preg_replace("|[^[:alpha:]\s]|","",str_replace("-"," ",$home_url)))))."'";
    	$houses = $db->select($sql);
    	//echo "-->";
    	$template_file = 'suggestion.tpl.html';
    	$tpl->assign("houses",    $houses);
    	$tpl->assign("request",    str_replace(".","",str_replace("-"," ",$home_url)));
    }
}

if(isset($_GET['Search'])){
	$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.state='".$db->escapeSting($_GET['States'])."' AND c.tid='".$category['tid']."'";
    }
    if(isset($_GET['City'])&&$_GET['City']!=''||isset($_GET['Address'])&&$_GET['Address']!=''){
        $where_house .= " AND match(h.address,h.city,h.zip) against ('".$db->escapeSting(trim(getvar('City',$_GET,"")))." ".$db->escapeSting(trim($_GET['Address']))."') ";
    }
	//$where_house = "";

	$state = $db->select("SELECT state_desc FROM states WHERE state_name='".$db->escapeSting(trim($_GET['States']))."' limit 1");
	if($state){
		$state = $state[0]['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']),);
        if(isset($_GET['AgentName'])){
        	$search_params = array( 'Address' =>     urlencode(strip_tags($_GET['Address'])),
                                'MlsId' =>       strip_tags($_GET['MlsId']),
                                'City' =>        strip_tags(getvar('City',$_GET,"")),
                                'States' =>      strip_tags($_GET['States']),
                                'PropertyTypes' => strip_tags($_GET['PropertyTypes']),
                                'AgentName' =>   strip_tags($_GET['AgentName']),
                                'AgentEmail' =>  strip_tags($_GET['AgentEmail']),
                                'MinPrice' =>    strip_tags($_GET['MinPrice']),
                                'MaxPrice' =>    strip_tags($_GET['MaxPrice']));
        }else{
            $search_params = array( 'Address' =>     urlencode(strip_tags(getvar('Address',$_GET))),
							    'MlsId' =>       strip_tags(getvar('MlsId',$_GET)),
							    'City' =>        strip_tags(getvar('City',$_GET)),
							    'States' =>      strip_tags(getvar('States',$_GET)),
							    'PropertyTypes' => strip_tags(getvar('PropertyTypes',$_GET)));
	    }
        //print_r($search_params); 
        $tpl->assign("search", 1);
        $tpl->assign("search_params", $search_params);
        $tpl->assign("state",  $state);		
	}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($where_house!=""){
    $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 realty h INNER JOIN content_category c ON h.ID=c.houses_id WHERE 1 and url_path is not null ".$where_house." ";
    
    $houses_count = $db->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['pagenum'])){
        $pagenum = (int) $_GET['pagenum'];
    } else {
        $pagenum = 1;
    }
    $rows = $num;


    $page_rows = 10;
    
    if($num<$page_rows){
        $next = "";
    }
    $last = ceil($rows/$page_rows);
    
    
    if ($pagenum < 1){
       $pagenum = 1;
    }elseif ($pagenum > $last){
       $pagenum = $last;
    }
    $max = 'LIMIT ' .($pagenum-1) * $page_rows.',' .$page_rows;
    $SqlQuery=$sql." and url_path is not null order by h.last_update DESC LIMIT ". ($pagenum-1) * $page_rows.",$page_rows";
    $houses = $db->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;
    }
    
    $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);
    $tpl->assign("houses",    $houses);
}

if(isset($q[1])&&$content_type=='housepage'){
	$db2 = new DbMySQL(DB_HOST2, DB_NAME2, DB_USER2, DB_PWD2);
	if(!$db2->connect()){
		header('HTTP/1.0 500 Internal Server Error');
		exit;
	}

	
	if(strpos($q[1],' ')){
		header("Location: ".GLOBAL_URL.str_replace(" ","",urldecode($_GET['q'])));
		exit;
	}
	if (isset($_SESSION['ok'])) {
		$tpl->assign("OkMessage",  $_SESSION['ok']);
		unset($_SESSION['ok']);
	}
	if (isset($_SESSION['error'])) {
		$tpl->assign("ErrorMessage",  $_SESSION['error']);
		unset($_SESSION['error']);
	}
	
	$home_url = str_replace('.html','',urldecode($q[1]));
	$sql = "select * from realty INNER JOIN states s ON s.state_name=state where url_path='".$db->escapeSting($home_url)."'";
	$house = $db->select($sql);
	if($house){
		//echo "SELECT CountyName,City,State,ZipCode FROM `zip-codes` WHERE State='".$db->escapeSting($house[0]['state'])."' AND City='".$db->escapeSting($house[0]['city'])."' AND ZipCode='".$db->escapeSting($house[0]['zip'])."'";
	   if($house[0]['zip']){
		  $county = $db->select("SELECT CountyName,City,State,StateFullName,ZipCode FROM `zip-codes` WHERE State='".$db->escapeSting($house[0]['state'])."' AND City='".$db->escapeSting($house[0]['city'])."' AND ZipCode='".$db->escapeSting($house[0]['zip'])."' and PrimaryRecord='P'");
	   }
	   //print_r($county);
	   if($county){
	   	   $tpl->assign("house_county",  $county[0]);
	   }
	   if(strtotime($house[0]['created_date'])>strtotime(date("Y-m-d H:i:s"))-10600){
	       $house[0]['image_path'] = null;
	   }
	   $tpl->assign("house",  $house[0]);
	   $spaces = array();
	   
	   $sql = "select * from spaces where house_id='".$house[0]['ID']."'";
	   $spaces = $db->select($sql);
	   foreach ($spaces as $key=>$value){
	       preg_match('/(\d)+/ims', $value['rent'], $price);
           $spaces[$key]['area'] = str_replace("SF","",str_replace(",","",$spaces[$key]['area']));
	       if(strpos($value['rent'],"(Annual)")&&strpos($value['rent'],"PSF")){
	           $spaces[$key]['rent1'] = number_format($price[0]*$spaces[$key]['area']/12,2);
	       }
	   }
	   $sql = "select * from images where house_id='".$house[0]['ID']."'";
	   $images = $db2->select($sql);
	   //print_r($images);
	   $sql = "select * from agents where house_id='".$house[0]['ID']."'";
	   $agents = $db2->select($sql);
	   
	   $sql = "select * from tenants where house_id='".$house[0]['ID']."'";
	   $tenants = $db2->select($sql);
	   
	   $sql = "select * from floor_sizes where house_id='".$house[0]['ID']."'";
	   $floor_sizes = $db2->select($sql);
	   
	   if($images){
	   	$tpl->assign("images",  $images);
	   }
	   if($agents){
	   	$tpl->assign("agents",  $agents);
	   }
	   if($tenants){
	   	$tpl->assign("tenants",  $tenants);
	   }
	   if($floor_sizes){
	   	$tpl->assign("floor_sizes",  $floor_sizes);
	   }
	   
	   $tpl->assign("spaces",  $spaces);
	} else {
		//error_reporting(E_ALL);
		//echo "<!--";
	//	$sql = "SELECT * FROM realty WHERE url_path LIKE '".$db->escapeSting(str_replace(" ","%",str_replace("Unit","",preg_replace("|[^[:alpha:]\s]|","",str_replace("-"," ",$home_url)))))."'";
	//	$houses = $db->select($sql);
		//echo "-->";
	//	$template_file = 'suggestion.tpl.html';
	//	$tpl->assign("houses",    $houses);
	//	$tpl->assign("request",    str_replace("-"," ",$home_url));
		//exit;
	   $not_found =1;
	}
//	echo "<pre>";
//	print_r($house);
//	echo "</pre>";
}
//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)||$not_found){
    $content_type = "front";
    $sql = "SELECT * FROM realty h INNER JOIN term_data t ON h.tid = t.tid INNER JOIN content_category c ON h.ID=c.houses_id WHERE 1 GROUP BY state ORDER BY state,last_update DESC LIMIT 50";
    $latest_updates = $db->select($sql);
    
    $sql = "SELECT StateFullName,State,CountyName,City,ZipCode FROM `zip-codes` WHERE PrimaryRecord!='P' GROUP BY City ORDER BY RAND() LIMIT 46";
    $random_listings = $db->select($sql);
    
//    $sql = "SELECT url_path,path,`status`,use_type,type,address,city,state,zip,last_update,created_date FROM realty h INNER JOIN content_category c ON h.ID=c.houses_id WHERE h.created_date>CURDATE()-INTERVAL 1 DAY AND h.url_path IS NOT NULL ORDER BY last_update DESC LIMIT 30";
//    $houses = $db->select($sql);
//    
	$sql = "SELECT url_path,path,`status`,use_type,address,city,state,last_update,created_date FROM realty h INNER JOIN content_category c ON h.ID=c.houses_id WHERE date(h.created_date)=date(h.last_update) AND h.url_path IS NOT NULL ORDER BY last_update DESC LIMIT 30";
	$houses = $db->select($sql);
	
    
    $tpl->assign("houses", $houses);
    $tpl->assign("random_listings", $random_listings);
    $tpl->assign("latest_updates", $latest_updates);
}

$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('".$db->escapeSting($_GET['q'])."','".$httpcode."','".$query_words."','".date("Y-m-d H:i")."')");
}
if($query_words){
      $res = $db->query("INSERT INTO keywords (query,code,keywords,date) values('".$db->escapeSting($_GET['q'])."','".$httpcode."','".$query_words."','".date("Y-m-d H:i")."')");
} 
$landing_pages = $db->select("SELECT * FROM landing_pages WHERE site='".SITE_ID."' and status=1");
$tpl->assign("landing_pages",$landing_pages);

$tags = $db->select("SELECT distinct keywords,`query` FROM keywords WHERE `query`='".$db->escapeSting($_GET['q'])."' and !INSTR(keywords, 'site:')");
if($tags){
	$tpl->assign("tags",$tags);
}
//echo $content_type;
if($seo_text){
	$tpl->assign("seo_text",$seo_text);
}
$tpl->assign("states",   $states);
$tpl->assign("error_message", $error_message);
$tpl->assign("category",   $category);
$tpl->display($template_file);
?>