<?php

namespace App\Http\Controllers;

use App\Http\Helpers\AdditionalHelpers;
use App\Models\NiveauFormation;
use App\Models\AnneeEtude;
use App\Models\Filieres;
use App\Models\UnitesE;
use App\Models\TabEtudiant;
use App\Models\EtablissementInfo;
use App\Models\TabInscription;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Session;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;


class GenFichExcelRattrapage
{
    static function fichierExcel($sheet)
    {
        $sheet->getColumnDimension('A')->setWidth(14);
        $sheet->getColumnDimension('B')->setWidth(31);
        $sheet->getColumnDimension('C')->setWidth(31);
        $sheet->getColumnDimension('D')->setWidth(4);
        $sheet->getColumnDimension('E')->setWidth(12);
        $sheet->getColumnDimension('F')->setWidth(18);
        $sheet->getColumnDimension('G')->setWidth(21);
        $sheet->getColumnDimension('H')->setWidth(30);
        $sheet->getColumnDimension('I')->setWidth(12);
        $sheet->getColumnDimension('J')->setWidth(10);
        $sheet->getColumnDimension('K')->setWidth(21);
        $sheet->getColumnDimension('L')->setWidth(7);

        $sheet->getRowDimension('1')->setRowHeight(32);
        $sheet->getRowDimension('2')->setRowHeight(100);

        $sheet->getStyle('A')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('B')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT);
        $sheet->getStyle('C')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT);
        $sheet->getStyle('D')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('E')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('F')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT);
        $sheet->getStyle('G')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT);
        $sheet->getStyle('H')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT);
        $sheet->getStyle('I')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('J')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('K')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('L')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);

        $styleArray = array(
            'alignment' => array(
                'wrap' => true,
                'shrinkToFit' => true,
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER
            ),
            'borders' => array(
                'top' => array('style' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN),
                'bottom' => array('style' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN),
                'left' => array('style' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN),
                'right'  => array('style' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN)
            )
        );
        $sheet->getStyle('A2')->applyFromArray($styleArray);
        $styleA1 = $sheet->getStyle('A2');
        $styleFont = $styleA1->getFont();
        $styleFont->setBold(true);
        $styleFont->setSize(8);
        $styleFont->setName('Arial');
        $styleFont->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_BLACK);
        $sheet->setCellValue('A' . 2, 'Matricule');

        $sheet->getStyle('B2')->applyFromArray($styleArray);
        $styleA1 = $sheet->getStyle('B2');
        $styleFont = $styleA1->getFont();
        $styleFont->setBold(true);
        $styleFont->setSize(8);
        $styleFont->setName('Arial');
        $styleFont->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_BLACK);
        $sheet->setCellValue('B' . 2, 'Nom');

        $sheet->getStyle('C2')->applyFromArray($styleArray);
        $styleA1 = $sheet->getStyle('C2');
        $styleFont = $styleA1->getFont();
        $styleFont->setBold(true);
        $styleFont->setSize(8);
        $styleFont->setName('Arial');
        $styleFont->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_BLACK);
        $sheet->setCellValue('C' . 2, 'Prénom'); //utf8_encode('Prénom')

        $sheet->getStyle('D2')->applyFromArray($styleArray);
        $styleA1 = $sheet->getStyle('D2');
        $styleFont = $styleA1->getFont();
        $styleFont->setBold(true);
        $styleFont->setSize(8);
        $styleFont->setName('Arial');
        $styleFont->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_BLACK);
        $sheet->setCellValue('D' . 2, 'Sexe');
        $sheet->getStyle('D2')->getAlignment()->setTextRotation(90);

        $sheet->getStyle('E2')->applyFromArray($styleArray);
        $styleA1 = $sheet->getStyle('E2');
        $styleFont = $styleA1->getFont();
        $styleFont->setBold(true);
        $styleFont->setSize(8);
        $styleFont->setName('Arial');
        $styleFont->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_BLACK);
        $sheet->setCellValue('E' . 2, 'Date de Naissance');
        $sheet->getStyle('E2')->getAlignment()->setTextRotation(90);

        $sheet->getStyle('F2')->applyFromArray($styleArray);
        $styleA1 = $sheet->getStyle('F2');
        $styleFont = $styleA1->getFont();
        $styleFont->setBold(true);
        $styleFont->setSize(8);
        $styleFont->setName('Arial');
        $styleFont->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_BLACK);
        $sheet->setCellValue('F' . 2, 'Lieu de Naissance');
        $sheet->getStyle('F2')->getAlignment()->setTextRotation(90);

        $sheet->getStyle('G2')->applyFromArray($styleArray);
        $styleA1 = $sheet->getStyle('G2');
        $styleFont = $styleA1->getFont();
        $styleFont->setBold(true);
        $styleFont->setSize(8);
        $styleFont->setName('Arial');
        $styleFont->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_BLACK);
        $sheet->setCellValue('G' . 2, 'Adresse');

        $sheet->getStyle('H2')->applyFromArray($styleArray);
        $styleA1 = $sheet->getStyle('H2');
        $styleFont = $styleA1->getFont();
        $styleFont->setBold(true);
        $styleFont->setSize(8);
        $styleFont->setName('Arial');
        $styleFont->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_BLACK);
        $sheet->setCellValue('H' . 2, 'E-mail');

        $sheet->getStyle('I2')->applyFromArray($styleArray);
        $styleA1 = $sheet->getStyle('I2');
        $styleFont = $styleA1->getFont();
        $styleFont->setBold(true);
        $styleFont->setSize(8);
        $styleFont->setName('Arial');
        $styleFont->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_BLACK);
        $sheet->setCellValue('I' . 2, 'Nationalité');

        $sheet->getStyle('J2')->applyFromArray($styleArray);
        $styleA1 = $sheet->getStyle('J2');
        $styleFont = $styleA1->getFont();
        $styleFont->setBold(true);
        $styleFont->setSize(8);
        $styleFont->setName('Arial');
        $styleFont->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_BLACK);
        $sheet->setCellValue('J' . 2, 'Statut');

        $sheet->getStyle('K2')->applyFromArray($styleArray);
        $styleA1 = $sheet->getStyle('K2');
        $styleFont = $styleA1->getFont();
        $styleFont->setBold(true);
        $styleFont->setSize(8);
        $styleFont->setName('Arial');
        $styleFont->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_BLACK);
        $sheet->setCellValue('K' . 2, 'Date validation');
        //$sheet->getStyle('K2')->getAlignment()->setTextRotation(90);

        $sheet->getStyle('L2')->applyFromArray($styleArray);
        $styleA1 = $sheet->getStyle('L2');
        $styleFont = $styleA1->getFont();
        $styleFont->setBold(true);
        $styleFont->setSize(8);
        $styleFont->setName('Arial');
        $styleFont->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_BLACK);
        $sheet->setCellValue('L' . 2, 'Nombre d\'UE restants à valider');
        $sheet->getStyle('L2')->getAlignment()->setTextRotation(90);
    }
};

class StatistiqueController extends Controller
{
    public function statistiques()
    {
        //Statistiques
        $data = [
            'formations' => NiveauFormation::all(),
            'etudes' => AnneeEtude::all(),
            'filieres' => Filieres::all()
        ];
        return view('stat_modif.statistique', compact('data'));
    }





    public function excelEffectifValidation(Request $request)
    {
        if ($request->statType == '2') {
            $annee  = $request->etudeYear;
        } else {
            $semestre  = $request->semestre;
            $annee = ($semestre % 2 == 0) ? $semestre / 2 : ($semestre + 1) / 2;
        }
        $countFiliere = count($request->idFiliere);

        $spreadsheet = new Spreadsheet();
        //Spécification des propriétés du document
        $spreadsheet->getProperties()
            ->setTitle(EtablissementInfo::first()->sigleeta)
            ->setSubject('Effectif validation UEs')
            ->setDescription('Statistique-de-reussite/Effectif validation UEs')
            ->setCreator('IITECH SARL')
            ->setLastModifiedBy('IITECH SARL');

        $spreadsheet->getDefaultStyle()->getFont()->setName('Arial')->setSize(11);
        //Période
        $date = Session::get('RESULTAT_DATA')['RESULTAT_YEAR']->periode;

        $filename = "Effectif-validation-ues_" . $date . '.xlsx';
        //On active la feuille
        $sheet = $spreadsheet->getActiveSheet();

        $sheet->getColumnDimension('A')->setWidth(30);
        $sheet->getColumnDimension('B')->setWidth(20);
        $sheet->getColumnDimension('C')->setWidth(20);
        $sheet->getColumnDimension('D')->setWidth(20);
        $sheet->getColumnDimension('E')->setWidth(15);

        $sheet->getStyle('A')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('B')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('C')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('D')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('E')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);

        //==================
        $styleArray0 = array(
            'alignment' => array(
                'wrap' => true,
                'shrinkToFit' => true,
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER
            ),
            'borders' => array(
                'top' => array('style' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN),
                'bottom' => array('style' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN),
                'left' => array('style' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN),
                'right'  => array('style' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN)
            )
        );
        //La bordure à appliquer à toutes les cellules
        $border = array(
            'allborders' => array(
                'style' => 'thin',
                'color' => array(
                    'rgb' => '808080',
                ),
            ),
        );

        $styleArray = array(
            'borders' => array(
                'top' => array('style' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN),
                'bottom' => array('style' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN),
                'left' => array('style' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN),
                'right'  => array('style' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN)
            )
        );

        $iter = 2;
        for ($i = 0; $i < $countFiliere; $i++) {
            $filiere = Filieres::where('idFiliere', $request->idFiliere[$i])->first();
            $titre = 'Statistiques : ' . $filiere->intituleFilieres . ' ' . $annee . ' & Année Universitaire : ' . $date;
            $sheet->getRowDimension($iter)->setRowHeight(30);
            $sheet->mergeCells('A' . $iter . ':D' . $iter);
            $sheet->setCellValue('E' . $iter, $filiere->sigleFilieres);
            $sheet->getStyle('E' . $iter)->applyFromArray($styleArray0);

            $sheet->setCellValue('A' . $iter, $titre);
            $sheet->getStyle('A' . $iter)->getFont()->setBold(true);
            $sheet->getStyle('A' . $iter)->getFont()->setItalic(true);
            $sheet->getStyle('A' . $iter)->getFont()->setColor(new \PhpOffice\PhpSpreadsheet\Style\Color(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_BLUE));
            $sheet->getStyle('A' . $iter)->applyFromArray($styleArray0);
            $sheet->getStyle('A' . $iter . ':D' . $iter)->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID);
            $sheet->getStyle('A' . $iter . ':D' . $iter)->getFill()->getStartColor()->setARGB('FFF080FF');

            if ($request->statType == 2) { //Annuel
                $nbrUnites = UnitesE::where(['idFiliere' => $request->idFiliere, 'annee' => $annee, 'niveauFormation' => $request->formation, 'annee_acad' => $request->acadYear])->count();
                $nbrUeRestant = TabInscription::selectRaw('nombreUErestant, COUNT(nombreUErestant) as nb')
                    ->where(['idFiliere' => $request->idFiliere, 'annee' => $annee, 'niveauFormation' => $request->formation, 'annee_acad' => $request->acadYear])
                    ->groupBy('nombreUErestant')
                    ->orderBy('nombreUErestant')->get();
            } else { //Semestriel
                $nbrUnites = UnitesE::where(['idFiliere' => $request->idFiliere, 'annee' => $annee, 'niveauFormation' => $request->formation, 'annee_acad' => $request->acadYear, 'semestre' => $semestre])->count();
                $champ = ($semestre % 2 == 0) ? 'nombreUErestantS2' : 'nombreUErestantS1';
                $nbrUeRestant = TabInscription::selectRaw("$champ, COUNT($champ) as nb")
                    ->where(['idFiliere' => $request->idFiliere, 'annee' => $annee, 'niveauFormation' => $request->formation, 'annee_acad' => $request->acadYear])
                    ->groupBy("$champ")
                    ->orderBy($champ)->get();
            }

            $nbrMatricule = TabInscription::where(['idFiliere' => $request->idFiliere, 'annee' => $annee, 'niveauFormation' => $request->formation, 'annee_acad' => $request->acadYear])->count();
            $cumul = 0;
            $iter++;
            $sheet->getStyle('A' . $iter)->applyFromArray($border);
            $sheet->getStyle('B' . $iter)->applyFromArray($border);
            $sheet->getStyle('C' . $iter)->applyFromArray($border);
            $sheet->getStyle('D' . $iter)->applyFromArray($border);
            $sheet->setCellValue('A' . $iter, 'Nombre ayant validé UE');
            $sheet->setCellValue('B' . $iter, 'Effectif');
            $sheet->setCellValue('C' . $iter, 'Pourcentage (%)');
            $sheet->setCellValue('D' . $iter, '%Cumulé');
            $sheet->setCellValue('E' . $iter, $filiere->sigleFilieres);


            foreach ($nbrUeRestant as $restant) {
                $iter++;
                $cumul = $cumul + ($restant->nb / $nbrMatricule) * 100;
                $perc = ($restant->nb / $nbrMatricule) * 100;

                $nbUEV = 0;
                if ($request->statType == 2) {
                    $nbUEV = $nbrUnites - $restant->nombreUErestant;
                } else {
                    $nbUEV = $nbrUnites - $restant->$champ;
                }
                $sheet->getStyle('A' . $iter)->applyFromArray($border);
                $sheet->getStyle('B' . $iter)->applyFromArray($border);
                $sheet->getStyle('C' . $iter)->applyFromArray($border);
                $sheet->getStyle('D' . $iter)->applyFromArray($border);
                $sheet->setCellValue('A' . $iter, 'Nombre ayant validé ' . $nbUEV . ' UE');
                $sheet->setCellValue('B' . $iter, $restant->nb);
                $sheet->setCellValue('C' . $iter, $perc);
                $sheet->setCellValue('D' . $iter, $cumul);
                $sheet->setCellValue('E' . $iter, $filiere->sigleFilieres);
            }
            $iter++;
            $sheet->getStyle('A' . $iter)->applyFromArray($border);
            $sheet->getStyle('B' . $iter)->applyFromArray($border);
            $sheet->getStyle('C' . $iter)->applyFromArray($border);
            $sheet->getStyle('D' . $iter)->applyFromArray($border);
            $sheet->setCellValue('A' . $iter, 'TOTAL');
            $sheet->setCellValue('B' . $iter, $nbrMatricule);
            $sheet->setCellValue('C' . $iter, '100');
            $sheet->setCellValue('D' . $iter, '');
            $sheet->setCellValue('E' . $iter, $filiere->sigleFilieres);
            $iter++;
        }

        // Rediriger la sortie vers le navigateur Web d'un client (Xlsx)
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="' . $filename . '"');
        header('Cache-Control: max-age=0');

        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');

        return redirect('/statistiques');
    }
}
