-- [PRDD_SET_STATUS_IN_THIRDPARTY_DB]
-- =================================================================
-- Set status info into a text field
-- 
-- Returns: 1 = SUCCESS; 0 = FAILED 
-- =================================================================
-- Copyright (c) 2024 by Digital Data GmbH
--
-- Digital Data GmbH • Ludwig-Rinn-Strasse 16 • D-35452 Heuchelheim
-- Tel.: 0641/202360 • E-Mail: info-flow@digitaldata.works
-- =================================================================
-- Creation Date / Author:    25.11.2024 / HE,MK
-- Version Date / Editor:     25.11.2024 / HE,MK
-- Version Number:            1.0.0.0
-- =================================================================
-- History:
-- 25.11.2024 / HE,MK - First Version

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE OR ALTER PROCEDURE [PRDD_SET_STATUS_IN_THIRDPARTY_DB]( 
	@pTARGET_DB				 NVARCHAR(256)	= '[DD_CWLDATEN_DDVP]',					-- Give target DB name (without schema!)
	@pTARGET_SCHEMA			 NVARCHAR(256)	= '[dbo]',								-- Give target DB schema
	@pTARGET_TABLE			 NVARCHAR(256)	= '[t025]',								-- Give target DB table
	@pTARGET_COLUMN			 NVARCHAR(256)	= '[c069]',								-- Give target DB column
	@pTARGET_COLUMN_TYPE	 NVARCHAR(256)	= 'NVARCHAR',							-- Give target DB column type eg. INT or NVARCHAR
	@pREF_DOC_TYPE			 NVARCHAR(256)	= 'Ausgangsrechnung',					-- Give REF_DOC_TYPE, eg.: Ausgangsangebot, Ausgangsauftrag, Ausgangslieferschein, Ausgangsrechnung (ONLY FOR LOGGING PURPOSE!)
	@pREF_DOC_NR_COLUMN		 NVARCHAR(256)	= '[c055]',								-- Give the column where the REF_DOC_NR is stored (eg. WINLINE: Ausgangsangebot=[c043], Ausgangsauftrag=[c044], Ausgangslieferschein=[c045], Ausgangsrechnung=[c055])
	@pREF_DOC_NR			 NVARCHAR(256),											-- Give REF_DOC_NR, like 'ARE-20234187' or 'AANG-20241060'
	@pADDITIONAL_WHERE		 NVARCHAR(256)	= '',									-- Give an additional WHERE clause like ' AND [COLUMN] IS NOT NULL'
	@pSTATUS_VALUE			 NVARCHAR(256),											-- Give text to write in @pTARGET_COLUMN
	@pSTATUS_VALUE_MAX_LEN	 INT			= 60,									-- Give the max lenght of the target field for the status text
	@pSTATUS_VALUE_DELIMITER NVARCHAR(3)	= '; ',									-- Give the separator to concat multiple status values, like "Text1; Text2; ..."
	@pUSERNAME				 NVARCHAR(50)	= '[PRDD_SET_STATUS_IN_THIRDPARTY_DB]',	-- Give the user of the Frontend call, for the logging
	@pOVERWRITE				 BIT			= 0,									-- Set overwrite for @pTARGET_COLUMN: 0 = concat; 1 = overwrite
	@pLOGLEVEL				 NVARCHAR(25)	= 'ERROR'								-- Set Loglevel (ERROR or INFO) to be written to table [TBDD_SET_STATUS_IN_THIRDPARTY_DB_LOG]
)
AS
BEGIN TRY

	SET NOCOUNT ON;

	-- decalare new vars because of parameter sniffing
	DECLARE		@TARGET_DB				NVARCHAR(256)	= @pTARGET_DB,
				@TARGET_SCHEMA			NVARCHAR(256)	= @pTARGET_SCHEMA,
				@TARGET_TABLE			NVARCHAR(256)	= @pTARGET_TABLE,
				@TARGET_COLUMN			NVARCHAR(256)	= @pTARGET_COLUMN,
				@TARGET_COLUMN_TYPE		NVARCHAR(256)	= @pTARGET_COLUMN_TYPE,
				@REF_DOC_TYPE			NVARCHAR(256)	= @pREF_DOC_TYPE,
				@REF_DOC_NR_COLUMN		NVARCHAR(256)	= @pREF_DOC_NR_COLUMN,
				@REF_DOC_NR				NVARCHAR(256)	= @pREF_DOC_NR,
				@ADDITIONAL_WHERE		NVARCHAR(256)	= @pADDITIONAL_WHERE,
				@STATUS_VALUE			NVARCHAR(256)	= @pSTATUS_VALUE,
				@STATUS_VALUE_MAX_LEN	INT				= @pSTATUS_VALUE_MAX_LEN,
				@STATUS_VALUE_DELIMITER	NVARCHAR(3)		= @pSTATUS_VALUE_DELIMITER,
				@USERNAME				NVARCHAR(50)	= @pUSERNAME,
				@OVERWRITE				BIT				= @pOVERWRITE,
				@LOGLEVEL				NVARCHAR(25)	= @pLOGLEVEL;

	-- decalare runtime vars
	DECLARE		@LINE_COUNT_1			INT				= 0,
				@LINE_COUNT_2			INT				= 0,
				@SQL_COMMAND			NVARCHAR(2000)	= NULL,
				@STATUS_VALUE_OLD		NVARCHAR(256)	= NULL,
				@RETURN_STATUS			NVARCHAR(256)	= NULL,
				@RETURN_STATUS_VALUE	NVARCHAR(256)	= NULL;

	PRINT '==============================='
	PRINT 'PROCEDURE   - START [PRDD_SET_STATUS_IN_THIRDPARTY_DB] @ ' + CONVERT(NVARCHAR(50),GETDATE(),120);
	PRINT 'PARAMETER01 - @TARGET_DB:			 ' + CONVERT(NVARCHAR(256),@TARGET_DB);
	PRINT 'PARAMETER02 - @TARGET_SCHEMA:		 ' + CONVERT(NVARCHAR(256),@TARGET_SCHEMA);
	PRINT 'PARAMETER03 - @TARGET_TABLE:			 ' + CONVERT(NVARCHAR(256),@TARGET_TABLE);
	PRINT 'PARAMETER04 - @TARGET_COLUMN:		 ' + CONVERT(NVARCHAR(256),@TARGET_COLUMN);
	PRINT 'PARAMETER05 - @TARGET_COLUMN_TYPE:	 ' + CONVERT(NVARCHAR(256),@TARGET_COLUMN_TYPE);
	PRINT 'PARAMETER06 - @REF_DOC_TYPE:			 ' + CONVERT(NVARCHAR(256),@REF_DOC_TYPE);
	PRINT 'PARAMETER07 - @REF_DOC_NR_COLUMN		 ' + CONVERT(NVARCHAR(256),@REF_DOC_NR_COLUMN);
	PRINT 'PARAMETER08 - @REF_DOC_NR:			 ' + CONVERT(NVARCHAR(256),@REF_DOC_NR);
	PRINT 'PARAMETER09 - @ADDITIONAL_WHERE		 ' + CONVERT(NVARCHAR(256),@ADDITIONAL_WHERE);
	PRINT 'PARAMETER10 - @STATUS_VALUE:			 ' + CONVERT(NVARCHAR(256),@STATUS_VALUE);
	PRINT 'PARAMETER11 - @STATUS_VALUE_MAX_LEN	 ' + CONVERT(NVARCHAR(256),@STATUS_VALUE_MAX_LEN);
	PRINT 'PARAMETER12 - @STATUS_VALUE_DELIMITER ' + CONVERT(NVARCHAR(3),@STATUS_VALUE_DELIMITER);
	PRINT 'PARAMETER13 - @USERNAME:				 ' + CONVERT(NVARCHAR(50),@USERNAME);
	PRINT 'PARAMETER14 - @OVERWRITE:			 ' + CONVERT(NVARCHAR(1),@OVERWRITE);
	PRINT 'PARAMETER15 - @LOGLEVEL:				 ' + CONVERT(NVARCHAR(25),@LOGLEVEL);

	--==============================================-- Prepare the log table --================================================--
	IF (@LOGLEVEL is not NULL) BEGIN
		PRINT '';
		IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'TBDD_SET_STATUS_IN_THIRDPARTY_DB_LOG') BEGIN
				PRINT 'Log table already exists';

		END; ELSE BEGIN
			PRINT 'Log table does not exists, trying to create...';

			CREATE TABLE [dbo].[TBDD_SET_STATUS_IN_THIRDPARTY_DB_LOG](
				[GUID] [bigint] IDENTITY(1,1) NOT NULL,
				[LOG_LEVEL] [NVARCHAR](25) NOT NULL,
				[MESSAGE1] [NVARCHAR](max) NOT NULL,
				[MESSAGE2] [NVARCHAR](max) NULL,
				[MESSAGE3] [NVARCHAR](max) NULL,
				[MESSAGE4] [NVARCHAR](max) NULL,
				[MESSAGE5] [NVARCHAR](max) NULL,
				[COMMENT] [NVARCHAR](max) NULL,
				[ADDED_WHO] [NVARCHAR](50) NOT NULL,
				[ADDED_WHEN] [datetime] NOT NULL,
				CONSTRAINT [PK_TBDD_SET_STATUS_IN_THIRDPARTY_DB_LOG] PRIMARY KEY CLUSTERED 
			(
				[GUID] ASC
			)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
			) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];

			ALTER TABLE [dbo].[TBDD_SET_STATUS_IN_THIRDPARTY_DB_LOG] ADD  CONSTRAINT [DF_TBDD_SET_STATUS_IN_THIRDPARTY_DB_LOG_ADDED_WHO]  DEFAULT ('DEFAULT') FOR [ADDED_WHO];

			ALTER TABLE [dbo].[TBDD_SET_STATUS_IN_THIRDPARTY_DB_LOG] ADD  CONSTRAINT [DF_TBDD_SET_STATUS_IN_THIRDPARTY_DB_LOG_ADDED_WHEN]  DEFAULT (getdate()) FOR [ADDED_WHEN];
		END;
	END;
	-----------------------------------------------------------------------------------------------------------------------------

	--=====================================================-- main part --=====================================================--
	PRINT 'Check if there is a valid target...'
	SET @SQL_COMMAND = N'SELECT @LINE_COUNT_1 = count(*) 
						FROM ' + @TARGET_DB + '.' + @TARGET_SCHEMA + '.' + @TARGET_TABLE + ' 
						WHERE ' + @REF_DOC_NR_COLUMN + ' = ''' + @REF_DOC_NR + ''' ' + @ADDITIONAL_WHERE;
	PRINT 'Build Query @SQL_COMMAND: ' + @SQL_COMMAND;
	EXEC @RETURN_STATUS = sp_executesql @SQL_COMMAND,N'@LINE_COUNT_1 INT OUTPUT', @LINE_COUNT_1 OUTPUT;

	IF (@RETURN_STATUS = 0) BEGIN
		IF (@LINE_COUNT_1 > 0) BEGIN
			PRINT 'Found: ' + CONVERT(NVARCHAR(25),@LINE_COUNT_1) + ' docs to update!';
			PRINT 'Checking if docs have a existing "status text"';
			SET @SQL_COMMAND = N'SELECT @LINE_COUNT_2 = count(*) 
								FROM '  + @TARGET_DB + '.' + @TARGET_SCHEMA + '.' + @TARGET_TABLE + ' 
								WHERE ' + @REF_DOC_NR_COLUMN  + ' = ''' + @REF_DOC_NR + ''' 
								AND ('  + @TARGET_COLUMN + ' =     ''' + @STATUS_VALUE  + '''
							 	 OR '   + @TARGET_COLUMN + ' LIKE ''%' + @STATUS_VALUE  + ''' 
								 OR '   + @TARGET_COLUMN + ' LIKE  ''' + @STATUS_VALUE  + '%''
								 OR '   + @TARGET_COLUMN + ' LIKE ''%' + @STATUS_VALUE  + '%'') '
								 		+ @ADDITIONAL_WHERE;
			PRINT 'Build "status text" Query @SQL_COMMAND: ' + @SQL_COMMAND;
			EXEC @RETURN_STATUS = sp_executesql @SQL_COMMAND,N'@LINE_COUNT_2 INT OUTPUT', @LINE_COUNT_2 OUTPUT;

			IF (@RETURN_STATUS = 0) BEGIN
				PRINT 'Query was successful!';
				PRINT 'Found: ' + CONVERT(NVARCHAR(25),@LINE_COUNT_2) + ' docs with the status text duplicate!';

				IF (@LINE_COUNT_2 > 0) BEGIN
					PRINT 'Given status text is already there, no update needed!';
					SET @RETURN_STATUS = 1;

				END; ELSE BEGIN
					PRINT 'Given status text is not there, update needed!';

					IF ((@OVERWRITE = 0) and (@LINE_COUNT_1 > 0)) BEGIN
						SET @SQL_COMMAND = N'SELECT TOP 1 @STATUS_VALUE_OLD = ' +  @TARGET_COLUMN + '
											FROM '  + @TARGET_DB + '.' + @TARGET_SCHEMA + '.' + @TARGET_TABLE + ' 
											WHERE ' + @REF_DOC_NR_COLUMN  + ' = ''' + @REF_DOC_NR + ''' 
											AND '   + @TARGET_COLUMN + ' IS NOT NULL 
											AND '   + @TARGET_COLUMN + ' <>  '''' '
													+ @ADDITIONAL_WHERE;
						PRINT 'Build "status text" Query for existing values @SQL_COMMAND: ' + @SQL_COMMAND;
						PRINT 'Found: ' + CONVERT(NVARCHAR(25),@LINE_COUNT_1) + ' docs with a existing status text!';
						EXEC @RETURN_STATUS = sp_executesql @SQL_COMMAND,N'@STATUS_VALUE_OLD NVARCHAR(256) OUTPUT', @STATUS_VALUE_OLD OUTPUT;

						IF ((@RETURN_STATUS = 0)) BEGIN
							PRINT 'Query was successful!';

							IF (@STATUS_VALUE_OLD <> @STATUS_VALUE) BEGIN
								PRINT 'Extending status text';
								SET @STATUS_VALUE = concat(@STATUS_VALUE_OLD,@STATUS_VALUE_DELIMITER,@STATUS_VALUE);
							END;

							SET @STATUS_VALUE = CASE WHEN LEFT(@STATUS_VALUE,2) = @STATUS_VALUE_DELIMITER
													THEN SUBSTRING(@STATUS_VALUE, 2, LEN(@STATUS_VALUE)-2) 
													ELSE @STATUS_VALUE
												END;
						END; ELSE BEGIN
							PRINT 'Query has failed!';
							PRINT '@STATUS_VALUE keeps untouched!';
						END;

					END; ELSE IF ((@OVERWRITE = 1) or (@LINE_COUNT_1 = 0)) BEGIN
						PRINT 'Overwrite is enabled and or no doc with existing status text found!';
					END;

					--===========================================-- data collected updating row --=============================================--
					
					-- Check STATUS TEXT for its lenght
					SET @STATUS_VALUE = LTRIM(RTRIM(convert(NVARCHAR(MAX),@STATUS_VALUE)))
					IF (LEN(@STATUS_VALUE) > @STATUS_VALUE_MAX_LEN)
						SET @STATUS_VALUE = LEFT(@STATUS_VALUE,@STATUS_VALUE_MAX_LEN)

					SET @SQL_COMMAND = N'SET NOCOUNT ON; UPDATE ' + @TARGET_DB + '.' + @TARGET_SCHEMA + '.' + @TARGET_TABLE + ' ';

					IF (@TARGET_COLUMN_TYPE LIKE 'INT%') BEGIN
						SET @STATUS_VALUE = convert(INT,@STATUS_VALUE);
						SET @SQL_COMMAND += ' SET ' + @TARGET_COLUMN + ' = ' + @STATUS_VALUE;
					END; ELSE BEGIN
						-- NVARCHAR is always the failsafe
						SET @SQL_COMMAND += ' SET ' + @TARGET_COLUMN + ' = ''' + @STATUS_VALUE  + '''';
					END;

					SET @SQL_COMMAND +=	' WHERE '  + @REF_DOC_NR_COLUMN + ' = ''' + @REF_DOC_NR + ''' ' + @ADDITIONAL_WHERE;
					PRINT 'Build Update @SQL_COMMAND: ' + @SQL_COMMAND;
					EXEC @RETURN_STATUS = sp_executesql @SQL_COMMAND;

					IF (@RETURN_STATUS = 0) BEGIN
						PRINT 'Update was successful!';
						SET @RETURN_STATUS = 1;
					END; ELSE BEGIN
						PRINT 'Update has failed!';
						PRINT '@RETURN_STATUS: ' + CONVERT(NVARCHAR(50),@RETURN_STATUS);
					END;
					-----------------------------------------------------------------------------------------------------------------------------
				END;

			END; ELSE BEGIN
				PRINT 'Query has failed!';
				SET @RETURN_STATUS = 0;
			END;
						
		END; ELSE BEGIN
			PRINT 'No doc were found!';
			SET @RETURN_STATUS = 0;
		END;

	END; ELSE BEGIN
		PRINT 'Error at dynamic SQL!';
		SET @RETURN_STATUS = 0;
	END;
	-----------------------------------------------------------------------------------------------------------------------------

	IF (@RETURN_STATUS = 1) BEGIN
		SET @RETURN_STATUS_VALUE = 'SUCCESS';
	END; ELSE BEGIN
		SET @RETURN_STATUS_VALUE = 'FAILED';
	END;

	PRINT '';
	PRINT 'PROCEDURE Result: ' + @RETURN_STATUS_VALUE;
	PRINT 'PROCEDURE - END [PRDD_SET_STATUS_IN_THIRDPARTY_DB] @ ' + CONVERT(NVARCHAR(50),GETDATE(),120);
	PRINT '===============================';

	--==============================================-- Log result to table --===============================================--

	IF (@LOGLEVEL in ('INFO','WARN')) BEGIN
		IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'TBDD_SET_STATUS_IN_THIRDPARTY_DB_LOG') BEGIN
			INSERT INTO [TBDD_SET_STATUS_IN_THIRDPARTY_DB_LOG]([LOG_LEVEL],[MESSAGE1],																	[MESSAGE2],							[MESSAGE3],		[MESSAGE4],											[MESSAGE5],				[ADDED_WHO],		[ADDED_WHEN])
													VALUES (@LOGLEVEL,  concat(@TARGET_DB,'.',@TARGET_SCHEMA,'.',@TARGET_TABLE,'.',@TARGET_COLUMN),   concat(@REF_DOC_TYPE,' - Nr: ',@REF_DOC_NR),	@STATUS_VALUE,		'OVERWRITE = ' + convert(varchar,@OVERWRITE),		@RETURN_STATUS_VALUE,	@USERNAME			,GetDate());
		END;
	END;
	-----------------------------------------------------------------------------------------------------------------------------

	RETURN @RETURN_STATUS; -- Because of the target index in windream!

END TRY
BEGIN CATCH

	-- Handle any errors that occur during the process.
	SELECT ERROR_MESSAGE() AS ErrorMessage;

	--================================================-- Log error to table --=================================================--
	IF (@LOGLEVEL in ('INFO','WARN','ERROR')) BEGIN
		IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'TBDD_SET_STATUS_IN_THIRDPARTY_DB_LOG') BEGIN
			INSERT INTO [TBDD_SET_STATUS_IN_THIRDPARTY_DB_LOG]([LOG_LEVEL],[MESSAGE1],																	[MESSAGE2],							[MESSAGE3],		[MESSAGE4],											[MESSAGE5],			[ADDED_WHO],		[ADDED_WHEN])
													VALUES ('ERROR',     concat(@TARGET_DB,'.',@TARGET_SCHEMA,'.',@TARGET_TABLE,'.',@TARGET_COLUMN),  concat(@REF_DOC_TYPE,' - Nr: ',@REF_DOC_NR),	@STATUS_VALUE,		'OVERWRITE = ' + convert(NVARCHAR,@OVERWRITE),		ERROR_MESSAGE(),	@USERNAME,			GetDate());
		END;
	END;
	-----------------------------------------------------------------------------------------------------------------------------
		
	PRINT '';
	PRINT 'PROCEDURE Result: ERROR! ' + ERROR_MESSAGE();
	PRINT 'PROCEDURE - END [PRDD_SET_STATUS_IN_THIRDPARTY_DB] @ ' + CONVERT(varchar(50),GETDATE(),120);
	PRINT '===============================';

	RETURN 0; -- Because of the target index in windream!

END CATCH;
GO