aonestar.public

Parameters

Name	Type	Mode
i_folio_type	character	IN (DEFAULT NULL)
i_status	character	IN (DEFAULT 'A')
i_search_text	text	IN (DEFAULT NULL)
i_room_number	text	IN (DEFAULT NULL)
i_folio_id	integer	IN (DEFAULT NULL)
i_register_id	integer	IN (DEFAULT NULL)
i_booking_id	integer	IN (DEFAULT NULL)
i_channel_name	text	IN (DEFAULT NULL)
i_arrival	date	IN (DEFAULT NULL)
i_departure	date	IN (DEFAULT NULL)
i_stay_date	date	IN (DEFAULT NULL)

Definition

WITH dataset AS (
        SELECT f.id AS folio_id, f.folio_type, f.status, 
            iif(f.folio_type = 'R', rg.booking_id, f.booking_id) AS booking_id, 
            bk.status AS booking_status,
            f.register_id, rg.room_id, rm.room_number, 
            CASE f.folio_type 
                WHEN 'R' THEN g.full_name
                WHEN 'B' THEN COALESCE(g.full_name, grp.name) 
                WHEN 'C' THEN cs.name
            END AS name, 
            (g.doc_id is not null or d.file_path is not null) as has_image,
            COALESCE(iif(f.folio_type = 'R', rg.msg_count, bk.msg_count), 0) AS msg_count, 
            COALESCE(iif(f.folio_type = 'R', rg.flag_count, bk.flag_count), 0) AS flag_count,     
            COALESCE(grp."name", '') AS group_name, mg.more_guests, cn."name" AS channel_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,
            COALESCE(rg.adult, 0) adult, COALESCE(rg.child, 0) child, COALESCE(rg.infant, 0) infant, 
            f.open_date, f.open_time, f.open_user, f.close_date, f.close_time, f.close_user,        
            COALESCE(f1.balance,0.0) as folio_balance1, 
            COALESCE(f2.balance,0.0) as folio_balance2, 
            COALESCE(f3.balance,0.0) as folio_balance3, 
            COALESCE(f4.balance,0.0) as folio_balance4,
            coalesce(f1.balance, 0.0) + coalesce(f2.balance, 0.0) + coalesce(f3.balance, 0.0) + coalesce(f4.balance, 0.0) AS total_balance
        FROM folio f 
            LEFT JOIN folio_items f1 ON f1.folio_id = f.id AND f1.folio_seq = 1 AND NOT f1.closed 
            LEFT JOIN folio_items f2 ON f2.folio_id = f.id AND f2.folio_seq = 2 AND NOT f2.closed 
            LEFT JOIN folio_items f3 ON f3.folio_id = f.id AND f3.folio_seq = 3 AND NOT f3.closed 
            LEFT JOIN folio_items f4 ON f4.folio_id = f.id AND f4.folio_seq = 4 AND NOT f4.closed 
            LEFT JOIN registration rg ON rg.id = f.register_id  --rg.folio_id = f.id 
            LEFT JOIN booking bk ON bk.id = f.booking_id  --bk.folio_id = f.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 = iif(f.folio_type = 'B', bk.guest_id, rg.guest_id) 
            LEFT JOIN doc.documents d on d.doc_type = 'guest_photo' AND d.ref_code = g.id::text    
            LEFT JOIN group_info grp ON grp.id = iif(f.folio_type = 'B', bk.group_id, rg.group_id) 
            LEFT JOIN channel cn ON cn.id = CASE f.folio_type 
                                                WHEN 'R' THEN rg.channel_id 
                                                WHEN 'B' THEN bk.channel_id 
                                                ELSE cs.channel_id 
                                            END, 
            lateral (select coalesce(array_agg(gi.full_name),'{}') as more_guests 
                     from register_guests mg left join guest gi on gi.id = mg.guest_id 
                     where mg.register_id = f.register_id and mg.guest_id <> rg.guest_id
                        /*and (i_search_text is null or gi.full_name like '%'||upper(i_search_text)||'%')-->Edit by KenG(2020/12/22)*/
                    ) as mg
        WHERE (i_folio_type IS NULL OR i_folio_type = f.folio_type)
            AND (i_status IS NULL OR i_status = f.status)
            AND (i_folio_id IS NULL OR i_folio_id = f.id)
            AND (i_register_id IS NULL OR i_register_id = f.register_id)
            AND (i_booking_id IS NULL OR i_booking_id = f.booking_id OR i_booking_id = rg.booking_id)
            AND (i_arrival IS NULL 
                 OR i_arrival = rg.arrival 
                 OR i_arrival = bk.arrival 
                 OR (f.folio_type = 'C' and i_arrival = f.open_date))
            AND (i_departure IS NULL 
                 OR i_departure = rg.departure 
                 OR i_departure = bk.departure 
                 OR (f.folio_type = 'C' and i_departure = f.close_date))
            AND (i_stay_date IS NULL 
                 OR i_stay_date BETWEEN rg.arrival AND rg.departure 
                 OR i_stay_date BETWEEN bk.arrival AND bk.departure 
                 OR (f.folio_type = 'C' and i_stay_date BETWEEN f.open_date AND f.close_date))
            AND (i_room_number IS NULL OR upper(rm.room_number) LIKE upper(i_room_number)||'%')
            AND (i_channel_name IS NULL OR lower(cn."name" ) LIKE '%'||lower(i_channel_name)||'%')
            AND (i_search_text IS NULL 
                 OR upper(rm.room_number) = upper(i_search_text) -- LIKE upper(i_search_text)||'%'
                 OR upper(g.full_name) LIKE '%'||upper(i_search_text)||'%'
                 OR upper(grp."name") LIKE '%'||upper(i_search_text)||'%'
                 OR lower(cs."name") LIKE '%'||lower(i_search_text)||'%'
                 --OR lower(cn."name") LIKE '%'||lower(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 rg.booking_id::text = i_search_text
                 OR (EXISTS(SELECT 1 FROM UNNEST(mg.more_guests) aname WHERE aname LIKE '%'||i_search_text||'%'))
                )
    )
    SELECT COALESCE(json_agg(dataset), '[]') FROM dataset

sp_search_guest_folio_js

Parameters

Definition