aonestar.public

Parameters

Name	Type	Mode
booking_id	integer	IN (DEFAULT NULL)
register_id	integer	IN (DEFAULT NULL)

Definition

WITH booking_folio AS (
        SELECT f.id AS folio_id, NULL::int AS register_id, 'Booking' AS room_number, b.booking_name AS name, b.arrival, b.departure, b.status,   
            COALESCE(f1.balance,0.0) as folio_balance1, 
            COALESCE(f2.balance,0.0) as folio_balance2, 
            COALESCE(f3.balance,0.0) as folio_balance3, 
            COALESCE(f4.balance,0.0) as folio_balance4,
            COALESCE(f1.balance, 0.0) + COALESCE(f2.balance, 0.0) + COALESCE(f3.balance, 0.0) + COALESCE(f4.balance, 0.0) AS total_balance,
            (b.status = 'I' AND COALESCE(f1.balance,0.0) = 0 AND COALESCE(f2.balance,0.0) = 0 AND COALESCE(f3.balance,0.0) = 0 AND COALESCE(f4.balance,0.0) = 0) AS checkout_available
        FROM booking b
            LEFT JOIN folio f  ON b.id = f.booking_id
            LEFT JOIN folio_items f1 ON f1.folio_id = f.id AND f1.folio_seq = 1 AND NOT f1.closed 
            LEFT JOIN folio_items f2 ON f2.folio_id = f.id AND f2.folio_seq = 2 AND NOT f2.closed 
            LEFT JOIN folio_items f3 ON f3.folio_id = f.id AND f3.folio_seq = 3 AND NOT f3.closed 
            LEFT JOIN folio_items f4 ON f4.folio_id = f.id AND f4.folio_seq = 4 AND NOT f4.closed 
        WHERE $1 IS NOT NULL AND f.booking_id = $1
    ), guest_folios AS (
        SELECT f.id AS folio_id, rg.id AS register_id, rm.room_number, g.full_name AS name, rg.arrival, rg.departure, rg.status,   
            COALESCE(f1.balance,0.0) as folio_balance1, 
            COALESCE(f2.balance,0.0) as folio_balance2, 
            COALESCE(f3.balance,0.0) as folio_balance3, 
            COALESCE(f4.balance,0.0) as folio_balance4,
            COALESCE(f1.balance, 0.0) + COALESCE(f2.balance, 0.0) + COALESCE(f3.balance, 0.0) + COALESCE(f4.balance, 0.0) AS total_balance,
            (rg.status = 'I' AND COALESCE(f1.balance,0.0) = 0 AND COALESCE(f2.balance,0.0) = 0 AND COALESCE(f3.balance,0.0) = 0 AND COALESCE(f4.balance,0.0) = 0) AS checkout_available
        FROM registration rg 
            LEFT JOIN folio f ON rg.id = f.register_id 
            LEFT JOIN room rm ON rm.id = rg.room_id 
            LEFT JOIN guest g ON g.id = rg.guest_id 
            LEFT JOIN folio_items f1 ON f1.folio_id = f.id AND f1.folio_seq = 1 AND NOT f1.closed 
            LEFT JOIN folio_items f2 ON f2.folio_id = f.id AND f2.folio_seq = 2 AND NOT f2.closed 
            LEFT JOIN folio_items f3 ON f3.folio_id = f.id AND f3.folio_seq = 3 AND NOT f3.closed 
            LEFT JOIN folio_items f4 ON f4.folio_id = f.id AND f4.folio_seq = 4 AND NOT f4.closed 
         WHERE ($2 IS NOT NULL AND rg.id = $2) OR ($2 IS NULL AND rg.booking_id = $1)
         ORDER BY 1
    ), all_folios AS (
        SELECT *, checkout_available AND (departure = fn_system_date()) AS default_selected FROM booking_folio
        UNION ALL 
        SELECT *, checkout_available AND (departure = fn_system_date()) AS default_selected FROM guest_folios
    )
    SELECT json_agg(all_folios) FROM all_folios

sp_get_booking_folio_balances_js

Parameters

Definition