USE [Elyse_DB]
GO
/****** Object:  StoredProcedure [reading].[usp_CHECK_file_ed_permission]    Script Date: Fri 02-10-2026 8:29:40 PM ******/
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 file id.
-- Input is a file 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_file_ed_permission] 

      @fileid bigint                   = 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
	    @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';

BEGIN TRY
  -- File id validation

	   -- Check that the file id exists.  Fail if it doesn't.
	   -- Check if a file id was supplied.
	  IF @fileid IS NULL
		 BEGIN
			 SET @data_validation_status = 'Fail';
			 SET @transaction_ready      = 'Fail';
			 SET @validation_result      = '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 -- File id was supplied
		 BEGIN  -- Check if it exists and the user has permission to edit the File data
		   EXEC [internal].[usp_AUTHENTICATE_file_ed_perm] 
 	            @file_id_to_check_ep =  @fileid,
	            @user_authentication_result_fep = @fileauthenticationstatus OUTPUT;
		   SET @validation_result   = @fileauthenticationstatus;
		   IF @fileauthenticationstatus = 'Fail'
				 BEGIN -- file 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 = 'NoFileIDAccess', 
						@message_text = @tempmessage OUTPUT;
  				   IF @tempmessage IS NOT NULL 
				      SET @message = CONCAT(@message, ' | ', 'File ID: ', CONVERT(nvarchar(20), @fileid), '.  ', @tempmessage);
			       ELSE  
				      SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on NoFileIDAccess.');
				 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, ' | ', 'File ID: ', CONVERT(nvarchar(20), @fileid), '.  ', @tempmessage);
			       ELSE  
				      SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on EdPermCheckPass.');
				 END				 
		 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