IF EXISTS
(SELECT TOP 1 1
FROM SYS.[objects] SO
WHERE SO.[name] = N'BRIEF_GetActionDocumentCount'
AND SO.[type] = 'P')
BEGIN
DROP PROCEDURE [dbo].[BRIEF_GetActionDocumentCount]
END
GO
CREATE PROCEDURE [dbo].[BRIEF_GetActionDocumentCount]
(@ActionID INT)
AS
/*******************************************************************************************************
* *
* Stored Procedure Name : [dbo].[BRIEF_GetActionDocumentCount] *
* *
* Description : To Check Action and Related Document present for brief application *
* *
* *
* Modification History: *
* 2023-09-21 Balamurugan.C Created *
* 2023-11-28 Balamurugan.C Modified - Removed Join Condition *
*******************************************************************************************************/
BEGIN
SET NOCOUNT ON
DECLARE @ResultCount INT
SET @ResultCount = (
SELECT COUNT(*) 'ActionCount'
FROM [dbo].[diary] [DIA]
WHERE [ActionId] = @ActionID
)
IF(@ResultCount > 0)
BEGIN
SELECT 'True'
END
ELSE
BEGIN
SELECT 'False'
END
SET NOCOUNT OFF
END
GO
IF EXISTS
(SELECT TOP 1 1
FROM SYS.[objects] SO
WHERE SO.[name] = N'BRIEF_GetDocumentsIM'
AND SO.[type] = 'P')
BEGIN
DROP PROCEDURE [dbo].[BRIEF_GetDocumentsIM]
END
GO
CREATE PROCEDURE
[dbo].[BRIEF_GetDocumentsIM]
(@matter VARCHAR(20),
@ActionID INT,
@DocFolderID INT,
@IMDocs VARCHAR(MAX),
@PageNo INT = NULL,
@PageSize INT = NULL,
@SearchText VARCHAR(4000) = '',
@FileType VARCHAR(4000) = '',
@SortColumn VARCHAR(50) = '',
@SortDirection VARCHAR(10) = '',
@LoggedInHandler VARCHAR(10) = NULL,
@ColumnNames VARCHAR(50) = '',
@FilterValues VARCHAR(4000) = '',
@DocumentIsNear BIT = 0,
@DocumentStartDate DATE='',
@DocumentEndDate DATE=''
)
AS
/************************************************************************************************************************
* *
* [dbo].[KAAS_GetDocumentsIM] *
* *
* Get list of Documents for the Document Manager *
* *
* Compatibility information - PLEASE update older versions if necessary to ensure the compatible software *
* remains fully functional *
* ***************************************************************************************************** *
* * * *
* * Supersedes: - * *
* * Original: [ky_NETSPGetDocumentsIM] * *
* * First compatible version: 5.7.2.1 * *
* * Last compatible software version: - * *
* * Superseded by: - * *
* * * *
* ***************************************************************************************************** *
* *
* Modification History *
* 2019-04-23 Vinodhan K Created *
* 2019-05-24 Pino Carafa Performance improvements *
* 2019-05-24 Vinodhan Search field have changed as document instead of filepath *
* 2019-05-24 Arun Performance improvements, *
* 2019-09-09 Vinodhan K Search now can be done on Document class description field *
* 2019-09-18 Vinodhan K Total no. of records now will be displayed based on searched text (KEYSAAS - 1035) *
* 2020-02-13 Prabhu.V Modified the SP to Implement filter works with dynamic values *
* 2020-04-23 Prabhu.V Performance Improved *
* 2020-04-29 Prabhu.V Fixed FileType Filter Not working after Improved the SP *
* 2020-05-04 Arun V Document classes datatype size incresed. KEYHM-582 *
* 2020-05-04 Prabhu V Document total reocrd issue fixed *
* 2020-05-20 Prabhu V Custom Filter Condition missed on performance improvement and added it now *
* 2021-06-02 Sadiq Handled seaching for Document near this Funcionlaity in Document manager(11989) *
* 2021-06-07 Sadiq Filter option need to be added when "DOcument Near this" is selected(11988) *
* 2021-06-11 Sadiq Added document name search also now . (12398)
* 2021-06-23 Sadiq Added DiaryDate which was wrongly wrote as Date . (12469)
* 2021-09-03 Aakif Included time for diary action and document *
* 2022-06-03 Vinodhkumar.M INcluded signing Status in the list *
* 2022-09-05 Revathy.D Fixed Random records missing issue *
* 2022-12-12 Balamurugan.C Added LastAccessDate column to the list for Brief Building Purpose *
* 2022-12-19 Sadiq Added LastAccessDate column to the whole SP and fixed error in 15782 *
* 2023-07-07 Revathy Added Source and Attachment fields for filter *
* 2023-08-23 Balamurugan.C Copied entire SP and replicating for Brief Purpose and added OriginalMatterLinkId *
************************************************************************************************************************/
BEGIN
DECLARE @res TABLE
([id] INT identity(1, 1),
[ActionId] INT,
[TrackReference] INT,
[IMDocID] VARCHAR(500),
[IMClass] VARCHAR(20),
[DocClass] VARCHAR(10),
[Document] VARCHAR(2000),
[DiaryDate] DATETIME,
[DocumentDate] DATETIME,
[TYPE] VARCHAR(20),
[Attachments] CHAR(1),
[EMAIL] VARCHAR(1),
[EMAILFROM] VARCHAR(200),
[EMAILSENT] DATETIME,
[EMAILTO] VARCHAR(1000),
[CurrentVersion] INT,
[IsInDocFolder] BIT,
[IsShared] BIT,
[DocumentClassDescription] VARCHAR(40),
[UniqueId] INT,
[FilePath] VARCHAR(500),
[Subject] VARCHAR(2000),
[ShortText] VARCHAR(120),
[NAME] VARCHAR(500),
[ProcessType] VARCHAR(50),
[ACTIONTYPE] VARCHAR(50),
[ActionTypeDescription] VARCHAR(500),
[DOCUMENTSOURCE] VARCHAR(500),
[SigningStatus] VARCHAR(100),
[LASTACCESSDATE] DATETIME,
[OriginalMatterLinkId] INT,
[TotalRecord] INT
)
DECLARE @TOTALRECORDS INT =0;
DECLARE @classes TABLE
([KeyhouseClass] INT,
[IMClass] INT,
[ClassCode] VARCHAR(20),
[ClassDescription] VARCHAR(100))
DECLARE @IMDocTable TABLE
([IMDocID] VARCHAR(500) PRIMARY KEY,
[Description] VARCHAR(2000),
[IMClass] VARCHAR(20),
[IMType] VARCHAR(20),
[EmailFrom] VARCHAR(200),
[EmailTo] VARCHAR(1000),
[EmailSent] DATETIME,
[DocumentDate] DATETIME)
DECLARE @IMDocExpanded TABLE
([id] INT IDENTITY(1, 1) PRIMARY KEY,
[ActionID] INT,
[TrackReference] INT,
[IMDocID] VARCHAR(500),
[IMClass] VARCHAR(20),
[DocClass] VARCHAR(10),
[Document] VARCHAR(2000),
[DiaryDate] DATETIME,
[DocumentDate] DATETIME,
[EmailSent] DATETIME,
[TYPE] VARCHAR(20),
[Attachments] CHAR(1),
[EMAIL] CHAR(1),
[EmailFrom] VARCHAR(200),
[EmailTo] VARCHAR(1000),
[CurrentVersion] INT,
[OriginalMatterLinkId] INT)
DECLARE @FileTypeList TABLE
([FileType] VARCHAR(20) PRIMARY KEY)
INSERT INTO @FileTypeList
SELECT
RTRIM(LTRIM([Item]))
FROM
KAAS_FN_SplitString(@FileType, ',')
DECLARE @idoc INT
/*
DECLARE @StartRow INT = NULL;
DECLARE @EndRow INT = NULL;
IF(((ISNULL(@PageNo, 0)) != 0 AND (ISNULL(@PageSize, 0)) != 0) AND (@PageNo > -1))
BEGIN
SET @PageNo = ISNULL(@PageNo, 0) - 1; -- 0 BASED INDEX
SET @StartRow = ((@PageNo) * @PageSize) + 1;
SET @EndRow = (@StartRow + @PageSize) - 1;
END
*/
IF(@SortColumn = '' OR @SortColumn = NULL)
BEGIN
SET @SortColumn = 'RowNumber'
END
IF(@SortDirection = '' OR @SortDirection = NULL)
BEGIN
SET @SortDirection = 'ASC'
END
DECLARE @uniqueid AS INT=0;
SELECT @uniqueid = [uniqueid]
FROM dbo.[matters]
WHERE [Code] = @matter
DECLARE @CustomFilter TABLE
([DOCCLASS] VARCHAR(2000),
[TYPE] VARCHAR(2000),
[SOURCE] VARCHAR(2000),
[ATTACHMENT] VARCHAR(2000))
DECLARE @DOCCLASSFilterCount INT = 0;
DECLARE @TypeFilterCount INT = 0;
DECLARE @AttachmentFilterCount INT = 0;
DECLARE @SourceFilterCount INT = 0;
IF @ColumnNames <> ''
BEGIN
-- Dynamic Filter By Custome Filter Settings Start
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 -- count(*) over(),
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 = 'DOCCLASS')
insert into @CustomFilter (DOCCLASS,[TYPE],[SOURCE],[ATTACHMENT]) values (@FilterValue,null,null,null)
ELSE IF(@ColumnName = 'Type')
insert into @CustomFilter (DOCCLASS,[TYPE],[SOURCE],[ATTACHMENT]) values (null,@FilterValue,null,null)
ELSE IF(@ColumnName = 'DocumentSource')
insert into @CustomFilter (DOCCLASS,[TYPE],[SOURCE],[ATTACHMENT]) values (null,null,@FilterValue,null)
ELSE IF(@ColumnName = 'Attachment')
insert into @CustomFilter (DOCCLASS,[TYPE],[SOURCE],[ATTACHMENT]) values (null,null,null,@FilterValue)
SET @lptcnt = @lptcnt + 1;
END
SET @DOCCLASSFilterCount = (SELECT COUNT(1) FROM @CustomFilter WHERE DOCCLASS IS NOT NULL AND DOCCLASS <>'')
SET @TypeFilterCount = (SELECT COUNT(1) FROM @CustomFilter WHERE TYPE IS NOT NULL AND TYPE <>'')
SET @SourceFilterCount = (SELECT COUNT(1) FROM @CustomFilter WHERE [SOURCE] IS NOT NULL AND [SOURCE] <>'')
SET @AttachmentFilterCount = (SELECT COUNT(1) FROM @CustomFilter WHERE [ATTACHMENT] IS NOT NULL AND [ATTACHMENT] <>'')
END
IF (ISNULL(@DocFolderID, 0) = 0) AND (ISNULL(@ActionID, 0) = 0)
BEGIN
SET @IMDocs = ISNULL(@IMDocs, '' + CHAR(13) + CHAR(10) + '')
IF (ISNULL(PATINDEX('%' + CHAR(13) + CHAR(10) + @IMDocs
END
BEGIN TRY
EXEC sp_xml_preparedocument @idoc OUTPUT, @IMDocs
END TRY
BEGIN CATCH
EXEC sp_xml_preparedocument @idoc OUTPUT, ''
END CATCH
INSERT
INTO @IMDocTable
([IMDocID],
[Description],
[IMClass],
[IMType],
[EmailFrom],
[EmailTo],
[EmailSent],
[DocumentDate])
SELECT [DAL].[IMDocID],
[DAL].[Description],
[DAL].[IMClass],
[DAL].[IMType],
[DAL].[EmailFrom],
[DAL].[EmailTo],
[DAL].[EmailSent],
[DAL].[DocumentDate]
FROM OPENXML(@idoc, '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]
EXEC sp_xml_removedocument @idoc
INSERT
INTO @res
([ActionId],
[TrackReference],
[IMDocID],
[IMClass],
[DocClass],
[Document],
[DiaryDate],
[DocumentDate],
[EmailSent],
[TYPE],
[Attachments],
[EMAIL],
[EmailFrom],
[EmailTo],
[CurrentVersion],
[IsInDocFolder],
[IsShared])
SELECT convert(INT, NULL) AS [ActionID],
convert(INT, NULL) AS [TrackReference],
[DAL].[IMDocID] AS [IMDocID],
[DAL].[IMClass] AS [IMClass],
convert(VARCHAR(10), NULL) 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],
CONVERT(INT, 1) AS [CurrentVersion],
0 AS [IsInDocFolder],
0 AS [IsShared]
FROM @IMDocTable [DAL]
LEFT OUTER JOIN [dbo].[DAIMXref] [DX]
ON [DX].[IMDocID] = [DAL].[IMDocID]
WHERE [DX].[TrackReference] IS NULL
AND (
@SearchText = ''
OR
([DAL].[Description] LIKE '%' + @SearchText + '%')
OR
(CONVERT(DATETIME, ISNULL([DAL].[DocumentDate], [DAL].[EmailSent])) LIKE '%' + @SearchText + '%')
)
AND
(
ISNULL(@FileType, '') = ''
OR (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 IN (select FileType from @FileTypeList ))
)
--Filterig the value using search
IF @DocumentIsNear=1
BEGIN
SELECT @TOTALRECORDS = Count(1) FROM dbo.[DiaryAttachmentSelector] [DAS]
LEFT JOIN dbo.[DiaryAttachments] [DA] ON [DAS].[TrackReference] = [DA].[TrackReference]
LEFT JOIN dbo.[diary] [DIA] ON [DA].[DiaryID] = [DIA].[ACTIONID] and [DIA].CASECODE = [DA].CASECODE
WHERE [DAS].[CaseCode] = @matter
AND
[DA].DATEENTERED>=@DocumentStartDate
AND
[DA].DATEENTERED<=@DocumentEndDate
AND
(
(CASE WHEN @DOCCLASSFilterCount = 0 THEN 0
WHEN [DA].[DocClass] 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 [DOCCLASS] from @CustomFilter WHERE [DOCCLASS] IS NOT NULL and [DOCCLASS] <>''),',','') + '' AS XML) AS x
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)) FILTERVALUETBLFnl) THEN 0
ELSE 1 END) = 0
AND
(CASE WHEN @TypeFilterCount = 0 THEN 0
WHEN [DA].[TYPE] 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 [TYPE] from @CustomFilter WHERE [TYPE] IS NOT NULL and [TYPE] <>''),',','') + '' AS XML) AS x
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)) FILTERVALUETBLFnl) THEN 0
ELSE 1 END) =0
AND
(CASE WHEN @AttachmentFilterCount = 0 THEN 0
WHEN [DIA].[Attachments] 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 [ATTACHMENT] from @CustomFilter WHERE [ATTACHMENT] IS NOT NULL and [ATTACHMENT] <>''),',','') + '' AS XML) AS x
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)) FILTERVALUETBLFnl) THEN 0
ELSE 1 END) = 0
AND
(CASE WHEN @SourceFilterCount = 0 THEN 0
WHEN (RTRIM(ISNULL([DA].[SOURCE], CASE WHEN [DIA].[ACTIONID] IS NULL THEN 'IManage' ELSE '' END))) 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 [SOURCE] from @CustomFilter WHERE [SOURCE] IS NOT NULL and [SOURCE] <>''),',','') + '' AS XML) AS x
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)) FILTERVALUETBLFnl) THEN 0
ELSE 1 END) = 0
)
SELECT @TOTALRECORDS = Count(1)+ @TOTALRECORDS FROM @res
SELECT
[DAS].[CASECODE],[DAS].[ACTIONID],[DAS].[DiaryDate] [DATE],[DIA].[TxmDate], [DAS].[EMAIL],[DAS].[ATTACHMENTS],[DAS].[EMAILADDRESS],[DAS].[ADDRESSTO],[DAS].[SUBJECT],[DAS].[DisplayText],
[DAS].[PROCESSTYPE],[DAS].[ACTIONTYPE],
[DA].[DIARYID],[DAS].[TrackReference],[DAS].[DocClass],[DA].[DATEENTERED],[DAS].[TYPE],
[DA].[FILEPATH],[DA].[NAME],[DA].[SOURCE],
convert(VARCHAR(500), ISNULL([DX].[IMDocID], '')) AS [IMDocID],
convert(VARCHAR(20), ISNULL([DL].[IMClass], '')) AS [IMClass],
RTRIM(convert(VARCHAR(2000), ISNULL([DL].[Description], ISNULL([DA].[Document], '')))) AS [Document],
CASE WHEN [DAS].[ACTIONTYPE] = 'A'
THEN 'Action'
WHEN [DAS].[ACTIONTYPE] = 'N'
THEN 'Note'
WHEN [DAS].[ACTIONTYPE] = 'P'
THEN 'Appointment'
WHEN [DAS].[ACTIONTYPE] = 'R'
THEN 'Reminder'
WHEN [DAS].[ACTIONTYPE] = 'E'
THEN 'Email'
WHEN [DAS].[ACTIONTYPE] = 'T'
THEN 'Phone Message'
WHEN [DAS].[ACTIONTYPE] = 'D'
THEN 'Dictation'
WHEN [DAS].[ACTIONTYPE] = 'U'
THEN 'Undertaking'
WHEN [DAS].[ACTIONTYPE] = 'S'
THEN 'Statute Date'
WHEN [DAS].[ACTIONTYPE] = 'C'
THEN 'Critical Date'
WHEN [DAS].[ACTIONTYPE] = 'O'
THEN 'Court Date'
WHEN [DAS].[ACTIONTYPE] = 'M'
THEN 'Scanned Post/Mail'
WHEN RTRIM(ISNULL([DAS].[ACTIONTYPE], '')) = ''
THEN CASE WHEN convert(VARCHAR(1), CASE WHEN RTRIM(ISNULL([DAS].[EMAIL], 'N')) = 'Y' THEN 'Y' ELSE 'N' END) = 'Y'
THEN 'Email'
WHEN convert(VARCHAR(500), ISNULL([DX].[IMDocID], '')) <> ''
THEN 'IManage document'
ELSE 'Unknown' END
ELSE 'Action Type: ' + [DAS].[ACTIONTYPE] END AS [ActionTypeDescription],
CASE WHEN convert(VARCHAR(10), ISNULL([DAS].[DocClass], '')) <> ''
THEN RTRIM(ISNULL(DCL.[CLASSDESCRIPTION], ''))
ELSE CASE WHEN [DCL].[ClassCode] = 'ATTNOTES'
THEN 'Attendance Notes'
WHEN [DCL].[ClassCode] = 'BILL'
THEN 'Bill'
WHEN [DCL].[ClassCode] = 'FAX'
THEN 'Fax'
WHEN [DCL].[ClassCode] = 'LEGAL'
THEN 'Legal'
WHEN [DCL].[ClassCode] = 'SCAN'
THEN 'Scan'
WHEN [DCL].[ClassCode] = 'COMPARE'
THEN 'Compare Document'
WHEN [DCL].[ClassCode] = 'DISCUSSION'
THEN 'Discussion'
WHEN [DCL].[ClassCode] = 'DOC'
THEN 'Document'
WHEN [DCL].[ClassCode] = 'E-MAIL'
THEN 'E-Mail'
WHEN [DCL].[ClassCode] = 'EVENT'
THEN 'Event'
WHEN [DCL].[ClassCode] = 'LETTER'
THEN 'Letter'
WHEN [DCL].[ClassCode] = 'MEMO'
THEN 'Memo'
WHEN [DCL].[ClassCode] = 'PAGE_ICON'
THEN 'Page Icon'
WHEN [DCL].[ClassCode] = 'TASK'
THEN 'Task'
WHEN [DCL].[ClassCode] = 'TEXT'
THEN 'Text File'
WHEN [DCL].[ClassCode] = 'WEBDOC'
THEN 'InfoLink Web Page'
ELSE ISNULL([DCL].[CLASSCODE], '') END END AS [DocumentClassDescription],
[DAS].[SigningStatus] AS [SigningStatus],
[DA].[LASTACCESSDATE] AS [LASTACCESSDATE],
[DAI].OriginalUniqueID AS [OriginalMatterLinkId]
--,Count(1) Over() as TotalRowsCount
INTO #DiaryTablesDoc
FROM [DiaryAttachmentSelector] [DAS]
LEFT JOIN dbo.[DiaryAttachments] [DA] ON [DAS].[TrackReference] = [DA].[TrackReference]
LEFT JOIN dbo.[diary] [DIA] ON [DA].[DiaryID] = [DIA].[ACTIONID] and [DIA].CASECODE = [DA].CASECODE
LEFT OUTER JOIN [dbo].[DAIMXRef] [DX]
LEFT OUTER JOIN @IMDocTable [DL]
ON [DL].[IMDocID] = [DX].[IMDocID]
ON [DX].[TrackReference] = [DA].[TrackReference]
LEFT OUTER JOIN [dbo].[DocumentClasses] DCL
ON convert(VARCHAR(10), ISNULL([DA].[DocClass], '')) <> ''
AND DCL.[CLASSCODE] = CASE WHEN convert(VARCHAR(10), ISNULL([DA].[DocClass], '')) = '' THEN convert(VARCHAR(20), ISNULL([DL].[IMClass], ''))
ELSE convert(VARCHAR(10), ISNULL([DA].[DocClass], ''))
END
LEFT JOIN [dbo].[diaryAttachmentOriginalmatterlinks] [DAI] ON [DAI].[TrackReference] = [DAS].[TrackReference]
WHERE [DAS].[CaseCode] = @matter
AND
[DA].DATEENTERED>=@DocumentStartDate
AND
[DA].DATEENTERED<=@DocumentEndDate
AND
(
(CASE WHEN @DOCCLASSFilterCount = 0 THEN 0
WHEN [DAS].[DocClass] 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 [DOCCLASS] from @CustomFilter WHERE [DOCCLASS] IS NOT NULL and [DOCCLASS] <>''),',','') + '' AS XML) AS x
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)) FILTERVALUETBLFnl) THEN 0
ELSE 1 END) = 0
AND
(CASE WHEN @TypeFilterCount = 0 THEN 0
WHEN [DAS].[TYPE] 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 [TYPE] from @CustomFilter WHERE [TYPE] IS NOT NULL and [TYPE] <>''),',','') + '' AS XML) AS x
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)) FILTERVALUETBLFnl) THEN 0
ELSE 1 END) =0
AND
(CASE WHEN @AttachmentFilterCount = 0 OR [DAS].[ATTACHMENTS] 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 [ATTACHMENT] from @CustomFilter WHERE [ATTACHMENT] IS NOT NULL and [ATTACHMENT] <>''),',','') + '' AS XML) AS x
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)) FILTERVALUETBLFnl) THEN 0
ELSE 1 END) = 0
AND
(CASE WHEN @SourceFilterCount = 0 OR [DA].[SOURCE] 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 [SOURCE] from @CustomFilter WHERE [SOURCE] IS NOT NULL and [SOURCE] <>''),',','') + '' AS XML) AS x
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)) FILTERVALUETBLFnl) THEN 0
ELSE 1 END) = 0
)
order by [DA].DATEENTERED
OFFSET @PageSize * (@PageNo - 1) ROWS
FETCH NEXT @PageSize ROWS ONLY OPTION (RECOMPILE);
INSERT
INTO @res
([ActionId],
[TrackReference],
[IMDocID],
[IMClass],
[DocClass],
[Document],
[DiaryDate],
[DocumentDate],
[EmailSent],
[TYPE],
[Attachments],
[EMAIL],
[EmailFrom],
[EmailTo],
[CurrentVersion],
[IsInDocFolder],
[IsShared],
[DocumentClassDescription],
[UniqueId],
[FilePath],
[Subject],
[ShortText],
[NAME],
[ProcessType],
[ACTIONTYPE],
[ActionTypeDescription],
[DOCUMENTSOURCE]
,[SigningStatus]
,[LASTACCESSDATE],
[OriginalMatterLinkId]
-- ,[TotalRecord]
)
SELECT convert(INT, [DIA].[DIARYID]) AS [ActionID],
convert(INT, [DIA].[TrackReference]) AS [TrackReference],
[DIA].[IMDocID],
[DIA].[IMClass],
convert(VARCHAR(10), ISNULL([DIA].[DocClass], '')) AS [DocClass],
[DIA].[Document],
convert(DATETIME, [DIA].[DATE]) As [DiaryDate],
convert(DATETIME, [DIA].[DATEENTERED]) AS [DocumentDate],
convert(DATETIME, ISNULL([DIA].[TxmDate], [DIA].[Date])) As [EMAILSENT],
convert(VARCHAR(20), UPPER(RTRIM(ISNULL([DIA].[TYPE], '')))) AS [TYPE],
-- CASE WHEN [DIA].[ATTACHMENTS] = 'Y' THEN 'Y' ELSE 'N' END AS [Attachments],
(CASE WHEN ISNULL( convert(VARCHAR(1), CASE WHEN RTRIM(ISNULL([DIA].[EMAIL], 'N')) = 'Y' THEN 'Y' ELSE 'N' END), '') = 'Y'
AND convert(INT, [DIA].[TrackReference]) = [OMS].[TrackReference]
THEN (CASE WHEN [DIA].[ATTACHMENTS] = 'Y' THEN 'Y' ELSE 'N' END )
WHEN ISNULL(convert(VARCHAR(20), UPPER(RTRIM(ISNULL([DIA].[TYPE], '')))), '') = 'MSG'
AND convert(INT, [DIA].[TrackReference]) = [OMS].[TrackReference]
THEN (CASE WHEN [DIA].[ATTACHMENTS] = 'Y' THEN 'Y' ELSE 'N' END)
ELSE 'N' END) AS [Attachments],
-- convert(VARCHAR(1), CASE WHEN RTRIM(ISNULL([DIA].[EMAIL], 'N')) = 'Y' THEN 'Y' ELSE 'N' END) AS [EMAIL],
(CASE WHEN convert(VARCHAR(1), CASE WHEN RTRIM(ISNULL([DIA].[EMAIL], 'N')) = 'Y' THEN 'Y' ELSE 'N' END) = 'Y'
AND convert(INT, [DIA].[TrackReference]) = [OMS].[TrackReference]
THEN 'Y'
WHEN ISNULL(convert(VARCHAR(20), UPPER(RTRIM(ISNULL([DIA].[TYPE], '')))), '') = 'MSG'
AND convert(INT, [DIA].[TrackReference]) = [OMS].[TrackReference]
THEN 'Y'
ELSE 'N' END) AS [EMAIL],
-- convert(VARCHAR(200), RTRIM(ISNULL([DIA].[EMAILADDRESS], ''))) AS [EMAILFROM],
RTRIM(ISNULL(CONVERT(VARCHAR(200), SUBSTRING(convert(VARCHAR(200), RTRIM(ISNULL([DIA].[EMAILADDRESS], ''))), 1, 200)), '')) AS [EMAILFROM],
--RTRIM(ISNULL(CONVERT(VARCHAR(1000), [DIA].[ADDRESSTO]), '')) AS [EMAILTO],
RTRIM(ISNULL(CONVERT(VARCHAR(1000), SUBSTRING(RTRIM(ISNULL(CONVERT(VARCHAR(1000), [DIA].[ADDRESSTO]), '')), 1, 1000)), '')) AS [EMAILTO],
[DV].[CurrentVersion],
[IND].[InDocFolder],
[SHD].[IsShared],
[DIA].[DocumentClassDescription],
@uniqueid AS uniqueid,
CASE WHEN [UNC].[UNC] IS NULL THEN RTRIM(ISNULL([DIA].[FILEPATH], ''))
ELSE [UNC].[UNC] + SUBSTRING(RTRIM(ISNULL([DIA].[FILEPATH], '')), 3, LEN(RTRIM(ISNULL([DIA].[FILEPATH], ''))) - 2) END AS [FILEPATH],
-- [RES].[EMAILSENT],
RTRIM(ISNULL(CONVERT(VARCHAR(1000),
SUBSTRING(RTRIM(ISNULL(CONVERT(VARCHAR(2000),
[DIA].[SUBJECT]),'')),
1,
1000)),
'')) AS [SUBJECT],
RTRIM(ISNULL(CONVERT(VARCHAR(120),
SUBSTRING(RTRIM(ISNULL(CONVERT(VARCHAR(120),
[DIA].[DisplayText]),'')),
1,
120)),
'')) AS [ShortText],
RTRIM(ISNULL([DIA].[NAME], '')) AS [NAME],
RTRIM(ISNULL([DIA].[PROCESSTYPE], '')) AS [PROCESSTYPE],
RTRIM(ISNULL([DIA].[ACTIONTYPE], '')) AS [ACTIONTYPE],
[DIA].[ActionTypeDescription],
RTRIM(ISNULL([DIA].[SOURCE], CASE WHEN [DIA].[ACTIONID] IS NULL THEN 'IManage' ELSE '' END)) AS [DOCUMENTSOURCE]
--,TotalRowsCount
,[DAS].[SigningStatus]
,CONVERT(DATETIME, [DIA].[LASTACCESSDATE]) AS [LASTACCESSDATE],
[DAI].OriginalUniqueID AS [OriginalMatterLinkId]
FROM #DiaryTablesDoc [DIA]
-- INNER JOIN dbo.[matters] [MAT] ON [MAT].[CODE] = [DIA].[CASECODE]
LEFT JOIN [DiaryAttachmentSelector] [DAS] ON [DAS].[TrackReference] = [DIA].[TrackReference]
CROSS APPLY (SELECT CASE WHEN COUNT(1) = 0
THEN CONVERT(BIT, 0)
ELSE CONVERT(BIT, 1) END AS [InDocFolder]
FROM [dbo].[DocFolderDocuments] [DFD]
WHERE [DFD].[TrackReference] = [DIA].[TrackReference]) [IND]
CROSS APPLY (SELECT CASE WHEN COUNT(1) = 0
THEN CONVERT(BIT, 0)
ELSE CONVERT(BIT, 1) END AS [IsShared]
FROM [dbo].[FileSharingDocuments] [FSD]
INNER JOIN [dbo].[FileSharingMapping] [FSM]
INNER JOIN [dbo].[FileSharingDocFolders] [FSDF]
ON [FSDF].[DocFolderID] = [FSM].[FileSharingDocFoldersID]
AND [FSDF].[Shared] = 1
ON [FSM].[id] = [FSD].[FileSharingMappingID]
WHERE [FSD].[TrackReference] = [DIA].[TrackReference]) [SHD]
CROSS APPLY (SELECT ISNULL(MAX([DAV].[Version]), 0) + 1 AS [CurrentVersion]
FROM [dbo].[DiaryAttachmentVersioning] [DAV]
WHERE TRACKREFERENCE = [DIA].[TRACKREFERENCE]) [DV]
LEFT OUTER JOIN [dbo].[UNCAlias] [UNC]
ON SUBSTRING([DIA].[FilePath], 2, 1) = ':'
AND [UNC].[Drive] = SUBSTRING([DIA].[FilePath], 1, 1)
OUTER APPLY ( SELECT TOP 1
[MST].[TrackReference] AS [TrackReference],
[MST].[Type] AS [Type]
FROM #DiaryTablesDoc [MST]
WHERE [MST].[diaryid] = [DIA].[ActionID]
ORDER BY CASE WHEN [MST].Type = 'MSG'
THEN 0
ELSE 1 END,
[MST].[TrackReference]) [OMS]
LEFT JOIN [dbo].[diaryAttachmentOriginalmatterlinks] [DAI] ON [DAI].[TrackReference] = [DIA].[TrackReference]
END
ELSE
BEGIN
SELECT @TOTALRECORDS = Count(1) FROM
dbo.[DiaryAttachmentSelector] [DAS]
LEFT JOIN dbo.[DiaryAttachments] [DA] ON [DAS].[TrackReference] = [DA].[TrackReference]
LEFT JOIN dbo.[diary] [DIA] ON [DA].[DiaryID] = [DIA].[ACTIONID] and [DIA].CASECODE = [DA].CASECODE
WHERE [DAS].[CaseCode] = @matter
AND
(
CASE WHEN @SearchText = '' THEN 1
WHEN
([DA].FILEPATH LIKE '%' + @SearchText + '%')
OR
(RTRIM(convert(VARCHAR(2000), ISNULL([DA].[Document], ''))) LIKE '%' + @SearchText + '%')
OR
([DA].[DATEENTERED] LIKE '%' + @SearchText + '%')
OR
([DA].[TYPE] LIKE '%' + @SearchText + '%')
OR
([DA].[DocClass] LIKE '%' + @SearchText + '%')
THEN 1
ELSE 0
END = 1
)
AND
(
ISNULL(@FileType, '') = ''
OR ([DA].[TYPE] IN (select FileType from @FileTypeList ))
)
AND
(
(CASE WHEN @DOCCLASSFilterCount = 0 THEN 0
WHEN [DA].[DocClass] 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 [DOCCLASS] from @CustomFilter WHERE [DOCCLASS] IS NOT NULL and [DOCCLASS] <>''),',','') + '' AS XML) AS x
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)) FILTERVALUETBLFnl) THEN 0
ELSE 1 END) = 0
AND
(CASE WHEN @TypeFilterCount = 0 THEN 0
WHEN [DA].[TYPE] 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 [TYPE] from @CustomFilter WHERE [TYPE] IS NOT NULL and [TYPE] <>''),',','') + '' AS XML) AS x
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)) FILTERVALUETBLFnl) THEN 0
ELSE 1 END) =0
AND
(CASE WHEN @AttachmentFilterCount = 0 THEN 0
WHEN [DAS].[Attachments] 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 [ATTACHMENT] from @CustomFilter WHERE [ATTACHMENT] IS NOT NULL and [ATTACHMENT] <>''),',','') + '' AS XML) AS x
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)) FILTERVALUETBLFnl) THEN 0
ELSE 1 END) = 0
AND
(CASE WHEN @SourceFilterCount = 0 THEN 0
WHEN (RTRIM(ISNULL([DA].[SOURCE], CASE WHEN [DAS].[ACTIONID] IS NULL THEN 'IManage' ELSE '' END))) 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 [SOURCE] from @CustomFilter WHERE [SOURCE] IS NOT NULL and [SOURCE] <>''),',','') + '' AS XML) AS x
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)) FILTERVALUETBLFnl) THEN 0
ELSE 1 END) = 0
)
SELECT @TOTALRECORDS = Count(1)+ @TOTALRECORDS FROM @res
SELECT
[DAS].[CASECODE],[DAS].[ACTIONID],[DAS].[DiaryDate] [DATE],[DIA].[TxmDate], [DAS].[EMAIL],[DAS].[ATTACHMENTS],[DAS].[EMAILADDRESS],[DAS].[ADDRESSTO],[DAS].[SUBJECT],[DAS].[DisplayText],
[DAS].[PROCESSTYPE],[DAS].[ACTIONTYPE],
[DA].[DIARYID],[DAS].[TrackReference],[DAS].[DocClass],[DA].[DATEENTERED],[DAS].[TYPE],
[DA].[FILEPATH],[DA].[NAME],[DA].[SOURCE],
convert(VARCHAR(500), ISNULL([DX].[IMDocID], '')) AS [IMDocID],
convert(VARCHAR(20), ISNULL([DL].[IMClass], '')) AS [IMClass],
RTRIM(convert(VARCHAR(2000), ISNULL([DL].[Description], ISNULL([DA].[Document], '')))) AS [Document],
CASE WHEN [DAS].[ACTIONTYPE] = 'A'
THEN 'Action'
WHEN [DAS].[ACTIONTYPE] = 'N'
THEN 'Note'
WHEN [DAS].[ACTIONTYPE] = 'P'
THEN 'Appointment'
WHEN [DAS].[ACTIONTYPE] = 'R'
THEN 'Reminder'
WHEN [DAS].[ACTIONTYPE] = 'E'
THEN 'Email'
WHEN [DAS].[ACTIONTYPE] = 'T'
THEN 'Phone Message'
WHEN [DAS].[ACTIONTYPE] = 'D'
THEN 'Dictation'
WHEN [DAS].[ACTIONTYPE] = 'U'
THEN 'Undertaking'
WHEN [DAS].[ACTIONTYPE] = 'S'
THEN 'Statute Date'
WHEN [DAS].[ACTIONTYPE] = 'C'
THEN 'Critical Date'
WHEN [DAS].[ACTIONTYPE] = 'O'
THEN 'Court Date'
WHEN [DAS].[ACTIONTYPE] = 'M'
THEN 'Scanned Post/Mail'
WHEN RTRIM(ISNULL([DAS].[ACTIONTYPE], '')) = ''
THEN CASE WHEN convert(VARCHAR(1), CASE WHEN RTRIM(ISNULL([DAS].[EMAIL], 'N')) = 'Y' THEN 'Y' ELSE 'N' END) = 'Y'
THEN 'Email'
WHEN convert(VARCHAR(500), ISNULL([DX].[IMDocID], '')) <> ''
THEN 'IManage document'
ELSE 'Unknown' END
ELSE 'Action Type: ' + [DAS].[ACTIONTYPE] END AS [ActionTypeDescription],
CASE WHEN convert(VARCHAR(10), ISNULL([DAS].[DocClass], '')) <> ''
THEN RTRIM(ISNULL(DCL.[CLASSDESCRIPTION], ''))
ELSE CASE WHEN [DCL].[ClassCode] = 'ATTNOTES'
THEN 'Attendance Notes'
WHEN [DCL].[ClassCode] = 'BILL'
THEN 'Bill'
WHEN [DCL].[ClassCode] = 'FAX'
THEN 'Fax'
WHEN [DCL].[ClassCode] = 'LEGAL'
THEN 'Legal'
WHEN [DCL].[ClassCode] = 'SCAN'
THEN 'Scan'
WHEN [DCL].[ClassCode] = 'COMPARE'
THEN 'Compare Document'
WHEN [DCL].[ClassCode] = 'DISCUSSION'
THEN 'Discussion'
WHEN [DCL].[ClassCode] = 'DOC'
THEN 'Document'
WHEN [DCL].[ClassCode] = 'E-MAIL'
THEN 'E-Mail'
WHEN [DCL].[ClassCode] = 'EVENT'
THEN 'Event'
WHEN [DCL].[ClassCode] = 'LETTER'
THEN 'Letter'
WHEN [DCL].[ClassCode] = 'MEMO'
THEN 'Memo'
WHEN [DCL].[ClassCode] = 'PAGE_ICON'
THEN 'Page Icon'
WHEN [DCL].[ClassCode] = 'TASK'
THEN 'Task'
WHEN [DCL].[ClassCode] = 'TEXT'
THEN 'Text File'
WHEN [DCL].[ClassCode] = 'WEBDOC'
THEN 'InfoLink Web Page'
ELSE ISNULL([DCL].[CLASSCODE], '') END END AS [DocumentClassDescription]
--,Count(1) Over() as TotalRowsCount
,[DAS].[SigningStatus]
,[DA].[LASTACCESSDATE] AS [LASTACCESSDATE]
,[DAI].OriginalUniqueID AS [OriginalMatterLinkId]
INTO #DiaryTables
FROM [DiaryAttachmentSelector] [DAS]
LEFT JOIN dbo.[DiaryAttachments] [DA] ON [DAS].[TrackReference] = [DA].[TrackReference]
LEFT JOIN dbo.[diary] [DIA] ON [DA].[DiaryID] = [DIA].[ACTIONID] and [DIA].CASECODE = [DA].CASECODE
LEFT OUTER JOIN [dbo].[DAIMXRef] [DX]
LEFT OUTER JOIN @IMDocTable [DL]
ON [DL].[IMDocID] = [DX].[IMDocID]
ON [DX].[TrackReference] = [DA].[TrackReference]
LEFT OUTER JOIN [dbo].[DocumentClasses] DCL
ON convert(VARCHAR(10), ISNULL([DA].[DocClass], '')) <> ''
AND DCL.[CLASSCODE] = CASE WHEN convert(VARCHAR(10), ISNULL([DA].[DocClass], '')) = '' THEN convert(VARCHAR(20), ISNULL([DL].[IMClass], ''))
ELSE convert(VARCHAR(10), ISNULL([DA].[DocClass], ''))
END
LEFT JOIN [dbo].[diaryAttachmentOriginalmatterlinks] [DAI] ON [DAS].[TrackReference] = [DAI].[TrackReference]
WHERE [DAS].[CaseCode] = @matter
AND
(
CASE WHEN @SearchText = '' THEN 1
WHEN
([DA].FILEPATH LIKE '%' + @SearchText + '%')
OR
(RTRIM(convert(VARCHAR(2000), ISNULL([DL].[Description], ISNULL([DA].[Document], '')))) LIKE '%' + @SearchText + '%')
OR
([DA].[DATEENTERED] LIKE '%' + @SearchText + '%')
OR
([DA].[TYPE] LIKE '%' + @SearchText + '%')
OR
([DA].[DocClass] LIKE '%' + @SearchText + '%')
THEN 1
ELSE 0
END = 1
)
AND
(
ISNULL(@FileType, '') = ''
OR ([DA].[TYPE] IN (select FileType from @FileTypeList ))
)
AND
(
(CASE WHEN @DOCCLASSFilterCount = 0 THEN 0
WHEN [DA].[DocClass] 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 [DOCCLASS] from @CustomFilter WHERE [DOCCLASS] IS NOT NULL and [DOCCLASS] <>''),',','') + '' AS XML) AS x
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)) FILTERVALUETBLFnl) THEN 0
ELSE 1 END) = 0
AND
(CASE WHEN @TypeFilterCount = 0 THEN 0
WHEN [DA].[TYPE] 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 [TYPE] from @CustomFilter WHERE [TYPE] IS NOT NULL and [TYPE] <>''),',','') + '' AS XML) AS x
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)) FILTERVALUETBLFnl) THEN 0
ELSE 1 END) =0
AND
(CASE WHEN @AttachmentFilterCount = 0 OR [DAS].[ATTACHMENTS] 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 [ATTACHMENT] from @CustomFilter WHERE [ATTACHMENT] IS NOT NULL and [ATTACHMENT] <>''),',','') + '' AS XML) AS x
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)) FILTERVALUETBLFnl) THEN 0
ELSE 1 END) = 0
AND
(CASE WHEN @SourceFilterCount = 0 OR [DA].[SOURCE] 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 [SOURCE] from @CustomFilter WHERE [SOURCE] IS NOT NULL and [SOURCE] <>''),',','') + '' AS XML) AS x
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)) FILTERVALUETBLFnl) THEN 0
ELSE 1 END) = 0
)
ORDER BY
CASE WHEN @SortDirection = 'ASC' AND @SortColumn!='RowNumber' THEN
CASE @SortColumn
WHEN 'Type' THEN convert(VARCHAR(20), UPPER(RTRIM(ISNULL([DA].[TYPE], ''))))
END
END,
CASE WHEN @SortDirection = 'DESC' AND @SortColumn!='RowNumber' THEN
CASE @SortColumn
WHEN 'Type' THEN convert(VARCHAR(20), UPPER(RTRIM(ISNULL([DA].[TYPE], ''))))
END
END DESC,
CASE WHEN @SortDirection = 'ASC' AND @SortColumn!='RowNumber' THEN
CASE @SortColumn
WHEN 'Document' THEN RTRIM(convert(VARCHAR(2000), ISNULL([DL].[Description], ISNULL([DA].[Document], ''))))
END
END,
CASE WHEN @SortDirection = 'DESC' AND @SortColumn!='RowNumber' THEN
CASE @SortColumn
WHEN 'Document' THEN RTRIM(convert(VARCHAR(2000), ISNULL([DL].[Description], ISNULL([DA].[Document], ''))))
END
END DESC,
CASE WHEN @SortDirection = 'ASC' AND @SortColumn!='RowNumber' THEN
CASE @SortColumn
WHEN 'DocumentClassDescription' THEN CASE WHEN convert(VARCHAR(10), ISNULL([DA].[DocClass], '')) <> ''
THEN RTRIM(ISNULL(DCL.[CLASSDESCRIPTION], ''))
ELSE CASE WHEN [DCL].[ClassCode] = 'ATTNOTES'
THEN 'Attendance Notes'
WHEN [DCL].[ClassCode] = 'BILL'
THEN 'Bill'
WHEN [DCL].[ClassCode] = 'FAX'
THEN 'Fax'
WHEN [DCL].[ClassCode] = 'LEGAL'
THEN 'Legal'
WHEN [DCL].[ClassCode] = 'SCAN'
THEN 'Scan'
WHEN [DCL].[ClassCode] = 'COMPARE'
THEN 'Compare Document'
WHEN [DCL].[ClassCode] = 'DISCUSSION'
THEN 'Discussion'
WHEN [DCL].[ClassCode] = 'DOC'
THEN 'Document'
WHEN [DCL].[ClassCode] = 'E-MAIL'
THEN 'E-Mail'
WHEN [DCL].[ClassCode] = 'EVENT'
THEN 'Event'
WHEN [DCL].[ClassCode] = 'LETTER'
THEN 'Letter'
WHEN [DCL].[ClassCode] = 'MEMO'
THEN 'Memo'
WHEN [DCL].[ClassCode] = 'PAGE_ICON'
THEN 'Page Icon'
WHEN [DCL].[ClassCode] = 'TASK'
THEN 'Task'
WHEN [DCL].[ClassCode] = 'TEXT'
THEN 'Text File'
WHEN [DCL].[ClassCode] = 'WEBDOC'
THEN 'InfoLink Web Page'
ELSE ISNULL([DCL].[CLASSCODE], '') END END
END
END,
CASE WHEN @SortDirection = 'DESC' AND @SortColumn!='RowNumber' THEN
CASE @SortColumn
WHEN 'DocumentClassDescription' THEN CASE WHEN convert(VARCHAR(10), ISNULL([DA].[DocClass], '')) <> ''
THEN RTRIM(ISNULL(DCL.[CLASSDESCRIPTION], ''))
ELSE CASE WHEN [DCL].[ClassCode] = 'ATTNOTES'
THEN 'Attendance Notes'
WHEN [DCL].[ClassCode] = 'BILL'
THEN 'Bill'
WHEN [DCL].[ClassCode] = 'FAX'
THEN 'Fax'
WHEN [DCL].[ClassCode] = 'LEGAL'
THEN 'Legal'
WHEN [DCL].[ClassCode] = 'SCAN'
THEN 'Scan'
WHEN [DCL].[ClassCode] = 'COMPARE'
THEN 'Compare Document'
WHEN [DCL].[ClassCode] = 'DISCUSSION'
THEN 'Discussion'
WHEN [DCL].[ClassCode] = 'DOC'
THEN 'Document'
WHEN [DCL].[ClassCode] = 'E-MAIL'
THEN 'E-Mail'
WHEN [DCL].[ClassCode] = 'EVENT'
THEN 'Event'
WHEN [DCL].[ClassCode] = 'LETTER'
THEN 'Letter'
WHEN [DCL].[ClassCode] = 'MEMO'
THEN 'Memo'
WHEN [DCL].[ClassCode] = 'PAGE_ICON'
THEN 'Page Icon'
WHEN [DCL].[ClassCode] = 'TASK'
THEN 'Task'
WHEN [DCL].[ClassCode] = 'TEXT'
THEN 'Text File'
WHEN [DCL].[ClassCode] = 'WEBDOC'
THEN 'InfoLink Web Page'
ELSE ISNULL([DCL].[CLASSCODE], '') END END
END
END DESC,
CASE WHEN @SortDirection = 'ASC' AND @SortColumn!='RowNumber' THEN
CASE @SortColumn
WHEN 'DocumentDate' THEN convert(DATETIME, [DA].[DATEENTERED])
END
END,
CASE WHEN @SortDirection = 'DESC' AND @SortColumn!='RowNumber' THEN
CASE @SortColumn
WHEN 'DocumentDate' THEN convert(DATETIME, [DA].[DATEENTERED])
END
END DESC, [DA].[TrackReference] DESC
OFFSET @PageSize * (@PageNo - 1) ROWS
FETCH NEXT @PageSize ROWS ONLY OPTION (RECOMPILE);
INSERT
INTO @res
([ActionId],
[TrackReference],
[IMDocID],
[IMClass],
[DocClass],
[Document],
[DiaryDate],
[DocumentDate],
[EmailSent],
[TYPE],
[Attachments],
[EMAIL],
[EmailFrom],
[EmailTo],
[CurrentVersion],
[IsInDocFolder],
[IsShared],
[DocumentClassDescription],
[UniqueId],
[FilePath],
[Subject],
[ShortText],
[NAME],
[ProcessType],
[ACTIONTYPE],
[ActionTypeDescription],
[DOCUMENTSOURCE],
[SigningStatus],
[LASTACCESSDATE],
[OriginalMatterLinkId]
-- ,[TotalRecord]
)
SELECT convert(INT, [DIA].[DIARYID]) AS [ActionID],
convert(INT, [DIA].[TrackReference]) AS [TrackReference],
[DIA].[IMDocID],
[DIA].[IMClass],
convert(VARCHAR(10), ISNULL([DIA].[DocClass], '')) AS [DocClass],
[DIA].[Document],
convert(DATETIME, [DIA].[DATE]) As [DiaryDate],
convert(DATETIME, [DIA].[DATEENTERED]) AS [DocumentDate],
convert(DATETIME, ISNULL([DIA].[TxmDate], [DIA].[Date])) As [EMAILSENT],
convert(VARCHAR(20), UPPER(RTRIM(ISNULL([DIA].[TYPE], '')))) AS [TYPE],
-- CASE WHEN [DIA].[ATTACHMENTS] = 'Y' THEN 'Y' ELSE 'N' END AS [Attachments],
(CASE WHEN ISNULL( convert(VARCHAR(1), CASE WHEN RTRIM(ISNULL([DIA].[EMAIL], 'N')) = 'Y' THEN 'Y' ELSE 'N' END), '') = 'Y'
AND convert(INT, [DIA].[TrackReference]) = [OMS].[TrackReference]
THEN (CASE WHEN [DIA].[ATTACHMENTS] = 'Y' THEN 'Y' ELSE 'N' END )
WHEN ISNULL(convert(VARCHAR(20), UPPER(RTRIM(ISNULL([DIA].[TYPE], '')))), '') = 'MSG'
AND convert(INT, [DIA].[TrackReference]) = [OMS].[TrackReference]
THEN (CASE WHEN [DIA].[ATTACHMENTS] = 'Y' THEN 'Y' ELSE 'N' END)
ELSE 'N' END) AS [Attachments],
-- convert(VARCHAR(1), CASE WHEN RTRIM(ISNULL([DIA].[EMAIL], 'N')) = 'Y' THEN 'Y' ELSE 'N' END) AS [EMAIL],
(CASE WHEN convert(VARCHAR(1), CASE WHEN RTRIM(ISNULL([DIA].[EMAIL], 'N')) = 'Y' THEN 'Y' ELSE 'N' END) = 'Y'
AND convert(INT, [DIA].[TrackReference]) = [OMS].[TrackReference]
THEN 'Y'
WHEN ISNULL(convert(VARCHAR(20), UPPER(RTRIM(ISNULL([DIA].[TYPE], '')))), '') = 'MSG'
AND convert(INT, [DIA].[TrackReference]) = [OMS].[TrackReference]
THEN 'Y'
ELSE 'N' END) AS [EMAIL],
-- convert(VARCHAR(200), RTRIM(ISNULL([DIA].[EMAILADDRESS], ''))) AS [EMAILFROM],
RTRIM(ISNULL(CONVERT(VARCHAR(200), SUBSTRING(convert(VARCHAR(200), RTRIM(ISNULL([DIA].[EMAILADDRESS], ''))), 1, 200)), '')) AS [EMAILFROM],
--RTRIM(ISNULL(CONVERT(VARCHAR(1000), [DIA].[ADDRESSTO]), '')) AS [EMAILTO],
RTRIM(ISNULL(CONVERT(VARCHAR(1000), SUBSTRING(RTRIM(ISNULL(CONVERT(VARCHAR(1000), [DIA].[ADDRESSTO]), '')), 1, 1000)), '')) AS [EMAILTO],
[DV].[CurrentVersion],
[IND].[InDocFolder],
[SHD].[IsShared],
[DIA].[DocumentClassDescription],
@uniqueid AS uniqueid,
CASE WHEN [UNC].[UNC] IS NULL THEN RTRIM(ISNULL([DIA].[FILEPATH], ''))
ELSE [UNC].[UNC] + SUBSTRING(RTRIM(ISNULL([DIA].[FILEPATH], '')), 3, LEN(RTRIM(ISNULL([DIA].[FILEPATH], ''))) - 2) END AS [FILEPATH],
-- [RES].[EMAILSENT],
RTRIM(ISNULL(CONVERT(VARCHAR(1000),
SUBSTRING(RTRIM(ISNULL(CONVERT(VARCHAR(2000),
[DIA].[SUBJECT]),'')),
1,
1000)),
'')) AS [SUBJECT],
RTRIM(ISNULL(CONVERT(VARCHAR(120),
SUBSTRING(RTRIM(ISNULL(CONVERT(VARCHAR(120),
[DIA].[DisplayText]),'')),
1,
120)),
'')) AS [ShortText],
RTRIM(ISNULL([DIA].[NAME], '')) AS [NAME],
RTRIM(ISNULL([DIA].[PROCESSTYPE], '')) AS [PROCESSTYPE],
RTRIM(ISNULL([DIA].[ACTIONTYPE], '')) AS [ACTIONTYPE],
[DIA].[ActionTypeDescription],
RTRIM(ISNULL([DIA].[SOURCE], CASE WHEN [DIA].[ACTIONID] IS NULL THEN 'IManage' ELSE '' END)) AS [DOCUMENTSOURCE],
--,TotalRowsCount
[DAS].[SigningStatus],
[DIA].[LASTACCESSDATE] AS [LASTACCESSDATE],
[DAI].OriginalUniqueID AS [OriginalMatterLinkId]
FROM #DiaryTables [DIA]
LEFT JOIN [DiaryAttachmentSelector] [DAS] ON [DAS].[TrackReference] = [DIA].[TrackReference]
-- INNER JOIN dbo.[matters] [MAT] ON [MAT].[CODE] = [DIA].[CASECODE]
CROSS APPLY (SELECT CASE WHEN COUNT(1) = 0
THEN CONVERT(BIT, 0)
ELSE CONVERT(BIT, 1) END AS [InDocFolder]
FROM [dbo].[DocFolderDocuments] [DFD]
WHERE [DFD].[TrackReference] = [DIA].[TrackReference]) [IND]
CROSS APPLY (SELECT CASE WHEN COUNT(1) = 0
THEN CONVERT(BIT, 0)
ELSE CONVERT(BIT, 1) END AS [IsShared]
FROM [dbo].[FileSharingDocuments] [FSD]
INNER JOIN [dbo].[FileSharingMapping] [FSM]
INNER JOIN [dbo].[FileSharingDocFolders] [FSDF]
ON [FSDF].[DocFolderID] = [FSM].[FileSharingDocFoldersID]
AND [FSDF].[Shared] = 1
ON [FSM].[id] = [FSD].[FileSharingMappingID]
WHERE [FSD].[TrackReference] = [DIA].[TrackReference]) [SHD]
CROSS APPLY (SELECT ISNULL(MAX([DAV].[Version]), 0) + 1 AS [CurrentVersion]
FROM [dbo].[DiaryAttachmentVersioning] [DAV]
WHERE TRACKREFERENCE = [DIA].[TRACKREFERENCE]) [DV]
LEFT OUTER JOIN [dbo].[UNCAlias] [UNC]
ON SUBSTRING([DIA].[FilePath], 2, 1) = ':'
AND [UNC].[Drive] = SUBSTRING([DIA].[FilePath], 1, 1)
OUTER APPLY ( SELECT TOP 1
[MST].[TrackReference] AS [TrackReference],
[MST].[Type] AS [Type]
FROM #DiaryTables [MST]
WHERE [MST].[diaryid] = [DIA].[ActionID]
ORDER BY CASE WHEN [MST].Type = 'MSG'
THEN 0
ELSE 1 END,
[MST].[TrackReference]) [OMS]
LEFT JOIN [dbo].[diaryAttachmentOriginalmatterlinks] [DAI] ON [DIA].[TrackReference] = [DAI].[TrackReference]
END
--SELECT * from #DiaryTables
-- OFFSET @PageSize * (@PageNo - 1) ROWS
-- FETCH NEXT @PageSize ROWS ONLY OPTION (RECOMPILE);
END
ELSE IF ISNULL(@DocFolderID, 0) <> 0
BEGIN
IF @DocumentIsNear=1
BEGIN
SELECT @TOTALRECORDS = Count(1)
FROM [dbo].[DocFolderDocuments] [DDC]
INNER JOIN dbo.[diary] [DIA]
INNER JOIN dbo.[DiaryAttachments] [DA] ON [DA].[DiaryID] = [DIA].[ACTIONID]
AND [DA].[CASECODE] = [DIA].[CASECODE]
ON [DA].[TrackReference] = [DDC].[TrackReference]
WHERE [DDC].[DocFolderID] = @DocFolderID
AND
[DA].DATEENTERED>=@DocumentStartDate
AND
[DA].DATEENTERED<=@DocumentEndDate
AND
(
(CASE WHEN @DOCCLASSFilterCount = 0 THEN 0
WHEN [DA].[DocClass] 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 [DOCCLASS] from @CustomFilter WHERE [DOCCLASS] IS NOT NULL and [DOCCLASS] <>''),',','') + '' AS XML) AS x
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)) FILTERVALUETBLFnl) THEN 0
ELSE 1 END) = 0
AND
(CASE WHEN @TypeFilterCount = 0 THEN 0
WHEN [DA].[TYPE] 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 [TYPE] from @CustomFilter WHERE [TYPE] IS NOT NULL and [TYPE] <>''),',','') + '' AS XML) AS x
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)) FILTERVALUETBLFnl) THEN 0
ELSE 1 END) =0
AND
(CASE WHEN @AttachmentFilterCount = 0 THEN 0
WHEN [DIA].[Attachments] 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 [ATTACHMENT] from @CustomFilter WHERE [ATTACHMENT] IS NOT NULL and [ATTACHMENT] <>''),',','') + '' AS XML) AS x
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)) FILTERVALUETBLFnl) THEN 0
ELSE 1 END) = 0
AND
(CASE WHEN @SourceFilterCount = 0 THEN 0
WHEN (RTRIM(ISNULL([DA].[SOURCE], CASE WHEN [DIA].[ACTIONID] IS NULL THEN 'IManage' ELSE '' END))) 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 [SOURCE] from @CustomFilter WHERE [SOURCE] IS NOT NULL and [SOURCE] <>''),',','') + '' AS XML) AS x
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)) FILTERVALUETBLFnl) THEN 0
ELSE 1 END) = 0
)
SELECT
[DIA].[CASECODE],[DIA].[ACTIONID],[DIA].[DATE],[DIA].[TxmDate], [DIA].[EMAIL],[DIA].[ATTACHMENTS],[DIA].[EMAILADDRESS],[DIA].[ADDRESSTO],[DIA].[SUBJECT],[DIA].[DisplayText],
[DIA].[PROCESSTYPE],[DIA].[ACTIONTYPE],
[DA].[DIARYID],[DA].[TrackReference],[DA].[DocClass],[DA].[DATEENTERED],[DA].[TYPE],
[DA].[FILEPATH],[DA].[NAME],[DA].[SOURCE],
convert(VARCHAR(500), ISNULL([DX].[IMDocID], '')) AS [IMDocID],
convert(VARCHAR(20), ISNULL([DL].[IMClass], '')) AS [IMClass],
RTRIM(convert(VARCHAR(2000), ISNULL([DL].[Description], ISNULL([DA].[Document], '')))) AS [Document],
CASE WHEN [DIA].[ACTIONTYPE] = 'A'
THEN 'Action'
WHEN [DIA].[ACTIONTYPE] = 'N'
THEN 'Note'
WHEN [DIA].[ACTIONTYPE] = 'P'
THEN 'Appointment'
WHEN [DIA].[ACTIONTYPE] = 'R'
THEN 'Reminder'
WHEN [DIA].[ACTIONTYPE] = 'E'
THEN 'Email'
WHEN [DIA].[ACTIONTYPE] = 'T'
THEN 'Phone Message'
WHEN [DIA].[ACTIONTYPE] = 'D'
THEN 'Dictation'
WHEN [DIA].[ACTIONTYPE] = 'U'
THEN 'Undertaking'
WHEN [DIA].[ACTIONTYPE] = 'S'
THEN 'Statute Date'
WHEN [DIA].[ACTIONTYPE] = 'C'
THEN 'Critical Date'
WHEN [DIA].[ACTIONTYPE] = 'O'
THEN 'Court Date'
WHEN [DIA].[ACTIONTYPE] = 'M'
THEN 'Scanned Post/Mail'
WHEN RTRIM(ISNULL([DIA].[ACTIONTYPE], '')) = ''
THEN CASE WHEN convert(VARCHAR(1), CASE WHEN RTRIM(ISNULL([DIA].[EMAIL], 'N')) = 'Y' THEN 'Y' ELSE 'N' END) = 'Y'
THEN 'Email'
WHEN convert(VARCHAR(500), ISNULL([DX].[IMDocID], '')) <> ''
THEN 'IManage document'
ELSE 'Unknown' END
ELSE 'Action Type: ' + [DIA].[ACTIONTYPE] END AS [ActionTypeDescription],
CASE WHEN convert(VARCHAR(10), ISNULL([DA].[DocClass], '')) <> ''
THEN RTRIM(ISNULL(DCL.[CLASSDESCRIPTION], ''))
ELSE CASE WHEN [DCL].[ClassCode] = 'ATTNOTES'
THEN 'Attendance Notes'
WHEN [DCL].[ClassCode] = 'BILL'
THEN 'Bill'
WHEN [DCL].[ClassCode] = 'FAX'
THEN 'Fax'
WHEN [DCL].[ClassCode] = 'LEGAL'
THEN 'Legal'
WHEN [DCL].[ClassCode] = 'SCAN'
THEN 'Scan'
WHEN [DCL].[ClassCode] = 'COMPARE'
THEN 'Compare Document'
WHEN [DCL].[ClassCode] = 'DISCUSSION'
THEN 'Discussion'
WHEN [DCL].[ClassCode] = 'DOC'
THEN 'Document'
WHEN [DCL].[ClassCode] = 'E-MAIL'
THEN 'E-Mail'
WHEN [DCL].[ClassCode] = 'EVENT'
THEN 'Event'
WHEN [DCL].[ClassCode] = 'LETTER'
THEN 'Letter'
WHEN [DCL].[ClassCode] = 'MEMO'
THEN 'Memo'
WHEN [DCL].[ClassCode] = 'PAGE_ICON'
THEN 'Page Icon'
WHEN [DCL].[ClassCode] = 'TASK'
THEN 'Task'
WHEN [DCL].[ClassCode] = 'TEXT'
THEN 'Text File'
WHEN [DCL].[ClassCode] = 'WEBDOC'
THEN 'InfoLink Web Page'
ELSE ISNULL([DCL].[CLASSCODE], '') END END AS [DocumentClassDescription],
[DA].[LASTACCESSDATE] AS [LASTACCESSDATE],
[DAI].OriginalUniqueID AS [OriginalMatterLinkId]
-- , Count(1) Over() as TotalRowsCount
INTO #DiaryTableListDOc
FROM [dbo].[DocFolderDocuments] [DDC]
INNER JOIN dbo.[diary] [DIA]
INNER JOIN dbo.[DiaryAttachments] [DA] ON [DA].[DiaryID] = [DIA].[ACTIONID]
AND [DIA].CASECODE = [DA].CASECODE
ON [DA].[TrackReference] = [DDC].[TrackReference]
LEFT OUTER JOIN [dbo].[DAIMXRef] [DX]
LEFT OUTER JOIN @IMDocTable [DL]
ON [DL].[IMDocID] = [DX].[IMDocID]
ON [DX].[TrackReference] = [DA].[TrackReference]
LEFT OUTER JOIN [dbo].[DocumentClasses] DCL
ON convert(VARCHAR(10), ISNULL([DA].[DocClass], '')) <> ''
AND DCL.[CLASSCODE] = CASE WHEN convert(VARCHAR(10), ISNULL([DA].[DocClass], '')) = '' THEN convert(VARCHAR(20), ISNULL([DL].[IMClass], ''))
ELSE convert(VARCHAR(10), ISNULL([DA].[DocClass], ''))
END
LEFT JOIN [dbo].[diaryAttachmentOriginalmatterlinks] [DAI] ON [DA].[TrackReference] = [DAI].[TrackReference]
WHERE [DDC].[DocFolderID] = @DocFolderID
AND
[DA].DATEENTERED>=@DocumentStartDate
AND
[DA].DATEENTERED<=@DocumentEndDate
AND
(
(CASE WHEN @DOCCLASSFilterCount = 0 THEN 0
WHEN [DA].[DocClass] 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 [DOCCLASS] from @CustomFilter WHERE [DOCCLASS] IS NOT NULL and [DOCCLASS] <>''),',','') + '' AS XML) AS x
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)) FILTERVALUETBLFnl) THEN 0
ELSE 1 END) = 0
AND
(CASE WHEN @TypeFilterCount = 0 THEN 0
WHEN [DA].[TYPE] 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 [TYPE] from @CustomFilter WHERE [TYPE] IS NOT NULL and [TYPE] <>''),',','') + '' AS XML) AS x
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)) FILTERVALUETBLFnl) THEN 0
ELSE 1 END) =0
AND
(CASE WHEN @AttachmentFilterCount = 0 OR [Attachments] 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 [ATTACHMENT] from @CustomFilter WHERE [ATTACHMENT] IS NOT NULL and [ATTACHMENT] <>''),',','') + '' AS XML) AS x
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)) FILTERVALUETBLFnl) THEN 0
ELSE 1 END) = 0
AND
(CASE WHEN @SourceFilterCount = 0 OR [SOURCE] 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 [SOURCE] from @CustomFilter WHERE [SOURCE] IS NOT NULL and [SOURCE] <>''),',','') + '' AS XML) AS x
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)) FILTERVALUETBLFnl) THEN 0
ELSE 1 END) = 0
)
order by [DA].DATEENTERED
OFFSET @PageSize * (@PageNo - 1) ROWS
FETCH NEXT @PageSize ROWS ONLY OPTION (RECOMPILE);
--SELECT * from #DiaryTableList
INSERT
INTO @res
([ActionId],
[TrackReference],
[IMDocID],
[IMClass],
[DocClass],
[Document],
[DiaryDate],
[DocumentDate],
[EmailSent],
[TYPE],
[Attachments],
[EMAIL],
[EmailFrom],
[EmailTo],
[CurrentVersion],
[IsInDocfolder],
[IsShared],
[DocumentClassDescription],
[UniqueId],
[FilePath],
[Subject],
[ShortText],
[NAME],
[ProcessType],
[ACTIONTYPE],
[ActionTypeDescription],
[DOCUMENTSOURCE],
[TotalRecord],
[SigningStatus],
[LASTACCESSDATE],
[OriginalMatterLinkId]
)
SELECT convert(INT, [DIA].[DIARYID]) AS [ActionID],
convert(INT, [DIA].[TrackReference]) AS [TrackReference],
convert(VARCHAR(500), ISNULL([DX].[IMDocID], '')) AS [IMDocID],
convert(VARCHAR(20), ISNULL([DIA].[IMClass], '')) AS [IMClass],
convert(VARCHAR(10), ISNULL([DIA].[DocClass], '')) AS [DocClass],
RTRIM(convert(VARCHAR(2000), ISNULL([DL].[Description], ISNULL([DIA].[Document], '')))) AS [Document],
convert(DATETIME, [DIA].[DATE]) As [DiaryDate],
convert(DATETIME, [DIA].[DATEENTERED]) AS [DocumentDate],
convert(DATETIME, ISNULL([DIA].[TxmDate], [DIA].[Date])) As [EMAILSENT],
convert(VARCHAR(20), UPPER(RTRIM(ISNULL([DIA].[TYPE], '')))) AS [TYPE],
--CASE WHEN [DIA].[ATTACHMENTS] = 'Y' THEN 'Y' ELSE 'N' END AS [Attachments],
(CASE WHEN ISNULL( convert(VARCHAR(1), CASE WHEN RTRIM(ISNULL([DIA].[EMAIL], 'N')) = 'Y' THEN 'Y' ELSE 'N' END), '') = 'Y'
AND convert(INT, [DIA].[TrackReference]) = [OMS].[TrackReference]
THEN (CASE WHEN [DIA].[ATTACHMENTS] = 'Y' THEN 'Y' ELSE 'N' END )
WHEN ISNULL(convert(VARCHAR(20), UPPER(RTRIM(ISNULL([DIA].[TYPE], '')))), '') = 'MSG'
AND convert(INT, [DIA].[TrackReference]) = [OMS].[TrackReference]
THEN (CASE WHEN [DIA].[ATTACHMENTS] = 'Y' THEN 'Y' ELSE 'N' END)
ELSE 'N' END) AS [Attachments],
-- convert(VARCHAR(1), CASE WHEN RTRIM(ISNULL([DIA].[EMAIL], 'N')) = 'Y' THEN 'Y' ELSE 'N' END) AS [EMAIL],
(CASE WHEN convert(VARCHAR(1), CASE WHEN RTRIM(ISNULL([DIA].[EMAIL], 'N')) = 'Y' THEN 'Y' ELSE 'N' END) = 'Y'
AND convert(INT, [DIA].[TrackReference]) = [OMS].[TrackReference]
THEN 'Y'
WHEN ISNULL(convert(VARCHAR(20), UPPER(RTRIM(ISNULL([DIA].[TYPE], '')))), '') = 'MSG'
AND convert(INT, [DIA].[TrackReference]) = [OMS].[TrackReference]
THEN 'Y'
ELSE 'N' END) AS [EMAIL],
--convert(VARCHAR(200), RTRIM(ISNULL([DIA].[EMAILADDRESS], ''))) AS [EMAILFROM],
RTRIM(ISNULL(CONVERT(VARCHAR(200), SUBSTRING(convert(VARCHAR(200), RTRIM(ISNULL([DIA].[EMAILADDRESS], ''))), 1, 200)), '')) AS [EMAILFROM],
-- RTRIM(ISNULL(CONVERT(VARCHAR(1000), [DIA].[ADDRESSTO]), '')) AS [EMAILTO],
RTRIM(ISNULL(CONVERT(VARCHAR(1000), SUBSTRING(RTRIM(ISNULL(CONVERT(VARCHAR(1000), [DIA].[ADDRESSTO]), '')), 1, 1000)), '')) AS [EMAILTO],
[DV].[CurrentVersion],
CONVERT(BIT, 1),
[SHD].[IsShared],
CASE WHEN convert(VARCHAR(10), ISNULL([DIA].[DocClass], '')) <> ''
THEN RTRIM(ISNULL(DCL.[CLASSDESCRIPTION], ''))
ELSE CASE WHEN [DCL].[ClassCode] = 'ATTNOTES'
THEN 'Attendance Notes'
WHEN [DCL].[ClassCode] = 'BILL'
THEN 'Bill'
WHEN [DCL].[ClassCode] = 'FAX'
THEN 'Fax'
WHEN [DCL].[ClassCode] = 'LEGAL'
THEN 'Legal'
WHEN [DCL].[ClassCode] = 'SCAN'
THEN 'Scan'
WHEN [DCL].[ClassCode] = 'COMPARE'
THEN 'Compare Document'
WHEN [DCL].[ClassCode] = 'DISCUSSION'
THEN 'Discussion'
WHEN [DCL].[ClassCode] = 'DOC'
THEN 'Document'
WHEN [DCL].[ClassCode] = 'E-MAIL'
THEN 'E-Mail'
WHEN [DCL].[ClassCode] = 'EVENT'
THEN 'Event'
WHEN [DCL].[ClassCode] = 'LETTER'
THEN 'Letter'
WHEN [DCL].[ClassCode] = 'MEMO'
THEN 'Memo'
WHEN [DCL].[ClassCode] = 'PAGE_ICON'
THEN 'Page Icon'
WHEN [DCL].[ClassCode] = 'TASK'
THEN 'Task'
WHEN [DCL].[ClassCode] = 'TEXT'
THEN 'Text File'
WHEN [DCL].[ClassCode] = 'WEBDOC'
THEN 'InfoLink Web Page'
ELSE ISNULL([DCL].[CLASSCODE], '') END END AS [DocumentClassDescription],
@uniqueid AS uniqueid,
CASE WHEN [UNC].[UNC] IS NULL THEN RTRIM(ISNULL([DIA].[FILEPATH], ''))
ELSE [UNC].[UNC] + SUBSTRING(RTRIM(ISNULL([DIA].[FILEPATH], '')), 3, LEN(RTRIM(ISNULL([DIA].[FILEPATH], ''))) - 2) END AS [FILEPATH],
-- [RES].[EMAILSENT],
RTRIM(ISNULL(CONVERT(VARCHAR(1000),
SUBSTRING(RTRIM(ISNULL(CONVERT(VARCHAR(2000),
[DIA].[SUBJECT]),'')),
1,
1000)),
'')) AS [SUBJECT],
RTRIM(ISNULL(CONVERT(VARCHAR(120),
SUBSTRING(RTRIM(ISNULL(CONVERT(VARCHAR(120),
[DIA].[DisplayText]),'')),
1,
120)),
'')) AS [ShortText],
RTRIM(ISNULL([DIA].[NAME], '')) AS [NAME],
RTRIM(ISNULL([DIA].[PROCESSTYPE], '')) AS [PROCESSTYPE],
RTRIM(ISNULL([DIA].[ACTIONTYPE], '')) AS [ACTIONTYPE],
CASE WHEN [DIA].[ACTIONTYPE] = 'A'
THEN 'Action'
WHEN [DIA].[ACTIONTYPE] = 'N'
THEN 'Note'
WHEN [DIA].[ACTIONTYPE] = 'P'
THEN 'Appointment'
WHEN [DIA].[ACTIONTYPE] = 'R'
THEN 'Reminder'
WHEN [DIA].[ACTIONTYPE] = 'E'
THEN 'Email'
WHEN [DIA].[ACTIONTYPE] = 'T'
THEN 'Phone Message'
WHEN [DIA].[ACTIONTYPE] = 'D'
THEN 'Dictation'
WHEN [DIA].[ACTIONTYPE] = 'U'
THEN 'Undertaking'
WHEN [DIA].[ACTIONTYPE] = 'S'
THEN 'Statute Date'
WHEN [DIA].[ACTIONTYPE] = 'C'
THEN 'Critical Date'
WHEN [DIA].[ACTIONTYPE] = 'O'
THEN 'Court Date'
WHEN [DIA].[ACTIONTYPE] = 'M'
THEN 'Scanned Post/Mail'
WHEN RTRIM(ISNULL([DIA].[ACTIONTYPE], '')) = ''
THEN CASE WHEN convert(VARCHAR(1), CASE WHEN RTRIM(ISNULL([DIA].[EMAIL], 'N')) = 'Y' THEN 'Y' ELSE 'N' END) = 'Y'
THEN 'Email'
WHEN convert(VARCHAR(500), ISNULL([DX].[IMDocID], '')) <> ''
THEN 'IManage document'
ELSE 'Unknown' END
ELSE 'Action Type: ' + [DIA].[ACTIONTYPE] END AS [ActionTypeDescription],
RTRIM(ISNULL([DIA].[SOURCE], CASE WHEN [DIA].[ACTIONID] IS NULL THEN 'IManage' ELSE '' END)) AS [DOCUMENTSOURCE]
,Count(1) over() [TotalRecord],
[DAS].[SigningStatus],
[DIA].[LASTACCESSDATE] AS [LASTACCESSDATE],
[DAI].OriginalUniqueID AS [OriginalMatterLinkId]
FROM #DiaryTableListDOc [DIA]
LEFT JOIN [DiaryAttachmentSelector] [DAS] ON [DAS].[TrackReference] = [DIA].[TrackReference]
--ON [DIA].[TrackReference] = [DDC].[TrackReference]
CROSS APPLY (SELECT ISNULL(MAX([DAV].[Version]), 0) + 1 AS [CurrentVersion]
FROM [dbo].[DiaryAttachmentVersioning] [DAV]
WHERE TRACKREFERENCE = [DIA].[TRACKREFERENCE]) [DV]
CROSS APPLY (SELECT CASE WHEN COUNT(1) = 0
THEN CONVERT(BIT, 0)
ELSE CONVERT(BIT, 1) END AS [IsShared]
FROM [dbo].[FileSharingDocuments] [FSD]
INNER JOIN [dbo].[FileSharingMapping] [FSM]
INNER JOIN [dbo].[FileSharingDocFolders] [FSDF]
ON [FSDF].[DocFolderID] = [FSM].[FileSharingDocFoldersID]
AND [FSDF].[Shared] = 1
ON [FSM].[id] = [FSD].[FileSharingMappingID]
AND [FSM].[FileSharingDocFoldersID] = @DocFolderID
WHERE [FSD].[TrackReference] = [DIA].[TrackReference]) [SHD]
LEFT OUTER JOIN [dbo].[DAIMXRef] [DX]
LEFT OUTER JOIN @IMDocTable [DL]
ON [DL].[IMDocID] = [DX].[IMDocID]
ON [DX].[TrackReference] = [DIA].[TrackReference]
LEFT OUTER JOIN [dbo].[DocumentClasses] DCL
ON convert(VARCHAR(10), ISNULL([DIA].[DocClass], '')) <> ''
AND DCL.[CLASSCODE] = CASE WHEN convert(VARCHAR(10), ISNULL([DIA].[DocClass], '')) = '' THEN convert(VARCHAR(20), ISNULL([DL].[IMClass], ''))
ELSE convert(VARCHAR(10), ISNULL([DIA].[DocClass], ''))
END
LEFT OUTER JOIN [dbo].[UNCAlias] [UNC]
ON SUBSTRING([DIA].[FilePath], 2, 1) = ':'
AND [UNC].[Drive] = SUBSTRING([DIA].[FilePath], 1, 1)
OUTER APPLY ( SELECT TOP 1
[MST].[TrackReference] AS [TrackReference],
[MST].[Type] AS [Type]
FROM #DiaryTableListDOc [MST]
WHERE [MST].[diaryid] = [DIA].[ActionID] --AND [MST].[TrackReference] = [DA].[TrackReference]
ORDER BY CASE WHEN [MST].Type = 'MSG'
THEN 0
ELSE 1 END,
[MST].[TrackReference]) [OMS]
LEFT JOIN [dbo].[diaryAttachmentOriginalmatterlinks] [DAI] ON [DIA].[TrackReference] = [DAI].[TrackReference]
END
ELSE
BEGIN
SELECT @TOTALRECORDS = Count(1)
FROM [dbo].[DocFolderDocuments] [DDC]
INNER JOIN dbo.[diary] [DIA]
INNER JOIN dbo.[DiaryAttachments] [DA] ON [DA].[DiaryID] = [DIA].[ACTIONID]
AND [DA].[CASECODE] = [DIA].[CASECODE]
ON [DA].[TrackReference] = [DDC].[TrackReference]
WHERE [DDC].[DocFolderID] = @DocFolderID
AND
(
CASE WHEN @SearchText = '' THEN 1
WHEN
([DA].FILEPATH LIKE '%' + @SearchText + '%')
OR
(RTRIM(convert(VARCHAR(2000), ISNULL([DA].[Document], ''))) LIKE '%' + @SearchText + '%')
OR
([DA].[DATEENTERED] LIKE '%' + @SearchText + '%')
OR
([DA].[TYPE] LIKE '%' + @SearchText + '%')
OR
([DA].[DocClass] LIKE '%' + @SearchText + '%')
THEN 1
ELSE 0
END =1
)
AND
(
ISNULL(@FileType, '') = ''
OR ([DA].[TYPE] IN (select FileType from @FileTypeList ))
)
AND
(
(CASE WHEN @DOCCLASSFilterCount = 0 THEN 0
WHEN [DA].[DocClass] 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 [DOCCLASS] from @CustomFilter WHERE [DOCCLASS] IS NOT NULL and [DOCCLASS] <>''),',','') + '' AS XML) AS x
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)) FILTERVALUETBLFnl) THEN 0
ELSE 1 END) = 0
AND
(CASE WHEN @TypeFilterCount = 0 THEN 0
WHEN [DA].[TYPE] 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 [TYPE] from @CustomFilter WHERE [TYPE] IS NOT NULL and [TYPE] <>''),',','') + '' AS XML) AS x
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)) FILTERVALUETBLFnl) THEN 0
ELSE 1 END) =0
AND
(CASE WHEN @AttachmentFilterCount = 0 THEN 0
WHEN [DIA].[Attachments] 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 [ATTACHMENT] from @CustomFilter WHERE [ATTACHMENT] IS NOT NULL and [ATTACHMENT] <>''),',','') + '' AS XML) AS x
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)) FILTERVALUETBLFnl) THEN 0
ELSE 1 END) = 0
AND
(CASE WHEN @SourceFilterCount = 0 THEN 0
WHEN (RTRIM(ISNULL([DA].[SOURCE], CASE WHEN [DIA].[ACTIONID] IS NULL THEN 'IManage' ELSE '' END))) 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 [SOURCE] from @CustomFilter WHERE [SOURCE] IS NOT NULL and [SOURCE] <>''),',','') + '' AS XML) AS x
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)) FILTERVALUETBLFnl) THEN 0
ELSE 1 END) = 0
)
SELECT
[DIA].[CASECODE],[DIA].[ACTIONID],[DIA].[DATE],[DIA].[TxmDate], [DIA].[EMAIL],[DIA].[ATTACHMENTS],[DIA].[EMAILADDRESS],[DIA].[ADDRESSTO],[DIA].[SUBJECT],[DIA].[DisplayText],
[DIA].[PROCESSTYPE],[DIA].[ACTIONTYPE],
[DA].[DIARYID],[DA].[TrackReference],[DA].[DocClass],[DA].[DATEENTERED],[DA].[TYPE],
[DA].[FILEPATH],[DA].[NAME],[DA].[SOURCE],
convert(VARCHAR(500), ISNULL([DX].[IMDocID], '')) AS [IMDocID],
convert(VARCHAR(20), ISNULL([DL].[IMClass], '')) AS [IMClass],
RTRIM(convert(VARCHAR(2000), ISNULL([DL].[Description], ISNULL([DA].[Document], '')))) AS [Document],
CASE WHEN [DIA].[ACTIONTYPE] = 'A'
THEN 'Action'
WHEN [DIA].[ACTIONTYPE] = 'N'
THEN 'Note'
WHEN [DIA].[ACTIONTYPE] = 'P'
THEN 'Appointment'
WHEN [DIA].[ACTIONTYPE] = 'R'
THEN 'Reminder'
WHEN [DIA].[ACTIONTYPE] = 'E'
THEN 'Email'
WHEN [DIA].[ACTIONTYPE] = 'T'
THEN 'Phone Message'
WHEN [DIA].[ACTIONTYPE] = 'D'
THEN 'Dictation'
WHEN [DIA].[ACTIONTYPE] = 'U'
THEN 'Undertaking'
WHEN [DIA].[ACTIONTYPE] = 'S'
THEN 'Statute Date'
WHEN [DIA].[ACTIONTYPE] = 'C'
THEN 'Critical Date'
WHEN [DIA].[ACTIONTYPE] = 'O'
THEN 'Court Date'
WHEN [DIA].[ACTIONTYPE] = 'M'
THEN 'Scanned Post/Mail'
WHEN RTRIM(ISNULL([DIA].[ACTIONTYPE], '')) = ''
THEN CASE WHEN convert(VARCHAR(1), CASE WHEN RTRIM(ISNULL([DIA].[EMAIL], 'N')) = 'Y' THEN 'Y' ELSE 'N' END) = 'Y'
THEN 'Email'
WHEN convert(VARCHAR(500), ISNULL([DX].[IMDocID], '')) <> ''
THEN 'IManage document'
ELSE 'Unknown' END
ELSE 'Action Type: ' + [DIA].[ACTIONTYPE] END AS [ActionTypeDescription],
CASE WHEN convert(VARCHAR(10), ISNULL([DA].[DocClass], '')) <> ''
THEN RTRIM(ISNULL(DCL.[CLASSDESCRIPTION], ''))
ELSE CASE WHEN [DCL].[ClassCode] = 'ATTNOTES'
THEN 'Attendance Notes'
WHEN [DCL].[ClassCode] = 'BILL'
THEN 'Bill'
WHEN [DCL].[ClassCode] = 'FAX'
THEN 'Fax'
WHEN [DCL].[ClassCode] = 'LEGAL'
THEN 'Legal'
WHEN [DCL].[ClassCode] = 'SCAN'
THEN 'Scan'
WHEN [DCL].[ClassCode] = 'COMPARE'
THEN 'Compare Document'
WHEN [DCL].[ClassCode] = 'DISCUSSION'
THEN 'Discussion'
WHEN [DCL].[ClassCode] = 'DOC'
THEN 'Document'
WHEN [DCL].[ClassCode] = 'E-MAIL'
THEN 'E-Mail'
WHEN [DCL].[ClassCode] = 'EVENT'
THEN 'Event'
WHEN [DCL].[ClassCode] = 'LETTER'
THEN 'Letter'
WHEN [DCL].[ClassCode] = 'MEMO'
THEN 'Memo'
WHEN [DCL].[ClassCode] = 'PAGE_ICON'
THEN 'Page Icon'
WHEN [DCL].[ClassCode] = 'TASK'
THEN 'Task'
WHEN [DCL].[ClassCode] = 'TEXT'
THEN 'Text File'
WHEN [DCL].[ClassCode] = 'WEBDOC'
THEN 'InfoLink Web Page'
ELSE ISNULL([DCL].[CLASSCODE], '') END END AS [DocumentClassDescription],
[DA].[LASTACCESSDATE] AS [LASTACCESSDATE],
[DAI].OriginalUniqueID AS [OriginalMatterLinkId]
-- , Count(1) Over() as TotalRowsCount
INTO #DiaryTableList
FROM [dbo].[DocFolderDocuments] [DDC]
INNER JOIN dbo.[diary] [DIA]
INNER JOIN dbo.[DiaryAttachments] [DA] ON [DA].[DiaryID] = [DIA].[ACTIONID]
AND [DIA].CASECODE = [DA].CASECODE
ON [DA].[TrackReference] = [DDC].[TrackReference]
LEFT OUTER JOIN [dbo].[DAIMXRef] [DX]
LEFT OUTER JOIN @IMDocTable [DL]
ON [DL].[IMDocID] = [DX].[IMDocID]
ON [DX].[TrackReference] = [DA].[TrackReference]
LEFT OUTER JOIN [dbo].[DocumentClasses] DCL
ON convert(VARCHAR(10), ISNULL([DA].[DocClass], '')) <> ''
AND DCL.[CLASSCODE] = CASE WHEN convert(VARCHAR(10), ISNULL([DA].[DocClass], '')) = '' THEN convert(VARCHAR(20), ISNULL([DL].[IMClass], ''))
ELSE convert(VARCHAR(10), ISNULL([DA].[DocClass], ''))
END
LEFT JOIN [dbo].[diaryAttachmentOriginalmatterlinks] [DAI] ON [DA].[TrackReference] = [DAI].[TrackReference]
WHERE [DDC].[DocFolderID] = @DocFolderID
AND
(
CASE WHEN @SearchText = '' THEN 1
WHEN
([DA].FILEPATH LIKE '%' + @SearchText + '%')
OR
([Document] LIKE '%' + @SearchText + '%')
OR
([DA].[DATEENTERED] LIKE '%' + @SearchText + '%')
OR
([DA].[TYPE] LIKE '%' + @SearchText + '%')
OR
([DA].[DocClass] LIKE '%' + @SearchText + '%')
THEN 1
ELSE 0
END =1
)
AND
(
ISNULL(@FileType, '') = ''
OR ([DA].[TYPE] IN (select FileType from @FileTypeList ))
)
AND
(
(CASE WHEN @DOCCLASSFilterCount = 0 THEN 0
WHEN [DA].[DocClass] 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 [DOCCLASS] from @CustomFilter WHERE [DOCCLASS] IS NOT NULL and [DOCCLASS] <>''),',','') + '' AS XML) AS x
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)) FILTERVALUETBLFnl) THEN 0
ELSE 1 END) = 0
AND
(CASE WHEN @TypeFilterCount = 0 THEN 0
WHEN [DA].[TYPE] 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 [TYPE] from @CustomFilter WHERE [TYPE] IS NOT NULL and [TYPE] <>''),',','') + '' AS XML) AS x
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)) FILTERVALUETBLFnl) THEN 0
ELSE 1 END) =0
AND
(CASE WHEN @AttachmentFilterCount = 0 OR [Attachments] 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 [ATTACHMENT] from @CustomFilter WHERE [ATTACHMENT] IS NOT NULL and [ATTACHMENT] <>''),',','') + '' AS XML) AS x
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)) FILTERVALUETBLFnl) THEN 0
ELSE 1 END) = 0
AND
(CASE WHEN @SourceFilterCount = 0 OR [SOURCE] 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 [SOURCE] from @CustomFilter WHERE [SOURCE] IS NOT NULL and [SOURCE] <>''),',','') + '' AS XML) AS x
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)) FILTERVALUETBLFnl) THEN 0
ELSE 1 END) = 0
)
ORDER BY
CASE WHEN @SortDirection = 'ASC' AND @SortColumn!='RowNumber' THEN
CASE @SortColumn
WHEN 'Type' THEN convert(VARCHAR(20), UPPER(RTRIM(ISNULL([DA].[TYPE], ''))))
END
END,
CASE WHEN @SortDirection = 'DESC' AND @SortColumn!='RowNumber' THEN
CASE @SortColumn
WHEN 'Type' THEN convert(VARCHAR(20), UPPER(RTRIM(ISNULL([DA].[TYPE], ''))))
END
END DESC,
CASE WHEN @SortDirection = 'ASC' AND @SortColumn!='RowNumber' THEN
CASE @SortColumn
WHEN 'Document' THEN RTRIM(convert(VARCHAR(2000), ISNULL([DL].[Description], ISNULL([DA].[Document], ''))))
END
END,
CASE WHEN @SortDirection = 'DESC' AND @SortColumn!='RowNumber' THEN
CASE @SortColumn
WHEN 'Document' THEN RTRIM(convert(VARCHAR(2000), ISNULL([DL].[Description], ISNULL([DA].[Document], ''))))
END
END DESC,
CASE WHEN @SortDirection = 'ASC' AND @SortColumn!='RowNumber' THEN
CASE @SortColumn
WHEN 'DocumentClassDescription' THEN CASE WHEN convert(VARCHAR(10), ISNULL([DA].[DocClass], '')) <> ''
THEN RTRIM(ISNULL(DCL.[CLASSDESCRIPTION], ''))
ELSE CASE WHEN [DCL].[ClassCode] = 'ATTNOTES'
THEN 'Attendance Notes'
WHEN [DCL].[ClassCode] = 'BILL'
THEN 'Bill'
WHEN [DCL].[ClassCode] = 'FAX'
THEN 'Fax'
WHEN [DCL].[ClassCode] = 'LEGAL'
THEN 'Legal'
WHEN [DCL].[ClassCode] = 'SCAN'
THEN 'Scan'
WHEN [DCL].[ClassCode] = 'COMPARE'
THEN 'Compare Document'
WHEN [DCL].[ClassCode] = 'DISCUSSION'
THEN 'Discussion'
WHEN [DCL].[ClassCode] = 'DOC'
THEN 'Document'
WHEN [DCL].[ClassCode] = 'E-MAIL'
THEN 'E-Mail'
WHEN [DCL].[ClassCode] = 'EVENT'
THEN 'Event'
WHEN [DCL].[ClassCode] = 'LETTER'
THEN 'Letter'
WHEN [DCL].[ClassCode] = 'MEMO'
THEN 'Memo'
WHEN [DCL].[ClassCode] = 'PAGE_ICON'
THEN 'Page Icon'
WHEN [DCL].[ClassCode] = 'TASK'
THEN 'Task'
WHEN [DCL].[ClassCode] = 'TEXT'
THEN 'Text File'
WHEN [DCL].[ClassCode] = 'WEBDOC'
THEN 'InfoLink Web Page'
ELSE ISNULL([DCL].[CLASSCODE], '') END END
END
END,
CASE WHEN @SortDirection = 'DESC' AND @SortColumn!='RowNumber' THEN
CASE @SortColumn
WHEN 'DocumentClassDescription' THEN CASE WHEN convert(VARCHAR(10), ISNULL([DA].[DocClass], '')) <> ''
THEN RTRIM(ISNULL(DCL.[CLASSDESCRIPTION], ''))
ELSE CASE WHEN [DCL].[ClassCode] = 'ATTNOTES'
THEN 'Attendance Notes'
WHEN [DCL].[ClassCode] = 'BILL'
THEN 'Bill'
WHEN [DCL].[ClassCode] = 'FAX'
THEN 'Fax'
WHEN [DCL].[ClassCode] = 'LEGAL'
THEN 'Legal'
WHEN [DCL].[ClassCode] = 'SCAN'
THEN 'Scan'
WHEN [DCL].[ClassCode] = 'COMPARE'
THEN 'Compare Document'
WHEN [DCL].[ClassCode] = 'DISCUSSION'
THEN 'Discussion'
WHEN [DCL].[ClassCode] = 'DOC'
THEN 'Document'
WHEN [DCL].[ClassCode] = 'E-MAIL'
THEN 'E-Mail'
WHEN [DCL].[ClassCode] = 'EVENT'
THEN 'Event'
WHEN [DCL].[ClassCode] = 'LETTER'
THEN 'Letter'
WHEN [DCL].[ClassCode] = 'MEMO'
THEN 'Memo'
WHEN [DCL].[ClassCode] = 'PAGE_ICON'
THEN 'Page Icon'
WHEN [DCL].[ClassCode] = 'TASK'
THEN 'Task'
WHEN [DCL].[ClassCode] = 'TEXT'
THEN 'Text File'
WHEN [DCL].[ClassCode] = 'WEBDOC'
THEN 'InfoLink Web Page'
ELSE ISNULL([DCL].[CLASSCODE], '') END END
END
END DESC,
CASE WHEN @SortDirection = 'ASC' AND @SortColumn!='RowNumber' THEN
CASE @SortColumn
WHEN 'DocumentDate' THEN convert(DATETIME, [DA].[DATEENTERED])
END
END,
CASE WHEN @SortDirection = 'DESC' AND @SortColumn!='RowNumber' THEN
CASE @SortColumn
WHEN 'DocumentDate' THEN convert(DATETIME, [DA].[DATEENTERED])
END
END DESC, [DA].[TrackReference] DESC
OFFSET @PageSize * (@PageNo - 1) ROWS
FETCH NEXT @PageSize ROWS ONLY OPTION (RECOMPILE);
--SELECT * from #DiaryTableList
INSERT
INTO @res
([ActionId],
[TrackReference],
[IMDocID],
[IMClass],
[DocClass],
[Document],
[DiaryDate],
[DocumentDate],
[EmailSent],
[TYPE],
[Attachments],
[EMAIL],
[EmailFrom],
[EmailTo],
[CurrentVersion],
[IsInDocfolder],
[IsShared],
[DocumentClassDescription],
[UniqueId],
[FilePath],
[Subject],
[ShortText],
[NAME],
[ProcessType],
[ACTIONTYPE],
[ActionTypeDescription],
[DOCUMENTSOURCE]
, [TotalRecord],
[SigningStatus],
[LASTACCESSDATE],
[OriginalMatterLinkId]
)
SELECT convert(INT, [DIA].[DIARYID]) AS [ActionID],
convert(INT, [DIA].[TrackReference]) AS [TrackReference],
convert(VARCHAR(500), ISNULL([DX].[IMDocID], '')) AS [IMDocID],
convert(VARCHAR(20), ISNULL([DIA].[IMClass], '')) AS [IMClass],
convert(VARCHAR(10), ISNULL([DIA].[DocClass], '')) AS [DocClass],
RTRIM(convert(VARCHAR(2000), ISNULL([DL].[Description], ISNULL([DIA].[Document], '')))) AS [Document],
convert(DATETIME, [DIA].[DATE]) As [DiaryDate],
convert(DATETIME, [DIA].[DATEENTERED]) AS [DocumentDate],
convert(DATETIME, ISNULL([DIA].[TxmDate], [DIA].[Date])) As [EMAILSENT],
convert(VARCHAR(20), UPPER(RTRIM(ISNULL([DIA].[TYPE], '')))) AS [TYPE],
--CASE WHEN [DIA].[ATTACHMENTS] = 'Y' THEN 'Y' ELSE 'N' END AS [Attachments],
(CASE WHEN ISNULL( convert(VARCHAR(1), CASE WHEN RTRIM(ISNULL([DIA].[EMAIL], 'N')) = 'Y' THEN 'Y' ELSE 'N' END), '') = 'Y'
AND convert(INT, [DIA].[TrackReference]) = [OMS].[TrackReference]
THEN (CASE WHEN [DIA].[ATTACHMENTS] = 'Y' THEN 'Y' ELSE 'N' END )
WHEN ISNULL(convert(VARCHAR(20), UPPER(RTRIM(ISNULL([DIA].[TYPE], '')))), '') = 'MSG'
AND convert(INT, [DIA].[TrackReference]) = [OMS].[TrackReference]
THEN (CASE WHEN [DIA].[ATTACHMENTS] = 'Y' THEN 'Y' ELSE 'N' END)
ELSE 'N' END) AS [Attachments],
-- convert(VARCHAR(1), CASE WHEN RTRIM(ISNULL([DIA].[EMAIL], 'N')) = 'Y' THEN 'Y' ELSE 'N' END) AS [EMAIL],
(CASE WHEN convert(VARCHAR(1), CASE WHEN RTRIM(ISNULL([DIA].[EMAIL], 'N')) = 'Y' THEN 'Y' ELSE 'N' END) = 'Y'
AND convert(INT, [DIA].[TrackReference]) = [OMS].[TrackReference]
THEN 'Y'
WHEN ISNULL(convert(VARCHAR(20), UPPER(RTRIM(ISNULL([DIA].[TYPE], '')))), '') = 'MSG'
AND convert(INT, [DIA].[TrackReference]) = [OMS].[TrackReference]
THEN 'Y'
ELSE 'N' END) AS [EMAIL],
--convert(VARCHAR(200), RTRIM(ISNULL([DIA].[EMAILADDRESS], ''))) AS [EMAILFROM],
RTRIM(ISNULL(CONVERT(VARCHAR(200), SUBSTRING(convert(VARCHAR(200), RTRIM(ISNULL([DIA].[EMAILADDRESS], ''))), 1, 200)), '')) AS [EMAILFROM],
-- RTRIM(ISNULL(CONVERT(VARCHAR(1000), [DIA].[ADDRESSTO]), '')) AS [EMAILTO],
RTRIM(ISNULL(CONVERT(VARCHAR(1000), SUBSTRING(RTRIM(ISNULL(CONVERT(VARCHAR(1000), [DIA].[ADDRESSTO]), '')), 1, 1000)), '')) AS [EMAILTO],
[DV].[CurrentVersion],
CONVERT(BIT, 1),
[SHD].[IsShared],
CASE WHEN convert(VARCHAR(10), ISNULL([DIA].[DocClass], '')) <> ''
THEN RTRIM(ISNULL(DCL.[CLASSDESCRIPTION], ''))
ELSE CASE WHEN [DCL].[ClassCode] = 'ATTNOTES'
THEN 'Attendance Notes'
WHEN [DCL].[ClassCode] = 'BILL'
THEN 'Bill'
WHEN [DCL].[ClassCode] = 'FAX'
THEN 'Fax'
WHEN [DCL].[ClassCode] = 'LEGAL'
THEN 'Legal'
WHEN [DCL].[ClassCode] = 'SCAN'
THEN 'Scan'
WHEN [DCL].[ClassCode] = 'COMPARE'
THEN 'Compare Document'
WHEN [DCL].[ClassCode] = 'DISCUSSION'
THEN 'Discussion'
WHEN [DCL].[ClassCode] = 'DOC'
THEN 'Document'
WHEN [DCL].[ClassCode] = 'E-MAIL'
THEN 'E-Mail'
WHEN [DCL].[ClassCode] = 'EVENT'
THEN 'Event'
WHEN [DCL].[ClassCode] = 'LETTER'
THEN 'Letter'
WHEN [DCL].[ClassCode] = 'MEMO'
THEN 'Memo'
WHEN [DCL].[ClassCode] = 'PAGE_ICON'
THEN 'Page Icon'
WHEN [DCL].[ClassCode] = 'TASK'
THEN 'Task'
WHEN [DCL].[ClassCode] = 'TEXT'
THEN 'Text File'
WHEN [DCL].[ClassCode] = 'WEBDOC'
THEN 'InfoLink Web Page'
ELSE ISNULL([DCL].[CLASSCODE], '') END END AS [DocumentClassDescription],
@uniqueid AS uniqueid,
CASE WHEN [UNC].[UNC] IS NULL THEN RTRIM(ISNULL([DIA].[FILEPATH], ''))
ELSE [UNC].[UNC] + SUBSTRING(RTRIM(ISNULL([DIA].[FILEPATH], '')), 3, LEN(RTRIM(ISNULL([DIA].[FILEPATH], ''))) - 2) END AS [FILEPATH],
-- [RES].[EMAILSENT],
RTRIM(ISNULL(CONVERT(VARCHAR(1000),
SUBSTRING(RTRIM(ISNULL(CONVERT(VARCHAR(2000),
[DIA].[SUBJECT]),'')),
1,
1000)),
'')) AS [SUBJECT],
RTRIM(ISNULL(CONVERT(VARCHAR(120),
SUBSTRING(RTRIM(ISNULL(CONVERT(VARCHAR(120),
[DIA].[DisplayText]),'')),
1,
120)),
'')) AS [ShortText],
RTRIM(ISNULL([DIA].[NAME], '')) AS [NAME],
RTRIM(ISNULL([DIA].[PROCESSTYPE], '')) AS [PROCESSTYPE],
RTRIM(ISNULL([DIA].[ACTIONTYPE], '')) AS [ACTIONTYPE],
CASE WHEN [DIA].[ACTIONTYPE] = 'A'
THEN 'Action'
WHEN [DIA].[ACTIONTYPE] = 'N'
THEN 'Note'
WHEN [DIA].[ACTIONTYPE] = 'P'
THEN 'Appointment'
WHEN [DIA].[ACTIONTYPE] = 'R'
THEN 'Reminder'
WHEN [DIA].[ACTIONTYPE] = 'E'
THEN 'Email'
WHEN [DIA].[ACTIONTYPE] = 'T'
THEN 'Phone Message'
WHEN [DIA].[ACTIONTYPE] = 'D'
THEN 'Dictation'
WHEN [DIA].[ACTIONTYPE] = 'U'
THEN 'Undertaking'
WHEN [DIA].[ACTIONTYPE] = 'S'
THEN 'Statute Date'
WHEN [DIA].[ACTIONTYPE] = 'C'
THEN 'Critical Date'
WHEN [DIA].[ACTIONTYPE] = 'O'
THEN 'Court Date'
WHEN [DIA].[ACTIONTYPE] = 'M'
THEN 'Scanned Post/Mail'
WHEN RTRIM(ISNULL([DIA].[ACTIONTYPE], '')) = ''
THEN CASE WHEN convert(VARCHAR(1), CASE WHEN RTRIM(ISNULL([DIA].[EMAIL], 'N')) = 'Y' THEN 'Y' ELSE 'N' END) = 'Y'
THEN 'Email'
WHEN convert(VARCHAR(500), ISNULL([DX].[IMDocID], '')) <> ''
THEN 'IManage document'
ELSE 'Unknown' END
ELSE 'Action Type: ' + [DIA].[ACTIONTYPE] END AS [ActionTypeDescription],
RTRIM(ISNULL([DIA].[SOURCE], CASE WHEN [DIA].[ACTIONID] IS NULL THEN 'IManage' ELSE '' END)) AS [DOCUMENTSOURCE]
,Count(1) over() [TotalRecord],
[DAS].[SigningStatus],
[DIA].[LASTACCESSDATE] AS [LASTACCESSDATE],
[DAI].OriginalUniqueID AS [OriginalMatterLinkId]
FROM #DiaryTableList [DIA]
LEFT JOIN [DiaryAttachmentSelector] [DAS] ON [DAS].[TrackReference] = [DIA].[TrackReference]
--ON [DIA].[TrackReference] = [DDC].[TrackReference]
CROSS APPLY (SELECT ISNULL(MAX([DAV].[Version]), 0) + 1 AS [CurrentVersion]
FROM [dbo].[DiaryAttachmentVersioning] [DAV]
WHERE TRACKREFERENCE = [DIA].[TRACKREFERENCE]) [DV]
CROSS APPLY (SELECT CASE WHEN COUNT(1) = 0
THEN CONVERT(BIT, 0)
ELSE CONVERT(BIT, 1) END AS [IsShared]
FROM [dbo].[FileSharingDocuments] [FSD]
INNER JOIN [dbo].[FileSharingMapping] [FSM]
INNER JOIN [dbo].[FileSharingDocFolders] [FSDF]
ON [FSDF].[DocFolderID] = [FSM].[FileSharingDocFoldersID]
AND [FSDF].[Shared] = 1
ON [FSM].[id] = [FSD].[FileSharingMappingID]
AND [FSM].[FileSharingDocFoldersID] = @DocFolderID
WHERE [FSD].[TrackReference] = [DIA].[TrackReference]) [SHD]
LEFT OUTER JOIN [dbo].[DAIMXRef] [DX]
LEFT OUTER JOIN @IMDocTable [DL]
ON [DL].[IMDocID] = [DX].[IMDocID]
ON [DX].[TrackReference] = [DIA].[TrackReference]
LEFT OUTER JOIN [dbo].[DocumentClasses] DCL
ON convert(VARCHAR(10), ISNULL([DIA].[DocClass], '')) <> ''
AND DCL.[CLASSCODE] = CASE WHEN convert(VARCHAR(10), ISNULL([DIA].[DocClass], '')) = '' THEN convert(VARCHAR(20), ISNULL([DL].[IMClass], ''))
ELSE convert(VARCHAR(10), ISNULL([DIA].[DocClass], ''))
END
LEFT OUTER JOIN [dbo].[UNCAlias] [UNC]
ON SUBSTRING([DIA].[FilePath], 2, 1) = ':'
AND [UNC].[Drive] = SUBSTRING([DIA].[FilePath], 1, 1)
OUTER APPLY ( SELECT TOP 1
[MST].[TrackReference] AS [TrackReference],
[MST].[Type] AS [Type]
FROM #DiaryTableList [MST]
WHERE [MST].[diaryid] = [DIA].[ActionID] --AND [MST].[TrackReference] = [DA].[TrackReference]
ORDER BY CASE WHEN [MST].Type = 'MSG'
THEN 0
ELSE 1 END,
[MST].[TrackReference]) [OMS]
LEFT JOIN [dbo].[diaryAttachmentOriginalmatterlinks] [DAI] ON [DIA].[TrackReference] = [DAI].[TrackReference]
END
END
ELSE
BEGIN
SELECT @TOTALRECORDS = COUNT(1)
FROM dbo.[diary] [DIA]
INNER JOIN dbo.[DiaryAttachments] [DA] ON [DA].[DiaryID] = [DIA].[ACTIONID]
AND [DA].[CASECODE] = [DIA].[CASECODE]
WHERE [DIA].[ACTIONID] = @ActionID
AND
(
CASE WHEN @SearchText = '' THEN 1
WHEN
([DA].FILEPATH LIKE '%' + @SearchText + '%')
OR
(RTRIM(convert(VARCHAR(2000), ISNULL([DA].[Document], ''))) LIKE '%' + @SearchText + '%')
OR
([DA].[DATEENTERED] LIKE '%' + @SearchText + '%')
OR
([DA].[TYPE] LIKE '%' + @SearchText + '%')
OR
([DA].[DocClass] LIKE '%' + @SearchText + '%')
THEN 1
ELSE 0
END = 1
)
AND
(
ISNULL(@FileType, '') = ''
OR ([DA].[TYPE] IN (select FileType from @FileTypeList ))
)
AND
(
(CASE WHEN @DOCCLASSFilterCount = 0 THEN 0
WHEN [DA].[DocClass] 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 [DOCCLASS] from @CustomFilter WHERE [DOCCLASS] IS NOT NULL and [DOCCLASS] <>''),',','') + '' AS XML) AS x
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)) FILTERVALUETBLFnl) THEN 0
ELSE 1 END) = 0
AND
(CASE WHEN @TypeFilterCount = 0 THEN 0
WHEN [DA].[TYPE] 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 [TYPE] from @CustomFilter WHERE [TYPE] IS NOT NULL and [TYPE] <>''),',','') + '' AS XML) AS x
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)) FILTERVALUETBLFnl) THEN 0
ELSE 1 END) =0
AND
(CASE WHEN @AttachmentFilterCount = 0 THEN 0
WHEN [DIA].[Attachments] 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 [ATTACHMENT] from @CustomFilter WHERE [ATTACHMENT] IS NOT NULL and [ATTACHMENT] <>''),',','') + '' AS XML) AS x
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)) FILTERVALUETBLFnl) THEN 0
ELSE 1 END) = 0
AND
(CASE WHEN @SourceFilterCount = 0 THEN 0
WHEN (RTRIM(ISNULL([DA].[SOURCE], CASE WHEN [DIA].[ACTIONID] IS NULL THEN 'IManage' ELSE '' END))) 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 [SOURCE] from @CustomFilter WHERE [SOURCE] IS NOT NULL and [SOURCE] <>''),',','') + '' AS XML) AS x
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)) FILTERVALUETBLFnl) THEN 0
ELSE 1 END) = 0
)
SELECT
[DIA].[CASECODE],[DIA].[ACTIONID],[DIA].[DATE],[DIA].[TxmDate], [DIA].[EMAIL],[DIA].[ATTACHMENTS],[DIA].[EMAILADDRESS],[DIA].[ADDRESSTO],[DIA].[SUBJECT],[DIA].[DisplayText],
[DIA].[PROCESSTYPE],[DIA].[ACTIONTYPE],
[DA].[DIARYID],[DA].[TrackReference],[DA].[DocClass],[DA].[DATEENTERED],[DA].[TYPE],
[DA].[FILEPATH],[DA].[NAME],[DA].[SOURCE],
convert(VARCHAR(500), ISNULL([DX].[IMDocID], '')) AS [IMDocID],
convert(VARCHAR(20), ISNULL([DL].[IMClass], '')) AS [IMClass],
RTRIM(convert(VARCHAR(2000), ISNULL([DL].[Description], ISNULL([DA].[Document], '')))) AS [Document],
CASE WHEN [DIA].[ACTIONTYPE] = 'A'
THEN 'Action'
WHEN [DIA].[ACTIONTYPE] = 'N'
THEN 'Note'
WHEN [DIA].[ACTIONTYPE] = 'P'
THEN 'Appointment'
WHEN [DIA].[ACTIONTYPE] = 'R'
THEN 'Reminder'
WHEN [DIA].[ACTIONTYPE] = 'E'
THEN 'Email'
WHEN [DIA].[ACTIONTYPE] = 'T'
THEN 'Phone Message'
WHEN [DIA].[ACTIONTYPE] = 'D'
THEN 'Dictation'
WHEN [DIA].[ACTIONTYPE] = 'U'
THEN 'Undertaking'
WHEN [DIA].[ACTIONTYPE] = 'S'
THEN 'Statute Date'
WHEN [DIA].[ACTIONTYPE] = 'C'
THEN 'Critical Date'
WHEN [DIA].[ACTIONTYPE] = 'O'
THEN 'Court Date'
WHEN [DIA].[ACTIONTYPE] = 'M'
THEN 'Scanned Post/Mail'
WHEN RTRIM(ISNULL([DIA].[ACTIONTYPE], '')) = ''
THEN CASE WHEN convert(VARCHAR(1), CASE WHEN RTRIM(ISNULL([DIA].[EMAIL], 'N')) = 'Y' THEN 'Y' ELSE 'N' END) = 'Y'
THEN 'Email'
WHEN convert(VARCHAR(500), ISNULL([DX].[IMDocID], '')) <> ''
THEN 'IManage document'
ELSE 'Unknown' END
ELSE 'Action Type: ' + [DIA].[ACTIONTYPE] END AS [ActionTypeDescription],
CASE WHEN convert(VARCHAR(10), ISNULL([DA].[DocClass], '')) <> ''
THEN RTRIM(ISNULL(DCL.[CLASSDESCRIPTION], ''))
ELSE CASE WHEN [DCL].[ClassCode] = 'ATTNOTES'
THEN 'Attendance Notes'
WHEN [DCL].[ClassCode] = 'BILL'
THEN 'Bill'
WHEN [DCL].[ClassCode] = 'FAX'
THEN 'Fax'
WHEN [DCL].[ClassCode] = 'LEGAL'
THEN 'Legal'
WHEN [DCL].[ClassCode] = 'SCAN'
THEN 'Scan'
WHEN [DCL].[ClassCode] = 'COMPARE'
THEN 'Compare Document'
WHEN [DCL].[ClassCode] = 'DISCUSSION'
THEN 'Discussion'
WHEN [DCL].[ClassCode] = 'DOC'
THEN 'Document'
WHEN [DCL].[ClassCode] = 'E-MAIL'
THEN 'E-Mail'
WHEN [DCL].[ClassCode] = 'EVENT'
THEN 'Event'
WHEN [DCL].[ClassCode] = 'LETTER'
THEN 'Letter'
WHEN [DCL].[ClassCode] = 'MEMO'
THEN 'Memo'
WHEN [DCL].[ClassCode] = 'PAGE_ICON'
THEN 'Page Icon'
WHEN [DCL].[ClassCode] = 'TASK'
THEN 'Task'
WHEN [DCL].[ClassCode] = 'TEXT'
THEN 'Text File'
WHEN [DCL].[ClassCode] = 'WEBDOC'
THEN 'InfoLink Web Page'
ELSE ISNULL([DCL].[CLASSCODE], '') END END AS [DocumentClassDescription],
[DA].[LASTACCESSDATE] AS [LASTACCESSDATE],
[DAI].OriginalUniqueID AS [OriginalMatterLinkId]
--,Count(1) Over() as TotalRowsCount
INTO #DiaryTable
FROM dbo.[diary] [DIA]
INNER JOIN dbo.[DiaryAttachments] [DA] ON [DA].[DiaryID] = [DIA].[ACTIONID]
AND [DIA].CASECODE = [DA].CASECODE
LEFT OUTER JOIN [dbo].[DAIMXRef] [DX] ON [DX].[TrackReference] = [DA].[TrackReference]
LEFT OUTER JOIN @IMDocTable [DL]
ON [DL].[IMDocID] = [DX].[IMDocID]
LEFT OUTER JOIN [dbo].[DocumentClasses] DCL
ON convert(VARCHAR(10), ISNULL([DA].[DocClass], '')) <> ''
AND DCL.[CLASSCODE] = CASE WHEN convert(VARCHAR(10), ISNULL([DA].[DocClass], '')) = '' THEN convert(VARCHAR(20), ISNULL([DL].[IMClass], ''))
ELSE convert(VARCHAR(10), ISNULL([DA].[DocClass], ''))
END
LEFT JOIN [dbo].[diaryAttachmentOriginalmatterlinks] [DAI] ON [DA].[TrackReference] = [DAI].[TrackReference]
WHERE [DIA].[ACTIONID] = @ActionID AND
(
CASE WHEN @SearchText = '' THEN 1
WHEN
([DA].FILEPATH LIKE '%' + @SearchText + '%')
OR
([Document] LIKE '%' + @SearchText + '%')
OR
([DA].[DATEENTERED] LIKE '%' + @SearchText + '%')
OR
([DA].[TYPE] LIKE '%' + @SearchText + '%')
OR
([DA].[DocClass] LIKE '%' + @SearchText + '%')
THEN 1
ELSE 0
END = 1
)
AND
(
ISNULL(@FileType, '') = ''
OR ([DA].[TYPE] IN (select FileType from @FileTypeList ))
)
AND
(
(CASE WHEN @DOCCLASSFilterCount = 0 THEN 0
WHEN [DA].[DocClass] 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 [DOCCLASS] from @CustomFilter WHERE [DOCCLASS] IS NOT NULL and [DOCCLASS] <>''),',','') + '' AS XML) AS x
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)) FILTERVALUETBLFnl) THEN 0
ELSE 1 END) = 0
AND
(CASE WHEN @TypeFilterCount = 0 THEN 0
WHEN [DA].[TYPE] 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 [TYPE] from @CustomFilter WHERE [TYPE] IS NOT NULL and [TYPE] <>''),',','') + '' AS XML) AS x
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)) FILTERVALUETBLFnl) THEN 0
ELSE 1 END) =0
AND
(CASE WHEN @AttachmentFilterCount = 0 OR [Attachments] 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 [ATTACHMENT] from @CustomFilter WHERE [ATTACHMENT] IS NOT NULL and [ATTACHMENT] <>''),',','') + '' AS XML) AS x
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)) FILTERVALUETBLFnl) THEN 0
ELSE 1 END) = 0
AND
(CASE WHEN @SourceFilterCount = 0 OR [SOURCE] 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 [SOURCE] from @CustomFilter WHERE [SOURCE] IS NOT NULL and [SOURCE] <>''),',','') + '' AS XML) AS x
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)) FILTERVALUETBLFnl) THEN 0
ELSE 1 END) = 0
)
ORDER BY
CASE WHEN @SortDirection = 'ASC' AND @SortColumn!='RowNumber' THEN
CASE @SortColumn
WHEN 'Type' THEN convert(VARCHAR(20), UPPER(RTRIM(ISNULL([DA].[TYPE], ''))))
END
END,
CASE WHEN @SortDirection = 'DESC' AND @SortColumn!='RowNumber' THEN
CASE @SortColumn
WHEN 'Type' THEN convert(VARCHAR(20), UPPER(RTRIM(ISNULL([DA].[TYPE], ''))))
END
END DESC,
CASE WHEN @SortDirection = 'ASC' AND @SortColumn!='RowNumber' THEN
CASE @SortColumn
WHEN 'Document' THEN RTRIM(convert(VARCHAR(2000), ISNULL([DL].[Description], ISNULL([DA].[Document], ''))))
END
END,
CASE WHEN @SortDirection = 'DESC' AND @SortColumn!='RowNumber' THEN
CASE @SortColumn
WHEN 'Document' THEN RTRIM(convert(VARCHAR(2000), ISNULL([DL].[Description], ISNULL([DA].[Document], ''))))
END
END DESC,
CASE WHEN @SortDirection = 'ASC' AND @SortColumn!='RowNumber' THEN
CASE @SortColumn
WHEN 'DocumentClassDescription' THEN CASE WHEN convert(VARCHAR(10), ISNULL([DA].[DocClass], '')) <> ''
THEN RTRIM(ISNULL(DCL.[CLASSDESCRIPTION], ''))
ELSE CASE WHEN [DCL].[ClassCode] = 'ATTNOTES'
THEN 'Attendance Notes'
WHEN [DCL].[ClassCode] = 'BILL'
THEN 'Bill'
WHEN [DCL].[ClassCode] = 'FAX'
THEN 'Fax'
WHEN [DCL].[ClassCode] = 'LEGAL'
THEN 'Legal'
WHEN [DCL].[ClassCode] = 'SCAN'
THEN 'Scan'
WHEN [DCL].[ClassCode] = 'COMPARE'
THEN 'Compare Document'
WHEN [DCL].[ClassCode] = 'DISCUSSION'
THEN 'Discussion'
WHEN [DCL].[ClassCode] = 'DOC'
THEN 'Document'
WHEN [DCL].[ClassCode] = 'E-MAIL'
THEN 'E-Mail'
WHEN [DCL].[ClassCode] = 'EVENT'
THEN 'Event'
WHEN [DCL].[ClassCode] = 'LETTER'
THEN 'Letter'
WHEN [DCL].[ClassCode] = 'MEMO'
THEN 'Memo'
WHEN [DCL].[ClassCode] = 'PAGE_ICON'
THEN 'Page Icon'
WHEN [DCL].[ClassCode] = 'TASK'
THEN 'Task'
WHEN [DCL].[ClassCode] = 'TEXT'
THEN 'Text File'
WHEN [DCL].[ClassCode] = 'WEBDOC'
THEN 'InfoLink Web Page'
ELSE ISNULL([DCL].[CLASSCODE], '') END END
END
END,
CASE WHEN @SortDirection = 'DESC' AND @SortColumn!='RowNumber' THEN
CASE @SortColumn
WHEN 'DocumentClassDescription' THEN CASE WHEN convert(VARCHAR(10), ISNULL([DA].[DocClass], '')) <> ''
THEN RTRIM(ISNULL(DCL.[CLASSDESCRIPTION], ''))
ELSE CASE WHEN [DCL].[ClassCode] = 'ATTNOTES'
THEN 'Attendance Notes'
WHEN [DCL].[ClassCode] = 'BILL'
THEN 'Bill'
WHEN [DCL].[ClassCode] = 'FAX'
THEN 'Fax'
WHEN [DCL].[ClassCode] = 'LEGAL'
THEN 'Legal'
WHEN [DCL].[ClassCode] = 'SCAN'
THEN 'Scan'
WHEN [DCL].[ClassCode] = 'COMPARE'
THEN 'Compare Document'
WHEN [DCL].[ClassCode] = 'DISCUSSION'
THEN 'Discussion'
WHEN [DCL].[ClassCode] = 'DOC'
THEN 'Document'
WHEN [DCL].[ClassCode] = 'E-MAIL'
THEN 'E-Mail'
WHEN [DCL].[ClassCode] = 'EVENT'
THEN 'Event'
WHEN [DCL].[ClassCode] = 'LETTER'
THEN 'Letter'
WHEN [DCL].[ClassCode] = 'MEMO'
THEN 'Memo'
WHEN [DCL].[ClassCode] = 'PAGE_ICON'
THEN 'Page Icon'
WHEN [DCL].[ClassCode] = 'TASK'
THEN 'Task'
WHEN [DCL].[ClassCode] = 'TEXT'
THEN 'Text File'
WHEN [DCL].[ClassCode] = 'WEBDOC'
THEN 'InfoLink Web Page'
ELSE ISNULL([DCL].[CLASSCODE], '') END END
END
END DESC,
CASE WHEN @SortDirection = 'ASC' AND @SortColumn!='RowNumber' THEN
CASE @SortColumn
WHEN 'DocumentDate' THEN convert(DATETIME, [DA].[DATEENTERED])
END
END,
CASE WHEN @SortDirection = 'DESC' AND @SortColumn!='RowNumber' THEN
CASE @SortColumn
WHEN 'DocumentDate' THEN convert(DATETIME, [DA].[DATEENTERED])
END
END DESC, [DA].[TrackReference] DESC
OFFSET @PageSize * (@PageNo - 1) ROWS
FETCH NEXT @PageSize ROWS ONLY OPTION (RECOMPILE);
--SELECT * from #DiaryTable
--select * from @IMDocTable
INSERT
INTO @res
([ActionId],
[TrackReference],
[IMDocID],
[IMClass],
[DocClass],
[Document],
[DiaryDate],
[DocumentDate],
[EmailSent],
[TYPE],
[Attachments],
[EMAIL],
[EmailFrom],
[EmailTo],
[CurrentVersion],
[IsInDocFolder],
[IsShared],
[DocumentClassDescription],
[UniqueId],
[FilePath],
[Subject],
[ShortText],
[NAME],
[ProcessType],
[ACTIONTYPE],
[ActionTypeDescription],
[DOCUMENTSOURCE]
,[TotalRecord],
SigningStatus,
[LASTACCESSDATE],
[OriginalMatterLinkId]
)
SELECT convert(INT, [DIA].[DIARYID]) AS [ActionID],
convert(INT, [DIA].[TrackReference]) AS [TrackReference],
convert(VARCHAR(500), ISNULL([DX].[IMDocID], '')) AS [IMDocID],
convert(VARCHAR(20), ISNULL([DL].[IMClass], '')) AS [IMClass],
convert(VARCHAR(10), ISNULL([DIA].[DocClass], '')) AS [DocClass],
RTRIM(convert(VARCHAR(2000), ISNULL([DL].[Description], ISNULL([DIA].[Document], '')))) AS [Document],
convert(DATETIME, [DIA].[DATE]) As [DiaryDate],
convert(DATETIME, [DIA].[DATEENTERED]) AS [DocumentDate],
convert(DATETIME, ISNULL([DIA].[TxmDate], [DIA].[Date])) As [EMAILSENT],
convert(VARCHAR(20), UPPER(RTRIM(ISNULL([DIA].[TYPE], '')))) AS [TYPE],
-- CASE WHEN [DIA].[ATTACHMENTS] = 'Y' THEN 'Y' ELSE 'N' END AS [Attachments],
(CASE WHEN ISNULL( convert(VARCHAR(1), CASE WHEN RTRIM(ISNULL([DIA].[EMAIL], 'N')) = 'Y' THEN 'Y' ELSE 'N' END), '') = 'Y'
AND convert(INT, [DIA].[TrackReference]) = [OMS].[TrackReference]
THEN (CASE WHEN [DIA].[ATTACHMENTS] = 'Y' THEN 'Y' ELSE 'N' END )
WHEN ISNULL(convert(VARCHAR(20), UPPER(RTRIM(ISNULL([DIA].[TYPE], '')))), '') = 'MSG'
AND convert(INT, [DIA].[TrackReference]) = [OMS].[TrackReference]
THEN (CASE WHEN [DIA].[ATTACHMENTS] = 'Y' THEN 'Y' ELSE 'N' END)
ELSE 'N' END) AS [Attachments],
--convert(VARCHAR(1), CASE WHEN RTRIM(ISNULL([DIA].[EMAIL], 'N')) = 'Y' THEN 'Y' ELSE 'N' END) AS [EMAIL],
(CASE WHEN convert(VARCHAR(1), CASE WHEN RTRIM(ISNULL([DIA].[EMAIL], 'N')) = 'Y' THEN 'Y' ELSE 'N' END) = 'Y'
AND convert(INT, [DIA].[TrackReference]) = [OMS].[TrackReference]
THEN 'Y'
WHEN ISNULL(convert(VARCHAR(20), UPPER(RTRIM(ISNULL([DIA].[TYPE], '')))), '') = 'MSG'
AND convert(INT, [DIA].[TrackReference]) = [OMS].[TrackReference]
THEN 'Y'
ELSE 'N' END) AS [EMAIL],
-- convert(VARCHAR(200), RTRIM(ISNULL([DIA].[EMAILADDRESS], ''))) AS [EMAILFROM],
RTRIM(ISNULL(CONVERT(VARCHAR(200), SUBSTRING(convert(VARCHAR(200), RTRIM(ISNULL([DIA].[EMAILADDRESS], ''))), 1, 200)), '')) AS [EMAILFROM],
--RTRIM(ISNULL(CONVERT(VARCHAR(1000), [DIA].[ADDRESSTO]), '')) AS [EMAILTO],
RTRIM(ISNULL(CONVERT(VARCHAR(1000), SUBSTRING(RTRIM(ISNULL(CONVERT(VARCHAR(1000), [DIA].[ADDRESSTO]), '')), 1, 1000)), '')) AS [EMAILTO],
[DV].[CurrentVersion],
[IND].[InDocFolder],
[SHD].[IsShared],
CASE WHEN convert(VARCHAR(10), ISNULL([DIA].[DocClass], '')) <> ''
THEN RTRIM(ISNULL(DCL.[CLASSDESCRIPTION], ''))
ELSE CASE WHEN [DCL].[ClassCode] = 'ATTNOTES'
THEN 'Attendance Notes'
WHEN [DCL].[ClassCode] = 'BILL'
THEN 'Bill'
WHEN [DCL].[ClassCode] = 'FAX'
THEN 'Fax'
WHEN [DCL].[ClassCode] = 'LEGAL'
THEN 'Legal'
WHEN [DCL].[ClassCode] = 'SCAN'
THEN 'Scan'
WHEN [DCL].[ClassCode] = 'COMPARE'
THEN 'Compare Document'
WHEN [DCL].[ClassCode] = 'DISCUSSION'
THEN 'Discussion'
WHEN [DCL].[ClassCode] = 'DOC'
THEN 'Document'
WHEN [DCL].[ClassCode] = 'E-MAIL'
THEN 'E-Mail'
WHEN [DCL].[ClassCode] = 'EVENT'
THEN 'Event'
WHEN [DCL].[ClassCode] = 'LETTER'
THEN 'Letter'
WHEN [DCL].[ClassCode] = 'MEMO'
THEN 'Memo'
WHEN [DCL].[ClassCode] = 'PAGE_ICON'
THEN 'Page Icon'
WHEN [DCL].[ClassCode] = 'TASK'
THEN 'Task'
WHEN [DCL].[ClassCode] = 'TEXT'
THEN 'Text File'
WHEN [DCL].[ClassCode] = 'WEBDOC'
THEN 'InfoLink Web Page'
ELSE ISNULL([DCL].[CLASSCODE], '') END END AS [DocumentClassDescription],
@uniqueid As uniqueid,
CASE WHEN [UNC].[UNC] IS NULL THEN RTRIM(ISNULL([DIA].[FILEPATH], ''))
ELSE [UNC].[UNC] + SUBSTRING(RTRIM(ISNULL([DIA].[FILEPATH], '')), 3, LEN(RTRIM(ISNULL([DIA].[FILEPATH], ''))) - 2) END AS [FILEPATH],
-- [RES].[EMAILSENT],
RTRIM(ISNULL(CONVERT(VARCHAR(1000),
SUBSTRING(RTRIM(ISNULL(CONVERT(VARCHAR(2000),
[DIA].[SUBJECT]),'')),
1,
1000)),
'')) AS [SUBJECT],
RTRIM(ISNULL(CONVERT(VARCHAR(120),
SUBSTRING(RTRIM(ISNULL(CONVERT(VARCHAR(120),
[DIA].[DisplayText]),'')),
1,
120)),
'')) AS [ShortText],
RTRIM(ISNULL([DIA].[NAME], '')) AS [NAME],
RTRIM(ISNULL([DIA].[PROCESSTYPE], '')) AS [PROCESSTYPE],
RTRIM(ISNULL([DIA].[ACTIONTYPE], '')) AS [ACTIONTYPE],
CASE WHEN [DIA].[ACTIONTYPE] = 'A'
THEN 'Action'
WHEN [DIA].[ACTIONTYPE] = 'N'
THEN 'Note'
WHEN [DIA].[ACTIONTYPE] = 'P'
THEN 'Appointment'
WHEN [DIA].[ACTIONTYPE] = 'R'
THEN 'Reminder'
WHEN [DIA].[ACTIONTYPE] = 'E'
THEN 'Email'
WHEN [DIA].[ACTIONTYPE] = 'T'
THEN 'Phone Message'
WHEN [DIA].[ACTIONTYPE] = 'D'
THEN 'Dictation'
WHEN [DIA].[ACTIONTYPE] = 'U'
THEN 'Undertaking'
WHEN [DIA].[ACTIONTYPE] = 'S'
THEN 'Statute Date'
WHEN [DIA].[ACTIONTYPE] = 'C'
THEN 'Critical Date'
WHEN [DIA].[ACTIONTYPE] = 'O'
THEN 'Court Date'
WHEN [DIA].[ACTIONTYPE] = 'M'
THEN 'Scanned Post/Mail'
WHEN RTRIM(ISNULL([DIA].[ACTIONTYPE], '')) = ''
THEN CASE WHEN convert(VARCHAR(1), CASE WHEN RTRIM(ISNULL([DIA].[EMAIL], 'N')) = 'Y' THEN 'Y' ELSE 'N' END) = 'Y'
THEN 'Email'
WHEN convert(VARCHAR(500), ISNULL([DX].[IMDocID], '')) <> ''
THEN 'IManage document'
ELSE 'Unknown' END
ELSE 'Action Type: ' + [DIA].[ACTIONTYPE] END AS [ActionTypeDescription],
RTRIM(ISNULL([DIA].[SOURCE], CASE WHEN [DIA].[ACTIONID] IS NULL THEN 'IManage' ELSE '' END)) AS [DOCUMENTSOURCE]
,Count(1) over() [TotalRecord],
[DAS].[SigningStatus],
[DIA].[LASTACCESSDATE] AS [LASTACCESSDATE],
[DAI].OriginalUniqueID AS [OriginalMatterLinkId]
FROM #DiaryTable [DIA]
LEFT JOIN [DiaryAttachmentSelector] [DAS] ON [DAS].[TrackReference] = [DIA].[TrackReference]
CROSS APPLY (SELECT CASE WHEN COUNT(1) = 0
THEN CONVERT(BIT, 0)
ELSE CONVERT(BIT, 1) END AS [InDocFolder]
FROM [dbo].[DocFolderDocuments] [DFD]
WHERE [DFD].[TrackReference] = [DIA].[TrackReference]) [IND]
CROSS APPLY (SELECT CASE WHEN COUNT(1) = 0
THEN CONVERT(BIT, 0)
ELSE CONVERT(BIT, 1) END AS [IsShared]
FROM [dbo].[FileSharingDocuments] [FSD]
INNER JOIN [dbo].[FileSharingMapping] [FSM]
INNER JOIN [dbo].[FileSharingDocFolders] [FSDF]
ON [FSDF].[DocFolderID] = [FSM].[FileSharingDocFoldersID]
AND [FSDF].[Shared] = 1
ON [FSM].[id] = [FSD].[FileSharingMappingID]
WHERE [FSD].[TrackReference] = [DIA].[TrackReference]) [SHD]
CROSS APPLY (SELECT ISNULL(MAX([DAV].[Version]), 0) + 1 AS [CurrentVersion]
FROM [dbo].[DiaryAttachmentVersioning] [DAV]
WHERE TRACKREFERENCE = [DIA].[TRACKREFERENCE]) [DV]
LEFT OUTER JOIN [dbo].[DAIMXRef] [DX]
LEFT OUTER JOIN @IMDocTable [DL]
ON [DL].[IMDocID] = [DX].[IMDocID]
ON [DX].[TrackReference] = [DIA].[TrackReference]
LEFT OUTER JOIN [dbo].[DocumentClasses] DCL
ON convert(VARCHAR(10), ISNULL([DIA].[DocClass], '')) <> ''
AND DCL.[CLASSCODE] = CASE WHEN convert(VARCHAR(10), ISNULL([DIA].[DocClass], '')) = '' THEN convert(VARCHAR(20), ISNULL([DL].[IMClass], ''))
ELSE convert(VARCHAR(10), ISNULL([DIA].[DocClass], ''))
END
LEFT OUTER JOIN [dbo].[UNCAlias] [UNC]
ON SUBSTRING([DIA].[FilePath], 2, 1) = ':'
AND [UNC].[Drive] = SUBSTRING([DIA].[FilePath], 1, 1)
OUTER APPLY ( SELECT TOP 1
[MST].[TrackReference] AS [TrackReference],
[MST].[Type] AS [Type]
FROM #DiaryTable [MST]
WHERE [MST].[diaryid] = [DIA].[ActionID] --AND [MST].[TrackReference] = [DA].[TrackReference]
ORDER BY CASE WHEN [MST].Type = 'MSG'
THEN 0
ELSE 1 END,
[MST].[TrackReference]) [OMS]
LEFT JOIN [dbo].[diaryAttachmentOriginalmatterlinks] [DAI] ON [DIA].[TrackReference] = [DAI].[TrackReference]
END
/*
INSERT
INTO @classes
([KeyhouseClass],
[IMClass],
[ClassCode],
[ClassDescription])
SELECT [CLS].[KeyhouseClass],
[CLS].[IMClass],
[CLS].[ClassCode],
CASE WHEN [CLS].[KeyhouseClass] = 1
THEN RTRIM(ISNULL(DCL.[CLASSDESCRIPTION], ''))
ELSE CASE WHEN [CLS].[ClassCode] = 'ATTNOTES'
THEN 'Attendance Notes'
WHEN [CLS].[ClassCode] = 'BILL'
THEN 'Bill'
WHEN [CLS].[ClassCode] = 'FAX'
THEN 'Fax'
WHEN [CLS].[ClassCode] = 'LEGAL'
THEN 'Legal'
WHEN [CLS].[ClassCode] = 'SCAN'
THEN 'Scan'
WHEN [CLS].[ClassCode] = 'COMPARE'
THEN 'Compare Document'
WHEN [CLS].[ClassCode] = 'DISCUSSION'
THEN 'Discussion'
WHEN [CLS].[ClassCode] = 'DOC'
THEN 'Document'
WHEN [CLS].[ClassCode] = 'E-MAIL'
THEN 'E-Mail'
WHEN [CLS].[ClassCode] = 'EVENT'
THEN 'Event'
WHEN [CLS].[ClassCode] = 'LETTER'
THEN 'Letter'
WHEN [CLS].[ClassCode] = 'MEMO'
THEN 'Memo'
WHEN [CLS].[ClassCode] = 'PAGE_ICON'
THEN 'Page Icon'
WHEN [CLS].[ClassCode] = 'TASK'
THEN 'Task'
WHEN [CLS].[ClassCode] = 'TEXT'
THEN 'Text File'
WHEN [CLS].[ClassCode] = 'WEBDOC'
THEN 'InfoLink Web Page'
ELSE ISNULL([CLS].[CLASSCODE], '') END END AS [ClassDescription]
FROM (SELECT DISTINCT
1 AS [KeyhouseClass],
0 AS [IMClass],
CONVERT(VARCHAR(20), [RES].[DocClass]) AS [ClassCode]
FROM @res [RES]
WHERE ISNULL([RES].[DocClass], '') <> ''
UNION
SELECT DISTINCT
0 AS [KeyhouseClass],
1 AS [IMClass],
CONVERT(VARCHAR(20), [RES].[IMClass]) AS [ClassCode]
FROM @res [RES]
WHERE ISNULL([RES].[IMClass], '') <> '') [CLS]
LEFT OUTER JOIN [dbo].[DocumentClasses] DCL
ON [CLS].[KeyhouseClass] = 1
AND DCL.[CLASSCODE] = [CLS].[ClassCode]
*/
/*
UPDATE [RES]
SET [RES].[EMAIL] = CASE WHEN ( ISNULL([RES].[EMAIL], '') = 'Y'
AND [RES].[TrackReference] = [OMS].[TrackReference])
THEN 'Y'
WHEN ( ISNULL([RES].[TYPE], '') = 'MSG'
AND [RES].[TrackReference] = [OMS].[TrackReference])
THEN 'Y'
ELSE 'N' END,
[RES].[Attachments] = CASE WHEN ( ISNULL([RES].[EMAIL], '') = 'Y'
AND [RES].[TrackReference] = [OMS].[TrackReference])
THEN [RES].[Attachments]
WHEN ( ISNULL([RES].[TYPE], '') = 'MSG'
AND [RES].[TrackReference] = [OMS].[TrackReference])
THEN [RES].[Attachments]
ELSE 'N' END
FROM @res [RES]
OUTER APPLY ( SELECT TOP 1
[MST].[TrackReference] AS [TrackReference],
[MST].[Type] AS [Type]
FROM [dbo].[diaryattachments] [MST]
WHERE [MST].[diaryid] = [RES].[ActionID]
ORDER BY CASE WHEN [MST].Type = 'MSG'
THEN 0
ELSE 1 END,
[MST].[TrackReference]) [OMS]
WHERE [RES].[ACTIONID] IS NOT NULL
*/
/*
SELECT [RES].[id],
CONVERT(VARCHAR(15),dbo.KAAS_FNConvertUTCDateToTargetTimeZone([RES].[DiaryDate], @LoggedInHandler), 23) AS [DATE],
ISNULL([RES].[ActionID], 0) AS [ACTIONID],
CONVERT(VARCHAR(15), dbo.KAAS_FNConvertUTCDateToTargetTimeZone([RES].[DocumentDate], @LoggedInHandler), 23) AS [DocumentDate],
[RES].[IMDocID],
[RES].[IMClass],
[RES].[DocumentClassDescription],
[RES].[DocClass],
[RES].[Document],
ISNULL([RES].[TrackReference], 0) AS [TRACKREFERENCE],
[RES].[TYPE] AS [TYPE],
[RES].[Attachments] AS [Attachments],
[RES].[EMAIL],
RTRIM(ISNULL(CONVERT(VARCHAR(200), SUBSTRING(ISNULL([DIA].[EMAILADDRESS], [RES].[EMAILFROM]), 1, 200)), '')) AS [EMAILADDRESS],
RTRIM(ISNULL(CONVERT(VARCHAR(1000), SUBSTRING(ISNULL([DIA].[ADDRESSTO], [RES].[EMAILTO]), 1, 1000)), '')) AS [ADDRESSTO],
[RES].[EMAILSENT],
RTRIM(ISNULL(CONVERT(VARCHAR(1000),
SUBSTRING(RTRIM(ISNULL(CONVERT(VARCHAR(2000),
[DIA].[SUBJECT]),'')),
1,
1000)),
'')) AS [SUBJECT],
RTRIM(ISNULL(CONVERT(VARCHAR(120),
SUBSTRING(RTRIM(ISNULL(CONVERT(VARCHAR(120),
[DIA].[DisplayText]),'')),
1,
120)),
'')) AS [ShortText],
RTRIM(ISNULL([DAT].[NAME], '')) AS [NAME],
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([DIA].[PROCESSTYPE], '')) AS [PROCESSTYPE],
RTRIM(ISNULL([DIA].[ACTIONTYPE], '')) AS [ACTIONTYPE],
CASE WHEN [DIA].[ACTIONTYPE] = 'A'
THEN 'Action'
WHEN [DIA].[ACTIONTYPE] = 'N'
THEN 'Note'
WHEN [DIA].[ACTIONTYPE] = 'P'
THEN 'Appointment'
WHEN [DIA].[ACTIONTYPE] = 'R'
THEN 'Reminder'
WHEN [DIA].[ACTIONTYPE] = 'E'
THEN 'Email'
WHEN [DIA].[ACTIONTYPE] = 'T'
THEN 'Phone Message'
WHEN [DIA].[ACTIONTYPE] = 'D'
THEN 'Dictation'
WHEN [DIA].[ACTIONTYPE] = 'U'
THEN 'Undertaking'
WHEN [DIA].[ACTIONTYPE] = 'S'
THEN 'Statute Date'
WHEN [DIA].[ACTIONTYPE] = 'C'
THEN 'Critical Date'
WHEN [DIA].[ACTIONTYPE] = 'O'
THEN 'Court Date'
WHEN [DIA].[ACTIONTYPE] = 'M'
THEN 'Scanned Post/Mail'
WHEN RTRIM(ISNULL([DIA].[ACTIONTYPE], '')) = ''
THEN CASE WHEN [RES].[EMAIL] = 'Y'
THEN 'Email'
WHEN [RES].[IMDocID] <> ''
THEN 'IManage document'
ELSE 'Unknown' END
ELSE 'Action Type: ' + [DIA].[ACTIONTYPE] END AS [ActionTypeDescription],
RTRIM(ISNULL([DAT].[SOURCE], CASE WHEN [DIA].[ACTIONID] IS NULL THEN 'IManage' ELSE '' END)) AS [DOCUMENTSOURCE],
[RES].[CurrentVersion],
[RES].[IsInDocFolder],
[RES].[IsShared],
[RES].UniqueId
FROM @res [RES]
LEFT OUTER JOIN [dbo].[DiaryAttachments] [DAT]
INNER JOIN [dbo].[diary] [DIA]
ON [DIA].[ACTIONID] = [DAT].[DIARYID]
LEFT OUTER JOIN [dbo].[UNCAlias] [UNC]
ON SUBSTRING([DAT].[FilePath], 2, 1) = ':'
AND [UNC].[Drive] = SUBSTRING([DAT].[FilePath], 1, 1)
ON [DAT].[TRACKREFERENCE] = [RES].[TrackReference]
--2018-02-26 START
--LEFT OUTER JOIN @classes [CLS]
-- ON [CLS].[KeyhouseClass] = CASE WHEN ISNULL([RES].[IMClass], '') = '' THEN 1 ELSE 0 END
-- AND [CLS].[IMClass] = CASE WHEN ISNULL([RES].[IMClass], '') = '' THEN 0 ELSE 1 END
-- AND [CLS].[ClassCode] = CASE WHEN ISNULL([RES].[IMClass], '') = '' THEN [RES].[DocClass] ELSE [RES].[IMClass] END
LEFT OUTER JOIN @classes [CLS]
ON [CLS].[KeyhouseClass] = CASE WHEN ISNULL([RES].[DocClass], '') = '' THEN 0 ELSE 1 END
AND [CLS].[IMClass] = CASE WHEN ISNULL([RES].[DocClass], '') = '' THEN 1 ELSE 0 END
AND [CLS].[ClassCode] = CASE WHEN ISNULL([RES].[DocClass], '') = '' THEN [RES].[IMClass] ELSE [RES].[DocClass] END
--2018-02-26 END
--WHERE [RES].id between (@PageNo * @PageSize) + 1 and ((@PageNo * @PageSize) + 1 + @PageSize) - 1
ORDER BY ID
*/
SELECT
[id],
CONVERT(VARCHAR(15),dbo.KAAS_FNConvertUTCDateToTargetTimeZone([DiaryDate], @LoggedInHandler), 23) AS [DiaryDate],
CONVERT(VARCHAR(10),dbo.KAAS_FNConvertUTCDateToTargetTimeZone([DiaryDate], @LoggedInHandler), 108) AS [DiaryTime],
[ACTIONID],
CONVERT(VARCHAR(15), dbo.KAAS_FNConvertUTCDateToTargetTimeZone([DocumentDate], @LoggedInHandler), 23) AS [DocumentDate],
CONVERT(VARCHAR(10), dbo.KAAS_FNConvertUTCDateToTargetTimeZone([DocumentDate], @LoggedInHandler), 108) AS [DocumentTime],
[IMDocID],
[IMClass],
[DocumentClassDescription],
[DocClass],
[Document],
[TRACKREFERENCE],
[TYPE],
[Attachments],
[EMAIL],
[EMAILFROM] AS [EMAILADDRESS],
[EMAILTO] AS [ADDRESSTO],
[EMAILSENT],
[SUBJECT],
[ShortText],
[NAME],
[FILEPATH],
[PROCESSTYPE],
[ACTIONTYPE],
[ActionTypeDescription],
[DOCUMENTSOURCE],
[CurrentVersion],
[IsInDocFolder],
[IsShared],
UniqueId,
[SigningStatus],
CONVERT(VARCHAR(15),dbo.KAAS_FNConvertUTCDateToTargetTimeZone([LASTACCESSDATE], @LoggedInHandler), 23) AS [LastAccessDate],
[OriginalMatterLinkId]
from @res
ORDER BY TrackReference DESC
-- WHERE ID between (@PageNo * @PageSize) + 1 and ((@PageNo * @PageSize) + 1 + @PageSize) - 1
-- ORDER BY ID
--OFFSET @PageSize * (@PageNo - 1) ROWS
-- FETCH NEXT @PageSize ROWS ONLY OPTION (RECOMPILE);
--OPTION (KEEPFIXED PLAN)
/* IF Exists(SELECT TOP 1 TotalRecord FROM @res)
BEGIN
SELECT TOP 1 ISNULL(TotalRecord,0) FROM @res
END
ELSE
BEGIN
SELECT 0 as TotalRecord
END
*/
SELECT @TOTALRECORDS AS TotalRecord
/*
SELECT top 1 count(1) Over() AS [TotalRecord]
FROM [dbo].[diary] [DIA]
INNER JOIN [dbo].[DiaryAttachments] [DAT]
ON [DAT].[DiaryID] = [DIA].[ActionID]
WHERE [DIA].[CASECODE] = @matter
*/
IF ISNULL(@DocFolderID, 0) <> 0
BEGIN
SELECT * FROM [dbo].[KAAS_FN_GetDocFolders](@matter, @DocFolderID, 1, 1) WHERE parent <> -1
END
ELSE
BEGIN
SELECT TOP 0
CONVERT(INT, 0) AS [seq],
CONVERT(INT, 0) AS [id],
CONVERT(INT, 0) AS [parent],
CONVERT(VARCHAR(500), '') AS [foldername],
CONVERT(INT, 0) AS [foldericon],
CONVERT(VARBINARY(MAX), NULL) AS [folderimage],
CONVERT(BIT, 0) AS [foldershared],
CONVERT(BIT, 0) AS [folderisours],
CONVERT(INT, 0) AS [documentcount]
END
END
GO
IF OBJECT_ID(N'BRIEF_GetFileData', N'P') IS NOT NULL
BEGIN
DROP PROCEDURE [dbo].[BRIEF_GetFileData]
END
GO
CREATE PROCEDURE [dbo].[BRIEF_GetFileData] (@FilePath NVARCHAR(MAX))
AS
/*******************************************************************************************************
* *
* *
* Stored Procedure Name : [dbo].[BRIEF_GetFileData] *
* Copied from : [dbo].[KAAS_GetFileData] *
* *
* Modification History: *
* 2022-07-01 Balamurugan C Created *
*******************************************************************************************************/
BEGIN
DECLARE @NCOMMAND NVARCHAR(MAX)
DECLARE @result INT
DECLARE @UNCFilePath NVARCHAR(256)
SET @FilePath = REPLACE(@FilePath, '''', '''''');
SET @UNCFilePath = (SELECT dbo.BRIEF_FN_GetUNCPathFromFile(@FilePath))
SET @NCOMMAND = 'EXEC Master.dbo.xp_fileexist ''' + @UNCFilePath + ''', @result OUTPUT PRINT @result'
EXECUTE sp_executesql @NCOMMAND, N'@result INT OUTPUT', @result = @result OUTPUT
IF (@result = 1)
BEGIN
SET @NCOMMAND = 'DECLARE @file AS VARBINARY(MAX);
SELECT @file = CAST(bulkcolumn AS VARBINARY(MAX))
FROM OPENROWSET (BULK ''' + @UNCFilePath + ''', SINGLE_BLOB) AS x;
SELECT @file AS FileData'
EXECUTE sp_executesql @NCOMMAND
END
END
GO
IF EXISTS
(SELECT TOP 1 1
FROM SYS.[objects] SO
WHERE SO.[name] = N'KAAS_GetMatterBasedOnPrivileges'
AND SO.[type] = 'P')
BEGIN
DROP PROCEDURE [dbo].[KAAS_GetMatterBasedOnPrivileges]
END
GO
CREATE PROCEDURE [dbo].[KAAS_GetMatterBasedOnPrivileges]
(@HandlerCode VARCHAR(10))
AS
/*******************************************************************************************************
* *
* Stored Procedure Name : [dbo].[KAAS_GetMatterBasedOnPrivileges] *
* *
* Description : To Fetch the matter list based on the handler Privileges it is used fetch brief *
* application to fetch brief list created by matter *
* *
* Modification History: *
* 2023-07-10 Balamurugan.C Created *
* 2023-09-14 Balamurugan.C Modified - Changed Permission Logic *
*******************************************************************************************************/
BEGIN
SET NOCOUNT ON
DECLARE @HandlerBitWiseId BIGINT
SET @HandlerBitWiseId = (SELECT [BitWiseID] FROM [dbo].[HandlerNETGroupMembership] WHERE Handler = @HandlerCode)
SELECT [MAT].[Code] 'MatterCode'
FROM [dbo].[Matters] [MAT]
LEFT JOIN [dbo].[MatterNETPrivileges] [MNP]
ON [MAT].[Code] = [MNP].[Matter]
WHERE (
@HandlerBitWiseId = -1
OR ([MNP].[Privileges] & @HandlerBitWiseId) <> 0
OR [MNP].[Matter] IS NULL
)
AND [MAT].[Closed] = 'N'
SET NOCOUNT OFF
END
GO