aonestar
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
sp_get_booking_folio_balances_js
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