sgi/datos_usuarios_export.php

<?php
    require_once("./include/nocache.php");
    require_once("./include/constantes.php");
    require_once("./include/bd_pdo.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 = "../datos_usuarios.php";
    session_start();
    
    //--- Valida que el usuaro tenga permisos de superadministrador
    if(!isset($_SESSION) || !$_SESSION["sgi_administrador"]){
        header("Location: main.php");
        exit();
    }

    $session_life = 1*60*60;//convertido a segundos
    if (isset($_SESSION["timeout"])) {
    // calculate the session's "time to live"
        $sessionTTL = time() - $_SESSION["timeout"];
        if ($sessionTTL > $session_life) {
            $_SESSION = array();
            session_destroy();
            $pag = $_SERVER['SERVER_NAME']."/salir.php?expired=1";
            header("Location: http://".$pag);
            exit();
        }
    }else{
        $_SESSION = array();
        session_destroy();
        $pag = $_SERVER['SERVER_NAME']."/salir.php?expired=1";
        header("Location: http://".$pag);
        exit();
    }
    $_SESSION["timeout"] = time();
                
    //Obtiene datos de alumnos
    $stmt = $pdo->prepare('Select * from fs_checkusuario(1, 0, NULL)');
    if(!$stmt->execute()){
        $errorDesc = "Ocurrió un error al cargar los datos";
    }else
        $datos_rs = $stmt->fetchAll();
    $stmt->closeCursor();
    $stmt = null;

    //--------
    //
    // Create new Spreadsheet object
    $spreadsheet = new Spreadsheet();
    
    // Set document properties
    $spreadsheet->getProperties()->setCreator('Ingeniería La Salle')
        ->setLastModifiedBy('Ingeniería La Salle')
        ->setTitle('Cambio de datos')
        ->setDescription('Reporte de cambio de datos.');
    
    
    // Rename worksheet
    $spreadsheet->getActiveSheet()->setTitle('Datos');
    
    
    $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', 'Fecha')
        ->setCellValue('B1', 'Corregido')
        ->setCellValue('C1', 'Nombre anterior')
        ->setCellValue('D1', 'Nombre nuevo')
        ->setCellValue('E1', 'CURP anterior')
        ->setCellValue('F1', 'CURP nuevo')
        ->setCellValue('G1', 'Correo institucional')
        ->setCellValue('H1', 'Correo personal')
        ->setCellValue('I1', 'Celular')
        ->setCellValue('J1', 'Teléfono fijo');
    
    $spreadsheet->getActiveSheet()->duplicateStyle($headerStyle, 'A1:J1');
    $highestRow = count($datos_rs)+1;

    $row = 2;
    foreach($datos_rs as $datos){
        $col = 1;
        $spreadsheet->setActiveSheetIndex(0)
            ->setCellValueByColumnAndRow($col++, $row, $datos["CheckUsuario_fecharevision"])
            ->setCellValueByColumnAndRow($col++, $row, $datos["CheckUsuario_cambio"])
            ->setCellValueByColumnAndRow($col++, $row, $datos["Usuario_apellidos_old"]." ".$datos["Usuario_nombre_old"])
            ->setCellValueByColumnAndRow($col++, $row, $datos["Usuario_apellidos_new"]." ".$datos["Usuario_nombre_new"])
            ->setCellValueByColumnAndRow($col++, $row, $datos["Usuario_curp_old"])
            ->setCellValueByColumnAndRow($col++, $row, $datos["Usuario_curp_new"]);

        $datosCon =[
            "tel_fijo"=>"",
            "tel_cel"=>"",
            "correo_lasalle"=>"",
            "correo_pers"=>"",
        ];
        $stmt = $pdo->prepare('Select * from fs_checkusuario_contacto(:usr)');
        $stmt->bindParam(":usr", $datos["Usuario_id"]);
        if(!$stmt->execute()){
            $errorDesc = "Ocurrió un error al cargar los datos";
        }else{
            foreach($stmt->fetchAll() as $contacto){
                if($contacto["TipoContacto_id"] == 3){
                    if(strpos($contacto["CheckContacto_valor"], "lasall") && $datosCon["correo_lasalle"] ==""){
                        $datosCon["correo_lasalle"] = $contacto["CheckContacto_valor"];
                    }else{
                        $datosCon["correo_pers"] = $contacto["CheckContacto_valor"];
                    }
                }else{
                    if($contacto["SubtipoContacto_id"] == 1){
                        $datosCon["tel_fijo"] = $contacto["CheckContacto_valor"];
                    }else{
                        $datosCon["tel_cel"] = $contacto["CheckContacto_valor"];
                    }
                }
            }
        }
        $stmt->closeCursor();

        $spreadsheet->setActiveSheetIndex(0)
            ->setCellValueByColumnAndRow($col++, $row, $datosCon["correo_lasalle"])
            ->setCellValueByColumnAndRow($col++, $row, $datosCon["correo_pers"])
            ->setCellValueByColumnAndRow($col++, $row, $datosCon["tel_cel"])
            ->setCellValueByColumnAndRow($col++, $row, $datosCon["tel_fijo"]);
        $row++;
    }
        //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);
    $spreadsheet->getActiveSheet()->getColumnDimension('I')->setAutoSize(true);
    $spreadsheet->getActiveSheet()->getColumnDimension('J')->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="datosUsuarios_'.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;

 ?>