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
*/
function count_perform_search($property_type = '', $main_search='', $price_sort='', $date_sort='', $new_filters=array()) {
$search_fields = array('LIST_31','LIST_33','LIST_34','LIST_35','LIST_36','LIST_37','LIST_39','LIST_40','LIST_41','LIST_43','LIST_105');
//make some property type based decisions
//echo $main_search."
";
switch ($property_type) {
case "residential":
$table = 'flex_a';
array_push($search_fields, 'LIST_32');
break;
case "land":
$table = 'flex_b';
break;
case "multiFamily":
$table = 'flex_c';
array_push($search_fields, 'LIST_32');
break;
case "commercial":
$table = 'flex_d';
array_push($search_fields, 'LIST_32');
break;
case "":
$table = 'flex_a,flex_b,flex_c,flex_d';
break;
default:
$table = 'flex_a';
}
//since we are just counting, only get the LIST_105
$select = $table.'.LIST_105';
//set variables
$where = '';
$open_house_selected = 0;
$new_listing_selected = 0;
$price_reduced_selected = 0;
//deal with where clause for main search
if ($main_search != '') {
$main_search_arr = explode(',',$main_search);
$main_search_with_keys = array();
foreach ($main_search_arr as $main_search_item) {
$main_search_key1212 = explode('/',$main_search_item);
$main_search_with_keys[$main_search_key1212[0]] = $main_search_key1212[1];
}
//echo ''. print_r($main_search_with_keys,true) .'
';
$main_search_count = 0;
$open_house_selected = 0;
$price_reduced_selected = 0;
$new_listing_selected = 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 == 'flex_a') {
$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."`.`GF20070114231804462718000000` LIKE '".trim($residential)."%'";
} else {
$where .= " OR `".$table."`.`GF20070114231804462718000000` LIKE '".trim($residential)."%'";
}
$residential_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`GF20070114231804462718000000` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_subcategory_commercial_sale') {
if ($table == 'flex_d') {
$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."`.`GF20070102232336067481000000` LIKE '".trim($commercial)."%'";
} else {
$where .= " OR `".$table."`.`GF20070102232336067481000000` LIKE '".trim($commercial)."%'";
}
$commercial_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`GF20070102232336067481000000` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_area') {
$where .= '(';
$where .= "`".$table."`.`LIST_29` = '".trim($value)."'";
$where .= ')';
} elseif ($field == 'selected_subdivision') {
if ($table == 'flex_a' || $table == 'flex_b' || $table == 'flex_c') {
$where .= '(';
if (trim($value) == 'None') {
$where .= "`".$table."`.`LIST_77` = '' OR `".$table."`.`LIST_77` = 'none' OR `".$table."`.`LIST_77` IS NULL";
} else {
$where .= "`".$table."`.`LIST_77` = '".trim($value)."'";
}
$where .= ')';
}
} elseif ($field == 'selected_land_description') {
if ($table == 'flex_b') {
$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."`.`GF20061106160309770348000000` LIKE '%".trim($land_description)."%'";
//$new_value = trim($land_description);
} else {
$where .= " AND `".$table."`.`GF20061106160309770348000000` LIKE '%".trim($land_description)."%'";
//$new_value .= ','.trim($land_description);
}
$land_count++;
}
//$where .= "`".$table."`.`GF20061106160309770348000000` LIKE '%".trim($new_value)."%'";
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`GF20061106160309770348000000` = '".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."`.`LIST_22` >= ".$price_min." AND `".$table."`.`LIST_22` <= ".$price_max;
$where .= ')';
} elseif ($field == 'selected_bedrooms') {
if ($table == 'flex_a') {
$where .= '(';
$where .= "`".$table."`.`LIST_66` >= ".(int)$value;
$where .= ')';
}
} elseif ($field == 'selected_bathrooms') {
if ($table == 'flex_a') {
$where .= '(';
$where .= "`".$table."`.`LIST_67` >= ".(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."`.`LIST_57` >= ".$acreage_min." AND `".$table."`.`LIST_57` <= ".$acreage_max;
$where .= ')';
} elseif ($field == 'square_feet') {
if ($table != 'flex_b') {
$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."`.`LIST_48` >= ".$square_feet_min." AND `".$table."`.`LIST_48` <= ".$square_feet_max;
$where .= ')';
}
} elseif ($field == 'year_built') {
if ($table != 'flex_b') {
$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."`.`LIST_53` >= ".$year_built_min." AND `".$table."`.`LIST_53` <= ".$year_built_max;
$where .= ')';
}
} elseif ($field == 'selected_elem') {
if ($table != 'flex_d') {
$where .= '(';
$where .= "`".$table."`.`LIST_91` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'selected_middle') {
if ($table != 'flex_d') {
$where .= '(';
$where .= "`".$table."`.`LIST_92` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'selected_high') {
if ($table != 'flex_d') {
$where .= '(';
$where .= "`".$table."`.`LIST_93` = '".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."`.`LIST_57` >= ".$acres_min." AND `".$table."`.`LIST_57` <= ".$acres_max;
$where .= ')';
} elseif ($field == 'agent_selected' && isset($main_search_with_keys['coagent_selected'])) {
$where .= '(';
$where .= "`".$table."`.`LIST_5` = '".trim($value)."'
OR `".$table."`.`LIST_6` = '".trim($main_search_with_keys['coagent_selected'])."'";
$where .= ')';
}
elseif($field == 'agent_selected') {
$where .= '(';
$where .= "`".$table."`.`LIST_5` = '".trim($value)."'";
$where .= ')';
}elseif ($field == 'coagent_selected' && !isset($main_search_with_keys['agent_selected'])) {
$where .= '(';
$where .= "`".$table."`.`LIST_6` = '".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."`.`LIST_106` != ''";
} else {
$where .= "`".$table."`.`LIST_106` = ".trim($office);
}
} else {
if ($office == 'any') {
$where .= " OR `".$table."`.`LIST_106` != ''";
} else {
$where .= " OR `".$table."`.`LIST_106` = ".trim($office);
}
}
$office_count++;
}
$where .= ')';
} else { // user has selected 'any'
$where .= '(';
$where .= "`".$table."`.`LIST_106` != ''";
$where .= ')';
}
/*GF20110914152909386107000000 - “Short Sale” residential
GF20110914153139512433000000 - “Short Sale” land
GF20110914153305831829000000 - “Short Sale” multi-family
GF20110914153602064916000000 - “Short Sale” commercial*/
} elseif ($field == 'short_sale_selected') {
if ($table == 'flex_a') {
$where .= '(';
$where .= "`".$table."`.`GF20110914152909386107000000` = 'Short Sale'";
$where .= ')';
} elseif ($table == 'flex_b') {
$where .= '(';
$where .= "`".$table."`.`GF20110914153139512433000000` = 'Short Sale'";
$where .= ')';
} elseif ($table == 'flex_c') {
$where .= '(';
$where .= "`".$table."`.`GF20110914153305831829000000` = 'Short Sale'";
$where .= ')';
} elseif ($table == 'flex_d') {
$where .= '(';
$where .= "`".$table."`.`GF20110914153602064916000000` = 'Short Sale'";
$where .= ')';
}
/*} elseif ($field == 'foreclosure_selected') {
if ($table == 'flex_a' || $table == 'flex_b') {
$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."`.`LIST_132` <= '".$date_min."' AND `".$table."`.`LIST_132` >= '".$date_max."'";
$where .= ')';
} elseif ($field == 'farm_features_selected') {
/*GF20061205204632343461000000 - Farm Features residential (Lookup - GFLU20061106160309770413000000)
GF20061106160309770413000000 - Farm Features land*/
if ($table == 'flex_a') {
$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."`.`GF20061205204632343461000000` != ''";
} else {
$where .= "`".$table."`.`GF20061205204632343461000000` LIKE '%".trim($farm_features)."%'";
}
} else {
if ($farm_features == 'any') {
$where .= " AND `".$table."`.`GF20061205204632343461000000` != ''";
} else {
$where .= " AND `".$table."`.`GF20061205204632343461000000` LIKE '%".trim($farm_features)."%'";
}
}
$farm_features_count++;
}
$where .= ')';
} else { // user has selected 'any'
$where .= '(';
$where .= "`".$table."`.`GF20061205204632343461000000` != ''";
$where .= ')';
}
} elseif ($table == 'flex_b') {
$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."`.`GF20061106160309770413000000` != ''";
} else {
$where .= "`".$table."`.`GF20061106160309770413000000` LIKE '%".trim($farm_features)."%'";
}
} else {
if ($farm_features == 'any') {
$where .= " AND `".$table."`.`GF20061106160309770413000000` != ''";
} else {
$where .= " AND `".$table."`.`GF20061106160309770413000000` LIKE '%".trim($farm_features)."%'";
}
}
$farm_features_count++;
}
$where .= ')';
} else { // user has selected 'any'
$where .= '(';
$where .= "`".$table."`.`GF20061106160309770413000000` != ''";
$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."`.`LIST_78` LIKE '%".trim($search_term)."%'";
} else {
$where .= " AND `".$table."`.`LIST_78` LIKE '%".trim($search_term)."%'";
}
} else {
if ($search_term == 'new construction' && $table == 'flex_a') {
if ($search_term_count == 0) {
$where .= "`".$table."`.`GF20061205153446881168000000` LIKE '%New Construction%'";
} else {
$where .= " AND `".$table."`.`GF20061205153446881168000000` LIKE '%New Construction%'";
}
} /*elseif ($search_term == 'hoa') {
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."`.`LIST_53` >= ".$year_built_min." AND `".$table."`.`LIST_53` <= ".$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 != '') {
//|| ($field == 'farm_features_selected' && $table != 'flex_a' || $field == 'farm_features_selected' && $table != 'flex_b')
if ($field == 'open_house_selected' || $field == 'price_reduced_selected' || $field == 'new_listing_selected' || ($field == 'farm_features_selected' && $table != 'flex_a' && $field == 'farm_features_selected' && $table != 'flex_b')) {
$where .= '';
} else {
$where .= ' AND ';
}
}
if ($field == 'selected_subcategory_residential') {
if ($table == 'flex_a') {
$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."`.`GF20070114231804462718000000` LIKE '".trim($residential)."%'";
} else {
$where .= " OR `".$table."`.`GF20070114231804462718000000` LIKE '".trim($residential)."%'";
}
$residential_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`GF20070114231804462718000000` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_subcategory_commercial_sale') {
if ($table == 'flex_d') {
$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."`.`GF20070102232336067481000000` LIKE '".trim($commercial)."%'";
} else {
$where .= " OR `".$table."`.`GF20070102232336067481000000` LIKE '".trim($commercial)."%'";
}
$commercial_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`GF20070102232336067481000000` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_area') {
$where .= '(';
$where .= "`".$table."`.`LIST_29` = '".trim($value)."'";
$where .= ')';
} elseif ($field == 'selected_subdivision') {
if ($table == 'flex_a' || $table == 'flex_b' || $table == 'flex_c') {
$where .= '(';
if (trim($value) == 'None') {
$where .= "`".$table."`.`LIST_77` = '' OR `".$table."`.`LIST_77` = 'none' OR `".$table."`.`LIST_77` IS NULL";
} else {
$where .= "`".$table."`.`LIST_77` = '".trim($value)."'";
}
$where .= ')';
}
} elseif ($field == 'selected_land_description') {
if ($table == 'flex_b') {
$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."`.`GF20061106160309770348000000` LIKE '%".trim($land_description)."%'";
//$new_value = trim($land_description);
} else {
$where .= " AND `".$table."`.`GF20061106160309770348000000` LIKE '%".trim($land_description)."%'";
//$new_value .= ','.trim($land_description);
}
$land_count++;
}
//$where .= "`".$table."`.`GF20061106160309770348000000` LIKE '%".trim($new_value)."%'";
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`GF20061106160309770348000000` = '".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."`.`LIST_22` >= ".$price_min." AND `".$table."`.`LIST_22` <= ".$price_max;
$where .= ')';
} elseif ($field == 'selected_bedrooms') {
if ($table == 'flex_a') {
$where .= '(';
$where .= "`".$table."`.`LIST_66` >= ".(int)$value;
$where .= ')';
}
} elseif ($field == 'selected_bathrooms') {
if ($table == 'flex_a') {
$where .= '(';
$where .= "`".$table."`.`LIST_67` >= ".(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."`.`LIST_57` >= ".$acreage_min." AND `".$table."`.`LIST_57` <= ".$acreage_max;
$where .= ')';
} elseif ($field == 'square_feet') {
if ($table != 'flex_b') {
$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."`.`LIST_48` >= ".$square_feet_min." AND `".$table."`.`LIST_48` <= ".$square_feet_max;
$where .= ')';
}
} elseif ($field == 'year_built') {
if ($table != 'flex_b') {
$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."`.`LIST_53` >= ".$year_built_min." AND `".$table."`.`LIST_53` <= ".$year_built_max;
$where .= ')';
}
} elseif ($field == 'selected_elem') {
if ($table != 'flex_d') {
$where .= '(';
$where .= "`".$table."`.`LIST_91` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'selected_middle') {
if ($table != 'flex_d') {
$where .= '(';
$where .= "`".$table."`.`LIST_92` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'selected_high') {
if ($table != 'flex_d') {
$where .= '(';
$where .= "`".$table."`.`LIST_93` = '".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."`.`LIST_57` >= ".$acres_min." AND `".$table."`.`LIST_57` <= ".$acres_max;
$where .= ')';
} elseif ($field == 'agent_selected' && isset($main_search_with_keys['coagent_selected'])) {
$where .= '(';
$where .= "`".$table."`.`LIST_5` = '".trim($value)."'
OR `".$table."`.`LIST_6` = '".trim($main_search_with_keys['coagent_selected'])."'";
$where .= ')';
}
elseif($field == 'agent_selected') {
$where .= '(';
$where .= "`".$table."`.`LIST_5` = '".trim($value)."'";
$where .= ')';
}elseif ($field == 'coagent_selected' && !isset($main_search_with_keys['agent_selected'])) {
$where .= '(';
$where .= "`".$table."`.`LIST_6` = '".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."`.`LIST_106` != ''";
} else {
$where .= "`".$table."`.`LIST_106` = ".trim($office);
}
} else {
if ($office == 'any') {
$where .= " OR `".$table."`.`LIST_106` != ''";
} else {
$where .= " OR `".$table."`.`LIST_106` = ".trim($office);
}
}
$office_count++;
}
$where .= ')';
} else { // user has selected 'any'
$where .= '(';
$where .= "`".$table."`.`LIST_106` != ''";
$where .= ')';
}
} elseif ($field == 'short_sale_selected') {
if ($table == 'flex_a') {
$where .= '(';
$where .= "`".$table."`.`GF20110914152909386107000000` = 'Short Sale'";
$where .= ')';
} elseif ($table == 'flex_b') {
$where .= '(';
$where .= "`".$table."`.`GF20110914153139512433000000` = 'Short Sale'";
$where .= ')';
} elseif ($table == 'flex_c') {
$where .= '(';
$where .= "`".$table."`.`GF20110914153305831829000000` = 'Short Sale'";
$where .= ')';
} elseif ($table == 'flex_d') {
$where .= '(';
$where .= "`".$table."`.`GF20110914153602064916000000` = 'Short Sale'";
$where .= ')';
}
/*} elseif ($field == 'foreclosure_selected') {
if ($table == 'flex_a' || $table == 'flex_b') {
$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."`.`LIST_132` <= '".$date_min."' AND `".$table."`.`LIST_132` >= '".$date_max."'";
$where .= ')';
} elseif ($field == 'farm_features_selected') {
if ($table == 'flex_a') {
$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."`.`GF20061205204632343461000000` != ''";
} else {
$where .= "`".$table."`.`GF20061205204632343461000000` LIKE '%".trim($farm_features)."%'";
}
} else {
if ($farm_features == 'any') {
$where .= " AND `".$table."`.`GF20061205204632343461000000` != ''";
} else {
$where .= " AND `".$table."`.`GF20061205204632343461000000` LIKE '%".trim($farm_features)."%'";
}
}
$farm_features_count++;
}
$where .= ')';
} else { // user has selected 'any'
$where .= '(';
$where .= "`".$table."`.`GF20061205204632343461000000` != ''";
$where .= ')';
}
} elseif ($table == 'flex_b') {
$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."`.`GF20061106160309770413000000` != ''";
} else {
$where .= "`".$table."`.`GF20061106160309770413000000` LIKE '%".trim($farm_features)."%'";
}
} else {
if ($farm_features == 'any') {
$where .= " AND `".$table."`.`GF20061106160309770413000000` != ''";
} else {
$where .= " AND `".$table."`.`GF20061106160309770413000000` LIKE '%".trim($farm_features)."%'";
}
}
$farm_features_count++;
}
$where .= ')';
} else { // user has selected 'any'
$where .= '(';
$where .= "`".$table."`.`GF20061106160309770413000000` != ''";
$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."`.`LIST_78` LIKE '%".trim($search_term)."%'";
} else {
$where .= " AND `".$table."`.`LIST_78` LIKE '%".trim($search_term)."%'";
}
} else {
if ($search_term == 'new construction' && $table == 'flex_a') {
if ($search_term_count == 0) {
$where .= "`".$table."`.`GF20061205153446881168000000` LIKE '%New Construction%'";
} else {
$where .= " AND `".$table."`.`GF20061205153446881168000000` LIKE '%New Construction%'";
}
} /*elseif ($search_term == 'hoa') {
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."`.`LIST_53` >= ".$year_built_min." AND `".$table."`.`LIST_53` <= ".$max_year_built;
$where .= ')';
}
}
}
//main_search2 is not an exploded array
} else {
$search_count = 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 == 'flex_a') {
$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."`.`GF20070114231804462718000000` LIKE '".trim($residential)."%'";
} else {
$where .= " OR `".$table."`.`GF20070114231804462718000000` LIKE '".trim($residential)."%'";
}
$residential_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`GF20070114231804462718000000` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_subcategory_commercial_sale') {
if ($table == 'flex_d') {
$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."`.`GF20070102232336067481000000` LIKE '".trim($commercial)."%'";
} else {
$where .= " OR `".$table."`.`GF20070102232336067481000000` LIKE '".trim($commercial)."%'";
}
$commercial_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`GF20070102232336067481000000` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_area') {
$where .= '(';
$where .= "`".$table."`.`LIST_29` = '".trim($value)."'";
$where .= ')';
} elseif ($field == 'selected_subdivision') {
if ($table == 'flex_a' || $table == 'flex_b' || $table == 'flex_c') {
$where .= '(';
if (trim($value) == 'None') {
$where .= "`".$table."`.`LIST_77` = '' OR `".$table."`.`LIST_77` = 'none' OR `".$table."`.`LIST_77` IS NULL";
} else {
$where .= "`".$table."`.`LIST_77` = '".trim($value)."'";
}
$where .= ')';
}
} elseif ($field == 'selected_land_description') {
if ($table == 'flex_b') {
$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."`.`GF20061106160309770348000000` LIKE '%".trim($land_description)."%'";
} else {
$where .= " AND `".$table."`.`GF20061106160309770348000000` LIKE '%".trim($land_description)."%'";
}
$land_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`GF20061106160309770348000000` = '".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."`.`LIST_22` >= ".$price_min." AND `".$table."`.`LIST_22` <= ".$price_max;
$where .= ')';
} elseif ($field == 'selected_bedrooms') {
if ($table == 'flex_a') {
$where .= '(';
$where .= "`".$table."`.`LIST_66` >= ".(int)$value;
$where .= ')';
}
} elseif ($field == 'selected_bathrooms') {
if ($table == 'flex_a') {
$where .= '(';
$where .= "`".$table."`.`LIST_67` >= ".(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."`.`LIST_57` >= ".$acreage_min." AND `".$table."`.`LIST_57` <= ".$acreage_max;
$where .= ')';
} elseif ($field == 'square_feet') {
if ($table != 'flex_b') {
$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."`.`LIST_48` >= ".$square_feet_min." AND `".$table."`.`LIST_48` <= ".$square_feet_max;
$where .= ')';
}
} elseif ($field == 'year_built') {
if ($table != 'flex_b') {
$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."`.`LIST_53` >= ".$year_built_min." AND `".$table."`.`LIST_53` <= ".$year_built_max;
$where .= ')';
}
} elseif ($field == 'selected_elem') {
if ($table != 'flex_d') {
$where .= '(';
$where .= "`".$table."`.`LIST_91` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'selected_middle') {
if ($table != 'flex_d') {
$where .= '(';
$where .= "`".$table."`.`LIST_92` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'selected_high') {
if ($table != 'flex_d') {
$where .= '(';
$where .= "`".$table."`.`LIST_93` = '".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."`.`LIST_57` >= ".$acres_min." AND `".$table."`.`LIST_57` <= ".$acres_max;
$where .= ')';
} elseif ($field == 'agent_selected' && isset($main_search_with_keys['coagent_selected'])) {
$where .= '(';
$where .= "`".$table."`.`LIST_5` = '".trim($value)."'
OR `".$table."`.`LIST_6` = '".trim($main_search_with_keys['coagent_selected'])."'";
$where .= ')';
}
elseif($field == 'agent_selected') {
$where .= '(';
$where .= "`".$table."`.`LIST_5` = '".trim($value)."'";
$where .= ')';
}elseif ($field == 'coagent_selected' && !isset($main_search_with_keys['agent_selected'])) {
$where .= '(';
$where .= "`".$table."`.`LIST_6` = '".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."`.`LIST_106` != ''";
} else {
$where .= "`".$table."`.`LIST_106` = ".trim($office);
}
} else {
if ($office == 'any') {
$where .= " OR `".$table."`.`LIST_106` != ''";
} else {
$where .= " OR `".$table."`.`LIST_106` = ".trim($office);
}
}
$office_count++;
}
$where .= ')';
} else { // user has selected 'any'
$where .= '(';
$where .= "`".$table."`.`LIST_106` != ''";
$where .= ')';
}
} elseif ($field == 'short_sale_selected') {
if ($table == 'flex_a') {
$where .= '(';
$where .= "`".$table."`.`GF20110914152909386107000000` = 'Short Sale'";
$where .= ')';
} elseif ($table == 'flex_b') {
$where .= '(';
$where .= "`".$table."`.`GF20110914153139512433000000` = 'Short Sale'";
$where .= ')';
} elseif ($table == 'flex_c') {
$where .= '(';
$where .= "`".$table."`.`GF20110914153305831829000000` = 'Short Sale'";
$where .= ')';
} elseif ($table == 'flex_d') {
$where .= '(';
$where .= "`".$table."`.`GF20110914153602064916000000` = 'Short Sale'";
$where .= ')';
}
/*} elseif ($field == 'foreclosure_selected') {
if ($table == 'flex_a' || $table == 'flex_b') {
$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."`.`LIST_132` <= '".$date_min."' AND `".$table."`.`LIST_132` >= '".$date_max."'";
$where .= ')';
} elseif ($field == 'farm_features_selected') {
if ($table == 'flex_a') {
$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."`.`GF20061205204632343461000000` != ''";
} else {
$where .= "`".$table."`.`GF20061205204632343461000000` LIKE '%".trim($farm_features)."%'";
}
} else {
if ($farm_features == 'any') {
$where .= " AND `".$table."`.`GF20061205204632343461000000` != ''";
} else {
$where .= " AND `".$table."`.`GF20061205204632343461000000` LIKE '%".trim($farm_features)."%'";
}
}
$farm_features_count++;
}
$where .= ')';
} else { // user has selected 'any'
$where .= '(';
$where .= "`".$table."`.`GF20061205204632343461000000` != ''";
$where .= ')';
}
} elseif ($table == 'flex_b') {
$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."`.`GF20061106160309770413000000` != ''";
} else {
$where .= "`".$table."`.`GF20061106160309770413000000` LIKE '%".trim($farm_features)."%'";
}
} else {
if ($farm_features == 'any') {
$where .= " AND `".$table."`.`GF20061106160309770413000000` != ''";
} else {
$where .= " AND `".$table."`.`GF20061106160309770413000000` LIKE '%".trim($farm_features)."%'";
}
}
$farm_features_count++;
}
$where .= ')';
} else { // user has selected 'any'
$where .= '(';
$where .= "`".$table."`.`GF20061106160309770413000000` != ''";
$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."`.`LIST_78` LIKE '%".trim($search_term)."%'";
} else {
$where .= " AND `".$table."`.`LIST_78` LIKE '%".trim($search_term)."%'";
}
} else {
if ($search_term == 'new construction' && $table == 'flex_a') {
if ($search_term_count == 0) {
$where .= "`".$table."`.`GF20061205153446881168000000` LIKE '%New Construction%'";
} else {
$where .= " AND `".$table."`.`GF20061205153446881168000000` LIKE '%New Construction%'";
}
} /*elseif ($search_term == 'hoa') {
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."`.`LIST_53` >= ".$year_built_min." AND `".$table."`.`LIST_53` <= ".$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 .= "(`flex_openHouse`.`EVENT200` <= '".$fourteen_days_from_now."' AND `flex_openHouse`.`EVENT200` >= '".$today."')";
$select .= ', flex_openHouse.EVENT100, flex_openHouse.EVENT200';
} 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 (`flex_openHouse`.`EVENT200` <= '".$fourteen_days_from_now."' AND `flex_openHouse`.`EVENT200` >= '".$today."')";
$select .= ', flex_openHouse.EVENT100, flex_openHouse.EVENT200';
}
}
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."`.`LIST_132` >= '".$seven_days_ago."' AND `".$table."`.`LIST_132` <= '".$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."`.`LIST_132` >= '".$seven_days_ago."' AND `".$table."`.`LIST_132` <= '".$today."')";
}
}
}
//deal with price reduced from main search
if ($price_reduced_selected == 1) {
if ($where == '') {
$where .= "(`".$table."`.`LIST_22` < `".$table."`.`LIST_22`)";
} else {
$where .= " AND (`".$table."`.`LIST_22` < `".$table."`.`LIST_22`)";
}
}
//SET 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('flex_openHouse', 'flex_openHouse.LIST1 = '.$table.'.LIST_1', 'inner');
$model_join .= " inner join flex_openHouse on ".'flex_openHouse.LIST1 = '.$table.'.LIST_1';
}
//$this->db->join('flex_activeAgents',$table.'.LIST_5 = flex_activeAgents.MEMBER_0', 'left');
$model_join .= " left join flex_activeAgents on ".$table.'.LIST_5 = flex_activeAgents.MEMBER_0';
//$this->db->join('flex_activeOffice',$table.'.LIST_106 = flex_activeOffice.OFFICE_0', 'left');
$model_join .= " left join flex_activeOffice on ".$table.'.LIST_106 = flex_activeOffice.OFFICE_0';
//WHERE
if ($where != '') {
//$this->db->where($where);
$model_where = "where ".$where;
}
if($model_where==''){
$model_where=' where 1=1 ';
}
//echo $virtual_tours_only.'--- added virtualtour only filter by vikas.';
//echo $farms_only.'--- added farms_only filter by vikas.';
if(isset($new_filters['farms_only']) && $new_filters['farms_only']==1)
{
$model_where.=" and ".$table.".LIST_9='farm'";
}
//GROUP BY
if ($get_open_houses == 1) {
//$this->db->group_by($table.'.LIST_105');
$model_group_by = " GROUP BY ".$table.'.L_ListingID';
}
//ORDER BY
//$this->db->order_by($table.'.LIST_22 '.$price_sort.', '.$table.'.LIST_132 '.$date_sort);
$model_order_by = " ORDER BY ".$table.'.LIST_22 '.$price_sort.', '.$table.'.LIST_132 '.$date_sort;
//RUN QUERY
//$query = $this->db->get();
//echo "select $select from $table $model_join $model_where $model_group_by $model_order_by ";
$results = $this->databaseConnection->get_results("select $select from $table $model_join $model_where $model_group_by $model_order_by ",ARRAY_A);
if(count($results)){
$listings = $results;
$listing_count = count($listings);
}
//echo $this->db->last_query().'
';
//return the result
return $listing_count;
}
/******************************************************************************************************************
*******************************************************************************************************************
*******************************************************************************************************************
*******************************************************************************************************************
*******************************************************************************************************************
*******************************************************************************************************************/
/**
* perform_search
* @author Amy McCrobie
* @return array $listings an array of the listings based on the search
*/
function perform_search($limit, $start, $property_type, $main_search, $price_sort, $date_sort,$new_filters=array()) {
global $disableUnusedCode;
// echo $main_search;
$search_fields = array('LIST_31','LIST_33','LIST_34','LIST_35','LIST_36','LIST_37','LIST_39','LIST_40','LIST_41','LIST_43','LIST_105');
//make some property type based decisions
switch ($property_type) {
case "residential":
$table = 'flex_a';
$class = 'A';
array_push($search_fields, 'LIST_32');
$select = $table.'.LIST_66,'.$table.'.LIST_15,'.$table.'.LIST_67,'.$table.'.LIST_69,'.$table.'.LIST_48,'.$table.'.LIST_77,'.$table.'.LIST_32,';
break;
case "land":
$table = 'flex_b';
$class = 'B';
$select = $table.'.LIST_77,';
break;
case "multiFamily":
$table = 'flex_c';
$class = 'C';
array_push($search_fields, 'LIST_32');
$select = $table.'.LIST_65,'.$table.'.LIST_77,'.$table.'.LIST_32,';
break;
case "commercial":
$table = 'flex_d';
$class = 'D';
array_push($search_fields, 'LIST_32');
$select = $table.'.LIST_48,';
break;
case "":
$table = 'flex_a,flex_b,flex_c,flex_d'.$table.'.LIST_32,';
break;
default:
$table = 'flex_a';
$class = 'A';
array_push($search_fields, 'LIST_32');
$select = $table.'.LIST_66,'.$table.'.LIST_15,'.$table.'.LIST_67,'.$table.'.LIST_69,'.$table.'.LIST_48,'.$table.'.LIST_77,'.$table.'.LIST_32,';
}
//selects
$select .= $table.'.LIST_78,'.$table.'.LIST_22,'.$table.'.LIST_31,'.$table.'.LIST_33,'.$table.'.LIST_34,'.$table.'.LIST_35,';
$select .= $table.'.LIST_36,'.$table.'.LIST_37,'.$table.'.LIST_39,'.$table.'.LIST_40,'.$table.'.LIST_41,'.$table.'.LIST_43,'.$table.'.LIST_5,'.$table.'.LIST_1,';
$select .= $table.'.LIST_105,'.$table.'.LIST_132,'.$table.'.LIST_57,'.$table.'.listing_office_name,'.$table.'.LIST_8,';
//$select .= $table.'.L_UpdateDate,';
//lat, long
$select .= $table.'.LIST_46,'.$table.'.LIST_47,';
$select .= 'flex_activeAgents.MEMBER_3,flex_activeAgents.MEMBER_4,flex_activeAgents.MEMBER_6,flex_activeAgents.MEMBER_5,flex_activeAgents.MEMBER_10,';
$select .= 'flex_activeAgents.MEMBER_1';
//set variables
$where = '';
$open_house_selected = 0;
$new_listing_selected = 0;
$price_reduced_selected = 0;
$show_open_houses_join = 0;
//deal with where clause for main search
if ($main_search != '') {
$main_search_arr = explode(',',$main_search);
$main_search_with_keys = array();
foreach ($main_search_arr as $main_search_item) {
$main_search_key1212 = explode('/',$main_search_item);
$main_search_with_keys[$main_search_key1212[0]] = $main_search_key1212[1];
}
$main_search_count = 0;
$open_house_selected = 0;
$price_reduced_selected = 0;
$new_listing_selected = 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 == 'flex_a') {
$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."`.`GF20070114231804462718000000` LIKE '".trim($residential)."%'";
} else {
$where .= " OR `".$table."`.`GF20070114231804462718000000` LIKE '".trim($residential)."%'";
}
$residential_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`GF20070114231804462718000000` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_subcategory_commercial_sale') {
if ($table == 'flex_d') {
$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."`.`GF20070102232336067481000000` LIKE '".trim($commercial)."%'";
} else {
$where .= " OR `".$table."`.`GF20070102232336067481000000` LIKE '".trim($commercial)."%'";
}
$commercial_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`GF20070102232336067481000000` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_area') {
$where .= '(';
$where .= "`".$table."`.`LIST_29` = '".trim($value)."'";
$where .= ')';
} elseif ($field == 'selected_subdivision') {
if ($table == 'flex_a' || $table == 'flex_b' || $table == 'flex_c') {
$where .= '(';
if (trim($value) == 'None') {
$where .= "`".$table."`.`LIST_77` = '' OR `".$table."`.`LIST_77` = 'none' OR `".$table."`.`LIST_77` IS NULL";
} else {
$where .= "`".$table."`.`LIST_77` = '".trim($value)."'";
}
$where .= ')';
}
} elseif ($field == 'selected_land_description') {
if ($table == 'flex_b') {
$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."`.`GF20061106160309770348000000` LIKE '%".trim($land_description)."%'";
//$new_value = trim($land_description);
} else {
$where .= " AND `".$table."`.`GF20061106160309770348000000` LIKE '%".trim($land_description)."%'";
//$new_value .= ','.trim($land_description);
}
$land_count++;
}
//$where .= "`".$table."`.`GF20061106160309770348000000` LIKE '%".trim($new_value)."%'";
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`GF20061106160309770348000000` = '".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."`.`LIST_22` >= ".$price_min." AND `".$table."`.`LIST_22` <= ".$price_max;
$where .= ')';
} elseif ($field == 'selected_bedrooms') {
if ($table == 'flex_a') {
$where .= '(';
$where .= "`".$table."`.`LIST_66` >= ".(int)$value;
$where .= ')';
}
} elseif ($field == 'selected_bathrooms') {
if ($table == 'flex_a') {
$where .= '(';
$where .= "`".$table."`.`LIST_67` >= ".(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."`.`LIST_57` >= ".$acreage_min." AND `".$table."`.`LIST_57` <= ".$acreage_max;
$where .= ')';
} elseif ($field == 'square_feet') {
if ($table != 'flex_b') {
$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."`.`LIST_48` >= ".$square_feet_min." AND `".$table."`.`LIST_48` <= ".$square_feet_max;
$where .= ')';
}
} elseif ($field == 'year_built') {
if ($table != 'flex_b') {
$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."`.`LIST_53` >= ".$year_built_min." AND `".$table."`.`LIST_53` <= ".$year_built_max;
$where .= ')';
}
} elseif ($field == 'selected_elem') {
if ($table != 'flex_d') {
$where .= '(';
$where .= "`".$table."`.`LIST_91` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'selected_middle') {
if ($table != 'flex_d') {
$where .= '(';
$where .= "`".$table."`.`LIST_92` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'selected_high') {
if ($table != 'flex_d') {
$where .= '(';
$where .= "`".$table."`.`LIST_93` = '".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."`.`LIST_57` >= ".$acres_min." AND `".$table."`.`LIST_57` <= ".$acres_max;
$where .= ')';
} elseif ($field == 'agent_selected' && isset($main_search_with_keys['coagent_selected'])) {
$where .= '(';
$where .= "`".$table."`.`LIST_5` = '".trim($value)."'
OR `".$table."`.`LIST_6` = '".trim($main_search_with_keys['coagent_selected'])."'";
$where .= ')';
}
elseif($field == 'agent_selected') {
$where .= '(';
$where .= "`".$table."`.`LIST_5` = '".trim($value)."'";
$where .= ')';
}elseif ($field == 'coagent_selected' && !isset($main_search_with_keys['agent_selected'])) {
$where .= '(';
$where .= "`".$table."`.`LIST_6` = '".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."`.`LIST_106` != ''";
} else {
$where .= "`".$table."`.`LIST_106` = ".trim($office);
}
} else {
if ($office == 'any') {
$where .= " OR `".$table."`.`LIST_106` != ''";
} else {
$where .= " OR `".$table."`.`LIST_106` = ".trim($office);
}
}
$office_count++;
}
$where .= ')';
} else { // user has selected 'any'
$where .= '(';
$where .= "`".$table."`.`LIST_106` != ''";
$where .= ')';
}
} elseif ($field == 'short_sale_selected') {
if ($table == 'flex_a') {
$where .= '(';
$where .= "`".$table."`.`GF20110914152909386107000000` = 'Short Sale'";
$where .= ')';
} elseif ($table == 'flex_b') {
$where .= '(';
$where .= "`".$table."`.`GF20110914153139512433000000` = 'Short Sale'";
$where .= ')';
} elseif ($table == 'flex_c') {
$where .= '(';
$where .= "`".$table."`.`GF20110914153305831829000000` = 'Short Sale'";
$where .= ')';
} elseif ($table == 'flex_d') {
$where .= '(';
$where .= "`".$table."`.`GF20110914153602064916000000` = 'Short Sale'";
$where .= ')';
}
/*} elseif ($field == 'foreclosure_selected') {
if ($table == 'flex_a' || $table == 'flex_b') {
$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."`.`LIST_132` <= '".$date_min."' AND `".$table."`.`LIST_132` >= '".$date_max."'";
$where .= ')';
} elseif ($field == 'farm_features_selected') {
if ($table == 'flex_a') {
$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."`.`GF20061205204632343461000000` != ''";
} else {
$where .= "`".$table."`.`GF20061205204632343461000000` LIKE '%".trim($farm_features)."%'";
}
} else {
if ($farm_features == 'any') {
$where .= " AND `".$table."`.`GF20061205204632343461000000` != ''";
} else {
$where .= " AND `".$table."`.`GF20061205204632343461000000` LIKE '%".trim($farm_features)."%'";
}
}
$farm_features_count++;
}
$where .= ')';
} else { // user has selected 'any'
$where .= '(';
$where .= "`".$table."`.`GF20061205204632343461000000` != ''";
$where .= ')';
}
} elseif ($table == 'flex_b') {
$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."`.`GF20061106160309770413000000` != ''";
} else {
$where .= "`".$table."`.`GF20061106160309770413000000` LIKE '%".trim($farm_features)."%'";
}
} else {
if ($farm_features == 'any') {
$where .= " AND `".$table."`.`GF20061106160309770413000000` != ''";
} else {
$where .= " AND `".$table."`.`GF20061106160309770413000000` LIKE '%".trim($farm_features)."%'";
}
}
$farm_features_count++;
}
$where .= ')';
} else { // user has selected 'any'
$where .= '(';
$where .= "`".$table."`.`GF20061106160309770413000000` != ''";
$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."`.`LIST_78` LIKE '%".trim($search_term)."%'";
} else {
$where .= " AND `".$table."`.`LIST_78` LIKE '%".trim($search_term)."%'";
}
} else {
if ($search_term == 'new construction' && $table == 'flex_a') {
if ($search_term_count == 0) {
$where .= "`".$table."`.`GF20061205153446881168000000` LIKE '%New Construction%'";
} else {
$where .= " AND `".$table."`.`GF20061205153446881168000000` LIKE '%New Construction%'";
}
} /*elseif ($search_term == 'hoa') {
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."`.`LIST_53` >= ".$year_built_min." AND `".$table."`.`LIST_53` <= ".$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 != '') {
// || ($field == 'farm_features_selected' && $table != 'flex_a') || ($field == 'farm_features_selected' && $table != 'flex_b')
if ($field == 'open_house_selected' || $field == 'price_reduced_selected' || $field == 'new_listing_selected' || ($field == 'farm_features_selected' && $table != 'flex_a' && $field == 'farm_features_selected' && $table != 'flex_b')) {
$where .= '';
} else {
$where .= ' AND ';
}
}
if ($field == 'selected_subcategory_residential') {
if ($table == 'flex_a') {
$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."`.`GF20070114231804462718000000` LIKE '".trim($residential)."%'";
} else {
$where .= " OR `".$table."`.`GF20070114231804462718000000` LIKE '".trim($residential)."%'";
}
$residential_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`GF20070114231804462718000000` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_subcategory_commercial_sale') {
if ($table == 'flex_d') {
$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."`.`GF20070102232336067481000000` LIKE '".trim($commercial)."%'";
} else {
$where .= " OR `".$table."`.`GF20070102232336067481000000` LIKE '".trim($commercial)."%'";
}
$commercial_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`GF20070102232336067481000000` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_area') {
$where .= '(';
$where .= "`".$table."`.`LIST_29` = '".trim($value)."'";
$where .= ')';
} elseif ($field == 'selected_subdivision') {
if ($table == 'flex_a' || $table == 'flex_b' || $table == 'flex_c') {
$where .= '(';
if (trim($value) == 'None') {
$where .= "`".$table."`.`LIST_77` = '' OR `".$table."`.`LIST_77` = 'none' OR `".$table."`.`LIST_77` IS NULL";
} else {
$where .= "`".$table."`.`LIST_77` = '".trim($value)."'";
}
$where .= ')';
}
} elseif ($field == 'selected_land_description') {
if ($table == 'flex_b') {
$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."`.`GF20061106160309770348000000` LIKE '%".trim($land_description)."%'";
} else {
$where .= " AND `".$table."`.`GF20061106160309770348000000` LIKE '%".trim($land_description)."%'";
}
$land_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`GF20061106160309770348000000` = '".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."`.`LIST_22` >= ".$price_min." AND `".$table."`.`LIST_22` <= ".$price_max;
$where .= ')';
} elseif ($field == 'selected_bedrooms') {
if ($table == 'flex_a') {
$where .= '(';
$where .= "`".$table."`.`LIST_66` >= ".(int)$value;
$where .= ')';
}
} elseif ($field == 'selected_bathrooms') {
if ($table == 'flex_a') {
$where .= '(';
$where .= "`".$table."`.`LIST_67` >= ".(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."`.`LIST_57` >= ".$acreage_min." AND `".$table."`.`LIST_57` <= ".$acreage_max;
$where .= ')';
} elseif ($field == 'square_feet') {
if ($table != 'flex_b') {
$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."`.`LIST_48` >= ".$square_feet_min." AND `".$table."`.`LIST_48` <= ".$square_feet_max;
$where .= ')';
}
} elseif ($field == 'year_built') {
if ($table != 'flex_b') {
$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."`.`LIST_53` >= ".$year_built_min." AND `".$table."`.`LIST_53` <= ".$year_built_max;
$where .= ')';
}
} elseif ($field == 'selected_elem') {
if ($table != 'flex_d') {
$where .= '(';
$where .= "`".$table."`.`LIST_91` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'selected_middle') {
if ($table != 'flex_d') {
$where .= '(';
$where .= "`".$table."`.`LIST_92` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'selected_high') {
if ($table != 'flex_d') {
$where .= '(';
$where .= "`".$table."`.`LIST_93` = '".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."`.`LIST_57` >= ".$acres_min." AND `".$table."`.`LIST_57` <= ".$acres_max;
$where .= ')';
} elseif ($field == 'agent_selected' && isset($main_search_with_keys['coagent_selected'])) {
$where .= '(';
$where .= "`".$table."`.`LIST_5` = '".trim($value)."'
OR `".$table."`.`LIST_6` = '".trim($main_search_with_keys['coagent_selected'])."'";
$where .= ')';
}
elseif($field == 'agent_selected') {
$where .= '(';
$where .= "`".$table."`.`LIST_5` = '".trim($value)."'";
$where .= ')';
}elseif ($field == 'coagent_selected' && !isset($main_search_with_keys['agent_selected'])) {
$where .= '(';
$where .= "`".$table."`.`LIST_6` = '".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."`.`LIST_106` != ''";
} else {
$where .= "`".$table."`.`LIST_106` = ".trim($office);
}
} else {
if ($office == 'any') {
$where .= " OR `".$table."`.`LIST_106` != ''";
} else {
$where .= " OR `".$table."`.`LIST_106` = ".trim($office);
}
}
$office_count++;
}
$where .= ')';
} else { // user has selected 'any'
$where .= '(';
$where .= "`".$table."`.`LIST_106` != ''";
$where .= ')';
}
} elseif ($field == 'short_sale_selected') {
if ($table == 'flex_a') {
$where .= '(';
$where .= "`".$table."`.`GF20110914152909386107000000` = 'Short Sale'";
$where .= ')';
} elseif ($table == 'flex_b') {
$where .= '(';
$where .= "`".$table."`.`GF20110914153139512433000000` = 'Short Sale'";
$where .= ')';
} elseif ($table == 'flex_c') {
$where .= '(';
$where .= "`".$table."`.`GF20110914153305831829000000` = 'Short Sale'";
$where .= ')';
} elseif ($table == 'flex_d') {
$where .= '(';
$where .= "`".$table."`.`GF20110914153602064916000000` = 'Short Sale'";
$where .= ')';
}
/*} elseif ($field == 'foreclosure_selected') {
if ($table == 'flex_a' || $table == 'flex_b') {
$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."`.`LIST_132` <= '".$date_min."' AND `".$table."`.`LIST_132` >= '".$date_max."'";
$where .= ')';
} elseif ($field == 'farm_features_selected') {
if ($table == 'flex_a') {
$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."`.`GF20061205204632343461000000` != ''";
} else {
$where .= "`".$table."`.`GF20061205204632343461000000` LIKE '%".trim($farm_features)."%'";
}
} else {
if ($farm_features == 'any') {
$where .= " AND `".$table."`.`GF20061205204632343461000000` != ''";
} else {
$where .= " AND `".$table."`.`GF20061205204632343461000000` LIKE '%".trim($farm_features)."%'";
}
}
$farm_features_count++;
}
$where .= ')';
} else { // user has selected 'any'
$where .= '(';
$where .= "`".$table."`.`GF20061205204632343461000000` != ''";
$where .= ')';
}
} elseif ($table == 'flex_b') {
$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."`.`GF20061106160309770413000000` != ''";
} else {
$where .= "`".$table."`.`GF20061106160309770413000000` LIKE '%".trim($farm_features)."%'";
}
} else {
if ($farm_features == 'any') {
$where .= " AND `".$table."`.`GF20061106160309770413000000` != ''";
} else {
$where .= " AND `".$table."`.`GF20061106160309770413000000` LIKE '%".trim($farm_features)."%'";
}
}
$farm_features_count++;
}
$where .= ')';
} else { // user has selected 'any'
$where .= '(';
$where .= "`".$table."`.`GF20061106160309770413000000` != ''";
$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."`.`LIST_78` LIKE '%".trim($search_term)."%'";
} else {
$where .= " AND `".$table."`.`LIST_78` LIKE '%".trim($search_term)."%'";
}
} else {
if ($search_term == 'new construction' && $table == 'flex_a') {
if ($search_term_count == 0) {
$where .= "`".$table."`.`GF20061205153446881168000000` LIKE '%New Construction%'";
} else {
$where .= " AND `".$table."`.`GF20061205153446881168000000` LIKE '%New Construction%'";
}
} /*elseif ($search_term == 'hoa') {
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."`.`LIST_53` >= ".$year_built_min." AND `".$table."`.`LIST_53` <= ".$max_year_built;
$where .= ')';
}
}
}
//main_search2 is not an exploded array
} else {
$search_count = 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 == 'flex_a') {
$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."`.`GF20070114231804462718000000` LIKE '".trim($residential)."%'";
} else {
$where .= " OR `".$table."`.`GF20070114231804462718000000` LIKE '".trim($residential)."%'";
}
$residential_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`GF20070114231804462718000000` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_subcategory_commercial_sale') {
if ($table == 'flex_d') {
$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."`.`GF20070102232336067481000000` LIKE '".trim($commercial)."%'";
} else {
$where .= " OR `".$table."`.`GF20070102232336067481000000` LIKE '".trim($commercial)."%'";
}
$commercial_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`GF20070102232336067481000000` = '".trim($value)."'";
$where .= ')';
}
}
} elseif ($field == 'selected_area') {
$where .= '(';
$where .= "`".$table."`.`LIST_29` = '".trim($value)."'";
$where .= ')';
} elseif ($field == 'selected_subdivision') {
if ($table == 'flex_a' || $table == 'flex_b' || $table == 'flex_c') {
$where .= '(';
if (trim($value) == 'None') {
$where .= "`".$table."`.`LIST_77` = '' OR `".$table."`.`LIST_77` = 'none' OR `".$table."`.`LIST_77` IS NULL";
} else {
$where .= "`".$table."`.`LIST_77` = '".trim($value)."'";
}
$where .= ')';
}
} elseif ($field == 'selected_land_description') {
if ($table == 'flex_b') {
$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."`.`GF20061106160309770348000000` LIKE '%".trim($land_description)."%'";
} else {
$where .= " AND `".$table."`.`GF20061106160309770348000000` LIKE '%".trim($land_description)."%'";
}
$land_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`GF20061106160309770348000000` = '".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."`.`LIST_22` >= ".$price_min." AND `".$table."`.`LIST_22` <= ".$price_max;
$where .= ')';
} elseif ($field == 'selected_bedrooms') {
if ($table == 'flex_a') {
$where .= '(';
$where .= "`".$table."`.`LIST_66` >= ".(int)$value;
$where .= ')';
}
} elseif ($field == 'selected_bathrooms') {
if ($table == 'flex_a') {
$where .= '(';
$where .= "`".$table."`.`LIST_67` >= ".(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."`.`LIST_57` >= ".$acreage_min." AND `".$table."`.`LIST_57` <= ".$acreage_max;
$where .= ')';
} elseif ($field == 'square_feet') {
if ($table != 'flex_b') {
$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."`.`LIST_48` >= ".$square_feet_min." AND `".$table."`.`LIST_48` <= ".$square_feet_max;
$where .= ')';
}
} elseif ($field == 'year_built') {
if ($table != 'flex_b') {
$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."`.`LIST_53` >= ".$year_built_min." AND `".$table."`.`LIST_53` <= ".$year_built_max;
$where .= ')';
}
} elseif ($field == 'selected_elem') {
if ($table != 'flex_d') {
$where .= '(';
$where .= "`".$table."`.`LIST_91` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'selected_middle') {
if ($table != 'flex_d') {
$where .= '(';
$where .= "`".$table."`.`LIST_92` = '".trim($value)."'";
$where .= ')';
}
} elseif ($field == 'selected_high') {
if ($table != 'flex_d') {
$where .= '(';
$where .= "`".$table."`.`LIST_93` = '".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."`.`LIST_57` >= ".$acres_min." AND `".$table."`.`LIST_57` <= ".$acres_max;
$where .= ')';
} elseif ($field == 'agent_selected' && isset($main_search_with_keys['coagent_selected'])) {
$where .= '(';
$where .= "`".$table."`.`LIST_5` = '".trim($value)."'
OR `".$table."`.`LIST_6` = '".trim($main_search_with_keys['coagent_selected'])."'";
$where .= ')';
}
elseif($field == 'agent_selected') {
$where .= '(';
$where .= "`".$table."`.`LIST_5` = '".trim($value)."'";
$where .= ')';
}elseif ($field == 'coagent_selected' && !isset($main_search_with_keys['agent_selected'])) {
$where .= '(';
$where .= "`".$table."`.`LIST_6` = '".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."`.`LIST_106` != ''";
} else {
$where .= "`".$table."`.`LIST_106` = ".trim($office);
}
} else {
if ($office == 'any') {
$where .= " OR `".$table."`.`LIST_106` != ''";
} else {
$where .= " OR `".$table."`.`LIST_106` = ".trim($office);
}
}
$office_count++;
}
$where .= ')';
} else { // user has selected 'any'
$where .= '(';
$where .= "`".$table."`.`LIST_106` != ''";
$where .= ')';
}
} elseif ($field == 'short_sale_selected') {
if ($table == 'flex_a') {
$where .= '(';
$where .= "`".$table."`.`GF20110914152909386107000000` = 'Short Sale'";
$where .= ')';
} elseif ($table == 'flex_b') {
$where .= '(';
$where .= "`".$table."`.`GF20110914153139512433000000` = 'Short Sale'";
$where .= ')';
} elseif ($table == 'flex_c') {
$where .= '(';
$where .= "`".$table."`.`GF20110914153305831829000000` = 'Short Sale'";
$where .= ')';
} elseif ($table == 'flex_d') {
$where .= '(';
$where .= "`".$table."`.`GF20110914153602064916000000` = 'Short Sale'";
$where .= ')';
}
/*} elseif ($field == 'foreclosure_selected') {
if ($table == 'flex_a' || $table == 'flex_b') {
$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."`.`LIST_132` <= '".$date_min."' AND `".$table."`.`LIST_132` >= '".$date_max."'";
$where .= ')';
} elseif ($field == 'farm_features_selected') {
if ($table == 'flex_a') {
$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."`.`GF20061205204632343461000000` != ''";
} else {
$where .= "`".$table."`.`GF20061205204632343461000000` LIKE '%".trim($farm_features)."%'";
}
} else {
if ($farm_features == 'any') {
$where .= " AND `".$table."`.`GF20061205204632343461000000` != ''";
} else {
$where .= " AND `".$table."`.`GF20061205204632343461000000` LIKE '%".trim($farm_features)."%'";
}
}
$farm_features_count++;
}
$where .= ')';
} else { // user has selected 'any'
$where .= '(';
$where .= "`".$table."`.`GF20061205204632343461000000` != ''";
$where .= ')';
}
} elseif ($table == 'flex_b') {
$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."`.`GF20061106160309770413000000` != ''";
} else {
$where .= "`".$table."`.`GF20061106160309770413000000` LIKE '%".trim($farm_features)."%'";
}
} else {
if ($farm_features == 'any') {
$where .= " AND `".$table."`.`GF20061106160309770413000000` != ''";
} else {
$where .= " AND `".$table."`.`GF20061106160309770413000000` LIKE '%".trim($farm_features)."%'";
}
}
$farm_features_count++;
}
$where .= ')';
} else { // user has selected 'any'
$where .= '(';
$where .= "`".$table."`.`GF20061106160309770413000000` != ''";
$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."`.`LIST_78` LIKE '%".trim($search_term)."%'";
} else {
$where .= " AND `".$table."`.`LIST_78` LIKE '%".trim($search_term)."%'";
}
} else {
if ($search_term == 'new construction' && $table == 'flex_a') {
if ($search_term_count == 0) {
$where .= "`".$table."`.`GF20061205153446881168000000` LIKE '%New Construction%'";
} else {
$where .= " AND `".$table."`.`GF20061205153446881168000000` LIKE '%New Construction%'";
}
} /*elseif ($search_term == 'hoa') {
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."`.`LIST_53` >= ".$year_built_min." AND `".$table."`.`LIST_53` <= ".$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 .= "(`flex_openHouse`.`EVENT200` <= '".$fourteen_days_from_now."' AND `flex_openHouse`.`EVENT200` >= '".$today."')";
$select .= ', flex_openHouse.EVENT100, flex_openHouse.EVENT200';
} 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 (`flex_openHouse`.`EVENT200` <= '".$fourteen_days_from_now."' AND `flex_openHouse`.`EVENT200` >= '".$today."')";
$select .= ', flex_openHouse.EVENT100, flex_openHouse.EVENT200';
}
}
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."`.`LIST_132` >= '".$seven_days_ago."' AND `".$table."`.`LIST_132` <= '".$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."`.`LIST_132` >= '".$seven_days_ago."' AND `".$table."`.`LIST_132` <= '".$today."')";
}
}
}
//deal with price reduced
if ($price_reduced_selected == 1) {
if ($where == '') {
$where .= "(`".$table."`.`LIST_22` < `".$table."`.`LIST_22`)";
} else {
$where .= " AND (`".$table."`.`LIST_22` < `".$table."`.`LIST_22`)";
}
}
//SET QUERY
$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('flex_openHouse', $table.'.LIST_1 = flex_openHouse.LIST1' , 'inner');
$model_join .= " inner join flex_openHouse on ". $table.'.LIST_1 = flex_openHouse.LIST1';
} elseif ($show_open_houses_join == 1) {
//$this->db->join('flex_openHouse', $table.'.LIST_1 = flex_openHouse.LIST1');
$model_join .= " inner join flex_openHouse on ". $table.'.LIST_1 = flex_openHouse.LIST1';
}
//$this->db->join('flex_activeAgents',$table.'.LIST_5 = flex_activeAgents.MEMBER_0', 'left');
$model_join .= " left join flex_activeAgents on ". $table.'.LIST_5 = flex_activeAgents.MEMBER_0';
//$this->db->join('flex_activeOffice',$table.'.LIST_106 = flex_activeOffice.OFFICE_0', 'left');
$model_join .= " left join flex_activeOffice on ". $table.'.LIST_106 = flex_activeOffice.OFFICE_0';
//WHERE
if ($where != '') {
//$this->db->where($where);
$model_where = "where ".$where;
}
//GROUP BY
if ($get_open_houses == 1) {
//$this->db->group_by($table.'.LIST_105');
$model_group_by = " GROUP BY ".$table.'.LIST_105';
}
//ORDER BY
//$this->db->order_by($table.'.LIST_22 '.$price_sort.', '.$table.'.LIST_132 '.$date_sort);
$model_order_by = " ORDER BY ".$table.'.LIST_22 '.$price_sort.', '.$table.'.LIST_132 '.$date_sort;
//LIMIT
//$this->db->limit($limit, $start);
$model_limit = " limit $start , $limit ";
//RUN QUERY
//$query = $this->db->get();
/* code below modified by vikas to get img url in select itself to reduce multiple calls to get image in for loop*/
$select.=',flex_imgsDocs.url, openhouse_timings ';
$model_join.=' left join flex_imgsDocs using(LIST_105) ';
if($model_where==''){
$model_where=' where 1=1 ';
}
$model_where.=' and flex_imgsDocs.type=\'image\' and flex_imgsDocs.sort_order=1';
//echo $farms_only.'--- added farms_only filter by vikas.';
if(isset($new_filters['farms_only']) && $new_filters['farms_only']==1)
{
$model_where.=" and ".$table.".LIST_9='farm'";
}
//echo "select $select from $table $model_join $model_where $model_group_by $model_order_by $model_limit",ARRAY_A;
$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 address
$address = '';
if ($listings[$element]['LIST_31'] != '') {
$address .= $listings[$element]['LIST_31'];
}
if ($address != '' && $listings[$element]['LIST_33'] != '') {
$address .= ' '.$listings[$element]['LIST_33'];
} elseif ($address == '' && $listings[$element]['LIST_33'] != '') {
$address .= $listings[$element]['LIST_33'];
}
if ($address != '' && $listings[$element]['LIST_34'] != '') {
$address .= ' '.$listings[$element]['LIST_34'];
} elseif ($address == '' && $listings[$element]['LIST_34'] != '') {
$address .= $listings[$element]['LIST_34'];
}
if ($address != '' && $listings[$element]['LIST_35'] != '') {
$address .= ' '.$listings[$element]['LIST_35'];
} elseif ($address == '' && $listings[$element]['LIST_35'] != '') {
$address .= $listings[$element]['LIST_35'];
}
if ($address != '' && $listings[$element]['LIST_36'] != '') {
$address .= ' '.$listings[$element]['LIST_36'];
} elseif ($address == '' && $listings[$element]['LIST_36'] != '') {
$address .= $listings[$element]['LIST_36'];
}
if ($address != '' && $listings[$element]['LIST_37'] != '') {
$address .= ' '.$listings[$element]['LIST_37'];
} elseif ($address == '' && $listings[$element]['LIST_37'] != '') {
$address .= $listings[$element]['LIST_37'];
}
if ($address != '' && $listings[$element]['LIST_39'] != '') {
$address .= ', '.$listings[$element]['LIST_39'];
} elseif ($address == '' && $listings[$element]['LIST_39'] != '') {
$address .= $listings[$element]['LIST_39'];
}
if ($address != '' && $listings[$element]['LIST_40'] != '') {
$address .= ' '.$listings[$element]['LIST_40'];
} elseif ($address == '' && $listings[$element]['LIST_40'] != '') {
$address .= $listings[$element]['LIST_40'];
}
if ($address != '' && $listings[$element]['LIST_43'] != '') {
$address .= ' '.$listings[$element]['LIST_43'];
} elseif ($address == '' && $listings[$element]['LIST_43'] != '') {
$address .= $listings[$element]['LIST_43'];
}
$listings[$element]['address'] = $address;
//set MLS ID
$mls_id = $listings[$element]['LIST_1'];
//set property_type
$listings[$element]['property_type'] = $property_type;
//set new listing
$listings[$element]['new_listing'] = 'No';
$input_date = str_replace("T", " ", $listings[$element]['LIST_132']);
$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]['LIST_22'] < $listings[$element]['LIST_22']) {
$listings[$element]['price_reduced'] = 'Yes';
}
//set open house
$openhouses = array();
//$this->db->select('EVENT100,EVENT200,LIST105');
//$this->db->from('flex_openHouse');
//$this->db->where('LIST1',$mls_id);
//$this->db->order_by('EVENT100', 'asc');
//$query = $this->db->get();
/* 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);
}
/* commented intentionality by vikas vk, as its slowing down too. */
if(!$disableUnusedCode)
{
$results = $this->databaseConnection->get_results("select EVENT100,EVENT200,LIST105 from flex_openHouse where LIST1 = $mls_id ORDER BY EVENT100 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]['EVENT100']);
$start_date_unix = strtotime($start_date);
$start_time = date('g:i a', $start_date_unix);
$end_date = str_replace("T", " ", $openhouses[$openhouse]['EVENT200']);
$end_date_unix = strtotime($end_date);
$end_time = date('g:i a', $end_date_unix);
$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..
$listings[$element]['image'] = '';
$images_array = array();
$mls_id_new = $listings[$element]['LIST_105'];
$images_where = array('LIST_105' => $mls_id_new, 'type' => 'image', 'sort_order' => 1);
//$this->db->select('url');
//$this->db->from('flex_imgsDocs');
//$this->db->where($images_where);
//$query = $this->db->get();
$results = $this->databaseConnection->get_row("select url from flex_imgsDocs where (LIST_105 = $mls_id_new) 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.
*/
}
}
//return the result
return $listings;
}
//get flex properties
public function getFlexProperties($limit='',$office_id='',$property_type='',$date_sort='',$price_sort=''){
//make some property type based decisions
switch ($property_type) {
case "residential":
$table = 'flex_a';
$class = 'A';
$select = $table.'.LIST_66,'.$table.'.LIST_67,'.$table.'.LIST_69,'.$table.'.LIST_48,'.$table.'.LIST_77,'.$table.'.LIST_32,'.$table.'.LIST_106,';
break;
case "land":
$table = 'flex_b';
$class = 'B';
$select = $table.'.LIST_77,'.$table.'.LIST_106,';
break;
case "multiFamily":
$table = 'flex_c';
$class = 'C';
$select = $table.'.LIST_65,'.$table.'.LIST_77,'.$table.'.LIST_32,'.$table.'.LIST_106,';
break;
case "commercial":
$table = 'flex_d';
$class = 'D';
$select = $table.'.LIST_48,'.$table.'.LIST_106,';
break;
case "":
$table = 'flex_a,flex_b,flex_c,flex_d'.$table.'.LIST_32,'.$table.'.LIST_106,';
break;
default:
$table = 'flex_a';
$class = 'A';
$select = $table.'.LIST_66,'.$table.'.LIST_67,'.$table.'.LIST_69,'.$table.'.LIST_48,'.$table.'.LIST_77,'.$table.'.LIST_32,'.$table.'.LIST_106,';
}
//selects
$select .= $table.'.LIST_78,'.$table.'.LIST_22,'.$table.'.LIST_31,'.$table.'.LIST_33,'.$table.'.LIST_34,'.$table.'.LIST_35,';
$select .= $table.'.LIST_36,'.$table.'.LIST_37,'.$table.'.LIST_39,'.$table.'.LIST_40,'.$table.'.LIST_41,'.$table.'.LIST_43,'.$table.'.LIST_5,'.$table.'.LIST_1,';
$select .= $table.'.LIST_105,'.$table.'.LIST_132,'.$table.'.LIST_57,'.$table.'.listing_office_name,'.$table.'.LIST_8,';
$select .= $table.'.LIST_46,'.$table.'.LIST_47,';
$select .= 'flex_activeAgents.MEMBER_3,flex_activeAgents.MEMBER_4,flex_activeAgents.MEMBER_6,flex_activeAgents.MEMBER_5,flex_activeAgents.MEMBER_10,';
$select .= 'flex_activeAgents.MEMBER_1';
//set variables
$where = '';
if(!empty($office_id)){
$where .= "(`".$table."`.`LIST_106` = ".trim($office_id).")";
}
//echo $where;
//LIMIT
$start = 0;
$model_join .= " left join flex_activeAgents on ". $table.'.LIST_5 = flex_activeAgents.MEMBER_0';
$model_join .= " left join flex_activeOffice on ". $table.'.LIST_106 = flex_activeOffice.OFFICE_0';
if ($where != '') {
$model_where = "where ".$where;
}
if(!empty($price_sort)){
$model_order_by = " ORDER BY ".$table.'.LIST_22 '.$price_sort;
}else{
$model_order_by = " ORDER BY ".$table.'.LIST_22 desc';
}
$model_group_by = " GROUP BY ".$table.'.LIST_105';
$model_limit = " limit $start , $limit ";
//echo "
test-- select $select from $table $model_join $model_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 address
$address = '';
if ($listings[$element]['LIST_31'] != '') {
$address .= $listings[$element]['LIST_31'];
}
if ($address != '' && $listings[$element]['LIST_33'] != '') {
$address .= ' '.$listings[$element]['LIST_33'];
} elseif ($address == '' && $listings[$element]['LIST_33'] != '') {
$address .= $listings[$element]['LIST_33'];
}
if ($address != '' && $listings[$element]['LIST_34'] != '') {
$address .= ' '.$listings[$element]['LIST_34'];
} elseif ($address == '' && $listings[$element]['LIST_34'] != '') {
$address .= $listings[$element]['LIST_34'];
}
if ($address != '' && $listings[$element]['LIST_35'] != '') {
$address .= ' '.$listings[$element]['LIST_35'];
} elseif ($address == '' && $listings[$element]['LIST_35'] != '') {
$address .= $listings[$element]['LIST_35'];
}
if ($address != '' && $listings[$element]['LIST_36'] != '') {
$address .= ' '.$listings[$element]['LIST_36'];
} elseif ($address == '' && $listings[$element]['LIST_36'] != '') {
$address .= $listings[$element]['LIST_36'];
}
if ($address != '' && $listings[$element]['LIST_37'] != '') {
$address .= ' '.$listings[$element]['LIST_37'];
} elseif ($address == '' && $listings[$element]['LIST_37'] != '') {
$address .= $listings[$element]['LIST_37'];
}
if ($address != '' && $listings[$element]['LIST_39'] != '') {
$address .= ', '.$listings[$element]['LIST_39'];
} elseif ($address == '' && $listings[$element]['LIST_39'] != '') {
$address .= $listings[$element]['LIST_39'];
}
if ($address != '' && $listings[$element]['LIST_40'] != '') {
$address .= ' '.$listings[$element]['LIST_40'];
} elseif ($address == '' && $listings[$element]['LIST_40'] != '') {
$address .= $listings[$element]['LIST_40'];
}
if ($address != '' && $listings[$element]['LIST_43'] != '') {
$address .= ' '.$listings[$element]['LIST_43'];
} elseif ($address == '' && $listings[$element]['LIST_43'] != '') {
$address .= $listings[$element]['LIST_43'];
}
$listings[$element]['address'] = $address;
//set MLS ID
$mls_id = $listings[$element]['LIST_1'];
//set property_type
$listings[$element]['property_type'] = $property_type;
//set new listing
$listings[$element]['new_listing'] = 'No';
$input_date = str_replace("T", " ", $listings[$element]['LIST_132']);
$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]['LIST_22'] < $listings[$element]['LIST_22']) {
$listings[$element]['price_reduced'] = 'Yes';
}
//set open house
$openhouses = array();
$results = $this->databaseConnection->get_results("select EVENT100,EVENT200,LIST105 from flex_openHouse where LIST1 = $mls_id ORDER BY EVENT100 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]['EVENT100']);
$start_date_unix = strtotime($start_date);
$start_time = date('g:i a', $start_date_unix);
$end_date = str_replace("T", " ", $openhouses[$openhouse]['EVENT200']);
$end_date_unix = strtotime($end_date);
$end_time = date('g:i a', $end_date_unix);
$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'] = '';
$images_array = array();
$mls_id_new = $listings[$element]['LIST_105'];
$images_where = array('LIST_105' => $mls_id_new, 'type' => 'image', 'sort_order' => 1);
$results = $this->databaseConnection->get_row("select url from flex_imgsDocs where (LIST_105 = $mls_id_new) 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;
}
public function getFlexPropertiesWithoutOfficeId($limit='',$office_id='',$property_type='',$price_sort='',$areaArray='',$price=''){
//make some property type based decisions
switch ($property_type) {
case "residential":
$table = 'flex_a';
$class = 'A';
$select = $table.'.LIST_66,'.$table.'.LIST_67,'.$table.'.LIST_69,'.$table.'.LIST_48,'.$table.'.LIST_77,'.$table.'.LIST_32,'.$table.'.LIST_106,';
break;
case "land":
$table = 'flex_b';
$class = 'B';
$select = $table.'.LIST_77,'.$table.'.LIST_106,';
break;
case "multiFamily":
$table = 'flex_c';
$class = 'C';
$select = $table.'.LIST_65,'.$table.'.LIST_77,'.$table.'.LIST_32,'.$table.'.LIST_106,';
break;
case "commercial":
$table = 'flex_d';
$class = 'D';
$select = $table.'.LIST_48,'.$table.'.LIST_106,';
break;
case "":
$table = 'flex_a,flex_b,flex_c,flex_d'.$table.'.LIST_32,'.$table.'.LIST_106,';
break;
default:
$table = 'flex_a';
$class = 'A';
$select = $table.'.LIST_66,'.$table.'.LIST_67,'.$table.'.LIST_69,'.$table.'.LIST_48,'.$table.'.LIST_77,'.$table.'.LIST_32,'.$table.'.LIST_106,';
}
//selects
$select .= $table.'.LIST_78,'.$table.'.LIST_22,'.$table.'.LIST_31,'.$table.'.LIST_33,'.$table.'.LIST_34,'.$table.'.LIST_35,';
$select .= $table.'.LIST_36,'.$table.'.LIST_37,'.$table.'.LIST_39,'.$table.'.LIST_40,'.$table.'.LIST_41,'.$table.'.LIST_43,'.$table.'.LIST_5,'.$table.'.LIST_1,';
$select .= $table.'.LIST_105,'.$table.'.LIST_132,'.$table.'.LIST_57,'.$table.'.listing_office_name,'.$table.'.LIST_8,';
$select .= $table.'.LIST_46,'.$table.'.LIST_47,';
$select .= 'flex_activeAgents.MEMBER_3,flex_activeAgents.MEMBER_4,flex_activeAgents.MEMBER_6,flex_activeAgents.MEMBER_5,flex_activeAgents.MEMBER_10,';
$select .= 'flex_activeAgents.MEMBER_1';
//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."`.`LIST_29` = '".trim($area)."'";
} else {
$where .= " OR `".$table."`.`LIST_29` = '".trim($area)."'";
}
$area_count++;
}
$where .= ')';
} else {
$where .= '(';
$where .= "`".$table."`.`LIST_29` = '".trim($areaArray)."'";
$where .= ')';
}
}
if(!empty($price)){
$price_array = explode('_',$price);
$price_min = $price_array[0];
$price_max = $price_array[1];
if($where == ''){
$where .= "(`".$table."`.`LIST_22` >= ".$price_min." AND `".$table."`.`LIST_22` <= ".$price_max.")";
}else{
$where .= " AND (`".$table."`.`LIST_22` >= ".$price_min." AND `".$table."`.`LIST_22` <= ".$price_max.")";
}
}
if(!empty($office_id)){
if($where == ''){
$where .= " NOT (`".$table."`.`LIST_106` = ".trim($office_id).")";
}else{
$where .= " AND NOT (`".$table."`.`LIST_106` = ".trim($office_id).")";
}
}
//LIMIT
$start = 0;
$model_join .= " left join flex_activeAgents on ". $table.'.LIST_5 = flex_activeAgents.MEMBER_0';
$model_join .= " left join flex_activeOffice on ". $table.'.LIST_106 = flex_activeOffice.OFFICE_0';
if ($where != '') {
$model_where = "where ".$where;
}
if(!empty($price_sort)){
$model_order_by = " ORDER BY ".$table.'.LIST_22 '.$price_sort;
}else{
$model_order_by = " ORDER BY ".$table.'.LIST_22 desc';
}
$model_group_by = " GROUP BY ".$table.'.LIST_105';
$model_limit = " limit $start , $limit ";
//echo "
test-- SELECT * FROM (SELECT $select FROM $table $model_join $model_where $model_group_by ORDER BY rand() $model_limit) T1 ORDER BY LIST_22 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 LIST_22 desc $model_limit",ARRAY_A);
if(count($results)){
$listings = $results;
}
if (count($listings) > 0) {
foreach ($listings as $element => $arr) {
//set address
$address = '';
if ($listings[$element]['LIST_31'] != '') {
$address .= $listings[$element]['LIST_31'];
}
if ($address != '' && $listings[$element]['LIST_33'] != '') {
$address .= ' '.$listings[$element]['LIST_33'];
} elseif ($address == '' && $listings[$element]['LIST_33'] != '') {
$address .= $listings[$element]['LIST_33'];
}
if ($address != '' && $listings[$element]['LIST_34'] != '') {
$address .= ' '.$listings[$element]['LIST_34'];
} elseif ($address == '' && $listings[$element]['LIST_34'] != '') {
$address .= $listings[$element]['LIST_34'];
}
if ($address != '' && $listings[$element]['LIST_35'] != '') {
$address .= ' '.$listings[$element]['LIST_35'];
} elseif ($address == '' && $listings[$element]['LIST_35'] != '') {
$address .= $listings[$element]['LIST_35'];
}
if ($address != '' && $listings[$element]['LIST_36'] != '') {
$address .= ' '.$listings[$element]['LIST_36'];
} elseif ($address == '' && $listings[$element]['LIST_36'] != '') {
$address .= $listings[$element]['LIST_36'];
}
if ($address != '' && $listings[$element]['LIST_37'] != '') {
$address .= ' '.$listings[$element]['LIST_37'];
} elseif ($address == '' && $listings[$element]['LIST_37'] != '') {
$address .= $listings[$element]['LIST_37'];
}
if ($address != '' && $listings[$element]['LIST_39'] != '') {
$address .= ', '.$listings[$element]['LIST_39'];
} elseif ($address == '' && $listings[$element]['LIST_39'] != '') {
$address .= $listings[$element]['LIST_39'];
}
if ($address != '' && $listings[$element]['LIST_40'] != '') {
$address .= ' '.$listings[$element]['LIST_40'];
} elseif ($address == '' && $listings[$element]['LIST_40'] != '') {
$address .= $listings[$element]['LIST_40'];
}
if ($address != '' && $listings[$element]['LIST_43'] != '') {
$address .= ' '.$listings[$element]['LIST_43'];
} elseif ($address == '' && $listings[$element]['LIST_43'] != '') {
$address .= $listings[$element]['LIST_43'];
}
$listings[$element]['address'] = $address;
//set MLS ID
$mls_id = $listings[$element]['LIST_1'];
//set property_type
$listings[$element]['property_type'] = $property_type;
//set new listing
$listings[$element]['new_listing'] = 'No';
$input_date = str_replace("T", " ", $listings[$element]['LIST_132']);
$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]['LIST_22'] < $listings[$element]['LIST_22']) {
$listings[$element]['price_reduced'] = 'Yes';
}
//set open house
$openhouses = array();
$results = $this->databaseConnection->get_results("select EVENT100,EVENT200,LIST105 from flex_openHouse where LIST1 = $mls_id ORDER BY EVENT100 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]['EVENT100']);
$start_date_unix = strtotime($start_date);
$start_time = date('g:i a', $start_date_unix);
$end_date = str_replace("T", " ", $openhouses[$openhouse]['EVENT200']);
$end_date_unix = strtotime($end_date);
$end_time = date('g:i a', $end_date_unix);
$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'] = '';
$images_array = array();
$mls_id_new = $listings[$element]['LIST_105'];
$images_where = array('LIST_105' => $mls_id_new, 'type' => 'image', 'sort_order' => 1);
$results = $this->databaseConnection->get_row("select url from flex_imgsDocs where (LIST_105 = $mls_id_new) 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;
}
}