File: /var/www/limestate-api/Controllers/SearchController.php
<?php
class SearchController
{
const INDEX_COMPLEXES = 'complexes';
const INDEX_SECOND = 'second';
const INDEX_FLATS = 'flats';
const CATEGORY_NEW_BUILDING = 1;
const CATEGORY_SECONDARY = 2;
public static function actionComplexes($strict = true)
{
$price_up_to = Data::getVar('price_up_to', 0);
$rooms = Data::getVar('rooms', []);
$completedAt = Data::getVar('completed_at');
$completedAfter = Data::getVar('completed_after');
$district = Data::getVar('district', []);
$subway = Data::getVar('subway', []);
$areaFrom = Data::getVar('area_from');
$areaTo = Data::getVar('area_to');
$phrase = Data::getVar('phrase');
$options = Data::getVar('options', []);
$categoryId = Data::getVar('category_id', 1);
$isApartment = Data::getVar('is_apartment');
$limit = Data::getVar('limit', 20);
$offset = Data::getVar('offset', 0);
$orderBy = Data::getVar('order_by', false);
$orderDirection = Data::getVar('order_dir', 'DESC') == 'DESC' ? 'DESC' : 'ASC';
$totalOnly = (bool) Data::getVar('total_only', false);
$developerId = (int) Data::getVar('developer_id', false);
if ($categoryId == 1) {
$sql = Complex::$db->select(
'c.*, MIN(f.price) min_price, MAX(f.price) max_price',
'c.street_name AS street',
'cd.name city_district', 'm.name metro', 'b.category_id category_id',
'GROUP_CONCAT(DISTINCT(fo.alias) SEPARATOR ", ") options'
)
->from(Complex::$table_name . ' c')
->join(Building::$table_name . ' b', 'b.complex_id = c.id')
->join(Flat::$table_name . ' f', 'f.building_id = b.id')
->joinLeft(CityDistricts::$table_name . ' cd', 'cd.id = c.city_district_id')
->joinLeft(Metro::$table_name . ' m', 'm.id = c.metro_id')
->joinLeft(FlatOptionRelation::$table_name . ' fro', 'f.id = fro.flat_id')
->joinLeft(FlatOption::$table_name . ' fo', 'fo.id = fro.option_id')
->where('f.published = 1')
->where('c.published = 1')
->where('b.published = 1')
->where('c.deleted_at IS NULL')
->where('b.deleted_at IS NULL')
->where('f.deleted_at IS NULL')
->where('b.category_id = ?', $categoryId)
->where('f.price > 0')
->group('c.id');
$sqlCount = Complex::$db->select('c.id')
->from(Complex::$table_name . ' c')
->join(Building::$table_name . ' b', 'b.complex_id = c.id')
->join(Flat::$table_name . ' f', 'f.building_id = b.id')
->joinLeft(CityDistricts::$table_name . ' cd', 'cd.id = c.city_district_id')
->joinLeft(Metro::$table_name . ' m', 'm.id = c.metro_id')
->joinLeft(FlatOptionRelation::$table_name . ' fro', 'f.id = fro.flat_id')
->joinLeft(FlatOption::$table_name . ' fo', 'fo.id = fro.option_id')
->where('f.published = 1')
->where('c.published = 1')
->where('b.published = 1')
->where('c.deleted_at IS NULL')
->where('b.deleted_at IS NULL')
->where('f.deleted_at IS NULL')
->where('b.category_id = ?', $categoryId)
->where('f.price > 0')
->group('c.id');
} else {
$sql = Complex::$db->select(
'f.id', 'f.price', 'b.name', 'b.latlng',
'b.street_name street', 'cd.name city_district', 'b.category_id category_id',
'm.name metroStation', 'ml.name metroLineName', 'ml.color metroLineColor',
'b.completed_year', 'f.rooms', 'f.total_area area', 'b.housing_number building_number',
'GROUP_CONCAT(DISTINCT(fo.alias) SEPARATOR ", ") options'
)
->from(Flat::$table_name . ' f')
->join(Building::$table_name . ' b', 'f.building_id = b.id')
->joinLeft(CityDistricts::$table_name . ' cd', 'cd.id = b.city_district_id')
->joinLeft(Metro::$table_name . ' m', 'm.id = b.metro_id')
->joinLeft(MetroLine::$table_name . ' ml', 'ml.id = m.line_id')
->joinLeft(FlatOptionRelation::$table_name . ' fro', 'f.id = fro.flat_id')
->joinLeft(FlatOption::$table_name . ' fo', 'fo.id = fro.option_id')
->where('f.published = 1')
->where('b.published = 1')
->where('b.deleted_at IS NULL')
->where('f.deleted_at IS NULL')
->where('b.category_id = ?', $categoryId)
->where('f.price > 0')
->group('f.id');
$sqlCount = Complex::$db->select('f.id')
->from(Flat::$table_name . ' f')
->join(Building::$table_name . ' b', 'f.building_id = b.id')
->joinLeft(CityDistricts::$table_name . ' cd', 'cd.id = b.city_district_id')
->joinLeft(Metro::$table_name . ' m', 'm.id = b.metro_id')
->joinLeft(FlatOptionRelation::$table_name . ' fro', 'f.id = fro.flat_id')
->joinLeft(FlatOption::$table_name . ' fo', 'fo.id = fro.option_id')
->where('f.published = 1')
->where('b.published = 1')
->where('b.deleted_at IS NULL')
->where('f.deleted_at IS NULL')
->where('b.category_id = ?', $categoryId)
->where('f.price > 0')
->group('f.id');
}
if (!empty($orderBy)) {
switch ($orderBy) {
case 'random':
$sql->order('RAND()');
break;
case 'completed':
$sql->order('completed_year', $orderDirection)
->order('completed_quarter', $orderDirection);
break;
default:
$sql->order($orderBy, $orderDirection);
break;
}
}
if (!empty($price_up_to)) {
$sql->where('f.price <= ? ', (int) $price_up_to);
$sqlCount->where('f.price <= ? ', (int) $price_up_to);
}
if (!empty($rooms)) {
$sql->where('f.rooms IN (?)', [$rooms]);
$sqlCount->where('f.rooms IN (?)', [$rooms]);
}
if (!empty($completedAt)) {
if ($completedAt == 'сдан') {
$year = date('Y');
$quarter = ceil(date('n', time()) / 3);
} else {
list($year, $quarter) = explode('-', $completedAt);
}
$sql->where('c.completed_year < ? OR (c.completed_year = ? AND c.completed_quarter <= ?)', [
$year,
$year,
$quarter,
]);
$sqlCount->where('c.completed_year < ? OR (c.completed_year = ? AND c.completed_quarter <= ?)', [
$year,
$year,
$quarter,
]);
}
if (!empty($completedAfter)) {
list($year, $quarter) = explode('-', $completedAfter);
$sql->where('c.completed_year > ? OR (c.completed_year = ? AND c.completed_quarter >= ?)', [$year, $year, $quarter]);
$sqlCount->where('c.completed_year > ? OR (c.completed_year = ? AND c.completed_quarter >= ?)', [$year, $year, $quarter]);
}
if (!empty($district)) {
if ($categoryId == 1) {
$sql->where('c.city_district_id IN (?)', [$district]);
$sqlCount->where('c.city_district_id IN (?)', [$district]);
} else {
$sql->where('b.city_district_id IN (?)', [$district]);
$sqlCount->where('b.city_district_id IN (?)', [$district]);
}
}
if (!empty($subway)) {
$sql->where('b.metro_id IN (?)', [$subway]);
$sqlCount->where('b.metro_id IN (?)', [$subway]);
}
if (!empty($areaFrom)) {
$sql->where('f.total_area >= ?', $areaFrom);
$sqlCount->where('f.total_area >= ?', $areaFrom);
}
if (!empty($areaTo)) {
$sql->where('f.total_area <= ?', $areaTo);
$sqlCount->where('f.total_area <= ?', $areaTo);
}
if (!empty($developerId)) {
$sql->where('b.developer_id = ?', $developerId);
$sqlCount->where('b.developer_id = ?', $developerId);
}
if (!empty($categoryId)) {
$sql->where('b.category_id = ?', $categoryId);
$sqlCount->where('b.category_id = ?', $categoryId);
}
if (isset($isApartment)) {
$sql->where('c.is_apartment = ?', (int) $isApartment);
$sqlCount->where('c.is_apartment = ?', (int) $isApartment);
}
if (!empty($phrase) && $categoryId == 1) {
$values = $wheres = [];
if ($strict === false) {
$phrase = explode(' ', $phrase);
foreach ($phrase as $word) {
if (mb_strlen($word) > 2) {
$values[] = ['%' . $word . '%'];
$values[] = ['%' . $word . '%'];
$values[] = ['%' . $word . '%'];
$wheres[] = 'c.name LIKE ? OR m.name LIKE ? OR cd.name LIKE ?';
}
}
} else {
$wheres[] = 'REPLACE ( c.name ,"\"", "") LIKE ? OR m.name LIKE ? OR cd.name LIKE ?';
$values = [['%' . $phrase . '%'], ['%' . $phrase . '%'], ['%' . $phrase . '%']];
}
if (count($wheres)) {
$sql->where(implode(' OR ', $wheres), $values);
$sqlCount->where(implode(' OR ', $wheres), $values);
}
}
if (!empty($options)) {
$sql->where('fo.alias IN (?)', [$options]);
$sqlCount->where('fo.alias IN (?)', [$options]);
}
$sql->limit($limit, $offset);
$totalCount = count($sqlCount->fetchAll());
if ($totalCount == 0 && $strict !== false) {
self::actionComplexes(false);
}
if ($totalOnly) {
Ajax::success(['total' => $totalCount]);
}
$complexes = $sql->fetchAll();
foreach ($complexes as $complex) {
$gallery = $categoryId == 1 ? null : Attachment::getFlatGallery($complex->id);
Complex::fillGallery($complex, $gallery);
}
Ajax::success(['complexes' => $complexes, 'total' => $totalCount]);
}
public static function actionComplexes2($strict = true)
{
$price_up_to = Data::getVar('price_up_to', 0);
$rooms = Data::getVar('rooms', []);
$completedAt = Data::getVar('completed_at');
$completedAfter = Data::getVar('completed_after');
$district = Data::getVar('district', []);
$subway = Data::getVar('subway', []);
$areaFrom = Data::getVar('area_from');
$areaTo = Data::getVar('area_to');
$phrase = Data::getVar('phrase');
$options = Data::getVar('options', []);
$categoryId = Data::getVar('category_id', self::CATEGORY_NEW_BUILDING);
$isApartment = Data::getVar('is_apartment');
$limit = Data::getVar('limit', 20);
$offset = Data::getVar('offset', 0);
$orderBy = Data::getVar('order_by', false);
$orderDirection = Data::getVar('order_dir', 'DESC') == 'DESC' ? 'desc' : 'asc';
$totalOnly = (bool) Data::getVar('total_only', false);
$developerId = (int) Data::getVar('developer_id', false);
$isNewBuildings = self::CATEGORY_NEW_BUILDING == $categoryId;
$elastic = new Elastic();
$params = [
'index' => $isNewBuildings ? self::INDEX_COMPLEXES : self::INDEX_SECOND,
'body' => [
'track_total_hits' => true,
'from' => $offset,
'size' => $limit,
'query' => [],
],
];
$query = [];
if (!empty($price_up_to)) {
$field = $isNewBuildings ? 'buildings.flats.price' : 'price';
if ($isNewBuildings) {
$query['bool']['must'][] = [
'nested' => [
'path' => 'buildings.flats',
'query' => [
'bool' => [
'filter' => [
'range' => [
$field => [
'lte' => $price_up_to,
],
],
],
],
],
],
];
} else {
$query['bool']['filter'][] = [
'range' => [
$field => [
'lte' => $price_up_to,
],
],
];
}
}
if (!empty($rooms)) {
$field = $isNewBuildings ? 'buildings.flats.rooms' : 'rooms';
$bool = [];
foreach($rooms as $room) {
if (strpos($room, '+') !== false) {
$room = (int)$room;
$filter = [
'range' => [
$field => [
'gte' => $room,
],
],
];
} else {
$filter = [
'term' => [
$field => $room,
],
];
}
$bool['should'][] = $filter;
$bool['minimum_should_match'] = 1;
}
if ($isNewBuildings) {
$query['bool']['must'][] = [
'nested' => [
'path' => 'buildings.flats',
'query' => [
'bool' => $bool,
],
],
];
} else {
$query['bool']['should'] = $bool['should'];
$query['bool']['minimum_should_match']= 1;
}
}
if (!empty($completedAt)) {
if ($completedAt == 'сдан') {
$year = date('Y');
$quarter = ceil(date('n', time()) / 3);
} else {
list($year, $quarter) = explode('-', $completedAt);
}
$field1 = $isNewBuildings ? 'completed_year' : 'building_completed_year';
$field2 = $isNewBuildings ? 'completed_quarter' : 'building_completed_quarter';
$subMust = [];
$subMust[] = [
'range' => [
$field2 => [
'lte' => (int) $quarter,
],
],
];
$subMust[] = [
'match' => [
$field1 => (int) $year,
],
];
if ($isNewBuildings) {
$query['bool']['must']['bool']['should'][] = [
'bool' => [
'filter' => [
'range' => [
$field1 => [
'lt' => (int) $year,
'gte' => 0,
],
],
],
],
];
$query['bool']['must']['bool']['should'][] = [
'bool' => [
'must' => $subMust,
],
];
} else {
$query['bool']['must']['bool']['should'][] = [
'range' => [
$field1 => [
'lt' => (int) $year,
'gt' => 0,
],
],
];
$query['bool']['must']['bool']['should'][] = [
'bool' => [
'must' => $subMust,
],
];
}
}
if (!empty($completedAfter)) {
list($year, $quarter) = explode('-', $completedAfter);
$field1 = $isNewBuildings ? 'buildings.completed_year' : 'building_completed_year';
$field2 = $isNewBuildings ? 'buildings.completed_quarter' : 'building_completed_quarter';
$subMust = [];
$subMust[] = [
'range' => [
$field2 => [
'gte' => $quarter,
],
],
];
$subMust[] = [
'match' => [
$field1 => $year,
],
];
if ($isNewBuildings) {
$query['bool']['must']['bool']['should'][] = [
'nested' => [
'path' => 'buildings',
'query' => [
'bool' => [
'filter' => [
'range' => [
$field1 => [
'gt' => $year,
],
],
],
],
],
],
];
$query['bool']['must']['bool']['should'][] = [
'nested' => [
'path' => 'buildings',
'query' => [
'bool' => [
'must' => $subMust,
],
],
],
];
} else {
$query['bool']['must']['bool']['should'][] = [
'range' => [
$field1 => [
'gt' => $year,
],
],
];
$query['bool']['must']['bool']['should'][] = [
'bool' => [
'must' => $subMust,
],
];
}
}
if (!empty($district)) {
$field = $isNewBuildings ? 'city_district_id' : 'building_city_district_id';
$query['bool']['filter'][] = [
'terms' => [
$field => $district,
],
];
}
if (!empty($subway)) {
$field = $isNewBuildings ? 'metro_id' : 'building_metro_id';
$query['bool']['filter'][] = [
'terms' => [
$field => $subway,
],
];
}
if (!empty($areaFrom)) {
$field = $isNewBuildings ? 'buildings.flats.total_area' : 'total_area';
if ($isNewBuildings) {
$query['bool']['must'][] = [
'nested' => [
'path' => 'buildings.flats',
'query' => [
'bool' => [
'filter' => [
'range' => [
$field => [
'gte' => $areaFrom,
],
],
],
],
],
],
];
} else {
$query['bool']['filter'][] = [
'range' => [
$field => [
'gte' => $areaFrom,
],
],
];
}
}
if (!empty($areaTo)) {
$field = $isNewBuildings ? 'buildings.flats.total_area' : 'total_area';
if ($isNewBuildings) {
$query['bool']['must'][] = [
'nested' => [
'path' => 'buildings.flats',
'query' => [
'bool' => [
'filter' => [
'range' => [
$field => [
'lte' => $areaTo,
],
],
],
],
],
],
];
} else {
$query['bool']['filter'][] = [
'range' => [
$field => [
'lte' => $areaTo,
],
],
];
}
}
if (!empty($developerId)) {
$field = $isNewBuildings ? 'developer_id' : 'building_developer_id';
$query['bool']['filter'][] = [
'term' => [
$field => $developerId,
],
];
}
// TODO: По идее уже не нужно, т.к. выбор индекса зависит от категории
if (!empty($categoryId)) {
$field = $isNewBuildings ? 'category_id' : 'building_category_id';
$query['bool']['filter'][] = [
'match' => [
$field => $categoryId,
],
];
}
if (isset($isApartment)) {
$field = $isNewBuildings ? 'is_apartment' : 'building_is_apartment';
$query['bool']['filter'][] = [
'match' => [
$field => (bool) $isApartment,
],
];
}
if (!empty($phrase)) {
if ($isNewBuildings) {
$query['bool']['must'][] = [
'match' => [
'name' => $phrase,
],
];
} else {
$query['bool'] = [
'minimum_should_match' => 1,
'should' => [
[
"bool" => [
"must" => [
'match_phrase' => [
'building_name' => $phrase,
],
],
],
],
[
"bool" => [
"must" => [
'match_phrase' => [
'description' => $phrase,
],
],
],
],
],
];
}
}
if (!empty($options)) {
$field = $isNewBuildings ? 'buildings.flats.options.option_id' : 'options.option_id';
$path = $isNewBuildings ? 'buildings.flats.options' : 'options';
$optionsList = FlatOption::$db->select()->from(FlatOption::$table_name)->fetchAll();
$optionsDict = [];
foreach ($optionsList as $option) {
$optionsDict[$option->alias] = $option->id;
}
foreach ($options as &$option) {
$option = $optionsDict[$option];
}
foreach ($options as $optionId) {
$query['bool']['must'][] = [
'nested' => [
'path' => $path,
'query' => [
'bool' => [
'filter' => [
'terms' => [
$field => [$optionId],
],
],
],
],
],
];
}
}
if (!empty($orderBy)) {
switch ($orderBy) {
case 'random':
$params['body']['query'] = [
'function_score' => [
'random_score' => new stdClass,
'query' => $query,
],
];
break;
case 'completed':
$field1 = $isNewBuildings ? 'buildings.completed_year' : 'building_completed_year';
$field2 = $isNewBuildings ? 'buildings.completed_quarter' : 'building_completed_quarter';
$params['body']['query'] = $query;
if ($isNewBuildings) {
$params['body']['sort'] = [
[
$field1 => [
'mode' => 'avg',
'order' => $orderDirection,
'nested' => [
'path' => 'buildings',
],
],
],
[
$field2 => [
'mode' => 'avg',
'order' => $orderDirection,
'nested' => [
'path' => 'buildings',
],
],
],
];
} else {
$params['body']['sort'] = [
[$field1 => $orderDirection],
[$field2 => $orderDirection],
];
}
break;
default:
$params['body']['query'] = $query;
$params['body']['sort'] = [
[$orderBy => $orderDirection],
];
break;
}
} else {
$params['body']['query'] = $query;
}
if (empty($params['body']['query'])) {
unset($params['body']['query']);
}
$result2 = $elastic->client->search($params);
$paramsSource = $params;
$total = $result2['hits']['total']['value'];
$field = $isNewBuildings ? 'buildings.flats.options.option_id' : 'options.option_id';
$path = $isNewBuildings ? 'buildings.flats.options' : 'options';
$withOptions = [];
$optionsList = FlatOption::$db->select()->from(FlatOption::$table_name)->fetchAll();
if (!empty($optionsList)) {
foreach ($optionsList as $flatOption) {
//if (!empty($options) && in_array($flatOption->id, $options)) continue;
$optionQueryParam = $paramsSource;
$optionQueryParam['body']['query']['bool']['must'][] = [
'nested' => [
'path' => $path,
'query' => [
'bool' => [
'filter' => [
'terms' => [
$field => [$flatOption->id],
],
],
],
],
],
];
$tempResult = $elastic->client->search($optionQueryParam);
$withOptions[$flatOption->alias] = $tempResult['hits']['total']['value'];
}
}
if ($totalOnly) {
Ajax::success([
'total' => $total,
'withOptions' => $withOptions,
]);
} else {
$complexes = $result2['hits']['hits'];
foreach ($complexes as &$complex) {
$complex = (object) $complex['_source'];
$complex->category_id = isset($complex->category_id) ? $complex->category_id : $complex->building_category_id;
unset($complex->buildings);
}
Ajax::success([
'complexes' => $complexes,
'total' => $result2['hits']['total']['value'],
'params' => $params,
'withOptions' => $withOptions,
]);
}
}
public static function actionAllFlats($strict = true)
{
$price_up_to = Data::getVar('price_up_to', 0);
$rooms = Data::getVar('rooms', []);
$completedAt = Data::getVar('completed_at');
$completedAfter = Data::getVar('completed_after');
$district = Data::getVar('district', []);
$subway = Data::getVar('subway', []);
$areaFrom = Data::getVar('area_from');
$areaTo = Data::getVar('area_to');
$phrase = Data::getVar('phrase');
$options = Data::getVar('options', []);
$categoryId = Data::getVar('category_id', 1);
$isApartment = Data::getVar('is_apartment');
$limit = Data::getVar('limit', 20);
$offset = Data::getVar('offset', 0);
$orderBy = Data::getVar('order_by', false);
$orderDirection = Data::getVar('order_dir', 'DESC') == 'DESC' ? 'DESC' : 'ASC';
$developerId = (int) Data::getVar('developer_id', false);
$totalOnly = (bool) Data::getVar('total_only', false);
$sql = Complex::$db->select(
'f.id', 'f.price', 'f.rooms', 'f.total_area area', 'f.floor',
'b.floors', 'GROUP_CONCAT(DISTINCT(fo.alias) SEPARATOR ", ") options',
'b.name', 'b.latlng', 'c.name complexName', 'c.id complex_id',
'b.street_name street', 'cd.name city_district', 'b.category_id category_id',
'm.name metroStation', 'ml.name metroLineName', 'ml.color metroLineColor',
'b.completed_year', 'b.housing_number building_number'
)
->from(Flat::$table_name . ' f')
->join(Building::$table_name . ' b', 'f.building_id = b.id')
->join(Complex::$table_name . ' c', 'b.complex_id = c.id')
->joinLeft(CityDistricts::$table_name . ' cd', 'cd.id = c.city_district_id')
->joinLeft(Metro::$table_name . ' m', 'm.id = c.metro_id')
->joinLeft(MetroLine::$table_name . ' ml', 'ml.id = m.line_id')
->joinLeft(FlatOptionRelation::$table_name . ' fro', 'f.id = fro.flat_id')
->joinLeft(FlatOption::$table_name . ' fo', 'fo.id = fro.option_id')
// ->joinLeft(Attachment::$table_name . ' a', 'a.attachable_id = f.id AND a.attachable_type = "Flat" AND a.file_type = "plan"')
->where('f.published = 1')
->where('b.published = 1')
->where('c.published = 1')
->where('c.deleted_at IS NULL')
->where('b.deleted_at IS NULL')
->where('f.deleted_at IS NULL')
->where('b.category_id = ?', $categoryId)
->where('f.price > 0')
->group('f.id');
$sqlCount = Complex::$db->select('c.id')
->from(Flat::$table_name . ' f')
->join(Building::$table_name . ' b', 'f.building_id = b.id')
->join(Complex::$table_name . ' c', 'b.complex_id = c.id')
->joinLeft(CityDistricts::$table_name . ' cd', 'cd.id = c.city_district_id')
->joinLeft(Metro::$table_name . ' m', 'm.id = c.metro_id')
->joinLeft(MetroLine::$table_name . ' ml', 'ml.id = m.line_id')
->joinLeft(FlatOptionRelation::$table_name . ' fro', 'f.id = fro.flat_id')
->joinLeft(FlatOption::$table_name . ' fo', 'fo.id = fro.option_id')
->where('f.published = 1')
->where('b.published = 1')
->where('c.published = 1')
->where('c.deleted_at IS NULL')
->where('b.deleted_at IS NULL')
->where('f.deleted_at IS NULL')
->where('b.category_id = ?', $categoryId)
->where('f.price > 0')
->group('f.id');
if (!empty($orderBy)) {
switch ($orderBy) {
case 'completed':
$sql->order('b.completed_year', $orderDirection)
->order('b.completed_quarter', $orderDirection);
break;
default:
$sql->order($orderBy, $orderDirection);
break;
}
}
if (!empty($price_up_to)) {
$sql->where('f.price <= ? ', (int) $price_up_to);
$sqlCount->where('f.price <= ? ', (int) $price_up_to);
}
if (!empty($rooms)) {
$sql->where('f.rooms IN (?)', [$rooms]);
$sqlCount->where('f.rooms IN (?)', [$rooms]);
}
if (!empty($completedAt)) {
if ($completedAt == 'сдан') {
$year = date('Y');
$quarter = ceil(date('n', time()) / 3);
} else {
list($year, $quarter) = explode('-', $completedAt);
}
$sql->where('b.completed_year < ? OR (b.completed_year = ? AND b.completed_quarter <= ?)', [
$year,
$year,
$quarter,
]);
$sqlCount->where('b.completed_year < ? OR (b.completed_year = ? AND b.completed_quarter <= ?)', [
$year,
$year,
$quarter,
]);
}
if (!empty($completedAfter)) {
list($year, $quarter) = explode('-', $completedAfter);
$sql->where('b.completed_year > ? OR (b.completed_year = ? AND b.completed_quarter >= ?)', [$year, $year, $quarter]);
$sqlCount->where('b.completed_year > ? OR (b.completed_year = ? AND b.completed_quarter >= ?)', [$year, $year, $quarter]);
}
if (!empty($district)) {
$district = array_map(function ($item) {
return (int) $item;
}, $district);
$sql->where('b.city_district_id IN (?)', [$district]);
$sqlCount->where('b.city_district_id IN (?)', [$district]);
}
if (!empty($subway)) {
$sql->where('b.metro_id IN (?)', [$subway]);
$sqlCount->where('b.metro_id IN (?)', [$subway]);
}
if (!empty($areaFrom)) {
$sql->where('f.total_area >= ?', $areaFrom);
$sqlCount->where('f.total_area >= ?', $areaFrom);
}
if (!empty($areaTo)) {
$sql->where('f.total_area <= ?', $areaTo);
$sqlCount->where('f.total_area <= ?', $areaTo);
}
if (!empty($categoryId)) {
$sql->where('b.category_id = ?', $categoryId);
$sqlCount->where('b.category_id = ?', $categoryId);
}
if (isset($isApartment)) {
$sql->where('c.is_apartment = ?', (int) $isApartment);
$sqlCount->where('c.is_apartment = ?', (int) $isApartment);
}
if (!empty($developerId)) {
$sql->where('b.developer_id = ?', $developerId);
$sqlCount->where('b.developer_id = ?', $developerId);
}
if (!empty($phrase) && $categoryId == 1) {
$values = $wheres = [];
if ($strict === false) {
$phrase = explode(' ', $phrase);
foreach ($phrase as $word) {
if (mb_strlen($word) > 2) {
$values[] = ['%' . $word . '%'];
$values[] = ['%' . $word . '%'];
$values[] = ['%' . $word . '%'];
$wheres[] = 'c.name LIKE ? OR m.name LIKE ? OR cd.name LIKE ?';
}
}
} else {
$wheres[] = 'REPLACE ( c.name ,"\"", "") LIKE ? OR m.name LIKE ? OR cd.name LIKE ?';
$values = [['%' . $phrase . '%'], ['%' . $phrase . '%'], ['%' . $phrase . '%']];
}
$sql->where(implode(' OR ', $wheres), $values);
$sqlCount->where(implode(' OR ', $wheres), $values);
}
if (!empty($options)) {
$sql->where('fo.alias IN (?)', [$options]);
$sqlCount->where('fo.alias IN (?)', [$options]);
}
$sql->limit($limit, $offset);
$totalCount = count($sqlCount->fetchAll());
// if ($totalCount == 0 && $strict !== false) {
// self::actionFlats();
// }
if ($totalOnly) {
Ajax::success([
'total' => $totalCount,
]);
} else {
$flats = $sql->fetchAll();
foreach ($flats as &$flat) {
$plan = Attachment::getFlatPlan($flat->id);
$flat->plan = $plan->file;
$flat->plan_thumb = $plan->file_thumb;
}
Ajax::success([
'flats' => $flats,
'total' => $totalCount,
]);
}
}
public static function actionAllFlats2($strict = true)
{
$price_up_to = Data::getVar('price_up_to', 0);
$rooms = Data::getVar('rooms', []);
$completedAt = Data::getVar('completed_at');
$completedAfter = Data::getVar('completed_after');
$district = Data::getVar('district', []);
$subway = Data::getVar('subway', []);
$areaFrom = Data::getVar('area_from');
$areaTo = Data::getVar('area_to');
$phrase = Data::getVar('phrase');
$options = Data::getVar('options', []);
$categoryId = Data::getVar('category_id', 1);
$isApartment = Data::getVar('is_apartment');
$limit = Data::getVar('limit', 20);
$offset = Data::getVar('offset', 0);
$orderBy = Data::getVar('order_by', false);
$orderDirection = Data::getVar('order_dir', 'DESC') == 'DESC' ? 'desc' : 'asc';
$developerId = (int) Data::getVar('developer_id', false);
$totalOnly = (bool) Data::getVar('total_only', false);
$elastic = new Elastic();
$params = [
'index' => self::INDEX_FLATS,
'body' => [
'track_total_hits' => true,
'from' => $offset,
'size' => $limit,
'query' => [],
],
];
$query = [];
if (!empty($price_up_to)) {
$field = 'price';
$query['bool']['filter'][] = [
'range' => [
$field => [
'lte' => $price_up_to,
],
],
];
}
if (!empty($rooms)) {
$field = 'rooms';
foreach($rooms as $room) {
if (strpos($room, '+') !== false) {
$room = (int)$room;
$filter = [
'range' => [
$field => [
'gte' => $room,
],
],
];
} else {
$filter = [
'term' => [
$field => $room,
],
];
}
$query['bool']['should'][] = $filter;
$query['bool']['minimum_should_match'] = 1;
}
}
if (!empty($completedAt)) {
if ($completedAt == 'сдан') {
$year = date('Y');
$quarter = ceil(date('n', time()) / 3);
} else {
list($year, $quarter) = explode('-', $completedAt);
}
$field1 = 'building_completed_year';
$field2 = 'building_completed_quarter';
$query['bool']['should'][] = [
'range' => [
$field1 => [
'lt' => $year,
'gt' => 0,
],
],
];
$subMust = [];
$subMust[] = [
'range' => [
$field2 => [
'lt' => $quarter,
'gt' => 0,
],
],
];
$subMust[] = [
'match' => [
$field1 => $year,
],
];
$query['bool']['should'][] = [
'bool' => [
'must' => $subMust,
],
];
}
if (!empty($completedAfter)) {
list($year, $quarter) = explode('-', $completedAfter);
$field1 = 'building_completed_year';
$field2 = 'building_completed_quarter';
$query['bool']['should'][] = [
'range' => [
$field1 => [
'gt' => $year,
],
],
];
$subMust = [];
$subMust[] = [
'range' => [
$field2 => [
'gte' => $quarter,
],
],
];
$subMust[] = [
'match' => [
$field1 => $year,
],
];
$query['bool']['should'][] = [
'bool' => [
'must' => $subMust,
],
];
}
if (!empty($district)) {
$field = 'building_city_district_id';
$query['bool']['filter'][] = [
'terms' => [
$field => $district,
],
];
}
if (!empty($subway)) {
$field = 'building_metro_id';
$query['bool']['filter'][] = [
'terms' => [
$field => $subway,
],
];
}
if (!empty($areaFrom)) {
$field = 'total_area';
$query['bool']['filter'][] = [
'range' => [
$field => [
'gte' => $areaFrom,
],
],
];
}
if (!empty($areaTo)) {
$field = 'total_area';
$query['bool']['filter'][] = [
'range' => [
$field => [
'lte' => $areaTo,
],
],
];
}
if (!empty($developerId)) {
$field = 'building_developer_id';
$query['bool']['filter'][] = [
'term' => [
$field => $developerId,
],
];
}
if (!empty($categoryId)) {
$field = 'building_category_id';
$query['bool']['filter'][] = [
'match' => [
$field => $categoryId,
],
];
}
if (isset($isApartment)) {
$field = 'building_is_apartment';
$query['bool']['filter'][] = [
'match' => [
$field => (bool) $isApartment,
],
];
}
if (!empty($phrase) && $categoryId == 1) {
$query['bool']['must'][] = [
'match' => [
'complex_name' => $phrase,
],
];
}
if (!empty($options)) {
$field = 'options.option_id';
$path = 'options';
$optionsList = FlatOption::$db->select()->from(FlatOption::$table_name)->fetchAll();
$optionsDict = [];
foreach ($optionsList as $option) {
$optionsDict[$option->alias] = $option->id;
}
foreach ($options as &$option) {
$option = $optionsDict[$option];
}
foreach ($options as $optionId) {
$query['bool']['must'][] = [
'nested' => [
'path' => $path,
'query' => [
'bool' => [
'filter' => [
'terms' => [
$field => [$optionId],
],
],
],
],
],
];
}
}
if (!empty($orderBy)) {
switch ($orderBy) {
case 'completed':
$field1 = 'building_completed_year';
$field2 = 'building_completed_quarter';
$params['body']['query'] = $query;
$params['body']['sort'] = [
[$field1 => $orderDirection],
[$field2 => $orderDirection],
];
break;
default:
$params['body']['query'] = $query;
$params['body']['sort'] = [
[$orderBy => $orderDirection],
];
break;
}
} else {
$params['body']['query'] = $query;
}
if (empty($params['body']['query'])) {
unset($params['body']['query']);
}
$paramsSource = $params;
$result2 = $elastic->client->search($params);
$field = 'options.option_id';
$path = 'options';
$withOptions = [];
$optionsList = FlatOption::$db->select()->from(FlatOption::$table_name)->fetchAll();
if (!empty($optionsList)) {
foreach ($optionsList as $flatOption) {
//if (!empty($options) && in_array($flatOption->id, $options)) continue;
$optionQueryParam = $paramsSource;
$optionQueryParam['body']['query']['bool']['must'][] = [
'nested' => [
'path' => $path,
'query' => [
'bool' => [
'filter' => [
'terms' => [
$field => [$flatOption->id],
],
],
],
],
],
];
$tempResult = $elastic->client->search($optionQueryParam);
$withOptions[$flatOption->alias] = $tempResult['hits']['total']['value'];
}
}
if ($totalOnly) {
Ajax::success([
'total' => $result2['hits']['total']['value'],
'withOptions' => $withOptions,
]);
} else {
$flats = $result2['hits']['hits'];
foreach ($flats as &$flat) {
$flat = (object) $flat['_source'];
}
Ajax::success([
'flats' => $flats,
'total' => $result2['hits']['total']['value'],
'params' => $params,
'withOptions' => $withOptions,
]);
}
}
public static function actionFlats()
{
$price_up_to = Data::getVar('price_up_to', 0);
$rooms = Data::getVar('rooms', []);
$floorFrom = Data::getVar('floor_from');
$floorTo = Data::getVar('floor_to');
$buildingId = Data::getVar('building_id', []);
$complexId = Data::getVar('complex_id', []);
$options = Data::getVar('options', []);
$categoryId = Data::getVar('category_id', 0);
$isApartment = Data::getVar('is_apartment');
$limit = Data::getVar('limit', 20);
$offset = Data::getVar('offset', 0);
$orderBy = Data::getVar('order_by', 'price');
$orderDirection = Data::getVar('order_dir', 'DESC') == 'DESC' ? 'DESC' : 'ASC';
$totalOnly = (bool) Data::getVar('total_only', false);
$notFlatId = Data::getVar('not', false);
$sql = Complex::$db->select(
'f.id, f.price', 'f.rooms', 'f.total_area area', 'f.floor',
'b.floors', 'GROUP_CONCAT(DISTINCT(fo.alias) SEPARATOR ", ") options',
'ANY_VALUE(a.file) plan, ANY_VALUE(a.file_thumb) plan_thumb, b.complex_id'
)
->from(Flat::$table_name . ' f')
->join(Building::$table_name . ' b', 'b.id = f.building_id')
->joinLeft(FlatOptionRelation::$table_name . ' fro', 'f.id = fro.flat_id')
->joinLeft(FlatOption::$table_name . ' fo', 'fo.id = fro.option_id')
->joinLeft(Attachment::$table_name . ' a', 'a.attachable_id = f.id AND a.attachable_type = "Flat" AND a.file_type = "plan"')
->where('f.price > 0')
->where('f.published = 1')
->where('b.published = 1')
->where('b.deleted_at IS NULL')
->where('f.deleted_at IS NULL')
->group('f.id');
if (!empty($orderBy)) {
switch ($orderBy) {
case 'random':
$sql->order('RAND()');
break;
case 'completed':
$sql->order('b.completed_year', $orderDirection)
->order('b.completed_quarter', $orderDirection);
break;
default:
$sql->order('f.' . $orderBy, $orderDirection);
break;
}
}
$sqlCount = Complex::$db->select('f.id')
->from(Flat::$table_name . ' f')
->join(Building::$table_name . ' b', 'b.id = f.building_id')
->joinLeft(FlatOptionRelation::$table_name . ' fro', 'f.id = fro.flat_id')
->joinLeft(FlatOption::$table_name . ' fo', 'fo.id = fro.option_id')
->where('f.price > 0')
->where('f.published = 1')
->where('b.published = 1')
->where('b.deleted_at IS NULL')
->where('f.deleted_at IS NULL')
->group('f.id');
if (!empty($price_up_to)) {
$sql->where('f.price <= ? ', (int) $price_up_to);
$sqlCount->where('f.price <= ? ', (int) $price_up_to);
}
if (!empty($rooms)) {
$sql->where('f.rooms IN (?)', [$rooms]);
$sqlCount->where('f.rooms IN (?)', [$rooms]);
}
if (!empty($floorFrom)) {
$sql->where('f.floor >= ?', $floorFrom);
$sqlCount->where('f.floor >= ?', $floorFrom);
}
if (!empty($floorTo)) {
$sql->where('f.floor <= ?', $floorTo);
$sqlCount->where('f.floor <= ?', $floorTo);
}
if (!empty($buildingId)) {
$sql->where('f.building_id IN (?)', [$buildingId]);
$sqlCount->where('f.building_id IN (?)', [$buildingId]);
}
if (!empty($complexId)) {
if (is_numeric($complexId)) {
$sql->where('b.complex_id IN (?)', [$complexId]);
$sqlCount->where('b.complex_id IN (?)', [$complexId]);
} else {
$sql->join(Complex::$table_name . ' c', 'c.id = b.complex_id')
->where('c.alias IN (?)', [$complexId]);
$sqlCount->join(Complex::$table_name . ' c', 'c.id = b.complex_id')
->where('c.alias IN (?)', [$complexId]);
}
}
if (!empty($options)) {
$sql->where('fo.alias IN (?)', [$options]);
$sqlCount->where('fo.alias IN (?)', [$options]);
}
if (!empty($categoryId)) {
$sql->where('b.category_id = ?', $categoryId);
$sqlCount->where('b.category_id <= ?', $categoryId);
}
if (isset($isApartment)) {
$sql->where('c.is_apartment = ?', (int) $isApartment);
$sqlCount->where('c.is_apartment = ?', (int) $isApartment);
}
if ($notFlatId) {
$sql->where('f.id != ?', $notFlatId);
$sqlCount->where('f.id != ?', $notFlatId);
}
$sql->limit($limit, $offset);
$totalCount = count($sqlCount->fetchAll());
if ($totalOnly) {
Ajax::success([
'total' => $totalCount,
]);
} else {
$flats = $sql->fetchAll();
foreach ($flats as &$flat) {
$gallery = Attachment::getFlatGallery($flat->id);
$flat->gallery = [];
$flat->gallery_thumbs = [];
foreach ($gallery as $image) {
$flat->gallery[] = $image->file;
$flat->gallery_thumbs[] = $image->file_thumb;
}
}
Ajax::success([
'flats' => $flats,
'total' => $totalCount,
]);
}
}
public static function actionFlats2()
{
$price_up_to = Data::getVar('price_up_to', 0);
$rooms = Data::getVar('rooms', []);
$floorFrom = Data::getVar('floor_from');
$floorTo = Data::getVar('floor_to');
$buildingId = Data::getVar('building_id', []);
$complexId = Data::getVar('complex_id', []);
$options = Data::getVar('options', []);
$categoryId = Data::getVar('category_id', 0);
$isApartment = Data::getVar('is_apartment');
$limit = Data::getVar('limit', 20);
$offset = Data::getVar('offset', 0);
$orderBy = Data::getVar('order_by', 'price');
$orderDirection = Data::getVar('order_dir', 'DESC') == 'DESC' ? 'DESC' : 'ASC';
$totalOnly = (bool) Data::getVar('total_only', false);
$notFlatId = Data::getVar('not', false);
$elastic = new Elastic();
$params = [
'index' => self::INDEX_FLATS,
'body' => [
'track_total_hits' => true,
'from' => $offset,
'size' => $limit,
'query' => [],
],
];
$query = [];
if (!empty($price_up_to)) {
$field = 'price';
$query['bool']['filter'][] = [
'range' => [
$field => [
'lte' => $price_up_to,
],
],
];
}
if (!empty($rooms)) {
$field = 'rooms';
$query['bool']['filter'][] = [
'terms' => [
$field => $rooms,
],
];
}
if (!empty($floorFrom)) {
$field = 'floor';
$query['bool']['filter'][] = [
'range' => [
$field => [
'gte' => $floorFrom,
],
],
];
}
if (!empty($floorTo)) {
$field = 'floor';
$query['bool']['filter'][] = [
'range' => [
$field => [
'lte' => $floorTo,
],
],
];
}
if (!empty($buildingId)) {
$field = 'building_id';
$query['bool']['filter'][] = [
'match' => [
$field => $buildingId,
],
];
}
if (!empty($complexId)) {
if (is_numeric($complexId)) {
$field = 'complex_id';
$query['bool']['filter'][] = [
'match' => [
$field => (int) $complexId,
],
];
} else {
$field = 'complex_name';
$query['bool']['filter'][] = [
'terms' => [
$field => $complexId,
],
];
}
}
if (isset($isApartment)) {
$query['bool']['filter'][] = [
'match' => [
'is_apartment' => (bool) $isApartment,
],
];
}
if (!empty($options)) {
$field = 'options.option_id';
$path = 'options';
$optionsList = FlatOption::$db->select()->from(FlatOption::$table_name)->fetchAll();
$optionsDict = [];
foreach ($optionsList as $option) {
$optionsDict[$option->alias] = $option->id;
}
foreach ($options as &$option) {
$option = $optionsDict[$option];
}
$query['bool']['must'][] = [
'nested' => [
'path' => $path,
'query' => [
'bool' => [
'filter' => [
'terms' => [
$field => $options,
],
],
],
],
],
];
}
if (!empty($categoryId)) {
$field = 'building_category_id';
$query['bool']['filter'][] = [
'match' => [
$field => $categoryId,
],
];
}
if (!empty($notFlatId)) {
$field = 'id';
$query['bool']['must_not'][] = [
'match' => [
$field => $notFlatId,
],
];
}
if (!empty($orderBy)) {
switch ($orderBy) {
case 'random':
$params['body']['query'] = [
'function_score' => [
'random_score' => new stdClass,
'query' => $query,
],
];
break;
case 'completed':
$field1 = $categoryId == 1 ? 'buildings.completed_year' : 'building_completed_year';
$field2 = $categoryId == 1 ? 'buildings.completed_quarter' : 'building_completed_quarter';
$params['body']['query'] = $query;
if ($categoryId == 1) {
$params['body']['sort'] = [
[
$field1 => [
'mode' => 'avg',
'order' => $orderDirection,
'nested' => [
'path' => 'buildings',
],
],
],
[
$field2 => [
'mode' => 'avg',
'order' => $orderDirection,
'nested' => [
'path' => 'buildings',
],
],
],
];
} else {
$params['body']['sort'] = [
[$field1 => $orderDirection],
[$field2 => $orderDirection],
];
}
break;
default:
$params['body']['query'] = $query;
$params['body']['sort'] = [
[$orderBy => $orderDirection],
];
break;
}
} else {
$params['body']['query'] = $query;
}
if (empty($params['body']['query'])) {
unset($params['body']['query']);
}
$result2 = $elastic->client->search($params);
if ($totalOnly) {
Ajax::success([
'total' => $result2['hits']['total']['value'],
]);
} else {
$flats = $result2['hits']['hits'];
foreach ($flats as &$flat) {
$flat = (object) $flat['_source'];
$flat->plan_thumb = $flat->plan_thumb ?? $flat->plan;
}
Ajax::success([
'flats' => $flats,
'total' => $result2['hits']['total']['value'],
'params' => $params,
]);
}
}
public static function actionTest()
{
$categoryId = 1;
$elastic = new Elastic();
$params = [
'index' => self::INDEX_COMPLEXES,
'body' => [
'track_total_hits' => true,
'from' => 0,
'size' => 10,
'query' => [],
],
];
$query = [];
if (!empty($categoryId)) {
$query['bool']['filter'][] = [
'match' => [
'category_id' => $categoryId,
],
];
}
$query['bool']['filter'][] = [
'match' => [
'is_apartment' => false,
],
];
$params['body']['query'] = $query;
$result2 = $elastic->client->search($params);
$data = $result2['hits']['hits'];
foreach ($data as &$item) {
$item = $item['_source'];
}
$data['params'] = $params;
Ajax::success($data);
}
}