<?php

namespace App\Http\Controllers;

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Storage;
use Illuminate\Support\Facades\Session;
use Illuminate\Http\Request;

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

use Exception;

class ExcelController extends Controller
{
    public function generateExcel(){

        try {
            $etudiants =
                DB::connection('tenant')
                ->table('tabetudiant')
                ->join('tabinscription','tabetudiant.matricule','=','tabinscription.matricule')
                ->where('tabinscription.annee_acad', session('annee_acad') )
                ->join('filieres','tabinscription.idFiliere','=','filieres.idFiliere')
                // ->whereIn('filieres.idDepartement',$idDepartements)
                ->whereIn('filieres.idFiliere', explode(',', session('filieres')))
                ->select('idDossier', 'cheminPhoto', 'tabetudiant.matricule', 'tabetudiant.pays', 'nom', 'prenom', 'sigleFilieres', 'adresse', 'date_nais', 'lieu_nais', 'tabinscription.idFiliere', 'sexe', 'nationalite', 'niveauFormation', 'annee', 'statut', 'resultat', 'nombreUErestant', 'pourcentageCredit',  'moyennePonderee', 'dateValidation')
                ->orderBy('nom', 'asc')
                ->get();

            // $etudiants = json_decode(json_encode($etudiants), true);

            $status =
                DB::connection('tenant')
                ->table('statuts')
                ->where('idEtablissement', '=', session('id_etablissement'))
                ->get()
                ->groupBy('idStatut');

            //Création de la feuille de calcul
            $spreadsheet = new Spreadsheet();

            //Spécification des propriétés du document
            $spreadsheet->getProperties()
                ->setTitle('Liste des Etudiants')
                ->setSubject( !(session('description')) ? " " : session('description') )
                ->setDescription('Liste des étudiants présents dans l\'établissement')
                ->setCreator( session('sigle') )
                ->setLastModifiedBy( session('sigle') );

            //On active la feuille
            $sheet = $spreadsheet->getActiveSheet();

            //Définition des styles qu'on aura à utiliser sur la feuille
            //Style de l'entête de la feuille
            $styleArray1 = [
                // FONT
                'font' => [
                    'bold' => true,
                    'italic' => true,
                    'name' => "SimSun",
                    'size' => 11
                ],

                // ALIGNMENT
                'alignment' => [
                    'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                    'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER
                ],

                // BORDER
                'borders' => [
                    'top' => [
                        'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK
                    ],
                    'bottom' => [
                        'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK
                    ],
                    'left' => [
                        'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_MEDIUM
                    ],
                    'right' => [
                        'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN
                    ]
                ],
                'fill' => [
                    'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
                    'color' => ['argb' => 'FFF080FF']
                ]

            ];

            //Style de l'entête du tableau
            $styleArray2 = [
                // FONT
                'font' => [
                    'bold' => true,
                    'italic' => true,
                    'shrinkToFit' => true,
                    'name' => "Century Gothic",
                    'size' => 9
                ],
                // ALIGNMENT
                'alignment' => [
                    'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                    'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER
                ],
                // BORDER
                'borders' => [
                    'top' => [
                        'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN
                    ],
                    'bottom' => [
                        'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN
                    ],
                    'left' => [
                        'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN
                    ],
                    'right' => [
                        'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN
                    ]
                ]
            ];

            //Style du contenu du tableau
            $styleArray3 = [
                // BORDER
                'borders' => [
                    'top' => [
                        'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN
                    ],
                    'bottom' => [
                        'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN
                    ],
                    'left' => [
                        'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN
                    ],
                    'right' => [
                        'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN
                    ]
                ]
            ];

            //Style des étudiants n'ayant pas valider l'inscription
            $styleArray4 = [
                // FONT
                'font' => [
                    // 'italic' => true,
                    // 'name' => "SimSun"
                ],

                'fill' => [
                    'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
                    'color' => ['argb' => 'f90606']
                ]

            ];

            //L'entête de la feuille
            $sheet->mergeCells('A1:L1');
            $sheet->getRowDimension(1)->setRowHeight(20);
            $sheet->getStyle('A1:L1')->applyFromArray($styleArray1);
            $sheet->setCellValue('A1', 'École : ' . session('sigle') . ' -   Année-Académique : ' .session('periode'));
            $sheet->getStyle('A2:L2')->getAlignment()->setWrapText(true);

            //Création du tableau et et son entête
            $this->excelFile($sheet, $styleArray2);

            //Le contenu du tableau
            $numero = 1;
            $iter = 3;
            foreach ($etudiants as $etudiant) {
                $etat =$etudiant->statut;
                //ajout des données
                $sheet->setCellValue('A' . $iter, $numero);
                $sheet->setCellValue('B' . $iter, $etudiant->matricule);
                $sheet->setCellValue('C' . $iter, $etudiant->nom);
                $sheet->setCellValue('D' . $iter, $etudiant->prenom);
                $sheet->setCellValue('E' . $iter, $etudiant->date_nais);
                $sheet->setCellValue('F' . $iter, $etudiant->lieu_nais);
                $sheet->setCellValue('G' . $iter, $status[$etat][0]->statut);
                $sheet->setCellValue('H' . $iter, $etudiant->sigleFilieres);
                $sheet->setCellValue('I' . $iter, $etudiant->sexe);
                $sheet->setCellValue('J' . $iter, $etudiant->nationalite);
                $sheet->setCellValue('K' . $iter, findNiveau($etudiant->niveauFormation).' '.$etudiant->annee);
                $sheet->setCellValue('L' . $iter, dateFormat($etudiant->dateValidation));

                //application du style
                $sheet->getStyle('A' . $iter)->applyFromArray($styleArray3);
                $sheet->getStyle('B' . $iter)->applyFromArray($styleArray3);
                $sheet->getStyle('C' . $iter)->applyFromArray($styleArray3);
                $sheet->getStyle('D' . $iter)->applyFromArray($styleArray3);
                $sheet->getStyle('E' . $iter)->applyFromArray($styleArray3);
                $sheet->getStyle('F' . $iter)->applyFromArray($styleArray3);
                $sheet->getStyle('G' . $iter)->applyFromArray($styleArray3);
                $sheet->getStyle('H' . $iter)->applyFromArray($styleArray3);
                $sheet->getStyle('I' . $iter)->applyFromArray($styleArray3);
                $sheet->getStyle('J' . $iter)->applyFromArray($styleArray3);
                $sheet->getStyle('K' . $iter)->applyFromArray($styleArray3);
                $sheet->getStyle('L' . $iter)->applyFromArray($styleArray3);

                //Si l'étudiant n'a pas encore validé son inscription
                if ($etudiant->dateValidation == "0001-01-01 00:00:00") {
                    $sheet->getStyle('L' . $iter . ':L' . $iter)->applyFromArray($styleArray4);
                }

                $numero++;
                $iter++;
            }

            // Définir l'index de la feuille active sur la première feuille, afin qu'Excel l'ouvre en tant que première feuille
            $spreadsheet->setActiveSheetIndex(0);

            //Le nom du fichier à générer
            $filename = 'ListeInscription.xlsx';

            // 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="ListeInscription.xlsx"');
            header('Cache-Control: max-age=0');

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


        } catch (Exception $e) {
            return redirect()->back()->with([
                'success' => false,
                'msg' => 'Nous rencontrons un probleme. Veuillez reessayez !'
            ]);
        }
    }

    public function excelFile($sheet, $styleArray) {
        //Longueur de chaque colonne du tableau
        $sheet->getColumnDimension('A')->setWidth(4);
        $sheet->getColumnDimension('B')->setWidth(17);
        $sheet->getColumnDimension('C')->setWidth(20);
        $sheet->getColumnDimension('D')->setWidth(31);
        $sheet->getColumnDimension('E')->setWidth(18);
        $sheet->getColumnDimension('F')->setWidth(15);
        $sheet->getColumnDimension('G')->setWidth(6);
        $sheet->getColumnDimension('H')->setWidth(15);
        $sheet->getColumnDimension('I')->setWidth(5);
        $sheet->getColumnDimension('J')->setWidth(10);
        $sheet->getColumnDimension('K')->setWidth(13);
        $sheet->getColumnDimension('L')->setWidth(25);

        $sheet->getRowDimension('1')->setRowHeight(30);
        $sheet->getRowDimension('2')->setRowHeight(35);

        //Ecriture des titres d'entête
        $sheet->getStyle('A2')->applyFromArray($styleArray);
        $styleA1 = $sheet->getStyle('A2');
        $styleFont = $styleA1->getFont();
        $sheet->setCellValue('A' . 2, 'N°');

        $sheet->getStyle('B2')->applyFromArray($styleArray);
        $styleA1 = $sheet->getStyle('B2');
        $styleFont = $styleA1->getFont();
        $sheet->setCellValue('B' . 2, 'Matricule');

        $sheet->getStyle('C2')->applyFromArray($styleArray);
        $styleA1 = $sheet->getStyle('C2');
        $styleFont = $styleA1->getFont();
        $sheet->setCellValue('C' . 2, 'Nom');

        $sheet->getStyle('D2')->applyFromArray($styleArray);
        $styleA1 = $sheet->getStyle('D2');
        $styleFont = $styleA1->getFont();
        $sheet->setCellValue('D' . 2, 'Prénoms');
        // $sheet->getStyle('D2')->getAlignment()->setTextRotation(90);

        $sheet->getStyle('E2')->applyFromArray($styleArray);
        $styleA1 = $sheet->getStyle('E2');
        $styleFont = $styleA1->getFont();
        $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();
        $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();
        $sheet->setCellValue('G' . 2, 'Statut');

        $sheet->getStyle('H2')->applyFromArray($styleArray);
        $styleA1 = $sheet->getStyle('H2');
        $styleFont = $styleA1->getFont();
        $sheet->setCellValue('H' . 2, 'Filière');

        $sheet->getStyle('I2')->applyFromArray($styleArray);
        $styleA1 = $sheet->getStyle('I2');
        $styleFont = $styleA1->getFont();
        $sheet->setCellValue('I' . 2, 'Sexe');

        $sheet->getStyle('J2')->applyFromArray($styleArray);
        $styleA1 = $sheet->getStyle('J2');
        $styleFont = $styleA1->getFont();
        $sheet->setCellValue('J' . 2, 'Nationalité');

        $sheet->getStyle('K2')->applyFromArray($styleArray);
        $styleA1 = $sheet->getStyle('K2');
        $styleFont = $styleA1->getFont();
        $sheet->setCellValue('K' . 2, 'Niveau d\'étude');

        $sheet->getStyle('L2')->applyFromArray($styleArray);
        $styleA1 = $sheet->getStyle('L2');
        $styleFont = $styleA1->getFont();
        $sheet->setCellValue('L' . 2, 'Date de validation');

        //Définition de l'alignement du text
        $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);
        $sheet->getStyle('F')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('G')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('H')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
        $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);
    }
}
