USE [Elyse_DB]
GO
/****** Object:  StoredProcedure [authorising].[usp_UPD_contr_file_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: 04-09-2024
-- Description:	Updates the mnemonic, name and description 
-- for the xref.controller_file_group_names table.
-- Input is the recordid, mnemonic, name and description.
-- The application must re-send all three values, even if only one is being updated.
-- Output is a message and transaction status.
/*
COPYRIGHT NOTICE
This database schema and stored procedures are protected by copyright.
Copyright.  Silkwood Software Pty. Ltd. 2024
*/

-- =============================================
CREATE PROCEDURE [authorising].[usp_UPD_contr_file_group_name] 

     @recordid bigint                 = NULL, 
	 @mnemonic nvarchar(10)           = NULL,
	 @attribute_name nvarchar(50)     = NULL,
	 @description nvarchar(max)       = NULL,
	 @message nvarchar(1000)          = NULL OUTPUT,
	 @transaction_status nvarchar(50) = NULL OUTPUT

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

  DECLARE 

	    @connectedusersid varbinary(100)        = SUSER_SID(ORIGINAL_LOGIN()),   -- The SID of the connected user
	    @username nvarchar(150)                 = ORIGINAL_LOGIN(),     -- The username exctracted from 
		@userauthentication_status nchar(10)    = 'Fail', -- The outcome of the authentication check of the user 
        @tempmessage nvarchar(300)              = '',
		@tempvalidationstatus nchar(10)         = '',
		@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 role
  EXEC [internal].[usp_AUTHENTICATE_authoriser] 
		@user_authentication_result = @userauthentication_status OUTPUT;
  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 @message = CONCAT(@message, ' | ', ISNULL(@username, ''), '  ', @tempmessage);
	    ELSE 
		   SET @message = CONCAT_WS(' | ', @message, '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
	  IF @recordid = 0
		 SET @recordid = NULL;
	  -- Check record id exists
	  IF @recordid IS NULL
		 BEGIN
		   SET @data_validation_status = 'Fail';
		   SET @transaction_ready      = 'Fail';
		   EXEC internal.usp_SEL_message 
				@message_id = 'NoRecordID', 
				@message_text = @tempmessage OUTPUT;
  		   IF (@tempmessage IS NOT NULL) 
			  SET @message = CONCAT_WS(' | ', @message, @tempmessage);
		   ELSE 
			  SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on NoRecordID.');
		 END
	  ELSE -- A record id has been supplied
		 BEGIN  -- Check that the record id exists
			 IF NOT EXISTS (SELECT controller_file_group_name_id
							  FROM xref.controller_file_group_names 
							 WHERE controller_file_group_name_id = @recordid) 
				 BEGIN -- Record id does not exist
				   SET @data_validation_status = 'Fail';
				   SET @transaction_ready      = 'Fail';
				   EXEC internal.usp_SEL_message 
						@message_id = 'NotExist', 
						@message_text = @tempmessage OUTPUT;
  				   IF (@tempmessage IS NOT NULL) 
					  SET @message = CONCAT_WS(' | ', @message, @tempmessage);
				   ELSE 
					  SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on NotExist.');
				 END
		 END

	  -- Attribute name must be unique, unless it matches the existing one for that record id
	  IF @data_validation_status = 'Pass' 
		 AND EXISTS (SELECT controller_file_group_name_id
					   FROM xref.controller_file_group_names
					  WHERE attr_name = @attribute_name
							AND controller_file_group_name_id <> @recordid)
		   BEGIN
			 EXEC internal.usp_SEL_message 
				  @message_id   = 'NameNotUnique', 
				  @message_text = @tempmessage OUTPUT;
			 IF @tempmessage IS NOT NULL 
  				SET @message = CONCAT(@message, ' | ', LEFT(@attribute_name, 10), '... | ', @tempmessage);
			 ELSE 
				SET @message = CONCAT_WS(' | ', @message, '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            = @tempmessage OUTPUT;
			IF @tempvalidationstatus = 'Fail'
				BEGIN
					SET @transaction_ready      = 'Fail';
					SET @data_validation_status = 'Fail';
					SET @message = CONCAT_WS(' | ', @message, @tempmessage);
				END


	  -- Output the failed data validation 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 @message = CONCAT_WS(' | ', @message, @tempmessage);
		  ELSE 
			 SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on FailedDataValidation.');
		END
	  -- End data validation

    END -- End IF user authentication = Pass  
-- Execute the insert query
  IF @transaction_ready = 'Ready'
	BEGIN
	  BEGIN TRY

	   BEGIN TRANSACTION;

	     UPDATE xref.controller_file_group_names 
		    SET      mnem = @mnemonic,
			    attr_name = @attribute_name, 
				    descr = @description
		  WHERE controller_file_group_name_id = @recordid; 

		COMMIT TRANSACTION;

         EXEC internal.usp_SEL_message 
              @message_id   = 'Success', 
              @message_text = @tempmessage OUTPUT;
  	     IF @tempmessage IS NOT NULL 
	        SET @message = CONCAT_WS(' | ', @message, @tempmessage);
	     ELSE 
		    SET @message = CONCAT_WS(' | ', @message, '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   = 'UpdateError', 
              @message_text = @tempmessage OUTPUT;
		 IF @tempmessage IS NOT NULL 
			SET @message = CONCAT(@message, ' | ', @tempmessage, ' | ', 
			CONVERT(nvarchar(10),ERROR_NUMBER()), ' | ', ERROR_MESSAGE());
		 ELSE 
		    SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on UpdateError.');
	  END CATCH
    END

	   
END
GO