sgi/apsa/export/xls_asistenciasprofesor.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(empty($_POST["fecha_inicial"]) || empty($_POST["fecha_final"]) || !isset($_POST["hora_inicial"]) || !isset($_POST["hora_final"]) || !isset($_POST["clave"])){
    //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

$filter_fini = trim(filter_input(INPUT_POST, "fecha_inicial", FILTER_SANITIZE_STRING,array('flags' => FILTER_FLAG_STRIP_LOW)));//limpia texto
$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 = "";
$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(: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());
    exit();
}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'],
        ]
    ]
);


// Add some data
$spreadsheet->setActiveSheetIndex(0)
    ->setCellValue('A1', 'Profesor')
    ->setCellValue('B1', 'Grupo')
    ->setCellValue('C1', 'Materia')
    ->setCellValue('D1', 'Fecha')
    ->setCellValue('E1', 'Hora de entrada')
    ->setCellValue('F1', 'Hora checador')
    ->setCellValue('G1', 'Estatus')
    ->setCellValue('H1', 'Área');

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

$highestRow = count($profesores_rs)+1;
//formato de fecha
$spreadsheet->getActiveSheet()->getStyle('D2:D'.($highestRow))
    ->getNumberFormat() 
    ->setFormatCode( 
    \PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_DDMMYYYY
    );

//Centrar fecha y horas
$spreadsheet->getActiveSheet()->getStyle('D2:F'.$highestRow)->getAlignment()->setHorizontal('center');


$row = 2;
foreach($profesores_rs as $profesor){
    $col = 1;
    
    $checador = "";
    if(isset($profesor["Asistencia_checador_inicial"]) && $profesor["Asistencia_checador_inicial"] != "")
        $checador = date('H:i', strtotime($profesor["Asistencia_checador_inicial"]));
    
    $colorStatus = '';
    if(isset($profesor["Asistencia_isRetardo"]) && $profesor["Asistencia_isRetardo"] !== ""){
        if($profesor["Asistencia_isRetardo"]){
            $status = "Retardo";
            $colorStatus = 'FFFFCC00';
        }else{
            if($profesor["Asistencia_isJustificada"]){
                $status = "Justificada";
                $colorStatus = 'FF001d68';
            }else{
                $status = "Asistencia";
                $colorStatus = 'FF339933';
            }
        }
    }else{
        $status = "Sin registro";
        $colorStatus = 'ffce0e2d';
    }
    $reposicion = '';
    if($profesor["Asistencia_isReposicion"]) $reposicion = ' (Reposición)';
    
    $spreadsheet->setActiveSheetIndex(0)
        ->setCellValueByColumnAndRow($col++, $row, $profesor["Usuario_apellidos"]." ".$profesor["Usuario_nombre"])
        ->setCellValueByColumnAndRow($col++, $row, $profesor["Grupo_desc"]." ".$profesor["Carrera_prefijo"])
        ->setCellValueByColumnAndRow($col++, $row, $profesor["Materia_desc"])
        ->setCellValueByColumnAndRow($col++, $row, Date::PHPToExcel(fechaSlash($profesor["Fecha"])))
        ->setCellValueByColumnAndRow($col++, $row, date('H:i', strtotime($profesor["Horario_hora"])))
        ->setCellValueByColumnAndRow($col++, $row, $checador)
        ->setCellValueByColumnAndRow($col++, $row, $status.$reposicion)
        ->setCellValueByColumnAndRow($col++, $row, $profesor["Area_desc"]);
    
    $spreadsheet->getActiveSheet()->getStyle('G'.$row)
        ->getFont()->getColor()->setARGB($colorStatus);
    
    
        //\PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex(1)
    $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="asistencias_'.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;