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

CREATE PROCEDURE [dbo].CPJ_Clientes_Deuda_a_Fecha @pJsonRecibido VARCHAR(8000)
AS 
SET NOCOUNT ON
/*Parametros*/

DECLARE 	@pFechaHasta			DATETIME,
			@pId_Unid_Negocio       VARCHAR(10),
			@pFiltrar_con_saldo     TINYINT,
			@pClientes_activos      TINYINT,
			@pFiltroFecha           TINYINT,
			@pCriterio              VARCHAR(2),
			@pId_Cliente            INT
			 
SET @pId_Unid_Negocio			= ISNULL(CONVERT(VARCHAR(10), dbo.CF_JSON_VALUE(@pJsonRecibido,'Id_Unid_Negocio')),'')
SET @pFechaHasta				= ISNULL(CONVERT(DATETIME, dbo.CF_JSON_VALUE(@pJsonRecibido,'FechaHasta')),GETDATE())
SET @pFiltrar_con_saldo			= ISNULL(CONVERT(TINYINT, dbo.CF_JSON_VALUE(@pJsonRecibido,'Filtrar_con_saldo')),0)
SET @pClientes_activos			= ISNULL(CONVERT(TINYINT, dbo.CF_JSON_VALUE(@pJsonRecibido,'Clientes_activos')),0)
SET @pFiltroFecha		    	= ISNULL(CONVERT(TINYINT, dbo.CF_JSON_VALUE(@pJsonRecibido,'FiltroFecha')),0)
SET @pCriterio			        = ISNULL(CONVERT(VARCHAR(2), dbo.CF_JSON_VALUE(@pJsonRecibido,'Criterio')),'R')
SET @pId_Cliente  		    	= ISNULL(CONVERT(INT, dbo.CF_JSON_VALUE(@pJsonRecibido,'Id_Cliente')),0)

 
DECLARE @pFecha DATETIME
SET @pFecha  = @pFechaHasta+1

DECLARE @Agrupar_GF TinyInt
SET @Agrupar_GF =  dbo.CF_xParam('Cli_Ref_CtaCte_Agrup')

Select Id_Cliente_Ref
    	into #Clientes from Clientes where 1=0

IF @Agrupar_GF <> 1
   BEGIN
		If @pId_Cliente <> 0
           INSERT INTO #Clientes SELECT Id_Cliente as Id_Cliente_Ref 
										from Clientes where Id_Cliente = @pId_Cliente
		  Else
		   If @pClientes_activos = 1
              INSERT INTO #Clientes SELECT Id_Cliente as Id_Cliente_Ref 
										from Clientes where Activo = 1 and Id_cliente > 1
			Else
              INSERT INTO #Clientes SELECT Id_Cliente as Id_Cliente_Ref 
										from Clientes where Activo = 1 and Id_cliente > 1
   END
  ELSE
   BEGIN
		Select Id_cliente,Id_Cliente_Ref,Razon_Social
    			into #Clientes_Temp from Clientes where 1=0

		If @pId_Cliente <> 0
			 Insert Into #Clientes_Temp select Id_cliente, case ISNULL(Id_Cliente_Ref,0) 
										WHEN 0 THEN Id_Cliente
										ELSE Id_Cliente_Ref
										END AS Id_Cliente_Ref,Razon_Social
										from Clientes where Id_Cliente = @pId_Cliente
		  Else
		   If @pClientes_activos = 1
			  Insert Into #Clientes_Temp Select Id_cliente, case ISNULL(Id_Cliente_Ref,0) 
										WHEN 0 THEN Id_Cliente
										ELSE Id_Cliente_Ref
										END AS Id_Cliente_Ref,Razon_Social
										from Clientes where Activo = 1 and Id_cliente > 1
			Else
			  Insert Into #Clientes_Temp select Id_cliente, case ISNULL(Id_Cliente_Ref,0) 
										WHEN 0 THEN Id_Cliente
										ELSE Id_Cliente_Ref
										END AS Id_Cliente_Ref,Razon_Social
										from Clientes where  Id_cliente > 1

 
			 INSERT INTO #Clientes SELECT DISTINCT(Id_Cliente_Ref) FROM #Clientes_Temp
		END




Select Id_unid_Negocio into #Unid_Negocios from Empresas_Unid_Negocios
If @pId_Unid_Negocio <> ''
   Delete #Unid_Negocios where Id_unid_Negocio <> @pId_Unid_Negocio

--paso 1 FC


SELECT K.Id_Cliente_Ref AS Id_Cliente
			,C.ID_TIPO_CBTE,C.COMPROBANTE_NRO,C.FECHA,(C.Imp_Total * T.Compromete_Factura) AS IMP_TOTAL
			,Isnull((SELECT SUM(ISNULL(A.Importe,0)) 
				 FROM cbtes_aplica a, cbtes p, Cbtes_Tipos tp 
				 WHERE p.Id_Trans = A.ID_TRANS_D  AND A.ID_TRANS = C.Id_Trans and p.Id_Tipo_Cbte = tp.Id_Tipo_Cbte 
					   and  ( tp.Compromete_Recibos in (1,-1) OR tp.Compromete_Factura in (-1))
					   AND P.FECHA < @pFecha AND p.id_estado_cbte not in (0,3)),0) AS Imp_Aplicado 
			,CAST(0 AS DECIMAL(12,2)) AS Imp_Aplicado2
			,(C.SALDO * T.Compromete_Factura) AS SALDO,C.ID_TRANS,1 AS SIGNO
	INTO #DEUDA
	FROM CBTES C, CBTES_TIPOS T, Clientes O,#Clientes k, #Unid_Negocios U
	WHERE C.Id_Tipo_Cbte = T.ID_TIPO_CBTE
	    AND C.Id_Cliente = O.ID_Cliente
		AND CASE ISNULL(O.Id_Cliente_Ref,0) 
								WHEN 0 THEN O.Id_Cliente
								ELSE O.Id_Cliente_Ref
								END = K.Id_Cliente_Ref
		AND T.Compromete_Factura IN (1)
		AND U.Id_Unid_Negocio = T.Id_Unid_Negocio
		AND C.Id_Estado_Cbte NOT IN (0,3)
		AND C.Id_Cond_Pago <> 1
		AND C.Id_Cliente > 1
		AND C.FECHA < @pFecha
		AND ( NOT EXISTS  (SELECT A.ID_TRANS FROM CBTES_APLICA A WHERE C.ID_TRANS = A.ID_TRANS) OR C.SALDO <> 0
		OR (SELECT MAX(p.fecha)
					 FROM cbtes_aplica a, cbtes p, Cbtes_Tipos tp 
					 WHERE p.Id_Trans = A.ID_TRANS_D  AND A.ID_TRANS = C.Id_Trans 
					 and p.Id_Tipo_Cbte = tp.Id_Tipo_Cbte 
					 AND p.id_estado_cbte not in (0,3)
					 and (tp.Compromete_Recibos in (1,-1) 
						OR ( tp.Compromete_Factura in (-1)   AND P.Id_Cond_Pago <> 1)
						)
			 ) >= @pFecha )

-- paso 2 NC

UNION ALL

SELECT K.Id_Cliente_Ref AS Id_Cliente
			,C.ID_TIPO_CBTE,C.COMPROBANTE_NRO,C.FECHA,(C.IMP_TOTAL* T.Compromete_Factura) AS IMP_TOTAL
			,(SELECT isnull(SUM(A.Importe),0)
					 FROM cbtes_aplica a, cbtes p, Cbtes_Tipos tp 
					 WHERE p.Id_Trans = A.ID_TRANS AND A.ID_TRANS_D = C.Id_Trans and p.Id_Tipo_Cbte = tp.Id_Tipo_Cbte 
						   and  ( tp.Compromete_Recibos in (1,-1) OR tp.Compromete_Factura in (1))
						   AND P.Id_Cond_Pago <> 1
						   AND P.FECHA < @pFecha AND p.id_estado_cbte not in (0,3)) AS Imp_Aplicado 
			,CAST(0 AS DECIMAL(12,2)) AS Imp_Aplicado2
			,(C.SALDO* T.Compromete_Factura) AS SALDO,C.ID_TRANS,-1 AS SIGNO
	FROM CBTES C, CBTES_TIPOS T, Clientes O,#Clientes K , #Unid_Negocios U
	WHERE C.Id_Tipo_Cbte = T.ID_TIPO_CBTE
	    AND C.Id_Cliente = O.ID_Cliente
		AND CASE ISNULL(O.Id_Cliente_Ref,0) 
								WHEN 0 THEN O.Id_Cliente
								ELSE O.Id_Cliente_Ref
								END = K.Id_Cliente_Ref
		AND T.Compromete_Factura IN (-1)
		AND C.Id_Cond_Pago <> 1
		AND U.Id_Unid_Negocio = T.Id_Unid_Negocio
		AND C.Id_Estado_Cbte NOT IN (0,3)
		AND C.Id_Cliente > 1
		AND C.FECHA < @pFecha
		--and C.Fecha >= '20200101'
		AND ( NOT EXISTS  (SELECT A.ID_TRANS FROM CBTES_APLICA A WHERE C.ID_TRANS = A.ID_TRANS)
		OR 	(SELECT MAX(p.fecha)
					 FROM cbtes_aplica a, cbtes p, Cbtes_Tipos tp 
					 WHERE p.Id_Trans = A.ID_TRANS_D  AND A.ID_TRANS = C.Id_Trans 
					 and p.Id_Tipo_Cbte = tp.Id_Tipo_Cbte 
					 AND p.id_estado_cbte not in (0,3)
					 and (tp.Compromete_Recibos in (1,-1) 
						OR (tp.Compromete_Factura in (-1) AND P.Id_Cond_Pago <> 1)
						)
			 ) >= @pFecha )

--paso 3 OP

UNION ALL

SELECT  K.Id_Cliente_Ref AS Id_Cliente
		,C.ID_TIPO_CBTE,C.COMPROBANTE_NRO,C.FECHA,(C.IMP_TOTAL* -1) as IMP_TOTAL
		,(SELECT ISNULL(SUM(A.IMPORTE),0) FROM CBTES_APLICA A, CBTES K1 WHERE C.ID_TRANS = A.ID_TRANS_D AND K1.FECHA < @pFecha
			  AND A.ID_TRANS = K1.ID_TRANS AND K1.ID_ESTADO_CBTE NOT IN (0,3)  and a.id_trans <> 0
			  AND ISNULL((SELECT COUNT(*) FROM CBTES_APLICA CA WHERE C.ID_TRANS = CA.ID_TRANS_D),0) > = 1 
			  )  as Imp_Aplicado
		,(SELECT ISNULL(SUM(A.IMPORTE),0) FROM CBTES_APLICA A, CBTES K WHERE C.ID_TRANS = A.ID_TRANS AND K.FECHA < @pFecha
			  AND A.ID_TRANS_D = K.ID_TRANS AND K.ID_ESTADO_CBTE NOT IN (0,3) )  as Imp_Aplicado2,
			  C.SALDO,C.ID_TRANS,-1 AS SIGNO
	FROM CBTES C, CBTES_TIPOS T, Clientes O,#Clientes K , #Unid_Negocios U
	WHERE C.Id_Tipo_Cbte = T.ID_TIPO_CBTE
	    AND C.Id_Cliente = O.ID_Cliente
		AND CASE ISNULL(O.Id_Cliente_Ref,0) 
								WHEN 0 THEN O.Id_Cliente
								ELSE O.Id_Cliente_Ref
								END = K.Id_Cliente_Ref
		AND T.Compromete_Recibos IN (1,-1)
		AND U.Id_Unid_Negocio = T.Id_Unid_Negocio
		AND C.Id_Estado_Cbte NOT IN (0,3)
		AND C.Id_Cliente > 1
		AND C.FECHA < @pFecha
		AND (((SELECT ISNULL(MAX(A.ID_TRANS),0) FROM CBTES_APLICA A , CBTES K WHERE C.ID_TRANS = A.Id_Trans_D
			 AND A.ID_TRANS = K.ID_TRANS AND K.ID_ESTADO_CBTE NOT IN (0,3)
			 AND K.FECHA < @pFecha
			 ) = 0)
		   OR (SELECT ISNULL(SUM(A.IMPORTE),0) FROM CBTES_APLICA A, CBTES K WHERE C.ID_TRANS = A.Id_Trans_D
			  AND A.ID_TRANS = K.ID_TRANS AND K.ID_ESTADO_CBTE NOT IN (0,3)	AND A.ID_TRANS <> 0
			  AND K.FECHA < @pFecha
			  ) <> ABS(C.IMP_TOTAL) )
		AND (SELECT ISNULL(ABS(SUM(A.IMPORTE)),0)  FROM CBTES_APLICA A, CBTES K WHERE C.Id_Trans = A.Id_Trans
			  AND A.ID_TRANS_D = K.ID_TRANS AND K.ID_ESTADO_CBTE NOT IN (0,3)
			  AND K.FECHA < @pFecha
			  ) <> ABS(C.IMP_TOTAL) 


UPDATE #DEUDA SET Imp_Aplicado = abs(IMP_TOTAL), Saldo = 0  FROM #DEUDA WHERE  ABS(Imp_Aplicado) > abs(IMP_TOTAL)

if @pCriterio = 'D'
	   SELECT D.Id_Cliente,K.Razon_Social,D.Fecha,C.Fecha_Venc,DBO.CF_Cbte_Descrip(D.ID_TRANS) AS Descripcion,c.Id_Moneda AS Moneda, ISNULL(D.imp_total,0) - ISNULL(Imp_Aplicado+ABS(Imp_Aplicado2) ,0) * D.Signo  AS IMP_TOTAL,D.Id_Trans 
	   ,(select sum(isnull(i.imp_final,0)) from cbtes_items i where i.id_trans = d.id_trans)-abs(D.imp_total) as items
	   FROM #DEUDA D JOIN  Clientes K ON K.Id_Cliente = D.Id_Cliente , CBTES C 
	   WHERE C.Id_trans = D.Id_Trans AND ISNULL(D.imp_total,0)- ISNULL(Imp_Aplicado+ABS(Imp_Aplicado2),0)*D.SIGNO  <> 0
		  ORDER BY K.Razon_Social,D.Fecha
 

if @pCriterio = 'T'
    IF @pFiltrar_con_saldo = 1
	   BEGIN
		SELECT D.Id_Cliente,K.Razon_Social,SUM(ISNULL(Imp_total,0) - ISNULL(Imp_Aplicado+ABS(Imp_Aplicado2),0) * Signo) as Saldo 
		FROM #DEUDA D JOIN  Clientes K ON K.Id_Cliente = D.Id_Cliente
		   GROUP BY D.Id_Cliente,Razon_Social 
		   HAVING SUM(ISNULL(Imp_total,0) - ISNULL(Imp_Aplicado+ABS(Imp_Aplicado2),0) * Signo) <> 0
		ORDER BY Razon_Social 
  /* 
		SELECT D.Id_Cliente,K.Razon_Social,SUM(ISNULL(Imp_total,0) - ISNULL(Imp_Aplicado+ABS(Imp_Aplicado2),0) * Signo) as Saldo 
		INTO Z_DEUDA_FECHA
		FROM #DEUDA D JOIN  Clientes K ON K.Id_Cliente = D.Id_Cliente
		   GROUP BY D.Id_Cliente,Razon_Social 
		   HAVING SUM(ISNULL(Imp_total,0) - ISNULL(Imp_Aplicado+ABS(Imp_Aplicado2),0) * Signo) <> 0
		ORDER BY Razon_Social 
  */
	   END
      ELSE
		SELECT D.Id_Cliente,K.Razon_Social,SUM((ISNULL(imp_total,0)) - Abs(ISNULL(Imp_Aplicado+ABS(Imp_Aplicado2),0))*SIGNO) as Saldo 
			FROM #DEUDA D JOIN  Clientes K ON K.Id_Cliente = D.Id_Cliente 
		   GROUP BY D.Id_Cliente,K.Razon_Social  
		ORDER BY Razon_Social 

if @pCriterio = 'P' OR @pCriterio = 'TP'
    BEGIN
		SELECT C.Id_Cliente,K.Razon_Social,D.Fecha,C.Fecha_Venc,DBO.CF_Cbte_Descrip(D.ID_TRANS) AS Descripcion,c.Id_Moneda AS Moneda,I.Id_Trans,I.Id_Producto,I.Descripcion_Producto,I.IMP_FINAL*D.SIGNO AS Imp_Prod, ISNULL(D.imp_totaL,0)* D.Signo AS Imp_Fac ,  ISNULL(Imp_Aplicado+ABS(Imp_Aplicado2) ,0) * D.Signo  AS Imp_Apli
			 , ABS((ISNULL(D.imp_total,0)- ISNULL(D.Imp_Aplicado+ABS(D.Imp_Aplicado2),0)*D.SIGNO )/D.imp_total) AS Coef
			 , (I.IMP_FINAL*D.SIGNO) * (ISNULL(D.imp_total,0)- ISNULL(D.Imp_Aplicado+ABS(D.Imp_Aplicado2),0)*D.SIGNO )/D.imp_total AS Saldo,I.Id_Cliente AS Id_Cliente_Item ,H.Razon_Social AS Razon_Social_Item
		INTO #DET
		FROM #DEUDA D JOIN  Clientes K ON K.Id_Cliente = D.Id_Cliente
			, CBTES C  JOIN  CBTES_ITEMS I ON C.ID_TRANS = I.ID_TRANS
				 LEFT JOIN Clientes H ON I.Id_Cliente = H.Id_Cliente
				 JOIN Cbtes_Tipos T ON C.Id_Tipo_Cbte = T.Id_Tipo_Cbte
		WHERE   D.ID_TRANS = C.ID_TRANS AND ISNULL(D.imp_total,0)- ISNULL(D.Imp_Aplicado+ABS(D.Imp_Aplicado2),0)*D.SIGNO  <> 0 
				AND T.Compromete_Recibos NOT IN (1,-1)


		INSERT INTO #DET
			SELECT C.Id_Cliente,K.Razon_Social,D.Fecha,C.Fecha_Venc,DBO.CF_Cbte_Descrip(D.ID_TRANS) AS Descripcion,c.Id_Moneda AS Moneda,C.ID_TRANS, 'ACUENTA' AS ID_PRODUCTO,'PAGOS A CUENTA' AS Descripcion_Producto, 0 AS IMP_PROD, ISNULL(D.imp_totaL,0)* D.Signo AS IMP_FAC ,  ISNULL(Imp_Aplicado+ABS(Imp_Aplicado2) ,0) * D.Signo  AS IMP_APLI
				 , ABS((ISNULL(D.imp_total,0)- ISNULL(D.Imp_Aplicado+ABS(D.Imp_Aplicado2),0)*D.SIGNO )/D.imp_total) AS COEF
				 ,0 AS SALDO ,0 AS Id_Cliente_Item ,'' AS Razon_Social_Item
			FROM #DEUDA D JOIN  Clientes K ON K.Id_Cliente = D.Id_Cliente
				, CBTES C JOIN Cbtes_Tipos T ON C.Id_Tipo_Cbte = T.Id_Tipo_Cbte
			   WHERE   D.ID_TRANS = C.ID_TRANS AND ISNULL(D.imp_total,0)- ISNULL(D.Imp_Aplicado+ABS(D.Imp_Aplicado2),0)*D.SIGNO  <> 0 
			   AND T.Compromete_Recibos IN (1,-1)

		UPDATE  #DET SET IMP_PROD = (IMP_FAC*COEF)*-1, SALDO  = (IMP_FAC*COEF)*-1 
				 WHERE IMP_PROD = 0    AND IMP_FAC >= ABS(IMP_APLI)  AND ID_PRODUCTO ='ACUENTA'

		UPDATE  #DET SET IMP_PROD = (IMP_FAC*COEF), SALDO  = (IMP_FAC*COEF) 
				 WHERE IMP_PROD = 0  AND IMP_FAC < ABS(IMP_APLI)  AND ID_PRODUCTO ='ACUENTA'

        IF @pCriterio = 'P'
		     SELECT * FROM #DET ORDER BY Razon_Social ,Razon_Social_Item,Descripcion_Producto ,Fecha
		ELSE
			 SELECT Id_Producto,Descripcion_Producto,Sum(Saldo) As Saldo 
			   	 FROM #DET
				 Group by Id_Producto,Descripcion_Producto 
				 ORDER BY Descripcion_Producto 

		 DROP TABLE #DET
	 END


DROP TABLE #DEUDA
DROP TABLE #Clientes
DROP TABLE #Unid_Negocios
 


/*
EXEC CPJ_Clientes_Deuda_a_Fecha '{"FechaHasta":"20230930","Id_Unid_Negocio":""
,"Filtrar_con_saldo":"1","Clientes_activos":"0","Criterio":"t","Id_Cliente":""}'

drop table Z_DEUDA_FECHA

EXEC CPJ_Clientes_Deuda_a_Fecha '{"FechaHasta":"20240202","Id_Unid_Negocio":""
,"Filtrar_con_saldo":"1","Clientes_activos":"0","Criterio":"t","Id_Cliente":"1423"}'
37410.00

EXEC CPJ_Clientes_Deuda_a_Fecha '{"FechaHasta":"20240202","Id_Unid_Negocio":""
,"Filtrar_con_saldo":"1","Clientes_activos":"0","Criterio":"t","Id_Cliente":"5271"}'
52110.00

EXEC CPJ_Clientes_Deuda_a_Fecha '{"FechaHasta":"20240202","Id_Unid_Negocio":""
,"Filtrar_con_saldo":"1","Clientes_activos":"0","Criterio":"t","Id_Cliente":"5629"}'
62950.00

EXEC CPJ_Clientes_Deuda_a_Fecha '{"FechaHasta":"20230930","Id_Unid_Negocio":""
,"Filtrar_con_saldo":"1","Clientes_activos":"0","Criterio":"d","Id_Cliente":"9839"}'

 
52770.00
drop table Z_DEUDA_FECHA 
*/
-- select * from cbtes_aplica where id_trans = 6036

-- select SUM(IMPORTE)  from cbtes_aplica where id_trans_d = 632361

-- select *  from cbtes  where id_trans = 632361
-- select *  from cbtes_ITEMS  where id_trans = 632361

--- select SUM(iMP_TOTAL) from cbtes   where id_trans  IN (652546,672318,689588,690504,708035,708952,722881,729788)

 
