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


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

DECLARE @Fam_Cuota		VARCHAR(200)
		,@Sql			NVARCHAR(MAX)
		,@Id_Trans		INT
		,@Mensaje		VARCHAR(100)
		,@UltError		INT
		,@Segundos		INT		

-------------------------------------------------------------------------------
DECLARE @Result_Tabla_Stored_Invocado TABLE (ErrorNumber	INT,
											ErrorSeverity	INT,
											ErrorState		INT,
											ErrorProcedure  VARCHAR(1000),
											ErrorLine		INT,
											ErrorMessage	VARCHAR(4000))

DECLARE @Result_Stored_Invocado				INT
		,@JSON_Parametros_Stored_Invocado	VARCHAR(8000)
		,@rta_JSON_Stored_Invocado			VARCHAR(8000)
-------------------------------------------------------------------------------

DECLARE 	@pLote				VARCHAR(29),
			@pFecha				DATETIME,
			@pDescripcion		VARCHAR(50),
			@pId_Lote_Tipo		SMALLINT,
			@pFecha_Desde		DATETIME,
			@pFecha_Hasta		DATETIME,
			@pId_Usuario		INT,
			@pFlagUsarTrans		TINYINT,
			@pModoDebug			TINYINT,
			@pId_Tipo_Cbte		CHAR(4)


SET @pLote						= ISNULL(CONVERT(VARCHAR(20), dbo.CF_JSON_VALUE(@pJsonRecibido,'Lote')), '')
SET @pFecha						= ISNULL(CONVERT(DATETIME, dbo.CF_JSON_VALUE(@pJsonRecibido,'Fecha')),GETDATE())
SET @pDescripcion				= ISNULL(CONVERT(VARCHAR(50), dbo.CF_JSON_VALUE(@pJsonRecibido,'Descripcion')), '')
SET @pId_Lote_Tipo				= ISNULL(CONVERT(SMALLINT, dbo.CF_JSON_VALUE(@pJsonRecibido,'Id_Lote_Tipo')), 0)
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 @pId_Usuario				= ISNULL(CONVERT(SMALLINT, dbo.CF_JSON_VALUE(@pJsonRecibido,'Id_Usuario')), 0)
SET @pFlagUsarTrans				= ISNULL(CONVERT(TINYINT, dbo.CF_JSON_VALUE(@pJsonRecibido,'FlagUsarTrans')), 0)
SET @pModoDebug					= ISNULL(CONVERT(TINYINT, dbo.CF_JSON_VALUE(@pJsonRecibido,'ModoDebug')), 0)
SET @pId_Tipo_Cbte				= ISNULL(CONVERT(CHAR(4), dbo.CF_JSON_VALUE(@pJsonRecibido,'Id_Tipo_Cbte')), '')


--------------------------------------------------------------------------



BEGIN TRY

IF @pFlagUsarTrans = 1 
BEGIN
	BEGIN TRAN
END

INSERT INTO Cbtes_Lotes (Lote,Fecha,Descripcion,Id_Lote_Tipo,Flag_enProceso,Fecha_Inicio,Fecha_Desde,Fecha_Hasta,Observacion,Id_Tipo_Con)
			VALUES (@pLote,@pFecha,@pDescripcion,@pId_Lote_Tipo,1,GETDATE(),@pFecha_Desde,@pFecha_Hasta,'',0)




SET @Fam_Cuota = dbo.CF_xParam('Fam_CuotasSoc')

CREATE TABLE #Trans (Id_Trans INT)

SET @Sql = 'INSERT INTO #Trans
			SELECT DISTINCT(I.Id_Trans) AS Id_Trans  FROM Cbtes_Items I, Cbtes C, Cbtes_Tipos T, Productos P, Clientes S
				WHERE C.Id_Tipo_Cbte = T.Id_Tipo_Cbte
				AND C.Id_Estado_Cbte = 1
				AND I.Id_Trans = C.Id_Trans
				AND C.Saldo = C.Imp_Total
				AND C.id_cliente=S.id_cliente
				AND T.Compromete_Factura = 1
				AND C.Fecha >= ''' + CONVERT(VARCHAR(8),@pFecha_Desde,112) + '''
				AND C.Fecha < ''' + CONVERT(VARCHAR(8),@pFecha_Hasta + 1,112)  + '''
				AND P.Id_Producto = I.Id_Producto
				AND P.Id_Familia IN ('+ @Fam_Cuota +')
				AND S.Activo=1   
				AND dbo.CF_Produc_Precio(I.Id_Producto,I.Id_Cliente,''' +CONVERT(VARCHAR(8),@pFecha + 1,112)  + ''',C.Id_Contrato,0,'''') <> I.Precio_Grav
				'

IF @pModoDebug = 1 PRINT @Sql
			

EXEC sp_executesql @Sql

SELECT * INTO #Transacciones		FROM Transacciones			WHERE Id_Trans IN (SELECT Id_Trans FROM #Trans)
SELECT * INTO #Cbtes				FROM Cbtes					WHERE Id_Trans IN (SELECT Id_Trans FROM #Trans)
SELECT * INTO #Cbtes_Items			FROM Cbtes_Items			WHERE Id_Trans IN (SELECT Id_Trans FROM #Trans)
SELECT * INTO #Clientes_CtaCte		FROM Clientes_CtaCte		WHERE Id_Trans IN (SELECT Id_Trans FROM #Trans)
SELECT * INTO #Cbtes_Conceptos 		FROM Cbtes_Conceptos		WHERE Id_Trans IN (SELECT Id_Trans FROM #Trans)
SELECT * INTO #Cbtes_Ctas_Contables	FROM Cbtes_Ctas_Contables	WHERE Id_Trans IN (SELECT Id_Trans FROM #Trans)
SELECT * INTO #Cbtes_Items_XCampos	FROM Cbtes_Items_XCampos	WHERE Id_Trans IN (SELECT Id_Trans FROM #Trans)
SELECT * INTO #Cbtes_XCampos		FROM Cbtes_XCampos			WHERE Id_Trans IN (SELECT Id_Trans FROM #Trans)
SELECT * INTO #Cbtes_Aplica			FROM Cbtes_Aplica			WHERE 1 = 0


SELECT * INTO #Transacciones_Fac		FROM #Transacciones			
SELECT * INTO #Cbtes_Fac				FROM #Cbtes					
SELECT * INTO #Cbtes_Items_Fac			FROM #Cbtes_Items			
SELECT * INTO #Cbtes_Items_XCampos_Fac	FROM #Cbtes_Items_XCampos	
SELECT * INTO #Cbtes_XCampos_Fac		FROM #Cbtes_XCampos			
SELECT * INTO #Clientes_CtaCte_Fac		FROM Clientes_CtaCte		WHERE Id_Trans IN (SELECT Id_Trans FROM #Trans)

--pongo en carga las trans para que nadie las toque
UPDATE Cbtes SET Id_Estado_Cbte = 0 WHERE Id_Trans IN (SELECT Id_Trans FROM #Trans)
UPDATE Clientes_CtaCte SET Id_Estado_Cbte = 0 WHERE Id_Trans IN (SELECT Id_Trans FROM #Trans)
--------------------------------------------------

IF @pModoDebug = 1 PRINT 'Cbtes en proceso'


/**************************** NC ******************************/


SET @Id_Trans = (SELECT MAX(Id_Trans) FROM Transacciones) + 1
INSERT INTO Transacciones VALUES (@Id_Trans + (SELECT COUNT(*) FROM #Trans)	,'NC RECALCULO PRECIO' 	,GETDATE() ,-1)

SET @Sql = 'ALTER TABLE #Trans ADD Id_Trans_Nuevo INT IDENTITY(' + CONVERT(VARCHAR(10),@Id_Trans) +',1)'
EXEC sp_executesql @Sql
-----------------------------------------------------------------------------
UPDATE #Cbtes SET Id_Tipo_Cbte = @pId_Tipo_Cbte, Fecha = @pFecha, Fecha_Venc = Fecha_Venc,Periodo = @pFecha
	,Periodo_Iva = @pFecha,Saldo = 0, Id_Trans = T.Id_Trans_Nuevo,Lote = @pLote
	FROM #Cbtes C, #Trans T
	WHERE C.Id_Trans = T.Id_Trans

UPDATE #Cbtes_Items				SET Id_Trans = T.Id_Trans_Nuevo	FROM #Cbtes_Items C, #Trans T WHERE C.Id_Trans = T.Id_Trans

UPDATE #Clientes_CtaCte			
	SET Id_Trans = T.Id_Trans_Nuevo	, Saldo = 0, Importe = Importe * -1,Id_Estado_Cbte = 2
	FROM #Clientes_CtaCte C, #Trans T WHERE C.Id_Trans = T.Id_Trans

UPDATE #Cbtes_Conceptos 		SET Id_Trans = T.Id_Trans_Nuevo	FROM #Cbtes_Conceptos C, #Trans T WHERE C.Id_Trans = T.Id_Trans

ALTER TABLE #Cbtes_Ctas_Contables ADD Imp_ DECIMAL(16,4)
UPDATE #Cbtes_Ctas_Contables	SET Id_Trans = T.Id_Trans_Nuevo,Imp_ = Debe	FROM #Cbtes_Ctas_Contables C, #Trans T WHERE C.Id_Trans = T.Id_Trans
UPDATE #Cbtes_Ctas_Contables	SET Debe = Haber, Haber = Imp_
ALTER TABLE #Cbtes_Ctas_Contables DROP COLUMN Imp_ 

UPDATE #Cbtes_Items_XCampos		SET Id_Trans = T.Id_Trans_Nuevo	FROM #Cbtes_Items_XCampos C, #Trans T WHERE C.Id_Trans = T.Id_Trans
UPDATE #Cbtes_XCampos			SET Id_Trans = T.Id_Trans_Nuevo	FROM #Cbtes_XCampos C, #Trans T WHERE C.Id_Trans = T.Id_Trans


INSERT INTO #Cbtes_Aplica (Id_Trans,Item,Id_Trans_D,Item_D,Cantidad,Importe,Fecha_Aplic)
	SELECT T.Id_Trans,1,T.Id_Trans_Nuevo,1,0,C.Imp_Total,GETDATE() FROM #Trans T, Cbtes C  WHERE C.Id_Trans = T.Id_Trans


UPDATE #Transacciones SET Id_Trans = Id_Trans_Nuevo, Fecha_Mov = GETDATE(),Descripcion = 'RECALCULO PRECIO'
	FROM #Transacciones C, #Trans T WHERE C.Id_Trans = T.Id_Trans
-------------------------------------------------------
INSERT INTO Transacciones			SELECT * FROM #Transacciones		
INSERT INTO Cbtes					SELECT * FROM #Cbtes				
INSERT INTO Cbtes_Items				SELECT * FROM #Cbtes_Items			
INSERT INTO Clientes_CtaCte			SELECT * FROM #Clientes_CtaCte		
INSERT INTO Cbtes_Conceptos			SELECT * FROM #Cbtes_Conceptos 		
INSERT INTO Cbtes_Ctas_Contables	SELECT * FROM #Cbtes_Ctas_Contables	
INSERT INTO Cbtes_Items_XCampos		SELECT * FROM #Cbtes_Items_XCampos	
INSERT INTO Cbtes_XCampos			SELECT * FROM #Cbtes_XCampos		
INSERT INTO Cbtes_Aplica			SELECT * FROM #Cbtes_Aplica			

DROP TABLE #Transacciones		
DROP TABLE #Cbtes				
DROP TABLE #Cbtes_Items			
DROP TABLE #Clientes_CtaCte		
DROP TABLE #Cbtes_Conceptos 		
DROP TABLE #Cbtes_Ctas_Contables	
DROP TABLE #Cbtes_Items_XCampos	
DROP TABLE #Cbtes_XCampos		
DROP TABLE #Cbtes_Aplica	


UPDATE Cbtes SET Id_Estado_Cbte = 2,Saldo = 0 WHERE Id_Trans IN (SELECT Id_Trans FROM #Trans)
UPDATE Clientes_CtaCte SET Id_Estado_Cbte = 2,Saldo = 0 WHERE Id_Trans IN (SELECT Id_Trans FROM #Trans)

UPDATE Clientes_CtaCte SET Descripcion = dbo.CF_Cbte_Descrip(Id_Trans) WHERE Id_Trans IN (SELECT Id_Trans_Nuevo FROM #Trans)


IF @pModoDebug = 1 PRINT 'NC Agregadas'


/**************************** FAC ******************************/


SET @Id_Trans = (SELECT MAX(Id_Trans) FROM Transacciones) + 1
INSERT INTO Transacciones VALUES (@Id_Trans + (SELECT COUNT(*) FROM #Trans)	,'RECALCULO PRECIO'	,GETDATE()	,-1)
ALTER TABLE #Trans DROP COLUMN Id_Trans_Nuevo

SET @Sql = 'ALTER TABLE #Trans ADD Id_Trans_Nuevo INT IDENTITY(' + CONVERT(VARCHAR(10),@Id_Trans) +',1)'
EXEC sp_executesql @Sql
-----------------------------------------------------------------------------
UPDATE #Cbtes_Fac SET Fecha = @pFecha, Fecha_Venc = @pFecha,Periodo = @pFecha
	,Periodo_Iva = @pFecha, Id_Trans = T.Id_Trans_Nuevo,Lote = @pLote,Id_Estado_Cbte = 0
	FROM #Cbtes_Fac C, #Trans T
	WHERE C.Id_Trans = T.Id_Trans

UPDATE #Cbtes_Items_Fac				SET Id_Trans = T.Id_Trans_Nuevo,
		Precio_Grav = dbo.CF_Produc_Precio(I.Id_Producto,I.Id_Cliente,@pFecha,C.Id_Contrato,0,'')
		FROM #Cbtes_Items_Fac I, #Trans T, Cbtes C 
		WHERE I.Id_Trans = T.Id_Trans AND C.Id_Trans = I.Id_Trans


UPDATE #Cbtes_Items_Fac				SET Imp_Gravado = I.Cantidad * I.Precio_Grav,
		Precio = Precio_Grav, Imp_Final = (I.Cantidad * I.Precio_Grav * (1 + (Porc_Iva / 100)))
		FROM #Cbtes_Items_Fac I, #Trans T
		WHERE I.Id_Trans = T.Id_Trans_Nuevo 


UPDATE #Cbtes_Items_XCampos_Fac		SET Id_Trans = T.Id_Trans_Nuevo	
		FROM #Cbtes_Items_XCampos_Fac C, #Trans T WHERE C.Id_Trans = T.Id_Trans
UPDATE #Cbtes_XCampos_Fac			SET Id_Trans = T.Id_Trans_Nuevo	
		FROM #Cbtes_XCampos_Fac C, #Trans T WHERE C.Id_Trans = T.Id_Trans

UPDATE #Clientes_CtaCte_Fac			SET Id_Trans = T.Id_Trans_Nuevo,Fecha = @pFecha , Fecha_Vence = @pFecha	
		FROM #Clientes_CtaCte_Fac C, #Trans T WHERE C.Id_Trans = T.Id_Trans

UPDATE #Transacciones_Fac SET Id_Trans = T.Id_Trans_Nuevo, Fecha_Mov = GETDATE(),Descripcion = 'RECALCULO PRECIO'
	FROM #Transacciones_Fac C, #Trans T WHERE C.Id_Trans = T.Id_Trans
-------------------------------------------------------
INSERT INTO Transacciones			SELECT * FROM #Transacciones_Fac		
INSERT INTO Cbtes					SELECT * FROM #Cbtes_Fac				
INSERT INTO Cbtes_Items				SELECT * FROM #Cbtes_Items_Fac			
INSERT INTO Cbtes_Items_XCampos		SELECT * FROM #Cbtes_Items_XCampos_Fac	
INSERT INTO Cbtes_XCampos			SELECT * FROM #Cbtes_XCampos_Fac		
INSERT INTO Clientes_CtaCte			SELECT * FROM #Clientes_CtaCte_Fac		

IF @pModoDebug = 1 PRINT 'Fac Agregadas'

----------------------------------------------------
DECLARE CurAumento CURSOR FOR 
	SELECT Id_Trans FROM #Transacciones_Fac
OPEN CurAumento
FETCH NEXT FROM CurAumento INTO @Id_Trans

WHILE @@FETCH_STATUS = 0
BEGIN
	IF @pModoDebug = 1 PRINT @Id_Trans

	SET @JSON_Parametros_Stored_Invocado =	'{' +
											dbo.CF_Json_ParsearVariable('FlagUsarTrans', 0, 'TINYINT') + ',' +
											dbo.CF_Json_ParsearVariable('ModoDebug', @pModoDebug, 'TINYINT') + ',' +
											dbo.CF_Json_ParsearVariable('Flag_Tabla_Temp', 0, 'TINYINT') + ',' +											
											dbo.CF_Json_ParsearVariable('Id_Trans', @Id_Trans, 'INT') + 
											'}'

	DELETE @Result_Tabla_Stored_Invocado

	IF @pModoDebug = 1 PRINT @JSON_Parametros_Stored_Invocado

	INSERT INTO @Result_Tabla_Stored_Invocado
	EXEC @Result_Stored_Invocado = dbo.CPJ_Cbtes_Fac_Fin @JSON_Parametros_Stored_Invocado
	IF ISNULL(@Result_Stored_Invocado, 0) <> 0 RAISERROR ('Error when calling CPJ_Cbtes_Fac_Fin', 16,  1 ) 

	FETCH NEXT FROM CurAumento INTO @Id_Trans
END
CLOSE CurAumento
DEALLOCATE CurAumento

----------------------------------------------------

DROP TABLE #Transacciones_Fac		
DROP TABLE #Cbtes_Fac				
DROP TABLE #Cbtes_Items_Fac			
DROP TABLE #Cbtes_Items_XCampos_Fac	
DROP TABLE #Cbtes_XCampos_Fac		

-----------------------------------------------------

DROP TABLE #Trans
		

-------------------------------------------------------------------------------


UPDATE Cbtes_Lotes SET	Cantidad = (SELECT COUNT(*) FROM Cbtes WHERE Lote = @pLote) ,
								Importe =  (SELECT SUM(Imp_Total) FROM Cbtes WHERE Lote = @pLote),
								Fecha_Finalizacion = GETDATE(),
								Flag_enProceso = 0
				WHERE Lote = @pLote

IF @pFlagUsarTrans = 1 COMMIT


SET @Segundos = (SELECT DATEDIFF(s,Fecha_Inicio,Fecha_Finalizacion) FROM CBTES_LOTES WHERE LOTE = @pLote) 
SET @Mensaje = 'Minutos Transcurridos: ' + CONVERT(VARCHAR(10),(@Segundos / 60 )  ) + ' ( ' + CONVERT(VARCHAR(10),(@Segundos)) +' segundos).'
 
SELECT	 0				AS ErrorNumber 
		,0				AS ErrorSeverity  
		,0				AS ErrorState 
		,0				AS ErrorProcedure  
		,0				AS ErrorLine		
		,@Mensaje		AS ErrorMessage

END TRY  


BEGIN CATCH  
	IF ISNULL(@Result_Stored_Invocado,0) <> 0
		SELECT * FROM @Result_Tabla_Stored_Invocado
    ELSE
		EXECUTE usp_GetErrorInfo

	UPDATE Cbtes_Lotes SET	Cantidad = (SELECT COUNT(*) FROM Cbtes WHERE Lote = @pLote) ,
							Importe =  (SELECT SUM(Imp_Total) FROM Cbtes WHERE Lote = @pLote),
							Fecha_Finalizacion = GETDATE(),
							Flag_enProceso = 0
			WHERE Lote = @pLote

	IF @pFlagUsarTrans = 1 ROLLBACK
	
   

	IF (SELECT CURSOR_STATUS('global','CurAumento') ) = -1
		DEALLOCATE CurAumento		 

	IF (SELECT CURSOR_STATUS('global','CurAumento') ) = 1
	BEGIN
		CLOSE CurAumento
		DEALLOCATE CurAumento		 
	END


	RETURN 9999
END CATCH

RETURN 0






/*

DECLARE @Param VARCHAR(5000)

SET @Param = '{' +
			'"Lote":"PRUEBA-AUMENTO",' +
			'"Fecha":"20210301",' +
			'"Descripcion":"PRUEBA-AUMENTO",' +
			'"Id_Lote_Tipo":"1",' + 
			'"Fecha_Desde":"20201201",' +
			'"Fecha_Hasta":"20210228",' +
			'"Id_Tipo_Cbte":"NC",' +
			'"Id_Usuario":"-1",' +
			'"FlagUsarTrans":"1",' +
			'"ModoDebug":"1"' +
			'}'

EXEC CPJ_Cbtes_Aumentar @Param

*/