<?php namespace App\Repository; use App\Entity\EtatPrestataire; use App\Entity\Groupe; use App\Entity\Groupeprestataire; use App\Entity\Prestataire; use App\Entity\Rubrique; use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository; use Doctrine\ORM\QueryBuilder; use Doctrine\Persistence\ManagerRegistry; /** * @method Prestataire|null find($id, $lockMode = null, $lockVersion = null) * @method Prestataire|null findOneBy(array $criteria, array $orderBy = null) * @method Prestataire[] findAll() * @method Prestataire[] findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null) */ class PrestataireRepository extends ServiceEntityRepository { public function __construct(ManagerRegistry $registry) { parent::__construct($registry, Prestataire::class); } /** * @return Adherent[] Returns an array of Adherent objects */ public function findByData(array $data) { $qb = $this->createQueryBuilder('p'); if (isset($data['user'])) { $qb = $qb ->where(':user MEMBER OF p.users') ->setParameter('user', $data['user']) ; } return $qb ->getQuery() ->getResult() ; } /** * @return Prestataire[] Returns an array of Prestataire objects */ public function findDefault($type = 'all', $orderBy = 'raison', $direction = 'ASC', $limit = null) { $qb = $this->createQueryBuilder('p'); $qb = $this->addDefaultFilter($qb); if ('groupelocal' == $orderBy && !empty($direction) && ('ASC' === $direction || 'DESC' === $direction)) { $qb->leftJoin('p.groupe', 'g') ->orderBy('g.name', $direction) ->addOrderBy('p.raison', 'ASC'); } elseif (!empty($orderBy) && !empty($direction) && ('ASC' === $direction || 'DESC' === $direction)) { $qb->orderBy('p.' . $orderBy, $direction); } if (null != $limit) { $qb->limit($limit); } if ('prestataire' == $type) { $qb ->leftJoin('p.typeprestataire', 't') ->andWhere('t.name = :nametype OR t.name IS NULL') ->setParameter('nametype', $type) ; } elseif ('partenaire' == $type) { $qb ->leftJoin('p.typeprestataire', 't') ->andWhere('t.name = :nametype') ->setParameter('nametype', $type) ; } $return = $qb ->getQuery() ->getResult() ; if (!$this->getEntityManager()->getFilters()->isEnabled('enabled_filter')) { $this->getEntityManager()->getFilters()->enable('enabled_filter'); } return $return; } public function getPrestataireSolidoume() { if ($this->getEntityManager()->getFilters()->isEnabled('enabled_filter')) { $this->getEntityManager()->getFilters()->disable('enabled_filter'); } $qb = $this->createQueryBuilder('p'); $return = $qb ->andWhere('p.solidoume = 1') // ->setParameter('solidoume', true) ->getQuery() ->getOneOrNullResult() ; if (!$this->getEntityManager()->getFilters()->isEnabled('enabled_filter')) { $this->getEntityManager()->getFilters()->enable('enabled_filter'); } return $return; } public function getPrestataireMLC() { if ($this->getEntityManager()->getFilters()->isEnabled('enabled_filter')) { $this->getEntityManager()->getFilters()->disable('enabled_filter'); } $qb = $this->createQueryBuilder('p'); $return = $qb ->andWhere('p.mlc = :mlc') ->setParameter('mlc', true) ->getQuery() ->getOneOrNullResult() ; if (!$this->getEntityManager()->getFilters()->isEnabled('enabled_filter')) { $this->getEntityManager()->getFilters()->enable('enabled_filter'); } return $return; } private function addDefaultFilter(QueryBuilder $qb) { $expr = $this->getEntityManager()->getExpressionBuilder(); if ($this->getEntityManager()->getFilters()->isEnabled('enabled_filter')) { $this->getEntityManager()->getFilters()->disable('enabled_filter'); } $qb ->andWhere('p.mlc = :mlc') ->andWhere('p.solidoume = :solidoume') ->andWhere('p.enabled = :enabled') ->andWhere( $expr->notIn( 'p.id', $this->getEntityManager()->createQueryBuilder() ->select('p2.id') ->from(EtatPrestataire::class, 'e') ->join('e.prestataires', 'p2') ->where('e.enabled = :disabled') ->setParameter('disabled', false) ->getDQL() ) ) ->setParameter('mlc', false) ->setParameter('solidoume', false) ->setParameter('enabled', true) ->setParameter('disabled', false) ; return $qb; } /** * Find prestataire from geoloc lat/lon and distance in meters. * * @return Prestataire[] Returns an array of Prestataire objects */ public function findByGeoloc($lat, $lon, $distance) { // @TODO : optimize geoloc (better SQL search, bundles ???) // https://numa-bord.com/miniblog/doctrine-recherche-table-contenant-latitudes-longitudes-celle-situes-a-de-xx-km/ // https://stackoverflow.com/questions/24370975/find-distance-between-two-points-using-latitude-and-longitude-in-mysql $sqlDistance = '(6373000 * acos(cos(radians(' . $lat . ')) * cos(radians(g.lat)) * cos(radians(g.lon) - radians(' . $lon . ')) + sin(radians(' . $lat . ')) * sin(radians(g.lat))))'; $qb = $this->createQueryBuilder('p'); $qb = $this->addDefaultFilter($qb); $return = $qb ->leftJoin('p.geolocs', 'gs') ->leftJoin('gs.geoloc', 'g') ->andWhere('p.mlc = :mlc') ->andWhere('p.solidoume = :solidoume') ->setParameter('mlc', false) ->setParameter('solidoume', false) ->andWhere('' . $sqlDistance . ' < :distance') ->setParameter('distance', $distance) ->orderBy('p.raison', 'ASC') ->getQuery() ->getResult() ; if (!$this->getEntityManager()->getFilters()->isEnabled('enabled_filter')) { $this->getEntityManager()->getFilters()->enable('enabled_filter'); } return $return; } /** * @return Prestataire[] Returns an array of Prestataire objects */ public function findForEPaiement($checkAcceptEPayment = false) { $qb = $this->createQueryBuilder('p'); $qb = $this->addDefaultFilter($qb); if ($checkAcceptEPayment) { $qb = $qb ->andWhere('p.acceptemlc = :acceptemlc') ->setParameter('acceptemlc', true) ; } $return = $qb ->orderBy('p.raison', 'ASC') ->getQuery() ->getResult() ; if (!$this->getEntityManager()->getFilters()->isEnabled('enabled_filter')) { $this->getEntityManager()->getFilters()->enable('enabled_filter'); } return $return; } /** * @return Prestataire[] Returns an array of Prestataire objects */ public function findByRubrique(Rubrique $rubrique) { $qb = $this->createQueryBuilder('p'); $qb = $this->addDefaultFilter($qb); $return = $qb ->andWhere($qb->expr()->isMemberOf(':rubrique', 'p.rubriques')) ->setParameter('rubrique', $rubrique) ->orderBy('p.raison', 'ASC') ->getQuery() ->getResult() ; if (!$this->getEntityManager()->getFilters()->isEnabled('enabled_filter')) { $this->getEntityManager()->getFilters()->enable('enabled_filter'); } return $return; } /** * For Prestataire front search. * * @return Prestataire[] Returns an array of Prestataire objects */ public function findByGroupe(Groupe $groupe) { $qb = $this->createQueryBuilder('p'); $qb = $this->addDefaultFilter($qb); return $qb ->andWhere('p.groupe = :groupe') ->setParameter('groupe', $groupe) ->orderBy('p.raison', 'ASC') ->leftJoin('p.typeprestataire', 't') ->andWhere('t.name = :nametype OR t.name IS NULL') ->setParameter('nametype', 'prestataire') ->getQuery() ->getResult() ; } /** * For Prestataire Admin search. * * @return Prestataire[] Returns an array of Prestataire objects */ public function findByGroupeLocal(Groupe $groupe) { $qb = $this->createQueryBuilder('p'); return $qb ->andWhere('p.groupe = :groupe') ->setParameter('groupe', $groupe) ->orderBy('p.raison', 'ASC') ->getQuery() ->getResult() ; } /** * @return Prestataire[] Returns an array of Prestataire objects */ public function findByGroupeprestataire(Groupeprestataire $groupe) { $qb = $this->createQueryBuilder('p'); $qb = $this->addDefaultFilter($qb); $return = $qb ->andWhere($qb->expr()->isMemberOf(':groupe', 'p.groupeprestataires')) ->setParameter('groupe', $groupe) ->orderBy('p.raison', 'ASC') ->getQuery() ->getResult() ; if (!$this->getEntityManager()->getFilters()->isEnabled('enabled_filter')) { $this->getEntityManager()->getFilters()->enable('enabled_filter'); } return $return; } /** * @return Prestataire[] Returns an array of Prestataire objects */ public function findbyExclude(Prestataire $presta, $checkAcceptEPayment = true) { $qb = $this->createQueryBuilder('p'); $qb = $this->addDefaultFilter($qb); if ($checkAcceptEPayment) { $qb = $qb ->andWhere('p.acceptemlc = :acceptemlc') ->setParameter('acceptemlc', true) ; } $return = $qb ->andWhere('p.id != :presta') ->setParameter('presta', $presta->getId()) ->orderBy('p.raison', 'ASC') ->getQuery() ->getResult() ; if (!$this->getEntityManager()->getFilters()->isEnabled('enabled_filter')) { $this->getEntityManager()->getFilters()->enable('enabled_filter'); } return $return; } }