IF EXISTS (SELECT TOP 1 1 FROM SYS.[objects] SO WHERE SO.[name] = N'KAAS_LS_GetCaseContactsAndFolioList' AND SO.[type] = 'P') BEGIN DROP PROCEDURE [dbo].[KAAS_LS_GetCaseContactsAndFolioList] END GO CREATE PROCEDURE [dbo].[KAAS_LS_GetCaseContactsAndFolioList] (@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_LS_GetCaseContactsAndFolioList] * 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 * 2025-05-28 Ponni M Created for adding the Folio list with contacts 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], LTRIM(RTRIM(ISNULL([CAN].[FORENAME], ''))) AS [FirstName], LTRIM(RTRIM(ISNULL([CAN].[SURNAME], ''))) AS [Surname], ISNULL([CAN].[SALUTATION], '') AS [Salutaion], ISNULL([CAN].[PostalCode], '') AS [PostalCode], 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], [CAN].[LEGALADDRESS] AS [LegalAddress] 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], LTRIM(RTRIM(ISNULL([CTC].[FIRSTNAME], ''))) AS [FirstName], LTRIM(RTRIM(ISNULL([CTC].[SURNAME], ''))) AS [Surname], ISNULL([CTC].[CLSALUTE], '') AS [Salutaion], ISNULL([CTC].[PostalCode], '') AS [PostalCode], 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], '' AS [LegalAddress] 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], LTRIM(CASE WHEN ISNULL([CAC].[Name], '') = '' THEN '' -- Handle empty or NULL names WHEN CHARINDEX(' ', [CAC].[Name]) = 0 THEN [CAC].[Name] -- One word only, treat as FirstName ELSE SUBSTRING([CAC].[Name], 1, LEN([CAC].[Name]) - CHARINDEX(' ', REVERSE([CAC].[Name]))) -- Get FirstName END) AS [FirstName], LTRIM(CASE WHEN ISNULL([CAC].[Name], '') = '' THEN '' -- Handle empty or NULL names WHEN CHARINDEX(' ', [CAC].[Name]) = 0 THEN '' -- One word only, no Surname ELSE REVERSE(LEFT(REVERSE([CAC].[Name]), CHARINDEX(' ', REVERSE([CAC].[Name])) - 1)) -- Get Surname END) AS [Surname], ISNULL([CAC].[SALUTATION], '') AS [Salutaion], ISNULL([CAC].[PostalCode], '') AS [PostalCode], 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], '' AS [LegalAddress] 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], LTRIM(CASE WHEN ISNULL([CTC].[Name], '') = '' THEN '' -- Handle empty or NULL names WHEN CHARINDEX(' ', [CTC].[Name]) = 0 THEN [CTC].[Name] -- One word only, treat as FirstName ELSE SUBSTRING([CTC].[Name], 1, LEN([CTC].[Name]) - CHARINDEX(' ', REVERSE([CTC].[Name]))) -- Get FirstName END) AS [FirstName], LTRIM(CASE WHEN ISNULL([CTC].[Name], '') = '' THEN '' -- Handle empty or NULL names WHEN CHARINDEX(' ', [CTC].[Name]) = 0 THEN '' -- One word only, no Surname ELSE REVERSE(LEFT(REVERSE([CTC].[Name]), CHARINDEX(' ', REVERSE([CTC].[Name])) - 1)) -- Get Surname END) AS [Surname], ISNULL([CTC].[Salut], '') AS [Salutaion], ISNULL([CTC].[PostalCode], '') AS [PostalCode], 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], '' AS [LegalAddress] 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], LTRIM(CASE WHEN ISNULL([HAN].[Name], '') = '' THEN '' -- Handle empty or NULL names WHEN CHARINDEX(' ', [HAN].[Name]) = 0 THEN [HAN].[Name] -- One word only, treat as FirstName ELSE SUBSTRING([HAN].[Name], 1, LEN([HAN].[Name]) - CHARINDEX(' ', REVERSE([HAN].[Name]))) -- Get FirstName END) AS [FirstName], LTRIM(CASE WHEN ISNULL([HAN].[Name], '') = '' THEN '' -- Handle empty or NULL names WHEN CHARINDEX(' ', [HAN].[Name]) = 0 THEN '' -- One word only, no Surname ELSE REVERSE(LEFT(REVERSE([HAN].[Name]), CHARINDEX(' ', REVERSE([HAN].[Name])) - 1)) -- Get Surname END) AS [Surname], '' AS [Salutaion], '' AS [PostalCode], 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], '' AS [LegalAddress] 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], LTRIM(CASE WHEN ISNULL([CTC].[Name], '') = '' THEN '' -- Handle empty or NULL names WHEN CHARINDEX(' ', [CTC].[Name]) = 0 THEN [CTC].[Name] -- One word only, treat as FirstName ELSE SUBSTRING([CTC].[Name], 1, LEN([CTC].[Name]) - CHARINDEX(' ', REVERSE([CTC].[Name]))) -- Get FirstName END) AS [FirstName], LTRIM(CASE WHEN ISNULL([CTC].[Name], '') = '' THEN '' -- Handle empty or NULL names WHEN CHARINDEX(' ', [CTC].[Name]) = 0 THEN '' -- One word only, no Surname ELSE REVERSE(LEFT(REVERSE([CTC].[Name]), CHARINDEX(' ', REVERSE([CTC].[Name])) - 1)) -- Get Surname END) AS [Surname], ISNULL([CTC].[Salut], '') AS [Salutaion], ISNULL([CTC].[PostalCode], '') AS [PostalCode], 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], [CTC].[Address] AS [Address], [CLI].[CLLEGALADDR] AS [LegalAddress] 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] --For fetching folio details SELECT [CUF].[CASECODE], RTRIM(LTRIM(REPLACE(REPLACE([SS3].[value], '(', ''), ')', ''))) AS [Folio] FROM [dbo].[SystemUserDefinedFields] [SUF] INNER JOIN [dbo].[CaseUDFAnswers] [CUF] ON [CUF].[UDFFILE] = [SUF].[FILEPREFIX] AND [CUF].[UDFNAME] = [SUF].[FIELDNAME] CROSS APPLY string_split(LTRIM(RTRIM([CUF].[TEXT1])), ',') [SS1] CROSS APPLY string_split(REPLACE([SS1].[value], ' and ', '&'), '&') [SS2] CROSS APPLY string_split(LTRIM(RTRIM([SS2].[value])), ' ') [SS3] WHERE [SUF].[PROMPT] like '%folio%' AND ISNULL([CUF].[TEXT1], '') <> '' AND LEN(RTRIM(LTRIM(REPLACE(REPLACE([SS3].[value], '(', ''), ')', '')))) > 3 AND RTRIM(LTRIM(REPLACE(REPLACE([SS3].[value], '(', ''), ')', ''))) NOT IN ('(part)', '(par', '(part', 'part', 'par', 'pt', 'of', 'co', '', '-', 'dealing') AND RTRIM(LTRIM(REPLACE(REPLACE([SS3].[value], '(', ''), ')', ''))) NOT LIKE '????%' AND RTRIM(LTRIM(REPLACE(REPLACE([SS3].[value], '(', ''), ')', ''))) NOT LIKE 'xxxx%' AND RTRIM(LTRIM(REPLACE(REPLACE([SS3].[value], '(', ''), ')', ''))) NOT LIKE '€%' AND LEN(RTRIM(LTRIM(REPLACE(REPLACE(REPLACE([SS3].[value], '(', ''), ')', ''), 'part', '')))) < 10    AND CASECODE = @CaseCode ORDER BY [CUF].[CASECODE] SET NOCOUNT OFF END GO 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 * 2024-10-22 Aakif Included FirstName, Surname, Salut and PostalCode in the dataset * 2024-04-04 Nithyanandham.M Updated Column Names(Name,Type) *************************************************************************************************************/ 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], LTRIM(RTRIM(ISNULL([CAN].[FORENAME], ''))) AS [FirstName], LTRIM(RTRIM(ISNULL([CAN].[SURNAME], ''))) AS [Surname], ISNULL([CAN].[SALUTATION], '') AS [Salutaion], ISNULL([CAN].[PostalCode], '') AS [PostalCode], 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], [CAN].[LEGALADDRESS] AS [LegalAddress] 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], LTRIM(RTRIM(ISNULL([CTC].[FIRSTNAME], ''))) AS [FirstName], LTRIM(RTRIM(ISNULL([CTC].[SURNAME], ''))) AS [Surname], ISNULL([CTC].[CLSALUTE], '') AS [Salutaion], ISNULL([CTC].[PostalCode], '') AS [PostalCode], 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], '' AS [LegalAddress] 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], LTRIM(CASE WHEN ISNULL([CAC].[Name], '') = '' THEN '' -- Handle empty or NULL names WHEN CHARINDEX(' ', [CAC].[Name]) = 0 THEN [CAC].[Name] -- One word only, treat as FirstName ELSE SUBSTRING([CAC].[Name], 1, LEN([CAC].[Name]) - CHARINDEX(' ', REVERSE([CAC].[Name]))) -- Get FirstName END) AS [FirstName], LTRIM(CASE WHEN ISNULL([CAC].[Name], '') = '' THEN '' -- Handle empty or NULL names WHEN CHARINDEX(' ', [CAC].[Name]) = 0 THEN '' -- One word only, no Surname ELSE REVERSE(LEFT(REVERSE([CAC].[Name]), CHARINDEX(' ', REVERSE([CAC].[Name])) - 1)) -- Get Surname END) AS [Surname], ISNULL([CAC].[SALUTATION], '') AS [Salutaion], ISNULL([CAC].[PostalCode], '') AS [PostalCode], 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], '' AS [LegalAddress] 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], LTRIM(CASE WHEN ISNULL([CTC].[Name], '') = '' THEN '' -- Handle empty or NULL names WHEN CHARINDEX(' ', [CTC].[Name]) = 0 THEN [CTC].[Name] -- One word only, treat as FirstName ELSE SUBSTRING([CTC].[Name], 1, LEN([CTC].[Name]) - CHARINDEX(' ', REVERSE([CTC].[Name]))) -- Get FirstName END) AS [FirstName], LTRIM(CASE WHEN ISNULL([CTC].[Name], '') = '' THEN '' -- Handle empty or NULL names WHEN CHARINDEX(' ', [CTC].[Name]) = 0 THEN '' -- One word only, no Surname ELSE REVERSE(LEFT(REVERSE([CTC].[Name]), CHARINDEX(' ', REVERSE([CTC].[Name])) - 1)) -- Get Surname END) AS [Surname], ISNULL([CTC].[Salut], '') AS [Salutaion], ISNULL([CTC].[PostalCode], '') AS [PostalCode], 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], '' AS [LegalAddress] 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], LTRIM(CASE WHEN ISNULL([HAN].[Name], '') = '' THEN '' -- Handle empty or NULL names WHEN CHARINDEX(' ', [HAN].[Name]) = 0 THEN [HAN].[Name] -- One word only, treat as FirstName ELSE SUBSTRING([HAN].[Name], 1, LEN([HAN].[Name]) - CHARINDEX(' ', REVERSE([HAN].[Name]))) -- Get FirstName END) AS [FirstName], LTRIM(CASE WHEN ISNULL([HAN].[Name], '') = '' THEN '' -- Handle empty or NULL names WHEN CHARINDEX(' ', [HAN].[Name]) = 0 THEN '' -- One word only, no Surname ELSE REVERSE(LEFT(REVERSE([HAN].[Name]), CHARINDEX(' ', REVERSE([HAN].[Name])) - 1)) -- Get Surname END) AS [Surname], '' AS [Salutaion], '' AS [PostalCode], 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], '' AS [LegalAddress] 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], LTRIM(CASE WHEN ISNULL([CTC].[Name], '') = '' THEN '' -- Handle empty or NULL names WHEN CHARINDEX(' ', [CTC].[Name]) = 0 THEN [CTC].[Name] -- One word only, treat as FirstName ELSE SUBSTRING([CTC].[Name], 1, LEN([CTC].[Name]) - CHARINDEX(' ', REVERSE([CTC].[Name]))) -- Get FirstName END) AS [FirstName], LTRIM(CASE WHEN ISNULL([CTC].[Name], '') = '' THEN '' -- Handle empty or NULL names WHEN CHARINDEX(' ', [CTC].[Name]) = 0 THEN '' -- One word only, no Surname ELSE REVERSE(LEFT(REVERSE([CTC].[Name]), CHARINDEX(' ', REVERSE([CTC].[Name])) - 1)) -- Get Surname END) AS [Surname], ISNULL([CTC].[Salut], '') AS [Salutaion], ISNULL([CTC].[PostalCode], '') AS [PostalCode], 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], [CTC].[Address] AS [Address], [CLI].[CLLEGALADDR] AS [LegalAddress] 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 OBJECT_ID(N'KAAS_LS_GetOpenMatterDetails',N'P')IS NOT NULL DROP PROCEDURE [dbo].[KAAS_LS_GetOpenMatterDetails] GO CREATE PROCEDURE [dbo].[KAAS_LS_GetOpenMatterDetails] ( @Search NVARCHAR(MAX), @PageNumber INT, @PageSize INT, @ColumnNames VARCHAR(50) = '', @FilterValues VARCHAR(50) = '', @orderby NVARCHAR(50)= '', @order NVARCHAR(4) = '', @Mode VARCHAR(10) = 'Grid' ) AS /************************************************************************************ * * Fetching Open Matter Details * * Stored Procedure Name : [dbo].[KAAS_LS_GetOpenMatterDetails] * * Copied from : [dbo].[KAAS_OffsetGetOpenMatterDetails] * * Modification History: * 2024-07-25 Ponni M Created * * 2024-07-25 Ponni M #18734 - Created to Fetch the Browse Matter details for LAWLINK ************************************************************************************/ BEGIN DECLARE @closed NCHAR(1) DECLARE @howmany INT DECLARE @handler NVARCHAR(10) DECLARE @which INT DECLARE @BitWiseID BIGINT DECLARE @searchtext VARCHAR(MAX) DECLARE @searchonlycode VARCHAR(3) DECLARE @departmentdesc VARCHAR(50) DECLARE @department VARCHAR(5) DECLARE @worktypedesc VARCHAR(50) DECLARE @worktype VARCHAR(5) DECLARE @filecolourdesc VARCHAR(20) DECLARE @filecolour VARCHAR(3) DECLARE @feecode VARCHAR(10) DECLARE @includename BIT DECLARE @includedescription BIT DECLARE @includeaddress BIT DECLARE @includeemail BIT DECLARE @includecode BIT DECLARE @includeoldref BIT DECLARE @includeuser1 BIT DECLARE @andorname VARCHAR(3) DECLARE @andornamevalue VARCHAR(200) DECLARE @andordescription VARCHAR(3) DECLARE @andordescriptionvalue VARCHAR(200) DECLARE @andoraddress VARCHAR(3) DECLARE @andoraddressvalue VARCHAR(200) DECLARE @andorcode VARCHAR(3) DECLARE @andorcodevalue VARCHAR(200) DECLARE @andorfeecode VARCHAR(3) DECLARE @andorfeecodevalue VARCHAR(200) DECLARE @andoroldref VARCHAR(3) DECLARE @andoroldrefvalue VARCHAR(200) DECLARE @andordept VARCHAR(3) DECLARE @andordeptvalue VARCHAR(200) DECLARE @andoruser1 VARCHAR(3) DECLARE @andoruser1value VARCHAR(200) DECLARE @andoruser2 VARCHAR(3) DECLARE @andoruser2value VARCHAR(200) DECLARE @andoruser3 VARCHAR(3) DECLARE @andoruser3value VARCHAR(200) DECLARE @andoryourref VARCHAR(3) DECLARE @andoryourrefvalue VARCHAR(200) DECLARE @andorwtype VARCHAR(3) DECLARE @andorwtypevalue VARCHAR(200) DECLARE @iSL INT DECLARE @StartRow INT; DECLARE @EndRow INT; --FOR DEFAULT DATE; USE OF PARAMETERIZATION DECLARE @DefaultDate VARCHAR(8) = '18000101'; DECLARE @DefaultDaybookDate VARCHAR(8) = '19000101'; SET NOCOUNT ON SET @PageNumber = ISNULL(@PageNumber, 0) - 1; -- 0 BASED INDEX IF(@PageNumber > -1) BEGIN SET @StartRow = ((@PageNumber) * @PageSize) + 1; SET @EndRow = (@StartRow + @PageSize) - 1; END BEGIN TRY EXEC sp_xml_preparedocument @iSL OUTPUT, @Search END TRY BEGIN CATCH EXEC sp_xml_preparedocument @iSL OUTPUT, N'' END CATCH SELECT @closed = CASE ISNULL(SRC.[closed], N'A') WHEN N'Y' THEN N'Y' WHEN N'B' THEN N'B' WHEN N'N' THEN N'N' WHEN N'A' THEN N'A' END, @orderby = ISNULL(SRC.[orderby], N''), @order = CASE ISNULL(SRC.[order], N'DESC') WHEN N'DESC' THEN N'DESC' ELSE N'ASC' END, @howmany = ISNULL(SRC.[howmany], 500), @handler = ISNULL(SRC.[handler], N''), @which = CASE ISNULL(SRC.[which], 0) WHEN 1 THEN 1 --My Matters WHEN 2 THEN 2 --Recent Matters ELSE 0 END, --All Matters @searchtext = ISNULL(SRC.[search], ''), @searchonlycode = ISNULL(SRC.[searchonlycode], ''), @departmentdesc = ISNULL(SRC.[dept], ''), @worktypedesc = ISNULL(SRC.[wtype], ''), @filecolourdesc = ISNULL(SRC.[colour], ''), @feecode = ISNULL(SRC.[feecodefilter], ''), @includeaddress = ISNULL(SRC.[includeaddress], 0), @includeemail = ISNULL(SRC.[includeemail], 0), @includecode = ISNULL(SRC.[includecode], 0), @includedescription = ISNULL(SRC.[includedescription], 0), @includename = ISNULL(SRC.[includename], 0), @includeoldref = ISNULL(SRC.[includeoldref], 0), @includeuser1 = ISNULL(SRC.[includeuser1], 0), @andorname = ISNULL(SRC.[andorname], ''), @andornamevalue = ISNULL(SRC.[andornamevalue], ''), @andordescription = ISNULL(SRC.[andordescription], ''), @andordescriptionvalue = ISNULL(SRC.[andordescriptionvalue], ''), @andoraddress = ISNULL(SRC.[andoraddress], ''), @andoraddressvalue = ISNULL(SRC.[andoraddressvalue], ''), @andorcode = ISNULL(SRC.[andorcode], ''), @andorcodevalue = ISNULL(SRC.[andorcodevalue], ''), @andorfeecode = ISNULL(SRC.[andorfeecode], ''), @andorfeecodevalue = ISNULL(SRC.[andorfeecodevalue], ''), @andoroldref = ISNULL(SRC.[andoroldref], ''), @andoroldrefvalue = ISNULL(SRC.[andoroldrefvalue], ''), @andordept = ISNULL(SRC.[andordept], ''), @andordeptvalue = ISNULL(SRC.[andordeptvalue], ''), @andoruser1 = ISNULL(SRC.[andoruser1], ''), @andoruser1value = ISNULL(SRC.[andoruser1value], ''), @andoruser2 = ISNULL(SRC.[andoruser2], ''), @andoruser2value = ISNULL(SRC.[andoruser2value], ''), @andoruser3 = ISNULL(SRC.[andoruser3], ''), @andoruser3value = ISNULL(SRC.[andoruser3value], ''), @andoryourref = ISNULL(SRC.[andoryourref], ''), @andoryourrefvalue = ISNULL(SRC.[andoryourrefvalue], ''), @andorwtype = ISNULL(SRC.[andorwtype], ''), @andorwtypevalue = ISNULL(SRC.[andorwtypevalue], '') FROM OPENXML(@iSL, N'search') WITH ([closed] NCHAR(1) '@closed', [orderby] NVARCHAR(50) '@orderby', [order] NVARCHAR(4) '@order', [howmany] INT '@howmany', [handler] NVARCHAR(10) '@handler', [which] INT '@which', [search] VARCHAR(MAX) '@search', [searchonlycode] VARCHAR(3) '@searchonlycode', [dept] VARCHAR(50) '@departmentfilter', [wtype] VARCHAR(50) '@worktypefilter', [colour] VARCHAR(20) '@filecolourfilter', [feecodefilter] VARCHAR(10) '@feecodefilter', [includeaddress] BIT 'include/includefield[@name="address"]', [includecode] BIT 'include/includefield[@name="code"]', [includedescription] BIT 'include/includefield[@name="description"]', [includename] BIT 'include/includefield[@name="name"]', [includeoldref] BIT 'include/includefield[@name="oldref"]', [includeuser1] BIT 'include/includefield[@name="user1"]', [includeemail] BIT 'include/includefield[@name="email"]', [andorname] VARCHAR(3) 'andor/andorfield[@name="name"]/@andor', [andornamevalue] VARCHAR(100) 'andor/andorfield[@name="name"]/@search', [andordescription] VARCHAR(3) 'andor/andorfield[@name="description"]/@andor', [andordescriptionvalue] VARCHAR(100) 'andor/andorfield[@name="description"]/@search', [andoraddress] VARCHAR(3) 'andor/andorfield[@name="address"]/@andor', [andoraddressvalue] VARCHAR(100) 'andor/andorfield[@name="address"]/@search', [andorcode] VARCHAR(3) 'andor/andorfield[@name="code"]/@andor', [andorcodevalue] VARCHAR(100) 'andor/andorfield[@name="code"]/@search', [andorfeecode] VARCHAR(3) 'andor/andorfield[@name="feecode"]/@andor', [andorfeecodevalue] VARCHAR(100) 'andor/andorfield[@name="feecode"]/@search', [andoroldref] VARCHAR(3) 'andor/andorfield[@name="oldref"]/@andor', [andoroldrefvalue] VARCHAR(100) 'andor/andorfield[@name="oldref"]/@search', [andordept] VARCHAR(3) 'andor/andorfield[@name="dept"]/@andor', [andordeptvalue] VARCHAR(100) 'andor/andorfield[@name="dept"]/@search', [andoruser1] VARCHAR(3) 'andor/andorfield[@name="user1"]/@andor', [andoruser1value] VARCHAR(100) 'andor/andorfield[@name="user1"]/@search', [andoruser2] VARCHAR(3) 'andor/andorfield[@name="user2"]/@andor', [andoruser2value] VARCHAR(100) 'andor/andorfield[@name="user2"]/@search', [andoruser3] VARCHAR(3) 'andor/andorfield[@name="user3"]/@andor', [andoruser3value] VARCHAR(100) 'andor/andorfield[@name="user3"]/@search', [andoryourref] VARCHAR(3) 'andor/andorfield[@name="yourref"]/@andor', [andoryourrefvalue] VARCHAR(100) 'andor/andorfield[@name="yourref"]/@search', [andorwtype] VARCHAR(3) 'andor/andorfield[@name="wtype"]/@andor', [andorwtypevalue] VARCHAR(100) 'andor/andorfield[@name="wtype"]/@search') SRC IF @filecolourdesc <> '' BEGIN SELECT @filecolour = FC.[COLOURCODE] FROM [dbo].[FileColours] FC WITH (NOLOCK) WHERE FC.[COLOURDESC] = @filecolourdesc END SET @filecolour = ISNULL(@filecolour, '') IF @departmentdesc <> '' BEGIN SELECT @department = DP.[CODE] FROM [dbo].[Departments] DP WITH (NOLOCK) WHERE DP.[DESCRIPTION] = @departmentdesc END SET @department = ISNULL(@department, '') IF @worktypedesc <> '' BEGIN IF @worktypedesc LIKE 'Unknown Worktype: %' BEGIN SELECT @worktype = WT.[CODE] FROM [dbo].[WorkTypes] WT WITH (NOLOCK) WHERE 'Unknown Worktype: ' + WT.[CODE] = @worktypedesc OR WT.[DESC] = @worktypedesc END ELSE BEGIN SELECT @worktype = WT.[CODE] FROM [dbo].[WorkTypes] WT WITH (NOLOCK) WHERE WT.[DESC] = @worktypedesc END END SET @worktype = ISNULL(@worktype, '') SET @feecode = ISNULL(@feecode, '') EXEC sp_xml_removedocument @iSL DECLARE @NCOMMAND NVARCHAR(MAX) DECLARE @HasWhere BIT SET @HasWhere = 0 SET @handler = CASE WHEN RTRIM(ISNULL(@handler, '')) = '' THEN 'ADM' ELSE RTRIM(@handler) END SELECT @BitWiseID = HNG.[BitWiseID] FROM HandlerNETGroupMembership HNG WITH (NOLOCK) WHERE HNG.[HANDLER] = CONVERT(VARCHAR(10), @handler) SET @BitWiseID = ISNULL(@BitWiseID, [dbo].[KAAS_FN_GetOldPermissions](RTRIM(ISNULL(@handler, '')))) IF (@handler = 'ADM') BEGIN SET @BitWiseID = CONVERT(BIGINT, -1) END -- Custom Filter Start SET @NCOMMAND = N' DECLARE @StatusCount INT = 0; IF @ColumnNames <> '''' BEGIN DECLARE @CustomFilter TABLE ([Status] VARCHAR(20)) SELECT columnName,rownumber INTO #columntbl FROM (SELECT LTRIM(RTRIM(m.n.value(''.[1]'',''varchar(8000)''))) AS columnName, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS rownumber FROM ( SELECT CAST('''' + REPLACE(@ColumnNames,'','','''') + '''' AS XML) AS x )t CROSS APPLY x.nodes(''/XMLRoot/RowData'')m(n)) ColumnTbl SELECT FilterValue,rownumber INTO #FILTERVALUETBL FROM (SELECT LTRIM(RTRIM(m.n.value(''.[1]'',''varchar(8000)''))) AS FilterValue, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS rownumber FROM ( SELECT CAST('''' + REPLACE(@FilterValues,''|'','''') + '''' AS XML) AS x )t CROSS APPLY x.nodes(''/XMLRoot/RowData'')m(n)) FILTERVALUETBL DECLARE @count INT DECLARE @lptcnt INT=1 DECLARE @ColumnName VARCHAR(50) DECLARE @FilterValue VARCHAR(2000) SELECT @count = count(1) FROM #columntbl; WHILE @lptcnt <= @count BEGIN SET @FilterValue ='''' SELECT @ColumnName = columnName FROM #columntbl WHERE rownumber = @lptcnt SELECT @FilterValue = FilterValue FROM #FILTERVALUETBL WHERE rownumber = @lptcnt IF(@ColumnName = ''Closed'') INSERT INTO @CustomFilter ([Status]) VALUES (@FilterValue) SET @lptcnt = @lptcnt + 1; END SET @StatusCount = (SELECT COUNT(1) FROM @CustomFilter WHERE [Status] IS NOT NULL AND [Status] <>'''') END ' -- Custom Filter End SET @NCOMMAND = @NCOMMAND + N' --SELECT ''How Many: '' + CONVERT(VARCHAR(10), @HowMany) --SELECT ''Handler: '' + @Handler --SELECT ''BitWise ID: '' + CONVERT(VARCHAR(10), @BitWiseID) DECLARE @Result TABLE ([id] INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED, [Time] VARCHAR(5) NOT NULL, [Code] VARCHAR(20) NOT NULL, [FileColour] VARCHAR(20) NOT NULL, [FileColour_Fld] VARCHAR(20) NOT NULL, [BackgroundColour] VARCHAR(30) NOT NULL, [TextColour] VARCHAR(30) NOT NULL, [Name] VARCHAR(256) NOT NULL, [Description] VARCHAR(200) NOT NULL, [Fe] VARCHAR(30) NOT NULL, [FECode] VARCHAR(10) NOT NULL, [FeeEarner] VARCHAR(30) NOT NULL, [User1] VARCHAR(100) NOT NULL, [User2] VARCHAR(100) NOT NULL, [User3] VARCHAR(100) NOT NULL, [Started] DATETIME NOT NULL, [Dept] VARCHAR(50) NOT NULL, [WorkType] VARCHAR(50) NOT NULL, [Privileges] BIGINT NOT NULL, [OldRef] VARCHAR(12) NOT NULL, [Address] VARCHAR(500) NOT NULL, [Email] VARCHAR(50) NOT NULL, [Closed] CHAR(1) NOT NULL, [OrgCloseDate] DATETIME NULL, [CloseDate] DATETIME NULL, [FileRGBColour] INT NOT NULL, [LastAccessed] DATETIME NULL, [PhoneNumber] VARCHAR(50) NOT NULL, [FileNum] INT NOT NULL, [ClientCode] VARCHAR(10) NOT NULL, [DepartmentCode] VARCHAR(5) NOT NULL, [FeeEarnerTeam] VARCHAR(10) NOT NULL, [TeamName] VARCHAR(30) NOT NULL, [YourRef] VARCHAR(100) NOT NULL, [CaseStatusCode] VARCHAR(6) NOT NULL, [CaseStatusDesc] VARCHAR(50) NOT NULL, [OutlayBal] DECIMAL(9) NULL, [ClientCurBal] DECIMAL(9) NULL, [ClientDepBal] DECIMAL(9) NULL, [Comment] VARCHAR(3000) NOT NULL, [Branch] VARCHAR(3) NULL, [OpenInvoiceNo] INT NOT NULL, [ContactName] VARCHAR(256) NULL, [MatterDesc] VARCHAR(200) NULL, [CompBillingMatter] VARCHAR(20) NULL, [CompMatter] VARCHAR(20) NULL ) INSERT INTO @Result ([Time], [Code], [FileColour], [FileColour_Fld], [BackgroundColour], [TextColour], [Name], [Description], [Fe], [FECode], [FeeEarner], [User1], [User2], [User3], [Started], [Dept], [WorkType], [Privileges], [OldRef], [Address], [Email], [Closed], [OrgCloseDate], [CloseDate], [FileRGBColour], [LastAccessed], [PhoneNumber], [FileNum], [ClientCode], [DepartmentCode], [FeeEarnerTeam], [TeamName], [YourRef], [CaseStatusCode], [CaseStatusDesc], [OutlayBal], [ClientCurBal], [ClientDepBal], [Comment], [Branch], [OpenInvoiceNo], [ContactName], [MatterDesc], [CompBillingMatter], [CompMatter] ) SELECT CASE WHEN TTT.[TOTALTIME] = ''00:00'' THEN '''' ELSE TTT.[TOTALTIME] END AS [Time], SM.[Code] AS [Code], SM.[FileColourDescription] AS [FileColour], SM.[FileColourDescription] AS [FileColour_Fld], SM.[FileBackgroundColour] AS [BackgroundColour], SM.[TextColour] AS [TextColour], SM.[Name] AS [Name], RTRIM(ISNULL(SM.[Description],'''')) AS [Description], SM.[HandlerName] AS [Fe], RTRIM(ISNULL(A.[FECode],'''')) AS [FECode], SM.[HandlerName] AS [FeeEarner], RTRIM(ISNULL(A.[User1],'''')) AS [User1], RTRIM(ISNULL(A.[User2],'''')) AS [User2], RTRIM(ISNULL(A.[User3],'''')) AS [User3], SM.[Started] AS [Started], RTRIM(ISNULL(A.[Dept],'''')) AS [Dept], RTRIM(ISNULL(A.[WType],'''')) AS [WorkType], SM.[Privileges] AS [Privileges], RTRIM(ISNULL(A.[OldRef],'''')) AS [OldRef], SM.[Address] AS [Address], SM.[Email] AS [Email], CASE WHEN SM.[Closed] = 0 THEN ''N'' ELSE ''Y'' END AS [Closed], SM.[OrgClosedDate] AS [OriginalClosedDate], SM.[CloseDate] AS [CloseDate], SM.[FileRGBColour] AS [FileRGBColour], CASE WHEN YEAR(ISNULL(RML.[DateField], CONVERT(DATETIME, @DefaultDate))) < 1901 THEN NULL ELSE ISNULL(RML.[DateField], CONVERT(DATETIME, @DefaultDate)) END AS [LastAccessed], SM.[PhoneNumber] AS [PhoneNumber], SM.[FileNum] AS [FileNum], SM.[ClientCode] AS [ClientCode], RTRIM(ISNULL(A.[Dept],'''')) AS [DepartmentCode], SM.[Team] AS [FeeEarnerTeam], SM.[TeamName] AS [TeamName], RTRIM(ISNULL(A.[YourRef],'''')) AS [YourRef], SM.[MatterStatus] AS [CaseStatusCode], SM.[MatterStatusDescription] AS [CaseStatusDesc], A.[OutlayBal] AS [OutlayBal], A.[ClientCurBal] AS [ClientCurBal], A.[ClientDepBal] AS [ClientDepBal], RTRIM(ISNULL(A.[Comment], '''')) AS [Comment], RTRIM(ISNULL(A.[Branch], '''')) AS [Branch], (SELECT COUNT(1) FROM [dbo].[BillHeader] [BH] WHERE ISNULL([BH].[OPENINVOICE], '''') = ''Y'' AND ISNULL([BH].[BILLNO], 0) = 0 AND [BH].[MATTER] = SM.[Code]) AS [OpenInvoiceNo], B.[Name] AS [ContactName], RTRIM(ISNULL(A.[Description],'''')) AS [MatterDesc] , A.[CompBillingMatter] AS [CompBillingMatter], B.[CompMatter] AS [CompMatter] FROM [dbo].[SearchMatters] SM WITH (NOLOCK) LEFT JOIN [dbo].[Matters] A ON A.[CODE] = SM.[Code] LEFT OUTER JOIN [dbo].[contacts] B ON A.[CLIENTCODE]= B.[CODE] CROSS APPLY (SELECT SUBSTRING(CONVERT(VARCHAR, DATEADD(minute, ISNULL(SUM(ISNULL(TDB.[time], 0)), 0), @DefaultDaybookDate), 108), 1, 5) AS [TOTALTIME] FROM [dbo].[TimeDayBook] TDB WITH (NOLOCK) WHERE TDB.[FeeEarn] = @handler AND TDB.[matter] = SM.[code] ) TTT' SET @NCOMMAND = @NCOMMAND + N' LEFT OUTER JOIN [dbo].[RecentMatterList] RML WITH (NOLOCK) ON RML.[FEE] = @handler AND RML.[MATTER] = SM.[Code] WHERE (@StatusCount = 0 OR (SM.[Closed] IN (SELECT FilterValue FROM (SELECT LTRIM(RTRIM(m.n.value(''.[1]'',''varchar(8000)''))) AS FilterValue , ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as rownumber FROM( SELECT CAST('''' + REPLACE((SELECT DISTINCT [Status] FROM @CustomFilter WHERE [Status] IS NOT NULL and [Status] <>''''),'','','''') + '''' AS XML) AS x )t CROSS APPLY x.nodes(''/XMLRoot/RowData'')m(n)) FILTERVALUETBLFnl)) or SM.Closed=1) AND A.[Closed] <> ''Y''' /* --Handler Privileges IF (@BitWiseID = 0) BEGIN SET @NCOMMAND = @NCOMMAND + N' AND ( SM.[Privileges] = 0 OR SM.[Privileges] = -1)' END ELSE IF (@BitWiseID = -1) BEGIN SET @NCOMMAND = @NCOMMAND + N' AND 1 = 1' END ELSE BEGIN SET @NCOMMAND = @NCOMMAND + N' AND SM.[Privileges] & @BitWiseID <> 0' END */ IF (@which = 1) BEGIN SET @NCOMMAND = @NCOMMAND + N' AND SM.[FECode] = @handler' END IF (@closed = 'N') BEGIN SET @NCOMMAND = @NCOMMAND + N' AND SM.[Closed] = 0' END IF (@closed = 'Y') BEGIN SET @NCOMMAND = @NCOMMAND + N' AND SM.[Closed] = 1' END IF (@closed = N'A') BEGIN SET @NCOMMAND = @NCOMMAND + N' AND (SM.[Closed] = 0 OR SM.[Closed] = 1)' END IF (@worktype <> '') BEGIN SET @NCOMMAND = @NCOMMAND + N' AND SM.[WType] = @WorkType' END IF (@department <> '') BEGIN SET @NCOMMAND = @NCOMMAND + N' AND SM.[Dept] = @department' END IF (@filecolour <> '') BEGIN SET @NCOMMAND = @NCOMMAND + N' AND SM.[FileColour] = @filecolour' END IF (@feecode <> '') BEGIN SET @NCOMMAND = @NCOMMAND + N' AND SM.[FECode] = @feecode' END DECLARE @SearchIncludes NVARCHAR(MAX) SET @SearchIncludes = N'' IF (@searchtext <> '') BEGIN IF SUBSTRING(@searchtext, 1, 1) <> '%' BEGIN SET @searchtext = '%' + @searchtext END IF SUBSTRING(@searchtext, LEN(@searchtext), 1) <> '%' BEGIN SET @searchtext = @searchtext + '%' END SET @searchtext = @searchtext COLLATE SQL_Latin1_General_Cp1251_CS_AS IF (@includeaddress = 0) AND (@includecode = 0) AND (@includedescription = 0) AND (@includename = 0) AND (@includeoldref = 0) AND (@includeuser1 = 0) AND (@includeemail = 0) BEGIN -- This is the standard call - not from Advanced Matter Search, so we search in everything IF @SearchIncludes <> N'' BEGIN SET @SearchIncludes = @SearchIncludes + N' OR ' END IF @searchonlycode <> 'yes' BEGIN SET @SearchIncludes = @SearchIncludes + N'SM.[NCDescription] LIKE @searchtext OR SM.[Code] LIKE @searchtext OR SM.[User1] LIKE @searchtext OR SM.[User2] LIKE @searchtext OR SM.[User3] LIKE @searchtext OR SM.[OldRef] LIKE @searchtext OR SM.[YourRef] LIKE @searchtext OR SM.[NCName] LIKE @searchtext OR SM.[NCAddress] LIKE @searchtext OR SM.[Email] LIKE @searchtext OR SM.[NCHandlerName] LIKE @searchtext OR SM.[NCPartnerName] LIKE @searchtext OR SM.[DeptDescription] LIKE @searchtext OR SM.[WTypeDescription] LIKE @searchtext OR SM.[FileColourDescription] LIKE @searchtext OR SM.[MatterStatusDescription] LIKE @searchtext' END ELSE BEGIN SET @SearchIncludes = @SearchIncludes + N'SM.[Code] LIKE @searchtext' END END ELSE BEGIN IF (@includeaddress = 1) BEGIN IF @SearchIncludes <> N'' BEGIN SET @SearchIncludes = @SearchIncludes + N' OR ' END SET @SearchIncludes = @SearchIncludes + N'SM.[NCAddress] LIKE @searchtext' END IF (@includecode = 1) BEGIN IF @SearchIncludes <> N'' BEGIN SET @SearchIncludes = @SearchIncludes + N' OR ' END SET @SearchIncludes = @SearchIncludes + N'SM.[Code] LIKE @searchtext' END IF (@includedescription = 1) BEGIN IF @SearchIncludes <> N'' BEGIN SET @SearchIncludes = @SearchIncludes + N' OR ' END SET @SearchIncludes = @SearchIncludes + N'SM.[NCDescription] LIKE @searchtext' END IF (@includename = 1) BEGIN IF @SearchIncludes <> N'' BEGIN SET @SearchIncludes = @SearchIncludes + N' OR ' END SET @SearchIncludes = @SearchIncludes + N'SM.[NCName] LIKE @searchtext' END IF (@includeoldref = 1) BEGIN IF @SearchIncludes <> N'' BEGIN SET @SearchIncludes = @SearchIncludes + N' OR ' END SET @SearchIncludes = @SearchIncludes + N'SM.[OldRef] LIKE @searchtext' END IF (@includeuser1 = 1) BEGIN IF @SearchIncludes <> N'' BEGIN SET @SearchIncludes = @SearchIncludes + N' OR ' END SET @SearchIncludes = @SearchIncludes + N'SM.[User1] LIKE @searchtext' END IF (@includeemail = 1) BEGIN IF @SearchIncludes <> N'' BEGIN SET @SearchIncludes = @SearchIncludes + N' OR ' END SET @SearchIncludes = @SearchIncludes + N'SM.[Email] LIKE @searchtext' END END END IF @andoraddress = 'OR' BEGIN IF SUBSTRING(@andoraddressvalue, 1, 1) <> '%' BEGIN SET @andoraddressvalue = '%' + @andoraddressvalue END IF SUBSTRING(@andoraddressvalue, LEN(@andoraddressvalue), 1) <> '%' BEGIN SET @andoraddressvalue = @andoraddressvalue + '%' END SET @andoraddressvalue = @andoraddressvalue COLLATE SQL_Latin1_General_Cp1251_CS_AS IF @SearchIncludes <> N'' BEGIN SET @SearchIncludes = @SearchIncludes + N' OR ' END SET @SearchIncludes = @SearchIncludes + N'SM.[NCAddress] LIKE @andoraddressvalue' END IF @andorcode = 'OR' BEGIN IF CHARINDEX('%', @andorcodevalue, 1) = 0 BEGIN IF SUBSTRING(@andorcodevalue, 1, 1) <> '%' BEGIN SET @andorcodevalue = '%' + @andorcodevalue END IF SUBSTRING(@andorcodevalue, LEN(@andorcodevalue), 1) <> '%' BEGIN SET @andorcodevalue = @andorcodevalue + '%' END END IF @SearchIncludes <> N'' BEGIN SET @SearchIncludes = @SearchIncludes + N' OR ' END SET @SearchIncludes = @SearchIncludes + N'SM.[Code] LIKE @andorcodevalue' END IF @andordept = 'OR' BEGIN IF @SearchIncludes <> N'' BEGIN SET @SearchIncludes = @SearchIncludes + N' OR ' END SET @SearchIncludes = @SearchIncludes + N'SM.[Dept]= @andordeptvalue' END IF @andordescription = 'OR' BEGIN IF SUBSTRING(@andordescriptionvalue, 1, 1) <> '%' BEGIN SET @andordescriptionvalue = '%' + @andordescriptionvalue END IF SUBSTRING(@andordescriptionvalue, LEN(@andordescriptionvalue), 1) <> '%' BEGIN SET @andordescriptionvalue = @andordescriptionvalue + '%' END SET @andordescriptionvalue = @andordescriptionvalue COLLATE SQL_Latin1_General_Cp1251_CS_AS IF @SearchIncludes <> N'' BEGIN SET @SearchIncludes = @SearchIncludes + N' OR ' END SET @SearchIncludes = @SearchIncludes + N'SM.[NCDescription] LIKE @andordescriptionvalue' END IF @andorfeecode = 'OR' BEGIN IF @SearchIncludes <> N'' BEGIN SET @SearchIncludes = @SearchIncludes + N' OR ' END SET @SearchIncludes = @SearchIncludes + N'SM.[FECode] = @andorfeecodevalue' END IF @andorname = 'OR' BEGIN IF SUBSTRING(@andornamevalue, 1, 1) <> '%' BEGIN SET @andornamevalue = '%' + @andornamevalue END IF SUBSTRING(@andornamevalue, LEN(@andornamevalue), 1) <> '%' BEGIN SET @andornamevalue = @andornamevalue + '%' END SET @andornamevalue = @andornamevalue COLLATE SQL_Latin1_General_Cp1251_CS_AS IF @SearchIncludes <> N'' BEGIN SET @SearchIncludes = @SearchIncludes + N' OR ' END SET @SearchIncludes = @SearchIncludes + N'SM.[NCName] LIKE @andornamevalue' END IF @andoroldref = 'OR' BEGIN IF SUBSTRING(@andoroldrefvalue, 1, 1) <> '%' BEGIN SET @andoroldrefvalue = '%' + @andoroldrefvalue END IF SUBSTRING(@andoroldrefvalue, LEN(@andoroldrefvalue), 1) <> '%' BEGIN SET @andoroldrefvalue = @andoroldrefvalue + '%' END IF @SearchIncludes <> N'' BEGIN SET @SearchIncludes = @SearchIncludes + N' OR ' END SET @SearchIncludes = @SearchIncludes + N'SM.[OldRef] LIKE @andoroldrefvalue' END IF @andoruser1 = 'OR' BEGIN IF SUBSTRING(@andoruser1value, 1, 1) <> '%' BEGIN SET @andoruser1value = '%' + @andoruser1value END IF SUBSTRING(@andoruser1value, LEN(@andoruser1value), 1) <> '%' BEGIN SET @andoruser1value = @andoruser1value + '%' END IF @SearchIncludes <> N'' BEGIN SET @SearchIncludes = @SearchIncludes + N' OR ' END SET @SearchIncludes = @SearchIncludes + N'SM.[User1] LIKE @andoruser1value' END IF @andoruser2 = 'OR' BEGIN IF SUBSTRING(@andoruser2value, 1, 1) <> '%' BEGIN SET @andoruser2value = '%' + @andoruser2value END IF SUBSTRING(@andoruser2value, LEN(@andoruser2value), 1) <> '%' BEGIN SET @andoruser2value = @andoruser2value + '%' END IF @SearchIncludes <> N'' BEGIN SET @SearchIncludes = @SearchIncludes + N' OR ' END SET @SearchIncludes = @SearchIncludes + N'SM.[User2] LIKE @andoruser2value' END IF @andoruser3 = 'OR' BEGIN IF SUBSTRING(@andoruser3value, 1, 1) <> '%' BEGIN SET @andoruser3value = '%' + @andoruser3value END IF SUBSTRING(@andoruser3value, LEN(@andoruser3value), 1) <> '%' BEGIN SET @andoruser3value = @andoruser3value + '%' END IF @SearchIncludes <> N'' BEGIN SET @SearchIncludes = @SearchIncludes + N' OR ' END SET @SearchIncludes = @SearchIncludes + N'SM.[User3] LIKE @andoruser3value' END IF @andorwtype = 'OR' BEGIN IF @SearchIncludes <> N'' BEGIN SET @SearchIncludes = @SearchIncludes + N' OR ' END SET @SearchIncludes = @SearchIncludes + N'SM.[WType] = @andorwtypevalue' END IF @andoryourref = 'OR' BEGIN IF SUBSTRING(@andoryourrefvalue, 1, 1) <> '%' BEGIN SET @andoryourrefvalue = '%' + @andoryourrefvalue END IF SUBSTRING(@andoryourrefvalue, LEN(@andoryourrefvalue), 1) <> '%' BEGIN SET @andoryourrefvalue = @andoryourrefvalue + '%' END IF @SearchIncludes <> N'' BEGIN SET @SearchIncludes = @SearchIncludes + N' OR ' END SET @SearchIncludes = @SearchIncludes + N'SM.[YourRef] LIKE @andoryourrefvalue' END --SELECT @SearchIncludes IF @SearchIncludes <> '' BEGIN SET @NCOMMAND = @NCOMMAND + N' AND ( ' + @SearchIncludes + N')' END IF @andoraddress = 'AND' BEGIN IF SUBSTRING(@andoraddressvalue, 1, 1) <> '%' BEGIN SET @andoraddressvalue = '%' + @andoraddressvalue END IF SUBSTRING(@andoraddressvalue, LEN(@andoraddressvalue), 1) <> '%' BEGIN SET @andoraddressvalue = @andoraddressvalue + '%' END SET @andoraddressvalue = @andoraddressvalue COLLATE SQL_Latin1_General_Cp1251_CS_AS SET @NCOMMAND = @NCOMMAND + N' AND SM.[NCAddress] LIKE @andoraddressvalue' END IF @andorcode = 'AND' BEGIN IF SUBSTRING(@andorcodevalue, 1, 1) <> '%' BEGIN SET @andorcodevalue = '%' + @andorcodevalue END IF SUBSTRING(@andorcodevalue, LEN(@andorcodevalue), 1) <> '%' BEGIN SET @andorcodevalue = @andorcodevalue + '%' END SET @NCOMMAND = @NCOMMAND + N' AND SM.[Code] LIKE @andorcodevalue' END IF @andordept = 'AND' BEGIN SET @NCOMMAND = @NCOMMAND + N' AND SM.[Dept] = @andordeptvalue' END IF @andordescription = 'AND' BEGIN IF SUBSTRING(@andordescriptionvalue, 1, 1) <> '%' BEGIN SET @andordescriptionvalue = '%' + @andordescriptionvalue END IF SUBSTRING(@andordescriptionvalue, LEN(@andordescriptionvalue), 1) <> '%' BEGIN SET @andordescriptionvalue = @andordescriptionvalue + '%' END SET @andordescriptionvalue = @andordescriptionvalue COLLATE SQL_Latin1_General_Cp1251_CS_AS SET @NCOMMAND = @NCOMMAND + N' AND SM.[NCDescription] LIKE @andordescriptionvalue' END IF @andorfeecode = 'AND' BEGIN SET @NCOMMAND = @NCOMMAND + N' AND SM.[FECode] = @andorfeecodevalue' END IF @andorname = 'AND' BEGIN IF SUBSTRING(@andornamevalue, 1, 1) <> '%' BEGIN SET @andornamevalue = '%' + @andornamevalue END IF SUBSTRING(@andornamevalue, LEN(@andornamevalue), 1) <> '%' BEGIN SET @andornamevalue = @andornamevalue + '%' END SET @andornamevalue = @andornamevalue COLLATE SQL_Latin1_General_Cp1251_CS_AS SET @NCOMMAND = @NCOMMAND + N' AND SM.[NCName] LIKE @andornamevalue' END IF @andoroldref = 'AND' BEGIN IF SUBSTRING(@andoroldrefvalue, 1, 1) <> '%' BEGIN SET @andoroldrefvalue = '%' + @andoroldrefvalue END IF SUBSTRING(@andoroldrefvalue, LEN(@andoroldrefvalue), 1) <> '%' BEGIN SET @andoroldrefvalue = @andoroldrefvalue + '%' END SET @NCOMMAND = @NCOMMAND + N' AND SM.[OldRef] LIKE @andoroldrefvalue' END IF @andoruser1 = 'AND' BEGIN IF SUBSTRING(@andoruser1value, 1, 1) <> '%' BEGIN SET @andoruser1value = '%' + @andoruser1value END IF SUBSTRING(@andoruser1value, LEN(@andoruser1value), 1) <> '%' BEGIN SET @andoruser1value = @andoruser1value + '%' END SET @NCOMMAND = @NCOMMAND + N' AND SM.[User1] LIKE @andoruser1value' END IF @andoruser2 = 'AND' BEGIN IF SUBSTRING(@andoruser2value, 1, 1) <> '%' BEGIN SET @andoruser2value = '%' + @andoruser2value END IF SUBSTRING(@andoruser2value, LEN(@andoruser2value), 1) <> '%' BEGIN SET @andoruser2value = @andoruser2value + '%' END SET @NCOMMAND = @NCOMMAND + N' AND SM.[User2] LIKE @andoruser2value' END IF @andoruser3 = 'AND' BEGIN IF SUBSTRING(@andoruser3value, 1, 1) <> '%' BEGIN SET @andoruser3value = '%' + @andoruser3value END IF SUBSTRING(@andoruser3value, LEN(@andoruser3value), 1) <> '%' BEGIN SET @andoruser3value = @andoruser3value + '%' END SET @NCOMMAND = @NCOMMAND + N' AND SM.[User3] LIKE @andoruser3value' END IF @andorwtype = 'AND' BEGIN SET @NCOMMAND = @NCOMMAND + N' AND SM.[WType] = @andorwtypevalue' END IF @andoryourref = 'AND' BEGIN IF SUBSTRING(@andoryourrefvalue, 1, 1) <> '%' BEGIN SET @andoryourrefvalue = '%' + @andoryourrefvalue END IF SUBSTRING(@andoryourrefvalue, LEN(@andoryourrefvalue), 1) <> '%' BEGIN SET @andoryourrefvalue = @andoryourrefvalue + '%' END SET @NCOMMAND = @NCOMMAND + N' AND SM.[YourRef] LIKE @andoryourrefvalue' END SET @NCOMMAND = @NCOMMAND + N' ORDER BY ' + CASE @orderby WHEN N'description' THEN N'CASE WHEN SM.[Description] = '''' THEN 1 ELSE 0 END, SM.[Description] ' + @order + N', SM.[Code] ' + @order WHEN N'Code' THEN N'CASE WHEN SM.[Code] = '''' THEN 1 ELSE 0 END, SM.[Code] ' + @order WHEN N'Name' THEN N'CASE WHEN SM.[Name] = '''' THEN 1 ELSE 0 END, SM.[Name] ' + @order + N', SM.[Code] ' + @order WHEN N'Address' THEN N'CASE WHEN SM.[Address] = '''' THEN 1 ELSE 0 END, SM.[Address] ' + @order + N', SM.[Code] ' + @order WHEN N'Email' THEN N'CASE WHEN SM.[Email] = '''' THEN 1 ELSE 0 END, SM.[Email] ' + @order + N', SM.[Code] ' + @order WHEN N'PhoneNumber' THEN N'CASE WHEN SM.[PhoneNumber] = '''' THEN 1 ELSE 0 END, SM.[PhoneNumber] ' + @order + N', SM.[Code] ' + @order WHEN N'user1' THEN N'CASE WHEN SM.[User1] = '''' THEN 1 ELSE 0 END, SM.[User1] ' + @order + N', SM.[Code] ' + @order WHEN N'user2' THEN N'CASE WHEN SM.[User2] = '''' THEN 1 ELSE 0 END, SM.[User2] ' + @order + N', SM.[Code] ' + @order WHEN N'user3' THEN N'CASE WHEN SM.[User3] = '''' THEN 1 ELSE 0 END, SM.[User3] ' + @order + N', SM.[Code] ' + @order WHEN N'yourref' THEN N'CASE WHEN SM.[YourRef] = '''' THEN 1 ELSE 0 END, SM.[YourRef] ' + @order + N', SM.[Code] ' + @order WHEN N'OldRef' THEN N'CASE WHEN SM.[OldRef] = '''' THEN 1 ELSE 0 END, SM.[OldRef] ' + @order + N', SM.[Code] ' + @order WHEN N'FileNum' THEN N'CASE WHEN SM.[FileNum] = 0 THEN 1 ELSE 0 END, SM.[FileNum] ' + @order + N', SM.[Code] ' + @order WHEN N'department' THEN N'CASE WHEN SM.[DeptDescription] = '''' THEN 1 ELSE 0 END, SM.[DeptDescription] ' + @order + N', SM.[Code] ' + @order WHEN N'Dept' THEN N'CASE WHEN SM.[DeptDescription] = '''' THEN 1 ELSE 0 END, SM.[DeptDescription] ' + @order + N', SM.[Code] ' + @order WHEN N'worktype' THEN N'CASE WHEN SM.[WTypeDescription] = '''' THEN 1 ELSE 0 END, SM.[WTypeDescription] ' + @order + N', SM.[Code] ' + @order WHEN N'FeeCode' THEN N'CASE WHEN SM.[FECode] = '''' THEN 2 WHEN SM.[FECode] = ''ADM'' THEN 1 ELSE 0 END, SM.[FECode] ' + @order + N', SM.[Code] ' + @order WHEN N'FeeEarner' THEN N'CASE WHEN SM.[HandlerName] = '''' AND SM.[FECode] = '''' THEN 3 WHEN SM.[HandlerName] = '''' THEN 2 WHEN SM.[HandlerName] = ''Admin'' THEN 1 ELSE 0 END, SM.[HandlerName] ' + @order + N', SM.[Code] ' + @order WHEN N'FileColour_Fld' THEN N'CASE WHEN SM.[FileColourDescription] = '''' THEN 1 ELSE 0 END, SM.[FileColourDescription] ' + @order + N', SM.[Code] ' + @order WHEN N'FileColour' THEN N'CASE WHEN SM.[FileColourDescription] = '''' THEN 1 ELSE 0 END, SM.[FileColourDescription] ' + @order + N', SM.[Code] ' + @order WHEN N'started' THEN N'SM.[Started] ' + @order + N', SM.[Code] ' + @order WHEN N'LastAccessed' THEN N'CASE WHEN RML.[DateField] IS NULL THEN 1 ELSE 0 END, RML.[DateField] ' + @order + N', SM.[Code] ' + @order WHEN N'closedate' THEN N'CASE WHEN SM.[CloseDate] IS NULL THEN 1 ELSE 0 END, SM.[CloseDate] ' + @order + N', SM.[Code] ' + @order WHEN N'orgcloseddate' THEN N'CASE WHEN SM.[orgcloseddate] IS NULL THEN 1 ELSE 0 END, SM.[orgcloseddate] ' + @order + N', SM.[Code] ' + @order WHEN N'casestatusdesc' THEN N'CASE WHEN SM.[MatterStatusDescription] IS NULL THEN 1 ELSE 0 END, SM.[MatterStatusDescription] ' + @order + N', SM.[Code] ' + @order ELSE CASE WHEN @Which <> 2 THEN N'SM.[Code] ' + @order ELSE N'CASE WHEN RML.[DateField] IS NULL THEN 1 ELSE 0 END, RML.[DateField] DESC, SM.[Code] ' + @order END END SET @NCOMMAND = @NCOMMAND + N' IF(@Mode <>''Dashboard'') BEGIN SELECT ROW_NUMBER() OVER(ORDER BY [Res].[id]) AS [RowNumber], [RES].[id] as Id, [RES].[Time], [RES].[Code], [RES].[FileColour], [RES].[FileColour_Fld], [RES].[BackgroundColour], [RES].[TextColour], [RES].[Name], [RES].[Description], [RES].[Fe], [RES].[FECode], [RES].[FeeEarner], [RES].[User1], [RES].[User2], [RES].[User3], [RES].[Started], [RES].[Dept], [RES].[WorkType], [RES].[Privileges], [RES].[OldRef], [RES].[Address], [RES].[Email], [RES].[Closed], [RES].[OrgCloseDate], [RES].[CloseDate], [RES].[FileRGBColour], [RES].[LastAccessed], [RES].[PhoneNumber], [RES].[FileNum], [RES].[ClientCode], [RES].[DepartmentCode], [RES].[FeeEarnerTeam], [RES].[TeamName], [RES].[YourRef], [RES].[CaseStatusCode], [RES].[CaseStatusDesc] , [RES].[OutlayBal], [RES].[ClientCurBal], [RES].[ClientDepBal], [RES].[Comment], [RES].[Branch], [RES].[OpenInvoiceNo], [RES].[ContactName], [RES].[MatterDesc], [RES].[CompBillingMatter], [RES].[CompMatter] FROM @Result [RES] WHERE [RES].[id] BETWEEN @StartRow AND @EndRow ORDER BY [RES].[id] ASC SELECT COUNT(1) AS [TotalRecord] FROM @Result [RES] SELECT USERPROMPT1 AS [UserPrompt1], USERPROMPT2 AS [UserPrompt2], USERPROMPT3 AS [UserPrompt3], YourRef AS [YourRef] FROM [Control] END ELSE BEGIN SELECT [RES].[ClientCode], [RES].[Code], [RES].[Name], [RES].[Description], [RES].[FECode], [RES].[FeeEarner], [RES].[DepartmentCode] AS [Dept], [RES].[CompBillingMatter], [RES].[CompMatter] FROM @Result [RES] WHERE [RES].[id] BETWEEN @StartRow AND @EndRow ORDER BY [RES].[id] ASC END ' EXECUTE sp_executesql @NCOMMAND, N'@HowMany INT, @BitWiseID BIGINT, @Handler VARCHAR(10), @WorkType VARCHAR(5), @department VARCHAR(5), @filecolour VARCHAR(3), @feecode VARCHAR(10), @searchtext VARCHAR(MAX), @andoraddressvalue VARCHAR(200), @andorcodevalue VARCHAR(200), @andordeptvalue VARCHAR(200), @andordescriptionvalue VARCHAR(200), @andorfeecodevalue VARCHAR(200), @andornamevalue VARCHAR(200), @andoroldrefvalue VARCHAR(200), @andoruser1value VARCHAR(200), @andoruser2value VARCHAR(200), @andoruser3value VARCHAR(200), @andorwtypevalue VARCHAR(200), @andoryourrefvalue VARCHAR(200), @StartRow INT, @EndRow INT, @ColumnNames VARCHAR(50), @FilterValues VARCHAR(50), @DefaultDate VARCHAR(8), @DefaultDaybookDate VARCHAR(8), @Mode VARCHAR(10) ', @Howmany, @BitWiseID, @handler, @worktype, @department, @filecolour, @feecode, @searchtext, @andoraddressvalue, @andorcodevalue, @andordeptvalue, @andordescriptionvalue, @andorfeecodevalue, @andornamevalue, @andoroldrefvalue, @andoruser1value, @andoruser2value, @andoruser3value, @andorwtypevalue, @andoryourrefvalue, @StartRow, @EndRow, @ColumnNames, @FilterValues, @DefaultDate, @DefaultDaybookDate, @Mode SET NOCOUNT OFF 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 IF OBJECT_ID(N'KAAS_LS_InsertTasksAction',N'P')IS NOT NULL DROP PROCEDURE [dbo].[KAAS_LS_InsertTasksAction] GO create PROCEDURE [dbo].[KAAS_LS_InsertTasksAction] ( @CASECODE VARCHAR(20), @FNCODE VARCHAR(10), @SUBJECT VARCHAR(150), @TEXT1 VARCHAR(MAX), @Result INT OUTPUT ) /******************************************************************************************************* * This sp used to insert blank action. * * * * Stored Procedure Name : [dbo].[KAAS_LS_InsertTasksAction] * * Copied from : [dbo].[KAAS_InsertTasksAction] * * * * Modification History : * * 2024-07-29 Ponni M Created * * 2024-09-03 Aakif Fetch team code for current handler * *******************************************************************************************************/ AS BEGIN SET NOCOUNT ON -- GET TEAM CODE FOR HANDLER DECLARE @TeamCode VARCHAR(10); SELECT @TeamCode = [HAN].[TEAM] FROM [dbo].[Handlers] [HAN] WHERE [HAN].[CODE] = @FNCODE --Diary DECLARE @TIMECONVERT INT SET @TIMECONVERT = dbo.ky_ConvertTimeToClarion(GETDATE()) DECLARE @ACTIONID INT --Pino 2015-09-30 Start EXEC @ACTIONID = [dbo].[KAAS_GetNextActionID] --SET @ACTIONID = (SELECT ISNULL(MAX(ACTIONID + 1), 1) FROM [dbo].[diary]) --Pino 2015-09-30 End DECLARE @ACTIONCODE Varchar(100) SET @ACTIONCODE = (SELECT TOP 1 KeyValue FROM settings WHERE KeyName = 'LS_DefautActionCode') INSERT INTO [dbo].[diary] ([CASECODE], [DATE], [STATUS], [ACTIONCODE], [ACTIONSTATUS], [ACTIONTYPE], [PROCESSTYPE], [FNCODE], [TEAMCODE], [TEXT1], [TEXT2], [DELEGATEDFNR], [DELEGATEDDATE], [DELEGATEDBACKDATE], [DEFERRED], [DUEDATE], [PUBLISH], [DYSTARTTIME], [DYENDTIME], [DURATION], [ACTIONID], [ORGINALACTIONID], [PRIORITY], [HIGHLIGHTED], [MILESTEONETYPE], [ATTACHMENTS], [PROCESSSTATUS], [WORKPROCESS], [BILLABLE], [BILLDESCRIPTION], [EMAILADDRESS], [ADDRESSTO], [CCTo], [BCCTo], [EMAIL], [SUBJECT], [DELEGATIONSTATUS], [DRAFTBILLNO], [CHEQUEREQNO], [TxmSent], [Location], [HearingType], [ForCopy], [TxmDate], [TxmSeqNo], [DisplayText], [Flag], [KYC]) VALUES(@CASECODE, GETDATE(), 0, @ACTIONCODE, null, 'A', null, @FNCODE, @TeamCode, @TEXT1, null, null, null, null, null, null, 'N', @TIMECONVERT, @TIMECONVERT, 0, @ACTIONID, 0, 'N', 'N', null, 'N', 0, 0, 0, 'N', null, null, null, null, null, @SUBJECT, 0, 0, 0, 0, null, null, null, null, 0, null, null, 'N' ) INSERT INTO [dbo].[DiaryDelegations] ([ACTIONID], [HANDLER], [TEAM], [DATE], [TIME], [DATER], [TIMER], [DUEDATE], [DUETIME], [REVIEW], [STATUS], [OWNER], [DELEGATE], [DELEGATESTATUS], [ActionType], [FromHandler], [ReturnedBy], [DelType]) VALUES(@ACTIONID, @FNCODE, @TeamCode, GETDATE(), @TIMECONVERT, null, @TIMECONVERT, GETDATE(), @TIMECONVERT, null, 0, 'Y', @FNCODE, 0, 'A', @FNCODE, null, 'Created') SET @Result = @ACTIONID SET NOCOUNT OFF END GO