sgi/gema/export/xls_estados.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;


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, 121, GEMA);
if(!$objSesion->tieneAcceso()){
    echo "No tienes permiso de ver esta página. Revisa que tu sesión siga activa.";
    exit();
}
unset($objValida);


$filter_periodo = $_SESSION["periodo_id"];
if(isset($_POST["plan"]) && isset($_POST["fini"], $_POST["ffin"]) && isset($_SESSION["nivel_id"])) {
    $filter_plan = filter_input(INPUT_POST, "plan", FILTER_SANITIZE_NUMBER_INT);//limpia texto
    $filter_revalida = filter_input(INPUT_POST, "revalida", 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
    $nom = trim(filter_input(INPUT_POST, "prefijo", FILTER_SANITIZE_STRING,array('flags' => FILTER_FLAG_STRIP_LOW)));//limpia texto
}else{
    echo "No se recibieron los datos.";
    exit();
}

$query = "NULL";
if($filter_plan != 0)
    $query = ":plan";

if($filter_revalida == 1)
    $stmt = $pdo->prepare('Select * from fs_estadoalumno_cambios(:nivel, '.$query.', :fini, :ffin, true)');//sólo no revalidadas
else
    $stmt = $pdo->prepare('Select * from fs_estadoalumno_cambios(:nivel, '.$query.', :fini, :ffin, false)');//sólo no revalidadas
$stmt->bindParam(":nivel", $_SESSION["nivel_id"]);
$stmt->bindParam(":fini", $filter_fini);
$stmt->bindParam(":ffin", $filter_ffin);
if($filter_plan != 0){ $stmt->bindParam(":plan", $filter_plan); }

if(!$stmt->execute()){
    echo "Ocurrió un error al cargar el total de estados.";
    //print_r($stmt->errorInfo());
    exit();
}else{
    $estado_rs = $stmt->fetchAll();

    $estadoArr = array();
    $estado_last = -1;
    $subestadoArr = array();
    $e = 0;
    $total = 0;
    foreach($estado_rs as $estado){
        if($estado_last != $estado["EstadoAlumno_id"]){
            if($estado_last != -1){
                $estadoArr[$e]["subestadoArr"]=$subestadoArr;
                $estadoArr[$e]["total"] = $total;
                $e++;
                $total = 0;
            }
            $estado_last = $estado["EstadoAlumno_id"];
            $subestadoArr = array();
        }
        $estadoArr[$e] = array("id" => $estado["EstadoAlumno_id"], "desc" =>$estado["EstadoAlumno_desc"], "color"=>$estado["EstadoAlumno_color"], "total"=>0, "subestadoArr"=>array());
        $subestadoArr[] = array("id" => $estado["SubEstadoAlumno_id"], "desc" =>$estado["SubEstadoAlumno_desc"], "total"=>$estado["SubEstadoAlumno_total"]);
        $total+= $estado["SubEstadoAlumno_total"];
    }
    if($estado_last != -1){
        $estadoArr[$e]["subestadoArr"]=$subestadoArr;
        $estadoArr[$e]["total"] = $total;
    }
    unset($e);
}
$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('Estado de alumnos')
    ->setDescription('Cambio de estado de alumnos.');


$alumnoStyle = new Style();
$alumnoStyle->applyFromArray(
    [
        'font' => [
            'bold'  =>  true,
            'color' => ['argb' => 'FF001d68'],
            'size' => 14,
        ]
    ]
);

$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->getActiveSheet()->setTitle('Programas');

$row = 1;
$spreadsheet->setActiveSheetIndex($sheet)
                ->setCellValueByColumnAndRow(1, $row++, "Programa: ". $nom)
                ->setCellValueByColumnAndRow(1, $row++, fechaSlash($filter_fini)." al ". fechaSlash($filter_ffin));

//$spreadsheet->getActiveSheet()->mergeCells('A1:B1');
$spreadsheet->getActiveSheet()->duplicateStyle($alumnoStyle, 'A1:A2');


$row = 4;
$col = 1;
$spreadsheet->setActiveSheetIndex($sheet)
                ->setCellValueByColumnAndRow($col++, $row, "Estado")
                ->setCellValueByColumnAndRow($col++, $row, "Subestado")
                ->setCellValueByColumnAndRow($col++, $row, "Total")
                ->setCellValueByColumnAndRow($col++, $row, "Porcentaje");
$colMax = $col;

$colLetter = numberToLetter($colMax-1);

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

$row++;
$rowStart = $row;

foreach($estadoArr as $edo){
    $spreadsheet->setActiveSheetIndex($sheet)
                ->setCellValueByColumnAndRow(1, $row, $edo["desc"]);
    if(count($edo["subestadoArr"]) > 1)
        $spreadsheet->getActiveSheet()->mergeCells('A'.$row.':A'.($row+count($edo["subestadoArr"])-1));
    $row += count($edo["subestadoArr"]);
}

$colStart = 2;
$row = $rowStart;
foreach($estadoArr as $edo){
    $total_edo = $edo["total"];
    
    foreach($edo["subestadoArr"] as $subedo){
        $col = $colStart;
        $spreadsheet->setActiveSheetIndex($sheet)
            ->setCellValueByColumnAndRow($col++, $row, $subedo["desc"])
            ->setCellValueByColumnAndRow($col++, $row, $subedo["total"])
            ->setCellValueByColumnAndRow($col++, $row, $subedo["total"]/$total_edo);
        
        $colLetter = numberToLetter($col-1);
        $spreadsheet->setActiveSheetIndex($sheet)->getStyle($colLetter.$row.':'.$colLetter.$row)->getNumberFormat()
            ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_PERCENTAGE_00);
        $row++;
    }
}

$spreadsheet->getActiveSheet()->duplicateStyle($headerStyle, 'A'.$rowStart.':A'.($row-1));
$spreadsheet->getActiveSheet()->getStyle('B'.$rowStart.':B'.($row-1))->getFont()->setBold(true);


//Auto ajustar anchos
for($col = 1; $col < $colMax; $col++)
    $spreadsheet->getActiveSheet()->getColumnDimension(numberToLetter($col))->setAutoSize(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="estadosAlumnos_'.$nom.'.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;