PlataformaEducativaWeb2/Back/backendP-Educativa/app/Http/Controllers/NivelExportController.php

<?php

namespace App\Http\Controllers;

use Illuminate\Support\Facades\Log;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Storage;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;

class NivelExportController extends Controller
{
    public function exportarExcel(Request $request)
    {
        $tabla = $request->input('tabla');
        $pagina = $request->input('pagina', 1);
        $pageSize = 5; // Ajusta si necesitas más registros por página
        $offset = ($pagina - 1) * $pageSize;

        switch ($tabla) {
            case 'niveles':
                return $this->generarReporte('niveles', 'NIVELES EDUCATIVOS', ['idNivel', 'nombreNivel', 'estado'], $offset, $pageSize);
            case 'materias_categorias':
                return $this->generarReporte('materias_categorias', 'CATEGORÍAS DE MATERIAS', ['categoria', 'ordenBoleta', 'estado'], $offset, $pageSize);
            case 'materias':
                return $this->generarReporte('materias', 'MATERIAS', ['idMateria','categoria','nombreMateria','idGradoGrupoRelacionado','afectaBoleta','materiaAcademica','mostrarMateria','estado'], $offset, $pageSize);
            case 'grados':
                return $this->generarReporte(
                    'vista_grados_niveles',
                    'GRADOS',
                    ['idGrado', 'nombreGrado', 'nombreNivel'],
                    $offset, $pageSize
                );
            case 'grupos':
                return $this->generarReporte('grupos', 'GRUPOS', ['idGrupo', 'nombreGrupo','Estado'], $offset, $pageSize);
            case 'usuarios':
                return $this->generarReporte('usuarios', 'USUARIOS', ['idUsuario', 'tipoUsuario', 'primerNombre','apellidoPaterno','grado','estatus'], $offset, $pageSize);
            case 'tipos_tareas':
                return $this->generarReporte('tareas_tipos', 'TIPOS DE TAREAS', ['idTareasTipos', 'nombreTareasTipos', 'envioPlatTareasTipos','estado'], $offset, $pageSize);
            case 'relacion_Alumno':
                return $this->generarReporte('vista_alumnos_padres_grupos', 'Relacion Alumno', ['idUsuario', 'nombreAlumno', 'grado','nombrePadre','nombreGrupo'], $offset, $pageSize);
            case 'profesor':
                return $this->generarReporte('vista_profesores_materias_grupos', 'Relacion Profesores', ['idUsuario', 'nombreProfesor', 'idMateria','gradoGrupo'], $offset, $pageSize);
            default:
                return response()->json(['error' => 'Tabla no válida'], 400);
        }
    }

    private function aclararColor(string $hexColor, float $factor = 0.7): string
    {
        $hexColor = ltrim($hexColor, '#');
        $r = hexdec(substr($hexColor, 0, 2));
        $g = hexdec(substr($hexColor, 2, 2));
        $b = hexdec(substr($hexColor, 4, 2));

        $r = min(255, (int)($r + (255 - $r) * $factor));
        $g = min(255, (int)($g + (255 - $g) * $factor));
        $b = min(255, (int)($b + (255 - $b) * $factor));

        return sprintf('%02X%02X%02X', $r, $g, $b);
    }

    private function generarReporte($tabla, $titulo, $headers, $offset, $pageSize)
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        $colorEncabezado = DB::table('personalizar')->value('PERPR_COL_BAR') ?: '4F81BD';
        $colorEncabezado = ltrim($colorEncabezado, '#');

        // Título
        $sheet->mergeCells('A1:P2');
        $sheet->setCellValue('A1', "REPORTE DE $titulo");
        $sheet->getStyle('A1:P2')->applyFromArray([
            'font' => ['bold' => true, 'size' => 18, 'name' => 'Century', 'color' => ['rgb' => 'FFFFFF']],
            'alignment' => ['horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                            'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER],
            'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => $colorEncabezado]],
            'borders' => ['bottom' => ['borderStyle' => Border::BORDER_THICK, 'color' => ['rgb' => 'FFFFFF']]],
        ]);
        $sheet->getRowDimension(1)->setRowHeight(70);

        // Logo
        $logoPathS3 = DB::table('personalizar')->value('PERLOGO');
        if ($logoPathS3 && Storage::disk('s3')->exists($logoPathS3)) {
            $tempLogoPath = tempnam(sys_get_temp_dir(), 'logo');
            file_put_contents($tempLogoPath, Storage::disk('s3')->get($logoPathS3));

            $logo = new Drawing();
            $logo->setName('Logo');
            $logo->setDescription('Logo de la empresa');
            $logo->setPath($tempLogoPath);
            $logo->setHeight(90);
            $logo->setCoordinates('A1');
            $logo->setOffsetX(10);
            $logo->setOffsetY(5);
            $logo->setWorksheet($sheet);
        }

        // Descripción
        $sheet->mergeCells('H3:J3');
        $sheet->setCellValue('H3', "Este reporte muestra los datos registrados en la tabla $tabla.");
        $sheet->getStyle('H3')->applyFromArray([
            'font' => ['italic' => true, 'size' => 10, 'name' => 'Century', 'color' => ['rgb' => '333333']],
            'alignment' => ['horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                            'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER],
        ]);

        // Encabezados
        $sheet->fromArray($headers, null, 'H4');
        $lastCol = chr(72 + count($headers) - 1);
        $sheet->getStyle("H4:$lastCol" . '4')->applyFromArray([
            'font' => ['bold' => true, 'color' => ['rgb' => 'FFFFFF'], 'size' => 12, 'name' => 'Century'],
            'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => $colorEncabezado]],
            'alignment' => ['horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                            'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER],
            'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN, 'color' => ['rgb' => 'FFFFFF']]],
        ]);

        // Datos con paginación
        $datos = DB::table($tabla)
            ->select($headers)
            ->skip($offset)
            ->take($pageSize)
            ->get();

        $row = 5;
        $colorClaro = $this->aclararColor($colorEncabezado, 0.85);

        foreach ($datos as $index => $registro) {
            $col = 'H';
            foreach ((array)$registro as $value) {
                $sheet->setCellValue($col . $row, $value);
                $col++;
            }

            $sheet->getRowDimension($row)->setRowHeight(25);
            $fillColor = $index % 2 === 0 ? $colorClaro : 'FFFFFF';

            $sheet->getStyle("H$row:$lastCol$row")->applyFromArray([
                'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => $fillColor]],
                'font' => ['name' => 'Calibri', 'size' => 11],
                'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN, 'color' => ['rgb' => 'BFBFBF']]],
            ]);
            $row++;
        }

        foreach (range('H', $lastCol) as $col) {
            $sheet->getColumnDimension($col)->setAutoSize(true);
        }

        $sheet->setAutoFilter("H4:$lastCol" . '4');
$writer = new Xlsx($spreadsheet);
$filename = "$titulo.xlsx";

return response()->streamDownload(function () use ($writer) {
    $writer->save('php://output');
}, $filename, [
    'Content-Type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
]);

    }
}