
CREATE OR REPLACE FUNCTION public.sp_listar_tarjeta_paginado(
	p_numero_documento character varying, 
	p_persona character varying, 
	p_numero_tarjeta character varying, 
	p_id_area character varying, 
	p_id_plan character varying, 
	p_estado character varying, 
	p_tipo_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=' t1.id,t1.persona_id,t1.numero_tarjeta,t2.tipo_documento,t2.numero_documento,
	t2.nombres||'' ''||t2.apellido_paterno||'' ''||t2.apellido_materno persona,t1.estado,t1.acceso,t1.msj,
	(select string_agg(DISTINCT t2_.plan_denominacion, '','')
	from afiliaciones t1_
	inner join plan_atenciones t2_ on t1_.plan_id=t2_.id
	where t1_.persona_id=t1.persona_id
	And t1_.estado=''1''
	And t2_.plan_estado=''A'')plan_denominacion,t2.flag_negativo 
	';

	v_tabla='from tarjetas t1
	inner join personas t2 on t1.persona_id=t2.id ';

	v_where = ' Where 1=1  ';

	If p_estado<>'' Then
	 v_where:=v_where||'And t1.estado = '''||p_estado||''' ';
	End If;

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

	If p_tipo_documento<>'0' Then
	 v_where:=v_where||'And t2.tipo_documento ilike ''%'||p_tipo_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;
	
	DROP TABLE IF EXISTS temporal2;
	EXECUTE ('CREATE TEMP TABLE temporal2 AS  SELECT '||v_campos||v_tabla||v_where);

	v_campos=' id,persona_id,numero_tarjeta,tipo_documento,numero_documento,persona,estado,acceso,msj,plan_denominacion,flag_negativo  ';
	v_tabla=' from temporal2 t1 ';
	v_where = ' Where 1=1 ';
		
	If p_id_area='0' Then
		v_where:=v_where||'And t1.persona_id in (select distinct t1_.persona_id
						from afiliaciones t1_
						inner join plan_atenciones t2_ on t1_.plan_id=t2_.id
						where t1_.estado=''1''
						And t2_.plan_estado=''A'') ';
	elseIf p_id_area='' Then
		v_where:=v_where||'And t1.persona_id not in (select distinct t1_.persona_id
						from afiliaciones t1_
						inner join plan_atenciones t2_ on t1_.plan_id=t2_.id
						where t1_.estado=''1''
						And t2_.plan_estado=''A'') ';
	else
		v_where:=v_where||'And t1.persona_id in (select distinct t1_.persona_id
						from afiliaciones t1_
						inner join plan_atenciones t2_ on t1_.plan_id=t2_.id
						inner join areas t3_ on t2_.plan_area=t3_.id 
						where t3_.id='||p_id_area||'
						And t1_.estado=''1''
						And t2_.plan_estado=''A'') ';
	End If;

	If p_id_plan<>'0' Then

		--v_where:=v_where||'And t1.plan_id = '''||p_id_plan||''' ';
		v_where:=v_where||'And t1.persona_id in (select distinct t1_.persona_id
						from afiliaciones t1_
						inner join plan_atenciones t2_ on t1_.plan_id=t2_.id
						where t1_.plan_id='||p_id_plan||' 
						And t1_.estado=''1'' 
						And t2_.plan_estado=''A'') ';
	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$
;
