
CREATE OR REPLACE FUNCTION public.sp_listar_servicio_paginado(p_tipo character varying,p_afiliado character varying,p_numero_documento character varying,p_periodo character varying,p_pagina character varying, p_limit character varying, p_ref refcursor)
  RETURNS refcursor AS
$BODY$

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.val_codigo,t1.val_estab,	t3.vsm_smodulo,to_char(t1.val_fecha,''dd/mm/yyyy'')val_fecha,
	(case when val_tipo=''P'' then t1.val_pac_nombre else razon_social end)val_pac_nombre,
	t1.val_subtotal,t1.val_total,t1.val_moneda,t2.vm_modulod,t2.vm_descripcion,t2.vm_precio,t3.vsm_smodulo,t3.vsm_smodulod,t3.vsm_precio
			,coalesce(t5.periodo,''DIA'') periodo';

	v_tabla='from valorizaciones t1
			inner join val_atencion_modulos t2 on t1.val_estab = t2.vm_vestab And t1.val_codigo = t2.vm_vnumero
			inner join val_atencion_smodulos t3 on t2.vm_vestab = t3.vsm_vestab And t2.vm_vnumero = t3.vsm_vnumero And t2.vm_modulo = t3.vsm_modulo
			inner join sub_modulos t4 on t3.vsm_smodulo = t4.smod_codigo and t3.vsm_modulo = t4.smod_modulo
			left join plan_atenciones t5 on t1.val_plan = t5.id
			left join empresas t6 on t1.val_ubicacion = t6.id 
			';
	
	v_where = ' Where t1.val_estab=1 
	And t1.val_estado_atencion=''G''
	And t1.val_anulado=''N''
	And t1.val_facturado=''N''
	And t2.vm_estado=''A'' And t2.vm_eliminado=''N''
	And t3.vsm_estado=''A'' And t3.vsm_eliminado=''N''
	/*and smod_grupo=1*/
	and smod_grupo=(case when val_tipo=''P'' then 1 else smod_grupo end)
	/*and val_tipo=''P''*/
	/*and val_plan=''0''*/
	and val_plan=(case when val_tipo=''P'' then ''0'' else val_plan end)
	';
	/*
	If p_numero_documento<>'' Then
	 v_where:=v_where||'And t2.numero_documento ilike ''%'||p_numero_documento||'%'' ';
	End If;
	If p_plan_denominacion<>'' Then
	 v_where:=v_where||'And t8.plan_denominacion ilike ''%'||p_plan_denominacion||'%'' ';
	End If;
	*/
	If p_periodo<>'' Then
	 v_where:=v_where||'And coalesce(t5.periodo,''DIA'') ilike ''%'||p_periodo||'%'' ';
	End If;
	
	If p_afiliado<>'' Then
	 v_where:=v_where||'And (case when val_tipo=''P'' then t1.val_pac_nombre else razon_social end) ilike ''%'||p_afiliado||'%'' ';
	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

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

