aonestar.public

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_interface_code TEXT;
    v_service_code   TEXT;

    v_trace_id uuid        := COALESCE((metadata->>'trace_id')::uuid, gen_random_uuid());
    v_sync_ts  timestamptz := COALESCE((metadata->>'sync_times')::timestamptz, now());
    v_start_ts timestamptz := clock_timestamp();
    v_end_ts   timestamptz;
    v_anomalies jsonb := '[]'::jsonb;
BEGIN

    v_interface_code := fn_intf_param('NBIS','INTERFACE_CODE','')::text;
    v_check_overall_avail := COALESCE(fn_intf_param('NBIS','CHECK_OVERALL_AVAIL','false')::boolean, false);
    v_property := NULLIF(fn_intf_param(v_interface_code,'PROPERTY_ID','')::text, '');
    v_service_code := 'SYNC_' || COALESCE(NULLIF(v_interface_code,''), 'UNKNOWN') || '_AVAIL';

    IF COALESCE(v_interface_code, '') = '' THEN
        RAISE EXCEPTION 'Missing INTERFACE_CODE from NBIS configuration';
    END IF;

    IF COALESCE(v_property, '') = '' THEN
        RAISE EXCEPTION 'Missing PROPERTY_ID for interface code %', v_interface_code;
    END IF;

    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
                )
               AND ra.start_date <= br.adate
               AND ra.end_date   >= br.adate
            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

        v_cur_qty := GREATEST(v_cur_qty, 0);

        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 intf_sync_channel_logs(
        interface_code,
        sync_type,
        trace_id,
        start_time,
        end_time,
        rows_returned,
        anomalies,
        metadata,
        params,
        return_payload,
        created_at
    )
    VALUES (
        v_service_code,
        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 jsonb_build_object(
        'result_data', jsonb_build_object(
            'metadata', jsonb_build_object(
                'trace_id',   v_trace_id,
                'sync_times', v_sync_ts,
                'rows',       v_rec_count
            ),
            '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 intf_sync_channel_logs(
            interface_code,
            sync_type,
            trace_id,
            start_time,
            end_time,
            rows_returned,
            anomalies,
            metadata,
            params,
            return_payload,
            created_at
        )
        VALUES (
            COALESCE(v_service_code, 'SYNC_UNKNOWN_AVAIL'),
            'ERROR',
            v_trace_id,
            v_start_ts,
            clock_timestamp(),
            0,
            jsonb_build_array(
                jsonb_build_object(
                    'error',   v_message,
                    'state',   v_state,
                    'detail',  v_detail,
                    'hint',    v_hint,
                    'context', v_context
                )
            ),
            metadata,
            params,
            NULL,
            now()
        );

        RETURN jsonb_build_object(
            'error',    v_message,
            'trace_id', v_trace_id,
            'state',    'FAILED'
        );
END;

sp_get_sync_roomavail

Parameters

Definition