sgi/apsa/export/xls_examenesextraordinarios.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_once("../classes/ArchivoMaterias.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;
use PhpOffice\PhpSpreadsheet\Style\Alignment;


//--- Objeto para validar usuario. El id de usuario lo lee desde sesión
$objSesion = new ValidaSesion($pdo, 274, APSA);
if(!$objSesion->tieneAcceso()){
    header("Location: ".$pag."?error=3");
    exit();
}

unset($objValida);

//Obtiene fechas de extraordinarios del periodo
$stmt = $pdo->prepare('Select * from fs_fechaimportante(:periodo, 2)');
$stmt->bindParam(":periodo", $_SESSION["periodo_id"]);
if(!$stmt->execute()){
    $errorDesc = "Ocurrió un error al cargar las fechas de extraordinarios del periodo";
}else{
    $extras_rs = $stmt->fetch();
}
$stmt->closeCursor();

//Obtiene examenes
$query = "";
if(isset($_POST["sem"]) && is_numeric($_POST["sem"])){
    $query .= ":sem,";
    $filter_sem = filter_input(INPUT_POST, "sem", FILTER_SANITIZE_NUMBER_INT);//limpia texto
}else{
    $query .= "NULL,";
}
if(isset($_POST["plan"]) && is_numeric($_POST["plan"])){
    $query .= ":plan,";
    $filter_plan = filter_input(INPUT_POST, "plan", FILTER_SANITIZE_NUMBER_INT);//limpia texto
}else{
    $query .= "NULL,";
}
if(isset($_POST["area"]) && is_numeric($_POST["area"])){
    $query .= ":area,";
    $filter_area = filter_input(INPUT_POST, "area", FILTER_SANITIZE_NUMBER_INT);//limpia texto
}else{
    $query .= "NULL,";
}
if(isset($_POST["fecha_inicial"]) && $_POST["fecha_inicial"]!=""){
    $filter_fini = trim(filter_input(INPUT_POST, "fecha_inicial", FILTER_SANITIZE_STRING,array('flags' => FILTER_FLAG_STRIP_LOW)));//limpia texto
}
if(isset($_POST["fecha_final"]) && $_POST["fecha_final"]!=""){
    $filter_ffin = trim(filter_input(INPUT_POST, "fecha_final", FILTER_SANITIZE_STRING,array('flags' => FILTER_FLAG_STRIP_LOW)));//limpia texto
}
if(isset($filter_fini)) $fecha_ini = fechaGuion($filter_fini); else $fecha_ini = $extras_rs["FechaImportante_inicial"];
if(isset($filter_ffin)) $fecha_fin = fechaGuion($filter_ffin); else $fecha_fin = $extras_rs["FechaImportante_final"];

$stmt = $pdo->prepare('Select * from fs_examen(NULL, :periodo, '.$query.' :fini, :ffin, NULL, 0, NULL )');
$stmt->bindParam(":periodo", $_SESSION["periodo_id"]);
$stmt->bindParam(":fini", $fecha_ini);
$stmt->bindParam(":ffin", $fecha_fin);
//if(isset($filter_extra)) $stmt->bindParam(":extra", $filter_extra);
if(isset($filter_plan)) $stmt->bindParam(":plan", $filter_plan);
if(isset($filter_area)) $stmt->bindParam(":area", $filter_area);
if(isset($filter_sem)) $stmt->bindParam(":sem", $filter_sem);

if(!$stmt->execute()){
    print_r($stmt->errorInfo());
    $errorDesc = "Ocurrió un error al cargar los exámenes";
}else{
    $examenes_rs = $stmt->fetchAll();
}
$stmt->closeCursor();

$pendiente = "Pendiente";


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

// Set document properties
$spreadsheet->getProperties()->setCreator('Ingeniería La Salle')
    ->setLastModifiedBy('Ingeniería La Salle')
    ->setTitle('Exámenes extraordinarios')
    ->setDescription('Exámenes extraordinarios en el periodo.');

// Rename worksheet
$spreadsheet->getActiveSheet()->setTitle('Examen');

$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'],
            'name' => 'Indivisa Text Sans',
        ]
    ]
);


$bodyStyle = new Style();
$bodyStyle->applyFromArray(
    [
        'alignment' => [
            'vertical' => Alignment::VERTICAL_TOP,
        ],
        'font' => [
            'size' => 10,
            'name' => 'Indivisa Text Sans',
        ],
        'borders' => [
            'allBorders' => [
                'borderStyle' => Border::BORDER_THIN,
                'color' => ['argb' => 'FF001d68'],
            ],
            /*'inside' => ['borderStyle' => Border::BORDER_THIN],*/
        ],
    ]
);


$spreadsheet->getDefaultStyle()->getFont()->setName('Indivisa Text Sans');

$row_base = 6;

//crea imagen
$drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
$drawing->setName('La Salle');
$drawing->setDescription('La Salle');
$drawing->setPath('../../img/logopdf.png'); // put your path and image here
$drawing->setCoordinates('A1');
$drawing->setHeight(100);
$drawing->setOffsetX(10);
//agrega imagen
$drawing->setWorksheet($spreadsheet->getActiveSheet());

//crea imagen
$drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
$drawing->setName('Facultad de Ingenería');
$drawing->setDescription('Facultad de Ingenería');
$drawing->setPath('../../img/logoIngenieria.png'); // put your path and image here
$drawing->setCoordinates('F1');
$drawing->setHeight(100);
$drawing->setOffsetX(-20);
//agrega imagen
$drawing->setWorksheet($spreadsheet->getActiveSheet());

$row = $row_base;
$col = 1;

$spreadsheet->setActiveSheetIndex(0)
    ->setCellValueByColumnAndRow($col++, $row, 'Semestre')
    ->setCellValueByColumnAndRow($col++, $row, 'Materia')
    ->setCellValueByColumnAndRow($col++, $row, 'Fecha')
    ->setCellValueByColumnAndRow($col++, $row, 'Horario')
    ->setCellValueByColumnAndRow($col++, $row, 'Salón')
    ->setCellValueByColumnAndRow($col++, $row, 'Profesor');

$spreadsheet->getActiveSheet()->duplicateStyle($headerStyle, 'A'.$row.':F'.$row);

$row ++;
foreach($examenes_rs as $examen){
    
    $fechaIArr = explode(" ", $examen["Examen_fechahora_inicial"]);
    $fechaFArr = explode(" ", $examen["Examen_fechahora_final"]);

    $horas = date('H:i', strtotime($fechaIArr[1]))." a ".date('H:i', strtotime($fechaFArr[1]))." hrs.";
    
    $salon = "Pendiente";
    if($examen["Salon_desc"]!= ""){
        if($examen["Salon_desc_larga"] != "")
            $salon = $examen["Salon_desc"]." (".$examen["Salon_desc_larga"].")";
        else
            $salon = $examen["Salon_desc"];
    }

    $semestre = "Electiva";
    if($examen["Materia_semestre"] != "")
        $semestre = $examen["Materia_semestre"];
    
    
    $col = 1;
    $spreadsheet->setActiveSheetIndex(0)
        ->setCellValueByColumnAndRow($col++, $row, $semestre)
        ->setCellValueByColumnAndRow($col++, $row, $examen["Materia_desc"]." (".$examen["Carrera_prefijo"]." ".$examen["PlanEstudio_desc"].")")
        ->setCellValueByColumnAndRow($col++, $row, fechaSlash($fechaIArr[0]))
        ->setCellValueByColumnAndRow($col++, $row, $horas)
        ->setCellValueByColumnAndRow($col++, $row, $salon)
        ->setCellValueByColumnAndRow($col++, $row, $examen["Usuario_apellidos"]." ".$examen["Usuario_nombre"]);

    $spreadsheet->getActiveSheet()->duplicateStyle($bodyStyle, 'A'.$row.':F'.$row);

    /*$spreadsheet->getActiveSheet()->getStyle('A'.$row)->getAlignment()->setWrapText(true);
    $spreadsheet->getActiveSheet()->getStyle('B'.$row)->getAlignment()->setWrapText(true);
    $spreadsheet->getActiveSheet()->getStyle('E'.$row)->getAlignment()->setWrapText(true);*/
    $row++;

}//for each materias

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

$spreadsheet->getActiveSheet()->setAutoFilter($spreadsheet->getActiveSheet()->calculateWorksheetDimension());

//Auto ajustar anchos
$spreadsheet->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
//$spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(15);
//$spreadsheet->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(35);
$spreadsheet->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(35);
//$spreadsheet->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);

// 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="examenesExtraordinarios_'.date("y-m-d").'.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;