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(20) = NULL,  
       @IsShowCloseMatter   BIT = 0)  
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 *  
* 2022-02-10    vinodhkumar.M  Included -  FE code, Team code column                                   *  
* 2022-02-10    vinodhkumar.M  Removed  -  new SP created to fetch FE code, Team code column, so       *  
                                           removing columns here                                       *  
* 2022-04-20    vinodhkumar.M  Included -  client Code                                                 *  
* 2022-04-26    vinodhkumar.M  Included -  Fe Code,Telephone number,DisallowNewMatter and              *  
                               handled Inactive    condition                                           *  
* 2022-04-29    vinodhkumar.M  Updated - Replaced Contact inactive condition with Matter table         *  
                                         closed in where clause also put left join for matter table    *  
                                         with [FeeEarnerCodes] table                                   *  
* 2022-06-06    Aakif          Updated SP to include option to list closed matter as well              *  
* 2022-06-08    Aakif          Included column to show matter close status                             *  
* 2022-06-14    Aakif          Included column to show Outstanding balance                             *  
* 2022-07-07    Aakif          Included option to show recently accessed matter first                  *  
* 2022-08-11    Aakif          Performance Tuning                                                      *  
* 2023-10-12    Pino Carafa    Rewrite using [dbo].[PublishedMatters] View                             *  
*                                                                                                      *  
********************************************************************************************************/  
BEGIN  
  
SET NOCOUNT ON  
  
SET @CaseCode = RTRIM(ISNULL(@CaseCode, ''))  
  
DECLARE @MATS TABLE  
       ([Code]               VARCHAR(20)      PRIMARY KEY,  
        [LastAccess]         DATETIME,  
        UNIQUE NONCLUSTERED ([LastAccess] DESC, [Code]))  
  
DECLARE @COUNT INT  
  
IF @CaseCode <> ''  
  BEGIN  
    SELECT @COUNT = COUNT(1)  
      FROM [dbo].[ClientLoginAccess] [CLA] WITH (NOLOCK)  
           INNER JOIN [dbo].[PublishedMatters] [MAT] WITH (NOEXPAND, NOLOCK)  
                   ON [MAT].[Code] = [CLA].[MatterCode]  
     WHERE [CLA].[ClientLoginId] = @LoginId  
       AND [CLA].[MatterCode] = @CaseCode  
  END  
ELSE IF @IsShowCloseMatter = 1  
  BEGIN  
    SELECT @COUNT = COUNT(1)  
      FROM [dbo].[ClientLoginAccess] [CLA] WITH (NOLOCK)  
           INNER JOIN [dbo].[PublishedMatters] [MAT] WITH (NOEXPAND, NOLOCK)  
                   ON [MAT].[Code] = [CLA].[MatterCode]  
     WHERE [CLA].[ClientLoginId] = @LoginId  
     AND [CLA].[MatterCode] IS NOT NULL  
  END  
ELSE  
  BEGIN  
    SELECT @COUNT = COUNT(1)  
      FROM [dbo].[ClientLoginAccess] [CLA] WITH (NOLOCK)  
           INNER JOIN [dbo].[PublishedMatters] [MAT] WITH (NOEXPAND, INDEX([IDX_Closed]), NOLOCK)  
                   ON [MAT].[Closed] = 0  
                  AND [MAT].[Code] = [CLA].[MatterCode]  
     WHERE [CLA].[ClientLoginId] = @LoginId  
       AND [CLA].[MatterCode] IS NOT NULL  
  END  
  
IF @COUNT > 0  
BEGIN  
  IF @CaseCode <> ''  
    BEGIN  
      INSERT  
        INTO @MATS  
            ([Code],  
             [LastAccess])  
      SELECT TOP (@COUNT)  
             [MAT].[Code],  
             [MAT].[Started]  
        FROM [dbo].[ClientLoginAccess] [CLA] WITH (NOLOCK)  
             INNER JOIN [dbo].[PublishedMatters] [MAT] WITH (NOEXPAND, NOLOCK)  
                     ON [MAT].[Code] = [CLA].[MatterCode]  
       WHERE [CLA].[ClientLoginId] = @LoginId  
         AND [CLA].[MatterCode] = @CaseCode  
    END  
  ELSE IF @IsShowCloseMatter = 1  
    BEGIN  
      INSERT  
        INTO @MATS  
            ([Code],  
             [LastAccess])  
      SELECT TOP (@COUNT)  
             [MAT].[Code],  
             [MAT].[Started]  
        FROM [dbo].[ClientLoginAccess] [CLA] WITH (NOLOCK)  
             INNER JOIN [dbo].[PublishedMatters] [MAT] WITH (NOEXPAND, NOLOCK)  
                     ON [MAT].[Code] = [CLA].[MatterCode]  
       WHERE [CLA].[ClientLoginId] = @LoginId  
         AND [CLA].[MatterCode] IS NOT NULL  
    END  
  ELSE  
    BEGIN  
      INSERT  
        INTO @MATS  
            ([Code],  
             [LastAccess])  
      SELECT TOP (@COUNT)  
             [MAT].[Code],  
             [MAT].[Started]  
        FROM [dbo].[ClientLoginAccess] [CLA] WITH (NOLOCK)  
             INNER JOIN [dbo].[PublishedMatters] [MAT] WITH (NOEXPAND, INDEX([IDX_Closed]), NOLOCK)  
                     ON [MAT].[Closed] = 0  
                    AND [MAT].[Code] = [CLA].[MatterCode]  
       WHERE [CLA].[ClientLoginId] = @LoginId  
         AND [CLA].[MatterCode] IS NOT NULL  
    END  
END  
  
IF @CaseCode <> ''  
  BEGIN  
    SELECT @COUNT = COUNT(1)  
      FROM [dbo].[ClientLoginAccess] [CLA] WITH (NOLOCK)  
           INNER JOIN [dbo].[PublishedMatters] [MAT] WITH (NOEXPAND, INDEX([IDX_Client]), NOLOCK)  
                   ON [MAT].[ClientCode] = [CLA].[ContactCode]  
                  AND [MAT].[Code] = @CaseCode  
     WHERE [CLA].[ClientLoginId] = @LoginId  
       AND [CLA].[MatterCode] IS NULL  
  END  
ELSE IF @IsShowCloseMatter = 1  
  BEGIN  
    SELECT @COUNT = COUNT(1)  
      FROM [dbo].[ClientLoginAccess] [CLA] WITH (NOLOCK)  
           INNER JOIN [dbo].[PublishedMatters] [MAT] WITH (NOEXPAND, INDEX([IDX_Client]), NOLOCK)  
                   ON [MAT].[ClientCode] = [CLA].[ContactCode]  
     WHERE [CLA].[ClientLoginId] = @LoginId  
       AND [CLA].[MatterCode] IS NULL  
  END  
ELSE  
  BEGIN  
    SELECT @COUNT = COUNT(1)  
      FROM [dbo].[ClientLoginAccess] [CLA] WITH (NOLOCK)  
           INNER JOIN [dbo].[PublishedMatters] [MAT] WITH (NOEXPAND, INDEX([IDX_ClosedClient]), NOLOCK)  
                   ON [MAT].[Closed] = 0  
                  AND [MAT].[ClientCode] = [CLA].[ContactCode]  
     WHERE [CLA].[ClientLoginId] = @LoginId  
       AND [CLA].[MatterCode] IS NULL  
  END  
  
IF @COUNT > 0  
BEGIN  
  IF @CaseCode <> ''  
    BEGIN  
      INSERT  
        INTO @MATS  
            ([Code],  
             [LastAccess])  
      SELECT TOP (@COUNT)  
             [MAT].[Code],  
             [MAT].[Started]  
        FROM [dbo].[ClientLoginAccess] [CLA] WITH (NOLOCK)  
             INNER JOIN [dbo].[PublishedMatters] [MAT] WITH (NOEXPAND, INDEX([IDX_Client]), NOLOCK)  
                        LEFT OUTER JOIN @MATS [MTX]  
                                     ON [MTX].[Code] = [MAT].[Code]  
                     ON [MAT].[ClientCode] = [CLA].[ContactCode]  
                    AND [MAT].[Code] = @CaseCode  
       WHERE [CLA].[ClientLoginId] = @LoginId  
         AND [CLA].[MatterCode] IS NULL  
         AND [MTX].[Code] IS NULL  
   END  
  ELSE IF @IsShowCloseMatter = 1  
    BEGIN  
      INSERT  
        INTO @MATS  
            ([Code],  
             [LastAccess])  
      SELECT TOP (@COUNT)  
             [MAT].[Code],  
             [MAT].[Started]  
        FROM [dbo].[ClientLoginAccess] [CLA] WITH (NOLOCK)  
             INNER JOIN [dbo].[PublishedMatters] [MAT] WITH (NOEXPAND, INDEX([IDX_Client]), NOLOCK)  
                        LEFT OUTER JOIN @MATS [MTX]  
                                     ON [MTX].[Code] = [MAT].[Code]  
                     ON [MAT].[ClientCode] = [CLA].[ContactCode]  
       WHERE [CLA].[ClientLoginId] = @LoginId  
         AND [CLA].[MatterCode] IS NULL  
         AND [MTX].[Code] IS NULL  
    END  
  ELSE  
    BEGIN  
      INSERT  
        INTO @MATS  
            ([Code],  
             [LastAccess])  
      SELECT TOP (@COUNT)  
             [MAT].[Code],  
             [MAT].[Started]  
        FROM [dbo].[ClientLoginAccess] [CLA] WITH (NOLOCK)  
             INNER JOIN [dbo].[PublishedMatters] [MAT] WITH (NOEXPAND, INDEX([IDX_ClosedClient]), NOLOCK)  
                        LEFT OUTER JOIN @MATS [MTX]  
                                     ON [MTX].[Code] = [MAT].[Code]  
                     ON [MAT].[Closed] = 0  
                    AND [MAT].[ClientCode] = [CLA].[ContactCode]  
       WHERE [CLA].[ClientLoginId] = @LoginId  
         AND [CLA].[MatterCode] IS NULL  
         AND [MTX].[Code] IS NULL  
    END  
END  
  
UPDATE [MAT]  
   SET [MAT].[LastAccess] = [CPRM].[LastAccessDate]  
  FROM [dbo].[ClientPortalRecentMatter] [CPRM] WITH (NOLOCK)  
       INNER JOIN @MATS [MAT]  
               ON [MAT].[Code] = [CPRM].[MatterCode]  
 WHERE [CPRM].[ClientLoginId] = @LoginId  
  
  SELECT [MAT].[Code]                    AS [Code],  
         [LA].[DATE]                     AS [LastActionDate],  
         [MS].[DATE]                     AS [LastMilestoneDate],  
         [NA].[DATE]                     AS [NextActionDate],  
         [LA].[DisplayText]              AS [LastActionText],  
         [MS].[DisplayText]              AS [LastMilestoneText],  
         [NA].[DisplayText]              AS [NextActionText],  
         [MAT].[Name]                    AS [ClientName],  
         [MAT].[ClientCode]              AS [ClientCode],  
         [MAT].[FECode]                  AS [FECode],  
         [MAT].[Telephone]               AS [Telephone],  
         [MAT].[ClientAddress]           AS [ClientAddress],  
         [MAT].[Code]                    AS [MatterCode],  
         [MAT].[MatterDescription]       AS [MatterDescription],  
         [MAT].[CaseHandler]             AS [CaseHandler],  
         [MAT].[User1Label]              AS [User1Label],  
         [MAT].[User1]                   AS [User1],  
         [MAT].[User2Label]              AS [User2Label],  
         [MAT].[User2]                   AS [User2],  
         [MAT].[User3Label]              AS [User3Label],  
         [MAT].[User3]                   AS [User3],  
         [MAT].[YourRefLabel]            AS [YourRefLabel],  
         [MAT].[YourRef]                 AS [YourRef],  
         [MAT].[MatterStartDate]         AS [MatterStartDate],  
         [MAT].[MatterCloseDate]         AS [MatterCloseDate],  
         [MAT].[MatterStatusCode]        AS [MatterStatusCode],  
         [MAT].[MatterStatus]            AS [MatterStatus],  
         [MAT].[MatterOriginalDebt]      AS [MatterOriginalDebt],  
         [MAT].[MatterComments]          AS [MatterComments],  
         [MAT].[DisallowNewMatter]       AS [DisallowNewMatter],  
         [MAT].[IsMatterClosed]          AS [IsMatterClosed],  
         [MAT].[OutstandingAmount]       AS [OutstandingAmount],  
         [MT1].[LastAccess]              AS [LastAccessDate]  
    FROM @MATS [MT1]  
         INNER JOIN [dbo].[PublishedMatters] [MAT] WITH (NOEXPAND, NOLOCK, INDEX([PK_PublishedMatters]))  
                 ON [MAT].[Code] = [MT1].[Code]  
         OUTER APPLY (  SELECT TOP 1  
                               [PUB].[ACTIONID],  
                               [PUB].[DATE],  
                               [PUB].[DisplayText]  
                          FROM [dbo].[PublishedActions] [PUB]  
                               WITH (NOEXPAND, INDEX ([IDX_NextActions]), NOLOCK)  
                         WHERE [PUB].[CASECODE] = [MAT].[Code]  
                           AND [PUB].[STATUS]   = 0  
                      ORDER BY [PUB].[CASECODE],  
                               [PUB].[STATUS],  
                               [PUB].[DATE]) [NA]  
         OUTER APPLY (  SELECT TOP 1  
                               [PUB].[ACTIONID],  
                               [PUB].[DATE],  
                               [PUB].[DisplayText]  
                          FROM [dbo].[PublishedActions] [PUB]  
                               WITH (NOEXPAND, INDEX ([IDX_LastActions]), NOLOCK)  
                         WHERE [PUB].[CASECODE] = [MAT].[Code]  
                           AND [PUB].[STATUS]   = 1  
                      ORDER BY [PUB].[CASECODE],  
                               [PUB].[STATUS] DESC,  
                               [PUB].[DATE]   DESC) [LA]  
         OUTER APPLY (  SELECT TOP 1  
                               [PUB].[ACTIONID],  
                               [PUB].[DATE],  
                               [PUB].[DisplayText]  
                          FROM [dbo].[PublishedActions] [PUB]  
                               WITH (NOEXPAND, INDEX ([IDX_Highlighted]), NOLOCK)  
                         WHERE [PUB].[CASECODE] = [MAT].[Code]  
                           AND [PUB].[HIGHLIGHTED] = 1  
                           AND [PUB].[STATUS]   = 1  
                      ORDER BY [PUB].[CASECODE],  
                               [PUB].[HIGHLIGHTED] DESC,  
                               [PUB].[DATE]        DESC) [MS]  
ORDER BY [MT1].[LastAccess] DESC,  
         [MAT].[Code] ASC  
  
SET NOCOUNT OFF  
  
END  
GO