<?php
declare(strict_types=1);
/*
* Copyright (C) Office National de Publication et de Communication - All Rights Reserved
*
* Unauthorized copying of this file, via any medium is strictly prohibited by law
* This file is proprietary and confidential
*/
namespace App\Repository;
use DateTime;
use App\Entity\OffreEmploi;
use App\Common\LieuInterface;
use App\Entity\Etablissement;
use Doctrine\ORM\QueryBuilder;
use Doctrine\Persistence\ManagerRegistry;
use App\Repository\Helper\LieuFilterTrait;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
/**
* @method null|OffreEmploi find($id, $lockMode = null, $lockVersion = null)
* @method null|OffreEmploi findOneBy(array $criteria, array $orderBy = null)
* @method OffreEmploi[] findAll()
* @method OffreEmploi[] findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
*/
final class OffreEmploiRepository extends ServiceEntityRepository
{
use LieuFilterTrait;
public function __construct(ManagerRegistry $registry)
{
parent::__construct($registry, OffreEmploi::class);
}
public function createQueryBuilderClientsOnly($alias, $indexBy = null): QueryBuilder
{
$builder = $this->createQueryBuilder($alias, $indexBy);
return $builder
->innerJoin("${alias}.etablissement", 'etablissement')
->andWhere(
$builder->expr()->orX(
$builder->expr()->eq('etablissement.droitPageWeb', true),
$builder->expr()->eq('etablissement.droitForfaitGX', true)
)
)
->orderBy("${alias}.dateCreation", 'DESC');
}
public function countDepuis(DateTime $depuis, array $idsSauf = []): int
{
$builder = $this->createQueryBuilderClientsOnly('o');
$builder
->select($builder->expr()->count('o'))
->where('o.dateCreation >= :depuis')
->setParameter('depuis', $depuis);
if ($idsSauf) {
$builder->andWhere($builder->expr()->notIn('o.id', $idsSauf));
}
return (int) $builder
->getQuery()
->getSingleScalarResult();
}
public function findMisEnEvidence(): ?OffreEmploi
{
return $this->findOneBy([], ['dateCreation' => 'DESC']);
}
public function findDistante(string $idDistant, Etablissement $etablissement): ?OffreEmploi
{
return $this
->findOneBy([
'idDistant' => $idDistant,
'etablissement' => $etablissement,
]);
}
public function getLibelles(): array
{
$query = $this->createQueryBuilder('oe');
$this->filterQueryWithValidOnly('oe', $query);
$result = $query
->select('oe.libelle')
->groupBy('oe.libelle')
->andWhere($query->expr()->isNotNull('oe.libelle'))
->getQuery()
->getScalarResult();
return array_column($result, 'libelle');
}
public function filterQueryWithValidOnly(string $alias, QueryBuilder $queryBuilder): void
{
$queryBuilder
->andWhere(
$queryBuilder->expr()->orX(
$queryBuilder->expr()->andX(
$queryBuilder->expr()->isNull("${alias}.dateValidite"),
$queryBuilder->expr()->gte("${alias}.dateActualisation", ':min_actualisation')
),
$queryBuilder->expr()->gte("${alias}.dateValidite", ':now')
)
)
->setParameter(':now', new DateTime())
->setParameter(':min_actualisation', (new DateTime())->modify('-3 months'));
}
/**
* @return OffreEmploi[] Returns an array of OffreEmploi objects
*/
public function getLast($limit = 3)
{
$builder = $this->createQueryBuilder('oe');
$builder
->innerJoin('oe.etablissement', 'etablissement')
->andWhere('oe.etablissement IS NOT NULL')
->leftJoin('oe.etablissement', 'e')
->addSelect('e')
->leftJoin('e.gestionnaire', 'g')
->addSelect('g')
->leftJoin("g.texteReferencement", 'gtexteReferencement')
->addSelect('gtexteReferencement')
->leftJoin("e.ville", 'ville')
->addSelect('ville')
->leftJoin('ville.coordonnees', 'villeCoordonnees')
->addSelect('villeCoordonnees')
->leftJoin('ville.departement', 'departement')
->addSelect('departement')
->leftJoin('departement.coordonnees', 'departementCoordonnees')
->addSelect('departementCoordonnees')
->leftJoin('departement.ancienneRegion', 'ancienneRegion')
->addSelect('ancienneRegion')
->leftJoin('ancienneRegion.region', 'region')
->addSelect('region')
->leftJoin('region.pays', 'pays')
->addSelect('pays')
->leftJoin("e.categoriePrimaire", 'categoriePrimaire')
->addSelect('categoriePrimaire')
->leftJoin("e.texteReferencement", 'texteReferencement')
->addSelect('texteReferencement')
->setMaxResults($limit)
->orderBy('oe.dateCreation', 'DESC')
;
return $builder
->getQuery()
->getResult();
return $this->createQueryBuilder('oe')
->andWhere('oe.etablissement IS NOT NULL')
->orderBy('oe.dateCreation', 'DESC')
->setMaxResults(3)
->getQuery()
->getResult()
;
}
public function findByLieu(LieuInterface $lieu, int $limit = 2, array $filter = null, $types = null): array
{
$builder = $this->createQueryBuilder('oe');
$builder
->innerJoin('oe.etablissement', 'etablissement')
->innerJoin('etablissement.ville', 'ville')
->innerJoin('ville.departement', 'departement')
->innerJoin('departement.ancienneRegion', 'ancienneRegion')
->innerJoin('ancienneRegion.region', 'region')
->innerJoin('oe.profil', 'offreEmploiProfil')
->addSelect('offreEmploiProfil')
->orderBy('oe.dateActualisation', 'DESC');
if ($limit > 0)
$builder->setMaxResults($limit);
if ($lieu->getSlug() !="default") {
$this->addWhereLieuToQuery($builder, $lieu);
}
if ($filter) {
$builder->andWhere($builder->expr()->in('oe.etablissement', $filter));
}
if ($types) {
$builder->andWhere($builder->expr()->in('etablissement.type', $types));
}
return $builder
->getQuery()
->getResult();
}
}