-- FUNCTION: adempiere.calculo_comision_cobranza(numeric, numeric, character varying, date, date, integer, integer)

-- DROP FUNCTION adempiere.calculo_comision_cobranza(numeric, numeric, character varying, date, date, integer, integer);

CREATE OR REPLACE FUNCTION adempiere.calculo_comision_cobranza(
	organizacion numeric,
	oficial numeric,
	tipo character varying,
	fechadesde date,
	fechahasta date,
	diadesde integer,
	diahasta integer)
    RETURNS double precision
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
    
AS $BODY$
declare
valor float;
begin 
if tipo='XV' then
select (
	select
	sum(pys.dueamt) as valor
	from Tb_ProjectPaySchedule pys
	inner join C_Project cp on cp.C_Project_id=pys.C_Project_id
	INNER join tb_estadoContrato tbec on tbec.tb_estadocontrato_id = cp.tb_estadocontrato_id
	left join C_ProjectType type on type.C_ProjectType_ID=cp.C_ProjectType_ID and type.Name='CONTRATO_INMOBILIARIO'
    left join ad_user ca on ca.ad_user_ID=cp.CarteraRep_ID

	where cp.ad_org_id=organizacion
	AND tbec.IsValid='Y'  AND tbec.IsActive='Y'	AND cp.IsActive='Y'
	AND CASE WHEN 'Y'='Y' THEN tbec.esVenta='Y' ELSE (tbec.esVenta='N' OR tbec.esVenta='Y') END
	AND pys.paymentType ='CE' AND pys.isActive='Y'
	AND cp.IsInDispute='N'
	AND cp.tipoFinanciamiento IN('DIR','CON','HIP')
	AND pys.DueDate::date>=fechaDesde and pys.DueDate::date<=fechaHasta
	AND cp.CarteraRep_ID=oficial
	AND pys.isActive='Y'
) into valor;
end if;

 if tipo='XVC' THEN 
 select (
 			
	 SELECT sum(val.valorConCargo)+sum(val.valor) as valor
			FROM(
	 		select sum(k.payamt) as valor,
	 
	 		/*(coalesce((select sum(k.payamt)
			from Tb_ProjectPaySchedule pp
			left join Tb_ProjectPayScheduleLine k on k.Tb_ProjectPaySchedule_id=pp.Tb_ProjectPaySchedule_id
			left join tb_allocationpayschedule al_pys on al_pys.tb_allocationpayschedule_id=k.tb_allocationpayschedule_id
			where 
			k.Tb_ProjectPaySchedule_id=pp.Tb_ProjectPaySchedule_id
			and al_pys.docstatus IN ('CO', 'CL')
			and pp.isActive='Y'
			and  k.c_payment_ID is null
			and k.paymentDate::date>=fechaDesde::date --$P{fechaVencimientoCuota}
			and CASE WHEN pp.DueDate::date + interval '1 day'*5 <= fechaHasta::date THEN k.paymentDate::date<=fechaHasta::date
			ELSE k.paymentDate::date<= (pp.DueDate::date + interval '1 day'*5) END
			AND pp.paymentType ='CE' 
			),0)) as valorConCargo*/
				
			(coalesce((select sum(k.payamt)
			from Tb_ProjectPaySchedule ppx  
			left join Tb_ProjectPayScheduleLine k on k.Tb_ProjectPaySchedule_id=ppx.Tb_ProjectPaySchedule_id
			left join tb_allocationpayschedule al_pys on al_pys.tb_allocationpayschedule_id=k.tb_allocationpayschedule_id
			where 
			k.Tb_ProjectPaySchedule_id=ppx.Tb_ProjectPaySchedule_id
			and al_pys.docstatus IN ('CO', 'CL')
			and ppx.isActive='Y'
			and  k.c_payment_ID is null
			and k.paymentDate::date>=fechaDesde::date --$P{fechaVencimientoCuota}
			and CASE WHEN ppx.DueDate::date + interval '1 day'*5 <= fechaHasta::date THEN k.paymentDate::date<=fechaHasta::date
			ELSE k.paymentDate::date<= (ppx.DueDate::date + interval '1 day'*5) END
			AND ppx.paymentType ='CE' 
			AND ppx.Tb_ProjectPaySchedule_id=pp.Tb_ProjectPaySchedule_id
			),0)) as valorConCargo		
	 
			from Tb_ProjectPaySchedule pp
			left join Tb_ProjectPayScheduleLine k on k.Tb_ProjectPaySchedule_id=pp.Tb_ProjectPaySchedule_id
			left join tb_allocationpayschedule al_pys on al_pys.tb_allocationpayschedule_id=k.tb_allocationpayschedule_id
			inner join c_payment cp on cp.c_payment_ID=k.c_payment_ID					
			inner join C_Project cpx on cpx.C_Project_id=pp.C_Project_id
			inner join tb_estadoContrato tbec on tbec.tb_estadocontrato_id = cpx.tb_estadocontrato_id
			left join C_ProjectType type on type.C_ProjectType_ID=cpx.C_ProjectType_ID and type.Name='CONTRATO_INMOBILIARIO'					
			where 
			cpx.ad_org_id=organizacion
			and al_pys.docstatus IN ('CO', 'CL')
			and pp.isActive='Y'
			and cp.docStatus IN ('CO','CL')
			and k.paymentDate::date>=fechaDesde
			and
			CASE WHEN pp.DueDate::date + interval '1 day'*5<= fechaHasta
				   THEN k.paymentDate::date<=fechaHasta
			ELSE k.paymentDate::date<= (pp.DueDate::date + interval '1 day'*5) END
			AND pp.paymentType ='CE' 			 			
			AND tbec.IsValid='Y'  AND tbec.IsActive='Y' AND cp.IsActive='Y'
			AND CASE WHEN 'Y'='Y' THEN tbec.esVenta='Y' ELSE (tbec.esVenta='N' OR tbec.esVenta='Y') END
			AND pp.DueDate::date>=fechaDesde::date and pp.DueDate::date<=fechaHasta
			AND cpx.CarteraRep_ID=oficial 
			AND cpx.IsInDispute='N' 
			AND cpx.tipoFinanciamiento IN('DIR','CON','HIP')
			GROUP BY pp.Tb_ProjectPaySchedule_id	
			)val
 ) into valor;
 end if;
 
 
  if tipo='V' THEN 
 select (
 	select 
(cobranza.valorCuotasVencida - COALESCE(cobranza.PagosCuotasVencidas,0)) as SaldoVencido
FROM (
	select
		sum((select sum(s.dueamt)
		FROM Tb_ProjectPaySchedule s
		where s.c_project_ID=cp.c_project_ID AND s.isActive='Y'
		AND s.paymentType IN ('CE','CI')		
		and s.DueDate::date <= fechaDesde - interval '1 day'*diadesde
		and s.DueDate::date >= fechaDesde - interval '1 day'*diahasta	
		))
		AS valorCuotasVencida,
	
		sum(coalesce((select
		sum(coalesce(k.payamt,0)) as saldo
		FROM Tb_ProjectPaySchedule s
		inner join Tb_ProjectPayScheduleLine k on  k.Tb_ProjectPaySchedule_id=s.Tb_ProjectPaySchedule_id and k.IsActive='Y'
				and exists (select 1 from tb_allocationpayschedule alp where alp.tb_allocationpayschedule_id=k.tb_allocationpayschedule_id and alp.docstatus in ('CO', 'CL'))
				and (case when k.c_Payment_ID isnull then 1=1 else exists (select 1 from c_Payment ptbl where ptbl.c_Payment_ID = k.c_Payment_ID and ptbl.docstatus in ('CO', 'CL')) end)
		inner join tb_allocationpayschedule al_pys on al_pys.tb_allocationpayschedule_id=k.tb_allocationpayschedule_id
		left join c_payment cpx on cpx.c_payment_ID=k.c_payment_ID and cpx.docStatus IN ('CO','CL')
		WHERE
		s.C_Project_ID=cp.C_Project_ID
		AND s.paymentType IN ('CE','CI') 				
		AND s.isActive='Y'		
		and s.DueDate::date <= fechaDesde - interval '1 day'*diadesde
		and s.DueDate::date >= fechaDesde - interval '1 day'*diahasta				  
		and k.paymentDate::date<fechaDesde					  
		and al_pys.docstatus IN ('CO', 'CL')
		and s.isActive='Y'
		),0)) AS PagosCuotasVencidas

	FROM C_Project cp	
	inner join tb_estadoContrato tbec on tbec.tb_estadocontrato_id = cp.tb_estadocontrato_id		
	WHERE cp.ad_org_id=organizacion	
	AND tbec.IsValid='Y' AND cp.IsActive='Y' AND tbec.IsActive='Y'
	AND tbec.esVenta='Y'
	AND cp.tipoFinanciamiento IN('DIR','CON','HIP')
	and cp.CarteraRep_ID=oficial
	AND cp.IsInDispute='N' 	
) cobranza
 ) into valor;
 end if;
 
 
   if tipo='VC' THEN 
 select (

	select
		sum(coalesce((select
		sum(coalesce(k.payamt,0)) as saldo
		FROM Tb_ProjectPaySchedule s
		inner join Tb_ProjectPayScheduleLine k on  k.Tb_ProjectPaySchedule_id=s.Tb_ProjectPaySchedule_id and k.IsActive='Y'
				and exists (select 1 from tb_allocationpayschedule alp where alp.tb_allocationpayschedule_id=k.tb_allocationpayschedule_id and alp.docstatus in ('CO', 'CL'))
				and (case when k.c_Payment_ID isnull then 1=1 else exists (select 1 from c_Payment ptbl where ptbl.c_Payment_ID = k.c_Payment_ID and ptbl.docstatus in ('CO', 'CL')) end)
		inner join tb_allocationpayschedule al_pys on al_pys.tb_allocationpayschedule_id=k.tb_allocationpayschedule_id
		left join c_payment cpx on cpx.c_payment_ID=k.c_payment_ID and cpx.docStatus IN ('CO','CL')
		WHERE
		s.C_Project_ID=cp.C_Project_ID
		AND s.paymentType IN ('CE','CI') 				
		AND s.isActive='Y'		
		and s.DueDate::date <= fechaDesde - interval '1 day'*diadesde
		and s.DueDate::date >= fechaDesde - interval '1 day'*diahasta				  
		and k.paymentDate::date>=fechaDesde					  
	    and k.paymentDate::date<=fechaHasta
		and al_pys.docstatus IN ('CO', 'CL')
		and s.isActive='Y'
		),0)) AS PagosCuotasVencidas

	FROM C_Project cp	
	inner join tb_estadoContrato tbec on tbec.tb_estadocontrato_id = cp.tb_estadocontrato_id		
	WHERE cp.ad_org_id=organizacion	
	AND tbec.IsValid='Y' AND cp.IsActive='Y' AND tbec.IsActive='Y'
	AND tbec.esVenta='Y'
	AND cp.tipoFinanciamiento IN('DIR','CON','HIP')
	and cp.CarteraRep_ID=oficial
	AND cp.IsInDispute='N' 	

 ) into valor;
 end if;
 

	return valor;
end
$BODY$;

ALTER FUNCTION adempiere.calculo_comision_cobranza(numeric, numeric, character varying, date, date, integer, integer)
    OWNER TO adempiere;