sgi/gema/export/xls_alumnos_old.php

<?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;
use PhpOffice\PhpSpreadsheet\Shared\Date;


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, 127, 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["fecha_corte"])){
    echo "No hay fechas de corte seleccionadas.";
    exit();
}
$fechasCorteArr = $_POST["fecha_corte"];

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

// Set document properties
$spreadsheet->getProperties()->setCreator('Ingeniería La Salle')
    ->setLastModifiedBy('Ingeniería La Salle')
    ->setTitle('Estado de Alumnos')
    ->setDescription('Estado actual de alumnos.');


$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('Alumnos');


foreach ($fechasCorteArr as $fecha_hoy){

    $query = "";
    if(isset($_POST["fecha_inicial"]) && $_POST["fecha_inicial"] !=""){
        $filter_fini = fechaGuion(trim(filter_input(INPUT_POST, "fecha_inicial", FILTER_SANITIZE_STRING,array('flags' => FILTER_FLAG_STRIP_LOW))));//limpia texto
        $query .= ":fini, ";
    }else{
        $query .= "NULL, ";
    }
    if(isset($_POST["fecha_final"]) && $_POST["fecha_final"] !="") {
        $filter_ffin = fechaGuion(trim(filter_input(INPUT_POST, "fecha_final", FILTER_SANITIZE_STRING,array('flags' => FILTER_FLAG_STRIP_LOW))));//limpia texto
        $query .= ":ffin, ";
    }else{
        $query .= "NULL, ";
    }
    if(isset($_POST["fecha_hoy"]) && $_POST["fecha_hoy"] !="") {
        $filter_fhoy = fechaGuion(trim(filter_input(INPUT_POST, "fecha_hoy", FILTER_SANITIZE_STRING,array('flags' => FILTER_FLAG_STRIP_LOW))));//limpia texto
    }
    $query .= ":fhoy, ";

    if(isset($_POST["estado"]) && $_POST["estado"] !=""){
        $filter_estado = filter_input(INPUT_POST, "estado", FILTER_SANITIZE_NUMBER_INT);//limpia texto
        $query .= ":edo, ";
    }else{
        $query .= "NULL, ";
    }
    if(isset($_POST["carrera"]) && $_POST["carrera"] !="" ){
        $filter_carrera = filter_input(INPUT_POST, "carrera", FILTER_SANITIZE_NUMBER_INT);//limpia texto
        $query .= ":carrera, ";
    }else{
        $query .= "NULL, ";
    }
    $query .= "0, NULL";

    $stmt = $pdo->prepare('Select * from fs_alumnosestado('.$query.')');//sólo activos
    if(isset($filter_fini)) $stmt->bindParam(":fini", $filter_fini);
    if(isset($filter_ffin)) $stmt->bindParam(":ffin", $filter_ffin);
    if(isset($filter_estado)) $stmt->bindParam(":edo", $filter_estado);
    if(isset($filter_carrera)) $stmt->bindParam(":carrera", $filter_carrera);
    $stmt->bindParam(":fhoy", $filter_fhoy);


    if(!$stmt->execute()){
        echo "Ocurrió un error al cargar los alumnos.";
        //print_r($stmt->errorInfo());
        exit();
    }
    $alumno_rs = $stmt->fetchAll();
    $stmt->closeCursor();



    $row = 1;
    $col = 1;
    $spreadsheet->setActiveSheetIndex($sheet)
                    ->setCellValueByColumnAndRow($col++, 1, "Estado")
                    ->setCellValueByColumnAndRow($col++, 1, "Subestado")
                    ->setCellValueByColumnAndRow($col++, 1, "Fecha estado")
                    ->setCellValueByColumnAndRow($col++, 1, "Clave")
                    ->setCellValueByColumnAndRow($col++, 1, "Apellidos")
                    ->setCellValueByColumnAndRow($col++, 1, "Nombre")
                    ->setCellValueByColumnAndRow($col++, 1, "CURP")
                    ->setCellValueByColumnAndRow($col++, 1, "Correo")
                    ->setCellValueByColumnAndRow($col++, 1, "Carrera")
                    ->setCellValueByColumnAndRow($col++, 1, "Plan")
                    ->setCellValueByColumnAndRow($col++, 1, "Generación");
    $colMax = $col;

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

    $row = 2;
    foreach($alumno_rs as $alumno){
        $col = 1;
        $spreadsheet->setActiveSheetIndex($sheet)
            ->setCellValueByColumnAndRow($col++, $row, $alumno["EstadoAlumno_desc"])
            ->setCellValueByColumnAndRow($col++, $row, $alumno["SubEstadoAlumno_desc"])
            ->setCellValueByColumnAndRow($col++, $row, fechaSlash($alumno["SEA_fecha"]))
            ->setCellValueByColumnAndRow($col++, $row, $alumno["Usuario_claveULSA"])
            ->setCellValueByColumnAndRow($col++, $row, $alumno["Usuario_apellidos"])
            ->setCellValueByColumnAndRow($col++, $row, $alumno["Usuario_nombre"])
            ->setCellValueByColumnAndRow($col++, $row, $alumno["Usuario_curp"])
            ->setCellValueByColumnAndRow($col++, $row, $alumno["Contacto_correo"])
            ->setCellValueByColumnAndRow($col++, $row, $alumno["Carrera_desc"])
            ->setCellValueByColumnAndRow($col++, $row, $alumno["PlanEstudio_desc"])
            //->setCellValueByColumnAndRow($col++, $row, fechaSlash($alumno["Alumno_generacion"]));
            ->setCellValueByColumnAndRow($col++, $row, fechaMonthPicker($alumno["Alumno_generacion"]));

        /*$colLetter = numberToLetter($col-1);
        $spreadsheet->setActiveSheetIndex($sheet)->getStyle($colLetter.$row.':'.$colLetter.$row)->getNumberFormat()
            ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_DDMMYYYY);*/

        $colLetter = numberToLetter(3);
        $spreadsheet->setActiveSheetIndex($sheet)->getStyle($colLetter.$row.':'.$colLetter.$row)->getNumberFormat()
            ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_DDMMYYYY);
        $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="alumnos.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;