// remove period from street names i.e. st. street
$keyword_sql .= "REPLACE(pro_addr3, '''', '') LIKE '%{$keyword}%' OR ";
$keyword_sql .= "pro_addr1 LIKE '%{$keyword}%' OR pro_addr2 LIKE '%{$keyword}%' OR pro_addr3 LIKE '%{$keyword}%' OR ";
$keyword_sql .= "pro_addr4 LIKE '%{$keyword}%' OR pro_addr5 LIKE '%{$keyword}%' OR pro_postcode LIKE '%{$keyword}%' OR ";
$keyword_sql .= "dea_keywords LIKE '%{$keyword}%' OR dea_strapline LIKE '%{$keyword}%' OR are_title LIKE '%{$keyword}%' OR ";
$keyword_sql .= "cli_fname LIKE '%{$keyword}%' OR cli_sname LIKE '%{$keyword}%' OR concat_ws(' ',cli_fname, cli_sname) LIKE '%{$keyword}%' OR ";
}
}
$keyword_sql = "(" . remove_lastchar($keyword_sql, "OR") . ") AND ";
$q[] = $keyword_sql;
}
}
if ($_GET["scope"] == "Lettings" && urldecode($_GET["term"]) == 'per month') {
$return["term"] = $_GET["term"];
$price_min = pcm2pw($_GET["price_min"]);
$price_max = pcm2pw($_GET["price_max"]);
} else {
$price_min = $_GET["price_min"];
$price_max = $_GET["price_max"];
}
if ($price_min && $price_min !== '(minimum)') {
$return["price_min"] = $_GET["price_min"];
$q[] = "(dea_marketprice IS NULL OR dea_marketprice > '" . $price_min . "') AND ";
}
if ($price_max && $price_max !== '(maximum)') {
$return["price_max"] = $_GET["price_max"];
$q[] = "(dea_marketprice IS NULL OR dea_marketprice < '" . $price_max . "') AND ";
}
if ($_GET["status"]) {
// using individual statuses disbales status_special
unset($_GET["status_special"]);
$return["keyword"] = $_GET["keyword"];
$keyword = str_replace(" ",",",$_GET["keyword"]);
$keywords = explode(",",$keyword);
foreach ($keywords AS $keyword) {
$keyword = trim($keyword);
$q[] = "(cli_fname LIKE '%$keyword%' OR cli_sname LIKE '%$keyword%' OR cli_email LIKE '%$keyword%') OR cli_id = '$keyword' AND ";
}
}
*/
if ($_GET["price"]) {
$price = numbers_only($_GET["price"]);
$return["price"] = $price;
$return["term"] = $_GET["term"];
if ($_GET["term"] == "pcm" && $scope == 'let') {
// only use term for lettings, per week by default
$price = pcm2pw($price);
}
$q[] = " (cli_" . $scope . "min <= {$price} AND cli_" . $scope . "max >= {$price}) AND ";
}
// type from simple select box - this is very messy, property types MUST be moved to link table
if ($_GET["type"]) {
$return["type"] = $_GET["type"];
// get property types in array
$sql_ptype = "SELECT * FROM ptype";
$q_ptype = $db->query($sql_ptype);
if (DB::isError($q_ptype)) {
die("db error: " . $q_ptype->getMessage() . $sql_ptype);
}
while ($row = $q_ptype->fetchRow()) {
if ($row["pty_type"] == 1) {
$house[] = $row["pty_id"];
function propertySearchLettings2()
{
global $db, $CONFIG;
// from search box
$strPropType = $_GET['t'];
$strPageLink = 't=' . $_GET['t'] . '&';
if ($strPropType == "House") {
$sql .= " (deal.dea_ptype = 1) AND ";
$strPropType = "House";
} elseif ($strPropType == "Apartment") {
$sql .= " (deal.dea_ptype = 2) AND ";
$strPropType = "Apartment";
} elseif ($strPropType == "Room") {
$sql .= " (deal.dea_psubtype = 26) AND ";
$strPropType = "Room";
} elseif ($strPropType == "Commercial") {
$sql .= " (deal.dea_ptype = 3) AND ";
$strPropType = "Commercial";
} else {
$strPropType = "Any";
}
if ($_GET['prices'] == 'pcm') {
$strPageLink .= 'prices=' . $_GET['prices'] . '&';
$strPrices = $_GET['prices'];
} else {
$strPrices = 'pw';
}
$minp = intval($_GET['minp']);
$maxp = intval($_GET['maxp']);
if ($strPrices == 'pcm') {
$minp_sql = pcm2pw($minp);
$maxp_sql = pcm2pw($maxp);
} else {
$minp_sql = $minp;
$maxp_sql = $maxp;
}
$strPageLink .= 'minp=' . $minp . '&';
if ($minp) {
$strMinPrice = $minp;
$sql .= " deal.dea_marketprice >= " . $minp_sql . " AND ";
}
$strPageLink .= 'maxp=' . $maxp . '&';
if ($maxp) {
$strMaxPrice = $maxp;
$sql .= " deal.dea_marketprice <= " . $maxp_sql . " AND ";
}
$minbeds = intval($_GET['minbeds']);
$strPageLink .= 'minbeds=' . $minbeds . '&';
if ($minbeds) {
$strMinBedrooms = $minbeds;
$sql .= " deal.dea_bedroom >= " . $minbeds . " AND ";
}
$maxbeds = $_GET['maxbeds'];
if ($_GET['maxbeds'] == '') {
$maxbeds = 99;
}
$strPageLink .= 'maxbeds=' . $maxbeds . '&';
if ($maxbeds || $maxbeds == 0) {
$strMaxBedrooms = $maxbeds;
$sql .= " deal.dea_bedroom <= " . $maxbeds . " AND ";
} else {
$strMaxBedrooms = 99;
}
$strKeywords = $_GET['kw'];
if ($strKeywords) {
$sqlKeyword = " ( ";
$strKeyword = explode(",", $strKeywords);
$kwCount = count($strKeyword);
for ($i = 0; $i <= $kwCount; $i++) {
if ($strKeyword[$i]) {
// if keyword exists
// fix for SE1 returning SE11, SE12 etc
if (trim(strtolower($strKeyword[$i])) == "se1") {
$sqlKeyword .= " property.pro_postcode LIKE 'SE1 %' OR ";
} else {
$sqlKeyword .= "\n\t\t\tproperty.pro_id LIKE '%" . trim($strKeyword[$i]) . "%' OR\n\t\t\tproperty.pro_addr1 LIKE '%" . trim($strKeyword[$i]) . "%' OR\n\t\t\tproperty.pro_addr3 LIKE '%" . trim($strKeyword[$i]) . "%' OR\n\t\t\tdeal.dea_strapline LIKE '%" . trim($strKeyword[$i]) . "%' OR\n\t\t\tproperty.pro_postcode LIKE '%" . $strKeyword[$i] . "%' OR\n\t\t\tarea.are_title LIKE '%" . trim($strKeyword[$i]) . "%' OR\n\t\t\tdeal.dea_keywords LIKE '%" . trim($strKeyword[$i]) . "%' OR ";
}
}
}
$sqlKeyword = substr($sqlKeyword, 0, -3);
$sqlKeyword .= ") ";
$strPageLink .= 'kw=' . $strKeywords . '&';
$sql .= $sqlKeyword . " AND ";
}
$area_id = intval($_GET['area_id']);
if ($area_id) {
$strAreaID = $area_id;
$sql .= " deal.dea_area = " . $area_id . " AND ";
}
$strFeature = $_GET['feature'];
$strPageLink .= 'feature=' . $_GET['feature'] . '&';
if ($strFeature) {
$sql .= " (feature.fea_title = 'Student Friendly') AND ";
}
// Show by Status
$strStatus = $_GET['s'];
$strPageLink .= 's=' . $_GET['s'] . '&';
if ($strStatus == "Available") {
$sql .= " (deal.dea_status = 'Available') AND ";
$strStatus = "Available";
} else {
$sql .= " (deal.dea_status = 'Available' OR deal.dea_status = 'Under Offer' OR deal.dea_status = 'Under Offer with Other' OR deal.dea_status = 'Exchanged') AND ";
$strStatus = "All";
}
if ($_GET['branch'] == '1' || $_GET['branch'] == '2' || $_GET['branch'] == '5') {
$sql .= " deal.dea_branch = " . intval($_GET['branch']) . " AND ";
$strPageLink .= 'branch=' . $_GET['branch'] . '&';
}
if ($_GET['special'] == "map") {
$sql .= " property.pro_east =" . intval($_GET['osx']) . " AND property.pro_north =" . intval($_GET['osy']) . " AND ";
}
// Order records
$strOrderBy = $_GET['o'];
if ($strOrderBy == "Date") {
$order = " dea_launchdate DESC";
$strOrderBy = "Date";
} elseif ($strOrderBy == "Street") {
$order = " property.pro_addr3 ASC, dea_launchdate DESC";
$strOrderBy = "Street";
} elseif ($strOrderBy == 'PriceASC') {
$order = " deal.dea_marketprice ASC, dea_launchdate DESC";
$strOrderBy = "PriceASC";
} else {
$order = " deal.dea_marketprice DESC, dea_launchdate DESC";
$strOrderBy = "Price";
}
$strPageLink .= 'o=' . $strOrderBy . '&';
$sql = remove_lastchar(trim($sql), "AND");
$sql = remove_lastchar(trim($sql), "OR");
$sql = "SELECT\nCONCAT(pro_addr3,' ',pro_addr4,' ',LEFT(pro_postcode, 4)) AS pro_addr,\npro_addr3,pro_postcode,are_title,\ndea_id,dea_type,dea_marketprice,dea_strapline,dea_status,dea_launchdate,\nmed_file,med_title\nFROM deal\n\n\nLEFT JOIN property ON deal.dea_prop = property.pro_id\nLEFT JOIN area ON property.pro_area = area.are_id\nLEFT JOIN media ON deal.dea_id = media.med_row AND media.med_table = 'deal' AND med_order = 1 AND med_type= 'Photograph'\n\nLEFT JOIN link_instruction_to_feature ON deal.dea_id = link_instruction_to_feature.dealId\nLEFT JOIN feature ON link_instruction_to_feature.featureId = feature.fea_id\nWHERE\n{$sql}\nAND deal.dea_type = 'Lettings' AND underTheRadar <> 1\nGROUP BY deal.dea_id\nORDER BY {$order}";
//echo $sql;
$q = $db->query($sql);
//if (DB::isError($q)) { die("db error: ".$q->getMessage().$sql); }
$numRows = $q->numRows();
if ($numRows !== 0) {
while ($row = $q->fetchRow()) {
// added 31/07/08 to fix shorter postcodes not displaying correctly
$pcSplit = explode(" ", $row['pro_postcode']);
$row['pro_postcode'] = $pcSplit[0];
if ($row['dea_status'] == 'Under Offer' || $row['dea_status'] == 'Under Offer with Other') {
$sot = 'LET S.T.C.';
} elseif ($row['dea_status'] == 'Exchanged') {
$sot = 'LET';
} else {
$sot = 'TO LET';
}
$propTitle = trim($row['pro_addr3'] . ', ' . $row['are_title'] . ' ' . $row['pro_postcode']);
// flag new properties
$newdate = strtotime(date('Y-m-j H:i:s')) - 18 * 24 * 60 * 60;
if ($row['dea_launchdate']) {
if (strtotime($row['dea_launchdate']) > $newdate) {
$class = ' class="new"';
$propTitle .= ' (New)';
}
}
$row['dea_strapline'] = str_replace(array('OSP', 'CHAIN FREE'), array('<abbr title="Off-street Parking">OSP</abbr>', '<abbr title="Offered with no forward chain">CHAIN FREE</abbr>'), $row['dea_strapline']);
if ($strPrices == "pcm") {
$price = format_price($row['dea_marketprice'] * 52 / 12) . 'pcm';
} else {
$price = format_price($row['dea_marketprice']) . 'p/w';
}
$data[] = '
<li>
<div class="thumbnail">
<a href="' . $CONFIG['SITE_URL'] . 'details/' . $row['dea_id'] . '.html"><img src="' . IMAGE_URL_PROPERTY . $row['dea_id'] . '/' . str_replace('.jpg', '_thumb1.jpg', $row['med_file']) . '" alt="' . $row['pro_addr3'] . '" /></a>
</div>
<div class="details">
<h2' . $class . '><a href="' . $CONFIG['SITE_URL'] . 'details/' . $row['dea_id'] . '.html" title="' . $row['pro_addr3'] . '"><span class="price">' . $price . '</span> ' . $propTitle . ' </a></h2>
<p class="sot">' . $sot . '</p>
<p class="strapline">' . $row['dea_strapline'] . '</p>
<p class="link"><a href="' . $CONFIG['SITE_URL'] . 'details/' . $row['dea_id'] . '.html">Full details</a></p>
</div>
</li>
';
unset($class);
}
}
require_once 'Pager/Pager.php';
$params = array('mode' => 'Jumping', 'perPage' => 15, 'delta' => 100, 'itemData' => $data, 'path' => $CONFIG['SITE_URL'] . 'test.html', 'append' => true, 'fileName' => '?pageID=%d');
$pager =& Pager::factory($params);
$data = $pager->getPageData();
$links = $pager->getLinks();
if ($pager->numItems()) {
if (!$links['back']) {
$back = "";
} else {
$back = '<a href="' . $CONFIG['SITE_URL'] . 'test.html?pageID=' . $pager->getPreviousPageID() . '&' . $strPageLink . '">« Prev Page</a>';
}
if (!$links['next']) {
$next = "";
} else {
$next = '<a href="' . $CONFIG['SITE_URL'] . 'test.html?pageID=' . $pager->getNextPageID() . '&' . $strPageLink . '">Next Page »</a>';
}
$pages = '
<p>Found ' . $pager->numItems() . ' Properties</p>
';
if ($pager->numPages() > 1) {
$pages .= '<p>Page: ' . str_replace(array(' ', '/index.php'), array(' ', ''), $links['pages']) . '</p>';
}
$top = '
<div class="top">
<p class="right">' . $back . ' ' . $next . '</p>
<p>Page: ' . $pager->getCurrentPageID() . ' of ' . $pager->numPages() . '</p>
</div>
';
foreach ($data as $output) {
$results .= $output;
}
$bottom = '
<div class="bottom">
<p class="right">' . $back . ' ' . $next . '</p>
<p>Page: ' . $pager->getCurrentPageID() . ' of ' . $pager->numPages() . '</p>
</div>
';
} else {
// no results
// no results
// pages table cells
$pages = '<p>Found 0 Properties</p>';
// no results message
$results = '
<li>
<h2>No Records Found</h2>
<p>Your search returned no records.</p>
<p>Please refine your search and try again.</p>
<p>Click <a href="' . $CONFIG['SITE_URL'] . 'lettings.html">here</a> to view all property
for sale, or click <a href="javascript:history.go(-1);">here</a>
to go back.</p>
</li>';
}
$pageLink = $CONFIG['SITE_URL'] . 'lettings.html?pageID=' . $_GET['pageID'] . '&' . $strPageLink;
$_SESSION['searchPage'] = $pageLink;
// build the form fields.
$branches = array(1 => 'Camberwell', 2 => 'Sydenham');
foreach ($branches as $key => $val) {
$render_branches .= '<option value="' . $key . '"';
if ($key == $_GET['branch']) {
$render_branches .= ' selected="selected"';
}
$render_branches .= '>' . $val . '</option>' . "\n";
}
$propertytypes = array('Any' => 'Any', 'House' => 'House', 'Apartment' => 'Apartment', 'Room' => 'House/Flat Share');
foreach ($propertytypes as $key => $val) {
$render_propertytypes .= '<option value="' . $key . '"';
if ($key == $strPropType) {
$render_propertytypes .= ' selected="selected"';
}
$render_propertytypes .= '>' . $val . '</option>' . "\n";
}
$minprices_pcm = array(400, 500, 600, 700, 800, 900, 1000, 1100, 1200, 1300, 1400, 1500, 1600, 1700, 1800, 1900, 2000, 2100, 2200, 2300, 2400, 2500, 2750, 3000, 3250, 3500);
foreach ($minprices_pcm as $key) {
$render_minprices_pcm .= '<option value="' . $key . '"';
if ($key == $strMinPrice) {
$render_minprices_pcm .= ' selected="selected"';
}
$render_minprices_pcm .= '>' . format_price($key) . '</option>';
$render_minprices_pcm_js .= 'AddToOptionList(document.WS_Form.minp, "' . $key . '", "\\u00A3' . number_format($key, 0, '', ',') . '");';
}
$maxprices_pcm = array(400, 500, 600, 700, 800, 900, 1000, 1100, 1200, 1300, 1400, 1500, 1600, 1700, 1800, 1900, 2000, 2100, 2200, 2300, 2400, 2500, 2750, 3000, 3250, 3500);
foreach ($maxprices_pcm as $key) {
$render_maxprices_pcm .= '<option value="' . $key . '"';
if ($key == $strMaxPrice) {
$render_maxprices_pcm .= ' selected="selected"';
}
$render_maxprices_pcm .= '>' . format_price($key) . '</option>';
$render_maxprices_pcm_js .= 'AddToOptionList(document.WS_Form.maxp, "' . $key . '", "\\u00A3' . number_format($key, 0, '', ',') . '");';
}
$minprices_pw = array(100, 150, 200, 250, 300, 350, 400, 450, 500, 600, 700, 800, 900, 1000, 1250, 1500, 2000);
foreach ($minprices_pw as $key) {
$render_minprices_pw .= '<option value="' . $key . '"';
if ($key == $strMinPrice) {
$render_minprices_pw .= ' selected="selected"';
}
$render_minprices_pw .= '>' . format_price($key) . '</option>';
$render_minprices_pw_js .= 'AddToOptionList(document.WS_Form.minp, "' . $key . '", "\\u00A3' . number_format($key, 0, '', ',') . '");';
}
$maxprices_pw = array(100, 150, 200, 250, 300, 350, 400, 450, 500, 600, 700, 800, 900, 1000, 1250, 1500, 2000);
foreach ($maxprices_pw as $key) {
$render_maxprices_pw .= '<option value="' . $key . '"';
if ($key == $strMaxPrice) {
$render_maxprices_pw .= ' selected="selected"';
}
$render_maxprices_pw .= '>' . format_price($key) . '</option>';
$render_maxprices_pw_js .= 'AddToOptionList(document.WS_Form.maxp, "' . $key . '", "\\u00A3' . number_format($key, 0, '', ',') . '");';
}
if ($_GET['prices'] == 'pcm') {
$render_maxprices = $render_maxprices_pcm;
$render_minprices = $render_minprices_pcm;
} else {
$render_maxprices = $render_maxprices_pw;
$render_minprices = $render_minprices_pw;
}
$minbedrooms = array(0 => 'Studio', 1 => 1, 2 => 2, 3 => 3, 4 => 4, 5 => 5);
foreach ($minbedrooms as $key => $val) {
$render_minbedrooms .= '<option value="' . $key . '"';
if ($key == $strMinBedrooms) {
$render_minbedrooms .= ' selected="selected"';
}
$render_minbedrooms .= '>' . $val . '</option>' . "\n";
}
$maxbedrooms = array(99 => 'Max', 0 => 'Studio', 1 => 1, 2 => 2, 3 => 3, 4 => 4, 5 => 5);
foreach ($maxbedrooms as $key => $val) {
$render_maxbedrooms .= '<option value="' . $key . '"';
if ($key == $strMaxBedrooms) {
$render_maxbedrooms .= ' selected="selected"';
}
$render_maxbedrooms .= '>' . $val . '</option>' . "\n";
}
/*
$sortbys = array('Price'=>'Price','Date'=>'Date','Street'=>'Street');
foreach ($sortbys as $key=>$val) {
$render_sortbys .= '<label for="'.$key.'"><input type="radio" name="o" value="'.$key.'" id="'.$key.'"';
if ($key == $strOrderBy) {
$render_sortbys .= ' checked="checked"';
}
$render_sortbys .= ' />'.$val.'</label> '."\n";
}
*/
$sortbys = array('Price' => 'Price', 'Date' => 'Date', 'Street' => 'Street');
$sortbys = array('Price' => 'Price (highest first)', 'PriceASC' => 'Price (lowest first)', 'Date' => 'Date', 'Street' => 'Street');
foreach ($sortbys as $key => $val) {
$render_sortbys .= '<option value="' . $key . '"';
if ($key == $strOrderBy) {
$render_sortbys .= ' selected="selected"';
}
$render_sortbys .= '>' . $val . '</option>' . "\n";
}
$shows = array('All' => 'All', 'Available' => 'Available');
foreach ($shows as $key => $val) {
$render_shows .= '<label for="' . $key . '"><input type="radio" name="s" value="' . $key . '" id="' . $key . '"';
if ($key == $strStatus) {
$render_shows .= ' checked="checked"';
}
$render_shows .= ' />' . $val . '</label> ' . "\n";
}
$priceFormats = array('pw' => 'Per Week', 'pcm' => 'Per Month');
foreach ($priceFormats as $key => $val) {
$render_prices .= '<label for="' . $key . '"><input type="radio" name="prices" value="' . $key . '" id="' . $key . '"';
if ($key == $strPrices) {
$render_prices .= ' checked="checked"';
}
$render_prices .= ' />' . $val . '</label> ' . "\n";
}
if ($_GET['feature']) {
$checkStudent = ' checked="checked"';
}
$form = '
<script type="text/javascript">
function ClearOptions(OptionList) {
for (x = OptionList.length; x >= 0; x--) {
OptionList[x] = null;
}
}
function AddToOptionList(OptionList, OptionValue, OptionText) {
OptionList[OptionList.length] = new Option(OptionText, OptionValue);
}
function PopulatePrices() {
var minp = document.WS_Form.minp;
var maxp = document.WS_Form.maxp;
// Clear out the list
ClearOptions(document.WS_Form.minp);
ClearOptions(document.WS_Form.maxp);
if ($("input:radio[name=prices]:checked").val() == "pw") {
AddToOptionList(document.WS_Form.minp, "0", "No Minimum");
' . $render_minprices_pw_js . '
AddToOptionList(document.WS_Form.maxp, "999999999", "No Maximum");
' . $render_maxprices_pw_js . '
}
if ($("input:radio[name=prices]:checked").val() == "pcm") {
AddToOptionList(document.WS_Form.minp, "0", "No Minimum");
' . $render_minprices_pcm_js . '
AddToOptionList(document.WS_Form.maxp, "999999999", "No Maximum");
' . $render_maxprices_pcm_js . '
}
}
$(document).ready(function(){
$("input:radio[name=prices]").change(function(){
PopulatePrices();
});
});
</script>
<form method="get" name="WS_Form" >
<p>Type of Property<br/>
<select name="t" class="formwide">
' . $render_propertytypes . '
</select> </p>
<p>Minimum Price<br/>
<select class="formwide" onchange="WSMax(this.form,0);" name="minp" id="minp">
<option value="0">No Minimum</option>
' . $render_minprices . '
</select></p>
<p>Maximum Price<br/>
<select class="formwide" onchange="WSMin(this.form,0);" name="maxp" id="maxp">
<option value="999999999">No Maximum</option>
' . $render_maxprices . '
</select></p>
<p>Display Prices<br/>
' . $render_prices . '
</p>
<p>Bedrooms<br/>
<select name="minbeds" class="formnarrow">
' . $render_minbedrooms . '
</select> to
<select name="maxbeds" class="formnarrow">
' . $render_maxbedrooms . '
</select></p>
<p>Street or Area<br/>
<input type="text" name="kw" class="formwide" value="' . $strKeywords . '" style="width:177px;" /></p>
<p>Sort By<br/>
<select name="o" class="formwide">
' . $render_sortbys . '
</select></p>
<p>Show<br/>
' . $render_shows . '</p>
<p><label><input type="checkbox" name="feature" value="Student Friendly"' . $checkStudent . ' />Student Friendly </label></p>
<div class="button"><input type="submit" value="Search" style="width: 100px; font-weight: bold;" /></div>
<div class="button"><input type="button" style="width: 100px" value="Reset" onclick="WSReset();" /></div>
</form>';
return '
<div id="searchResults">
' . $top . '
<ul>
' . $results . '
</ul>
' . $bottom . '
</div>
<div id="rightColumn">
<h5>Property Search Results</h5>
<div class="box pagination">' . $pages . '</div>
<h5>Property Search Lettings</h5>
' . $form . '
</div>
';
}
