<?php
//echo "<pre>";
//print_r($_REQUEST);
//print_r($_SERVER);
//session_start();
//echo "</pre>";
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="";
$default_category = 'homes-for-sale';
$category = array();
$not_found = 0;
$add_fulltext_sql = '';
    
$tpl = new AwtTpl();
$db = new DbMySQL(DB_HOST, DB_NAME, DB_USER, DB_PWD);
//$db->connect();

if(!$db->connect()){
	die("501 Internal server error!");
}


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);
}

$states = $db->select("SELECT DISTINCT State,StateFullName FROM `zip-codes` WHERE StateFullName!=''");
//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";
}

//echo $content_type;
//Navigation Category
if(isset($q[0])){
    $category = $db->select("SELECT * FROM term_data WHERE vid=1 and site='".SITE_ID."' and name = '".mysqli_escape_string($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 = '".mysqli_escape_string($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='".mysqli_escape_string($page)."' and status=1 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='".mysqli_escape_string($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='".mysqli_escape_string($state)."'");
//		$cities =   $db->select("SELECT DISTINCT City,CountyName FROM `zip-codes` WHERE StateFullName='".mysqli_escape_string($state)."'");
	    $counties = '';
	    $where_house = " AND h.state='".$state_info['state_name']."' AND c.tid='".$category['tid']."'";
		
		//$sql = "SELECT COUNT(*) count_houses,z.City,z.CountyName as county FROM realty r INNER JOIN `zip-codes` z ON r.city=z.City WHERE r.state='".mysqli_escape_string($state_info['state_name'])."' AND last_update>NOW() - INTERVAL 7 DAY GROUP BY z.City ORDER BY count_houses DESC";
		$sql = "SELECT COUNT(*) count_houses,z.City,z.CountyName AS county FROM realty r INNER JOIN `zip-codes` z ON r.city=z.City AND r.state=z.State WHERE r.state='".mysqli_escape_string($state_info['state_name'])."' AND r.url_path IS NOT NULL AND path1 IS NOT NULL AND last_update>NOW() - INTERVAL 7 DAY GROUP BY z.City,z.CountyName ORDER BY count_houses DESC";
		$cities = $db->select($sql);
		
		if(isset($q[2])&&$q[2]!='rss'){
			$county = str_replace('-'," ",$q[2]);
//  			if (ctype_upper($q[2])&&count($q)<4){
//  				header("Location: /".$q[0]."/".$q[1]."/".str_replace(" ","-",strtoupper($county)));
//  				exit();
//  			}
			$ZipCodes = $db->select("SELECT DISTINCT ZipCode FROM  `zip-codes` WHERE StateFullName='".mysqli_escape_string($state)."' AND CountyName='".mysqli_escape_string($county)."'");
			$sql = "SELECT DISTINCT City FROM  `zip-codes` WHERE StateFullName='".mysqli_escape_string($state)."' AND CountyName='".mysqli_escape_string($county)."'";
			$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]);
//  	        if (ctype_upper($q[2])&&count($q)<5){
//                  header("Location: /".$q[0]."/".$q[1]."_".str_replace(" ","-",strtoupper($county))."/".str_replace(" ","-",strtoupper($city)));
//                  exit();
//              }
if(ctype_lower(lcfirst($q[2]))&&count($q)<5){
		header("HTTP/1.1 301 Moved Permanently");

            	if(isset($_GET["pagenum"])){
            		header("Location: http://www.yousellwelist.com/".$q[0]."/".$q[1]."/".str_replace(" ","-",strtoupper($county))."/".str_replace(" ","-",strtoupper($city))."?pagenum=".(int) $_GET["pagenum"]);
            		
            	}else{
            		header("Location: http://www.yousellwelist.com/".$q[0]."/".$q[1]."/".str_replace(" ","-",strtoupper($county))."/".str_replace(" ","-",strtoupper($city)));
            		
            	}
            }	    	
$city_info =   $db->select("SELECT City,CountyName FROM `zip-codes` WHERE StateFullName='".mysqli_escape_string($state)."' AND CountyName='".mysqli_escape_string($county)."' AND City='".mysqli_escape_string($city)."'");
	    	$ZipCodes = $db->select("SELECT DISTINCT ZipCode FROM  `zip-codes` WHERE StateFullName='".mysqli_escape_string($state)."' AND CountyName='".mysqli_escape_string($county)."' AND City='".mysqli_escape_string($city)."'");
	    	if($city_info){
		        $template_file = 'city.tpl.html';
		        $tpl->assign("city",   $city);
		        $where_house .= " AND city='".mysqli_escape_string($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='".mysqli_escape_string($state)."' AND CountyName='".mysqli_escape_string($county)."' and ZipCode='".mysqli_escape_string($ZipCode)."'";
            $ZipCodes = $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);
            }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;
    }
}

if(isset($_GET['Search'])){
	$state_info = $db->select("SELECT state_name,state_desc FROM states WHERE state_name='".mysqli_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.state='".mysqli_escape_string($_GET['States'])."' AND c.tid='".$category['tid']."'";
    }
    if(isset($_GET['City'])&&$_GET['City']!=''||isset($_GET['Address'])&&$_GET['Address']!=''){
        $where_house .= " AND match(ft.address,ft.city,ft.zip,ft.state) against ('".mysqli_escape_string(trim(getvar('City',$_GET,"")))." ".mysqli_escape_string(trim($_GET['Address']))."') ";
    }
    $add_fulltext_sql = " INNER JOIN realty_fulltext ft ON h.ID = ft.houses_id ";
	//$where_house = "";

	$state = $db->select("SELECT state_desc FROM states WHERE state_name='".mysqli_escape_string(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 ".$add_fulltext_sql." WHERE 1 ".$where_house." AND url_path is not NULL AND path1 is not NULL ";
    
    $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." group by h.ID 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'){
	$home_url = str_replace('.html','',urldecode($q[1]));
	$sql = "select * from realty LEFT JOIN states s ON s.state_name=state OR s.state_desc=state where url_path='".mysqli_escape_string($home_url)."' and path1 is not null";
	$house = $db->select($sql);
//print_r($house);
	if($house&&$house[0]['path1']!=$q[0]){
		header("HTTP/1.1 301 Moved Permanently");
		header("Location: ".GLOBAL_URL.$house[0]['path1']."/".$q[1]);
	}
	if($house&&$house[0]['agent']!='Doyce Mathis'&&!strpos($house[0]['additional_info'], 'VMF listing')){
	   $county = $db->select("SELECT DISTINCT CountyName FROM `zip-codes` WHERE State='".mysqli_escape_string($house[0]['state'])."' AND City='".mysqli_escape_string($house[0]['city'])."' AND ZipCode='".mysqli_escape_string($house[0]['zip'])."'");
	   if($county){
	   	   $tpl->assign("house_county",  $county[0]);
	   }
	   $sql = "SELECT * FROM realty WHERE state='".$house[0]['state_name']."' AND zip='".$house[0]['zip']."' AND path1 IS NOT NULL AND path1<>'' AND url_path IS NOT NULL AND url_path<>'' AND url_path<>'".$house[0]['url_path']."' ORDER BY last_update DESC LIMIT 5";
	   $related_houses = $db->select($sql);
	   //echo "<!--";
	   //echo $sql;
	   //echo "-->";
	   
	   $tpl->assign("related_houses",  $related_houses);
	   $tpl->assign("house",  $house[0]);
	   
	} else {
	   //$not_found =1;
	    //echo "<!--";
	    $sql = "SELECT * FROM realty WHERE path1 is not null and sale_status!=1 and additional_info not like '%VMF listing%' and url_path LIKE '".mysqli_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));	
        if(!$houses){
        	header("HTTP/1.1 410 Gone");
        }
	}
}
//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 h.ID,url_path,path1,status_type,`type`,`status`,address,city,state,zip,last_update,created_date 
        FROM realty h  WHERE h.created_date>CURDATE()-INTERVAL 1 DAY AND h.url_path IS NOT NULL AND path1 IS NOT NULL ORDER BY last_update DESC LIMIT 20";
    $houses = $db->select($sql);
    
    if(!$houses){
    	$sql = "SELECT h.ID,url_path,path1,status_type,`type`,`status`,address,city,state,zip,last_update,created_date 
        FROM realty h  WHERE DATE(h.created_date)=DATE(h.last_update) AND h.url_path IS NOT NULL AND path1 IS NOT NULL ORDER BY last_update DESC LIMIT 20";
        $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('".mysqli_escape_string($_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('".mysqli_escape_string($_GET['q'])."','".$httpcode."','".$query_words."','".date("Y-m-d H:i")."')");
} 
$landing_pages = $db->select("SELECT * FROM landing_pages WHERE site='".SITE_ID."'");
$tpl->assign("landing_pages",$landing_pages);

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