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,
			@pTipo				CHAR(1)


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)
SET @pTipo				     	= ISNULL(CONVERT(CHAR(1), dbo.CF_JSON_VALUE(@pJsonRecibido,'Tipo')),'')


--VERSION: 11/02/2022
IF UPPER(@pTipo) = 'D'
BEGIN
		SELECT t.Id_Trans,t.Id_Estado_Cbte,SUM(t.importe) AS Importe, SUM(T.Saldo) AS Saldo INTO #CtatCteSaldos FROM  clientes_ctacte t WHERE t.Id_Estado_Cbte NOT in (0,3) GROUP BY t.Id_Trans,t.Id_Estado_Cbte
	
		select * from #CtatCteSaldos where saldo <>0 and Id_Estado_Cbte=2
		
		select c.id_cliente,c.id_trans,C.Imp_Total,C.Saldo,t.Saldo as SaldoCtacte,C.Id_Estado_Cbte,t.Id_Estado_Cbte AS Id_Estado_CtaCte
		INTO #VerificacionFact
		from  Cbtes C LEFT JOIN #CtatCteSaldos t ON  c.id_trans = t.id_trans, Clientes K , Cbtes_Tipos e
		where c.id_trans = t.id_trans
		and c.id_cliente = K.id_cliente
		and t.Id_Estado_Cbte NOT in (0,3)
		and c.Id_Estado_Cbte NOT in (0,3)
		and c.id_tipo_cbte = e.Id_Tipo_Cbte
		and e.compromete_factura in (1,-1)
		and c.id_cliente > 1
		AND C.Imp_Total <> 0
		AND C.Saldo <> 0
		AND t.Saldo <> 0
		AND (c.Id_Estado_Cbte <> t.Id_Estado_Cbte
		OR ABS(C.Saldo)  <> ABS(t.Saldo)
		OR ABS(C.Imp_Total)  <> ABS(t.Importe) 
		)

	
		--select a.id_trans_D,importe,z.* from #VerificacionFact z, Cbtes_Aplica a where  z.Id_trans=a.Id_trans and id_Estado_Cbte=1

		select c.id_cliente,c.id_trans,C.Imp_Total,C.Saldo,t.Saldo as SaldoCtacte,C.Id_Estado_Cbte,t.Id_Estado_Cbte AS Id_Estado_CtaCte
		INTO #VerificacionRec
		from  Cbtes C LEFT JOIN #CtatCteSaldos t ON  c.id_trans = t.id_trans, Clientes K , Cbtes_Tipos e
		where c.id_trans = t.id_trans
		and c.id_cliente = K.id_cliente
		and t.Id_Estado_Cbte NOT in (0,3)
		and c.Id_Estado_Cbte NOT in (0,3)
		and c.id_tipo_cbte = e.Id_Tipo_Cbte
		and e.compromete_recibos in (1,-1)
		and c.id_cliente > 1
		AND C.Imp_Total <> 0
		AND C.Saldo <> 0
		AND (c.Id_Estado_Cbte <> t.Id_Estado_Cbte
		OR ABS(C.Saldo)  <> ABS(t.Saldo)
		OR ABS(C.Imp_Total)  <> ABS(t.Importe) 
		)

		SELECT * FROM #VerificacionFact
		SELECT * FROM  #VerificacionRec 

		DROP TABLE #CtatCteSaldos
		DROP TABLE #VerificacionFact
		DROP TABLE #VerificacionRec

END
ELSE
BEGIN
		select c.id_cliente,cast(sum(Isnull(t.Saldo,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,4)
		and c.Id_Estado_Cbte  in (1,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
		
END

	--	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
