IF OBJECT_ID(N'KAAS_CP_AcitvateNewUserAccount',N'P')IS NOT NULL DROP PROCEDURE [dbo].[KAAS_CP_AcitvateNewUserAccount] GO CREATE PROCEDURE [dbo].[KAAS_CP_AcitvateNewUserAccount] (@EmailId VARCHAR(200), @UserName VARCHAR(256), @PhoneNumber VARCHAR(50), @PasswordHash NVARCHAR(512)) AS /******************************************************************************************************* * * * Stored Procedure Name : [dbo].[KAAS_CP_AcitvateNewUserAccount] * * Description: To activate new user account with password hash * * * * Modification History: * * 2021-06-08 Aakif Created * *******************************************************************************************************/ BEGIN -- Update user details to activate account UPDATE [dbo].[ClientLogin] SET [PasswordHash] = @PasswordHash, [Name] = @UserName, [PhoneNumber] = @PhoneNumber, [IsEmailConfirmed] = 1, [IsUserVerifiedEmail] = 1, [EmailActivationToken] = NULL, [EmailActivationTokenExpirationTime] = NULL WHERE [Email] = @EmailId END GO IF OBJECT_ID(N'KAAS_CP_FetchDiaryByMatterCode',N'P')IS NOT NULL DROP PROCEDURE [dbo].[KAAS_CP_FetchDiaryByMatterCode] GO CREATE PROCEDURE [dbo].[KAAS_CP_FetchDiaryByMatterCode] (@Matter VARCHAR(20), @Search VARCHAR(200) = NULL, @PageNumber INT = NULL, @PageSize INT = NULL, @ActionTypeXML XML = NULL, @PriorityXML XML = NULL, @FlagXML XML = NULL, @DateXML XML = NULL, @LoginId BIGINT = NULL ) AS /************************************************************************************************************* * * * Copied From: [dbo].[KEYHM_FetchDiaryByMatterCode] * * * * Fetch Diary Items by Matter Code, with support for pagination and for filtering on ActionType, * * Priority and/or Flag. * * * * @Search can be any string. Wildcard % will be appended at start and finish * * @ActionTypeXML: example - '' * * @PriorityXML: example - '' * * @DateXML: example - '' * * @FlagXML: example- '' * * * * Compatibility information - PLEASE update older versions if necessary to ensure the compatible software * * remains fully functional * * ***************************************************************************************************** * * * * * * * Supersedes: [dbo].[ky_NETCASEDIARYFetchDiarybyMatterCode] * * * * Original: [dbo].[ky_NETSPFetchDiaryByMatterCode] * * * * First compatible version: 5.7.2.1 * * * * Last compatible software version: - * * * * Superseded by: - * * * * * * * ***************************************************************************************************** * * * * Modification History * * 2019-04-02 Pino Carafa Created * * 2019-04-30 Pino Carafa Added Subject search and Date Range * * 2019-05-21 Saravanan Performance tuning done * * 2019-05-29 Arun KEYHM-402 - Sorting order was correct till @FOUND table * variable(Line No: 232), after this, few column have joined for desired * output, at that time, sorting order gets changed, so explicitily have * soredted by id column after join operation completed(Line no: 380). * * 2019-10-23 Vinodhan If Flag is null, it be replaced with 0 and filtered while filtering the case diary data * soredted by id column after join operation completed(Line no: 380). * * 2019-10-29 Arun Have used KEYHM_FNConvertUTCDateToTargetTimeZone function to convert * action date to utc * * 2021-06-14 Aakif Created * *************************************************************************************************************/ BEGIN SET NOCOUNT ON DECLARE @ShowFromToSynopsis BIT DECLARE @FromDate DATETIME DECLARE @ToDate DATETIME SET @ShowFromToSynopsis = CASE WHEN ISNULL((SELECT TOP 1 [SET].[KeyValue] FROM [dbo].[Settings] [SET] WHERE [SET].[KeyName] = 'ShowFromToSynopsis'), 'True') = 'False' THEN 0 ELSE 1 END -- Convert @ActionTypesXML etc into Table Variables for easier reference in later scripts START DECLARE @ACTIONTYPES TABLE ([ActionType] VARCHAR(1) PRIMARY KEY) DECLARE @ACTIONTYPESFILTERED BIT SET @ACTIONTYPESFILTERED = 0 --Squeeze the last little bit of performance out of this DECLARE @PRIORITIES TABLE ([Priority] VARCHAR(1) PRIMARY KEY) DECLARE @PRIORITIESFILTERED BIT SET @PRIORITIESFILTERED = 0 --Squeeze the last little bit of performance out of this DECLARE @Flags TABLE ([Flag] TINYINT PRIMARY KEY) DECLARE @FLAGSFILTERED BIT SET @FLAGSFILTERED = 0 --Squeeze the last little bit of performance out of this IF NOT (@DateXML IS NULL) IF NOT (CONVERT(VARCHAR(MAX), @DateXML) = '') BEGIN SELECT [FromDate], [ToDate] FROM (SELECT [DT].[COL].value('@FromDate', 'DATETIME') AS [FromDate], -- Use >= for comparison DATEADD(DAY, 1, [DT].[COL].value('@ToDate', 'DATETIME')) AS [ToDate] -- Add 1 Day to ToDate so that we can use < for comparison FROM @DateXML.nodes('/Dates') AS [DT]([COL])) [DTP] END IF NOT (@ActionTypeXML IS NULL) IF NOT (CONVERT(VARCHAR(MAX), @ActionTypeXML) = '') BEGIN INSERT INTO @ACTIONTYPES ([ActionType]) SELECT [ActionType] FROM (SELECT [AT].[COL].value('@Value', 'VARCHAR(10)') AS [ActionType] FROM @ActionTypeXML.nodes('/ActionType') AS [AT]([COL])) [ATP] SELECT @ACTIONTYPESFILTERED = CASE COUNT(1) WHEN 0 THEN 0 ELSE 1 END FROM @ACTIONTYPES END --Select * from @ActionTypes IF NOT (@PriorityXML IS NULL) IF NOT (CONVERT(VARCHAR(MAX), @PriorityXML) = '') BEGIN INSERT INTO @PRIORITIES ([Priority]) SELECT [Priority] FROM (SELECT [PT].[COL].value('@Value', 'VARCHAR(10)') AS [Priority] FROM @PriorityXML.nodes('/Priority') AS [PT]([COL])) [PTY] SELECT @PRIORITIESFILTERED = CASE COUNT(1) WHEN 0 THEN 0 ELSE 1 END FROM @PRIORITIES END IF NOT (@FlagXML IS NULL) IF NOT (CONVERT(VARCHAR(MAX), @FlagXML) = '') BEGIN INSERT INTO @Flags ([Flag]) SELECT [Flag] FROM (SELECT [FG].[COL].value('@Value', 'INT') AS [Flag] FROM @FlagXML.nodes('/Flag') AS [FG]([COL])) [FLG] SELECT @FLAGSFILTERED = CASE COUNT(1) WHEN 0 THEN 0 ELSE 1 END FROM @Flags END -- Convert @ActionTypesXML etc into Table Variables for easier reference in later scripts END -- Set @SearchText to NULL if nothing entered, otherwise ensure search starts and ends with -- a wildcard character % - START DECLARE @SEARCHTEXT VARCHAR(202) SET @SearchText = CASE WHEN RTRIM(ISNULL(@Search, '')) = '' THEN NULL ELSE RTRIM(ISNULL(@Search, '')) END IF @SearchText IS NOT NULL BEGIN IF SUBSTRING(@SearchText, 1, 1) <> '%' BEGIN SET @SearchText = '%' + @SearchText END IF SUBSTRING(@SearchText, LEN(@SearchText), 1) <> '%' BEGIN SET @SearchText = @SearchText + '%' END END -- Set @SearchText to NULL if nothing entered, otherwise ensure search starts and ends with -- a wildcard character % - END -- Avoid Unnecessary Join START DECLARE @ChargeArrangement VARCHAR(3) SELECT @ChargeArrangement = RTRIM(ISNULL([MAT].[ChargeArrangement], '')) FROM [dbo].[matters] [MAT] WHERE [MAT].[Code] = @Matter -- Avoid Unnecessary Join END --SELECT ActionType FROM #ACTIONTYPES -- Table to contain all matching ActionIDs in the desired order DECLARE @FOUND TABLE ([id] INT IDENTITY(1, 1) PRIMARY KEY, [ActionID] INT) -- Insert ONLY the Primary Keys into the @FOUND Table so that it's much faster to retrieve only the requested page - Performance! INSERT INTO @FOUND ([ActionID]) SELECT [DIA].[ActionID] FROM [dbo].[diary] [DIA] LEFT OUTER JOIN [dbo].[Handlers] [FN] ON [FN].[CODE] = [DIA].[FNCODE] WHERE [DIA].[CASECODE] = @Matter AND ISNULL([DIA].PUBLISH, '') = 'P' -- -- Use the CASE Construct to ensure it doesn't perform the PATINDEX unless it's actually necessary - Performance! AND CASE WHEN @SEARCHTEXT IS NULL THEN 0 WHEN PATINDEX(@SEARCHTEXT, [DIA].[TEXT1]) > 0 THEN 0 WHEN PATINDEX(@SEARCHTEXT, [DIA].[SUBJECT]) > 0 THEN 0 WHEN [FN].[NAME] IS NULL THEN 1 WHEN PATINDEX(@SEARCHTEXT, [FN].[NAME]) > 0 THEN 0 ELSE 1 END = 0 -- Use the CASE Construct to ensure it doesn't perform SELECT until an actual filter is specified - Performance! AND CASE WHEN @FromDate IS NULL THEN 0 WHEN [DIA].[DATE] >= @FromDate THEN 0 ELSE 1 END = 0 -- Use the CASE Construct to ensure it doesn't perform SELECT until an actual filter is specified - Performance! AND CASE WHEN @ToDate IS NULL THEN 0 WHEN [DIA].[DATE] < @ToDate THEN 0 ELSE 1 END = 0 -- Use the CASE Construct to ensure it doesn't perform SELECT until an actual filter is specified - Performance! AND (@ACTIONTYPESFILTERED = 0 Or ([DIA].[ActionType] in (SELECT * FROM @ACTIONTYPES))) -- -- Use the CASE Construct to ensure it doesn't perform SELECT until an actual filter is specified - Performance! AND (@PRIORITIESFILTERED = 0 OR ([DIA].[Priority] IN (SELECT * FROM @Priorities))) -- -- Use the CASE Construct to ensure it doesn't perform SELECT until an actual filter is specified - Performance! AND (@FLAGSFILTERED = 0 OR (ISNULL([DIA].[Flag], 0) IN (SELECT * FROM @Flags))) -- The Order By Clause is only required here, as further down the @FOUND table will return values ordered by [id] -- which is the exact order they were entered into it here - Again, Performance! ORDER BY [DIA].[CASECODE] ASC, [DIA].[STATUS] ASC, [DIA].[DATE] DESC, [DIA].[DYSTARTTIME] DESC, [DIA].[ACTIONID] DESC --for debugging uncomment this below line, after completion please dont forget to uncomment --select * from @FOUND DECLARE @TOTAL INT SET @TOTAL = (SELECT COUNT(1) FROM @FOUND) SELECT @TOTAL AS [TotalRecords] declare @HandlerTimeZone nvarchar(100) -- Only do the full SELECT with all columns when returning the actual result set. Driven from @FOUND SELECT --[FND].[id], RTRIM(ISNULL([DIA].[CASECODE], '')) AS [CASECODE], --CONVERT(VARCHAR, [DIA].[DATE], 20) AS [DATE], --CONVERT(VARCHAR(5), [DIA].[DATE], 108) AS [Time], dbo.KAAS_CP_FNConvertUTCDateToTargetTimeZone([DIA].[DATE], @LoginId) AS [DATE], CONVERT(VARCHAR(5),dbo.KAAS_CP_FNConvertUTCDateToTargetTimeZone([DIA].[DATE], @LoginId), 108) AS [Time], RTRIM(ISNULL([DIA].[STATUS], 0)) AS [DStatus], RTRIM(ISNULL([DIA].[ACTIONCODE], '')) AS [ACTIONCODE], RTRIM(ISNULL([DIA].[ACTIONTYPE], '')) AS [ACTIONTYPE], RTRIM(ISNULL([DIA].[PROCESSTYPE], '')) AS [PROCESSTYPE], RTRIM(ISNULL([DIA].[FNCODE], '')) AS [FNCODE], RTRIM(ISNULL([FN].[NAME], '')) AS [FNNAME], RTRIM(ISNULL([DIA].[TEAMCODE], '')) AS [TEAMCODE], RTRIM(ISNULL([DIA].[DisplayText], '')) AS [TEXT1], ISNULL(CASE WHEN ISNUMERIC([DIA].[DYSTARTTIME]) = 1 THEN [DIA].[DYSTARTTIME] ELSE [dbo].[ky_ConvertTimeToClarion]([DIA].[DYSTARTTIME]) END, 0) AS [DYSTARTTIME], ISNULL(CASE WHEN ISNUMERIC([DIA].[DYENDTIME]) = 1 THEN [DIA].[DYENDTIME] ELSE [dbo].[ky_ConvertTimeToClarion]([DIA].[DYENDTIME]) END, 0) AS [DYENDTIME], ISNULL([DIA].[ACTIONID],0) AS [ACTIONID], ISNULL([DIA].[ORGINALACTIONID],0) AS [ORGINALACTIONID], RTRIM(ISNULL([DIA].[PRIORITY], '')) AS [PRIORITY], RTRIM(ISNULL([DIA].[HIGHLIGHTED], 'N')) AS [HIGHLIGHTED], [HAT].[HasAttachments] AS [ATTACHMENTS], ISNULL([DIA].[PROCESSSTATUS], 0) AS [PROCESSSTATUS], ISNULL([DIA].[WORKPROCESS], 0) AS [WORKPROCESS], ISNULL([DIA].[BILLABLE], 0) AS [BILLABLE], RTRIM(ISNULL([DIA].[EMAILADDRESS], '')) AS [EMAILADDRESS], RTRIM(ISNULL(CONVERT(VARCHAR(200), [DIA].[ADDRESSTO]), '')) AS [ADDRESSTO], RTRIM(ISNULL([DIA].[EMAIL], 'N')) AS [EMAIL], RTRIM(ISNULL(CONVERT(VARCHAR(200), [DIA].[SUBJECT]), '')) AS [SUBJECT], ISNULL([DIA].[DELEGATIONSTATUS], 0) AS [DELEGATIONSTATUS], ISNULL([DIA].[DRAFTBILLNO], 0) AS [DRAFTBILLNO], ISNULL([DIA].[CHEQUEREQNO], 0) AS [CHEQUEREQNO], CAST(CASE WHEN RTRIM(ISNULL([DIA].[ACTIONTYPE],'')) ='E' AND @ShowFromToSynopsis = 1 THEN CASE WHEN ISNULL([DIA].[PROCESSTYPE],'O')='I' THEN 'Email From:' + (RTRIM(ISNULL(CAST([DIA].[EMAILADDRESS] AS VARCHAR(300)), ''))) + ' - ' + ISNULL(CAST ([DIA].[Subject] AS VARCHAR(300)),'') ELSE 'Email To:' + RTRIM(CAST(ISNULL([DIA].[AddressTo], '') AS VARCHAR(300))) + ' - ' + ISNULL(CAST([DIA].[Subject] AS VARCHAR(300)), '') END ELSE CASE WHEN DATALENGTH([DIA].[SUBJECT]) = 0 THEN CASE WHEN SUBSTRING([DIA].[DisplayText], 0, CHARINDEX(CHAR(13), [DIA].[DisplayText])) = '' THEN ISNULL([DIA].[TEXT1], '') ELSE SUBSTRING([DIA].[DisplayText], 0, CHARINDEX(CHAR(13), [DIA].[DisplayText])) END WHEN RTRIM(CAST([DIA].[SUBJECT] AS VARCHAR(300))) = '' THEN CASE WHEN SUBSTRING([DIA].[DisplayText], 0, CHARINDEX(CHAR(13), [DIA].[DisplayText])) = '' THEN ISNULL([DIA].[TEXT1], '') ELSE SUBSTRING([DIA].[DisplayText], 0, CHARINDEX(CHAR(13), [DIA].[DisplayText])) END WHEN [DIA].[SUBJECT] IS NULL THEN CASE WHEN SUBSTRING([DIA].[DisplayText], 0, CHARINDEX(CHAR(13), [DIA].[DisplayText])) = '' THEN ISNULL([DIA].[TEXT1], '') ELSE SUBSTRING([DIA].[DisplayText], 0, CHARINDEX(CHAR(13), [DIA].[DisplayText])) END ELSE [DIA].[SUBJECT] END END AS VARCHAR(300)) AS [Synopsis], ISNULL([DIA].[Flag],0) AS [Flag], CASE WHEN RTRIM(ISNULL([DIA].[ForCopy], 0)) = '1' THEN 'True' ELSE 'False' END AS [ForCopy], CASE WHEN ISNULL([DIA].[WORKPROCESS], 0) = 1 AND ISNULL([DIA].[STATUS], 0) <> 1 THEN 'G' WHEN ISNULL([DIA].[STATUS], 0) = 1 THEN 'C' ELSE '' END AS [COMPELETEORGENERATE], CASE WHEN ISNULL([DIA].[WORKPROCESS], 0) = 1 AND ISNULL([DIA].[STATUS], 0) <> 1 THEN 'G' WHEN ISNULL([DIA].[STATUS], 0) = 1 THEN 'C' ELSE '' END AS [COMPELETEORGENERATEDD], ISNULL([TAC].[DESC], '') AS [ActionTitle], ISNULL([TMP].[WKDESC], '') AS [WorkFlow], CASE WHEN ISNULL([UND].[NeedsAttention], 0) = 1 THEN 'Yes' ELSE 'No' END AS [Needs Attention], CASE WHEN ISNULL([UND].[NeedsAttention], 0) = 1 THEN 'Visible' ELSE 'Collapsed' END AS [NeedsAttentionIcon], CASE WHEN @SearchText IS NULL THEN '' WHEN PATINDEX(@SearchText, [DIA].[TEXT1]) > 0 THEN @Search ELSE '' END AS [SearchMatch], ISNULL([DIA].[CreationDate], CONVERT(DATETIME, [DIA].[DATE])) AS [CreationDate], CASE WHEN [DIA].[DUEDATE] != NULL OR [DIA].[DUEDATE] != '' THEN CONVERT(VARCHAR, dbo.KAAS_CP_FNConvertUTCDateToTargetTimeZone([DIA].[DUEDATE], @LoginId) , 20) ELSE CONVERT(VARCHAR, dbo.KAAS_CP_FNConvertUTCDateToTargetTimeZone([DIA].[DATE], @LoginId) , 20) END AS [DUEDATE], RTRIM(ISNULL([DIA].[DELEGATEDFNR], '')) AS [DelegatedFE], RTRIM(ISNULL([TAC].[Arrangement], '')) AS [Arrangement], @ChargeArrangement AS [ChargeArrangement] FROM @FOUND [FND] INNER JOIN [dbo].[diary] [DIA] LEFT OUTER JOIN [dbo].[Handlers] [FN] ON [FN].[CODE] = [DIA].[FNCODE] ON [DIA].[ActionID] = [FND].[ActionID] LEFT OUTER JOIN [dbo].[TemplateActions] [TAC] LEFT OUTER JOIN [dbo].[Templates] [TMP] ON [TMP].[WKCODE] = [TAC].[WKTCODE] ON [TAC].[ACTIONCODE] = [DIA].[ACTIONCODE] LEFT OUTER JOIN [dbo].[Undertakings] [UND] ON [UND].[ActionID] = [DIA].[ACTIONID] AND [UND].[ActionID] <> 0 AND [UND].[ActionID] IS NOT NULL CROSS APPLY (SELECT CASE WHEN COUNT(1) = 0 THEN 'N' ELSE 'Y' END AS [HasAttachments] FROM [dbo].[DiaryAttachments] [DAT] WHERE [DAT].[DiaryID] = [DIA].[ACTIONID]) [HAT] WHERE CASE WHEN @PageNumber = 0 THEN 0 WHEN [FND].[ID] <= (@PageNumber - 1) * @PageSize THEN 1 WHEN [FND].[ID] > @PageNumber * @PageSize THEN 1 ELSE 0 END = 0 AND ISNULL([DIA].PUBLISH, '') = 'P' ORDER BY [FND].[id] End GO IF OBJECT_ID(N'KAAS_CP_GetCurrentTenantFirmName',N'P')IS NOT NULL DROP PROCEDURE [dbo].[KAAS_CP_GetCurrentTenantFirmName] GO CREATE PROCEDURE [dbo].[KAAS_CP_GetCurrentTenantFirmName] AS /******************************************************************************************************* * * * Stored Procedure Name : [dbo].[KAAS_CP_GetCurrentTenantFirmName] * * Description: To get tenant firm name for client portal * * * * Modification History: * * 2021-06-04 Aakif Created * *******************************************************************************************************/ BEGIN --SET DEFAULT HEADER IF FIRM NAME IS NOT AVAILABLE SELECT [NAME] AS [FirmName] FROM [dbo].[control] END GO IF OBJECT_ID(N'KAAS_CP_GetDocumentsIM',N'P')IS NOT NULL DROP PROCEDURE [dbo].[KAAS_CP_GetDocumentsIM] GO CREATE PROCEDURE [dbo].[KAAS_CP_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) = '', @LoginId BIGINT = 0, @ColumnNames VARCHAR(50) = '', @FilterValues VARCHAR(4000) = '', @DocumentIsNear BIT = 0, @DocumentStartDate DATE ='', @DocumentEndDate DATE ='' ) AS /************************************************************************************************************************ * * * Copied From: [dbo].[KAAS_GetDocumentsIM] * * * * Get list of Documents for the Document Manager * * * * Compatibility information - PLEASE update older versions if necessary to ensure the compatible software * * remains fully functional * * ***************************************************************************************************** * * * * * * * Supersedes: - * * * * Original: [ky_NETSPGetDocumentsIM] * * * * First compatible version: 5.7.2.1 * * * * Last compatible software version: - * * * * Superseded by: - * * * * * * * ***************************************************************************************************** * * * * Modification History * * 2019-04-23 Vinodhan K Created * * 2019-05-24 Pino Carafa Performance improvements * * 2019-05-24 Vinodhan Search field have changed as document instead of filepath * * 2019-05-24 Arun Performance improvements, * * 2019-09-09 Vinodhan K Search now can be done on Document class description field * * 2019-09-18 Vinodhan K Total no. of records now will be displayed based on searched text (KEYSAAS - 1035) * * 2020-02-13 Prabhu.V Modified the SP to Implement filter works with dynamic values * * 2020-04-23 Prabhu.V Performance Improved * * 2020-04-29 Prabhu.V Fixed FileType Filter Not working after Improved the SP * * 2020-05-04 Arun V Document classes datatype size incresed. KEYHM-582 * * 2020-05-04 Prabhu V Document total reocrd issue fixed * * 2020-05-20 Prabhu V Custom Filter Condition missed on performance improvement and added it now * * 2021-06-02 Sadiq Handled seaching for Document near this Funcionlaity in Document manager(11989) * * 2021-06-07 Sadiq Filter option need to be added when "DOcument Near this" is selected(11988) * * 2021-06-11 Sadiq Added document name search also now . (12398) * 2021-06-16 Aakf Created * ************************************************************************************************************************/ BEGIN DECLARE @res TABLE ([id] INT identity(1, 1), [ActionId] INT, [TrackReference] INT, [IMDocID] VARCHAR(500), [IMClass] VARCHAR(20), [DocClass] VARCHAR(10), [Document] VARCHAR(2000), [DiaryDate] DATETIME, [DocumentDate] DATETIME, [TYPE] VARCHAR(20), [Attachments] CHAR(1), [EMAIL] VARCHAR(1), [EMAILFROM] VARCHAR(200), [EMAILSENT] DATETIME, [EMAILTO] VARCHAR(1000), [CurrentVersion] INT, [IsInDocFolder] BIT, [IsShared] BIT, [DocumentClassDescription] VARCHAR(40), [UniqueId] INT, [FilePath] VARCHAR(500), [Subject] VARCHAR(2000), [ShortText] VARCHAR(120), [NAME] VARCHAR(500), [ProcessType] VARCHAR(50), [ACTIONTYPE] VARCHAR(50), [ActionTypeDescription] VARCHAR(500), [DOCUMENTSOURCE] VARCHAR(500) ,[TotalRecord] INT ) DECLARE @TOTALRECORDS INT =0; DECLARE @classes TABLE ([KeyhouseClass] INT, [IMClass] INT, [ClassCode] VARCHAR(20), [ClassDescription] VARCHAR(100)) DECLARE @IMDocTable TABLE ([IMDocID] VARCHAR(500) PRIMARY KEY, [Description] VARCHAR(2000), [IMClass] VARCHAR(20), [IMType] VARCHAR(20), [EmailFrom] VARCHAR(200), [EmailTo] VARCHAR(1000), [EmailSent] DATETIME, [DocumentDate] DATETIME) DECLARE @IMDocExpanded TABLE ([id] INT IDENTITY(1, 1) PRIMARY KEY, [ActionID] INT, [TrackReference] INT, [IMDocID] VARCHAR(500), [IMClass] VARCHAR(20), [DocClass] VARCHAR(10), [Document] VARCHAR(2000), [DiaryDate] DATETIME, [DocumentDate] DATETIME, [EmailSent] DATETIME, [TYPE] VARCHAR(20), [Attachments] CHAR(1), [EMAIL] CHAR(1), [EmailFrom] VARCHAR(200), [EmailTo] VARCHAR(1000), [CurrentVersion] INT) DECLARE @FileTypeList TABLE ([FileType] VARCHAR(20) PRIMARY KEY) INSERT INTO @FileTypeList SELECT RTRIM(LTRIM([Item])) FROM 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)) DECLARE @DOCCLASSFilterCount INT = 0; DECLARE @TypeFilterCount INT = 0; IF @ColumnNames <> '' BEGIN -- Dynamic Filter By Custome Filter Settings Start SELECT columnName,rownumber INTO #columntbl FROM (SELECT LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS columnName, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as rownumber FROM ( SELECT CAST('' + REPLACE(@ColumnNames,',','') + '' AS XML) AS x )t CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)) ColumnTbl -- count(*) over(), SELECT FilterValue,rownumber INTO #FILTERVALUETBL FROM (SELECT LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS FilterValue, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as rownumber FROM ( SELECT CAST('' + REPLACE(@FilterValues,'|','') + '' AS XML) AS x )t CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)) FILTERVALUETBL DECLARE @count int DECLARE @lptcnt int=1 DECLARE @ColumnName varchar(50) DECLARE @FilterValue varchar(2000) SELECT @count = count(1) FROM #columntbl; WHILE @lptcnt <= @count BEGIN SET @FilterValue ='' SELECT @ColumnName = columnName FROM #columntbl WHERE rownumber = @lptcnt SELECT @FilterValue = FilterValue FROM #FILTERVALUETBL WHERE rownumber = @lptcnt IF(@ColumnName = 'DOCCLASS') insert into @CustomFilter (DOCCLASS,TYPE) values (@FilterValue,null) ELSE insert into @CustomFilter (DOCCLASS,TYPE) values (null,@FilterValue) SET @lptcnt = @lptcnt + 1; END SET @DOCCLASSFilterCount = (SELECT COUNT(1) FROM @CustomFilter WHERE DOCCLASS IS NOT NULL AND DOCCLASS <>'') SET @TypeFilterCount = (SELECT COUNT(1) FROM @CustomFilter WHERE TYPE IS NOT NULL AND TYPE <>'') END IF (ISNULL(@DocFolderID, 0) = 0) AND (ISNULL(@ActionID, 0) = 0) BEGIN SET @IMDocs = ISNULL(@IMDocs, '' + CHAR(13) + CHAR(10) + '') IF (ISNULL(PATINDEX('%' + CHAR(13) + CHAR(10) + @IMDocs END BEGIN TRY EXEC sp_xml_preparedocument @idoc OUTPUT, @IMDocs END TRY BEGIN CATCH EXEC sp_xml_preparedocument @idoc OUTPUT, '' END CATCH INSERT INTO @IMDocTable ([IMDocID], [Description], [IMClass], [IMType], [EmailFrom], [EmailTo], [EmailSent], [DocumentDate]) SELECT [DAL].[IMDocID], [DAL].[Description], [DAL].[IMClass], [DAL].[IMType], [DAL].[EmailFrom], [DAL].[EmailTo], [DAL].[EmailSent], [DAL].[DocumentDate] FROM OPENXML(@idoc, 'IMDocs/Doc', 2) WITH([IMDocID] VARCHAR(500) '@DocId', [Description] VARCHAR(2000) '@Description', [IMClass] VARCHAR(20) '@ClassId', [IMType] VARCHAR(20) '@TypeId', [EmailFrom] VARCHAR(200) '@EmailFrom', [EmailTo] VARCHAR(1000) '@EmailTo', [EmailSent] DATETIME '@EmailSent', [DocumentDate] DATETIME '@DocumentDate') [DAL] EXEC sp_xml_removedocument @idoc INSERT INTO @res ([ActionId], [TrackReference], [IMDocID], [IMClass], [DocClass], [Document], [DiaryDate], [DocumentDate], [EmailSent], [TYPE], [Attachments], [EMAIL], [EmailFrom], [EmailTo], [CurrentVersion], [IsInDocFolder], [IsShared]) SELECT convert(INT, NULL) AS [ActionID], convert(INT, NULL) AS [TrackReference], [DAL].[IMDocID] AS [IMDocID], [DAL].[IMClass] AS [IMClass], convert(VARCHAR(10), NULL) AS [DocClass], [DAL].[Description] AS [Document], convert(DATETIME, ISNULL([DAL].[DocumentDate], [DAL].[EmailSent])) AS [DiaryDate], convert(DATETIME, ISNULL([DAL].[DocumentDate], [DAL].[EmailSent])) AS [DocumentDate], convert(DATETIME, [DAL].[EmailSent]) AS [EMAILSENT], CASE convert(VARCHAR(20), [DAL].[IMTYPE]) WHEN 'WORD' THEN 'DOC' WHEN 'WORDX' THEN 'DOC' WHEN 'WPF' THEN 'DOC' WHEN 'ANSI' THEN 'DOC' WHEN 'ACROBAT' THEN 'PDF' WHEN 'EXCEL' THEN 'XLS' WHEN 'EXCELX' THEN 'XLS' WHEN 'MIME' THEN CASE WHEN [DAL].[IMClass] = 'E-Mail' THEN 'MSG' ELSE 'MIME' END ELSE [DAL].[IMTYPE] END AS [TYPE], CONVERT(CHAR(1), 'N') AS [Attachments], CONVERT(CHAR(1), CASE WHEN ISNULL([DAL].[IMClass], '') = 'E-Mail' THEN 'Y' ELSE 'N' END) As [EMAIL], CASE WHEN ISNULL([DAL].[IMClass], '') = 'E-Mail' THEN [DAL].[EMAILFROM] ELSE CONVERT(VARCHAR(200), NULL) END AS [EmailFrom], CASE WHEN ISNULL([DAL].[IMClass], '') = 'E-Mail' THEN [DAL].[EMAILTO] ELSE CONVERT(VARCHAR(1000), NULL) END AS [EmailTo], CONVERT(INT, 1) AS [CurrentVersion], 0 AS [IsInDocFolder], 0 AS [IsShared] FROM @IMDocTable [DAL] LEFT OUTER JOIN [dbo].[DAIMXref] [DX] ON [DX].[IMDocID] = [DAL].[IMDocID] WHERE [DX].[TrackReference] IS NULL AND ( @SearchText = '' OR ([DAL].[Description] LIKE '%' + @SearchText + '%') OR (CONVERT(DATETIME, ISNULL([DAL].[DocumentDate], [DAL].[EmailSent])) LIKE '%' + @SearchText + '%') ) AND ( ISNULL(@FileType, '') = '' OR (CASE CONVERT(VARCHAR(20),[DAL].[IMTYPE]) WHEN 'WORD' THEN 'DOC' WHEN 'WORDX' THEN 'DOC' WHEN 'WPF' THEN 'DOC' WHEN 'ANSI' THEN 'DOC' WHEN 'ACROBAT' THEN 'PDF' WHEN 'EXCEL' THEN 'XLS' WHEN 'EXCELX' THEN 'XLS' WHEN 'MIME' THEN CASE WHEN [DAL].[IMClass] = 'E-Mail' THEN 'MSG' ELSE 'MIME' END ELSE [DAL].[IMTYPE] END IN (select FileType from @FileTypeList )) ) --Filterig the value using search IF @DocumentIsNear=1 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].[CaseCode] = @matter AND [DA].DATEENTERED>=@DocumentStartDate AND [DA].DATEENTERED<=@DocumentEndDate AND ISNULL([DIA].PUBLISH, '') = 'P' 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 ) SELECT @TOTALRECORDS = Count(1)+ @TOTALRECORDS FROM @res SELECT [DIA].[CASECODE],[DIA].[ACTIONID],[DIA].[DATE],[DIA].[TxmDate], [DIA].[EMAIL],[DIA].[ATTACHMENTS],[DIA].[EMAILADDRESS],[DIA].[ADDRESSTO],[DIA].[SUBJECT],[DIA].[DisplayText], [DIA].[PROCESSTYPE],[DIA].[ACTIONTYPE], [DA].[DIARYID],[DA].[TrackReference],[DA].[DocClass],[DA].[DATEENTERED],[DA].[TYPE], [DA].[FILEPATH],[DA].[NAME],[DA].[SOURCE], convert(VARCHAR(500), ISNULL([DX].[IMDocID], '')) AS [IMDocID], convert(VARCHAR(20), ISNULL([DL].[IMClass], '')) AS [IMClass], RTRIM(convert(VARCHAR(2000), ISNULL([DL].[Description], ISNULL([DA].[Document], '')))) AS [Document], CASE WHEN [DIA].[ACTIONTYPE] = 'A' THEN 'Action' WHEN [DIA].[ACTIONTYPE] = 'N' THEN 'Note' WHEN [DIA].[ACTIONTYPE] = 'P' THEN 'Appointment' WHEN [DIA].[ACTIONTYPE] = 'R' THEN 'Reminder' WHEN [DIA].[ACTIONTYPE] = 'E' THEN 'Email' WHEN [DIA].[ACTIONTYPE] = 'T' THEN 'Phone Message' WHEN [DIA].[ACTIONTYPE] = 'D' THEN 'Dictation' WHEN [DIA].[ACTIONTYPE] = 'U' THEN 'Undertaking' WHEN [DIA].[ACTIONTYPE] = 'S' THEN 'Statute Date' WHEN [DIA].[ACTIONTYPE] = 'C' THEN 'Critical Date' WHEN [DIA].[ACTIONTYPE] = 'O' THEN 'Court Date' WHEN [DIA].[ACTIONTYPE] = 'M' THEN 'Scanned Post/Mail' WHEN RTRIM(ISNULL([DIA].[ACTIONTYPE], '')) = '' THEN CASE WHEN convert(VARCHAR(1), CASE WHEN RTRIM(ISNULL([DIA].[EMAIL], 'N')) = 'Y' THEN 'Y' ELSE 'N' END) = 'Y' THEN 'Email' WHEN convert(VARCHAR(500), ISNULL([DX].[IMDocID], '')) <> '' THEN 'IManage document' ELSE 'Unknown' END ELSE 'Action Type: ' + [DIA].[ACTIONTYPE] END AS [ActionTypeDescription], CASE WHEN convert(VARCHAR(10), ISNULL([DA].[DocClass], '')) <> '' THEN RTRIM(ISNULL(DCL.[CLASSDESCRIPTION], '')) ELSE CASE WHEN [DCL].[ClassCode] = 'ATTNOTES' THEN 'Attendance Notes' WHEN [DCL].[ClassCode] = 'BILL' THEN 'Bill' WHEN [DCL].[ClassCode] = 'FAX' THEN 'Fax' WHEN [DCL].[ClassCode] = 'LEGAL' THEN 'Legal' WHEN [DCL].[ClassCode] = 'SCAN' THEN 'Scan' WHEN [DCL].[ClassCode] = 'COMPARE' THEN 'Compare Document' WHEN [DCL].[ClassCode] = 'DISCUSSION' THEN 'Discussion' WHEN [DCL].[ClassCode] = 'DOC' THEN 'Document' WHEN [DCL].[ClassCode] = 'E-MAIL' THEN 'E-Mail' WHEN [DCL].[ClassCode] = 'EVENT' THEN 'Event' WHEN [DCL].[ClassCode] = 'LETTER' THEN 'Letter' WHEN [DCL].[ClassCode] = 'MEMO' THEN 'Memo' WHEN [DCL].[ClassCode] = 'PAGE_ICON' THEN 'Page Icon' WHEN [DCL].[ClassCode] = 'TASK' THEN 'Task' WHEN [DCL].[ClassCode] = 'TEXT' THEN 'Text File' WHEN [DCL].[ClassCode] = 'WEBDOC' THEN 'InfoLink Web Page' ELSE ISNULL([DCL].[CLASSCODE], '') END END AS [DocumentClassDescription] --,Count(1) Over() as TotalRowsCount INTO #DiaryTablesDoc FROM dbo.[diary] [DIA] INNER JOIN dbo.[DiaryAttachments] [DA] ON [DA].[DiaryID] = [DIA].[ACTIONID] and [DIA].CASECODE = [DA].CASECODE LEFT OUTER JOIN [dbo].[DAIMXRef] [DX] LEFT OUTER JOIN @IMDocTable [DL] ON [DL].[IMDocID] = [DX].[IMDocID] ON [DX].[TrackReference] = [DA].[TrackReference] LEFT OUTER JOIN [dbo].[DocumentClasses] DCL ON convert(VARCHAR(10), ISNULL([DA].[DocClass], '')) <> '' AND DCL.[CLASSCODE] = CASE WHEN convert(VARCHAR(10), ISNULL([DA].[DocClass], '')) = '' THEN convert(VARCHAR(20), ISNULL([DL].[IMClass], '')) ELSE convert(VARCHAR(10), ISNULL([DA].[DocClass], '')) END WHERE [DIA].[CaseCode] = @matter AND [DA].DATEENTERED>=@DocumentStartDate AND [DA].DATEENTERED<=@DocumentEndDate AND ISNULL([DIA].PUBLISH, '') = 'P' 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 ) 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] -- ,[TotalRecord] ) SELECT convert(INT, [DIA].[DIARYID]) AS [ActionID], convert(INT, [DIA].[TrackReference]) AS [TrackReference], [DIA].[IMDocID], [DIA].[IMClass], convert(VARCHAR(10), ISNULL([DIA].[DocClass], '')) AS [DocClass], [DIA].[Document], convert(DATETIME, [DIA].[DATE]) As [DiaryDate], convert(DATETIME, [DIA].[DATEENTERED]) AS [DocumentDate], convert(DATETIME, ISNULL([DIA].[TxmDate], [DIA].[Date])) As [EMAILSENT], convert(VARCHAR(20), UPPER(RTRIM(ISNULL([DIA].[TYPE], '')))) AS [TYPE], -- CASE WHEN [DIA].[ATTACHMENTS] = 'Y' THEN 'Y' ELSE 'N' END AS [Attachments], (CASE WHEN ISNULL( convert(VARCHAR(1), CASE WHEN RTRIM(ISNULL([DIA].[EMAIL], 'N')) = 'Y' THEN 'Y' ELSE 'N' END), '') = 'Y' AND convert(INT, [DIA].[TrackReference]) = [OMS].[TrackReference] THEN (CASE WHEN [DIA].[ATTACHMENTS] = 'Y' THEN 'Y' ELSE 'N' END ) WHEN ISNULL(convert(VARCHAR(20), UPPER(RTRIM(ISNULL([DIA].[TYPE], '')))), '') = 'MSG' AND convert(INT, [DIA].[TrackReference]) = [OMS].[TrackReference] THEN (CASE WHEN [DIA].[ATTACHMENTS] = 'Y' THEN 'Y' ELSE 'N' END) ELSE 'N' END) AS [Attachments], -- convert(VARCHAR(1), CASE WHEN RTRIM(ISNULL([DIA].[EMAIL], 'N')) = 'Y' THEN 'Y' ELSE 'N' END) AS [EMAIL], (CASE WHEN convert(VARCHAR(1), CASE WHEN RTRIM(ISNULL([DIA].[EMAIL], 'N')) = 'Y' THEN 'Y' ELSE 'N' END) = 'Y' AND convert(INT, [DIA].[TrackReference]) = [OMS].[TrackReference] THEN 'Y' WHEN ISNULL(convert(VARCHAR(20), UPPER(RTRIM(ISNULL([DIA].[TYPE], '')))), '') = 'MSG' AND convert(INT, [DIA].[TrackReference]) = [OMS].[TrackReference] THEN 'Y' ELSE 'N' END) AS [EMAIL], -- convert(VARCHAR(200), RTRIM(ISNULL([DIA].[EMAILADDRESS], ''))) AS [EMAILFROM], RTRIM(ISNULL(CONVERT(VARCHAR(200), SUBSTRING(convert(VARCHAR(200), RTRIM(ISNULL([DIA].[EMAILADDRESS], ''))), 1, 200)), '')) AS [EMAILFROM], --RTRIM(ISNULL(CONVERT(VARCHAR(1000), [DIA].[ADDRESSTO]), '')) AS [EMAILTO], RTRIM(ISNULL(CONVERT(VARCHAR(1000), SUBSTRING(RTRIM(ISNULL(CONVERT(VARCHAR(1000), [DIA].[ADDRESSTO]), '')), 1, 1000)), '')) AS [EMAILTO], [DV].[CurrentVersion], [IND].[InDocFolder], [SHD].[IsShared], [DIA].[DocumentClassDescription], @uniqueid AS uniqueid, CASE WHEN [UNC].[UNC] IS NULL THEN RTRIM(ISNULL([DIA].[FILEPATH], '')) ELSE [UNC].[UNC] + SUBSTRING(RTRIM(ISNULL([DIA].[FILEPATH], '')), 3, LEN(RTRIM(ISNULL([DIA].[FILEPATH], ''))) - 2) END AS [FILEPATH], -- [RES].[EMAILSENT], RTRIM(ISNULL(CONVERT(VARCHAR(1000), SUBSTRING(RTRIM(ISNULL(CONVERT(VARCHAR(2000), [DIA].[SUBJECT]),'')), 1, 1000)), '')) AS [SUBJECT], RTRIM(ISNULL(CONVERT(VARCHAR(120), SUBSTRING(RTRIM(ISNULL(CONVERT(VARCHAR(120), [DIA].[DisplayText]),'')), 1, 120)), '')) AS [ShortText], RTRIM(ISNULL([DIA].[NAME], '')) AS [NAME], RTRIM(ISNULL([DIA].[PROCESSTYPE], '')) AS [PROCESSTYPE], RTRIM(ISNULL([DIA].[ACTIONTYPE], '')) AS [ACTIONTYPE], [DIA].[ActionTypeDescription], RTRIM(ISNULL([DIA].[SOURCE], CASE WHEN [DIA].[ACTIONID] IS NULL THEN 'IManage' ELSE '' END)) AS [DOCUMENTSOURCE] --,TotalRowsCount FROM #DiaryTablesDoc [DIA] -- INNER JOIN dbo.[matters] [MAT] ON [MAT].[CODE] = [DIA].[CASECODE] CROSS APPLY (SELECT CASE WHEN COUNT(1) = 0 THEN CONVERT(BIT, 0) ELSE CONVERT(BIT, 1) END AS [InDocFolder] FROM [dbo].[DocFolderDocuments] [DFD] WHERE [DFD].[TrackReference] = [DIA].[TrackReference]) [IND] CROSS APPLY (SELECT CASE WHEN COUNT(1) = 0 THEN CONVERT(BIT, 0) ELSE CONVERT(BIT, 1) END AS [IsShared] FROM [dbo].[FileSharingDocuments] [FSD] INNER JOIN [dbo].[FileSharingMapping] [FSM] INNER JOIN [dbo].[FileSharingDocFolders] [FSDF] ON [FSDF].[DocFolderID] = [FSM].[FileSharingDocFoldersID] AND [FSDF].[Shared] = 1 ON [FSM].[id] = [FSD].[FileSharingMappingID] WHERE [FSD].[TrackReference] = [DIA].[TrackReference]) [SHD] CROSS APPLY (SELECT ISNULL(MAX([DAV].[Version]), 0) + 1 AS [CurrentVersion] FROM [dbo].[DiaryAttachmentVersioning] [DAV] WHERE TRACKREFERENCE = [DIA].[TRACKREFERENCE]) [DV] LEFT OUTER JOIN [dbo].[UNCAlias] [UNC] ON SUBSTRING([DIA].[FilePath], 2, 1) = ':' AND [UNC].[Drive] = SUBSTRING([DIA].[FilePath], 1, 1) OUTER APPLY ( SELECT TOP 1 [MST].[TrackReference] AS [TrackReference], [MST].[Type] AS [Type] FROM #DiaryTablesDoc [MST] WHERE [MST].[diaryid] = [DIA].[ActionID] ORDER BY CASE WHEN [MST].Type = 'MSG' THEN 0 ELSE 1 END, [MST].[TrackReference]) [OMS] END ELSE BEGIN SELECT @TOTALRECORDS = Count(1) FROM dbo.[diary] [DIA] INNER JOIN dbo.[DiaryAttachments] [DA] ON [DA].[DiaryID] = [DIA].[ACTIONID] AND [DA].[CASECODE] = [DIA].[CASECODE] WHERE [DIA].[CaseCode] = @matter AND ISNULL([DIA].PUBLISH, '') = 'P' 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 ) SELECT @TOTALRECORDS = Count(1)+ @TOTALRECORDS FROM @res SELECT [DIA].[CASECODE],[DIA].[ACTIONID],[DIA].[DATE],[DIA].[TxmDate], [DIA].[EMAIL],[DIA].[ATTACHMENTS],[DIA].[EMAILADDRESS],[DIA].[ADDRESSTO],[DIA].[SUBJECT],[DIA].[DisplayText], [DIA].[PROCESSTYPE],[DIA].[ACTIONTYPE], [DA].[DIARYID],[DA].[TrackReference],[DA].[DocClass],[DA].[DATEENTERED],[DA].[TYPE], [DA].[FILEPATH],[DA].[NAME],[DA].[SOURCE], convert(VARCHAR(500), ISNULL([DX].[IMDocID], '')) AS [IMDocID], convert(VARCHAR(20), ISNULL([DL].[IMClass], '')) AS [IMClass], RTRIM(convert(VARCHAR(2000), ISNULL([DL].[Description], ISNULL([DA].[Document], '')))) AS [Document], CASE WHEN [DIA].[ACTIONTYPE] = 'A' THEN 'Action' WHEN [DIA].[ACTIONTYPE] = 'N' THEN 'Note' WHEN [DIA].[ACTIONTYPE] = 'P' THEN 'Appointment' WHEN [DIA].[ACTIONTYPE] = 'R' THEN 'Reminder' WHEN [DIA].[ACTIONTYPE] = 'E' THEN 'Email' WHEN [DIA].[ACTIONTYPE] = 'T' THEN 'Phone Message' WHEN [DIA].[ACTIONTYPE] = 'D' THEN 'Dictation' WHEN [DIA].[ACTIONTYPE] = 'U' THEN 'Undertaking' WHEN [DIA].[ACTIONTYPE] = 'S' THEN 'Statute Date' WHEN [DIA].[ACTIONTYPE] = 'C' THEN 'Critical Date' WHEN [DIA].[ACTIONTYPE] = 'O' THEN 'Court Date' WHEN [DIA].[ACTIONTYPE] = 'M' THEN 'Scanned Post/Mail' WHEN RTRIM(ISNULL([DIA].[ACTIONTYPE], '')) = '' THEN CASE WHEN convert(VARCHAR(1), CASE WHEN RTRIM(ISNULL([DIA].[EMAIL], 'N')) = 'Y' THEN 'Y' ELSE 'N' END) = 'Y' THEN 'Email' WHEN convert(VARCHAR(500), ISNULL([DX].[IMDocID], '')) <> '' THEN 'IManage document' ELSE 'Unknown' END ELSE 'Action Type: ' + [DIA].[ACTIONTYPE] END AS [ActionTypeDescription], CASE WHEN convert(VARCHAR(10), ISNULL([DA].[DocClass], '')) <> '' THEN RTRIM(ISNULL(DCL.[CLASSDESCRIPTION], '')) ELSE CASE WHEN [DCL].[ClassCode] = 'ATTNOTES' THEN 'Attendance Notes' WHEN [DCL].[ClassCode] = 'BILL' THEN 'Bill' WHEN [DCL].[ClassCode] = 'FAX' THEN 'Fax' WHEN [DCL].[ClassCode] = 'LEGAL' THEN 'Legal' WHEN [DCL].[ClassCode] = 'SCAN' THEN 'Scan' WHEN [DCL].[ClassCode] = 'COMPARE' THEN 'Compare Document' WHEN [DCL].[ClassCode] = 'DISCUSSION' THEN 'Discussion' WHEN [DCL].[ClassCode] = 'DOC' THEN 'Document' WHEN [DCL].[ClassCode] = 'E-MAIL' THEN 'E-Mail' WHEN [DCL].[ClassCode] = 'EVENT' THEN 'Event' WHEN [DCL].[ClassCode] = 'LETTER' THEN 'Letter' WHEN [DCL].[ClassCode] = 'MEMO' THEN 'Memo' WHEN [DCL].[ClassCode] = 'PAGE_ICON' THEN 'Page Icon' WHEN [DCL].[ClassCode] = 'TASK' THEN 'Task' WHEN [DCL].[ClassCode] = 'TEXT' THEN 'Text File' WHEN [DCL].[ClassCode] = 'WEBDOC' THEN 'InfoLink Web Page' ELSE ISNULL([DCL].[CLASSCODE], '') END END AS [DocumentClassDescription] --,Count(1) Over() as TotalRowsCount INTO #DiaryTables FROM dbo.[diary] [DIA] INNER JOIN dbo.[DiaryAttachments] [DA] ON [DA].[DiaryID] = [DIA].[ACTIONID] and [DIA].CASECODE = [DA].CASECODE LEFT OUTER JOIN [dbo].[DAIMXRef] [DX] LEFT OUTER JOIN @IMDocTable [DL] ON [DL].[IMDocID] = [DX].[IMDocID] ON [DX].[TrackReference] = [DA].[TrackReference] LEFT OUTER JOIN [dbo].[DocumentClasses] DCL ON convert(VARCHAR(10), ISNULL([DA].[DocClass], '')) <> '' AND DCL.[CLASSCODE] = CASE WHEN convert(VARCHAR(10), ISNULL([DA].[DocClass], '')) = '' THEN convert(VARCHAR(20), ISNULL([DL].[IMClass], '')) ELSE convert(VARCHAR(10), ISNULL([DA].[DocClass], '')) END WHERE [DIA].[CaseCode] = @matter AND ISNULL([DIA].PUBLISH, '') = 'P' 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 ) 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] -- ,[TotalRecord] ) SELECT convert(INT, [DIA].[DIARYID]) AS [ActionID], convert(INT, [DIA].[TrackReference]) AS [TrackReference], [DIA].[IMDocID], [DIA].[IMClass], convert(VARCHAR(10), ISNULL([DIA].[DocClass], '')) AS [DocClass], [DIA].[Document], convert(DATETIME, [DIA].[DATE]) As [DiaryDate], convert(DATETIME, [DIA].[DATEENTERED]) AS [DocumentDate], convert(DATETIME, ISNULL([DIA].[TxmDate], [DIA].[Date])) As [EMAILSENT], convert(VARCHAR(20), UPPER(RTRIM(ISNULL([DIA].[TYPE], '')))) AS [TYPE], -- CASE WHEN [DIA].[ATTACHMENTS] = 'Y' THEN 'Y' ELSE 'N' END AS [Attachments], (CASE WHEN ISNULL( convert(VARCHAR(1), CASE WHEN RTRIM(ISNULL([DIA].[EMAIL], 'N')) = 'Y' THEN 'Y' ELSE 'N' END), '') = 'Y' AND convert(INT, [DIA].[TrackReference]) = [OMS].[TrackReference] THEN (CASE WHEN [DIA].[ATTACHMENTS] = 'Y' THEN 'Y' ELSE 'N' END ) WHEN ISNULL(convert(VARCHAR(20), UPPER(RTRIM(ISNULL([DIA].[TYPE], '')))), '') = 'MSG' AND convert(INT, [DIA].[TrackReference]) = [OMS].[TrackReference] THEN (CASE WHEN [DIA].[ATTACHMENTS] = 'Y' THEN 'Y' ELSE 'N' END) ELSE 'N' END) AS [Attachments], -- convert(VARCHAR(1), CASE WHEN RTRIM(ISNULL([DIA].[EMAIL], 'N')) = 'Y' THEN 'Y' ELSE 'N' END) AS [EMAIL], (CASE WHEN convert(VARCHAR(1), CASE WHEN RTRIM(ISNULL([DIA].[EMAIL], 'N')) = 'Y' THEN 'Y' ELSE 'N' END) = 'Y' AND convert(INT, [DIA].[TrackReference]) = [OMS].[TrackReference] THEN 'Y' WHEN ISNULL(convert(VARCHAR(20), UPPER(RTRIM(ISNULL([DIA].[TYPE], '')))), '') = 'MSG' AND convert(INT, [DIA].[TrackReference]) = [OMS].[TrackReference] THEN 'Y' ELSE 'N' END) AS [EMAIL], -- convert(VARCHAR(200), RTRIM(ISNULL([DIA].[EMAILADDRESS], ''))) AS [EMAILFROM], RTRIM(ISNULL(CONVERT(VARCHAR(200), SUBSTRING(convert(VARCHAR(200), RTRIM(ISNULL([DIA].[EMAILADDRESS], ''))), 1, 200)), '')) AS [EMAILFROM], --RTRIM(ISNULL(CONVERT(VARCHAR(1000), [DIA].[ADDRESSTO]), '')) AS [EMAILTO], RTRIM(ISNULL(CONVERT(VARCHAR(1000), SUBSTRING(RTRIM(ISNULL(CONVERT(VARCHAR(1000), [DIA].[ADDRESSTO]), '')), 1, 1000)), '')) AS [EMAILTO], [DV].[CurrentVersion], [IND].[InDocFolder], [SHD].[IsShared], [DIA].[DocumentClassDescription], @uniqueid AS uniqueid, CASE WHEN [UNC].[UNC] IS NULL THEN RTRIM(ISNULL([DIA].[FILEPATH], '')) ELSE [UNC].[UNC] + SUBSTRING(RTRIM(ISNULL([DIA].[FILEPATH], '')), 3, LEN(RTRIM(ISNULL([DIA].[FILEPATH], ''))) - 2) END AS [FILEPATH], -- [RES].[EMAILSENT], RTRIM(ISNULL(CONVERT(VARCHAR(1000), SUBSTRING(RTRIM(ISNULL(CONVERT(VARCHAR(2000), [DIA].[SUBJECT]),'')), 1, 1000)), '')) AS [SUBJECT], RTRIM(ISNULL(CONVERT(VARCHAR(120), SUBSTRING(RTRIM(ISNULL(CONVERT(VARCHAR(120), [DIA].[DisplayText]),'')), 1, 120)), '')) AS [ShortText], RTRIM(ISNULL([DIA].[NAME], '')) AS [NAME], RTRIM(ISNULL([DIA].[PROCESSTYPE], '')) AS [PROCESSTYPE], RTRIM(ISNULL([DIA].[ACTIONTYPE], '')) AS [ACTIONTYPE], [DIA].[ActionTypeDescription], RTRIM(ISNULL([DIA].[SOURCE], CASE WHEN [DIA].[ACTIONID] IS NULL THEN 'IManage' ELSE '' END)) AS [DOCUMENTSOURCE] --,TotalRowsCount FROM #DiaryTables [DIA] -- INNER JOIN dbo.[matters] [MAT] ON [MAT].[CODE] = [DIA].[CASECODE] CROSS APPLY (SELECT CASE WHEN COUNT(1) = 0 THEN CONVERT(BIT, 0) ELSE CONVERT(BIT, 1) END AS [InDocFolder] FROM [dbo].[DocFolderDocuments] [DFD] WHERE [DFD].[TrackReference] = [DIA].[TrackReference]) [IND] CROSS APPLY (SELECT CASE WHEN COUNT(1) = 0 THEN CONVERT(BIT, 0) ELSE CONVERT(BIT, 1) END AS [IsShared] FROM [dbo].[FileSharingDocuments] [FSD] INNER JOIN [dbo].[FileSharingMapping] [FSM] INNER JOIN [dbo].[FileSharingDocFolders] [FSDF] ON [FSDF].[DocFolderID] = [FSM].[FileSharingDocFoldersID] AND [FSDF].[Shared] = 1 ON [FSM].[id] = [FSD].[FileSharingMappingID] WHERE [FSD].[TrackReference] = [DIA].[TrackReference]) [SHD] CROSS APPLY (SELECT ISNULL(MAX([DAV].[Version]), 0) + 1 AS [CurrentVersion] FROM [dbo].[DiaryAttachmentVersioning] [DAV] WHERE TRACKREFERENCE = [DIA].[TRACKREFERENCE]) [DV] LEFT OUTER JOIN [dbo].[UNCAlias] [UNC] ON SUBSTRING([DIA].[FilePath], 2, 1) = ':' AND [UNC].[Drive] = SUBSTRING([DIA].[FilePath], 1, 1) OUTER APPLY ( SELECT TOP 1 [MST].[TrackReference] AS [TrackReference], [MST].[Type] AS [Type] FROM #DiaryTables [MST] WHERE [MST].[diaryid] = [DIA].[ActionID] ORDER BY CASE WHEN [MST].Type = 'MSG' THEN 0 ELSE 1 END, [MST].[TrackReference]) [OMS] 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 ISNULL([DIA].PUBLISH, '') = 'P' 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 ) SELECT [DIA].[CASECODE],[DIA].[ACTIONID],[DIA].[DATE],[DIA].[TxmDate], [DIA].[EMAIL],[DIA].[ATTACHMENTS],[DIA].[EMAILADDRESS],[DIA].[ADDRESSTO],[DIA].[SUBJECT],[DIA].[DisplayText], [DIA].[PROCESSTYPE],[DIA].[ACTIONTYPE], [DA].[DIARYID],[DA].[TrackReference],[DA].[DocClass],[DA].[DATEENTERED],[DA].[TYPE], [DA].[FILEPATH],[DA].[NAME],[DA].[SOURCE], convert(VARCHAR(500), ISNULL([DX].[IMDocID], '')) AS [IMDocID], convert(VARCHAR(20), ISNULL([DL].[IMClass], '')) AS [IMClass], RTRIM(convert(VARCHAR(2000), ISNULL([DL].[Description], ISNULL([DA].[Document], '')))) AS [Document], CASE WHEN [DIA].[ACTIONTYPE] = 'A' THEN 'Action' WHEN [DIA].[ACTIONTYPE] = 'N' THEN 'Note' WHEN [DIA].[ACTIONTYPE] = 'P' THEN 'Appointment' WHEN [DIA].[ACTIONTYPE] = 'R' THEN 'Reminder' WHEN [DIA].[ACTIONTYPE] = 'E' THEN 'Email' WHEN [DIA].[ACTIONTYPE] = 'T' THEN 'Phone Message' WHEN [DIA].[ACTIONTYPE] = 'D' THEN 'Dictation' WHEN [DIA].[ACTIONTYPE] = 'U' THEN 'Undertaking' WHEN [DIA].[ACTIONTYPE] = 'S' THEN 'Statute Date' WHEN [DIA].[ACTIONTYPE] = 'C' THEN 'Critical Date' WHEN [DIA].[ACTIONTYPE] = 'O' THEN 'Court Date' WHEN [DIA].[ACTIONTYPE] = 'M' THEN 'Scanned Post/Mail' WHEN RTRIM(ISNULL([DIA].[ACTIONTYPE], '')) = '' THEN CASE WHEN convert(VARCHAR(1), CASE WHEN RTRIM(ISNULL([DIA].[EMAIL], 'N')) = 'Y' THEN 'Y' ELSE 'N' END) = 'Y' THEN 'Email' WHEN convert(VARCHAR(500), ISNULL([DX].[IMDocID], '')) <> '' THEN 'IManage document' ELSE 'Unknown' END ELSE 'Action Type: ' + [DIA].[ACTIONTYPE] END AS [ActionTypeDescription], CASE WHEN convert(VARCHAR(10), ISNULL([DA].[DocClass], '')) <> '' THEN RTRIM(ISNULL(DCL.[CLASSDESCRIPTION], '')) ELSE CASE WHEN [DCL].[ClassCode] = 'ATTNOTES' THEN 'Attendance Notes' WHEN [DCL].[ClassCode] = 'BILL' THEN 'Bill' WHEN [DCL].[ClassCode] = 'FAX' THEN 'Fax' WHEN [DCL].[ClassCode] = 'LEGAL' THEN 'Legal' WHEN [DCL].[ClassCode] = 'SCAN' THEN 'Scan' WHEN [DCL].[ClassCode] = 'COMPARE' THEN 'Compare Document' WHEN [DCL].[ClassCode] = 'DISCUSSION' THEN 'Discussion' WHEN [DCL].[ClassCode] = 'DOC' THEN 'Document' WHEN [DCL].[ClassCode] = 'E-MAIL' THEN 'E-Mail' WHEN [DCL].[ClassCode] = 'EVENT' THEN 'Event' WHEN [DCL].[ClassCode] = 'LETTER' THEN 'Letter' WHEN [DCL].[ClassCode] = 'MEMO' THEN 'Memo' WHEN [DCL].[ClassCode] = 'PAGE_ICON' THEN 'Page Icon' WHEN [DCL].[ClassCode] = 'TASK' THEN 'Task' WHEN [DCL].[ClassCode] = 'TEXT' THEN 'Text File' WHEN [DCL].[ClassCode] = 'WEBDOC' THEN 'InfoLink Web Page' ELSE ISNULL([DCL].[CLASSCODE], '') END END AS [DocumentClassDescription] -- , Count(1) Over() as TotalRowsCount INTO #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 WHERE [DDC].[DocFolderID] = @DocFolderID AND [DA].DATEENTERED>=@DocumentStartDate AND [DA].DATEENTERED<=@DocumentEndDate AND ISNULL([DIA].PUBLISH, '') = 'P' 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 ) 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] ) SELECT convert(INT, [DIA].[DIARYID]) AS [ActionID], convert(INT, [DIA].[TrackReference]) AS [TrackReference], convert(VARCHAR(500), ISNULL([DX].[IMDocID], '')) AS [IMDocID], convert(VARCHAR(20), ISNULL([DIA].[IMClass], '')) AS [IMClass], convert(VARCHAR(10), ISNULL([DIA].[DocClass], '')) AS [DocClass], RTRIM(convert(VARCHAR(2000), ISNULL([DL].[Description], ISNULL([DIA].[Document], '')))) AS [Document], convert(DATETIME, [DIA].[DATE]) As [DiaryDate], convert(DATETIME, [DIA].[DATEENTERED]) AS [DocumentDate], convert(DATETIME, ISNULL([DIA].[TxmDate], [DIA].[Date])) As [EMAILSENT], convert(VARCHAR(20), UPPER(RTRIM(ISNULL([DIA].[TYPE], '')))) AS [TYPE], --CASE WHEN [DIA].[ATTACHMENTS] = 'Y' THEN 'Y' ELSE 'N' END AS [Attachments], (CASE WHEN ISNULL( convert(VARCHAR(1), CASE WHEN RTRIM(ISNULL([DIA].[EMAIL], 'N')) = 'Y' THEN 'Y' ELSE 'N' END), '') = 'Y' AND convert(INT, [DIA].[TrackReference]) = [OMS].[TrackReference] THEN (CASE WHEN [DIA].[ATTACHMENTS] = 'Y' THEN 'Y' ELSE 'N' END ) WHEN ISNULL(convert(VARCHAR(20), UPPER(RTRIM(ISNULL([DIA].[TYPE], '')))), '') = 'MSG' AND convert(INT, [DIA].[TrackReference]) = [OMS].[TrackReference] THEN (CASE WHEN [DIA].[ATTACHMENTS] = 'Y' THEN 'Y' ELSE 'N' END) ELSE 'N' END) AS [Attachments], -- convert(VARCHAR(1), CASE WHEN RTRIM(ISNULL([DIA].[EMAIL], 'N')) = 'Y' THEN 'Y' ELSE 'N' END) AS [EMAIL], (CASE WHEN convert(VARCHAR(1), CASE WHEN RTRIM(ISNULL([DIA].[EMAIL], 'N')) = 'Y' THEN 'Y' ELSE 'N' END) = 'Y' AND convert(INT, [DIA].[TrackReference]) = [OMS].[TrackReference] THEN 'Y' WHEN ISNULL(convert(VARCHAR(20), UPPER(RTRIM(ISNULL([DIA].[TYPE], '')))), '') = 'MSG' AND convert(INT, [DIA].[TrackReference]) = [OMS].[TrackReference] THEN 'Y' ELSE 'N' END) AS [EMAIL], --convert(VARCHAR(200), RTRIM(ISNULL([DIA].[EMAILADDRESS], ''))) AS [EMAILFROM], RTRIM(ISNULL(CONVERT(VARCHAR(200), SUBSTRING(convert(VARCHAR(200), RTRIM(ISNULL([DIA].[EMAILADDRESS], ''))), 1, 200)), '')) AS [EMAILFROM], -- RTRIM(ISNULL(CONVERT(VARCHAR(1000), [DIA].[ADDRESSTO]), '')) AS [EMAILTO], RTRIM(ISNULL(CONVERT(VARCHAR(1000), SUBSTRING(RTRIM(ISNULL(CONVERT(VARCHAR(1000), [DIA].[ADDRESSTO]), '')), 1, 1000)), '')) AS [EMAILTO], [DV].[CurrentVersion], CONVERT(BIT, 1), [SHD].[IsShared], CASE WHEN convert(VARCHAR(10), ISNULL([DIA].[DocClass], '')) <> '' THEN RTRIM(ISNULL(DCL.[CLASSDESCRIPTION], '')) ELSE CASE WHEN [DCL].[ClassCode] = 'ATTNOTES' THEN 'Attendance Notes' WHEN [DCL].[ClassCode] = 'BILL' THEN 'Bill' WHEN [DCL].[ClassCode] = 'FAX' THEN 'Fax' WHEN [DCL].[ClassCode] = 'LEGAL' THEN 'Legal' WHEN [DCL].[ClassCode] = 'SCAN' THEN 'Scan' WHEN [DCL].[ClassCode] = 'COMPARE' THEN 'Compare Document' WHEN [DCL].[ClassCode] = 'DISCUSSION' THEN 'Discussion' WHEN [DCL].[ClassCode] = 'DOC' THEN 'Document' WHEN [DCL].[ClassCode] = 'E-MAIL' THEN 'E-Mail' WHEN [DCL].[ClassCode] = 'EVENT' THEN 'Event' WHEN [DCL].[ClassCode] = 'LETTER' THEN 'Letter' WHEN [DCL].[ClassCode] = 'MEMO' THEN 'Memo' WHEN [DCL].[ClassCode] = 'PAGE_ICON' THEN 'Page Icon' WHEN [DCL].[ClassCode] = 'TASK' THEN 'Task' WHEN [DCL].[ClassCode] = 'TEXT' THEN 'Text File' WHEN [DCL].[ClassCode] = 'WEBDOC' THEN 'InfoLink Web Page' ELSE ISNULL([DCL].[CLASSCODE], '') END END AS [DocumentClassDescription], @uniqueid AS uniqueid, CASE WHEN [UNC].[UNC] IS NULL THEN RTRIM(ISNULL([DIA].[FILEPATH], '')) ELSE [UNC].[UNC] + SUBSTRING(RTRIM(ISNULL([DIA].[FILEPATH], '')), 3, LEN(RTRIM(ISNULL([DIA].[FILEPATH], ''))) - 2) END AS [FILEPATH], -- [RES].[EMAILSENT], RTRIM(ISNULL(CONVERT(VARCHAR(1000), SUBSTRING(RTRIM(ISNULL(CONVERT(VARCHAR(2000), [DIA].[SUBJECT]),'')), 1, 1000)), '')) AS [SUBJECT], RTRIM(ISNULL(CONVERT(VARCHAR(120), SUBSTRING(RTRIM(ISNULL(CONVERT(VARCHAR(120), [DIA].[DisplayText]),'')), 1, 120)), '')) AS [ShortText], RTRIM(ISNULL([DIA].[NAME], '')) AS [NAME], RTRIM(ISNULL([DIA].[PROCESSTYPE], '')) AS [PROCESSTYPE], RTRIM(ISNULL([DIA].[ACTIONTYPE], '')) AS [ACTIONTYPE], CASE WHEN [DIA].[ACTIONTYPE] = 'A' THEN 'Action' WHEN [DIA].[ACTIONTYPE] = 'N' THEN 'Note' WHEN [DIA].[ACTIONTYPE] = 'P' THEN 'Appointment' WHEN [DIA].[ACTIONTYPE] = 'R' THEN 'Reminder' WHEN [DIA].[ACTIONTYPE] = 'E' THEN 'Email' WHEN [DIA].[ACTIONTYPE] = 'T' THEN 'Phone Message' WHEN [DIA].[ACTIONTYPE] = 'D' THEN 'Dictation' WHEN [DIA].[ACTIONTYPE] = 'U' THEN 'Undertaking' WHEN [DIA].[ACTIONTYPE] = 'S' THEN 'Statute Date' WHEN [DIA].[ACTIONTYPE] = 'C' THEN 'Critical Date' WHEN [DIA].[ACTIONTYPE] = 'O' THEN 'Court Date' WHEN [DIA].[ACTIONTYPE] = 'M' THEN 'Scanned Post/Mail' WHEN RTRIM(ISNULL([DIA].[ACTIONTYPE], '')) = '' THEN CASE WHEN convert(VARCHAR(1), CASE WHEN RTRIM(ISNULL([DIA].[EMAIL], 'N')) = 'Y' THEN 'Y' ELSE 'N' END) = 'Y' THEN 'Email' WHEN convert(VARCHAR(500), ISNULL([DX].[IMDocID], '')) <> '' THEN 'IManage document' ELSE 'Unknown' END ELSE 'Action Type: ' + [DIA].[ACTIONTYPE] END AS [ActionTypeDescription], RTRIM(ISNULL([DIA].[SOURCE], CASE WHEN [DIA].[ACTIONID] IS NULL THEN 'IManage' ELSE '' END)) AS [DOCUMENTSOURCE] ,Count(1) over() [TotalRecord] FROM #DiaryTableListDOc [DIA] --ON [DIA].[TrackReference] = [DDC].[TrackReference] CROSS APPLY (SELECT ISNULL(MAX([DAV].[Version]), 0) + 1 AS [CurrentVersion] FROM [dbo].[DiaryAttachmentVersioning] [DAV] WHERE TRACKREFERENCE = [DIA].[TRACKREFERENCE]) [DV] CROSS APPLY (SELECT CASE WHEN COUNT(1) = 0 THEN CONVERT(BIT, 0) ELSE CONVERT(BIT, 1) END AS [IsShared] FROM [dbo].[FileSharingDocuments] [FSD] INNER JOIN [dbo].[FileSharingMapping] [FSM] INNER JOIN [dbo].[FileSharingDocFolders] [FSDF] ON [FSDF].[DocFolderID] = [FSM].[FileSharingDocFoldersID] AND [FSDF].[Shared] = 1 ON [FSM].[id] = [FSD].[FileSharingMappingID] AND [FSM].[FileSharingDocFoldersID] = @DocFolderID WHERE [FSD].[TrackReference] = [DIA].[TrackReference]) [SHD] LEFT OUTER JOIN [dbo].[DAIMXRef] [DX] LEFT OUTER JOIN @IMDocTable [DL] ON [DL].[IMDocID] = [DX].[IMDocID] ON [DX].[TrackReference] = [DIA].[TrackReference] LEFT OUTER JOIN [dbo].[DocumentClasses] DCL ON convert(VARCHAR(10), ISNULL([DIA].[DocClass], '')) <> '' AND DCL.[CLASSCODE] = CASE WHEN convert(VARCHAR(10), ISNULL([DIA].[DocClass], '')) = '' THEN convert(VARCHAR(20), ISNULL([DL].[IMClass], '')) ELSE convert(VARCHAR(10), ISNULL([DIA].[DocClass], '')) END LEFT OUTER JOIN [dbo].[UNCAlias] [UNC] ON SUBSTRING([DIA].[FilePath], 2, 1) = ':' AND [UNC].[Drive] = SUBSTRING([DIA].[FilePath], 1, 1) OUTER APPLY ( SELECT TOP 1 [MST].[TrackReference] AS [TrackReference], [MST].[Type] AS [Type] FROM #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] 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 ISNULL([DIA].PUBLISH, '') = 'P' 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 ) SELECT [DIA].[CASECODE],[DIA].[ACTIONID],[DIA].[DATE],[DIA].[TxmDate], [DIA].[EMAIL],[DIA].[ATTACHMENTS],[DIA].[EMAILADDRESS],[DIA].[ADDRESSTO],[DIA].[SUBJECT],[DIA].[DisplayText], [DIA].[PROCESSTYPE],[DIA].[ACTIONTYPE], [DA].[DIARYID],[DA].[TrackReference],[DA].[DocClass],[DA].[DATEENTERED],[DA].[TYPE], [DA].[FILEPATH],[DA].[NAME],[DA].[SOURCE], convert(VARCHAR(500), ISNULL([DX].[IMDocID], '')) AS [IMDocID], convert(VARCHAR(20), ISNULL([DL].[IMClass], '')) AS [IMClass], RTRIM(convert(VARCHAR(2000), ISNULL([DL].[Description], ISNULL([DA].[Document], '')))) AS [Document], CASE WHEN [DIA].[ACTIONTYPE] = 'A' THEN 'Action' WHEN [DIA].[ACTIONTYPE] = 'N' THEN 'Note' WHEN [DIA].[ACTIONTYPE] = 'P' THEN 'Appointment' WHEN [DIA].[ACTIONTYPE] = 'R' THEN 'Reminder' WHEN [DIA].[ACTIONTYPE] = 'E' THEN 'Email' WHEN [DIA].[ACTIONTYPE] = 'T' THEN 'Phone Message' WHEN [DIA].[ACTIONTYPE] = 'D' THEN 'Dictation' WHEN [DIA].[ACTIONTYPE] = 'U' THEN 'Undertaking' WHEN [DIA].[ACTIONTYPE] = 'S' THEN 'Statute Date' WHEN [DIA].[ACTIONTYPE] = 'C' THEN 'Critical Date' WHEN [DIA].[ACTIONTYPE] = 'O' THEN 'Court Date' WHEN [DIA].[ACTIONTYPE] = 'M' THEN 'Scanned Post/Mail' WHEN RTRIM(ISNULL([DIA].[ACTIONTYPE], '')) = '' THEN CASE WHEN convert(VARCHAR(1), CASE WHEN RTRIM(ISNULL([DIA].[EMAIL], 'N')) = 'Y' THEN 'Y' ELSE 'N' END) = 'Y' THEN 'Email' WHEN convert(VARCHAR(500), ISNULL([DX].[IMDocID], '')) <> '' THEN 'IManage document' ELSE 'Unknown' END ELSE 'Action Type: ' + [DIA].[ACTIONTYPE] END AS [ActionTypeDescription], CASE WHEN convert(VARCHAR(10), ISNULL([DA].[DocClass], '')) <> '' THEN RTRIM(ISNULL(DCL.[CLASSDESCRIPTION], '')) ELSE CASE WHEN [DCL].[ClassCode] = 'ATTNOTES' THEN 'Attendance Notes' WHEN [DCL].[ClassCode] = 'BILL' THEN 'Bill' WHEN [DCL].[ClassCode] = 'FAX' THEN 'Fax' WHEN [DCL].[ClassCode] = 'LEGAL' THEN 'Legal' WHEN [DCL].[ClassCode] = 'SCAN' THEN 'Scan' WHEN [DCL].[ClassCode] = 'COMPARE' THEN 'Compare Document' WHEN [DCL].[ClassCode] = 'DISCUSSION' THEN 'Discussion' WHEN [DCL].[ClassCode] = 'DOC' THEN 'Document' WHEN [DCL].[ClassCode] = 'E-MAIL' THEN 'E-Mail' WHEN [DCL].[ClassCode] = 'EVENT' THEN 'Event' WHEN [DCL].[ClassCode] = 'LETTER' THEN 'Letter' WHEN [DCL].[ClassCode] = 'MEMO' THEN 'Memo' WHEN [DCL].[ClassCode] = 'PAGE_ICON' THEN 'Page Icon' WHEN [DCL].[ClassCode] = 'TASK' THEN 'Task' WHEN [DCL].[ClassCode] = 'TEXT' THEN 'Text File' WHEN [DCL].[ClassCode] = 'WEBDOC' THEN 'InfoLink Web Page' ELSE ISNULL([DCL].[CLASSCODE], '') END END AS [DocumentClassDescription] -- , Count(1) Over() as TotalRowsCount INTO #DiaryTableList FROM [dbo].[DocFolderDocuments] [DDC] INNER JOIN dbo.[diary] [DIA] INNER JOIN dbo.[DiaryAttachments] [DA] ON [DA].[DiaryID] = [DIA].[ACTIONID] AND [DIA].CASECODE = [DA].CASECODE ON [DA].[TrackReference] = [DDC].[TrackReference] LEFT OUTER JOIN [dbo].[DAIMXRef] [DX] LEFT OUTER JOIN @IMDocTable [DL] ON [DL].[IMDocID] = [DX].[IMDocID] ON [DX].[TrackReference] = [DA].[TrackReference] LEFT OUTER JOIN [dbo].[DocumentClasses] DCL ON convert(VARCHAR(10), ISNULL([DA].[DocClass], '')) <> '' AND DCL.[CLASSCODE] = CASE WHEN convert(VARCHAR(10), ISNULL([DA].[DocClass], '')) = '' THEN convert(VARCHAR(20), ISNULL([DL].[IMClass], '')) ELSE convert(VARCHAR(10), ISNULL([DA].[DocClass], '')) END WHERE [DDC].[DocFolderID] = @DocFolderID AND ISNULL([DIA].PUBLISH, '') = 'P' 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 ) ORDER BY CASE WHEN @SortDirection = 'ASC' AND @SortColumn!='RowNumber' THEN CASE @SortColumn WHEN 'Type' THEN convert(VARCHAR(20), UPPER(RTRIM(ISNULL([DA].[TYPE], '')))) END END, CASE WHEN @SortDirection = 'DESC' AND @SortColumn!='RowNumber' THEN CASE @SortColumn WHEN 'Type' THEN convert(VARCHAR(20), UPPER(RTRIM(ISNULL([DA].[TYPE], '')))) END END DESC, CASE WHEN @SortDirection = 'ASC' AND @SortColumn!='RowNumber' THEN CASE @SortColumn WHEN 'Document' THEN RTRIM(convert(VARCHAR(2000), ISNULL([DL].[Description], ISNULL([DA].[Document], '')))) END END, CASE WHEN @SortDirection = 'DESC' AND @SortColumn!='RowNumber' THEN CASE @SortColumn WHEN 'Document' THEN RTRIM(convert(VARCHAR(2000), ISNULL([DL].[Description], ISNULL([DA].[Document], '')))) END END DESC, CASE WHEN @SortDirection = 'ASC' AND @SortColumn!='RowNumber' THEN CASE @SortColumn WHEN 'DocumentClassDescription' THEN CASE WHEN convert(VARCHAR(10), ISNULL([DA].[DocClass], '')) <> '' THEN RTRIM(ISNULL(DCL.[CLASSDESCRIPTION], '')) ELSE CASE WHEN [DCL].[ClassCode] = 'ATTNOTES' THEN 'Attendance Notes' WHEN [DCL].[ClassCode] = 'BILL' THEN 'Bill' WHEN [DCL].[ClassCode] = 'FAX' THEN 'Fax' WHEN [DCL].[ClassCode] = 'LEGAL' THEN 'Legal' WHEN [DCL].[ClassCode] = 'SCAN' THEN 'Scan' WHEN [DCL].[ClassCode] = 'COMPARE' THEN 'Compare Document' WHEN [DCL].[ClassCode] = 'DISCUSSION' THEN 'Discussion' WHEN [DCL].[ClassCode] = 'DOC' THEN 'Document' WHEN [DCL].[ClassCode] = 'E-MAIL' THEN 'E-Mail' WHEN [DCL].[ClassCode] = 'EVENT' THEN 'Event' WHEN [DCL].[ClassCode] = 'LETTER' THEN 'Letter' WHEN [DCL].[ClassCode] = 'MEMO' THEN 'Memo' WHEN [DCL].[ClassCode] = 'PAGE_ICON' THEN 'Page Icon' WHEN [DCL].[ClassCode] = 'TASK' THEN 'Task' WHEN [DCL].[ClassCode] = 'TEXT' THEN 'Text File' WHEN [DCL].[ClassCode] = 'WEBDOC' THEN 'InfoLink Web Page' ELSE ISNULL([DCL].[CLASSCODE], '') END END END END, CASE WHEN @SortDirection = 'DESC' AND @SortColumn!='RowNumber' THEN CASE @SortColumn WHEN 'DocumentClassDescription' THEN CASE WHEN convert(VARCHAR(10), ISNULL([DA].[DocClass], '')) <> '' THEN RTRIM(ISNULL(DCL.[CLASSDESCRIPTION], '')) ELSE CASE WHEN [DCL].[ClassCode] = 'ATTNOTES' THEN 'Attendance Notes' WHEN [DCL].[ClassCode] = 'BILL' THEN 'Bill' WHEN [DCL].[ClassCode] = 'FAX' THEN 'Fax' WHEN [DCL].[ClassCode] = 'LEGAL' THEN 'Legal' WHEN [DCL].[ClassCode] = 'SCAN' THEN 'Scan' WHEN [DCL].[ClassCode] = 'COMPARE' THEN 'Compare Document' WHEN [DCL].[ClassCode] = 'DISCUSSION' THEN 'Discussion' WHEN [DCL].[ClassCode] = 'DOC' THEN 'Document' WHEN [DCL].[ClassCode] = 'E-MAIL' THEN 'E-Mail' WHEN [DCL].[ClassCode] = 'EVENT' THEN 'Event' WHEN [DCL].[ClassCode] = 'LETTER' THEN 'Letter' WHEN [DCL].[ClassCode] = 'MEMO' THEN 'Memo' WHEN [DCL].[ClassCode] = 'PAGE_ICON' THEN 'Page Icon' WHEN [DCL].[ClassCode] = 'TASK' THEN 'Task' WHEN [DCL].[ClassCode] = 'TEXT' THEN 'Text File' WHEN [DCL].[ClassCode] = 'WEBDOC' THEN 'InfoLink Web Page' ELSE ISNULL([DCL].[CLASSCODE], '') END END END END DESC, CASE WHEN @SortDirection = 'ASC' AND @SortColumn!='RowNumber' THEN CASE @SortColumn WHEN 'DocumentDate' THEN convert(DATETIME, [DA].[DATEENTERED]) END END, CASE WHEN @SortDirection = 'DESC' AND @SortColumn!='RowNumber' THEN CASE @SortColumn WHEN 'DocumentDate' THEN convert(DATETIME, [DA].[DATEENTERED]) END END DESC, [DA].[TrackReference] DESC OFFSET @PageSize * (@PageNo - 1) ROWS FETCH NEXT @PageSize ROWS ONLY OPTION (RECOMPILE); --SELECT * from #DiaryTableList INSERT INTO @res ([ActionId], [TrackReference], [IMDocID], [IMClass], [DocClass], [Document], [DiaryDate], [DocumentDate], [EmailSent], [TYPE], [Attachments], [EMAIL], [EmailFrom], [EmailTo], [CurrentVersion], [IsInDocfolder], [IsShared], [DocumentClassDescription], [UniqueId], [FilePath], [Subject], [ShortText], [NAME], [ProcessType], [ACTIONTYPE], [ActionTypeDescription], [DOCUMENTSOURCE] , [TotalRecord] ) SELECT convert(INT, [DIA].[DIARYID]) AS [ActionID], convert(INT, [DIA].[TrackReference]) AS [TrackReference], convert(VARCHAR(500), ISNULL([DX].[IMDocID], '')) AS [IMDocID], convert(VARCHAR(20), ISNULL([DIA].[IMClass], '')) AS [IMClass], convert(VARCHAR(10), ISNULL([DIA].[DocClass], '')) AS [DocClass], RTRIM(convert(VARCHAR(2000), ISNULL([DL].[Description], ISNULL([DIA].[Document], '')))) AS [Document], convert(DATETIME, [DIA].[DATE]) As [DiaryDate], convert(DATETIME, [DIA].[DATEENTERED]) AS [DocumentDate], convert(DATETIME, ISNULL([DIA].[TxmDate], [DIA].[Date])) As [EMAILSENT], convert(VARCHAR(20), UPPER(RTRIM(ISNULL([DIA].[TYPE], '')))) AS [TYPE], --CASE WHEN [DIA].[ATTACHMENTS] = 'Y' THEN 'Y' ELSE 'N' END AS [Attachments], (CASE WHEN ISNULL( convert(VARCHAR(1), CASE WHEN RTRIM(ISNULL([DIA].[EMAIL], 'N')) = 'Y' THEN 'Y' ELSE 'N' END), '') = 'Y' AND convert(INT, [DIA].[TrackReference]) = [OMS].[TrackReference] THEN (CASE WHEN [DIA].[ATTACHMENTS] = 'Y' THEN 'Y' ELSE 'N' END ) WHEN ISNULL(convert(VARCHAR(20), UPPER(RTRIM(ISNULL([DIA].[TYPE], '')))), '') = 'MSG' AND convert(INT, [DIA].[TrackReference]) = [OMS].[TrackReference] THEN (CASE WHEN [DIA].[ATTACHMENTS] = 'Y' THEN 'Y' ELSE 'N' END) ELSE 'N' END) AS [Attachments], -- convert(VARCHAR(1), CASE WHEN RTRIM(ISNULL([DIA].[EMAIL], 'N')) = 'Y' THEN 'Y' ELSE 'N' END) AS [EMAIL], (CASE WHEN convert(VARCHAR(1), CASE WHEN RTRIM(ISNULL([DIA].[EMAIL], 'N')) = 'Y' THEN 'Y' ELSE 'N' END) = 'Y' AND convert(INT, [DIA].[TrackReference]) = [OMS].[TrackReference] THEN 'Y' WHEN ISNULL(convert(VARCHAR(20), UPPER(RTRIM(ISNULL([DIA].[TYPE], '')))), '') = 'MSG' AND convert(INT, [DIA].[TrackReference]) = [OMS].[TrackReference] THEN 'Y' ELSE 'N' END) AS [EMAIL], --convert(VARCHAR(200), RTRIM(ISNULL([DIA].[EMAILADDRESS], ''))) AS [EMAILFROM], RTRIM(ISNULL(CONVERT(VARCHAR(200), SUBSTRING(convert(VARCHAR(200), RTRIM(ISNULL([DIA].[EMAILADDRESS], ''))), 1, 200)), '')) AS [EMAILFROM], -- RTRIM(ISNULL(CONVERT(VARCHAR(1000), [DIA].[ADDRESSTO]), '')) AS [EMAILTO], RTRIM(ISNULL(CONVERT(VARCHAR(1000), SUBSTRING(RTRIM(ISNULL(CONVERT(VARCHAR(1000), [DIA].[ADDRESSTO]), '')), 1, 1000)), '')) AS [EMAILTO], [DV].[CurrentVersion], CONVERT(BIT, 1), [SHD].[IsShared], CASE WHEN convert(VARCHAR(10), ISNULL([DIA].[DocClass], '')) <> '' THEN RTRIM(ISNULL(DCL.[CLASSDESCRIPTION], '')) ELSE CASE WHEN [DCL].[ClassCode] = 'ATTNOTES' THEN 'Attendance Notes' WHEN [DCL].[ClassCode] = 'BILL' THEN 'Bill' WHEN [DCL].[ClassCode] = 'FAX' THEN 'Fax' WHEN [DCL].[ClassCode] = 'LEGAL' THEN 'Legal' WHEN [DCL].[ClassCode] = 'SCAN' THEN 'Scan' WHEN [DCL].[ClassCode] = 'COMPARE' THEN 'Compare Document' WHEN [DCL].[ClassCode] = 'DISCUSSION' THEN 'Discussion' WHEN [DCL].[ClassCode] = 'DOC' THEN 'Document' WHEN [DCL].[ClassCode] = 'E-MAIL' THEN 'E-Mail' WHEN [DCL].[ClassCode] = 'EVENT' THEN 'Event' WHEN [DCL].[ClassCode] = 'LETTER' THEN 'Letter' WHEN [DCL].[ClassCode] = 'MEMO' THEN 'Memo' WHEN [DCL].[ClassCode] = 'PAGE_ICON' THEN 'Page Icon' WHEN [DCL].[ClassCode] = 'TASK' THEN 'Task' WHEN [DCL].[ClassCode] = 'TEXT' THEN 'Text File' WHEN [DCL].[ClassCode] = 'WEBDOC' THEN 'InfoLink Web Page' ELSE ISNULL([DCL].[CLASSCODE], '') END END AS [DocumentClassDescription], @uniqueid AS uniqueid, CASE WHEN [UNC].[UNC] IS NULL THEN RTRIM(ISNULL([DIA].[FILEPATH], '')) ELSE [UNC].[UNC] + SUBSTRING(RTRIM(ISNULL([DIA].[FILEPATH], '')), 3, LEN(RTRIM(ISNULL([DIA].[FILEPATH], ''))) - 2) END AS [FILEPATH], -- [RES].[EMAILSENT], RTRIM(ISNULL(CONVERT(VARCHAR(1000), SUBSTRING(RTRIM(ISNULL(CONVERT(VARCHAR(2000), [DIA].[SUBJECT]),'')), 1, 1000)), '')) AS [SUBJECT], RTRIM(ISNULL(CONVERT(VARCHAR(120), SUBSTRING(RTRIM(ISNULL(CONVERT(VARCHAR(120), [DIA].[DisplayText]),'')), 1, 120)), '')) AS [ShortText], RTRIM(ISNULL([DIA].[NAME], '')) AS [NAME], RTRIM(ISNULL([DIA].[PROCESSTYPE], '')) AS [PROCESSTYPE], RTRIM(ISNULL([DIA].[ACTIONTYPE], '')) AS [ACTIONTYPE], CASE WHEN [DIA].[ACTIONTYPE] = 'A' THEN 'Action' WHEN [DIA].[ACTIONTYPE] = 'N' THEN 'Note' WHEN [DIA].[ACTIONTYPE] = 'P' THEN 'Appointment' WHEN [DIA].[ACTIONTYPE] = 'R' THEN 'Reminder' WHEN [DIA].[ACTIONTYPE] = 'E' THEN 'Email' WHEN [DIA].[ACTIONTYPE] = 'T' THEN 'Phone Message' WHEN [DIA].[ACTIONTYPE] = 'D' THEN 'Dictation' WHEN [DIA].[ACTIONTYPE] = 'U' THEN 'Undertaking' WHEN [DIA].[ACTIONTYPE] = 'S' THEN 'Statute Date' WHEN [DIA].[ACTIONTYPE] = 'C' THEN 'Critical Date' WHEN [DIA].[ACTIONTYPE] = 'O' THEN 'Court Date' WHEN [DIA].[ACTIONTYPE] = 'M' THEN 'Scanned Post/Mail' WHEN RTRIM(ISNULL([DIA].[ACTIONTYPE], '')) = '' THEN CASE WHEN convert(VARCHAR(1), CASE WHEN RTRIM(ISNULL([DIA].[EMAIL], 'N')) = 'Y' THEN 'Y' ELSE 'N' END) = 'Y' THEN 'Email' WHEN convert(VARCHAR(500), ISNULL([DX].[IMDocID], '')) <> '' THEN 'IManage document' ELSE 'Unknown' END ELSE 'Action Type: ' + [DIA].[ACTIONTYPE] END AS [ActionTypeDescription], RTRIM(ISNULL([DIA].[SOURCE], CASE WHEN [DIA].[ACTIONID] IS NULL THEN 'IManage' ELSE '' END)) AS [DOCUMENTSOURCE] ,Count(1) over() [TotalRecord] FROM #DiaryTableList [DIA] --ON [DIA].[TrackReference] = [DDC].[TrackReference] CROSS APPLY (SELECT ISNULL(MAX([DAV].[Version]), 0) + 1 AS [CurrentVersion] FROM [dbo].[DiaryAttachmentVersioning] [DAV] WHERE TRACKREFERENCE = [DIA].[TRACKREFERENCE]) [DV] CROSS APPLY (SELECT CASE WHEN COUNT(1) = 0 THEN CONVERT(BIT, 0) ELSE CONVERT(BIT, 1) END AS [IsShared] FROM [dbo].[FileSharingDocuments] [FSD] INNER JOIN [dbo].[FileSharingMapping] [FSM] INNER JOIN [dbo].[FileSharingDocFolders] [FSDF] ON [FSDF].[DocFolderID] = [FSM].[FileSharingDocFoldersID] AND [FSDF].[Shared] = 1 ON [FSM].[id] = [FSD].[FileSharingMappingID] AND [FSM].[FileSharingDocFoldersID] = @DocFolderID WHERE [FSD].[TrackReference] = [DIA].[TrackReference]) [SHD] LEFT OUTER JOIN [dbo].[DAIMXRef] [DX] LEFT OUTER JOIN @IMDocTable [DL] ON [DL].[IMDocID] = [DX].[IMDocID] ON [DX].[TrackReference] = [DIA].[TrackReference] LEFT OUTER JOIN [dbo].[DocumentClasses] DCL ON convert(VARCHAR(10), ISNULL([DIA].[DocClass], '')) <> '' AND DCL.[CLASSCODE] = CASE WHEN convert(VARCHAR(10), ISNULL([DIA].[DocClass], '')) = '' THEN convert(VARCHAR(20), ISNULL([DL].[IMClass], '')) ELSE convert(VARCHAR(10), ISNULL([DIA].[DocClass], '')) END LEFT OUTER JOIN [dbo].[UNCAlias] [UNC] ON SUBSTRING([DIA].[FilePath], 2, 1) = ':' AND [UNC].[Drive] = SUBSTRING([DIA].[FilePath], 1, 1) OUTER APPLY ( SELECT TOP 1 [MST].[TrackReference] AS [TrackReference], [MST].[Type] AS [Type] FROM #DiaryTableList [MST] WHERE [MST].[diaryid] = [DIA].[ActionID] --AND [MST].[TrackReference] = [DA].[TrackReference] ORDER BY CASE WHEN [MST].Type = 'MSG' THEN 0 ELSE 1 END, [MST].[TrackReference]) [OMS] END 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 ISNULL([DIA].PUBLISH, '') = 'P' 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 ) SELECT [DIA].[CASECODE],[DIA].[ACTIONID],[DIA].[DATE],[DIA].[TxmDate], [DIA].[EMAIL],[DIA].[ATTACHMENTS],[DIA].[EMAILADDRESS],[DIA].[ADDRESSTO],[DIA].[SUBJECT],[DIA].[DisplayText], [DIA].[PROCESSTYPE],[DIA].[ACTIONTYPE], [DA].[DIARYID],[DA].[TrackReference],[DA].[DocClass],[DA].[DATEENTERED],[DA].[TYPE], [DA].[FILEPATH],[DA].[NAME],[DA].[SOURCE], convert(VARCHAR(500), ISNULL([DX].[IMDocID], '')) AS [IMDocID], convert(VARCHAR(20), ISNULL([DL].[IMClass], '')) AS [IMClass], RTRIM(convert(VARCHAR(2000), ISNULL([DL].[Description], ISNULL([DA].[Document], '')))) AS [Document], CASE WHEN [DIA].[ACTIONTYPE] = 'A' THEN 'Action' WHEN [DIA].[ACTIONTYPE] = 'N' THEN 'Note' WHEN [DIA].[ACTIONTYPE] = 'P' THEN 'Appointment' WHEN [DIA].[ACTIONTYPE] = 'R' THEN 'Reminder' WHEN [DIA].[ACTIONTYPE] = 'E' THEN 'Email' WHEN [DIA].[ACTIONTYPE] = 'T' THEN 'Phone Message' WHEN [DIA].[ACTIONTYPE] = 'D' THEN 'Dictation' WHEN [DIA].[ACTIONTYPE] = 'U' THEN 'Undertaking' WHEN [DIA].[ACTIONTYPE] = 'S' THEN 'Statute Date' WHEN [DIA].[ACTIONTYPE] = 'C' THEN 'Critical Date' WHEN [DIA].[ACTIONTYPE] = 'O' THEN 'Court Date' WHEN [DIA].[ACTIONTYPE] = 'M' THEN 'Scanned Post/Mail' WHEN RTRIM(ISNULL([DIA].[ACTIONTYPE], '')) = '' THEN CASE WHEN convert(VARCHAR(1), CASE WHEN RTRIM(ISNULL([DIA].[EMAIL], 'N')) = 'Y' THEN 'Y' ELSE 'N' END) = 'Y' THEN 'Email' WHEN convert(VARCHAR(500), ISNULL([DX].[IMDocID], '')) <> '' THEN 'IManage document' ELSE 'Unknown' END ELSE 'Action Type: ' + [DIA].[ACTIONTYPE] END AS [ActionTypeDescription], CASE WHEN convert(VARCHAR(10), ISNULL([DA].[DocClass], '')) <> '' THEN RTRIM(ISNULL(DCL.[CLASSDESCRIPTION], '')) ELSE CASE WHEN [DCL].[ClassCode] = 'ATTNOTES' THEN 'Attendance Notes' WHEN [DCL].[ClassCode] = 'BILL' THEN 'Bill' WHEN [DCL].[ClassCode] = 'FAX' THEN 'Fax' WHEN [DCL].[ClassCode] = 'LEGAL' THEN 'Legal' WHEN [DCL].[ClassCode] = 'SCAN' THEN 'Scan' WHEN [DCL].[ClassCode] = 'COMPARE' THEN 'Compare Document' WHEN [DCL].[ClassCode] = 'DISCUSSION' THEN 'Discussion' WHEN [DCL].[ClassCode] = 'DOC' THEN 'Document' WHEN [DCL].[ClassCode] = 'E-MAIL' THEN 'E-Mail' WHEN [DCL].[ClassCode] = 'EVENT' THEN 'Event' WHEN [DCL].[ClassCode] = 'LETTER' THEN 'Letter' WHEN [DCL].[ClassCode] = 'MEMO' THEN 'Memo' WHEN [DCL].[ClassCode] = 'PAGE_ICON' THEN 'Page Icon' WHEN [DCL].[ClassCode] = 'TASK' THEN 'Task' WHEN [DCL].[ClassCode] = 'TEXT' THEN 'Text File' WHEN [DCL].[ClassCode] = 'WEBDOC' THEN 'InfoLink Web Page' ELSE ISNULL([DCL].[CLASSCODE], '') END END AS [DocumentClassDescription] --,Count(1) Over() as TotalRowsCount INTO #DiaryTable FROM dbo.[diary] [DIA] INNER JOIN dbo.[DiaryAttachments] [DA] ON [DA].[DiaryID] = [DIA].[ACTIONID] AND [DIA].CASECODE = [DA].CASECODE LEFT OUTER JOIN [dbo].[DAIMXRef] [DX] ON [DX].[TrackReference] = [DA].[TrackReference] LEFT OUTER JOIN @IMDocTable [DL] ON [DL].[IMDocID] = [DX].[IMDocID] LEFT OUTER JOIN [dbo].[DocumentClasses] DCL ON convert(VARCHAR(10), ISNULL([DA].[DocClass], '')) <> '' AND DCL.[CLASSCODE] = CASE WHEN convert(VARCHAR(10), ISNULL([DA].[DocClass], '')) = '' THEN convert(VARCHAR(20), ISNULL([DL].[IMClass], '')) ELSE convert(VARCHAR(10), ISNULL([DA].[DocClass], '')) END WHERE [DIA].[ACTIONID] = @ActionID AND ISNULL([DIA].PUBLISH, '') = 'P' 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 ) ORDER BY CASE WHEN @SortDirection = 'ASC' AND @SortColumn!='RowNumber' THEN CASE @SortColumn WHEN 'Type' THEN convert(VARCHAR(20), UPPER(RTRIM(ISNULL([DA].[TYPE], '')))) END END, CASE WHEN @SortDirection = 'DESC' AND @SortColumn!='RowNumber' THEN CASE @SortColumn WHEN 'Type' THEN convert(VARCHAR(20), UPPER(RTRIM(ISNULL([DA].[TYPE], '')))) END END DESC, CASE WHEN @SortDirection = 'ASC' AND @SortColumn!='RowNumber' THEN CASE @SortColumn WHEN 'Document' THEN RTRIM(convert(VARCHAR(2000), ISNULL([DL].[Description], ISNULL([DA].[Document], '')))) END END, CASE WHEN @SortDirection = 'DESC' AND @SortColumn!='RowNumber' THEN CASE @SortColumn WHEN 'Document' THEN RTRIM(convert(VARCHAR(2000), ISNULL([DL].[Description], ISNULL([DA].[Document], '')))) END END DESC, CASE WHEN @SortDirection = 'ASC' AND @SortColumn!='RowNumber' THEN CASE @SortColumn WHEN 'DocumentClassDescription' THEN CASE WHEN convert(VARCHAR(10), ISNULL([DA].[DocClass], '')) <> '' THEN RTRIM(ISNULL(DCL.[CLASSDESCRIPTION], '')) ELSE CASE WHEN [DCL].[ClassCode] = 'ATTNOTES' THEN 'Attendance Notes' WHEN [DCL].[ClassCode] = 'BILL' THEN 'Bill' WHEN [DCL].[ClassCode] = 'FAX' THEN 'Fax' WHEN [DCL].[ClassCode] = 'LEGAL' THEN 'Legal' WHEN [DCL].[ClassCode] = 'SCAN' THEN 'Scan' WHEN [DCL].[ClassCode] = 'COMPARE' THEN 'Compare Document' WHEN [DCL].[ClassCode] = 'DISCUSSION' THEN 'Discussion' WHEN [DCL].[ClassCode] = 'DOC' THEN 'Document' WHEN [DCL].[ClassCode] = 'E-MAIL' THEN 'E-Mail' WHEN [DCL].[ClassCode] = 'EVENT' THEN 'Event' WHEN [DCL].[ClassCode] = 'LETTER' THEN 'Letter' WHEN [DCL].[ClassCode] = 'MEMO' THEN 'Memo' WHEN [DCL].[ClassCode] = 'PAGE_ICON' THEN 'Page Icon' WHEN [DCL].[ClassCode] = 'TASK' THEN 'Task' WHEN [DCL].[ClassCode] = 'TEXT' THEN 'Text File' WHEN [DCL].[ClassCode] = 'WEBDOC' THEN 'InfoLink Web Page' ELSE ISNULL([DCL].[CLASSCODE], '') END END END END, CASE WHEN @SortDirection = 'DESC' AND @SortColumn!='RowNumber' THEN CASE @SortColumn WHEN 'DocumentClassDescription' THEN CASE WHEN convert(VARCHAR(10), ISNULL([DA].[DocClass], '')) <> '' THEN RTRIM(ISNULL(DCL.[CLASSDESCRIPTION], '')) ELSE CASE WHEN [DCL].[ClassCode] = 'ATTNOTES' THEN 'Attendance Notes' WHEN [DCL].[ClassCode] = 'BILL' THEN 'Bill' WHEN [DCL].[ClassCode] = 'FAX' THEN 'Fax' WHEN [DCL].[ClassCode] = 'LEGAL' THEN 'Legal' WHEN [DCL].[ClassCode] = 'SCAN' THEN 'Scan' WHEN [DCL].[ClassCode] = 'COMPARE' THEN 'Compare Document' WHEN [DCL].[ClassCode] = 'DISCUSSION' THEN 'Discussion' WHEN [DCL].[ClassCode] = 'DOC' THEN 'Document' WHEN [DCL].[ClassCode] = 'E-MAIL' THEN 'E-Mail' WHEN [DCL].[ClassCode] = 'EVENT' THEN 'Event' WHEN [DCL].[ClassCode] = 'LETTER' THEN 'Letter' WHEN [DCL].[ClassCode] = 'MEMO' THEN 'Memo' WHEN [DCL].[ClassCode] = 'PAGE_ICON' THEN 'Page Icon' WHEN [DCL].[ClassCode] = 'TASK' THEN 'Task' WHEN [DCL].[ClassCode] = 'TEXT' THEN 'Text File' WHEN [DCL].[ClassCode] = 'WEBDOC' THEN 'InfoLink Web Page' ELSE ISNULL([DCL].[CLASSCODE], '') END END END END DESC, CASE WHEN @SortDirection = 'ASC' AND @SortColumn!='RowNumber' THEN CASE @SortColumn WHEN 'DocumentDate' THEN convert(DATETIME, [DA].[DATEENTERED]) END END, CASE WHEN @SortDirection = 'DESC' AND @SortColumn!='RowNumber' THEN CASE @SortColumn WHEN 'DocumentDate' THEN convert(DATETIME, [DA].[DATEENTERED]) END END DESC, [DA].[TrackReference] DESC OFFSET @PageSize * (@PageNo - 1) ROWS FETCH NEXT @PageSize ROWS ONLY OPTION (RECOMPILE); --SELECT * from #DiaryTable --select * from @IMDocTable INSERT INTO @res ([ActionId], [TrackReference], [IMDocID], [IMClass], [DocClass], [Document], [DiaryDate], [DocumentDate], [EmailSent], [TYPE], [Attachments], [EMAIL], [EmailFrom], [EmailTo], [CurrentVersion], [IsInDocFolder], [IsShared], [DocumentClassDescription], [UniqueId], [FilePath], [Subject], [ShortText], [NAME], [ProcessType], [ACTIONTYPE], [ActionTypeDescription], [DOCUMENTSOURCE] ,[TotalRecord] ) SELECT convert(INT, [DIA].[DIARYID]) AS [ActionID], convert(INT, [DIA].[TrackReference]) AS [TrackReference], convert(VARCHAR(500), ISNULL([DX].[IMDocID], '')) AS [IMDocID], convert(VARCHAR(20), ISNULL([DL].[IMClass], '')) AS [IMClass], convert(VARCHAR(10), ISNULL([DIA].[DocClass], '')) AS [DocClass], RTRIM(convert(VARCHAR(2000), ISNULL([DL].[Description], ISNULL([DIA].[Document], '')))) AS [Document], convert(DATETIME, [DIA].[DATE]) As [DiaryDate], convert(DATETIME, [DIA].[DATEENTERED]) AS [DocumentDate], convert(DATETIME, ISNULL([DIA].[TxmDate], [DIA].[Date])) As [EMAILSENT], convert(VARCHAR(20), UPPER(RTRIM(ISNULL([DIA].[TYPE], '')))) AS [TYPE], -- CASE WHEN [DIA].[ATTACHMENTS] = 'Y' THEN 'Y' ELSE 'N' END AS [Attachments], (CASE WHEN ISNULL( convert(VARCHAR(1), CASE WHEN RTRIM(ISNULL([DIA].[EMAIL], 'N')) = 'Y' THEN 'Y' ELSE 'N' END), '') = 'Y' AND convert(INT, [DIA].[TrackReference]) = [OMS].[TrackReference] THEN (CASE WHEN [DIA].[ATTACHMENTS] = 'Y' THEN 'Y' ELSE 'N' END ) WHEN ISNULL(convert(VARCHAR(20), UPPER(RTRIM(ISNULL([DIA].[TYPE], '')))), '') = 'MSG' AND convert(INT, [DIA].[TrackReference]) = [OMS].[TrackReference] THEN (CASE WHEN [DIA].[ATTACHMENTS] = 'Y' THEN 'Y' ELSE 'N' END) ELSE 'N' END) AS [Attachments], --convert(VARCHAR(1), CASE WHEN RTRIM(ISNULL([DIA].[EMAIL], 'N')) = 'Y' THEN 'Y' ELSE 'N' END) AS [EMAIL], (CASE WHEN convert(VARCHAR(1), CASE WHEN RTRIM(ISNULL([DIA].[EMAIL], 'N')) = 'Y' THEN 'Y' ELSE 'N' END) = 'Y' AND convert(INT, [DIA].[TrackReference]) = [OMS].[TrackReference] THEN 'Y' WHEN ISNULL(convert(VARCHAR(20), UPPER(RTRIM(ISNULL([DIA].[TYPE], '')))), '') = 'MSG' AND convert(INT, [DIA].[TrackReference]) = [OMS].[TrackReference] THEN 'Y' ELSE 'N' END) AS [EMAIL], -- convert(VARCHAR(200), RTRIM(ISNULL([DIA].[EMAILADDRESS], ''))) AS [EMAILFROM], RTRIM(ISNULL(CONVERT(VARCHAR(200), SUBSTRING(convert(VARCHAR(200), RTRIM(ISNULL([DIA].[EMAILADDRESS], ''))), 1, 200)), '')) AS [EMAILFROM], --RTRIM(ISNULL(CONVERT(VARCHAR(1000), [DIA].[ADDRESSTO]), '')) AS [EMAILTO], RTRIM(ISNULL(CONVERT(VARCHAR(1000), SUBSTRING(RTRIM(ISNULL(CONVERT(VARCHAR(1000), [DIA].[ADDRESSTO]), '')), 1, 1000)), '')) AS [EMAILTO], [DV].[CurrentVersion], [IND].[InDocFolder], [SHD].[IsShared], CASE WHEN convert(VARCHAR(10), ISNULL([DIA].[DocClass], '')) <> '' THEN RTRIM(ISNULL(DCL.[CLASSDESCRIPTION], '')) ELSE CASE WHEN [DCL].[ClassCode] = 'ATTNOTES' THEN 'Attendance Notes' WHEN [DCL].[ClassCode] = 'BILL' THEN 'Bill' WHEN [DCL].[ClassCode] = 'FAX' THEN 'Fax' WHEN [DCL].[ClassCode] = 'LEGAL' THEN 'Legal' WHEN [DCL].[ClassCode] = 'SCAN' THEN 'Scan' WHEN [DCL].[ClassCode] = 'COMPARE' THEN 'Compare Document' WHEN [DCL].[ClassCode] = 'DISCUSSION' THEN 'Discussion' WHEN [DCL].[ClassCode] = 'DOC' THEN 'Document' WHEN [DCL].[ClassCode] = 'E-MAIL' THEN 'E-Mail' WHEN [DCL].[ClassCode] = 'EVENT' THEN 'Event' WHEN [DCL].[ClassCode] = 'LETTER' THEN 'Letter' WHEN [DCL].[ClassCode] = 'MEMO' THEN 'Memo' WHEN [DCL].[ClassCode] = 'PAGE_ICON' THEN 'Page Icon' WHEN [DCL].[ClassCode] = 'TASK' THEN 'Task' WHEN [DCL].[ClassCode] = 'TEXT' THEN 'Text File' WHEN [DCL].[ClassCode] = 'WEBDOC' THEN 'InfoLink Web Page' ELSE ISNULL([DCL].[CLASSCODE], '') END END AS [DocumentClassDescription], @uniqueid As uniqueid, CASE WHEN [UNC].[UNC] IS NULL THEN RTRIM(ISNULL([DIA].[FILEPATH], '')) ELSE [UNC].[UNC] + SUBSTRING(RTRIM(ISNULL([DIA].[FILEPATH], '')), 3, LEN(RTRIM(ISNULL([DIA].[FILEPATH], ''))) - 2) END AS [FILEPATH], -- [RES].[EMAILSENT], RTRIM(ISNULL(CONVERT(VARCHAR(1000), SUBSTRING(RTRIM(ISNULL(CONVERT(VARCHAR(2000), [DIA].[SUBJECT]),'')), 1, 1000)), '')) AS [SUBJECT], RTRIM(ISNULL(CONVERT(VARCHAR(120), SUBSTRING(RTRIM(ISNULL(CONVERT(VARCHAR(120), [DIA].[DisplayText]),'')), 1, 120)), '')) AS [ShortText], RTRIM(ISNULL([DIA].[NAME], '')) AS [NAME], RTRIM(ISNULL([DIA].[PROCESSTYPE], '')) AS [PROCESSTYPE], RTRIM(ISNULL([DIA].[ACTIONTYPE], '')) AS [ACTIONTYPE], CASE WHEN [DIA].[ACTIONTYPE] = 'A' THEN 'Action' WHEN [DIA].[ACTIONTYPE] = 'N' THEN 'Note' WHEN [DIA].[ACTIONTYPE] = 'P' THEN 'Appointment' WHEN [DIA].[ACTIONTYPE] = 'R' THEN 'Reminder' WHEN [DIA].[ACTIONTYPE] = 'E' THEN 'Email' WHEN [DIA].[ACTIONTYPE] = 'T' THEN 'Phone Message' WHEN [DIA].[ACTIONTYPE] = 'D' THEN 'Dictation' WHEN [DIA].[ACTIONTYPE] = 'U' THEN 'Undertaking' WHEN [DIA].[ACTIONTYPE] = 'S' THEN 'Statute Date' WHEN [DIA].[ACTIONTYPE] = 'C' THEN 'Critical Date' WHEN [DIA].[ACTIONTYPE] = 'O' THEN 'Court Date' WHEN [DIA].[ACTIONTYPE] = 'M' THEN 'Scanned Post/Mail' WHEN RTRIM(ISNULL([DIA].[ACTIONTYPE], '')) = '' THEN CASE WHEN convert(VARCHAR(1), CASE WHEN RTRIM(ISNULL([DIA].[EMAIL], 'N')) = 'Y' THEN 'Y' ELSE 'N' END) = 'Y' THEN 'Email' WHEN convert(VARCHAR(500), ISNULL([DX].[IMDocID], '')) <> '' THEN 'IManage document' ELSE 'Unknown' END ELSE 'Action Type: ' + [DIA].[ACTIONTYPE] END AS [ActionTypeDescription], RTRIM(ISNULL([DIA].[SOURCE], CASE WHEN [DIA].[ACTIONID] IS NULL THEN 'IManage' ELSE '' END)) AS [DOCUMENTSOURCE] ,Count(1) over() [TotalRecord] FROM #DiaryTable [DIA] CROSS APPLY (SELECT CASE WHEN COUNT(1) = 0 THEN CONVERT(BIT, 0) ELSE CONVERT(BIT, 1) END AS [InDocFolder] FROM [dbo].[DocFolderDocuments] [DFD] WHERE [DFD].[TrackReference] = [DIA].[TrackReference]) [IND] CROSS APPLY (SELECT CASE WHEN COUNT(1) = 0 THEN CONVERT(BIT, 0) ELSE CONVERT(BIT, 1) END AS [IsShared] FROM [dbo].[FileSharingDocuments] [FSD] INNER JOIN [dbo].[FileSharingMapping] [FSM] INNER JOIN [dbo].[FileSharingDocFolders] [FSDF] ON [FSDF].[DocFolderID] = [FSM].[FileSharingDocFoldersID] AND [FSDF].[Shared] = 1 ON [FSM].[id] = [FSD].[FileSharingMappingID] WHERE [FSD].[TrackReference] = [DIA].[TrackReference]) [SHD] CROSS APPLY (SELECT ISNULL(MAX([DAV].[Version]), 0) + 1 AS [CurrentVersion] FROM [dbo].[DiaryAttachmentVersioning] [DAV] WHERE TRACKREFERENCE = [DIA].[TRACKREFERENCE]) [DV] LEFT OUTER JOIN [dbo].[DAIMXRef] [DX] LEFT OUTER JOIN @IMDocTable [DL] ON [DL].[IMDocID] = [DX].[IMDocID] ON [DX].[TrackReference] = [DIA].[TrackReference] LEFT OUTER JOIN [dbo].[DocumentClasses] DCL ON convert(VARCHAR(10), ISNULL([DIA].[DocClass], '')) <> '' AND DCL.[CLASSCODE] = CASE WHEN convert(VARCHAR(10), ISNULL([DIA].[DocClass], '')) = '' THEN convert(VARCHAR(20), ISNULL([DL].[IMClass], '')) ELSE convert(VARCHAR(10), ISNULL([DIA].[DocClass], '')) END LEFT OUTER JOIN [dbo].[UNCAlias] [UNC] ON SUBSTRING([DIA].[FilePath], 2, 1) = ':' AND [UNC].[Drive] = SUBSTRING([DIA].[FilePath], 1, 1) OUTER APPLY ( SELECT TOP 1 [MST].[TrackReference] AS [TrackReference], [MST].[Type] AS [Type] FROM #DiaryTable [MST] WHERE [MST].[diaryid] = [DIA].[ActionID] --AND [MST].[TrackReference] = [DA].[TrackReference] ORDER BY CASE WHEN [MST].Type = 'MSG' THEN 0 ELSE 1 END, [MST].[TrackReference]) [OMS] END /* INSERT INTO @classes ([KeyhouseClass], [IMClass], [ClassCode], [ClassDescription]) SELECT [CLS].[KeyhouseClass], [CLS].[IMClass], [CLS].[ClassCode], CASE WHEN [CLS].[KeyhouseClass] = 1 THEN RTRIM(ISNULL(DCL.[CLASSDESCRIPTION], '')) ELSE CASE WHEN [CLS].[ClassCode] = 'ATTNOTES' THEN 'Attendance Notes' WHEN [CLS].[ClassCode] = 'BILL' THEN 'Bill' WHEN [CLS].[ClassCode] = 'FAX' THEN 'Fax' WHEN [CLS].[ClassCode] = 'LEGAL' THEN 'Legal' WHEN [CLS].[ClassCode] = 'SCAN' THEN 'Scan' WHEN [CLS].[ClassCode] = 'COMPARE' THEN 'Compare Document' WHEN [CLS].[ClassCode] = 'DISCUSSION' THEN 'Discussion' WHEN [CLS].[ClassCode] = 'DOC' THEN 'Document' WHEN [CLS].[ClassCode] = 'E-MAIL' THEN 'E-Mail' WHEN [CLS].[ClassCode] = 'EVENT' THEN 'Event' WHEN [CLS].[ClassCode] = 'LETTER' THEN 'Letter' WHEN [CLS].[ClassCode] = 'MEMO' THEN 'Memo' WHEN [CLS].[ClassCode] = 'PAGE_ICON' THEN 'Page Icon' WHEN [CLS].[ClassCode] = 'TASK' THEN 'Task' WHEN [CLS].[ClassCode] = 'TEXT' THEN 'Text File' WHEN [CLS].[ClassCode] = 'WEBDOC' THEN 'InfoLink Web Page' ELSE ISNULL([CLS].[CLASSCODE], '') END END AS [ClassDescription] FROM (SELECT DISTINCT 1 AS [KeyhouseClass], 0 AS [IMClass], CONVERT(VARCHAR(20), [RES].[DocClass]) AS [ClassCode] FROM @res [RES] WHERE ISNULL([RES].[DocClass], '') <> '' UNION SELECT DISTINCT 0 AS [KeyhouseClass], 1 AS [IMClass], CONVERT(VARCHAR(20), [RES].[IMClass]) AS [ClassCode] FROM @res [RES] WHERE ISNULL([RES].[IMClass], '') <> '') [CLS] LEFT OUTER JOIN [dbo].[DocumentClasses] DCL ON [CLS].[KeyhouseClass] = 1 AND DCL.[CLASSCODE] = [CLS].[ClassCode] */ /* UPDATE [RES] SET [RES].[EMAIL] = CASE WHEN ( ISNULL([RES].[EMAIL], '') = 'Y' AND [RES].[TrackReference] = [OMS].[TrackReference]) THEN 'Y' WHEN ( ISNULL([RES].[TYPE], '') = 'MSG' AND [RES].[TrackReference] = [OMS].[TrackReference]) THEN 'Y' ELSE 'N' END, [RES].[Attachments] = CASE WHEN ( ISNULL([RES].[EMAIL], '') = 'Y' AND [RES].[TrackReference] = [OMS].[TrackReference]) THEN [RES].[Attachments] WHEN ( ISNULL([RES].[TYPE], '') = 'MSG' AND [RES].[TrackReference] = [OMS].[TrackReference]) THEN [RES].[Attachments] ELSE 'N' END FROM @res [RES] OUTER APPLY ( SELECT TOP 1 [MST].[TrackReference] AS [TrackReference], [MST].[Type] AS [Type] FROM [dbo].[diaryattachments] [MST] WHERE [MST].[diaryid] = [RES].[ActionID] ORDER BY CASE WHEN [MST].Type = 'MSG' THEN 0 ELSE 1 END, [MST].[TrackReference]) [OMS] WHERE [RES].[ACTIONID] IS NOT NULL */ /* SELECT [RES].[id], CONVERT(VARCHAR(15),dbo.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] AS [Id], CONVERT(VARCHAR(15),dbo.KAAS_CP_FNConvertUTCDateToTargetTimeZone([DiaryDate], @LoginId), 23) AS [DATE], [ACTIONID], CONVERT(VARCHAR(15), dbo.KAAS_CP_FNConvertUTCDateToTargetTimeZone([DocumentDate], @LoginId), 23) AS [DocumentDate], [IMDocID], [IMClass], [DocumentClassDescription], [DocClass], [Document], [TRACKREFERENCE] AS [DocTrackReference], [TYPE] AS [DocType], [Attachments], [EMAIL], [EMAILFROM] AS [EMAILADDRESS], [EMAILTO] AS [ADDRESSTO], [EMAILSENT], [SUBJECT], [ShortText], [NAME], [FILEPATH] AS [FilePath], [PROCESSTYPE], [ACTIONTYPE], [ActionTypeDescription], [DOCUMENTSOURCE], [CurrentVersion], [IsInDocFolder], [IsShared], UniqueId from @res ORDER BY TrackReference DESC -- WHERE ID between (@PageNo * @PageSize) + 1 and ((@PageNo * @PageSize) + 1 + @PageSize) - 1 -- ORDER BY ID --OFFSET @PageSize * (@PageNo - 1) ROWS -- FETCH NEXT @PageSize ROWS ONLY OPTION (RECOMPILE); --OPTION (KEEPFIXED PLAN) /* IF Exists(SELECT TOP 1 TotalRecord FROM @res) BEGIN SELECT TOP 1 ISNULL(TotalRecord,0) FROM @res END ELSE BEGIN SELECT 0 as TotalRecord END */ SELECT @TOTALRECORDS AS TotalRecord /* SELECT top 1 count(1) Over() AS [TotalRecord] FROM [dbo].[diary] [DIA] INNER JOIN [dbo].[DiaryAttachments] [DAT] ON [DAT].[DiaryID] = [DIA].[ActionID] WHERE [DIA].[CASECODE] = @matter */ IF ISNULL(@DocFolderID, 0) <> 0 BEGIN SELECT [seq], [id] AS [FolderId], [parent], [foldername], [foldericon], [folderimage], [foldershared], [folderisours], [documentcount] AS [DocumentCount], 999 + TRY_CAST(seq AS INT) AS [Id] 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 [FolderId], CONVERT(INT, 0) AS [parent], CONVERT(VARCHAR(500), '') AS [foldername], CONVERT(INT, 0) AS [foldericon], CONVERT(VARBINARY(MAX), NULL) AS [folderimage], CONVERT(BIT, 0) AS [foldershared], CONVERT(BIT, 0) AS [folderisours], CONVERT(INT, 0) AS [DocumentCount] END END GO IF OBJECT_ID(N'KAAS_CP_GetMatterDetailsForClient',N'P')IS NOT NULL DROP PROCEDURE [dbo].[KAAS_CP_GetMatterDetailsForClient] GO CREATE PROCEDURE [dbo].[KAAS_CP_GetMatterDetailsForClient] (@LoginId BIGINT, @CaseCode VARCHAR(15) = NULL) AS /******************************************************************************************************* * * * Stored Procedure Name : [dbo].[KAAS_CP_GetMatterDetailsForClient] * * Description: To get matter details for client portal * * * * Modification History: * * 2021-06-09 Aakif Created * * 2021-06-11 Aakif Included option to serach with case code. User TimeZone conversion * * and label names for User1, User2, User3 and Your Ref * * 2021-06-22 Aakif Included column for matter comment and fixed null data issue for status * *******************************************************************************************************/ BEGIN SELECT *, CONVERT(NVARCHAR(15), dbo.KAAS_CP_FNConvertUTCDateToTargetTimeZone( dbo.KAAS_CP_GetLastOrNextActionDate(MatterCode, 1, 0), @LoginId), 23) AS [LastActionDate], CONVERT(NVARCHAR(15), dbo.KAAS_CP_FNConvertUTCDateToTargetTimeZone( dbo.KAAS_CP_GetLastOrNextActionDate(MatterCode, 0, 1), @LoginId), 23) AS [LastMilestoneDate], CONVERT(NVARCHAR(15), dbo.KAAS_CP_FNConvertUTCDateToTargetTimeZone( dbo.KAAS_CP_GetLastOrNextActionDate(MatterCode, 0, 0), @LoginId), 23) AS [NextActionDate], dbo.KAAS_CP_GetLastOrNextActionText(MatterCode, 1, 0) AS LastActionDescription, dbo.KAAS_CP_GetLastOrNextActionText(MatterCode, 0, 1) AS LastMilestoneDescription, dbo.KAAS_CP_GetLastOrNextActionText(MatterCode, 0, 0) AS NextActionDescription FROM ( SELECT CON.[Name] AS ClientName, CON.[Address] AS ClientAddress, MAT.[Code] AS MatterCode, MAT.[Description] AS MatterDescription, SM.[HandlerName] AS CaseHandler, RTRIM(CTL.[USERPROMPT1]) AS User1Label, MAT.[User1] AS User1, RTRIM(CTL.[USERPROMPT2]) AS User2Label, MAT.[User2] AS User2, RTRIM(CTL.[USERPROMPT3]) AS User3Label, MAT.[User3] AS User3, RTRIM(CTL.[YourRef]) AS YourRefLabel, MAT.[YourRef] AS YourRef, MAT.[Started] AS MatterStartDate, MAT.[CloseDate] AS MatterCloseDate, MAT.[Status] AS MatterStatusCode, RTRIM(SC.[DESCRIPTION]) AS MatterStatus, MAT.[OriginalDebt] AS MatterOriginalDebt, MAT.Comment AS MatterComments FROM [Contacts] CON JOIN [matters] MAT ON CON.Code = MAT.ClientCode JOIN [SearchMatters] SM ON SM.Code = MAT.Code LEFT JOIN [StatusCodes] SC ON SC.CODE = MAT.[Status] CROSS JOIN [control] CTL WHERE MAT.Code IN ( SELECT MatterCode FROM dbo.KAAS_CP_TFGetMatterCodeForClient(@LoginId)) AND (ISNULL(@CaseCode, '') = '' OR MAT.Code = @CaseCode) ) FIN END GO IF OBJECT_ID(N'KAAS_CP_GetMatterDocFolderTree', N'P') IS NOT NULL BEGIN DROP PROCEDURE [dbo].[KAAS_CP_GetMatterDocFolderTree] END GO CREATE PROCEDURE [dbo].[KAAS_CP_GetMatterDocFolderTree] ( @matter VARCHAR(20) ) AS /******************************************************************************************************** * Gets the folder structure for a Matter. Creates it if it doesn't exist, use the CasePlan folder * * structure as a template. Create it too, if it doesn't exist. * * * * Stored Procedure Name : [dbo].[KAAS_GetMatterDocFolderTree] * * Copied from : [dbo].[ky_NETSPGetMatterDocFolderTree] * * * * Modification History: * * 2019-04-23 Vinodhan K Created * * 2019-06-19 Vinodhan K Removed the condition to check whether UseDocFolders is set as True * * or False. For now it wil return the data even when it is set as False * * 2021-06-17 Aakif Created * *******************************************************************************************************/ BEGIN DECLARE @WKCODE CHAR(4) DECLARE @MatterCode VARCHAR(20) DECLARE @folderid INT SELECT TOP 1 @MatterCode = [SM].[CODE], @WKCODE = [TMP].[WKCODE] FROM [dbo].[SearchMatters] SM INNER JOIN [dbo].[CaseMaster] CSM ON [CSM].[CSCODE] = SM.[Code] LEFT OUTER JOIN [dbo].[Templates] [TMP] ON [TMP].[WKCODE] = [CSM].[CSWKTCODE] WHERE [CSM].[CSCODE] = @matter IF @MatterCode IS NULL BEGIN -- Dummy statement SELECT TOP 0 CONVERT(INT, 0) AS [id], CONVERT(INT, 0) AS [parent], CONVERT(VARCHAR(500), '') AS [foldername], CONVERT(INT, 0) AS [foldericon] RETURN END SELECT TOP 1 @folderid = MDF.[DocFolderID] FROM [dbo].[MatterDocFolders] MDF WHERE [MDF].[MatterCode] = @MatterCode IF @folderid IS NULL BEGIN DECLARE @DefaultRootFolderName VARCHAR(500) SELECT @DefaultRootFolderName = CONVERT(VARCHAR(500), [SET].[KeyValue]) FROM [dbo].[Settings] [SET] WHERE [SET].[KeyName] = 'DocFolderDefaultRootName' SET @DefaultRootFolderName = RTRIM(ISNULL(@DefaultRootFolderName, 'Documents')) IF @WKCODE IS NULL BEGIN EXEC @folderid = [dbo].[KAAS_SaveDocFolderTree] '' UPDATE [DF] SET [DF].[foldername] = @DefaultRootFolderName FROM [dbo].[DocFolders] [DF] WHERE [DF].[id] = @folderid END ELSE BEGIN SELECT TOP 1 @folderid = CDF.[DocFolderID] FROM [dbo].[CasePlanDocFolders] CDF WHERE [CDF].[WKCode] = @WKCODE IF @folderid IS NULL BEGIN EXEC @folderid = [dbo].[KAAS_SaveDocFolderTree] '' UPDATE [DF] SET [DF].[foldername] = @DefaultRootFolderName FROM [dbo].[DocFolders] [DF] WHERE [DF].[id] = @folderid INSERT INTO [dbo].[CasePlanDocFolders] ([WKCode], [DocFolderID]) SELECT @WKCODE, @folderid END DECLARE @DUP TABLE ([id] INT) INSERT INTO @DUP EXEC @folderid = [dbo].[KAAS_DuplicateDocFolderTree] @folderid END INSERT INTO [dbo].[MatterDocFolders] ([MatterCode], [DocFolderID]) SELECT @MatterCode, @folderid END SELECT [seq], [id] AS [FolderId], [parent], [foldername], [foldericon], [folderimage], [foldershared], [folderisours], [documentcount] AS [DocumentCount], 999 + TRY_CAST(seq AS INT) AS [Id] FROM [dbo].[KAAS_FN_GetDocFolders](@matter, @folderid, 1, 1) END GO IF OBJECT_ID(N'KAAS_CP_GetSettings',N'P')IS NOT NULL DROP PROCEDURE [dbo].[KAAS_CP_GetSettings] GO CREATE PROCEDURE [dbo].[KAAS_CP_GetSettings] AS /******************************************************************************************************* * * * Stored Procedure Name : [dbo].[KAAS_CP_GetSettings] * * Description: To get settings for client portal * * * * Modification History: * * 2021-05-27 Aakif Created * * 2021-05-31 Aakif Included column for two factor authentication * *******************************************************************************************************/ BEGIN SELECT [TenantLogo] AS [TenantLogo], [TenantLogoType] AS [TenantLogoType], [UPEKey] AS [UPEKey], ISNULL([IsTwoFactorEnabled], 0) AS [IsTwoFactorEnabled], ISNULL([IsLockOut_Attempt], 0) AS [IsLockOutAttempt], ISNULL([LockoutDurationInMins], 10) AS [LockoutLimitInMin], ISNULL([PasswordResetNotification], 0) AS [PasswordResetNotification] FROM [dbo].[ClientPortalSetting] END GO IF OBJECT_ID(N'KAAS_CP_GetUserLoginDetails',N'P')IS NOT NULL DROP PROCEDURE [dbo].[KAAS_CP_GetUserLoginDetails] GO CREATE PROCEDURE [dbo].[KAAS_CP_GetUserLoginDetails] (@EmailId VARCHAR(200)) AS /******************************************************************************************************* * * * Stored Procedure Name : [dbo].[KAAS_CP_GetUserLoginDetails] * * Description: To get login details for specified email * * * * Modification History: * * 2021-05-27 Aakif Created * * 2021-05-31 Aakif Included column for two factor authentication * *******************************************************************************************************/ BEGIN SELECT [ClientLoginId] AS [ClientLoginId], [Email] AS [Email], [PhoneNumber] AS [PhoneNumber], [Name] AS [Name], [ClientDefaulTimeZone] AS [ClientDefaulTimeZone], [AccessCode] AS [AccessCode], [AccessCodeStatus] AS [AccessCodeStatus], [PasswordHash] AS [PasswordHash], ISNULL([NoOfAttempt],0) AS [NoOfAttempt], [LastAccessedDateTime] AS [LastAccessedDateTime], [LockedOutEndDate] AS [LockedOutEndDate], [SecurityStamp] AS [SecurityStamp], ISNULL([IsTwoFactorEnabled],0) AS [IsTwoFactorEnabled], [IsActiveLogin] AS [IsActiveLogin], [IsFirstlogin] AS [IsFirstlogin], [IsDeleted] AS [IsDeleted], [IsAdmin] AS [IsAdmin], [IsEmailConfirmed] AS [IsEmailConfirmed], [IsPhoneNumberConfirmed] AS [IsPhoneNumberConfirmed], [EmailActivationToken] AS [EmailActivationToken], [ConcurrencyStamp] AS [ConcurrencyStamp], [PasswordResetOTP] AS [PasswordResetOTP], [PasswordResetOTPCreationTime] AS [PasswordResetOTPCreationTime], [PasswordResetOTPExpirationTime] AS [PasswordResetOTPExpirationTime], [OTPAccessToken] AS [OTPAccessToken], [OTPAccessTokenCreationTime] AS [OTPAccessTokenCreationTime], [OTPAccessTokenExpirationTime] AS [OTPAccessTokenExpirationTime], [PasswordResetOTPAttemptCount] AS [PasswordResetOTPAttemptCount], [PasswordResetLockOutEndDate] AS [PasswordResetLockOutEndDate], [IsPasswordResetOTPVerified] AS [IsPasswordResetOTPVerified], [IsUserVerifiedEmail] AS [IsUserVerifiedEmail], [VerifiedByAdminUserId] AS [VerifiedByAdminUserId], [EmailActivationTokenCreatedTime] AS [EmailActivationTokenCreatedTime], [EmailActivationTokenExpirationTime] AS [EmailActivationTokenExpirationTime], [IsAdminVerifiedUserLogin] AS [IsAdminVerifiedUserLogin], [SecretQuestion] AS [SecretQuestion], [Answer] AS [Answer] FROM [dbo].[ClientLogin] WHERE [Email] = @EmailId END GO IF OBJECT_ID(N'KAAS_CP_ListCaseContacts',N'P')IS NOT NULL DROP PROCEDURE [dbo].[KAAS_CP_ListCaseContacts] GO CREATE PROCEDURE [dbo].[KAAS_CP_ListCaseContacts] (@CaseCode VARCHAR(20), @ForEmail BIT = 1, @Number INT = 0, @Status VARCHAR(1) = '', @SearchText VARCHAR(4000) = NULL ) AS /************************************************************************************************************* * Stored Procedure Name: [KAAS_CP_ListCaseContacts] * Copied From : [ky_NETSPListCaseContacts] * * 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 *************************************************************************************************************/ BEGIN SET NOCOUNT ON DECLARE @FromWhom VARCHAR(10) DECLARE @FeeEarnerLabel VARCHAR(20) DECLARE @Partner VARCHAR(20) IF(@Status = 'T') BEGIN SET @FromWhom = RTRIM(ISNULL((SELECT [FMI].[TOWHOCODE] FROM [dbo].[FileManagerItems] [FMI] WHERE [FMI].[NUMBER] = @Number), '')) END ELSE BEGIN SET @FromWhom = RTRIM(ISNULL((SELECT [FMI].[FROMWHOCODE] FROM [dbo].[FileManagerItems] [FMI] WHERE [FMI].[NUMBER] = @Number), '')) END --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 [RES].[CODE] AS [Code], CASE WHEN [RES].[NAME] = '' THEN [RES].[COMPANY] ELSE [RES].[NAME] END AS [Name], [RES].[COMPANY] AS [Company], [RES].[EMAIL] AS [Email], [RES].[NAMECODE], [RES].[CASECODE] AS [CaseCode], [RES].[TYPE] AS [Type], [RES].[Occupation] AS [Occupation], CASE WHEN RTRIM(ISNULL([AST].[DESCRIPTION], '')) = '' THEN [RES].[TYPE] ELSE RTRIM(ISNULL([AST].[DESCRIPTION], '')) END AS [TypeDesc], LTRIM( CASE WHEN [RES].[NAME] = '' THEN '' WHEN [RES].[NAME] = [RES].[COMPANY] THEN '' ELSE [RES].[NAME] END + CASE WHEN [RES].[COMPANY] = '' THEN '' ELSE ' (' + [RES].[COMPANY] + ')' END) AS [NAMECOMP], [RES].[MobileNo] AS [MobileNo], [RES].[TITLE] AS [Title], [RES].[SourceTable], [RES].[SourceCode], [RES].[Work] FROM (SELECT CONVERT(VARCHAR(10), RTRIM(ISNULL([CAN].[CODE], ''))) AS [CODE], CONVERT(VARCHAR(300), RTRIM(ISNULL([CAN].[NAME], ''))) AS [NAME], CONVERT(VARCHAR(50), RTRIM(ISNULL([CAN].[COMPANY], ''))) AS [COMPANY], CONVERT(VARCHAR(100), RTRIM(ISNULL([CAN].[EMAIL], ''))) AS [Email], CONVERT(VARCHAR(100), RTRIM(ISNULL([CAN].[OCCUPATION], ''))) AS [Occupation], CONVERT(VARCHAR(10), RTRIM(ISNULL([CC].[NAMECODE], ''))) AS [NAMECODE], CONVERT(VARCHAR(20), RTRIM(ISNULL([CC].[CASECODE], ''))) AS [CASECODE], CONVERT(VARCHAR(20), RTRIM(ISNULL([CC].[CONTYPE], ''))) AS [TYPE], --PINO 2019-12-04, WAS [CAN].[TYPE] CONVERT(VARCHAR(100), RTRIM(ISNULL([CAN].[TITLE], ''))) AS [Title], CONVERT(VARCHAR(50), ISNULL([CAN].[MOBILENO], '')) AS [MobileNo], 'CaseAssociatesNames' AS [SourceTable], [CAN].[CODE] AS [SourceCode], CONVERT(VARCHAR(50), ISNULL([CAN].[PHONENO], '')) AS [Work] FROM [dbo].[CaseContacts] [CC] INNER JOIN [dbo].[CaseAssociatesNames] [CAN] ON [CAN].[CODE] = [CC].[NameCODE] AND ( @ForEmail = 0 OR RTRIM(ISNULL([CAN].[EMAIL], '')) <> '') WHERE [CC].[CASECODE] = @CaseCode AND ISNULL([CC].PUBLISH, '') = 'Y' AND ( ISNULL([CC].Retired,'N') <> 'Y' OR [CC].NAMECODE = CASE @Number WHEN 0 THEN '' ELSE @FromWhom END) --UNION --SELECT CONVERT(VARCHAR(10), RTRIM(ISNULL([CTC].[CLCODE], ''))) AS [Code], -- CONVERT(VARCHAR(300), RTRIM(ISNULL([CTC].[FIRSTNAME], '')) -- + ' ' -- + RTRIM(ISNULL([CTC].[SURNAME], ''))) AS [Name], -- CONVERT(VARCHAR(50), RTRIM(ISNULL([CTC].[CLNAMECON], ''))) AS [Company], -- CONVERT(VARCHAR(100), RTRIM(ISNULL([CTC].[EmailAddress], ''))) AS [Email], -- CONVERT(VARCHAR(100), RTRIM(ISNULL([CTC].[Occupation], ''))) AS [Occupation], -- CONVERT(VARCHAR(10), '') AS [NAMECODE], -- CONVERT(VARCHAR(20), @CaseCode) AS [CaseCode], -- CONVERT(VARCHAR(20), 'Client Contact') AS [Type], -- CONVERT(VARCHAR(100), RTRIM(ISNULL([CTC].[CLTITLE], ''))) AS [TITLE], -- CONVERT(VARCHAR(50), ISNULL([CTC].[Mobile], '')) AS [MobileNo], -- 'ClientContacts' AS [SourceTable], -- CONVERT(VARCHAR(50), [CTC].[id]) AS [SourceCode], -- CONVERT(VARCHAR(50),ISNULL([CTC].[WorkPhone],'')) AS [Work] -- FROM [dbo].[matters] [MAT] -- INNER JOIN [dbo].[ClientContacts] [CTC] -- ON [CTC].[CLCODE] = [MAT].[ClientCode] -- AND ( @ForEmail = 0 -- OR RTRIM(ISNULL([CTC].[EmailAddress], '')) <> '') -- WHERE [MAT].[CODE] = @CaseCode UNION SELECT CONVERT(VARCHAR(10), RTRIM(ISNULL([CAN].[CODE], ''))) AS [Code], CONVERT(VARCHAR(300), RTRIM(ISNULL([CAC].[NAME], ''))) AS [Name], CONVERT(VARCHAR(50), RTRIM(ISNULL([CAN].[COMPANY], ''))) AS [Company], CONVERT(VARCHAR(100), RTRIM(ISNULL([CAC].[EMAIL], ''))) AS [Email], CONVERT(VARCHAR(100), RTRIM(ISNULL([CAN].[OCCUPATION], ''))) AS [Occupation], CONVERT(VARCHAR(10), RTRIM(ISNULL([CC].[NAMECODE], ''))) AS [NAMECODE], CONVERT(VARCHAR(20), RTRIM(ISNULL([CC].[CASECODE], ''))) AS [CaseCode], CONVERT(VARCHAR(20), RTRIM(ISNULL([CC].[CONTYPE], ''))) AS [Type], CONVERT(VARCHAR(100), RTRIM(ISNULL([CAC].[JOBTITLE], ''))) AS [Title], CONVERT(VARCHAR(50), ISNULL([CAC].[MOBILE], '')) AS [MobileNo], 'CaseAssociatesContacts' AS [SourceTable], CONVERT(VARCHAR(50), [CAC].[KEYID]) AS [SourceCode], CONVERT(VARCHAR(50), ISNULL([CAC].[PHONE],'')) AS [Work] FROM [dbo].[CaseContacts] [CC] INNER JOIN [dbo].[CaseAssociatesNames] [CAN] INNER JOIN [dbo].[CaseAssociatesContacts] [CAC] ON [CAC].[NAMECODE] = [CAN].[CODE] AND ( @ForEmail = 0 OR RTRIM(ISNULL([CAC].[EMAIL], '')) <> '') ON [CAN].[CODE] = [CC].[NameCODE] WHERE [CC].[CASECODE] = @CaseCode AND ISNULL([CC].PUBLISH, '') = 'Y' --UNION --SELECT CONVERT(VARCHAR(10), RTRIM(ISNULL([CTC].[CODE], ''))) AS [Code], -- CONVERT(VARCHAR(300), RTRIM(ISNULL([CTC].[Name], ''))) AS [Name], -- CONVERT(VARCHAR(50), '') AS [Company], -- CONVERT(VARCHAR(100), RTRIM(ISNULL([CTC].[email], ''))) AS [Email], -- CONVERT(VARCHAR(100), RTRIM(ISNULL([CTC].[Occupation], ''))) AS [Occupation], -- CONVERT(VARCHAR(10), '') AS [NAMECODE], -- CONVERT(VARCHAR(20), @CaseCode) AS [CaseCode], -- CONVERT(VARCHAR(20), 'Client') AS [Type], -- CONVERT(VARCHAR(100), '') AS [Title], --Not sure there is a column representing this in Contacts or client -- CONVERT(VARCHAR(50), ISNULL([CLI].[CLMOBNO], '')) AS [MobileNo], -- 'client' AS [SourceTable], -- [CTC].[CODE] AS [SourceCode], -- CONVERT(VARCHAR(50), ISNULL([CTC].[Tel], '')) AS [Work] -- FROM [dbo].[matters] [MAT] -- INNER JOIN [dbo].[Contacts] [CTC] -- ON [CTC].[CODE] = [MAT].[ClientCode] -- AND ( @ForEmail = 0 -- OR RTRIM(ISNULL([CTC].[email], '')) <> '') -- LEFT OUTER JOIN [dbo].[client] AS [CLI] -- ON [CLI].[CLCODE] = [CTC].[CODE] -- WHERE [MAT].[CODE] = @CaseCode UNION SELECT CONVERT(VARCHAR(10), RTRIM(ISNULL([HAN].[CODE], ''))) AS [Code], CONVERT(VARCHAR(300), RTRIM(ISNULL([HAN].[Name], ''))) AS [Name], CONVERT(VARCHAR(50), '') AS [Company], CONVERT(VARCHAR(100), RTRIM(ISNULL([HAN].[email], ''))) AS [Email], CONVERT(VARCHAR(100), '') AS [Occupation], CONVERT(VARCHAR(10), '') AS [NAMECODE], CONVERT(VARCHAR(20), @CaseCode) AS [CaseCode], CONVERT(VARCHAR(20), (CASE WHEN ColumnName='FECode' THEN ISNULL(@FeeEarnerLabel, 'Fee Earner') WHEN ColumnName='Partner' THEN ISNULL(@Partner, 'Partner') ELSE ColumnName END)) AS [Type], CONVERT(VARCHAR(100), '') AS [Title], CONVERT(VARCHAR(50), ISNULL([HAN].[PhoneNumber], '')) AS [MobileNo], 'Handlers' AS [SourceTable], [HAN].[CODE] AS [SourceCode], CONVERT(VARCHAR(50), ISNULL([HAN].[Phoneno], '')) AS [Work] FROM (SELECT [UNPVT].[Handlers] AS [HandlerValue], [UNPVT].[Handlerslist] AS [ColumnName] FROM [dbo].[matters] [MAT] UNPIVOT ( [Handlers] FOR [Handlerslist] IN ([MAT].[FECode], [MAT].[Partner], [MAT].[Secretary])) [UNPVT] WHERE [UNPVT].[Code] = @CaseCode AND [UNPVT].[Handlers] <> '') [HANDLERLIST] INNER JOIN [Handlers] [HAN] ON [HAN].[CODE] = [HANDLERLIST].[HandlerValue] UNION SELECT CONVERT(VARCHAR(10), RTRIM(ISNULL([CTC].[CODE], ''))) AS [Code], CONVERT(VARCHAR(300), RTRIM(ISNULL([CTC].[Name], ''))) AS [Name], CONVERT(VARCHAR(50), '') AS [Company], CONVERT(VARCHAR(100), RTRIM(ISNULL([CTC].[SecondaryEmail], ''))) AS [Email], CONVERT(VARCHAR(100), RTRIM(ISNULL([CTC].[Occupation], ''))) AS [Occupation], CONVERT(VARCHAR(10), '') AS [NAMECODE], CONVERT(VARCHAR(20), @CaseCode) AS [CaseCode], CONVERT(VARCHAR(20), 'Client') AS [Type], CONVERT(VARCHAR(100), '') AS [Title], --Not sure there is a column representing this in Contacts or client CONVERT(VARCHAR(50), ISNULL([CLI].[CLMOBNO], '')) AS [MobileNo], 'client' AS [SourceTable], [CTC].[CODE] AS [SourceCode], CONVERT(VARCHAR(50), ISNULL([CTC].[Tel], '')) AS [Work] 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 IF OBJECT_ID(N'KAAS_CP_OtherCaseDetails',N'P')IS NOT NULL DROP PROCEDURE [dbo].[KAAS_CP_OtherCaseDetails] GO CREATE PROCEDURE [dbo].[KAAS_CP_OtherCaseDetails] (@CaseCode VARCHAR(20), @PageNumber INT = NULL, @PageSize INT = NULL, @SearchText VARCHAR(400) = NULL, @SortColumn VARCHAR(50) = NULL, @SortDirection VARCHAR(10) = NULL) AS /******************************************************************************************************* * To FETCH data for other case details main grid. * * * * Stored Procedure Name : [dbo].[KAAS_CP_OtherCaseDetails] * * Copied from : [dbo].[KAAS_OtherCaseDetailsFetchMainGrid] * * * * Modification History: * * 2019-04-23 Vinodhan K Created * * 2019-08-23 Dheepin Vijith K Modified - Default sorting changed to DESC to show the latest values * * on the top of the grid first * * 2021-06-22 Aakif List only published UDF data * *******************************************************************************************************/ BEGIN SET NOCOUNT ON DECLARE @StartRow INT; DECLARE @EndRow INT; SET @PageNumber = ISNULL(@PageNumber, 0) - 1; -- 0 BASED INDEX IF(@PageNumber > -1) BEGIN SET @StartRow = ((@PageNumber) * @PageSize) + 1; SET @EndRow = (@StartRow + @PageSize) - 1; END IF(RTRIM(ISNULL(@SearchText, '')) = '' OR RTRIM(@SearchText) = '') BEGIN SET @SearchText = '' END IF(ISNULL(@SortColumn, '') = '' OR RTRIM(LTRIM(@SortColumn)) = '') BEGIN SET @SortColumn = 'SeqNo' END IF(ISNULL(@SortDirection, '') = '' OR RTRIM(LTRIM(@SortDirection)) = '') BEGIN SET @SortDirection = 'DESC' END SELECT IDENTITY(INT, 1,1) AS [Row_Number], * INTO #OtherCaseDetailTable FROM (SELECT RTRIM(ISNULL(UDFCategory.UDFCategory,'')) AS GroupName, RTRIM( SystemUserDefinedFields.PROMPT) AS Detail, CONVERT(DATETIME, RTRIM(CaseUDFAnswers.DateValue), 23) AS DateValue, --ISNULL(CONVERT(VARCHAR,CaseUDFAnswers.DateValue,23),'') AS DateValue, CASE RTRIM(SystemUserDefinedFields.[VALIDATION]) WHEN 'L' THEN CONVERT(VARCHAR(MAX),RTRIM(ISNULL(CaseUDFAnswers.TEXT1,'')))+' - ' + (SELECT RTRIM(ISNULL(UDFLookups.[DESCRIPTION],'')) FROM UDFLookups WHERE SystemUserDefinedFields.FIELDNAME = UDFLookups.FIELDNAME AND CONVERT(VARCHAR(MAX),CaseUDFAnswers.TEXT1) = UDFLookups.CODE) WHEN 'R' THEN CONVERT(VARCHAR(200),RTRIM(ISNULL(CaseUDFAnswers.TEXT1,''))) WHEN 'T' THEN CONVERT(VARCHAR(200),RTRIM(ISNULL(CaseUDFAnswers.TEXT1,''))) ELSE CASE RTRIM(SystemUserDefinedFields.[TYPE]) WHEN 'Text' THEN (CASE WHEN CONVERT(VARCHAR(200),RTRIM(ISNULL(CaseUDFAnswers.TEXT1,'')))='' THEN '' ELSE CONVERT(VARCHAR(200),RTRIM(ISNULL(CaseUDFAnswers.TEXT1,''))) END) WHEN 'Numeric' THEN --(CASE -- WHEN SystemUserDefinedFields.DECMIAL=0 THEN CONVERT(VARCHAR(20),CONVERT(DECIMAL(18,0),CaseUDFAnswers.NumberValue) ) -- WHEN SystemUserDefinedFields.DECMIAL=1 THEN CONVERT(VARCHAR(20),CONVERT(DECIMAL(18,1),CaseUDFAnswers.NumberValue) ) -- WHEN SystemUserDefinedFields.DECMIAL=2 THEN CONVERT(VARCHAR(20),CONVERT(DECIMAL(18,2),CaseUDFAnswers.NumberValue) ) --END) CONVERT(VARCHAR(20),CAST(CaseUDFAnswers.NumberValue AS MONEY ),1) WHEN 'Date' THEN CASE WHEN CONVERT(VARCHAR(20), CaseUDFAnswers.DateValue, 23) = '1900-01-01' THEN '' --ELSE CONVERT(VARCHAR(20), CaseUDFAnswers.DateValue, 23) ELSE CONVERT(VARCHAR(20), CaseUDFAnswers.DateValue, 106) END ELSE CONVERT(VARCHAR(200),RTRIM(ISNULL(CaseUDFAnswers.TEXT1,''))) END END AS Value , RTRIM(ISNULL(CaseUDFAnswers.NumberValue,0)) AS NumberValue , RTRIM(ISNULL(CaseUDFAnswers.TEXT1,'')) AS [Text1] , RTRIM(SystemUserDefinedFields.TYPE) AS [Type] , RTRIM(ISNULL(CaseUDFAnswers.SEQNO,0)) AS [SeqNo] , RTRIM(SystemUserDefinedFields.FILEPREFIX) AS [FilePrefix] , RTRIM(SystemUserDefinedFields.FIELDNAME) AS [FieldName] , RTRIM(ISNULL(SystemUserDefinedFields.UDFCategory,0)) AS UDFCategory , CASE WHEN RTRIM(ISNULL(CaseUDFAnswers.ForCopy,0)) = '1' THEN 'True' ELSE 'False' END AS ForCopy , SystemUserDefinedFields.DECMIAL AS [Decimal] , SystemUserDefinedFields.LENGTH AS [Length] FROM CaseUDFAnswers INNER JOIN SystemUserDefinedFields ON CaseUDFAnswers.UDFFILE = SystemUserDefinedFields.FILEPREFIX AND CaseUDFAnswers.UDFNAME = SystemUserDefinedFields.FIELDNAME LEFT OUTER JOIN UDFCategory ON SystemUserDefinedFields.UDFCategory = UDFCategory.ID WHERE ( ISNULL(@SearchText, '') = '' OR RTRIM(LTRIM(@SearchText)) = '' OR ( [UDFCategory].[UDFCategory] LIKE '%' + @SearchText + '%' OR [SystemUserDefinedFields].[PROMPT] LIKE '%' + @SearchText + '%' OR [CaseUDFAnswers].[DateValue] LIKE '%' + @SearchText + '%' OR [SystemUserDefinedFields].[VALIDATION] LIKE '%' + @SearchText + '%' OR [CaseUDFAnswers].[TEXT1] LIKE '%' + @SearchText + '%' OR [SystemUserDefinedFields].[TYPE] LIKE '%' + @SearchText + '%' OR [CaseUDFAnswers].[NumberValue] LIKE '%' + @SearchText + '%' OR [CaseUDFAnswers].[SEQNO] LIKE '%' + @SearchText + '%' OR [SystemUserDefinedFields].[FILEPREFIX] LIKE '%' + @SearchText + '%' OR [SystemUserDefinedFields].[FIELDNAME] LIKE '%' + @SearchText + '%' OR [SystemUserDefinedFields].[UDFCategory] LIKE '%' + @SearchText + '%' OR [CaseUDFAnswers].[ForCopy] LIKE '%' + @SearchText + '%' OR [SystemUserDefinedFields].[DECMIAL] LIKE '%' + @SearchText + '%' OR [SystemUserDefinedFields].[LENGTH] LIKE '%' + @SearchText + '%' ) ) AND SystemUserDefinedFields.PUBLISH = 1 AND (RTRIM(CaseUDFAnswers.CASECODE) = RTRIM(@CaseCode))) AS FoundTable ORDER BY CASE WHEN @SortDirection = 'ASC' THEN CASE @SortColumn WHEN 'SeqNo' THEN CAST([SeqNo] AS INT) END END, CASE WHEN @SortDirection = 'DESC' THEN CASE @SortColumn WHEN 'SeqNo' THEN CAST([SeqNo] AS INT) END END DESC, CASE WHEN @SortDirection = 'ASC' THEN CASE @SortColumn WHEN 'GroupName' THEN [GroupName] END END, CASE WHEN @SortDirection = 'DESC' THEN CASE @SortColumn WHEN 'GroupName' THEN [GroupName] END END DESC, CASE WHEN @SortDirection = 'ASC' THEN CASE @SortColumn WHEN 'Detail' THEN [Detail] END END, CASE WHEN @SortDirection = 'DESC' THEN CASE @SortColumn WHEN 'Detail' THEN [Detail] END END DESC, CASE WHEN @SortDirection = 'ASC' THEN CASE @SortColumn WHEN 'DateValue' THEN [DateValue] END END, CASE WHEN @SortDirection = 'DESC' THEN CASE @SortColumn WHEN 'DateValue' THEN [DateValue] END END DESC, CASE WHEN @SortDirection = 'ASC' THEN CASE @SortColumn WHEN 'Value' THEN [Value] END END, CASE WHEN @SortDirection = 'DESC' THEN CASE @SortColumn WHEN 'Value' THEN [Value] END END DESC, CASE WHEN @SortDirection = 'ASC' THEN CASE @SortColumn WHEN 'NumberValue' THEN [NumberValue] END END, CASE WHEN @SortDirection = 'DESC' THEN CASE @SortColumn WHEN 'NumberValue' THEN [NumberValue] END END DESC, CASE WHEN @SortDirection = 'ASC' THEN CASE @SortColumn WHEN 'Text1' THEN [Text1] END END, CASE WHEN @SortDirection = 'DESC' THEN CASE @SortColumn WHEN 'Text1' THEN [Text1] END END DESC, CASE WHEN @SortDirection = 'ASC' THEN CASE @SortColumn WHEN 'Type' THEN [Type] END END, CASE WHEN @SortDirection = 'DESC' THEN CASE @SortColumn WHEN 'Type' THEN [Type] END END DESC, CASE WHEN @SortDirection = 'ASC' THEN CASE @SortColumn WHEN 'FilePrefix' THEN [FilePrefix] END END, CASE WHEN @SortDirection = 'DESC' THEN CASE @SortColumn WHEN 'FilePrefix' THEN [FilePrefix] END END DESC, CASE WHEN @SortDirection = 'ASC' THEN CASE @SortColumn WHEN 'FieldName' THEN [FieldName] END END, CASE WHEN @SortDirection = 'DESC' THEN CASE @SortColumn WHEN 'FieldName' THEN [FieldName] END END DESC, CASE WHEN @SortDirection = 'ASC' THEN CASE @SortColumn WHEN 'UDFCategory' THEN [UDFCategory] END END, CASE WHEN @SortDirection = 'DESC' THEN CASE @SortColumn WHEN 'UDFCategory' THEN [UDFCategory] END END DESC, CASE WHEN @SortDirection = 'ASC' THEN CASE @SortColumn WHEN 'ForCopy' THEN [ForCopy] END END, CASE WHEN @SortDirection = 'DESC' THEN CASE @SortColumn WHEN 'ForCopy' THEN [ForCopy] END END DESC, CASE WHEN @SortDirection = 'ASC' THEN CASE @SortColumn WHEN 'Decimal' THEN [Decimal] END END, CASE WHEN @SortDirection = 'DESC' THEN CASE @SortColumn WHEN 'Decimal' THEN [Decimal] END END DESC, CASE WHEN @SortDirection = 'ASC' THEN CASE @SortColumn WHEN 'Length' THEN [Length] END END, CASE WHEN @SortDirection = 'DESC' THEN CASE @SortColumn WHEN 'Length' THEN [Length] END END DESC SELECT * FROM #OtherCaseDetailTable WHERE ( @StartRow IS NULL OR ( [Row_Number] BETWEEN @StartRow AND @EndRow ) ) SELECT (COUNT([Row_Number])) AS TotalRecords FROM #OtherCaseDetailTable SET NOCOUNT OFF END GO IF OBJECT_ID(N'KAAS_CP_RegisterNewUser',N'P')IS NOT NULL DROP PROCEDURE [dbo].[KAAS_CP_RegisterNewUser] GO CREATE PROCEDURE [dbo].[KAAS_CP_RegisterNewUser] (@EmailId VARCHAR(200), @IsTwoFactorEnabled BIT = 0, @EmailActivationToken NVARCHAR(252) = NULL, @TokenCreationTime DATETIME = NULL, @TokenExpirationTime DATETIME = NULL) AS /******************************************************************************************************* * * * Stored Procedure Name : [dbo].[KAAS_CP_RegisterNewUser] * * Description: To register new user in Client Portal * * * * Modification History: * * 2021-06-07 Aakif Created * *******************************************************************************************************/ BEGIN -- Update OTP with its expiry time INSERT INTO [dbo].[ClientLogin] ( [Email], [IsActiveLogin], [IsTwoFactorEnabled], [EmailActivationToken], [EmailActivationTokenCreatedTime], [EmailActivationTokenExpirationTime] ) VALUES ( @EmailId, 0, @IsTwoFactorEnabled, @EmailActivationToken, @TokenCreationTime, @TokenExpirationTime ) END GO IF OBJECT_ID(N'KAAS_CP_UpdateAccessCodeForUser',N'P')IS NOT NULL DROP PROCEDURE [dbo].[KAAS_CP_UpdateAccessCodeForUser] GO CREATE PROCEDURE [dbo].[KAAS_CP_UpdateAccessCodeForUser] (@EmailId VARCHAR(200), @AccessCode VARCHAR(50) = NULL, @AccessCodeStatus VARCHAR(200) = NULL, @OffSetForOtpExpiry TINYINT = 10) AS /******************************************************************************************************* * * * Stored Procedure Name : [dbo].[KAAS_CP_UpdateAccessCodeForUser] * * Description: To store Access Code and OTP Expirty time for user * * * * Modification History: * * 2021-05-31 Aakif Created * *******************************************************************************************************/ BEGIN DECLARE @CurrentDateTime DATETIME = GETUTCDATE(); -- Update OTP with its expiry time UPDATE [dbo].[ClientLogin] SET [AccessCode] = @AccessCode, [AccessCodeStatus] = @AccessCodeStatus, [OTPAccessTokenCreationTime] = @CurrentDateTime, [OTPAccessTokenExpirationTime] = DATEADD(MINUTE, @OffSetForOtpExpiry, @CurrentDateTime) WHERE [Email] = @EmailId END GO IF OBJECT_ID(N'KAAS_CP_UpdateClientAccess',N'P')IS NOT NULL DROP PROCEDURE [dbo].[KAAS_CP_UpdateClientAccess] GO CREATE PROCEDURE [dbo].[KAAS_CP_UpdateClientAccess] (@EmailId VARCHAR(200), @HandlerCode VARCHAR(10), @ContactCode VARCHAR(25), @MatterCode VARCHAR(25) = NULL) AS /******************************************************************************************************* * * * Stored Procedure Name : [dbo].[KAAS_CP_UpdateClientAccess] * * Description: To add accesss to clients for a code * * * * Modification History: * * 2021-06-25 Aakif Created * *******************************************************************************************************/ BEGIN -- CLIENT ACCESS LOGIN TO CONTACT CODES DECLARE @ClientId BIGINT = 0; SELECT @ClientId = ClientLoginId FROM [ClientLogin] WHERE Email = @EmailId -- INSERT TO CLIENT LOGIN ACCESS INSERT INTO [ClientLoginAccess] ( ClientLoginId, ContactCode, MatterCode, HasAccessToAllMatter, PublishedHandlerCode, PublishedDateTime ) VALUES ( @ClientId, @ContactCode, @MatterCode, 'Y', @HandlerCode, GETUTCDATE() ) END GO IF OBJECT_ID(N'KAAS_CP_UpdateIncorrectLoginAttempt',N'P')IS NOT NULL DROP PROCEDURE [dbo].[KAAS_CP_UpdateIncorrectLoginAttempt] GO CREATE PROCEDURE [dbo].[KAAS_CP_UpdateIncorrectLoginAttempt] (@EmailId VARCHAR(200), @Result INT = 0 OUTPUT) AS /******************************************************************************************************* * * * Stored Procedure Name : [dbo].[KAAS_CP_UpdateIncorrectLoginAttempt] * * Description: To update lockout date and number of attempts for user * * * * Modification History: * * 2021-06-01 Aakif Created * *******************************************************************************************************/ BEGIN DECLARE @CurrentLoginAttempt TINYINT; DECLARE @MaxLockoutAttemp INT, @LockoutTimeoutLimit INT; --GET current incorrect login attempt count SELECT @CurrentLoginAttempt = ISNULL(NoOfAttempt,0) FROM [dbo].[ClientLogin] -- GET Max attempt and lockout duration from setting SELECT @MaxLockoutAttemp = ISNULL(IsLockOut_Attempt, 3), @LockoutTimeoutLimit = ISNULL(LockoutDurationInMins, 10) FROM [dbo].[ClientPortalSetting] -- Step 1: Update current attempt at login UPDATE [dbo].[ClientLogin] SET [NoOfAttempt] = (@CurrentLoginAttempt + 1) WHERE [Email] = @EmailId -- Step 2: Check if current attempt exceeds limit and update lockedout date IF(@MaxLockoutAttemp <= (@CurrentLoginAttempt + 1 )) BEGIN UPDATE [dbo].[ClientLogin] SET [LockedOutEndDate] = DATEADD(MINUTE, @LockoutTimeoutLimit, GETUTCDATE()) WHERE [Email] = @EmailId SET @Result = @LockoutTimeoutLimit END END GO IF OBJECT_ID(N'KAAS_CP_UpdateLastLoginData',N'P')IS NOT NULL DROP PROCEDURE [dbo].[KAAS_CP_UpdateLastLoginData] GO CREATE PROCEDURE [dbo].[KAAS_CP_UpdateLastLoginData] (@EmailId VARCHAR(200), @LoginId BIGINT, @DeviceInfo NVARCHAR(20) = NULL, @BrowserInfo NVARCHAR(20) = NULL, @IPAddress NVARCHAR(30) = NULL) AS /******************************************************************************************************* * * * Stored Procedure Name : [dbo].[KAAS_CP_UpdateLastAccessDate] * * Description: To update last accessed datetime for user * * * * Modification History: * * 2021-05-27 Aakif Created * * 2021-06-31 Aakif Reset LockedoutEndDate after successful login * *******************************************************************************************************/ BEGIN -- Step 1: Update last login access date UPDATE [dbo].[ClientLogin] SET [LastAccessedDateTime] = GETUTCDATE(), [NoOfAttempt] = 0, [LockedOutEndDate] = NULL, [IsFirstlogin] = 0, [IsActiveLogin] = 1 WHERE [Email] = @EmailId -- Step 2: Add user login history INSERT INTO [dbo].[ClientPortalLogonInfo] ([ClientLoginId] ,[LastAccessedDateTime] ,[DeviceInfo] ,[Browser] ,[IPAddress]) VALUES (@LoginId ,GETUTCDATE() ,@DeviceInfo ,@BrowserInfo ,@IPAddress) END GO IF OBJECT_ID(N'KAAS_CP_UpdatePasswordHash',N'P')IS NOT NULL DROP PROCEDURE [dbo].[KAAS_CP_UpdatePasswordHash] GO CREATE PROCEDURE [dbo].[KAAS_CP_UpdatePasswordHash] (@LoginId BIGINT, @NewPasswordHash NVARCHAR(512)) AS /******************************************************************************************************* * * * Stored Procedure Name : [dbo].[KAAS_CP_UpdatePasswordHash] * * Description: To update password hash after the reset is successful * * * * Modification History: * * 2021-06-02 Aakif Created * *******************************************************************************************************/ BEGIN -- Update password hash after password is reset UPDATE [dbo].[ClientLogin] SET [PasswordHash] = @NewPasswordHash, [PasswordResetLockOutEndDate] = NULL WHERE [ClientLoginId] = @LoginId END GO IF OBJECT_ID(N'KAAS_CP_UpdateResetPasswordCode',N'P')IS NOT NULL DROP PROCEDURE [dbo].[KAAS_CP_UpdateResetPasswordCode] GO CREATE PROCEDURE [dbo].[KAAS_CP_UpdateResetPasswordCode] (@LoginId BIGINT, @ResetPasswordCode VARCHAR(10), @CodeCreationTime DATETIME = NULL, @CodeExpirationTime DATETIME = NULL) AS /******************************************************************************************************* * * * Stored Procedure Name : [dbo].[KAAS_CP_UpdateResetPasswordCode] * * Description: To store Reset password OTP Expirty time for user * * * * Modification History: * * 2021-06-02 Aakif Created * *******************************************************************************************************/ BEGIN -- Update OTP with its expiry time UPDATE [dbo].[ClientLogin] SET [IsPasswordResetOTPVerified] = 0, [PasswordResetOtp] = @ResetPasswordCode, [PasswordResetOtpcreationTime] = @CodeCreationTime, [PasswordResetOtpexpirationTime] = @CodeExpirationTime WHERE [ClientLoginId] = @LoginId END GO IF OBJECT_ID(N'KAAS_CP_ValidateResetPassword',N'P')IS NOT NULL DROP PROCEDURE [dbo].[KAAS_CP_ValidateResetPassword] GO CREATE PROCEDURE [dbo].[KAAS_CP_ValidateResetPassword] (@LoginId BIGINT, @IsOtpValid BIT, @IsPasswordReset BIT = 0) AS /******************************************************************************************************* * * * Stored Procedure Name : [dbo].[KAAS_CP_ValidateResetPassword] * * Description: To validate Reset password OTP and lockout user if invalid credentails provided * * * * Modification History: * * 2021-06-03 Aakif Created * *******************************************************************************************************/ BEGIN DECLARE @CurrentAttempt TINYINT, @LockoutLimit TINYINT, @LockoutDuration INT; --HANDLE FOR VALID RESET PWD OTP IF(@IsOtpValid = 1) BEGIN UPDATE [dbo].[ClientLogin] SET [IsPasswordResetOTPVerified] = 1 WHERE [ClientLoginId] = @LoginId END ELSE BEGIN --GET CURRENT ATTEMPT COUNT TO DECIDE WHETHER TO LOCKOUT USER SELECT @CurrentAttempt = ISNULL(PasswordResetOTPAttemptCount, 0) FROM [dbo].[ClientLogin] -- SET LOCKOUT TENANT SETTINGS SELECT @LockoutLimit = ISNULL(IsLockOut_Attempt, 3), @LockoutDuration = ISNULL(LockoutDurationInMins, 10) FROM [dbo].[ClientPortalSetting] -- LOCKOUT USER FOR MAX LIMIT REACHED IF(@CurrentAttempt >= @LockoutLimit) BEGIN UPDATE [dbo].[ClientLogin] SET [PasswordResetLockOutEndDate] = DATEADD(MINUTE, @LockoutDuration, GETUTCDATE()) WHERE [ClientLoginId] = @LoginId END --INCREMENT CURRENT ATTEMPT COUNT UPDATE [dbo].[ClientLogin] SET [PasswordResetOTPAttemptCount] = (@CurrentAttempt + 1) WHERE [ClientLoginId] = @LoginId END --RESET ALL DATA AFTER PASSWORD IS RESET IF(@IsPasswordReset = 1) BEGIN UPDATE [dbo].[ClientLogin] SET [PasswordResetOTPAttemptCount] = 0, [IsPasswordResetOTPVerified] = 0, [PasswordResetLockOutEndDate] = NULL, [PasswordResetOTP] = NULL, [PasswordResetOTPCreationTime] = NULL, [PasswordResetOTPExpirationTime] = NULL WHERE [ClientLoginId] = @LoginId END END GO