databaseConnection = $this->databaseConnection();
}
/**
* Count the number of rows returned for the perform_search function
* @author Amy McCrobie
* @return int $listing_count a count of results returned from the search
*/
public function count_perform_search($property_type = '', $main_search, $price_sort, $date_sort,$new_filters=array()) {
//get post variables
$ne = '';
$nw = '';
$se = '';
$sw = '';
if(isset($_POST['ne'])){
$ne = $_POST['ne'];
}
if(isset($_POST['nw'])){
$nw = $_POST['nw'];
}
if(isset($_POST['se'])){
$se = $_POST['se'];
}
if(isset($_POST['sw'])){
$sw = $_POST['sw'];
}
//make some property type based decisions
switch ($property_type) {
case "residential":
$table = 'prop_residential';
//address, city, state, zipcode, zip, mls #, elementary school, middle school, high school
$search_fields = array('L_Address','L_City','L_State','L_ZipCode','L_Zip','L_ListingID');
break;
case "land":
$table = 'prop_land';
$search_fields = array('L_Address','L_City','L_State','L_ZipCode','L_Zip','L_ListingID');
break;
case "commercialLease":
$table = 'prop_commercialLease';
$search_fields = array('L_Address','L_City','L_State','L_ZipCode','L_Zip','L_ListingID');
break;
case "commercialSales":
$table = 'prop_commercialSales';
$search_fields = array('L_Address','L_City','L_State','L_ZipCode','L_Zip','L_ListingID');
break;
case "multiFamily":
$table = 'prop_multiFamily';
$search_fields = array('L_Address','L_City','L_State','L_ZipCode','L_Zip','L_ListingID');
break;
case "rental":
$table = 'prop_rental';
$search_fields = array('L_Address','L_City','L_State','L_ZipCode','L_Zip','L_ListingID');
break;
case "":
$table = 'prop_residential,prop_land,prop_commercialLease,prop_commercialSales,prop_multiFamily,prop_rental';
break;
default:
$table = 'prop_residential';
$search_fields = array('L_Address','L_City','L_State','L_ZipCode','L_Zip','L_ListingID');
}
//since we are just counting, only get the L_ListingID
$select = $table.'.L_ListingID';
//set variables
$where = '';
$open_house_selected = 0;
$price_reduced_selected = 0;
$new_listing_selected = 0;
//deal with where clause for main search
if ($main_search != '') {
$main_search_arr = explode(',',$main_search);
$main_search_count = 0;
//if the main_search array has more than one item to search by
if (count($main_search_arr) > 1) {
foreach ($main_search_arr as $main_search2) {
//if we are on the first iteration through main_search_arr
if ($main_search_count == 0) {
//break apart extended searches in $main_search2
$kv_pair_array = explode('/',$main_search2);
//if main_search2 is an extended search: "searchDescription/search"
if (count($kv_pair_array) > 1) {
$field = $kv_pair_array[0];
$value = $kv_pair_array[1];
if ($field == 'selected_subcategory_residential') {
if ($table == 'prop_residential') {
$residential_array = explode('~',$value);
//selected_subcategory_residential will only contain one value
if (count($residential_array) > 1) {
$residential_count = 0;
$where .= '(';
foreach($residential_array as $residential) {
if ($residential_count == 0) {
$where .= "`".$table."`.`L_Type_` LIKE '".trim($residential)."%'";
} else {
$where .= " OR `".$table."`.`L_Type_` LIKE '".trim($residential)."%'";
}
$residential_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`L_Type_` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_subcategory_commercial_sale') {
if ($table == 'prop_commercialLease' || $table == 'prop_commercialSales') {
$commercial_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($commercial_array) > 1) {
$commercial_count = 0;
$where .= '(';
foreach($commercial_array as $commercial) {
if ($commercial_count == 0) {
$where .= "`".$table."`.`L_Type_` LIKE '".trim($commercial)."%'";
} else {
$where .= " OR `".$table."`.`L_Type_` LIKE '".trim($commercial)."%'";
}
$commercial_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`L_Type_` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_level') {
if ($table == 'prop_residential' || $table == 'prop_rental') {
$level_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($level_array) > 1) {
$level_count = 0;
$where .= '(';
foreach($level_array as $level) {
if ($level_count == 0) {
$where .= "`".$table."`.`LM_Char10_7` = '".trim($level)."'";
} else {
$where .= " OR `".$table."`.`LM_Char10_7` = '".trim($level)."'";
}
$level_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_7` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_builder') {
if ($table == 'prop_residential' ) {
$builder_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($builder_array) > 1) {
$builder_count = 0;
$where .= '(';
foreach($builder_array as $builder) {
if ($builder_count == 0) {
$where .= "`".$table."`.`LM_char50_11` = '".trim($builder)."'";
} else {
$where .= " OR `".$table."`.`LM_char50_11` = '".trim($builder)."'";
}
$builder_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`LM_char50_11` = '".trim($value)."'";
$where .= ')';
}
}
else{
return 0;
}
} elseif ($field == 'selected_area') {
$area_array = explode('~', $value);
if (count($area_array) > 1) {
$area_count = 0;
$where .= '(';
foreach ($area_array as $area) {
if ($area_count == 0) {
$where .= "`".$table."`.`L_Area` = '".trim($area)."'";
} else {
$where .= " OR `".$table."`.`L_Area` = '".trim($area)."'";
}
$area_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`L_Area` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'selected_mlsID') {
$mlsID_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($mlsID_array) > 1) {
$mlsID_count = 0;
$where .= '(';
foreach($mlsID_array as $mlsID) {
if ($mlsID_count == 0) {
$where .= "`".$table."`.`L_ListingID` = ".trim($mlsID)."";
} else {
$where .= " OR `".$table."`.`L_ListingID` = ".trim($mlsID)."";
}
$mlsID_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`L_ListingID` = ".trim($value)."";
$where .= ')';
}
} elseif ($field == 'selected_subcategory') {
if ($table == 'prop_residential') {
$subcategory_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($subcategory_array) > 1) {
$subcategory_count = 0;
$where .= '(';
foreach($subcategory_array as $subcategory) {
if ($subcategory_count == 0) {
$where .= "`".$table."`.`L_Type_` = '".trim($subcategory)."'";
} else {
$where .= " OR `".$table."`.`L_Type_` = '".trim($subcategory)."'";
}
$subcategory_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`L_Type_` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_school_elementary') {
if ($table == 'prop_residential') {
$school_elementary_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($school_elementary_array) > 1) {
$school_elementary_count = 0;
$where .= '(';
foreach($school_elementary_array as $school_elementary) {
if ($school_elementary_count == 0) {
$where .= "`".$table."`.`LM_Char10_5` = '".trim($school_elementary)."'";
} else {
$where .= " OR `".$table."`.`LM_Char10_5` = '".trim($school_elementary)."'";
}
$school_elementary_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_5` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_school_middle') {
if ($table == 'prop_residential') {
$school_middle_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($school_middle_array) > 1) {
$school_middle_count = 0;
$where .= '(';
foreach($school_middle_array as $school_middle) {
if ($school_middle_count == 0) {
$where .= "`".$table."`.`LM_Char10_8` = '".esc_sql(trim($school_middle))."'";
} else {
$where .= " OR `".$table."`.`LM_Char10_8` = '".esc_sql(trim($school_middle))."'";
}
$school_middle_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_8` LIKE '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_school_high') {
if ($table == 'prop_residential') {
$school_high_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($school_high_array) > 1) {
$school_high_count = 0;
$where .= '(';
foreach($school_high_array as $school_high) {
if ($school_high_count == 0) {
$where .= "`".$table."`.`LM_Char10_6` = '".trim($school_high)."'";
} else {
$where .= " OR `".$table."`.`LM_Char10_6` = '".trim($school_high)."'";
}
$school_high_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_6` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_subdivision') {
if ($table == 'prop_residential' || $table == 'prop_land' || $table == 'prop_multiFamily' || $table == 'prop_rental') {
$where .= '(';
if (trim($value) == 'None') {
$where .= "`".$table."`.`LM_char10_43` = '' OR `".$table."`.`LM_char10_43` = 'none' OR `".$table."`.`LM_char10_43` IS NULL";
} else {
$multiple_values = explode('~',trim($value));
if(count($multiple_values)){
$i = 1;
foreach($multiple_values as $multiple_value ){
$where .= "`".$table."`.`LM_char10_43` = '".trim($multiple_value)."'";
if($i !=count($multiple_values)){
$where .= " OR ";
$i++;
}
}
}else{
$where .= "`".$table."`.`LM_char10_43` = '".trim($value)."'";
}
}
$where .= ')';
}
} elseif ($field == 'selected_land_description') {
if ($table == 'prop_land') {
$land_description_array = explode('~',$value);
//selected_land_description might contain one value, or several values
if (count($land_description_array) > 1) {
$land_count = 0;
$where .= '(';
foreach($land_description_array as $land_description) {
if ($land_count == 0) {
$where .= "`".$table."`.`LFD_LandDescription_64` LIKE '%".trim($land_description)."%'";
} else {
$where .= " AND `".$table."`.`LFD_LandDescription_64` LIKE '%".trim($land_description)."%'";
}
$land_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`LFD_LandDescription_64` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'price_range') {
$selected_price_array = explode('--',$value);
//price_range will always be two values
$where .= '(';
$price_min = $selected_price_array[0];
$price_max = $selected_price_array[1];
$where .= "`".$table."`.`L_AskingPrice` >= ".$price_min." AND `".$table."`.`L_AskingPrice` <= ".$price_max;
$where .= ')';
} elseif ($field == 'selected_bedrooms') {
if ($table == 'prop_residential' || $table == 'prop_rental') {
$where .= '(';
$where .= "`".$table."`.`LM_Int1_1` >= ".(int)$value;
$where .= ')';
}
} elseif ($field == 'selected_bathrooms') {
if ($table == 'prop_residential' || $table == 'prop_rental') {
$where .= '(';
$where .= "`".$table."`.`LM_Dec_2` >= ".(int)$value;
$where .= ')';
}
} elseif ($field == 'acreage') {
$selected_acreage_array = explode('--',$value);
//acreage will always be two values
$where .= '(';
$acreage_min = $selected_acreage_array[0];
$acreage_max = $selected_acreage_array[1];
$where .= "`".$table."`.`L_NumAcres` >= ".$acreage_min." AND `".$table."`.`L_NumAcres` <= ".$acreage_max;
$where .= ')';
} elseif ($field == 'square_feet') {
if ($table != 'prop_land' && $table != 'prop_multiFamily') {
$selected_square_feet_array = explode('--',$value);
//square_feet will always be two values
$where .= '(';
$square_feet_min = $selected_square_feet_array[0];
$square_feet_max = $selected_square_feet_array[1];
$where .= "`".$table."`.`L_SquareFeet` >= ".$square_feet_min." AND `".$table."`.`L_SquareFeet` <= ".$square_feet_max;
$where .= ')';
}
} elseif ($field == 'year_built') {
if ($table != 'prop_land') {
$selected_year_built_array = explode('--',$value);
//year_built will always be two values
$where .= '(';
$year_built_min = $selected_year_built_array[0];
$year_built_max = $selected_year_built_array[1];
$where .= "`".$table."`.`LM_Int2_2` >= ".$year_built_min." AND `".$table."`.`LM_Int2_2` <= ".$year_built_max;
$where .= ')';
}
} elseif ($field == 'selected_elem') {
if ($table != 'prop_commercialLease' && $table != 'prop_commercialSales') {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_5` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'selected_middle') {
if ($table != 'prop_commercialLease' && $table != 'prop_commercialSales') {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_8` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'selected_high') {
if ($table != 'prop_commercialLease' && $table != 'prop_commercialSales') {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_6` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'open_house_selected') {
$open_house_selected = 1;
} elseif ($field == 'price_reduced_selected') {
$price_reduced_selected = 1;
} elseif ($field == 'new_listing_selected') {
$new_listing_selected = 1;
} elseif ($field == 'acres_selected') {
$selected_acres_array = explode('--',$value);
//acres will always be two values
$where .= '(';
$acres_min = $selected_acres_array[0];
$acres_max = $selected_acres_array[1];
$where .= "`".$table."`.`L_NumAcres` >= ".$acres_min." AND `".$table."`.`L_NumAcres` <= ".$acres_max;
$where .= ')';
} elseif ($field == 'agent_selected') {
$agent_array = explode('~',$value);
if (count($agent_array) > 0){
$agent_count = 0;
$where .= '(';
foreach($agent_array as $agents) {
if ($agent_count == 0){
$where .= "`".$table."`.`L_ListAgent1` = '".trim($agents)."'";
}
else {
$where .= " OR `".$table."`.`L_ListAgent1` = '".trim($agents)."'";
}
$agent_count++;
}
$where .=')';
}
else{
$where .= '(';
$where .= "`".$table."`.`L_ListAgent1` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'office_selected') {
$office_array = explode('~',$value);
//office_selected might contain one value, or several values
if (count($office_array) > 0) {
$office_count = 0;
$where .= '(';
foreach($office_array as $office) {
if ($office_count == 0) {
if ($office == 'any') {
$where .= "`".$table."`.`L_ListOffice1` != ''";
} else {
$where .= "`".$table."`.`L_ListOffice1` = ".trim($office);
}
} else {
if ($office == 'any') {
$where .= " OR `".$table."`.`L_ListOffice1` != ''";
} else {
$where .= " OR `".$table."`.`L_ListOffice1` = ".trim($office);
}
}
$office_count++;
}
$where .= ')';
} else { // user has selected 'any'
$where .= '(';
$where .= "`".$table."`.`L_ListOffice1` != ''";
$where .= ')';
}
} elseif ($field == 'short_sale_selected') {
if ($table == 'prop_residential' || $table == 'prop_land') {
$where .= '(';
$where .= "`".$table."`.`LM_char1_57` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'foreclosure_selected') {
if ($table == 'prop_residential' || $table == 'prop_land') {
$where .= '(';
$where .= "`".$table."`.`LM_char1_21` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'listing_date_selected') {
$today = date('Y-m-d H:i:s');
$selected_listing_date_array = explode('--',$value);
//listing date will always be two values
$where .= '(';
$date_min_val = $selected_listing_date_array[0];
$date_max_val = $selected_listing_date_array[1];
if ($date_min_val == 0) {
$date_min = $today;
$date_max = date('Y-m-d H:i:s', strtotime($today .' -'.$date_max_val.' day'));
} else {
$date_min = date('Y-m-d H:i:s', strtotime($today .' -'.$date_min_val.' day'));
$date_max = date('Y-m-d H:i:s', strtotime($today .' -'.$date_max_val.' day'));
}
$where .= "`".$table."`.`L_ListingDate` <= ".$date_min." AND `".$table."`.`L_ListingDate` >= ".$date_max;
$where .= ')';
} elseif ($field == 'farm_features_selected') {
if ($table == 'prop_residential') {
$farm_features_array = explode('~',$value);
//farm_features_selected might contain one value, or several values
if (count($farm_features_array) > 0) {
$farm_features_count = 0;
$where .= '(';
foreach($farm_features_array as $farm_features) {
if ($farm_features_count == 0) {
if ($farm_features == 'any') {
$where .= "`".$table."`.`LFD_FarmFeatures_246` != ''";
} else {
$where .= "`".$table."`.`LFD_FarmFeatures_246` LIKE '%".trim($farm_features)."%'";
}
} else {
if ($farm_features == 'any') {
$where .= " AND `".$table."`.`LFD_FarmFeatures_246` != ''";
} else {
$where .= " AND `".$table."`.`LFD_FarmFeatures_246` LIKE '%".trim($farm_features)."%'";
}
}
$farm_features_count++;
}
$where .= ')';
} else { // user has selected 'any'
$where .= '(';
$where .= "`".$table."`.`LFD_FarmFeatures_246` != ''";
$where .= ')';
}
}
} elseif ($field == 'search_term_selected') {
$search_term_array = explode('~',$value);
//search_term_selected might contain one value, or several values
if (count($search_term_array) > 0) {
$search_term_count = 0;
$where .= '(';
foreach ($search_term_array as $search_term) {
if ($search_term != 'new construction' && $search_term != 'hoa') {
if ($search_term_count == 0) {
$where .= "`".$table."`.`LR_remarks22` LIKE '%".trim($search_term)."%'";
} else {
$where .= " AND `".$table."`.`LR_remarks22` LIKE '%".trim($search_term)."%'";
}
} else {
if ($search_term == 'new construction') {
if ($table == 'prop_residential' || $table == 'prop_multiFamily') {
if ($search_term_count == 0) {
$where .= "`".$table."`.`LM_char1_22` = 'Yes'";
} else {
$where .= " AND `".$table."`.`LM_char1_22` = 'Yes'";
}
}
} elseif ($search_term == 'hoa') {
if ($table != 'prop_commercialLease' && $table != 'prop_commercialSales') {
if ($search_term_count == 0) {
$where .= "`".$table."`.`LM_Char1_18` = 'Yes'";
} else {
$where .= " AND `".$table."`.`LM_Char1_18` = 'Yes'";
}
}
}
}
$search_term_count++;
}
$where .= ')';
}
} elseif ($field == 'max_year_built_selected') {
$max_year_built_array = explode('~',$value);
//max_year_built_selected might contain one value, or several values
if (count($max_year_built_array) > 0) {
$max_year_built_count = 0;
$where .= '(';
foreach ($max_year_built_array as $max_year_built) {
$year_built_min = 0;
$where .= "`".$table."`.`LM_Int2_2` >= ".$year_built_min." AND `".$table."`.`LM_Int2_2` <= ".$max_year_built;
$where .= ')';
}
}
}
//main_search2 is not an exploded array
} else {
$search_count = 0;
foreach ($search_fields as $field) {
if ($search_count == 0) {
$where .= "(`".$table."`.`".$field."` LIKE '%".trim($main_search2)."%'";
} else {
$where .= " OR `".$table."`.`".$field."` LIKE '%".trim($main_search2)."%'";
}
$search_count++;
}
$where .= ')';
}
////if we are on the 2nd or more iteration through main_search_arr
} elseif ($main_search_count > 0) {
//break apart extended searches in $main_search2
$kv_pair_array = explode('/',$main_search2);
//if main_search2 is an extended search: "searchDescription/search"
if (count($kv_pair_array) > 1) {
$field = $kv_pair_array[0];
$value = $kv_pair_array[1];
if ($where != '') {
if ($field == 'open_house_selected' || ($field == 'selected_builder') || $field == 'price_reduced_selected' || $field == 'new_listing_selected' || ($field == 'farm_features_selected' && $table != 'prop_residential')) {
$where .= '';
} else {
$where .= ' AND ';
}
}
if ($field == 'selected_subcategory_residential') {
if ($table == 'prop_residential') {
$residential_array = explode('~',$value);
//selected_subcategory_residential will only contain one value
if (count($residential_array) > 1) {
$residential_count = 0;
$where .= '(';
foreach($residential_array as $residential) {
if ($residential_count == 0) {
$where .= "`".$table."`.`L_Type_` LIKE '".trim($residential)."%'";
} else {
$where .= " OR `".$table."`.`L_Type_` LIKE '".trim($residential)."%'";
}
$residential_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`L_Type_` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_subcategory_commercial_sale') {
if ($table == 'prop_commercialLease' || $table == 'prop_commercialSales') {
$commercial_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($commercial_array) > 1) {
$commercial_count = 0;
$where .= '(';
foreach($commercial_array as $commercial) {
if ($commercial_count == 0) {
$where .= "`".$table."`.`L_Type_` LIKE '".trim($commercial)."%'";
} else {
$where .= " OR `".$table."`.`L_Type_` LIKE '".trim($commercial)."%'";
}
$commercial_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`L_Type_` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_level') {
if ($table == 'prop_residential' || $table == 'prop_rental') {
$level_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($level_array) > 1) {
$level_count = 0;
$where .= '(';
foreach($level_array as $level) {
if ($level_count == 0) {
$where .= "`".$table."`.`LM_Char10_7` = '".trim($level)."'";
} else {
$where .= " OR `".$table."`.`LM_Char10_7` = '".trim($level)."'";
}
$level_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_7` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_builder') {
if ($table == 'prop_residential' ) {
$builder_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($builder_array) > 1) {
$builder_count = 0;
$where .= ' AND (';
foreach($builder_array as $builder) {
if ($builder_count == 0) {
$where .= "`".$table."`.`LM_char50_11` = '".trim($builder)."'";
} else {
$where .= " OR `".$table."`.`LM_char50_11` = '".trim($builder)."'";
}
$builder_count++;
}
$where .= ')';
} else {
$where .= ' AND (';
$where .= "`".$table."`.`LM_char50_11` = '".trim($value)."'";
$where .= ')';
}
}
else{
return 0;
}
} elseif ($field == 'selected_area') {
$area_array = explode('~', $value);
if (count($area_array) > 1) {
$area_count = 0;
$where .= '(';
foreach ($area_array as $area) {
if ($area_count == 0) {
$where .= "`".$table."`.`L_Area` = '".trim($area)."'";
} else {
$where .= " OR `".$table."`.`L_Area` = '".trim($area)."'";
}
$area_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`L_Area` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'selected_mlsID') {
$mlsID_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($mlsID_array) > 1) {
$mlsID_count = 0;
$where .= '(';
foreach($mlsID_array as $mlsID) {
if ($mlsID_count == 0) {
$where .= "`".$table."`.`L_ListingID` = ".trim($mlsID)."";
} else {
$where .= " OR `".$table."`.`L_ListingID` = ".trim($mlsID)."";
}
$mlsID_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`L_ListingID` = ".trim($value)."";
$where .= ')';
}
} elseif ($field == 'selected_subcategory') {
if ($table == 'prop_residential') {
$subcategory_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($subcategory_array) > 1) {
$subcategory_count = 0;
$where .= '(';
foreach($subcategory_array as $subcategory) {
if ($subcategory_count == 0) {
$where .= "`".$table."`.`L_Type_` = '".trim($subcategory)."'";
} else {
$where .= " OR `".$table."`.`L_Type_` = '".trim($subcategory)."'";
}
$subcategory_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`L_Type_` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_school_elementary') {
if ($table == 'prop_residential') {
$school_elementary_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($school_elementary_array) > 1) {
$school_elementary_count = 0;
$where .= '(';
foreach($school_elementary_array as $school_elementary) {
if ($school_elementary_count == 0) {
$where .= "`".$table."`.`LM_Char10_5` = '".trim($school_elementary)."'";
} else {
$where .= " OR `".$table."`.`LM_Char10_5` = '".trim($school_elementary)."'";
}
$school_elementary_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_5` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_school_middle') {
if ($table == 'prop_residential') {
$school_middle_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($school_middle_array) > 1) {
$school_middle_count = 0;
$where .= '(';
foreach($school_middle_array as $school_middle) {
if ($school_middle_count == 0) {
$where .= "`".$table."`.`LM_Char10_8` = '".esc_sql(trim($school_middle))."'";
} else {
$where .= " OR `".$table."`.`LM_Char10_8` = '".esc_sql(trim($school_middle))."'";
}
$school_middle_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_8` LIKE '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_school_high') {
if ($table == 'prop_residential') {
$school_high_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($school_high_array) > 1) {
$school_high_count = 0;
$where .= '(';
foreach($school_high_array as $school_high) {
if ($school_high_count == 0) {
$where .= "`".$table."`.`LM_Char10_6` = '".trim($school_high)."'";
} else {
$where .= " OR `".$table."`.`LM_Char10_6` = '".trim($school_high)."'";
}
$school_high_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_6` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_subdivision') {
if ($table == 'prop_residential' || $table == 'prop_land' || $table == 'prop_multiFamily' || $table == 'prop_rental') {
$where .= '(';
if (trim($value) == 'None') {
$where .= "`".$table."`.`LM_char10_43` = '' OR `".$table."`.`LM_char10_43` = 'none' OR `".$table."`.`LM_char10_43` IS NULL";
} else {
$multiple_values = explode('~',trim($value));
if(count($multiple_values)){
$i = 1;
foreach($multiple_values as $multiple_value ){
$where .= "`".$table."`.`LM_char10_43` = '".trim($multiple_value)."'";
if($i !=count($multiple_values)){
$where .= " OR ";
$i++;
}
}
}else{
$where .= "`".$table."`.`LM_char10_43` = '".trim($value)."'";
}
}
$where .= ')';
}
} elseif ($field == 'selected_land_description') {
if ($table == 'prop_land') {
$land_description_array = explode('~',$value);
//selected_land_description might contain one value, or several values
if (count($land_description_array) > 1) {
$land_count = 0;
$where .= '(';
foreach($land_description_array as $land_description) {
if ($land_count == 0) {
$where .= "`".$table."`.`LFD_LandDescription_64` LIKE '%".trim($land_description)."%'";
} else {
$where .= " AND `".$table."`.`LFD_LandDescription_64` LIKE '%".trim($land_description)."%'";
}
$land_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`LFD_LandDescription_64` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'price_range') {
$selected_price_array = explode('--',$value);
//price_range will always be two values
$where .= '(';
$price_min = $selected_price_array[0];
$price_max = $selected_price_array[1];
$where .= "`".$table."`.`L_AskingPrice` >= ".$price_min." AND `".$table."`.`L_AskingPrice` <= ".$price_max;
$where .= ')';
} elseif ($field == 'selected_bedrooms') {
if ($table == 'prop_residential' || $table == 'prop_rental') {
$where .= '(';
$where .= "`".$table."`.`LM_Int1_1` >= ".(int)$value;
$where .= ')';
}
} elseif ($field == 'selected_bathrooms') {
if ($table == 'prop_residential' || $table == 'prop_rental') {
$where .= '(';
$where .= "`".$table."`.`LM_Dec_2` >= ".(int)$value;
$where .= ')';
}
} elseif ($field == 'acreage') {
$selected_acreage_array = explode('--',$value);
//acreage will always be two values
$where .= '(';
$acreage_min = $selected_acreage_array[0];
$acreage_max = $selected_acreage_array[1];
$where .= "`".$table."`.`L_NumAcres` >= ".$acreage_min." AND `".$table."`.`L_NumAcres` <= ".$acreage_max;
$where .= ')';
} elseif ($field == 'square_feet') {
if ($table != 'prop_land' && $table != 'prop_multiFamily') {
$selected_square_feet_array = explode('--',$value);
//square_feet will always be two values
$where .= '(';
$square_feet_min = $selected_square_feet_array[0];
$square_feet_max = $selected_square_feet_array[1];
$where .= "`".$table."`.`L_SquareFeet` >= ".$square_feet_min." AND `".$table."`.`L_SquareFeet` <= ".$square_feet_max;
$where .= ')';
}
} elseif ($field == 'year_built') {
if ($table != 'prop_land') {
$selected_year_built_array = explode('--',$value);
//year_built will always be two values
$where .= '(';
$year_built_min = $selected_year_built_array[0];
$year_built_max = $selected_year_built_array[1];
$where .= "`".$table."`.`LM_Int2_2` >= ".$year_built_min." AND `".$table."`.`LM_Int2_2` <= ".$year_built_max;
$where .= ')';
}
} elseif ($field == 'selected_elem') {
if ($table != 'prop_commercialLease' && $table != 'prop_commercialSales') {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_5` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'selected_middle') {
if ($table != 'prop_commercialLease' && $table != 'prop_commercialSales') {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_8` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'selected_high') {
if ($table != 'prop_commercialLease' && $table != 'prop_commercialSales') {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_6` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'open_house_selected') {
$open_house_selected = 1;
} elseif ($field == 'price_reduced_selected') {
$price_reduced_selected = 1;
} elseif ($field == 'new_listing_selected') {
$new_listing_selected = 1;
} elseif ($field == 'acres_selected') {
$selected_acres_array = explode('--',$value);
//acres will always be two values
$where .= '(';
$acres_min = $selected_acres_array[0];
$acres_max = $selected_acres_array[1];
$where .= "`".$table."`.`L_NumAcres` >= ".$acres_min." AND `".$table."`.`L_NumAcres` <= ".$acres_max;
$where .= ')';
} elseif ($field == 'agent_selected') {
$agent_array = explode('~',$value);
if (count($agent_array) > 0){
$agent_count = 0;
$where .= '(';
foreach($agent_array as $agents) {
if ($agent_count == 0){
$where .= "`".$table."`.`L_ListAgent1` = '".trim($agents)."'";
}
else {
$where .= " OR `".$table."`.`L_ListAgent1` = '".trim($agents)."'";
}
$agent_count++;
}
$where .=')';
}
else{
$where .= '(';
$where .= "`".$table."`.`L_ListAgent1` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'office_selected') {
$office_array = explode('~',$value);
//office_selected might contain one value, or several values
if (count($office_array) > 0) {
$office_count = 0;
$where .= '(';
foreach($office_array as $office) {
if ($office_count == 0) {
if ($office == 'any') {
$where .= "`".$table."`.`L_ListOffice1` != ''";
} else {
$where .= "`".$table."`.`L_ListOffice1` = ".trim($office);
}
} else {
if ($office == 'any') {
$where .= " OR `".$table."`.`L_ListOffice1` != ''";
} else {
$where .= " OR `".$table."`.`L_ListOffice1` = ".trim($office);
}
}
$office_count++;
}
$where .= ')';
} else { // user has selected 'any'
$where .= '(';
$where .= "`".$table."`.`L_ListOffice1` != ''";
$where .= ')';
}
} elseif ($field == 'short_sale_selected') {
if ($table == 'prop_residential' || $table == 'prop_land') {
$where .= '(';
$where .= "`".$table."`.`LM_char1_57` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'foreclosure_selected') {
if ($table == 'prop_residential' || $table == 'prop_land') {
$where .= '(';
$where .= "`".$table."`.`LM_char1_21` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'listing_date_selected') {
$today = date('Y-m-d H:i:s');
$selected_listing_date_array = explode('--',$value);
//listing date will always be two values
$where .= '(';
$date_min_val = $selected_listing_date_array[0];
$date_max_val = $selected_listing_date_array[1];
if ($date_min_val == 0) {
$date_min = $today;
$date_max = date('Y-m-d H:i:s', strtotime($today .' -'.$date_max_val.' day'));
} else {
$date_min = date('Y-m-d H:i:s', strtotime($today .' -'.$date_min_val.' day'));
$date_max = date('Y-m-d H:i:s', strtotime($today .' -'.$date_max_val.' day'));
}
$where .= "`".$table."`.`L_ListingDate` <= ".$date_min." AND `".$table."`.`L_ListingDate` >= ".$date_max;
$where .= ')';
} elseif ($field == 'farm_features_selected') {
if ($table == 'prop_residential') {
$farm_features_array = explode('~',$value);
//farm_features_selected might contain one value, or several values
if (count($farm_features_array) > 0) {
$farm_features_count = 0;
$where .= '(';
foreach($farm_features_array as $farm_features) {
if ($farm_features_count == 0) {
if ($farm_features == 'any') {
$where .= "`".$table."`.`LFD_FarmFeatures_246` != ''";
} else {
$where .= "`".$table."`.`LFD_FarmFeatures_246` LIKE '%".trim($farm_features)."%'";
}
} else {
if ($farm_features == 'any') {
$where .= " AND `".$table."`.`LFD_FarmFeatures_246` != ''";
} else {
$where .= " AND `".$table."`.`LFD_FarmFeatures_246` LIKE '%".trim($farm_features)."%'";
}
}
$farm_features_count++;
}
$where .= ')';
} else { // user has selected 'any'
$where .= '(';
$where .= "`".$table."`.`LFD_FarmFeatures_246` != ''";
$where .= ')';
}
}
} elseif ($field == 'search_term_selected') {
$search_term_array = explode('~',$value);
//search_term_selected might contain one value, or several values
if (count($search_term_array) > 0) {
$search_term_count = 0;
$where .= '(';
foreach ($search_term_array as $search_term) {
if ($search_term != 'new construction' && $search_term != 'hoa') {
if ($search_term_count == 0) {
$where .= "`".$table."`.`LR_remarks22` LIKE '%".trim($search_term)."%'";
} else {
$where .= " AND `".$table."`.`LR_remarks22` LIKE '%".trim($search_term)."%'";
}
} else {
if ($search_term == 'new construction') {
if ($table == 'prop_residential' || $table == 'prop_multiFamily') {
if ($search_term_count == 0) {
$where .= "`".$table."`.`LM_char1_22` = 'Yes'";
} else {
$where .= " AND `".$table."`.`LM_char1_22` = 'Yes'";
}
}
} elseif ($search_term == 'hoa') {
if ($table != 'prop_commercialLease' && $table != 'prop_commercialSales') {
if ($search_term_count == 0) {
function count_perform_search($property_type = '', $main_search, $price_sort, $date_sort,$new_filters=array()) {
//get post variables
$ne = '';
$nw = '';
$se = '';
$sw = '';
$ne = $_POST['ne'];
$nw = $_POST['nw'];
$se = $_POST['se'];
$sw = $_POST['sw'];
//make some property type based decisions
switch ($property_type) {
case "residential":
$table = 'prop_residential';
//address, city, state, zipcode, zip, mls #, elementary school, middle school, high school
$search_fields = array('L_Address','L_City','L_State','L_ZipCode','L_Zip','L_ListingID');
break;
case "land":
$table = 'prop_land';
$search_fields = array('L_Address','L_City','L_State','L_ZipCode','L_Zip','L_ListingID');
break;
case "commercialLease":
$table = 'prop_commercialLease';
$search_fields = array('L_Address','L_City','L_State','L_ZipCode','L_Zip','L_ListingID');
break;
case "commercialSales":
$table = 'prop_commercialSales';
$search_fields = array('L_Address','L_City','L_State','L_ZipCode','L_Zip','L_ListingID');
break;
case "multiFamily":
$table = 'prop_multiFamily';
$search_fields = array('L_Address','L_City','L_State','L_ZipCode','L_Zip','L_ListingID');
break;
case "rental":
$table = 'prop_rental';
$search_fields = array('L_Address','L_City','L_State','L_ZipCode','L_Zip','L_ListingID');
break;
case "":
$table = 'prop_residential,prop_land,prop_commercialLease,prop_commercialSales,prop_multiFamily,prop_rental';
break;
default:
$table = 'prop_residential';
$search_fields = array('L_Address','L_City','L_State','L_ZipCode','L_Zip','L_ListingID');
}
//since we are just counting, only get the L_ListingID
$select = $table.'.L_ListingID';
//set variables
$where = '';
$open_house_selected = 0;
$price_reduced_selected = 0;
$new_listing_selected = 0;
//deal with where clause for main search
if ($main_search != '') {
$main_search_arr = explode(',',$main_search);
$main_search_count = 0;
//if the main_search array has more than one item to search by
if (count($main_search_arr) > 1) {
foreach ($main_search_arr as $main_search2) {
//if we are on the first iteration through main_search_arr
if ($main_search_count == 0) {
//break apart extended searches in $main_search2
$kv_pair_array = explode('/',$main_search2);
//if main_search2 is an extended search: "searchDescription/search"
if (count($kv_pair_array) > 1) {
$field = $kv_pair_array[0];
$value = $kv_pair_array[1];
if ($field == 'selected_subcategory_residential') {
if ($table == 'prop_residential') {
$residential_array = explode('~',$value);
//selected_subcategory_residential will only contain one value
if (count($residential_array) > 1) {
$residential_count = 0;
$where .= '(';
foreach($residential_array as $residential) {
if ($residential_count == 0) {
$where .= "`".$table."`.`L_Type_` LIKE '".trim($residential)."%'";
} else {
$where .= " OR `".$table."`.`L_Type_` LIKE '".trim($residential)."%'";
}
$residential_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`L_Type_` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_subcategory_commercial_sale') {
if ($table == 'prop_commercialLease' || $table == 'prop_commercialSales') {
$commercial_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($commercial_array) > 1) {
$commercial_count = 0;
$where .= '(';
foreach($commercial_array as $commercial) {
if ($commercial_count == 0) {
$where .= "`".$table."`.`L_Type_` LIKE '".trim($commercial)."%'";
} else {
$where .= " OR `".$table."`.`L_Type_` LIKE '".trim($commercial)."%'";
}
$commercial_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`L_Type_` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_level') {
if ($table == 'prop_residential' || $table == 'prop_rental') {
$level_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($level_array) > 1) {
$level_count = 0;
$where .= '(';
foreach($level_array as $level) {
if ($level_count == 0) {
$where .= "`".$table."`.`LM_Char10_7` = '".trim($level)."'";
} else {
$where .= " OR `".$table."`.`LM_Char10_7` = '".trim($level)."'";
}
$level_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_7` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_builder') {
if ($table == 'prop_residential' ) {
$builder_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($builder_array) > 1) {
$builder_count = 0;
$where .= '(';
foreach($builder_array as $builder) {
if ($builder_count == 0) {
$where .= "`".$table."`.`LM_char50_11` = '".trim($builder)."'";
} else {
$where .= " OR `".$table."`.`LM_char50_11` = '".trim($builder)."'";
}
$builder_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`LM_char50_11` = '".trim($value)."'";
$where .= ')';
}
}
else{
return 0;
}
} elseif ($field == 'selected_area') {
$area_array = explode('~', $value);
if (count($area_array) > 1) {
$area_count = 0;
$where .= '(';
foreach ($area_array as $area) {
if ($area_count == 0) {
$where .= "`".$table."`.`L_Area` = '".trim($area)."'";
} else {
$where .= " OR `".$table."`.`L_Area` = '".trim($area)."'";
}
$area_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`L_Area` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'selected_mlsID') {
$mlsID_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($mlsID_array) > 1) {
$mlsID_count = 0;
$where .= '(';
foreach($mlsID_array as $mlsID) {
if ($mlsID_count == 0) {
$where .= "`".$table."`.`L_ListingID` = ".trim($mlsID)."";
} else {
$where .= " OR `".$table."`.`L_ListingID` = ".trim($mlsID)."";
}
$mlsID_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`L_ListingID` = ".trim($value)."";
$where .= ')';
}
} elseif ($field == 'selected_subcategory') {
if ($table == 'prop_residential') {
$subcategory_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($subcategory_array) > 1) {
$subcategory_count = 0;
$where .= '(';
foreach($subcategory_array as $subcategory) {
if ($subcategory_count == 0) {
$where .= "`".$table."`.`L_Type_` = '".trim($subcategory)."'";
} else {
$where .= " OR `".$table."`.`L_Type_` = '".trim($subcategory)."'";
}
$subcategory_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`L_Type_` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_school_elementary') {
if ($table == 'prop_residential') {
$school_elementary_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($school_elementary_array) > 1) {
$school_elementary_count = 0;
$where .= '(';
foreach($school_elementary_array as $school_elementary) {
if ($school_elementary_count == 0) {
$where .= "`".$table."`.`LM_Char10_5` = '".trim($school_elementary)."'";
} else {
$where .= " OR `".$table."`.`LM_Char10_5` = '".trim($school_elementary)."'";
}
$school_elementary_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_5` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_school_middle') {
if ($table == 'prop_residential') {
$school_middle_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($school_middle_array) > 1) {
$school_middle_count = 0;
$where .= '(';
foreach($school_middle_array as $school_middle) {
if ($school_middle_count == 0) {
$where .= "`".$table."`.`LM_Char10_8` = '".esc_sql(trim($school_middle))."'";
} else {
$where .= " OR `".$table."`.`LM_Char10_8` = '".esc_sql(trim($school_middle))."'";
}
$school_middle_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_8` LIKE '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_school_high') {
if ($table == 'prop_residential') {
$school_high_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($school_high_array) > 1) {
$school_high_count = 0;
$where .= '(';
foreach($school_high_array as $school_high) {
if ($school_high_count == 0) {
$where .= "`".$table."`.`LM_Char10_6` = '".trim($school_high)."'";
} else {
$where .= " OR `".$table."`.`LM_Char10_6` = '".trim($school_high)."'";
}
$school_high_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_6` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_subdivision') {
if ($table == 'prop_residential' || $table == 'prop_land' || $table == 'prop_multiFamily' || $table == 'prop_rental') {
$where .= '(';
if (trim($value) == 'None') {
$where .= "`".$table."`.`LM_char10_43` = '' OR `".$table."`.`LM_char10_43` = 'none' OR `".$table."`.`LM_char10_43` IS NULL";
} else {
$multiple_values = explode('~',trim($value));
if(count($multiple_values)){
$i = 1;
foreach($multiple_values as $multiple_value ){
$where .= "`".$table."`.`LM_char10_43` = '".trim($multiple_value)."'";
if($i !=count($multiple_values)){
$where .= " OR ";
$i++;
}
}
}else{
$where .= "`".$table."`.`LM_char10_43` = '".trim($value)."'";
}
}
$where .= ')';
}
} elseif ($field == 'selected_land_description') {
if ($table == 'prop_land') {
$land_description_array = explode('~',$value);
//selected_land_description might contain one value, or several values
if (count($land_description_array) > 1) {
$land_count = 0;
$where .= '(';
foreach($land_description_array as $land_description) {
if ($land_count == 0) {
$where .= "`".$table."`.`LFD_LandDescription_64` LIKE '%".trim($land_description)."%'";
} else {
$where .= " AND `".$table."`.`LFD_LandDescription_64` LIKE '%".trim($land_description)."%'";
}
$land_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`LFD_LandDescription_64` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'price_range') {
$selected_price_array = explode('--',$value);
//price_range will always be two values
$where .= '(';
$price_min = $selected_price_array[0];
$price_max = $selected_price_array[1];
$where .= "`".$table."`.`L_AskingPrice` >= ".$price_min." AND `".$table."`.`L_AskingPrice` <= ".$price_max;
$where .= ')';
} elseif ($field == 'selected_bedrooms') {
if ($table == 'prop_residential' || $table == 'prop_rental') {
$where .= '(';
$where .= "`".$table."`.`LM_Int1_1` >= ".(int)$value;
$where .= ')';
}
} elseif ($field == 'selected_bathrooms') {
if ($table == 'prop_residential' || $table == 'prop_rental') {
$where .= '(';
$where .= "`".$table."`.`LM_Dec_2` >= ".(int)$value;
$where .= ')';
}
} elseif ($field == 'acreage') {
$selected_acreage_array = explode('--',$value);
//acreage will always be two values
$where .= '(';
$acreage_min = $selected_acreage_array[0];
$acreage_max = $selected_acreage_array[1];
$where .= "`".$table."`.`L_NumAcres` >= ".$acreage_min." AND `".$table."`.`L_NumAcres` <= ".$acreage_max;
$where .= ')';
} elseif ($field == 'square_feet') {
if ($table != 'prop_land' && $table != 'prop_multiFamily') {
$selected_square_feet_array = explode('--',$value);
//square_feet will always be two values
$where .= '(';
$square_feet_min = $selected_square_feet_array[0];
$square_feet_max = $selected_square_feet_array[1];
$where .= "`".$table."`.`L_SquareFeet` >= ".$square_feet_min." AND `".$table."`.`L_SquareFeet` <= ".$square_feet_max;
$where .= ')';
}
} elseif ($field == 'year_built') {
if ($table != 'prop_land') {
$selected_year_built_array = explode('--',$value);
//year_built will always be two values
$where .= '(';
$year_built_min = $selected_year_built_array[0];
$year_built_max = $selected_year_built_array[1];
$where .= "`".$table."`.`LM_Int2_2` >= ".$year_built_min." AND `".$table."`.`LM_Int2_2` <= ".$year_built_max;
$where .= ')';
}
} elseif ($field == 'selected_elem') {
if ($table != 'prop_commercialLease' && $table != 'prop_commercialSales') {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_5` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'selected_middle') {
if ($table != 'prop_commercialLease' && $table != 'prop_commercialSales') {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_8` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'selected_high') {
if ($table != 'prop_commercialLease' && $table != 'prop_commercialSales') {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_6` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'open_house_selected') {
$open_house_selected = 1;
} elseif ($field == 'price_reduced_selected') {
$price_reduced_selected = 1;
} elseif ($field == 'new_listing_selected') {
$new_listing_selected = 1;
} elseif ($field == 'acres_selected') {
$selected_acres_array = explode('--',$value);
//acres will always be two values
$where .= '(';
$acres_min = $selected_acres_array[0];
$acres_max = $selected_acres_array[1];
$where .= "`".$table."`.`L_NumAcres` >= ".$acres_min." AND `".$table."`.`L_NumAcres` <= ".$acres_max;
$where .= ')';
} elseif ($field == 'agent_selected') {
$agent_array = explode('~',$value);
if (count($agent_array) > 0){
$agent_count = 0;
$where .= '(';
foreach($agent_array as $agents) {
if ($agent_count == 0){
$where .= "`".$table."`.`L_ListAgent1` = '".trim($agents)."'";
}
else {
$where .= " OR `".$table."`.`L_ListAgent1` = '".trim($agents)."'";
}
$agent_count++;
}
$where .=')';
}
else{
$where .= '(';
$where .= "`".$table."`.`L_ListAgent1` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'office_selected') {
$office_array = explode('~',$value);
//office_selected might contain one value, or several values
if (count($office_array) > 0) {
$office_count = 0;
$where .= '(';
foreach($office_array as $office) {
if ($office_count == 0) {
if ($office == 'any') {
$where .= "`".$table."`.`L_ListOffice1` != ''";
} else {
$where .= "`".$table."`.`L_ListOffice1` = ".trim($office);
}
} else {
if ($office == 'any') {
$where .= " OR `".$table."`.`L_ListOffice1` != ''";
} else {
$where .= " OR `".$table."`.`L_ListOffice1` = ".trim($office);
}
}
$office_count++;
}
$where .= ')';
} else { // user has selected 'any'
$where .= '(';
$where .= "`".$table."`.`L_ListOffice1` != ''";
$where .= ')';
}
} elseif ($field == 'short_sale_selected') {
if ($table == 'prop_residential' || $table == 'prop_land') {
$where .= '(';
$where .= "`".$table."`.`LM_char1_57` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'foreclosure_selected') {
if ($table == 'prop_residential' || $table == 'prop_land') {
$where .= '(';
$where .= "`".$table."`.`LM_char1_21` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'listing_date_selected') {
$today = date('Y-m-d H:i:s');
$selected_listing_date_array = explode('--',$value);
//listing date will always be two values
$where .= '(';
$date_min_val = $selected_listing_date_array[0];
$date_max_val = $selected_listing_date_array[1];
if ($date_min_val == 0) {
$date_min = $today;
$date_max = date('Y-m-d H:i:s', strtotime($today .' -'.$date_max_val.' day'));
} else {
$date_min = date('Y-m-d H:i:s', strtotime($today .' -'.$date_min_val.' day'));
$date_max = date('Y-m-d H:i:s', strtotime($today .' -'.$date_max_val.' day'));
}
$where .= "`".$table."`.`L_ListingDate` <= ".$date_min." AND `".$table."`.`L_ListingDate` >= ".$date_max;
$where .= ')';
} elseif ($field == 'farm_features_selected') {
if ($table == 'prop_residential') {
$farm_features_array = explode('~',$value);
//farm_features_selected might contain one value, or several values
if (count($farm_features_array) > 0) {
$farm_features_count = 0;
$where .= '(';
foreach($farm_features_array as $farm_features) {
if ($farm_features_count == 0) {
if ($farm_features == 'any') {
$where .= "`".$table."`.`LFD_FarmFeatures_246` != ''";
} else {
$where .= "`".$table."`.`LFD_FarmFeatures_246` LIKE '%".trim($farm_features)."%'";
}
} else {
if ($farm_features == 'any') {
$where .= " AND `".$table."`.`LFD_FarmFeatures_246` != ''";
} else {
$where .= " AND `".$table."`.`LFD_FarmFeatures_246` LIKE '%".trim($farm_features)."%'";
}
}
$farm_features_count++;
}
$where .= ')';
} else { // user has selected 'any'
$where .= '(';
$where .= "`".$table."`.`LFD_FarmFeatures_246` != ''";
$where .= ')';
}
}
} elseif ($field == 'search_term_selected') {
$search_term_array = explode('~',$value);
//search_term_selected might contain one value, or several values
if (count($search_term_array) > 0) {
$search_term_count = 0;
$where .= '(';
foreach ($search_term_array as $search_term) {
if ($search_term != 'new construction' && $search_term != 'hoa') {
if ($search_term_count == 0) {
$where .= "`".$table."`.`LR_remarks22` LIKE '%".trim($search_term)."%'";
} else {
$where .= " AND `".$table."`.`LR_remarks22` LIKE '%".trim($search_term)."%'";
}
} else {
if ($search_term == 'new construction') {
if ($table == 'prop_residential' || $table == 'prop_multiFamily') {
if ($search_term_count == 0) {
$where .= "`".$table."`.`LM_char1_22` = 'Yes'";
} else {
$where .= " AND `".$table."`.`LM_char1_22` = 'Yes'";
}
}
} elseif ($search_term == 'hoa') {
if ($table != 'prop_commercialLease' && $table != 'prop_commercialSales') {
if ($search_term_count == 0) {
$where .= "`".$table."`.`LM_Char1_18` = 'Yes'";
} else {
$where .= " AND `".$table."`.`LM_Char1_18` = 'Yes'";
}
}
}
}
$search_term_count++;
}
$where .= ')';
}
} elseif ($field == 'max_year_built_selected') {
$max_year_built_array = explode('~',$value);
//max_year_built_selected might contain one value, or several values
if (count($max_year_built_array) > 0) {
$max_year_built_count = 0;
$where .= '(';
foreach ($max_year_built_array as $max_year_built) {
$year_built_min = 0;
$where .= "`".$table."`.`LM_Int2_2` >= ".$year_built_min." AND `".$table."`.`LM_Int2_2` <= ".$max_year_built;
}
$where .= ')';
}
}
//main_search2 is not an exploded array
} else {
$search_count = 0;
foreach ($search_fields as $field) {
if ($search_count == 0) {
$where .= "(`".$table."`.`".$field."` LIKE '%".trim($main_search2)."%'";
} else {
$where .= " OR `".$table."`.`".$field."` LIKE '%".trim($main_search2)."%'";
}
$search_count++;
}
$where .= ')';
}
////if we are on the 2nd or more iteration through main_search_arr
} elseif ($main_search_count > 0) {
//break apart extended searches in $main_search2
$kv_pair_array = explode('/',$main_search2);
//if main_search2 is an extended search: "searchDescription/search"
if (count($kv_pair_array) > 1) {
$field = $kv_pair_array[0];
$value = $kv_pair_array[1];
if ($where != '') {
if ($field == 'open_house_selected' || $field == 'price_reduced_selected' || $field == 'new_listing_selected' || ($field == 'farm_features_selected' && $table != 'prop_residential')) {
$where .= '';
} else {
$where .= ' AND ';
}
}
if ($field == 'selected_subcategory_residential') {
if ($table == 'prop_residential') {
$residential_array = explode('~',$value);
//selected_subcategory_residential will only contain one value
if (count($residential_array) > 1) {
$residential_count = 0;
$where .= '(';
foreach($residential_array as $residential) {
if ($residential_count == 0) {
$where .= "`".$table."`.`L_Type_` LIKE '".trim($residential)."%'";
} else {
$where .= " OR `".$table."`.`L_Type_` LIKE '".trim($residential)."%'";
}
$residential_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`L_Type_` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_subcategory_commercial_sale') {
if ($table == 'prop_commercialLease' || $table == 'prop_commercialSales') {
$commercial_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($commercial_array) > 1) {
$commercial_count = 0;
$where .= '(';
foreach($commercial_array as $commercial) {
if ($commercial_count == 0) {
$where .= "`".$table."`.`L_Type_` LIKE '".trim($commercial)."%'";
} else {
$where .= " OR `".$table."`.`L_Type_` LIKE '".trim($commercial)."%'";
}
$commercial_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`L_Type_` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_level') {
if ($table == 'prop_residential' || $table == 'prop_rental') {
$level_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($level_array) > 1) {
$level_count = 0;
$where .= '(';
foreach($level_array as $level) {
if ($level_count == 0) {
$where .= "`".$table."`.`LM_Char10_7` = '".trim($level)."'";
} else {
$where .= " OR `".$table."`.`LM_Char10_7` = '".trim($level)."'";
}
$level_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_7` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_builder') {
if ($table == 'prop_residential' ) {
$builder_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($builder_array) > 1) {
$builder_count = 0;
$where .= '(';
foreach($builder_array as $builder) {
if ($builder_count == 0) {
$where .= "`".$table."`.`LM_char50_11` = '".trim($builder)."'";
} else {
$where .= " OR `".$table."`.`LM_char50_11` = '".trim($builder)."'";
}
$builder_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`LM_char50_11` = '".trim($value)."'";
$where .= ')';
}
}
else{
return 0;
}
} elseif ($field == 'selected_area') {
$area_array = explode('~', $value);
if (count($area_array) > 1) {
$area_count = 0;
$where .= '(';
foreach ($area_array as $area) {
if ($area_count == 0) {
$where .= "`".$table."`.`L_Area` = '".trim($area)."'";
} else {
$where .= " OR `".$table."`.`L_Area` = '".trim($area)."'";
}
$area_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`L_Area` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'selected_mlsID') {
$mlsID_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($mlsID_array) > 1) {
$mlsID_count = 0;
$where .= '(';
foreach($mlsID_array as $mlsID) {
if ($mlsID_count == 0) {
$where .= "`".$table."`.`L_ListingID` = ".trim($mlsID)."";
} else {
$where .= " OR `".$table."`.`L_ListingID` = ".trim($mlsID)."";
}
$mlsID_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`L_ListingID` = ".trim($value)."";
$where .= ')';
}
} elseif ($field == 'selected_subcategory') {
if ($table == 'prop_residential') {
$subcategory_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($subcategory_array) > 1) {
$subcategory_count = 0;
$where .= '(';
foreach($subcategory_array as $subcategory) {
if ($subcategory_count == 0) {
$where .= "`".$table."`.`L_Type_` = '".trim($subcategory)."'";
} else {
$where .= " OR `".$table."`.`L_Type_` = '".trim($subcategory)."'";
}
$subcategory_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`L_Type_` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_school_elementary') {
if ($table == 'prop_residential') {
$school_elementary_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($school_elementary_array) > 1) {
$school_elementary_count = 0;
$where .= '(';
foreach($school_elementary_array as $school_elementary) {
if ($school_elementary_count == 0) {
$where .= "`".$table."`.`LM_Char10_5` = '".trim($school_elementary)."'";
} else {
$where .= " OR `".$table."`.`LM_Char10_5` = '".trim($school_elementary)."'";
}
$school_elementary_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_5` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_school_middle') {
if ($table == 'prop_residential') {
$school_middle_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($school_middle_array) > 1) {
$school_middle_count = 0;
$where .= '(';
foreach($school_middle_array as $school_middle) {
if ($school_middle_count == 0) {
$where .= "`".$table."`.`LM_Char10_8` = '".esc_sql(trim($school_middle))."'";
} else {
$where .= " OR `".$table."`.`LM_Char10_8` = '".esc_sql(trim($school_middle))."'";
}
$school_middle_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_8` LIKE '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_school_high') {
if ($table == 'prop_residential') {
$school_high_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($school_high_array) > 1) {
$school_high_count = 0;
$where .= '(';
foreach($school_high_array as $school_high) {
if ($school_high_count == 0) {
$where .= "`".$table."`.`LM_Char10_6` = '".trim($school_high)."'";
} else {
$where .= " OR `".$table."`.`LM_Char10_6` = '".trim($school_high)."'";
}
$school_high_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_6` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_subdivision') {
if ($table == 'prop_residential' || $table == 'prop_land' || $table == 'prop_multiFamily' || $table == 'prop_rental') {
$where .= '(';
if (trim($value) == 'None') {
$where .= "`".$table."`.`LM_char10_43` = '' OR `".$table."`.`LM_char10_43` = 'none' OR `".$table."`.`LM_char10_43` IS NULL";
} else {
$multiple_values = explode('~',trim($value));
if(count($multiple_values)){
$i = 1;
foreach($multiple_values as $multiple_value ){
$where .= "`".$table."`.`LM_char10_43` = '".trim($multiple_value)."'";
if($i !=count($multiple_values)){
$where .= " OR ";
$i++;
}
}
}else{
$where .= "`".$table."`.`LM_char10_43` = '".trim($value)."'";
}
}
$where .= ')';
}
} elseif ($field == 'selected_land_description') {
if ($table == 'prop_land') {
$land_description_array = explode('~',$value);
//selected_land_description might contain one value, or several values
if (count($land_description_array) > 1) {
$land_count = 0;
$where .= '(';
foreach($land_description_array as $land_description) {
if ($land_count == 0) {
$where .= "`".$table."`.`LFD_LandDescription_64` LIKE '%".trim($land_description)."%'";
} else {
$where .= " AND `".$table."`.`LFD_LandDescription_64` LIKE '%".trim($land_description)."%'";
}
$land_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`LFD_LandDescription_64` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'price_range') {
$selected_price_array = explode('--',$value);
//price_range will always be two values
$where .= '(';
$price_min = $selected_price_array[0];
$price_max = $selected_price_array[1];
$where .= "`".$table."`.`L_AskingPrice` >= ".$price_min." AND `".$table."`.`L_AskingPrice` <= ".$price_max;
$where .= ')';
} elseif ($field == 'selected_bedrooms') {
if ($table == 'prop_residential' || $table == 'prop_rental') {
$where .= '(';
$where .= "`".$table."`.`LM_Int1_1` >= ".(int)$value;
$where .= ')';
}
} elseif ($field == 'selected_bathrooms') {
if ($table == 'prop_residential' || $table == 'prop_rental') {
$where .= '(';
$where .= "`".$table."`.`LM_Dec_2` >= ".(int)$value;
$where .= ')';
}
} elseif ($field == 'acreage') {
$selected_acreage_array = explode('--',$value);
//acreage will always be two values
$where .= '(';
$acreage_min = $selected_acreage_array[0];
$acreage_max = $selected_acreage_array[1];
$where .= "`".$table."`.`L_NumAcres` >= ".$acreage_min." AND `".$table."`.`L_NumAcres` <= ".$acreage_max;
$where .= ')';
} elseif ($field == 'square_feet') {
if ($table != 'prop_land' && $table != 'prop_multiFamily') {
$selected_square_feet_array = explode('--',$value);
//square_feet will always be two values
$where .= '(';
$square_feet_min = $selected_square_feet_array[0];
$square_feet_max = $selected_square_feet_array[1];
$where .= "`".$table."`.`L_SquareFeet` >= ".$square_feet_min." AND `".$table."`.`L_SquareFeet` <= ".$square_feet_max;
$where .= ')';
}
} elseif ($field == 'year_built') {
if ($table != 'prop_land') {
$selected_year_built_array = explode('--',$value);
//year_built will always be two values
$where .= '(';
$year_built_min = $selected_year_built_array[0];
$year_built_max = $selected_year_built_array[1];
$where .= "`".$table."`.`LM_Int2_2` >= ".$year_built_min." AND `".$table."`.`LM_Int2_2` <= ".$year_built_max;
$where .= ')';
}
} elseif ($field == 'selected_elem') {
if ($table != 'prop_commercialLease' && $table != 'prop_commercialSales') {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_5` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'selected_middle') {
if ($table != 'prop_commercialLease' && $table != 'prop_commercialSales') {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_8` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'selected_high') {
if ($table != 'prop_commercialLease' && $table != 'prop_commercialSales') {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_6` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'open_house_selected') {
$open_house_selected = 1;
} elseif ($field == 'price_reduced_selected') {
$price_reduced_selected = 1;
} elseif ($field == 'new_listing_selected') {
$new_listing_selected = 1;
} elseif ($field == 'acres_selected') {
$selected_acres_array = explode('--',$value);
//acres will always be two values
$where .= '(';
$acres_min = $selected_acres_array[0];
$acres_max = $selected_acres_array[1];
$where .= "`".$table."`.`L_NumAcres` >= ".$acres_min." AND `".$table."`.`L_NumAcres` <= ".$acres_max;
$where .= ')';
} elseif ($field == 'agent_selected') {
$agent_array = explode('~',$value);
if (count($agent_array) > 0){
$agent_count = 0;
$where .= '(';
foreach($agent_array as $agents) {
if ($agent_count == 0){
$where .= "`".$table."`.`L_ListAgent1` = '".trim($agents)."'";
}
else {
$where .= " OR `".$table."`.`L_ListAgent1` = '".trim($agents)."'";
}
$agent_count++;
}
$where .=')';
}
else{
$where .= '(';
$where .= "`".$table."`.`L_ListAgent1` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'office_selected') {
$office_array = explode('~',$value);
//office_selected might contain one value, or several values
if (count($office_array) > 0) {
$office_count = 0;
$where .= '(';
foreach($office_array as $office) {
if ($office_count == 0) {
if ($office == 'any') {
$where .= "`".$table."`.`L_ListOffice1` != ''";
} else {
$where .= "`".$table."`.`L_ListOffice1` = ".trim($office);
}
} else {
if ($office == 'any') {
$where .= " OR `".$table."`.`L_ListOffice1` != ''";
} else {
$where .= " OR `".$table."`.`L_ListOffice1` = ".trim($office);
}
}
$office_count++;
}
$where .= ')';
} else { // user has selected 'any'
$where .= '(';
$where .= "`".$table."`.`L_ListOffice1` != ''";
$where .= ')';
}
} elseif ($field == 'short_sale_selected') {
if ($table == 'prop_residential' || $table == 'prop_land') {
$where .= '(';
$where .= "`".$table."`.`LM_char1_57` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'foreclosure_selected') {
if ($table == 'prop_residential' || $table == 'prop_land') {
$where .= '(';
$where .= "`".$table."`.`LM_char1_21` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'listing_date_selected') {
$today = date('Y-m-d H:i:s');
$selected_listing_date_array = explode('--',$value);
//listing date will always be two values
$where .= '(';
$date_min_val = $selected_listing_date_array[0];
$date_max_val = $selected_listing_date_array[1];
if ($date_min_val == 0) {
$date_min = $today;
$date_max = date('Y-m-d H:i:s', strtotime($today .' -'.$date_max_val.' day'));
} else {
$date_min = date('Y-m-d H:i:s', strtotime($today .' -'.$date_min_val.' day'));
$date_max = date('Y-m-d H:i:s', strtotime($today .' -'.$date_max_val.' day'));
}
$where .= "`".$table."`.`L_ListingDate` <= ".$date_min." AND `".$table."`.`L_ListingDate` >= ".$date_max;
$where .= ')';
} elseif ($field == 'farm_features_selected') {
if ($table == 'prop_residential') {
$farm_features_array = explode('~',$value);
//farm_features_selected might contain one value, or several values
if (count($farm_features_array) > 0) {
$farm_features_count = 0;
$where .= '(';
foreach($farm_features_array as $farm_features) {
if ($farm_features_count == 0) {
if ($farm_features == 'any') {
$where .= "`".$table."`.`LFD_FarmFeatures_246` != ''";
} else {
$where .= "`".$table."`.`LFD_FarmFeatures_246` LIKE '%".trim($farm_features)."%'";
}
} else {
if ($farm_features == 'any') {
$where .= " AND `".$table."`.`LFD_FarmFeatures_246` != ''";
} else {
$where .= " AND `".$table."`.`LFD_FarmFeatures_246` LIKE '%".trim($farm_features)."%'";
}
}
$farm_features_count++;
}
$where .= ')';
} else { // user has selected 'any'
$where .= '(';
$where .= "`".$table."`.`LFD_FarmFeatures_246` != ''";
$where .= ')';
}
}
} elseif ($field == 'search_term_selected') {
$search_term_array = explode('~',$value);
//search_term_selected might contain one value, or several values
if (count($search_term_array) > 0) {
$search_term_count = 0;
$where .= '(';
foreach ($search_term_array as $search_term) {
if ($search_term != 'new construction' && $search_term != 'hoa') {
if ($search_term_count == 0) {
$where .= "`".$table."`.`LR_remarks22` LIKE '%".trim($search_term)."%'";
} else {
$where .= " AND `".$table."`.`LR_remarks22` LIKE '%".trim($search_term)."%'";
}
} else {
if ($search_term == 'new construction') {
if ($table == 'prop_residential' || $table == 'prop_multiFamily') {
if ($search_term_count == 0) {
$where .= "`".$table."`.`LM_char1_22` = 'Yes'";
} else {
$where .= " AND `".$table."`.`LM_char1_22` = 'Yes'";
}
}
} elseif ($search_term == 'hoa') {
if ($table != 'prop_commercialLease' && $table != 'prop_commercialSales') {
if ($search_term_count == 0) {
$where .= "`".$table."`.`LM_Char1_18` = 'Yes'";
} else {
$where .= " AND `".$table."`.`LM_Char1_18` = 'Yes'";
}
}
}
}
$search_term_count++;
}
$where .= ')';
}
} elseif ($field == 'max_year_built_selected') {
$max_year_built_array = explode('~',$value);
//max_year_built_selected might contain one value, or several values
if (count($max_year_built_array) > 0) {
$max_year_built_count = 0;
$where .= '(';
foreach ($max_year_built_array as $max_year_built) {
$year_built_min = 0;
$where .= "`".$table."`.`LM_Int2_2` >= ".$year_built_min." AND `".$table."`.`LM_Int2_2` <= ".$max_year_built;
$where .= ')';
}
}
}
//main_search2 is not an exploded array
} else {
$search_count = 0;
//Added To handle SQL Error
if(is_array($search_fields) && count($search_fields) >0){
$where .= ' AND ';
foreach ($search_fields as $field) {
if ($search_count == 0) {
$where .= "(`".$table."`.`".$field."` LIKE '%".trim($main_search2)."%'";
} else {
$where .= " OR `".$table."`.`".$field."` LIKE '%".trim($main_search2)."%'";
}
$search_count++;
}
$where .= ')';
}
}
} //end main_search_count
$main_search_count++;
} //end foreach main_search_arr
//if main_search_arr only has one search term
} elseif (count($main_search_arr) == 1) {
$main_search2 = $main_search_arr[0];
//break apart extended searches in $main_search2
$kv_pair_array = explode('/',$main_search2);
//if main_search2 is an extended search: "searchDescription/search"
if (count($kv_pair_array) > 1) {
$field = $kv_pair_array[0];
$value = $kv_pair_array[1];
if ($field == 'selected_subcategory_residential') {
if ($table == 'prop_residential') {
$residential_array = explode('~',$value);
//selected_subcategory_residential will only contain one value
if (count($residential_array) > 1) {
$residential_count = 0;
$where .= '(';
foreach($residential_array as $residential) {
if ($residential_count == 0) {
$where .= "`".$table."`.`L_Type_` LIKE '".trim($residential)."%'";
} else {
$where .= " OR `".$table."`.`L_Type_` LIKE '".trim($residential)."%'";
}
$residential_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`L_Type_` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_subcategory_commercial_sale') {
if ($table == 'prop_commercialLease' || $table == 'prop_commercialSales') {
$commercial_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($commercial_array) > 1) {
$commercial_count = 0;
$where .= '(';
foreach($commercial_array as $commercial) {
if ($commercial_count == 0) {
$where .= "`".$table."`.`L_Type_` LIKE '".trim($commercial)."%'";
} else {
$where .= " OR `".$table."`.`L_Type_` LIKE '".trim($commercial)."%'";
}
$commercial_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`L_Type_` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_level') {
if ($table == 'prop_residential' || $table == 'prop_rental') {
$level_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($level_array) > 1) {
$level_count = 0;
$where .= '(';
foreach($level_array as $level) {
if ($level_count == 0) {
$where .= "`".$table."`.`LM_Char10_7` = '".trim($level)."'";
} else {
$where .= " OR `".$table."`.`LM_Char10_7` = '".trim($level)."'";
}
$level_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_7` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_builder') {
if ($table == 'prop_residential' ) {
$builder_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($builder_array) > 1) {
$builder_count = 0;
$where .= '(';
foreach($builder_array as $builder) {
if ($builder_count == 0) {
$where .= "`".$table."`.`LM_char50_11` = '".trim($builder)."'";
} else {
$where .= " OR `".$table."`.`LM_char50_11` = '".trim($builder)."'";
}
$builder_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`LM_char50_11` = '".trim($value)."'";
$where .= ')';
}
}
else{
return 0;
}
} elseif ($field == 'selected_area') {
$area_array = explode('~', $value);
if (count($area_array) > 1) {
$area_count = 0;
$where .= '(';
foreach ($area_array as $area) {
if ($area_count == 0) {
$where .= "`".$table."`.`L_Area` = '".trim($area)."'";
} else {
$where .= " OR `".$table."`.`L_Area` = '".trim($area)."'";
}
$area_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`L_Area` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'selected_mlsID') {
$mlsID_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($mlsID_array) > 1) {
$mlsID_count = 0;
$where .= '(';
foreach($mlsID_array as $mlsID) {
if ($mlsID_count == 0) {
$where .= "`".$table."`.`L_ListingID` = ".trim($mlsID)."";
} else {
$where .= " OR `".$table."`.`L_ListingID` = ".trim($mlsID)."";
}
$mlsID_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`L_ListingID` = ".trim($value)."";
$where .= ')';
}
} elseif ($field == 'selected_subcategory') {
if ($table == 'prop_residential') {
$subcategory_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($subcategory_array) > 1) {
$subcategory_count = 0;
$where .= '(';
foreach($subcategory_array as $subcategory) {
if ($subcategory_count == 0) {
$where .= "`".$table."`.`L_Type_` = '".trim($subcategory)."'";
} else {
$where .= " OR `".$table."`.`L_Type_` = '".trim($subcategory)."'";
}
$subcategory_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`L_Type_` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_school_elementary') {
if ($table == 'prop_residential') {
$school_elementary_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($school_elementary_array) > 1) {
$school_elementary_count = 0;
$where .= '(';
foreach($school_elementary_array as $school_elementary) {
if ($school_elementary_count == 0) {
$where .= "`".$table."`.`LM_Char10_5` = '".trim($school_elementary)."'";
} else {
$where .= " OR `".$table."`.`LM_Char10_5` = '".trim($school_elementary)."'";
}
$school_elementary_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_5` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_school_middle') {
if ($table == 'prop_residential') {
$school_middle_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($school_middle_array) > 1) {
$school_middle_count = 0;
$where .= '(';
foreach($school_middle_array as $school_middle) {
if ($school_middle_count == 0) {
$where .= "`".$table."`.`LM_Char10_8` = '".esc_sql(trim($school_middle))."'";
} else {
$where .= " OR `".$table."`.`LM_Char10_8` = '".esc_sql(trim($school_middle))."'";
}
$school_middle_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_8` LIKE '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_school_high') {
if ($table == 'prop_residential') {
$school_high_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($school_high_array) > 1) {
$school_high_count = 0;
$where .= '(';
foreach($school_high_array as $school_high) {
if ($school_high_count == 0) {
$where .= "`".$table."`.`LM_Char10_6` = '".trim($school_high)."'";
} else {
$where .= " OR `".$table."`.`LM_Char10_6` = '".trim($school_high)."'";
}
$school_high_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_6` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_subdivision') {
if ($table == 'prop_residential' || $table == 'prop_land' || $table == 'prop_multiFamily' || $table == 'prop_rental') {
$where .= '(';
if (trim($value) == 'None') {
$where .= "`".$table."`.`LM_char10_43` = '' OR `".$table."`.`LM_char10_43` = 'none' OR `".$table."`.`LM_char10_43` IS NULL";
} else {
$multiple_values = explode('~',trim($value));
if(count($multiple_values)){
$i = 1;
foreach($multiple_values as $multiple_value ){
$where .= "`".$table."`.`LM_char10_43` = '".trim($multiple_value)."'";
if($i !=count($multiple_values)){
$where .= " OR ";
$i++;
}
}
}else{
$where .= "`".$table."`.`LM_char10_43` = '".trim($value)."'";
}
}
$where .= ')';
}
} elseif ($field == 'selected_land_description') {
if ($table == 'prop_land') {
$land_description_array = explode('~',$value);
//selected_land_description might contain one value, or several values
if (count($land_description_array) > 1) {
$land_count = 0;
$where .= '(';
foreach($land_description_array as $land_description) {
if ($land_count == 0) {
$where .= "`".$table."`.`LFD_LandDescription_64` LIKE '%".trim($land_description)."%'";
} else {
$where .= " AND `".$table."`.`LFD_LandDescription_64` LIKE '%".trim($land_description)."%'";
}
$land_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`LFD_LandDescription_64` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'price_range') {
$selected_price_array = explode('--',$value);
//price_range will always be two values
$where .= '(';
$price_min = $selected_price_array[0];
$price_max = $selected_price_array[1];
$where .= "`".$table."`.`L_AskingPrice` >= ".$price_min." AND `".$table."`.`L_AskingPrice` <= ".$price_max;
$where .= ')';
} elseif ($field == 'selected_bedrooms') {
if ($table == 'prop_residential' || $table == 'prop_rental') {
$where .= '(';
$where .= "`".$table."`.`LM_Int1_1` >= ".(int)$value;
$where .= ')';
}
} elseif ($field == 'selected_bathrooms') {
if ($table == 'prop_residential' || $table == 'prop_rental') {
$where .= '(';
$where .= "`".$table."`.`LM_Dec_2` >= ".(int)$value;
$where .= ')';
}
} elseif ($field == 'acreage') {
$selected_acreage_array = explode('--',$value);
//acreage will always be two values
$where .= '(';
$acreage_min = $selected_acreage_array[0];
$acreage_max = $selected_acreage_array[1];
$where .= "`".$table."`.`L_NumAcres` >= ".$acreage_min." AND `".$table."`.`L_NumAcres` <= ".$acreage_max;
$where .= ')';
} elseif ($field == 'square_feet') {
if ($table != 'prop_land' && $table != 'prop_multiFamily') {
$selected_square_feet_array = explode('--',$value);
//square_feet will always be two values
$where .= '(';
$square_feet_min = $selected_square_feet_array[0];
$square_feet_max = $selected_square_feet_array[1];
$where .= "`".$table."`.`L_SquareFeet` >= ".$square_feet_min." AND `".$table."`.`L_SquareFeet` <= ".$square_feet_max;
$where .= ')';
}
} elseif ($field == 'year_built') {
if ($table != 'prop_land') {
$selected_year_built_array = explode('--',$value);
//year_built will always be two values
$where .= '(';
$year_built_min = $selected_year_built_array[0];
$year_built_max = $selected_year_built_array[1];
$where .= "`".$table."`.`LM_Int2_2` >= ".$year_built_min." AND `".$table."`.`LM_Int2_2` <= ".$year_built_max;
$where .= ')';
}
} elseif ($field == 'selected_elem') {
if ($table != 'prop_commercialLease' && $table != 'prop_commercialSales') {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_5` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'selected_middle') {
if ($table != 'prop_commercialLease' && $table != 'prop_commercialSales') {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_8` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'selected_high') {
if ($table != 'prop_commercialLease' && $table != 'prop_commercialSales') {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_6` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'open_house_selected') {
$open_house_selected = 1;
} elseif ($field == 'price_reduced_selected') {
$price_reduced_selected = 1;
} elseif ($field == 'new_listing_selected') {
$new_listing_selected = 1;
} elseif ($field == 'acres_selected') {
$selected_acres_array = explode('--',$value);
//acres will always be two values
$where .= '(';
$acres_min = $selected_acres_array[0];
$acres_max = $selected_acres_array[1];
$where .= "`".$table."`.`L_NumAcres` >= ".$acres_min." AND `".$table."`.`L_NumAcres` <= ".$acres_max;
$where .= ')';
} elseif ($field == 'agent_selected') {
$agent_array = explode('~',$value);
if (count($agent_array) > 0){
$agent_count = 0;
$where .= '(';
foreach($agent_array as $agents) {
if ($agent_count == 0){
$where .= "`".$table."`.`L_ListAgent1` = '".trim($agents)."'";
}
else {
$where .= " OR `".$table."`.`L_ListAgent1` = '".trim($agents)."'";
}
$agent_count++;
}
$where .=')';
}
else{
$where .= '(';
$where .= "`".$table."`.`L_ListAgent1` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'office_selected') {
$office_array = explode('~',$value);
//office_selected might contain one value, or several values
if (count($office_array) > 0) {
$office_count = 0;
$where .= '(';
foreach($office_array as $office) {
if ($office_count == 0) {
if ($office == 'any') {
$where .= "`".$table."`.`L_ListOffice1` != ''";
} else {
$where .= "`".$table."`.`L_ListOffice1` = ".trim($office);
}
} else {
if ($office == 'any') {
$where .= " OR `".$table."`.`L_ListOffice1` != ''";
} else {
$where .= " OR `".$table."`.`L_ListOffice1` = ".trim($office);
}
}
$office_count++;
}
$where .= ')';
} else { // user has selected 'any'
$where .= '(';
$where .= "`".$table."`.`L_ListOffice1` != ''";
$where .= ')';
}
} elseif ($field == 'short_sale_selected') {
if ($table == 'prop_residential' || $table == 'prop_land') {
$where .= '(';
$where .= "`".$table."`.`LM_char1_57` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'foreclosure_selected') {
if ($table == 'prop_residential' || $table == 'prop_land') {
$where .= '(';
$where .= "`".$table."`.`LM_char1_21` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'listing_date_selected') {
$today = date('Y-m-d H:i:s');
$selected_listing_date_array = explode('--',$value);
//listing date will always be two values
$where .= '(';
$date_min_val = $selected_listing_date_array[0];
$date_max_val = $selected_listing_date_array[1];
if ($date_min_val == 0) {
$date_min = $today;
$date_max = date('Y-m-d H:i:s', strtotime($today .' -'.$date_max_val.' day'));
} else {
$date_min = date('Y-m-d H:i:s', strtotime($today .' -'.$date_min_val.' day'));
$date_max = date('Y-m-d H:i:s', strtotime($today .' -'.$date_max_val.' day'));
}
$where .= "`".$table."`.`L_ListingDate` <= ".$date_min." AND `".$table."`.`L_ListingDate` >= ".$date_max;
$where .= ')';
} elseif ($field == 'farm_features_selected') {
if ($table == 'prop_residential') {
$farm_features_array = explode('~',$value);
//farm_features_selected might contain one value, or several values
if (count($farm_features_array) > 0) {
$farm_features_count = 0;
$where .= '(';
foreach($farm_features_array as $farm_features) {
if ($farm_features_count == 0) {
if ($farm_features == 'any') {
$where .= "`".$table."`.`LFD_FarmFeatures_246` != ''";
} else {
$where .= "`".$table."`.`LFD_FarmFeatures_246` LIKE '%".trim($farm_features)."%'";
}
} else {
if ($farm_features == 'any') {
$where .= " AND `".$table."`.`LFD_FarmFeatures_246` != ''";
} else {
$where .= " AND `".$table."`.`LFD_FarmFeatures_246` LIKE '%".trim($farm_features)."%'";
}
}
$farm_features_count++;
}
$where .= ')';
} else { // user has selected 'any'
$where .= '(';
$where .= "`".$table."`.`LFD_FarmFeatures_246` != ''";
$where .= ')';
}
}
} elseif ($field == 'search_term_selected') {
$search_term_array = explode('~',$value);
//search_term_selected might contain one value, or several values
if (count($search_term_array) > 0) {
$search_term_count = 0;
$where .= '(';
foreach ($search_term_array as $search_term) {
if ($search_term != 'new construction' && $search_term != 'hoa') {
if ($search_term_count == 0) {
$where .= "`".$table."`.`LR_remarks22` LIKE '%".trim($search_term)."%'";
} else {
$where .= " AND `".$table."`.`LR_remarks22` LIKE '%".trim($search_term)."%'";
}
} else {
if ($search_term == 'new construction') {
if ($table == 'prop_residential' || $table == 'prop_multiFamily') {
if ($search_term_count == 0) {
$where .= "`".$table."`.`LM_char1_22` = 'Yes'";
} else {
$where .= " AND `".$table."`.`LM_char1_22` = 'Yes'";
}
}
} elseif ($search_term == 'hoa') {
if ($table != 'prop_commercialLease' && $table != 'prop_commercialSales') {
if ($search_term_count == 0) {
$where .= "`".$table."`.`LM_Char1_18` = 'Yes'";
} else {
$where .= " AND `".$table."`.`LM_Char1_18` = 'Yes'";
}
}
}
}
$search_term_count++;
}
$where .= ')';
}
} elseif ($field == 'max_year_built_selected') {
$max_year_built_array = explode('~',$value);
//max_year_built_selected might contain one value, or several values
if (count($max_year_built_array) > 0) {
$max_year_built_count = 0;
$where .= '(';
foreach ($max_year_built_array as $max_year_built) {
$year_built_min = 0;
$where .= "`".$table."`.`LM_Int2_2` >= ".$year_built_min." AND `".$table."`.`LM_Int2_2` <= ".$max_year_built;
$where .= ')';
}
}
}
//main_search2 is not an exploded array
} else {
$search_count = 0;
foreach ($search_fields as $field) {
if ($search_count == 0) {
$where .= "(`".$table."`.`".$field."` LIKE '%".trim($main_search2)."%'";
} else {
$where .= " OR `".$table."`.`".$field."` LIKE '%".trim($main_search2)."%'";
}
$search_count++;
}
$where .= ')';
}
} //end main_search_arr only has one search term
} //end main_search != ''
//set variable
$get_open_houses = 0;
//deal with open house and new listings from main search
if ($open_house_selected == 1 || $new_listing_selected == 1) {
if ($open_house_selected == 1) {
if ($where == '') {
$get_open_houses = 1;
$today = date('Y-m-d H:i:s', time());
$fourteen_days_from_now = date('Y-m-d H:i:s', strtotime('+14 days'));
$where .= "(`openHouse`.`OH_EndDateTime` <= '".$fourteen_days_from_now."' AND `openHouse`.`OH_EndDateTime` >= '".$today."')";
$select .= ', openHouse.OH_StartDateTime, openHouse.OH_EndDateTime';
} else {
$get_open_houses = 1;
$today = date('Y-m-d H:i:s', time());
$fourteen_days_from_now = date('Y-m-d H:i:s', strtotime('+14 days'));
$where .= " AND (`openHouse`.`OH_EndDateTime` <= '".$fourteen_days_from_now."' AND `openHouse`.`OH_EndDateTime` >= '".$today."')";
$select .= ', openHouse.OH_StartDateTime, openHouse.OH_EndDateTime';
}
}
if ($new_listing_selected == 1) {
if ($where == '') {
$today = date('Y-m-d H:i:s', time());
$seven_days_ago = date('Y-m-d H:i:s', strtotime('-7 days'));
$where .= "(`".$table."`.`L_InputDate` >= '".$seven_days_ago."' AND `".$table."`.`L_InputDate` <= '".$today."')";
} else {
$today = date('Y-m-d H:i:s', time());
$seven_days_ago = date('Y-m-d H:i:s', strtotime('-7 days'));
$where .= " AND (`".$table."`.`L_InputDate` >= '".$seven_days_ago."' AND `".$table."`.`L_InputDate` <= '".$today."')";
}
}
}
//deal with price reduced from main search
if ($price_reduced_selected == 1) {
if ($where == '') {
$where .= "(`".$table."`.`L_AskingPrice` < `".$table."`.`L_OriginalPrice`)";
} else {
$where .= " AND (`".$table."`.`L_AskingPrice` < `".$table."`.`L_OriginalPrice`)";
}
}
//start building query
$listings = array();
$listing_count = 0;
//$this->db->select($select);
//$this->db->from($table);
//JOINS
$model_join ='';
$model_where='';
$model_group_by = '';
$model_order_by='';
$model_limit='';
if ($get_open_houses == 1) {
//$this->db->join('openHouse', 'openHouse.L_ListingID = '.$table.'.L_ListingID', 'inner');
$model_join .= " inner join openHouse on ".'openHouse.L_ListingID = '.$table.'.L_ListingID';
}
//$this->db->join('activeAgents',$table.'.L_ListAgent1 = activeAgents.U_AgentID', 'left');
$model_join .= " left join activeAgents on ".$table.'.L_ListAgent1 = activeAgents.U_AgentID';
//$this->db->join('activeOffice',$table.'.L_ListOffice1 = activeOffice.O_OfficeID', 'left');
$model_join .= " left join activeOffice on ".$table.'.L_ListOffice1 = activeOffice.O_OfficeID';
//WHERE
if ($ne != '' && $nw != '' && $se != '' && $sw != '') {
$where .= " AND ((`".$table."`.`LMD_MP_Latitude` > ".$nw." AND `".$table."`.`LMD_MP_Latitude` < ".$ne." AND `".$table."`.`LMD_MP_Latitude` > ".$sw." AND `".$table."`.`LMD_MP_Latitude` < ".$se.") AND (`".$table."`.`LMD_MP_Longitude` > ".$sw." AND `".$table."`.`LMD_MP_Longitude` < ".$nw." AND `".$table."`.`LMD_MP_Longitude` > ".$se." AND `".$table."`.`LMD_MP_Longitude` < ".$ne."))";
}
if ($where != '') {
$last_char = substr($where, -1);
if ($last_char != ')') {
$where .= ")";
}
//echo $where;
//$this->db->where($where);
$model_where = "where ".$where;
}
//GROUP BY
if ($get_open_houses == 1) {
//$this->db->group_by($table.'.L_ListingID');
$model_group_by = " GROUP BY ".$table.'.L_ListingID';
}
//ORDER BY
//$this->db->order_by($table.'.L_AskingPrice '.$price_sort.', '.$table.'.L_InputDate '.$date_sort);
//RUN QUERY
//$query = $this->db->get();
$results = $this->databaseConnection->get_results("select $select from $table $model_join $model_where $model_group_by",ARRAY_A);
if (count($results))
{
$listings = $results;
$listing_count = count($listings);
}
/*echo $this->db->last_query().'
';
die();*/
//return the result
return $listing_count;
} $where .= "`".$table."`.`LM_Char1_18` = 'Yes'";
} else {
$where .= " AND `".$table."`.`LM_Char1_18` = 'Yes'";
}
}
}
}
$search_term_count++;
}
$where .= ')';
}
} elseif ($field == 'max_year_built_selected') {
$max_year_built_array = explode('~',$value);
//max_year_built_selected might contain one value, or several values
if (count($max_year_built_array) > 0) {
$max_year_built_count = 0;
$where .= '(';
foreach ($max_year_built_array as $max_year_built) {
$year_built_min = 0;
$where .= "`".$table."`.`LM_Int2_2` >= ".$year_built_min." AND `".$table."`.`LM_Int2_2` <= ".$max_year_built;
$where .= ')';
}
}
}
//main_search2 is not an exploded array
} else {
$search_count = 0;
//Added To handle SQL Error
if(is_array($search_fields) && count($search_fields) > 0){
$where .= ' AND ';
foreach ($search_fields as $field) {
if ($search_count == 0) {
$where .= "(`".$table."`.`".$field."` LIKE '%".trim($main_search2)."%'";
} else {
$where .= " OR `".$table."`.`".$field."` LIKE '%".trim($main_search2)."%'";
}
$search_count++;
}
$where .= ')';
}
}
} //end main_search_count
$main_search_count++;
} //end foreach main_search_arr
//if main_search_arr only has one search term
} elseif (count($main_search_arr) == 1) {
$main_search2 = $main_search_arr[0];
//break apart extended searches in $main_search2
$kv_pair_array = explode('/',$main_search2);
//if main_search2 is an extended search: "searchDescription/search"
if (count($kv_pair_array) > 1) {
$field = $kv_pair_array[0];
$value = $kv_pair_array[1];
if ($field == 'selected_subcategory_residential') {
if ($table == 'prop_residential') {
$residential_array = explode('~',$value);
//selected_subcategory_residential will only contain one value
if (count($residential_array) > 1) {
$residential_count = 0;
$where .= '(';
foreach($residential_array as $residential) {
if ($residential_count == 0) {
$where .= "`".$table."`.`L_Type_` LIKE '".trim($residential)."%'";
} else {
$where .= " OR `".$table."`.`L_Type_` LIKE '".trim($residential)."%'";
}
$residential_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`L_Type_` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_subcategory_commercial_sale') {
if ($table == 'prop_commercialLease' || $table == 'prop_commercialSales') {
$commercial_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($commercial_array) > 1) {
$commercial_count = 0;
$where .= '(';
foreach($commercial_array as $commercial) {
if ($commercial_count == 0) {
$where .= "`".$table."`.`L_Type_` LIKE '".trim($commercial)."%'";
} else {
$where .= " OR `".$table."`.`L_Type_` LIKE '".trim($commercial)."%'";
}
$commercial_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`L_Type_` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_level') {
if ($table == 'prop_residential' || $table == 'prop_rental') {
$level_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($level_array) > 1) {
$level_count = 0;
$where .= '(';
foreach($level_array as $level) {
if ($level_count == 0) {
$where .= "`".$table."`.`LM_Char10_7` = '".trim($level)."'";
} else {
$where .= " OR `".$table."`.`LM_Char10_7` = '".trim($level)."'";
}
$level_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_7` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_builder') {
if ($table == 'prop_residential' ) {
$builder_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($builder_array) > 1) {
$builder_count = 0;
$where .= '(';
foreach($builder_array as $builder) {
if ($builder_count == 0) {
$where .= "`".$table."`.`LM_char50_11` = '".trim($builder)."'";
} else {
$where .= " OR `".$table."`.`LM_char50_11` = '".trim($builder)."'";
}
$builder_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`LM_char50_11` = '".trim($value)."'";
$where .= ')';
}
}
else{
return 0;
}
} elseif ($field == 'selected_area') {
$area_array = explode('~', $value);
if (count($area_array) > 1) {
$area_count = 0;
$where .= '(';
foreach ($area_array as $area) {
if ($area_count == 0) {
$where .= "`".$table."`.`L_Area` = '".trim($area)."'";
} else {
$where .= " OR `".$table."`.`L_Area` = '".trim($area)."'";
}
$area_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`L_Area` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'selected_mlsID') {
$mlsID_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($mlsID_array) > 1) {
$mlsID_count = 0;
$where .= '(';
foreach($mlsID_array as $mlsID) {
if ($mlsID_count == 0) {
$where .= "`".$table."`.`L_ListingID` = ".trim($mlsID)."";
} else {
$where .= " OR `".$table."`.`L_ListingID` = ".trim($mlsID)."";
}
$mlsID_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`L_ListingID` = ".trim($value)."";
$where .= ')';
}
} elseif ($field == 'selected_subcategory') {
if ($table == 'prop_residential') {
$subcategory_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($subcategory_array) > 1) {
$subcategory_count = 0;
$where .= '(';
foreach($subcategory_array as $subcategory) {
if ($subcategory_count == 0) {
$where .= "`".$table."`.`L_Type_` = '".trim($subcategory)."'";
} else {
$where .= " OR `".$table."`.`L_Type_` = '".trim($subcategory)."'";
}
$subcategory_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`L_Type_` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_school_elementary') {
if ($table == 'prop_residential') {
$school_elementary_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($school_elementary_array) > 1) {
$school_elementary_count = 0;
$where .= '(';
foreach($school_elementary_array as $school_elementary) {
if ($school_elementary_count == 0) {
$where .= "`".$table."`.`LM_Char10_5` = '".trim($school_elementary)."'";
} else {
$where .= " OR `".$table."`.`LM_Char10_5` = '".trim($school_elementary)."'";
}
$school_elementary_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_5` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_school_middle') {
if ($table == 'prop_residential') {
$school_middle_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($school_middle_array) > 1) {
$school_middle_count = 0;
$where .= '(';
foreach($school_middle_array as $school_middle) {
if ($school_middle_count == 0) {
$where .= "`".$table."`.`LM_Char10_8` = '".esc_sql(trim($school_middle))."'";
} else {
$where .= " OR `".$table."`.`LM_Char10_8` = '".esc_sql(trim($school_middle))."'";
}
$school_middle_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_8` LIKE '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_school_high') {
if ($table == 'prop_residential') {
$school_high_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($school_high_array) > 1) {
$school_high_count = 0;
$where .= '(';
foreach($school_high_array as $school_high) {
if ($school_high_count == 0) {
$where .= "`".$table."`.`LM_Char10_6` = '".trim($school_high)."'";
} else {
$where .= " OR `".$table."`.`LM_Char10_6` = '".trim($school_high)."'";
}
$school_high_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_6` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_subdivision') {
if ($table == 'prop_residential' || $table == 'prop_land' || $table == 'prop_multiFamily' || $table == 'prop_rental') {
$where .= '(';
if (trim($value) == 'None') {
$where .= "`".$table."`.`LM_char10_43` = '' OR `".$table."`.`LM_char10_43` = 'none' OR `".$table."`.`LM_char10_43` IS NULL";
} else {
$multiple_values = explode('~',trim($value));
if(count($multiple_values)){
$i = 1;
foreach($multiple_values as $multiple_value ){
$where .= "`".$table."`.`LM_char10_43` = '".trim($multiple_value)."'";
if($i !=count($multiple_values)){
$where .= " OR ";
$i++;
}
}
}else{
$where .= "`".$table."`.`LM_char10_43` = '".trim($value)."'";
}
}
$where .= ')';
}
} elseif ($field == 'selected_land_description') {
if ($table == 'prop_land') {
$land_description_array = explode('~',$value);
//selected_land_description might contain one value, or several values
if (count($land_description_array) > 1) {
$land_count = 0;
$where .= '(';
foreach($land_description_array as $land_description) {
if ($land_count == 0) {
$where .= "`".$table."`.`LFD_LandDescription_64` LIKE '%".trim($land_description)."%'";
} else {
$where .= " AND `".$table."`.`LFD_LandDescription_64` LIKE '%".trim($land_description)."%'";
}
$land_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`LFD_LandDescription_64` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'price_range') {
$selected_price_array = explode('--',$value);
//price_range will always be two values
$where .= '(';
$price_min = $selected_price_array[0];
$price_max = $selected_price_array[1];
$where .= "`".$table."`.`L_AskingPrice` >= ".$price_min." AND `".$table."`.`L_AskingPrice` <= ".$price_max;
$where .= ')';
} elseif ($field == 'selected_bedrooms') {
if ($table == 'prop_residential' || $table == 'prop_rental') {
$where .= '(';
$where .= "`".$table."`.`LM_Int1_1` >= ".(int)$value;
$where .= ')';
}
} elseif ($field == 'selected_bathrooms') {
if ($table == 'prop_residential' || $table == 'prop_rental') {
$where .= '(';
$where .= "`".$table."`.`LM_Dec_2` >= ".(int)$value;
$where .= ')';
}
} elseif ($field == 'acreage') {
$selected_acreage_array = explode('--',$value);
//acreage will always be two values
$where .= '(';
$acreage_min = $selected_acreage_array[0];
$acreage_max = $selected_acreage_array[1];
$where .= "`".$table."`.`L_NumAcres` >= ".$acreage_min." AND `".$table."`.`L_NumAcres` <= ".$acreage_max;
$where .= ')';
} elseif ($field == 'square_feet') {
if ($table != 'prop_land' && $table != 'prop_multiFamily') {
$selected_square_feet_array = explode('--',$value);
//square_feet will always be two values
$where .= '(';
$square_feet_min = $selected_square_feet_array[0];
$square_feet_max = $selected_square_feet_array[1];
$where .= "`".$table."`.`L_SquareFeet` >= ".$square_feet_min." AND `".$table."`.`L_SquareFeet` <= ".$square_feet_max;
$where .= ')';
}
} elseif ($field == 'year_built') {
if ($table != 'prop_land') {
$selected_year_built_array = explode('--',$value);
//year_built will always be two values
$where .= '(';
$year_built_min = $selected_year_built_array[0];
$year_built_max = $selected_year_built_array[1];
$where .= "`".$table."`.`LM_Int2_2` >= ".$year_built_min." AND `".$table."`.`LM_Int2_2` <= ".$year_built_max;
$where .= ')';
}
} elseif ($field == 'selected_elem') {
if ($table != 'prop_commercialLease' && $table != 'prop_commercialSales') {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_5` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'selected_middle') {
if ($table != 'prop_commercialLease' && $table != 'prop_commercialSales') {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_8` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'selected_high') {
if ($table != 'prop_commercialLease' && $table != 'prop_commercialSales') {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_6` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'open_house_selected') {
$open_house_selected = 1;
} elseif ($field == 'price_reduced_selected') {
$price_reduced_selected = 1;
} elseif ($field == 'new_listing_selected') {
$new_listing_selected = 1;
} elseif ($field == 'acres_selected') {
$selected_acres_array = explode('--',$value);
//acres will always be two values
$where .= '(';
$acres_min = $selected_acres_array[0];
$acres_max = $selected_acres_array[1];
$where .= "`".$table."`.`L_NumAcres` >= ".$acres_min." AND `".$table."`.`L_NumAcres` <= ".$acres_max;
$where .= ')';
} elseif ($field == 'agent_selected') {
$agent_array = explode('~',$value);
if (count($agent_array) > 0){
$agent_count = 0;
$where .= '(';
foreach($agent_array as $agents) {
if ($agent_count == 0){
$where .= "`".$table."`.`L_ListAgent1` = '".trim($agents)."'";
}
else {
$where .= " OR `".$table."`.`L_ListAgent1` = '".trim($agents)."'";
}
$agent_count++;
}
$where .=')';
}
else{
$where .= '(';
$where .= "`".$table."`.`L_ListAgent1` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'office_selected') {
$office_array = explode('~',$value);
//office_selected might contain one value, or several values
if (count($office_array) > 0) {
$office_count = 0;
$where .= '(';
foreach($office_array as $office) {
if ($office_count == 0) {
if ($office == 'any') {
$where .= "`".$table."`.`L_ListOffice1` != ''";
} else {
$where .= "`".$table."`.`L_ListOffice1` = ".trim($office);
}
} else {
if ($office == 'any') {
$where .= " OR `".$table."`.`L_ListOffice1` != ''";
} else {
$where .= " OR `".$table."`.`L_ListOffice1` = ".trim($office);
}
}
$office_count++;
}
$where .= ')';
} else { // user has selected 'any'
$where .= '(';
$where .= "`".$table."`.`L_ListOffice1` != ''";
$where .= ')';
}
} elseif ($field == 'short_sale_selected') {
if ($table == 'prop_residential' || $table == 'prop_land') {
$where .= '(';
$where .= "`".$table."`.`LM_char1_57` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'foreclosure_selected') {
if ($table == 'prop_residential' || $table == 'prop_land') {
$where .= '(';
$where .= "`".$table."`.`LM_char1_21` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'listing_date_selected') {
$today = date('Y-m-d H:i:s');
$selected_listing_date_array = explode('--',$value);
//listing date will always be two values
$where .= '(';
$date_min_val = $selected_listing_date_array[0];
$date_max_val = $selected_listing_date_array[1];
if ($date_min_val == 0) {
$date_min = $today;
$date_max = date('Y-m-d H:i:s', strtotime($today .' -'.$date_max_val.' day'));
} else {
$date_min = date('Y-m-d H:i:s', strtotime($today .' -'.$date_min_val.' day'));
$date_max = date('Y-m-d H:i:s', strtotime($today .' -'.$date_max_val.' day'));
}
$where .= "`".$table."`.`L_ListingDate` <= ".$date_min." AND `".$table."`.`L_ListingDate` >= ".$date_max;
$where .= ')';
} elseif ($field == 'farm_features_selected') {
if ($table == 'prop_residential') {
$farm_features_array = explode('~',$value);
//farm_features_selected might contain one value, or several values
if (count($farm_features_array) > 0) {
$farm_features_count = 0;
$where .= '(';
foreach($farm_features_array as $farm_features) {
if ($farm_features_count == 0) {
if ($farm_features == 'any') {
$where .= "`".$table."`.`LFD_FarmFeatures_246` != ''";
} else {
$where .= "`".$table."`.`LFD_FarmFeatures_246` LIKE '%".trim($farm_features)."%'";
}
} else {
if ($farm_features == 'any') {
$where .= " AND `".$table."`.`LFD_FarmFeatures_246` != ''";
} else {
$where .= " AND `".$table."`.`LFD_FarmFeatures_246` LIKE '%".trim($farm_features)."%'";
}
}
$farm_features_count++;
}
$where .= ')';
} else { // user has selected 'any'
$where .= '(';
$where .= "`".$table."`.`LFD_FarmFeatures_246` != ''";
$where .= ')';
}
}
} elseif ($field == 'search_term_selected') {
$search_term_array = explode('~',$value);
//search_term_selected might contain one value, or several values
if (count($search_term_array) > 0) {
$search_term_count = 0;
$where .= '(';
foreach ($search_term_array as $search_term) {
if ($search_term != 'new construction' && $search_term != 'hoa') {
if ($search_term_count == 0) {
$where .= "`".$table."`.`LR_remarks22` LIKE '%".trim($search_term)."%'";
} else {
$where .= " AND `".$table."`.`LR_remarks22` LIKE '%".trim($search_term)."%'";
}
} else {
if ($search_term == 'new construction') {
if ($table == 'prop_residential' || $table == 'prop_multiFamily') {
if ($search_term_count == 0) {
$where .= "`".$table."`.`LM_char1_22` = 'Yes'";
} else {
$where .= " AND `".$table."`.`LM_char1_22` = 'Yes'";
}
}
} elseif ($search_term == 'hoa') {
if ($table != 'prop_commercialLease' && $table != 'prop_commercialSales') {
if ($search_term_count == 0) {
$where .= "`".$table."`.`LM_Char1_18` = 'Yes'";
} else {
$where .= " AND `".$table."`.`LM_Char1_18` = 'Yes'";
}
}
}
}
$search_term_count++;
}
$where .= ')';
}
} elseif ($field == 'max_year_built_selected') {
$max_year_built_array = explode('~',$value);
//max_year_built_selected might contain one value, or several values
if (count($max_year_built_array) > 0) {
$max_year_built_count = 0;
$where .= '(';
foreach ($max_year_built_array as $max_year_built) {
$year_built_min = 0;
$where .= "`".$table."`.`LM_Int2_2` >= ".$year_built_min." AND `".$table."`.`LM_Int2_2` <= ".$max_year_built;
$where .= ')';
}
}
}
//main_search2 is not an exploded array
} else {
$search_count = 0;
foreach ($search_fields as $field) {
if ($search_count == 0) {
$where .= "(`".$table."`.`".$field."` LIKE '%".trim($main_search2)."%'";
} else {
$where .= " OR `".$table."`.`".$field."` LIKE '%".trim($main_search2)."%'";
}
$search_count++;
}
$where .= ')';
}
} //end main_search_arr only has one search term
} //end main_search != ''
//set variable
$get_open_houses = 0;
//deal with open house and new listings from main search
if ($open_house_selected == 1 || $new_listing_selected == 1) {
if ($open_house_selected == 1) {
if ($where == '') {
$get_open_houses = 1;
$today = date('Y-m-d H:i:s', time());
$fourteen_days_from_now = date('Y-m-d H:i:s', strtotime('+14 days'));
$where .= "(`openHouse`.`OH_EndDateTime` <= '".$fourteen_days_from_now."' AND `openHouse`.`OH_EndDateTime` >= '".$today."')";
$select .= ', openHouse.OH_StartDateTime, openHouse.OH_EndDateTime';
} else {
$get_open_houses = 1;
$today = date('Y-m-d H:i:s', time());
$fourteen_days_from_now = date('Y-m-d H:i:s', strtotime('+14 days'));
$where .= " AND (`openHouse`.`OH_EndDateTime` <= '".$fourteen_days_from_now."' AND `openHouse`.`OH_EndDateTime` >= '".$today."')";
$select .= ', openHouse.OH_StartDateTime, openHouse.OH_EndDateTime';
}
}
if ($new_listing_selected == 1) {
if ($where == '') {
$today = date('Y-m-d H:i:s', time());
$seven_days_ago = date('Y-m-d H:i:s', strtotime('-7 days'));
$where .= "(`".$table."`.`L_InputDate` >= '".$seven_days_ago."' AND `".$table."`.`L_InputDate` <= '".$today."')";
} else {
$today = date('Y-m-d H:i:s', time());
$seven_days_ago = date('Y-m-d H:i:s', strtotime('-7 days'));
$where .= " AND (`".$table."`.`L_InputDate` >= '".$seven_days_ago."' AND `".$table."`.`L_InputDate` <= '".$today."')";
}
}
}
//deal with price reduced from main search
if ($price_reduced_selected == 1) {
if ($where == '') {
$where .= "(`".$table."`.`L_AskingPrice` < `".$table."`.`L_OriginalPrice`)";
} else {
$where .= " AND (`".$table."`.`L_AskingPrice` < `".$table."`.`L_OriginalPrice`)";
}
}
//start building query
$listings = array();
$listing_count = 0;
//$this->db->select($select);
//$this->db->from($table);
$model_join ='';
$model_where='';
$model_group_by = '';
$model_order_by='';
$model_limit='';
//JOINS
if ($get_open_houses == 1) {
//$this->db->join('openHouse', 'openHouse.L_ListingID = '.$table.'.L_ListingID', 'inner');
$model_join .= " inner join openHouse on ".'openHouse.L_ListingID = '.$table.'.L_ListingID';
}
//$this->db->join('activeAgents',$table.'.L_ListAgent1 = activeAgents.U_AgentID', 'left');
//$this->db->join('activeOffice',$table.'.L_ListOffice1 = activeOffice.O_OfficeID', 'left');
$model_join .= " left join activeAgents on ".$table.'.L_ListAgent1 = activeAgents.U_AgentID';
$model_join .= " left join activeOffice on ".$table.'.L_ListOffice1 = activeOffice.O_OfficeID';
//WHERE
if ($ne != '' && $nw != '' && $se != '' && $sw != '') {
$where .= " AND ((`".$table."`.`LMD_MP_Latitude` > ".$nw." AND `".$table."`.`LMD_MP_Latitude` < ".$ne." AND `".$table."`.`LMD_MP_Latitude` > ".$sw." AND `".$table."`.`LMD_MP_Latitude` < ".$se.") AND (`".$table."`.`LMD_MP_Longitude` > ".$sw." AND `".$table."`.`LMD_MP_Longitude` < ".$nw." AND `".$table."`.`LMD_MP_Longitude` > ".$se." AND `".$table."`.`LMD_MP_Longitude` < ".$ne."))";
}
if ($where != '') {
$last_char = substr($where, -1);
if ($last_char != ')') {
$where .= ")";
}
//echo $where;
//$this->db->where($where);
$model_where = "WHERE ".$where;
}
//echo $virtual_tours_only.'--- added virtualtour only filter by vikas.';
if(isset($new_filters['virtual_tours_only']) && $new_filters['virtual_tours_only']==1)
{
if ($model_where == '')
{
$model_where=" where ".$table.".VT_VTourURL!=''";
}
else
{
$model_where.=" and ".$table.".VT_VTourURL!=''";
}
}
//GROUP BY
if ($get_open_houses == 1) {
//$this->db->group_by($table.'.L_ListingID');
$model_group_by = "GROUP BY ".$table.'.L_ListingID';
}
//ORDER BY
//$this->db->order_by($table.'.L_AskingPrice '.$price_sort.', '.$table.'.L_InputDate '.$date_sort);
//RUN QUERY
//$query = $this->db->get();
//echo "select $select from $table $model_join $model_where $model_group_by";
$results = $this->databaseConnection->get_results("select $select from $table $model_join $model_where $model_group_by",ARRAY_A);
if (count($results))
{
$listings = $results;
$listing_count = count($listings);
}
/*echo $this->db->last_query().'
';
die();*/
//return the result
return $listing_count;
}
/**
* perform_search
* @author Amy McCrobie
* @return array $listings an array of the listings based on the search
*/
public function perform_search($limit, $start, $property_type, $main_search, $price_sort, $date_sort,$new_filters=array()) {
//make some property type based decisions
global $disableUnusedCode;
switch ($property_type) {
case "residential":
$table = 'prop_residential';
$class = 'RE_1';
$search_fields = array('L_Address','L_City','L_State','L_ZipCode','L_Zip','L_ListingID',);
$select = $table.'.LM_Int1_1,'.$table.'.LM_Dec_2,'.$table.'.LM_int4_33,'.$table.'.L_SquareFeet,'.$table.'.LM_char10_43,';
break;
case "land":
$table = 'prop_land';
$class = 'VL_2';
$search_fields = array('L_Address','L_City','L_State','L_ZipCode','L_Zip','L_ListingID');
$select = $table.'.LM_char10_43,';
break;
case "commercialLease":
$table = 'prop_commercialLease';
$class = 'CL_3';
$search_fields = array('L_Address','L_City','L_State','L_ZipCode','L_Zip','L_ListingID');
$select = $table.'.L_SquareFeet,';
break;
case "commercialSales":
$table = 'prop_commercialSales';
$class = 'CS_4';
$search_fields = array('L_Address','L_City','L_State','L_ZipCode','L_Zip','L_ListingID');
$select = $table.'.L_SquareFeet,';
break;
case "multiFamily":
$table = 'prop_multiFamily';
$class = 'MF_5';
$search_fields = array('L_Address','L_City','L_State','L_ZipCode','L_Zip','L_ListingID');
$select = $table.'.L_NumUnits,'.$table.'.LM_char10_43,';
break;
case "rental":
$table = 'prop_rental';
$class = 'RN_6';
$search_fields = array('L_Address','L_City','L_State','L_ZipCode','L_Zip','L_ListingID');
$select = $table.'.LM_Int1_1,'.$table.'.LM_Dec_2,'.$table.'.LM_int4_33,'.$table.'.L_SquareFeet,'.$table.'.LM_char10_43,';
break;
case "":
$table = 'prop_residential';
break;
default:
$table = 'prop_residential';
$class = 'RE_1';
$search_fields = array('L_Address','L_City','L_State','L_ZipCode','L_Zip','L_ListingID');
$select = $table.'.LM_Int1_1,'.$table.'.LM_Dec_2,'.$table.'.LM_int4_33,'.$table.'.L_SquareFeet,'.$table.'.LM_char10_43,';
}
$select .= $table.'.LR_remarks22,'.$table.'.L_AskingPrice,'.$table.'.L_Address,'.$table.'.L_City,'.$table.'.L_ListAgent1,'.$table.'.L_ListingID,'.$table.'.L_UpdateDate,';
$select .= $table.'.L_OriginalPrice,'.$table.'.L_InputDate,'.$table.'.L_NumAcres,';
$select .= $table.'.LMD_MP_Latitude,'.$table.'.LMD_MP_Longitude,'.$table.'.VT_VTourURL,';
$select .= 'activeAgents.U_UserFirstName,activeAgents.U_UserLastName,activeAgents.U_PhoneNumber1,activeAgents.U_PhoneNumber2,activeAgents.U_Email,activeAgents.AO_OrganizationName';
//set variables
$where = '';
$open_house_selected = 0;
$price_reduced_selected = 0;
$new_listing_selected = 0;
//deal with where clause for main search
if ($main_search != '') {
$main_search_arr = explode(',',$main_search);
$main_search_count = 0;
//if the main_search array has more than one item to search by
if (count($main_search_arr) > 1) {
foreach ($main_search_arr as $main_search2) {
//if we are on the first iteration through main_search_arr
if ($main_search_count == 0) {
//break apart extended searches in $main_search2
$kv_pair_array = explode('/',$main_search2);
//if main_search2 is an extended search: "searchDescription/search"
if (count($kv_pair_array) > 1) {
$field = $kv_pair_array[0];
$value = $kv_pair_array[1];
if ($field == 'selected_subcategory_residential') {
if ($table == 'prop_residential') {
$residential_array = explode('~',$value);
//selected_subcategory_residential will only contain one value
if (count($residential_array) > 1) {
$residential_count = 0;
$where .= '(';
foreach($residential_array as $residential) {
if ($residential_count == 0) {
$where .= "`".$table."`.`L_Type_` LIKE '".trim($residential)."%'";
} else {
$where .= " OR `".$table."`.`L_Type_` LIKE '".trim($residential)."%'";
}
$residential_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`L_Type_` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_subcategory_commercial_sale') {
if ($table == 'prop_commercialLease' || $table == 'prop_commercialSales') {
$commercial_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($commercial_array) > 1) {
$commercial_count = 0;
$where .= '(';
foreach($commercial_array as $commercial) {
if ($commercial_count == 0) {
$where .= "`".$table."`.`L_Type_` LIKE '".trim($commercial)."%'";
} else {
$where .= " OR `".$table."`.`L_Type_` LIKE '".trim($commercial)."%'";
}
$commercial_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`L_Type_` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_level') {
if ($table == 'prop_residential' || $table == 'prop_rental') {
$level_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($level_array) > 1) {
$level_count = 0;
$where .= '(';
foreach($level_array as $level) {
if ($level_count == 0) {
$where .= "`".$table."`.`LM_Char10_7` = '".trim($level)."'";
} else {
$where .= " OR `".$table."`.`LM_Char10_7` = '".trim($level)."'";
}
$level_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_7` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_builder') {
if ($table == 'prop_residential' ) {
$builder_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($builder_array) > 1) {
$builder_count = 0;
$where .= '(';
foreach($builder_array as $builder) {
if ($builder_count == 0) {
$where .= "`".$table."`.`LM_char50_11` = '".trim($builder)."'";
} else {
$where .= " OR `".$table."`.`LM_char50_11` = '".trim($builder)."'";
}
$builder_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`LM_char50_11` = '".trim($value)."'";
$where .= ')';
}
}
else{
return array();
}
} elseif ($field == 'selected_area') {
$area_array = explode('~', $value);
if (count($area_array) > 1) {
$area_count = 0;
$where .= '(';
foreach ($area_array as $area) {
if ($area_count == 0) {
$where .= "`".$table."`.`L_Area` = '".trim($area)."'";
} else {
$where .= " OR `".$table."`.`L_Area` = '".trim($area)."'";
}
$area_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`L_Area` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'selected_mlsID') {
$mlsID_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($mlsID_array) > 1) {
$mlsID_count = 0;
$where .= '(';
foreach($mlsID_array as $mlsID) {
if ($mlsID_count == 0) {
$where .= "`".$table."`.`L_ListingID` = ".trim($mlsID)."";
} else {
$where .= " OR `".$table."`.`L_ListingID` = ".trim($mlsID)."";
}
$mlsID_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`L_ListingID` = ".trim($value)."";
$where .= ')';
}
} elseif ($field == 'selected_subcategory') {
if ($table == 'prop_residential') {
$subcategory_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($subcategory_array) > 1) {
$subcategory_count = 0;
$where .= '(';
foreach($subcategory_array as $subcategory) {
if ($subcategory_count == 0) {
$where .= "`".$table."`.`L_Type_` = '".trim($subcategory)."'";
} else {
$where .= " OR `".$table."`.`L_Type_` = '".trim($subcategory)."'";
}
$subcategory_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`L_Type_` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_school_elementary') {
if ($table == 'prop_residential') {
$school_elementary_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($school_elementary_array) > 1) {
$school_elementary_count = 0;
$where .= '(';
foreach($school_elementary_array as $school_elementary) {
if ($school_elementary_count == 0) {
$where .= "`".$table."`.`LM_Char10_5` = '".trim($school_elementary)."'";
} else {
$where .= " OR `".$table."`.`LM_Char10_5` = '".trim($school_elementary)."'";
}
$school_elementary_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_5` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_school_middle') {
if ($table == 'prop_residential') {
$school_middle_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($school_middle_array) > 1) {
$school_middle_count = 0;
$where .= '(';
foreach($school_middle_array as $school_middle) {
if ($school_middle_count == 0) {
$where .= "`".$table."`.`LM_Char10_8` = '".esc_sql(trim($school_middle))."'";
} else {
$where .= " OR `".$table."`.`LM_Char10_8` = '".esc_sql(trim($school_middle))."'";
}
$school_middle_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_8` LIKE '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_school_high') {
if ($table == 'prop_residential') {
$school_high_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($school_high_array) > 1) {
$school_high_count = 0;
$where .= '(';
foreach($school_high_array as $school_high) {
if ($school_high_count == 0) {
$where .= "`".$table."`.`LM_Char10_6` = '".trim($school_high)."'";
} else {
$where .= " OR `".$table."`.`LM_Char10_6` = '".trim($school_high)."'";
}
$school_high_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_6` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_subdivision') {
if ($table == 'prop_residential' || $table == 'prop_land' || $table == 'prop_multiFamily' || $table == 'prop_rental') {
$where .= '(';
if (trim($value) == 'None') {
$where .= "`".$table."`.`LM_char10_43` = '' OR `".$table."`.`LM_char10_43` = 'none' OR `".$table."`.`LM_char10_43` IS NULL";
} else {
$multiple_values = explode('~',trim($value));
if(count($multiple_values)){
$i = 1;
foreach($multiple_values as $multiple_value ){
$where .= "`".$table."`.`LM_char10_43` = '".trim($multiple_value)."'";
if($i !=count($multiple_values)){
$where .= " OR ";
$i++;
}
}
}else{
$where .= "`".$table."`.`LM_char10_43` = '".trim($value)."'";
}
}
$where .= ')';
}
} elseif ($field == 'selected_land_description') {
if ($table == 'prop_land') {
$land_description_array = explode('~',$value);
//selected_land_description might contain one value, or several values
if (count($land_description_array) > 1) {
$land_count = 0;
$where .= '(';
foreach($land_description_array as $land_description) {
if ($land_count == 0) {
$where .= "`".$table."`.`LFD_LandDescription_64` LIKE '%".trim($land_description)."%'";
} else {
$where .= " AND `".$table."`.`LFD_LandDescription_64` LIKE '%".trim($land_description)."%'";
}
$land_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`LFD_LandDescription_64` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'price_range') {
$selected_price_array = explode('--',$value);
//price_range will always be two values
$where .= '(';
$price_min = $selected_price_array[0];
$price_max = $selected_price_array[1];
$where .= "`".$table."`.`L_AskingPrice` >= ".$price_min." AND `".$table."`.`L_AskingPrice` <= ".$price_max;
$where .= ')';
} elseif ($field == 'selected_bedrooms') {
if ($table == 'prop_residential' || $table == 'prop_rental') {
$where .= '(';
$where .= "`".$table."`.`LM_Int1_1` >= ".(int)$value;
$where .= ')';
}
} elseif ($field == 'selected_bathrooms') {
if ($table == 'prop_residential' || $table == 'prop_rental') {
$where .= '(';
$where .= "`".$table."`.`LM_Dec_2` >= ".(int)$value;
$where .= ')';
}
} elseif ($field == 'acreage') {
$selected_acreage_array = explode('--',$value);
//acreage will always be two values
$where .= '(';
$acreage_min = $selected_acreage_array[0];
$acreage_max = $selected_acreage_array[1];
$where .= "`".$table."`.`L_NumAcres` >= ".$acreage_min." AND `".$table."`.`L_NumAcres` <= ".$acreage_max;
$where .= ')';
} elseif ($field == 'square_feet') {
if ($table != 'prop_land' && $table != 'prop_multiFamily') {
$selected_square_feet_array = explode('--',$value);
//square_feet will always be two values
$where .= '(';
$square_feet_min = $selected_square_feet_array[0];
$square_feet_max = $selected_square_feet_array[1];
$where .= "`".$table."`.`L_SquareFeet` >= ".$square_feet_min." AND `".$table."`.`L_SquareFeet` <= ".$square_feet_max;
$where .= ')';
}
} elseif ($field == 'year_built') {
if ($table != 'prop_land') {
$selected_year_built_array = explode('--',$value);
//year_built will always be two values
$where .= '(';
$year_built_min = $selected_year_built_array[0];
$year_built_max = $selected_year_built_array[1];
$where .= "`".$table."`.`LM_Int2_2` >= ".$year_built_min." AND `".$table."`.`LM_Int2_2` <= ".$year_built_max;
$where .= ')';
}
} elseif ($field == 'selected_elem') {
if ($table != 'prop_commercialLease' && $table != 'prop_commercialSales') {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_5` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'selected_middle') {
if ($table != 'prop_commercialLease' && $table != 'prop_commercialSales') {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_8` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'selected_high') {
if ($table != 'prop_commercialLease' && $table != 'prop_commercialSales') {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_6` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'open_house_selected') {
$open_house_selected = 1;
} elseif ($field == 'price_reduced_selected') {
$price_reduced_selected = 1;
} elseif ($field == 'new_listing_selected') {
$new_listing_selected = 1;
} elseif ($field == 'acres_selected') {
$selected_acres_array = explode('--',$value);
//acres will always be two values
$where .= '(';
$acres_min = $selected_acres_array[0];
$acres_max = $selected_acres_array[1];
$where .= "`".$table."`.`L_NumAcres` >= ".$acres_min." AND `".$table."`.`L_NumAcres` <= ".$acres_max;
$where .= ')';
} elseif ($field == 'agent_selected') {
$agent_array = explode('~',$value);
if (count($agent_array) > 0){
$agent_count = 0;
$where .= '(';
foreach($agent_array as $agents) {
if ($agent_count == 0){
$where .= "`".$table."`.`L_ListAgent1` = '".trim($agents)."'";
}
else {
$where .= " OR `".$table."`.`L_ListAgent1` = '".trim($agents)."'";
}
$agent_count++;
}
$where .=')';
}
else{
$where .= '(';
$where .= "`".$table."`.`L_ListAgent1` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'office_selected') {
$office_array = explode('~',$value);
//office_selected might contain one value, or several values
if (count($office_array) > 0) {
$office_count = 0;
$where .= '(';
foreach($office_array as $office) {
if ($office_count == 0) {
if ($office == 'any') {
$where .= "`".$table."`.`L_ListOffice1` != ''";
} else {
$where .= "`".$table."`.`L_ListOffice1` = ".trim($office);
}
} else {
if ($office == 'any') {
$where .= " OR `".$table."`.`L_ListOffice1` != ''";
} else {
$where .= " OR `".$table."`.`L_ListOffice1` = ".trim($office);
}
}
$office_count++;
}
$where .= ')';
} else { // user has selected 'any'
$where .= '(';
$where .= "`".$table."`.`L_ListOffice1` != ''";
$where .= ')';
}
} elseif ($field == 'short_sale_selected') {
if ($table == 'prop_residential' || $table == 'prop_land') {
$where .= '(';
$where .= "`".$table."`.`LM_char1_57` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'foreclosure_selected') {
if ($table == 'prop_residential' || $table == 'prop_land') {
$where .= '(';
$where .= "`".$table."`.`LM_char1_21` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'listing_date_selected') {
$today = date('Y-m-d H:i:s');
$selected_listing_date_array = explode('--',$value);
//listing date will always be two values
$where .= '(';
$date_min_val = $selected_listing_date_array[0];
$date_max_val = $selected_listing_date_array[1];
if ($date_min_val == 0) {
$date_min = $today;
$date_max = date('Y-m-d H:i:s', strtotime($today .' -'.$date_max_val.' day'));
} else {
$date_min = date('Y-m-d H:i:s', strtotime($today .' -'.$date_min_val.' day'));
$date_max = date('Y-m-d H:i:s', strtotime($today .' -'.$date_max_val.' day'));
}
$where .= "`".$table."`.`L_ListingDate` <= ".$date_min." AND `".$table."`.`L_ListingDate` >= ".$date_max;
$where .= ')';
} elseif ($field == 'farm_features_selected') {
if ($table == 'prop_residential') {
$farm_features_array = explode('~',$value);
//farm_features_selected might contain one value, or several values
if (count($farm_features_array) > 0) {
$farm_features_count = 0;
$where .= '(';
foreach($farm_features_array as $farm_features) {
if ($farm_features_count == 0) {
if ($farm_features == 'any') {
$where .= "`".$table."`.`LFD_FarmFeatures_246` != ''";
} else {
$where .= "`".$table."`.`LFD_FarmFeatures_246` LIKE '%".trim($farm_features)."%'";
}
} else {
if ($farm_features == 'any') {
$where .= " AND `".$table."`.`LFD_FarmFeatures_246` != ''";
} else {
$where .= " AND `".$table."`.`LFD_FarmFeatures_246` LIKE '%".trim($farm_features)."%'";
}
}
$farm_features_count++;
}
$where .= ')';
} else { // user has selected 'any'
$where .= '(';
$where .= "`".$table."`.`LFD_FarmFeatures_246` != ''";
$where .= ')';
}
}
} elseif ($field == 'search_term_selected') {
$search_term_array = explode('~',$value);
//search_term_selected might contain one value, or several values
if (count($search_term_array) > 0) {
$search_term_count = 0;
$where .= '(';
foreach ($search_term_array as $search_term) {
if ($search_term != 'new construction' && $search_term != 'hoa') {
if ($search_term_count == 0) {
$where .= "`".$table."`.`LR_remarks22` LIKE '%".trim($search_term)."%'";
} else {
$where .= " AND `".$table."`.`LR_remarks22` LIKE '%".trim($search_term)."%'";
}
} else {
if ($search_term == 'new construction') {
if ($table == 'prop_residential' || $table == 'prop_multiFamily') {
if ($search_term_count == 0) {
$where .= "`".$table."`.`LM_char1_22` = 'Yes'";
} else {
$where .= " AND `".$table."`.`LM_char1_22` = 'Yes'";
}
}
} elseif ($search_term == 'hoa') {
if ($table != 'prop_commercialLease' && $table != 'prop_commercialSales') {
if ($search_term_count == 0) {
$where .= "`".$table."`.`LM_Char1_18` = 'Yes'";
} else {
$where .= " AND `".$table."`.`LM_Char1_18` = 'Yes'";
}
}
}
}
$search_term_count++;
}
$where .= ')';
}
} elseif ($field == 'max_year_built_selected') {
$max_year_built_array = explode('~',$value);
//max_year_built_selected might contain one value, or several values
if (count($max_year_built_array) > 0) {
$max_year_built_count = 0;
$where .= '(';
foreach ($max_year_built_array as $max_year_built) {
$year_built_min = 0;
$where .= "`".$table."`.`LM_Int2_2` >= ".$year_built_min." AND `".$table."`.`LM_Int2_2` <= ".$max_year_built;
$where .= ')';
}
}
}
//main_search2 is not an exploded array
} else {
$search_count = 0;
foreach ($search_fields as $field) {
if ($search_count == 0) {
$where .= "(`".$table."`.`".$field."` LIKE '%".trim($main_search2)."%'";
} else {
$where .= " OR `".$table."`.`".$field."` LIKE '%".trim($main_search2)."%'";
}
$search_count++;
}
$where .= ')';
}
////if we are on the 2nd or more iteration through main_search_arr
} elseif ($main_search_count > 0) {
//break apart extended searches in $main_search2
$kv_pair_array = explode('/',$main_search2);
//if main_search2 is an extended search: "searchDescription/search"
if (count($kv_pair_array) > 1) {
$field = $kv_pair_array[0];
$value = $kv_pair_array[1];
if ($where != '') {
if ($field == 'open_house_selected' || $field == 'price_reduced_selected' || $field == 'new_listing_selected' || ($field == 'farm_features_selected' && $table != 'prop_residential')) {
$where .= '';
} else {
$where .= ' AND ';
}
}
if ($field == 'selected_subcategory_residential') {
if ($table == 'prop_residential') {
$residential_array = explode('~',$value);
//selected_subcategory_residential will only contain one value
if (count($residential_array) > 1) {
$residential_count = 0;
$where .= '(';
foreach($residential_array as $residential) {
if ($residential_count == 0) {
$where .= "`".$table."`.`L_Type_` LIKE '".trim($residential)."%'";
} else {
$where .= " OR `".$table."`.`L_Type_` LIKE '".trim($residential)."%'";
}
$residential_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`L_Type_` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_subcategory_commercial_sale') {
if ($table == 'prop_commercialLease' || $table == 'prop_commercialSales') {
$commercial_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($commercial_array) > 1) {
$commercial_count = 0;
$where .= '(';
foreach($commercial_array as $commercial) {
if ($commercial_count == 0) {
$where .= "`".$table."`.`L_Type_` LIKE '".trim($commercial)."%'";
} else {
$where .= " OR `".$table."`.`L_Type_` LIKE '".trim($commercial)."%'";
}
$commercial_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`L_Type_` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_level') {
if ($table == 'prop_residential' || $table == 'prop_rental') {
$level_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($level_array) > 1) {
$level_count = 0;
$where .= '(';
foreach($level_array as $level) {
if ($level_count == 0) {
$where .= "`".$table."`.`LM_Char10_7` = '".trim($level)."'";
} else {
$where .= " OR `".$table."`.`LM_Char10_7` = '".trim($level)."'";
}
$level_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_7` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_builder') {
if ($table == 'prop_residential' ) {
$builder_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($builder_array) > 1) {
$builder_count = 0;
$where .= '(';
foreach($builder_array as $builder) {
if ($builder_count == 0) {
$where .= "`".$table."`.`LM_char50_11` = '".trim($builder)."'";
} else {
$where .= " OR `".$table."`.`LM_char50_11` = '".trim($builder)."'";
}
$builder_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`LM_char50_11` = '".trim($value)."'";
$where .= ')';
}
}
else{
return array();
}
} elseif ($field == 'selected_area') {
$area_array = explode('~', $value);
if (count($area_array) > 1) {
$area_count = 0;
$where .= '(';
foreach ($area_array as $area) {
if ($area_count == 0) {
$where .= "`".$table."`.`L_Area` = '".trim($area)."'";
} else {
$where .= " OR `".$table."`.`L_Area` = '".trim($area)."'";
}
$area_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`L_Area` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'selected_mlsID') {
$mlsID_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($mlsID_array) > 1) {
$mlsID_count = 0;
$where .= '(';
foreach($mlsID_array as $mlsID) {
if ($mlsID_count == 0) {
$where .= "`".$table."`.`L_ListingID` = ".trim($mlsID)."";
} else {
$where .= " OR `".$table."`.`L_ListingID` = ".trim($mlsID)."";
}
$mlsID_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`L_ListingID` = ".trim($value)."";
$where .= ')';
}
} elseif ($field == 'selected_subcategory') {
if ($table == 'prop_residential') {
$subcategory_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($subcategory_array) > 1) {
$subcategory_count = 0;
$where .= '(';
foreach($subcategory_array as $subcategory) {
if ($subcategory_count == 0) {
$where .= "`".$table."`.`L_Type_` = '".trim($subcategory)."'";
} else {
$where .= " OR `".$table."`.`L_Type_` = '".trim($subcategory)."'";
}
$subcategory_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`L_Type_` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_school_elementary') {
if ($table == 'prop_residential') {
$school_elementary_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($school_elementary_array) > 1) {
$school_elementary_count = 0;
$where .= '(';
foreach($school_elementary_array as $school_elementary) {
if ($school_elementary_count == 0) {
$where .= "`".$table."`.`LM_Char10_5` = '".trim($school_elementary)."'";
} else {
$where .= " OR `".$table."`.`LM_Char10_5` = '".trim($school_elementary)."'";
}
$school_elementary_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_5` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_school_middle') {
if ($table == 'prop_residential') {
$school_middle_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($school_middle_array) > 1) {
$school_middle_count = 0;
$where .= '(';
foreach($school_middle_array as $school_middle) {
if ($school_middle_count == 0) {
$where .= "`".$table."`.`LM_Char10_8` = '".esc_sql(trim($school_middle))."'";
} else {
$where .= " OR `".$table."`.`LM_Char10_8` = '".esc_sql(trim($school_middle))."'";
}
$school_middle_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_8` LIKE '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_school_high') {
if ($table == 'prop_residential') {
$school_high_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($school_high_array) > 1) {
$school_high_count = 0;
$where .= '(';
foreach($school_high_array as $school_high) {
if ($school_high_count == 0) {
$where .= "`".$table."`.`LM_Char10_6` = '".trim($school_high)."'";
} else {
$where .= " OR `".$table."`.`LM_Char10_6` = '".trim($school_high)."'";
}
$school_high_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_6` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_subdivision') {
if ($table == 'prop_residential' || $table == 'prop_land' || $table == 'prop_multiFamily' || $table == 'prop_rental') {
$where .= '(';
if (trim($value) == 'None') {
$where .= "`".$table."`.`LM_char10_43` = '' OR `".$table."`.`LM_char10_43` = 'none' OR `".$table."`.`LM_char10_43` IS NULL";
} else {
$multiple_values = explode('~',trim($value));
if(count($multiple_values)){
$i = 1;
foreach($multiple_values as $multiple_value ){
$where .= "`".$table."`.`LM_char10_43` = '".trim($multiple_value)."'";
if($i !=count($multiple_values)){
$where .= " OR ";
$i++;
}
}
}else{
$where .= "`".$table."`.`LM_char10_43` = '".trim($value)."'";
}
}
$where .= ')';
}
} elseif ($field == 'selected_land_description') {
if ($table == 'prop_land') {
$land_description_array = explode('~',$value);
//selected_land_description might contain one value, or several values
if (count($land_description_array) > 1) {
$land_count = 0;
$where .= '(';
foreach($land_description_array as $land_description) {
if ($land_count == 0) {
$where .= "`".$table."`.`LFD_LandDescription_64` LIKE '%".trim($land_description)."%'";
} else {
$where .= " AND `".$table."`.`LFD_LandDescription_64` LIKE '%".trim($land_description)."%'";
}
$land_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`LFD_LandDescription_64` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'price_range') {
$selected_price_array = explode('--',$value);
//price_range will always be two values
$where .= '(';
$price_min = $selected_price_array[0];
$price_max = $selected_price_array[1];
$where .= "`".$table."`.`L_AskingPrice` >= ".$price_min." AND `".$table."`.`L_AskingPrice` <= ".$price_max;
$where .= ')';
} elseif ($field == 'selected_bedrooms') {
if ($table == 'prop_residential' || $table == 'prop_rental') {
$where .= '(';
$where .= "`".$table."`.`LM_Int1_1` >= ".(int)$value;
$where .= ')';
}
} elseif ($field == 'selected_bathrooms') {
if ($table == 'prop_residential' || $table == 'prop_rental') {
$where .= '(';
$where .= "`".$table."`.`LM_Dec_2` >= ".(int)$value;
$where .= ')';
}
} elseif ($field == 'acreage') {
$selected_acreage_array = explode('--',$value);
//acreage will always be two values
$where .= '(';
$acreage_min = $selected_acreage_array[0];
$acreage_max = $selected_acreage_array[1];
$where .= "`".$table."`.`L_NumAcres` >= ".$acreage_min." AND `".$table."`.`L_NumAcres` <= ".$acreage_max;
$where .= ')';
} elseif ($field == 'square_feet') {
if ($table != 'prop_land' && $table != 'prop_multiFamily') {
$selected_square_feet_array = explode('--',$value);
//square_feet will always be two values
$where .= '(';
$square_feet_min = $selected_square_feet_array[0];
$square_feet_max = $selected_square_feet_array[1];
$where .= "`".$table."`.`L_SquareFeet` >= ".$square_feet_min." AND `".$table."`.`L_SquareFeet` <= ".$square_feet_max;
$where .= ')';
}
} elseif ($field == 'year_built') {
if ($table != 'prop_land') {
$selected_year_built_array = explode('--',$value);
//year_built will always be two values
$where .= '(';
$year_built_min = $selected_year_built_array[0];
$year_built_max = $selected_year_built_array[1];
$where .= "`".$table."`.`LM_Int2_2` >= ".$year_built_min." AND `".$table."`.`LM_Int2_2` <= ".$year_built_max;
$where .= ')';
}
} elseif ($field == 'selected_elem') {
if ($table != 'prop_commercialLease' && $table != 'prop_commercialSales') {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_5` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'selected_middle') {
if ($table != 'prop_commercialLease' && $table != 'prop_commercialSales') {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_8` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'selected_high') {
if ($table != 'prop_commercialLease' && $table != 'prop_commercialSales') {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_6` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'open_house_selected') {
$open_house_selected = 1;
} elseif ($field == 'price_reduced_selected') {
$price_reduced_selected = 1;
} elseif ($field == 'new_listing_selected') {
$new_listing_selected = 1;
} elseif ($field == 'acres_selected') {
$selected_acres_array = explode('--',$value);
//acres will always be two values
$where .= '(';
$acres_min = $selected_acres_array[0];
$acres_max = $selected_acres_array[1];
$where .= "`".$table."`.`L_NumAcres` >= ".$acres_min." AND `".$table."`.`L_NumAcres` <= ".$acres_max;
$where .= ')';
} elseif ($field == 'agent_selected') {
$agent_array = explode('~',$value);
if (count($agent_array) > 0){
$agent_count = 0;
$where .= '(';
foreach($agent_array as $agents) {
if ($agent_count == 0){
$where .= "`".$table."`.`L_ListAgent1` = '".trim($agents)."'";
}
else {
$where .= " OR `".$table."`.`L_ListAgent1` = '".trim($agents)."'";
}
$agent_count++;
}
$where .=')';
}
else{
$where .= '(';
$where .= "`".$table."`.`L_ListAgent1` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'office_selected') {
$office_array = explode('~',$value);
//office_selected might contain one value, or several values
if (count($office_array) > 0) {
$office_count = 0;
$where .= '(';
foreach($office_array as $office) {
if ($office_count == 0) {
if ($office == 'any') {
$where .= "`".$table."`.`L_ListOffice1` != ''";
} else {
$where .= "`".$table."`.`L_ListOffice1` = ".trim($office);
}
} else {
if ($office == 'any') {
$where .= " OR `".$table."`.`L_ListOffice1` != ''";
} else {
$where .= " OR `".$table."`.`L_ListOffice1` = ".trim($office);
}
}
$office_count++;
}
$where .= ')';
} else { // user has selected 'any'
$where .= '(';
$where .= "`".$table."`.`L_ListOffice1` != ''";
$where .= ')';
}
} elseif ($field == 'short_sale_selected') {
if ($table == 'prop_residential' || $table == 'prop_land') {
$where .= '(';
$where .= "`".$table."`.`LM_char1_57` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'foreclosure_selected') {
if ($table == 'prop_residential' || $table == 'prop_land') {
$where .= '(';
$where .= "`".$table."`.`LM_char1_21` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'listing_date_selected') {
$today = date('Y-m-d H:i:s');
$selected_listing_date_array = explode('--',$value);
//listing date will always be two values
$where .= '(';
$date_min_val = $selected_listing_date_array[0];
$date_max_val = $selected_listing_date_array[1];
if ($date_min_val == 0) {
$date_min = $today;
$date_max = date('Y-m-d H:i:s', strtotime($today .' -'.$date_max_val.' day'));
} else {
$date_min = date('Y-m-d H:i:s', strtotime($today .' -'.$date_min_val.' day'));
$date_max = date('Y-m-d H:i:s', strtotime($today .' -'.$date_max_val.' day'));
}
$where .= "`".$table."`.`L_ListingDate` <= ".$date_min." AND `".$table."`.`L_ListingDate` >= ".$date_max;
$where .= ')';
} elseif ($field == 'farm_features_selected') {
if ($table == 'prop_residential') {
$farm_features_array = explode('~',$value);
//farm_features_selected might contain one value, or several values
if (count($farm_features_array) > 0) {
$farm_features_count = 0;
$where .= '(';
foreach($farm_features_array as $farm_features) {
if ($farm_features_count == 0) {
if ($farm_features == 'any') {
$where .= "`".$table."`.`LFD_FarmFeatures_246` != ''";
} else {
$where .= "`".$table."`.`LFD_FarmFeatures_246` LIKE '%".trim($farm_features)."%'";
}
} else {
if ($farm_features == 'any') {
$where .= " AND `".$table."`.`LFD_FarmFeatures_246` != ''";
} else {
$where .= " AND `".$table."`.`LFD_FarmFeatures_246` LIKE '%".trim($farm_features)."%'";
}
}
$farm_features_count++;
}
$where .= ')';
} else { // user has selected 'any'
$where .= '(';
$where .= "`".$table."`.`LFD_FarmFeatures_246` != ''";
$where .= ')';
}
}
} elseif ($field == 'search_term_selected') {
$search_term_array = explode('~',$value);
//search_term_selected might contain one value, or several values
if (count($search_term_array) > 0) {
$search_term_count = 0;
$where .= '(';
foreach ($search_term_array as $search_term) {
if ($search_term != 'new construction' && $search_term != 'hoa') {
if ($search_term_count == 0) {
$where .= "`".$table."`.`LR_remarks22` LIKE '%".trim($search_term)."%'";
} else {
$where .= " AND `".$table."`.`LR_remarks22` LIKE '%".trim($search_term)."%'";
}
} else {
if ($search_term == 'new construction') {
if ($table == 'prop_residential' || $table == 'prop_multiFamily') {
if ($search_term_count == 0) {
$where .= "`".$table."`.`LM_char1_22` = 'Yes'";
} else {
$where .= " AND `".$table."`.`LM_char1_22` = 'Yes'";
}
}
} elseif ($search_term == 'hoa') {
if ($table != 'prop_commercialLease' && $table != 'prop_commercialSales') {
if ($search_term_count == 0) {
$where .= "`".$table."`.`LM_Char1_18` = 'Yes'";
} else {
$where .= " AND `".$table."`.`LM_Char1_18` = 'Yes'";
}
}
}
}
$search_term_count++;
}
$where .= ')';
}
} elseif ($field == 'max_year_built_selected') {
$max_year_built_array = explode('~',$value);
//max_year_built_selected might contain one value, or several values
if (count($max_year_built_array) > 0) {
$max_year_built_count = 0;
$where .= '(';
foreach ($max_year_built_array as $max_year_built) {
$year_built_min = 0;
$where .= "`".$table."`.`LM_Int2_2` >= ".$year_built_min." AND `".$table."`.`LM_Int2_2` <= ".$max_year_built;
$where .= ')';
}
}
}
//main_search2 is not an exploded array
} else {
$search_count = 0;
//Added To handle SQL Error
if(is_array($search_fields) && count($search_fields) >0){
$where .= ' AND ';
foreach ($search_fields as $field) {
if ($search_count == 0) {
$where .= "(`".$table."`.`".$field."` LIKE '%".trim($main_search2)."%'";
} else {
$where .= " OR `".$table."`.`".$field."` LIKE '%".trim($main_search2)."%'";
}
$search_count++;
}
$where .= ')';
}
}
} //end main_search_count
$main_search_count++;
} //end foreach main_search_arr
//if main_search_arr only has one search term
} elseif (count($main_search_arr) == 1) {
$main_search2 = $main_search_arr[0];
//break apart extended searches in $main_search2
$kv_pair_array = explode('/',$main_search2);
//if main_search2 is an extended search: "searchDescription/search"
if (count($kv_pair_array) > 1) {
$field = $kv_pair_array[0];
$value = $kv_pair_array[1];
if ($field == 'selected_subcategory_residential') {
if ($table == 'prop_residential') {
$residential_array = explode('~',$value);
//selected_subcategory_residential will only contain one value
if (count($residential_array) > 1) {
$residential_count = 0;
$where .= '(';
foreach($residential_array as $residential) {
if ($residential_count == 0) {
$where .= "`".$table."`.`L_Type_` LIKE '".trim($residential)."%'";
} else {
$where .= " OR `".$table."`.`L_Type_` LIKE '".trim($residential)."%'";
}
$residential_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`L_Type_` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_subcategory_commercial_sale') {
if ($table == 'prop_commercialLease' || $table == 'prop_commercialSales') {
$commercial_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($commercial_array) > 1) {
$commercial_count = 0;
$where .= '(';
foreach($commercial_array as $commercial) {
if ($commercial_count == 0) {
$where .= "`".$table."`.`L_Type_` LIKE '".trim($commercial)."%'";
} else {
$where .= " OR `".$table."`.`L_Type_` LIKE '".trim($commercial)."%'";
}
$commercial_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`L_Type_` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_level') {
if ($table == 'prop_residential' || $table == 'prop_rental') {
$level_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($level_array) > 1) {
$level_count = 0;
$where .= '(';
foreach($level_array as $level) {
if ($level_count == 0) {
$where .= "`".$table."`.`LM_Char10_7` = '".trim($level)."'";
} else {
$where .= " OR `".$table."`.`LM_Char10_7` = '".trim($level)."'";
}
$level_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_7` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_builder') {
if ($table == 'prop_residential' ) {
$builder_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($builder_array) > 1) {
$builder_count = 0;
$where .= '(';
foreach($builder_array as $builder) {
if ($builder_count == 0) {
$where .= "`".$table."`.`LM_char50_11` = '".trim($builder)."'";
} else {
$where .= " OR `".$table."`.`LM_char50_11` = '".trim($builder)."'";
}
$builder_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`LM_char50_11` = '".trim($value)."'";
$where .= ')';
}
}
else{
return array();
}
} elseif ($field == 'selected_area') {
$area_array = explode('~', $value);
if (count($area_array) > 1) {
$area_count = 0;
$where .= '(';
foreach ($area_array as $area) {
if ($area_count == 0) {
$where .= "`".$table."`.`L_Area` = '".trim($area)."'";
} else {
$where .= " OR `".$table."`.`L_Area` = '".trim($area)."'";
}
$area_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`L_Area` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'selected_mlsID') {
$mlsID_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($mlsID_array) > 1) {
$mlsID_count = 0;
$where .= '(';
foreach($mlsID_array as $mlsID) {
if ($mlsID_count == 0) {
$where .= "`".$table."`.`L_ListingID` = ".trim($mlsID)."";
} else {
$where .= " OR `".$table."`.`L_ListingID` = ".trim($mlsID)."";
}
$mlsID_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`L_ListingID` = ".trim($value)."";
$where .= ')';
}
} elseif ($field == 'selected_subcategory') {
if ($table == 'prop_residential') {
$subcategory_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($subcategory_array) > 1) {
$subcategory_count = 0;
$where .= '(';
foreach($subcategory_array as $subcategory) {
if ($subcategory_count == 0) {
$where .= "`".$table."`.`L_Type_` = '".trim($subcategory)."'";
} else {
$where .= " OR `".$table."`.`L_Type_` = '".trim($subcategory)."'";
}
$subcategory_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`L_Type_` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_school_elementary') {
if ($table == 'prop_residential') {
$school_elementary_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($school_elementary_array) > 1) {
$school_elementary_count = 0;
$where .= '(';
foreach($school_elementary_array as $school_elementary) {
if ($school_elementary_count == 0) {
$where .= "`".$table."`.`LM_Char10_5` = '".trim($school_elementary)."'";
} else {
$where .= " OR `".$table."`.`LM_Char10_5` = '".trim($school_elementary)."'";
}
$school_elementary_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_5` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_school_middle') {
if ($table == 'prop_residential') {
$school_middle_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($school_middle_array) > 1) {
$school_middle_count = 0;
$where .= '(';
foreach($school_middle_array as $school_middle) {
if ($school_middle_count == 0) {
$where .= "`".$table."`.`LM_Char10_8` = '".esc_sql(trim($school_middle))."'";
} else {
$where .= " OR `".$table."`.`LM_Char10_8` = '".esc_sql(trim($school_middle))."'";
}
$school_middle_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_8` LIKE '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_school_high') {
if ($table == 'prop_residential') {
$school_high_array = explode('~',$value);
//selected_subcategory_commercial_sale will only contain one value
if (count($school_high_array) > 1) {
$school_high_count = 0;
$where .= '(';
foreach($school_high_array as $school_high) {
if ($school_high_count == 0) {
$where .= "`".$table."`.`LM_Char10_6` = '".trim($school_high)."'";
} else {
$where .= " OR `".$table."`.`LM_Char10_6` = '".trim($school_high)."'";
}
$school_high_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_6` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_subdivision') {
if ($table == 'prop_residential' || $table == 'prop_land' || $table == 'prop_multiFamily' || $table == 'prop_rental') {
$where .= '(';
if (trim($value) == 'None') {
$where .= "`".$table."`.`LM_char10_43` = '' OR `".$table."`.`LM_char10_43` = 'none' OR `".$table."`.`LM_char10_43` IS NULL";
} else {
$multiple_values = explode('~',trim($value));
if(count($multiple_values)){
$i = 1;
foreach($multiple_values as $multiple_value ){
$where .= "`".$table."`.`LM_char10_43` = '".trim($multiple_value)."'";
if($i !=count($multiple_values)){
$where .= " OR ";
$i++;
}
}
}else{
$where .= "`".$table."`.`LM_char10_43` = '".trim($value)."'";
}
}
$where .= ')';
}
} elseif ($field == 'selected_land_description') {
if ($table == 'prop_land') {
$land_description_array = explode('~',$value);
//selected_land_description might contain one value, or several values
if (count($land_description_array) > 1) {
$land_count = 0;
$where .= '(';
foreach($land_description_array as $land_description) {
if ($land_count == 0) {
$where .= "`".$table."`.`LFD_LandDescription_64` LIKE '%".trim($land_description)."%'";
} else {
$where .= " AND `".$table."`.`LFD_LandDescription_64` LIKE '%".trim($land_description)."%'";
}
$land_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`LFD_LandDescription_64` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'price_range') {
$selected_price_array = explode('--',$value);
//price_range will always be two values
$where .= '(';
$price_min = $selected_price_array[0];
$price_max = $selected_price_array[1];
$where .= "`".$table."`.`L_AskingPrice` >= ".$price_min." AND `".$table."`.`L_AskingPrice` <= ".$price_max;
$where .= ')';
} elseif ($field == 'selected_bedrooms') {
if ($table == 'prop_residential' || $table == 'prop_rental') {
$where .= '(';
$where .= "`".$table."`.`LM_Int1_1` >= ".(int)$value;
$where .= ')';
}
} elseif ($field == 'selected_bathrooms') {
if ($table == 'prop_residential' || $table == 'prop_rental') {
$where .= '(';
$where .= "`".$table."`.`LM_Dec_2` >= ".(int)$value;
$where .= ')';
}
} elseif ($field == 'acreage') {
$selected_acreage_array = explode('--',$value);
//acreage will always be two values
$where .= '(';
$acreage_min = $selected_acreage_array[0];
$acreage_max = $selected_acreage_array[1];
$where .= "`".$table."`.`L_NumAcres` >= ".$acreage_min." AND `".$table."`.`L_NumAcres` <= ".$acreage_max;
$where .= ')';
} elseif ($field == 'square_feet') {
if ($table != 'prop_land' && $table != 'prop_multiFamily') {
$selected_square_feet_array = explode('--',$value);
//square_feet will always be two values
$where .= '(';
$square_feet_min = $selected_square_feet_array[0];
$square_feet_max = $selected_square_feet_array[1];
$where .= "`".$table."`.`L_SquareFeet` >= ".$square_feet_min." AND `".$table."`.`L_SquareFeet` <= ".$square_feet_max;
$where .= ')';
}
} elseif ($field == 'year_built') {
if ($table != 'prop_land') {
$selected_year_built_array = explode('--',$value);
//year_built will always be two values
$where .= '(';
$year_built_min = $selected_year_built_array[0];
$year_built_max = $selected_year_built_array[1];
$where .= "`".$table."`.`LM_Int2_2` >= ".$year_built_min." AND `".$table."`.`LM_Int2_2` <= ".$year_built_max;
$where .= ')';
}
} elseif ($field == 'selected_elem') {
if ($table != 'prop_commercialLease' && $table != 'prop_commercialSales') {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_5` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'selected_middle') {
if ($table != 'prop_commercialLease' && $table != 'prop_commercialSales') {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_8` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'selected_high') {
if ($table != 'prop_commercialLease' && $table != 'prop_commercialSales') {
$where .= '(';
$where .= "`".$table."`.`LM_Char10_6` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'open_house_selected') {
$open_house_selected = 1;
} elseif ($field == 'price_reduced_selected') {
$price_reduced_selected = 1;
} elseif ($field == 'new_listing_selected') {
$new_listing_selected = 1;
} elseif ($field == 'acres_selected') {
$selected_acres_array = explode('--',$value);
//acres will always be two values
$where .= '(';
$acres_min = $selected_acres_array[0];
$acres_max = $selected_acres_array[1];
$where .= "`".$table."`.`L_NumAcres` >= ".$acres_min." AND `".$table."`.`L_NumAcres` <= ".$acres_max;
$where .= ')';
} elseif ($field == 'agent_selected') {
$agent_array = explode('~',$value);
if (count($agent_array) > 0){
$agent_count = 0;
$where .= '(';
foreach($agent_array as $agents) {
if ($agent_count == 0){
$where .= "`".$table."`.`L_ListAgent1` = '".trim($agents)."'";
}
else {
$where .= " OR `".$table."`.`L_ListAgent1` = '".trim($agents)."'";
}
$agent_count++;
}
$where .=')';
}
else{
$where .= '(';
$where .= "`".$table."`.`L_ListAgent1` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'office_selected') {
$office_array = explode('~',$value);
//office_selected might contain one value, or several values
if (count($office_array) > 0) {
$office_count = 0;
$where .= '(';
foreach($office_array as $office) {
if ($office_count == 0) {
if ($office == 'any') {
$where .= "`".$table."`.`L_ListOffice1` != ''";
} else {
$where .= "`".$table."`.`L_ListOffice1` = ".trim($office);
}
} else {
if ($office == 'any') {
$where .= " OR `".$table."`.`L_ListOffice1` != ''";
} else {
$where .= " OR `".$table."`.`L_ListOffice1` = ".trim($office);
}
}
$office_count++;
}
$where .= ')';
} else { // user has selected 'any'
$where .= '(';
$where .= "`".$table."`.`L_ListOffice1` != ''";
$where .= ')';
}
} elseif ($field == 'short_sale_selected') {
if ($table == 'prop_residential' || $table == 'prop_land') {
$where .= '(';
$where .= "`".$table."`.`LM_char1_57` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'foreclosure_selected') {
if ($table == 'prop_residential' || $table == 'prop_land') {
$where .= '(';
$where .= "`".$table."`.`LM_char1_21` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'listing_date_selected') {
$today = date('Y-m-d H:i:s');
$selected_listing_date_array = explode('--',$value);
//listing date will always be two values
$where .= '(';
$date_min_val = $selected_listing_date_array[0];
$date_max_val = $selected_listing_date_array[1];
if ($date_min_val == 0) {
$date_min = $today;
$date_max = date('Y-m-d H:i:s', strtotime($today .' -'.$date_max_val.' day'));
} else {
$date_min = date('Y-m-d H:i:s', strtotime($today .' -'.$date_min_val.' day'));
$date_max = date('Y-m-d H:i:s', strtotime($today .' -'.$date_max_val.' day'));
}
$where .= "`".$table."`.`L_ListingDate` <= ".$date_min." AND `".$table."`.`L_ListingDate` >= ".$date_max;
$where .= ')';
} elseif ($field == 'farm_features_selected') {
if ($table == 'prop_residential') {
$farm_features_array = explode('~',$value);
//farm_features_selected might contain one value, or several values
if (count($farm_features_array) > 0) {
$farm_features_count = 0;
$where .= '(';
foreach($farm_features_array as $farm_features) {
if ($farm_features_count == 0) {
if ($farm_features == 'any') {
$where .= "`".$table."`.`LFD_FarmFeatures_246` != ''";
} else {
$where .= "`".$table."`.`LFD_FarmFeatures_246` LIKE '%".trim($farm_features)."%'";
}
} else {
if ($farm_features == 'any') {
$where .= " AND `".$table."`.`LFD_FarmFeatures_246` != ''";
} else {
$where .= " AND `".$table."`.`LFD_FarmFeatures_246` LIKE '%".trim($farm_features)."%'";
}
}
$farm_features_count++;
}
$where .= ')';
} else { // user has selected 'any'
$where .= '(';
$where .= "`".$table."`.`LFD_FarmFeatures_246` != ''";
$where .= ')';
}
}
} elseif ($field == 'search_term_selected') {
$search_term_array = explode('~',$value);
//search_term_selected might contain one value, or several values
if (count($search_term_array) > 0) {
$search_term_count = 0;
$where .= '(';
foreach ($search_term_array as $search_term) {
if ($search_term != 'new construction' && $search_term != 'hoa') {
if ($search_term_count == 0) {
$where .= "`".$table."`.`LR_remarks22` LIKE '%".trim($search_term)."%'";
} else {
$where .= " AND `".$table."`.`LR_remarks22` LIKE '%".trim($search_term)."%'";
}
} else {
if ($search_term == 'new construction') {
if ($table == 'prop_residential' || $table == 'prop_multiFamily') {
if ($search_term_count == 0) {
$where .= "`".$table."`.`LM_char1_22` = 'Yes'";
} else {
$where .= " AND `".$table."`.`LM_char1_22` = 'Yes'";
}
}
} elseif ($search_term == 'hoa') {
if ($table != 'prop_commercialLease' && $table != 'prop_commercialSales') {
if ($search_term_count == 0) {
$where .= "`".$table."`.`LM_Char1_18` = 'Yes'";
} else {
$where .= " AND `".$table."`.`LM_Char1_18` = 'Yes'";
}
}
}
}
$search_term_count++;
}
$where .= ')';
}
} elseif ($field == 'max_year_built_selected') {
$max_year_built_array = explode('~',$value);
//max_year_built_selected might contain one value, or several values
if (count($max_year_built_array) > 0) {
$max_year_built_count = 0;
$where .= '(';
foreach ($max_year_built_array as $max_year_built) {
$year_built_min = 0;
$where .= "`".$table."`.`LM_Int2_2` >= ".$year_built_min." AND `".$table."`.`LM_Int2_2` <= ".$max_year_built;
$where .= ')';
}
}
}
//main_search2 is not an exploded array
} else {
$search_count = 0;
foreach ($search_fields as $field) {
if ($search_count == 0) {
$where .= "(`".$table."`.`".$field."` LIKE '%".trim($main_search2)."%'";
} else {
$where .= " OR `".$table."`.`".$field."` LIKE '%".trim($main_search2)."%'";
}
$search_count++;
}
$where .= ')';
}
} //end main_search_arr only has one search term
} //end main_search != ''
//set variable
$get_open_houses = 0;
//deal with open houses and new listings from main search
if ($open_house_selected == 1 || $new_listing_selected == 1) {
if ($open_house_selected == 1) {
if ($where == '') {
$get_open_houses = 1;
$today = date('Y-m-d H:i:s', time());
$fourteen_days_from_now = date('Y-m-d H:i:s', strtotime('+14 days'));
$where .= "(`openHouse`.`OH_EndDateTime` <= '".$fourteen_days_from_now."' AND `openHouse`.`OH_EndDateTime` >= '".$today."')";
$select .= ', openHouse.OH_StartDateTime, openHouse.OH_EndDateTime';
} else {
$get_open_houses = 1;
$today = date('Y-m-d H:i:s', time());
$fourteen_days_from_now = date('Y-m-d H:i:s', strtotime('+14 days'));
$where .= " AND (`openHouse`.`OH_EndDateTime` <= '".$fourteen_days_from_now."' AND `openHouse`.`OH_EndDateTime` >= '".$today."')";
$select .= ', openHouse.OH_StartDateTime, openHouse.OH_EndDateTime';
}
}
if ($new_listing_selected == 1) {
if ($where == '') {
$today = date('Y-m-d H:i:s', time());
$seven_days_ago = date('Y-m-d H:i:s', strtotime('-7 days'));
$where .= "(`".$table."`.`L_InputDate` >= '".$seven_days_ago."' AND `".$table."`.`L_InputDate` <= '".$today."')";
} else {
$today = date('Y-m-d H:i:s', time());
$seven_days_ago = date('Y-m-d H:i:s', strtotime('-7 days'));
$where .= " AND (`".$table."`.`L_InputDate` >= '".$seven_days_ago."' AND `".$table."`.`L_InputDate` <= '".$today."')";
}
}
}
//deal with price reduced
if ($price_reduced_selected == 1) {
if ($where == '') {
$where .= "(`".$table."`.`L_AskingPrice` < `".$table."`.`L_OriginalPrice`)";
} else {
$where .= " AND (`".$table."`.`L_AskingPrice` < `".$table."`.`L_OriginalPrice`)";
}
}
//start building array
$listings = array();
//$this->db->select($select);
//$this->db->from($table);
$model_join ='';
$model_where='';
$model_group_by = '';
$model_order_by='';
$model_limit='';
//JOINS
if ($get_open_houses == 1) {
//$this->db->join('openHouse', 'openHouse.L_ListingID = '.$table.'.L_ListingID', 'inner');
$model_join .= " inner join openHouse on ".'openHouse.L_ListingID = '.$table.'.L_ListingID';
} /*elseif ($show_open_houses_join == 1) {
$this->db->join('openHouse', $table.'.L_ListingID = openHouse.L_ListingID');
}*/
//$this->db->join('activeAgents',$table.'.L_ListAgent1 = activeAgents.U_AgentID', 'left');
$model_join .= " left join activeAgents on ".$table.'.L_ListAgent1 = activeAgents.U_AgentID';
//$this->db->join('activeOffice',$table.'.L_ListOffice1 = activeOffice.O_OfficeID', 'left');
$model_join .= " left join activeOffice on ".$table.'.L_ListOffice1 = activeOffice.O_OfficeID';
//WHERE
$is_set_where = false;
if ($where != '') {
$is_set_where = true;
$last_char = substr($where, -1);
if ($last_char != ')') {
$where .= ")";
}
//$this->db->where($where);
$model_where = " WHERE ".$where;
}
//GROUP BY
if ($get_open_houses == 1) {
//$this->db->group_by($table.'.L_ListingID');
$model_group_by = " GROUP BY ".$table.'.L_ListingID';
}
//ORDER BY
//$this->db->order_by($table.'.L_AskingPrice '.$price_sort.', '.$table.'.L_InputDate '.$date_sort);
$model_order_by = " ORDER BY ".$table.'.L_AskingPrice '.$price_sort.', '.$table.'.L_InputDate '.$date_sort;
//LIMIT
//$this->db->limit($limit, $start);
$model_limit = " limit $start , $limit ";
//RUN QUERY
//$query = $this->db->get();
//echo "
test-- select $select from $table $model_join $model_where $model_group_by $model_order_by $model_limit";
//exit;
/* code below modified by vikas to get img url in select itself to reduce multiple calls to get image in for loop*/
$select.=',imgsDocs.url, openhouse_timings, VT_ExtVTourURL1, VT_ExtVTourURL2, VT_ExtVTourURL3, VT_ExtVTourURL4, VT_ExtVTourURL5 ';
$model_join.=' left join imgsDocs using(L_ListingID) ';
/***
* ADDED BY VIKAS TO MANAGE SQL SYNTAX ERROR
*/
if(!$is_set_where && $model_where !=''){
$model_where = 'WHERE 1 = 1 '. $model_where;
}
if($model_where != ''){
$model_where.=' and imgsDocs.type=\'image\' and imgsDocs.sort_order=1';
}
else{
$model_where.=' WHERE imgsDocs.type=\'image\' and imgsDocs.sort_order=1';
}
// echo "select $select from $table $model_join $model_where $model_group_by $model_order_by $model_limit";
// exit;
/***
* END ADDED BY VIKAS TO MANAGE SQL SYNTAX ERROR
*/
//echo $virtual_tours_only.'--- added virtualtour only filter by vikas.';
if(isset($new_filters['virtual_tours_only']) && $new_filters['virtual_tours_only']==1)
{
$model_where.=" and ".$table.".VT_VTourURL!=''";
}
/* code above modified by vikas to get img url in select itself to reduce multiple calls to get image in for loop*/
//echo "select $select from $table $model_join $model_where $model_group_by $model_order_by $model_limit";
#exit($where);
$results = $this->databaseConnection->get_results("select $select from $table $model_join $model_where $model_group_by $model_order_by $model_limit",ARRAY_A);
if(count($results)){
$listings = $results;
}
//echo $this->db->last_query().'
';
//set some values to pass along
if (count($listings) > 0) {
foreach ($listings as $element => $arr) {
//set MLS ID
$mls_id = $listings[$element]['L_ListingID'];
//set property_type
$listings[$element]['property_type'] = $property_type;
//set new listing
$listings[$element]['new_listing'] = 'No';
$input_date = str_replace("T", " ", $listings[$element]['L_InputDate']);
$input_date_unix = strtotime($input_date);
$seven_days_ago_unix = strtotime('-7 days');
if($input_date_unix >= $seven_days_ago_unix) {
$listings[$element]['new_listing'] = 'Yes';
}
//set price reduced
$listings[$element]['price_reduced'] = 'No';
if ($listings[$element]['L_AskingPrice'] < $listings[$element]['L_OriginalPrice']) {
$listings[$element]['price_reduced'] = 'Yes';
}
//set open house
$openhouses = array();
//$this->db->select('OH_StartDateTime,OH_EndDateTime,L_ListingID,OH_EndTime,OH_StartTime');
//$this->db->from('openHouse');
//$this->db->where('L_ListingID',$mls_id);
//$this->db->order_by('OH_StartDateTime', 'asc');
//$query = $this->db->get();
// echo "select OH_StartDateTime,OH_EndDateTime,L_ListingID,OH_EndTime,OH_StartTime from openHouse where L_ListingID=$mls_id ORDER BY OH_StartDateTime asc";
/* Below openHouses code has been replace with this new code*/
if(!empty($listings[$element]['openhouse_timings']))
{
//echo $listings[$element]['openhouse_timings'];
$listings[$element]['open_houses']=json_decode($listings[$element]['openhouse_timings'],true);
}
// echo ''; print_r($listings[$element]['open_houses']);
// exit;
/* VK, I think below openhouse related code can be commented
and code below to get image should also be improved */
/* disabled intentionality by vikas vk, as its slowing down too. */
if(!$disableUnusedCode)
{
$results = $this->databaseConnection->get_results("select
OH_StartDateTime,OH_EndDateTime,L_ListingID,OH_EndTime,OH_StartTime
from openHouse where L_ListingID=$mls_id ORDER BY
OH_StartDateTime asc",ARRAY_A);
if (count($results)) {
$openhouses = $results;
}
$listings[$element]['open_houses'] = array();
if (count($openhouses) > 0) {
foreach ($openhouses as $openhouse => $oh_arr)
{
$start_date = str_replace("T", " ", $openhouses[$openhouse]['OH_StartDateTime']);
$start_date_unix = strtotime($start_date);
$start_time = date('g:i a', strtotime($openhouses[$openhouse]['OH_StartTime']));
$end_date = str_replace("T", " ", $openhouses[$openhouse]['OH_EndDateTime']);
$end_date_unix = strtotime($end_date);
$end_time = date('g:i a', strtotime($openhouses[$openhouse]['OH_EndTime']));
$date_in_two_weeks_unix = strtotime('+2 weeks');
$today = time();
if ($end_date_unix <= $date_in_two_weeks_unix && $end_date_unix >= $today) {
$listings[$element]['open_houses'][] = array('start_date' => date('n/d/y', $start_date_unix), 'end_date' => date('n/d/y g:i a', $end_date_unix), 'start_time' => $start_time, 'end_time' => $end_time);
}
}
}
}
//get main image
$listings[$element]['image'] = $arr['url'];
/*
below code commented by vikas as img has been fetched in main sql itself..
$images_array = array();
$images_where = array('L_ListingID' => $mls_id, 'type' => 'image', 'sort_order' => 1);
//$this->db->select('url');
//$this->db->from('imgsDocs');
//$this->db->where($images_where);
//$query = $this->db->get();
$results = $this->databaseConnection->get_row("select url from imgsDocs where (L_ListingID = $mls_id) AND (type = 'image') AND (sort_order = 1) ",ARRAY_A);
if (count($results)) {
$images_array = $results;
$listings[$element]['image'] = $images_array['url'];
}
above code commented by vikas as not its been added in sql itself to avoid repeated calls.
*/
// $listings[$element]['image'] = 'https://cdnparap110.paragonrels.com/ParagonImages/Property/P11/CAAR/534828/0/0/0/5d8051660a4d846fafb95b545b71d482/3/1678b13c80a8e178875f0c4d9ee91653/534828.JPG';
}
}
//return the result
return $listings;
}
function rc_get_featured_listings()
{
/* added by vikas to add this in session to avoid unnecessary calls to database */
if(isset($_SESSION['rcplugin']['featured_listings']))
{
//print_r($_SESSION['rcplugin']['recent_properties']);
return $_SESSION['rcplugin']['featured_listings'];
}
$sql = "select prop_residential.LM_Int1_1,prop_residential.L_DOM,prop_residential.LM_Dec_2,prop_residential.LM_int4_33,prop_residential.L_SquareFeet,prop_residential.LM_char10_43,prop_residential.LR_remarks22,prop_residential.L_AskingPrice,prop_residential.L_Address,prop_residential.L_City,prop_residential.L_ListAgent1,prop_residential.L_ListingID,prop_residential.L_UpdateDate,prop_residential.L_OriginalPrice,prop_residential.L_InputDate,prop_residential.L_NumAcres,prop_residential.LMD_MP_Latitude,prop_residential.LMD_MP_Longitude,prop_residential.VT_VTourURL,activeAgents.U_UserFirstName,activeAgents.U_UserLastName,activeAgents.U_PhoneNumber1,activeAgents.U_PhoneNumber2,activeAgents.U_Email,activeAgents.AO_OrganizationName,imgsDocs.type,imgsDocs.url from prop_residential left join imgsDocs on prop_residential.L_ListingID = imgsDocs.L_ListingID left join activeAgents on prop_residential.L_ListAgent1 = activeAgents.U_AgentID left join activeOffice on prop_residential.L_ListOffice1 = activeOffice.O_OfficeID where (prop_residential.L_City = 'Charlottesville' or prop_residential.L_City = 'Albemarle') and imgsDocs.type = 'image' and imgsDocs.sort_order = '1' and prop_residential.L_OriginalPrice > '1000000' ORDER BY RAND() limit 9";
$results = $this->databaseConnection->get_results($sql,ARRAY_A);
$_SESSION['rcplugin']['featured_listings']=$results; // added by vikas
return $results;
}
public function rc_filter_company_listing_model($office_id,$property_type,$sort_order,$max_listings)
{
switch ($property_type) {
case "residential":
$table = 'prop_residential';
$class = 'RE_1';
$search_fields = array('L_Address','L_City','L_State','L_ZipCode','L_Zip','L_ListingID');
$select = $table.'.LM_Int1_1,'.$table.'.LM_Dec_2,'.$table.'.LM_int4_33,'.$table.'.L_SquareFeet,'.$table.'.LM_char10_43,';
break;
case "land":
$table = 'prop_land';
$class = 'VL_2';
$search_fields = array('L_Address','L_City','L_State','L_ZipCode','L_Zip','L_ListingID');
$select = $table.'.LM_char10_43,';
break;
case "rental":
$table = 'prop_rental';
$class = 'RN_6';
$search_fields = array('L_Address','L_City','L_State','L_ZipCode','L_Zip','L_ListingID');
$select = $table.'.LM_Int1_1,'.$table.'.LM_Dec_2,'.$table.'.LM_int4_33,'.$table.'.L_SquareFeet,'.$table.'.LM_char10_43,';
break;
case "":
$table = 'prop_residential';
break;
default:
$table = 'prop_residential';
$class = 'RE_1';
$search_fields = array('L_Address','L_City','L_State','L_ZipCode','L_Zip','L_ListingID');
$select = $table.'.LM_Int1_1,'.$table.'.LM_Dec_2,'.$table.'.LM_int4_33,'.$table.'.L_SquareFeet,'.$table.'.LM_char10_43,';
}
//since we are just counting, only get the L_ListingID
$select .= $table.'.LR_remarks22,'.$table.'.L_AskingPrice,'.$table.'.L_Address,'.$table.'.L_City,'.$table.'.L_ListAgent1,'.$table.'.L_ListingID,'.$table.'.L_UpdateDate,';
$select .= $table.'.L_OriginalPrice,'.$table.'.L_InputDate,'.$table.'.L_NumAcres,';
$select .= $table.'.LMD_MP_Latitude,'.$table.'.LMD_MP_Longitude,'.$table.'.VT_VTourURL,';
$select .= 'activeAgents.U_UserFirstName,activeAgents.U_UserLastName,activeAgents.U_PhoneNumber1,activeAgents.U_PhoneNumber2,activeAgents.U_Email,activeAgents.AO_OrganizationName';
$model_join = " left join activeAgents on ".$table.'.L_ListAgent1 = activeAgents.U_AgentID';
$model_join .= " left join activeOffice on ".$table.'.L_ListOffice1 = activeOffice.O_OfficeID';
//set variables
if(!empty($office_id))
{
$where .= "WHERE (`$table`.`L_ListOffice1` = $office_id)";
}
$model_order_by = " ORDER BY ".$table.'.L_AskingPrice '.$sort_order.', '.$table.'.L_InputDate '.$date_sort;
//LIMIT
//$this->db->limit($limit, $start);
$model_limit = " limit $max_listings ";
/*$sql = "select prop_residential.LM_Int1_1,prop_residential.LM_Dec_2,prop_residential.LM_int4_33,prop_residential.L_SquareFeet,prop_residential.LM_char10_43,prop_residential.LR_remarks22,prop_residential.L_AskingPrice,prop_residential.L_Address,prop_residential.L_City,prop_residential.L_ListAgent1,prop_residential.L_ListingID,prop_residential.L_UpdateDate,prop_residential.L_OriginalPrice,prop_residential.L_InputDate,prop_residential.L_NumAcres,prop_residential.LMD_MP_Latitude,prop_residential.LMD_MP_Longitude,prop_residential.VT_VTourURL,activeAgents.U_UserFirstName,activeAgents.U_UserLastName,activeAgents.U_PhoneNumber1,activeAgents.U_PhoneNumber2,activeAgents.U_Email,activeAgents.AO_OrganizationName from prop_residential left join activeAgents on prop_residential.L_ListAgent1 = activeAgents.U_AgentID left join activeOffice on prop_residential.L_ListOffice1 = activeOffice.O_OfficeID WHERE (`prop_residential`.`L_ListOffice1` = $office_id) ORDER BY prop_residential.L_AskingPrice DESC, prop_residential.L_InputDate desc limit 0 , 30";*/
//echo '--
'.$sql = "select $select from $table $model_join $where $model_order_by $model_limit";
$results = $this->databaseConnection->get_results($sql,ARRAY_A);
return $results;
}
//get 30 property listing
public function getProperties($limit='',$office_id='',$property_type='',$date_sort='',$price_sort=''){
//make some property type based decisions
switch ($property_type) {
case "residential":
$table = 'prop_residential';
$class = 'RE_1';
$search_fields = array('L_Address','L_City','L_State','L_ZipCode','L_Zip','L_ListingID');
$select = $table.'.LM_Int1_1,'.$table.'.LM_Dec_2,'.$table.'.LM_int4_33,'.$table.'.L_SquareFeet,'.$table.'.LM_char10_43,'.$table.'.L_ListOffice1,';
break;
case "land":
$table = 'prop_land';
$class = 'VL_2';
$search_fields = array('L_Address','L_City','L_State','L_ZipCode','L_Zip','L_ListingID');
$select = $table.'.LM_char10_43,'.$table.'.L_ListOffice1,';
break;
case "commercialLease":
$table = 'prop_commercialLease';
$class = 'CL_3';
$search_fields = array('L_Address','L_City','L_State','L_ZipCode','L_Zip','L_ListingID');
$select = $table.'.L_SquareFeet,'.$table.'.L_ListOffice1,';
break;
case "commercialSales":
$table = 'prop_commercialSales';
$class = 'CS_4';
$search_fields = array('L_Address','L_City','L_State','L_ZipCode','L_Zip','L_ListingID');
$select = $table.'.L_SquareFeet,'.$table.'.L_ListOffice1,';
break;
case "multiFamily":
$table = 'prop_multiFamily';
$class = 'MF_5';
$search_fields = array('L_Address','L_City','L_State','L_ZipCode','L_Zip','L_ListingID');
$select = $table.'.L_NumUnits,'.$table.'.LM_char10_43,'.$table.'.L_ListOffice1,';
break;
case "rental":
$table = 'prop_rental';
$class = 'RN_6';
$search_fields = array('L_Address','L_City','L_State','L_ZipCode','L_Zip','L_ListingID');
$select = $table.'.LM_Int1_1,'.$table.'.LM_Dec_2,'.$table.'.LM_int4_33,'.$table.'.L_SquareFeet,'.$table.'.LM_char10_43,'.$table.'.L_ListOffice1,';
break;
case "":
$table = 'prop_residential';
break;
default:
$table = 'prop_residential';
$class = 'RE_1';
$search_fields = array('L_Address','L_City','L_State','L_ZipCode','L_Zip','L_ListingID');
$select = $table.'.LM_Int1_1,'.$table.'.LM_Dec_2,'.$table.'.LM_int4_33,'.$table.'.L_SquareFeet,'.$table.'.LM_char10_43,'.$table.'.L_ListOffice1,';
}
$select .= $table.'.LR_remarks22,'.$table.'.L_AskingPrice,'.$table.'.L_Address,'.$table.'.L_City,'.$table.'.L_ListAgent1,'.$table.'.L_ListingID,'.$table.'.L_UpdateDate,';
$select .= $table.'.L_OriginalPrice,'.$table.'.L_InputDate,'.$table.'.L_NumAcres,';
$select .= $table.'.LMD_MP_Latitude,'.$table.'.LMD_MP_Longitude,'.$table.'.VT_VTourURL,';
$select .= 'activeAgents.U_UserFirstName,activeAgents.U_UserLastName,activeAgents.U_PhoneNumber1,activeAgents.U_PhoneNumber2,activeAgents.U_Email,activeAgents.AO_OrganizationName';
//set variables
$where = '';
if(!empty($office_id)){
$where .= "(`".$table."`.`L_ListOffice1` = ".trim($office_id).")";
}
//echo $where;
//LIMIT
$start = 0;
$model_limit = " limit $start , $limit ";
$model_join .= " left join activeAgents on ".$table.'.L_ListAgent1 = activeAgents.U_AgentID';
$model_join .= " left join activeOffice on ".$table.'.L_ListOffice1 = activeOffice.O_OfficeID';
if($where != ''){
$model_where = " WHERE ".$where;
}
if(!empty($price_sort) && !empty($date_sort)){
$model_order_by = " ORDER BY ".$table.'.L_AskingPrice '.$price_sort.', '.$table.'.L_InputDate '.$date_sort;
}elseif(!empty($price_sort)){
$model_order_by = " ORDER BY ".$table.'.L_AskingPrice '.$price_sort;
}elseif(!empty($date_sort)){
$model_order_by = " ORDER BY ".$table.'.L_InputDate '.$date_sort;
}else{
$model_order_by = " ORDER BY ".$table.'.L_AskingPrice desc';
}
//echo $model_order_by;
$model_group_by = " GROUP BY ".$table.'.L_ListingID';
//echo "
test-- SELECT $select FROM $table $model_join WHERE $where $model_group_by $model_order_by $model_limit";
$results = $this->databaseConnection->get_results("SELECT $select FROM $table $model_join $model_where $model_group_by $model_order_by $model_limit",ARRAY_A);
if(count($results)){
$listings = $results;
}
if (count($listings) > 0) {
foreach ($listings as $element => $arr) {
//set MLS ID
$mls_id = $listings[$element]['L_ListingID'];
$listings[$element]['property_type'] = $property_type;
$listings[$element]['image'] = '';
$images_array = array();
$results = $this->databaseConnection->get_row("select url from imgsDocs where (L_ListingID = $mls_id) AND (type = 'image') AND (sort_order = 1) ",ARRAY_A);
if (count($results)) {
$images_array = $results;
$listings[$element]['image'] = $images_array['url'];
}
}
}
//return the result
return $listings;
}
//get Properties Without Office Id
public function getPropertiesWithoutOfficeId($limit='',$office_id='',$property_type='',$price_sort='',$areaArray='',$price=''){
//make some property type based decisions
switch ($property_type) {
case "residential":
$table = 'prop_residential';
$class = 'RE_1';
$search_fields = array('L_Address','L_City','L_State','L_ZipCode','L_Zip','L_ListingID');
$select = $table.'.LM_Int1_1,'.$table.'.LM_Dec_2,'.$table.'.LM_int4_33,'.$table.'.L_SquareFeet,'.$table.'.LM_char10_43,'.$table.'.L_ListOffice1,';
break;
case "land":
$table = 'prop_land';
$class = 'VL_2';
$search_fields = array('L_Address','L_City','L_State','L_ZipCode','L_Zip','L_ListingID');
$select = $table.'.LM_char10_43,'.$table.'.L_ListOffice1,';
break;
case "commercialLease":
$table = 'prop_commercialLease';
$class = 'CL_3';
$search_fields = array('L_Address','L_City','L_State','L_ZipCode','L_Zip','L_ListingID');
$select = $table.'.L_SquareFeet,'.$table.'.L_ListOffice1,';
break;
case "commercialSales":
$table = 'prop_commercialSales';
$class = 'CS_4';
$search_fields = array('L_Address','L_City','L_State','L_ZipCode','L_Zip','L_ListingID');
$select = $table.'.L_SquareFeet,'.$table.'.L_ListOffice1,';
break;
case "multiFamily":
$table = 'prop_multiFamily';
$class = 'MF_5';
$search_fields = array('L_Address','L_City','L_State','L_ZipCode','L_Zip','L_ListingID');
$select = $table.'.L_NumUnits,'.$table.'.LM_char10_43,'.$table.'.L_ListOffice1,';
break;
case "rental":
$table = 'prop_rental';
$class = 'RN_6';
$search_fields = array('L_Address','L_City','L_State','L_ZipCode','L_Zip','L_ListingID');
$select = $table.'.LM_Int1_1,'.$table.'.LM_Dec_2,'.$table.'.LM_int4_33,'.$table.'.L_SquareFeet,'.$table.'.LM_char10_43,'.$table.'.L_ListOffice1,';
break;
case "":
$table = 'prop_residential';
break;
default:
$table = 'prop_residential';
$class = 'RE_1';
$search_fields = array('L_Address','L_City','L_State','L_ZipCode','L_Zip','L_ListingID');
$select = $table.'.LM_Int1_1,'.$table.'.LM_Dec_2,'.$table.'.LM_int4_33,'.$table.'.L_SquareFeet,'.$table.'.LM_char10_43,'.$table.'.L_ListOffice1,';
}
$select .= $table.'.LR_remarks22,'.$table.'.L_AskingPrice,'.$table.'.L_Address,'.$table.'.L_City,'.$table.'.L_ListAgent1,'.$table.'.L_ListingID,'.$table.'.L_UpdateDate,';
$select .= $table.'.L_OriginalPrice,'.$table.'.L_InputDate,'.$table.'.L_NumAcres,';
$select .= $table.'.LMD_MP_Latitude,'.$table.'.LMD_MP_Longitude,'.$table.'.VT_VTourURL,';
$select .= 'activeAgents.U_UserFirstName,activeAgents.U_UserLastName,activeAgents.U_PhoneNumber1,activeAgents.U_PhoneNumber2,activeAgents.U_Email,activeAgents.AO_OrganizationName';
//set variables
$where = '';
if(!empty($areaArray)){
$area_array = explode(',',$areaArray);
if (count($area_array) > 1) {
$area_count = 0;
$where .= '(';
foreach ($area_array as $area) {
if ($area_count == 0) {
$where .= "`".$table."`.`L_Area` = '".trim($area)."'";
} else {
$where .= " OR `".$table."`.`L_Area` = '".trim($area)."'";
}
$area_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`L_Area` = '".trim($areaArray)."'";
$where .= ')';
}
}
if(!empty($price)){
$price_array = explode('_',$price);
$price_min = $price_array[0];
$price_max = $price_array[1];
if($where == ''){
$where .= "(`".$table."`.`L_AskingPrice` >= ".$price_min." AND `".$table."`.`L_AskingPrice` <= ".$price_max.")";
}else{
$where .= " AND (`".$table."`.`L_AskingPrice` >= ".$price_min." AND `".$table."`.`L_AskingPrice` <= ".$price_max.")";
}
}
if(!empty($office_id)){
if($where == ''){
$where .= " NOT (`".$table."`.`L_ListOffice1` = ".trim($office_id).")";
}else{
$where .= " AND NOT (`".$table."`.`L_ListOffice1` = ".trim($office_id).")";
}
}
//echo $where;exit;
//LIMIT
$start = 0;
$model_limit = " limit $start , $limit ";
$model_join .= " left join activeAgents on ".$table.'.L_ListAgent1 = activeAgents.U_AgentID';
$model_join .= " left join activeOffice on ".$table.'.L_ListOffice1 = activeOffice.O_OfficeID';
if($where != ''){
$model_where = " WHERE ".$where;
}
if(!empty($price_sort)){
$model_order_by = " ORDER BY ".$table.'.L_AskingPrice '.$price_sort;
}else{
$model_order_by = " ORDER BY ".$table.'.L_AskingPrice desc';
}
//echo $model_order_by;
$model_group_by = " GROUP BY ".$table.'.L_ListingID';
//echo "
test-- SELECT * FROM (SELECT $select FROM $table $model_join $model_where $model_group_by ORDER BY rand() $model_limit) T1 ORDER BY L_AskingPrice desc $model_limit";
$results = $this->databaseConnection->get_results("SELECT * FROM (SELECT $select FROM $table $model_join $model_where $model_group_by ORDER BY rand() $model_limit) T1 ORDER BY L_AskingPrice desc $model_limit",ARRAY_A);
if(count($results)){
$listings = $results;
}
if (count($listings) > 0) {
foreach ($listings as $element => $arr) {
//set MLS ID
$mls_id = $listings[$element]['L_ListingID'];
$listings[$element]['property_type'] = $property_type;
$listings[$element]['image'] = '';
$images_array = array();
$results = $this->databaseConnection->get_row("select url from imgsDocs where (L_ListingID = $mls_id) AND (type = 'image') AND (sort_order = 1) ",ARRAY_A);
if (count($results)) {
$images_array = $results;
$listings[$element]['image'] = $images_array['url'];
}
}
}
//return the result
return $listings;
}
}