manjapro_v6/paymentlink/services/Invoice.php

<?php

class Invoice
{
    private $pdo;

    public function __construct($pdo)
    {
        $this->pdo = $pdo;
    }

    /**
     * Tagihan belum dibayar
     */
    public function getUnpaid($id_pelanggan)
    {
        $sql = "
            SELECT id, limit_pembayaran, keterangan, verifikasi_tagihan, keterangan, status, nominal_tagihan, jumlah_konpensasi, jumlah_tagihan, ppn, total_tagihan, kode_unik, transaction_id
            FROM tagihan
            WHERE id_pelanggan = :id_pelanggan
            AND limit_pembayaran >= DATE_FORMAT(CURDATE(), '%Y-%m-01')
            AND status IN ('pending', 'pending0', 'pending1', 'pending2', 'lewat')
            ORDER BY id DESC 
            LIMIT 1
        ";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(['id_pelanggan' => $id_pelanggan]);

        return $stmt->fetch();
    }

    public function getPaymentPending($id_tagihan)
    {
        $sql = "
            SELECT trx_id, method, channel, amount, va_number, qr_string, qr_url, payment_url, status, expired_at, response_callback, paid_at, created_at, gateway_type
            FROM payment_winpay
            WHERE id_tagihan = :id_tagihan
            AND status = 'PENDING'
            ORDER BY id DESC
        ";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(['id_tagihan' => $id_tagihan]);

        return $stmt->fetch();
    }

    /**
     * Total tagihan belum bayar
     */
    public function getTotalUnpaid($id_pelanggan)
    {
        $sql = "
            SELECT verifikasi_tagihan
            FROM tagihan
            WHERE id_pelanggan = :id_pelanggan
            AND status IN ('pending', 'pending0', 'pending1', 'pending2', 'lewat')
            ORDER BY id DESC 
            LIMIT 1
        ";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(['id_pelanggan' => $id_pelanggan]);

        $result = $stmt->fetch();
        return $result['verifikasi_tagihan'] ?? 0;
    }

    /**
     * Riwayat pembayaran (3 terakhir)
     */
    public function getLastPaid($id_pelanggan)
    {
        $sql = "
            SELECT limit_pembayaran, verifikasi_tagihan, tanggal_bayar, keterangan, status, nominal_tagihan, jumlah_konpensasi, jumlah_tagihan, ppn, total_tagihan, kode_unik
            FROM tagihan
            WHERE id_pelanggan = :id_pelanggan
            AND status = 'lunas'
            ORDER BY tanggal_bayar DESC
            LIMIT 3
        ";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(['id_pelanggan' => $id_pelanggan]);

        return $stmt->fetchAll();
    }

    /**
     * Ambil invoice berdasarkan ID (dipilih user)
     */
    public function getByIds($ids, $id_pelanggan)
    {
        if (empty($ids)) return [];

        // sanitasi integer
        $ids = array_map('intval', $ids);
        $in  = implode(',', $ids);

        $sql = "
            SELECT id, limit_pembayaran, total
            FROM tagihan
            WHERE id IN ($in)
            AND id_pelanggan = :id_pelanggan
            AND status IN ('pending', 'pending0', 'pending1', 'pending2', 'lewat')
        ";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(['id_pelanggan' => $id_pelanggan]);

        return $stmt->fetchAll();
    }

    /**
    * Total tagihan belum bayar
    */
    public function getPayment($trx_id, $id_pelanggan)
    {
        $sql = "
            SELECT trx_id, method, channel, amount, va_number, qr_string, qr_url, payment_url, status, expired_at, response_callback, paid_at, created_at
            FROM payment_winpay
            WHERE trx_id = :trx_id
            AND id_pelanggan = :id_pelanggan
        ";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(['trx_id' => $trx_id, 'id_pelanggan' => $id_pelanggan]);

        $result = $stmt->fetch();
        return $result;
    }

    public function getPaymentStatus($trx_id)
    {
        $sql = "
            SELECT status
            FROM payment_winpay
            WHERE trx_id = :trx_id
        ";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(['trx_id' => $trx_id]);

        $result = $stmt->fetch();
        return $result;
    }

    /**
     * =========================
     * HANDLE ERROR DARI GATEWAY
     * =========================
     */

    

    /**
     * Tandai invoice sebagai paid
     */
    // public function markAsPaid($invoice_ids)
    // {
    //     if (empty($invoice_ids)) return false;

    //     $ids = array_map('intval', $invoice_ids);
    //     $in  = implode(',', $ids);

    //     $sql = "
    //         UPDATE tagihan
    //         SET status = 'lunas',
    //             paid_at = NOW()
    //         WHERE id IN ($in)
    //     ";

    //     return $this->pdo->exec($sql);
    // }
}