Symfony Profiler

<?php
namespace PortalBundle\Model;
use Application\Sonata\MediaBundle\Entity\Media;
use CoreBundle\Entity\Vehicles\CharacteristicValue;
use CoreBundle\Entity\Vehicles\VariationCharacteristic;
use CoreBundle\Entity\Dealer;
use CoreBundle\Model\Vehicles\Characteristic;
use DateTime;
use CoreBundle\Entity\Brand;
use CoreBundle\Entity\Model;
use CoreBundle\Entity\Vehicles\ConfiguratorColor;
use CoreBundle\Entity\Vehicles\InStock;
use CoreBundle\Entity\Vehicles\RecommendGroup;
use CoreBundle\Entity\Vehicles\Vehicle;
use CoreBundle\Entity\Vehicles\VehicleItem;
use CoreBundle\Factory\Vehicle as VehicleFactory;
use CoreBundle\Factory\InStockVehicle as VehicleInStockFactory;
use CoreBundle\Model\Vehicles\InStockRepository;
use CoreBundle\Model\Vehicles\VehicleType;
use CoreBundle\Services\MediaExtensionVidi;
use DcSiteBundle\Controller\Unicommerce\MainController;
use DcSiteBundle\Model\CreditModel;
use Doctrine\ORM\EntityManagerInterface;
use Doctrine\ORM\Query\Expr\Join;
use PortalBundle\Enums\CatalogEnum;
use PortalBundle\Services\VehicleService;
use Symfony\Component\HttpFoundation\RequestStack;
use Symfony\Component\Routing\RouterInterface;
use Symfony\Contracts\Translation\TranslatorInterface;
class Catalog
{
const NOT_VIDI_SELECT_DEALERS = [4, 6, 7, 32, 43, 14];
const PRE_PAGES = 18;
private EntityManagerInterface $em;
private RouterInterface $router;
private MediaExtensionVidi $mediaExtension;
private VehicleFactory $vehicleFactory;
private TranslatorInterface $translator;
private RequestStack $requestStack;
private CreditModel $creditModel;
private InStockRepository $carInStockService;
private VehicleService $vehicleService;
private VehicleInStockFactory $vehicleInStockFactory;
public function __construct(
EntityManagerInterface $em,
RouterInterface $router,
MediaExtensionVidi $mediaExtension,
TranslatorInterface $translator,
VehicleFactory $vehicleFactory,
RequestStack $requestStack,
CreditModel $creditModel,
InStockRepository $inStockRepository,
VehicleService $vehicleService,
VehicleInStockFactory $vehicleInStockFactory
)
{
$this->em = $em;
$this->router = $router;
$this->mediaExtension = $mediaExtension;
$this->translator = $translator;
$this->vehicleFactory = $vehicleFactory;
$this->requestStack = $requestStack;
$this->creditModel = $creditModel;
$this->inStockRepository = $inStockRepository;
$this->vehicleService = $vehicleService;
$this->vehicleInStockFactory = $vehicleInStockFactory;
}
public function getVehicleByCatalog($user, $findVehicles, $routeParams, $compareCookie)
{
$featuresIds = $this->vehicleService->getFeatures($user);
$vehicleComparison = explode(',', $compareCookie);
$items = [];
foreach ($findVehicles as $item) {
$vehicle = $this->vehicleFactory->createByVehicleItem($item[0]);
if (!$vehicle) {
continue;
}
/** @var InStock $carInStock */
$carInStock = $this->inStockRepository->getByVehicleId($vehicle->getVehicleId(), 1);
$carInStockImage = ($carInStock) ? $carInStock[0]->getPreview() : null;
$vehicleColors = [];
foreach ($vehicle->getColors() as $color) {
if (!$color->getState()) {
continue;
}
$imageVehicle = $color->getGallery();
if (!$imageVehicle) {
continue;
}
$firstImageVehicle = $imageVehicle->getGalleryItems()->first();
if (!$firstImageVehicle) {
continue;
}
$vehicleColors[$color->getId()] = [
'imageColor' => $this->mediaExtension->getPath($color->getImage(), 'reference'),
'imageWebpVehicle' => $this->mediaExtension->pathWebp($firstImageVehicle->getMedia(), 'reference'),
'imageVehicle' => $this->mediaExtension->getPath($firstImageVehicle->getMedia(), 'reference'),
];
}
if ($carInStockImage) {
$vehiclePictureSecond = [
'img' => $this->mediaExtension->getPath($carInStockImage, 'reference'),
'img_webp' => $this->mediaExtension->pathWebp($carInStockImage, 'reference'),
];
} elseif ($vehicle->getPreviewSecond()) {
$vehiclePictureSecond = [
'img' => $this->mediaExtension->getPath($vehicle->getPreviewSecond(), 'reference'),
'img_webp' => $this->mediaExtension->pathWebp($vehicle->getPreviewSecond(), 'reference'),
];
} else {
$vehiclePictureSecond = [
'img' => $this->mediaExtension->getPath($vehicle->getPreview(), 'reference'),
'img_webp' => $this->mediaExtension->pathWebp($vehicle->getPreview(), 'reference'),
];
}
$items[] = [
'vehicleType' => VehicleType::getTypeDataById($vehicle->getVehicleType()),
'creditPayment' => $this->creditModel->getMinPayment($vehicle),
'vehicle' => $vehicle,
'featuredId' => $featuresIds[$vehicle->getVehicleItemId()] ?? null,
'comparedId' => in_array($vehicle->getVehicleItemId(), $vehicleComparison) ? $vehicle->getVehicleItemId() : null,
'dealer' => $vehicle->getDealer()->getId(),
'vehiclePrice' => $item['price'],
'vehicleColors' => $vehicleColors,
'vehiclePicture' => [
'img' => $this->mediaExtension->getPath($vehicle->getPreview(), 'reference'),
'img_webp' => $this->mediaExtension->pathWebp($vehicle->getPreview(), 'reference'),
],
'vehiclePictureSecond' => $vehiclePictureSecond,
'hasNds' => $routeParams['state'] === CatalogEnum::CATALOG_USED ? $vehicle->getHasNds() : false,
'isSelect' =>
$routeParams['state'] === CatalogEnum::CATALOG_USED &&
!in_array($vehicle->getDealer()->getId(), Catalog::NOT_VIDI_SELECT_DEALERS),
];
}
return $items;
}
public function getVehicleByCatalogForSitemap($routeParams)
{
$findVehicles = $this->findByParams($routeParams, [], 9999);
if(!$findVehicles['data']){
return false;
}
$items = [];
foreach ($findVehicles['data'] as $item) {
$vehicle = $this->vehicleFactory->createByVehicleItem($item[0]);
if (!$vehicle) {
continue;
}
$items[] = $vehicle;
}
return $items;
}
public function getInStockVehicleByCatalog($user, $inStockVehicle)
{
$request = $this->requestStack->getCurrentRequest();
$vehicle = $inStockVehicle->getVehicle();
$vehicleFactory = $this->vehicleFactory->createByEntity($vehicle);
$featuresIds = $this->vehicleService->getFeatures($user);
$compareCookie = $request->cookies->get('compare');
$vehicleComparison = explode(',', $compareCookie);
$item = [
'vehicleType' => VehicleType::getTypeDataById($vehicle->getVehicleType()),
'creditPayment' => $this->creditModel->getMinPayment($vehicleFactory),
'vehicle' => $inStockVehicle,
'featuredId' => $featuresIds[$vehicleFactory->getVehicleItemId()] ?? null,
'comparedId' => in_array($vehicleFactory->getVehicleItemId(), $vehicleComparison) ? $vehicleFactory->getVehicleItemId() : null,
'vehiclePrice' => '',
'vehicleColors' => '',
'vehiclePicture' => [
'img' => $this->mediaExtension->getPath($inStockVehicle->getPreview(), 'reference'),
'img_webp' => $this->mediaExtension->pathWebp($inStockVehicle->getPreview(), 'reference'),
],
'vehiclePictureSecond' => [
'img' => $this->mediaExtension->getPath($inStockVehicle->getPreview(), 'reference'),
'img_webp' => $this->mediaExtension->pathWebp($inStockVehicle->getPreview(), 'reference'),
],
'hasNds' => $inStockVehicle->getHasNds(),
'isSelect' => !in_array($vehicle->getDealer()->getId(), Catalog::NOT_VIDI_SELECT_DEALERS),
'isStock' => true,
'isUsed' => false
];
return $item;
}
public function getCatalogNav($type, $state, $hasCredit, $brand, $param, $value, $group = null)
{
$request = $this->requestStack->getCurrentRequest();
$route = $request->get('_route');
$catalogNav = [];
if (in_array($route, [CatalogEnum::ROUTE_PORTAL_CATALOG, CatalogEnum::ROUTE_PORTAL_CREDIT_CATALOG])) {
$catalogNav = $this->getVehicleTypeBodyPrice($state, $type, $hasCredit, 'BT');
}
if (in_array($route, [CatalogEnum::ROUTE_PORTAL_CATALOG_PARAM, CatalogEnum::ROUTE_PORTAL_CREDIT_CATALOG_PARAM])) {
$catalogNav = $this->getVehicleBrandPrice($state, $type, $hasCredit, $param, $value);
}
if ($route == CatalogEnum::ROUTE_PORTAL_CATALOG_GROUPS) {
$catalogNav = $this->getVehicleTypeBodyPrice($state, $type, $hasCredit,'BT', $group);
}
if ($route == CatalogEnum::ROUTE_PORTAL_CATALOG_PARAM) {
$catalogNav = $this->getVehicleBrandPrice($state, $type, $hasCredit, $param, $value);
}
if (in_array($route, [CatalogEnum::ROUTE_PORTAL_CATALOG_BRAMD_BODY_TYPE, CatalogEnum::ROUTE_PORTAL_CREDIT_CATALOG_BRAMD_BODY_TYPE])) {
$catalogNav = $this->getVehicleModelPrice($state, $type, $hasCredit, $brand, $param, $value);
}
if (in_array($route, [CatalogEnum::ROUTE_PORTAL_CATALOG_B_NAME, CatalogEnum::ROUTE_PORTAL_CREDIT_CATALOG_B_NAME])) {
$catalogNav = $this->getVehicleModelPriceWithoutParam($state, $type, $hasCredit, $brand);
}
return $catalogNav;
}
public function getCatalogNavByGetParams($type, $state, $hasCredit, $brand, $searchParams, $value)
{
$request = $this->requestStack->getCurrentRequest();
$route = $request->get('_route');
$catalogNav = [];
if ($route == CatalogEnum::ROUTE_PORTAL_CATALOG || $route == CatalogEnum::ROUTE_PORTAL_CATALOG_FILTER) {
if (!isset($searchParams['dynamic']) && !isset($searchParams['m-brand'])) {
$catalogNav = $this->getVehicleTypeBodyPrice($state, $type, $hasCredit, 'BT');
}
if (isset($searchParams['dynamic']) && !isset($searchParams['m-brand'])) {
$param = array_key_first($searchParams['dynamic']);
$value = array_key_first($searchParams['dynamic'][$param]);
$catalogNav = $this->getVehicleBrandPrice($state, $type, $hasCredit, $param, $value);
}
if (isset($searchParams['dynamic']) && isset($searchParams['m-brand'])) {
$param = array_key_first($searchParams['dynamic']);
$value = array_key_first($searchParams['dynamic'][$param]);
$brand = array_key_first($searchParams['m-brand']);
$catalogNav = $this->getVehicleModelPrice($state, $type, $hasCredit, $brand, $param, $value);
}
if (!isset($searchParams['dynamic']) && isset($searchParams['m-brand'])) {
$brand = array_key_first($searchParams['m-brand']);
$catalogNav = $this->getVehicleModelPrice($state, $type, $hasCredit, $brand, '', '', $searchParams);
}
if (!isset($searchParams['dynamic']) && !isset($searchParams['m-model']) && isset($searchParams['m-brand'])) {
$catalogNav = $this->getVehicleModelPriceWithoutParam($state, $type, $hasCredit, $brand);
}
}
return $catalogNav;
}
public function getStockCatalogNav($type, $state, $brand, $searchParams, $value)
{
$request = $this->requestStack->getCurrentRequest();
$route = $request->get('_route');
$catalogNav = [];
if ($route == CatalogEnum::ROUTE_PORTAL_CATALOG_STOCK || $route == CatalogEnum::ROUTE_PORTAL_CATALOG_FILTER_STOCK) {
if (!isset($searchParams['dynamic']) && !isset($searchParams['m-brand'])) {
$catalogNav = $this->getStockTypeBodyPrice($state, $type, 'BT');
}
if (isset($searchParams['dynamic']) && !isset($searchParams['m-brand'])) {
$param = array_key_first($searchParams['dynamic']);
$value = array_key_first($searchParams['dynamic'][$param]);
$catalogNav = $this->getStockVehicleBrandPrice($state, $type, $param, $value, $searchParams);
}
if (isset($searchParams['dynamic']) && isset($searchParams['m-brand'])) {
$param = array_key_first($searchParams['dynamic']);
$value = array_key_first($searchParams['dynamic'][$param]);
$brand = array_key_first($searchParams['m-brand']);
$catalogNav = $this->getStockVehicleModelPrice($state, $type, $brand, $param, $value, $searchParams);
}
if (!isset($searchParams['dynamic']) && isset($searchParams['m-brand'])) {
$brand = array_key_first($searchParams['m-brand']);
$catalogNav = $this->getStockVehicleModelPrice($state, $type, $brand, '', '', $searchParams);
}
if (!isset($searchParams['dynamic']) && !isset($searchParams['m-model']) && isset($searchParams['m-brand'])) {
$catalogNav = $this->getStockVehicleModelPriceWithoutParam($state, $type, array_key_first($searchParams['m-brand']));
}
}
return $catalogNav;
}
public function getVehicleBrandPrice($state, $type, $hasCredit, $param, $value)
{
$isUsed = $state == 'new' ? 0 : 1;
$vehicleType = VehicleType::getTypeDataByUrl($type);
$query = $this->em->getRepository(VehicleItem::class)->createQueryBuilder('vi')
->select(
'b.id as brand_id',
'COUNT(ve.id) as vehicle_count',
'MIN(
CASE
WHEN
(ve.is_used = 0)
THEN
CASE
WHEN vi.alt_price > 0 THEN vi.alt_price
WHEN vi.alt_rate > 0 THEN vi.alt_rate * vi.price
ELSE vi.price * d.rate
END
ELSE
CASE
WHEN vi.alt_price > 0 THEN vi.alt_price
ELSE vi.price
END
END
) AS min_price'
);
$query->innerJoin('vi.vehicle', 've', Join::WITH, 've.vehicle_type = :vehicleType')->setParameter('vehicleType', $vehicleType['id'])
->innerJoin('ve.dealer', 'd')
->innerJoin('ve.model', 'm')
->innerJoin('m.brand', 'b')
->innerJoin('vi.variation', 'va')
->innerJoin('va.characteristics', 'vch')
->innerJoin('vch.characteristic', 'ch')
->innerJoin('vch.characteristic_value', 'chv')
->andWhere("ch.id = :param")
->andWhere('chv.url = :url')
->andWhere('vi.sold != 1')
->setParameter('param', $param)
->setParameter('url', $value)
->andWhere('ve.state = 1')
->andWhere('vi.sold != 1')
->andWhere('ve.is_not_filled = 0')
->andWhere('ve.is_delete != 1')
->andWhere('ve.is_used = :isUsed')
->setParameter('isUsed', $isUsed);
if ($hasCredit) {
$query->andWhere('ve.credit_available = :hasCredit')
->setParameter('hasCredit', $hasCredit);
}
$query->groupBy('brand_id');
$vehicleItems = $query->getQuery()->getResult();
$brands_id = array_column($vehicleItems, 'brand_id');
$query = $this->em->getRepository(Brand::class)->createQueryBuilder('b')
->where('b.id in (:brands)')->setParameter('brands', $brands_id);
$brands = $query->getQuery()->getResult();
if ($hasCredit) {
$nameRoute = CatalogEnum::ROUTE_PORTAL_CREDIT_CATALOG_BRAMD_BODY_TYPE;
} else {
$nameRoute = CatalogEnum::ROUTE_PORTAL_CATALOG_BRAMD_BODY_TYPE;
}
/** @var Brand $brand */
foreach ($brands as $brand) {
$key = array_search($brand->getId(), array_column($vehicleItems, 'brand_id'));
$vehicleItems[$key]['brand'] = $brand;
$vehicleItems[$key]['url'] = $this->router->generate($nameRoute, ['state' => $state, 'type' => $vehicleType['url'], 'brand' => $brand->getUrl(), 'param' => $param, 'value' => $value]);
}
return $vehicleItems;
}
public function getStockVehicleBrandPrice($state, $type, $param, $value, $searchParams)
{
$vehicleType = VehicleType::getTypeDataByUrl($type);
$query = $this->em->getRepository(VehicleItem::class)->createQueryBuilder('vi')
->select('b.id as brand_id','COUNT(ve.id) as vehicle_count','MIN(
CASE
WHEN cis.action_price > 0 THEN cis.action_price
WHEN cis.action_price_usd > 0 THEN
CASE
WHEN vi.alt_rate > 0 THEN cis.action_price_usd * vi.alt_rate
ELSE cis.action_price_usd * d.rate
END
WHEN vi.alt_price > 0 THEN vi.alt_price
ELSE
CASE
WHEN vi.alt_rate > 0 THEN vi.price * vi.alt_rate
ELSE vi.price * d.rate
END
END + COALESCE(cis.add_cost, 0)
) AS min_price');
// ->select('b.id as brand_id', 'COUNT(ve.id) as vehicle_count', 'MIN(case when (case when (ve.is_used = 0 OR d.id != 6) then (case when vi.alt_price > 0 then vi.alt_price else (case when vi.alt_rate > 0 then vi.alt_rate * vi.price else vi.price * d.rate end) end) else (case when vi.alt_price > 0 then vi.alt_price else vi.price end) end) = 0 then 9999999999 else (case when (ve.is_used = 0 OR d.id != 6) then (case when vi.alt_price > 0 then vi.alt_price else (case when vi.alt_rate > 0 then vi.alt_rate * vi.price else vi.price * d.rate end) end) else (case when vi.alt_price > 0 then vi.alt_price else vi.price end) end) end) as min_price');
$query->innerJoin('vi.vehicle', 've', Join::WITH, 've.vehicle_type = :vehicleType')->setParameter('vehicleType', $vehicleType['id'])
->innerJoin('ve.dealer', 'd')
->innerJoin('ve.model', 'm')
->innerJoin('m.brand', 'b')
->innerJoin('vi.variation', 'va')
->innerJoin('va.characteristics', 'vch')
->innerJoin('vch.characteristic', 'ch')
->innerJoin('vch.characteristic_value', 'chv')
->leftJoin('vi.vehicleInStock', 'cis')
->andWhere("ch.id = :param")
->andWhere('chv.url = :url')
->setParameter('param', $param)
->setParameter('url', $value)
->andWhere('ve.state = 1')
->andWhere('cis.state = 1')
->andWhere('ve.is_not_filled = 0')
->andWhere('ve.is_delete != 1')
->andWhere('cis.is_delete != 1')
->andWhere('ve.is_used = 0')
->groupBy('brand_id');
$vehicleItems = $query->getQuery()->getResult();
$brands_id = array_column($vehicleItems, 'brand_id');
$query = $this->em->getRepository(Brand::class)->createQueryBuilder('b')
->where('b.id in (:brands)')->setParameter('brands', $brands_id);
$brands = $query->getQuery()->getResult();
/** @var Brand $brand */
foreach ($brands as $brand) {
$key = array_search($brand->getId(), array_column($vehicleItems, 'brand_id'));
$vehicleItems[$key]['brand'] = $brand;
$vehicleItems[$key]['url'] = $this->router->generate(CatalogEnum::ROUTE_PORTAL_CATALOG_FILTER_STOCK, array_merge($searchParams,['state' => $state, 'type' => $vehicleType['url'], 'm-brand' => [$brand->getUrl() => true]]));
}
return $vehicleItems;
}
public function getVehicleModelPriceWithoutParam($state, $type, $hasCredit, $brandUrl)
{
$vehicleType = VehicleType::getTypeDataByUrl($type);
$isUsed = $state == 'new' ? 0 : 1;
$query = $this->em->getRepository(VehicleItem::class)->createQueryBuilder('vi')
->select(
'm.id AS model_id',
'COUNT(DISTINCT vi.id) AS vehicle_count',
'MIN(CASE
WHEN (ve.is_used = 0) THEN
CASE
WHEN vi.alt_price > 0 THEN vi.alt_price
ELSE
CASE
WHEN vi.alt_rate > 0 THEN vi.alt_rate * vi.price
ELSE vi.price * d.rate
END
END
ELSE
CASE
WHEN vi.alt_price > 0 THEN vi.alt_price
ELSE vi.price
END
END) AS min_price'
)
->innerJoin('vi.vehicle', 've', Join::WITH, 've.vehicle_type = :vehicleType')
->setParameter('vehicleType', $vehicleType['id'])
->innerJoin('ve.dealer', 'd')
->innerJoin('ve.model', 'm', Join::WITH, 'm.url IS NOT NULL')
->innerJoin('m.brand', 'b', Join::WITH, 'b.url = :brandUrl')
->setParameter('brandUrl', $brandUrl)
->innerJoin('vi.variation', 'va')
->innerJoin('va.characteristics', 'vch')
->innerJoin('vch.characteristic', 'ch')
->innerJoin('vch.characteristic_value', 'chv')
->andWhere('vi.state = 1')
->andWhere('ve.state = 1')
->andWhere('ve.is_not_filled = 0')
->andWhere('ve.is_delete != 1')
->andWhere('ve.is_used = :isUsed')
->andWhere('vi.sold != 1')
->setParameter('isUsed', $isUsed);
if ($hasCredit) {
$query->andWhere('ve.credit_available = :hasCredit')
->setParameter('hasCredit', $hasCredit);
}
$query->groupBy('m.id');
$vehicleItems = $query->getQuery()->getResult();
$models_id = array_column($vehicleItems, 'model_id');
$query = $this->em->getRepository(Model::class)->createQueryBuilder('m')
->where('m.id in (:models)')->setParameter('models', $models_id);
$models = $query->getQuery()->getResult();
if ($hasCredit) {
$nameRoute = CatalogEnum::ROUTE_PORTAL_CREDIT_CATALOG_BM_NAME;
} else {
$nameRoute = CatalogEnum::ROUTE_PORTAL_CATALOG_BM_NAME;
}
/** @var Model $model */
foreach ($models as $model) {
$key = array_search($model->getId(), array_column($vehicleItems, 'model_id'));
$vehicleItems[$key]['model'] = $model;
$vehicleItems[$key]['url'] = $this->router->generate($nameRoute, [
'state' => $state,
'type' => $vehicleType['url'],
'brand' => $brandUrl,
'model' => $model->getUrl()]);
}
return $vehicleItems;
}
public function getStockVehicleModelPriceWithoutParam($state, $type, $brandUrl)
{
$vehicleType = VehicleType::getTypeDataByUrl($type);
$query = $this->em->getRepository(VehicleItem::class)->createQueryBuilder('vi')
->select('m.id as model_id', 'COUNT(ve.id) as vehicle_count', 'MIN(
CASE
WHEN cis.action_price > 0 THEN cis.action_price
WHEN cis.action_price_usd > 0 THEN
CASE
WHEN vi.alt_rate > 0 THEN cis.action_price_usd * vi.alt_rate
ELSE cis.action_price_usd * d.rate
END
WHEN vi.alt_price > 0 THEN vi.alt_price
ELSE
CASE
WHEN vi.alt_rate > 0 THEN vi.price * vi.alt_rate
ELSE vi.price * d.rate
END
END + COALESCE(cis.add_cost, 0)
) AS min_price')
->innerJoin('vi.vehicle', 've', Join::WITH, 've.vehicle_type = :vehicleType')->setParameter('vehicleType', $vehicleType['id'])
->innerJoin('ve.dealer', 'd')
->innerJoin('ve.model', 'm', Join::WITH, 'm.url IS NOT NULL')
->innerJoin('m.brand', 'b', Join::WITH, 'b.url = :brandUrl')->setParameter('brandUrl', $brandUrl)
->innerJoin('vi.variation', 'va')
->innerJoin('va.characteristics', 'vch')
->innerJoin('vch.characteristic', 'ch')
->innerJoin('vch.characteristic_value', 'chv')
->innerJoin('vi.vehicleInStock', 'cis')
->andWhere('vi.state = 1')
->andWhere('cis.state = 1')
->andWhere('ve.state = 1')
->andWhere('ve.is_not_filled = 0')
->andWhere('ve.is_delete != 1')
->andWhere('cis.is_delete != 1')
->andWhere('ve.is_used = 0')
->groupBy('m.id');
$vehicleItems = $query->getQuery()->getResult();
$models_id = array_column($vehicleItems, 'model_id');
$query = $this->em->getRepository(Model::class)->createQueryBuilder('m')
->where('m.id in (:models)')->setParameter('models', $models_id);
$models = $query->getQuery()->getResult();
/** @var Model $model */
foreach ($models as $model) {
$key = array_search($model->getId(), array_column($vehicleItems, 'model_id'));
$vehicleItems[$key]['model'] = $model;
$vehicleItems[$key]['url'] = $this->router->generate(CatalogEnum::ROUTE_PORTAL_CATALOG_FILTER_STOCK, ['state' => $state, 'type' => $vehicleType['url'], 'm-brand' => [$brandUrl => true], 'm-model' => [$model->getUrl() => true]]);
}
return $vehicleItems;
}
public function getVehicleModelPrice($state, $type, $hasCredit, $brandUrl, $param, $value)
{
$isUsed = $state == 'used' ? 1 : 0;
$vehicleType = VehicleType::getTypeDataByUrl($type);
$query = $this->em->getRepository(VehicleItem::class)->createQueryBuilder('vi')
->select(
'm.id AS model_id',
'COUNT(DISTINCT ve.id) AS vehicle_count',
'MIN(CASE
WHEN (ve.is_used = 0) THEN
CASE
WHEN vi.alt_price > 0 THEN vi.alt_price
ELSE
CASE
WHEN vi.alt_rate > 0 THEN vi.alt_rate * vi.price
ELSE vi.price * d.rate
END
END
ELSE
CASE
WHEN vi.alt_price > 0 THEN vi.alt_price
ELSE vi.price
END
END) AS min_price'
);
$query->innerJoin('vi.vehicle', 've', Join::WITH, 've.vehicle_type = :vehicleType')->setParameter('vehicleType', $vehicleType['id'])
->innerJoin('ve.dealer', 'd')
->innerJoin('ve.model', 'm', Join::WITH, 'm.url IS NOT NULL')
->innerJoin('m.brand', 'b', Join::WITH, 'b.url = :brandUrl')->setParameter('brandUrl', $brandUrl)
->innerJoin('vi.variation', 'va')
->innerJoin('va.characteristics', 'vch')
->innerJoin('vch.characteristic', 'ch')
->innerJoin('vch.characteristic_value', 'chv')
->andWhere("ch.id = :param")
->andWhere('chv.url = :url')
->setParameter('param', $param)
->setParameter('url', $value)
->andWhere('vi.state = 1')
->andWhere('vi.sold != 1')
->andWhere('ve.is_used = :is_used')
->setParameter('is_used', $isUsed)
->andWhere('ve.state = 1')
->andWhere('ve.is_not_filled = 0')
->andWhere('ve.is_delete != 1');
if ($hasCredit) {
$query->andWhere('ve.credit_available = :hasCredit')
->setParameter('hasCredit', $hasCredit);
}
$query->groupBy('model_id');
$vehicleItems = $query->getQuery()->getResult();
$models_id = array_column($vehicleItems, 'model_id');
$query = $this->em->getRepository(Model::class)->createQueryBuilder('m')
->where('m.id in (:models)')->setParameter('models', $models_id);
$models = $query->getQuery()->getResult();
if ($hasCredit) {
$nameRoute = CatalogEnum::ROUTE_PORTAL_CREDIT_CATALOG_BM_NAME;
} else {
$nameRoute = CatalogEnum::ROUTE_PORTAL_CATALOG_BM_NAME;
}
/** @var Model $model */
foreach ($models as $model) {
$key = array_search($model->getId(), array_column($vehicleItems, 'model_id'));
$vehicleItems[$key]['model'] = $model;
$vehicleItems[$key]['url'] = $this->router->generate($nameRoute, ['state' => $state, 'type' => $vehicleType['url'], 'brand' => $brandUrl, 'model' => $model->getUrl()]);
}
return $vehicleItems;
}
public function getStockVehicleModelPrice($state, $type, $brandUrl, $param, $value, $searchParams)
{
$vehicleType = VehicleType::getTypeDataByUrl($type);
$query = $this->em->getRepository(VehicleItem::class)->createQueryBuilder('vi')
->select('m.id as model_id', 'COUNT(ve.id) as vehicle_count', 'MIN(
CASE
WHEN cis.action_price > 0 THEN cis.action_price
WHEN cis.action_price_usd > 0 THEN
CASE
WHEN vi.alt_rate > 0 THEN cis.action_price_usd * vi.alt_rate
ELSE cis.action_price_usd * d.rate
END
WHEN vi.alt_price > 0 THEN vi.alt_price
ELSE
CASE
WHEN vi.alt_rate > 0 THEN vi.price * vi.alt_rate
ELSE vi.price * d.rate
END
END + COALESCE(cis.add_cost, 0)
) AS min_price');
$query->innerJoin('vi.vehicle', 've', Join::WITH, 've.vehicle_type = :vehicleType')->setParameter('vehicleType', $vehicleType['id'])
->innerJoin('ve.dealer', 'd')
->innerJoin('ve.model', 'm', Join::WITH, 'm.url IS NOT NULL')
->innerJoin('m.brand', 'b', Join::WITH, 'b.url = :brandUrl')->setParameter('brandUrl', $brandUrl)
->innerJoin('vi.variation', 'va')
->innerJoin('va.characteristics', 'vch')
->innerJoin('vch.characteristic', 'ch')
->innerJoin('vch.characteristic_value', 'chv')
->innerJoin('vi.vehicleInStock', 'cis')
->andWhere("ch.id = :param")
->andWhere('chv.url = :url')
->setParameter('param', $param)
->setParameter('url', $value)
->andWhere('vi.state = 1')
->andWhere('cis.state = 1')
->andWhere('ve.is_used = 0')
->andWhere('ve.state = 1')
->andWhere('ve.is_not_filled = 0')
->andWhere('cis.is_delete != 1')
->andWhere('ve.is_delete != 1')
->groupBy('model_id');
$vehicleItems = $query->getQuery()->getResult();
$models_id = array_column($vehicleItems, 'model_id');
$query = $this->em->getRepository(Model::class)->createQueryBuilder('m')
->where('m.id in (:models)')->setParameter('models', $models_id);
$models = $query->getQuery()->getResult();
/** @var Model $model */
foreach ($models as $model) {
$key = array_search($model->getId(), array_column($vehicleItems, 'model_id'));
$vehicleItems[$key]['model'] = $model;
$vehicleItems[$key]['url'] = $this->router->generate(CatalogEnum::ROUTE_PORTAL_CATALOG_FILTER_STOCK, array_merge($searchParams, ['state' => $state, 'type' => $vehicleType['url'], 'm-model' => [$model->getUrl() => true]]));
}
return $vehicleItems;
}
public function getVehicleTypeBodyPrice($state, $type, $hasCredit, $modelUnique, $group = null)
{
$vehicleType = VehicleType::getTypeDataByUrl($type);
$request = $this->requestStack->getCurrentRequest();
$query = $this->em->getRepository(VehicleItem::class)->createQueryBuilder('vi')
->select('vcv.url', 'vc.id as characteristic_id', 'MIN(case when (case when (ve.is_used = 0 OR (d.id != 6 AND ve.vehicle_type != 4)) then (case when vi.alt_price > 0 then vi.alt_price else (case when vi.alt_rate > 0 then vi.alt_rate * vi.price else vi.price * d.rate end) end) else (case when vi.alt_price > 0 then vi.alt_price else vi.price end) end) = 0 then 9999999999 else (case when (ve.is_used = 0 OR (d.id != 6 AND ve.vehicle_type != 4)) then (case when vi.alt_price > 0 then vi.alt_price else (case when vi.alt_rate > 0 then vi.alt_rate * vi.price else vi.price * d.rate end) end) else (case when vi.alt_price > 0 then vi.alt_price else vi.price end) end) end) as min_price');
if ($request->getLocale() == 'ru') {
$query->addSelect('vcv.value_ru as title');
} else {
$query->addSelect('vcv.value_ua as title');
}
$query->innerJoin('vi.vehicle', 've', Join::WITH, 've.vehicle_type = :vehicleType')->setParameter('vehicleType', $vehicleType['id'])
->innerJoin('ve.dealer', 'd')
->innerJoin('vi.variation', 'v')
->innerJoin('v.characteristics', 'vrc')
->innerJoin('vrc.characteristic', 'vc', Join::WITH, 'vc.model_unique = :modelUnique AND vc.vehicle_type = :vehicleType')
->setParameter('modelUnique', $modelUnique)
->setParameter('vehicleType', $vehicleType['id'])
->leftJoin('ve.recommend_group', 'rg')
->innerJoin('vrc.characteristic_value', 'vcv');
if ($group){
$query->andWhere('rg.url = :group')
->setParameter('group', $group);
}
if ($state === CatalogEnum::CATALOG_USED) {
$query->andWhere('ve.is_used = 1');
} else {
$query->andWhere('ve.is_used = 0');
}
if ($hasCredit) {
$query->andWhere('ve.credit_available = :hasCredit')
->setParameter('hasCredit', $hasCredit);
}
$query->andWhere('vi.state = 1')
->andWhere('ve.state = 1')
->andWhere('vi.sold != 1')
->andWhere('ve.is_not_filled = 0')
->andWhere('ve.is_delete != 1')
->andWhere('vcv.is_action = 1')
->orderBy('vcv.position')
->groupBy('title', 'vcv.url', 'characteristic_id', 'vcv.position');
$vehicleTypeBody = $query->getQuery()->getArrayResult();
if ($hasCredit) {
$nameRoute = CatalogEnum::ROUTE_PORTAL_CREDIT_CATALOG_PARAM;
} else {
$nameRoute = CatalogEnum::ROUTE_PORTAL_CATALOG_PARAM;
}
foreach ($vehicleTypeBody as &$item) {
$item['tag'] = $item['url'];
$item['url'] = $this->router->generate($nameRoute, ['state' => $state, 'type' => $vehicleType['url'], 'param' => $item['characteristic_id'], 'value' => $item['url']]);
}
return $vehicleTypeBody;
}
public function getStockTypeBodyPrice($state, $type, $modelUnique)
{
$vehicleType = VehicleType::getTypeDataByUrl($type);
$request = $this->requestStack->getCurrentRequest();
$query = $this->em->getRepository(InStock::class)->createQueryBuilder('cis')
->select('vcv.url', 'vc.id as characteristic_id', 'MIN(
CASE
WHEN cis.action_price > 0 THEN cis.action_price
WHEN cis.action_price_usd > 0 THEN
CASE
WHEN vi.alt_rate > 0 THEN cis.action_price_usd * vi.alt_rate
ELSE cis.action_price_usd * d.rate
END
WHEN vi.alt_price > 0 THEN vi.alt_price
ELSE
CASE
WHEN vi.alt_rate > 0 THEN vi.price * vi.alt_rate
ELSE vi.price * d.rate
END
END + COALESCE(cis.add_cost, 0)
) AS min_price');
if ($request->getLocale() == 'ru') {
$query->addSelect('vcv.value_ru as title');
} else {
$query->addSelect('vcv.value_ua as title');
}
$query->innerJoin('cis.vehicle_item', 'vi')
->innerJoin('vi.vehicle', 've', Join::WITH, 've.vehicle_type = :vehicleType')->setParameter('vehicleType', $vehicleType['id'])
->innerJoin('ve.dealer', 'd')
->innerJoin('vi.variation', 'v')
->innerJoin('v.characteristics', 'vrc')
->innerJoin('vrc.characteristic', 'vc', Join::WITH, 'vc.model_unique = :modelUnique AND vc.vehicle_type = :vehicleType')
->setParameter('modelUnique', $modelUnique)
->setParameter('vehicleType', $vehicleType['id'])
->innerJoin('vrc.characteristic_value', 'vcv');
$query->andWhere('vi.state = 1')
->andWhere('ve.state = 1')
->andWhere('cis.state = 1')
->andWhere('ve.is_not_filled = 0')
->andWhere('ve.is_delete != 1')
->andWhere('cis.is_delete != 1')
->andWhere('vcv.is_action = 1')
->andWhere('ve.is_used = 0')
->orderBy('vcv.position')
->groupBy('title', 'vcv.url', 'characteristic_id', 'vcv.position');
$vehicleTypeBody = $query->getQuery()->getArrayResult();
foreach ($vehicleTypeBody as &$item) {
$item['tag'] = $item['url'];
$item['url'] = $this->router->generate(CatalogEnum::ROUTE_PORTAL_CATALOG_FILTER_STOCK, ['inStock' => 1, 'state' => $state, 'type' => $vehicleType['url'], 'dynamic' => [$item['characteristic_id']=>[$item['url'] => true]]]);
}
return $vehicleTypeBody;
}
public function getVehicleTopViews($limit = 5, $brand = null)
{
$request = $this->requestStack->getCurrentRequest();
$state = $request->get('state');
$type = $request->get('type');
$hasCredit = $request->get('credit');
$group = $request->get('group');
$vehicleType = VehicleType::getTypeDataByUrl($type);
$isUsed = $state == CatalogEnum::CATALOG_USED;
$query = $this->em->getRepository(Vehicle::class)->createQueryBuilder('ve')
->select('ve', 'MIN(case when (case when (ve.is_used = 0 OR d.id != 6) then (case when vi.alt_price > 0 then vi.alt_price else (case when vi.alt_rate > 0 then vi.alt_rate * vi.price else vi.price * d.rate end) end) else (case when vi.alt_price > 0 then vi.alt_price else vi.price end) end) = 0 then 9999999999 else (case when (ve.is_used = 0 OR d.id != 6) then (case when vi.alt_price > 0 then vi.alt_price else (case when vi.alt_rate > 0 then vi.alt_rate * vi.price else vi.price * d.rate end) end) else (case when vi.alt_price > 0 then vi.alt_price else vi.price end) end) end) as min_price');
$query->innerJoin('ve.vehicle_items', 'vi')
->innerJoin('ve.dealer', 'd')
->innerJoin('ve.model', 'm')
->leftJoin('ve.recommend_group', 'rg');
if ($brand) {
$query->andWhere('m.brand = :brand')->setParameter('brand', $brand);
}
if ($hasCredit) {
$query->andWhere('ve.credit_available = :hasCredit')->setParameter('hasCredit', $hasCredit);
}
if ($group) {
$query->andWhere('rg.url = :group')->setParameter('group', $group);
}
$query->andWhere('ve.vehicle_type = :vehicleType')->setParameter('vehicleType', $vehicleType['id'])
->andWhere('vi.state = 1')
->andWhere('vi.sold = 0')
->andWhere('ve.is_used = :isUsed')->setParameter('isUsed', $isUsed)
->andWhere('ve.state = 1')
->andWhere('ve.is_not_filled = 0')
->andWhere('ve.is_delete != 1')
->andWhere('vi.sold != 1')
->groupBy('ve', 'vi.views')
->orderBy('vi.views', 'DESC')
->addOrderBy('min_price', 'ASC')
->setMaxResults($limit);
$vehicle = $query->getQuery()->getResult();
foreach ($vehicle as &$item) {
$item = $this->vehicleFactory->createByEntity($item[0]);
}
return $vehicle;
}
public function getVehicleItemTopViews($limit = 5, $brand = null, $model = null)
{
$request = $this->requestStack->getCurrentRequest();
$state = $request->get('state');
$type = $request->get('type');
$hasCredit = $request->get('credit');
$vehicleType = VehicleType::getTypeDataByUrl($type);
$isUsed = $state == CatalogEnum::CATALOG_USED;
$query = $this->em->getRepository(VehicleItem::class)->createQueryBuilder('vi')
->select('vi', 'MIN(case when (case when (ve.is_used = 0 OR d.id != 6) then (case when vi.alt_price > 0 then vi.alt_price else (case when vi.alt_rate > 0 then vi.alt_rate * vi.price else vi.price * d.rate end) end) else (case when vi.alt_price > 0 then vi.alt_price else vi.price end) end) = 0 then 9999999999 else (case when (ve.is_used = 0 OR d.id != 6) then (case when vi.alt_price > 0 then vi.alt_price else (case when vi.alt_rate > 0 then vi.alt_rate * vi.price else vi.price * d.rate end) end) else (case when vi.alt_price > 0 then vi.alt_price else vi.price end) end) end) as min_price');
$query
->innerJoin('vi.vehicle', 've')
->innerJoin('ve.model', 'm')
->innerJoin('ve.dealer', 'd');
if ($brand) {
$query->andWhere('m.brand = :brand')->setParameter('brand', $brand);
}
if ($model) {
$query->andWhere('ve.model = :model')->setParameter('model', $model);
}
$query->andWhere('ve.vehicle_type = :vehicleType')->setParameter('vehicleType', $vehicleType['id'])
->andWhere('vi.state = 1')
->andWhere('ve.is_used = :isUsed')->setParameter('isUsed', $isUsed)
->andWhere('ve.state = 1')
->andWhere('ve.is_not_filled = 0')
->andWhere('vi.sold != 1')
->andWhere('ve.is_delete != 1');
if ($hasCredit) {
$query->andWhere('ve.credit_available = :hasCredit')->setParameter('hasCredit', $hasCredit);
}
if ($isUsed) {
$query->groupBy('vi')
->orderBy('ve.date_create', 'DESC');
} else {
$query->groupBy('vi')
->orderBy('ve.views', 'DESC');
}
$query->addOrderBy('min_price', 'ASC')
->setMaxResults($limit);
$vehicleItem = $query->getQuery()->getResult();
/** @var VehicleItem $item */
foreach ($vehicleItem as &$item) {
$item = $this->vehicleFactory->createByVehicleItem($item[0]);
}
return $vehicleItem;
}
public function getRandomBrand($limit = 5, $brand = null)
{
$request = $this->requestStack->getCurrentRequest();
$state = $request->get('state');
$type = $request->get('type');
$hasCredit = $request->get('credit');
$vehicleType = VehicleType::getTypeDataByUrl($type);
$isUsed = $state == CatalogEnum::CATALOG_USED;
$query = $this->em->getRepository(\CoreBundle\Entity\Vehicles\Vehicle::class)->createQueryBuilder('ve')
->select('ve')
->addSelect('MIN(case when (case when (ve.is_used = 0 OR d.id != 6) then (case when vi.alt_price > 0 then vi.alt_price else (case when vi.alt_rate > 0 then vi.alt_rate * vi.price else vi.price * d.rate end) end) else (case when vi.alt_price > 0 then vi.alt_price else vi.price end) end) = 0 then 9999999999 else (case when (ve.is_used = 0 OR d.id != 6) then (case when vi.alt_price > 0 then vi.alt_price else (case when vi.alt_rate > 0 then vi.alt_rate * vi.price else vi.price * d.rate end) end) else (case when vi.alt_price > 0 then vi.alt_price else vi.price end) end) end) as min_price');
$query->innerJoin('ve.vehicle_items', 'vi')
->innerJoin('ve.model', 'm')
->innerJoin('m.brand', 'b')
->innerJoin('ve.dealer', 'd');
if ($brand) {
$query->andWhere('m.brand != :brand')->setParameter('brand', $brand);
}
if ($hasCredit) {
$query->andWhere('ve.credit_available = :hasCredit')->setParameter('hasCredit', $hasCredit);
}
$query->andWhere('ve.vehicle_type = :vehicleType')->setParameter('vehicleType', $vehicleType['id'])
->andWhere('vi.state = 1')
->andWhere('vi.sold = 0')
->andWhere('ve.is_used = :isUsed')->setParameter('isUsed', $isUsed)
->andWhere('ve.state = 1')
->andWhere('ve.is_not_filled = 0')
->andWhere('ve.is_delete != 1')
->andWhere('vi.sold != 1')
->groupBy('b')
->orderBy('min_price', 'ASC')
->setMaxResults($limit);
$vehicles = $query->getQuery()->getResult();
/** @var Vehicle[] $item */
foreach ($vehicles as &$item) {
$item = [
'title' => $item[0]->getModel()->getBrand(),
'url' => $item[0]->getModel()->getBrand()->getUrl(),
'type' => 'brand',
'price' => $item['min_price'],
'creditPayment' => $this->creditModel->getMinPayment($this->vehicleFactory->createByEntity($item[0])),
];
}
return $vehicles;
}
public function getRandomModelBrand($limit = 5, $brand = null, $model = null)
{
$request = $this->requestStack->getCurrentRequest();
$state = $request->get('state');
$type = $request->get('type');
$hasCredit = $request->get('credit');
$vehicleType = VehicleType::getTypeDataByUrl($type);
$isUsed = $state == CatalogEnum::CATALOG_USED;
$query = $this->em->getRepository(\CoreBundle\Entity\Vehicles\Vehicle::class)->createQueryBuilder('ve')
->select('ve')
->addSelect('MIN(case when (case when (ve.is_used = 0 OR d.id != 6) then (case when vi.alt_price > 0 then vi.alt_price else (case when vi.alt_rate > 0 then vi.alt_rate * vi.price else vi.price * d.rate end) end) else (case when vi.alt_price > 0 then vi.alt_price else vi.price end) end) = 0 then 9999999999 else (case when (ve.is_used = 0 OR d.id != 6) then (case when vi.alt_price > 0 then vi.alt_price else (case when vi.alt_rate > 0 then vi.alt_rate * vi.price else vi.price * d.rate end) end) else (case when vi.alt_price > 0 then vi.alt_price else vi.price end) end) end) as min_price');
$query->innerJoin('ve.vehicle_items', 'vi')
->innerJoin('ve.model', 'm')
->innerJoin('m.brand', 'b')
->innerJoin('ve.dealer', 'd');
if ($brand) {
$query->andWhere('m.brand = :brand')->setParameter('brand', $brand);
}
if ($model) {
$query->andWhere('m.id != :model')->setParameter('model', $model);
}
if ($hasCredit) {
$query->andWhere('ve.credit_available = :hasCredit')->setParameter('hasCredit', $hasCredit);
}
$query->andWhere('ve.vehicle_type = :vehicleType')->setParameter('vehicleType', $vehicleType['id'])
->andWhere('vi.state = 1')
->andWhere('vi.sold = 0')
->andWhere('ve.is_used = :isUsed')->setParameter('isUsed', $isUsed)
->andWhere('ve.state = 1')
->andWhere('ve.is_not_filled = 0')
->andWhere('ve.is_delete != 1')
->andWhere('vi.sold != 1')
->groupBy('m')
->orderBy('min_price', 'ASC')
->setMaxResults($limit);
$vehicles = $query->getQuery()->getResult();
/** @var Vehicle[] $item */
foreach ($vehicles as &$item) {
$item = [
'title' => $item[0]->getModel(),
'url' => $item[0]->getModel()->getUrl(),
'type' => 'model',
'price' => $item['min_price'],
'creditPayment' => $this->creditModel->getMinPayment($this->vehicleFactory->createByEntity($item[0])),
];
}
return $vehicles;
}
public function getVehicleImageColor()
{
$request = $this->requestStack->getCurrentRequest();
$firstImage = false;
$vehicleId = $request->get('vehicleId');
$colorId = $request->get('colorId');
/** @var ConfiguratorColor $vehicleColor */
$vehicleColor = $this->em->getRepository(ConfiguratorColor::class)->findOneBy(['id' => $colorId, 'vehicle' => $vehicleId]);
if($vehicleColor && $vehicleColor->getGallery() && !empty($vehicleColor->getGallery()->getGalleryItems())) {
$firstImage = $vehicleColor->getGallery()->getGalleryItems()->first();
}
if (!$firstImage) {
return null;
}
return $this->mediaExtension->getPath($vehicleColor->getGallery()->getGalleryItems()->first()->getMedia(), 'reference');
}
public function getFilterByCatalog()
{
$request = $this->requestStack->getCurrentRequest();
$searchParams = $request->query->all();
$typeData = VehicleType::getTypeDataByUrl($request->get('type'));
$state = $request->get('state');
$isUsed = $state == 'used';
//TODO remove this magic string
$catalogBrands = array_column($this->getBrands($state, $typeData['id']), 'name', 'url');
$catalogModels = array_column($this->getModels($typeData['id'], $isUsed), 'title', 'url');
$catalogCharacteristic = $this->getCharacteristicsByParams($typeData['id'], null, $isUsed);
foreach ($catalogCharacteristic as $key => $value) {
$catalogCharacteristic[$key] = array_column($value['values'], 'value_' . $request->getLocale(), 'url');
}
$filters = [];
foreach ($searchParams as $type => $params) {
if (!is_array($params)) {
$from = $this->translator->trans('new_catalog.from', [], 'portal_base') . ' ' . $params;
$to = ' до ' . $params;
$year = $this->translator->trans('new_catalog.year', [], 'portal_base') . ' ';
$price = $this->translator->trans('new_catalog.price', [], 'portal_base') . ' ';
$mileage = $this->translator->trans('cars.used.mileage', [], 'portal_base') . ' ';
if (!isset($filters['range'])) {
$filters['range'] = [];
}
//TODO remove this magic string
switch ($type) {
case 'yearFrom':
$filters['range']['year'] = $year . $from;
break;
case 'yearTo':
$filters['range']['year'] =
isset($filters['range']['year']) ? $filters['range']['year'] . $to : $year . $to;
break;
case 'priceFrom':
$filters['range']['price'] = $price . $from;
break;
case 'priceTo':
$filters['range']['price'] =
isset($filters['range']['price']) ? $filters['range']['price'] . $to : $price . $to;
break;
case 'mileageFrom':
$filters['range']['mileage'] = $mileage . $from;
break;
case 'mileageTo':
$filters['range']['mileage'] =
isset($filters['range']['mileage']) ? $filters['range']['mileage'] . $to : $mileage . $to;
break;
}
continue;
}
foreach ($params as $key => $value) {
if (empty($filters[$type])) {
//TODO remove this magic string
if ($type == 'm-brand' && empty($filters['brand'])) {
$filters['brand'] = [];
} elseif ($type == 'm-model' && empty($filters['model'])) {
$filters['model'] = [];
} else {
$filters[$type] = [];
}
}
switch ($type) {
//TODO remove this magic string
case 'm-brand':
$filters['brand'][$key] = $catalogBrands[$key];
break;
case 'm-model':
$filters['model'][$key] = $catalogModels[$key];
break;
case 'dynamic':
foreach ($value as $characteristic => $state) {
if (!isset($catalogCharacteristic[$key])) {
continue;
}
$filters[$type][$key][$characteristic] = $catalogCharacteristic[$key][$characteristic];
}
break;
}
}
}
if (empty($filters['range'])) {
unset($filters['range']);
}
}
public function getActualTypes($isUsed, $hasCredit = false, $locale = 'ua')
{
$query = $this->em->getRepository(Vehicle::class)->createQueryBuilder('v')
->select('v.vehicle_type')
->where('v.state = 1')
->andWhere('v.is_delete != 1');
if ($isUsed !== false) {
$query->andWhere('v.is_used = :isUsed')
->setParameter('isUsed', $isUsed);
}
if ($hasCredit) {
$query->andWhere('v.credit_available = :hasCredit')
->setParameter('hasCredit', $hasCredit);
}
$query->groupBy('v.vehicle_type');
$types = $query->getQuery()->getResult();
$ids = [];
foreach ($types as $row) {
$ids[] = $row['vehicle_type'];
}
$result = [];
VehicleType::$locale = $locale;
foreach (VehicleType::getTypesData() as $id => $item) {
if (in_array($id, $ids)) {
$result[] = $item;
}
}
return $result;
}
public function getBrands($state, $type, $hasCredit = false)
{
$query = $this->em->getRepository(Vehicle::class)
->createQueryBuilder('v')
->select('b.id', 'b.name', 'b.url')
->join('v.model', 'm')
->join('m.brand', 'b')
->where('v.state = 1')
->andWhere('v.is_delete != 1')
->andWhere('v.vehicle_type = :type')
->setParameter('type', $type);
if ($hasCredit) {
$query->andWhere('v.credit_available = :hasCredit')
->setParameter('hasCredit', $hasCredit);
}
if ($state == 'used') {
$query->andWhere('v.is_used = 1');
}
if ($state == 'new') {
$query->andWhere('v.is_used = 0');
}
$query->orderBy('b.name');
$query->groupBy('b.id');
return $query->getQuery()->getResult();
}
public function getModels($type, $isUsed, Brand $brand = null)
{
$query = $this->em->getRepository(Vehicle::class)
->createQueryBuilder('v')
->select('m.id', 'm.title', 'm.url')
->join('v.model', 'm')
->join('m.brand', 'b')
->where('v.state = 1')
->andWhere('v.is_delete != 1')
->andWhere('v.vehicle_type = :type')
->setParameter('type', $type);
if ($brand != null) {
$query->andWhere('b.id = :brand')
->setParameter('brand', $brand);
}
if ($isUsed !== false) {
$query->andWhere('v.is_used = :isUsed')
->setParameter('isUsed', $isUsed);
}
$query->orderBy('m.title');
$query->groupBy('m.id');
return $query->getQuery()->getResult();
}
public function getModelsAndArhive($type, $isUsed, Brand $brand = null, $hasCredit = false)
{
$query = $this->em->getRepository(Vehicle::class)
->createQueryBuilder('v')
->select('m.id', 'm.title', 'm.url')
->join('v.model', 'm')
->join('m.brand', 'b')
->andWhere('v.vehicle_type = :type')
->setParameter('type', $type);
if ($brand != null) {
$query->andWhere('b.id = :brand')
->setParameter('brand', $brand);
}
if ($isUsed !== false) {
$query->andWhere('v.is_used = :isUsed')
->setParameter('isUsed', $isUsed);
}
if ($hasCredit) {
$query->andWhere('v.credit_available = :hasCredit')
->setParameter('hasCredit', $hasCredit);
}
$query->orderBy('m.title');
$query->groupBy('m.id');
return $query->getQuery()->getResult();
}
public function getGroups($type, $hasCredit = false)
{
$request = $this->requestStack->getCurrentRequest();
$query = $this->em->getRepository(RecommendGroup::class)
->createQueryBuilder('rg')
->select('rg.id', 'rg.title_'.$request->getLocale().' as title', 'rg.url')
->innerJoin('rg.vehicles','v')
->where('rg.state = :state')
->setParameter('state', true)
->andWhere('v.vehicle_type = :type')
->setParameter('type', $type);
if ($hasCredit) {
$query->andWhere('v.credit_available = :hasCredit')
->setParameter('hasCredit', $hasCredit);
}
return $query->getQuery()->getResult();
}
public function findByParams($routeParams, $searchParams, $pageLimit = 21, $withOutYear = true)
{
$brand = isset($routeParams['brand']) && $routeParams['brand'] != 'all' ? $this->em->getRepository(Brand::class)->findOneBy(['url' => $routeParams['brand']]) : null;
$model = isset($routeParams['model']) && $brand ? $this->em->getRepository(Model::class)->findOneBy(['url' => $routeParams['model'], 'brand' => $brand]) : null;
$group = isset($routeParams['group']) ? $this->em->getRepository(RecommendGroup::class)->findOneBy(['url' => $routeParams['group']]) : null;
$typeData = VehicleType::getTypeDataByUrl($routeParams['type']);
$isUsed = $routeParams['state'] == 'all' ? false : ($routeParams['state'] == 'new' ? 0 : 1);
$hasCredit = filter_var($routeParams['credit'] ?? false, FILTER_VALIDATE_BOOLEAN);
$query = $this->em->getRepository(VehicleItem::class)
->createQueryBuilder('vi')
->select('vi',
'case when (ve.is_used = 0 OR d.id != 6) then (case when vi.alt_price > 0 then vi.alt_price else (case when vi.alt_rate > 0 then vi.alt_rate * vi.price else vi.price * d.rate end) end) else (case when vi.alt_price > 0 then vi.alt_price else vi.price end) end as price',
'case when (case when (ve.is_used = 0 OR d.id != 6) then (case when vi.alt_price > 0 then vi.alt_price else (case when vi.alt_rate > 0 then vi.alt_rate * vi.price else vi.price * d.rate end) end) else (case when vi.alt_price > 0 then vi.alt_price else vi.price end) end) = 0 then 9999999999 else (case when (ve.is_used = 0 OR d.id != 6) then (case when vi.alt_price > 0 then vi.alt_price else (case when vi.alt_rate > 0 then vi.alt_rate * vi.price else vi.price * d.rate end) end) else (case when vi.alt_price > 0 then vi.alt_price else vi.price end) end) end as orderPrice')
->join('vi.variation', 'v');
if (isset($searchParams['inStock']) && $searchParams['inStock'] === 'true') {
$query->join('vi.vehicleInStock', 'vis');
}
$query->join('v.vehicle', 've')
->join('ve.dealer', 'd')
->join('ve.model', 'm')
->join('m.brand', 'b')
->leftJoin('ve.recommend_group', 'rg')
->where('ve.vehicle_type = :type')
->andWhere('vi.state = 1')
->andWhere('ve.state = 1')
->andWhere('ve.is_not_filled = 0')
->andWhere('ve.is_delete != 1')
->setParameter('type', $typeData['id']);
if ($hasCredit) {
$query->andWhere('ve.credit_available = :hasCredit')
->setParameter('hasCredit', $hasCredit);
}
if ($isUsed !== false) {
$mounthAgo = (new DateTime())->modify('-30 days');
$query->andWhere('ve.is_used = :is_used')
->setParameter('is_used', $isUsed)
->andWhere('vi.date_of_sale > :mounthAgo OR vi.date_of_sale IS NULL')
->setParameter('mounthAgo', $mounthAgo)
->setParameter('is_used', $isUsed);
}
if ($model) {
$query->andWhere('m.id = :model')
->setParameter('model', $model->getId());
}
if ($brand && !$model) {
$query->andWhere('b.id = :brand')
->setParameter('brand', $brand->getId());
}
if ($group) {
$query->andWhere('rg.id = :group')
->setParameter('group', $group->getId());
}
if (count($searchParams)) {
foreach ($searchParams as $key => $item) {
switch ($key) {
case 'm-brand':
if (!isset($searchParams['m-model'])) {
$query->andWhere('b.url IN (:mBrand)')
->setParameter('mBrand', array_keys($item));
} else {
$filterModelIds = $this->findModelByParams(array_keys($item), array_keys($searchParams['m-model']));
$query->andWhere('m.id IN (:modelIds)')
->setParameter('modelIds', $filterModelIds);
}
break;
case 'm-model':
if (!isset($searchParams['m-brand']) || count($searchParams['m-brand']) == 1) {
$query->andWhere('m.url IN (:mModel)')
->setParameter('mModel', array_keys($item));
}
break;
case 'group':
$query->andWhere('rg.url IN (:group)')
->setParameter('group', array_keys($item));
break;
case 'priceFrom':
if ($item > 0) {
$query->andHaving('price >= :minPrice')
->setParameter('minPrice', $item);
}
break;
case 'priceTo':
if ($item > 0) {
$query->andHaving('price <= :priceMax')
->setParameter('priceMax', $item);
}
break;
case 'yearFrom':
if ($item > 0) {
$query->andHaving('vi.year >= :yearMin')
->setParameter('yearMin', $item);
}
break;
case 'yearTo':
if ($item > 0) {
$query->andHaving('vi.year <= :yearMax')
->setParameter('yearMax', $item);
}
break;
case 'mileageFrom':
if ($item > 0) {
$query->andHaving('vi.mileage >= :mileageFrom')
->setParameter('mileageFrom', $item);
}
break;
case 'mileageTo':
if ($item > 0) {
$query->andHaving('vi.mileage <= :mileageTo')
->setParameter('mileageTo', $item);
}
break;
case 'recommend':
if ($item > 0) {
$query->andWhere('rg.id = :recommend')
->setParameter('recommend', $item);
}
break;
case 'hasNDS':
if ($item == 'true') {
$query->andWhere('vi.has_nds = 1');
}
break;
case 'isSelect':
if ($item == 'true') {
$query->andWhere($query->expr()->notIn('ve.dealer', Catalog::NOT_VIDI_SELECT_DEALERS));
}
break;
}
}
}
$catalogCharacteristics = $this->getCatalogCharacteristicsId();
$dynamic = isset($searchParams['dynamic']) && count($searchParams['dynamic']) ? $searchParams['dynamic'] : [];
if (isset($routeParams['param']) && isset($routeParams['value'])) {
// Exclusion for catalog parameter "Years"
if ($routeParams['param'] == SeoMetaTag::YEARS_PARAMETER && $withOutYear) {
$query->andHaving('vi.year = :year')
->setParameter('year', $routeParams['value']);
} else {
$charValue = $this->em->getRepository(CharacteristicValue::class)->findOneBy(
['url' => $routeParams['value'], 'characteristic' => $routeParams['param']]
);
if ($charValue) {
$dynamic[$charValue->getCharacteristic()->getId()][$charValue->getUrl()] = 1;
}
}
}
// if (isset($routeParams['group']) && !is_array($routeParams['group'])){
// $query->andWhere('rg.url = :group')
// ->setParameter('group', $routeParams['group']);
// }
if (count($dynamic)) {
foreach ($dynamic as $cId => $params) {
$alias = 'vc' . $cId;
$fKey = 'charId' . $cId;
$tKey = 'cids' . $cId;
$query->join('v.characteristics', $alias);
if (in_array($cId, $catalogCharacteristics)) {
$cvIds = [];
foreach ($params as $vcUrl => $true) {
$cValue = $this->em->getRepository(CharacteristicValue::class)->findOneBy(['url' => $vcUrl, 'characteristic' => (int)$cId]);
if ($cValue) {
$cvIds[] = $cValue->getId();
}
}
if (!empty($cvIds)) {
$query->andWhere($alias . '.characteristic = :' . $fKey)
->setParameter($fKey, $cId);
$query->andWhere($alias . '.characteristic_value IN (:' . $tKey . ')')
->setParameter($tKey, $cvIds);
}
} else {
$localeValue = 'value_' . $routeParams['_locale'];
if (!empty($params['from']) && $params['from'] > 0) {
$query->andWhere($alias . '.characteristic = :' . $fKey . ' AND INT(' . $alias . '.' . $localeValue . ') >= :from')
->setParameter($fKey, $cId)
->setParameter('from', $params['from']);
}
if (!empty($params['to']) && $params['to'] > 0) {
$query->andWhere($alias . '.characteristic = :' . $fKey . ' AND INT(' . $alias . '.' . $localeValue . ') <= :to')
->setParameter($fKey, $cId)
->setParameter('to', $params['to']);
}
}
}
}
$allResult = $query->getQuery()->getResult();
$prices = [];
$creditPayments = [];
foreach ($allResult as $result) {
if ($result['price'] != 0) {
$prices[] = $result['price'];
}
if ($result['price'] != 0) {
$creditPayments[] = $this->creditModel->getMinPayment($this->vehicleFactory->createByEntity($result[0]->getVehicle()));
}
}
if($prices){
$minMaxPrice = [
'minPrice' => count($prices) ? min($prices) : 0,
'maxPrice' => count($prices) ? max($prices) : 0,
];
}
$minMaxCreditPayment = [
'min' => count($prices) ? min($prices) : 0,
'max' => count($prices) ? max($prices) : 0,
];
$totalCount = count($allResult);
unset($allResult);
$limit = $pageLimit;
$page = $searchParams['page'] ?? 1;
$start = ($page - 1) * $limit;
$query
->groupBy('vi')
->orderBy('vi.sold', 'ASC');
if (isset($searchParams['sortOrder'])) {
$sortData = explode('-', $searchParams['sortOrder']);
$sortField = $sortData[0] ?? 'price';
$sortAD = isset($sortData[1]) && in_array($sortData[1], ['ASC', 'DESC']) ? $sortData[1] : 'ASC';
switch ($sortField) {
case 'price':
$field = $sortAD == 'DESC' ? 'price' : 'orderPrice';
$query->addOrderBy($field, $sortAD);
$query->addOrderBy('b.name', 'ASC');
$query->addOrderBy('m.title', 'ASC');
break;
case 'year':
$query->addOrderBy('vi.year', $sortAD);
$query->addOrderBy('b.name', 'ASC');
$query->addOrderBy('m.title', 'ASC');
break;
case 'mileage':
$query->addOrderBy('vi.mileage', $sortAD);
$query->addOrderBy('b.name', 'ASC');
$query->addOrderBy('m.title', 'ASC');
break;
default:
$query->addOrderBy('orderPrice', 'ASC');
$query->addOrderBy('b.name', 'ASC');
$query->addOrderBy('m.title', 'ASC');
break;
}
} else {
$query->addOrderBy('orderPrice', 'ASC');
}
$query->setFirstResult($start)
->setMaxResults($limit);
return [
'count' => $totalCount,
'data' => $query->getQuery()->getResult(),
'minMaxPrice' => $minMaxPrice ?? null,
'minMaxCreditPayment' => $minMaxCreditPayment
];
}
public function findStockByParams($routeParams, $searchParams, $pageLimit = 21, $withOutYear = true)
{
$brand = isset($routeParams['brand']) && $routeParams['brand'] != 'all' ? $this->em->getRepository(Brand::class)->findOneBy(['url' => $routeParams['brand']]) : null;
$model = isset($routeParams['model']) && $brand ? $this->em->getRepository(Model::class)->findOneBy(['url' => $routeParams['model'], 'brand' => $brand]) : null;
$typeData = VehicleType::getTypeDataByUrl($routeParams['type']);
$query = $this->em->getRepository(InStock::class)
->createQueryBuilder('cis')
->select('cis',
'vi',
'(CASE
WHEN cis.action_price > 0 THEN cis.action_price
WHEN cis.action_price_usd > 0 THEN
(CASE
WHEN vi.alt_rate > 0 THEN cis.action_price_usd * vi.alt_rate
ELSE cis.action_price_usd * d.rate
END)
WHEN vi.alt_price > 0 THEN vi.alt_price
ELSE
(CASE
WHEN vi.alt_rate > 0 THEN vi.price * vi.alt_rate
ELSE vi.price * d.rate
END)
END) + COALESCE(cis.add_cost, 0) AS price',
'(CASE
WHEN (CASE
WHEN cis.action_price > 0 THEN cis.action_price
WHEN cis.action_price_usd > 0 THEN
(CASE
WHEN vi.alt_rate > 0 THEN cis.action_price_usd * vi.alt_rate
ELSE cis.action_price_usd * d.rate
END)
WHEN vi.alt_price > 0 THEN vi.alt_price
ELSE
(CASE
WHEN vi.alt_rate > 0 THEN vi.price * vi.alt_rate
ELSE vi.price * d.rate
END)
END + COALESCE(cis.add_cost, 0)) = 0 THEN 9999999999
ELSE
(CASE
WHEN cis.action_price > 0 THEN cis.action_price
WHEN cis.action_price_usd > 0 THEN
(CASE
WHEN vi.alt_rate > 0 THEN cis.action_price_usd * vi.alt_rate
ELSE cis.action_price_usd * d.rate
END)
WHEN vi.alt_price > 0 THEN vi.alt_price
ELSE
(CASE
WHEN vi.alt_rate > 0 THEN vi.price * vi.alt_rate
ELSE vi.price * d.rate
END)
END + COALESCE(cis.add_cost, 0))
END) AS orderPrice')
->innerJoin('cis.vehicle_item', 'vi')
->innerJoin('vi.variation', 'v')
->innerJoin('v.characteristics', 'vc')
->innerJoin('vc.characteristic_value', 'vcv')
->innerJoin('vi.equipment', 'e')
->innerJoin('vi.vehicle', 'vehicle')
->join('vehicle.dealer', 'd')
->join('vehicle.model', 'm')
->join('m.brand', 'b')
->leftJoin('vehicle.recommend_group', 'rg')
->where('cis.state = 1 and cis.is_delete != 1')
->andWhere('vehicle.is_delete != 1')
->andWhere('vehicle.is_delete != 1')
->andWhere('vehicle.state = 1');
if ($model) {
$query->andWhere('m.id = :model')
->setParameter('model', $model->getId());
}
if ($brand && !$model) {
$query->andWhere('b.id = :brand')
->setParameter('brand', $brand->getId());
}
if (count($searchParams)) {
foreach ($searchParams as $key => $item) {
switch ($key) {
case 'm-brand':
if (!isset($searchParams['m-model'])) {
$query->andWhere('b.url IN (:mBrand)')
->setParameter('mBrand', array_keys($item));
} else {
$filterModelIds = $this->findModelByParams(array_keys($item), array_keys($searchParams['m-model']));
$query->andWhere('m.id IN (:modelIds)')
->setParameter('modelIds', $filterModelIds);
}
break;
case 'm-model':
if (!isset($searchParams['m-brand']) || count($searchParams['m-brand']) == 1) {
$query->andWhere('m.url IN (:mModel)')
->setParameter('mModel', array_keys($item));
}
break;
case 'group':
$query->andWhere('rg.url IN (:group)')
->setParameter('group', array_keys($item));
break;
case 'priceFrom':
if ($item > 0) {
$query->andHaving('price >= :minPrice')
->setParameter('minPrice', $item);
}
break;
case 'priceTo':
if ($item > 0) {
$query->andHaving('price <= :priceMax')
->setParameter('priceMax', $item);
}
break;
case 'yearFrom':
if ($item > 0) {
$query->andHaving('vi.year >= :yearMin')
->setParameter('yearMin', $item);
}
break;
case 'yearTo':
if ($item > 0) {
$query->andHaving('vi.year <= :yearMax')
->setParameter('yearMax', $item);
}
break;
case 'mileageFrom':
if ($item > 0) {
$query->andHaving('vi.mileage >= :mileageFrom')
->setParameter('mileageFrom', $item);
}
break;
case 'mileageTo':
if ($item > 0) {
$query->andHaving('vi.mileage <= :mileageTo')
->setParameter('mileageTo', $item);
}
break;
case 'recommend':
if ($item > 0) {
$query->andWhere('rg.id = :recommend')
->setParameter('recommend', $item);
}
break;
case 'hasNDS':
if ($item == 'true') {
$query->andWhere('vi.has_nds = 1');
}
break;
case 'isSelect':
if ($item == 'true') {
$query->andWhere($query->expr()->notIn('ve.dealer', Catalog::NOT_VIDI_SELECT_DEALERS));
}
break;
}
}
}
$catalogCharacteristics = $this->getCatalogCharacteristicsId();
$dynamic = isset($searchParams['dynamic']) && count($searchParams['dynamic']) ? $searchParams['dynamic'] : [];
if (isset($routeParams['param']) && isset($routeParams['value'])) {
// Exclusion for catalog parameter "Years"
if ($routeParams['param'] == SeoMetaTag::YEARS_PARAMETER && $withOutYear) {
$query->andHaving('vi.year = :year')
->setParameter('year', $routeParams['value']);
} else {
$charValue = $this->em->getRepository(CharacteristicValue::class)->findOneBy(
['url' => $routeParams['value'], 'characteristic' => $routeParams['param']]
);
if ($charValue) {
$dynamic[$charValue->getCharacteristic()->getId()][$charValue->getUrl()] = 1;
}
}
}
if (count($dynamic)) {
foreach ($dynamic as $cId => $params) {
$alias = 'vc' . $cId;
$fKey = 'charId' . $cId;
$tKey = 'cids' . $cId;
$query->join('v.characteristics', $alias);
if (in_array($cId, $catalogCharacteristics)) {
$cvIds = [];
foreach ($params as $vcUrl => $true) {
$cValue = $this->em->getRepository(CharacteristicValue::class)->findOneBy(['url' => $vcUrl, 'characteristic' => (int)$cId]);
if ($cValue) {
$cvIds[] = $cValue->getId();
}
}
if (!empty($cvIds)) {
$query->andWhere($alias . '.characteristic = :' . $fKey)
->setParameter($fKey, $cId);
$query->andWhere($alias . '.characteristic_value IN (:' . $tKey . ')')
->setParameter($tKey, $cvIds);
}
} else {
$localeValue = 'value_' . $routeParams['_locale'];
if (!empty($params['from']) && $params['from'] > 0) {
$query->andWhere($alias . '.characteristic = :' . $fKey . ' AND INT(' . $alias . '.' . $localeValue . ') >= :from')
->setParameter($fKey, $cId)
->setParameter('from', $params['from']);
}
if (!empty($params['to']) && $params['to'] > 0) {
$query->andWhere($alias . '.characteristic = :' . $fKey . ' AND INT(' . $alias . '.' . $localeValue . ') <= :to')
->setParameter($fKey, $cId)
->setParameter('to', $params['to']);
}
}
}
}
$allResult = $query->getQuery()->getResult();
$totalCount = count($allResult);
unset($allResult);
$limit = $pageLimit;
$page = $searchParams['page'] ?? 1;
$start = ($page - 1) * $limit;
$query
->groupBy('cis');
if (isset($searchParams['sortOrder'])) {
$sortData = explode('-', $searchParams['sortOrder']);
$sortField = $sortData[0] ?? 'price';
$sortAD = isset($sortData[1]) && in_array($sortData[1], ['ASC', 'DESC']) ? $sortData[1] : 'ASC';
switch ($sortField) {
case 'price':
$field = $sortAD == 'DESC' ? 'price' : 'orderPrice';
$query->orderBy($field, $sortAD);
$query->addOrderBy('b.name', 'ASC');
$query->addOrderBy('m.title', 'ASC');
break;
case 'year':
$query->orderBy('vi.year', $sortAD);
$query->addOrderBy('b.name', 'ASC');
$query->addOrderBy('m.title', 'ASC');
break;
case 'mileage':
$query->orderBy('vi.mileage', $sortAD);
$query->addOrderBy('b.name', 'ASC');
$query->addOrderBy('m.title', 'ASC');
break;
default:
$query->orderBy('orderPrice', 'ASC');
$query->addOrderBy('b.name', 'ASC');
$query->addOrderBy('m.title', 'ASC');
break;
}
} else {
$query->orderBy('orderPrice', 'ASC');
}
$query->setFirstResult($start)
->setMaxResults($limit);
return [
'count' => $totalCount,
'data' => $query->getQuery()->getResult()
];
}
public function noFindVehicleByParams($routeParams, $searchParams, $pageLimit = 21)
{
$typeData = VehicleType::getTypeDataByUrl($routeParams['type']);
$isUsed = $routeParams['state'] == 'all' ? false : ($routeParams['state'] == 'new' ? 0 : 1);
$query = $this->em->getRepository(VehicleItem::class)
->createQueryBuilder('vi')
->select('vi',
'case when (ve.is_used = 0 OR d.id != 6) then (case when vi.alt_price > 0 then vi.alt_price else (case when vi.alt_rate > 0 then vi.alt_rate * vi.price else vi.price * d.rate end) end) else (case when vi.alt_price > 0 then vi.alt_price else vi.price end) end as price',
'case when (case when (ve.is_used = 0 OR d.id != 6) then (case when vi.alt_price > 0 then vi.alt_price else (case when vi.alt_rate > 0 then vi.alt_rate * vi.price else vi.price * d.rate end) end) else (case when vi.alt_price > 0 then vi.alt_price else vi.price end) end) = 0 then 9999999999 else (case when (ve.is_used = 0 OR d.id != 6) then (case when vi.alt_price > 0 then vi.alt_price else (case when vi.alt_rate > 0 then vi.alt_rate * vi.price else vi.price * d.rate end) end) else (case when vi.alt_price > 0 then vi.alt_price else vi.price end) end) end as orderPrice')
->join('vi.variation', 'v')
->join('v.vehicle', 've', Join::WITH, 've.is_used = :is_used')->setParameter('is_used', $isUsed);
if (isset($searchParams['inStock']) && $searchParams['inStock'] === 'true') {
$query->join('vi.vehicleInStock', 'vis');
}
$query->join('ve.dealer', 'd')
->andWhere('ve.vehicle_type = :type')
->andWhere('vi.state = 1')
->andWhere('ve.state = 1')
->andWhere('ve.is_not_filled = 0')
->andWhere('ve.is_delete != 1')
->setParameter('type', $typeData['id'])
->orderBy('v.id', 'DESC')
->setMaxResults($pageLimit);
return [
'count' => 0,
'data' => $query->getQuery()->getResult()
];
}
public function noFindStockVehicleByParams($routeParams, $searchParams, $pageLimit = 21)
{
$typeData = VehicleType::getTypeDataByUrl($routeParams['type']);
$isUsed = $routeParams['state'] == 'all' ? false : ($routeParams['state'] == 'new' ? 0 : 1);
$query = $this->em->getRepository(InStock::class)
->createQueryBuilder('cis')
->select('cis',
'vi',
'(CASE
WHEN cis.action_price > 0 THEN cis.action_price
WHEN cis.action_price_usd > 0 THEN
(CASE
WHEN vi.alt_rate > 0 THEN cis.action_price_usd * vi.alt_rate
ELSE cis.action_price_usd * d.rate
END)
WHEN vi.alt_price > 0 THEN vi.alt_price
ELSE
(CASE
WHEN vi.alt_rate > 0 THEN vi.price * vi.alt_rate
ELSE vi.price * d.rate
END)
END) + COALESCE(cis.add_cost, 0) AS price',
'(CASE
WHEN (CASE
WHEN cis.action_price > 0 THEN cis.action_price
WHEN cis.action_price_usd > 0 THEN
(CASE
WHEN vi.alt_rate > 0 THEN cis.action_price_usd * vi.alt_rate
ELSE cis.action_price_usd * d.rate
END)
WHEN vi.alt_price > 0 THEN vi.alt_price
ELSE
(CASE
WHEN vi.alt_rate > 0 THEN vi.price * vi.alt_rate
ELSE vi.price * d.rate
END)
END + COALESCE(cis.add_cost, 0)) = 0 THEN 9999999999
ELSE
(CASE
WHEN cis.action_price > 0 THEN cis.action_price
WHEN cis.action_price_usd > 0 THEN
(CASE
WHEN vi.alt_rate > 0 THEN cis.action_price_usd * vi.alt_rate
ELSE cis.action_price_usd * d.rate
END)
WHEN vi.alt_price > 0 THEN vi.alt_price
ELSE
(CASE
WHEN vi.alt_rate > 0 THEN vi.price * vi.alt_rate
ELSE vi.price * d.rate
END)
END + COALESCE(cis.add_cost, 0))
END) AS orderPrice')
->innerJoin('cis.vehicle_item', 'vi')
->innerJoin('vi.variation', 'v')
->innerJoin('vi.equipment', 'e')
->innerJoin('vi.vehicle', 'vehicle')
->join('vehicle.dealer', 'd')
->where('cis.state = 1 and cis.is_delete != 1')
->andWhere('vehicle.is_delete != 1')
->andWhere('vehicle.is_delete != 1')
->andWhere('vehicle.state = 1')
->orderBy('v.id', 'DESC')
->setMaxResults($pageLimit);
return [
'count' => 0,
'data' => $query->getQuery()->getResult()
];
}
public function findModelByParams($urlBrands = null, $urlModels = null)
{
$filterBrands = $this->em->getRepository(Brand::class)->getBrandsByUrl($urlBrands);
$filterModelIds = [];
/** @var Brand $brand */
foreach ($filterBrands as $brand) {
$models = [];
$modelSearch = [];
/** @var Model $model */
foreach ($brand->getModels() as $model) {
if (!$model->getUrl()) {
continue;
}
if (in_array($model->getUrl(), $urlModels)) {
$modelSearch[] = $model->getId();
}
$models[] = $model->getId();
}
$filterModelIds = (empty($modelSearch)) ? array_merge($filterModelIds, $models) : array_merge($filterModelIds, $modelSearch);
}
return $filterModelIds;
}
public function getCharacteristicsByParams($type, Brand $brand = null, Model $model = null, $isUsed)
{
$query = $this->em->getRepository(VariationCharacteristic::class)
->createQueryBuilder('vc')
->select('c.id', 'c.title_ua', 'c.title_ru', 'cv.url', 'cv.value_ua', 'cv.value_ru')
->join('vc.characteristic_value', 'cv')
->join('vc.characteristic', 'c', Join::WITH, 'c.vehicle_type = :type')->setParameter('type', $type)
->join('vc.variation', 'v', Join::WITH, 'v.state = 1')
->where('c.in_filter = 1');
if ($isUsed !== false) {
$query
->join('v.vehicle', 've', Join::WITH, 've.state = 1 AND ve.is_delete <> 1 AND ve.is_used = :is_used')
->join('ve.model', 'm')
->setParameter('is_used', $isUsed);
} else {
$query
->join('v.vehicle', 've', Join::WITH, 've.state = 1 AND ve.is_delete <> 1')
->join('ve.model', 'm');
}
if ($brand) {
$query
->join('m.brand', 'b')
->andWhere('b.id = :brand')
->setParameter('brand', $brand->getId());
}
if ($model) {
$query->andWhere('m.id = :model')->setParameter('model', $model->getId());
}
$query->groupBy('cv', 'c');
$characteristics = $query->getQuery()->getResult();
$result = [];
foreach ($characteristics as $row) {
if (!isset($result[$row['id']])) {
$result[$row['id']] = [
'id' => $row['id'],
'title_ru' => $row['title_ru'],
'title_ua' => $row['title_ua'],
'values' => [],
];
}
$result[$row['id']]['values'][] = [
'url' => $row['url'],
'value_ru' => $row['value_ru'],
'value_ua' => $row['value_ua'],
];
}
return $result;
}
public function buildFilters($routeParams, $locale)
{
$brand = isset($routeParams['brand']) && $routeParams['brand'] != 'all' ? $this->em->getRepository(Brand::class)->findOneBy(['url' => $routeParams['brand']]) : null;
$model = isset($routeParams['model']) && $brand ? $this->em->getRepository(Model::class)->findOneBy(['url' => $routeParams['model'], 'brand' => $brand, 'state' => true]) : null;
$typeData = VehicleType::getTypeDataByUrl($routeParams['type']);
$isUsed = $routeParams['state'] == 'all' ? false : ($routeParams['state'] == 'new' ? 0 : 1);
$hasCredit = filter_var($routeParams['credit'] ?? false, FILTER_VALIDATE_BOOLEAN);
$vehicleTypes = [];
foreach ($this->getActualTypes($isUsed, $hasCredit, $locale) as $oneType) {
//Todo not exist template
if ($oneType['url'] === 'special') {
continue;
}
$vehicleTypes[] = [
'url' => $oneType['url'],
'title' => $oneType['title'],
];
}
$filters = [
'types' => $vehicleTypes,
'dynamic' => [],
'group' => [],
'brand' => [],
];
$query = $this->em->getRepository(VariationCharacteristic::class)
->createQueryBuilder('vc')
->select('c.id', 'c.title_ua', 'c.title_ru', 'cv.url', 'cv.value_ua', 'cv.value_ru')
->leftJoin('vc.characteristic_value', 'cv')
->join('vc.characteristic', 'c', Join::WITH, 'c.vehicle_type = :type')
->setParameter('type', $typeData['id'])
->join('vc.variation', 'v', Join::WITH, 'v.state = 1')
->where('c.in_filter = 1');
if ($isUsed !== false) {
$query
->join('v.vehicle', 've', Join::WITH, 've.state = 1 AND ve.is_delete <> 1 AND ve.is_used = :is_used')
->setParameter('is_used', $isUsed);
} else {
$query
->join('v.vehicle', 've', Join::WITH, 've.state = 1 AND ve.is_delete <> 1');
}
if ($model) {
$query
->join('ve.model', 'm')
->andWhere('m.id = :model')
->setParameter('model', $model->getId());
}
if ($brand && !$model) {
$query
->join('ve.model', 'm')
->join('m.brand', 'b')
->andWhere('b.id = :brand')
->setParameter('brand', $brand->getId());
}
$query->orderBy('cv.position');
if($locale == 'ru'){
$query->addOrderBy('cv.value_ru');
}else{
$query->addOrderBy('cv.value_ua');
}
$query->groupBy('cv', 'c');
$result = $query->getQuery()->getResult();
foreach ($result as $row) {
if (!isset($filters['dynamic'][$row['id']])) {
$filters['dynamic'][$row['id']] = [
'id' => $row['id'],
'title' => $locale == 'ru' ? $row['title_ru'] : $row['title_ua'],
'values' => [],
];
}
if (isset($row['url'])) {
$filters['dynamic'][$row['id']]['values'][] = [
'url' => $row['url'],
'value' => $locale == 'ru' ? $row['value_ru'] : $row['value_ua'],
];
} else {
$filters['dynamic'][$row['id']]['values'] = null;
}
}
$query = $this->em->getRepository(RecommendGroup::class)
->createQueryBuilder('rg')
->join('rg.vehicles', 'v')
->where('rg.state = :state')->setParameter('state', true)
->andWhere('v.vehicle_type = :type')->setParameter('type', $typeData['id'])
->andWhere('v.state = 1 AND v.is_delete <> 1');
if ($isUsed !== false) {
$query->andWhere('v.is_used = :is_used')->setParameter('is_used', $isUsed);
}
if ($model) {
$query
->join('v.model', 'm')
->andWhere('m.id = :model')
->setParameter('model', $model->getId());
}
if ($brand && !$model) {
$query
->join('v.model', 'm')
->join('m.brand', 'b')
->andWhere('b.id = :brand')
->setParameter('brand', $brand->getId());
}
$query->orderBy('rg.position');
$recommendGroup = $query->getQuery()->getResult();
/** @var RecommendGroup $group */
foreach ($recommendGroup as $group) {
if ($group->getUrl()) {
$filters['group'][] = [
'url' => $group->getUrl(),
'title' => $locale == 'ru' ? $group->getTitleRu() : $group->getTitleUa(),
];
} else {
$filters['group'] = null;
}
}
$query = $this->em->getRepository(Vehicle::class)
->createQueryBuilder('v')
->select('b.url as burl', 'b.name', 'm.id', 'm.url', 'm.title', 'logo.id as logo_id')
->join('v.model', 'm')
->join('m.brand', 'b')
->join('b.logo', 'logo')
->where('v.vehicle_type = :type')
->andWhere('v.state = 1')
->andWhere('v.is_delete != 1')
->setParameter('type', $typeData['id']);
if ($isUsed !== false) {
$query->andWhere('v.is_used = :is_used')
->setParameter('is_used', $isUsed);
}
$brands = $query->getQuery()->getResult();
foreach ($brands as $line) {
if (!isset($filters['brand'][$line['burl']])) {
$logo = $this->em->getRepository(Media::class)->find($line['logo_id']);
$filters['brand'][$line['burl']] = [
'url' => $line['burl'],
'title' => $line['name'],
'link' => $this->router->generate('portal_new_catalog_b', [
'state' => $routeParams['state'],
'type' => $typeData['url'],
'brand' => $line['burl'],
]),
'logo' => $this->mediaExtension->getPath($logo, 'menu'),
'count' => 1,
'models' => [],
];
}
$filters['brand'][$line['burl']]['count'] += 1;
$filters['brand'][$line['burl']]['models'][$line['id']] = [
'url' => $line['url'],
'title' => $line['title'],
];
}
usort($filters['brand'], function ($a, $b) {
if ($a['title'] == $b['title']) {
return 1;
}
return $a['title'] > $b['title'] ? 1 : -1;
});
return $filters;
}
public function buildStockFilters($routeParams, $locale)
{
$brand = isset($routeParams['brand']) && $routeParams['brand'] != 'all' ? $this->em->getRepository(Brand::class)->findOneBy(['url' => $routeParams['brand']]) : null;
$model = isset($routeParams['model']) && $brand ? $this->em->getRepository(Model::class)->findOneBy(['url' => $routeParams['model'], 'brand' => $brand, 'state' => true]) : null;
$typeData = VehicleType::getTypeDataByUrl($routeParams['type']);
$filters = [
'dynamic' => [],
'group' => [],
'brand' => [],
];
$query = $this->em->getRepository(VariationCharacteristic::class)
->createQueryBuilder('vc')
->select('c.id', 'c.title_ua', 'c.title_ru', 'cv.url', 'cv.value_ua', 'cv.value_ru')
->leftJoin('vc.characteristic_value', 'cv')
->join('vc.characteristic', 'c', Join::WITH, 'c.vehicle_type = :type')
->setParameter('type', $typeData['id'])
->join('vc.variation', 'v', Join::WITH, 'v.state = 1')
->where('c.in_filter = 1')
->leftJoin('v.vehicle_items', 'vi')
->leftJoin('vi.vehicleInStock', 'cis')
->andWhere('cis.state = 1 and cis.is_delete != 1');
$query
->join('v.vehicle', 've', Join::WITH, 've.state = 1 AND ve.is_delete != 1');
if ($model) {
$query
->join('ve.model', 'm')
->andWhere('m.id = :model')
->setParameter('model', $model->getId());
}
if ($brand && !$model) {
$query
->join('ve.model', 'm')
->join('m.brand', 'b')
->andWhere('b.id = :brand')
->setParameter('brand', $brand->getId());
}
$query->orderBy('cv.position');
if($locale == 'ru'){
$query->addOrderBy('cv.value_ru');
}else{
$query->addOrderBy('cv.value_ua');
}
$query->groupBy('cv', 'c');
$result = $query->getQuery()->getResult();
foreach ($result as $row) {
if (!isset($filters['dynamic'][$row['id']])) {
$filters['dynamic'][$row['id']] = [
'id' => $row['id'],
'title' => $locale == 'ru' ? $row['title_ru'] : $row['title_ua'],
'values' => [],
];
}
if (isset($row['url'])) {
$filters['dynamic'][$row['id']]['values'][] = [
'url' => $row['url'],
'value' => $locale == 'ru' ? $row['value_ru'] : $row['value_ua'],
];
} else {
$filters['dynamic'][$row['id']]['values'] = null;
}
}
$query = $this->em->getRepository(RecommendGroup::class)
->createQueryBuilder('rg')
->join('rg.vehicles', 'v')
->where('rg.state = :state')->setParameter('state', true)
->andWhere('v.vehicle_type = :type')->setParameter('type', $typeData['id'])
->andWhere('v.state = 1 AND v.is_delete != 1');
if ($model) {
$query
->join('v.model', 'm')
->andWhere('m.id = :model')
->setParameter('model', $model->getId());
}
if ($brand && !$model) {
$query
->join('v.model', 'm')
->join('m.brand', 'b')
->andWhere('b.id = :brand')
->setParameter('brand', $brand->getId());
}
$query->orderBy('rg.position');
$recommendGroup = $query->getQuery()->getResult();
/** @var RecommendGroup $group */
foreach ($recommendGroup as $group) {
if ($group->getUrl()) {
$filters['group'][] = [
'url' => $group->getUrl(),
'title' => $locale == 'ru' ? $group->getTitleRu() : $group->getTitleUa(),
];
} else {
$filters['group'] = null;
}
}
$query = $this->em->getRepository(InStock::class)
->createQueryBuilder('cis')
->select('b.url as burl', 'b.name ', 'm.id ', 'm.url', 'm.title', 'logo.id as logo_id')
->innerJoin('cis.vehicle_item', 'vi')
->innerJoin('vi.variation', 'v')
->innerJoin('vi.vehicle', 'vehicle')
->join('vehicle.model', 'm')
->join('m.brand', 'b')
->join('b.logo', 'logo')
->where('cis.state = 1')
->andWhere('cis.is_delete != 1')
->andWhere('vehicle.is_delete != 1')
->andWhere('vehicle.state = 1');
$brands = $query->getQuery()->getResult();
foreach ($brands as $line) {
if (!isset($filters['brand'][$line['burl']])) {
$logo = $this->em->getRepository(Media::class)->find($line['logo_id']);
$filters['brand'][$line['burl']] = [
'url' => $line['burl'],
'title' => $line['name'],
'link' => $this->router->generate('portal_in_stock_filter_catalog', [
'state' => $routeParams['state'],
'type' => $typeData['url'],
'brand' => $line['burl'],
]),
'logo' => $this->mediaExtension->getPath($logo, 'menu'),
'count' => 1,
'models' => [],
];
}
$filters['brand'][$line['burl']]['count'] += 1;
$filters['brand'][$line['burl']]['models'][$line['id']] = [
'url' => $line['url'],
'title' => $line['title'],
];
}
usort($filters['brand'], function ($a, $b) {
if ($a['title'] == $b['title']) {
return 1;
}
return $a['title'] > $b['title'] ? 1 : -1;
});
return $filters;
}
public function getCatalogCharacteristicsId(): array
{
$result = $this->em->getRepository(CharacteristicValue::class)->createQueryBuilder('cv')
->join('cv.characteristic', 'c')
->select('c.id')
->distinct()
->getQuery()->getResult();
return array_column($result, 'id');
}
public function getCharacteristicsByType($type, $vehicleType, $locale)
{
$characteristics = $this->em->getRepository(Characteristic::class)->findBy([
'characteristic_type' => $type,
'vehicle_type' => $vehicleType,
'in_filter' => 1,
]);
$result = [];
foreach ($characteristics as $item) {
$result[$item->getId()] = [
'id' => $item->getId(),
'title' => $item->getTitle($locale),
];
}
return $result;
}
public function getYearsOfProductionFromUsedVehicles($locale)
{
$years = $this->em->getRepository(VehicleItem::class)->createQueryBuilder('vi')
->select('vi.year')
->join('vi.vehicle', 'vehicle')
->where('vehicle.is_used = 1')
->andWhere('vehicle.state = 1')
->groupBy('vi.year')
->orderBy('vi.year', 'DESC')
->getQuery()->getResult();
$title = $locale == 'ua' ? 'Рік випуску' : 'Год выпуска';
$result = [SeoMetaTag::YEARS_PARAMETER => ['title' => $title, 'links' => []]];
foreach ($years as $year) {
$result[SeoMetaTag::YEARS_PARAMETER]['links'][] = [
'url' => $year['year'],
'title' => $year['year'],
];
}
return $result;
}
public function findStockVehiclesSiteMap()
{
$vehicleTypes = [VehicleType::PASSENGER_TYPE, VehicleType::BUS_TYPE];
$query = $this->em->getRepository(\CoreBundle\Entity\Vehicles\InStock::class)
->createQueryBuilder('cis')
->select('cis',
'vi',
'(CASE
WHEN cis.action_price > 0 THEN cis.action_price
WHEN cis.action_price_usd > 0 THEN
(CASE
WHEN vi.alt_rate > 0 THEN cis.action_price_usd * vi.alt_rate
ELSE cis.action_price_usd * d.rate
END)
WHEN vi.alt_price > 0 THEN vi.alt_price
ELSE
(CASE
WHEN vi.alt_rate > 0 THEN vi.price * vi.alt_rate
ELSE vi.price * d.rate
END)
END) + COALESCE(cis.add_cost, 0) AS price',
'(CASE
WHEN (CASE
WHEN cis.action_price > 0 THEN cis.action_price
WHEN cis.action_price_usd > 0 THEN
(CASE
WHEN vi.alt_rate > 0 THEN cis.action_price_usd * vi.alt_rate
ELSE cis.action_price_usd * d.rate
END)
WHEN vi.alt_price > 0 THEN vi.alt_price
ELSE
(CASE
WHEN vi.alt_rate > 0 THEN vi.price * vi.alt_rate
ELSE vi.price * d.rate
END)
END + COALESCE(cis.add_cost, 0)) = 0 THEN 9999999999
ELSE
(CASE
WHEN cis.action_price > 0 THEN cis.action_price
WHEN cis.action_price_usd > 0 THEN
(CASE
WHEN vi.alt_rate > 0 THEN cis.action_price_usd * vi.alt_rate
ELSE cis.action_price_usd * d.rate
END)
WHEN vi.alt_price > 0 THEN vi.alt_price
ELSE
(CASE
WHEN vi.alt_rate > 0 THEN vi.price * vi.alt_rate
ELSE vi.price * d.rate
END)
END + COALESCE(cis.add_cost, 0))
END) AS orderPrice')
->innerJoin('cis.vehicle_item', 'vi')
->innerJoin('vi.variation', 'v')
->innerJoin('v.characteristics', 'vc')
->innerJoin('vc.characteristic_value', 'vcv')
->innerJoin('vi.equipment', 'e')
->innerJoin('vi.vehicle', 'vehicle')
->join('vehicle.dealer', 'd')
->join('vehicle.model', 'm')
->join('m.brand', 'b')
->where('cis.state = 1 and cis.is_delete != 1')
->andWhere('vehicle.is_delete != 1')
->andWhere('vehicle.is_delete != 1')
->andWhere('vehicle.vehicle_type IN (:vehicle_type)')
->setParameter('vehicle_type', $vehicleTypes)
->andWhere('vehicle.state = 1');
$vehicles = $query->getQuery()->getResult();
foreach ($vehicles as $vehicle) {
$inStockModel = $this->vehicleInStockFactory->createByEntity($vehicle[0]);
$result[] = $inStockModel;
}
return $result;
}
public function findUsedVehiclesSiteMap()
{
$typeData = VehicleType::getTypeDataByUrl('car');
$query = $this->em->getRepository(\CoreBundle\Entity\Vehicles\VehicleItem::class)
->createQueryBuilder('vi')
->select('vi',
'case when (ve.is_used = 0 OR d.id != 6) then (case when vi.alt_price > 0 then vi.alt_price else (case when vi.alt_rate > 0 then vi.alt_rate * vi.price else vi.price * d.rate end) end) else (case when vi.alt_price > 0 then vi.alt_price else vi.price end) end as price',
'case when (case when (ve.is_used = 0 OR d.id != 6) then (case when vi.alt_price > 0 then vi.alt_price else (case when vi.alt_rate > 0 then vi.alt_rate * vi.price else vi.price * d.rate end) end) else (case when vi.alt_price > 0 then vi.alt_price else vi.price end) end) = 0 then 9999999999 else (case when (ve.is_used = 0 OR d.id != 6) then (case when vi.alt_price > 0 then vi.alt_price else (case when vi.alt_rate > 0 then vi.alt_rate * vi.price else vi.price * d.rate end) end) else (case when vi.alt_price > 0 then vi.alt_price else vi.price end) end) end as orderPrice')
->join('vi.variation', 'v');
if (isset($searchParams['inStock']) && $searchParams['inStock'] === 'true') {
$query->join('vi.vehicleInStock', 'vis');
}
$query->join('v.vehicle', 've')
->join('ve.dealer', 'd')
->join('ve.model', 'm')
->join('m.brand', 'b')
->leftJoin('ve.recommend_group', 'rg')
->where('ve.vehicle_type = :type')
->andWhere('vi.state = 1')
->andWhere('ve.state = 1')
->andWhere('ve.is_not_filled = 0')
->andWhere('ve.is_delete != 1')
->andWhere('vi.sold != 1')
->setParameter('type', $typeData['id']);
$mounthAgo = (new DateTime())->modify('-30 days');
$query->andWhere('ve.is_used = :is_used')
->setParameter('is_used', 1)
->andWhere('vi.date_of_sale > :mounthAgo OR vi.date_of_sale IS NULL')
->setParameter('mounthAgo', $mounthAgo);
$vehicles = $query->getQuery()->getResult();
foreach ($vehicles as $vehicle) {
$usedModel = $this->vehicleFactory->createByEntity($vehicle[0]->getVehicle());
$result[] = $usedModel;
}
return $result;
}
public function findStockVehiclesRss(Dealer $dealer)
{
$vehicleTypes = [VehicleType::PASSENGER_TYPE, VehicleType::BUS_TYPE];
$mounthAgo = (new DateTime())->modify('-30 days');
$query = $this->em->getRepository(InStock::class)
->createQueryBuilder('cis')
->select('cis', 'vi')
->innerJoin('cis.vehicle_item', 'vi')
->innerJoin('vi.variation', 'v')
->innerJoin('v.characteristics', 'vc')
->innerJoin('vc.characteristic_value', 'vcv')
->innerJoin('vi.equipment', 'e')
->innerJoin('vi.vehicle', 'vehicle')
->join('vehicle.dealer', 'd')
->join('vehicle.model', 'm')
->join('m.brand', 'b')
->where('cis.state = 1 and cis.is_delete != 1')
->andWhere('vehicle.is_delete != 1')
->andWhere('vehicle.is_delete != 1')
->andWhere('vehicle.vehicle_type IN (:vehicle_type)')
->andWhere('vehicle.date_create <= :mounthAgo')
->andWhere('d.id = :dealer')
->setParameter('dealer', $dealer)
->setParameter('vehicle_type', $vehicleTypes)
->setParameter('mounthAgo', $mounthAgo)
->andWhere('vehicle.state = 1');
$vehicles = $query->getQuery()->getResult();
foreach ($vehicles as $vehicle) {
$inStockModel = $this->vehicleInStockFactory->createByEntity($vehicle);
$result[] = $inStockModel;
}
return $result;
}
public function findUsedVehiclesRss(Dealer $dealer)
{
$typeData = VehicleType::getTypeDataByUrl('car');
$mounthAgo = (new DateTime())->modify('-30 days');
$query = $this->em->getRepository(VehicleItem::class)
->createQueryBuilder('vi')
->select('vi')
->join('vi.variation', 'v');
if (isset($searchParams['inStock']) && $searchParams['inStock'] === 'true') {
$query->join('vi.vehicleInStock', 'vis');
}
$query->join('v.vehicle', 've')
->join('ve.dealer', 'd')
->join('ve.model', 'm')
->join('m.brand', 'b')
->leftJoin('ve.recommend_group', 'rg')
->where('ve.vehicle_type = :type')
->andWhere('vi.state = 1')
->andWhere('ve.state = 1')
->andWhere('ve.is_not_filled = 0')
->andWhere('ve.is_delete != 1')
->andWhere('vi.sold != 1')
->andWhere('ve.ria_publication_date <= :monthAgo' )
->setParameter('monthAgo', $mounthAgo)
->setParameter('type', $typeData['id']);
$query->andWhere('d.id = :dealer')
->setParameter('dealer', $dealer);
$vehicles = $query->getQuery()->getResult();
foreach ($vehicles as $vehicle) {
$usedModel = $this->vehicleFactory->createByEntity($vehicle->getVehicle());
$result[] = $usedModel;
}
return $result;
}
}

src/PortalBundle/Model/Catalog.php line 176