aonestar.public

Parameters

Name	Type	Mode
folio_id	integer	IN
user_name	text	IN (DEFAULT NULL)
post_date	date	IN (DEFAULT NULL)
include_members	boolean	IN (DEFAULT false)
status	text	OUT
msg	text	OUT
posted_count	integer	OUT

Definition

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

SELECT f2.folio_type, f2.register_id, f2.status AS folio_status
    FROM folio f2
    WHERE f2.id = _folio_id
    INTO f;

IF NOT FOUND OR f.folio_status <> 'A' THEN
        status := 'error';
        msg    := format('Folio %s not found or not active', _folio_id);
        RETURN;
    END IF;

/* ═══════════════════════════════════════════════════════════════════
       folio_type = 'R'  —  guest folio (individual or group member own-account)
       ═══════════════════════════════════════════════════════════════════ */
    IF f.folio_type = 'R' THEN
        post_result := sp_auto_post(f.register_id, user_name);
        msg := post_result->>'result_msg';
        posted_count := coalesce((post_result->'result_data'->>'posted_count')::int, 0);
        IF post_result->>'result_code' = '4' THEN
            status := 'error';
        ELSE
            status := 'success';
        END IF;
        RETURN;
    END IF;

/* ═══════════════════════════════════════════════════════════════════
       folio_type = 'G'  —  group folio
       ═══════════════════════════════════════════════════════════════════ */
    IF f.folio_type <> 'G' THEN
        status := 'error';
        msg    := format('Folio %s: unsupported folio_type ''%s''', _folio_id, f.folio_type);
        RETURN;
    END IF;

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);

/* ── Phase B: Validate rate approved + inclusion <= room rate for CTB members ──
       ไม่รวมแขก departure = post_date (checkout today) */
    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
        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 AND bk.folio_id = _folio_id
            LEFT JOIN room rm    ON rm.id = s.room_id
        WHERE (rg.departure > post_date OR rg.day_used)
    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));
        ELSIF v.room < 0 AND NOT v.room_posted THEN
            error_msgs := array_append(error_msgs,
                format('Room %s: inclusion exceeds room rate (rate=%s, inclusion=%s).',
                       v.room_number, v.rate_amount::text, v.inc_total::text));
        END IF;
    END LOOP;

IF array_length(error_msgs, 1) > 0 THEN
        status := 'error';
        msg    := array_to_string(error_msgs, '; ');
        RETURN;
    END IF;

/* ── Phase B: Post room / meal / inclusive charges → group folio ──
       ไม่รวมแขก departure = post_date (checkout today) */
    FOR chg IN
        WITH src AS (
            SELECT s.register_id, s.room_id, rm.room_number::text AS room_number, s.charge_date,
                   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,
                   s.room_posted
            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 AND bk.folio_id = _folio_id
                JOIN folio gf        ON gf.id = _folio_id
                LEFT JOIN room rm    ON rm.id = s.room_id
                LEFT JOIN rate_plan rp ON rp.id = s.rateplan_id
            WHERE (rg.departure > post_date OR rg.day_used)
        ), lines AS (
            /* Room */
            SELECT _folio_id AS 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 AND NOT src.room_posted
            UNION ALL /* Extra bed */
            SELECT _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 AND NOT src.room_posted
            UNION ALL /* ABF (charge_date + 1) */
            SELECT _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 AND NOT src.room_posted
            UNION ALL /* Lunch (charge_date + 1) */
            SELECT _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 AND NOT src.room_posted
            UNION ALL /* Dinner */
            SELECT _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 AND NOT src.room_posted
            UNION ALL /* Inclusive items */
            SELECT _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');

IF post_result->>'result_code' = '4' THEN
                RAISE EXCEPTION SQLSTATE '90003'
                    USING MESSAGE = post_result->>'result_msg',
                          DETAIL  = 'folio_id='||_folio_id||', '||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;

/* [Fix ClickUp 86d3m5k10] Comp / zero-amount CTB members ไม่สร้าง line ข้างบน
       → ต้องเก็บเข้า array เพื่อ mark posted ด้วย ไม่งั้นค้าง Pending ถาวร
       เก็บเฉพาะ register ที่ไม่มีอะไรต้อง post จริงๆ (ทุก component = 0 และ
       ไม่มี inclusive item ค้าง post) — ห้องที่มี amount แต่ post ไม่สำเร็จยัง retry ได้เหมือนเดิม */
    all_register_ids := all_register_ids || ARRAY(
        SELECT s.register_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 AND bk.folio_id = _folio_id
        WHERE (rg.departure > post_date OR rg.day_used)
          AND s.room <= 0 AND s.extrabed <= 0 AND s.abf <= 0 AND s.lunch <= 0 AND s.dinner <= 0
          AND NOT EXISTS (
              SELECT 1 FROM json_to_recordset(s.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 NOT item.posted AND item.charge_rate > 0)
    );
    room_register_ids := room_register_ids || all_register_ids;

/* ── Mark room/inclusive posted for CTB members ── */
    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);

/* ── Phase C1: Per-booking requirements (register_id IS NULL) → group folio ──
       Only truly per-booking reqs: charge_to_booking=TRUE AND multiplier=1.
       Requirements with multiplier>1 belong at register-level (created during check-in);
       requirements with charge_to_booking=FALSE have no group-folio target.
       Also requires at least one in-house member: do not post for reserved/confirmed
       bookings with nobody checked in yet. */
    FOR chg IN
        SELECT b.id AS booking_id, b.folio_id AS target_folio_id, cs.charge_date,
               dp.id AS charge_dept, dp.code AS dept_code,
               SUM(cs.total_amount)::t_money AS charge_amount, 1 AS charge_qty,
               ARRAY_AGG(cs.id) AS charge_ids
        FROM charge_schedule cs
            JOIN booking b             ON b.id = cs.booking_id AND b.folio_id = _folio_id
            JOIN folio gf              ON gf.id = b.folio_id AND gf.status = 'A' AND gf.folio_type = 'G'
            LEFT JOIN guest_request rq ON rq.id = cs.request_id
            LEFT JOIN inclusion inc    ON inc.id = rq.inclusion_id
            LEFT JOIN department dp    ON dp.id = COALESCE(cs.charge_dept, rq.charge_dept, inc.charge_dept)
        WHERE cs.charge_date = post_date AND cs.charge_type = 'requirement' AND NOT cs.posted
            AND cs.register_id IS NULL AND cs.booking_id IS NOT NULL
            AND cs.charge_to_booking AND rq.multiplier = 1
            AND b.book_type = 'G' AND b.status IN ('R','C','I')
            AND EXISTS (SELECT 1 FROM registration rg2
                        WHERE rg2.booking_id = b.id AND rg2.status = 'I')
        GROUP BY b.id, b.folio_id, cs.charge_date, dp.id, dp.code
    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);

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 => null, charge_date => chg.charge_date, post_flag => 'A');
            IF post_result->>'result_code' = '4' THEN
                RAISE EXCEPTION SQLSTATE '90003'
                    USING MESSAGE = post_result->>'result_msg',
                          DETAIL  = 'folio_id='||_folio_id||', '||chg::text;
            END IF;
            posted_tran_id := (post_result->'result_data'->>'id')::int;
            posted_count   := posted_count + 1;
        END IF;

UPDATE charge_schedule cs SET posted = TRUE, tran_id = posted_tran_id
        WHERE cs.id = ANY(chg.charge_ids);
    END LOOP;

/* ── Phase C2: CTB requirements (per-room/per-pax, charge_to_booking=TRUE) → group folio ──
       ไม่รวมแขก departure = post_date (checkout today) */
    FOR chg IN
        WITH req AS (
            SELECT cs.id AS charge_id, b.folio_id AS target_folio_id, gf.split_room_charge,
                   rm.room_number::text AS room_number, rg.room_id, cs.charge_date,
                   dp.id AS charge_dept, dp.code AS dept_code,
                   cs.charge_amount, cs.charge_qty, cs.total_amount
            FROM charge_schedule cs
                JOIN registration rg ON rg.id = cs.register_id
                JOIN booking b       ON b.id = rg.booking_id AND b.folio_id = _folio_id
                JOIN folio gf        ON gf.id = b.folio_id AND gf.status = 'A' AND gf.folio_type = 'G'
                LEFT JOIN guest_request rq ON rq.id = cs.request_id
                LEFT JOIN inclusion inc    ON inc.id = rq.inclusion_id
                LEFT JOIN department dp    ON dp.id = COALESCE(cs.charge_dept, rq.charge_dept, inc.charge_dept)
                LEFT JOIN room rm          ON rm.id = rg.room_id
            WHERE cs.charge_date = post_date AND cs.charge_type = 'requirement' AND NOT cs.posted
                AND cs.register_id IS NOT NULL AND cs.charge_to_booking AND rg.status = 'I'
                AND (rg.departure > post_date OR rg.day_used)
        ), single_req AS (
            SELECT target_folio_id, charge_date, charge_dept, dept_code,
                   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(charge_id) AS charge_ids
            FROM req WHERE NOT split_room_charge AND charge_dept IS NOT NULL
            GROUP BY target_folio_id, charge_date, charge_dept, dept_code
        ), split_req AS (
            SELECT target_folio_id, charge_date, charge_dept, dept_code,
                   charge_qty, charge_amount,
                   1 AS room_count, room_number AS rooms,
                   ARRAY[charge_id] AS charge_ids
            FROM req WHERE split_room_charge AND charge_dept IS NOT NULL
        )
        SELECT * FROM single_req
        UNION ALL
        SELECT * FROM split_req
    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 => 'A');
            IF post_result->>'result_code' = '4' THEN
                RAISE EXCEPTION SQLSTATE '90003'
                    USING MESSAGE = post_result->>'result_msg',
                          DETAIL  = 'folio_id='||_folio_id||', '||chg::text;
            END IF;
            posted_tran_id := (post_result->'result_data'->>'id')::int;
            posted_count   := posted_count + 1;
        END IF;

UPDATE charge_schedule cs SET posted = TRUE, tran_id = posted_tran_id
        WHERE cs.id = ANY(chg.charge_ids);
    END LOOP;

/* ── Phase C3: CTB member own-account requirements → member own folio ──
       charge_schedule rows where charge_to_booking=FALSE belonging to CTB registrations
       (rg.charge_to_booking=TRUE) are posted to the member's own folio (rg.folio_id).
       ไม่รวมแขก departure = post_date (checkout today) */
    FOR chg IN
        SELECT rg.folio_id AS target_folio_id,
               rm.room_number::text AS room_number,
               cs.charge_date,
               dp.id AS charge_dept, dp.code AS dept_code,
               cs.charge_amount, cs.charge_qty, cs.total_amount,
               cs.id AS charge_id
        FROM charge_schedule cs
            JOIN registration rg ON rg.id = cs.register_id
            JOIN booking b       ON b.id = rg.booking_id AND b.folio_id = _folio_id
            LEFT JOIN guest_request rq ON rq.id = cs.request_id
            LEFT JOIN inclusion inc    ON inc.id = rq.inclusion_id
            LEFT JOIN department dp    ON dp.id = COALESCE(cs.charge_dept, rq.charge_dept, inc.charge_dept)
            LEFT JOIN room rm          ON rm.id = rg.room_id
        WHERE cs.charge_date = post_date
            AND cs.charge_type = 'requirement'
            AND NOT cs.charge_to_booking
            AND NOT cs.posted
            AND rg.charge_to_booking
            AND rg.status = 'I'
            AND (rg.departure > post_date OR rg.day_used)
            AND rg.folio_id IS NOT NULL
            AND dp.id IS NOT NULL
    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);

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      => 'Room '||chg.room_number,
                                              charge_date => chg.charge_date,
                                              post_flag   => 'A');
            IF post_result->>'result_code' = '4' THEN
                RAISE EXCEPTION SQLSTATE '90003'
                    USING MESSAGE = post_result->>'result_msg',
                          DETAIL  = 'Phase C3: CTB own-req folio='||chg.target_folio_id
                                    ||', charge_id='||chg.charge_id;
            END IF;
            posted_tran_id := (post_result->'result_data'->>'id')::int;
            posted_count   := posted_count + 1;
        END IF;

UPDATE charge_schedule SET posted = TRUE, tran_id = posted_tran_id
        WHERE id = chg.charge_id;
    END LOOP;

status := 'success';
    msg    := format('%s transactions posted (%s rooms)', posted_count, posted_rooms);

/* ── include_members: post each in-house member's own-account folio ── */
    IF include_members THEN
        FOR m IN
            SELECT DISTINCT rg.folio_id AS member_folio_id
            FROM registration rg
                JOIN booking bk ON bk.id = rg.booking_id AND bk.folio_id = _folio_id
            WHERE rg.status = 'I'
            ORDER BY rg.folio_id
        LOOP
            SELECT s.status, s.msg, s.posted_count
            FROM sp_auto_post_folio(m.member_folio_id, user_name, post_date, false) s
            INTO v_member_status, v_member_msg, v_member_posted;

posted_count := posted_count + coalesce(v_member_posted, 0);
            IF v_member_status LIKE 'error%' THEN
                member_msgs := array_append(member_msgs,
                    format('Member folio %s: %s', m.member_folio_id, v_member_msg));
            END IF;
        END LOOP;

IF array_length(member_msgs, 1) > 0 THEN
            status := 'error';
            msg    := concat_ws(E'\n', msg,
                'Member errors: '||array_to_string(member_msgs, '; '));
        END IF;
    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_folio',
                    jsonb_build_object('folio_id', folio_id, 'post_date', post_date));
    status := 'error';
    msg    := v_result->>'result_msg';
END;