aonestar.public

Parameters

Name	Type	Mode
p_roomtype_id	integer	IN
register_id	integer	IN
room_id	integer	IN
arrival	date	IN
departure	date	IN
adult	integer	IN
child	integer	IN (DEFAULT 0)
infant	integer	IN (DEFAULT 0)
i_origin_id	integer	IN (DEFAULT NULL)
i_market_id	integer	IN (DEFAULT NULL)
show_original_rate	boolean	IN (DEFAULT false)

Definition

declare
    night int;
    adv_period int;
    v_result_code text;
    v_result_msg text;
    v_msg_code text;
    v_result_data json;
    final_result json;
    book_date date;
    booking_item integer;
    qty integer := 1;  -- Since it's checking in one room at a time
begin
    /* RESULT CODE
       0, 10100 : Available 
       4, 10101 : All rooms were full 
       2, 10102 : Specified room type is not sellable (confirm user to make booking)
       4, 10103 : Specified room type not enough, overbook not allow
       2, 10104 : Specified room type not enough (confirm user to make booking)
       4, 10105 : Rate not found
    */

v_result_code := '0';
    v_result_data := '[]';

child  := coalesce(child, 0);
    infant := coalesce(infant, 0);
 
    -- Check room available
    SELECT 
        a.result_code,
        a.result_msg,
        a.msg_code 
    FROM sp_check_room_available(
        roomtype_id := p_roomtype_id,
        arrival := arrival,
        departure := departure,
        qty := qty
    ) AS a
    INTO v_result_code, v_result_msg, v_msg_code;

-- Get available rates
    IF v_result_code < '4' THEN
        book_date  := coalesce(book_date, fn_system_date());
        night      := departure - arrival;
        adv_period := 1;
    
        with 
        original AS (
            SELECT DISTINCT rateplan_id
            FROM registration_rates rr
            WHERE rr.register_id = $1  -- ใช้ $1 แทน register_id parameter
            AND rr.charge_date BETWEEN arrival AND departure - 1
        ),
        inv AS (
            SELECT 
                rate_id,
                rate_date,
                rate_amount,
                stop_sell,
                cta,
                ctd,
                min_stay,
                max_stay,
                available,
                enabled
            FROM sp_inventory_rates(
                arrival,
                departure-1,
                i_roomtype_id => p_roomtype_id,
                allow_inactive_rates => false
            ) AS iv
            WHERE available = TRUE
        ), 
        Rates AS (
            SELECT 
                rate_id,
                rd.rateplan_id,
                rd.name,
                rd.description,
                LEAST(adult, rd.include_adult) AS include_adult, 
                LEAST(child, COALESCE(rd.include_child, rd.max_child, child)) AS include_child, 
                LEAST(infant, COALESCE(rd.include_infant, rd.max_infant, infant)) AS include_infant,
                rd.no_amend, 
                rd.no_cancel, 
                rd.no_refund, 
                rd.origin_id, 
                rd.market_id,
                rd.rate_extra_adult, 
                rd.rate_extra_child, 
                rd.rate_extra_infant, 
                rp.comp, 
                rp.comp_abf,
                rp.mealtype_id, 
                m.name as mealtype_name,
                rd.order_seq
            FROM v_rate_details rd 
                LEFT JOIN rate_plan rp ON rp.id = rd.rateplan_id 
                LEFT JOIN meal_type m ON m.id = rp.mealtype_id
                LEFT JOIN original o ON o.rateplan_id = rd.rateplan_id
            WHERE rd.enabled 
                AND rd.roomtype_id = p_roomtype_id
                AND (rd.book_start_date IS NULL OR book_date >= rd.book_start_date) 
                AND (rd.book_end_date IS NULL OR book_date <= rd.book_end_date) 
                AND (rd.stay_start_date IS NULL OR arrival >= rd.stay_start_date) 
                AND (rd.stay_end_date IS NULL OR departure <= rd.stay_end_date) 
                AND adv_period BETWEEN COALESCE(rd.min_adv_book, 0) AND COALESCE(rd.max_adv_book, 9999999)  
                AND adult <= coalesce(rd.max_adult, 9999)
                AND child <= coalesce(rd.max_child, 9999)
                AND infant <= coalesce(rd.max_infant, 9999)
                AND (adult + child) <= coalesce(rd.max_pax, 9999)
                AND (show_original_rate = true OR o.rateplan_id IS NULL)
            ORDER BY order_seq, rate_id
        ), 
        Schedule AS (
            SELECT 
                R.rate_id, 
                sum(p.rate_amount) total_amount,
                array_agg(json_build_object(
                    'date', p.rate_date, 
                    'amount', p.rate_amount,
                    'extra_adult_rate', r.rate_extra_adult,
                    'extra_child_rate', r.rate_extra_child,
                    'extra_infant_rate', r.rate_extra_infant,
                    'comp', r.comp,
                    'comp_abf', r.comp_abf,
                    'mealtype_id', r.mealtype_id,
                    'mealtype_name', r.mealtype_name
                )) AS rate_schedule,
                bool_or(
                    NOT available 
                    OR NOT enabled 
                    OR p.stop_sell
                    OR NOT (night BETWEEN COALESCE(p.min_stay, 0) AND COALESCE(p.max_stay, 9999999)) 
                    OR (COALESCE(p.cta, FALSE) AND p.rate_date = arrival)
                    OR (COALESCE(p.ctd, FALSE) AND p.rate_date = departure)
                ) AS restricted
            FROM Rates R 
            LEFT JOIN inv AS P ON p.rate_id = R.rate_id
            GROUP BY R.rate_id
        ), 
        DataSet AS (
            SELECT 
                R.rate_id,
                R.rateplan_id,
                name,
                description,
                R.mealtype_id,
                R.mealtype_name,
                (S.rate_schedule[1]->>'amount')::t_money AS rate_amount,
                include_adult, 
                include_child,
                include_infant,
                CASE WHEN adult > include_adult THEN adult - include_adult ELSE 0 END AS extra_adult,
                CASE WHEN child > include_child THEN child - include_child ELSE 0 END AS extra_child,
                CASE WHEN infant > include_infant THEN infant - include_infant ELSE 0 END AS extra_infant,
                no_amend,
                no_cancel,
                no_refund,
                origin_id,
                market_id,
                S.total_amount,
                (fn_length(S.rate_schedule) > 1 
                    AND S.rate_schedule[1]->>'amount' <> ANY(
                        SELECT unnest(S.rate_schedule)->>'amount'
                    )
                ) AS rate_multiple,
                S.rate_schedule
            FROM Rates R 
            INNER JOIN Schedule S ON S.rate_id = R.rate_id
            WHERE NOT S.restricted
        )   
        SELECT json_agg(DataSet) 
        FROM DataSet 
        INTO v_result_data;
    
        IF v_result_data IS NULL THEN 
            v_result_data := '[]';
            v_result_code := '4';
            v_msg_code    := '10105';
            v_result_msg  := sys_msg(v_msg_code, 'No available rates during this period');
        END IF;
    END IF;

-- Construct the final JSON result
    final_result := json_build_object(
        'result_code', v_result_code,
        'result_msg', v_result_msg,
        'msg_code', v_msg_code,
        'result_data', v_result_data
    );

RETURN final_result;
END;

sp_guest_inhouse_get_available_rate_json

Parameters

Definition