PrestataireRepository.php 10.8 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 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47
    /**
     * @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()
        ;
    }

48 49 50
    /**
     * @return Prestataire[] Returns an array of Prestataire objects
     */
51
    public function findDefault($type = 'all', $orderBy = 'raison', $direction = 'ASC', $limit = null)
52 53 54
    {
        $qb = $this->createQueryBuilder('p');
        $qb = $this->addDefaultFilter($qb);
Julien Jorry committed
55
        if ('groupelocal' == $orderBy && !empty($direction) && ('ASC' === $direction || 'DESC' === $direction)) {
56 57 58
            $qb->leftJoin('p.groupe', 'g')
                ->orderBy('g.name', $direction)
                ->addOrderBy('p.raison', 'ASC');
Julien Jorry committed
59
        } elseif (!empty($orderBy) && !empty($direction) && ('ASC' === $direction || 'DESC' === $direction)) {
60 61
            $qb->orderBy('p.' . $orderBy, $direction);
        }
62
        if (null != $limit) {
63 64
            $qb->limit($limit);
        }
65 66 67 68 69 70 71 72 73 74 75 76 77
        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)
            ;
        }
78

79
        $return = $qb
80 81 82
            ->getQuery()
            ->getResult()
        ;
83 84 85 86 87
        if (!$this->getEntityManager()->getFilters()->isEnabled('enabled_filter')) {
            $this->getEntityManager()->getFilters()->enable('enabled_filter');
        }

        return $return;
88 89
    }

Julien Jorry committed
90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109
    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;
    }

Julien Jorry committed
110 111
    public function getPrestataireMLC()
    {
112 113
        if ($this->getEntityManager()->getFilters()->isEnabled('enabled_filter')) {
            $this->getEntityManager()->getFilters()->disable('enabled_filter');
Julien Jorry committed
114 115
        }
        $qb = $this->createQueryBuilder('p');
116 117

        $return = $qb
Julien Jorry committed
118 119 120 121 122
            ->andWhere('p.mlc = :mlc')
            ->setParameter('mlc', true)
            ->getQuery()
            ->getOneOrNullResult()
        ;
123 124 125
        if (!$this->getEntityManager()->getFilters()->isEnabled('enabled_filter')) {
            $this->getEntityManager()->getFilters()->enable('enabled_filter');
        }
126 127

        return $return;
Julien Jorry committed
128 129
    }

Julien Jorry committed
130
    private function addDefaultFilter(QueryBuilder $qb)
Julien Jorry committed
131
    {
132 133 134 135 136
        $expr = $this->getEntityManager()->getExpressionBuilder();
        if ($this->getEntityManager()->getFilters()->isEnabled('enabled_filter')) {
            $this->getEntityManager()->getFilters()->disable('enabled_filter');
        }

Julien Jorry committed
137 138
        $qb
            ->andWhere('p.mlc = :mlc')
Julien Jorry committed
139
            ->andWhere('p.solidoume = :solidoume')
Julien Jorry committed
140
            ->andWhere('p.enabled = :enabled')
141 142 143 144 145 146
            ->andWhere(
                $expr->notIn(
                    'p.id',
                    $this->getEntityManager()->createQueryBuilder()
                       ->select('p2.id')
                       ->from(EtatPrestataire::class, 'e')
147
                       ->join('e.prestataires', 'p2')
148 149 150 151 152
                       ->where('e.enabled = :disabled')
                       ->setParameter('disabled', false)
                       ->getDQL()
                )
            )
Julien Jorry committed
153
            ->setParameter('mlc', false)
Julien Jorry committed
154
            ->setParameter('solidoume', false)
Julien Jorry committed
155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175
            ->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);

176
        $return = $qb
Julien Jorry committed
177 178 179
            ->leftJoin('p.geolocs', 'gs')
            ->leftJoin('gs.geoloc', 'g')
            ->andWhere('p.mlc = :mlc')
Julien Jorry committed
180
            ->andWhere('p.solidoume = :solidoume')
Julien Jorry committed
181
            ->setParameter('mlc', false)
Julien Jorry committed
182
            ->setParameter('solidoume', false)
Julien Jorry committed
183 184 185 186 187 188
            ->andWhere('' . $sqlDistance . ' < :distance')
            ->setParameter('distance', $distance)
            ->orderBy('p.raison', 'ASC')
            ->getQuery()
            ->getResult()
        ;
189 190 191 192 193 194

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

        return $return;
Julien Jorry committed
195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211
    }

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

212
        $return = $qb
213
            ->orderBy('p.raison', 'ASC')
Julien Jorry committed
214 215 216
            ->getQuery()
            ->getResult()
        ;
217 218 219 220 221 222

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

        return $return;
Julien Jorry committed
223 224 225 226 227 228 229 230 231 232
    }

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

233
        $return = $qb
Julien Jorry committed
234 235 236 237 238 239
            ->andWhere($qb->expr()->isMemberOf(':rubrique', 'p.rubriques'))
            ->setParameter('rubrique', $rubrique)
            ->orderBy('p.raison', 'ASC')
            ->getQuery()
            ->getResult()
        ;
240 241 242 243 244 245

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

        return $return;
Julien Jorry committed
246 247 248
    }

    /**
249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270
     * 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()
        ;
    }

    /**
271
     * For Prestataire Admin search.
272
     *
Julien Jorry committed
273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295
     * @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);

296
        $return = $qb
Julien Jorry committed
297 298 299 300 301 302
            ->andWhere($qb->expr()->isMemberOf(':groupe', 'p.groupeprestataires'))
            ->setParameter('groupe', $groupe)
            ->orderBy('p.raison', 'ASC')
            ->getQuery()
            ->getResult()
        ;
303 304 305 306 307 308

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

        return $return;
Julien Jorry committed
309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325
    }

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

326
        $return = $qb
Julien Jorry committed
327 328 329 330 331 332
            ->andWhere('p.id != :presta')
            ->setParameter('presta', $presta->getId())
            ->orderBy('p.raison', 'ASC')
            ->getQuery()
            ->getResult()
        ;
333 334 335 336 337 338

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

        return $return;
Julien Jorry committed
339 340
    }
}