sgi/apsa/export/xls_examenesfinales.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, 56, APSA);
if(!$objSesion->tieneAcceso()){
    header("Location: ".$pag."?error=3");
    exit();
}

unset($objValida);

$filter_modificados = 0;//todos


if(!isset($_POST["grupo"]) || !isset($_POST["fecha_inicial"]) ){
    echo "No se recibieron los datos de consulta."; exit();
}

$filter_grupo = filter_input(INPUT_POST, "grupo", FILTER_SANITIZE_NUMBER_INT);//limpia texto
$filter_fini = trim(filter_input(INPUT_POST, "fecha_inicial", FILTER_SANITIZE_STRING,array('flags' => FILTER_FLAG_STRIP_LOW)));//limpia texto
//$filter_ffin = trim(filter_input(INPUT_POST, "fecha_final", FILTER_SANITIZE_STRING,array('flags' => FILTER_FLAG_STRIP_LOW)));//limpia texto
$filter_ffin = date('Y-m-d', strtotime(fechaGuion($filter_fini).' +7 days')) ;


//Obtiene grupos del periodo
if(!empty($filter_grupo)){
    $stmt = $pdo->prepare('Select * from fs_grupo(:gpo, :periodo)');
    $stmt->bindParam(":gpo", $filter_grupo);
}else{
    $stmt = $pdo->prepare('Select * from fs_grupo(NULL, :periodo)');
}
$stmt->bindParam(":periodo", $_SESSION["periodo_id"]);
if(!$stmt->execute()){
    header("Location: grupos.php?error=4");
    exit();
}else{
    $grupo_rs = $stmt->fetch();
}
$stmt->closeCursor();

$pendiente = "Pendiente";
$materia_old = -1;
                    
//Obtiene horario del grupo
$stmt = $pdo->prepare('Select * from fs_horariogrupoexamenes(:gpo,:fini, :ffin);');
$stmt->bindParam(":gpo", $grupo_rs["Grupo_id"]);
$stmt->bindParam(":fini", $filter_fini);
$stmt->bindParam(":ffin", $filter_ffin);

if(!$stmt->execute()){
    echo "Ocurrió un error al cargar los horarios del grupo";
    print_r($stmt->errorInfo());
    //header("Location: ".$pag."?error=2");
    exit();
}else{
    $horario_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('Exámenes finales')
    ->setDescription('Exámenes finales en el periodo.');

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

$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',
        ]
    ]
);

$titleStyle = new Style();
$titleStyle->applyFromArray(
    [
        'font' => [
            'bold'  =>  true,
            'color' => ['argb' => 'FF001e61'],
            'size' => 16,
            'name' => 'Indivisa Text Sans',
        ],
        'alignment' => [
            'horizontal' => Alignment::HORIZONTAL_CENTER,
        ],
    ]
);

$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],*/
        ],
    ]
);

$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('D1');
$drawing->setHeight(100);
$drawing->setOffsetX(-20);
//agrega imagen
$drawing->setWorksheet($spreadsheet->getActiveSheet());

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

// Add some data
$gpo = $grupo_rs["Grupo_desc"]." ".$grupo_rs["Carrera_prefijo"];
$spreadsheet->setActiveSheetIndex(0)
    ->setCellValue('A1', 'EXÁMENES FINALES DEL GRUPO '.$gpo);
$spreadsheet->getActiveSheet()->mergeCells('A1:E1');
$spreadsheet->getActiveSheet()->duplicateStyle($titleStyle, 'A1:E1');

$row = $row_base;
$col = 1;

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

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

$row ++;
foreach($horario_rs as $horario){
    if(intval($horario["Materia_id"]) != $materia_old ){
        if((isset($filter_fecha) && strtotime($filter_fecha) >= strtotime($horario["fecha_inicial"]) && strtotime($filter_fecha) <= strtotime($horario["fecha_final"])  ) || !isset($filter_fecha)){

            $materia_old = intval($horario["Materia_id"]);
            
            $dia = ucfirst(diaNombre($horario["Dia_id"]))." ".fechaTexto($horario["Fecha"], false);
            $horas = date('H:i', strtotime($horario["Horario_hora"]))." - ".date('H:i', strtotime($horario["Horario_hora_final"]));
            
            
            //----- Carga profesores ----
            if($horario["TipoSubmateria_id"] == 0){//sin submaterias
                $stmt = $pdo->prepare('Select * from fs_profesorhorariogrupo(:id)');
                $stmt->bindParam(":id", $horario["HorarioGrupo_id"]);

                if(!$stmt->execute()){
                    $t = $stmt->errorInfo();
                    echo "Ocurrió un error al obtener los datos de los profesores ".$t[2];
                    exit();
                }
                $profesores_rs = $stmt->fetchAll();
                $stmt->closeCursor();
                $prof_nombre = "";
                if(isset($profesores_rs) && count($profesores_rs) > 0){
                    $profesores_all = "";
                    foreach($profesores_rs as $profesor){
                        $prof_clave = $profesor["Usuario_claveULSA"].' - ';
                        $prof_nombre = $profesor["Usuario_apellidos"]." ".$profesor["Usuario_nombre"];
                        $profesores_all .= $prof_nombre."\n";
                    }
                }else{
                    $profesores_all = $pendiente;
                }
                
                $salon = $pendiente;
                if($horario["Salon_desc"]!= ""){
                    if($horario["Salon_desc_larga"] != "")
                        $salon = $horario["Salon_desc"]." (".$horario["Salon_desc_larga"].")";
                    else
                        $salon = $horario["Salon_desc"];
                }
                
                $col = 1;
                $spreadsheet->setActiveSheetIndex(0)
                    ->setCellValueByColumnAndRow($col++, $row, $profesores_all)
                    ->setCellValueByColumnAndRow($col++, $row, $horario["Materia_desc"]/*."\n". $horario["Materia_clave"]*/)
                    ->setCellValueByColumnAndRow($col++, $row, $dia)
                    ->setCellValueByColumnAndRow($col++, $row, $horas)
                    ->setCellValueByColumnAndRow($col++, $row, $salon);

                $spreadsheet->getActiveSheet()->duplicateStyle($bodyStyle, 'A'.$row.':E'.$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++;
            
            }else{//Con submaterias

                //----- Carga submaterias ----
                $stmt = $pdo->prepare('Select * from fs_submateriahorariogrupo(:id)');
                $stmt->bindParam(":id", $horario["HorarioGrupo_id"]);

                if(!$stmt->execute()){
                    $t = $stmt->errorInfo();
                    echo "Ocurrió un error al obtener los datos de los profesores ".$t[2];
                    exit();
                }
                $submaterias_rs = $stmt->fetchAll();
                $stmt->closeCursor();
                $sub_prof_num = 0;
                $profesores_all = "";
                if(count($submaterias_rs) > 0){
                    foreach($submaterias_rs as $submateria){
                        //---
                        $stmt = $pdo->prepare('Select * from fs_submateriahorariogrupoprofesor(:id,:sub)');
                        $stmt->bindParam(":id", $horario["HorarioGrupo_id"]);
                        $stmt->bindParam(":sub", $submateria["Submateria_id"]);

                        if(!$stmt->execute()){
                            $t = $stmt->errorInfo();
                            echo "Ocurrió un error al obtener los datos de los profesores ".$t[2];
                            exit();
                        }
                        $profesores_rs = $stmt->fetchAll();
                        $stmt->closeCursor();

                        $profesores_all = "";
                        $sub_prof_num += count($profesores_rs);
                        if(isset($profesores_rs) && count($profesores_rs) > 0){
                            foreach($profesores_rs as $profesor){
                                $prof_clave = $profesor["Usuario_claveULSA"].' - ';
                                $prof_nombre = $profesor["Usuario_apellidos"]." ".$profesor["Usuario_nombre"];
                                $profesores_all .= $prof_nombre."\n";
                            }
                        }else{
                            $profesores_all = $pendiente;
                        }
                        
                        $salon = $pendiente;
                        if($submateria["Salon_desc"]!= ""){
                            if($submateria["Salon_desc_larga"] != "")
                                $salon = $submateria["Salon_desc"]." (".$submateria["Salon_desc_larga"].")";
                            else
                                $salon = $submateria["Salon_desc"];
                        }
                        
                        $col = 1;
                        $spreadsheet->setActiveSheetIndex(0)
                            ->setCellValueByColumnAndRow($col++, $row, $profesores_all)
                            ->setCellValueByColumnAndRow($col++, $row, $submateria["Submateria_desc"]/*."\n". $horario["Materia_clave"]*/)
                            ->setCellValueByColumnAndRow($col++, $row, $dia)
                            ->setCellValueByColumnAndRow($col++, $row, $horas)
                            ->setCellValueByColumnAndRow($col++, $row, $salon);

                        $spreadsheet->getActiveSheet()->duplicateStyle($bodyStyle, 'A'.$row.':E'.$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++;
                        
                    }
                    /*if($sub_prof_num > 0)
                        $spreadsheet->setActiveSheetIndex(0)
                            ->setCellValueByColumnAndRow(1, $row, $profesores_all);
                    else
                        $spreadsheet->setActiveSheetIndex(0)
                            ->setCellValueByColumnAndRow(1, $row, $pendiente);*/
                }
                unset($profesores_rs);
            }//fin con submaterias

            
        }
    }
}//for each materias

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

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

$spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(35);
$spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(30);
$spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(20);
$spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(12);

// 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="examenesFinales_'.$gpo.'.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;