282 lines
14 KiB
Transact-SQL
282 lines
14 KiB
Transact-SQL
-- [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
|