sgi/gema/export/xls_cambiocarrera.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;
\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_o"], $_POST["plan_d"]) && isset($_POST["fini"], $_POST["ffin"])) {
    $filter_plan_o = filter_input(INPUT_POST, "plan_o", FILTER_SANITIZE_NUMBER_INT);//limpia texto
    $filter_plan_d = filter_input(INPUT_POST, "plan_d", FILTER_SANITIZE_NUMBER_INT);//limpia texto
    $filter_fini = fechaGuion(trim(filter_input(INPUT_POST, "fini", FILTER_SANITIZE_STRING,array('flags' => FILTER_FLAG_STRIP_LOW))));//limpia texto
    $filter_ffin = fechaGuion(trim(filter_input(INPUT_POST, "ffin", FILTER_SANITIZE_STRING,array('flags' => FILTER_FLAG_STRIP_LOW))));//limpia texto
}else{
    echo "No se recibieron los datos.";
    exit();
}
$query = "";
if($filter_plan_o != 0){
    $query .= ":plan_o,";
}else
    $query .= "NULL,";

if($filter_plan_d != 0){
    $query .= ":plan_d,";
}else
    $query .= "NULL,";

$stmt = $pdo->prepare('Select * from fs_alumnocarrera_cambio('.$query.' :fini, :ffin)');
$stmt->bindParam(":fini", $filter_fini);
$stmt->bindParam(":ffin", $filter_ffin);
if($filter_plan_o != 0){ $stmt->bindParam(":plan_o", $filter_plan_o); }
if($filter_plan_d != 0){ $stmt->bindParam(":plan_d", $filter_plan_d); }

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


if($filter_plan_o != 0){
    $stmt = $pdo->prepare('Select * from fs_alumnoprocedencia(:plan_d, :fini, :ffin)');
    $stmt->bindParam(":plan_d", $filter_plan_d);
}else{
    $stmt = $pdo->prepare('Select * from fs_alumnoprocedencia(NULL, :fini, :ffin)');
}
$stmt->bindParam(":fini", $filter_fini);
$stmt->bindParam(":ffin", $filter_ffin);
if(!$stmt->execute()){
    echo "Ocurrió un error al cargar las instituciones de procedencia.";
    //print_r($stmt->errorInfo());
    exit();
}else{
    $procedencia_rs = $stmt->fetchAll();
}
$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('Cambio de carrera')
    ->setDescription('Cambio de carrera 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->setActiveSheetIndex($sheet);
$spreadsheet->getActiveSheet()->setTitle('Programas');

$row = 1;
$col = 1;
$spreadsheet->getActiveSheet()
                ->setCellValueByColumnAndRow($col++, 1, "Alumno")
                ->setCellValueByColumnAndRow($col++, 1, "Programa origen")
                ->setCellValueByColumnAndRow($col++, 1, "Clave origen")
                ->setCellValueByColumnAndRow($col++, 1, "Programa destino")
                ->setCellValueByColumnAndRow($col++, 1, "Clave destino")
                ->setCellValueByColumnAndRow($col++, 1, "Fecha de cambio");
$colMax = $col;
$colLetter = numberToLetter($colMax-1);

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

$row = 2;
foreach($cambio_rs as $cambio){
    $col = 1;
    $spreadsheet->setActiveSheetIndex($sheet)
        ->setCellValueByColumnAndRow($col++, $row, $cambio["Usuario_apellidos"]." ".$cambio["Usuario_nombre"])
        ->setCellValueByColumnAndRow($col++, $row, $cambio["Carrera_desc_origen"]." ".$cambio["PlanEstudio_desc_origen"])
        ->setCellValueByColumnAndRow($col++, $row, $cambio["Usuario_claveULSA_origen"])
        ->setCellValueByColumnAndRow($col++, $row, $cambio["Carrera_desc_destino"]." ".$cambio["PlanEstudio_desc_destino"])
        ->setCellValueByColumnAndRow($col++, $row, $cambio["Usuario_claveULSA_destino"])
        ->setCellValueByColumnAndRow($col, $row, $cambio["CambioCarrera_fecha"]);

    $colLetter = numberToLetter($col);
    $spreadsheet->getActiveSheet()->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());

//--------
$sheet = 1;
// Rename worksheet
$worksheet1 = $spreadsheet->createSheet();
$worksheet1->setTitle('Procedencia');

$spreadsheet->setActiveSheetIndex($sheet);

$row = 1;
$col = 1;
$spreadsheet->getActiveSheet()
                ->setCellValueByColumnAndRow($col++, 1, "Alumno")
                ->setCellValueByColumnAndRow($col++, 1, "Institución origen")
                ->setCellValueByColumnAndRow($col++, 1, "Programa destino")
                ->setCellValueByColumnAndRow($col++, 1, "Clave destino")
                ->setCellValueByColumnAndRow($col++, 1, "Fecha de ingreso");
$colMax = $col;
$colLetter = numberToLetter($colMax-1);

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

$row = 2;
foreach($procedencia_rs as $cambio){
    $col = 1;
    $spreadsheet->setActiveSheetIndex($sheet)
        ->setCellValueByColumnAndRow($col++, $row, $cambio["Usuario_apellidos"]." ".$cambio["Usuario_nombre"])
        ->setCellValueByColumnAndRow($col++, $row, $cambio["Alumno_procedencia"])
        ->setCellValueByColumnAndRow($col++, $row, $cambio["Carrera_desc"]." ".$cambio["PlanEstudio_desc"])
        ->setCellValueByColumnAndRow($col++, $row, $cambio["Usuario_claveULSA"])
        ->setCellValueByColumnAndRow($col, $row, $cambio["Alumno_fecha_ingreso"]);

    $colLetter = numberToLetter($col);
    $spreadsheet->getActiveSheet()->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="cambiosCarrera.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;