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)
order_by	text	IN (DEFAULT 'id desc')
page_no	integer	IN (DEFAULT 1)
row_per_page	integer	IN (DEFAULT NULL)

Definition

declare 
	json_result json;

sql_log_all text = $$
		with logs as (
			SELECT  lg.*, row_number() over(order by %s) as seq
		    FROM sp_get_user_logs($1, $2, $3, $4, $5, $6, $7, $8) as lg	
		)	
	    SELECT json_build_object(
			    	'total_rows', (select count(*) from logs),
			    	'result', (select json_agg(logs) from logs)
		      )		
	$$;	   

	sql_log_paging text = $$
		with logs as (
			SELECT  lg.*, row_number() over(order by %s) as seq
		    FROM sp_get_user_logs($1, $2, $3, $4, $5, $6, $7, $8) as lg			
		), limit_data as (
		 	SELECT logs.*
			from logs
		    limit $9
		    offset $10
		)		
	    SELECT json_build_object(
			    	'total_rows', (select count(*) from logs),
			    	'result', (select json_agg(limit_data) from limit_data)
		      ) 
	$$;
begin
	--raise notice '%', format(stmt, order_by, order_by); 
	if row_per_page is null then
		EXECUTE format(sql_log_all, order_by) USING $1, $2, $3, $4, $5, $6, $7, $8
	    into json_result; 	
	else 
	    EXECUTE format(sql_log_paging, order_by) USING $1, $2, $3, $4, $5, $6, $7, $8, row_per_page, (page_no-1)*row_per_page
	    into json_result; 	
    end if;
   	return json_result;
end

sp_get_user_logs_js

Parameters

Definition