Tul xxx Tul

Viewing: Payment.php

<?php
class Payment
{
    public int $id;
    public int $invoice_id;
    public string $date;
    public float $amount;
    public string $method;
    public string $category;

    /**
     * Get paginated list of payments with filters and joins.
     * Filters: q (receipt/invoice/customer), customer_id, invoice_id, method, category, date_from, date_to
     */
    public static function getAll(array $filters = [], int $page = 1, int $perPage = 15): array
    {
        $pdo = (new Database())->getConnection();
        $where = [];
        $params = [];

        if (!empty($filters['q'])) {
            $where[] = '(p.receipt_number LIKE :q1 OR i.invoice_number LIKE :q2 OR c.name LIKE :q3 OR c.customer_code LIKE :q4)';
            $params[':q1'] = '%' . $filters['q'] . '%';
            $params[':q2'] = '%' . $filters['q'] . '%';
            $params[':q3'] = '%' . $filters['q'] . '%';
            $params[':q4'] = '%' . $filters['q'] . '%';
        }
        if (!empty($filters['customer_id'])) {
            $where[] = 'p.customer_id = :customer_id';
            $params[':customer_id'] = (int)$filters['customer_id'];
        }
        if (!empty($filters['invoice_id'])) {
            $where[] = 'p.invoice_id = :invoice_id';
            $params[':invoice_id'] = (int)$filters['invoice_id'];
        }
        if (!empty($filters['method'])) {
            $where[] = 'p.method = :method';
            $params[':method'] = $filters['method'];
        }
        if (!empty($filters['category'])) {
            $where[] = 'p.category = :category';
            $params[':category'] = $filters['category'];
        }
        if (!empty($filters['date_from'])) {
            $where[] = 'p.payment_date >= :date_from';
            $params[':date_from'] = $filters['date_from'] . ' 00:00:00';
        }
        if (!empty($filters['date_to'])) {
            $where[] = 'p.payment_date <= :date_to';
            $params[':date_to'] = $filters['date_to'] . ' 23:59:59';
        }

        $whereSql = $where ? ('WHERE ' . implode(' AND ', $where)) : '';

        // Count
        $stmt = $pdo->prepare("SELECT COUNT(*)
                               FROM payments p
                               LEFT JOIN invoices i ON i.id = p.invoice_id
                               JOIN customers c ON c.id = p.customer_id
                               LEFT JOIN users u ON u.id = p.received_by_user_id
                               $whereSql");
        $stmt->execute($params);
        $total = (int)$stmt->fetchColumn();

        $lastPage = max(1, (int)ceil($total / max(1, $perPage)));
        $page = max(1, min($page, $lastPage));
        $offset = ($page - 1) * max(1, $perPage);

        $sql = "SELECT p.*, i.invoice_number, i.total AS invoice_total,
                       c.name AS customer_name, c.customer_code,
                       CONCAT(u.first_name, ' ', u.last_name) AS cashier_name,
                       a.name AS account_name, a.code AS account_code, a.currency AS account_currency
                FROM payments p
                LEFT JOIN invoices i ON i.id = p.invoice_id
                JOIN customers c ON c.id = p.customer_id
                LEFT JOIN users u ON u.id = p.received_by_user_id
                LEFT JOIN accounts a ON a.id = p.account_id
                $whereSql
                ORDER BY p.payment_date DESC, p.id DESC
                LIMIT :limit OFFSET :offset";
        $stmt = $pdo->prepare($sql);
        foreach ($params as $k => $v) { $stmt->bindValue($k, $v); }
        $stmt->bindValue(':limit', (int)$perPage, PDO::PARAM_INT);
        $stmt->bindValue(':offset', (int)$offset, PDO::PARAM_INT);
        $stmt->execute();
        $items = $stmt->fetchAll(PDO::FETCH_ASSOC);

        return [
            'items' => $items,
            'total' => $total,
            'page' => $page,
            'perPage' => $perPage,
            'lastPage' => $lastPage,
        ];
    }

    /**
     * Find payment with joins.
     */
    public static function findById(int $id): ?array
    {
        $pdo = (new Database())->getConnection();
        $sql = "SELECT p.*, i.invoice_number, i.total AS invoice_total, i.issue_date, i.due_date,
                       c.name AS customer_name, c.address AS customer_address,
                       CONCAT(u.first_name, ' ', u.last_name) AS cashier_name,
                       a.name AS account_name, a.code AS account_code, a.currency AS account_currency
                FROM payments p
                LEFT JOIN invoices i ON i.id = p.invoice_id
                JOIN customers c ON c.id = p.customer_id
                LEFT JOIN users u ON u.id = p.received_by_user_id
                LEFT JOIN accounts a ON a.id = p.account_id
                WHERE p.id = :id
                LIMIT 1";
        $stmt = $pdo->prepare($sql);
        $stmt->execute([':id' => $id]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ?: null;
    }

    /**
     * Create a new payment. $data keys: invoice_id (int), customer_id (int), payment_date (Y-m-d H:i:s), amount (float), method (string), category (string), received_by_user_id (int), note (string|null)
     * Returns new payment ID.
     */
    public static function create(array $data): int
    {
        $pdo = (new Database())->getConnection();
        $pdo->beginTransaction();
        try {
            $receipt = self::generateReceiptNumber($pdo, (string)$data['payment_date']);
            $stmt = $pdo->prepare("INSERT INTO payments (invoice_id, customer_id, payment_date, amount, method, category, received_by_user_id, receipt_number, note, account_id, account_transaction_id)
                                   VALUES (:invoice_id, :customer_id, :payment_date, :amount, :method, :category, :received_by_user_id, :receipt_number, :note, :account_id, NULL)");
            $stmt->execute([
                ':invoice_id' => !empty($data['invoice_id']) ? (int)$data['invoice_id'] : null,
                ':customer_id' => (int)$data['customer_id'],
                ':payment_date' => (string)$data['payment_date'],
                ':amount' => (float)$data['amount'],
                ':method' => (string)$data['method'],
                ':category' => (string)($data['category'] ?? 'Servicio'),
                ':received_by_user_id' => !empty($data['received_by_user_id']) ? (int)$data['received_by_user_id'] : null,
                ':receipt_number' => $receipt,
                ':note' => $data['note'] ?? null,
                ':account_id' => !empty($data['account_id']) ? (int)$data['account_id'] : null,
            ]);
            $id = (int)$pdo->lastInsertId();

            if (!empty($data['account_id'])) {
                $concept = $data['account_concept'] ?? ('Cobro de factura #' . ($data['invoice_number'] ?? ($data['invoice_id'] ?? '')));
                if ($concept === 'Cobro de factura #') {
                    $concept .= $id;
                }
                $reference = $data['account_reference'] ?? ($data['invoice_number'] ?? null);
                $method = $data['account_method'] ?? ($data['method'] ?? 'Efectivo');
                $counterparty = $data['account_counterparty'] ?? null;
                $transactedAt = $data['payment_date'] ?? date('Y-m-d H:i:s');
                $notes = $data['note'] ?? null;
                $userId = !empty($data['received_by_user_id']) ? (int)$data['received_by_user_id'] : null;

                $ledger = AccountTransaction::record(
                    (int)$data['account_id'],
                    'Ingreso',
                    (float)$data['amount'],
                    $concept,
                    $reference,
                    $method,
                    $counterparty,
                    $notes,
                    $userId,
                    $transactedAt,
                    $pdo,
                    $id
                );

                $update = $pdo->prepare('UPDATE payments SET account_transaction_id = :txn WHERE id = :id');
                $update->execute([
                    ':txn' => (int)$ledger['id'],
                    ':id' => $id,
                ]);
            }

            $pdo->commit();
            return $id;
        } catch (Throwable $e) {
            if ($pdo->inTransaction()) $pdo->rollBack();
            throw $e;
        }
    }

    /**
     * Generate sequential receipt number per day: RCPT-YYYYMMDD-XXXXX
     */
    private static function generateReceiptNumber(PDO $pdo, string $paymentDateTime): string
    {
        $ymd = date('Ymd', strtotime($paymentDateTime));
        $prefix = 'RCPT-' . $ymd . '-';
        $stmt = $pdo->prepare("SELECT COUNT(*) FROM payments WHERE DATE(payment_date) = :d");
        $stmt->execute([':d' => date('Y-m-d', strtotime($paymentDateTime))]);
        $seq = (int)$stmt->fetchColumn() + 1;
        return $prefix . str_pad((string)$seq, 5, '0', STR_PAD_LEFT);
    }

    /**
     * Update an existing payment's editable fields.
     */
    public static function updateById(int $id, array $data, ?PDO $pdo = null): bool
    {
        $pdo = $pdo ?: (new Database())->getConnection();
        $managed = !$pdo->inTransaction();
        if ($managed) { $pdo->beginTransaction(); }
        try {
            $fields = [
                'amount = :amount',
                'method = :method',
                'category = :category',
                'note = :note',
                'payment_date = :payment_date',
            ];
            if (isset($data['account_id'])) {
                $fields[] = 'account_id = :account_id';
            }
            $sql = 'UPDATE payments SET ' . implode(', ', $fields) . ' WHERE id = :id';
            $stmt = $pdo->prepare($sql);
            $params = [
                ':id' => $id,
                ':amount' => (float)$data['amount'],
                ':method' => (string)$data['method'],
                ':category' => (string)$data['category'],
                ':note' => $data['note'] !== '' ? $data['note'] : null,
                ':payment_date' => $data['payment_date'],
            ];
            if (isset($data['account_id'])) {
                $params[':account_id'] = (int)$data['account_id'];
            }
            $ok = $stmt->execute($params);
            if ($managed) { $pdo->commit(); }
            return $ok;
        } catch (Throwable $e) {
            if ($managed && $pdo->inTransaction()) { $pdo->rollBack(); }
            throw $e;
        }
    }

    public static function setAccountTransaction(int $id, ?int $transactionId, ?PDO $pdo = null): void
    {
        $pdo = $pdo ?: (new Database())->getConnection();
        $stmt = $pdo->prepare('UPDATE payments SET account_transaction_id = :txn WHERE id = :id');
        $stmt->execute([
            ':txn' => $transactionId !== null ? (int)$transactionId : null,
            ':id' => $id,
        ]);
    }

    public static function setAccountAndTransaction(int $id, int $accountId, ?int $transactionId, ?PDO $pdo = null): void
    {
        $pdo = $pdo ?: (new Database())->getConnection();
        $stmt = $pdo->prepare('UPDATE payments SET account_id = :account_id, account_transaction_id = :txn WHERE id = :id');
        $stmt->execute([
            ':account_id' => $accountId > 0 ? $accountId : null,
            ':txn' => $transactionId !== null ? (int)$transactionId : null,
            ':id' => $id,
        ]);
    }

    /**
     * Sum of payments registered for an invoice.
     */
    public static function sumPaidForInvoice(int $invoice_id): float
    {
        $pdo = (new Database())->getConnection();
        $stmt = $pdo->prepare('SELECT COALESCE(SUM(amount),0) FROM payments WHERE invoice_id = :id');
        $stmt->execute([':id' => $invoice_id]);
        return (float)$stmt->fetchColumn();
    }

    /**
     * Open invoices for a customer with balance.
     */
    public static function getOpenInvoicesByCustomer(int $customer_id): array
    {
        $pdo = (new Database())->getConnection();
        // Consider Pendiente, Parcial, Vencida as open
        $sql = "SELECT i.id, i.invoice_number, i.issue_date, i.due_date, i.total,
                       i.status, i.category,
                       (SELECT COALESCE(SUM(p2.amount),0) FROM payments p2 WHERE p2.invoice_id = i.id) AS paid
                FROM invoices i
                WHERE i.customer_id = :cid AND i.status IN ('Pendiente','Parcial','Vencida')
                ORDER BY i.issue_date DESC, i.id DESC";
        $stmt = $pdo->prepare($sql);
        $stmt->execute([':cid' => $customer_id]);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
        foreach ($rows as &$r) {
            $r['paid'] = (float)$r['paid'];
            $r['balance'] = max(0.0, (float)$r['total'] - (float)$r['paid']);
        }
        return $rows;
    }

    /**
     * Get raw payments within a date range (inclusive), optionally grouped later for reports.
     */
    public static function getPaymentsByDateRange(string $fromDate, string $toDate): array
    {
        $pdo = (new Database())->getConnection();
        $stmt = $pdo->prepare("SELECT p.*, i.invoice_number, c.name AS customer_name, CONCAT(u.first_name, ' ', u.last_name) AS cashier_name
                               FROM payments p
                               LEFT JOIN invoices i ON i.id = p.invoice_id
                               JOIN customers c ON c.id = p.customer_id
                               LEFT JOIN users u ON u.id = p.received_by_user_id
                               WHERE p.payment_date BETWEEN :from AND :to
                               ORDER BY p.payment_date ASC, p.id ASC");
        $stmt->execute([
            ':from' => $fromDate . ' 00:00:00',
            ':to' => $toDate . ' 23:59:59',
        ]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    /**
     * Retrieve payments de un cajero dentro de un rango que aún no se han asociado a ningún cierre.
     */
    public static function getUnclosedByCashier(int $cashierId, string $fromDateTime, string $toDateTime): array
    {
        $pdo = (new Database())->getConnection();
        $sql = "SELECT p.*, c.name AS customer_name
                FROM payments p
                LEFT JOIN customers c ON c.id = p.customer_id
                WHERE p.received_by_user_id = :uid
                  AND p.payment_date BETWEEN :from AND :to
                  AND (p.cash_closure_id IS NULL)
                ORDER BY p.payment_date ASC, p.id ASC";
        $stmt = $pdo->prepare($sql);
        $stmt->execute([
            ':uid' => $cashierId,
            ':from' => $fromDateTime,
            ':to' => $toDateTime,
        ]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }
    /**
     * Devuelve sumatoria y conteo de pagos sin cierre para un cajero.
     */
    public static function summarizeUnclosedByCashier(int $cashierId, string $fromDateTime, string $toDateTime): array
    {
        $pdo = (new Database())->getConnection();
        $sql = "SELECT COALESCE(SUM(p.amount),0) AS total_amount,
                       COUNT(*) AS movements
                FROM payments p
                WHERE p.received_by_user_id = :uid
                  AND p.payment_date BETWEEN :from AND :to
                  AND (p.cash_closure_id IS NULL)";
        $stmt = $pdo->prepare($sql);
        $stmt->execute([
            ':uid' => $cashierId,
            ':from' => $fromDateTime,
            ':to' => $toDateTime,
        ]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC) ?: [];
        return [
            'total_amount' => (float)($row['total_amount'] ?? 0.0),
            'movements' => (int)($row['movements'] ?? 0),
        ];
    }

    /**
     * Marca pagos dentro de un rango como parte de un cierre de caja.
     */
    public static function assignClosureInRange(int $cashierId, string $fromDateTime, string $toDateTime, int $closureId, ?PDO $pdo = null): int
    {
        $pdo = $pdo ?: (new Database())->getConnection();
        $stmt = $pdo->prepare("UPDATE payments
                               SET cash_closure_id = :closure
                               WHERE received_by_user_id = :uid
                                 AND payment_date BETWEEN :from AND :to
                                 AND (cash_closure_id IS NULL)");
        $stmt->execute([
            ':closure' => $closureId,
            ':uid' => $cashierId,
            ':from' => $fromDateTime,
            ':to' => $toDateTime,
        ]);
        return (int)$stmt->rowCount();
    }

    /**
     * Libera los pagos asociados a un cierre (útil para reaperturas).
     */
    public static function releaseClosure(int $closureId, ?PDO $pdo = null): int
    {
        $pdo = $pdo ?: (new Database())->getConnection();
        $stmt = $pdo->prepare("UPDATE payments SET cash_closure_id = NULL WHERE cash_closure_id = :closure");
        $stmt->execute([':closure' => $closureId]);
        return (int)$stmt->rowCount();
    }

    /**
     * Pagos asociados a un cierre específico.
     */
    public static function getByClosure(int $closureId): array
    {
        $pdo = (new Database())->getConnection();
        $sql = "SELECT p.*, c.name AS customer_name
                FROM payments p
                LEFT JOIN customers c ON c.id = p.customer_id
                WHERE p.cash_closure_id = :closure
                ORDER BY p.payment_date ASC, p.id ASC";
        $stmt = $pdo->prepare($sql);
        $stmt->execute([':closure' => $closureId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }
}
User / IP	: 216.73.217.21
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