Tul xxx Tul

Viewing: Reading.php

<?php
class Reading
{
    // Schema reference:
    // id, meter_id, customer_id, period_start, period_end, reading_date,
    // previous_reading, reading_value, consumption_m3, reader_user_id, voucher_printed, notes

    public int $id;
    public int $meter_id;
    public int $customer_id;
    public string $period_start;
    public string $period_end;
    public string $reading_date;
    public float $previous_reading;
    public float $reading_value;
    public float $consumption_m3;
    public int $reader_user_id;
    public int $voucher_printed;
    public string $notes;

    public static function getAll(array $filters = [], int $page = 1, int $perPage = 15): array
    {
        $pdo = (new Database())->getConnection();
        $where = [];
        $params = [];

        if (!empty($filters['q'])) {
            // Evitar reutilizar el mismo marcador nombrado dos veces (causa HY093)
            $where[] = "(c.name LIKE :q1 OR c.customer_code LIKE :q3 OR m.number LIKE :q2)";
            $params[':q1'] = '%' . $filters['q'] . '%';
            $params[':q2'] = '%' . $filters['q'] . '%';
            $params[':q3'] = '%' . $filters['q'] . '%';
        }
        if (!empty($filters['customer_id'])) {
            $where[] = 'r.customer_id = :customer_id';
            $params[':customer_id'] = (int)$filters['customer_id'];
        }
        if (!empty($filters['meter_id'])) {
            $where[] = 'r.meter_id = :meter_id';
            $params[':meter_id'] = (int)$filters['meter_id'];
        }
        if (!empty($filters['date_from'])) {
            $where[] = 'r.reading_date >= :date_from';
            $params[':date_from'] = $filters['date_from'];
        }
        if (!empty($filters['date_to'])) {
            $where[] = 'r.reading_date <= :date_to';
            $params[':date_to'] = $filters['date_to'];
        }

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

        $countSql = "SELECT COUNT(*)
                     FROM readings r
                     LEFT JOIN customers c ON c.id = r.customer_id
                     LEFT JOIN meters m ON m.id = r.meter_id
                     LEFT JOIN users u ON u.id = r.reader_user_id
                     $whereSql";
        $stmt = $pdo->prepare($countSql);
        $stmt->execute($params);
        $total = (int)$stmt->fetchColumn();

        $offset = max(0, ($page - 1) * $perPage);
        $sql = "SELECT r.*, c.name AS customer_name, m.number AS meter_number,
                        TRIM(CONCAT(COALESCE(u.first_name,''),' ',COALESCE(u.last_name,''))) AS reader_name
                FROM readings r
                LEFT JOIN customers c ON c.id = r.customer_id
                LEFT JOIN meters m ON m.id = r.meter_id
                LEFT JOIN users u ON u.id = r.reader_user_id
                $whereSql
                ORDER BY r.reading_date DESC, r.id DESC
                LIMIT :limit OFFSET :offset";
        $stmt = $pdo->prepare($sql);
        foreach ($params as $k => $v) {
            $stmt->bindValue($k, $v);
        }
        $stmt->bindValue(':limit', $perPage, PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
        $stmt->execute();
        $rows = $stmt->fetchAll();

        return ['data' => $rows, 'total' => $total, 'page' => $page, 'perPage' => $perPage];
    }

    public static function findById(int $id): ?array
    {
        $pdo = (new Database())->getConnection();
        $sql = "SELECT r.*, c.name AS customer_name, m.number AS meter_number,
                       TRIM(CONCAT(COALESCE(u.first_name,''),' ',COALESCE(u.last_name,''))) AS reader_name
                FROM readings r
                LEFT JOIN customers c ON c.id = r.customer_id
                LEFT JOIN meters m ON m.id = r.meter_id
                LEFT JOIN users u ON u.id = r.reader_user_id
                WHERE r.id = :id LIMIT 1";
        $stmt = $pdo->prepare($sql);
        $stmt->execute([':id' => $id]);
        $row = $stmt->fetch();
        return $row ?: null;
    }

    public static function getLastByMeter(int $meterId): ?array
    {
        $pdo = (new Database())->getConnection();
        $stmt = $pdo->prepare("SELECT * FROM readings WHERE meter_id = :id ORDER BY reading_date DESC, id DESC LIMIT 1");
        $stmt->execute([':id' => $meterId]);
        $row = $stmt->fetch();
        return $row ?: null;
    }

    public static function create(array $data): int
    {
        $pdo = (new Database())->getConnection();
        $sql = "INSERT INTO readings
                (meter_id, customer_id, period_start, period_end, reading_date,
                 previous_reading, reading_value, reader_user_id, voucher_printed, notes)
                VALUES
                (:meter_id, :customer_id, :period_start, :period_end, :reading_date,
                 :previous_reading, :reading_value, :reader_user_id, :voucher_printed, :notes)";
        $stmt = $pdo->prepare($sql);
        $stmt->execute([
            ':meter_id' => (int)$data['meter_id'],
            ':customer_id' => (int)$data['customer_id'],
            ':period_start' => $data['period_start'],
            ':period_end' => $data['period_end'],
            ':reading_date' => $data['reading_date'],
            ':previous_reading' => (float)$data['previous_reading'],
            ':reading_value' => (float)$data['reading_value'],
            ':reader_user_id' => (int)$data['reader_user_id'],
            ':voucher_printed' => !empty($data['voucher_printed']) ? 1 : 0,
            ':notes' => $data['notes'] ?? null,
        ]);
        return (int)$pdo->lastInsertId();
    }

    public static function update(int $id, array $data): bool
    {
        $pdo = (new Database())->getConnection();
        $sql = "UPDATE readings SET
                    meter_id = :meter_id,
                    customer_id = :customer_id,
                    period_start = :period_start,
                    period_end = :period_end,
                    reading_date = :reading_date,
                    previous_reading = :previous_reading,
                    reading_value = :reading_value,
                    reader_user_id = :reader_user_id,
                    voucher_printed = :voucher_printed,
                    notes = :notes
                WHERE id = :id";
        $stmt = $pdo->prepare($sql);
        return $stmt->execute([
            ':meter_id' => (int)$data['meter_id'],
            ':customer_id' => (int)$data['customer_id'],
            ':period_start' => $data['period_start'],
            ':period_end' => $data['period_end'],
            ':reading_date' => $data['reading_date'],
            ':previous_reading' => (float)$data['previous_reading'],
            ':reading_value' => (float)$data['reading_value'],
            ':reader_user_id' => (int)$data['reader_user_id'],
            ':voucher_printed' => !empty($data['voucher_printed']) ? 1 : 0,
            ':notes' => $data['notes'] ?? null,
            ':id' => $id,
        ]);
    }

    public static function delete(int $id): bool
    {
        $pdo = (new Database())->getConnection();
        $stmt = $pdo->prepare("DELETE FROM readings WHERE id = :id");
        return $stmt->execute([':id' => $id]);
    }
}
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