Symfony Profiler

<?php
/**
* @license proprietary
* This code is part of vidi portal application
*/
namespace CoreBundle\Repository;
use DateTime;
use CoreBundle\Entity\Brand;
use CoreBundle\Entity\Dealer;
use Doctrine\ORM\EntityRepository;
use Doctrine\ORM\NonUniqueResultException;
use Doctrine\ORM\NoResultException;
use Doctrine\ORM\Query\Expr\Join;
class BrandRepository extends EntityRepository
{
/**
* @param bool $withoutImage
* @param null $state
* @return array
*/
public function getAvailBrands($withoutImage = false, $state = null, $vehicleType = null, $hasNDS = null)
{
$query = $this->createQueryBuilder('b')
->select('b, count(v.id) as cnt, MIN(case when d.position > 0 then d.position else 99 end) as position')
->innerJoin('b.models', 'm')
->innerJoin('m.vehicles', 'v', Join::WITH, 'v.state = 1')
->innerJoin('v.dealer', 'd')
->innerJoin('v.preview', 'p')
->innerJoin('v.vehicle_items', 'vi', Join::WITH, 'vi.state = 1')
->orderBy('position', 'ASC')
->groupBy('b.id');
if ($state !== null) {
$query->andWhere('v.is_used = :state')
->setParameter('state', $state);
}
if ($vehicleType && !is_array($vehicleType)) {
$query->andWhere('v.vehicle_type = :type')
->setParameter('type', $vehicleType);
}
if ($vehicleType && is_array($vehicleType)) {
$query->andWhere('v.vehicle_type in (:type)')
->setParameter('type', $vehicleType);
}
if (!$withoutImage) {
$query->innerJoin('b.image', 'i')
->addSelect('i.id as image');
$query->innerJoin('b.logo', 'logo')
->addSelect('logo');
} else {
$query->leftJoin('b.image', 'i')
->addSelect('i.id as image');
$query->leftJoin('b.logo', 'logo')
->addSelect('logo');
}
if ($hasNDS) {
$query->andWhere('vi.has_nds = 1');
}
$query->orderBy('b.name');
return $query->getQuery()->getResult();
}
public function getBrandList($brandId, $brandName)
{
$query = $this->createQueryBuilder('b')
->select('b.uid_1c as brandID, b.name as brandName')
->andWhere('b.uid_1c is not null');
if ($brandId) {
$query->andWhere('b.uid_1c = :brand')
->setParameter('brand', $brandId);
}
if ($brandName) {
$query->andWhere('b.name LIKE :name')
->setParameter('name', '%'.$brandName.'%');
}
return $query->getQuery()->getArrayResult();
}
/**
* @param string $url
* @return bool
* @throws NoResultException
* @throws NonUniqueResultException
*/
public function checkByUrl($url)
{
$brandCounts = $this->createQueryBuilder('b')
->select('count(b.id)')
->where('b.url = :url')
->setParameter('url', $url)
->getQuery()
->getSingleScalarResult();
return $brandCounts > 0;
}
public function getVehicleBrands($vehicleType = null, $isUsed = false)
{
$brands = $this->createQueryBuilder('b')
->innerJoin('b.models', 'm')
->innerJoin('m.vehicles', 'v', Join::WITH, 'v.state = true and v.is_used = :isUsed')
->setParameter('isUsed', $isUsed);
if($vehicleType){
$brands->andWhere('v.vehicle_type = :vehicleType')
->setParameter('vehicleType', $vehicleType);
}
$brands->orderBy('b.name', 'ASC')
->groupBy('b.id');
return $brands->getQuery()->getResult();
}
/**
* @param $vehicleType
* @return Brand[]
*/
public function getUsedVehicleBrands($vehicleType)
{
return $this->createQueryBuilder('b')
->innerJoin('b.models', 'm')
->innerJoin('m.vehicles', 'vehicle', Join::WITH, 'vehicle.state = 1 and vehicle.is_used = 1')
->orderBy('b.name', 'ASC')
->groupBy('b.id')
->getQuery()
->getResult();
}
public function getByName($name)
{
return $this->createQueryBuilder('b')
->where('LOWER(b.name) = :name')
->setParameter('name', strtolower($name))
->setMaxResults(1)
->getQuery()
->getOneOrNullResult();
}
public function getCreditAvailBrand()
{
$query = $this->createQueryBuilder('b')
->select('b')
->innerJoin('b.logo', 'l')
->innerJoin('b.dealer', 'd')
->innerJoin('b.models', 'm')
->innerJoin('m.vehicles', 'vh', Join::WITH, 'vh.state = 1')
->innerJoin('vh.variations', 'v', Join::WITH, 'v.state = 1');
}
/**
* @param bool $withoutUsedCar
* @param bool $with_credit_only
* @return array
*/
public function getAvailBrandsInstance($withoutUsedCar = false, $with_credit_only = false, bool $filterPalmira = false)
{
$query = $this->createQueryBuilder('b')
->select('b');
if ($filterPalmira) {
$query->addSelect('d')
->where('d.id != :toyotaPalmiraId')
->setParameter('toyotaPalmiraId', 7);
}
$query->innerJoin('b.logo', 'l')
->innerJoin('b.dealer', 'd')
->innerJoin('b.models', 'm')
->innerJoin('m.vehicles', 'v', Join::WITH, 'v.state = 1')
->innerJoin('v.vehicle_items', 'vi', Join::WITH, 'vi.state = 1');
if ($with_credit_only) {
$query->andWhere('v.credit_available = 1');
}
$query
->orderBy('d.position', 'ASC')
->groupBy('b.id', 'd.id');
if ($withoutUsedCar !== null) {
$query->andWhere('v.is_used = 0');
}
return $query->getQuery()->getResult();
}
/**
* @param bool $withoutUsedCar
* @param bool $withCreditOnly
* @return array
*/
public function getCarAvailBrandsInstance($isUsed = false, $withCreditOnly = false, Dealer $dealer = null, $isAutomarket = false): array
{
$query = $this->createQueryBuilder('b')
->select('b', 'MIN(
CASE
WHEN (
CASE
WHEN ((v.is_used = 0 AND d.id != 6) OR (v.is_used = 1 AND d.id = 33))
THEN (
CASE
WHEN vi.alt_price > 0 THEN vi.alt_price
WHEN vi.alt_usd_price > 0 THEN vi.alt_usd_price * d.rate
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
WHEN vi.alt_usd_price > 0 THEN vi.alt_usd_price * d.rate
ELSE vi.price
END
)
END
) = 0
THEN 9999999999
ELSE (
CASE
WHEN ((v.is_used = 0 AND d.id != 6) OR (v.is_used = 1 AND d.id = 33))
THEN (
CASE
WHEN vi.alt_price > 0 THEN vi.alt_price
WHEN vi.alt_usd_price > 0 THEN vi.alt_usd_price * d.rate
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
WHEN vi.alt_usd_price > 0 THEN vi.alt_usd_price * d.rate
ELSE vi.price
END
)
END
)
END
) as min_price')
->innerJoin('b.models', 'm')
->innerJoin('m.vehicles', 'v', Join::WITH, 'v.state = 1')
->innerJoin('v.vehicle_items', 'vi', Join::WITH, 'vi.state = 1 AND v.vehicle_type = 1')
->innerJoin('vi.vehicle', 've')
->innerJoin('ve.dealer', 'd')
->andWhere('v.is_used = :isUsed')
->setParameter('isUsed', $isUsed);
if ($withCreditOnly) {
$query->andWhere('v.credit_available = 1');
}
if ($isUsed) {
$query->andWhere('vi.is_reserved = 0');
$query->andWhere('vi.deposit = 0');
$query->andWhere('vi.sold = 0');
}
if ($dealer && $isUsed) {
$query->andWhere('v.dealer = :dealer')->setParameter('dealer', $dealer);
}
if ($isAutomarket) {
$query ->andWhere('vi.state = 1')
->andWhere('ve.state = 1')
->andWhere('ve.vehicle_type = :type')
->andWhere('ve.is_not_filled = 0')
->andWhere('ve.is_delete != 1')
->andWhere('ve.is_used = :isUsed')
->andWhere('vi.date_of_sale > :mounthAgo OR vi.date_of_sale IS NULL')
->setParameter('type', 1)
->setParameter('mounthAgo', (new DateTime())->modify('-30 days')->format('Y-m-d H:i:s'));
}
$query->groupBy('b.id');
$query->orderBy('b.name');
return $query->getQuery()->getResult();
}
/**
* @param false $withoutUsedCar
* @param false $with_credit_only
* @return array|int|string
*/
public function getAvailBrandsInstanceName($withoutUsedCar = false, $with_credit_only = false): array
{
$query = $this->createQueryBuilder('b')
->select('b.name')
->innerJoin('b.logo', 'l')
->innerJoin('b.dealer', 'd')
->innerJoin('b.models', 'm')
->innerJoin('m.vehicles', 'v', Join::WITH, 'v.state = 1')
->innerJoin('v.vehicle_items', 'vi', Join::WITH, 'vi.state = 1');
if ($with_credit_only) {
$query->andWhere('v.credit_available = 1');
}
$query
->orderBy('d.position', 'ASC')
->groupBy('b.id', 'd.id');
if ($withoutUsedCar !== null) {
$query->andWhere('v.is_used = 0');
}
return array_column($query->getQuery()->getResult(), 'name');
}
public function getByDealer(Dealer $dealer)
{
return $this->createQueryBuilder('b')
->select('b.id,b.name')
->innerJoin('b.models', 'm')
->innerJoin('m.vehicles', 'vh', Join::WITH, 'vh.state = 1 and vh.dealer = :dealer')
->innerJoin('vh.preview', 'p')
->setParameter('dealer', $dealer)
->groupBy('b.id')
->getQuery()->getResult();
}
public function getBrandByUrl($url){
return $this->createQueryBuilder('b')
->andWhere('b.url = :url')->setParameter('url', $url)
->andWhere('b.url is not null')
->getQuery()->getOneOrNullResult();
}
public function getBrandsByUrl($url){
return $this->createQueryBuilder('b')
->andWhere('b.url IN (:url)')->setParameter('url', $url)
->andWhere('b.url is not null')
->getQuery()->getResult();
}
public function getBrandsHasRegulation(){
return $this->createQueryBuilder('b')
->innerJoin('b.models', 'm')
->innerJoin('m.service_variations','sv')
->innerJoin('sv.service_works', 'sw')
->where('sw.is_regulations = :isRegulations')->setParameter('isRegulations', true)
->getQuery()->getResult();
}
}

src/CoreBundle/Repository/BrandRepository.php line 25