<?php

$fecha = date('d_m_Y');
require_once '../class/c_login.php';
if (!isset($_SESSION['user'])){
    die(header('Location: index.php'));
}
header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
header("Content-Disposition: attachment;filename=solicitudes_$fecha.xlsx");
header("Cache-Control: max-age=0");

require_once "../vendor/autoload.php";

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\IOFactory;


$fecha_ini = $_POST["fecha_inicial"];
$fecha_fin = $_POST["fecha_final"];

//Reposiciones
$repEdo_rs = $db->query('SELECT * FROM fs_estado_reposicion' );

$repoParams = array();

$user = Login::get_user();
$user->access();
$query="";
if($user->rol["rol_id"] == 9){//es coordinador
    $query .= ":facultad, ";
    $repoParams[":facultad"] = $user->facultad["facultad_id"];
}else{//supervisor
    $query .= "NULL, ";
}
if(isset($_POST["prof"]) ){
    $query .= ":prof,";
    $profesor = trim($_POST["prof"]);//limpia texto
    $repoParams[":prof"] = $profesor;
}else{
    $query .= "NULL,";
}
$query .= ":f_ini, :f_fin, ";
$repoParams[":f_ini"] = DateTime::createFromFormat('d/m/Y', $fecha_ini)->format('Y-m-d');
$repoParams[":f_fin"] = DateTime::createFromFormat('d/m/Y', $fecha_fin)->format('Y-m-d');

$spreadsheet = new Spreadsheet();

// Set document properties
$spreadsheet->getProperties()->setCreator('Universidad La Salle')
    ->setLastModifiedBy('Universidad La Salle')
    ->setTitle('Solicitudes')
    ->setDescription('Reporte de solicitudes.');

$headerStyle = 
    [
        'fill' => [
            'fillType' => Fill::FILL_SOLID,
            'color' => ['argb' => 'FF001d68'],
        ],
        'borders' => [
            'bottom' => ['borderStyle' => Border::BORDER_THIN],
            'right' => ['borderStyle' => Border::BORDER_MEDIUM],
        ],
        'font' => [
            'bold'  =>  true,
            'color' => ['argb' => 'FFFFFFFF'],
        ]
    ];

$row_base = 6;

$i=0;
foreach($repEdo_rs as $redo){
    $row = $row_base;
    if($i >= $spreadsheet->getSheetCount()){
        $spreadsheet->createSheet();
    }
    $spreadsheet->setActiveSheetIndex($i);
    //crea imagen 
    $drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
    $drawing->setName('La Salle');
    $drawing->setDescription('La Salle');
    $drawing->setPath('../imagenes/logo.png'); // put your path and image here
    $drawing->setCoordinates('A1');
    $drawing->setHeight(100);
    $drawing->setOffsetX(10);
    //agrega imagen
    $drawing->setWorksheet($spreadsheet->getActiveSheet());

    $spreadsheet->getActiveSheet()
        ->setCellValue('A'.$row, 'Estado')
        ->setCellValue('B'.$row, 'Tipo')
        ->setCellValue('C'.$row, 'Profesor')
        ->setCellValue('D'.$row, 'Materia')
        ->setCellValue('E'.$row, 'Grupo')
        ->setCellValue('F'.$row, 'Fecha falta')
        ->setCellValue('G'.$row, 'Fecha reposición')
        ->setCellValue('H'.$row, 'Salón');

    $spreadsheet->getActiveSheet()->getStyle('A'.$row.':H'.$row)->applyFromArray($headerStyle);
    $repoParams[":edo"]=$redo["estado_reposicion_id"];
    if($user->rol["rol_id"] == 7){//es supervisor
        $repoParams[":sup"] = $user->user["id"];
        $solicitudes_rs = $db->query('SELECT * FROM fs_solicitud(NULL, '.$query.':edo, NULL, :sup) ', $repoParams );
    }else{
        $solicitudes_rs = $db->query('SELECT * FROM fs_solicitud(NULL, '.$query.':edo, NULL, NULL) ', $repoParams );
    }
    
    $row++;
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->setTitle($redo["estado_nombre"]);
    if(isset($solicitudes_rs) && count($solicitudes_rs)>0){
        foreach($solicitudes_rs as $reposicion){

            
            $sheet->setCellValue('A'.$row, $reposicion["estado_nombre"]);
            $sheet->setCellValue('B'.$row, $reposicion["solicitudtipo_nombre"]);
            $sheet->setCellValue('C'.$row, $reposicion["profesor_clave"]." - ".$reposicion["profesor_nombre"]);
            $sheet->setCellValue('D'.$row, $reposicion["materia_nombre"]);
            if($reposicion["horario_grupo"]!="")
                $sheet->setCellValue('E'.$row, $reposicion["horario_grupo"]);
            
            if($reposicion["fecha_clase"]!=""){
                $fechaI = DateTime::createFromFormat('Y-m-d', $reposicion["fecha_nueva"])->format('d/m/Y');
                $sheet->setCellValue('F'.$row, $fechaI." ".substr($reposicion["horario_hora"],0, 5));
            }
                    
            $fechaF = date("d/m/Y", strtotime($reposicion["fecha_nueva"]));
            $sheet->setCellValue('G'.$row, $fechaF." ".substr($reposicion["hora_nueva"],0, 5)." a ".substr($reposicion["hora_nueva_fin"],0, 5));
                    
            if($reposicion["salon_id"] != ""){
                $salon_json = json_decode($reposicion["salon_array"], true);
                $sheet->setCellValue('H'.$row, $salon_json[count($salon_json)-1]);
            }else
                $sheet->setCellValue('H'.$row, "Pendiente");
            $row++;
                    
        }//foreach
        
    }//if
    foreach ($sheet->getColumnIterator() as $column) {
        $sheet->getColumnDimension($column->getColumnIndex())->setAutoSize(true);
    }
    $sheet->setAutoFilter('A'.$row_base.':H'.$row_base);
    $i++;
}
$spreadsheet->setActiveSheetIndex(0);


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