Symfony Profiler

<?php
namespace App\Controller;
use App\Entity\GasMeter;
use App\Entity\ElectricMeter;
use App\Entity\Entreprise;
use App\Entity\Rappel;
use App\Entity\Contrat;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\HttpFoundation\JsonResponse;
use Symfony\Component\Routing\Annotation\Route;
use Doctrine\Persistence\ManagerRegistry;
use Doctrine\ORM\EntityManagerInterface;
class HomeController extends AbstractController
{
/**
* @Route("/", name="app_home")
*/
public function index(): Response
{
return $this->redirectToRoute('app_site');
}
/**
* @Route("/site", name="app_site")
*/
public function site(ManagerRegistry $doctrine, Request $request): Response
{
// Vérifier si l'utilisateur est connecté et a le rôle ROLE_TEAM
if (!$this->getUser() || !$this->isGranted('ROLE_TEAM')) {
return $this->redirectToRoute('app_login');
}
// Récupérer le nombre de jours depuis la requête, par défaut 7 jours
$nbJours = $request->query->get('nbJours', 7);
// Calculer la date limite
$dateLimite = new \DateTime();
$dateLimite->modify('+' . $nbJours . ' days');
// Récupérer les rappels non complétés à venir dans les X prochains jours
$rappels = $doctrine->getRepository(Rappel::class)
->createQueryBuilder('r')
->where('r.completer = :completer')
->andWhere('r.echeance >= :now')
->andWhere('r.echeance <= :dateLimite')
->setParameter('completer', false)
->setParameter('now', new \DateTime())
->setParameter('dateLimite', $dateLimite)
->orderBy('r.echeance', 'ASC')
->getQuery()
->getResult();
// Récupérer les entreprises liées aux rappels
$entreprises = [];
foreach ($rappels as $rappel) {
if ($rappel->getEntrepriseId()) {
$entreprise = $doctrine->getRepository(Entreprise::class)->find($rappel->getEntrepriseId());
if ($entreprise) {
$entreprises[$rappel->getId()] = $entreprise->getRaisonSociale();
}
}
}
return $this->render('home/index.html.twig', [
'controller_name' => 'HomeController',
'rappels' => $rappels,
'entreprises' => $entreprises,
'nbJours' => $nbJours
]);
}
/**
* @Route("/rappel/complete/{id}", name="app_rappel_complete", methods={"POST"})
*/
public function completeRappel(Rappel $rappel, EntityManagerInterface $entityManager): JsonResponse
{
// Vérifier si l'utilisateur est connecté et a le rôle ROLE_TEAM
if (!$this->getUser() || !$this->isGranted('ROLE_TEAM')) {
return new JsonResponse(['error' => 'Accès non autorisé'], 403);
}
$rappel->setCompleter(true);
$entityManager->flush();
return new JsonResponse(['success' => true]);
}
/**
* @Route("/requetes", name="app_requetes")
*/
public function requetes(ManagerRegistry $doctrine): Response
{
// Vérifier si l'utilisateur est connecté et a le rôle ROLE_TEAM
if (!$this->getUser() || !$this->isGranted('ROLE_TEAM')) {
return $this->redirectToRoute('app_login');
}
$electric_meters = $doctrine->getRepository(ElectricMeter::class)->findAll();
$gas_meters = $doctrine->getRepository(GasMeter::class)->findAll();
$entreprises = $doctrine->getRepository(Entreprise::class)->findAll();
// prepare entities to display in datatables
$electric_meters_array = [];
foreach ($electric_meters as $electric_meter) {
$entreprise = $doctrine->getRepository(Entreprise::class)->find($electric_meter->getEntrepriseId());
$electric_meters_array[] = [
$entreprise ? $entreprise->getRaisonSociale() : '',
$electric_meter->getAdresseCompteur(),
$electric_meter->getPDL(),
$electric_meter->getDateDebut()? $electric_meter->getDateDebut()->format('d/m/Y') : '',
$electric_meter->getDateFin()? $electric_meter->getDateFin()->format('d/m/Y') : '',
$electric_meter->getProfil(),
$electric_meter->getCAR(),
$electric_meter->getFournisseur(),
$electric_meter->getPrix(),
'',
];
}
$gas_meters_array = [];
foreach ($gas_meters as $gas_meter) {
if ($gas_meter->getDateFin() !== null) { // Only include meters with a set end date
$entreprise = $doctrine->getRepository(Entreprise::class)->find($gas_meter->getEntrepriseId());
$gas_meters_array[] = [
$entreprise ? $entreprise->getRaisonSociale() : '',
$gas_meter->getAdresseCompteur(),
$gas_meter->getPDL(),
$gas_meter->getDateDebut()? $gas_meter->getDateDebut()->format('d/m/Y') : '',
$gas_meter->getDateFin()? $gas_meter->getDateFin()->format('d/m/Y') : '',
$gas_meter->getProfil(),
$gas_meter->getCAR(),
$gas_meter->getFournisseur(),
$gas_meter->getPrix(),
'',
];
}
}
$entreprises_array = [];
foreach ($entreprises as $entreprise) {
$entreprises_array[] = [
$entreprise->getRaisonSociale(),
$entreprise->getAdresse(),
$entreprise->getSiret(),
$entreprise->getNaf(),
$entreprise->getCodeInsee(),
$entreprise->getStatut(),
'',
];
}
return $this->render('requetes.html.twig', [
'electric_meters' => $electric_meters_array,
'gas_meters' => $gas_meters_array,
'entreprises' => $entreprises_array,
]);
}
/**
* @Route("/requetes2", name="app_requetes2")
*/
public function requetes2(ManagerRegistry $doctrine,Request $request): JsonResponse
{
// Vérifier si l'utilisateur est connecté et a le rôle ROLE_TEAM
if (!$this->getUser() || !$this->isGranted('ROLE_TEAM')) {
return new JsonResponse(['error' => 'Accès non autorisé'], 403);
}
$filters = $request->request->all();
// prepare filters for each entities
$electric_meter_filters = [];
$gas_meter_filters = [];
$entreprise_filters = [];
foreach ($filters as $key => $value) {
if (strpos($key, 'Electric')) {
$electric_meter_filters[str_replace(['Electric','Filter'], '', $key)] = $value;
} elseif (strpos($key, 'Gaz')) {
$gas_meter_filters[str_replace(['Gaz','Filter'], '', $key)] = $value;
} elseif (strpos($key, 'Entreprise')) {
$entreprise_filters[str_replace(['Entreprise','Filter'], '', $key)] = $value;
}
}
// Fetch the repository for each entity type
$electricMeterRepository = $doctrine->getRepository(ElectricMeter::class);
$gasMeterRepository = $doctrine->getRepository(GasMeter::class);
$entrepriseRepository = $doctrine->getRepository(Entreprise::class);
$contratRepository = $doctrine->getRepository(Contrat::class);
//convert filters date to datetime
if(isset($electric_meter_filters['date_debut']) && $electric_meter_filters['date_debut'] !== '') {
$electric_meter_filters['date_debut'] = new \DateTime($electric_meter_filters['date_debut']);
}
if(isset($electric_meter_filters['date_fin']) && $electric_meter_filters['date_fin'] !== '') {
$electric_meter_filters['date_fin'] = new \DateTime($electric_meter_filters['date_fin']);
}
if(isset($gas_meter_filters['date_debut']) && $gas_meter_filters['date_debut'] !== '') {
$gas_meter_filters['date_debut'] = new \DateTime($gas_meter_filters['date_debut']);
}
if(isset($gas_meter_filters['date_fin']) && $gas_meter_filters['date_fin'] !== '') {
$gas_meter_filters['date_fin'] = new \DateTime($gas_meter_filters['date_fin']);
}
// Build the query for electric meters with contracts
$electric_meters = $electricMeterRepository->createQueryBuilder('e')
->leftJoin(Contrat::class, 'c', 'WITH', 'e.PDL = c.pdl')
->select('e', 'CASE WHEN c.date_fin > e.date_fin OR e.date_fin IS NULL THEN c.date_fin ELSE e.date_fin END as HIDDEN latest_end_date');
if(isset($electric_meter_filters['adresse_compteur']) && $electric_meter_filters['adresse_compteur'] !== '') {
$electric_meters->andWhere('e.adresse_compteur LIKE :adresse_compteur')
->setParameter('adresse_compteur', '%'.$electric_meter_filters['adresse_compteur'].'%');
}
if(isset($electric_meter_filters['PDL']) && $electric_meter_filters['PDL'] !== '') {
$electric_meters->andWhere('e.PDL LIKE :PDL')
->setParameter('PDL', '%'.$electric_meter_filters['PDL'].'%');
}
if(isset($electric_meter_filters['date_debut']) && $electric_meter_filters['date_debut'] !== '') {
$electric_meters->andWhere('(CASE WHEN c.date_fin > e.date_fin OR e.date_fin IS NULL THEN c.date_fin ELSE e.date_fin END) >= :date_debut')
->setParameter('date_debut', $electric_meter_filters['date_debut']);
}
if(isset($electric_meter_filters['date_fin']) && $electric_meter_filters['date_fin'] !== '') {
$electric_meters->andWhere('(CASE WHEN c.date_fin > e.date_fin OR e.date_fin IS NULL THEN c.date_fin ELSE e.date_fin END) <= :date_fin')
->setParameter('date_fin', $electric_meter_filters['date_fin']);
}
$electric_meters = $electric_meters->getQuery()->getResult();
// Build the query for gas meters with contracts
$gas_meters = $gasMeterRepository->createQueryBuilder('g')
->leftJoin(Contrat::class, 'c', 'WITH', 'g.PDL = c.pdl')
->select('g', 'CASE WHEN c.date_fin > g.date_fin OR g.date_fin IS NULL THEN c.date_fin ELSE g.date_fin END as HIDDEN latest_end_date')
->andWhere('g.date_fin IS NOT NULL'); // Only include meters with a set end date
if(isset($gas_meter_filters['adresse_compteur']) && $gas_meter_filters['adresse_compteur'] !== '') {
$gas_meters->andWhere('g.adresse_compteur LIKE :adresse_compteur')
->setParameter('adresse_compteur', '%'.$gas_meter_filters['adresse_compteur'].'%');
}
if(isset($gas_meter_filters['PDL']) && $gas_meter_filters['PDL'] !== '') {
$gas_meters->andWhere('g.PDL LIKE :PDL')
->setParameter('PDL', '%'.$gas_meter_filters['PDL'].'%');
}
if(isset($gas_meter_filters['date_debut']) && $gas_meter_filters['date_debut'] !== '') {
$gas_meters->andWhere('(CASE WHEN c.date_fin > g.date_fin OR g.date_fin IS NULL THEN c.date_fin ELSE g.date_fin END) >= :date_debut')
->setParameter('date_debut', $gas_meter_filters['date_debut']);
}
if(isset($gas_meter_filters['date_fin']) && $gas_meter_filters['date_fin'] !== '') {
$gas_meters->andWhere('(CASE WHEN c.date_fin > g.date_fin OR g.date_fin IS NULL THEN c.date_fin ELSE g.date_fin END) <= :date_fin')
->setParameter('date_fin', $gas_meter_filters['date_fin']);
}
$gas_meters = $gas_meters->getQuery()->getResult();
// Build the query for entreprises
$entreprises = $entrepriseRepository->createQueryBuilder('e');
if(isset($entreprise_filters['RaisonSociale']) && $entreprise_filters['RaisonSociale'] !== '') {
$entreprises->andWhere('e.RaisonSociale LIKE :RaisonSociale')
->setParameter('RaisonSociale', '%'.$entreprise_filters['RaisonSociale'].'%');
}
if(isset($entreprise_filters['Adresse']) && $entreprise_filters['Adresse'] !== '') {
$entreprises->andWhere('e.Adresse LIKE :Adresse')
->setParameter('Adresse', '%'.$entreprise_filters['Adresse'].'%');
}
if(isset($entreprise_filters['Siret']) && $entreprise_filters['Siret'] !== '') {
$entreprises->andWhere('e.Siret LIKE :Siret')
->setParameter('Siret', '%'.$entreprise_filters['Siret'].'%');
}
if(isset($entreprise_filters['Naf']) && $entreprise_filters['Naf'] !== '') {
$entreprises->andWhere('e.Naf LIKE :Naf')
->setParameter('Naf', '%'.$entreprise_filters['Naf'].'%');
}
if(isset($entreprise_filters['Code_Insee']) && $entreprise_filters['Code_Insee'] !== '') {
$entreprises->andWhere('e.Code_Insee LIKE :Code_Insee')
->setParameter('Code_Insee', '%'.$entreprise_filters['Code_Insee'].'%');
}
if(isset($entreprise_filters['Statut']) && $entreprise_filters['Statut'] !== '') {
$entreprises->andWhere('e.Statut LIKE :Statut')
->setParameter('Statut', '%'.$entreprise_filters['Statut'].'%');
}
$entreprises = $entreprises->getQuery()->getResult();
// Prepare arrays for response
$electric_meters_array = [];
foreach ($electric_meters as $electric_meter) {
$entreprise = $doctrine->getRepository(Entreprise::class)->find($electric_meter->getEntrepriseId());
$electric_meters_array[] = [
$entreprise ? $entreprise->getRaisonSociale() : '',
$electric_meter->getAdresseCompteur(),
$electric_meter->getPDL(),
$electric_meter->getDateDebut()? $electric_meter->getDateDebut()->format('d/m/Y') : '',
$electric_meter->getDateFin()? $electric_meter->getDateFin()->format('d/m/Y') : '',
$electric_meter->getProfil(),
$electric_meter->getCAR(),
$electric_meter->getFournisseur(),
$electric_meter->getPrix(),
'',
];
}
$gas_meters_array = [];
foreach ($gas_meters as $gas_meter) {
$entreprise = $doctrine->getRepository(Entreprise::class)->find($gas_meter->getEntrepriseId());
$gas_meters_array[] = [
$entreprise ? $entreprise->getRaisonSociale() : '',
$gas_meter->getAdresseCompteur(),
$gas_meter->getPDL(),
$gas_meter->getDateDebut()? $gas_meter->getDateDebut()->format('d/m/Y') : '',
$gas_meter->getDateFin()? $gas_meter->getDateFin()->format('d/m/Y') : '',
$gas_meter->getProfil(),
$gas_meter->getCAR(),
$gas_meter->getFournisseur(),
$gas_meter->getPrix(),
'',
];
}
$entreprises_array = [];
foreach ($entreprises as $entreprise) {
$entreprises_array[] = [
$entreprise->getRaisonSociale(),
$entreprise->getAdresse(),
$entreprise->getSiret(),
$entreprise->getNaf(),
$entreprise->getCodeInsee(),
$entreprise->getStatut(),
'',
];
}
return new JsonResponse([
'electric_meters' => $electric_meters_array,
'gas_meters' => $gas_meters_array,
'entreprises' => $entreprises_array,
'filters' => $filters,
'electric_meter_filters' => $electric_meter_filters,
'gas_meter_filters' => $gas_meter_filters,
'entreprise_filters' => $entreprise_filters,
]);
}
}

src/Controller/HomeController.php line 23