USE [Elyse_DB]
GO
/****** Object:  StoredProcedure [reading].[usp_SEL_docs_by_text_field]    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: 09-06-2024
-- Description:	Original Creation
-- Selects document IDs and metadata according to the content of a text field using the LIKE operator.
-- A 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_docs_by_text_field] 
      @doctextnameid bigint            = NULL,   -- This is optional, providing that the global defaults contains a default value. 
	  @likestring nvarchar(max)        = '',
	  @filtergroupid bigint            = NULL,  -- This is optional, providing that the global defaults contains a default value. 
	  @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.
	  @numrecords bigint               = NULL OUTPUT, -- This corresponds to the number of records in the table that the application presents to the user.
	                                               -- Each record is a document-file pair.
	  @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,
	  @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(),     
		@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;

 -- 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 = 0
		     SET @doctextnameid = NULL;
		  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

  -- 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 and then check the default
	      -- This means that the calling function does not need to know what filter group to use. 
		  -- But it also means that there is no option to search without a filter group.  
		  -- Searching without a filter group is handled by a differerent procedure. 
	     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 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 search string is NULL
		    OR (searchtable.text_value LIKE '%' + @likestring + '%' ESCAPE '\')
		  )

 -- Apply filter group filtering
     AND NOT EXISTS (
        -- Radio button attribute failure
        SELECT 1
        FROM doc_attr.doc_radiob_attr_fgroup_links AS fgl
        WHERE fgl.filter_group_id = @filtergroupid
          AND NOT EXISTS (
              SELECT 1
              FROM doc_attr.doc_radio_button_links AS drbl
              WHERE drbl.doc_radiob_attr_id = fgl.doc_radiob_attr_id
                AND drbl.doc_id = searchtable.doc_id
          )
		)
	AND
		NOT EXISTS (
			-- Multi-select attribute failure
			SELECT 1
			FROM doc_attr.doc_ms_attr_fgroup_links AS dmsafgl
			WHERE dmsafgl.filter_group_id = @filtergroupid
			  AND NOT EXISTS (
				  SELECT 1
				  FROM doc_attr.doc_multi_select_links AS dmsl
				  WHERE dmsl.doc_ms_attr_id = dmsafgl.doc_ms_attr_id
					AND dmsl.doc_id = searchtable.doc_id
			  )
		)
	), 	  

-- ================================================================================
--      Retrieve the fileid for the document according to the filter group     

		docidfileidCTE AS (
			SELECT 
				docid                                                    AS documentid, 
				f.file_id                                                AS fileid,
				DENSE_RANK() OVER (ORDER BY docidsCTE.docid, f.file_id)  AS numrows,
				DENSE_RANK() OVER (ORDER BY docidsCTE.docid)             AS doccount
			FROM docidsCTE 
			OUTER APPLY internal.ufn_SEL_file_by_doc_fgroup_TVF(docidsCTE.docid, @filtergroupid) AS f
		)
  

-- ================================================================================
-- Extract the data according to the form id
	INSERT INTO @AttData  (num_records, doc_count, docid, fileid, field_type, field_name_id, field_name, field_mnemonic, attr_id, attr_value, units, form_position)
		(SELECT 
		    numrows                 AS num_records,
		    df.doccount             AS doc_count, -- This informs the calling application how to parse the data into rows 
			                                     -- since each row in this table is a field.
			doc_data.doc_id         AS docid, 
			doc_data.file_id        AS fileid, 
			doc_data.field_type     AS field_type, 
			doc_data.field_name_id  AS field_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 docidfileidCTE AS df
		OUTER APPLY internal.ufn_SEL_one_dc_data_by_frm_ITVF(df.documentid, df.fileid, @formid) AS doc_data
	    );

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

			 SELECT 
			        ad.num_records                AS 'Record',
					ad.docid                      AS 'Document ID', 
					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.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)
  
  -- 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 @numrecords = ISNULL(@numrecords,0);
	SET @numdocs    = ISNULL(@numdocs, 0);


END
GO