aonestar.public

Parameters

Name	Type	Mode
folio_id	integer	IN
format	integer	IN (DEFAULT 1)
i_source	text	IN (DEFAULT 'active')

Definition

DECLARE
    result_json json;
    SYS_DATE date;
    is_folio_closed boolean;
    _use_active boolean;
    v_folio_id integer;
BEGIN
    /* format:
       1 = default format, returns all transactions, use in guest's folio page
       2 = compact format, excluding transferred out transactions, use in transaction transfer page
       i_source: 'active' (default), 'archive', 'any'
    */
    v_folio_id := folio_id;
    SYS_DATE := fn_system_date();

IF i_source = 'any' THEN
        _use_active := EXISTS (SELECT 1 FROM folio WHERE id = v_folio_id);
    ELSE
        _use_active := (i_source <> 'archive');
    END IF;

IF _use_active THEN
        is_folio_closed := (SELECT f.status = 'C' FROM folio f WHERE f.id = v_folio_id);
    ELSE
        -- For archived folios, always show items (folio is closed but we want to display them)
        is_folio_closed := false;
    END IF;

WITH
    src_folio AS (
        SELECT id, folio_type, status, register_id, booking_id, folio_pattern_id, folio_locked, open_date, close_date
        FROM folio WHERE id = v_folio_id AND _use_active
        UNION ALL
        SELECT id, folio_type, status, register_id, booking_id, folio_pattern_id, folio_locked, open_date, close_date
        FROM backup.folio WHERE id = v_folio_id AND NOT _use_active
    ),
    src_folio_items AS (
        SELECT fi.item_id, fi.folio_id AS fi_folio_id, fi.folio_seq, fi.folio_name, fi.balance, fi.note, fi.closed, fi.charge_to_folio,
               fi.vat_effect_amt, fi.vat_issued_amt, fi.vat_nonvat_amt
        FROM folio_items fi WHERE fi.folio_id = v_folio_id AND _use_active
        UNION ALL
        SELECT fi.item_id, fi.folio_id AS fi_folio_id, fi.folio_seq, fi.folio_name, fi.balance, fi.note, fi.closed, fi.charge_to_folio,
               fi.vat_effect_amt, fi.vat_issued_amt, fi.vat_nonvat_amt
        FROM backup.folio_items fi WHERE fi.folio_id = v_folio_id AND NOT _use_active
    ),
    src_transactions AS (
        SELECT t.id, t.folio_id AS t_folio_id, t.folio_item, t.dept_id, t.post_type, t.tran_type,
               t.charge_date, t.qty, t.amount, t.total_amount, t.post_time, t.post_shift, t.post_user,
               t.reference, t.itemizers, t.remark, t.voided, t.splitted, t.splitted_src,
               t.transfer_in, t.transfer_src, t.transfer_out, t.transfer_des,
               t.active, t.vat_issued, t.vat_id, t.description
        FROM transactions t WHERE t.folio_id = v_folio_id AND _use_active
        UNION ALL
        SELECT t.id, t.folio_id AS t_folio_id, t.folio_item, t.dept_id, t.post_type, t.tran_type,
               t.charge_date, t.qty, t.amount, t.total_amount, t.post_time, t.post_shift, t.post_user,
               t.reference, t.itemizers, t.remark, t.voided, t.splitted, t.splitted_src,
               t.transfer_in, t.transfer_src, t.transfer_out, t.transfer_des,
               t.active, t.vat_issued, t.vat_id, t.description
        FROM backup.transactions t WHERE t.folio_id = v_folio_id AND NOT _use_active
    ),
    src_cashier_log AS (
        SELECT id, tran_id, log_user, log_type, log_text, log_date, log_time, log_shift
        FROM cashier_log WHERE _use_active
            AND tran_id IN (SELECT id FROM src_transactions)
        UNION ALL
        SELECT id, tran_id, log_user, log_type, log_text, log_date, log_time, log_shift
        FROM backup.cashier_log WHERE NOT _use_active
            AND tran_id IN (SELECT id FROM src_transactions)
    ),
    src_registration AS (
        SELECT id, booking_id FROM registration WHERE id = (SELECT register_id FROM src_folio LIMIT 1) AND _use_active
        UNION ALL
        SELECT id, booking_id FROM backup.registration WHERE id = (SELECT register_id FROM src_folio LIMIT 1) AND NOT _use_active
    ),
    src_booking AS (
        SELECT id, guest_id, booking_name, book_type, arrival, departure, channel_id,
               credit_limit, note
        FROM booking WHERE id = (SELECT booking_id FROM src_folio LIMIT 1) AND _use_active
        UNION ALL
        SELECT id, guest_id, booking_name, book_type, arrival, departure, channel_id,
               credit_limit, note
        FROM backup.booking WHERE id = (SELECT booking_id FROM src_folio LIMIT 1) AND NOT _use_active
    ),
    src_cash_sale AS (
        SELECT cs.folio_id AS cs_folio_id, cs.name, cs.address1, cs.address2, cs.company, cs.note, cs.channel_id
        FROM cash_sale cs WHERE cs.folio_id = v_folio_id AND _use_active
        UNION ALL
        SELECT cs.folio_id AS cs_folio_id, cs.name, cs.address1, cs.address2, cs.company, cs.note, cs.channel_id
        FROM backup.cash_sale cs WHERE cs.folio_id = v_folio_id AND NOT _use_active
    ),
    trans AS (
        SELECT t.folio_item AS folio_item_id,
               sum(iif(t.active AND t.charge_date > SYS_DATE, t.total_amount, 0.0))::t_money AS advance_post_amt,
               json_agg(json_build_object(
                    'id', t.id, 'date', t.charge_date, 'post_type', t.post_type, 'tran_type', t.tran_type,
                    'code', dep.code, 'description', coalesce(t.description, dep."name"),
                    'qty', t.qty, 'amount', t.amount, 'total_amount', t.total_amount,
                    'post_time', t.post_time, 'post_shift', t.post_shift, 'post_user', t.post_user,
                    'reference', t.reference, 'itemizers', t.itemizers, 'remark', t.remark,
                    'voided', t.voided AND NOT t.transfer_out, 'splitted', t.splitted,
                    'splitted_src', t.splitted_src, 'transfer_in', t.transfer_in,
                    'transfer_src', t.transfer_src, 'transfer_out', t.transfer_out,
                    'transfer_des', t.transfer_des,
                    'advance_post', t.charge_date > SYS_DATE, 'active', t.active,
                    'vat_effect', dep.vat_effect, 'vat_issued', t.vat_issued, 'vat_id', t.vat_id,
                    'logs', (SELECT COALESCE(json_agg(log_data ORDER BY id), '[]')
                             FROM (SELECT id, log_user AS user_name, log_type, log_text,
                                         log_date, log_time, log_shift AS shift
                                   FROM src_cashier_log l WHERE l.tran_id = t.id) AS log_data)
               ) ORDER BY t.post_time, t.id) AS transactions
        FROM src_transactions t
            LEFT JOIN department dep ON dep.id = t.dept_id
        WHERE (format <> 2 OR t.transfer_out = FALSE)
        GROUP BY 1
    ),
    items AS (
        SELECT i.item_id, i.folio_seq, i.folio_name, i.balance, t.advance_post_amt, i.note, i.closed,
            i.charge_to_folio AS link_folio_id,
            link_pub.folio_type AS link_folio_type,
            COALESCE(link_pub.folio_type, link_bak.folio_type) AS link_folio_type_resolved,
            CASE COALESCE(link_pub.folio_type, link_bak.folio_type)
                WHEN 'R' THEN COALESCE(
                    (SELECT full_name FROM registration rg LEFT JOIN guest g ON g.id = rg.guest_id WHERE rg.id = COALESCE(link_pub.register_id, link_bak.register_id)),
                    (SELECT full_name FROM backup.registration rg LEFT JOIN guest g ON g.id = rg.guest_id WHERE rg.id = COALESCE(link_pub.register_id, link_bak.register_id))
                )
                WHEN 'B' THEN COALESCE(
                    (SELECT full_name FROM booking bk LEFT JOIN guest g ON g.id = bk.guest_id WHERE bk.id = COALESCE(link_pub.booking_id, link_bak.booking_id)),
                    (SELECT full_name FROM backup.booking bk LEFT JOIN guest g ON g.id = bk.guest_id WHERE bk.id = COALESCE(link_pub.booking_id, link_bak.booking_id))
                )
                WHEN 'C' THEN COALESCE(
                    (SELECT name FROM cash_sale cs WHERE cs.folio_id = i.charge_to_folio),
                    (SELECT name FROM backup.cash_sale cs WHERE cs.folio_id = i.charge_to_folio)
                )
            END AS link_folio_name,
            i.vat_effect_amt, i.vat_issued_amt, i.vat_nonvat_amt,
            t.transactions
        FROM src_folio_items i
            LEFT JOIN folio link_pub ON link_pub.id = i.charge_to_folio
            LEFT JOIN backup.folio link_bak ON link_bak.id = i.charge_to_folio AND link_pub.id IS NULL
            LEFT JOIN trans t ON t.folio_item_id = i.item_id
        WHERE (NOT i.closed OR NOT _use_active) AND NOT is_folio_closed
    ),
    rg_src AS (
        SELECT rg.id, rg.booking_id, rg.room_id, rg.adult, rg.child, rg.infant, rg.extra_adult,
               rg.extra_child, rg.extra_infant, rg.arrival, rg.departure, rg.credit_limit,
               rg.payment_method, rg.channel_id, rg.rateplan_id, rg.note
        FROM registration rg WHERE rg.id = (SELECT register_id FROM src_folio LIMIT 1) AND _use_active
        UNION ALL
        SELECT rg.id, rg.booking_id, rg.room_id, rg.adult, rg.child, rg.infant, rg.extra_adult,
               rg.extra_child, rg.extra_infant, rg.arrival, rg.departure, rg.credit_limit,
               rg.payment_method, rg.channel_id, rg.rateplan_id, rg.note
        FROM backup.registration rg WHERE rg.id = (SELECT register_id FROM src_folio LIMIT 1) AND NOT _use_active
    ),
    dataset AS (
        SELECT
            f.id AS folio_id, f.folio_type, f.status,
            rg.id AS register_id,
            iif(f.folio_type='B', bk.id, rg.booking_id) AS booking_id,
            rg.room_id, rm.room_number,
            iif(f.folio_type = 'B', bk.guest_id, rg_full.guest_id) AS guest_id,
            bk.book_type, f.folio_pattern_id, fp."name" AS folio_pattern_name,
            CASE f.folio_type
                WHEN 'R' THEN g_rg.full_name WHEN 'B' THEN bk.booking_name WHEN 'C' THEN cs.name
            END AS "name",
            CASE f.folio_type WHEN 'R' THEN rg.arrival WHEN 'B' THEN bk.arrival WHEN 'C' THEN f.open_date END AS arrival,
            CASE f.folio_type WHEN 'R' THEN rg.departure WHEN 'B' THEN bk.departure WHEN 'C' THEN f.close_date END AS departure,
            CASE f.folio_type WHEN 'R' THEN rg.departure-rg.arrival WHEN 'B' THEN bk.departure-bk.arrival WHEN 'C' THEN f.close_date-f.open_date END AS night,
            rg.adult, rg.child, rg.infant, rg.extra_adult, rg.extra_child, rg.extra_infant,
            iif(f.folio_type='B', bk.credit_limit, rg.credit_limit) AS credit_limit,
            rg.payment_method,
            ch.id AS channel_id, ch.name AS channel_name,
            CASE f.folio_type WHEN 'R' THEN g_rg.address1 WHEN 'B' THEN g_bk.address1 WHEN 'C' THEN cs.address1 END AS address1,
            CASE f.folio_type WHEN 'R' THEN g_rg.address2 WHEN 'B' THEN g_bk.address2 WHEN 'C' THEN cs.address2 END AS address2,
            CASE f.folio_type WHEN 'R' THEN g_rg.company WHEN 'B' THEN g_bk.company WHEN 'C' THEN cs.company END AS company,
            CASE f.folio_type WHEN 'R' THEN rg.note WHEN 'B' THEN bk.note WHEN 'C' THEN cs.note END AS note,
            f.folio_locked AS "locked",
            NOT _use_active AS is_archived,
            (SELECT sum(balance) FROM items WHERE folio_seq IN (1,2,3,4)) AS folio_balance,
            (SELECT row_to_json(items) FROM items WHERE folio_seq = 1) AS folio1,
            (SELECT row_to_json(items) FROM items WHERE folio_seq = 2) AS folio2,
            (SELECT row_to_json(items) FROM items WHERE folio_seq = 3) AS folio3,
            (SELECT row_to_json(items) FROM items WHERE folio_seq = 4) AS folio4,
            rp.name AS rate_plan
        FROM src_folio f
            LEFT JOIN rg_src rg ON rg.id = f.register_id
            LEFT JOIN registration rg_full ON rg_full.id = f.register_id  -- for guest_id when active
            LEFT JOIN src_booking bk ON bk.id = f.booking_id
            LEFT JOIN src_cash_sale cs ON cs.cs_folio_id = f.id
            LEFT JOIN room rm ON rm.id = rg.room_id
            LEFT JOIN folio_pattern fp ON fp.id = f.folio_pattern_id
            LEFT JOIN guest g_rg ON g_rg.id = rg_full.guest_id
            LEFT JOIN guest g_bk ON g_bk.id = bk.guest_id
            LEFT JOIN channel ch ON ch.id = CASE f.folio_type WHEN 'R' THEN rg.channel_id WHEN 'B' THEN bk.channel_id ELSE cs.channel_id END
            LEFT JOIN rate_plan rp ON rp.id = rg.rateplan_id
    )
    SELECT
        CASE format
            WHEN 2 THEN row_to_json(dataset)
            ELSE (SELECT row_to_json(obj)
                  FROM (SELECT dataset.*, sp_get_guest_js(guest_id) AS guest) AS obj)
        END
    FROM dataset
    INTO result_json;

RETURN result_json;
END

sp_get_folio_details_js

Parameters

Definition