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

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

unset($objValida);

if(!isset($_POST["area"])){
    header("Location: ".$pag."?error=0");
    exit();
}

//Obtiene profesores
$query = "";
if(isset($_POST["area"]) && is_numeric($_POST["area"]) && $_POST["area"]!=0){
    $query .= ":area,";
    $area = filter_input(INPUT_POST, "area", FILTER_SANITIZE_NUMBER_INT);//limpia texto
}else{
    $query .= "NULL,";
}
if(isset($_POST["desc"])){
    $query .= ":desc,";
    $filter_desc = trim(filter_input(INPUT_POST, "desc", FILTER_SANITIZE_STRING,array('flags' => FILTER_FLAG_STRIP_LOW)));//limpia texto
}else{
    $query .= "NULL,";
}

$stmt = $pdo->prepare('Select * from fs_profesoresareahoras(:per, '.$query.' 1, 0, NULL)');//sólo activos
$stmt->bindParam(":per", $_SESSION["periodo_id"]);

if(isset($area)) $stmt->bindParam(":area", $area);
if(isset($filter_desc)) $stmt->bindParam(":desc", $filter_desc);
if(!$stmt->execute()){
    print_r($stmt->errorInfo());
    //header("Location:".$pag."?error=4");
    exit();
}
$profesores_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('Profesores por materia')
    ->setDescription('Profesores por por materia en el periodo.');

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


// Rename worksheet
$spreadsheet->getActiveSheet()->setTitle($_SESSION["periodo_desc"]);

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

// Add some data
$spreadsheet->setActiveSheetIndex(0)
    ->setCellValue('A'.$row_base, 'Clave')
    ->setCellValue('B'.$row_base, 'Profesor')
    ->setCellValue('C'.$row_base, 'Materias')
    ->setCellValue('D'.$row_base, 'Horas/Materia')
    ->setCellValue('E'.$row_base, 'Horas totales');
$spreadsheet->getActiveSheet()->duplicateStyle($headerStyle, 'A'.$row_base.':E'.$row_base);


//Obtiene horario del usuario
if(isset($area)){
    $stmt = $pdo->prepare('Select * from fs_mihorario(:usr, :per, 3) WHERE "TipoHorario_id" = 3 AND "Area_id"=:area');//Obtiene calendario docente
    $stmt->bindParam(":area", $area);
}else{
    $stmt = $pdo->prepare('Select * from fs_mihorario(:usr, :per, 3) WHERE "TipoHorario_id" = 3 ');//Obtiene calendario docente
}
$stmt->bindParam(":per", $_SESSION["periodo_id"]);

$row = $row_base+1;
foreach($profesores_rs as $profesor){
    $stmt->bindParam(":usr", $profesor["Usuario_id"]);

    if(!$stmt->execute()){
        //$t = $stmt->errorInfo();
        echo "Ocurrió un error al obtener los horarios de los profesores";
        print_r($stmt->errorInfo());
        exit();
    }
    $horario_rs = $stmt->fetchAll();
    $materiasArr = array();

    if(count($horario_rs) > 0){
        //$i++;

        for($rs_i = 0; $rs_i < count($horario_rs);){
            if((!isset($area) || $area=="" ) || (isset($area) && $area == $horario_rs[$rs_i]["Area_id"])){
                $horas = $horario_rs[$rs_i]["Duracion"]/60;
                $nombreArr = array();
                $key = "";
                do{
                    $nombreArr[] = $horario_rs[$rs_i]["Materia_desc"]." (".$horario_rs[$rs_i]["Carrera_prefijo"].")";
                    $key .= $horario_rs[$rs_i]["Materia_desc"].$horario_rs[$rs_i]["Carrera_prefijo"];
                    $rs_i++;
                }while($rs_i < count($horario_rs) && date('H:i', strtotime($horario_rs[$rs_i-1]["Hora_inicio"])) == date('H:i', strtotime($horario_rs[$rs_i]["Hora_inicio"])) && $horario_rs[$rs_i]["Dia_id"] == $horario_rs[$rs_i-1]["Dia_id"]);

                $materiasArr[] = array("key"=>md5($key), "nombreArr" => $nombreArr, "horas"=>$horas);
            }
        }

        //fusiona horarios
        for($ii = 0; $ii < count($materiasArr)-1; $ii++){
            for($jj = $ii+1; $jj < count($materiasArr); ){
                if($materiasArr[$ii]["key"] == $materiasArr[$jj]["key"]){
                    $materiasArr[$ii]["horas"] += $materiasArr[$jj]["horas"];
                    array_splice($materiasArr, $jj, 1);
                }else
                    $jj++;
            }
        }
        
        
        $col = 1;
        
        $prof = $profesor["Usuario_apellidos"]." ".$profesor["Usuario_nombre"];
        if(trim($prof) == "")
            $prof = "- PENDIENTE -";
        

        $spreadsheet->getActiveSheet()
            ->setCellValueByColumnAndRow(1, $row, $profesor["Usuario_claveULSA"])
            ->setCellValueByColumnAndRow(2, $row, $prof);
        
        $sumaHoras = 0;
        $first =$row;
        foreach($materiasArr as $materia){
            $col = 3;
            $mat_texto = "";
            foreach($materia["nombreArr"] as $nombre){
                $mat_texto.=$nombre."\n";
            }
            $spreadsheet->getActiveSheet()
                ->setCellValueByColumnAndRow($col++, $row, $mat_texto)
                ->setCellValueByColumnAndRow($col++, $row, sprintf('%0.2f', $materia["horas"])." hrs");
            $row++;
            $sumaHoras+=$materia["horas"];
        }
        $spreadsheet->getActiveSheet()
            ->setCellValueByColumnAndRow($col++, $first, sprintf('%0.2f', $sumaHoras)." hrs");

        if($first != $row-1){
            $spreadsheet->getActiveSheet()->mergeCells('A'.$first.':A'.($row-1));
            $spreadsheet->getActiveSheet()->getStyle('A'.$first)->getAlignment()->setVertical('center');
            $spreadsheet->getActiveSheet()->mergeCells('B'.$first.':B'.($row-1));
            $spreadsheet->getActiveSheet()->getStyle('B'.$first)->getAlignment()->setVertical('center');
            $spreadsheet->getActiveSheet()->mergeCells('E'.$first.':E'.($row-1));
            $spreadsheet->getActiveSheet()->getStyle('E'.$first)->getAlignment()->setVertical('center');
        }
        
        /*$xtpl->assign("TD_TEXTO", $profesor["Usuario_claveULSA"]);
        $xtpl->parse("main.renglon.data.celda.borde");
        $xtpl->parse("main.renglon.data.celda");
        $xtpl->parse("main.renglon.data");
        
        $xtpl->assign("TD_TEXTO", $profesor["Usuario_apellidos"]." ".$profesor["Usuario_nombre"]);
        $xtpl->parse("main.renglon.data.celda.borde");
        $xtpl->parse("main.renglon.data.celda");
        $xtpl->parse("main.renglon.data");
        
        $sumaHoras = 0;
        foreach($materiasArr as $materia){
            $xtpl->assign("WIDTH", "80");
            $mat_texto = "";
            foreach($materia["nombreArr"] as $nombre){
                $mat_texto.=$nombre."<br>";
            }
            $xtpl->assign("TD_TEXTO", $mat_texto);
            $xtpl->parse("main.renglon.data.celda_tabla.renglon.celda");
            $xtpl->assign("WIDTH", "20");
            $xtpl->assign("TD_TEXTO", sprintf('%0.2f', $materia["horas"])." hrs");
            $xtpl->assign("CLASS", "derecha");
            $sumaHoras+=$materia["horas"];
            $xtpl->parse("main.renglon.data.celda_tabla.renglon.celda.class");
            $xtpl->parse("main.renglon.data.celda_tabla.renglon.celda");
            $xtpl->parse("main.renglon.data.celda_tabla.renglon");
        }
        $xtpl->parse("main.renglon.data.celda_tabla.borde");
        $xtpl->parse("main.renglon.data.celda_tabla");
        $xtpl->parse("main.renglon.data");

        $xtpl->assign("TD_TEXTO", sprintf('%0.2f', $sumaHoras)." hrs");
        $xtpl->parse("main.renglon.data.celda.borde");
        $xtpl->parse("main.renglon.data.celda");
        $xtpl->parse("main.renglon.data");*/
    }
    //$xtpl->parse("main.renglon");
}//foreach prof
$stmt->closeCursor();




//$spreadsheet->getActiveSheet()->getStyle('A1:H1')->getFont()->setBold(true);
$spreadsheet->getActiveSheet()->setAutoFilter('A'.$row_base.':E'.$row_base);

//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);

// 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="materiasProfesores_'.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;