PrestataireRepository.php 9.6 KB
Newer Older
Julien Jorry committed
1 2 3 4
<?php

namespace App\Repository;

5
use App\Entity\EtatPrestataire;
Julien Jorry committed
6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26
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);
    }

27 28 29
    /**
     * @return Prestataire[] Returns an array of Prestataire objects
     */
30
    public function findDefault($type = 'all', $orderBy = 'raison', $direction = 'ASC', $limit = null)
31 32 33
    {
        $qb = $this->createQueryBuilder('p');
        $qb = $this->addDefaultFilter($qb);
34 35 36 37 38 39 40
        if ($orderBy == 'groupelocal' && !empty($direction) && ('ASC' === $direction || 'DESC' === $direction)) {
            $qb->leftJoin('p.groupe', 'g')
                ->orderBy('g.name', $direction)
                ->addOrderBy('p.raison', 'ASC');
        } else if (!empty($orderBy) && !empty($direction) && ('ASC' === $direction || 'DESC' === $direction)) {
            $qb->orderBy('p.' . $orderBy, $direction);
        }
41
        if (null != $limit) {
42 43
            $qb->limit($limit);
        }
44 45 46 47 48 49 50 51 52 53 54 55 56
        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)
            ;
        }
57

58
        $return = $qb
59 60 61
            ->getQuery()
            ->getResult()
        ;
62 63 64 65 66
        if (!$this->getEntityManager()->getFilters()->isEnabled('enabled_filter')) {
            $this->getEntityManager()->getFilters()->enable('enabled_filter');
        }

        return $return;
67 68
    }

Julien Jorry committed
69 70
    public function getPrestataireMLC()
    {
71 72
        if ($this->getEntityManager()->getFilters()->isEnabled('enabled_filter')) {
            $this->getEntityManager()->getFilters()->disable('enabled_filter');
Julien Jorry committed
73 74
        }
        $qb = $this->createQueryBuilder('p');
75 76

        $return = $qb
Julien Jorry committed
77 78 79 80 81 82
            ->leftJoin('p.geolocs', 'g')
            ->andWhere('p.mlc = :mlc')
            ->setParameter('mlc', true)
            ->getQuery()
            ->getOneOrNullResult()
        ;
83 84 85
        if (!$this->getEntityManager()->getFilters()->isEnabled('enabled_filter')) {
            $this->getEntityManager()->getFilters()->enable('enabled_filter');
        }
86 87

        return $return;
Julien Jorry committed
88 89 90 91
    }

    public function addDefaultFilter(QueryBuilder $qb)
    {
92 93 94 95 96
        $expr = $this->getEntityManager()->getExpressionBuilder();
        if ($this->getEntityManager()->getFilters()->isEnabled('enabled_filter')) {
            $this->getEntityManager()->getFilters()->disable('enabled_filter');
        }

Julien Jorry committed
97
        $qb
98
            // ->leftJoin('p.etats', 'tt')
Julien Jorry committed
99 100
            ->andWhere('p.mlc = :mlc')
            ->andWhere('p.enabled = :enabled')
101 102 103 104 105 106 107 108 109 110 111 112 113
            // ->andWhere('tt.enabled != :disabled OR tt IS NULL')
            ->andWhere(
                $expr->notIn(
                    'p.id',
                    $this->getEntityManager()->createQueryBuilder()
                       ->select('p2.id')
                       ->from(EtatPrestataire::class, 'e')
                       ->leftJoin('e.prestataires', 'p2')
                       ->where('e.enabled = :disabled')
                       ->setParameter('disabled', false)
                       ->getDQL()
                )
            )
Julien Jorry committed
114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135
            ->setParameter('mlc', 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);

136
        $return = $qb
Julien Jorry committed
137 138 139 140 141 142 143 144 145 146
            ->leftJoin('p.geolocs', 'gs')
            ->leftJoin('gs.geoloc', 'g')
            ->andWhere('p.mlc = :mlc')
            ->setParameter('mlc', false)
            ->andWhere('' . $sqlDistance . ' < :distance')
            ->setParameter('distance', $distance)
            ->orderBy('p.raison', 'ASC')
            ->getQuery()
            ->getResult()
        ;
147 148 149 150 151 152

        if (!$this->getEntityManager()->getFilters()->isEnabled('enabled_filter')) {
            $this->getEntityManager()->getFilters()->enable('enabled_filter');
        }

        return $return;
Julien Jorry committed
153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169
    }

    /**
     * @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)
            ;
        }

170
        $return = $qb
171
            ->orderBy('p.raison', 'ASC')
Julien Jorry committed
172 173 174
            ->getQuery()
            ->getResult()
        ;
175 176 177 178 179 180

        if (!$this->getEntityManager()->getFilters()->isEnabled('enabled_filter')) {
            $this->getEntityManager()->getFilters()->enable('enabled_filter');
        }

        return $return;
Julien Jorry committed
181 182 183 184 185 186 187 188 189 190
    }

    /**
     * @return Prestataire[] Returns an array of Prestataire objects
     */
    public function findByRubrique(Rubrique $rubrique)
    {
        $qb = $this->createQueryBuilder('p');
        $qb = $this->addDefaultFilter($qb);

191
        $return = $qb
Julien Jorry committed
192 193 194 195 196 197
            ->andWhere($qb->expr()->isMemberOf(':rubrique', 'p.rubriques'))
            ->setParameter('rubrique', $rubrique)
            ->orderBy('p.raison', 'ASC')
            ->getQuery()
            ->getResult()
        ;
198 199 200 201 202 203

        if (!$this->getEntityManager()->getFilters()->isEnabled('enabled_filter')) {
            $this->getEntityManager()->getFilters()->enable('enabled_filter');
        }

        return $return;
Julien Jorry committed
204 205 206
    }

    /**
207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228
     * 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()
        ;
    }

    /**
229
     * For Prestataire Admin search.
230
     *
Julien Jorry committed
231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253
     * @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);

254
        $return = $qb
Julien Jorry committed
255 256 257 258 259 260
            ->andWhere($qb->expr()->isMemberOf(':groupe', 'p.groupeprestataires'))
            ->setParameter('groupe', $groupe)
            ->orderBy('p.raison', 'ASC')
            ->getQuery()
            ->getResult()
        ;
261 262 263 264 265 266

        if (!$this->getEntityManager()->getFilters()->isEnabled('enabled_filter')) {
            $this->getEntityManager()->getFilters()->enable('enabled_filter');
        }

        return $return;
Julien Jorry committed
267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283
    }

    /**
     * @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)
            ;
        }

284
        $return = $qb
Julien Jorry committed
285 286 287 288 289 290
            ->andWhere('p.id != :presta')
            ->setParameter('presta', $presta->getId())
            ->orderBy('p.raison', 'ASC')
            ->getQuery()
            ->getResult()
        ;
291 292 293 294 295 296

        if (!$this->getEntityManager()->getFilters()->isEnabled('enabled_filter')) {
            $this->getEntityManager()->getFilters()->enable('enabled_filter');
        }

        return $return;
Julien Jorry committed
297 298
    }
}