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_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_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