sgi/gema/export/xls_reprobados.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);
};

function califTexto($col){
    switch($col%3){
        case 0: return "Total";
        case 1: return "No aprobados";
        case 2: return "Porcentaje";
    }
    return "Error";
}

//--- Objeto para validar usuario. El id de usuario lo lee desde sesión
$objSesion = new ValidaSesion($pdo, 123, 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"]) && is_numeric($_POST["plan"]) && isset($_POST["sem"]) && is_numeric($_POST["sem"])) {
    $filter_plan = filter_input(INPUT_POST, "plan", FILTER_SANITIZE_NUMBER_INT);//limpia texto
    $filter_sem = filter_input(INPUT_POST, "sem", FILTER_SANITIZE_NUMBER_INT);//limpia texto
    $nom = trim(filter_input(INPUT_POST, "prefijo", FILTER_SANITIZE_STRING,array('flags' => FILTER_FLAG_STRIP_LOW)));//limpia texto
}


//Obtiene tipos de calificaciones
$stmt = $pdo->prepare('Select * from fs_tipocalificacion(NULL) WHERE "TipoCalificacion_esEquivalencia" = false ');//no revalidadas
if(!$stmt->execute()){
    $errorDesc = "Ocurrió un error al cargar los tipos de calificaciones";
    //print_r($stmt->errorInfo());
    exit();
}else{
    $tipocalif_rs = $stmt->fetchAll();
}
$stmt->closeCursor();

//materias por asignar
$labelMateriasArr =array();
$stmt = $pdo->prepare('Select * from fs_materiagrupo(:plan, :sem, NULL) WHERE "Area_hasAlerta" = true');
$stmt->bindParam(":plan", $filter_plan);
$stmt->bindParam(":sem", $filter_sem);
if(!$stmt->execute()){
    echo "Ocurrió un error al leer las materias del semestre.";
    //print_r($stmt->errorInfo());
    exit();
}else{
    $mat_rs = $stmt->fetchAll();
    foreach($mat_rs as $mat){
        $labelMateriasArr[$mat["Materia_id"]] = $mat["Materia_desc"];
    }
    $labelMateriasArr = $labelMateriasArr;
}

//reporte por plan
$stmt = $pdo->prepare('Select * from fs_reprobadas_plan(:periodo, NULL, false)');//sólo no revalidadas
$stmt->bindParam(":periodo", $_SESSION["periodo_id"]);
if(!$stmt->execute()){
    echo "Ocurrió un error al cargar las materias reprobadas por plan";
    exit();
    //print_r($stmt->errorInfo());
}else{
    $rs = $stmt->fetchAll();
    $planArr = array();
    $plan_last = -1;
    $califArr = array();
    $p = 0;
    foreach($rs as $plan){
        if($plan_last != $plan["PlanEstudio_id"]){
            if($plan_last != -1){
                $planArr[$p]["califArr"]=$califArr;
                $p++;
            }
            $plan_last = $plan["PlanEstudio_id"];
            $califArr = array();
            foreach($tipocalif_rs as $tipo){
                $califArr[$tipo["TipoCalificacion_id"]] = array("total"=>0, "reprobadas"=>0);
            }
        }
        $planArr[$p] = array("id" => $plan["PlanEstudio_id"], "desc" =>$plan["Carrera_desc"]." ".$plan["PlanEstudio_desc"], "califArr"=>array());
        $califArr[$plan["TipoCalificacion_id"]] = array("total"=>$plan["Calificacion_total"], "reprobadas"=>$plan["Calificacion_reprobadas"]);
    }
    if($plan_last != -1){
        $planArr[$p]["califArr"]=$califArr;
    }
    unset($p);
}
$stmt->closeCursor();

//reporte por materias
$stmt = $pdo->prepare('Select * from fs_reprobadas_materia(:periodo, :plan, :sem, false)');//sólo no revalidadas
$stmt->bindParam(":periodo", $_SESSION["periodo_id"]);
$stmt->bindParam(":plan", $filter_plan);
$stmt->bindParam(":sem", $filter_sem);
if(!$stmt->execute()){
    echo "Ocurrió un error al cargar las materias reprobadas por grupo";
    exit();
    //print_r($stmt->errorInfo()); exit();
}else{
    $rs = $stmt->fetchAll();
    $matArr = array();
    $tipo_last = -1;
    $califArr = array();
    $m = 0;
    foreach($rs as $grupo){
        if($tipo_last != $grupo["TipoCalificacion_id"]){
            if($tipo_last != -1){
                $matArr[$m]["califArr"]=$califArr;
                $m++;
            }
            $tipo_last = $grupo["TipoCalificacion_id"];
            $califArr = array();
            foreach($mat_rs as $mat){
                $califArr[$mat["Materia_id"]] = array("total"=>0, "reprobadas"=>0, "data"=>0, "materia"=>$mat["Materia_desc"]);
            }
        }
        $matArr[$m] = array("id" => $grupo["TipoCalificacion_id"], "desc" =>$grupo["TipoCalificacion_desc"], "califArr"=>array());
        $califArr[$grupo["Materia_id"]] = array("total"=>$grupo["Calificacion_total"], "reprobadas"=>$grupo["Calificacion_reprobadas"],
                "data"=>($grupo["Calificacion_total"]==0)?0:round($grupo["Calificacion_reprobadas"]*100/$grupo["Calificacion_total"]), "materia"=>$grupo["Materia_desc"]);

    }
    if($tipo_last != -1){
        $matArr[$m]["califArr"]=$califArr;
    }
    unset($m);
}
$stmt->closeCursor();

//reporte por grupos
$stmt = $pdo->prepare('Select * from fs_reprobadas_grupo(:periodo, :plan, :sem)');//sólo ord
$stmt->bindParam(":periodo", $_SESSION["periodo_id"]);
$stmt->bindParam(":plan", $filter_plan);
$stmt->bindParam(":sem", $filter_sem);
if(!$stmt->execute()){
    echo "Ocurrió un error al cargar las materias reprobadas por grupo";
    exit();
    //print_r($stmt->errorInfo()); exit();
}else{
    $rs = $stmt->fetchAll();
    $gpoArr = array();
    $grupo_last = -1;
    $califArr = array();
    $g = 0;
    foreach($rs as $grupo){
        if($grupo_last != $grupo["Grupo_id"]){
            if($grupo_last != -1){
                $gpoArr[$g]["califArr"]=$califArr;
                $g++;
            }
            $grupo_last = $grupo["Grupo_id"];
            $califArr = array();
            foreach($mat_rs as $mat){
                $califArr[$mat["Materia_id"]] = array("total"=>0, "reprobadas"=>0, "data"=>0, "materia"=>$mat["Materia_desc"]);
            }
        }
        $gpoArr[$g] = array("id" => $grupo["Grupo_id"], "desc" =>$grupo["Grupo_desc"]." ".$grupo["Carrera_prefijo"], "califArr"=>array());
        $califArr[$grupo["Materia_id"]] = array("total"=>$grupo["Calificacion_total"], "reprobadas"=>$grupo["Calificacion_reprobadas"],
                "data"=>($grupo["Calificacion_total"]==0)?0:round($grupo["Calificacion_reprobadas"]*100/$grupo["Calificacion_total"]), "materia"=>$grupo["Materia_desc"]);

    }
    if($grupo_last != -1){
        $gpoArr[$g]["califArr"]=$califArr;
    }
    unset($g);
}
$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('Alumnos no aprobados')
    ->setDescription('Alumnos no aprobados.');


$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, 2, "Programa académico");
$offsetCol = 2;
$col = 0;
foreach($tipocalif_rs as $tipo){
    $spreadsheet->setActiveSheetIndex($sheet)
                ->setCellValueByColumnAndRow($col+$offsetCol, 1, $tipo["TipoCalificacion_desc"]);
    $spreadsheet->getActiveSheet()->mergeCells(numberToLetter($col+$offsetCol).'1:'.numberToLetter($col+2+$offsetCol).'1');
    $col+=3;
}
$colMax = $col;

for($col = 0; $col < $colMax; $col++){
    $spreadsheet->setActiveSheetIndex($sheet)
                ->setCellValueByColumnAndRow($col+$offsetCol, 2, califTexto($col));
    //$spreadsheet->getActiveSheet()->getColumnDimension(numberToLetter($col+$offsetCol))->setAutoSize(true);
    $spreadsheet->getActiveSheet()->getDefaultColumnDimension()->setWidth(15);
}

$colLetter = numberToLetter($colMax+1);
$spreadsheet->getActiveSheet()->duplicateStyle($headerStyle, 'A1:'.$colLetter.'2');


$highestRow = count($planArr)+1;
$row = 3;
foreach($planArr as $plan){
    $col = 1;
    $spreadsheet->setActiveSheetIndex($sheet)
            ->setCellValueByColumnAndRow($col++, $row, $plan["desc"]);

    foreach($plan["califArr"] as $calif){
        $spreadsheet->setActiveSheetIndex($sheet)
            ->setCellValueByColumnAndRow($col++, $row, $calif["total"])
            ->setCellValueByColumnAndRow($col++, $row, $calif["reprobadas"])
            ->setCellValueByColumnAndRow($col++, $row, ($calif["total"]>0)? $calif["reprobadas"]/$calif["total"]:0);
        
        $colLetter = numberToLetter($col-1);
        $spreadsheet->setActiveSheetIndex($sheet)->getStyle($colLetter.$row.':'.$colLetter.$row)->getNumberFormat()
            ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_PERCENTAGE);
    }
    $row++;
}



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

//Auto ajustar anchos
$spreadsheet->setActiveSheetIndex($sheet)->getColumnDimension('A')->setAutoSize(true);

//-----------------
$sheet = 1;
$worksheet1 = $spreadsheet->createSheet();
$worksheet1->setTitle('Materias');

$spreadsheet->setActiveSheetIndex($sheet)
                ->setCellValueByColumnAndRow(1, 2, "Materia");

$offsetCol = 2;
$col = 0;
foreach($matArr as $tipo){
    $spreadsheet->setActiveSheetIndex($sheet)
                ->setCellValueByColumnAndRow($col+$offsetCol, 1, $tipo["desc"]);
    $spreadsheet->getActiveSheet()->mergeCells(numberToLetter($col+$offsetCol).'1:'.numberToLetter($col+2+$offsetCol).'1');
    $col+=3;
}
$colMax = $col;

for($col = 0; $col < $colMax; $col++){
    $spreadsheet->setActiveSheetIndex($sheet)
                ->setCellValueByColumnAndRow($col+$offsetCol, 2, califTexto($col));
    //$spreadsheet->getActiveSheet()->getColumnDimension(numberToLetter($col+$offsetCol))->setAutoSize(true);
    $spreadsheet->getActiveSheet()->getDefaultColumnDimension()->setWidth(15);
}

$colLetter = numberToLetter($colMax+1);
$highestRow = count($labelMateriasArr)+2;

$spreadsheet->getActiveSheet()->duplicateStyle($headerStyle, 'A1:'.$colLetter.'2');
$spreadsheet->getActiveSheet()->duplicateStyle($headerStyle, 'A3:A'.$highestRow);

$row = 3;
foreach($labelMateriasArr as $mat){
    $spreadsheet->setActiveSheetIndex($sheet)
                ->setCellValueByColumnAndRow(1, $row++, $mat);
}

$colStart = 2;
$col = 2;

foreach($matArr as $mat){
    $row = 3;
    
    foreach($mat["califArr"] as $calif){
        $col = $colStart;
        $spreadsheet->setActiveSheetIndex($sheet)
            ->setCellValueByColumnAndRow($col++, $row, $calif["total"])
            ->setCellValueByColumnAndRow($col++, $row, $calif["reprobadas"])
            ->setCellValueByColumnAndRow($col++, $row, ($calif["total"]>0)? $calif["reprobadas"]/$calif["total"]:0);
        
        $colLetter = numberToLetter($col-1);
        $spreadsheet->setActiveSheetIndex($sheet)->getStyle($colLetter.$row.':'.$colLetter.$row)->getNumberFormat()
            ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_PERCENTAGE);
        $row++;
    }
    $colStart+=3;
}


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

//Auto ajustar anchos
$spreadsheet->setActiveSheetIndex($sheet)->getColumnDimension('A')->setAutoSize(true);

//-----------------
$sheet = 2;
$worksheet1 = $spreadsheet->createSheet();
$worksheet1->setTitle('Grupos');

$spreadsheet->setActiveSheetIndex($sheet)
                ->setCellValueByColumnAndRow(1, 2, "Materia");
$offsetCol = 2;
$col = 0;
foreach($gpoArr as $gpo){
    $spreadsheet->setActiveSheetIndex($sheet)
                ->setCellValueByColumnAndRow($col+$offsetCol, 1, $gpo["desc"]);
    $spreadsheet->getActiveSheet()->mergeCells(numberToLetter($col+$offsetCol).'1:'.numberToLetter($col+2+$offsetCol).'1');
    $col+=3;
}
$colMax = $col;

for($col = 0; $col < $colMax; $col++){
    $spreadsheet->setActiveSheetIndex($sheet)
                ->setCellValueByColumnAndRow($col+$offsetCol, 2, califTexto($col));
    //$spreadsheet->getActiveSheet()->getColumnDimension(numberToLetter($col+$offsetCol))->setAutoSize(true);
    $spreadsheet->getActiveSheet()->getDefaultColumnDimension()->setWidth(15);
}

$colLetter = numberToLetter($colMax+1);
$highestRow = count($labelMateriasArr)+2;

$spreadsheet->getActiveSheet()->duplicateStyle($headerStyle, 'A1:'.$colLetter.'2');
$spreadsheet->getActiveSheet()->duplicateStyle($headerStyle, 'A3:A'.$highestRow);

$row = 3;
foreach($labelMateriasArr as $mat){
    $spreadsheet->setActiveSheetIndex($sheet)
                ->setCellValueByColumnAndRow(1, $row++, $mat);
}

$colStart = 2;
$col = 2;

foreach($gpoArr as $gpo){
    $row = 3;
    
    foreach($gpo["califArr"] as $calif){
        $col = $colStart;
        $spreadsheet->setActiveSheetIndex($sheet)
            ->setCellValueByColumnAndRow($col++, $row, $calif["total"])
            ->setCellValueByColumnAndRow($col++, $row, $calif["reprobadas"])
            ->setCellValueByColumnAndRow($col++, $row, ($calif["total"]>0)? $calif["reprobadas"]/$calif["total"]:0);
        
        $colLetter = numberToLetter($col-1);
        $spreadsheet->setActiveSheetIndex($sheet)->getStyle($colLetter.$row.':'.$colLetter.$row)->getNumberFormat()
            ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_PERCENTAGE);
        
        $row++;
    }
    $colStart+=3;
}

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

//Auto ajustar anchos
$spreadsheet->setActiveSheetIndex($sheet)->getColumnDimension('A')->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="noAprobados_'.$nom.'_'.$filter_sem.'.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;