IF EXISTS (SELECT TOP 1 1 FROM SYS.[objects] SO WHERE SO.[name] = N'KAAS_LS_GetCaseContactsList' AND SO.[type] = 'P') BEGIN DROP PROCEDURE [dbo].[KAAS_LS_GetCaseContactsList] END GO CREATE PROCEDURE [dbo].[KAAS_LS_GetCaseContactsList] (@CaseCode VARCHAR(20), @ForEmail BIT = 1, @Number INT = 0, @Status VARCHAR(1) = '', @SearchText VARCHAR(4000) = NULL ) AS /************************************************************************************************************* * Stored Procedure Name: [KAAS_GetCaseContactsListForLegalSearch] * Copied From : [KAAS_GetCaseContactsList] * List Case Contacts for Outlook Add-in * * ******************* * * * * * PLEASE NOTE: * If you make any changes to this procedure's parameters, please inform Pino Carafa as * * ============ * this will impact the Outlook Add-in! * * * * ******************* * * Modification History * 2015-12-03 Pino Make it usable for Framework, too * 2016-07-21 Suriya M KEYD-3783 - When you add a new strong room Deed file to a case, the Retired * Associates are showing in the "From Whom" & "To Whom" fields of "DEED" tab. * 2016-07-22 Pino Set default value for Status to ensure Outlook Add-in remains unaffected * 2018-05-01 Pino Carafa KEYD-4573 Add Secondary Email * 2019-12-04 Pino Carafa Return CC CONTYPE rather than CAN TYPE in first query * 2019-12-16 Pino Carafa Return Occupation * 2020-06-03 John Ginnane KEYD-6531 - Added mobile no to output * 2020-07-14 John Ginnane KEYD-6531 - Added SourceTable and SourceCode to output * 2020-08-25 Pino Carafa KEYD-6753 - Added Title to output * 2020-09-24 Arun Vasu KEYD-6648 - Added Work phone number to output * 2021-03-11 Arun Vasu Have get the FeeEarner, Partner label from setting table ref: 11634 * 2021-03-12 Arun Vasu Changed the logic the get the handler label values and set for handlers label ref: 11634 * 2021-06-18 Aakif Created * 2021-06-22 Aakif Fixed issue to fetch published contact * 2022-02-02 Vinodhkumar.M Created - Case Contacts list based on Matter Code for Saas Web App * 2022-02-03 Vinodhkumar.M Modified - added Id from ClientContacts * 2022-02-08 Vinodhkumar.M Modified - uncomment the query of ClientContacts, clients * 2022-02-24 Vinodhkumar.M Modified - Display all contacts whether it is published or not * 2023-10-05 Nithyanandham.M Added Address column with the return list *************************************************************************************************************/ BEGIN SET NOCOUNT ON DECLARE @FromWhom VARCHAR(10) DECLARE @FeeEarnerLabel VARCHAR(20) DECLARE @Partner VARCHAR(20) --Getting the fee earner, and Partner label value from setting SELECT @FeeEarnerLabel = [Keyvalue] FROM [Settings] WHERE [KeyName] = 'FeeEarnerLabel' SELECT @Partner = [Keyvalue] FROM [Settings] WHERE [KeyName] = 'PartnerLabel' SELECT * FROM (SELECT CONVERT(VARCHAR(10), RTRIM(ISNULL([CAN].[Code], ''))) AS [Code], CONVERT(VARCHAR(300), RTRIM(ISNULL([CAN].[Name], ''))) AS [Name], CONVERT(VARCHAR(50), RTRIM(ISNULL([CAN].[Company], ''))) AS [Company], CONVERT(VARCHAR(100), RTRIM(ISNULL([CAN].[EMAIL], ''))) AS [Email], CONVERT(VARCHAR(100), RTRIM(ISNULL([CAN].[OCCUPATION], ''))) AS [Occupation], CONVERT(VARCHAR(10), RTRIM(ISNULL([CC].[NAMECODE], ''))) AS [NAMECODE], CONVERT(VARCHAR(20), RTRIM(ISNULL([CC].[CASECODE], ''))) AS [CASECODE], CONVERT(VARCHAR(20), RTRIM(ISNULL([CC].[CONTYPE], ''))) AS [TYPE], --PINO 2019-12-04, WAS [CAN].[TYPE] CONVERT(VARCHAR(100), RTRIM(ISNULL([CAN].[TITLE], ''))) AS [Title], CONVERT(VARCHAR(50), ISNULL([CAN].[MOBILENO], '')) AS [MobileNo], 'CaseAssociatesNames' AS [SourceTable], [CAN].[Code] AS [SourceCode], CONVERT(VARCHAR(50), ISNULL([CAN].[PHONENO], '')) AS [Work], CONVERT(VARCHAR(50), '') AS [ClientNumberContact], [CAN].[Address] AS [Address] FROM [dbo].[CaseContacts] [CC] INNER JOIN [dbo].[CaseAssociatesNames] [CAN] ON [CAN].[Code] = [CC].[NameCODE] AND ( @ForEmail = 0 OR RTRIM(ISNULL([CAN].[EMAIL], '')) <> '') WHERE [CC].[CASECODE] = @CaseCode -- AND ISNULL([CC].PUBLISH, '') = 'Y' AND ( ISNULL([CC].Retired,'N') <> 'Y' OR [CC].NAMECODE = CASE @Number WHEN 0 THEN '' ELSE @FromWhom END) -- UNION SELECT CONVERT(VARCHAR(10), RTRIM(ISNULL([CTC].[CLCODE], ''))) AS [Code], CONVERT(VARCHAR(300), RTRIM(ISNULL([CTC].[FIRSTNAME], '')) + ' ' + RTRIM(ISNULL([CTC].[SURNAME], ''))) AS [Name], CONVERT(VARCHAR(50), RTRIM(ISNULL([CTC].[CLNAMECON], ''))) AS [Company], CONVERT(VARCHAR(100), RTRIM(ISNULL([CTC].[EmailAddress], ''))) AS [Email], CONVERT(VARCHAR(100), RTRIM(ISNULL([CTC].[Occupation], ''))) AS [Occupation], CONVERT(VARCHAR(10), '') AS [NAMECODE], CONVERT(VARCHAR(20), @CaseCode) AS [CaseCode], CONVERT(VARCHAR(20), 'Client Contact') AS [Type], CONVERT(VARCHAR(100), RTRIM(ISNULL([CTC].[CLTITLE], ''))) AS [TITLE], CONVERT(VARCHAR(50), ISNULL([CTC].[Mobile], '')) AS [MobileNo], 'ClientContacts' AS [SourceTable], CONVERT(VARCHAR(50), [CTC].[id]) AS [SourceCode], CONVERT(VARCHAR(50),ISNULL([CTC].[WorkPhone],'')) AS [Work], CONVERT(VARCHAR(50),ISNULL([CTC].[CLNUMCONTACT],'')) AS [ClientNumberContact], [CTC].[Address] AS [Address] FROM [dbo].[matters] [MAT] INNER JOIN [dbo].[ClientContacts] [CTC] ON [CTC].[CLCODE] = [MAT].[ClientCode] AND ( @ForEmail = 0 OR RTRIM(ISNULL([CTC].[EmailAddress], '')) <> '') WHERE [MAT].[Code] = @CaseCode -- UNION SELECT CONVERT(VARCHAR(10), RTRIM(ISNULL([CAN].[Code], ''))) AS [Code], CONVERT(VARCHAR(300), RTRIM(ISNULL([CAC].[Name], ''))) AS [Name], CONVERT(VARCHAR(50), RTRIM(ISNULL([CAN].[Company], ''))) AS [Company], CONVERT(VARCHAR(100), RTRIM(ISNULL([CAC].[EMAIL], ''))) AS [Email], CONVERT(VARCHAR(100), RTRIM(ISNULL([CAN].[OCCUPATION], ''))) AS [Occupation], CONVERT(VARCHAR(10), RTRIM(ISNULL([CC].[NAMECODE], ''))) AS [NAMECODE], CONVERT(VARCHAR(20), RTRIM(ISNULL([CC].[CASECODE], ''))) AS [CaseCode], CONVERT(VARCHAR(20), RTRIM(ISNULL([CC].[CONTYPE], ''))) AS [Type], CONVERT(VARCHAR(100), RTRIM(ISNULL([CAC].[JOBTITLE], ''))) AS [Title], CONVERT(VARCHAR(50), ISNULL([CAC].[MOBILE], '')) AS [MobileNo], 'CaseAssociatesContacts' AS [SourceTable], CONVERT(VARCHAR(50), [CAC].[KEYID]) AS [SourceCode], CONVERT(VARCHAR(50), ISNULL([CAC].[PHONE],'')) AS [Work], CONVERT(VARCHAR(50), '') AS [ClientNumberContact], [CAC].[Address] AS [Address] FROM [dbo].[CaseContacts] [CC] INNER JOIN [dbo].[CaseAssociatesNames] [CAN] INNER JOIN [dbo].[CaseAssociatesContacts] [CAC] ON [CAC].[NAMECODE] = [CAN].[Code] AND ( @ForEmail = 0 OR RTRIM(ISNULL([CAC].[EMAIL], '')) <> '') ON [CAN].[Code] = [CC].[NameCODE] WHERE [CC].[CASECODE] = @CaseCode -- AND ISNULL([CC].PUBLISH, '') = 'Y' --- UNION SELECT CONVERT(VARCHAR(10), RTRIM(ISNULL([CTC].[Code], ''))) AS [Code], CONVERT(VARCHAR(300), RTRIM(ISNULL([CTC].[Name], ''))) AS [Name], CONVERT(VARCHAR(50), '') AS [Company], CONVERT(VARCHAR(100), RTRIM(ISNULL([CTC].[email], ''))) AS [Email], CONVERT(VARCHAR(100), RTRIM(ISNULL([CTC].[Occupation], ''))) AS [Occupation], CONVERT(VARCHAR(10), '') AS [NAMECODE], CONVERT(VARCHAR(20), @CaseCode) AS [CaseCode], CONVERT(VARCHAR(20), 'Client') AS [Type], CONVERT(VARCHAR(100), '') AS [Title], --Not sure there is a column representing this in Contacts or client CONVERT(VARCHAR(50), ISNULL([CLI].[CLMOBNO], '')) AS [MobileNo], 'client' AS [SourceTable], [CTC].[Code] AS [SourceCode], CONVERT(VARCHAR(50), ISNULL([CTC].[Tel], '')) AS [Work], CONVERT(VARCHAR(50), '') AS [ClientNumberContact], [CTC].[Address] AS [Address] FROM [dbo].[matters] [MAT] INNER JOIN [dbo].[Contacts] [CTC] ON [CTC].[Code] = [MAT].[ClientCode] AND ( @ForEmail = 0 OR RTRIM(ISNULL([CTC].[email], '')) <> '') LEFT OUTER JOIN [dbo].[client] AS [CLI] ON [CLI].[CLCODE] = [CTC].[Code] WHERE [MAT].[Code] = @CaseCode --- UNION SELECT CONVERT(VARCHAR(10), RTRIM(ISNULL([HAN].[Code], ''))) AS [Code], CONVERT(VARCHAR(300), RTRIM(ISNULL([HAN].[Name], ''))) AS [Name], CONVERT(VARCHAR(50), '') AS [Company], CONVERT(VARCHAR(100), RTRIM(ISNULL([HAN].[email], ''))) AS [Email], CONVERT(VARCHAR(100), '') AS [Occupation], CONVERT(VARCHAR(10), '') AS [NAMECODE], CONVERT(VARCHAR(20), @CaseCode) AS [CaseCode], CONVERT(VARCHAR(20), (CASE WHEN ColumnName='FECode' THEN ISNULL(@FeeEarnerLabel, 'Fee Earner') WHEN ColumnName='Partner' THEN ISNULL(@Partner, 'Partner') ELSE ColumnName END)) AS [Type], CONVERT(VARCHAR(100), '') AS [Title], CONVERT(VARCHAR(50), ISNULL([HAN].[PhoneNumber], '')) AS [MobileNo], 'Handlers' AS [SourceTable], [HAN].[Code] AS [SourceCode], CONVERT(VARCHAR(50), ISNULL([HAN].[Phoneno], '')) AS [Work], CONVERT(VARCHAR(50), '') AS [ClientNumberContact], '' AS [Address] FROM (SELECT [UNPVT].[Handlers] AS [HandlerValue], [UNPVT].[Handlerslist] AS [ColumnName] FROM [dbo].[matters] [MAT] UNPIVOT ( [Handlers] FOR [Handlerslist] IN ([MAT].[FECode], [MAT].[Partner], [MAT].[Secretary])) [UNPVT] WHERE [UNPVT].[Code] = @CaseCode AND [UNPVT].[Handlers] <> '') [HANDLERLIST] INNER JOIN [Handlers] [HAN] ON [HAN].[Code] = [HANDLERLIST].[HandlerValue] UNION SELECT CONVERT(VARCHAR(10), RTRIM(ISNULL([CTC].[Code], ''))) AS [Code], CONVERT(VARCHAR(300), RTRIM(ISNULL([CTC].[Name], ''))) AS [Name], CONVERT(VARCHAR(50), '') AS [Company], CONVERT(VARCHAR(100), RTRIM(ISNULL([CTC].[SecondaryEmail], ''))) AS [Email], CONVERT(VARCHAR(100), RTRIM(ISNULL([CTC].[Occupation], ''))) AS [Occupation], CONVERT(VARCHAR(10), '') AS [NAMECODE], CONVERT(VARCHAR(20), @CaseCode) AS [CaseCode], CONVERT(VARCHAR(20), 'Client') AS [Type], CONVERT(VARCHAR(100), '') AS [Title], --Not sure there is a column representing this in Contacts or client CONVERT(VARCHAR(50), ISNULL([CLI].[CLMOBNO], '')) AS [MobileNo], 'client' AS [SourceTable], [CTC].[Code] AS [SourceCode], CONVERT(VARCHAR(50), ISNULL([CTC].[Tel], '')) AS [Work], CONVERT(VARCHAR(50), '') AS [ClientNumberContact], '' AS [Address] FROM [dbo].[matters] [MAT] INNER JOIN [dbo].[Contacts] [CTC] ON [CTC].[Code] = [MAT].[ClientCode] AND ( @ForEmail = 0 OR RTRIM(ISNULL([CTC].[SecondaryEmail], '')) <> '') LEFT OUTER JOIN [dbo].[client] AS [CLI] ON [CLI].[CLCODE] = [CTC].[Code] WHERE [MAT].[Code] = @CaseCode) [RES] LEFT OUTER JOIN [dbo].[AssociateTypes] [AST] ON [AST].[Code] = [RES].[TYPE] WHERE --[RES].[TYPE] <> 'client' --AND ( (RTRIM(LTRIM(ISNULL(@SearchText, ''''))) = '''') OR (RTRIM(LTRIM(@SearchText)) = '''') OR ( [Name] LIKE '%' + RTRIM(LTRIM(@SearchText)) + '%' OR [TYPE] LIKE '%' + RTRIM(LTRIM(@SearchText)) + '%' OR [Email] LIKE '%' + RTRIM(LTRIM(@SearchText)) + '%' OR [MobileNo] LIKE '%' + RTRIM(LTRIM(@SearchText)) + '%' OR [Work] LIKE '%' + RTRIM(LTRIM(@SearchText)) + '%' ) ) ORDER BY CASE WHEN [RES].[Name] = '' THEN [RES].[Company] ELSE [RES].[Name] END, [RES].[TYPE] SET NOCOUNT OFF END GO IF EXISTS (SELECT TOP 1 1 FROM SYS.[objects] SO WHERE SO.[name] = N'KAAS_LS_GetDocumentsIM' AND SO.[type] = 'P') BEGIN DROP PROCEDURE [dbo].[KAAS_LS_GetDocumentsIM] END GO CREATE PROCEDURE [dbo].[KAAS_LS_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_LS_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 * * 2023-10-17 Nithyanandham.M Copied entire SP and replicating for Legal Search and Fetch Only PDF and Img Files * ************************************************************************************************************************/ 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 WHERE [TYPE] = 'PDF' or [TYPE]='JPG' 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 EXISTS (SELECT TOP 1 1 FROM SYS.[objects] SO WHERE SO.[name] = N'KAAS_LS_GetFolioList' AND SO.[type] = 'P') BEGIN DROP PROCEDURE [dbo].[KAAS_LS_GetFolioList] END GO CREATE PROCEDURE [dbo].[KAAS_LS_GetFolioList] (@CaseCode VARCHAR(50), @UDFName VARCHAR(50)) AS /**************************************************************************************************************** * * Stored Procedure Name - KAAS_GetFolioList * * This stored procedure used to fetch the Folio List * * * Modification History * 2023-10-04 Nithyanandham.M Created ****************************************************************************************************************/ BEGIN SELECT [CaseCode] , [UDFFile] , [UDFName] , [SeqNo] , [DateValue] , [NumberValue] , [Text1] as Folio FROM [CaseUDFAnswers] WHERE [udfName] = @UDFName and [CaseCode] = @CaseCode END GO IF EXISTS (SELECT TOP 1 1 FROM SYS.[objects] SO WHERE SO.[name] = N'KAAS_LS_GetUDFForFolio' AND SO.[type] = 'P') BEGIN DROP PROCEDURE [dbo].[KAAS_LS_GetUDFForFolio] END GO CREATE PROCEDURE [dbo].[KAAS_LS_GetUDFForFolio] (@KeyName VARCHAR(100) ) AS /************************************************************************************************************* * Stored Procedure Name: [KAAS_LS_GetUDFForFolio] * Get Folio settings from the Settings table using Keyname * * ******************* * * * * * PLEASE NOTE: * If you make any changes to this procedure's parameters, please inform Pino Carafa as * * ============ * this will impact the Outlook Add-in! * * * * ******************* * * Modification History * 2023-10-09 Nithyanandham.M Created *************************************************************************************************************/ BEGIN SELECT [KeyName], [KeyValue] FROM [Settings] WHERE [KeyName] = @KeyName END GO