aonestar.public

Parameters

Name	Type	Mode

Definition

DECLARE
    v_roomtype_id integer;
    v_rateplan_id integer;
    -- resolve ผู้กระทำเอง แทนการเรียก fn_current_user() เพื่อ "ไม่" ตกไปถึง
    -- fn_get_param('DEBUG','USER_NAME') ซึ่ง seed เป็น placeholder '@UNKNOWN' ทุก property DB
    -- แล้วรั่วลง audit log (ClickUp 86d42y50e)
    -- path ที่ไม่มี session user (pg_cron/dblink, endday, migration, psql) จะลง 'System'
    v_user_name text := COALESCE(
        NULLIF(current_setting('rate_log.user_name', true), ''),
        NULLIF(current_setting('current.username', true), ''),
        NULLIF(fn_get_session('user_name'), ''),
        'System');
    v_action_type char(1) := COALESCE(NULLIF(current_setting('rate_log.action_type', true), ''), 'D')::char(1);
    v_log_group_setting text := NULLIF(current_setting('rate_log.log_group', true), '');
    v_log_group uuid;
    v_yield_new text;
BEGIN
    IF TG_OP NOT IN ('INSERT', 'UPDATE') THEN
        RETURN NEW;
    END IF;

v_log_group := COALESCE(v_log_group_setting::uuid, uuidv7());

SELECT rd.roomtype_id, rd.rateplan_id
    INTO v_roomtype_id, v_rateplan_id
    FROM public.rate_details rd
    WHERE rd.rate_id = NEW.rate_id;

IF TG_OP = 'INSERT' THEN
        IF NEW.rate_amount IS NOT NULL THEN
            PERFORM public.sp_log_rate(v_user_name, NEW.rate_date, NEW.rate_id, v_roomtype_id, v_rateplan_id, NULL, 'Rate', NEW.rate_amount::text, NULL, v_log_group, v_action_type);
        END IF;

IF NEW.stop_sell IS NOT NULL THEN
            PERFORM public.sp_log_rate(v_user_name, NEW.rate_date, NEW.rate_id, v_roomtype_id, v_rateplan_id, NULL, 'Close Sell', NEW.stop_sell::text, NULL, v_log_group, v_action_type);
        END IF;

IF NEW.cta IS NOT NULL THEN
            PERFORM public.sp_log_rate(v_user_name, NEW.rate_date, NEW.rate_id, v_roomtype_id, v_rateplan_id, NULL, 'CTA', NEW.cta::text, NULL, v_log_group, v_action_type);
        END IF;

IF NEW.ctd IS NOT NULL THEN
            PERFORM public.sp_log_rate(v_user_name, NEW.rate_date, NEW.rate_id, v_roomtype_id, v_rateplan_id, NULL, 'CTD', NEW.ctd::text, NULL, v_log_group, v_action_type);
        END IF;

IF NEW.min_stay IS NOT NULL THEN
            PERFORM public.sp_log_rate(v_user_name, NEW.rate_date, NEW.rate_id, v_roomtype_id, v_rateplan_id, NULL, 'Min Stay', NEW.min_stay::text, NULL, v_log_group, v_action_type);
        END IF;

IF NEW.max_stay IS NOT NULL THEN
            PERFORM public.sp_log_rate(v_user_name, NEW.rate_date, NEW.rate_id, v_roomtype_id, v_rateplan_id, NULL, 'Max Stay', NEW.max_stay::text, NULL, v_log_group, v_action_type);
        END IF;

IF NEW.yield_adjust_type IS NOT NULL OR NEW.yield_override IS NOT NULL THEN
            v_yield_new := public.fn_yield_effective_rate(NEW.rate_id, NEW.rate_date)::text;
            PERFORM public.sp_log_rate(v_user_name, NEW.rate_date, NEW.rate_id, v_roomtype_id, v_rateplan_id, NULL, 'Yield', v_yield_new, NULL, v_log_group, v_action_type);
        END IF;

RETURN NEW;
    END IF;

IF OLD.rate_amount IS DISTINCT FROM NEW.rate_amount THEN
        PERFORM public.sp_log_rate(v_user_name, NEW.rate_date, NEW.rate_id, v_roomtype_id, v_rateplan_id, NULL, 'Rate', NEW.rate_amount::text, OLD.rate_amount::text, v_log_group, v_action_type);
    END IF;

IF OLD.stop_sell IS DISTINCT FROM NEW.stop_sell THEN
        PERFORM public.sp_log_rate(v_user_name, NEW.rate_date, NEW.rate_id, v_roomtype_id, v_rateplan_id, NULL, 'Close Sell', NEW.stop_sell::text, OLD.stop_sell::text, v_log_group, v_action_type);
    END IF;

IF OLD.cta IS DISTINCT FROM NEW.cta THEN
        PERFORM public.sp_log_rate(v_user_name, NEW.rate_date, NEW.rate_id, v_roomtype_id, v_rateplan_id, NULL, 'CTA', NEW.cta::text, OLD.cta::text, v_log_group, v_action_type);
    END IF;

IF OLD.ctd IS DISTINCT FROM NEW.ctd THEN
        PERFORM public.sp_log_rate(v_user_name, NEW.rate_date, NEW.rate_id, v_roomtype_id, v_rateplan_id, NULL, 'CTD', NEW.ctd::text, OLD.ctd::text, v_log_group, v_action_type);
    END IF;

IF OLD.min_stay IS DISTINCT FROM NEW.min_stay THEN
        PERFORM public.sp_log_rate(v_user_name, NEW.rate_date, NEW.rate_id, v_roomtype_id, v_rateplan_id, NULL, 'Min Stay', NEW.min_stay::text, OLD.min_stay::text, v_log_group, v_action_type);
    END IF;

IF OLD.max_stay IS DISTINCT FROM NEW.max_stay THEN
        PERFORM public.sp_log_rate(v_user_name, NEW.rate_date, NEW.rate_id, v_roomtype_id, v_rateplan_id, NULL, 'Max Stay', NEW.max_stay::text, OLD.max_stay::text, v_log_group, v_action_type);
    END IF;

-- Auto Yield: log เมื่อ adjust หรือ override เปลี่ยนจริง (ราคา yield เปลี่ยน)
    IF (OLD.yield_adjust_value IS DISTINCT FROM NEW.yield_adjust_value)
       OR (OLD.yield_override IS DISTINCT FROM NEW.yield_override) THEN
        v_yield_new := public.fn_yield_effective_rate(NEW.rate_id, NEW.rate_date)::text;
        PERFORM public.sp_log_rate(v_user_name, NEW.rate_date, NEW.rate_id, v_roomtype_id, v_rateplan_id, NULL, 'Yield', v_yield_new, NULL, v_log_group, v_action_type);
    END IF;

RETURN NEW;
END

sp_tr_rate_daily_save_log

Description

Parameters

Definition