USE [Elyse_DB]
GO
/****** Object:  StoredProcedure [controlling].[usp_INS_doc_gp_ed_perm_funct]    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: 17-12-2023
-- Description:	Initial creation
-- Inserts a record into user_restr.doc_group_edit_perm_funct_lst to authorise users within a function list
-- to edit documents within a document group. 
-- Input is a document group ID, a function list ID and 
-- and notes.  
-- Output is a status message and a transaction status.  
/*
COPYRIGHT NOTICE
This database schema and stored procedures are protected by copyright.
Copyright.  Silkwood Software Pty. Ltd. 2023
*/
-- =============================================
CREATE PROCEDURE [controlling].[usp_INS_doc_gp_ed_perm_funct] 

     @docgroupid bigint               = NULL,  -- Document group 
	 @functionlistid bigint           = NULL,  -- User duty function list id
	 @inputnotes nvarchar(1000)       = '',    -- Notes for the entry
	 @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)           = '',
	    @transactionmessage nvarchar(300)    = '',     -- Communicates what was the outcome of the transaction
	    @validationmessage nvarchar(200)     = '',     -- Communicates that the transaction failed due to data validation
		@nopermissionmessage nvarchar(200)   = '',     -- Communicates that the user does not have the required permission
		@tempuserauth_status nchar(10)       = '',
		@recordexistsmessage nvarchar(200)   = '',     -- Communicates that a matching record already exists
        @nodocgroupmsg nvarchar(200)         = '',     -- Communicates that a document group id was not supplied
		@nofunctionlistidmsg nvarchar(200)   = '',     -- Communicates that no function list ID was supplied
		@functionlistinvalid nvarchar(200)   = '',     -- Communicates that the function list was invalid
		@docgroupinvalidmsg nvarchar(200)    = '', -- Communicates that the document group does not exist or the user does not have rights to it
	    @connectedusersid varbinary(100)     = SUSER_SID(ORIGINAL_LOGIN()),   -- The SID of the connected user
	    @username nvarchar(150)              = ORIGINAL_LOGIN(),     -- The username 
		@sidid bigint                        = NULL, 
		@functionlistname nvarchar(50)       = '',
		@docgroupname nvarchar(50)           = '',
		@userauthentication_status nchar(10) = 'Fail', -- The outcome of the authentication check of the user 
		@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 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 @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

	     IF @docgroupid = 0
		    SET @docgroupid = NULL;
		 IF @docgroupid IS NULL
		   BEGIN
			 EXEC internal.usp_SEL_message 
				  @message_id   = 'NoDocGroupId', 
				  @message_text = @tempmessage OUTPUT;
			 IF (@tempmessage IS NOT NULL) 
  				SET @nodocgroupmsg = @tempmessage;
			 ELSE 
				SET @nodocgroupmsg = 'A database level message error occurred on NoDocGroupId';
			 SET @data_validation_status = 'Fail';
			 SET @transaction_ready      = 'Fail';
		   END
		 ELSE
		   BEGIN
			 -- Authenticate the user for the given document group
			 -- This also returns negative if the id does not exist
			  EXEC [internal].[usp_AUTHENTICATE_user_doc_grp] 
					@docgroupid_to_check_vp = @docgroupid,
					@user_authentication_result = @tempuserauth_status OUTPUT;
			  IF @tempuserauth_status = 'Fail'
				BEGIN  -- The user does not have permission for this action
					SET @userauthentication_status = 'Fail';
					SET @transaction_ready         = 'Fail';
					EXEC internal.usp_SEL_message 
						@message_id   = 'NoDocGroupPermission', 
						@message_text = @tempmessage OUTPUT;
					IF (@tempmessage IS NOT NULL) 
  						SET @docgroupinvalidmsg = 'Document group ID: ' + ISNULL(CONVERT(nvarchar(10), @docgroupid), 'NULL') + '  ' + @tempmessage;
					ELSE 
						SET @docgroupinvalidmsg = 'A database level message error occurred on NoDocGroupPermission.';
				END
            END


      -- Check the function list id has been supplied
	  IF @functionlistid = 0
	     SET @functionlistid = NULL;
	  IF @functionlistid IS NULL  
	     BEGIN
			 EXEC internal.usp_SEL_message 
				  @message_id   = 'NoFunctionListID', 
				  @message_text = @tempmessage OUTPUT;
			 IF (@tempmessage IS NOT NULL) 
  				SET @nofunctionlistidmsg  = @tempmessage;
			 ELSE 
				SET @nofunctionlistidmsg  = 'A database level message error occurred on NoFunctionListID';
			 SET @data_validation_status = 'Fail';
			 SET @transaction_ready      = 'Fail';
		 END
		 ELSE
	        BEGIN -- Check the function list exists
				 IF NOT EXISTS (SELECT function_list_id
				                  FROM people.function_list_names
								 WHERE function_list_id = @functionlistid)
				   BEGIN
					 EXEC internal.usp_SEL_message 
						  @message_id   = 'FunctListIDInvalid', 
						  @message_text = @tempmessage OUTPUT;
					 IF (@tempmessage IS NOT NULL) 
  						SET @functionlistinvalid =  ISNULL(CONVERT(nvarchar(10), @functionlistid), 'NULL') + ' | '  + @tempmessage; 
					 ELSE 
						SET @functionlistinvalid = 'A database level message error occurred on FunctListIDInvalid';
					 SET @data_validation_status = 'Fail';
					 SET @transaction_ready      = 'Fail';
				   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 @transactionmessage = @tempmessage;
		  ELSE 
			   SET @transactionmessage = 'A database level message error occurred on FailedDataValidation.';
		END
	  -- End data validation
    END -- End of IF authentication status = Pass.

-- Execute the insert query
  IF @inputnotes IS NULL SET @inputnotes = '';

  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 user_restr.doc_group_edit_perm_funct_lst 
						 WHERE doc_group_id = @docgroupid
						   AND function_list_id = @functionlistid

	     INSERT INTO user_restr.doc_group_edit_perm_funct_lst
		             (doc_group_id, function_list_id,   notes, granted_by,     valid_from,  valid_until,  app_reference)
		      VALUES (@docgroupid, @functionlistid,   @inputnotes, @username, @valid_from, @valid_until, @app_reference); 

			 -- Create an audit log entry
			   -- Select the SID ID for the connected user
			  SELECT @sidid = sl.sid_id
				FROM user_restr.sid_list AS sl
			   WHERE sl.sid = @connectedusersid;

			   SELECT @functionlistname = name
			     FROM people.function_list_names
				WHERE function_list_id =  @functionlistid

			   SELECT @docgroupname = attr_name
			     FROM xref.doc_group_names
				WHERE doc_group_id =  @docgroupid

	 			  IF EXISTS (SELECT gsg.file_doc_change_log
						   FROM base.global_settings_groups AS gsg
						  WHERE gsg.setting_group_name = 'Master'
							AND gsg.file_doc_change_log = 'On')
				   BEGIN
					 INSERT INTO base.file_doc_data_log
								 (created_by_username, created_by_sid_id, change_type, change_field,       
								  record_id,    record_name, original_value, new_value, notes, app_reference)
						  VALUES (@username,           @sidid,            'Create',    'Document Group to Function List Link',     
								  @docgroupid, @docgroupname, NULL,        @functionlistname, @inputnotes, @app_reference)
				   END

         -- Write to the privilege log --
		 INSERT INTO user_restr.user_privilege_log
		             (privilege_type,                  privilege_name,           linked_to,    action_type, 
					 created_by_sid_id, valid_from, valid_until, notes, app_reference)
			  VALUES ('Document Group to Function List Link for Editing', 
			  @docgroupname,
			  (SELECT name
				 FROM people.function_list_names
				WHERE function_list_id = @functionlistid), 
			  'Grant',
			    (SELECT sid_id
		           FROM user_restr.sid_list
				  WHERE sid = @connectedusersid), 
					 @valid_from, @valid_until, @inputnotes, @app_reference)

	   COMMIT TRANSACTION
         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 (@nodocgroupmsg <> '')              SET @message = @message + ' | ' + @nodocgroupmsg;
	IF (@docgroupinvalidmsg <> '')         SET @message = @message + ' | ' + @docgroupinvalidmsg;
	IF (@recordexistsmessage <> '')        SET @message = @message + ' | ' + @recordexistsmessage;
    IF (@transactionmessage2 <> '')        SET @message = @message + ' | ' + @transactionmessage2;
	IF (@nopermissionmessage <> '')        SET @message = @message + ' | ' + @nopermissionmessage;
	IF (@nofunctionlistidmsg <> '')        SET @message = @message + ' | ' + @nofunctionlistidmsg;
	IF (@functionlistinvalid <> '')        SET @message = @message + ' | ' + @functionlistinvalid;



END
GO