Tul xxx Tul

Viewing: ClientesController.php

<?php
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Fill;
class ClientesController
{
    public function index()
    {
        // Buscar clientes (todos los estados) por nombre, código o número de medidor
        $q = trim((string)($_GET['q'] ?? ''));
        $customers = Customer::searchAll($q);
        // CSRF token para formularios inline (eliminar)
        if (empty($_SESSION['csrf'])) {
            $_SESSION['csrf'] = bin2hex(random_bytes(32));
        }
        $csrf = $_SESSION['csrf'];
        require __DIR__ . '/../views/clientes/index.php';
    }

    public function show()
    {
        $id = (int)($_GET['id'] ?? 0);
        if ($id <= 0) {
            http_response_code(400);
            echo 'ID inválido';
            return;
        }

        // Restringir a CLIENTE sólo su propio registro
        $role = getCurrentUserRole();
        if ($role === 'CLIENTE') {
            $cid = (int)($_SESSION['user']['customer_id'] ?? 0);
            if ($cid <= 0 || $cid !== $id) {
                http_response_code(403);
                echo '403 Prohibido';
                return;
            }
        }

        $pdo = (new Database())->getConnection();
        $stmt = $pdo->prepare('SELECT * FROM customers WHERE id = :id LIMIT 1');
        $stmt->execute([':id' => $id]);
        $customer = $stmt->fetch();
        if (!$customer) {
            http_response_code(404);
            echo 'Cliente no encontrado';
            return;
        }

        // Cargar nombre de tarifa asignada (si existe) o inferir desde la última factura
        $tariffName = null;
        $tariffId = $customer['tariff_id'] ?? null;
        if (!empty($tariffId)) {
            $tariff = Tariff::findById((int)$tariffId);
            if ($tariff) { $tariffName = (string)($tariff['name'] ?? ''); }
        }
        if ($tariffName === null) {
            // Inferir desde la última factura emitida con tarifa asignada
            $stmtT = $pdo->prepare("SELECT t.name
                                    FROM invoices i
                                    JOIN tariffs t ON t.id = i.tariff_id
                                    WHERE i.customer_id = :cid AND i.tariff_id IS NOT NULL
                                    ORDER BY i.issue_date DESC, i.id DESC
                                    LIMIT 1");
            $stmtT->execute([':cid' => $id]);
            $tariffName = $stmtT->fetchColumn() ?: null;
        }

        // CSRF token para acciones inline
        if (empty($_SESSION['csrf'])) {
            $_SESSION['csrf'] = bin2hex(random_bytes(32));
        }
        $csrf = $_SESSION['csrf'];

        $meters = Meter::getByCustomer($id);
        $openInvoices = Payment::getOpenInvoicesByCustomer($id);
        $customerReports = CustomerReport::getByCustomer($id);
        // Historial de contratos
        $contracts = Contract::getByCustomer($id);
        $pageTitle = 'Cliente: ' . ($customer['name'] ?? '');

        require __DIR__ . '/../views/clientes/show.php';
    }

    public function create()
    {
        // CSRF token
        if (empty($_SESSION['csrf'])) {
            $_SESSION['csrf'] = bin2hex(random_bytes(32));
        }
        $csrf = $_SESSION['csrf'];

        // Enums desde la base de datos (hardcodeadas para el formulario)
        $docTypes = ['CI','NIT','PASAPORTE'];
        $customerStatuses = ['Activo','Suspendido','Cancelado'];
        $meterTypes = ['Micro','Macro'];
        $meterStatuses = ['Operativo','Averiado','Retirado'];

        // Generar código de cliente sugerido y único para el formulario
        $newCode = Customer::getNextCustomerCode();

        // Listado de tarifas disponibles para seleccionar (mostrar solo Activas por claridad)
        $tariffs = Tariff::getAll(['status' => 'Activa']);

        require __DIR__ . '/../views/clientes/create.php';
    }

    public function store()
    {
        if ($_SERVER['REQUEST_METHOD'] !== 'POST') { header('Location: ' . BASE_URL . '?route=clientes.index'); exit; }

        // CSRF
        $csrf = $_POST['csrf'] ?? '';
        if (!hash_equals($_SESSION['csrf'] ?? '', $csrf)) {
            $_SESSION['flash_error'] = 'Sesión inválida. Intenta nuevamente.';
            header('Location: ' . BASE_URL . '?route=clientes.create');
            exit;
        }

        // Datos cliente
        // Generar SIEMPRE en el servidor para evitar manipulaciones del formulario
        $customer_code = Customer::getNextCustomerCode();
        // Salvaguarda adicional: si llegase a existir por concurrencia, genera otro
        $guard = 0;
        while (Customer::codeExists($customer_code) && $guard < 3) {
            $customer_code = Customer::getNextCustomerCode();
            $guard++;
        }
        $document_type = (string)($_POST['document_type'] ?? 'CI');
        $document_number = trim((string)($_POST['document_number'] ?? ''));
        $name = trim((string)($_POST['name'] ?? ''));
        $address = trim((string)($_POST['address'] ?? ''));
        $sector = trim((string)($_POST['sector'] ?? ''));
        $phone = trim((string)($_POST['phone'] ?? ''));
        $email = trim((string)($_POST['email'] ?? ''));
        $members_count = (int)($_POST['members_count'] ?? 1);
        $connection_date = trim((string)($_POST['connection_date'] ?? ''));
        $status = (string)($_POST['status'] ?? 'Activo');
        $tariff_id = isset($_POST['tariff_id']) && $_POST['tariff_id'] !== '' ? (int)$_POST['tariff_id'] : null;

        // Datos medidor
        $meter_id = isset($_POST['meter_id']) && $_POST['meter_id'] !== '' ? (int)$_POST['meter_id'] : null;
        $meter_number = trim((string)($_POST['meter_number'] ?? ''));
        $meter_type = (string)($_POST['meter_type'] ?? 'Micro');
        $install_date = trim((string)($_POST['install_date'] ?? ''));
        $location = trim((string)($_POST['location'] ?? ''));
        $meter_status = (string)($_POST['meter_status'] ?? 'Operativo');
        $initial_reading = (float)($_POST['initial_reading'] ?? 0);

        // Validaciones mínimas
        $errors = [];
        if ($customer_code === '') { $errors[] = 'El código de cliente es obligatorio.'; }
        if ($name === '') { $errors[] = 'El nombre es obligatorio.'; }
        if ($email !== '' && !filter_var($email, FILTER_VALIDATE_EMAIL)) { $errors[] = 'El correo electrónico no es válido.'; }
        if ($members_count <= 0) { $members_count = 1; }
        if ($meter_number === '') { $errors[] = 'El número de medidor es obligatorio.'; }
        // Estado: validar opciones válidas y prohibir 'Cortado'
        $allowedCustomerStatuses = ['Activo','Suspendido','Cancelado'];
        if ($status === 'Cortado') { $errors[] = 'El estado "Cortado" ha sido retirado. Usa "Suspendido".'; }
        if (!in_array($status, $allowedCustomerStatuses, true)) { $errors[] = 'Estado de cliente inválido.'; }

        if (!empty($errors)) {
            $_SESSION['flash_error'] = implode("\n", $errors);
            header('Location: ' . BASE_URL . '?route=clientes.create');
            exit;
        }

        try {
            $pdo = (new Database())->getConnection();
            if (!$pdo) { throw new RuntimeException('Sin conexión a la base de datos'); }

            $pdo->beginTransaction();

            // Insertar cliente
            $sqlC = "INSERT INTO customers (customer_code, document_type, document_number, name, address, sector, phone, email, members_count, connection_date, status, tariff_id)
                     VALUES (:customer_code, :document_type, :document_number, :name, :address, :sector, :phone, :email, :members_count, :connection_date, :status, :tariff_id)";
            $stmtC = $pdo->prepare($sqlC);
            $stmtC->execute([
                ':customer_code' => $customer_code,
                ':document_type' => $document_type,
                ':document_number' => $document_number !== '' ? $document_number : null,
                ':name' => $name,
                ':address' => $address !== '' ? $address : null,
                ':sector' => $sector !== '' ? $sector : null,
                ':phone' => $phone !== '' ? $phone : null,
                ':email' => $email !== '' ? $email : null,
                ':members_count' => $members_count,
                ':connection_date' => $connection_date !== '' ? $connection_date : null,
                ':status' => $status,
                ':tariff_id' => $tariff_id,
            ]);

            $customerId = (int)$pdo->lastInsertId();

            // Insertar medidor
            $sqlM = "INSERT INTO meters (number, customer_id, type, install_date, location, status, initial_reading)
                     VALUES (:number, :customer_id, :type, :install_date, :location, :status, :initial_reading)";
            $stmtM = $pdo->prepare($sqlM);
            $stmtM->execute([
                ':number' => $meter_number,
                ':customer_id' => $customerId,
                ':type' => $meter_type,
                ':install_date' => $install_date !== '' ? $install_date : null,
                ':location' => $location !== '' ? $location : null,
                ':status' => $meter_status,
                ':initial_reading' => $initial_reading,
            ]);

            $pdo->commit();

            $_SESSION['flash_success'] = 'Cliente creado correctamente. Completa el contrato.';
            header('Location: ' . BASE_URL . '?route=contratos.create&customer_id=' . $customerId);
            exit;
        } catch (Throwable $e) {
            if (isset($pdo) && $pdo && $pdo->inTransaction()) { $pdo->rollBack(); }
            // Duplicado de código o medidor
            $msg = $e->getMessage();
            if (stripos($msg, 'Duplicate') !== false || stripos($msg, 'UNIQUE') !== false) {
                $_SESSION['flash_error'] = 'Código de cliente o número de medidor ya existen.';
            } else {
                $_SESSION['flash_error'] = 'No se pudo crear el cliente.';
            }
            header('Location: ' . BASE_URL . '?route=clientes.create');
            exit;
        }
    }

    public function edit()
    {
        // CSRF token
        if (empty($_SESSION['csrf'])) {
            $_SESSION['csrf'] = bin2hex(random_bytes(32));
        }
        $csrf = $_SESSION['csrf'];

        $id = (int)($_GET['id'] ?? 0);
        if ($id <= 0) { http_response_code(400); echo 'ID inválido'; return; }

        $customer = Customer::findById($id);
        if (!$customer) { http_response_code(404); echo 'Cliente no encontrado'; return; }

        $meters = Meter::getByCustomer($id);
        // Cargar detalles completos del primer medidor (si existe)
        $meter = null;
        if (!empty($meters)) {
            $firstMeterId = (int)($meters[0]['id'] ?? 0);
            if ($firstMeterId > 0) {
                $meter = Meter::findById($firstMeterId);
            }
        }
        // Normalizar estado legado 'Cortado' a 'Suspendido' para el formulario
        if (($customer['status'] ?? '') === 'Cortado') {
            $customer['status'] = 'Suspendido';
        }

        // Enums para selects
        $docTypes = ['CI','NIT','PASAPORTE'];
        $customerStatuses = ['Activo','Suspendido','Cancelado'];
        $meterTypes = ['Micro','Macro'];
        $meterStatuses = ['Operativo','Averiado','Retirado'];

        // Listado de tarifas para selector
        $tariffs = Tariff::getAll(['status' => 'Activa']);

        require __DIR__ . '/../views/clientes/edit.php';
    }

    public function update()
    {
        if ($_SERVER['REQUEST_METHOD'] !== 'POST') { header('Location: ' . BASE_URL . '?route=clientes.index'); exit; }

        // CSRF
        $csrf = $_POST['csrf'] ?? '';
        if (!hash_equals($_SESSION['csrf'] ?? '', $csrf)) {
            $_SESSION['flash_error'] = 'Sesión inválida. Intenta nuevamente.';
            header('Location: ' . BASE_URL . '?route=clientes.index');
            exit;
        }

        $id = (int)($_POST['id'] ?? 0);
        if ($id <= 0) { $_SESSION['flash_error'] = 'ID inválido.'; header('Location: ' . BASE_URL . '?route=clientes.index'); exit; }

        // Datos cliente
        $existing = Customer::findById($id);
        if (!$existing) { $_SESSION['flash_error'] = 'Cliente no encontrado.'; header('Location: ' . BASE_URL . '?route=clientes.index'); exit; }
        $customer_code = (string)($existing['customer_code'] ?? '');
        $document_type = (string)($_POST['document_type'] ?? 'CI');
        $document_number = trim((string)($_POST['document_number'] ?? ''));
        $name = trim((string)($_POST['name'] ?? ''));
        $address = trim((string)($_POST['address'] ?? ''));
        $sector = trim((string)($_POST['sector'] ?? ''));
        $phone = trim((string)($_POST['phone'] ?? ''));
        $email = trim((string)($_POST['email'] ?? ''));
        $members_count = (int)($_POST['members_count'] ?? 1);
        $connection_date = trim((string)($_POST['connection_date'] ?? ''));
        $status = (string)($_POST['status'] ?? 'Activo');
        $tariff_id = isset($_POST['tariff_id']) && $_POST['tariff_id'] !== '' ? (int)$_POST['tariff_id'] : null;

        // Datos medidor
        $meter_id = isset($_POST['meter_id']) && $_POST['meter_id'] !== '' ? (int)$_POST['meter_id'] : null;
        $meter_number = trim((string)($_POST['meter_number'] ?? ''));
        $meter_type = (string)($_POST['meter_type'] ?? 'Micro');
        $install_date = trim((string)($_POST['install_date'] ?? ''));
        $location = trim((string)($_POST['location'] ?? ''));
        $meter_status = (string)($_POST['meter_status'] ?? 'Operativo');
        $initial_reading = (float)($_POST['initial_reading'] ?? 0);

        // Validaciones mínimas
        $errors = [];
        if ($customer_code === '') { $errors[] = 'El código de cliente es obligatorio.'; }
        if ($name === '') { $errors[] = 'El nombre es obligatorio.'; }
        if ($email !== '' && !filter_var($email, FILTER_VALIDATE_EMAIL)) { $errors[] = 'El correo electrónico no es válido.'; }
        if ($members_count <= 0) { $members_count = 1; }
        if ($meter_id !== null && $meter_number === '') { $errors[] = 'El número de medidor es obligatorio.'; }
        // Estado: validar opciones válidas y prohibir 'Cortado'
        $allowedCustomerStatuses = ['Activo','Suspendido','Cancelado'];
        if ($status === 'Cortado') { $errors[] = 'El estado "Cortado" ha sido retirado. Usa "Suspendido".'; }
        if (!in_array($status, $allowedCustomerStatuses, true)) { $errors[] = 'Estado de cliente inválido.'; }

        if (!empty($errors)) {
            $_SESSION['flash_error'] = implode("\n", $errors);
            header('Location: ' . BASE_URL . '?route=clientes.edit&id=' . $id);
            exit;
        }

        try {
            $pdo = (new Database())->getConnection();
            if (!$pdo) { throw new RuntimeException('Sin conexión a la base de datos'); }
            $pdo->beginTransaction();

            // Actualizar cliente
            Customer::update($id, [
                'customer_code' => $customer_code,
                'document_type' => $document_type,
                'document_number' => $document_number,
                'name' => $name,
                'address' => $address,
                'sector' => $sector,
                'phone' => $phone,
                'email' => $email,
                'members_count' => $members_count,
                'connection_date' => $connection_date,
                'tariff_id' => $tariff_id,
                'status' => $status,
            ]);

            // Actualizar medidor si corresponde
            if ($meter_id !== null && $meter_id > 0) {
                Meter::update($meter_id, [
                    'number' => $meter_number,
                    'type' => $meter_type,
                    'install_date' => $install_date,
                    'location' => $location,
                    'status' => $meter_status,
                    'initial_reading' => $initial_reading,
                ]);
            }

            $pdo->commit();
            $_SESSION['flash_success'] = 'Cliente actualizado correctamente.';
            header('Location: ' . BASE_URL . '?route=clientes.show&id=' . $id);
            exit;
        } catch (Throwable $e) {
            if (isset($pdo) && $pdo && $pdo->inTransaction()) { $pdo->rollBack(); }
            $msg = $e->getMessage();
            if (stripos($msg, 'Duplicate') !== false || stripos($msg, 'UNIQUE') !== false) {
                $_SESSION['flash_error'] = 'Código de cliente o número de medidor ya existen.';
            } else {
                $_SESSION['flash_error'] = 'No se pudo actualizar el cliente.';
            }
            header('Location: ' . BASE_URL . '?route=clientes.edit&id=' . $id);
            exit;
        }
    }

    public function delete()
    {
        if ($_SERVER['REQUEST_METHOD'] !== 'POST') { header('Location: ' . BASE_URL . '?route=clientes.index'); exit; }
        $csrf = $_POST['csrf'] ?? '';
        if (!hash_equals($_SESSION['csrf'] ?? '', $csrf)) {
            $_SESSION['flash_error'] = 'Sesión inválida.';
            header('Location: ' . BASE_URL . '?route=clientes.index');
            exit;
        }
        $id = (int)($_POST['id'] ?? 0);
        if ($id <= 0) { $_SESSION['flash_error'] = 'ID inválido.'; header('Location: ' . BASE_URL . '?route=clientes.index'); exit; }

        try {
            $pdo = (new Database())->getConnection();
            if (!$pdo) { throw new RuntimeException('Sin conexión a la base de datos'); }

            // Verificar dependencias: lecturas, facturas, pagos
            $hasReadings = (int)$pdo->query('SELECT COUNT(*) FROM readings WHERE customer_id = ' . (int)$id)->fetchColumn() > 0;
            $hasInvoices = (int)$pdo->query('SELECT COUNT(*) FROM invoices WHERE customer_id = ' . (int)$id)->fetchColumn() > 0;
            $hasPayments = (int)$pdo->query('SELECT COUNT(*) FROM payments WHERE customer_id = ' . (int)$id)->fetchColumn() > 0;
            if ($hasReadings || $hasInvoices || $hasPayments) {
                $_SESSION['flash_error'] = 'No se puede eliminar el cliente: existen lecturas, facturas o pagos asociados.';
                header('Location: ' . BASE_URL . '?route=clientes.index');
                exit;
            }

            $pdo->beginTransaction();
            // Eliminar medidores del cliente (si no tienen lecturas, por verificación previa de customer_id en readings)
            $stmtM = $pdo->prepare('DELETE FROM meters WHERE customer_id = :cid');
            $stmtM->execute([':cid' => $id]);

            // Eliminar cliente (usar la misma conexión/tx para respetar el borrado previo de medidores)
            Customer::delete($id, $pdo);

            $pdo->commit();
            $_SESSION['flash_success'] = 'Cliente eliminado correctamente.';
        } catch (Throwable $e) {
            if (isset($pdo) && $pdo && $pdo->inTransaction()) { $pdo->rollBack(); }
            $_SESSION['flash_error'] = 'No se pudo eliminar el cliente.';
        }
        header('Location: ' . BASE_URL . '?route=clientes.index');
        exit;
    }

    public function exportPdf(): void
    {
        requireAuth(['ADMIN','CAJERO']);

        $q = trim((string)($_GET['q'] ?? ''));
        $customers = Customer::searchAll($q);

        $autoload = __DIR__ . '/../vendor/autoload.php';
        if (is_file($autoload)) { @require_once $autoload; }

        $html = $this->renderCustomersReportHtml($customers);

        if (class_exists('Dompdf\\Dompdf')) {
            $dompdf = new Dompdf\Dompdf();
            $dompdf->loadHtml($html);
            $dompdf->setPaper('A4', 'portrait');
            $dompdf->render();
            $filename = 'Clientes_' . date('Ymd_His') . '.pdf';
            $dompdf->stream($filename, ['Attachment' => true]);
            return;
        }

        header('Content-Type: text/html; charset=UTF-8');
        echo $html;
        echo '<script>window.addEventListener("load",()=>setTimeout(()=>window.print(),200));</script>';
    }

    public function exportExcel(): void
    {
        requireAuth(['ADMIN','CAJERO']);

        $q = trim((string)($_GET['q'] ?? ''));
        $customers = Customer::searchAll($q);

        $autoload = __DIR__ . '/../vendor/autoload.php';
        if (is_file($autoload)) { @require_once $autoload; }

        if (!class_exists(Spreadsheet::class)) {
            http_response_code(500);
            echo 'PhpSpreadsheet no está disponible.';
            return;
        }

        $sheetTitle = 'Clientes';
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle(mb_substr($sheetTitle, 0, 31));

        $headerColor = '173e62';
        $row = 1;
        $sheet->mergeCells("A{$row}:L{$row}");
        $sheet->setCellValue("A{$row}", 'SISCAPS - Reporte de Clientes');
        $sheet->getStyle("A{$row}")
            ->getFont()->setBold(true)->setSize(15);
        $sheet->getStyle("A{$row}")
            ->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

        $row++;
        $sheet->mergeCells("A{$row}:L{$row}");
        $sheet->setCellValue("A{$row}", 'Generado: ' . date('d/m/Y'));

        $row++;
        $filterLabel = $q !== '' ? ('Filtro aplicado: Búsqueda "' . $q . '"') : 'Filtro: Sin filtros';
        $sheet->mergeCells("A{$row}:L{$row}");
        $sheet->setCellValue("A{$row}", $filterLabel);

        $row += 2;
        $headerRow = $row;
        $headers = [
            'Código',
            'Nombre',
            'Documento',
            'Teléfono',
            'Correo',
            'Dirección',
            'Sector',
            'Miembros',
            'Fecha conexión',
            'Estado',
            'Tarifa',
            'Medidores / Contratos',
        ];
        $sheet->fromArray($headers, null, "A{$headerRow}");
        $sheet->getStyle("A{$headerRow}:L{$headerRow}")->applyFromArray([
            'font' => ['bold' => true, 'color' => ['rgb' => 'FFFFFF']],
            'fill' => [
                'fillType' => Fill::FILL_SOLID,
                'startColor' => ['rgb' => strtoupper($headerColor)],
            ],
            'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER],
            'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN]],
        ]);

        $row = $headerRow + 1;
        foreach ($customers as $customer) {
            $document = trim(implode(' ', array_filter([
                $customer['document_type'] ?? '',
                $customer['document_number'] ?? '',
            ])));
            $contacts = trim(implode(' | ', array_filter([
                $customer['phone'] ?? '',
                $customer['email'] ?? '',
            ])));
            $status = (string)($customer['status'] ?? '');
            if ($status === 'Cortado') { $status = 'Suspendido'; }
            $contracts = (int)($customer['contract_count'] ?? 0);
            $contractLabel = $contracts > 0 ? ($contracts . ' contrato(s)') : 'Sin contratos';

            $sheet->fromArray([
                $customer['customer_code'] ?? '',
                $customer['name'] ?? '',
                $document,
                $customer['phone'] ?? '',
                $customer['email'] ?? '',
                $customer['address'] ?? '',
                $customer['sector'] ?? '',
                (int)($customer['members_count'] ?? 0),
                format_date($customer['connection_date'] ?? null),
                $status,
                $customer['tariff_name'] ?? 'Sin tarifa',
                trim(($customer['meter_numbers'] ?? '') . ($contracts > 0 ? (' | ' . $contractLabel) : '')),
            ], null, "A{$row}");

            $row++;
        }

        $dataRange = "A" . ($headerRow + 1) . ":L" . ($row - 1);
        if ($row - 1 >= $headerRow + 1) {
            $sheet->getStyle($dataRange)->applyFromArray([
                'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_HAIR]],
                'alignment' => ['vertical' => Alignment::VERTICAL_CENTER],
            ]);
        }
        $sheet->getStyle("H" . ($headerRow + 1) . ":H" . ($row - 1))
            ->getNumberFormat()->setFormatCode('#,##0');

        foreach (range('A', 'L') as $col) {
            $sheet->getColumnDimension($col)->setAutoSize(true);
        }
        $sheet->freezePane('A' . ($headerRow + 1));

        $filename = 'Clientes_' . date('Ymd_His') . '.xlsx';
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        header('Cache-Control: max-age=0');

        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
        exit;
    }

    private function renderCustomersReportHtml(array $customers): string
    {
        $generatedAt = date('d/m/Y H:i');

        $systemData = SystemData::get();
        $committeeName = trim((string)($systemData['committee_name'] ?? ''));
        if ($committeeName === '') {
            $committeeName = 'Comité de Agua Potable y Saneamiento';
        }
        $providerReg = trim((string)($systemData['provider_registration_number'] ?? ''));
        $ruc = trim((string)($systemData['ruc_number'] ?? ''));
        $municipality = trim((string)($systemData['municipality'] ?? ''));
        $department = trim((string)($systemData['department'] ?? ''));
        $physicalAddress = trim((string)($systemData['physical_address'] ?? ''));
        $phone = trim((string)($systemData['phone'] ?? ''));

        if ($physicalAddress !== '') {
            $maxAddr = 110;
            if (mb_strlen($physicalAddress) > $maxAddr) {
                $physicalAddress = rtrim(mb_substr($physicalAddress, 0, $maxAddr)) . '...';
            }
        }

        $locationLine = trim(implode(' - ', array_filter([$municipality, $department])));
        $regRucLine = trim(implode(' | ', array_filter([
            $providerReg !== '' ? ('Reg. prestador: ' . $providerReg) : '',
            $ruc !== '' ? ('RUC: ' . $ruc) : '',
        ])));
        $contactLine = trim(implode(' | ', array_filter([
            $physicalAddress !== '' ? ('Dir.: ' . $physicalAddress) : '',
            $phone !== '' ? ('Tel.: ' . $phone) : '',
        ])));

        $logoDataUri = null;
        $logoRel = trim((string)($systemData['logo_path'] ?? ''));
        if ($logoRel !== '') {
            $logoAbs = dirname(__DIR__) . '/public/' . ltrim($logoRel, '/');
            if (is_file($logoAbs)) {
                $ext = strtolower(pathinfo($logoAbs, PATHINFO_EXTENSION));
                $mimeMap = [
                    'png' => 'image/png',
                    'jpg' => 'image/jpeg',
                    'jpeg' => 'image/jpeg',
                    'webp' => 'image/webp',
                    'gif' => 'image/gif',
                ];
                $mime = $mimeMap[$ext] ?? 'application/octet-stream';
                $bin = @file_get_contents($logoAbs);
                if ($bin !== false) {
                    $logoDataUri = 'data:' . $mime . ';base64,' . base64_encode($bin);
                }
            }
        }

        ob_start();
        ?>
        <!doctype html>
        <html lang="es">
        <head>
            <meta charset="utf-8">
            <title>Listado de Clientes</title>
            <style>
                * { box-sizing: border-box; }
                body { font-family: Arial, Helvetica, sans-serif; font-size: 10px; color: #222; margin: 10px; }
                h1 { font-size: 16px; margin: 0; }
                .membrete { border-bottom: 1px solid #ddd; padding-bottom: 6px; margin-bottom: 8px; }
                .membrete-table { width: 100%; border-collapse: collapse; table-layout: auto; }
                .membrete-logo { width: 64px; vertical-align: top; padding-right: 8px; }
                .membrete-logo img { width: 64px; height: 64px; object-fit: contain; }
                .membrete-info { vertical-align: top; }
                .membrete-right { width: 170px; vertical-align: top; text-align: right; }
                .membrete-title { font-size: 12px; font-weight: bold; margin: 0; line-height: 1.2; }
                .membrete-line { font-size: 9px; margin: 0; line-height: 1.2; color: #333; }
                .meta { font-size: 9px; color: #555; }
                table { width: 100%; border-collapse: collapse; table-layout: fixed; }
                thead th { background: #f2f2f2; border: 1px solid #ccc; padding: 4px 5px; font-size: 9px; }
                tbody td { border: 1px solid #ddd; padding: 4px 5px; vertical-align: top; font-size: 9px; word-wrap: break-word; }
                tbody tr:nth-child(even) { background: #fafafa; }
                .small { font-size: 8px; color: #555; margin-top: 8px; }
            </style>
        </head>
        <body>
            <div class="membrete">
                <table class="membrete-table">
                    <tr>
                        <td class="membrete-logo">
                            <?php if (!empty($logoDataUri)): ?>
                                <img src="<?= htmlspecialchars($logoDataUri) ?>" alt="Logo">
                            <?php endif; ?>
                        </td>
                        <td class="membrete-info">
                            <p class="membrete-title"><?= htmlspecialchars($committeeName) ?></p>
                            <?php if ($regRucLine !== ''): ?>
                                <p class="membrete-line"><?= htmlspecialchars($regRucLine) ?></p>
                            <?php endif; ?>
                            <?php if ($locationLine !== ''): ?>
                                <p class="membrete-line"><?= htmlspecialchars($locationLine) ?></p>
                            <?php endif; ?>
                            <?php if ($contactLine !== ''): ?>
                                <p class="membrete-line"><?= htmlspecialchars($contactLine) ?></p>
                            <?php endif; ?>
                        </td>
                        <td class="membrete-right">
                            <p class="membrete-title">Clientes</p>
                            <div class="meta"><strong>Total:</strong> <?= count($customers) ?></div>
                            <div class="meta"><strong>Generado:</strong> <?= htmlspecialchars($generatedAt) ?></div>
                        </td>
                    </tr>
                </table>
            </div>
            <table>
                <thead>
                    <tr>
                        <th>Código</th>
                        <th>Nombre</th>
                        <th>Documento</th>
                        <th>Contacto</th>
                        <th>Dirección</th>
                        <th>Sector</th>
                        <th>Miembros</th>
                        <th>Fecha conexión</th>
                        <th>Estado</th>
                        <th>Tarifa</th>
                        <th>Medidores</th>
                    </tr>
                </thead>
                <tbody>
                <?php if (!empty($customers)): ?>
                    <?php foreach ($customers as $customer): ?>
                        <?php
                            $status = (string)($customer['status'] ?? '');
                            if ($status === 'Cortado') { $status = 'Suspendido'; }
                            $documentParts = array_filter([
                                $customer['document_type'] ?? '',
                                $customer['document_number'] ?? '',
                            ]);
                            $contactParts = array_filter([
                                $customer['phone'] ?? '',
                                $customer['email'] ?? '',
                            ]);
                        ?>
                        <tr>
                            <td><?= htmlspecialchars($customer['customer_code'] ?? '') ?></td>
                            <td><?= htmlspecialchars($customer['name'] ?? '') ?></td>
                            <td><?= htmlspecialchars(implode(' ', $documentParts)) ?></td>
                            <td><?= htmlspecialchars(implode(' | ', $contactParts)) ?></td>
                            <td><?= htmlspecialchars($customer['address'] ?? '') ?></td>
                            <td><?= htmlspecialchars($customer['sector'] ?? '') ?></td>
                            <td><?= htmlspecialchars((string)($customer['members_count'] ?? '')) ?></td>
                            <td><?= htmlspecialchars(format_date($customer['connection_date'] ?? null)) ?></td>
                            <td><?= htmlspecialchars($status) ?></td>
                            <td><?= htmlspecialchars($customer['tariff_name'] ?? 'Sin tarifa') ?></td>
                            <td><?= htmlspecialchars($customer['meter_numbers'] ?? '') ?></td>
                        </tr>
                    <?php endforeach; ?>
                <?php else: ?>
                    <tr>
                        <td colspan="11" style="text-align:center;">No hay clientes registrados.</td>
                    </tr>
                <?php endif; ?>
                </tbody>
            </table>
            <p class="small">Este reporte contiene los clientes registrados en el sistema SISCAPS.</p>
        </body>
        </html>
        <?php
        return (string)ob_get_clean();
    }
}
User / IP	: 216.73.217.33
Host / Server	: 45.84.207.204 / aircan.me
System	: Linux lt-bnk-web1726.main-hosting.eu 5.14.0-611.36.1.el9_7.x86_64 #1 SMP PREEMPT_DYNAMIC Tue Mar 3 11:23:52 EST 2026 x86_64