<?php

namespace App\Controller;

use App\Entity\Commune;
use App\Entity\Department;
use App\Entity\Desagregation;
use App\Entity\Goal;
use App\Entity\Indicator;
use App\Entity\Indice;
use App\Entity\Ministry;
use App\Entity\Modalite;
use App\Entity\Statut;
use App\Entity\Trend;
use App\Entity\Zone;
use App\Form\Indicator2Type;
use App\Form\GoalSelType;
use App\Form\GoalSelNewType;
use App\Form\GoalOnlySelNewType;
use App\Form\MinistryOnlySelNewType;
//use App\Form\ActivityType;
use App\Form\GoalOnlySelType;
use App\Form\IndicatorSelType;
use App\Form\SuiviINDModelType;
use App\Form\SuiviINDModelNewType;
//use App\Form\IndiceCollectionModelType;
use App\Form\Model\IndiceModel;
//use App\Form\Model\IndiceCollectionModel;
use App\Utils\YearsUtils;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\Routing\Annotation\Route;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\DependencyInjection\ParameterBag\ParameterBagInterface;
use Sensio\Bundle\FrameworkExtraBundle\Configuration\IsGranted;
use App\Service\SettingsManager;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use Symfony\Component\HttpFoundation\StreamedResponse;
use App\Entity\StructureYear;


class SuiviINDController extends AbstractController
{
    private $params;
    private $settingsManager;

    public function __construct(ParameterBagInterface $params, SettingsManager $settingsManager)
    {
        $this->params = $params;
        $this->settingsManager = $settingsManager;
    }

    function getIndicatorValue($year, $indicator, $flag, $desagregation, $modalite)
    {
        $indice = $this->getDoctrine()->getRepository(Indice::class)
            ->getRecIndice($year, $indicator, $desagregation, $modalite);
        if ($indice) {
            switch ($flag) {
                case 'maxValue':
                    return $indice->getMaxValue();
                    break;
                case 'minValue':
                    return $indice->getMinValue();
                    break;
                case 'refYear':
                    return $indice->getRefYear();
                    break;
                case 'refValue':
                    return $indice->getRefValue();
                    break;
                case 'recYear':
                    return $indice->getYear();
                    break;
                case 'recValue':
                    return $indice->getValue();
                    break;
                default:
                    return null;
            }
        }
    }

    function getIndicatorTrend($year, $indicator)
    {
        $indice = $this->getDoctrine()->getRepository(Indice::class)
            ->getRecIndice($year, $indicator, 1, null);
        $result = null;
        if ($indice and $indice->getRefYear() and ($indice->getYear() != $indice->getRefYear())) {
            $actualTrend = pow($indice->getValue() / $indice->getRefValue(), 1 / ($indice->getYear() - $indice->getRefYear())) - 1;
            $normalTrend = pow($indice->getMaxValue() / $indice->getRefValue(), 1 / (2030 - $indice->getRefYear())) - 1;
            $val = $normalTrend ? ($actualTrend / $normalTrend * 100) : 9999;

            //Normalisation de la valeur
            if ($val <= -200) {
                $result = 0;
            }
            if (($val > -200) && ($val <= 0)) {
                $result = ((($val + 200) / (0 + 200)) * (1 - 0)) + 0;
            }
            if (($val > 0) && ($val <= 50)) {
                $result = ((($val - 0) / (50 - 0)) * (2 - 1)) + 1;
            }
            if (($val > 50) && ($val <= 100)) {
                $result = ((($val - 50) / (100 - 50)) * (3 - 2)) + 2;
            }
            if (($val > 100) && ($val <= 200)) {
                $result = ((($val - 100) / (200 - 100)) * (4 - 3)) + 3;
            }
            if ($val > 200) {
                $result = 4;
            }

            //$result = $val;
        }

        return $result;
    }

    function getTargetTrend($year, $target)
    {
        $nbre = 0;
        $total = 0;
        foreach ($target->getIndicators() as $indicator) {
            $indicatorTrend = $this->getIndicatorTrend($year, $indicator);
            if ($indicatorTrend) {
                $total += $indicatorTrend;
                $nbre += 1;
            }
        }
        return $nbre ? ($total / $nbre) : null;
    }

    function getGoalTrend($year, $goal)
    {
        $nbre = 0;
        $total = 0;
        foreach ($goal->getTargets() as $target) {
            $targetTrend = $this->getTargetTrend($year, $target);
            if ($targetTrend) {
                $total += $targetTrend;
                $nbre += 1;
            }
        }
        return $nbre ? ($total / $nbre) : null;
    }

    function getGlobalTrend($year, $goals)
    {
        $nbre = 0;
        $total = 0;
        foreach ($goals as $goal) {
            $goalTrend = $this->getGoalTrend($year, $goal);
            if ($goalTrend) {
                $total += $goalTrend;
                $nbre += 1;
            }
        }
        return $nbre ? ($total / $nbre) : null;
    }

    function getIndicatorIndice($year, $indicator, $flag = 'rec')
    {
        $result = null;
        $indice = $this->getDoctrine()->getRepository(Indice::class)
            ->getRecIndice($year, $indicator, 1, null);


        if ($indice and ($indice->getMaxValue() - $indice->getMinValue())) {
            if ($flag == 'rec') {
                $result = ($indice->getValue() - $indice->getMinValue()) / ($indice->getMaxValue() - $indice->getMinValue()) * 100;
            } else {
                $result = ($indicator->getRefValue() - $indice->getMinValue()) / ($indice->getMaxValue() - $indice->getMinValue()) * 100;
            }
        }

        return $result;
    }

    function getTargetIndice($year, $target, $flag = 'rec', $moyenne=null)
    {
        $moyenne = $moyenne ? $moyenne : $this->settingsManager->getYear($year)->getMoyenne();
        if($moyenne=='GEOMETRIQUE'){
            $nbre = 0;
            $total = 1;
            foreach ($target->getIndicators() as $indicator) {
                $indicatorIndice = $this->getIndicatorIndice($year, $indicator, $flag);
                if ($indicatorIndice) {
                    $total *= $indicatorIndice;
                    $nbre += 1;
                }
            }
            return $nbre ? pow($total, 1 / $nbre) : null;
        }elseif($moyenne=='ARITHMETIQUE'){
            $nbre = 0;
            $total = 0;
            foreach ($target->getIndicators() as $indicator) {
                $indicatorIndice = $this->getIndicatorIndice($year, $indicator, $flag);
                if ($indicatorIndice) {
                    $total += $indicatorIndice;
                    $nbre += 1;
                }
            }
            return $nbre ? $total/ $nbre : null;
        }else{
            return null;
        }
    }

    function getGoalIndice($year, $goal, $flag = 'rec', $moyenne=null)
    {
        $moyenne = $moyenne ? $moyenne : $this->settingsManager->getYear($year)->getMoyenne();
        if($moyenne=='GEOMETRIQUE'){
            $nbre = 0;
            $total = 1;
            foreach ($goal->getTargets() as $target) {
                $targetIndice = $this->getTargetIndice($year, $target, $flag);
                if ($targetIndice) {
                    $total *= $targetIndice;
                    $nbre += 1;
                }
            }
            return $nbre ? pow($total, 1 / $nbre) : null;
        }elseif($moyenne=='ARITHMETIQUE'){
            $nbre = 0;
            $total = 0;
            foreach ($goal->getTargets() as $target) {
                $targetIndice = $this->getTargetIndice($year, $target, $flag);
                if ($targetIndice) {
                    $total += $targetIndice;
                    $nbre += 1;
                }
            }
            return $nbre ? $total/$nbre : null;
        }else{
            return null;
        }
    }

    function getGlobalIndice($year, $goals, $flag = 'rec', $moyenne=null)
    {
        $moyenne = $moyenne ? $moyenne : $this->settingsManager->getYear($year)->getMoyenne();
        if($moyenne=='GEOMETRIQUE'){
            $nbre = 0;
            $total = 1;
            foreach ($goals as $goal) {
                $goalIndice = $this->getGoalIndice($year, $goal, $flag);
                if ($goalIndice) {
                    $total *= $goalIndice;
                    $nbre += 1;
                }
            }
            return $nbre ? pow($total, 1 / $nbre) : null;
        }elseif($moyenne=='ARITHMETIQUE'){
            $nbre = 0;
            $total = 0;
            foreach ($goals as $goal) {
                $goalIndice = $this->getGoalIndice($year, $goal, $flag);
                if ($goalIndice) {
                    $total += $goalIndice;
                    $nbre += 1;
                }
            }
            return $nbre ?$total/$nbre: null;
        }else{
            return null;
        }
    }

    function normalizerStatut($value)
    {
        //Normalisation de la valeur    
        if (($value > 0) && ($value <= 40)) return ((($value - 0) / (40 - 0)) * (1 - 0)) + 0;
        if (($value > 40) && ($value <= 60)) return ((($value - 40) / (60 - 40)) * (1.5 - 1)) + 1;
        if (($value > 60) && ($value <= 80)) return ((($value - 60) / (80 - 60)) * (2 - 1.5)) + 1.5;
        if (($value > 80) && ($value <= 100)) return ((($value - 80) / (100 - 60)) * (3 - 2)) + 2;
    }

    function adjustStatut($statuts)
    {
        $result = null;
        if (count($statuts) == 1) {
            $result = $statuts[0];
        } elseif (count($statuts) > 1) {
            sort($statuts);
            $result = ($statuts[0] + $statuts[1]) / 2;
            //Condition particulière
            if (($statuts[0] > 2) and ($statuts[1] > 2)) {
                $result = 3;
            } elseif (($statuts[0] > 1.5) or ($statuts[1] > 1.5)) {
                $result = 2;
            }

            if (($statuts[0] <= 1) and ($statuts[1] <= 1)) {
                $result = 1;
            } elseif (($statuts[0] <= 1.5) or ($statuts[1] <= 1.5)) {
                $result = 1.5;
            }
        }

        return $result;
    }

    function getIndicatorStatut($year, $indicator)
    {
        $result = null;
        if ($this->getIndicatorIndice($year, $indicator, 'rec')) {
            $result = $this->normalizerStatut($this->getIndicatorIndice($year, $indicator, 'rec'));
        }
        return $result;
    }

    function getTargetStatut($year, $target)
    {
        $result = null;
        $arrayStatut = [];
        foreach ($target->getIndicators() as $indicator) {
            $indicatorStatut = $this->getIndicatorStatut($year, $indicator);
            if ($indicatorStatut) {
                $arrayStatut[] = $indicatorStatut;
            }
        }
        $result = $this->adjustStatut($arrayStatut);

        return $result;
    }

    function getGoalStatut($year, $goal)
    {
        $result = null;
        $arrayStatut = [];
        foreach ($goal->getTargets() as $target) {
            $targetStatut = $this->getTargetStatut($year, $target);
            if ($targetStatut) {
                $arrayStatut[] = $targetStatut;
            }
        }
        $result = $this->adjustStatut($arrayStatut);

        return $result;
    }

    function getGlobalStatut($year, $goals)
    {
        $result = null;
        $arrayStatut = [];
        foreach ($goals as $goal) {
            $goalStatut = $this->getGoalStatut($year, $goal);
            if ($goalStatut) {
                $arrayStatut[] = $goalStatut;
            }
        }
        $result = $this->adjustStatut($arrayStatut);

        return $result;
    }

    /**
     * @Route("/app/suiviIND/indicators", name="app_suiviIND_indicators")
     */
    public function indicatorsSuiviINDAction()
    {
        $indicators = $this->getDoctrine()->getRepository(Indicator::class)->findBy(['communal' => false]);

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

    /**
     * @Route("/app/suiviIND/indicators/{id}", name="app_suiviIND_indicator")
     
     */
    //* @IsGranted({"ROLE_ADMIN", "ROLE_MANAGER"})
    public function indicatorSuiviINDAction($id, Request $request)
    {
        $startYear = $this->params->get('suiodd_year');

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

        $form = $this->createForm(Indicator2Type::class, $indicator, [
            'choices' => YearsUtils::choicesYears($startYear),
        ]);

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

            $this->getDoctrine()->getManagerForClass(Indicator::class)->flush();

            return $this->redirectToRoute('app_suiviIND_indicators');
        }

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

    /**
     * @Route("/app/suiviIND/",  name="app_suiviIND")
     * @Route("/app/suiviIND/{id}", name="app_suiviIND", defaults={"id" = "0"})
     */
    //* @IsGranted({"ROLE_ADMIN", "ROLE_MANAGER"})
    public function suiviIndicateurAction($id = 0, Request $request, SettingsManager $settingsManager)
    {

        $request->getSession()->set('_modalite', null);
        $request->getSession()->set('_desagregation', 1);
        if ($this->isGranted('ROLE_MANAGER')) {
            $collection = $request->request->get('goal_only_sel');
            $postId = isset($collection['goal']) ? $collection['goal'] : null;
            if ($postId and ($postId != $id)) {
                return $this->redirectToRoute('app_suiviIND', ['id' => $postId]);
            }

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

            $year = $request->getSession()->get('_year');
            $indicators = [];
            $suivis = [];

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

            if ($goal) {
                foreach ($goal->getTargets() as $target) {
                    foreach ($target->getIndicators() as $indicator) {
                        $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]);
                            $suivis[$indicator->getId()][$year - $i] = $suivi ? $suivi->getValue() : '-';
                            if ($i == 0) {
                                $suivis[$indicator->getId()]['observation'] = $suivi ? $suivi->getObservation() : '';
                            }
                        }
                    }
                }
            }
        } else if ($this->isGranted('ROLE_INSTAD')) {
            $year = $request->getSession()->get('_year');
            $indicators = [];
            $suivis = [];
            $indicators = $this->getDoctrine()->getRepository(Indicator::class)->findBy(['instad' => true]);
            foreach ($indicators as $indicator) {
                $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]);
                    $suivis[$indicator->getId()][$year - $i] = $suivi ? $suivi->getValue() : '-';
                    if ($i == 0) {
                        $suivis[$indicator->getId()]['observation'] = $suivi ? $suivi->getObservation() : '';
                    }
                }
            }

            $form = $this->createForm(GoalOnlySelType::class, null, []);
        } else {
            $year = $request->getSession()->get('_year');
            $indicators = [];
            $suivis = [];
            $ministry = $request->getSession()->get('_ministry');
            $ministry = $this->getDoctrine()->getRepository(Ministry::class)->findOneBy(['id' => $ministry->getId()]);
            foreach ($ministry->getIndicators() as $indicator) {
                $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]);
                    $suivis[$indicator->getId()][$year - $i] = $suivi ? $suivi->getValue() : '-';
                    if ($i == 0) {
                        $suivis[$indicator->getId()]['observation'] = $suivi ? $suivi->getObservation() : '';
                    }
                }
            }

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

        return $this->render('suivi_ind/suivi_list.html.twig', [
            'indicators' => $indicators,
            'suivis' => $suivis,
            'year' => $year,
            'form' => $form->createView(),
            'year' => $year,
            'etat' => $settingsManager->autorisationSaisie($request->getSession()->get('_year')),
        ]);
    }


    /**
     * @Route("/app/indice/edit/{id}", name="app_indice_edit")
     */
    //* @IsGranted({"ROLE_ADMIN", "ROLE_MANAGER"})
    public function editIndiceAction($id, Request $request, SettingsManager $settingsManager)
    {
        $national = 1;
        $departement = 2;
        $commune = 3;
        $zone = 4;
        $sexe = 5;

        $year = $request->getSession()->get('_year');
        $indicator = $this->getDoctrine()->getRepository(Indicator::class)->findOneBy(['id' => $id]);
        $desagregation = $this->getDoctrine()->getRepository(Desagregation::class)->findOneBy(['id' => $request->getSession()->get('_desagregation')]);
        $modalite = $settingsManager->entityModalite($desagregation, $request->getSession()->get('_modalite'));
        $show = false;
        $etats = [false, false, false, false, false];
        $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();

        $desagregationsArray = $indicator->getDesagregations();
        $desagregations = array();
        if ($desagregationsArray) {
            sort($desagregationsArray);
            foreach ($desagregationsArray as $d) {
                $desagregations[] = $this->getDoctrine()->getRepository(Desagregation::class)->findOneBy(['id' => $d]);
            }
        } else {
            $desagregations[] = $this->getDoctrine()->getRepository(Desagregation::class)->findOneBy(['id' => $national]);
        }
        $collection = $request->request->get('goal_only_sel_new');
        if ($collection) {
            $desagregation = isset($collection['desagregation']) ? $this->getDoctrine()->getRepository(Desagregation::class)->findOneBy(['id' => $collection['desagregation']])  : null;
            if ($request->getSession()->get('_desagregation') == $collection['desagregation']) {
                $modalite = isset($collection['modalite']) ? $collection['modalite'] : null;
                $request->getSession()->set('_modalite', $modalite);
                $modalite = $settingsManager->entityModalite($desagregation, $modalite);
            } else {
                $modalite = null;
                $request->getSession()->set('_desagregation', $collection['desagregation']);
                $request->getSession()->set('_modalite', null);
            }
        }

        if (($desagregation && $desagregation->getId() == $national) || ($desagregation && $modalite)) {
            $show = true;
            //$desagregation = $desagregation->getId() == $national ? null : $desagregation;
            for ($i = 0; $i < 5; ++$i) {
                $etats[$i] = (!$settingsManager->autorisationSaisie($year - $i) || $i>0);
                $indice = $this->getDoctrine()
                    ->getRepository(Indice::class)
                    ->findOneBy(['indicator' => $indicator->getId(), 'year' => $year - $i, 'desagregation' => $desagregation, 'modalite' => $modalite]);
                if ($indice) {
                    if ($i == 0) {
                        $model->recValue = $indice->getValue();
                        $model->observation = $indice->getObservation();
                    }
                    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(SuiviINDModelNewType::class, $model, array(
            'etats' => $etats,
        ));

        $formdesagregation = $this->createForm(GoalOnlySelNewType::class, null, array(
            'desagregation' => $desagregation, 'modalite' => $modalite, 'desagregations' => $desagregations,
        ));

        $form->handleRequest($request);
        if ($form->isSubmitted() && $form->isValid()) {
            $desagregation = $this->getDoctrine()->getRepository(Desagregation::class)->findOneBy(['id' => $request->getSession()->get('_desagregation')]);
            $modalite = $request->getSession()->get('_modalite');
            $model = $form->getData();
            if ($year == $model->refYear) {
                $model->recValue = $model->refValue;
            }
            $indice = $this->getDoctrine()
                ->getRepository(Indice::class)
                ->findOneBy(['indicator' => $indicator->getId(), 'year' => $year, 'desagregation' => $desagregation, 'modalite' => $modalite]);

            if (!$model->recValue or ($desagregation && $desagregation->getId() != $national) or ((($model->recValue >= $model->minValue) and ($model->recValue <= $model->maxValue))
                    or (($model->recValue <= $model->minValue) and ($model->recValue >= $model->maxValue)))
            ) {

                if (!$indice) {
                    $indice = new Indice();
                    $indice->setIndicator($model->indicator);
                    $indice->setYear($year);
                    $indice->setDesagregation($desagregation);
                    $indice->setModalite($modalite);
                }
                if ($model->indicator->getInstad()) {
                    if ($this->isGranted('ROLE_INSTAD')||$this->isGranted('ROLE_MANAGER')) {
                        $indice->setValue($model->recValue);
                    } else {
                        $indice->setValeurAdministrative($model->recValue);
                    }
                } else {
                    $indice->setValue($model->recValue);
                    $indice->setValeurAdministrative($model->recValue);
                }
                if(!$indice->getValue()){
                    $indice->setValue($model->recValue);
                }
                $indice->setObservation($model->observation);
                $indice->setRefYear($model->refYear);
                $indice->setRefValue($model->refValue);
                $indice->setMinValue($model->minValue);
                $indice->setMaxValue($model->maxValue);
                $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('suivi_ind/suivi_form.html.twig', [
            'model' => $model,
            'year' => $year,
            'show' => $show,
            'etat0' => !$etats[0],
            'msgvaleur' => $settingsManager->valeurMsg($desagregation, $modalite),
            'form' => $form->createView(),
            'formdesagregation' => $formdesagregation->createView(),
        ]);
    }


    /**
     * @Route("/app/output/indice", name="app_output_indice")
     */
    public function outputIndiceAction(Request $request, SettingsManager $settingsManager)
    {
        $datas = [
            'header' => [
                'indice' => '-',
                'trend' => '-'
            ],
            'rows' => []
        ];
        $national = 1;
        $departement = 2;
        $commune = 3;
        $zone = 4;
        $sexe = 5;
        $desagregationEntity = $this->getDoctrine()->getRepository(Desagregation::class)->findOneBy(['id' => $national]);
        $modaliteEntity = null;

        $year = $request->getSession()->get('_year');
        $target = null;
        $goal = null;
        $goals = $this->getDoctrine()->getRepository(Goal::class)->findBy(['agenda' => 2030]);


        $form = $this->createForm(GoalSelNewType::class);

        $form->handleRequest($request);
        if ($form->isSubmitted() && $form->isValid()) {
            $goal = $form->get('goal')->getData();
            $target = $form->get('target')->getData();
            $desagregationEntity = $form->get('desagregation')->getData();
            $modaliteEntity = $form->get('modalite')->getData();
        }
        $desagregation = $desagregationEntity->getId();
        $modalite = $modaliteEntity ? $modaliteEntity->getId() : null;
        if ($target) {
            $showValue = true;
            foreach ($target->getIndicators() as $indicator) {
                if ($this->getIndicatorIndice($year, $indicator, 'rec')) {
                    $datas['rows'][$indicator->getId()]['code'] = $indicator->getCode();
                    $datas['rows'][$indicator->getId()]['name'] = $indicator->getName();
                    $datas['rows'][$indicator->getId()]['maxValue'] = $settingsManager->getIndicatorValue($year, $indicator, 'maxValue', $desagregation, $modalite); //$indice->getMaxValue();
                    $datas['rows'][$indicator->getId()]['minValue'] = $settingsManager->getIndicatorValue($year, $indicator, 'minValue', $desagregation, $modalite); //$indice->getMinValue(); 
                    $datas['rows'][$indicator->getId()]['refYear'] = $indicator->getRefYear() ? $indicator->getRefYear() : '-';
                    $datas['rows'][$indicator->getId()]['refValue'] = $indicator->getRefValue() ? $indicator->getRefValue() : '-';
                    $datas['rows'][$indicator->getId()]['recYear'] = $settingsManager->getIndicatorValue($year, $indicator, 'recYear', $desagregation, $modalite); //$indice->getYear();
                    $datas['rows'][$indicator->getId()]['recValue'] = $settingsManager->getIndicatorValue($year, $indicator, 'recValue', $desagregation, $modalite); //$indice->getValue();
                    $datas['rows'][$indicator->getId()]['refIndice'] = round($settingsManager->getIndicatorIndice($year, $indicator, 'ref', $desagregation, $modalite), 2);
                    $datas['rows'][$indicator->getId()]['recIndice'] = round($settingsManager->getIndicatorIndice($year, $indicator, 'rec', $desagregation, $modalite), 2);
                    $statut = $this->getDoctrine()->getRepository(Statut::class)
                        ->findOneByValue($settingsManager->getIndicatorStatut($year, $indicator, $desagregation, $modalite));
                    $datas['rows'][$indicator->getId()]['statut'] = $statut ? $statut->getFlag() : '-';

                    $trend = $this->getDoctrine()->getRepository(Trend::class)
                        ->findOneByValue($settingsManager->getIndicatorTrend($year, $indicator, $desagregation, $modalite));
                    $datas['rows'][$indicator->getId()]['trend'] = $trend ? $trend->getFlag() : '-';
                }
            }
            $datas['header']['indice'] = $settingsManager->getTargetIndice($year, $target, 'rec', $desagregation, $modalite);
            $datas['header']['indiceA'] = $settingsManager->getTargetIndice($year, $target, 'rec', $desagregation, $modalite,'ARITHMETIQUE');
            $datas['header']['indiceG'] = $settingsManager->getTargetIndice($year, $target, 'rec', $desagregation, $modalite, 'GEOMETRIQUE');
            $trend = $this->getDoctrine()->getRepository(Trend::class)
                ->findOneByValue($settingsManager->getTargetTrend($year, $target, $desagregation, $modalite));
            $datas['header']['trend'] = $trend ? $trend->getFlag() : '-';
            $statut = $this->getDoctrine()->getRepository(Statut::class)
                ->findOneByValue($settingsManager->getTargetStatut($year, $target, $desagregation, $modalite));
            $datas['header']['statut'] = $statut ? $statut->getFlag() : '-';
            $datas['title'] = "Indice Cible " . $target->getCode();
        } elseif ($goal) {
            $showValue = false;
            foreach ($goal->getTargets() as $target) {
                if ($this->getTargetIndice($year, $target, 'rec')) {
                    $datas['rows'][$target->getId()]['code'] = $target->getCode();
                    $datas['rows'][$target->getId()]['name'] = $target->getName();
                    $datas['rows'][$target->getId()]['refIndice'] = round($settingsManager->getTargetIndice($year, $target, 'ref', $desagregation, $modalite), 2);
                    $datas['rows'][$target->getId()]['recIndice'] = round($settingsManager->getTargetIndice($year, $target, 'rec', $desagregation, $modalite), 2);
                    $datas['rows'][$target->getId()]['recIndiceA'] = round($settingsManager->getTargetIndice($year, $target, 'rec', $desagregation, $modalite, 'ARITHMETIQUE'), 2);
                    $datas['rows'][$target->getId()]['recIndiceG'] = round($settingsManager->getTargetIndice($year, $target, 'rec', $desagregation, $modalite, 'GEOMETRIQUE'), 2);
                    $statut = $this->getDoctrine()->getRepository(Statut::class)
                        ->findOneByValue($settingsManager->getTargetStatut($year, $target, $desagregation, $modalite));
                    $datas['rows'][$target->getId()]['statut'] = $statut ? $statut->getFlag() : '-';
                    $trend = $this->getDoctrine()->getRepository(Trend::class)
                        ->findOneByValue($settingsManager->getTargetTrend($year, $target, $desagregation, $modalite));
                    $datas['rows'][$target->getId()]['trend'] = $trend ? $trend->getFlag() : '-';
                }
            }
            $datas['header']['indice'] = $settingsManager->getGoalIndice($year, $goal, 'rec', $desagregation, $modalite);
            $datas['header']['indiceA'] = $settingsManager->getGoalIndice($year, $goal, 'rec', $desagregation, $modalite, 'ARITHMETIQUE');
            $datas['header']['indiceG'] = $settingsManager->getGoalIndice($year, $goal, 'rec', $desagregation, $modalite, 'GEOMETRIQUE');
            $trend = $this->getDoctrine()->getRepository(Trend::class)
                ->findOneByValue($settingsManager->getGoalTrend($year, $goal, $desagregation, $modalite));
            $datas['header']['trend'] = $trend ? $trend->getFlag() : '-';
            $statut = $this->getDoctrine()->getRepository(Statut::class)
                ->findOneByValue($settingsManager->getGoalStatut($year, $goal, $desagregation, $modalite));
            $datas['header']['statut'] = $statut ? $statut->getFlag() : '-';
            $datas['title'] = "Indice ODD " . $goal->getCode();
        } else {
            $showValue = false;
            foreach ($goals as $goal) {
                //if ($settingsManager->getGoalIndice($year, $goal, 'rec', $desagregation, $modalite)) {
                    $datas['rows'][$goal->getId()]['code'] = $goal->getCode();
                    $datas['rows'][$goal->getId()]['name'] = $goal->getName();
                    $datas['rows'][$goal->getId()]['refIndice'] = round($settingsManager->getGoalIndice($year, $goal, 'ref', $desagregation, $modalite), 2);
                    $datas['rows'][$goal->getId()]['recIndice'] = round($settingsManager->getGoalIndice($year, $goal, 'rec', $desagregation, $modalite), 2);
                    $datas['rows'][$goal->getId()]['recIndiceG'] = round($settingsManager->getGoalIndice($year, $goal, 'rec', $desagregation, $modalite,'GEOMETRIQUE'), 2);
                    $datas['rows'][$goal->getId()]['recIndiceA'] = round($settingsManager->getGoalIndice($year, $goal, 'rec', $desagregation, $modalite,'ARITHMETIQUE'), 2);
                    $statut = $this->getDoctrine()->getRepository(Statut::class)
                        ->findOneByValue($settingsManager->getGoalStatut($year, $goal, $desagregation, $modalite));
                    $datas['rows'][$goal->getId()]['statut'] = $statut ? $statut->getFlag() : '-';
                    $trend = $this->getDoctrine()->getRepository(Trend::class)
                        ->findOneByValue($settingsManager->getGoalTrend($year, $goal, $desagregation, $modalite));
                    $datas['rows'][$goal->getId()]['trend'] = $trend ? $trend->getFlag() : '-';
                //}
            }
            $datas['header']['indice'] = $settingsManager->getGlobalIndice($year, $goals, 'rec', $desagregation, $modalite);
            $datas['header']['indiceG'] = $settingsManager->getGlobalIndice($year, $goals, 'rec', $desagregation, $modalite, 'GEOMETRIQUE');
            $datas['header']['indiceA'] = $settingsManager->getGlobalIndice($year, $goals, 'rec', $desagregation, $modalite, 'ARITHMETIQUE');
            $trend = $this->getDoctrine()->getRepository(Trend::class)
                ->findOneByValue($settingsManager->getGlobalTrend($year, $goals, $desagregation, $modalite));
            $datas['header']['trend'] = $trend ? $trend->getFlag() : '-';
            $statut = $this->getDoctrine()->getRepository(Statut::class)
                ->findOneByValue($settingsManager->getGlobalStatut($year, $goals, $desagregation, $modalite));
            $datas['header']['statut'] = $statut ? $statut->getFlag() : '-';
            $datas['title'] = "Indice global";
        }
        return $this->render('suivi_ind/output_indice.html.twig', [
            'datas' => $datas,
            'form' => $form->createView(),
            'showValue' => $showValue,
        ]);
    }

    /**
     * @Route("/app/suiviIND/graph/indicator", name="app_suiviIND_graph_indicator")
     */
    public function graphSuiviINDIndicatorAction(Request $request)
    {
        $barChart = [];
        $desagregation = $this->getDoctrine()->getRepository(Desagregation::class)->findOneBy(['id' => 1]);
        $indicator=null;
        $year = $request->getSession()->get('_year');

        $form = $this->createForm(IndicatorSelType::class);

        $datas = [];

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

            if ($indicator) {
                $datas = $this->getDoctrine()->getRepository(Indice::class)
                    ->findBy(['indicator' => $indicator, 'desagregation' => $desagregation, 'modalite' => null ], array('year' => 'ASC'));
                foreach ($datas as $data) {
                    $barChart['labels'][] = 'Année ' . $data->getYear();
                    $barChart['bgColor'][] = $goal->getColor();
                    $barChart['values'][] = $data->getValue();
                }

                $barChart['labels'][] = 'Année Cible';
                $barChart['bgColor'][] = '#36a2eb'; //$goal->getColor();
                $barChart['values'][] = $indicator->getMaxValue();
                $barChart['title'][] = $indicator->getName();
            }
        }

        return $this->render('suivi_ind/graph_indicator.html.twig', [
            'barChart' => $barChart,
            'form' => $form->createView(),
            'indicator' => $indicator,
        ]);
    }

    /**
     * @Route("/app/suiviIND/graph/indice", name="app_suiviIND_graph_indice")
     */
    public function graphSuiviINDIndiceAction(Request $request)
    {
        $barChart = [];

        $year = $request->getSession()->get('_year');
        $target = null;
        $goal = null;
        $goals = $this->getDoctrine()->getRepository(Goal::class)->findBy(['agenda' => 2030]);

        $form = $this->createForm(GoalSelType::class);

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

        if ($target) {
            foreach ($target->getIndicators() as $indicator) {
                if ($this->getIndicatorIndice($year, $indicator, 'rec')) {
                    $barChart['labels'][] = ' ' . $indicator->getCode();
                    $statut = $this->getDoctrine()->getRepository(Statut::class)
                        ->findOneByValue($this->getTargetStatut($year, $target));
                    $barChart['bgColor'][] = $statut->getFlag(); //'#36a2eb';
                    $barChart['values'][] = round($this->getIndicatorIndice($year, $indicator, 'rec'), 2);
                }
            }
        } elseif ($goal) {
            foreach ($goal->getTargets() as $target) {
                if ($this->getTargetIndice($year, $target, 'rec')) {
                    $barChart['labels'][] = ' ' . $target->getCode();
                    $statut = $this->getDoctrine()->getRepository(Statut::class)
                        ->findOneByValue($this->getTargetStatut($year, $target));
                    $barChart['bgColor'][] =  $statut->getFlag(); //'#ff6384';
                    $barChart['values'][] = round($this->getTargetIndice($year, $target, 'rec'), 2);
                }
            }
        } else {
            foreach ($goals as $goal) {
                if ($this->getGoalIndice($year, $goal, 'rec')) {
                    $barChart['labels'][] = 'ODD ' . $goal->getCode();
                    $statut = $this->getDoctrine()->getRepository(Statut::class)
                        ->findOneByValue($this->getGoalStatut($year, $goal));
                    $barChart['bgColor'][] = $statut->getFlag(); //$goal->getColor(); //'#4bc0c0';
                    $barChart['values'][] = round($this->getGoalIndice($year, $goal, 'rec'), 2);
                }
            }
        }

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


    //Chargement des données

    /**
     * @Route("/app/ministere/chargement", name="app_chargement_indicateurs")
     */
    public function ministereChargementAction(Request $request, SettingsManager $settingsManager)
    {
        if ($this->isGranted('ROLE_MANAGER')) {
            $indicators = $this->getDoctrine()->getRepository(Indicator::class)->findBy(['communal' => false]);
        } else if ($this->isGranted('ROLE_INSTAD')) {
            $indicators = $this->getDoctrine()->getRepository(Indicator::class)->findBy(['communal' => false, 'instad' => true]);
        } else {
            $ministry = $request->getSession()->get('_ministry');
            $ministry = $this->getDoctrine()->getRepository(Ministry::class)->findOneBy(['id' => $ministry->getId()]);
            $indicators = $ministry->getIndicators();
        }
        $etat = $settingsManager->autorisationSaisie($request->getSession()->get('_year'));
        $year = $settingsManager->getYear($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();
        }

        $year = $request->getSession()->get('_year');
        $suivis = [];

        foreach ($indicators as $indicator) {
            $suivis[$indicator->getId()]['indicator'] = $indicator;
            for ($i = 0; $i < 5; ++$i) {
                $suivi = $this->getDoctrine()
                    ->getRepository(Indice::class)
                    ->findOneBy(['indicator' => $indicator, 'year' => $year - $i, 'desagregation' => 1, 'modalite' => null]);
                $suivis[$indicator->getId()][$year - $i] = $suivi ? $suivi->getValue() : '-';
                if ($i == 0) {
                    $suivis[$indicator->getId()]['observation'] = $suivi ? $suivi->getObservation() : '';
                }
            }
        }

        $formformat = $this->createForm(MinistryOnlySelNewType::class, null, [
            'action' => $this->generateUrl('app_ministere_format'),
            'sessionYear' => $request->getSession()->get('_year'),
            'sessionMinistry' => $request->getSession()->get('_ministry'),
            'etat' => $this->isGranted('ROLE_MANAGER')
        ]);

        $formformat->handleRequest($request);
        if ($formformat->isSubmitted() && $formformat->isValid()) {
            $ministry =  $formformat->get('ministry')->getData();
            return $this->redirectToRoute('app_ministere_format', ['id' => $ministry->getId()]);
        }

        $formimport = $this->createForm(MinistryOnlySelNewType::class, null, [
            'action' => $this->generateUrl('app_ministere_import'),
            'sessionYear' => $request->getSession()->get('_year'),
            'sessionMinistry' => $request->getSession()->get('_ministry'),
            'etat' => $this->isGranted('ROLE_MANAGER')
        ]);

        $formimport->handleRequest($request);
        if ($formimport->isSubmitted() && $formimport->isValid()) {
            if ($this->isGranted('ROLE_MANAGER')) {
                $ministry =  $formimport->get('ministry')->getData();
            } else {
                $ministry = $request->getSession()->get('_ministry');
                $ministry = $this->getDoctrine()->getRepository(Ministry::class)->findOneBy(['id' => $ministry->getId()]);
            }

            return $this->redirectToRoute('app_ministere_import', ['id' => $ministry->getId()]);
        }

        return $this->render('suivi_ind/chargement_list.html.twig', [
            'indicators' => $indicators,
            'etat' => $etat,
            'fin' => $fin,
            'suivis' => $suivis,
            'formformat' => $formformat->createView(),
            'formimport' => $formimport->createView(),
            'year' => $request->getSession()->get('_year')
        ]);
    }

    /**
     * @Route("/app/ministere/format/{id}", name="app_ministere_format")
     */
    public function ministereFormatAction(Request $request, SettingsManager $settingsManager, $id = 0)
    {
        //Désagrégations
        $national = 1;
        $departement = 2;
        $commune = 3;
        $zone = 4;
        $sexe = 5;
        $annee = $request->getSession()->get('_year');

        if ($this->isGranted('ROLE_MANAGER')) {
            $m = $request->request->get('ministry_only_sel_new')['ministry'];
            $ministry = $this->getDoctrine()->getRepository(Ministry::class)->findOneBy(['id' => $m]);
            $indicateurs = $ministry ? $ministry->getIndicators() : null;
            $fileName = $ministry->getCode() . '-ODD-' . $annee . '.xlsx';
        } else if ($this->isGranted('ROLE_DPP') || $this->isGranted('ROLE_FOCAL')) {
            $ministry = $request->getSession()->get('_ministry');
            $ministry = $this->getDoctrine()->getRepository(Ministry::class)->findOneBy(['id' => $ministry->getId()]);
            $indicateurs = $ministry->getIndicators();
            $fileName = $ministry->getCode() . '-ODD-' . $annee . '.xlsx';
        }
        if ($this->isGranted('ROLE_INSTAD')) {
            $indicateurs = $this->getDoctrine()->getRepository(Indicator::class)->findBy(['instad' => true]);
            $fileName = 'INStaD-ODD-' . $annee . '.xlsx';
        }
        $maquette = $this->getParameter('kernel.project_dir') . '/public/upload/ministeres/base.xlsx';

        if (count($indicateurs) > 0) {
            //Chargement de la maquette de base
            $spreadsheet = IOFactory::load($maquette);

            //Création des feuilles par indicateur
            $spreadsheet->getActiveSheet()->setTitle("Indicateur " . $indicateurs[0]->getCode());
            $nombreIndicateurs = count($indicateurs);
            for ($i = 1; $i < $nombreIndicateurs; $i++) {
                $clonedWorksheet = clone $spreadsheet->getSheet(0);
                $clonedWorksheet->setTitle("Indicateur " . $indicateurs[$i]->getCode());
                $spreadsheet->addSheet($clonedWorksheet);
            }

            //Remplissage de chaque feuille
            for ($i = 0; $i < $nombreIndicateurs; $i++) {
                $indicateur = $indicateurs[$i];
                $sheet = $spreadsheet->getSheet($i);
                $sheet->setCellValue('A1', $indicateur->getCode());
                $sheet->setCellValue('D1', $indicateur->getCode() . '-' . $indicateur->getName());
                $sheet->setCellValue('H1', $indicateur->getMinValue());
                $sheet->setCellValue('J1', $indicateur->getMaxValue());
                $sheet->setCellValue('B3', 1);

                $numero = 2;
                $line = 4;
                if (in_array($departement, $indicateur->getDesagregations())) {
                    $sheet->insertNewRowBefore($line);
                    $sheet->setCellValue('A' . $line, "Valeur de l'indicateur par département");
                    $sheet->mergeCells('A' . $line . ':J' . $line);
                    $sheet->getStyle('A' . $line)->getFont()->setBold(true);
                    $line++;

                    $departements = $this->getDoctrine()->getRepository(Department::class)->findBy(array(), array('name' => 'ASC'));
                    foreach ($departements as $d) {
                        $sheet->insertNewRowBefore($line);
                        $sheet->setCellValue('A' . $line, $numero);
                        $numero++;
                        $sheet->getStyle('A' . $line)->getFont()->setBold(false);
                        $sheet->setCellValue('B' . $line, $departement);
                        $sheet->setCellValue('C' . $line, $d->getId());
                        $sheet->setCellValue('D' . $line, $d->getname());
                        $sheet->mergeCells('F' . $line . ':J' . $line);
                        $line++;
                    }
                }

                if (in_array($commune, $indicateur->getDesagregations())) {
                    $sheet->insertNewRowBefore($line);
                    $sheet->setCellValue('A' . $line, "Valeur de l'indicateur par commune");
                    $sheet->mergeCells('A' . $line . ':J' . $line);
                    $sheet->getStyle('A' . $line)->getFont()->setBold(true);
                    $line++;

                    $communes = $this->getDoctrine()->getRepository(Commune::class)->findBy(array(), array('name' => 'ASC'));
                    foreach ($communes as $c) {
                        $sheet->insertNewRowBefore($line);
                        $sheet->setCellValue('A' . $line, $numero);
                        $numero++;
                        $sheet->getStyle('A' . $line)->getFont()->setBold(false);
                        $sheet->setCellValue('B' . $line, $commune);
                        $sheet->setCellValue('C' . $line, $c->getId());
                        $sheet->setCellValue('D' . $line, $c->getname());
                        $sheet->mergeCells('F' . $line . ':J' . $line);
                        $line++;
                    }
                } else {
                    $numero++;
                }

                if (in_array($zone, $indicateur->getDesagregations())) {
                    $sheet->insertNewRowBefore($line);
                    $sheet->setCellValue('A' . $line, "Valeur de l'indicateur par zone sanitaire");
                    $sheet->getStyle('A' . $line)->getFont()->setBold(true);
                    $sheet->mergeCells('A' . $line . ':J' . $line);
                    $line++;

                    $zones = $this->getDoctrine()->getRepository(Zone::class)->findBy(array(), array('name' => 'ASC'));
                    foreach ($zones as $z) {
                        $sheet->insertNewRowBefore($line);
                        $sheet->setCellValue('A' . $line, $numero);
                        $numero++;
                        $sheet->getStyle('A' . $line)->getFont()->setBold(false);
                        $sheet->setCellValue('B' . $line, $zone);
                        $sheet->setCellValue('C' . $line, $z->getId());
                        $sheet->setCellValue('D' . $line, $z->getname());
                        $sheet->mergeCells('F' . $line . ':J' . $line);
                        $line++;
                    }
                }

                if (in_array($sexe, $indicateur->getDesagregations())) {
                    $sheet->insertNewRowBefore($line);
                    $sheet->setCellValue('A' . $line, "Valeur de l'indicateur par sexe");
                    $sheet->getStyle('A' . $line)->getFont()->setBold(true);
                    $sheet->mergeCells('A' . $line . ':J' . $line);
                    $line++;

                    $sheet->insertNewRowBefore($line);
                    $sheet->setCellValue('A' . $line, $numero);
                    $numero++;
                    $sheet->getStyle('A' . $line)->getFont()->setBold(false);
                    $sheet->setCellValue('B' . $line, $sexe);
                    $sheet->setCellValue('C' . $line, 1);
                    $sheet->setCellValue('D' . $line, "Homme");
                    $sheet->mergeCells('F' . $line . ':J' . $line);
                    $line++;

                    $sheet->insertNewRowBefore($line);
                    $sheet->setCellValue('A' . $line, $numero);
                    $numero++;
                    $sheet->getStyle('A' . $line)->getFont()->setBold(false);
                    $sheet->setCellValue('B' . $line, $sexe);
                    $sheet->setCellValue('C' . $line, 2);
                    $sheet->setCellValue('D' . $line, "Femme");
                    $sheet->mergeCells('F' . $line . ':J' . $line);
                    $line++;
                }

                foreach ($indicateur->getDesagregations() as $d) {
                    if ($d > $sexe) {
                        $desagregation = $this->getDoctrine()->getRepository(Desagregation::class)->findOneBy(['id' => $d]);
                        $sheet->insertNewRowBefore($line);
                        $sheet->setCellValue('A' . $line, "Valeur de l'indicateur par " . $desagregation->getName());
                        $sheet->getStyle('A' . $line)->getFont()->setBold(true);
                        $sheet->mergeCells('A' . $line . ':J' . $line);
                        $line++;


                        foreach ($desagregation->getModalites() as $modalite) {
                            $sheet->insertNewRowBefore($line);
                            $sheet->setCellValue('A' . $line, $numero);
                            $numero++;
                            $sheet->getStyle('A' . $line)->getFont()->setBold(false);
                            $sheet->setCellValue('B' . $line, $desagregation->getId());
                            $sheet->setCellValue('C' . $line, $modalite->getId());
                            $sheet->setCellValue('D' . $line, $modalite->getname());
                            $sheet->mergeCells('F' . $line . ':J' . $line);
                            $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;
        } else {
            $this->addFlash('error', "Une erreur s'est produite. Veuillez réessayer ou contacter la DGCS-ODD.");
            return $this->redirectToRoute('app_chargement_indicateurs');
        }
    }

    /**
     * @Route("/app/ministere/import/{id}", name="app_ministere_import")
     */
    public function importFormatAction(Request $request, SettingsManager $settingsManager, $id = 0)
    {
        //Désagrégations
        $national = 1;
        $departement = 2;
        $commune = 3;
        $zone = 4;
        $sexe = 5;
        $erreur=false;
        if ($this->isGranted('ROLE_MANAGER')) {
            $id = $request->request->get('ministry_only_sel_new')['ministry'];
            $ministry = $this->getDoctrine()->getRepository(Ministry::class)->findOneBy(['id' => $id]);
            $indicateurs = $ministry->getIndicators();
        } else if ($this->isGranted('ROLE_DPP') || $this->isGranted('ROLE_FOCAL')) {
            $ministry = $request->getSession()->get('_ministry');
            $ministry = $this->getDoctrine()->getRepository(Ministry::class)->findOneBy(['id' => $ministry->getId()]);
            $indicateurs = $ministry->getIndicators();
        }
        if ($this->isGranted('ROLE_INSTAD')) {
            $indicateurs = $this->getDoctrine()->getRepository(Indicator::class)->findBy(['instad' => true]);
        }
        $year = $settingsManager->getYear($request->getSession()->get('_year'));
        //Fichier
        $file = $request->files->get('fichier');
        if (!empty($file)) {
            //Importation des données
            $spreadsheet = IOFactory::load($file);
            if ($spreadsheet->getSheetCount() == count($indicateurs)) {
                for ($i = 0; $i < count($indicateurs); $i++) {
                    $sheet = $spreadsheet->getSheet($i);
                    $indicateur = $this->getDoctrine()->getRepository(Indicator::class)->findOneBy(['code' => $sheet->getCell('A1')->getCalculatedValue()]);
                    if ($indicateur) {
                        for ($j = 3; $j <= $sheet->getHighestDataRow(); $j++) {
                            if (is_int($sheet->getCell('A' . $j)->getCalculatedValue())) {
                                $modalite = $sheet->getCell('C' . $j)->getCalculatedValue();
                                $desagregation = $this->getDoctrine()->getRepository(Desagregation::class)->findOneBy(['id' => $sheet->getCell('B' . $j)->getCalculatedValue()]);
                                $indice = $this->getDoctrine()->getRepository(Indice::class)->findOneBy(['year' => $year->getName(), 'indicator' => $indicateur, 'modalite' => $modalite, 'desagregation' => $desagregation]);
                                if (!$indice) {
                                    $indice = new Indice();
                                    $indice->setYear($year->getName());
                                    $indice->setIndicator($indicateur);
                                    $indice->setModalite($modalite);
                                    $indice->setDesagregation($desagregation);
                                }

                                $val = $sheet->getCell('E' . $j)->getCalculatedValue();
                                if ($val == "#DIV/0!" || $val == "#REF!" || $val == "#VALEUR!" || $val == "") {
                                    //$val = null;
                                } else {
                                    $val = floatval(str_replace(',', '.', $val));
                                    //if (!($desagregation->getId() == $national && $val >= $indicateur->getMinValue() && $val <= $indicateur->getMaxValue())) {
                                       // $this->addFlash('error', "Impossible d'enregistrer la valeur nationale de l'indicateur " . $indicateur->getCode() . ". Veuillez contacter la DGCS-ODD.");
                                        //$erreur=true;
                                        //dd("Impossible d'enregistrer la valeur nationale de l'indicateur " . $indicateur->getCode() . ". Veuillez contacter la DGCS-ODD.");
                                    //} else {
                                        if ($indice->getInstad()) {
                                            if ($this->isGranted('ROLE_DPP') || $this->isGranted('ROLE_FOCAL')) {
                                                $indice->setValeurAdministrative($val);
                                                $indice->setObservationAdministrative($sheet->getCell('F' . $j)->getCalculatedValue());
                                            }
                                            if ($this->isGranted('ROLE_INSTAD')) {
                                                $indice->setValue($val);
                                                $indice->setInstad(true);
                                                $indice->setObservation($sheet->getCell('F' . $j)->getCalculatedValue());
                                                $indice->setObservationAdministrative($sheet->getCell('F' . $j)->getCalculatedValue());
                                            }
                                        } else {
                                            $indice->setValue($val);
                                            $indice->setValeurAdministrative($val);
                                            $indice->setObservation($sheet->getCell('F' . $j)->getCalculatedValue());
                                            $indice->setObservationAdministrative($sheet->getCell('F' . $j)->getCalculatedValue());
                                        }
                                        $indice->setRefYear($indicateur->getRefYear());
                                        $indice->setRefValue($indicateur->getRefValue());
                                        $indice->setMinValue($indicateur->getMinValue());
                                        $indice->setMaxValue($indicateur->getMaxValue());

                                        $this->getDoctrine()->getManager()->persist($indice);
                                    //}
                                }
                            }
                        }
                    }
                }
            }


            if ($this->isGranted('ROLE_DPP') || $this->isGranted('ROLE_FOCAL')) {
                $structureyear = $settingsManager->getStructureYear($request->getSession()->get('_year'));
                if (!$structureyear) {
                    $structureyear = new StructureYear();
                    $structureyear->setMinistry($ministry);
                    $structureyear->setYear($year);
                }
                $this->getDoctrine()->getManager()->persist($structureyear);
            }
            $this->getDoctrine()->getManager()->flush();
            if(!$erreur){
                $this->addFlash('success', 'Données importées avec succès');
            }
            
        } else {
            $this->addFlash('error', "Une erreur s'est produite. Veuillez utilier la maquette pour importer les données.");
        }
        return $this->redirectToRoute('app_chargement_indicateurs');
    }

    /**
     * @Route("/app/ministere/validation", name="app_ministere_validation")
     */
    public function ministereValidationAction(Request $request, SettingsManager $settingsManager)
    {
        $year = $settingsManager->getYear($request->getSession()->get('_year'));
        $ministry = $request->getSession()->get('_ministry');
        $ministry = $this->getDoctrine()->getRepository(Ministry::class)->findOneBy(['id' => $ministry->getId()]);
        $structureyear = $settingsManager->getStructureYear($request->getSession()->get('_year'));
        if (!$structureyear) {
            $structureyear = new StructureYear();
            $structureyear->setMinistry($ministry);
            $structureyear->setYear($year);
            //$this->getDoctrine()->getManager()->persist($structureyear);
            //$this->getDoctrine()->getManager()->flush();
        }
        if ($year && $ministry) {
            if ($this->isGranted('ROLE_DPP')) {
                $structureyear->setDpp(true);
            }
            if ($this->isGranted('ROLE_FOCAL')) {
                $structureyear->setFocal(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 avec succès');
            }
        } else {
            $this->addFlash('error', "Une erreur s'est produite. Veuillez réessayer plus tard.");
        }
        return $this->redirectToRoute('app_suiviIND');
    }

    /**
     * @Route("/app/ministere/reinitialisation/indicateur/{id}", name="reinit_ministere_indicateur")
     */
    public function reinitMinistereIndicateurAction(Request $request, SettingsManager $settingsManager, $id)
    {
        $ministry = $this->getDoctrine()->getRepository(Ministry::class)->findOneBy(['id' => $id]);
        $structure = $settingsManager->getStructureYear($request->getSession()->get('_year'), $ministry);
        $structure->setDpp(false);
        $structure->setFocal(false);
        $this->getDoctrine()->getManager()->persist($structure);
        $this->getDoctrine()->getManager()->flush();
        return $this->redirectToRoute('app_suiodd_dashboard');
    }

    /**
     * @Route("/app/commune/reinitialisation/indicateur/{id}", name="reinit_commune_indicateur")
     */
    public function reinitCommuneIndicateurAction(Request $request, SettingsManager $settingsManager, $id)
    {
        $commune = $this->getDoctrine()->getRepository(Commune::class)->findOneBy(['id' => $id]);
        $structure = $settingsManager->getStructureYear($request->getSession()->get('_year'), null, $commune);
        $structure->setDpp2(false);
        $structure->setFocal2(false);
        $this->getDoctrine()->getManager()->persist($structure);
        $this->getDoctrine()->getManager()->flush();
        return $this->redirectToRoute('app_suiodd_dashboard');
    }

    /**
     * @Route("/app/ministere/reinitialisation/activite/{id}", name="reinit_ministere_activite")
     */
    public function reinitMinistereActiviteAction(Request $request, SettingsManager $settingsManager, $id)
    {
        $ministry = $this->getDoctrine()->getRepository(Ministry::class)->findOneBy(['id' => $id]);
        $structure = $settingsManager->getStructureYear($request->getSession()->get('_year'), $ministry);
        $structure->setDpppta(false);
        $structure->setFocalpta(false);
        $this->getDoctrine()->getManager()->persist($structure);
        $this->getDoctrine()->getManager()->flush();
        return $this->redirectToRoute('app_suiodd_dashboard');
    }
}