USE [Elyse_DB]
GO
/****** Object:  StoredProcedure [reading].[usp_SEL_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.
-- A filter group is applied
/*
COPYRIGHT NOTICE
This database schema and stored procedures are protected by copyright.
Copyright.  Silkwood Software Pty. Ltd. 2026
*/

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

	  @likestring nvarchar(max)        = '', 
	  @formid bigint                   = NULL,  -- This is optional, providing that the global defaults contains a default value.
	  @filtergroupid bigint            = NULL,  -- This is optional, providing that the global defaults contains a default value.
      @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,
	  @outputfiltergroupid bigint      = NULL OUTPUT,
	  @filtergroupname 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(),    
		@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
 


 -- 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, '^', '\^');
    	  -- String validation  

 -- Filter group id validation
  	  -- Check if the filter group exists  
	  IF @filtergroupid = 0 
		 SET @filtergroupid = NULL;
	  IF @filtergroupid IS NOT NULL
		 BEGIN 
			 IF NOT EXISTS (SELECT filter_group_id 
							  FROM forms.filter_groups 
							 WHERE filter_group_id = @filtergroupid) 
				 BEGIN  -- The filter group does not exist
				   SET @data_validation_status = 'Fail';
				   SET @transaction_ready      = 'Fail';
				   EXEC internal.usp_SEL_message 
						@message_id = 'FGNotExist', 
						@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 FGNotExist.');
				 END 
		 END   
	 ELSE -- If no filter group supplied then retrieve the default from global settings and then check the default
	     BEGIN
		     SET @filtergroupid = (SELECT default_filter_group_id 
			                         FROM base.global_settings_groups
			                        WHERE base.global_settings_groups.setting_group_name = 'Master');
			 IF  @filtergroupid IS NULL
				 BEGIN  -- The filter group does not exist
				   EXEC internal.usp_SEL_message 
						@message_id = 'DefFGroupNotExist', 
						@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 DefFGroupNotExist.');
				 END 
		 END -- End checking filter group 


    -- 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 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 '\')

-- Apply filter group filtering.
AND NOT EXISTS (
    -- Radio button attribute failure
    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 drbl
          WHERE drbl.file_radiob_attr_id = fgl.file_radiob_attr_id
            AND drbl.file_id = searchtable.file_id
      )
)
AND NOT EXISTS (
    -- Multi-select attribute failure
    SELECT 1
    FROM file_attr.file_ms_attr_fgroup_links AS dmsafgl
    WHERE dmsafgl.filter_group_id = @filtergroupid
      AND NOT EXISTS (
          SELECT 1
          FROM file_attr.file_multi_select_links AS dmsl
          WHERE dmsl.file_ms_attr_id = dmsafgl.file_ms_attr_id
            AND dmsl.file_id = searchtable.file_id
      )
)
	                     )
		 ),

				 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)

	-- Return the actual filter group details applied.
		SET @outputfiltergroupid = @filtergroupid
		SET @filtergroupname = (SELECT fg.attr_name
								FROM forms.filter_groups AS fg
								WHERE fg.filter_group_id = @filtergroupid)

		  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