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 *
* *
* ... *
* ... *
* *
* *
* Retrieve for individual matter, doc folder or action *
* support for paging and sorting *
* - a list of documents found in IManage *
* Example: *
* *
* *
* *
* * *
* 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, ''
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] ''
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] ''
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''
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('''' + REPLACE(@ColumnNames,'','','''') + '''' 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('''' + REPLACE(@FilterValues,''|'','''') + '''' 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('''' + REPLACE((SELECT DISTINCT [Status] FROM @CustomFilter WHERE [Status] IS NOT NULL and [Status] <>''''),'','','''') + '''' 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