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

$pag = "../reporte_materiasgeneracion.php";

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

unset($objValida);

if(!isset($_POST["plan"], $_POST["generacion"], $_POST["nivel"], $_POST["revalida"])){
    header("Location: ".$pag."?error=0");
    exit();
}
$nivel_id = filter_input(INPUT_POST, "nivel", FILTER_SANITIZE_NUMBER_INT);//limpia texto
$filter_plan = filter_input(INPUT_POST, "plan", FILTER_SANITIZE_NUMBER_INT);//limpia texto
$plan_text = trim(filter_input(INPUT_POST, "plan_text", FILTER_SANITIZE_STRING,array('flags' => FILTER_FLAG_STRIP_LOW)));
$prefijo = trim(filter_input(INPUT_POST, "prefijo", FILTER_SANITIZE_STRING,array('flags' => FILTER_FLAG_STRIP_LOW)));
$filter_generacion = fechaGuion(trim(filter_input(INPUT_POST, "generacion", FILTER_SANITIZE_STRING,array('flags' => FILTER_FLAG_STRIP_LOW))));//limpia texto
$filter_revalida = filter_input(INPUT_POST, "revalida", FILTER_SANITIZE_NUMBER_INT);//limpia texto

//Obtiene horario del salon
if($filter_revalida == 1)
    $stmt = $pdo->prepare('Select * from fs_materias_reprobadaspromedio(:plan, :gen, :nivel, true)');
else
    $stmt = $pdo->prepare('Select * from fs_materias_reprobadaspromedio(:plan, :gen, :nivel, false)');
$stmt->bindParam(":plan", $filter_plan);
$stmt->bindParam(":gen", $filter_generacion);
$stmt->bindParam(":nivel", $nivel_id);
if(!$stmt->execute()){
     header("Location: ".$pag."?error=1");
     exit();
}else{
    $materias_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('Estado de Alumnos')
    ->setDescription('Estado actual de alumnos.');


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

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

$normalStyle = new Style();
$normalStyle->applyFromArray(
    [
        'font' => [
            /*'color' => ['argb' => 'FF001d68'],*/
            'size' => 10,
        ],
        'alignment' => [
            'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
        ],
    ]
);
$reprobadoStyle=
    [
        'font' => [
            'color' => ['argb' => 'FFd21034'],
            'size' => 10,
        ],
        'alignment' => [
            'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
        ],
    ];

//--------
$sheet = 0;
// Rename worksheet
$spreadsheet->getActiveSheet()->setTitle('Materias de la generación');

$row = 1;
$spreadsheet->setActiveSheetIndex($sheet)
                ->setCellValueByColumnAndRow(1, $row++, "Programa: ". $plan_text)
                ->setCellValueByColumnAndRow(1, $row++, "Generación: ". ucwords(fechaMonthPicker($filter_generacion)));

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

//--------
$row = 4;
$sem = -1;
if($nivel_id == 1)
    $nivel_desc = "Semestre";
else
    $nivel_desc = "Cuatrimestre";

foreach($materias_rs as $mat){
    if($sem != intval($mat["Materia_semestre"])){
        $sem = intval($mat["Materia_semestre"]);
        if($sem != -1){
            $spreadsheet->setActiveSheetIndex($sheet)
                    ->setCellValueByColumnAndRow(1, $row, $nivel_desc ." ". $sem)
                    ->setCellValueByColumnAndRow(2, $row, "Créditos")
                    ->setCellValueByColumnAndRow(3, $row, "Materias reprobadas")
                    ->setCellValueByColumnAndRow(4, $row, "Alumnos reprobados")
                    ->setCellValueByColumnAndRow(5, $row, "Total alumnos")
                    ->setCellValueByColumnAndRow(6, $row, "Reprobados porcentaje")
                    ->setCellValueByColumnAndRow(7, $row, "Promedio")
                    ->setCellValueByColumnAndRow(8, $row, "Menores al promedio")
                    ->setCellValueByColumnAndRow(9, $row, "Menores porcentaje")
                    ->setCellValueByColumnAndRow(10, $row, "Mayores iguales al promedio")
                    ->setCellValueByColumnAndRow(11, $row, "Mayores porcentaje");
            $spreadsheet->getActiveSheet()->duplicateStyle($headerStyle, "A".$row.":K".$row);
            $row++;
        }
    }
    
    $spreadsheet->setActiveSheetIndex($sheet)
                    ->setCellValueByColumnAndRow(1, $row, $mat["Materia_desc"])
                    ->setCellValueByColumnAndRow(2, $row, $mat["Materia_creditos"])
                    ->setCellValueByColumnAndRow(3, $row, $mat["Materias_reprobadas"])
                    ->setCellValueByColumnAndRow(4, $row, $mat["Alumnos_reprobados"])
                    ->setCellValueByColumnAndRow(5, $row, $mat["Alumnos_total"])
                    ->setCellValueByColumnAndRow(7, $row, $mat["Promedio"]/10)
                    ->setCellValueByColumnAndRow(8, $row, $mat["Menor_promedio"])
                    ->setCellValueByColumnAndRow(10, $row, $mat["Alumnos_total"] - $mat["Menor_promedio"]);
    
    $spreadsheet->getActiveSheet()->duplicateStyle($normalStyle, "A".$row.":K".$row);
    
    $spreadsheet->getActiveSheet()->getStyle(numberToLetter(2).$row)->getNumberFormat()
            ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_00 );
    
    if($mat["Alumnos_total"]>0){
        //$spreadsheet->getActiveSheet()->setCellValueByColumnAndRow(6, $row, number_format($mat["Alumnos_reprobados"]*100/$mat["Alumnos_total"],1)."%");
        $spreadsheet->getActiveSheet()->setCellValueByColumnAndRow(6, $row, "=".numberToLetter(4).$row."/".numberToLetter(5).$row);
        $spreadsheet->getActiveSheet()->getStyle(numberToLetter(6).$row)->getNumberFormat()
            ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_PERCENTAGE_00 );
        
        $spreadsheet->getActiveSheet()->setCellValueByColumnAndRow(9, $row, "=".numberToLetter(8).$row."/".numberToLetter(5).$row);
        $spreadsheet->getActiveSheet()->getStyle(numberToLetter(9).$row)->getNumberFormat()
            ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_PERCENTAGE_00 );
        
        $spreadsheet->getActiveSheet()->setCellValueByColumnAndRow(11, $row, "=".numberToLetter(10).$row."/".numberToLetter(5).$row);
        $spreadsheet->getActiveSheet()->getStyle(numberToLetter(11).$row)->getNumberFormat()
            ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_PERCENTAGE_00 );
    }
    
    
    if($mat["Materias_reprobadas"] > 0){
        $spreadsheet->getActiveSheet()->getStyle(numberToLetter(3).$row)->applyFromArray($reprobadoStyle);
    }
    if($mat["Alumnos_reprobados"] > 0){
        $spreadsheet->getActiveSheet()->getStyle(numberToLetter(4).$row)->applyFromArray($reprobadoStyle);
    }
    /*$spreadsheet->getActiveSheet()->getStyle(numberToLetter(6).$row)->getNumberFormat()
            ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_PERCENTAGE);*/
    $row++;
}



$spreadsheet->getActiveSheet()->getColumnDimension(numberToLetter(1))->setAutoSize(true);

//Auto ajustar anchos
/*for($col = 2; $col <= 7; $col++){
    //$spreadsheet->getActiveSheet()->getColumnDimension(numberToLetter($col))->setWidth(5);
    $spreadsheet->getActiveSheet()->getColumnDimension(numberToLetter($col))->setAutoSize(true);
}*/

//---------
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$spreadsheet->setActiveSheetIndex(0);

$name = str_replace(", ", "", fechaMonthPicker($filter_generacion));

// Redirect output to a client’s web browser (Xlsx)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="materias_'.$prefijo.$name.'.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;