aonestar
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
sp_get_guest_folio_pattern_json
Parameters
Name
Type
Mode
p_folio_id
integer
IN
Definition
declare v_result jsonb; begin -- Create by T.Supol 7-Nov-2024 -- with department_json as ( -- select -- fi.folio_id, -- fi.folio_seq, -- json_agg( -- json_build_object( -- 'id', d.id, -- 'code', d.code, -- 'name', d.name -- ) -- )::jsonb as departments -- from folio_items fi -- left join folio_details fd on fd.folio_id = fi.folio_id and fd.folio_seq = fi.folio_seq -- left join department d on d.id = fd.dept_id -- where fi.closed = false -- group by fi.folio_id, fi.folio_seq -- ) -- select into v_result ( -- jsonb_build_object( -- 'folio_id', f.id, -- 'folio_pattern_id', fp.id, -- 'folio_pattern_name', fp.name, -- 'register_id', f.register_id, -- 'room_number', r2.room_number, -- 'split_room_charge', f.split_room_charge, -- 'folio1', ( -- select jsonb_build_object( -- 'item_id', fi.item_id, -- 'folio_seq', fi.folio_seq, -- 'folio_name', fi.folio_name, -- 'charge_to_folio_id', fi.charge_to_folio, -- 'charge_to_room_number', r2.room_number, -- 'charge_to_guest_name', g.full_name, -- 'note', fi.note, -- 'departments', coalesce(dj.departments, '[]'::jsonb) -- ) -- from folio_items fi -- left join department_json dj on dj.folio_id = fi.folio_id and dj.folio_seq = fi.folio_seq -- where fi.folio_id = f.id -- and fi.folio_seq = 1 -- and fi.closed = false -- limit 1 -- ), -- 'folio2', ( -- select jsonb_build_object( -- 'item_id', fi.item_id, -- 'folio_seq', fi.folio_seq, -- 'folio_name', fi.folio_name, -- 'charge_to_folio_id', fi.charge_to_folio, -- 'charge_to_room_number', r2.room_number, -- 'charge_to_guest_name', g.full_name, -- 'note', fi.note, -- 'departments', coalesce(dj.departments, '[]'::jsonb) -- ) -- from folio_items fi -- left join department_json dj on dj.folio_id = fi.folio_id and dj.folio_seq = fi.folio_seq -- where fi.folio_id = f.id -- and fi.folio_seq = 2 -- and fi.closed = false -- limit 1 -- ), -- 'folio3', ( -- select jsonb_build_object( -- 'item_id', fi.item_id, -- 'folio_seq', fi.folio_seq, -- 'folio_name', fi.folio_name, -- 'charge_to_folio_id', fi.charge_to_folio, -- 'charge_to_room_number', r2.room_number, -- 'charge_to_guest_name', g.full_name, -- 'note', fi.note, -- 'departments', coalesce(dj.departments, '[]'::jsonb) -- ) -- from folio_items fi -- left join department_json dj on dj.folio_id = fi.folio_id and dj.folio_seq = fi.folio_seq -- where fi.folio_id = f.id -- and fi.folio_seq = 3 -- and fi.closed = false -- limit 1 -- ), -- 'folio4', ( -- select jsonb_build_object( -- 'item_id', fi.item_id, -- 'folio_seq', fi.folio_seq, -- 'folio_name', fi.folio_name, -- 'charge_to_folio_id', fi.charge_to_folio, -- 'charge_to_room_number', r2.room_number, -- 'charge_to_guest_name', g.full_name, -- 'note', fi.note, -- 'departments', coalesce(dj.departments, '[]'::jsonb) -- ) -- from folio_items fi -- left join department_json dj on dj.folio_id = fi.folio_id and dj.folio_seq = fi.folio_seq -- where fi.folio_id = f.id -- and fi.folio_seq = 4 -- and fi.closed = false -- limit 1 -- ) -- ) -- ) -- from folio f -- left join folio_pattern fp on f.folio_pattern_id = fp.id -- left join registration r on f.booking_id = r.booking_id -- left join room r2 on r2.id = r.room_id -- left join guest g on g.id = r.guest_id -- where f.id = p_folio_id; -- -- return v_result; with department_json as ( select fi.folio_id, fi.folio_seq, case when count(d.id) = 0 then '[]'::jsonb else json_agg( json_build_object( 'id', d.id, 'code', d.code, 'name', d.name ) order by d.name )::jsonb end as departments from folio_items fi left join folio_details fd on fd.folio_id = fi.folio_id and fd.folio_seq = fi.folio_seq left join department d on d.id = fd.dept_id where fi.folio_id = p_folio_id and fi.closed = false group by fi.folio_id, fi.folio_seq ) select into v_result ( jsonb_build_object( 'folio_id', f.id, 'folio_pattern_id', fp.id, 'folio_pattern_name', fp.name, 'register_id', f.register_id, 'room_number', r2.room_number, 'split_room_charge', f.split_room_charge, -- 'folio1', ( -- select jsonb_build_object( -- 'item_id', fi.item_id, -- 'folio_seq', fi.folio_seq, -- 'folio_name', fi.folio_name, -- 'charge_to_folio_id', fi.charge_to_folio, -- 'charge_to_room_number', r2.room_number, -- 'charge_to_guest_name', g.full_name, -- 'note', coalesce(fi.note,''), -- 'departments', coalesce(dj.departments, '[]'::jsonb) -- ) -- from folio_items fi -- left join department_json dj on dj.folio_id = fi.folio_id and dj.folio_seq = fi.folio_seq -- where fi.folio_id = f.id -- and fi.folio_seq = 1 -- limit 1 -- ), -- 'folio2', ( -- select jsonb_build_object( -- 'item_id', fi.item_id, -- 'folio_seq', fi.folio_seq, -- 'folio_name', fi.folio_name, -- 'charge_to_folio_id', fi.charge_to_folio, -- 'charge_to_room_number', r2.room_number, -- 'charge_to_guest_name', g.full_name, -- 'note', coalesce(fi.note,''), -- 'departments', coalesce(dj.departments, '[]'::jsonb) -- ) -- from folio_items fi -- left join department_json dj on dj.folio_id = fi.folio_id and dj.folio_seq = fi.folio_seq -- where fi.folio_id = f.id -- and fi.folio_seq = 2 -- limit 1 -- ), 'folio1', ( select case when ((select count(1) from folio_details fd where fd.folio_id = f.id and fd.folio_seq = 1) = 0) and ((select fi.folio_name from folio_items fi where fi.folio_id=f.id and fi.folio_seq = 1 limit 1) is null) then null else jsonb_build_object( 'item_id', fi.item_id, 'folio_seq', fi.folio_seq, 'folio_name', fi.folio_name, 'charge_to_folio_id', fi.charge_to_folio, 'charge_to_room_number', r2.room_number, 'charge_to_guest_name', g.full_name, 'note', coalesce(fi.note,''), 'departments', coalesce(dj.departments, '[]'::jsonb) ) end from folio_items fi left join department_json dj on dj.folio_id = fi.folio_id and dj.folio_seq = fi.folio_seq where fi.folio_id = f.id and fi.folio_seq = 1 and fi.closed = false limit 1 ), 'folio2', ( select case when ((select count(1) from folio_details fd where fd.folio_id = f.id and fd.folio_seq = 2) = 0) and ((select fi.folio_name from folio_items fi where fi.folio_id=f.id and fi.folio_seq = 2 limit 1) is null) then null else jsonb_build_object( 'item_id', fi.item_id, 'folio_seq', fi.folio_seq, 'folio_name', fi.folio_name, 'charge_to_folio_id', fi.charge_to_folio, 'charge_to_room_number', r2.room_number, 'charge_to_guest_name', g.full_name, 'note', coalesce(fi.note,''), 'departments', coalesce(dj.departments, '[]'::jsonb) ) end from folio_items fi left join department_json dj on dj.folio_id = fi.folio_id and dj.folio_seq = fi.folio_seq where fi.folio_id = f.id and fi.folio_seq = 2 and fi.closed = false limit 1 ), 'folio3', ( select case when ((select count(1) from folio_details fd where fd.folio_id = f.id and fd.folio_seq = 3) = 0) and ((select fi.folio_name from folio_items fi where fi.folio_id=f.id and fi.folio_seq = 3 limit 1) is null) then null else jsonb_build_object( 'item_id', fi.item_id, 'folio_seq', fi.folio_seq, 'folio_name', fi.folio_name, 'charge_to_folio_id', fi.charge_to_folio, 'charge_to_room_number', r2.room_number, 'charge_to_guest_name', g.full_name, 'note', coalesce(fi.note,''), 'departments', coalesce(dj.departments, '[]'::jsonb) ) end from folio_items fi left join department_json dj on dj.folio_id = fi.folio_id and dj.folio_seq = fi.folio_seq where fi.folio_id = f.id and fi.folio_seq = 3 and fi.closed = false limit 1 ), 'folio4', ( select case when ((select count(1) from folio_details fd where fd.folio_id = f.id and fd.folio_seq = 4) = 0) and ((select fi.folio_name from folio_items fi where fi.folio_id=f.id and fi.folio_seq = 4 limit 1) is null) then null else jsonb_build_object( 'item_id', fi.item_id, 'folio_seq', fi.folio_seq, 'folio_name', fi.folio_name, 'charge_to_folio_id', fi.charge_to_folio, 'charge_to_room_number', r2.room_number, 'charge_to_guest_name', g.full_name, 'note', coalesce(fi.note,''), 'departments', coalesce(dj.departments, '[]'::jsonb) ) end from folio_items fi left join department_json dj on dj.folio_id = fi.folio_id and dj.folio_seq = fi.folio_seq where fi.folio_id = f.id and fi.folio_seq = 4 and fi.closed = false limit 1 ) -- 'folio3', ( -- select jsonb_build_object( -- 'item_id', fi.item_id, -- 'folio_seq', fi.folio_seq, -- 'folio_name', fi.folio_name, -- 'charge_to_folio_id', fi.charge_to_folio, -- 'charge_to_room_number', r2.room_number, -- 'charge_to_guest_name', g.full_name, -- 'note', coalesce(fi.note,''), -- 'departments', coalesce(dj.departments, '[]'::jsonb) -- ) -- from folio_items fi -- left join department_json dj on dj.folio_id = fi.folio_id and dj.folio_seq = fi.folio_seq -- where fi.folio_id = f.id -- and fi.folio_seq = 3 -- limit 1 -- ), -- 'folio4', ( -- select jsonb_build_object( -- 'item_id', fi.item_id, -- 'folio_seq', fi.folio_seq, -- 'folio_name', fi.folio_name, -- 'charge_to_folio_id', fi.charge_to_folio, -- 'charge_to_room_number', r2.room_number, -- 'charge_to_guest_name', g.full_name, -- 'note', coalesce(fi.note,''), -- 'departments', coalesce(dj.departments, '[]'::jsonb) -- ) -- from folio_items fi -- left join department_json dj on dj.folio_id = fi.folio_id and dj.folio_seq = fi.folio_seq -- where fi.folio_id = f.id -- and fi.folio_seq = 4 -- limit 1 -- ) ) ) from folio f left join folio_pattern fp on f.folio_pattern_id = fp.id left join registration r on f.booking_id = r.booking_id left join room r2 on r2.id = r.room_id left join guest g on g.id = r.guest_id where f.id = p_folio_id; return v_result; end;