--psql -U postgres -d sigtp_prod8 -c "CREATE EXTENSION dblink"
--select sp_listar_vencidos_paginado('','','siprefi_prod2','5432','127.0.0.1','postgres','postgres','1','100000','ref_');fetch all in ref_

CREATE OR REPLACE FUNCTION public.sp_listar_vencidos_paginado(
    p_numero_documento character varying,
    p_nombre character varying,
    p_dbname character varying,
    p_port character varying,
    p_host character varying,
    p_user character varying,
    p_password character varying,
    p_pagina character varying,
    p_limit character varying,
    p_ref refcursor)
  RETURNS refcursor AS
$BODY$

Declare

v_scad varchar;
v_campos varchar;
v_tabla varchar;
v_where varchar;
v_group varchar;
v_count varchar;
v_col_count varchar;

v_interes_pendiente varchar;
v_deuda_pendiente_con_interes varchar;
v_deuda_pendiente_sin_interes varchar;
v_valor_prestamo varchar;

Begin

	p_pagina=(p_pagina::Integer-1)*p_limit::Integer;
	
	v_campos=' t3.id tarjeta_id,t2.id id_persona,numero_tarjeta
,R.numero_documento,persona,fecha_desembolso,valor_prestamo,deuda_total_con_interes,deuda_pendiente_con_interes
,deuda_total_sin_interes,deuda_pendiente_sin_interes,interes_pendiente 
';

	v_tabla='from afiliaciones t1
inner join personas t2 on t1.persona_id=t2.id
inner join tarjetas t3 on t2.id=t3.persona_id and t3.estado=''1''
		
inner join (Select *
From dblink (''dbname='||p_dbname||' port='||p_port||' host='||p_host||' user='||p_user||' password='||p_password||''',
''select t1.id,t2.numero_documento,t2.apellido_paterno||'''' ''''||t2.apellido_materno||'''' ''''||t2.nombres as persona,
to_char(t3.fecha,''''dd-mm-yyyy'''') fecha_desembolso,t3.valor_prestamo,
sum(t4.cuota_pagar)deuda_total_con_interes,
sum(case when t4.facturado=''''N'''' then t4.cuota_pagar else 0 end)deuda_pendiente_con_interes,
sum(t4.capital_amortizado)deuda_total_sin_interes,
sum(case when t4.facturado=''''N'''' then t4.capital_amortizado else 0 end)deuda_pendiente_sin_interes,
sum(case when t4.facturado=''''N'''' then t4.interes else 0 end) interes_pendiente
from solicitudes t1
inner join personas t2 on t1.id_persona=t2.id
inner join cronogramas t3 on t1.id=t3.id_solicitud
inner join cronograma_detalles t4 on t3.id = t4.id_cronograma
left join factura_detalles t5 on t4.fac_tipo=t5.facd_tipo and t4.fac_serie=t5.facd_serie and t4.fac_numero=t5.facd_numero 
and t4.nro_cuota=t5.facd_item
Where t1.eliminado=''''N'''' 
And t4.estado=''''A''''
And t4.facturado=''''N''''
And t4.flag_dudoso=''''0'''' 
And t4.fecha_pago<now() 
group by t1.id,t2.id,t3.id'')ret
(id varchar,numero_documento varchar,persona varchar,fecha_desembolso varchar,valor_prestamo varchar,deuda_total_con_interes varchar,deuda_pendiente_con_interes varchar
,deuda_total_sin_interes varchar,deuda_pendiente_sin_interes varchar,interes_pendiente varchar)) R on t2.numero_documento=R.numero_documento ';
	
	v_where = ' Where t2.estado=''1'' 
		    And t1.estado=''1'' ';
	
	If p_numero_documento<>'' Then
	 v_where:=v_where||' And t2.numero_documento like ''%'||p_numero_documento||'%'' ';
	End If;
	
	If p_nombre<>'' Then
	 v_where:=v_where||' And trim(t2.apellido_paterno)||'' ''||trim(t2.apellido_materno)||'' ''||trim(t2.nombres) ilike ''%'||p_nombre||'%'' '; 
	End If;

	v_group = ' ';
	
	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||v_group||' Order By t2.apellido_paterno Asc LIMIT '||p_limit||' OFFSET '||p_pagina||';'; 
	else
		v_scad:='SELECT '||v_campos||v_col_count||v_tabla||v_where||v_group||' Order By t2.apellido_paterno Asc;'; 
	End If;
	
	--Raise Notice '%',v_scad;
	Open p_ref For Execute(v_scad);
	Return p_ref;
End

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
