DROP PROCEDURE IF EXISTS planning.spPOST_TabLayout;
GO

/*
	Procedure to POST a layout for a tab
 
	Saxess Software GmbH
	Last modified: 09/2023 for OCT 5.10

	Testcall
		DECLARE @RC INT;
		DECLARE @OutputID NVARCHAR(255);
		EXEC @RC = planning.spPOST_TabLayout
			 @Username = 'SQL'
			,@FactoryID = 'ZT'
			,@ProductLineID = ''
			,@TabID = 'SF'
			,@LayoutID = '*'
			,@LayoutName = 'Test Layout nummer 2'
			,@LayoutJSON = '{}'
			,@OrderIndex = 2
			,@LayoutOwner = ''
			,@OutputID = @OutputID OUTPUT;
		SELECT @RC;
		SELECT @OutputID;
		SELECT * FROM planning.tTabLayouts ORDER BY RowKey DESC;

	Testcall Documentation
		SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'planning', 'PROCEDURE', 'spPOST_TabLayout',NULL,NULL)
		UNION ALL
		SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'planning', 'PROCEDURE', 'spPOST_TabLayout','PARAMETER',NULL)
*/
 
CREATE PROCEDURE planning.spPOST_TabLayout
    @Username       NVARCHAR(255)
  , @FactoryID      NVARCHAR(255) = ''
  , @ProductLineID  NVARCHAR(255) = ''
  , @TabID          NVARCHAR(255)
  , @LayoutID       NVARCHAR(255)
  , @LayoutName     NVARCHAR(255)
  , @LayoutJSON     NVARCHAR(MAX)
  , @OrderIndex     INT
  , @LayoutOwner	NVARCHAR(255)
  , @OutputID       NVARCHAR(255) = NULL OUTPUT
AS
BEGIN
    BEGIN TRY
		BEGIN TRANSACTION planning_spPOST_TabLayout;

		-- 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, 9, @Username, @FactoryID, @ProductLineID, @TabID, @LayoutID, @LayoutName, @LayoutJSON, @OrderIndex, @LayoutOwner;
		EXEC system.spGET_TransactUsername @TransactUsername OUTPUT, @Username;
		
		-- Parameter protection
		SET @Username = COALESCE(@Username, N'');
		SET @FactoryID = dbo.sx_pf_pProtectID(COALESCE(@FactoryID, N''));
		SET @ProductLineID = dbo.sx_pf_pProtectID(COALESCE(@ProductLineID, N''));
		SET @TabID = dbo.sx_pf_pProtectID(COALESCE(@TabID, N''));
		SET @LayoutID = dbo.sx_pf_pProtectID(COALESCE(@LayoutID, N''));
		SET @LayoutName = COALESCE(@LayoutName, N'');
		SET @LayoutJSON = COALESCE(@LayoutJSON, N'');
		SET @OrderIndex = COALESCE(@OrderIndex, 999);
		SET @LayoutOwner = COALESCE(@LayoutOwner, N'');

		-- Variables
		DECLARE @SettingUserEditPublicLayouts	BIT;
		DECLARE @SettingPrivateLayouts			BIT;
		DECLARE @SettingMultipleLayouts			BIT;
		DECLARE @IsUserAdministrator			BIT;
		DECLARE @IsPublicLayout                 BIT = CASE WHEN @LayoutOwner = N'' THEN 1 ELSE 0 END;
				
		-- Get setting: regular users can edit public layouts?
		SELECT @SettingUserEditPublicLayouts = COALESCE(ValueInt, 0)
		FROM system.fGET_Setting('TabPreferencesUserEditPublicLayouts');

		-- Get setting: are private layouts allowed in general?
		SELECT @SettingPrivateLayouts = COALESCE(ValueInt, 1)
		FROM system.fGET_Setting('TabPreferencesPrivateLayouts');

		-- Get setting: are multiple layouts allowed in general?
		SELECT @SettingMultipleLayouts = COALESCE(ValueInt, 1)
		FROM system.fGET_Setting('TabPreferencesMultipleLayouts');

		-- Is user administrator?
		SELECT @IsUserAdministrator = IsAdministratorFlag
		FROM system.trUser
		WHERE UserName = @Username;
		
		-- Right rules:
		-- Administrators are allowed to create and edit all layouts
		-- Regular users can only create/edit private layouts
		-- Regular users can create/edit public layouts if the setting "UserEditPublicLayouts" = 1 but they need write rights on the location of the tab
		
		-- Regular users can only create/edit private layouts
		IF @IsUserAdministrator = 0 AND @SettingUserEditPublicLayouts = 0 AND @IsPublicLayout = 1 AND @SettingMultipleLayouts = 1
		BEGIN
			RAISERROR('Insufficient rights to save this layout.', 16, 10);
		END

		-- Are private layouts allowed?
		IF @IsUserAdministrator = 0 AND @IsPublicLayout = 0 AND @SettingPrivateLayouts = 0
		BEGIN
			RAISERROR('Private layouts are not allowed.', 16, 10);
		END

		-- Regular users can create/edit public layouts but need to have write rights
		IF @IsUserAdministrator = 0 AND @SettingUserEditPublicLayouts = 1 AND @IsPublicLayout = 1
		BEGIN
			-- Cluster level
			IF @FactoryID = ''
			BEGIN
				EXEC @ResultCode = sx_pf_pGET_ClusterWriteRight @TransactUsername;
				IF @ResultCode <> 200 RAISERROR('Insufficient rights to save this layout.', 16, 10);
			END
			ELSE
			BEGIN
				-- Factory level
				IF @ProductLineID = ''
				BEGIN
					EXEC @ResultCode = sx_pf_pGET_FactoryWriteRight @TransactUsername, @FactoryID;
					IF @ResultCode <> 200 RAISERROR('Insufficient rights to save this layout.', 16, 10);
				END
				ELSE
				BEGIN
					-- ProductLine level
					EXEC @ResultCode = sx_pf_pGET_ProductLineWriteRight @TransactUsername, @FactoryID, @ProductLineID;
					IF @ResultCode <> 200 RAISERROR('Insufficient rights to save this layout.', 16, 10);
				END
			END
		END

		-- New layout?
		IF @LayoutID = '*' OR NOT EXISTS(SELECT 1 FROM planning.tTabLayouts WHERE FactoryID = @FactoryID AND ProductLineID = @ProductLineID AND TabID = @TabID AND LayoutID = @LayoutID)
		BEGIN
			IF NOT EXISTS(SELECT 1 FROM planning.tTabs WHERE FactoryID = @FactoryID AND ProductLineID = @ProductLineID AND TabID = @TabID)
				RAISERROR('Tab doesn''t exist.', 16, 10);
			
			-- Get next free LayoutID
            IF @LayoutID = '*'
            BEGIN
                SELECT @LayoutID = COALESCE(MAX(CAST(LayoutID AS INT)), 0) + 1
                FROM planning.tTabLayouts
                WHERE
                    FactoryID = @FactoryID
                    AND ProductLineID = @ProductLineID
                    AND TabID = @TabID;
			END
			
			-- Are multiple layouts allowed?
			IF @IsUserAdministrator = 0 AND @LayoutID > 1 AND @SettingMultipleLayouts = 0
			BEGIN
				RAISERROR('Can''t create another layout because multiple layouts are not allowed.', 16, 10);
			END
			
			-- Insert new layout
			INSERT INTO planning.tTabLayouts (FactoryID, ProductLineID, TabID, LayoutID, LayoutName, LayoutJSON, OrderIndex, LayoutOwner)
			VALUES (@FactoryID, @ProductLineID, @TabID, @LayoutID, @LayoutName, @LayoutJSON, @OrderIndex, @LayoutOwner);
			
		END
		ELSE
		BEGIN
			-- Update existing layout
			UPDATE planning.tTabLayouts
			SET
				LayoutName = @LayoutName
			  , LayoutJSON = @LayoutJSON
			  , OrderIndex = @OrderIndex
			  , LayoutOwner = @LayoutOwner
			WHERE
				FactoryID = @FactoryID
				AND ProductLineID = @ProductLineID
				AND TabID = @TabID
				AND LayoutID = @LayoutID;
		END
		
		SET @AffectedRows = @@ROWCOUNT;
		SET @OutputID = @LayoutID;
		
		-- Rearrange OrderIndex
		WITH Layouts AS (
			SELECT
				*
				, ROW_NUMBER() OVER (PARTITION BY LayoutOwner ORDER BY OrderIndex, CASE WHEN LayoutID = @LayoutID THEN 0 ELSE 1 END) AS NewOrderIndex
			FROM planning.tTabLayouts
			WHERE
				FactoryID = @FactoryID
				AND ProductLineID = @ProductLineID
				AND TabID = @TabID
		)
		UPDATE Layouts
		SET OrderIndex = NewOrderIndex;

		COMMIT TRANSACTION planning_spPOST_TabLayout;
		
		SET @ResultCode = 200
	END TRY
	BEGIN CATCH
		ROLLBACK TRANSACTION planning_spPOST_TabLayout;

		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'planning'			    -- enter schema name of the table
    ,@level1name        NVARCHAR(255)  = N'spPOST_TabLayout'     -- 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'@FactoryID';
    SET @value      = N'optional FactoryID, skip / NULL / empty string if request 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 request is for Cluster or Factory level';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

	SET @level2name = N'@TabID';
    SET @value      = N'TabID which is requested';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

	SET @level2name = N'@LayoutID';
    SET @value      = N'ID of the layout which is updated. If LayoutID is * then a new layout is created.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

	SET @level2name = N'@LayoutName';
    SET @value      = N'The name of the layout.';
    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'@OrderIndex';
    SET @value      = N'Index to help with ordering the layouts. Needs to have unique values for all combinations of FactoryID, ProductLineID, TabID & LayoutOwner.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

	SET @level2name = N'@LayoutOwner';
    SET @value      = N'Username if it is a private layout. Empty if it is a public layout.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
GO


UPDATE system.tSettings SET ValueText = '5.11.21', ValueInt = 51121 WHERE SettingID = 'DBVersion'