/**
* 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;
}
