src/Repository/Booking/ProspectRequestRepository.php line 62

Open in your IDE?
  1. <?php
  3. namespace App\Repository\Booking;
  5. use App\Controller\__Sale\ProspectRequestController;
  6. use App\DBAL\Types\Booking\BookingStep;
  7. use App\Entity\__Contact\contact\Contact;
  8. use App\Entity\Booking\ProspectRequest;
  9. use App\Entity\Contact\Prospect;
  10. use App\Entity\ParameterCodes;
  11. use App\Entity\User\User;
  12. use App\Migration\Entity\CsvProspectRequest;
  13. use App\Repository\User\UserRepository;
  14. use App\Voter\ProspectRequestVoter;
  15. use Crea\ParameterBundle\Entity\Parameter;
  16. use Crea\SecurityBundle\Helper\AccessHelper;
  17. use Crea\SecurityBundle\Model\Access;
  18. use DateTime;
  19. use DateTimeInterface;
  20. use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
  21. use Doctrine\ORM\NonUniqueResultException;
  22. use Doctrine\ORM\NoResultException;
  23. use Doctrine\ORM\QueryBuilder;
  24. use Doctrine\Persistence\ManagerRegistry;
  25. use Exception;
  26. use Symfony\Component\HttpFoundation\RequestStack;
  27. use Symfony\Component\Security\Core\Security;
  29. /**
  30.  * @method ProspectRequest|null find($id, $lockMode = null, $lockVersion = null)
  31.  * @method ProspectRequest|null findOneBy(array $criteria, array $orderBy = null)
  32.  * @method ProspectRequest[]    findAll()
  33.  * @method ProspectRequest[]    findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
  34.  */
  35. class ProspectRequestRepository extends ServiceEntityRepository
  36. {
  37.     const USER_GROUP_CODE_BY_PROSPECT_REQUEST_TYPE = [
  38.         ParameterCodes::PROSPECT_REQUEST_TYPE_CONTACT => UserRepository::MARKETING_USER_GROUP_CODE,
  39.         ParameterCodes::PROSPECT_REQUEST_TYPE_QUOTE_REQUEST => UserRepository::SELLER_USER_GROUP_CODE,
  40.         ParameterCodes::PROSPECT_REQUEST_TYPE_CONTACT_ADV => UserRepository::BACK_OFFICE_USER_GROUP_CODE
  41.     ];
  42.     const USER_GROUP_CODE_BY_PROSPECT_REQUEST_TYPE_DEFAULT = UserRepository::SELLER_USER_GROUP_CODE;
  43.     const NUMBER_OF_REMINDERS_BEFORE_PERMANENT_MUTED = 1;
  45.     const STATE_CONFIRMED = 'confirmed';
  46.     const STATE_CANCELLED = 'cancelled';
  47.     const ANOMALY = 'anomaly';
  49.     const CONTACT_TYPE_CLIENT = 'CONTACT_TYPE_CLIENT';
  50.     const CONTACT_TYPE_PROSPECT = 'CONTACT_TYPE_PROSPECT';
  51.     const CONTACT_TYPE_NEW_CONTACT = 'CONTACT_TYPE_NEW_CONTACT';
  53.     protected Security $security;
  54.     protected AccessHelper $accessHelper;
  55.     protected RequestStack $requestStack;
  57.     public function __construct(ManagerRegistry $registry,
  58.                                 Security $security,
  59.                                 AccessHelper $accessHelper,
  60.                                 RequestStack $requestStack)
  61.     {
  62.         parent::__construct($registry, ProspectRequest::class);
  63.         $this->security = $security;
  64.         $this->accessHelper = $accessHelper;
  65.         $this->requestStack = $requestStack;
  66.     }
  68.     private function getUnassignedRequestsQueryBuilder(string $select): QueryBuilder
  69.     {
  70.         $qb1 = $this->createQueryBuilder('pr1');
  72.         $qb1 = $qb1
  73.             ->select($select)
  74.             ->join('pr1.status', 'st1')
  75.             ->andWhere('pr1.assignedUser IS NULL')
  76.             ->andWhere('st1.code = :newStateTypeId')
  77.             ->setParameter('newStateTypeId', ParameterCodes::PROSPECT_REQUEST_STATUS_NOT_ASSIGNED);
  79.         return $qb1;
  80.     }
  82.     public function getUnassignedRequestsQuery(): QueryBuilder
  83.     {
  84.         return $this->getUnassignedRequestsQueryBuilder('pr1');
  85.     }
  87.     private function getRequestsByTypeToAssignBaseQuery(string $typeCode, QueryBuilder $qb, string $pre = ''):QueryBuilder
  88.     {
  89.         $qb = $qb
  90.             ->join( $pre.'pr.type', $pre.'prt')
  91.             ->join( $pre.'pr.status', $pre.'prst')
  92.             ->where($qb->expr()->eq($pre.'prt.code', ':code'))
  93.             ->andWhere($qb->expr()->in($pre.'prst.code', ':statusCodes'))
  94.             ->andWhere($qb->expr()->isNull($pre.'pr.assignedUser'))
  95.             ->andWhere($qb->expr()->eq($pre.'pr.isActive', ':isActive'))
  96.             ->andWhere($qb->expr()->eq($pre.'pr.isQuoteSent', ':isQuoteSent'))
  97.             ->setParameter('code', $typeCode)
  98.             ->setParameter('statusCodes', [
  99.                 ParameterCodes::PROSPECT_REQUEST_STATUS_NOT_ASSIGNED
  100.             ])
  101.             ->setParameter('isActive', true)
  102.             ->setParameter('isQuoteSent', false);
  104.         return $qb;
  105.     }
  107.     private function getRequestsByTypeBaseQuery(string $typeCode, QueryBuilder $qb, string $pre = ''):QueryBuilder
  108.     {
  109.         $qb = $qb
  110.             ->join( $pre.'pr.type', $pre.'prt')
  111.             ->where($qb->expr()->eq($pre.'prt.code', ':code'))
  112.             ->andWhere($qb->expr()->eq($pre.'pr.isActive', ':isActive'))
  113.             ->setParameter('code', $typeCode)
  114.             ->setParameter('isActive', true);
  116.         if (null !== $state = $this->requestStack->getCurrentRequest()->query->get('state')) {
  117.             if ($state === self::STATE_CONFIRMED) {
  118.                 $qb = $qb
  119.                     ->innerJoin( $pre.'pr.quotes', $pre.'prq')
  120.                     ->innerJoin( $pre.'prq.booking', $pre.'prb');
  121.             }
  122.             elseif ($state === self::STATE_CANCELLED) {
  123.                 $qb = $qb
  124.                     ->leftJoin( $pre.'pr.quotes', $pre.'prq')
  125.                     ->leftJoin( $pre.'prq.booking', $pre.'prb')
  126.                     ->andWhere($qb->expr()->andX(
  127.                         $qb->expr()->isNull($pre.'prb.id'),
  128.                         $qb->expr()->lt($pre.'prq.quoteValidityAt', ':now')
  129.                     ))
  130.                     ->setParameter('now', new DateTime())
  131.                 ;
  132.             }
  133.         }
  135.         return $qb;
  136.     }
  138.     public function getRequestsByTypeToAssignQuery(string $typeCode, bool $groupByProject = false): QueryBuilder
  139.     {
  140.         $qb = $this->createQueryBuilder('pr');
  141.         $qb = $this->getRequestsByTypeToAssignBaseQuery($typeCode, $qb);
  143.         if ($groupByProject) {
  144.             $pre = 'sub_';
  145.             $qb2 = $this->createQueryBuilder($pre.'pr')
  146.                 ->select($pre.'pr.id');
  147.             $qb2 = $this->getRequestsByTypeToAssignBaseQuery($typeCode, $qb2, $pre)
  148.                 ->join( $pre.'pr.prospectProject', $pre.'prp')
  149.                 ->groupBy($pre.'prp.id');
  151.             $ids = array_map(function ($line) {
  152.                 return $line['id'];
  153.             }, $qb2->getQuery()->getArrayResult());
  155.             if (count($ids) > 0) {
  156.                 $qb->andWhere($qb->expr()->in('pr.id', $ids));
  157.             }
  158.         }
  160.         return $qb;
  161.     }
  163.     public function getRequestsByTypeQuery(string $typeCode, bool $groupByProject = false, $assigned = false): QueryBuilder
  164.     {
  165.         $qb = $this->createQueryBuilder('pr');
  166.         $qb = $this->getRequestsByTypeBaseQuery($typeCode, $qb);
  168.         if ($assigned) {
  169.             $qb = $qb->innerJoin( 'pr.assignedUser', 'u');
  170.         }
  172.         if ($typeCode === ParameterCodes::PROSPECT_REQUEST_TYPE_CONTACT) {
  173.             $hasAccess = $this->accessHelper->hasAccess(new Access([ProspectRequestVoter::SHOW_CONTACT_REQUESTS_FROM_ALL_USERS]));
  174.         }
  175.         elseif ($typeCode === ParameterCodes::PROSPECT_REQUEST_TYPE_CONTACT_ADV) {
  176.             $hasAccess = $this->accessHelper->hasAccess(new Access([ProspectRequestVoter::SHOW_ADV_CONTACT_REQUESTS_FROM_ALL_USERS]));
  177.         }
  178.         else {
  179.             $hasAccess = $this->accessHelper->hasAccess(new Access([ProspectRequestVoter::SHOW_QUOTE_REQUESTS_FROM_ALL_USERS]));
  180.         }
  182.         if (!$hasAccess) {
  183.             /** @var User $user */
  184.             $user = $this->security->getUser();
  185.             $qb = $qb
  186.                 ->join('pr.assignedUser', 'u')
  187.                 ->andWhere($qb->expr()->eq('u.id', $user->getId()));
  188.         }
  190.         if ($groupByProject) {
  191.             $pre = 'sub_';
  192.             $qb2 = $this->createQueryBuilder($pre.'pr')
  193.                 ->select($pre.'pr.id');
  194.             $qb2 = $this->getRequestsByTypeBaseQuery($typeCode, $qb2, $pre)
  195.                 ->join( $pre.'pr.prospectProject', $pre.'prp')
  196.                 ->groupBy($pre.'prp.id');
  198.             if ($assigned) {
  199.                 $qb2 = $qb2->innerJoin( $pre.'pr.assignedUser', $pre.'u');
  200.             }
  202.             if (!$hasAccess) {
  203.                 /** @var User $user */
  204.                 $user = $this->security->getUser();
  205.                 $qb2 = $qb2
  206.                     ->join($pre.'pr.assignedUser', $pre.'u')
  207.                     ->andWhere($qb2->expr()->eq($pre.'u.id', $user->getId()));
  208.             }
  210.             $ids = array_map(function ($line) {
  211.                 return $line['id'];
  212.             }, $qb2->getQuery()->getArrayResult());
  214.             if (count($ids) > 0) {
  215.                 $qb->andWhere($qb->expr()->in('pr.id', $ids));
  216.             }
  217.         }
  219.         return $qb;
  220.     }
  222.     public function getRequestsByTypeQuoteQuery(): QueryBuilder
  223.     {
  224.         return $this->getRequestsByTypeQuery(ParameterCodes::PROSPECT_REQUEST_TYPE_QUOTE_REQUEST, false, true);
  225.     }
  227.     public function getDtBaseQuery(QueryBuilder $qb, string $origin, string $typeCode, string $pre = ''): QueryBuilder
  228.     {
  229.         $qb = $qb
  230.             ->join( $pre.'pr.type', $pre.'prt')
  231.             ->leftJoin($pre.'pr.status', $pre.'st')
  232.             ->leftJoin($pre.'pr.statusReason', $pre.'str')
  233.             ->leftJoin($pre.'pr.prospectRequestOrigin', $pre.'pro')
  234.             ->leftJoin($pre.'pr.contactProspect', $pre.'cp')
  235.             ->leftJoin($pre.'cp.contact', $pre.'c')
  236.             ->leftJoin($pre.'c.type', $pre.'cot')
  237.             ->leftJoin($pre.'cp.prospect', $pre.'pros')
  238.             ->leftJoin($pre.'pr.prospectProject', $pre.'pp')
  239.             ->leftJoin($pre.'pr.cruiseHistory', $pre.'ch')
  240.             ->leftJoin($pre.'ch.ship', $pre.'s')
  241.             ->leftJoin($pre.'s.company', $pre.'com')
  242.             ->leftJoin($pre.'ch.itineraryHistory', $pre.'ih')
  243.             ->leftJoin($pre.'ih.destination', $pre.'d')
  244.             ->leftJoin($pre.'pr.cabinCategoryHistory', $pre.'cch')
  245.             ->leftJoin($pre.'cch.type', $pre.'cct')
  246.             ->leftJoin($pre.'cct.cabinType', $pre.'ct')
  247.             ->leftJoin($pre.'pr.partner', $pre.'p')
  248.             ->leftJoin($pre.'c.partnerByContacts', $pre.'pbc')
  249.             ->leftJoin($pre.'pbc.partner', $pre.'p2')
  250.             ->where($qb->expr()->eq($pre.'prt.code', ':typeCode'))
  251.             ->andWhere($qb->expr()->eq($pre.'pr.isActive', ':isActive'))
  252.             ->andWhere($qb->expr()->eq($pre.'pr.removed', ':removed'))
  253.             ->setParameter('typeCode', $typeCode)
  254.             ->setParameter('isActive', true)
  255.             ->setParameter('removed', false);
  257.         if ($origin === ProspectRequestController::ORIGIN_TO_ASSIGN && $typeCode === ParameterCodes::PROSPECT_REQUEST_TYPE_QUOTE_REQUEST) {
  258.             $qb = $qb
  259.                 ->andWhere($qb->expr()->in($pre.'st.code', ':statusCodes'))
  260.                 ->andWhere($qb->expr()->isNull($pre.'pr.assignedUser'))
  261.                 ->andWhere($qb->expr()->eq($pre.'pr.isQuoteSent', ':isQuoteSent'))
  262.                 ->setParameter('statusCodes', [
  263.                     ParameterCodes::PROSPECT_REQUEST_STATUS_NOT_ASSIGNED
  264.                 ])
  265.                 ->setParameter('isQuoteSent', false);
  266.         }
  268.         return $qb;
  269.     }
  271.     public function getDtQuery(QueryBuilder $qb, string $origin, string $type, ?int $projectId = null): QueryBuilder
  272.     {
  273.         if ($type === ProspectRequestController::TYPE_CONTACT) {
  274.             $typeCode = ParameterCodes::PROSPECT_REQUEST_TYPE_CONTACT;
  275.             $hasAccess = $this->accessHelper->hasAccess(new Access([ProspectRequestVoter::SHOW_CONTACT_REQUESTS_FROM_ALL_USERS]));
  276.         }
  277.         elseif ($type === ProspectRequestController::TYPE_ADV) {
  278.             $typeCode = ParameterCodes::PROSPECT_REQUEST_TYPE_CONTACT_ADV;
  279.             $hasAccess = $this->accessHelper->hasAccess(new Access([ProspectRequestVoter::SHOW_ADV_CONTACT_REQUESTS_FROM_ALL_USERS]));
  280.         }
  281.         else {
  282.             $typeCode = ParameterCodes::PROSPECT_REQUEST_TYPE_QUOTE_REQUEST;
  283.             $hasAccess = $this->accessHelper->hasAccess(new Access([ProspectRequestVoter::SHOW_QUOTE_REQUESTS_FROM_ALL_USERS]));
  284.         }
  286.         if ($origin === ProspectRequestController::ORIGIN_TO_ASSIGN) {
  287.             $assigned = false;
  288.         }
  289.         else {
  290.             $assigned = true;
  291.         }
  293.         $qb = $this->getDtBaseQuery($qb, $origin, $typeCode);
  295.         if ($origin === ProspectRequestController::ORIGIN_TO_ASSIGN && $projectId === null) {
  296.             $pre = 'sub_';
  297.             $qb2 = $this->createQueryBuilder($pre.'pr')
  298.                 ->select($pre.'pr.id');
  299.             $qb2 = $this->getDtBaseQuery($qb2, $origin, $typeCode, $pre)
  300.                 ->join( $pre.'pr.prospectProject', $pre.'prp')
  301.                 ->groupBy($pre.'prp.id');
  303.             $ids = array_map(function ($line) {
  304.                 return $line['id'];
  305.             }, $qb2->getQuery()->getArrayResult());
  307.             if (count($ids) > 0) {
  308.                 $qb->andWhere($qb->expr()->in('pr.id', $ids));
  309.             }
  310.         }
  311.         elseif ($projectId !== null) {
  312.             $qb = $qb->innerJoin( 'pr.prospectProject', 'prp')
  313.                 ->andWhere($qb->expr()->eq('prp.id', $projectId));
  314.         }
  316.         if ($assigned) {
  317.             $qb = $qb
  318.                 ->leftJoin( 'pr.assignedUser', 'au')
  319.                 ->andWhere($qb->expr()->orX(
  320.                     $qb->expr()->isNotNull('au.id'),
  321.                     $qb->expr()->eq('st.code', ':stCode')
  322.                 ))
  323.                 ->setParameter('stCode', ParameterCodes::PROSPECT_REQUEST_STATUS_DONT_PROCESS);
  324.         }
  325.         else {
  326.             $qb = $qb->andWhere($qb->expr()->isNull('pr.assignedUser'));
  327.         }
  330.         if (!$hasAccess) {
  331.             /** @var User $user */
  332.             $user = $this->security->getUser();
  333.             $qb = $qb
  334.                 ->join('pr.assignedUser', 'u')
  335.                 ->andWhere($qb->expr()->eq('u.id', $user->getId()));
  336.         }
  338.         return $qb;
  339.     }
  341.     public function getRequestsByTypeContactQuery(bool $groupByProject = true, bool $assigned = true): QueryBuilder
  342.     {
  343.         return $this->getRequestsByTypeQuery(ParameterCodes::PROSPECT_REQUEST_TYPE_CONTACT, $groupByProject, $assigned);
  344.     }
  346.     public function getRequestsByTypeContactAdvQuery(bool $groupByProject = true): QueryBuilder
  347.     {
  348.         return $this->getRequestsByTypeQuery(ParameterCodes::PROSPECT_REQUEST_TYPE_CONTACT_ADV, $groupByProject);
  349.     }
  351.     public function getRequestsByTypeQuoteToAssignQuery(bool $groupByProject = true): QueryBuilder
  352.     {
  353.         return $this->getRequestsByTypeToAssignQuery(ParameterCodes::PROSPECT_REQUEST_TYPE_QUOTE_REQUEST, $groupByProject);
  354.     }
  356.     public function getRequestsByTypeContactToAssignQuery(bool $groupByProject = true): QueryBuilder
  357.     {
  358.         return $this->getRequestsByTypeToAssignQuery(ParameterCodes::PROSPECT_REQUEST_TYPE_CONTACT, $groupByProject);
  359.     }
  361.     public function getRequestsByTypeContactAdvToAssignQuery(): QueryBuilder
  362.     {
  363.         return $this->getRequestsByTypeToAssignQuery(ParameterCodes::PROSPECT_REQUEST_TYPE_CONTACT_ADV);
  364.     }
  366.     /**
  367.      * @param array $originCodes
  368.      * @param bool $groupByProject
  369.      * @return array|ProspectRequest[]
  370.      */
  371.     public function getRequestsByTypeQuoteToAssign(array $originCodes = [], bool $groupByProject = true): array
  372.     {
  373.         $qb = $this->getRequestsByTypeQuoteToAssignQuery($groupByProject);
  375.         if (count($originCodes) > 0) {
  376.             $qb = $qb->join('pr.prospectRequestOrigin', 'pro')
  377.                 ->andWhere($qb->expr()->in('pro.code', ':originCodes'))
  378.                 ->setParameter('originCodes', $originCodes);
  379.         }
  381.         return $qb
  382.             ->getQuery()
  383.             ->getResult();
  384.     }
  386.     /**
  387.      * @param array $originCodes
  388.      * @param bool $groupByProject
  389.      * @return array|ProspectRequest[]
  390.      */
  391.     public function getRequestsByTypeContactToAssign(array $originCodes = [], bool $groupByProject = true): array
  392.     {
  393.         $qb = $this->getRequestsByTypeContactToAssignQuery($groupByProject);
  395.         if (count($originCodes) > 0) {
  396.             $qb = $qb->join('pr.prospectRequestOrigin', 'pro')
  397.                 ->andWhere($qb->expr()->in('pro.code', ':originCodes'))
  398.                 ->setParameter('originCodes', $originCodes);
  399.         }
  401.         return $qb
  402.             ->getQuery()
  403.             ->getResult();
  404.     }
  406.     /**
  407.      * @param int $projectId
  408.      * @param $unassigned
  409.      * @param $prospectRequestTypeCode
  410.      * @return array|ProspectRequest[]
  411.      */
  412.     public function getProjectRequests(int $projectId, $unassigned = true, $prospectRequestTypeCode = null): array
  413.     {
  414.         $qb = $this->getProjectRequestQueryBuilder($projectId, $unassigned, $prospectRequestTypeCode);
  415.         return $qb->getQuery()->getResult();
  416.     }
  418.     public function countProjectRequests(int $projectId, $unassigned = true, $prospectRequestTypeCode = null): int
  419.     {
  420.         try {
  421.             $qb = $this->getProjectRequestQueryBuilder($projectId, $unassigned, $prospectRequestTypeCode)->select('count(pr.id)');
  422.             return $qb->getQuery()->getSingleScalarResult();
  423.         }
  424.         catch (NonUniqueResultException|NoResultException $e) {
  425.             return 0;
  426.         }
  427.     }
  429.     /**
  430.      * @param Contact|Prospect $contactOrProspect
  431.      *
  432.      * @return ProspectRequest[]
  433.      */
  434.     public function findContactOrProspectRequestsByDate($contactOrProspect, DateTimeInterface $date, Parameter $type): array
  435.     {
  436.         $queryBuilder = $this->createQueryBuilder('pr')
  437.             ->join('pr.type', 'pt')
  438.             ->join('pr.contactProspect', 'cp')
  439.             ->andWhere('DATE_FORMAT(pr.createdAt, \'%Y-%m-%d\') = :date')
  440.             ->setParameter('date', $date->format('Y-m-d'));
  442.         $queryBuilder = $queryBuilder
  443.             ->andWhere($queryBuilder->expr()->eq('pt.id', ':type'))
  444.             ->setParameter('type', $type->getId());
  446.         if ($contactOrProspect instanceof Contact) {
  447.             $queryBuilder
  448.                 ->andWhere('cp.contact = :contact')
  449.                 ->setParameter('contact', $contactOrProspect);
  450.         } else {
  451.             $queryBuilder
  452.                 ->andWhere('cp.prospect = :prospect')
  453.                 ->setParameter('prospect', $contactOrProspect);
  454.         }
  456.         return $queryBuilder
  457.             ->getQuery()
  458.             ->getResult();
  459.     }
  461.     public function findByDates(DateTime $startingDate, DateTime $endingDate): array
  462.     {
  463.         return $this->createQueryBuilder('pr')
  464.             ->andWhere('pr.appointmentDate >= :startingDate')
  465.             ->andWhere('pr.appointmentDate <= :endingDate')
  466.             ->setParameter('startingDate', $startingDate->format('Y-m-d'))
  467.             ->setParameter('endingDate', $endingDate->format('Y-m-d'))
  468.             ->getQuery()
  469.             ->getResult();
  470.     }
  472.     public function findBySellerAndDates(User $seller, DateTime $startingDate, DateTime $endingDate): array
  473.     {
  474.         return $this->createQueryBuilder('pr')
  475.             ->join('pr.assignedUser', 's')
  476.             ->where('s = :seller')
  477.             ->andWhere('pr.appointmentDate >= :startingDate')
  478.             ->andWhere('pr.appointmentDate <= :endingDate')
  479.             ->setParameter('seller', $seller)
  480.             ->setParameter('startingDate', $startingDate->format('Y-m-d'))
  481.             ->setParameter('endingDate', $endingDate->format('Y-m-d'))
  482.             ->getQuery()
  483.             ->getResult();
  484.     }
  486.     /**
  487.      * Récupère les demandes de devis d'un contact.
  488.      *
  489.      * @return ProspectRequest[]
  490.      */
  491.     public function findQuoteRequests(Contact $contact): array
  492.     {
  493.         return $this->createQueryBuilder('pr')
  494.             ->innerJoin('pr.type', 'prt')
  495.             ->innerJoin('pr.contactProspect', 'cp')
  496.             ->where('cp.contact = :contact')
  497.             ->andWhere('prt.code = :quoteRequest')
  498.             ->setParameters([
  499.                 'contact' => $contact,
  500.                 'quoteRequest' => ParameterCodes::PROSPECT_REQUEST_TYPE_QUOTE_REQUEST,
  501.             ])
  502.             ->getQuery()
  503.             ->getResult()
  504.         ;
  505.     }
  507.     /**
  508.      * @return ProspectRequest[]
  509.      */
  510.     public function findToProcessedBySeller(User $seller, array $originCodes = []): array
  511.     {
  512.         $qb = $this->createQueryBuilder('pr');
  513.         $qb = $qb
  514.             ->join('pr.assignedUser', 's')
  515.             ->join('pr.status', 'st')
  516.             ->andWhere($qb->expr()->eq('s.id', ':sellerId'))
  517.             ->andWhere($qb->expr()->in('st.code', ':statusCodes'))
  518.             ->andWhere($qb->expr()->eq('pr.isActive', ':isActive'))
  519.             ->setParameter('sellerId', $seller->getId())
  520.             ->setParameter('isActive', true)
  521.             ->setParameter('statusCodes', [
  522.                 ParameterCodes::PROSPECT_REQUEST_STATUS_MUTED,
  523.                 ParameterCodes::PROSPECT_REQUEST_STATUS_ATTRIBUTED_AND_NOT_PROCESSED
  524.             ]);
  526.         if (count($originCodes) > 0) {
  527.             $qb = $qb
  528.                 ->join('pr.prospectRequestOrigin', 'pro')
  529.                 ->andWhere($qb->expr()->in('pro.code', ':originCodes'))
  530.                 ->setParameter('originCodes', $originCodes);
  531.         }
  533.         return $qb
  534.             ->orderBy('pr.appointmentDate')
  535.             ->getQuery()
  536.             ->getResult();
  537.     }
  539.     /********************************* home-module *************************************/
  541.     public function countThisDateProspectRequest($type, $date, ?bool $assignedUser = null): int
  542.     {
  543.         $qb = $this->createQueryBuilder('pr')
  544.             ->select('count(pr.id)')
  545.             ->where('pr.createdAt > :date')
  546.             ->andWhere('pr.type = :type');
  548.         if ($assignedUser !== null) {
  549.             $qb = $assignedUser === true ? $qb->andWhere('pr.assignedUser IS NOT NULL') : $qb->andWhere('pr.assignedUser IS NULL');
  550.         }
  552.         return $qb
  553.             ->setParameter('date', $date)
  554.             ->setParameter('type', $type)
  555.             ->getQuery()
  556.             ->getSingleScalarResult();
  557.     }
  559.     public function countBeforeThisDateProspectRequest($type, $date, ?bool $assignedUser = null): int
  560.     {
  561.         $qb = $this->createQueryBuilder('pr')
  562.             ->select('count(pr.id)')
  563.             ->where('pr.createdAt < :date')
  564.             ->andWhere('pr.type = :type')
  565.         ;
  567.         if ($assignedUser !== null) {
  568.             $qb = $assignedUser === true ? $qb->andWhere('pr.assignedUser IS NOT NULL') : $qb->andWhere('pr.assignedUser IS NULL');
  569.         }
  571.         return $qb
  572.             ->setParameter('date', $date)
  573.             ->setParameter('type', $type)
  574.             ->getQuery()
  575.             ->getSingleScalarResult()
  576.         ;
  577.     }
  579.     public function countBetweenThisDateProspectRequest($type, $startDate, $endDate, ?bool $assignedUser = null): int
  580.     {
  581.         $qb = $this->createQueryBuilder('pr')
  582.             ->select('count(pr.id)')
  583.             ->where('pr.createdAt > :startDate')
  584.             ->andWhere('pr.createdAt < :endDate')
  585.             ->andWhere('pr.type = :type')
  586.             ->setParameter('startDate', $startDate)
  587.             ->setParameter('endDate', $endDate)
  588.             ->setParameter('type', $type)
  589.         ;
  591.         if ($assignedUser !== null) {
  592.             $qb = $assignedUser === true ? $qb->andWhere('pr.assignedUser IS NOT NULL') : $qb->andWhere('pr.assignedUser IS NULL');
  593.         }
  595.         return $qb
  596.             ->getQuery()
  597.             ->getSingleScalarResult()
  598.         ;
  599.     }
  601.     public function countBetweenThisDateWithOrigin(string $originCode, ?\DateTimeInterface $startDate = null, ?\DateTimeInterface $endDate = null, ?bool $assignedUser = null, ?bool $processToQuote = null): int
  602.     {
  603.         $qb = $this->createQueryBuilder('pr')
  604.             ->select('count(pr.id)')
  605.             ->join('pr.prospectRequestOrigin', 'pro')
  606.             ->where('pro.code = :originCode')
  607.             ->setParameter('originCode', $originCode);
  609.         if ($assignedUser !== null) {
  610.             $qb = $assignedUser === true ? $qb->andWhere('pr.assignedUser IS NOT NULL') : $qb->andWhere('pr.assignedUser IS NULL');
  611.         }
  613.         if ($startDate !== null) {
  614.             $qb = $qb
  615.                 ->andWhere('pr.createdAt > :startDate')
  616.                 ->setParameter('startDate', $startDate);
  617.         }
  619.         if ($endDate !== null) {
  620.             $qb = $qb
  621.                 ->andWhere('pr.createdAt < :endDate')
  622.                 ->setParameter('endDate', $endDate);
  623.         }
  625.         if ($processToQuote !== null) {
  626.             if ($processToQuote === true) {
  627.                 $qb->join('pr.quotes', 'q');
  628.             }
  629.             else {
  630.                 $qb
  631.                     ->leftJoin('pr.quotes', 'q')
  632.                     ->andWhere($qb->expr()->isNull('q.id'));
  633.             }
  634.         }
  636.         return $qb
  637.             ->getQuery()
  638.             ->getSingleScalarResult();
  639.     }
  641.     /**
  642.      * @return ProspectRequest[]
  643.      */
  644.     public function findQuoteRequestsMuted(): array
  645.     {
  646.         $qb = $this->createQueryBuilder('pr');
  648.         $result = $qb
  649.             ->join('pr.prospectRequestOrigin', 'pro')
  650.             ->join('pr.status', 'st')
  651.             ->andWhere($qb->expr()->in('pro.code', ':codes'))
  652.             ->andWhere($qb->expr()->eq('st.code', ':statusCode'))
  653.             ->andWhere($qb->expr()->gte('pr.updatedAt', ':nowLess24'))
  654.             ->andWhere($qb->expr()->gte('pr.createdAt', ':nowLess7days'))
  655.             ->setParameter('codes', [
  656.                 ParameterCodes::PROSPECT_REQUEST_ORIGIN_WEB,
  657.                 ParameterCodes::PROSPECT_REQUEST_ORIGIN_OTHER
  658.             ])
  659.             ->setParameter('statusCode', ParameterCodes::PROSPECT_REQUEST_STATUS_MUTED)
  660.             ->setParameter('nowLess24', (new DateTime())->modify('-24 hours'))
  661.             ->setParameter('nowLess7days', (new DateTime())->modify('-7 days'))
  662.         ;
  664.         return
  665.             $result
  666.             ->getQuery()
  667.             ->getResult()
  668.         ;
  669.     }
  671.     public function findMutedWithReminderToDo(?User $seller = null): array
  672.     {
  673.         $qb = $this->createQueryBuilder('pr');
  675.         if ($seller !== null) {
  676.             $qb = $qb
  677.                 ->join('pr.assignedUser', 'u')
  678.                 ->andWhere($qb->expr()->eq('u.id', $seller->getId()));
  679.         }
  681.         return $qb
  682.             ->join('pr.prospectRequestOrigin', 'pro')
  683.             ->join('pr.status', 'st')
  684.             ->innerJoin('pr.reminders', 'r')
  685.             ->andWhere($qb->expr()->eq('st.code', ':statusCode'))
  686.             ->andWhere($qb->expr()->lte('r.remindAt', ':now'))
  687.             ->andWhere($qb->expr()->eq('r.muted', ':muted'))
  688.             ->setParameter('now', new \DateTime())
  689.             ->setParameter('muted', false)
  690.             ->setParameter('statusCode', ParameterCodes::PROSPECT_REQUEST_STATUS_MUTED)
  691.             ->getQuery()
  692.             ->getResult()
  693.         ;
  694.     }
  696.     public function findByOriginAndStateType(string $originCode, string $statusCode): array
  697.     {
  698.         $qb = $this->createQueryBuilder('pr');
  700.         return $qb
  701.             ->join('pr.status', 'st')
  702.             ->join('pr.assignedUser', 'u')
  703.             ->join('pr.prospectRequestOrigin', 'pro')
  704.             ->where($qb->expr()->eq('st.code', ':statusCode'))
  705.             ->andWhere($qb->expr()->isNotNull('u.id'))
  706.             ->andWhere($qb->expr()->eq('pro.code', ':originCode'))
  707.             ->andWhere($qb->expr()->eq('pr.isActive', ':isActive'))
  708.             ->setParameter('statusCode', $statusCode)
  709.             ->setParameter('originCode', $originCode)
  710.             ->setParameter('isActive', true)
  711.             ->getQuery()
  712.             ->getResult()
  713.         ;
  714.     }
  716.     /**
  717.      * @param Contact $contact
  718.      * @param QueryBuilder|null $queryBuilder
  719.      * @return QueryBuilder
  720.      */
  721.     private function getContactQuoteRequestsBaseQueryBuilder(Contact $contact, ?QueryBuilder $queryBuilder = null): QueryBuilder
  722.     {
  723.         if ($queryBuilder === null) {
  724.             $queryBuilder = $this->createQueryBuilder('pr');
  725.         }
  726.         else {
  727.             $queryBuilder = $queryBuilder->from(ProspectRequest::class, 'pr');
  728.         }
  730.         return $queryBuilder
  731.             ->select('pr', 'pro')
  732.             ->join('pr.prospectRequestOrigin', 'pro')
  733.             ->join('pr.type', 'pt')
  734.             ->join('pr.contactProspect', 'cp')
  735.             ->join('cp.contact', 'c')
  736.             ->leftJoin('pr.assignedUser', 's')
  737.             ->leftJoin('pr.cruiseHistory', 'cr')
  738.             ->leftJoin('pr.cabinCategoryHistory', 'cch')
  739.             ->andWhere($queryBuilder->expr()->in('pt.code', ':codes'))
  740.             ->andWhere($queryBuilder->expr()->eq('c.id', $contact->getId()))
  741.             ->andWhere($queryBuilder->expr()->eq('pr.isActive', ':isActive'))
  742.             ->setParameter('codes', [
  743.                 ParameterCodes::PROSPECT_REQUEST_TYPE_QUOTE_REQUEST
  744.             ])
  745.             ->setParameter('isActive', true);
  746.     }
  748.     /**
  749.      * @param Contact $contact
  750.      * @param QueryBuilder|null $queryBuilder
  751.      * @return QueryBuilder
  752.      */
  753.     public function getContactQuoteRequestsInProgressQueryBuilder(Contact $contact, ?QueryBuilder $queryBuilder = null): QueryBuilder
  754.     {
  755.         $queryBuilder = $this->getContactQuoteRequestsBaseQueryBuilder($contact, $queryBuilder);
  756.         return $queryBuilder->andWhere($queryBuilder->expr()->eq('pr.isQuoteSent', 0));
  757.     }
  759.     /**
  760.      * @param Contact $contact
  761.      * @param QueryBuilder|null $queryBuilder
  762.      * @return QueryBuilder
  763.      */
  764.     public function getContactPastQuoteRequestsQueryBuilder(Contact $contact, ?QueryBuilder $queryBuilder = null): QueryBuilder
  765.     {
  766.         $queryBuilder = $this->getContactQuoteRequestsBaseQueryBuilder($contact, $queryBuilder);
  767.         return $queryBuilder->andWhere($queryBuilder->expr()->eq('pr.isQuoteSent', 1));
  768.     }
  770.     /**
  771.      * Get the list of current quote requests for a contact
  772.      *
  773.      * @param Contact $contact
  774.      * @return array
  775.      */
  776.     public function getContactQuoteRequestsInProgress(Contact $contact): array
  777.     {
  778.         return $this->getContactQuoteRequestsInProgressQueryBuilder($contact)
  779.             ->getQuery()
  780.             ->getResult();
  781.     }
  783.     /**
  784.      * Get the list of past quote requests for a contact
  785.      *
  786.      * @param Contact $contact
  787.      * @return array
  788.      */
  789.     public function getContactPastQuoteRequests(Contact $contact): array
  790.     {
  791.         return $this->getContactPastQuoteRequestsQueryBuilder($contact)
  792.             ->getQuery()
  793.             ->getResult();
  794.     }
  796.     /**
  797.      * Get the list of all quote requests for a contact
  798.      *
  799.      * @param Contact $contact
  800.      * @return array
  801.      */
  802.     public function getContactAllQuoteRequests(Contact $contact): array
  803.     {
  804.         return $this->getContactQuoteRequestsBaseQueryBuilder($contact)
  805.             ->getQuery()
  806.             ->getResult();
  807.     }
  809.     /**
  810.      * @return array|ProspectRequest[]
  811.      */
  812.     public function findProspectRequestWithAnomalies(): array
  813.     {
  814.         $qb = $this->createQueryBuilder('pr');
  815.         return $qb
  816.             ->join('pr.status', 'prst')
  817.             ->leftJoin('pr.statusReason', 'prr')
  818.             ->where($qb->expr()->eq('prst.code', ':statusCode'))
  819.             ->andWhere($qb->expr()->isNull('prr.id'))
  820.             ->andWhere($qb->expr()->isNotNull('pr.statusChangeLastDate'))
  821.             ->andWhere($qb->expr()->lt('pr.statusChangeLastDate', ':statusChangeLastDate'))
  822.             ->setParameter('statusCode', ParameterCodes::PROSPECT_REQUEST_STATUS_WITH_DIALOG_WITHOUT_QUOTE)
  823.             ->setParameter('statusChangeLastDate', (new DateTime())->modify('-7 days'))
  824.             ->getQuery()
  825.             ->getResult();
  826.     }
  828.     /**
  829.      * @return array|ProspectRequest[]
  830.      */
  831.     public function findProspectRequestFromStatus(string $statusCode): array
  832.     {
  833.         $qb = $this->createQueryBuilder('pr');
  834.         return $qb
  835.             ->join('pr.status', 'prst')
  836.             ->where($qb->expr()->eq('prst.code', ':statusCode'))
  837.             ->setParameter('statusCode', $statusCode)
  838.             ->getQuery()
  839.             ->getResult();
  840.     }
  842.     /**
  843.      * @return array|ProspectRequest[]
  844.      */
  845.     public function findActiveProspectRequestsFromUser(User $user): array
  846.     {
  847.         $qb = $this->createQueryBuilder('pr');
  848.         return $qb
  849.             ->join('pr.assignedUser', 'au')
  850.             ->join('pr.status', 'prst')
  851.             ->where($qb->expr()->eq('au.id', $user->getId()))
  852.             ->andWhere($qb->expr()->eq('prst.code', ':statusCode'))
  853.             ->setParameter('statusCode', ParameterCodes::PROSPECT_REQUEST_STATUS_ATTRIBUTED_AND_NOT_PROCESSED)
  854.             ->getQuery()
  855.             ->getResult();
  856.     }
  857. }