<?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 App\Common\LieuInterface;
use App\Common\QuoiInterface;
use App\Entity\Actualite;
use App\Entity\Etablissement;
use App\Repository\Helper\LieuFilterTrait;
use DateTime;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\ORM\QueryBuilder;
use Doctrine\Persistence\ManagerRegistry;
/**
* @method null|Actualite find($id, $lockMode = null, $lockVersion = null)
* @method null|Actualite findOneBy(array $criteria, array $orderBy = null)
* @method Actualite[] findAll()
* @method Actualite[] findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
*/
final class ActualiteRepository extends ServiceEntityRepository
{
use LieuFilterTrait;
public function __construct(ManagerRegistry $registry)
{
parent::__construct($registry, Actualite::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}.datePublication", 'DESC');
}
public function suprimmeDistantesParPeriode(DateTime $min, DateTime $max, array $idsSauf): void
{
$builder = $this->createQueryBuilder('a');
$builder
->delete(Actualite::class, 'a')
->andWhere($builder->expr()->isNotNull('a.idDistant'))
->andWhere('a.datePublication BETWEEN :min AND :max')
->setParameter('min', $min->format('Y-m-d H:i:s'))
->setParameter('max', $max->format('Y-m-d H:i:s'));
if ($idsSauf) {
$builder->andWhere($builder->expr()->notIn('a.idDistant', $idsSauf));
}
$builder->getQuery()->execute();
}
public function findDistante(string $idDistant, Etablissement $etablissement): ?Actualite
{
return $this
->findOneBy([
'idDistant' => $idDistant,
'etablissement' => $etablissement,
]);
}
public function findByQuoiLieu(QuoiInterface $quoi, ?LieuInterface $lieu, int $limit): array
{
$idsCategorie = $quoi->getIdsCategorie();
if (!$idsCategorie) {
return [];
}
$builder = $this->createQueryBuilderClientsOnly('actualite');
$builder
->innerJoin('etablissement.ville', 'ville')
->innerJoin('ville.departement', 'departement')
->innerJoin('departement.ancienneRegion', 'ancienneRegion')
->innerJoin('ancienneRegion.region', 'region')
->andWhere($builder->expr()->in('etablissement.categoriePrimaire', $idsCategorie))
->setMaxResults($limit)
->orderBy('actualite.datePublication', 'DESC');
if ($lieu) {
$this->addWhereLieuToQuery($builder, $lieu);
}
return $builder
->getQuery()
->getResult();
}
public function findByLieu(LieuInterface $lieu, int $limit = 2, array $filter = null, $types = null): array
{
$builder = $this->createQueryBuilder('actualite');
$builder
->innerJoin('actualite.etablissement', 'etablissement')
->addSelect('etablissement') // gagner une req SQL pour récupérer l'id de l'étab de l'actu (depuis le TWIG)
->innerJoin('etablissement.texteReferencement', 'texteReferencement') // gagner une req SQL car oneToOne
->addSelect('texteReferencement')
->innerJoin('etablissement.ville', 'ville')
->innerJoin('ville.departement', 'departement')
->innerJoin('departement.ancienneRegion', 'ancienneRegion')
->innerJoin('ancienneRegion.region', 'region')
->orderBy('actualite.datePublication', 'DESC');
if ($limit > 0)
$builder->setMaxResults($limit);
if ($lieu->getSlug() !="default") {
$this->addWhereLieuToQuery($builder, $lieu);
}
if ($filter) {
$builder->andWhere($builder->expr()->in('actualite.etablissement', $filter));
}
if ($types) {
$builder->andWhere($builder->expr()->in('etablissement.type', $types));
}
return $builder
->getQuery()
->getResult();
}
public function countDepuis(DateTime $depuis, array $idsSauf = []): int
{
$builder = $this->createQueryBuilderClientsOnly('a');
$builder
->select($builder->expr()->count('a'))
->where('a.datePublication >= :depuis')
->setParameter('depuis', $depuis);
if ($idsSauf) {
$builder->andWhere($builder->expr()->notIn('a.id', $idsSauf));
}
return (int) $builder
->getQuery()
->getSingleScalarResult();
}
/**
* @return Actualite[] Returns an array of Actualite objects
*/
public function getLast($limit = 3)
{
$builder = $this->createQueryBuilder('a');
$builder
->innerJoin('a.etablissement', 'etablissement')
->where($builder->expr()->eq('etablissement.videoPlus',true))
->leftJoin('a.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('a.datePublication', 'DESC')
;
return $builder
->getQuery()
->getResult();
/*
return $this->createQueryBuilder('a')
// ->andWhere('a.datePublication >= CURRENT_TIMESTAMP()')
->andWhere('a.etablissement IS NOT NULL')
->orderBy('a.datePublication', 'DESC')
->setMaxResults($limit)
->getQuery()
->getResult()
;
*/
}
}