aonestar.public

Parameters

Name	Type	Mode
folio_id	integer	IN
format	integer	IN (DEFAULT 1)

Definition

declare 
    result_json json;
    SYS_DATE date;
    is_folio_closed boolean;
BEGIN
    /* format:
       1 = default format, returns all transactions, use in guest's folio page
       2 = compact format, with exclude transferred out transactions, use in transaction transfer page
    */
    SYS_DATE := fn_system_date();
    is_folio_closed := (select f.status = 'C' from folio f where f.id = $1);

    WITH 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,
                    '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 cashier_log l 
                                   WHERE l.tran_id = t.id
                                  ) as log_data
                            )
               ) order by t.post_time, t.id) as transactions 
        from transactions t 
            LEFT JOIN department dep ON dep.id = t.dept_id
        WHERE (t.folio_id = $1) AND (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.folio_type AS link_folio_type,
            CASE link.folio_type 
                WHEN 'R' THEN 
                    (SELECT full_name 
                     FROM registration rg 
                     LEFT JOIN guest g ON g.id = rg.guest_id 
                     WHERE rg.id = link.register_id) 
                WHEN 'B' THEN
                    (SELECT full_name 
                     FROM booking bk 
                     LEFT JOIN guest g ON g.id = bk.guest_id 
                     WHERE bk.id = link.booking_id)  
                WHEN 'C' THEN 
                    (SELECT name 
                     FROM cash_sale cs 
                     WHERE cs.folio_id = link.id)
            END AS link_folio_name,
            t.transactions       
        FROM folio_items i 
            LEFT JOIN folio link ON link.id = i.charge_to_folio 
            LEFT JOIN trans t on t.folio_item_id = i.item_id
        WHERE i.folio_id = $1 AND NOT i.closed AND NOT is_folio_closed
    ), 
    dataset AS (
        SELECT 
            f.id AS folio_id, 
            f.folio_type, 
            f.status, 
            rg.id AS register_id, 
            iif(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.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(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", 
            (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 folio f 
            LEFT JOIN registration rg ON rg.id = f.register_id 
            LEFT JOIN booking bk ON bk.id = f.booking_id 
            LEFT JOIN cash_sale cs ON 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.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                                
        WHERE f.id = $1
    )
    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