DROP PROCEDURE IF EXISTS dbo.sx_pf_POST_ProductDataTableValues;
GO

/*
	POST Operation for all values of the ProductDataTable
	The method can be switched between incremental POST and full POST

	Gerd Tautenhahn for Saxess Software GmbH
	Last modified: 12/2022 for OCT 5.8

	Concept:
	- all Values are deliverd as Textarray - this requires a big split operation
	- the Text array must also deliver deleted values if it does a incrementel transmisson (deleted Values are delivered with empty ValueInt, ValueText, ValueFormula)
	- Values may be sended with '<#NV>' to keep existing values

	@ValuesInBracketsCommaSeparated format is: (ValueSeriesID,TimeID,Formula,Value,ValueText,ValueComment),(ValueSeriesIDTimeID,Formula,Value,ValueText,ValueComment)
	The whole string is single quoted and every parameter inside except the TimeID is single quoted
	if you check this in SQL Server Profiler or Table sx_pf_APILog, every String must have two inverted commas, the whole Parameter must have Single inverted comma
	this must show profiler: '[(''ValueSeriesID'',TimeID,''Formula'',''ValueInt'',''ValueText'',''ValueComment'')],[(''ValueSeriesID'',TimeID,''Formula'',''ValueInt'',''ValueText'',''ValueComment'')]'

	Informal: 
	In further releases the )],[( delimiter was planend be replaced with CHAR(29) - which is no longer likely, as we will use JSON
	But CHAR(29) is at the moment used at internal temporary delimiter

	Test call
	1. Empty FactoryID / ProductLineID / ProductID / ValueSeriesID / TimeID			=> 404 Not Found
	2. TransactionUsername not determined for User									=> 403 Forbidden
	3. Username does not have write access to ProductLine							=> 401 Unauthorized
	4. Non exists Factory / ProductLine / Product keys								=> 404 Not Found
	5. If ValueSeriesID not exists in planning.tdValueSeries for this ProductKey	=> 403 Forbidden
	6. Product updated																=> 200 OK

	Test case 1 - Incremental Sending ***************************************************************
	1. One Value for an existing Value must replace them
	2. One Value for an not exiting Value must create it
	3. If all Parameter are posted empty, the value is deleted
	4. If one or more of the parameter are sended with <#NV> there Value is replaced with the existing value (or nothing if it exists not)
	5. An ZeroEmpty Value in old or sended values is deleted

	DECLARE @RC							INT
	DECLARE @Username					NVARCHAR (255) = 'SQL'
	DECLARE @ProductID					NVARCHAR (255) = '1'
	DECLARE @ProductLineID				NVARCHAR (255) = 'U'
	DECLARE @FactoryID					NVARCHAR (255) = 'ZT'
	DECLARE @IsIncrementalValuesFlag	INT			   = 1

	-- 3 Values with comments  - NEVER place a linebreak in the sample array !
	-- DECLARE @ValuesInBracketsCommaSeparated nvarchar(max) =  '[(''K1'',20170115,''=IF(H25=4,3,2.1)'',''6'',''HaseTextValue'',''HaseComment'')],[(''K2'',20170215,''=IF(H25=4,3,2)'',''555'',''HaseTextValue'',''HaseComment2'')],[(''E1'',20170515,'''','''',''Text'',''Comment'')]'

	-- Delete the three values by sending them empty
	-- DECLARE @ValuesInBracketsCommaSeparated nvarchar(max) = '[(''K1'',20170115,'''','''','''',''HaseComment'')],[(''K2'',20170215,'''','''','''',''HaseComment2'')],[(''E1'',20170515,'''','''','''','''')]'

	-- Send an <#NV> Value for an existing numeric and an Text Value and delete one
	-- DECLARE @ValuesInBracketsCommaSeparated nvarchar(max) = '[(''K1'',20170115,'''',''<#NV>'','''',''HaseComment'')],[(''K2'',20170215,'''','''','''',''HaseComment2'')],[(''E1'',20170515,'''','''',''<#NV>'','''')]'

	EXECUTE @RC = dbo.sx_pf_POST_ProductDataTableValues 
		 @Username
		,@ProductID
		,@ProductLineID
		,@FactoryID
		,@IsIncrementalValuesFlag
		,@ValuesInBracketsCommaSeparated;

	PRINT @RC;
 
	Test case 2 - fill all Values of an Product ************************************************
	DECLARE @RC								INT
	DECLARE @Username						NVARCHAR(255) = 'SQL'
	DECLARE @ProductID						NVARCHAR(255) = '1'
	DECLARE @ProductLineID					NVARCHAR(255) = 'U'
	DECLARE @FactoryID						NVARCHAR(255) = 'ZT'
	DECLARE @IsIncrementalValuesFlag		INT = 0
	DECLARE @ValuesInBracketsCommaSeparated nvarchar(max) = ''
	DECLARE @start_time						DATETIME

	-- Generate fictive values for all TimeIDs of Products
	SELECT @ValuesInBracketsCommaSeparated = @ValuesInBracketsCommaSeparated + '[(''' + dVS.ValueSeriesID +''',' + CAST(TimeID AS nvarchar(255)) +',''=A2'',''1000'',''Hase | Maus'',''Komment|ar'')],' 
	FROM 
		planning.tdValueSeries dVS LEFT JOIN planning.tdTime dT
			ON dVS.ProductKey = dT.ProductKey
	WHERE 
		dVS.FactoryID			= @FactoryID 
		AND dVS.ProductLineID	= @ProductLineID 
		AND dVS.ProductID		= @ProductID 

	SET @ValuesInBracketsCommaSeparated = LEFT(@ValuesInBracketsCommaSeparated, LEN(@ValuesInBracketsCommaSeparated) -1)
	PRINT @ValuesInBracketsCommaSeparated

	SET @start_time = GETUTCDATE()
	EXECUTE @RC = dbo.sx_pf_POST_ProductDataTableValues
		@Username
		,@ProductID
		,@ProductLineID
		,@FactoryID
		,@IsIncrementalValuesFlag
		,@ValuesInBracketsCommaSeparated;
	PRINT @RC;

	SELECT RTRIM(CAST(DATEDIFF(MS, @start_time, GETUTCDATE()) AS CHAR(10))) AS 'TimeTaken'

	-- Testcase 3 - delete all values
	DECLARE @RC								INT
	DECLARE @Username						NVARCHAR (255)	= 'SQL'
	DECLARE @ProductID						NVARCHAR (255)	= '1'
	DECLARE @ProductLineID					NVARCHAR (255)	= 'U'
	DECLARE @FactoryID						NVARCHAR (255)	= 'ZT'
	DECLARE @IsIncrementalValuesFlag		INT				= 0
	DECLARE @ValuesInBracketsCommaSeparated NVARCHAR (255)	= ''

	EXECUTE @RC = dbo.sx_pf_POST_ProductDataTableValues
		@Username
		,@ProductID
		,@ProductLineID
		,@FactoryID
		,@IsIncrementalValuesFlag
		,@ValuesInBracketsCommaSeparated;
	PRINT @RC;

	Testcall Documentation
		SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'dbo', 'PROCEDURE', 'sx_pf_POST_ProductDataTableValues',NULL,NULL)
		UNION ALL
		SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'dbo', 'PROCEDURE', 'sx_pf_POST_ProductDataTableValues','PARAMETER',NULL)
*/

CREATE PROCEDURE dbo.sx_pf_POST_ProductDataTableValues
	@Username							NVARCHAR(255),
	@ProductID							NVARCHAR(255),
	@ProductLineID						NVARCHAR(255),
	@FactoryID							NVARCHAR(255),
	@IsIncrementalValuesFlag			INT,
	@ValuesInBracketsCommaSeparated		NVARCHAR(MAX) 

/*

*/

AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @TransactUsername		NVARCHAR(255)	= N'';
	DECLARE @FactoryKey				INT				= 0;
	DECLARE @ProductLineKey 		INT				= 0;
	DECLARE @ProductKey 			INT				= 0;
	DECLARE @errorvalue				NVARCHAR(255);
	DECLARE @errormessage			NVARCHAR(2000);

	DECLARE @ProcedureName			NVARCHAR(255)	= OBJECT_SCHEMA_NAME(@@PROCID) + N'.' + OBJECT_NAME(@@PROCID);
	DECLARE @ParameterString		NVARCHAR(MAX)	= N'''' 
									+ ISNULL(@Username													, N'NULL')	+ N''',''' 
									+ ISNULL(@ProductID													, N'NULL')	+ N''',''' 
									+ ISNULL(@ProductLineID												, N'NULL')	+ N''',''' 
									+ ISNULL(@FactoryID													, N'NULL')	+ N''','
									+ ISNULL(CAST(@IsIncrementalValuesFlag AS NVARCHAR(255))			, N'NULL')	+ N','''
									+ ISNULL(REPLACE(@ValuesInBracketsCommaSeparated, N'''', N'''''')	, N'NULL')	+ N'''';  -- WHY the replace - as there are double inverted comma's ?

	DECLARE @EffectedRows			INT				= 0;						
	DECLARE @ResultCode				INT				= 501;						
	DECLARE @TimestampCall			DATETIME		= GETUTCDATE();
	DECLARE @Comment				NVARCHAR(2000)	= N'';			
	-- SET Debug Flag for debugging and disable Transcation 
	DECLARE @DebugFlag				INT				= 0
	
	/*
		--Use for debugging
		SELECT * FROM ##Debug_ValuesInBracketsReceived 
		SELECT * FROM ##Debug_ValueMergeTableFilled ORDER BY ValueSeriesID,TimeID
		SELECT * FROM ##Debug_ValueMergeTableBeforeTransmissions ORDER BY ValueSeriesID,TimeID
	*/

	-- STEP 0.1 - NULL Protection
	IF @Username						IS NULL SET @Username						= N'';
	IF @ProductID						IS NULL SET @ProductID						= N'';
	IF @ProductLineID					IS NULL SET @ProductLineID					= N'';
	IF @FactoryID						IS NULL SET @FactoryID						= N'';
	IF @IsIncrementalValuesFlag			IS NULL SET @IsIncrementalValuesFlag		= 0;
	IF @ValuesInBracketsCommaSeparated	IS NULL SET @ValuesInBracketsCommaSeparated = N'';

	-- To delete Values in NOT Incremental Transmission, an empty @ValuesInBracketsCommaSeparated Parameter is sended
	IF @ValuesInBracketsCommaSeparated = '' AND @IsIncrementalValuesFlag = 0 
	BEGIN
		SET @ValuesInBracketsCommaSeparated = '[(''DeleteFlagID'',99999999,'''',''0'','''','''')]'  --special ID with prevents from rejection from errorhandler
	END

	BEGIN TRY
		BEGIN TRANSACTION POST_PDT;

		-- STEP 0.2 - Protect input parameters
		SET @FactoryID		= dbo.sx_pf_pProtectID (@FactoryID);
		SET @ProductID		= dbo.sx_pf_pProtectID (@ProductID);
		SET @ProductLineID	= dbo.sx_pf_pProtectID (@ProductLineID);

		IF	@FactoryID			= N'' 
			OR @ProductLineID	= N'' 
			OR @ProductID		= N''
		BEGIN
			SET @ResultCode = 404;
			RAISERROR('Empty input parameters, one or more IDs are empty', 16, 10);
		END;
		
		-- STEP 1.1 - Determine transaction user
		SELECT @TransactUsername = dbo.sx_pf_Determine_TransactionUsername (@Username);

		IF @TransactUsername = N'403' 
		BEGIN
			SET @ResultCode = 403;
			RAISERROR('Transaction user don`t exists', 16, 10);
		END;

		-- STEP 1.2 - Determine keys
		SELECT @FactoryKey		= FactoryKey		FROM planning.tdFactories	WHERE FactoryID = @FactoryID;
		SELECT @ProductLineKey	= ProductLineKey	FROM planning.tdProductLines	WHERE FactoryKey = @FactoryKey AND ProductLineID = @ProductLineID;
		SELECT @ProductKey		= ProductKey		FROM planning.tdProducts		WHERE ProductLineKey =  @ProductLineKey AND ProductID = @ProductID;

		IF	@FactoryKey			= 0 
			OR @ProductLineKey	= 0 
			OR @ProductKey		= 0
		BEGIN
			SET @ResultCode = 404;
			RAISERROR('One or more IDs dont exist (No Keys found)', 16, 10);
		END;

		-- STEP 2 - Check rights
		EXEC @ResultCode = sx_pf_pGET_ProductLineWriteRight @TransactUsername, @FactoryID, @ProductLineID;

		IF @ResultCode <> 200
			BEGIN
				SET @errormessage = 'Invalid rights - no write Rights for ' + @TransactUsername + '.';
				RAISERROR(@errormessage, 16, 10);
			END

		-- STEP 3.0 - Store Timeline, only TimeID, as Timeline is not changed during Posting
		IF OBJECT_ID('tempdb..#Timeline') IS NOT NULL
		DROP TABLE #Timeline;	

		CREATE TABLE #Timeline (																																							
			TimeID NVARCHAR(10) COLLATE DATABASE_DEFAULT NOT NULL UNIQUE CLUSTERED
		);
				
		INSERT INTO #Timeline
		SELECT TimeID
		FROM planning.tdTime
		WHERE ProductKey = @ProductKey;

		-- STEP 3.1 - ValueMergeTable Merge the current values of the product into the ValueMergeTable
		IF OBJECT_ID('tempdb..#ValueMergeTable') IS NOT NULL
		DROP TABLE #ValueMergeTable;

		CREATE TABLE #ValueMergeTable ( 
			 ValueSeriesKey			BIGINT										NOT NULL
			,ValueSeriesID			NVARCHAR(255)	COLLATE DATABASE_DEFAULT	NOT NULL 
			,TimeID					NVARCHAR(10)	COLLATE DATABASE_DEFAULT	NOT NULL 
			,ValueFormula			NVARCHAR(MAX)	COLLATE DATABASE_DEFAULT	NOT NULL 
			,ValueInt				NVARCHAR(255)	COLLATE DATABASE_DEFAULT	NOT NULL 
			,ValueText				NVARCHAR(MAX)	COLLATE DATABASE_DEFAULT	NOT NULL 
			,ValueComment			NVARCHAR(MAX)	COLLATE DATABASE_DEFAULT	NOT NULL 
			,[IsNumeric]			INT											NOT NULL 
			,IsFormula				INT											NOT NULL 
			,ProcessCode			NVARCHAR(20)	COLLATE DATABASE_DEFAULT	NOT NULL 
			,DocuCode				NVARCHAR(20)	COLLATE DATABASE_DEFAULT	NOT NULL 
			,ValueSeriesID_TimeID 	NVARCHAR(255)	COLLATE DATABASE_DEFAULT	NOT NULL 
			,AllowZero				INT											NOT NULL
			);

		-- First Type "Old Values" is loaded in ValueMergeTable
		INSERT INTO #ValueMergeTable
		SELECT 
			0												AS ValueSeriesKey  -- No Values from dValueSeries, this Informations are later added when old and sended Values are together
			,ValueSeriesID  
			,v.TimeID
			,ValueFormula
			,ValueInt
			,ValueText
			,ValueComment
			,0												AS IsNumeric
			,0												AS IsFormula
			,N'OLD VALUES'									AS ProcessCode
			,N'NONE'										AS DocuCode
			,ValueSeriesID + CAST(v.TimeID AS NVARCHAR(10))	AS ValueSeries_TimeID
			,0												AS AllowZero
		FROM planning.tfValues v INNER JOIN #Timeline tl ON v.TimeID = tl.TimeID
		WHERE ProductKey = @ProductKey;		
			
		-- Store Header, which is dValueSeries Information which will added to old and sended Values
		IF OBJECT_ID('tempdb..#Header') IS NOT NULL
		DROP TABLE #Header;																			

		CREATE TABLE #Header (
			 ValueSeriesKey		BIGINT									NOT NULL
			,ValueSeriesID		NVARCHAR(255) COLLATE DATABASE_DEFAULT	NOT NULL
			,[IsNumeric]		INT										NOT NULL
			,IsFormula			INT										NOT NULL
			,AllowZero			INT										NOT NULL
			,UNIQUE CLUSTERED (ValueSeriesID, ValueSeriesKey, [IsNumeric], IsFormula)
			);
			
		INSERT INTO #Header
		SELECT 
			ValueSeriesKey
			,ValueSeriesID
			,[IsNumeric]
			,IIF(ValueSource IN (N'XLS',N'XLS-Strict'), 1, 0) AS IsFormula
			,AllowZero
		FROM planning.tdValueSeries 
		WHERE ProductKey = @ProductKey;

		-- STEP 3.3 - Check for count of delimiter in Array
		DECLARE @OpenDelimetersCount	INT = 0;
		DECLARE @DelimetersIndex		INT = CHARINDEX(N'[(', @ValuesInBracketsCommaSeparated);

		WHILE @DelimetersIndex > 0 
		BEGIN
			SET @OpenDelimetersCount += 1;
			SET @DelimetersIndex = CHARINDEX(N'[(', @ValuesInBracketsCommaSeparated, @DelimetersIndex + 1);
		END;

		IF @OpenDelimetersCount > 32765 
		BEGIN
			SET @ResultCode = 403;
			RAISERROR('ValueArray to big (More then 32750 Values need special API Update.)', 16, 10);
		END;
				
		IF @OpenDelimetersCount = 0
		BEGIN
			SET @ResultCode = 404;
			RAISERROR('No delimiters found in @ValuesInBracketsCommaSeparated', 16, 10);
		END;

		SET @DelimetersIndex = CHARINDEX(N')]', @ValuesInBracketsCommaSeparated);

		WHILE (@DelimetersIndex > 0) 
		BEGIN
			SET @OpenDelimetersCount -= 1;
			SET @DelimetersIndex = CHARINDEX(N')]', @ValuesInBracketsCommaSeparated, @DelimetersIndex + 1);
		END;

		IF @OpenDelimetersCount <> 0
		BEGIN
			SET @ResultCode = 404;
			RAISERROR('Invalid count of delimiter in @ValuesInBracketsCommaSeparated', 16, 10);
		END;

		-- STEP 3.4 - Splitt the Array with the new values for the Product to a temporary table #######################################################################################################################
		-- cut leading/following brackets and ' from ValueSeriesID
		SET @ValuesInBracketsCommaSeparated = LEFT(@ValuesInBracketsCommaSeparated,  LEN(@ValuesInBracketsCommaSeparated)-2);
		SET @ValuesInBracketsCommaSeparated = RIGHT(@ValuesInBracketsCommaSeparated, LEN(@ValuesInBracketsCommaSeparated)-2);

		DROP TABLE IF EXISTS #Values1;
		CREATE TABLE #Values1  
			(
				txtValuesPipe		NVARCHAR(MAX)	COLLATE DATABASE_DEFAULT NOT NULL
			);
		DROP TABLE IF EXISTS #Values2;
		CREATE TABLE #Values2  
			(
				 ValueSeriesID		NVARCHAR(255)	COLLATE DATABASE_DEFAULT NOT NULL
				,txtValuesPipe		NVARCHAR(MAX)	COLLATE DATABASE_DEFAULT NOT NULL
			);
		DROP TABLE IF EXISTS #Values3;
		CREATE TABLE #Values3  
			(
				  ValueSeriesID		NVARCHAR(255)	COLLATE DATABASE_DEFAULT NOT NULL
				 ,TimeID			NVARCHAR(10)	COLLATE DATABASE_DEFAULT NOT NULL
				 ,txtValuesPipe		NVARCHAR(MAX)	COLLATE DATABASE_DEFAULT NOT NULL
			);
		DROP TABLE IF EXISTS #Values4;
		CREATE TABLE #Values4  
			(
				 ValueSeriesID		NVARCHAR(255)	COLLATE DATABASE_DEFAULT NOT NULL
				,TimeID				NVARCHAR(10)	COLLATE DATABASE_DEFAULT NOT NULL
				,ValueFormula		NVARCHAR(MAX)	COLLATE DATABASE_DEFAULT NOT NULL
				,txtValuesPipe		NVARCHAR(MAX)	COLLATE DATABASE_DEFAULT NOT NULL
			);
		DROP TABLE IF EXISTS #Values5;
		CREATE TABLE #Values5  
			(
				 ValueSeriesID		NVARCHAR(255)	COLLATE DATABASE_DEFAULT NOT NULL
				,TimeID				NVARCHAR(10)	COLLATE DATABASE_DEFAULT NOT NULL
				,ValueFormula		NVARCHAR(MAX)	COLLATE DATABASE_DEFAULT NOT NULL
				,ValueInt			NVARCHAR(255)	COLLATE DATABASE_DEFAULT NOT NULL
				,txtValuesPipe		NVARCHAR(MAX)	COLLATE DATABASE_DEFAULT NOT NULL
			);
		DROP TABLE IF EXISTS #Values6;
		CREATE TABLE #Values6  
			(
				 ValueSeriesID		NVARCHAR(255)	COLLATE DATABASE_DEFAULT NOT NULL
				,TimeID				NVARCHAR(10)	COLLATE DATABASE_DEFAULT NOT NULL
				,ValueFormula		NVARCHAR(MAX)	COLLATE DATABASE_DEFAULT NOT NULL
				,ValueInt			NVARCHAR(255)	COLLATE DATABASE_DEFAULT NOT NULL
				,ValueText			NVARCHAR(MAX)	COLLATE DATABASE_DEFAULT NOT NULL
				,ValueComment		NVARCHAR(MAX)	COLLATE DATABASE_DEFAULT NOT NULL
			);

		DECLARE @spr NCHAR (1) = CHAR(29)

		-- Remove existing CHAR(29) = not visible character from string
		SET @ValuesInBracketsCommaSeparated = REPLACE(@ValuesInBracketsCommaSeparated,@spr,N'');
		--PRINT @ValuesInBracketsCommaSeparated

		-- Split String into Rows - all Values except the TimeID are single quoted after that an separated by @spr																													
		INSERT INTO #Values1 
		SELECT REPLACE(REPLACE(txtValues,N',''', @spr + ''''), N''',', '''' + @spr) 
		FROM dbo.sx_pf_PivotStringIntoTable(@ValuesInBracketsCommaSeparated, N')],[(');

		-- Split string into two Columns (separate first column ValueSeriesID)
		INSERT INTO #Values2
		SELECT  
			LEFT(txtValuesPipe, CHARINDEX(@spr, txtValuesPipe) - 1)
			,RIGHT(txtValuesPipe, LEN(txtValuesPipe) - CHARINDEX(@spr, txtValuesPipe))
		FROM #Values1;

		-- Split last columns once more (separate TimeID)
		INSERT INTO #Values3
		SELECT  
			ValueSeriesID
			,LEFT(txtValuesPipe, CHARINDEX(@spr, txtValuesPipe) - 1) 
			,RIGHT(txtValuesPipe, LEN(txtValuesPipe) - CHARINDEX(@spr, txtValuesPipe))
		FROM #Values2;

		-- Delete Implicit sended periods (dont exit in current Product)
		DELETE FROM #Values3 
		WHERE TimeID NOT IN (SELECT CAST(TimeID AS NVARCHAR(10)) FROM #Timeline);

		-- Split last columns once more (separate ValueFormula)
		INSERT INTO #Values4
		SELECT  
			ValueSeriesID
			,TimeID
			,LEFT(txtValuesPipe, CHARINDEX(@spr, txtValuesPipe) - 1) 
			,RIGHT(txtValuesPipe, LEN(txtValuesPipe) - CHARINDEX(@spr, txtValuesPipe))
		FROM #Values3;

		-- Split last columns once more (separate ValueInt)
		INSERT INTO #Values5
		SELECT
			ValueSeriesID  
			,TimeID
			,ValueFormula
			,LEFT(txtValuesPipe, CHARINDEX(@spr, txtValuesPipe) - 1)
			,RIGHT(txtValuesPipe, LEN(txtValuesPipe) - CHARINDEX(@spr, txtValuesPipe))
		FROM #Values4;

		-- Split last columns once more (Separate ValueText and ValueComment)
		INSERT INTO #Values6
		SELECT 
			ValueSeriesID 
			,TimeID
			,ValueFormula
			,ValueInt
			,LEFT(txtValuesPipe, CHARINDEX(@spr, txtValuesPipe) - 1) 
			,RIGHT(txtValuesPipe, LEN(txtValuesPipe) - CHARINDEX(@spr, txtValuesPipe))
		FROM #Values5;

		-- Replace Quotes from all strings (all except TimeID)
		UPDATE #Values6
		SET	 
			 ValueSeriesID	= RIGHT(LEFT(ValueSeriesID,	(LEN(ValueSeriesID)-1)),	LEN(ValueSeriesID)-2)
			,ValueFormula	= RIGHT(LEFT(ValueFormula,	(LEN(ValueFormula)-1)),		LEN(ValueFormula)-2)
			,ValueInt		= RIGHT(LEFT(ValueInt,		(LEN(ValueInt)-1)),			LEN(ValueInt)-2)
			,ValueText		= RIGHT(LEFT(ValueText,		(LEN(ValueText)-1)),		LEN(ValueText)-2)
			,ValueComment	= RIGHT(LEFT(ValueComment,	(LEN(ValueComment)-1)),		LEN(ValueComment)-2);

		-- Replace Linebreaks in ValueText, but keep them in ValueComment
		UPDATE #Values6
			SET 
				ValueText =REPLACE(REPLACE(ValueText,CHAR(13),''),CHAR(10),'');

		IF @DebugFlag = 1 
		BEGIN
			IF OBJECT_ID('tempdb..##Debug_Values6') IS NOT NULL DROP TABLE ##Debug_Values6;
				SELECT * INTO ##Debug_Values6 FROM #Values6;
		END;


		-- STEP 3.5 - UNION both temporary tables #######################################################################################################################

		-- Second Typ "Sended Values" is loaded in ValueMergeTable
		INSERT INTO #ValueMergeTable
		SELECT 
			0 AS ValueSeriesKey
			,ValueSeriesID
			,TimeID
			,ValueFormula
			,ValueInt
			,ValueText
			,ValueComment
			,0 AS [IsNumeric]
			,0 AS IsFormula
			,N'SENDED VALUES'
			,N'NONE'
			,ValueSeriesID+TimeID
			,0 AS AllowZero
		FROM #Values6;

		-- Add the header informations to the values
		UPDATE VMT 
		SET	
			 VMT.ValueSeriesKey = H.ValueSeriesKey
			,VMT.[IsNumeric]	= H.[IsNumeric]
			,VMT.IsFormula		= H.IsFormula 
			,VMT.AllowZero		= H.AllowZero
		FROM #ValueMergeTable VMT INNER JOIN #Header H 
			ON VMT.ValueSeriesID = H.ValueSeriesID;

		-- check for not existing ValueSeriesIDs which may have been sended
		IF EXISTS(SELECT TOP (1) ValueSeriesID FROM #ValueMergeTable WHERE ValueSeriesKey = 0)
		BEGIN
			SELECT TOP(1) @errorvalue = ValueSeriesID 
			FROM #ValueMergeTable 
			WHERE ValueSeriesKey = 0 
			ORDER BY TimeID;

			IF @errorvalue = 'DeleteFlagID'
			BEGIN
				DELETE FROM #ValueMergeTable
			END
			ELSE
			BEGIN
				SET @errormessage = 'One or more sended ValueSeriesIDs were not found, e.g. '+ @errorvalue +'.'
				SET @ResultCode = 403;
				RAISERROR(@errormessage, 16, 10);
			END
		END;

		-- check if more than one value is sended for a ValueSeries/TimeID combination
		IF EXISTS(
			SELECT COUNT(TimeID) 
			FROM #ValueMergeTable 
			WHERE ProcessCode = 'SENDED VALUES'
			GROUP BY ValueSeriesID_TimeID
			HAVING COUNT(TimeID) > 1
		)
		BEGIN
			SET @ResultCode = 403;
			RAISERROR('Douplicate Values found in ValueArray', 16, 10);
		END;

		-- STEP 3.6 - Prepare the ValueMergeTable #######################################################################################################################	
		-- FLAG OLD Values the user want to delete in Case of Numeric Series without formula
		-- Value 0 maybe sended as '000'
		UPDATE #ValueMergeTable 
		SET ProcessCode = N'OLD VALUES TO DELETE' 
		WHERE 
			ProcessCode = N'OLD VALUES' 
			AND [IsNumeric] = 1 
			AND IsFormula	= 0 
			AND ValueSeriesID_TimeID IN 
				(
					SELECT ValueSeriesID_TimeID 
					FROM #ValueMergeTable 
					WHERE 
						ProcessCode = N'SENDED VALUES' 
						AND	(TRY_CAST(ValueInt AS BIGINT) = 0 OR ValueInt = N'')
				);

		-- FLAG OLD Values the user want to delete in Case of Numeric Series with formula
		UPDATE #ValueMergeTable 
		SET ProcessCode = N'OLD VALUES TO DELETE' 
		WHERE 
			ProcessCode = N'OLD VALUES' 
			AND [IsNumeric] = 1 
			AND IsFormula = 1  
			AND ValueSeriesID_TimeID IN 
				(
					SELECT ValueSeriesID_TimeID 
					FROM #ValueMergeTable 
					WHERE 
						ProcessCode	 = N'SENDED VALUES' 
						AND ValueFormula = N''
						AND (TRY_CAST(ValueInt AS BIGINT) = 0 OR ValueInt = N'')
				);

		-- FLAG OLD Values the user want to delete in Case of NON-Numeric Series without formula
		UPDATE #ValueMergeTable 
		SET ProcessCode = N'OLD VALUES TO DELETE' 
		WHERE 
			ProcessCode = N'OLD VALUES' 
			AND [IsNumeric] = 0 
			AND IsFormula = 0  
			AND ValueSeriesID_TimeID IN 
				(
					SELECT ValueSeriesID_TimeID 
					FROM #ValueMergeTable 
					WHERE 
						ProcessCode = N'SENDED VALUES' 
						AND ValueText	= N''
				);

		-- FLAG OLD Values the user want to delete in Case of NON-Numeric Series with formula
		UPDATE #ValueMergeTable 
		SET ProcessCode = N'OLD VALUES TO DELETE' 
		WHERE 
			ProcessCode = N'OLD VALUES' 
			AND [IsNumeric] = 0 
			AND IsFormula = 1 
			AND ValueSeriesID_TimeID IN 
				(
					SELECT ValueSeriesID_TimeID 
					FROM #ValueMergeTable 
					WHERE 
						ProcessCode		= N'SENDED VALUES' 
						AND	ValueText		= N'' 
						AND ValueFormula	= N''
				);

		-- Debugging Output
		IF @DebugFlag = 1 
		BEGIN
			IF OBJECT_ID('tempdb..##Debug_ValueMergeTableFilled') IS NOT NULL 
				DROP TABLE ##Debug_ValueMergeTableFilled;
			
			SELECT * 
			INTO ##Debug_ValueMergeTableFilled 
			FROM #ValueMergeTable;
		END;

		-- STEP 3.7 - Handle VMT #######################################################################################################################

		-- Documentation of changes in log table

			/* 

			-- NEW is a Value which is not contained in existing Values
			UPDATE	#ValueMergeTable 
			SET		DocuCode = N'NEW VALUE'
			WHERE 
						ProcessCode = N'SENDED VALUES' 
					AND DocuCode = N'NONE' 
					AND ValueSeriesID + N'_' + TimeID NOT IN 
						(
							SELECT	ValueSeriesID + N'_' + TimeID
							FROM	#ValueMergeTable 
							WHERE	ProcessCode = N'OLD VALUES'
						); 
			-- CHANGED is a Value which is contained in existing Values
			UPDATE	#ValueMergeTable 
			SET		DocuCode = N'CHANGED VALUE'
			WHERE	
						ProcessCode = N'SENDED VALUES' 
					AND DocuCode = N'NONE' 
					AND	ValueSeriesID + N'_' + TimeID IN 
						(
							SELECT	ValueSeriesID + N'_' + TimeID
							FROM	#ValueMergeTable 
							WHERE	ProcessCode = N'OLD VALUES'
						); 
			
			INSERT INTO sx_pf_AuditLog VALUES
				SELECT * FROM #ValueMergeTable WHERE 
					ValueSeriesID + '_' + CAST(TimeID AS NVARCHAR(10)) IN 
					(
					SELECT
						ValueSeriesID + '_' + CAST(TimeID AS NVARCHAR(10)) FROM #ValueMergeTable WHERE ProcessCode = 'SENDED VALUES' AND
						DocuCode != 'NONE'
					)
			*/

		-- Handle <#NV> Parameters - Replace them with the old Values (if exist !)

		UPDATE	VMT 
		SET		VMT.ValueInt = OV.ValueInt 
		FROM	#ValueMergeTable VMT
			, (SELECT ValueInt, ValueSeriesID, TimeID  FROM #ValueMergeTable WHERE ProcessCode IN (N'OLD VALUES', N'OLD VALUES TO DELETE')) OV
		WHERE	
			VMT.ProcessCode = N'SENDED VALUES' 
			AND VMT.ValueInt LIKE N'<#NV>' 
			AND VMT.ValueSeriesID = OV.ValueSeriesID 
			AND VMT.TimeID = OV.TimeID;

		UPDATE	VMT 
		SET		VMT.ValueText = OV.ValueText
		FROM	#ValueMergeTable VMT
			, (SELECT ValueText, ValueSeriesID, TimeID FROM #ValueMergeTable WHERE ProcessCode IN (N'OLD VALUES', N'OLD VALUES TO DELETE')) OV
		WHERE	
			VMT.ProcessCode		= N'SENDED VALUES' 
			AND VMT.ValueText = N'<#NV>'
			AND VMT.ValueSeriesID = OV.ValueSeriesID 
			AND VMT.TimeID = OV.TimeID;

		UPDATE	VMT 
		SET		VMT.ValueFormula = OV.ValueFormula 
		FROM	#ValueMergeTable VMT
			, (SELECT ValueFormula, ValueSeriesID, TimeID FROM #ValueMergeTable WHERE ProcessCode IN (N'OLD VALUES', N'OLD VALUES TO DELETE')) OV
		WHERE	
			VMT.ProcessCode = N'SENDED VALUES' 
			AND VMT.ValueFormula = N'<#NV>'
			AND VMT.ValueSeriesID = OV.ValueSeriesID
			AND VMT.TimeID = OV.TimeID;

		UPDATE	VMT 
		SET		VMT.ValueComment = OV.ValueComment 
		FROM	#ValueMergeTable VMT
			, (SELECT ValueComment, ValueSeriesID, TimeID FROM #ValueMergeTable WHERE ProcessCode IN (N'OLD VALUES', N'OLD VALUES TO DELETE')) OV
		WHERE	
			VMT.ProcessCode = N'SENDED VALUES' 
			AND VMT.ValueComment = N'<#NV>'
			AND VMT.ValueSeriesID = OV.ValueSeriesID
			AND VMT.TimeID = OV.TimeID;

		-- Handle <#NV> Parameter, where no old Values existed
		UPDATE #ValueMergeTable SET ValueInt		= N'0'	WHERE ValueInt		= N'<#NV>' OR [IsNumeric] = 0;
		UPDATE #ValueMergeTable SET ValueText		= N''	WHERE ValueText		= N'<#NV>' OR [IsNumeric] = 1;
		UPDATE #ValueMergeTable SET ValueFormula	= N''	WHERE ValueFormula	= N'<#NV>' OR IsFormula = 0;
		UPDATE #ValueMergeTable SET ValueComment	= N''	WHERE ValueComment	= N'<#NV>';

		-- Debugging Output
		IF @DebugFlag = 1 
		BEGIN
			IF OBJECT_ID('tempdb..##Debug_ValueMergeTableBeforeTransmissions') IS NOT NULL 
				DROP TABLE ##Debug_ValueMergeTableBeforeTransmissions;
			
			SELECT * 
			INTO ##Debug_ValueMergeTableBeforeTransmissions 
			FROM #ValueMergeTable;
		END;

		-- STEP 3.8 - Save data
		IF @IsIncrementalValuesFlag <> 1 
		BEGIN
			-- CASE FULL transmission #########################################
			-- Delete all Values
			DELETE FROM planning.tfValues WHERE ProductKey = @ProductKey;
			
			SET @EffectedRows += @@ROWCOUNT;
			
			-- DELETE Useless NullEmpty Values in ALL Values

			-- einfache Nullwerte
			DELETE FROM #ValueMergeTable
			WHERE 
				[IsNumeric]		= 1 
				AND IsFormula	= 0 
				AND COALESCE(TRY_CAST(ValueInt AS BIGINT),0) = 0
				AND (RIGHT(ValueSeriesID,2) <> '_0' AND AllowZero = 0);

			-- Numerische Formelzellen ohne Formel mit Wert 0
			DELETE FROM #ValueMergeTable
			WHERE 
				[IsNumeric]			= 1 
				AND IsFormula		= 1 
				AND ValueFormula	= '' 
				AND COALESCE(TRY_CAST(ValueInt AS BIGINT),0) = 0
				AND (RIGHT(ValueSeriesID,2) <> '_0' AND AllowZero = 0);

			-- Gelöschte Werte in numerischen Zellen
			DELETE FROM #ValueMergeTable
			WHERE 
				[IsNumeric]			= 1 
				AND IsFormula		= 0
				AND ValueInt = N'';

			-- einfach Leertexte
			DELETE FROM #ValueMergeTable
			WHERE 
				[IsNumeric]	  = 0 
				AND IsFormula = 0 
				AND ValueText = N'';

			-- Nichtnumerische Formelzellen ohne Formel mit Leertext
			DELETE FROM #ValueMergeTable
			WHERE 
				[IsNumeric]		 = 0 
				AND IsFormula	 = 1 
				AND ValueFormula = '' 
				AND ValueText	 = N'';

			-- Insert all Values
			INSERT INTO planning.tfValues
			SELECT 
				ValueSeriesKey
				,@ProductKey
				,@ProductLineKey
				,@FactoryKey
				,@ProductID
				,@ProductLineID
				,@FactoryID
				,ValueSeriesID
				,TimeID
				,ValueFormula
				,ValueInt
				,ValueText
				,ValueComment
			FROM #ValueMergeTable 
			WHERE ProcessCode IN (N'SENDED VALUES');
				
			SET @EffectedRows += @@ROWCOUNT;
		END
		ELSE
		BEGIN
			-- CASE INCREMENTAL Transmission - it also leads to a full delete and insert !! #########################################
			-- DELETE Values, which are sended to delete in OLD VALUES
			DELETE FROM #ValueMergeTable WHERE ProcessCode = N'OLD VALUES TO DELETE'; 

			-- DELETE sended non-formula values
			DELETE FROM #ValueMergeTable
			WHERE 
				ProcessCode	  = N'SENDED VALUES' 
				AND [IsNumeric] = 1 
				AND IsFormula = 0 
				AND COALESCE(TRY_CAST(ValueInt AS BIGINT),0) = 0
				AND (RIGHT(ValueSeriesID,2) <> '_0' AND AllowZero = 0);						-- send Null Values for ValueSeries *_0;

			--empty values must delete 0 in _0 Series
			DELETE FROM #ValueMergeTable
			WHERE 
				ProcessCode	  = N'SENDED VALUES' 
				AND [IsNumeric] = 1 
				AND IsFormula = 0 
				AND ValueInt = ''
				AND (RIGHT(ValueSeriesID,2) = '_0' AND AllowZero = 0);
			
			-- delete deleted formulas 
			DELETE FROM #ValueMergeTable 
			WHERE 
				ProcessCode	  = N'SENDED VALUES' 
				AND [IsNumeric] = 1 
				AND IsFormula = 1 
				AND COALESCE(TRY_CAST(ValueInt AS BIGINT),0) = 0 
				AND ValueFormula = N''
				AND (RIGHT(ValueSeriesID,2) <> '_0' AND AllowZero = 0);						-- send Null Values for ValueSeries *_0;

			-- Delete 
			DELETE FROM #ValueMergeTable 
			WHERE 
				ProcessCode	  = N'SENDED VALUES'
				AND [IsNumeric] = 0
				AND IsFormula = 0 
				AND ValueText = N''
				

			DELETE FROM #ValueMergeTable 
			WHERE 
				ProcessCode		 = N'SENDED VALUES' 
				AND [IsNumeric]	 = 0 
				AND IsFormula	 = 1 
				AND ValueText	 = N'' 
				AND ValueFormula = N'';

			DELETE FROM #ValueMergeTable
			WHERE 
				ProcessCode	  = N'SENDED VALUES' 
				AND [IsNumeric] = 1
				AND IsFormula = 0 
				AND ValueInt = N'';

			-- DELETE Useless NullEmpty Values in OLD Values
			DELETE FROM #ValueMergeTable
			WHERE 
				ProcessCode	  = N'OLD VALUES'
				AND [IsNumeric] = 1 
				AND IsFormula = 0 
				AND COALESCE(TRY_CAST(ValueInt AS BIGINT),0) = 0
				AND (RIGHT(ValueSeriesID,2) <> '_0' AND AllowZero = 0);						-- send Null Values for ValueSeries *_0
				
			DELETE FROM #ValueMergeTable
			WHERE 
				ProcessCode	  = N'OLD VALUES' 
				AND [IsNumeric] = 0 
				AND IsFormula = 0 
				AND ValueText = N'';
			-- DELETE all Values
			DELETE FROM planning.tfValues
			WHERE ProductKey = @ProductKey;
			
			SET @EffectedRows += @@ROWCOUNT;

			-- Delete OLD VALUES which are new sended
			DELETE	FROM #ValueMergeTable 
			WHERE 
				ProcessCode = N'OLD VALUES'  
				AND ValueSeriesID_TimeID IN 
					(
						SELECT ValueSeriesID_TimeID
						FROM  #ValueMergeTable 
						WHERE ProcessCode = N'SENDED VALUES'
					);

			-- Insert all Values
			INSERT INTO planning.tfValues
			SELECT 
				ValueSeriesKey
				,@ProductKey
				,@ProductLineKey
				,@FactoryKey
				,@ProductID
				,@ProductLineID
				,@FactoryID
				,ValueSeriesID
				,TimeID
				,ValueFormula
				,CAST(ValueInt AS BIGINT)
				,ValueText
				,ValueComment
			FROM #ValueMergeTable 
			WHERE ProcessCode IN (N'SENDED VALUES', N'OLD VALUES');
			
			SET @EffectedRows += @@ROWCOUNT;

		END;

		--- materialize Product, if it is in ZT-PARAM
		IF	@FactoryID = 'ZT' AND @ProductLineID LIKE 'PARAM%'
			BEGIN
				EXEC control.spParamTables @ProductID
			END
			
		COMMIT TRANSACTION POST_PDT;
	END TRY
	BEGIN CATCH
		DECLARE @Error_state INT = ERROR_STATE();
		SET @Comment = ERROR_MESSAGE();

		ROLLBACK TRANSACTION POST_PDT;		

		IF @Error_state <> 10 
		BEGIN
			SET @ResultCode = 500;		
			PRINT 'Rollback due to not executable command.';
		END
		ELSE IF @ResultCode IS NULL OR @ResultCode/100 = 2
		BEGIN
			SET @ResultCode = 500;	
		END;
	END CATCH

	EXEC dbo.sx_pf_pPOST_API_LogEntry @Username, @TransactUsername, @ProcedureName, @ParameterString, @EffectedRows, @ResultCode, @TimestampCall, @Comment;
	RETURN @ResultCode;
END
GO
 
ALTER AUTHORIZATION ON OBJECT ::dbo.sx_pf_POST_ProductDataTableValues TO OCTService;
GO

-- SET documentation variables      ***********************************************************************
DECLARE
     @level0name        NVARCHAR(255)  = N'dbo'					-- enter schema name of the table
    ,@level1name        NVARCHAR(255)  = N'sx_pf_POST_ProductDataTableValues'				-- enter procedure name
    ,@SX_Owner	        NVARCHAR(255)  = N'OCT.core'						-- enter owner name of the procedure from list (OCT.core, OCT.modules, Custom)
    ,@SX_Module			NVARCHAR(255)  = N'CORE'							-- enter module name as free text  (CORE,FIN, DEBKRED, HR, ...)
    ,@SX_ShipmentFlag	INT            = 1									-- 0 = Demo object - out of shipment process
																			
																			-- STANDARD OBJECTS
																			-- 1 = shiped from saxess standard without modification
																			-- 2 = shiped from saxess standard modified FOR customer from saxess
																			-- 3 = shiped from saxess standard modified FOR customer from partner
																			-- 4 = shiped from saxess standard modified FROM customer themself for own needs
																			-- CUSTOM OBJECTS
																			-- 10 = shiped from saxess as customer specific object
																			-- 11 = shiped from partner	as customer specific object
																			-- 12 = shiped from customer as own specific object

	,@SX_UserHint		NVARCHAR(2000) = N''								-- optional, fill if Procedure shall be offerend for end user (e.g. for Pivot / Datagrid usage)
                                                             
 
-- KEEP this default constants      *************************************************************************
DECLARE
     @name          NVARCHAR(255)   = N'MS_Description'
    ,@level0type    NVARCHAR(255)   = N'SCHEMA'
    ,@level1type    NVARCHAR(255)   = N'PROCEDURE'
    ,@level2type    NVARCHAR(255)   = N'PARAMETER'
    ,@level2name    NVARCHAR(255)   = N''
    ,@value         NVARCHAR(1000)  = N'';
 
    SET @value = @SX_Owner;
    EXEC sys.sp_addextendedproperty N'SX_Owner'			,@value,@level0type,@level0name,@level1type,@level1name;
 
    SET @value = @SX_Module;
    EXEC sys.sp_addextendedproperty N'SX_Module'		,@value,@level0type,@level0name,@level1type,@level1name;
 
    SET @value = @SX_ShipmentFlag;
    EXEC sys.sp_addextendedproperty N'SX_ShipmentFlag'	,@value,@level0type,@level0name,@level1type,@level1name;

	SET @value = @SX_UserHint;
    EXEC sys.sp_addextendedproperty N'SX_UserHint'		,@value,@level0type,@level0name,@level1type,@level1name;
 
 
-- SET documententation             *************************************************************************
 
 
-- SET Procedure documentation
    SET @value = N'POST Operation for all values of the ProductDataTable
         The method can be switched between incremental POST and full POST';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name;
 
    SET @level2name = N'@Username';
    SET @value      = N'Username';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
 
    SET @level2name = N'@ProductID';
    SET @value      = N'ProductID';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
 
    SET @level2name = N'@ProductLineID';
    SET @value      = N'ProductLineID';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
 
    SET @level2name = N'@FactoryID';
    SET @value      = N'FactoryID';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
 
    SET @level2name = N'@IsIncrementalValuesFlag';
    SET @value      = N'IsIncrementalValuesFlag';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
 
    SET @level2name = N'@ValuesInBracketsCommaSeparated';
    SET @value      = N'ValuesInBracketsCommaSeparated';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
 
GO


UPDATE system.tSettings SET ValueText = '5.10.20', ValueInt = 51020 WHERE SettingID = 'DBVersion'