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