aonestar.public

Parameters

Name	Type	Mode
number_of_days	integer	IN
start_date	date	IN (DEFAULT NULL)
i_roomtype_ids	int4[]	IN (DEFAULT NULL)
i_rateplan_ids	int4[]	IN (DEFAULT NULL)
i_channel_id	integer	IN (DEFAULT NULL)

Definition

DECLARE
    v_start date;
    v_end   date;
    v_days  integer;
    json_result json;
BEGIN
    v_days  := LEAST(GREATEST(COALESCE(number_of_days, 14), 1), 62);   -- cap interactive window
    v_start := COALESCE(start_date, fn_system_date(), current_date);
    v_end   := v_start + v_days - 1;

WITH d AS (
        SELECT gs::date AS adate, (gs::date - v_start)::int AS seq
        FROM generate_series(v_start, v_end, interval '1 day') gs
    ),
    ev AS (
        SELECT e.*
        FROM json_to_recordset(fn_get_calendar_events_js(v_start, v_end, NULL))
            AS e(calendar_type text, name text, color text, is_public_holiday boolean, start_date date, end_date date)
    ),
    chan_rp AS (
        SELECT rateplan_id
        FROM public.rate_plan_channels
        WHERE i_channel_id IS NOT NULL AND channel_id = i_channel_id AND enabled
    ),
    inv_rates AS MATERIALIZED (    -- รัน sp_inventory_rates ครั้งเดียว; push roomtype filter เข้า source
        SELECT ir.*
        FROM public.sp_inventory_rates(v_start, v_end, allow_special_rates := false, allow_inactive_rates := true,
                                       i_roomtype_ids := i_roomtype_ids) ir
        WHERE (i_rateplan_ids IS NULL OR ir.rateplan_id = ANY(i_rateplan_ids))   -- rateplan/channel ยัง post-filter
          AND (i_channel_id   IS NULL OR ir.rateplan_id IN (SELECT rateplan_id FROM chan_rp))
    ),
    dates_js AS (
        SELECT json_agg(json_build_object(
                   'date', d.adate,
                   'dow', extract(dow from d.adate)::int,
                   'is_weekend', is_weekend(d.adate),   -- per-property WEEKEND_DAYS (fn_weekends)
                   'is_holiday', fn_is_holiday(d.adate),
                   'events', COALESCE((
                        SELECT json_agg(json_build_object(
                                   'calendar_type', e.calendar_type, 'name', e.name,
                                   'color', e.color, 'is_public_holiday', e.is_public_holiday))
                        FROM ev e
                        WHERE d.adate BETWEEN e.start_date AND COALESCE(e.end_date, e.start_date)
                   ), '[]'::json)
               ) ORDER BY d.seq) AS js
        FROM d
    ),
    occ_js AS (
        SELECT json_agg(o.occ_percent ORDER BY d.seq) AS js
        FROM d
        LEFT JOIN (
            SELECT adate, occ_percent_overall AS occ_percent
            FROM public.sp_get_occupancy_calendar(v_start, v_end)
        ) o ON o.adate = d.adate
    ),
    inv AS (
        SELECT rt.id AS roomtype_id, rt.code, rt.name AS roomtype_name, rt.order_seq AS sequence, rt.total_rooms,
            -- available row (align by date)
            (SELECT json_agg(a.avail ORDER BY a.avail_date)
             FROM public.sp_get_room_available(v_start, v_end, rt.id) a) AS available,
            -- sell status row (align by date)
            (SELECT json_agg(json_build_object('stop_sell', COALESCE(r.stop_sell, false),
                                               'min_avail', r.min_avail, 'max_avail', r.max_avail) ORDER BY d.seq)
             FROM d LEFT JOIN public.room_restriction r ON r.roomtype_id = rt.id AND r.effect_date = d.adate) AS sell_status,
            -- rate plans: 1 row/plan
            --   base plan (plan_type=1, editable): amount=ก่อน yield + yield object(หลัง) + has_yield
            --   derived/linked plan (plan_type=2, แก้ไม่ได้): amount=ราคาหลัง yield (inherited) เลย, yield=null, has_yield=false
            (SELECT COALESCE(json_agg(
                        json_build_object(
                            'rate_id', rp.rate_id, 'rateplan_id', rp.rateplan_id, 'description', rp.description,
                            'link_plan_id', rp.link_plan_id, 'link_description', rp.link_description,
                            'editable', rp.editable, 'sequence', rp.sequence, 'min_rate', rp.min_rate,
                            'has_yield', CASE WHEN rp.link_plan_id IS NOT NULL THEN false
                                              ELSE COALESCE(agg.has_yield, false) END,
                            'rates', agg.rates
                        ) ORDER BY rp.sequence), '[]'::json)
             FROM (SELECT DISTINCT rate_id, rateplan_id,
                          rateplan_name || COALESCE((' (' || NULLIF(description, '')) || ')', '') AS description,
                          link_plan_id, link_description, editable, rateplan_seq AS sequence, min_rate
                   FROM inv_rates
                   WHERE roomtype_id = rt.id AND (enabled OR (plan_type = 1 AND has_active_child))) rp
             CROSS JOIN LATERAL (
                 -- yield metadata มาจาก inv_rates แล้ว (ไม่ต้อง join rate_daily ซ้ำ)
                 SELECT json_agg(json_build_object(
                            -- derived plan: amount = ราคาหลัง yield (inherited); base plan: amount = ก่อน yield
                            'amount', CASE WHEN rp.link_plan_id IS NOT NULL THEN b.rate_amount ELSE b.base_rate_amount END,
                            'yield', CASE
                                WHEN rp.link_plan_id IS NOT NULL THEN NULL   -- derived: ไม่มี yield object (amount รวม yield แล้ว)
                                WHEN b.yield_adjust_type IS NULL AND b.yield_override IS NULL
                                     AND b.rate_amount IS NOT DISTINCT FROM b.base_rate_amount THEN NULL
                                ELSE json_build_object(
                                    'amount', b.rate_amount,    -- after yield (base plan own)
                                    'adjust_type', CASE b.yield_adjust_type WHEN 3 THEN 'amount' WHEN 2 THEN 'percent' END,
                                    'adjust_value', b.yield_adjust_value,
                                    'override', b.yield_override) END,
                            'restrictions', json_build_object(
                                'stop_sell', b.stop_sell, 'cta', b.cta, 'ctd', b.ctd,
                                'min_stay', b.min_stay, 'max_stay', b.max_stay)) ORDER BY b.rate_date) AS rates,
                        bool_or(b.yield_adjust_type IS NOT NULL OR b.yield_override IS NOT NULL
                                OR b.rate_amount IS DISTINCT FROM b.base_rate_amount) AS has_yield
                 FROM inv_rates b
                 WHERE b.roomtype_id = rt.id AND b.rate_id = rp.rate_id
             ) agg
            ) AS rate_plans
        FROM public.room_type rt
        WHERE rt.enabled
          AND (i_roomtype_ids IS NULL OR rt.id = ANY(i_roomtype_ids))
        ORDER BY rt.order_seq
    )
    SELECT json_build_object(
        'dates',     (SELECT js FROM dates_js),
        'occupancy', (SELECT js FROM occ_js),
        'inventory', (SELECT json_agg(inv) FROM inv)
    )
    INTO json_result;

RETURN json_result;
END

sp_ratecalendar_get_js

Parameters

Definition