 IF EXISTS ( SELECT * 
            FROM   sysobjects 
            WHERE  id = object_id(N'CPJ_Clientes_Deuda_a_Fecha') 
                   and OBJECTPROPERTY(id, N'IsProcedure') = 1 )
BEGIN
    DROP PROCEDURE 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


Select * into #Clientes from Clientes where 1=0

If @pId_Cliente <> 0
     Insert Into #Clientes select * from Clientes where Id_Cliente = @pId_Cliente
  Else
   If @pClientes_activos = 1
      Insert Into #Clientes select * from Clientes where Activo = 1
	Else
	  Insert Into #Clientes select * from Clientes 


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 C.Id_Cliente,K.Razon_Social,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
INTO #DEUDA
FROM CBTES C, CBTES_TIPOS T, #Clientes k, #Unid_Negocios U
WHERE C.Id_Tipo_Cbte = T.ID_TIPO_CBTE
AND K.Id_Cliente = C.Id_Cliente
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_Cliente IS NOT NULL AND C.Id_Cliente <> 0 
AND C.FECHA < @pFecha
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))
	 ) >= @pFecha )

-- paso 2 NC

UNION ALL

SELECT C.Id_Cliente,K.Razon_Social,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_Compras in (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

FROM CBTES C, CBTES_TIPOS T, #Clientes K , #Unid_Negocios U
WHERE C.Id_Tipo_Cbte = T.ID_TIPO_CBTE
AND K.Id_Cliente = C.Id_Cliente
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_Cliente IS NOT NULL AND C.Id_Cliente <> 0 
AND C.FECHA < @pFecha
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))
	 ) >= @pFecha )

--paso 3 OP
UNION ALL

SELECT  C.Id_Cliente,K.Razon_Social,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 K1 WHERE C.ID_TRANS = A.ID_TRANS_D
  AND A.ID_TRANS = K1.ID_TRANS AND K1.ID_ESTADO_CBTE NOT IN (0,3) AND C.Id_Cliente = K1.Id_Cliente 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 A.ID_TRANS_D = K.ID_TRANS AND K.ID_ESTADO_CBTE NOT IN (0,3) AND C.Id_Cliente = K.Id_Cliente )  as Imp_Aplicado2,
  C.SALDO,C.ID_TRANS
FROM CBTES C, CBTES_TIPOS T, #Clientes K , #Unid_Negocios U
WHERE C.Id_Tipo_Cbte = T.ID_TIPO_CBTE
AND K.Id_Cliente = C.Id_Cliente
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 IS NOT NULL AND C.Id_Cliente <> 0  
AND C.FECHA < @pFecha
AND (((SELECT max(A.ID_TRANS) 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)) = 0)
   OR (SELECT SUM(A.IMPORTE) 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) <> ABS(C.IMP_TOTAL) )
AND isnull((SELECT ABS(SUM(A.IMPORTE)) 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)),0) <> ABS(C.IMP_TOTAL) 



if @pCriterio = 'D'
 
   SELECT D.Id_Cliente,D.Razon_Social,D.Fecha,C.Fecha_Venc,DBO.CF_Cbte_Descrip(D.ID_TRANS) AS Descripcion,c.Id_Moneda AS Signo,ISNULL(D.imp_total,0) - Abs(ISNULL(Imp_Aplicado+Imp_Aplicado2,0))  AS IMP_TOTAL,D.Id_Trans 
   FROM #DEUDA D, CBTES C where C.Id_trans = D.Id_Trans AND ISNULL(D.imp_total,0)- Abs(ISNULL(Imp_Aplicado+Imp_Aplicado2,0)) <> 0
      ORDER BY Razon_Social,D.Fecha
 
 else
    IF @pFiltrar_con_saldo = 1
		SELECT Id_Cliente,Razon_Social,SUM(ISNULL(imp_total,0) - Abs(ISNULL(Imp_Aplicado+Imp_Aplicado2,0))) as Saldo FROM #DEUDA 
		   GROUP BY Id_Cliente,Razon_Social 	 HAVING SUM(ISNULL(imp_total,0) - Abs(ISNULL(Imp_Aplicado+Imp_Aplicado2,0))) <> 0
		ORDER BY Razon_Social 
      ELSE
		SELECT Id_Cliente,Razon_Social,SUM(ISNULL(imp_total,0) - Abs(ISNULL(Imp_Aplicado+Imp_Aplicado2,0))) as Saldo FROM #DEUDA 
		   GROUP BY Id_Cliente,Razon_Social
		ORDER BY Razon_Social 


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

/*

EXEC CPJ_clientes_Deuda_a_Fecha '{"FechaHasta":"20220902","Id_Unid_Negocio":"","Filtrar_con_saldo":"1","Clientes_activos":"1","Criterio":"D","Id_Cliente":"1120"}'

*/
-- select * from cbtes_aplica where id_trans = 6036

-- select * from cbtes_aplica where id_trans_d = 6036

--- select * from cbtes  where id_trans =6036

--- EXEC CPJ_Proveedores_Deuda_a_Fecha '{"FechaHasta":"20220905","Id_Unid_Negocio":"","Filtrar_con_saldo":"1","Proveed_activos":"1"}'

