/**
* Build a sql query - quick limits, quick order by
*
* @param int $ThisService Service/Module
* @param string $Table sql table
* @param string $CustomWhere sql where
* @param string $OrderBy sql orderby
* @param mixed $arVar misc vars
* @return string query put together
*/
function fun_SQLQuery_Builder($ThisService, $Table, $ID_Name, $ID, $CustomWhere, $OrderBy, $arVar)
{
//init vars
global $WhereQuery;
global $LayID;
//Comes From view.php,edit.php,... is ServiceID - makes for unique var for each service that is laid out on page
//Fix up order by so something can't be snuck in there
//Fix up Limit ints to, so something can't be snuck in there.
/************************************************************************/
/************************************************************************/
//WhereQuery Setup - By Account - or do I need to skip
if (preg_match("/listing/i", $Table)) {
//skip on purpose - b/c its not narrowed down by hostid
} elseif (preg_match("/gv_todo/i", $Table)) {
} else {
//Always narrow down by HostID - or error
$arQueryAnd[] = $WhereQuery;
}
//ID - Narrow down by ID
if ($ID) {
$arQueryAnd[] = "({$ID_Name}='{$ID}')";
}
//CustomWhere - drilldown by
if ($CustomWhere) {
$arQueryAnd[] = $CustomWhere;
}
///////////////////////////////////////
//Put them together
//And Queries
if ($arQueryAnd) {
$arQueryCombine[] = fun_MySQL_Query_CombineWhere($Type = "AND", $arQueryAnd);
}
//Or Queries
if ($arQueryOr) {
$arQueryCombine[] = fun_MySQL_Query_CombineWhere($Type = "OR", $arQueryOr);
}
//Combine Where Queries
if ($arQueryCombine) {
$strQueryWhereCombine = fun_MySQL_Query_CombineWhere($Type = "AND", $arQueryCombine);
}
/************************************************************************/
/************************************************************************/
/************************************************************************/
/************************************************************************/
//LIMIT Query - Each service/query can be limited differently
$strS1 = "S{$LayID}";
$strL1 = "L{$LayID}";
if ($_SESSION[$strS1]) {
$S1 = $_SESSION[$strS1];
}
if ($_SESSION[$strL1]) {
$L1 = $_SESSION[$strL1];
}
if ($_REQUEST[$strS1] == 0 or $_REQUEST[$strS1] != "") {
$S1 = $_REQUEST[$strS1];
$_SESSION[$strS1] = $S1;
}
if ($_REQUEST[$strL1]) {
$L1 = $_REQUEST[$strL1];
$_SESSION[$strL1] = $L1;
}
//LIMIT: set up the query part -> LIMIT RowStart, HowMany
if ($S1 or $L1) {
if (!$S1) {
$S1 = 0;
}
if (!$L1) {
$L1 = 25;
}
//make sure they are ints
settype($L1, "integer");
settype($S1, "integer");
$HowMany = $L1 - $S1;
$strLimit = " LIMIT {$S1},{$HowMany}; ";
}
/************************************************************************/
/************************************************************************/
/************************************************************************/
/************************************************************************/
//Build Entire Query
$strQuery = "SELECT * FROM {$Table}";
//Where
if ($strQueryWhereCombine) {
$strQuery .= " WHERE {$strQueryWhereCombine} ";
}
//Order By
if ($OrderBy) {
$strQuery .= " {$OrderBy} ";
}
//Limit
if ($strLimit) {
$strQuery .= " {$strLimit} ";
}
/************************************************************************/
/************************************************************************/
unset($arVar);
//debug
//echo $rtnQuery;
return $strQuery;
}
/**
* Build NWMLS listing Query
*
*
* @return string where query
*/
public function fun_NWMLS_Search_Query_Listing($arVar)
{
$Count = $this->CleanVar($arVar['Count']);
//count listings instead of show them
$Status = $this->CleanVar($arVar['Status']);
$PropertyType = $this->CleanVar($arVar['PropertyType']);
//array
$LNs = $this->CleanVar($arVar['LNs']);
//array
$Street = $this->CleanVar($arVar['Street']);
$City = $this->CleanVar($arVar['City']);
$State = $this->CleanVar($arVar['State']);
$Zip = $this->CleanVar($arVar['Zip']);
$PriceFrom = $this->CleanVar($arVar['PriceFrom']);
$PriceTo = $this->CleanVar($arVar['PriceTo']);
$HomeSizeFrom = $this->CleanVar($arVar['HomeSizeFrom']);
$HomeSizeTo = $this->CleanVar($arVar['HomeSizeTo']);
$LandSizeFrom = $this->CleanVar($arVar['LandSizeFrom']);
$LandSizeTo = $this->CleanVar($arVar['LandSizeTo']);
$Bed = $this->CleanVar($arVar['Bed']);
$Bath = $this->CleanVar($arVar['Bath']);
$AgentID = $this->CleanVar($arVar['AgentID']);
$SearchID = $this->CleanVar($arVar['SearchID']);
//used for saved queries, 1=featured
$query = "";
/* debug
foreach ($arVar as $Key => $Value)
{
if ($Value)
{
echo "Key($Key) Val($Value)<br>\n";
}
}*/
//print_r($arVar);
//ST
if (is_array($Status)) {
foreach ($Status as $strStatus) {
if ($strStatus) {
$arST[] = "(ST='{$strStatus}')";
}
}
$strStatus = fun_MySQL_Query_CombineWhere($Type = "OR", $arST);
if ($strStatus) {
$arQueryA[] = "({$strStatus})";
}
} elseif ($Status) {
$arQueryA[] = "(ST='{$Status}')";
}
//LN
if (is_array($LNs)) {
foreach ($LNs as $LN) {
if ($LN) {
$arLN[] = "(LN='{$LN}')";
}
}
$strLN = fun_MySQL_Query_CombineWhere($Type = "OR", $arLN);
if ($strLN) {
$arQueryA[] = "({$strLN})";
}
} elseif ($LNs) {
$arQueryA[] = "(LN='{$LNs}')";
}
//PTYP
if (is_array($PropertyType)) {
foreach ($PropertyType as $pType) {
$arPTYP[] = "(PTYP='{$pType}')";
}
$strPTYP = fun_MySQL_Query_CombineWhere($Type = "OR", $arPTYP);
if ($strPTYP) {
$arQueryA[] = "({$strPTYP})";
}
} elseif ($PropertyType) {
$arQueryA[] = "(PTYP='{$PropertyType}')";
}
//ADDRESS
if ($Street) {
$arQueryA[] = "(STR like '%{$Street}%')";
}
if ($City) {
$arQueryA[] = "(CIT like '{$City}')";
}
if ($Zip) {
$arQueryA[] = "(ZIP like '{$Zip}%')";
}
if ($PriceFrom or $PriceTo) {
if ($PriceFrom) {
$arPrice[] = "(LP >= '{$PriceFrom}')";
}
if ($PriceTo) {
$arPrice[] = "(LP <= '{$PriceTo}')";
}
if ($arPrice) {
$strPrice = implode(" AND ", $arPrice);
}
$arQueryA[] = "({$strPrice})";
}
//ASF
if ($HomeSizeFrom or $HomeSizeTo) {
if ($HomeSizeFrom) {
$arHS[] = "(ASF >= '{$HomeSizeFrom}')";
}
if ($HomeSizeTo) {
$arHS[] = "(ASF <= '{$HomeSizeTo}')";
}
if ($arHS) {
$strHS = implode(" AND ", $arHS);
}
$arQueryA[] = "({$strHS})";
}
//LSF
if ($LandSizeFrom or $LandSizeTo) {
//acres
$LandSizeFrom = $LandSizeFrom * 43560;
$LandSizeTo = $LandSizeTo * 43560;
if ($LandSizeFrom) {
$arLS[] = "(LSF >= '{$LandSizeFrom}')";
}
if ($LandSizeTo) {
$arLS[] = "(LSF <= '{$LandSizeTo}')";
}
if ($arLS) {
$strLS = implode(" AND ", $arLS);
}
$arQueryA[] = "({$strLS})";
}
//BR
if ($Bed) {
$arQueryA[] = "(BR >= '{$Bed}')";
}
//BTH
if ($Bath) {
$arQueryA[] = "(BTH >= '{$Bath}')";
}
//LAG, CLA
if ($AgentID) {
$arQueryA[] = "(LAG IN ({$AgentID}) OR CLA IN ({$AgentID}))";
}
//end of logic
///////////////////////////////
///////////////////////////////
if ($Count) {
$strField = " COUNT(ListingID) AS Total ";
//count the listings in query
} else {
$strField = " * ";
//select all
}
//print_r($arQueryA);
//Saved Searches
if ($arVar['SearchID']) {
$strSearch = $this->fun_NWMLS_Search_Query_SearchID($SearchID);
if ($strSearch) {
$arQueryA[] = $strSearch;
}
}
//print_r($arQueryA);
if ($arQueryA) {
$strQueryA = fun_MySQL_Query_CombineWhere($Type = "AND", $arQueryA);
$query .= " {$strQueryA}";
}
//debug
//$query .= " LIMIT 0,1000"; //Just in case
//echo "class_nwmls.php $query<br><br>";
return $query;
}
