sgi/apsa/export/xls_horariogrupo.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;


function getHora($h, $i){
    $cadena = "";
    if($h<10){
        $cadena.="0";
    }
    $cadena.=$h.":";
    if($i*DURACION_STEP < 10){
        $cadena.='0';
    }
    $cadena.=($i*DURACION_STEP);
    return $cadena;
}

function getCoordenada($dia, $hora, $hora_ini, $row = 4){
    $inicio = explode(':',$hora_ini);
    $inicio = intval($inicio[0]);

    $letra = chr(ord('A')+$dia);
    $tmp = explode(":", $hora);
    $row = (intval($tmp[0])-$inicio)*FRACCION_HORA + $row;

    $row+=(intval($tmp[1]))/60*FRACCION_HORA;

    return $letra.$row;
}

function getCoordenadaMerge($dia, $hora, $dura, $hora_ini, $row = 4){
    $start = $row;
    $inicio = explode(':',$hora_ini);
    $inicio = intval($inicio[0]);

    $letra = chr(ord('A')+$dia);
    $tmp = explode(":", $hora);
    $row = (intval($tmp[0])-$inicio)*FRACCION_HORA + $row;

    $row+=(intval($tmp[1]))/60*FRACCION_HORA;

    $range = $letra.$row.":";

    $letra = chr(ord('A')+$dia);
    $row = $start;
    $tmp = explode(":", $hora);
    $row = (intval($tmp[0])-$inicio)*FRACCION_HORA + $row;

    $row+=(intval($tmp[1]))/60*FRACCION_HORA;
    $row+=$dura/15 - 1;

    $range .= $letra.$row;
    return $range;
}


//--- Objeto para validar usuario. El id de usuario lo lee desde sesión
$objSesion = new ValidaSesion($pdo, 272, APSA);
if(!$objSesion->tieneAcceso()){
    echo "Error de sesión. No tienes acceso a esta página.";
    exit();
}

unset($objValida);

$filter_modificados = 0;//todos

if(isset($_POST["grupo"]) && $_POST["grupo"]!=0){
    $gpo = filter_input(INPUT_POST, "grupo", FILTER_SANITIZE_NUMBER_INT);//limpia texto
    $stmt = $pdo->prepare('Select * from fs_grupo(:gpo, :periodo)');
    $stmt->bindParam(":gpo", $gpo);
}
else{
    $stmt = $pdo->prepare('Select * from fs_grupo(NULL, :periodo)');
}
$stmt->bindParam(":periodo", $_SESSION["periodo_id"]);

if(!$stmt->execute()){
    $errorDesc = "Ocurrió un error al cargar el grupo.";
}else{
    $grupo_rs = $stmt->fetchAll();
    if(!is_array($grupo_rs) || count($grupo_rs) == 0){
        $stmt->closeCursor();
        header("Location: ../grupos.php?error=6");
        exit();
    }
}
$stmt->closeCursor();

$stmt = $pdo->prepare('Select * from fs_dia(NULL)');
if(!$stmt->execute()){
    echo "Error al obtener los días";
    print_r($stmt->errorInfo());
    exit();
}
$dias_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('Horarios')
    ->setDescription('Horarios del grupo '.$gpo);

$headerStyle = new Style();
$headerStyle->applyFromArray(
    [
        'fill' => [
            'fillType' => Fill::FILL_SOLID,
            'color' => ['argb' => 'FF001d68'],
        ],
        'borders' => [
            'bottom' => ['borderStyle' => Border::BORDER_THIN, 'color' => ['argb' => 'FFFFFFFF'],],
            'right' => ['borderStyle' => Border::BORDER_MEDIUM, 'color' => ['argb' => 'FFFFFFFF'],],
        ],
        'font' => [
            'bold'  =>  true,
            'color' => ['argb' => 'FFFFFFFF'],
            'name' => 'Indivisa Text Sans',
        ],
        'alignment' => [
            'horizontal' => Alignment::HORIZONTAL_CENTER,
        ],
    ]
);

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

$bodyStyle = new Style();
$bodyStyle->applyFromArray(
    [
        'alignment' => [
            'vertical' => Alignment::VERTICAL_TOP,
            'wrapText' => true
        ],
        'font' => [
            'size' => 10,
            'name' => 'Indivisa Text Sans',
        ],
        'borders' => [
            /*'allBorders' => [
                'borderStyle' => Border::BORDER_THIN,
                'color' => ['argb' => 'FF001d68'],
            ],*/
            'allBorders' => [
                'borderStyle' => Border::BORDER_THICK,
                'color' => ['argb' => 'FFFFFFFF'],
            ],
        ],
        /*'fill'=>[
            'fillType' => Fill::FILL_SOLID,
            'color' => ['argb' => 'FFDEE2E6'],
        ]*/
    ]
);

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



$row_base = 6;
$page = 0;
foreach($grupo_rs as $grupo){
    if($page>0){
        $spreadsheet->createSheet();
    }
    $spreadsheet->setActiveSheetIndex($page);

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

    // Rename worksheet
    $gpo = $grupo["Grupo_desc"]." ".$grupo["Carrera_prefijo"];
    $spreadsheet->getActiveSheet()->setTitle($gpo);

    $row = $row_base;

    //Obtiene horario del grupo
    $stmt = $pdo->prepare('Select * from fs_horariogrupo(:gpo, NULL, NULL) order by "Dia_id", "Horario_hora"');
    $stmt->bindParam(":gpo", $grupo["Grupo_id"]);

    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();
        $horaMin = horaMin($horario_rs);
        $horaMax = horaMax($horario_rs);
    }
    $stmt->closeCursor();


    $hora_ini = date('H', strtotime($horaMin));
    $hora_fin = date('H', strtotime($horaMax));
    if(intval(date('i', strtotime($horaMax))) != 0)
        $hora_fin++;

    
    // Add some data
    $spreadsheet->getActiveSheet()
        ->setCellValue('A'.$row, 'Grupo '.$gpo);
    $spreadsheet->getActiveSheet()->mergeCells('A'.$row.':C'.$row);
    $spreadsheet->getActiveSheet()->duplicateStyle($titleStyle, 'A'.$row.':E'.$row);

    //$spreadsheet->getActiveSheet()->duplicateStyle($bodyStyle, 'A4:G'.(4+FRACCION_HORA*(HORA_FINAL-HORA_INICIO)));
    
    
    $row += 2;
    $spreadsheet->getActiveSheet()
            ->setCellValue('A'.$row, "Hora");

    $col = 2;
    foreach($dias_rs as $dia){
        $spreadsheet->getActiveSheet()
            ->setCellValueByColumnAndRow($col++, $row, $dia["Dia_desc"]);
    }
    $spreadsheet->getActiveSheet()->duplicateStyle($headerStyle, 'A'.$row.':G'.$row);

    $row++;
    $start_row = $row;

    $spreadsheet->getActiveSheet()->duplicateStyle($bodyStyle, 
        getCoordenada(1, $hora_ini, $hora_ini, $start_row).":".getCoordenada(6, $hora_fin, $hora_ini, $start_row)
    );

    // HORA_INICIO  a HORA_FINAL   cada FRACCION_HORA (4) o DURACION_STEP (15)

    // for($h = $hora_ini, $i=0; $h < $hora_fin; $h++, $i++){
    //for($h = HORA_INICIO; $h < HORA_FINAL; $h++){
    for($h = intval($hora_ini); $h < intval($hora_fin); $h++){
        for($i=0; $i<FRACCION_HORA; $i++){
            $spreadsheet->getActiveSheet()
                ->setCellValueByColumnAndRow(1, $row++, getHora($h, $i));
        }
    }//for each materias

    foreach($horario_rs as $bloque){
        $coord = getCoordenada($bloque["Dia_id"], $bloque["Horario_hora"], $hora_ini, $start_row);
        $salon = "Pendiente";
        if(!empty($bloque["Salon_desc"])){
            $salon=$bloque["Salon_desc"];
        }

        $profesores_all = "";
        if(empty($bloque["TipoSubmateria_id"]) ){
            $stmt = $pdo->prepare('Select * from fs_profesorhorariogrupo(:id)');
            $stmt->bindParam(":id", $bloque["HorarioGrupo_id"]);

            if(!$stmt->execute()){
                $t = $stmt->errorInfo();
                $errorDesc = "Ocurrió un error al obtener los datos de los profesores ".$t[2];
                break;
            }
            $profesores_rs = $stmt->fetchAll();
            $stmt->closeCursor();
            $prof_nombre = "";
            if(isset($profesores_rs) && count($profesores_rs) > 0){
                foreach($profesores_rs as $profesor){
                    $prof_nombre = $profesor["Usuario_apellidos"]." ".$profesor["Usuario_nombre"]."\n";
                    //$profesores_all .= $prof_clave.$prof_nombre."<br>";
                    $profesores_all .=$prof_nombre;
                }
                $profesores_all = substr($profesores_all, 0, -1);
            }
        }

        $richText = new \PhpOffice\PhpSpreadsheet\RichText\RichText();
        $richText->createText(substr($bloque["Horario_hora"], 0, -3)." ");
        $titulo = $richText->createTextRun($bloque["Materia_desc"]);
        $titulo->getFont()->setBold(true);
        $richText->createText("\nSalón: ".$salon."\n".$profesores_all);

        $spreadsheet->getActiveSheet()
            //->setCellValue($coord, $bloque["Materia_desc"]."\nSalón: ".$salon."\n".$profesores_all);
            ->setCellValue($coord, $richText);

        $spreadsheet->getActiveSheet()->mergeCells( getCoordenadaMerge($bloque["Dia_id"], $bloque["Horario_hora"], $bloque["Horario_duracion"], $hora_ini, $start_row) );
        $spreadsheet->getActiveSheet()->getStyle($coord)->getFill()
            ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
            ->getStartColor()
            ->setARGB('FFDEE2E6');
    }


    $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(6);
    $spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(20);
    $spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(20);
    $spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(20);
    $spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(20);
    $spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(20);
    $spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(20);

    $page++;
}
$spreadsheet->setActiveSheetIndex(0);

// Redirect output to a client’s web browser (Xlsx)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
if(count($grupo_rs)==1)
    header('Content-Disposition: attachment;filename="horario_'.$gpo.'.xlsx"');
else
    header('Content-Disposition: attachment;filename="horarioGrupos.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;