<?php
namespace App\Controller;
use App\Entity\GasMeter;
use App\Entity\ElectricMeter;
use App\Entity\Entreprise;
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(): Response
{
return $this->render('home/index.html.twig', [
'controller_name' => 'HomeController',
]);
}
/**
* @Route("/requetes", name="app_requetes")
*/
public function requetes(ManagerRegistry $doctrine): Response
{
$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->getFournisseur1(),
$electric_meter->getPrix1(),
$electric_meter->getFournisseur2(),
$electric_meter->getPrix2(),
$electric_meter->getDateTransfert()? $electric_meter->getDateTransfert()->format('d/m/Y') : '',
'',
];
}
$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->getFournisseur1(),
$gas_meter->getPrix1(),
$gas_meter->getFournisseur2(),
$gas_meter->getPrix2(),
$gas_meter->getDateTransfert()? $gas_meter->getDateTransfert()->format('d/m/Y') : '',
'',
];
}
$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
{
$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;
}
}
// prepare entities to display in datatables (searching using filtersn like %value%) using SQL WHERE
// Fetch the repository for each entity type
$electricMeterRepository = $doctrine->getRepository(ElectricMeter::class);
$gasMeterRepository = $doctrine->getRepository(GasMeter::class);
$entrepriseRepository = $doctrine->getRepository(Entreprise::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 the doctrine paginator
$electric_meters = $electricMeterRepository->createQueryBuilder('e');
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('e.date_debut IS NULL OR (e.date_debut IS NOT NULL AND e.date_debut >= :date_debut)')
->andWhere('e.date_fin IS NULL OR (e.date_fin IS NOT NULL AND e.date_fin >= :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('e.date_fin IS NULL OR (e.date_fin IS NOT NULL AND e.date_fin <= :date_fin)')
->andWhere('e.date_debut IS NULL OR (e.date_debut IS NOT NULL AND e.date_debut <= :date_fin)')
->setParameter('date_fin', $electric_meter_filters['date_fin']);
}
$electric_meters = $electric_meters->getQuery()->getResult();
$gas_meters = $gasMeterRepository->createQueryBuilder('g');
if(isset($gas_meter_filters['titre']) && $gas_meter_filters['titre'] !== '') {
$gas_meters->andWhere('g.titre LIKE :titre')
->setParameter('titre', '%'.$gas_meter_filters['titre'].'%');
}
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('g.date_debut IS NULL OR (g.date_debut IS NOT NULL AND g.date_debut >= :date_debut)')
->andWhere('g.date_fin IS NULL OR (g.date_fin IS NOT NULL AND g.date_fin >= :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('g.date_fin IS NULL OR (g.date_fin IS NOT NULL AND g.date_fin <= :date_fin)')
->andWhere('g.date_debut IS NULL OR (g.date_debut IS NOT NULL AND g.date_debut <= :date_fin)')
->setParameter('date_fin', $gas_meter_filters['date_fin']);
}
$gas_meters = $gas_meters->getQuery()->getResult();
$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();
$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->getFournisseur1(),
$electric_meter->getPrix1(),
$electric_meter->getFournisseur2(),
$electric_meter->getPrix2(),
$electric_meter->getDateTransfert()? $electric_meter->getDateTransfert()->format('d/m/Y') : '',
'',
];
}
$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->getFournisseur1(),
$gas_meter->getPrix1(),
$gas_meter->getFournisseur2(),
$gas_meter->getPrix2(),
$gas_meter->getDateTransfert()? $gas_meter->getDateTransfert()->format('d/m/Y') : '',
'',
];
}
$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,
]);
}
}