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();
}
}

src/Repository/CentreRepository.php line 66