USE [Elyse_DB]
GO
/****** Object:  StoredProcedure [controlling].[usp_INS_contr_file_group_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: 05-09-2024
-- Description: Inserts a file to controller level file group link.
-- Only permits a file group and file which the user has rights to.
-- Input is a file id, controller level file group id and notes.
-- Output is a message and 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_contr_file_group_link]

     @fileid bigint                   = '', 
	 @contr_file_ed_grp_name_id bigint = NULL,
	 @inputnotes nvarchar(max)        = '',
	 @app_reference nvarchar(1000)    = '',
	 @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 
		@contfilegroupname nvarchar(50)         = '',
		@sidid bigint                           = NULL, 
		@userauthentication_status nchar(10)    = 'Fail', -- The outcome of the authentication check of the user 
		@tempuserauth_status nchar(10)          = '', 
		@tempmessage nvarchar(300)              = '',
		@fileauthenticationstatus nchar(10)      = '',  -- Communicates if the user is not authorised to access the file
		                                               -- or alternatively if the file does not exist.  The result does not distinguish either. 
		@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 = @tempuserauth_status OUTPUT;
  IF @tempuserauth_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 @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 @contr_file_ed_grp_name_id = 0
		   SET @contr_file_ed_grp_name_id = NULL;

		IF @contr_file_ed_grp_name_id IS NULL
		   BEGIN
			 EXEC internal.usp_SEL_message 
				  @message_id   = 'NoConFileGroup', 
				  @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 NoConFileGroup');
			 SET @data_validation_status = 'Fail';
			 SET @transaction_ready      = 'Fail';
		   END
		 ELSE

	 -- Authenticate the user for the given file group
	 -- This also returns fail if the id does not exist
    EXEC [internal].[usp_AUTHENTICATE_cont_file_grp]
	     @contr_file_grp_to_check = @contr_file_ed_grp_name_id,
		 @user_authentication_result_cep = @tempuserauth_status OUTPUT;
	  IF @tempuserauth_status = 'Fail'
		BEGIN  -- The user does not have permission for this action
			SET @data_validation_status = 'Fail';
			SET @transaction_ready      = 'Fail';
			EXEC internal.usp_SEL_message 
				@message_id   = 'InvalidConFileGrp', 
				@message_text = @tempmessage OUTPUT;
			IF (@tempmessage IS NOT NULL) 
  			   SET @message = CONCAT(@message, ' | ', @tempmessage);
			ELSE 
			   SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on InvalidConFileGrp');
		END


		  IF @fileid = 0
			 SET @fileid = NULL;

		  IF @fileid IS NULL
			 BEGIN -- A file id has not been supplied
			   SET @data_validation_status = 'Fail';
			   SET @transaction_ready      = 'Fail';
			   EXEC internal.usp_SEL_message 
					@message_id = 'NoFileID', 
					@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 NoFileID.');
			 END

		   ELSE
				BEGIN -- Check if the file ID exists and the user has edit permission for the file
					   EXEC [internal].[usp_AUTHENTICATE_file_ed_perm] 
 							@file_id_to_check_ep =  @fileid,
							 @user_authentication_result_fep = @fileauthenticationstatus OUTPUT;
					 IF @fileauthenticationstatus = 'Fail'
						 BEGIN -- File id does not exist or the user does not have edit permission for
						   SET @data_validation_status = 'Fail';
						   SET @transaction_ready      = 'Fail';
						   EXEC internal.usp_SEL_message 
								@message_id = 'FileIDNotExist', 
								@message_text = @tempmessage OUTPUT;
  						   IF @tempmessage IS NOT NULL 
							  SET @message = CONCAT(@message, ' | ', 'File ID: ', CONVERT(nvarchar(10), @fileid), '... ', @tempmessage);
						   ELSE 
							  SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on FileIDNotExist.');
						 END
				END


 
			-- Check if the group has been linked to that file already
			IF @data_validation_status = 'Pass'
			   AND EXISTS (SELECT file_id
							 FROM xref.controller_file_group_links
			 				WHERE controller_file_group_name_id = @contr_file_ed_grp_name_id
								  AND file_id = @fileid)
				BEGIN
					SET @data_validation_status = 'Fail';
					SET @transaction_ready      = 'Fail';
					EXEC internal.usp_SEL_message 
						@message_id = 'RecordExists', 
						@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 RecordExists.');
				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 authentiation = Pass
	
  -- Execute the insert query
  IF @transaction_ready = 'Ready'
	BEGIN
	  BEGIN TRY
	     INSERT INTO xref.controller_file_group_links 
		             (file_id,  controller_file_group_name_id,       notes)
		      VALUES (@fileid, @contr_file_ed_grp_name_id,     @inputnotes)

			 -- 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 @contfilegroupname = attr_name
			     FROM xref.controller_file_group_names
				WHERE controller_file_group_name_id =  @contr_file_ed_grp_name_id

	 			  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,  file_id,     
								  record_id,    record_name, original_value, new_value, notes, app_reference)
						  VALUES (@username,           @sidid,            'Insert',    'File to Controller File Group Link',  @fileid,   
								  @contr_file_ed_grp_name_id, @contfilegroupname, NULL,           @contfilegroupname, @inputnotes, @app_reference)
				   END

         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   = '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
GO