Tul xxx Tul

Viewing: PagosController.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;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;

class PagosController
{
    private function ensureCsrf(): string
    {
        if (empty($_SESSION['csrf'])) {
            $_SESSION['csrf'] = bin2hex(random_bytes(32));
        }
        return $_SESSION['csrf'];
    }

    private function getCategories(): array
    {
        return ['Servicio','Inscripcion','Reconexion','Multa'];
    }

    private function collectPaymentFilters(): array
    {
        return [
            'q' => trim($_GET['q'] ?? ''),
            'customer_id' => $_GET['customer_id'] ?? '',
            'invoice_id' => $_GET['invoice_id'] ?? '',
            'method' => $_GET['method'] ?? '',
            'category' => $_GET['category'] ?? '',
            'date_from' => $_GET['date_from'] ?? '',
            'date_to' => $_GET['date_to'] ?? '',
        ];
    }

    public function index(): void
    {
        $csrf = $this->ensureCsrf();
        $filters = $this->collectPaymentFilters();
        $page = max(1, (int)($_GET['page'] ?? 1));
        $perPage = max(1, min(100, (int)($_GET['perPage'] ?? 15)));

        $result = Payment::getAll($filters, $page, $perPage);
        $items = $result['items'];
        $total = $result['total'];
        $lastPage = $result['lastPage'];

        $customers = Customer::getActive();
        $methods = ['Efectivo','Transferencia','POS','Cheque','Otro'];
        $categories = $this->getCategories();

        require __DIR__ . '/../views/pagos/index.php';
    }

    public function create(): void
    {
        $csrf = $this->ensureCsrf();
        // Evitar que el navegador sirva versión en caché de esta página
        header('Cache-Control: no-store, no-cache, must-revalidate, max-age=0');
        header('Pragma: no-cache');
        header('Expires: 0');
        // Mostrar solo clientes con facturas abiertas (saldo > 0)
        $customers = Invoice::customersWithOpenBalance();
        $methods = ['Efectivo','Transferencia','POS','Cheque','Otro'];
        $selectedCustomer = isset($_GET['customer_id']) ? (int)$_GET['customer_id'] : null;
        $openInvoices = $selectedCustomer ? Payment::getOpenInvoicesByCustomer($selectedCustomer) : [];
        $defaultAccountId = Account::defaultCashAccountId();
        $accounts = Account::allActive();
        require __DIR__ . '/../views/pagos/create.php';
    }

    public function invoices(): void
    {
        header('Content-Type: application/json');
        header('Cache-Control: no-store, no-cache, must-revalidate, max-age=0');
        header('Pragma: no-cache');
        header('Expires: 0');
        $cid = (int)($_GET['customer_id'] ?? 0);
        if ($cid <= 0) { echo json_encode([]); return; }
        // Si el cliente no tiene contrato, no mostrar facturas
        if (!Contract::existsForCustomer($cid)) { echo json_encode([]); return; }
        // Actualizar mora antes de responder
        Invoice::refreshLateFeesForCustomer($cid);
        $rows = Payment::getOpenInvoicesByCustomer($cid);
        echo json_encode($rows);
    }

    public function store(): void
    {
        if (($_SERVER['REQUEST_METHOD'] ?? 'GET') !== 'POST') { redirect('pagos.index'); }
        $token = $_POST['csrf'] ?? '';
        if (!$token || !isset($_SESSION['csrf']) || !hash_equals($_SESSION['csrf'], $token)) {
            http_response_code(400);
            echo 'CSRF inválido';
            return;
        }

        $customer_id = (int)($_POST['customer_id'] ?? 0);
        $invoice_id = (int)($_POST['invoice_id'] ?? 0);
        $amount = (float)($_POST['amount'] ?? 0);
        $method = trim($_POST['method'] ?? '');
        $note = trim($_POST['note'] ?? '');
        $dateOnly = $_POST['payment_date'] ?? date('Y-m-d');
        $payment_date = $dateOnly . ' ' . date('H:i:s');
        $userId = (int)($_SESSION['user']['id'] ?? 0);
        $allowedCategories = $this->getCategories();
        $category = 'Servicio';
        $account_id = (int)($_POST['account_id'] ?? 0);
        $role = $_SESSION['user']['role'] ?? '';
        if ($role === 'CAJERO') {
            $account_id = Account::defaultCashAccountId();
        }

        if ($customer_id <= 0 || $invoice_id <= 0 || $amount <= 0 || $method === '' || $account_id <= 0) {
            setFlashMessage('error', 'Completa todos los campos obligatorios.');
            redirect('pagos.create');
        }

        // Bloquear cobros para clientes sin contrato
        if (!Contract::existsForCustomer($customer_id)) {
            setFlashMessage('error', 'El cliente no tiene contrato registrado. No puedes registrar cobros.');
            redirect('pagos.create');
        }

        $pdo = (new Database())->getConnection();

        // Asegurar que el total de la factura esté actualizado con mora
        if ($invoice_id > 0) {
            Invoice::refreshLateFeeForInvoice($invoice_id, $pdo);
        }

        // Modo pago a una sola factura
        $stmt = $pdo->prepare('SELECT id, invoice_number, customer_id, total, status, category FROM invoices WHERE id = :id LIMIT 1');
        $stmt->execute([':id' => $invoice_id]);
        $inv = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!$inv) { setFlashMessage('error', 'Factura no encontrada.'); redirect('pagos.create'); }
        if ((int)$inv['customer_id'] !== $customer_id) { setFlashMessage('error', 'La factura no corresponde al cliente seleccionado.'); redirect('pagos.create'); }

        $account = Account::find($account_id);
        if (!$account || (int)$account['is_active'] !== 1) {
            setFlashMessage('error', 'La cuenta seleccionada no está disponible.');
            redirect('pagos.create');
        }

        $customerRow = Customer::findById($customer_id) ?? [];

        $invoiceCategory = $inv['category'] ?? 'Servicio';
        if (in_array($invoiceCategory, $allowedCategories, true)) {
            $category = $invoiceCategory;
        }

        $paid = Payment::sumPaidForInvoice($invoice_id);
        $balance = max(0.0, (float)$inv['total'] - $paid);
        if ($amount - 0.00001 > $balance) {
            setFlashMessage('error', 'El monto excede el saldo de la factura.');
            redirect('pagos.create');
        }

        try {
            $concept = 'Cobro factura ' . ($inv['invoice_number'] ?? ('#' . $invoice_id));
            $reference = $inv['invoice_number'] ?? null;
            $id = Payment::create([
                'invoice_id' => $invoice_id,
                'customer_id' => $customer_id,
                'payment_date' => $payment_date,
                'amount' => $amount,
                'method' => $method,
                'category' => $category,
                'received_by_user_id' => $userId,
                'note' => $note !== '' ? $note : null,
                'account_id' => $account_id,
                'account_concept' => $concept,
                'account_reference' => $reference,
                'account_method' => $method,
                'account_counterparty' => $customerRow['name'] ?? null,
                'invoice_number' => $inv['invoice_number'] ?? null,
            ]);

            // Actualizar estado de la factura
            $paidAfter = $paid + $amount;
            $total = (float)$inv['total'];
            $status = $paidAfter >= $total && $total > 0 ? 'Pagado' : ($paidAfter > 0 ? 'Parcial' : 'Pendiente');
            Invoice::updateStatus($invoice_id, $status);

            setFlashMessage('success', 'Pago registrado correctamente.');
            header('Location: ' . BASE_URL . '?route=pagos.show&id=' . (int)$id);
            exit;
        } catch (Throwable $e) {
            setFlashMessage('error', 'No se pudo registrar el pago: ' . $e->getMessage());
            redirect('pagos.create');
        }
    }

    public function show(): void
    {
        $csrf = $this->ensureCsrf();
        $id = (int)($_GET['id'] ?? 0);
        if ($id <= 0) { http_response_code(400); echo 'ID inválido'; return; }
        $payment = Payment::findById($id);
        if (!$payment) { http_response_code(404); echo 'Pago no encontrado'; return; }
        require __DIR__ . '/../views/pagos/show.php';
    }

    public function edit(): void
    {
        $csrf = $this->ensureCsrf();
        $id = (int)($_GET['id'] ?? 0);
        if ($id <= 0) { http_response_code(400); echo 'ID inválido'; return; }
        $payment = Payment::findById($id);
        if (!$payment) { http_response_code(404); echo 'Pago no encontrado'; return; }
        $methods = ['Efectivo','Transferencia','POS','Cheque','Otro'];
        $categories = $this->getCategories();
        $defaultAccountId = Account::defaultCashAccountId();
        $accounts = Account::allActive();
        require __DIR__ . '/../views/pagos/edit.php';
    }

    public function update(): void
    {
        if (($_SERVER['REQUEST_METHOD'] ?? 'GET') !== 'POST') { redirect('pagos.index'); }
        $token = $_POST['csrf'] ?? '';
        if (!$token || !isset($_SESSION['csrf']) || !hash_equals($_SESSION['csrf'], $token)) {
            http_response_code(400);
            echo 'CSRF inválido';
            return;
        }
        $id = (int)($_POST['id'] ?? 0);
        if ($id <= 0) { redirect('pagos.index'); }

        $amount = (float)($_POST['amount'] ?? 0);
        $method = trim($_POST['method'] ?? '');
        $note = trim($_POST['note'] ?? '');
        $date = trim($_POST['payment_date'] ?? '');
        $time = trim($_POST['payment_time'] ?? '');
        $methods = ['Efectivo','Transferencia','POS','Cheque','Otro'];
        $categories = $this->getCategories();
        $categoryInput = trim($_POST['category'] ?? 'Servicio');
        $category = in_array($categoryInput, $categories, true) ? $categoryInput : 'Servicio';
        if (!in_array($method, $methods, true)) { $method = 'Efectivo'; }
        if ($amount <= 0) { setFlashMessage('error', 'El monto debe ser mayor a 0.'); header('Location: ' . BASE_URL . '?route=pagos.edit&id=' . $id); exit; }

        // Cargar pago actual para obtener invoice_id y hora por defecto si no se envía
        $current = Payment::findById($id);
        if (!$current) { setFlashMessage('error', 'Pago no encontrado.'); redirect('pagos.index'); }
        $existingTime = '';
        if (!empty($current['payment_date'])) {
            $existingTime = date('H:i:s', strtotime($current['payment_date']));
        }
        $finalDateTime = null;
        if ($date !== '') {
            $hhmmss = $time !== '' ? ($time . (strlen($time) === 5 ? ':00' : '')) : ($existingTime ?: date('H:i:s'));
            $finalDateTime = $date . ' ' . $hhmmss;
        } else {
            // mantener fecha/hora actual si no se envía fecha
            $finalDateTime = $current['payment_date'] ?? date('Y-m-d H:i:s');
        }

        $account_id = (int)($_POST['account_id'] ?? ($current['account_id'] ?? 0));
        $role = $_SESSION['user']['role'] ?? '';
        if ($role === 'CAJERO') {
            $account_id = Account::defaultCashAccountId();
        }
        if ($account_id <= 0) {
            setFlashMessage('error', 'Selecciona la cuenta donde registrar el cobro.');
            header('Location: ' . BASE_URL . '?route=pagos.edit&id=' . $id);
            exit;
        }
        $accountRow = Account::find($account_id);
        if (!$accountRow || (int)($accountRow['is_active'] ?? 0) !== 1) {
            setFlashMessage('error', 'La cuenta seleccionada no está disponible.');
            header('Location: ' . BASE_URL . '?route=pagos.edit&id=' . $id);
            exit;
        }

        try {
            $pdo = (new Database())->getConnection();
            $pdo->beginTransaction();

            $updated = Payment::updateById($id, [
                'amount' => $amount,
                'method' => $method,
                'category' => $category,
                'note' => $note,
                'payment_date' => $finalDateTime,
                'account_id' => $account_id,
            ], $pdo);
            if (!$updated) {
                throw new RuntimeException('No se pudo actualizar el pago.');
            }

            $concept = 'Cobro factura ' . ($current['invoice_number'] ?? ('#' . ($current['invoice_id'] ?? $id)));
            $reference = $current['invoice_number'] ?? null;
            $counterparty = $current['customer_name'] ?? null;
            $txnId = (int)($current['account_transaction_id'] ?? 0);
            $previousAccountId = (int)($current['account_id'] ?? 0);

            if ($txnId > 0 && $previousAccountId !== $account_id) {
                AccountTransaction::deleteCascade($txnId, $pdo);
                $ledger = AccountTransaction::record(
                    $account_id,
                    'Ingreso',
                    $amount,
                    $concept,
                    $reference,
                    $method,
                    $counterparty,
                    $note !== '' ? $note : null,
                    (int)($_SESSION['user']['id'] ?? 0),
                    $finalDateTime,
                    $pdo,
                    $id
                );
                Payment::setAccountTransaction($id, (int)$ledger['id'], $pdo);
            } elseif ($txnId > 0) {
                AccountTransaction::updateExisting(
                    $txnId,
                    $amount,
                    [
                        'reference' => $reference,
                        'concept' => $concept,
                        'method' => $method,
                        'counterparty' => $counterparty,
                        'notes' => $note !== '' ? $note : null,
                        'transacted_at' => $finalDateTime,
                    ],
                    $pdo
                );
            } else {
                $ledger = AccountTransaction::record(
                    $account_id,
                    'Ingreso',
                    $amount,
                    $concept,
                    $reference,
                    $method,
                    $counterparty,
                    $note !== '' ? $note : null,
                    (int)($_SESSION['user']['id'] ?? 0),
                    $finalDateTime,
                    $pdo,
                    $id
                );
                Payment::setAccountTransaction($id, (int)$ledger['id'], $pdo);
            }

            // Recalcular estado de factura si corresponde
            $invoiceId = (int)($current['invoice_id'] ?? 0);
            if ($invoiceId > 0) {
                $stmt2 = $pdo->prepare('SELECT total FROM invoices WHERE id = :id LIMIT 1');
                $stmt2->execute([':id' => $invoiceId]);
                $total = (float)($stmt2->fetchColumn() ?: 0);
                $paid = Payment::sumPaidForInvoice($invoiceId);
                $status = $paid >= $total && $total > 0 ? 'Pagado' : ($paid > 0 ? 'Parcial' : 'Pendiente');
                Invoice::updateStatus($invoiceId, $status);
            }

            $pdo->commit();

            setFlashMessage('success', 'Pago actualizado correctamente.');
            header('Location: ' . BASE_URL . '?route=pagos.show&id=' . $id);
            exit;
        } catch (Throwable $e) {
            if (isset($pdo) && $pdo->inTransaction()) { $pdo->rollBack(); }
            setFlashMessage('error', 'No se pudo actualizar el pago: ' . $e->getMessage());
            header('Location: ' . BASE_URL . '?route=pagos.edit&id=' . $id);
            exit;
        }
    }

    public function receipt(): void
    {
        $id = (int)($_GET['id'] ?? 0);
        if ($id <= 0) { http_response_code(400); echo 'ID inválido'; return; }
        $payment = Payment::findById($id);
        if (!$payment) { http_response_code(404); echo 'Pago no encontrado'; return; }
        // Restringir a CLIENTE dueño del pago
        $role = $_SESSION['user']['role'] ?? '';
        if ($role === 'CLIENTE') {
            $cid = (int)($_SESSION['user']['customer_id'] ?? 0);
            if ($cid <= 0 || (int)($payment['customer_id'] ?? 0) !== $cid) {
                http_response_code(403);
                echo '403 Prohibido';
                return;
            }
        }

        // Intentar cargar composer si existe
        $autoload = __DIR__ . '/../vendor/autoload.php';
        if (is_file($autoload)) { @require_once $autoload; }

        $html = $this->renderReceiptHtml($payment);

        if (class_exists('Dompdf\\Dompdf')) {
            $dompdf = new Dompdf\Dompdf();
            $dompdf->loadHtml($html);
            $dompdf->setPaper('A5', 'portrait');
            $dompdf->render();
            $dompdf->stream('Recibo-' . ($payment['receipt_number'] ?? $payment['id']) . '.pdf', ['Attachment' => true]);
            return;
        }

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

    public function receiptTicket(): void
    {
        $id = (int)($_GET['id'] ?? 0);
        if ($id <= 0) { http_response_code(400); echo 'ID inválido'; return; }
        $payment = Payment::findById($id);
        if (!$payment) { http_response_code(404); echo 'Pago no encontrado'; return; }
        // Restringir a CLIENTE dueño del pago
        $role = $_SESSION['user']['role'] ?? '';
        if ($role === 'CLIENTE') {
            $cid = (int)($_SESSION['user']['customer_id'] ?? 0);
            if ($cid <= 0 || (int)($payment['customer_id'] ?? 0) !== $cid) {
                http_response_code(403);
                echo '403 Prohibido';
                return;
            }
        }

        // Intentar cargar composer si existe
        $autoload = __DIR__ . '/../vendor/autoload.php';
        if (is_file($autoload)) { @require_once $autoload; }

        $html = $this->renderReceiptTicketHtml($payment);

        if (class_exists('Dompdf\\Dompdf')) {
            $dompdf = new Dompdf\Dompdf();
            $dompdf->loadHtml($html);
            // 57mm de ancho (~162pt). Alto generoso para contenido.
            $dompdf->setPaper([0, 0, 162, 1200], 'portrait');
            $dompdf->render();
            $dompdf->stream('Ticket-Recibo-' . ($payment['receipt_number'] ?? $payment['id']) . '.pdf', ['Attachment' => true]);
            return;
        }

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

    public function delete(): void
    {
        if (($_SERVER['REQUEST_METHOD'] ?? 'GET') !== 'POST') { redirect('pagos.index'); }
        $token = $_POST['csrf'] ?? '';
        if (!$token || !isset($_SESSION['csrf']) || !hash_equals($_SESSION['csrf'], $token)) {
            http_response_code(400);
            echo 'CSRF inválido';
            return;
        }
        $id = (int)($_POST['id'] ?? 0);
        if ($id <= 0) { redirect('pagos.index'); }

        $p = Payment::findById($id);
        if (!$p) { $_SESSION['flash_error'] = 'Pago no encontrado.'; redirect('pagos.index'); }
        $invoiceId = (int)($p['invoice_id'] ?? 0);

        try {
            $pdo = (new Database())->getConnection();
            $pdo->beginTransaction();
            $stmt = $pdo->prepare('DELETE FROM payments WHERE id = :id');
            $stmt->execute([':id' => $id]);
            $deleted = $stmt->rowCount() > 0;

            if ($deleted && $invoiceId > 0) {
                // Recalcular estado de factura después de eliminar usando la misma transacción
                $stmt2 = $pdo->prepare('SELECT total FROM invoices WHERE id = :id LIMIT 1');
                $stmt2->execute([':id' => $invoiceId]);
                $total = (float)($stmt2->fetchColumn() ?: 0);

                $stmt3 = $pdo->prepare('SELECT COALESCE(SUM(amount),0) FROM payments WHERE invoice_id = :id');
                $stmt3->execute([':id' => $invoiceId]);
                $paid = (float)$stmt3->fetchColumn();

                $status = $paid >= $total && $total > 0 ? 'Pagado' : ($paid > 0 ? 'Parcial' : 'Pendiente');
                Invoice::updateStatus($invoiceId, $status);
            }

            $pdo->commit();
            setFlashMessage($deleted ? 'success' : 'warning', $deleted ? 'Pago eliminado correctamente.' : 'No se eliminó el pago.');
        } catch (Throwable $e) {
            if ($pdo->inTransaction()) { $pdo->rollBack(); }
            setFlashMessage('error', 'No se pudo eliminar el pago: ' . $e->getMessage());
        }
        redirect('pagos.index');
    }

    public function exportPdf(): void
    {
        requireAuth(['ADMIN','CAJERO']);
        $filters = $this->collectPaymentFilters();
        $rows = $this->fetchAllPayments($filters);

        // Intentar cargar composer si existe (para Dompdf)
        $autoload = __DIR__ . '/../vendor/autoload.php';
        if (is_file($autoload)) { @require_once $autoload; }

        $html = $this->renderPaymentsReportHtml($rows, $filters);

        if (class_exists('Dompdf\\Dompdf')) {
            $dompdf = new Dompdf\Dompdf();
            $dompdf->loadHtml($html);
            $dompdf->setPaper('A4', 'portrait');
            $dompdf->render();
            $fname = 'Cobros';
            // Añadir rango de fechas si aplica
            if (!empty($filters['date_from']) || !empty($filters['date_to'])) {
                $from = $filters['date_from'] ?: 'inicio';
                $to = $filters['date_to'] ?: 'hoy';
                $fname .= '_' . $from . '_a_' . $to;
            }
            $dompdf->stream($fname . '.pdf', ['Attachment' => true]);
            return;
        }

        // Fallback HTML imprimible
        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']);
        $filters = $this->collectPaymentFilters();
        $rows = $this->fetchAllPayments($filters);
        $totalAmount = 0.0;
        foreach ($rows as $r) {
            $totalAmount += (float)($r['amount'] ?? 0);
        }

        $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;
        }

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle('Cobros');

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

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

        $row++;
        $sheet->mergeCells("A{$row}:K{$row}");
        $sheet->setCellValue("A{$row}", 'Filtros: ' . $this->describePaymentFilters($filters));

        $row += 2;
        $headerRow = $row;
        $headers = [
            'Recibo',
            'Factura',
            'Cliente',
            'Código',
            'Fecha de pago',
            'Método',
            'Categoría',
            'Cuenta',
            'Monto',
            'Cajero',
            'Nota',
        ];
        $sheet->fromArray($headers, null, "A{$headerRow}");
        $sheet->getStyle("A{$headerRow}:K{$headerRow}")->applyFromArray([
            'font' => ['bold' => true, 'color' => ['rgb' => 'FFFFFF']],
            'fill' => [
                'fillType' => Fill::FILL_SOLID,
                'startColor' => ['rgb' => strtoupper($headerColor)],
            ],
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER,
            ],
            'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN]],
        ]);

        $row = $headerRow + 1;
        foreach ($rows as $payment) {
            $sheet->fromArray([
                $payment['receipt_number'] ?? ('#' . ($payment['id'] ?? '')),
                $payment['invoice_number'] ?? '-',
                $payment['customer_name'] ?? '',
                $payment['customer_code'] ?? '',
                format_datetime($payment['payment_date'] ?? ''),
                $payment['method'] ?? '',
                $payment['category'] ?? '',
                $this->formatAccountLabel($payment),
                (float)($payment['amount'] ?? 0),
                $payment['cashier_name'] ?? '',
                $payment['note'] ?? '',
            ], null, "A{$row}");
            $row++;
        }

        $dataEndRow = $row - 1;
        if ($dataEndRow >= $headerRow + 1) {
            $sheet->getStyle("A" . ($headerRow + 1) . ":K{$dataEndRow}")->applyFromArray([
                'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_HAIR]],
                'alignment' => ['vertical' => Alignment::VERTICAL_CENTER],
            ]);
            $sheet->getStyle("I" . ($headerRow + 1) . ":I{$dataEndRow}")
                ->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
            $sheet->setAutoFilter("A{$headerRow}:K{$dataEndRow}");
        }

        if ($dataEndRow >= $headerRow + 1) {
            $sheet->mergeCells("A{$row}:H{$row}");
            $sheet->setCellValue("A{$row}", 'Total recaudado');
            $sheet->setCellValue("I{$row}", $totalAmount);
            $sheet->getStyle("A{$row}:K{$row}")->applyFromArray([
                'font' => ['bold' => true],
                'borders' => ['top' => ['borderStyle' => Border::BORDER_THIN]],
            ]);
            $sheet->getStyle("I{$row}")
                ->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
            $row++;
        }

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

        $filename = 'Cobros_' . 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 fetchAllPayments(array $filters): array
    {
        $res = Payment::getAll($filters, 1, 100000);
        return $res['items'] ?? [];
    }

    private function describePaymentFilters(array $filters): string
    {
        $parts = [];
        if (!empty($filters['q'])) {
            $parts[] = 'Búsqueda: "' . $filters['q'] . '"';
        }
        if (!empty($filters['customer_id'])) {
            $customer = Customer::findById((int)$filters['customer_id']);
            $parts[] = 'Cliente: ' . ($customer['name'] ?? ('ID ' . $filters['customer_id']));
        }
        if (!empty($filters['invoice_id'])) {
            $parts[] = 'Factura ID: ' . $filters['invoice_id'];
        }
        if (!empty($filters['method'])) {
            $parts[] = 'Método: ' . $filters['method'];
        }
        if (!empty($filters['category'])) {
            $parts[] = 'Categoría: ' . $filters['category'];
        }
        if (!empty($filters['date_from']) || !empty($filters['date_to'])) {
            $parts[] = 'Rango: ' . ($filters['date_from'] ?: 'inicio') . ' a ' . ($filters['date_to'] ?: 'hoy');
        }
        return $parts ? implode(' | ', $parts) : 'Sin filtros';
    }

    private function formatAccountLabel(array $payment): string
    {
        $name = $payment['account_name'] ?? '';
        $code = $payment['account_code'] ?? '';
        if ($name === '' && $code === '') {
            return 'No vinculado';
        }
        $label = $name !== '' ? $name : '';
        if ($code !== '') {
            $label .= $label !== '' ? ' (' . $code . ')' : $code;
        }
        return trim($label);
    }

    private function renderReceiptHtml(array $p): string
    {
        $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'] ?? ''));
        $municipalCertificate = trim((string)($systemData['municipal_certificate'] ?? ''));
        $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) : '',
            $municipalCertificate !== '' ? ('Cert. municipal: ' . $municipalCertificate) : '',
            $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>Recibo <?= htmlspecialchars($p['receipt_number'] ?? ('#'.$p['id'])) ?></title>
            <style>
            * { box-sizing: border-box; }
            body { font-family: Arial, sans-serif; font-size: 12px; color:#222; margin: 12px; }
            .membrete { border-bottom: 1px solid #ddd; padding-bottom: 6px; margin-bottom: 10px; }
            .membrete-table { width: 100%; border-collapse: collapse; table-layout: auto; }
            .membrete-logo { width: 56px; vertical-align: top; padding-right: 8px; }
            .membrete-logo img { width: 56px; height: 56px; object-fit: contain; }
            .membrete-info { vertical-align: top; }
            .membrete-right { width: 190px; 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; }
            .membrete-meta { font-size: 10px; color: #333; }
            .box { border:1px solid #ccc; padding:10px; border-radius:6px; margin-bottom:8px; }
            table { width:100%; border-collapse: collapse; }
            th, td { padding: 6px; text-align:left; }
            .right { text-align:right; }
            .muted { color:#666; font-size: 11px; }
            </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">Recibo</p>
                            <div class="membrete-meta"><strong>Nº:</strong> <?= htmlspecialchars($p['receipt_number'] ?? ('#'.$p['id'])) ?></div>
                            <div class="membrete-meta"><strong>Fecha:</strong> <?= htmlspecialchars(format_datetime($p['payment_date'] ?? '')) ?></div>
                            <div class="membrete-meta"><strong>Cajero:</strong> <?= htmlspecialchars($p['cashier_name'] ?? '') ?></div>
                        </td>
                    </tr>
                </table>
            </div>
            <div class="box">
                <strong>Cliente:</strong> <?= htmlspecialchars($p['customer_name'] ?? '') ?><br>
                <span class="muted"><?= htmlspecialchars($p['customer_address'] ?? '') ?></span>
            </div>
            <div class="box">
                <table>
                    <tr>
                        <th>Factura</th>
                        <td><?= htmlspecialchars($p['invoice_number'] ?? '-') ?></td>
                    </tr>
                    <tr>
                        <th>Total factura</th>
                        <td class="right"><?= format_currency($p['invoice_total'] ?? 0) ?></td>
                    </tr>
                    <tr>
                        <th>Método</th>
                        <td><?= htmlspecialchars($p['method'] ?? '') ?></td>
                    </tr>
                    <tr>
                        <th>Monto pagado</th>
                        <td class="right"><strong><?= format_currency($p['amount'] ?? 0) ?></strong></td>
                    </tr>
                </table>
            </div>
            <div class="muted">Nota: <?= htmlspecialchars($p['note'] ?? '') ?></div>
        </body>
        </html>
        <?php
        return (string)ob_get_clean();
    }

    private function renderReceiptTicketHtml(array $p): string
    {
        $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'] ?? ''));
        $municipalCertificate = trim((string)($systemData['municipal_certificate'] ?? ''));
        $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 = 55;
            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) : '',
            $municipalCertificate !== '' ? ('Cert. municipal: ' . $municipalCertificate) : '',
            $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>Ticket Recibo <?= htmlspecialchars($p['receipt_number'] ?? ('#'.$p['id'])) ?></title>
            <style>
                @page { margin: 3mm 2mm; }
                body { font-family: DejaVu Sans, Arial, sans-serif; font-size: 8px; line-height: 1.15; width: 57mm; margin: 0 auto; color:#000; }
                .center { text-align:center; }
                .right { text-align:right; }
                .b { font-weight:bold; }
                .sep { margin: 4px 0; border-top: 1px dashed #000; height: 0; }
                .small { font-size: 6.5px; }
                .logo { max-width: 40mm; max-height: 18mm; margin: 0 auto 2px; display:block; object-fit: contain; }
            table { width:100%; border-collapse: collapse; }
                td { vertical-align: top; padding: 1px 0; }
                td.right { text-align:left; padding-left: 6mm; }
            </style>
        </head>
        <body>
            <div class="center">
                <?php if (!empty($logoDataUri)): ?>
                    <img class="logo" src="<?= htmlspecialchars($logoDataUri) ?>" alt="Logo">
                <?php endif; ?>
                <div class="b"><?= htmlspecialchars($committeeName) ?></div>
                <?php if ($regRucLine !== ''): ?>
                    <div class="small"><?= htmlspecialchars($regRucLine) ?></div>
                <?php endif; ?>
                <?php if ($locationLine !== ''): ?>
                    <div class="small"><?= htmlspecialchars($locationLine) ?></div>
                <?php endif; ?>
                <?php if ($contactLine !== ''): ?>
                    <div class="small"><?= htmlspecialchars($contactLine) ?></div>
                <?php endif; ?>
            </div>
            <div class="sep"></div>
            <div><span class="b">Recibo:</span> <?= htmlspecialchars($p['receipt_number'] ?? ('#'.$p['id'])) ?></div>
            <div><span class="b">Fecha:</span> <?= htmlspecialchars(format_datetime($p['payment_date'] ?? '')) ?></div>
            <div><span class="b">Cajero:</span> <?= htmlspecialchars($p['cashier_name'] ?? '') ?></div>
            <div class="sep"></div>
            <div><span class="b">Cliente:</span> <?= htmlspecialchars($p['customer_name'] ?? '') ?></div>
            <?php if (!empty($p['customer_address'])): ?>
            <div><?= htmlspecialchars($p['customer_address']) ?></div>
            <?php endif; ?>
            <div class="sep"></div>
            <table>
                <tbody>
                    <tr><td class="b">Factura</td><td class="right"><?= htmlspecialchars($p['invoice_number'] ?? '-') ?></td></tr>
                    <tr><td class="b">Total factura</td><td class="right"><?= format_currency($p['invoice_total'] ?? 0) ?></td></tr>
                    <tr><td class="b">Método</td><td class="right"><?= htmlspecialchars($p['method'] ?? '') ?></td></tr>
                    <tr><td class="b">Monto pagado</td><td class="right b"><?= format_currency($p['amount'] ?? 0) ?></td></tr>
                </tbody>
            </table>
            <?php if (!empty($p['note'])): ?>
            <div class="sep"></div>
            <div><span class="b">Nota:</span> <?= htmlspecialchars($p['note']) ?></div>
            <?php endif; ?>
        </body>
        </html>
        <?php
        return (string)ob_get_clean();
    }

    private function renderPaymentsReportHtml(array $rows, array $filters): string
    {
        $totalAmount = 0.0;
        foreach ($rows as $r) { $totalAmount += (float)($r['amount'] ?? 0); }

        $generatedAt = date('d/m/Y H:i');
        $filtersDesc = $this->describePaymentFilters($filters);

        $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>Informe de Cobros</title>
            <style>
                * { box-sizing: border-box; }
                body { font-family: Arial, Helvetica, sans-serif; font-size: 10px; color:#222; margin: 12px; }
                .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: 260px; 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; }
                .membrete-meta { font-size: 9px; color: #555; word-break: break-word; }
                table { width:100%; border-collapse: collapse; table-layout: fixed; }
                th, td { padding:5px 6px; border-bottom:1px solid #eee; text-align:left; font-size: 9px; vertical-align: top; word-wrap: break-word; }
                thead th { background:#f8f9fa; border-bottom:1px solid #ddd; }
                .right { text-align:right; }
            </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">Cobros</p>
                            <div class="membrete-meta"><strong>Total registros:</strong> <?= count($rows) ?></div>
                            <div class="membrete-meta"><strong>Monto total:</strong> <?= format_currency($totalAmount) ?></div>
                            <div class="membrete-meta"><strong>Generado:</strong> <?= htmlspecialchars($generatedAt) ?></div>
                            <div class="membrete-meta"><strong>Filtro:</strong> <?= htmlspecialchars($filtersDesc) ?></div>
                        </td>
                    </tr>
                </table>
            </div>

            <table>
                <thead>
                    <tr>
                        <th style="width:16%">Recibo</th>
                        <th style="width:16%">Factura</th>
                        <th>Cliente</th>
                        <th style="width:18%">Fecha</th>
                        <th class="right" style="width:14%">Monto</th>
                        <th style="width:12%">Método</th>
                        <th style="width:16%">Cajero</th>
                    </tr>
                </thead>
                <tbody>
                <?php if ($rows): foreach ($rows as $r): ?>
                    <tr>
                        <td><?= htmlspecialchars($r['receipt_number'] ?? ('#'.($r['id'] ?? ''))) ?></td>
                        <td><?= htmlspecialchars($r['invoice_number'] ?? '-') ?></td>
                        <td><?= htmlspecialchars($r['customer_name'] ?? '') ?></td>
                        <td><?= htmlspecialchars(format_datetime($r['payment_date'] ?? '')) ?></td>
                        <td class="right"><?= format_currency($r['amount'] ?? 0) ?></td>
                        <td><?= htmlspecialchars($r['method'] ?? '') ?></td>
                        <td><?= htmlspecialchars($r['cashier_name'] ?? '') ?></td>
                    </tr>
                <?php endforeach; else: ?>
                    <tr><td colspan="7" class="muted">No hay cobros para los filtros seleccionados.</td></tr>
                <?php endif; ?>
                </tbody>
                <tfoot>
                    <tr>
                        <th colspan="4" class="right">Total</th>
                        <th class="right"><?= format_currency($totalAmount) ?></th>
                        <th colspan="2"></th>
                    </tr>
                </tfoot>
            </table>
        </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