IF EXISTS ( SELECT * 
            FROM   sysobjects 
            WHERE  id = object_id(N'dbo.CPJ_Cbtes_Interes_Calc') 
                   and OBJECTPROPERTY(id, N'IsProcedure') = 1 )
BEGIN
    DROP PROCEDURE dbo.CPJ_Cbtes_Interes_Calc 
END
GO


CREATE PROCEDURE [dbo].[CPJ_Cbtes_Interes_Calc] @pJsonRecibido VARCHAR(8000)
AS 
SET NOCOUNT ON

DECLARE @pId_Cliente INT
       ,@pId_Unid_Negocio VARCHAR(10)
	   ,@pCriterio VARCHAR(1)
	   ,@pId_Trans INT

SET @pId_Cliente		= ISNULL(CONVERT(INT,dbo.CF_JSON_VALUE(@pJsonRecibido,'Id_Cliente')), 0)
SET @pId_Unid_Negocio	= ISNULL(CONVERT(VARCHAR(10),dbo.CF_JSON_VALUE(@pJsonRecibido,'Id_Unid_Negocio')), 0)
SET @pCriterio	    	= ISNULL(CONVERT(VARCHAR(1),dbo.CF_JSON_VALUE(@pJsonRecibido,'Criterio')), 'D')
SET @pId_Trans		    = ISNULL(CONVERT(INT,dbo.CF_JSON_VALUE(@pJsonRecibido,'Id_Trans')), 0)


SELECT Id_Unid_Negocio INTO #UN FROM Empresas_Unid_Negocios
IF @pId_Unid_Negocio <> ''
    DELETE #UN WHERE Id_Unid_Negocio <> @pId_Unid_Negocio

SELECT I.Id_Trans,I.ID_PRODUCTO,I.Descripcion_producto,I.Imp_Final,C.Imp_Total,C.Saldo
                ,(C.Saldo/C.Imp_Total) as Coef
				, DATEADD(DAY, 1, EOMONTH(C.Fecha, -1)) as Fecha_Desde
				,DATEdiff(dd,DATEADD(DAY, 1, EOMONTH(C.Fecha, -1)),getdate()) AS Dif_dias
				,I.Id_Cond_Pago
				,(Select CP.Porc_Bonif from Cbtes_Cond_Pago_Items Cp 
						where cp.Id_Cond_Pago = I.Id_Cond_Pago
						and DATEdiff(dd,DATEADD(DAY, 1, EOMONTH(C.Fecha, -1)),getdate()) >= CP.Plazo_Desde
						and DATEdiff(dd,DATEADD(DAY, 1, EOMONTH(C.Fecha, -1)),getdate()) < CP.Plazo_Bonif 
						) As Porc_Recargo
				,(Select CP.Plazo_Sin_Recargo from Cbtes_Cond_Pago_Items Cp 
						where cp.Id_Cond_Pago = I.Id_Cond_Pago
						and DATEdiff(dd,DATEADD(DAY, 1, EOMONTH(C.Fecha, -1)),getdate()) >= CP.Plazo_Desde
						and DATEdiff(dd,DATEADD(DAY, 1, EOMONTH(C.Fecha, -1)),getdate()) < CP.Plazo_Bonif 
						) As Plazo_Sin_Recargo
				,(Select CP.Tipo from Cbtes_Cond_Pago_Items Cp 
						where cp.Id_Cond_Pago = I.Id_Cond_Pago
						and DATEdiff(dd,DATEADD(DAY, 1, EOMONTH(C.Fecha, -1)),getdate()) >= CP.Plazo_Desde
						and DATEdiff(dd,DATEADD(DAY, 1, EOMONTH(C.Fecha, -1)),getdate()) < CP.Plazo_Bonif 
						) As Tipo
				,Cast(0 as Decimal(12,2)) as Importe_Recargo
		 INTO #RECARGOS
		 FROM Clientes_CtaCte k, Cbtes C, Cbtes_Tipos T, Cbtes_Items I,#UN U, Productos P
			  WHERE k.ID_CLIENTE = @pId_Cliente 
			  and k.Id_Estado_Cbte in (1,4)
			  and C.Id_Trans = I.Id_Trans
			  And C.Id_Trans = K.Id_Trans
			  And C.Id_Tipo_Cbte = T.Id_Tipo_Cbte
			  And T.Id_Unid_Negocio = U.Id_Unid_Negocio
			  And T.Compromete_Factura IN (1,-1)
			  And T.Letra <> 'L'
			  And P.Id_Producto = I.Id_Producto
			  AND I.Imp_Final <> 0
			  AND I.Id_Cond_Pago IS NOT NULL AND I.Id_Cond_Pago <> 0
		--	  And P.Flag_Mes = 1
			  AND I.Fecha_QA IS NOT NULL
			  ORDER BY k.Fecha_Vence,k.Id_Trans

UPDATE #RECARGOS SET Importe_Recargo =  Imp_Final * (Porc_Recargo / 100)

UPDATE #RECARGOS SET Importe_Recargo =  Imp_Final * (Porc_Recargo * (Dif_Dias - Plazo_Sin_Recargo) / 100)
	          WHERE UPPER(Tipo) = 'D' AND Porc_Recargo > 0 AND Dif_Dias > Plazo_Sin_Recargo

UPDATE #RECARGOS SET Importe_Recargo =  Importe_Recargo * Coef

SELECT DISTINCT(ID_TRANS) INTO #TRANS FROM #RECARGOS

IF @pId_Trans <> 0
	     DELETE #TRANS WHERE ID_TRANS <> @pId_Trans

If @pCriterio = 'R' 
         Select Id_Producto,Sum(Importe_Recargo) Recargo from #RECARGOS R , #TRANS T
		        WHERE R.ID_TRANS = T.ID_TRANS group by Id_Producto

If @pCriterio = 'D'
		 Select * from #RECARGOS R , #TRANS T
               WHERE R.ID_TRANS = T.ID_TRANS  

If @pCriterio = 'T' 
         Select R.Id_TRANS,Sum(Importe_Recargo) Recargo from #RECARGOS R , #TRANS T
		        WHERE R.ID_TRANS = T.ID_TRANS group by R.Id_TRANS

DROP TABLE #RECARGOS
DROP TABLE #UN
DROP TABLE #TRANS

 /*
 EXEC dbo.CPJ_Cbtes_Interes_Calc.   '{
			"Id_Cliente":"19887",
			"Id_Unid_Negocio":"",
			"Criterio":"T",
			"Id_Trans":"493364",
		}'

 EXEC dbo.CPJ_Cbtes_Interes_Calc.   '{
			"Id_Cliente":"19887",
			"Id_Unid_Negocio":"",
			"Criterio":"T",
			"Id_Trans":"",
		}'

*/
    
 