IF EXISTS
(SELECT TOP 1 1
FROM SYS.[objects] SO
WHERE SO.[name] = N'KEYHM_GetDocumentsIM'
AND SO.[type] = 'P')
BEGIN
DROP PROCEDURE [dbo].[KEYHM_GetDocumentsIM]
END
GO
CREATE PROCEDURE
[dbo].[KEYHM_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) = ''
)
AS
/************************************************************************************************************************
* *
* [dbo].[KEYHM_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-12-04 Kowshik.R Mobile documents page special character search and.docx file type issue is fixed *
************************************************************************************************************************/
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)
,[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)
DECLARE @FileTypeList TABLE
([FileType] VARCHAR(20) PRIMARY KEY)
INSERT INTO @FileTypeList
SELECT
RTRIM(LTRIM([Item]))
FROM
KEYHM_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))
DECLARE @DOCCLASSFilterCount INT = 0;
DECLARE @TypeFilterCount 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) values (@FilterValue,null)
ELSE
insert into @CustomFilter (DOCCLASS,TYPE) values (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 <>'')
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 ))
)
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].[CaseCode] = @matter
AND
(
CASE WHEN @SearchText = '' THEN 1
WHEN
([DA].FILEPATH 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 ))
)
SELECT @TOTALRECORDS = Count(1)+ @TOTALRECORDS FROM @res
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]
--,Count(1) Over() as TotalRowsCount
INTO #DiaryTables
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]
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
WHERE [DIA].[CaseCode] = @matter
AND
(
CASE WHEN @SearchText = '' THEN 1
WHEN
([DA].FILEPATH LIKE '%' + @SearchText + '%')
OR
([DA].[DATEENTERED] LIKE '%' + @SearchText + '%')
OR
([DA].[TYPE] LIKE '%' + @SearchText + '%')
OR
([DA].[DocClass] LIKE '%' + @SearchText + '%')
OR
([DA].[DOCUMENT] LIKE '%' + @SearchText + '%')
THEN 1
ELSE 0
END = 1
)
AND
(
ISNULL(@FileType, '') = ''
OR ([DA].[TYPE] IN (select FileType from @FileTypeList ))
)
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 #DiaryTables
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]
)
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
FROM #DiaryTables [DIA]
-- 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]
-- OFFSET @PageSize * (@PageNo - 1) ROWS
-- FETCH NEXT @PageSize ROWS ONLY OPTION (RECOMPILE);
END
ELSE IF ISNULL(@DocFolderID, 0) <> 0
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
([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 ))
)
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]
-- , 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
WHERE [DDC].[DocFolderID] = @DocFolderID
AND
(
CASE WHEN @SearchText = '' THEN 1
WHEN
([DA].FILEPATH 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 ))
)
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]
)
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]
FROM #DiaryTableList [DIA]
--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]
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
([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 ))
)
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]
--,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
WHERE [DIA].[ACTIONID] = @ActionID AND
(
CASE WHEN @SearchText = '' THEN 1
WHEN
([DA].FILEPATH 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 ))
)
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]
)
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]
FROM #DiaryTable [DIA]
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]
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.KEYHM_FNConvertUTCDateToTargetTimeZone([RES].[DiaryDate], @LoggedInHandler), 23) AS [DATE],
ISNULL([RES].[ActionID], 0) AS [ACTIONID],
CONVERT(VARCHAR(15), dbo.KEYHM_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.KEYHM_FNConvertUTCDateToTargetTimeZone([DiaryDate], @LoggedInHandler), 23) AS [DATE],
[ACTIONID],
CONVERT(VARCHAR(15), dbo.KEYHM_FNConvertUTCDateToTargetTimeZone([DocumentDate], @LoggedInHandler), 23) AS [DocumentDate],
[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
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].[KEYHM_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