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_rate   int;

v_cur_plan   text;
    v_cur_date   date;
    v_cur_rate   int;

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

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

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
        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
    )
    SELECT COALESCE(
        jsonb_agg(
            jsonb_build_object(
                'rate_plan_id', rate_plan_id,
                'rate_date',    rate_date,
                'rate',         rate
            )
            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_start_date := NULL;

FOR v_cur_plan, v_cur_date, v_cur_rate IN
        SELECT x.rate_plan_id, x.rate_date, x.rate
        FROM jsonb_to_recordset(v_chg) AS x(
            rate_plan_id text,
            rate_date    date,
            rate         int
        )
        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
        ) 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
                )
            );
            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;
    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
            )
        );
        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_roomrate

Parameters

Definition