<?php

namespace App\Controller;

use App\Entity\Goal;
use App\Entity\ActivityOSC;
use App\Entity\Mainstay;
use App\Entity\Accelerator;
use App\Form\ActivityOSCType;
use App\Form\MainstaySelType;
use App\Form\MainstayOnlySelType;
use App\Form\SuiviOSCModelType;
use App\Form\Model\SuiviOSCModel;
use App\Form\UploadMainstayType;
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\StreamedResponse;

class SuiviOSCController extends AbstractController
{
    private $accelerators = [];


    /**
     * @Route("/app/suiviOSC/activities", name="app_suiviOSC_activities")
     */
    public function activitiesSuiviOSCAction(Request $request)
    {
        $activities = [];

        $form = $this->createForm(MainstayOnlySelType::class, null);

        $form->handleRequest($request);
        if ($form->isSubmitted() && $form->isValid()) {
            $mainstay = $form->get('mainstay')->getData();
            $activities = $this->getDoctrine()
                ->getRepository(ActivityOSC::class)
                ->createQueryBuilder('y')
                ->select('y')
                ->andWhere('y.year = :year')
                ->andWhere('y.mainstay = :mainstay')
                ->setParameter('year', $request->getSession()->get('_year'))
                ->setParameter('mainstay',$mainstay)
                ->getQuery()->getResult();
        }

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

    /**
     * @Route("/app/suiviOSC/activities/new", name="app_suiviOSC_activity_new")
     
     */
    //* @IsGranted({"ROLE_ADMIN", "ROLE_MANAGER", "ROLE_DPP", "ROLE_FOCAL"})
    public function newSuiviOSCActivityAction(Request $request)
    {
        $activity = new ActivityOSC();

        $form = $this->createForm(ActivityOSCType::class, $activity);

        $form->handleRequest($request);
        if ($form->isSubmitted() && $form->isValid()) {
            $activity = $form->getData();
            $activity->setYear($request->getSession()->get('_year'));

            $this->getDoctrine()->getManagerForClass(ActivityOSC::class)->persist($activity);
            $this->getDoctrine()->getManagerForClass(ActivityOSC::class)->flush();
            
            return $this->redirectToRoute('app_suiviOSC_activities');
        }

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

    /**
     * @Route("/app/suiviOSC/activities/{id}", name="app_suiviOSC_activity")
     
     */
    //* @IsGranted({"ROLE_ADMIN", "ROLE_MANAGER", "ROLE_DPP", "ROLE_FOCAL"})
    public function activitySuiviOSCAction($id, Request $request)
    {
        $activity = $this->getDoctrine()
            ->getRepository(ActivityOSC::class)
            ->findOneBy(['id' => $id]);

        $form = $this->createForm(ActivityOSCType::class, $activity);

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

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

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

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


    /**
     * @Route("/app/suiviOSC/{id}", name="app_suiviOSC", defaults={"id"=0})
     */
    //* @IsGranted({"ROLE_ADMIN", "ROLE_MANAGER"})
    public function suiviOSCAction($id=0, Request $request)
    {
        $collection = $request->request->get('mainstay_only_sel');
        $postId = isset($collection['mainstay']) ? $collection['mainstay'] : null;
        if ($postId and ($postId != $id)) {
            return $this->redirectToRoute('app_suiviOSC', ['id' => $postId]);
        }

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

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

        $form = $this->createForm(MainstayOnlySelType::class, null);

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

        if ($mainstay) {
            $activities = $this->getDoctrine()
                ->getRepository(ActivityOSC::class)
                ->findByMainstay($year, $mainstay);
        }

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

    /**
     * @Route("/app/suiviOSC/edit/{id}", name="app_suiviOSC_edit")
     */
    //* @IsGranted({"ROLE_ADMIN", "ROLE_MANAGER"})
    public function editSuiviOSCAction($id, Request $request)
    {
        $activity = $this->getDoctrine()
            ->getRepository(ActivityOSC::class)
            ->findOneBy(['id' => $id]);

        $model = New SuiviOSCModel();
        $model->activity = $activity;
        $model->expectedCost = $activity->getExpectedCost();
        $model->realizedCost = $activity->getRealizedCost();
        $model->executed = $activity->getExecuted();
        $model->achievement = $activity->getAchievement();
        $model->constraints = $activity->getConstraints();
        $model->factors = $activity->getFactors();
        $model->observations = $activity->getObservations();
        $model->goal = $activity->getGoal();

        $form = $this->createForm(SuiviOSCModelType::class, $model, [
            'accelerator' => $activity->getAccelerator(),
        ]);

        $form->handleRequest($request);
        if ($form->isSubmitted() && $form->isValid()) {
            $model = $form->getData();
            
            if ($model->realizedCost) {
                $activity->setExecuted($model->executed);
                $activity->setRealizedCost($model->realizedCost);
                $activity->setAchievement($model->achievement);
                $activity->setConstraints($model->constraints);
                $activity->setFactors($model->factors);
                $activity->setObservations($model->observations);
                $activity->setGoal($model->goal);

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

            return $this->redirectToRoute('app_suiviOSC', ['id' => $activity->getMainstay()->getID()]);
        }

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

    /**
     * @Route("/app/suiviosc/format", name="app_suivi_osc_format")
     
     */
    //* @IsGranted({"ROLE_FOCAL"})
    public function formatSuiviOSCAction(Request $request)
    {
        $annee = $request->getSession()->get('_year');
        $fileName = 'Activite-OSC-' . $annee . '.xlsx';
        $maquette = $this->getParameter('kernel.project_dir') . '/public/upload/ministeres/osc.xlsx';

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

        $sheet = $spreadsheet->getSheet(1);
        $actions = $this->getDoctrine()->getRepository(Accelerator::class)->findAll();
        $line = 2;
        foreach ($actions as $action) {
            $sheet->setCellValue('A' . $line, $action->getCode());
            $sheet->setCellValue('B' . $line, $action->getName());
            $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/suiviosc/upload", name="app_suivi_osc_upload")
     
     */
    //* @IsGranted({"ROLE_ADMIN", "ROLE_FOCAL"})
    public function uploadSuiviOSCAction(Request $request)
    {
        $sessionYear = $request->getSession()->get('_year');

        $form = $this->createForm(UploadMainstayType::class, null);

        $form->handleRequest($request);
        if ($form->isSubmitted() && $form->isValid()) {
            $mainstay = $form->get('mainstay')->getData();
            
            $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 = 2; $row <= $highestRow; ++$row) {
                $acceleratorKey = $worksheet->getCellByColumnAndRow(3, $row)->getValue();
                if ( substr($acceleratorKey, 0, 1) == 'A' ) {
                    if (!key_exists($acceleratorKey, $this->accelerators)) {
                        $accelerator = $this->getDoctrine()
                            ->getRepository(Accelerator::class)
                            ->findOneBy(['id' => (int)substr($acceleratorKey, 1)]); //, 'mainstay' => $mainstay->getId()
                        
                        if ($accelerator) {
                            $this->accelerators[$acceleratorKey] = $accelerator;
                        } else {
                            $this->addFlash("error", "La accelerator ". $acceleratorKey . " n'esxiste pas");
                            $fileOK = false;
                            break;
                        }
                    }
                } else {
                    $this->addFlash('error', "La clé ". $acceleratorKey . " n'est plas une clé de accelerator");
                    $fileOK = false;
                    break;
                }
            }

            $nbActivity = 0;
            if ($fileOK) {
                $currentProgramm = null;
                for ($row = 1; $row <= $highestRow; ++$row) {
                    $activityName = $worksheet->getCellByColumnAndRow(1, $row)->getValue();
                    $activityExpectedCost = $worksheet->getCellByColumnAndRow(2, $row)->getValue();
                    $acceleratorKey = $worksheet->getCellByColumnAndRow(3, $row)->getValue();

                    $activity = new ActivityOSC();
                    $activity->setName(trim($activityName));
                    $activity->setMainstay($mainstay);
                    $activity->setYear($sessionYear);
                    $activity->setAccelerator($this->accelerators[$acceleratorKey]);
                    $activity->setExpectedCost((int)str_replace(' ', '', $activityExpectedCost));

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

                $this->addFlash('notice', "Chargement effectué avec succès - Nombre d'activités chargées ".$nbActivity);
            }
            //return $this->redirectToRoute('app_upload');

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


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

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

        $rows = $this->getDoctrine()
            ->getRepository(Mainstay::class)
            ->getFinancialOSC($year);

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

            if ( $mainstay ) {
                $rows = $this->getDoctrine()
                    ->getRepository(Goal::class)
                    ->getFinancialOSC($year, $mainstay);
            }
        }

        $nbActivity = 0;
        $sumExpectedCost = 0;
        $sumRealizedCost = 0;

        foreach ($rows as $key => $row) {
            $nbActivity += $rows[$key]['nbActivity'];
            $sumExpectedCost += $rows[$key]['sumExpectedCost'];
            $sumRealizedCost += $rows[$key]['sumRealizedCost'];
        }

        /*
        $header['year'] = $year;
        $header['level1']['name'] = $mainstay ? $mainstay->getName() : '';
        $header['level1']['code'] = $mainstay ? $mainstay->getCode() : '';
        $header['level2']['name'] = $goal ? $goal->getName() : '';
        $header['level2']['code'] = $goal ? $goal->getCode() : '';
        $header['level3']['name'] = '';
        $header['level3']['code'] = '';
        */
        $header['totalRate'] = $nbActivity ? $sumRealizedCost / $sumExpectedCost * 100 : 0;
        
        $header['nbActivity'] = $nbActivity;
        $header['sumExpectedCost'] = $sumExpectedCost;
        $header['sumRealizedCost'] = $sumRealizedCost;

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

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

    /**
     * @Route("/app/suiviOSC/graph/finance", name="app_suiviOSC_graph_finance")
     */
    public function graphSuiviOSCFinanceAction(Request $request)
    {
        $barChart = [];
        $year = $request->getSession()->get('_year');

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

        $label = 'Pilier';
        $datas = $this->getDoctrine()
            ->getRepository(Mainstay::class)
            ->getFinancialOSC($year);

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

            if ( $goal ) {
                $label = '';
                $datas = $this->getDoctrine()
                    ->getRepository(Ministry::class)
                    ->getFinancialOSC($year, $mainstay, $goal);
            } elseif ( $mainstay ) {
                $label = 'ODD';
                $datas = $this->getDoctrine()
                    ->getRepository(Goal::class)
                    ->getFinancialOSC($year, $mainstay);
            }
        }

        foreach ($datas as $key => $data) {
            $barChart['labels'][] = $label.' '.$datas[$key]['code'];
            $barChart['bgColor'][] = $datas[$key]['color'];
            $barChart['values'][] = round($datas[$key]['sumRealizedCost'] / $datas[$key]['sumExpectedCost'] * 100, 2);
        } 

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

}