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)
i_page	integer	IN (DEFAULT NULL)
i_limit	integer	IN (DEFAULT NULL)
i_sort_field	text	IN (DEFAULT NULL)
i_sort_order	text	IN (DEFAULT NULL)
flag_active	boolean	IN (DEFAULT NULL)

Definition

DECLARE
    v_result json;
    v_summary json;
    v_transactions json;
    v_sql text;
    v_limit_clause text := '';
    v_order_by text;
    v_offset integer := 0;
    v_page integer ;
    v_limit integer;
    v_total_rows integer;
   
begin
    
    ------ Create by. T.Supol 2025-03-24 -------------------
    
    -- Set pagination parameters
    IF i_page IS NOT NULL AND i_limit IS NOT NULL THEN
        v_offset := (i_page - 1) * i_limit;
        v_limit_clause := format('LIMIT %s', i_limit);
    END IF;
   
    v_page := COALESCE(i_page, 1);
    v_limit := COALESCE(i_limit, 20);
    
    -- Set ordering based on sort field and direction
    v_order_by := CASE 
        WHEN i_sort_field = 'id' THEN 't.id'
        WHEN i_sort_field = 'date' THEN 't.charge_date'
        WHEN i_sort_field = 'post_type' THEN 't.post_type'
        WHEN i_sort_field = 'tran_type' THEN 't.tran_type'
        WHEN i_sort_field = 'code' THEN 'd.code'
        WHEN i_sort_field = 'description' THEN 't.description'
        WHEN i_sort_field = 'qty' THEN 't.qty'
        WHEN i_sort_field = 'amount' THEN 't.amount'
        WHEN i_sort_field = 'total_amount' THEN 't.total_amount'
        WHEN i_sort_field = 'post_time' THEN 't.post_time'
        WHEN i_sort_field = 'post_shift' THEN 't.post_shift'
        WHEN i_sort_field = 'post_user' THEN 't.post_user'
        WHEN i_sort_field = 'reference' THEN 't.reference'
        WHEN i_sort_field = 'remark' THEN 't.remark'
        WHEN i_sort_field = 'folio_id' THEN 'fi.folio_id'
        WHEN i_sort_field = 'folio_seq' THEN 'fi.folio_seq'
        WHEN i_sort_field = 'folio_type' THEN 'f.folio_type'
        WHEN i_sort_field = 'room_number' THEN 'rm.room_number'
        WHEN i_sort_field = 'channel' THEN 'cn.name'
        ELSE 't.post_time'
    END;
    
    -- Add sort direction
    IF lower(COALESCE(i_sort_order, 'asc')) = 'desc' THEN
        v_order_by := v_order_by || ' DESC';
    ELSE
        v_order_by := v_order_by || ' ASC';
    END IF;
    
    -- First get summary data with minimal fields
    v_sql := format($$
        WITH trn AS (
            SELECT 
                t.id,
                t.tran_type,
                t.qty,
                t.total_amount,
                t.voided,
                t.transfer_out,
                NOT(t.voided OR t.transfer_out) AS active
            FROM transactions t
            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 room rm ON rm.id = rg.room_id
            LEFT JOIN department d ON d.id = t.dept_id
            WHERE 
                ((COALESCE(%L,TRUE) AND t.post_type = 'P') 
            OR (COALESCE(%L,TRUE) AND t.post_type = 'S'))
            AND (NOT COALESCE(%L,FALSE) OR t.voided)
            AND (NOT COALESCE(%L,FALSE) OR t.splitted)
            AND (NOT COALESCE(%L,FALSE) OR t.transfer_in)
            AND (NOT COALESCE(%L,FALSE) OR t.transfer_out)
            AND (%L IS NULL OR f.folio_type = %L)
            AND (%L IS NULL OR d.code = ANY(%L))
            AND (%L IS NULL OR t.charge_date >= %L)
            AND (%L IS NULL OR t.charge_date <= %L)
            AND (%L IS NULL OR t.post_user = ANY(%L))
            AND (%L IS NULL OR t.post_shift = %L)
            AND (%L IS NULL OR rm.room_number = %L)
            AND (%L IS NULL OR fi.folio_id = %L)
            AND (%L IS NULL OR fi.folio_seq = %L)
            AND (%L IS NULL OR t.qty = %L)
            AND (%L IS NULL   
                OR (COALESCE(%L,'=') = '=' AND t.amount = %L)
                OR (%L = '>'  AND t.amount > %L)
                OR (%L = '>=' AND t.amount >= %L)
                OR (%L = '<'  AND t.amount < %L)
                OR (%L = '<=' AND t.amount <= %L)
            )
            AND (%L IS NULL OR %L = '' 
                OR t.description ILIKE '%%'||%L||'%%'
                or  rm.room_number = %L
                OR f.id::text = %L
                OR f.register_id::text = %L
                OR f.booking_id::text = %L
            )
            and (%L is null or t.active = %L)
        ),
        summary AS (
            SELECT 
                COUNT(IIF(NOT transfer_out, id, NULL)) AS post_count,
                COALESCE(SUM(IIF(NOT transfer_out, qty, 0)), 0) AS gross_qty,
                COALESCE(SUM(IIF(NOT transfer_out, IIF(tran_type='D',total_amount,-total_amount), 0)), 0) 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
        )
        SELECT row_to_json(summary) FROM summary $$
        , flag_post, flag_settle, 
          flag_void, flag_split, flag_transfer_in, flag_transfer_out,
          i_folio_type, i_folio_type, 
          i_trn_codes, i_trn_codes, 
          i_start_date, i_start_date, 
          i_end_date, i_end_date, 
          i_post_users, i_post_users, 
          i_post_shift, i_post_shift, 
          i_room_number, i_room_number, 
          i_folio_id, i_folio_id, 
          i_folio_seq, i_folio_seq, 
          i_qty, i_qty, 
          i_amount, i_amount_op, i_amount, 
          i_amount_op, i_amount, 
          i_amount_op, i_amount, 
          i_amount_op, i_amount, 
          i_amount_op, i_amount, 
          i_search_text, i_search_text, i_search_text, i_search_text, 
          i_search_text, i_search_text, i_search_text, flag_active, flag_active
    );
    
    EXECUTE v_sql INTO v_summary;
    v_total_rows := (v_summary->>'post_count')::integer;
    -- Now get transaction details with pagination using two-step approach
    v_sql := format($$
        WITH filtered_ids AS (
            SELECT t.id
            FROM transactions t
            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 room rm ON rm.id = rg.room_id
            LEFT JOIN department d ON d.id = t.dept_id
            WHERE ((COALESCE(%L,TRUE) AND t.post_type = 'P') OR (COALESCE(%L,TRUE) AND t.post_type = 'S'))
            AND (NOT COALESCE(%L,FALSE) OR t.voided)
            AND (NOT COALESCE(%L,FALSE) OR t.splitted)
            AND (NOT COALESCE(%L,FALSE) OR t.transfer_in)
            AND (NOT COALESCE(%L,FALSE) OR t.transfer_out)
            AND (%L IS NULL OR f.folio_type = %L)
            AND (%L IS NULL OR d.code = ANY(%L))
            AND (%L IS NULL OR t.charge_date >= %L)
            AND (%L IS NULL OR t.charge_date <= %L)
            AND (%L IS NULL OR t.post_user = ANY(%L))
            AND (%L IS NULL OR t.post_shift = %L)
            AND (%L IS NULL or upper(rm.room_number) =  upper(%L))
            AND (%L IS NULL OR fi.folio_id = %L)
            AND (%L IS NULL OR fi.folio_seq = %L)
            AND (%L IS NULL OR t.qty = %L)
            AND (%L IS NULL   
                OR (COALESCE(%L,'=') = '=' AND t.amount = %L)
                OR (%L = '>'  AND t.amount > %L)
                OR (%L = '>=' AND t.amount >= %L)
                OR (%L = '<'  AND t.amount < %L)
                OR (%L = '<=' AND t.amount <= %L)
            )
            AND (%L IS NULL OR %L = '' 
                OR t.description ILIKE '%%'||%L||'%%'
                OR rm.room_number = %L
                OR f.id::text = %L
                OR f.register_id::text = %L
                OR f.booking_id::text = %L
            )
            and (%L is null or t.active = %L)
            ORDER BY %s
            %s
            OFFSET %s
        ),
        transactions AS (
            SELECT 
                t.id,
                t.charge_date AS "date",
                t.post_type,
                t.tran_type,
                d.code,
                t.description,
                t.qty,
                t.amount,
                t.total_amount,
                t.post_time,
                t.post_shift,
                t.post_user,
                t.reference,
                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 (CASE WHEN rg.status='I' THEN 'INH' ELSE 'C/O' END)
                   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 (CASE WHEN f.status='C' THEN 'CLOSED' ELSE 'ACTIVE' END)
                END 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,
                
                COALESCE((SELECT 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 
                filtered_ids
                JOIN transactions t ON filtered_ids.id = t.id
                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
            ORDER BY %s
        )
        SELECT COALESCE(json_agg(transactions), '[]') FROM transactions
        $$,
        flag_post, flag_settle, 
        flag_void, flag_split, flag_transfer_in, flag_transfer_out,
        i_folio_type, i_folio_type, 
        i_trn_codes, i_trn_codes, 
        i_start_date, i_start_date, 
        i_end_date, i_end_date, 
        i_post_users, i_post_users, 
        i_post_shift, i_post_shift, 
        i_room_number, i_room_number, 
        i_folio_id, i_folio_id, 
        i_folio_seq, i_folio_seq, 
        i_qty, i_qty, 
        i_amount, i_amount_op, i_amount, 
        i_amount_op, i_amount, 
        i_amount_op, i_amount, 
        i_amount_op, i_amount, 
        i_amount_op, i_amount, 
        i_search_text, i_search_text, i_search_text, i_search_text, 
        i_search_text, i_search_text, i_search_text, flag_active, flag_active,
        v_order_by,
        v_limit_clause,
        v_offset,
        v_order_by
    );
    
    EXECUTE v_sql INTO v_transactions;
    
    -- Combine the results
    v_result := json_build_object(
        'summary', v_summary,
        'transactions', v_transactions,
        'paging', json_build_object(
            'current', v_page,
            'limit', v_limit,
            'total_rows', v_total_rows,
            'row_count', LEAST(v_limit, v_total_rows)
        )
    );
    
    RETURN v_result;
END;

sp_search_transaction_json

Parameters

Definition