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_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;