public function getResults()
{
/*
* Possible search types from HOME/SEARCH:
* 0 by locality (locality provided)
* 1 by proximity (lat, lng calculated from provided address)
*
* possible operations from HOME/SEARCH:
* 0 Buy
* 1 Rent
* 2 Share
*
* possible typologies from HOME/SEARCH:
* 0 New development
* 1 Houses + country houses + apartments
* 2 Vacation
* 3 Room
* 4 Office
* 5 Business
* 6 Garage
* 7 Land
*/
$input = \Input::all();
// Input validation
if ($input['operation'] == '0') {
$allowed_price_values = '1000000,2000000,3000000,950000,900000,850000,750000,700000,650000,600000,550000,' . '500000,450000,400000,380000,360000,340000,320000,300000,280000,260000,240000,220000,200000,180000,' . '160000,140000,120000,100000,80000,60000';
} else {
$allowed_price_values = '3000,2700,2400,2100,2000,1900,1800,1700,1600,1500,1400,1300,1200,1100,1000,900,' . '800,700,600,500,400,300,200,100';
}
$rules = array('operation' => 'required|digits:1|in:0,1,2', 'typology' => 'required|digits:1', 'search_type' => 'required|digits:1|in:0,1', 'locality' => 'sometimes|string|max:255', 'address' => 'sometimes|string|max:510', 'price-min' => 'sometimes|string|in:' . $allowed_price_values, 'price-max' => 'sometimes|string|in:' . $allowed_price_values);
$validator = \Validator::make($input, $rules);
if ($validator->fails()) {
return \Redirect::route('home');
}
// Set default values
if (!isset($input['operation'])) {
$input['operation'] = '0';
}
if (!isset($input['typology'])) {
$input['typology'] = '1';
}
if (!isset($input['locality'])) {
$input['locality'] = 'Barcelona';
}
if (!isset($input['search_type'])) {
$input['search_type'] = '0';
}
if (!isset($input['address']) || $input['address'] == '') {
$input['address'] = $input['locality'];
}
if (!isset($input['price-min']) || $input['price-min'] == '') {
$input['price-min'] = 0;
}
if (!isset($input['price-max']) || $input['price-max'] == '') {
$input['price-max'] = 0;
}
// Set minimum and maximum prices when no price range provided
$price_min = $input['price-min'] ? (int) $input['price-min'] : -1;
$price_max = $input['price-max'] ? (int) $input['price-max'] : 999999999;
// Share a house = Rent a room
if ($input['operation'] == '2' && $input['typology'] == '1') {
$input['operation'] = '1';
$input['typology'] = '3';
}
// Search queries
if ($input['search_type'] == '0') {
$locality = $input['locality'];
switch ($input['operation']) {
case '0':
//buy
switch ($input['typology']) {
case '0':
//new development
$ads = \DB::select(\DB::raw("\n SELECT rooms,floor,locality,route,street_number,price,has_parking_space,has_elevator,description,area,hide_address,`name` as type,ad_id FROM (\n SELECT t1.n_bedrooms as rooms,'0' as floor,t1.locality,t1.route,t1.street_number,t1.price,t1.has_parking_space,'0' as has_elevator,t1.description,t1.area_constructed as area,t1.hide_address,t5.name,t1.ad_id\n FROM sell_house AS t1\n LEFT JOIN category_house AS t5 ON t1.category_house_id = t5.id\n WHERE t1.is_new_development = 1 AND t1.locality = ? AND t1.price >= ? AND t1.price <= ?\n UNION\n SELECT t2.n_bedrooms as rooms,'0' as floor,t2.locality,t2.route,t2.street_number,t2.price,t2.has_parking_space,'0' as has_elevator,t2.description,t2.area_constructed as area,t2.hide_address,t6.name,t2.ad_id\n FROM sell_country_house AS t2\n LEFT JOIN category_country_house AS t6 ON t2.category_country_house_id = t6.id\n WHERE t2.is_new_development = 1 AND t2.locality = ? AND t2.price >= ? AND t2.price <= ?\n UNION\n SELECT t3.n_bedrooms as rooms,t3.floor_number as floor,t3.locality,t3.route,t3.street_number,t3.price,t3.has_elevator,t3.has_parking_space,t3.description,t3.area_constructed as area,t3.hide_address,\n IF(is_duplex = 1, 'Dúplex', IF(is_penthouse = 1, 'Ático', IF(is_studio = 1, 'Estudio', 'Piso'))) as `name`,t3.ad_id\n FROM sell_apartment AS t3\n WHERE t3.is_new_development = 1 AND t3.locality = ? AND t3.price >= ? AND t3.price <= ?\n ) AS t4;\n "), [$input['locality'], $price_min, $price_max, $input['locality'], $price_min, $price_max, $input['locality'], $price_min, $price_max]);
break;
case '1':
//house + country house + apartment
$ads = \DB::select(\DB::raw("\n SELECT rooms,floor,locality,route,street_number,price,has_parking_space,has_elevator,description,area,hide_address,`name` as type,ad_id FROM (\n SELECT t1.n_bedrooms as rooms,'0' as floor,t1.locality,t1.route,t1.street_number,t1.price,t1.has_parking_space,'0' as has_elevator,t1.description,t1.area_constructed as area,t1.hide_address,t5.name,t1.ad_id\n FROM sell_house AS t1\n LEFT JOIN category_house AS t5 ON t1.category_house_id = t5.id\n WHERE t1.locality = ? AND t1.price >= ? AND t1.price <= ?\n UNION\n SELECT t2.n_bedrooms as rooms,'0' as floor,t2.locality,t2.route,t2.street_number,t2.price,t2.has_parking_space,'0' as has_elevator,t2.description,t2.area_constructed as area,t2.hide_address,t6.name,t2.ad_id\n FROM sell_country_house AS t2\n LEFT JOIN category_country_house AS t6 ON t2.category_country_house_id = t6.id\n WHERE t2.locality = ? AND t2.price >= ? AND t2.price <= ?\n UNION\n SELECT t3.n_bedrooms as rooms,t3.floor_number as floor,t3.locality,t3.route,t3.street_number,t3.price,t3.has_parking_space,t3.has_elevator,t3.description,t3.area_constructed as area,t3.hide_address,\n IF(is_duplex = 1, 'Dúplex', IF(is_penthouse = 1, 'Ático', IF(is_studio = 1, 'Estudio', 'Piso'))) as `name`,t3.ad_id\n FROM sell_apartment AS t3\n WHERE t3.locality = ? AND t3.price >= ? AND t3.price <= ?\n ) AS t4;\n "), [$input['locality'], $price_min, $price_max, $input['locality'], $price_min, $price_max, $input['locality'], $price_min, $price_max]);
break;
case '4':
//office
$ads = \DB::select(\DB::raw("\n SELECT floor_number as floor,locality,route,street_number,price,description,'Oficina' as type,area_constructed as area,hide_address,ad_id\n FROM sell_office AS t1\n WHERE t1.locality = ? AND t1.price >= ? AND t1.price <= ?;\n "), [$input['locality'], $price_min, $price_max]);
break;
case '5':
//business
$ads = \DB::select(\DB::raw("\n SELECT floor_number as floor,locality,route,street_number,price,description,'Garaje' as type,area_constructed as area,hide_address,ad_id\n FROM sell_business AS t1\n WHERE t1.locality = ? AND t1.price >= ? AND t1.price <= ?;\n "), [$input['locality'], $price_min, $price_max]);
break;
case '6':
//garage
$ads = \DB::select(\DB::raw("\n SELECT locality,route,street_number,price,description,'Garaje' as type,`name` as garage_capacity,hide_address,ad_id\n FROM sell_garage AS t1\n LEFT JOIN garage_capacity AS t2 ON t1.garage_capacity_id = t2.id\n WHERE t1.locality = ? AND t1.price >= ? AND t1.price <= ?;\n "), [$input['locality'], $price_min, $price_max]);
break;
case '7':
//land
$ads = \DB::select(\DB::raw("\n SELECT locality,route,street_number,price,description,area_total as area,'Terreno' as type,`name` as land_category,hide_address,ad_id\n FROM sell_land AS t1\n LEFT JOIN category_land AS t2 ON t1.category_land_id = t2.id\n WHERE t1.locality = ? AND t1.price >= ? AND t1.price <= ?;\n "), [$input['locality'], $price_min, $price_max]);
break;
}
break;
case '1':
//rent
switch ($input['typology']) {
case '0':
//new development
$ads = \DB::select(\DB::raw("\n SELECT rooms,floor,locality,route,street_number,price,has_parking_space,has_elevator,description,area,hide_address,`name` as type,ad_id FROM (\n SELECT t1.n_bedrooms as rooms,'0' as floor,t1.locality,t1.route,t1.street_number,t1.price,t1.has_parking_space,'0' as has_elevator,t1.description,t1.area_constructed as area,t1.hide_address,t5.name,t1.ad_id\n FROM rent_house AS t1\n LEFT JOIN category_house AS t5 ON t1.category_house_id = t5.id\n WHERE t1.is_new_development = 1 AND t1.locality = ? AND t1.price >= ? AND t1.price <= ?\n UNION\n SELECT t2.n_bedrooms as rooms,'0' as floor,t2.locality,t2.route,t2.street_number,t2.price,t2.has_parking_space,'0' as has_elevator,t2.description,t2.area_constructed as area,t2.hide_address,t6.name,t2.ad_id\n FROM rent_country_house AS t2\n LEFT JOIN category_country_house AS t6 ON t2.category_country_house_id = t6.id\n WHERE t2.is_new_development = 1 AND t2.locality = ? AND t2.price >= ? AND t2.price <= ?\n UNION\n SELECT t3.n_bedrooms as rooms,t3.floor_number as floor,t3.locality,t3.route,t3.street_number,t3.price,t3.has_parking_space,t3.has_elevator,t3.description,t3.area_constructed as area,t3.hide_address,\n IF(is_duplex = 1, 'Dúplex', IF(is_penthouse = 1, 'Ático', IF(is_studio = 1, 'Estudio', 'Piso'))) as `name`,t3.ad_id\n FROM rent_apartment AS t3\n WHERE t3.is_new_development = 1 AND t3.locality = ? AND t3.price >= ? AND t3.price <= ?\n ) AS t4;\n "), [$input['locality'], $price_min, $price_max, $input['locality'], $price_min, $price_max, $input['locality'], $price_min, $price_max]);
break;
case '1':
//house + country house + apartment
$ads = \DB::select(\DB::raw("\n SELECT rooms,floor,locality,route,street_number,price,has_parking_space,has_elevator,description,area,hide_address,`name` as type,ad_id FROM (\n SELECT t1.n_bedrooms as rooms,'0' as floor,t1.locality,t1.route,t1.street_number,t1.price,t1.has_parking_space,'0' as has_elevator,t1.description,t1.area_constructed as area,t1.hide_address,t5.name,t1.ad_id\n FROM rent_house AS t1\n LEFT JOIN category_house AS t5 ON t1.category_house_id = t5.id\n WHERE t1.locality = ? AND t1.price >= ? AND t1.price <= ?\n UNION\n SELECT t2.n_bedrooms as rooms,'0' as floor,t2.locality,t2.route,t2.street_number,t2.price,t2.has_parking_space,'0' as has_elevator,t2.description,t2.area_constructed as area,t2.hide_address,t6.name,t2.ad_id\n FROM rent_country_house AS t2\n LEFT JOIN category_country_house AS t6 ON t2.category_country_house_id = t6.id\n WHERE t2.locality = ? AND t2.price >= ? AND t2.price <= ?\n UNION\n SELECT t3.n_bedrooms as rooms,t3.floor_number as floor,t3.locality,t3.route,t3.street_number,t3.price,t3.has_parking_space,t3.has_elevator,t3.description,t3.area_constructed as area,t3.hide_address,\n IF(is_duplex = 1, 'Dúplex', IF(is_penthouse = 1, 'Ático', IF(is_studio = 1, 'Estudio', 'Piso'))) as `name`,t3.ad_id\n FROM rent_apartment AS t3\n WHERE t3.locality = ? AND t3.price >= ? AND t3.price <= ?\n ) AS t4;\n "), [$input['locality'], $price_min, $price_max, $input['locality'], $price_min, $price_max, $input['locality'], $price_min, $price_max]);
break;
case '2':
//vacation/lodge
$ads = \DB::select(\DB::raw("\n SELECT floor_number as floor,locality,route,street_number,description,t2.`name` as type,t4.`name` as surroundings,area_total as area,min_capacity,max_capacity,hide_address,MIN(p_one_month) as min_price_per_night,ad_id\n FROM rent_vacation AS t1\n LEFT JOIN category_lodging AS t2 ON t1.category_lodging_id = t2.id\n LEFT JOIN vacation_season_price AS t3 ON t1.id = t3.rent_vacation_id\n LEFT JOIN surroundings AS t4 ON t1.surroundings_id = t4.id\n WHERE t1.locality = ?\n HAVING min_price_per_night >= ? AND min_price_per_night <= ?;\n "), [$input['locality'], $price_min, $price_max]);
break;
case '3':
//room
$ads = \DB::select(\DB::raw("\n SELECT floor_number as floor,locality,route,street_number,price,description,'Habitación' as type,area_room as area,hide_address,ad_id\n FROM rent_room AS t1\n WHERE t1.locality = ? AND t1.price >= ? AND t1.price <= ?;\n "), [$input['locality'], $price_min, $price_max]);
break;
case '4':
//office
$ads = \DB::select(\DB::raw("\n SELECT floor_number as floor,locality,route,street_number,price,description,'Oficina' as type,area_constructed as area,hide_address,ad_id\n FROM rent_office AS t1\n WHERE t1.locality = ? AND t1.price >= ? AND t1.price <= ?;\n "), [$input['locality'], $price_min, $price_max]);
break;
case '5':
//business
$ads = \DB::select(\DB::raw("\n SELECT floor_number as floor,locality,route,street_number,price,description,`name` as type,area_constructed as area,hide_address,ad_id\n FROM rent_business AS t1\n LEFT JOIN category_business AS t2 ON t1.category_business_id = t2.id\n WHERE t1.locality = ? AND t1.price >= ? AND t1.price <= ?;\n "), [$input['locality'], $price_min, $price_max]);
break;
case '6':
//garage
$ads = \DB::select(\DB::raw("\n SELECT locality,route,street_number,price,description,'Garaje' as type,`name` as garage_capacity,hide_address,ad_id\n FROM rent_garage AS t1\n LEFT JOIN garage_capacity AS t2 ON t1.garage_capacity_id = t2.id\n WHERE t1.locality = ? AND t1.price >= ? AND t1.price <= ?;\n "), [$input['locality'], $price_min, $price_max]);
break;
case '7':
//land
$ads = \DB::select(\DB::raw("\n SELECT locality,route,street_number,price,description,area_total as area,'Terreno' as type,`name` as land_category,hide_address,ad_id\n FROM rent_land AS t1\n LEFT JOIN category_land AS t2 ON t1.category_land_id = t2.id\n WHERE t1.locality = ? AND t1.price >= ? AND t1.price <= ?;\n "), [$input['locality'], $price_min, $price_max]);
break;
}
break;
}
} else {
// Geo-located search queries
// Geo-locate address
$location = Geocode::geocodeAddress($input['address']);
if (!$location) {
return \Redirect::back();
}
$locality = $location['locality'];
// Geo-distance calculations
$R = 6371.01;
//radio de la tierra promedio (en km)
$distance = \App\Constants::first()->search_distance;
$r = $distance / $R;
//ángulo en radianes que equivale a recorrer $distance sobre un círculo de radio $R
$lat_r = deg2rad($location['lat']);
//en rads
$lng_r = deg2rad($location['lng']);
//en rads
$min_lat = rad2deg($lat_r - $r);
//en sexag
$max_lat = rad2deg($lat_r + $r);
//en sexag
$delta_lng = asin(sin($r) / cos($lat_r));
//en rads
$min_lng = rad2deg($lng_r - $delta_lng);
//en sexag
$max_lng = rad2deg($lng_r + $delta_lng);
//en sexag
// DB queries
switch ($input['operation']) {
case '0':
//buy
switch ($input['typology']) {
case '0':
//new development
$ads = \DB::select(\DB::raw("\n SELECT rooms,floor,locality,route,street_number,price,has_parking_space,description,area,hide_address,`name` as type,ad_id,distance FROM (\n SELECT t1.n_bedrooms as rooms,'0' as floor,t1.locality,t1.route,t1.street_number,t1.price,t1.has_parking_space,t1.description,t1.area_constructed as area,t1.hide_address,t5.name,t1.ad_id,\n ({$R}*ACOS(SIN({$lat_r})*SIN(RADIANS(t1.lat))+COS({$lat_r})*COS(RADIANS(t1.lat))*COS(RADIANS(t1.lng)-{$lng_r}))) AS distance\n FROM sell_house AS t1\n LEFT JOIN category_house AS t5 ON t1.category_house_id = t5.id\n WHERE t1.is_new_development = 1\n AND t1.lat >= ? AND t1.lat <= ? AND t1.lng >= ? AND t1.lng <= ? AND t1.price >= ? AND t1.price <= ?\n HAVING distance <= ?\n UNION\n SELECT t2.n_bedrooms as rooms,'0' as floor,t2.locality,t2.route,t2.street_number,t2.price,t2.has_parking_space,t2.description,t2.area_constructed as area,t2.hide_address,t6.name,t2.ad_id,\n ({$R}*ACOS(SIN({$lat_r})*SIN(RADIANS(t2.lat))+COS({$lat_r})*COS(RADIANS(t2.lat))*COS(RADIANS(t2.lng)-{$lng_r}))) AS distance\n FROM sell_country_house AS t2\n LEFT JOIN category_country_house AS t6 ON t2.category_country_house_id = t6.id\n WHERE t2.is_new_development = 1\n AND t2.lat >= ? AND t2.lat <= ? AND t2.lng >= ? AND t2.lng <= ? AND t2.price >= ? AND t2.price <= ?\n HAVING distance <= ?\n UNION\n SELECT t3.n_bedrooms as rooms,t3.floor_number as floor,t3.locality,t3.route,t3.street_number,t3.price,t3.has_parking_space,t3.description,t3.area_constructed as area,t3.hide_address,\n IF(is_duplex = 1, 'Dúplex', IF(is_penthouse = 1, 'Ático', IF(is_studio = 1, 'Estudio', 'Piso'))) as `name`,t3.ad_id,\n ({$R}*ACOS(SIN({$lat_r})*SIN(RADIANS(t3.lat))+COS({$lat_r})*COS(RADIANS(t3.lat))*COS(RADIANS(t3.lng)-{$lng_r}))) AS distance\n FROM sell_apartment AS t3\n WHERE t3.is_new_development = 1\n AND t3.lat >= ? AND t3.lat <= ? AND t3.lng >= ? AND t3.lng <= ? AND t3.price >= ? AND t3.price <= ?\n HAVING distance <= ?\n ) AS t4\n ORDER BY t4.distance ASC;\n "), [$min_lat, $max_lat, $min_lng, $max_lng, $price_min, $price_max, $distance, $min_lat, $max_lat, $min_lng, $max_lng, $price_min, $price_max, $distance, $min_lat, $max_lat, $min_lng, $max_lng, $price_min, $price_max, $distance]);
break;
case '1':
//house + country house + apartment
$ads = \DB::select(\DB::raw("\n SELECT rooms,floor,locality,route,street_number,price,has_parking_space,description,area,hide_address,`name` as type,ad_id,distance FROM (\n SELECT t1.n_bedrooms as rooms,'0' as floor,t1.locality,t1.route,t1.street_number,t1.price,t1.has_parking_space,t1.description,t1.area_constructed as area,t1.hide_address,t5.name,t1.ad_id,\n ({$R}*ACOS(SIN({$lat_r})*SIN(RADIANS(t1.lat))+COS({$lat_r})*COS(RADIANS(t1.lat))*COS(RADIANS(t1.lng)-{$lng_r}))) AS distance\n FROM sell_house AS t1\n LEFT JOIN category_house AS t5 ON t1.category_house_id = t5.id\n WHERE t1.lat >= ? AND t1.lat <= ? AND t1.lng >= ? AND t1.lng <= ? AND t1.price >= ? AND t1.price <= ?\n HAVING distance <= ?\n UNION\n SELECT t2.n_bedrooms as rooms,'0' as floor,t2.locality,t2.route,t2.street_number,t2.price,t2.has_parking_space,t2.description,t2.area_constructed as area,t2.hide_address,t6.name,t2.ad_id,\n ({$R}*ACOS(SIN({$lat_r})*SIN(RADIANS(t2.lat))+COS({$lat_r})*COS(RADIANS(t2.lat))*COS(RADIANS(t2.lng)-{$lng_r}))) AS distance\n FROM sell_country_house AS t2\n LEFT JOIN category_country_house AS t6 ON t2.category_country_house_id = t6.id\n WHERE t2.lat >= ? AND t2.lat <= ? AND t2.lng >= ? AND t2.lng <= ? AND t2.price >= ? AND t2.price <= ?\n HAVING distance <= ?\n UNION\n SELECT t3.n_bedrooms as rooms,t3.floor_number as floor,t3.locality,t3.route,t3.street_number,t3.price,t3.has_parking_space,t3.description,t3.area_constructed as area,t3.hide_address,\n IF(is_duplex = 1, 'Dúplex', IF(is_penthouse = 1, 'Ático', IF(is_studio = 1, 'Estudio', 'Piso'))) as `name`,t3.ad_id,\n ({$R}*ACOS(SIN({$lat_r})*SIN(RADIANS(t3.lat))+COS({$lat_r})*COS(RADIANS(t3.lat))*COS(RADIANS(t3.lng)-{$lng_r}))) AS distance\n FROM sell_apartment AS t3\n WHERE t3.lat >= ? AND t3.lat <= ? AND t3.lng >= ? AND t3.lng <= ? AND t3.price >= ? AND t3.price <= ?\n HAVING distance <= ?\n ) AS t4\n ORDER BY t4.distance ASC;\n "), [$min_lat, $max_lat, $min_lng, $max_lng, $price_min, $price_max, $distance, $min_lat, $max_lat, $min_lng, $max_lng, $price_min, $price_max, $distance, $min_lat, $max_lat, $min_lng, $max_lng, $price_min, $price_max, $distance]);
break;
case '4':
//office
$ads = \DB::select(\DB::raw("\n SELECT floor_number as floor,locality,route,street_number,price,description,'Oficina' as type,area_constructed as area,hide_address,ad_id,\n ({$R}*ACOS(SIN({$lat_r})*SIN(RADIANS(t1.lat))+COS({$lat_r})*COS(RADIANS(t1.lat))*COS(RADIANS(t1.lng)-{$lng_r}))) AS distance\n FROM sell_office AS t1\n WHERE t1.lat >= ? AND t1.lat <= ? AND t1.lng >= ? AND t1.lng <= ? AND t1.price >= ? AND t1.price <= ?\n HAVING distance <= ?\n ORDER BY distance ASC;\n "), [$min_lat, $max_lat, $min_lng, $max_lng, $price_min, $price_max, $distance]);
break;
case '5':
//business
$ads = \DB::select(\DB::raw("\n SELECT floor_number as floor,locality,route,street_number,price,description,`name` as type,area_constructed as area,hide_address,ad_id,\n ({$R}*ACOS(SIN({$lat_r})*SIN(RADIANS(t1.lat))+COS({$lat_r})*COS(RADIANS(t1.lat))*COS(RADIANS(t1.lng)-{$lng_r}))) AS distance\n FROM sell_business AS t1\n LEFT JOIN category_business AS t2 ON t1.category_business_id = t2.id\n WHERE t1.lat >= ? AND t1.lat <= ? AND t1.lng >= ? AND t1.lng <= ? AND t1.price >= ? AND t1.price <= ?\n HAVING distance <= ?\n ORDER BY distance ASC;\n "), [$min_lat, $max_lat, $min_lng, $max_lng, $price_min, $price_max, $distance]);
break;
case '6':
//garage
$ads = \DB::select(\DB::raw("\n SELECT locality,route,street_number,price,description,'Garaje' as type,`name` as garage_capacity,hide_address,ad_id,\n ({$R}*ACOS(SIN({$lat_r})*SIN(RADIANS(t1.lat))+COS({$lat_r})*COS(RADIANS(t1.lat))*COS(RADIANS(t1.lng)-{$lng_r}))) AS distance\n FROM sell_garage AS t1\n LEFT JOIN garage_capacity AS t2 ON t1.garage_capacity_id = t2.id\n WHERE t1.lat >= ? AND t1.lat <= ? AND t1.lng >= ? AND t1.lng <= ? AND t1.price >= ? AND t1.price <= ?\n HAVING distance <= ?\n ORDER BY distance ASC;\n "), [$min_lat, $max_lat, $min_lng, $max_lng, $price_min, $price_max, $distance]);
break;
case '7':
//land
$ads = \DB::select(\DB::raw("\n SELECT locality,route,street_number,price,description,area_total as area,'Terreno' as type,`name` as land_category,hide_address,ad_id,\n ({$R}*ACOS(SIN({$lat_r})*SIN(RADIANS(t1.lat))+COS({$lat_r})*COS(RADIANS(t1.lat))*COS(RADIANS(t1.lng)-{$lng_r}))) AS distance\n FROM sell_land AS t1\n LEFT JOIN category_land AS t2 ON t1.category_land_id = t2.id\n WHERE t1.lat >= ? AND t1.lat <= ? AND t1.lng >= ? AND t1.lng <= ? AND t1.price >= ? AND t1.price <= ?\n HAVING distance <= ?\n ORDER BY distance ASC;\n "), [$min_lat, $max_lat, $min_lng, $max_lng, $price_min, $price_max, $distance]);
break;
}
break;
case '1':
//rent
switch ($input['typology']) {
case '0':
//new development
$ads = \DB::select(\DB::raw("\n SELECT rooms,floor,locality,route,street_number,price,has_parking_space,description,area,hide_address,`name` as type,ad_id,distance FROM (\n SELECT t1.n_bedrooms as rooms,'0' as floor,t1.locality,t1.route,t1.street_number,t1.price,t1.has_parking_space,t1.description,t1.area_constructed as area,t1.hide_address,t5.name,t1.ad_id,\n ({$R}*ACOS(SIN({$lat_r})*SIN(RADIANS(t1.lat))+COS({$lat_r})*COS(RADIANS(t1.lat))*COS(RADIANS(t1.lng)-{$lng_r}))) AS distance\n FROM rent_house AS t1\n LEFT JOIN category_house AS t5 ON t1.category_house_id = t5.id\n WHERE t1.is_new_development = 1\n AND t1.lat >= ? AND t1.lat <= ? AND t1.lng >= ? AND t1.lng <= ? AND t1.price >= ? AND t1.price <= ?\n HAVING distance <= ?\n UNION\n SELECT t2.n_bedrooms as rooms,'0' as floor,t2.locality,t2.route,t2.street_number,t2.price,t2.has_parking_space,t2.description,t2.area_constructed as area,t2.hide_address,t6.name,t2.ad_id,\n ({$R}*ACOS(SIN({$lat_r})*SIN(RADIANS(t2.lat))+COS({$lat_r})*COS(RADIANS(t2.lat))*COS(RADIANS(t2.lng)-{$lng_r}))) AS distance\n FROM rent_country_house AS t2\n LEFT JOIN category_country_house AS t6 ON t2.category_country_house_id = t6.id\n WHERE t2.is_new_development = 1\n AND t2.lat >= ? AND t2.lat <= ? AND t2.lng >= ? AND t2.lng <= ? AND t2.price >= ? AND t2.price <= ?\n HAVING distance <= ?\n UNION\n SELECT t3.n_bedrooms as rooms,t3.floor_number as floor,t3.locality,t3.route,t3.street_number,t3.price,t3.has_parking_space,t3.description,t3.area_constructed as area,t3.hide_address,\n IF(is_duplex = 1, 'Dúplex', IF(is_penthouse = 1, 'Ático', IF(is_studio = 1, 'Estudio', 'Piso'))) as `name`,t3.ad_id,\n ({$R}*ACOS(SIN({$lat_r})*SIN(RADIANS(t3.lat))+COS({$lat_r})*COS(RADIANS(t3.lat))*COS(RADIANS(t3.lng)-{$lng_r}))) AS distance\n FROM rent_apartment AS t3\n WHERE t3.is_new_development = 1\n AND t3.lat >= ? AND t3.lat <= ? AND t3.lng >= ? AND t3.lng <= ? AND t3.price >= ? AND t3.price <= ?\n HAVING distance <= ?\n ) AS t4\n ORDER BY t4.distance ASC;\n "), [$min_lat, $max_lat, $min_lng, $max_lng, $price_min, $price_max, $distance, $min_lat, $max_lat, $min_lng, $max_lng, $price_min, $price_max, $distance, $min_lat, $max_lat, $min_lng, $max_lng, $price_min, $price_max, $distance]);
break;
case '1':
//house + country house + apartment
$ads = \DB::select(\DB::raw("\n SELECT rooms,floor,locality,route,street_number,price,has_parking_space,description,area,hide_address,`name` as type,ad_id,distance FROM (\n SELECT t1.n_bedrooms as rooms,'0' as floor,t1.locality,t1.route,t1.street_number,t1.price,t1.has_parking_space,t1.description,t1.area_constructed as area,t1.hide_address,t5.name,t1.ad_id,\n ({$R}*ACOS(SIN({$lat_r})*SIN(RADIANS(t1.lat))+COS({$lat_r})*COS(RADIANS(t1.lat))*COS(RADIANS(t1.lng)-{$lng_r}))) AS distance\n FROM rent_house AS t1\n LEFT JOIN category_house AS t5 ON t1.category_house_id = t5.id\n WHERE t1.lat >= ? AND t1.lat <= ? AND t1.lng >= ? AND t1.lng <= ? AND t1.price >= ? AND t1.price <= ?\n HAVING distance <= ?\n UNION\n SELECT t2.n_bedrooms as rooms,'0' as floor,t2.locality,t2.route,t2.street_number,t2.price,t2.has_parking_space,t2.description,t2.area_constructed as area,t2.hide_address,t6.name,t2.ad_id,\n ({$R}*ACOS(SIN({$lat_r})*SIN(RADIANS(t2.lat))+COS({$lat_r})*COS(RADIANS(t2.lat))*COS(RADIANS(t2.lng)-{$lng_r}))) AS distance\n FROM rent_country_house AS t2\n LEFT JOIN category_country_house AS t6 ON t2.category_country_house_id = t6.id\n WHERE t2.lat >= ? AND t2.lat <= ? AND t2.lng >= ? AND t2.lng <= ? AND t2.price >= ? AND t2.price <= ?\n HAVING distance <= ?\n UNION\n SELECT t3.n_bedrooms as rooms,t3.floor_number as floor,t3.locality,t3.route,t3.street_number,t3.price,t3.has_parking_space,t3.description,t3.area_constructed as area,t3.hide_address,\n IF(is_duplex = 1, 'Dúplex', IF(is_penthouse = 1, 'Ático', IF(is_studio = 1, 'Estudio', 'Piso'))) as `name`,t3.ad_id,\n ({$R}*ACOS(SIN({$lat_r})*SIN(RADIANS(t3.lat))+COS({$lat_r})*COS(RADIANS(t3.lat))*COS(RADIANS(t3.lng)-{$lng_r}))) AS distance\n FROM rent_apartment AS t3\n WHERE t3.lat >= ? AND t3.lat <= ? AND t3.lng >= ? AND t3.lng <= ? AND t3.price >= ? AND t3.price <= ?\n HAVING distance <= ?\n ) AS t4\n ORDER BY t4.distance ASC;\n "), [$min_lat, $max_lat, $min_lng, $max_lng, $price_min, $price_max, $distance, $min_lat, $max_lat, $min_lng, $max_lng, $price_min, $price_max, $distance, $min_lat, $max_lat, $min_lng, $max_lng, $price_min, $price_max, $distance]);
break;
case '2':
//vacation/lodge
$ads = \DB::select(\DB::raw("\n SELECT floor_number as floor,locality,route,street_number,description,t2.`name` as type,t4.`name` as surroundings,area_total as area,min_capacity,max_capacity,hide_address,MIN(p_one_month) as min_price_per_night,ad_id,\n ({$R}*ACOS(SIN({$lat_r})*SIN(RADIANS(t1.lat))+COS({$lat_r})*COS(RADIANS(t1.lat))*COS(RADIANS(t1.lng)-{$lng_r}))) AS distance\n FROM rent_vacation AS t1\n LEFT JOIN category_lodging AS t2 ON t1.category_lodging_id = t2.id\n LEFT JOIN vacation_season_price AS t3 ON t1.id = t3.rent_vacation_id\n LEFT JOIN surroundings AS t4 ON t1.surroundings_id = t4.id\n WHERE t1.lat >= ? AND t1.lat <= ? AND t1.lng >= ? AND t1.lng <= ?\n HAVING distance <= ? AND min_price_per_night >= ? AND min_price_per_night <= ?\n ORDER BY distance ASC;\n "), [$min_lat, $max_lat, $min_lng, $max_lng, $distance, $price_min, $price_max]);
break;
case '3':
//room
$ads = \DB::select(\DB::raw("\n SELECT floor_number as floor,locality,route,street_number,price,description,'Habitación' as type,area_room as area,hide_address,ad_id,\n ({$R}*ACOS(SIN({$lat_r})*SIN(RADIANS(t1.lat))+COS({$lat_r})*COS(RADIANS(t1.lat))*COS(RADIANS(t1.lng)-{$lng_r}))) AS distance\n FROM rent_room AS t1\n WHERE t1.lat >= ? AND t1.lat <= ? AND t1.lng >= ? AND t1.lng <= ? AND t1.price >= ? AND t1.price <= ?\n HAVING distance <= ?\n ORDER BY distance ASC;\n "), [$min_lat, $max_lat, $min_lng, $max_lng, $price_min, $price_max, $distance]);
break;
case '4':
//office
$ads = \DB::select(\DB::raw("\n SELECT floor_number as floor,locality,route,street_number,price,description,'Oficina' as type,area_constructed as area,hide_address,ad_id,\n ({$R}*ACOS(SIN({$lat_r})*SIN(RADIANS(t1.lat))+COS({$lat_r})*COS(RADIANS(t1.lat))*COS(RADIANS(t1.lng)-{$lng_r}))) AS distance\n FROM rent_office AS t1\n WHERE t1.lat >= ? AND t1.lat <= ? AND t1.lng >= ? AND t1.lng <= ? AND t1.price >= ? AND t1.price <= ?\n HAVING distance <= ?\n ORDER BY distance ASC;\n "), [$min_lat, $max_lat, $min_lng, $max_lng, $price_min, $price_max, $distance]);
break;
case '5':
//business
$ads = \DB::select(\DB::raw("\n SELECT floor_number as floor,locality,route,street_number,price,description,`name` as type,area_constructed as area,hide_address,ad_id,\n ({$R}*ACOS(SIN({$lat_r})*SIN(RADIANS(t1.lat))+COS({$lat_r})*COS(RADIANS(t1.lat))*COS(RADIANS(t1.lng)-{$lng_r}))) AS distance\n FROM rent_business AS t1\n LEFT JOIN category_business AS t2 ON t1.category_business_id = t2.id\n WHERE t1.lat >= ? AND t1.lat <= ? AND t1.lng >= ? AND t1.lng <= ? AND t1.price >= ? AND t1.price <= ?\n HAVING distance <= ?\n ORDER BY distance ASC;\n "), [$min_lat, $max_lat, $min_lng, $max_lng, $price_min, $price_max, $distance]);
break;
case '6':
//garage
$ads = \DB::select(\DB::raw("\n SELECT locality,route,street_number,price,description,'Garaje' as type,`name` as garage_capacity,hide_address,ad_id,\n ({$R}*ACOS(SIN({$lat_r})*SIN(RADIANS(t1.lat))+COS({$lat_r})*COS(RADIANS(t1.lat))*COS(RADIANS(t1.lng)-{$lng_r}))) AS distance\n FROM rent_garage AS t1\n LEFT JOIN garage_capacity AS t2 ON t1.garage_capacity_id = t2.id\n WHERE t1.lat >= ? AND t1.lat <= ? AND t1.lng >= ? AND t1.lng <= ? AND t1.price >= ? AND t1.price <= ?\n HAVING distance <= ?\n ORDER BY distance ASC;\n "), [$min_lat, $max_lat, $min_lng, $max_lng, $price_min, $price_max, $distance]);
break;
case '7':
//land
$ads = \DB::select(\DB::raw("\n SELECT locality,route,street_number,price,description,area_total as area,'Terreno' as type,`name` as land_category,hide_address,ad_id,\n ({$R}*ACOS(SIN({$lat_r})*SIN(RADIANS(t1.lat))+COS({$lat_r})*COS(RADIANS(t1.lat))*COS(RADIANS(t1.lng)-{$lng_r}))) AS distance\n FROM rent_land AS t1\n LEFT JOIN category_land AS t2 ON t1.category_land_id = t2.id\n WHERE t1.lat >= ? AND t1.lat <= ? AND t1.lng >= ? AND t1.lng <= ? AND t1.price >= ? AND t1.price <= ?\n HAVING distance <= ?\n ORDER BY distance ASC;\n "), [$min_lat, $max_lat, $min_lng, $max_lng, $price_min, $price_max, $distance]);
break;
}
break;
}
}
// Prepare a human readable typology string for the results view
switch ($input['typology']) {
case '0':
//new development
$typology = 'promociones de obra nueva';
break;
case '1':
//house + country house + apartment
$typology = 'casas y pisos';
break;
case '2':
//vacation/lodge
$typology = 'alquileres vacacionales';
break;
case '3':
//room
$typology = 'habitaciones';
break;
case '4':
//office
$typology = 'oficinas';
break;
case '5':
//business
$typology = 'locales o naves';
break;
case '6':
//garage
$typology = 'garajes';
break;
case '7':
//land
$typology = 'terrenos';
break;
default:
$typology = 'inmueble';
break;
}
// Search type as a variable, to be sent to the results view
$search_type = $input['search_type'];
return view('results', compact('ads', 'typology', 'locality', 'search_type', 'input'));
}
