CREATE OR REPLACE FUNCTION public.sp_listar_vencidos_paginado(
    p_numero_documento character varying,
    p_nombre character varying,
    p_fecha_desde character varying,
    p_fecha_hasta character varying,
    p_id_moneda 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=' t1.id,t2.numero_documento,t2.apellido_paterno||'' ''||t2.apellido_materno||'' ''||t2.nombres as persona,
	case when t2.telefono not in (''99999999'',''999999999'') then t2.telefono end telefono, 
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(t5.facd_importe)monto_pagado,
sum(case when t4.facturado=''N'' then t4.interes else 0 end) interes_pendiente,
--count(t4.cuota_pagar)num_cuota,
--count(t5.facd_item)num_cuota_facturada,
case 
	when sum(t5.facd_importe)>=sum(t4.cuota_pagar) and sum(t5.facd_importe)>0 Then ''Finalizado''
	when sum(t5.facd_importe)<sum(t4.cuota_pagar) and sum(t5.facd_importe)>0 Then ''En proceso''
	when sum(t5.facd_importe)=0 Then ''Pendiente'' 
	else ''Pendiente''
end estado,
area,puesto,cargo 
';

	v_tabla='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
		
		left join (Select numero_documento,area,puesto,cargo
				From dblink (''dbname='||p_dbname||' port='||p_port||' host='||p_host||' user='||p_user||' password='||p_password||''',
				''select t2_.numero_documento,string_agg(DISTINCT t4_.denominacion, '''','''') area,string_agg(DISTINCT t3_.plan_denominacion, '''','''') puesto,
				(case when t1_.titular_id>0 then ''''TITULAR'''' else ''''AYUDANTE'''' end) cargo
				from afiliaciones t1_
				inner join personas t2_ on t1_.persona_id=t2_.id
				inner join plan_atenciones t3_ on t1_.plan_id=t3_.id
				left join areas t4_ on t3_.plan_area=t4_.id
				Where 1=1
				--and t2_.numero_documento=''''03385040'''' 
				and t2_.estado=''''1'''' 
				and t1_.estado=''''1'''' 
				group by t2_.numero_documento,(case when t1_.titular_id>0 then ''''TITULAR'''' else ''''AYUDANTE'''' end)'')ret
				(numero_documento varchar,area varchar,puesto varchar,cargo varchar)) R on t2.numero_documento=R.numero_documento
		';
	
	v_where = ' Where t1.eliminado=''N'' 
		    And t4.estado=''A''
		    And t4.facturado=''N''
		    And t4.flag_dudoso=''0'' 
		    And t4.fecha_pago<now() ';
	
	If p_fecha_desde<>'' Then
	 v_where:=v_where||' And t3.fecha >= '''||p_fecha_desde||' :00:00'' ';
	End If;
	If p_fecha_hasta<>'' Then
	 v_where:=v_where||' And t3.fecha <= '''||p_fecha_hasta||' :23:59'' ';
	End If;
	
	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;

	If p_id_moneda<>'0' Then
	 v_where:=v_where||' And t1.id_moneda = '|| p_id_moneda;
	End If;
	
	v_group = ' group by t1.id,t2.id,t3.id 
			 ,R.area,R.puesto,R.cargo';

	/*
	If p_id_estado<>'0' Then
	  v_group:=v_group||' having (case 
						when sum(t5.facd_importe)>=sum(t4.cuota_pagar) and sum(t5.facd_importe)>0 Then ''3''
						when sum(t5.facd_importe)<sum(t4.cuota_pagar) and sum(t5.facd_importe)>0 Then ''2''
						when sum(t5.facd_importe)=0 Then ''1''
					end) = '''|| p_id_estado||'''';
	End If;
	*/
	
	EXECUTE ('SELECT count(1) From (SELECT count(1) '||v_tabla||v_where||v_group||')R') INTO v_count;

	EXECUTE ('SELECT coalesce(sum(interes_pendiente),0) From (SELECT '||v_campos||v_tabla||v_where||v_group||')R') INTO v_interes_pendiente;
	EXECUTE ('SELECT coalesce(sum(deuda_pendiente_con_interes),0) From (SELECT '||v_campos||v_tabla||v_where||v_group||')R') INTO v_deuda_pendiente_con_interes;
	EXECUTE ('SELECT coalesce(sum(deuda_pendiente_sin_interes),0) From (SELECT '||v_campos||v_tabla||v_where||v_group||')R') INTO v_deuda_pendiente_sin_interes;
	EXECUTE ('SELECT coalesce(sum(valor_prestamo),0) From (SELECT '||v_campos||v_tabla||v_where||v_group||')R') INTO v_valor_prestamo;
	--v_count:='1';
	v_col_count:=' ,'||v_count||' as TotalRows ';
	v_col_count:=v_col_count||' ,'||v_interes_pendiente||' as total_interes_pendiente ';
	v_col_count:=v_col_count||' ,'||v_deuda_pendiente_con_interes||' as total_deuda_pendiente_con_interes ';
	v_col_count:=v_col_count||' ,'||v_deuda_pendiente_sin_interes||' as total_deuda_pendiente_sin_interes ';
	v_col_count:=v_col_count||' ,'||v_valor_prestamo||' as total_valor_prestamo ';

	--Raise Notice '%',v_col_count;
	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;
