aonestar
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
sp_get_auto_post_rooms_js
Parameters
Name
Type
Mode
Definition
with individual as ( select s.register_id, rm.room_number, rg.booking_id, g.full_name as name, s.rate_amount as room_rate, s.room as room_charge, s.extrabed, s.abf, s.lunch, s.dinner, s.inclusive as inclusive_charge, s.other as other_charge, s.room + s.extrabed + s.abf + s.lunch + s.dinner + s.inclusive + s.other as total, IIF(s.room_posted, 'posted', 'unposted') as status, s.room_posted as posted, s.charge_to_booking from sp_rate_schedule_breakdown(fn_system_date(), posted := null, charge_to_booking := false) s left join registration rg on rg.id = s.register_id LEFT JOIN room rm ON rm.id = s.room_id left join guest g on g.id = rg.guest_id order by rm.room_number, s.register_id ) select json_build_object( 'ChargeToBooking', '[]'::json, --(select jsonb_agg(chg_to_book) from chg_to_book), 'Individual', coalesce((select json_agg(individual) from individual), '[]'::json) ); -- with charges as ( -- SELECT * -- FROM sp_get_charge_schedule(post_date => fn_system_date(), posted => null) cs -- ), summ as ( -- select register_id, room_id, booking_id, posted, charge_to_booking, -- sum(iif(charge_type='room' , total_amount, 0)) as room_charge, -- sum(iif(charge_type='ABF' , total_amount, 0)) as abf, -- sum(iif(charge_type='Lunch' , total_amount, 0)) as lunch, -- sum(iif(charge_type='Dinner' , total_amount, 0)) as dinner, -- sum(iif(charge_type='inclusive', total_amount, 0)) as inclusive_charge -- from charges -- group by 1,2,3,4,5 -- ), chg_to_own as ( -- select s.register_id, rm.room_number, s.booking_id, g.full_name as name, -- s.room_charge, 0 as extra_adult, 0 as extra_child, 0 as extra_infant, -- s.abf, s.lunch, s.dinner, s.inclusive_charge, -- s.room_charge + s.abf + s.lunch + s.dinner + s.inclusive_charge as total, -- IIF(s.posted, 'posted', 'unposted') as status, -- s.posted, s.charge_to_booking -- from summ s -- left join registration rg on rg.id = s.register_id -- LEFT JOIN room rm ON rm.id = s.room_id -- left join guest g on g.id = rg.guest_id -- order by rm.room_number, s.register_id -- ) -- select json_build_object( -- 'ChargeToBooking', '[]'::json, --(select jsonb_agg(chg_to_book) from chg_to_book), -- 'Individual', (select json_agg(chg_to_own) from chg_to_own) -- );