<?php

setlocale(LC_TIME, 'es_MX.UTF-8');
require_once("../../include/nocache.php");
require_once("../../include/constantes.php");
require_once("../../include/bd_pdo.php");
require_once("../../include/util.php");
require_once("../../classes/ValidaSesion.php");
require "../../include/phpSpreadsheet/autoload.php";

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Style;
\PhpOffice\PhpSpreadsheet\Cell\Cell::setValueBinder( new \PhpOffice\PhpSpreadsheet\Cell\AdvancedValueBinder() );

function numberToLetter ($number){
    $temp = ($number-1) % 26;
    return chr($temp + 65);
};


//--- Objeto para validar usuario. El id de usuario lo lee desde sesión
$objSesion = new ValidaSesion($pdo, 125, GEMA);
if(!$objSesion->tieneAcceso()){
    echo "No tienes permiso de ver esta página. Revisa que tu sesión siga activa.";
    exit();
}
unset($objValida);

if(isset($_POST["plan"], $_POST["intentos"], $_POST["prefijo"]) ) {
    $filter_plan = filter_input(INPUT_POST, "plan", FILTER_SANITIZE_NUMBER_INT);//limpia texto
    $filter_generacion = fechaGuion(trim(filter_input(INPUT_POST, "generacion", FILTER_SANITIZE_STRING,array('flags' => FILTER_FLAG_STRIP_LOW))));//limpia texto
    $filter_intentos = filter_input(INPUT_POST, "intentos", FILTER_SANITIZE_NUMBER_INT);//limpia texto
    $nom = trim(filter_input(INPUT_POST, "prefijo", FILTER_SANITIZE_STRING,array('flags' => FILTER_FLAG_STRIP_LOW)));//limpia texto
}else{
    echo "No se recibieron los datos.";
    exit();
}
$query = "";
if($filter_plan != 0){
    $query .= ":plan,";
}else
    $query .= "NULL,";
if($filter_generacion != ""){
    $query .= ":generacion,";
}else
    $query .= "NULL,";

$stmt = $pdo->prepare('Select * from fs_materiasreprobadas('.$query.' :intentos)');//sólo no revalidadas
$stmt->bindParam(":intentos", $filter_intentos);
if($filter_plan != 0){ $stmt->bindParam(":plan", $filter_plan); }
if($filter_generacion != ""){ $stmt->bindParam(":generacion", $filter_generacion); }

if(!$stmt->execute()){
    $errorDesc = "Ocurrió un error al cargar el total de materias reprobadas.";
    print_r($stmt->errorInfo());
}else{
    $intentos_rs = $stmt->fetchAll();
/*
    $intentosTotalArr = array();
    $last = -1;
    $matriasArr = array();
    $i = 0;
    $total = 0;
    foreach($intentos_rs as $intento){
        if($last != $intento["Usuario_claveULSA"]){
            if($last != -1){
                $intentosTotalArr[$i]["materiaArr"]=$materiasArr;
                $i++;
            }
            $last = $intento["Usuario_claveULSA"];
            $materiasArr = array();
        }
        $intentosTotalArr[$i] = array("id" => $intento["Usuario_id"], "clave" => $intento["Usuario_claveULSA"], "nombre" =>$intento["Usuario_apellidos"]." ".$intento["Usuario_nombre"], "carrera"=>$intento["Carrera_desc"]." ".$intento["PlanEstudio_desc"],
            "fecha"=>$intento["Alumno_fecha_ingreso"], "generacion"=>$intento["Alumno_generacion"], "total"=>$intento["Intentos_total"], "materiaArr"=>array());
        $materiasArr[] = array("id" => $intento["Materia_id"], "desc" =>$intento["Materia_desc"]);
    }
    if($last != -1){
        $intentosTotalArr[$i]["materiaArr"]=$materiasArr;
    }
    unset($i);

    $filter_orden = 0;
    if(isset($_POST["orden"]) && $_POST["orden"] != 0){
        $filter_orden = filter_input(INPUT_POST, "orden", FILTER_SANITIZE_NUMBER_INT);//limpia texto
        usort($intentosTotalArr, function ($item1, $item2) {
            return $item2['total'] <=> $item1['total'];
        });
    }*/
}
$stmt->closeCursor();

//--------
//
// Create new Spreadsheet object
$spreadsheet = new Spreadsheet();

// Set document properties
$spreadsheet->getProperties()->setCreator('Ingeniería La Salle')
    ->setLastModifiedBy('Ingeniería La Salle')
    ->setTitle('Oportunidades de aprobación')
    ->setDescription('Oportunidades para aprobar una materia.');


$headerStyle = new Style();
$headerStyle->applyFromArray(
    [
        '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'],
        ]
    ]
);
//--------
$sheet = 0;
// Rename worksheet
$spreadsheet->getActiveSheet()->setTitle('Oportunidades');

$row = 1;
$col = 1;
$spreadsheet->setActiveSheetIndex($sheet)
                ->setCellValueByColumnAndRow($col++, 1, "Clave")
                ->setCellValueByColumnAndRow($col++, 1, "Nombre")
                ->setCellValueByColumnAndRow($col++, 1, "Carrera")
                ->setCellValueByColumnAndRow($col++, 1, "Generación")
                ->setCellValueByColumnAndRow($col++, 1, "Materia no acreditadas");

$colMax = $col;
$colLetter = numberToLetter($colMax-1);

$spreadsheet->getActiveSheet()->duplicateStyle($headerStyle, 'A1:'.$colLetter.'1');

$row = 2;
foreach($intentos_rs as $intento){
    $col = 1;
    $spreadsheet->setActiveSheetIndex($sheet)
        ->setCellValueByColumnAndRow($col++, $row, $intento["Usuario_claveULSA"])
        ->setCellValueByColumnAndRow($col++, $row, $intento["Usuario_apellidos"]." ".$intento["Usuario_nombre"])
        ->setCellValueByColumnAndRow($col++, $row, $intento["Carrera_desc"]." ".$intento["PlanEstudio_desc"])
        ->setCellValueByColumnAndRow($col++, $row, fechaMonthPicker($intento["Alumno_generacion"]))
        ->setCellValueByColumnAndRow($col++, $row, $intento["Materia_desc"]);

    /*$colLetter = numberToLetter(4);
    $spreadsheet->setActiveSheetIndex($sheet)->getStyle($colLetter.$row)->getNumberFormat()
        ->setFormatCode('dd/mm/yyyy');*/
    $row++;
}

//Auto ajustar anchos
for($col = 1; $col < $colMax; $col++)
    $spreadsheet->getActiveSheet()->getColumnDimension(numberToLetter($col))->setAutoSize(true);


//$spreadsheet->getActiveSheet()->getStyle('A1:H1')->getFont()->setBold(true);
$spreadsheet->getActiveSheet()->setAutoFilter($spreadsheet->getActiveSheet()->calculateWorksheetDimension());


//---------
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$spreadsheet->setActiveSheetIndex(0);


// Redirect output to a client’s web browser (Xlsx)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="reprobadosTotal'.$nom.'.xlsx"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header('Pragma: public'); // HTTP/1.0

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