aonestar.public

Parameters

Name	Type	Mode
metadata	jsonb	IN
params	jsonb	IN

Definition

DECLARE
    v_service_code      text;
    v_interface_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_date_from         date;
    v_date_to           date;

v_sync_all          boolean := COALESCE((params->>'sync_all')::boolean, true);
    v_property_id       uuid;
    v_allow_special     boolean := COALESCE((params->>'allow_special_rates')::boolean, true);

v_result            jsonb := '[]'::jsonb;
    v_rec_count         integer := 0;
    v_anomalies         jsonb := '[]'::jsonb;
    v_chg               jsonb := '[]'::jsonb;

v_prv_plan   text;
    v_prv_date   date;
    v_prv_ss     boolean;
    v_prv_cta    boolean;
    v_prv_ctd    boolean;
    v_prv_min    smallint;
    v_prv_max    smallint;

v_cur_plan   text;
    v_cur_date   date;
    v_cur_ss     boolean;
    v_cur_cta    boolean;
    v_cur_ctd    boolean;
    v_cur_min    smallint;
    v_cur_max    smallint;

v_start_date date;
BEGIN
    v_interface_code := fn_intf_param('NBIS','INTERFACE_CODE','')::text;
    v_service_code   := 'NBIS_' || v_interface_code || '_RESTRICTIONS';

v_property_id := params->>'property_id';
    IF v_property_id IS NULL THEN
        RAISE EXCEPTION 'property_id is required in params';
    END IF;

CREATE TABLE IF NOT EXISTS public.intf_rate_state (
        interface_code  text        NOT NULL,
        property_id     uuid        NOT NULL,
        rate_plan_id    text        NOT NULL,
        rate_date       date        NOT NULL,
        rate            integer     NOT NULL DEFAULT 0,
        updated_at      timestamptz NOT NULL DEFAULT now(),
        stop_sell       boolean,
        cta             boolean,
        ctd             boolean,
        min_stay        smallint,
        max_stay        smallint,
        PRIMARY KEY (interface_code, property_id, rate_plan_id, rate_date)
    );

v_date_from := COALESCE((params->>'date_from')::date, v_sync_ts::date, current_date);
    v_date_to   := v_date_from + COALESCE(NULLIF(params->>'max_days','')::int, 0);

WITH rd AS (
        SELECT DISTINCT ON (rate_id)
            rate_id,
            channel_codes
        FROM rate_details
        WHERE channel_linked = true
        ORDER BY rate_id
    ),
    src AS (
        SELECT
            rd.channel_codes[1]::text AS rate_plan_id,
            r.rate_date::date         AS rate_date,
            COALESCE(r.stop_sell, false)::boolean AS stop_sell,
            COALESCE(r.cta, false)::boolean       AS cta,
            COALESCE(r.ctd, false)::boolean       AS ctd,
            CASE WHEN r.min_stay IS NULL THEN 1 ELSE r.min_stay::smallint END AS min_stay,
            CASE WHEN r.max_stay IS NULL THEN 0 ELSE r.max_stay::smallint END AS max_stay
        FROM public.sp_inventory_rates(
            v_date_from, v_date_to,
            NULL, NULL, NULL,
            false, v_allow_special
        ) r
        JOIN rd ON rd.rate_id = r.rate_id
        WHERE rd.channel_codes[1] IS NOT NULL
    ),
    chg AS (
        SELECT s.*
        FROM src s
        LEFT JOIN public.intf_rate_state st
          ON st.interface_code = v_service_code
         AND st.property_id    = v_property_id
         AND st.rate_plan_id   = s.rate_plan_id
         AND st.rate_date      = s.rate_date
        WHERE v_sync_all
           OR st.rate_plan_id IS NULL
           OR st.stop_sell IS DISTINCT FROM s.stop_sell
           OR st.cta       IS DISTINCT FROM s.cta
           OR st.ctd       IS DISTINCT FROM s.ctd
           OR st.min_stay  IS DISTINCT FROM s.min_stay
           OR st.max_stay  IS DISTINCT FROM s.max_stay
    )
    SELECT COALESCE(
        jsonb_agg(
            jsonb_build_object(
                'rate_plan_id',       rate_plan_id,
                'rate_date',          rate_date,
                'stop_sell',          stop_sell,
                'closed_to_arrival',  cta,
                'closed_to_departure', ctd,
                'min_stay',           min_stay,
                'max_stay',           max_stay
            )
            ORDER BY rate_plan_id, rate_date
        ),
        '[]'::jsonb
    )
    INTO v_chg
    FROM chg;

v_result := '[]'::jsonb;
    v_rec_count := 0;
    v_prv_plan := NULL;
    v_prv_date := NULL;
    v_prv_ss := NULL;
    v_prv_cta := NULL;
    v_prv_ctd := NULL;
    v_prv_min := NULL;
    v_prv_max := NULL;
    v_start_date := NULL;

FOR v_cur_plan, v_cur_date, v_cur_ss, v_cur_cta, v_cur_ctd, v_cur_min, v_cur_max IN
        SELECT
            x.rate_plan_id, x.rate_date,
            x.stop_sell, x.closed_to_arrival, x.closed_to_departure,
            x.min_stay, x.max_stay
        FROM jsonb_to_recordset(v_chg) AS x(
            rate_plan_id           text,
            rate_date              date,
            stop_sell              boolean,
            closed_to_arrival      boolean,
            closed_to_departure    boolean,
            min_stay               smallint,
            max_stay               smallint
        )
        ORDER BY x.rate_plan_id, x.rate_date
    LOOP
        IF (v_prv_plan IS NOT NULL) AND NOT (
               v_prv_plan = v_cur_plan
           AND v_prv_date + 1 = v_cur_date
           AND v_prv_ss  IS NOT DISTINCT FROM v_cur_ss
           AND v_prv_cta IS NOT DISTINCT FROM v_cur_cta
           AND v_prv_ctd IS NOT DISTINCT FROM v_cur_ctd
           AND v_prv_min IS NOT DISTINCT FROM v_cur_min
           AND v_prv_max IS NOT DISTINCT FROM v_cur_max
        ) THEN
            v_result := v_result || jsonb_build_array(
                jsonb_build_object(
                    'property_id',         v_property_id::text,
                    'rate_plan_id',        v_prv_plan,
                    'date_from',           v_start_date,
                    'date_to',             v_prv_date,
                    'stop_sell',           v_prv_ss,
                    'closed_to_arrival',   v_prv_cta,
                    'closed_to_departure', v_prv_ctd,
                    'min_stay',            v_prv_min,
                    'min_stay_arrival',    v_prv_min,
                    'min_stay_through',    v_prv_min,
                    'max_stay',            v_prv_max
                )
            );
            v_rec_count := v_rec_count + 1;
            v_start_date := v_cur_date;
        ELSIF v_start_date IS NULL THEN
            v_start_date := v_cur_date;
        END IF;

v_prv_plan := v_cur_plan;
        v_prv_date := v_cur_date;
        v_prv_ss   := v_cur_ss;
        v_prv_cta  := v_cur_cta;
        v_prv_ctd  := v_cur_ctd;
        v_prv_min  := v_cur_min;
        v_prv_max  := v_cur_max;
    END LOOP;

IF v_prv_plan IS NOT NULL THEN
        v_result := v_result || jsonb_build_array(
            jsonb_build_object(
                'property_id',         v_property_id::text,
                'rate_plan_id',        v_prv_plan,
                'date_from',           v_start_date,
                'date_to',             v_prv_date,
                'stop_sell',           v_prv_ss,
                'closed_to_arrival',   v_prv_cta,
                'closed_to_departure', v_prv_ctd,
                'min_stay',            v_prv_min,
                'max_stay',            v_prv_max
            )
        );
        v_rec_count := v_rec_count + 1;
    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,
        COALESCE(metadata, '{}'::jsonb) || jsonb_build_object('state_rows', v_chg),
        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,
                'date_from',  v_date_from,
                'date_to',    v_date_to,
                'rows',       v_rec_count
            ),
            'result', v_result
        )
    );
EXCEPTION WHEN OTHERS THEN
    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, 'ERROR', v_trace_id,
        v_start_ts, v_end_ts, 0,
        jsonb_build_array(SQLERRM),
        COALESCE(metadata, '{}'::jsonb),
        params, NULL, now()
    );

RETURN jsonb_build_object(
        'error', SQLERRM,
        'trace_id', v_trace_id,
        'state', 'FAILED'
    );
END;

sp_get_sync_room_restrictions

Parameters

Definition