aonestar.public

Parameters

Name	Type	Mode
post_date	date	IN (DEFAULT NULL)
user_name	text	IN (DEFAULT NULL)
status	text	OUT
msg	text	OUT

Definition

DECLARE
    v   record;
    chg record;
    default_room_dept int;
    extrabed_dept int;
    abf_dept int;
    lunch_dept int;
    dinner_dept int;
    post_result json;
    posted_tran_id int;
    posted_count int := 0;
    posted_rooms int := 0;
    _folio_seq int;
    _remark text;
    error_msgs text[] := '{}';
    failed_folios int[] := '{}';
    room_register_ids int[] := '{}';
    all_register_ids  int[] := '{}';
    _sqlstate TEXT; _detail TEXT; _hint TEXT; _context TEXT; _msg_text TEXT;
    v_result json;
BEGIN
    PERFORM sys.set_session('user_name', user_name);
    status    := 'success';
    msg       := '';
    post_date := COALESCE(post_date, fn_system_date());

default_room_dept := fn_sys_param('AUTO_POST', 'ROOM', null::int);
    extrabed_dept     := fn_sys_param('AUTO_POST', 'EXTRABED', default_room_dept);
    abf_dept          := fn_sys_param('AUTO_POST', 'ABF', null::int);
    lunch_dept        := fn_sys_param('AUTO_POST', 'LUNCH', null::int);
    dinner_dept       := fn_sys_param('AUTO_POST', 'DINNER', null::int);

/* ---- Validation: rate approved + inclusion ต้องไม่เกินค่าห้อง (ต่อ charge-to-booking member) ---- */
    FOR v IN
        SELECT s.register_id, rm.room_number, s.rate_amount, s.room, s.room_posted, s.approved,
               (s.abf_included + s.lunch_included + s.dinner_included + s.inclusive) AS inc_total,
               bk.folio_id AS target_folio_id
        FROM sp_rate_schedule_breakdown(post_date, posted := false, charge_to_booking := true) s
            JOIN registration rg ON rg.id = s.register_id
            JOIN booking bk      ON bk.id = rg.booking_id
            JOIN folio gf        ON gf.id = bk.folio_id AND gf.status = 'A' AND gf.folio_type = 'G'
            LEFT JOIN room rm    ON rm.id = s.room_id
    LOOP
        IF NOT v.room_posted AND NOT v.approved THEN
            error_msgs    := array_append(error_msgs, format('Room %s: rate not approved.', v.room_number));
            failed_folios := array_append(failed_folios, v.target_folio_id);
        ELSIF v.room < 0 AND NOT v.room_posted THEN
            error_msgs    := array_append(error_msgs,
                format('Room %s: inclusion amount exceeds room rate (rate=%s, inclusion=%s).',
                       v.room_number, v.rate_amount::text, v.inc_total::text));
            failed_folios := array_append(failed_folios, v.target_folio_id);
        END IF;
    END LOOP;

/* ---- Aggregate (single vs split ตาม group folio.split_room_charge) แล้ว post ---- */
    FOR chg IN
        WITH src AS (
            SELECT s.register_id, s.room_id, rm.room_number::text AS room_number, s.charge_date,
                   bk.folio_id AS target_folio_id,
                   gf.split_room_charge,
                   COALESCE(rp.room_charge_dept, default_room_dept) AS room_dept,
                   s.room, s.extrabed, s.abf, s.lunch, s.dinner, s.inclusive_items
            FROM sp_rate_schedule_breakdown(post_date, posted := false, charge_to_booking := true) s
                JOIN registration rg ON rg.id = s.register_id
                JOIN booking bk      ON bk.id = rg.booking_id
                JOIN folio gf        ON gf.id = bk.folio_id AND gf.status = 'A' AND gf.folio_type = 'G'
                LEFT JOIN room rm       ON rm.id = s.room_id
                LEFT JOIN rate_plan rp  ON rp.id = s.rateplan_id
            WHERE NOT (bk.folio_id = ANY(failed_folios))
        ), lines AS (
            /* Room */
            SELECT target_folio_id, register_id, room_id, room_number, split_room_charge,
                   room_dept AS charge_dept, charge_date, 'R'::char(1) AS post_flag, TRUE AS is_room,
                   1 AS qty, room AS amount, room AS total_amount
            FROM src WHERE room > 0
            UNION ALL /* Extra bed */
            SELECT target_folio_id, register_id, room_id, room_number, split_room_charge,
                   extrabed_dept, charge_date, 'R'::char(1), FALSE,
                   1, extrabed, extrabed
            FROM src WHERE extrabed > 0
            UNION ALL /* ABF (advance +1) */
            SELECT target_folio_id, register_id, room_id, room_number, split_room_charge,
                   abf_dept, charge_date + 1, 'R'::char(1), FALSE,
                   1, abf, abf
            FROM src WHERE abf > 0
            UNION ALL /* Lunch (advance +1) */
            SELECT target_folio_id, register_id, room_id, room_number, split_room_charge,
                   lunch_dept, charge_date + 1, 'R'::char(1), FALSE,
                   1, lunch, lunch
            FROM src WHERE lunch > 0
            UNION ALL /* Dinner */
            SELECT target_folio_id, register_id, room_id, room_number, split_room_charge,
                   dinner_dept, charge_date, 'R'::char(1), FALSE,
                   1, dinner, dinner
            FROM src WHERE dinner > 0
            UNION ALL /* Inclusive items */
            SELECT src.target_folio_id, src.register_id, src.room_id, src.room_number, src.split_room_charge,
                   item.charge_dept, item.charge_date, 'R'::char(1), FALSE,
                   item.qty, item.charge_rate, (item.qty * item.charge_rate)::t_money
            FROM src,
                 LATERAL json_to_recordset(src.inclusive_items)
                 AS item(inclusion_id int, name text, charge_date date, charge_dept int,
                         frequency int, qty int, charge_rate t_money, remark text, posted bool)
            WHERE src.inclusive_items IS NOT NULL AND NOT item.posted AND item.charge_rate > 0
        ), single_charge AS (
            SELECT target_folio_id, charge_date, charge_dept, post_flag,
                   1 AS charge_qty,
                   SUM(total_amount)::t_money AS charge_amount,
                   COUNT(DISTINCT room_id)::int AS room_count,
                   STRING_AGG(DISTINCT room_number, ',' ORDER BY room_number) AS rooms,
                   ARRAY_AGG(DISTINCT register_id) AS register_ids,
                   BOOL_OR(is_room) AS has_room
            FROM lines
            WHERE NOT split_room_charge AND charge_dept IS NOT NULL
            GROUP BY target_folio_id, charge_date, charge_dept, post_flag
        ), split_charge AS (
            SELECT target_folio_id, charge_date, charge_dept, post_flag,
                   qty AS charge_qty, amount AS charge_amount,
                   1 AS room_count, room_number AS rooms,
                   ARRAY[register_id] AS register_ids, is_room AS has_room
            FROM lines
            WHERE split_room_charge AND charge_dept IS NOT NULL
        )
        SELECT * FROM single_charge
        UNION ALL
        SELECT * FROM split_charge
    LOOP
        posted_tran_id := NULL;
        IF (chg.charge_qty > 0) AND (chg.charge_amount <> 0) THEN
            _folio_seq := fn_get_target_folio_seq(chg.target_folio_id, chg.charge_dept, true);
            _remark    := IIF(chg.room_count = 1, 'Room '||chg.rooms, chg.room_count||' rooms');

post_result := sp_post_transaction(
                chg.target_folio_id, chg.charge_dept, chg.charge_amount, chg.charge_qty,
                folio_seq   => _folio_seq,
                user_name   => user_name,
                reference   => 'AUTO',
                remark      => _remark,
                charge_date => chg.charge_date,
                post_flag   => chg.post_flag
            );

IF post_result->>'result_code' = '4' THEN
                RAISE EXCEPTION SQLSTATE '90003'
                    USING MESSAGE = post_result->>'result_msg',
                          DETAIL  = 'folio_id='||COALESCE(chg.target_folio_id::text,'null')||', '||chg::text;
            END IF;

posted_tran_id := (post_result->'result_data'->>'id')::int;
            posted_count   := posted_count + 1;
            IF chg.has_room THEN
                posted_rooms      := posted_rooms + chg.room_count;
                room_register_ids := room_register_ids || chg.register_ids;
            END IF;
        END IF;
        all_register_ids := all_register_ids || chg.register_ids;
    END LOOP;

/* ---- Mark posted (สำหรับคืน post_date เท่านั้น) ---- */
    UPDATE registration_rates rr
    SET posted = TRUE, inclusive_posted = TRUE
    WHERE rr.register_id = ANY(all_register_ids) AND rr.charge_date = post_date;

UPDATE registration rg
    SET room_posted = TRUE
    WHERE rg.id = ANY(room_register_ids);

IF array_length(error_msgs, 1) > 0 THEN
        status := 'error';
        msg := format('%s group transactions posted, %s error(s): %s',
                      posted_count, array_length(error_msgs, 1), array_to_string(error_msgs, '; '));
    ELSE
        msg := format('%s group transactions posted (%s rooms)', posted_count, posted_rooms);
    END IF;

EXCEPTION WHEN OTHERS THEN
    GET STACKED DIAGNOSTICS
        _sqlstate = RETURNED_SQLSTATE, _msg_text = MESSAGE_TEXT,
        _detail = PG_EXCEPTION_DETAIL, _hint = PG_EXCEPTION_HINT, _context = PG_EXCEPTION_CONTEXT;
    v_result := fn_handle_error(_sqlstate, _msg_text, _detail, _hint, _context, 'sp_auto_post_group_rooms');
    status := 'error';
    msg    := v_result->>'result_msg';
END;