<?php

namespace App\Controller;

use App\Entity\Programm;
use App\Entity\Commune;
use App\Entity\Department;
use App\Entity\Ministry;
use App\Entity\Direction;
use App\Entity\Action;
use App\Entity\Activity;
use App\Entity\StructureYear;
use App\Form\ActivityType;
use App\Form\UploadType;
use App\Form\ValidateType;
use App\Form\FormatType;
use App\Form\Model\ActivityCollectionModel;
use App\Utils\PlanUtils;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\Routing\Annotation\Route;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\HttpFoundation\ResponseHeaderBag;
use Symfony\Component\HttpFoundation\StreamedResponse;
use App\Service\SettingsManager;
use Sensio\Bundle\FrameworkExtraBundle\Configuration\IsGranted;

class ProcessingController extends AbstractController
{
    private $programms = [];
    private $actions = [];
    private $directions = [];

    private $labels = [
        'vertical' => [
            'label1' => 'Ministère',
            'label2' => 'Programme',
            'label3' => 'Action',
        ],

        'horizontal' => [
            'label1' => 'ODD',
            'label2' => 'Cible'
        ],

        'oblique' => [
            'label1' => 'Département',
            'label2' => 'Commune'
        ]
    ];

    /**
     * @Route("/app/format", name="app_format")
     * 
     */
     public function formatAction(Request $request)
     {
        set_time_limit(0);
        $request->setLocale('fr');
        
        $fileOk=true;
        $commune=null;
        $ministry=true;

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

        $form->handleRequest($request);
        if ($form->isSubmitted() && $form->isValid()) {
            $programme = $form->get('programm')->getData();
            $annee = $request->getSession()->get('_year');
            if($request->getSession()->get('_ministry')){
                $ministry = $this->getDoctrine()->getRepository(Ministry::class)->findOneBy(['id' => $request->getSession()->get('_ministry')]);
                $fileName = 'PTA_'.$ministry->getCode().'_'.$programme->getCode().'_Format_'.$annee.'.xlsx';
            }else if($request->getSession()->get('_commune')){
                $commune = $this->getDoctrine()->getRepository(Commune::class)->findOneBy(['id' => $request->getSession()->get('_commune')]);
                $fileName = 'PTA_'.$commune->getName().'_'.$programme->getCode().'_Format_'.$annee.'.xlsx';
            }else if ($this->isGranted('ROLE_DDD')) {
                $commune = $form->get('entite')->getData();
                $fileName = 'PTA_'.$commune->getName().'_'.$programme->getCode().'_Format_'.$annee.'.xlsx';
            }else{
                $type = $form->get('type')->getData();
                if($type == 'ministere') {
                    $ministry= $form->get('entite')->getData();
                    $fileName = 'PTA_'.$ministry->getCode().'_'.$programme->getCode().'_Format_'.$annee.'.xlsx';
                }elseif($type == 'commune'){
                    $commune = $form->get('entite')->getData();
                    $fileName = 'PTA_'.$commune->getName().'_'.$programme->getCode.'_Format_'.$annee.'.xlsx';
                }
            }

            $start = $form->get('start')->getData();

            $code=$form->get('code')->getData();
            $libelle=$form->get('libelle')->getData();
            $direction=$form->get('direction')->getData();
            $cout = $form->get('cout')->getData();
            $inputFileName = $form->get('attachment')->getData();
            $inputFileType = IOFactory::identify($inputFileName);
            $reader = IOFactory::createReader($inputFileType);
            $reader->setReadDataOnly(true);
            $spreadsheet = $reader->load($inputFileName);

            $sheet = $spreadsheet->getSheet(0);

            //Vérification de quelques éléments pour s'assurer que le fichier vient de BAI
            $verifBudget=$sheet->getCell('G11')->getCalculatedValue()=='Budget';
            $verifCode=$sheet->getCell('A12')->getCalculatedValue()=='Code';
            $verifResponsable=$sheet->getCell('N12')->getCalculatedValue()=='Responsable';


            if(!($verifBudget && $verifCode && $verifResponsable)){
                 $this->addFlash('error', 'Le fichier de PTA que vous soumettez semble ne pas être tiré de la plateforme BAI. Veuillez extraire votre fichier de cette plateforme');
                return $this->render('processing/format_form.html.twig', [
                    'form' => $form->createView(),
                ]);
            }

            $highestRow = $sheet->getHighestRow();

            $maquette = $this->getParameter('kernel.project_dir') . '/public/upload/pta_format.xlsx';

            $spreadsheetSortie = IOFactory::load($maquette);
            $spreadsheetSortie->getProperties()
                ->setCreator("DGCS-ODD")
                ->setTitle("Format SIGODD");
                
            $sheetSortie = $spreadsheetSortie->getSheet(0);
            $sheetSortie->setTitle("PTA Format");

            $l=1;
            if($programme->getMinistry()){
                $cle = 'M'.str_pad($programme->getMinistry()->getId(), 2, '0', STR_PAD_LEFT).'-P'.str_pad($programme->getId(), 5, '0', STR_PAD_LEFT);
            }else if($programme->getCommune()){
                $cle = 'C'.str_pad($programme->getCommune()->getId(), 2, '0', STR_PAD_LEFT).'-P'.str_pad($programme->getId(), 5, '0', STR_PAD_LEFT);
            }
            $sheetSortie->setCellValue('A' . $l, $cle);
            $sheetSortie->setCellValue('B' . $l, $programme->getName());
            $sheetSortie->setCellValue('D' . $l, '');
            $l++;
            $somme=0;
            for ($row = $start; $row <= $highestRow; ++$row) {
                    try {
                        $value = $sheet->getCell($code.$row)->getCalculatedValue();
                        if ($value === null || $value === '') {
                            continue;
                        }
                        $count = substr_count($value, '.');

                        $numero = $sheet->getCell($code . $row)->getCalculatedValue();
                        $nom = $sheet->getCell($libelle . $row)->getCalculatedValue();
                        $structure = $sheet->getCell($direction . $row)->getCalculatedValue();
                        $montant = $sheet->getCell($cout . $row)->getCalculatedValue();
                        $montant = str_replace(' ', '', $montant);

                        $somme+=$montant;

                        if ($count === 0 && $nom) {
                        }
                        elseif ($count === 1 && $nom) {
                                $sheetSortie->setCellValue('A' . $l, 'Action '.$numero);
                                $sheetSortie->setCellValue('B' . $l, $nom);
                                $sheetSortie->setCellValue('D' . $l, $montant);
                                $l++;
                        } elseif($count === 3 && $nom) {
                            $dir=null;
                            if($commune){
                                $dir = $this->getDoctrine()->getRepository(Direction::class)->findOneBy(['code' => $structure, 'commune' => $commune]);
                            }else if($ministry){
                                $dir = $this->getDoctrine()->getRepository(Direction::class)->findOneBy(['code' => $structure, 'ministry' => $ministry]);
                            }

                            if($dir){
                                $dir->addYear($request->getSession()->get('_year'));
                                if($dir->getMinistry()){
                                    $cle = 'M'.str_pad($dir->getMinistry()->getId(), 2, '0', STR_PAD_LEFT).'-D'.str_pad($dir->getId(), 5, '0', STR_PAD_LEFT);
                                }else if($dir->getCommune()){
                                    $cle = 'C'.str_pad($dir->getCommune()->getId(), 2, '0', STR_PAD_LEFT).'-D'.str_pad($dir->getId(), 5, '0', STR_PAD_LEFT);
                                }
                                $sheetSortie->setCellValue('A' . $l, 'Activité '.$numero);
                                $sheetSortie->setCellValue('B' . $l, $nom);
                                $sheetSortie->setCellValue('C' . $l, $dir ? $cle : $structure);
                                $sheetSortie->setCellValue('D' . $l, $montant);
                                $l++;
                            }
                            else{
                                if($structure){
                                    $this->addFlash('error', 'La Direction '.$structure. ' n\'existe pas. Veuillez créer toutes vos directions.');
                                }else{
                                    $this->addFlash('error', 'Vous n\'avez pas affecté une direction responsable à toutes vous activités. Veuillez le faire et rassurez-vous que toutes les directions sont créées.');
                                }
                                return $this->render('processing/format_form.html.twig', [
                                    'form' => $form->createView(),
                                ]); 
                            }
                        }
                    }catch (\PhpOffice\PhpSpreadsheet\Exception $e) {
                        $fileOk=false;
                        $errorMessage = "Erreur lors de la lecture de la cellule {$cout}{$row}. Veuillez vérifier la valeur inscrite dans cette cellule et recharger le fichier";
                        $this->addFlash('error', $errorMessage);
                        break;
                    }
                }
                $sheetSortie->setCellValue('D1', $somme);

                //Télécharchement du fichier
            if($fileOk){
                $writer = new Xlsx($spreadsheetSortie);
                $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;
            }

        }

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

    /**
     * @Route("/app/upload", name="app_upload")
     * 
     */
	 /* @IsGranted({"ROLE_ADMIN", "ROLE_FOCAL"}) */
    public function uploadAction(Request $request, SettingsManager $settingsManager)
    {
        $sessionYear = $request->getSession()->get('_year');
        $structureYear = null;

        if($this->isGranted('ROLE_FOCAL') && $request->getSession()->get('_ministry')){
            $structureYear = $settingsManager->getStructureYear($sessionYear, null);
        }elseif($this->isGranted('ROLE_FOCAL') &&  $request->getSession()->get('_commune')){
            $structureYear = $settingsManager->getStructureYear($sessionYear, null, $request->getSession()->get('_commune'));
        }
        $autorisation = $this->isGranted('ROLE_FOCAL') && $structureYear && $structureYear->getPta();
        
        $form = $this->createForm(UploadType::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);
        $type=null;
        if ($form->isSubmitted() && $form->isValid()) {
            if($request->getSession()->get('_ministry')){
                $type='ministere';
                $entite=$request->getSession()->get('_ministry');
            }else if ($request->getSession()->get('_commune')){
                $type='commune';
                $entite=$request->getSession()->get('_commune');
            }else{
                $type = $form->get('type')->getData();
                $entite = $form->get('entite')->getData();
            }
            if($type){
            $inputFileName = $form->get('attachment')->getData(); 
            $inputFileType = IOFactory::identify($inputFileName);
            $reader = IOFactory::createReader($inputFileType);
            $reader->setReadDataOnly(true);
            $spreadsheet = $reader->load($inputFileName);
            
            $worksheet = $spreadsheet->getActiveSheet();
            $highestRow = $worksheet->getHighestRow();
            $fileOK = true;
            for ($row = 1; $row <= $highestRow; ++$row) {
                $value = $worksheet->getCellByColumnAndRow(1, $row)->getValue();

                if ( (substr($value, 0, 6) != 'Action') and (substr($value, 0, 7) != 'Activit') ) {
                    $programmKey = $value;
                    if ( (($type=='ministere' and substr($value, 0, 1) == 'M') or ($type=='commune' and substr($value, 0, 1) == 'C')) and (substr($value, 4, 1) == 'P') ) {
                        if (!key_exists($programmKey, $this->programms)) {
                            if($type=='ministere') {
                                $programm = $this->getDoctrine()
                                ->getRepository(Programm::class)
                                ->findOneBy([
                                    'id' => (int)substr($value, 5), 
                                    'ministry' => $entite->getId()
                                ]);
                            }else{
                                $programm = $this->getDoctrine()
                                ->getRepository(Programm::class)
                                ->findOneBy([
                                    'id' => (int)substr($value, 5), 
                                    'commune' => $entite->getId()
                                ]);
                            }
                            if ($programm) {
                                $this->programms[$programmKey] = $programm;
                                if (!$programm->hasYear((int)$sessionYear)) {
                                    $this->addFlash('error', "L'année ". $sessionYear ." n'est pas activé pour le programme: ". $programm->getName());
                                    $fileOK = false;
                                    break;
                                }
                            } else {
                                $this->addFlash("error", "Le programme de clé ". $programmKey . " n'existe pas dans ce ministère");
                                $fileOK = false;
                                break;
                            }
                        }
                    } else {
                        $this->addFlash('error', "La clé ". $programmKey . " n'est plas une clé de programme");
                        $fileOK = false;
                        break;
                    }
                }

                if ( substr($value, 0, 7) == 'Activit' ) {
                    $directionKey = $worksheet->getCellByColumnAndRow(3, $row)->getValue();
                    if ( (($type=='ministere' and substr($directionKey, 0, 1) == 'M') or ($type=='commune' and  substr($directionKey, 0, 1) == 'C')) and (substr($directionKey, 4, 1) == 'D') ) {
                        if (!key_exists($directionKey, $this->directions)) {
                            if($type=='ministere') {
                                $direction = $this->getDoctrine()
                                    ->getRepository(Direction::class)
                                    ->findOneBy([
                                        'id' => (int)substr($directionKey, 5), 
                                        'ministry' => $entite->getId()
                                    ]);
                            }else{
                                $direction = $this->getDoctrine()
                                    ->getRepository(Direction::class)
                                    ->findOneBy([
                                        'id' => (int)substr($directionKey, 5), 
                                        'commune' => $entite->getId()
                                    ]);
                            }
                            if ($direction) {
                                $this->directions[$directionKey] = $direction;
                                if (!$direction->hasYear((int)$sessionYear)) {
                                    $this->addFlash("error", "L'année ". $sessionYear ." n'est pas activé pour la direction: ". $direction->getName());
                                    $fileOK = false;
                                    break;
                                }
                            } else {
                                $this->addFlash("error", "La direction ". $directionKey . " n'esxiste pas dans ce ministère");
                                $fileOK = false;
                                break;
                            }
                        }
                    } else {
                        $this->addFlash('error', "La clé ". $directionKey . " n'est plas une clé de direction");
                        $fileOK = false;
                        break;
                    }
                }
            }


            $nbAction = 0;
            $nbActivity = 0;
            $programme= $worksheet->getCellByColumnAndRow(1, 1)->getValue();
            $programme = $this->getDoctrine()
                                ->getRepository(Programm::class)
                                ->findOneBy([
                                    'id' => (int)substr($programme, 5), 
                                    'ministry' => $entite->getId()
                                ]);

            if ($fileOK) {
                //Suppression des anciens PTA
                if($type=='ministere'){
                    $this->getDoctrine()->getManager()->beginTransaction();

                    try {
                        // Supprimer les activités
                        $this->getDoctrine()->getManager()->createQuery(
                            'DELETE FROM App\Entity\Activity actv
                            WHERE actv.year = :year
                            AND actv.action IN (
                                SELECT a.id
                                FROM App\Entity\Action a
                                JOIN a.programm p
                                WHERE p.ministry = :ministry
                                AND p = :programme
                            )'
                        )->setParameters(['programme'=>$programme, 'ministry' => $entite, 'year' => $sessionYear])->execute();

                        // Supprimer les actions
                        $this->getDoctrine()->getManager()->createQuery(
                            'DELETE FROM App\Entity\Action a
                            WHERE a.programm IN (
                                SELECT p.id
                                FROM App\Entity\Programm p
                                WHERE p.ministry = :ministry
                                AND p = :programme
                            )'
                        )->setParameters(['programme'=>$programme, 'ministry'=>$entite])->execute();

                        $this->getDoctrine()->getManager()->commit();
                    } catch (\Exception $e) {
                        $this->getDoctrine()->getManager()->rollback();
                        throw $e;
                    }

                }else if($type=='commune'){
                    $this->getDoctrine()->getManager()->beginTransaction();

                    try {
                        // Supprimer les activités
                        $this->getDoctrine()->getManager()->createQuery(
                            'DELETE FROM App\Entity\Activity actv
                            WHERE actv.year = :year
                            AND actv.action IN (
                                SELECT a.id
                                FROM App\Entity\Action a
                                JOIN a.programm p
                                WHERE p.commune = :commune 
                                AND p = :programme
                            )'
                        )->setParameters(['programme'=>$programme, 'commune' => $entite, 'year' => $sessionYear])->execute();

                        // Supprimer les actions
                        $this->getDoctrine()->getManager()->createQuery(
                            'DELETE FROM App\Entity\Action a
                            WHERE a.programm IN (
                                SELECT p.id
                                FROM App\Entity\Programm p
                                WHERE p.commune = :commune 
                                AND p = :programme
                            )'
                        )->setParameters(['programme'=>$programme, 'commune', $entite])->execute();

                        $this->getDoctrine()->getManager()->commit();
                    } catch (\Exception $e) {
                        $this->getDoctrine()->getManager()->rollback();
                        throw $e;
                    }
                }

                $currentProgramm = null;
                for ($row = 1; $row <= $highestRow; ++$row) {
                    $value = $worksheet->getCellByColumnAndRow(1, $row)->getValue();
                    
                    # traitement du programme
                    if ( (($type=='ministere' and substr($value, 0, 1) == 'M') or ($type=='commune' and substr($value, 0, 1) == 'C')) and (substr($value, 4, 1) == 'P') ) {
                        $currentProgramm = $this->programms[$value];
                        
                    # traitement du l'action
                    } elseif (substr($value, 0, 6) == 'Action') {
                        $actionCode = substr($value, 7);
                        $actionName = $worksheet->getCellByColumnAndRow(2, $row)->getValue();

                        $action = new Action();
                        $action->setCode(trim($actionCode));
                        $action->setName(trim($actionName));
                        $action->setYear($sessionYear);
                        $action->setProgramm($currentProgramm);
                        $action->setCreatedBy($this->getUser());
                        $currentAction = $action;

                        $this->getDoctrine()->getManagerForClass(Action::class)->persist($action);
                        $nbAction += 1;

                    } elseif (substr($value, 0, 7) == 'Activit') {
                        $activityCode = substr($value, 9);
                        $activityName = $worksheet->getCellByColumnAndRow(2, $row)->getValue();
                        $directionKey = $worksheet->getCellByColumnAndRow(3, $row)->getValue();
                        $activityAmount = $worksheet->getCellByColumnAndRow(4, $row)->getValue();

                        $activity = new Activity();
                        $activity->setCode(trim($activityCode));
                        $activity->setName(trim($activityName));
                        $activity->setYear($sessionYear);
                        $activity->setAction($currentAction);
                        $activity->setDirection($this->directions[$directionKey]);
                        $activity->setCreatedBy($this->getUser());
                        $activity->setAmount((int)str_replace(' ', '', $activityAmount));

                        $this->getDoctrine()->getManagerForClass(Activity::class)->persist($activity);
                        $nbActivity += 1;
                    }
                }
                $this->getDoctrine()->getManagerForClass(Programm::class)->flush();

                $this->addFlash('notice', "Chargement effectué avec succès - Nombre d'actions chargées ".$nbAction." - Nombre d'activités chargées ".$nbActivity);

                //return $this->redirectToRoute('app_validate');
            }
            
        }
        }
            
        return $this->render('processing/upload_form.html.twig', [
            'form' => $form->createView(),
            'autorisation' => $autorisation,
            'showCommune' => $request->getSession()->get('_commune') ? 'true' : 'false'
        ]);
    }

    /**
     * @Route("/app/validate/{id}", name="app_validate", defaults={"id"=0})
     * 
     */
	 /* @IsGranted({"ROLE_ADMIN", "ROLE_DPP"}) */
    public function validateAction($id=0, Request $request, SettingsManager $settingsManager)
    { 
        $sessionYear = $request->getSession()->get('_year');
        $structureYear = null;

        if($this->isGranted('ROLE_FOCAL') && $request->getSession()->get('_ministry')){
            $structureYear = $settingsManager->getStructureYear($sessionYear, null);
        }elseif($this->isGranted('ROLE_FOCAL') &&  $request->getSession()->get('_commune')){
            $structureYear = $settingsManager->getStructureYear($sessionYear, null, $request->getSession()->get('_commune'));
        }
        $autorisation = $this->isGranted('ROLE_FOCAL') && $structureYear && $structureYear->getPta();

        $ministry = $this->getDoctrine()
        ->getRepository(Ministry::class)
        ->findOneBy(['id' => $request->getSession()->get('_ministry')]);
        $commune = $this->getDoctrine()
        ->getRepository(Commune::class)
        ->findOneBy(['id' => $request->getSession()->get('_commune')]);
        $departement = $this->getDoctrine()
        ->getRepository(Department::class)
        ->findOneBy(['id' => $request->getSession()->get('_departement')]);

        $activities = [];
        $action = $this->getDoctrine()
            ->getRepository(Action::class)
            ->findOneBy(['id' => (int)$id]);

        $form = $this->createForm(ValidateType::class, $action, [
            '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()) {
            $action = $form->get('action')->getData();

            if ($action) {
                foreach ($action->getActivities() as $activity) {
                    if ($activity->getValidated() == false) {
                        $activities[] = $activity;
                    }
                }
            }
        //}
        
        return $this->render('processing/validate_form.html.twig', [
            'form' => $form->createView(),
            'action' => $action,
            'activities' => $activities,
            'autorisation' => !$autorisation,
            'showCommune' => $request->getSession()->get('_commune') ? 'true' : 'false'
        ]);
    }

    /**
     * @Route("/app/validate/activities/{id}", name="app_validate_activities")
     * 
     */
	 /* @IsGranted({"ROLE_MANAGER", "ROLE_DDD", "ROLE_DPP"}) */
    public function validateActivities($id, Request $request)
    {
        $activities = [];
        $action = $this->getDoctrine()
            ->getRepository(Action::class)
            ->findOneBy(['id' => (int)$id]);

        if ($action) {
            foreach ($action->getActivities() as $activity) {
                if ($activity->getValidated() == false) {
                    //$activities[] = $activity;
                    $activity->setValidatedBy($this->getUser());
                    $activity->setValidated(true);
                }
            }
            $action->setValidatedBy($this->getUser());
            $action->setValidated(PlanUtils::checkActionValidated($action));

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

        $this->addFlash('success', "Validation des activités de l'action ".$action->getName()." effectuée avec succès");

        return $this->redirectToRoute('app_validate', ['id' => $action->getId()]);

                //if ($action) {
            /*    foreach ($activities as $activity) {
                    $activity->setValidatedBy($this->getUser());
                    $activity->setValidated(true);
                }
                $action->setValidatedBy($this->getUser());
                $action->setValidated(PlanUtils::checkActionValidated($action));

                $this->getDoctrine()->getManagerForClass(Activity::class)->flush();
                
                return $this->redirectToRoute('app_validate', ['id' => $action->getId()]);
            */
            //}
    }

    /**
     * @Route("/app/update/soumettre/pta", name="app_soumettre_pta_action")
     * 
    */
    public function soumettrePTAActivityAction(Request $request, SettingsManager $settingsManager)
    {
        $sessionYear = $request->getSession()->get('_year');

        if($this->isGranted('ROLE_FOCAL') && $request->getSession()->get('_ministry')){
            $structureyear = $settingsManager->getStructureYear($sessionYear, null);
            if($structureyear){
                $structureyear->setPta(true);
    
            }else{
                $structureyear = new StructureYear();
                $ministry = $this->getDoctrine()->getRepository(Ministry::class)->findOneBy(['id' => $request->getSession()->get('_ministry')->getId()]);
                $structureyear->setMinistry( $ministry);
                $structureyear->setYear($settingsManager->getYear($sessionYear));
                $structureyear->setPta(true);
            }
            $this->getDoctrine()->getManager()->persist($structureyear);
            $this->getDoctrine()->getManager()->flush();
        }elseif($this->isGranted('ROLE_FOCAL') &&  $request->getSession()->get('_commune')){
            $structureyear = $settingsManager->getStructureYear($sessionYear, null, $request->getSession()->get('_commune'));
            if($structureyear){
                $structureyear->setPta(true);
    
            }else{
                $structureyear = new StructureYear();
                $commune = $this->getDoctrine()->getRepository(Commune::class)->findOneBy(['id' => $request->getSession()->get('_commune')->getId()]);
                $structureyear->setCommune($commune);
                $structureyear->setYear($settingsManager->getYear($sessionYear));
                $structureyear->setPta(true);
            }
            $this->getDoctrine()->getManager()->persist($structureyear);
            $this->getDoctrine()->getManager()->flush();
        }
        $this->addFlash('success', "PTA soumis avec succès pour validation");
        return $this->redirectToRoute('app_validate');
    }
    

    /**
     * @Route("/app/update/activities/{id}", name="app_update_activity")
     * 
     */
    public function updateActivityAction($id, Request $request)
    {
        $activity = $this->getDoctrine()
            ->getRepository(Activity::class)
            ->findOneBy(['id' => $id]);

        if($activity->getAction()->getProgramm()->getCommune()){
            $form = $this->createForm(ActivityType::class, $activity, [
                'sessionYear' => $request->getSession()->get('_year'),
                'sessionMinistry' => $request->getSession()->get('_ministry'),
                'sessionCommune' => $request->getSession()->get('_commune'),
                'defaultType' => 'commune',
                'defaultEntite' => $activity->getAction()->getProgramm()->getCommune(),
                'defaultProgramm' => $activity->getAction()->getProgramm(),
            ]);
        }
        if($activity->getAction()->getProgramm()->getMinistry()){
            $form = $this->createForm(ActivityType::class, $activity, [
                'sessionYear' => $request->getSession()->get('_year'),
                'sessionMinistry' => $request->getSession()->get('_ministry'),
                'sessionCommune' => $request->getSession()->get('_commune'),
                'defaultType' => 'ministere',
                'defaultEntite' => $activity->getAction()->getProgramm()->getMinistry(),
                'defaultProgramm' => $activity->getAction()->getProgramm(),
            ]);
        }

        $form->handleRequest($request);
        if ($form->isSubmitted() && $form->isValid()) {
            $activity = $form->getData();
            //$activity->setUpdatedBy($this->getUser());

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

            return $this->redirectToRoute('app_validate', ['id' => $activity->getAction()->getId()]);
        }

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

    /**
     * @Route("/app/delete/activity/{id}", name="app_delete_activity")
     * 
     */
	 /* @IsGranted({"ROLE_ADMIN", "ROLE_DPP"}) */
    public function deleteActivityAction($id, Request $request)
    {
        $activity = $this->getDoctrine()
            ->getRepository(Activity::class)
            ->findOneBy(['id' => $id]);

        $this->getDoctrine()->getManagerForClass(Activity::class)->remove($activity);
        $this->getDoctrine()->getManagerForClass(Activity::class)->flush();

        return $this->redirectToRoute('app_validate', ['id' => $activity->getAction()->getId()]);
    }

    /**
     * @Route("/app/delete/action/{id}", name="app_delete_action")
     * 
     */
	 /* @IsGranted({"ROLE_ADMIN", "ROLE_DPP"}) */
    public function deleteActionAction($id, Request $request)
    {
        $action = $this->getDoctrine()
            ->getRepository(Action::class)
            ->findOneBy(['id' => $id]);

        $this->getDoctrine()->getManagerForClass(Action::class)->remove($action);
        $this->getDoctrine()->getManagerForClass(Action::class)->flush();

        return $this->redirectToRoute('app_validate', ['id' => $action->getID()]);
    }

    /**
     * @Route("/app/output/indice/export", name="app_output_indice_export")
    */
    public function exportIndice(Request $request, SettingsManager $settingsManager) 
    {
        $datas = json_decode($request->request->get('datas'));
        $title=$datas->title;
        $year= $request->getSession()->get('_year');
        $maquette= $this->getParameter('kernel.project_dir') . '/public/upload/export/indice.xlsx';
        //Chargement de la maquette de base
        $spreadsheet = IOFactory::load($maquette);

        $sheet = $spreadsheet->getSheet(0);
        $sheet->setTitle($title);

        $line=4;

        foreach ($datas->rows as $data) {
            $sheet->setCellValue('A' . $line, $data->code.' ');
            $sheet->setCellValue('B' . $line, $data->name);
            $sheet->setCellValue('C' . $line, $data->refIndice);
            $sheet->setCellValue('D' . $line, $data->recIndice);
            $sheet->setCellValue('E' . $line, $data->statut);
            $sheet->setCellValue('F' . $line, $data->trend);
            $line++;
        }

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

    public function exportXLSFile($datas, $title, $columns, $position, $filename) 
    {
        $colors = [
            'blue'      => '007bff',
            'indigo'    => '6610f2',
            'purple'    => '6f42c1',
            'pink'      => 'e83e8c',
            'red'       => 'dc3545',
            'orange'    => 'fd7e14',
            'yellow'    => 'ffc107',
            'green'     => '28a745',
            'teal'      => '20c997',
            'cyan'      => '17a2b8',
            'white'     => 'ffffff',
            'gray'      => '6c757d',
            'gray-dark' => '343a40',
        ];

        $spreadsheet = new Spreadsheet();
        
        $spreadsheet->getDefaultStyle()->getFont()->setName('Arial');
        $spreadsheet->getDefaultStyle()->getFont()->setSize(10);

        $worksheet = $spreadsheet->getActiveSheet();
        $worksheet->setTitle("MesODD");
        //$worksheet->getDefaultStyle()->getFont()->setName('Times New Roman');
        
        //$datas = json_decode($request->request->get('datas'));
        $currentRow = 1;
        $highestRow = count($datas->rows);

        # Ligne 1
        $worksheet->setCellValueByColumnAndRow(2, $currentRow, 'Année');
        $worksheet->setCellValueByColumnAndRow(3, $currentRow, (string)$datas->header->year);
        $worksheet->setCellValueByColumnAndRow(5, $currentRow, 'Act. total');
        $worksheet->setCellValueByColumnAndRow(6, $currentRow, 'Sen. globale');
        $worksheet->setCellValueByColumnAndRow(7, $currentRow, 'App. globale');
        $worksheet->setCellValueByColumnAndRow(8, $currentRow, 'Mon. global');
        $worksheet->getStyle('C'.$currentRow.':D'.$currentRow)
            ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT);
        $worksheet->getStyle('E'.$currentRow.':H'.$currentRow)
            ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
        $worksheet->getStyle('C'.$currentRow)
            ->getFont()->setBold(true);
        $worksheet->getStyle('E'.$currentRow.':H'.$currentRow)
            ->getFont()->setBold(true);
        $currentRow += 1;
        
        if ($datas->header->level1->name) {
            $code = $datas->header->level1->code;
            $name = $datas->header->level1->name;
            if ($position == 'vertical') {
                $name = $name.' ('.$code.')';
            } elseif ($position == 'horizontal') {
                $name = $code.' - '.$name;
            }
            $filename = $filename.'-'.$code;

            $worksheet->setCellValueByColumnAndRow(2, $currentRow, $this->labels[$position]['label1']);
            $worksheet->setCellValueByColumnAndRow(3, $currentRow, $name);
            $worksheet->getStyle('A'.$currentRow)
                ->getFont()->setBold(true);
        }
        $worksheet->setCellValueByColumnAndRow(5, $currentRow, $datas->header->nbActivity);
        $worksheet->setCellValueByColumnAndRow(6, $currentRow, $datas->header->sensitivity);
        $worksheet->setCellValueByColumnAndRow(7, $currentRow, $datas->header->appreciation);
        $worksheet->setCellValueByColumnAndRow(8, $currentRow, $datas->header->amount);
        $worksheet->getStyle('E'.$currentRow.':H'.$currentRow)->getFill()
            ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
            ->getStartColor()->setARGB('FF'.$colors[$datas->header->flag]);
        $worksheet->getStyle('H'.$currentRow)->getNumberFormat()
            ->setFormatCode('#,##0');
        $worksheet->getStyle('E'.$currentRow.':F'.$currentRow)
            ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
        $currentRow += 1;

        if ($datas->header->level2->name) {
            $code = $datas->header->level2->code;
            $name = $datas->header->level2->name;
            if ($position == 'vertical') {
                $name = $name.' ('.$code.')';
            } elseif ($position == 'horizontal') {
                $name = $code.' - '.$name;
            }
            $filename = $filename.'-'.$code;

            $worksheet->setCellValueByColumnAndRow(2, $currentRow, $this->labels[$position]['label2']);
            $worksheet->setCellValueByColumnAndRow(3, $currentRow, $name);
            $worksheet->getStyle('A'.$currentRow)
                ->getFont()->setBold(true);
            $currentRow += 1;
        }
        
        if ($datas->header->level3->name) {
            $code = $datas->header->level3->code;
            $name = $datas->header->level3->name;
            if ($position == 'vertical') {
                $name = $name.' ('.$code.')';
            } else {
                $name = $code.' - '.$name;
            }
            $filename = $filename.'-'.$code;

            $worksheet->setCellValueByColumnAndRow(1, $currentRow, $this->labels[$position]['label3']);
            $worksheet->setCellValueByColumnAndRow(2, $currentRow, $name);
            $worksheet->getStyle('C'.$currentRow)
                ->getFont()->setBold(true);
            $currentRow += 1;
        }
        $currentRow += 1;

        $worksheet->setCellValueByColumnAndRow(1, $currentRow, $title[0]);
        $worksheet->setCellValueByColumnAndRow(2, $currentRow, $title[1]);
        $worksheet->setCellValueByColumnAndRow(3, $currentRow, $title[2]);
        $worksheet->setCellValueByColumnAndRow(4, $currentRow, $title[3]);
        $worksheet->setCellValueByColumnAndRow(5, $currentRow, $title[4]);
        $worksheet->setCellValueByColumnAndRow(6, $currentRow, $title[5]);
        $worksheet->setCellValueByColumnAndRow(7, $currentRow, $title[6]);
        $worksheet->setCellValueByColumnAndRow(8, $currentRow, $title[7]);
        $worksheet->getColumnDimension('A')->setWidth(16);
        $worksheet->getColumnDimension('B')->setWidth(16);
        $worksheet->getColumnDimension('C')->setWidth(64);
        $worksheet->getColumnDimension('D')->setWidth(12);
        $worksheet->getColumnDimension('E')->setWidth(15);
        $worksheet->getColumnDimension('F')->setWidth(15);
        $worksheet->getColumnDimension('G')->setWidth(15);
        $worksheet->getColumnDimension('H')->setWidth(20);
        $worksheet->getStyle('A'.$currentRow.':H'.$currentRow)
            ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
        $worksheet->getStyle('A'.$currentRow.':H'.$currentRow)
            ->getFont()->setBold(true);
        $currentRow += 1;

        
        for ($row = $currentRow; $row < $highestRow+$currentRow; ++$row) {
            $data = $datas->rows[$row-$currentRow];
            $col = 0;

            if ($columns[$col]=='') {
                $col += 1;
                $worksheet->setCellValueByColumnAndRow($col, $row, '');
            }
            if ($columns[$col]=='programm') {
                $col += 1;
                $worksheet->setCellValueByColumnAndRow($col, $row, $data->programm);
            }
            if ($columns[$col]=='') {
                $col += 1;
                $worksheet->setCellValueByColumnAndRow($col, $row, '');
            }
            if ($columns[$col]=='action') {
                $col += 1;
                $worksheet->setCellValueByColumnAndRow($col, $row, $data->action);
            }
            if ($columns[$col]=='name') {
                $col += 1;
                $worksheet->setCellValueByColumnAndRow($col, $row, $data->name);
            }
            if ($columns[$col]=='code') {
                $col += 1;
                $worksheet->setCellValueByColumnAndRow($col, $row, $data->code);
            }
            if ($columns[$col]=='nature') {
                $col += 1;
                $worksheet->setCellValueByColumnAndRow($col, $row, $data->nature);
            }
            if ($columns[$col]=='rate') {
                $col += 1;
                $worksheet->setCellValueByColumnAndRow($col, $row, $data->rate);
            }
            if ($columns[$col]=='nbActivity') {
                $col += 1;
                $worksheet->setCellValueByColumnAndRow($col, $row, $data->nbActivity);
            }
            if ($columns[$col]=='target') {
                $col += 1;
                $worksheet->setCellValueByColumnAndRow($col, $row, $data->target);
            }
            if ($columns[$col]=='sensitivity') {
                $col += 1;
                $worksheet->setCellValueByColumnAndRow($col, $row, $data->sensitivity);
            }
            if ($columns[$col]=='appreciation') {
                $col += 1;
                $worksheet->setCellValueByColumnAndRow($col, $row, $data->appreciation);
            }
            if ($columns[$col]=='amount') {
                $col += 1;
                $worksheet->setCellValueByColumnAndRow($col, $row, $data->amount);
            }

            $startColor = $colors[$data->flag];
            $worksheet->getStyle('F'.$row.':G'.$row)->getFill()
                ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                ->getStartColor()->setARGB('FF'.$startColor);
            $worksheet->getStyle('H'.$row)->getNumberFormat()
                ->setFormatCode('#,##0');

            $worksheet->getStyle('D'.$row.':E'.$row)
                ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
        }

        $totalRow = $highestRow+$currentRow;
        $worksheet->getStyle('A1:H'.$totalRow)
            ->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);

        $writer = new Xlsx($spreadsheet);

        $temp_file = tempnam(sys_get_temp_dir(), $filename.'.xlsx');

        $writer->save($temp_file);

        return $this->file($temp_file, $filename.'.xlsx', ResponseHeaderBag::DISPOSITION_INLINE);
    }

    /**
     * @Route("/app/output/goal/export", name="app_output_goal_export")
     */
    public function outputGoalExportAction(Request $request)
    {
        $title = ['', '', 'ODD', 'Code', 'Nbre Activités', 'Sensibilité', 'Appréciation', 'Montant'];
        $columns = ['', '', 'name', 'code', 'nbActivity', 'sensitivity', 'appreciation', 'amount'];
        $datas = json_decode($request->request->get('datas'));
        return $this->exportXLSFile($datas, $title, $columns, 'vertical', 'mesodd_odd_ministere');
    }

    /**
     * @Route("/app/output/target/export", name="app_output_target_export")
     */
    public function exportTargetAction(Request $request)
    {
        $title = ['', '', 'Cible', 'ODD', 'Nbre Activités', 'Sensibilité', 'Appréciation', 'Montant'];
        $columns = ['', '', 'name', 'code', 'nbActivity', 'sensitivity', 'appreciation', 'amount'];
        $datas = json_decode($request->request->get('datas'));
        return $this->exportXLSFile($datas, $title, $columns, 'vertical', 'mesodd_cible_ministere');
    }

    /**
     * @Route("/app/output/ministry/export", name="app_output_ministry_export")
     */
    public function exportMinistryAction(Request $request)
    {
        $title = ['', '', 'Ministère', 'Code', 'Nbre Activités', 'Sensibilité', 'Appréciation', 'Montant'];
        $columns = ['', '', 'name', 'code', 'nbActivity', 'sensitivity', 'appreciation', 'amount'];
        $datas = json_decode($request->request->get('datas'));
        return $this->exportXLSFile($datas, $title, $columns, 'horizontal', 'mesodd_ministere_odd');
    }

    /**
     * @Route("/app/output/programm/export", name="app_output_programm_export")
     */
    public function exportProgrammAction(Request $request)
    {
        $title = ['', '', 'Programme', 'Ministère', 'Nbre Activités', 'Sensibilité', 'Appréciation', 'Montant'];
        $columns = ['', '', 'name', 'code', 'nbActivity', 'sensitivity', 'appreciation', 'amount'];
        $datas = json_decode($request->request->get('datas'));
        return $this->exportXLSFile($datas, $title, $columns, 'horizontal', 'mesodd_programme_odd');
    }

    /**
     * @Route("/app/output/direction/export", name="app_output_direction_export")
     */
    public function exportDirectionAction(Request $request)
    {
        $title = ['', '', 'Direction', 'Ministère', 'Nbre Activités', 'Sensibilité', 'Appréciation', 'Montant'];
        $columns = ['', '', 'name', 'code', 'nbActivity', 'sensitivity', 'appreciation', 'amount'];
        $datas = json_decode($request->request->get('datas'));
        return $this->exportXLSFile($datas, $title, $columns, 'horizontal', 'mesodd_direction_odd');
    }

    /**
     * @Route("/app/output/nature/export", name="app_output_nature_export")
     */
    public function exportNatureAction(Request $request)
    {
        $title = ['', '', 'Nature', 'Taux', 'Nbre Activités', 'Sensibilité', 'Appréciation', 'Montant'];
        $columns = ['', '', 'name', 'rate', 'nbActivity', 'sensitivity', 'appreciation', 'amount'];
        $datas = json_decode($request->request->get('datas'));
        return $this->exportXLSFile($datas, $title, $columns, 'vertical', 'mesodd_nature_cout');
    }

    /**
     * @Route("/app/output/activity/list/export", name="app_output_activity_list_export")
     */
    public function exportActivityListAction(Request $request)
    {
        $title = ['Programme', 'Action', 'Activité', 'Nature', 'Cible', 'Sensibilité', 'Appréciation', 'Montant'];
        $columns = ['programm', 'action', 'name', 'nature', 'target', 'sensitivity', 'appreciation', 'amount'];
        $datas = json_decode($request->request->get('datas'));
        return $this->exportXLSFile($datas, $title, $columns, 'vertical', 'mesodd_activity_list');
    }

    /**
     * @Route("/app/output/activity/less/export", name="app_output_activity_less_export")
     */
    public function exportActivityLessAction(Request $request)
    {
        $title = ['Programme', 'Action', 'Activité', 'Nature', 'Cible', 'Sensibilité', 'Appréciation', 'Montant'];
        $columns = ['programm', 'action', 'name', 'nature', 'target', 'sensitivity', 'appreciation', 'amount'];
        $datas = json_decode($request->request->get('datas'));
        return $this->exportXLSFile($datas, $title, $columns, 'vertical', 'mesodd_activity_less');
    }

    /**
     * @Route("/app/output/commune/export", name="app_output_commune_export")
     */
    public function exportCommuneAction(Request $request)
    {
        $title = ['Nature', 'Activité', 'Cible', 'Sensibilité', 'Appréciation', 'Montant'];
        $columns = ['nature', 'name', 'target', 'sensitivity', 'appreciation', 'amount'];
        $datas = json_decode($request->request->get('datas'));
        return $this->exportXLSFile($datas, $title, $columns, 'oblique', 'mesodd_activity_commune');
    }
}