USE [Elyse_DB]
GO
/****** Object:  StoredProcedure [controlling].[usp_INS_doc_group_name]    Script Date: Sat 05-09-2026 7:01:55 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Silkwood Software
-- Create date: 07-08-2023
-- Description:	Initial creation
-- Creates a new document group name
-- by inserting a record into doc_group_names.
-- Input is the paramters for a document group name:
-- mnemonic, name, description.  
-- Output is a status message, a transaction status and the id of the new record.  
/*
COPYRIGHT NOTICE
This database schema and stored procedures are protected by copyright.
Copyright.  Silkwood Software Pty. Ltd. 2023
*/

-- =============================================
CREATE PROCEDURE [controlling].[usp_INS_doc_group_name] 

	 @mnemonic nvarchar(10)           = NULL,
	 @attribute_name nvarchar(50)     = NULL,
	 @description nvarchar(max)       = NULL,
	 @message nvarchar(1000)          = '' OUTPUT,
	 @transaction_status nvarchar(50) = NULL OUTPUT,
	 @newrecordid bigint              = NULL OUTPUT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

  DECLARE 
		@nopermissionmessage nvarchar(200)      = '', -- Communicates that the user does not have the required permission
	    @connectedusersid varbinary(100)        = SUSER_SID(ORIGINAL_LOGIN()),   -- The SID of the connected user
	    @username nvarchar(150)                 = ORIGINAL_LOGIN(),     -- The username 
		@userauthentication_status nchar(10)    = 'Fail', -- The outcome of the authentication check of the user 
        @tempmessage nvarchar(300)              = '',
	    @transactionmessage nvarchar(300)       = '', -- Communicates what was the outcome of the transaction
	    @validationmessage nvarchar(200)        = '', -- Communicates that the transaction failed due to data validation
	    @notuniquemessage nvarchar(200)         = '', -- Communicates that the attribute name is not unique
		@noattributenamemessage nvarchar(200)   = '', -- Communicates that no attribute name was supplied	
		@temp_userauth_status nchar(10)         = 'Fail',
		@tempvalidationstatus nchar(10)         = '',		
		@transactionmessage2 nvarchar(200)      = '',
		@transaction_ready nchar(10)            = 'Ready',
		@data_validation_status nchar(10)       = 'Pass';

  -- Parameters which have been initialised at declaration but not explicitly set might be output as null to calling functions.
  SET @transaction_status = 'Transaction not attempted';  


-- Connected user authentication
  -- Authenticate the connected user for the roles
  EXEC [internal].[usp_AUTHENTICATE_user_role] 
        @role_to_check = 'Controller',
		@user_authentication_result = @temp_userauth_status OUTPUT;
  IF @temp_userauth_status = 'Pass' SET @userauthentication_status = 'Pass';

  IF @userauthentication_status = 'Fail'
    BEGIN  -- The user does not have permission for this action
		SET @transaction_ready      = 'Fail';
	    EXEC internal.usp_SEL_message 
            @message_id   = 'NoPermission', 
			@message_text = @tempmessage OUTPUT;
	    IF (@tempmessage IS NOT NULL) 
  	       SET @nopermissionmessage = ISNULL(@username, '') + '  ' + @tempmessage;
	    ELSE 
		   SET @nopermissionmessage = 'A database level message error occurred on NoPermission.';
	END

  IF @userauthentication_status = 'Pass' -- Don't do anything if the user is not authorised.
    BEGIN
	  -- Data validation
	  -- Attribute name check
		IF @attribute_name = ''
		SET @attribute_name = NULL;
		-- Attribute name must be unique
		IF EXISTS (SELECT attr_name 
		 			FROM xref.doc_group_names 
					WHERE attr_name = @attribute_name) 
			BEGIN
				EXEC internal.usp_SEL_message 
					@message_id   = 'NameNotUnique', 
					@message_text = @tempmessage OUTPUT;
				IF (@tempmessage IS NOT NULL) 
  				SET @notuniquemessage = ISNULL(LEFT(@attribute_name, 10), '') + '... | ' + @tempmessage;
				ELSE 
					SET @notuniquemessage = 'A database level message error occurred on NameNotUnique';
				SET @data_validation_status = 'Fail';
				SET @transaction_ready      = 'Fail';
			END

	  -- Perform generic data validation checks.  (Does not require access to the table.) 
		EXEC internal.usp_VALIDATE_mnem_name_descr
			@mnem             = @mnemonic,
			@attr_name        = @attribute_name,
			@descr            = @description,
			@data_valn_status = @tempvalidationstatus OUTPUT,
			@messg            = @validationmessage OUTPUT;
		IF @tempvalidationstatus = 'Fail'
			BEGIN
				SET @transaction_ready      = 'Fail';
				SET @data_validation_status = 'Fail';
			END

		-- Output the data validation status failed message
		IF @data_validation_status = 'Fail'
		BEGIN
			EXEC internal.usp_SEL_message 
				@message_id   = 'FailedDataValidation', 
				@message_text = @tempmessage OUTPUT;
			IF (@tempmessage IS NOT NULL) 
				SET @transactionmessage = @tempmessage;
			ELSE 
				SET @transactionmessage = 'A database level message error occurred on FailedDataValidation.';
		END
		-- End data validation
    END -- End user authentication = Pass
 
-- Execute the insert query
  IF @transaction_ready = 'Ready'
	BEGIN
	  BEGIN TRY
	     INSERT INTO xref.doc_group_names 
		             (mnem,      attr_name,       descr)
		      VALUES (@mnemonic, @attribute_name, @description); 
	    SET @newrecordid = SCOPE_IDENTITY();
         EXEC internal.usp_SEL_message 
              @message_id   = 'Success', 
              @message_text = @tempmessage OUTPUT;
  	     IF (@tempmessage IS NOT NULL) 
	        SET @transactionmessage = @tempmessage;
	     ELSE 
		      SET @transactionmessage = 'A database level message error occurred on Success.';
		 SET @transaction_status = 'Good';
   	  END TRY
	  BEGIN CATCH

	     IF XACT_STATE() <> 0
            ROLLBACK TRANSACTION;

	     SET @transaction_status = 'Bad';
         EXEC internal.usp_SEL_message 
              @message_id   = 'InsertError', 
              @message_text = @tempmessage OUTPUT;
		 IF (@tempmessage IS NOT NULL) 
		    SET @transactionmessage = @tempmessage + ' | ' + 
		    CONVERT(nvarchar(10),ERROR_NUMBER()) + ' | ' + ERROR_MESSAGE();
		 ELSE 
		      SET @transactionmessage = 'A database level message error occurred on InsertError.';
	  END CATCH
    END


-- Concatenate the messages
/*
NOTE: "The + (String Concatenation) operator behaves differently when it works with an empty, 
zero-length string than when it works with NULL, or unknown values. A zero-length 
character string can be specified as two single quotation marks without any characters 
inside the quotation marks. A zero-length binary string can be specified as 0x without 
any byte values specified in the hexadecimal constant. Concatenating a zero-length string 
always concatenates the two specified strings. When you work with strings with a null value, 
the result of the concatenation depends on the session settings. Just like arithmetic 
operations that are performed on null values, when a null value is added to a known 
value the result is typically an unknown value, a string concatenation operation that 
is performed with a null value should also produce a null result." 
*/
    IF (@message = '' OR @message IS NULL) SET @message = ' ';
    IF (@transactionmessage <> '')         SET @message = @transactionmessage;
    IF (@validationmessage <> '')          SET @message = @message + ' | ' + @validationmessage;
	IF (@notuniquemessage <> '')           SET @message = @message + ' | ' + @notuniquemessage;
	IF (@noattributenamemessage <> '')     SET @message = @message + ' | ' + @noattributenamemessage;
    IF (@transactionmessage2 <> '')        SET @message = @message + ' | ' + @transactionmessage2;
	IF (@nopermissionmessage <> '')        SET @message = @message + ' | ' + @nopermissionmessage;


END
GO