IF NOT EXISTS
  (SELECT TOP 1 1
     FROM sys.objects [SO]
    WHERE [SO].[name] = 'RMLBackup'
      AND [SO].[type] = 'U')
  BEGIN
    SELECT *
      INTO [dbo].[RMLBackup]
      FROM [dbo].[RecentMatterList]
  END
GO

IF EXISTS
  (SELECT TOP 1 1
     FROM sys.objects [SO]
          LEFT OUTER JOIN sys.[columns] [SC]
                       ON [SC].object_id = [SO].object_id
                      AND [SC].[name] = 'UniqueID'
    WHERE [SO].[name] = 'RecentMatterList'
      AND [SO].[type] = 'U'
      AND [SC].[column_id] IS NULL)
  BEGIN
    ALTER TABLE
          [dbo].[RecentMatterList]
      ADD [UniqueID] BIGINT IDENTITY(1, 1) NOT NULL
  END
GO

IF EXISTS
  (SELECT TOP 1 1
     FROM sys.objects [SO]
          INNER JOIN sys.indexes [SI]
                     INNER JOIN sys.index_columns [IC]
                                INNER JOIN sys.columns [SC]
                                        ON [SC].object_id = [IC].object_id
                                       AND [SC].[column_id] = [IC].[column_id]
                             ON [IC].object_id = [SI].object_id
                            AND [IC].[index_id] = [SI].[index_id]
                  ON [SI].object_id = [SO].object_id
                 AND [SI].[is_primary_key] = 1
    WHERE [SO].[name] = 'RecentMatterList'
      AND [SO].[type] = 'U'
      AND [SC].[name] <> 'UniqueID')
  BEGIN
    DECLARE @NAME NVARCHAR(128)

    SELECT @NAME = CONVERT(NVARCHAR(128), [SI].[name])
      FROM sys.objects [SO]
           INNER JOIN sys.indexes [SI]
                   ON [SI].object_id = [SO].object_id
                  AND [SI].[is_primary_key] = 1
     WHERE [SO].[name] = 'RecentMatterList'
       AND [SO].[type] = 'U'

    DECLARE @COMMAND NVARCHAR(MAX)

    SET @COMMAND = N'ALTER TABLE [dbo].[RecentMatterList] DROP CONSTRAINT [' + @NAME + N']'

    EXEC sp_executesql @COMMAND
  END
GO

IF EXISTS
  (SELECT TOP 1 1
     FROM sys.objects [SO]
          LEFT OUTER JOIN sys.indexes [SI]
                       ON [SI].object_id = [SO].object_id
                      AND [SI].[is_primary_key] = 1
    WHERE [SO].[name] = 'RecentMatterList'
      AND [SO].[type] = 'U'
      AND [SI].[index_id] IS NULL)
  BEGIN
    ALTER 
    TABLE [dbo].[RecentMatterList]
      ADD CONSTRAINT [PK_RecentMatterList] 
                     PRIMARY KEY CLUSTERED
                    ([UniqueID])
  END
GO

IF EXISTS
  (SELECT TOP 1 1
     FROM sys.objects [SO]
          LEFT OUTER JOIN sys.indexes [SI]
                       ON [SI].object_id = [SO].object_id
                      AND [SI].[name] = 'IDX_FeeMatter'
    WHERE [SO].[name] = 'RecentMatterList'
      AND [SO].[type] = 'U'
      AND [SI].[index_id] IS NULL)
  BEGIN
    CREATE UNIQUE NONCLUSTERED INDEX
           [IDX_FeeMatter]
        ON [dbo].[RecentMatterList]
          ([FEE],
           [MATTER])
           INCLUDE ([Datefield], [UniqueID])
  END
GO

IF EXISTS
  (SELECT TOP 1 1
     FROM sys.objects [SO]
          LEFT OUTER JOIN sys.indexes [SI]
                       ON [SI].object_id = [SO].object_id
                      AND [SI].[name] = 'IDX_FeeDateDesc'
    WHERE [SO].[name] = 'RecentMatterList'
      AND [SO].[type] = 'U'
      AND [SI].[index_id] IS NULL)
  BEGIN
    CREATE NONCLUSTERED INDEX
           [IDX_FeeDateDesc]
        ON [dbo].[RecentMatterList]
          ([FEE],
           [DateField] DESC)
           INCLUDE ([MATTER], [UniqueID])
  END
GO


IF EXISTS
  (SELECT TOP 1 1
     FROM SYS.[objects] SO
    WHERE SO.[name] = N'ky_NETPSetMostRecentMatter'
      AND SO.[type] = 'P')
  BEGIN
    DROP PROCEDURE [dbo].[ky_NETPSetMostRecentMatter]
  END
GO

CREATE PROCEDURE
       [dbo].[ky_NETPSetMostRecentMatter]
      (@handler      VARCHAR(10),
       @matter       VARCHAR(20))
AS
/*************************************************************************************************************
 *                                                                                                           *
 * [dbo].[ky_NETPSetMostRecentMatter]                                                                        *
 *                                                                                                           *
 * Updates the Recent Matter List for a handler. The Handlers table stores the                               *
 * maximum number of Recent Matters to store for each handler. If not SET, the                               *
 * default value is 30.                                                                                      *
 *                                                                                                           *
 * Compatibility information - PLEASE update older versions if necessary to ensure the compatible software   *
 *                                    remains fully functional                                               *
 *   *****************************************************************************************************   *
 *   *                                                                                                   *   *
 *   * Supersedes:                              -                                                        *   *
 *   * Original:                                -                                                        *   *
 *   * First compatible version:                5.7.2.1                                                  *   *
 *   * Last compatible software version:        -                                                        *   *
 *   * Superseded by:                           -                                                        *   *
 *   *                                                                                                   *   *
 *   *****************************************************************************************************   *
 *                                                                                                           *
 * Modification History                                                                                      *
 * ????-??-?? ???????????     Created                                                                        *
 * 2014-10-23 Pino Carafa     Remove transaction handling                                                    *
 * 2016-07-22 Pino Carafa     Imported into Framework                                                        *
 * 2016-07-22 Pino Carafa     Add Option (KEEPFIXED PLAN) to prevent unnecessary                             *
 *                            procedure recompiles                                                           *
 * 2018-02-15 Pino Carafa     Modify procedure to prevent problems                                           *
 *                            when the system date/time is changed                                           *
 * 2019-08-29 Arun V          Have included the distinct keyword while inserting                             *
 *                            the records into Recent matter.                                                *
 *                            In case of any duplication, due to repeated call,                              *
 *                            this will remove the duplicate item from the list.                             *
 * 2019-08-29 Pino Carafa     Use Group By and MAX instead of DISTINCT                                       *
 * 2019-08-30 Pino Carafa     Rewrite after change to PK and Indexes                                         *
 *                                                                                                           *
 *************************************************************************************************************/
BEGIN

DECLARE @RMLMax INT

SELECT @RMLMax = [HAN].[NORECENTMATTERS]
  FROM [dbo].[Handlers] [HAN]
 WHERE [HAN].[code] = @handler

SET @RMLMax = CASE WHEN ISNULL(@RMLMax, 0) = 0
                        THEN 30
                   ELSE @RMLMax END

DECLARE @NOW DATETIME
DECLARE @MAXDATE DATETIME

SET @NOW = GETDATE()

DECLARE @UniqueID BIGINT

SELECT @UniqueID = [RML].[UniqueID]
  FROM [dbo].[RecentMatterList] [RML]
 WHERE [RML].[FEE] = @handler
   AND [RML].[MATTER] = @matter

IF @UniqueID IS NOT NULL
  BEGIN
    UPDATE [RML]
       SET [RML].[DateField] = @Now
      FROM [dbo].[RecentMatterList] [RML]
     WHERE [RML].[UniqueID] = @UniqueID
  END
ELSE
  BEGIN
    INSERT
      INTO [dbo].[RecentMatterList]
          ([FEE],
           [MATTER],
           [DateField])
    VALUES(@handler,
           @matter,
           @NOW)

    DECLARE @total INT

    SELECT @total = COUNT(1)
      FROM [dbo].[RecentMatterList] [RML]
     WHERE [RML].[FEE] = @handler

    SET @total = ISNULL(@total, 0) - @RMLMax --the number of items we must delete

    -- In theory @total should always be <= 1, but just in case
    WHILE @total > 0
      BEGIN
        SET @UniqueID = NULL

        -- Get the oldest item in the Recent Matter List for this FEE
          SELECT TOP 1
                 @UniqueID = [RML].[UniqueID]
            FROM [dbo].[RecentMatterList] [RML]
           WHERE [RML].[FEE] = @handler
        ORDER BY [RML].[DateField] ASC

        IF @UniqueID IS NULL
          BEGIN
            -- In theory this should never happen... but just in case
            SET @total = 0
          END
        ELSE
          BEGIN

            -- and delete the oldest item in the Recent Matter List for this FEE
            DELETE [RML]
              FROM [dbo].[RecentMatterList] [RML]
             WHERE [RML].[UniqueID] = @UniqueID

            SET @total = @total - 1
          END
      END
  END

END

GO