USE [Elyse_DB]
GO
/****** Object:  StoredProcedure [reading].[usp_SEL_all_files_by_filename]    Script Date: Mon 07-09-2026 7:26:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Silkwood Software
-- Create date: 26-06-2024
-- Description:	Original Creation
-- Selects file data according to filename search using the LIKE operator.
-- The search is restricted to files which the user has viewing rights to. 
/*
COPYRIGHT NOTICE
This database schema and stored procedures are protected by copyright.
Copyright.  Silkwood Software Pty. Ltd. 2024
*/

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

	  @likestring nvarchar(max)        = '', 
	  @formid bigint                   = NULL,  
      @message nvarchar(1000)          = NULL OUTPUT,
      @transaction_status nvarchar(50) = NULL OUTPUT,
	  @numrows bigint                  = NULL OUTPUT, -- This is the number of rows in the output table of this procedure.
	  @numfiles bigint                 = NULL OUTPUT,  -- This is the number of files.  
      @outputformid bigint             = NULL OUTPUT,
	  @formname 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)         = '',
	    @connectedusersid varbinary(100)   = SUSER_SID(ORIGINAL_LOGIN()),   -- The SID of the connected user	
	    @username nvarchar(150)            = ORIGINAL_LOGIN(),    
	    @userauthentication_status nchar(10)    = 'Fail', -- The outcome of the authentication check of the user 
	    @temp_userauth_status nchar(10)         = 'Fail',
		@fileid bigint,
		@transaction_ready nchar(10)       = 'Ready',
		@data_validation_status nchar(10)  = 'Pass';

DECLARE @AttData TABLE
  (
        num_records bigint,
  		file_count bigint,
		fileid bigint,
		field_type nvarchar(50), 
		field_name_id bigint,
		field_mnemonic nvarchar(15),
		field_name  nvarchar(50),
		attr_id  bigint,
		attr_value nvarchar(max),
		units nvarchar(50),
		position nvarchar(50)
  )
	
  -- 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';  
  SET @numrows = 0;
  SET @numfiles = 0;  

-- Connected user authentication
 -- Authenticate the connected user for the role

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


  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

-- End user authentication. 

  IF @userauthentication_status = 'Pass' -- Don't do anything if the user is not authorised.
    BEGIN
 -- Data validation

		-- Escape the string so users don't get wildcard results
		 SET @likestring = REPLACE(@likestring, '\', '\\');
		 SET @likestring = REPLACE(@likestring, '%', '\%');
		 SET @likestring = REPLACE(@likestring, '_', '\_');
         SET @likestring = REPLACE(@likestring, '[', '\[');
         SET @likestring = REPLACE(@likestring, ']', '\]');
         SET @likestring = REPLACE(@likestring, '^', '\^');
 
 
    -- Form id validation
	  IF @formid = 0 
		 SET @formid = NULL;
	  IF @formid IS NOT NULL
		 BEGIN 
			 IF NOT EXISTS (SELECT form_id 
							  FROM forms.form_identifier_names 
							 WHERE form_id = @formid) 
				 BEGIN  -- The form id does not exist
				   SET @data_validation_status = 'Fail';
				   SET @transaction_ready      = 'Fail';
				   EXEC internal.usp_SEL_message 
						@message_id = 'FormNotExist', 
						@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 FormNotExist.');
				 END 
		 END   
	 ELSE -- If no form ID supplied then retrieve the default and then check the default
	     BEGIN
		     SET @formid = (SELECT default_form_id 
			                  FROM base.global_settings_groups
			                 WHERE base.global_settings_groups.setting_group_name = 'Master');
			 IF  @formid IS NULL
				 BEGIN  -- The form ID does not exist
				   EXEC internal.usp_SEL_message 
						@message_id = 'DefFormNotExist', 
						@message_text = @tempmessage OUTPUT;
				   SET @data_validation_status = 'Fail';
				   SET @transaction_ready      = 'Fail';
  				   IF @tempmessage IS NOT NULL 
						  SET @message = CONCAT_WS(' | ', @message, @tempmessage);
					   ELSE  
						  SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on DefFormNotExist.');
				 END 
		 END -- End checking filter group 


		  -- 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 -- End IF @userauthentication_status = 'Pass' 		  
 -- End data validation

 -- ==================================================================

	IF @transaction_ready = 'Ready'
	  BEGIN
		BEGIN TRY

	  WITH fileidsCTE AS(
		  SELECT DISTINCT
				 searchtable.file_id              AS ID
		    FROM base.file_metadata AS searchtable
   INNER JOIN internal.ufn_SEL_files_permission_filtr_ITVF(@connectedusersid) AS perm  
           ON searchtable.file_id = perm.file_id  -- Apply permission filtering
	    WHERE 
					(@likestring IS NULL AND searchtable.filename IS NULL)  -- Caters for searching for records where filename is NULL
					OR (searchtable.filename LIKE '%' + @likestring + '%' ESCAPE '\')
				  
		 ),

				 distinctfileidsCTE AS(
				 SELECT DISTINCT fileidsCTE.ID,
				 ROW_NUMBER() OVER (ORDER BY fileidsCTE.ID) AS row_number -- Including this in fileidsCTE causes duplicate records to be created
				 FROM fileidsCTE)


		-- Extract the data according to the file id
		   INSERT INTO @AttData  (num_records, file_count, fileid, field_type, field_name_id, field_name, field_mnemonic, attr_id, attr_value, units, position)
				(SELECT 
				        DENSE_RANK() OVER (ORDER BY fi.row_number) AS num_records,
						DENSE_RANK() OVER (ORDER BY fi.row_number, distinctfileidsCTE.ID) AS file_count,  -- This informs the calling application how to parse the data into rows since 
																						  -- each row in this table is a field. 
						distinctfileidsCTE.ID    AS file_id, 
						file_data.field_type     AS field_type, 
						file_data.field_name_id  AS field_name_id, 
						file_data.field_name     AS field_name,
					    file_data.field_mnemonic AS field_mnemonic, 
						file_data.attr_id        AS attr_id, 
						file_data.attr_value     AS attr_value, 
						file_data.units          AS units,
					    file_data.form_position  AS position
				   FROM distinctfileidsCTE AS fi
			CROSS APPLY internal.ufn_SEL_one_fl_data_by_frm_TVF(fi.ID, @formid) AS file_data
			 RIGHT JOIN distinctfileidsCTE
					 ON distinctfileidsCTE.ID 
						= fi.ID);

				 SELECT @numfiles   = MAX(file_count)  from @AttData;

			 SELECT 
					ad.file_count                 AS 'Record',
					ad.fileid                     AS 'File ID', 
					ad.field_type                 AS 'Field Type', 
					ad.field_name_id              AS 'Name ID', 
					ISNULL(ad.field_name, '')     AS 'Name', 
				    ISNULL(ad.field_mnemonic, '') AS 'Mnemonic',
					ad.attr_id                    AS 'Attribute ID', 
					ISNULL(ad.attr_value, '')     AS 'Value', 
					ISNULL(ad.units, '')          AS 'Units',
				    ISNULL(ad.position, '')       AS 'Position'
			   FROM @AttData as ad
		   ORDER BY Position, 'Record', 'File ID';


-- ===================================================

		  SET @numrows = @@ROWCOUNT;


 -- Return the actual form details applied.  
     SET @outputformid = @formid;
	 SET @formname = (SELECT form_name 
	                    FROM forms.form_identifier_names AS fin
					   WHERE fin.form_id
					         = @outputformid)


		  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';
		  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'
   
	SET @numrows  = ISNULL(@numrows, 0);
	SET @numfiles = ISNULL(@numfiles, 0);


END
GO