IF EXISTS
  (SELECT TOP 1 1
     FROM SYS.[objects] SO
    WHERE SO.[name] = N'KEYHM_GetDocumentsIM'
      AND SO.[type] = 'P')
  BEGIN
    DROP PROCEDURE [dbo].[KEYHM_GetDocumentsIM]
  END
GO
CREATE PROCEDURE
       [dbo].[KEYHM_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)     = '',
	   @LoggedInHandler		VARCHAR(10)		= NULL,
	   @ColumnNames         VARCHAR(50)		= '',
	   @FilterValues        VARCHAR(4000)	= ''
	   )
AS
/************************************************************************************************************************
 *                                                                                                                      *
 * [dbo].[KEYHM_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-12-04   Kowshik.R         Mobile documents page special character search and.docx file type issue is fixed                                                              *
 ************************************************************************************************************************/
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 
			KEYHM_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 ))
		)

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
		(
		CASE WHEN @SearchText = '' THEN 1
			 WHEN 
			([DA].FILEPATH 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 ))
		)

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
		(
		CASE WHEN @SearchText = '' THEN 1
			 WHEN 
			([DA].FILEPATH LIKE '%' + @SearchText + '%')
			OR
			([DA].[DATEENTERED] LIKE '%' + @SearchText + '%')
			OR
			([DA].[TYPE] LIKE '%' + @SearchText + '%')
			OR
			([DA].[DocClass] LIKE '%' + @SearchText + '%')
			   OR
            ([DA].[DOCUMENT] LIKE '%' + @SearchText + '%') 
			THEN 1
			ELSE 0
			END = 1
		)
		
		AND
		(
			ISNULL(@FileType, '') = ''
			OR ([DA].[TYPE] IN (select FileType from @FileTypeList ))
		)
 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 #DiaryTables
  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]
   
   

 --  OFFSET @PageSize * (@PageNo - 1) ROWS
 --  FETCH NEXT @PageSize ROWS ONLY OPTION (RECOMPILE);
END
ELSE IF ISNULL(@DocFolderID, 0) <> 0
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
		(
		CASE WHEN @SearchText = '' THEN 1
			 WHEN
				([DA].FILEPATH 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 ))
		)

	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
		(
		CASE WHEN @SearchText = '' THEN 1
			 WHEN
				([DA].FILEPATH 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 ))
		)
 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
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
		(
		CASE WHEN @SearchText = '' THEN 1 
			 WHEN			
			([DA].FILEPATH 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 ))
		)

  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
		(
		CASE WHEN @SearchText = '' THEN 1 
			 WHEN			
			([DA].FILEPATH 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 ))
		)
 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.KEYHM_FNConvertUTCDateToTargetTimeZone([RES].[DiaryDate], @LoggedInHandler), 23) AS [DATE],
       ISNULL([RES].[ActionID], 0) AS [ACTIONID],
       CONVERT(VARCHAR(15), dbo.KEYHM_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],
       CONVERT(VARCHAR(15),dbo.KEYHM_FNConvertUTCDateToTargetTimeZone([DiaryDate], @LoggedInHandler), 23)  AS [DATE],
       [ACTIONID],
       CONVERT(VARCHAR(15), dbo.KEYHM_FNConvertUTCDateToTargetTimeZone([DocumentDate], @LoggedInHandler), 23) AS [DocumentDate],
       [IMDocID],
       [IMClass],
       [DocumentClassDescription],
       [DocClass],
       [Document],
       [TRACKREFERENCE],
       [TYPE],
       [Attachments],
       [EMAIL],
       [EMAILFROM] AS [EMAILADDRESS],
       [EMAILTO] AS [ADDRESSTO],
       [EMAILSENT],
       [SUBJECT],
       [ShortText],
       [NAME],
       [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 * FROM [dbo].[KEYHM_FN_GetDocFolders](@matter, @DocFolderID, 1, 1) WHERE parent <> -1
    END
  ELSE
    BEGIN
      SELECT TOP 0
             CONVERT(INT, 0) AS [seq],
             CONVERT(INT, 0) AS [id],
             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

