IF OBJECT_ID(N'[KAAS_CP_ConvertTime]',N'FN')IS NOT NULL
    DROP FUNCTION [KAAS_CP_ConvertTime]
GO


create function
       [dbo].[KAAS_CP_ConvertTime]
       (@datetime    datetime)
/**************************************************************************************
 * Funtion Name : KAAS_CP_ConvertTime
 * Copied from [ky_ConvertTimeToClarion]
 *
 * converts a SQL Date (ignoring any DATE part in that date)
 * to the Clarion Time value, i.e. the number of 1/100 of a second since
 * midnight, plus one.
 *
 * 2022-02-11	Vinodhkumar.M   Created										           
 **************************************************************************************/
RETURNS int
AS
BEGIN
  DECLARE @hour int
  DECLARE @minute int
  DECLARE @second int
  DECLARE @centi int
  DECLARE @result int

  SET @centi = convert(int,
                       convert(decimal(4, 1),
                               DATEPART(millisecond, @datetime))
                     / convert(decimal(4, 1),
                               10))

  SET @second = DATEPART(second, @datetime)
  SET @minute = DATEPART(minute, @datetime)
  SET @hour = DATEPART(hour, @datetime)

  SET @result = @hour
  SET @result = 60 * @result
  SET @result = @result + @minute
  SET @result = 60 * @result
  SET @result = @result + @second
  SET @result = 100 * @result
  SET @result = @result + @centi + 1

  RETURN @result
END
GO

IF OBJECT_ID('KAAS_CP_FNCheckUserIsLocked',N'FN') IS NOT NULL
	DROP FUNCTION KAAS_CP_FNCheckUserIsLocked
GO
CREATE FUNCTION [dbo].[KAAS_CP_FNCheckUserIsLocked](
	@LoginId BIGINT
)
RETURNS BIT
AS
/*******************************************************************************************************
* This function is used to check if the user is locked or not										   *
*                                                                                                      *
* Copied From : KAAS_CP_FNCheckUserIsLocked															   *
*                                                                                                      *
* Modification History	:                                                                              *
* 2021-07-06	Aakif			Created				                                                   *
*******************************************************************************************************/ 
BEGIN
	DECLARE @LockOutLimit TINYINT;
	DECLARE @IsLocked BIT = 0;
	DECLARE @CurrentAttempt INT, @LockoutDate DATETIME
	
	SELECT 
		@LockOutLimit = [IsLockOut_Attempt] 
	FROM 
		ClientPortalSetting

	SELECT
		@CurrentAttempt = ISNULL(NoOfAttempt, 0),
		@LockoutDate	= LockedOutEndDate
	FROM 
		[ClientLogin]
	WHERE
		[ClientLoginId] = @LoginId


	IF(@CurrentAttempt >= @LockOutLimit AND @LockoutDate > GETUTCDATE())
	BEGIN
		SET @IsLocked = 1;
	END


	RETURN @IsLocked
END
GO

IF OBJECT_ID(N'KAAS_CP_FNConvertUTCDateToTargetTimeZone',N'FN') IS NOT NULL
  BEGIN
    DROP FUNCTION [dbo].[KAAS_CP_FNConvertUTCDateToTargetTimeZone]
  END
GO

DECLARE @Compatible BIT

SET @Compatible = CASE WHEN ServerProperty('EngineEdition') = 5
                            THEN 1
                       WHEN @@VERSION LIKE '%SQL Server 2008%'
                            THEN 0
                       WHEN @@VERSION LIKE '%SQL Server 2012%'
                            THEN 0
                       WHEN @@VERSION LIKE '%SQL Server 2014%'
                            THEN 0
                       ELSE 1 END

IF @Compatible = 1
  BEGIN
    EXEC ('CREATE FUNCTION 
	[dbo].[KAAS_CP_FNConvertUTCDateToTargetTimeZone]
	( 
		@Date		DATETIME,
		@LoginId	BIGINT
	)
RETURNS DATETIME
AS
/*******************************************************************************************************
* This function is used to convert local time to specified zone time. default is GMT				   *
* Copied From : KAAS_FNConvertUTCDateToTargetTimeZone                                                  *
* Function	: KAAS_CP_FNConvertUTCDateToTargetTimeZone												   *
*                                                                                                      *
* Modification History	:                                                                              *
* 2021-06-11	Aakif			Created				                                                   *
*******************************************************************************************************/ 
BEGIN
	RETURN
	(			
		SELECT CONVERT(DATETIME,CONVERT(DATETIMEOFFSET, @Date) AT TIME ZONE (SELECT dbo.KAAS_CP_FNGetClientTimeZone(@LoginId)))
	)
End')
  END
ELSE
  BEGIN
    EXEC ('CREATE FUNCTION [dbo].[KAAS_CP_FNConvertUTCDateToTargetTimeZone]
( 
	@Date		DATETIME,
	@LoginId	BIGINT
)
RETURNS DATETIME
AS
/*******************************************************************************************************
* This function is used to convert local time to specified zone time. default is GMT				   *
* Copied From : KAAS_FNConvertUTCDateToTargetTimeZone                                                  *
* Function	: KAAS_CP_FNConvertUTCDateToTargetTimeZone												   *
*                                                                                                      *
* Modification History	:                                                                              *
* 2021-06-11	Aakif			Created				                                                   *
*******************************************************************************************************/ 
BEGIN
	RETURN
	(
			SELECT @Date
	)
End')
  END

GO


IF OBJECT_ID('KAAS_CP_FNGetClientTimeZone',N'FN') IS NOT NULL
	DROP FUNCTION KAAS_CP_FNGetClientTimeZone
GO
CREATE FUNCTION [dbo].[KAAS_CP_FNGetClientTimeZone](
	@LoginId BIGINT
)
RETURNS NVARCHAR(50)
AS
/*******************************************************************************************************
* This function is used to get the handler time zone, for now its default to GMT standard Time		   *
*                                                                                                      *
* Copied From : KAAS_FNGetDateForTimeZone															   *
*                                                                                                      *
* Modification History	:                                                                              *
* 2021-06-11	Aakif			Created				                                                   *
*******************************************************************************************************/ 
BEGIN
	DECLARE @HandlerTimeZone NVARCHAR(50)
	
	SELECT 
		@HandlerTimeZone = [ClientDefaulTimeZone] 
	FROM 
		ClientLogin 
	WHERE 
		ClientLoginId = @LoginId
	
	SELECT 
		@HandlerTimeZone = ISNULL(@HandlerTimeZone, 'GMT Standard Time')

	RETURN @HandlerTimeZone
End
GO
IF OBJECT_ID(N'KAAS_CP_FNGetDocFolders',N'TF')IS NOT NULL
BEGIN
	DROP FUNCTION KAAS_CP_FNGetDocFolders
END
GO

CREATE FUNCTION [dbo].[KAAS_CP_FNGetDocFolders]
       (@MatterCode		VARCHAR(20),
	    @parentid       INT,
        @fulltree       BIT,
        @includeparent	BIT)
 RETURNS
		@DocFolders TABLE
       ([seq]                 INT              IDENTITY (1, 1)       NOT NULL PRIMARY KEY,
        [id]                  INT                                    NOT NULL,
        [parent]              INT                                    NOT NULL,
        [foldername]          VARCHAR(500)                           NOT NULL,
        [foldericon]          INT                                    NOT NULL,
		[folderimage]		  VARBINARY(MAX),
        [foldershared]        BIT                                    NOT NULL,
        [folderisours]        BIT                                    NOT NULL,
        [documentcount]       INT                                    NOT NULL)
AS
/*************************************************************************************************************
 *                                                                                                           *
 * Function Name : [dbo].[KAAS_FN_GetDocFolders]		                                                     *
 * Copied from   : [dbo].[KAAS_FN_GetDocFolders]			                                                 *
 *                                                                                                           *
 *                                                                                                           *
 * Retrieve a document folder tree                                                                           *
 * @parentid       - the ID of the root folder                                                               *
 * @fulltree       - get the complete tree for @parentid                                                     *
 *                   0 - get only the first level child nodes                                                *
 *                   1 - get all child nodes                                                                 *
 * @includeparent  - include the parent node itself                                                          *
 *                   0 - the top level returned are the child nodes                                          *
 *                   1 - the top level returned is the parent node                                           *
 *                                                                                                           *
 * Modification History:                                                                                     *
 * 2018-12-06    Arun.V        Created to return file count                                                  *
 * 2019-04-26    Vinodhan.K    Added folderimage field                                                       *
 * 2019-08-16    Arun V        Changed [docfoldercount] to update the files count,                           *
 * 2021-07-29    Aakf	       Created																		 *
 *************************************************************************************************************/
BEGIN
Declare @DocFoldersX  Table
         ([seq]                 INT            IDENTITY (0, 1)         NOT NULL PRIMARY KEY,
          [id]                  INT                                    NOT NULL,
          [parent]              INT                                    NOT NULL,
          [foldername]          VARCHAR(500)                           NOT NULL,
          [foldericon]          INT                                    NOT NULL,
		  [folderimage]		  VARBINARY(MAX),    
          [foldershared]        BIT                                    NOT NULL,
          [folderisours]        BIT                                    NOT NULL,
          [docfoldercount]      INT                                    NOT NULL,
		  [Iteration]			INT									   NULL)


  DECLARE @AllFilesCount INT =0  

  IF(@includeparent =1)
  BEGIN
		SET @AllFilesCount = (
			SELECT count(1)
			 FROM [dbo].[DiaryAttachments] DBA
			 JOIN [dbo].[diary] DIA
			   ON [DBA].[DiaryID] = [DIA].[ACTIONID]
			WHERE [DBA].[CASECODE] = @MatterCode
			  AND ISNULL([DIA].PUBLISH, '') = 'P')

		INSERT    
			INTO @DocFolders    
              ([id],    
               [parent],    
               [foldername],    
               [foldericon],
			   [folderimage],
               [foldershared],    
               [folderisours],    
               [documentcount])    
			SELECT 0,    
               -1,  -- To avoid recusrive loop in WebApi  
               'All',    
               0,
			   NULL,    
               0,    
               0,    
               @AllFilesCount
	END		

  IF ISNULL(@parentid, 0) = 0
    BEGIN
      INSERT
          INTO @DocFoldersX
              ([id],
               [parent],
               [foldername],
               [foldericon],
			   [folderimage],    
               [foldershared],
               [folderisours],
               [docfoldercount],
			   Iteration)
        SELECT [DF].[id],
               0,
               [DF].[foldername],
               [DF].[foldericon],
			    [DFU].[image], 
               0,
               1,
               0,
			   0
          FROM [dbo].[DocFolders] [DF]
		  LEFT JOIN [dbo].[DocFolderUploadedImages] [DFU]  
		  ON [DF].[foldericon] = [DFU].[id]    
         WHERE [DF].[parent] IS NULL
      ORDER BY [DF].[foldername],
               [DF].[id]
    END
  ELSE
    BEGIN
      IF @includeparent = 1
        BEGIN
            INSERT
              INTO @DocFoldersX
                  ([id],
                   [parent],
                   [foldername],
                   [foldericon],
				     [folderimage],    
                   [foldershared],
                   [folderisours],
                   [docfoldercount],
				   Iteration)
            SELECT [DF].[id],
                   ISNULL([DF].[parent], 0),
                   [DF].[foldername],
                   [DF].[foldericon],
				   [DFU].[image], 
                   0,
                   1,
                   0,
				   0
              FROM [dbo].[DocFolders] [DF]
			  	  LEFT JOIN [dbo].[DocFolderUploadedImages] [DFU]  
			  ON [DF].[foldericon] = [DFU].[id]  
             WHERE [DF].[id] = @parentid
          ORDER BY [DF].[foldername],
                   [DF].[id]
        END
      ELSE
        BEGIN
            INSERT
              INTO @DocFoldersX
                  ([id],
                   [parent],
                   [foldername],
                   [foldericon],
				   [folderimage],    
                   [foldershared],
                   [folderisours],
                   [docfoldercount],
				   Iteration)
            SELECT [DF].[id],
                   ISNULL([DF].[parent], 0),
                   [DF].[foldername],
                   [DF].[foldericon],
				   [DFU].[image],   
                   0,
                   1,
                   0,
				   0
              FROM [dbo].[DocFolders] [DF]
			  LEFT JOIN [dbo].[DocFolderUploadedImages] [DFU]  
			  ON [DF].[foldericon] = [DFU].[id]    
             WHERE [DF].[parent] = @parentid
          ORDER BY [DF].[foldername],
                   [DF].[id]
        END
    END


  UPDATE [DFX]
     SET [DFX].[foldershared] = [FSDF].[Shared],
         [DFX].[folderisours] = [IO].[IsOurs]
    FROM @DocFoldersX [DFX]
         INNER JOIN [dbo].[FileSharingDocFolders] [FSDF]
                    CROSS APPLY (SELECT CASE WHEN COUNT(CASE WHEN ISNULL([FSS].[CollabDocFolderID], 0) = 0
                                                                  THEN 1
                                                             ELSE NULL END) = 0
                                                  THEN 0
                                             ELSE 1 END AS [IsOurs]
                                   FROM [dbo].[FileSharingMapping] [FSM]
                                        INNER JOIN [dbo].[FileSharingServers] [FSS]
         ON [FSS].[id] = [FSM].[FileSharingServersID]
                                  WHERE [FSM].[FileSharingDocFoldersID] = [FSDF].[DocFolderID]) [IO]
                 ON [FSDF].[DocFolderID] = [DFX].[id]

  
  DECLARE @ITEM INT=0

  
	WHILE EXISTS(SELECT NULL FROM [dbo].[DocFolders] [DF]
			  INNER JOIN @DocFoldersX DocFoldersX ON DF.parent=DocFoldersX.Id And Iteration=@Item)
    BEGIN

			INSERT
              INTO @DocFoldersX
                  ([id]
                  ,[parent]        
				  ,[foldername]    
				  ,[foldericon]    
				  ,[folderimage]	
				  ,[foldershared]  
				  ,[folderisours]  
				  ,[docfoldercount]
				  ,Iteration)
              SELECT [DF].[id],
                   ISNULL(DocFoldersX.ID, 0) [parent],
                   DF.[foldername],
                   [DF].[foldericon],
				   [DFU].[image],   
                   0 [foldershared],
                   1 [folderisours],
                   0 [docfoldercount], --INTO #DocFoldersX_Itr
				   @ITEM + 1 [Iteration]
              FROM [dbo].[DocFolders] [DF]
			  INNER JOIN @DocFoldersX DocFoldersX ON DF.parent=DocFoldersX.Id
			  LEFT JOIN [dbo].[DocFolderUploadedImages] [DFU]  
			  ON [DF].[foldericon] = [DFU].[id]    
             WHERE DocFoldersX.Iteration=@ITEM
			  ORDER BY [DF].[foldername],
					   [DF].[id]


			 SET @ITEM=@ITEM+1 
         
    END

  UPDATE [DF]
     SET [DF].[docfoldercount] = [TTD].[TOTAL]
    FROM @DocFoldersX  [DF]
         CROSS APPLY (SELECT COUNT(1) AS [TOTAL]
                        FROM [dbo].[DocFolderDocuments] [DFD]
                       WHERE [DFD].[DocFolderID] = [DF].[id]) [TTD]
	WHERE
	[DF].[Parent] <> -1

	Insert into @DocFolders([id],[parent],[foldername],[foldericon],[folderimage],[foldershared],[folderisours],[documentcount])
	select [id],[parent],[foldername],[foldericon],[folderimage],[foldershared],[folderisours],[docfoldercount]
    from 
	@DocFoldersX 
	Order by Foldername,
	Case when parent =0 then Id else parent end

  RETURN
END
GO

IF OBJECT_ID(N'[KAAS_CP_GETFILEPATH]',N'FN')IS NOT NULL
    DROP FUNCTION [KAAS_CP_GETFILEPATH]
GO


CREATE FUNCTION
       [dbo].[KAAS_CP_GETFILEPATH]
	   (@DOCUMENT        VARCHAR(500),
	   @FPATH              VARCHAR(500))
RETURNS VARCHAR(255)
AS
/*******************************************************************************************************
* This function is used to restrict file not exceed 255 character.									   *
*                                                                                                      *
* Stored Procedure Name	: [dbo].[KAAS_CP_GETFILEPATH]												   *
* Copied from			: [dbo].[ky_NETFNGETFILEPATH]												   *
* Copied from			: [dbo].[KAAS_FN_GETFILEPATH]												   *
*                                                                                                      *
* Modification History	:                                                                              *
* 2019-04-12	Vinodhan K		Created                                                                *
* 2022-02-11	Vinodhkumar.M   Created                                                                *
 *******************************************************************************************************/
BEGIN
    DECLARE @LEN INT
    DECLARE @RPATH VARCHAR(2000)
    SET @RPATH =  SUBSTRING(@FPATH,LEN(@FPATH)-CHARINDEX ('\',REVERSE(@FPATH))+2,LEN(@FPATH))
    SET @FPATH =  REPLACE(@FPATH,@RPATH,@DOCUMENT  )
    SET @LEN = CHARINDEX ('_',REVERSE(@DOCUMENT))
    IF( @LEN = 0)
    BEGIN
        SET @RPATH = @FPATH
    END
    ELSE
    BEGIN
        SET @RPATH = REPLACE(@FPATH,SUBSTRING(@DOCUMENT,0,LEN(@DOCUMENT)-CHARINDEX ('_',REVERSE(@DOCUMENT))+1)  ,'')
    END
    IF(LEN(@FPATH) > 255)
    BEGIN
        IF(LEN(@RPATH) < 255)
        BEGIN
            DECLARE @PLENGTH INT
            SET @PLENGTH = 255 - LEN(@RPATH )
            DECLARE @FNAME VARCHAR(200) -- PINO 2015-04-20
      SET @FNAME = SUBSTRING(@DOCUMENT,0,LEN(@DOCUMENT)-CHARINDEX ('_',REVERSE(@DOCUMENT))+1) --PINO 2015-04-20
            SET @RPATH =  SUBSTRING(@FNAME,0,@PLENGTH)
            SET @FPATH = REPLACE(@FPATH, @FNAME, @RPATH)
            RETURN @FPATH
        END
        ELSE IF(LEN(@RPATH) = 255)
        BEGIN
            RETURN  @RPATH
        END
        ELSE
        BEGIN
            RETURN 'NOCHANGE'
        END
    END
    ELSE
    BEGIN
        RETURN @FPATH
    END
 RETURN 'NOCHANGE'
END
GO


IF OBJECT_ID(N'KAAS_CP_GetLastLoginDateTime',N'FN') IS NOT NULL
  BEGIN
    DROP FUNCTION [dbo].[KAAS_CP_GetLastLoginDateTime]
  END
GO

 CREATE FUNCTION
        [dbo].[KAAS_CP_GetLastLoginDateTime]
       (@ClientLoginId	  BIGINT)
RETURNS DATETIME
AS
/*************************************************************************************************************
 *                                                                                                           *
 * Stored Procedure Name : KAAS_CP_GetLastLoginDateTime	                                                     *
 *                                                                                                           *
 * Retrieve last login access date and time for user														 *
 *                                                                                                           *
 * Modification History:                                                                                     *
 * 2021-06-28    Aakif		      Created                                                                    *
 *************************************************************************************************************/
BEGIN
	DECLARE @ReturnValue DATETIME

	SELECT
		@ReturnValue =  LastAccessedDateTime 
	FROM(
		SELECT
			ROW_NUMBER() OVER (ORDER BY LogonInfoId DESC)	AS RowNum,
			LastAccessedDateTime							AS LastAccessedDateTime
		FROM
			[ClientPortalLogonInfo]
		WHERE
			ClientLoginId = @ClientLoginId) T 
	WHERE
		RowNum = 2

	RETURN @ReturnValue
END
GO

IF OBJECT_ID(N'KAAS_CP_GetLastOrNextActionDate',N'FN') IS NOT NULL
  BEGIN
    DROP FUNCTION [dbo].[KAAS_CP_GetLastOrNextActionDate]
  END
GO

 CREATE FUNCTION
        [dbo].[KAAS_CP_GetLastOrNextActionDate]
       (@CaseCode	  VARCHAR(15),
	    @IsLastAction BIT = 1,
	    @IsMilestone  BIT = 0)
RETURNS DATETIME
AS
/*************************************************************************************************************
 *                                                                                                           *
 * Stored Procedure Name : KAAS_CP_GetLastOrNextActionDate                                                   *
 *                                                                                                           *
 * Retrieve last action, last milestone and next action date of corresponding to a matter code               *
 *                                                                                                           *
 * Modification History:                                                                                     *
 * 2021-06-09    Aakif		      Created                                                                    *
 *                                                                                                           *
 *************************************************************************************************************/
BEGIN
	DECLARE @ReturnValue DATETIME

	-- LAST ACTION DATE
	IF(@IsLastAction = 1)
	BEGIN
		SELECT
			TOP 1 @ReturnValue = LDIA.[DATE]
		FROM 
			[dbo].[diary] LDIA 
		WHERE
			LDIA.[CASECODE] = @CaseCode  
			AND LDIA.[STATUS] = 1  
			AND ISNULL(LDIA.[PUBLISH], 'N') = 'P'
		ORDER BY LDIA.[DATE] DESC
	END

	-- LAST MILESTONE DATE
	ELSE IF(@IsMilestone = 1)
	BEGIN
		SELECT
			TOP 1 @ReturnValue = MDIA.[DATE]
		FROM
			[dbo].[diary] MDIA
		WHERE 
			MDIA.[CASECODE] = @CaseCode
			AND ISNULL(MDIA.[PUBLISH], 'N') = 'P'
			AND MDIA.[HIGHLIGHTED] = 'Y'
		ORDER BY MDIA.[DATE] DESC
	END

	-- NEXT ACTION DATE
	ELSE IF(@IsLastAction = 0)
	BEGIN
		SELECT
			TOP 1 @ReturnValue = NDIA.[DATE]
		FROM
			dbo.[diary] NDIA
		WHERE 
			NDIA.[CASECODE] = @CaseCode
			AND ISNULL(NDIA.[PUBLISH], 'N') = 'P'
			AND NDIA.[STATUS] = 0
		ORDER BY NDIA.[DATE] ASC
	END
	
	RETURN @ReturnValue
END
GO
IF OBJECT_ID(N'KAAS_CP_GetLastOrNextActionText',N'FN') IS NOT NULL
  BEGIN
    DROP FUNCTION [dbo].[KAAS_CP_GetLastOrNextActionText]
  END
GO

 CREATE FUNCTION
        [dbo].[KAAS_CP_GetLastOrNextActionText]
       (@CaseCode	  VARCHAR(15),
	    @IsLastAction BIT = 1,
	    @IsMilestone  BIT = 0)
RETURNS VARCHAR(MAX)
AS
/*************************************************************************************************************
 *                                                                                                           *
 * Table Function Name : KAAS_CP_GetLastOrNextActionText                                                     *
 *                                                                                                           *
 * Retrieve last action, last milestone and next action text of corresponding to a matter code               *
 *                                                                                                           *
 * Modification History:                                                                                     *
 * 2021-06-09    Aakif		      Created                                                                    *
 *                                                                                                           *
 *************************************************************************************************************/
BEGIN
	DECLARE @ReturnValue VARCHAR(MAX)

	-- LAST ACTION DATE
	IF(@IsLastAction = 1)
	BEGIN
		SELECT
			TOP 1 @ReturnValue = LDIA.[TEXT1]
		FROM 
			[dbo].[diary] LDIA 
		WHERE
			LDIA.[CASECODE] = @CaseCode  
			AND LDIA.[STATUS] = 1  
			AND ISNULL(LDIA.[PUBLISH], 'N') = 'P'
		ORDER BY LDIA.[DATE] DESC
	END

	-- LAST MILESTONE DATE
	ELSE IF(@IsMilestone = 1)
	BEGIN
		SELECT
			TOP 1 @ReturnValue = MDIA.[TEXT1]
		FROM
			[dbo].[diary] MDIA
		WHERE 
			MDIA.[CASECODE] = @CaseCode
			AND ISNULL(MDIA.[PUBLISH], 'N') = 'P'
			AND MDIA.[HIGHLIGHTED] = 'Y'
		ORDER BY MDIA.[DATE] DESC
	END

	-- NEXT ACTION DATE
	ELSE IF(@IsLastAction = 0)
	BEGIN
		SELECT
			TOP 1 @ReturnValue = NDIA.[TEXT1]
		FROM
			dbo.[diary] NDIA
		WHERE 
			NDIA.[CASECODE] = @CaseCode
			AND ISNULL(NDIA.[PUBLISH], 'N') = 'P'
			AND NDIA.[STATUS] = 0
		ORDER BY NDIA.[DATE] ASC
	END

	SET @ReturnValue = RTRIM(ISNULL(@ReturnValue, ''))
	
	RETURN @ReturnValue
END
GO

IF OBJECT_ID(N'[KAAS_CP_NETFNGetUDFDefaultValue]',N'TF')IS NOT NULL
 BEGIN
    DROP FUNCTION [KAAS_CP_NETFNGetUDFDefaultValue]
 END
GO


CREATE FUNCTION [dbo].[KAAS_CP_NETFNGetUDFDefaultValue] (
       @FIELDNAME VARCHAR(200))
RETURNS @DefaultValues TABLE (
       [NumberValue] DECIMAL(19, 2),
       [DateValue]   DATETIME,
       [TEXT1]       VARCHAR(MAX))
AS
/*************************************************************************************************************
 *                                                                                                           *
 * FUNCTION NAME : [dbo].[KAAS_CP_NETFNGetUDFDefaultValue]                                                    *
 * Copied from : [dbo].[ky_NETFNGetUDFDefaultValue]                                                          *
 *                                                                                                           *
 * Returns the default values for the specified UDF. This must always return only 1 row.                     *
 *                                                                                                           *
 * Modification History                                                                                      *
 * 2020-04-01   John Ginnane   KEYD-6341 - Created                                                           *
 * 2020-04-07   John Ginnane   KEYD-6341 - Made sure procedure is backwards compatible                       *
 * 2022-04-21   Vinodhkumar.M      Created				                                                     *
 *************************************************************************************************************/
BEGIN
    DECLARE @INITIALVALUE        VARCHAR(MAX)
    DECLARE @INITIALFORMAT       VARCHAR(50)
    DECLARE @INITIALTYPE         VARCHAR(11)

    SELECT @INITIALVALUE  = [SUF].[INITIALVALUE],
           @INITIALFORMAT = [SUF].[FORMAT],
           @INITIALTYPE   = [SUF].[TYPE]
      FROM [dbo].[SystemUserDefinedFields] [SUF]
     WHERE [SUF].[FILEPREFIX] = 'UDF:'
       AND [SUF].[FIELDNAME]  = @FIELDNAME

    INSERT INTO @DefaultValues (
            [NumberValue],
            [DateValue],
            [TEXT1])
    VALUES (0,
            NULL,
            '')

    IF ISNULL(LTRIM(RTRIM(@INITIALVALUE)), '') = ''
    BEGIN
        RETURN
    END

    IF ((@INITIALTYPE = 'Numeric') OR (@INITIALFORMAT LIKE '@n%')) AND PATINDEX('%[^0-9.]%', @INITIALVALUE) <= 0 AND ISNUMERIC(@INITIALVALUE) = 1
    BEGIN
        UPDATE @DefaultValues
           SET [NumberValue] = CONVERT(DECIMAL(20, 2), CONVERT(MONEY, @INITIALVALUE))

        UPDATE @DefaultValues
           SET [TEXT1] = CONVERT(VARCHAR(20), [NumberValue])
    END
    ELSE IF (@INITIALTYPE = 'Date') OR (@INITIALFORMAT LIKE '@d%') OR (@INITIALFORMAT LIKE '%DD%') OR (@INITIALFORMAT LIKE '%MM%') OR (@INITIALFORMAT LIKE '%YY%')
    BEGIN
        IF ISNULL(RTRIM(LTRIM([dbo].[ky_SUFFormat](@INITIALVALUE, @INITIALFORMAT))), '') <> ''
        BEGIN
            UPDATE @DefaultValues
               SET [DateValue] = CONVERT(DATETIME, [dbo].[ky_SUFFormat](@INITIALVALUE, '@D8'))

            UPDATE [TMP]
               SET [TMP].[NumberValue] = [ClarionDate].[Value],
                   [TMP].[TEXT1] = CONVERT(VARCHAR(20), [ClarionDate].[Value])
              FROM @DefaultValues AS [TMP]
                   OUTER APPLY (SELECT [dbo].[ky_NETFNConvertDateToClarion]([TMP].[DateValue]) AS [Value]) AS [ClarionDate]
        END
    END
    ELSE
    BEGIN
        UPDATE @DefaultValues
           SET [TEXT1] = @INITIALVALUE
    END

    RETURN
END

GO

IF OBJECT_ID(N'KAAS_CP_TFGetMatterCodeForClient',N'TF') IS NOT NULL
  BEGIN
    DROP FUNCTION [dbo].[KAAS_CP_TFGetMatterCodeForClient]
  END
GO

 CREATE FUNCTION
        [dbo].[KAAS_CP_TFGetMatterCodeForClient]
       (@ClientLoginId         BIGINT)
RETURNS @MattersCode TABLE
       ([MatterCode]          VARCHAR(11))
AS
/*************************************************************************************************************
 *                                                                                                           *
 * Stored Procedure Name : KAAS_TF_GetDocFolders3                                                            *
 *                                                                                                           *
 * Retrieve list of matter corresponding to a client                                                         *
 *                                                                                                           *
 * Modification History:                                                                                     *
 * 2021-06-09    Aakif		      Created                                                                    *
 *                                                                                                           *
 *************************************************************************************************************/
BEGIN
	INSERT @MattersCode

	SELECT 
		MAT.[Code]
	FROM 
		[ClientLoginAccess] CL
		JOIN [Contacts] CON
			ON CON.Code = CL.ContactCode
		JOIN [matters] MAT
			ON MAT.ClientCode = CON.Code
	WHERE
		CL.HasAccessToAllMatter = 'Y'
		AND CL.ClientLoginId = @ClientLoginId
		AND CON.RemoteAccess = 'Y'
		AND MAT.Publish = 'P' 

	UNION
		SELECT 
			MatterCode
		FROM 
			[ClientLoginAccess] CLA
			JOIN [matters] MTR
				ON CLA.MatterCode = MTR.[Code]
			JOIN [Contacts] CTS
				ON CTS.Code = MTR.ClientCode
		WHERE 
			ISNULL(CLA.HasAccessToAllMatter, 'N') = 'N'
			AND CLA.ClientLoginId = @ClientLoginId
			AND	Publish = 'P'
			AND CTS.RemoteAccess = 'Y'
		
	RETURN

END
GO