Symfony Profiler

<?php
namespace App\Repository;
use App\Entity\Centre;
use App\Entity\Client;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\Persistence\ManagerRegistry;
/**
* @method Centre|null find($id, $lockMode = null, $lockVersion = null)
* @method Centre|null findOneBy(array $criteria, array $orderBy = null)
* @method Centre[] findAll()
* @method Centre[] findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
*/
class CentreRepository extends ServiceEntityRepository
{
public function __construct(ManagerRegistry $registry)
{
parent::__construct($registry, Centre::class);
}
/**
* @return Centre[] Returns an array of Centre search
*/
/* public function findByCoordonates(float $longitude, float $latitude)
{
// gets all the centres in a diameter of 15 km
$distance = 0.1;
return $this->createQueryBuilder('c')
->where('c.longitude BETWEEN :min_longitude AND :max_longitude')
->andWhere('c.latitude BETWEEN :min_latitude AND :max_latitude')
->setParameter('min_longitude', $longitude - $distance)
->setParameter('max_longitude', $longitude + $distance)
->setParameter('min_latitude', $latitude - $distance)
->setParameter('max_latitude', $latitude + $distance)
->getQuery()
->getResult();
}*/
public function findByCoordonates(float $longitude, float $latitude, $page = 1, $limit = 10)
{
$distance = 0.4;
$qb = $this->createQueryBuilder('c');
$qb->select('c')
->addSelect('(
(c.longitude - :longitude) * (c.longitude - :longitude) +
(c.latitude - :latitude) * (c.latitude - :latitude)
) AS HIDDEN distance')
->setParameter('longitude', $longitude)
->setParameter('latitude', $latitude)
->andWhere('c.longitude BETWEEN :min_longitude AND :max_longitude')
->andWhere('c.latitude BETWEEN :min_latitude AND :max_latitude')
->setParameter('min_longitude', $longitude - $distance)
->setParameter('max_longitude', $longitude + $distance)
->setParameter('min_latitude', $latitude - $distance)
->setParameter('max_latitude', $latitude + $distance)
->orderBy('distance', 'ASC')
->setFirstResult(($page - 1) * $limit)
->setMaxResults($limit);
return $qb->getQuery()->getResult();
}
public function findByDistance(float $longitude, float $latitude, float $radiusKm = 2, int $limit = 4)
{
$distanceLat = $radiusKm / 111;
$distanceLng = $radiusKm / (111 * cos(deg2rad($latitude)));
$qb = $this->createQueryBuilder('c');
$qb->select('c')
->andWhere('c.longitude BETWEEN :min_longitude AND :max_longitude')
->andWhere('c.latitude BETWEEN :min_latitude AND :max_latitude')
->andWhere('c.isValid = :is_valid')
->setParameter('min_longitude', $longitude - $distanceLng)
->setParameter('max_longitude', $longitude + $distanceLng)
->setParameter('min_latitude', $latitude - $distanceLat)
->setParameter('max_latitude', $latitude + $distanceLat)
->setParameter('is_valid', true)
->setMaxResults($limit);
return $qb->getQuery()->getResult();
}
public function findByDistanceKm(float $longitude, float $latitude, int $limit = 4)
{
$qb = $this->createQueryBuilder('c');
$qb->addSelect(
'(6371 * acos(cos(radians(:latitude)) * cos(radians(c.latitude))
* cos(radians(c.longitude) - radians(:longitude)) + sin(radians(:latitude))
* sin(radians(c.latitude)))) AS distance'
)
->having('distance <= c.zoneKm')
->andWhere('c.isValid = :is_valid')
->setParameter('latitude', $latitude)
->setParameter('longitude', $longitude)
->setParameter('is_valid', true)
->orderBy('distance', 'ASC')
->setMaxResults($limit);
return $qb->getQuery()->getResult();
}
/**
* @return Centre[] Returns an array of Centre search
*/
public function findByAdress(string $address)
{
// gets all the centres in a diameter of 15 km
return $this->createQueryBuilder('c')
->Where('c.address LIKE CONCAT(\'%\',:query,\'%\')')
->setParameter('query', $address)
->getQuery()
->getResult();
}
/**
* @return Centre[] Returns an array of Centre search
*/
public function findByQuery(String $query)
{
return $this->createQueryBuilder('c')
->where('c.name LIKE CONCAT(\'%\',:query,\'%\')')
->orWhere(':query LIKE CONCAT(\'%\',c.name,\'%\')')
->orWhere('c.postale LIKE CONCAT(\'%\',:query,\'%\')')
->orWhere(':query LIKE CONCAT(\'%\',c.postale,\'%\')')
->orWhere('c.city LIKE CONCAT(\'%\',:query,\'%\')')
->orWhere(':query LIKE CONCAT(\'%\',c.city,\'%\')')
->orWhere('c.address LIKE CONCAT(\'%\',:query,\'%\')')
->orWhere(':query LIKE CONCAT(\'%\',c.address,\'%\')')
->setParameter('query', $query)
->getQuery()
->getResult();
}
/**
* @return int[] Returns an array of Centre search
*/
public function findByName(string $name)
{
$conn = $this->getEntityManager()->getConnection();
$sql = '
SELECT audio_centre.id_centre_id
FROM audio_centre
INNER JOIN audio ON audio.id = audio_centre.id_audio_id
WHERE (CONCAT(audio.name," ",audio.lastname) LIKE :name) OR (CONCAT(audio.lastname," ",audio.name) LIKE :name)
UNION
SELECT centre.id
FROM centre
WHERE centre.name LIKE :name;
';
$stmt = $conn->prepare($sql);
$result = $stmt->executeQuery(['name' => '%' . $name . '%']);
// returns the number of audio that have the specified
return $result->fetchFirstColumn();
// gets all the centres in a diameter of 15 km
}
/**
* @return int[] Returns an array of Centre search
*/
public function findByRdvTaken(Client $client)
{
$conn = $this->getEntityManager()->getConnection();
$sql = '
SELECT centre.id
FROM centre
INNER JOIN rdv ON centre.id = rdv.id_centre_id
WHERE rdv.id_client_id = :client_id
';
$stmt = $conn->prepare($sql);
$result = $stmt->executeQuery(['client_id' => $client->getId()]);
// returns the number of audio that have the specified
return $result->fetchFirstColumn();
// gets all the centres in a diameter of 15 km
}
/**
* @return int Returns an array of Centre search
*/
public function centreHasMotif(int $centreId, int $motifId): int
{
$conn = $this->getEntityManager()->getConnection();
$sql = '
SELECT COUNT(1)
FROM `audio_motif`
INNER JOIN audio ON audio.id = audio_motif.id_audio_id
INNER JOIN audio_centre ON audio.id = audio_centre.id_audio_id
WHERE `id_motif_id` = :motifId AND `id_centre_id`=:centreId;
';
$stmt = $conn->prepare($sql);
$result = $stmt->executeQuery(['centreId' => $centreId, 'motifId' => $motifId]);
// returns the number of audio that have the specified
return (int) $result->fetchFirstColumn()[0];
}
public function findVisibleCenters(): array
{
return $this->createQueryBuilder('c')
->andWhere('c.isVisible IS NULL')
->getQuery()
->getResult();
}
// /**
// * @return Centre[] Returns an array of Centre objects
// */
/*
public function findByExampleField($value)
{
return $this->createQueryBuilder('c')
->andWhere('c.exampleField = :val')
->setParameter('val', $value)
->orderBy('c.id', 'ASC')
->setMaxResults(10)
->getQuery()
->getResult()
;
}
*/
/*
public function findOneBySomeField($value): ?Centre
{
return $this->createQueryBuilder('c')
->andWhere('c.exampleField = :val')
->setParameter('val', $value)
->getQuery()
->getOneOrNullResult()
;
}
*/
public function countByPeriod(?\DateTime $from, ?\DateTime $to, string $dateField): int
{
$qb = $this->createQueryBuilder('e');
if ($from) {
$qb->andWhere("e.{$dateField} >= :from")->setParameter('from', $from);
}
if ($to) {
$qb->andWhere("e.{$dateField} <= :to")->setParameter('to', $to);
}
return (int) $qb->select('COUNT(e.id)')
->getQuery()
->getSingleScalarResult();
}
public function findByFilters(
?string $postalCode,
?string $subscriptionType,
?string $centerStatus,
?string $signupDateFrom,
?string $signupDateTo,
?array $contractTypes, // new
?string $city // ← add this
): array {
$qb = $this->createQueryBuilder('c');
if ($postalCode) {
$qb->andWhere('c.postale = :postalCode')
->setParameter('postalCode', $postalCode);
}
if ($centerStatus) {
switch ($centerStatus) {
case 'active':
$qb->andWhere('c.isValid = true')
->andWhere('c.isResilied = false')
->andWhere('c.isBlocked = false');
break;
case 'cancelled':
$qb->andWhere('c.isResilied = true');
break;
case 'blocked':
$qb->andWhere('c.isBlocked = true');
break;
}
}
if ($signupDateFrom) {
$from = new \DateTime($signupDateFrom);
$from->setTime(0, 0, 0);
$qb->andWhere('c.signupDate >= :from')
->setParameter('from', $from);
}
if ($signupDateTo) {
$to = new \DateTime($signupDateTo);
$to->setTime(23, 59, 59);
$qb->andWhere('c.signupDate <= :to')
->setParameter('to', $to);
}
if ($subscriptionType) {
$qb->join('c.subscription', 's') // Assuming 'subscription' is the relation name in Centre entity
->andWhere('s.planInterval = :planInterval')
->setParameter('planInterval', $subscriptionType);
}
if ($city) {
$cities = array_map('trim', explode(',', $city));
$qb->andWhere('c.city IN (:cities)')
->setParameter('cities', $cities);
}
if ($contractTypes && is_array($contractTypes)) {
$qb->join('c.specificSubscription', 'ss') // adjust if different relation
->andWhere('ss.contractCategory IN (:contractTypes)')
->setParameter('contractTypes', $contractTypes);
}
return $qb->getQuery()->getResult();
}
/**
* Recherche universelle dans tous les champs pertinents d'un centre avec pagination
*/
public function searchByQuery(string $query, int $page = 1, int $limit = 20): array
{
$qb = $this->createQueryBuilder('c');
// Nettoyer la query
$cleanQuery = trim($query);
// Si la query n'est pas vide, ajouter les conditions de recherche
if (!empty($cleanQuery)) {
$searchParam = '%' . $cleanQuery . '%';
// Rechercher dans tous les champs pertinents
$qb->where('c.name LIKE :query')
->orWhere('c.city LIKE :query')
->orWhere('c.postale LIKE :query')
->orWhere('c.address LIKE :query')
->orWhere('c.rue LIKE :query')
->orWhere('c.phone LIKE :query')
->orWhere('c.siret LIKE :query')
->orWhere('c.finess LIKE :query')
->setParameter('query', $searchParam);
// Si c'est un code postal exact (5 chiffres)
if (preg_match('/^\d{5}$/', $cleanQuery)) {
$qb->orWhere('c.postale = :exactPostal')
->setParameter('exactPostal', $cleanQuery);
}
}
// Si la query est vide, on ne met aucune condition WHERE pour récupérer tous les centres
// Pagination
$qb->setFirstResult(($page - 1) * $limit)
->setMaxResults($limit);
return $qb->getQuery()->getResult();
}
/**
* Compter les résultats de la recherche universelle
*/
public function countByQuery(string $query): int
{
$qb = $this->createQueryBuilder('c');
// Nettoyer la query
$cleanQuery = trim($query);
$qb->select('COUNT(c.id)');
// Si la query n'est pas vide, ajouter les conditions de recherche
if (!empty($cleanQuery)) {
$searchParam = '%' . $cleanQuery . '%';
// Rechercher dans tous les champs pertinents
$qb->where('c.name LIKE :query')
->orWhere('c.city LIKE :query')
->orWhere('c.postale LIKE :query')
->orWhere('c.address LIKE :query')
->orWhere('c.rue LIKE :query')
->orWhere('c.phone LIKE :query')
->orWhere('c.siret LIKE :query')
->orWhere('c.finess LIKE :query')
->setParameter('query', $searchParam);
// Si c'est un code postal exact (5 chiffres)
if (preg_match('/^\d{5}$/', $cleanQuery)) {
$qb->orWhere('c.postale = :exactPostal')
->setParameter('exactPostal', $cleanQuery);
}
}
// Si la query est vide, on ne met aucune condition WHERE pour compter tous les centres
return (int) $qb->getQuery()->getSingleScalarResult();
}
/**
* Recherche géographique avec query optionnelle
*/
public function searchByGeographyAndQuery(float $latitude, float $longitude, float $radiusKm = 10, ?string $query = null, int $page = 1, int $limit = 20): array
{
$qb = $this->createQueryBuilder('c');
// Calcul approximatif des limites géographiques
// 1 degré de latitude ≈ 111 km
// 1 degré de longitude varie selon la latitude
$latDelta = $radiusKm / 111;
$lngDelta = $radiusKm / (111 * cos(deg2rad($latitude)));
// Recherche par zone géographique approximative
$qb->select('c')
->andWhere('c.longitude BETWEEN :min_longitude AND :max_longitude')
->andWhere('c.latitude BETWEEN :min_latitude AND :max_latitude')
->setParameter('min_longitude', $longitude - $lngDelta)
->setParameter('max_longitude', $longitude + $lngDelta)
->setParameter('min_latitude', $latitude - $latDelta)
->setParameter('max_latitude', $latitude + $latDelta);
// Ajouter la recherche textuelle si fournie et non vide
if ($query && trim($query) !== '') {
$cleanQuery = trim($query);
$searchParam = '%' . $cleanQuery . '%';
$qb->andWhere(
$qb->expr()->orX(
'c.name LIKE :query',
'c.city LIKE :query',
'c.postale LIKE :query',
'c.address LIKE :query',
'c.rue LIKE :query'
)
)->setParameter('query', $searchParam);
}
// Pagination
$qb->setFirstResult(($page - 1) * $limit)
->setMaxResults($limit);
return $qb->getQuery()->getResult();
}
/**
* Compter les résultats de la recherche géographique avec query optionnelle
*/
public function countByGeographyAndQuery(float $latitude, float $longitude, float $radiusKm = 10, ?string $query = null): int
{
$qb = $this->createQueryBuilder('c');
// Calcul approximatif des limites géographiques
$latDelta = $radiusKm / 111;
$lngDelta = $radiusKm / (111 * cos(deg2rad($latitude)));
// Recherche par zone géographique approximative
$qb->select('COUNT(c.id)')
->andWhere('c.longitude BETWEEN :min_longitude AND :max_longitude')
->andWhere('c.latitude BETWEEN :min_latitude AND :max_latitude')
->setParameter('min_longitude', $longitude - $lngDelta)
->setParameter('max_longitude', $longitude + $lngDelta)
->setParameter('min_latitude', $latitude - $latDelta)
->setParameter('max_latitude', $latitude + $latDelta);
// Ajouter la recherche textuelle si fournie et non vide
if ($query && trim($query) !== '') {
$cleanQuery = trim($query);
$searchParam = '%' . $cleanQuery . '%';
$qb->andWhere(
$qb->expr()->orX(
'c.name LIKE :query',
'c.city LIKE :query',
'c.postale LIKE :query',
'c.address LIKE :query',
'c.rue LIKE :query'
)
)->setParameter('query', $searchParam);
}
return (int) $qb->getQuery()->getSingleScalarResult();
}
}

src/Repository/CentreRepository.php line 66