src/Contact/Repository/DoctrineContactRepository.php line 162

  1. <?php
  3. declare(strict_types=1);
  4. /**
  5.  * Copyright (c) 2019 TECLA Consulting Group oü.
  6.  * All rights reserved.
  7.  *
  8.  * This unpublished material is proprietary to TECLA Consulting Group oü.
  9.  * All rights reserved. The methods and
  10.  * techniques described herein are considered trade secrets
  11.  * and/or confidential. Reproduction or distribution, in whole
  12.  * or in part, is forbidden except by express written permission
  13.  * of TECLA Consulting Group oü.
  14.  *
  15.  * @author    Matúš Sýkorjak <matus@tecla.no>
  16.  * @copyright 2019 TECLA Consulting Group oü
  17.  */
  19. namespace App\Contact\Repository;
  21. use App\Contact\Doctrine\DBAL\Types\ContactIdType;
  22. use App\Contact\Doctrine\DBAL\Types\GenderType;
  23. use App\Contact\Doctrine\DBAL\Types\GroupIdType;
  24. use App\Contact\Doctrine\DBAL\Types\IndustrySectorType;
  25. use App\Contact\Doctrine\DBAL\Types\MaritalStatusType;
  26. use App\Contact\Doctrine\DBAL\Types\WorkOccupationType;
  27. use App\Contact\Model\Contact;
  28. use App\Contact\Model\ContactAddressInterface;
  29. use App\Contact\Model\ContactInterface;
  30. use App\Contact\Model\ContactPermissionInterface;
  31. use App\Contact\Model\ContactRelationshipCustomerSupplierInterface;
  32. use App\Contact\Model\GroupItemInterface;
  33. use App\Contact\Model\Identity\ContactIdInterface;
  34. use App\Contact\Model\Relationship\AccountantInterface;
  35. use App\Contact\Model\Relationship\AuthorizedAccountantInterface;
  36. use App\Contact\Model\Relationship\ManagingDirectorInterface;
  37. use App\Contact\Model\Relationship\EmploymentInterface;
  38. use App\Invoice\Model\InvoicePaymentNotificationInterface;
  39. use App\Note\Model\NoteInterface;
  40. use App\Note\Model\NotePermissionInterface;
  41. use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
  42. use Doctrine\ORM\Query\Expr\Join;
  43. use Doctrine\ORM\QueryBuilder;
  44. use Doctrine\Persistence\ManagerRegistry;
  46. class DoctrineContactRepository extends ServiceEntityRepository
  47. {
  48.     public function __construct(ManagerRegistry $registry)
  49.     {
  50.         parent::__construct($registry, Contact::class);
  51.     }
  53.     /**
  54.      * Returns contact with $authorizeId if contact with $id has permission to access it.
  55.      */
  56.     public function findOneAuthorizedById(ContactIdInterface $contactId, ContactIdInterface $authorizedContactId): ?ContactInterface
  57.     {
  58.         $qb = $this->createQueryBuilder('c');
  60.         return $qb
  61.             ->leftJoin(ContactPermissionInterface::class, 'cp', Join::WITH, $qb->expr()->eq('cp.contact', 'c.id'))
  62.             ->andWhere('c.id = :authorizedId')
  63.             ->andWhere($qb->expr()->eq('cp.authorizedContact', ':id'))
  64.             ->addGroupBy('c.id')
  65.             ->setParameter('id', $contactId, ContactIdType::NAME)
  66.             ->setParameter('authorizedId', $authorizedContactId, ContactIdType::NAME)
  67.             ->getQuery()
  68.             ->getOneOrNullResult();
  69.     }
  71.     /**
  72.      * Returns all contacts for what is contact with given ID authorized.
  73.      *
  74.      * @param ?int $limit
  75.      * @param ?int $offset
  76.      *
  77.      * @return ContactInterface[]|null
  78.      */
  79.     public function findAllAuthorized(ContactIdInterface $id, array $criteria = null, array $orderBy = null, ?int $limit = null, ?int $offset = null): ?array
  80.     {
  81.         return $this
  82.             ->createQueryBuilderAllAuthorized(false, $id, $criteria, $orderBy, $limit, $offset)
  83.             ->getQuery()
  84.             ->getResult()
  85.         ;
  86.     }
  88.     /**
  89.      * Counts all contacts for what is contact with given ID authorized.
  90.      */
  91.     public function countAllAuthorized(ContactIdInterface $id, array $criteria = null): int
  92.     {
  93.         return (int) ($this
  94.             ->createQueryBuilderAllAuthorized(true, $id, $criteria)
  95.             ->getQuery()
  96.             ->getSingleScalarResult())
  97.         ;
  98.     }
  100.     /**
  101.      * Returns Contact of person type with given unique information.
  102.      */
  103.     public function findOnePersonByUniqueInformation(string $email): ?ContactInterface
  104.     {
  105.         $qb = $this->createQueryBuilder('c');
  107.         return $qb
  108.             ->where('c.type = :type')
  109.             ->andWhere('c.email = :email')
  110.             ->setParameter('type', ContactInterface::TYPE_PERSON)
  111.             ->setParameter('email', $email)
  112.             ->getQuery()
  113.             ->getOneOrNullResult();
  114.     }
  116.     /**
  117.      * Returns Contact of company type with given unique information.
  118.      */
  119.     public function findOneCompanyByUniqueInformation(string $registrationNumber, string $country): ?ContactInterface
  120.     {
  121.         $qb = $this->createQueryBuilder('c');
  123.         return $qb
  124.             ->where('c.type = :type')
  125.             ->andWhere('c.registrationNumber = :registrationNumber')
  126.             ->andWhere('c.country = :country')
  127.             ->setParameter('type', ContactInterface::TYPE_COMPANY)
  128.             ->setParameter('registrationNumber', $registrationNumber)
  129.             ->setParameter('country', $country)
  130.             ->getQuery()
  131.             ->getOneOrNullResult();
  132.     }
  134.     public function createAddContactAwareQueryBuilder(ContactIdInterface $authorizedContactId): QueryBuilder
  135.     {
  136.         $qb = $this->createQueryBuilder('c');
  138.         return $qb
  139.             ->innerJoin(ContactPermissionInterface::class, 'cp', Join::WITH, $qb->expr()->eq('c.id', 'cp.contact'))
  140.             ->where($qb->expr()->eq('cp.authorizedContact', ':id'))
  141.             ->setParameter('id', $authorizedContactId, ContactIdType::NAME);
  142.     }
  144.     public function createAddContactAccessAccessorQueryBuilder(ContactIdInterface $authorizedContactId)
  145.     {
  146.         $qb = $this->createQueryBuilder('c');
  148.         return $qb
  149.             ->innerJoin(ContactPermissionInterface::class, 'cp', Join::WITH, $qb->expr()->eq('c.id', 'cp.contact'))
  150.             ->where($qb->expr()->eq('cp.authorizedContact', ':id'))
  151.             ->andWhere($qb->expr()->eq('c.type', ':personType'))
  152.             ->setParameter('id', $authorizedContactId, ContactIdType::NAME)
  153.             ->setParameter('personType', ContactInterface::TYPE_PERSON);
  154.     }
  156.     /**
  157.      * Create query builder for all contacts for what is contact with given ID authorized.
  158.      *
  159.      * @param ?int $limit
  160.      * @param ?int $offset
  161.      */
  162.     private function createQueryBuilderAllAuthorized(bool $isCount, ContactIdInterface $id, array $criteria = null, array $orderBy = null, ?int $limit = null, ?int $offset = null): QueryBuilder
  163.     {
  164.         $addFullNameQuery = false;
  165.         $addContactEmailQuery = false;
  166.         $addGroupQuery = false;
  167.         $addManagingDirectorQuery = false;
  168.         $addAccountantQuery = false;
  169.         $addAuthorizedAccountantQuery = false;
  170.         $addEmploymentRelationshipQuery = false;
  171.         $addRelationshipCustomerSupplierQuery = false;
  172.         $addNotificationInvoicePaymentQuery = false;
  173.         $addAddressQuery = false;
  174.         $addNoteTitleQuery = false;
  176.         $qb = $this->createQueryBuilder('c');
  177.         $groupsWhereExpr = $qb->expr()->orX();
  178.         $managingDirectorsWhereExpr = $qb->expr()->orX();
  179.         $accountantsWhereExpr = $qb->expr()->orX();
  180.         $authorizedAccountantsWhereExpr = $qb->expr()->orX();
  182.         if (true === $isCount) {
  183.             $qb->select($qb->expr()->countDistinct('c.id'));
  184.         } else {
  185.             $qb->groupBy('c.id');
  187.             if (null !== $limit) {
  188.                 $qb->setMaxResults($limit);
  189.             }
  191.             if (null !== $offset) {
  192.                 $qb->setFirstResult($offset);
  193.             }
  195.             $addContactEmailQuery = true;
  196.         }
  198.         if (false === empty($criteria)) {
  199.             foreach ($criteria as $criterion => $values) {
  200.                 if (true === empty($values)) {
  201.                     continue;
  202.                 }
  204.                 if ('search' === $criterion) {
  205.                     $searchTokens = array_filter(explode(' ', $values), function (mixed $value) {
  206.                         return ' ' !== $value;
  207.                     });
  209.                     $expression = $qb->expr()->orX(
  210.                         $qb->expr()->like('c.name', ':search'),
  211.                         $qb->expr()->like('c.oldName', ':search'),
  212.                         $qb->expr()->like('c.phone', ':search'),
  213.                         $qb->expr()->like('c.secondaryPhone', ':search'),
  214.                         $qb->expr()->like('ce.email', ':search'),
  215.                         $qb->expr()->like('ce.secondaryEmail', ':search'),
  216.                         $qb->expr()->like('c.registrationNumber', ':search'),
  217.                         $qb->expr()->like('c.idNumber', ':search'),
  218.                         $qb->expr()->like('c.norwegianIdNumber', ':search'),
  219.                         $qb->expr()->like('c.norwegianTemporaryIdNumber', ':search'),
  220.                     );
  222.                     $nameExpression = $qb->expr()->andX();
  224.                     foreach ($searchTokens as $key => $searchToken) {
  225.                         $paramKey = 'search_' . $key;
  227.                         $nameExpression->add(
  228.                             $qb->expr()->orX(
  229.                                 $qb->expr()->like('c.firstName', ':' . $paramKey),
  230.                                 $qb->expr()->like('c.lastName', ':' . $paramKey)
  231.                             )
  232.                         );
  234.                         $qb->setParameter($paramKey, '%' . $searchToken . '%');
  235.                     }
  237.                     $expression->add($nameExpression);
  239.                     if (true === $isCount) {
  240.                         $qb->andWhere($expression);
  241.                     } else {
  242.                         $qb->having($expression);
  243.                     }
  245.                     $qb->setParameter('search', '%' . $values . '%');
  246.                     $addFullNameQuery = true;
  247.                     $addContactEmailQuery = true;
  248.                 }
  250.                 if ('type' === $criterion) {
  251.                     if (true === \is_array($values)) {
  252.                         $qb->andWhere($qb->expr()->in('c.type', ':type'));
  253.                     } else {
  254.                         $qb->andWhere($qb->expr()->eq('c.type', ':type'));
  255.                     }
  257.                     $qb->setParameter('type', $values);
  258.                 }
  260.                 if ('relationshipCustomerSupplier' === $criterion) {
  261.                     $addRelationshipCustomerSupplierQuery = true;
  262.                 }
  264.                 if ('employmentRelationship' === $criterion) {
  265.                     $addEmploymentRelationshipQuery = true;
  266.                 }
  268.                 if ('notificationInvoicePayment' === $criterion) {
  269.                     $addNotificationInvoicePaymentQuery = true;
  270.                 }
  272.                 if ('forSale' === $criterion) {
  273.                     $qb->andWhere($qb->expr()->eq('c.forSale', ':forSale'));
  274.                     $qb->setParameter('forSale', true);
  275.                 }
  277.                 if ('country' === $criterion) {
  278.                     $countryWhereExpr = $qb->expr()->orX();
  280.                     foreach ($values as $key => $value) {
  281.                         $paramKey = 'country_' . $key;
  283.                         $countryWhereExpr->add($qb->expr()->eq('ca.country', ':' . $paramKey));
  284.                         $qb->setParameter($paramKey, $value);
  285.                     }
  287.                     $qb->andWhere($countryWhereExpr);
  288.                     $addAddressQuery = true;
  289.                 }
  291.                 if ('gender' === $criterion) {
  292.                     $genderWhereExpr = $qb->expr()->orX();
  294.                     foreach ($values as $key => $value) {
  295.                         $paramKey = 'gender_' . $key;
  297.                         $genderWhereExpr->add($qb->expr()->eq('c.gender', ':' . $paramKey));
  298.                         $qb->setParameter($paramKey, $value, GenderType::NAME);
  299.                     }
  301.                     $qb->andWhere($genderWhereExpr);
  302.                 }
  304.                 if ('group' === $criterion) {
  305.                     foreach ($values as $key => $value) {
  306.                         $paramKey = 'groupId_' . $key;
  308.                         $groupsWhereExpr->add($qb->expr()->eq('cgi.group', ':' . $paramKey));
  309.                         $qb->setParameter($paramKey, $value, GroupIdType::NAME);
  310.                     }
  312.                     $qb->andWhere($groupsWhereExpr);
  313.                     $addGroupQuery = true;
  314.                 }
  316.                 if ('managingDirector' === $criterion) {
  317.                     foreach ($values as $key => $value) {
  318.                         $paramKey = 'managingDirectorId_' . $key;
  320.                         $managingDirectorsWhereExpr->add($qb->expr()->eq('cr_md.managingDirector', ':' . $paramKey));
  321.                         $qb->setParameter($paramKey, $value, ContactIdType::NAME);
  322.                     }
  324.                     $qb->andWhere($managingDirectorsWhereExpr);
  325.                     $addManagingDirectorQuery = true;
  326.                 }
  328.                 if ('accountant' === $criterion) {
  329.                     foreach ($values as $key => $value) {
  330.                         $paramKey = 'accountantId_' . $key;
  332.                         $accountantsWhereExpr->add($qb->expr()->eq('cr_a.accountant', ':' . $paramKey));
  333.                         $qb->setParameter($paramKey, $value, ContactIdType::NAME);
  334.                     }
  336.                     $qb->andWhere($accountantsWhereExpr);
  337.                     $addAccountantQuery = true;
  338.                 }
  340.                 if ('authorizedAccountant' === $criterion) {
  341.                     foreach ($values as $key => $value) {
  342.                         $paramKey = 'authorizedAccountantId_' . $key;
  344.                         $authorizedAccountantsWhereExpr->add($qb->expr()->eq('cr_aa.authorizedAccountant', ':' . $paramKey));
  345.                         $qb->setParameter($paramKey, $value, ContactIdType::NAME);
  346.                     }
  348.                     $qb->andWhere($authorizedAccountantsWhereExpr);
  349.                     $addAuthorizedAccountantQuery = true;
  350.                 }
  352.                 if ('language' === $criterion) {
  353.                     $languageWhereExpr = $qb->expr()->orX();
  355.                     foreach ($values as $key => $value) {
  356.                         $paramKey = 'language_' . $key;
  358.                         $languageWhereExpr->add($qb->expr()->eq('c.language', ':' . $paramKey));
  359.                         $qb->setParameter($paramKey, $value);
  360.                     }
  362.                     $qb->andWhere($languageWhereExpr);
  363.                 }
  365.                 if ('workOccupation' === $criterion) {
  366.                     $workOccupationWhereExpr = $qb->expr()->orX();
  368.                     foreach ($values as $key => $value) {
  369.                         $paramKey = 'workOccupationId_' . $key;
  371.                         $workOccupationWhereExpr->add($qb->expr()->eq('c.workOccupation', ':' . $paramKey));
  372.                         $qb->setParameter($paramKey, $value, WorkOccupationType::NAME);
  373.                     }
  375.                     $qb->andWhere($workOccupationWhereExpr);
  376.                 }
  378.                 if ('industrySector' === $criterion) {
  379.                     $industrySectorWhereExpr = $qb->expr()->orX();
  381.                     foreach ($values as $key => $value) {
  382.                         $paramKey = 'industrySector_' . $key;
  384.                         $industrySectorWhereExpr->add($qb->expr()->eq('c.industrySector', ':' . $paramKey));
  385.                         $qb->setParameter($paramKey, $value, IndustrySectorType::NAME);
  386.                     }
  388.                     $qb->andWhere($industrySectorWhereExpr);
  389.                 }
  391.                 if ('maritalStatus' === $criterion) {
  392.                     $maritalStatusWhereExpr = $qb->expr()->orX();
  394.                     foreach ($values as $key => $value) {
  395.                         $paramKey = 'maritalStatus_' . $key;
  397.                         $maritalStatusWhereExpr->add($qb->expr()->eq('c.maritalStatus', ':' . $paramKey));
  398.                         $qb->setParameter($paramKey, $value, MaritalStatusType::NAME);
  399.                     }
  401.                     $qb->andWhere($maritalStatusWhereExpr);
  402.                 }
  404.                 if ('nationality' === $criterion) {
  405.                     $nationalityWhereExpr = $qb->expr()->orX();
  407.                     foreach ($values as $key => $value) {
  408.                         $paramKey = 'nationality_' . $key;
  410.                         $nationalityWhereExpr->add($qb->expr()->eq('c.nationality', ':' . $paramKey));
  411.                         $qb->setParameter($paramKey, $value);
  412.                     }
  414.                     $qb->andWhere($nationalityWhereExpr);
  415.                 }
  417.                 if ('vatActivity' === $criterion) {
  418.                     if (true === $values) {
  419.                         $qb->andWhere($qb->expr()->isNotNull('c.vatin'));
  420.                     }
  421.                 }
  423.                 if ('vatActivityNone' === $criterion) {
  424.                     if (true === $values) {
  425.                         $qb->andWhere($qb->expr()->isNull('c.vatin'));
  426.                     }
  427.                 }
  429.                 if ('noteTitle' === $criterion) {
  430.                     $noteTitleWhereExpr = $qb->expr()->orX();
  432.                     foreach ($values as $key => $value) {
  433.                         $paramKey = 'note_title_' . $key;
  435.                         $noteTitleWhereExpr->add($qb->expr()->eq('n.title', ':' . $paramKey));
  436.                         $qb->setParameter($paramKey, $value);
  437.                     }
  439.                     $qb->andWhere($noteTitleWhereExpr);
  440.                     $addNoteTitleQuery = true;
  441.                 }
  442.             }
  443.         }
  445.         if (false === $isCount) {
  446.             if (false === empty($orderBy)) {
  447.                 foreach ($orderBy as $column => $direction) {
  448.                     if ('fullName' === $column) {
  449.                         $qb->addOrderBy('fullName', $direction);
  450.                         $addFullNameQuery = true;
  451.                     }
  453.                     if ('phone' === $column) {
  454.                         $qb->addOrderBy('c.phone', $direction);
  455.                     }
  457.                     if ('email' === $column) {
  458.                         $qb->addOrderBy('ce.email', $direction);
  459.                         $addContactEmailQuery = true;
  460.                     }
  461.                 }
  462.             }
  463.         }
  465.         if (true === $addFullNameQuery && false === $isCount) {
  466.             $qb
  467.                 ->addSelect(
  468.                     "CASE WHEN c.type = 1 THEN c.name WHEN c.type = 2 THEN CONCAT(c.lastName, ' ', c.firstName) ELSE '' END AS HIDDEN fullName"
  469.                 )
  470.             ;
  471.         }
  473.         if (true === $addContactEmailQuery) {
  474.             if (false === $isCount) {
  475.                 $qb->addSelect('ce');
  476.             }
  478.             $qb
  479.                 ->innerJoin('c.contactEmail', 'ce', Join::WITH, $qb->expr()->eq('c.contactEmail', 'ce.id'))
  480.             ;
  481.         }
  483.         $qb
  484.             ->innerJoin(ContactPermissionInterface::class, 'cp', Join::WITH, $qb->expr()->andX(
  485.                 $qb->expr()->eq('cp.contact', 'c.id'),
  486.                 $qb->expr()->eq('cp.authorizedContact', ':id')
  487.             ))
  488.         ;
  490.         if (true === $addAddressQuery) {
  491.             $qb
  492.                 ->innerJoin(ContactAddressInterface::class, 'ca', Join::WITH, $qb->expr()->andX(
  493.                     $qb->expr()->eq('ca.id', 'c.contactAddress')
  494.                 ))
  495.             ;
  496.         }
  498.         if (true === $addGroupQuery) {
  499.             $qb
  500.                 ->innerJoin(GroupItemInterface::class, 'cgi', Join::WITH, $qb->expr()->andX(
  501.                     $qb->expr()->eq('cgi.contact', 'c.id')
  502.                 ))
  503.             ;
  504.         }
  506.         if (true === $addManagingDirectorQuery) {
  507.             $qb
  508.                 ->innerJoin(ManagingDirectorInterface::class, 'cr_md', Join::WITH, $qb->expr()->andX(
  509.                     $qb->expr()->eq('cr_md.contact', 'c.id')
  510.                 ))
  511.             ;
  512.         }
  514.         if (true === $addAccountantQuery) {
  515.             $qb
  516.                 ->innerJoin(AccountantInterface::class, 'cr_a', Join::WITH, $qb->expr()->andX(
  517.                     $qb->expr()->eq('cr_a.contact', 'c.id')
  518.                 ))
  519.             ;
  520.         }
  522.         if (true === $addAuthorizedAccountantQuery) {
  523.             $qb
  524.                 ->innerJoin(AuthorizedAccountantInterface::class, 'cr_aa', Join::WITH, $qb->expr()->andX(
  525.                     $qb->expr()->eq('cr_aa.contact', 'c.id')
  526.                 ))
  527.             ;
  528.         }
  530.         if (true === $addEmploymentRelationshipQuery) {
  531.             $qb
  532.                 ->innerJoin(EmploymentInterface::class, 'cer', Join::WITH, $qb->expr()->andX(
  533.                     $qb->expr()->eq('cer.employer', 'cp.authorizedContact'),
  534.                     $qb->expr()->eq('cer.employee', 'cp.contact')
  535.                 ))
  536.             ;
  537.         }
  539.         if (true === $addRelationshipCustomerSupplierQuery) {
  540.             $qb
  541.                 ->innerJoin(ContactRelationshipCustomerSupplierInterface::class, 'ccr', Join::WITH, $qb->expr()->andX(
  542.                     $qb->expr()->eq('ccr.supplier', 'cp.authorizedContact'),
  543.                     $qb->expr()->eq('ccr.customer', 'cp.contact')
  544.                 ))
  545.             ;
  546.         }
  548.         if (true === $addNotificationInvoicePaymentQuery) {
  549.             $qb
  550.                 ->innerJoin(InvoicePaymentNotificationInterface::class, 'ipn', Join::WITH, $qb->expr()->andX(
  551.                     $qb->expr()->eq('ipn.supplier', ':id'),
  552.                     $qb->expr()->eq('ipn.customer', 'c.id')
  553.                 ))
  554.             ;
  555.         }
  557.         if (true === $addNoteTitleQuery) {
  558.             $qb
  559.                 ->innerJoin(NoteInterface::class, 'n', Join::WITH, $qb->expr()->andX(
  560.                     $qb->expr()->eq('n.contact', 'c.id')
  561.                 ))
  562.                 ->innerJoin(NotePermissionInterface::class, 'np', Join::WITH, $qb->expr()->andX(
  563.                     $qb->expr()->eq('n.id', 'np.note'),
  564.                     $qb->expr()->eq('np.contact', ':id')
  565.                 ))
  566.             ;
  567.         }
  569.         $qb->setParameter('id', $id, ContactIdType::NAME);
  571.         return $qb;
  572.     }
  573. }