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), @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 * * 2021-07-09 Aakif #12608 - Name and email has to be removed while account activation * *******************************************************************************************************/ BEGIN -- Update user details to activate account UPDATE [dbo].[ClientLogin] SET [PasswordHash] = @PasswordHash, [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_GetAccountSummaryDetails',N'P')IS NOT NULL DROP PROCEDURE [dbo].[KAAS_CP_GetAccountSummaryDetails] GO CREATE PROCEDURE [dbo].[KAAS_CP_GetAccountSummaryDetails] (@MatterCode VARCHAR(20)) AS /****************************************************************************************************************** * * * Used to fetch account summary details * * * * Stored Procedure Name: [dbo].[KAAS_CP_GetAccountSummaryDetails] * * Copied from : [dbo].[KAAS_GetMatterLedgerDetail] * * [dbo].[ky_NETGetMatterLedger] * * [dbo].[ky_NETGetMatterLedgerSAM4] * * * * Modification History: * * 2021-07-13 Vinodhkumar Created * ******************************************************************************************************************/ BEGIN DECLARE @IsSAM4 bit DECLARE @Query NVARCHAR(200) SET @IsSAM4 = [dbo].[ISSAM4]() EXECUTE KAAS_GetMatterLedger @MatterCode /*Account Summary*/ DECLARE @NCOMMAND nvarchar(MAX) DECLARE @DraftBillValue DECIMAL(18, 2) IF @IsSAM4 = 0 BEGIN SET @NCOMMAND = N' SELECT @DraftBillValue = ISNULL(SUM(ISNULL(BD.[NET], 0)), 0) + ISNULL(SUM(ISNULL(BD.[VATVALUE], 0)), 0) FROM [dbo].[BillHeader] HED INNER JOIN [dbo].[BillDetails] BD ON BD.[DRAFTNO] = HED.[DRAFTNO] WHERE HED.[MATTER] = @MatterCode AND HED.[BILLNO] = 0' END ELSE BEGIN SET @NCOMMAND = N' SELECT @DraftBillValue = ISNULL(SUM(ISNULL(BD.[NetValue], 0)), 0) + ISNULL(SUM(ISNULL(BD.[VatValue], 0)), 0) FROM [dbo].[BillHead] HED INNER JOIN [dbo].[BillDetail] BD ON BD.[BillID] = HED.[BillingID] WHERE HED.[MatterCode] = @MatterCode AND HED.[BillStatus] <> ''Approved'' AND ISNULL(HED.[ProForma], '''') = ''Y''' END EXECUTE sp_executesql @NCOMMAND, N'@MatterCode VARCHAR(20),@DraftBillValue DECIMAL(18, 2) OUTPUT', @MatterCode = @MatterCode, @DraftBillValue=@DraftBillValue OUTPUT DECLARE @Section68 VARCHAR(10) DECLARE @IsApproved VARCHAR(10) DECLARE @TimeBal INT DECLARE @ChargeBal DECIMAL(19, 2) DECLARE @TotalCharge Decimal(19, 2) DECLARE @TotalTime INT --Matter values that are re-used elsewhere in this procedure. SELECT @Section68 = ISNULL(MAT.[Section68],''), @IsApproved = ISNULL(CON.[Approved],'') FROM [dbo].[matters] MAT LEFT OUTER JOIN [dbo].[Contacts] CON ON CON.[Code] = MAT.[ClientCode] WHERE MAT.[Code] = @MatterCode DECLARE @LDIADATE DATETIME DECLARE @LDIAACTIONTYPE VARCHAR(1) DECLARE @LDIATEXT1 VARCHAR(MAX) DECLARE @LDIAFNCODE VARCHAR(10) SELECT TOP 1 @LDIADATE = LDIA.[DATE], @LDIAACTIONTYPE = LDIA.[ACTIONTYPE], @LDIATEXT1 = LDIA.[TEXT1], @LDIAFNCODE = LDIA.[FNCODE] FROM [dbo].[diary] LDIA WHERE LDIA.[CASECODE] = @MatterCode ORDER BY LDIA.[DATE] DESC DECLARE @MDIADATE DATETIME DECLARE @MDIAACTIONTYPE VARCHAR(1) DECLARE @MDIATEXT1 VARCHAR(MAX) DECLARE @MDIAFNCODE VARCHAR(10) SELECT TOP 1 @MDIADATE= MDIA.[DATE], @MDIAACTIONTYPE = MDIA.[ACTIONTYPE], @MDIATEXT1 = MDIA.[TEXT1], @MDIAFNCODE = MDIA.[FNCODE] FROM [dbo].[diary] MDIA WHERE MDIA.[CASECODE] = @MatterCode AND MDIA.[HIGHLIGHTED] = 'Y' ORDER BY MDIA.[DATE] DESC DECLARE @NDIADATE DATETIME DECLARE @NDIAACTIONTYPE VARCHAR(1) DECLARE @NDIATEXT1 VARCHAR(MAX) DECLARE @NDIAFNCODE VARCHAR(10) SELECT TOP 1 @NDIADATE = NDIA.[DATE], @NDIAACTIONTYPE = NDIA.[ACTIONTYPE], @NDIATEXT1 = NDIA.[TEXT1], @NDIAFNCODE = NDIA.[FNCODE] FROM dbo.[diary] NDIA WHERE NDIA.[CASECODE] = @MatterCode AND NDIA.[STATUS] = 0 ORDER BY NDIA.[DATE] ASC DECLARE @TIEDATE DATETIME --Pino 2015-05-27 Start --SELECT @TIEDATE = (MAX(TIE.[DATE])) -- FROM [dbo].[TimeEntry] TIE -- WHERE TIE.[Matter] = @MatterCode IF [dbo].[ISSAM4]() = 0 BEGIN SET @NCOMMAND = N' SELECT @TIEDATE = MAX(CASE WHEN DLG.[DATE] IS NULL THEN CONVERT(DATETIME, NULL) WHEN DLG.[DATE] > GETDATE() THEN CONVERT(DATETIME, NULL) ELSE DLG.[DATE] END) FROM [dbo].[DebtorsLedger] DLG WHERE DLG.[MATTER] = @MatterCode' END ELSE BEGIN SET @NCOMMAND = N' SELECT @TIEDATE = MAX(CASE WHEN TRN.[TransDate] IS NULL THEN CONVERT(DATETIME, NULL) WHEN TRN.[TransDate] > GETDATE() THEN CONVERT(DATETIME, NULL) ELSE TRN.[TransDate] END) FROM [dbo].[Transactions] TRN WHERE TRN.[Posted] = ''Y'' AND TRN.[MatterCode] = @MatterCode AND TRN.[DebtorsValue] <> 0 AND TRN.[CorrectionInd] = ''L'' AND ( ( TRN.[XnType] = ''I'' AND TRN.[RecType] = ''H'') OR ( TRN.[XnType] = ''J'' AND TRN.[RecType] = ''D''))' END EXECUTE sp_executesql @NCOMMAND, N'@MatterCode VARCHAR(20), @TIEDATE DATETIME OUTPUT', @MatterCode = @MatterCode, @TIEDATE = @TIEDATE OUTPUT --Pino 2015-05-27 End DECLARE @LastTimeEntryDate DATETIME DECLARE @LastTimeEntryTime INT DECLARE @LastTimeEntryRate DECIMAL(10, 2) DECLARE @LastTimeEntryCharge DECIMAL(10, 2) DECLARE @LastTimeEntryTask VARCHAR(6) DECLARE @LastTimeEntryComment VARCHAR(1000) DECLARE @LastTimeEntryFE VARCHAR(10) DECLARE @TimeEntryTotalCharge DECIMAL(19, 2) DECLARE @TimeEntryTotalTime INT SELECT TOP (1) @LastTimeEntryDate = TIE.[DATE], @LastTimeEntryTime = TIE.[Time], @LastTimeEntryRate = TIE.[Rate], @LastTimeEntryCharge = TIE.[Charge], @LastTimeEntryTask = TIE.[Task], @LastTimeEntryComment = TIE.[Comment], @LastTimeEntryFE = TIE.[FeeEarn] FROM [dbo].[TimeEntry] TIE WHERE TIE.[Matter] = @MatterCode AND ( (TIE.[TimeOrCharge] = 'T') OR (TIE.[TimeOrCharge] = 'C')) --**Changed fetch condition JIRA 1081 - arun ORDER BY TIE.[Date] DESC, TIE.[Time] DESC SET @LastTimeEntryRate = ISNULL(@LastTimeEntryRate, 0) SET @LastTimeEntryCharge = ISNULL(@LastTimeEntryCharge, 0) -- Please see also: ky_NETFNMatterClosable. If you change any of these calculations, -- ensure the ones in there are changed as well. -- ky_NETPMatterClosable. If you change any of these calculations, -- ensure the ones in there are changed as well. -- ky_NETTLFetchTimeEntry. If you change any of these calculations, -- ensure the ones in there are changed as well. SELECT @TimeEntryTotalCharge = ISNULL(SUM(ISNULL(TIE.[Charge], 0)), 0), @TimeEntryTotalTime = ISNULL(SUM(ISNULL(TIE.[TIME], 0)), 0), @TotalCharge = ISNULL(SUM(CASE WHEN ISNULL(TIE.[Rec_Irr], '') = 'N' THEN 0 ELSE ISNULL(TIE.[Charge], 0) END), 0), @ChargeBal = ISNULL(SUM(CASE WHEN ISNULL(TIE.[Rec_Irr], '') = 'N' THEN 0 WHEN @IsSAM4 = 1 AND ISNULL(TIE.[InvoiceNo], 0) <> 0 THEN 0 WHEN @IsSAM4 = 1 THEN ISNULL(TIE.[Charge], 0) ELSE ISNULL(TIE.[Charge], 0) - ISNULL(TIE.[BilledAmount], 0) END), 0), @TimeBal = ISNULL(SUM(CASE WHEN ISNULL(TIE.[Rec_Irr], '') = 'N' THEN 0 WHEN @IsSAM4 = 1 AND ISNULL(TIE.[InvoiceNo], 0) <> 0 THEN 0 WHEN @IsSAM4 = 0 THEN CASE WHEN TIE.[Charge] = 0 THEN 0 ELSE CONVERT(INT, FLOOR(CONVERT(DECIMAL(18, 2), TIE.[TIME]) * ( (TIE.[Charge] - TIE.[BilledAmount]) / TIE.[Charge]))) END ELSE ISNULL(TIE.[Time], 0) END), 0), @TotalTime = ISNULL(SUM(CASE WHEN ISNULL(TIE.[Rec_Irr], '') = 'N' THEN 0 ELSE ISNULL(TIE.[Time], 0) END), 0) FROM [dbo].[TimeEntry] TIE WHERE TIE.[Matter] = @MatterCode AND ( (TIE.[TimeOrCharge] = 'T') OR (TIE.[TimeOrCharge] = 'C')) --**Changed fetch condition JIRA 1081 - arun DECLARE @DebtBal DECIMAL(19, 2) DECLARE @OutlayBal DECIMAL(19, 2) DECLARE @DebtBalDate DATETIME DECLARE @OutlayBalDate DATETIME DECLARE @ClientCurrent DECIMAL(19, 2) DECLARE @ClientDeposit DECIMAL(19, 2) -- Please see also: ky_NETFNMatterClosable. If you change any of these calculations, -- ensure the ones in there are changed as well. -- ky_NETTLFetchTimeEntry. If you change any of these calculations, -- ensure the ones in there are changed as well. -- To ensure SAM3 will retrieve these from [dbo].[matters] SET @DebtBal = 0 SET @OutlayBal = 0 SET @ClientCurrent = 0 SET @ClientDeposit = 0 IF @IsSAM4 = 1 BEGIN SET @NCOMMAND = N' SELECT @DebtBal = CONVERT(DECIMAL(19, 2), ISNULL(SUM(ISNULL(TRN.[DebtorsValue], 0)), 0)), @DebtBalDate = MAX(CASE WHEN ISNULL(TRN.[DebtorsValue], 0) = 0 THEN NULL ELSE TRN.[TransDate] END), @OutlayBal = CONVERT(DECIMAL(19, 2), ISNULL(SUM(ISNULL(TRN.[OutlayValue], 0)), 0)), @OutlayBalDate = MAX(CASE WHEN ISNULL(TRN.[OutlayValue], 0) = 0 THEN NULL ELSE TRN.[TransDate] END), @ClientCurrent = CONVERT(DECIMAL(19, 2), ISNULL(SUM(ISNULL(TRN.[ClientCurrent], 0)), 0)), @ClientDeposit = CONVERT(DECIMAL(19, 2), ISNULL(SUM(ISNULL(TRN.[ClientDeposit], 0)), 0)) FROM [dbo].[Transactions] TRN WHERE TRN.[MatterCode] = @MatterCode AND TRN.[Posted] = ''Y'' AND TRN.[RecType] <> ''V'' AND TRN.[CorrectionInd] = ''L''' EXECUTE sp_executesql @NCOMMAND, N'@MatterCode VARCHAR(20), @DebtBal DECIMAL(19, 2) OUTPUT, @DebtBalDate DATETIME OUTPUT, @OutlayBal DECIMAL(19, 2) OUTPUT, @OutlayBalDate DATETIME OUTPUT, @ClientCurrent DECIMAL(19, 2) OUTPUT, @ClientDeposit DECIMAL(19, 2) OUTPUT', @MatterCode = @MatterCode, @DebtBal = @DebtBal OUTPUT, @DebtBalDate = @DebtBalDate OUTPUT, @OutlayBal = @OutlayBal OUTPUT, @OutlayBalDate = @OutlayBalDate OUTPUT, @ClientCurrent = @ClientCurrent OUTPUT, @ClientDeposit = @ClientDeposit OUTPUT END --Result Set 0 --Matter Data SELECT RTRIM(ISNULL(MAT.[Code], '')) AS [Code], RTRIM(ISNULL(MAT.[Description], '')) AS [Description], RTRIM(ISNULL(MAT.[ClientCode], '')) AS [ClientCode], RTRIM(ISNULL(CLT.[CLNAME], '')) AS [CLName], RTRIM(ISNULL(CLT.[CLADDR], '')) AS [CLAddr], RTRIM(ISNULL(MAT.[FECode], '')) AS [FECode], RTRIM(ISNULL(HAN.[NAME], '')) AS [HandlerName], RTRIM(ISNULL(MAT.[Dept], '')) AS [Dept], RTRIM(ISNULL(DPT.[DESC], '')) AS [Desc], RTRIM(MAT.WType) AS [WType], RTRIM(ISNULL(WKT.[DESC], '')) AS [WorkType], MAT.[Started] AS [Started], MAT.[EstFee] AS [EstFee], MAT.[ExpBillD] AS [ExpBillD], CONVERT(DECIMAL(19, 2), CASE WHEN @DebtBal <> 0 THEN @DebtBal ELSE ISNULL(MAT.[DebtBal], 0) END) AS [DebtBal], CONVERT(DECIMAL(19, 2), CASE WHEN @OutlayBal <> 0 THEN @OutlayBal ELSE ISNULL(MAT.[OutlayBal], 0) END) AS [OutlayBal], MAT.[OutlayBud] AS [OutlayBud], CONVERT(DECIMAL(19, 2), CASE WHEN @ClientCurrent <> 0 THEN @ClientCurrent + @ClientDeposit WHEN @ClientDeposit <> 0 THEN @ClientCurrent + @ClientDeposit ELSE ISNULL(MAT.[ClientBal], 0) END) AS [ClientBal], CONVERT(DECIMAL(19, 2), CASE WHEN @ClientCurrent <> 0 THEN @ClientCurrent ELSE ISNULL(MAT.[ClientCurBal], 0) END) AS [ClientCurBal], CONVERT(DECIMAL(19, 2), CASE WHEN @ClientDeposit <> 0 THEN @ClientDeposit ELSE ISNULL(MAT.[ClientDepBal], 0) END) AS [ClientDepBal], MAT.[StatuteLimits] AS [StatuteLimits], RTRIM(ISNULL(MAT.[User1], '')) AS [User1], RTRIM(ISNULL(MAT.[User2], '')) AS [User2], RTRIM(ISNULL(MAT.[User3], '')) AS [User3], MAT.[Value] AS [Value], @TimeBal AS [TimeBal], CASE WHEN LEN(@TimeBal / 60) < 2 THEN '0' + CAST(@TimeBal / 60 AS VARCHAR) ELSE CAST(@TimeBal / 60 AS VARCHAR) END + ':' + SUBSTRING(CONVERT(VARCHAR(3), 100 + CONVERT(INT, @TimeBal) % 60), 2, 2) AS [TimeBalHours], @ChargeBal AS [ChargeBal], CASE WHEN LEN(@TotalTime / 60) < 2 THEN '0' + CAST(@TotalTime / 60 AS VARCHAR) ELSE CAST(@TotalTime / 60 AS VARCHAR) END + ':' + SUBSTRING(CONVERT(VARCHAR(3), 100 + CONVERT(INT, @TotalTime) % 60), 2, 2) AS [TotalHours], @TotalCharge AS [TotalCharge], MAT.[FileColour] AS [FileColour], RTRIM(ISNULL(MAT.[OldRef], '')) AS [OldRef], RTRIM(ISNULL(MAT.[Status], '')) AS [Status], MAT.[StatuteLimits] AS [StatuteLims], RTRIM(ISNULL(MAT.[Comment], '')) AS [Comment], @DraftBillValue AS [DraftBillTotal], @TIEDATE AS [LastBillDate], @LDIADATE AS [LastActionDate], RTRIM(ISNULL(@LDIAActionType, '')) AS [LastActionType], RTRIM(ISNULL(@LDIATEXT1, '')) AS [LastActionText], RTRIM(ISNULL(@LDIAFNCODE, '')) AS [LastActionFE], @MDIADATE AS [LastMilestoneDate], RTRIM(ISNULL(@MDIAActionType, '')) AS [LastMilestoneType], RTRIM(ISNULL(@MDIATEXT1, '')) AS [LastMilestoneText], RTRIM(ISNULL(@MDIAFNCODE, '')) AS [LastMilestoneFE], @NDIADATE AS [NextActionDate], RTRIM(ISNULL(@NDIAActionType, '')) AS [NextActionType], RTRIM(ISNULL(@NDIATEXT1, '')) AS [NextActionText], RTRIM(ISNULL(@NDIAFNCODE, '')) AS [NextActionFE], @LastTimeEntryDate AS [LastTimeEntryDate], @LastTimeEntryTime AS [LastTimeEntryTime], @LastTimeEntryRate AS [LastTimeEntryRate], @LastTimeEntryCharge AS [LastTimeEntryCharge], @LastTimeEntryTask AS [LastTimeEntrytask], @LastTimeEntryComment AS [LastTimeEntryComment], @LastTimeEntryFE AS [LastTimeEntryFE], @TimeEntryTotalCharge AS [TimeEntryTotalCharge], @TimeEntryTotalTime AS [TotalTime], STC.[DESCRIPTION] AS [StatusCodeDescription], CSM.[CSWKTCODE] AS [CSWKTCode], TMP.[WKDESC] AS [WKDesc], CASE WHEN MAT.[ShowCommentInAlarm] = 'Y' THEN 'True' ELSE 'False' END AS [ShowCommentInAlarm], RTRIM(ISNULL(FCL.[COLOURDESC], '')) AS [FileColourDescription], ISNULL(FCL.[RGBColour], -1) AS [FileColourRGB], @Section68 AS [Section68], @IsApproved AS [IsApproved] FROM dbo.[matters] MAT LEFT JOIN dbo.[WorkTypes] WKT ON WKT.[CODE] = MAT.[WType] LEFT OUTER JOIN dbo.[Departments] DPT ON DPT.[CODE] = MAT.[Dept] LEFT OUTER JOIN dbo.[Handlers] HAN ON HAN.[CODE] = MAT.[FECode] LEFT OUTER JOIN dbo.[client] CLT ON CLT.[CLCODE] = MAT.[ClientCode] LEFT OUTER JOIN dbo.[StatusCodes] STC ON STC.[CODE] = MAT.[Status] LEFT OUTER JOIN dbo.[CaseMaster] CSM LEFT OUTER JOIN dbo.[Templates] TMP ON TMP.[WKCODE] = CSM.[CSWKTCODE] ON CSM.[CSCODE] = MAT.[Code] LEFT OUTER JOIN [dbo].[FileColours] FCL ON FCL.[COLOURCODE] = MAT.[FileColour] WHERE MAT.[Code] = @MatterCode --Result Set 6 --Total Time [Activity] SELECT CASE WHEN LEN(TIES.[TOTALTIME] / 60) < 2 THEN '0' + CAST(TIES.[TOTALTIME] / 60 AS VARCHAR) ELSE CAST(TIES.[TOTALTIME] / 60 AS VARCHAR) END + ':' + SUBSTRING(CONVERT(VARCHAR(3), 100 + (TIES.[TOTALTIME] % 60)), 2, 2) AS [ActivityTotalTime], TIES.[Value] AS [ActivityTotalValue] FROM ( SELECT ISNULL(SUM(ISNULL(TIE.[TIME], 0)), 0) AS [TOTALTIME], SUM(CONVERT(DECIMAL(18, 2), ISNULL(TIE.[Charge], 0))) AS [Value] FROM [dbo].[matters] MAT INNER JOIN [dbo].[TimeEntry] TIE ON TIE.[MATTER] = MAT.[Code] WHERE TIE.[MATTER] = @MatterCode AND ( (TIE.[TimeOrCharge] = 'T') OR (TIE.[TimeOrCharge] = 'C')) AND TIE.[TASK] <> 'WRI') TIES --Result Set 7 --Write off time SELECT TIES.[WriteOffValue] As [WriteOffValue] FROM ( SELECT SUM(CONVERT(DECIMAL(18, 2), ISNULL(TIE.[Charge], 0))) AS [WriteOffValue] FROM [dbo].[matters] MAT INNER JOIN [dbo].[TimeEntry] TIE ON TIE.[MATTER] = MAT.[Code] WHERE TIE.[MATTER] = @MatterCode AND ( (TIE.[TimeOrCharge] = 'T') OR (TIE.[TimeOrCharge] = 'C')) AND TIE.[Task] = 'WRI') TIES -- PLEASE NOTE THAT THE CALCULATION BELOW IS ALSO USED IN ky_NETTLFetchTimeEntry, so -- please modify both procedures if this needs to be changed. -- Result Set 10 -- The fees issued to date IF @IsSAM4 = 0 BEGIN SET @NCOMMAND = N' SELECT ISNULL(SUM((CASE WHEN HED.[INVCR] = ''I'' THEN TRN.[VALUE] ELSE TRN.[VALUE] * - 1 END)), 0) AS [FeesIssued] FROM [dbo].[BatchH] HED INNER JOIN [dbo].[BatchDetails] TRN ON TRN.[BATCHNO] = HED.[BATCHNO] WHERE HED.[MATTER] = @MatterCode AND HED.[POSTED] = ''Y'' AND TRN.[TYPE] = ''I'' AND TRN.[OUTLAY] = ''F''' END ELSE BEGIN SET @NCOMMAND = N' DECLARE @TotalFeesBilled DECIMAL(22, 2) SELECT @TotalFeesBilled = ISNULL(SUM(ISNULL(TRN.[FeesBilled], 0) * ISNULL(TRN.[DrCr], 1) * (-1)), 0) FROM [dbo].[TransactionsHeaders] HED INNER JOIN [dbo].[Transactions] TRN ON TRN.[BatchNo] = HED.[BatchNo] AND TRN.[RecType] = ''D'' AND TRN.[LineType] = ''F'' AND TRN.[XnType] = ''I'' AND TRN.[Posted] = ''Y'' AND ( TRN.[CorrectionInd] = ''L'' OR TRN.[CorrectionInd] = ''X'') WHERE HED.[MatterCode] = @MatterCode --values imported from Axxia SELECT @TotalFeesBilled = @TotalFeesBilled + ISNULL(SUM(ISNULL(TRN.[FeesBilled], 0) * ISNULL(TRN.[DrCr], 1) * (-1)), 0) FROM [dbo].[Transactions] TRN LEFT OUTER JOIN [dbo].[TransactionsHeaders] HED ON HED.[BatchNo] = TRN.[BatchNo] WHERE TRN.[MatterCode] = @MatterCode AND TRN.[RecType] = ''D'' AND TRN.[LineType] = ''F'' AND TRN.[XnType] = ''I'' AND TRN.[Posted] = ''Y'' AND ( TRN.[CorrectionInd] = ''L'' OR TRN.[CorrectionInd] = ''X'') AND HED.[BatchNo] IS NULL SELECT @TotalFeesBilled AS [FeesIssued]' END EXECUTE sp_executesql @NCOMMAND, N'@MatterCode VARCHAR(20)', @MatterCode = @MatterCode -- Result Set 11 -- Current outstanding fees IF @IsSAM4 = 0 BEGIN SET @NCOMMAND = N' SELECT CONVERT(DECIMAL(19, 2), ISNULL(SUM(ISNULL(DTL.[OSFees], 0)), 0)) AS [OSFees] FROM [dbo].[DebtorsLedger] DTL WHERE DTL.[matter] = @MatterCode' END ELSE BEGIN SET @NCOMMAND = N' SELECT CONVERT(DECIMAL(19, 2), ISNULL(SUM(ISNULL(TRN.[FeesOS], 0)), 0)) AS [OSFees] FROM [dbo].[Transactions] TRN WHERE TRN.[MatterCode] = @MatterCode AND TRN.[Posted] = ''Y'' AND TRN.[RecType] <> ''V'' AND TRN.[CorrectionInd] = ''L''' END EXECUTE sp_executesql @NCOMMAND, N'@MatterCode VARCHAR(20)', @MatterCode = @MatterCode SELECT @IsSAM4 AS [IsSAM4] END GO IF OBJECT_ID(N'KAAS_CP_GetAllUserLoginDetails',N'P')IS NOT NULL DROP PROCEDURE [dbo].[KAAS_CP_GetAllUserLoginDetails] GO CREATE PROCEDURE [dbo].[KAAS_CP_GetAllUserLoginDetails] AS /******************************************************************************************************* * * * Stored Procedure Name : [dbo].[KAAS_CP_GetAllUserLoginDetails] * * Description: To get login details for all users in system * * * * Modification History: * * 2021-07-06 Aakif Created * *******************************************************************************************************/ 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], dbo.KAAS_CP_GetLastLoginDateTime( ClientLoginId) AS [ClientLastAccessDateTime], dbo.KAAS_CP_FNCheckUserIsLocked( ClientLoginId) AS [IsLocked], [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] 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_GetDashboardWidgetDetails',N'P')IS NOT NULL DROP PROCEDURE [dbo].[KAAS_CP_GetDashboardWidgetDetails] GO CREATE PROCEDURE [dbo].[KAAS_CP_GetDashboardWidgetDetails] (@LoginId VARCHAR(100)) AS /******************************************************************************************************* * * * Stored Procedure Name : [dbo].[KAAS_CP_GetDashboardWidgetDetails] * * Description: To get widget details for Home page Dashboard * * * * Modification History: * * 2021-07-06 Vinodhkumar Created * 2021-07-09 Vinodhkumar Instead of using select statement,using scalar variables to store * and using the variable to dispaly the data * * 2021-08-09 Aakif Included default value decimal precision for cost variables * *******************************************************************************************************/ BEGIN DECLARE @ActiveCases INT, @OutstandingAction INT, @RecentAction INT, @OutstandingInvoice DECIMAL(10,2), @UnbilledOutlay DECIMAL(10,2), @UnbilledTime DECIMAL(10,2) DECLARE @PublishedCasesList TABLE ( [MatterCode] NVARCHAR(15) ) --Getting the published matter list and stored in table variable INSERT INTO @PublishedCasesList SELECT [MatterCode] FROM [dbo].[KAAS_CP_TFGetMatterCodeForClient] (@LoginId) --To get active cases SELECT @ActiveCases = COUNT(1) FROM @PublishedCasesList --To get outstanding action for all active cases SELECT @OutstandingAction = COUNT(1) FROM [dbo].[diary] WHERE ISNULL([Publish], '') = 'P' AND [Status] = 0 AND [CaseCode] IN (SELECT [MatterCode] FROM @PublishedCasesList) --To get the recent action for all active cases SELECT @RecentAction = COUNT(1) FROM [dbo].[Diary] WHERE ISNULL([Publish], '') = 'P' AND [Date] >= DATEADD(DAY,-7,GETUTCDATE()) AND [CaseCode] IN (SELECT [MatterCode] FROM @PublishedCasesList) --To get the Outstanding invoice for active cases SELECT @OutstandingInvoice = CONVERT(DECIMAL(19, 2), ISNULL(SUM(ISNULL([DebtBal], 0)), 0)) FROM [dbo].[Matters] WHERE ISNULL([Publish], '') = 'P' AND [Code] IN (SELECT [MatterCode] FROM @PublishedCasesList) --To get the Unbilled outlay amount SELECT @UnbilledOutlay = CONVERT(DECIMAL(19, 2), ISNULL(SUM(ISNULL([OutlayBal], 0)), 0)) FROM [dbo].[Matters] WHERE ISNULL([Publish], '') = 'P' AND [Code] IN (SELECT [MatterCode] FROM @PublishedCasesList) --To get the unbilled time for all active cases SELECT @UnbilledTime = CONVERT(DECIMAL(19, 2), ISNULL(SUM(ISNULL([ChargeBal], 0)), 0)) FROM [dbo].[Matters] WHERE ISNULL([Publish], '') = 'P' AND [Code] IN (SELECT [MatterCode] FROM @PublishedCasesList) SELECT @ActiveCases AS [ActiveCases], @OutstandingAction AS [OutstandingAction], @RecentAction AS [RecentAction], @OutstandingInvoice AS [OutstandingInvoice], @UnbilledOutlay AS [UnbilledOutlay], @UnbilledTime AS [UnbilledTime] 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 * * 2021-07-29 Aakif Replaced table function to get document counts for published items * ************************************************************************************************************************/ 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] AS [seq], [id] AS [FolderId], [parent] AS [parent], [foldername] AS [foldername], [foldericon] AS [foldericon], [folderimage] AS [folderimage], [foldershared] AS [foldershared], [folderisours] AS [folderisours], [foldername] AS [Document], [documentcount] AS [DocumentCount], 999 + TRY_CAST(seq AS INT) AS [Id] FROM [dbo].[KAAS_CP_FNGetDocFolders](@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 * * 2021-07-29 Aakif Replaced table function to get document counts for published items * *******************************************************************************************************/ 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] AS [seq], [id] AS [FolderId], [parent] AS [parent], [foldername] AS [foldername], [foldericon] AS [foldericon], [folderimage] AS [folderimage], [foldershared] AS [foldershared], [folderisours] AS [folderisours], [foldername] AS [Document], [documentcount] AS [DocumentCount], 999 + TRY_CAST(seq AS INT) AS [Id] FROM [dbo].[KAAS_CP_FNGetDocFolders](@matter, @folderid, 1, 1) END GO IF OBJECT_ID(N'KAAS_CP_GetPortalTemplateForKey',N'P')IS NOT NULL DROP PROCEDURE [dbo].[KAAS_CP_GetPortalTemplateForKey] GO CREATE PROCEDURE [dbo].[KAAS_CP_GetPortalTemplateForKey] (@TemplateKey VARCHAR(100)) AS /******************************************************************************************************* * * * Stored Procedure Name : [dbo].[KAAS_CP_GetPortalTemplateForKey] * * Description: To get email or message template for provided key * * * * Modification History: * * 2021-07-02 Aakif Created * *******************************************************************************************************/ BEGIN SELECT [PortalTemplateId] AS [PortalTemplateId], [TemplateType] AS [TemplateKey], [TemplateSubject] AS [TemplateSubject], [TemplateBody] AS [TemplateBody] FROM [dbo].[PortalNotificationTemplate] WHERE [TemplateType] = @TemplateKey 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 GO 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, @IsResendMail BIT = 0, @ClientName VARCHAR(256) = NULL, @PhoneNo VARCHAR(50) = NULL) AS /******************************************************************************************************* * * * Stored Procedure Name : [dbo].[KAAS_CP_RegisterNewUser] * * Description: To register new user in Client Portal * * * * Modification History: * * 2021-06-07 Aakif Created * * 2021-06-28 Aakif Included condition to resend account activation mail * *******************************************************************************************************/ BEGIN -- Update OTP with its expiry time IF(@IsResendMail = 0) BEGIN INSERT INTO [dbo].[ClientLogin] ( [Email], [IsActiveLogin], [IsTwoFactorEnabled], [EmailActivationToken], [EmailActivationTokenCreatedTime], [EmailActivationTokenExpirationTime] ) VALUES ( @EmailId, 0, @IsTwoFactorEnabled, @EmailActivationToken, @TokenCreationTime, @TokenExpirationTime ) END ELSE BEGIN UPDATE [dbo].[ClientLogin] SET [EmailActivationToken] = @EmailActivationToken, [EmailActivationTokenCreatedTime] = @TokenCreationTime, [EmailActivationTokenExpirationTime] = @TokenExpirationTime WHERE [Email] = @EmailId END -- Check if name and phone number is availabe and update user data IF (ISNULL(@ClientName, '') != '') BEGIN UPDATE [dbo].[ClientLogin] SET [Name] = @ClientName WHERE [Email] = @EmailId END IF (ISNULL(@PhoneNo, '') != '') BEGIN UPDATE [dbo].[ClientLogin] SET [PhoneNumber] = @PhoneNo WHERE [Email] = @EmailId END END GO IF OBJECT_ID(N'KAAS_CP_RevokeClientAccessForCode',N'P')IS NOT NULL DROP PROCEDURE [dbo].[KAAS_CP_RevokeClientAccessForCode] GO CREATE PROCEDURE [dbo].[KAAS_CP_RevokeClientAccessForCode] (@LoginId BIGINT, @ClientCode VARCHAR(20) ) AS /******************************************************************************************************* * * * Stored Procedure Name : [dbo].[KAAS_CP_RevokeClientAccessForCode] * * Description: To revoke users access for client codein Client Portal * * * * Modification History: * * 2021-06-28 Aakif Created * *******************************************************************************************************/ BEGIN DELETE FROM [ClientLoginAccess] WHERE ClientLoginId = @LoginId AND ContactCode = @ClientCode END GO IF OBJECT_ID(N'KAAS_CP_UnlockOrToggleIsActive',N'P')IS NOT NULL DROP PROCEDURE [dbo].[KAAS_CP_UnlockOrToggleIsActive] GO CREATE PROCEDURE [dbo].[KAAS_CP_UnlockOrToggleIsActive] (@LoginId BIGINT, @ToggleIsActive BIT, @IsActive BIT = 0) AS /******************************************************************************************************* * * * Stored Procedure Name : [dbo].[KAAS_CP_UnlockOrToggleIsActive] * * Description: To unlock a user or toggle IsActive * * * * Modification History: * * 2021-07-07 Aakif Created * *******************************************************************************************************/ BEGIN -- UNLOCK A USER IF(@ToggleIsActive = 0) BEGIN UPDATE [dbo].[ClientLogin] SET [NoOfAttempt] = 0, [LockedOutEndDate] = NULL, [IsActiveLogin] = 1 WHERE [ClientLoginId] = @LoginId END -- TOGGLE ISACTIVE FLAG FOR USER ELSE IF(@ToggleIsActive = 1) BEGIN UPDATE [dbo].[ClientLogin] SET [IsActiveLogin] = @IsActive WHERE [ClientLoginId] = @LoginId END 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, @Result BIT OUTPUT) AS /******************************************************************************************************* * * * Stored Procedure Name : [dbo].[KAAS_CP_UpdateClientAccess] * * Description: To add accesss to clients for a code * * * * Modification History: * * 2021-06-25 Aakif Created * * 2021-06-29 Aakif Included out paramter to denote if client code is already added for user * *******************************************************************************************************/ BEGIN -- CLIENT ACCESS LOGIN TO CONTACT CODES DECLARE @ClientId BIGINT = 0; DECLARE @IsNewEntry BIT = 0; SELECT @ClientId = ClientLoginId FROM [ClientLogin] WHERE Email = @EmailId -- INSERT TO CLIENT LOGIN ACCESS IF NOT EXISTS (SELECT 1 FROM ClientLoginAccess WHERE ClientLoginId = @ClientId AND ContactCode = @ContactCode) BEGIN INSERT INTO [ClientLoginAccess] ( ClientLoginId, ContactCode, MatterCode, HasAccessToAllMatter, PublishedHandlerCode, PublishedDateTime ) VALUES ( @ClientId, @ContactCode, @MatterCode, 'Y', @HandlerCode, GETUTCDATE() ) SELECT @Result = 1 END ELSE BEGIN SELECT @Result = 0 END 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] WHERE [Email] = @EmailId -- 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_UpdatePortalSetting',N'P')IS NOT NULL DROP PROCEDURE [dbo].[KAAS_CP_UpdatePortalSetting] GO CREATE PROCEDURE [dbo].[KAAS_CP_UpdatePortalSetting] (@IsTwoFactor BIT, @LockoutCount INT, @LockoutDuration INT, @PasswordNotifyMode TINYINT = 0, @UpeKey VARCHAR(200) = NULL, @IsKeyUpdated BIT = 0, @TenantLogo IMAGE = NULL, @LogoType VARCHAR(15) = NULL) AS /******************************************************************************************************* * * * Stored Procedure Name : [dbo].[KAAS_CP_UpdatePortalSetting] * * Description: To get settings for client portal * * * * Modification History: * * 2021-06-29 Aakif Created * * 2021-07-01 Aakif Included option to modify tenant Logo * *******************************************************************************************************/ BEGIN IF (@IsKeyUpdated = 1) BEGIN UPDATE [ClientPortalSetting] SET [IsTwoFactorEnabled] = @IsTwoFactor, [IsLockOut_Attempt] = @LockoutCount, [LockoutDurationInMins] = @LockoutDuration, [PasswordResetNotification] = @PasswordNotifyMode END ELSE BEGIN INSERT INTO [dbo].[ClientPortalSetting] ( [UPEKey], [IsTwoFactorEnabled], [IsLockOut_Attempt], [LockoutDurationInMins], [PasswordResetNotification] ) VALUES ( @UpeKey, @IsTwoFactor, @LockoutCount, @LockoutDuration, @PasswordNotifyMode ) END IF(ISNULL(@LogoType, '') <> '') BEGIN UPDATE [ClientPortalSetting] SET [TenantLogoType] = @LogoType, [TenantLogo] = @TenantLogo END 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_UpdateUserPhoneNumber',N'P')IS NOT NULL DROP PROCEDURE [dbo].[KAAS_CP_UpdateUserPhoneNumber] GO CREATE PROCEDURE [dbo].[KAAS_CP_UpdateUserPhoneNumber] (@PhoneNo VARCHAR(50), @LoginId BIGINT, @HandlerCode VARCHAR(10)) AS /******************************************************************************************************* * * * Stored Procedure Name : [dbo].[KAAS_CP_UpdateUserPhoneNumber] * * Description: To update user phone number and log who updated it * * * * Modification History: * * 2021-06-30 Aakif Created * *******************************************************************************************************/ BEGIN -- Step 1: Update user data UPDATE [dbo].[ClientLogin] SET [PhoneNumber] = @PhoneNo WHERE [ClientLoginId] = @LoginId -- Step 2: Add modification history for user INSERT INTO [dbo].[ClientPortalPhoneNoUpdateLog] ( [ClientLoginId], [UpdatedByHandler], [UpdateDateTime] ) VALUES ( @LoginId, @HandlerCode, GETUTCDATE() ) 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