/**
* Creates query string from given params
* @param string $select
* @param string $sorting
* @param array $filters
* @return string
*/
protected static function GetQuery($select = "SELECT * ", $sorting = "", $filters = null, $groupBy = '')
{
$query = $select . " FROM #S#offers AS o ";
if ($filters != null && (array_key_exists("department", $filters) || array_key_exists("departments", $filters) || array_key_exists("NajpierwIdekiOddzialow", $filters) || array_key_exists("not_agents", $filters) || array_key_exists("not_mls", $filters))) {
$query .= " LEFT JOIN #S#agents AS a ON o.agents_id=a.id ";
}
if (array_key_exists('properties', $filters)) {
$i = 1;
foreach ($filters['properties'] as $name => $values) {
$query .= " INNER JOIN #S#offers_properties AS op{$i} ON (o.id = op{$i}.offers_id AND o.id_lng = op{$i}.offers_id_lng AND op{$i}.properties_id = " . OffersHelper::getProps($name) . ")";
$i++;
}
if ($groupBy === '') {
$groupBy = 'o.id';
}
}
$query .= " WHERE 1=1 ";
if ($filters != null) {
foreach ($filters as $key => $value) {
switch ($key) {
case "country":
$query .= " AND o.country = ?";
break;
case "floor_noFrom":
$query .= " AND o.floor_no >= ?";
break;
case "floor_noTo":
$query .= " AND o.floor_no <= ?";
break;
case "RokBudowyOd":
$query .= " AND CAST(o.year_of_construction AS SIGNED) >= ?";
break;
case "RokBudowyDo":
$query .= " AND CAST(o.year_of_construction AS SIGNED) <= ?";
break;
case "PierwszaStrona":
$query .= " AND o.first_page = ?";
break;
case "house_types":
$query .= " AND o.house_type IN (" . self::prepareStringToBind($value) . ")";
break;
case "object_types":
$query .= " AND o.object_type IN (" . self::prepareStringToBind($value) . ")";
break;
case "wylacznosc":
$query .= " AND o.contract_type LIKE ?";
break;
case "IloscOdslonWWWSort":
$query .= " AND o.visits_no ";
break;
case "StatusWlasnosci":
$query .= " AND o.ownership_status = ?";
break;
case "StanPrawny":
$query .= " AND o.legal_status = ?";
break;
case "umeblowanie":
$query .= " AND o.furnishings = ?";
break;
case "field_areaFrom":
$query .= " AND o.field_area >= ?";
break;
case "field_areaTo":
$query .= " AND o.field_area <= ?";
break;
case "zamiana":
$query .= " AND o.change_status = 1";
break;
case "pola_opisowe":
$query .= " AND (o.notices LIKE ? OR o.notices_property LIKE ?)";
break;
case "IdLng":
$query .= " AND o.id_lng=?";
break;
case "status":
$query .= " AND o.status = ?";
break;
case "statuses":
$query .= " AND o.status IN (" . self::prepareStringToBind($value) . ")";
break;
case "symbol":
$query .= " AND o.symbol LIKE ?";
break;
case "province":
$query .= " AND o.province = ?";
break;
case "district":
$query .= " AND o.district = ?";
break;
case "location":
$query .= " AND o.location = ?";
break;
case "object":
$query .= " AND o.object = ?";
break;
case "quarter":
$query .= " AND o.quarter = ?";
break;
case "region":
$query .= " AND o.region = ?";
break;
case "street":
$query .= " AND o.street LIKE ?";
break;
case "building_technology":
$query .= " AND o.building_technology = ?";
break;
case "construction_material":
$query .= " AND o.construction_material = ?";
break;
case "construction_status":
$query .= " AND o.construction_status = ?";
break;
case "construction_statuses":
$query .= " AND o.construction_status IN (" . self::prepareStringToBind($value) . ")";
break;
case "building_type":
$query .= " AND o.building_type = ?";
break;
case "priceFrom":
$query .= " AND o.price >= ?";
break;
case "priceTo":
$query .= " AND o.price <= ?";
break;
case "price_squareFrom":
$query .= " AND o.price_square >= ?";
break;
case "price_squareTo":
$query .= " AND o.price_square <= ?";
break;
case "rooms_no":
$query .= " AND o.rooms_no = ?";
break;
case "rooms_noFrom":
$query .= " AND o.rooms_no >= ?";
break;
case "rooms_noTo":
$query .= " AND o.rooms_no <= ?";
break;
case "areaFrom":
$query .= " AND o.area >= ?";
break;
case "areaTo":
$query .= " AND o.area <= ?";
break;
case "floorFrom":
$query .= " AND FLOOR(REPLACE(REPLACE(o.floor,'parter','0'),'p','')) >= ?";
break;
case "floorTo":
$query .= " AND FLOOR(REPLACE(REPLACE(o.floor,'parter','0'),'p','')) <= ?";
break;
case "districts":
$query .= " AND o.district IN (" . self::prepareStringToBind($value) . ")";
break;
case "locations":
$query .= " AND o.location IN (" . self::prepareStringToBind($value) . ")";
break;
case "objects":
$query .= " AND o.object IN (" . self::prepareStringToBind($value) . ")";
break;
case "quarters":
$query .= " AND o.quarter IN (" . self::prepareStringToBind($value) . ")";
break;
case "noquarters":
$query .= " AND o.quarter NOT IN (" . self::prepareStringToBind($value) . ")";
break;
case "locations_or_quarters":
$query .= " AND (o.location IN (" . self::prepareStringToBind($value) . ") OR o.quarter IN (" . self::prepareStringToBind($value) . "))";
break;
case "locations_or_quarters_or_region":
$query .= " AND (o.location IN (" . self::prepareStringToBind($value) . ") OR o.quarter IN (" . self::prepareStringToBind($value) . ") OR o.region IN (" . self::prepareStringToBind($value) . "))";
break;
case "quarters_or_region":
$query .= " AND ( o.quarter IN (" . self::prepareStringToBind($value) . ") OR o.region IN (" . self::prepareStringToBind($value) . "))";
break;
case "districts_or_locations_or_quarters_or_region":
$query .= " AND (o.district IN (" . self::prepareStringToBind($value) . ") OR o.location IN (" . self::prepareStringToBind($value) . ") OR o.quarter IN (" . self::prepareStringToBind($value) . ") OR o.region IN (" . self::prepareStringToBind($value) . "))";
break;
case "districts_or_locations_or_quarters_or_region_or_street":
$query .= " AND (o.district IN (" . self::prepareStringToBind($value) . ") OR o.location IN (" . self::prepareStringToBind($value) . ") OR o.quarter IN (" . self::prepareStringToBind($value) . ") OR o.region IN (" . self::prepareStringToBind($value) . ") OR o.street IN(" . self::prepareStringToBind($value) . ") )";
break;
case "location_or_location":
$query .= " AND " . self::prepareArrayToBind($value);
break;
case "building_types":
$query .= " AND o.building_type IN (" . self::prepareStringToBind($value) . ")";
break;
case "creationDateFrom":
$query .= " AND o.creation_date >= ?";
break;
case "creationDateTo":
$query .= " AND o.creation_date <= ?";
break;
case "modificationDateFrom":
$query .= " AND o.modification_date >= ?";
break;
case "modificationDateTo":
$query .= " AND o.modification_date <= ?";
break;
case "expirationDateFrom":
$query .= " AND o.expiration_date >= ?";
break;
case "video_swf":
$query .= " AND (o.has_swfs=1 OR o.video_link IS NOT NULL)";
break;
case "virtual_visit":
$query .= " AND o.has_swfs=1";
break;
case "without_virtual_visit":
$query .= " AND o.has_swfs=0";
break;
case "photos":
$query .= " AND o.has_photos=1";
break;
case "projs":
$query .= " AND o.has_proj=1";
break;
case "ids":
$query .= " AND o.id IN (" . self::prepareStringToBind($value) . ")";
break;
case "not_ids":
$query .= " AND o.id NOT IN (" . self::prepareStringToBind($value) . ")";
break;
case "original":
$query .= " AND o.original=?";
break;
case "on_map":
$query .= " AND o.latitude > 0 AND o.longitude > 0";
break;
case "map_bounds":
$query .= " AND o.latitude < ? AND o.latitude > ? AND o.longitude < ? AND o.longitude > ?";
break;
case "department":
$query .= " AND a.departments_id = ?";
break;
case "departments":
$query .= " AND a.departments_id IN (" . self::prepareStringToBind($value) . ")";
break;
case "not_agents":
$query .= " AND a.id NOT IN (" . self::prepareStringToBind($value) . ")";
break;
case "not_mls":
$query .= " AND a.id NOT IN (" . Agents::GetMLSAgentId() . ")";
break;
case "zeroprow":
$query .= " AND o.no_commission=1 ";
break;
case "hasUwagiOpis":
$query .= " AND o.notices != ''";
break;
case "kitchen_type":
$query .= " AND o.id IN (SELECT ofr.offers_id FROM #S#offers_rooms ofr WHERE ofr.type IN (" . self::prepareStringToBind($value) . ") )";
break;
case "SortNaWylacznosc":
break;
case "properties":
$query .= self::preparePropertiesToQuery($value);
break;
case "NajpierwIdekiOddzialow":
break;
//na potrzeby sortowania wg własnych oddziałów
//na potrzeby sortowania wg własnych oddziałów
default:
$query .= " AND o.{$key}=?";
break;
}
}
}
if ($groupBy) {
$query .= " GROUP BY " . $groupBy;
}
if ($sorting != "") {
if ($filters != null && array_key_exists("SortNaWylacznosc", $filters)) {
$sorting = "id_lng ASC, CASE o.contract_type WHEN 'Umowa na wyłączność' THEN 0 ELSE 1 END ASC, o." . self::prepareSort($sorting);
$query .= " ORDER BY " . $sorting;
} elseif ($filters != null && array_key_exists("NajpierwIdekiOddzialow", $filters)) {
$sort = "id_lng ASC, CASE a.departments_id ";
$odids = explode(",", $filters["NajpierwIdekiOddzialow"]);
foreach ($odids as $oid) {
$sort .= "WHEN " . $oid . " THEN 0 ";
}
$sort .= " ELSE 1 END ASC, ";
$sorting = $sort . self::prepareSort($sorting);
$query .= " ORDER BY " . $sorting;
} else {
$query .= " ORDER BY o." . self::prepareSort($sorting);
}
}
return $query;
}