aonestar
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
sp_get_block_room_js
Parameters
Name
Type
Mode
i_booking_id
integer
IN
Definition
DECLARE json_result json; _details json; _file_url_prefix TEXT; BEGIN /** * Create by KenG (2020/05/26) * ---(2020/04/17) * concat instead || * --extract for compare year month * -- */ _file_url_prefix := fn_prop_config('PREFIX_URL_PMS_FILE'); select json_agg(dataset) from ( select bi.item_id as item_id, rt.id as roomtype_id, rt.code as roomtype_code, bt.code as bedtype_code, bi.bedtype_id as bedtype_id, bi.arrival, bi.departure, bi.qty as qty, bi.block_qty as block_qty, bi.inhouse_qty as inhouse_qty, coalesce( rt.code || coalesce('/' || bt.code, '') --some booking_item do not have bed_type id || ' @' || rp."name" || ' (' || case when (extract(month from bi.arrival) = extract(month from bi.departure) and extract(year from bi.arrival) = extract(year from bi.departure)) and extract(year from bi.arrival) = extract(year from CURRENT_DATE) then to_char(bi.arrival, 'DD') || ' - ' || to_char(bi.departure, 'DD') || to_char(bi.departure, ' Mon') when (extract(month from bi.arrival) <> extract(month from bi.departure) and extract(year from bi.arrival) = extract(year from bi.departure)) and extract(year from bi.arrival) = extract(year from CURRENT_DATE) then to_char(bi.arrival, 'DD Mon') || ' - ' || to_char(bi.departure, 'DD Mon') when (extract(month from bi.arrival) <> extract(month from bi.departure) and extract(year from bi.arrival) = extract(year from bi.departure)) then to_char(bi.arrival, 'DD Mon') || ' - ' || to_char(bi.departure, 'DD Mon') || to_char(bi.departure, ' YYYY') when (extract(month from bi.arrival) = extract(month from bi.departure) and extract(year from bi.arrival) = extract(year from bi.departure)) then to_char(bi.arrival, 'DD') || ' - ' || to_char(bi.departure, 'DD') || to_char(bi.departure, ' Mon YYYY') else to_char(bi.arrival, 'DD Mon YYYY') || ' - ' || to_char(bi.departure, 'DD Mon YYYY') end || ')' ) as str_code, ( select coalesce(array_agg(json_build_object( --use for drag to search zone (param like sp_search_room_js) 'id', r2.id, 'room', r2.room_number || '/' || rt2.code, 'roomtype_id', rt2.id, 'room_type', rt2."name", 'bedtype_id', bt2.id, 'bed_type', bt2."name" , 'location_id', loc2.id, 'location', loc2."name", 'building_id', b2.id, 'building', b2."name", 'exposure_id', rx2.id, 'exposure', rx2."name", 'room_status', st2.status, 'hk_status', st2.hk_status, 'roomgroup_id', rg2.id, 'room_group', rg2."name", --Use for room block 'room_number', r2.room_number, 'roomtype_code', rt2.code, 'bedtype_code', bt2.code, 'block_date', rb2.block_date, 'block_time', rb2.block_time, 'block_user', rb2.block_user, 'super_block', rb2.super_block, 'bedtype_image_url', _file_url_prefix ||bt2.image_url ) order by rb2.room_id desc -- add by T.Supol 15-May-2025 ), '{}') from room_block rb2 left join room r2 on r2.id = rb2.room_id left join room_type rt2 on rt2.id = r2.roomtype_id left join room_group rg2 on rg2.id = rt2.group_id left join bed_type bt2 on bt2.id = r2.bedtype_id left join room_location loc2 on loc2.id = r2.location_id left join building b2 on b2.id = r2.building_id left join room_exposure rx2 on rx2.id = r2.exposure_id left join room_status st2 on st2.room_id = r2.id where rb2.booking_id = i_booking_id and rb2.booking_item = bi.item_id --and rt2.id = rt.id and coalesce(bt2.code, '') = coalesce(bt.code, '') and rb2.status <> 'I' -- add by T.Supol 6-June-2025 ) as rooms from booking_items bi left join room_type rt on rt.id = bi.roomtype_id left join bed_type bt on bt.id = bi.bedtype_id left join rate_plan rp on rp.id = bi.rateplan_id where bi.booking_id = i_booking_id and bi.qty <> bi.inhouse_qty -- add by T.Supol 6-June-2025 ) as dataset into json_result; RETURN json_result; END;