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

CREATE PROCEDURE  
       [dbo].[KAAS_LS_GetCaseContactsList]  
      (@CaseCode	VARCHAR(20),  
       @ForEmail	BIT           = 1,  
       @Number		INT           = 0,  
       @Status		VARCHAR(1)	  = '',
	   @SearchText	VARCHAR(4000) = NULL
	   )  
AS  
/*************************************************************************************************************  
 * Stored Procedure Name: [KAAS_GetCaseContactsListForLegalSearch]														 
 * Copied From			: [KAAS_GetCaseContactsList]														 
 * 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
 * 2022-02-02    Vinodhkumar.M     Created - Case Contacts list based on Matter Code for Saas Web App		
 * 2022-02-03    Vinodhkumar.M     Modified - added Id from ClientContacts
 * 2022-02-08    Vinodhkumar.M     Modified - uncomment the query of ClientContacts, clients
 * 2022-02-24    Vinodhkumar.M     Modified - Display all contacts whether it is published or not
 * 2023-10-05    Nithyanandham.M   Added Address column with the return list
 * 2024-10-22    Aakif			   Included FirstName, Surname, Salut and PostalCode in the dataset
 *************************************************************************************************************/  
BEGIN  
SET NOCOUNT ON  
  DECLARE @FromWhom       VARCHAR(10)  
  DECLARE @FeeEarnerLabel VARCHAR(20)  
  DECLARE @Partner        VARCHAR(20)  
  
--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 *
    FROM (SELECT CONVERT(VARCHAR(10), RTRIM(ISNULL([CAN].[Code], '')))           AS [Code],  
                 CONVERT(VARCHAR(300), RTRIM(ISNULL([CAN].[Name], '')))          AS [FullName], 
				 LTRIM(RTRIM(ISNULL([CAN].[FORENAME], '')))						 AS [FirstName],
				 LTRIM(RTRIM(ISNULL([CAN].[SURNAME], '')))						 AS [Surname],
				 ISNULL([CAN].[SALUTATION], '')									 AS [Salutaion],
				 ISNULL([CAN].[PostalCode], '')									 AS [PostalCode],
                 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],
				 CONVERT(VARCHAR(50), '')										 AS [ClientNumberContact],
				 [CAN].[Address]                                                 AS [Address],
				 [CAN].[LEGALADDRESS]                                            AS [LegalAddress]
            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 [FullName],
				 LTRIM(RTRIM(ISNULL([CTC].[FIRSTNAME], '')))					 AS [FirstName],
				 LTRIM(RTRIM(ISNULL([CTC].[SURNAME], '')))						 AS [Surname],
				 ISNULL([CTC].[CLSALUTE], '')									 AS [Salutaion],
				 ISNULL([CTC].[PostalCode], '')									 AS [PostalCode],
                 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],
				 CONVERT(VARCHAR(50),ISNULL([CTC].[CLNUMCONTACT],''))			 AS [ClientNumberContact],
				 [CTC].[Address]                                                 AS [Address],
				 ''																 AS [LegalAddress]
            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 [FullName],
				 LTRIM(CASE 
						WHEN ISNULL([CAC].[Name], '') = '' 
							THEN ''  -- Handle empty or NULL names
						WHEN CHARINDEX(' ', [CAC].[Name]) = 0 
							THEN [CAC].[Name]  -- One word only, treat as FirstName
						ELSE
							SUBSTRING([CAC].[Name], 1, LEN([CAC].[Name]) - CHARINDEX(' ', REVERSE([CAC].[Name])))  -- Get FirstName
				 END)															 AS [FirstName],    
				 LTRIM(CASE 
						WHEN ISNULL([CAC].[Name], '') = '' 
							THEN ''  -- Handle empty or NULL names
						WHEN CHARINDEX(' ', [CAC].[Name]) = 0 
							THEN ''  -- One word only, no Surname
						ELSE
							REVERSE(LEFT(REVERSE([CAC].[Name]), CHARINDEX(' ', REVERSE([CAC].[Name])) - 1))  -- Get Surname
						END)													 AS [Surname],
				 ISNULL([CAC].[SALUTATION], '')									 AS [Salutaion],
				 ISNULL([CAC].[PostalCode], '')									 AS [PostalCode],
                 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],
				 CONVERT(VARCHAR(50), '')										 AS [ClientNumberContact],
				 [CAC].[Address]                                                 AS [Address],
				 ''																 AS [LegalAddress]
            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 [FullName], 
				 LTRIM(CASE 
						WHEN ISNULL([CTC].[Name], '') = '' 
							THEN ''  -- Handle empty or NULL names
						WHEN CHARINDEX(' ', [CTC].[Name]) = 0 
							THEN [CTC].[Name]  -- One word only, treat as FirstName
						ELSE
							SUBSTRING([CTC].[Name], 1, LEN([CTC].[Name]) - CHARINDEX(' ', REVERSE([CTC].[Name])))  -- Get FirstName
				 END)															 AS [FirstName],    
				 LTRIM(CASE 
						WHEN ISNULL([CTC].[Name], '') = '' 
							THEN ''  -- Handle empty or NULL names
						WHEN CHARINDEX(' ', [CTC].[Name]) = 0 
							THEN ''  -- One word only, no Surname
						ELSE
							REVERSE(LEFT(REVERSE([CTC].[Name]), CHARINDEX(' ', REVERSE([CTC].[Name])) - 1))  -- Get Surname
						END)													 AS [Surname],
				 ISNULL([CTC].[Salut], '')										 AS [Salutaion],
				 ISNULL([CTC].[PostalCode], '')									 AS [PostalCode],
                 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],
				 CONVERT(VARCHAR(50), '')										 AS [ClientNumberContact],
				 [CTC].[Address]                                                 AS [Address],
				 ''																 AS [LegalAddress]
            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 [FullName],
				 LTRIM(CASE 
						WHEN ISNULL([HAN].[Name], '') = '' 
							THEN ''  -- Handle empty or NULL names
						WHEN CHARINDEX(' ', [HAN].[Name]) = 0 
							THEN [HAN].[Name]  -- One word only, treat as FirstName
						ELSE
							SUBSTRING([HAN].[Name], 1, LEN([HAN].[Name]) - CHARINDEX(' ', REVERSE([HAN].[Name])))  -- Get FirstName
				 END)															 AS [FirstName],    
				 LTRIM(CASE 
						WHEN ISNULL([HAN].[Name], '') = '' 
							THEN ''  -- Handle empty or NULL names
						WHEN CHARINDEX(' ', [HAN].[Name]) = 0 
							THEN ''  -- One word only, no Surname
						ELSE
							REVERSE(LEFT(REVERSE([HAN].[Name]), CHARINDEX(' ', REVERSE([HAN].[Name])) - 1))  -- Get Surname
						END)													 AS [Surname],
				 ''																 AS [Salutaion],
				 ''																 AS [PostalCode],
                 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],
				 CONVERT(VARCHAR(50), '')										 AS [ClientNumberContact],
				 ''                                                              AS [Address],
				 ''																 AS [LegalAddress]
            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 [FullName],
				 LTRIM(CASE 
						WHEN ISNULL([CTC].[Name], '') = '' 
							THEN ''  -- Handle empty or NULL names
						WHEN CHARINDEX(' ', [CTC].[Name]) = 0 
							THEN [CTC].[Name]  -- One word only, treat as FirstName
						ELSE
							SUBSTRING([CTC].[Name], 1, LEN([CTC].[Name]) - CHARINDEX(' ', REVERSE([CTC].[Name])))  -- Get FirstName
				 END)															 AS [FirstName],    
				 LTRIM(CASE 
						WHEN ISNULL([CTC].[Name], '') = '' 
							THEN ''  -- Handle empty or NULL names
						WHEN CHARINDEX(' ', [CTC].[Name]) = 0 
							THEN ''  -- One word only, no Surname
						ELSE
							REVERSE(LEFT(REVERSE([CTC].[Name]), CHARINDEX(' ', REVERSE([CTC].[Name])) - 1))  -- Get Surname
						END)													 AS [Surname],
				 ISNULL([CTC].[Salut], '')										 AS [Salutaion],
				 ISNULL([CTC].[PostalCode], '')									 AS [PostalCode],
                 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],
				 CONVERT(VARCHAR(50), '')										 AS [ClientNumberContact],
				 [CTC].[Address]                                                 AS [Address],
				 [CLI].[CLLEGALADDR]											 AS [LegalAddress]
            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
				(
					   [FullName] 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].[FullName] = ''  
                   THEN [RES].[Company]  
              ELSE [RES].[FullName] END,  
         [RES].[TYPE]  
SET NOCOUNT OFF  
END  

GO

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

CREATE PROCEDURE
       [dbo].[KAAS_LS_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)	= '',
	   @DocumentIsNear   	BIT = 0,
	   @DocumentStartDate  	DATE='',
	   @DocumentEndDate  	DATE=''
	   )
AS
/************************************************************************************************************************
 *                                                                                                                      *
 * [dbo].[KAAS_LS_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-23   Sadiq             Added DiaryDate which was wrongly wrote as Date . (12469)
 * 2021-09-03   Aakif			  Included time for diary action and document											*
 * 2022-06-03   Vinodhkumar.M     INcluded signing Status in the list													*
 * 2022-09-05   Revathy.D         Fixed Random records missing issue                    						        *
 * 2022-12-12   Balamurugan.C     Added LastAccessDate column to the list for Brief Building Purpose                    *
 * 2022-12-19   Sadiq		      Added LastAccessDate column to the whole SP and fixed error in 15782                  *
 * 2023-07-07	Revathy 	      Added Source and Attachment fields for filter											*
 * 2023-08-23   Balamurugan.C     Copied entire SP and replicating for Brief Purpose and added OriginalMatterLinkId     *
 * 2023-10-17   Nithyanandham.M   Copied entire SP and replicating for Legal Search and Fetch Only PDF and Img Files    *
 ************************************************************************************************************************/
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),
		[SigningStatus]             VARCHAR(100),
		[LASTACCESSDATE]            DATETIME,
		[OriginalMatterLinkId]		INT,
		[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,
		[OriginalMatterLinkId]		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),
	   [SOURCE]       VARCHAR(2000),
	   [ATTACHMENT]   VARCHAR(2000))

DECLARE @DOCCLASSFilterCount INT = 0;
DECLARE @TypeFilterCount INT = 0;
DECLARE @AttachmentFilterCount INT = 0;
DECLARE @SourceFilterCount 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],[SOURCE],[ATTACHMENT]) values (@FilterValue,null,null,null)
	   ELSE IF(@ColumnName = 'Type')
	    insert into @CustomFilter (DOCCLASS,[TYPE],[SOURCE],[ATTACHMENT]) values (null,@FilterValue,null,null)
		 ELSE IF(@ColumnName = 'DocumentSource')
	   insert into @CustomFilter (DOCCLASS,[TYPE],[SOURCE],[ATTACHMENT]) values (null,null,@FilterValue,null)	   
	   ELSE IF(@ColumnName = 'Attachment')
	   insert into @CustomFilter (DOCCLASS,[TYPE],[SOURCE],[ATTACHMENT]) values (null,null,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 <>'')
	SET @SourceFilterCount = (SELECT COUNT(1) FROM @CustomFilter WHERE [SOURCE] IS NOT NULL AND [SOURCE] <>'')
	SET @AttachmentFilterCount = (SELECT  COUNT(1) FROM @CustomFilter WHERE [ATTACHMENT] IS NOT NULL AND [ATTACHMENT] <>'')
	
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.[DiaryAttachmentSelector] [DAS] 
 LEFT JOIN dbo.[DiaryAttachments] [DA]  ON [DAS].[TrackReference] = [DA].[TrackReference]
 LEFT JOIN dbo.[diary]  [DIA] ON  [DA].[DiaryID] = [DIA].[ACTIONID] and [DIA].CASECODE = [DA].CASECODE
WHERE [DAS].[CaseCode] = @matter
		AND
		[DA].DATEENTERED>=@DocumentStartDate
		AND
		[DA].DATEENTERED<=@DocumentEndDate
		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
	AND	
	 (CASE WHEN @AttachmentFilterCount = 0 THEN 0
		   WHEN [DIA].[Attachments] 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 [ATTACHMENT] from @CustomFilter WHERE [ATTACHMENT] IS NOT NULL and [ATTACHMENT] <>''),',','</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 @SourceFilterCount = 0 THEN 0
		   WHEN (RTRIM(ISNULL([DA].[SOURCE], CASE WHEN [DIA].[ACTIONID] IS NULL THEN 'IManage' ELSE '' END))) 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 [SOURCE] from @CustomFilter WHERE [SOURCE] IS NOT NULL and [SOURCE] <>''),',','</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  
[DAS].[CASECODE],[DAS].[ACTIONID],[DAS].[DiaryDate] [DATE],[DIA].[TxmDate], [DAS].[EMAIL],[DAS].[ATTACHMENTS],[DAS].[EMAILADDRESS],[DAS].[ADDRESSTO],[DAS].[SUBJECT],[DAS].[DisplayText],
		[DAS].[PROCESSTYPE],[DAS].[ACTIONTYPE],
		[DA].[DIARYID],[DAS].[TrackReference],[DAS].[DocClass],[DA].[DATEENTERED],[DAS].[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 [DAS].[ACTIONTYPE] = 'A'
                 THEN 'Action'
            WHEN [DAS].[ACTIONTYPE] = 'N'
                 THEN 'Note'
            WHEN [DAS].[ACTIONTYPE] = 'P'
                 THEN 'Appointment'
            WHEN [DAS].[ACTIONTYPE] = 'R'
                 THEN 'Reminder'
            WHEN [DAS].[ACTIONTYPE] = 'E'
                 THEN 'Email'
            WHEN [DAS].[ACTIONTYPE] = 'T'
                 THEN 'Phone Message'
            WHEN [DAS].[ACTIONTYPE] = 'D'
                 THEN 'Dictation'
            WHEN [DAS].[ACTIONTYPE] = 'U'
                 THEN 'Undertaking'
            WHEN [DAS].[ACTIONTYPE] = 'S'
                 THEN 'Statute Date'
            WHEN [DAS].[ACTIONTYPE] = 'C'
                 THEN 'Critical Date'
            WHEN [DAS].[ACTIONTYPE] = 'O'
                 THEN 'Court Date'
            WHEN [DAS].[ACTIONTYPE] = 'M'
                 THEN 'Scanned Post/Mail'
            WHEN RTRIM(ISNULL([DAS].[ACTIONTYPE], '')) = ''
                 THEN CASE WHEN convert(VARCHAR(1), CASE WHEN RTRIM(ISNULL([DAS].[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: ' + [DAS].[ACTIONTYPE] END AS [ActionTypeDescription],

			CASE WHEN convert(VARCHAR(10), ISNULL([DAS].[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],
					  [DAS].[SigningStatus] AS [SigningStatus],
					  [DA].[LASTACCESSDATE] AS [LASTACCESSDATE],
					  [DAI].OriginalUniqueID AS [OriginalMatterLinkId]
					  --,Count(1) Over() as TotalRowsCount
		INTO #DiaryTablesDoc
 FROM [DiaryAttachmentSelector] [DAS] 
 LEFT JOIN dbo.[DiaryAttachments] [DA]  ON  [DAS].[TrackReference] = [DA].[TrackReference]
 LEFT JOIN dbo.[diary]  [DIA] 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
LEFT JOIN [dbo].[diaryAttachmentOriginalmatterlinks] [DAI] ON  [DAI].[TrackReference] = [DAS].[TrackReference]
 WHERE [DAS].[CaseCode] = @matter
		AND
		[DA].DATEENTERED>=@DocumentStartDate
		AND
		[DA].DATEENTERED<=@DocumentEndDate
		AND
		(
	 (CASE WHEN @DOCCLASSFilterCount = 0 THEN 0
		   WHEN [DAS].[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  [DAS].[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
	AND	
	 (CASE WHEN @AttachmentFilterCount = 0 OR [DAS].[ATTACHMENTS] 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 [ATTACHMENT] from @CustomFilter WHERE [ATTACHMENT] IS NOT NULL and [ATTACHMENT] <>''),',','</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 @SourceFilterCount = 0 OR [DA].[SOURCE] 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 [SOURCE] from @CustomFilter WHERE [SOURCE] IS NOT NULL and [SOURCE] <>''),',','</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]
		 ,[SigningStatus]
		 ,[LASTACCESSDATE],
		 [OriginalMatterLinkId]
		-- ,[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
	 ,[DAS].[SigningStatus]
	 ,CONVERT(DATETIME, [DIA].[LASTACCESSDATE]) AS [LASTACCESSDATE],
	 [DAI].OriginalUniqueID AS [OriginalMatterLinkId]
    FROM #DiaryTablesDoc [DIA]
		-- INNER JOIN dbo.[matters] [MAT]			ON [MAT].[CODE] = [DIA].[CASECODE]
			LEFT JOIN [DiaryAttachmentSelector] [DAS] ON [DAS].[TrackReference] = [DIA].[TrackReference]
                    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]
   
   LEFT JOIN [dbo].[diaryAttachmentOriginalmatterlinks] [DAI] ON  [DAI].[TrackReference] = [DIA].[TrackReference]
   
END

ELSE
BEGIN
SELECT @TOTALRECORDS = Count(1)  FROM  
dbo.[DiaryAttachmentSelector] [DAS] 
 LEFT JOIN dbo.[DiaryAttachments] [DA]  ON [DAS].[TrackReference] = [DA].[TrackReference]
 LEFT JOIN dbo.[diary]  [DIA] ON  [DA].[DiaryID] = [DIA].[ACTIONID] and [DIA].CASECODE = [DA].CASECODE
WHERE [DAS].[CaseCode] = @matter
		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
	AND	
	 (CASE WHEN @AttachmentFilterCount = 0 THEN 0
		   WHEN [DAS].[Attachments] 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 [ATTACHMENT] from @CustomFilter WHERE [ATTACHMENT] IS NOT NULL and [ATTACHMENT] <>''),',','</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 @SourceFilterCount = 0 THEN 0
		   WHEN (RTRIM(ISNULL([DA].[SOURCE], CASE WHEN [DAS].[ACTIONID] IS NULL THEN 'IManage' ELSE '' END))) 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 [SOURCE] from @CustomFilter WHERE [SOURCE] IS NOT NULL and [SOURCE] <>''),',','</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  
[DAS].[CASECODE],[DAS].[ACTIONID],[DAS].[DiaryDate] [DATE],[DIA].[TxmDate], [DAS].[EMAIL],[DAS].[ATTACHMENTS],[DAS].[EMAILADDRESS],[DAS].[ADDRESSTO],[DAS].[SUBJECT],[DAS].[DisplayText],
		[DAS].[PROCESSTYPE],[DAS].[ACTIONTYPE],
		[DA].[DIARYID],[DAS].[TrackReference],[DAS].[DocClass],[DA].[DATEENTERED],[DAS].[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 [DAS].[ACTIONTYPE] = 'A'
                 THEN 'Action'
            WHEN [DAS].[ACTIONTYPE] = 'N'
                 THEN 'Note'
            WHEN [DAS].[ACTIONTYPE] = 'P'
                 THEN 'Appointment'
            WHEN [DAS].[ACTIONTYPE] = 'R'
                 THEN 'Reminder'
            WHEN [DAS].[ACTIONTYPE] = 'E'
                 THEN 'Email'
            WHEN [DAS].[ACTIONTYPE] = 'T'
                 THEN 'Phone Message'
            WHEN [DAS].[ACTIONTYPE] = 'D'
                 THEN 'Dictation'
            WHEN [DAS].[ACTIONTYPE] = 'U'
                 THEN 'Undertaking'
            WHEN [DAS].[ACTIONTYPE] = 'S'
                 THEN 'Statute Date'
            WHEN [DAS].[ACTIONTYPE] = 'C'
                 THEN 'Critical Date'
            WHEN [DAS].[ACTIONTYPE] = 'O'
                 THEN 'Court Date'
            WHEN [DAS].[ACTIONTYPE] = 'M'
                 THEN 'Scanned Post/Mail'
            WHEN RTRIM(ISNULL([DAS].[ACTIONTYPE], '')) = ''
                 THEN CASE WHEN convert(VARCHAR(1), CASE WHEN RTRIM(ISNULL([DAS].[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: ' + [DAS].[ACTIONTYPE] END AS [ActionTypeDescription],

			CASE WHEN convert(VARCHAR(10), ISNULL([DAS].[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
					  ,[DAS].[SigningStatus]
					  ,[DA].[LASTACCESSDATE] AS [LASTACCESSDATE]
					  ,[DAI].OriginalUniqueID AS [OriginalMatterLinkId]
		INTO #DiaryTables
  FROM [DiaryAttachmentSelector] [DAS] 
 LEFT JOIN dbo.[DiaryAttachments] [DA]  ON [DAS].[TrackReference] = [DA].[TrackReference]
 LEFT JOIN dbo.[diary]  [DIA] 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
 LEFT JOIN [dbo].[diaryAttachmentOriginalmatterlinks] [DAI] ON  [DAS].[TrackReference] = [DAI].[TrackReference]
 WHERE [DAS].[CaseCode] = @matter
		AND
		(
		CASE WHEN @SearchText = '' THEN 1
			 WHEN 
			([DA].FILEPATH LIKE '%' + @SearchText + '%')
			OR
			(RTRIM(convert(VARCHAR(2000), ISNULL([DL].[Description], 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
	AND	
	 (CASE WHEN @AttachmentFilterCount = 0 OR [DAS].[ATTACHMENTS] 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 [ATTACHMENT] from @CustomFilter WHERE [ATTACHMENT] IS NOT NULL and [ATTACHMENT] <>''),',','</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 @SourceFilterCount = 0 OR [DA].[SOURCE] 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 [SOURCE] from @CustomFilter WHERE [SOURCE] IS NOT NULL and [SOURCE] <>''),',','</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],
		 [SigningStatus],
		 [LASTACCESSDATE],
		 [OriginalMatterLinkId]
		-- ,[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
	 [DAS].[SigningStatus],
	 [DIA].[LASTACCESSDATE] AS [LASTACCESSDATE],
	 [DAI].OriginalUniqueID AS [OriginalMatterLinkId]
    FROM #DiaryTables [DIA]
	LEFT JOIN [DiaryAttachmentSelector] [DAS] ON [DAS].[TrackReference] = [DIA].[TrackReference]
		-- 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]

   LEFT JOIN [dbo].[diaryAttachmentOriginalmatterlinks] [DAI] ON  [DIA].[TrackReference] = [DAI].[TrackReference]
   
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
		(
	 (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
	AND	
	 (CASE WHEN @AttachmentFilterCount = 0 THEN 0
		   WHEN [DIA].[Attachments] 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 [ATTACHMENT] from @CustomFilter WHERE [ATTACHMENT] IS NOT NULL and [ATTACHMENT] <>''),',','</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 @SourceFilterCount = 0 THEN 0
		   WHEN (RTRIM(ISNULL([DA].[SOURCE], CASE WHEN [DIA].[ACTIONID] IS NULL THEN 'IManage' ELSE '' END))) 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 [SOURCE] from @CustomFilter WHERE [SOURCE] IS NOT NULL and [SOURCE] <>''),',','</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],
					  [DA].[LASTACCESSDATE] AS [LASTACCESSDATE],
					  [DAI].OriginalUniqueID AS [OriginalMatterLinkId]
					 -- , 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
LEFT JOIN [dbo].[diaryAttachmentOriginalmatterlinks] [DAI] ON  [DA].[TrackReference] = [DAI].[TrackReference]

 WHERE [DDC].[DocFolderID] = @DocFolderID 
		AND
		[DA].DATEENTERED>=@DocumentStartDate
		AND
		[DA].DATEENTERED<=@DocumentEndDate
		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
	AND	
	 (CASE WHEN @AttachmentFilterCount = 0 OR [Attachments] 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 [ATTACHMENT] from @CustomFilter WHERE [ATTACHMENT] IS NOT NULL and [ATTACHMENT] <>''),',','</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 @SourceFilterCount = 0 OR [SOURCE] 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 [SOURCE] from @CustomFilter WHERE [SOURCE] IS NOT NULL and [SOURCE] <>''),',','</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],
		 [SigningStatus],
		 [LASTACCESSDATE],
		 [OriginalMatterLinkId]

		 )
  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],
	   [DAS].[SigningStatus],
	   [DIA].[LASTACCESSDATE] AS [LASTACCESSDATE],
	   [DAI].OriginalUniqueID AS [OriginalMatterLinkId]
    FROM  #DiaryTableListDOc [DIA]
	LEFT JOIN [DiaryAttachmentSelector] [DAS] ON [DAS].[TrackReference] = [DIA].[TrackReference]
                 --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]
   LEFT JOIN [dbo].[diaryAttachmentOriginalmatterlinks] [DAI] ON  [DIA].[TrackReference] = [DAI].[TrackReference]

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
		(
		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
	AND	
	 (CASE WHEN @AttachmentFilterCount = 0 THEN 0
		   WHEN [DIA].[Attachments] 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 [ATTACHMENT] from @CustomFilter WHERE [ATTACHMENT] IS NOT NULL and [ATTACHMENT] <>''),',','</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 @SourceFilterCount = 0 THEN 0
		   WHEN (RTRIM(ISNULL([DA].[SOURCE], CASE WHEN [DIA].[ACTIONID] IS NULL THEN 'IManage' ELSE '' END))) 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 [SOURCE] from @CustomFilter WHERE [SOURCE] IS NOT NULL and [SOURCE] <>''),',','</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],
					  [DA].[LASTACCESSDATE] AS [LASTACCESSDATE],
					  [DAI].OriginalUniqueID AS [OriginalMatterLinkId]
					 -- , 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
LEFT JOIN [dbo].[diaryAttachmentOriginalmatterlinks] [DAI] ON  [DA].[TrackReference] = [DAI].[TrackReference]

 WHERE [DDC].[DocFolderID] = @DocFolderID 
		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
	AND	
	 (CASE WHEN @AttachmentFilterCount = 0 OR [Attachments] 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 [ATTACHMENT] from @CustomFilter WHERE [ATTACHMENT] IS NOT NULL and [ATTACHMENT] <>''),',','</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 @SourceFilterCount = 0 OR [SOURCE] 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 [SOURCE] from @CustomFilter WHERE [SOURCE] IS NOT NULL and [SOURCE] <>''),',','</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],
		[SigningStatus],
		[LASTACCESSDATE],
		[OriginalMatterLinkId]
		 )
  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],
	   [DAS].[SigningStatus],
	   [DIA].[LASTACCESSDATE] AS [LASTACCESSDATE],
	   [DAI].OriginalUniqueID AS [OriginalMatterLinkId]
    FROM  #DiaryTableList [DIA]
	LEFT JOIN [DiaryAttachmentSelector] [DAS] ON [DAS].[TrackReference] = [DIA].[TrackReference]
                 --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]
		LEFT JOIN [dbo].[diaryAttachmentOriginalmatterlinks] [DAI] ON  [DIA].[TrackReference] = [DAI].[TrackReference]
   
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
		(
		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
	AND	
	 (CASE WHEN @AttachmentFilterCount = 0 THEN 0
		   WHEN [DIA].[Attachments] 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 [ATTACHMENT] from @CustomFilter WHERE [ATTACHMENT] IS NOT NULL and [ATTACHMENT] <>''),',','</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 @SourceFilterCount = 0 THEN 0
		   WHEN (RTRIM(ISNULL([DA].[SOURCE], CASE WHEN [DIA].[ACTIONID] IS NULL THEN 'IManage' ELSE '' END))) 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 [SOURCE] from @CustomFilter WHERE [SOURCE] IS NOT NULL and [SOURCE] <>''),',','</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],
					  [DA].[LASTACCESSDATE] AS [LASTACCESSDATE],
					  [DAI].OriginalUniqueID AS [OriginalMatterLinkId]
					  --,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
LEFT JOIN [dbo].[diaryAttachmentOriginalmatterlinks] [DAI] ON  [DA].[TrackReference] = [DAI].[TrackReference]

 WHERE [DIA].[ACTIONID] = @ActionID 		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
	AND	
	 (CASE WHEN @AttachmentFilterCount = 0 OR [Attachments] 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 [ATTACHMENT] from @CustomFilter WHERE [ATTACHMENT] IS NOT NULL and [ATTACHMENT] <>''),',','</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 @SourceFilterCount = 0 OR [SOURCE]  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 [SOURCE] from @CustomFilter WHERE [SOURCE] IS NOT NULL and [SOURCE] <>''),',','</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],
		 SigningStatus,
		 [LASTACCESSDATE],
		 [OriginalMatterLinkId]
		 )
  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],
	  [DAS].[SigningStatus],
	  [DIA].[LASTACCESSDATE] AS [LASTACCESSDATE],
	  [DAI].OriginalUniqueID AS [OriginalMatterLinkId]
    FROM #DiaryTable [DIA]
						LEFT JOIN [DiaryAttachmentSelector] [DAS] ON [DAS].[TrackReference] = [DIA].[TrackReference]
                    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]
		LEFT JOIN [dbo].[diaryAttachmentOriginalmatterlinks] [DAI] ON  [DIA].[TrackReference] = [DAI].[TrackReference]
   
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],
       CONVERT(VARCHAR(15),dbo.KAAS_FNConvertUTCDateToTargetTimeZone([DiaryDate], @LoggedInHandler), 23)  AS [DiaryDate],
       CONVERT(VARCHAR(10),dbo.KAAS_FNConvertUTCDateToTargetTimeZone([DiaryDate], @LoggedInHandler), 108)  AS [DiaryTime],
       [ACTIONID],
       CONVERT(VARCHAR(15), dbo.KAAS_FNConvertUTCDateToTargetTimeZone([DocumentDate], @LoggedInHandler), 23) AS [DocumentDate],
       CONVERT(VARCHAR(10), dbo.KAAS_FNConvertUTCDateToTargetTimeZone([DocumentDate], @LoggedInHandler), 108) AS [DocumentTime],
       [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,
	   [SigningStatus],
	   CONVERT(VARCHAR(15),dbo.KAAS_FNConvertUTCDateToTargetTimeZone([LASTACCESSDATE], @LoggedInHandler), 23)  AS [LastAccessDate],
	   [OriginalMatterLinkId]
	from @res
	WHERE [TYPE] = 'PDF' or [TYPE]='JPG'
	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].[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 [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

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

CREATE PROCEDURE
       [dbo].[KAAS_LS_GetFolioList]
	   (@CaseCode	VARCHAR(50),
	   @UDFName	VARCHAR(50))
AS
/****************************************************************************************************************
 *
 * Stored Procedure Name -   KAAS_GetFolioList
 *
 * This stored procedure used to fetch the Folio List 
 *
 *
 * Modification History
 * 2023-10-04   Nithyanandham.M       Created

 ****************************************************************************************************************/
BEGIN

SELECT 
    [CaseCode]                     ,
	[UDFFile]                      ,
	[UDFName]                      ,
	[SeqNo]                        ,
	[DateValue]                    ,
	[NumberValue]                  ,
	[Text1]                as Folio
    FROM [CaseUDFAnswers] 
	WHERE [udfName] = @UDFName and [CaseCode] = @CaseCode
END
GO

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

CREATE PROCEDURE
       [dbo].[KAAS_LS_GetOpenMatterDetails]
	    ( 
			 @Search        NVARCHAR(MAX),    
			 @PageNumber    INT,    
			 @PageSize      INT,
	         @ColumnNames   VARCHAR(50) = '',
	         @FilterValues  VARCHAR(50) = '',
	         @orderby       NVARCHAR(50)= '',    
             @order         NVARCHAR(4) = '',
			 @Mode			VARCHAR(10) = 'Grid' 
		 )
AS
/************************************************************************************ *
 * Fetching Open Matter Details														  *
 * Stored Procedure Name : [dbo].[KAAS_LS_GetOpenMatterDetails]                       *
 * Copied from			 : [dbo].[KAAS_OffsetGetOpenMatterDetails]                    *
 * Modification History:  
 * 2024-07-25    Ponni M    Created                                                   *
 * 2024-07-25    Ponni M    #18734 - Created to Fetch the Browse Matter details for
							LAWLINK 	
************************************************************************************/
BEGIN
      

DECLARE @closed                 NCHAR(1)      
DECLARE @howmany                INT
DECLARE @handler                NVARCHAR(10)
DECLARE @which                  INT      
DECLARE @BitWiseID              BIGINT      
DECLARE @searchtext             VARCHAR(MAX)      
DECLARE @searchonlycode         VARCHAR(3)      
DECLARE @departmentdesc         VARCHAR(50)      
DECLARE @department             VARCHAR(5)      
DECLARE @worktypedesc           VARCHAR(50)      
DECLARE @worktype               VARCHAR(5)      
DECLARE @filecolourdesc         VARCHAR(20)      
DECLARE @filecolour             VARCHAR(3)      
DECLARE @feecode                VARCHAR(10)      
      
DECLARE @includename            BIT      
DECLARE @includedescription     BIT      
DECLARE @includeaddress         BIT      
DECLARE @includeemail           BIT      
DECLARE @includecode            BIT      
DECLARE @includeoldref          BIT      
DECLARE @includeuser1           BIT      
      
DECLARE @andorname              VARCHAR(3)      
DECLARE @andornamevalue         VARCHAR(200)      
DECLARE @andordescription       VARCHAR(3)      
DECLARE @andordescriptionvalue  VARCHAR(200)      
DECLARE @andoraddress           VARCHAR(3)      
DECLARE @andoraddressvalue      VARCHAR(200)      
DECLARE @andorcode              VARCHAR(3)      
DECLARE @andorcodevalue         VARCHAR(200)      
DECLARE @andorfeecode           VARCHAR(3)      
DECLARE @andorfeecodevalue      VARCHAR(200)      
DECLARE @andoroldref            VARCHAR(3)      
DECLARE @andoroldrefvalue       VARCHAR(200)      
DECLARE @andordept              VARCHAR(3)      
DECLARE @andordeptvalue         VARCHAR(200)      
DECLARE @andoruser1             VARCHAR(3)      
DECLARE @andoruser1value        VARCHAR(200)      
DECLARE @andoruser2             VARCHAR(3)      
DECLARE @andoruser2value        VARCHAR(200)      
DECLARE @andoruser3             VARCHAR(3)      
DECLARE @andoruser3value        VARCHAR(200)      
DECLARE @andoryourref           VARCHAR(3)      
DECLARE @andoryourrefvalue      VARCHAR(200)      
DECLARE @andorwtype             VARCHAR(3)      
DECLARE @andorwtypevalue        VARCHAR(200)      
      
DECLARE @iSL INT      
    
DECLARE @StartRow INT;      
DECLARE @EndRow INT;

--FOR DEFAULT DATE; USE OF PARAMETERIZATION
DECLARE @DefaultDate VARCHAR(8) = '18000101';
DECLARE @DefaultDaybookDate VARCHAR(8) = '19000101';

SET NOCOUNT ON
    
SET @PageNumber = ISNULL(@PageNumber, 0) - 1; -- 0 BASED INDEX
        
IF(@PageNumber > -1)
BEGIN        
	SET @StartRow = ((@PageNumber) * @PageSize) + 1;
	SET @EndRow = (@StartRow + @PageSize) - 1;
END   
      
BEGIN TRY      
EXEC sp_xml_preparedocument @iSL OUTPUT, @Search      
END TRY      
BEGIN CATCH      
EXEC sp_xml_preparedocument @iSL OUTPUT, N'<search/>'      
END CATCH      
      
SELECT @closed = CASE ISNULL(SRC.[closed], N'A')      
                      WHEN N'Y' THEN N'Y'   
                      WHEN N'B' THEN N'B'    
					  WHEN N'N' THEN N'N'   
					  WHEN N'A' THEN N'A'   
                       END,      
       @orderby = ISNULL(SRC.[orderby], N''),      
       @order = CASE ISNULL(SRC.[order], N'DESC')
                     WHEN N'DESC'      
                          THEN N'DESC'      
                     ELSE N'ASC' END,      
       @howmany = ISNULL(SRC.[howmany], 500),      
       @handler = ISNULL(SRC.[handler], N''),      
       @which = CASE ISNULL(SRC.[which], 0)      
                     WHEN 1 THEN 1 --My Matters      
                     WHEN 2 THEN 2 --Recent Matters      
                     ELSE 0 END,   --All Matters      
       @searchtext = ISNULL(SRC.[search], ''),      
       @searchonlycode = ISNULL(SRC.[searchonlycode], ''),      
       @departmentdesc = ISNULL(SRC.[dept], ''),      
       @worktypedesc = ISNULL(SRC.[wtype], ''),      
       @filecolourdesc = ISNULL(SRC.[colour], ''),      
       @feecode = ISNULL(SRC.[feecodefilter], ''),      
       @includeaddress = ISNULL(SRC.[includeaddress], 0),      
       @includeemail = ISNULL(SRC.[includeemail], 0),      
       @includecode = ISNULL(SRC.[includecode], 0),      
       @includedescription = ISNULL(SRC.[includedescription], 0),      
       @includename = ISNULL(SRC.[includename], 0),      
       @includeoldref = ISNULL(SRC.[includeoldref], 0),      
       @includeuser1 = ISNULL(SRC.[includeuser1], 0),      
       @andorname = ISNULL(SRC.[andorname], ''),      
       @andornamevalue = ISNULL(SRC.[andornamevalue], ''),      
       @andordescription = ISNULL(SRC.[andordescription], ''),      
       @andordescriptionvalue = ISNULL(SRC.[andordescriptionvalue], ''),      
       @andoraddress = ISNULL(SRC.[andoraddress], ''),      
       @andoraddressvalue = ISNULL(SRC.[andoraddressvalue], ''),      
       @andorcode = ISNULL(SRC.[andorcode], ''),      
       @andorcodevalue = ISNULL(SRC.[andorcodevalue], ''),      
       @andorfeecode = ISNULL(SRC.[andorfeecode], ''),      
       @andorfeecodevalue = ISNULL(SRC.[andorfeecodevalue], ''),      
       @andoroldref = ISNULL(SRC.[andoroldref], ''),      
       @andoroldrefvalue = ISNULL(SRC.[andoroldrefvalue], ''),      
       @andordept = ISNULL(SRC.[andordept], ''),      
       @andordeptvalue = ISNULL(SRC.[andordeptvalue], ''),      
       @andoruser1 = ISNULL(SRC.[andoruser1], ''),      
       @andoruser1value = ISNULL(SRC.[andoruser1value], ''),      
       @andoruser2 = ISNULL(SRC.[andoruser2], ''),      
       @andoruser2value = ISNULL(SRC.[andoruser2value], ''),      
       @andoruser3 = ISNULL(SRC.[andoruser3], ''),      
       @andoruser3value = ISNULL(SRC.[andoruser3value], ''),      
       @andoryourref = ISNULL(SRC.[andoryourref], ''),      
       @andoryourrefvalue = ISNULL(SRC.[andoryourrefvalue], ''),      
       @andorwtype = ISNULL(SRC.[andorwtype], ''),      
       @andorwtypevalue = ISNULL(SRC.[andorwtypevalue], '')      
  FROM OPENXML(@iSL, N'search')      
  WITH ([closed]                   NCHAR(1)        '@closed',      
        [orderby]                  NVARCHAR(50)    '@orderby',      
        [order]                    NVARCHAR(4)     '@order',      
        [howmany]                  INT             '@howmany',      
        [handler]                  NVARCHAR(10)    '@handler',      
        [which]                    INT             '@which',      
        [search]                   VARCHAR(MAX)    '@search',      
        [searchonlycode]           VARCHAR(3)      '@searchonlycode',      
        [dept]                     VARCHAR(50)     '@departmentfilter',      
        [wtype]                    VARCHAR(50)     '@worktypefilter',      
        [colour]                   VARCHAR(20)     '@filecolourfilter',      
        [feecodefilter]            VARCHAR(10)     '@feecodefilter',      
        [includeaddress]           BIT             'include/includefield[@name="address"]',      
        [includecode]              BIT             'include/includefield[@name="code"]',      
        [includedescription]       BIT             'include/includefield[@name="description"]',      
        [includename]              BIT             'include/includefield[@name="name"]',      
        [includeoldref]            BIT             'include/includefield[@name="oldref"]',      
        [includeuser1]             BIT             'include/includefield[@name="user1"]',      
        [includeemail]             BIT             'include/includefield[@name="email"]',      
        [andorname]                VARCHAR(3)      'andor/andorfield[@name="name"]/@andor',      
        [andornamevalue]           VARCHAR(100)    'andor/andorfield[@name="name"]/@search',      
        [andordescription]         VARCHAR(3)      'andor/andorfield[@name="description"]/@andor',      
        [andordescriptionvalue]    VARCHAR(100)    'andor/andorfield[@name="description"]/@search',      
        [andoraddress]             VARCHAR(3)      'andor/andorfield[@name="address"]/@andor',      
        [andoraddressvalue]        VARCHAR(100)    'andor/andorfield[@name="address"]/@search',      
        [andorcode]                VARCHAR(3)      'andor/andorfield[@name="code"]/@andor',      
        [andorcodevalue]           VARCHAR(100)    'andor/andorfield[@name="code"]/@search',      
        [andorfeecode]             VARCHAR(3)      'andor/andorfield[@name="feecode"]/@andor',      
        [andorfeecodevalue]        VARCHAR(100)    'andor/andorfield[@name="feecode"]/@search',      
        [andoroldref]              VARCHAR(3)      'andor/andorfield[@name="oldref"]/@andor',      
        [andoroldrefvalue]         VARCHAR(100)    'andor/andorfield[@name="oldref"]/@search',      
        [andordept]                VARCHAR(3)      'andor/andorfield[@name="dept"]/@andor',      
        [andordeptvalue]           VARCHAR(100)    'andor/andorfield[@name="dept"]/@search',      
        [andoruser1]               VARCHAR(3)      'andor/andorfield[@name="user1"]/@andor',      
        [andoruser1value]          VARCHAR(100)    'andor/andorfield[@name="user1"]/@search',      
        [andoruser2]               VARCHAR(3)      'andor/andorfield[@name="user2"]/@andor',      
        [andoruser2value]          VARCHAR(100)    'andor/andorfield[@name="user2"]/@search',      
        [andoruser3]               VARCHAR(3)      'andor/andorfield[@name="user3"]/@andor',      
        [andoruser3value]          VARCHAR(100)    'andor/andorfield[@name="user3"]/@search',      
        [andoryourref]             VARCHAR(3)      'andor/andorfield[@name="yourref"]/@andor',      
        [andoryourrefvalue]        VARCHAR(100)    'andor/andorfield[@name="yourref"]/@search',      
        [andorwtype]               VARCHAR(3)      'andor/andorfield[@name="wtype"]/@andor',      
        [andorwtypevalue]          VARCHAR(100)    'andor/andorfield[@name="wtype"]/@search') SRC      
      
IF @filecolourdesc <> ''      
  BEGIN      
    SELECT @filecolour = FC.[COLOURCODE]      
      FROM [dbo].[FileColours] FC WITH (NOLOCK)      
     WHERE FC.[COLOURDESC] = @filecolourdesc      
  END      
      
SET @filecolour = ISNULL(@filecolour, '')      
      
IF @departmentdesc <> ''      
  BEGIN      
    SELECT @department = DP.[CODE]      
      FROM [dbo].[Departments] DP WITH (NOLOCK)      
     WHERE DP.[DESCRIPTION] = @departmentdesc      
  END      
      
SET @department = ISNULL(@department, '')      
      
IF @worktypedesc <> ''      
  BEGIN      
    IF @worktypedesc LIKE 'Unknown Worktype: %'      
      BEGIN      
        SELECT @worktype = WT.[CODE]      
          FROM [dbo].[WorkTypes] WT WITH (NOLOCK)      
         WHERE 'Unknown Worktype: ' + WT.[CODE] = @worktypedesc      
            OR WT.[DESC] = @worktypedesc      
      END      
    ELSE      
      BEGIN      
        SELECT @worktype = WT.[CODE]      
          FROM [dbo].[WorkTypes] WT WITH (NOLOCK)      
         WHERE WT.[DESC] = @worktypedesc      
      END      
  END      
      
SET @worktype = ISNULL(@worktype, '')      
      
SET @feecode = ISNULL(@feecode, '')      
      
EXEC sp_xml_removedocument @iSL      
      
DECLARE @NCOMMAND NVARCHAR(MAX)      
DECLARE @HasWhere BIT      
      
SET @HasWhere = 0      
      
SET @handler = CASE WHEN RTRIM(ISNULL(@handler, '')) = '' THEN 'ADM' ELSE RTRIM(@handler) END      
      
SELECT @BitWiseID = HNG.[BitWiseID]      
  FROM HandlerNETGroupMembership HNG WITH (NOLOCK)      
 WHERE HNG.[HANDLER] = CONVERT(VARCHAR(10), @handler)      
      
SET @BitWiseID = ISNULL(@BitWiseID, [dbo].[KAAS_FN_GetOldPermissions](RTRIM(ISNULL(@handler, ''))))      
      
IF (@handler = 'ADM')      
  BEGIN      
    SET @BitWiseID = CONVERT(BIGINT, -1)      
  END     
  
  -- Custom Filter Start
SET @NCOMMAND = N'   
DECLARE @StatusCount INT = 0;
IF @ColumnNames <> ''''
BEGIN
DECLARE @CustomFilter TABLE
       ([Status]     VARCHAR(20))	

	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  

	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 = ''Closed'')
		INSERT INTO @CustomFilter ([Status]) VALUES (@FilterValue)	   

	   SET @lptcnt = @lptcnt + 1;   

	END
	
	SET @StatusCount = (SELECT COUNT(1) FROM @CustomFilter WHERE [Status] IS NOT NULL AND [Status] <>'''')	
	
END '
-- Custom Filter End

SET @NCOMMAND = @NCOMMAND + N'      
  --SELECT ''How Many: '' + CONVERT(VARCHAR(10), @HowMany)      
  --SELECT ''Handler: '' + @Handler      
  --SELECT ''BitWise ID: '' + CONVERT(VARCHAR(10), @BitWiseID)      
      
DECLARE @Result TABLE      
       ([id]                    INT                       IDENTITY(1, 1) PRIMARY KEY CLUSTERED,      
        [Time]                  VARCHAR(5)                NOT NULL,      
        [Code]                  VARCHAR(20)               NOT NULL,      
        [FileColour]            VARCHAR(20)               NOT NULL,      
        [FileColour_Fld]        VARCHAR(20)               NOT NULL,      
        [BackgroundColour]      VARCHAR(30)               NOT NULL,      
        [TextColour]            VARCHAR(30)               NOT NULL,      
        [Name]                  VARCHAR(256)              NOT NULL,      
        [Description]           VARCHAR(200)              NOT NULL,      
        [Fe]                    VARCHAR(30)               NOT NULL,
		    [FECode]                VARCHAR(10)               NOT NULL,     
        [FeeEarner]             VARCHAR(30)               NOT NULL,      
        [User1]                 VARCHAR(100)              NOT NULL,      
        [User2]                 VARCHAR(100)              NOT NULL,      
        [User3]                 VARCHAR(100)              NOT NULL,      
        [Started]               DATETIME                  NOT NULL,      
        [Dept]                  VARCHAR(50)               NOT NULL,      
        [WorkType]              VARCHAR(50)               NOT NULL,      
        [Privileges]            BIGINT                    NOT NULL,      
        [OldRef]                VARCHAR(12)               NOT NULL,      
        [Address]               VARCHAR(500)              NOT NULL,      
        [Email]                 VARCHAR(50)               NOT NULL,      
        [Closed]                CHAR(1)                   NOT NULL,      
        [OrgCloseDate]          DATETIME                      NULL,      
        [CloseDate]             DATETIME                      NULL,      
        [FileRGBColour]         INT                       NOT NULL,      
        [LastAccessed]          DATETIME                      NULL,      
        [PhoneNumber]           VARCHAR(50)               NOT NULL,      
        [FileNum]               INT                       NOT NULL,      
        [ClientCode]            VARCHAR(10)               NOT NULL,      
        [DepartmentCode]        VARCHAR(5)                NOT NULL,      
        [FeeEarnerTeam]         VARCHAR(10)               NOT NULL,      
        [TeamName]              VARCHAR(30)               NOT NULL,      
        [YourRef]               VARCHAR(100)              NOT NULL,      
        [CaseStatusCode]        VARCHAR(6)                NOT NULL,      
        [CaseStatusDesc]        VARCHAR(50)               NOT NULL,
		[OutlayBal]				DECIMAL(9)					 NULL,
		[ClientCurBal]          DECIMAL(9)                   NULL,
		[ClientDepBal]          DECIMAL(9)                   NULL,
		[Comment]				        VARCHAR(3000)			       NOT NULL,
		[Branch]                VARCHAR(3)					         NULL,
		[OpenInvoiceNo]         INT                      NOT NULL,
		[ContactName]			VARCHAR(256)			 NULL,
		[MatterDesc]            VARCHAR(200)			 NULL,
		[CompBillingMatter]     VARCHAR(20)              NULL,
		[CompMatter]			VARCHAR(20)              NULL
		)      
      
  INSERT      
    INTO @Result      
        ([Time],      
         [Code],      
         [FileColour],
         [FileColour_Fld],      
         [BackgroundColour],      
         [TextColour],      
         [Name],      
         [Description],      
         [Fe],      
         [FECode],      
         [FeeEarner],      
         [User1],      
         [User2],      
         [User3],      
         [Started],      
         [Dept],      
         [WorkType],      
		 [Privileges],      
         [OldRef],      
         [Address],      
         [Email],      
         [Closed],      
         [OrgCloseDate],      
         [CloseDate],      
         [FileRGBColour],      
         [LastAccessed],      
         [PhoneNumber],      
         [FileNum],      
         [ClientCode],      
         [DepartmentCode],      
         [FeeEarnerTeam],      
         [TeamName],      
         [YourRef],      
         [CaseStatusCode],      
         [CaseStatusDesc],
		 [OutlayBal],
		 [ClientCurBal],
		 [ClientDepBal],
		 [Comment],
		 [Branch],
		 [OpenInvoiceNo],
		 [ContactName],
		 [MatterDesc],
		 [CompBillingMatter],
		 [CompMatter]
		 )      
    
  SELECT      
         CASE WHEN TTT.[TOTALTIME] = ''00:00''                   THEN ''''      
              ELSE TTT.[TOTALTIME] END													  AS [Time],      
         SM.[Code]                                                                        AS [Code],      
         SM.[FileColourDescription]                                                       AS [FileColour],      
         SM.[FileColourDescription]                                                       AS [FileColour_Fld],      
         SM.[FileBackgroundColour]                                                        AS [BackgroundColour],      
         SM.[TextColour]                                                                  AS [TextColour],      
         SM.[Name]                                                                        AS [Name],      
         RTRIM(ISNULL(SM.[Description],''''))                                             AS [Description],      
         SM.[HandlerName]                                                                 AS [Fe],      
         RTRIM(ISNULL(A.[FECode],''''))                                                   AS [FECode],      
         SM.[HandlerName]                                                                 AS [FeeEarner],      
         RTRIM(ISNULL(A.[User1],''''))                                                    AS [User1],      
         RTRIM(ISNULL(A.[User2],''''))                                                    AS [User2],      
         RTRIM(ISNULL(A.[User3],''''))                                                    AS [User3],      
         SM.[Started]                                                                     AS [Started],      
         RTRIM(ISNULL(A.[Dept],''''))                                                     AS [Dept],      
         RTRIM(ISNULL(A.[WType],''''))                                                    AS [WorkType],      
         SM.[Privileges]                                                                  AS [Privileges],      
         RTRIM(ISNULL(A.[OldRef],''''))                                                   AS [OldRef],      
         SM.[Address]                                                                     AS [Address],      
         SM.[Email]                                                                       AS [Email],      
         CASE WHEN SM.[Closed] = 0 THEN ''N'' ELSE ''Y'' END                              AS [Closed],      
         SM.[OrgClosedDate]                                                               AS [OriginalClosedDate],      
         SM.[CloseDate]                                                                   AS [CloseDate],      
         SM.[FileRGBColour]                                                               AS [FileRGBColour],      
         CASE WHEN YEAR(ISNULL(RML.[DateField], CONVERT(DATETIME, @DefaultDate))) < 1901      
                   THEN NULL      
              ELSE ISNULL(RML.[DateField], CONVERT(DATETIME, @DefaultDate)) END           AS [LastAccessed],      
         SM.[PhoneNumber]                                                                 AS [PhoneNumber],      
         SM.[FileNum]                                                                     AS [FileNum],      
         SM.[ClientCode]                                                                  AS [ClientCode],      
         RTRIM(ISNULL(A.[Dept],''''))                                                     AS [DepartmentCode],      
         SM.[Team]                                                                        AS [FeeEarnerTeam],      
         SM.[TeamName]                                                                    AS [TeamName],      
         RTRIM(ISNULL(A.[YourRef],''''))                                                  AS [YourRef],      
         SM.[MatterStatus]                                                                AS [CaseStatusCode],      
         SM.[MatterStatusDescription]                                                     AS [CaseStatusDesc],
		  A.[OutlayBal]																	  AS [OutlayBal],
		  A.[ClientCurBal]																  AS [ClientCurBal],
		  A.[ClientDepBal]																  AS [ClientDepBal],
		    RTRIM(ISNULL(A.[Comment], ''''))                                                  AS [Comment],
		    RTRIM(ISNULL(A.[Branch], ''''))                                                   AS [Branch],
		  (SELECT COUNT(1) FROM [dbo].[BillHeader] [BH]
                       WHERE ISNULL([BH].[OPENINVOICE], '''') = ''Y''
                         AND ISNULL([BH].[BILLNO], 0) = 0
						 AND [BH].[MATTER] = SM.[Code])									  AS [OpenInvoiceNo],
		B.[Name]																		  AS [ContactName],
	   RTRIM(ISNULL(A.[Description],''''))												  AS [MatterDesc] ,
	   A.[CompBillingMatter]    														  AS [CompBillingMatter],
	   B.[CompMatter]																	  AS [CompMatter]
    FROM				[dbo].[SearchMatters] SM WITH (NOLOCK)      
	LEFT JOIN			[dbo].[Matters] A  ON A.[CODE] =  SM.[Code]
	LEFT OUTER JOIN		[dbo].[contacts] B ON  A.[CLIENTCODE]= B.[CODE]    
    CROSS APPLY			(SELECT SUBSTRING(CONVERT(VARCHAR, DATEADD(minute, ISNULL(SUM(ISNULL(TDB.[time], 0)), 0), @DefaultDaybookDate), 108), 1, 5) AS [TOTALTIME]      
                        FROM [dbo].[TimeDayBook] TDB WITH (NOLOCK)      
						WHERE TDB.[FeeEarn] = @handler      
                         AND TDB.[matter] = SM.[code]
					     ) TTT'      
      
SET @NCOMMAND = @NCOMMAND + N'      
         LEFT OUTER JOIN [dbo].[RecentMatterList] RML WITH (NOLOCK)      
       ON RML.[FEE] = @handler      
                     AND RML.[MATTER] = SM.[Code]
	      WHERE (@StatusCount = 0 OR (SM.[Closed] 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 [Status] FROM @CustomFilter WHERE [Status] IS NOT NULL and [Status] <>''''),'','',''</RowData><RowData>'') + ''</RowData></XMLRoot>'' AS XML) AS x
																)t
																CROSS APPLY x.nodes(''/XMLRoot/RowData'')m(n)) FILTERVALUETBLFnl))  or SM.Closed=1)
				AND A.[Closed] <> ''Y''' 
/*   
--Handler Privileges
IF (@BitWiseID = 0)      
  BEGIN      
    SET @NCOMMAND = @NCOMMAND + N'      
   AND (   SM.[Privileges] = 0      
          OR SM.[Privileges] = -1)'      
  END      
ELSE IF (@BitWiseID = -1)      
  BEGIN      
    SET @NCOMMAND = @NCOMMAND + N'      
   AND 1 = 1'      
  END      
ELSE      
  BEGIN      
    SET @NCOMMAND = @NCOMMAND + N'      
   AND SM.[Privileges] & @BitWiseID <> 0'      
  END      
*/      
IF (@which = 1)      
  BEGIN      
    SET @NCOMMAND = @NCOMMAND + N'      
     AND SM.[FECode] = @handler'      
  END      
      
IF (@closed = 'N')      
  BEGIN      
    SET @NCOMMAND = @NCOMMAND + N'      
     AND SM.[Closed] = 0'      
  END      
      
IF (@closed = 'Y')      
  BEGIN      
    SET @NCOMMAND = @NCOMMAND + N'      
     AND SM.[Closed] = 1'      
  END      

IF (@closed = N'A')      
  BEGIN      
    SET @NCOMMAND = @NCOMMAND + N'      
     AND (SM.[Closed] = 0 OR SM.[Closed] = 1)'      
  END 
      
IF (@worktype <> '')      
  BEGIN      
    SET @NCOMMAND = @NCOMMAND + N'      
     AND SM.[WType] = @WorkType'      
  END      
      
IF (@department <> '')      
  BEGIN      
    SET @NCOMMAND = @NCOMMAND + N'      
     AND SM.[Dept] = @department'      
  END      
      
IF (@filecolour <> '')      
  BEGIN      
    SET @NCOMMAND = @NCOMMAND + N'      
     AND SM.[FileColour] = @filecolour'      
  END      
      
IF (@feecode <> '')      
  BEGIN      
    SET @NCOMMAND = @NCOMMAND + N'      
     AND SM.[FECode] = @feecode'      
  END      
      
DECLARE @SearchIncludes NVARCHAR(MAX)      
      
SET @SearchIncludes = N''      
      
IF (@searchtext <> '')      
  BEGIN      
    IF SUBSTRING(@searchtext, 1, 1) <> '%'      
      BEGIN      
        SET @searchtext = '%' + @searchtext      
      END      
      
    IF SUBSTRING(@searchtext, LEN(@searchtext), 1) <> '%'      
      BEGIN      
        SET @searchtext = @searchtext + '%'      
      END      
      
    SET @searchtext = @searchtext COLLATE SQL_Latin1_General_Cp1251_CS_AS      
          
     IF (@includeaddress = 0)       
    AND (@includecode = 0)      
    AND (@includedescription = 0)      
    AND (@includename = 0)      
    AND (@includeoldref = 0)      
    AND (@includeuser1 = 0)      
    AND (@includeemail = 0)      
      BEGIN      
        -- This is the standard call - not from Advanced Matter Search, so we search in everything      
        IF @SearchIncludes <> N''      
          BEGIN      
                SET @SearchIncludes = @SearchIncludes + N'      
          OR '      
          END      
      
        IF @searchonlycode <> 'yes'      
          BEGIN      
            SET @SearchIncludes = @SearchIncludes + N'SM.[NCDescription]         LIKE @searchtext      
          OR SM.[Code]                     LIKE @searchtext      
          OR SM.[User1]                    LIKE @searchtext      
          OR SM.[User2]                    LIKE @searchtext      
          OR SM.[User3]                    LIKE @searchtext      
          OR SM.[OldRef]                   LIKE @searchtext      
          OR SM.[YourRef]                  LIKE @searchtext      
          OR SM.[NCName]                   LIKE @searchtext      
          OR SM.[NCAddress]                LIKE @searchtext      
          OR SM.[Email]                    LIKE @searchtext      
          OR SM.[NCHandlerName]            LIKE @searchtext      
          OR SM.[NCPartnerName]            LIKE @searchtext      
          OR SM.[DeptDescription]          LIKE @searchtext      
          OR SM.[WTypeDescription]         LIKE @searchtext      
          OR SM.[FileColourDescription]    LIKE @searchtext      
          OR SM.[MatterStatusDescription]  LIKE @searchtext'      
          END      
        ELSE      
          BEGIN      
            SET @SearchIncludes = @SearchIncludes + N'SM.[Code]                  LIKE @searchtext'      
          END      
      
      END      
    ELSE      
      BEGIN      
        IF (@includeaddress = 1)      
          BEGIN      
            IF @SearchIncludes <> N''      
              BEGIN      
                SET @SearchIncludes = @SearchIncludes + N'      
          OR '      
              END      
      
            SET @SearchIncludes = @SearchIncludes + N'SM.[NCAddress]             LIKE @searchtext'      
          END      
      
        IF (@includecode = 1)      
          BEGIN      
            IF @SearchIncludes <> N''      
              BEGIN      
                SET @SearchIncludes = @SearchIncludes + N'      
          OR '      
              END      
      
            SET @SearchIncludes = @SearchIncludes + N'SM.[Code]                  LIKE @searchtext'      
          END      
      
        IF (@includedescription = 1)      
          BEGIN      
            IF @SearchIncludes <> N''      
              BEGIN      
                SET @SearchIncludes = @SearchIncludes + N'      
          OR '      
              END      
      
            SET @SearchIncludes = @SearchIncludes + N'SM.[NCDescription]         LIKE @searchtext'      
          END      
      
        IF (@includename = 1)      
          BEGIN      
            IF @SearchIncludes <> N''      
              BEGIN      
                SET @SearchIncludes = @SearchIncludes + N'      
          OR '      
              END      
      
            SET @SearchIncludes = @SearchIncludes + N'SM.[NCName]                LIKE @searchtext'      
          END      
      
        IF (@includeoldref = 1)      
          BEGIN      
            IF @SearchIncludes <> N''      
              BEGIN      
                SET @SearchIncludes = @SearchIncludes + N'      
          OR '      
              END      
      
            SET @SearchIncludes = @SearchIncludes + N'SM.[OldRef]                LIKE @searchtext'      
          END      
      
        IF (@includeuser1 = 1)      
          BEGIN      
            IF @SearchIncludes <> N''      
              BEGIN      
                SET @SearchIncludes = @SearchIncludes + N'      
          OR '      
              END      
      
            SET @SearchIncludes = @SearchIncludes + N'SM.[User1]                 LIKE @searchtext'      
          END      
      
        IF (@includeemail = 1)      
          BEGIN      
            IF @SearchIncludes <> N''      
              BEGIN      
                SET @SearchIncludes = @SearchIncludes + N'      
          OR '      
              END      
      
            SET @SearchIncludes = @SearchIncludes + N'SM.[Email]                 LIKE @searchtext'      
          END      
      
      END      
                  
  END      
IF @andoraddress = 'OR'      
  BEGIN      
    IF SUBSTRING(@andoraddressvalue, 1, 1) <> '%'      
      BEGIN      
        SET @andoraddressvalue = '%' + @andoraddressvalue       
      END      
      
    IF SUBSTRING(@andoraddressvalue, LEN(@andoraddressvalue), 1) <> '%'      
      BEGIN      
        SET @andoraddressvalue = @andoraddressvalue + '%'      
      END      
      
    SET @andoraddressvalue = @andoraddressvalue COLLATE SQL_Latin1_General_Cp1251_CS_AS      
      
    IF @SearchIncludes <> N''      
      BEGIN      
        SET @SearchIncludes = @SearchIncludes + N'      
          OR '      
      END      
      
    SET @SearchIncludes = @SearchIncludes + N'SM.[NCAddress]					LIKE @andoraddressvalue'      
  END      
      
IF @andorcode = 'OR'      
  BEGIN      
    IF CHARINDEX('%', @andorcodevalue, 1) = 0      
      BEGIN      
        IF SUBSTRING(@andorcodevalue, 1, 1) <> '%'      
          BEGIN      
            SET @andorcodevalue = '%' + @andorcodevalue       
          END      
      
        IF SUBSTRING(@andorcodevalue, LEN(@andorcodevalue), 1) <> '%'      
          BEGIN      
            SET @andorcodevalue = @andorcodevalue + '%'      
          END      
      END      
      
    IF @SearchIncludes <> N''      
      BEGIN      
        SET @SearchIncludes = @SearchIncludes + N'      
          OR '      
      END      
      
    SET @SearchIncludes = @SearchIncludes + N'SM.[Code]							LIKE @andorcodevalue'      
  END      
      
IF @andordept = 'OR'      
  BEGIN      
    IF @SearchIncludes <> N''      
      BEGIN      
        SET @SearchIncludes = @SearchIncludes + N'      
          OR '      
      END      
      
    SET @SearchIncludes = @SearchIncludes + N'SM.[Dept]= @andordeptvalue'      
  END      
      
IF @andordescription = 'OR'      
  BEGIN      
    IF SUBSTRING(@andordescriptionvalue, 1, 1) <> '%'      
      BEGIN      
        SET @andordescriptionvalue = '%' + @andordescriptionvalue       
      END      
      
    IF SUBSTRING(@andordescriptionvalue, LEN(@andordescriptionvalue), 1) <> '%'      
      BEGIN      
        SET @andordescriptionvalue = @andordescriptionvalue + '%'      
      END      
      
    SET @andordescriptionvalue = @andordescriptionvalue COLLATE SQL_Latin1_General_Cp1251_CS_AS      
      
    IF @SearchIncludes <> N''      
      BEGIN      
        SET @SearchIncludes = @SearchIncludes + N'      
          OR '      
      END      
      
    SET @SearchIncludes = @SearchIncludes + N'SM.[NCDescription]			LIKE @andordescriptionvalue'      
  END      
      
IF @andorfeecode = 'OR'      
  BEGIN      
    IF @SearchIncludes <> N''      
      BEGIN      
        SET @SearchIncludes = @SearchIncludes + N'      
          OR '      
      END      
      
    SET @SearchIncludes = @SearchIncludes + N'SM.[FECode] = @andorfeecodevalue'      
  END      
      
IF @andorname = 'OR'      
  BEGIN      
    IF SUBSTRING(@andornamevalue, 1, 1) <> '%'      
      BEGIN      
        SET @andornamevalue = '%' + @andornamevalue       
      END      
      
    IF SUBSTRING(@andornamevalue, LEN(@andornamevalue), 1) <> '%'      
      BEGIN      
        SET @andornamevalue = @andornamevalue + '%'      
      END      
      
    SET @andornamevalue = @andornamevalue COLLATE SQL_Latin1_General_Cp1251_CS_AS      
      
    IF @SearchIncludes <> N''      
      BEGIN      
        SET @SearchIncludes = @SearchIncludes + N'      
          OR '      
      END      
      
    SET @SearchIncludes = @SearchIncludes + N'SM.[NCName]							LIKE @andornamevalue'      
  END      
      
IF @andoroldref = 'OR'      
  BEGIN      
    IF SUBSTRING(@andoroldrefvalue, 1, 1) <> '%'      
      BEGIN      
        SET @andoroldrefvalue = '%' + @andoroldrefvalue       
      END      
      
    IF SUBSTRING(@andoroldrefvalue, LEN(@andoroldrefvalue), 1) <> '%'      
      BEGIN      
        SET @andoroldrefvalue = @andoroldrefvalue + '%'      
      END      
      
    IF @SearchIncludes <> N''      
      BEGIN      
        SET @SearchIncludes = @SearchIncludes + N'      
          OR '      
      END      
      
    SET @SearchIncludes = @SearchIncludes + N'SM.[OldRef]							LIKE @andoroldrefvalue'      
  END      
      
IF @andoruser1 = 'OR'      
  BEGIN      
    IF SUBSTRING(@andoruser1value, 1, 1) <> '%'      
      BEGIN      
        SET @andoruser1value = '%' + @andoruser1value       
      END      
      
   IF SUBSTRING(@andoruser1value, LEN(@andoruser1value), 1) <> '%'      
      BEGIN      
        SET @andoruser1value = @andoruser1value + '%'      
      END      
      
    IF @SearchIncludes <> N''      
      BEGIN      
        SET @SearchIncludes = @SearchIncludes + N'      
          OR '      
      END      
      
    SET @SearchIncludes = @SearchIncludes + N'SM.[User1]						  LIKE @andoruser1value'      
  END      
      
IF @andoruser2 = 'OR'      
  BEGIN      
    IF SUBSTRING(@andoruser2value, 1, 1) <> '%'      
      BEGIN      
        SET @andoruser2value = '%' + @andoruser2value       
      END      
      
    IF SUBSTRING(@andoruser2value, LEN(@andoruser2value), 1) <> '%'      
      BEGIN      
        SET @andoruser2value = @andoruser2value + '%'      
      END      
      
    IF @SearchIncludes <> N''      
      BEGIN      
        SET @SearchIncludes = @SearchIncludes + N'      
          OR '      
      END      
      
    SET @SearchIncludes = @SearchIncludes + N'SM.[User2]						   LIKE @andoruser2value'      
  END      
      
IF @andoruser3 = 'OR'      
  BEGIN      
    IF SUBSTRING(@andoruser3value, 1, 1) <> '%'      
      BEGIN      
        SET @andoruser3value = '%' + @andoruser3value       
      END      
      
    IF SUBSTRING(@andoruser3value, LEN(@andoruser3value), 1) <> '%'      
      BEGIN      
        SET @andoruser3value = @andoruser3value + '%'      
      END      
      
    IF @SearchIncludes <> N''      
      BEGIN      
        SET @SearchIncludes = @SearchIncludes + N'      
          OR '      
      END      
      
    SET @SearchIncludes = @SearchIncludes + N'SM.[User3]						   LIKE @andoruser3value'      
  END      
      
IF @andorwtype = 'OR'      
  BEGIN      
    IF @SearchIncludes <> N''      
      BEGIN      
        SET @SearchIncludes = @SearchIncludes + N'      
          OR '      
      END      
      
    SET @SearchIncludes = @SearchIncludes + N'SM.[WType] = @andorwtypevalue'      
  END      
      
IF @andoryourref = 'OR'      
  BEGIN      
    IF SUBSTRING(@andoryourrefvalue, 1, 1) <> '%'      
      BEGIN      
        SET @andoryourrefvalue = '%' + @andoryourrefvalue       
      END      
      
    IF SUBSTRING(@andoryourrefvalue, LEN(@andoryourrefvalue), 1) <> '%'      
      BEGIN      
        SET @andoryourrefvalue = @andoryourrefvalue + '%'      
      END      
      
    IF @SearchIncludes <> N''      
      BEGIN      
        SET @SearchIncludes = @SearchIncludes + N'      
          OR '      
      END      
      
    SET @SearchIncludes = @SearchIncludes + N'SM.[YourRef]							LIKE @andoryourrefvalue'      
  END      
      
--SELECT @SearchIncludes      
      
IF @SearchIncludes <> ''      
  BEGIN      
    SET @NCOMMAND = @NCOMMAND + N'      
     AND (   ' + @SearchIncludes + N')'      
  END      
      
IF @andoraddress = 'AND'      
  BEGIN      
    IF SUBSTRING(@andoraddressvalue, 1, 1) <> '%'      
      BEGIN      
        SET @andoraddressvalue = '%' + @andoraddressvalue       
      END      
      
    IF SUBSTRING(@andoraddressvalue, LEN(@andoraddressvalue), 1) <> '%'      
      BEGIN      
        SET @andoraddressvalue = @andoraddressvalue + '%'      
      END      
      
    SET @andoraddressvalue = @andoraddressvalue COLLATE SQL_Latin1_General_Cp1251_CS_AS      
      
    SET @NCOMMAND = @NCOMMAND + N'      
     AND SM.[NCAddress]             LIKE @andoraddressvalue'      
  END      
      
IF @andorcode = 'AND'      
  BEGIN      
    IF SUBSTRING(@andorcodevalue, 1, 1) <> '%'      
      BEGIN      
        SET @andorcodevalue = '%' + @andorcodevalue       
      END      
      
    IF SUBSTRING(@andorcodevalue, LEN(@andorcodevalue), 1) <> '%'      
      BEGIN      
        SET @andorcodevalue = @andorcodevalue + '%'      
      END      
      
    SET @NCOMMAND = @NCOMMAND + N'      
     AND SM.[Code]                  LIKE @andorcodevalue'      
  END      
      
IF @andordept = 'AND'      
  BEGIN      
    SET @NCOMMAND = @NCOMMAND + N'      
     AND SM.[Dept]              = @andordeptvalue'      
  END      
      
IF @andordescription = 'AND'      
  BEGIN      
    IF SUBSTRING(@andordescriptionvalue, 1, 1) <> '%'      
      BEGIN      
        SET @andordescriptionvalue = '%' + @andordescriptionvalue       
      END      
      
    IF SUBSTRING(@andordescriptionvalue, LEN(@andordescriptionvalue), 1) <> '%'      
      BEGIN      
        SET @andordescriptionvalue = @andordescriptionvalue + '%'      
      END      
      
    SET @andordescriptionvalue = @andordescriptionvalue COLLATE SQL_Latin1_General_Cp1251_CS_AS      
      
    SET @NCOMMAND = @NCOMMAND + N'      
     AND SM.[NCDescription]         LIKE @andordescriptionvalue'      
  END      
      
IF @andorfeecode = 'AND'      
  BEGIN      
    SET @NCOMMAND = @NCOMMAND + N'      
     AND SM.[FECode]                = @andorfeecodevalue'      
  END      
      
IF @andorname = 'AND'      
  BEGIN      
    IF SUBSTRING(@andornamevalue, 1, 1) <> '%'      
      BEGIN      
        SET @andornamevalue = '%' + @andornamevalue       
      END      
      
IF SUBSTRING(@andornamevalue, LEN(@andornamevalue), 1) <> '%'      
      BEGIN      
        SET @andornamevalue = @andornamevalue + '%'      
      END      
      
    SET @andornamevalue = @andornamevalue COLLATE SQL_Latin1_General_Cp1251_CS_AS      
      
    SET @NCOMMAND = @NCOMMAND + N'      
     AND SM.[NCName]                LIKE @andornamevalue'      
  END      
      
IF @andoroldref = 'AND'      
  BEGIN      
    IF SUBSTRING(@andoroldrefvalue, 1, 1) <> '%'      
      BEGIN      
        SET @andoroldrefvalue = '%' + @andoroldrefvalue       
      END      
      
    IF SUBSTRING(@andoroldrefvalue, LEN(@andoroldrefvalue), 1) <> '%'      
      BEGIN      
        SET @andoroldrefvalue = @andoroldrefvalue + '%'      
      END      
      
    SET @NCOMMAND = @NCOMMAND + N'      
     AND SM.[OldRef]                LIKE @andoroldrefvalue'      
  END      
      
IF @andoruser1 = 'AND'      
  BEGIN      
    IF SUBSTRING(@andoruser1value, 1, 1) <> '%'      
      BEGIN      
        SET @andoruser1value = '%' + @andoruser1value       
      END      
      
    IF SUBSTRING(@andoruser1value, LEN(@andoruser1value), 1) <> '%'      
      BEGIN      
        SET @andoruser1value = @andoruser1value + '%'      
      END      
      
    SET @NCOMMAND = @NCOMMAND + N'      
     AND SM.[User1]                 LIKE @andoruser1value'      
  END      
      
IF @andoruser2 = 'AND'      
  BEGIN      
    IF SUBSTRING(@andoruser2value, 1, 1) <> '%'      
      BEGIN      
        SET @andoruser2value = '%' + @andoruser2value       
      END      
      
    IF SUBSTRING(@andoruser2value, LEN(@andoruser2value), 1) <> '%'      
      BEGIN      
        SET @andoruser2value = @andoruser2value + '%'      
      END      
      
    SET @NCOMMAND = @NCOMMAND + N'      
     AND SM.[User2]                 LIKE @andoruser2value'      
  END      
      
IF @andoruser3 = 'AND'      
  BEGIN      
    IF SUBSTRING(@andoruser3value, 1, 1) <> '%'      
      BEGIN      
        SET @andoruser3value = '%' + @andoruser3value       
      END      
      
    IF SUBSTRING(@andoruser3value, LEN(@andoruser3value), 1) <> '%'      
      BEGIN      
        SET @andoruser3value = @andoruser3value + '%'      
      END      
      
    SET @NCOMMAND = @NCOMMAND + N'      
     AND SM.[User3]      LIKE @andoruser3value'      
  END      
      
IF @andorwtype = 'AND'      
  BEGIN      
    SET @NCOMMAND = @NCOMMAND + N'      
     AND SM.[WType]                 = @andorwtypevalue'      
  END      
      
IF @andoryourref = 'AND'      
  BEGIN      
    IF SUBSTRING(@andoryourrefvalue, 1, 1) <> '%'      
      BEGIN      
        SET @andoryourrefvalue = '%' + @andoryourrefvalue       
      END      
      
    IF SUBSTRING(@andoryourrefvalue, LEN(@andoryourrefvalue), 1) <> '%'      
      BEGIN      
        SET @andoryourrefvalue = @andoryourrefvalue + '%'      
      END      
      
    SET @NCOMMAND = @NCOMMAND + N'      
     AND SM.[YourRef]               LIKE @andoryourrefvalue'      
  END      
      

SET @NCOMMAND = @NCOMMAND + N'      
ORDER BY ' + CASE @orderby      
                  WHEN N'description'      
                       THEN N'CASE WHEN SM.[Description] = ''''      
                   THEN 1      
              ELSE 0 END,      
         SM.[Description] ' + @order + N',      
         SM.[Code] ' + @order      
                  WHEN N'Code'      
                       THEN N'CASE WHEN SM.[Code] = ''''      
                   THEN 1      
              ELSE 0 END,      
         SM.[Code] ' + @order      
                  WHEN N'Name'      
                       THEN N'CASE WHEN SM.[Name] = ''''      
                   THEN 1      
              ELSE 0 END,      
         SM.[Name] ' + @order + N',      
         SM.[Code] ' + @order      
                  WHEN N'Address'      
                       THEN N'CASE WHEN SM.[Address] = ''''      
                   THEN 1      
              ELSE 0 END,      
         SM.[Address] ' + @order + N',      
         SM.[Code] ' + @order      
                  WHEN N'Email'      
                       THEN N'CASE WHEN SM.[Email] = ''''      
                   THEN 1      
              ELSE 0 END,      
         SM.[Email] ' + @order + N',      
         SM.[Code] ' + @order      
                  WHEN N'PhoneNumber'      
                       THEN N'CASE WHEN SM.[PhoneNumber] = ''''      
                   THEN 1      
              ELSE 0 END,      
         SM.[PhoneNumber] ' + @order + N',      
         SM.[Code] ' + @order      
                  WHEN N'user1'      
                      THEN N'CASE WHEN SM.[User1] = ''''      
                   THEN 1      
              ELSE 0 END,      
         SM.[User1] ' + @order + N',      
         SM.[Code] ' + @order      
                  WHEN N'user2'      
                       THEN N'CASE WHEN SM.[User2] = ''''      
                   THEN 1      
              ELSE 0 END,      
         SM.[User2] ' + @order + N',      
         SM.[Code] ' + @order      
    WHEN N'user3'      
                       THEN N'CASE WHEN SM.[User3] = ''''      
                   THEN 1      
              ELSE 0 END,      
         SM.[User3] ' + @order + N',      
         SM.[Code] ' + @order      
                  WHEN N'yourref'      
                       THEN N'CASE WHEN SM.[YourRef] = ''''      
                   THEN 1      
              ELSE 0 END,      
         SM.[YourRef] ' + @order + N',      
         SM.[Code] ' + @order      
                  WHEN N'OldRef'      
                       THEN N'CASE WHEN SM.[OldRef] = ''''      
                   THEN 1      
              ELSE 0 END,      
         SM.[OldRef] ' + @order + N',      
         SM.[Code] ' + @order      
                  WHEN N'FileNum'      
                       THEN N'CASE WHEN SM.[FileNum] = 0      
                   THEN 1      
              ELSE 0 END,      
         SM.[FileNum] ' + @order + N',      
         SM.[Code] ' + @order      
                  WHEN N'department'      
                       THEN N'CASE WHEN SM.[DeptDescription] = ''''      
                   THEN 1      
  ELSE 0 END,      
         SM.[DeptDescription] ' + @order + N',      
         SM.[Code] ' + @order      
                  WHEN N'Dept'      
                       THEN N'CASE WHEN SM.[DeptDescription] = ''''      
                   THEN 1      
              ELSE 0 END,      
         SM.[DeptDescription] ' + @order + N',      
         SM.[Code] ' + @order      
                  WHEN N'worktype'      
                       THEN N'CASE WHEN SM.[WTypeDescription] = ''''      
                   THEN 1      
              ELSE 0 END,      
         SM.[WTypeDescription] ' + @order + N',      
         SM.[Code] ' + @order      
                  WHEN N'FeeCode'      
                       THEN N'CASE WHEN SM.[FECode] = ''''      
                   THEN 2      
              WHEN SM.[FECode] = ''ADM''      
                   THEN 1      
 ELSE 0 END,      
         SM.[FECode] ' + @order + N',      
         SM.[Code] ' + @order      
                  WHEN N'FeeEarner'      
                       THEN N'CASE WHEN SM.[HandlerName] = '''' AND SM.[FECode] = ''''      
                   THEN 3      
              WHEN SM.[HandlerName] = ''''      
                   THEN 2      
              WHEN SM.[HandlerName] = ''Admin''      
                   THEN 1      
              ELSE 0 END,      
         SM.[HandlerName] ' + @order + N',      
         SM.[Code] ' + @order      
                  WHEN N'FileColour_Fld'      
                       THEN N'CASE WHEN SM.[FileColourDescription] = ''''      
                   THEN 1      
              ELSE 0 END,      
         SM.[FileColourDescription] ' + @order + N',      
         SM.[Code] ' + @order      
                  WHEN N'FileColour'      
                       THEN N'CASE WHEN SM.[FileColourDescription] = ''''      
                   THEN 1      
              ELSE 0 END,      
         SM.[FileColourDescription] ' + @order + N',      
         SM.[Code] ' + @order      
                  WHEN N'started'      
                       THEN N'SM.[Started] ' + @order + N',      
         SM.[Code] ' + @order      
                  WHEN N'LastAccessed'      
                       THEN N'CASE WHEN RML.[DateField] IS NULL      
                   THEN 1      
              ELSE 0 END,      
         RML.[DateField] ' + @order + N',      
         SM.[Code] ' + @order      
                  WHEN N'closedate'      
                       THEN N'CASE WHEN SM.[CloseDate] IS NULL      
                   THEN 1      
              ELSE 0 END,      
         SM.[CloseDate] ' + @order + N',      
         SM.[Code] ' + @order      
                  WHEN N'orgcloseddate'      
                       THEN N'CASE WHEN SM.[orgcloseddate] IS NULL      
                   THEN 1      
              ELSE 0 END,      
         SM.[orgcloseddate] ' + @order + N',      
         SM.[Code] ' + @order      
                  WHEN N'casestatusdesc'      
                       THEN N'CASE WHEN SM.[MatterStatusDescription] IS NULL      
                   THEN 1      
              ELSE 0 END,      
         SM.[MatterStatusDescription] ' + @order + N',      
         SM.[Code] ' + @order      
                  ELSE CASE WHEN @Which <> 2      
                                 THEN N'SM.[Code] ' + @order      
                            ELSE N'CASE WHEN RML.[DateField] IS NULL      
                   THEN 1      
              ELSE 0 END,      
         RML.[DateField] DESC,      
         SM.[Code] ' + @order END END      
      
SET @NCOMMAND = @NCOMMAND + N'    
 IF(@Mode <>''Dashboard'')  
 	BEGIN
  SELECT ROW_NUMBER() OVER(ORDER BY [Res].[id]) AS [RowNumber],    
		 [RES].[id] as Id,      
         [RES].[Time],      
         [RES].[Code],      
         [RES].[FileColour],      
         [RES].[FileColour_Fld],      
         [RES].[BackgroundColour],      
         [RES].[TextColour],      
         [RES].[Name],    
         [RES].[Description],      
         [RES].[Fe],      
         [RES].[FECode],      
         [RES].[FeeEarner],      
         [RES].[User1],      
         [RES].[User2],      
         [RES].[User3],      
         [RES].[Started],      
         [RES].[Dept],      
         [RES].[WorkType],      
         [RES].[Privileges],      
         [RES].[OldRef],      
         [RES].[Address],      
         [RES].[Email],      
         [RES].[Closed],      
         [RES].[OrgCloseDate],      
         [RES].[CloseDate],      
         [RES].[FileRGBColour],      
         [RES].[LastAccessed],      
         [RES].[PhoneNumber],      
         [RES].[FileNum],      
         [RES].[ClientCode],      
         [RES].[DepartmentCode],      
         [RES].[FeeEarnerTeam],      
         [RES].[TeamName],      
         [RES].[YourRef],      
         [RES].[CaseStatusCode],      
         [RES].[CaseStatusDesc] ,
		 [RES].[OutlayBal],
		 [RES].[ClientCurBal],
		 [RES].[ClientDepBal],
		 [RES].[Comment],
		 [RES].[Branch],
		 [RES].[OpenInvoiceNo],
		 [RES].[ContactName],
		 [RES].[MatterDesc],
		 [RES].[CompBillingMatter],
		 [RES].[CompMatter] 
    FROM @Result [RES]
   WHERE [RES].[id] BETWEEN @StartRow AND @EndRow
ORDER BY [RES].[id] ASC
	SELECT  
		COUNT(1) AS [TotalRecord]  
	FROM  
		@Result [RES]  
	  
    SELECT
		USERPROMPT1 AS [UserPrompt1],
		USERPROMPT2 AS [UserPrompt2],
		USERPROMPT3 AS [UserPrompt3],
		YourRef     AS [YourRef]
    FROM
	    [Control]
	END	
	ELSE
		BEGIN
		SELECT   
		 [RES].[ClientCode],  
         [RES].[Code],
		 [RES].[Name],         
         [RES].[Description],        
         [RES].[FECode],      
         [RES].[FeeEarner],      
         [RES].[DepartmentCode] AS [Dept],
		 [RES].[CompBillingMatter],
		 [RES].[CompMatter]     
    FROM @Result [RES]
   WHERE [RES].[id] BETWEEN @StartRow AND @EndRow
ORDER BY [RES].[id] ASC
		END
		'

EXECUTE sp_executesql @NCOMMAND,      
                      N'@HowMany               INT,      
                        @BitWiseID             BIGINT,      
                        @Handler               VARCHAR(10),      
                        @WorkType              VARCHAR(5),      
                        @department            VARCHAR(5),      
                        @filecolour            VARCHAR(3),      
                        @feecode               VARCHAR(10),      
                        @searchtext            VARCHAR(MAX),      
                        @andoraddressvalue     VARCHAR(200),      
                        @andorcodevalue        VARCHAR(200),      
                        @andordeptvalue        VARCHAR(200),      
                        @andordescriptionvalue VARCHAR(200),      
                        @andorfeecodevalue     VARCHAR(200),      
                        @andornamevalue        VARCHAR(200),      
                        @andoroldrefvalue      VARCHAR(200),      
                        @andoruser1value       VARCHAR(200),      
                        @andoruser2value       VARCHAR(200),      
                        @andoruser3value       VARCHAR(200),      
                        @andorwtypevalue       VARCHAR(200),      
                        @andoryourrefvalue     VARCHAR(200),    
					    @StartRow			   INT,    
					    @EndRow                INT,
					    @ColumnNames           VARCHAR(50),
					    @FilterValues          VARCHAR(50),
						@DefaultDate		   VARCHAR(8),
						@DefaultDaybookDate	   VARCHAR(8),
						@Mode				   VARCHAR(10)
						',      
                      @Howmany,      
                      @BitWiseID,      
                      @handler,      
                      @worktype,      
                      @department,      
                      @filecolour,      
                      @feecode,      
                      @searchtext,      
                      @andoraddressvalue,      
                      @andorcodevalue,      
                      @andordeptvalue,      
                      @andordescriptionvalue,      
                      @andorfeecodevalue,      
                     @andornamevalue,      
                      @andoroldrefvalue,      
                      @andoruser1value,      
                      @andoruser2value,      
                      @andoruser3value,      
                      @andorwtypevalue,      
                      @andoryourrefvalue,    
					  @StartRow,    
					  @EndRow,
					  @ColumnNames,
					  @FilterValues,
					  @DefaultDate,
					  @DefaultDaybookDate,
					  @Mode	

SET NOCOUNT OFF

END


GO



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

CREATE PROCEDURE  
       [dbo].[KAAS_LS_GetUDFForFolio]
      (@KeyName	VARCHAR(100)
	   )  
AS  
/*************************************************************************************************************  
 * Stored Procedure Name: [KAAS_LS_GetUDFForFolio]														 
 * Get Folio settings from the Settings table using Keyname  
 *  
 *   *******************  
 *   *                 *  
 *   * 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  
 * 2023-10-09    Nithyanandham.M   Created
 *************************************************************************************************************/  
BEGIN  
SELECT 
    [KeyName],
	[KeyValue]
    FROM [Settings] 
	WHERE [KeyName] = @KeyName
END  

GO

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

create PROCEDURE [dbo].[KAAS_LS_InsertTasksAction]
(
    @CASECODE                 VARCHAR(20),
    @FNCODE                   VARCHAR(10),
	@SUBJECT                  VARCHAR(150),
	@TEXT1                    VARCHAR(MAX),
    @Result                   INT    OUTPUT
)

/*******************************************************************************************************
* This sp used to insert blank action.																   *
*                                                                                                      *
* Stored Procedure Name	: [dbo].[KAAS_LS_InsertTasksAction]											   *
* Copied from			: [dbo].[KAAS_InsertTasksAction]											   *
*                                                                                                      *
* Modification History	:                                                                              *
* 2024-07-29	Ponni M		Created																	   *
* 2024-09-03	Aakif		Fetch team code for current handler										   *
*******************************************************************************************************/ 

AS
BEGIN
SET NOCOUNT ON
	-- GET TEAM CODE FOR HANDLER
	DECLARE @TeamCode VARCHAR(10);

	SELECT @TeamCode = [HAN].[TEAM] 
	  FROM [dbo].[Handlers] [HAN]
	 WHERE [HAN].[CODE] = @FNCODE

	--Diary
    DECLARE @TIMECONVERT INT
    SET @TIMECONVERT = dbo.ky_ConvertTimeToClarion(GETDATE())
    DECLARE @ACTIONID    INT

    --Pino 2015-09-30 Start
    EXEC @ACTIONID = [dbo].[KAAS_GetNextActionID]
    --SET @ACTIONID = (SELECT ISNULL(MAX(ACTIONID + 1), 1) FROM [dbo].[diary])
    --Pino 2015-09-30 End

	DECLARE @ACTIONCODE Varchar(100)
SET @ACTIONCODE = (SELECT TOP 1 KeyValue FROM settings WHERE KeyName = 'LS_DefautActionCode')

    INSERT
      INTO [dbo].[diary]
          ([CASECODE],
           [DATE],
           [STATUS],
           [ACTIONCODE],
           [ACTIONSTATUS],
           [ACTIONTYPE],
           [PROCESSTYPE],
           [FNCODE],
           [TEAMCODE],
           [TEXT1],
           [TEXT2],
           [DELEGATEDFNR],
           [DELEGATEDDATE],
           [DELEGATEDBACKDATE],
           [DEFERRED],
           [DUEDATE],
           [PUBLISH],
           [DYSTARTTIME],
           [DYENDTIME],
           [DURATION],
           [ACTIONID],
           [ORGINALACTIONID],
           [PRIORITY],
           [HIGHLIGHTED],
           [MILESTEONETYPE],
           [ATTACHMENTS],
           [PROCESSSTATUS],
           [WORKPROCESS],
           [BILLABLE],
           [BILLDESCRIPTION],
           [EMAILADDRESS],
           [ADDRESSTO],
           [CCTo],
           [BCCTo],
           [EMAIL],
           [SUBJECT],
           [DELEGATIONSTATUS],
           [DRAFTBILLNO],
           [CHEQUEREQNO],
           [TxmSent],
           [Location],
           [HearingType],
           [ForCopy],
           [TxmDate],
           [TxmSeqNo],
           [DisplayText],
           [Flag],
		   [KYC])
    VALUES(@CASECODE,
           GETDATE(),
           0,
           @ACTIONCODE,
           null,
           'A',
           null,
           @FNCODE,
           @TeamCode,
           @TEXT1,
           null,
           null,
           null,
           null,
           null,
           null,
          'N',
           @TIMECONVERT,
           @TIMECONVERT,
           0,
           @ACTIONID,
           0,
           'N',
           'N',
           null,
           'N',
           0,
           0,
           0,
           'N',
           null,
           null,
           null,
           null,
           null,
           @SUBJECT,
           0,
           0,
           0,
           0,
           null,
           null,
           null,
           null,
           0,
          null,
		  null,
		 'N'
)
    INSERT
              INTO [dbo].[DiaryDelegations]
                  ([ACTIONID],
                   [HANDLER],
                   [TEAM],
                   [DATE],
                   [TIME],
                   [DATER],
                   [TIMER],
                   [DUEDATE],
                   [DUETIME],
                   [REVIEW],
                   [STATUS],
                   [OWNER],
                   [DELEGATE],
                   [DELEGATESTATUS],
                   [ActionType],
                   [FromHandler],
                   [ReturnedBy],
                   [DelType])
            VALUES(@ACTIONID,
                   @FNCODE,
                   @TeamCode,
                   GETDATE(),
                   @TIMECONVERT,
                   null,
                   @TIMECONVERT,
                   GETDATE(),
                   @TIMECONVERT,
                   null,
                   0,
                   'Y',
                   @FNCODE,
                   0,
                   'A',
                   @FNCODE,
                   null,
                   'Created')
    SET @Result = @ACTIONID
SET NOCOUNT OFF
END


GO


