Symfony Profiler

<?php
namespace App\Repository;
use App\Entity\Administrator;
use App\Entity\CentralPurchasing;
use App\Entity\Enum\ProspectOnStoreStatusEnum;
use App\Entity\Enum\ProspectOnStoreCallbackReminderStateEnum;
use App\Entity\Enum\QualificationStateEnum;
use App\Entity\ProspectOnStoreStatus;
use App\Entity\User;
use DateTime;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\Persistence\ManagerRegistry;
use Doctrine\ORM\Query\Expr\Join;
use Doctrine\ORM\QueryBuilder;
/**
* @method ProspectOnStoreStatus|null find($id, $lockMode = null, $lockVersion = null)
* @method ProspectOnStoreStatus|null findOneBy(array $criteria, array $orderBy = null)
* @method ProspectOnStoreStatus[] findAll()
* @method ProspectOnStoreStatus[] findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
*/
class ProspectOnStoreStatusRepository extends ServiceEntityRepository
{
public function __construct(ManagerRegistry $registry)
{
parent::__construct($registry, ProspectOnStoreStatus::class);
}
/**
* @deprecated
* Find prospectOnStoreStatus without prospect on store (to migrate)
* @return ProspectOnStoreStatus[] Returns an array of ProspectOnStoreStatus objects
*/
public function findProspectOnStoreStatusWithoutProspectOnStore($limit=10)
{
return $this->createQueryBuilder('pg')
->addSelect('p')
->addSelect('pos')
->join('p.prospectsOnStore', 'pos')
->orderBy('pg.id', 'DESC')
->setMaxResults($limit)
->getQuery()
->getResult()
;
}
/**
* Return QB of the Prospect (admin)
* @param User $user
* @return \Doctrine\ORM\QueryBuilder
*/
public function findQBAllWithJoin(User $user = null)
{
$qb = $this->createQueryBuilder('pg')
->leftJoin('pg.prospectOnStore', 'pos')->addSelect('pos')
->leftJoin('pos.store', 's')->addSelect('s')
->leftJoin('s.administrator', 'admin')
;
//filter for user
if ($user) {
$qb->join('s.administrator', 'a');
$qb->andWhere('a.user = :user');
$qb->setParameter('user', $user);
}
return $qb;
}
/**
* Return nb of Appointment offered by a centralPurchasing
* @param CentralPurchasing $centralPurchasing
* @return mixed
*/
public function findNbAppointmentOfferedByCentralPurchasing(CentralPurchasing $centralPurchasing){
$qb = $this->createQueryBuilder('pg');
$qb
->addSelect('count(pg) as nb_apt')
->addSelect('s.id as store_id')
->addSelect('ad.id as admin_id')
->innerJoin('pg.prospectOnStore', 'pos')
->innerJoin('pos.cost', 'cc', Join::WITH, 'cc.centralPurchasing = :centralPurchasing')
->innerJoin('pos.store', 's')
->innerJoin('s.administrator', 'ad')
->andWhere('pg.status = :grade')
->andWhere('cc.centralPurchasing = :centralPurchasing')
->setParameter('centralPurchasing', $centralPurchasing)
->setParameter('grade', ProspectOnStoreStatusEnum::APPOINTMENT_MADE)
->groupBy('s.id')
;
$result = $qb->getQuery()->getArrayResult();
return $result;
}
/**
* Return nb of ProspectOnStoreStatus 'RDV' group by store for an administrator
* @param Administrator $administrator
* @param DateTime|null $startDate
* @param DateTime|null $endDate
* @return array
*/
public function findNbAppointmentByStoreForAdministrator(Administrator $administrator, DateTime $startDate=null, DateTime $endDate=null): array
{
$qb = $this->createQueryBuilder('pg');
$qb
->addSelect('count(distinct pg.prospectOnStore) as nb_rdv')
->addSelect('s.id as store_id')
->innerJoin('pg.prospectOnStore', 'pos')
->innerJoin('pos.store', 's')
->innerJoin('pos.prospect', 'p')
->innerJoin('s.administrator', 'ad')
->andWhere('s.administrator = :admin')
->setParameter('admin', $administrator)
->andWhere('pg.status = :grade')->setParameter('grade', ProspectOnStoreStatusEnum::APPOINTMENT_MADE)
->groupBy('store_id')
->orderBy('store_id');
$this->addValidOrQualifiedValidCondition($qb);
$this->addProspectTimeWindow($qb, $startDate, $endDate);
return $qb->getQuery()->getArrayResult();
}
/**
* Return nb of ProspectOnStoreStatus 'RDV' for all stores for an administrator
* @param Administrator $administrator
* @return int
*/
public function findNbAppointmentForAllStoresForAdministrator(Administrator $administrator, DateTime $startDate=null, DateTime $endDate=null)
{
$qb = $this->createQueryBuilder('pg');
$qb
->select('count(distinct pg.prospectOnStore) as nb_rdv')
//->addSelect('s.id as store_id')
->innerJoin('pg.prospectOnStore', 'pos')
->innerJoin('pos.store', 's')
->innerJoin('pos.prospect', 'p')
->innerJoin('s.administrator', 'ad')
->andWhere('s.administrator = :admin')
->setParameter('admin', $administrator)
->andWhere('pg.status = :grade')->setParameter('grade', ProspectOnStoreStatusEnum::APPOINTMENT_MADE)
//->orderBy('store_id')
;
$this->addValidOrQualifiedValidCondition($qb);
$this->addProspectTimeWindow($qb, $startDate, $endDate);
return $qb->getQuery()->getSingleScalarResult();
}
/**
* add conditions for POS on date
* alias must existed: pos (ProspectOnStore)
* @param QueryBuilder $qb
*/
private function addProspectTimeWindow(QueryBuilder $qb, DateTime $startDate=null, DateTime $endDate=null){
if(!is_null($startDate) && !is_null($endDate)){
$qb->andWhere('pos.created between :start and :end')
->setParameter('start', $startDate)
->setParameter('end', $endDate);
}
}
/**
* add conditions for POS to be valid. No qualification or qualification valid)
* alias must existed: pq (qualification)
* @param QueryBuilder $qb
*/
private function addValidOrQualifiedValidCondition(QueryBuilder $qb){
$qb->andWhere('pos.delivered = true');
}
/**
* Find ProspectOnStoreStatus with callback reminder email to be sent
* @return mixed
*/
public function findWithCallbackReminderToSend(int $limit=10)
{
$tomorrow = new DateTime("now + 15 minutes ");
$qb = $this->createQueryBuilder('pg')
->andWhere('pg.callbackReminderState = :state')
->setParameter('state', ProspectOnStoreCallbackReminderStateEnum::SCHEDULE)
->andWhere('pg.callbackReminderEmail is not null')
->andWhere('pg.dateCallback < :date')
->setParameter('date', $tomorrow)
->setMaxResults($limit);
;
return $qb->getQuery()->getResult();
}
}

src/Repository/ProspectOnStoreStatusRepository.php line 26