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 := COALESCE((params->>'property_id')::uuid, 'cf2a2d1b-692f-4140-b150-8542653bd4f6'::uuid);

    v_sync_restrictions boolean := COALESCE(fn_intf_param('NBIS','INTERFACE_RESTRICTION_ENABLED','false')::boolean, false);
    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_rate          int;
    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_rate          int;
    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 || '_RATE';

    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,
            (r.rate_amount::numeric * 100)::int  AS rate,
            COALESCE(r.stop_sell, false)         AS stop_sell,
            COALESCE(r.cta, false)               AS cta,
            COALESCE(r.ctd, false)               AS ctd,
            COALESCE(r.min_stay, 0)::smallint    AS min_stay,
            COALESCE(r.max_stay, 1)::smallint    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
          AND r.rate_amount 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 IS NULL
           OR st.rate IS DISTINCT FROM s.rate
           OR (
                v_sync_restrictions AND (
                    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,
                'rate',         rate,
                '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_rate := 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_rate, 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.rate,
            x.stop_sell,
            x.cta,
            x.ctd,
            x.min_stay,
            x.max_stay
        FROM jsonb_to_recordset(v_chg) AS x(
            rate_plan_id text,
            rate_date    date,
            rate         int,
            stop_sell    boolean,
            cta          boolean,
            ctd          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_rate = v_cur_rate
           AND (
                 NOT v_sync_restrictions
                 OR (
                     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,
                    'rate',         v_prv_rate
                )
                ||
                CASE WHEN v_sync_restrictions THEN
                    jsonb_build_object(
                        '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
                    )
                ELSE '{}'::jsonb END
            );

            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_rate := v_cur_rate;
        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,
                'rate',         v_prv_rate
            )
            ||
            CASE WHEN v_sync_restrictions THEN
                jsonb_build_object(
                    '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
                )
            ELSE '{}'::jsonb END
        );

        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
        )
    );
END;

sp_get_sync_roomrate

Parameters

Definition