USE [Elyse_DB]
GO
/****** Object:  StoredProcedure [reading].[usp_CHECK_doc_ed_permission]    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-08-2024
-- Description:	Checks whether the connected user has edit permission for the given document id.
-- Input is a document id.  Output is a result and messages.  
/*
COPYRIGHT NOTICE
This database schema, stored procedures and functions are protected by copyright.
Copyright.  Silkwood Software Pty. Ltd. 2024
*/
-- =============================================
CREATE PROCEDURE [reading].[usp_CHECK_doc_ed_permission] 

      @documentid nvarchar(50)         = NULL,
      @message nvarchar(1000)          = NULL OUTPUT,
      @transaction_status nvarchar(50) = NULL OUTPUT,
	  @validation_result nvarchar(10)  = 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     
	    @tempmessage nvarchar(300)         = '',
	    @validationmessage nvarchar(200)   = '',  -- Communicates that the transaction failed due to data validation
	    @docauthenticationstatus nchar(10) = '',  -- Communicates if the user is not authorised to access the document
		                                          -- or alternatively if the document does not exist.  The result does not distinguish either. 
	    @transaction_ready nchar(10)       = 'Ready',
	    @data_validation_status nchar(10)  = 'Pass';

BEGIN TRY
  -- Document id validation
	  IF @documentid = ''
		 SET @documentid = NULL;
	   -- Check that the doc id exists.  Fail if it doesn't.
	   -- Check if a doc id was supplied.
	  IF @documentid IS NULL
		 BEGIN
			 SET @data_validation_status = 'Fail';
			 SET @transaction_ready      = 'Fail';
			 SET @validation_result      = 'Fail';
			 EXEC internal.usp_SEL_message 
				  @message_id = 'NoDocID', @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 NoDocID.');
		 END
	  ELSE -- Doc id was supplied
		 BEGIN  -- Check if it exists and the user has permission to edit the document data
		   EXEC [internal].[usp_AUTHENTICATE_doc_ed_perm] 
 	            @doc_id_to_check_ep =  @documentid,
	            @user_authentication_result_dep = @docauthenticationstatus OUTPUT;
		   SET @validation_result   = @docauthenticationstatus;
		   IF @docauthenticationstatus = 'Fail'
				 BEGIN -- Doc id does not exist or the user does not have permission to edit it
				   SET @data_validation_status = 'Fail';
				   SET @transaction_ready      = 'Fail';
				   EXEC internal.usp_SEL_message 
						@message_id = 'NoDocIDAccess', 
						@message_text = @tempmessage OUTPUT;
  				   IF @tempmessage IS NOT NULL 
				      SET @message = CONCAT(@message, ' | ' ,  LEFT(@documentid, 20), '... ', @tempmessage);
			       ELSE  
				      SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on NoDocIDAccess.');
				 END	
			 ELSE
				 BEGIN -- The user has edit permission
				   EXEC internal.usp_SEL_message 
						@message_id = 'EdPermCheckPass', 
						@message_text = @tempmessage OUTPUT;
  				   IF @tempmessage IS NOT NULL 
				      SET @message = CONCAT(@message, ' | ' ,  LEFT(@documentid, 20), '... ', @tempmessage);
			       ELSE  
				      SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on EdPermCheckPass.');
				 END

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

	SET @validation_result = RTRIM(@validation_result);
  

END
GO