<?php
namespace Troika\RealEstateBundle\Model;
use Troika\MainBundle\Entity\Lot;
class LotModel
{
public function __construct($em, $paginator)
{
$this->_em = $em;
$this->paginator = $paginator;
}
protected $_em;
private function _setQueryCountry($query, $country)
{
return $query
->andWhere('lot.country IN (:country)')
->setParameter('country', $country);
}
private function _setQueryCountryCity($query, $countryCity)
{
return $query
->andWhere('lot.countryCity IN (:countryCity)')
->setParameter('countryCity', $countryCity);
}
private function _setQueryDistance($query, $min, $max)
{
if ($max < 50) {
$query
->andWhere('lot.mkadDistance <= :distance_max')
->setParameter('distance_max', $max);
}
if ($min > 0) {
$query
->andWhere('lot.mkadDistance >= :distance_min')
->setParameter('distance_min', $min);
}
return $query;
}
private function _setQueryDistrict($query, $district)
{
return $query
->andWhere('lot.district3 IN (:district)')
->setParameter('district', $district);
}
private function _setQueryHighway($query, $highway)
{
if ((is_countable($highway) ? count($highway) : 0) > 0) {
return $query
->andWhere('lot.highway IN (:highway)')
->setParameter('highway', $highway);
}
}
private function _setQueryHomesteadArea($query, $min, $max)
{
if ($max < 150) {
$query->andWhere('lot.homesteadArea <= :homesteadArea_max')->setParameter('homesteadArea_max', $max);
}
if ($min > 0) {
$query->andWhere('lot.homesteadArea >= :homesteadArea_min')->setParameter('homesteadArea_min', $min);
}
return $query;
}
private function _setQueryPrice($query, $min, $max)
{
if ($max < 15000 && $min <= 0) {
$query
->andWhere('lot.dollars <= :price_max')
->andWhere('lot.dollars > :m')
->setParameter('price_max', $max * 1000)
->setParameter('m', 1);
}
if ($min > 0 && $max >= 15000) {
//->andWhere('lot.dollars >= :price_min')
$query
->andWhere(
$query->expr()->orX(
$query->expr()->gte('lot.dollars', $min * 1000)
)
);
}
if ($min > 0 && $max < 15000) {
//->andWhere('lot.dollars >= :price_min')
$query
->andWhere(
$query->expr()->orX(
$query->expr()->between('lot.dollars', $min * 1000, (int)($max * 1000))
)
);
}
return $query;
}
private function _setQueryRooms($query, $min, $max)
{
if ($max < 5) {
$query
->andWhere('lot.rooms <= :rooms_max')
->setParameter('rooms_max', $max);
}
if ($min > 1) {
$query
->andWhere('lot.rooms >= :rooms_min')
->setParameter('rooms_min', $min);
}
return $query;
}
private function _setQueryRoomsT($query, $rooms)
{
$query
->andWhere('lot.rooms between :rooms_min and :rooms_max')
->setParameter('rooms_min', !empty($rooms['min']) ? $rooms['min'] : 0)
->setParameter('rooms_max', !empty($rooms['max']) ? $rooms['max'] : 10000);
return $query;
}
private function _setQuerySettlement($query, $settlement)
{
return $query
->andWhere('lot.settlement IN (:settlement)')
->setParameter('settlement', $settlement);
}
private function _setQuerySpace($query, $min, $max)
{
$query->andWhere('lot.area <> 0');
if ($max < 100000) {
$query
->andWhere('lot.area <= :space_max')
->setParameter('space_max', $max);
}
if ($min > 0) {
$query
->andWhere('lot.area >= :space_min')
->setParameter('space_min', $min);
}
return $query;
}
private function _setQuerySubway($query, $subway)
{
return $query
->andWhere('lot.subway2 IN (:subway)')
->setParameter('subway', $subway);
}
private function _setQueryTag($query, $tag)
{
return $query
->innerJoin('lot.tags', 'tags')
->andWhere('tags.id = :tag')
->setParameter('tag', $tag);
}
private function _setQueryType($query, $type)
{
return $query
->andWhere('lot.typeHouse IN (:type)')
->setParameter('type', $type);
}
private function _setQueryTypeLot($query, $type)
{
return $query
->andWhere('lot.typeLot IN (:type)')
->setParameter('type', $type);
}
public function _setQueryRepair($query, $repair)
{
return $query
->andWhere('lot.repair = :repair')
->setParameter('repair', $repair);
}
public function _setQuerySale($query, $sale)
{
return $query
->andWhere('lot.oldPrice > 0');
}
public function _setQueryStage($query, $stage)
{
return $query
->andWhere('lot.stageHouse IN (:stage)')
->setParameter('stage', $stage);
}
public function _setQueryText($query, $text)
{
$text = str_replace("%", "\%", $text);
//if (!is_numeric($text)) {
$query = $query
->innerJoin("TroikaMainBundle:Address", "address", "WITH", "address.id = lot.address")
->innerJoin("TroikaMainBundle:Street", "street", "WITH", "street.id = address.street")
->leftJoin("TroikaMainBundle:Complex", "complex", "WITH", "complex.id = lot.complex")
#->leftJoin("TroikaMainBundle:BusinessCenter", "business", "WITH", "business.id = lot.business")
->leftJoin("TroikaMainBundle:CountryCity", "cities", "WITH", "cities.id = lot.countryCity")
->leftJoin("TroikaMainBundle:Settlement", "settlement", "WITH", "settlement.id = lot.settlement")
->leftJoin("TroikaMainBundle:District3", "area", "WITH", "area.id = lot.district3")
#->leftJoin("TroikaMainBundle:Subway2", "subway2", "WITH", "subway2.id = lot.subway2")
->andWhere(
$query->expr()->orX(
$query->expr()->like('lot.id', ':text'),
$query->expr()->like('address.address', ':text'),
$query->expr()->like('street.name', ':text'),
$query->expr()->like('complex.name', ':text'),
$query->expr()->like('settlement.name', ':text'),
$query->expr()->like('cities.name', ':text'),
#$query->expr()->like('business.name', ':text'),
$query->expr()->like('area.name', ':text')
#$query->expr()->like('subway2.name', ':text')
)
)
->setParameter('text', '%' . $text . '%');
/*} else {
$query = $query
->andWhere('lot.id = :id')
->setParameter('id', $text);
}*/
return $query;
}
public function _setQueryText2($query, $text)
{
if (!is_numeric($text)) {
$query = $query
->leftJoin("TroikaMainBundle:Address", "address", "WITH", "address.id = lot.address")
->leftJoin("TroikaMainBundle:Settlement", "settlement", "WITH", "settlement.id = lot.settlement")
->leftJoin("TroikaMainBundle:Highway", "highway", "WITH", "highway.id = lot.highway")
->andWhere(
$query->expr()->orX(
$query->expr()->like('address.address', ':text'),
$query->expr()->like('settlement.name', ':text'),
$query->expr()->like('highway.name', ':text')
)
)
->setParameter('text', '%' . $text . '%');
} else {
$query = $query
->andWhere('lot.id = :id')
->setParameter('id', $text);
}
return $query;
}
public function _setQueryText3($query, $text)
{
if (!is_numeric($text)) {
$query = $query
->leftJoin("TroikaMainBundle:Address", "address", "WITH", "address.id = lot.address")
->innerJoin("TroikaMainBundle:Street", "street", "WITH", "street.id = address.street")
->leftJoin("TroikaMainBundle:BusinessCenter", "business", "WITH", "business.id = lot.business")
->andWhere(
$query->expr()->orX(
$query->expr()->like('street.name', ':text'),
$query->expr()->like('address.address', ':text'),
$query->expr()->like('business.name', ':text')
)
)
->setParameter('text', '%' . $text . '%');
} else {
$query = $query
->andWhere('lot.id = :id')
->setParameter('id', $text);
}
return $query;
}
public function _setQueryText4($query, $text)
{
if (!is_numeric($text)) {
$query = $query
->leftJoin("TroikaMainBundle:Address", "address", "WITH", "address.id = lot.address")
->innerJoin("TroikaMainBundle:Street", "street", "WITH", "street.id = address.street")
->leftJoin("TroikaMainBundle:Country", "country", "WITH", "country.id = lot.country")
->leftJoin("TroikaMainBundle:CountryCity", "city", "WITH", "city.id = lot.countryCity")
->andWhere(
$query->expr()->orX(
$query->expr()->like('street.name', ':text'),
$query->expr()->like('address.address', ':text'),
$query->expr()->like('country.name', ':text'),
$query->expr()->like('city.name', ':text')
)
)
->setParameter('text', '%' . $text . '%');
} else {
$query = $query
->andWhere('lot.id = :id')
->setParameter('id', $text);
}
return $query;
}
public function getByBid($id)
{
return $this
->_em
->getRepository('TroikaMainBundle:Lot')
->find($id);
}
public function getByCityFilter($filter)
{
if (!empty($filter["sorter"]) && !empty($filter["sorter"]["order"])
&& $filter["sorter"]["order"] === "price") {
$filter["sorter"]["order"] = 'dollars';
}
// var_dump($filter["sorter"]["order"], $filter["sorter"]["direction"]);die;
$query = $this
->_em
->getRepository('TroikaMainBundle:Lot')
->createQueryBuilder('lot')
->where('lot.typeOperation = :operation')
->andWhere('lot.showLot = 1')
->andWhere('lot.typeLot = :typeLot')
->orderBy('lot.' . $filter["sorter"]["order"], $filter["sorter"]["direction"])
->setParameter('operation', $filter['operation'])
->setParameter('typeLot', $filter['lot']);
if ((!empty($filter["sorter"]["order"])) && $filter["sorter"]["order"] !== "date") {
// $query->andWhere('lot.price <> 0');
}
if ((!empty($filter['price']['max']) && ($filter['price']['max'] < 15000))
|| (!empty($filter['price']['min']) && ($filter['price']['min'] > 0))) {
$query = $this->_setQueryPrice($query, $filter['price']['min'], $filter['price']['max']);
} else {
if ($filter["sorter"]["order"] === "dollars") {
$query = $this->_setQueryPrice($query, 1, 15000);
}
}
if (!empty($filter['text'])) {
$query = $this->_setQueryText($query, $filter['text']);
}
if (isset($filter['repair'])) {
$query = $this->_setQueryRepair($query, $filter['repair']);
}
if (!empty($filter['rooms'])) {
$arr = array();
$arr['min'] = $filter['rooms'];
$arr['max'] = $filter['rooms'];
$query = $this->_setQueryRoomsT($query, $arr);
/*$query = $this->_setQueryRoomsT($query, $filter['rooms']);*/
}
if ((!empty($filter['space']['max']) && ($filter['space']['max'] < 100000))
|| (!empty($filter['space']['min']) && ($filter['space']['min'] > 0))) {
$query = $this->_setQuerySpace($query, $filter['space']['min'], $filter['space']['max']);
}
if (!empty($filter['district'])) {
$query = $this->_setQueryDistrict($query, $filter['district']);
}
if (!empty($filter['tag'])) {
$query = $this->_setQueryTag($query, $filter['tag'][0]);
}
if (!empty($filter['subway'])) {
$query = $this->_setQuerySubway($query, $filter['subway']);
}
if (!empty($filter['type'])) {
$query = $this->_setQueryType($query, $filter['type']);
}
if (!empty($filter['stage'])) {
$query = $this->_setQueryStage($query, $filter['stage']);
}
if (!empty($filter['sale'])) {
$query = $this->_setQuerySale($query, $filter['sale']);
}
$queryCount = clone $query;
return [
'count' => $queryCount->select('count(lot)')->getQuery()->getResult()[0][1],
'lotsAddress' => $query->getQuery()->getResult(),
'lots' => $query->setMaxResults(10)->setFirstResult(($filter['page'] - 1) * 10)->getQuery()->getResult(),
];
}
public function getByCommercialFilter($filter)
{
if ($filter["sorter"]["order"] === "price") {
$filter["sorter"]["order"] = 'dollars';
}
$query = $this
->_em
->getRepository('TroikaMainBundle:Lot')
->createQueryBuilder('lot')
->where('lot.typeOperation = :operation')
->andWhere('lot.showLot = 1')
->andWhere('lot.typeLot = :typeLot')
->orderBy('lot.' . $filter["sorter"]["order"], $filter["sorter"]["direction"])
->setParameter('operation', $filter['operation'])
->setParameter('typeLot', $filter['lot']);
if ($filter["sorter"]["order"] != "date") {
// $query->andWhere('lot.price <> 0');
}
// var_dump($filter); die;
if ($filter['price']['max'] < 15000 || $filter['price']['min'] > 0) {
$query = $this->_setQueryPrice($query, $filter['price']['min'], $filter['price']['max']);
} else {
if ($filter["sorter"]["order"] == "dollars") {
$query = $this->_setQueryPrice($query, 1, 15000);
}
}
if ($filter['space']['max'] < 100000 || $filter['space']['min'] > 0) {
$query = $this->_setQuerySpace($query, $filter['space']['min'], $filter['space']['max']);
}
if (!empty($filter['tag'])) {
$query = $this->_setQueryTag($query, $filter['tag'][0]);
}
if ($filter['text']) {
$query = $this->_setQueryText3($query, $filter['text']);
}
if (!empty($filter['district'])) {
$query = $this->_setQueryDistrict($query, $filter['district']);
}
if (!empty($filter['subway'])) {
$query = $this->_setQuerySubway($query, $filter['subway']);
}
if (!empty($filter['type'])) {
$query = $this->_setQueryType($query, $filter['type']);
}
if (!empty($filter['stage'])) {
$query = $this->_setQueryStage($query, $filter['stage']);
}
if (!empty($filter['sale'])) {
$query = $this->_setQuerySale($query, $filter['sale']);
}
if (isset($filter['repair'])) {
$query = $this->_setQueryRepair($query, $filter['repair']);
}
$queryCount = clone $query;
return [
'count' => $queryCount->select('count(lot)')->getQuery()->getResult()[0][1],
'lotsAddress' => $query->getQuery()->getResult(),
'lots' => $query->setMaxResults(10)->setFirstResult(($filter['page'] - 1) * 10)->getQuery()->getResult(),
];
}
public function getByFilter($filter)
{
$q = $this
->_em
->getRepository('TroikaMainBundle:Lot')
->createQueryBuilder('lot')
->select('lot')
->where('lot.typeLot = :typeLot')
->andWhere('lot.typeOperation = :typeOperation')
->andWhere('lot.showLot = 1')
->setParameter('typeOperation', $filter["typeoperation"])
->setParameter('typeLot', $filter["typelot"]);
if ($filter['space'] && ($filter['space']['max'] < 100000 || $filter['space']['min'] > 0)) {
$q = $this->_setQuerySpace($q, $filter['space']['min'], $filter['space']['max']);
}
if ($filter['price'] && ($filter['price']['max'] < 15000 || $filter['price']['min'] > 0)) {
$q = $this->_setQueryPrice($q, $filter['price']['min'], $filter['price']['max']);
}
if ($filter['homestead'] && ($filter['homestead']['max'] < 150 || $filter['homestead']['min'] > 0)) {
$q = $this->_setQueryHomesteadArea(
$q,
$filter['homestead']['min'],
$filter['homestead']['max']
);
}
if ($filter['rooms'] && ($filter['rooms']['max'] <= 5 || $filter['rooms']['min'] >= 1)) {
$q = $this->_setQueryRooms($q, $filter['rooms']['min'], $filter['rooms']['max']);
}
if ($filter['subway']) {
$q = $this->_setQuerySubway($q, $filter['subway']);
}
if ($filter['stage']) {
$q = $this->_setQueryStage($q, $filter['stage']);
}
if ($filter['highway']) {
$q = $this->_setQueryHighway($q, $filter['highway']);
}
if ($filter['settlement']) {
$q = $this->_setQuerySettlement($q, $filter['settlement']);
}
if ($filter['type']) {
$q = $this->_setQueryType($q, $filter['type']);
}
if ($filter['district']) {
$q = $this->_setQueryDistrict($q, $filter['district']);
}
if (isset($filter['repair'])) {
$q = $this->_setQueryRepair($q, $filter['repair']);
}
return $q
->getQuery()
->getResult();
}
public function getByForeignFilter($filter)
{
if ($filter["sorter"]["order"] === "price") {
$filter["sorter"]["order"] = 'dollars';
}
$query = $this
->_em
->getRepository('TroikaMainBundle:Lot')
->createQueryBuilder('lot')
->where('lot.typeOperation = :operation')
->andWhere('lot.showLot = 1')
->andWhere('lot.typeLot = :typeLot')
->orderBy('lot.' . $filter["sorter"]["order"], $filter["sorter"]["direction"])
->setParameter('operation', $filter['operation'])
->setParameter('typeLot', $filter['lot']);
if ($filter["sorter"]["order"] !== "date") {
// $query->andWhere('lot.price <> 0');
}
if ($filter['space']['max'] < 100000 || $filter['space']['min'] > 0) {
$query = $this->_setQuerySpace($query, $filter['space']['min'], $filter['space']['max']);
}
// var_dump($filter); die;
if ($filter['price']['max'] < 15000 || $filter['price']['min'] > 0) {
$query = $this->_setQueryPrice($query, $filter['price']['min'], $filter['price']['max']);
} else {
if ($filter["sorter"]["order"] == "dollars") {
$query = $this->_setQueryPrice($query, 1, 15000);
}
}
if (!empty($filter['tag'])) {
$query = $this->_setQueryTag($query, $filter['tag'][0]);
}
if (!empty($filter['text'])) {
$query = $this->_setQueryText4($query, $filter['text']);
}
if (!empty($filter['country'])) {
$query = $this->_setQueryCountry($query, $filter['country']);
}
if (!empty($filter['countryCity'])) {
$query = $this->_setQueryCountryCity($query, $filter['countryCity']);
}
if (!empty($filter['type'])) {
$query = $this->_setQueryType($query, $filter['type']);
}
if (!empty($filter['sale'])) {
$query = $this->_setQuerySale($query, $filter['sale']);
}
if (isset($filter['repair'])) {
$query = $this->_setQueryRepair($query, $filter['repair']);
}
if (!empty($filter['rooms'])) {
$arr = array();
$arr['min'] = $filter['rooms'];
$arr['max'] = $filter['rooms'];
$query = $this->_setQueryRoomsT($query, $arr);
/*$query = $this->_setQueryRoomsT($query, $filter['rooms']);*/
}
$queryCount = clone $query;
return [
'count' => $queryCount->select('count(lot)')->getQuery()->getResult()[0][1],
'lotsAddress' => $query->getQuery()->getResult(),
'lots' => $query->setMaxResults(10)->setFirstResult(($filter['page'] - 1) * 10)->getQuery()->getResult(),
];
}
public function getBySpecialFilter($filter)
{
if ($filter["sorter"]["order"] == "price") {
$filter["sorter"]["order"] = 'dollars';
}
$query = $this
->_em
->getRepository('TroikaMainBundle:Lot')
->createQueryBuilder('lot')
->where('lot.exclusive = :exclusive')
->andWhere('lot.showLot = 1')
->andWhere('lot.typeLot <> 5')
->orderBy('lot.' . $filter["sorter"]["order"], $filter["sorter"]["direction"])
->setParameter('exclusive', true);
if (!empty($filter['type'])) {
$query = $this->_setQueryTypeLot($query, $filter['type']);
}
if (isset($filter['repair'])) {
$query = $this->_setQueryRepair($query, $filter['repair']);
}
$queryCount = clone $query;
return [
'count' => $queryCount->select('count(lot)')->getQuery()->getResult()[0][1],
'lotsAddress' => $query->getQuery()->getResult(),
'lots' => $query->setMaxResults(10)->setFirstResult(($filter['page'] - 1) * 10)->getQuery()->getResult(),
];
}
public function getBySuburbanFilter($filter)
{
if ($filter["sorter"]["order"] === "price") {
$filter["sorter"]["order"] = 'dollars';
}
$query = $this
->_em
->getRepository('TroikaMainBundle:Lot')
->createQueryBuilder('lot')
->where('lot.typeOperation = :operation')
->andWhere('lot.showLot = 1')
->andWhere('lot.typeLot = :typeLot')
->orderBy('lot.' . $filter["sorter"]["order"], $filter["sorter"]["direction"])
->setParameter('operation', $filter['operation'])
->setParameter('typeLot', $filter['lot']);
if (isset($filter['repair'])) {
$query = $this->_setQueryRepair($query, $filter['repair']);
}
if (
(!empty($filter['space']['max'])
&& $filter['space']['max'] < 100000)
|| (!empty($filter['space']['min'])
&& $filter['space']['min'] > 0)) {
$query->andWhere('lot.area <> 0');
$query = $this->_setQuerySpace($query, $filter['space']['min'], $filter['space']['max']);
}
if (
(!empty($filter['price']['max'])
&& $filter['price']['max'] < 15000)
|| (!empty($filter['price']['min'])
&& $filter['price']['min'] > 0)) {
$query = $this->_setQueryPrice($query, $filter['price']['min'], $filter['price']['max']);
} else {
if (!empty($filter["sorter"]["order"]) && $filter["sorter"]["order"] === "dollars") {
$query = $this->_setQueryPrice($query, 1, 15000);
}
}
if (!empty($filter['text'])) {
$query = $this->_setQueryText2($query, $filter['text']);
}
if ((!empty($filter['distance']['max']) && $filter['distance']['max'] < 50) ||
(!empty($filter['distance']['min']) && $filter['distance']['min'] > 0)) {
$query = $this->_setQueryDistance($query, $filter['distance']['min'], $filter['distance']['max']);
}
if (!empty($filter['highway'])) {
$query = $this->_setQueryHighway($query, $filter['highway']);
}
if (!empty($filter['settlement'])) {
$query = $this->_setQuerySettlement($query, $filter['settlement']);
}
if (!empty($filter['type'])) {
$query = $this->_setQueryType($query, $filter['type']);
}
if (!empty($filter['tag'])) {
$query = $this->_setQueryTag($query, $filter['tag'][0]);
}
if (!empty($filter['stage'])) {
$query = $this->_setQueryStage($query, $filter['stage']);
}
if (!empty($filter['sale'])) {
$query = $this->_setQuerySale($query, $filter['sale']);
}
if (isset($filter['repair'])) {
$query = $this->_setQueryRepair($query, $filter['repair']);
}
if (!empty($filter['rooms'])) {
$arr = array();
$arr['min'] = $filter['rooms'];
$arr['max'] = $filter['rooms'];
$query = $this->_setQueryRoomsT($query, $arr);
/*$query = $this->_setQueryRoomsT($query, $filter['rooms']);*/
}
$queryCount = clone $query;
return [
'count' => $queryCount->select('count(lot)')->getQuery()->getResult()[0][1],
'lotsAddress' => $query->getQuery()->getResult(),
'lots' => $query->setMaxResults(10)->setFirstResult(($filter['page'] - 1) * 10)->getQuery()->getResult(),
'query' => $query->getQuery()->getDql(),
];
}
public function getComplexes()
{
return $this
->_em
->getRepository('TroikaMainBundle:Complex')
->findAll();
}
public function getComplexesList($r)
{
$query = $this
->_em
->getRepository('TroikaMainBundle:ComplexPage')
->createQueryBuilder('c')
->andWhere('c.isVisible = 1')
->orderBy('c.id', 'desc');
if ($district = $r->get('district')) {
$query
->andWhere('c.area2 IN (:district)')
->setParameter('district', $district);
}
if ($subway = $r->get('subway')) {
$query
->andWhere('c.subway2 IN (:subway)')
->setParameter('subway', $subway);
}
if ($type = $r->get('type')) {
$query
->andWhere('c.typeComplex IN (:type)')
->setParameter('type', $type);
}
if ($stage = $r->get('stage')) {
$query
->andWhere('c.stageComplex IN (:stage)')
->setParameter('stage', $stage);
}
if ($text = $r->get('text')) {
$text = str_replace("%", "\%", $text);
$query
->andWhere($query->expr()->like('c.name', ':text'))
->setParameter('text', $text . '%');
}
$page = $r->get('page') ?: 1;
$query = $query->getQuery();
$complexes = $this->paginator->paginate(
$query,
$page,
10
);
return $complexes;
}
public function getEmptyPage()
{
return $this->paginator->paginate(
[],
0,
10
);
}
public function getFave($lots)
{
return $this
->_em
->getRepository('TroikaMainBundle:Lot')
->createQueryBuilder('lot')
->where('lot.id IN (:ids)')
->setParameter('ids', $lots)
->getQuery()
->getResult();
}
public function getForPartner($i)
{
return $lot = $this
->_em
->getRepository('TroikaMainBundle:Lot')
->findBy(['typeLot' => $i]);
}
public function getForTypeById($id, $type)
{
$lot = $this
->_em
->getRepository('TroikaMainBundle:Lot')
->findBy(
[
'id' => $id,
'typeLot' => $type//,
// 'showLot' => true
]
);
return ((is_countable($lot) ? count($lot) : 0) === 0) ? false : $lot[0];
}
public function getLot($id)
{
return $this
->_em
->getRepository('TroikaMainBundle:Lot')
->find($id);
}
public function getLotAddresses($typeLot, $typeOperation)
{
$lots = $this
->_em
->getRepository('TroikaMainBundle:Lot')
->findBy(['typeLot' => $typeLot, 'typeOperation' => $typeOperation, 'showLot' => true]);
$result = [];
/** @var Lot $lot */
foreach ($lots as $lot) {
if ($lot->getAddress()) {
$address = "";
if ($lot->getTypeLot()->getId() == 2) {
$address = "Московская область ";
}
if ($lot->getTypeLot()->getId() == 1 || $lot->getTypeLot()->getId() == 3) {
$address = "Москва ";
} else {
if ($lot->getCountry()) {
$address .= $lot->getCountry()->getName();
if ($lot->getCountryCity()) {
$address .= " " . $lot->getCountryCity()->getName();
}
}
}
$address .= " " . $lot->getAddress()->getAddress();
// if ($lot->getAddress()->getLat() && $lot->getTypeLot()->getId() != 2) {
// $address = $lot->getAddress()->getLat()." ".$lot->getAddress()->getLng();
// }
if ($lot->getTypeLot()->getId() == 2) {
if ($lot->getSettlement()) {
$address = $lot->getSettlement()->getName();
} else {
if ($lot->getHighway()) {
$address = "Московская область ";
$address .= " " . $lot->getHighway()->getOnSite() . ", " . $lot->getMkadDistance() . " км";
}
}
}
$result[] = [
'id' => $lot->getId(),
'address' => $address,
'coord' => [$lot->getX(), $lot->getY()],
// 'string' => is_null($lot->getAddress()->getStreet()) ? $lot->getAddressString() : $lot->getAddress()->getStreet() . $lot->getAddress()->getHome(),
'price' => $lot->getPrice(),
];
}
}
return $result;
}
public function getLotsForFeed()
{
return $this
->_em
->getRepository('TroikaMainBundle:Lot')
->createQueryBuilder('lot')
->select('lot')
->where('lot.typeLot in (1,2)')
->andWhere('lot.showLot = 1')
->andWhere('lot.typeOperation IN (1,2)')
->getQuery()
->getResult();
}
public function getLotsForFeedCian()
{
return $this
->_em
->getRepository('TroikaMainBundle:Lot')
->createQueryBuilder('lot')
->select('lot')
->where('lot.typeLot in (1,2,3)')
->andWhere('lot.typeOperation IN (1,2)')
->andWhere('lot.showLot = 1')
->getQuery()
->getResult();
}
public function getLotsForFeedCianCom()
{
return $this
->_em
->getRepository('TroikaMainBundle:Lot')
->createQueryBuilder('lot')
->select('lot')
->where('lot.typeLot = 3')
->andWhere('lot.typeOperation IN (1,2)')
->andWhere('lot.showLot = 1')
->getQuery()
->getResult();
}
public function getLotsForFeedForeign()
{
return $this
->_em
->getRepository('TroikaMainBundle:Lot')
->createQueryBuilder('lot')
->select('lot')
->andWhere('lot.typeOperation IN (1,2)')
->andWhere('lot.showLot = 1')
->getQuery()
->getResult();
}
public function getLotsForFeedKvadroom()
{
return $this
->_em
->getRepository('TroikaMainBundle:Lot')
->createQueryBuilder('lot')
->select('lot')
->where('lot.typeLot = 1')
->andWhere('lot.typeOperation = 1')
->andWhere('lot.showLot = 1')
->getQuery()
->getResult();
}
public function getLotsForFeedKvadroomCom()
{
return $this
->_em
->getRepository('TroikaMainBundle:Lot')
->createQueryBuilder('lot')
->select('lot')
->where('lot.typeLot = 3')
->andWhere('lot.typeOperation = 1')
->andWhere('lot.showLot = 1')
->getQuery()
->getResult();
}
public function getLotsForFeedKvadroomSub()
{
return $this
->_em
->getRepository('TroikaMainBundle:Lot')
->createQueryBuilder('lot')
->select('lot')
->where('lot.typeLot = 2')
->andWhere('lot.typeOperation = 1')
->andWhere('lot.showLot = 1')
//->andWhere('lot.typeHouse.name in ("","","")')
->getQuery()
->getResult();
}
public function getLotsForFeedRbc($s)
{
$q = $this
->_em
->getRepository('TroikaMainBundle:Lot')
->createQueryBuilder('lot')
->select('lot')
->where("lot.typeLot = $s")
->andWhere('lot.showLot = 1')
->andWhere('lot.typeOperation IN (1,2)')// ->setMaxResults(15);
;
if ($s == '1') {
$q->andWhere('lot.rooms > 0');
}
if ($s == '2') {
$q->andWhere("lot.price is not null and lot.area is not null and lot.description is not null and lot.rooms is not null");
}
if ($s == '3') {
$q->andWhere(
"lot.price is not null and lot.area is not null and lot.subway2 is not null and lot.livArea is not null and lot.kitArea is not null and lot.description is not null and lot.rooms is not null"
);
}
return $q
->getQuery()
->getResult();
}
public function getLotsForFeedRbcTest($s)
{
$q = $this
->_em
->getRepository('TroikaMainBundle:Lot')
->createQueryBuilder('lot')
->select('lot')
->where("lot.typeLot = $s")
->andWhere('lot.showLot = 1')
->andWhere('lot.typeOperation IN (1,2)')
->andWhere(
"lot.price is not null and lot.area is not null and lot.subway2 is not null and lot.livArea is not null and lot.kitArea is not null and lot.description is not null and lot.rooms is not null"
)
->setMaxResults(50);
if ($s == '1') {
$q->andWhere('lot.rooms > 0');
}
$arr = $q
->getQuery()
->getResult();
$cb = function ($value) {
if (is_numeric($value->getFloor())) {
return true;
} else {
return false;
}
};
$arr = array_filter($arr, $cb);
return $arr;
}
public function getPageByType($options)
{
$query = $this
->_em
->getRepository('TroikaMainBundle:Lot')
->createQueryBuilder('lot')
->select('lot')
->where('lot.typeLot = :type')
->andWhere('lot.typeOperation = :operation')
->andWhere('lot.showLot = 1')
->setParameter('type', $options['type'])
->setParameter('operation', $options['operation'])
->orderBy('lot.date', 'desc');
if ($options['price']['max'] < 15000 || $options['price']['min'] > 0) {
$query = $this->_setQueryPrice($query, $options['price']['min'], $options['price']['max']);
}
if ($options['space']['max'] < 100000 || $options['space']['min'] > 0) {
$query = $this->_setQuerySpace($query, $options['space']['min'], $options['space']['max']);
}
if (isset($options['rooms'])) {
$arr = array();
$arr['min'] = $options['rooms'];
$arr['max'] = $options['rooms'];
//$query = $this->_setQueryRoomsT($query, $options['rooms']);
$query = $this->_setQueryRoomsT($query, $arr);
}
if (isset($options['repair'])) {
$query = $this->_setQueryRepair($query, $options['repair']);
}
if ($options['sale']) {
$query = $this->_setQuerySale($query);
}
$opts = $_GET;
if(isset($opts['page'])){
unset($opts['page']);
}
$query = $query->getQuery();
$pagination = $this->paginator->paginate(
$query,
$options['page'],
10,
['distinct' => false]
);
return $pagination;
}
public function getRecent()
{
$city = $this
->_em
->getRepository('TroikaMainBundle:Lot')
->createQueryBuilder("lot")
->where('lot.typeLot = :type')
->andWhere('lot.showLot = 1')
->orderBy("lot.date", "DESC")
->setParameter('type', 1)
->setMaxResults(3)
->getQuery()
->getResult();
$suburban = $this
->_em
->getRepository('TroikaMainBundle:Lot')
->createQueryBuilder("lot")
->where('lot.typeLot = :type')
->andWhere('lot.showLot = 1')
->orderBy("lot.date", "DESC")
->setParameter('type', 2)
->setMaxResults(3)
->getQuery()
->getResult();
$commercial = $this
->_em
->getRepository('TroikaMainBundle:Lot')
->createQueryBuilder("lot")
->where('lot.typeLot = :type')
->andWhere('lot.showLot = 1')
->orderBy("lot.date", "DESC")
->setParameter('type', 3)
->setMaxResults(3)
->getQuery()
->getResult();
$foreign = $this
->_em
->getRepository('TroikaMainBundle:Lot')
->createQueryBuilder("lot")
->where('lot.typeLot = :type')
->andWhere('lot.showLot = 1')
->orderBy("lot.date", "DESC")
->setParameter('type', 4)
->setMaxResults(3)
->getQuery()
->getResult();
return array_merge($city, $suburban, $foreign, $commercial);
}
public function getSimilar(Lot $lot)
{
$similar = $this
->_em
->getRepository('TroikaMainBundle:Lot')
->createQueryBuilder('lot');
// if ($lot->getTypeLot()->getId() == 1 && $lot->getRooms()) {
// $similar->addOrderBy('ABS(:ORooms - lot.rooms)', 'ASC');
// $similar->setParameter('ORooms', $lot->getRooms());
// }
if ($lot->getMkadDistance()) {
$similar->addOrderBy('ABS(:OmkadDistance - lot.mkadDistance)', 'ASC');
$similar->setParameter('OmkadDistance', $lot->getMkadDistance());
}
$similar->setMaxResults(13);
// $similar->andWhere('lot.livArea is not NULL');
$similar->andWhere('lot.typeOperation=:Otypeop');
$similar->andWhere('lot.id <> :Oid');
$similar->andWhere('lot.showLot=1');
$similar->andWhere('lot.typeLot=:Otypelot');
$similar->setParameter('Otypeop', $lot->getTypeOperation());
$similar->setParameter('Otypelot', $lot->getTypeLot());
$similar->setParameter('Oid', $lot->getId());
if ($lot->getTypeLot()->getId() == 2) {
if ($lot->getDollars()) {
$similar->addOrderBy('ABS(:Oprice - lot.dollars)', 'ASC');
$similar->setParameter('Oprice', $lot->getDollars());
}
$similar->andWhere('lot.mkadDistance is NOT NULL');
$similar->addOrderBy('ABS(:Ohighway - lot.highway)', 'ASC');
$similar->setParameter('Ohighway', $lot->getHighway()->getId());
} elseif ($lot->getTypeLot()->getId() == 1 || $lot->getTypeLot()->getId() == 3) {
if ($lot->getDollars()) {
$similar->addOrderBy('ABS(:Oprice - lot.dollars)', 'ASC');
$similar->setParameter('Oprice', $lot->getDollars());
}
$similar->andWhere('lot.dollars is not NULL');
$similar->andWhere('lot.dollars is not NULL');
if ($lot->getTypeLot()->getId() == 1 && $lot->getDistrict3()) {
$similar->andWhere('lot.district3=:Odistrict');
$similar->setParameter('Odistrict', $lot->getDistrict3()->getId());
} else {
/* if ($lot->getTypeLot()->getId() == 1 && $lot->getDistrict2()) {
$similar->andWhere('lot.district2=:Odistrict');
$similar->setParameter('Odistrict', $lot->getDistrict2()->getId());
} else { */
if ($lot->getTypeLot()->getId() == 1 && $lot->getDistrict()) {
$similar->andWhere('lot.district=:Odistrict');
$similar->setParameter('Odistrict', $lot->getDistrict()->getId());
}
// }
}
} else {
$similar->andWhere('lot.country=:Odistrict');
$similar->setParameter('Odistrict', $lot->getCountry()->getId());
}
$similar = $similar->getQuery();
$similar = $similar->getResult();
return $similar;
}
public function getSimilarCom(Lot $lot)
{
$similar = $this
->_em
->getRepository('TroikaMainBundle:Lot')
->createQueryBuilder('lot');
$similar->andWhere('lot.typeOperation=:Otypeop');
$similar->andWhere('lot.id <> :Oid');
$similar->andWhere('lot.price > :Oprice');
$similar->andWhere('lot.dollars > :Oprice');
$similar->andWhere('lot.showLot=1');
$similar->andWhere('lot.typeLot=:Otypelot');
$similar->andWhere('lot.typeHouse=:OtypeHouse');
$similar->setParameter('Otypeop', $lot->getTypeOperation());
$similar->setParameter('Otypelot', $lot->getTypeLot());
$similar->setParameter('OtypeHouse', $lot->getTypeHouse());
$similar->setParameter('Oid', $lot->getId());
$similar->setParameter('Oprice', 1);
$similar = $similar->getQuery();
$similar = $similar->getResult();
return $similar;
}
public function getSpecial()
{
$query = $this
->_em
->getRepository('TroikaMainBundle:Lot')
->createQueryBuilder('lot')
->where('lot.exclusive = :exclusive')
->andWhere('lot.showLot = 1')
->andWhere('lot.typeLot <> 5')
->orderBy('lot.date', 'desc')
->setParameter('exclusive', true)
->getQuery()
->getResult();
$pagination = $this->paginator->paginate(
$query,
1,
10
);
return $pagination;
}
public function getSpecialLotAddresses()
{
$lots = $query = $this
->_em
->getRepository('TroikaMainBundle:Lot')
->createQueryBuilder('lot')
->where('lot.exclusive = :exclusive')
// ->andWhere('lot.showLot = 1')
->andWhere('lot.typeLot <> 5')
->orderBy('lot.price', 'desc')
->setParameter('exclusive', true)
->getQuery()
->getResult();;
$result = [];
foreach ($lots as $lot) {
if ($lot->getAddress()) {
$result[] = [
'id' => $lot->getId(),
'address' => $lot->getAddress()->getAddress(),
'coord' => [$lot->getX(), $lot->getY()],
// 'string' => is_null($lot->getAddress()->getStreet()) ? $lot->getAddressString() : $lot->getAddress()->getStreet() . $lot->getAddress()->getHome(),
'price' => $lot->getPrice(),
];
}
}
return $result;
}
}