-- DROP FUNCTION public.sp_listar_supervision_paginado(varchar, varchar, varchar, varchar, refcursor);

CREATE OR REPLACE FUNCTION public.sp_listar_supervision_paginado(p_persona character varying, p_numero_documento character varying, p_pagina character varying, p_limit character varying, p_ref refcursor)
 RETURNS refcursor
 LANGUAGE plpgsql
AS $function$

Declare
--v_id numeric;
--v_numinf character varying;
v_scad varchar;
v_campos varchar;
v_tabla varchar;
v_where varchar;
v_count varchar;
v_col_count varchar;
--v_perfil varchar;

Begin

	p_pagina=(p_pagina::Integer-1)*p_limit::Integer;
	
	v_campos='to_char(t1.fecha_supervision,''dd-mm-yyyy'')fecha_supervision,t2.tipo_documento,t2.numero_documento,t2.apellido_paterno||'' ''||t2.apellido_materno||'' ''||t2.nombres persona,t3.plan_denominacion plan, v.val_total, v.val_observaciones ';

	v_tabla='from supervisiones t1
	inner join personas t2 on t1.persona_id=t2.id
	left join plan_atenciones t3 on t1.plan_id=t3.id
	left join valorizaciones v on t1.id = v.id_supervision ';
		
	v_where = ' Where ''1''=''1'' ';

	If p_numero_documento<>'' Then
	 v_where:=v_where||' And t2.numero_documento ilike ''%'||p_numero_documento||'%'' ';
	End If;

	If p_persona<>'' Then
	 v_where:=v_where||' And t2.nombres||'' ''||t2.apellido_paterno||'' ''||t2.apellido_materno ilike ''%'||p_persona||'%'' ';
	End If;
	
	EXECUTE ('SELECT count(1) '||v_tabla||v_where) INTO v_count;
	v_col_count:=' ,'||v_count||' as TotalRows ';

	If v_count::Integer > p_limit::Integer then
		v_scad:='SELECT '||v_campos||v_col_count||v_tabla||v_where||' Order By t1.id Desc LIMIT '||p_limit||' OFFSET '||p_pagina||';'; 
	else
		v_scad:='SELECT '||v_campos||v_col_count||v_tabla||v_where||' Order By t1.id Desc;'; 
	End If;
	
	--Raise Notice '%',v_scad;
	Open p_ref For Execute(v_scad);
	Return p_ref;
End

$function$
;
