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;
    v_status       text;
    v_msg          text;
    v_posted       int;
    posted_folios  int := 0;
    error_msgs     text[] := '{}';
    mismatch_count int;
    mismatch_detail text;
    mismatch_data  jsonb;
    _sqlstate TEXT; _detail TEXT; _hint TEXT; _context TEXT; _msg_text TEXT;
    v_result json;
BEGIN
    post_date := COALESCE(post_date, fn_system_date());
    PERFORM sys.set_session('user_name', user_name);
    status := 'success';

/* ── Phase A: Guest folios (individual + group-member own-account) ──
       [Fix 1] rate not approved → collect error and continue (no longer fatal)
       other errors → collect and continue to next room
       ไม่รวมแขก departure = post_date (checkout today) */
    FOR v IN
        SELECT DISTINCT f.id AS folio_id, MIN(rm.room_number) AS room_number
        FROM folio f
            JOIN registration rg ON rg.folio_id = f.id
            LEFT JOIN room rm ON rm.id = rg.room_id
        WHERE f.folio_type = 'R' AND f.status = 'A' AND rg.status = 'I'
          AND (rg.departure > post_date OR rg.day_used)
          AND (
              NOT rg.room_posted
              OR EXISTS (
                  SELECT 1 FROM charge_schedule cs
                  WHERE cs.register_id = rg.id
                    AND cs.charge_date = post_date
                    AND cs.charge_type = 'requirement'
                    AND NOT cs.charge_to_booking AND NOT cs.posted
              )
          )
        GROUP BY f.id
        ORDER BY MIN(rm.room_number), f.id
    LOOP
        BEGIN
            SELECT s.status, s.msg, s.posted_count
            FROM sp_auto_post_folio(v.folio_id, user_name, post_date) s
            INTO v_status, v_msg, v_posted;

IF v_status LIKE 'error%' THEN
                error_msgs := array_append(error_msgs,
                    CASE WHEN v_msg ~ '^Room \S+:' THEN v_msg
                         ELSE format('Room %s: %s', COALESCE(v.room_number, '?'), v_msg)
                    END);
            ELSE
                posted_folios := posted_folios + IIF(coalesce(v_posted, 0) > 0, 1, 0);
            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_room_charges');
            error_msgs := array_append(error_msgs,
                format('Room %s: %s', COALESCE(v.room_number, '?'), v_result->>'result_msg'));
        END;
    END LOOP;

/* ── Phase B+C: Group folios ── */
    FOR v IN
        SELECT DISTINCT bk.folio_id
        FROM booking bk
            JOIN folio gf ON gf.id = bk.folio_id AND gf.status = 'A' AND gf.folio_type = 'G'
        WHERE bk.book_type = 'G' AND bk.status IN ('R','C','I')
          AND (
              EXISTS (SELECT 1 FROM registration rg
                      WHERE rg.booking_id = bk.id AND rg.status = 'I')
              OR EXISTS (
                  SELECT 1 FROM charge_schedule cs
                  WHERE cs.booking_id = bk.id AND cs.charge_date = post_date
                    AND cs.charge_type = 'requirement' AND cs.register_id IS NULL
                    AND cs.charge_to_booking AND NOT cs.posted
              )
          )
        ORDER BY bk.folio_id
    LOOP
        BEGIN
            SELECT s.status, s.msg, s.posted_count
            FROM sp_auto_post_folio(v.folio_id, user_name, post_date) s
            INTO v_status, v_msg, v_posted;

IF v_status LIKE 'error%' THEN
                error_msgs    := array_append(error_msgs, format('Folio %s: %s', v.folio_id, v_msg));
            ELSE
                posted_folios := posted_folios + IIF(coalesce(v_posted, 0) > 0, 1, 0);
            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_room_charges');
            error_msgs := array_append(error_msgs,
                format('Folio %s: %s', v.folio_id, v_result->>'result_msg'));
        END;
    END LOOP;

IF array_length(error_msgs, 1) > 0 THEN
        status := 'error';
        msg    := sys_msg_format('30505', E'%s folios posted, %s error(s):\n- %s',
            posted_folios::text,
            array_length(error_msgs, 1)::text,
            array_to_string(error_msgs, E'\n- '));
    ELSE
        status := 'success';
        msg    := IIF(posted_folios > 0,
            format('%s folios posted', posted_folios),
            sys_msg('30501', 'No transaction posted'));
    END IF;

/* ── Mismatch check (success path only) ──
       Query v_auto_post_result and warn admin if plan ≠ actual. */
    IF status = 'success' THEN
        SELECT count(*),
               string_agg(
                   format('folio %s (%s %s, booking %s): plan=%s actual=%s diff=%s',
                       folio_id, folio_type, coalesce(room_number, '?'),
                       coalesce(booking_id::text, '?'),
                       coalesce(plan_total, 0), coalesce(total_post, 0),
                       coalesce(total_post, 0) - coalesce(plan_total, 0)),
                   E'\n' ORDER BY folio_id),
               jsonb_agg(jsonb_build_object(
                   'folio_id',   folio_id,
                   'folio_type', folio_type,
                   'room_number', room_number,
                   'booking_id', booking_id,
                   'plan_total', coalesce(plan_total, 0),
                   'total_post', coalesce(total_post, 0),
                   'diff',       coalesce(total_post, 0) - coalesce(plan_total, 0)
               ) ORDER BY folio_id)
        INTO mismatch_count, mismatch_detail, mismatch_data
        FROM v_auto_post_result
        WHERE (match = false AND coalesce(plan_total, 0) > 0)
           OR (coalesce(plan_total, 0) = 0 AND coalesce(total_post, 0) > 0);

IF mismatch_count > 0 THEN
            PERFORM sp_log_warning(
                'sp_auto_post_room_charges',
                format('Auto-post mismatch: %s folio(s)' || E'\n' || '%s',
                       mismatch_count, mismatch_detail),
                error_data   := mismatch_data,
                user_name    := user_name,
                force_write  := true,
                notify_admin := true
            );
        END IF;
    END IF;

RAISE NOTICE E'----------------------------\n%\n', msg;
END;