sgi/apsa/export/xls_asistenciasprofesor_all.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;
use PhpOffice\PhpSpreadsheet\Shared\Date;


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

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

unset($objValida);

if(!isset($_POST["fecha_inicial"]) || !isset($_POST["fecha_final"]) || !isset($_POST["area"]) || !isset($_POST["desc"]) /*|| !isset($_POST["periodo"])*/){
    //header("Location: ".$pag."?error=0");
    echo "<h1>Error, no se recibieron los datos</h1>";
    exit();
}

//$filter_periodo = filter_input(INPUT_POST, "periodo", FILTER_SANITIZE_NUMBER_INT);//limpia texto
$filter_periodo = $_SESSION["periodo_id"];

//Periodo
$stmt = $pdo->prepare('Select * from fs_periodo(:id, NULL, NULL, NULL)');
$stmt->bindParam(":id", $filter_periodo);
if(!$stmt->execute()){
    $errorDesc = "Ocurrió un error al cargar el periodo";
}else{
    $periodo_rs = $stmt->fetch();
}
$stmt->closeCursor();

//Obtiene profesores

if(isset($_POST["fecha_inicial"]) && $_POST["fecha_inicial"]!=""){
    $filter_fini = trim(filter_input(INPUT_POST, "fecha_inicial", FILTER_SANITIZE_STRING,array('flags' => FILTER_FLAG_STRIP_LOW)));//limpia texto
}
if(isset($_POST["fecha_final"]) && $_POST["fecha_final"]!=""){
    $filter_ffin = trim(filter_input(INPUT_POST, "fecha_final", FILTER_SANITIZE_STRING,array('flags' => FILTER_FLAG_STRIP_LOW)));//limpia texto
}
if(isset($_POST["hora_inicial"]) && $_POST["hora_inicial"]!=""){
    $filter_hini = trim(filter_input(INPUT_POST, "hora_inicial", FILTER_SANITIZE_STRING,array('flags' => FILTER_FLAG_STRIP_LOW)));//limpia texto
}else{
    $filter_hini = '00:00';
}
if(isset($_POST["hora_final"]) && $_POST["hora_final"]!=""){
    $filter_hfin = trim(filter_input(INPUT_POST, "hora_final", FILTER_SANITIZE_STRING,array('flags' => FILTER_FLAG_STRIP_LOW)));//limpia texto
}else{
    $filter_hfin = '23:59';
}

$query = "";
if(isset($_POST["desc"]) && $_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,";
}
if(isset($_POST["clave"]) && $_POST["clave"] != ""){
    $query .= ":clave,";
    $filter_clave = filter_input(INPUT_POST, "clave", FILTER_SANITIZE_NUMBER_INT);//limpia texto
}else{
    $query .= "NULL,";
}
if(isset($_POST["area"]) && is_numeric($_POST["area"]) && trim($_POST["area"]) != ""){
    $query .= ":area,";
    $filter_area = filter_input(INPUT_POST, "area", FILTER_SANITIZE_NUMBER_INT);//limpia texto
}else{
    $query .= "NULL,";
}

$hoy_fecha = date("Y-m-d");

if(isset($filter_fini)) $fecha_ini = fechaGuion($filter_fini); else $fecha_ini = $periodo_rs["Periodo_fecha_inicial"];
if(isset($filter_ffin)){
    $fecha_fin = fechaGuion($filter_ffin);
}else{
    if($periodo_rs["Periodo_fecha_final"] < $hoy_fecha){
        $fecha_fin = $periodo_rs["Periodo_fecha_final"];
    }else{
        $fecha_fin = $hoy_fecha;
    }

}

$stmt = $pdo->prepare('Select * from fs_asistenciaprofesores_all(:per, :fini, :ffin, :hini, :hfin, '.$query.' 0, NULL)');//sólo activos
$stmt->bindParam(":per", $filter_periodo);
$stmt->bindParam(":fini", $fecha_ini);
$stmt->bindParam(":ffin", $fecha_fin);
$stmt->bindParam(":hini", $filter_hini);
$stmt->bindParam(":hfin", $filter_hfin);
if(isset($filter_desc)) $stmt->bindParam(":desc", $filter_desc);
if(isset($filter_clave)) $stmt->bindParam(":clave", $filter_clave);
if(isset($filter_area)) $stmt->bindParam(":area", $filter_area);
if(!$stmt->execute()){
    $errorDesc = "Ocurrió un error al cargar los profesores";
    print_r($stmt->errorInfo());
}else{
    $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('Asistencias profesores')
    ->setDescription('Reporte de asistencia de profesores.');


// Rename worksheet
$spreadsheet->getActiveSheet()->setTitle('Asistencias');


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

$colorRojo = 'ffce0e2d';
// Add some data
$spreadsheet->setActiveSheetIndex(0)
    ->setCellValue('A1', 'Profesor')
    ->setCellValue('B1', 'Clases total')
    ->setCellValue('C1', 'Asistencias')
    ->setCellValue('D1', 'Reposiciones')
    ->setCellValue('E1', 'Faltas')
    ->setCellValue('F1', 'Retardos')
    ->setCellValue('G1', '% Asistencia')
    ->setCellValue('H1', '% Retardos');

$spreadsheet->getActiveSheet()->duplicateStyle($headerStyle, 'A1:H1');

$highestRow = count($profesores_rs)+1;

//Centrar números
$spreadsheet->getActiveSheet()->getStyle('B2:F'.$highestRow)->getAlignment()->setHorizontal('center');
$spreadsheet->getActiveSheet()->getStyle('E2:E'.$highestRow)->getFont()->getColor()->setARGB($colorRojo);

$spreadsheet->getActiveSheet()->getStyle('G2:H'.($highestRow))
    ->getNumberFormat() 
    ->setFormatCode( 
    \PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_PERCENTAGE_00
    );

$row = 2;
foreach($profesores_rs as $profesor){
    $col = 1;
    
    $faltas = $profesor["Faltas"];
    $porcentaje_asistencia = 0;
    $porcentaje_retardo = 0;
    if( ($profesor["Asistencias"] == "" && $profesor["Reposiciones"] == "") || ($profesor["Asistencias"] + $profesor["Reposiciones"]) == 0){
        $faltas = $profesor["Clases_total"];
    }else if($profesor["Clases_total"] > 0){
        $porcentaje_asistencia = (($profesor["Asistencias"] + $profesor["Reposiciones"])/$profesor["Clases_total"]);
        $porcentaje_retardo = ($profesor["Retardos"]/($profesor["Asistencias"] + $profesor["Reposiciones"]));
    }
    
    
    $spreadsheet->setActiveSheetIndex(0)
        ->setCellValueByColumnAndRow($col++, $row, $profesor["Usuario_apellidos"]." ".$profesor["Usuario_nombre"])
        ->setCellValueByColumnAndRow($col++, $row, $profesor["Clases_total"])
        ->setCellValueByColumnAndRow($col++, $row, $profesor["Asistencias"])
        ->setCellValueByColumnAndRow($col++, $row, $profesor["Reposiciones"])
        ->setCellValueByColumnAndRow($col++, $row, $faltas)
        ->setCellValueByColumnAndRow($col++, $row, $profesor["Retardos"])
        ->setCellValueByColumnAndRow($col++, $row, $porcentaje_asistencia)
        ->setCellValueByColumnAndRow($col++, $row, $porcentaje_retardo);

    if($porcentaje_asistencia == 0){
        $spreadsheet->getActiveSheet()->getStyle('G'.$row)
            ->getFont()->getColor()->setARGB($colorRojo);
    }
    $row++;
}



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

//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);
$spreadsheet->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('G')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('H')->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="asistenciasTotal_'.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;