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

CREATE PROCEDURE [dbo].[CPJ_Cbtes_Rec_Web_Completar] @pJson VARCHAR(8000)

AS
SET NOCOUNT ON
/*
 * LOG VERSIONES:
 * 12/12/2023: Se agrego Log de Versiones
 * Uso: Solo completa (No genera) recibos web donde exista el nro Operacion en WebProcesos
 * Casos de uso:
 *			1. Items faltantes
 *			2. Medios de Pagos Faltantes
 *			3. Cbte en carga y sin numero de comprobante
 *
 * Modo de uso: EXEC CPJ_Cbtes_Rec_Web_Completar '{"ModoDebug":"1" ,"Id_Trans":"1316787"}'
 * Retorno: 	ErrorNumber	ErrorSeverity	ErrorState	ErrorProcedure	ErrorLine	ErrorMessage
 *			S:	0			0				0			0				0			Recibo Completado | Nro. Operacion no abierta | Las transacciones asociadas estan aplicadas a otro comprobante, quedando la operación en estado parcial o pago a cuenta
 *			E:	70001		0				0			0				0			Transaccion no abierta									
*/

/*Declaracion Variables Json*/
DECLARE @pFlagUsarTrans			TINYINT
DECLARE @pModoDebug				TINYINT
DECLARE @pId_Trans				INT

SET @pFlagUsarTrans				= ISNULL(CONVERT(TINYINT, dbo.CF_JSON_VALUE(@pJson,'FlagUsarTrans')), 0)
SET @pModoDebug					= ISNULL(CONVERT(TINYINT, dbo.CF_JSON_VALUE(@pJson,'ModoDebug')), 0)
SET @pId_Trans					= ISNULL(CONVERT(INT, dbo.CF_JSON_VALUE(@pJson,'Id_Trans')), 0)
/*Fin Declaracion Variables Json*/

/*Variable de Retorno del Stored*/
DECLARE @rtaErrorStored			INT
DECLARE @rtaMsjStored			VARCHAR(4000)
/*FIN Variable de Retorono del Stored*/

/*Declaracion Variables Locales*/
DECLARE @Json								VARCHAR(4000)
DECLARE @Transacciones						VARCHAR(4000)
DECLARE @Id_Cliente							INT
DECLARE @Lote								VARCHAR(100)
DECLARE @Index								INT = 1
DECLARE @Id_Trans_Fac						INT
DECLARE @Saldo_Fac							DECIMAL (20,2)
DECLARE @Items_Trans						SMALLINT
DECLARE @Items_Rec							SMALLINT
DECLARE @JSON_Parametros_Stored_Invocado	VARCHAR(8000)
DECLARE @Result_Stored_Invocado				INT
DECLARE @Aplicado							SMALLINT
DECLARE	@Existe_Valores						SMALLINT
DECLARE @Importe							DECIMAL(20,2)
DECLARE @Id_Tipo_Val						CHAR(3)
DECLARE @Id_Tarjeta							VARCHAR(10)
DECLARE @Tar_Cod_Autorizar					VARCHAR(10)
DECLARE @Tar_Lote							SMALLINT
DECLARE @Tipo_Valor_Recibo_VW				CHAR(3)
DECLARE @Id_Cta_Banco_CWeb					INT
DECLARE @Fecha								DATETIME
DECLARE @Resultado							VARCHAR(200)
DECLARE @Trans_Aplicadas					SMALLINT
DECLARE @Cbtes_Saldo						SMALLINT
DECLARE @Tipo_Operacion						VARCHAR(1)
/*Fin de Declaraciones Variables Locales */

BEGIN TRY
	
	IF (@pFlagUsarTrans <> 0 )
	BEGIN
		BEGIN TRAN
	END

	/*===================================================================================================================================================================*/	

	IF (@pModoDebug = 1)
	BEGIN
		PRINT 'Ingreso a Stored Procedure: CPJ_Cbtes_Rec_Web_Completar'
		PRINT 'Parametros:'	
		PRINT 'FlagUsarTrans: ' + CONVERT(VARCHAR(10), @pFlagUsarTrans) + ' Id_Trans: ' + CONVERT(VARCHAR(10), @pId_Trans)
	END
	
	/*===================================================================================================================================================================*/

	IF(@pId_Trans = 0)
	BEGIN
		SET @rtaErrorStored = 70001 /*Transaccion no abierta*/
		
		SELECT	 @rtaErrorStored	AS ErrorNumber 
				,0					AS ErrorSeverity  
				,0					AS ErrorState 
				,0					AS ErrorProcedure  
				,0					AS ErrorLine		
				,''					AS ErrorMessage
			
		IF (@pFlagUsarTrans <> 0)
		BEGIN
			ROLLBACK
		END
		
		RETURN 9999
	END

	/*===================================================================================================================================================================*/

	IF OBJECT_ID('tempdb..#Transacciones') IS NOT NULL
	BEGIN
		DROP TABLE #Transacciones
	END

	CREATE TABLE #Transacciones (Id_Trans	INT
								, Saldo		DECIMAL(20,2)
								, Aplicado	SMALLINT)

	IF OBJECT_ID('tempdb..#Valores') IS NOT NULL
	BEGIN
		DROP TABLE #Valores
	END

	CREATE TABLE #Valores (Id_Tipo_Val					CHAR(3)
						  , Id_Tarjeta					VARCHAR(10)
						  , Tar_Cod_Autorizar			VARCHAR(10)
						  , Tar_Lote					SMALLINT
						  , Importe_Total				DECIMAL(20,2)
						  , Importe_Total_Con_Recargo	DECIMAL(20,2))
						  
	IF OBJECT_ID('tempdb..#Json_Trans') IS NOT NULL
	BEGIN
		DROP TABLE #Json_Trans
	END

	CREATE TABLE #Json_Trans (Id						INT
							 ,ColumnA					VARCHAR(4000))

	/*===================================================================================================================================================================*/

	-- (1) Datos de la Operacion (WebProcesos)
	SET @Lote = ISNULL((SELECT Lote FROM Cbtes WHERE Id_Trans = @pId_Trans),'')
	SET @Json = ISNULL((SELECT Json FROM WebProcesos WHERE Nro_Operacion = @Lote),'')
	SET @Transacciones = ISNULL(CONVERT(VARCHAR(4000), dbo.CF_JSON_VALUE(@Json,'transacciones')), '')


	IF(@Json <> '')
	BEGIN
		
		SET @Resultado = ''

		IF(@pModoDebug = 1)
		BEGIN
			PRINT 'Datos | Json: ' + CONVERT(VARCHAR(4000), @Json) + ' Transacciones: '  + CONVERT(VARCHAR(4000), @Transacciones)
		END

		SET @Items_Trans = (LEN(@Transacciones) - LEN(REPLACE(@Transacciones, '"id_trans"', ''))) / LEN('"id_trans"')

		WHILE @Index <= @Items_Trans
		BEGIN
			
			INSERT INTO #Json_Trans (Id, ColumnA)
			SELECT Id,
				   ColumnA
			FROM dbo.CF_Split(@Transacciones, '},{') 
			WHERE Id = @Index

			UPDATE #Json_Trans
			SET ColumnA = STUFF(ColumnA, 1, 1, '')
			WHERE (CHARINDEX('{', ColumnA) = 1
				   OR CHARINDEX('}', ColumnA) = 1
				   OR CHARINDEX(',{', ColumnA) = 1)

			SELECT @Id_Trans_Fac = CONVERT(INT, dbo.CF_JSON_VALUE(ColumnA,'Id_Trans'))
				  ,@Saldo_Fac = CONVERT(DECIMAL(20,2), dbo.CF_JSON_VALUE(ColumnA,'Saldo'))
			FROM #Json_Trans WHERE Id = @Index

			/*--Version simplificada-- (JSON_VALUE funciona a partir de versiones 2016 de sql)
			SET @Id_Trans_Fac = CONVERT(INT, JSON_VALUE(@Json, '$.transacciones[' + CAST(@Index AS NVARCHAR(MAX)) + '].id_trans'))
			SET @Saldo_Fac = CONVERT(DECIMAL(20,2), JSON_VALUE(@Json, '$.transacciones[' + CAST(@Index AS NVARCHAR(MAX)) + '].saldo'))
			*/

			INSERT INTO #Transacciones (Id_Trans, Saldo) VALUES (@Id_Trans_Fac, @Saldo_Fac)

			SET @Index = @Index + 1
		END


		INSERT INTO #Valores (Id_Tipo_Val, Id_Tarjeta, Tar_Cod_Autorizar, Tar_Lote, Importe_Total, Importe_Total_Con_Recargo)
					VALUES	 (ISNULL(CONVERT(CHAR(3), dbo.CF_JSON_VALUE(@Json,'Id_Tipo_Val')), '')
							 ,ISNULL(CONVERT(VARCHAR(10), dbo.CF_JSON_VALUE(@Json,'id_tarjeta')), '')
							 ,ISNULL(CONVERT(VARCHAR(10), dbo.CF_JSON_VALUE(@Json,'tar_cod_autorizar')), '')
							 ,ISNULL(CONVERT(SMALLINT, dbo.CF_JSON_VALUE(@Json,'tar_lote')), 0)
							 ,ISNULL(CONVERT(DECIMAL(20,2), dbo.CF_JSON_VALUE(@Json,'importe_total')), 0)
							 ,ISNULL(CONVERT(DECIMAL(20,2), dbo.CF_JSON_VALUE(@Json,'importe_total_con_recargo')), 0))


		-- (2) Verificar Items del Recibo
		SET @Items_Rec = (SELECT COUNT(*) AS Items FROM Cbtes_Items WHERE Id_Trans = @pId_Trans)

			-- Si no corresponden la cantidad de items abonados al recibo generado, eliminar registros incompletos y volver a generar
		IF (@Items_Trans <> @Items_Rec)
		BEGIN

			IF(@pModoDebug = 1)
			BEGIN
				PRINT 'Se generan los items del cbte'
			END

			DELETE FROM Cbtes_Items 	WHERE Id_Trans = @pId_Trans
			DELETE FROM Clientes_CtaCte WHERE Id_Trans = @pId_Trans
			DELETE FROM Cbtes_Aplica	WHERE Id_Trans_D = @pId_Trans AND Id_Trans = 0

			SET @Index = 1
			DECLARE @Id_Trans_A_Aplicar INT
			DECLARE @Saldo				DECIMAL(20,2)
			DECLARE curFacInicializar_Agregar CURSOR FOR
			SELECT Id_Trans, Saldo FROM #Transacciones

			OPEN curFacInicializar_Agregar
			FETCH NEXT FROM curFacInicializar_Agregar INTO @Id_Trans_A_Aplicar, @Saldo
			WHILE @@FETCH_STATUS = 0
			BEGIN
				
				IF(@pModoDebug = 1)
				BEGIN
					PRINT 'Id_Trans_A_Aplicar: ' + CONVERT(VARCHAR(20),@Id_Trans_A_Aplicar)
				END
				
				-- Verifico si las transacciones ya no estan aplicadas a otro cbte, de ser asi no se pasan por el fac_fin 
				SET @Cbtes_Saldo = ISNULL((SELECT 1 FROM Cbtes 
													WHERE Id_Trans = @Id_Trans_A_Aplicar 
													AND (EXISTS (SELECT Id_Trans FROM Cbtes_Aplica 
																				WHERE Id_Trans_D = @pId_Trans 
																				AND Id_Trans = @Id_Trans_A_Aplicar)
													OR Saldo > 0)
											), 0)
											
				DELETE FROM Cbtes_Aplica WHERE Id_Trans_D = @pId_Trans AND Id_Trans = @Id_Trans_A_Aplicar
				
				IF (@Cbtes_Saldo = 1)
				BEGIN
					SET @JSON_Parametros_Stored_Invocado =	
					'{' + dbo.CF_Json_ParsearVariable('Flag_Renumerar', 0, 'TINYINT') + ',' +
						  dbo.CF_Json_ParsearVariable('Id_Trans', @Id_Trans_A_Aplicar, 'TINYINT') +
					'}'				

					EXEC @Result_Stored_Invocado = dbo.CPJ_Cbtes_Fac_Fin @JSON_Parametros_Stored_Invocado
				END
				
				SET @JSON_Parametros_Stored_Invocado =	
						'{' + dbo.CF_Json_ParsearVariable('FlagUsarTrans', 0, 'TINYINT') + ',' +
							  --dbo.CF_Json_ParsearVariable('ModoDebug', @pModoDebug, 'TINYINT') + ',' +
							  dbo.CF_Json_ParsearVariable('Id_Trans', @pId_Trans, 'TINYINT') + ',' +
							  dbo.CF_Json_ParsearVariable('Item', @Index, 'SMALLINT') + ',' +
							  dbo.CF_Json_ParsearVariable('Id_Producto', '', 'VARCHAR(20)') + ',' +
							  dbo.CF_Json_ParsearVariable('Cantidad', 1, 'DECIMAL(15,5)') + ',' +
							  dbo.CF_Json_ParsearVariable('Id_Trans_A_Cancelar', @Id_Trans_A_Aplicar, 'INT') + ',' +
							  dbo.CF_Json_ParsearVariable('Importe_A_Cancelar', @Saldo, 'DECIMAL(16,2)') + ',' +
							  dbo.CF_Json_ParsearVariable('Omitir_Resultado_OK', 1, 'TINYINT') + 
						'}'	

				EXEC @Result_Stored_Invocado = dbo.CPJ_Cbtes_Rec_Generar_Item @JSON_Parametros_Stored_Invocado

				SET @Index = @Index + 1

				SET @Aplicado =  ISNULL((SELECT 1 FROM Cbtes_Aplica WHERE Id_Trans = @Id_Trans_A_Aplicar AND Id_Trans_D = @pId_Trans),0)
				UPDATE #Transacciones SET Aplicado = @Aplicado WHERE Id_Trans = @Id_Trans_A_Aplicar

			FETCH NEXT FROM curFacInicializar_Agregar INTO @Id_Trans_A_Aplicar, @Saldo
			END
			CLOSE curFacInicializar_Agregar
			DEALLOCATE curFacInicializar_Agregar

			SET @Trans_Aplicadas = ISNULL((SELECT SUM(Aplicado) FROM #Transacciones), 0)

			IF(@Trans_Aplicadas < @Items_Trans)
			BEGIN
				SET @Resultado = 'Las transacciones asociadas estan aplicadas a otro comprobante, quedando la operación en estado parcial o pago a cuenta.'
			END

		END


		-- (3) Verificar Valores del Recibo
		SET @Existe_Valores = ISNULL((SELECT 1 FROM Cbtes C, Valores V  WHERE C.Id_Trans = V.Id_Trans AND C.Id_Trans = @pId_Trans),0) 

		IF (@Existe_Valores = 0)
		BEGIN

			IF(@pModoDebug = 1)
			BEGIN
				PRINT 'Se genera los valores del cbte'
			END

			SET @Tipo_Valor_Recibo_VW = DBO.CF_xParam('Tipo_Valor_Recibo_VW')
			SET @Id_Cta_Banco_CWeb	  = DBO.CF_xParam('Id_Cta_Baco_CWeb')

			SELECT  @Id_Tipo_Val = 
					CASE 
						WHEN ISNULL(@Id_Tarjeta, '') <> '' THEN
							'TAR'
						WHEN ISNULL((SELECT Id_Tipo_Val), '') = '' THEN
							@Tipo_Valor_Recibo_VW
						ELSE
							ISNULL((SELECT Id_Tipo_Val), '')
					END,
					@Id_Tarjeta = ISNULL((SELECT Id_Tipo_Val), ''),
					@Tar_Cod_Autorizar = ISNULL((SELECT Id_Tipo_Val), ''),
					@Tar_Lote = ISNULL((SELECT Id_Tipo_Val), 0),
					@Importe = 
					CASE 
						WHEN ISNULL((SELECT Importe_Total_Con_Recargo), 0) = 0 THEN
							ISNULL((SELECT Importe_Total FROM #Valores), 0)
						ELSE
							ISNULL((SELECT Importe_Total_Con_Recargo), 0)
					END,
					@Fecha = CONVERT(DATE, GETDATE())
			FROM #Valores

			SET @JSON_Parametros_Stored_Invocado =	
				'{' + dbo.CF_Json_ParsearVariable('Flag_Renumerar', 0, 'TINYINT') + ',' +
					  --dbo.CF_Json_ParsearVariable('ModoDebug', @pModoDebug, 'TINYINT') + ',' +
					  dbo.CF_Json_ParsearVariable('Id_Trans', @pId_Trans, 'TINYINT') + ',' +
					  dbo.CF_Json_ParsearVariable('Id_Tipo_Val', @Id_Tipo_Val, 'CHAR(3)') + ',' +
					  dbo.CF_Json_ParsearVariable('Valor_Nro', 1, 'INT') + ',' +
					  dbo.CF_Json_ParsearVariable('Fecha_Val', @Fecha, 'DATETIME') + ',' +
					  dbo.CF_Json_ParsearVariable('Fecha_Emision', @Fecha, 'DATETIME') + ',' +
					  dbo.CF_Json_ParsearVariable('Fecha_Acreditacion', @Fecha, 'DATETIME') + ',' +
					  dbo.CF_Json_ParsearVariable('Fecha_Vence', @Fecha, 'DATETIME') + ',' +
					  dbo.CF_Json_ParsearVariable('Importe', @Importe, 'DECIMAL(20,2)') + ',' +
					  dbo.CF_Json_ParsearVariable('Id_Tarjeta', @Id_Tarjeta, 'VARCHAR(25)') + ',' +
					  dbo.CF_Json_ParsearVariable('Tarjeta_Nro', '', 'VARCHAR(25)') + ',' +
					  dbo.CF_Json_ParsearVariable('Tarjeta_Vence', @Fecha, 'DATETIME') + ',' +
					  dbo.CF_Json_ParsearVariable('Tarjeta_Lote', @Tar_Lote, 'SMALLINT') + ',' +
					  dbo.CF_Json_ParsearVariable('Id_Cta_banco', @Id_Cta_Banco_CWeb, 'INT') + ',' +
					  dbo.CF_Json_ParsearVariable('RtaMsjStored', 1, 'TINYINT') + 
				'}'				

			EXEC @Result_Stored_Invocado = dbo.CPJ_Cbtes_Rec_Generar_Pago @JSON_Parametros_Stored_Invocado

		END


		-- (4) Completar Recibo
		IF(@pModoDebug = 1)
		BEGIN
			PRINT 'Se ejecuta el Rec Fin'
		END

		SET @JSON_Parametros_Stored_Invocado =	
				'{' + dbo.CF_Json_ParsearVariable('Id_Trans', @pId_Trans, 'INT') + ',' +
					  dbo.CF_Json_ParsearVariable('ModoDebug', @pModoDebug, 'TINYINT') + ',' +
					  dbo.CF_Json_ParsearVariable('Flag_Renumerar', 1, 'TINYINT') + ',' + 
					  dbo.CF_Json_ParsearVariable('Omitir_Resultado_OK', 1, 'TINYINT') + 
				'}'

		EXEC @Result_Stored_Invocado = dbo.CPJ_Cbtes_Rec_Fin @JSON_Parametros_Stored_Invocado


		-- (5) Respuesta
		IF(@Resultado = '')
		BEGIN
			SET @Resultado = 'Recibo Completado.'
		END

	END
	ELSE
	BEGIN
		SET @Tipo_Operacion = (ISNULL((SELECT LEFT(Lote, 1) FROM Cbtes WHERE Id_Trans = @pId_Trans), ''))
		IF(@Tipo_Operacion = 'N')
		BEGIN

			IF(@pModoDebug = 1)
			BEGIN
				PRINT 'Operacion de Nuevo Socio.'
			END

			SET @JSON_Parametros_Stored_Invocado =	
					'{' + dbo.CF_Json_ParsearVariable('Id_Trans', @pId_Trans, 'INT') + ',' +
					      dbo.CF_Json_ParsearVariable('ModoDebug', @pModoDebug, 'TINYINT') + ',' +
						  dbo.CF_Json_ParsearVariable('Flag_Renumerar', 1, 'TINYINT') + ',' + 
						  dbo.CF_Json_ParsearVariable('Omitir_Resultado_OK', 1, 'TINYINT') + 
					'}'

			EXEC @Result_Stored_Invocado = dbo.CPJ_Cbtes_Rec_Fin @JSON_Parametros_Stored_Invocado

			SET @Resultado = 'Recibo Completado.'
			
		END
		ELSE
		BEGIN
			SET @Resultado = 'Nro. Operacion no abierta.'
		END
	END
	/*===================================================================================================================================================================*/
	
		IF OBJECT_ID('tempdb..#Transacciones') IS NOT NULL
		BEGIN
			DROP TABLE #Transacciones
		END

		IF OBJECT_ID('tempdb..#Valores') IS NOT NULL
		BEGIN
			DROP TABLE #Valores
		END
		
		IF OBJECT_ID('tempdb..#Json_Trans') IS NOT NULL
		BEGIN
			DROP TABLE #Json_Trans
		END

	/*===================================================================================================================================================================*/

	IF (@pFlagUsarTrans <> 0)
	BEGIN
		COMMIT
	END

		SET @rtaMsjStored	=	@Resultado + ' Trans: ' + CONVERT(VARCHAR(20), @pId_Trans)

		SELECT	 0				AS ErrorNumber 
				,0				AS ErrorSeverity  
				,0				AS ErrorState 
				,0				AS ErrorProcedure  
				,0				AS ErrorLine		
				,@rtaMsjStored	AS ErrorMessage

	IF(@pModoDebug = 1)
	BEGIN
		PRINT 'Fin Stored Procedure: CPJ_Cbtes_Rec_Web_Completar'
	END

END TRY  


BEGIN CATCH  
    EXECUTE usp_GetErrorInfo

	IF (@pFlagUsarTrans <> 0 )
	BEGIN
		ROLLBACK
	END

	RETURN 9999
END CATCH

RETURN 0