/** * Returns property objects from offers and count them. * @param string $name * @param int $idLng * @return properties array */ public static function GetPropertiesFromOffersCount($name, $idLng = 1045) { $db = DataBase::GetDbInstance(); $params = array((int) OffersHelper::getProps($name), (int) $idLng); $query = "SELECT op.value, count(op.value) FROM #S#offers_properties op WHERE op.properties_id=? AND op.offers_id_lng=? GROUP BY op.value"; $result = $db->ExecuteQueryWithParams($query, $params); $list = array(); while ($row = $db->FetchArray($result)) { $list[$row[0]] = $row[1]; } return $list; }
/** * Creates query string from given params * @param string $select * @param string $sorting * @param mixed $filters * @return string */ protected static function GetQuery($select = "SELECT * ", $sorting = "", $filters = null) { $query = $select . " FROM #S#investments AS i "; if (array_key_exists('properties', $filters)) { $i = 1; foreach ($filters['properties'] as $name => $values) { $query .= " INNER JOIN #S#investments_properties AS ip{$i} ON (i.id = ip{$i}.investments_id AND i.id_lng = ip{$i}.investments_id_lng) INNER JOIN #S#properties AS p{$i} ON (p{$i}.id = ip{$i}.properties_id AND p{$i}.id = " . OffersHelper::getProps($name) . ")"; $i++; } } $query .= " WHERE 1=1 "; if ($filters != null) { foreach ($filters as $key => $value) { switch ($key) { case "IdLng": $query .= " AND i.id_lng=?"; break; case "no": $query .= " AND i.no LIKE ?"; break; case "number": $query .= " AND i.number LIKE ?"; break; case "name": $query .= " AND i.name LIKE ?"; break; case "description": $query .= " AND i.description LIKE ?"; break; case "shortDescription": $query .= " AND i.short_description LIKE ?"; break; case "contact": $query .= " AND i.contact LIKE ?"; break; case "country": $query .= " AND i.country LIKE ?"; break; case "province": $query .= " AND i.province LIKE ?"; break; case "district": $query .= " AND i.district LIKE ?"; break; case "location": $query .= " AND i.location LIKE ?"; break; case "quarter": $query .= " AND i.quarter LIKE ?"; break; case "region": $query .= " AND i.region LIKE ?"; break; case "street": $query .= " AND i.street LIKE ?"; break; case "category": $query .= " AND i.category LIKE ?"; break; case "creationDateFrom": $query .= " AND i.creation_date >= ?"; break; case "creationDateTo": $query .= " AND i.creation_date <= ?"; break; case "dueDateFrom": $query .= " AND i.due_date >= ?"; break; case "dueDateTo": $query .= " AND i.due_date <= ?"; break; case "totalAreaFrom": $query .= " AND i.total_area >= ?"; break; case "totalAreaTo": $query .= " AND i.total_area <= ?"; break; case "grossVolumeFrom": $query .= " AND i.gross_volume >= ?"; break; case "grossVolumeTo": $query .= " AND i.gross_volume <= ?"; break; case "area": $query .= " AND i.area_from <= ?" . " AND i.area_to >= ?"; break; case "areaFrom": $query .= " AND i.area_from >= ?"; break; case "areaTo": $query .= " AND i.area_to <= ?"; break; case "price": $query .= " AND i.price_from <= ?" . " AND i.price_to >= ?"; break; case "priceFrom": $query .= " AND i.price_from >= ?"; break; case "priceTo": $query .= " AND i.price_to <= ?"; break; case "pricem2": $query .= " AND i.pricem2_from <= ?" . " AND i.pricem2_to >= ?"; break; case "floor": $query .= " AND i.floor_from <= ?" . " AND i.floor_to >= ?"; break; case "rooms": $query .= " AND i.rooms_no_from <= ?" . " AND i.rooms_no_to >= ?"; break; case "roomsNoFrom": $query .= " AND i.rooms_no_from >= ?"; break; case "roomsNoTo": $query .= " AND i.rooms_no_to <= ?"; break; case "districts": $query .= " AND i.district IN (" . self::prepareStringToBind($value) . ")"; break; case "locations": $query .= " AND i.location IN (" . self::prepareStringToBind($value) . ")"; break; case "quarters": $query .= " AND i.quarter IN (" . self::prepareStringToBind($value) . ")"; break; case "categories": $query .= " AND i.category IN (" . self::prepareStringToBind($value) . ")"; break; case "specjalne": $query .= " AND i.special=1"; break; case "department": $query .= " AND i.departments_id = ?"; break; case "departments": $query .= " AND i.departments_id IN (" . self::prepareStringToBind($value) . ")"; break; case "properties": $query .= self::preparePropertiesToQuery($value); break; default: $query .= " AND i.{$key}=?"; break; } } } if ($sorting != "") { $query .= " ORDER BY i." . self::prepareSort($sorting); } return $query; }