aonestar.public

Parameters

Name	Type	Mode
category	text	IN (DEFAULT NULL)
user_names	text[]	IN (DEFAULT NULL)
log_types	text[]	IN (DEFAULT NULL)
start_date	date	IN (DEFAULT NULL)
end_date	date	IN (DEFAULT NULL)
shift	t_shift[]	IN (DEFAULT NULL)
reference_id	integer	IN (DEFAULT NULL)
search_text	text	IN (DEFAULT NULL)
id	bigint	OUT
category	text	OUT
user_name	text	OUT
log_type	text	OUT
log_date	date	OUT
log_time	timestamp with time zone	OUT
log_shift	t_shift	OUT
reference_id	integer	OUT
reference_text	text	OUT
log_text	text	OUT

Definition

SELECT l.id, l.category, l.log_user, l.log_type, l.log_date, l.log_time, l.log_shift, 
        CASE category
            WHEN 'cashier' THEN l.folio_id 
            WHEN 'room' THEN l.room_id 
            WHEN 'booking' THEN l.booking_id 
            WHEN 'registration' THEN l.register_id 
        END AS reference_id, 
        CASE category
            WHEN 'cashier' THEN 'Folio# '||l.folio_id 
            WHEN 'room' THEN 'Room# '||rm.room_number
            WHEN 'booking' THEN 'RSV# '||l.booking_id 
            WHEN 'registration' THEN 'REG# '||l.register_id 
        END AS reference_text, 
        l.log_text 
    FROM user_logs l left join room rm on rm.id = l.room_id
    WHERE ($1 IS NULL OR l.category = $1)
        AND ($2 IS NULL OR l.log_user = ANY($2))
        AND ($3 IS NULL OR l.log_type = ANY($3))
        AND ($4 IS NULL OR l.log_date >= $4)
        AND ($5 IS NULL OR l.log_date <= $5)
        AND ($6 IS NULL OR l.log_shift = ANY($6))
        AND ($7 IS NULL OR $1 IS NULL 
             OR $7 = CASE category
                        WHEN 'cashier'      THEN l.folio_id 
                        WHEN 'room'         THEN l.room_id 
                        WHEN 'booking'      THEN l.booking_id 
                        WHEN 'registration' THEN l.register_id 
                     END
            )
       AND ($8 IS NULL 
            or l.log_text ilike '%'||$8||'%'
            or l.log_user = $8 --like '%'||$8||'%'  
            or $8 = CASE category
                        WHEN 'cashier'      THEN l.folio_id 
                        WHEN 'room'         THEN l.room_id 
                        WHEN 'booking'      THEN l.booking_id 
                        WHEN 'registration' THEN l.register_id 
                    END::text      
            )

sp_get_user_logs

Parameters

Definition