aonestar.public

Parameters

Name	Type	Mode
folio_id	integer	IN
i_source	text	IN (DEFAULT 'any')

Definition

DECLARE
    v_folio_id  integer;
    _use_active boolean;
    result_json json;
BEGIN
    /* i_source: 'any' (default) = หา public ก่อน ไม่เจอจึง fallback ไป archive
                 'active'        = public เท่านั้น
                 'archive'       = backup เท่านั้น
    */
    v_folio_id := folio_id;

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;

WITH
    src_folio AS (
        SELECT id, status, close_date, close_time, close_user
        FROM folio WHERE id = v_folio_id AND _use_active
        UNION ALL
        SELECT id, status, close_date, close_time, close_user
        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.note,
               fi.closed, fi.charge_to_folio, fi.close_date, fi.close_time, fi.close_user, fi.close_shift
        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.note,
               fi.closed, fi.charge_to_folio, fi.close_date, fi.close_time, fi.close_user, fi.close_shift
        FROM backup.folio_items fi WHERE fi.folio_id = v_folio_id AND NOT _use_active
    ),
    src_transactions AS (
        SELECT t.id, t.folio_item, t.dept_id, t.charge_date, t.post_type, t.tran_type,
               t.description, t.qty, t.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.vat_issued, t.vat_id
        FROM transactions t WHERE t.folio_id = v_folio_id AND _use_active
        UNION ALL
        SELECT t.id, t.folio_item, t.dept_id, t.charge_date, t.post_type, t.tran_type,
               t.description, t.qty, t.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.vat_issued, t.vat_id
        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)
    ),
    dataset AS (
        SELECT i.item_id, i.folio_seq, i.folio_name, i.note,
            coalesce(i.close_date, f.close_date) as close_date,
            coalesce(i.close_time, f.close_time) as close_time,
            coalesce(i.close_user, f.close_user) as close_user,
            i.close_shift,
            NOT _use_active AS is_archived,
            i.charge_to_folio AS link_folio_id,
            coalesce(link_pub.folio_type, link_bak.folio_type) AS link_folio_type,
           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 'G' 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,
           (SELECT coalesce(array_agg(row_to_json(trn)), '{}')
            FROM (SELECT t.id, t.charge_date AS "date", t.post_type, t.tran_type, dep.code,
                    coalesce(t.description, dep."name") description, t.qty,
                    t.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,
                    not(t.voided OR t.transfer_out) AS active,
                    dep.vat_effect,
                    t.vat_issued,
                    t.vat_id,
                    (SELECT COALESCE(json_agg(log_data), '[]')
                     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
                    ) AS logs
                 FROM src_transactions t LEFT JOIN department dep ON dep.id = t.dept_id
                 WHERE (t.folio_item = i.item_id)
                 ORDER BY t.post_time
                 ) AS trn
            ) AS transactions
        FROM src_folio_items i
            LEFT JOIN src_folio f on f.id = i.fi_folio_id
            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
        WHERE i.closed or f.status = 'C'
        ORDER BY i.close_time, i.folio_seq
    )
    SELECT coalesce(json_agg(dataset), '[]') FROM dataset
    INTO result_json;

RETURN result_json;
END

sp_get_folio_history_js

Description

Parameters

Definition