USE [Elyse_DB]
GO
/****** Object:  StoredProcedure [reading].[usp_SEL_all_docs_by_text_field]    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: 02-09-2024
-- Description:	Original Creation
-- Selects document IDs and metadata according to the content of a text field using the LIKE operator.
-- Differs from usp_SEL_docs_by_text_field in that no filter group is applied 
-- The search is restricted to document IDs which the user has viewing rights to. 
/*
COPYRIGHT NOTICE
This database schema and stored procedures are protected by copyright.
Copyright.  Silkwood Software Pty. Ltd. 2023
*/

-- =============================================
CREATE PROCEDURE [reading].[usp_SEL_all_docs_by_text_field] 
      @doctextnameid bigint            = NULL,   -- This is optional, providing that the global defaults contains a default value. 
	  @likestring nvarchar(max)        = '',	  
	  @formid 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.
	  @numdocs bigint                  = NULL OUTPUT,  -- This is the number of documents.  Noting that each document will have zero or more associated 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',
		@documentid bigint,
		@fileid bigint,
		@transaction_ready nchar(10)       = 'Ready',
		@data_validation_status nchar(10)  = 'Pass';

DECLARE @AttData TABLE
  (
        num_records bigint,
  		doc_count bigint,
		docid nvarchar(50),
		fileid bigint,
		field_type nvarchar(50), 
		field_name_id bigint,
		field_name  nvarchar(50),
		field_mnemonic nvarchar(10),
		attr_id  bigint,
		attr_value nvarchar(max),
		units nvarchar(50),
		form_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 @numdocs = 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, '^', '\^');

		  -- Check the doc text name field id
		  IF @doctextnameid IS NOT NULL
		     BEGIN -- A document doc text name field id has been supplied
				 IF NOT EXISTS (SELECT doc_free_text_name_id 
								  FROM doc_attr.doc_free_text_values
			  	 				 WHERE doc_free_text_name_id = @doctextnameid) -- Check if the doc text name field id is invalid
				   BEGIN -- Doc text name field is invalid.
						SET @data_validation_status = 'Fail';
						SET @transaction_ready      = 'Fail';
						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
		  ELSE -- No doc text name field ID has been supplied
			BEGIN
			   SET @doctextnameid = (SELECT default_doc_free_text_name_id 
			            FROM base.global_settings_groups
			        WHERE base.global_settings_groups.setting_group_name = 'Master');
			    IF  @doctextnameid IS NULL
			        BEGIN
						SET @data_validation_status = 'Fail';
						SET @transaction_ready      = 'Fail';
						EXEC internal.usp_SEL_message 
							@message_id = 'DefFrTextIdNotExist', 
							@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 DefFrTextIdNotExist.');
					END
		     END -- END checking the default doc text name field id

  

    -- 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 docidsCTE AS (
		SELECT searchtable.doc_id     AS docid
		  FROM doc_attr.doc_free_text_values AS searchtable  --### The search criteria table
	INNER JOIN internal.ufn_SEL_docs_permission_filtr_ITVF(@connectedusersid) AS perm  -- Apply the permission filter
		    ON    searchtable.doc_id
			    = perm.doc_id
		WHERE   searchtable.doc_free_text_name_id = @doctextnameid  --### The search criteria    
			  AND (
		    (@likestring IS NULL AND searchtable.text_value IS NULL)  -- Caters for searching for records where the string is NULL
		    OR (searchtable.text_value LIKE '%' + @likestring + '%' ESCAPE '\') )
		         )
			 
       -- Extract the data according to the form id

			 INSERT INTO @AttData  (doc_count, docid, field_type, field_name_id, field_name, field_mnemonic, attr_id, attr_value, units, form_position)
			  (SELECT 
					  DENSE_RANK() OVER (ORDER BY docidsCTE.docid) AS doc_count, 
					  docidsCTE.docid         AS docid, 
					  doc_data.field_type     AS field_type, 
					  doc_data.field_name_id  AS name_id, 
					  doc_data.field_name     AS field_name,  
			          doc_data.field_mnemonic AS field_mnemonic,
					  doc_data.attr_id        AS attr_id, 
					  doc_data.attr_value     AS attr_value, 
					  doc_data.units          AS units,
			          doc_data.form_position  AS form_position
				 FROM docidsCTE AS df
		  CROSS APPLY internal.ufn_SEL_one_dc_data_by_frm_ITVF(df.docid, NULL, @formid) AS doc_data 
		   RIGHT JOIN docidsCTE
				   ON docidsCTE.docid
						  = doc_data.doc_id);

				SELECT @numdocs = MAX(doc_count) from @AttData;

			 SELECT 
					ad.doc_count                  AS 'Record',  -- This informs the calling application how to parse the data into rows since 
			                                                -- each row in this table is a field.
					ad.docid                      AS 'Document 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.form_position, '')  AS 'Position'
			   FROM @AttData as ad
		   ORDER BY Position, Record, 'Document 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)

  	    IF @@ROWCOUNT = 0
	      BEGIN
	        EXEC internal.usp_SEL_message 
                 @message_id   = 'ZeroRecords', 
                 @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 ZeroRecords');
		  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';
        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

   SET @numrows    = ISNULL(@numrows, 0);
   SET @numdocs    = ISNULL(@numdocs, 0);
   
END
GO