USE [Elyse_DB]
GO
/****** Object:  StoredProcedure [reading].[usp_SEL_file]    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: 09-07-2023
-- Description:	Initial creation
-- This procedure reads the contents of a file.
-- Input is the file ID.  Output is the binary file,
-- the filename, and a message. 
-- If the user does not have Reader, Reviewer, Editor or Controller privileges then
-- the file can only be accessed if it has a Release State Type of Authoritative and passes the default filter group filter. 
/*
COPYRIGHT NOTICE
This database schema and stored procedures are protected by copyright.
Copyright.  Silkwood Software Pty. Ltd. 2023
*/

-- =============================================
CREATE PROCEDURE [reading].[usp_SEL_file]

    @fileid bigint                   = NULL,
	@notes nvarchar(1000)            = '', -- Optional
	@app_reference nvarchar(1000)    = '', -- Optional
	@filename nvarchar(255)          = NULL OUTPUT,
	@filesize bigint                 = NULL OUTPUT,
    @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
        @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. 	
	    @userauthentication_status nchar(10)    = 'Fail', -- The outcome of the authentication check of the user 
	    @temp_userauth_status nchar(10)         = 'Fail',				
		@filtergroupid bigint                   = NULL, -- The default filter group
	    @connectedusersid varbinary(100)         = SUSER_SID(ORIGINAL_LOGIN()),   -- The SID of the connected user
		@sidid bigint                            = NULL,  
		@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';  

  -- Check if the user has any of the following privileges.
  -- This is a check NOT to determine whether the user can execute this stored procedure.
  -- All users can execute this stored procedure.  
  -- This check is used later to check if the user is permitted to access the file. 

  EXEC [internal].[usp_AUTHENTICATE_user_role] 
        @role_to_check = 'Controller',
		@user_authentication_result = @temp_userauth_status OUTPUT;
  IF @temp_userauth_status = 'Pass' SET @userauthentication_status = 'Pass';

  EXEC [internal].[usp_AUTHENTICATE_user_role] 
        @role_to_check = 'Editor',
		@user_authentication_result = @temp_userauth_status OUTPUT;
  IF @temp_userauth_status = 'Pass' SET @userauthentication_status = 'Pass';

  EXEC [internal].[usp_AUTHENTICATE_user_role] 
        @role_to_check = 'Reviewer',
		@user_authentication_result = @temp_userauth_status OUTPUT;
  IF @temp_userauth_status = 'Pass' SET @userauthentication_status = 'Pass';

  EXEC [internal].[usp_AUTHENTICATE_user_role] 
        @role_to_check = 'Reader',
		@user_authentication_result = @temp_userauth_status OUTPUT;
  IF @temp_userauth_status = 'Pass' SET @userauthentication_status = 'Pass';


  	-- Retrieve the default filter group
	SET @filtergroupid = (SELECT default_filter_group_id 
			                FROM base.global_settings_groups
			               WHERE base.global_settings_groups.setting_group_name = 'Master');
			 


   -- Check that the file id exists.  Fail if it doesn't.
  IF @fileid = 0
     SET @fileid = NULL;
  IF @fileid IS NULL
     BEGIN
	   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 permission to access the file
			EXEC [internal].[usp_AUTHENTICATE_user_file_id] 
 				@file_id_to_check =  @fileid,
				@user_authentication_result = @fileauthenticationstatus OUTPUT;
				IF @fileauthenticationstatus = 'Fail'
					BEGIN -- File id does not exist or the user does not have permission to access it
						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(20), @fileid), '.  ', @tempmessage);
						ELSE 
		                    SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on FileIDNotExist.');
					END
				ELSE
				    BEGIN

					-- If the user does not have Reader, Reviewer, Editor or Controller privileges, check if the file is linked to a document release ID.  
					-- If it is linked to a document release, only pass if the release state type is Authoritative and within the effective_from and effective_until dates.
					IF @userauthentication_status = 'Fail'						
					  BEGIN
						IF EXISTS (
							SELECT 1
							FROM base.file_metadata AS f
							LEFT JOIN base.release_identifiers AS r
								ON f.release_id_pk = r.release_id_pk
							WHERE f.file_id = @fileid
							  -- Fails if it has a release_id_pk AND state is NOT 'Authoritative' (or missing from lookup) or fails effective date
							  AND f.release_id_pk IS NOT NULL
							  AND (r.release_state_type_id <> N'Authoritative' 
							       OR r.release_state_type_id IS NULL
								   OR (r.release_state_type_id = 'Authoritative' 
								     AND (
									       (r.effective_from IS NOT NULL AND r.effective_from > SYSDATETIME()) 
									       OR (r.effective_until IS NOT NULL AND r.effective_until < SYSDATETIME()) 
										  )
									   )
								   )
						)
                             BEGIN
										SET @data_validation_status = 'Fail';
										SET @transaction_ready      = 'Fail';
										EXEC internal.usp_SEL_message 
											@message_id = 'RelIdFail', 
											@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 RelIdFail.');				
						     END

                       END



					-- If the user does not have Reader, Reviewer, Editor or Controller privileges then
					-- the file cannot be accessed if it does not passes the default filter group filter. 
					IF @userauthentication_status = 'Fail' AND @filtergroupid IS NOT NULL
					   BEGIN
						IF NOT EXISTS -- Check if it does not pass the default filter group filter
								(
									SELECT 1
									WHERE NOT EXISTS
									(
										-- Fail if any required radio button attribute is missing
										SELECT 1
										FROM file_attr.file_radiob_attr_fgroup_links AS fgl
										WHERE fgl.filter_group_id = @filtergroupid
										  AND NOT EXISTS
										  (
											  SELECT 1
											  FROM file_attr.file_radio_button_links AS frbl
											  WHERE frbl.file_radiob_attr_id = fgl.file_radiob_attr_id
												AND frbl.file_id = @fileid
										  )
									)
									AND NOT EXISTS
									(
										-- Fail if any required multi-select attribute is missing
										SELECT 1
										FROM file_attr.file_ms_attr_fgroup_links AS fgl
										WHERE fgl.filter_group_id = @filtergroupid
										  AND NOT EXISTS
										  (
											  SELECT 1
											  FROM file_attr.file_multi_select_links AS fmsl
											  WHERE fmsl.file_ms_attr_id = fgl.file_ms_attr_id
												AND fmsl.file_id = @fileid
										  )
									)
									AND NOT EXISTS
									(
										-- Fail if the file does not have a release in the required state
										SELECT 1
										FROM file_attr.file_rel_state_fgroup_links AS fgl
										WHERE fgl.filter_group_id = @filtergroupid
										  AND NOT EXISTS
										  (
											  SELECT 1
											  FROM base.file_metadata AS fmd
											  INNER JOIN base.release_identifiers AS ri
											          ON fmd.release_id_pk = ri.release_id_pk
											  WHERE fmd.file_id = @fileid
											    AND ri.release_state_name_id = fgl.release_state_name_id
										  )
									)
								)
									BEGIN -- File fails the filter group.  
										SET @data_validation_status = 'Fail';
										SET @transaction_ready      = 'Fail';
										EXEC internal.usp_SEL_message 
											@message_id = 'FileFilterFail', 
											@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 FileFilterFail.');
									END
								END
					END -- END IF @userauthentication_status = 'Fail'
				
	 END   -- End checking if file id exists

  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

-- Select the filename, file size and content.  Other metadata can be retrieved elsewhere.  
-- Select the filename and file size separate from the content so that they will be in a separate
-- cursor element.  This reduces the risk that an application programming error will
-- embed the metadata into the file content.  It is expected that the transaction load on
-- this database will generally be low.

  IF @transaction_ready = 'Ready'
    BEGIN
      BEGIN TRY
	  
  -- 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 @filename = filename,
		          @filesize = file_size
			 FROM base.file_metadata
			WHERE file_id = @fileid;

           IF @filename IS NULL OR @filename = '' -- If the file_name field was NULL or empty then substitute the file id.
			    SET @filename = CONCAT('FileID', CONVERT(nvarchar(255),@fileid), '.unknown');

	   --   Write an entry in the file read log if it is enabled.
	  IF EXISTS (SELECT gsg.file_read_log 
	               FROM base.global_settings_groups AS gsg
		          WHERE gsg.setting_group_name = 'Master'
				    AND gsg.file_read_log = 'On')
		   BEGIN
		     INSERT INTO base.file_read_log
			             (file_id,  filename,  read_by_sid_id, notes, app_reference)
				  VALUES (@fileid, @filename, @sidid, @notes, @app_reference)  -- read_by_username is populated by the table column binding to (original_login())

		   END

           SELECT file_content AS 'File Content' 
             FROM base.file_storage 
	        WHERE file_id = @fileid;

	   IF @@ROWCOUNT > 0 -- If rowcount = 0 the TRY will still not be failed
	      BEGIN
	         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
	   ELSE
	      BEGIN
            SET @transaction_status = 'Bad';
           EXEC internal.usp_SEL_message 
                @message_id   = 'NotExist',
				@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 NotExist.');
		  END
	  END TRY
	  BEGIN CATCH
        SET @transaction_status = 'Bad';
        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

	END -- End if transaction ready = 'Ready'

END
GO