<?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 = 'for-sale';

$categories = array('foreclosures'=>array('name'=>'foreclosures','description'=>'Foreclosure Listings'),
    'mobile'=>array('name'=>'mobile','description'=>'Mobile Homes'),
    'for-rent'=>array('name'=>'for-rent','description'=>'Homes For Rent'),
    'for-sale'=>array('name'=>'for-sale','description'=>'Homes For Sale'));
$category = $categories['for-sale'];

$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);
    }
}
//print_r($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 "foreclosures":
            $where_house .= " AND h.site_index in (2,4,1000,1002,1006,1009,1020,1022,3001) ";
            $where_house1 = " AND h.site_index in (2,4,1000,1002,1006,1009,1020,1022,3001) ";
            break;
        case "for-sale":
            $where_house .= " AND status='For Sale' ";
            $where_house1 = " AND status='For Sale' ";

            break;
        case "for-rent":
            $where_house .= " AND status='For Rent' ";
            $where_house1 = " AND status='For Rent' ";
            break;
        default:
            break;
    }
}

if(isset($q)&&isset($category['name'])&&count($q)==1){
    //echo "select true_state as state_name,true_state_name as state_desc from houses where  true_state !='' and true_state is not null and true_state_name is not null and true_state_name!=''  $where_house group by true_state";
    //print_r($db);
    $states = $db2->select("select true_state as state_name,true_state_name as state_desc from houses h where  true_state !='' and true_state is not null and true_state_name is not null and true_state_name!='' ".$where_house." group by true_state");
    //print_r($states);
    $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['for-sale'];
    $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]=='listing'){
    $content_type = "housepage";
    $template_file = 'housepage.tpl.html';
    $category = $categories['for-sale'];
}


//echo $content_type;
if(isset($q)&&count($q)>1&&$content_type == "listing"){
    //echo $content_type;
    $state = str_replace("-"," ",$q[1]);
    $state_info = $db->select("SELECT state_name,state_desc FROM states WHERE state_name='".$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';
        //$counties = $db->select("SELECT DISTINCT CountyName FROM `zip-codes` WHERE StateFullName='".$db->escapeSting($state)."'");
        //echo "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";
        $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('-'," ",str_replace(".html","",$q[2]));
            $city_info =   $db->select("SELECT City,CountyName FROM `zip-codes` WHERE State='".$db->escapeSting($state)."' AND City='".$db->escapeSting($city)."'");
            //echo "SELECT ZipCode FROM  `zip-codes` WHERE State='".$db->escapeSting($state)."' AND PrimaryRecord = 'P' AND City='".$db->escapeSting($city)."'";
            //$ZipCodes = $db->select("SELECT ZipCode FROM  `zip-codes` WHERE State='".$db->escapeSting($state)."' AND PrimaryRecord = 'P' AND City='".$db->escapeSting($city)."'");
            $ZipCodes = $db2->select("select true_zip as ZipCode from houses h 
		where true_state='".$state_info['state_name']."' and true_city='".$city."' group by true_zip");

            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;
            }
            $tpl->assign("city",     $city);
            $tpl->assign("ZipCodes",   $ZipCodes);
        }
        if(isset($q[3])){
            $ZipCode = str_replace('-'," ",str_replace(".html","",$q[3]));

            $sql = "SELECT * FROM `zip-codes` WHERE State='".$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(getvar('City',$_GET,"")))." ".$db2->escapeSting(trim($_GET['Address']))."') ";
    }
    //$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.ID 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>";
    //if(!$houses){
    //echo $houses;
    // header("HTTP/1.0 404 Not Found");
    // header("Status: 404 Not Found");
    //}
    $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('.html','',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);
        if($house['agent']=='Jennifer Abke'){
            $agents = array();
            $house['agent']="";
        }

        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['for-sale'];
        }
        if($house['status']=="For Rent"&&!$category){
            $category = $categories['for-rent'];
        }

//	   $sql = "SELECT * FROM `zip-codes` WHERE State='".$db->escapeSting($house['state'])."' AND PrimaryRecord = 'P'  and ZipCode='".$db->escapeSting($house['true_zip'])."'";
//	   $ZipCode = $db->select_row($sql);
        $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(!$related_houses){
            $sql = "SELECT * FROM houses WHERE state='".$house['true_state']."' AND true_city='".$house['city']."' 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);
        }
        $tpl->assign("related_houses",  $related_houses);

        $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)){
    $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 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('".$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")."')");
}

//$states = $db->select("SELECT state_name,state_desc FROM states order by state_name");
//echo $not_found;
//print_r($states);
if (isset($states)){
    $tpl->assign("states",     $states);
}
$tpl->assign("content_type",   $content_type);
$tpl->assign("error_message", $error_message);
$tpl->assign("category",   $category);
$tpl->assign("categories",   $categories);
//$tpl->assign("default_category",   $default_category);
$tpl->display($template_file);
?>