sgi/CIDIT/action/xls_reporte.php

<?php
setlocale(LC_TIME, 'es_MX.UTF-8');
require_once '../include/bd_pdo.php';
require_once '../classes/Concurso.php';
require_once '../classes/Etapa.php'; '';
require_once '../classes/Resultados.php';
require_once '../adds/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;
use PhpOffice\PhpSpreadsheet\Style\Alignment;

$files = glob('../temps/*'); // get all file names
foreach($files as $file){ // iterate files
  if(is_file($file)) {
    unlink($file); // delete file
  }
}

$spreadsheet;
if(!isset($_POST['id']) && !isset($_POST['etapa'])){ 
  $return['error'] = 'Primero debes elegir un concurso para consultar';  
} else {
    if (is_null($pdo)){
        $return['error'] = 'No fue posible obtener los datos para generar el archivo';
    } else {
        $concurso = Concurso::getCouncursoxId($pdo, $_POST['id']);
        if (count($concurso) > 0){
            $acciones = Proyecto::determinaAccionXEtapa($pdo, $_POST['etapa']);
            $accion = '';
            if(count($acciones) > 0)
                $accion = $acciones['accion'];
            $spreadsheet = new Spreadsheet();
            $spreadsheet->getProperties()->setCreator('Facultad de Ingeniería / La Salle')
                ->setLastModifiedBy('Facultad de Ingeniería / La Salle')
                ->setTitle($concurso['nom'] . ' - CIDIT')
                ->setDescription('Reporte la ' . $accion . ' de proyectos del '. $concurso['nom'] . ' CIDIT');
            $spreadsheet->getDefaultStyle()->getFont()->setName('Indivisa Text Sans');
            $listaProyectos = '';
            $proyectos = array();
            $tipoMarcas = ''; 
            switch (mb_strtoupper(Etapa::getClaveEtapaXID($pdo,$_POST['etapa']))){
                case 'REVISION': 
                    $proyectos = Proyecto::getProyectosAutorizados($pdo, $concurso['idconcurso'], 0, 'clave');
                    $temp = Proyecto::getProyectosFinalistas($pdo, $concurso['idconcurso'], 0, 'clave');
                    $marcar = array_column($temp,'idproyecto');
                    $tipoMarcas = 'FINALISTAS';
                    break;
                case 'EVALUACION': 
                    $proyectos = Proyecto::getProyectosFinalistas($pdo, $concurso['idconcurso'], 0, 'clave');
                    $temp = Proyecto::getProyectosGanadores($pdo, 0, $concurso['idconcurso']);
                    $marca = array();
                    foreach ($temp as $t){
                        $marca[$t['idproyecto']] = $t['lugar'];
                    }
                    $tipoMarcas = 'GANADORES';
                    $usuarios = Etapa::getUsuariosValoraciones($pdo, $concurso['idconcurso'], $_POST['etapa'], 'apellidos');
                    break;
            }            
            if (count($proyectos) > 0){
                $cont = 0;
                foreach ($proyectos as $proyecto) {
                    $banner = null;
                    switch ($tipoMarcas){
                        case 'FINALISTAS':
                            if (in_array($proyecto['idproyecto'], $marca))
                                $banner = array('texto' => 'Proyecto Finalista','estilo' => $tipoMarcas);
                            break;
                        case 'GANADORES':
                            if (isset($marca[$proyecto['idproyecto']])){
                                $texto = '';
                                $color = '';
                                switch ($marca[$proyecto['idproyecto']]){
                                    case 1: $texto = '1° Lugar'; $color = 'ORO'; break;
                                    case 2: $texto = '2° Lugar'; $color = 'PLATA'; break;
                                    case 3: $texto = '3° Lugar'; $color = 'BRONCE'; break;
                                }
                                $texto .= ' - ' . Proyecto::getCategoriaXID($pdo, $proyecto['categoria']);
                                $banner = array('texto' => $texto, 'estilo' =>$color);
                            }
                            break;
                    }
                    $tabla = Resultados::evaluacionesXProyecto($pdo, $proyecto['idproyecto'], $_POST['etapa'], 'ARRAY');
                    generaContenido($cont,$proyecto,$tabla, $banner);
                    $cont++;
                }
                if (isset($usuarios))
                    if (count($usuarios) > 0 )
                        generaParticipantes ($cont, $usuarios);
            }
            $sheetIndex = $spreadsheet->getIndex($spreadsheet->getSheetByName('Worksheet'));
            $spreadsheet->removeSheetByIndex($sheetIndex);    
            $spreadsheet->setActiveSheetIndex(0);
            
            $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
            $writer->save('../temps/' . $concurso['nom'] .'-CIDIT.xlsx');
            $return['ok'] = 'ok';
            $return['nombre'] = $concurso['nom'] .'-CIDIT';
            $return['file'] = 'temps/' . $concurso['nom'] .'-CIDIT.xlsx';
        }
    }
}
$return['json'] = json_encode($return);
echo json_encode($return);

function eligeEstilo($tipo, $indice = 0){    
    $estilo = new Style();
    switch (mb_strtoupper($tipo)){
        case 'TITULO':
        case 'TÍTULO':
            $estilo->applyFromArray([
                'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER,'vertical' => Alignment::VERTICAL_CENTER,],
                'borders' => ['bottom' => ['borderStyle' => Border::BORDER_MEDIUM],'color' => ['argb' => 'FFE0E0E0'],],
                'font' => ['size' => 18,'bold'  =>  true,'color' => ['argb' => 'FF001D68'],'name' => 'Indivisa Text Sans',]
            ]);
            break;
        case 'PERSONA':
            switch($indice) {
                case 1:
                    $estilo->applyFromArray([
                        'alignment' => ['vertical' => Alignment::VERTICAL_CENTER, 'horizontal' => Alignment::HORIZONTAL_LEFT,],
                        'borders' => ['right' => ['borderStyle' => Border::BORDER_THIN],'color' => ['argb' => 'FF898A8C'],],
                        'fill' => ['fillType' => Fill::FILL_SOLID,'color' => ['argb' => 'FFE0E0E0'],],
                        'font' => ['size' => 11,'name' => 'Indivisa Text Sans','color' => ['argb' => 'FF001D68'],],
                    ]);
                    break;
                case 2:
                    $estilo->applyFromArray([
                        'alignment' => ['vertical' => Alignment::VERTICAL_CENTER, 'horizontal' => Alignment::HORIZONTAL_LEFT,],
                        'borders' => ['right' => ['borderStyle' => Border::BORDER_THIN],'color' => ['argb' => 'FF898A8C'],],
                        'font' => ['size' => 11,'name' => 'Indivisa Text Sans','color' => ['argb' => 'FF001D68'],],
                    ]);
                    break;
            }
            break;
        case 'RENGLON':
            switch($indice) {
                case 1:
                    $estilo->applyFromArray([
                        'alignment' => ['vertical' => Alignment::VERTICAL_CENTER, 'horizontal' => Alignment::HORIZONTAL_CENTER,],
                        'fill' => ['fillType' => Fill::FILL_SOLID,'color' => ['argb' => 'FFE0E0E0'],],
                        'font' => ['size' => 11,'name' => 'Indivisa Text Sans','color' => ['argb' => 'FF001D68'],],
                    ]);
                    break;
                case 2:
                    $estilo->applyFromArray([
                        'alignment' => ['vertical' => Alignment::VERTICAL_CENTER, 'horizontal' => Alignment::HORIZONTAL_CENTER,],
                        'font' => ['size' => 11,'name' => 'Indivisa Text Sans','color' => ['argb' => 'FF001D68'],],
                    ]);
                    break;
            }
            break;
        case 'ESTRELLA':
        case 'ESTRELLAS':
            switch($indice) {
                case 1:
                    $estilo->applyFromArray([
                        'alignment' => ['vertical' => Alignment::VERTICAL_CENTER, 'horizontal' => Alignment::HORIZONTAL_RIGHT,],
                        'fill' => ['fillType' => Fill::FILL_SOLID,'color' => ['argb' => 'FFE0E0E0'],],
                        'font' => ['size' => 14,'name' => 'Indivisa Text Sans','color' => ['argb' => 'FFF9BF3B'],],
                    ]);
                    break;
                case 2:
                    $estilo->applyFromArray([
                        'alignment' => ['vertical' => Alignment::VERTICAL_CENTER, 'horizontal' => Alignment::HORIZONTAL_RIGHT,],
                        'font' => ['size' => 14,'name' => 'Indivisa Text Sans','color' => ['argb' => 'FFF9BF3B'],],
                    ]);
                    break;
            }
            break;
        case 'PROMEDIO':
            $estilo->applyFromArray([
                'fill' => ['fillType' => Fill::FILL_SOLID,'color' => ['argb' => 'FF001D68'],],
                'alignment' => ['vertical' => Alignment::VERTICAL_CENTER, 'horizontal' => Alignment::HORIZONTAL_CENTER,],
                'font' => ['size' => 11,'bold'  =>  true,'color' => ['argb' => 'FFFFFFFF'],'name' => 'Indivisa Text Sans',]
            ]);
            break;
        case 'RUBRO':
            $estilo->applyFromArray([
                'fill' => ['fillType' => Fill::FILL_SOLID,'color' => ['argb' => 'FF001D68'],],
                'alignment' => ['vertical' => Alignment::VERTICAL_CENTER, 'horizontal' => Alignment::HORIZONTAL_RIGHT,],
                'font' => ['size' => 11,'bold'  =>  true,'color' => ['argb' => 'FFFFFFFF'],'name' => 'Indivisa Text Sans',]
            ]);
            break;
        case 'AZUL':
            $azules = array('FFFFFF','007BC4','0091CF','009ED7','00A6CE','00ADDE','64BEE6','8AAAC8','7197BA','5285B2','137EA3','0C7696','2D549B','27489B');
            $estilo->applyFromArray([
                'fill' => ['fillType' => Fill::FILL_SOLID,'color' => ['argb' => 'FF' . $azules[$indice]],],
                'alignment' => ['vertical' => Alignment::VERTICAL_CENTER, 'horizontal' => Alignment::HORIZONTAL_CENTER,],
                'font' => ['size' => 11,'bold'  =>  true,'color' => ['argb' => 'FFFFFFFF'],'name' => 'Indivisa Text Sans',]
            ]);
            break;
        case 'ORO':
            $estilo->applyFromArray([
                'alignment' => ['vertical' => Alignment::VERTICAL_CENTER, 'horizontal' => Alignment::HORIZONTAL_CENTER,],
                'fill' => ['fillType' => Fill::FILL_SOLID,'color' => ['argb' => 'FFD4AF37'],],
                'font' => ['size' => 16,'name' => 'Indivisa Text Sans','bold'  =>  true,'color' => ['argb' => 'FFFFFFFF'],],
            ]);
            break;
        case 'PLATA':
            $estilo->applyFromArray([
                'alignment' => ['vertical' => Alignment::VERTICAL_CENTER, 'horizontal' => Alignment::HORIZONTAL_CENTER,],
                'fill' => ['fillType' => Fill::FILL_SOLID,'color' => ['argb' => 'FFB4B4B4'],],
                'font' => ['size' => 16,'bold'  =>  true,'name' => 'Indivisa Text Sans','color' => ['argb' => 'FFFFFFFF'],],
            ]);
            break;
        case 'BRONCE':
            $estilo->applyFromArray([
                'alignment' => ['vertical' => Alignment::VERTICAL_CENTER, 'horizontal' => Alignment::HORIZONTAL_CENTER,],
                'fill' => ['fillType' => Fill::FILL_SOLID,'color' => ['argb' => 'FFAD8A56'],],
                'font' => ['size' => 16,'bold'  =>  true,'name' => 'Indivisa Text Sans','color' => ['argb' => 'FFFFFFFF'],],
            ]);
            break;
        case 'FINALISTA':
            $estilo->applyFromArray([
                'alignment' => ['vertical' => Alignment::VERTICAL_CENTER, 'horizontal' => Alignment::HORIZONTAL_CENTER,],
                'fill' => ['fillType' => Fill::FILL_SOLID,'color' => ['argb' => 'FF339933'],],
                'font' => ['size' => 16,'bold'  =>  true,'name' => 'Indivisa Text Sans','color' => ['argb' => 'FFFFFFFF'],],
            ]);
            break;
    } 
    return $estilo;
}

function generaContenido($hoja,$proyecto,$tablaResultados, $banner = null){
    global $spreadsheet;
    $ajustables = array();
    $spreadsheet->setActiveSheetIndex($hoja);
    $spreadsheet->getActiveSheet()->setTitle($proyecto['clave']);    
    $renglon = 1;
    $columna = 'A';
    $maxColumn = 'A';
    if (!is_null($banner)){        
        array_push($ajustables,$renglon);
        $renglon++;
    }    
    array_push($ajustables,$renglon);
    $renglon += 2;
    $renglonInicial = $renglon;
    foreach ($tablaResultados as $resultados){
        foreach ($resultados as $linea){
            $columna = 'A';
            $cont = 0;
            foreach ($linea as $celda){
                if (is_numeric($celda['texto']))
                    $tipo = \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_NUMERIC;
                else{
                    $tipo = \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING;
                }
                $spreadsheet->getActiveSheet()->getCell($columna.$renglon)->setValueExplicit($celda['texto'],$tipo);
                $spreadsheet->getActiveSheet()->duplicateStyle(eligeEstilo($celda['estilo'],$celda['indice']), $columna.$renglon);
                $cont++;
                if ($cont < count($linea))
                    $columna++;
            }
            if ($columna > $maxColumn)
                $maxColumn = $columna;            
            $renglon++;
        }
        $renglon += 2;
    }
    $renglonFinal = $renglon - 2;            
    foreach ($ajustables as $ajusta){
        if (count($ajustables) == 2 && $ajusta == 1){
            $spreadsheet->getActiveSheet()->setCellValue('A'.$ajusta, $banner['texto']);
            $spreadsheet->getActiveSheet()->duplicateStyle(eligeEstilo($banner['estilo']), 'A'.$ajusta.':'.$maxColumn.$ajusta);
            $spreadsheet->getActiveSheet()->mergeCells('A'.$ajusta.':'.$maxColumn.$ajusta);
            $spreadsheet->getActiveSheet()->getRowDimension($ajusta)->setRowHeight(35);
        } else {
            $spreadsheet->getActiveSheet()->setCellValue('A'.$ajusta, $proyecto['proyecto']);
            $spreadsheet->getActiveSheet()->mergeCells('A'.$ajusta.':'.$maxColumn.$ajusta);
            $spreadsheet->getActiveSheet()->duplicateStyle(eligeEstilo('TITULO'), 'A'.$ajusta.':'.$maxColumn.$ajusta);
            $spreadsheet->getActiveSheet()->getRowDimension($ajusta)->setRowHeight(55);
        }
    }            
    $spreadsheet->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
    for ($ajusta = 'B'; $ajusta <= $maxColumn; $ajusta++){
        $spreadsheet->getActiveSheet()->getColumnDimension($ajusta)->setWidth(15);
        $spreadsheet->getActiveSheet()-> getStyle($ajusta.$renglonInicial.':'.$ajusta.$renglonFinal)->getAlignment()->setWrapText(true);
    }
    $renglon++;
    $spreadsheet->getActiveSheet()->getStyle('A1:G1')->getAlignment()->setWrapText(true);
    for ($ajusta = $renglonInicial; $ajusta <= $renglonFinal; $ajusta++){
         $spreadsheet->getActiveSheet()->getRowDimension($ajusta)->setRowHeight(-1);
    }
    $spreadsheet->getActiveSheet()->setShowGridlines(false);
    $spreadsheet->getActiveSheet()->setSelectedCell('A1');
    $spreadsheet->createSheet();
}

function generaParticipantes($hoja, $usuarios){
    global $spreadsheet;
    $spreadsheet->setActiveSheetIndex($hoja);
    $spreadsheet->getActiveSheet()->setTitle('Administrativos-Docentes');    
    $spreadsheet->getActiveSheet()->getCell('A1')->setValue("Administrativos y Docentes Participantes");
    $spreadsheet->getActiveSheet()->getStyle('A1')->getAlignment()->setWrapText(true);
    $spreadsheet->getActiveSheet()->duplicateStyle(eligeEstilo('TITULO'), 'A1');    
    $renglon = 2;
    $cont = 1;
    foreach ($usuarios as $usuario){
        $spreadsheet->getActiveSheet()->getCell('A'.$renglon)->setValueExplicit($usuario['apellidos'] . ' ' . $usuario['nombre'],\PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
        $spreadsheet->getActiveSheet()->duplicateStyle(eligeEstilo('RENGLON',$cont%3), 'A'.$renglon);
        $cont++;
        if ($cont % 3 == 0)
            $cont++;
        $renglon++;
    }       
    $spreadsheet->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);    
    for ($ajusta = 2; $ajusta <= $renglon; $ajusta++){
         $spreadsheet->getActiveSheet()->getRowDimension($ajusta)->setRowHeight(-1);
    }
    $spreadsheet->getActiveSheet()->setShowGridlines(false);
    $spreadsheet->getActiveSheet()->setSelectedCell('A1');
    $spreadsheet->createSheet();
}