aonestar
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
sp_get_sync_roomavail
Parameters
Name
Type
Mode
metadata
jsonb
IN
params
jsonb
IN
Definition
DECLARE i_last_sync timestamptz := (params->>'last_sync')::timestamptz; i_roomtypeno bpchar := params->>'roomtypeno'; i_max_days integer := COALESCE((params->>'max_days')::int, 400); v_cur_date date := fn_system_date(); v_check_overall_avail boolean; v_sync_all boolean := false; v_prv_date date; v_prv_type text; v_prv_qty integer; v_prv_channel text; v_cur_type text; v_cur_date_loop date; v_cur_qty integer; v_cur_channel text; v_rec_count integer := 0; start_date date; v_result jsonb := '[]'::jsonb; v_state TEXT; v_message TEXT; v_detail TEXT; v_hint TEXT; v_context TEXT; v_property TEXT; v_trace_id uuid := COALESCE((metadata->>'trace_id')::uuid, gen_random_uuid()); v_start_ts timestamptz := clock_timestamp(); v_end_ts timestamptz; v_anomalies jsonb := '[]'::jsonb; BEGIN v_property := NULLIF(fn_intf_param('NBIS','PROPERTY_ID','')::text, ''); v_check_overall_avail := COALESCE(fn_intf_param('NBIS','CHECK_OVERALL_AVAIL','false')::boolean, false); IF i_last_sync IS NOT NULL THEN i_last_sync := i_last_sync - interval '5 minute'; END IF; SELECT CASE WHEN i_last_sync IS NULL THEN true WHEN i_last_sync <= COALESCE(MIN(activity_time), NOW()) THEN true ELSE false END INTO v_sync_all FROM room_activity; CREATE TEMP TABLE IF NOT EXISTS avail_temp(adate date, avail integer) ON COMMIT DELETE ROWS; TRUNCATE TABLE avail_temp; IF v_check_overall_avail THEN INSERT INTO avail_temp(adate, avail) SELECT avail_date::date, avail FROM sp_get_room_available(v_cur_date, v_cur_date + (i_max_days - 1), NULL); ELSE INSERT INTO avail_temp(adate, avail) SELECT d::date, 9999 FROM generate_series(v_cur_date, v_cur_date + (i_max_days - 1), interval '1 day') d; END IF; v_prv_type := NULL; v_prv_date := NULL; v_prv_qty := NULL; v_prv_channel := NULL; FOR v_cur_type, v_cur_date_loop, v_cur_qty, v_cur_channel IN WITH base_room AS ( SELECT r.code AS roomtypeno, d::date AS adate, (r.total_rooms - COALESCE(a.qty, 0)) AS raw_avail, CASE WHEN COALESCE(overall.avail, 9999) <= 0 AND (r.total_rooms - COALESCE(a.qty, 0)) > 0 THEN 0 ELSE (r.total_rooms - COALESCE(a.qty, 0)) END AS adj_avail, COALESCE(r.channel_codes[1], NULL) AS channel_code FROM room_type r CROSS JOIN generate_series(v_cur_date, v_cur_date + (i_max_days - 1), interval '1 day') d LEFT JOIN ( SELECT ra.roomtype_id, d2::date AS adate, SUM(ra.qty) AS qty FROM room_activity ra JOIN generate_series(v_cur_date, v_cur_date + (i_max_days - 1), interval '1 day') d2 ON ra.start_date <= d2::date AND ra.end_date >= d2::date WHERE (i_roomtypeno IS NULL OR ra.roomtype_id = (SELECT id FROM room_type WHERE code = i_roomtypeno LIMIT 1)) GROUP BY ra.roomtype_id, d2 ) a ON a.roomtype_id = r.id AND a.adate = d::date LEFT JOIN avail_temp overall ON overall.adate = d::date WHERE (i_roomtypeno IS NULL OR r.code::text = i_roomtypeno::text) ), changed_only AS ( SELECT DISTINCT br.roomtypeno, br.adate, br.adj_avail, br.channel_code FROM base_room br JOIN room_activity ra ON ra.roomtype_id = (SELECT id FROM room_type WHERE code = br.roomtypeno LIMIT 1) WHERE ra.activity_time > i_last_sync OR v_sync_all ) SELECT roomtypeno, adate, adj_avail, channel_code FROM changed_only ORDER BY roomtypeno, adate LOOP IF (v_prv_type IS NOT NULL) AND NOT ( v_prv_type = v_cur_type AND v_prv_qty = v_cur_qty AND v_prv_channel IS NOT DISTINCT FROM v_cur_channel AND (v_prv_date + 1 = v_cur_date_loop) ) THEN v_result := v_result || jsonb_build_array(jsonb_build_object( 'property_id', v_property, 'date_from', start_date, 'date_to', v_prv_date, 'availability',v_prv_qty, 'room_type_id',v_prv_channel )); start_date := v_cur_date_loop; ELSIF start_date IS NULL THEN start_date := v_cur_date_loop; END IF; v_prv_type := v_cur_type; v_prv_date := v_cur_date_loop; v_prv_qty := v_cur_qty; v_prv_channel := v_cur_channel; v_rec_count := v_rec_count + 1; END LOOP; IF v_rec_count > 0 THEN v_result := v_result || jsonb_build_array(jsonb_build_object( 'property_id', v_property, 'date_from', start_date, 'date_to', v_prv_date, 'availability',v_prv_qty, 'room_type_id',v_prv_channel )); END IF; v_end_ts := clock_timestamp(); INSERT INTO interface_avail_logs( interface_code, sync_type, trace_id, start_time, end_time, rows_returned, anomalies, metadata, params, return_payload, created_at ) VALUES ( 'NBIS', CASE WHEN v_sync_all THEN 'FULL' ELSE 'ONCHANGE' END, v_trace_id, v_start_ts, v_end_ts, v_rec_count, v_anomalies, metadata, params, v_result, now() ); RETURN fn_result_success(jsonb_build_object( 'metadata', metadata, 'result', v_result )); EXCEPTION WHEN OTHERS THEN GET STACKED DIAGNOSTICS v_state = RETURNED_SQLSTATE, v_message = MESSAGE_TEXT, v_detail = PG_EXCEPTION_DETAIL, v_hint = PG_EXCEPTION_HINT, v_context = PG_EXCEPTION_CONTEXT; INSERT INTO interface_avail_logs( interface_code, sync_type, trace_id, start_time, end_time, rows_returned, anomalies, metadata, params, return_payload, created_at ) VALUES ( 'NBIS', 'ERROR', v_trace_id, v_start_ts, clock_timestamp(), 0, jsonb_build_array(jsonb_build_object('error', v_message)), metadata, params, NULL, now() ); RETURN fn_handle_error( v_state, v_message, v_detail, v_hint, v_context, 'public.sp_get_sync_roomavail', jsonb_build_object( 'metadata', metadata, 'params', params ) ); END;