USE [Elyse_DB]
GO
/****** Object:  StoredProcedure [controlling].[usp_INS_duty_function_sid_link]    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: 08-01-2024
-- Description:	Initial creation
-- Inserts a record into people.duty_function_sid_links
-- Input is a function is, a sid record id and notes 
-- Output is a status message, and transaction status.  
/*
COPYRIGHT NOTICE
This database schema and stored procedures are protected by copyright.
Copyright.  Silkwood Software Pty. Ltd. 2023
*/
-- =============================================
/**
* Inserts a record into people.duty_function_sid_links
* 
* **Acceptable Inputs:**
*
* - @functionid:  Must be a valid id from the duty_functions table. Not permitted to be empty or NULL
* - @sidrecordid: Must be a valid id from the sid_list table. Not permitted to be empty or NULL
* - @inputnotes: Optional
* - The pair @functionid and @sidrecordid must be unique
*
*
* **Return Values:**
*
* - @message nvarchar(1000) OUTPUT: Descriptive status message of the procedure's execution.
* - @transaction_status nchar(50) OUTPUT: Indicates the transaction status ('Good', 'Bad', or default 'Transaction not attempted').
*
* **Error and Exception Conditions:**
*
* - User Role Validation Fail: Returns 'No Permission' message.
* - Data Validation Fail: Returns messages for invalid input data.
*
* **Side Effects:**
*
* - Adds a new record to people.duty_function_sid_links 
*
* **Preconditions:**
*
* - The user executing the procedure must have the 'Controller' role.
*
* **Postconditions:**
* - The procedure returns status messages indicating the outcome of the operation.
*
*/
-- =========================================================
CREATE PROCEDURE [controlling].[usp_INS_duty_function_sid_link] 

     @functionid bigint               = NULL,
	 @sidrecordid bigint              = NULL,             -- A valid id from the sid_list table, not an actual SID
	 @inputnotes nvarchar(1000)       = '',    -- Optional
	 @valid_from datetime2(7)         = NULL,  -- Optional
	 @valid_until datetime2(7)        = NULL,  -- Optional
	 @app_reference nvarchar(1000)    = '',    -- Optional
	 @message nvarchar(1000)          = '' 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 
        @tempmessage nvarchar(300)           = '',
		@tempuserauth_status nchar(10)       = '',
	    @connectedusersid varbinary(100)     = SUSER_SID(ORIGINAL_LOGIN()),   -- The SID of the connected user
	    @username nvarchar(150)              = ORIGINAL_LOGIN(),     -- The username 
	    @validationmessage nvarchar(200)     = '', -- Communicates that the transaction failed due to data validation
		@userauthentication_status nchar(10) = 'Fail', -- The outcome of the authentication check of the user 
		@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_user_role] 
        @role_to_check = 'Controller',
		@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


	 -- Check the function id
		  IF @functionid = 0
			 SET @functionid = NULL; 
		  -- Check record id has been supplied
		  IF @functionid IS NULL
			 BEGIN
			   SET @data_validation_status = 'Fail';
			   SET @transaction_ready      = 'Fail';
			   EXEC internal.usp_SEL_message 
					@message_id = 'NoFunctionID', 
					@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 NoFunctionID.');
			 END
		  ELSE -- A record id has been supplied
		    -- Check if the record id exists
			IF NOT EXISTS (SELECT df.function_id
			                 FROM people.duty_functions AS df
							WHERE df.function_id = @functionid)
				 BEGIN
				   SET @data_validation_status = 'Fail';
				   SET @transaction_ready      = 'Fail';
				   EXEC internal.usp_SEL_message 
						@message_id = 'FunctionIDNotExist', 
						@message_text = @tempmessage OUTPUT;
  				   IF (@tempmessage IS NOT NULL) 
					  SET @message = CONCAT(@message, ' | ', ISNULL(CONVERT(nvarchar, @functionid), 'NULL'), '.  ', @tempmessage);
				   ELSE 
					  SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on FunctionIDNotExist.');
				 END


	 -- Validate @sidrecordid
	   -- Check that the sid is valid
		 IF @sidrecordid = 0
		    SET @sidrecordid = NULL;
		 IF @sidrecordid IS NULL 
		    BEGIN
					 SET @data_validation_status = 'Fail';
					 SET @transaction_ready      = 'Fail';
					 EXEC internal.usp_SEL_message 
						  @message_id   = 'NoUserSidID', 
						  @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 NoUserSidID');	
			END
		ELSE -- @sidrecordid is not NULL
		  BEGIN
			   IF NOT EXISTS (SELECT sid_id 
				  				FROM user_restr.sid_list 
								WHERE sid_id = @sidrecordid) 
				   BEGIN
					 SET @data_validation_status = 'Fail';
					 SET @transaction_ready      = 'Fail';
					 EXEC internal.usp_SEL_message 
						  @message_id   = 'UserIdNotExist', 
						  @message_text = @tempmessage OUTPUT;
					 IF (@tempmessage IS NOT NULL) 
  						SET @message = CONCAT(@message, ' | ', ISNULL(CONVERT(nvarchar, @sidrecordid), 'NULL'), '.  ', @tempmessage);
					 ELSE 
						  SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on UserIdNotExist');
				   END
		   END

	  -- Date validation
	  IF @valid_from IS NOT NULL
         AND @valid_until IS NOT NULL
         AND @valid_until < @valid_from
			      BEGIN
					SET @data_validation_status = 'Fail';
					SET @transaction_ready      = 'Fail';
					EXEC internal.usp_SEL_message 
						@message_id = 'DateInvalid', 
						@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 DateInvalid.');
				  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 @message = CONCAT_WS(' | ', @message, @tempmessage);
		  ELSE 
		    SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on FailedDataValidation');
		END
	  -- End data validation


-- Execute the insert query

  IF @transaction_ready = 'Ready'
	BEGIN
	  BEGIN TRY
	   BEGIN TRANSACTION 

	  -- If a duplicate exists then delete it first.
	  -- This allows for re-validating of a privilege in a single action.

	        DELETE FROM people.duty_function_sid_links
				  WHERE function_id = @functionid
					AND sid_id  = @sidrecordid

	     INSERT INTO people.duty_function_sid_links
		             (function_id,  sid_id,              notes,  granted_by,          valid_from,  valid_until, app_reference)
		      VALUES (@functionid, @sidrecordid, ISNULL(@inputnotes, ''), @username, @valid_from, @valid_until, @app_reference); 
         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';

         -- Write to the privilege log --
		 INSERT INTO user_restr.user_privilege_log
		             (privilege_type,                  privilege_name,           sid_id,      username, action_type, 
					 created_by_sid_id, valid_from, valid_until, notes, app_reference)
			  VALUES ('Duty Function to User Link', 
			           (SELECT df.name
			              FROM people.duty_functions AS df
					   	 WHERE df.function_id = @functionid),         
					   @sidrecordid, 
					   (SELECT username
		                  FROM user_restr.sid_list
						 WHERE sid_id = @sidrecordid), 
					   'Grant',
			         (SELECT sid_id
		                FROM user_restr.sid_list
					   WHERE sid = @connectedusersid), 
					 @valid_from, @valid_until, @inputnotes, @app_reference)

	   COMMIT TRANSACTION
   	  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 @message = CONCAT(@message, ' | ', @tempmessage, ' | ', 
		    CONVERT(nvarchar(10), ERROR_NUMBER()), ' | ', ERROR_MESSAGE());
		 ELSE 
		    SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on InsertError');
	  END CATCH
    END


 END -- End IF @userauthentication = 'Pass'


END
GO