Symfony Profiler

<?php
namespace App\Repository\Booking;
use App\DBAL\Types\Account\WebQuoteStepEnum;
use App\DBAL\Types\Booking\BookingStep;
use App\DBAL\Types\Booking\Cabin\CabinStepEnum;
use App\Entity\__Contact\contact\Contact;
use App\Entity\Booking\Booking;
use App\Entity\Booking\Quote;
use App\Entity\ParameterCodes;
use App\Entity\User\User;
use App\Migration\Entity\CsvQuote;
use App\Repository\Email\EmailContentRepository;
use App\Voter\QuoteVoter;
use Crea\SecurityBundle\Helper\AccessHelper;
use Crea\SecurityBundle\Model\Access;
use DateTime;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\ORM\NonUniqueResultException;
use Doctrine\ORM\QueryBuilder;
use Doctrine\Persistence\ManagerRegistry;
use Exception;
use Symfony\Component\Security\Core\Security;
/**
* @method Quote|null find($id, $lockMode = null, $lockVersion = null)
* @method Quote|null findOneBy(array $criteria, array $orderBy = null)
* @method Quote[] findAll()
* @method Quote[] findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
*/
class QuoteRepository extends ServiceEntityRepository
{
protected Security $security;
protected AccessHelper $accessHelper;
public function __construct(ManagerRegistry $registry,
Security $security,
AccessHelper $accessHelper)
{
parent::__construct($registry, Quote::class);
$this->security = $security;
$this->accessHelper = $accessHelper;
}
public const QUOTE_NUMBER_PREFIX = 'DEV';
public const QUOTE_CONVERTER_B2B = 'B2B';
public const QUOTE_CONVERTER_FULLWEB = 'FULLWEB';
public const QUOTE_CONVERTER_MANUAL = 'MANUAL';
public const QUOTE_ORIGIN_ERP = 'erp';
public const QUOTE_ORIGIN_CMS = 'cms';
public function getGridQueryBuilderQuotesWithNotConfirmedOption(): QueryBuilder
{
return $this->createQueryBuilder('q')
->join('q.booking', 'b')
->join('q.cabins', 'c')
->andWhere('q.webQuoteStep IN (:insurance, :summary, :payment)')
->andWhere('b.step = :notBooked')
->andWhere('c.step = :option')
->andWhere('q.converter IS NOT NULL')
// ->andWhere('q.converter != :manual')
->setParameters([
'insurance' => WebQuoteStepEnum::INSURANCE,
'summary' => WebQuoteStepEnum::SUMMARY,
'payment' => WebQuoteStepEnum::PAYMENT,
'notBooked' => BookingStep::NOT_BOOKED,
'option' => CabinStepEnum::OPTION,
// 'manual' => QuoteManager::MANUAL,
])
;
}
public function getGridQueryBuilderQuotesWithConfirmedOption(): QueryBuilder
{
return $this->createQueryBuilder('q')
->andWhere('q.quoteValidityAt > :now')
->andWhere('q.converter IN (:fullWeb)')
->setParameters([
'fullWeb' => self::QUOTE_CONVERTER_FULLWEB,
'now' => new DateTime(),
]);
}
public function getGridQueryBuilderQuotesWithOptionInProgress(): QueryBuilder
{
$qb = $this->createQueryBuilder('q')
->andWhere('q.quoteValidityAt > :now')
->setParameters([
'now' => new DateTime(),
]);
if (!$this->accessHelper->hasAccess(new Access([QuoteVoter::OPTION_IN_PROGRESS_SHOW_ALL_SELLERS]))) {
/** @var User $user */
$user = $this->security->getUser();
$qb = $qb
->join('q.seller', 'u')
->andWhere($qb->expr()->eq('u.id', $user->getId()));
}
return $qb;
}
public function getGridQueryBuilderQuotes(): QueryBuilder
{
$qb = $this->createQueryBuilder('q');
if (!$this->accessHelper->hasAccess(new Access([QuoteVoter::SHOW_ALL_SELLERS]))) {
/** @var User $user */
$user = $this->security->getUser();
$qb = $qb
->join('q.seller', 'u')
->andWhere($qb->expr()->eq('u.id', $user->getId()));
}
return $qb;
}
public function findQuotesWithOptionInProgress(?User $user = null): array
{
$qb = $this->createQueryBuilder('q');
$qb = $qb
->andWhere($qb->expr()->gte('q.quoteValidityAt', ':now'))
->setParameter('now', new DateTime());
if ($user !== null) {
$qb = $qb
->join('q.seller', 'u')
->andWhere($qb->expr()->eq('u.id', $user->getId()));
}
return $qb
->getQuery()
->getResult();
}
/**
* @throws NonUniqueResultException
*/
public function findProcessQuote(int $id): ?Quote
{
return $this
->createQueryBuilder('q')
->innerJoin('q.cruiseHistory', 'ch')
->innerJoin('ch.itineraryHistory', 'ih')
->leftJoin('q.cabinCategoryHistory', 'cch')
->leftJoin('q.participants', 'qp')
->leftJoin('qp.passengerInformation', 'qpi')
->leftJoin('qp.cabin', 'ca')
->leftJoin('q.quoteLines', 'ql')
->select('q', 'ch', 'ih', 'cch', 'qp', 'qpi', 'ca', 'ql')
->where('q.id = :id')
->andWhere('ch.isCurrent = :cruiseHistoryIsCurrent')
->setParameters(['id' => $id, 'cruiseHistoryIsCurrent' => true])
->getQuery()
->getOneOrNullResult();
}
/**
* @param Contact $contact
* @return array|Quote[]
*/
public function getContactQuotes(Contact $contact): array
{
return $this->createQueryBuilder('q')
->andWhere('q.contact = :contact')
->setParameters(['contact' => $contact])
->orderBy('q.id', 'DESC')
->getQuery()
->getResult();
}
// public function getContactExpiredQuotes(Contact $contact): array
// {
// return $this->createQueryBuilder('q')
// ->andWhere('q.contact = :contact')
// ->andWhere('q.webQuoteStep = :steps')
// ->setParameters(['contact' => $contact, 'steps' => ParameterCodes::QUOTE_STATUS_DEADLINE_OVER])
// ->orderBy('q.id', 'DESC')
// ->getQuery()
// ->getResult();
// }
//
//// permet de trouver le dernier quote ayant un numéro commençant par DEV
//
// /**
// * @throws NonUniqueResultException
// */
// public function findLastQuoteWithNumber(): ?Quote
// {
// return $this->createQueryBuilder('q')
// ->andWhere('q.number IS NOT NULL')
// ->andWhere('q.number like :number')
// ->setParameter('number', self::QUOTE_NUMBER_PREFIX.'%')
// ->orderBy('CAST(REPLACE(q.number, \''.self::QUOTE_NUMBER_PREFIX.'\', \'\') AS UNSIGNED)', 'DESC')
// ->setMaxResults(1)
// ->getQuery()
// ->getOneOrNullResult();
// }
//
//// retourne les devis vente de contingents ($contigent = true), ou la liste des devis vente classique ($contingent = false)
// public function getContingentBooking(Booking $booking, bool $contingent): array
// {
// return $this->createQueryBuilder('q')
// ->where('q.contingent = :con')
// ->andWhere('q.booking = :booking')
// ->setParameters(['con' => $contingent, 'booking' => $booking])
// ->getQuery()
// ->getResult();
// }
//
// /**
// * Récupère le nombre de devis qui a un lien avec une demande de prospect.
// *
// * @param null|User|array $statsFrom
// * @throws Exception
// */
// public function findConvertedQuoteNumber($statsFrom = null): int
// {
// if (!($statsFrom instanceof User) && !is_array($statsFrom) && null !== $statsFrom) {
// throw new Exception('Error: the parameter should be one of the following instance: User, array, null. ' . get_class($statsFrom) . ' given.');
// }
//
// $queryBuilder = $this->createQueryBuilder('q')
// ->select('COUNT(q.id)')
// ->where('q.prospectRequest IS NOT NULL')
// ;
//
// if ($statsFrom instanceof User) {
// $queryBuilder
// ->join('q.prospectRequest', 'p')
// ->andWhere('p.assignedUser = :user')
// ->setParameter('user', $statsFrom)
// ;
// } elseif (is_array($statsFrom)) {
// $queryBuilder
// ->join('q.prospectRequest', 'p')
// ->andWhere('p.assignedUser in (:users)')
// ->setParameter('users', $statsFrom)
// ;
// }
//
// return $queryBuilder
// ->getQuery()
// ->getSingleScalarResult()
// ;
// }
//
// /**
// * Récupère le nombre de devis à l'état non réservé qui a un lien avec une demande de prospect, elle-même liée à un contact.
// *
// * @param null|User|array $statsFrom
// * @throws Exception
// */
// public function findContactRequestConvertedToQuoteNumber($statsFrom = null): int
// {
// if (!($statsFrom instanceof User) && !is_array($statsFrom) && null !== $statsFrom) {
// throw new Exception('Error: the parameter should be one of the following instance: User, array, null. ' . get_class($statsFrom) . ' given.');
// }
//
// $queryBuilder = $this->createQueryBuilder('q')
// ->select('COUNT(q.id)')
// ->join('q.prospectRequest', 'p')
// ->join('q.booking', 'b')
// ->where('p.contact IS NOT NULL')
// ->andWhere('b.step in (:steps)')
// ;
//
// $parameters = ['steps' => [BookingStep::NOT_BOOKED]];
//
// if ($statsFrom instanceof User) {
// $queryBuilder->andWhere('p.assignedUser = :user');
// $parameters['user'] = $statsFrom;
// } elseif (is_array($statsFrom)) {
// $queryBuilder->andWhere('p.assignedUser in (:users)');
// $parameters['users'] = $statsFrom;
// }
//
// return $queryBuilder
// ->setParameters($parameters)
// ->getQuery()
// ->getSingleScalarResult()
// ;
// }
//
// /**
// * Récupère le nombre de devis à l'état réservé qui a un lien avec une demande de prospect, elle-même liée à un contact.
// *
// * @param null|User|array $statsFrom
// * @throws Exception
// */
// public function findContactRequestConvertedToBookingNumber($statsFrom = null): int
// {
// if (!($statsFrom instanceof User) && !is_array($statsFrom) && null !== $statsFrom) {
// throw new Exception('Error: the parameter should be one of the following instance: User, array, null. ' . get_class($statsFrom) . ' given.');
// }
//
// $queryBuilder = $this->createQueryBuilder('q')
// ->select('COUNT(q.id)')
// ->join('q.prospectRequest', 'p')
// ->join('q.booking', 'b')
// ->where('p.contact IS NOT NULL')
// ->andWhere('b.step in (:steps)')
// ;
//
// $parameters = ['steps' => [BookingStep::ACCOUNT_PAID, BookingStep::FULLY_PAID]];
//
// if ($statsFrom instanceof User) {
// $queryBuilder->andWhere('p.assignedUser = :user');
// $parameters['user'] = $statsFrom;
// } elseif (is_array($statsFrom)) {
// $queryBuilder->andWhere('p.assignedUser in (:users)');
// $parameters['users'] = $statsFrom;
// }
//
// return $queryBuilder
// ->setParameters($parameters)
// ->getQuery()
// ->getSingleScalarResult()
// ;
// }
//
// /**
// * Récupère le nombre de devis créé par utilisateur.
// */
// public function countCreatedQuoteByUser(array $users): array
// {
// return $this->createQueryBuilder('q')
// ->select('COUNT(q.id) as number, u.id as sellerId, u.firstName as sellerFirstName, u.lastName as sellerLastName')
// ->join('q.seller', 'u')
// ->where('u.id in (:users)')
// ->setParameter('users', $users)
// ->groupBy('u.id')
// ->getQuery()
// ->getResult()
// ;
// }
public function findExpiredQuotes(?array $companyCodes = null): array
{
$qb = $this->createQueryBuilder('q')
->innerJoin('q.cruiseHistory', 'ch')
->innerJoin('ch.ship', 's')
->innerJoin('s.company', 'c');
$qb->where($qb->expr()->isNotNull('q.quoteValidityAt'))
->andWhere($qb->expr()->lt('q.quoteValidityAt', ':now'))
->andWhere($qb->expr()->isNull('q.booking'));
$parameters = ['now' => new DateTime()];
if ($companyCodes !== null) {
$qb->andWhere($qb->expr()->in('c.code', ':companyCodes'));
$parameters["companyCodes"] = $companyCodes;
}
return $qb->setParameters($parameters)
->getQuery()
->getResult();
}
public function findNoTransformedQuotes8HoursBeforeExpiration(): array
{
$qb = $this->createQueryBuilder('q')
->innerJoin('q.attributedSentEmails', 'ase')
->innerJoin('ase.sentEmail', 'se')
->innerJoin('se.email', 'e')
->innerJoin('e.emailContent', 'ec');
$qb->where($qb->expr()->isNotNull('q.quoteValidityAt'))
->andWhere($qb->expr()->gt('q.quoteValidityAt', ':now8Hours'))
->andWhere($qb->expr()->lte('q.quoteValidityAt', ':now9Hours'))
->andWhere($qb->expr()->in('ec.code', ':codes'))
->setParameters([
'now8Hours' => (new DateTime())->modify('+8 hour')->format("Y-m-d H:i:s"),
'now9Hours' => (new DateTime())->modify('+9 hour')->format("Y-m-d H:i:s"),
'codes' => [EmailContentRepository::SEND_QUOTE_NEW_BOOKING_CODE, EmailContentRepository::SEND_QUOTE_NEW_BOOKING_WITH_CREDIT_CODE],
])
->groupBy('q.id');
return $qb->getQuery()
->getResult();
}
// /**
// * @param QueryBuilder $qb
// * @param $start
// * @param $end
// */
// private function currentDateFullWebQuotes(QueryBuilder $qb, $start, $end): void
// {
// $qb
// ->andWhere('q.createdAt BETWEEN :start AND :end')
// ->andWhere('q.converter = :fullWeb')
// ->setParameters([
// 'fullWeb' => self::QUOTE_CONVERTER_FULLWEB,
// 'start' => $start,
// 'end' => $end,
// ])
// ;
// }
//
// /**
// * @throws Exception
// * @return Quote[] Returns an array of Quote objects
// */
// public function getCurrentYearFullWebQuotes(): array
// {
// $qb = $this->createQueryBuilder('q');
// $start = new Datetime(date('Y').'-01-01');
// $end = new Datetime(date('Y').'-12-31');
//
// $this->currentDateFullWebQuotes($qb, $start, $end);
//
// return $qb
// ->getQuery()
// ->getResult()
// ;
// }
//
// /**
// * @throws Exception
// * @return Quote[] Returns an array of Quote objects
// */
// public function getCurrentWeekFullWebQuotes(): array
// {
// $qb = $this->createQueryBuilder('q');
//
// if( date('D') != 'Mon' ) {
// $lastMonday = date('Y-m-d 00:00',strtotime('last monday'));
// }else {
// $lastMonday = date('Y-m-d 00:00');
// }
//
// $start = new Datetime($lastMonday);
// $end = (clone $start)->modify('+ 6 days');
//
// $this->currentDateFullWebQuotes($qb, $start, $end);
//
// return $qb
// ->getQuery()
// ->getResult()
// ;
// }
//
// /**
// * @throws Exception
// * @return Quote[] Returns an array of Quote objects
// */
// public function getCurrentDayFullWebQuotes(): array
// {
// $qb = $this->createQueryBuilder('q');
// $start = new Datetime(date('Y-m-d 00:00'));
// $end = new Datetime(date('Y-m-d h:i'));
//
// $this->currentDateFullWebQuotes($qb, $start, $end);
//
// return $qb
// ->getQuery()
// ->getResult()
// ;
// }
//
// /**
// * @return QueryBuilder
// */
// public function getQuotesInReviewQueryBuilder(): QueryBuilder
// {
// return $this->createQueryBuilder('q')
// ->innerJoin('q.attributedSentEmails', 'emails')
// ->innerJoin('q.stateType', 'qst')
// ->andWhere('emails.sentEmail IS NOT NULL')
// ->andWhere('qst.code = :code')
// ->setParameters([
// 'code' => ParameterCodes::QUOTE_STATUS_REVIEW,
// ]);
// }
//
// /**
// * @return Quote[] Returns an array of Quote objects
// */
// public function getQuotesInReviewAndSend(): array
// {
// return $this->getQuotesInReviewQueryBuilder()
// ->getQuery()
// ->getResult()
// ;
// }
//
// /**
// * @return Quote[] Returns an array of Quote objects
// */
// public function getQuotesInReviewAndSendBySeller(array $sellers): array
// {
// return $this->createQueryBuilder('q')
// ->innerJoin('q.attributedSentEmails', 'emails')
// ->innerJoin('q.stateType', 'qst')
// ->andWhere('emails.sentEmail IS NOT NULL')
// ->andWhere('qst.code = :code')
// ->innerJoin('q.seller', 's')
// ->andWhere('s.id in (:sellers)')
// ->select('COUNT(q) as number, MIN(q.createdAt) as date, s.firstName as sellerFirstName, s.lastName as sellerLastName')
// ->groupBy('s')
// ->setParameters([
// 'sellers' => $sellers,
// 'code' => ParameterCodes::QUOTE_STATUS_REVIEW,
// ])
// ->getQuery()
// ->getResult()
// ;
// }
//
// /**
// * @throws Exception
// * @return Quote[] Returns an array of Quote objects
// */
// public function getQuotesWithCroisilandOptionInProgressBySeller(array $sellers): array
// {
// return $this->createQueryBuilder('q')
// ->andWhere('q.quoteValidityAt = :now')
// ->innerJoin('q.seller', 's')
// ->join('q.contact', 'c')
// ->join('q.stateType', 'qst')
// ->andWhere('c.isActive = :isActive')
// ->andWhere('qst.code = :code')
// ->andWhere('s.id in (:sellers)')
// ->select('COUNT(q.id) as number, s.firstName as sellerFirstName, s.lastName as sellerLastName')
// ->groupBy('s')
// ->setParameters([
// 'now' => new DateTime(),
// 'code' => ParameterCodes::QUOTE_STATUS_REVIEW,
// 'isActive' => true,
// 'sellers' => $sellers,
// ])
// ->getQuery()
// ->getResult()
// ;
// }
//
//
// /**
// * @return Quote[]
// */
// public function getQuotesWithoutCsvRelation(): array
// {
// $qb = $this->createQueryBuilder('q')
// ->leftJoin(CsvQuote::class, 'cq', 'WITH', 'cq.tdevisId = q.oldCrmId');
// return $qb
// ->where($qb->expr()->isNull('cq.id'))
// ->getQuery()
// ->getResult();
// }
/********************************* home-module *************************************/
public function countThisDateQuotes(DateTime $date, ?User $user = null): int
{
$qb = $this->createQueryBuilder('q')
->select('count(q.id)');
$qb = $qb
->where($qb->expr()->gt('q.createdAt', ':date'))
->setParameter('date', $date);
if ($user !== null) {
$qb = $qb
->join('q.seller', 'u')
->andWhere($qb->expr()->eq('u.id', $user->getId()));
}
return $qb
->getQuery()
->getSingleScalarResult()
;
}
public function countAllQuotes(?User $user = null): int
{
$qb = $this->createQueryBuilder('q')
->select('count(q.id)');
if ($user !== null) {
$qb = $qb
->join('q.seller', 'u')
->where($qb->expr()->eq('u.id', $user->getId()));
}
return $qb
->getQuery()
->getSingleScalarResult()
;
}
public function countConversionQuotesThisDate(DateTime $date, ?User $user = null): int
{
$qb = $this->createQueryBuilder('q');
$qb = $qb
->select('count(q.id)')
->where($qb->expr()->gt('q.createdAt', ':date'))
->andWhere($qb->expr()->eq('q.stateType', ':code'))
->setParameter('date', $date)
->setParameter('code', ParameterCodes::QUOTE_STATUS_CONFIRM);
if ($user !== null) {
$qb = $qb
->join('q.seller', 'u')
->andWhere($qb->expr()->eq('u.id', $user->getId()));
}
return $qb
->getQuery()
->getSingleScalarResult()
;
}
public function countAllConversionQuotes(?User $user = null): int
{
$qb = $this->createQueryBuilder('q');
$qb = $qb
->select('count(q.id)')
->join('q.stateType', 'qst')
->where($qb->expr()->eq('qst.code', ':code'))
->setParameter('code', ParameterCodes::QUOTE_STATUS_CONFIRM);
if ($user !== null) {
$qb = $qb
->join('q.seller', 'u')
->andWhere($qb->expr()->eq('u.id', $user->getId()));
}
return $qb
->getQuery()
->getSingleScalarResult()
;
}
/**
* @param Contact $contact
* @param QueryBuilder|null $queryBuilder
* @return QueryBuilder
*/
private function getContactQuoteBaseQueryBuilder(Contact $contact, ?QueryBuilder $queryBuilder = null): QueryBuilder
{
if ($queryBuilder === null) {
$queryBuilder = $this->createQueryBuilder('q');
}
else {
$queryBuilder = $queryBuilder->from(Quote::class, 'q');
}
return $queryBuilder
->select('q')
->join('q.contact', 'c')
->join('q.stateType', 'qst')
->leftJoin('q.quoteReasonStatus', 'qrs')
->leftJoin('q.booking', 'b')
->leftJoin('q.cruiseHistory', 'cr')
->leftJoin('cr.ship', 's')
->leftJoin('s.company', 'co')
->andWhere($queryBuilder->expr()->eq('c.id', $contact->getId()));
}
/**
* @param Contact $contact
* @param QueryBuilder|null $queryBuilder
* @return QueryBuilder
*/
public function getContactQuoteInProgressQueryBuilder(Contact $contact, ?QueryBuilder $queryBuilder = null): QueryBuilder
{
$queryBuilder = $this->getContactQuoteBaseQueryBuilder($contact, $queryBuilder);
return $queryBuilder
->andWhere($queryBuilder->expr()->andX(
$queryBuilder->expr()->gte('q.quoteValidityAt', ':now'),
$queryBuilder->expr()->isNotNull('q.quoteValidityAt')
))
->andWhere($queryBuilder->expr()->isNull('b.id'))
->setParameter('now', new DateTime());
}
/**
* @param Contact $contact
* @param QueryBuilder|null $queryBuilder
* @return QueryBuilder
*/
public function getContactPastQuoteQueryBuilder(Contact $contact, ?QueryBuilder $queryBuilder = null): QueryBuilder
{
$queryBuilder = $this->getContactQuoteBaseQueryBuilder($contact, $queryBuilder);
return $queryBuilder
->andWhere(
$queryBuilder->expr()->orX(
$queryBuilder->expr()->andX(
$queryBuilder->expr()->lt('q.quoteValidityAt', ':now'),
$queryBuilder->expr()->isNotNull('q.quoteValidityAt')
),
$queryBuilder->expr()->isNotNull('b.id')
)
)
->setParameter('now', new DateTime());
}
/**
* Get the list of current quotes for a contact
*
* @param Contact $contact
* @return array
*/
public function getContactQuoteInProgress(Contact $contact): array
{
return $this->getContactQuoteInProgressQueryBuilder($contact)
->getQuery()
->getResult();
}
/**
* Get the list of past quotes for a contact
*
* @param Contact $contact
* @return array
*/
public function getContactPastQuote(Contact $contact): array
{
return $this->getContactPastQuoteQueryBuilder($contact)
->getQuery()
->getResult();
}
/**
* Get the list of all quotes for a contact
*
* @param Contact $contact
* @return array
*/
public function getContactAllQuote(Contact $contact): array
{
return $this->getContactQuoteBaseQueryBuilder($contact)
->getQuery()
->getResult();
}
}

src/Repository/Booking/QuoteRepository.php line 37