<?php

namespace App\Controller;

use App\Entity\Ministry;
use App\Entity\Goal;
use App\Entity\Target;
use App\Entity\Activity;
use App\Entity\Notation;
use App\Entity\Nature;
use App\Entity\Appreciation;
use App\Entity\Direction;
use App\Entity\Programm;
use App\Entity\Commune;
use App\Entity\Department;
use App\Entity\Indicator;
use App\Entity\Mainstay;
use App\Form\GoalSelType;
use App\Form\MinistrySelType;
use App\Form\CommuneSelType;
use App\Form\ArrondissementSelType;
use App\Form\CommuneOnlySelType;
use App\Form\GoalOnlySelType;
use App\Form\MainstayOnlySelType;
use App\Form\MainstaySelType;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\Routing\Annotation\Route;
use Symfony\Component\HttpFoundation\Request;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use Symfony\Component\HttpFoundation\StreamedResponse;
use App\Service\SettingsManager;

class OutputController extends AbstractController
{

    /**
     * @Route("/app/output/goal", name="app_output_goal")
     */
    public function outputGoalAction(Request $request)
    {
        $datas = [
            'header' => [],
            'rows' => []
        ];

        $ministry=null;
        $commune=null;
        $departement=null;
        $programm=null;
        $action=null;
        $type=null;

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

        if($request->getSession()->get('_ministry')){
            $ministry = $this->getDoctrine()->getRepository(Ministry::class)->findOneBy(['id' => $request->getSession()->get('_ministry')]);
        }

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

        if($request->getSession()->get('_departement')){
            $departement = $this->getDoctrine()->getRepository(Department::class)->findOneBy(['id' => $request->getSession()->get('_departement')]);
        }

        $form = $this->createForm(MinistrySelType::class, null, [
            'sessionYear' => $request->getSession()->get('_year'),
            'sessionMinistry' => $request->getSession()->get('_ministry'),
            'sessionCommune' => $request->getSession()->get('_commune'),
            'sessionDepartement' => $request->getSession()->get('_departement'),
        ]);

        $form->handleRequest($request);
        if ($form->isSubmitted() && $form->isValid()) {
            if ($this->isGranted('ROLE_MANAGER')) {
                $type = $form->get('type')->getData();
                if($type == 'ministere') {
                    $ministry= $form->get('entite')->getData();
                }elseif($type == 'commune'){
                    $commune = $form->get('entite')->getData();
                }
            }
            
            $programm = $form->get('programm')->getData();
            $action = $form->get('action')->getData();
        }

        $sumSensitivity = 0;
        $nbActivity = 0;
        $amount = 0;
        $rows = [];

        if ( $ministry ) {
            $rows = $this->getDoctrine()
                ->getRepository(Goal::class)
                ->getSensitivityByMinistry($year, $ministry, $programm, $action);
        }else if($commune){
            $rows = $this->getDoctrine()
                ->getRepository(Goal::class)
                ->getSensitivityByCommune($year, $commune, $programm, $action);
        }

            foreach ($rows as $key => $row) {
                $nbActivity += $rows[$key]['nbActivity'];
                $sumSensitivity += $rows[$key]['sumSensitivity'];
                $amount += $rows[$key]['amount'];
            }
            $header['year'] = $year;
            $header['level1']['name'] = $ministry ? $ministry->getName() : '';
            $header['level1']['code'] = $ministry ? $ministry->getCode() : '';
            $header['level2']['name'] = $programm ? $programm->getName() : '';
            $header['level2']['code'] = $programm ? $programm->getCode() : '';
            $header['level3']['name'] = $action ? $action->getName() : '';
            $header['level3']['code'] = $action ? $action->getCode() : '';

            $header['sensitivity'] = $nbActivity ? round($sumSensitivity / $nbActivity, 2) : 0;
            $sumAppreciation = $this->getDoctrine()
                ->getRepository(Appreciation::class)
                ->findOneByValue($header['sensitivity']);
            $header['appreciation'] = $sumAppreciation ? $sumAppreciation->getName() : '-';
            $header['flag'] = $sumAppreciation ? $sumAppreciation->getFlag() : 'red';
            $header['amount'] = $amount;
            $header['nbActivity'] = $nbActivity;

            $datas['header'] = $header;
            $datas['rows'] = $rows;  

        return $this->render('output/output_goal.html.twig', [
            'datas' => $datas,
            'show' => $ministry || $commune,
            'form' => $form->createView(),
        ]);
    }

    /**
     * @Route("/app/output/target", name="app_output_target")
     */
    public function outputTargetAction(Request $request)
    {
        $datas = [
            'header' => [],
            'rows' => []
        ];

        $ministry=null;
        $commune=null;
        $departement=null;
        $programm=null;
        $action=null;
        $type=null;

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

        if($request->getSession()->get('_ministry')){
            $ministry = $this->getDoctrine()->getRepository(Ministry::class)->findOneBy(['id' => $request->getSession()->get('_ministry')]);
        }

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

        if($request->getSession()->get('_departement')){
            $departement = $this->getDoctrine()->getRepository(Department::class)->findOneBy(['id' => $request->getSession()->get('_departement')]);
        }

        $form = $this->createForm(MinistrySelType::class, null, [
            'sessionYear' => $request->getSession()->get('_year'),
            'sessionMinistry' => $request->getSession()->get('_ministry'),
            'sessionCommune' => $request->getSession()->get('_commune'),
            'sessionDepartement' => $request->getSession()->get('_departement'),
        ]);

        $form->handleRequest($request);
        if ($form->isSubmitted() && $form->isValid()) {
            if ($this->isGranted('ROLE_MANAGER')) {
                $type = $form->get('type')->getData();
                if($type == 'ministere') {
                    $ministry= $form->get('entite')->getData();
                }elseif($type == 'commune'){
                    $commune = $form->get('entite')->getData();
                }
            }
            $programm = $form->get('programm')->getData();
            $action = $form->get('action')->getData();
        }

                $sumSensitivity = 0;
                $nbActivity = 0;
                $amount = 0;
                $rows = [];

                if ( $ministry ) {
                    $rows = $this->getDoctrine()
                    ->getRepository(Target::class)
                    ->getSensitivityByMinistry($year, $ministry, $programm, $action);
                }else if($commune){
                    $rows = $this->getDoctrine()
                        ->getRepository(Target::class)
                        ->getSensitivityByCommune($year, $commune, $programm, $action);
                }

                
                foreach ($rows as $key => $row) {
                    $nbActivity += $rows[$key]['nbActivity'];
                    $sumSensitivity += $rows[$key]['sumSensitivity'];
                    $amount += $rows[$key]['amount'];
                }
                $header['year'] = $year;
                $header['level1']['name'] = $ministry ? $ministry->getName() : '';
                $header['level1']['code'] = $ministry ? $ministry->getCode() : '';
                $header['level2']['name'] = $programm ? $programm->getName() : '';
                $header['level2']['code'] = $programm ? $programm->getCode() : '';
                $header['level3']['name'] = $action ? $action->getName() : '';
                $header['level3']['code'] = $action ? $action->getCode() : '';

                $header['sensitivity'] = $nbActivity ? round($sumSensitivity / $nbActivity, 2) : 0;
                $sumAppreciation = $this->getDoctrine()
                    ->getRepository(Appreciation::class)
                    ->findOneByValue($header['sensitivity']);
                $header['appreciation'] = $sumAppreciation ? $sumAppreciation->getName() : '-';
                $header['flag'] = $sumAppreciation ? $sumAppreciation->getFlag() : 'red';
                $header['amount'] = $amount;
                $header['nbActivity'] = $nbActivity;

                $datas['header'] = $header;
                $datas['rows'] = $rows;  

        return $this->render('output/output_target.html.twig', [
            'datas' => $datas,
            'show' => $ministry || $commune,
            'form' => $form->createView(),
        ]);
    }

    /**
     * @Route("/app/output/ministry", name="app_output_ministry")
     */
    public function outputMinistryAction(Request $request)
    {
        $datas = [
            'header' => [],
            'rows' => []
        ];
        $year = $request->getSession()->get('_year');

        $ministry=null;
        $commune=null;
        $departement=null;
        $goal=null;
        $type='ministere';

        if($request->getSession()->get('_ministry')){
            $type='ministere';
            $ministry = $this->getDoctrine()->getRepository(Ministry::class)->findOneBy(['id' => $request->getSession()->get('_ministry')]);
        }

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

        if($request->getSession()->get('_departement')){
            $type='commune';
            $departement = $this->getDoctrine()->getRepository(Department::class)->findOneBy(['id' => $request->getSession()->get('_departement')]);
        }

        $form = $this->createForm(GoalSelType::class, null, [
            'sessionMinistry' => $request->getSession()->get('_ministry'),
            'sessionCommune' => $request->getSession()->get('_commune'),
            'sessionDepartement' => $request->getSession()->get('_departement'),
        ]);

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

            if ( $goal ) {
                $sumSensitivity = 0;
                $nbActivity = 0;
                $amount = 0;

                if($ministry || $type=='ministere'){
                    $rows = $this->getDoctrine()
                    ->getRepository(Ministry::class)
                    ->getSensitivity($year, $goal, $target);
                }else if ($commune || $type=='commune'){
                    $rows = $this->getDoctrine()
                    ->getRepository(Commune::class)
                    ->getSensitivity($year, $goal, $target);
                }else{
                    $rows = [];
                }
               
                foreach ($rows as $key => $row) {
                    $nbActivity += $rows[$key]['nbActivity'];
                    $sumSensitivity += $rows[$key]['sumSensitivity'];
                    $amount += $rows[$key]['amount'];
                }
                $header['year'] = $year;
                $header['level1']['name'] = $goal ? $goal->getName() : '';
                $header['level1']['code'] = $goal ? $goal->getCode() : '';
                $header['level2']['name'] = $target ? $target->getName() : '';
                $header['level2']['code'] = $target ? $target->getCode() : '';
                $header['level3']['name'] = '';
                $header['level3']['code'] = '';

                $header['sensitivity'] = $nbActivity ? round($sumSensitivity / $nbActivity, 2) : 0;
                $sumAppreciation = $this->getDoctrine()
                    ->getRepository(Appreciation::class)
                    ->findOneByValue($header['sensitivity']);
                $header['appreciation'] = $sumAppreciation ? $sumAppreciation->getName() : '-';
                $header['flag'] = $sumAppreciation ? $sumAppreciation->getFlag() : 'red';
                $header['amount'] = $amount;
                $header['nbActivity'] = $nbActivity;

                $datas['header'] = $header;
                $datas['rows'] = $rows;  
            }
        }

        return $this->render('output/output_ministry.html.twig', [
            'datas' => $datas,
            'goal' => $goal,
            'type' => $type,
            'form' => $form->createView(),
        ]);
    }

    /**
     * @Route("/app/output/direction", name="app_output_direction")
     */
    public function outputDirectionAction(Request $request)
    {
        $datas = [
            'header' => [],
            'rows' => []
        ];
        $year = $request->getSession()->get('_year');

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

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

            if ( $goal ) {
                $sumSensitivity = 0;
                $nbActivity = 0;
                $amount = 0;
                $rows = $this->getDoctrine()
                    ->getRepository(Direction::class)
                    ->getSensitivity($year, $goal, $target);
            
                foreach ($rows as $key => $row) {
                    $nbActivity += $rows[$key]['nbActivity'];
                    $sumSensitivity += $rows[$key]['sumSensitivity'];
                    $amount += $rows[$key]['amount'];
                }
                $header['year'] = $year;
                $header['level1']['name'] = $goal ? $goal->getName() : '';
                $header['level1']['code'] = $goal ? $goal->getCode() : '';
                $header['level2']['name'] = $target ? $target->getName() : '';
                $header['level2']['code'] = $target ? $target->getCode() : '';
                $header['level3']['name'] = '';
                $header['level3']['code'] = '';

                $header['sensitivity'] = $nbActivity ? round($sumSensitivity / $nbActivity, 2) : 0;
                $sumAppreciation = $this->getDoctrine()
                    ->getRepository(Appreciation::class)
                    ->findOneByValue($header['sensitivity']);
                $header['appreciation'] = $sumAppreciation->getName();
                $header['flag'] = $sumAppreciation->getFlag();
                $header['amount'] = $amount;
                $header['nbActivity'] = $nbActivity;

                $datas['header'] = $header;
                $datas['rows'] = $rows;  
            }
        }

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

    /**
     * @Route("/app/output/programm", name="app_output_programm")
     */
    public function outputProgrammAction(Request $request)
    {
        $datas = [
            'header' => [],
            'rows' => []
        ];
        $type='ministere';
        $year = $request->getSession()->get('_year');
        $goal=null;

        if($request->getSession()->get('_ministry')){
            $type='ministere';
            $ministry = $this->getDoctrine()->getRepository(Ministry::class)->findOneBy(['id' => $request->getSession()->get('_ministry')]);
        }

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

        if($request->getSession()->get('_departement')){
            $type='commune';
            $departement = $this->getDoctrine()->getRepository(Department::class)->findOneBy(['id' => $request->getSession()->get('_departement')]);
        }

        $form = $this->createForm(GoalSelType::class, null, [
            'sessionMinistry' => $request->getSession()->get('_ministry'),
            'sessionCommune' => $request->getSession()->get('_commune'),
            'sessionDepartement' => $request->getSession()->get('_departement'),
        ]);

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

            if ( $goal ) {
                $sumSensitivity = 0;
                $nbActivity = 0;
                $amount = 0;
                if($type=='ministere'){
                    $rows = $this->getDoctrine()
                    ->getRepository(Programm::class)
                    ->getSensitivity($year, $goal, $target);
                }else if($type=='commune'){
                    $rows = $this->getDoctrine()
                    ->getRepository(Programm::class)
                    ->getSensitivityByCommune($year, $goal, $target);
                }
                
            
                foreach ($rows as $key => $row) {
                    $nbActivity += $rows[$key]['nbActivity'];
                    $sumSensitivity += $rows[$key]['sumSensitivity'];
                    $amount += $rows[$key]['amount'];
                }
                $header['year'] = $year;
                $header['level1']['name'] = $goal ? $goal->getName() : '';
                $header['level1']['code'] = $goal ? $goal->getCode() : '';
                $header['level2']['name'] = $target ? $target->getName() : '';
                $header['level2']['code'] = $target ? $target->getCode() : '';
                $header['level3']['name'] = '';
                $header['level3']['code'] = '';

                $header['sensitivity'] = $nbActivity ? round($sumSensitivity / $nbActivity, 2) : 0;
                $sumAppreciation = $this->getDoctrine()
                    ->getRepository(Appreciation::class)
                    ->findOneByValue($header['sensitivity']);
                $header['appreciation'] = $sumAppreciation ? $sumAppreciation->getName() : '-';
                $header['flag'] = $sumAppreciation ? $sumAppreciation->getFlag() : 'red';
                $header['amount'] = $amount;
                $header['nbActivity'] = $nbActivity;

                $datas['header'] = $header;
                $datas['rows'] = $rows;  
            }
        }

        return $this->render('output/output_programm.html.twig', [
            'datas' => $datas,
            'goal' => $goal,
            'type' => $type,
            'form' => $form->createView(),
        ]);
    }

    /**
     * @Route("/app/output/activity/list", name="app_output_activity_list")
     */
    public function outputActivityListAction(Request $request)
    {
        $datas = [
            'header' => [],
            'rows' => []
        ];

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

        $type='';
        $programm=null;
        $action=null;
        $rows=[];
        $ministry=null;
        $commune=null;

        if($request->getSession()->get('_ministry')){
            $type='ministere';
            $ministry = $this->getDoctrine()->getRepository(Ministry::class)->findOneBy(['id' => $request->getSession()->get('_ministry')]);
        }

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

        if($request->getSession()->get('_departement')){
            $type='commune';
            $departement = $this->getDoctrine()->getRepository(Department::class)->findOneBy(['id' => $request->getSession()->get('_departement')]);
        }


        $form = $this->createForm(MinistrySelType::class, null, [
            'sessionYear' => $request->getSession()->get('_year'),
            'sessionMinistry' => $request->getSession()->get('_ministry'),
            'sessionCommune' => $request->getSession()->get('_commune'),
            'sessionDepartement' => $request->getSession()->get('_departement'),
        ]);

        $form->handleRequest($request);
        if ($form->isSubmitted() && $form->isValid()) {
            $programm = $form->get('programm')->getData();
            $action = $form->get('action')->getData();
            if($this->isGranted('ROLE_MANAGER')){
                $type = $form->get('type')->getData();

                if($type == 'ministere') {
                    $ministry= $form->get('entite')->getData();
                }elseif($type == 'commune'){
                    $commune = $form->get('entite')->getData();
                }
    
                $previousType = $request->getSession()->get('previous_type');
    
                if ($type !== $previousType) {
                    $form = $this->createForm(MinistrySelType::class, null, [
                        'sessionYear' => $request->getSession()->get('_year'),
                        'sessionMinistry' => $request->getSession()->get('_ministry'),
                        'sessionCommune' => $request->getSession()->get('_commune'),
                        'sessionDepartement' => $request->getSession()->get('_departement'),
                        'sessionType' => $type,
                    ]);
                } 
                $request->getSession()->set('previous_type', $type);
            }
            
        }  
         
                $sumSensitivity = 0;
                $nbActivity = 0;
                $amount = 0;
                if($type=='ministere' && $ministry){
                    $rows = $this->getDoctrine()
                    ->getRepository(Notation::class)
                    ->getActivityByMinistry($year, $ministry, $programm, $action);
                }else if($type=='commune' && $commune){
                    $rows = $this->getDoctrine()
                    ->getRepository(Notation::class)
                    ->getActivityByMinistryNew($year, $commune, $programm, $action);
                }

                foreach ($rows as $key => $row) {
                    $sumSensitivity += $rows[$key]['sensitivity'];
                    $rows[$key]['sensitivity'] = round($rows[$key]['sensitivity'], 2);
                    $amount += $rows[$key]['amount'];
                    $nbActivity++;
                }
                $header['year'] = $year;
                $header['level1']['name'] = $ministry ? $ministry->getName() : '';
                $header['level1']['code'] = $ministry ? $ministry->getCode() : '';
                $header['level2']['name'] = $programm ? $programm->getName() : '';
                $header['level2']['code'] = $programm ? $programm->getCode() : '';
                $header['level3']['name'] = $action ? $action->getName() : '';
                $header['level3']['code'] = $action ? $action->getCode() : '';

                $header['sensitivity'] = $nbActivity ? round($sumSensitivity / $nbActivity, 2) : 0;
                $sumAppreciation = $this->getDoctrine()
                    ->getRepository(Appreciation::class)
                    ->findOneByValue($header['sensitivity']);
                $header['appreciation'] = $sumAppreciation ? $sumAppreciation->getName() : '-';
                $header['flag'] = $sumAppreciation ? $sumAppreciation->getFlag() : 'red';
                $header['amount'] = $amount;
                $header['nbActivity'] = $nbActivity;

                $datas['header'] = $header;
                $datas['rows'] = $rows;  
    
   
        return $this->render('output/output_activity_list.html.twig', [
            'datas' => $datas,
            'show' => $ministry || $commune,
            'form' => $form->createView(),
        ]);
    }

    /**
     * @Route("/app/output/activity/less", name="app_output_activity_less")
     */
    public function outputActivityLessAction(Request $request)
    {
        $datas = [
            'header' => [],
            'rows' => []
        ];

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

        $type='ministere';
        $programm=null;
        $action=null;
        $rows=[];
        $ministry=null;
        $commune=null;

        if($request->getSession()->get('_ministry')){
            $type='ministere';
            $ministry = $this->getDoctrine()->getRepository(Ministry::class)->findOneBy(['id' => $request->getSession()->get('_ministry')]);
        }

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

        if($request->getSession()->get('_departement')){
            $type='commune';
            $departement = $this->getDoctrine()->getRepository(Department::class)->findOneBy(['id' => $request->getSession()->get('_departement')]);
        }


        $form = $this->createForm(MinistrySelType::class, null, [
            'sessionYear' => $request->getSession()->get('_year'),
            'sessionMinistry' => $request->getSession()->get('_ministry'),
            'sessionCommune' => $request->getSession()->get('_commune'),
            'sessionDepartement' => $request->getSession()->get('_departement'),
        ]);

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

            if ($this->isGranted('ROLE_MANAGER')) {
                $type = $form->get('type')->getData();
            }
            if ($this->isGranted('ROLE_MANAGER') || $this->isGranted('ROLE_DDD')) {
                if($type == 'ministere') {
                    $ministry= $form->get('entite')->getData();
                }elseif($type == 'commune'){
                    $commune = $form->get('entite')->getData();
                }
            }

            $previousType = $request->getSession()->get('previous_type');

            if ($type !== $previousType) {
                $form = $this->createForm(MinistrySelType::class, null, [
                    'sessionYear' => $request->getSession()->get('_year'),
                    'sessionMinistry' => $request->getSession()->get('_ministry'),
                    'sessionCommune' => $request->getSession()->get('_commune'),
                    'sessionDepartement' => $request->getSession()->get('_departement'),
                    'sessionType' => $type,
                ]);
            } 
            $request->getSession()->set('previous_type', $type);
        }

            $sumSensitivity = 0;
            $nbActivity = 0;
            $amount = 0;
            $rows =[];

            if($type=='ministere' && $ministry){
                $rows = $this->getDoctrine()
                    ->getRepository(Notation::class)
                    ->getActivityByMinistry($year, $ministry, $programm, $action, true);
            }else if($type=='commune' && $commune){
                $rows = $this->getDoctrine()
                    ->getRepository(Notation::class)
                    ->getActivityByMinistryNew($year, $commune, $programm, $action, true);
            }
        
            foreach ($rows as $key => $row) {
                $sumSensitivity += $rows[$key]['sensitivity'];
                $rows[$key]['sensitivity'] = round($rows[$key]['sensitivity'], 2);
                $amount += $rows[$key]['amount'];
                $nbActivity++;
            }
            $header['year'] = $year;
            $header['level1']['name'] = $ministry ? $ministry->getName() : '';
            $header['level1']['code'] = $ministry ? $ministry->getCode() : '';
            $header['level2']['name'] = $programm ? $programm->getName() : '';
            $header['level2']['code'] = $programm ? $programm->getCode() : '';
            $header['level3']['name'] = $action ? $action->getName() : '';
            $header['level3']['code'] = $action ? $action->getCode() : '';

            $header['sensitivity'] = $nbActivity ? round($sumSensitivity / $nbActivity, 2) : 0;
            $sumAppreciation = $this->getDoctrine()
                ->getRepository(Appreciation::class)
                ->findOneByValue($header['sensitivity']);
            $header['appreciation'] =  $sumAppreciation ? $sumAppreciation->getName() : '-';
            $header['flag'] =  $sumAppreciation ? $sumAppreciation->getFlag() : 'red';
            $header['amount'] = $amount;
            $header['nbActivity'] = $nbActivity;

            $datas['header'] = $header;
            $datas['rows'] = $rows;  

        return $this->render('output/output_activity_less.html.twig', [
            'datas' => $datas,
            'show' => $ministry || $commune,
            'form' => $form->createView(),
        ]);
    }

    /**
     * @Route("/app/output/nature", name="app_output_nature")
     */
    public function outputNatureAction(Request $request)
    {
        $datas = [
            'header' => [],
            'rows' => []
        ];
        $type='';
        $programm=null;
        $action=null;
        $year = $request->getSession()->get('_year');
        $rows=[];
        $ministry=null;
        $commune=null;

        if($request->getSession()->get('_ministry')){
            $type='ministere';
            $ministry = $this->getDoctrine()->getRepository(Ministry::class)->findOneBy(['id' => $request->getSession()->get('_ministry')]);
        }

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

        if($request->getSession()->get('_departement')){
            $type='commune';
            $departement = $this->getDoctrine()->getRepository(Department::class)->findOneBy(['id' => $request->getSession()->get('_departement')]);
        }


        $form = $this->createForm(MinistrySelType::class, null, [
            'sessionYear' => $request->getSession()->get('_year'),
            'sessionMinistry' => $request->getSession()->get('_ministry'),
            'sessionCommune' => $request->getSession()->get('_commune'),
            'sessionDepartement' => $request->getSession()->get('_departement'),
        ]);

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

            if ($this->isGranted('ROLE_MANAGER')){
                $type = $form->get('type')->getData();
                $entite = $form->get('entite')->getData();
                if($type == 'ministere') {
                    $ministry= $form->get('entite')->getData();
                }elseif($type == 'commune'){
                    $commune = $form->get('entite')->getData();
                }
            }
            
        }

            $sumSensitivity = 0;
            $nbActivity = 0;
            $amount = 0;
            if($type=='ministere'){
                $rows = $this->getDoctrine()
                ->getRepository(Nature::class)
                ->getSensitivityByMinistry($year, $ministry, $programm, $action);
            }else if($type=='commune'){
                $rows = $this->getDoctrine()
                ->getRepository(Nature::class)
                ->getSensitivityByCommune($year, $commune, $programm, $action);
            }
            

            foreach ($rows as $key => $row) {
                $nbActivity += $rows[$key]['nbActivity'];
                $sumSensitivity += $rows[$key]['sumSensitivity'];
                $amount += $rows[$key]['amount'];
            }
            $header['year'] = $year;
            if($type=='ministere'){
                $header['level1']['name'] = $ministry ? $ministry->getName() : '';
                $header['level1']['code'] = $ministry ? $ministry->getCode() : '';
            }else if($type=='commune'){
                $header['level1']['name'] = $commune ? $commune->getName() : '';
                $header['level1']['code'] = $commune ? $commune->getCode() : '';
            }
            $header['level2']['name'] = $programm ? $programm->getName() : '';
            $header['level2']['code'] = $programm ? $programm->getCode() : '';
            $header['level3']['name'] = $action ? $action->getName() : '';
            $header['level3']['code'] = $action ? $action->getCode() : '';

            $header['sensitivity'] = $nbActivity ? round($sumSensitivity / $nbActivity, 2) : 0;
            $sumAppreciation = $this->getDoctrine()
                ->getRepository(Appreciation::class)
                ->findOneByValue($header['sensitivity']);
            $header['appreciation'] = $sumAppreciation ? $sumAppreciation->getName() : '-';
            $header['flag'] = $sumAppreciation ? $sumAppreciation->getFlag() : 'red';
            $header['amount'] = $amount;
            $header['nbActivity'] = $nbActivity;

            $datas['header'] = $header;
            $datas['rows'] = $rows;  

        return $this->render('output/output_nature.html.twig', [
            'datas' => $datas,
            'show' => $ministry || $commune,
            'form' => $form->createView(),
        ]);
    }


    /**
     * @Route("/app/output/cost", name="app_output_cost")
     */
    public function outputCostAction(Request $request)
    {
        $datas = [
            'header' => [],
            'rows' => []
        ];
        $year = $request->getSession()->get('_year');

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

        $form->handleRequest($request);
        if ($form->isSubmitted() && $form->isValid()) {
            $goal = $form->get('goal')->getData();
            $target = $form->get('target')->getData();
            
            if ( $goal ) {
                $sumSensitivity = 0;
                $nbActivity = 0;
                $amount = 0;
                $rows = $this->getDoctrine()
                    ->getRepository(Ministry::class)
                    ->getSensitivity($year, $goal, $target);

                foreach ($rows as $key => $row) {
                    $rows[$key]['sensitivity'] = round($rows[$key]['sensitivity'], 2);
                    $appreciation = $this->getDoctrine()
                        ->getRepository(Appreciation::class)
                        ->findOneByValue($rows[$key]['sensitivity']);
                    $rows[$key]['appreciation'] = $appreciation->getName();
                    $rows[$key]['flag'] = $appreciation->getFlag();

                    $sumSensitivity += $rows[$key]['sumSensitivity'];
                    $nbActivity += $rows[$key]['nbActivity'];
                    $amount += $rows[$key]['amount'];
                }
                $header['year'] = $year;
                $header['level1']['name'] = $goal ? $goal->getName() : '';
                $header['level1']['code'] = $goal ? $goal->getCode() : '';
                $header['level2']['name'] = $target ? $target->getName() : '';
                $header['level2']['code'] = $target ? $target->getCode() : '';
                $header['level3']['name'] = '';
                $header['level3']['code'] = '';

                $header['sensitivity'] = $nbActivity ? round($sumSensitivity / $nbActivity, 2) : 0;
                $sumAppreciation = $this->getDoctrine()
                    ->getRepository(Appreciation::class)
                    ->findOneByValue($header['sensitivity']);
                $header['appreciation'] = $sumAppreciation->getName();
                $header['flag'] = $sumAppreciation->getFlag();
                $header['amount'] = $amount;
                $header['nbActivity'] = $nbActivity;

                $datas['header'] = $header;
                $datas['rows'] = $rows;  
            }
        }

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

    /**
     * @Route("/app/output/commune", name="app_output_commune")
     */
    public function outputCommuneAction(Request $request)
    {
        $datas = [
            'header' => [],
            'rows' => []
        ];
        $year = $request->getSession()->get('_year');

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

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

            if ( $department && $commune ) {
                $sumSensitivity = 0;
                $nbActivity = 0;
                $amount = 0;
                $rows = $this->getDoctrine()
                    ->getRepository(Notation::class)
                    ->getActivityByCommune($year, $department, $commune);
            
                foreach ($rows as $key => $row) {
                    $sumSensitivity += $rows[$key]['sensitivity'];
                    $rows[$key]['sensitivity'] = round($rows[$key]['sensitivity'], 2);
                    $amount += $rows[$key]['amount'];
                    $nbActivity++;
                }
                $header['year'] = $year;
                $header['level1']['name'] = $department ? $department->getName() : '';
                $header['level1']['code'] = $department ? $department->getCode() : '';
                $header['level2']['name'] = $commune ? $commune->getName() : '';
                $header['level2']['code'] = $commune ? $commune->getCode() : '';
                $header['level3']['name'] = '';
                $header['level3']['code'] = '';

                $header['sensitivity'] = $nbActivity ? round($sumSensitivity / $nbActivity, 2) : 0;
                $sumAppreciation = $this->getDoctrine()
                    ->getRepository(Appreciation::class)
                    ->findOneByValue($header['sensitivity']);
                $header['appreciation'] = $sumAppreciation ? $sumAppreciation->getName() : '-';
                $header['flag'] = $sumAppreciation ? $sumAppreciation->getFlag() : 'red';
                $header['amount'] = $amount;
                $header['nbActivity'] = $nbActivity;

                $datas['header'] = $header;
                $datas['rows'] = $rows;  
            }
        }

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


    /**
     * @Route("/app/output/arrondissement", name="app_output_arrondissement")
     */
    public function outputArrondissementAction(Request $request)
    {
        $datas = [
            'header' => [],
            'rows' => []
        ];
        $year = $request->getSession()->get('_year');
        $arrondissement = null;

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

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

            if ( $department && $commune && $arrondissement) {
                $sumSensitivity = 0;
                $nbActivity = 0;
                $amount = 0;
                $rows = $this->getDoctrine()
                    ->getRepository(Notation::class)
                    ->getActivityByArrondissement($year, $department, $commune, $arrondissement);
            
                foreach ($rows as $key => $row) {
                    $sumSensitivity += $rows[$key]['sensitivity'];
                    $rows[$key]['sensitivity'] = round($rows[$key]['sensitivity'], 2);
                    $amount += $rows[$key]['amount'];
                    $nbActivity++;
                }
                $header['year'] = $year;
                $header['level1']['name'] = $department ? $department->getName() : '';
                $header['level1']['code'] = $department ? $department->getCode() : '';
                $header['level2']['name'] = $commune ? $commune->getName() : '';
                $header['level2']['code'] = $commune ? $commune->getCode() : '';
                $header['level3']['name'] = $arrondissement ? $arrondissement->getName() : '';
                $header['level3']['code'] = $arrondissement ? $arrondissement->getCode() : '';

                $header['sensitivity'] = $nbActivity ? round($sumSensitivity / $nbActivity, 2) : 0;
                $sumAppreciation = $this->getDoctrine()
                    ->getRepository(Appreciation::class)
                    ->findOneByValue($header['sensitivity']);
                $header['appreciation'] = $sumAppreciation ? $sumAppreciation->getName() : '-';
                $header['flag'] = $sumAppreciation ? $sumAppreciation->getFlag() :  'red';
                $header['amount'] = $amount;
                $header['nbActivity'] = $nbActivity;

                $datas['header'] = $header;
                $datas['rows'] = $rows;  
            }
        }

        return $this->render('output/output_arrondissement.html.twig', [
            'datas' => $datas,
            'arrondissement' => $arrondissement,
            'form' => $form->createView(),
        ]);
    }


    /**
     * @Route("/app/output/indicateurs/commune", name="app_indicateurs_communes")
     */
    public function outputIndicateurCommuneAction(Request $request)
    {

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

        $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();
            $commune = $this->getDoctrine()->getRepository(Commune::class)->findOneBy(['id' => $commune]);
        }

        return $this->render('output/output_indicateur_commune.html.twig', [
            'year' => $year,
            'commune' => $commune,
            'indicators' => $indicators,
            'form' => $form->createView(),
        ]);
    }

    /**
     * @Route("/app/output/indicateurs", name="app_output_indicateur")
     */
    public function outputIndicateurAction(Request $request)
    {

        $goal=null;
        $indicateurs=null;
        $year = $request->getSession()->get('_year');
        $form = $this->createForm(GoalOnlySelType::class, null, array(
        ));

        if ($this->isGranted('ROLE_MANAGER')){
        }else{
            $ministry = $request->getSession()->get('_ministry');
            $ministry = $this->getDoctrine()->getRepository(Ministry::class)->findOneBy(['id' => $ministry->getId()]);
            $indicateurs=$ministry->getIndicators();
        }

        $form->handleRequest($request);
        if ($form->isSubmitted() && $form->isValid()) {
            $goal = $form->get('goal')->getData();
            $goal = $this->getDoctrine()->getRepository(Goal::class)->findOneBy(['id' => $goal]);
    
            $indicateurs=null;
            foreach ($goal->getTargets() as $target) {
                foreach ($target->getIndicators() as $indicator) {
                        if(!$indicator->isCommunal()){
                            $indicateurs[] = $indicator;
                        }
                }
            }
        }

        return $this->render('output/output_indicateur.html.twig', [
            'year' => $year,
            'goal' => $goal,
            'indicateurs' => $indicateurs,
            'form' => $form->createView(),
        ]);
    }

     /**
     * @Route("/app/output/export/indicateurs/{id}", name="app_export_indicateurs", defaults={"id"=0})
     */
    public function exportIndicateurAction(Request $request, SettingsManager $settingsManager, $id=0)
    {

        $year = $request->getSession()->get('_year');
        $goal = $this->getDoctrine()->getRepository(Goal::class)->findOneBy(['id' => $id]);

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

        $sheet = $spreadsheet->getSheet(0);
        $sheet->setCellValue('C1', $year-4);
        $sheet->setCellValue('D1', $year-3);
        $sheet->setCellValue('E1', $year-2);
        $sheet->setCellValue('F1', $year-1);
        $sheet->setCellValue('G1', $year);

        if ($this->isGranted('ROLE_MANAGER')){
            $fileName = $goal ? 'Indicateurs-ODD'.$goal->getCode().'-' . $year . '.xlsx' : 'Indicateurs-ODD-' . $year . '.xlsx';
            //Création des feuilles par indicateur

            if($goal){

                $spreadsheet->getSheet(0)->setTitle("ODD ".$goal->getCode());
                $i=0;
                    $sheet = $spreadsheet->getSheet($i);
                    $line=2;
                    foreach ($goal->getTargets() as $target) {
                        foreach ($target->getIndicators() as $indicateur) {
                            $sheet->setCellValue('A' . $line, $indicateur->getCode());
                            $sheet->setCellValue('B' . $line, $indicateur->getName());
                            $sheet->setCellValue('C'. $line, $settingsManager->getValeurIndicateur($year-4,$indicateur));
                            $sheet->setCellValue('D'. $line, $settingsManager->getValeurIndicateur($year-3,$indicateur));
                            $sheet->setCellValue('E'. $line, $settingsManager->getValeurIndicateur($year-2,$indicateur));
                            $sheet->setCellValue('F'. $line, $settingsManager->getValeurIndicateur($year-1,$indicateur));
                            $sheet->setCellValue('G'. $line, $settingsManager->getValeurIndicateur($year,$indicateur));
                            $line++;
                        }
                    }

            }else{

                $spreadsheet->getSheet(0)->setTitle("ODD 1 ");
                for ($i = 2; $i <=17; $i++) {
                    $clonedWorksheet = clone $spreadsheet->getSheet(0);
                    $clonedWorksheet->setTitle("ODD " . $i);
                    $spreadsheet->addSheet($clonedWorksheet);
                }
                $i=0;
                $goals = $this->getDoctrine()->getRepository(Goal::class)->findBy(['agenda' => 2030, 'commune'=> false]);
                foreach ($goals as $goal) {
                    $sheet = $spreadsheet->getSheet($i);
                    $line=2;
                    foreach ($goal->getTargets() as $target) {
                        foreach ($target->getIndicators() as $indicateur) {
                            $sheet->setCellValue('A' . $line, $indicateur->getCode());
                            $sheet->setCellValue('B' . $line, $indicateur->getName());
                            $sheet->setCellValue('C'. $line, $settingsManager->getValeurIndicateur($year-4,$indicateur));
                            $sheet->setCellValue('D'. $line, $settingsManager->getValeurIndicateur($year-3,$indicateur));
                            $sheet->setCellValue('E'. $line, $settingsManager->getValeurIndicateur($year-2,$indicateur));
                            $sheet->setCellValue('F'. $line, $settingsManager->getValeurIndicateur($year-1,$indicateur));
                            $sheet->setCellValue('G'. $line, $settingsManager->getValeurIndicateur($year,$indicateur));
                            $line++;
                        }
                    }
                    $i++;
                }

            }
            

        }else{
            $ministry = $request->getSession()->get('_ministry');
            $ministry = $this->getDoctrine()->getRepository(Ministry::class)->findOneBy(['id' => $ministry->getId()]);

            
            $fileName = "Indicateurs-".$ministry->getCode() . "-".$year . '.xlsx';

            $spreadsheet->getActiveSheet()->setTitle("Indicateurs-".$ministry->getCode());
            $line=2;
            foreach ($ministry->getIndicators() as $indicateur) {
                $sheet->setCellValue('A' . $line, $indicateur->getCode());
                $sheet->setCellValue('B' . $line, $indicateur->getName());
                $sheet->setCellValue('C'. $line, $settingsManager->getValeurIndicateur($year-4,$indicateur));
                $sheet->setCellValue('D'. $line, $settingsManager->getValeurIndicateur($year-3,$indicateur));
                $sheet->setCellValue('E'. $line, $settingsManager->getValeurIndicateur($year-2,$indicateur));
                $sheet->setCellValue('F'. $line, $settingsManager->getValeurIndicateur($year-1,$indicateur));
                $sheet->setCellValue('G'. $line, $settingsManager->getValeurIndicateur($year,$indicateur));
                $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/output/pta", name="app_output_pta")
     */
    public function outputPTAAction(Request $request, SettingsManager $settingsManager)
    {
        
        $pilier=null;
        $year = $request->getSession()->get('_year');
        $piliers = $this->getDoctrine()->getRepository(Mainstay::class)->findAll();
        $form = $this->createForm(MainstayOnlySelType::class, null, array(
        ));

        $form->handleRequest($request);
        if ($form->isSubmitted() && $form->isValid()) {
            $year = $request->getSession()->get('_year');
            $pilier = $form->get('mainstay')->getData();
        }
        
        return $this->render('output/output_pta.html.twig', [
            'year' => $year,
            'pilier' => $pilier,
            'piliers' => $piliers,
            'form' => $form->createView(),
        ]);
    }

    /**
     * @Route("/app/output/export/pta", name="app_output_export_pta")
     */
    public function outputExportPTAAction(Request $request)
    {
    }

    /**
     * @Route("/app/output/export/indicateur/ministere/{id}", name="app_export_indicateurs_ministere")
     */
    public function exportIndicateursMinistere(Request $request, $id, SettingsManager $settingsManager)
    {
        $year = $request->getSession()->get('_year');
        $indicators=null;
        $name='';
        if($id>0){
            $ministry = $this->getDoctrine()->getRepository(Ministry::class)->findOneBy(['id' => $id]);
            $indicators = $ministry->getIndicators();
            $name=$ministry->getCode();
        }else{
            $name='INStaD';
            $indicators = $this->getDoctrine()->getRepository(Indicator::class)->findBy(['instad' => true]);
        }

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

        $sheet = $spreadsheet->getSheet(0);
        $sheet->setCellValue('C1', $year);


        $fileName = "Indicateurs-".$name . "-".$year . '.xlsx';

            $spreadsheet->getActiveSheet()->setTitle($name);
            $line=2;
            foreach ($indicators as $indicateur) {
                $sheet->setCellValue('A' . $line, $indicateur->getCode());
                $sheet->setCellValue('B' . $line, $indicateur->getName());
                $sheet->setCellValue('C'. $line, $settingsManager->getValeurIndicateur($year,$indicateur,1,NULL));
                $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;
    }

}