Symfony Profiler

<?php
namespace App\Controller;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\Routing\Annotation\Route;
use App\Entity\Exploitant;
use Symfony\Contracts\Translation\TranslatorInterface;
use App\Entity\UserExploitant;
use Doctrine\ORM\Query\ResultSetMapping;
class IndexController extends AbstractController
{
/**
* @Route("/", name="home")
*/
public function home(TranslatorInterface $translator)
{
$translated = $translator->trans('user.login');
//var_dump($translated);exit;
return $this->render('home.html.twig', [
]);
}
function execute($sql){
$em = $this->getDoctrine()->getManager();
$query = $em->getConnection()->prepare($sql);
$query->execute();
return $query->fetchAll();
}
/**
* @Route("/generate_my_sql", name="generate_my_sql")
*
*/
public function generate_my_sql(){
ini_set('memory_limit', '-1');
/*$setttings = [
["as" => ["farm","farm_","F"], "ignore" => ["updated_at"]],
["as" => ["farm_infos","farm_infos_","FI"], "ignore" => ["updated_at"]],
["as" => ["groupement","groupement_","GR"], "ignore" => ["updated_at"]],
["as" => ["groupement_infos","groupement_infos_","GRI"], "ignore" => ["updated_at"]],
];
$fields_as = [];
$extra_jointure = "";
foreach ($setttings as $S) {
$fields_as[] = $this->list_fields($S["as"][0],$S["as"][1],$S["as"][2], $S["ignore"], $extra_jointure);
}
*/
$query = "SELECT lsc.value from list_sous_charge lsc LEFT JOIN list_type_charge ltc ON ltc.id = lsc.type_charge_id where ltc.is_vegetal = true ORDER BY lsc.id asc";
$result = $this->execute($query);
$chargeV = "";
foreach ($result as $res){
$string = str_replace(' ', '_', $res["value"]); // Replaces all spaces with hyphens.
$string = str_replace('-', '_', $string);
$retour = preg_replace('/[^A-Za-z0-9\-]/', '', $string);
$chargeV .= 'charge_agriculture_'.strtolower($retour).' character varying(255), ';
}
$query = "SELECT lsc.value from list_sous_charge lsc LEFT JOIN list_type_charge ltc ON ltc.id = lsc.type_charge_id where ltc.is_vegetal = false ORDER BY lsc.id asc";
$result = $this->execute($query);
$chargeE = "";
foreach ($result as $res){
$string = str_replace(' ', '_', $res["value"]); // Replaces all spaces with hyphens.
$string = str_replace('-', '_', $string); // Replaces all spaces with hyphens.
$retour = preg_replace('/[^A-Za-z0-9\-]/', '', $string);
$chargeE .= 'charge_elevage_'.strtolower($retour).' character varying(255), ';
}
$query = "SELECT value from list_production where is_vegetal = true ORDER BY id asc";
$result = $this->execute($query);
$prod = "";
foreach ($result as $res){
$string = str_replace(' ', '_', $res["value"]); // Replaces all spaces with hyphens.
$string = str_replace('-', '_', $string);
$retour = preg_replace('/[^A-Za-z0-9\-]/', '', $string);
$prod .= 'produit_agriculture_'.strtolower($retour).' character varying(255), ';
}
$query = "SELECT value from list_production where is_vegetal = false ORDER BY id asc";
$result = $this->execute($query);
foreach ($result as $res){
$string = str_replace(' ', '_', $res["value"]); // Replaces all spaces with hyphens.
$string = str_replace('-', '_', $string);
$retour = preg_replace('/[^A-Za-z0-9\-]/', '', $string);
$prod .= 'produit_elevage_'.strtolower($retour).' character varying(255), ';
}
//echo $chargeE;
$reqDrop =
"DROP TABLE IF EXISTS public.datamart;"
;
$reqCreate =
"CREATE TABLE IF NOT EXISTS public.datamart (
exploitant_id integer,
exploitant_nom character varying(255) ,
exploitant_prenom character varying(255),
exploitant_latitude numeric(17,14) ,
exploitant_longitude numeric(17,14),
exploitant_identite_id integer,
exploitant_identite_annee integer,
exploitant_identite_date_saisie date,
exploitant_identite_organisation character varying(255),
exploitant_identite_fonction_accompagnateur character varying(255),
exploitant_identite_nom_accompagnateur character varying(255),
exploitant_identite_id_accompagnateur character varying(255),
exploitant_identite_profil text,
exploitant_identite_sexe_id character varying(255),
exploitant_identite_annee_naissance integer,
exploitant_identite_region character varying(255),
exploitant_identite_district character varying(255),
exploitant_identite_commune character varying(255),
exploitant_identite_fokontany character varying(255),
exploitant_identite_telephone integer,
exploitant_identite_adhesion_op text,
exploitant_identite_nom_op character varying(255),
exploitant_identite_responsabilite_op character varying(255),
exploitant_identite_taille_menage integer,
exploitant_identite_periode_soudure character varying(255),
exploitant_identite_eaf character varying(255),
exploitant_identite_production character varying(255),
production_id integer,
production_agriculture_variete character varying(255),
production_agriculture_saison character varying(255),
production_agriculture_eau character varying(255),
production_agriculture_surface character varying(255),
production_agriculture_date_semis date,
production_agriculture_date_repiquage date,
production_agriculture_date_recolte date,
production_agriculture_volume_recolte character varying(255),
production_agriculture_obs_itk character varying(255) ,
production_agriculture_obs_contexte_campagne character varying(255),
production_agriculture_obs_contexte_commerce character varying(255),
production_agriculture_code_analytique_a character varying(255),
production_agriculture_code_analytique_b character varying(255),
production_elevage_race character varying(255),
production_elevage_elevage character varying(255),
production_elevage_duree_cycle character varying(255),
production_elevage_nb_reproducteurs character varying(255),
production_elevage_investissements_reproducteurs character varying(255),
production_elevage_date_debut date,
production_elevage_date_fin date,
production_elevage_nbre_cheptel character varying(255),
production_elevage_obs_itk character varying(255) ,
production_elevage_obs_contexte_campagne character varying(255),
production_elevage_obs_contexte_commerce character varying(255),
production_elevage_code_analytique_a character varying(255),
production_elevage_code_analytique_b character varying(255),
$chargeV
$chargeE
$prod
retour integer,
stat_agri_surface_reelle_surface integer,
stat_agri_surface_reelle_charges integer,
stat_agri_surface_reelle_produits integer,
stat_agri_surface_reelle_marge integer,
stat_agri_surface_reelle_depense integer,
stat_agri_surface_reelle_recette integer,
stat_agri_surface_reelle_benefice integer,
stat_agri_are_charges integer,
stat_agri_are_produits integer,
stat_agri_are_marge integer,
stat_agri_are_depense integer,
stat_agri_are_recette integer,
stat_agri_are_benefice integer,
stat_agri_are_cout_production integer,
stat_agri_are_prix_vente integer,
stat_agri_are_rendement integer,
stat_elevage_taille_reelle_nb_repro integer,
stat_elevage_taille_reelle_nb_cheptel integer,
stat_elevage_taille_reelle_nb_jeunes_vendus integer,
stat_elevage_taille_reelle_nb_adultes_vendus integer,
stat_elevage_taille_reelle_nb_autres_vendus integer,
stat_elevage_taille_reelle_taux_perte integer,
stat_elevage_taille_reelle_charges integer,
stat_elevage_taille_reelle_produits integer,
stat_elevage_taille_reelle_marge integer,
stat_elevage_taille_reelle_depense integer,
stat_elevage_taille_reelle_recette integer,
stat_elevage_taille_reelle_benefice integer,
stat_elevage_ratio_charge_reproducteur integer,
stat_elevage_ratio_produit_reproducteur integer,
stat_elevage_ratio_marge_reproducteur integer,
stat_elevage_ratio_depense_reproducteur integer,
stat_elevage_ratio_recette_reproducteur integer,
stat_elevage_ratio_benefice_reproducteur integer,
stat_elevage_ratio_prix_jeune integer,
stat_elevage_ratio_prix_adulte integer,
stat_elevage_ratio_prix_autre integer
);"
;
//echo $reqCreate;exit;
try {
$resultDrop = $this->execute($reqDrop);
$resultCreate = $this->execute($reqCreate);
} catch (\Exception $e) {
print_r($e);
}
$query = "SELECT DISTINCT(id), specialite1_id, specialite2_id, specialite3_id, specialite4_id from exploitant_identite";
$result = $this->execute($query);
foreach ($result as $res) {
//var_dump($res);
if ($res['specialite1_id'] == null && $res['specialite2_id'] == null && $res['specialite3_id'] == null && $res['specialite4_id'] == null ) {
$reqInsert =
"INSERT INTO datamart (
SELECT f.id,
f.nom,
f.prenom,
f.latitude,
f.longitude,
expi.id ,
expi.annee ,
expi.date_collecte ,
organisation.value,
technicien.value ,
expi.nom_enqueteur ,
expi.id_collecteur ,
(select array_to_string(array_agg(distinct(lp.value)),',') from exploitant_identite ei
LEFT JOIN exploitant_identite_list_profil eilp ON eilp.exploitant_identite_id = ei.id
LEFT JOIN list_profil lp ON eilp.list_profil_id = lp.id where ei.id = ".$res['id']." ),
sexe.value ,
expi.annee_naissance ,
region.value,
district.value ,
commune.value ,
expi.fokontany ,
expi.telephone ,
(select array_to_string(array_agg(distinct(lto.value)),',') from exploitant_identite ei
LEFT JOIN exploitant_identite_list_type_op eilto ON eilto.exploitant_identite_id = ei.id
LEFT JOIN list_type_op lto ON eilto.list_type_op_id = lto.id where ei.id = ".$res['id']." ),
expi.nom_op ,
adhesion_structure.value ,
expi.taille_menage ,
periode_soudure.value ,
eaf.value
FROM exploitant f
LEFT JOIN exploitant_identite expi ON f.id = expi.exploitant_id
LEFT JOIN list_region region ON region.id = expi.region_exp_id
LEFT JOIN list_district district ON district.id = expi.district_exp_id
LEFT JOIN list_commune commune ON commune.id = expi.commune_exp_id
LEFT JOIN list_organisation organisation ON organisation.id = expi.organisation_id
LEFT JOIN list_technicien technicien ON technicien.id = expi.technicien_id
LEFT JOIN list_sexe sexe ON sexe.id = expi.sexe_id
LEFT JOIN list_responsabilite_op adhesion_structure ON adhesion_structure.id = expi.responsabilite_op_id
LEFT JOIN list_periode_soudure periode_soudure ON periode_soudure.id = expi.periode_soudure_id
LEFT JOIN list_economie_exploitation economie_exploitation ON economie_exploitation.id = expi.economie_exploitation_id
LEFT JOIN list_niveau_vie niveau_vie ON niveau_vie.id = expi.niveau_vie_id
LEFT JOIN list_eaf eaf ON eaf.id = expi.eaf_id
LEFT JOIN list_responsabilite_op responsabilite_op ON responsabilite_op.id = expi.responsabilite_op_id
WHERE expi.id = ".$res['id']."
-- WITH DATA;
);"
;
echo 'i';
try {
// var_dump($reqInsert);exit;
$resultInsert = $this->execute($reqInsert);
} catch (\Exception $e) {
print_r($e);
}
} else {
$query = "SELECT id, exploitant_identite_id from exploitant_speculation where exploitant_identite_id = ".$res['id']." ORDER BY id";
$resultExp = $this->execute($query);
foreach ($resultExp as $key=>$resExp) {
$query = "SELECT lsc.id from list_sous_charge lsc LEFT JOIN list_type_charge ltc ON ltc.id = lsc.type_charge_id where ltc.is_vegetal = true ORDER BY lsc.id asc";
$resultC = $this->execute($query);
$totStatCV = $totStatPV = $depense = $recette = $qteProduitV = $sommePrix = $nbVente = $totStatCP = $depenseP = 0;
$chargeV = "";
$statV = "spec.pv_surface, ";
foreach ($resultC as $resC){
$query = 'select * from charge where exploitant_speculation_id = '.$resExp["id"].' AND sous_charge_id = '.$resC["id"].' ';
$resultCh = $this->execute($query);
$so = 0;
foreach ($resultCh as $charge){
for ($w=1;$w<13;$w++){
//$method_set = "getMois".$w;
$so += (float) $charge["mois".$w];
if ($charge["type_depense_id"] == 2 ){
$depense += (float) $charge["mois".$w];
}
}
}
$chargeV .= "'".$so."' , ";
$totStatCV += (float) $so;
}
$statV .= "'".$totStatCV."' ,";
$query = "SELECT lsc.id from list_sous_charge lsc LEFT JOIN list_type_charge ltc ON ltc.id = lsc.type_charge_id where ltc.is_vegetal = false ORDER BY lsc.id asc";
$resultC = $this->execute($query);
$chargeC = "";
foreach ($resultC as $resC){
$query = 'select * from charge where exploitant_speculation_id = '.$resExp["id"].' AND sous_charge_id = '.$resC["id"].' ';
$resultCh = $this->execute($query);
$so = 0;
foreach ($resultCh as $charge){
for ($w=1;$w<13;$w++){
//$method_set = "getMois".$w;
$so += (float) $charge["mois".$w];
if ($charge["type_depense_id"] == 2 ){
$depenseP += (float) $charge["mois".$w];
}
}
}
$chargeC .= "'".$so."' , ";
$totStatCP += (float) $so;
}
$query = "SELECT id from list_production where is_vegetal = true ORDER BY id asc";
$resultC = $this->execute($query);
$prodC = "";
foreach ($resultC as $resC){
$query = 'select * from produit where exploitant_speculation_id = '.$resExp["id"].' AND production_id = '.$resC["id"].' ';
$resultCh = $this->execute($query);
$so = 0;
foreach ($resultCh as $charge){
$so += (float) $charge["somme"];
$qteProduitV += (float) $charge["quantite"];
if ($charge["production_id"] == 1 ){
$recette += (float) $charge["somme"];
$sommePrix += (float) $charge["pu"];
$nbVente++;
}
}
$prodC .= "'".$so."' , ";
$totStatPV += (float) $so;
}
$statV .= "'".$totStatPV."' ,";
$marge = (float) $totStatPV - (float) $totStatCV ;
$statV .= "'".$marge."' , '".$depense."' ,'".$recette."', ";
$benefice = (float) $recette - (float) $depense;
$statV .= "'".$benefice."', ceil(".(float) $totStatCV ."/spec.pv_surface), ceil(".(float) $totStatPV ."/spec.pv_surface) , ceil(".(float) $marge ."/spec.pv_surface) ,";
$statV .= " ceil(".(float) $depense ."/spec.pv_surface), ceil(".(float) $recette ."/spec.pv_surface) , ceil(".(float) $benefice ."/spec.pv_surface) ,";
if ($qteProduitV == 0) {
$coutprod = 0;
} else {
$coutprod = ((float) $totStatCV / (float) $qteProduitV);
}
$statV .= " ceil($coutprod) ,";
if ($nbVente ==0) {
$prvente = 0;
} else {
$prvente = ((float) $sommePrix / (float) $nbVente);
}
$statV .= " ceil($prvente), ceil(".(float)$qteProduitV."/spec.pv_surface )";
$qteProduit = $qteJeune = $qteAdulte = $qteAutre = $perte = $totStatPP = $recetteP = $recettePP = $nbVenteJ = $nbVenteA = $nbVenteAu = $sommePrixJ = $sommePrixA = $sommePrixAu = 0;
$query = "SELECT id from list_production where is_vegetal = false ORDER BY id asc";
$resultC = $this->execute($query);
foreach ($resultC as $resC){
$query = 'select * from produit where exploitant_speculation_id = '.$resExp["id"].' AND production_id = '.$resC["id"].' ';
$resultCh = $this->execute($query);
$so = 0;
foreach ($resultCh as $charge){
$so += (float) $charge["somme"];
$recettePP = $charge["somme"];
if ($charge["production_id"] < 12){
$qteProduit += (float) $charge["quantite"];
}
if ($charge["production_id"] == 5){
$qteJeune += (float) $charge["quantite"];
$recetteP += (float) $charge["somme"];
$sommePrixJ += (float) $charge["pu"];
$nbVenteJ++ ;
}
if ($charge["production_id"] == 9){
$qteAdulte += (float) $charge["quantite"];
$recetteP += (float) $charge["somme"];
$sommePrixA += (float) $charge["pu"];
$nbVenteA++;
}
if ($charge["production_id"] == 13){
$qteAutre += (float) $charge["quantite"];
$recetteP += (float) $charge["somme"];
$sommePrixAu += (float) $charge["pu"];
$nbVenteAu++;
}
if ($charge["production_id"] == 6 || $charge["production_id"] == 10 || $charge["production_id"] == 14){
$perte += (float) $charge["quantite"];
}
}
$prodC .= "'".$so."' , ";
$totStatPP += (float) $so;
}
$statP = " spec.pa_cheptel, '".$qteProduit."', '".$qteJeune."' , '".$qteAdulte."' , '".$qteAutre."', ";
if ($qteProduit != 0) {
$statP .= " ceil(".(float)$perte/(float)$qteProduit.") ,";
} else {
$statP .= " '0', ";
}
$statP .= "'".$totStatCP."' , '".$totStatPP."' , ";
$marge = (float)$totStatPP - (float)$totStatCP;
$ben = (float) $recetteP - (float) $depenseP;
$statP .= "'".$marge."' , '".$depenseP."', '".$recetteP."', '".$ben."', ";
$statP .= " CASE WHEN spec.pa_cheptel=0 THEN '0'
WHEN spec.pa_cheptel is null THEN '0'
ELSE ceil(".(float) $totStatCP."/spec.pa_cheptel) END ,";
$statP .= " CASE WHEN spec.pa_cheptel=0 THEN '0'
WHEN spec.pa_cheptel is null THEN '0'
ELSE ceil(".(float) $totStatPP."/spec.pa_cheptel) END ,";
$statP .= " CASE WHEN spec.pa_cheptel=0 THEN '0'
WHEN spec.pa_cheptel is null THEN '0'
ELSE ceil(".(float) $marge."/spec.pa_cheptel) END ,";
$statP .= " CASE WHEN spec.pa_cheptel=0 THEN '0'
WHEN spec.pa_cheptel is null THEN '0'
ELSE ceil(".(float) $depenseP."/spec.pa_cheptel) END ,";
$statP .= " CASE WHEN spec.pa_cheptel=0 THEN '0'
WHEN spec.pa_cheptel is null THEN '0'
ELSE ceil(".(float) $recettePP."/spec.pa_cheptel) END ,";
$statP .= " CASE WHEN spec.pa_cheptel=0 THEN '0'
WHEN spec.pa_cheptel is null THEN '0'
ELSE ceil((".(float) $recettePP." - ".$depenseP.")/spec.pa_cheptel) END , ";
if ($nbVenteJ !=0) {
$statP .= " ceil(".(float)$sommePrixJ/ (float) $nbVenteJ.") , ";
} else {
$statP .= "'0', ";
}
if ($nbVenteA !=0) {
$statP .= " ceil(".(float)$sommePrixA/ (float) $nbVenteA.") , ";
} else {
$statP .= "'0', ";
}
if ($nbVenteAu !=0) {
$statP .= " ceil(".(float)$sommePrixAu/ (float) $nbVenteAu.") ";
} else {
$statP .= "'0' ";
}
$myKey = $key+1;
$reqInsert =
"INSERT INTO datamart (
SELECT f.id,
f.nom,
f.prenom,
f.latitude,
f.longitude,
expi.id ,
expi.annee ,
expi.date_collecte ,
organisation.value,
technicien.value ,
expi.nom_enqueteur ,
expi.id_collecteur ,
(select array_to_string(array_agg(distinct(lp.value)),',') from exploitant_identite ei
LEFT JOIN exploitant_identite_list_profil eilp ON eilp.exploitant_identite_id = ei.id
LEFT JOIN list_profil lp ON eilp.list_profil_id = lp.id where ei.id = ".$resExp['exploitant_identite_id']." ),
sexe.value ,
expi.annee_naissance ,
region.value,
district.value ,
commune.value ,
expi.fokontany ,
expi.telephone ,
(select array_to_string(array_agg(distinct(lto.value)),',') from exploitant_identite ei
LEFT JOIN exploitant_identite_list_type_op eilto ON eilto.exploitant_identite_id = ei.id
LEFT JOIN list_type_op lto ON eilto.list_type_op_id = lto.id where ei.id = ".$resExp['exploitant_identite_id']." ),
expi.nom_op ,
adhesion_structure.value ,
expi.taille_menage ,
periode_soudure.value ,
eaf.value ,
specialite.value,
spec.id,
spec.pv_variete,
saison.value,
eau.value,
spec.pv_surface,
spec.pv_date_semis,
spec.pv_date_repiquage,
spec.pv_date_recolte,
spec.pv_volume,
spec.pv_obs_itk,
spec.pv_obs_contexte,
spec.pv_obs_commerce,
spec.pv_obs_code_a,
spec.pv_obs_code_b,
spec.pa_race,
elevage.value,
spec.pa_duree_cycle,
spec.pa_cheptel,
spec.pa_investi_repro,
spec.pa_date_debut,
spec.pa_date_fin,
spec.pa_nb_cheptel,
spec.pa_obs_itk,
spec.pa_obs_contexte,
spec.pa_obs_commerce,
spec.pa_obs_code_a,
spec.pa_obs_code_b,
$chargeV
$chargeC
$prodC
1,
$statV,
$statP
FROM exploitant f
LEFT JOIN exploitant_identite expi ON f.id = expi.exploitant_id
LEFT JOIN exploitant_speculation spec ON expi.id = spec.exploitant_identite_id
LEFT JOIN list_region region ON region.id = expi.region_exp_id
LEFT JOIN list_district district ON district.id = expi.district_exp_id
LEFT JOIN list_commune commune ON commune.id = expi.commune_exp_id
LEFT JOIN list_organisation organisation ON organisation.id = expi.organisation_id
LEFT JOIN list_technicien technicien ON technicien.id = expi.technicien_id
LEFT JOIN list_sexe sexe ON sexe.id = expi.sexe_id
LEFT JOIN list_responsabilite_op adhesion_structure ON adhesion_structure.id = expi.responsabilite_op_id
LEFT JOIN list_periode_soudure periode_soudure ON periode_soudure.id = expi.periode_soudure_id
LEFT JOIN list_economie_exploitation economie_exploitation ON economie_exploitation.id = expi.economie_exploitation_id
LEFT JOIN list_niveau_vie niveau_vie ON niveau_vie.id = expi.niveau_vie_id
LEFT JOIN list_eaf eaf ON eaf.id = expi.eaf_id
LEFT JOIN list_produit specialite ON specialite.id = expi.specialite".$myKey."_id
LEFT JOIN list_responsabilite_op responsabilite_op ON responsabilite_op.id = expi.responsabilite_op_id
LEFT JOIN list_type_culture culture ON culture.id = spec.pv_type_culture_id
LEFT JOIN list_saison saison ON saison.id = spec.pv_saison_id
LEFT JOIN list_eau eau ON eau.id = spec.pv_eau_id
LEFT JOIN list_elevage elevage ON elevage.id = spec.pa_elevage_id
WHERE spec.id = ".$resExp['id']." AND expi.specialite".$myKey."_id is not null
-- WITH DATA;
);"
;
echo 'a';
try {
// var_dump($reqInsert);exit;
$resultInsert = $this->execute($reqInsert);
} catch (\Exception $e) {
print_r($e);
}
}
}
}
//return new Response($req);
If($resultInsert) {
return $this->render('datamart/sql.html.twig');
}
else{
return $this->render('datamart/sql.html.twig',
[
"result" => $resultInsert
]);
}
}
/**
* @Route("/dashboard", name="dashboard")
*/
public function dashboard()
{
$em = $this->getDoctrine()->getManager();
$sql = "";
if(in_array('ROLE_USER', $this->getUser()->getRoles()) || in_array('ROLE_OPERATOR', $this->getUser()->getRoles())){
$user = $this->getUser();
$listeExp = $em->getRepository(UserExploitant::class)->findBy(array('userId'=>$user->getId()));
$myLis = "";
$last_key = @end(array_keys($listeExp));
foreach ($listeExp as $key => $value) {
if ($key == $last_key) {
$myLis .= $value->getExpId();
} else {
$myLis .= $value->getExpId().",";
}
}
if ($myLis == "" ){
$sql .= " AND exp.id = 0 ";
} else {
$sql .= " AND exp.id IN ($myLis) ";
}
}
if( in_array('ROLE_REGIONAL', $this->getUser()->getRoles())){
$user = $this->getUser();
$region = $user->getRegion();
if ($region == "" ){
$sql .= " AND expi.region_exp_id = 0 ";
} else {
$regions = explode('||', $region);
$list = '';
$i = 0;
foreach($regions as $r) {
if ($r != '' && $i == 0){
$list .= $r;
$i++;
} else {
if ($r != '') {
$list .= ','.$r;
$i++;
}
}
}
$sql .= " AND expi.region_exp_id IN ($list) ";
}
}
$rsm = new ResultSetMapping();
$rsm->addScalarResult('nom', 'nom');
$rsm->addScalarResult('prenom', 'prenom');
$rsm->addScalarResult('id', 'id');
$rsm->addScalarResult('annee', 'annee');
$rsm->addScalarResult('latitude', 'latitude');
$rsm->addScalarResult('longitude', 'longitude');
$query = $this->getDoctrine()->getManager()->createNativeQuery("SELECT distinct(exp.id) as id, exp.nom as nom, exp.prenom as prenom,
max(expi.annee) as annee, exp.latitude, exp.longitude, expi.region_exp_id
FROM exploitant exp
JOIN exploitant_identite expi ON expi.exploitant_id = exp.id
WHERE 1=1 $sql group by exp.id,expi.region_exp_id ORDER BY nom asc ", $rsm);
$exps = $query->getResult();
$nb_exp = $em->getRepository(Exploitant::class)
->createQueryBuilder('a')->select('count(a.id)')
->getQuery()->getSingleScalarResult();
$farms = $em->getRepository(Exploitant::class)->findAll();
return $this->render('index.html.twig', [
"nb" => count($exps),
"farms" => $exps,
]);
}
public function sidebar()
{
$em = $this->getDoctrine()->getManager();
$farms = $em->getRepository(Exploitant::class)->findAll();
return $this->render('sidebar.html.twig', [
"farms" => $farms,
]);
}
}

src/Controller/IndexController.php line 17