--select sp_listar_cobranza_paginado('','','113','1','10','ref');fetch all in ref

CREATE OR REPLACE FUNCTION public.sp_listar_cobranza_paginado(
    p_fecha_desde character varying,
    p_fecha_hasta character varying,
    p_id_moneda 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_camposc varchar;
v_tablac varchar;
v_wherec varchar;


Begin

	p_pagina=(p_pagina::Integer-1)*p_limit::Integer;

	v_camposc = 'to_char(t1.fecha_inicio,''dd-mm-yyyy'')fecha,/*t1.saldo_total*/ t1.total_recaudado importe,''Ingresado'' estado,t1.id_moneda,t2.denominacion moneda ';
	v_tablac = 'from caja_cobranzas t1
	inner join tabla_maestras t2 on t2.id=t1.id_moneda ';
	v_wherec = 'where t1.estado=''1'' and t1.id_tipo_movimiento=147 ';

	If p_id_moneda<>'0' Then
	 v_wherec:=v_wherec||' And t1.id_moneda = '|| p_id_moneda;
	End If;
	
	v_campos='R.fac_fecha,

	facd_importe
	-
	(select coalesce(round(sum(t4_.adelanto)::decimal,2),0)adelanto
	from facturas t1_
	inner join factura_detalles t2_ on t1_.fac_tipo=t2_.facd_tipo and t1_.fac_serie=t2_.facd_serie and t1_.fac_numero=t2_.facd_numero
	inner join cronograma_detalles t3_ on t3_.fac_tipo=t2_.facd_tipo and t3_.fac_serie=t2_.facd_serie and t3_.fac_numero=t2_.facd_numero and t3_.nro_cuota=t2_.facd_item
	inner join adelantos t4_ on t3_.id=t4_.id_cronograma_detalle And t4_.id_moneda = '|| p_id_moneda ||' 
	where t1_.fac_anulado=''N''
	And to_char(t1_.fac_fecha,''dd-mm-yyyy'')=R.fac_fecha
	)--adelanto_descontado
	+
	(select coalesce(round(sum(t1_.adelanto)::decimal,2),0)
	from adelantos t1_
	where t1_.estado=''A''
	and coalesce(t1_.id_cronograma_detalle,0)=0
	And to_char(t1_.fecha,''dd-mm-yyyy'')=R.fac_fecha
	And t1_.id_moneda = '|| p_id_moneda ||' 
	)--adelanto
	+
	(select coalesce(round(sum(t4_.adelanto)::decimal,2),0)adelanto
	from facturas t1_
	inner join factura_detalles t2_ on t1_.fac_tipo=t2_.facd_tipo and t1_.fac_serie=t2_.facd_serie and t1_.fac_numero=t2_.facd_numero
	inner join cronograma_detalles t3_ on t3_.fac_tipo=t2_.facd_tipo and t3_.fac_serie=t2_.facd_serie and t3_.fac_numero=t2_.facd_numero and t3_.nro_cuota=t2_.facd_item
	inner join adelantos t4_ on t3_.id=t4_.id_cronograma_detalle And t4_.id_moneda = '|| p_id_moneda ||' 
	where t1_.fac_anulado=''N''
	And to_char(t1_.fac_fecha,''dd-mm-yyyy'')=R.fac_fecha
	and to_char(t4_.fecha,''dd-mm-yyyy'')=to_char(t1_.fac_fecha,''dd-mm-yyyy'')
	)--adelanto_hoy
	importe
	
	,(case when R.fac_fecha::date <= (select fecha_inicio::date from caja_cobranzas where id_moneda='|| p_id_moneda ||' and estado=''1'' order by fecha_fin asc limit 1)
	then ''Saldo Inicial'' else ''Pendiente'' end)estado
	,R.id_moneda,R.moneda
	';

	v_tabla='from(
	select to_char(t1.fac_fecha,''dd-mm-yyyy'')fac_fecha,sum(t1.fac_total)fac_total,sum(t3.interes)interes,sum(t3.capital_amortizado)capital_amortizado,
	sum(t3.capital_vivo)capital_vivo,sum(t2.facd_importe)facd_importe,t4.id id_moneda,t4.denominacion moneda
	from facturas t1
	inner join factura_detalles t2 on t1.fac_tipo=t2.facd_tipo and t1.fac_serie=t2.facd_serie and t1.fac_numero=t2.facd_numero
	inner join cronograma_detalles t3 on t3.fac_tipo=t2.facd_tipo and t3.fac_serie=t2.facd_serie and t3.fac_numero=t2.facd_numero and t3.nro_cuota=t2.facd_item 
	inner join tabla_maestras t4 on t4.id=t1.fac_moneda_id 
	left join caja_cobranzas t5 on t5.id_moneda = t1.fac_moneda_id and to_char(t1.fac_fecha,''dd-mm-yyyy'')=to_char(t5.fecha_inicio,''dd-mm-yyyy'') 
	and t5.estado=''1'' and t5.id_tipo_movimiento=147 
	';
	
	v_where = ' Where t1.fac_anulado=''N'' And t3.estado=''A'' And t5.id is null ';
	
	If p_fecha_desde<>'' Then
	 v_where:=v_where||' And t1.fac_fecha >= '''||p_fecha_desde||' :00:00'' ';
	End If;
	If p_fecha_hasta<>'' Then
	 v_where:=v_where||' And t1.fac_fecha <= '''||p_fecha_hasta||' :23:59'' ';
	End If;

	If p_id_moneda<>'0' Then
	 v_where:=v_where||' And t1.fac_moneda_id = '|| p_id_moneda;
	End If;
	
	v_group = ' group by t4.id,to_char(t1.fac_fecha,''dd-mm-yyyy'')';
	v_group:=v_group|| ' )R ';

	
	--EXECUTE ('SELECT count(1) '||v_tabla||v_where||v_group) INTO v_count;
	EXECUTE ('SELECT count(1) FROM ( SELECT '||v_camposc||v_tablac||v_wherec||' UNION ALL SELECT '||v_campos||v_tabla||v_where||v_group||')S') 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 R.fac_fecha::date desc LIMIT '||p_limit||' OFFSET '||p_pagina||';'; 
		v_scad:='SELECT * FROM ( SELECT '||v_camposc||v_col_count||v_tablac||v_wherec||' UNION ALL SELECT '||v_campos||v_col_count||v_tabla||v_where||v_group||')S Order By S.fecha::date desc LIMIT '||p_limit||' OFFSET '||p_pagina||';';
	else
		--v_scad:='SELECT '||v_campos||v_col_count||v_tabla||v_where||v_group||' Order By R.fac_fecha::date desc;'; 
		v_scad:='SELECT * FROM ( SELECT '||v_camposc||v_col_count||v_tablac||v_wherec||' UNION ALL SELECT '||v_campos||v_col_count||v_tabla||v_where||v_group||')S Order By S.fecha::date desc;';
	End If;
	
	Raise Notice '%',v_scad;
	Open p_ref For Execute(v_scad);
	Return p_ref;
End

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
