aonestar.public

Parameters

Name	Type	Mode
i_search_text	text	IN (DEFAULT NULL)
i_folio_type	character	IN (DEFAULT NULL)
i_trn_codes	text[]	IN (DEFAULT NULL)
i_start_date	date	IN (DEFAULT NULL)
i_end_date	date	IN (DEFAULT NULL)
i_post_users	text[]	IN (DEFAULT NULL)
i_post_shift	character	IN (DEFAULT NULL)
i_room_number	text	IN (DEFAULT NULL)
i_folio_id	integer	IN (DEFAULT NULL)
i_folio_seq	integer	IN (DEFAULT NULL)
i_qty	integer	IN (DEFAULT NULL)
i_amount	t_money	IN (DEFAULT NULL)
i_amount_op	text	IN (DEFAULT '=')
flag_post	boolean	IN (DEFAULT true)
flag_settle	boolean	IN (DEFAULT true)
flag_void	boolean	IN (DEFAULT false)
flag_split	boolean	IN (DEFAULT false)
flag_transfer_in	boolean	IN (DEFAULT false)
flag_transfer_out	boolean	IN (DEFAULT false)

Definition

WITH trn AS (
        SELECT t.id, 
            t.charge_date AS "date", t.post_type, t.tran_type, d.code, 
            t.description, --coalesce(t.description, dep."name") description,  
            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,
            not(t.voided OR t.transfer_out) AS active,

            fi.folio_id, fi.folio_seq, f.folio_type,            
            CASE f.folio_type
               WHEN 'R' THEN iif(rg.status='I','INH','C/O')
               WHEN 'B' THEN fn_lookup(bk_fo.status::text, ARRAY['R','C','W','I','N','O','X'], ARRAY['RSV','CFM','TTT','INH','NOS','C/O','CLX'])
               WHEN 'C' THEN iif(f.status ='C','CLOSED','ACTIVE')
            END AS folio_status, 
            --iif(f.status ='C','CLOSED','ACTIVE') AS folio_status, 
            f.register_id, coalesce(f.booking_id, rg.booking_id) AS booking_id, rm.room_number, 
            CASE f.folio_type
                WHEN 'R' THEN g.full_name 
                WHEN 'B' THEN bk_fo.booking_name 
                WHEN 'C' THEN cs."name" 
            END AS folio_name,
            bk_rg.booking_name, 
            CASE f.folio_type
                WHEN 'R' THEN rg.arrival
                WHEN 'B' THEN bk_fo.arrival 
                WHEN 'C' THEN f.open_date 
            END AS arrival, 
            CASE f.folio_type
                WHEN 'R' THEN rg.departure
                WHEN 'B' THEN bk_fo.departure 
                WHEN 'C' THEN f.close_date  
            END AS departure, 
            cn."name" AS channel, 
            
--            json_build_object(
--              'folio_id', fi.folio_id,
--              'folio_seq', fi.folio_seq,
--              'folio_type', f.folio_type,
--              'status', CASE f.folio_type
--                            WHEN 'R' THEN iif(rg.status='I','INH','C/O')
--                            WHEN 'B' THEN fn_lookup(bk_fo.status::text, ARRAY['R','C','W','I','N','O','X'], ARRAY['RSV','CFM','TTT','INH','NOS','C/O','CLX'])
--                            WHEN 'C' THEN iif(f.status ='C','CLOSED','ACTIVE')
--                        END ,
--              'register_id', f.register_id,
--              'booking_id', coalesce(f.booking_id, rg.booking_id),
--              'room_number', rm.room_number, 
--              'name', CASE f.folio_type
--                          WHEN 'R' THEN g.full_name 
--                          WHEN 'B' THEN bk_fo.booking_name 
--                          WHEN 'C' THEN cs."name" 
--                      END,
--              'booking_name', bk_rg.booking_name,
--              'arrival', CASE f.folio_type
--                              WHEN 'R' THEN rg.arrival
--                              WHEN 'B' THEN bk_fo.arrival 
--                              WHEN 'C' THEN f.open_date 
--                         END,
--              'departure', CASE f.folio_type
--                              WHEN 'R' THEN rg.departure
--                              WHEN 'B' THEN bk_fo.departure 
--                              WHEN 'C' THEN f.close_date  
--                           END,
--              'channel', cn."name" 
--            ) AS folio,

            (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 cashier_log l 
                    WHERE l.tran_id  = t.id
                    ) AS log_data
            ) AS logs 
        FROM transactions t 
            LEFT JOIN department d ON d.id = t.dept_id 
            LEFT JOIN folio_items fi ON fi.item_id = t.folio_item 
            LEFT JOIN folio f ON f.id = fi.folio_id 
            LEFT JOIN registration rg ON rg.id = f.register_id 
            LEFT JOIN booking bk_fo ON bk_fo.id = f.booking_id
            LEFT JOIN booking bk_rg ON bk_rg.id = rg.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 guest g ON g.id = rg.guest_id 
            LEFT JOIN channel cn ON cn.id = CASE f.folio_type
                                                WHEN 'R' THEN rg.channel_id 
                                                WHEN 'B' THEN bk_fo.channel_id 
                                                WHEN 'C' THEN cs.channel_id  
                                            END
        WHERE ((COALESCE(flag_post,TRUE) AND t.post_type = 'P') or (COALESCE(flag_settle,TRUE) AND t.post_type = 'S'))          
            --(i_post_type IS NULL OR i_post_type = t.post_type)
            AND (NOT COALESCE(flag_void,FALSE) OR t.voided)
            AND (NOT COALESCE(flag_split,FALSE) OR t.splitted)
            AND (NOT COALESCE(flag_transfer_in,FALSE) OR t.transfer_in)
            AND (NOT COALESCE(flag_transfer_out,FALSE) OR t.transfer_out)
--          ((COALESCE(flag_post,TRUE) IS FALSE AND t.post_type = 'P')
--              OR (COALESCE(flag_settle,TRUE) AND t.post_type = 'S')
--              OR (COALESCE(flag_void,TRUE) AND t.voided)
--              OR (COALESCE(flag_split,TRUE) AND t.splitted)
--              OR (COALESCE(flag_transfer_in,TRUE) AND t.transfer_in)
--              OR (COALESCE(flag_transfer_out,TRUE) AND t.transfer_out)
--          )
            AND (i_folio_type IS NULL OR i_folio_type = f.folio_type)
            AND (i_trn_codes IS NULL OR d.code = ANY(i_trn_codes))
            AND (i_start_date IS NULL OR t.charge_date >= i_start_date) 
            AND (i_end_date IS NULL OR t.charge_date <= i_end_date) 
            AND (i_post_users IS NULL OR t.post_user = ANY(i_post_users))
            AND (i_post_shift IS NULL OR i_post_shift = t.post_shift)
            AND (i_room_number IS NULL OR i_room_number = rm.room_number)
            AND (i_folio_id IS NULL OR i_folio_id = fi.folio_id)
            AND (i_folio_seq IS NULL OR i_folio_seq = fi.folio_seq)
            AND (i_qty IS NULL OR i_qty = t.qty)
            --AND (NOT COALESCE(i_only_active,FALSE) OR t.active)
            AND (i_amount IS NULL   
                OR (COALESCE(i_amount_op,'=') = '=' AND (i_amount = t.amount))
                OR (i_amount_op = '>'  AND t.amount > i_amount)
                OR (i_amount_op = '>=' AND t.amount >= i_amount)
                OR (i_amount_op = '<'  AND t.amount < i_amount)
                OR (i_amount_op = '<=' AND t.amount <= i_amount)
            )
            AND (i_search_text IS NULL OR i_search_text = '' 
                OR t.description ILIKE '%'||i_search_text||'%'
                OR rm.room_number = i_search_text
                OR f.id::text = i_search_text
                OR f.register_id::text = i_search_text
                OR f.booking_id::text = i_search_text
                OR g.full_name ILIKE '%'||i_search_text||'%'
                OR cs."name" ILIKE '%'||i_search_text||'%'
                OR bk_fo.booking_name ILIKE '%'||i_search_text||'%'
                OR bk_rg.booking_name ILIKE '%'||i_search_text||'%'
            )       
        ORDER BY t.post_time
    ), summary AS (
        SELECT COUNT(IIF(NOT transfer_out, id, NULL)) AS post_count,
            COALESCE(SUM(IIF(NOT transfer_out, qty, 0)), 0) gross_qty, 
            COALESCE(SUM(IIF(NOT transfer_out, iif(tran_type='D',total_amount,-total_amount), 0)), 0) gross_amount,
            --SUM(qty) AS gross_qty,
            --SUM(IIF(tran_type='D', total_amount, -total_amount)) AS gross_amount,
            COALESCE(SUM(IIF(NOT transfer_out AND voided, qty, 0)), 0) AS correct_qty,
            COALESCE(SUM(IIF(NOT transfer_out AND voided, IIF(tran_type='D', total_amount, -total_amount), 0)), 0) AS correct_amount,
            COALESCE(SUM(IIF(active, qty, 0)), 0) AS net_qty,
            COALESCE(SUM(IIF(active, IIF(tran_type='D', total_amount, -total_amount), 0)), 0) AS net_amount         
        FROM trn 
        --WHERE NOT transfer_out
    )
    SELECT json_build_object(
        'summary', (SELECT row_to_json(summary) FROM summary),
        'transactions', (SELECT json_agg(trn) FROM trn)
    )

sp_search_transaction_js

Parameters

Definition