<?php

namespace App\Controller;

use App\Entity\ActiviteNorme;
use App\Entity\ActivityNormeCommune;
use App\Form\ActiviteNormeType;
use App\Form\ActivityNormeCommuneType;
use App\Form\CommuneOnlySelType;
use App\Form\AnnoterCommuneType;
use App\Form\CommuneOnlySelByDepType;
use App\Form\NormeActionCommuneType;
use App\Form\IndicatorCommunalType;
use App\Form\CommuneByDepartementSelType;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\Routing\Annotation\Route;
use Symfony\Component\HttpFoundation\Request;
use App\Entity\Goal;
use App\Entity\Year;
use App\Entity\Ministry;
use App\Entity\Indicator;
use App\Entity\Target;
use App\Entity\Commune;
use App\Entity\Department;
use App\Entity\Desagregation;
use App\Entity\Indice;
use App\Entity\IndiceCommune;
use App\Entity\NormeActionCommune;
use App\Entity\RealisationProjet;
use App\Entity\SousIndicateur;
use App\Entity\StructureYear;
use App\Entity\ValeurSousIndicateur;
use App\Form\Model\IndiceModel;
use App\Service\SettingsManager;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use Symfony\Component\HttpFoundation\StreamedResponse;
use Symfony\Component\HttpFoundation\BinaryFileResponse;
use Symfony\Component\HttpFoundation\ResponseHeaderBag;
use App\Form\SuiviINDModelCommuneType;
use App\Form\GoalOnlySelType;

class CommuneController extends AbstractController
{

     /**
     * @Route("/app/departement/dashboard", name="app_departement_dashboard")
     */
    public function departementDashboardAction(Request $request, SettingsManager $settingsManager)
    {
        $request->getSession()->set('_application', 1);
        $odd=1; $cible=2; $indicateur=3;

        $departement = $request->getSession()->get('_departement');
        $departement = $this->getDoctrine()->getRepository(Department::class)->findOneBy(['id' => $departement->getId()]);
        
        $default=$this->getDoctrine()->getRepository(Year::class)->maxYear();
        if($request->getSession()->get('_year')>$default || !$request->getSession()->get('_year')){
            $request->getSession()->set('_year', $default);
        }
        $year = $settingsManager->getYear($request->getSession()->get('_year') );
        $etat = $settingsManager->autorisationSaisie($request->getSession()->get('_year'));
        $fin = null;
        if ($etat && $this->isGranted('ROLE_FOCAL')) {
            $fin = $year->getFinfocal();
        } else if ($etat && $this->isGranted('ROLE_DPP')) {
            $fin = $year->getFindpp();
        }else if ($this->isGranted('ROLE_DDD')) {
            $fin = $year->getFindpp();
        }

        $communes = $departement->getCommunes();
        $all=0;
        foreach ($communes as $commune) {
            if(count($settingsManager->getNoIndicateursByCommune($year, $commune))==0){
                $all++;
            }
        }

        
        return $this->render('communes/ddd_dashboard.html.twig', [
            'odds' => $settingsManager->compter(2030,$odd),
            'cibles' => $settingsManager->compter(2030,$cible),
            'indicateurs' => $settingsManager->compter(2030,$indicateur),
            'indicateurscommunaux' => $settingsManager->compter(2030,$indicateur,true),
            'fin' => $fin,
            'year' => $year,
            'departement' => $departement,
            'communes' => $communes,
            'all' => $all,
        ]);
    }
    /**
     * @Route("/app/commune/dashboard", name="app_commune_dashboard")
     */
    public function communeDashboardAction(Request $request, SettingsManager $settingsManager)
    {
        $commune = $request->getSession()->get('_commune');
        if (!$commune) {
            $route = $request->headers->get('referer');
            return $this->redirect($route);
        }
        $commune = $this->getDoctrine()->getRepository(Commune::class)->findOneBy(['id' => $commune->getId()]);
        $normes =  $commune->getNormeActionCommunes();

        $oddAll = $this->getDoctrine()->getRepository(Goal::class)->findAll();

        $odd = 1;
        $cible = 2;
        $indicateur = 3;
        $year = $settingsManager->getYear($request->getSession()->get('_year'));
        $etat = $settingsManager->autorisationSaisieCommune($request->getSession()->get('_year'));
        $fin = null;
        if ($etat && $this->isGranted('ROLE_FOCAL')) {
            $fin = $year->getFinfocal2();
        } else if ($etat && $this->isGranted('ROLE_DPP')) {
            $fin = $year->getFindpp2();
        }

        $indicateursRenseignables = $this->getDoctrine()->getRepository(Indicator::class)->findBy(['communal' => true]);

        return $this->render('communes/dashboard.html.twig', [
            'commune' => $commune,
            'fin' => $fin,
            'oddAll' => $oddAll,
            'year' => $year, 
            'odds' => $settingsManager->compter(2030, $odd),
            'cibles' => $settingsManager->compter(2030, $cible),
            'ciblesspacialisees' => $settingsManager->getCiblesSpacialisees($commune),
            'indicateurs' => $settingsManager->compter(2030, $indicateur),
            'indicateurscommunaux' => count($settingsManager->getIndicateursRenseignables($commune)),
            'indicateursRenseignables' => count($indicateursRenseignables),
            'normes' => $normes,
        ]);
    }

    /**
     * @Route("/app/commune/target", name="app_commune_target")
     */
    public function communeTargetAction(Request $request)
    {
        $targets = $this->getDoctrine()->getRepository(TArget::class)->findByAgenda(2030);
        return $this->render('communes/target.html.twig', [
            'targets' => $targets,
        ]);
    }

    /**
     * @Route("/app/commune/target_spacialise", name="app_commune_targetspacialise")
     */
    public function communeTargetSpacialiseAction(Request $request, SettingsManager $settingsManager)
    {
        $commune = $request->getSession()->get('_commune');
        if (!$commune) {
            $route = $request->headers->get('referer');
            return $this->redirect($route);
        }
        $commune = $this->getDoctrine()->getRepository(Commune::class)->findOneBy(['id' => $commune->getId()]);
        $targets = $settingsManager->getCiblesSpacialisees($commune);
        return $this->render('communes/targetspacialise.html.twig', [
            'targets' => $targets,
        ]);
    }

    /**
     * @Route("/app/departement/target_spacialise", name="app_departement_targetspacialise")
     */
    public function departementTargetSpacialiseAction(Request $request, SettingsManager $settingsManager)
    {
        $departement = $request->getSession()->get('_departement');
        $departement = $this->getDoctrine()->getRepository(Department::class)->findOneBy(['id' => $departement->getId()]);
        $communes = $departement->getCommunes();

        $targets=null;

        $form = $this->createForm(CommuneOnlySelByDepType::class, null, array('communes' => $communes,
        ));

        $form->handleRequest($request);
        if ($form->isSubmitted() && $form->isValid()) {
            $commune = $form->get('commune')->getData();
            $commune = $this->getDoctrine()->getRepository(Commune::class)->findOneBy(['id' => $commune]);
            $targets = $settingsManager->getCiblesSpacialisees($commune);
        }
        return $this->render('communes/deptargetspacialise.html.twig', [
            'communes' => $communes,
            'targets' => $targets,
            'form' => $form->createView(),
        ]);
    }

    /**
     * @Route("/app/commune/indicator", name="app_commune_indicator")
     */
    public function communeIndicatorAction(Request $request)
    {
        $indicators = $this->getDoctrine()->getRepository(Indicator::class)->findBy(['communal' => false]);

        return $this->render('communes/indicator.html.twig', [
            'indicators' => $indicators,
        ]);
    }

    /**
     * @Route("/app/commune/indicator_spacialise", name="app_commune_indicatorspacialise")
     */
    public function communeIndicatorSpacialiseAction(Request $request, SettingsManager $settingsManager)
    {
        $commune = $request->getSession()->get('_commune');
        if (!$commune) {
            $route = $request->headers->get('referer');
            return $this->redirect($route);
        }
        $commune = $this->getDoctrine()->getRepository(Commune::class)->findOneBy(['id' => $commune->getId()]);
        $indicators = $settingsManager->getIndicateursRenseignables($commune);

        $allindicators = $this->getDoctrine()->getRepository(Indicator::class)->findBy(['communal'=>true]);

        return $this->render('communes/indicatorspacialise.html.twig', [
            'indicators' => $indicators,
            'allindicators' => $allindicators,
        ]);
    }

     /**
     * @Route("/app/departement/indicator_spacialise", name="app_departement_indicatorspacialise")
     */
    public function departementIndicatorSpacialiseAction(Request $request, SettingsManager $settingsManager)
    {
        
        $departement = $request->getSession()->get('_departement');
        $departement = $this->getDoctrine()->getRepository(Department::class)->findOneBy(['id' => $departement->getId()]);
        $communes = $departement->getCommunes();
        
        $indicators=null;
        $form = $this->createForm(CommuneOnlySelByDepType::class, null, array('communes' => $communes,
        ));

        $form->handleRequest($request);
        if ($form->isSubmitted() && $form->isValid()) {
            $commune = $form->get('commune')->getData();
            $commune = $this->getDoctrine()->getRepository(Commune::class)->findOneBy(['id' => $commune->getId()]);
            $indicators = $settingsManager->getIndicateursRenseignables($commune);
        }
        
        $allindicators = $this->getDoctrine()->getRepository(Indicator::class)->findBy(['communal'=>true]);

        return $this->render('communes/depindicatorspacialise.html.twig', [
            'indicators' => $indicators,
            'allindicators' => $allindicators,
            'form' => $form->createView(),
        ]);
    }

    /**
     * @Route("/app/commune/goal", name="app_commune_goal")
     */
    public function communeGoalAction(Request $request)
    {
        $goals = $this->getDoctrine()->getRepository(Goal::class)->findBy(['agenda' => 2030]);;
        return $this->render('communes/goal.html.twig', [
            'goals' => $goals,
        ]);
    }

    /**
     * @Route("/app/commune/chargement/indicators", name="app_commune_chargement_indicators")
     */
    public function communeChargementIndicatorsAction(Request $request, SettingsManager $settingsManager)
    {
        $etat = $settingsManager->autorisationSaisieCommune($request->getSession()->get('_year'));
        $year = $settingsManager->getYear($request->getSession()->get('_year'));

        $structureyear = $settingsManager->getStructureYear($request->getSession()->get('_year'));

        $odds = $this->getDoctrine()->getRepository(Goal::class)->findAll();

        $commune = $request->getSession()->get('_commune');
        $commune = $this->getDoctrine()->getRepository(Commune::class)->findOneBy(['id' => $commune->getId()]);

        $queryBuilder = $this->getDoctrine()->getManager()->createQueryBuilder();
        $queryBuilder->select(array('i'))
            ->from(Indicator::class, 'i')
            ->andWhere('i.communal = :communal')
            ->orderBy('i.id')
            ->setParameter('communal', true);
        $data = $queryBuilder->getQuery()->getScalarResult();

        return $this->render('communes/chargement_indicators.html.twig', [
            'etat' => $etat,
            'year' => $year,
            'structureyear' => $structureyear,
            'data' => $data,
            'odds' => $odds,
            'commune' => $commune,
        ]);
    }

    /**
     * @Route("/app/commune/format", name="app_commune_format")
     */
    public function communeDownloadFormatAction(Request $request, SettingsManager $settingsManager)
    {
        set_time_limit(0);
        
        $annee = $request->getSession()->get('_year');
        $commune = $request->getSession()->get('_commune');
        $commune = $this->getDoctrine()->getRepository(Commune::class)->findOneBy(['id' => $commune->getId()]);
        $fileName = $commune->getName() . '-ODD-' . $annee . '.xlsx';
        
        $maquette = $this->getParameter('kernel.project_dir') . '/public/upload/communes/format_2023.xlsx';
        $spreadsheet = IOFactory::load($maquette);
        $spreadsheet->getProperties()
                    ->setCreator("DGCS-ODD")
                    ->setTitle("Format SIGODD");

        $odds = $this->getDoctrine()->getRepository(Goal::class)->findBy(['agenda' => 2030]);

        /*$sheet = $spreadsheet->getSheet(0);
        $line = 3;
        $sheet->setCellValue('A1', $commune->getName().' - '. 'Indicateurs Renseignables');
        foreach ($odds as $odd) {
			foreach ($odd->getTargets() as $cible){
                foreach ($settingsManager->getIndicateursRenseignablesByCible($cible) as $indicateur){
                    $sheet->insertNewRowBefore($line+1);
                    $sheet->setCellValue('A' . $line, $cible->getCode().' ');
                    $sheet->setCellValue('B' . $line, $indicateur->getCode());
                    $sheet->setCellValue('C' . $line, $indicateur->getName());
                    $line++;
                }
            }
        }
        //$sheet->removeRow($line, 1);
        $sheet->getRowDimension($line)->setVisible(false);*/

        $sheet = $spreadsheet->getSheet(2);
        $line = 3;
        $sheet->setCellValue('A1', $commune->getName().' - '. 'Indicateurs ODD');
        foreach ($odds as $odd) {
			foreach ($settingsManager->getCibleCommuneByOdd($commune, $odd) as $cible){
                foreach ($settingsManager->getIndicateurDesagregeCommuneByCible($cible) as $indicateur){

                    $suivi = $this->getDoctrine()->getRepository(Indice::class)
                    ->findOneBy(['indicator' => $indicateur, 'year' => $annee, 'desagregation' => 3, 'modalite' => $commune->getId()]);

                    $sheet->insertNewRowBefore($line+1);
                    $sheet->setCellValue('A' . $line, $cible->getCode().' ');
                    $sheet->setCellValue('B' . $line, $indicateur->getCode());
                    $sheet->setCellValue('C' . $line, $indicateur->getName());
                    $sheet->setCellValue('D' . $line, $suivi ? $suivi->getValue() : '');
                    $sheet->setCellValue('E' . $line, $suivi ? $suivi->getObservation(): '');
                    $line++;
                }
            }
        }
        
        $sheet->getRowDimension($line)->setVisible(false);
        //$sheet->removeRow($line, 1);
        

        //Retour sur la feuille accueil
        $sheet = $spreadsheet->setActiveSheetIndex(1);
        header('Content-type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); 
        header('Content-Disposition: attachment;filename="'. $fileName .'"');
        header('Cache-Control: max-age=0');
        $writer = IOFactory::createWriter($spreadsheet,'Xlsx');
        
        ob_end_clean();
        $writer->save('php://output');
        exit();
    }

    /**
     * @Route("/app/commune/import", name="app_commune_import")
     */
    public function communeImportAction(Request $request, SettingsManager $settingsManager)
    {
        $year = $settingsManager->getYear($request->getSession()->get('_year'));
        $commune = $request->getSession()->get('_commune');
        $commune = $this->getDoctrine()->getRepository(Commune::class)->findOneBy(['id' => $commune->getId()]);
        $fileok=true;

        if ($year && $commune) {
            $structureyear = $settingsManager->getStructureYear($request->getSession()->get('_year'));
            if (!$structureyear) {
                $structureyear = new StructureYear();
                $structureyear->setCommune($commune);
                $structureyear->setYear($year);
            }
            //Fichier
            $file = $request->files->get('fichier');
            if (!empty($file)) {
                $fileName = 'data_' . $commune->getCode() . '_' . $year->getName() . '.' . $file->guessExtension();
                $folder = $this->getParameter('kernel.project_dir') . '/public/upload/communes/formats/';
                if (file_exists($folder . $fileName)) {
                    chmod($folder . $fileName, 0755);
                    unlink($folder . $fileName);
                }
                $file->move($folder, $fileName);
                $structureyear->setFile($fileName);

                //Importation des données
                $spreadsheet = IOFactory::load($folder . $fileName);
                if($spreadsheet->getSheetCount()==3){

                    //Indicateur renseignable
                    $sheet = $spreadsheet->setActiveSheetIndex(1);
                    for ($i = 2; $i <= 41; $i++) {
                        $code = $sheet->getCell('B' . $i)->getCalculatedValue();
                        $codeSous = $sheet->getCell('C' . $i)->getCalculatedValue();
                        if($code && $codeSous){
                            $sous = $this->getDoctrine()->getRepository(SousIndicateur::class)->findOneBy(['code' => $codeSous]);
                            if ($sous) {
                                $valeur = $this->getDoctrine()->getRepository(ValeurSousIndicateur::class)->findOneBy(['commune' => $commune, 'year' => $year, 'sousindicateur' => $sous]);
                                $indice_delete=$valeur;
                                if (!$valeur) {
                                    $valeur = new ValeurSousIndicateur();
                                    $valeur->setCommune($commune);
                                    $valeur->setYear($year);
                                    $valeur->setSousindicateur($sous);
                                    $valeur->setObservation($sheet->getCell('F' . $i)->getCalculatedValue());
                                    $valeur->setSource($sheet->getCell('G' . $i)->getCalculatedValue());
                                }
                                $val = $sheet->getCell('E' . $i)->getCalculatedValue();
    
                                if (is_numeric($val)) {
                                    $val = floatval(str_replace(',', '.', $val));
                                    $valeur->setValeur($val);
                                    $this->getDoctrine()->getManager()->persist($valeur);
                                }else{
                                   //Supprimer l'indice
                                   if(strtoupper($val)=="OUI" || strtoupper($val)=="NON"){
                                    $valeur->setValeur(strtoupper($val));
                                    $this->getDoctrine()->getManager()->persist($valeur);
                                   }else if($indice_delete){
                                        $this->getDoctrine()->getManager()->remove($indice_delete);
                                   }
                                }
                                
                            }else{
                                $fileok=false;
                                $this->addFlash('error', "Erreur d'importation sur la ligne ".$i. " du format excel");
                            }
                        }
                        else if($code && !$codeSous){
                            $indicator = $this->getDoctrine()->getRepository(Indicator::class)->findOneBy(['code' => $code,'communal'=>true]);
                            if ($indicator) {
                                $indice_commune = $this->getDoctrine()->getRepository(IndiceCommune::class)->findOneBy(['commune' => $commune, 'year' => $year, 'indicator' => $indicator]);
                                $indice_delete=$indice_commune;
                                if (!$indice_commune) {
                                    $indice_commune = new IndiceCommune();
                                    $indice_commune->setCommune($commune);
                                    $indice_commune->setYear($year);
                                    $indice_commune->setIndicator($indicator);
                                }
                                $val = str_replace('%','',$sheet->getCell('E' . $i)->getCalculatedValue());

                                if (is_numeric($val)) {
                                    $val = floatval(str_replace(',', '.', $val));
                                    $indice_commune->setValue($val);
                                    $indice_commune->setRefYear($indicator->getRefYear());
                                    $indice_commune->setRefValue($indicator->getRefValue());
                                    $indice_commune->setMinValue($indicator->getMinValue());
                                    $indice_commune->setMaxValue($indicator->getMaxValue());
                                    $indice_commune->setObservation($sheet->getCell('F' . $i)->getCalculatedValue());
                                    $indice_commune->setSource($sheet->getCell('G' . $i)->getCalculatedValue());
                                    $this->getDoctrine()->getManager()->persist($indice_commune);
                                }else{
                                //Supprimer l'indice
                                if($indice_delete){
                                        $this->getDoctrine()->getManager()->remove($indice_delete);
                                }
                                }
                                
                            }else{
                                $fileok=false;
                                $this->addFlash('error', "Erreur d'importation sur la ligne ".$i. " du format excel");
                            }
                        }
                        
                    }

                    //Indicateurs ODD
                    /*$sheet = $spreadsheet->setActiveSheetIndex(1);
                    for ($i = 3; $i <= $sheet->getHighestDataRow(); $i++) {
                        $modalite = $commune->getId();
                        $desagregation = $this->getDoctrine()->getRepository(Desagregation::class)->findOneBy(['id' => 3]);
                        $code = $sheet->getCell('B' . $i)->getCalculatedValue();
                        $indicator = $this->getDoctrine()->getRepository(Indicator::class)->findOneBy(['code' => $code,'communal'=>false]);
                        if ($indicator) {
                            $indice = $this->getDoctrine()->getRepository(Indice::class)->findOneBy(['year' => $year->getName(), 'indicator' => $indicator, 'modalite' => $modalite, 'desagregation' => $desagregation]);
                                if (!$indice) {
                                    $indice = new Indice();
                                    $indice->setYear($year->getName());
                                    $indice->setIndicator($indicator);
                                    $indice->setModalite($modalite);
                                    $indice->setDesagregation($desagregation);
                                }

                                $val = $sheet->getCell('D' . $i)->getCalculatedValue();
                                if ($val == "#DIV/0!" || $val == "#REF!" || $val == "#VALEUR!" || $val == "") {
                                        //$val = null;
                                } 
                                else {
                                    $val = floatval(str_replace(',', '.', $val));
                                        
                                    $indice->setValeurAdministrative($val);
                                    $indice->setValue($val);
                                    $indice->setObservationAdministrative($sheet->getCell('E' . $i)->getCalculatedValue());
                                    $indice->setObservation($sheet->getCell('E' . $i)->getCalculatedValue());
                                    $indice->setRefYear($indicator->getRefYear());
                                    $indice->setRefValue($indicator->getRefValue());
                                    $indice->setMinValue($indicator->getMinValue());
                                    $indice->setMaxValue($indicator->getMaxValue());
                                    $this->getDoctrine()->getManager()->persist($indice);
                                }
                        }
                    }*/
                    if($fileok){
                        $this->getDoctrine()->getManager()->persist($structureyear);
                        $this->getDoctrine()->getManager()->flush();
                        $this->addFlash('success', 'Données importées avec succès');
                    }
                    
                }else{
                    $this->addFlash('error', "Erreur d'importation, Veuillez utiliser le bon format.");
                }   
            }
            
        } else {
            $this->addFlash('error', "Une erreur s'est produite. Veuillez réessayer plus tard.");
        }
        return $this->redirectToRoute('app_commune_suivi_indicateur');
    }

    /**
     * @Route("/app/commune/all_data/{id}", name="all_data_communes", defaults={"id"=0})
     */
    public function allData(Request $request, SettingsManager $settingsManager,$id=0)
    {
        $fileName="";
        $year = $settingsManager->getYear($request->getSession()->get('_year'));
        
        if($id!=0){
            $departement = $this->getDoctrine()->getRepository(Department::class)->findOneBy(['id' => $id]);
            $communes = $departement->getCommunes();
            $fileName='Donnees-'.$departement->getName().'-'.$year->getName().'.xlsx';
        }else{
            $fileName='Donnees-Communes-'.$year->getName().'.xlsx'; 
            $communes = $this->getDoctrine()->getRepository(Commune::class)->findAll();
        }
        
        $indicators = $this->getDoctrine()->getRepository(Indicator::class)->findBy(['communal'=>true], array('ordre' => 'ASC'));

        $maquette= $this->getParameter('kernel.project_dir') . '/public/upload/communes/data.xlsx';
        //Chargement de la maquette de base
        $spreadsheet = IOFactory::load($maquette);
        $sheet = $spreadsheet->getSheet(0);
        $sheet->getColumnDimension('A')->setWidth(18);
        $bold = array( "font" => array("bold" => true));

        $i=2;
        foreach ($indicators as $indicator) {
            $sheet->getColumnDimensionByColumn($i)->setWidth(20);
            $sheet->setCellValueByColumnAndRow($i, 1, $indicator->getCode().". ".$indicator->getName());
            $sheet->getCellByColumnAndRow($i,1)->getStyle()->applyFromArray($bold);
            $i++;
            foreach ($indicator->getSousIndicateurs() as $indicator) {
                $sheet->getColumnDimensionByColumn($i)->setWidth(20);
                $sheet->setCellValueByColumnAndRow($i, 1, $indicator->getCode().". ".$indicator->getNom());
                $i++;
            }
        }

        $i=2;
        foreach ($communes as $commune) {
            $structure=$settingsManager->getStructureYear($year->getName(), null, $commune); 
            $j=2;
            $sheet->setCellValueByColumnAndRow(1, $i, $commune->getName());
            foreach ($indicators as $indicator) {
                $val=$settingsManager->getIndiceCommune($commune, $indicator, $year);
                $sheet->setCellValueByColumnAndRow($j, $i,(is_numeric($val) && $structure && $structure->getDpp2()) ? $val : "ND" );
                $sheet->getCellByColumnAndRow($j,$i)->getStyle()->applyFromArray($bold);
                $j++;
                foreach ($indicator->getSousIndicateurs() as $indicator) {
                    $val=$settingsManager->getSousIndicateurCommune($commune, $indicator, $year);
                    $sheet->setCellValueByColumnAndRow($j, $i, ((is_numeric($val) || strtoupper($val)=="OUI" || strtoupper($val)=="NON") && $structure && $structure->getDpp2() ) ? $val : "ND");
                    $j++;
                }
            }
            $i++;
        }

        //Télécharchement du fichier
        $writer = new Xlsx($spreadsheet);
        $response =  new StreamedResponse(
            function () use ($writer) {
                $writer->save('php://output');
            }
        );
        $response->headers->set('Content-Type', 'application/vnd.ms-excel');
        $response->headers->set('Content-Disposition', 'attachment;filename="'.$fileName.'"');
        $response->headers->set('Cache-Control','max-age=0');
        return $response;
    }


    /**
     * @Route("/app/admin/all_data", name="all_data_indicateurs")
     */
    public function allDataIndicateur(Request $request, SettingsManager $settingsManager)
    {
        $sql="SELECT indicator.id FROM indicator, target, goal where indicator.target_id = target.id and target.goal_id = goal.id and goal.agenda =2030 and communal =false order by LENGTH(indicator.code), indicator.code";
        $stmt =  $this->getDoctrine()->getManager()->getConnection()->prepare($sql);
        $stmt->execute([]);
        $indicators = $stmt->fetchAll();

        /*$indicators = $this->getDoctrine()->getRepository(Indicator::class)->findBy(['communal'=>false], array('LENGTH(code)' => 'ASC'));
        dd($indicators);*/
        $year = $settingsManager->getYear($request->getSession()->get('_year'));

        $maquette= $this->getParameter('kernel.project_dir') . '/public/upload/indicateurs.xlsx';
        //Chargement de la maquette de base
        $spreadsheet = IOFactory::load($maquette);
        $sheet = $spreadsheet->getSheet(0);

        $i=4;
        for($j=4; $j>=0;$j--){
            $sheet->setCellValueByColumnAndRow($i, 1, $year->getName()-$j);
            $i++;
        }
            
        $i=2;
        foreach ($indicators as $ind) {
            $indicator=$this->getDoctrine()->getRepository(Indicator::class)->find($ind["id"]);
            $sheet->setCellValueByColumnAndRow(1, $i, $indicator->getCode().". ".$indicator->getName());
            $sheet->setCellValueByColumnAndRow(2, $i, 'ODD '.$indicator->getTarget()->getGoal()->getCode());
            $sheet->setCellValueByColumnAndRow(3, $i, 'Cible '.$indicator->getTarget()->getCode());
            $k=4;
            for($j=4; $j>=0;$j--){
                $an=$year->getName()-$j;
                $sheet->setCellValueByColumnAndRow($k, $i, $settingsManager->getValeurIndicateur($an,$indicator));
                $k++;
            }
            $i++;
        }

        //Télécharchement du fichier
        $fileName='Valeurs-Indicateurs-'.$year->getName().'.xlsx';
        $writer = new Xlsx($spreadsheet);
        $response =  new StreamedResponse(
            function () use ($writer) {
                $writer->save('php://output');
            }
        );
        $response->headers->set('Content-Type', 'application/vnd.ms-excel');
        $response->headers->set('Content-Disposition', 'attachment;filename="'.$fileName.'"');
        $response->headers->set('Cache-Control','max-age=0');
        return $response;
    }


    /**
     * @Route("/app/commune/validation", name="app_commune_validation")
     */
    public function communeValidationAction(Request $request, SettingsManager $settingsManager)
    {
        $year = $settingsManager->getYear($request->getSession()->get('_year'));
        $commune = $request->getSession()->get('_commune');
        $commune = $this->getDoctrine()->getRepository(Commune::class)->findOneBy(['id' => $commune->getId()]);
        $structureyear = $settingsManager->getStructureYear($request->getSession()->get('_year'));
        
        if(!$structureyear){
            $structureyear = new StructureYear();
            $structureyear->setYear($year);
            $structureyear->setCommune($commune);
        }

        if ($year && $commune && $structureyear) {
            if ($this->isGranted('ROLE_DPP')) {
                $structureyear->setDpp2(true);
            }
            if ($this->isGranted('ROLE_FOCAL')) {
                $structureyear->setFocal2(true);
            }
            $this->getDoctrine()->getManagerForClass(StructureYear::class)->persist($structureyear);
            $this->getDoctrine()->getManagerForClass(StructureYear::class)->flush();
            if ($this->isGranted('ROLE_DPP')) {
                $this->addFlash('success', 'Données validées avec succès');
            }
            if ($this->isGranted('ROLE_FOCAL')) {
                $this->addFlash('success', 'Données soumises à la validation du SE avec succès');
            }
        } else {
            $this->addFlash('error', "Une erreur s'est produite. Veuillez réessayer plus tard.");
        }
        //return $this->redirectToRoute('app_commune_chargement_indicators');
        $route = $request->headers->get('referer');
        return $this->redirect($route);
    }

    /**
     * @Route("/app/commune/data", name="app_commune_data")
     */
    public function communeDataAction(Request $request, SettingsManager $settingsManager)
    {
        $commune = $request->getSession()->get('_commune');
        $commune = $this->getDoctrine()->getRepository(Commune::class)->findOneBy(['id' => $commune->getId()]);
        
        $year= $request->getSession()->get('_year');
        $maquette= $this->getParameter('kernel.project_dir') . '/public/upload/communes/download.xlsx';
        //Chargement de la maquette de base
        $spreadsheet = IOFactory::load($maquette);

        $sheet = $spreadsheet->getSheet(0);
        
        $sheet->setCellValue('A1', $commune->getName().' - '.$year);

        $sheet->setCellValue('C3', $year-4);
        $sheet->setCellValue('D3', $year-3);
        $sheet->setCellValue('E3', $year-2);
        $sheet->setCellValue('F3', $year-1);
        $sheet->setCellValue('G3', $year);

        $queryBuilder = $this->getDoctrine()->getManager()->createQueryBuilder();
        $queryBuilder->select(array('i'))
            ->from(Indicator::class, 'i')
            ->andWhere('i.communal = :communal')
            ->orderBy('i.id')
            ->setParameter('communal', true);
        $indicators = $queryBuilder->getQuery()->getScalarResult();

        $line=4;
        foreach ($indicators as $indicator) {
            $sheet->setCellValue('A'.$line, $indicator['i_code']);
            $sheet->setCellValue('B'.$line, $indicator['i_name']);
            $sheet->setCellValue('C'.$line, $settingsManager->getIndiceCommune($commune, $settingsManager->getIndicateur($indicator['i_id']) , $settingsManager->getYear($year-4)));
            $sheet->setCellValue('D'.$line, $settingsManager->getIndiceCommune($commune, $settingsManager->getIndicateur($indicator['i_id']) , $settingsManager->getYear($year-3)));
            $sheet->setCellValue('E'.$line, $settingsManager->getIndiceCommune($commune, $settingsManager->getIndicateur($indicator['i_id']) , $settingsManager->getYear($year-2)));
            $sheet->setCellValue('F'.$line, $settingsManager->getIndiceCommune($commune, $settingsManager->getIndicateur($indicator['i_id']) , $settingsManager->getYear($year-1)));
            $sheet->setCellValue('G'.$line, $settingsManager->getIndiceCommune($commune, $settingsManager->getIndicateur($indicator['i_id']) , $settingsManager->getYear($year)));
            $sheet->setCellValue('H'.$line, $settingsManager->getIndiceCommuneObservation($commune, $settingsManager->getIndicateur($indicator['i_id']) , $settingsManager->getYear($year)));
            $line++;
        }

        //Télécharchement du fichier
        $fileName=$commune->getName().'-ODD-'.$year.'.xlsx';
        $writer = new Xlsx($spreadsheet);
        $response =  new StreamedResponse(
            function () use ($writer) {
                $writer->save('php://output');
            }
        );
        $response->headers->set('Content-Type', 'application/vnd.ms-excel');
        $response->headers->set('Content-Disposition', 'attachment;filename="'.$fileName.'"');
        $response->headers->set('Cache-Control','max-age=0');
        return $response;
    }

    /**
     * @Route("/app/commune/suivi/normes/{id}", name="app_commune_suivi_normes", defaults={"id"=0})
     */
    public function communeSuiviNormeAction($id=0, Request $request, SettingsManager $settingsManager)
    {
        $activities=null;
        $commune=null;
        $year = $request->getSession()->get('_year');
        $form = $this->createForm(CommuneOnlySelType::class, null, array(
        ));

        $form->handleRequest($request);
        if ($form->isSubmitted() && $form->isValid()) {
            $commune = $form->get('commune')->getData();
            $activities=$this->getDoctrine()->getRepository(ActivityNormeCommune::class)->findBy(['commune' => $commune, 'year' => $year]);
        }
        return $this->render('communes/suivi_normes.html.twig', [
            'activities' => $activities,
            'year' => $year,
            'commune' => $commune,
            'form' => $form->createView(),
            'etat' => $settingsManager->autorisationSaisieCommune($request->getSession()->get('_year'))
        ]);
    }

    /**
     * @Route("/app/commune/norme/new", name="app_commune_norme_new")
     */
    public function communeNewNormeAction(Request $request)
    {
        $norme = new ActiviteNorme();

        $form = $this->createForm(ActiviteNormeType::class,$norme);

        $form->handleRequest($request);
        if ($form->isSubmitted() && $form->isValid()) {
            $norme = $form->getData();
            $commune = $request->getSession()->get('_commune');
            $commune = $this->getDoctrine()->getRepository(Commune::class)->findOneBy(['id' => $commune->getId()]);
            $norme->setCommune($commune);
            $this->getDoctrine()->getManagerForClass(ActiviteNorme::class)->persist($norme);

            if ($request->get('realisations')) {
                foreach ($request->get('realisations') as $r) {
                    $realisation = new RealisationProjet();
                    $realisation->setCode($r['coderealisation']);
                    $realisation->setLibelle($r['namerealisation']);
                    $realisation->setType('REALISATION');
                    $realisation->setActivitenorme($norme);
                    $this->getDoctrine()->getManager()->persist($realisation);
                }
            }
            if ($request->get('projets')) {
                foreach ($request->get('projets') as $p) {
                    $projet = new RealisationProjet();
                    $projet->setCode($p['codeprojet']);
                    $projet->setLibelle($p['nameprojet']);
                    $projet->setType('PROJET');
                    $projet->setActivitenorme($norme);
                    $this->getDoctrine()->getManager()->persist($projet);
                }
            }
            $this->getDoctrine()->getManager()->flush();
            $this->addFlash('success', "Norme d'action ajoutée avec succès");
            return $this->redirectToRoute('app_commune_norme_new');
        }

        return $this->render('communes/norme_new.html.twig', [
            'form' => $form->createView(),
        ]);
    }

    /**
     * @Route("/app/commune/norme/edit/{id}", name="app_commune_norme_edit")
     */
    public function communeEditNormeAction($id, Request $request, SettingsManager $settingsManager)
    {
        $norme = $this->getDoctrine()->getRepository(ActiviteNorme::class)->find($id);
        $form = $this->createForm(ActiviteNormeType::class,$norme);

        $form->handleRequest($request);
        if ($form->isSubmitted() && $form->isValid()) {
            $norme = $form->getData();
            $commune = $request->getSession()->get('_commune');
            $commune = $this->getDoctrine()->getRepository(Commune::class)->findOneBy(['id' => $commune->getId()]);
            $norme->setCommune($commune);
            $this->getDoctrine()->getManagerForClass(ActiviteNorme::class)->persist($norme);

            $this->getDoctrine()->getRepository(ActiviteNorme::class)->deleteRealisationProjet($norme->getId());

            if ($request->get('realisations')) {
                foreach ($request->get('realisations') as $r) {
                    $realisation = new RealisationProjet();
                    $realisation->setCode($r['coderealisation']);
                    $realisation->setLibelle($r['namerealisation']);
                    $realisation->setType('REALISATION');
                    $realisation->setActivitenorme($norme);
                    $this->getDoctrine()->getManager()->persist($realisation);
                }
            }
            if ($request->get('projets')) {
                foreach ($request->get('projets') as $p) {
                    $projet = new RealisationProjet();
                    $projet->setCode($p['codeprojet']);
                    $projet->setLibelle($p['nameprojet']);
                    $projet->setType('PROJET');
                    $projet->setActivitenorme($norme);
                    $this->getDoctrine()->getManager()->persist($projet);
                }
            }
            $this->getDoctrine()->getManager()->flush();
            $this->addFlash('success', "Norme d'action modifiée avec succès");
            return $this->redirectToRoute('app_commune_normes');
        }

        return $this->render('communes/norme_edit.html.twig', [
            'realisations' => $settingsManager->getRealisationsProjets($norme,'REALISATION'),
            'projets' => $settingsManager->getRealisationsProjets($norme,'PROJET'),
            'form' => $form->createView(),
        ]);
    }

    /**
     * @Route("/app/commune/norme/delete/{id}", name="app_commune_norme_delete")
     */
    public function communeDeleteNormeAction($id, Request $request)
    {
        try {
            $norme = $this->getDoctrine()->getRepository(ActiviteNorme::class)->find($id);
            $this->getDoctrine()->getManagerForClass(ActiviteNorme::class)->remove($norme);
            $this->getDoctrine()->getManagerForClass(ActiviteNorme::class)->flush();
        } catch (\Exception $e) {
            $this->addFlash('error', 'Une erreur s\'est produite. Veuillez réssayer ou contacter le développeur');
            return $this->redirectToRoute('app_commune_normes');
        }
        $this->addFlash('success', 'Norme d\'action supprimée avec succès');
        return $this->redirectToRoute('app_commune_normes');
    }

    /**
     * @Route("/app/commune/suivi_indicateur", name="app_commune_suivi_indicateur")
     */
    public function communeSuiviIndicateurAction(Request $request, SettingsManager $settingsManager)
    {
        $sessionYear = $request->getSession()->get('_year');
        $etat = $settingsManager->autorisationSaisieCommune($sessionYear);
        $structureyear = $settingsManager->getStructureYear($sessionYear);
        $goals = $this->getDoctrine()->getRepository(Goal::class)->findAll();
        $commune = $request->getSession()->get('_commune');
        $commune = $this->getDoctrine()->getRepository(Commune::class)->findOneBy(['id' => $commune->getId()]);
        $indicators = [];

        $year = $request->getSession()->get('_year');
        foreach ($goals as $goal) {
            foreach ($goal->getTargets() as $target) {
                foreach ($target->getIndicators() as $indicator) {
                    if ($indicator->isCommunal()) {
                        $indicators[] = $indicator;
                        $suivis[$indicator->getId()]['indicator'] = $indicator;
                        for ($i = 0; $i < 6; ++$i) {
                            //$indicator = $this->getDoctrine()->getRepository(Indicator::class)->findOneBy(['id' => 257]);
                            $suivi = $this->getDoctrine()
                                ->getRepository(IndiceCommune::class)
                                ->findOneBy(['indicator' => $indicator, 'year' => $settingsManager->getYear($year - $i), 'commune' => $commune]);
                            $suivis[$indicator->getId()][$year - $i] = $suivi ? round($suivi->getValue(),3) : '-';
                            if ($i == 0) {
                                $suivis[$indicator->getId()]['observation'] = ($suivi && $suivi->getObservation()) ? $suivi->getObservation() : '';
                                $suivis[$indicator->getId()]['source'] = ($suivi && $suivi->getSource()) ? $suivi->getSource() : '';
                            }
                        }
                    }
                }
            }
        }

        return $this->render('communes/suivi.html.twig', [
            'indicators' => $indicators,
            'suivis' => $suivis,
            'year' => $year,
            'etat' => $etat,
            'year' => $year,
            'structureyear' => $structureyear,
        ]);
    }

    /**
     * @Route("/app/commune/indice/edit/{id}", name="app_commune_indice_edit")
     */
    public function communeIndiceEditAction($id, Request $request, SettingsManager $settingsManager)
    {
        $year = $request->getSession()->get('_year');
        $indicator = $this->getDoctrine()
            ->getRepository(Indicator::class)
            ->findOneBy(['id' => $id]);

        $commune = $request->getSession()->get('_commune');
        $commune = $this->getDoctrine()->getRepository(Commune::class)->findOneBy(['id' => $commune->getId()]);

        $model = new IndiceModel();
        $model->indicator = $indicator;
        $model->maxValue = $indicator->getMaxValue();
        $model->minValue = $indicator->getMinValue();
        $model->refValue = $indicator->getRefValue();
        $model->refYear = $indicator->getRefYear();
        $model->unity = $indicator->getUnity();

        for ($i = 0; $i < 5; ++$i) {
            $etats[$i] = !$settingsManager->autorisationSaisieCommune($year - $i);
            $indice = $this->getDoctrine()
                ->getRepository(IndiceCommune::class)
                ->findOneBy(['indicator' => $indicator->getId(), 'year' => $settingsManager->getYear($year - $i), 'commune' => $commune]);
            if ($indice) {
                if ($i == 0) {
                    $model->recValue = $indice->getValue();
                    $model->observation = $indice->getObservation();
                    $model->source = $indice->getSource();
                }
                if ($i == 1) {
                    $model->value1 = $indice->getValue();
                }
                if ($i == 2) {
                    $model->value2 = $indice->getValue();
                }
                if ($i == 3) {
                    $model->value3 = $indice->getValue();
                }
                if ($i == 4) {
                    $model->value4 = $indice->getValue();
                }
            }
        }

        $form = $this->createForm(SuiviINDModelCommuneType::class, $model, array(
            'etats' => $etats,
        ));

        $form->handleRequest($request);
        if ($form->isSubmitted() && $form->isValid()) {
            $model = $form->getData();
            for ($i = 0; $i < 5; ++$i) {
                $indice = $this->getDoctrine()
                    ->getRepository(IndiceCommune::class)
                    ->findOneBy(['indicator' => $indicator->getId(), 'year' => $settingsManager->getYear($year - $i), 'commune' => $commune]);
                
                if (!$indice) {
                    $indice = new IndiceCommune();
                    $indice->setCommune($commune);
                    $indice->setYear($settingsManager->getYear($year - $i));
                    $indice->setIndicator($indicator);
                    $indice->setMaxValue($indicator->getMaxValue());
                    $indice->setMinValue($indicator->getMinValue());
                    $indice->setRefValue($indicator->getRefValue());
                    $indice->setRefYear($indicator->getRefYear());
                }
                    if ($i == 0) {
                        $indice->setValue($model->recValue);
                        $indice->setObservation($model->observation);
                    }
                    if ($i == 1) {
                        $indice->setValue($model->value1);
                    }
                    if ($i == 2) {
                        $indice->setValue($model->value2);
                    }
                    if ($i == 3) {
                        $indice->setValue($model->value3);
                    }
                    if ($i == 4) {
                        $indice->setValue($model->value4);
                    }

                    $this->getDoctrine()->getManagerForClass(IndiceCommune::class)->persist($indice);
            }
            $this->getDoctrine()->getManagerForClass(IndiceCommune::class)->flush();
            $this->addFlash('success', "Valeur de l'indicateur modifiée avec succès");
            return $this->redirectToRoute('app_commune_suivi_indicateur');
            /*if ($year == $model->refYear) {
                $model->recValue = $model->refValue;
            }
            $indice = $this->getDoctrine()
                ->getRepository(IndiceCommune::class)
                ->findOneBy(['indicator' => $indicator->getId(), 'year' => $year, 'commune' => $commune]);

            if ((($model->recValue >= $model->minValue) and ($model->recValue <= $model->maxValue))
                or (($model->recValue <= $model->minValue) and ($model->recValue >= $model->maxValue))
            ) {

                if ($indice) {
                    $indice->setObservation($model->observation);
                    $this->getDoctrine()->getManagerForClass(Indice::class)->persist($indice);
                    $this->getDoctrine()->getManagerForClass(Indice::class)->flush();
                    $this->addFlash('success', "Modification effectuée avec succès");
                }
            } else {
                $this->addFlash('error', "Impossible d'enregister la valeur puisqu'elle est inférieur à la valeur minimale");
            }*/

            //return $this->redirectToRoute('app_suiviIND', ['id' => $indicator->getTarget()->getGoal()->getID()]);
        }

        return $this->render('communes/suivi_edit.html.twig', [
            'model' => $model,
            'year' => $year,
            'form' => $form->createView(),
        ]);
    }

    /**
     * @Route("/app/commune/graph", name="app_commune_graphe")
     */
    public function communeGraphAction(Request $request, SettingsManager $settingsManager)
    {
        
        $data1 = [];
        $data2 = [];
        $data3 = [];
        $indicator=null;

        $year = $settingsManager->getYear($request->getSession()->get('_year'));
        $commune = $request->getSession()->get('_commune');
        $commune = $this->getDoctrine()->getRepository(Commune::class)->findOneBy(['id' => $commune->getId()]);

        $queryBuilder = $this->getDoctrine()->getManager()->createQueryBuilder();
        $queryBuilder->select(array('i'))
            ->from(Indicator::class, 'i')
            ->andWhere('i.communal = :communal')
            ->orderBy('i.id')
            ->setParameter('communal', true);
        $indicators = $queryBuilder->getQuery()->getScalarResult();
        foreach ($indicators as $ind) {
            $indicator = $this->getDoctrine()->getRepository(Indicator::class)->findOneBy(['id' => $ind['i_id']]);
            $data1['labels'][] = 'Indicateur '.$indicator->getCode();
            $indice_commune = $this->getDoctrine()->getRepository(IndiceCommune::class)->findOneBy(['commune' => $commune, 'year' => $year, 'indicator' => $indicator]);
            $data1['values'][] = $indice_commune ? round($indice_commune->getValue(),3) : 0;
        } 

        $form = $this->createForm(IndicatorCommunalType::class, null, [
        ]);

        $indicator = null;
        $form->handleRequest($request);
        if ($form->isSubmitted() && $form->isValid()) {
            $indicator = $form->get('indicator')->getData();
            if($indicator){

                for ($i=4; $i>=0; $i--) {
                    $data2['labels'][] = $year->getName()-$i;
                    $indice_commune = $this->getDoctrine()->getRepository(IndiceCommune::class)->findOneBy(['commune' => $commune, 'year' => $settingsManager->getYear($year->getName()-$i) , 'indicator' => $indicator]);
                    $data2['values'][] = $indice_commune ? round($indice_commune->getValue(),3) : 0;
                } 

                $departement=$commune->getDepartment();
                foreach ($departement->getCommunes() as $c) {
                    $data3['labels'][] = $c->getName();

                    //Vérifier si les données sont validées par le SE
                    $structureyear = $this->getDoctrine()->getRepository(StructureYear::class)->findOneBy(['commune' => $c, 'year' => $year]);
                    $validation=$structureyear && $structureyear->getDpp2();
                    if($commune->getId()==$c->getId() || $validation){
                        $indice_commune = $this->getDoctrine()->getRepository(IndiceCommune::class)->findOneBy(['commune' => $c, 'year' => $year, 'indicator' => $indicator]);
                        $data3['values'][] = $indice_commune ? round($indice_commune->getValue(),3) : 0;
                    }else{
                        $data3['values'][] = 0;
                    } 
                } 
            }
            
        }

        return $this->render('communes/graph.html.twig', [
            'indicator' => $indicator,
            'data1' => $data1,
            'data2' => $data2,
            'data3' => $data3,
            'form' => $form->createView(),
        ]);
    }

    /**
     * @Route("/app/departement/commune/graph", name="app_departement_commune_graphe")
     */
    public function departementCommuneGraphAction(Request $request, SettingsManager $settingsManager)
    {
        $data1 = [];
        $data2 = [];
        $indicators=null;
        $commune=null;
        $form = $this->createForm(IndicatorCommunalType::class, null, []);

        $year = $settingsManager->getYear($request->getSession()->get('_year'));
        
        $departement = $request->getSession()->get('_departement');
        $departement = $this->getDoctrine()->getRepository(Department::class)->findOneBy(['id' => $departement->getId()]);

        $communes = $departement->getCommunes();


        $form = $this->createForm(CommuneByDepartementSelType::class, null,[
            'departement' => $departement
        ]);

        $form->handleRequest($request);
        if ($form->isSubmitted() && $form->isValid()) {
            $commune = $form->get('commune')->getData();
            if($commune){
                $queryBuilder = $this->getDoctrine()->getManager()->createQueryBuilder();
                $queryBuilder->select(array('i'))
                    ->from(Indicator::class, 'i')
                    ->andWhere('i.communal = :communal')
                    ->orderBy('i.id')
                    ->setParameter('communal', true);
                $indicators = $queryBuilder->getQuery()->getScalarResult();
                foreach ($indicators as $ind) {
                    $indicator = $this->getDoctrine()->getRepository(Indicator::class)->findOneBy(['id' => $ind['i_id']]);
                    $data1['labels'][] = 'Indicateur '.$indicator->getCode();
                    $indice_commune = $this->getDoctrine()->getRepository(IndiceCommune::class)->findOneBy(['commune' => $commune, 'year' => $year, 'indicator' => $indicator]);
                    $data1['values'][] = $indice_commune ? round($indice_commune->getValue(),3) : 0;
                } 

                foreach ($indicators as $ind) {
                    $indicator = $this->getDoctrine()->getRepository(Indicator::class)->findOneBy(['id' => $ind['i_id']]);

                    for ($i=4; $i>=0; $i--) {
                        $data2[$indicator->getId()]['labels'][] = $year->getName()-$i;
                        $indice_commune = $this->getDoctrine()->getRepository(IndiceCommune::class)->findOneBy(['commune' => $commune, 'year' => $settingsManager->getYear($year->getName()-$i) , 'indicator' => $indicator]);
                        $data2[$indicator->getId()]['values'][] = $indice_commune ? round($indice_commune->getValue(),3) : 0;
                    } 
                }

            }
        }

        return $this->render('communes/graphdepartementcommune.html.twig', [
            'indicators' => $indicators,
            'commune' => $commune,
            'data1' => $data1,
            'data2' => $data2,
            'form' => $form->createView(),
        ]);
    }

    /**
     * @Route("/app/departement/graph", name="app_departement_graphe")
     */
    public function departementGraphAction(Request $request, SettingsManager $settingsManager)
    {
        
        $data1 = [];
        $data2 = [];
        $data3 = [];
        $indicator=null;

        $year = $settingsManager->getYear($request->getSession()->get('_year'));
        $departement = $request->getSession()->get('_departement');
        $departement = $this->getDoctrine()->getRepository(Department::class)->findOneBy(['id' => $departement->getId()]);

        $communes = $departement->getCommunes();
        foreach ($communes as $commune) {
            $data1['labels'][] = $commune->getName();
            $data1['values'][] = $settingsManager->getRIndicateursByCommune($year, $commune);
        } 
            
            $data1['labels'][] = "Nombre d'indicateurs";
            $indicateur=3;
            $data1['values'][] = $settingsManager->compter(2030,$indicateur,true);

        $form = $this->createForm(IndicatorCommunalType::class, null, [
        ]);

        $indicator = null;
        $form->handleRequest($request);
        if ($form->isSubmitted() && $form->isValid()) {
            $indicator = $form->get('indicator')->getData();
            if($indicator){

                for ($i=4; $i>=0; $i--) {
                    $data2['labels'][] = $year->getName()-$i;
                    $indice_commune = $this->getDoctrine()->getRepository(IndiceCommune::class)->findOneBy(['commune' => $commune, 'year' => $settingsManager->getYear($year->getName()-$i) , 'indicator' => $indicator]);
                    $data2['values'][] = $indice_commune ? $indice_commune->getValue() : 0;
                } 

                $departement=$commune->getDepartment();
                foreach ($departement->getCommunes() as $c) {
                    $data3['labels'][] = $c->getName();
                    //Vérifier si les données sont validées par le SE
                    $structureyear = $this->getDoctrine()->getRepository(StructureYear::class)->findOneBy(['commune' => $c, 'year' => $year]);
                    $validation=$structureyear && $structureyear->getDpp2();
                    if($commune->getId()==$c->getId() || $validation){
                        $indice_commune = $this->getDoctrine()->getRepository(IndiceCommune::class)->findOneBy(['commune' => $c, 'year' => $year, 'indicator' => $indicator]);
                        $data3['values'][] = $indice_commune ? round($indice_commune->getValue(),3) : 0;
                    }else{
                        $data3['values'][] = 0;
                    } 
                } 
            }
            
        }

        return $this->render('communes/graphdepartement.html.twig', [
            'indicator' => $indicator,
            'data1' => $data1,
            'data2' => $data2,
            'data3' => $data3,
            'form' => $form->createView(),
        ]);
    }

    /**
     * @Route("/app/commune/output", name="app_commune_output")
     */
    public function communeOutputAction(Request $request, SettingsManager $settingsManager)
    {
        
        $indicators = [];
        $suivis = [];
        $commune = 3;
        $year = $request->getSession()->get('_year');
        $desagregation = $this->getDoctrine()->getRepository(Desagregation::class)->findOneBy(['id' => $commune]);
        $modalite = $settingsManager->entityModalite($desagregation, $request->getSession()->get('_commune'));
        
        $form = $this->createForm(GoalOnlySelType::class, null, array(
        ));

        $form->handleRequest($request);
        if ($form->isSubmitted() && $form->isValid()) {
            $goal = $form->get('goal')->getData();

            $goal = $this->getDoctrine()->getRepository(Goal::class)->findOneBy(['id' => $goal]);

            $indicators = [];
            $suivis = [];

            $form = $this->createForm(GoalOnlySelType::class, null, [
                'goal' => $goal,
            ]);

            if ($goal) {
                foreach ($goal->getTargets() as $target) {
                    foreach ($target->getIndicators() as $indicator) {
                        if($indicator->getDesagregations() && in_array($commune, $indicator->getDesagregations())){
                        $indicators[] = $indicator;
                        $suivis[$indicator->getId()]['indicator'] = $indicator;
                        for ($i = 0; $i < 5; ++$i) {
                            $suivi = $this->getDoctrine()
                            ->getRepository(Indice::class)
                            ->findOneBy(['indicator' => $indicator->getId(), 'year' => $year - $i, 'desagregation' => $desagregation, 'modalite' => $modalite]);
                            $suivis[$indicator->getId()][$year - $i] = $suivi ? $suivi->getValue() : '-';
                            if ($i == 0) {
                                $suivis[$indicator->getId()]['observation'] = $suivi ? $suivi->getObservation() : '';
                            }
                        }
                    }
                    }
                }
            }
        }
            return $this->render('communes/output.html.twig', [
            'form' => $form->createView(),
            'suivis' => $suivis,
            'indicators' => $indicators,
            'year' => $year,
        ]);
    }


    /**
     * @Route("/app/commune/normes_action/{id}", name="app_normes_actions", defaults={"id"=0})
     */
    public function communeNormesActionAction($id=0, Request $request, SettingsManager $settingsManager)
    {
        $id= $id==0 ? $request->getSession()->get('_commune') : $id;
        $commune = $this->getDoctrine()->getRepository(Commune::class)->findOneBy(['id' => $id]);
        $year = $request->getSession()->get('_year');

        return $this->render('communes/normes_actions_list.html.twig', [
            'commune' => $commune,
            'year'=> $year,
            'normes'=> $commune->getNormeActionCommunes(),
        ]);
    }

    /**
     * @Route("/app/commune/normes_action/new/{id}/{t}", name="app_normes_actions_new")
     */
    public function communeNormesActionNewAction($id, $t=0, Request $request, SettingsManager $settingsManager)
    {
        $commune = $this->getDoctrine()->getRepository(Commune::class)->findOneBy(['id' => $id]);
        $year = $request->getSession()->get('_year');

        $norme = new NormeActionCommune();

        $target = $this->getDoctrine()->getRepository(Target::class)->findOneBy(['id' => $t]);
        $actions=$this->getDoctrine()->getRepository(NormeActionCommune::class)->findBy(['commune' => $commune, 'target' => $target]);

        $form = $this->createForm(NormeActionCommuneType::class, $norme,[
            'targets' => $settingsManager->getCiblesSpacialisees($commune),
            'target' => $target
        ]);

        $form->handleRequest($request);
        if ($form->isSubmitted() && $form->isValid()) {
            $norme = $form->getData();
            if($request->get('save')){
                $this->getDoctrine()->getRepository(ActivityNormeCommune::class)->deleteAll($norme->getTarget()->getId());
                $goal = $norme->getTarget()->getGoal();
                $i=1;

                foreach ($request->get('normes') as $n) {
                    $normeModel = new NormeActionCommune();
                    $normeModel->setTarget($norme->getTarget());
                    $normeModel->setName($n['name']);
                    $normeModel->setCommune($commune);

                    $codegoal=$goal->getId()<10 ? '0'.$goal->getId() : $goal->getId();
                    $codecommune=$commune->getId()<10 ? '0'.$commune->getId() : $commune->getId();
                    $codetarget=$norme->getTarget()->getId()<10 ? '0'.$norme->getTarget()->getId() : $norme->getTarget()->getId();
                    $codei=$i<10 ? '0'.$i : $i;
                    $normeModel->setCode('N'.$codegoal.$codetarget.$codecommune.$codei);
                    $this->getDoctrine()->getManager()->persist($normeModel);
                    $i++;
                }

                $this->getDoctrine()->getManager()->flush();
                $this->addFlash('success', "Normes d'action ajoutées avec succès");
                return $this->redirectToRoute('app_normes_actions', ['id' => $commune->getId()]);

            }else{
                $actions=$this->getDoctrine()->getRepository(NormeActionCommune::class)->findBy(['commune' => $commune, 'target' => $norme->getTarget()]);
            }
        }

        return $this->render('communes/normes_actions_form.html.twig', [
            'form' => $form->createView(),
            'commune' => $commune,
            'year'=> $year,
            'actions'=> $actions,
        ]);
    }

    /**
     * @Route("/app/commune/chargement/actions", name="app_commune_chargement_actions")
     */
    public function communeChargementActionsAction(Request $request, SettingsManager $settingsManager)
    {
        $etat = $settingsManager->autorisationSaisieCommune($request->getSession()->get('_year'));
        $year = $request->getSession()->get('_year');

        $commune = $request->getSession()->get('_commune');
        $commune = $this->getDoctrine()->getRepository(Commune::class)->findOneBy(['id' => $commune->getId()]);

        $activities=$this->getDoctrine()->getRepository(ActivityNormeCommune::class)->findBy(['commune' => $commune, 'year' => $year]);


        return $this->render('communes/chargement_actions.html.twig', [
            'etat' => $etat,
            'year' => $year,
            'activities' => $activities,
            'commune' => $commune,
        ]);
    }

    /**
     * @Route("/app/commune/actions/format", name="app_commune_actions_format")
     
     */
    //* @IsGranted({"ROLE_FOCAL"})
    public function formatCommuneActionsAction(Request $request, SettingsManager $settingsManager)
    {
        $annee = $request->getSession()->get('_year');
        $commune = $request->getSession()->get('_commune');
        $commune = $this->getDoctrine()->getRepository(Commune::class)->findOneBy(['id' => $commune->getId()]);
        $fileName = 'Normes-Actions-'.$commune->getName().'-'. $annee . '.xlsx';
        $maquette = $this->getParameter('kernel.project_dir') . '/public/upload/communes/normes.xlsx';

        $spreadsheet = IOFactory::load($maquette);

        $sheet = $spreadsheet->getSheet(1);
        $odds = $this->getDoctrine()->getRepository(Goal::class)->findBy(['agenda' => 2030]);
        $line = 2;
        foreach ($odds as $odd) {
			foreach ($odd->getTargets() as $target){
                $actions=$this->getDoctrine()->getRepository(NormeActionCommune::class)->findBy(['commune' => $commune, 'target' => $target]);
                foreach ($actions as $action){
                    $sheet->setCellValue('A' . $line, 'ODD '.$odd->getCode().' ');
                    $sheet->setCellValue('B' . $line, 'Cible '.$target->getCode());
                    $sheet->setCellValue('C' . $line, $action->getName());
                    $sheet->setCellValue('D' . $line, $action->getCode());
                    $line++;
                }
            }
        }

        //Retour sur la feuille accueil
        $sheet = $spreadsheet->setActiveSheetIndex(0);

        //Télécharchement du fichier
        $writer = new Xlsx($spreadsheet);
        $response =  new StreamedResponse(
            function () use ($writer) {
                $writer->save('php://output');
            }
        );
        $response->headers->set('Content-Type', 'application/vnd.ms-excel');
        $response->headers->set('Content-Disposition', 'attachment;filename="' . $fileName . '"');
        $response->headers->set('Cache-Control', 'max-age=0');
        return $response;
    }

    /**
     * @Route("/app/commune/actions/import", name="app_commune_actions_import")
     
     */
    //* @IsGranted({"ROLE_FOCAL"})
    public function importCommuneActionsAction(Request $request, SettingsManager $settingsManager)
    {
        $annee = $request->getSession()->get('_year');
        $commune = $request->getSession()->get('_commune');
        $commune = $this->getDoctrine()->getRepository(Commune::class)->findOneBy(['id' => $commune->getId()]);

        $file = $request->files->get('fichier');
        if (!empty($file)) {
            //Importation des données
            $fileok=true;
            $spreadsheet = IOFactory::load($file);
            $sheet = $spreadsheet->setActiveSheetIndex(0);
            for ($i = 2; $i <= $sheet->getHighestDataRow(); $i++) {
                $code = $sheet->getCell('A' . $i)->getCalculatedValue();
                if($code !=""){
                    $norme=$this->getDoctrine()->getRepository(NormeActionCommune::class)->findOneBy(['code' => $code]);
                    if(! $norme){
                        $this->addFlash('error', "Erreur d'importation. Veuillez vérifier le code de la norme d'action de la ligne ".$i);
                        $fileok=false;
                    }else{
                        $action = new ActivityNormeCommune();
                        $action->setCommune($commune);
                        $action->setNorme($norme);
                        $action->setYear($annee);
                        $action->setAction(trim($sheet->getCell('B' . $i)->getCalculatedValue()));
                        $action->setPrevu($sheet->getCell('C' . $i)->getCalculatedValue());
                        $action->setRealise($sheet->getCell('D' . $i)->getCalculatedValue());

                        $t=$sheet->getCell('E' . $i)->getCalculatedValue();
                        if ($t == "#DIV/0!" || $t == "#REF!" || $t == "#VALEUR!" || $t == "") {
                        }else{
                            $action->setTef(round($t*100,2));
                        }
                        $action->setContraintes(trim($sheet->getCell('F' . $i)->getCalculatedValue()));
                        $action->setObservation(trim($sheet->getCell('G' . $i)->getCalculatedValue()));
                        $this->getDoctrine()->getManager()->persist($action);
                    }
                } 
            }
            if($fileok){
                $this->getDoctrine()->getManager()->flush();
                $this->addFlash('success', "Actions ajoutées avec succès");
            }
        }

        return $this->redirect($request->headers->get('referer'));

    }

    /**
     * @Route("/app/commune/action/new/", name="app_actions_new")
     */
    public function communeActionNewAction(Request $request, SettingsManager $settingsManager)
    {
        $commune = $request->getSession()->get('_commune');
        $commune = $this->getDoctrine()->getRepository(Commune::class)->findOneBy(['id' => $commune->getId()]);
        $year = $request->getSession()->get('_year');

        $norme=new ActivityNormeCommune();

        $form = $this->createForm(ActivityNormeCommuneType::class, $norme,[
            'commune' => $commune
        ]);

        $form->handleRequest($request);
        if ($form->isSubmitted() && $form->isValid()) {
            $norme = $form->getData();

            $norme->setYear($year);
            $norme->setCommune($commune);

            $this->getDoctrine()->getManagerForClass(ActivityNormeCommune::class)->persist($norme);
            $this->getDoctrine()->getManagerForClass(ActivityNormeCommune::class)->flush();
            $this->addFlash('success', 'Action prioritaire ajoutée avec succès');
            return $this->redirectToRoute('app_actions_new');
        }

        return $this->render('communes/actions_form.html.twig', [
            'form' => $form->createView(),
            'commune' => $commune,
        ]);

    }


    /**
     * @Route("/app/commune/action/edit/{id}/", name="app_actions_edit")
     */
    public function communeActionEditAction($id, Request $request, SettingsManager $settingsManager)
    {
        $commune = $this->getDoctrine()->getRepository(Commune::class)->findOneBy(['id' => $id]);
        $year = $request->getSession()->get('_year');

        $norme = $this->getDoctrine()->getRepository(ActivityNormeCommune::class)->findOneBy(['id' => $id]);

        $form = $this->createForm(ActivityNormeCommuneType::class, $norme,[
            'commune' => $commune
        ]);

        $form->handleRequest($request);
        if ($form->isSubmitted() && $form->isValid()) {
            $norme = $form->getData();

            $this->getDoctrine()->getManagerForClass(ActivityNormeCommune::class)->persist($norme);
            $this->getDoctrine()->getManagerForClass(ActivityNormeCommune::class)->flush();
            $this->addFlash('success', 'Action prioritaire modifiée avec succès');
            return $this->redirectToRoute('app_commune_chargement_actions');
        }

        return $this->render('communes/actions_form.html.twig', [
            'form' => $form->createView(),
            'commune' => $commune,
        ]);
    }

    /**
     * @Route("/commune/export/{id}", name="export_commune")
     */
    public function download_commune($id, SettingsManager $settingsManager, Request $request)
    {
        $year = $request->getSession()->get('_year');
        $commune = $this->getDoctrine()->getRepository(Commune::class)->findOneBy(['id' => $id]);

        $maquette= $this->getParameter('kernel.project_dir') . '/public/upload/communes/download.xlsx';
        //Chargement de la maquette de base
        $spreadsheet = IOFactory::load($maquette);

        $sheet = $spreadsheet->getSheet(0);
        $sheet->setCellValue('C3', $year-4);
        $sheet->setCellValue('D3', $year-3);
        $sheet->setCellValue('E3', $year-2);
        $sheet->setCellValue('F3', $year-1);
        $sheet->setCellValue('G3', $year);

        $queryBuilder = $this->getDoctrine()->getManager()->createQueryBuilder();
        $queryBuilder->select(array('i'))
            ->from(Indicator::class, 'i')
            ->andWhere('i.communal = :communal')
            ->orderBy('i.id')
            ->setParameter('communal', true);
        $indicators = $queryBuilder->getQuery()->getScalarResult();

        $line=4;
        foreach ($indicators as $indicator) {
            $sheet->setCellValue('A1', $commune->getName().' - '.$year);
            $sheet->setCellValue('A'.$line, $indicator['i_code']);
            $sheet->setCellValue('B'.$line, $indicator['i_name']);
            $sheet->setCellValue('C'.$line, $settingsManager->getIndiceCommune($commune, $settingsManager->getIndicateur($indicator['i_id']) , $settingsManager->getYear($year-4)));
            $sheet->setCellValue('D'.$line, $settingsManager->getIndiceCommune($commune, $settingsManager->getIndicateur($indicator['i_id']) , $settingsManager->getYear($year-3)));
            $sheet->setCellValue('E'.$line, $settingsManager->getIndiceCommune($commune, $settingsManager->getIndicateur($indicator['i_id']) , $settingsManager->getYear($year-2)));
            $sheet->setCellValue('F'.$line, $settingsManager->getIndiceCommune($commune, $settingsManager->getIndicateur($indicator['i_id']) , $settingsManager->getYear($year-1)));
            $sheet->setCellValue('G'.$line, $settingsManager->getIndiceCommune($commune, $settingsManager->getIndicateur($indicator['i_id']) , $settingsManager->getYear($year)));
            $sheet->setCellValue('H'.$line, $settingsManager->getIndiceCommuneObservation($commune, $settingsManager->getIndicateur($indicator['i_id']) , $settingsManager->getYear($year)));
            $line++;
        }

        //Télécharchement du fichier
        $fileName=$commune->getName().'-ODD-'.$year.'.xlsx';
        $writer = new Xlsx($spreadsheet);
        $response =  new StreamedResponse(
            function () use ($writer) {
                $writer->save('php://output');
            }
        );
        $response->headers->set('Content-Type', 'application/vnd.ms-excel');
        $response->headers->set('Content-Disposition', 'attachment;filename="'.$fileName.'"');
        $response->headers->set('Cache-Control','max-age=0');
        return $response;
    }

    /**
     * @Route("/commune/annoter/{id}", name="annoter_commune")
     */
    public function annoter_commune($id, SettingsManager $settingsManager, Request $request)
    {
        $year = $request->getSession()->get('_year');
        $commune = $this->getDoctrine()->getRepository(Commune::class)->findOneBy(['id' => $id]);
        $goals = $this->getDoctrine()->getRepository(Goal::class)->findAll();

        $indicators = [];
        foreach ($goals as $goal) {
            foreach ($goal->getTargets() as $target) {
                foreach ($target->getIndicators() as $indicator) {
                    if ($indicator->isCommunal()) {
                        $indicators[] = $indicator;
                        $suivis[$indicator->getId()]['indicator'] = $indicator;
                            $suivi = $this->getDoctrine()
                                ->getRepository(IndiceCommune::class)
                                ->findOneBy(['indicator' => $indicator, 'year' => $settingsManager->getYear($year), 'commune' => $commune]);
                            $suivis[$indicator->getId()] = $suivi ? round($suivi->getValue(),3) : '-';
                
                }
            }
        }
    }

    $structure=$settingsManager->getStructureYear($year, null, $commune);
    $etat = !($structure && $structure->getDpp2());
    $avis = $structure ? $structure->getAvis() : '';
    $form = $this->createForm(AnnoterCommuneType::class, $structure, array('avis' => $avis,  'etat' => $etat
    ));

    $form->handleRequest($request);
    if ($form->isSubmitted() && $form->isValid()) {
        
        $avis = $form->get('avis')->getData();
        $observation = $form->get('observation')->getData();
        $structure->setAvis($avis);
        $structure->setObservation($observation);
        $this->getDoctrine()->getManagerForClass(StructureYear::class)->flush();
        
            return $this->redirectToRoute('app_departement_dashboard');
        
    }  
        return $this->render('communes/annoter.html.twig', [
            'commune' => $commune,
            'etat' => $etat,
            'structure' => $structure,
            'year' => $year,
            'indicators' => $indicators,
            'suivis' => $suivis,
            'form' => $form->createView(),
        ]);
    }

    /**
     * @Route("/app/commune/action/{id}/delete", name="app_commune_action_delete")
     * 
     */
    public function communeActionDeleteAction($id, Request $request)
    {
        try {
            $indicator = $this->getDoctrine()->getRepository(ActivityNormeCommune::class)->find($id);
            $this->getDoctrine()->getManagerForClass(ActivityNormeCommune::class)->remove($indicator);
            $this->getDoctrine()->getManagerForClass(ActivityNormeCommune::class)->flush();
        } catch (\Exception $e) {
            $this->addFlash('error', 'Une erreur s\'est produite. Veuillez réssayer ou contacter le développeur');
            return $this->redirectToRoute('app_indicators');
        }
        $this->addFlash('success', 'Action prioritaire supprimée avec succès');
        return $this->redirectToRoute('app_commune_chargement_actions');
    }

     /**
     * @Route("/app/commune/importxlxnormes", name="app_commune_importxlxnormes")
     */
     public function importxlxnormes(){

        //$maquette = $this->getParameter('kernel.project_dir') . '/public/upload/normes/dep1.xlsx';
        //$n=13;
        //$maquette = $this->getParameter('kernel.project_dir') . '/public/upload/normes/dep2.xlsx';
        //$n=9;
        //$maquette = $this->getParameter('kernel.project_dir') . '/public/upload/normes/dep3.xlsx';
        //$n=14;
        //$maquette = $this->getParameter('kernel.project_dir') . '/public/upload/normes/dep4.xlsx';
        //$n=12;
        //$maquette = $this->getParameter('kernel.project_dir') . '/public/upload/normes/dep5.xlsx';
        //$n=14;

        
        /*$maquette = $this->getParameter('kernel.project_dir') . '/public/upload/normes/dep6.xlsx';
        $n=14;*/

        $spreadsheet = IOFactory::load($maquette);
        for ($i = 0; $i <$n; $i++) {
            $sheet = $spreadsheet->getSheet($i);
            $commune = $this->getDoctrine()->getRepository(Commune::class)->findOneBy(['name' => $sheet->getTitle()]);
            if(!$commune) dd($sheet->getTitle());
            $k=1;
            for ($j = 2; $j <= $sheet->getHighestDataRow(); $j++) {
                if($sheet->getCell('A' . $j)->getCalculatedValue()){
                $target = $this->getDoctrine()->getRepository(Target::class)->findOneBy(['code' => $sheet->getCell('A' . $j)->getCalculatedValue()]);
                if($target){
                    if($sheet->getCell('C' . $j)->getCalculatedValue()){
                        $goal=$target->getGoal();
                        $normeModel = new NormeActionCommune();
                        $normeModel->setTarget($target);
                        $normeModel->setName($sheet->getCell('C' . $j)->getCalculatedValue());
                        $normeModel->setCommune($commune);

                        $codegoal=$goal->getId()<10 ? '0'.$goal->getId() : $goal->getId();
                        $codecommune=$commune->getId()<10 ? '0'.$commune->getId() : $commune->getId();
                        $codetarget=$target->getId()<10 ? '0'.$target->getId() : $target->getId();
                        $codei=$k<10 ? '0'.$k : $k;
                        $normeModel->setCode('N'.$codegoal.$codetarget.$codecommune.$codei);
                        $this->getDoctrine()->getManager()->persist($normeModel);
                        $k++;
                    }

                }else{
                    dd($sheet->getTitle() . " ".$j);
                }
                }
            }
        }
        $this->getDoctrine()->getManager()->flush();
        dd('Importation réussie');
    }
}