/*
    Update-Schritte 2026.04.0:

    1. PresentationCODEs in planning.tTabs auf aktuellen Stand bringen

*/
GO

UPDATE planning.tTabs
SET PresentationCODE = 'ExternalWebsite'
WHERE PresentationCODE = 'HTML_URL';

UPDATE planning.tTabs
SET PresentationCODE = 'HTMLFromSQLQuery'
WHERE PresentationCODE = 'HTML_Static';

UPDATE planning.tTabs
SET PresentationCODE = 'Editor'
WHERE PresentationCODE = 'HTML_Editor';
GO


DROP PROCEDURE IF EXISTS agent.pDeleteIntegrationValues;
GO

/*
	Procedure to delete Integration Values 
	this is only an SWITCH procedure - it switches to an special procedure if defined or uses a default procedure

	Saxess Software GmbH
	Last modified: 09/2023 for OCT 5.10

	Testcall
		DECLARE @RC INT;
		EXEC @RC = agent.pDeleteIntegrationValues
			 @Username			= 'SQffL'
			,@Module			= 'FIN'
			,@CompanyKeys		= '1|1'
			,@PeriodFrom		= 202001
			,@PeriodTo			= 202012
			,@IsDeltaloadBool	= 0;
		SELECT @RC;

		SELECT TOP 10 * FROM system.tAPI_Log;

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

CREATE PROCEDURE agent.pDeleteIntegrationValues
    @Username        NVARCHAR(255)
  , @Module          NVARCHAR(255)
  , @CompanyKeys     NVARCHAR(MAX) -- delimited by ;
  , @PeriodFrom      INT
  , @PeriodTo        INT
  , @IsDeltaloadBool BIT = 0
WITH EXECUTE AS 'dbo'
AS
BEGIN
	BEGIN TRY
		-- Logging
		DECLARE @TimestampCall		DATETIME		= GETUTCDATE();
		DECLARE @ProcedureName		NVARCHAR(255)	= OBJECT_SCHEMA_NAME(@@PROCID) + N'.' + OBJECT_NAME(@@PROCID);
		DECLARE @AffectedRows		INT				= 0;
		DECLARE @ResultCode			INT				= 501;
		DECLARE @Comment			NVARCHAR(4000)	= N'';
		DECLARE @ParameterString	NVARCHAR(MAX)	= N'';
		DECLARE @TransactUsername	NVARCHAR(255)	= N'';
		EXEC system.spGET_ParameterString @ParameterString OUTPUT, 6, @Username, @Module, @CompanyKeys, @PeriodFrom, @PeriodTo, @IsDeltaloadBool;
		EXEC system.spGET_TransactUsername @TransactUsername OUTPUT, @Username;

		-- Variables
		DECLARE @SQLCommand		NVARCHAR(MAX)	= N'';
		DECLARE @CompanyKey		NVARCHAR(100)	= N'';
		DECLARE @DataSourceID	NVARCHAR(50)	= NULL;
		DECLARE @DataSourceKey	INT				= NULL;
		DECLARE @CompanyID		NVARCHAR(50)	= N'';
		DECLARE @ProcName		NVARCHAR(50)	= N'agent.pDeleteIntegrationValuesGeneric';

		-- Check for module specific delete stored procedure
		IF (OBJECT_ID('agent.pDeleteIntegrationValues' + @Module) IS NOT NULL)
		BEGIN
			SET @ProcName = 'agent.pDeleteIntegrationValues' + @Module;
		END

		-- Get single companies from CompanyKeys
		DECLARE CompanyKeyCursor CURSOR FOR
			SELECT value AS CompanyKey
			FROM STRING_SPLIT(@CompanyKeys, ';');

		OPEN CompanyKeyCursor;
		FETCH CompanyKeyCursor INTO @CompanyKey;

		WHILE @@FETCH_STATUS = 0
		BEGIN
			-- First value is DataSourceID
			SELECT TOP 1 @DataSourceID = value
			FROM STRING_SPLIT(@CompanyKey, '|');

            -- Get real DataSourceKey
            SET @DataSourceKey = NULL

            IF @DataSourceID = '0'
            BEGIN
                SET @DataSourceKey = 0
            END
            ELSE
            BEGIN
                SELECT @DataSourceKey = DataSourceKey
                FROM system.tDataSources
                WHERE DataSourceID = @DataSourceID;
            END

            IF @DataSourceKey IS NULL
            BEGIN
			    SET @Comment = 'DataSourceID ' + @DataSourceID + ' doesn''t exist.';
                EXEC system.spSEND_Message 'ERROR', @Comment
            END

			-- Last value is CompanyID
			SELECT @CompanyID = value
			FROM STRING_SPLIT(@CompanyKey, '|');
		
			-- Build command to call correct delete stored procedure dynamically
			SET @SQLCommand	= N'EXEC ' + @ProcName 
				+ N'  @DataSourceKey	= '		+ CAST(@DataSourceKey		AS NVARCHAR(10))
				+ N', @CompanyID		= N'''  + @CompanyID				+ N'''' 
				+ N', @PeriodFrom		= '		+ CAST(@PeriodFrom			AS NVARCHAR(6))
				+ N', @PeriodTo			= '		+ CAST(@PeriodTo			AS NVARCHAR(6)) 
				+ N', @Username			= N'''	+ @Username					+ N''''
				+ N', @IsDeltaloadBool	= ' 	+ CAST(@IsDeltaloadBool		AS NVARCHAR(1))
				+ N', @Module			= N'''  + @Module					+ N'''' 
				+ N';';
			
			-- Execute delete stored procedure
			EXEC (@SQLCommand);

			FETCH CompanyKeyCursor
			INTO @CompanyKey;
		END;
		
		CLOSE CompanyKeyCursor;
		DEALLOCATE CompanyKeyCursor;

		SET @ResultCode = 200;
	END TRY
	BEGIN CATCH
		SET @Comment = ERROR_MESSAGE();
		SET @ResultCode = 500;
	END CATCH;

	EXEC dbo.sx_pf_pPOST_API_LogEntry @Username, @TransactUsername, @ProcedureName, @ParameterString, @AffectedRows, @ResultCode, @TimestampCall, @Comment;

	IF @ResultCode >= 500
	    EXEC system.spSEND_Message 'ERROR', @Comment

	RETURN @ResultCode;
END;
GO


-- SET documentation variables      ***********************************************************************
DECLARE
     @level0name        NVARCHAR(255)  = N'agent'						 -- enter schema name of the table
    ,@level1name        NVARCHAR(255)  = N'pDeleteIntegrationValues'     -- 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'Procedure to delete integration values - switches between executing a special delete procedure or deleting generic over a default procedure.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name;
 
 
-- SET parameter documentation
	SET @level2name = N'@Username';
    SET @value      = N'User name';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

    SET @level2name = N'@Module';
    SET @value      = N'Name of the Module, e.g. FIN.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
 
    SET @level2name = N'@CompanyKeys';
    SET @value      = N'CompanyKeys as semikolon separated list, e.g. 1|22;1|33';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

	SET @level2name = N'@PeriodFrom';
    SET @value      = N'PeriodFrom, e.g. 202201';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

	SET @level2name = N'@PeriodTo';
    SET @value      = N'PeriodTo, e.g. 202212';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

	SET @level2name = N'@IsDeltaloadBool';
    SET @value      = N'Flag if Delta Load is used.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
GO

DROP PROCEDURE IF EXISTS agent.pPostBulkload;
GO

/*
	Procedure for POST Bulkload
 
	Saxess Software GmbH
	Last modified: 09/2023 for OCT 5.10

	Testcall
		DECLARE @RC INT;
		EXEC @RC = agent.pPostBulkload
			 @Username = 'SQL'
			,@Modules = 'FIN'
			,@CompanyKeys = '1|1';
		SELECT @RC;

	Testcall Documentation
		SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'agent', 'PROCEDURE', 'pPostBulkload',NULL,NULL)
		UNION ALL
		SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'agent', 'PROCEDURE', 'pPostBulkload','PARAMETER',NULL)
*/
 
CREATE PROCEDURE agent.pPostBulkload
    @Username     NVARCHAR(255)
  , @Modules      NVARCHAR(MAX)
  , @CompanyKeys  NVARCHAR(MAX)
WITH EXECUTE AS 'dbo'
AS
BEGIN
    BEGIN TRY
		-- Logging
		DECLARE @TimestampCall		DATETIME		= GETUTCDATE();
		DECLARE @ProcedureName		NVARCHAR(255)	= OBJECT_SCHEMA_NAME(@@PROCID) + N'.' + OBJECT_NAME(@@PROCID);
		DECLARE @AffectedRows		INT				= 0;
		DECLARE @ResultCode			INT				= 501;
		DECLARE @Comment			NVARCHAR(4000)	= N'';
		DECLARE @ParameterString	NVARCHAR(MAX)	= N'';
		DECLARE @TransactUsername	NVARCHAR(255)	= N'';
		EXEC system.spGET_ParameterString @ParameterString OUTPUT, 3, @Username, @Modules, @CompanyKeys;
		EXEC system.spGET_TransactUsername @TransactUsername OUTPUT, @Username;

		-- Variables
		DECLARE @Module		    NVARCHAR(255);
		DECLARE @ProcName	    NVARCHAR(50);
        DECLARE @CompanyKey     NVARCHAR(255);
        DECLARE @CompanyKeysTmp NVARCHAR(MAX) = '';
        DECLARE @CompanyID      NVARCHAR(255);
        DECLARE @DataSourceID   NVARCHAR(50);
        DECLARE @DataSourceKey  INT;

        -- Get single companies from CompanyKeys
		DECLARE CompanyKeyCursor CURSOR FOR
			SELECT value AS CompanyKey
			FROM STRING_SPLIT(@CompanyKeys, ';');

		OPEN CompanyKeyCursor;
		FETCH CompanyKeyCursor INTO @CompanyKey;

		WHILE @@FETCH_STATUS = 0
		BEGIN
			-- First value is DataSourceID
			SELECT TOP 1 @DataSourceID = value
			FROM STRING_SPLIT(@CompanyKey, '|');

            -- Get real DataSourceKey
            SET @DataSourceKey = NULL

            IF @DataSourceID = '0'
            BEGIN
                SET @DataSourceKey = 0
            END
            ELSE
            BEGIN
                SELECT @DataSourceKey = DataSourceKey
                FROM system.tDataSources
                WHERE DataSourceID = @DataSourceID;
            END

            IF @DataSourceKey IS NULL
            BEGIN
			    SET @Comment = 'DataSourceID ' + @DataSourceID + ' doesn''t exist.';
                EXEC system.spSEND_Message 'ERROR', @Comment
            END

			-- Last value is CompanyID
			SELECT @CompanyID = value
			FROM STRING_SPLIT(@CompanyKey, '|');
		
			SET @CompanyKeysTmp = @CompanyKeysTmp + CAST(@DataSourceKey AS NVARCHAR(50)) + '|' + @CompanyID + ';'

			FETCH CompanyKeyCursor
			INTO @CompanyKey;
		END;
		
		CLOSE CompanyKeyCursor;
		DEALLOCATE CompanyKeyCursor;

        SET @CompanyKeys = LEFT(@CompanyKeysTmp, LEN(@CompanyKeysTmp) - 1);

        -- Create cursor to iterate over @Modules
        DECLARE ModuleCursor CURSOR FOR
            SELECT value AS Module
            FROM STRING_SPLIT(@Modules, ';')
            WHERE value <> '';

        -- Iterate over ModuleCursor
        OPEN ModuleCursor;
        FETCH ModuleCursor
        INTO @Module;

        WHILE @@FETCH_STATUS = 0
        BEGIN
            -- Execute PostBulkload stored procedure for selected module
            IF (OBJECT_ID('agent.pPostBulkload' + @Module) IS NOT NULL)
            BEGIN
                SET @ProcName = N'agent.pPostBulkload' + @Module;
                EXEC @ResultCode = @ProcName @Username = @Username, @CompanyKeys = @CompanyKeys;
            END;

            -- Execute maintenance procedure to create JSON views based on CustomValuesJSON column.
            EXEC @ResultCode = agent.pMaintenanceJSONViews @Username = @Username, @Module = @Module;

            -- Recreate result views to include new or deleted CustomValuesJSON columns.
            EXEC @ResultCode = agent.pMaintenanceRecreateViews @Username = @Username, @Module = @Module;

            -- Reorganize all columnstore indexes of given module.
            EXEC @ResultCode = agent.pMaintenanceIndexes @Username = @Username, @Module = @Module;
            
            FETCH ModuleCursor
            INTO @Module;
        END;

        CLOSE ModuleCursor;
        DEALLOCATE ModuleCursor;

        RETURN @ResultCode;
	END TRY
	BEGIN CATCH
		SET @ResultCode = 500;
		SET @Comment = ERROR_MESSAGE();
	END CATCH;
	
	EXEC dbo.sx_pf_pPOST_API_LogEntry @Username, @TransactUsername, @ProcedureName, @ParameterString, @AffectedRows, @ResultCode, @TimestampCall, @Comment;

	IF @ResultCode >= 500
	    EXEC system.spSEND_Message 'ERROR', @Comment

    RETURN @ResultCode;
END;
GO


-- SET documentation variables      ***********************************************************************
DECLARE
     @level0name        NVARCHAR(255)  = N'agent'			    -- enter schema name of the table
    ,@level1name        NVARCHAR(255)  = N'pPostBulkload'     -- 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'Procedure for POST Bulkload';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name;
 
 
-- SET parameter documentation
    SET @level2name = N'@Username';
    SET @value      = N'Username';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
 
    SET @level2name = N'@Modules';
    SET @value      = N'Modules';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

	SET @level2name = N'@CompanyKeys';
    SET @value      = N'CompanyKeys';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
GO


DROP PROCEDURE IF EXISTS agent.pSync;
GO

/*
	Procedure to start the data providing process
 
	Saxess Software GmbH
	Last modified: 09/2023 for OCT 5.10

	Testcall
		DECLARE @RC INT;
		EXEC @RC = agent.pSync
			 @Username = 'SQL'
			,@Module = 'FIN'
			,@CompanyKeys = '1|1';
		SELECT @RC;

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

CREATE PROCEDURE agent.pSync
    @Username     NVARCHAR(255)
  , @Module       NVARCHAR(255)
  , @CompanyKeys  NVARCHAR(MAX)
  , @FullRebuild  BIT           = 0
WITH EXECUTE AS 'dbo'
AS
BEGIN
	BEGIN TRY
		-- Logging
		DECLARE @TimestampCall		DATETIME		= GETUTCDATE();
		DECLARE @ProcedureName		NVARCHAR(255)	= OBJECT_SCHEMA_NAME(@@PROCID) + N'.' + OBJECT_NAME(@@PROCID);
		DECLARE @AffectedRows		INT				= 0;
		DECLARE @ResultCode			INT				= 501;
		DECLARE @Comment			NVARCHAR(4000)	= N'';
		DECLARE @ParameterString	NVARCHAR(MAX)	= N'';
		DECLARE @TransactUsername	NVARCHAR(255)	= N'';
		EXEC system.spGET_ParameterString @ParameterString OUTPUT, 4, @Username, @Module, @CompanyKeys, @FullRebuild;
		EXEC system.spGET_TransactUsername @TransactUsername OUTPUT, @Username;

		-- Variables
		DECLARE @SQLCommand	NVARCHAR(255);
        DECLARE @CompanyKey     NVARCHAR(255);
        DECLARE @CompanyKeysTmp NVARCHAR(MAX) = '';
        DECLARE @CompanyID      NVARCHAR(255);
        DECLARE @DataSourceID   NVARCHAR(50);
        DECLARE @DataSourceKey  INT;

        -- Get single companies from CompanyKeys
        IF @FullRebuild = 0
        BEGIN
            DECLARE CompanyKeyCursor CURSOR FOR
                SELECT value AS CompanyKey
                FROM STRING_SPLIT(@CompanyKeys, ';');

            OPEN CompanyKeyCursor;
            FETCH CompanyKeyCursor INTO @CompanyKey;

            WHILE @@FETCH_STATUS = 0
            BEGIN
                -- First value is DataSourceID
                SELECT TOP 1 @DataSourceID = value
                FROM STRING_SPLIT(@CompanyKey, '|');

                -- Get real DataSourceKey
                SET @DataSourceKey = NULL

                IF @DataSourceID = '0'
                BEGIN
                    SET @DataSourceKey = 0
                END
                ELSE
                BEGIN
                    SELECT @DataSourceKey = DataSourceKey
                    FROM system.tDataSources
                    WHERE DataSourceID = @DataSourceID;
                END

                IF @DataSourceKey IS NULL
                BEGIN
                    SET @Comment = 'DataSourceID ' + @DataSourceID + ' doesn''t exist.';
                    EXEC system.spSEND_Message 'ERROR', @Comment
                END

                -- Last value is CompanyID
                SELECT @CompanyID = value
                FROM STRING_SPLIT(@CompanyKey, '|');
            
                SET @CompanyKeysTmp = @CompanyKeysTmp + CAST(@DataSourceKey AS NVARCHAR(50)) + '|' + @CompanyID + ';'

                FETCH CompanyKeyCursor
                INTO @CompanyKey;
            END;
            
            CLOSE CompanyKeyCursor;
            DEALLOCATE CompanyKeyCursor;

            SET @CompanyKeys = LEFT(@CompanyKeysTmp, LEN(@CompanyKeysTmp) - 1);
        END

		-- Protect input parameters
		SET @Module = dbo.sx_pf_pProtectString(@Module);

		-- Update MappingPriority in global.tCompanies
		WITH Companies AS (
			SELECT
				*
			  , ROW_NUMBER() OVER (PARTITION BY COALESCE(NULLIF(CompanyIDResult, ''), CompanyID) ORDER BY MappingPriority) AS NewPriority
			FROM global.tCompanies
		)
		UPDATE Companies
		SET MappingPriority = NewPriority;

		-- Execute pSync stored procedure for the selected module
		SET @SQLCommand = N'agent.pSync' + @Module;
		IF (OBJECT_ID(@SQLCommand) IS NOT NULL)
		BEGIN
			EXEC @ResultCode = @SQLCommand @Username = @Username, @CompanyKeys = @CompanyKeys, @FullRebuild = @FullRebuild;
		END
		ELSE
		BEGIN
			SET @Comment = @SQLCommand + ' doesn''t exist'
			RAISERROR(@Comment, 16, 10);
		END;
	END TRY
	BEGIN CATCH
		SET @ResultCode = 500;
		SET @Comment = ERROR_MESSAGE();
	END CATCH;
	
	EXEC dbo.sx_pf_pPOST_API_LogEntry @Username, @TransactUsername, @ProcedureName, @ParameterString, @AffectedRows, @ResultCode, @TimestampCall, @Comment;

	IF @ResultCode >= 500
	    RAISERROR(@Comment, 16, 10);

    RETURN @ResultCode;
END
GO


-- SET documentation variables      ***********************************************************************
DECLARE
     @level0name        NVARCHAR(255)  = N'agent'			    -- enter schema name of the table
    ,@level1name        NVARCHAR(255)  = N'pSync'			    -- 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'Procedure to start the data providing process';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name;
 
 
-- SET parameter documentation
    SET @level2name = N'@Username';
    SET @value      = N'Username';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
 
    SET @level2name = N'@Module';
    SET @value      = N'Module';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

	SET @level2name = N'@CompanyKeys';
    SET @value      = N'CompanyKeys';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
GO


  
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
			);
        DROP TABLE IF EXISTS #Values7;
		CREATE TABLE #Values7  
			(
				 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;

		-- Abfangen von End-Kommas im Inputspalten
		INSERT INTO #Values7
		SELECT 
			ValueSeriesID 
			,TimeID
			,ValueFormula
			,ValueInt
			,IIF(RIGHT(ValueText,1)<> '''',ValueText + '''',Valuetext) AS ValueText
			,IIF(REPLACE(ValueComment,CHAR(29),'') = '''''''','''''',ValueComment) AS ValueComment
		FROM #Values6

		-- Replace Quotes from all strings (all except TimeID)
		UPDATE #Values7
		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)
			-- Eingabe eines Kommas hinterlässt hier nur ein Hochkommma
			,ValueText		= IIF(LEN(ValueText)>1,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 #Values7
			SET 
				ValueText =REPLACE(REPLACE(ValueText,CHAR(13),''),CHAR(10),'');

		IF @DebugFlag = 1 
		BEGIN
			IF OBJECT_ID('tempdb..##Debug_Values7') IS NOT NULL DROP TABLE ##Debug_Values7;
				SELECT * INTO ##Debug_Values7 FROM #Values7;
		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 #Values7;

		-- 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'';

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

			-- Insert all Values
			INSERT INTO planning.tfValues (
				 ValueSeriesKey
				,ProductKey
				,ProductLineKey
				,FactoryKey
				,ProductID
				,ProductLineID
				,FactoryID
				,ValueSeriesID
				,TimeID
				,ValueFormula
				,ValueInt
				,ValueText
				,ValueComment
			)
			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 FROM #ValueMergeTable
			WHERE 
				ProcessCode	  = N'SENDED VALUES' 
				AND [IsNumeric] = 1
				AND IsFormula = 1 
				AND ValueInt = N''
                AND ValueFormula = 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 (
				 ValueSeriesKey
				,ProductKey
				,ProductLineKey
				,FactoryKey
				,ProductID
				,ProductLineID
				,FactoryID
				,ValueSeriesID
				,TimeID
				,ValueFormula
				,ValueInt
				,ValueText
				,ValueComment
			)
			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 db_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



DROP PROCEDURE IF EXISTS global.spDELETE_Company;
GO

/*
	Procedure to delete a company
		- in Dimension global tCompanies
		- generic serch over tables with Column CompanyKey
		- TODO: in all Pipeline Steps
 
	Gerd Tautenhahn for Saxess Software GmbH
	Last modified: 10/2022 for OCT 5.8
 
	Testcall Procedure
		DECLARE  @RC INT;
		EXEC	 @RC = global.spDELETE_Company
				 @Username		= 'SQL'
				,@DataSourceID	= '0'
				,@CompanyID		= 'A'
		PRINT    @RC

	SELECT * FROM global.tCompanies;
	SELECT * FROM system.tDataSources;

	Testcall Documentation
		SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'global', 'PROCEDURE', 'spDELETE_Company',NULL,NULL)
		UNION ALL
		SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'global', 'PROCEDURE', 'spDELETE_Company','PARAMETER',NULL)
*/
 
CREATE PROCEDURE global.spDELETE_Company
    (
         @Username				NVARCHAR(255)
        ,@DataSourceID			NVARCHAR(50)
		,@CompanyID				NVARCHAR(50)
    )

WITH EXECUTE AS 'dbo'  -- to access tables in system schema

AS 
	BEGIN	

		-- Standard declaration for logging
		DECLARE 
				 @ProcedureName		NVARCHAR(255)	= OBJECT_SCHEMA_NAME(@@PROCID) + N'.' + OBJECT_NAME(@@PROCID)

				,@ParameterString	NVARCHAR(MAX)	= N''''					--set Strings inside in single quotes (N''','''), Numbers without strings inside without quotes (N','), end list with '''' in case of string or '' in case of number on last position
				+ ISNULL(@Username			,N'NULL')	+ N''','''											 
				+ ISNULL(@DataSourceID		,N'NULL')	+ N''','''
				+ ISNULL(@CompanyID			,N'NULL')	+ N''''

				,@EffectedRows		INT				= 0			
				,@ResultCode		INT				= 501				
				,@TimestampCall		DATETIME		= GETUTCDATE()
				,@Comment			NVARCHAR(2000)	= N''
				,@TransactUsername	NVARCHAR(255)	= N''
				,@StartTime			DATETIME2		= SysUTCDateTime()
				,@Message			NVARCHAR(2000)	= N''
				,@Tablename			NVARCHAR(2000)  = N''
				,@SQL				NVARCHAR(4000)	= N''
                ,@DataSourceKey     INT;

		-- NULL Protection for all Input parameters
		IF @Username				IS NULL SET @Username			= N'';
		IF @DataSourceID			IS NULL SET @DataSourceID		= N'';
		IF @CompanyID				IS NULL SET @CompanyID			= N'';


		-- START TRANSACTION			***********************************************************************************
		BEGIN TRY
			BEGIN TRANSACTION spDELETE_Company
				-- check transaction user existence
				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;

				-- check existence of IDs
                SELECT @DataSourceKey = DataSourceKey
                FROM system.tDataSources
                WHERE DataSourceID = @DataSourceID;
                IF @DataSourceID = '0' SET @DataSourceKey = 0

				IF @DataSourceKey IS NULL
					BEGIN
						SET @ResultCode = 404;
						SET @Message = CONCAT('DatsourceID ',@DataSourceID,' don`t exits');
						PRINT @Message;
						RAISERROR(@Message, 16, 10);
					END

				IF NOT EXISTS (
					SELECT CompanyID FROM global.tCompanies WHERE DataSourceKey = @DataSourceKey AND CompanyID = @CompanyID
					) 
					BEGIN
						SET @ResultCode = 404;
						SET @Message = CONCAT('CompanyID ',@CompanyID,' don`t exits');
						PRINT @Message;
						RAISERROR(@Message, 16, 10);
					END
				

		
				-- Generic delete over dynamic SQL is possible in all OCT schema except "system" in default
				-- Generic delete is still done over CompanyKey
				-- GET all Non-Core Tables where the column CompanyKey is used (usually result schema) and delete the Values for this ComanpanyKey
				-- The Company Key is an combinded Key "DatasourceID | CompanyID"
				-- some are deleted the second time, as they have the Datasource Key also !!

				PRINT '';
				PRINT 'Starting generic Delete Process for all Tables outside system containing the Column "CompanyKey" which is a concated Key with the Datasource.';
				PRINT '#####################################################################################################';
				PRINT '';
				DECLARE TableCursor CURSOR FOR
					SELECT      
							CONCAT(infs.TABLE_SCHEMA COLLATE DATABASE_DEFAULT,N'.',infs.TABLE_NAME COLLATE DATABASE_DEFAULT)	AS Tablename

					FROM INFORMATION_SCHEMA.COLUMNS infs
						-- Determination Objekttype
						LEFT JOIN 
								(
								SELECT 
									 Name					COLLATE DATABASE_DEFAULT AS Objektname
									,SCHEMA_NAME(Schema_id) COLLATE DATABASE_DEFAULT AS Schemaname
									,type_desc				COLLATE DATABASE_DEFAULT AS Objekttyp
								FROM sys.objects
								) syo ON 
											syo.Schemaname = infs.TABLE_SCHEMA
										AND syo.Objektname = infs.TABLE_NAME

				WHERE 
							syo.Objekttyp		= N'USER_TABLE'
						AND infs.COLUMN_NAME	= N'CompanyKey'
						-- ToDo: Determination not a Core table should be moved to metadata 
						AND infs.TABLE_SCHEMA  <> N'system'
						AND CONCAT(infs.TABLE_SCHEMA,N'.',infs.TABLE_NAME) <> N'global.tCompanies';
				OPEN TableCursor;
				FETCH TableCursor
				INTO @Tablename;
					IF @@CURSOR_ROWS = 0
						BEGIN
							PRINT N'No Tables found for generic deletion over Column CompanyKey'
						END
				WHILE @@FETCH_STATUS = 0
					BEGIN
						-- clear variables
						SET @SQL = N'';
						SET @SQL = CONCAT(N'DELETE FROM ', @Tablename, N' WHERE CompanyKey LIKE ''', @DataSourceID,N'|',@CompanyID,''';') 


						PRINT CONCAT(N'Delete all Rows with DatasourceID ', @DataSourceID,' and CompanyID ',@CompanyID, N' in Table ',@Tablename)
						PRINT @SQL
						EXEC (@SQL);

						FETCH TableCursor
						INTO @Tablename;
					END;
				CLOSE TableCursor;
				DEALLOCATE TableCursor;

				PRINT ''
				PRINT 'End of generic delition'
				PRINT  '######################################################################'
				PRINT ''

				PRINT '';
				PRINT 'Starting Delete for Core Data'
				PRINT '#####################################################################################################';
				PRINT '';
								
				-- PIPELINES - Company selection is deleted in all steps, but not the steps itself

				-- system.tPipelineSteps  - TODO 
					-- Datasource must be extracted from JSON and filtered on
					-- Company ID must be extracted from different JSON field
			
	
				PRINT CONCAT(N'Starting delete',N' (started after ', DateDiff(millisecond, @StartTime, SysUTCDateTime()),N'ms)');
				
				DELETE 
				FROM global.tCompanies
				WHERE
						DataSourceKey	= @DataSourceKey
					AND CompanyID		= @CompanyID;

				SET @EffectedRows = @@ROWCOUNT;
				SET @ResultCode = 200;


				PRINT CONCAT(N'Transaktion finished',N' (after ', DateDiff(millisecond, @StartTime, SysUTCDateTime()),N'ms)')
			COMMIT TRANSACTION spDELETE_Company
		END TRY

		-- START CATCH				***********************************************************************************
		BEGIN CATCH
			DECLARE @Error_state INT = ERROR_STATE();
			SET @Comment = ERROR_MESSAGE();
			
			ROLLBACK TRANSACTION spDELETE_Company	

			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 sx_pf_pPOST_API_LogEntry @Username, @TransactUsername, @ProcedureName, @ParameterString, @EffectedRows, @ResultCode, @TimestampCall, @Comment;
		RETURN @ResultCode;
	END
 
GO

-- NO GRANT - as EXECUTE AS 'dbo'

-- SET documentation variables      ***********************************************************************
DECLARE
     @level0name        NVARCHAR(255)   = N'global'    -- enter schema name of the table
    ,@level1name        NVARCHAR(255)   = N'spDELETE_Company'         -- 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'Procedure to delete a company - as company and all data which are accociated with this company.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name;
 
 
-- optional SET parameter documentation (only for Core / Standardmodules)
    SET @level2name = N'@DataSourceID';
    SET @value      = N'ID of the Datasource to delete.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
 
    SET @level2name = N'@CompanyID';
    SET @value      = N'ID of the company to delete';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

	GO


DROP PROCEDURE IF EXISTS global.spGET_Companies;
GO

/*
	Procedure to get one or all companies of an Datasource or for all Datasources
	 - use CompanyID = '' to get all companies
	 - use DatasourceID = '' to get the companies of all Datasources

	Saxess Software GmbH
	Last modified: 02/2024 for OCT 5.10
 
	Testcall Procedure
		DECLARE  @RC INT;
		EXEC	 @RC = global.spGET_Companies
				 @Username		= 'SQL'
				,@DataSourceID	= ''
				,@CompanyID		= ''
		PRINT    @RC

	Testcall Documentation
		SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'global', 'PROCEDURE', 'spGET_Companies',NULL,NULL)
		UNION ALL
		SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'global', 'PROCEDURE', 'spGET_Companies','PARAMETER',NULL)
*/
 
CREATE PROCEDURE global.spGET_Companies (
    @Username     NVARCHAR(255)
  , @DataSourceID NVARCHAR(50) = ''
  , @CompanyID    NVARCHAR(50) = ''
)
WITH EXECUTE AS 'dbo'
AS
BEGIN
    BEGIN TRY
        -- Logging
        DECLARE @TimestampCall DATETIME = GETUTCDATE();
        DECLARE @ProcedureName NVARCHAR(255) = OBJECT_SCHEMA_NAME(@@PROCID) + N'.' + OBJECT_NAME(@@PROCID);
        DECLARE @AffectedRows INT = 0;
        DECLARE @ResultCode INT = 501;
        DECLARE @Comment NVARCHAR(4000) = N'';
        DECLARE @ParameterString NVARCHAR(MAX) = N'';
        DECLARE @TransactUsername NVARCHAR(255) = N'';
        DECLARE @DataSourceKey  INT;
        EXEC system.spGET_ParameterString @ParameterString OUTPUT, 3, @Username, @DataSourceID, @CompanyID;
        EXEC system.spGET_TransactUsername @TransactUsername OUTPUT, @Username;

		-- NULL & Content Protection
		SET @Username = COALESCE(@Username, N'');
		SET @DataSourceID = dbo.sx_pf_pProtectID(COALESCE(@DataSourceID, N''));
		SET @CompanyID = dbo.sx_pf_pProtectString(COALESCE(@CompanyID, N''));
		
		-- DataSourceID exists?
        SELECT @DataSourceKey = DataSourceKey
        FROM system.tDataSources
        WHERE DataSourceID = @DataSourceID;
        IF @DataSourceID = '0' SET @DataSourceKey = 0

		IF @DataSourceKey IS NULL AND @DataSourceID <> ''
		BEGIN
			SET @Comment = 'DataSourceID ' + @DataSourceID + ' doesn''t exist.';
			EXEC system.spSEND_Message 'ERROR', @Comment;
		END

		-- CompanyID exists?
		IF NOT EXISTS(SELECT CompanyID FROM global.tCompanies WHERE DataSourceKey = @DataSourceKey AND CompanyID = @CompanyID) AND @CompanyID <> ''
		BEGIN
			SET @Comment = 'CompanyID ' + @CompanyID + ' doesn''t exist in DataSourceID ' + @DataSourceID + '.';
			EXEC system.spSEND_Message 'ERROR', @Comment;
		END

		-- Data output
        SELECT
            d.DataSourceID                                                                                   AS DataSourceID
          , c.CompanyID                                                                                      AS CompanyID
          , c.CompanyName                                                                                    AS CompanyName
          , c.Description                                                                                    AS Description
          , c.FinancialYearBegin                                                                             AS FinancialYearBegin
          , c.ChartsOfAccountsID                                                                             AS ChartsOfAccountsID
          , c.AccountLength                                                                                  AS AccountLength
          , c.Currency                                                                                       AS Currency
          , c.CustomValuesJSON                                                                               AS CustomValuesJSON
          , c.CustomValuesJSONUser                                                                           AS CustomValuesJSONUser
          , c.OwnerCODE                                                                                      AS OwnerCODE
          , CONCAT(COALESCE(d.DataSourceName, 'OCT'), N' (ID ', CAST(d.DataSourceID AS NVARCHAR(50)), N')')  AS DataSource
          , c.CompanyIDResult                                                                                AS CompanyIDResult
          , c.MappingPriority                                                                                AS MappingPriority
        FROM global.tCompanies   AS c
        LEFT JOIN system.tDataSources AS d
        ON d.DataSourceKey = c.DataSourceKey
        WHERE
            (c.DataSourceKey = @DataSourceKey OR @DataSourceID = '' OR @DataSourceKey = 0)
            AND (c.CompanyID = @CompanyID OR @CompanyID = '');

        SET @AffectedRows = @@ROWCOUNT;
	
		SET @ResultCode = 200;
	END TRY
	BEGIN CATCH
		SET @ResultCode = 500;
		SET @Comment = ERROR_MESSAGE();
	END CATCH
	
	EXEC dbo.sx_pf_pPOST_API_LogEntry @Username, @TransactUsername, @ProcedureName, @ParameterString, @AffectedRows, @ResultCode, @TimestampCall, @Comment;
	
	IF @ResultCode >= 500
	    EXEC system.spSEND_Message 'ERROR', @Comment;

    RETURN @ResultCode;
END;
GO
 
-- SET documentation variables      ***********************************************************************
DECLARE
     @level0name        NVARCHAR(255)   = N'global'    -- enter schema name of the table
    ,@level1name        NVARCHAR(255)   = N'spGET_Companies'         -- 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'Procedure to get one or all companies of a datasource';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name;
 
 
-- optional SET parameter documentation (only for Core / Standardmodules)
    SET @level2name = N'@DataSourceID';
    SET @value      = N'optional - ID of an Datasource, send '''' to get all Datasources.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
 
    SET @level2name = N'@CompanyID';
    SET @value      = N'optional - ID of an Company, send '''' to get all Companies.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
GO


DROP PROCEDURE IF EXISTS global.spPOST_Company;
GO

/*
	Procedure to POST a Company, it is called if
		- Companies are created by query the from a sources system
		- Company Properties are edited by the Company edit GUI
		- all optional Parameters are keept properties if skip during sended or sended as NULL
		- Companies for Datasource 0 can be created even that this datasource don't exists

    Saxess Software GmbH
 
	Testcall Procedure
		DECLARE  @RC INT;
		EXEC	 @RC = global.spPOST_Company
				 @Username				= 'SQL'
				,@DataSourceID			= '0'
				,@CompanyID				= 'B'
				,@CompanyName			= 'C GmbH'
				,@Description			= 'Das ist die C GmbH'
				,@FinancialYearBegin	= 1
				,@ChartsOfAccountsID	= 'SKR04'
				,@AccountLength			= 4
				,@Currency				= 'EUR'
				,@CustomValuesJSON		= '{}'
				,@CustomValuesJSONUser	= '{}'
				,@OwnerCODE				= 'INTEGRATION'
				,@CompanyIDResult       = ''
				,@MappingPriority       = 1
                ,@RecalculatePriority   = 1

		PRINT    @RC

		SELECT * FROM global.tCompanies;
		SELECT * FROM system.tDataSources;

		DELETE FROM global.tCompanies WHERE CompanyID = 'A';


	Testcall Documentation
		SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'global', 'PROCEDURE', 'spPOST_Company',NULL,NULL)
		UNION ALL
		SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'global', 'PROCEDURE', 'spPOST_Company','PARAMETER',NULL)
*/
 
CREATE PROCEDURE global.spPOST_Company (
    @Username             NVARCHAR(255)
  , @DataSourceID         NVARCHAR(50)
  , @CompanyID            NVARCHAR(50)
  , @CompanyName          NVARCHAR(255)  = NULL
  , @Description          NVARCHAR(2000) = NULL
  , @FinancialYearBegin   INT            = NULL
  , @ChartsOfAccountsID   NVARCHAR(50)   = NULL
  , @AccountLength        INT            = NULL
  , @Currency             NVARCHAR(50)   = NULL
  , @CustomValuesJSON     NVARCHAR(MAX)  = NULL
  , @CustomValuesJSONUser NVARCHAR(MAX)  = NULL
  , @OwnerCODE            NVARCHAR(50)   = NULL
  , @CompanyIDResult      NVARCHAR(50)   = NULL
  , @MappingPriority      INT            = NULL
  , @RecalculatePriority  BIT            = 1
)
WITH EXECUTE AS 'dbo'
AS
BEGIN
    BEGIN TRY
        -- Logging
        DECLARE @TimestampCall DATETIME = GETUTCDATE();
        DECLARE @ProcedureName NVARCHAR(255) = OBJECT_SCHEMA_NAME(@@PROCID) + N'.' + OBJECT_NAME(@@PROCID);
        DECLARE @AffectedRows INT = 0;
        DECLARE @ResultCode INT = 501;
        DECLARE @Comment NVARCHAR(4000) = N'';
        DECLARE @ParameterString NVARCHAR(MAX) = N'';
        DECLARE @TransactUsername NVARCHAR(255) = N'';
        DECLARE @DataSourceKey  INT;
        EXEC system.spGET_ParameterString @ParameterString OUTPUT, 15, @Username, @DataSourceID, @CompanyID, @CompanyName, @Description, @FinancialYearBegin, @ChartsOfAccountsID, @AccountLength, @Currency, @CustomValuesJSON, @CustomValuesJSONUser, @OwnerCODE, @CompanyIDResult, @MappingPriority, @RecalculatePriority;
        EXEC system.spGET_TransactUsername @TransactUsername OUTPUT, @Username;

		-- NULL Protection
		SET @Username = COALESCE(@Username, N'');
		SET @DataSourceID = COALESCE(@DataSourceID, N'');
		SET @CompanyID = COALESCE(@CompanyID, N'');

		-- DataSourceID exists?
        SELECT @DataSourceKey = DataSourceKey
        FROM system.tDataSources
        WHERE DataSourceID = @DataSourceID;
        IF @DataSourceID = '0' SET @DataSourceKey = 0

		IF @DataSourceKey IS NULL
		BEGIN
			SET @Comment = 'DataSourceID ' + @DataSourceID + ' doesn''t exist.';
			EXEC system.spSEND_Message 'ERROR', @Comment;
		END

		BEGIN TRANSACTION
			-- Remove linebreaks from input
			SET @CompanyName = REPLACE(REPLACE(@CompanyName, CHAR(10), ''), CHAR(13), '')
			SET @Description = REPLACE(REPLACE(@Description, CHAR(10), ''), CHAR(13), '')
			SET @ChartsOfAccountsID = REPLACE(REPLACE(@ChartsOfAccountsID, CHAR(10), ''), CHAR(13), '')
			SET @Currency = REPLACE(REPLACE(@Currency, CHAR(10), ''), CHAR(13), '')
			SET @CustomValuesJSON = REPLACE(REPLACE(@CustomValuesJSON, CHAR(10), ''), CHAR(13), '')
			SET @CompanyIDResult = REPLACE(REPLACE(@CompanyIDResult, CHAR(10), ''), CHAR(13), '')

		    -- Update MappingPriority in existing companies
			IF @CompanyIDResult IS NOT NULL AND @MappingPriority IS NOT NULL AND @RecalculatePriority = 1
			BEGIN
                UPDATE global.tCompanies
                SET MappingPriority = MappingPriority + 1
                WHERE
                    DataSourceKey = @DataSourceKey
                    AND CompanyIDResult = @CompanyIDResult
                    AND MappingPriority >= @MappingPriority;
			END

			-- Update existing data
            UPDATE global.tCompanies
            SET
                CompanyName = COALESCE(@CompanyName, CompanyName)
              , Description = COALESCE(@Description, Description)
              , FinancialYearBegin = COALESCE(@FinancialYearBegin, FinancialYearBegin)
              , ChartsOfAccountsID = COALESCE(@ChartsOfAccountsID, ChartsOfAccountsID)
              , AccountLength = COALESCE(@AccountLength, AccountLength)
              , Currency = COALESCE(@Currency, Currency)
              , CustomValuesJSON = COALESCE(@CustomValuesJSON, CustomValuesJSON)
              , CustomValuesJSONUser = COALESCE(@CustomValuesJSONUser, CustomValuesJSONUser)
              , OwnerCODE = COALESCE(@OwnerCODE, OwnerCODE)
              , CompanyIDResult = COALESCE(@CompanyIDResult, CompanyIDResult)
              , MappingPriority = COALESCE(@MappingPriority, MappingPriority)
            WHERE
                DataSourceKey = @DataSourceKey
                AND CompanyID = @CompanyID;

			SET @AffectedRows = @@ROWCOUNT;

			-- No rows updated because CompanyID doesn't exist -> insert new row
			IF @AffectedRows = 0
			BEGIN
			    INSERT INTO GLOBAL.tCompanies (
                    DataSourceKey
                  , CompanyID
                  , CompanyName
                  , Description
                  , FinancialYearBegin
                  , ChartsOfAccountsID
                  , AccountLength
                  , Currency
                  , CustomValuesJSON
                  , CustomValuesJSONUser
                  , OwnerCODE
                  , CompanyIDResult
                  , MappingPriority
                )
                VALUES (
                    @DataSourceKey
                  , @CompanyID
                  , COALESCE(@CompanyName, 'UNKNOWN')
                  , COALESCE(@Description, '')
                  , COALESCE(@FinancialYearBegin, 1)
                  , COALESCE(@ChartsOfAccountsID, '')
                  , COALESCE(@AccountLength, 4)
                  , COALESCE(@Currency, '')
                  , COALESCE(@CustomValuesJSON, '')
                  , COALESCE(@CustomValuesJSONUser, '')
                  , COALESCE(@OwnerCODE, '')
                  , COALESCE(@CompanyIDResult, '')
                  , COALESCE(@MappingPriority, 1)
				);

				SET @AffectedRows = @@ROWCOUNT;
			END;

			-- Update MappingPriority to prevent duplicates
            IF @RecalculatePriority = 1
            BEGIN
                WITH Companies AS (
                    SELECT
                        *
                    , ROW_NUMBER() OVER (PARTITION BY COALESCE(NULLIF(CompanyIDResult, ''), CompanyID) ORDER BY MappingPriority) AS NewPriority
                    FROM global.tCompanies
                )
                UPDATE Companies
                SET MappingPriority = NewPriority;
            END
		COMMIT TRANSACTION

		SET @ResultCode = 200;
	END TRY
	BEGIN CATCH
		IF @@TRANCOUNT = 1
		BEGIN
			ROLLBACK TRANSACTION
		END

		SET @ResultCode = 500;
		SET @Comment = ERROR_MESSAGE();
	END CATCH
	
	EXEC dbo.sx_pf_pPOST_API_LogEntry @Username, @TransactUsername, @ProcedureName, @ParameterString, @AffectedRows, @ResultCode, @TimestampCall, @Comment;
	
	IF @ResultCode >= 500
	    EXEC system.spSEND_Message 'ERROR', @Comment;

    RETURN @ResultCode;
END;
		

-- SET documentation variables      ***********************************************************************
DECLARE
     @level0name        NVARCHAR(255)   = N'global'    -- enter schema name of the table
    ,@level1name        NVARCHAR(255)   = N'spPOST_Company'         -- 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'Procedure save / edit Companies.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name;
 
 
-- optional SET parameter documentation (only for Core / Standardmodules)
    SET @level2name = N'@DataSourceID';
    SET @value      = N'Integer number of the DataSource.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
 
    SET @level2name = N'@CompanyID';
    SET @value      = N'ID of the Company, must be unique in this DataSource.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

	SET @level2name = N'@CompanyName';
    SET @value      = N'Name of this company.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

	SET @level2name = N'@Description';
    SET @value      = N'Description of the company.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

	SET @level2name = N'@FinancialYearBegin';
    SET @value      = N'Month of the start of the financial year - e.g. 1 for January.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

	SET @level2name = N'@ChartsOfAccountsID';
    SET @value      = N'ID of the current Chart of Accounts, this ID must match the ID in Table global.tFIN_ChartsOfAccounts.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

	SET @level2name = N'@AccountLength';
    SET @value      = N'Integer Value for the length of the AccountID - usually between 4 and 8.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

	SET @level2name = N'@Currency';
    SET @value      = N'ISO Code of the currency of the company, e.g. EUR';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

	SET @level2name = N'@CustomValuesJSON';
    SET @value      = N'Custom field to store any informations as JSON Array.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

	SET @level2name = N'@CustomValuesJSONUser';
    SET @value      = N'Custom field to store any informations as JSON Array, may have rights difference for reading.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

	SET @level2name = N'@OwnerCode';
    SET @value      = N'CODE Word who the owner of this company is - INTEGRATION or PLANNING.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

GO


DROP PROCEDURE IF EXISTS planning.spPOST_Tab;
GO

/*
	Procedure to POST a Tab for
		- a cluster
		- a factory
		- a productline
		- a product
 
	Saxess Software GmbH
	Last modified: 08/2024 for OCT 5.10
  
	Testcall Procedure
		DECLARE @RC INT;
		EXEC @RC = planning.spPOST_Tab
				 @Username			= 'W10\admin'
				,@FactoryID			= ''
				,@ProductLineID		= ''
				,@ProductID			= ''
				,@TabID				= 'Td5dd'
				,@OrderIndex		= '3'
				,@TabName			= 'Name'
				,@TabHint			= 'Hint'
				,@TabText			= 'Text'
				,@PresentationCODE	= 'Pivot'
				,@Datasource		= 'fdafds'
				,@IsVisibleBOOL		= '1'
				,@LayoutJSON		= 'dafds'
				,@ParameterJSON		= 'fdafd'
				,@SQLCommand        = ''
		PRINT @RC
		SELECT * FROM planning.tTabs ORDER BY OrderIndex;

		TRUNCATE TABLE planning.tTabs;

		SELECT TOP 10 * FROM system.tAPI_Log;

	Testcall Documentation
		SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'planning', 'PROCEDURE', 'spPOST_Tab',NULL,NULL)
		UNION ALL
		SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'planning', 'PROCEDURE', 'spPOST_Tab','PARAMETER',NULL)
*/
   
CREATE PROCEDURE planning.spPOST_Tab
    (
         @Username				NVARCHAR(255)
		,@FactoryID				NVARCHAR(255) = ''				
		,@ProductLineID			NVARCHAR(255) = ''
		,@ProductID				NVARCHAR(255) = ''
		,@TabID					NVARCHAR(255) 
		,@OrderIndex			INT 
		,@TabName				NVARCHAR(255) 
		,@TabHint				NVARCHAR(2000) 
		,@TabText				NVARCHAR(2000) 
		,@PresentationCODE		NVARCHAR(255) 
		,@Datasource			NVARCHAR(2000) 
		,@IsVisibleBOOL			INT
		,@LayoutJSON			NVARCHAR(MAX) = NULL
		,@ParameterJSON			NVARCHAR(MAX)
		,@SQLCommand			NVARCHAR(MAX) = NULL
    )
AS 
	BEGIN

		SET NOCOUNT ON;

		-- Standard declaration for logging
		DECLARE 
				 @ProcedureName		NVARCHAR(255)	= OBJECT_SCHEMA_NAME(@@PROCID) + N'.' + OBJECT_NAME(@@PROCID)

				,@ParameterString	NVARCHAR(MAX)	= N'''' 
				+ ISNULL(@Username				,N'NULL')					+ N''',''' 
				+ ISNULL(@FactoryID				,N'NULL')					+ N''',''' 
				+ ISNULL(@ProductLineID			,N'NULL')					+ N''',''' 
				+ ISNULL(@ProductID				,N'NULL')					+ N''','''
				+ ISNULL(@TabID					,N'NULL')					+ N''','
				+ CAST(ISNULL(@OrderIndex		,N'NULL') AS NVARCHAR(255)) + N','''
				+ ISNULL(@TabName				,N'NULL')					+ N''','''
				+ ISNULL(@TabHint				,N'NULL')					+ N''','''
				+ ISNULL(@TabText				,N'NULL')					+ N''','''
				+ ISNULL(@PresentationCODE		,N'NULL')					+ N''','''
				+ ISNULL(@Datasource			,N'NULL')					+ N''','
				+ CAST(ISNULL(@IsVisibleBOOL	,N'NULL') AS NVARCHAR(255)) + N','''
				+ ISNULL(@LayoutJSON			,N'NULL')					+ N''','''
				+ ISNULL(@ParameterJSON			,N'NULL')					+ N''','''
				+ ISNULL(@SQLCommand			,N'NULL')					+ N''''

				,@EffectedRows		INT				= 0			
				,@ResultCode		INT				= 501				
				,@TimestampCall		DATETIME		= GETUTCDATE()
				,@Comment			NVARCHAR(2000)	= N''
				,@TransactUsername	NVARCHAR(255)	= N''

		-- Procedure specific declarations
		DECLARE  @FactoryKey		INT			= 0
				,@ProductLineKey	INT			= 0
				,@ProductKey		INT			= 0
				,@RowKey			BIGINT		= 0
				,@OldOrderIndex		INT			= 0;	

		-- NULL Protection for all Input parameters
		IF @Username			IS NULL SET @Username			= N'';
		IF @FactoryID			IS NULL SET @FactoryID			= N'';
		IF @ProductLineID		IS NULL SET @ProductLineID		= N'';
		IF @ProductID			IS NULL SET @ProductID			= N'';
		IF @TabID				IS NULL SET @TabID				= N'';
		IF @OrderIndex			IS NULL SET @OrderIndex			= 0  ;
		IF @TabName				IS NULL SET @TabName			= N'';
		IF @TabHint				IS NULL SET @TabHint			= N'';
		IF @TabText				IS NULL SET @TabText			= N'';
		IF @PresentationCODE	IS NULL SET @PresentationCODE	= N'';
		IF @Datasource			IS NULL SET @Datasource			= N'';
		IF @IsVisibleBOOL		IS NULL SET @IsVisibleBOOL		= 0  ;
		IF @LayoutJSON			IS NULL SET @LayoutJSON			= N'';
		IF @ParameterJSON		IS NULL SET @ParameterJSON		= N'';
		IF @SQLCommand			IS NULL SET @SQLCommand			= N'';

		-- dont use negative index values
		IF @OrderIndex			< 0		SET @OrderIndex			= 1;
		-- protect bool
		IF @IsVisibleBOOL      <> 1		SET @IsVisibleBOOL		= 0;

		-- Content Protection for specific input parameters (e.g.IDs)
		SET @FactoryID		= dbo.sx_pf_pProtectID	(@FactoryID);
		SET @ProductLineID	= dbo.sx_pf_pProtectID	(@ProductLineID);
		SET @ProductID		= dbo.sx_pf_pProtectID	(@ProductID);

        -- Handle updated PresentationCODEs
        IF @PresentationCODE = 'HTML_URL' SET @PresentationCODE = 'ExternalWebsite'
        IF @PresentationCODE = 'HTML_Static' SET @PresentationCODE = 'HTMLFromSQLQuery'
        IF @PresentationCODE = 'HTML_Editor' SET @PresentationCODE = 'Editor'
		
		-- START TRANSACTION			***********************************************************************************
		BEGIN TRY
			BEGIN TRANSACTION spPOST_Tab;
				-- check transaction user existence
				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;
				
				-- determine Keys for requested IDs - empty IDs keep Key -1
				IF @FactoryID		= '' SET @FactoryKey		= -1;
				IF @ProductLineID	= '' SET @ProductLineKey	= -1;
				IF @ProductID		= '' SET @ProductKey		= -1;

				SELECT @FactoryKey		= FactoryKey		FROM planning.tdFactories	 WHERE FactoryID		= @FactoryID												AND @FactoryKey <> -1;
				SELECT @ProductLineKey	= ProductLineKey	FROM planning.tdProductLines WHERE FactoryKey		= @FactoryKey		AND ProductLineID	= @ProductLineID	AND @ProductLineKey <> -1;
				SELECT @ProductKey		= ProductKey		FROM planning.tdProducts	 WHERE ProductLineKey	= @ProductLineKey	AND ProductID		= @ProductID		AND @ProductKey <> -1;
				
				-- error if and ID dont exists
				IF		@FactoryKey		= 0 
					OR	@ProductLineKey	= 0
					OR  @ProductKey		= 0
				BEGIN
					SET @ResultCode = 404;
					RAISERROR(N'Not existing FID / PID / PLID - Keys don`t exists', 16, 10);
				END;

				-- error if ID chains has gaps
				IF		@FactoryKey		= -1 AND (@ProductLineKey <> -1 OR @ProductKey <> -1)
					OR	@ProductLineKey	= -1 AND @ProductKey <> -1 
				BEGIN
					SET @ResultCode = 404;
					RAISERROR('ID combination can have only gaps at the end - Keys don`t exists', 16, 10);
				END;

				-- check user rights for cluster level
				IF @FactoryKey = -1
					BEGIN
							EXEC @ResultCode = sx_pf_pGET_ClusterWriteRight @TransactUsername;
								IF @ResultCode <> 200
								BEGIN
									RAISERROR('Invalid rights for Cluster write.', 16, 10);
								END 
					END

				-- check user rights for Factory level
				IF @FactoryKey <> -1 AND @ProductLineKey = -1
					BEGIN
							EXEC @ResultCode = sx_pf_pGET_FactoryWriteRight @TransactUsername,@FactoryID;
								IF @ResultCode <> 200
								BEGIN
									RAISERROR('Invalid rights for Factory write.', 16, 10);
								END 
					END

				-- check user rights for ProductLine / Product level
				IF @FactoryKey <> -1 AND @ProductLineKey <> -1
					BEGIN
							EXEC @ResultCode = sx_pf_pGET_ProductLineWriteRight @TransactUsername,@FactoryID,@ProductLineID;
								IF @ResultCode <> 200
								BEGIN
									RAISERROR('Invalid rights for ProductLine / Product write.', 16, 10);
								END 
					END

				SET @ResultCode = 501 -- reset

				-- determine if posted Tab already exists
				SELECT 
					 @Rowkey = RowKey
					,@OldOrderIndex = COALESCE(Orderindex, CASE WHEN TabID = '0' THEN 0 END)
				FROM planning.tTabs
				WHERE 
						FactoryKey		= @FactoryKey
					AND ProductLineKey	= @ProductLineKey
					AND ProductKey		= @ProductKey
					AND TabID			= @TabID

				-- data operation starts	***********************************************************************************
				-- delete if exits, to determine Resultcode
				IF @RowKey > 0 
					BEGIN
						-- delete tab layouts if tab type was changed
						IF @PresentationCODE <> (SELECT PresentationCODE FROM planning.tTabs WHERE RowKey = @RowKey)
						BEGIN
							DELETE FROM planning.tTabLayouts WHERE FactoryID = @FactoryID AND ProductLineID = @ProductLineID AND TabID = @TabID;
						END

						DELETE FROM planning.tTabs WHERE RowKey = @RowKey;
						SET @Resultcode = 200
					END
					
				-- System Tab with TabID 0 can only change visibility
				IF @TabID = '0'
					BEGIN
						INSERT INTO planning.tTabs (
							 FactoryKey
							,ProductLineKey
							,ProductKey
							,FactoryID
							,ProductLineID
							,ProductID
							,TabID
							,Orderindex
							,TabName
							,TabHint
							,TabText
							,PresentationCODE
							,Datasource
							,IsVisibleBOOL
							,ParameterJSON
							,SQLCommand
						)
							VALUES
							(
								 @FactoryKey
								,@ProductLineKey
								,@ProductKey
								,@FactoryID
								,@ProductLineID
								,@ProductID
								,@TabID
								,@OrderIndex
								,@TabName
								,N''
								,N''
								,N'System'
								,N''
								,@IsVisibleBOOL
								,N''
								,N''
							)
					END
				ELSE
					BEGIN
						-- Insert new Tab
						INSERT INTO planning.tTabs (
							 FactoryKey
							,ProductLineKey
							,ProductKey
							,FactoryID
							,ProductLineID
							,ProductID
							,TabID
							,Orderindex
							,TabName
							,TabHint
							,TabText
							,PresentationCODE
							,Datasource
							,IsVisibleBOOL
							,ParameterJSON
							,SQLCommand
						)
							VALUES
							(
								 @FactoryKey
								,@ProductLineKey
								,@ProductKey
								,@FactoryID
								,@ProductLineID
								,@ProductID
								,@TabID
								,@OrderIndex
								,@TabName
								,@TabHint
								,@TabText
								,@PresentationCODE
								,@Datasource
								,@IsVisibleBOOL
								,@ParameterJSON
								,@SQLCommand
							)
					END
					
            -- Update other numbers if Number is changed or new
            IF (@OldOrderIndex <> @OrderIndex)
                BEGIN			
                    -- case if OrderIndex is bigger now and existed before - decrement all in move window
                    IF @OldOrderIndex < @OrderIndex AND @RowKey <> 0
                        BEGIN
                            UPDATE planning.tTabs
                            SET Orderindex = Orderindex - 1 
                            WHERE 
                                    Orderindex		>= @OldOrderIndex 
                                AND Orderindex		<= @OrderIndex
                                AND	FactoryKey		= @FactoryKey
                                AND ProductLineKey	= @ProductLineKey
                                AND ProductKey		= @ProductKey
                                AND TabID			<> @TabID
                        END
                    -- case if OrderIndex is smaller now and existed before - increment all in move window
                    IF @OldOrderIndex > @OrderIndex AND @RowKey <> 0
                        BEGIN
                            UPDATE planning.tTabs
                            SET Orderindex = Orderindex + 1 
                            WHERE 
                                    Orderindex		<= @OldOrderIndex 
                                AND Orderindex		>= @OrderIndex
                                AND	FactoryKey		= @FactoryKey
                                AND ProductLineKey	= @ProductLineKey
                                AND ProductKey		= @ProductKey
                                AND TabID			<> @TabID
                        END

                    -- case new Tab - increment all following
                    IF @RowKey = 0
                        BEGIN
                            UPDATE planning.tTabs
                            SET Orderindex = Orderindex + 1 
                            WHERE 
                                    Orderindex		>= @OrderIndex
                                AND	FactoryKey		= @FactoryKey
                                AND ProductLineKey	= @ProductLineKey
                                AND ProductKey		= @ProductKey
                                AND TabID			<> @TabID
                        END

                    -- eliminate all Gaps
                    UPDATE planning.tTabs 
                    SET Orderindex = Rownumbers.Number
                    FROM planning.tTabs tT INNER JOIN (
                                                                SELECT 
                                                                        ROW_NUMBER() OVER (ORDER BY Orderindex) AS Number
                                                                    ,TabID
                                                        
                                                                FROM planning.tTabs
                                                                WHERE 
                                                                        FactoryKey		= @FactoryKey
                                                                    AND ProductLineKey	= @ProductLineKey
                                                                    AND ProductKey		= @ProductKey
                                                        ) AS Rownumbers
                    ON 
                            tT.TabID			= Rownumbers.TabID
                        AND tT.FactoryKey		= @FactoryKey
                        AND tT.ProductLineKey	= @ProductLineKey
                        AND tT.ProductKey		= @ProductKey
                END		

                -- save TabLayout
                IF @LayoutJSON <> ''
                BEGIN
                    EXEC planning.spPOST_TabLayout
                        @Username = @Username,
                        @FactoryID = @FactoryID,
                        @ProductLineID = @ProductLineID,
                        @TabID = @TabID,
                        @LayoutID = '*',
                        @LayoutName = 'Default',
                        @LayoutJSON = @LayoutJSON,
                        @OrderIndex = 1,
                        @LayoutOwner = ''
                END

				IF @ResultCode <> 200 SET @ResultCode = 201;

			COMMIT TRANSACTION spPOST_Tab;
		END TRY

		-- START CATCH				***********************************************************************************
		BEGIN CATCH
			DECLARE @Error_state INT = ERROR_STATE();
			SET @Comment = ERROR_MESSAGE();
			
			ROLLBACK TRANSACTION spPOST_Tab;		

			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 sx_pf_pPOST_API_LogEntry @Username, @TransactUsername, @ProcedureName, @ParameterString, @EffectedRows, @ResultCode, @TimestampCall, @Comment;
		RETURN @ResultCode;
	END
 
GO
 
-- SET documentation variables      ***********************************************************************
DECLARE
     @level0name        NVARCHAR(255)   = N'planning'		-- enter schema name of the table
    ,@level1name        NVARCHAR(255)   = N'spPOST_Tab'		-- 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 = not shiped from saxess
                                                            -- 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
 
                                                            -- 10 = shiped from saxess as customer specific object
                                                              
-- 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 documententation             *************************************************************************
 
-- SET Procedure documentation
    SET @value = N'Procedure to POST a Tab. The Tab with ID 0 is the special system tab, but does not get special handling in databae.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name;
  
-- SET parameter documentation
    SET @level2name = N'@Username';
    SET @value      = N'Name of the user this action is requested for.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
 
    SET @level2name = N'@FactoryID';
    SET @value      = N'optional FactoryID, skip / NULL / empty string if POST is for Cluster level';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

	SET @level2name = N'@ProductLineID';
    SET @value      = N'optional ProductLineID, skip / NULL / empty string if POST is for Cluster or Factory level';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

	SET @level2name = N'@ProductID';
    SET @value      = N'optional ProductD, skip / NULL / empty string if POST is for Cluster, Factory or ProductLine level';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

	SET @level2name = N'@TabID';
    SET @value      = N'Business Identifier for the Tab.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

	SET @level2name = N'@OrderIndex';
    SET @value      = N'Position in the list of Tabs. Any Number > 0 can be passed in, tabs reorder themself.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

 	SET @level2name = N'@TabName';
    SET @value      = N'Name of the Tab, used as Label.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

	SET @level2name = N'@TabHint';
    SET @value      = N'Hint on the Tab, displayed as Tooltip.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

	SET @level2name = N'@TabText';
    SET @value      = N'Text for the headerarea inside the tab.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

	SET @level2name = N'@PresentationCODE';
    SET @value      = N'CODE word for the type of presentation, e.g PIVOT, DATAGRID, HTML_URL, HTML_STATIC.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

	SET @level2name = N'@Datasource';
    SET @value      = N'Datasource fitting to PresentationCODE - name of the stored procedure or URL.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

	SET @level2name = N'@IsVisibleBOOL';
    SET @value      = N'1 for visible, 0 for not visible';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

	SET @level2name = N'@LayoutJSON';
    SET @value      = N'The JSON definition of the layout.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

	SET @level2name = N'@ParameterJSON';
    SET @value      = N'Parameter definition (List and default value) as JSON String';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

	SET @level2name = N'@SQLCommand';
    SET @value      = N'A custom SQL command that needs to return a result set which can be displayed by pivot tables and data grids.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
GO



DROP PROCEDURE IF EXISTS system.spDELETE_DataSource;
GO

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

	Procedure to delete a DataSource an all data which belong to it. 

	A Datasource contains mostly three types of data
		- itselft, its definition of a connnection to an Datasource
		- the data extracted from this datasource (Companies, Dimensions, Facts)
		- process definitions for this datasource (Pipelines, Steps..)
 
	Testcall Procedure
		DECLARE  @RC INT;
		EXEC	 @RC = system.spDELETE_DataSource
				 @Username		= 'SQL'
				,@DataSourceID	= '2'
		PRINT    @RC

		SELECT * FROM system.tDataSources

	Testcall Documentation
		SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spDELETE_DataSource',NULL,NULL)
		UNION ALL
		SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spDELETE_DataSource','PARAMETER',NULL)
*/
 
CREATE PROCEDURE system.spDELETE_DataSource
    (
         @Username			NVARCHAR(255)
        ,@DataSourceID		NVARCHAR(50)
    )

AS 
	BEGIN	

		-- Standard declaration for logging
		DECLARE 
				 @ProcedureName		NVARCHAR(255)	= OBJECT_SCHEMA_NAME(@@PROCID) + N'.' + OBJECT_NAME(@@PROCID)

				,@ParameterString	NVARCHAR(MAX)	= N''''					--set Strings inside in single quotes (N''','''), Numbers without strings inside without quotes (N','), end list with '''' in case of string or '' in case of number on last position
				+ ISNULL(@Username					,N'NULL')	+ N''','''											 
				+ ISNULL(@DataSourceID 				,N'NULL')	+ N''''

				,@EffectedRows		INT				= 0			
				,@ResultCode		INT				= 501				
				,@TimestampCall		DATETIME		= GETUTCDATE()
				,@Comment			NVARCHAR(2000)	= N''
				,@TransactUsername	NVARCHAR(255)	= N''
				,@Tablename			NVARCHAR(255)	= N''
				,@SQL				NVARCHAR(MAX)	= N''
				,@DataSourceKey			INT;

		-- NULL Protection for all Input parameters
		IF @Username				IS NULL SET @Username			= N'';
		IF @DataSourceID			IS NULL SET @DataSourceID		= N'';


		-- START TRANSACTION			***********************************************************************************
		BEGIN TRY
			BEGIN TRANSACTION spDELETE_DataSource
				-- check transaction user existence
				SELECT @TransactUsername = dbo.sx_pf_Determine_TransactionUsername (@Username);
					IF @TransactUsername  = N'403' 
						BEGIN
							SET @ResultCode = 403;
							RAISERROR(N'Transaction user don`t exists', 16, 10);
						END;
				-- Check for Cluster Write Rights

				-- Check if Datasource exits
				SELECT @DataSourceKey = DataSourceKey
				FROM system.tDataSources 
				WHERE DataSourceID = @DataSourceID;

				IF @DataSourceKey IS NULL
					BEGIN
						SET @ResultCode = 404;
						RAISERROR(N'Datasource don`t exists', 16, 10);
					END;



				-- Generic delete is still done over DatasourceKey, as DatasourceID was till 5.5 the DatasourceKey
				PRINT '';
				PRINT 'Starting generic Delete Process for all Tables outside system containing the Column "DatasourceKey"';
				PRINT '#####################################################################################################';
				PRINT '';
				-- DELETE Date in all Non-Core Tables where the column DataSourceKey is used (usually global and integration schema)
				-- Generic delete over dynamic SQL is possible in all OCT schema except "system" in default

				DECLARE TableCursor CURSOR FOR
					SELECT      
						 CONCAT(infs.TABLE_SCHEMA COLLATE DATABASE_DEFAULT,N'.',infs.TABLE_NAME COLLATE DATABASE_DEFAULT)	AS Tablename

					FROM INFORMATION_SCHEMA.COLUMNS infs
						-- Determination Objekttype
						LEFT JOIN 
								(
								SELECT 
									 Name	  				COLLATE DATABASE_DEFAULT    AS Objektname
									,SCHEMA_NAME(Schema_id) COLLATE DATABASE_DEFAULT	AS Schemaname
									,type_desc				COLLATE DATABASE_DEFAULT	AS Objekttyp
								FROM sys.objects
								) syo ON 
											syo.Schemaname = infs.TABLE_SCHEMA
										AND syo.Objektname = infs.TABLE_NAME

					WHERE 
							syo.Objekttyp		= N'USER_TABLE'
						AND infs.COLUMN_NAME	= N'DataSourceKey'
						-- ToDo: Determination not a Core table should be moved to metadata 
						AND infs.TABLE_SCHEMA  <> N'system'
						AND CONCAT(infs.TABLE_SCHEMA,N'.',infs.TABLE_NAME) <> N'global.tCompanies'

				OPEN TableCursor;
				FETCH TableCursor
				INTO @Tablename;
				IF @@CURSOR_ROWS = 0
					BEGIN
						PRINT CONCAT(N'No Tables found for generic deletion over DataSourceKey ',@DataSourceKey,N'.')
					END
				WHILE @@FETCH_STATUS = 0
				BEGIN
					-- clear variables
					SET @SQL = N'';
					SET @SQL = CONCAT(N'DELETE FROM ', @Tablename, N' WHERE DataSourceKey =', @DataSourceKey,';') -- ToDo: ID is numeric at the moment, change to string when changed					
					
					PRINT CONCAT(N'Delete all Rows with DatasourceKey ', @DataSourceKey, N' in Table ',@Tablename)
					EXEC (@SQL);

					FETCH TableCursor
					INTO @Tablename;
				END;
				CLOSE TableCursor;
				DEALLOCATE TableCursor;

				-- Generic delete is still done over CompanyKey
				-- GET all Non-Core Tables where the column CompanyKey is used (usually result schema) and delete the Values for this ComanpanyKey
				-- The Company Key is an combinded Key "DataSourceKey | CompanyID"
				-- some are deleted the second time, as they have the Datasource Key also !!

				PRINT '';
				PRINT 'Starting generic Delete Process for all Tables outside system containing the Column "CompanyKey" which is a concated Key with the Datasource.';
				PRINT '#####################################################################################################';
				PRINT '';
				DECLARE TableCursor CURSOR FOR
					SELECT      
							CONCAT(infs.TABLE_SCHEMA COLLATE DATABASE_DEFAULT,N'.',infs.TABLE_NAME COLLATE DATABASE_DEFAULT)	AS Tablename

					FROM INFORMATION_SCHEMA.COLUMNS infs
						-- Determination Objekttype
						LEFT JOIN 
								(
								SELECT 
									 Name					COLLATE DATABASE_DEFAULT AS Objektname
									,SCHEMA_NAME(Schema_id) COLLATE DATABASE_DEFAULT AS Schemaname
									,type_desc				COLLATE DATABASE_DEFAULT AS Objekttyp
								FROM sys.objects
								) syo ON 
											syo.Schemaname = infs.TABLE_SCHEMA
										AND syo.Objektname = infs.TABLE_NAME

				WHERE 
							syo.Objekttyp		= N'USER_TABLE'
						AND infs.COLUMN_NAME	= N'CompanyKey'
						-- ToDo: Determination not a Core table should be moved to metadata 
						AND infs.TABLE_SCHEMA  <> N'system'
						AND CONCAT(infs.TABLE_SCHEMA,N'.',infs.TABLE_NAME) <> N'global.tCompanies';
				OPEN TableCursor;
				FETCH TableCursor
				INTO @Tablename;
					IF @@CURSOR_ROWS = 0
						BEGIN
							PRINT CONCAT(N'No Tables found for generic deletion over CompanyKeys starting with ',@DataSourceKey,N'| .')
						END
				WHILE @@FETCH_STATUS = 0
					BEGIN
						-- clear variables
						SET @SQL = N'';
						SET @SQL = CONCAT(N'DELETE FROM ', @Tablename, N' WHERE CompanyKey LIKE ''', @DataSourceKey,N'|%'';') 

						PRINT CONCAT(N'Delete all Rows with DatasourceKey ', @DataSourceKey, N' in CompanyKey in Table ',@Tablename)
						EXEC (@SQL);

						FETCH TableCursor
						INTO @Tablename;
					END;
				CLOSE TableCursor;
				DEALLOCATE TableCursor;

				PRINT ''
				PRINT 'End of generic delition'
				PRINT  '######################################################################'
				PRINT ''

				PRINT '';
				PRINT 'Starting Delete for Core Data'
				PRINT '#####################################################################################################';
				PRINT '';
								
				-- PIPELINES - Company selection is deleted in all steps, but not the steps themself using the datasource

				-- system.tPipelineSteps  - TODO - in new field CompaniesJSON
			
				-- global.tCompanies
				DELETE FROM global.tCompanies WHERE DataSourceKey = @DataSourceKey;

				PRINT 'Delete the datasource itself'
				-- DELETE Datasource (as last due to calculated / dependend columns)
				DELETE FROM system.tDataSources WHERE DataSourceID = @DataSourceID;

				SET @ResultCode = 200;
			COMMIT TRANSACTION spDELETE_DataSource
		END TRY

		-- START CATCH				***********************************************************************************
		BEGIN CATCH
			DECLARE @Error_state INT = ERROR_STATE();
			SET @Comment = ERROR_MESSAGE();
			
			ROLLBACK TRANSACTION spDELETE_DataSource	

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

		EXEC sx_pf_pPOST_API_LogEntry @Username, @TransactUsername, @ProcedureName, @ParameterString, @EffectedRows, @ResultCode, @TimestampCall, @Comment;
		RETURN @ResultCode;
	END
GO

-- SET documentation variables      ***********************************************************************
DECLARE
     @level0name        NVARCHAR(255)   = N'system'    -- enter schema name of the table
    ,@level1name        NVARCHAR(255)   = N'spDELETE_DataSource'         -- 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'Procedure to delete a Datasouce - the data from its proceeded companies is deleted in all tables with Columns DatasourceKey or CompanyKey too, but the datesource is keept in Process Steps.';    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name;
  
-- optional SET parameter documentation (only for Core / Standardmodules)
    SET @level2name = N'@DataSourceID';
    SET @value      = N'ID of the DataSource to be deleted.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;


	GO


DROP PROCEDURE IF EXISTS system.spGET_Datasources;
GO

/*
    Procedure to GET Datasources information

    Saxess Software GmbH

    Testcall Procedure
        DECLARE @RC INT;
        EXEC @RC = system.spGET_Datasources
                @Username = N'SQL'
              , @DataSourceID = N''
        PRINT @RC

    Testcall Documentation
        SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spGET_Datasources', NULL, NULL)
        UNION ALL
        SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spGET_Datasources', 'PARAMETER', NULL)
*/
 
CREATE PROCEDURE system.spGET_Datasources
    @Username       NVARCHAR(255)
  , @DataSourceID   NVARCHAR(50) = N''
AS
BEGIN
    BEGIN TRY
		-- Logging
        DECLARE @TimestampCall      DATETIME        = GETUTCDATE();
        DECLARE @ProcedureName      NVARCHAR(255)   = OBJECT_SCHEMA_NAME(@@PROCID) + N'.' + OBJECT_NAME(@@PROCID);
        DECLARE @AffectedRows       INT             = 0;
        DECLARE @ResultCode         INT             = 501;
        DECLARE @Comment            NVARCHAR(4000)  = N'';
        DECLARE @ParameterString    NVARCHAR(MAX)   = N'';
        DECLARE @TransactUsername   NVARCHAR(255)   = N'';
        EXEC system.spGET_ParameterString @ParameterString OUTPUT, 2, @Username, @DataSourceID;
        EXEC system.spGET_TransactUsername @TransactUsername OUTPUT, @Username;
        
        -- Input parameter handling
        SET @DataSourceID = COALESCE(dbo.sx_pf_pProtectID(@DataSourceID), N'');

        -- Return Datasources information
        SELECT
            DataSourceKey
          , DataSourceID
          , DataSourceName
          , SourceType
          , ConnectionValuesJSON 
        FROM system.tDataSources
        WHERE
            DataSourceID = @DataSourceID
            OR @DataSourceID = '';

        SET @AffectedRows = @@ROWCOUNT;

        SET @ResultCode = 200
    END TRY
    BEGIN CATCH
        SET @ResultCode = 500;
        SET @Comment = ERROR_MESSAGE();
    END CATCH;

    EXEC dbo.sx_pf_pPOST_API_LogEntry @Username, @TransactUsername, @ProcedureName, @ParameterString, @AffectedRows, @ResultCode, @TimestampCall, @Comment;

    IF @ResultCode >= 500
        EXEC system.spSEND_Message N'ERROR', @Comment;

    RETURN @ResultCode;
END;
GO

-- SET documentation variables      ***********************************************************************
DECLARE
     @level0name        NVARCHAR(255)  = N'system'			    -- enter schema name of the table
    ,@level1name        NVARCHAR(255)  = N'spGET_Datasources'    -- 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'Procedure to GET one or all Datasourcess.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name;


-- SET parameter documentation
    SET @level2name = N'@Username';
    SET @value      = N'Username';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

    SET @level2name = N'@DataSourceID';
    SET @value      = N'DataSourceID - ID of the data source.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
GO


DROP PROCEDURE IF EXISTS system.spMOVE_Pipeline;
GO

/*
    Procedure to MOVE a Pipeline

    Saxess Software GmbH

    Testcall
        DECLARE @RC INT;
        EXEC @RC = system.spMOVE_Pipeline
                @Username = N'SQL'
              , @SourcePipelineID = N'P1'
              , @TargetPipelineID = N'Pipeline1'
        SELECT @RC;

    Testcall Documentation
        SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spMOVE_Pipeline', NULL, NULL)
        UNION ALL
        SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spMOVE_Pipeline', 'PARAMETER', NULL)
*/
 
CREATE PROCEDURE system.spMOVE_Pipeline
    @Username           NVARCHAR(255)
  , @SourcePipelineID   NVARCHAR(255)
  , @TargetPipelineID   NVARCHAR(255)
AS
BEGIN
    BEGIN TRY
        -- Logging
        DECLARE @TimestampCall      DATETIME        = GETUTCDATE();
        DECLARE @ProcedureName      NVARCHAR(255)   = OBJECT_SCHEMA_NAME(@@PROCID) + N'.' + OBJECT_NAME(@@PROCID);
        DECLARE @AffectedRows       INT             = 0;
        DECLARE @ResultCode         INT             = 501;
        DECLARE @Comment            NVARCHAR(4000)  = N'';
        DECLARE @ParameterString    NVARCHAR(MAX)   = N'';
        DECLARE @TransactUsername   NVARCHAR(255)   = N'';
        EXEC system.spGET_ParameterString @ParameterString OUTPUT, 3, @Username, @SourcePipelineID, @TargetPipelineID;
        EXEC system.spGET_TransactUsername @TransactUsername OUTPUT, @Username;
        
        -- Input parameter handling
        SET @SourcePipelineID = COALESCE(dbo.sx_pf_pProtectID(@SourcePipelineID), N'');
        SET @TargetPipelineID = COALESCE(@TargetPipelineID, N'');
        
        -- Error handling
        IF @SourcePipelineID = N''
            EXEC system.spSEND_Message N'ERROR', N'No SourcePipelineID provided'

        IF @TargetPipelineID = N''
            EXEC system.spSEND_Message N'ERROR', N'No TargetPipelineID provided'

        IF EXISTS(
            SELECT 1
            FROM system.tPipelines
            WHERE PipelineID = @TargetPipelineID
        )
            EXEC system.spSEND_Message N'ERROR', N'TargetPipelineID already exists'
        
        -- Rename Pipeline
        UPDATE system.tPipelines
        SET PipelineID = @TargetPipelineID
        WHERE PipelineID = @SourcePipelineID;

        SET @AffectedRows = @@ROWCOUNT;

        IF @AffectedRows = 0
            EXEC system.spSEND_Message N'ERROR', N'SourcePipelineID doesn''t exist'

        SET @ResultCode = 200
    END TRY
    BEGIN CATCH
        SET @ResultCode = 500;
        SET @Comment = ERROR_MESSAGE();
    END CATCH;

    EXEC dbo.sx_pf_pPOST_API_LogEntry @Username, @TransactUsername, @ProcedureName, @ParameterString, @AffectedRows, @ResultCode, @TimestampCall, @Comment;

    IF @ResultCode >= 500
        EXEC system.spSEND_Message N'ERROR', @Comment;

    RETURN @ResultCode;
END;
GO

-- SET documentation variables      ***********************************************************************
DECLARE
     @level0name        NVARCHAR(255)  = N'system'			    -- enter schema name of the table
    ,@level1name        NVARCHAR(255)  = N'spMOVE_Pipeline'    -- enter procedure name
    ,@SX_Owner	        NVARCHAR(255)  = N'OCT.core'		    -- enter owner name of the procedure from list (OCT.core, OCT.Pipelines, Custom)
    ,@SX_Pipeline		NVARCHAR(255)  = N'CORE'			    -- enter Pipeline 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_Pipeline;
    EXEC sys.sp_addextendedproperty N'SX_Pipeline'		,@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'Procedure to MOVE a Pipeline';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name;

-- SET parameter documentation
    SET @level2name = N'@Username';
    SET @value      = N'Username';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

    SET @level2name = N'@SourcePipelineID';
    SET @value      = N'PipelineID of the Pipeline that should be renamed.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

    SET @level2name = N'@TargetPipelineID';
    SET @value      = N'New PipelineID of the Pipeline.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
GO



DROP PROCEDURE IF EXISTS system.spPOST_DataSource;
GO
/*
	Procedure to POST a DataSource
 
	Gerd Tautenhahn for Saxess Software GmbH
	Last modified: 10/2022 for OCT 5.8
 
	Testcall Base tables
		SELECT * FROM system.tDataSources
 
	Testcall Procedure
		DECLARE  @RC INT;
		EXEC	 @RC = system.spPOST_DataSource
				 @Username				= N'SQL'
				,@DataSourceID			= N'0'  -- must be numeric for compatibility with modules. Full ID Support from 6.0 on
				,@DataSourceName		= N'GT1'
				,@SourceType			= N''
				,@ConnectionValuesJSON	= N''
		PRINT    @RC

	Testcall Documentation
		SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spPOST_DataSource',NULL,NULL)
		UNION ALL
		SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spPOST_DataSource','PARAMETER',NULL)
*/
 
CREATE PROCEDURE system.spPOST_DataSource
    (
         @Username				NVARCHAR(255)
        ,@DataSourceID			NVARCHAR(50)
		,@DataSourceName		NVARCHAR(255) = ''
		,@SourceType			NVARCHAR(50) = ''
		,@ConnectionValuesJSON	NVARCHAR(MAX) = ''
    )

-- must be always execute as owner as it uses IDENTITY INSERT - but this don't work on Azure, if the Owner is an AAD User - OPEN PROBLEM
WITH EXECUTE AS 'dbo'

AS 
	BEGIN	

		-- Standard declaration for logging
		DECLARE 
				 @ProcedureName		NVARCHAR(255)	= OBJECT_SCHEMA_NAME(@@PROCID) + N'.' + OBJECT_NAME(@@PROCID)

				,@ParameterString	NVARCHAR(MAX)	=		 N''''					--set Strings inside in single quotes (N''','''), Numbers without strings inside without quotes (N','), end list with '''' in case of string or '' in case of number on last position
				+ ISNULL(@Username							,N'NULL')	+ N''','''											 
				+ ISNULL(@DataSourceID						,N'NULL')	+ N''','''
				+ ISNULL(@DataSourceName					,N'NULL')	+ N''','''
				+ ISNULL(@SourceType						,N'NULL')	+ N''','''
				+ ISNULL(@ConnectionValuesJSON				,N'NULL')	+ N''''

				,@EffectedRows		INT				= 0			
				,@ResultCode		INT				= 501				
				,@TimestampCall		DATETIME		= GETUTCDATE()
				,@Comment			NVARCHAR(2000)	= N''
				,@TransactUsername	NVARCHAR(255)	= N''


		-- NULL Protection for all Input parameters
		IF @Username				IS NULL SET @Username				= N'';
		IF @DataSourceName			IS NULL SET @DataSourceName			= N'';
		IF @SourceType				IS NULL SET @SourceType				= N'';
		IF @ConnectionValuesJSON	IS NULL SET @ConnectionValuesJSON	= N'';
        
        SET @DataSourceID = COALESCE(dbo.sx_pf_pProtectID(@DataSourceID), N'');


		-- START TRANSACTION			***********************************************************************************
		BEGIN TRY
			BEGIN TRANSACTION spPOST_DataSource
				-- check transaction user existence
				SELECT @TransactUsername = dbo.sx_pf_Determine_TransactionUsername (@Username);
					IF @TransactUsername  = N'403' 
					BEGIN
						SET @ResultCode = 403;
						RAISERROR(N'Transaction user don`t exists', 16, 10);
					END;
				
				IF @DataSourceID  = ''
					BEGIN
						SET @ResultCode = 403;
						RAISERROR(N'DataSourceID must not be empty.', 16, 10);
					END;

				IF @DataSourceID  = '0'
					BEGIN
						SET @ResultCode = 403;
						RAISERROR(N'DataSourceID 0 is reserved for Companies without Datasource and cant be created.', 16, 10);
					END;
                
				IF @DataSourceName  = '' 
					BEGIN
						SET @ResultCode = 403;
						RAISERROR('DataSourceName must not be empty.', 16, 10);
					END;

				-- Data Transactions #################################################

				-- delete / recreate DataSource with same Key
				DELETE FROM system.tDataSources WHERE DataSourceID = @DataSourceID;
	
				INSERT INTO system.tDataSources (DataSourceID,DataSourceName,SourceType,ConnectionValuesJSON)
					VALUES (@DataSourceID,@DataSourceName,@SourceType,@ConnectionValuesJSON)
	
				SET @ResultCode = 200;
			COMMIT TRANSACTION spPOST_DataSource
		END TRY

		-- START CATCH				***********************************************************************************
		BEGIN CATCH
			DECLARE @Error_state INT = ERROR_STATE();
			SET @Comment = ERROR_MESSAGE();
			
			ROLLBACK TRANSACTION spPOST_DataSource	

			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

-- SET documentation variables      ***********************************************************************
DECLARE
     @level0name        NVARCHAR(255)   = N'system'    -- enter schema name of the table
    ,@level1name        NVARCHAR(255)   = N'spPOST_DataSource'         -- 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'Procedure to POST a DataSource.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name;
 
 
-- optional SET parameter documentation (only for Core / Standardmodules)
    SET @level2name = N'@DataSourceID';
    SET @value      = N'The ID of the DataSource, an ID which is used to identify the datasource';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
 
    SET @level2name = N'@DataSourceName';
    SET @value      = N'The name of the DataSource, free description';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

	SET @level2name = N'@SourceType';
    SET @value      = N'The Source type of the datasource, its a CODE Word like MSSQL and determines the way a connection is established.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

	SET @level2name = N'@ConnectionValuesJSON';
    SET @value      = N'Encrypted string containing the Connection String including credentials.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;


	GO






UPDATE system.tSettings SET ValueText = '2026.04.0', ValueInt = 2026040 WHERE SettingID = 'DBVersion'