Symfony Profiler

<?php
namespace App\Repository;
use App\Entity\Contact;
use App\Entity\Cotisation;
use App\Entity\CotisationStatut;
use App\Entity\Departement;
use App\Entity\EntityStatus;
use App\Entity\Entreprise;
use App\Entity\Etablissement;
use App\Entity\Event;
use App\Entity\FirmType;
use App\Entity\Region;
use App\Entity\Syndicat;
use App\Entity\User;
use App\Model\Search\AdvancedSearch;
use DateTime;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\ORM\Query;
use Doctrine\ORM\Query\Expr\Join;
use Doctrine\Persistence\ManagerRegistry;
/**
* @extends ServiceEntityRepository<Event>
*
* @method Entreprise|null find($id, $lockMode = null, $lockVersion = null)
* @method Entreprise|null findOneBy(array $criteria, array $orderBy = null)
* @method Entreprise[] findAll()
* @method Entreprise[] findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
*/
class EnterpriseRepository extends BaseRepository
{
public function __construct(
ManagerRegistry $registry,
private RegionRepository $regionRepository
)
{
parent::__construct($registry, Entreprise::class);
}
public function save(Entreprise $entity, bool $flush = false): void
{
$this->getEntityManager()->persist($entity);
if ($flush) {
$this->getEntityManager()->flush();
}
}
public function remove(Entreprise $entity, bool $flush = false): void
{
$this->getEntityManager()->remove($entity);
if ($flush) {
$this->getEntityManager()->flush();
}
}
public function countMemberPerRegion(int $year, int $regionId)
{
/*$cotisationLimit = new DateTime($year.'-07-01');
$now = new DateTime();
if ($now <= $cotisationLimit) {
$year = $year - 1;
}*/
return $this
->createQueryBuilder('e')
->select('COUNT(e.id)')
->innerJoin(Cotisation::class, 'c', Join::WITH, 'c.entreprise = e.id')
->innerJoin(CotisationStatut::class, 'cs', Join::WITH, 'cs.id = c.cotisationStatut')
->innerJoin(Departement::class, 'd', Join::WITH, 'd.id = e.departement')
->innerJoin(Region::class, 'r', Join::WITH, 'r.id = d.region AND r.id = :regionId')
->setParameter('regionId', $regionId)
->andWhere('c.year = :year')
->setParameter('year', $year)
->andWhere('cs.id = 1')
->addGroupBy('r.id')
->getQuery()
->getOneOrNullResult()
;
}
public function countPerRegion()
{
$regions = $this->regionRepository->findAll();
$entreprisesPerRegion = [];
$totalRegions = \count($regions) + 1;
for ($regionId = 1; $regionId < $totalRegions; $regionId++) {
$entreprisesPerRegion[$regions[$regionId - 1]->getId()] = $this
->createQueryBuilder('e')
->leftJoin('e.departement', 'd')
->leftJoin('d.regions', 'r')
->innerJoin(Cotisation::class, 'c', Join::WITH, 'c.entreprise = e.id')
->innerJoin(CotisationStatut::class, 'cs', Join::WITH, 'cs.id = c.cotisationStatut')
->select('count(e.id) as value, r.nom')
->andWhere('r.id = :regionId')
->setParameter('regionId', $regionId)
->andWhere('cs.id = 1')
->getQuery()
->getScalarResult();
}
return $entreprisesPerRegion;
}
public function countFirmByType()
{
return $this
->createQueryBuilder('e')
->innerJoin(FirmType::class, 'ft', Join::WITH, 'ft.id = e.firmType')
->select('COUNT(e.id) AS countFirm, ft.name')
->andWhere('e.status != :status')
->setParameter('status', EntityStatus::STATUS_ARCHIVED)
->groupBy('e.firmType')
->getQuery()
->getScalarResult()
;
}
public function retrieveAllData(
?AdvancedSearch $advancedSearch,
?bool $forEmailSending = false,
?bool $forSmsSending = false,
?bool $forDisplayArchived = false,
array $params = [],
array $selection = [],
?User $user = null
): Query
{
$searchCriteriasCollection = is_null($advancedSearch) ? [] : $advancedSearch->getSearchCriteria();
$joinContact = false;
$joinEtablissement = false;
$joinSyndicat = false;
$joinCotisation = false;
$query = $this->createQueryBuilder('e');
$whereParts = [];
foreach ($searchCriteriasCollection as $key => $criteria) {
$entity = $criteria->getEntity();
$field = $criteria->getField();
$term = $criteria->getTerm();
$currentAlias = null;
switch ($entity) {
case 'entreprise':
$currentAlias = 'e';
break;
case 'contact':
if (false === $joinContact) {
$query->innerJoin(Contact::class, 'c', Join::WITH, 'c.firm = e.id');
$joinContact = true;
}
$currentAlias = 'c';
break;
case 'etablissements':
if (false === $joinEtablissement) {
$query->innerJoin(Etablissement::class, 'et', Join::WITH, 'et.entreprise = e.id');
$joinEtablissement = true;
}
$currentAlias = 'et';
break;
case 'syndicat':
if (false === $joinSyndicat) {
$query->innerJoin(Syndicat::class, 's', Join::WITH, 's.id = e.syndicat');
$joinSyndicat = true;
}
$currentAlias = 's';
break;
case 'cotisation':
if (false === $joinCotisation) {
$query->innerJoin(Cotisation::class, 'co', Join::WITH, 'co.entreprise = e.id');
$joinCotisation = true;
}
$currentAlias = 'co';
break;
}
$whereParts = $this->buildWhereParts(
$params,
$entity,
$field,
$term,
$key,
$currentAlias,
array_key_exists($currentAlias.'.'.$field, $whereParts),
$whereParts
);
}
$query = $this->buildWhereQuery($query, $whereParts);
if (false === $forDisplayArchived) {
$query
->andWhere('e.status != :status')
->setParameter('status', EntityStatus::STATUS_ARCHIVED)
;
}
if (in_array('ROLE_REGIONAL_AGENT', $user->getRoles())) {
$query
->innerJoin(Departement::class, 'd', Join::WITH, 'd.id = e.departement')
->innerJoin(Region::class, 'r', Join::WITH, 'r.id = d.region')
->andWhere('r.id = :user_region')
->setParameter('user_region', $user->getRegion())
;
}
if (in_array('ROLE_UNION_PRESIDENT', $user->getRoles())) {
$query
->andWhere('e.syndicat = :user_syndicat')
->setParameter('user_syndicat', $user->getSyndicat())
;
}
if (in_array('ROLE_BOARD_DIRECTOR', $user->getRoles())) {
$query
->andWhere('e.commission = :user_commission')
->setParameter('user_commission', $user->getCommission())
;
}
return $query->getQuery();
}
public function findMemberOnPhoneBookQuery(?string $search = null): Query
{
$query = $this
->createQueryBuilder('e')
->leftJoin(Contact::class, 'c', Join::WITH, 'c.firm = e.id AND c.isResponsible = 1')
->andWhere('e.status = :status')
->setParameter('status', Entreprise::STATUS_ENABLED)
->andWhere('e.figurerAnnuaireAdherents = 1')
;
if (!is_null($search)) {
$query->andWhere(
$query->expr()->orX(
$query->expr()->like('e.raisonSocial', ':search_1'),
$query->expr()->like('e.tel1', ':search_2'),
$query->expr()->like('e.email', ':search_3'),
$query->expr()->like('e.adresse', ':search_4'),
$query->expr()->like('e.adresse2', ':search_5'),
$query->expr()->like('e.codePostal', ':search_6'),
$query->expr()->like('e.ville', ':search_7'),
$query->expr()->like('c.nom', ':search_8'),
$query->expr()->like('c.prenom', ':search_9'),
$query->expr()->like('c.telPortable', ':search_10'),
$query->expr()->like('c.emailContact', ':search_11')
)
)
->setParameter('search_1', '%'.$search.'%')
->setParameter('search_2', '%'.$search.'%')
->setParameter('search_3', '%'.$search.'%')
->setParameter('search_4', '%'.$search.'%')
->setParameter('search_5', '%'.$search.'%')
->setParameter('search_6', '%'.$search.'%')
->setParameter('search_7', '%'.$search.'%')
->setParameter('search_8', '%'.$search.'%')
->setParameter('search_9', '%'.$search.'%')
->setParameter('search_10', '%'.$search.'%')
->setParameter('search_11', '%'.$search.'%')
;
}
return $query->getQuery();
}
public function getEnterprisesByCodeAdherents($codeAdherentsArray)
{
return $this->createQueryBuilder('a')
->andWhere('a.ancienIdentifiant IN (:entreprises)')
->setParameter('entreprises', $codeAdherentsArray)
->getQuery()->getResult();
}
public function findMemberPerRegion(Region $region, int|string $year) {
return $this
->createQueryBuilder('e')
->innerJoin(Cotisation::class, 'c', Join::WITH, 'c.entreprise = e.id')
->innerJoin(Departement::class, 'd', Join::WITH, 'd.id = e.departement')
->innerJoin(Region::class, 'r', Join::WITH, 'r.id = d.region')
->andWhere('e.isMember = 1')
->andWhere('r.id = :region')
->setParameter('region', $region)
->andWhere('c.year = :year')
->setParameter('year', $year)
->getQuery()
->getResult()
;
}
}

src/Repository/EnterpriseRepository.php line 34