USE [Elyse_DB]
GO
/****** Object:  StoredProcedure [controlling].[usp_DEL_doc_group_name_rstr]    Script Date: Sat 05-09-2026 7:01:55 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Silkwood Software
-- Initial creation: 08-12-2023
-- Description:	Deletes a document group name. 
-- Only for document groups which the user has permission to modify.
-- Input is a document group id
-- Output is 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_DEL_doc_group_name_rstr] 

     @id_to_delete bigint             = 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 
		@tempuserauth_status nchar(10)          = '',     -- Temporary value for authentication sp.
		@userauthentication_status nchar(10)    = 'Fail', -- The outcome of the authentication check of the user 
		                                                  -- Defaults to Fail until it is set to Pass.
        @tempmessage nvarchar(300)              = '',
  		@nopermissionmessage nvarchar(200)      = '', -- Communicates that the user does not have the required permission
		@nodocgroup_permissionmsg nvarchar(200)	= '', -- Communicates that the user does not have permission to modify the document group
	    @transactionmessage nvarchar(300)       = '', -- Communicates what was the outcome of the transaction
	    @validationmessage nvarchar(200)        = '', -- Communicates that the transaction failed due to data validation
		@nodocgroupid nvarchar(200)             = '', -- Communicates that a document group id has not been supplied
		@recordnotexistmessage nvarchar(200)    = '', -- Communicats that the supplied document group id does not exist
		@docgrpexistsmessage nvarchar(200)      = '', -- Communicates that a document to document group link exists
		@docpermexistsmessage nvarchar(200)     = '', -- Communicates that a document group view permission entry exists
		@transaction_ready nchar(10)            = 'Ready',  -- Defaults to Ready until it is set to Fail
		@data_validation_status nchar(10)       = 'Pass';   -- Defaults to Pass until it is set to Fail

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

	 -- 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 = @id_to_delete,
			@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 @nodocgroup_permissionmsg = 'Document group ID: ' + ISNULL(CONVERT(nvarchar(10), @id_to_delete), 'NULL') + '  ' + @tempmessage;
			ELSE 
				SET @nodocgroup_permissionmsg = 'A database level message error occurred on NoDocGroupPermission.';
		END


  IF @userauthentication_status = 'Pass' -- Don't do anything if the user is not authorised.
    BEGIN
	  -- Data validation
	  IF @id_to_delete = 0
		 SET @id_to_delete = NULL;
	  -- Check document group id has been supplied
	  IF @id_to_delete IS NULL
		 BEGIN
		   SET @data_validation_status = 'Fail';
		   SET @transaction_ready      = 'Fail';
		   EXEC internal.usp_SEL_message 
				@message_id = 'NoDocGroupId', 
				@message_text = @tempmessage OUTPUT;
  		   IF (@tempmessage IS NOT NULL) 
			  SET @nodocgroupid = @tempmessage;
		   ELSE 
			   SET @nodocgroupid = 'A database level message error occurred on NoDocGroupId.';
		 END

      -- Check for any references to documents.  Fail if any exist.
      IF EXISTS (SELECT dgl.doc_group_id
	              FROM xref.doc_group_links AS dgl
			LEFT JOIN xref.doc_group_names AS dgn
			       ON dgl.doc_group_id
				    = dgn.doc_group_id
				WHERE dgl.doc_group_id = @id_to_delete)
		 BEGIN
		   SET @data_validation_status = 'Fail';
		   SET @transaction_ready      = 'Fail';
		   EXEC internal.usp_SEL_message 
				@message_id = 'DocGrpLinkExists', 
				@message_text = @tempmessage OUTPUT;
  		   IF (@tempmessage IS NOT NULL) 
			  SET @docgrpexistsmessage = @tempmessage;
		   ELSE 
			   SET @docgrpexistsmessage = 'A database level message error occurred on DocGrpLinkExists.';
		 END


	  -- Check for any document group view permission entries.  Fail if any exist. 
      IF EXISTS (SELECT dgn.doc_group_id
	              FROM xref.doc_group_names AS dgn
			LEFT JOIN user_restr.doc_group_view_permissions AS dgvp
			       ON dgvp.doc_group_id
				     = dgn.doc_group_id
				WHERE dgn.doc_group_id = @id_to_delete)
		 BEGIN
		   SET @data_validation_status = 'Fail';
		   SET @transaction_ready      = 'Fail';
		   EXEC internal.usp_SEL_message 
				@message_id = 'ViewPermExists', 
				@message_text = @tempmessage OUTPUT;
  		   IF (@tempmessage IS NOT NULL) 
			  SET @docpermexistsmessage = @tempmessage;
		   ELSE 
			   SET @docpermexistsmessage = 'A database level message error occurred on ViewPermExists.';
		 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 @transactionmessage = @tempmessage;
		  ELSE 
			   SET @transactionmessage = 'A database level message error occurred on FailedDataValidation.';
		END
	  -- End data validation
    END -- End IF user authentication = Pass
 
-- Execute the delete query
  IF @transaction_ready = 'Ready'
	BEGIN
	  BEGIN TRY
	   BEGIN TRANSACTION;
	    DELETE FROM xref.doc_group_names 
		      WHERE doc_group_id = @id_to_delete;
	   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   = 'DeleteError', 
              @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 DeleteError.';
	  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 (@nodocgroupid <> '')               SET @message = @message + ' | ' + @nodocgroupid;
    IF (@recordnotexistmessage <> '')      SET @message = @message + ' | ' + @recordnotexistmessage;	
	IF (@nopermissionmessage <> '')        SET @message = @message + ' | ' + @nopermissionmessage;
	IF (@nodocgroup_permissionmsg <> '')   SET @message = @message + ' | ' + @nodocgroup_permissionmsg;
	IF (@docgrpexistsmessage <> '')        SET @message = @message + ' | ' + @docgrpexistsmessage;
	IF (@docpermexistsmessage <> '')       SET @message = @message + ' | ' + @docpermexistsmessage;	



END
GO