IF EXISTS
  (SELECT TOP 1 1
     FROM SYS.[objects] SO
    WHERE SO.[name] = N'KAAS_WD_GetAzureDATInfo'
      AND SO.[type] = 'P')
  BEGIN
    DROP PROCEDURE [dbo].[KAAS_WD_GetAzureDATInfo]
  END
GO

Create Procedure
       [dbo].[KAAS_WD_GetAzureDATInfo]
      (@TrackReference INT,
       @Version        INT)
AS
/*************************************************************************************************************
 *                                                                                                           *
 * [dbo].[ky_NETSPGetAzureDATInfo]                                                                           *
 *                                                                                                           *
 * Gets the information Azure needs to retrieve a Diary Attachment                                           *
 *                                                                                                           *
 * Compatibility information - PLEASE update older versions if necessary to ensure the compatible software   *
 *                                    remains fully functional                                               *
 *   *****************************************************************************************************   *
 *   *                                                                                                   *   *
 *   * Supersedes:                              -                                                        *   *
 *   * First compatible version:                5.6.4.1                                                  *   *
 *   * Last compatible software version:        -                                                        *   *
 *   * Superseded by:                           -                                                        *   *
 *   *                                                                                                   *   *
 *   *****************************************************************************************************   *
 *                                                                                                           *
 * Modification History                                                                                      *
 * 2018-11-12   Pino Carafa       Created                                                                    *
 * 2019-11-19   Pino Carafa       Add Link to DiaryAttachmentOriginalMatterLinks                             *
 * 2020-02-06   Pino Carafa       Filepath loses colon for file paths that are really local, and that don't  *
 *                                have a drive letter covered by [dbo].[UNCAlias]                            *
 * 2020-05-26   Pino Carafa       Add Matter Code and Client Code                                            *
 * 2020-06-08   Sadiq			  Copied from KAAS_PGetAzureDATInfo                                                                                                                        *
 *************************************************************************************************************/
BEGIN
  DECLARE @FilePath       VARCHAR(500)
  DECLARE @Document       VARCHAR(100)
  DECLARE @DotPos         INT
  DECLARE @SlashPos       INT
  DECLARE @FileExtension  VARCHAR(10)
  DECLARE @MatterUniqueID INT
  DECLARE @IsCurrent      BIT
  DECLARE @Type           VARCHAR(10)
  DECLARE @MatterCode     VARCHAR(20)
  DECLARE @ClientCode     VARCHAR(10)

  SELECT @TrackReference = [DAT].[TrackReference],
         @Document = RTRIM(ISNULL([DAT].[DOCUMENT], '')),
         @FilePath = REVERSE(RTRIM(ISNULL([DAV].[Filepath], ISNULL([DAT].[FILEPATH], '')))),
         @MatterUniqueID = ISNULL([DAOML].[OriginalUniqueID], [MAT].[uniqueid]),
         @Version = CASE WHEN [DAV].[Version] IS NULL
                              THEN [CV].[CurrentVersion]
                         ELSE [DAV].[Version] END,
         @IsCurrent = CASE WHEN [DAV].[Version] IS NULL
                               THEN 1
                          ELSE 0 END,
         @Type = [DAT].[TYPE],
         @MatterCode = RTRIM(ISNULL([MAT].[Code], '')),
         @ClientCode = RTRIM(ISNULL([MAT].[ClientCode], ''))
    FROM [dbo].[DiaryAttachments] [DAT]
         LEFT OUTER JOIN [dbo].[DiaryAttachmentOriginalMatterLinks] [DAOML]
                      ON [DAOML].[TrackReference] = [DAT].[TrackReference]
         INNER JOIN [dbo].[diary] [DIA]
                    INNER JOIN [dbo].[matters] [MAT]
                            ON [MAT].[Code] = [DIA].[CASECODE]
                 ON [DIA].[ACTIONID] = [DAT].[DiaryID]
         LEFT OUTER JOIN [dbo].[DiaryAttachmentVersioning] [DAV]
                      ON [DAV].[TrackReference] = [DAT].[TrackReference]
                     AND [DAV].[Version] = @Version
         CROSS APPLY (SELECT ISNULL(MAX([DAV2].[Version]), 0) + 1 AS [CurrentVersion]
                        FROM [dbo].[DiaryAttachmentVersioning] [DAV2]
                       WHERE [DAV2].[TrackReference] = [DAT].[TrackReference]) [CV]
   WHERE [DAT].[TrackReference] = @TrackReference

  SET @DotPos = CHARINDEX('.', @FilePath)
  SET @SlashPos = CHARINDEX('\', @FilePath)

  IF @DotPos < 1
    BEGIN
      SET @FileExtension = ''
    END
  ELSE
    IF @DotPos < @SlashPos
      BEGIN
        SET @FileExtension = REVERSE(SUBSTRING(@FilePath, 1, @DotPos))
      END
    ELSE
      BEGIN
        SET @FileExtension = ''
      END

  IF @Document = ''
    BEGIN
      IF @SlashPos > 0
        BEGIN
          SET @Document = REVERSE(SUBSTRING(@FilePath, 1, @SlashPos - 1))
          SET @Document = SUBSTRING(@Document, 1, LEN(@Document) - LEN(@FileExtension))
          IF @Document LIKE '%_' + CONVERT(VARCHAR(10), @TrackReference)
            BEGIN
              SET @Document = SUBSTRING(@Document, 1, LEN(@Document) - LEN('_' + CONVERT(VARCHAR(10), @TrackReference)))
            END
        END
      ELSE
        BEGIN
          SET @Document = 'File'
        END
    END

  SET @FilePath = REVERSE(@FilePath)
  IF LEN(@FilePath) > 1
    BEGIN
      IF SUBSTRING(@FilePath, 2, 1) = ':'
        BEGIN
          IF (SELECT [UNC].[UNC] FROM [dbo].[UNCAlias] [UNC] WHERE [UNC].[Drive] = SUBSTRING(@FilePath, 1, 1)) IS NOT NULL
            BEGIN
              SELECT @FilePath = ISNULL((SELECT [UNC].[UNC] FROM [dbo].[UNCAlias] [UNC] WHERE [UNC].[Drive] = SUBSTRING(@FilePath, 1, 1)), SUBSTRING(@FilePath, 1, 1)) + SUBSTRING(@FilePath, 3, LEN(@FilePath) - 2)
            END
        END
    END

  SELECT @TrackReference AS [TrackReference],
         @Document       AS [Document],
         @FileExtension  AS [FileExtension],
         @MatterUniqueID AS [MatterUniqueID],
         @FilePath       AS [FilePath],
         @Version        AS [RetrievedVersion],
         @IsCurrent      AS [IsCurrent],
         @Type           AS [Type],
         @MatterCode     AS [MatterCode],
         @ClientCode     AS [ClientCode]
END

GO
IF OBJECT_ID(N'KAAS_WD_GetDocumentsIMResult',N'P')IS NOT NULL
    DROP PROCEDURE [dbo].[KAAS_WD_GetDocumentsIMResult]
GO


CREATE PROCEDURE
       [dbo].[KAAS_WD_GetDocumentsIMResult]
      (@Tracks      TrackRefsTable    READONLY,
       @res         DocRes3Table      READONLY)
AS
/*************************************************************************************************************
 *                                                                                                           *
 * [dbo].[KAAS_WD_GetDocumentsIMResult]                                                                      *
 *                                                                                                           *                                                                                                   *
 * Modification history                                                                                      *
 *                                                                                                           *             *
 * Sadiq			 2022-03-14 Copied from [ky_NETSPGetDocumentsIMResult]	                                                                                                          *
 *************************************************************************************************************/
BEGIN

DECLARE @TracksRes TrackRefsND

IF NOT EXISTS
  (SELECT TOP 1 1 FROM @res)
  BEGIN
    SELECT [DAS].[DiaryDate]                    AS [DiaryDate],
           [DAS].[CASECODE]                     AS [CASECODE],
           [DAS].[ActionID]                     AS [ACTIONID],
           [DAS].[DocumentDate]                 AS [DocumentDate],
           [DAS].[IMDocID]                      AS [IMDocID],
           ''                                   AS [IMClass],
           [DAS].[DocumentClassDescription]     AS [DocumentClassDescription],
           [DAS].[DocClass]                     AS [DocClass],
           [DAS].[Document]                     AS [Document],
           [DAS].[TrackReference]               AS [TRACKREFERENCE],
           [DAS].[TYPE]                         AS [TYPE],
           [DAS].[ATTACHMENTS]                  AS [Attachments],
           [DAS].[EMAIL]                        AS [EMAIL],
           [DAS].[EMAILADDRESS]                 AS [EMAILADDRESS],
           [DAS].[ADDRESSTO]                    AS [ADDRESSTO],
           [DAS].[EMAILSENT]                    AS [EMAILSENT],
           [DAS].[SUBJECT]                      AS [SUBJECT],
           [DAS].[DisplayText]                  AS [ShortText],
           [DAS].[NAME]                         AS [NAME],
           [DAS].[UNCFILEPATH]                  AS [FILEPATH],
           [DAS].[PROCESSTYPE]                  AS [PROCESSTYPE],
           [DAS].[ACTIONTYPE]                   AS [ACTIONTYPE],
           [DAS].[ActionTypeDescription]        AS [ActionTypeDescription],
           [DAS].[SOURCE]                       AS [DOCUMENTSOURCE],
           [DAS].[CurrentVersion]               AS [CurrentVersion],
           [DAS].[InDocFolder]                  AS [IsInDocFolder],
           [DAS].[IsShared]                     AS [IsShared],
           [DAS].[MatterUniqueID]               AS [UniqueId],
           [DAS].[SigningStatus]                AS [SigningStatus]
      FROM @Tracks [TRK]
           INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                   ON [DAS].[TrackReference] = [TRK].[TrackReference]
  ORDER BY [TRK].[id]
    OPTION (RECOMPILE)
  END
ELSE IF ISNULL((SELECT TOP 1 [ACTIVE] FROM [dbo].[IManage]), 0) = 0
  BEGIN
    SELECT ISNULL([RES].[DiaryDate], [DAS].[DiaryDate])              AS [DiaryDate],
           ISNULL([RES].[CASECODE], [DAS].[CASECODE])                AS [CASECODE],
           ISNULL([RES].[ActionId], [DAS].[ActionID])                AS [ACTIONID],
           ISNULL([RES].[DocumentDate], [DAS].[DocumentDate])        AS [DocumentDate],
           ISNULL([RES].[IMDocID], [DAS].[IMDocID])                  AS [IMDocID],
           [RES].[IMClass]                                           AS [IMClass],
           [DAS].[DocumentClassDescription]                          AS [DocumentClassDescription],
           ISNULL([RES].[DocClass], [DAS].[DocClass])                AS [DocClass],
           ISNULL([RES].[Document], [DAS].[Document])                AS [Document],
           ISNULL([RES].[TrackReference], [DAS].[TrackReference])    AS [TRACKREFERENCE],
           ISNULL([RES].[TYPE], [DAS].[TYPE])                        AS [TYPE],
           ISNULL([RES].[Attachments], [DAS].[ATTACHMENTS])          AS [Attachments],
           COALESCE([RES].[EMAIL], [DAS].[EMAIL], '')                AS [EMAIL],
           COALESCE([RES].[EMAILFROM], [DAS].[EMAILADDRESS], '')     AS [EMAILADDRESS],
           COALESCE([RES].[EMAILTO], [DAS].[ADDRESSTO], '')          AS [ADDRESSTO],
           COALESCE([RES].[EMAILSENT], [DAS].[EMAILSENT], '')        AS [EMAILSENT],
           [DAS].[SUBJECT]                                           AS [SUBJECT],
           [DAS].[DisplayText]                                       AS [ShortText],
           [DAS].[NAME]                                              AS [NAME],
           [DAS].[UNCFILEPATH]                                       AS [FILEPATH],
           [DAS].[PROCESSTYPE]                                       AS [PROCESSTYPE],
           [DAS].[ACTIONTYPE]                                        AS [ACTIONTYPE],
           [DAS].[ActionTypeDescription]                             AS [ActionTypeDescription],
           [DAS].[SOURCE]                                            AS [DOCUMENTSOURCE],
           ISNULL([DAS].[CurrentVersion], 1)                         AS [CurrentVersion],
           ISNULL([DAS].[InDocFolder], 0)                            AS [IsInDocFolder],
           ISNULL([DAS].[IsShared], 0)                               AS [IsShared],
           ISNULL([RES].[MatterUniqueID], [DAS].[MatterUniqueID])    AS [UniqueId],
           [RES].[SigningStatus]                                     AS [SigningStatus]
      FROM @Tracks [TRK]
           INNER JOIN @res [RES]
                   ON [RES].[id] = [TRK].[RESID]
           INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                   ON [DAS].[TrackReference] = [TRK].[TrackReference]
  ORDER BY [TRK].[id]
    OPTION (RECOMPILE)
  END
ELSE
  BEGIN

    -- Pino 2020-08-17
    -- Where there IS a "track reference" in @res
    -- there will be a 1:1 with actual Diary Attachments so a COUNT is unnecessary
    INSERT
      INTO @TracksRes
          ([TrackReference])
    SELECT DISTINCT
           [TrackReference]
      FROM @res [RES]
     WHERE [RES].[TrackReference] <> 0

           /* Because these fields exist on both systems, give preference to IM */
    SELECT ISNULL([RES].[DiaryDate], [DAS].[DiaryDate])              AS [DiaryDate],
           ISNULL([RES].[CASECODE], [DAS].[CASECODE])                AS [CASECODE],
           ISNULL([RES].[ActionId], [DAS].[ActionID])                AS [ACTIONID],
           ISNULL([RES].[DocumentDate], [DAS].[DocumentDate])        AS [DocumentDate],
           ISNULL([RES].[IMDocID], [DAS].[IMDocID])                  AS [IMDocID],
           [RES].[IMClass]                                           AS [IMClass],
           CASE WHEN [RES].[id] IS NULL
                     THEN [DAS].[DocumentClassDescription]
                WHEN [RES].[IMClass] IS NULL
                     THEN [DAS].[DocumentClassDescription]
                WHEN [DCL].[CLASSDESCRIPTION] IS NOT NULL
                     THEN [DCL].[CLASSDESCRIPTION]
                WHEN [RES].[IMClass] = 'ATTNOTES'
                     THEN 'Attendance Notes'
                WHEN [RES].[IMClass] = 'BILL'
                     THEN 'Bill'
                WHEN [RES].[IMClass] = 'FAX'
                     THEN 'Fax'
                WHEN [RES].[IMClass] = 'LEGAL'
                     THEN 'Legal'
                WHEN [RES].[IMClass] = 'SCAN'
                     THEN 'Scan'
                WHEN [RES].[IMClass] = 'COMPARE'
                     THEN 'Compare Document'
                WHEN [RES].[IMClass] = 'DISCUSSION'
                     THEN 'Discussion'
                WHEN [RES].[IMClass] = 'DOC'
                     THEN 'Document'
                WHEN [RES].[IMClass] = 'E-MAIL'
                     THEN 'E-Mail'
                WHEN [RES].[IMClass] = 'EVENT'
                     THEN 'Event'
                WHEN [RES].[IMClass] = 'LETTER'
                     THEN 'Letter'
                WHEN [RES].[IMClass] = 'MEMO'
                     THEN 'Memo'
                WHEN [RES].[IMClass] = 'PAGE_ICON'
                     THEN 'Page Icon'
                WHEN [RES].[IMClass] = 'TASK'
                     THEN 'Task'
                WHEN [RES].[IMClass] = 'TEXT'
                     THEN 'Text File'
                WHEN [RES].[IMClass] = 'WEBDOC'
                     THEN 'InfoLink Web Page'
                ELSE /* '' */ [DAS].[DocumentClassDescription] END   AS [DocumentClassDescription],
           ISNULL([RES].[DocClass], [DAS].[DocClass])                AS [DocClass],
           ISNULL([RES].[Document], [DAS].[Document])                AS [Document],
           ISNULL([RES].[TrackReference], [DAS].[TrackReference])    AS [TRACKREFERENCE],
           ISNULL([RES].[TYPE], [DAS].[TYPE])                        AS [TYPE],
           ISNULL([RES].[Attachments], [DAS].[ATTACHMENTS])          AS [Attachments],
           COALESCE([RES].[EMAIL], [DAS].[EMAIL], '')                AS [EMAIL],
           COALESCE([RES].[EMAILFROM], [DAS].[EMAILADDRESS], '')     AS [EMAILADDRESS],
           COALESCE([RES].[EMAILTO], [DAS].[ADDRESSTO], '')          AS [ADDRESSTO],
           COALESCE([RES].[EMAILSENT], [DAS].[EMAILSENT], '')        AS [EMAILSENT],
           [DAS].[SUBJECT]                                           AS [SUBJECT],
           [DAS].[DisplayText]                                       AS [ShortText],
           [DAS].[NAME]                                              AS [NAME],
           [DAS].[UNCFILEPATH]                                       AS [FILEPATH],
           [DAS].[PROCESSTYPE]                                       AS [PROCESSTYPE],
           [DAS].[ACTIONTYPE]                                        AS [ACTIONTYPE],
           ISNULL([DAS].[ActionTypeDescription], 'IManage document') AS [ActionTypeDescription],
           ISNULL([DAS].[SOURCE], 'IManage')                         AS [DOCUMENTSOURCE],
           ISNULL([DAS].[CurrentVersion], 1)                         AS [CurrentVersion],
           ISNULL([DAS].[InDocFolder], 0)                            AS [IsInDocFolder],
           ISNULL([DAS].[IsShared], 0)                               AS [IsShared],
           ISNULL([RES].[MatterUniqueID], [DAS].[MatterUniqueID])    AS [UniqueId],
           [RES].[SigningStatus]                                     AS [SigningStatus]
      FROM @Tracks [TRK]
           /* Check if KH-tracked documents are in IM docs list
              If the TrackReference is found in IM docs list, then ignore the @Tracks record */
           LEFT OUTER JOIN @TracksRes [TRS]                                --2020-08-17
                        ON [TRS].[TrackReference] = [TRK].[TrackReference] --2020-08-17
           LEFT OUTER JOIN @res [RES]
                           LEFT OUTER JOIN [dbo].[CLSIMXRef] [CIX]
                                           INNER JOIN [dbo].[DocumentClasses] [DCL]
                                                   ON [DCL].[CLASSCODE] = [CIX].[CLASSCODE]
                                        ON [CIX].[IMCLASS] = [RES].[IMClass]
                        ON [RES].[id] = [TRK].[RESID]
           LEFT OUTER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                        ON [DAS].[TrackReference] = [TRK].[TrackReference]
  ORDER BY [TRK].[id]
    OPTION (RECOMPILE)
  END
END


GO


IF OBJECT_ID(N'KAAS_WD_GetList2',N'P')IS NOT NULL
    DROP PROCEDURE [dbo].[KAAS_WD_GetList2]
GO

CREATE PROCEDURE
       [dbo].[KAAS_WD_GetList2]
      (@matter           varchar(20),
       @WKTCode          varchar(10),
       @ActionCategory   varchar(1))
AS

/***********************************************************************************************************
 * Modification History
 * 2022-03-30			Sadiq				Created 
 *
 ***********************************************************************************************************/

BEGIN
  DECLARE @DEFAULTTA varchar(20)
  SET @ActionCategory = RTRIM(ISNULL(@ActionCategory, ''))
  SET @WKTCODE = RTRIM(ISNULL(@WKTCODE, ''))
  IF (@ActionCategory = 'N')
    BEGIN
      SELECT 
		@DEFAULTTA = DA.[Action]
        FROM [dbo].[DefaultNoteTA] DA
    END

  IF (@ActionCategory = 'T')
    BEGIN
      SELECT @DEFAULTTA = DA.[Action]
        from [dbo].[DefaultPhoneTA] DA
    END

  SELECT RTRIM(ISNULL(TA.[WKTCODE], '')) AS [WORKTYPE],
         IsNull(AWT.[SEQ], 999999) AS [SEQ],
         RTRIM(ISNULL(TA.[ACTIONCODE], '')) AS [ACTIONCODE],
         REPLACE(REPLACE(RTRIM(ISNULL(TA.[DESC], '')), CHAR(13) + CHAR(10), ' '), CHAR(10), ' ') AS [DESC],
         ISNULL(TA.[ACTIONCATEGORY], 'A') AS [ACTIONCATEGORY],
         CASE WHEN ISNULL(TA.[HIGHLIGHTED], 'N') = 'Y'
                   THEN 'Y'
              ELSE 'N' END AS [HIGHLIGHTED],
         ISNULL(TA.[PUBLISHER], 'N') AS [PUBLISHER],
         ISNULL(TA.[BILLABLE], 'N') AS [BILLABLE],
         RTRIM(ISNULL(TA.[PRIORITY], 'Normal')) AS [PRIORITY],
         RTRIM(ISNULL(TA.[STATUS], 'NA')) AS [STATUS]
    FROM [dbo].[Templates] T
         INNER JOIN [dbo].[ActionWorkTypes] AWT
                    INNER JOIN [dbo].[TemplateActions] TA
                            ON TA.[ACTIONCODE] = AWT.[ACTIONCODE]
                 ON RTRIM(ISNULL(AWT.[WORKTYPE], '')) = RTRIM(ISNULL(T.[WKCODE], ''))
                 OR RTRIM(ISNULL(AWT.[ACTIONCODE], '')) = RTRIM(ISNULL(@DEFAULTTA, ''))
   WHERE RTRIM(ISNULL(T.[WKCODE], '')) = CASE WHEN RTRIM(ISNULL(@WKTCode, '')) <> ''
                                                   THEN @WKTCode
                                              WHEN RTRIM(ISNULL(@matter, '')) <> ''
                                                   THEN (SELECT TOP 1
                                                                CSM.[CSWKTCODE] FROM [dbo].[CaseMaster] CSM WHERE CSM.[CSCODE] = @matter)
                                              ELSE '' END
ORDER BY CASE WHEN @ActionCategory = ''
                   THEN 0
              WHEN @ActionCategory = TA.[ActionCategory]
                   THEN CASE WHEN TA.[ACTIONCODE] = @DEFAULTTA
                                  THEN 1
                             WHEN @ActionCategory = 'N' AND TA.[DESC] LIKE '%note%'
                                  THEN 2
                             WHEN @ActionCategory = 'P' AND TA.[DESC] LIKE '%appointment%'
								  THEN 2
                             WHEN @ActionCategory = 'T' AND TA.[DESC] LIKE '%phone%'
                                  THEN 2
                             ELSE 3 END
              ELSE 4 END,
         ISNULL(AWT.[SEQ], 999999)

END
GO
IF OBJECT_ID(N'KAAS_WD_CheckDocumentActivity',N'P')IS NOT NULL
    DROP PROCEDURE [dbo].[KAAS_WD_CheckDocumentActivity]
GO


CREATE PROCEDURE
       [dbo].[KAAS_WD_CheckDocumentActivity]
      (@TrackRef			INT
	   )
AS
/*************************************************************************************************************
 *                                                                                                           *
 * [dbo].[KAAS_WD_CheckDocumentActivity]                                                                      *
 *                                                                                                           *                                                                                                   *
 * Modification history                                                                                      *
 *                                                                                                           *             *
 * Sadiq			 2022-04-06    Created                                                                                                        *
 *************************************************************************************************************/
BEGIN
SELECT TOP 1
       RTRIM(ISNULL(DAT.[DOCUMENT], '')) AS [DocumentName],
       CASE WHEN UNC.[UNC] IS NULL THEN RTRIM(ISNULL(DAT.[FILEPATH], ''))
                 ELSE UNC.[UNC] + SUBSTRING(RTRIM(ISNULL(DAT.[FILEPATH], '')), 3, LEN(RTRIM(ISNULL(DAT.[FILEPATH], ''))) - 2) END AS [FILEPATH],
       RTRIM(ISNULL(MAT.[Code], '')) + ' ' + RTRIM(ISNULL(CLT.[CLNAME], '')) + '/' + RTRIM(ISNULL(MAT.[Description], '')) AS [Matter],
       CASE WHEN RTRIM(ISNULL(DIA.ACTIONTYPE,'')) ='E'
                 THEN CASE WHEN ISNULL(DIA.PROCESSTYPE,'O')='I'
                                THEN 'Email From:' + (RTRIM(ISNULL(CAST(DIA.[EMAILADDRESS] AS VARCHAR),''))) + ' - ' + ISNULL(CAST (DIA.[Subject] AS VARCHAR),'')
                           ELSE 'Email To:' + RTRIM(CAST(ISNULL(DIA.[AddressTo],'') AS VARCHAR)) + ' - ' + ISNULL(CAST (DIA.[Subject] AS VARCHAR),'') END
            ELSE CASE WHEN DIA.[SUBJECT] IS NULL
                           THEN [dbo].[ky_RemoveSpuriousWhitespace2](CONVERT(VARCHAR(5000), DIA.[TEXT1]), 200)
                      WHEN DATALENGTH(DIA.[SUBJECT]) = 0   
                           THEN [dbo].[ky_RemoveSpuriousWhitespace2](CONVERT(VARCHAR(5000), DIA.[TEXT1]), 200)
                      WHEN RTRIM(CAST(DIA.[SUBJECT] AS VARCHAR)) = ''   
                           THEN [dbo].[ky_RemoveSpuriousWhitespace2](CONVERT(VARCHAR(5000), DIA.[TEXT1]), 200)
                      ELSE CAST(DIA.[SUBJECT] AS VARCHAR) END END AS [Action],
       DIA.[ACTIONID],
       RTRIM(ISNULL(MAT.[CODE], '')) AS [MatterCode],
       RTRIM(ISNULL(MAT.[ClientCode], '')) AS [ClientCode],
       RTRIM(ISNULL(DAT.[DOCCLASS], '')) AS [DOCCLASS],
       RTRIM(ISNULL(CLT.[CLNAME], '')) AS [ClientName],
       RTRIM(ISNULL(MAT.[DESCRIPTION], '')) AS [MatterDescription],
       RTRIM(ISNULL(DCL.[ClassDescription], '')) AS [DocumentClass],
       [VSN].[CurrentVersion] AS [CurrentVersion]
  FROM [dbo].[DiaryAttachments] DAT
       INNER JOIN [dbo].[diary] DIA
               ON DIA.[ACTIONID] = DAT.[DIARYID]
       INNER JOIN [dbo].[matters] MAT
                  INNER JOIN [dbo].[client] CLT
                          ON CLT.[CLCODE] = MAT.[ClientCode]
               ON MAT.[Code] = DAT.[CASECODE]
       LEFT OUTER JOIN [dbo].[UNCAlias] UNC
                    ON SUBSTRING(DAT.[FilePath], 2, 1) = ':'
                   AND UNC.[Drive] = SUBSTRING(DAT.[FilePath], 1, 1)
       LEFT OUTER JOIN [dbo].[DocumentClasses] DCL
                    ON DCL.[ClassCode] = DAT.[DOCCLASS]
       CROSS APPLY (select ISNULL(MAX([DAV].[Version]), 0) + 1 AS [CurrentVersion]
                      from [dbo].[DiaryAttachmentVersioning] [DAV]
                     WHERE [DAV].[TrackReference] = [DAT].[TrackReference]) [VSN]
 WHERE DAT.[TRACKREFERENCE] = @TrackRef
 END

GO
IF OBJECT_ID(N'KAAS_WD_FetchDocumentProperties',N'P')IS NOT NULL
	DROP PROCEDURE KAAS_WD_FetchDocumentProperties
GO
CREATE PROCEDURE [dbo].[KAAS_WD_FetchDocumentProperties]
(    
	@TrackReference      VARCHAR(10)
)      
AS
/*******************************************************************************************************
*                                                                                                      *
*                                                                                                      *
* Stored Procedure Name : [KAAS_WD_FetchDocumentProperties]					                       *
* Copied from			: [KAAS_FetchDocumentPropertiesForEdit]												                           *
*                                                                                                      *
* Modification History:                                                                                *
* 2019-05-15		Arun		Created, merged from two stored procedure to fetch document classes and*
*								document properties													   *
* 2022-02-22        Sadiq		Copied Stored Proceudre from KAAS_FetchDocumentPropertiesForEdit
*******************************************************************************************************/
BEGIN  

	--To List Document Class
	SELECT convert(INT, DA.[DIARYID]) AS [ActionID],
       convert(INT, DA.[TrackReference]) AS [TrackReference],
       convert(VARCHAR(10), ISNULL(DA.[DocClass], '')) AS [DocClass],
       RTRIM(ISNULL(DCL.[CLASSDESCRIPTION], '')) AS [DocumentClassDescription],
       RTRIM(convert(VARCHAR(2000), ISNULL(DA.[Document], ''))) AS [Document],
       RTRIM(convert(VARCHAR(1000), ISNULL(DA.[Document], ''))) AS [OriginalDesc],
       convert(DATETIME, DIA.[DATE]) As [DiaryDate],
       convert(DATETIME, DA.[DATEENTERED]) AS [DocumentDate],
       convert(VARCHAR(20), UPPER(RTRIM(ISNULL(DA.[TYPE], '')))) AS [TYPE],
       [dbo].[ky_GetVerboseType](RTRIM(ISNULL(DA.[TYPE], ''))) AS [TYPEDESCRIPTION],
       DA.[DATEENTERED] AS [DateEntered],
       RTRIM(ISNULL(HANE.[NAME], '')) AS [EnteredBy],
       DA.[LASTACCESSDATE] As [LastAccessedDate],
       RTRIM(ISNULL(HANA.[NAME], '')) AS [LastAccessedBy],
       CASE WHEN UNC.[UNC] IS NULL THEN RTRIM(ISNULL(DA.[FILEPATH], ''))
                 ELSE UNC.[UNC] + SUBSTRING(RTRIM(ISNULL(DA.[FILEPATH], '')), 3, LEN(RTRIM(ISNULL(DA.[FILEPATH], ''))) - 2) END AS [FILEPATH],
       RTRIM(ISNULL(DIA.[CASECODE], '')) AS [MATTERCODE],
       RTRIM(ISNULL(CON.[Name], '')) + ' / ' + RTRIM(ISNULL(MAT.[Description], '')) AS [MATTER]
  FROM [dbo].[DiaryAttachments] DA
       INNER JOIN [dbo].[diary] DIA
                  INNER JOIN [dbo].[matters] MAT
                             INNER JOIN [dbo].[Contacts] CON
                                     ON CON.[CODE] = MAT.[ClientCode]
                          ON MAT.[Code] = DIA.[CASECODE]
               ON DIA.[ActionID] = DA.[DiaryID]
       LEFT OUTER JOIN [dbo].[DocumentClasses] DCL
                    ON DCL.[CLASSCODE] = DA.[DOCCLASS]
       LEFT OUTER JOIN [dbo].[Handlers] HANE
                    ON HANE.[CODE] = DA.[ENTEREDBY]
       LEFT OUTER JOIN [dbo].[Handlers] HANA
                    ON HANA.[CODE] = DA.[LASTACCESSBY]
       LEFT OUTER JOIN [dbo].[UNCAlias] UNC
                    ON SUBSTRING(DA.[FilePath], 2, 1) = ':'
                   AND UNC.[Drive] = SUBSTRING(DA.[FilePath], 1, 1)
 WHERE DA.[TrackReference] = @TrackReference

 exec [dbo].[KAAS_WD_GetAzureDATInfo] @TrackReference ,0
END 
GO
IF OBJECT_ID(N'KAAS_WD_FetchExtraSaveToKeyhouseValue',N'P')IS NOT NULL
    DROP PROCEDURE [dbo].[KAAS_WD_FetchExtraSaveToKeyhouseValue]
GO

CREATE PROCEDURE
       [dbo].[KAAS_WD_FetchExtraSaveToKeyhouseValue]
      (@MATTER                VARCHAR(20),
       @CurrentDepartmentOnly BIT,
       @IncludeRetired        BIT,
       @WKTCode          varchar(10),
       @ActionCategory   varchar(1))
AS
/*************************************************************************************************************
 *                                                                                                           *
 * [dbo].[KAAS_WD_FetchExtraSaveToKeyhouseValue]                                                                   *
 *                                                                                                           *
 * Description:  Fetch Extra value like Document class ,Work type and action for Save to Keyhouse word addin                                                                                  *
 *                                                                                                           *
 *                                                                                                          *
 * Modification History                                                                                      *
 * 2022-03-30   Sadiq       Created																			 *
 *                                                                                                           *
 *************************************************************************************************************/
BEGIN

SET NOCOUNT ON

	exec [dbo].[ky_ListDocumentClassesForMatter] @MATTER,@CurrentDepartmentOnly,@IncludeRetired

	SELECT RTRIM(ISNULL(TMP.[WKCODE], '')) AS [WKCODE],
			 RTRIM(ISNULL(TMP.[WKDESC], '')) AS [WKDESC]
		FROM [dbo].[Templates] TMP
	ORDER BY RTRIM(ISNULL(TMP.[WKDESC], ''))

	exec [dbo].[KAAS_WD_GetList2] @MATTER, @WKTCode, @ActionCategory

SET NOCOUNT OFF

END


GO


IF OBJECT_ID(N'KAAS_WD_GetDocumentsIM2',N'P')IS NOT NULL
    DROP PROCEDURE [dbo].[KAAS_WD_GetDocumentsIM2]
GO

CREATE PROCEDURE
       [dbo].[KAAS_WD_GetDocumentsIM2]
      (@XML NVARCHAR(MAX))
AS
/*************************************************************************************************************
 *                                                                                                           *
 * [dbo].[KAAS_WD_GetDocumentsIM2]                                                                           *
 *                                                                                                           *
 * Get the Case Documents for the Framework's Document Management screen                                     *
 * @XML format                                                                                               *
 * <Documents matter="..."                                                                                   *
 *            docfolderid="..."                                                                              *
 *            selectedactionid="..."                                                                         *
 *            pagenumber="..."                                                                               *
 *            pagesize="..."                                                                                 *
 *            sortby="..."                                                                                   *
 *            sortdesc="..."                                                                                 *
 *            search="...">                                                                                  *
 *   <IMDocs>...</IMDocs>                                                                                    *
 *   <Filters>...</Filters>                                                                                  *
 * </Documents>                                                                                              *
 *                                                                                                           *
 * Retrieve for individual matter, doc folder or action                                                      *
 * support for paging and sorting                                                                            *
 * <IMDocs> - a list of documents found in IManage                                                           *
 * Example:     <IMDocs>                                                                                     *
 *                <Doc DocId="xxxxxxxxxxxxxx"                                                                *
 *                    Description="This is Document One"                                                     *
 *                     ClassId="E-mail"                                                                      *
 *                     TypeId="Type"                                                                         *
 *                     EmailFrom="joe@bloggs.com"                                                            *
 *                     EmailTo="jane@doe.com"                                                                *
 *                     EmailSent="20140319 11:12:13"                                                         *
 *                     DocumentDate="20140320 14:15:16">                                                     *
 *                </Doc>                                                                                     *
 *              </IMDocs>                                                                                    *
 *                                                                                                           *                                                                                                     *
 * Modification history                                                                                      *
 *                                                                                                           *
 * Sadiq    2022-03-09     Copied from ky_NETSPGetDocumentsIM
 *                                                                                                           *
 *************************************************************************************************************/
BEGIN

SET NOCOUNT ON

DECLARE @Issues              TABLE
       ([Issue]              NVARCHAR(50),
        [Data]               NVARCHAR(MAX))

DECLARE @matter              VARCHAR(20)
DECLARE @currenthandler      VARCHAR(10)
DECLARE @currenthandlerBIN   BINARY(128)
DECLARE @parmhandler         VARCHAR(10)
DECLARE @BitwiseID           BIGINT
DECLARE @ActionID            INT
DECLARE @DocFolderID         INT
DECLARE @IMDocs              VARCHAR(MAX)
DECLARE @PageNumber          INT
DECLARE @PageSize            INT
DECLARE @SortBy              VARCHAR(50)
DECLARE @SortDesc            BIT
DECLARE @Search              VARCHAR(250)
DECLARE @SearchStem          VARCHAR(2000)
DECLARE @SearchInflectional  VARCHAR(280)
DECLARE @SearchINTText       VARCHAR(10)
DECLARE @SearchINT           INT
DECLARE @FilterCount         INT
DECLARE @XMLFilterCount      INT

--DECLARE @IsAzure             BIT
DECLARE @HasFulltext         BIT
DECLARE @NCOMMAND            NVARCHAR(MAX)
DECLARE @ExactPhrase         BIT
DECLARE @Inflectional        BIT
DECLARE @CaseLabel           VARCHAR(50)

DECLARE @FullTextStopwords   TABLE
       ([stopword]           NVARCHAR(128))

DECLARE @res DocRes3Table

DECLARE @classes TABLE
       ([KeyhouseClass]     INT,
        [IMClass]           INT,
        [ClassCode]         VARCHAR(20),
        [ClassDescription]  VARCHAR(100))

DECLARE @MatterSel          MatterSel
DECLARE @MatterSelCount     INT
DECLARE @MatterSelBroad     BIT
SET @MatterSelBroad = 0

DECLARE @IMDocTable IMDocTable

DECLARE @Tracks TrackRefsTable
DECLARE @TrackND TrackRefsND
DECLARE @TrackSR TrackRefsND

DECLARE @idoc INT

DECLARE @SigningStatusesFiltered   BIT
DECLARE @SigningStatuses           TABLE
       ([SigningStatus]            NVARCHAR(20)         PRIMARY KEY)
SET @SigningStatusesFiltered = 0

DECLARE @ActionTypesFiltered       BIT
DECLARE @ActionTypes               TABLE
       ([ActionType]               VARCHAR(1)           PRIMARY KEY)
SET @ActionTypesFiltered = 0

DECLARE @ProcessTypesFiltered      BIT
DECLARE @ProcessTypes              TABLE
       ([ProcessType]              VARCHAR(1)           PRIMARY KEY)
SET @ProcessTypesFiltered = 0

DECLARE @DocClassesFiltered        BIT
DECLARE @DocClasses                TABLE
       ([DocClass]                 VARCHAR(3)           PRIMARY KEY)
SET @DocClassesFiltered = 0

DECLARE @DocumentSourcesFiltered   BIT
DECLARE @DocumentSources           TABLE
       ([DocumentSource]           VARCHAR(20)           PRIMARY KEY)
SET @DocumentSourcesFiltered = 0

DECLARE @NamesFiltered             BIT
DECLARE @Names                     TABLE
       ([Name]                     VARCHAR(10)           PRIMARY KEY)
SET @NamesFiltered = 0

DECLARE @TypesFiltered              BIT
DECLARE @Types                     TABLE
       ([Type]                     VARCHAR(20)           PRIMARY KEY)
SET @TypesFiltered = 0

DECLARE @AttachmentsFiltered       BIT
DECLARE @Attachments               TABLE
       ([Attachments]              VARCHAR(1)            PRIMARY KEY)
SET @AttachmentsFiltered = 0

DECLARE @EmailsFiltered            BIT
DECLARE @Emails                    TABLE
       ([Email]                    VARCHAR(1)            PRIMARY KEY)
SET @EmailsFiltered = 0

DECLARE @EmailDatesFiltered        BIT
DECLARE @EmailDatesFrom            DATETIME
DECLARE @EmailDatesTo              DATETIME
SET @EmailDatesFiltered = 0

DECLARE @DiaryDatesFiltered        BIT
DECLARE @DiaryDatesFrom            DATETIME
DECLARE @DiaryDatesTo              DATETIME
SET @DiaryDatesFiltered = 0

DECLARE @DocumentDatesFiltered     BIT
DECLARE @DocumentDatesFrom         DATETIME
DECLARE @DocumentDatesTo           DATETIME
SET @DocumentDatesFiltered = 0

DECLARE @FolderSettingFiltered     BIT
DECLARE @FolderSettingNone         BIT
DECLARE @FolderSettingFolder       BIT
DECLARE @FolderSettingShared       BIT
SET @FolderSettingFiltered = 0

DECLARE @SearchFiltered            BIT
DECLARE @SearchInTable TABLE
       ([SearchField]  VARCHAR(50))
DECLARE @HasSearchFields           BIT
DECLARE @SearchFieldList           NVARCHAR(MAX)
DECLARE @SearchSelectorView        NVARCHAR(50)

SELECT @currenthandlerBIN = [P].[context_info]
  FROM sys.sysprocesses [P]
 WHERE [P].[spid] = @@SPID

SET @currenthandler = CONVERT(VARCHAR(10),
                              CASE WHEN CHARINDEX(CHAR(0),
                                                  CONVERT(VARCHAR(MAX),
                                                          @currenthandlerBIN)) > 0
                                        THEN SUBSTRING(CONVERT(VARCHAR(MAX),
                                                               @currenthandlerBIN),
                                                       1,
                                                       CHARINDEX(CHAR(0),
                                                                 CONVERT(VARCHAR(MAX),
                                                                 @currenthandlerBIN)) - 1)
                                   ELSE CONVERT(VARCHAR(MAX),
                                                @currenthandlerBIN) END)

SELECT @BitwiseID = [HNG].[BitWiseID]
  FROM [dbo].[Handlers] [HAN]
       INNER JOIN [dbo].[HandlerNETGroupMembership] [HNG]
               ON [HNG].[HANDLER] = [HAN].[CODE]
 WHERE [HAN].[CODE] = @currenthandler

BEGIN TRY
EXEC sp_xml_preparedocument @idoc OUTPUT, @XML
END TRY
BEGIN CATCH
EXEC sp_xml_preparedocument @idoc OUTPUT, '<Documents/>'
END CATCH

INSERT
  INTO @SearchInTable
      ([SearchField])
SELECT [SF].[SearchField]
  FROM OPENXML(@idoc, 'Documents/searchin/searchfield', 2)
  WITH ([SearchField]     VARCHAR(50)    '.') [SF]

SET @HasSearchFields = (SELECT CASE WHEN COUNT(1) = 0 THEN 0 ELSE 1 END FROM @SearchInTable)

SET @SearchSelectorView = N'[dbo].[DiaryAttachmentSelectorView2]'
SET @SearchFieldList = N'[TEXT]'
IF @HasSearchFields = 1
  BEGIN
    SET @SearchFieldList = N''
    SELECT @SearchFieldList = @SearchFieldList
                            + CASE WHEN @SearchFieldList = N''
                                        THEN N''
                                   ELSE N', ' END
                            + N'[' + CONVERT(NVARCHAR(50), [SIT].[SearchField]) + N']'
      FROM @SearchInTable [SIT]

    SET @SearchFieldList = N'(' + @SearchFieldList + N')'
    SET @SearchSelectorView = N'[dbo].[DiaryAttachmentSelectorView]'
  END

SELECT @matter      = [DAL].[matter],
       @DocFolderID = [DAL].[docfolderid],
       @ActionID    = [DAL].[selectedactionid],
       @PageNumber  = [DAL].[pagenumber],
       @PageSize    = [DAL].[pagesize],
       @SortBy      = [DAL].[sortby],
       @SortDesc    = [DAL].[sortdesc],
       @Search      = [DAL].[search],
       @parmhandler = [DAL].[currenthandler]
  FROM OPENXML(@idoc, 'Documents', 2)
  WITH([matter]           VARCHAR(20)    '@matter',
       [docfolderid]      INT            '@docfolderid',
       [selectedactionid] INT            '@selectedactionid',
       [pagenumber]       INT            '@pagenumber',
       [pagesize]         INT            '@pagesize',
       [sortby]           VARCHAR(50)    '@sortby',
       [sortdesc]         BIT            '@sortdesc',
       [search]           VARCHAR(200)   '@search',
       [currenthandler]   VARCHAR(10)    '@currenthandler') [DAL]

IF @currenthandler IS NULL
  BEGIN
    SET @currenthandler = @parmhandler
  END

IF @currenthandler IS NULL
  BEGIN
    SET @BitwiseID = 0
  END
ELSE IF @currenthandler = 'ADM'
  BEGIN
    SET @BitwiseID = CONVERT(BIGINT, -1)
  END
ELSE
  BEGIN
    SET @BitwiseID = ISNULL((SELECT [BitWiseID]
                               FROM [dbo].[HandlerNETGroupMembership] [HNG]
                              WHERE [HNG].[HANDLER] = @currenthandler), 0)
  END

IF RTRIM(ISNULL(@Search, '')) <> ''
  BEGIN
    SET @SearchFiltered = 1

    IF [dbo].[ky_NETFNCanHaveFulltext]() <> 1
      BEGIN
        --SET @IsAzure = 0 --PINO 20200327
        Set @HasFulltext = 0
        IF FULLTEXTSERVICEPROPERTY('ISFULLTEXTINSTALLED') = 1
          BEGIN
            SET @NCOMMAND = N'IF EXISTS
  (SELECT TOP 1 1
     FROM SYS.objects [SO]
          INNER JOIN SYS.fulltext_indexes [FI]
                  ON [FI].object_id = [SO].object_id
    WHERE [SO].[Name] = ''FulltextDiary''
      AND [SO].[type] = ''V'')
  BEGIN
    SET @HasFulltext = 1
  END'
            EXEC sp_executesql @NCOMMAND, N'@HasFulltext BIT OUT', @HasFulltext OUT
          END
      END
    ELSE
      BEGIN
        SET @HasFulltext = 1
      END
  END

IF @HasFullText = 1
  BEGIN
    SET @ExactPhrase = 0
    SET @Inflectional = 0
    IF CHARINDEX('"', @Search) > 0
      BEGIN
        SET @ExactPhrase = 1
      END
    ELSE IF CHARINDEX(' ', @Search) > 0
      BEGIN
        IF (    PATINDEX('% near %', @Search) = 0
            AND PATINDEX('% and %', @Search) = 0
            AND PATINDEX('% or %', @Search) = 0)
          BEGIN
            INSERT
              INTO @FullTextStopwords
                  ([stopword])
            EXEC [dbo].[ky_NETSPCheckFulltextSearch] @Search
            IF EXISTS (SELECT TOP 1 1 FROM @FullTextStopwords)
              BEGIN
                SET @HasFulltext = 0
              END
            ELSE
              BEGIN
                SET @ExactPhrase = 1
                SET @Search = '"' + @Search + '"'
              END
          END
        ELSE
          BEGIN
            INSERT
              INTO @FullTextStopwords
                  ([stopword])
            EXEC [dbo].[ky_NETSPCheckFulltextSearch] @Search

            DELETE @FullTextStopwords WHERE [stopword] IN (N'near', N'and', N'or')
          END
      END
    ELSE
      IF LEN(@Search) > 0
        BEGIN
          IF SUBSTRING(@Search, LEN(@Search), 1) = '*'
            BEGIN
              SET @Search = RTRIM(SUBSTRING(@Search, 1, LEN(@Search) - 1))
              SET @Inflectional = 1
            END
          ELSE
            BEGIN
              INSERT
                INTO @FullTextStopwords
                    ([stopword])
              EXEC [dbo].[ky_NETSPCheckFulltextSearch] @Search
            END
        END

    IF (@ExactPhrase = 0) AND (@Inflectional = 0)
      BEGIN
        IF CHARINDEX(' ', @Search) = 0
          BEGIN
            SET @SearchStem = '(' + [dbo].[ky_NETFNGetSearchStem](@Search) + ')'
          END
        ELSE IF PATINDEX('% and %', @Search) > 0
          BEGIN
            SET @SearchStem = @Search
          END
        ELSE IF PATINDEX('% or %', @Search) > 0
          BEGIN
            SET @SearchStem = @Search
          END
        ELSE IF PATINDEX('% near %', @Search) > 0
          BEGIN
            SET @SearchStem = @Search
          END
        ELSE
          BEGIN
            SET @SearchStem = '"' + @Search + '"'
          END

        SET @SearchInflectional = 'FORMSOF(INFLECTIONAL, ' + @Search + ')'
      END
  END

IF EXISTS (SELECT TOP 1 1 FROM @FullTextStopwords)
  BEGIN
    SET @HasFulltext = 0
  END

IF @HasFulltext = 0
  BEGIN
    IF SUBSTRING(@Search, 1, 1) <> '%'
      BEGIN
        SET @Search = '%' + @Search
      END
    IF SUBSTRING(@Search, LEN(@Search), 1) <> '%'
      BEGIN
        SET @Search = @Search + '%'
      END
  END

IF LEN(@Search) < 12
  BEGIN
    SET @SearchINTText = CONVERT(VARCHAR(10), REPLACE(@Search, '%', ''))
    IF ISNUMERIC(@SearchINTText) = 1
      BEGIN
          IF CHARINDEX('.', @SearchINTText) = 0
         AND CHARINDEX(',', @SearchINTText) = 0
         AND CHARINDEX('E', @SearchINTText) = 0
         AND CHARINDEX(' ', @SearchINTText) = 0
         AND CHARINDEX('-', @SearchINTText) = 0
         AND CHARINDEX('+', @SearchINTText) = 0
          BEGIN
            SET @SearchINT = CONVERT(INT, @SearchINTText)
          END
      END
  END

SELECT @XMLFilterCount = COUNT(1)
  FROM OPENXML(@idoc, 'Documents/Filters/Matters/Matter', 2)
          WITH([Value]           VARCHAR(20)     '@Value') [DAL]
IF @XMLFilterCount > 0
  BEGIN
    INSERT
      INTO @MatterSel
          ([CASECODE])
    SELECT TOP (@XMLFilterCount) [Value]
      FROM OPENXML(@idoc, 'Documents/Filters/Matters/Matter', 2)
              WITH([Value]          VARCHAR(20)    '@Value') [DAL]

    SET @matter = NULL
  END

SELECT @XMLFilterCount = COUNT(1)
  FROM OPENXML(@idoc, 'Documents/Filters/ActionTypes/ActionType', 2)
          WITH([Value]           VARCHAR(10)    '@Value') [DAL]
IF @XMLFilterCount > 0
  BEGIN
    SET @ActionTypesFiltered = 1

    INSERT
      INTO @ActionTypes
          ([ActionType])
    SELECT TOP (@XMLFilterCount) [Value]
      FROM OPENXML(@idoc, 'Documents/Filters/ActionTypes/ActionType', 2)
              WITH([Value]          VARCHAR(10)    '@Value') [DAL]
  END

SELECT @XMLFilterCount = COUNT(1)
  FROM OPENXML(@idoc, 'Documents/Filters/SigningStatuses/SigningStatus', 2)
          WITH([Value]           NVARCHAR(20)    '@Value') [DAL]
IF @XMLFilterCount > 0
  BEGIN
    SET @SigningStatusesFiltered = 1

    INSERT
      INTO @SigningStatuses
          ([SigningStatus])
    SELECT TOP (@XMLFilterCount) [Value]
      FROM OPENXML(@idoc, 'Documents/Filters/SigningStatuses/SigningStatus', 2)
              WITH([Value]          NVARCHAR(20)    '@Value') [DAL]
  END

SELECT @XMLFilterCount = COUNT(1)
  FROM OPENXML(@idoc, 'Documents/Filters/ProcessTypes/ProcessType', 2)
          WITH([Value]           VARCHAR(3)     '@Value') [DAL]
IF @XMLFilterCount > 0
  BEGIN
    SET @ProcessTypesFiltered = 1

    INSERT
      INTO @ProcessTypes
          ([ProcessType])
    SELECT TOP (@XMLFilterCount) [Value]
      FROM OPENXML(@idoc, 'Documents/Filters/ProcessTypes/ProcessType', 2)
              WITH([Value]          VARCHAR(1)     '@Value') [DAL]
  END

SELECT @XMLFilterCount = COUNT(1)
  FROM OPENXML(@idoc, 'Documents/Filters/DocClasses/DocClass', 2)
          WITH([Value]           VARCHAR(3)     '@Value') [DAL]
IF @XMLFilterCount > 0
  BEGIN
    SET @DocClassesFiltered = 1

    INSERT
      INTO @DocClasses
          ([DocClass])
    SELECT TOP (@XMLFilterCount) [Value]
      FROM OPENXML(@idoc, 'Documents/Filters/DocClasses/DocClass', 2)
              WITH([Value]          VARCHAR(3)     '@Value') [DAL]
  END

DECLARE @CLSIMXREF TABLE
       ([CLASSCODE]        VARCHAR(20)            NOT NULL      PRIMARY KEY,
        [IMCLASS]          VARCHAR(500)               NULL,
        [IMLOCATION]       VARCHAR(500)               NULL,
        [IMSUBCLASS]       VARCHAR(500)               NULL,
        UNIQUE NONCLUSTERED
              ([IMCLASS],
               [CLASSCODE]))

SELECT @XMLFilterCount = COUNT(1)
  FROM OPENXML(@idoc, 'Documents/Filters/DocumentSources/DocumentSource', 2)
          WITH([Value]           VARCHAR(20)     '@Value') [DAL]
IF @XMLFilterCount > 0
  BEGIN
    SET @DocumentSourcesFiltered = 1

    INSERT
      INTO @DocumentSources
          ([DocumentSource])
    SELECT TOP (@XMLFilterCount) [Value]
      FROM OPENXML(@idoc, 'Documents/Filters/DocumentSources/DocumentSource', 2)
              WITH([Value]          VARCHAR(20)    '@Value') [DAL]
  END

SELECT @XMLFilterCount = COUNT(1)
  FROM OPENXML(@idoc, 'Documents/Filters/Names/Name', 2)
          WITH([Value]           VARCHAR(10)     '@Value') [DAL]
IF @XMLFilterCount > 0
  BEGIN
    SET @NamesFiltered = 1

    INSERT
      INTO @Names
          ([Name])
    SELECT TOP (@XMLFilterCount) [Value]
      FROM OPENXML(@idoc, 'Documents/Filters/Names/Name', 2)
              WITH([Value]          VARCHAR(10)    '@Value') [DAL]
  END

SELECT @XMLFilterCount = COUNT(1)
  FROM OPENXML(@idoc, 'Documents/Filters/Types/Type', 2)
          WITH([Value]           VARCHAR(3)     '@Value') [DAL]
IF @XMLFilterCount > 0
  BEGIN
    SET @TypesFiltered = 1

    INSERT
      INTO @Types
          ([Type])
    SELECT TOP (@XMLFilterCount) [DAL].[Value]
      FROM OPENXML(@idoc, 'Documents/Filters/Types/Type', 2)
              WITH([Value]           VARCHAR(20)    '@Value') [DAL]
  END

IF EXISTS
  (SELECT TOP 1 1
     FROM OPENXML(@idoc, 'Documents/Filters/Attachments/Attachment', 2)
             WITH([Value]           VARCHAR(1)      '@Value') [DAL])
  BEGIN
    SET @AttachmentsFiltered = 1

    INSERT
      INTO @Attachments
          ([Attachments])
    SELECT TOP 2 --The only valid values are Y and N, and this prevents an ExcessiveGrant warning
           [DAL].[Value]
      FROM OPENXML(@idoc, 'Documents/Filters/Attachments/Attachment', 2)
              WITH([Value]           VARCHAR(1)     '@Value') [DAL]
  END

IF EXISTS
  (SELECT TOP 1 1
     FROM OPENXML(@idoc, 'Documents/Filters/Emails/Email', 2)
             WITH([Value]           VARCHAR(1)      '@Value') [DAL])
  BEGIN
    SET @EmailsFiltered = 1

    INSERT
      INTO @Emails
          ([Email])
    SELECT TOP 2 --The only valid values are Y and N, and this prevents an ExcessiveGrant warning
           [DAL].[Value]
      FROM OPENXML(@idoc, 'Documents/Filters/Emails/Email', 2)
              WITH([Value]           VARCHAR(1)     '@Value') [DAL]
  END

SELECT @EmailDatesFiltered    = 1,
       @EmailDatesFrom        = [DAL].[FromDate],
       @EmailDatesTo          = [DAL].[ToDate]
  FROM OPENXML(@idoc, 'Documents/Filters/EMAILSENT/Dates', 2)
          WITH([FromDate]            DATETIME       '@FromDate',
               [ToDate]              DATETIME       '@ToDate') [DAL]

SELECT @DiaryDatesFiltered    = 1,
       @DiaryDatesFrom        = [DAL].[FromDate],
       @DiaryDatesTo          = [DAL].[ToDate]
  FROM OPENXML(@idoc, 'Documents/Filters/DiaryDates/Dates', 2)
          WITH([FromDate]            DATETIME       '@FromDate',
               [ToDate]              DATETIME       '@ToDate') [DAL]

SELECT @DocumentDatesFiltered = 1,
       @DocumentDatesFrom     = [DAL].[FromDate],
       @DocumentDatesTo       = [DAL].[ToDate]
  FROM OPENXML(@idoc, 'Documents/Filters/DocumentDates/Dates', 2)
          WITH([FromDate]            DATETIME       '@FromDate',
               [ToDate]              DATETIME       '@ToDate') [DAL]

SELECT @FolderSettingFiltered = CASE WHEN [DAL].[FolderSettingNone] = 1
                                      AND [DAL].[FolderSettingFolder] = 1
                                      AND [DAL].[FolderSettingShared] = 1
                                          THEN NULL
                                     WHEN [DAL].[FolderSettingNone] = 1
                                          THEN 1
                                     WHEN [DAL].[FolderSettingFolder] = 1
                                          THEN 1
                                     WHEN [DAL].[FolderSettingShared] = 1
                                          THEN 1
                                     ELSE NULL END,
       @FolderSettingNone     = [DAL].[FolderSettingNone],
       @FolderSettingFolder   = [DAL].[FolderSettingFolder],
       @FolderSettingShared   = [DAL].[FolderSettingShared]
  FROM OPENXML(@idoc, 'Documents/Filters/FolderSetting/FolderSetting', 2)
          WITH([FolderSettingNone]   BIT            '@FolderSettingNone',
               [FolderSettingFolder] BIT            '@FolderSettingFolder',
               [FolderSettingShared] BIT            '@FolderSettingShared') [DAL]

DECLARE @Filtered BIT

SET @Filtered = 0

DECLARE @FilterOrder TABLE
       ([Type]       VARCHAR(50),
        [Count]      INT)

IF @matter is not null
  BEGIN
    INSERT
      INTO @MatterSel
          ([CASECODE])
    VALUES(@matter)
  END

-- Thanks to our Indexes all these COUNT operations are extremely cheap.
-- We fill the "FilterOrder" table, and then we'll process the filters listed in this
-- table in order of increasing number of matches.
-- Then we will apply the filters in THAT order - so the one with the LEAST matches
-- FIRST. This then massively reduces the amount of processing required to process
-- any subsequent filters.
IF @SearchFiltered = 1
  BEGIN
    IF (@HasFulltext = 1) --Azure
      BEGIN

        IF @Inflectional = 1
          BEGIN
            SET @Search = 'FORMSOF(INFLECTIONAL, ' + REPLACE(@Search, '''', '''''') + ')'
          END

        IF NOT EXISTS
          (SELECT TOP 1 1
             FROM @MatterSel)
          BEGIN
            IF (@ExactPhrase = 0) AND (@Inflectional = 0)
              BEGIN
                SET @NCOMMAND = N'SELECT [DID].[CASECODE]
  FROM CONTAINSTABLE(' + @SearchSelectorView + N', ' + @SearchFieldList + N', @SearchStem) [CT]
           INNER JOIN ' + @SearchSelectorView + N' [DID]
                      WITH (NOEXPAND, NOLOCK)
                   ON [DID].[TrackReference] = [CT].[Key]'

                IF CHARINDEX(' ', @SearchStem) = 0
                  BEGIN
                    SET @NCOMMAND = @NCOMMAND + N'
UNION
SELECT [DID].[CASECODE]
  FROM CONTAINSTABLE(' + @SearchSelectorView + N', ' + @SearchFieldList + N', @SearchInflectional) [CT]
           INNER JOIN ' + @SearchSelectorView + N' [DID]
                      WITH (NOEXPAND, NOLOCK)
                   ON [DID].[TrackReference] = [CT].[Key]'
                  END

                INSERT
                  INTO @MatterSel
                      ([CASECODE])
                EXEC sp_executesql @NCOMMAND, N'@SearchStem VARCHAR(2000), @SearchInflectional VARCHAR(280)', @SearchStem, @SearchInflectional
              END
            ELSE
              BEGIN
                SET @NCOMMAND = N'SELECT DISTINCT [DID].[CASECODE]
  FROM CONTAINSTABLE(' + @SearchSelectorView + N', ' + @SearchFieldList + N', @Search) [CT]
           INNER JOIN ' + @SearchSelectorView + N' [DID]
                      WITH (NOEXPAND, NOLOCK)
                   ON [DID].[TrackReference] = [CT].[Key]'

                INSERT
                  INTO @MatterSel
                      ([CASECODE])
                EXEC sp_executesql @NCOMMAND, N'@Search VARCHAR(250)', @Search
              END

            IF (SELECT COUNT(1) FROM @MatterSel) < 50 --If 50 or more matters this will be aborted so no point carrying on
              BEGIN
                IF ISNULL(@SearchINT, 0) <> 0
                  BEGIN
                    INSERT
                      INTO @MatterSel
                          ([CASECODE])
                    SELECT DISTINCT
                           [DAS].[CASECODE]
                      FROM [dbo].[DiaryAttachmentSelector] [DAS]
                           LEFT OUTER JOIN @MatterSel [MAT]
                                        ON [MAT].[CASECODE] = [DAS].[CASECODE]
                     WHERE [DAS].[TrackReference] = @SearchINT
                       AND [MAT].[CASECODE] IS NULL

                    INSERT
                      INTO @MatterSel
                          ([CASECODE])
                    SELECT DISTINCT
                           [DAS].[CASECODE]
                      FROM [dbo].[DiaryAttachmentSelector] [DAS]
                           LEFT OUTER JOIN @MatterSel [MAT]
                                        ON [MAT].[CASECODE] = [DAS].[CASECODE]
                     WHERE [DAS].[ActionID] = @SearchINT
                       AND [MAT].[CASECODE] IS NULL
                  END
              END

            IF @BitwiseID NOT IN (CONVERT(BIGINT, -1), CONVERT(BIGINT,0))
              BEGIN
                DELETE [MAT]
                  FROM @MatterSel [MAT]
                       INNER JOIN [dbo].[SearchMatters] [SM]
                               ON [SM].[Code] = [MAT].[CASECODE]
                 WHERE [SM].[Privileges] & @BitWiseID = 0
              END
            ELSE IF @BitWiseID = 0
              BEGIN
                DELETE [MAT]
                  FROM @MatterSel [MAT]
                       INNER JOIN [dbo].[SearchMatters] [SM]
                               ON [SM].[Code] = [MAT].[CASECODE]
                 WHERE [SM].[Privileges] NOT IN (CONVERT(BIGINT, -1), CONVERT(BIGINT,0))
              END
          END

        IF (SELECT COUNT(1) FROM @MatterSel) < 50 --If 50 or more matters this will be aborted so no point carrying on
          BEGIN
            IF (@ExactPhrase = 0) AND (@Inflectional = 0)
              BEGIN
                IF (SELECT COUNT(1) FROM @MatterSel) = 1
                  BEGIN
                    --PINO 2020-09-08
                    DECLARE @MATTERCODESTEM VARCHAR(22)
                    SET @MATTERCODESTEM = REPLACE((SELECT TOP 1 [CASECODE] FROM @MatterSel), '/', '')
                    IF CHARINDEX(' ', @MATTERCODESTEM) > 0
                      BEGIN
                        SET @MATTERCODESTEM = '"' + @MATTERCODESTEM + '"'
                      END
                    SET @SearchStem         = @MATTERCODESTEM + ' AND ' + @SearchStem
                    SET @SearchInflectional = @MATTERCODESTEM + ' AND ' + @SearchInflectional
                  END

                SET @NCOMMAND = N'
IF (SELECT COUNT(1) FROM @MatterSel) = 1
  BEGIN
    DECLARE @MATTER VARCHAR(20)
    SELECT TOP 1 @MATTER = [MAT].[CASECODE]
      FROM @MatterSel [MAT]

    SELECT ''Search'',
           COUNT(1)
      FROM (SELECT [DID].[TrackReference]
              FROM ' + @SearchSelectorView + N' [DID]
                   WITH (NOEXPAND, NOLOCK)
                   INNER JOIN CONTAINSTABLE(' + @SearchSelectorView + N', ' + @SearchFieldList + N', @SearchStem) [CT]
                           ON [CT].[Key] = [DID].[TrackReference]
             WHERE [DID].[CASECODE] = @MATTER'
                IF CHARINDEX(' ', @SearchStem) = 0
                  BEGIN
                    SET @NCOMMAND = @NCOMMAND + N'
            UNION
            SELECT [DID].[TrackReference]
              FROM ' + @SearchSelectorView + N' [DID]
                   WITH (NOEXPAND, NOLOCK)
                   INNER JOIN CONTAINSTABLE(' + @SearchSelectorView + N', ' + @SearchFieldList + N', @SearchInflectional) [CT]
                           ON [CT].[Key] = [DID].[TrackReference]
             WHERE [DID].[CASECODE] = @MATTER'
                  END
                SET @NCOMMAND = @NCOMMAND + N') [CNT]
  END
ELSE
  BEGIN
    SELECT ''Search'',
           COUNT(1)
      FROM (SELECT [DID].[TrackReference]
              FROM @MatterSel [MAT]
                   INNER JOIN ' + @SearchSelectorView + N' [DID]
                              WITH (NOEXPAND, NOLOCK)
                              INNER JOIN CONTAINSTABLE(' + @SearchSelectorView + N', ' + @SearchFieldList + N', @SearchStem) [CT]
                                      ON [CT].[Key] = [DID].[TrackReference]
                           ON [DID].[CASECODE] = [MAT].[CASECODE]'
                IF CHARINDEX(' ', @SearchStem) = 0
                  BEGIN
                    SET @NCOMMAND = @NCOMMAND +  + N'
            UNION
            SELECT [DID].[TrackReference]
              FROM @MatterSel [MAT]
                   INNER JOIN ' + @SearchSelectorView + N' [DID]
                              WITH (NOEXPAND, NOLOCK)
                              INNER JOIN CONTAINSTABLE(' + @SearchSelectorView + N', ' + @SearchFieldList + N', @SearchInflectional) [CT]
                                      ON [CT].[Key] = [DID].[TrackReference]
                           ON [DID].[CASECODE] = [MAT].[CASECODE]'
                  END
                SET @NCOMMAND = @NCOMMAND +  + N') [CNT]
  END'
              END
            ELSE
              BEGIN
                IF (SELECT COUNT(1) FROM @MatterSel) = 1
                  BEGIN
                    --PINO 2020-09-08
                    DECLARE @MATTERCODESTEM2 VARCHAR(22)
                    SET @MATTERCODESTEM2 = REPLACE((SELECT TOP 1 [CASECODE] FROM @MatterSel), '/', '')
                    IF CHARINDEX(' ', @MATTERCODESTEM2) > 0
                      BEGIN
                        SET @MATTERCODESTEM2 = '"' + @MATTERCODESTEM2 + '"'
                      END
                    SET @Search             = @MATTERCODESTEM2 + ' AND ' + @Search
                  END

                SET @NCOMMAND = N'
IF (SELECT COUNT(1) FROM @MatterSel) = 1
  BEGIN
    DECLARE @MATTER VARCHAR(20)
    SELECT TOP 1 @MATTER = [MAT].[CASECODE]
      FROM @MatterSel [MAT]

    SELECT ''Search'',
           COUNT(1)
      FROM ' + @SearchSelectorView + N' [DID]
           WITH (NOEXPAND, NOLOCK)
           INNER JOIN CONTAINSTABLE(' + @SearchSelectorView + N', ' + @SearchFieldList + N', @Search) [CT]
                   ON [CT].[Key] = [DID].[TrackReference]
     WHERE [DID].[CASECODE] = @MATTER
  END
ELSE
  BEGIN
    SELECT ''Search'',
           COUNT(1)
      FROM @MatterSel [MAT]
           INNER JOIN ' + @SearchSelectorView + N' [DID]
                      WITH (NOEXPAND, NOLOCK)
                      INNER JOIN CONTAINSTABLE(' + @SearchSelectorView + N', ' + @SearchFieldList + N', @Search) [CT]
                              ON [CT].[Key] = [DID].[TrackReference]
                   ON [DID].[CASECODE] = [MAT].[CASECODE]
  END'
              END
          END
      END
    ELSE
      BEGIN
        IF NOT EXISTS
          (SELECT TOP 1 1
             FROM @MatterSel)
          BEGIN
           SELECT @MatterSelCount = COUNT(1)
             FROM (SELECT TOP 50
                          [DD].[ACTIONID]
                     FROM [dbo].[DiaryData] [DD]
                          WITH (NOEXPAND)
                    WHERE CASE WHEN PATINDEX(@SEARCH, [DD].[SUBJECT]) > 0
                                    THEN 1
                               WHEN PATINDEX(@SEARCH, [DD].[EmailSynopsis]) > 0
                                    THEN 1
                               WHEN PATINDEX(@SEARCH, [DD].[OtherSynopsis]) > 0
                                    THEN 1
                               WHEN PATINDEX(@SEARCH, [DD].[TEXT1]) > 0
                                    THEN 1
                               ELSE 0 END = 1
                 ORDER BY [DD].[ACTIONID] DESC) [UGH]
            IF @MatterSelCount = 0
              BEGIN
                INSERT
                  INTO @Issues
                      ([Issue],
                       [Data])
                VALUES('Narrow Search',
                       'No records found to match your search.')
              END

            IF @MatterSelCount = 50
              BEGIN
                SET @MatterSelBroad = 1
                SET @CaseLabel = ISNULL((SELECT RTRIM(ISNULL([KeyValue], ''))
                                           FROM [dbo].[Settings] [SET]
                                          WHERE [SET].[KeyName] = 'CaseLabel'), 'Case')

                INSERT
                  INTO @Issues
                      ([Issue],
                       [Data])
                VALUES('Search is too broad',
                       'At least 50 ' + @CaseLabel + 's contain documents matching your search.
This would cause the Search to take an excessive amount of time to complete.

Your search has been aborted. Please narrow down your search criteria.')
              END

            IF (@MatterSelCount > 0) AND (@MatterSelCount < 50)
              BEGIN
                SET @NCOMMAND = N'
  SELECT TOP 10
         [DD].[CASECODE]
    FROM [dbo].[DiaryData] [DD]
         WITH (NOEXPAND)
   WHERE [DD].[TEXT1] LIKE @Search
GROUP BY [DD].[CASECODE]
ORDER BY COUNT(1) DESC'
                INSERT
                  INTO @MatterSel
                      ([CASECODE])
                EXEC sp_executesql @NCOMMAND, N'@Search VARCHAR(250)', @Search

                IF @MatterSelCount < 50
                  BEGIN
                    SELECT @MatterSelCount = COUNT(1) FROM @MatterSel

                    IF @MatterSelCount < 10
                      BEGIN
                        SET @NCOMMAND = N'
  SELECT TOP (10 - @MatterSelCount)
         [DD].[CASECODE]
    FROM [dbo].[DiaryData] [DD]
         WITH (NOEXPAND)
         LEFT OUTER JOIN @MatterSel [MAT]
                      ON [MAT].[CASECODE] = [DD].[CASECODE]
   WHERE [DD].[EmailSynopsis] LIKE @Search
     AND [MAT].[CASECODE] IS NULL
GROUP BY [DD].[CASECODE]
ORDER BY COUNT(1) DESC'
                        INSERT
                          INTO @MatterSel
                              ([CASECODE])
                        EXEC sp_executesql @NCOMMAND, N'@Search VARCHAR(250), @MatterSelCount INT, @MatterSel MatterSel READONLY', @Search, @MatterSelCount, @MatterSel
                      END
                  END
              END

            IF @MatterSelCount < 50
              BEGIN
                IF ISNULL(@SearchINT, 0) <> 0
                  BEGIN
                    INSERT
                      INTO @MatterSel
                          ([CASECODE])
                    SELECT DISTINCT
                           [DAS].[CASECODE]
                      FROM [dbo].[DiaryAttachmentSelector] [DAS]
                           LEFT OUTER JOIN @MatterSel [MAT]
                                        ON [MAT].[CASECODE] = [DAS].[CASECODE]
                     WHERE [DAS].[TrackReference] = @SearchINT
                       AND [MAT].[CASECODE] IS NULL

                    INSERT
                      INTO @MatterSel
                          ([CASECODE])
                    SELECT DISTINCT
                           [DAS].[CASECODE]
                      FROM [dbo].[DiaryAttachmentSelector] [DAS]
                           LEFT OUTER JOIN @MatterSel [MAT]
                                        ON [MAT].[CASECODE] = [DAS].[CASECODE]
                     WHERE [DAS].[ActionID] = @SearchINT
                       AND [MAT].[CASECODE] IS NULL
                  END
              END

            IF @BitwiseID NOT IN (CONVERT(BIGINT, -1), CONVERT(BIGINT,0))
              BEGIN
                DELETE [MAT]
                  FROM @MatterSel [MAT]
                       INNER JOIN [dbo].[SearchMatters] [SM]
                               ON [SM].[Code] = [MAT].[CASECODE]
                 WHERE [SM].[Privileges] & @BitWiseID = 0
              END
            ELSE IF @BitWiseID = 0
              BEGIN
                DELETE [MAT]
                  FROM @MatterSel [MAT]
                       INNER JOIN [dbo].[SearchMatters] [SM]
                               ON [SM].[Code] = [MAT].[CASECODE]
                 WHERE [SM].[Privileges] NOT IN (CONVERT(BIGINT, -1), CONVERT(BIGINT,0))
              END
          END
        ELSE
          BEGIN
            SELECT @MatterSelCount = 1
          END

        IF @MatterSelBroad = 1
          BEGIN
            SET @NCOMMAND = N'SELECT ''Search'', 10000'
          END
        ELSE IF (@MatterSelCount > 0) AND (@MatterSelBroad = 0)
          BEGIN
            SET @NCOMMAND = N'DECLARE @COUNT INT
SELECT @COUNT = (SELECT COUNT(1)
                   FROM @MatterSel [MAT]
                        INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                     ON [DAS].[CASECODE] = [MAT].[CASECODE])
IF @COUNT > 3000
  BEGIN
    -- Once there are more than a few thousand documents already in the case, this check itself will
    -- become too expensive, and it would be better to just do this search LAST
    SELECT ''Search'',
           @COUNT
  END
ELSE
  BEGIN
    SELECT ''Search'',
           COUNT(1)
      FROM @MatterSel [MAT]
           INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                      INNER JOIN [dbo].[diary] [DIA]
                              ON [DIA].[ActionID] = [DAS].[ActionID]
                      --LEFT OUTER JOIN [dbo].[DiaryAttachmentText] [DTX]
                      --             ON [DTX].[TrackReference] = [DAS].[TrackReference]
                   ON [DAS].[CASECODE] = [MAT].[CASECODE]
                  AND CASE WHEN ISNULL([DAS].[DOCUMENT], '''') LIKE @Search
                                THEN 1
                           WHEN ISNULL([DIA].[SUBJECT], '''') LIKE @Search
                                THEN 1
                           WHEN ISNULL([DIA].[DisplayText], '''') LIKE @Search
                                THEN 1
                           --WHEN CONVERT(VARCHAR(1000), ISNULL([DTX].[TEXT], '''')) LIKE @Search
                           --     THEN 1
                           WHEN CONVERT(VARCHAR(1000), ISNULL([DIA].[TEXT1], '''')) LIKE @Search
                                THEN 1
                           WHEN CONVERT(VARCHAR(1000), ISNULL([DIA].[TEXT2], '''')) LIKE @Search
                                THEN 1
                           ELSE 0 END = 1
  END'
          END
        ELSE
          BEGIN
            SET @NCOMMAND = NULL
          END
      END

    IF (SELECT COUNT(1) FROM @MatterSel) < 50 --If 50 or more matters this will be aborted so no point carrying on
      BEGIN
        IF @NCOMMAND IS NOT NULL
          BEGIN
            INSERT
              INTO @FilterOrder
                  ([Type],
                   [Count])
            EXEC sp_executesql @NCOMMAND, N'@MatterSel MatterSel READONLY, @Search VARCHAR(250), @SearchStem VARCHAR(2000), @SearchInflectional VARCHAR(280)', @MatterSel, @Search, @SearchStem, @SearchInflectional
          END
      END
  END

IF (SELECT COUNT(1) FROM @MatterSel) > 49
  BEGIN
    SET @CaseLabel = ISNULL((SELECT RTRIM(ISNULL([KeyValue], ''))
                               FROM [dbo].[Settings] [SET]
                              WHERE [SET].[KeyName] = 'CaseLabel'), 'Case')

    INSERT
      INTO @Issues
          ([Issue],
           [Data])
    VALUES(CONVERT(NVARCHAR(50), 'Too Many ' + @CaseLabel + 's'),
           CONVERT(NVARCHAR(MAX), 'We found ' + CONVERT(VARCHAR, (SELECT COUNT(1) FROM @MatterSel)) + ' ' + @CaseLabel + 's containing documents that match your Search Criteria.
This would cause the Search to take an excessive amount of time to complete.

Your search has been aborted. Please narrow down your search criteria.'))

    DELETE @MatterSel
  END

IF @SigningStatusesFiltered = 1
  BEGIN
    INSERT
      INTO @FilterOrder
          ([Type],
           [Count])
    SELECT 'SigningStatuses',
           COUNT(1)
      FROM @MatterSel [MAT]
           INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                   ON [DAS].[CASECODE] = [MAT].[CASECODE]
                  AND [DAS].[SigningStatus] IN (SELECT [SEL].[SigningStatus] FROM @SigningStatuses [SEL])
  END


IF @ActionTypesFiltered = 1
  BEGIN
    INSERT
      INTO @FilterOrder
          ([Type],
           [Count])
    SELECT 'ActionTypes',
           COUNT(1)
      FROM @MatterSel [MAT]
           INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                   ON [DAS].[CASECODE] = [MAT].[CASECODE]
                  AND [DAS].[ActionType] IN (SELECT [SEL].[ActionType] FROM @ActionTypes [SEL])
  END

IF @ProcessTypesFiltered = 1
  BEGIN
    INSERT
      INTO @FilterOrder
          ([Type],
           [Count])
    SELECT 'ProcessTypes',
           COUNT(1)
      FROM @MatterSel [MAT]
           INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                   ON [DAS].[CASECODE] = [MAT].[CASECODE]
                  AND [DAS].[PROCESSTYPE] IN (SELECT [SEL].[ProcessType] FROM @ProcessTypes [SEL])
  END

IF @DocClassesFiltered = 1
  BEGIN
    INSERT
      INTO @FilterOrder
          ([Type],
           [Count])
    SELECT 'DocClasses',
           COUNT(1)
      FROM @MatterSel [MAT]
           INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                   ON [DAS].[CASECODE] = [MAT].[CASECODE]
                  AND [DAS].[DocClass] IN (SELECT [SEL].[DocClass] FROM @DocClasses [SEL])
  END

IF @DocumentSourcesFiltered = 1
  BEGIN
    INSERT
      INTO @FilterOrder
          ([Type],
           [Count])
    SELECT 'DocumentSources',
           COUNT(1)
      FROM @MatterSel [MAT]
           INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                   ON [DAS].[CASECODE] = [MAT].[CASECODE]
                  AND [DAS].[SOURCE] IN (SELECT [SEL].[DocumentSource] FROM @DocumentSources [SEL])
  END

IF @NamesFiltered = 1
  BEGIN
    INSERT
      INTO @FilterOrder
          ([Type],
           [Count])
    SELECT 'Names',
           COUNT(1)
      FROM @MatterSel [MAT]
           INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                   ON [DAS].[CASECODE] = [MAT].[CASECODE]
                  AND [DAS].[NAME] IN (SELECT [SEL].[Name] FROM @Names [SEL])
  END

IF @TypesFiltered = 1
  BEGIN
    INSERT
      INTO @FilterOrder
          ([Type],
           [Count])
    SELECT 'Types',
           COUNT(1)
      FROM @MatterSel [MAT]
           INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                   ON [DAS].[CASECODE] = [MAT].[CASECODE]
                  AND [DAS].[FileType] IN (SELECT [SEL].[Type] FROM @Types [SEL])
  END

IF @AttachmentsFiltered = 1
  BEGIN
    INSERT
      INTO @FilterOrder
          ([Type],
           [Count])
    SELECT 'Attachments',
           COUNT(1)
      FROM @MatterSel [MAT]
           INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                   ON [DAS].[CASECODE] = [MAT].[CASECODE]
                  AND [DAS].[ATTACHMENTS] IN (SELECT [SEL].[Attachments] FROM @Attachments [SEL])
  END

IF @EmailsFiltered = 1
  BEGIN
    INSERT
      INTO @FilterOrder
          ([Type],
           [Count])
    SELECT 'Emails',
           COUNT(1)
      FROM @MatterSel [MAT]
           INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                   ON [DAS].[CASECODE] = [MAT].[CASECODE]
                  AND [DAS].[EMAIL] IN (SELECT [SEL].[Email] FROM @Emails [SEL])
  END

IF @EmailDatesFiltered = 1
  BEGIN
    SET @EmailDatesFrom = ISNULL(@EmailDatesFrom, CONVERT(DATETIME, '18000101'))
    SET @EmailDatesTo = ISNULL(@EmailDatesTo, CONVERT(DATETIME, '22000101'))
    BEGIN
      INSERT
        INTO @FilterOrder
            ([Type],
             [Count])
      SELECT 'EmailDates',
             COUNT(1)
        FROM @MatterSel [MAT]
             INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                     ON [DAS].[CASECODE] = [MAT].[CASECODE]
                    AND [DAS].[EMAILSENT] >= @EmailDatesFrom
                    AND [DAS].[EMAILSENT] < DATEADD(DAY, 1, @EmailDatesTo)
      END
  END

IF @DiaryDatesFiltered = 1
  BEGIN
    SET @DiaryDatesFrom = ISNULL(@DiaryDatesFrom, CONVERT(DATETIME, '18000101'))
    SET @DiaryDatesTo = ISNULL(@DiaryDatesTo, CONVERT(DATETIME, '22000101'))
    BEGIN
      INSERT
        INTO @FilterOrder
            ([Type],
             [Count])
      SELECT 'DiaryDates',
             COUNT(1)
        FROM @MatterSel [MAT]
             INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                     ON [DAS].[CASECODE] = [MAT].[CASECODE]
                    AND [DAS].[DiaryDate] >= @DiaryDatesFrom
                    AND [DAS].[DiaryDate] < DATEADD(DAY, 1, @DiaryDatesTo)
      END
  END

IF @DocumentDatesFiltered = 1
  BEGIN
    SET @DocumentDatesFrom = ISNULL(@DocumentDatesFrom, CONVERT(DATETIME, '18000101'))
    SET @DocumentDatesTo = ISNULL(@DocumentDatesTo, CONVERT(DATETIME, '22000101'))
    BEGIN
      INSERT
        INTO @FilterOrder
            ([Type],
             [Count])
      SELECT 'DocumentDates',
             COUNT(1)
        FROM @MatterSel [MAT]
             INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                     ON [DAS].[CASECODE] = [MAT].[CASECODE]
                    AND [DAS].[DocumentDate] >= @DocumentDatesFrom
                    AND [DAS].[DocumentDate] < DATEADD(DAY, 1, @DocumentDatesTo)
    END
  END

IF @FolderSettingFiltered = 1
  BEGIN
    SET @FilterCount = 0
    IF ISNULL(@FolderSettingShared, 0) = 1
      BEGIN
        SELECT @FilterCount = @FilterCount + COUNT(1)
          FROM @MatterSel [MAT]
               INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                       ON [DAS].[CASECODE] = [MAT].[CASECODE]
                      AND [DAS].[IsShared] = 1
      END

    IF ISNULL(@FolderSettingFolder, 0) = 1
      BEGIN
        SELECT @FilterCount = @FilterCount + COUNT(1)
          FROM @MatterSel [MAT]
               INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                       ON [DAS].[CASECODE] = [MAT].[CASECODE]
                      AND [DAS].[InDocFolder] = 1
                      AND [DAS].[IsShared] = 0
      END

    IF ISNULL(@FolderSettingNone, 0) = 1
      BEGIN
        SELECT @FilterCount = @FilterCount + COUNT(1)
          FROM @MatterSel [MAT]
               INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                       ON [DAS].[CASECODE] = [MAT].[CASECODE]
                      AND [DAS].[InDocFolder] = 0
      END

    BEGIN
      INSERT
        INTO @FilterOrder
            ([Type],
             [Count])
      VALUES('FolderSetting',
             @FilterCount)
    END
  END

DECLARE @FilterOrderField VARCHAR(50)
DECLARE @FilterOrderCount INT

SELECT @FilterOrderCount = COUNT(1) FROM @FilterOrder

WHILE EXISTS
     (SELECT TOP 1 1
        FROM @FilterOrder)
  BEGIN
    SET @FilterOrderField = NULL
      SELECT TOP 1 @FilterOrderField = [FIL].[Type]
        FROM @FilterOrder [FIL]
    ORDER BY [FIL].[Count]

    --SELECT 'Filter Order Field: ', @FilterOrderField

    IF @FilterOrderField = 'ActionTypes'
      BEGIN
        IF @Filtered = 0
          BEGIN
            INSERT
              INTO @TrackND
                  ([TrackReference])
            SELECT [DAS].[TrackReference]
              FROM @MatterSel [MAT]
                   INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                           ON [DAS].[CASECODE] = [MAT].[CASECODE]
                          AND [DAS].[ActionType] IN (SELECT [SEL].[ActionType] FROM @ActionTypes [SEL])
          END
        ELSE
          BEGIN
            DELETE [TRK]
              FROM @MatterSel [MAT]
                   INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                              INNER JOIN @TrackND [TRK]
                                      ON [TRK].[TrackReference] = [DAS].[TrackReference]
                           ON [DAS].[CASECODE] = [MAT].[CASECODE]
                          AND [DAS].[ActionType] NOT IN (SELECT [SEL].[ActionType] FROM @ActionTypes [SEL])
          END
        SET @Filtered = 1
      END

    IF @FilterOrderField = 'SigningStatuses'
      BEGIN
        IF @Filtered = 0
          BEGIN
            INSERT
              INTO @TrackND
                  ([TrackReference])
            SELECT [DAS].[TrackReference]
              FROM @MatterSel [MAT]
                   INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                           ON [DAS].[CASECODE] = [MAT].[CASECODE]
                          AND [DAS].[SigningStatus] IN (SELECT [SEL].[SigningStatus] FROM @SigningStatuses [SEL])
          END
        ELSE
          BEGIN
            DELETE [TRK]
              FROM @MatterSel [MAT]
                   INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                              INNER JOIN @TrackND [TRK]
                                      ON [TRK].[TrackReference] = [DAS].[TrackReference]
                           ON [DAS].[CASECODE] = [MAT].[CASECODE]
                          AND [DAS].[SigningStatus] NOT IN (SELECT [SEL].[SigningStatus] FROM @SigningStatuses [SEL])
          END
        SET @Filtered = 1
      END

    IF @FilterOrderField = 'ProcessTypes'
      BEGIN
        IF @Filtered = 0
          BEGIN
            INSERT
              INTO @TrackND
                  ([TrackReference])
            SELECT [DAS].[TrackReference]
              FROM @MatterSel [MAT]
                   INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                           ON [DAS].[CASECODE] = [MAT].[CASECODE]
                          AND [DAS].[PROCESSTYPE] IN (SELECT [SEL].[ProcessType] FROM @ProcessTypes [SEL])
          END
        ELSE
          BEGIN
            DELETE [TRK]
              FROM @MatterSel [MAT]
                   INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                              INNER JOIN @TrackND [TRK]
                                      ON [TRK].[TrackReference] = [DAS].[TrackReference]
                           ON [DAS].[CASECODE] = [MAT].[CASECODE]
                          AND [DAS].[PROCESSTYPE] NOT IN (SELECT [SEL].[ProcessType] FROM @ProcessTypes [SEL])
          END
        SET @Filtered = 1
      END

    IF @FilterOrderField = 'DocClasses'
      BEGIN
        IF @Filtered = 0
          BEGIN
            INSERT
              INTO @TrackND
                  ([TrackReference])
            SELECT [DAS].[TrackReference]
              FROM @MatterSel [MAT]
                   INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                           ON [DAS].[CASECODE] = [MAT].[CASECODE]
                          AND [DAS].[DocClass] IN (SELECT [SEL].[DocClass] FROM @DocClasses [SEL])
          END
        ELSE
          BEGIN
            DELETE [TRK]
              FROM @MatterSel [MAT]
                   INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                              INNER JOIN @TrackND [TRK]
                                      ON [TRK].[TrackReference] = [DAS].[TrackReference]
                           ON [DAS].[CASECODE] = [MAT].[CASECODE]
                          AND [DAS].[DocClass] NOT IN (SELECT [SEL].[DocClass] FROM @DocClasses [SEL])
          END
        SET @Filtered = 1
      END

    IF @FilterOrderField = 'DocumentSources'
      BEGIN
        IF @Filtered = 0
          BEGIN
            INSERT
              INTO @TrackND
                  ([TrackReference])
            SELECT [DAS].[TrackReference]
              FROM @MatterSel [MAT]
                   INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                           ON [DAS].[CASECODE] = [MAT].[CASECODE]
                          AND [DAS].[SOURCE] IN (SELECT [SEL].[DocumentSource] FROM @DocumentSources [SEL])
          END
        ELSE
          BEGIN
            DELETE [TRK]
              FROM @MatterSel [MAT]
                   INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                              INNER JOIN @TrackND [TRK]
                                      ON [TRK].[TrackReference] = [DAS].[TrackReference]
                           ON [DAS].[CASECODE] = [MAT].[CASECODE]
                          AND [DAS].[SOURCE] NOT IN (SELECT [SEL].[DocumentSource] FROM @DocumentSources [SEL])
          END
        SET @Filtered = 1
      END

    IF @FilterOrderField = 'Names'
      BEGIN
        IF @Filtered = 0
          BEGIN
            INSERT
              INTO @TrackND
                  ([TrackReference])
            SELECT [DAS].[TrackReference]
              FROM @MatterSel [MAT]
                   INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                           ON [DAS].[CASECODE] = [MAT].[CASECODE]
                          AND [DAS].[NAME] IN (SELECT [SEL].[Name] FROM @Names [SEL])
          END
        ELSE
          BEGIN
            DELETE [TRK]
              FROM @MatterSel [MAT]
                   INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                              INNER JOIN @TrackND [TRK]
                                      ON [TRK].[TrackReference] = [DAS].[TrackReference]
                           ON [DAS].[CASECODE] = [MAT].[CASECODE]
                          AND [DAS].[NAME] NOT IN (SELECT [SEL].[Name] FROM @Names [SEL])
          END
        SET @Filtered = 1
      END

    IF @FilterOrderField = 'Types'
      BEGIN
        IF @Filtered = 0
          BEGIN
            INSERT
              INTO @TrackND
                  ([TrackReference])
            SELECT [DAS].[TrackReference]
              FROM @MatterSel [MAT]
                   INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                           ON [DAS].[CASECODE] = [MAT].[CASECODE]
                          AND [DAS].[FileType] IN (SELECT [SEL].[Type] FROM @Types [SEL])
          END
        ELSE
          BEGIN
            DELETE [TRK]
              FROM @MatterSel [MAT]
                   INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                              INNER JOIN @TrackND [TRK]
                                      ON [TRK].[TrackReference] = [DAS].[TrackReference]
                           ON [DAS].[CASECODE] = [MAT].[CASECODE]
                          AND [DAS].[FileType] NOT IN (SELECT [SEL].[Type] FROM @Types [SEL])
          END
        SET @Filtered = 1
      END

    IF @FilterOrderField = 'Attachments'
      BEGIN
        IF @Filtered = 0
          BEGIN
            INSERT
              INTO @TrackND
                  ([TrackReference])
            SELECT [DAS].[TrackReference]
              FROM @MatterSel [MAT]
                   INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                           ON [DAS].[CASECODE] = [MAT].[CASECODE]
                          AND [DAS].[ATTACHMENTS] IN (SELECT [SEL].[Attachments] FROM @Attachments [SEL])
          END
        ELSE
          BEGIN
            DELETE [TRK]
              FROM @MatterSel [MAT]
                   INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                              INNER JOIN @TrackND [TRK]
                                      ON [TRK].[TrackReference] = [DAS].[TrackReference]
                           ON [DAS].[CASECODE] = [MAT].[CASECODE]
                          AND [DAS].[ATTACHMENTS] NOT IN (SELECT [SEL].[Attachments] FROM @Attachments [SEL])
          END
        SET @Filtered = 1
      END

    IF @FilterOrderField = 'Emails'
      BEGIN
        IF @Filtered = 0
          BEGIN
            INSERT
              INTO @TrackND
                  ([TrackReference])
            SELECT [DAS].[TrackReference]
              FROM @MatterSel [MAT]
                   INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                           ON [DAS].[CASECODE] = [MAT].[CASECODE]
                          AND [DAS].[EMAIL] IN (SELECT [SEL].[Email] FROM @Emails [SEL])
          END
        ELSE
          BEGIN
            DELETE [TRK]
              FROM @MatterSel [MAT]
                   INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                              INNER JOIN @TrackND [TRK]
                                      ON [TRK].[TrackReference] = [DAS].[TrackReference]
                           ON [DAS].[CASECODE] = [MAT].[CASECODE]
                          AND [DAS].[EMAIL] NOT IN (SELECT [SEL].[Email] FROM @Emails [SEL])
          END
        SET @Filtered = 1
      END

    IF @FilterOrderField = 'EmailDates'
      BEGIN
        IF @Filtered = 0
          BEGIN
            INSERT
              INTO @TrackND
                  ([TrackReference])
            SELECT [DAS].[TrackReference]
              FROM @MatterSel [MAT]
                   INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                           ON [DAS].[CASECODE] = [MAT].[CASECODE]
                          AND [DAS].[EMAILSENT] >= @EmailDatesFrom
                          AND [DAS].[EMAILSENT] < DATEADD(DAY, 1, @EmailDatesTo)
          END
        ELSE
          BEGIN
            DELETE [TRK]
              FROM @MatterSel [MAT]
                   INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                              INNER JOIN @TrackND [TRK]
                                      ON [TRK].[TrackReference] = [DAS].[TrackReference]
                           ON [DAS].[CASECODE] = [MAT].[CASECODE]
                          AND (   [DAS].[EMAILSENT] < @EmailDatesFrom
                               OR [DAS].[EMAILSENT] >= DATEADD(DAY, 1, @EmailDatesTo))
          END
        SET @Filtered = 1
      END

    IF @FilterOrderField = 'DiaryDates'
      BEGIN
        IF @Filtered = 0
          BEGIN
            INSERT
              INTO @TrackND
                  ([TrackReference])
            SELECT [DAS].[TrackReference]
              FROM @MatterSel [MAT]
                   INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                           ON [DAS].[CASECODE] = [MAT].[CASECODE]
                          AND [DAS].[DiaryDate] >= @DiaryDatesFrom
                          AND [DAS].[DiaryDate] < DATEADD(DAY, 1, @DiaryDatesTo)
          END
        ELSE
          BEGIN
            DELETE [TRK]
              FROM @MatterSel [MAT]
                   INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                              INNER JOIN @TrackND [TRK]
                                      ON [TRK].[TrackReference] = [DAS].[TrackReference]
                           ON [DAS].[CASECODE] = [MAT].[CASECODE]
                          AND (   [DAS].[DiaryDate] < @DiaryDatesFrom
                               OR [DAS].[DiaryDate] >= DATEADD(DAY, 1, @DiaryDatesTo))
          END
        SET @Filtered = 1
      END

    IF @FilterOrderField = 'DocumentDates'
      BEGIN
        IF @Filtered = 0
          BEGIN
            INSERT
              INTO @TrackND
                  ([TrackReference])
            SELECT [DAS].[TrackReference]
              FROM @MatterSel [MAT]
                   INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                           ON [DAS].[CASECODE] = [MAT].[CASECODE]
                          AND [DAS].[DocumentDate] >= @DocumentDatesFrom
                          AND [DAS].[DocumentDate] < DATEADD(DAY, 1, @DocumentDatesTo)
          END
        ELSE
          BEGIN
            DELETE [TRK]
              FROM @MatterSel [MAT]
                   INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                              INNER JOIN @TrackND [TRK]
                                      ON [TRK].[TrackReference] = [DAS].[TrackReference]
                           ON [DAS].[CASECODE] = [MAT].[CASECODE]
                          AND (   [DAS].[DocumentDate] < @DocumentDatesFrom
                               OR [DAS].[DocumentDate] >= DATEADD(DAY, 1, @DocumentDatesTo))
          END
        SET @Filtered = 1
      END

    IF @FilterOrderField = 'FolderSetting'
      BEGIN
        IF @Filtered = 0
          BEGIN
            IF ISNULL(@FolderSettingShared, 0) = 1
              BEGIN
                INSERT
                  INTO @TrackND
                      ([TrackReference])
                SELECT [DAS].[TrackReference]
                  FROM @MatterSel [MAT]
                       INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                               ON [DAS].[CASECODE] = [MAT].[CASECODE]
                              AND [DAS].[IsShared] = 1
              END

            IF ISNULL(@FolderSettingFolder, 0) = 1
              BEGIN
                INSERT
                  INTO @TrackND
                      ([TrackReference])
                SELECT [DAS].[TrackReference]
                  FROM @MatterSel [MAT]
                       INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                               ON [DAS].[CASECODE] = [MAT].[CASECODE]
                              AND [DAS].[InDocFolder] = 1
                              AND [DAS].[IsShared] = 0
              END

            IF ISNULL(@FolderSettingNone, 0) = 1
              BEGIN
                INSERT
                  INTO @TrackND
                      ([TrackReference])
                SELECT [DAS].[TrackReference]
                  FROM @MatterSel [MAT]
                       INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                               ON [DAS].[CASECODE] = [MAT].[CASECODE]
                              AND [DAS].[InDocFolder] = 0
              END
          END
        ELSE
          BEGIN
            IF ISNULL(@FolderSettingNone, 0) = 1 AND ISNULL(@FolderSettingFolder, 0) = 1
              BEGIN
                DELETE [TRK]
                  FROM @MatterSel [MAT]
                       INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                  INNER JOIN @TrackND [TRK]
                                          ON [TRK].[TrackReference] = [DAS].[TrackReference]
                               ON [DAS].[CASECODE] = [MAT].[CASECODE]
                              AND [DAS].[IsShared] = 1
              END
            ELSE IF ISNULL(@FolderSettingNone, 0) = 1 AND ISNULL(@FolderSettingShared, 0) = 1
              BEGIN
                DELETE [TRK]
                  FROM @MatterSel [MAT]
                       INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                  INNER JOIN @TrackND [TRK]
                                          ON [TRK].[TrackReference] = [DAS].[TrackReference]
                               ON [DAS].[CASECODE] = [MAT].[CASECODE]
                              AND [DAS].[InDocFolder] = 1
                              AND [DAS].[IsShared] = 0
              END
            ELSE IF ISNULL(@FolderSettingNone, 0) = 1
              BEGIN
                DELETE [TRK]
                  FROM @MatterSel [MAT]
                       INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                  INNER JOIN @TrackND [TRK]
                                          ON [TRK].[TrackReference] = [DAS].[TrackReference]
                               ON [DAS].[CASECODE] = [MAT].[CASECODE]
                              AND [DAS].[InDocFolder] = 1
              END
            ELSE IF ISNULL(@FolderSettingFolder, 0) = 1 AND ISNULL(@FolderSettingShared, 0) = 1
              BEGIN
                DELETE [TRK]
                  FROM @MatterSel [MAT]
                       INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                  INNER JOIN @TrackND [TRK]
                                          ON [TRK].[TrackReference] = [DAS].[TrackReference]
                               ON [DAS].[CASECODE] = [MAT].[CASECODE]
                              AND [DAS].[InDocFolder] = 0
              END
            ELSE IF ISNULL(@FolderSettingFolder, 0) = 1
              BEGIN
                DELETE [TRK]
                  FROM @MatterSel [MAT]
                       INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                  INNER JOIN @TrackND [TRK]
                                          ON [TRK].[TrackReference] = [DAS].[TrackReference]
                               ON [DAS].[CASECODE] = [MAT].[CASECODE]
                              AND (   [DAS].[InDocFolder] = 0
                                   OR [DAS].[IsShared] = 1)
              END
            ELSE -- @FolderSettingShared = 1
              BEGIN
                DELETE [TRK]
                  FROM @MatterSel [MAT]
                       INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                  INNER JOIN @TrackND [TRK]
                                          ON [TRK].[TrackReference] = [DAS].[TrackReference]
                               ON [DAS].[CASECODE] = [MAT].[CASECODE]
                              AND [DAS].[IsShared] = 0
              END

            DELETE [TRK]
              FROM @MatterSel [MAT]
                   INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                              INNER JOIN @TrackND [TRK]
                                      ON [TRK].[TrackReference] = [DAS].[TrackReference]
                           ON [DAS].[CASECODE] = [MAT].[CASECODE]
          END
        SET @Filtered = 1
      END

    IF @FilterOrderField = 'Search'
      BEGIN
        IF @Filtered = 0
          BEGIN
            -- PINO 20200327 START - remove conditional processing on @HasFulltext = 1 AND @IsAzure = 0
  --          IF (@HasFulltext = 1) AND (@IsAzure = 0)
  --            BEGIN
  --              SET @NCOMMAND = N'SELECT [DAS].[TrackReference]
  --FROM @MatterSel [MAT]
  --     INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
  --                INNER JOIN [dbo].[FulltextDiary] [FTD]
  --                        ON [FTD].[TrackReference] = [DAS].[TrackReference]
  --                       AND CONTAINS(([FTD].[TEXT1],
  --                                     [FTD].[TEXT2],
  --                                     [FTD].[DOCUMENT],
  --                                     [FTD].[DocumentText]), @Search)
  --             ON [DAS].[CASECODE] = [MAT].[CASECODE]'
  --            END
  --          ELSE IF (@HasFulltext = 1) --Azure
            IF (@HasFulltext = 1) --Azure
            -- PINO 20200327 END - remove conditional processing on @HasFulltext = 1 AND @IsAzure = 0
              BEGIN
                IF (@ExactPhrase = 0) AND (@Inflectional = 0)
                  BEGIN
                    SET @NCOMMAND = N'SELECT [DID].[Trackreference]
  FROM @MatterSel [MAT]
       INNER JOIN ' + @SearchSelectorView + N' [DID]
                  WITH (NOEXPAND, NOLOCK)
                  INNER JOIN CONTAINSTABLE(' + @SearchSelectorView + N', ' + @SearchFieldList + N', @SearchStem) [CT]
                          ON [CT].[Key] = [DID].[TrackReference]
               ON [DID].[CASECODE] = [MAT].[CASECODE]'
                    IF CHARINDEX(' ', @SearchStem) = 0
                      BEGIN
                        SET @NCOMMAND = @NCOMMAND + N'
UNION
SELECT [DID].[Trackreference]
  FROM @MatterSel [MAT]
       INNER JOIN ' + @SearchSelectorView + N' [DID]
                  WITH (NOEXPAND, NOLOCK)
                  INNER JOIN CONTAINSTABLE(' + @SearchSelectorView + N', ' + @SearchFieldList + N', @SearchInflectional) [CT]
                          ON [CT].[Key] = [DID].[TrackReference]
               ON [DID].[CASECODE] = [MAT].[CASECODE]'
                      END
                  END
                ELSE
                  BEGIN
                    SET @NCOMMAND = N'SELECT [DID].[Trackreference]
  FROM @MatterSel [MAT]
       INNER JOIN ' + @SearchSelectorView + N' [DID]
                  WITH (NOEXPAND, NOLOCK)
                  INNER JOIN CONTAINSTABLE(' + @SearchSelectorView + N', ' + @SearchFieldList + N', @Search) [CT]
                          ON [CT].[Key] = [DID].[TrackReference]
               ON [DID].[CASECODE] = [MAT].[CASECODE]'
                  END
              END
            ELSE IF @MatterSelBroad = 0
              BEGIN
                SET @NCOMMAND = N'
  SELECT TOP 100 PERCENT
         [DAS].[TrackReference]
    FROM [dbo].[DiaryData] [DD]
         WITH (NOEXPAND)
         INNER JOIN @MatterSel [MAT]
                 ON [MAT].[CASECODE] = [DD].[CASECODE]
         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                 ON [DAS].[CASECODE] = [DD].[CASECODE]
                AND [DAS].[ACTIONID] = [DD].[ACTIONID]
   WHERE CASE WHEN PATINDEX(@SEARCH, [DAS].[DOCUMENT]) > 0
                   THEN 1
              WHEN PATINDEX(@SEARCH, [DD].[SUBJECT]) > 0
                   THEN 1
              WHEN PATINDEX(@SEARCH, [DD].[EmailSynopsis]) > 0
                   THEN 1
              WHEN PATINDEX(@SEARCH, [DD].[OtherSynopsis]) > 0
                   THEN 1
              WHEN PATINDEX(@SEARCH, [DD].[TEXT1]) > 0
                   THEN 1
              ELSE 0 END = 1
ORDER BY [DD].[ACTIONID] DESC'
              END
            ELSE
              BEGIN
                SET @NCOMMAND = N'
  SELECT TOP 200
         [DAS].[TrackReference]
    FROM [dbo].[DiaryData] [DD]
         WITH (NOEXPAND)
         INNER JOIN @MatterSel [MAT]
                 ON [MAT].[CASECODE] = [DD].[CASECODE]
         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                 ON [DAS].[CASECODE] = [DD].[CASECODE]
                AND [DAS].[ACTIONID] = [DD].[ACTIONID]
   WHERE CASE WHEN PATINDEX(@SEARCH, [DAS].[DOCUMENT]) > 0
                   THEN 1
              WHEN PATINDEX(@SEARCH, [DD].[SUBJECT]) > 0
                   THEN 1
              WHEN PATINDEX(@SEARCH, [DD].[EmailSynopsis]) > 0
                   THEN 1
              WHEN PATINDEX(@SEARCH, [DD].[OtherSynopsis]) > 0
                   THEN 1
              WHEN PATINDEX(@SEARCH, [DD].[TEXT1]) > 0
                   THEN 1
              ELSE 0 END = 1
ORDER BY [DD].[ACTIONID] DESC'
              END

            INSERT
              INTO @TrackND
                  ([TrackReference])
            EXEC sp_executesql @NCOMMAND, N'@MatterSel MatterSel READONLY, @Search VARCHAR(250), @SearchStem VARCHAR(2000), @SearchInflectional VARCHAR(280)', @MatterSel, @Search, @SearchStem, @SearchInflectional

            IF ISNULL(@SearchINT, 0) <> 0
              BEGIN
                INSERT
                  INTO @TrackND
                      ([TrackReference])
                SELECT [DAS].[TrackReference]
                  FROM @MatterSel [MAT]
                       INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                  LEFT OUTER JOIN @TrackND [TND]
                                               ON [TND].[TrackReference] = [DAS].[TrackReference]
                               ON [DAS].[CASECODE] = [MAT].[CASECODE]
                              AND [DAS].[TrackReference] = @SearchINT
                 WHERE [TND].[TrackReference] IS NULL

                INSERT
                  INTO @TrackND
                      ([TrackReference])
                SELECT [DAS].[TrackReference]
                  FROM @MatterSel [MAT]
                       INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                  LEFT OUTER JOIN @TrackND [TND]
                                               ON [TND].[TrackReference] = [DAS].[TrackReference]
                               ON [DAS].[CASECODE] = [MAT].[CASECODE]
                              AND [DAS].[ActionID] = @SearchINT
                 WHERE [TND].[TrackReference] IS NULL
              END
          END
        ELSE
          BEGIN
            -- PINO 20200327 START - remove conditional processing on @HasFulltext = 1 AND @IsAzure = 0
  --          IF (@HasFulltext = 1) AND (@IsAzure = 0)
  --            BEGIN
  --              SET @NCOMMAND = N'SELECT [DAS].[TrackReference]
  --FROM @MatterSel [MAT]
  --     INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
  --                INNER JOIN @TrackND [TND]
  --                        ON [TND].[TrackReference] = [DAS].[TrackReference]
  --                INNER JOIN [dbo].[FulltextDiary] [FTD]
  --                        ON [FTD].[TrackReference] = [DAS].[TrackReference]
  --                       AND NOT CONTAINS(([FTD].[TEXT1],
  --                                         [FTD].[TEXT2],
  --                                         [FTD].[DOCUMENT],
  --                                         [FTD].[DocumentText]), @Search)
  --             ON [DAS].[CASECODE] = [MAT].[CASECODE]'
  --            END
  --          ELSE IF (@HasFulltext = 1) -- Azure
            IF (@HasFulltext = 1) -- Azure
            -- PINO 20200327 END - remove conditional processing on @HasFulltext = 1 AND @IsAzure = 0
              BEGIN
                IF (@ExactPhrase = 0) AND (@Inflectional = 0)
                  BEGIN
                    SET @NCOMMAND = N'SELECT [DID].[Trackreference]
  FROM @MatterSel [MAT]
       INNER JOIN ' + @SearchSelectorView + N' [DID]
                  WITH (NOEXPAND, NOLOCK)
                  INNER JOIN CONTAINSTABLE(' + @SearchSelectorView + N', ' + @SearchFieldList + N', @SearchStem) [CT]
                          ON [CT].[Key] = [DID].[TrackReference]
               ON [DID].[CASECODE] = [MAT].[CASECODE]
UNION
SELECT [DID].[Trackreference]
  FROM @MatterSel [MAT]
       INNER JOIN ' + @SearchSelectorView + N' [DID]
                  WITH (NOEXPAND, NOLOCK)
                  INNER JOIN CONTAINSTABLE(' + @SearchSelectorView + N', ' + @SearchFieldList + N', @SearchInflectional) [CT]
                          ON [CT].[Key] = [DID].[TrackReference]
               ON [DID].[CASECODE] = [MAT].[CASECODE]'
                  END
                ELSE
                  BEGIN
                    SET @NCOMMAND = N'SELECT [DID].[Trackreference]
  FROM @MatterSel [MAT]
       INNER JOIN ' + @SearchSelectorView + N' [DID]
                  WITH (NOEXPAND, NOLOCK)
                  INNER JOIN CONTAINSTABLE(' + @SearchSelectorView + N', ' + @SearchFieldList + N', @Search) [CT]
                          ON [CT].[Key] = [DID].[TrackReference]
               ON [DID].[CASECODE] = [MAT].[CASECODE]'
                  END
              END
            ELSE
              BEGIN
                SET @NCOMMAND = N'
    SELECT [DAS].[TrackReference]
      FROM @MatterSel [MAT]
           INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                      INNER JOIN @TrackND [TND]
                              ON [TND].[TrackReference] = [DAS].[TrackReference]
                      --LEFT OUTER JOIN [dbo].[DiaryAttachmentText] [DTX]
                      --             ON [DTX].[TrackReference] = [DAS].[TrackReference]
                      INNER JOIN [dbo].[diary] [DIA]
                              ON [DIA].[ActionID] = [DAS].[ActionID]
                   ON [DAS].[CASECODE] = [MAT].[CASECODE]
                  AND CASE WHEN ISNULL([DAS].[DOCUMENT], '''') LIKE @Search
                                THEN 1
                           WHEN ISNULL([DIA].[SUBJECT], '''') LIKE @Search
                                THEN 1
                           WHEN ISNULL([DIA].[DisplayText], '''') LIKE @Search
                                THEN 1
                           --WHEN CONVERT(VARCHAR(1000), ISNULL([DTX].[TEXT], '''')) LIKE @Search
                           --     THEN 1
                           WHEN CONVERT(VARCHAR(1000), ISNULL([DIA].[TEXT1], '''')) LIKE @Search
                                THEN 1
                           WHEN CONVERT(VARCHAR(1000), ISNULL([DIA].[TEXT2], '''')) LIKE @Search
                                THEN 1
                           ELSE 0 END = 0'
              END

            INSERT
              INTO @TrackSR
                  ([TrackReference])
            EXEC sp_executesql @NCOMMAND, N'@MatterSel MatterSel READONLY, @Search VARCHAR(250), @SearchStem VARCHAR(2000), @SearchInflectional VARCHAR(280), @TrackND TrackRefsND READONLY', @MatterSel, @Search, @SearchStem, @SearchInflectional, @TrackND

            --Pino 2019-11-08 We were deleting search *matches*. We should be deleting search *mismatches*
            DELETE [TRK]
              FROM @TrackND [TRK]
                   LEFT OUTER JOIN @TrackSR [DAS]
                                ON [DAS].[TrackReference] = [TRK].[TrackReference]
             WHERE [DAS].[TrackReference] IS NULL
          END
        SET @Filtered = 1
      END

    DELETE @FilterOrder WHERE [Type] = @FilterOrderField
  END

SET @SortBy = CASE WHEN @SortBy IS NULL
                        THEN NULL
                   WHEN @SortBy IN ('ActionType',
                                    'ACTIONID',
                                    'ATTACHMENTS',
                                    'DiaryDate',
                                    'Document',
                                    'DocumentClass',
                                    'DocumentDate',
                                    'DocumentSource',
                                    'EMAIL',
                                    'EMAILADDRESS',
                                    'EMAILSENT',
                                    'FolderSetting',
                                    'InDocFolder',
                                    'IsShared',
                                    'NAME',
                                    'PROCESSTYPE',
                                    'SigningStatus',
                                    'TYPE')
                        THEN @SortBy
                   ELSE NULL END

DECLARE @HASIMDOCS BIT

SET @HASIMDOCS = 0

IF (ISNULL(@DocFolderID, 0) = 0) AND (ISNULL(@ActionID, 0) = 0)
  BEGIN
    IF (@DocumentSourcesFiltered = 0) OR EXISTS(SELECT TOP 1 1 FROM @DocumentSources [SRC] WHERE [SRC].[DocumentSource] = 'IManage')
      BEGIN
        IF EXISTS
          (SELECT TOP 1 1
             FROM OPENXML(@idoc, 'Documents/IMDocs/Doc', 2)
                     WITH([IMDocID]        VARCHAR(500)    '@DocId') [DAL])
          BEGIN
            INSERT
              INTO @IMDocTable
                  ([IMDocID],
                   [Description],
                   [IMClass],
                   [IMType],
                   [EmailFrom],
                   [EmailTo],
                   [EmailSent],
                   [DocumentDate])
            SELECT DISTINCT
                   [DAL].[IMDocID],
                   [DAL].[Description],
                   [DAL].[IMClass],
                   [DAL].[IMType],
                   [DAL].[EmailFrom],
                   [DAL].[EmailTo],
                   [DAL].[EmailSent],
                   [DAL].[DocumentDate]
              FROM OPENXML(@idoc, 'Documents/IMDocs/Doc', 2)
                      WITH([IMDocID]        VARCHAR(500)    '@DocId',
                           [Description]    VARCHAR(2000)   '@Description',
                           [IMClass]        VARCHAR(20)     '@ClassId',
                           [IMType]         VARCHAR(20)     '@TypeId',
                           [EmailFrom]      VARCHAR(200)    '@EmailFrom',
                           [EmailTo]        VARCHAR(1000)   '@EmailTo',
                           [EmailSent]      DATETIME        '@EmailSent',
                           [DocumentDate]   DATETIME        '@DocumentDate') [DAL]

            IF RTRIM(ISNULL(@SEARCH, '')) <> ''
              BEGIN
                DELETE @IMDocTable
                 WHERE [Description] NOT LIKE '%' + @Search + '%'
                   AND [EmailFrom] NOT LIKE '%' + @Search + '%'   --2021-05-12
                   AND [EmailTo] NOT LIKE '%' + @Search + '%'     --2021-05-12
              END

            IF @EmailDatesFiltered = 1
              BEGIN
                DELETE @IMDocTable WHERE [EmailSent] < @EmailDatesFrom
                DELETE @IMDocTable WHERE [EmailSent] >= DATEADD(DAY, 1, @EmailDatesTo)
              END

            IF @DiaryDatesFiltered = 1
              BEGIN
                -- In IManage there is no "diary" so we use the document date here, too
                DELETE @IMDocTable WHERE [DocumentDate] < @DiaryDatesFrom
                DELETE @IMDocTable WHERE [DocumentDate] >= DATEADD(DAY, 1, @DiaryDatesTo)
              END

            IF @DocumentDatesFiltered = 1
              BEGIN
                DELETE @IMDocTable WHERE [DocumentDate] < @DocumentDatesFrom
                DELETE @IMDocTable WHERE [DocumentDate] >= DATEADD(DAY, 1, @DocumentDatesTo)
              END
          END

        IF EXISTS
          (SELECT TOP 1 1
             FROM @IMDocTable)
        BEGIN
          SET @HASIMDOCS = 1

          IF (@DocClassesFiltered = 1)
            BEGIN
              INSERT
                INTO @CLSIMXREF
                    ([CLASSCODE],
                     [IMCLASS],
                     [IMLOCATION],
                     [IMSUBCLASS])
              SELECT [IMX].[CLASSCODE],
                     [IMX].[IMCLASS],
                     [IMX].[IMLOCATION],
                     [IMX].[IMSUBCLASS]
                FROM [dbo].[CLSIMXREF] [IMX]
                     INNER JOIN @DocClasses [DCL]
                             ON [DCL].[DocClass] = [IMX].[CLASSCODE]
            END
          ELSE
            BEGIN
              INSERT
                INTO @CLSIMXREF
                    ([CLASSCODE],
                     [IMCLASS],
                     [IMLOCATION],
                     [IMSUBCLASS])
              SELECT [IMX].[CLASSCODE],
                     [IMX].[IMCLASS],
                     [IMX].[IMLOCATION],
                     [IMX].[IMSUBCLASS]
                FROM [dbo].[CLSIMXREF] [IMX]
                     INNER JOIN [dbo].[DocumentClasses] [DCL]
                             ON [DCL].[CLASSCODE] = [IMX].[CLASSCODE]
            END

          /* Insert IManage details into this table
             ORIGINALLY: Only include records not found in [dbo].[DAIMXRef] (IM records we track in KH)
             CURRENTLY: Always populate with IM records (ones we don't track, and ones we do) - Later we will filter out duplicates
             This allows the IM details to take precedence over the KH details (e.g. if renamed in IM then we see the name in KH) */
          INSERT
            INTO @res
                ([ActionId],
                 [CASECODE],
                 [TrackReference],
                 [IMDocID],
                 [IMClass],
                 [DocClass],
                 [Document],
                 [DiaryDate],
                 [DocumentDate],
                 [EmailSent],
                 [TYPE],
                 [Attachments],
                 [EMAIL],
                 [EmailFrom],
                 [EmailTo],
                 [SigningStatus],
                 [CurrentVersion],
                 [IsInDocFolder],
                 [IsShared],
                 [MatterUniqueID])
          SELECT ISNULL([DAS].[ActionID], 0)                                            AS [ActionID],
                 ISNULL([DAS].[CASECODE], '')                                           AS [CASECODE],
                 ISNULL([DX].[TrackReference], 0)                                       AS [TrackReference],
                 [DAL].[IMDocID]                                                        AS [IMDocID],
                 [DAL].[IMClass]                                                        AS [IMClass],
                 [DCL].[CLASSCODE]                                                      AS [DocClass],
                 [DAL].[Description]                                                    AS [Document],
                 convert(DATETIME,
                         ISNULL([DAL].[DocumentDate],
                                [DAL].[EmailSent]))                                     AS [DiaryDate],
                 convert(DATETIME,
                         ISNULL([DAL].[DocumentDate],
                                [DAL].[EmailSent]))                                     AS [DocumentDate],
                 convert(DATETIME,
                         [DAL].[EmailSent])                                             AS [EMAILSENT],
                 CASE convert(VARCHAR(20),
                              [DAL].[IMTYPE])
                      WHEN 'WORD'      THEN 'DOC'
                      WHEN 'WORDX'     THEN 'DOC'
                      WHEN 'WPF'       THEN 'DOC'
                      WHEN 'ANSI'      THEN 'DOC'
                      WHEN 'ACROBAT'   THEN 'PDF'
                      WHEN 'EXCEL'     THEN 'XLS'
                      WHEN 'EXCELX'    THEN 'XLS'
                      WHEN 'MIME'      THEN CASE WHEN [DAL].[IMClass] = 'E-Mail'
                                                      THEN 'MSG'
                                                 ELSE 'MIME' END
                      ELSE [DAL].[IMTYPE] END                                           AS [TYPE],
                 CONVERT(CHAR(1),
                         'N')                                                           AS [Attachments],
                 CONVERT(CHAR(1), CASE WHEN ISNULL([DAL].[IMClass], '') = 'E-Mail'
                                            THEN 'Y'
                                       ELSE 'N' END)                                    As [EMAIL],
                 CASE WHEN ISNULL([DAL].[IMClass], '') = 'E-Mail'
                           THEN [DAL].[EMAILFROM]
                      ELSE CONVERT(VARCHAR(200), NULL) END                              AS [EmailFrom],
                 CASE WHEN ISNULL([DAL].[IMClass], '') = 'E-Mail'
                           THEN [DAL].[EMAILTO]
                      ELSE CONVERT(VARCHAR(1000), NULL) END                             AS [EmailTo],
                 [DAS].[SigningStatus]                                                  AS [SigningStatus],
                 CONVERT(INT, 1)                                                        AS [CurrentVersion],
                 0                                                                      AS [IsInDocFolder],
                 0                                                                      AS [IsShared],
                 ISNULL([DAS].[MatterUniqueID], 0)                                      AS [MatterUniqueID]
            FROM @IMDocTable [DAL]
                 LEFT OUTER JOIN [dbo].[DAIMXref] [DX]
                              ON [DX].[IMDocID] = [DAL].[IMDocID]
                 LEFT OUTER JOIN [dbo].[DiaryAttachmentSelector] AS [DAS]
                              ON [DX].[TrackReference] = [DAS].[TrackReference]
                 LEFT OUTER JOIN @Types [TYP]
                              ON [TYP].[Type] = CASE convert(VARCHAR(20),
                                                             [DAL].[IMTYPE])
                                                     WHEN 'WORD'      THEN 'DOC'
                                                     WHEN 'WORDX'     THEN 'DOC'
                                                     WHEN 'WPF'       THEN 'DOC'
                                                     WHEN 'ANSI'      THEN 'DOC'
                                                     WHEN 'ACROBAT'   THEN 'PDF'
                                                     WHEN 'EXCEL'     THEN 'XLS'
                                                     WHEN 'EXCELX'    THEN 'XLS'
                                                     WHEN 'MIME'      THEN CASE WHEN [DAL].[IMClass] = 'E-Mail'
                                                                                     THEN 'E-Mail'
                                                                                ELSE 'MIME' END
                                                     ELSE [DAL].[IMTYPE] END
                 OUTER APPLY (SELECT TOP 1
                                     [IMX].[CLASSCODE]
                                FROM @CLSIMXREF [IMX]
                               WHERE [IMX].[IMCLASS] = [DAL].[IMCLASS]) [DCL]
           WHERE CASE WHEN @TypesFiltered = 0
                           THEN 1
                      WHEN [TYP].[Type] IS NULL
                           THEN 0
                      ELSE 1 END = 1
             AND CASE WHEN @DocClassesFiltered = 0
                           THEN 1
                      WHEN [DCL].[CLASSCODE] IS NULL
                           THEN 0
                      ELSE 1 END = 1
             /* Took this out so that IM records within our system are still added to this table
                Later on we will stop duplicates and choose IM details over KH details */
             --AND [DX].[TrackReference] IS NULL

          /* This table is used to hold track references and sorter value
             Later when displaying the results we join from this table
             to [dbo].[DiaryAttachmentSelector] to get KH-only documents

             IM documents are entered here and later joined back onto @res
             to include them in the result.

             We need to make sure we filter out records which exist in both
             systems to avoid showing the same document twice */
          INSERT
            INTO @Tracks
                ([TrackReference],
                 [RESID],
                 [SORTER])
          SELECT ISNULL([RES].[TrackReference], 0),
                 [RES].[id],
                 CASE WHEN @SortBy = 'DiaryDate'
                           THEN CONVERT(VARCHAR, [RES].[DiaryDate], 121)
                      WHEN @SortBy = 'Document'
                           THEN CONVERT(VARCHAR(50), [RES].[Document])
                      WHEN @SortBy = 'DocumentDate'
                           THEN CONVERT(VARCHAR, [RES].[DocumentDate], 121)
                      WHEN @SortBy = 'DocumentClass'
                           THEN RTRIM(ISNULL([DCL].[CLASSDESCRIPTION], ''))
                      WHEN @SortBy = 'DocumentSource'
                           THEN 'IManage'
                      WHEN @SortBy = 'EMAIL'
                           THEN [RES].[EMAIL]
                      WHEN @SortBy = 'EMAILADDRESS'
                           THEN CONVERT(VARCHAR(50), REPLACE([RES].[EMAILFROM], '"', ''))
                      WHEN @SortBy = 'EMAILSENT'
                           THEN CONVERT(VARCHAR, [RES].[EMAILSENT], 121)
                      WHEN @SortBy = 'NAME'
                           THEN CONVERT(VARCHAR(50), [RES].[Document])
                      WHEN @SortBy = 'SigningStatus'
                           THEN CONVERT(VARCHAR(50), [RES].[SigningStatus])
                      WHEN @SortBy = 'TYPE'
                           THEN [RES].[TYPE]
                      ELSE '' END
            FROM @res [RES]
                 LEFT OUTER JOIN [dbo].[DocumentClasses] [DCL]
                              ON [DCL].[CLASSCODE] = [RES].[DocClass]
        END
      END
END

EXEC sp_xml_removedocument @idoc

DECLARE @NewTotalCount INT

DECLARE @MAXRESULT INT

IF @HASIMDOCS = 1
  BEGIN
    SET @PageNumber = 0
  END

IF @FilterOrderCount < 2
  BEGIN
    IF ISNULL(@PageNumber, 0) = 0
      BEGIN
        SET @MAXRESULT = 999999
      END
    ELSE IF ISNULL(@PageSize, 0) = 0
      BEGIN
        SET @MAXRESULT = 999999
      END
    ELSE
      BEGIN
        SET @MAXRESULT = (@PageNumber * @PageSize)
      END
  END
ELSE
  BEGIN
    SET @MAXRESULT = 999999
  END

IF @Filtered = 0
  BEGIN
    SELECT @NewTotalCount = COUNT(1)
      FROM @MatterSel [MAT]
           INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                   ON [DAS].[CASECODE] = [MAT].[CASECODE]

    IF ISNULL(@ActionID, 0) = 0 AND ISNULL(@DocFolderID, 0) = 0 AND @HASIMDOCS = 0
      BEGIN
        -- When we are searching within a Case there can be tens of thousands of matching documents. To get the
        -- best performance we ensure that the system uses an INDEX when sorting. The only field for which this
        -- is not possible is the EMAILADDRESS
        IF @SortBy IS NULL
          BEGIN
            INSERT
              INTO @Tracks
                  ([TrackReference],
                   [RESID],
                   [SORTER])
            SELECT TOP (@MAXRESULT)
                   [DAS].[TrackReference],
                   0,
                   ''
              FROM @MatterSel [MAT]
                   INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                           ON [DAS].[CASECODE] = [MAT].[CASECODE]
          END
        ELSE IF @SortBy IN ('EMAILADDRESS')
          BEGIN
            INSERT
              INTO @Tracks
                  ([TrackReference],
                   [RESID],
                   [SORTER])
            SELECT TOP (@MAXRESULT)
                   [DAS].[TrackReference],
                   0,
                   CASE WHEN @SortBy = 'EMAILADDRESS'
                             THEN CONVERT(VARCHAR(50), REPLACE([DAS].[EMAILADDRESS], '"', ''))
                        ELSE '' END
              FROM @MatterSel [MAT]
                   INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                           ON [DAS].[CASECODE] = [MAT].[CASECODE]
          END
        ELSE IF @SortBy = 'ACTIONID'
          BEGIN
            IF ISNULL(@SortDesc, 1) = 1
              BEGIN
                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                ORDER BY [DAS].[CASECODE] DESC,
                         [DAS].[ACTIONID] DESC
              END
            ELSE
              BEGIN
                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                ORDER BY [DAS].[CASECODE],
                         [DAS].[ACTIONID]
              END
            SET @SortBy = NULL
          END
        ELSE IF @SortBy = 'ActionType'
          BEGIN
            IF ISNULL(@SortDesc, 1) = 1
              BEGIN
                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                ORDER BY [DAS].[CASECODE] DESC,
                         [DAS].[ActionTypeDescription] DESC
              END
            ELSE
              BEGIN
                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                ORDER BY [DAS].[CASECODE],
                         [DAS].[ActionTypeDescription]
              END
            SET @SortBy = NULL
          END
        ELSE IF @SortBy = 'ATTACHMENTS'
          BEGIN
            IF ISNULL(@SortDesc, 1) = 1
              BEGIN
                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                ORDER BY [DAS].[CASECODE] DESC,
                         [DAS].[ATTACHMENTS] DESC
              END
            ELSE
              BEGIN
                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                ORDER BY [DAS].[CASECODE],
                         [DAS].[ATTACHMENTS]
              END
            SET @SortBy = NULL
          END
        ELSE IF @SortBy = 'DiaryDate'
          BEGIN
            IF ISNULL(@SortDesc, 1) = 1
              BEGIN
                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                ORDER BY [DAS].[CASECODE] DESC,
                         [DAS].[DiaryDate] DESC
              END
            ELSE
              BEGIN
                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                ORDER BY [DAS].[CASECODE],
                         [DAS].[DiaryDate]
              END
            SET @SortBy = NULL
          END
        ELSE IF @SortBy = 'Document'
          BEGIN
            IF ISNULL(@SortDesc, 1) = 1
              BEGIN
                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                                AND [DAS].[Document] <> ''
                ORDER BY [DAS].[CASECODE] DESC,
                         [DAS].[Document] DESC
                IF @MAXRESULT = 999999
                  BEGIN
                    SET @NewTotalCount = (SELECT COUNT(1) FROM @Tracks)
                  END
              END
            ELSE
              BEGIN
                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                                AND [DAS].[Document] <> ''
                ORDER BY [DAS].[CASECODE],
                         [DAS].[Document]
                IF @MAXRESULT = 999999
                  BEGIN
                    SET @NewTotalCount = (SELECT COUNT(1) FROM @Tracks)
                  END
              END
            SET @SortBy = NULL
          END
        ELSE IF @SortBy = 'DocumentClass'
          BEGIN
            IF ISNULL(@SortDesc, 1) = 1
              BEGIN
                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                ORDER BY [DAS].[CASECODE] DESC,
                         [DAS].[DocumentClassDescription] DESC
              END
            ELSE
              BEGIN
                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                ORDER BY [DAS].[CASECODE],
                         [DAS].[DocumentClassDescription]
              END
            SET @SortBy = NULL
          END
        ELSE IF @SortBy = 'DocumentDate'
          BEGIN
            IF ISNULL(@SortDesc, 1) = 1
              BEGIN
                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                ORDER BY [DAS].[CASECODE] DESC,
                         [DAS].[DocumentDate] DESC
              END
            ELSE
              BEGIN
                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                ORDER BY [DAS].[CASECODE],
                         [DAS].[DocumentDate]
              END
            SET @SortBy = NULL
          END
        ELSE IF @SortBy = 'DocumentSource'
          BEGIN
            IF ISNULL(@SortDesc, 1) = 1
              BEGIN
                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                                AND [DAS].[SOURCE] <> ''
                ORDER BY [DAS].[CASECODE] DESC,
                         [DAS].[SOURCE] DESC
                IF @MAXRESULT = 999999
                  BEGIN
                    SET @NewTotalCount = (SELECT COUNT(1) FROM @Tracks)
                  END
              END
            ELSE
              BEGIN
                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                                AND [DAS].[SOURCE] <> ''
                ORDER BY [DAS].[CASECODE],
                         [DAS].[SOURCE]
                IF @MAXRESULT = 999999
                  BEGIN
                    SET @NewTotalCount = (SELECT COUNT(1) FROM @Tracks)
                  END
              END
            SET @SortBy = NULL
          END
        ELSE IF @SortBy = 'EMAIL'
          BEGIN
            IF ISNULL(@SortDesc, 1) = 1
              BEGIN
                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                ORDER BY [DAS].[CASECODE] DESC,
                         [DAS].[EMAIL] DESC
              END
            ELSE
              BEGIN
                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                ORDER BY [DAS].[CASECODE],
                         [DAS].[EMAIL]
              END
            SET @SortBy = NULL
          END
        ELSE IF @SortBy = 'EMAILSENT'
          BEGIN
            IF ISNULL(@SortDesc, 1) = 1
              BEGIN
                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                ORDER BY [DAS].[CASECODE] DESC,
                         [DAS].[EMAILSENT] DESC
              END
            ELSE
              BEGIN
                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                ORDER BY [DAS].[CASECODE],
                         [DAS].[EMAILSENT]
              END
            SET @SortBy = NULL
          END
        ELSE IF @SortBy = 'FolderSetting'
          BEGIN
            IF ISNULL(@SortDesc, 1) = 1
              BEGIN
                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                ORDER BY [DAS].[CASECODE] DESC,
                         [DAS].[IsShared] DESC,
                         [DAS].[InDocFolder] DESC
              END
            ELSE
              BEGIN
                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                ORDER BY [DAS].[CASECODE],
                         [DAS].[IsShared],
                         [DAS].[InDocFolder]
              END
            SET @SortBy = NULL
          END
        ELSE IF @SortBy = 'InDocFolder'
          BEGIN
            IF ISNULL(@SortDesc, 1) = 1
              BEGIN
                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                ORDER BY [DAS].[CASECODE] DESC,
                         [DAS].[InDocFolder] DESC
              END
            ELSE
              BEGIN
                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                ORDER BY [DAS].[CASECODE],
                         [DAS].[InDocFolder]
              END
            SET @SortBy = NULL
          END
        ELSE IF @SortBy = 'IsShared'
          BEGIN
            IF ISNULL(@SortDesc, 1) = 1
              BEGIN
                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                ORDER BY [DAS].[CASECODE] DESC,
                         [DAS].[IsShared] DESC
              END
            ELSE
              BEGIN
                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                ORDER BY [DAS].[CASECODE],
                         [DAS].[IsShared]
              END
            SET @SortBy = NULL
          END
        ELSE IF @SortBy = 'NAME'
          BEGIN
            IF ISNULL(@SortDesc, 1) = 1
              BEGIN
                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                                AND [DAS].[NAME] <> ''
                ORDER BY [DAS].[CASECODE] DESC,
                         [DAS].[NAME] DESC
                IF @MAXRESULT = 999999
                  BEGIN
                    SET @NewTotalCount = (SELECT COUNT(1) FROM @Tracks)
                  END
              END
            ELSE
              BEGIN
                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                                AND [DAS].[NAME] <> ''
                ORDER BY [DAS].[CASECODE],
                         [DAS].[NAME]
                IF @MAXRESULT = 999999
                  BEGIN
                    SET @NewTotalCount = (SELECT COUNT(1) FROM @Tracks)
                  END
              END
            SET @SortBy = NULL
          END
        ELSE IF @SortBy = 'PROCESSTYPE'
          BEGIN
            IF ISNULL(@SortDesc, 1) = 1
              BEGIN
                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                                AND [DAS].[PROCESSTYPE] <> ''
                ORDER BY [DAS].[CASECODE] DESC,
                         [DAS].[PROCESSTYPE] DESC
                IF @MAXRESULT = 999999
                  BEGIN
                    SET @NewTotalCount = (SELECT COUNT(1) FROM @Tracks)
                  END
              END
            ELSE
              BEGIN
                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                                AND [DAS].[PROCESSTYPE] <> ''
                ORDER BY [DAS].[CASECODE],
                         [DAS].[PROCESSTYPE]
                IF @MAXRESULT = 999999
                  BEGIN
                    SET @NewTotalCount = (SELECT COUNT(1) FROM @Tracks)
                  END
              END
            SET @SortBy = NULL
          END
        ELSE IF @SortBy = 'SigningStatus'
          BEGIN
            IF ISNULL(@SortDesc, 1) = 1
              BEGIN
                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                ORDER BY [DAS].[CASECODE] DESC,
                         [DAS].[SigningStatus] DESC
                IF @MAXRESULT = 999999
                  BEGIN
                    SET @NewTotalCount = (SELECT COUNT(1) FROM @Tracks)
                  END
              END
            ELSE
              BEGIN
                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                ORDER BY [DAS].[CASECODE],
                         [DAS].[SigningStatus]
                IF @MAXRESULT = 999999
                  BEGIN
                    SET @NewTotalCount = (SELECT COUNT(1) FROM @Tracks)
                  END
              END
            SET @SortBy = NULL
          END
        ELSE IF @SortBy = 'TYPE'
          BEGIN
            IF ISNULL(@SortDesc, 1) = 1
              BEGIN
                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                                AND [DAS].[TYPE] <> ''
                ORDER BY [DAS].[CASECODE] DESC,
                         [DAS].[FileType] DESC
                IF @MAXRESULT = 999999
                  BEGIN
                    SET @NewTotalCount = (SELECT COUNT(1) FROM @Tracks)
                  END
              END
            ELSE
              BEGIN
                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                                AND [DAS].[TYPE] <> ''
                ORDER BY [DAS].[CASECODE],
                         [DAS].[FileType]
                IF @MAXRESULT = 999999
                  BEGIN
                    SET @NewTotalCount = (SELECT COUNT(1) FROM @Tracks)
                  END
              END
            SET @SortBy = NULL
          END
      END
    ELSE IF ISNULL(@ActionID, 0) = 0 AND ISNULL(@DocFolderID, 0) = 0
      BEGIN
        -- When we have IManage documents we have no choice but to rely on the SORTER,
        INSERT
          INTO @Tracks
              ([TrackReference],
               [RESID],
               [SORTER])
        SELECT TOP (@MAXRESULT)
               [DAS].[TrackReference],
               0,
               CASE WHEN @SortBy IS NULL
                         THEN ''
                    WHEN @SortBy = 'ActionType'
                         THEN CONVERT(VARCHAR(50), [DAS].[ActionTypeDescription])
                    WHEN @SortBy = 'ACTIONID'
                         THEN CONVERT(VARCHAR(10), 1000000000 + [DAS].[ActionID])
                    WHEN @SortBy = 'ATTACHMENTS'
                         THEN [DAS].[ATTACHMENTS]
                    WHEN @SortBy = 'DiaryDate'
                         THEN CONVERT(VARCHAR, [DAS].[DiaryDate], 121)
                    WHEN @SortBy = 'Document'
                         THEN CONVERT(VARCHAR(50), [DAS].[Document])
                    WHEN @SortBy = 'DocumentClass'
                         THEN CONVERT(VARCHAR(50), [DAS].[DocumentClassDescription])
                    WHEN @SortBy = 'DocumentDate'
                         THEN CONVERT(VARCHAR, [DAS].[DocumentDate], 121)
                    WHEN @SortBy = 'DocumentSource'
                         THEN CONVERT(VARCHAR(50), [DAS].[SOURCE])
                    WHEN @SortBy = 'EMAIL'
                         THEN [DAS].[EMAIL]
                    WHEN @SortBy = 'EMAILADDRESS'
                         THEN CONVERT(VARCHAR(50), REPLACE([DAS].[EMAILADDRESS], '"', ''))
                    WHEN @SortBy = 'EMAILSENT'
                         THEN CONVERT(VARCHAR, [DAS].[EMAILSENT], 121)
                    WHEN @SortBy = 'FolderSetting'
                         THEN CONVERT(VARCHAR(1), [DAS].[IsShared]) + CONVERT(VARCHAR(1), [DAS].[InDocFolder])
                    WHEN @SortBy = 'InDocFolder'
                         THEN CONVERT(VARCHAR(1), [DAS].[InDocFolder])
                    WHEN @SortBy = 'IsShared'
                         THEN CONVERT(VARCHAR(1), [DAS].[IsShared])
                    WHEN @SortBy = 'NAME'
                         THEN [DAS].[NAME]
                    WHEN @SortBy = 'PROCESSTYPE'
                         THEN [DAS].[PROCESSTYPE]
                    WHEN @SortBy = 'SigningStatus'
                         THEN [DAS].[SigningStatus]
                    WHEN @SortBy = 'TYPE'
                         THEN [DAS].[TYPE]
                    ELSE '' END
          FROM @MatterSel [MAT]
               INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                       ON [DAS].[CASECODE] = [MAT].[CASECODE]
      END
    ELSE IF ISNULL(@ActionID, 0) = 0
      BEGIN
        -- When we are searching within a Doc Folder we can assume that there won't be many thousands of documents,
        -- so the SORTER functionality should be more than sufficient to sort effectively
        INSERT
          INTO @Tracks
              ([TrackReference],
               [RESID],
               [SORTER])
        SELECT TOP (@MAXRESULT)
               [DFD].[TrackReference],
               0,
               CASE WHEN @SortBy IS NULL
                         THEN ''
                    WHEN @SortBy = 'ActionType'
                         THEN CONVERT(VARCHAR(50), [DAS].[ActionTypeDescription])
                    WHEN @SortBy = 'ACTIONID'
                         THEN CONVERT(VARCHAR(10), 1000000000 + [DAS].[ActionID])
                    WHEN @SortBy = 'ATTACHMENTS'
                         THEN [DAS].[ATTACHMENTS]
                    WHEN @SortBy = 'DiaryDate'
                         THEN CONVERT(VARCHAR, [DAS].[DiaryDate], 121)
                    WHEN @SortBy = 'Document'
                         THEN CONVERT(VARCHAR(50), [DAS].[Document])
                    WHEN @SortBy = 'DocumentClass'
                         THEN CONVERT(VARCHAR(50), [DAS].[DocumentClassDescription])
                    WHEN @SortBy = 'DocumentDate'
                         THEN CONVERT(VARCHAR, [DAS].[DocumentDate], 121)
                    WHEN @SortBy = 'DocumentSource'
                         THEN CONVERT(VARCHAR(50), [DAS].[SOURCE])
                    WHEN @SortBy = 'EMAIL'
                         THEN [DAS].[EMAIL]
                    WHEN @SortBy = 'EMAILADDRESS'
                         THEN CONVERT(VARCHAR(50), REPLACE([DAS].[EMAILADDRESS], '"', ''))
                    WHEN @SortBy = 'EMAILSENT'
                         THEN CONVERT(VARCHAR, [DAS].[EMAILSENT], 121)
                    WHEN @SortBy = 'FolderSetting'
                         THEN CONVERT(VARCHAR(1), [DAS].[IsShared]) + CONVERT(VARCHAR(1), [DAS].[InDocFolder])
                    WHEN @SortBy = 'InDocFolder'
                         THEN CONVERT(VARCHAR(1), [DAS].[InDocFolder])
                    WHEN @SortBy = 'IsShared'
                         THEN CONVERT(VARCHAR(1), [DAS].[IsShared])
                    WHEN @SortBy = 'NAME'
                         THEN [DAS].[NAME]
                    WHEN @SortBy = 'PROCESSTYPE'
                         THEN [DAS].[PROCESSTYPE]
                    WHEN @SortBy = 'SigningStatus'
                         THEN [DAS].[SigningStatus]
                    WHEN @SortBy = 'TYPE'
                         THEN [DAS].[TYPE]
                    ELSE '' END
          FROM [dbo].[DocFolderDocuments] [DFD]
               INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                       ON [DAS].[TrackReference] = [DFD].[TrackReference]
         WHERE [DFD].[DocFolderID] = @DocFolderID
      END
    ELSE
      BEGIN
        -- When we are searching within a single Action we can assume that there won't be many thousands of documents,
        -- so the SORTER functionality should be more than sufficient to sort effectively
        INSERT
          INTO @Tracks
              ([TrackReference],
               [RESID],
               [SORTER])
        SELECT TOP (@MAXRESULT)
               [DAT].[TrackReference],
               0,
               CASE WHEN @SortBy IS NULL
                         THEN ''
                    WHEN @SortBy = 'ActionType'
                         THEN CONVERT(VARCHAR(50), [DAS].[ActionTypeDescription])
                    WHEN @SortBy = 'ACTIONID'
                         THEN CONVERT(VARCHAR(10), 1000000000 + [DAS].[ActionID])
                    WHEN @SortBy = 'ATTACHMENTS'
                         THEN [DAS].[ATTACHMENTS]
                    WHEN @SortBy = 'DiaryDate'
                         THEN CONVERT(VARCHAR, [DAS].[DiaryDate], 121)
                    WHEN @SortBy = 'Document'
                         THEN CONVERT(VARCHAR(50), [DAS].[Document])
                    WHEN @SortBy = 'DocumentClass'
                         THEN CONVERT(VARCHAR(50), [DAS].[DocumentClassDescription])
                    WHEN @SortBy = 'DocumentDate'
                         THEN CONVERT(VARCHAR, [DAS].[DocumentDate], 121)
                    WHEN @SortBy = 'DocumentSource'
                         THEN CONVERT(VARCHAR(50), [DAS].[SOURCE])
                    WHEN @SortBy = 'EMAIL'
                         THEN [DAS].[EMAIL]
                    WHEN @SortBy = 'EMAILADDRESS'
                         THEN CONVERT(VARCHAR(50), REPLACE([DAS].[EMAILADDRESS], '"', ''))
                    WHEN @SortBy = 'EMAILSENT'
                         THEN CONVERT(VARCHAR, [DAS].[EMAILSENT], 121)
                    WHEN @SortBy = 'FolderSetting'
                         THEN CONVERT(VARCHAR(1), [DAS].[IsShared]) + CONVERT(VARCHAR(1), [DAS].[InDocFolder])
                    WHEN @SortBy = 'InDocFolder'
                         THEN CONVERT(VARCHAR(1), [DAS].[InDocFolder])
                    WHEN @SortBy = 'IsShared'
                         THEN CONVERT(VARCHAR(1), [DAS].[IsShared])
                    WHEN @SortBy = 'NAME'
                         THEN [DAS].[NAME]
                    WHEN @SortBy = 'PROCESSTYPE'
                         THEN [DAS].[PROCESSTYPE]
                    WHEN @SortBy = 'SigningStatus'
                         THEN [DAS].[SigningStatus]
                    WHEN @SortBy = 'TYPE'
                         THEN [DAS].[TYPE]
                    ELSE '' END
          FROM [dbo].[DiaryAttachments] [DAT]
               INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                       ON [DAS].[TrackReference] = [DAT].[TrackReference]
         WHERE [DAT].[DiaryID] = @ActionID
      END
  END
ELSE
  BEGIN
    --SELECT '002', GETDATE()

    SELECT @NewTotalCount = COUNT(1)
      FROM @TrackND [TRK]

    IF ISNULL(@ActionID, 0) = 0 AND ISNULL(@DocFolderID, 0) = 0 AND @HASIMDOCS = 0
      BEGIN
        -- When we are searching within a Case there can be tens of thousands of matching documents. To get the
        -- best performance we ensure that the system uses an INDEX when sorting. The only field for which this
        -- is not possible is the EMAILADDRESS
        IF @SortBy IS NULL
          BEGIN
            --SELECT '00201', GETDATE()

            INSERT
              INTO @Tracks
                  ([TrackReference],
                   [RESID],
                   [SORTER])
            SELECT TOP (@MAXRESULT)
                   [DAS].[TrackReference],
                   0,
                   ''
              FROM @MatterSel [MAT]
                   INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                              INNER JOIN @TrackND [TRK]
                                      ON [TRK].[TrackReference] = [DAS].[TrackReference]
                           ON [DAS].[CASECODE] = [MAT].[CASECODE]
            OPTION (RECOMPILE)
          END
        ELSE IF @SortBy IN ('EMAILADDRESS')
          BEGIN
            --SELECT '00202', GETDATE()

            INSERT
              INTO @Tracks
                  ([TrackReference],
                   [RESID],
                   [SORTER])
            SELECT TOP (@MAXRESULT)
                   [DAS].[TrackReference],
                   0,
                   CASE WHEN @SortBy = 'EMAILADDRESS'
                             THEN CONVERT(VARCHAR(50), REPLACE([DAS].[EMAILADDRESS], '"', ''))
                        ELSE '' END
              FROM @MatterSel [MAT]
                   INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                              INNER JOIN @TrackND [TRK]
                                      ON [TRK].[TrackReference] = [DAS].[TrackReference]
                           ON [DAS].[CASECODE] = [MAT].[CASECODE]
            OPTION (RECOMPILE)
          END
        ELSE IF @SortBy = 'ACTIONID'
          BEGIN
            IF ISNULL(@SortDesc, 1) = 1
              BEGIN
                --SELECT '00203', GETDATE()

                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                    INNER JOIN @TrackND [TRK]
                                            ON [TRK].[TrackReference] = [DAS].[TrackReference]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                ORDER BY [DAS].[CASECODE] DESC,
                         [DAS].[ACTIONID] DESC
                  OPTION (RECOMPILE)
              END
            ELSE
              BEGIN
                --SELECT '00204', GETDATE()

                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                    INNER JOIN @TrackND [TRK]
                                            ON [TRK].[TrackReference] = [DAS].[TrackReference]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                ORDER BY [DAS].[CASECODE],
                         [DAS].[ACTIONID]
                  OPTION (RECOMPILE)
              END
            SET @SortBy = NULL
          END
        ELSE IF @SortBy = 'ActionType'
          BEGIN
            IF ISNULL(@SortDesc, 1) = 1
              BEGIN
                --SELECT '00205', GETDATE()

                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                    INNER JOIN @TrackND [TRK]
                                            ON [TRK].[TrackReference] = [DAS].[TrackReference]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                ORDER BY [DAS].[CASECODE] DESC,
                         [DAS].[ActionTypeDescription] DESC
                  OPTION (RECOMPILE)
              END
            ELSE
              BEGIN
                --SELECT '00206', GETDATE()

                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                    INNER JOIN @TrackND [TRK]
                                            ON [TRK].[TrackReference] = [DAS].[TrackReference]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                ORDER BY [DAS].[CASECODE],
                         [DAS].[ActionTypeDescription]
                  OPTION (RECOMPILE)
              END
            SET @SortBy = NULL
          END
        ELSE IF @SortBy = 'ATTACHMENTS'
          BEGIN
            IF ISNULL(@SortDesc, 1) = 1
              BEGIN
                --SELECT '00207', GETDATE()

                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                    INNER JOIN @TrackND [TRK]
                                            ON [TRK].[TrackReference] = [DAS].[TrackReference]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                ORDER BY [DAS].[CASECODE] DESC,
                         [DAS].[ATTACHMENTS] DESC
                  OPTION (RECOMPILE)
              END
            ELSE
              BEGIN
                --SELECT '00208', GETDATE()

                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                    INNER JOIN @TrackND [TRK]
                                            ON [TRK].[TrackReference] = [DAS].[TrackReference]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                ORDER BY [DAS].[CASECODE],
                         [DAS].[ATTACHMENTS]
                  OPTION (RECOMPILE)
              END
            SET @SortBy = NULL
          END
        ELSE IF @SortBy = 'DiaryDate'
          BEGIN
            IF ISNULL(@SortDesc, 1) = 1
              BEGIN
                --SELECT '00209', GETDATE()

                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                    WITH (NOLOCK)
                                    INNER JOIN @TrackND [TRK]
                                            ON [TRK].[TrackReference] = [DAS].[TrackReference]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                ORDER BY [DAS].[CASECODE] DESC,
                         [DAS].[DiaryDate] DESC
                  OPTION (RECOMPILE)
              END
            ELSE
              BEGIN
                --SELECT '00210', GETDATE()

                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                    INNER JOIN @TrackND [TRK]
                                            ON [TRK].[TrackReference] = [DAS].[TrackReference]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                ORDER BY [DAS].[CASECODE],
                         [DAS].[DiaryDate]
                  OPTION (RECOMPILE)
              END
            SET @SortBy = NULL
          END
        ELSE IF @SortBy = 'Document'
          BEGIN
            IF ISNULL(@SortDesc, 1) = 1
              BEGIN
                --SELECT '00211', GETDATE()

                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                    INNER JOIN @TrackND [TRK]
                                            ON [TRK].[TrackReference] = [DAS].[TrackReference]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                ORDER BY [DAS].[CASECODE] DESC,
                         [DAS].[Document] DESC
                  OPTION (RECOMPILE)
              END
            ELSE
              BEGIN
                --SELECT '00212', GETDATE()

                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                    INNER JOIN @TrackND [TRK]
                                            ON [TRK].[TrackReference] = [DAS].[TrackReference]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                ORDER BY [DAS].[CASECODE],
                         [DAS].[Document]
                  OPTION (RECOMPILE)
              END
            SET @SortBy = NULL
          END
        ELSE IF @SortBy = 'DocumentClass'
          BEGIN
            IF ISNULL(@SortDesc, 1) = 1
              BEGIN
                --SELECT '00211', GETDATE()

                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                    INNER JOIN @TrackND [TRK]
                                            ON [TRK].[TrackReference] = [DAS].[TrackReference]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                ORDER BY [DAS].[CASECODE] DESC,
                         [DAS].[DocumentClassDescription] DESC
                  OPTION (RECOMPILE)
              END
            ELSE
              BEGIN
                --SELECT '00212', GETDATE()

                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                    INNER JOIN @TrackND [TRK]
                                            ON [TRK].[TrackReference] = [DAS].[TrackReference]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                ORDER BY [DAS].[CASECODE],
                         [DAS].[DocumentClassDescription]
                  OPTION (RECOMPILE)
              END
            SET @SortBy = NULL
          END
        ELSE IF @SortBy = 'DocumentDate'
          BEGIN
            IF ISNULL(@SortDesc, 1) = 1
              BEGIN
                --SELECT '00213', GETDATE()

                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                    INNER JOIN @TrackND [TRK]
                                            ON [TRK].[TrackReference] = [DAS].[TrackReference]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                ORDER BY [DAS].[CASECODE] DESC,
                         [DAS].[DocumentDate] DESC
                  OPTION (RECOMPILE)
              END
            ELSE
              BEGIN
                --SELECT '00214', GETDATE()

                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                    INNER JOIN @TrackND [TRK]
                                            ON [TRK].[TrackReference] = [DAS].[TrackReference]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                ORDER BY [DAS].[CASECODE],
                         [DAS].[DocumentDate]
                  OPTION (RECOMPILE)
              END
            SET @SortBy = NULL
          END
        ELSE IF @SortBy = 'DocumentSource'
          BEGIN
            IF ISNULL(@SortDesc, 1) = 1
              BEGIN
                --SELECT '00215', GETDATE()

                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                    INNER JOIN @TrackND [TRK]
                                            ON [TRK].[TrackReference] = [DAS].[TrackReference]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                ORDER BY [DAS].[CASECODE] DESC,
                         [DAS].[SOURCE] DESC
                  OPTION (RECOMPILE)
              END
            ELSE
              BEGIN
                --SELECT '00216', GETDATE()

                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                    INNER JOIN @TrackND [TRK]
                                            ON [TRK].[TrackReference] = [DAS].[TrackReference]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                ORDER BY [DAS].[CASECODE],
                         [DAS].[SOURCE]
                  OPTION (RECOMPILE)
              END
            SET @SortBy = NULL
          END
        ELSE IF @SortBy = 'EMAIL'
          BEGIN
            IF ISNULL(@SortDesc, 1) = 1
              BEGIN
                --SELECT '00217', GETDATE()

                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                    INNER JOIN @TrackND [TRK]
                                            ON [TRK].[TrackReference] = [DAS].[TrackReference]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                ORDER BY [DAS].[CASECODE] DESC,
                         [DAS].[EMAIL] DESC
                  OPTION (RECOMPILE)
              END
            ELSE
              BEGIN
                --SELECT '00218', GETDATE()

                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                    INNER JOIN @TrackND [TRK]
                                            ON [TRK].[TrackReference] = [DAS].[TrackReference]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                ORDER BY [DAS].[CASECODE],
                         [DAS].[EMAIL]
                  OPTION (RECOMPILE)
              END
            SET @SortBy = NULL
          END
        ELSE IF @SortBy = 'EMAILSENT'
          BEGIN
            IF ISNULL(@SortDesc, 1) = 1
              BEGIN
                --SELECT '00219', GETDATE()

                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                    INNER JOIN @TrackND [TRK]
                                            ON [TRK].[TrackReference] = [DAS].[TrackReference]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                ORDER BY [DAS].[CASECODE] DESC,
                         [DAS].[EMAILSENT] DESC
                  OPTION (RECOMPILE)
              END
            ELSE
              BEGIN
                --SELECT '00220', GETDATE()

                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                    INNER JOIN @TrackND [TRK]
                                            ON [TRK].[TrackReference] = [DAS].[TrackReference]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                ORDER BY [DAS].[CASECODE],
                         [DAS].[EMAILSENT]
                  OPTION (RECOMPILE)
              END
            SET @SortBy = NULL
          END
        ELSE IF @SortBy = 'FolderSetting'
          BEGIN
            IF ISNULL(@SortDesc, 1) = 1
              BEGIN
                --SELECT '00221', GETDATE()

                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                    INNER JOIN @TrackND [TRK]
                                            ON [TRK].[TrackReference] = [DAS].[TrackReference]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                ORDER BY [DAS].[CASECODE] DESC,
                         [DAS].[IsShared] DESC,
                         [DAS].[InDocFolder] DESC
                  OPTION (RECOMPILE)
              END
            ELSE
              BEGIN
                --SELECT '00222', GETDATE()

                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                    INNER JOIN @TrackND [TRK]
                                            ON [TRK].[TrackReference] = [DAS].[TrackReference]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                ORDER BY [DAS].[CASECODE],
                         [DAS].[IsShared],
                         [DAS].[InDocFolder]
                  OPTION (RECOMPILE)
              END
            SET @SortBy = NULL
          END
        ELSE IF @SortBy = 'InDocFolder'
          BEGIN
            IF ISNULL(@SortDesc, 1) = 1
              BEGIN
                --SELECT '00223', GETDATE()

                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                    INNER JOIN @TrackND [TRK]
                                            ON [TRK].[TrackReference] = [DAS].[TrackReference]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                ORDER BY [DAS].[CASECODE] DESC,
                         [DAS].[InDocFolder] DESC
                  OPTION (RECOMPILE)
              END
            ELSE
              BEGIN
                --SELECT '00224', GETDATE()

                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                    INNER JOIN @TrackND [TRK]
                                            ON [TRK].[TrackReference] = [DAS].[TrackReference]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                ORDER BY [DAS].[CASECODE],
                         [DAS].[InDocFolder]
                  OPTION (RECOMPILE)
              END
            SET @SortBy = NULL
          END
        ELSE IF @SortBy = 'IsShared'
          BEGIN
            IF ISNULL(@SortDesc, 1) = 1
              BEGIN
                --SELECT '00225', GETDATE()

                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                    INNER JOIN @TrackND [TRK]
                                            ON [TRK].[TrackReference] = [DAS].[TrackReference]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                ORDER BY [DAS].[CASECODE] DESC,
                         [DAS].[IsShared] DESC
                  OPTION (RECOMPILE)
              END
            ELSE
              BEGIN
                --SELECT '00226', GETDATE()

                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                    INNER JOIN @TrackND [TRK]
                                            ON [TRK].[TrackReference] = [DAS].[TrackReference]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                ORDER BY [DAS].[CASECODE],
                         [DAS].[IsShared]
                  OPTION (RECOMPILE)
              END
            SET @SortBy = NULL
          END
        ELSE IF @SortBy = 'NAME'
          BEGIN
            IF ISNULL(@SortDesc, 1) = 1
              BEGIN
                --SELECT '00227', GETDATE()

                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                    INNER JOIN @TrackND [TRK]
                                            ON [TRK].[TrackReference] = [DAS].[TrackReference]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                                AND [DAS].[NAME] <> ''
                ORDER BY [DAS].[CASECODE] DESC,
                         [DAS].[NAME] DESC
                  OPTION (RECOMPILE)
              END
            ELSE
              BEGIN
                --SELECT '00228', GETDATE()

                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                    INNER JOIN @TrackND [TRK]
                                            ON [TRK].[TrackReference] = [DAS].[TrackReference]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                                AND [DAS].[NAME] <> ''
                ORDER BY [DAS].[CASECODE],
                         [DAS].[NAME]
                  OPTION (RECOMPILE)
              END
            SET @SortBy = NULL
          END
        ELSE IF @SortBy = 'PROCESSTYPE'
          BEGIN
            IF ISNULL(@SortDesc, 1) = 1
              BEGIN
                --SELECT '00229', GETDATE()

                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                    INNER JOIN @TrackND [TRK]
                                            ON [TRK].[TrackReference] = [DAS].[TrackReference]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                                AND [DAS].[PROCESSTYPE] <> ''
                ORDER BY [DAS].[CASECODE] DESC,
                         [DAS].[PROCESSTYPE] DESC
                  OPTION (RECOMPILE)
              END
            ELSE
              BEGIN
                --SELECT '00230', GETDATE()

                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                    INNER JOIN @TrackND [TRK]
                                            ON [TRK].[TrackReference] = [DAS].[TrackReference]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                                AND [DAS].[PROCESSTYPE] <> ''
                ORDER BY [DAS].[CASECODE],
                         [DAS].[PROCESSTYPE]
                  OPTION (RECOMPILE)
              END
            SET @SortBy = NULL
          END
        ELSE IF @SortBy = 'SigningStatus'
          BEGIN
            IF ISNULL(@SortDesc, 1) = 1
              BEGIN
                --SELECT '00215', GETDATE()

                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                    INNER JOIN @TrackND [TRK]
                                            ON [TRK].[TrackReference] = [DAS].[TrackReference]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                ORDER BY [DAS].[CASECODE] DESC,
                         [DAS].[SigningStatus] DESC,
                         [DAS].[DocumentDate] DESC
                  OPTION (RECOMPILE)
              END
            ELSE
              BEGIN
                --SELECT '00216', GETDATE()

                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                    INNER JOIN @TrackND [TRK]
                                            ON [TRK].[TrackReference] = [DAS].[TrackReference]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                ORDER BY [DAS].[CASECODE],
                         [DAS].[SigningStatus],
                         [DAS].[DocumentDate]
                  OPTION (RECOMPILE)
              END
            SET @SortBy = NULL
          END
        ELSE IF @SortBy = 'TYPE'
          BEGIN
            IF ISNULL(@SortDesc, 1) = 1
              BEGIN
                --SELECT '00231', GETDATE()

                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                    INNER JOIN @TrackND [TRK]
                                            ON [TRK].[TrackReference] = [DAS].[TrackReference]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                                AND [DAS].[TYPE] <> ''
                ORDER BY [DAS].[CASECODE] DESC,
                         [DAS].[TYPE] DESC
                  OPTION (RECOMPILE)
              END
            ELSE
              BEGIN
                --SELECT '00232', GETDATE()

                INSERT
                  INTO @Tracks
                      ([TrackReference],
                       [RESID],
                       [SORTER])
                  SELECT TOP (@MAXRESULT)
                         [DAS].[TrackReference],
                         0,
                         ''
                    FROM @MatterSel [MAT]
                         INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                                    INNER JOIN @TrackND [TRK]
                                            ON [TRK].[TrackReference] = [DAS].[TrackReference]
                                 ON [DAS].[CASECODE] = [MAT].[CASECODE]
                                AND [DAS].[TYPE] <> ''
                ORDER BY [DAS].[CASECODE],
                         [DAS].[TYPE]
                  OPTION (RECOMPILE)
              END
            SET @SortBy = NULL
          END
      END
    ELSE IF ISNULL(@ActionID, 0) = 0 AND ISNULL(@DocFolderID, 0) = 0
      BEGIN
        -- When we have IManage documents we have no choice but to rely on the SORTER,
        INSERT
          INTO @Tracks
              ([TrackReference],
               [RESID],
               [SORTER])
        SELECT TOP (@MAXRESULT)
               [DAS].[TrackReference],
               0,
               CASE WHEN @SortBy IS NULL
                         THEN ''
                    WHEN @SortBy = 'ActionType'
                         THEN CONVERT(VARCHAR(50), [DAS].[ActionTypeDescription])
                    WHEN @SortBy = 'ACTIONID'
                         THEN CONVERT(VARCHAR(10), 1000000000 + [DAS].[ActionID])
                    WHEN @SortBy = 'ATTACHMENTS'
                         THEN [DAS].[ATTACHMENTS]
                    WHEN @SortBy = 'DiaryDate'
                         THEN CONVERT(VARCHAR, [DAS].[DiaryDate], 121)
                    WHEN @SortBy = 'Document'
                         THEN CONVERT(VARCHAR(50), [DAS].[Document])
                    WHEN @SortBy = 'DocumentClass'
                         THEN CONVERT(VARCHAR(50), [DAS].[DocumentClassDescription])
                    WHEN @SortBy = 'DocumentDate'
                         THEN CONVERT(VARCHAR, [DAS].[DocumentDate], 121)
                    WHEN @SortBy = 'DocumentSource'
                         THEN CONVERT(VARCHAR(50), [DAS].[SOURCE])
                    WHEN @SortBy = 'EMAIL'
                         THEN [DAS].[EMAIL]
                    WHEN @SortBy = 'EMAILADDRESS'
                         THEN CONVERT(VARCHAR(50), REPLACE([DAS].[EMAILADDRESS], '"', ''))
                    WHEN @SortBy = 'EMAILSENT'
                         THEN CONVERT(VARCHAR, [DAS].[EMAILSENT], 121)
                    WHEN @SortBy = 'FolderSetting'
                         THEN CONVERT(VARCHAR(1), [DAS].[IsShared]) + CONVERT(VARCHAR(1), [DAS].[InDocFolder])
                    WHEN @SortBy = 'InDocFolder'
                         THEN CONVERT(VARCHAR(1), [DAS].[InDocFolder])
                    WHEN @SortBy = 'IsShared'
                         THEN CONVERT(VARCHAR(1), [DAS].[IsShared])
                    WHEN @SortBy = 'NAME'
                         THEN [DAS].[NAME]
                    WHEN @SortBy = 'PROCESSTYPE'
                         THEN [DAS].[PROCESSTYPE]
                    WHEN @SortBy = 'SigningStatus'
                         THEN [DAS].[SigningStatus]
                    WHEN @SortBy = 'TYPE'
                         THEN [DAS].[TYPE]
                    ELSE '' END
          FROM @MatterSel [MAT]
               INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                          INNER JOIN @TrackND [TRK]
                                  ON [TRK].[TrackReference] = [DAS].[TrackReference]
                       ON [DAS].[CASECODE] = [MAT].[CASECODE]
      END
    ELSE IF ISNULL(@ActionID, 0) = 0
      BEGIN
        -- When we are searching within a Doc Folder we can assume that there won't be many thousands of documents,
        -- so the SORTER functionality should be more than sufficient to sort effectively
        INSERT
          INTO @Tracks
              ([TrackReference],
               [RESID],
               [SORTER])
        SELECT TOP (@MAXRESULT)
               [DFD].[TrackReference],
               0,
               CASE WHEN @SortBy IS NULL
                         THEN ''
                    WHEN @SortBy = 'ActionType'
                         THEN CONVERT(VARCHAR(50), [DAS].[ActionTypeDescription])
                    WHEN @SortBy = 'ACTIONID'
                         THEN CONVERT(VARCHAR(10), 1000000000 + [DAS].[ActionID])
                    WHEN @SortBy = 'ATTACHMENTS'
                         THEN [DAS].[ATTACHMENTS]
                    WHEN @SortBy = 'DiaryDate'
                         THEN CONVERT(VARCHAR, [DAS].[DiaryDate], 121)
                    WHEN @SortBy = 'Document'
                         THEN CONVERT(VARCHAR(50), [DAS].[Document])
                    WHEN @SortBy = 'DocumentClass'
                         THEN CONVERT(VARCHAR(50), [DAS].[DocumentClassDescription])
                    WHEN @SortBy = 'DocumentDate'
                         THEN CONVERT(VARCHAR, [DAS].[DocumentDate], 121)
                    WHEN @SortBy = 'DocumentSource'
                         THEN CONVERT(VARCHAR(50), [DAS].[SOURCE])
                    WHEN @SortBy = 'EMAIL'
                         THEN [DAS].[EMAIL]
                    WHEN @SortBy = 'EMAILADDRESS'
                         THEN CONVERT(VARCHAR(50), REPLACE([DAS].[EMAILADDRESS], '"', ''))
                    WHEN @SortBy = 'EMAILSENT'
                         THEN CONVERT(VARCHAR, [DAS].[EMAILSENT], 121)
                    WHEN @SortBy = 'FolderSetting'
                         THEN CONVERT(VARCHAR(1), [DAS].[IsShared]) + CONVERT(VARCHAR(1), [DAS].[InDocFolder])
                    WHEN @SortBy = 'InDocFolder'
                         THEN CONVERT(VARCHAR(1), [DAS].[InDocFolder])
                    WHEN @SortBy = 'IsShared'
                         THEN CONVERT(VARCHAR(1), [DAS].[IsShared])
                    WHEN @SortBy = 'NAME'
                         THEN [DAS].[NAME]
                    WHEN @SortBy = 'PROCESSTYPE'
                         THEN [DAS].[PROCESSTYPE]
                    WHEN @SortBy = 'SigningStatus'
                         THEN [DAS].[SigningStatus]
                    WHEN @SortBy = 'TYPE'
                         THEN [DAS].[TYPE]
                    ELSE '' END
          FROM [dbo].[DocFolderDocuments] [DFD]
               INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                          INNER JOIN @TrackND [TRK]
                                  ON [TRK].[TrackReference] = [DAS].[TrackReference]
                       ON [DAS].[TrackReference] = [DFD].[TrackReference]
         WHERE [DFD].[DocFolderID] = @DocFolderID
      END
    ELSE
      BEGIN
        -- When we are searching within a single Action we can assume that there won't be many thousands of documents,
        -- so the SORTER functionality should be more than sufficient to sort effectively
        INSERT
          INTO @Tracks
              ([TrackReference],
               [RESID],
               [SORTER])
        SELECT TOP (@MAXRESULT)
               [DAT].[TrackReference],
               0,
               CASE WHEN @SortBy IS NULL
                         THEN ''
                    WHEN @SortBy = 'ActionType'
                         THEN CONVERT(VARCHAR(50), [DAS].[ActionTypeDescription])
                    WHEN @SortBy = 'ACTIONID'
                         THEN CONVERT(VARCHAR(10), 1000000000 + [DAS].[ActionID])
                    WHEN @SortBy = 'ATTACHMENTS'
                         THEN [DAS].[ATTACHMENTS]
                    WHEN @SortBy = 'DiaryDate'
                         THEN CONVERT(VARCHAR, [DAS].[DiaryDate], 121)
                    WHEN @SortBy = 'Document'
                         THEN CONVERT(VARCHAR(50), [DAS].[Document])
                    WHEN @SortBy = 'DocumentClass'
                         THEN CONVERT(VARCHAR(50), [DAS].[DocumentClassDescription])
                    WHEN @SortBy = 'DocumentDate'
                         THEN CONVERT(VARCHAR, [DAS].[DocumentDate], 121)
                    WHEN @SortBy = 'DocumentSource'
                         THEN CONVERT(VARCHAR(50), [DAS].[SOURCE])
                    WHEN @SortBy = 'EMAIL'
                         THEN [DAS].[EMAIL]
                    WHEN @SortBy = 'EMAILADDRESS'
                         THEN CONVERT(VARCHAR(50), REPLACE([DAS].[EMAILADDRESS], '"', ''))
                    WHEN @SortBy = 'EMAILSENT'
                         THEN CONVERT(VARCHAR, [DAS].[EMAILSENT], 121)
                    WHEN @SortBy = 'FolderSetting'
                         THEN CONVERT(VARCHAR(1), [DAS].[IsShared]) + CONVERT(VARCHAR(1), [DAS].[InDocFolder])
                    WHEN @SortBy = 'InDocFolder'
                         THEN CONVERT(VARCHAR(1), [DAS].[InDocFolder])
                    WHEN @SortBy = 'IsShared'
                         THEN CONVERT(VARCHAR(1), [DAS].[IsShared])
                    WHEN @SortBy = 'NAME'
                         THEN [DAS].[NAME]
                    WHEN @SortBy = 'PROCESSTYPE'
                         THEN [DAS].[PROCESSTYPE]
                    WHEN @SortBy = 'SigningStatus'
                         THEN [DAS].[SigningStatus]
                    WHEN @SortBy = 'TYPE'
                         THEN [DAS].[TYPE]
                    ELSE '' END
          FROM [dbo].[DiaryAttachments] [DAT]
               INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                          INNER JOIN @TrackND [TRK]
                                  ON [TRK].[TrackReference] = [DAS].[TrackReference]
                       ON [DAS].[TrackReference] = [DAT].[TrackReference]
         WHERE [DAT].[DiaryID] = @ActionID
      END
  END

--SELECT 'New Total Count', @NewTotalCount

--SELECT '003', GETDATE()

DECLARE @MinValue    INT
DECLARE @MaxValue    INT
DECLARE @Tracks2     TrackRefsTable
DECLARE @Tracks3     TrackRefsTable
DECLARE @TotalResult INT

IF (RTRIM(ISNULL(@SortBy, '')) = '') AND (@HASIMDOCS = 0)
  BEGIN
    IF ISNULL(@PageNumber, 0) = 0 OR ISNULL(@PageSize, 0) = 0
      BEGIN
        BEGIN
          --SELECT @TotalResult = COUNT(1) FROM @Tracks
          SET @TotalResult = @NewTotalCount
          EXEC [dbo].[KAAS_WD_GetDocumentsIMResult] @Tracks, @res
        END
      END
    ELSE
      BEGIN
        DECLARE @PageOffset INT

        IF @Filtered = 0
          BEGIN
            SET @PageOffset = 0
          END
        ELSE
          BEGIN
            SELECT TOP 1
                   @PageOffset = [TRK].[id]
              FROM @Tracks [TRK]

            SET @PageOffset = ISNULL(@PageOffset, 1) - 1
          END

        --SELECT 'Page Offset:', @PageOffset

        SET @MinValue = @PageOffset + ((@PageNumber - 1) * @PageSize)
        SET @MaxValue = @PageOffset + (@PageNumber * @PageSize)

        --SELECT @TotalResult = COUNT(1) FROM @Tracks
        SET @TotalResult = @NewTotalCount

        INSERT
          INTO @Tracks2
              ([RESID],
               [TrackReference],
               [SORTER])
        SELECT [TRK].[RESID],
               [TRK].[TrackReference],
               [TRK].[SORTER]
          FROM @Tracks [TRK]
         WHERE [TRK].[id] > @MinValue
           AND [TRK].[id] <= @MaxValue

        EXEC [dbo].[KAAS_WD_GetDocumentsIMResult] @Tracks2, @res
      END
  END
ELSE IF ISNULL(@SortDesc, 0) = 1
  BEGIN
    INSERT
      INTO @Tracks2
          ([RESID],
           [TrackReference],
           [SORTER])
      SELECT [TRK].[RESID],
             [TRK].[TrackReference],
             ''
        FROM @Tracks [TRK]
       WHERE [TRK].[SORTER] <> ''
    ORDER BY [TRK].[SORTER] DESC,
             [TRK].[id] DESC

    IF ISNULL(@PageNumber, 0) = 0 OR ISNULL(@PageSize, 0) = 0
      BEGIN
        IF @HASIMDOCS = 1
          BEGIN
            DELETE [TRK2]
              FROM @Tracks2 [TRK2]
             WHERE [TRK2].[TrackReference] IN (  SELECT [TRK2DUP].[TrackReference]
                                                   FROM @Tracks2 [TRK2DUP]
                                                  WHERE [TRK2DUP].[TrackReference] <> 0
                                               GROUP BY [TRK2DUP].[TrackReference]
                                                 HAVING count(1) > 1)
               AND [TRK2].[RESID] = 0
          END
        SELECT @TotalResult = COUNT(1) FROM @Tracks2
        EXEC [dbo].[KAAS_WD_GetDocumentsIMResult] @Tracks2, @res
      END
    ELSE
      BEGIN
        SET @MinValue = (@PageNumber - 1) * @PageSize
        SET @MaxValue = @PageNumber * @PageSize

        SELECT @TotalResult = COUNT(1) FROM @Tracks2

        INSERT
          INTO @Tracks3
              ([RESID],
               [TrackReference],
               [SORTER])
        SELECT [TRK].[RESID],
               [TRK].[TrackReference],
               ''
          FROM @Tracks2 [TRK]
         WHERE [TRK].[id] > @MinValue
           AND [TRK].[id] <= @MaxValue

        EXEC [dbo].[KAAS_WD_GetDocumentsIMResult] @Tracks3, @res
      END
  END
ELSE
  BEGIN
    INSERT
      INTO @Tracks2
          ([RESID],
           [TrackReference],
           [SORTER])
      SELECT [TRK].[RESID],
             [TRK].[TrackReference],
             ''
        FROM @Tracks [TRK]
       WHERE [TRK].[SORTER] <> ''
    ORDER BY [TRK].[SORTER] ASC,
             [TRK].[id] ASC

    IF ISNULL(@PageNumber, 0) = 0 OR ISNULL(@PageSize, 0) = 0
      BEGIN
        SELECT @TotalResult = COUNT(1) FROM @Tracks2
        EXEC [dbo].[KAAS_WD_GetDocumentsIMResult] @Tracks2, @res
      END
    ELSE
      BEGIN
        SET @MinValue = (@PageNumber - 1) * @PageSize
        SET @MaxValue = @PageNumber * @PageSize

        SELECT @TotalResult = COUNT(1) FROM @Tracks2

        INSERT
          INTO @Tracks3
              ([RESID],
               [TrackReference],
               [SORTER])
        SELECT [TRK].[RESID],
               [TRK].[TrackReference],
               ''
          FROM @Tracks2 [TRK]
         WHERE [TRK].[id] > @MinValue
           AND [TRK].[id] <= @MaxValue

        EXEC [dbo].[KAAS_WD_GetDocumentsIMResult] @Tracks3, @res
      END
  END

DECLARE @IssueData NVARCHAR(MAX)
DECLARE @Issue     NVARCHAR(20)
IF EXISTS
  (SELECT TOP 1 1
     FROM @FullTextStopwords)
  BEGIN
    IF @ExactPhrase = 1
      BEGIN
        SET @Issue = N'Noise Words'
      END
    ELSE
      BEGIN
        SET @Issue = N'Noise Words'
      END

    SET @IssueData = N''
    SELECT @IssueData = @IssueData
                      + CASE WHEN @IssueData = N''
                                  THEN N''
                             ELSE N', ' END
                      + N'"' + [stopword] + N'"'
      FROM @FulltextStopwords

    IF @ExactPhrase = 1
      BEGIN
        SET @IssueData = N'The phrase you are looking for contains the following Noise Words:

' + @IssueData + N'

Noise Words are words that are likely to appear in almost every piece of English text and that can significantly slow down your search. To prevent this we have done a simplified search. If this doesn''t return what you are looking for, please search for different terms or an exact phrase.

When using multiple words to find content in a document use AND or OR between the words.

AND finds documents containing all the words in the search.
OR finds documents containing at least one of the words in the search.'
      END
    ELSE
      BEGIN
        SET @IssueData = N'You are searching for a Noise Word:

' + @IssueData + N'

Noise Words are words that are likely to appear in almost every piece of English text and that can significantly slow down your search. To prevent this we have done a simplified search. If this doesn''t return what you are looking for, please search for different terms or an exact phrase.'
      END

    INSERT
      INTO @Issues
          ([Issue],
           [Data])
    VALUES(@Issue,
           @IssueData)
  END

SELECT @TotalResult AS [Total]

IF EXISTS
  (SELECT TOP 1 1
     FROM @Issues)
  BEGIN
    SELECT [Issue], [Data] FROM @Issues
  END

SET NOCOUNT OFF

END



GO


IF OBJECT_ID(N'KAAS_WD_GetMatterDocFolder',N'P') IS NOT NULL
    DROP PROCEDURE [dbo].[KAAS_WD_GetMatterDocFolder]
GO

CREATE PROCEDURE [dbo].[KAAS_WD_GetMatterDocFolder]      
(  
 @matter      VARCHAR(20)  
)  
AS      
/********************************************************************************************************
* Gets the folder structure for a Matter. Creates it if it doesn't exist, use the CasePlan folder       * 
* structure as a template. Create it too, if it doesn't exist.		   								    *
*                                                                                                       *
* Stored Procedure Name : [dbo].[KAAS_WD_GetMatterDocFolder]										    *
* Copied from			: [dbo].[KAAS_GetMatterDocFolderTree]									    *
*                                                                                                       *
* Modification History:                                                                                 *
* 2019-04-23    Vinodhan K        Created                                                               *
* 2019-06-19    Vinodhan K        Removed the condition to check whether UseDocFolders is set as True   *
*                                 or False. For now it wil return the data even when it is set as False *  
* 2022-03-11    Sadiq			  Copied for Word addin                                                 
 *******************************************************************************************************/    
BEGIN    
  DECLARE @WKCODE CHAR(4)      
  DECLARE @MatterCode VARCHAR(20)      
  DECLARE @folderid INT    
      
  SELECT TOP 1      
         @MatterCode = [SM].[CODE],      
         @WKCODE = [TMP].[WKCODE]      
    FROM [dbo].[SearchMatters] SM      
         INNER JOIN [dbo].[CaseMaster] CSM      
                 ON [CSM].[CSCODE] = SM.[Code]      
         LEFT OUTER JOIN [dbo].[Templates] [TMP]      
                      ON [TMP].[WKCODE] = [CSM].[CSWKTCODE]      
   WHERE [CSM].[CSCODE] = @matter      
      
  IF @MatterCode IS NULL      
    BEGIN      
      -- Dummy statement      
      SELECT TOP 0      
             CONVERT(INT, 0) AS [id],      
             CONVERT(INT, 0) AS [parent],      
             CONVERT(VARCHAR(500), '') AS [foldername],      
             CONVERT(INT, 0) AS [foldericon]      
      
      RETURN      
    END      
      
  SELECT TOP 1      
         @folderid = MDF.[DocFolderID]      
    FROM [dbo].[MatterDocFolders] MDF      
   WHERE [MDF].[MatterCode] = @MatterCode      
      
  IF @folderid IS NULL      
    BEGIN      
      DECLARE @DefaultRootFolderName VARCHAR(500)      
      
      SELECT @DefaultRootFolderName = CONVERT(VARCHAR(500), [SET].[KeyValue])      
        FROM [dbo].[Settings] [SET]      
       WHERE [SET].[KeyName] = 'DocFolderDefaultRootName'      
      
      SET @DefaultRootFolderName = RTRIM(ISNULL(@DefaultRootFolderName, 'Documents'))      
      
      IF @WKCODE IS NULL      
        BEGIN      
          EXEC @folderid = [dbo].[KAAS_SaveDocFolderTree] '<folders><folder id="1" newfolder="True" deletefolder="False" foldername="Documents" parent="0" foldericon="1"/></folders>'      
      
          UPDATE [DF]      
             SET [DF].[foldername] = @DefaultRootFolderName      
            FROM [dbo].[DocFolders] [DF]       
           WHERE [DF].[id] = @folderid      
        END      
      ELSE      
        BEGIN      
          SELECT TOP 1      
                 @folderid = CDF.[DocFolderID]      
            FROM [dbo].[CasePlanDocFolders] CDF      
           WHERE [CDF].[WKCode] = @WKCODE      
      
          IF @folderid IS NULL      
            BEGIN      
              EXEC @folderid = [dbo].[KAAS_SaveDocFolderTree] '<folders><folder id="1" newfolder="True" deletefolder="False" foldername="Documents" parent="0" foldericon="1"/></folders>'      
      
UPDATE [DF]      
                 SET [DF].[foldername] = @DefaultRootFolderName      
                FROM [dbo].[DocFolders] [DF]       
               WHERE [DF].[id] = @folderid      
      
              INSERT      
                INTO [dbo].[CasePlanDocFolders]      
                    ([WKCode],      
                     [DocFolderID])      
              SELECT @WKCODE,      
                     @folderid      
            END      
      
          DECLARE @DUP TABLE      
                 ([id] INT)      
      
          INSERT INTO @DUP      
          EXEC @folderid = [dbo].[KAAS_DuplicateDocFolderTree] @folderid      
        END      
      
      INSERT      
        INTO [dbo].[MatterDocFolders]      
            ([MatterCode],      
             [DocFolderID])      
      SELECT @MatterCode,      
             @folderid      
    END    
      
 SELECT  
  *    
 FROM [dbo].[KAAS_FN_GetDocFolders](@matter, @folderid, 1, 1)     
END
GO

IF OBJECT_ID(N'KAAS_WD_InsertDocToFolders',N'P')IS NOT NULL
    DROP PROCEDURE [dbo].[KAAS_WD_InsertDocToFolders]
GO


CREATE PROCEDURE
       [dbo].[KAAS_WD_InsertDocToFolders]
      (@TrackRef			INT,
       @DocFolderId         INT)
AS
/*************************************************************************************************************
 *                                                                                                           *
 * [dbo].[KAAS_WD_InsertDocToFolders]                                                                      *
 *                                                                                                           *                                                                                                   *
 * Modification history                                                                                      *
 *                                                                                                           *             *
 * Sadiq			 2022-03-14    Created                                                                                                        *
 *************************************************************************************************************/
BEGIN
	IF EXISTS
  (SELECT TOP 1 1 FROM DiaryAttachments where TrackReference=@TrackRef)
  BEGIN
  INSERT INTO
	 [dbo].[DocFolderDocuments] VALUES
	 (@TrackRef,@DocFolderId)
  END
	

END

GO

IF OBJECT_ID(N'KAAS_WD_ListDocumentClassesForMatter',N'P')IS NOT NULL
    DROP PROCEDURE [dbo].[KAAS_WD_ListDocumentClassesForMatter]
GO

CREATE PROCEDURE
       [dbo].[KAAS_WD_ListDocumentClassesForMatter]
      (@MATTER                VARCHAR(20),
       @CurrentDepartmentOnly BIT,
       @IncludeRetired        BIT)
AS
/*************************************************************************************************************
 *                                                                                                           *
 * [dbo].[KAAS_WD_ListDocumentClassesForMatter]                                                                   *
 *                                                                                                           *
 * Description:  Lists the document classes that have been used in any matters for the current matter's      *
 *               Department                                                                                  *
 *                                                                                                           *
 *                                                                                                          *
 * Modification History                                                                                      *
 * 2022-03-30   Sadiq       Copied from ky_ListDocumentClassesForMatter                                                                    *
 *                                                                                                           *
 *************************************************************************************************************/
BEGIN

SET NOCOUNT ON

DECLARE @DEPT   VARCHAR(5)
DECLARE @MATS   TABLE
       ([Code]  VARCHAR(20)                  PRIMARY KEY   NOT NULL)

DECLARE @CLS    TABLE
       ([Code]  VARCHAR(3)                   PRIMARY KEY   NOT NULL,
        [Desc]  VARCHAR(40)                                NOT NULL,
        [ForD]  BIT                                        NOT NULL,
        [Retd]  CHAR                                       NOT NULL,
        UNIQUE NONCLUSTERED ([ForD], [Desc], [Code]),
        UNIQUE NONCLUSTERED ([Retd], [Code]))

IF RTRIM(ISNULL(@MATTER, '')) = ''
  BEGIN
    SET @CurrentDepartmentOnly = 0
  END

IF @CurrentDepartmentOnly = 1
  BEGIN
    SELECT @DEPT = [SM].[Dept]
      FROM [dbo].[SearchMatters] [SM]
     WHERE [SM].[Code] = @MATTER
  END

IF RTRIM(ISNULL(@DEPT, '')) = ''
  BEGIN
    SET @CurrentDepartmentOnly = 0
  END

IF @CurrentDepartmentOnly = 1
  BEGIN
    INSERT
      INTO @MATS
    SELECT [SM].[Code]
      FROM [dbo].[SearchMatters] [SM]
     WHERE [SM].[Dept] = @DEPT

    INSERT
      INTO @CLS
          ([Code],
           [Desc],
           [ForD],
           [Retd])
    SELECT [NEW].[DocClass],
           [DCL].[CLASSDESCRIPTION],
           0,
           [DCL].[Retired]
      FROM ( SELECT [DAS].[DocClass]
               FROM @MATS [MAT]
                    INNER JOIN [dbo].[DiaryAttachmentSelector] [DAS]
                            ON [DAS].[CASECODE] = [MAT].[Code]
            GROUP BY [DAS].[DocClass]) [NEW]
           INNER JOIN [dbo].[DocumentClasses] [DCL]
                   ON [DCL].[CLASSCODE] = [NEW].[DocClass]
  END

IF NOT EXISTS
  (SELECT TOP 1 1
     FROM @CLS)
  BEGIN
    SET @CurrentDepartmentOnly = 0
  END

IF @CurrentDepartmentOnly = 0
  BEGIN
    INSERT
      INTO @CLS
          ([Code],
           [Desc],
           [ForD],
           [Retd])
    SELECT CONVERT(VARCHAR(3), [DCL].[CLASSCODE]),
           [DCL].[CLASSDESCRIPTION],
           1,
           [DCL].[Retired]
      FROM [dbo].[DocumentClasses] [DCL]
           LEFT OUTER JOIN @CLS [CLS]
                        ON [CLS].[Code] = [DCL].[CLASSCODE]
     WHERE [CLS].[Code] IS NULL
  END

IF @IncludeRetired = 0
  BEGIN
    DELETE @CLS WHERE [Retd] = 'Y'
  END

--Make sure GEN is always in the list
INSERT
  INTO @CLS
      ([Code],
       [Desc],
       [ForD],
       [Retd])
SELECT CONVERT(VARCHAR(3), [DCL].[CLASSCODE]),
       [DCL].[CLASSDESCRIPTION],
       1,
       [DCL].[Retired]
  FROM [dbo].[DocumentClasses] [DCL]
       LEFT OUTER JOIN @CLS [CLS]
                    ON [CLS].[Code] = [DCL].[CLASSCODE]
 WHERE [DCL].[CLASSCODE] = 'GEN'
   AND [CLS].[Code] IS NULL

  SELECT [CLS].[Code] AS [ClassCode],
         [CLS].[Desc] AS [ClassDescription],
         [CLS].[Retd] AS [Retired]
    FROM @CLS [CLS]
ORDER BY [ForD],
         [Desc]

SET NOCOUNT OFF

END


GO


IF OBJECT_ID(N'KAAS_WD_UpdateAttachmentToPDF',N'P')IS NOT NULL
    DROP PROCEDURE [dbo].[KAAS_WD_UpdateAttachmentToPDF]
GO


CREATE PROCEDURE
       [dbo].[KAAS_WD_UpdateAttachmentToPDF]
      (@TrackRef			INT,
	   @FilePath            VARCHAR(MAX)
	   )
AS
/*************************************************************************************************************
 *                                                                                                           *
 * [dbo].[KAAS_WD_InsertDocToFolders]                                                                      *
 *                                                                                                           *                                                                                                   *
 * Modification history                                                                                      *
 *                                                                                                           *             *
 * Sadiq			 2022-03-14    Created                                                                                                        *
 *************************************************************************************************************/
BEGIN
	IF EXISTS
  (SELECT TOP 1 1 FROM DiaryAttachments where TrackReference=@TrackRef)
  BEGIN
  UPDATE [DiaryAttachments] 
	SET [FILEPATH]=@FilePath , [TYPE]='PDF'
	WHERE TrackReference=@TrackRef

	END
	

END

GO

IF OBJECT_ID(N'KAAS_WD_UpdateLastAccessedDateTime',N'P')IS NOT NULL
    DROP PROCEDURE [dbo].[KAAS_WD_UpdateLastAccessedDateTime]
GO


CREATE PROCEDURE
       [dbo].[KAAS_WD_UpdateLastAccessedDateTime]
      (@TrackRef			INT,
	  @SaveAsVersion	 BIT
	   )
AS
/*************************************************************************************************************
 *                                                                                                           *
 * [dbo].[KAAS_WD_UpdateLastAccessedDateTime]                                                                      *
 *                                                                                                           *                                                                                                   *
 * Modification history                                                                                      *
 *                                                                                                           *             *
 * Sadiq			 2022-05-25    Created                                                                                                        *
 *************************************************************************************************************/
BEGIN
	IF EXISTS
  (SELECT TOP 1 1 FROM DiaryAttachments where TrackReference=@TrackRef)
  BEGIN
  UPDATE [DiaryAttachments] 
	SET [LASTACCESSDATE]=GETDATE() 
	WHERE TrackReference=@TrackRef

		IF(@SaveAsVersion=1)
		BEGIN
			UPDATE [DiaryAttachments] 
			SET [DATEENTERED]=GETDATE() 
			WHERE TrackReference=@TrackRef
		END

	END
	

END

GO

IF OBJECT_ID(N'KAAS_WD_XMLMatterSearch',N'P')IS NOT NULL
    DROP PROCEDURE [dbo].[KAAS_WD_XMLMatterSearch]
GO

CREATE PROCEDURE      
       [dbo].[KAAS_WD_XMLMatterSearch]      
      (@Search        NVARCHAR(MAX),    
	   @PageNumber    INT=1,    
       @PageSize      INT=500,
	   @ColumnNames   VARCHAR(50) = '',
	   @FilterValues  VARCHAR(50) = '')      
AS      
/*******************************************************************************************************
* Performs a matter search based on search criteria passed in in XML format through @Search.           * 
*                                                                                                      *											   *
* Copied from			: [dbo].[KAAS_XMLMATTERSEARCH]											   *
*                                                                                                      *
* Modification History:                                                                                *
* 2019-04-24    Vinodhan K        Created                                                              *
* 2020-02-25    Prabhu V          Applied Custom Filter Changes with Fetching the Closed Matter        *
*								  details as well                                                      *
* 2020-06-12    Arun V            Closed matter check have added in recent matter list                 *
* 2020-09-02    Prabhu V          By Default All Matters should be fetched both open and closed  	   *
*								  KEYSAAS-2216 														   *
* 2022-03-08    Sadiq			 Copied from KAAS_XMLMATTERSEARCH
 *******************************************************************************************************/
BEGIN      
      
DECLARE @closed                 NCHAR(1)      
DECLARE @orderby                NVARCHAR(50)      
DECLARE @order                  NVARCHAR(4)      
DECLARE @howmany                INT      
DECLARE @handler                NVARCHAR(10)      
DECLARE @which                  INT      
DECLARE @BitWiseID              BIGINT      
DECLARE @searchtext             VARCHAR(MAX)      
DECLARE @searchonlycode         VARCHAR(3)      
DECLARE @departmentdesc         VARCHAR(50)      
DECLARE @department             VARCHAR(5)      
DECLARE @worktypedesc           VARCHAR(50)      
DECLARE @worktype               VARCHAR(5)      
DECLARE @filecolourdesc         VARCHAR(20)      
DECLARE @filecolour             VARCHAR(3)      
DECLARE @feecode                VARCHAR(10)      
      
DECLARE @includename            BIT      
DECLARE @includedescription     BIT      
DECLARE @includeaddress         BIT      
DECLARE @includeemail           BIT      
DECLARE @includecode            BIT      
DECLARE @includeoldref          BIT      
DECLARE @includeuser1           BIT      
      
DECLARE @andorname              VARCHAR(3)      
DECLARE @andornamevalue         VARCHAR(200)      
DECLARE @andordescription       VARCHAR(3)      
DECLARE @andordescriptionvalue  VARCHAR(200)      
DECLARE @andoraddress           VARCHAR(3)      
DECLARE @andoraddressvalue      VARCHAR(200)      
DECLARE @andorcode              VARCHAR(3)      
DECLARE @andorcodevalue         VARCHAR(200)      
DECLARE @andorfeecode           VARCHAR(3)      
DECLARE @andorfeecodevalue      VARCHAR(200)      
DECLARE @andoroldref            VARCHAR(3)      
DECLARE @andoroldrefvalue       VARCHAR(200)      
DECLARE @andordept              VARCHAR(3)      
DECLARE @andordeptvalue         VARCHAR(200)      
DECLARE @andoruser1             VARCHAR(3)      
DECLARE @andoruser1value        VARCHAR(200)      
DECLARE @andoruser2             VARCHAR(3)      
DECLARE @andoruser2value        VARCHAR(200)      
DECLARE @andoruser3             VARCHAR(3)      
DECLARE @andoruser3value        VARCHAR(200)      
DECLARE @andoryourref           VARCHAR(3)      
DECLARE @andoryourrefvalue      VARCHAR(200)      
DECLARE @andorwtype             VARCHAR(3)      
DECLARE @andorwtypevalue        VARCHAR(200)      
      
DECLARE @iSL INT      
    
DECLARE @StartRow INT;      
DECLARE @EndRow INT;     
    
SET @PageNumber = ISNULL(@PageNumber, 0) - 1; -- 0 BASED INDEX
        
IF(@PageNumber > -1)
BEGIN        
	SET @StartRow = ((@PageNumber) * @PageSize) + 1;
	SET @EndRow = (@StartRow + @PageSize) - 1;
END   
      
BEGIN TRY      
EXEC sp_xml_preparedocument @iSL OUTPUT, @Search      
END TRY      
BEGIN CATCH      
EXEC sp_xml_preparedocument @iSL OUTPUT, N'<search/>'      
END CATCH      
      
SELECT @closed = CASE ISNULL(SRC.[closed], N'A')      
                      WHEN N'Y' THEN N'Y'   
                      WHEN N'B' THEN N'B'    
					  WHEN N'N' THEN N'N'   
					  WHEN N'A' THEN N'A'   
                       END,      
       @orderby = ISNULL(SRC.[orderby], N''),      
       @order = CASE ISNULL(SRC.[order], N'DESC')
                     WHEN N'DESC'      
                          THEN N'DESC'      
                     ELSE N'ASC' END,      
       @howmany = ISNULL(SRC.[howmany], 500),      
       @handler = ISNULL(SRC.[handler], N''),      
       @which = CASE ISNULL(SRC.[which], 0)      
                     WHEN 1 THEN 1 --My Matters      
                     WHEN 2 THEN 2 --Recent Matters      
                     ELSE 0 END,   --All Matters      
       @searchtext = ISNULL(SRC.[search], ''),      
       @searchonlycode = ISNULL(SRC.[searchonlycode], ''),      
       @departmentdesc = ISNULL(SRC.[dept], ''),      
       @worktypedesc = ISNULL(SRC.[wtype], ''),      
       @filecolourdesc = ISNULL(SRC.[colour], ''),      
       @feecode = ISNULL(SRC.[feecodefilter], ''),      
       @includeaddress = ISNULL(SRC.[includeaddress], 0),      
       @includeemail = ISNULL(SRC.[includeemail], 0),      
       @includecode = ISNULL(SRC.[includecode], 0),      
       @includedescription = ISNULL(SRC.[includedescription], 0),      
       @includename = ISNULL(SRC.[includename], 0),      
       @includeoldref = ISNULL(SRC.[includeoldref], 0),      
       @includeuser1 = ISNULL(SRC.[includeuser1], 0),      
       @andorname = ISNULL(SRC.[andorname], ''),      
       @andornamevalue = ISNULL(SRC.[andornamevalue], ''),      
       @andordescription = ISNULL(SRC.[andordescription], ''),      
       @andordescriptionvalue = ISNULL(SRC.[andordescriptionvalue], ''),      
       @andoraddress = ISNULL(SRC.[andoraddress], ''),      
       @andoraddressvalue = ISNULL(SRC.[andoraddressvalue], ''),      
       @andorcode = ISNULL(SRC.[andorcode], ''),      
       @andorcodevalue = ISNULL(SRC.[andorcodevalue], ''),      
       @andorfeecode = ISNULL(SRC.[andorfeecode], ''),      
       @andorfeecodevalue = ISNULL(SRC.[andorfeecodevalue], ''),      
       @andoroldref = ISNULL(SRC.[andoroldref], ''),      
       @andoroldrefvalue = ISNULL(SRC.[andoroldrefvalue], ''),      
       @andordept = ISNULL(SRC.[andordept], ''),      
       @andordeptvalue = ISNULL(SRC.[andordeptvalue], ''),      
       @andoruser1 = ISNULL(SRC.[andoruser1], ''),      
       @andoruser1value = ISNULL(SRC.[andoruser1value], ''),      
       @andoruser2 = ISNULL(SRC.[andoruser2], ''),      
       @andoruser2value = ISNULL(SRC.[andoruser2value], ''),      
       @andoruser3 = ISNULL(SRC.[andoruser3], ''),      
       @andoruser3value = ISNULL(SRC.[andoruser3value], ''),      
       @andoryourref = ISNULL(SRC.[andoryourref], ''),      
       @andoryourrefvalue = ISNULL(SRC.[andoryourrefvalue], ''),      
       @andorwtype = ISNULL(SRC.[andorwtype], ''),      
       @andorwtypevalue = ISNULL(SRC.[andorwtypevalue], '')      
  FROM OPENXML(@iSL, N'search')      
  WITH ([closed]                   NCHAR(1)        '@closed',      
        [orderby]                  NVARCHAR(50)    '@orderby',      
        [order]                    NVARCHAR(4)     '@order',      
        [howmany]                  INT             '@howmany',      
        [handler]                  NVARCHAR(10)    '@handler',      
        [which]                    INT             '@which',      
        [search]                   VARCHAR(MAX)    '@search',      
        [searchonlycode]           VARCHAR(3)      '@searchonlycode',      
        [dept]                     VARCHAR(50)     '@departmentfilter',      
        [wtype]                    VARCHAR(50)     '@worktypefilter',      
        [colour]                   VARCHAR(20)     '@filecolourfilter',      
        [feecodefilter]            VARCHAR(10)     '@feecodefilter',      
        [includeaddress]           BIT             'include/includefield[@name="address"]',      
        [includecode]              BIT             'include/includefield[@name="code"]',      
        [includedescription]       BIT             'include/includefield[@name="description"]',      
        [includename]              BIT             'include/includefield[@name="name"]',      
        [includeoldref]            BIT             'include/includefield[@name="oldref"]',      
        [includeuser1]             BIT             'include/includefield[@name="user1"]',      
        [includeemail]             BIT             'include/includefield[@name="email"]',      
        [andorname]                VARCHAR(3)      'andor/andorfield[@name="name"]/@andor',      
        [andornamevalue]           VARCHAR(100)    'andor/andorfield[@name="name"]/@search',      
        [andordescription]         VARCHAR(3)      'andor/andorfield[@name="description"]/@andor',      
        [andordescriptionvalue]    VARCHAR(100)    'andor/andorfield[@name="description"]/@search',      
        [andoraddress]             VARCHAR(3)      'andor/andorfield[@name="address"]/@andor',      
        [andoraddressvalue]        VARCHAR(100)    'andor/andorfield[@name="address"]/@search',      
        [andorcode]                VARCHAR(3)      'andor/andorfield[@name="code"]/@andor',      
        [andorcodevalue]           VARCHAR(100)    'andor/andorfield[@name="code"]/@search',      
        [andorfeecode]             VARCHAR(3)      'andor/andorfield[@name="feecode"]/@andor',      
        [andorfeecodevalue]        VARCHAR(100)    'andor/andorfield[@name="feecode"]/@search',      
        [andoroldref]              VARCHAR(3)      'andor/andorfield[@name="oldref"]/@andor',      
        [andoroldrefvalue]         VARCHAR(100)    'andor/andorfield[@name="oldref"]/@search',      
        [andordept]                VARCHAR(3)      'andor/andorfield[@name="dept"]/@andor',      
        [andordeptvalue]           VARCHAR(100)    'andor/andorfield[@name="dept"]/@search',      
        [andoruser1]               VARCHAR(3)      'andor/andorfield[@name="user1"]/@andor',      
        [andoruser1value]          VARCHAR(100)    'andor/andorfield[@name="user1"]/@search',      
        [andoruser2]               VARCHAR(3)      'andor/andorfield[@name="user2"]/@andor',      
        [andoruser2value]          VARCHAR(100)    'andor/andorfield[@name="user2"]/@search',      
        [andoruser3]               VARCHAR(3)      'andor/andorfield[@name="user3"]/@andor',      
        [andoruser3value]          VARCHAR(100)    'andor/andorfield[@name="user3"]/@search',      
        [andoryourref]             VARCHAR(3)      'andor/andorfield[@name="yourref"]/@andor',      
        [andoryourrefvalue]        VARCHAR(100)    'andor/andorfield[@name="yourref"]/@search',      
        [andorwtype]               VARCHAR(3)      'andor/andorfield[@name="wtype"]/@andor',      
        [andorwtypevalue]          VARCHAR(100)    'andor/andorfield[@name="wtype"]/@search') SRC      
      
IF @filecolourdesc <> ''      
  BEGIN      
    SELECT @filecolour = FC.[COLOURCODE]      
      FROM [dbo].[FileColours] FC WITH (NOLOCK)      
     WHERE FC.[COLOURDESC] = @filecolourdesc      
  END      
      
SET @filecolour = ISNULL(@filecolour, '')      
      
IF @departmentdesc <> ''      
  BEGIN      
    SELECT @department = DP.[CODE]      
      FROM [dbo].[Departments] DP WITH (NOLOCK)      
     WHERE DP.[DESCRIPTION] = @departmentdesc      
  END      
      
SET @department = ISNULL(@department, '')      
      
IF @worktypedesc <> ''      
  BEGIN      
    IF @worktypedesc LIKE 'Unknown Worktype: %'      
      BEGIN      
        SELECT @worktype = WT.[CODE]      
          FROM [dbo].[WorkTypes] WT WITH (NOLOCK)      
         WHERE 'Unknown Worktype: ' + WT.[CODE] = @worktypedesc      
            OR WT.[DESC] = @worktypedesc      
      END      
    ELSE      
      BEGIN      
        SELECT @worktype = WT.[CODE]      
          FROM [dbo].[WorkTypes] WT WITH (NOLOCK)      
         WHERE WT.[DESC] = @worktypedesc      
      END      
  END      
      
SET @worktype = ISNULL(@worktype, '')      
      
SET @feecode = ISNULL(@feecode, '')      
      
EXEC sp_xml_removedocument @iSL      
      
DECLARE @NCOMMAND NVARCHAR(MAX)      
DECLARE @HasWhere BIT      
      
SET @HasWhere = 0      
      
SET @handler = CASE WHEN RTRIM(ISNULL(@handler, '')) = '' THEN 'ADM' ELSE RTRIM(@handler) END      
      
SELECT @BitWiseID = HNG.[BitWiseID]      
  FROM HandlerNETGroupMembership HNG WITH (NOLOCK)      
 WHERE HNG.[HANDLER] = CONVERT(VARCHAR(10), @handler)      
      
SET @BitWiseID = ISNULL(@BitWiseID, [dbo].[KAAS_FN_GetOldPermissions](RTRIM(ISNULL(@handler, ''))))      
      
IF (@handler = 'ADM')      
  BEGIN      
    SET @BitWiseID = CONVERT(BIGINT, -1)      
  END     
  
  -- Custom Filter Start
SET @NCOMMAND = N'   
DECLARE @StatusCount INT = 0;
IF @ColumnNames <> ''''
BEGIN
DECLARE @CustomFilter TABLE
       ([Status]     VARCHAR(20))	

	SELECT columnName,rownumber INTO #columntbl FROM 
		(SELECT 
	LTRIM(RTRIM(m.n.value(''.[1]'',''varchar(8000)''))) AS columnName, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS rownumber
	FROM
	(
	SELECT CAST(''<XMLRoot><RowData>'' + REPLACE(@ColumnNames,'','',''</RowData><RowData>'') + ''</RowData></XMLRoot>'' AS XML) AS x
	)t
	CROSS APPLY x.nodes(''/XMLRoot/RowData'')m(n)) ColumnTbl  

	SELECT FilterValue,rownumber INTO #FILTERVALUETBL FROM 
		(SELECT 
	LTRIM(RTRIM(m.n.value(''.[1]'',''varchar(8000)''))) AS FilterValue, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS rownumber
	FROM
	(
	SELECT CAST(''<XMLRoot><RowData>'' + REPLACE(@FilterValues,''|'',''</RowData><RowData>'') + ''</RowData></XMLRoot>'' AS XML) AS x
	)t
	CROSS APPLY x.nodes(''/XMLRoot/RowData'')m(n)) FILTERVALUETBL 

	DECLARE @count INT
	DECLARE @lptcnt INT=1
	
	DECLARE @ColumnName VARCHAR(50)
	DECLARE @FilterValue VARCHAR(2000)

	SELECT @count = count(1)  FROM #columntbl;

	WHILE @lptcnt <= @count
	BEGIN

	   SET @FilterValue =''''
	   SELECT @ColumnName = columnName FROM #columntbl WHERE rownumber = @lptcnt
	   SELECT @FilterValue = FilterValue FROM #FILTERVALUETBL WHERE rownumber = @lptcnt
	   IF(@ColumnName = ''Closed'')
		INSERT INTO @CustomFilter ([Status]) VALUES (@FilterValue)	   

	   SET @lptcnt = @lptcnt + 1;   

	END
	
	SET @StatusCount = (SELECT COUNT(1) FROM @CustomFilter WHERE [Status] IS NOT NULL AND [Status] <>'''')	
	
END '
-- Custom Filter End

SET @NCOMMAND = @NCOMMAND + N'      
  --SELECT ''How Many: '' + CONVERT(VARCHAR(10), @HowMany)      
  --SELECT ''Handler: '' + @Handler      
  --SELECT ''BitWise ID: '' + CONVERT(VARCHAR(10), @BitWiseID)      
      
DECLARE @Result TABLE      
       ([id]                    INT                       IDENTITY(1, 1) PRIMARY KEY CLUSTERED,      
        [Time]                  VARCHAR(5)                NOT NULL,      
        [Code]                  VARCHAR(20)               NOT NULL,      
        [FileColour]            VARCHAR(20)               NOT NULL,      
        [FileColour_Fld]        VARCHAR(20)               NOT NULL,      
        [BackgroundColour]      VARCHAR(30)               NOT NULL,      
        [TextColour]            VARCHAR(30)               NOT NULL,      
        [Name]                  VARCHAR(256)              NOT NULL,      
        [Description]           VARCHAR(200)              NOT NULL,      
        [Fe]                    VARCHAR(30)               NOT NULL,      
        [FeeCode]               VARCHAR(10)               NOT NULL,      
        [FeeEarner]             VARCHAR(30)               NOT NULL,      
        [User1]                 VARCHAR(100)              NOT NULL,      
        [User2]                 VARCHAR(100)              NOT NULL,      
        [User3]                 VARCHAR(100)              NOT NULL,      
        [Started]               DATETIME                  NOT NULL,      
        [Dept]                  VARCHAR(50)               NOT NULL,      
        [WorkType]              VARCHAR(50)               NOT NULL,      
        [Privileges]            BIGINT                    NOT NULL,      
        [OldRef]                VARCHAR(12)               NOT NULL,      
        [Address]               VARCHAR(500)              NOT NULL,      
        [Email]                 VARCHAR(50)               NOT NULL,      
        [Closed]                CHAR(1)                   NOT NULL,      
        [OrgCloseDate]          DATETIME                      NULL,      
        [CloseDate]             DATETIME                      NULL,      
        [FileRGBColour]         INT                       NOT NULL,      
        [LastAccessed]          DATETIME                      NULL,      
        [PhoneNumber]           VARCHAR(50)               NOT NULL,      
        [FileNum]               INT                       NOT NULL,      
        [ClientCode]            VARCHAR(10)               NOT NULL,      
        [DepartmentCode]        VARCHAR(5)                NOT NULL,      
        [FeeEarnerTeam]         VARCHAR(10)               NOT NULL,      
        [TeamName]              VARCHAR(30)               NOT NULL,      
        [YourRef]               VARCHAR(100)              NOT NULL,      
        [CaseStatusCode]        VARCHAR(6)                NOT NULL,      
        [CaseStatusDesc]        VARCHAR(50)               NOT NULL)      
      
  INSERT      
    INTO @Result      
        ([Time],      
         [Code],      
         [FileColour],      
         [FileColour_Fld],      
         [BackgroundColour],      
         [TextColour],      
         [Name],      
         [Description],      
         [Fe],      
         [FeeCode],      
         [FeeEarner],      
         [User1],      
         [User2],      
         [User3],      
         [Started],      
         [Dept],      
         [WorkType],      
  [Privileges],      
         [OldRef],      
         [Address],      
         [Email],      
         [Closed],      
         [OrgCloseDate],      
         [CloseDate],      
         [FileRGBColour],      
         [LastAccessed],      
         [PhoneNumber],      
         [FileNum],      
         [ClientCode],      
         [DepartmentCode],      
         [FeeEarnerTeam],      
         [TeamName],      
         [YourRef],      
         [CaseStatusCode],      
         [CaseStatusDesc])      
    
  SELECT      
         CASE WHEN TTT.[TOTALTIME] = ''00:00''      
                   THEN ''''      
              ELSE TTT.[TOTALTIME] END													  AS [Time],      
         SM.[Code]                                                                        AS [Code],      
         SM.[FileColourDescription]                                                       AS [FileColour],      
         SM.[FileColourDescription]                                                       AS [FileColour_Fld],      
         SM.[FileBackgroundColour]                                                        AS [BackgroundColour],      
         SM.[TextColour]                                                                  AS [TextColour],      
         SM.[Name]                                                                        AS [Name],      
         SM.[Description]                                                                 AS [Description],      
         SM.[HandlerName]                                                                 AS [Fe],      
         SM.[FECode]                                                                      AS [FeeCode],      
         SM.[HandlerName]                                                                 AS [FeeEarner],      
         SM.[User1]                                                                       AS [User1],      
         SM.[User2]                                                                       AS [User2],      
         SM.[User3]                                                                       AS [User3],      
         SM.[Started]                                                                     AS [Started],      
         SM.[DeptDescription]                                                             AS [Dept],      
         SM.[WTypeDescription]                                                            AS [WorkType],      
         SM.[Privileges]                                                                  AS [Privileges],      
         SM.[OldRef]                                                                      AS [OldRef],      
         SM.[Address]                                                                     AS [Address],      
         SM.[Email]                                                                       AS [Email],      
         CASE WHEN SM.[Closed] = 0 THEN ''N'' ELSE ''Y'' END                              AS [Closed],      
         SM.[OrgClosedDate]                                                               AS [OrgCloseDate],      
         SM.[CloseDate]                                                                   AS [CloseDate],      
         SM.[FileRGBColour]                                                               AS [FileRGBColour],      
         CASE WHEN YEAR(ISNULL(RML.[DateField], CONVERT(DATETIME, ''18000101''))) < 1901      
                   THEN NULL      
              ELSE ISNULL(RML.[DateField], CONVERT(DATETIME, ''18000101'')) END           AS [LastAccessed],      
         SM.[PhoneNumber]                                                                 AS [PhoneNumber],      
         SM.[FileNum]                                                                     AS [FileNum],      
         SM.[ClientCode]                                                                  AS [ClientCode],      
         SM.[Dept]                                                                        AS [DepartmentCode],      
         SM.[Team]                                                                        AS [FeeEarnerTeam],      
         SM.[TeamName]                                                                    AS [TeamName],      
         SM.[YourRef]                                                                     AS [YourRef],      
         SM.[MatterStatus]                                                                AS [CaseStatusCode],      
         SM.[MatterStatusDescription]                                                     AS [CaseStatusDesc]
    FROM [dbo].[SearchMatters] SM WITH (NOLOCK)      
         CROSS APPLY (SELECT SUBSTRING(CONVERT(VARCHAR, DATEADD(minute, ISNULL(SUM(ISNULL(TDB.[time], 0)), 0), ''19000101''), 108), 1, 5) AS [TOTALTIME]      
                        FROM [dbo].[TimeDayBook] TDB WITH (NOLOCK)      
                       WHERE TDB.[FeeEarn] = @Handler      
                         AND TDB.[matter] = SM.[code]
					     ) TTT'      
      
SET @NCOMMAND = @NCOMMAND + N'      
         LEFT OUTER JOIN [dbo].[RecentMatterList] RML WITH (NOLOCK)      
                      ON RML.[FEE] = @handler      
                     AND RML.[MATTER] = SM.[Code]
	      WHERE (@StatusCount = 0 OR (SM.[Closed] IN (SELECT FilterValue FROM 
															(SELECT LTRIM(RTRIM(m.n.value(''.[1]'',''varchar(8000)''))) AS FilterValue , ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as rownumber
															 FROM(
																SELECT CAST(''<XMLRoot><RowData>'' + REPLACE((SELECT DISTINCT [Status] FROM @CustomFilter WHERE [Status] IS NOT NULL and [Status] <>''''),'','',''</RowData><RowData>'') + ''</RowData></XMLRoot>'' AS XML) AS x
																)t
																CROSS APPLY x.nodes(''/XMLRoot/RowData'')m(n)) FILTERVALUETBLFnl))  or SM.Closed=1) '      
      
IF (@BitWiseID = 0)      
  BEGIN      
    SET @NCOMMAND = @NCOMMAND + N'      
   AND (   SM.[Privileges] = 0      
          OR SM.[Privileges] = -1)'      
  END      
ELSE IF (@BitWiseID = -1)      
  BEGIN      
    SET @NCOMMAND = @NCOMMAND + N'      
   AND 1 = 1'      
  END      
ELSE      
  BEGIN      
    SET @NCOMMAND = @NCOMMAND + N'      
   AND SM.[Privileges] & @BitWiseID <> 0'      
  END      
      
IF (@which = 1)      
  BEGIN      
    SET @NCOMMAND = @NCOMMAND + N'      
     AND SM.[FECode] = @handler'      
  END      
      
IF (@closed = 'N')      
  BEGIN      
    SET @NCOMMAND = @NCOMMAND + N'      
     AND SM.[Closed] = 0'      
  END      
      
IF (@closed = 'Y')      
  BEGIN      
    SET @NCOMMAND = @NCOMMAND + N'      
     AND SM.[Closed] = 1'      
  END      

IF (@closed = N'A')      
  BEGIN      
    SET @NCOMMAND = @NCOMMAND + N'      
     AND (SM.[Closed] = 0 OR SM.[Closed] = 1)'      
  END 
      
IF (@worktype <> '')      
  BEGIN      
    SET @NCOMMAND = @NCOMMAND + N'      
     AND SM.[WType] = @WorkType'      
  END      
      
IF (@department <> '')      
  BEGIN      
    SET @NCOMMAND = @NCOMMAND + N'      
     AND SM.[Dept] = @department'      
  END      
      
IF (@filecolour <> '')      
  BEGIN      
    SET @NCOMMAND = @NCOMMAND + N'      
     AND SM.[FileColour] = @filecolour'      
  END      
      
IF (@feecode <> '')      
  BEGIN      
    SET @NCOMMAND = @NCOMMAND + N'      
     AND SM.[FECode] = @feecode'      
  END      
      
DECLARE @SearchIncludes NVARCHAR(MAX)      
      
SET @SearchIncludes = N''      
      
IF (@searchtext <> '')      
  BEGIN      
    IF SUBSTRING(@searchtext, 1, 1) <> '%'      
      BEGIN      
        SET @searchtext = '%' + @searchtext      
      END      
      
    IF SUBSTRING(@searchtext, LEN(@searchtext), 1) <> '%'      
      BEGIN      
        SET @searchtext = @searchtext + '%'      
      END      
      
    SET @searchtext = @searchtext COLLATE SQL_Latin1_General_Cp1251_CS_AS      
          
     IF (@includeaddress = 0)       
    AND (@includecode = 0)      
    AND (@includedescription = 0)      
    AND (@includename = 0)      
    AND (@includeoldref = 0)      
    AND (@includeuser1 = 0)      
    AND (@includeemail = 0)      
      BEGIN      
        -- This is the standard call - not from Advanced Matter Search, so we search in everything      
        IF @SearchIncludes <> N''      
          BEGIN      
                SET @SearchIncludes = @SearchIncludes + N'      
          OR '      
          END      
      
        IF @searchonlycode <> 'yes'      
          BEGIN      
            SET @SearchIncludes = @SearchIncludes + N'SM.[NCDescription]         LIKE @searchtext      
          OR SM.[Code]                     LIKE @searchtext      
          OR SM.[User1]                    LIKE @searchtext      
          OR SM.[User2]                    LIKE @searchtext      
          OR SM.[User3]                    LIKE @searchtext      
          OR SM.[OldRef]                   LIKE @searchtext      
          OR SM.[YourRef]                  LIKE @searchtext      
          OR SM.[NCName]                   LIKE @searchtext      
          OR SM.[NCAddress]                LIKE @searchtext      
          OR SM.[Email]                    LIKE @searchtext      
          OR SM.[NCHandlerName]            LIKE @searchtext      
          OR SM.[NCPartnerName]            LIKE @searchtext      
          OR SM.[DeptDescription]          LIKE @searchtext      
          OR SM.[WTypeDescription]         LIKE @searchtext      
          OR SM.[FileColourDescription]    LIKE @searchtext      
          OR SM.[MatterStatusDescription]  LIKE @searchtext'      
          END      
        ELSE      
          BEGIN      
            SET @SearchIncludes = @SearchIncludes + N'SM.[Code]                  LIKE @searchtext'      
          END      
      
      END      
    ELSE      
      BEGIN      
        IF (@includeaddress = 1)      
          BEGIN      
            IF @SearchIncludes <> N''      
              BEGIN      
                SET @SearchIncludes = @SearchIncludes + N'      
          OR '      
              END      
      
            SET @SearchIncludes = @SearchIncludes + N'SM.[NCAddress]             LIKE @searchtext'      
          END      
      
        IF (@includecode = 1)      
          BEGIN      
            IF @SearchIncludes <> N''      
              BEGIN      
                SET @SearchIncludes = @SearchIncludes + N'      
          OR '      
              END      
      
            SET @SearchIncludes = @SearchIncludes + N'SM.[Code]                  LIKE @searchtext'      
          END      
      
        IF (@includedescription = 1)      
          BEGIN      
            IF @SearchIncludes <> N''      
              BEGIN      
                SET @SearchIncludes = @SearchIncludes + N'      
          OR '      
              END      
      
            SET @SearchIncludes = @SearchIncludes + N'SM.[NCDescription]         LIKE @searchtext'      
          END      
      
        IF (@includename = 1)      
          BEGIN      
            IF @SearchIncludes <> N''      
              BEGIN      
                SET @SearchIncludes = @SearchIncludes + N'      
          OR '      
              END      
      
            SET @SearchIncludes = @SearchIncludes + N'SM.[NCName]                LIKE @searchtext'      
          END      
      
        IF (@includeoldref = 1)      
          BEGIN      
            IF @SearchIncludes <> N''      
              BEGIN      
                SET @SearchIncludes = @SearchIncludes + N'      
          OR '      
              END      
      
            SET @SearchIncludes = @SearchIncludes + N'SM.[OldRef]                LIKE @searchtext'      
          END      
      
        IF (@includeuser1 = 1)      
          BEGIN      
            IF @SearchIncludes <> N''      
              BEGIN      
                SET @SearchIncludes = @SearchIncludes + N'      
          OR '      
              END      
      
            SET @SearchIncludes = @SearchIncludes + N'SM.[User1]                 LIKE @searchtext'      
          END      
      
        IF (@includeemail = 1)      
          BEGIN      
            IF @SearchIncludes <> N''      
              BEGIN      
                SET @SearchIncludes = @SearchIncludes + N'      
          OR '      
              END      
      
            SET @SearchIncludes = @SearchIncludes + N'SM.[Email]                 LIKE @searchtext'      
          END      
      
      END      
                  
  END      
      
IF @andoraddress = 'OR'      
  BEGIN      
    IF SUBSTRING(@andoraddressvalue, 1, 1) <> '%'      
      BEGIN      
        SET @andoraddressvalue = '%' + @andoraddressvalue       
      END      
      
    IF SUBSTRING(@andoraddressvalue, LEN(@andoraddressvalue), 1) <> '%'      
      BEGIN      
        SET @andoraddressvalue = @andoraddressvalue + '%'      
      END      
      
    SET @andoraddressvalue = @andoraddressvalue COLLATE SQL_Latin1_General_Cp1251_CS_AS      
      
    IF @SearchIncludes <> N''      
      BEGIN      
        SET @SearchIncludes = @SearchIncludes + N'      
          OR '      
      END      
      
    SET @SearchIncludes = @SearchIncludes + N'SM.[NCAddress]					LIKE @andoraddressvalue'      
  END      
      
IF @andorcode = 'OR'      
  BEGIN      
    IF CHARINDEX('%', @andorcodevalue, 1) = 0      
      BEGIN      
        IF SUBSTRING(@andorcodevalue, 1, 1) <> '%'      
          BEGIN      
            SET @andorcodevalue = '%' + @andorcodevalue       
          END      
      
        IF SUBSTRING(@andorcodevalue, LEN(@andorcodevalue), 1) <> '%'      
          BEGIN      
            SET @andorcodevalue = @andorcodevalue + '%'      
          END      
      END      
      
    IF @SearchIncludes <> N''      
      BEGIN      
        SET @SearchIncludes = @SearchIncludes + N'      
          OR '      
      END      
      
    SET @SearchIncludes = @SearchIncludes + N'SM.[Code]							LIKE @andorcodevalue'      
  END      
      
IF @andordept = 'OR'      
  BEGIN      
    IF @SearchIncludes <> N''      
      BEGIN      
        SET @SearchIncludes = @SearchIncludes + N'      
          OR '      
      END      
      
    SET @SearchIncludes = @SearchIncludes + N'SM.[Dept]= @andordeptvalue'      
  END      
      
IF @andordescription = 'OR'      
  BEGIN      
    IF SUBSTRING(@andordescriptionvalue, 1, 1) <> '%'      
      BEGIN      
        SET @andordescriptionvalue = '%' + @andordescriptionvalue       
      END      
      
    IF SUBSTRING(@andordescriptionvalue, LEN(@andordescriptionvalue), 1) <> '%'      
      BEGIN      
        SET @andordescriptionvalue = @andordescriptionvalue + '%'      
      END      
      
    SET @andordescriptionvalue = @andordescriptionvalue COLLATE SQL_Latin1_General_Cp1251_CS_AS      
      
    IF @SearchIncludes <> N''      
      BEGIN      
        SET @SearchIncludes = @SearchIncludes + N'      
          OR '      
      END      
      
    SET @SearchIncludes = @SearchIncludes + N'SM.[NCDescription]			LIKE @andordescriptionvalue'      
  END      
      
IF @andorfeecode = 'OR'      
  BEGIN      
    IF @SearchIncludes <> N''      
      BEGIN      
        SET @SearchIncludes = @SearchIncludes + N'      
          OR '      
      END      
      
    SET @SearchIncludes = @SearchIncludes + N'SM.[FECode] = @andorfeecodevalue'      
  END      
      
IF @andorname = 'OR'      
  BEGIN      
    IF SUBSTRING(@andornamevalue, 1, 1) <> '%'      
      BEGIN      
        SET @andornamevalue = '%' + @andornamevalue       
      END      
      
    IF SUBSTRING(@andornamevalue, LEN(@andornamevalue), 1) <> '%'      
      BEGIN      
        SET @andornamevalue = @andornamevalue + '%'      
      END      
      
    SET @andornamevalue = @andornamevalue COLLATE SQL_Latin1_General_Cp1251_CS_AS      
      
    IF @SearchIncludes <> N''      
      BEGIN      
        SET @SearchIncludes = @SearchIncludes + N'      
          OR '      
      END      
      
    SET @SearchIncludes = @SearchIncludes + N'SM.[NCName]							LIKE @andornamevalue'      
  END      
      
IF @andoroldref = 'OR'      
  BEGIN      
    IF SUBSTRING(@andoroldrefvalue, 1, 1) <> '%'      
      BEGIN      
        SET @andoroldrefvalue = '%' + @andoroldrefvalue       
      END      
      
    IF SUBSTRING(@andoroldrefvalue, LEN(@andoroldrefvalue), 1) <> '%'      
      BEGIN      
        SET @andoroldrefvalue = @andoroldrefvalue + '%'      
      END      
      
    IF @SearchIncludes <> N''      
      BEGIN      
        SET @SearchIncludes = @SearchIncludes + N'      
          OR '      
      END      
      
    SET @SearchIncludes = @SearchIncludes + N'SM.[OldRef]							LIKE @andoroldrefvalue'      
  END      
      
IF @andoruser1 = 'OR'      
  BEGIN      
    IF SUBSTRING(@andoruser1value, 1, 1) <> '%'      
      BEGIN      
        SET @andoruser1value = '%' + @andoruser1value       
      END      
      
   IF SUBSTRING(@andoruser1value, LEN(@andoruser1value), 1) <> '%'      
      BEGIN      
        SET @andoruser1value = @andoruser1value + '%'      
      END      
      
    IF @SearchIncludes <> N''      
      BEGIN      
        SET @SearchIncludes = @SearchIncludes + N'      
          OR '      
      END      
      
    SET @SearchIncludes = @SearchIncludes + N'SM.[User1]						  LIKE @andoruser1value'      
  END      
      
IF @andoruser2 = 'OR'      
  BEGIN      
    IF SUBSTRING(@andoruser2value, 1, 1) <> '%'      
      BEGIN      
        SET @andoruser2value = '%' + @andoruser2value       
      END      
      
    IF SUBSTRING(@andoruser2value, LEN(@andoruser2value), 1) <> '%'      
      BEGIN      
        SET @andoruser2value = @andoruser2value + '%'      
      END      
      
    IF @SearchIncludes <> N''      
      BEGIN      
        SET @SearchIncludes = @SearchIncludes + N'      
          OR '      
      END      
      
    SET @SearchIncludes = @SearchIncludes + N'SM.[User2]						   LIKE @andoruser2value'      
  END      
      
IF @andoruser3 = 'OR'      
  BEGIN      
    IF SUBSTRING(@andoruser3value, 1, 1) <> '%'      
      BEGIN      
        SET @andoruser3value = '%' + @andoruser3value       
      END      
      
    IF SUBSTRING(@andoruser3value, LEN(@andoruser3value), 1) <> '%'      
      BEGIN      
        SET @andoruser3value = @andoruser3value + '%'      
      END      
      
    IF @SearchIncludes <> N''      
      BEGIN      
        SET @SearchIncludes = @SearchIncludes + N'      
          OR '      
      END      
      
    SET @SearchIncludes = @SearchIncludes + N'SM.[User3]						   LIKE @andoruser3value'      
  END      
      
IF @andorwtype = 'OR'      
  BEGIN      
    IF @SearchIncludes <> N''      
      BEGIN      
        SET @SearchIncludes = @SearchIncludes + N'      
          OR '      
      END      
      
    SET @SearchIncludes = @SearchIncludes + N'SM.[WType] = @andorwtypevalue'      
  END      
      
IF @andoryourref = 'OR'      
  BEGIN      
    IF SUBSTRING(@andoryourrefvalue, 1, 1) <> '%'      
      BEGIN      
        SET @andoryourrefvalue = '%' + @andoryourrefvalue       
      END      
      
    IF SUBSTRING(@andoryourrefvalue, LEN(@andoryourrefvalue), 1) <> '%'      
      BEGIN      
        SET @andoryourrefvalue = @andoryourrefvalue + '%'      
      END      
      
    IF @SearchIncludes <> N''      
      BEGIN      
        SET @SearchIncludes = @SearchIncludes + N'      
          OR '      
      END      
      
    SET @SearchIncludes = @SearchIncludes + N'SM.[YourRef]							LIKE @andoryourrefvalue'      
  END      
      
--SELECT @SearchIncludes      
      
IF @SearchIncludes <> ''      
  BEGIN      
    SET @NCOMMAND = @NCOMMAND + N'      
     AND (   ' + @SearchIncludes + N')'      
  END      
      
IF @andoraddress = 'AND'      
  BEGIN      
    IF SUBSTRING(@andoraddressvalue, 1, 1) <> '%'      
      BEGIN      
        SET @andoraddressvalue = '%' + @andoraddressvalue       
      END      
      
    IF SUBSTRING(@andoraddressvalue, LEN(@andoraddressvalue), 1) <> '%'      
      BEGIN      
        SET @andoraddressvalue = @andoraddressvalue + '%'      
      END      
      
    SET @andoraddressvalue = @andoraddressvalue COLLATE SQL_Latin1_General_Cp1251_CS_AS      
      
    SET @NCOMMAND = @NCOMMAND + N'      
     AND SM.[NCAddress]             LIKE @andoraddressvalue'      
  END      
      
IF @andorcode = 'AND'      
  BEGIN      
    IF SUBSTRING(@andorcodevalue, 1, 1) <> '%'      
      BEGIN      
        SET @andorcodevalue = '%' + @andorcodevalue       
      END      
      
    IF SUBSTRING(@andorcodevalue, LEN(@andorcodevalue), 1) <> '%'      
      BEGIN      
        SET @andorcodevalue = @andorcodevalue + '%'      
      END      
      
    SET @NCOMMAND = @NCOMMAND + N'      
     AND SM.[Code]                  LIKE @andorcodevalue'      
  END      
      
IF @andordept = 'AND'      
  BEGIN      
    SET @NCOMMAND = @NCOMMAND + N'      
     AND SM.[Dept]              = @andordeptvalue'      
  END      
      
IF @andordescription = 'AND'      
  BEGIN      
    IF SUBSTRING(@andordescriptionvalue, 1, 1) <> '%'      
      BEGIN      
        SET @andordescriptionvalue = '%' + @andordescriptionvalue       
      END      
      
    IF SUBSTRING(@andordescriptionvalue, LEN(@andordescriptionvalue), 1) <> '%'      
      BEGIN      
        SET @andordescriptionvalue = @andordescriptionvalue + '%'      
      END      
      
    SET @andordescriptionvalue = @andordescriptionvalue COLLATE SQL_Latin1_General_Cp1251_CS_AS      
      
    SET @NCOMMAND = @NCOMMAND + N'      
     AND SM.[NCDescription]         LIKE @andordescriptionvalue'      
  END      
      
IF @andorfeecode = 'AND'      
  BEGIN      
    SET @NCOMMAND = @NCOMMAND + N'      
     AND SM.[FECode]                = @andorfeecodevalue'      
  END      
      
IF @andorname = 'AND'      
  BEGIN      
    IF SUBSTRING(@andornamevalue, 1, 1) <> '%'      
      BEGIN      
        SET @andornamevalue = '%' + @andornamevalue       
      END      
      
IF SUBSTRING(@andornamevalue, LEN(@andornamevalue), 1) <> '%'      
      BEGIN      
        SET @andornamevalue = @andornamevalue + '%'      
      END      
      
    SET @andornamevalue = @andornamevalue COLLATE SQL_Latin1_General_Cp1251_CS_AS      
      
    SET @NCOMMAND = @NCOMMAND + N'      
     AND SM.[NCName]                LIKE @andornamevalue'      
  END      
      
IF @andoroldref = 'AND'      
  BEGIN      
    IF SUBSTRING(@andoroldrefvalue, 1, 1) <> '%'      
      BEGIN      
        SET @andoroldrefvalue = '%' + @andoroldrefvalue       
      END      
      
    IF SUBSTRING(@andoroldrefvalue, LEN(@andoroldrefvalue), 1) <> '%'      
      BEGIN      
        SET @andoroldrefvalue = @andoroldrefvalue + '%'      
      END      
      
    SET @NCOMMAND = @NCOMMAND + N'      
     AND SM.[OldRef]                LIKE @andoroldrefvalue'      
  END      
      
IF @andoruser1 = 'AND'      
  BEGIN      
    IF SUBSTRING(@andoruser1value, 1, 1) <> '%'      
      BEGIN      
        SET @andoruser1value = '%' + @andoruser1value       
      END      
      
    IF SUBSTRING(@andoruser1value, LEN(@andoruser1value), 1) <> '%'      
      BEGIN      
        SET @andoruser1value = @andoruser1value + '%'      
      END      
      
    SET @NCOMMAND = @NCOMMAND + N'      
     AND SM.[User1]                 LIKE @andoruser1value'      
  END      
      
IF @andoruser2 = 'AND'      
  BEGIN      
    IF SUBSTRING(@andoruser2value, 1, 1) <> '%'      
      BEGIN      
        SET @andoruser2value = '%' + @andoruser2value       
      END      
      
    IF SUBSTRING(@andoruser2value, LEN(@andoruser2value), 1) <> '%'      
      BEGIN      
        SET @andoruser2value = @andoruser2value + '%'      
      END      
      
    SET @NCOMMAND = @NCOMMAND + N'      
     AND SM.[User2]                 LIKE @andoruser2value'      
  END      
      
IF @andoruser3 = 'AND'      
  BEGIN      
    IF SUBSTRING(@andoruser3value, 1, 1) <> '%'      
      BEGIN      
        SET @andoruser3value = '%' + @andoruser3value       
      END      
      
    IF SUBSTRING(@andoruser3value, LEN(@andoruser3value), 1) <> '%'      
      BEGIN      
        SET @andoruser3value = @andoruser3value + '%'      
      END      
      
    SET @NCOMMAND = @NCOMMAND + N'      
     AND SM.[User3]                 LIKE @andoruser3value'      
  END      
      
IF @andorwtype = 'AND'      
  BEGIN      
    SET @NCOMMAND = @NCOMMAND + N'      
     AND SM.[WType]                 = @andorwtypevalue'      
  END      
      
IF @andoryourref = 'AND'      
  BEGIN      
    IF SUBSTRING(@andoryourrefvalue, 1, 1) <> '%'      
      BEGIN      
        SET @andoryourrefvalue = '%' + @andoryourrefvalue       
      END      
      
    IF SUBSTRING(@andoryourrefvalue, LEN(@andoryourrefvalue), 1) <> '%'      
      BEGIN      
        SET @andoryourrefvalue = @andoryourrefvalue + '%'      
      END      
      
    SET @NCOMMAND = @NCOMMAND + N'      
     AND SM.[YourRef]               LIKE @andoryourrefvalue'      
  END      
      

SET @NCOMMAND = @NCOMMAND + N'      
ORDER BY ' + CASE @orderby      
                  WHEN N'description'      
                       THEN N'CASE WHEN SM.[Description] = ''''      
                   THEN 1      
              ELSE 0 END,      
         SM.[Description] ' + @order + N',      
         SM.[Code] ' + @order      
                  WHEN N'Code'      
                       THEN N'CASE WHEN SM.[Code] = ''''      
                   THEN 1      
              ELSE 0 END,      
         SM.[Code] ' + @order      
                  WHEN N'Name'      
                       THEN N'CASE WHEN SM.[Name] = ''''      
                   THEN 1      
              ELSE 0 END,      
         SM.[Name] ' + @order + N',      
         SM.[Code] ' + @order      
                  WHEN N'Address'      
                       THEN N'CASE WHEN SM.[Address] = ''''      
                   THEN 1      
              ELSE 0 END,      
         SM.[Address] ' + @order + N',      
         SM.[Code] ' + @order      
                  WHEN N'Email'      
                       THEN N'CASE WHEN SM.[Email] = ''''      
                   THEN 1      
              ELSE 0 END,      
         SM.[Email] ' + @order + N',      
         SM.[Code] ' + @order      
                  WHEN N'PhoneNumber'      
                       THEN N'CASE WHEN SM.[PhoneNumber] = ''''      
                   THEN 1      
              ELSE 0 END,      
         SM.[PhoneNumber] ' + @order + N',      
         SM.[Code] ' + @order      
                  WHEN N'user1'      
                      THEN N'CASE WHEN SM.[User1] = ''''      
                   THEN 1      
              ELSE 0 END,      
         SM.[User1] ' + @order + N',      
         SM.[Code] ' + @order      
                  WHEN N'user2'      
                       THEN N'CASE WHEN SM.[User2] = ''''      
                   THEN 1      
              ELSE 0 END,      
         SM.[User2] ' + @order + N',      
         SM.[Code] ' + @order      
    WHEN N'user3'      
                       THEN N'CASE WHEN SM.[User3] = ''''      
                   THEN 1      
              ELSE 0 END,      
         SM.[User3] ' + @order + N',      
         SM.[Code] ' + @order      
                  WHEN N'yourref'      
                       THEN N'CASE WHEN SM.[YourRef] = ''''      
                   THEN 1      
              ELSE 0 END,      
         SM.[YourRef] ' + @order + N',      
         SM.[Code] ' + @order      
                  WHEN N'OldRef'      
                       THEN N'CASE WHEN SM.[OldRef] = ''''      
                   THEN 1      
              ELSE 0 END,      
         SM.[OldRef] ' + @order + N',      
         SM.[Code] ' + @order      
                  WHEN N'FileNum'      
                       THEN N'CASE WHEN SM.[FileNum] = 0      
                   THEN 1      
              ELSE 0 END,      
         SM.[FileNum] ' + @order + N',      
         SM.[Code] ' + @order      
                  WHEN N'department'      
                       THEN N'CASE WHEN SM.[DeptDescription] = ''''      
                   THEN 1      
              ELSE 0 END,      
         SM.[DeptDescription] ' + @order + N',      
         SM.[Code] ' + @order      
                  WHEN N'Dept'      
                       THEN N'CASE WHEN SM.[DeptDescription] = ''''      
                   THEN 1      
              ELSE 0 END,      
         SM.[DeptDescription] ' + @order + N',      
         SM.[Code] ' + @order      
                  WHEN N'worktype'      
                       THEN N'CASE WHEN SM.[WTypeDescription] = ''''      
                   THEN 1      
              ELSE 0 END,      
         SM.[WTypeDescription] ' + @order + N',      
         SM.[Code] ' + @order      
                  WHEN N'FeeCode'      
                       THEN N'CASE WHEN SM.[FECode] = ''''      
                   THEN 2      
              WHEN SM.[FECode] = ''ADM''      
                   THEN 1      
 ELSE 0 END,      
         SM.[FECode] ' + @order + N',      
         SM.[Code] ' + @order      
                  WHEN N'FeeEarner'      
                       THEN N'CASE WHEN SM.[HandlerName] = '''' AND SM.[FECode] = ''''      
                   THEN 3      
              WHEN SM.[HandlerName] = ''''      
                   THEN 2      
              WHEN SM.[HandlerName] = ''Admin''      
                   THEN 1      
              ELSE 0 END,      
         SM.[HandlerName] ' + @order + N',      
         SM.[Code] ' + @order      
                  WHEN N'FileColour_Fld'      
                       THEN N'CASE WHEN SM.[FileColourDescription] = ''''      
                   THEN 1      
              ELSE 0 END,      
         SM.[FileColourDescription] ' + @order + N',      
         SM.[Code] ' + @order      
                  WHEN N'FileColour'      
                       THEN N'CASE WHEN SM.[FileColourDescription] = ''''      
                   THEN 1      
              ELSE 0 END,      
         SM.[FileColourDescription] ' + @order + N',      
         SM.[Code] ' + @order      
                  WHEN N'started'      
                       THEN N'SM.[Started] ' + @order + N',      
         SM.[Code] ' + @order      
                  WHEN N'LastAccessed'      
                       THEN N'CASE WHEN RML.[DateField] IS NULL      
                   THEN 1      
              ELSE 0 END,      
         RML.[DateField] ' + @order + N',      
         SM.[Code] ' + @order      
                  WHEN N'closedate'      
                       THEN N'CASE WHEN SM.[CloseDate] IS NULL      
                   THEN 1      
              ELSE 0 END,      
         SM.[CloseDate] ' + @order + N',      
         SM.[Code] ' + @order      
                  WHEN N'orgcloseddate'      
                       THEN N'CASE WHEN SM.[orgcloseddate] IS NULL      
                   THEN 1      
              ELSE 0 END,      
         SM.[orgcloseddate] ' + @order + N',      
         SM.[Code] ' + @order      
                  WHEN N'casestatusdesc'      
                       THEN N'CASE WHEN SM.[MatterStatusDescription] IS NULL      
                   THEN 1      
              ELSE 0 END,      
         SM.[MatterStatusDescription] ' + @order + N',      
         SM.[Code] ' + @order      
                  ELSE CASE WHEN @Which <> 2      
                                 THEN N'SM.[Code] ' + @order      
                            ELSE N'CASE WHEN RML.[DateField] IS NULL      
                   THEN 1      
              ELSE 0 END,      
         RML.[DateField] DESC,      
         SM.[Code] ' + @order END END      
      
SET @NCOMMAND = @NCOMMAND + N'    
      
  SELECT * FROM(    
  SELECT ROW_NUMBER() OVER(ORDER BY [Res].[id]) AS [RowNumber],    
   [RES].[id],      
         [RES].[Time],      
         [RES].[Code],      
         [RES].[FileColour],      
         [RES].[FileColour_Fld],      
         [RES].[BackgroundColour],      
         [RES].[TextColour],      
         [RES].[Name],      
         [RES].[Description],      
         [RES].[Fe],      
         [RES].[FeeCode],      
         [RES].[FeeEarner],      
         [RES].[User1],      
         [RES].[User2],      
         [RES].[User3],      
         [RES].[Started],      
         [RES].[Dept],      
         [RES].[WorkType],      
         [RES].[Privileges],      
         [RES].[OldRef],      
         [RES].[Address],      
         [RES].[Email],      
         [RES].[Closed],      
         [RES].[OrgCloseDate],      
         [RES].[CloseDate],      
         [RES].[FileRGBColour],      
         [RES].[LastAccessed],      
         [RES].[PhoneNumber],      
         [RES].[FileNum],      
         [RES].[ClientCode],      
         [RES].[DepartmentCode],      
         [RES].[FeeEarnerTeam],      
         [RES].[TeamName],      
         [RES].[YourRef],      
         [RES].[CaseStatusCode],      
         [RES].[CaseStatusDesc]      
    FROM @Result [RES])AS MatterTable WHERE   MatterTable.[RowNumber] BETWEEN @StartRow AND @EndRow ORDER BY MatterTable.[id] ASC      
     
	SELECT  
		COUNT([RES].[id]) AS [TotalRecord]  
	FROM  
		@Result [RES]  
	  
    SELECT
		USERPROMPT1 AS [UserPrompt1],
		USERPROMPT2 AS [UserPrompt2],
		USERPROMPT3 AS [UserPrompt3],
		YourRef     AS [YourRef]
    FROM
	    [Control]'

EXECUTE sp_executesql @NCOMMAND,      
                      N'@HowMany               INT,      
                        @BitWiseID             BIGINT,      
                        @Handler               VARCHAR(10),      
                        @WorkType              VARCHAR(5),      
                        @department            VARCHAR(5),      
                        @filecolour            VARCHAR(3),      
                        @feecode               VARCHAR(10),      
                        @searchtext            VARCHAR(MAX),      
                        @andoraddressvalue     VARCHAR(200),      
                        @andorcodevalue        VARCHAR(200),      
                        @andordeptvalue        VARCHAR(200),      
                        @andordescriptionvalue VARCHAR(200),      
                        @andorfeecodevalue     VARCHAR(200),      
                        @andornamevalue        VARCHAR(200),      
                        @andoroldrefvalue      VARCHAR(200),      
                        @andoruser1value       VARCHAR(200),      
                        @andoruser2value       VARCHAR(200),      
                        @andoruser3value       VARCHAR(200),      
                        @andorwtypevalue       VARCHAR(200),      
                        @andoryourrefvalue     VARCHAR(200),    
					    @StartRow			   INT,    
					    @EndRow                INT,
					    @ColumnNames           VARCHAR(50),
					    @FilterValues          VARCHAR(50)',      
                      @Howmany,      
                      @BitWiseID,      
                      @handler,      
                      @worktype,      
                      @department,      
                      @filecolour,      
                      @feecode,      
                      @searchtext,      
                      @andoraddressvalue,      
                      @andorcodevalue,      
                      @andordeptvalue,      
                      @andordescriptionvalue,      
                      @andorfeecodevalue,      
                      @andornamevalue,      
                      @andoroldrefvalue,      
                      @andoruser1value,      
                      @andoruser2value,      
                      @andoruser3value,      
                      @andorwtypevalue,      
                      @andoryourrefvalue,    
					  @StartRow,    
					  @EndRow,
					  @ColumnNames,
					  @FilterValues    
END

GO

