<?php
    date_default_timezone_set('Asia/Bangkok');
class ReservationDelete {

    private $conn;
    private $table_name = "booking_delete"; 

    public $id;
    public $date_time;
    public $reservation_no;
    public $folio_no;
    public $agent_id;
    public $agent_name;
    public $guest_id;
    public $guest_name; 
    public $remark; 
    public $adult;
    public $child;
    public $room_type;
    public $bed_type;
    public $room_no;
    public $entry_by; 
    public $update_by; 
    public $created_at; 
    public $updated_at; 
    public $checkin; 
    public $checkin_created_at; 
    public $checkout; 
    public $checkout_created_at; 

    public $df; 
    public $dt; 
    public $status; 
    public $balance;
    // public $extra_bed; 
    // public $extra_person; 

    // public $old_room_no; 



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


    function getReservationList() {
        $query = "SELECT r.id, r.booking_no AS reservation_no, r.folio_no, agent_name,
            customer_name AS guest_name, drivers AS adult, passengers AS child, vehicle_no AS room_no,
            date_time, MIN(date) AS checkin_date, DATE_ADD(MAX(date), INTERVAL 1 DAY) AS checkout_date,
            COUNT(rd.date) AS nights,
            SUM(rental_rate + (extra_driver * extra_driver_rate) + (extra_passenger * extra_passenger_rate)) AS total,
            IFNULL(r.discount_amount, 0) AS discount_amount,
            GREATEST(SUM(rental_rate + (extra_driver * extra_driver_rate) + (extra_passenger * extra_passenger_rate)) - IFNULL(r.discount_amount, 0), 0) AS net_amount,
            IFNULL(deposit, 0) AS deposit, remark
            FROM booking_delete r
            LEFT JOIN booking_detail_delete rd ON r.booking_no = rd.booking_no
            LEFT JOIN (SELECT booking_no, SUM(deposit_amount) AS deposit FROM deposit GROUP BY booking_no) AS d ON r.booking_no = d.booking_no
            WHERE picked_up = 0 AND returned = 0
            GROUP BY r.booking_no
            ORDER BY checkin_date, checkout_date";
    
        $stmt = $this->conn->prepare($query);
        $stmt->execute();
        return $stmt;
    }

    function getReservationListDetail(){
        $query = "SELECT rd.booking_no AS reservation_no, rd.date,
            rd.rental_rate AS room_rate,
            rd.extra_driver AS extra_bed,
            rd.extra_driver_rate AS extra_bed_rate,
            rd.extra_passenger AS extra_person,
            rd.extra_passenger_rate AS extra_person_rate,
            r.vehicle_no AS room_no
            FROM booking_detail_delete rd
            LEFT JOIN booking_delete r ON rd.booking_no = r.booking_no
            WHERE picked_up = 0 AND returned = 0";

        $stmt = $this->conn->prepare( $query );
        $stmt->execute();
        return $stmt;
    }
  
}
?>
