aonestar
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
sp_get_charge_schedule1_js
Parameters
Name
Type
Mode
folio_id
integer
IN
Definition
WITH fol AS ( SELECT IIF(folio_type = 'R', register_id, NULL) AS register_id, IIF(folio_type = 'B', booking_id, NULL) AS booking_id FROM folio WHERE id = $1 ), schedule AS ( SELECT charge_date AS "date", rm.room_number, charge_type, dp."name" AS department, charge_qty AS qty, charge_amount AS amount, total_amount, charge_to_booking, posted FROM fol, LATERAL sp_get_charge_schedule(register_id => fol.register_id, booking_id => fol.booking_id) AS cs LEFT JOIN department dp ON dp.id = cs.charge_dept LEFT JOIN room rm ON rm.id = cs.room_id ), rooms AS ( SELECT "date", room_number, department, qty, amount, total_amount, charge_to_booking, posted FROM schedule cs WHERE charge_type = 'room' ORDER BY 1,2 ), inclusions AS ( SELECT "date", room_number, department, qty, amount, total_amount, charge_to_booking, posted FROM schedule cs WHERE charge_type = 'inclusion' ORDER BY 1,2 ), other AS ( SELECT charge_date AS "date", rm.room_number, dp."name" AS department, charge_qty AS qty, charge_amount AS amount, total_amount, cs.charge_to_booking, posted FROM fol, charge_schedule cs LEFT JOIN department dp ON dp.id = cs.charge_dept LEFT JOIN registration rg ON rg.id = cs.register_id LEFT JOIN room rm ON rm.id = rg.room_id WHERE cs.register_id = fol.register_id OR cs.booking_id = fol.booking_id ORDER BY 1,2 ) SELECT json_build_object( 'room_charges', (SELECT COALESCE(json_agg(rooms), '[]') FROM rooms), 'inclusion_charges', (SELECT COALESCE(json_agg(inclusions), '[]') FROM inclusions), 'other_charges', (SELECT COALESCE(json_agg(other), '[]') FROM other) )