<?php
    require_once("../../include/nocache.php");
    require_once("../../include/constantes.php");
    require_once("../../include/bd_pdo.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;

    
    $menu = 8;
    $submenu = 86;

    //--- Objeto para validar usuario. El id de usuario lo lee desde sesión
    $objSesion = new ValidaSesion($pdo, $submenu, APSA);
    if(!$objSesion->tieneAcceso()){
        $objSesion->terminaSesion();
    }
                
    //Obtiene datos de alumnos
    $stmt = $pdo->prepare('Select * from fs_checkusuario(1, 0, NULL)  AS cu INNER JOIN "UsuarioAPSA" u ON cu."Usuario_id" = u."Usuario_id"');
    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', 'Grado anterior')
        ->setCellValue('D1', 'Nombre anterior')
        ->setCellValue('E1', 'Grado nuevo')
        ->setCellValue('F1', 'Nombre nuevo')
        ->setCellValue('G1', 'CURP anterior')
        ->setCellValue('H1', 'CURP nuevo')
        ->setCellValue('I1', 'Correo institucional')
        ->setCellValue('J1', 'Correo personal')
        ->setCellValue('K1', 'Celular')
        ->setCellValue('L1', 'Teléfono fijo');
    
    $spreadsheet->getActiveSheet()->duplicateStyle($headerStyle, 'A1:L1');
    $highestRow = count($datos_rs)+1;

    $row = 2;
    foreach($datos_rs as $datos){
        $col = 1;
        $spreadsheet->getActiveSheet()
            ->setCellValueByColumnAndRow($col++, $row, date("d/m/Y H:i:s", strtotime($datos["CheckUsuario_fecha_revision"])))
            ->setCellValueByColumnAndRow($col++, $row, $datos["CheckUsuario_cambio"]?"SÍ":"NO")
            ->setCellValueByColumnAndRow($col++, $row, $datos["Usuario_grado_old"])
            ->setCellValueByColumnAndRow($col++, $row, $datos["Usuario_apellidos_old"]." ".$datos["Usuario_nombre_old"])
            ->setCellValueByColumnAndRow($col++, $row, $datos["Usuario_grado_new"])
            ->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"]);

        $spreadsheet->getActiveSheet()->getStyle('A'.$row)
            ->getNumberFormat()
            ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_DATETIME);

        $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->getActiveSheet()
            ->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
    foreach ($spreadsheet->getActiveSheet()->getColumnIterator() as $column) {
        $spreadsheet->getActiveSheet()->getColumnDimension($column->getColumnIndex())->setAutoSize(true);
    }

    
    $spreadsheet->getActiveSheet()->setAutoFilter($spreadsheet->getActiveSheet()->calculateWorksheetDimension());

    // 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;

 ?>