<?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 = 7;
    $submenu = 74;

    //--- 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
    $query = "";
                
    if(isset($_POST["area"]) && is_numeric($_POST["area"]) && $_POST["area"]!=""){
        $query .= ":area";
        $filter_area = filter_input(INPUT_POST, "area", FILTER_SANITIZE_NUMBER_INT);//limpia texto
    }else{
        $query .= "NULL";
    }
    
    $stmt = $pdo->prepare('Select * from fs_reporte_syllabus_plan(:periodo, '.$query.')');
    $stmt->bindParam(":periodo", $_SESSION["periodo_id"]);
    if(isset($filter_area)) $stmt->bindParam(":area", $filter_area);
    
    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('Syllabus y plan de cátedra')
        ->setDescription('Syllabus y plan de cátedra');
    
    $row_base = 6;
    //crea imagen
    $drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
    $drawing->setName('La Salle');
    $drawing->setDescription('La Salle');
    $drawing->setPath('../../img/logopdf.png'); // put your path and image here
    $drawing->setCoordinates('A1');
    $drawing->setHeight(100);
    $drawing->setOffsetX(10);
    //agrega imagen
    $drawing->setWorksheet($spreadsheet->getActiveSheet());
    
    //crea imagen
    $drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
    $drawing->setName('Facultad de Ingenería');
    $drawing->setDescription('Facultad de Ingenería');
    $drawing->setPath('../../img/logoIngenieria.png'); // put your path and image here
    $drawing->setCoordinates('F1');
    $drawing->setHeight(100);
    $drawing->setOffsetX(-20);
    //agrega imagen
    $drawing->setWorksheet($spreadsheet->getActiveSheet());

    // Rename worksheet
    $spreadsheet->getActiveSheet()->setTitle('Materias');
    
    
    $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('A'.$row_base, 'Clave')
        ->setCellValue('B'.$row_base, 'Nombre')
        ->setCellValue('C'.$row_base, 'Materia')
        ->setCellValue('D'.$row_base, 'Grupo')
        ->setCellValue('E'.$row_base, 'Área')
        ->setCellValue('F'.$row_base, 'Syllabus')
        ->setCellValue('G'.$row_base, 'Plan de cátedra')
        ->setCellValue('H'.$row_base, 'Tipo');
    
    $spreadsheet->getActiveSheet()->duplicateStyle($headerStyle, 'A'.$row_base.':H'.$row_base);
    $spreadsheet->getActiveSheet()->setAutoFilter('A'.$row_base.':H'.$row_base);
    $highestRow = count($datos_rs)+1;

    $row = $row_base+1;
    foreach($datos_rs as $datos){
        $col = 1;
        $spreadsheet->getActiveSheet()
            ->setCellValueByColumnAndRow($col++, $row, $datos["Usuario_claveULSA"])
            ->setCellValueByColumnAndRow($col++, $row, $datos["Usuario_apellidos"]." ".$datos["Usuario_nombre"])
            ->setCellValueByColumnAndRow($col++, $row, $datos["Materia_desc"])
            ->setCellValueByColumnAndRow($col++, $row, $datos["Grupo_desc"].$datos["Carrera_prefijo"])
            ->setCellValueByColumnAndRow($col++, $row, $datos["Area_desc"])
            ->setCellValueByColumnAndRow($col++, $row, round($datos["total_campos_llenos"]/13*100,1)."%")
            ->setCellValueByColumnAndRow($col++, $row, round($datos["promedio_sesiones_semana"]*100,1)."%")
            ->setCellValueByColumnAndRow($col++, $row, ($datos["electiva"]?"Electva":"Materia"));
        $row++;

    }
    //Auto ajustar anchos
    foreach ($spreadsheet->getActiveSheet()->getColumnIterator() as $column) {
        $spreadsheet->getActiveSheet()->getColumnDimension($column->getColumnIndex())->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="reporte_syllabus_plan_'.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;

 ?>