IF OBJECT_ID(N'KAAS_CP_AcitvateNewUserAccount',N'P')IS NOT NULL
	DROP PROCEDURE [dbo].[KAAS_CP_AcitvateNewUserAccount]
GO

CREATE PROCEDURE 
	[dbo].[KAAS_CP_AcitvateNewUserAccount]
    (@EmailId       VARCHAR(200),
	 @UserName      VARCHAR(256),
	 @PhoneNumber   VARCHAR(50),
	 @PasswordHash  NVARCHAR(512))

AS

/*******************************************************************************************************
*                                                                                                      *
* Stored Procedure Name : [dbo].[KAAS_CP_AcitvateNewUserAccount]									   *
* Description: To activate new user account with password hash										   *
*                                                                                                      *
* Modification History:                                                                                *
* 2021-06-08    Aakif        Created                                                                   *
 *******************************************************************************************************/

BEGIN

-- Update user details to activate account
UPDATE [dbo].[ClientLogin]
	SET
		[PasswordHash]						 = @PasswordHash,
		[Name]								 = @UserName,
		[PhoneNumber]						 = @PhoneNumber,
		[IsEmailConfirmed]					 = 1,
		[IsUserVerifiedEmail]				 = 1,
		[EmailActivationToken]				 = NULL,
		[EmailActivationTokenExpirationTime] = NULL
	WHERE [Email] = @EmailId


END

GO
IF OBJECT_ID(N'KAAS_CP_FetchDiaryByMatterCode',N'P')IS NOT NULL
	DROP PROCEDURE [dbo].[KAAS_CP_FetchDiaryByMatterCode]
GO

CREATE PROCEDURE
       [dbo].[KAAS_CP_FetchDiaryByMatterCode]
      (@Matter        VARCHAR(20),
       @Search        VARCHAR(200) = NULL,
       @PageNumber    INT          = NULL,
       @PageSize      INT          = NULL,
       @ActionTypeXML XML          = NULL,
       @PriorityXML   XML          = NULL,
       @FlagXML       XML          = NULL,
       @DateXML       XML          = NULL,
	   @LoginId		  BIGINT	   = NULL )
AS
/*************************************************************************************************************
 *                                                                                                           *
 * Copied From: [dbo].[KEYHM_FetchDiaryByMatterCode]                                                         *
 *                                                                                                           *
 * Fetch Diary Items by Matter Code, with support for pagination and for filtering on ActionType,            *
 * Priority and/or Flag.                                                                                     *
 *                                                                                                           *
 * @Search can be any string. Wildcard % will be appended at start and finish                                *
 * @ActionTypeXML: example - '<ActionType Value="A" /><ActionType Value="N" />'                              *
 * @PriorityXML: example -  '<Priority Value="N" />'                                                         *
 * @DateXML: example -  '<Dates FromDate="20190101" ToDate="20190430"/>'                                     *
 * @FlagXML: example-  '<Flag Value="0" /><Flag Value="1" /><Flag Value="2" /><Flag Value="3" />'            *
 *                                                                                                           *
 * Compatibility information - PLEASE update older versions if necessary to ensure the compatible software   *
 *                                    remains fully functional                                               *
 *   *****************************************************************************************************   *
 *   *                                                                                                   *   *
 *   * Supersedes:                              [dbo].[ky_NETCASEDIARYFetchDiarybyMatterCode]            *   *
 *   * Original:                                [dbo].[ky_NETSPFetchDiaryByMatterCode]                   *   *
 *   * First compatible version:                5.7.2.1                                                  *   *
 *   * Last compatible software version:        -                                                        *   *
 *   * Superseded by:                           -                                                        *   *
 *   *                                                                                                   *   *
 *   *****************************************************************************************************   *
 *                                                                                                           *
 * Modification History                                                                                      *
  * 2019-04-02   Pino Carafa			Created                                                              *
 * 2019-04-30   Pino Carafa			Added Subject search and Date Range                                      *
 * 2019-05-21   Saravanan			Performance tuning done                                                  *
 * 2019-05-29   Arun				KEYHM-402 - Sorting order was correct till @FOUND table					 *
									variable(Line No: 232), after this, few column have joined for desired	 *
									output, at that time, sorting  order gets changed, so explicitily have	 * 
									soredted by id column after join operation completed(Line no: 380).		 *
 * 2019-10-23   Vinodhan            If Flag is null, it be replaced with 0 and filtered while filtering the
                                    case diary data                                                          *
									soredted by id column after join operation completed(Line no: 380).		 *	
 * 2019-10-29   Arun                Have used KEYHM_FNConvertUTCDateToTargetTimeZone function to convert     * 
									action date to utc									                     *
 * 2021-06-14   Aakif				Created                                                                  *
 *************************************************************************************************************/

BEGIN

SET NOCOUNT ON

DECLARE @ShowFromToSynopsis BIT
DECLARE @FromDate DATETIME
DECLARE @ToDate   DATETIME

SET @ShowFromToSynopsis = CASE WHEN ISNULL((SELECT TOP 1
                                                   [SET].[KeyValue]
                                              FROM [dbo].[Settings] [SET]
                                       WHERE [SET].[KeyName] = 'ShowFromToSynopsis'),
                                           'True') = 'False'
                                    THEN 0
                               ELSE 1 END

-- Convert @ActionTypesXML etc into Table Variables for easier reference in later scripts START
DECLARE @ACTIONTYPES TABLE
       ([ActionType] VARCHAR(1)       PRIMARY KEY)



DECLARE @ACTIONTYPESFILTERED BIT
SET @ACTIONTYPESFILTERED = 0 --Squeeze the last little bit of performance out of this

DECLARE @PRIORITIES  TABLE
       ([Priority]   VARCHAR(1)       PRIMARY KEY)

DECLARE @PRIORITIESFILTERED BIT
SET @PRIORITIESFILTERED = 0  --Squeeze the last little bit of performance out of this

DECLARE @Flags  TABLE
       ([Flag]       TINYINT       PRIMARY KEY)

DECLARE @FLAGSFILTERED BIT
SET @FLAGSFILTERED = 0       --Squeeze the last little bit of performance out of this

IF NOT (@DateXML IS NULL)
  IF NOT (CONVERT(VARCHAR(MAX), @DateXML) = '')
    BEGIN
      SELECT [FromDate], [ToDate]
        FROM (SELECT [DT].[COL].value('@FromDate', 'DATETIME')                  AS [FromDate], -- Use >= for comparison
                     DATEADD(DAY, 1, [DT].[COL].value('@ToDate', 'DATETIME'))   AS [ToDate]    -- Add 1 Day to ToDate so that we can use < for comparison
                FROM @DateXML.nodes('/Dates') AS [DT]([COL])) [DTP]
    END

IF NOT (@ActionTypeXML IS NULL)
  IF NOT (CONVERT(VARCHAR(MAX), @ActionTypeXML) = '')
    BEGIN
      INSERT
        INTO @ACTIONTYPES
            ([ActionType])
      SELECT [ActionType]
        FROM (SELECT [AT].[COL].value('@Value', 'VARCHAR(10)') AS [ActionType]
                FROM @ActionTypeXML.nodes('/ActionType') AS [AT]([COL])) [ATP]

      SELECT @ACTIONTYPESFILTERED = CASE COUNT(1) WHEN 0 THEN 0 ELSE 1 END FROM @ACTIONTYPES
    END


    --Select * from @ActionTypes

IF NOT (@PriorityXML IS NULL)
  IF NOT (CONVERT(VARCHAR(MAX), @PriorityXML) = '')
    BEGIN
      INSERT
        INTO @PRIORITIES
            ([Priority])
      SELECT [Priority]
        FROM (SELECT [PT].[COL].value('@Value', 'VARCHAR(10)') AS [Priority]
                FROM @PriorityXML.nodes('/Priority') AS [PT]([COL])) [PTY]

      SELECT @PRIORITIESFILTERED = CASE COUNT(1) WHEN 0 THEN 0 ELSE 1 END FROM @PRIORITIES
    END

IF NOT (@FlagXML IS NULL)
  IF NOT (CONVERT(VARCHAR(MAX), @FlagXML) = '')
    BEGIN
      INSERT
        INTO @Flags
            ([Flag])
      SELECT [Flag]
        FROM (SELECT [FG].[COL].value('@Value', 'INT') AS [Flag]
                FROM @FlagXML.nodes('/Flag') AS [FG]([COL])) [FLG]

      SELECT @FLAGSFILTERED = CASE COUNT(1) WHEN 0 THEN 0 ELSE 1 END FROM @Flags
    END
-- Convert @ActionTypesXML etc into Table Variables for easier reference in later scripts END

-- Set @SearchText to NULL if nothing entered, otherwise ensure search starts and ends with
-- a wildcard character % - START
DECLARE @SEARCHTEXT VARCHAR(202)

SET @SearchText = CASE WHEN RTRIM(ISNULL(@Search, '')) = '' THEN NULL ELSE RTRIM(ISNULL(@Search, '')) END

IF @SearchText IS NOT NULL
  BEGIN
    IF SUBSTRING(@SearchText, 1, 1) <> '%'
      BEGIN
        SET @SearchText = '%' + @SearchText
      END

    IF SUBSTRING(@SearchText, LEN(@SearchText), 1) <> '%'
      BEGIN
        SET @SearchText = @SearchText + '%'
      END
  END
-- Set @SearchText to NULL if nothing entered, otherwise ensure search starts and ends with
-- a wildcard character % - END

-- Avoid Unnecessary Join START
DECLARE @ChargeArrangement VARCHAR(3)
SELECT @ChargeArrangement = RTRIM(ISNULL([MAT].[ChargeArrangement], '')) FROM [dbo].[matters] [MAT] WHERE [MAT].[Code] = @Matter
-- Avoid Unnecessary Join END

--SELECT ActionType FROM #ACTIONTYPES

-- Table to contain all matching ActionIDs in the desired order
DECLARE @FOUND TABLE
       ([id]        INT        IDENTITY(1, 1)         PRIMARY KEY,
        [ActionID]  INT)

-- Insert ONLY the Primary Keys into the @FOUND Table so that it's much faster to retrieve only the requested page - Performance!
INSERT
  INTO @FOUND
      ([ActionID])
SELECT [DIA].[ActionID]
  FROM [dbo].[diary] [DIA]
       LEFT OUTER JOIN [dbo].[Handlers] [FN]
                    ON [FN].[CODE] = [DIA].[FNCODE]
 WHERE [DIA].[CASECODE] = @Matter
	   AND ISNULL([DIA].PUBLISH, '') = 'P'

   --    -- Use the CASE Construct to ensure it doesn't perform the PATINDEX unless it's actually necessary - Performance!
   AND CASE WHEN @SEARCHTEXT IS NULL
                 THEN 0
            WHEN PATINDEX(@SEARCHTEXT, [DIA].[TEXT1]) > 0
                 THEN 0
            WHEN PATINDEX(@SEARCHTEXT, [DIA].[SUBJECT]) > 0
                 THEN 0
            WHEN [FN].[NAME] IS NULL
                 THEN 1
            WHEN PATINDEX(@SEARCHTEXT, [FN].[NAME]) > 0
                 THEN 0
            ELSE 1 END = 0

       -- Use the CASE Construct to ensure it doesn't perform SELECT until an actual filter is specified - Performance!
   AND CASE WHEN @FromDate IS NULL
                 THEN 0
            WHEN [DIA].[DATE] >= @FromDate
                 THEN 0
            ELSE 1 END = 0

       -- Use the CASE Construct to ensure it doesn't perform SELECT until an actual filter is specified - Performance!
   AND CASE WHEN @ToDate IS NULL
                 THEN 0
            WHEN [DIA].[DATE] < @ToDate
                 THEN 0
            ELSE 1 END = 0

       -- Use the CASE Construct to ensure it doesn't perform SELECT until an actual filter is specified - Performance!
   AND (@ACTIONTYPESFILTERED = 0 Or ([DIA].[ActionType] in (SELECT * FROM @ACTIONTYPES)))

   --    -- Use the CASE Construct to ensure it doesn't perform SELECT until an actual filter is specified - Performance!
   AND (@PRIORITIESFILTERED = 0 OR ([DIA].[Priority] IN (SELECT * FROM @Priorities)))

   --    -- Use the CASE Construct to ensure it doesn't perform SELECT until an actual filter is specified - Performance!
   AND (@FLAGSFILTERED = 0 OR (ISNULL([DIA].[Flag], 0) IN (SELECT * FROM @Flags)))

-- The Order By Clause is only required here, as further down the @FOUND table will return values ordered by [id]
-- which is the exact order they were entered into it here - Again, Performance!
ORDER BY [DIA].[CASECODE] ASC,
         [DIA].[STATUS] ASC,
         [DIA].[DATE] DESC,
         [DIA].[DYSTARTTIME] DESC,
         [DIA].[ACTIONID] DESC

--for debugging uncomment this below line, after completion please dont forget to uncomment
--select * from @FOUND

DECLARE @TOTAL INT
SET @TOTAL = (SELECT COUNT(1) FROM @FOUND)

SELECT @TOTAL AS [TotalRecords]


declare @HandlerTimeZone nvarchar(100)




-- Only do the full SELECT with all columns when returning the actual result set. Driven from @FOUND
SELECT --[FND].[id],
        RTRIM(ISNULL([DIA].[CASECODE], ''))                                                                 AS [CASECODE],
        --CONVERT(VARCHAR, [DIA].[DATE], 20)    AS [DATE],
        --CONVERT(VARCHAR(5), [DIA].[DATE], 108)                                                              AS [Time],
		dbo.KAAS_CP_FNConvertUTCDateToTargetTimeZone([DIA].[DATE], @LoginId)								AS [DATE],
		CONVERT(VARCHAR(5),dbo.KAAS_CP_FNConvertUTCDateToTargetTimeZone([DIA].[DATE], @LoginId), 108)		AS [Time],
        RTRIM(ISNULL([DIA].[STATUS], 0))                                                                    AS [DStatus],
        RTRIM(ISNULL([DIA].[ACTIONCODE], ''))                                                               AS [ACTIONCODE],
        RTRIM(ISNULL([DIA].[ACTIONTYPE], ''))                                                               AS [ACTIONTYPE],
        RTRIM(ISNULL([DIA].[PROCESSTYPE], ''))                                                              AS [PROCESSTYPE],
        RTRIM(ISNULL([DIA].[FNCODE], ''))                                                                   AS [FNCODE],
        RTRIM(ISNULL([FN].[NAME], ''))                                                                      AS [FNNAME],
        RTRIM(ISNULL([DIA].[TEAMCODE], ''))                                                                 AS [TEAMCODE],
        RTRIM(ISNULL([DIA].[DisplayText], ''))                                                              AS [TEXT1],
        ISNULL(CASE WHEN ISNUMERIC([DIA].[DYSTARTTIME]) = 1
                         THEN [DIA].[DYSTARTTIME]
                    ELSE [dbo].[ky_ConvertTimeToClarion]([DIA].[DYSTARTTIME]) END,
               0)                                                                                           AS [DYSTARTTIME],
        ISNULL(CASE WHEN ISNUMERIC([DIA].[DYENDTIME]) = 1
                         THEN [DIA].[DYENDTIME]
                    ELSE [dbo].[ky_ConvertTimeToClarion]([DIA].[DYENDTIME]) END,
               0)                                                                                           AS [DYENDTIME],
        ISNULL([DIA].[ACTIONID],0)                                                                          AS [ACTIONID],
        ISNULL([DIA].[ORGINALACTIONID],0)                                                                   AS [ORGINALACTIONID],
        RTRIM(ISNULL([DIA].[PRIORITY], ''))                                                                 AS [PRIORITY],
        RTRIM(ISNULL([DIA].[HIGHLIGHTED], 'N'))                                                             AS [HIGHLIGHTED],
        [HAT].[HasAttachments]                                                                              AS [ATTACHMENTS],
        ISNULL([DIA].[PROCESSSTATUS], 0)                                                                    AS [PROCESSSTATUS],
        ISNULL([DIA].[WORKPROCESS], 0)                                                                      AS [WORKPROCESS],
        ISNULL([DIA].[BILLABLE], 0)                                                                         AS [BILLABLE],
        RTRIM(ISNULL([DIA].[EMAILADDRESS], ''))                                                             AS [EMAILADDRESS],
        RTRIM(ISNULL(CONVERT(VARCHAR(200), [DIA].[ADDRESSTO]), ''))                                         AS [ADDRESSTO],
        RTRIM(ISNULL([DIA].[EMAIL], 'N'))                                                                   AS [EMAIL],
        RTRIM(ISNULL(CONVERT(VARCHAR(200), [DIA].[SUBJECT]), ''))                                           AS [SUBJECT],
        ISNULL([DIA].[DELEGATIONSTATUS], 0)                                                                 AS [DELEGATIONSTATUS],
        ISNULL([DIA].[DRAFTBILLNO], 0)                                                                      AS [DRAFTBILLNO],
        ISNULL([DIA].[CHEQUEREQNO], 0)                                                                      AS [CHEQUEREQNO],
        CAST(CASE WHEN RTRIM(ISNULL([DIA].[ACTIONTYPE],'')) ='E'
                   AND @ShowFromToSynopsis = 1
      THEN CASE WHEN ISNULL([DIA].[PROCESSTYPE],'O')='I'
                                      THEN   'Email From:'
                                           + (RTRIM(ISNULL(CAST([DIA].[EMAILADDRESS] AS VARCHAR(300)),
                                                           '')))
                                           + ' - '
                                           + ISNULL(CAST ([DIA].[Subject] AS VARCHAR(300)),'')
                                 ELSE   'Email To:'
                                      + RTRIM(CAST(ISNULL([DIA].[AddressTo],
                                                          '') AS VARCHAR(300)))
                                      + ' - '
                                      + ISNULL(CAST([DIA].[Subject] AS VARCHAR(300)), '') END
                  ELSE CASE WHEN DATALENGTH([DIA].[SUBJECT]) = 0
                                 THEN CASE WHEN SUBSTRING([DIA].[DisplayText],
                                                          0,
                                                          CHARINDEX(CHAR(13),
                  [DIA].[DisplayText])) = ''
                                                THEN ISNULL([DIA].[TEXT1], '')
                                           ELSE SUBSTRING([DIA].[DisplayText],
                                                          0,
                                                          CHARINDEX(CHAR(13),
                                                                    [DIA].[DisplayText])) END
                            WHEN RTRIM(CAST([DIA].[SUBJECT] AS VARCHAR(300))) = ''
                                 THEN CASE WHEN SUBSTRING([DIA].[DisplayText],
                                                          0,
                                                          CHARINDEX(CHAR(13),
                                                                    [DIA].[DisplayText])) = ''
                                                THEN ISNULL([DIA].[TEXT1], '')
                                           ELSE SUBSTRING([DIA].[DisplayText],
                                                          0,
                                                          CHARINDEX(CHAR(13),
                                                                    [DIA].[DisplayText])) END
                            WHEN [DIA].[SUBJECT] IS NULL
                                 THEN CASE WHEN SUBSTRING([DIA].[DisplayText],
                                                          0,
                                                          CHARINDEX(CHAR(13),
                                                                    [DIA].[DisplayText])) = ''
                                                THEN ISNULL([DIA].[TEXT1], '')
                                           ELSE SUBSTRING([DIA].[DisplayText],
                                                          0,
                                                          CHARINDEX(CHAR(13),
                                                                    [DIA].[DisplayText])) END
                            ELSE [DIA].[SUBJECT] END END AS VARCHAR(300))                                   AS [Synopsis],
        ISNULL([DIA].[Flag],0)                                                                              AS [Flag],
        CASE WHEN RTRIM(ISNULL([DIA].[ForCopy], 0)) = '1'
                  THEN 'True'
             ELSE 'False' END                                                                               AS [ForCopy],
        CASE WHEN ISNULL([DIA].[WORKPROCESS], 0) = 1
              AND ISNULL([DIA].[STATUS], 0) <> 1
                  THEN 'G'
             WHEN ISNULL([DIA].[STATUS], 0) = 1
                  THEN 'C'
             ELSE '' END                                                                                    AS [COMPELETEORGENERATE],
        CASE WHEN ISNULL([DIA].[WORKPROCESS], 0) = 1
              AND ISNULL([DIA].[STATUS], 0) <> 1
          THEN 'G'
             WHEN ISNULL([DIA].[STATUS], 0) = 1
                  THEN 'C'
             ELSE '' END                                                                                    AS [COMPELETEORGENERATEDD],
        ISNULL([TAC].[DESC], '')                                                                            AS [ActionTitle],
        ISNULL([TMP].[WKDESC], '')                                                                          AS [WorkFlow],
        CASE WHEN ISNULL([UND].[NeedsAttention], 0) = 1
                  THEN 'Yes'
             ELSE 'No' END                                                                                  AS [Needs Attention],
        CASE WHEN ISNULL([UND].[NeedsAttention], 0) = 1
                  THEN 'Visible'
             ELSE 'Collapsed' END                                                                           AS [NeedsAttentionIcon],
        CASE WHEN @SearchText IS NULL
                  THEN ''
             WHEN PATINDEX(@SearchText, [DIA].[TEXT1]) > 0
                  THEN @Search
             ELSE '' END                                                                                    AS [SearchMatch],
        ISNULL([DIA].[CreationDate], CONVERT(DATETIME, [DIA].[DATE]))                                       AS [CreationDate],
		CASE
			WHEN [DIA].[DUEDATE] != NULL OR [DIA].[DUEDATE] != ''
				THEN CONVERT(VARCHAR, dbo.KAAS_CP_FNConvertUTCDateToTargetTimeZone([DIA].[DUEDATE], @LoginId)	, 20)
			ELSE
				CONVERT(VARCHAR, dbo.KAAS_CP_FNConvertUTCDateToTargetTimeZone([DIA].[DATE], @LoginId)	, 20)
		END                                                                                                 AS [DUEDATE], 
        RTRIM(ISNULL([DIA].[DELEGATEDFNR], ''))                                                             AS [DelegatedFE],
        RTRIM(ISNULL([TAC].[Arrangement], ''))                                                              AS [Arrangement],
        @ChargeArrangement                                                                                  AS [ChargeArrangement]

 FROM @FOUND [FND]
        INNER JOIN [dbo].[diary] [DIA]
                   LEFT OUTER JOIN [dbo].[Handlers] [FN]
                                ON [FN].[CODE] = [DIA].[FNCODE]
                ON [DIA].[ActionID] = [FND].[ActionID]
        LEFT OUTER JOIN [dbo].[TemplateActions] [TAC]
                        LEFT OUTER JOIN [dbo].[Templates] [TMP]
                                     ON [TMP].[WKCODE] = [TAC].[WKTCODE]
                    ON [TAC].[ACTIONCODE] = [DIA].[ACTIONCODE]
        LEFT OUTER JOIN [dbo].[Undertakings] [UND]
                    ON [UND].[ActionID] = [DIA].[ACTIONID]
                    AND [UND].[ActionID] <> 0
                    AND [UND].[ActionID] IS NOT NULL
        CROSS APPLY (SELECT CASE WHEN COUNT(1) = 0
                                      THEN 'N'
                                ELSE 'Y' END AS [HasAttachments]
                       FROM [dbo].[DiaryAttachments] [DAT]
                      WHERE [DAT].[DiaryID] = [DIA].[ACTIONID]) [HAT]
  WHERE CASE WHEN @PageNumber = 0
                  THEN 0
             WHEN [FND].[ID] <= (@PageNumber - 1) * @PageSize
                  THEN 1
             WHEN [FND].[ID] > @PageNumber * @PageSize
                  THEN 1
             ELSE 0 END = 0
			 AND ISNULL([DIA].PUBLISH, '') = 'P'
 ORDER BY [FND].[id]

End
GO

IF OBJECT_ID(N'KAAS_CP_GetCurrentTenantFirmName',N'P')IS NOT NULL
	DROP PROCEDURE [dbo].[KAAS_CP_GetCurrentTenantFirmName]
GO
CREATE PROCEDURE [dbo].[KAAS_CP_GetCurrentTenantFirmName]
AS
/*******************************************************************************************************
*                                                                                                      *
* Stored Procedure Name : [dbo].[KAAS_CP_GetCurrentTenantFirmName]	      							   *
* Description: To get tenant firm name for client portal											   *
*                                                                                                      *
* Modification History:                                                                                *
* 2021-06-04    Aakif        Created																   *
 *******************************************************************************************************/

BEGIN

	--SET DEFAULT HEADER IF FIRM NAME IS NOT AVAILABLE
	SELECT
		[NAME] AS [FirmName]
	FROM
		[dbo].[control]
END

GO






IF OBJECT_ID(N'KAAS_CP_GetDocumentsIM',N'P')IS NOT NULL
	DROP PROCEDURE [dbo].[KAAS_CP_GetDocumentsIM]
GO

CREATE PROCEDURE
       [dbo].[KAAS_CP_GetDocumentsIM]
      (@matter              VARCHAR(20),
       @ActionID            INT,
       @DocFolderID         INT,
       @IMDocs              VARCHAR(MAX),
	   @PageNo              INT             = NULL,
       @PageSize            INT             = NULL,
	   @SearchText          VARCHAR(4000)   = '',
       @FileType            VARCHAR(4000)   = '',
       @SortColumn          VARCHAR(50)     = '',
       @SortDirection       VARCHAR(10)     = '',
	   @LoginId				BIGINT			= 0,
	   @ColumnNames         VARCHAR(50)		= '',
	   @FilterValues        VARCHAR(4000)	= '',
	   @DocumentIsNear		BIT				= 0,
	   @DocumentStartDate	DATE			='',
	   @DocumentEndDate		DATE			=''
	   )
AS
/************************************************************************************************************************
 *                                                                                                                      *
 * Copied From: [dbo].[KAAS_GetDocumentsIM]                                                                             *
 *                                                                                                                      *
 * Get list of Documents for the Document Manager                                                                       *
 *                                                                                                                      *
 * Compatibility information - PLEASE update older versions if necessary to ensure the compatible software              *
 *                                    remains fully functional                                                          *
 *   *****************************************************************************************************              *
 *   *                                                                                                   *              *
 *   * Supersedes:                              -                                                        *              *
 *   * Original:                                [ky_NETSPGetDocumentsIM]                                 *              *
 *   * First compatible version:                5.7.2.1                                                  *              *
 *   * Last compatible software version:        -                                                        *              *
 *   * Superseded by:                           -                                                        *              *
 *   *                                                                                                   *              *
 *   *****************************************************************************************************              *
 *                                                                                                                      *
 * Modification History                                                                                                 *
 * 2019-04-23   Vinodhan K        Created                                                                               *
 * 2019-05-24   Pino Carafa       Performance improvements                                                              *
 * 2019-05-24   Vinodhan		  Search field have changed as document instead of filepath                             *
 * 2019-05-24   Arun			  Performance improvements,									                            *
 * 2019-09-09   Vinodhan K		  Search now can be done on Document class description field				            *
 * 2019-09-18   Vinodhan K		  Total no. of records now will be displayed based on searched text (KEYSAAS - 1035)    *
 * 2020-02-13   Prabhu.V      	  Modified the SP to Implement filter works with dynamic values                         *
 * 2020-04-23   Prabhu.V          Performance Improved                           										*
 * 2020-04-29   Prabhu.V          Fixed FileType Filter Not working after Improved the SP                				*
 * 2020-05-04   Arun V            Document classes datatype size incresed.   KEYHM-582                                  *
 * 2020-05-04   Prabhu V          Document total reocrd issue fixed                                                     *
 * 2020-05-20   Prabhu V          Custom Filter Condition missed on performance improvement and added it now            *
 * 2021-06-02   Sadiq			  Handled seaching for Document near this Funcionlaity in Document manager(11989)		*
 * 2021-06-07   Sadiq             Filter option need to be added when "DOcument Near this" is selected(11988)			*
 * 2021-06-11   Sadiq             Added document name search  also now . (12398)
 * 2021-06-16   Aakf              Created																				*
 ************************************************************************************************************************/
BEGIN
DECLARE @res TABLE
       ([id]                       INT identity(1, 1),
        [ActionId]                 INT,
        [TrackReference]           INT,
        [IMDocID]                  VARCHAR(500),
        [IMClass]                  VARCHAR(20),
        [DocClass]                 VARCHAR(10),
        [Document]                 VARCHAR(2000),
        [DiaryDate]                DATETIME,
        [DocumentDate]             DATETIME,
        [TYPE]                     VARCHAR(20),
        [Attachments]              CHAR(1),
        [EMAIL]                    VARCHAR(1),
        [EMAILFROM]                VARCHAR(200),
        [EMAILSENT]                DATETIME,
        [EMAILTO]                  VARCHAR(1000),
        [CurrentVersion]           INT,
        [IsInDocFolder]            BIT,
        [IsShared]                 BIT,
		[DocumentClassDescription] VARCHAR(40),
        [UniqueId]					INT,
		[FilePath]					VARCHAR(500),
		[Subject]					VARCHAR(2000),
		[ShortText]					VARCHAR(120),
		[NAME]						VARCHAR(500),
		[ProcessType]               VARCHAR(50),
		[ACTIONTYPE]				VARCHAR(50),
		[ActionTypeDescription]		VARCHAR(500),
		[DOCUMENTSOURCE]			VARCHAR(500)
		,[TotalRecord] INT
		)
DECLARE @TOTALRECORDS INT =0;
DECLARE @classes TABLE
       ([KeyhouseClass]     INT,
        [IMClass]           INT,
        [ClassCode]         VARCHAR(20),
        [ClassDescription]  VARCHAR(100))

DECLARE @IMDocTable TABLE
       ([IMDocID]           VARCHAR(500)        PRIMARY KEY,
        [Description]       VARCHAR(2000),
        [IMClass]           VARCHAR(20),
        [IMType]            VARCHAR(20),
        [EmailFrom]         VARCHAR(200),
        [EmailTo]           VARCHAR(1000),
        [EmailSent]         DATETIME,
        [DocumentDate]      DATETIME)

DECLARE @IMDocExpanded TABLE
       ([id]                INT              IDENTITY(1, 1)  PRIMARY KEY,
        [ActionID]          INT,
        [TrackReference]    INT,
        [IMDocID]           VARCHAR(500),
        [IMClass]           VARCHAR(20),
        [DocClass]          VARCHAR(10),
        [Document]          VARCHAR(2000),
        [DiaryDate]         DATETIME,
        [DocumentDate]      DATETIME,
        [EmailSent]         DATETIME,
        [TYPE]              VARCHAR(20),
        [Attachments]       CHAR(1),
        [EMAIL]             CHAR(1),
        [EmailFrom]         VARCHAR(200),
        [EmailTo]           VARCHAR(1000),
        [CurrentVersion]    INT)

		DECLARE @FileTypeList TABLE
		([FileType]			VARCHAR(20)						PRIMARY KEY)

		INSERT INTO @FileTypeList
		SELECT 
			RTRIM(LTRIM([Item])) 
		FROM 
			KAAS_FN_SplitString(@FileType, ',')

DECLARE @idoc INT
/*
DECLARE @StartRow INT = NULL;
DECLARE @EndRow   INT = NULL;
IF(((ISNULL(@PageNo, 0)) != 0 AND (ISNULL(@PageSize, 0)) != 0) AND (@PageNo > -1))
BEGIN
    SET @PageNo = ISNULL(@PageNo, 0) - 1; -- 0 BASED INDEX

	SET @StartRow = ((@PageNo) * @PageSize) + 1;
	SET @EndRow = (@StartRow + @PageSize) - 1;
END
*/
IF(@SortColumn = '' OR @SortColumn = NULL)
BEGIN
	SET @SortColumn = 'RowNumber'
END

IF(@SortDirection = '' OR @SortDirection = NULL)
BEGIN
	SET @SortDirection = 'ASC'
END

DECLARE @uniqueid AS INT=0;
SELECT @uniqueid = [uniqueid] 
FROM dbo.[matters] 
WHERE [Code] = @matter


DECLARE @CustomFilter TABLE
       ([DOCCLASS]     VARCHAR(2000),
        [TYPE]           VARCHAR(2000))

DECLARE @DOCCLASSFilterCount INT = 0;
DECLARE @TypeFilterCount INT = 0;
IF @ColumnNames <> ''
BEGIN
	-- Dynamic Filter By Custome Filter Settings Start

	SELECT columnName,rownumber INTO #columntbl FROM 
		(SELECT 
	LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS columnName, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as rownumber
	FROM
	(
	SELECT CAST('<XMLRoot><RowData>' + REPLACE(@ColumnNames,',','</RowData><RowData>') + '</RowData></XMLRoot>' 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('<XMLRoot><RowData>' + REPLACE(@FilterValues,'|','</RowData><RowData>') + '</RowData></XMLRoot>' 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, '<?xml version="1.0" encoding="windows-1252" ?>' + CHAR(13) + CHAR(10) + '<IMDocs/>')

IF (ISNULL(PATINDEX('%<?xml %', @IMDocs), 0) < 1)
  BEGIN
    SET @IMDocs = '<?xml version="1.0" encoding="windows-1252" ?>' + 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, '<IMDocs/>'
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('<XMLRoot><RowData>' + REPLACE((SELECT DISTINCT [DOCCLASS] from @CustomFilter WHERE [DOCCLASS] IS NOT NULL and [DOCCLASS] <>''),',','</RowData><RowData>') + '</RowData></XMLRoot>' 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('<XMLRoot><RowData>' + REPLACE((SELECT DISTINCT [TYPE] from @CustomFilter WHERE [TYPE] IS NOT NULL and [TYPE] <>''),',','</RowData><RowData>') + '</RowData></XMLRoot>' 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('<XMLRoot><RowData>' + REPLACE((SELECT DISTINCT [DOCCLASS] from @CustomFilter WHERE [DOCCLASS] IS NOT NULL and [DOCCLASS] <>''),',','</RowData><RowData>') + '</RowData></XMLRoot>' 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('<XMLRoot><RowData>' + REPLACE((SELECT DISTINCT [TYPE] from @CustomFilter WHERE [TYPE] IS NOT NULL and [TYPE] <>''),',','</RowData><RowData>') + '</RowData></XMLRoot>' 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('<XMLRoot><RowData>' + REPLACE((SELECT DISTINCT [DOCCLASS] from @CustomFilter WHERE [DOCCLASS] IS NOT NULL and [DOCCLASS] <>''),',','</RowData><RowData>') + '</RowData></XMLRoot>' 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('<XMLRoot><RowData>' + REPLACE((SELECT DISTINCT [TYPE] from @CustomFilter WHERE [TYPE] IS NOT NULL and [TYPE] <>''),',','</RowData><RowData>') + '</RowData></XMLRoot>' 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('<XMLRoot><RowData>' + REPLACE((SELECT DISTINCT [DOCCLASS] from @CustomFilter WHERE [DOCCLASS] IS NOT NULL and [DOCCLASS] <>''),',','</RowData><RowData>') + '</RowData></XMLRoot>' 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('<XMLRoot><RowData>' + REPLACE((SELECT DISTINCT [TYPE] from @CustomFilter WHERE [TYPE] IS NOT NULL and [TYPE] <>''),',','</RowData><RowData>') + '</RowData></XMLRoot>' 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('<XMLRoot><RowData>' + REPLACE((SELECT DISTINCT [DOCCLASS] from @CustomFilter WHERE [DOCCLASS] IS NOT NULL and [DOCCLASS] <>''),',','</RowData><RowData>') + '</RowData></XMLRoot>' 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('<XMLRoot><RowData>' + REPLACE((SELECT DISTINCT [TYPE] from @CustomFilter WHERE [TYPE] IS NOT NULL and [TYPE] <>''),',','</RowData><RowData>') + '</RowData></XMLRoot>' 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('<XMLRoot><RowData>' + REPLACE((SELECT DISTINCT [DOCCLASS] from @CustomFilter WHERE [DOCCLASS] IS NOT NULL and [DOCCLASS] <>''),',','</RowData><RowData>') + '</RowData></XMLRoot>' 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('<XMLRoot><RowData>' + REPLACE((SELECT DISTINCT [TYPE] from @CustomFilter WHERE [TYPE] IS NOT NULL and [TYPE] <>''),',','</RowData><RowData>') + '</RowData></XMLRoot>' 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('<XMLRoot><RowData>' + REPLACE((SELECT DISTINCT [DOCCLASS] from @CustomFilter WHERE [DOCCLASS] IS NOT NULL and [DOCCLASS] <>''),',','</RowData><RowData>') + '</RowData></XMLRoot>' 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('<XMLRoot><RowData>' + REPLACE((SELECT DISTINCT [TYPE] from @CustomFilter WHERE [TYPE] IS NOT NULL and [TYPE] <>''),',','</RowData><RowData>') + '</RowData></XMLRoot>' 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('<XMLRoot><RowData>' + REPLACE((SELECT DISTINCT [DOCCLASS] from @CustomFilter WHERE [DOCCLASS] IS NOT NULL and [DOCCLASS] <>''),',','</RowData><RowData>') + '</RowData></XMLRoot>' 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('<XMLRoot><RowData>' + REPLACE((SELECT DISTINCT [TYPE] from @CustomFilter WHERE [TYPE] IS NOT NULL and [TYPE] <>''),',','</RowData><RowData>') + '</RowData></XMLRoot>' 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('<XMLRoot><RowData>' + REPLACE((SELECT DISTINCT [DOCCLASS] from @CustomFilter WHERE [DOCCLASS] IS NOT NULL and [DOCCLASS] <>''),',','</RowData><RowData>') + '</RowData></XMLRoot>' 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('<XMLRoot><RowData>' + REPLACE((SELECT DISTINCT [TYPE] from @CustomFilter WHERE [TYPE] IS NOT NULL and [TYPE] <>''),',','</RowData><RowData>') + '</RowData></XMLRoot>' 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('<XMLRoot><RowData>' + REPLACE((SELECT DISTINCT [DOCCLASS] from @CustomFilter WHERE [DOCCLASS] IS NOT NULL and [DOCCLASS] <>''),',','</RowData><RowData>') + '</RowData></XMLRoot>' 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('<XMLRoot><RowData>' + REPLACE((SELECT DISTINCT [TYPE] from @CustomFilter WHERE [TYPE] IS NOT NULL and [TYPE] <>''),',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
																)t
																CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)) FILTERVALUETBLFnl) THEN 0
			ELSE 1 END) =0
	)
 ORDER BY 
 
	     CASE WHEN @SortDirection = 'ASC' AND @SortColumn!='RowNumber'  THEN
	     	CASE @SortColumn
	     			WHEN 'Type'   THEN convert(VARCHAR(20), UPPER(RTRIM(ISNULL([DA].[TYPE], ''))))
	     		END
	     	END,
	     	CASE WHEN @SortDirection = 'DESC'  AND @SortColumn!='RowNumber'  THEN
	     		CASE @SortColumn
	     			WHEN 'Type'   THEN convert(VARCHAR(20), UPPER(RTRIM(ISNULL([DA].[TYPE], ''))))
	     	END
	     END DESC,
	     CASE WHEN @SortDirection = 'ASC'  AND @SortColumn!='RowNumber' THEN
	     	CASE @SortColumn
	     			WHEN 'Document'   THEN RTRIM(convert(VARCHAR(2000), ISNULL([DL].[Description], ISNULL([DA].[Document], ''))))
	     		END
	     	END,
	     	CASE WHEN @SortDirection = 'DESC'  AND @SortColumn!='RowNumber' THEN
	     		CASE @SortColumn
	     			WHEN 'Document'   THEN RTRIM(convert(VARCHAR(2000), ISNULL([DL].[Description], ISNULL([DA].[Document], ''))))
	     	END
	     END DESC,
	     CASE WHEN @SortDirection = 'ASC'  AND @SortColumn!='RowNumber'  THEN
	     	CASE @SortColumn
	     			WHEN 'DocumentClassDescription'   THEN CASE WHEN convert(VARCHAR(10), ISNULL([DA].[DocClass], '')) <> ''
                 THEN RTRIM(ISNULL(DCL.[CLASSDESCRIPTION], ''))
            ELSE CASE WHEN [DCL].[ClassCode] = 'ATTNOTES'
                           THEN 'Attendance Notes'
                      WHEN [DCL].[ClassCode] = 'BILL'
                           THEN 'Bill'
                      WHEN [DCL].[ClassCode] = 'FAX'
                           THEN 'Fax'
                      WHEN [DCL].[ClassCode] = 'LEGAL'
                           THEN 'Legal'
                      WHEN [DCL].[ClassCode] = 'SCAN'
                           THEN 'Scan'
                      WHEN [DCL].[ClassCode] = 'COMPARE'
                           THEN 'Compare Document'
                      WHEN [DCL].[ClassCode] = 'DISCUSSION'
                           THEN 'Discussion'
                      WHEN [DCL].[ClassCode] = 'DOC'
                           THEN 'Document'
                      WHEN [DCL].[ClassCode] = 'E-MAIL'
                           THEN 'E-Mail'
                      WHEN [DCL].[ClassCode] = 'EVENT'
                           THEN 'Event'
                      WHEN [DCL].[ClassCode] = 'LETTER'
                           THEN 'Letter'
                      WHEN [DCL].[ClassCode] = 'MEMO'
                           THEN 'Memo'
                      WHEN [DCL].[ClassCode] = 'PAGE_ICON'
                           THEN 'Page Icon'
                      WHEN [DCL].[ClassCode] = 'TASK'
                           THEN 'Task'
                      WHEN [DCL].[ClassCode] = 'TEXT'
                           THEN 'Text File'
                      WHEN [DCL].[ClassCode] = 'WEBDOC'
                           THEN 'InfoLink Web Page'
                      ELSE ISNULL([DCL].[CLASSCODE], '') END END
	     		END
	     	END,
	     	CASE WHEN @SortDirection = 'DESC'  AND @SortColumn!='RowNumber'  THEN
	     		CASE @SortColumn
	     			WHEN 'DocumentClassDescription'   THEN CASE WHEN convert(VARCHAR(10), ISNULL([DA].[DocClass], '')) <> ''
                 THEN RTRIM(ISNULL(DCL.[CLASSDESCRIPTION], ''))
            ELSE CASE WHEN [DCL].[ClassCode] = 'ATTNOTES'
                           THEN 'Attendance Notes'
                      WHEN [DCL].[ClassCode] = 'BILL'
                           THEN 'Bill'
                      WHEN [DCL].[ClassCode] = 'FAX'
                           THEN 'Fax'
                      WHEN [DCL].[ClassCode] = 'LEGAL'
                           THEN 'Legal'
                      WHEN [DCL].[ClassCode] = 'SCAN'
                           THEN 'Scan'
                      WHEN [DCL].[ClassCode] = 'COMPARE'
                           THEN 'Compare Document'
                      WHEN [DCL].[ClassCode] = 'DISCUSSION'
                           THEN 'Discussion'
                      WHEN [DCL].[ClassCode] = 'DOC'
                           THEN 'Document'
                      WHEN [DCL].[ClassCode] = 'E-MAIL'
                           THEN 'E-Mail'
                      WHEN [DCL].[ClassCode] = 'EVENT'
                           THEN 'Event'
                      WHEN [DCL].[ClassCode] = 'LETTER'
                           THEN 'Letter'
                      WHEN [DCL].[ClassCode] = 'MEMO'
                           THEN 'Memo'
                      WHEN [DCL].[ClassCode] = 'PAGE_ICON'
                           THEN 'Page Icon'
                      WHEN [DCL].[ClassCode] = 'TASK'
                           THEN 'Task'
                      WHEN [DCL].[ClassCode] = 'TEXT'
                           THEN 'Text File'
                      WHEN [DCL].[ClassCode] = 'WEBDOC'
                           THEN 'InfoLink Web Page'
                      ELSE ISNULL([DCL].[CLASSCODE], '') END END
	     	END
	     END DESC,
		 CASE WHEN @SortDirection = 'ASC'  AND @SortColumn!='RowNumber'  THEN
	     	CASE @SortColumn
	     			WHEN 'DocumentDate'   THEN convert(DATETIME, [DA].[DATEENTERED])
	     		END
	     	END,
	     	CASE WHEN @SortDirection = 'DESC'  AND @SortColumn!='RowNumber' THEN
	     		CASE @SortColumn
	     			WHEN 'DocumentDate'   THEN convert(DATETIME, [DA].[DATEENTERED])
	     	END
	     END DESC, [DA].[TrackReference] DESC
	OFFSET @PageSize * (@PageNo - 1) ROWS
    FETCH NEXT @PageSize ROWS ONLY OPTION (RECOMPILE);
--SELECT * from #DiaryTable
--select * from @IMDocTable
  INSERT
    INTO @res
        ([ActionId],
         [TrackReference],
         [IMDocID],
         [IMClass],
         [DocClass],
         [Document],
         [DiaryDate],
         [DocumentDate],
         [EmailSent],
         [TYPE],
         [Attachments],
         [EMAIL],
         [EmailFrom],
         [EmailTo],
         [CurrentVersion],
         [IsInDocFolder],
         [IsShared],
		 [DocumentClassDescription],
		 [UniqueId],
		 [FilePath],
		 [Subject],
		 [ShortText],
		 [NAME],
		 [ProcessType],
		 [ACTIONTYPE],
		 [ActionTypeDescription],
		 [DOCUMENTSOURCE]
		 ,[TotalRecord]
		 )
  SELECT convert(INT, [DIA].[DIARYID]) AS [ActionID],
         convert(INT, [DIA].[TrackReference]) AS [TrackReference],
         convert(VARCHAR(500), ISNULL([DX].[IMDocID], '')) AS [IMDocID],
         convert(VARCHAR(20), ISNULL([DL].[IMClass], '')) AS [IMClass],
         convert(VARCHAR(10), ISNULL([DIA].[DocClass], '')) AS [DocClass],
         RTRIM(convert(VARCHAR(2000), ISNULL([DL].[Description], ISNULL([DIA].[Document], '')))) AS [Document],
         convert(DATETIME, [DIA].[DATE]) As [DiaryDate],
         convert(DATETIME, [DIA].[DATEENTERED]) AS [DocumentDate],
         convert(DATETIME, ISNULL([DIA].[TxmDate], [DIA].[Date])) As [EMAILSENT],
         convert(VARCHAR(20), UPPER(RTRIM(ISNULL([DIA].[TYPE], '')))) AS [TYPE],
        -- CASE WHEN [DIA].[ATTACHMENTS] = 'Y' THEN 'Y' ELSE 'N' END AS [Attachments],
		  (CASE WHEN     ISNULL( convert(VARCHAR(1), CASE WHEN RTRIM(ISNULL([DIA].[EMAIL], 'N')) = 'Y' THEN 'Y' ELSE 'N' END), '') = 'Y' 
                                        AND convert(INT, [DIA].[TrackReference]) = [OMS].[TrackReference]
                                       THEN (CASE WHEN [DIA].[ATTACHMENTS] = 'Y' THEN 'Y' ELSE 'N' END )
                                  WHEN     ISNULL(convert(VARCHAR(20), UPPER(RTRIM(ISNULL([DIA].[TYPE], '')))), '') = 'MSG' 
                                        AND convert(INT, [DIA].[TrackReference]) = [OMS].[TrackReference]
                                       THEN (CASE WHEN [DIA].[ATTACHMENTS] = 'Y' THEN 'Y' ELSE 'N' END)
                                  ELSE 'N' END) AS [Attachments],
         --convert(VARCHAR(1), CASE WHEN RTRIM(ISNULL([DIA].[EMAIL], 'N')) = 'Y' THEN 'Y' ELSE 'N' END) AS [EMAIL],
		 (CASE WHEN     convert(VARCHAR(1), CASE WHEN RTRIM(ISNULL([DIA].[EMAIL], 'N')) = 'Y' THEN 'Y' ELSE 'N' END) = 'Y' 
                                  AND convert(INT, [DIA].[TrackReference]) = [OMS].[TrackReference]
                                 THEN 'Y'
                            WHEN     ISNULL(convert(VARCHAR(20), UPPER(RTRIM(ISNULL([DIA].[TYPE], '')))), '') = 'MSG' 
                                  AND convert(INT, [DIA].[TrackReference]) = [OMS].[TrackReference]
                                 THEN 'Y'
                            ELSE 'N' END) AS [EMAIL],
       --  convert(VARCHAR(200), RTRIM(ISNULL([DIA].[EMAILADDRESS], ''))) AS [EMAILFROM],
	   RTRIM(ISNULL(CONVERT(VARCHAR(200), SUBSTRING(convert(VARCHAR(200), RTRIM(ISNULL([DIA].[EMAILADDRESS], ''))), 1, 200)), '')) AS [EMAILFROM],
         --RTRIM(ISNULL(CONVERT(VARCHAR(1000), [DIA].[ADDRESSTO]), '')) AS [EMAILTO],
		 RTRIM(ISNULL(CONVERT(VARCHAR(1000), SUBSTRING(RTRIM(ISNULL(CONVERT(VARCHAR(1000), [DIA].[ADDRESSTO]), '')), 1, 1000)), '')) AS [EMAILTO],
         [DV].[CurrentVersion],
         [IND].[InDocFolder],
         [SHD].[IsShared],
		
		  CASE WHEN convert(VARCHAR(10), ISNULL([DIA].[DocClass], '')) <> ''
                 THEN RTRIM(ISNULL(DCL.[CLASSDESCRIPTION], ''))
            ELSE CASE WHEN [DCL].[ClassCode] = 'ATTNOTES'
                           THEN 'Attendance Notes'
                      WHEN [DCL].[ClassCode] = 'BILL'
                           THEN 'Bill'
                      WHEN [DCL].[ClassCode] = 'FAX'
                           THEN 'Fax'
                      WHEN [DCL].[ClassCode] = 'LEGAL'
                           THEN 'Legal'
                      WHEN [DCL].[ClassCode] = 'SCAN'
                           THEN 'Scan'
                      WHEN [DCL].[ClassCode] = 'COMPARE'
                           THEN 'Compare Document'
                      WHEN [DCL].[ClassCode] = 'DISCUSSION'
                           THEN 'Discussion'
                      WHEN [DCL].[ClassCode] = 'DOC'
                           THEN 'Document'
                      WHEN [DCL].[ClassCode] = 'E-MAIL'
                           THEN 'E-Mail'
                      WHEN [DCL].[ClassCode] = 'EVENT'
                           THEN 'Event'
                      WHEN [DCL].[ClassCode] = 'LETTER'
                           THEN 'Letter'
                      WHEN [DCL].[ClassCode] = 'MEMO'
                           THEN 'Memo'
                      WHEN [DCL].[ClassCode] = 'PAGE_ICON'
                           THEN 'Page Icon'
                      WHEN [DCL].[ClassCode] = 'TASK'
                           THEN 'Task'
                      WHEN [DCL].[ClassCode] = 'TEXT'
                           THEN 'Text File'
                      WHEN [DCL].[ClassCode] = 'WEBDOC'
                           THEN 'InfoLink Web Page'
                      ELSE ISNULL([DCL].[CLASSCODE], '') END END AS [DocumentClassDescription],
					  @uniqueid As uniqueid,
					  CASE WHEN [UNC].[UNC] IS NULL THEN RTRIM(ISNULL([DIA].[FILEPATH], ''))
							ELSE [UNC].[UNC] + SUBSTRING(RTRIM(ISNULL([DIA].[FILEPATH], '')), 3, LEN(RTRIM(ISNULL([DIA].[FILEPATH], ''))) - 2) END AS [FILEPATH],
					--	[RES].[EMAILSENT],
						   RTRIM(ISNULL(CONVERT(VARCHAR(1000),
												SUBSTRING(RTRIM(ISNULL(CONVERT(VARCHAR(2000),
																			   [DIA].[SUBJECT]),'')),
														  1,
														  1000)),
										'')) AS [SUBJECT],
					   RTRIM(ISNULL(CONVERT(VARCHAR(120),
											SUBSTRING(RTRIM(ISNULL(CONVERT(VARCHAR(120),
																		   [DIA].[DisplayText]),'')),
													  1,
													  120)),
									'')) AS [ShortText],
						RTRIM(ISNULL([DIA].[NAME], '')) AS [NAME],
      
       RTRIM(ISNULL([DIA].[PROCESSTYPE], '')) AS [PROCESSTYPE],
       RTRIM(ISNULL([DIA].[ACTIONTYPE], '')) AS [ACTIONTYPE],
       CASE WHEN [DIA].[ACTIONTYPE] = 'A'
                 THEN 'Action'
            WHEN [DIA].[ACTIONTYPE] = 'N'
                 THEN 'Note'
            WHEN [DIA].[ACTIONTYPE] = 'P'
                 THEN 'Appointment'
            WHEN [DIA].[ACTIONTYPE] = 'R'
                 THEN 'Reminder'
            WHEN [DIA].[ACTIONTYPE] = 'E'
                 THEN 'Email'
            WHEN [DIA].[ACTIONTYPE] = 'T'
                 THEN 'Phone Message'
            WHEN [DIA].[ACTIONTYPE] = 'D'
                 THEN 'Dictation'
            WHEN [DIA].[ACTIONTYPE] = 'U'
                 THEN 'Undertaking'
            WHEN [DIA].[ACTIONTYPE] = 'S'
                 THEN 'Statute Date'
            WHEN [DIA].[ACTIONTYPE] = 'C'
                 THEN 'Critical Date'
            WHEN [DIA].[ACTIONTYPE] = 'O'
                 THEN 'Court Date'
            WHEN [DIA].[ACTIONTYPE] = 'M'
                 THEN 'Scanned Post/Mail'
            WHEN RTRIM(ISNULL([DIA].[ACTIONTYPE], '')) = ''
                 THEN CASE WHEN convert(VARCHAR(1), CASE WHEN RTRIM(ISNULL([DIA].[EMAIL], 'N')) = 'Y' THEN 'Y' ELSE 'N' END) = 'Y'
                                THEN 'Email'
                           WHEN convert(VARCHAR(500), ISNULL([DX].[IMDocID], '')) <> ''
                                THEN 'IManage document'
                           ELSE 'Unknown' END

            ELSE 'Action Type: ' + [DIA].[ACTIONTYPE] END AS [ActionTypeDescription],
       RTRIM(ISNULL([DIA].[SOURCE], CASE WHEN [DIA].[ACTIONID] IS NULL THEN 'IManage' ELSE '' END)) AS [DOCUMENTSOURCE]
	   ,Count(1) over() [TotalRecord]
    FROM #DiaryTable [DIA]
                    CROSS APPLY (SELECT CASE WHEN COUNT(1) = 0
                                                  THEN CONVERT(BIT, 0)
                                             ELSE CONVERT(BIT, 1) END AS [InDocFolder]
                                   FROM [dbo].[DocFolderDocuments] [DFD]
                                  WHERE [DFD].[TrackReference] = [DIA].[TrackReference]) [IND]
                    CROSS APPLY (SELECT CASE WHEN COUNT(1) = 0
                                                  THEN CONVERT(BIT, 0)
                                             ELSE CONVERT(BIT, 1) END AS [IsShared]
                                   FROM [dbo].[FileSharingDocuments] [FSD]
                                        INNER JOIN [dbo].[FileSharingMapping] [FSM]
                                                   INNER JOIN [dbo].[FileSharingDocFolders] [FSDF]
                                                           ON [FSDF].[DocFolderID] = [FSM].[FileSharingDocFoldersID]
                                                          AND [FSDF].[Shared] = 1
                                                ON [FSM].[id] = [FSD].[FileSharingMappingID]
                                  WHERE [FSD].[TrackReference] = [DIA].[TrackReference]) [SHD]
                 
         CROSS APPLY (SELECT ISNULL(MAX([DAV].[Version]), 0) + 1 AS [CurrentVersion]
                        FROM [dbo].[DiaryAttachmentVersioning] [DAV]
                       WHERE TRACKREFERENCE = [DIA].[TRACKREFERENCE]) [DV]
         LEFT OUTER JOIN [dbo].[DAIMXRef] [DX]
                         LEFT OUTER JOIN @IMDocTable [DL]
                                      ON [DL].[IMDocID] = [DX].[IMDocID]
                      ON [DX].[TrackReference] = [DIA].[TrackReference]
		LEFT OUTER JOIN [dbo].[DocumentClasses] DCL
						ON convert(VARCHAR(10), ISNULL([DIA].[DocClass], '')) <> ''
						AND DCL.[CLASSCODE] = CASE WHEN convert(VARCHAR(10), ISNULL([DIA].[DocClass], '')) = '' THEN convert(VARCHAR(20), ISNULL([DL].[IMClass], '')) 
													 ELSE convert(VARCHAR(10), ISNULL([DIA].[DocClass], ''))
													 END
		LEFT OUTER JOIN [dbo].[UNCAlias] [UNC]
                                    ON SUBSTRING([DIA].[FilePath], 2, 1) = ':'
                                   AND [UNC].[Drive] = SUBSTRING([DIA].[FilePath], 1, 1)
		OUTER APPLY (  SELECT TOP 1
                             [MST].[TrackReference] AS [TrackReference],
                             [MST].[Type] AS [Type]
                        FROM #DiaryTable [MST]
                       WHERE [MST].[diaryid] = [DIA].[ActionID] --AND [MST].[TrackReference] =  [DA].[TrackReference]
                    ORDER BY CASE WHEN [MST].Type = 'MSG'
                                       THEN 0
                                  ELSE 1 END,
                             [MST].[TrackReference]) [OMS]
   
END
/*
INSERT
  INTO @classes
      ([KeyhouseClass],
       [IMClass],
       [ClassCode],
       [ClassDescription])
SELECT [CLS].[KeyhouseClass],
       [CLS].[IMClass],
       [CLS].[ClassCode],
       CASE WHEN [CLS].[KeyhouseClass] = 1
                 THEN RTRIM(ISNULL(DCL.[CLASSDESCRIPTION], ''))
            ELSE CASE WHEN [CLS].[ClassCode] = 'ATTNOTES'
                           THEN 'Attendance Notes'
                      WHEN [CLS].[ClassCode] = 'BILL'
                           THEN 'Bill'
                      WHEN [CLS].[ClassCode] = 'FAX'
                           THEN 'Fax'
                      WHEN [CLS].[ClassCode] = 'LEGAL'
                           THEN 'Legal'
                      WHEN [CLS].[ClassCode] = 'SCAN'
                           THEN 'Scan'
                      WHEN [CLS].[ClassCode] = 'COMPARE'
                           THEN 'Compare Document'
                      WHEN [CLS].[ClassCode] = 'DISCUSSION'
                           THEN 'Discussion'
                      WHEN [CLS].[ClassCode] = 'DOC'
                           THEN 'Document'
                      WHEN [CLS].[ClassCode] = 'E-MAIL'
                           THEN 'E-Mail'
                      WHEN [CLS].[ClassCode] = 'EVENT'
                           THEN 'Event'
                      WHEN [CLS].[ClassCode] = 'LETTER'
                           THEN 'Letter'
                      WHEN [CLS].[ClassCode] = 'MEMO'
                           THEN 'Memo'
                      WHEN [CLS].[ClassCode] = 'PAGE_ICON'
                           THEN 'Page Icon'
                      WHEN [CLS].[ClassCode] = 'TASK'
                           THEN 'Task'
                      WHEN [CLS].[ClassCode] = 'TEXT'
                           THEN 'Text File'
                      WHEN [CLS].[ClassCode] = 'WEBDOC'
                           THEN 'InfoLink Web Page'
                      ELSE ISNULL([CLS].[CLASSCODE], '') END END AS [ClassDescription]
  FROM (SELECT DISTINCT
               1 AS [KeyhouseClass],
               0 AS [IMClass],
               CONVERT(VARCHAR(20), [RES].[DocClass]) AS [ClassCode]
          FROM @res [RES]
         WHERE ISNULL([RES].[DocClass], '') <> ''
        UNION
        SELECT DISTINCT
               0 AS [KeyhouseClass],
               1 AS [IMClass],
               CONVERT(VARCHAR(20), [RES].[IMClass]) AS [ClassCode]
          FROM @res [RES]
         WHERE ISNULL([RES].[IMClass], '') <> '') [CLS]
       LEFT OUTER JOIN [dbo].[DocumentClasses] DCL
                    ON [CLS].[KeyhouseClass] = 1
                   AND DCL.[CLASSCODE] = [CLS].[ClassCode]
*/
/*
UPDATE [RES]
   SET [RES].[EMAIL] = CASE WHEN (    ISNULL([RES].[EMAIL], '') = 'Y'
                                  AND [RES].[TrackReference] = [OMS].[TrackReference])
                                 THEN 'Y'
                            WHEN (    ISNULL([RES].[TYPE], '') = 'MSG'
                                  AND [RES].[TrackReference] = [OMS].[TrackReference])
                                 THEN 'Y'
                            ELSE 'N' END,
       [RES].[Attachments] = CASE WHEN (    ISNULL([RES].[EMAIL], '') = 'Y'
                                        AND [RES].[TrackReference] = [OMS].[TrackReference])
                                       THEN [RES].[Attachments]
                                  WHEN (    ISNULL([RES].[TYPE], '') = 'MSG'
                                        AND [RES].[TrackReference] = [OMS].[TrackReference])
                                       THEN [RES].[Attachments]
                                  ELSE 'N' END
  FROM @res [RES]
       OUTER APPLY (  SELECT TOP 1
                             [MST].[TrackReference] AS [TrackReference],
                             [MST].[Type] AS [Type]
                        FROM [dbo].[diaryattachments] [MST]
                       WHERE [MST].[diaryid] = [RES].[ActionID]
                    ORDER BY CASE WHEN [MST].Type = 'MSG'
                                       THEN 0
                                  ELSE 1 END,
                             [MST].[TrackReference]) [OMS]
 WHERE [RES].[ACTIONID] IS NOT NULL
 */
/*
SELECT [RES].[id],
       CONVERT(VARCHAR(15),dbo.KAAS_FNConvertUTCDateToTargetTimeZone([RES].[DiaryDate], @LoggedInHandler), 23) AS [DATE],
       ISNULL([RES].[ActionID], 0) AS [ACTIONID],
       CONVERT(VARCHAR(15), dbo.KAAS_FNConvertUTCDateToTargetTimeZone([RES].[DocumentDate], @LoggedInHandler), 23)  AS [DocumentDate],
       [RES].[IMDocID],
       [RES].[IMClass],
       [RES].[DocumentClassDescription],
       [RES].[DocClass],
       [RES].[Document],
       ISNULL([RES].[TrackReference], 0) AS [TRACKREFERENCE],
       [RES].[TYPE] AS [TYPE],
       [RES].[Attachments] AS [Attachments],
       [RES].[EMAIL],
       RTRIM(ISNULL(CONVERT(VARCHAR(200), SUBSTRING(ISNULL([DIA].[EMAILADDRESS], [RES].[EMAILFROM]), 1, 200)), '')) AS [EMAILADDRESS],
       RTRIM(ISNULL(CONVERT(VARCHAR(1000), SUBSTRING(ISNULL([DIA].[ADDRESSTO], [RES].[EMAILTO]), 1, 1000)), '')) AS [ADDRESSTO],
       [RES].[EMAILSENT],
       RTRIM(ISNULL(CONVERT(VARCHAR(1000),
                            SUBSTRING(RTRIM(ISNULL(CONVERT(VARCHAR(2000),
                                                           [DIA].[SUBJECT]),'')),
                                      1,
                                      1000)),
                    '')) AS [SUBJECT],
       RTRIM(ISNULL(CONVERT(VARCHAR(120),
                            SUBSTRING(RTRIM(ISNULL(CONVERT(VARCHAR(120),
                                                           [DIA].[DisplayText]),'')),
                                      1,
                                      120)),
                    '')) AS [ShortText],
       RTRIM(ISNULL([DAT].[NAME], '')) AS [NAME],
       CASE WHEN [UNC].[UNC] IS NULL THEN RTRIM(ISNULL([DAT].[FILEPATH], ''))
                 ELSE [UNC].[UNC] + SUBSTRING(RTRIM(ISNULL([DAT].[FILEPATH], '')), 3, LEN(RTRIM(ISNULL([DAT].[FILEPATH], ''))) - 2) END AS [FILEPATH],
       RTRIM(ISNULL([DIA].[PROCESSTYPE], '')) AS [PROCESSTYPE],
       RTRIM(ISNULL([DIA].[ACTIONTYPE], '')) AS [ACTIONTYPE],
       CASE WHEN [DIA].[ACTIONTYPE] = 'A'
                 THEN 'Action'
            WHEN [DIA].[ACTIONTYPE] = 'N'
                 THEN 'Note'
            WHEN [DIA].[ACTIONTYPE] = 'P'
                 THEN 'Appointment'
            WHEN [DIA].[ACTIONTYPE] = 'R'
                 THEN 'Reminder'
            WHEN [DIA].[ACTIONTYPE] = 'E'
                 THEN 'Email'
            WHEN [DIA].[ACTIONTYPE] = 'T'
                 THEN 'Phone Message'
            WHEN [DIA].[ACTIONTYPE] = 'D'
                 THEN 'Dictation'
            WHEN [DIA].[ACTIONTYPE] = 'U'
                 THEN 'Undertaking'
            WHEN [DIA].[ACTIONTYPE] = 'S'
                 THEN 'Statute Date'
            WHEN [DIA].[ACTIONTYPE] = 'C'
                 THEN 'Critical Date'
            WHEN [DIA].[ACTIONTYPE] = 'O'
                 THEN 'Court Date'
            WHEN [DIA].[ACTIONTYPE] = 'M'
                 THEN 'Scanned Post/Mail'
            WHEN RTRIM(ISNULL([DIA].[ACTIONTYPE], '')) = ''
                 THEN CASE WHEN [RES].[EMAIL] = 'Y'
                                THEN 'Email'
                           WHEN [RES].[IMDocID] <> ''
                                THEN 'IManage document'
                           ELSE 'Unknown' END

            ELSE 'Action Type: ' + [DIA].[ACTIONTYPE] END AS [ActionTypeDescription],
       RTRIM(ISNULL([DAT].[SOURCE], CASE WHEN [DIA].[ACTIONID] IS NULL THEN 'IManage' ELSE '' END)) AS [DOCUMENTSOURCE],
       [RES].[CurrentVersion],
       [RES].[IsInDocFolder],
       [RES].[IsShared],
	   [RES].UniqueId
  FROM @res [RES]
       LEFT OUTER JOIN [dbo].[DiaryAttachments] [DAT]
                       INNER JOIN [dbo].[diary] [DIA]
                               ON [DIA].[ACTIONID] = [DAT].[DIARYID]
                       LEFT OUTER JOIN [dbo].[UNCAlias] [UNC]
                                    ON SUBSTRING([DAT].[FilePath], 2, 1) = ':'
                                   AND [UNC].[Drive] = SUBSTRING([DAT].[FilePath], 1, 1)
                    ON [DAT].[TRACKREFERENCE] = [RES].[TrackReference]
	
       --2018-02-26 START
       --LEFT OUTER JOIN @classes [CLS]
       --             ON [CLS].[KeyhouseClass] = CASE WHEN ISNULL([RES].[IMClass], '') = '' THEN 1 ELSE 0 END
       --            AND [CLS].[IMClass] = CASE WHEN ISNULL([RES].[IMClass], '') = '' THEN 0 ELSE 1 END
       --            AND [CLS].[ClassCode] = CASE WHEN ISNULL([RES].[IMClass], '') = '' THEN [RES].[DocClass] ELSE [RES].[IMClass] END
       LEFT OUTER JOIN @classes [CLS]
                    ON [CLS].[KeyhouseClass] = CASE WHEN ISNULL([RES].[DocClass], '') = '' THEN 0 ELSE 1 END
                   AND [CLS].[IMClass] = CASE WHEN ISNULL([RES].[DocClass], '') = '' THEN 1 ELSE 0 END
                   AND [CLS].[ClassCode] = CASE WHEN ISNULL([RES].[DocClass], '') = '' THEN [RES].[IMClass] ELSE [RES].[DocClass] END
       --2018-02-26 END
	--WHERE [RES].id between  (@PageNo * @PageSize) + 1 and ((@PageNo * @PageSize) + 1 + @PageSize) - 1
	ORDER BY ID
	*/
	SELECT 
		[id] AS [Id],
       CONVERT(VARCHAR(15),dbo.KAAS_CP_FNConvertUTCDateToTargetTimeZone([DiaryDate], @LoginId), 23)  AS [DATE],
       [ACTIONID],
       CONVERT(VARCHAR(15), dbo.KAAS_CP_FNConvertUTCDateToTargetTimeZone([DocumentDate], @LoginId), 23) AS [DocumentDate],
       [IMDocID],
       [IMClass],
       [DocumentClassDescription],
       [DocClass],
       [Document],
       [TRACKREFERENCE] AS [DocTrackReference],
       [TYPE] AS [DocType],
       [Attachments],
       [EMAIL],
       [EMAILFROM] AS [EMAILADDRESS],
       [EMAILTO] AS [ADDRESSTO],
       [EMAILSENT],
       [SUBJECT],
       [ShortText],
       [NAME],
       [FILEPATH] AS [FilePath],
       [PROCESSTYPE],
       [ACTIONTYPE],
       [ActionTypeDescription],
       [DOCUMENTSOURCE],
       [CurrentVersion],
       [IsInDocFolder],
       [IsShared],
	   UniqueId

	from @res
	ORDER BY TrackReference DESC
--	WHERE ID between  (@PageNo * @PageSize) + 1 and ((@PageNo * @PageSize) + 1 + @PageSize) - 1
--	ORDER BY ID
	--OFFSET @PageSize * (@PageNo - 1) ROWS
   -- FETCH NEXT @PageSize ROWS ONLY OPTION (RECOMPILE);
--OPTION (KEEPFIXED PLAN)
/*	IF Exists(SELECT TOP 1 TotalRecord FROM @res)
		BEGIN
		SELECT TOP 1 ISNULL(TotalRecord,0) FROM @res
		END
	ELSE
		BEGIN
		SELECT 0 as TotalRecord
		END
*/
		SELECT @TOTALRECORDS AS TotalRecord
/*	
	SELECT top 1 count(1) Over()	AS [TotalRecord]
			
	FROM [dbo].[diary] [DIA]
		INNER JOIN [dbo].[DiaryAttachments] [DAT]
				ON [DAT].[DiaryID] = [DIA].[ActionID]
	WHERE [DIA].[CASECODE] = @matter
*/	
	 IF ISNULL(@DocFolderID, 0) <> 0
    BEGIN
	    SELECT [seq],
		[id] AS [FolderId],
		[parent],
		[foldername],
		[foldericon],
		[folderimage],
		[foldershared],
		[folderisours],
		[documentcount] AS [DocumentCount],
		999 + TRY_CAST(seq AS INT) AS [Id]
		FROM [dbo].[KAAS_FN_GetDocFolders](@matter, @DocFolderID, 1, 1) WHERE parent <> -1
    END
  ELSE
    BEGIN
      SELECT TOP 0
             CONVERT(INT, 0) AS [seq],
             CONVERT(INT, 0) AS [FolderId],
             CONVERT(INT, 0) AS [parent],
             CONVERT(VARCHAR(500), '') AS [foldername],
             CONVERT(INT, 0) AS [foldericon],
             CONVERT(VARBINARY(MAX), NULL) AS [folderimage],
             CONVERT(BIT, 0) AS [foldershared],
             CONVERT(BIT, 0) AS [folderisours],
             CONVERT(INT, 0) AS [DocumentCount]
    END

END
GO

IF OBJECT_ID(N'KAAS_CP_GetMatterDetailsForClient',N'P')IS NOT NULL
	DROP PROCEDURE [dbo].[KAAS_CP_GetMatterDetailsForClient]
GO

CREATE PROCEDURE
	[dbo].[KAAS_CP_GetMatterDetailsForClient]
	(@LoginId     BIGINT,
	 @CaseCode    VARCHAR(15) = NULL)

AS
/*******************************************************************************************************
*                                                                                                      *
* Stored Procedure Name : [dbo].[KAAS_CP_GetMatterDetailsForClient]									   *
* Description: To get matter details for client portal												   *
*                                                                                                      *
* Modification History:                                                                                *
* 2021-06-09    Aakif        Created																   *
* 2021-06-11    Aakif        Included option to serach with case code. User TimeZone conversion		   *
*							 and label names for User1, User2, User3 and Your Ref					   *
* 2021-06-22	Aakif		 Included column for matter comment and fixed null data issue for status   *
 *******************************************************************************************************/

BEGIN
	SELECT *,
	
		CONVERT(NVARCHAR(15), dbo.KAAS_CP_FNConvertUTCDateToTargetTimeZone(
		dbo.KAAS_CP_GetLastOrNextActionDate(MatterCode, 1, 0), @LoginId), 23)	AS [LastActionDate],

		CONVERT(NVARCHAR(15), dbo.KAAS_CP_FNConvertUTCDateToTargetTimeZone(
		dbo.KAAS_CP_GetLastOrNextActionDate(MatterCode, 0, 1), @LoginId), 23)	AS [LastMilestoneDate],

		CONVERT(NVARCHAR(15), dbo.KAAS_CP_FNConvertUTCDateToTargetTimeZone(
		dbo.KAAS_CP_GetLastOrNextActionDate(MatterCode, 0, 0), @LoginId), 23)	AS [NextActionDate],

		dbo.KAAS_CP_GetLastOrNextActionText(MatterCode, 1, 0)					AS LastActionDescription,
		dbo.KAAS_CP_GetLastOrNextActionText(MatterCode, 0, 1)					AS LastMilestoneDescription,
		dbo.KAAS_CP_GetLastOrNextActionText(MatterCode, 0, 0)					AS NextActionDescription
	FROM
	(
		SELECT 
			CON.[Name]					AS ClientName,
			CON.[Address]				AS ClientAddress,
			MAT.[Code]					AS MatterCode,
			MAT.[Description]			AS MatterDescription,
			SM.[HandlerName]			AS CaseHandler,
			RTRIM(CTL.[USERPROMPT1])	AS User1Label,
			MAT.[User1]					AS User1,
			RTRIM(CTL.[USERPROMPT2])	AS User2Label,
			MAT.[User2]					AS User2,
			RTRIM(CTL.[USERPROMPT3])	AS User3Label,
			MAT.[User3]					AS User3,
			RTRIM(CTL.[YourRef])		AS YourRefLabel,
			MAT.[YourRef]				AS YourRef,
			MAT.[Started]				AS MatterStartDate,
			MAT.[CloseDate]				AS MatterCloseDate,	
			MAT.[Status]				AS MatterStatusCode,
			RTRIM(SC.[DESCRIPTION])		AS MatterStatus,
			MAT.[OriginalDebt]			AS MatterOriginalDebt,
			MAT.Comment					AS MatterComments
		FROM
			 [Contacts] CON
		JOIN [matters] MAT
			ON CON.Code = MAT.ClientCode
		JOIN [SearchMatters] SM
			ON SM.Code = MAT.Code
		LEFT JOIN [StatusCodes] SC
			ON SC.CODE = MAT.[Status]
		CROSS JOIN [control] CTL
		WHERE
			MAT.Code IN (
			SELECT MatterCode FROM dbo.KAAS_CP_TFGetMatterCodeForClient(@LoginId))
			AND (ISNULL(@CaseCode, '') = '' OR MAT.Code = @CaseCode)
	 ) FIN

END

GO






IF OBJECT_ID(N'KAAS_CP_GetMatterDocFolderTree', N'P') IS NOT NULL
BEGIN
    DROP PROCEDURE [dbo].[KAAS_CP_GetMatterDocFolderTree]
END
GO

CREATE PROCEDURE [dbo].[KAAS_CP_GetMatterDocFolderTree]      
(  
 @matter      VARCHAR(20)  
)  
AS      
/********************************************************************************************************
* Gets the folder structure for a Matter. Creates it if it doesn't exist, use the CasePlan folder       * 
* structure as a template. Create it too, if it doesn't exist.		   								    *
*                                                                                                       *
* Stored Procedure Name : [dbo].[KAAS_GetMatterDocFolderTree]										    *
* Copied from			: [dbo].[ky_NETSPGetMatterDocFolderTree]									    *
*                                                                                                       *
* Modification History:                                                                                 *
* 2019-04-23    Vinodhan K        Created                                                               *
* 2019-06-19    Vinodhan K        Removed the condition to check whether UseDocFolders is set as True   *
*                                 or False. For now it wil return the data even when it is set as False *
* 2021-06-17    Aakif	          Created                                                               *
 *******************************************************************************************************/    
BEGIN    
  DECLARE @WKCODE CHAR(4)      
  DECLARE @MatterCode VARCHAR(20)      
  DECLARE @folderid INT    
      
  SELECT TOP 1      
         @MatterCode = [SM].[CODE],      
         @WKCODE = [TMP].[WKCODE]      
    FROM [dbo].[SearchMatters] SM      
         INNER JOIN [dbo].[CaseMaster] CSM      
                 ON [CSM].[CSCODE] = SM.[Code]      
         LEFT OUTER JOIN [dbo].[Templates] [TMP]      
                      ON [TMP].[WKCODE] = [CSM].[CSWKTCODE]      
   WHERE [CSM].[CSCODE] = @matter      
      
  IF @MatterCode IS NULL      
    BEGIN      
      -- Dummy statement      
      SELECT TOP 0      
             CONVERT(INT, 0) AS [id],      
             CONVERT(INT, 0) AS [parent],      
             CONVERT(VARCHAR(500), '') AS [foldername],      
             CONVERT(INT, 0) AS [foldericon]      
      
      RETURN      
    END      
      
  SELECT TOP 1      
         @folderid = MDF.[DocFolderID]      
    FROM [dbo].[MatterDocFolders] MDF      
   WHERE [MDF].[MatterCode] = @MatterCode      
      
  IF @folderid IS NULL      
    BEGIN      
      DECLARE @DefaultRootFolderName VARCHAR(500)      
      
      SELECT @DefaultRootFolderName = CONVERT(VARCHAR(500), [SET].[KeyValue])      
        FROM [dbo].[Settings] [SET]      
       WHERE [SET].[KeyName] = 'DocFolderDefaultRootName'      
      
      SET @DefaultRootFolderName = RTRIM(ISNULL(@DefaultRootFolderName, 'Documents'))      
      
      IF @WKCODE IS NULL      
        BEGIN      
          EXEC @folderid = [dbo].[KAAS_SaveDocFolderTree] '<folders><folder id="1" newfolder="True" deletefolder="False" foldername="Documents" parent="0" foldericon="1"/></folders>'      
      
          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] '<folders><folder id="1" newfolder="True" deletefolder="False" foldername="Documents" parent="0" foldericon="1"/></folders>'      
      
UPDATE [DF]      
                 SET [DF].[foldername] = @DefaultRootFolderName      
                FROM [dbo].[DocFolders] [DF]       
               WHERE [DF].[id] = @folderid      
      
              INSERT      
                INTO [dbo].[CasePlanDocFolders]      
                    ([WKCode],      
                     [DocFolderID])      
              SELECT @WKCODE,      
                     @folderid      
            END      
      
          DECLARE @DUP TABLE      
                 ([id] INT)      
      
          INSERT INTO @DUP      
          EXEC @folderid = [dbo].[KAAS_DuplicateDocFolderTree] @folderid      
        END      
      
      INSERT      
        INTO [dbo].[MatterDocFolders]      
            ([MatterCode],      
             [DocFolderID])      
      SELECT @MatterCode,      
             @folderid      
    END    
      
 SELECT  
		[seq],
		[id] AS [FolderId],
		[parent],
		[foldername],
		[foldericon],
		[folderimage],
		[foldershared],
		[folderisours],
		[documentcount] AS [DocumentCount],
		999 + TRY_CAST(seq AS INT) AS [Id]
 FROM [dbo].[KAAS_FN_GetDocFolders](@matter, @folderid, 1, 1)     
END
GO
IF OBJECT_ID(N'KAAS_CP_GetSettings',N'P')IS NOT NULL
	DROP PROCEDURE [dbo].[KAAS_CP_GetSettings]
GO
CREATE PROCEDURE [dbo].[KAAS_CP_GetSettings]
AS
/*******************************************************************************************************
*                                                                                                      *
* Stored Procedure Name : [dbo].[KAAS_CP_GetSettings]	      										   *
* Description: To get settings for client portal													   *
*                                                                                                      *
* Modification History:                                                                                *
* 2021-05-27    Aakif        Created																   *
* 2021-05-31    Aakif        Included column for two factor authentication							   *
 *******************************************************************************************************/

BEGIN
	SELECT
		[TenantLogo]						   AS [TenantLogo], 
		[TenantLogoType]					   AS [TenantLogoType],
		[UPEKey]							   AS [UPEKey],
		ISNULL([IsTwoFactorEnabled], 0)		   AS [IsTwoFactorEnabled],
		ISNULL([IsLockOut_Attempt], 0)		   AS [IsLockOutAttempt],
		ISNULL([LockoutDurationInMins], 10)    AS [LockoutLimitInMin],
		ISNULL([PasswordResetNotification], 0) AS [PasswordResetNotification]
	FROM
		[dbo].[ClientPortalSetting]
END

GO






IF OBJECT_ID(N'KAAS_CP_GetUserLoginDetails',N'P')IS NOT NULL
	DROP PROCEDURE [dbo].[KAAS_CP_GetUserLoginDetails]
GO
CREATE PROCEDURE 
	 [dbo].[KAAS_CP_GetUserLoginDetails]
	(@EmailId VARCHAR(200))
AS 
/*******************************************************************************************************
*                                                                                                      *
* Stored Procedure Name : [dbo].[KAAS_CP_GetUserLoginDetails]      									   *
* Description: To get login details for specified email												   *
*                                                                                                      *
* Modification History:                                                                                *
* 2021-05-27    Aakif        Created                                                                   *
* 2021-05-31    Aakif        Included column for two factor authentication							   *
 *******************************************************************************************************/

BEGIN
	SELECT
		  [ClientLoginId]							AS	 [ClientLoginId],
		  [Email]									AS	 [Email],
		  [PhoneNumber]								AS	 [PhoneNumber],
		  [Name]									AS	 [Name],
		  [ClientDefaulTimeZone]					AS	 [ClientDefaulTimeZone],
		  [AccessCode]								AS	 [AccessCode],
		  [AccessCodeStatus]						AS	 [AccessCodeStatus],
		  [PasswordHash]							AS	 [PasswordHash],
		  ISNULL([NoOfAttempt],0)					AS   [NoOfAttempt],
		  [LastAccessedDateTime]					AS	 [LastAccessedDateTime],
		  [LockedOutEndDate]						AS	 [LockedOutEndDate],
		  [SecurityStamp]							AS	 [SecurityStamp],
		  ISNULL([IsTwoFactorEnabled],0)			AS   [IsTwoFactorEnabled],
		  [IsActiveLogin]							AS   [IsActiveLogin],
		  [IsFirstlogin]							AS   [IsFirstlogin],
		  [IsDeleted]								AS	 [IsDeleted],
		  [IsAdmin]									AS	 [IsAdmin],
		  [IsEmailConfirmed]						AS	 [IsEmailConfirmed],
		  [IsPhoneNumberConfirmed]					AS	 [IsPhoneNumberConfirmed],
		  [EmailActivationToken]					AS	 [EmailActivationToken],
		  [ConcurrencyStamp]						AS   [ConcurrencyStamp],
		  [PasswordResetOTP]						AS   [PasswordResetOTP],
		  [PasswordResetOTPCreationTime]			AS   [PasswordResetOTPCreationTime],  
		  [PasswordResetOTPExpirationTime]			AS   [PasswordResetOTPExpirationTime],
		  [OTPAccessToken]							AS	 [OTPAccessToken],
		  [OTPAccessTokenCreationTime]				AS	 [OTPAccessTokenCreationTime],
		  [OTPAccessTokenExpirationTime]			AS   [OTPAccessTokenExpirationTime],
		  [PasswordResetOTPAttemptCount]			AS	 [PasswordResetOTPAttemptCount],
		  [PasswordResetLockOutEndDate]				AS	 [PasswordResetLockOutEndDate],
		  [IsPasswordResetOTPVerified]				AS	 [IsPasswordResetOTPVerified],
		  [IsUserVerifiedEmail]						AS	 [IsUserVerifiedEmail],
		  [VerifiedByAdminUserId]					AS	 [VerifiedByAdminUserId],
		  [EmailActivationTokenCreatedTime]			AS   [EmailActivationTokenCreatedTime],
		  [EmailActivationTokenExpirationTime]		AS   [EmailActivationTokenExpirationTime],
		  [IsAdminVerifiedUserLogin]				AS   [IsAdminVerifiedUserLogin],
		  [SecretQuestion]							AS   [SecretQuestion],
		  [Answer]									AS	 [Answer]
	FROM
		  [dbo].[ClientLogin]
	WHERE 
		  [Email] = @EmailId
END

GO
IF OBJECT_ID(N'KAAS_CP_ListCaseContacts',N'P')IS NOT NULL
	DROP PROCEDURE [dbo].[KAAS_CP_ListCaseContacts]
GO
  
CREATE PROCEDURE  
       [dbo].[KAAS_CP_ListCaseContacts]  
      (@CaseCode	VARCHAR(20),  
       @ForEmail	BIT           = 1,  
       @Number		INT           = 0,  
       @Status		VARCHAR(1)	  = '',
	   @SearchText	VARCHAR(4000) = NULL
	   )  
AS  
/*************************************************************************************************************  
 * Stored Procedure Name: [KAAS_CP_ListCaseContacts]  
 * Copied From			: [ky_NETSPListCaseContacts]  
 *
 * List Case Contacts for Outlook Add-in  
 *  
 *   *******************  
 *   *                 *  
 *   * PLEASE NOTE:    * If you make any changes to this procedure's parameters, please inform Pino Carafa as  
 *   * ============    * this will impact the Outlook Add-in!  
 *   *                 *  
 *   *******************  
 *  
 * Modification History  
 * 2015-12-03    Pino              Make it usable for Framework, too  
 * 2016-07-21    Suriya M          KEYD-3783 - When you add a new strong room Deed file to a case, the Retired  
 *                                 Associates are showing in the "From Whom" & "To Whom" fields of "DEED" tab.  
 * 2016-07-22    Pino              Set default value for Status to ensure Outlook Add-in remains unaffected  
 * 2018-05-01    Pino Carafa       KEYD-4573 Add Secondary Email  
 * 2019-12-04    Pino Carafa       Return CC CONTYPE rather than CAN TYPE in first query  
 * 2019-12-16    Pino Carafa       Return Occupation  
 * 2020-06-03    John Ginnane      KEYD-6531 - Added mobile no to output  
 * 2020-07-14    John Ginnane      KEYD-6531 - Added SourceTable and SourceCode to output  
 * 2020-08-25    Pino Carafa       KEYD-6753 - Added Title to output  
 * 2020-09-24    Arun Vasu         KEYD-6648 - Added Work phone number to output  
 * 2021-03-11    Arun Vasu         Have get the FeeEarner, Partner label from setting table ref: 11634  
 * 2021-03-12    Arun Vasu         Changed the logic the get the handler label values and set for handlers label ref: 11634  
 * 2021-06-18    Aakif			   Created
 * 2021-06-22    Aakif			   Fixed issue to fetch published contact
 *************************************************************************************************************/  
BEGIN  
SET NOCOUNT ON  
  DECLARE @FromWhom       VARCHAR(10)  
  DECLARE @FeeEarnerLabel VARCHAR(20)  
  DECLARE @Partner        VARCHAR(20)  
  
  IF(@Status = 'T')  
    BEGIN  
      SET @FromWhom = RTRIM(ISNULL((SELECT [FMI].[TOWHOCODE]  
                                      FROM [dbo].[FileManagerItems] [FMI]  
                                     WHERE [FMI].[NUMBER] = @Number), ''))  
    END  
  ELSE  
    BEGIN  
      SET @FromWhom = RTRIM(ISNULL((SELECT [FMI].[FROMWHOCODE]  
                                      FROM [dbo].[FileManagerItems] [FMI]  
                                     WHERE [FMI].[NUMBER] = @Number), ''))  
    END  
  
  
--Getting the fee earner, and Partner label value from setting  
    SELECT @FeeEarnerLabel = [Keyvalue]   
      FROM [Settings]   
     WHERE [KeyName] = 'FeeEarnerLabel'  
      
    SELECT @Partner = [Keyvalue]   
      FROM [Settings]   
     WHERE [KeyName] = 'PartnerLabel'  
  
  
  SELECT [RES].[CODE]															 AS [Code],  
         CASE WHEN [RES].[NAME] = ''  
                   THEN [RES].[COMPANY]  
              ELSE [RES].[NAME] END                                              AS [Name],  
         [RES].[COMPANY]														 AS [Company],  
         [RES].[EMAIL]															 AS [Email],  
         [RES].[NAMECODE],  
         [RES].[CASECODE]														 AS [CaseCode],  
         [RES].[TYPE]															 AS [Type],  
         [RES].[Occupation]                                                      AS [Occupation],  
         CASE WHEN RTRIM(ISNULL([AST].[DESCRIPTION], '')) = ''  
                   THEN [RES].[TYPE]  
              ELSE RTRIM(ISNULL([AST].[DESCRIPTION], '')) END                    AS [TypeDesc],  
         LTRIM(  CASE WHEN [RES].[NAME] = ''  
                           THEN ''  
                      WHEN [RES].[NAME] = [RES].[COMPANY]  
                           THEN ''  
                      ELSE [RES].[NAME] END  
               + CASE WHEN [RES].[COMPANY] = ''  
                           THEN ''  
                      ELSE ' (' + [RES].[COMPANY] + ')' END)                     AS [NAMECOMP],  
         [RES].[MobileNo]														 AS [MobileNo],  
         [RES].[TITLE]															 AS [Title],  
         [RES].[SourceTable],  
         [RES].[SourceCode],  
         [RES].[Work]  
    FROM (SELECT CONVERT(VARCHAR(10), RTRIM(ISNULL([CAN].[CODE], '')))           AS [CODE],  
                 CONVERT(VARCHAR(300), RTRIM(ISNULL([CAN].[NAME], '')))          AS [NAME],  
                 CONVERT(VARCHAR(50), RTRIM(ISNULL([CAN].[COMPANY], '')))        AS [COMPANY],  
                 CONVERT(VARCHAR(100), RTRIM(ISNULL([CAN].[EMAIL], '')))         AS [Email],  
                 CONVERT(VARCHAR(100), RTRIM(ISNULL([CAN].[OCCUPATION], '')))    AS [Occupation],  
                 CONVERT(VARCHAR(10), RTRIM(ISNULL([CC].[NAMECODE], '')))        AS [NAMECODE],  
                 CONVERT(VARCHAR(20), RTRIM(ISNULL([CC].[CASECODE], '')))        AS [CASECODE],  
                 CONVERT(VARCHAR(20), RTRIM(ISNULL([CC].[CONTYPE], '')))         AS [TYPE], --PINO 2019-12-04, WAS [CAN].[TYPE]  
                 CONVERT(VARCHAR(100), RTRIM(ISNULL([CAN].[TITLE], '')))         AS [Title],  
                 CONVERT(VARCHAR(50), ISNULL([CAN].[MOBILENO], ''))              AS [MobileNo],  
                 'CaseAssociatesNames'                                           AS [SourceTable],  
                 [CAN].[CODE]                                                    AS [SourceCode],  
                 CONVERT(VARCHAR(50), ISNULL([CAN].[PHONENO], ''))               AS [Work]  
            FROM [dbo].[CaseContacts] [CC]  
                 INNER JOIN [dbo].[CaseAssociatesNames] [CAN]  
                         ON [CAN].[CODE] = [CC].[NameCODE]  
                        AND (   @ForEmail = 0  
                             OR RTRIM(ISNULL([CAN].[EMAIL], '')) <> '')  
           WHERE [CC].[CASECODE] = @CaseCode
		     AND ISNULL([CC].PUBLISH, '') = 'Y'
             AND (   ISNULL([CC].Retired,'N') <> 'Y'  
                  OR [CC].NAMECODE = CASE @Number  
                                        WHEN 0 THEN ''  
                                   ELSE @FromWhom END)  
          --UNION  
          --SELECT CONVERT(VARCHAR(10), RTRIM(ISNULL([CTC].[CLCODE], '')))         AS [Code],  
          --       CONVERT(VARCHAR(300),   RTRIM(ISNULL([CTC].[FIRSTNAME], ''))  
          --                             + ' '  
          --                             + RTRIM(ISNULL([CTC].[SURNAME], '')))     AS [Name],  
          --       CONVERT(VARCHAR(50), RTRIM(ISNULL([CTC].[CLNAMECON], '')))      AS [Company],  
          --       CONVERT(VARCHAR(100), RTRIM(ISNULL([CTC].[EmailAddress], '')))  AS [Email],  
          --       CONVERT(VARCHAR(100), RTRIM(ISNULL([CTC].[Occupation], '')))    AS [Occupation],  
          --       CONVERT(VARCHAR(10), '')                                        AS [NAMECODE],  
          --       CONVERT(VARCHAR(20), @CaseCode)                                 AS [CaseCode],  
          --       CONVERT(VARCHAR(20), 'Client Contact')                          AS [Type],  
          --       CONVERT(VARCHAR(100), RTRIM(ISNULL([CTC].[CLTITLE], '')))       AS [TITLE],  
          --       CONVERT(VARCHAR(50), ISNULL([CTC].[Mobile], ''))                AS [MobileNo],  
          --       'ClientContacts'                                                AS [SourceTable],  
          --       CONVERT(VARCHAR(50), [CTC].[id])                                AS [SourceCode],  
          --       CONVERT(VARCHAR(50),ISNULL([CTC].[WorkPhone],''))               AS [Work]  
          --  FROM [dbo].[matters] [MAT]  
          --       INNER JOIN [dbo].[ClientContacts] [CTC]  
          --               ON [CTC].[CLCODE] = [MAT].[ClientCode]  
          --              AND (   @ForEmail = 0  
          --                   OR RTRIM(ISNULL([CTC].[EmailAddress], '')) <> '')  
          -- WHERE [MAT].[CODE] = @CaseCode  
          UNION  
          SELECT CONVERT(VARCHAR(10), RTRIM(ISNULL([CAN].[CODE], '')))           AS [Code],  
                 CONVERT(VARCHAR(300), RTRIM(ISNULL([CAC].[NAME], '')))          AS [Name],  
                 CONVERT(VARCHAR(50), RTRIM(ISNULL([CAN].[COMPANY], '')))        AS [Company],  
                 CONVERT(VARCHAR(100), RTRIM(ISNULL([CAC].[EMAIL], '')))         AS [Email],  
                 CONVERT(VARCHAR(100), RTRIM(ISNULL([CAN].[OCCUPATION], '')))    AS [Occupation],  
                 CONVERT(VARCHAR(10), RTRIM(ISNULL([CC].[NAMECODE], '')))        AS [NAMECODE],  
                 CONVERT(VARCHAR(20), RTRIM(ISNULL([CC].[CASECODE], '')))        AS [CaseCode],  
                 CONVERT(VARCHAR(20), RTRIM(ISNULL([CC].[CONTYPE], '')))         AS [Type],  
                 CONVERT(VARCHAR(100), RTRIM(ISNULL([CAC].[JOBTITLE], '')))      AS [Title],  
                 CONVERT(VARCHAR(50), ISNULL([CAC].[MOBILE], ''))                AS [MobileNo],  
                 'CaseAssociatesContacts'                                        AS [SourceTable],  
                 CONVERT(VARCHAR(50), [CAC].[KEYID])                             AS [SourceCode],  
                 CONVERT(VARCHAR(50), ISNULL([CAC].[PHONE],''))                  AS [Work]  
            FROM [dbo].[CaseContacts] [CC]  
                 INNER JOIN [dbo].[CaseAssociatesNames] [CAN]  
                            INNER JOIN [dbo].[CaseAssociatesContacts] [CAC]  
                                    ON [CAC].[NAMECODE] = [CAN].[CODE]  
                                   AND (   @ForEmail = 0  
                                        OR RTRIM(ISNULL([CAC].[EMAIL], '')) <> '')  
                         ON [CAN].[CODE] = [CC].[NameCODE]  
           WHERE [CC].[CASECODE] = @CaseCode 
		   AND ISNULL([CC].PUBLISH, '') = 'Y'
          --UNION  
          --SELECT CONVERT(VARCHAR(10), RTRIM(ISNULL([CTC].[CODE], '')))           AS [Code],  
          --       CONVERT(VARCHAR(300), RTRIM(ISNULL([CTC].[Name], '')))          AS [Name],  
          --       CONVERT(VARCHAR(50), '')                                        AS [Company],  
          --       CONVERT(VARCHAR(100), RTRIM(ISNULL([CTC].[email], '')))         AS [Email],  
          --       CONVERT(VARCHAR(100), RTRIM(ISNULL([CTC].[Occupation], '')))    AS [Occupation],  
          --       CONVERT(VARCHAR(10), '')                                        AS [NAMECODE],  
          --       CONVERT(VARCHAR(20), @CaseCode)                                 AS [CaseCode],  
          --       CONVERT(VARCHAR(20), 'Client')                                  AS [Type],  
          --       CONVERT(VARCHAR(100), '')                                       AS [Title], --Not sure there is a column representing this in Contacts or client  
          --       CONVERT(VARCHAR(50), ISNULL([CLI].[CLMOBNO], ''))               AS [MobileNo],  
          --       'client'                                                        AS [SourceTable],  
          --       [CTC].[CODE]                                                    AS [SourceCode],  
          --       CONVERT(VARCHAR(50), ISNULL([CTC].[Tel], ''))                   AS [Work]  
          --  FROM [dbo].[matters] [MAT]  
          --       INNER JOIN [dbo].[Contacts] [CTC]  
          --               ON [CTC].[CODE] = [MAT].[ClientCode]  
          --              AND (   @ForEmail = 0  
          --                   OR RTRIM(ISNULL([CTC].[email], '')) <> '')  
          --       LEFT OUTER JOIN [dbo].[client] AS [CLI]  
          --                    ON [CLI].[CLCODE] = [CTC].[CODE]  
          -- WHERE [MAT].[CODE] = @CaseCode  
          UNION  
          SELECT CONVERT(VARCHAR(10), RTRIM(ISNULL([HAN].[CODE], '')))           AS [Code],  
                 CONVERT(VARCHAR(300), RTRIM(ISNULL([HAN].[Name], '')))          AS [Name],  
                 CONVERT(VARCHAR(50), '')                                        AS [Company],  
                 CONVERT(VARCHAR(100), RTRIM(ISNULL([HAN].[email], '')))         AS [Email],  
                 CONVERT(VARCHAR(100), '')                                       AS [Occupation],  
                 CONVERT(VARCHAR(10), '')                                        AS [NAMECODE],  
                 CONVERT(VARCHAR(20), @CaseCode)                                 AS [CaseCode],  
                 CONVERT(VARCHAR(20),  
                   (CASE WHEN ColumnName='FECode'   
                              THEN ISNULL(@FeeEarnerLabel, 'Fee Earner')   
                       WHEN ColumnName='Partner'  
                              THEN ISNULL(@Partner, 'Partner')  
                         ELSE ColumnName   
                    END))                                                        AS [Type],  
                 CONVERT(VARCHAR(100), '')                                       AS [Title],   
                 CONVERT(VARCHAR(50), ISNULL([HAN].[PhoneNumber], ''))           AS [MobileNo],  
                 'Handlers'                                                      AS [SourceTable],  
                 [HAN].[CODE]                                                    AS [SourceCode],  
                 CONVERT(VARCHAR(50), ISNULL([HAN].[Phoneno], ''))               AS [Work]  
            FROM (SELECT [UNPVT].[Handlers]     AS [HandlerValue],   
                         [UNPVT].[Handlerslist] AS [ColumnName]  
                    FROM [dbo].[matters] [MAT]  
                         UNPIVOT (    [Handlers]  
                                  FOR [Handlerslist]   
                                   IN ([MAT].[FECode],  
                                       [MAT].[Partner],  
                                       [MAT].[Secretary])) [UNPVT]  
                   WHERE [UNPVT].[Code] = @CaseCode  
                     AND [UNPVT].[Handlers] <> '') [HANDLERLIST]  
                 INNER JOIN [Handlers] [HAN]  
                         ON [HAN].[CODE] = [HANDLERLIST].[HandlerValue]  
          UNION  
          SELECT CONVERT(VARCHAR(10), RTRIM(ISNULL([CTC].[CODE], '')))           AS [Code],  
                 CONVERT(VARCHAR(300), RTRIM(ISNULL([CTC].[Name], '')))          AS [Name],  
                 CONVERT(VARCHAR(50), '')                                        AS [Company],  
                 CONVERT(VARCHAR(100), RTRIM(ISNULL([CTC].[SecondaryEmail],  
                                                    '')))                        AS [Email],  
                 CONVERT(VARCHAR(100), RTRIM(ISNULL([CTC].[Occupation], '')))    AS [Occupation],  
                 CONVERT(VARCHAR(10), '')                                        AS [NAMECODE],  
                 CONVERT(VARCHAR(20), @CaseCode)                                 AS [CaseCode],  
                 CONVERT(VARCHAR(20), 'Client')                                  AS [Type],  
                 CONVERT(VARCHAR(100), '')                                       AS [Title], --Not sure there is a column representing this in Contacts or client  
                 CONVERT(VARCHAR(50), ISNULL([CLI].[CLMOBNO], ''))               AS [MobileNo],  
                 'client'                                                        AS [SourceTable],  
                 [CTC].[CODE]                                                    AS [SourceCode],  
                 CONVERT(VARCHAR(50), ISNULL([CTC].[Tel], ''))                   AS [Work]  
            FROM [dbo].[matters] [MAT]  
                 INNER JOIN [dbo].[Contacts] [CTC]  
                         ON [CTC].[CODE] = [MAT].[ClientCode]  
                        AND (   @ForEmail = 0  
                             OR RTRIM(ISNULL([CTC].[SecondaryEmail], '')) <> '')  
                 LEFT OUTER JOIN [dbo].[client] AS [CLI]  
                              ON [CLI].[CLCODE] = [CTC].[CODE]  
           WHERE [MAT].[CODE] = @CaseCode) [RES]  
         LEFT OUTER JOIN [dbo].[AssociateTypes] [AST]  
                      ON [AST].[CODE] = [RES].[TYPE]
		WHERE [RES].[TYPE] <> 'client'
		AND
			(
				(RTRIM(LTRIM(ISNULL(@SearchText, ''''))) = '''') OR (RTRIM(LTRIM(@SearchText)) = '''')
				OR
				(
					   [NAME] LIKE '%' + RTRIM(LTRIM(@SearchText)) + '%'
					OR [TYPE] LIKE '%' + RTRIM(LTRIM(@SearchText)) + '%'
					OR [Email] LIKE '%' + RTRIM(LTRIM(@SearchText)) + '%'
					OR [MobileNo] LIKE '%' + RTRIM(LTRIM(@SearchText)) + '%'
					OR [Work] LIKE '%' + RTRIM(LTRIM(@SearchText)) + '%'
				)
			)
ORDER BY CASE WHEN [RES].[NAME] = ''  
                   THEN [RES].[COMPANY]  
              ELSE [RES].[NAME] END,  
         [RES].[TYPE]  
SET NOCOUNT OFF  
END  IF OBJECT_ID(N'KAAS_CP_OtherCaseDetails',N'P')IS NOT NULL
	DROP PROCEDURE [dbo].[KAAS_CP_OtherCaseDetails]
GO

CREATE PROCEDURE 
	 [dbo].[KAAS_CP_OtherCaseDetails]            
     (@CaseCode          VARCHAR(20),
	  @PageNumber         INT           = NULL, 
      @PageSize           INT           = NULL,
      @SearchText         VARCHAR(400)  = NULL, 
      @SortColumn         VARCHAR(50)   = NULL,
      @SortDirection      VARCHAR(10)   = NULL)
            
AS            
/*******************************************************************************************************
* To FETCH data for other case details main grid.													   * 
*                                                                                                      *
* Stored Procedure Name : [dbo].[KAAS_CP_OtherCaseDetails]											   *
* Copied from			: [dbo].[KAAS_OtherCaseDetailsFetchMainGrid]								   *
*                                                                                                      *
* Modification History:                                                                                *
* 2019-04-23    Vinodhan K        Created                                                              *
* 2019-08-23    Dheepin Vijith K  Modified - Default sorting changed to DESC to show the latest values *
*											on the top of the grid first							   *
* 2021-06-22    Aakif			  List only published UDF data                                         *
 *******************************************************************************************************/
BEGIN           
 SET NOCOUNT ON

    DECLARE @StartRow INT;
    DECLARE @EndRow INT;
    
    SET @PageNumber = ISNULL(@PageNumber, 0) - 1; --  0 BASED INDEX
    
    IF(@PageNumber > -1)
    BEGIN
    	SET @StartRow = ((@PageNumber) * @PageSize) + 1;
    	SET @EndRow = (@StartRow + @PageSize) - 1;
    END
    

    IF(RTRIM(ISNULL(@SearchText, '')) = '' OR RTRIM(@SearchText) = '')
    BEGIN
    	SET @SearchText = ''
    END
    
	IF(ISNULL(@SortColumn, '') = '' OR RTRIM(LTRIM(@SortColumn)) = '')
	BEGIN
		SET @SortColumn = 'SeqNo'
	END

    IF(ISNULL(@SortDirection, '') = '' OR RTRIM(LTRIM(@SortDirection)) = '')
	BEGIN
		SET @SortDirection = 'DESC'
	END

	SELECT 
		IDENTITY(INT, 1,1) AS [Row_Number],
		*
	INTO
		#OtherCaseDetailTable
	FROM
	(SELECT RTRIM(ISNULL(UDFCategory.UDFCategory,'')) AS GroupName,
		   RTRIM( SystemUserDefinedFields.PROMPT) AS Detail,          
		   CONVERT(DATETIME, RTRIM(CaseUDFAnswers.DateValue), 23) AS DateValue,
		   --ISNULL(CONVERT(VARCHAR,CaseUDFAnswers.DateValue,23),'') AS  DateValue,
		            
		   CASE RTRIM(SystemUserDefinedFields.[VALIDATION]) 
			  WHEN 'L' 
				THEN CONVERT(VARCHAR(MAX),RTRIM(ISNULL(CaseUDFAnswers.TEXT1,'')))+' - ' +  
			  	(SELECT RTRIM(ISNULL(UDFLookups.[DESCRIPTION],'')) 
				   FROM UDFLookups 
				  WHERE SystemUserDefinedFields.FIELDNAME = UDFLookups.FIELDNAME 
						AND CONVERT(VARCHAR(MAX),CaseUDFAnswers.TEXT1) = UDFLookups.CODE)        
			  WHEN 'R' 
				THEN CONVERT(VARCHAR(200),RTRIM(ISNULL(CaseUDFAnswers.TEXT1,'')))  
			  WHEN 'T' 
				THEN CONVERT(VARCHAR(200),RTRIM(ISNULL(CaseUDFAnswers.TEXT1,'')))  
			  ELSE   
				CASE RTRIM(SystemUserDefinedFields.[TYPE]) 
					WHEN 'Text' 
						THEN (CASE 
								WHEN CONVERT(VARCHAR(200),RTRIM(ISNULL(CaseUDFAnswers.TEXT1,'')))='' 
									THEN '' 
								ELSE  CONVERT(VARCHAR(200),RTRIM(ISNULL(CaseUDFAnswers.TEXT1,'')))  
							  END)      
					WHEN 'Numeric' 
						THEN 
							--(CASE 
							--	WHEN SystemUserDefinedFields.DECMIAL=0 THEN  CONVERT(VARCHAR(20),CONVERT(DECIMAL(18,0),CaseUDFAnswers.NumberValue) )
							--	WHEN SystemUserDefinedFields.DECMIAL=1 THEN  CONVERT(VARCHAR(20),CONVERT(DECIMAL(18,1),CaseUDFAnswers.NumberValue) )
							--	WHEN SystemUserDefinedFields.DECMIAL=2 THEN  CONVERT(VARCHAR(20),CONVERT(DECIMAL(18,2),CaseUDFAnswers.NumberValue) )
							--END)
							CONVERT(VARCHAR(20),CAST(CaseUDFAnswers.NumberValue AS MONEY ),1)
					 WHEN 'Date' 
						THEN
							 CASE 
								WHEN CONVERT(VARCHAR(20), CaseUDFAnswers.DateValue, 23) = '1900-01-01' 
									THEN '' 
								--ELSE CONVERT(VARCHAR(20), CaseUDFAnswers.DateValue, 23) 
								ELSE CONVERT(VARCHAR(20), CaseUDFAnswers.DateValue, 106) 
							 END  
					 ELSE CONVERT(VARCHAR(200),RTRIM(ISNULL(CaseUDFAnswers.TEXT1,'')))        
				END  
			END AS Value    
		, RTRIM(ISNULL(CaseUDFAnswers.NumberValue,0))			AS NumberValue    
		, RTRIM(ISNULL(CaseUDFAnswers.TEXT1,''))				AS [Text1]
		, RTRIM(SystemUserDefinedFields.TYPE)					AS [Type]    
		, RTRIM(ISNULL(CaseUDFAnswers.SEQNO,0))					AS [SeqNo]    
		, RTRIM(SystemUserDefinedFields.FILEPREFIX)				AS [FilePrefix]    
		, RTRIM(SystemUserDefinedFields.FIELDNAME)				AS [FieldName]    
		, RTRIM(ISNULL(SystemUserDefinedFields.UDFCategory,0))	AS  UDFCategory      
		, CASE WHEN RTRIM(ISNULL(CaseUDFAnswers.ForCopy,0)) = '1' THEN 'True' ELSE 'False' END AS  ForCopy  
		, SystemUserDefinedFields.DECMIAL						AS [Decimal]
		, SystemUserDefinedFields.LENGTH						AS [Length]
	FROM CaseUDFAnswers 
		 INNER JOIN SystemUserDefinedFields 
		 ON CaseUDFAnswers.UDFFILE = SystemUserDefinedFields.FILEPREFIX 
		 AND CaseUDFAnswers.UDFNAME = SystemUserDefinedFields.FIELDNAME 
		 LEFT OUTER JOIN UDFCategory 
		 ON SystemUserDefinedFields.UDFCategory = UDFCategory.ID     
   WHERE
   (
   ISNULL(@SearchText, '') = '' OR RTRIM(LTRIM(@SearchText)) = ''
   OR
   (
   [UDFCategory].[UDFCategory] LIKE '%' + @SearchText + '%'
   OR
   [SystemUserDefinedFields].[PROMPT] LIKE '%' + @SearchText + '%'
   OR
   [CaseUDFAnswers].[DateValue] LIKE '%' + @SearchText + '%'
   OR
   [SystemUserDefinedFields].[VALIDATION] LIKE '%' + @SearchText + '%'
   OR
   [CaseUDFAnswers].[TEXT1] LIKE '%' + @SearchText + '%'
   OR
   [SystemUserDefinedFields].[TYPE] LIKE '%' + @SearchText + '%'
   OR
   [CaseUDFAnswers].[NumberValue] LIKE '%' + @SearchText + '%'
   OR
   [CaseUDFAnswers].[SEQNO] LIKE '%' + @SearchText + '%'
   OR
   [SystemUserDefinedFields].[FILEPREFIX] LIKE '%' + @SearchText + '%'
   OR
   [SystemUserDefinedFields].[FIELDNAME] LIKE '%' + @SearchText + '%'
   OR
   [SystemUserDefinedFields].[UDFCategory] LIKE '%' + @SearchText + '%'
   OR
   [CaseUDFAnswers].[ForCopy] LIKE '%' + @SearchText + '%'
   OR
   [SystemUserDefinedFields].[DECMIAL] LIKE '%' + @SearchText + '%'
   OR
   [SystemUserDefinedFields].[LENGTH] LIKE '%' + @SearchText + '%'
   )
   )
   AND 
   SystemUserDefinedFields.PUBLISH = 1
   AND
   (RTRIM(CaseUDFAnswers.CASECODE) = RTRIM(@CaseCode)))
   AS FoundTable     
   ORDER BY 
   CASE WHEN @SortDirection = 'ASC' THEN
		   CASE @SortColumn 
		   	  WHEN 'SeqNo'   THEN CAST([SeqNo] AS INT)
		   END
	  END,
	  CASE WHEN @SortDirection = 'DESC' THEN
	  	  CASE @SortColumn 
	  	  	  WHEN 'SeqNo'   THEN CAST([SeqNo] AS INT)
	  	  END
	  END DESC,
	  CASE WHEN @SortDirection = 'ASC' THEN
	  	  CASE @SortColumn 
	  	  	  WHEN 'GroupName' THEN [GroupName]
	  	  END
	  END,
	  CASE WHEN @SortDirection = 'DESC' THEN
	  	  CASE @SortColumn 
	  	  	  WHEN 'GroupName'   THEN [GroupName]
	  	  END
	  END DESC,
	  CASE WHEN @SortDirection = 'ASC' THEN
	  	  CASE @SortColumn 
	  	  	  WHEN 'Detail'   THEN [Detail]
	  	  END
	  END,
	  CASE WHEN @SortDirection = 'DESC' THEN
	  	  CASE @SortColumn 
	  	  	  WHEN 'Detail'   THEN [Detail]
	  	  END
	  END DESC,
	  CASE WHEN @SortDirection = 'ASC' THEN
	  	  CASE @SortColumn 
	  	  	  WHEN 'DateValue'   THEN [DateValue]
	  	  END
	  END,
	  CASE WHEN @SortDirection = 'DESC' THEN
	  	  CASE @SortColumn 
	  	  	  WHEN 'DateValue'   THEN [DateValue]
	  	  END
	  END DESC,
	  CASE WHEN @SortDirection = 'ASC' THEN
	  	  CASE @SortColumn 
	  	  	  WHEN 'Value'   THEN [Value]
	  	  END
	  END,
	  CASE WHEN @SortDirection = 'DESC' THEN
	  	  CASE @SortColumn 
	  	  	  WHEN 'Value'   THEN [Value]
	  	  END
	  END DESC,
	  CASE WHEN @SortDirection = 'ASC' THEN
	  	  CASE @SortColumn 
	  	  	  WHEN 'NumberValue'   THEN [NumberValue]
	  	  END
	  END,
	  CASE WHEN @SortDirection = 'DESC' THEN
	  	  CASE @SortColumn 
	  	  	  WHEN 'NumberValue'   THEN [NumberValue]
	  	  END
	  END DESC,
	  CASE WHEN @SortDirection = 'ASC' THEN
	  	  CASE @SortColumn 
	  	  	  WHEN 'Text1'   THEN [Text1]
	  	  END
	  END,
	  CASE WHEN @SortDirection = 'DESC' THEN
	  	  CASE @SortColumn 
	  	  	  WHEN 'Text1'   THEN [Text1]
	  	  END
	  END DESC,
	  CASE WHEN @SortDirection = 'ASC' THEN
	  	  CASE @SortColumn 
	  	  	  WHEN 'Type'   THEN [Type]
	  	  END
	  END,
	  CASE WHEN @SortDirection = 'DESC' THEN
	  	  CASE @SortColumn 
	  	  	  WHEN 'Type'   THEN [Type]
	  	  END
	  END DESC,
	  CASE WHEN @SortDirection = 'ASC' THEN
	  	  CASE @SortColumn 
	  	  	  WHEN 'FilePrefix'   THEN [FilePrefix]
	  	  END
	  END,
	  CASE WHEN @SortDirection = 'DESC' THEN
	  	  CASE @SortColumn 
	  	  	  WHEN 'FilePrefix'   THEN [FilePrefix]
	  	  END
	  END DESC,
	  CASE WHEN @SortDirection = 'ASC' THEN
	  	  CASE @SortColumn 
	  	  	  WHEN 'FieldName'   THEN [FieldName]
	  	  END
	  END,
	  CASE WHEN @SortDirection = 'DESC' THEN
	  	  CASE @SortColumn 
	  	  	  WHEN 'FieldName'   THEN [FieldName]
	  	  END
	  END DESC,
	  CASE WHEN @SortDirection = 'ASC' THEN
	  	  CASE @SortColumn 
	  	  	  WHEN 'UDFCategory'   THEN [UDFCategory]
	  	  END
	  END,
	  CASE WHEN @SortDirection = 'DESC' THEN
	  	  CASE @SortColumn 
	  	  	  WHEN 'UDFCategory'   THEN [UDFCategory]
	  	  END
	  END DESC,
	  CASE WHEN @SortDirection = 'ASC' THEN
	  	  CASE @SortColumn 
	  	  	  WHEN 'ForCopy'   THEN [ForCopy]
	  	  END
	  END,
	  CASE WHEN @SortDirection = 'DESC' THEN
	  	  CASE @SortColumn 
	  	  	  WHEN 'ForCopy'   THEN [ForCopy]
	  	  END
	  END DESC,
	  CASE WHEN @SortDirection = 'ASC' THEN
	  	  CASE @SortColumn 
	  	  	  WHEN 'Decimal'   THEN [Decimal]
	  	  END
	  END,
	  CASE WHEN @SortDirection = 'DESC' THEN
	  	  CASE @SortColumn 
	  	  	  WHEN 'Decimal'   THEN [Decimal]
	  	  END
	  END DESC,
	  CASE WHEN @SortDirection = 'ASC' THEN
	  	  CASE @SortColumn 
	  	  	  WHEN 'Length'   THEN [Length]
	  	  END
	  END,
	  CASE WHEN @SortDirection = 'DESC' THEN
	  	  CASE @SortColumn 
	  	  	  WHEN 'Length'   THEN [Length]
	  	  END
	  END DESC

	SELECT
		*
	FROM
		#OtherCaseDetailTable
	WHERE
	(
		@StartRow IS NULL
		OR
		(
			[Row_Number] BETWEEN @StartRow AND @EndRow		
		)
	)

	SELECT
		(COUNT([Row_Number])) AS TotalRecords
	FROM
        #OtherCaseDetailTable
SET NOCOUNT OFF
END

GO

IF OBJECT_ID(N'KAAS_CP_RegisterNewUser',N'P')IS NOT NULL
	DROP PROCEDURE [dbo].[KAAS_CP_RegisterNewUser]
GO

CREATE PROCEDURE 
	[dbo].[KAAS_CP_RegisterNewUser]
    (@EmailId               VARCHAR(200),
	 @IsTwoFactorEnabled    BIT = 0,
	 @EmailActivationToken  NVARCHAR(252) = NULL,
	 @TokenCreationTime     DATETIME = NULL,
	 @TokenExpirationTime   DATETIME = NULL)

AS

/*******************************************************************************************************
*                                                                                                      *
* Stored Procedure Name : [dbo].[KAAS_CP_RegisterNewUser] 											   *
* Description: To register new user in Client Portal												   *
*                                                                                                      *
* Modification History:                                                                                *
* 2021-06-07    Aakif        Created                                                                   *
 *******************************************************************************************************/

BEGIN

-- Update OTP with its expiry time

INSERT INTO
	[dbo].[ClientLogin]
	(
	 [Email],
	 [IsActiveLogin],
	 [IsTwoFactorEnabled],
	 [EmailActivationToken],
	 [EmailActivationTokenCreatedTime],
	 [EmailActivationTokenExpirationTime]
	)
	VALUES
	(
	 @EmailId,
	 0,
	 @IsTwoFactorEnabled,
	 @EmailActivationToken,
	 @TokenCreationTime,
	 @TokenExpirationTime
	)

END

GO
IF OBJECT_ID(N'KAAS_CP_UpdateAccessCodeForUser',N'P')IS NOT NULL
	DROP PROCEDURE [dbo].[KAAS_CP_UpdateAccessCodeForUser]
GO
CREATE PROCEDURE 
	 [dbo].[KAAS_CP_UpdateAccessCodeForUser]
	(@EmailId            VARCHAR(200),
	 @AccessCode		  VARCHAR(50)  = NULL,
	 @AccessCodeStatus   VARCHAR(200) = NULL,
	 @OffSetForOtpExpiry TINYINT      = 10)
AS
/*******************************************************************************************************
*                                                                                                      *
* Stored Procedure Name : [dbo].[KAAS_CP_UpdateAccessCodeForUser] 									   *
* Description: To store Access Code and OTP Expirty time for user									   *
*                                                                                                      *
* Modification History:                                                                                *
* 2021-05-31    Aakif        Created                                                                   *
 *******************************************************************************************************/

BEGIN
	DECLARE @CurrentDateTime DATETIME = GETUTCDATE();

-- Update OTP with its expiry time
	UPDATE [dbo].[ClientLogin]
		SET 
			[AccessCode]				   = @AccessCode,
		    [AccessCodeStatus]			   = @AccessCodeStatus,
		    [OTPAccessTokenCreationTime]   = @CurrentDateTime,
		    [OTPAccessTokenExpirationTime] = DATEADD(MINUTE, @OffSetForOtpExpiry, @CurrentDateTime)
		WHERE 
			[Email] = @EmailId
END

GO
IF OBJECT_ID(N'KAAS_CP_UpdateClientAccess',N'P')IS NOT NULL
	DROP PROCEDURE [dbo].[KAAS_CP_UpdateClientAccess]
GO

CREATE PROCEDURE 
	[dbo].[KAAS_CP_UpdateClientAccess]
    (@EmailId       VARCHAR(200),
	 @HandlerCode	VARCHAR(10),
	 @ContactCode	VARCHAR(25),
	 @MatterCode	VARCHAR(25) = NULL)

AS

/*******************************************************************************************************
*                                                                                                      *
* Stored Procedure Name : [dbo].[KAAS_CP_UpdateClientAccess] 										   *
* Description: To add accesss to clients for a code													   *
*                                                                                                      *
* Modification History:                                                                                *
* 2021-06-25    Aakif        Created                                                                   *
 *******************************************************************************************************/

BEGIN

-- CLIENT ACCESS LOGIN TO CONTACT CODES

	DECLARE @ClientId BIGINT = 0;

	SELECT 
		@ClientId = ClientLoginId
	FROM
		[ClientLogin]
	WHERE
		Email = @EmailId

	-- INSERT TO CLIENT LOGIN ACCESS
	INSERT INTO [ClientLoginAccess]
		(
		 ClientLoginId,
		 ContactCode,
		 MatterCode,
		 HasAccessToAllMatter,
		 PublishedHandlerCode,
		 PublishedDateTime
		)
	VALUES
		(
		@ClientId,
		@ContactCode,
		@MatterCode,
		'Y',
		@HandlerCode,
		GETUTCDATE()
		)


END

GO
IF OBJECT_ID(N'KAAS_CP_UpdateIncorrectLoginAttempt',N'P')IS NOT NULL
	DROP PROCEDURE [dbo].[KAAS_CP_UpdateIncorrectLoginAttempt]
GO
CREATE PROCEDURE 
	 [dbo].[KAAS_CP_UpdateIncorrectLoginAttempt]
	(@EmailId VARCHAR(200),
	 @Result  INT = 0 OUTPUT)
AS
/*******************************************************************************************************
*                                                                                                      *
* Stored Procedure Name : [dbo].[KAAS_CP_UpdateIncorrectLoginAttempt]								   *
* Description: To update lockout date and number of attempts for user								   *
*                                                                                                      *
* Modification History:                                                                                *
* 2021-06-01    Aakif        Created																   *
 *******************************************************************************************************/

BEGIN
	DECLARE @CurrentLoginAttempt TINYINT;
	DECLARE @MaxLockoutAttemp INT, @LockoutTimeoutLimit INT;

--GET current incorrect login attempt count
	SELECT 
	  @CurrentLoginAttempt   = ISNULL(NoOfAttempt,0) 
	FROM 
	  [dbo].[ClientLogin]

-- GET Max attempt and lockout duration from setting
	SELECT 
		@MaxLockoutAttemp    = ISNULL(IsLockOut_Attempt, 3),
		@LockoutTimeoutLimit = ISNULL(LockoutDurationInMins, 10)
	FROM 
		[dbo].[ClientPortalSetting]

-- Step 1: Update current attempt at login
	UPDATE [dbo].[ClientLogin]
	  SET 
		[NoOfAttempt] = (@CurrentLoginAttempt + 1)
	  WHERE 
		[Email] = @EmailId

-- Step 2: Check if current attempt exceeds limit and update lockedout date
IF(@MaxLockoutAttemp <= (@CurrentLoginAttempt + 1 ))
	BEGIN
	    UPDATE [dbo].[ClientLogin]
		  SET 
			[LockedOutEndDate] = DATEADD(MINUTE, @LockoutTimeoutLimit, GETUTCDATE())
		  WHERE 
			[Email] = @EmailId
			
		SET @Result = @LockoutTimeoutLimit

	END
END

GO

IF OBJECT_ID(N'KAAS_CP_UpdateLastLoginData',N'P')IS NOT NULL
	DROP PROCEDURE [dbo].[KAAS_CP_UpdateLastLoginData]
GO
CREATE PROCEDURE 
	  [dbo].[KAAS_CP_UpdateLastLoginData]
	 (@EmailId     VARCHAR(200),
	  @LoginId     BIGINT,
	  @DeviceInfo  NVARCHAR(20)   = NULL,
	  @BrowserInfo NVARCHAR(20)   = NULL,
	  @IPAddress   NVARCHAR(30)   = NULL)
AS
/*******************************************************************************************************
*                                                                                                      *
* Stored Procedure Name : [dbo].[KAAS_CP_UpdateLastAccessDate] 										   *
* Description: To update last accessed datetime for user											   *
*                                                                                                      *
* Modification History:                                                                                *
* 2021-05-27    Aakif        Created                                                                   *
* 2021-06-31    Aakif        Reset LockedoutEndDate after successful login							   *
 *******************************************************************************************************/

BEGIN
-- Step 1: Update last login access date
	UPDATE [dbo].[ClientLogin]
		SET [LastAccessedDateTime] = GETUTCDATE(),
		    [NoOfAttempt]		   = 0,
		    [LockedOutEndDate]     = NULL,
		    [IsFirstlogin]         = 0,
		    [IsActiveLogin]        = 1
		WHERE 
			[Email] = @EmailId

-- Step 2: Add user login history
	INSERT INTO 
	    [dbo].[ClientPortalLogonInfo]
		([ClientLoginId]
		,[LastAccessedDateTime]
		,[DeviceInfo]
		,[Browser]
		,[IPAddress])
	VALUES
		(@LoginId
		,GETUTCDATE()
		,@DeviceInfo
		,@BrowserInfo
		,@IPAddress)
END

GO






IF OBJECT_ID(N'KAAS_CP_UpdatePasswordHash',N'P')IS NOT NULL
	DROP PROCEDURE [dbo].[KAAS_CP_UpdatePasswordHash]
GO

CREATE PROCEDURE 
	[dbo].[KAAS_CP_UpdatePasswordHash]
    (@LoginId         BIGINT,
	 @NewPasswordHash NVARCHAR(512))

AS

/*******************************************************************************************************
*                                                                                                      *
* Stored Procedure Name : [dbo].[KAAS_CP_UpdatePasswordHash] 										   *
* Description: To update password hash after the reset is successful								   *
*                                                                                                      *
* Modification History:                                                                                *
* 2021-06-02    Aakif        Created                                                                   *
 *******************************************************************************************************/

BEGIN

-- Update password hash after password is reset
UPDATE [dbo].[ClientLogin]
	SET
		[PasswordHash]				  = @NewPasswordHash,
		[PasswordResetLockOutEndDate] = NULL
	WHERE [ClientLoginId] = @LoginId


END

GO
IF OBJECT_ID(N'KAAS_CP_UpdateResetPasswordCode',N'P')IS NOT NULL
	DROP PROCEDURE [dbo].[KAAS_CP_UpdateResetPasswordCode]
GO

CREATE PROCEDURE 
	[dbo].[KAAS_CP_UpdateResetPasswordCode]
    (@LoginId            BIGINT,
	 @ResetPasswordCode  VARCHAR(10),
	 @CodeCreationTime   DATETIME = NULL,
	 @CodeExpirationTime DATETIME = NULL)

AS

/*******************************************************************************************************
*                                                                                                      *
* Stored Procedure Name : [dbo].[KAAS_CP_UpdateResetPasswordCode] 									   *
* Description: To store Reset password OTP Expirty time for user									   *
*                                                                                                      *
* Modification History:                                                                                *
* 2021-06-02    Aakif        Created                                                                   *
 *******************************************************************************************************/

BEGIN

-- Update OTP with its expiry time
UPDATE [dbo].[ClientLogin]
	SET
		[IsPasswordResetOTPVerified]	 = 0,
		[PasswordResetOtp]               = @ResetPasswordCode,
		[PasswordResetOtpcreationTime]   = @CodeCreationTime,
		[PasswordResetOtpexpirationTime] = @CodeExpirationTime
	WHERE [ClientLoginId] = @LoginId

END

GO
IF OBJECT_ID(N'KAAS_CP_ValidateResetPassword',N'P')IS NOT NULL
	DROP PROCEDURE [dbo].[KAAS_CP_ValidateResetPassword]
GO

CREATE PROCEDURE 
	[dbo].[KAAS_CP_ValidateResetPassword]
    (@LoginId		  BIGINT,
	 @IsOtpValid	  BIT,
	 @IsPasswordReset BIT = 0)

AS

/*******************************************************************************************************
*                                                                                                      *
* Stored Procedure Name : [dbo].[KAAS_CP_ValidateResetPassword] 									   *
* Description: To validate Reset password OTP and lockout user if invalid credentails provided		   *
*                                                                                                      *
* Modification History:                                                                                *
* 2021-06-03    Aakif        Created                                                                   *
 *******************************************************************************************************/

BEGIN

DECLARE @CurrentAttempt TINYINT, @LockoutLimit TINYINT, @LockoutDuration INT;

--HANDLE FOR VALID RESET PWD OTP
IF(@IsOtpValid = 1)
BEGIN
	UPDATE [dbo].[ClientLogin]
		SET
			[IsPasswordResetOTPVerified] = 1
		WHERE [ClientLoginId] = @LoginId

END

ELSE
BEGIN
	
	--GET CURRENT ATTEMPT COUNT TO DECIDE WHETHER TO LOCKOUT USER
	SELECT 
		@CurrentAttempt = ISNULL(PasswordResetOTPAttemptCount, 0)
	FROM 
		[dbo].[ClientLogin]

	-- SET LOCKOUT TENANT SETTINGS
	SELECT
		@LockoutLimit	 = ISNULL(IsLockOut_Attempt, 3),
		@LockoutDuration = ISNULL(LockoutDurationInMins, 10)
	FROM
		[dbo].[ClientPortalSetting]

	-- LOCKOUT USER FOR MAX LIMIT REACHED
	IF(@CurrentAttempt >= @LockoutLimit)
	BEGIN
		UPDATE [dbo].[ClientLogin]
			SET
				[PasswordResetLockOutEndDate] = DATEADD(MINUTE, @LockoutDuration, GETUTCDATE())
			WHERE [ClientLoginId] = @LoginId
	END

	--INCREMENT CURRENT ATTEMPT COUNT
	UPDATE [dbo].[ClientLogin]
		SET
			[PasswordResetOTPAttemptCount] = (@CurrentAttempt + 1)
		WHERE [ClientLoginId] = @LoginId
END


--RESET ALL DATA AFTER PASSWORD IS RESET
IF(@IsPasswordReset = 1)
BEGIN
	UPDATE [dbo].[ClientLogin]
		SET
			[PasswordResetOTPAttemptCount]	 = 0,
			[IsPasswordResetOTPVerified]	 = 0,
			[PasswordResetLockOutEndDate]	 = NULL,
			[PasswordResetOTP]				 = NULL,
			[PasswordResetOTPCreationTime]	 = NULL,
			[PasswordResetOTPExpirationTime] = NULL
		WHERE [ClientLoginId] = @LoginId

END

END

GO
