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

CREATE PROCEDURE [dbo].[CPJ_Verificacion_Saldos_Clientes]  @pJsonRecibido VARCHAR(8000)	 

AS

DECLARE 	@pFecha_Desde		DATETIME,
			@pFecha_Hasta		DATETIME,
			@pAgrupaCli         TINYINT


SET @pFecha_Desde				= ISNULL(CONVERT(DATETIME, dbo.CF_JSON_VALUE(@pJsonRecibido,'Fecha_Desde')),GETDATE())
SET @pFecha_Hasta				= ISNULL(CONVERT(DATETIME, dbo.CF_JSON_VALUE(@pJsonRecibido,'Fecha_Hasta')),GETDATE())
SET @pAgrupaCli			     	= ISNULL(CONVERT(TINYINT, dbo.CF_JSON_VALUE(@pJsonRecibido,'AgrupaCli')),0)

--VERSION: 11/02/2022

		select c.id_cliente,cast(sum(Isnull(t.Importe,0)*Isnull(c.Cotizacion,1)) as decimal(14,2)) as DEUDA
		Into #Verificacion
		from  clientes_ctacte t, Cbtes C, Clientes K 
		where c.id_trans = t.id_trans
		and c.id_cliente = K.id_cliente
		and t.Id_Estado_Cbte  in (1,2,4)
		and c.id_cliente > 1
		group by  c.id_cliente

	
		select C.id_cliente, sum(isnull(c.imp_total,0)*t.compromete_factura*Isnull(c.cotizacion,1))  as Rescta
		Into #Facturas
		from cbtes c, cbtes_tipos t
		where c.id_tipo_cbte = t.Id_Tipo_Cbte
		and compromete_factura in (1,-1)
		and c.Id_Estado_Cbte  in (1,2,4)
		and c.Id_Cond_Pago <> 1  
		and c.id_cliente > 1
		group by  c.id_cliente


		select c.id_cliente,sum(Isnull(c.imp_total,0)*Isnull(c.cotizacion,1)) as recibos
		into #Recibos
		from cbtes c, cbtes_tipos t, Clientes K 
		where c.id_tipo_cbte = t.Id_Tipo_Cbte
		and c.id_cliente = K.id_cliente
		and compromete_recibos in (1,-1)
		and c.Id_Estado_Cbte  in (1,2,4)
		and c.id_cliente > 1
		group by  c.id_cliente

		ALTER TABLE #Verificacion ADD Rescta Decimal(14,2),Diferencia Decimal(14,2), Cliente_Ref INT

		UPDATE #Verificacion SET Rescta = 0, DIFERENCIA = 0, Cliente_Ref = 0

        INSERT INTO #Verificacion 
		       select F.Id_Cliente,0,0,0,0 from #Facturas F  where NOT EXISTS 
			          (SELECT 1 FROM #Verificacion v WHERE V.ID_CLIENTE = F.ID_CLIENTE)

        INSERT INTO #Verificacion 
		       select F.Id_Cliente,0,0,0,0 from #Recibos F  where NOT EXISTS 
			          (SELECT 1 FROM #Verificacion v WHERE V.ID_CLIENTE = F.ID_CLIENTE)

		Update #Verificacion set Rescta = R.Rescta  from #Verificacion C, #Facturas R Where C.Id_Cliente = R.Id_Cliente

		Update #Verificacion set Rescta = C.Rescta - R.Recibos from #Verificacion C, #Recibos R Where C.Id_Cliente = R.Id_Cliente

		Update #Verificacion set Diferencia = Rescta - Deuda 
 
		UPDATE #Verificacion SET Cliente_Ref = isnull(C.ID_Cliente_Ref,0) FROM #Verificacion V, CLIENTES C
		WHERE V.Id_Cliente = C.Id_Cliente 

		Update #Verificacion SET Cliente_Ref = Id_Cliente where Cliente_Ref = 0
   

    IF @pAgrupaCli <> 1
		   BEGIN
			  SELECT v.id_cliente,c.razon_social,SUM(Rescta) as rescta,SUM(Deuda) as Deuda, SUM(Diferencia) as Diferencia FROM #Verificacion V,Clientes C 
			  where c.id_cliente = V.Id_cliente   GROUP BY v.id_cliente,c.razon_social having SUM(Diferencia) <> 0 order by C.Razon_Social
		   END
		ELSE
		   BEGIN
			  SELECT v.Cliente_Ref,c.razon_social,SUM(Rescta) as Rescta,SUM(Deuda) as Deuda ,SUM(Diferencia) as Diferencia FROM #Verificacion V,Clientes C 
			  where c.id_cliente = V.Cliente_Ref  GROUP BY v.Cliente_Ref,c.razon_social having SUM(Diferencia) <> 0 order by C.Razon_Social
		   END		  
 
		DROP TABLE #Verificacion
		DROP TABLE #Recibos
		DROP TABLE #Facturas


	--	CPJ_Verificacion_Saldos_Clientes '{"AgrupaCli":"1"}' 


-- SELECT * FROM CBTES C, Clientes_CtaCte K WHERE C.Id_Trans = k.Id_Trans and c.Id_Cliente <> k.Id_Cliente

-- SELECT Id_Cliente_Ref,* FROM CLIENTES K WHERE K.Id_Cliente_Ref = 0