aonestar.public

Parameters

Name	Type	Mode
i_days	integer	IN (DEFAULT NULL)
i_by_roomtype	boolean	IN (DEFAULT NULL)
i_rateplan_id	integer	IN (DEFAULT NULL)

Definition

DECLARE
    v_start date := fn_system_date();
    v_days  integer := COALESCE(i_days, fn_sys_param('ROOM_RATE', 'YIELD_DAYS', 400));
    v_end   date;
    v_by_rt boolean := COALESCE(i_by_roomtype, fn_sys_param('ROOM_RATE', 'YIELD_BY_ROOMTYPE', false));
    v_count integer := 0;
BEGIN
    v_end := v_start + v_days - 1;

-- group log ต่อ run + action 'Y' → trigger tr_rate_daily_aiu_save_log ใช้ค่านี้
    PERFORM set_config('rate_log.action_type', 'Y', true);
    PERFORM set_config('rate_log.log_group', uuidv7()::text, true);

DROP TABLE IF EXISTS _yield_calc;
    CREATE TEMP TABLE _yield_calc ON COMMIT DROP AS
    WITH dates AS (
        SELECT d::date AS adate, (d::date - v_start)::int AS dba
        FROM generate_series(v_start, v_end, interval '1 day') d
    ),
    asg AS (
        SELECT a.*
        FROM public.yield_assignment a
        WHERE a.enabled
          AND (i_rateplan_id IS NULL OR a.rateplan_id = i_rateplan_id)
    ),
    asg_dates AS (   -- expand assignment × dates (absolute / recurring month-day + wrap) + DOW filter
        SELECT a.rateplan_id, a.rule_id, d.adate, d.dba
        FROM asg a
        JOIN dates d ON
            (
              a.recurring = false
              AND d.adate BETWEEN make_date(a.from_year::int, a.from_month::int, a.from_day::int)
                              AND make_date(a.to_year::int, a.to_month::int, a.to_day::int)
            )
            OR
            (
              a.recurring = true
              AND (
                CASE WHEN (a.from_month * 100 + a.from_day) <= (a.to_month * 100 + a.to_day)
                     THEN (extract(month from d.adate) * 100 + extract(day from d.adate))
                            BETWEEN (a.from_month * 100 + a.from_day) AND (a.to_month * 100 + a.to_day)
                     ELSE (extract(month from d.adate) * 100 + extract(day from d.adate)) >= (a.from_month * 100 + a.from_day)
                       OR (extract(month from d.adate) * 100 + extract(day from d.adate)) <= (a.to_month * 100 + a.to_day)
                END
              )
            )
        WHERE a.days[extract(dow from d.adate)::int + 1]     -- days[1]=Sunday (ตรง rate_plan.days)
    ),
    rd AS (          -- ต่อ rate_details ที่อนุญาต yield — เฉพาะ base plan (plan_type = 1)
        SELECT det.rate_id, det.roomtype_id, ad.rule_id, ad.adate, ad.dba
        FROM asg_dates ad
        JOIN public.rate_details det ON det.rateplan_id = ad.rateplan_id AND det.active AND det.allow_yield
        JOIN public.rate_plan p ON p.id = ad.rateplan_id AND p.enabled AND p.plan_type = 1
    ),
    occ_rt AS (
        SELECT adate, roomtype_id, (occ * 100.0 / NULLIF(ttl, 0))::public.t_percent AS occ_percent
        FROM public.sp_room_avail_by_roomtype(v_start, v_end)
    ),
    occ_all AS (
        SELECT adate, occ_percent_overall AS occ_percent
        FROM public.sp_get_occupancy_calendar(v_start, v_end)
    ),
    rd_occ AS (
        SELECT rd.rate_id, rd.roomtype_id, rd.rule_id, rd.adate, rd.dba,
               CASE WHEN v_by_rt THEN ort.occ_percent ELSE oa.occ_percent END AS occ_percent
        FROM rd
        LEFT JOIN occ_rt  ort ON v_by_rt       AND ort.adate = rd.adate AND ort.roomtype_id = rd.roomtype_id
        LEFT JOIN occ_all oa  ON NOT v_by_rt   AND oa.adate  = rd.adate
    )
    SELECT rd_occ.rate_id, rd_occ.adate AS rate_date,
           r.adjust_type, t.adjust_value, rd_occ.occ_percent
    FROM rd_occ
    JOIN public.yield_rule r ON r.id = rd_occ.rule_id AND r.enabled
    JOIN public.yield_rule_tier t ON t.rule_id = rd_occ.rule_id
         AND rd_occ.occ_percent >= t.occ_from
         AND (rd_occ.occ_percent < t.occ_to OR t.occ_to >= 100)     -- tier บนสุด (100) catch-all
         AND rd_occ.dba BETWEEN t.lead_from AND t.lead_to;

-- กันซ้ำ: ถ้า config/tier ทับกันจน 1 (rate_id,date) match หลาย tier → เลือก adjust_value มากสุด
    DELETE FROM _yield_calc a
    USING _yield_calc b
    WHERE a.rate_id = b.rate_id AND a.rate_date = b.rate_date
      AND (a.adjust_value, a.ctid) < (b.adjust_value, b.ctid);

-- clear cell ที่พ้นเงื่อนไข (มี yield เดิม แต่ไม่อยู่ในผลรอบนี้) — คง yield_override
    UPDATE public.rate_daily rdy
    SET yield_adjust_type = NULL, yield_adjust_value = NULL, yield_occ_percent = NULL
    WHERE rdy.rate_date BETWEEN v_start AND v_end
      AND rdy.yield_adjust_type IS NOT NULL
      AND (i_rateplan_id IS NULL OR EXISTS (
            SELECT 1 FROM public.rate_details d WHERE d.rate_id = rdy.rate_id AND d.rateplan_id = i_rateplan_id))
      AND NOT EXISTS (SELECT 1 FROM _yield_calc c WHERE c.rate_id = rdy.rate_id AND c.rate_date = rdy.rate_date);

-- upsert ผลใหม่ (ไม่แตะ rate_amount / yield_override) — trigger log 'Yield' เมื่อ adjust_value เปลี่ยน
    INSERT INTO public.rate_daily AS rdy (rate_id, rate_date, yield_adjust_type, yield_adjust_value, yield_occ_percent)
    SELECT rate_id, rate_date, adjust_type, adjust_value, occ_percent
    FROM _yield_calc
    ON CONFLICT (rate_id, rate_date) DO UPDATE
        SET yield_adjust_type  = excluded.yield_adjust_type,
            yield_adjust_value = excluded.yield_adjust_value,
            yield_occ_percent  = excluded.yield_occ_percent;

GET DIAGNOSTICS v_count = ROW_COUNT;
    RETURN v_count;
END

sp_recalc_yield_rates

Parameters

Definition