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

CREATE PROCEDURE [dbo].[CPJ_Cbtes_OC_Aprobar] @pJson VARCHAR(8000)
AS 
SET NOCOUNT ON

	/***************************************************************************************************

	Procedimiento:      dbo.CPJ_Cbtes_OC_Aprobar
	Fecha de Creacion:  2023-08-01
	Creado por:         Florencia Gonzalez
	Descripcion:        Procedimiento encargado de la generacion de pedido de aprobacion para Ordenes de Compras
	Llamado por:        sys/modules/purchases/orden_compra_ficha.php (ERP WEB)
	Tablas afectadas:   [Cbtes_Aprobaciones]
	Usado en:           Ficha de Inscripcion/Orden de Compra
	Parametro(s):       FlagUsarTrans, ModoDebug, Id_Trans, Omitir_Resultado_OK
	Forma de uso:       EXEC dbo.CPJ_Cbtes_OC_Aprobar '{"FlagUsarTrans":"0","ModoDebug":"1","Id_Trans":"154245","Omitir_Resultado_OK":"1"}'
	Notas adicionales:  

	- Para utilizar este procedimiento, los usuarios responsables de la aprobacion deben tener un mail seteado en el campo Mail de la tabla 
	  Seg_User del registro que corresponda a su codigo de usuarios

	- El campo Cuerpo_Texto del registro de la tabla Tab_Emails_Tipos que corresponda con el codigo del parametro Tipo_Mail_OC debe estar 
	  seteado con un valor diferente de vacio para que se envie el mail de notificacion

	****************************************************************************************************

	SUMARIO DE CAMBIOS
	FECHA(yyyy-mm-dd)    Autor              Comentarios
	------------------- ------------------- ------------------------------------------------------------
	20240412			Adriana Laverde		Se cambia el formato de respuesta
              
	***************************************************************************************************/

	-------------------------------------------- VARIABLES RECIBIDAS POR PARAM --------------------------------------------
	
	DECLARE @pFlagUsarTrans			TINYINT
	DECLARE @pModoDebug				TINYINT
	DECLARE @pId_Trans				INT
	DECLARE @pOmitir_Resultado_OK	TINYINT
	
	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)
	SET @pOmitir_Resultado_OK		= ISNULL(CONVERT(TINYINT, dbo.CF_JSON_VALUE(@pJson,'Omitir_Resultado_OK')), 0)
	
	-------------------------------------------- FIN VARIABLES RECIBIDAS POR PARAM --------------------------------------------

	-------------------------------------------- VARIABLES LOCALES --------------------------------------------
	DECLARE @Id_Tipo_Cbte						CHAR(5)
	DECLARE @Flag_Utiliza_Aprob					TINYINT
	DECLARE @Utiliza_Rango_Cbte					INT
	DECLARE @Rango_Aprob						INT
	DECLARE @Cant_Minima_Aprob					TINYINT
	DECLARE @Importe_Cbte						DECIMAL(20,5)
	DECLARE @Cant_Minima_Usuarios				SMALLINT
	DECLARE @Usuarios							TABLE (Id_Usuario INT, Email_Notif VARCHAR(100), Orden SMALLINT NOT NULL IDENTITY(1,1))
	DECLARE @Cant_Notificaciones				INT
	DECLARE @Cant_Usuarios_Notif				SMALLINT
	DECLARE @Estado_Inicio_Tipo_Cbte			SMALLINT
	DECLARE @Fecha_Cbte							DATE
	DECLARE @Proveedor_Cbte						VARCHAR(250)
	DECLARE @JSON_Respuesta						VARCHAR(8000)
	DECLARE @JSON_Parametros_Stored_Invocado	VARCHAR(8000)
	DECLARE @rtaMsjStored						VARCHAR(250)
	DECLARE @Estado								TINYINT
	DECLARE @UltError							INT
	DECLARE @Tipo_Mail_OC						INT
	DECLARE @Asunto_Mail						VARCHAR(100)
	DECLARE @Cuerpo_Mail						VARCHAR(8000)
	DECLARE @Cuerpo_Mail_Adicional				VARCHAR(8000)
	DECLARE @Email_Usuario						VARCHAR(100)
	
	SET @Id_Tipo_Cbte							= ISNULL((SELECT Id_Tipo_Cbte FROM Cbtes WHERE Id_Trans = @pId_Trans),'')
	SET @Estado_Inicio_Tipo_Cbte				= ISNULL((SELECT Id_Estado_Cbte FROM Cbtes_Tipos WHERE Id_Tipo_Cbte = @Id_Tipo_Cbte),0)
	SET @Flag_Utiliza_Aprob						= ISNULL((SELECT Flag_Utiliza_Aprob FROM Cbtes_Tipos WHERE Id_Tipo_Cbte = @Id_Tipo_Cbte),0)
	SET @Importe_Cbte							= ISNULL((SELECT Imp_Total FROM Cbtes WHERE Id_Trans = @pId_Trans),0)
	SET @Utiliza_Rango_Cbte						= ISNULL((SELECT TOP 1 1 FROM Cbtes_Tipos_Aprob_Rangos WHERE Id_Tipo_Cbte = @Id_Tipo_Cbte),0)
	SET @Rango_Aprob							= ISNULL((SELECT Id_Aprob_Rango FROM Cbtes_Tipos_Aprob_Rangos WHERE Id_Tipo_Cbte = @Id_Tipo_Cbte AND @Importe_Cbte >= Imp_Desde AND @Importe_Cbte <= Imp_Hasta),0)
	SET @Cant_Minima_Aprob						= ISNULL((SELECT Cant_Aprob_Min FROM Cbtes_Tipos_Aprob_Rangos WHERE Id_Aprob_Rango = @Rango_Aprob),0)
	SET @Cant_Minima_Usuarios					= ISNULL((SELECT COUNT(*) FROM Cbtes_Tipos_Aprob_Usuarios WHERE Id_Aprob_Rango = @Rango_Aprob),0)
	SET @Fecha_Cbte								= ISNULL((SELECT Fecha FROM Cbtes WHERE Id_Trans = @pId_Trans),GETDATE())
	SET @Tipo_Mail_OC							= ISNULL(dbo.CF_xParam('Tipo_Mail_OC'),0)
	SET @Asunto_Mail							= ISNULL((SELECT Asunto FROM Tab_Emails_Tipos WHERE Id_Tipo_Email = @Tipo_Mail_OC),'')
	SET @Cuerpo_Mail							= ISNULL((SELECT Cuerpo_Texto FROM Tab_Emails_Tipos WHERE Id_Tipo_Email = @Tipo_Mail_OC),'')
	SET @Proveedor_Cbte							= ISNULL((SELECT Razon_Social FROM Proveedores P, Cbtes C WHERE P.Id_Proveedor = C.Id_Proveedor AND C.Id_Trans = @pId_Trans),'')
	
	-------------------------------------------- FIN VARIABLES LOCALES --------------------------------------------

	-------------------------------------------- VALIDACIONES GENERALES --------------------------------------------
IF(@Flag_Utiliza_Aprob = 1)
BEGIN
	IF(@Utiliza_Rango_Cbte = 0)
	BEGIN
		SET @rtaMsjStored = 'El comprobante requiere de aprobacion por importe, debe configurar un rango de importe para el tipo de comprobante.'

		IF(@pOmitir_Resultado_OK = 0)
		BEGIN
			SELECT	 1				AS ErrorNumber 
					,0				AS ErrorSeverity  
					,0				AS ErrorState 
					,0				AS ErrorProcedure  
					,0				AS ErrorLine		
					,@rtaMsjStored	AS ErrorMessage
			RETURN 0
		END
	END

	IF(@Rango_Aprob = 0)
	BEGIN
		SET @rtaMsjStored = 'El importe del comprobante es $' + CONVERT(VARCHAR(25),@Importe_Cbte) +'. Debe configurar un rango de aprobacion para el importe del comprobante'

		IF(@pOmitir_Resultado_OK = 0)
		BEGIN
			SELECT	 1				AS ErrorNumber 
					,0				AS ErrorSeverity  
					,0				AS ErrorState 
					,0				AS ErrorProcedure  
					,0				AS ErrorLine		
					,@rtaMsjStored	AS ErrorMessage
			RETURN 0
		END
	END

	IF(@Cant_Minima_Usuarios < @Cant_Minima_Aprob)
	BEGIN
		SET @rtaMsjStored = 'La cantidad minima de aprobaciones requeridas para este comprobante es de ' + CONVERT(VARCHAR(10),@Cant_Minima_Aprob) + ' y actualmente solo se han configurado ' + CONVERT(VARCHAR(10),@Cant_Minima_Usuarios) + ' usuarios de aprobacion'

		IF(@pOmitir_Resultado_OK = 0)
		BEGIN
			SELECT	 1				AS ErrorNumber 
					,0				AS ErrorSeverity  
					,0				AS ErrorState 
					,0				AS ErrorProcedure  
					,0				AS ErrorLine		
					,@rtaMsjStored	AS ErrorMessage
			RETURN 0
		END
	END

	-------------------------------------------- FIN VALIDACIONES GENERALES --------------------------------------------

	-------------------------------------------- INSERTO VALORES EN TABLA Y REALIZO ENVIO DE NOTIFICACION --------------------------------------------

	BEGIN TRY
		IF(@pModoDebug = 1)
		BEGIN
			PRINT 'Ingreso a Stored Procedure: CPJ_Cbtes_Aprobar_OC'
			PRINT 'Parametros:'
			PRINT 'pFlagUsarTrans' + CONVERT(VARCHAR(5), @pFlagUsarTrans) + '. pModoDebug: ' + CONVERT(VARCHAR(5), @pModoDebug) + '. pId_Trans: ' + CONVERT(VARCHAR(10), @pId_Trans)
		END

		IF @pFlagUsarTrans <> 0 
		BEGIN
			BEGIN TRAN
		END

		IF(@pModoDebug = 1)
		BEGIN
			PRINT 'Genero Tabla Usuarios con Email para Notif'
		END

		SET @Asunto_Mail = 'Orden de Compra "Pendiente de aprobación": '  + dbo.CF_Descrip_Cbte(@pId_Trans) 

		SET @Cuerpo_Mail = @Cuerpo_Mail + '<br/>'
		SET @Cuerpo_Mail_Adicional = 'Transaccion: ' + CONVERT(VARCHAR(25), @pId_Trans)  + '<br/>'
		SET @Cuerpo_Mail_Adicional = @Cuerpo_Mail_Adicional + 'Comprobante: ' + dbo.CF_Descrip_Cbte(@pId_Trans)  + '<br/>'
		SET @Cuerpo_Mail_Adicional = @Cuerpo_Mail_Adicional + 'Fecha de Comprobante: ' + CONVERT(VARCHAR(25), @Fecha_Cbte, 103)  + '<br/>'
		SET @Cuerpo_Mail_Adicional = @Cuerpo_Mail_Adicional + 'Proveedor: ' + @Proveedor_Cbte + '<br/>'

		INSERT INTO @Usuarios SELECT S.No, S.Email
									 FROM Cbtes_Tipos_Aprob_Usuarios U, Seg_User S 
									 WHERE U.Id_Usuario = S.No AND
									 U.Id_Aprob_Rango = @Rango_Aprob AND
									 NOT EXISTS (SELECT D.Id_Usuario 
								 						FROM Cbtes_Tipos_Aprob_Usu_Del D, Cbtes_Tipos_Aprob_Usuarios U2 
								 						WHERE D.Id_Aprob_Usuario = U2.Id_Aprob_Usuario AND
								 							U2.Id_Aprob_Usuario = U.Id_Aprob_Usuario AND
								 							U2.Id_Aprob_Rango = @Rango_Aprob AND 
								 							D.Activo = 1 AND 
								 							CONVERT(VARCHAR(25),D.Fecha_Desde,103) <= CONVERT(VARCHAR(25),GETDATE(),103) AND 
								 							(CONVERT(VARCHAR(25),D.Fecha_Hasta,103) >= CONVERT(VARCHAR(25),GETDATE(),103)))

		INSERT INTO @Usuarios SELECT S.No, S.Email
								  FROM Cbtes_Tipos_Aprob_Usu_Del D, Cbtes_Tipos_Aprob_Usuarios U, Seg_User S
								  WHERE D.Id_Aprob_Usuario = U.Id_Aprob_Usuario AND
										D.Id_Usuario = S.No AND
										U.Id_Aprob_Rango = @Rango_Aprob AND 
										D.Activo = 1 AND 
										CONVERT(VARCHAR(25),D.Fecha_Desde,103) <= CONVERT(VARCHAR(25),GETDATE(),103) AND 
										(CONVERT(VARCHAR(25),D.Fecha_Hasta,103) >= CONVERT(VARCHAR(25),GETDATE(),103))

		SET @Cant_Usuarios_Notif = (SELECT COUNT(*) FROM @Usuarios)
		SET @Cant_Notificaciones = 1

		WHILE(@Cant_Notificaciones <= @Cant_Usuarios_Notif)
		BEGIN
			IF(@pModoDebug = 1)
			BEGIN
				PRINT 'Inserto en Tabla Cbtes_Aprobaciones'
			END

			INSERT INTO Cbtes_Aprobaciones (Id_Trans, Id_Aprob_Rango, Id_Usuario, Fecha, Fecha_Proc, Estado, Nota)
									VALUES (@pId_Trans, @Rango_Aprob, (SELECT Id_Usuario FROM @Usuarios WHERE Orden = @Cant_Notificaciones), GETDATE(), NULL, 'P', NULL)

			SET @Email_Usuario = (SELECT TOP 1 Email_Notif FROM @Usuarios WHERE Orden = @Cant_Notificaciones)

			IF(@Email_Usuario <> '' AND @Cuerpo_Mail <> '')
			BEGIN


				SET @JSON_Parametros_Stored_Invocado =	'{' +
															dbo.CF_Json_ParsearVariable('from', '', 'VARCHAR(100)') + ',' +
															dbo.CF_Json_ParsearVariable('to', @Email_Usuario, 'VARCHAR(100)') + ',' +
															dbo.CF_Json_ParsearVariable('subject', @Asunto_Mail, 'VARCHAR(100)') + ',' +
															dbo.CF_Json_ParsearVariable('body', @Cuerpo_Mail + @Cuerpo_Mail_Adicional, 'VARCHAR(8000)') + ',' +
															dbo.CF_Json_ParsearVariable('Omitir_Resultado_OK', 1, 'TINYINT') +
														'}'

				IF(@pModoDebug = 1)
				BEGIN
					PRINT 'Genero Notificacion por Email'
				END

				EXEC @UltError = dbo.CPJ_Mail_Enviar_CleverAPI @JSON_Parametros_Stored_Invocado

				IF(@UltError <> 0)
				BEGIN
					SET @rtaMsjStored = 'Ocurrio un error al enviar las notificaciones'

					IF(@pOmitir_Resultado_OK = 0)
					BEGIN
						SELECT	 1				AS ErrorNumber 
								,0				AS ErrorSeverity  
								,0				AS ErrorState 
								,0				AS ErrorProcedure  
								,0				AS ErrorLine		
								,@rtaMsjStored	AS ErrorMessage
						RETURN 0
					END

					RAISERROR ('Error when calling CPJ_Mail_Enviar_CleverAPI', 16, 1) 
				END
			END

			SET @Cant_Notificaciones += 1
		END

		-------------------------------------------- FIN INSERTO VALORES EN TABLA Y REALIZO ENVIO DE NOTIFICACION --------------------------------------------

		IF @pFlagUsarTrans <> 0
		BEGIN
			COMMIT
		END

		IF @pOmitir_Resultado_OK = 0 AND @rtaMsjStored IS NOT NULL
		BEGIN
			SELECT	 0				AS ErrorNumber 
					,0				AS ErrorSeverity  
					,0				AS ErrorState 
					,0				AS ErrorProcedure  
					,0				AS ErrorLine		
					,@rtaMsjStored	AS ErrorMessage
		END

		IF(@pModoDebug = 1)
		BEGIN
			PRINT 'Fin Stored Procedure: CPJ_Cbtes_Aprobar_OC'
		END
		
	END TRY
	BEGIN CATCH

		IF EXISTS (SELECT @rtaMsjStored)
		BEGIN
			SELECT @JSON_Respuesta
		END
		ELSE
		BEGIN
			EXECUTE usp_GetErrorInfo
		END

		IF @pFlagUsarTrans <> 0 AND @@TRANCOUNT > 0
		BEGIN
			ROLLBACK
		END

		RETURN 9999

	END CATCH
END