Tul xxx Tul

Viewing: Expense.php

<?php
class Expense
{
    /**
     * Get paginated list of expenses with filters and joins.
     * Filters: q (vendor/ref/description), category_id, subcategory_id, method, 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[] = '(e.vendor LIKE :q1 OR e.ref_number LIKE :q2 OR e.description LIKE :q3)';
            $params[':q1'] = '%' . $filters['q'] . '%';
            $params[':q2'] = '%' . $filters['q'] . '%';
            $params[':q3'] = '%' . $filters['q'] . '%';
        }
        if (!empty($filters['category_id'])) {
            $where[] = 'e.category_id = :cat';
            $params[':cat'] = (int)$filters['category_id'];
        }
        if (!empty($filters['subcategory_id'])) {
            $where[] = 'e.subcategory_id = :subcat';
            $params[':subcat'] = (int)$filters['subcategory_id'];
        }
        if (!empty($filters['method'])) {
            $where[] = 'e.method = :method';
            $params[':method'] = $filters['method'];
        }
        if (!empty($filters['account_id'])) {
            $where[] = 'e.account_id = :account_id';
            $params[':account_id'] = (int)$filters['account_id'];
        }
        if (!empty($filters['date_from'])) {
            $where[] = 'e.expense_date >= :date_from';
            $params[':date_from'] = $filters['date_from'] . ' 00:00:00';
        }
        if (!empty($filters['date_to'])) {
            $where[] = 'e.expense_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 expenses e
                               LEFT JOIN expense_categories c ON c.id = e.category_id
                               LEFT JOIN expense_subcategories s ON s.id = e.subcategory_id
                               LEFT JOIN users u ON u.id = e.created_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 e.*, c.name AS category_name, s.name AS subcategory_name,
                       CONCAT(u.first_name,' ',u.last_name) AS creator_name,
                       a.name AS account_name, a.code AS account_code, a.currency AS account_currency
                FROM expenses e
                LEFT JOIN expense_categories c ON c.id = e.category_id
                LEFT JOIN expense_subcategories s ON s.id = e.subcategory_id
                LEFT JOIN users u ON u.id = e.created_by_user_id
                LEFT JOIN accounts a ON a.id = e.account_id
                $whereSql
                ORDER BY e.expense_date DESC, e.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,
        ];
    }

    public static function findById(int $id): ?array
    {
        $pdo = (new Database())->getConnection();
        $sql = "SELECT e.*, c.name AS category_name, s.name AS subcategory_name,
                       CONCAT(u.first_name,' ',u.last_name) AS creator_name,
                       a.name AS account_name, a.code AS account_code, a.currency AS account_currency
                FROM expenses e
                LEFT JOIN expense_categories c ON c.id = e.category_id
                LEFT JOIN expense_subcategories s ON s.id = e.subcategory_id
                LEFT JOIN users u ON u.id = e.created_by_user_id
                LEFT JOIN accounts a ON a.id = e.account_id
                WHERE e.id = :id LIMIT 1";
        $stmt = $pdo->prepare($sql);
        $stmt->execute([':id' => $id]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ?: null;
    }

    public static function create(array $data, ?PDO $pdo = null): int
    {
        $pdo = $pdo ?: (new Database())->getConnection();
        $stmt = $pdo->prepare("INSERT INTO expenses (expense_date, category_id, subcategory_id, amount, method, account_id, vendor, ref_number, description, created_by_user_id, account_transaction_id)
                               VALUES (:expense_date, :category_id, :subcategory_id, :amount, :method, :account_id, :vendor, :ref_number, :description, :created_by_user_id, NULL)");
        $stmt->execute([
            ':expense_date' => (string)$data['expense_date'],
            ':category_id' => (int)$data['category_id'],
            ':subcategory_id' => !empty($data['subcategory_id']) ? (int)$data['subcategory_id'] : null,
            ':amount' => (float)$data['amount'],
            ':method' => (string)$data['method'],
            ':account_id' => !empty($data['account_id']) ? (int)$data['account_id'] : null,
            ':vendor' => $data['vendor'] !== '' ? (string)$data['vendor'] : null,
            ':ref_number' => $data['ref_number'] !== '' ? (string)$data['ref_number'] : null,
            ':description' => $data['description'] !== '' ? (string)$data['description'] : null,
            ':created_by_user_id' => !empty($data['created_by_user_id']) ? (int)$data['created_by_user_id'] : null,
        ]);
        return (int)$pdo->lastInsertId();
    }

    public static function update(int $id, array $data, ?PDO $pdo = null): bool
    {
        $pdo = $pdo ?: (new Database())->getConnection();
        $stmt = $pdo->prepare("UPDATE expenses
                               SET expense_date = :expense_date, category_id = :category_id, subcategory_id = :subcategory_id,
                                   amount = :amount, method = :method, account_id = :account_id, vendor = :vendor, ref_number = :ref_number,
                                   description = :description
                               WHERE id = :id");
        return $stmt->execute([
            ':expense_date' => (string)$data['expense_date'],
            ':category_id' => (int)$data['category_id'],
            ':subcategory_id' => !empty($data['subcategory_id']) ? (int)$data['subcategory_id'] : null,
            ':amount' => (float)$data['amount'],
            ':method' => (string)$data['method'],
            ':account_id' => !empty($data['account_id']) ? (int)$data['account_id'] : null,
            ':vendor' => $data['vendor'] !== '' ? (string)$data['vendor'] : null,
            ':ref_number' => $data['ref_number'] !== '' ? (string)$data['ref_number'] : null,
            ':description' => $data['description'] !== '' ? (string)$data['description'] : null,
            ':id' => $id,
        ]);
    }

    public static function delete(int $id, ?PDO $pdo = null): bool
    {
        $pdo = $pdo ?: (new Database())->getConnection();
        $stmt = $pdo->prepare('DELETE FROM expenses WHERE id = :id');
        $stmt->execute([':id' => $id]);
        return $stmt->rowCount() > 0;
    }

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

    /**
     * Get raw expenses within a date range (inclusive), ordered ascending.
     */
    public static function getExpensesByDateRange(string $fromDate, string $toDate): array
    {
        $pdo = (new Database())->getConnection();
        $stmt = $pdo->prepare("SELECT e.*, c.name AS category_name, s.name AS subcategory_name,
                                      CONCAT(u.first_name,' ',u.last_name) AS creator_name
                                 FROM expenses e
                                 LEFT JOIN expense_categories c ON c.id = e.category_id
                                 LEFT JOIN expense_subcategories s ON s.id = e.subcategory_id
                                 LEFT JOIN users u ON u.id = e.created_by_user_id
                                WHERE e.expense_date BETWEEN :from AND :to
                             ORDER BY e.expense_date ASC, e.id ASC");
        $stmt->execute([
            ':from' => $fromDate . ' 00:00:00',
            ':to' => $toDate . ' 23:59:59',
        ]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    /**
     * Allowed payment methods for expenses
     */
    public static function getMethods(): array
    {
        return ['Efectivo','Transferencia','POS','Cheque','Otro'];
    }

    /**
     * Retrieve expenses registrados por un usuario en un rango que no tienen cierre asociado.
     */
    public static function getUnclosedByUser(int $userId, string $fromDateTime, string $toDateTime): array
    {
        $pdo = (new Database())->getConnection();
        $sql = "SELECT e.*, c.name AS category_name, s.name AS subcategory_name
                FROM expenses e
                LEFT JOIN expense_categories c ON c.id = e.category_id
                LEFT JOIN expense_subcategories s ON s.id = e.subcategory_id
                WHERE e.created_by_user_id = :uid
                  AND e.expense_date BETWEEN :from AND :to
                  AND (e.cash_closure_id IS NULL)
                ORDER BY e.expense_date ASC, e.id ASC";
        $stmt = $pdo->prepare($sql);
        $stmt->execute([
            ':uid' => $userId,
            ':from' => $fromDateTime,
            ':to' => $toDateTime,
        ]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }

    /**
     * Sumatoria y conteo de egresos sin cierre para un usuario.
     */
    public static function summarizeUnclosedByUser(int $userId, string $fromDateTime, string $toDateTime): array
    {
        $pdo = (new Database())->getConnection();
        $sql = "SELECT COALESCE(SUM(e.amount),0) AS total_amount,
                       COUNT(*) AS movements
                FROM expenses e
                WHERE e.created_by_user_id = :uid
                  AND e.expense_date BETWEEN :from AND :to
                  AND (e.cash_closure_id IS NULL)";
        $stmt = $pdo->prepare($sql);
        $stmt->execute([
            ':uid' => $userId,
            ':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 egresos dentro de un rango como parte de un cierre de caja.
     */
    public static function assignClosureInRange(int $userId, string $fromDateTime, string $toDateTime, int $closureId, ?PDO $pdo = null): int
    {
        $pdo = $pdo ?: (new Database())->getConnection();
        $stmt = $pdo->prepare("UPDATE expenses
                               SET cash_closure_id = :closure
                               WHERE created_by_user_id = :uid
                                 AND expense_date BETWEEN :from AND :to
                                 AND (cash_closure_id IS NULL)");
        $stmt->execute([
            ':closure' => $closureId,
            ':uid' => $userId,
            ':from' => $fromDateTime,
            ':to' => $toDateTime,
        ]);
        return (int)$stmt->rowCount();
    }

    /**
     * Libera egresos asociados a un cierre específico.
     */
    public static function releaseClosure(int $closureId, ?PDO $pdo = null): int
    {
        $pdo = $pdo ?: (new Database())->getConnection();
        $stmt = $pdo->prepare("UPDATE expenses SET cash_closure_id = NULL WHERE cash_closure_id = :closure");
        $stmt->execute([':closure' => $closureId]);
        return (int)$stmt->rowCount();
    }

    /**
     * Obtiene los egresos etiquetados a un cierre.
     */
    public static function getByClosure(int $closureId): array
    {
        $pdo = (new Database())->getConnection();
        $sql = "SELECT e.*, c.name AS category_name, s.name AS subcategory_name
                FROM expenses e
                LEFT JOIN expense_categories c ON c.id = e.category_id
                LEFT JOIN expense_subcategories s ON s.id = e.subcategory_id
                WHERE e.cash_closure_id = :closure
                ORDER BY e.expense_date ASC, e.id ASC";
        $stmt = $pdo->prepare($sql);
        $stmt->execute([':closure' => $closureId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }
}
User / IP	: 216.73.216.183
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