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

/**************************************************************************************
 *                                                                                    *
 * Stored Procedure : [KAAS_ConvertTimeToClarion]									  *
 * 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.																  *
 *																					  *
 * 2019-06-04	Vinodhan	Created [KAAS__ConvertTimeToClarion]					  *
 * 																					  *
 **************************************************************************************/
create function
       [dbo].[KAAS_ConvertTimeToClarion]
      (@datetime    datetime)
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 EXISTS
  (SELECT TOP 1 1
     FROM SYS.[objects] SO
    WHERE SO.[name] = N'KAAS_FNConvertDateToUTC'
      AND SO.[type] = 'FN')
  BEGIN
    DROP FUNCTION [dbo].[KAAS_FNConvertDateToUTC]
  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_FNConvertDateToUTC]
	( 
		@Date		DATETIME,
		@Handler	NVARCHAR(10)
	)
RETURNS DATETIME
AS
/*******************************************************************************************************
* This function is used to convert local time to UTC				                                   *
*                                                                                                      *
* Function	: KAAS_FNGetDateForTimeZone																   *
*                                                                                                      *
* Modification History	:                                                                              *
* 2019-10-29	Arun V		Created				                                                   *
*******************************************************************************************************/ 
BEGIN
	RETURN
	(
			SELECT  @Date AT TIME ZONE dbo.KAAS_FNGetHandlerTimeZone(@Handler) AT TIME ZONE ''UTC''			
	)
End')
  END
ELSE
  BEGIN
    EXEC ('CREATE FUNCTION [dbo].[KAAS_FNConvertDateToUTC]
( 
	@Date		DATETIME,
	@Handler	NVARCHAR(10)
)
RETURNS DATETIME
AS
/*******************************************************************************************************
* This function is used to convert local time to UTC	                            				   *
*                                                                                                      *
* Function	: KAAS_FNConvertDateToUTC																   *
*                                                                                                      *
* Modification History	:                                                                              *
* 2019-10-29	Arun V		Created					                                                   *
*******************************************************************************************************/ 
BEGIN
	RETURN
	(
			SELECT @Date
	)
End')
  END

GO


IF EXISTS
  (SELECT TOP 1 1
     FROM SYS.[objects] SO
    WHERE SO.[name] = N'KAAS_FNConvertUTCDateToTargetTimeZone'
      AND SO.[type] = 'FN')
  BEGIN
    DROP FUNCTION [dbo].[KAAS_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_FNConvertUTCDateToTargetTimeZone]
	( 
		@Date		DATETIME,
		@Handler	NVARCHAR(10)
	)
RETURNS DATETIME
AS
/*******************************************************************************************************
* This function is used to convert local time to specified zone time. default is GMT				   *
*                                                                                                      *
* Function	: KAAS_FNGetDateForTimeZone																   *
*                                                                                                      *
* Modification History	:                                                                              *
* 2019-07-03	Rajesh P		Created				                                                   *
*******************************************************************************************************/ 
BEGIN
	RETURN
	(
			SELECT CONVERT(DATETIME,CONVERT(DATETIMEOFFSET, @Date) AT TIME ZONE (SELECT dbo.KAAS_FNGetHandlerTimeZone(@Handler)))
	)
End')
  END
ELSE
  BEGIN
    EXEC ('CREATE FUNCTION [dbo].[KAAS_FNConvertUTCDateToTargetTimeZone]
( 
	@Date		DATETIME,
	@Handler	NVARCHAR(10)
)
RETURNS DATETIME
AS
/*******************************************************************************************************
* This function is used to convert local time to specified zone time. default is GMT				   *
*                                                                                                      *
* Function	: KAAS_FNGetDateForTimeZone																   *
*                                                                                                      *
* Modification History	:                                                                              *
* 2019-07-03	Rajesh P		Created				                                                   *
*******************************************************************************************************/ 
BEGIN
	RETURN
	(
			SELECT @Date
	)
End')
  END

GO


IF OBJECT_ID('KAAS_FNGetHandlerTimeZone',N'FN') IS NOT NULL
	DROP FUNCTION KAAS_FNGetHandlerTimeZone
GO
CREATE FUNCTION [dbo].[KAAS_FNGetHandlerTimeZone](
	@Handler VARCHAR(10)
)
RETURNS NVARCHAR(50)
AS
/*******************************************************************************************************
* This function is used to get the handler time zone, for now its default to GMT standard Time		   *
*                                                                                                      *
* Function	: KAAS_FNGetDateForTimeZone																   *
*                                                                                                      *
* Modification History	:                                                                              *
* 2019-07-03	Rajesh P		Created				                                                   *
* 2019-09-24	Arun V			Handler Code added to get used based time zone, default to GMT         *
*******************************************************************************************************/ 
BEGIN
	DECLARE @HandlerTimeZone NVARCHAR(50)
	
	SELECT 
		@HandlerTimeZone = [Value] 
	FROM 
		UserSettings 
	WHERE 
		[Handler] = @Handler AND 
		[Key] = 'TimeZone'
	
	SELECT 
		@HandlerTimeZone = ISNULL(@HandlerTimeZone, 'GMT Standard Time')

	RETURN @HandlerTimeZone
End
GO
IF EXISTS
  (SELECT TOP 1 1
     FROM SYS.[objects] SO
    WHERE SO.[name] = N'KAAS_FN_ConvertClarionTimeToHHMM'
      AND SO.[type] = 'FN')
  BEGIN
    DROP FUNCTION [dbo].[KAAS_FN_ConvertClarionTimeToHHMM]
  END
GO

create function
       [dbo].[KAAS_FN_ConvertClarionTimeToHHMM]
      (@ClarionTime INT)
returns VARCHAR(5)
AS
/**************************************************************************************
 *
 * converts an integer value between 1 and 8640000, representing a Clarion Time Value
 * in 1/100 of a Second since Midnight to a Time representation in HH:MM format
 *
 * Stored Procedure name : KAAS_FN_ConvertClarionTimeToHHMM
 * Copied From           : ky_NETFNConvertClarionTimeToHHMM
 *
 * This function prevents Cardinality Estimate Warnings when used in SELECT statements
 *
 **************************************************************************************/
BEGIN

DECLARE @HHMM   VARCHAR(5)
DECLARE @HOUR   INT
DECLARE @MINUTE INT

IF @ClarionTime IS NULL
  BEGIN
    SET @HHMM = ''
  END
ELSE IF @ClarionTime < 1
  BEGIN
    SET @HHMM = ''
  END
ELSE IF @ClarionTime > 8640000
  BEGIN
    SET @HHMM = ''
  END
ELSE
  BEGIN
    SET @ClarionTime = @ClarionTime - 1

    SET @HOUR = FLOOR(@ClarionTime / 360000)

    SET @ClarionTime = @ClarionTime - (@HOUR * 360000)

    SET @MINUTE = FLOOR(@ClarionTime / 6000)

    SET @HHMM = SUBSTRING(CONVERT(VARCHAR(3), 100 + @HOUR), 2, 2) + ':' + SUBSTRING(CONVERT(VARCHAR(3), 100 + @MINUTE), 2, 2)
  END

RETURN @HHMM

END

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

CREATE FUNCTION
       [dbo].[KAAS_FN_ConvertDate]
      (@DATE DATETIME, @FORMAT VARCHAR(50))
RETURNS VARCHAR(MAX)
AS
/*************************************************************************************************************
 *                                                                                                           *
 * Stored Procedure Name : KAAS_FN_ConvertDate                                                               *
 * Copied From           : [dbo].[ky_NETFNConvertDate]                                                       *
 *                                                                                                           *
 * An extended "Format" function                                                                             *
 *                                                                                                           *
 * Compatibility information - PLEASE update older versions if necessary to ensure the compatible software   *
 *                                    remains fully functional                                               *
 *   *****************************************************************************************************   *
 *   *                                                                                                   *   *
 *   * Supersedes:                              -                                                        *   *
 *   * First compatible version:                5.6.4.1                                                  *   *
 *   * Last compatible software version:        -                                                        *   *
 *   * Superseded by:                           -                                                        *   *
 *   *                                                                                                   *   *
 *   *****************************************************************************************************   *
 *                                                                                                           *
 * Modification History                                                                                      *
 * 2018-12-04   Pino Carafa       Created                                                                    *
 * 2019-01-11   Pino Carafa       Added support for @d8 (DD MMM YYYY) format                                 *
 * 2019-02-06   Pino Carafa       Unnecessary space after 'D' format                                         *
 * 2019-27-07   Vinodhan K        Created KAAS_FN_ConvertDate                                                *
 *                                                                                                           *
 *************************************************************************************************************/
BEGIN

IF ISNUMERIC(@FORMAT) = 1
  BEGIN
    RETURN CONVERT(VARCHAR, @DATE, CONVERT(INT, @FORMAT))
  END

IF @Format = '@d6'
  BEGIN
    RETURN CONVERT(VARCHAR, @DATE, 103)
  END

IF @Format = '@d8'
  BEGIN
    RETURN CONVERT(VARCHAR, @DATE, 106)
  END

IF @format = '@d18'
  BEGIN
    RETURN CONVERT(VARCHAR(2), SUBSTRING(CONVERT(VARCHAR(3), 100 + DAY(@DATE)), 2, 2)) + ' ' + DATENAME(month, @DATE) + ' ' + CONVERT(VARCHAR(4), YEAR(@DATE))
  END

IF @Format = '@t6'
  BEGIN
    RETURN CONVERT(VARCHAR, @DATE, 108)
  END

IF @Format = '@t4'
  BEGIN
    RETURN SUBSTRING(CONVERT(VARCHAR, @DATE, 108), 1, 5)
  END

IF @Format = 'sql'
  BEGIN
    RETURN CONVERT(VARCHAR, @DATE, 112)
  END

IF @Format LIKE 'verbose%'
  BEGIN
    IF @FORMAT = 'verbose'
      BEGIN
        SET @FORMAT = 'verbose(1, 1, 1, 1, 1, 0)'
      END

    DECLARE @RESULT VARCHAR(200)

    IF @Format LIKE 'verbose(%'
      BEGIN
        DECLARE @UseDayName BIT
        DECLARE @UseThe BIT
        DECLARE @UseDay BIT
        DECLARE @UseOf BIT
        DECLARE @UseComma BIT
        DECLARE @Capitalise BIT
        DECLARE @COMMA INT
        SET @Format = SUBSTRING(@Format, 9, LEN(@Format) - 8)
        SET @COMMA = CHARINDEX(',', @Format, 1)
        IF @COMMA > 0
          BEGIN
            SET @UseDayName = CONVERT(BIT, RTRIM(SUBSTRING(@Format, 1, @COMMA - 1)))
            SET @Format = LTRIM(SUBSTRING(@Format, @COMMA + 1, LEN(@Format) - @COMMA))
          END
        SET @COMMA = CHARINDEX(',', @Format, 1)
        IF @COMMA > 0
          BEGIN
            SET @UseThe = CONVERT(BIT, RTRIM(SUBSTRING(@Format, 1, @COMMA - 1)))
            SET @Format = LTRIM(SUBSTRING(@Format, @COMMA + 1, LEN(@Format) - @COMMA))
          END
        SET @COMMA = CHARINDEX(',', @Format, 1)
        IF @COMMA > 0
          BEGIN
            SET @UseDay = CONVERT(BIT, RTRIM(SUBSTRING(@Format, 1, @COMMA - 1)))
            SET @Format = LTRIM(SUBSTRING(@Format, @COMMA + 1, LEN(@Format) - @COMMA))
          END
        SET @COMMA = CHARINDEX(',', @Format, 1)
        IF @COMMA > 0
          BEGIN
            SET @UseOf = CONVERT(BIT, RTRIM(SUBSTRING(@Format, 1, @COMMA - 1)))
            SET @Format = LTRIM(SUBSTRING(@Format, @COMMA + 1, LEN(@Format) - @COMMA))
          END
        SET @COMMA = CHARINDEX(',', @Format, 1)
        IF @COMMA > 0
          BEGIN
            SET @UseComma = CONVERT(BIT, RTRIM(SUBSTRING(@Format, 1, @COMMA - 1)))
            SET @Format = LTRIM(SUBSTRING(@Format, @COMMA + 1, LEN(@Format) - @COMMA))
          END
        SET @COMMA = CHARINDEX(')', @Format, 1)
        IF @COMMA > 0
          BEGIN
            SET @Capitalise = CONVERT(BIT, RTRIM(SUBSTRING(@Format, 1, @COMMA - 1)))
            SET @Format = LTRIM(SUBSTRING(@Format, @COMMA + 1, LEN(@Format) - @COMMA))
          END
        IF @UseDayName = 1
          BEGIN
            SET @RESULT = DATENAME(WEEKDAY, @DATE)
            IF @UseComma = 1
              BEGIN
                SET @RESULT = @RESULT + ', '
              END
            ELSE
              BEGIN
                SET @RESULT = @RESULT + ' '
              END
          END
        ELSE
          BEGIN
            SET @RESULT = ''
          END

        SET @RESULT = @RESULT + [dbo].[KAAS_FN_VerboseDate](@Date, @UseThe, @UseDay, @UseOf, @UseComma, @Capitalise)
        RETURN @RESULT
      END
    ELSE
      BEGIN
        RETURN [dbo].[KAAS_FN_VerboseDate](@Date, 1, 1, 1, 1, 0)
      END
  END

SET @RESULT = @FORMAT
SET @RESULT = REPLACE(@RESULT, 'DDDD', '<**xn**>')
SET @RESULT = REPLACE(@RESULT, 'DDD', '<**xsn**>')
SET @RESULT = REPLACE(@RESULT, 'DD', '<**x2**>')
SET @RESULT = REPLACE(@RESULT, 'D', '<**x**>')
SET @RESULT = REPLACE(@RESULT, 'MMMM', '<**xxn**>')
SET @RESULT = REPLACE(@RESULT, 'MMM', '<**xxsn**>')
SET @RESULT = REPLACE(@RESULT, 'MM', '<**xx2**>')
SET @RESULT = REPLACE(@RESULT, 'MI', '<**w2**>')
SET @RESULT = REPLACE(@RESULT, 'M', '<**xx**> ')
SET @RESULT = REPLACE(@RESULT, 'HH', '<**w1**>')
SET @RESULT = REPLACE(@RESULT, 'SS', '<**w3**>')
SET @RESULT = REPLACE(@RESULT, 'YYYY', '<**yf**>')
SET @RESULT = REPLACE(@RESULT, 'YY', '<**ys**>')
SET @RESULT = REPLACE(@RESULT, '<**xn**>', DATENAME(WEEKDAY, @DATE))
SET @RESULT = REPLACE(@RESULT, '<**xsn**>', SUBSTRING(DATENAME(WEEKDAY, @DATE), 1, 3))
SET @RESULT = REPLACE(@RESULT, '<**x2**>', SUBSTRING(CONVERT(VARCHAR, 100 + DAY(@DATE)), 2, 2))
SET @RESULT = REPLACE(@RESULT, '<**x**>', CONVERT(VARCHAR, DAY(@DATE)))
SET @RESULT = REPLACE(@RESULT, '<**xxn**>', DATENAME(MONTH, @DATE))
SET @RESULT = REPLACE(@RESULT, '<**xxsn**>', SUBSTRING(DATENAME(MONTH, @DATE), 1, 3))
SET @RESULT = REPLACE(@RESULT, '<**xx2**>', SUBSTRING(CONVERT(VARCHAR, 100 + MONTH(@DATE)), 2, 2))
SET @RESULT = REPLACE(@RESULT, '<**yf**>', CONVERT(VARCHAR, YEAR(@DATE)))
SET @RESULT = REPLACE(@RESULT, '<**ys**>', SUBSTRING(CONVERT(VARCHAR, YEAR(@DATE)), 3, 2))
SET @RESULT = REPLACE(@RESULT, '<**w1**>', SUBSTRING(CONVERT(VARCHAR, 100 + DATEPART(HOUR, @DATE)), 2, 2))
SET @RESULT = REPLACE(@RESULT, '<**w2**>', SUBSTRING(CONVERT(VARCHAR, 100 + DATEPART(MINUTE, @DATE)), 2, 2))
SET @RESULT = REPLACE(@RESULT, '<**w3**>', SUBSTRING(CONVERT(VARCHAR, 100 + DATEPART(SECOND, @DATE)), 2, 2))
RETURN @RESULT

END

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

CREATE FUNCTION
       [dbo].[KAAS_FN_ConvertDateToClarion]
      (@datetime DATETIME)
RETURNS INT
AS
/*****************************************************************************

 Converts a date/time value to a Clarion number.

 Stored Procedure name : KAAS_FN_ConvertDateToClarion
 Copy From             : ky_NETFNConvertDateToClarion

 2019-07-30    Vinodhan K    Created KAAS_FN_ConvertDateToClarion

 *****************************************************************************/
BEGIN
  DECLARE @notime DATETIME
  SET @notime = CONVERT(VARCHAR(8), @datetime, 112)
  RETURN DATEDIFF(dd, '18001228', @datetime)
END
GO
IF EXISTS
  (SELECT TOP 1 1
     FROM SYS.[objects] SO
    WHERE SO.[name] = N'KAAS_FN_ConvertNumber'
      AND SO.[type] = 'FN')
  BEGIN
    DROP FUNCTION [dbo].[KAAS_FN_ConvertNumber]
  END
GO

-- Please note: the FORMAT function is not supported in SQL2008, so we can't make this Function
-- quite as good there as we could make it on SQL2012 or later
DECLARE @VERSION NVARCHAR(128)
DECLARE @VERSIONNO INT
DECLARE @NCOMMAND NVARCHAR(MAX)

SET @VERSION = @@VERSION

DECLARE @POS INT

SET @POS = PATINDEX('% - %', @VERSION)

SET @VERSIONNO = 0

IF @POS > 0
  BEGIN
    DECLARE @END INT
    SET @END = CHARINDEX('.', @VERSION, @POS)

    SET @VERSION = SUBSTRING(@VERSION, @POS + 3, @END - @POS - 3)

    IF ISNUMERIC(@VERSION) = 1
      BEGIN
        SET @VERSIONNO = CONVERT(INT, @VERSION)
      END      
  END

IF @VERSIONNO > 10
  BEGIN
SET @NCOMMAND = N'CREATE FUNCTION
       [dbo].[KAAS_FN_ConvertNumber]
      (@NUMBER FLOAT, @FORMAT VARCHAR(200), @THOUSANDSEPARATORS BIT, @NEGBRACKETS BIT, @CULTURE VARCHAR(10))
RETURNS VARCHAR(MAX)
AS
/*************************************************************************************************************
 *                                                                                                           *
 * Stored Procedure Name : KAAS_FN_ConvertNumber                                                             *
 * Copied From           : [dbo].[ky_NETFNConvertNumber]                                                     *
 *                                                                                                           *
 * Convert a Number into the desired Format                                                                  *
 *                                                                                                           *
 * Compatibility information - PLEASE update older versions if necessary to ensure the compatible software   *
 *                                    remains fully functional                                               *
 *   *****************************************************************************************************   *
 *   *                                                                                                   *   *
 *   * Supersedes:                              -                                                        *   *
 *   * First compatible version:                5.6.4.1                                                  *   *
 *   * Last compatible software version:        -                                                        *   *
 *   * Superseded by:                           -                                                        *   *
 *   *                                                                                                   *   *
 *   *****************************************************************************************************   *
 *                                                                                                           *
 * Modification History                                                                                      *
 * 2018-12-04   Pino Carafa       Created                                                                    *
 * 2019-07-27   Vinodhan K        Created KAAS_FN_ConvertNumber                                              *
 *                                                                                                           *
 *************************************************************************************************************/
BEGIN

DECLARE @THSEP VARCHAR(1)
DECLARE @DSEP VARCHAR(1)

SET @CULTURE = ISNULL(@CULTURE, ''en-IE'')

DECLARE @DIGITS INT
DECLARE @DECIMALS INT
DECLARE @PERIOD INT
DECLARE @REST VARCHAR(50)
DECLARE @RESULT VARCHAR(200)

SET @REST = FORMAT(1000, ''N1'', @CULTURE)
SET @THSEP = SUBSTRING(@REST, 2, 1)
SET @DSEP = SUBSTRING(@REST, 6, 1)

IF @FORMAT LIKE ''@n%''
  BEGIN
    SET @FORMAT = SUBSTRING(@FORMAT, 3, LEN(@FORMAT) - 2)
    SET @PERIOD = CHARINDEX(''.'', @FORMAT, 1)
    IF @PERIOD > 0
      BEGIN
        SET @DIGITS = CONVERT(INT, SUBSTRING(@FORMAT, 1, @PERIOD - 1))
        SET @DECIMALS = CONVERT(INT, SUBSTRING(@FORMAT, @PERIOD + 1, LEN(@FORMAT) - @PERIOD))
      END
    ELSE
      BEGIN
        SET @DIGITS = CONVERT(INT, @FORMAT)
        SET @DECIMALS = 0
      END

    SET @FORMAT = ''''
    WHILE @DIGITS > 3
      BEGIN
        IF @FORMAT <> ''''
          BEGIN
            SET @FORMAT = ''###'' + CASE WHEN @THOUSANDSEPARATORS = 1 THEN @THSEP ELSE '''' END + @FORMAT
          END
        ELSE
          BEGIN
            SET @FORMAT = ''###''
          END
        SET @DIGITS = @DIGITS - 3
      END

      IF @DIGITS > 0
        BEGIN
          SET @REST = ''''
          WHILE @DIGITS > 0
            BEGIN
              SET @REST = @REST + ''#''
              SET @DIGITS = @DIGITS - 1
            END
          SET @FORMAT = @REST + CASE WHEN @FORMAT = '''' THEN '''' WHEN @THOUSANDSEPARATORS = 1 THEN @THSEP ELSE '''' END + @FORMAT
        END

      IF @DECIMALS > 0
        BEGIN
          SET @FORMAT = @FORMAT + @DSEP
          WHILE @DECIMALS > 0
            BEGIN
              SET @FORMAT = @FORMAT + ''0''
              SET @DECIMALS = @DECIMALS - 1
            END
        END

    IF ISNULL(@NEGBRACKETS, 0) = 0
      BEGIN
        SET @RESULT = FORMAT(@NUMBER, @FORMAT, @CULTURE)
      END
    ELSE
      BEGIN
        IF @NUMBER < 0
          BEGIN
            SET @RESULT = ''('' + FORMAT(-@NUMBER, @FORMAT, @CULTURE) + '')''
          END
        ELSE
          BEGIN
            SET @RESULT = FORMAT(@NUMBER, @FORMAT, @CULTURE)
          END
      END

    --SELECT @RESULT
    RETURN @RESULT
  END

IF ISNULL(@NEGBRACKETS, 0) = 0
  BEGIN
    SET @RESULT = FORMAT(@NUMBER, @FORMAT, @CULTURE)
  END
ELSE
  BEGIN
    IF @NUMBER < 0
      BEGIN
        SET @RESULT = ''('' + FORMAT(-@NUMBER, @FORMAT, @CULTURE) + '')''
      END
    ELSE
      BEGIN
        SET @RESULT = FORMAT(@NUMBER, @FORMAT, @CULTURE)
      END
  END

IF ISNULL(@THOUSANDSEPARATORS, 0) = 0
  BEGIN
    SET @RESULT = REPLACE(@RESULT, @THSEP, '''')
  END

--SELECT @RESULT
RETURN @RESULT
END'
  END
ELSE
  BEGIN
SET @NCOMMAND = N'CREATE FUNCTION
       [dbo].[KAAS_FN_ConvertNumber]
      (@NUMBER FLOAT, @FORMAT VARCHAR(200), @THOUSANDSEPARATORS BIT, @NEGBRACKETS BIT, @CULTURE VARCHAR(10))
RETURNS VARCHAR(MAX)
AS
/*************************************************************************************************************
 *                                                                                                           *
 * Stored Procedure name : KAAS_FN_ConvertNumber                                                             *
 * Copied From           : [dbo].[ky_NETFNConvertNumber]                                                     *
 *                                                                                                           *
 * Convert a Number into the desired Format                                                                  *
 *                                                                                                           *
 * Compatibility information - PLEASE update older versions if necessary to ensure the compatible software   *
 *                                    remains fully functional                                               *
 *   *****************************************************************************************************   *
 *   *                                                                                                   *   *
 *   * Supersedes:                              -                                                        *   *
 *   * First compatible version:                5.6.4.1                                                  *   *
 *   * Last compatible software version:        -                                                        *   *
 *   * Superseded by:                           -                                                        *   *
 *   *                                                                                                   *   *
 *   *****************************************************************************************************   *
 *                                                                                                           *
 * Modification History                                                                                      *
 * 2018-12-04   Pino Carafa       Created                                                                    *
 * 2019-07-27   Vinodhan K        Created KAAS_FN_ConvertNumber                                              *
 *                                                                                                           *
 *************************************************************************************************************/
BEGIN

DECLARE @THSEP VARCHAR(1)
DECLARE @DSEP VARCHAR(1)

SET @CULTURE = ISNULL(@CULTURE, ''en-IE'')

DECLARE @DIGITS INT
DECLARE @DECIMALS INT
DECLARE @PERIOD INT
DECLARE @REST VARCHAR(50)
DECLARE @RESULT VARCHAR(200)

SET @REST = CONVERT(VARCHAR(10), CONVERT(MONEY, 1000), 1)
SET @THSEP = SUBSTRING(@REST, 2, 1)
SET @DSEP = SUBSTRING(@REST, 6, 1)

IF @FORMAT LIKE ''@n%''
  BEGIN
    SET @FORMAT = SUBSTRING(@FORMAT, 3, LEN(@FORMAT) - 2)
    SET @PERIOD = CHARINDEX(''.'', @FORMAT, 1)
    IF @PERIOD > 0
      BEGIN
        SET @DIGITS = CONVERT(INT, SUBSTRING(@FORMAT, 1, @PERIOD - 1))
        SET @DECIMALS = CONVERT(INT, SUBSTRING(@FORMAT, @PERIOD + 1, LEN(@FORMAT) - @PERIOD))
      END
    ELSE
      BEGIN
        SET @DIGITS = CONVERT(INT, @FORMAT)
        SET @DECIMALS = 0
      END

    SET @FORMAT = ''''
    WHILE @DIGITS > 3
      BEGIN
        IF @FORMAT <> ''''
          BEGIN
            SET @FORMAT = ''###'' + CASE WHEN @THOUSANDSEPARATORS = 1 THEN @THSEP ELSE '''' END + @FORMAT
          END
        ELSE
          BEGIN
            SET @FORMAT = ''###''
          END
        SET @DIGITS = @DIGITS - 3
      END

      IF @DIGITS > 0
        BEGIN
          SET @REST = ''''
          WHILE @DIGITS > 0
            BEGIN
              SET @REST = @REST + ''#''
              SET @DIGITS = @DIGITS - 1
            END
          SET @FORMAT = @REST + CASE WHEN @FORMAT = '''' THEN '''' WHEN @THOUSANDSEPARATORS = 1 THEN @THSEP ELSE '''' END + @FORMAT
        END

      IF @DECIMALS > 0
        BEGIN
          SET @FORMAT = @FORMAT + @DSEP
          WHILE @DECIMALS > 0
            BEGIN
              SET @FORMAT = @FORMAT + ''0''
              SET @DECIMALS = @DECIMALS - 1
            END
        END

    IF ISNULL(@NEGBRACKETS, 0) = 0
      BEGIN
        SET @RESULT = CONVERT(VARCHAR(20), CONVERT(MONEY, @NUMBER), 1) --FORMAT(@NUMBER, @FORMAT, @CULTURE)
      END
    ELSE
      BEGIN
        IF @NUMBER < 0
          BEGIN
            SET @RESULT = ''('' + CONVERT(VARCHAR(20), CONVERT(MONEY, @NUMBER), 1) + '')'' --''('' + FORMAT(-@NUMBER, @FORMAT, @CULTURE) + '')''
          END
        ELSE
          BEGIN
            SET @RESULT = CONVERT(VARCHAR(20), CONVERT(MONEY, @NUMBER), 1) --FORMAT(@NUMBER, @FORMAT, @CULTURE)
          END
      END

    --SELECT @RESULT
    RETURN @RESULT
  END

IF ISNULL(@NEGBRACKETS, 0) = 0
  BEGIN
    SET @RESULT = CONVERT(VARCHAR(20), CONVERT(MONEY, @NUMBER), 1) --FORMAT(@NUMBER, @FORMAT, @CULTURE)
  END
ELSE
  BEGIN
    IF @NUMBER < 0
      BEGIN
        SET @RESULT = ''('' + CONVERT(VARCHAR(20), CONVERT(MONEY, @NUMBER), 1) + '')'' --''('' + FORMAT(-@NUMBER, @FORMAT, @CULTURE) + '')''
      END
    ELSE
      BEGIN
        SET @RESULT = CONVERT(VARCHAR(20), CONVERT(MONEY, @NUMBER), 1) --FORMAT(@NUMBER, @FORMAT, @CULTURE)
      END
  END

IF ISNULL(@THOUSANDSEPARATORS, 0) = 0
  BEGIN
    SET @RESULT = REPLACE(@RESULT, @THSEP, '''')
  END

--SELECT @RESULT
RETURN @RESULT
END'
  END

EXEC sp_executesql @NCOMMAND
GO
IF EXISTS
  (SELECT TOP 1 1
     FROM SYS.[objects] SO
    WHERE SO.[name] = N'KAAS_FN_CSVToTable'
      AND SO.[type] = 'TF')
  BEGIN
    DROP FUNCTION [dbo].[KAAS_FN_CSVToTable]
  END
GO


CREATE FUNCTION [dbo].[KAAS_FN_CSVToTable] (@InStr VARCHAR(MAX))
RETURNS @TempTab TABLE
(id varchar(50) not null)
AS


/*******************************************************************************************************
* Converts CSV Values into Table by using Comma Split String										   *
*                                                                                                      *
* Stored Procedure Name	: [dbo].[KAAS_FN_CSVToTable]												   *
*                                                                                                      *
* Modification History	:                                                                              *
* 2019-05-17	Vinodhan K		Created			                                                       *
*******************************************************************************************************/


BEGIN

;-- Ensure input ends with comma
SET @InStr = REPLACE(@InStr + ',', ',,', ',')
DECLARE @SP INT
DECLARE @VALUE VARCHAR(1000)
WHILE PATINDEX('%,%', @INSTR ) <> 0 
BEGIN
SELECT @SP = PATINDEX('%,%',@INSTR)
SELECT @VALUE = LEFT(@INSTR , @SP - 1)
SELECT @INSTR = STUFF(@INSTR, 1, @SP, '')
INSERT INTO @TempTab(id) VALUES (@VALUE)
END
RETURN
END

GO




IF EXISTS
  (SELECT *
     FROM SYS.[objects] SO
    WHERE SO.[name] = 'KAAS_FN_DateDiffVerbose'
      AND SO.[type] = 'FN')
BEGIN
  DROP FUNCTION [dbo].[KAAS_FN_DateDiffVerbose]
END
GO

CREATE FUNCTION
       [dbo].[KAAS_FN_DateDiffVerbose]
      (@FromDate      DATETIME,
       @ToDate        DATETIME)
RETURNS VARCHAR(200)
AS
/*******************************************************************************************************
* Fetch Attachment list by diary number												                   *
*                                                                                                      *
* Stored Procedure Name	: [dbo].[KAAS_FN_DateDiffVerbose]			                                   *
* Copied from			: [dbo].[ky_NETFNDateDiffVerbose]				                               *
*                                                                                                      *
* Modification History	:                                                                              *
* 2019-04-16	Vinodhan K		Created                                                                *
 *******************************************************************************************************/

BEGIN
  DECLARE @OUTPUT VARCHAR(200)

  IF @FromDate IS NULL
    BEGIN
      RETURN ''
    END

  IF @ToDate IS NULL
    BEGIN
      RETURN ''
    END

  IF (@FromDate >= @ToDate)
    BEGIN
      RETURN ''
    END

  DECLARE @Work DATETIME
  DECLARE @YEARS INT

  SET @YEARS = DATEDIFF(YEAR, @FromDate, @ToDate)

  SET @Work = DATEADD(YEAR, @YEARS, @FromDate)

  IF (@Work > @ToDate)
    BEGIN
      SET @YEARS = @YEARS - 1
    END

  SET @OUTPUT = ''

  IF @YEARS = 1
    BEGIN
      IF @OUTPUT <> ''
        BEGIN
          SET @OUTPUT = @OUTPUT + ', '
        END
      
      SET @OUTPUT = @OUTPUT + '1 Year'
    END

  IF @YEARS > 1
    BEGIN
      IF @OUTPUT <> ''
        BEGIN
          SET @OUTPUT = @OUTPUT + ', '
        END
      
      SET @OUTPUT = @OUTPUT + CONVERT(VARCHAR(10), @YEARS) + ' Years'
    END

  SET @FromDate = DATEADD(YEAR, @YEARS, @FromDate)

  DECLARE @MONTHS INT

  SET @MONTHS = DATEDIFF(MONTH, @FromDate, @ToDate)

  SET @Work = DATEADD(MONTH, @MONTHS, @FromDate)

  IF (@Work > @ToDate)
    BEGIN
      SET @MONTHS = @MONTHS - 1
    END

  IF @MONTHS = 1
    BEGIN
      IF @OUTPUT <> ''
        BEGIN
          SET @OUTPUT = @OUTPUT + ', '
        END
      
      SET @OUTPUT = @OUTPUT + '1 Month'
    END

  IF @MONTHS > 1
    BEGIN
      IF @OUTPUT <> ''
        BEGIN
          SET @OUTPUT = @OUTPUT + ', '
        END
      
      SET @OUTPUT = @OUTPUT + CONVERT(VARCHAR(10), @MONTHS) + ' Months'
    END

  SET @FromDate = DATEADD(MONTH, @MONTHS, @FromDate)

  DECLARE @DAYS INT

  SET @DAYS = DATEDIFF(DAY, @FromDate, @ToDate)

  IF @DAYS = 1
    BEGIN
      IF @OUTPUT <> ''
        BEGIN
          SET @OUTPUT = @OUTPUT + ' and '
        END
      
      SET @OUTPUT = @OUTPUT + '1 Day'
    END

  IF @DAYS > 1
    BEGIN
      IF @OUTPUT <> ''
        BEGIN
          SET @OUTPUT = @OUTPUT + ' and '
        END
      
      SET @OUTPUT = @OUTPUT + CONVERT(VARCHAR(10), @DAYS) + ' Days'
    END

  RETURN @OUTPUT

END
GO
IF EXISTS
  (SELECT TOP 1 1
     FROM SYS.[objects] SO
    WHERE SO.[name] = 'KAAS_FN_DefaultBillLayoutReport'
      AND SO.[type] = 'FN')
  BEGIN
    DROP FUNCTION [dbo].[KAAS_FN_DefaultBillLayoutReport]
  END
GO

CREATE FUNCTION
       [dbo].[KAAS_FN_DefaultBillLayoutReport]
      ()
RETURNS INT
AS

/*******************************************************************************************************
*																									   *
*																									   *
*                                                                                                      *
* Stored Procedure Name	: [dbo].[KAAS_FN_DefaultBillLayoutReport]			                           *
* Copied from			: [dbo].[ky_FNDefaultBillLayoutReport]				                           *
*                                                                                                      *
* Modification History	:                                                                              *
* 2019-11-13	Dheepin Vijith K		Created                                                        *
 *******************************************************************************************************/

BEGIN

  DECLARE @ReportID INT

  SELECT @ReportID = CRR.[ReportID]
    FROM [dbo].[CRReport] CRR
   WHERE CRR.[report_type] = 'B'
     AND CRR.[name] = 'Bill Layout'

  IF (@ReportID IS NULL)
    BEGIN
      SELECT TOP 1 @ReportID = CRR.[ReportID]
        FROM [dbo].[CRReport] CRR
       WHERE CRR.[report_type] = 'B'
    END

  IF (@ReportID IS NULL)
    BEGIN
      SELECT TOP 1 @ReportID = CRR.[ReportID]
        FROM [dbo].[CRReport] CRR
       WHERE CRR.[name] = 'Bill Layout'
    END

  IF (@ReportID IS NULL)
    BEGIN
      SET @ReportID = 0
    END

  RETURN @ReportID
END

GO

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

CREATE FUNCTION
       [dbo].[KAAS_FN_GetColumnSize]
      (@ObjectID INT,
       @ColumnID INT)
RETURNS INT
AS
/*******************************************************************
 * Function Name : KAAS_FN_GetColumnSize
 * Copied From   : ky_NETFNGetColumnSize
 *
 * Modify History:
 * 2019-08-06   Vinodhan   Created KAAS_FN_GetColumnSize
 *******************************************************************/
BEGIN
  DECLARE @MAXLEN INT

  SELECT @MAXLEN = CASE WHEN SC.[system_type_id] IN (35, 99)
                        THEN 0
                   WHEN (SC.[system_type_id] IN (167, 231)) AND (SC.[max_length] = -1)
                        THEN 0
                   WHEN SC.[system_type_id] IN (167, 175, 231, 239)
                        THEN SC.[max_length]
                   WHEN SC.[system_type_id] = 204
                        THEN 1 --bit
                   WHEN SC.[system_type_id] = 48
                        THEN 3 --tinyint - valid values 0 to 255
                   WHEN SC.[system_type_id] = 52
                        THEN 6 --SMALLINT - valid values -32768 to 32767
                   WHEN SC.[system_type_id] = 56
                        THEN 11 --int - valid values -2147483648 to 2147483647
                   WHEN SC.[system_type_id] = 127
                        THEN 20 --BIGINT - valid values -9223372036854775808 to 9223372036854775807
                   WHEN (SC.[system_type_id] IN (60, 106, 108, 122)) AND (SC.[scale] = 0)
                        THEN SC.[precision] + 1 -- Allow for- sign
                   WHEN (SC.[system_type_id] IN (60, 106, 108, 122)) AND (SC.[scale] <> 0)
                        THEN SC.[precision] + 2 -- Allow for DECIMAL point and - sign
                   ELSE 0 END
    FROM sys.[columns] SC
   WHERE SC.[object_id] = @objectid
     AND SC.[column_id] = @columnid

  RETURN @MAXLEN
END

GO
IF EXISTS
  (SELECT TOP 1 1
     FROM SYS.[objects] SO
    WHERE SO.[name] = 'KAAS_FN_GetDefaultRate'
      AND SO.[type] = 'FN')
  BEGIN
    DROP FUNCTION [dbo].[KAAS_FN_GetDefaultRate]
  END
GO

CREATE FUNCTION
       [dbo].[KAAS_FN_GetDefaultRate]
      (@MATTER  VARCHAR(20),
       @FEEEARN VARCHAR(10),
       @NCCCODE VARCHAR(10),
       @TASK    VARCHAR(6))
RETURNS DECIMAL(18, 2)
AS
/*******************************************************************************************************
* Calculates the default Rate for a FeeEarner/Matter												   *
*                                                                                                      *
* Stored Procedure Name	: [dbo].[KAAS_FN_GetDefaultRate]											   *
* Copied from			: [dbo].[ky_NETFNGetDefaultRate]											   *
*                                                                                                      *
* Modification History	:                                                                              *
* 2019-04-24	Vinodhan K		Created                                                                *
 *******************************************************************************************************/
BEGIN
  DECLARE @CLIENT VARCHAR(10)
  DECLARE @MatterChargeLevel INT

  DECLARE @MCRRATE DECIMAL(18, 2)
  DECLARE @MCRRATE2 DECIMAL(18, 2)
  DECLARE @CCRRATE DECIMAL(18, 2)
  DECLARE @CCRRATE2 DECIMAL(18, 2)
  DECLARE @HDLRATE DECIMAL(10, 2)
  DECLARE @TASKCODE VARCHAR(10)
  DECLARE @CHARGEFACTOR DECIMAL(4, 2)
  DECLARE @ACTUALRATE DECIMAL(18, 2)

  SELECT @CLIENT = RTRIM(ISNULL(MAT.[ClientCode], '')),
         @MatterChargeLevel = ISNULL(MAT.[ChargeLevel], 3),
         @TASKCODE = CASE RTrim(IsNull(@MATTER, ''))
                          WHEN '' THEN RTRIM(ISNULL(@NCCCODE, ''))
                          ELSE RTRIM(ISNULL(@TASK, '')) END
    FROM [dbo].[matters] MAT
   WHERE MAT.[Code] = @MATTER

  SELECT @MCRRATE = MCOR.[Rate]
    FROM [dbo].[MatterChargeOutRates] MCOR
   WHERE MCOR.[FeCode] = @FEEEARN
     AND MCOR.[MatterCode] = @MATTER

  SELECT @MCRRATE2 = MCORG.[Rate]
    FROM [dbo].[Handlers] HANG
         INNER JOIN [dbo].[MatterChargeOutRates] MCORG
                 ON MCORG.[FeCode] = HANG.[ChargeGroup]
                AND MCORG.[MatterCode] = @MATTER
   WHERE HANG.[CODE] = @FEEEARN

  SET @MCRRATE2 = ISNULL(@MCRRATE2, 0)
  SET @MCRRATE = ISNULL(@MCRRATE, @MCRRATE2)

  SELECT @CCRRATE = CCOR.[Rate]
    FROM [dbo].[ClientChargeOutRates] CCOR
   WHERE CCOR.[FeCode] = @FEEEARN
     AND CCOR.[ClientCode] = @CLIENT

  SELECT @CCRRATE2 = CCORG.[Rate]
    FROM [dbo].[Handlers] HANG2
         INNER JOIN [dbo].[ClientChargeOutRates] CCORG
                 ON CCORG.[FeCode] = HANG2.[ChargeGroup]
                AND CCORG.[ClientCode] = @CLIENT
   WHERE HANG2.[CODE] = @FEEEARN

  SET @CCRRATE2 = ISNULL(@CCRRATE2, 0)
  SET @CCRRATE = ISNULL(@CCRRATE, @CCRRATE2)

  SELECT @HDLRATE = CASE IsNull(@MatterChargeLevel, 3)
                         WHEN 1 THEN IsNull(HDL.[RATE1], 0)
                         WHEN 2 THEN IsNull(HDL.[RATE2], 0)
                         WHEN 3 THEN IsNull(HDL.[RATE3], 0)
                         WHEN 4 THEN IsNull(HDL.[RATE4], 0)
                         WHEN 5 THEN IsNull(HDL.[RATE5], 0)
                         ELSE IsNull(HDL.[RATE], 0) END
    FROM [dbo].[Handlers] HDL
   WHERE HDL.[CODE] = @FEEEARN

  SET @HDLRATE = ISNULL(@HDLRATE, 0)

  SELECT @CHARGEFACTOR = CASE IsNull(TAC.[ChargeLevel], 'C')
                              WHEN 'A' THEN 0.33
                              WHEN 'B' THEN 0.67
                              WHEN 'C' THEN 1.00
                              WHEN 'D' THEN 1.33
                              WHEN 'E' THEN 1.67
                              ELSE 1.00 END
    FROM [dbo].[TasksCodes] TAC
   WHERE TAC.[CODE] = @TASKCODE

  SET @CHARGEFACTOR = ISNULL(@CHARGEFACTOR, 1)

  SELECT @ACTUALRATE = Case When IsNull(MAT.[ChargeOutRateInd], 'N') = 'Y'
                                 THEN @MCRRATE
                            When IsNull(CON.[ChargeOutRateInd], 'N') = 'Y'
                                 THEN @CCRRATE
                            ELSE @CHARGEFACTOR * @HDLRATE END
    FROM [dbo].[matters] MAT
         INNER JOIN [dbo].[contacts] CON
                 ON CON.[Code] = MAT.[ClientCode]
   WHERE MAT.[code] = @MATTER

  SET @ACTUALRATE = ISNULL(@ACTUALRATE, @CHARGEFACTOR * @HDLRATE)

  RETURN @ACTUALRATE
END
GO
IF OBJECT_ID(N'KAAS_FN_GetDocFolders',N'TF')IS NOT NULL
	DROP FUNCTION KAAS_FN_GetDocFolders
GO
CREATE FUNCTION [dbo].[KAAS_FN_GetDocFolders]
       (@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
/*************************************************************************************************************
 *                                                                                                           *
 * Stored Procedure Name: [dbo].[KAAS_FN_GetDocFolders]                                                      *
 * Copied from   : [dbo].[ky_NETTFGetDocFolders4]                                                            *
 *                                                                                                           *
 *                                                                                                           *
 * 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,                           *
 *************************************************************************************************************/
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(*) FROM DiaryAttachments where CASECODE = @MatterCode)
		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 EXISTS
  (SELECT TOP 1 1
     FROM SYS.[objects] SO
    WHERE SO.[name] = N'KAAS_FN_GetDocumentName'
      AND SO.[type] = 'FN')
  BEGIN
    DROP FUNCTION [dbo].[KAAS_FN_GetDocumentName]
  END
GO

CREATE FUNCTION
       [dbo].[KAAS_FN_GetDocumentName]
      (@path    VARCHAR(255))
RETURNS VARCHAR(255)
AS

/*******************************************************************************************************
* This function is used to Extracts the name of a document out of its path.							   *
*                                                                                                      *
* Function Name			: [dbo].[[KAAS_FN_GetDocumentName]]											   *
* Copied from			: [dbo].[[ky_NETFNGetDocumentName]]											   *
*                                                                                                      *
* Modification History	:                                                                              *
* 2019-11-01  Dheepin Vijith    Created																   *
*******************************************************************************************************/  
BEGIN
  DECLARE @htap   VARCHAR(255)
  DECLARE @period INT
  DECLARE @slash  INT

  SET @htap = REVERSE(RTRIM(ISNULL(@path, '')))

  SET @period = CHARINDEX('.', @htap)
  SET @slash = CHARINDEX('\', @htap)

  IF @period > 0
    BEGIN
      IF (@slash > @period) OR (@slash = 0)
        BEGIN
          SET @htap = SUBSTRING(@htap, @period + 1, LEN(@htap) - @period)
        END
    END

  SET @slash = CHARINDEX('\', @htap)

  IF @slash > 0
    BEGIN
      SET @htap = SUBSTRING(@htap, 1, @slash - 1)
    END

  RETURN REVERSE(@htap)
END

GO

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


CREATE FUNCTION
       [dbo].[KAAS_FN_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_FN_GETFILEPATH]												   *
* Copied from			: [dbo].[ky_NETFNGETFILEPATH]												   *
*                                                                                                      *
* Modification History	:                                                                              *
* 2019-04-12	Vinodhan K		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 EXISTS
  (SELECT *
     FROM SYS.[objects] SO
    WHERE SO.[name] = 'KAAS_FN_GetOldPermissions'
      AND SO.[type] = 'FN')
BEGIN
  DROP FUNCTION [dbo].[KAAS_FN_GetOldPermissions]
END
GO

 CREATE FUNCTION
        [dbo].[KAAS_FN_GetOldPermissions]
       (@HANDLER VARCHAR(10))
RETURNS BIGINT
AS

/*******************************************************************************************************
*																								       * 
*                                                                                                      *
* Stored Procedure Name : [dbo].[KAAS_FN_GetOldPermissions]											   *
* Copied from			: [dbo].[ky_NETFNGetOldPermissions]			                                   *
*                                                                                                      *
* Modification History:                                                                                *
* 2019-04-26    Vinodhan K        Created                                                              *
 *******************************************************************************************************/

BEGIN

DECLARE @PERMISSIONS BIGINT

DECLARE @LOGONS TABLE
       ([CODE]        VARCHAR(10),
        [LOGON]       VARCHAR(10),
        [NAME]        varchar(50),
        [OPE_NUMBER]  int)

DECLARE @LOGONS2 TABLE
       ([CODE]        VARCHAR(10),
        [LOGON]       VARCHAR(10),
        [NAME]        varchar(50),
        [OPE_NUMBER]  int)

  /* To aid uniformity, all queries have the same form. In this first one it should not be necessary to join to @LOGONs but
     doing so is harmless and it makes this query look the same as all the others.

     The Outer Apply links to DS1, DS2, DS3 and DS4 check that the DSSW21 entry has not already been matched to a handler.

     If this is so, it tries to match the DSSW21 record to a handler with an exact name match.

     */
  INSERT
    INTO @LOGONS2
        ([CODE],
         [LOGON],
         [NAME],
         [OPE_NUMBER])
  SELECT MTC.[CODE],
         MTC.[LOGON],
         MTC.[NAME],
         MTC.[MATCH]
    FROM (select HAN.[CODE],
                 HAN.[LOGON],
                 HAN.[NAME],
                 CASE WHEN DS1.[OPE_NUMBER] IS NOT NULL
                           THEN DS1.[OPE_NUMBER]
                      ELSE DS2.[OPE_NUMBER] END AS [Match]
            from [dbo].[Handlers] HAN
                 OUTER APPLY (SELECT TOP 1 DS.[OPE_NUMBER]
                                FROM [dbo].[DSSW21] DS
                               WHERE RTrim(HAN.[NAME]) LIKE RTrim(DS.[OPE_FIRSTNAME]) + '%' + RTRIM(DS.[OPE_SURNAME])) DS1
                 OUTER APPLY (SELECT TOP 1 DS.[OPE_NUMBER]
                                FROM [dbo].[DSSW21] DS
                               WHERE RTrim(HAN.[NAME]) LIKE RTrim(DS.[OPE_SURNAME]) + '%' + RTRIM(DS.[OPE_FIRSTNAME])) DS2
                 OUTER APPLY (SELECT TOP 1 DS.[OPE_NUMBER]
                                FROM [dbo].[DSSW21] DS
                               WHERE Soundex(RTrim(HAN.[NAME])) = SOUNDEX(RTrim(DS.[OPE_SURNAME]) + ' ' + RTRIM(DS.[OPE_FIRSTNAME]))) DS3
                 OUTER APPLY (SELECT TOP 1 DS.[OPE_NUMBER]
                                FROM [dbo].[DSSW21] DS
                               WHERE Soundex(RTrim(HAN.[NAME])) = SOUNDEX(RTrim(DS.[OPE_FIRSTNAME]) + ' ' + RTRIM(DS.[OPE_SURNAME]))) DS4
                 OUTER APPLY (SELECT TOP 1 DS.[OPE_NUMBER]
                                FROM [dbo].[DSSW21] DS
                                     LEFT OUTER JOIN [dbo].[Handlers] HN
                                                  ON HN.[NAME] LIKE RTrim(DS.[OPE_FIRSTNAME]) + '%' + RTRIM(DS.[OPE_SURNAME])
                                                  OR HN.[NAME] LIKE RTrim(DS.[OPE_SURNAME]) + '%' + RTRIM(DS.[OPE_FIRSTNAME])
                               WHERE DS1.[OPE_NUMBER] IS NULL
                                 AND DS2.[OPE_NUMBER] IS NULL
                                 AND DS3.[OPE_NUMBER] IS NULL
                                 AND DS4.[OPE_NUMBER] IS NULL
                                 AND HN.[CODE] IS NULL
                                 AND (   RTrim(HAN.[NAME]) LIKE '%' + RTrim(DS.[OPE_FIRSTNAME]) + '%'
                                      OR RTrim(HAN.[NAME]) LIKE '%' + RTrim(DS.[OPE_SURNAME]) + '%')) DS5
          WHERE HAN.[CODE] = @HANDLER
            AND RTRIM(ISNULL(HAN.[Logon], '')) <> '') MTC
         LEFT OUTER JOIN @LOGONS LO
                      ON LO.[OPE_NUMBER] = MTC.[Match]
   WHERE MTC.[Match] IS NOT NULL
     AND LO.[OPE_NUMBER] IS NULL

  -- This eliminates duplicate matches. If there are more than one handler matching a DSSW21 entry, we can't be sure which one it is,
  -- and we will ignore the match. We only include the ones that have exactly ONE match
  INSERT
    INTO @LOGONS
        ([CODE],
         [LOGON],
         [NAME],
         [OPE_NUMBER])
  SELECT LO.[CODE],
         LO.[LOGON],
         LO.[NAME],
         LO.[OPE_NUMBER]
    FROM (  SELECT OPE_NUMBER
              from @LOGONS2
          GROUP BY OPE_NUMBER
            HAVING COUNT(1) = 1) MH
         INNER JOIN @LOGONS2 LO
                 ON LO.[OPE_NUMBER] = MH.[OPE_NUMBER]

  DELETE @LOGONS2

  /* The Outer Apply links to DS1, DS2, DS3 and DS4 check that the DSSW21 entry has not already been matched to a handler.

     If this is so, it tries to match the DSSW21 record to a handler with an exact name match.

     */
  INSERT
    INTO @LOGONS2
        ([CODE],
         [LOGON],
         [NAME],
         [OPE_NUMBER])
  SELECT MTC.[CODE],
         MTC.[LOGON],
         MTC.[NAME],
         MTC.[MATCH]
    FROM (select HAN.[CODE],
                 HAN.[LOGON],
                 HAN.[NAME],
                 CASE WHEN DS1.[OPE_NUMBER] IS NOT NULL
                           THEN DS1.[OPE_NUMBER]
                      WHEN DS2.[OPE_NUMBER] IS NOT NULL
                           THEN DS2.[OPE_NUMBER]
                      WHEN DS3.[OPE_NUMBER] IS NOT NULL
                           THEN DS3.[OPE_NUMBER]
                      ELSE DS4.[OPE_NUMBER] END AS [Match]
            from [dbo].[Handlers] HAN
                 OUTER APPLY (SELECT TOP 1 DS.[OPE_NUMBER]
                                FROM [dbo].[DSSW21] DS
                               WHERE RTrim(HAN.[NAME]) LIKE RTrim(DS.[OPE_FIRSTNAME]) + '%' + RTRIM(DS.[OPE_SURNAME])) DS1
                 OUTER APPLY (SELECT TOP 1 DS.[OPE_NUMBER]
                                FROM [dbo].[DSSW21] DS
                               WHERE RTrim(HAN.[NAME]) LIKE RTrim(DS.[OPE_SURNAME]) + '%' + RTRIM(DS.[OPE_FIRSTNAME])) DS2
                 OUTER APPLY (SELECT TOP 1 DS.[OPE_NUMBER]
                                FROM [dbo].[DSSW21] DS
                               WHERE Soundex(RTrim(HAN.[NAME])) = SOUNDEX(RTrim(DS.[OPE_SURNAME]) + ' ' + RTRIM(DS.[OPE_FIRSTNAME]))) DS3
                 OUTER APPLY (SELECT TOP 1 DS.[OPE_NUMBER]
                                FROM [dbo].[DSSW21] DS
                               WHERE Soundex(RTrim(HAN.[NAME])) = SOUNDEX(RTrim(DS.[OPE_FIRSTNAME]) + ' ' + RTRIM(DS.[OPE_SURNAME]))) DS4
                 OUTER APPLY (SELECT TOP 1 DS.[OPE_NUMBER]
                                FROM [dbo].[DSSW21] DS
                                     LEFT OUTER JOIN [dbo].[Handlers] HN
                                                  ON HN.[NAME] LIKE RTrim(DS.[OPE_FIRSTNAME]) + '%' + RTRIM(DS.[OPE_SURNAME])
                                                  OR HN.[NAME] LIKE RTrim(DS.[OPE_SURNAME]) + '%' + RTRIM(DS.[OPE_FIRSTNAME])
                               WHERE DS1.[OPE_NUMBER] IS NULL
                                 AND DS2.[OPE_NUMBER] IS NULL
                                 AND DS3.[OPE_NUMBER] IS NULL
                                 AND DS4.[OPE_NUMBER] IS NULL
                                 AND HN.[CODE] IS NULL
                                 AND (   RTrim(HAN.[NAME]) LIKE '%' + RTrim(DS.[OPE_FIRSTNAME]) + '%'
                                      OR RTrim(HAN.[NAME]) LIKE '%' + RTrim(DS.[OPE_SURNAME]) + '%')) DS5
          WHERE HAN.[CODE] = @HANDLER
            AND RTRIM(ISNULL(HAN.[Logon], '')) <> '') MTC
         LEFT OUTER JOIN @LOGONS LO
                      ON LO.[OPE_NUMBER] = MTC.[Match]
   WHERE MTC.[Match] IS NOT NULL
     AND LO.[OPE_NUMBER] IS NULL

  INSERT
    INTO @LOGONS
        ([CODE],
         [LOGON],
         [NAME],
         [OPE_NUMBER])
  SELECT LO.[CODE],
         LO.[LOGON],
         LO.[NAME],
         LO.[OPE_NUMBER]
    FROM (  SELECT OPE_NUMBER
              from @LOGONS2
          GROUP BY OPE_NUMBER
            HAVING COUNT(1) = 1) MH
         INNER JOIN @LOGONS2 LO
                 ON LO.[OPE_NUMBER] = MH.[OPE_NUMBER]

  DELETE @LOGONS2

  INSERT
    INTO @LOGONS2
        ([CODE],
         [LOGON],
         [NAME],
         [OPE_NUMBER])
  SELECT MTC.[CODE],
         MTC.[LOGON],
         MTC.[NAME],
         MTC.[MATCH]
    FROM (select HAN.[CODE],
                 HAN.[LOGON],
                 HAN.[NAME],
                 CASE WHEN DS1.[OPE_NUMBER] IS NOT NULL
                           THEN DS1.[OPE_NUMBER]
                      WHEN DS2.[OPE_NUMBER] IS NOT NULL
                           THEN DS2.[OPE_NUMBER]
                      WHEN DS3.[OPE_NUMBER] IS NOT NULL
                           THEN DS3.[OPE_NUMBER]
                      WHEN DS4.[OPE_NUMBER] IS NOT NULL
                           THEN DS4.[OPE_NUMBER]
                      ELSE DS5.[OPE_NUMBER] END AS [Match]
            from [dbo].[Handlers] HAN
                 OUTER APPLY (SELECT TOP 1 DS.[OPE_NUMBER]
                                FROM [dbo].[DSSW21] DS
                               WHERE RTrim(HAN.[NAME]) LIKE RTrim(DS.[OPE_FIRSTNAME]) + '%' + RTRIM(DS.[OPE_SURNAME])) DS1
                 OUTER APPLY (SELECT TOP 1 DS.[OPE_NUMBER]
                                FROM [dbo].[DSSW21] DS
                               WHERE RTrim(HAN.[NAME]) LIKE RTrim(DS.[OPE_SURNAME]) + '%' + RTRIM(DS.[OPE_FIRSTNAME])) DS2
                 OUTER APPLY (SELECT TOP 1 DS.[OPE_NUMBER]
                                FROM [dbo].[DSSW21] DS
                               WHERE Soundex(RTrim(HAN.[NAME])) = SOUNDEX(RTrim(DS.[OPE_SURNAME]) + ' ' + RTRIM(DS.[OPE_FIRSTNAME]))) DS3
                 OUTER APPLY (SELECT TOP 1 DS.[OPE_NUMBER]
                                FROM [dbo].[DSSW21] DS
                               WHERE Soundex(RTrim(HAN.[NAME])) = SOUNDEX(RTrim(DS.[OPE_FIRSTNAME]) + ' ' + RTRIM(DS.[OPE_SURNAME]))) DS4
                 OUTER APPLY (SELECT TOP 1 DS.[OPE_NUMBER]
                                FROM [dbo].[DSSW21] DS
                                     LEFT OUTER JOIN [dbo].[Handlers] HN
                                                  ON HN.[NAME] LIKE RTrim(DS.[OPE_FIRSTNAME]) + '%' + RTRIM(DS.[OPE_SURNAME])
                                                  OR HN.[NAME] LIKE RTrim(DS.[OPE_SURNAME]) + '%' + RTRIM(DS.[OPE_FIRSTNAME])
                               WHERE DS1.[OPE_NUMBER] IS NULL
                                 AND DS2.[OPE_NUMBER] IS NULL
                                 AND DS3.[OPE_NUMBER] IS NULL
                                 AND DS4.[OPE_NUMBER] IS NULL
                                 AND HN.[CODE] IS NULL
                                 AND (   RTrim(HAN.[NAME]) LIKE '%' + RTrim(DS.[OPE_FIRSTNAME]) + '%'
                                      OR RTrim(HAN.[NAME]) LIKE '%' + RTrim(DS.[OPE_SURNAME]) + '%')) DS5
          WHERE HAN.[CODE] = @HANDLER
            AND RTRIM(ISNULL(HAN.[Logon], '')) <> '') MTC
         LEFT OUTER JOIN @LOGONS LO
                      ON LO.[OPE_NUMBER] = MTC.[Match]
   WHERE MTC.[Match] IS NOT NULL
     AND LO.[OPE_NUMBER] IS NULL

  INSERT
    INTO @LOGONS
        ([CODE],
         [LOGON],
         [NAME],
         [OPE_NUMBER])
  SELECT LO.[CODE],
         LO.[LOGON],
         LO.[NAME],
         LO.[OPE_NUMBER]
    FROM (  SELECT OPE_NUMBER
              from @LOGONS2
          GROUP BY OPE_NUMBER
            HAVING COUNT(1) = 1) MH
         INNER JOIN @LOGONS2 LO
                 ON LO.[OPE_NUMBER] = MH.[OPE_NUMBER]

  DELETE @LOGONS2

  SELECT @PERMISSIONS = CONVERT(BIGINT, ISNULL(JP.[JPER_Permissions], 0))
    from [dbo].[Handlers] HAN
         LEFT OUTER JOIN @LOGONS LO
                         INNER JOIN [dbo].[DSSW21] DS
                                 ON DS.[OPE_NUMBER] = LO.[OPE_NUMBER]
                      ON LO.[CODE] = HAN.[CODE]
         LEFT OUTER JOIN JPermissions JP
                      ON JP.[JPER_UserNumber] = LO.[OPE_NUMBER]
   WHERE LO.[OPE_NUMBER] IS NOT NULL
  ORDER BY LO.[OPE_NUMBER]

  SET @PERMISSIONS = ISNULL(@PERMISSIONS, 0)

  RETURN @PERMISSIONS
END
GO
IF EXISTS
  (SELECT TOP 1 1
     FROM SYS.[objects] SO
    WHERE SO.[name] = N'KAAS_FN_ISIMAGE_TYPE'
      AND SO.[type] = 'FN')
  BEGIN
    DROP FUNCTION [dbo].[KAAS_FN_ISIMAGE_TYPE]
  END
GO

CREATE FUNCTION [dbo].[KAAS_FN_ISIMAGE_TYPE]
(
	@TYPE        NVARCHAR(10)
)
RETURNS BIT
AS
/*******************************************************************************************************
* This function is used to check the type, as it is in image format or not.							   *
*                                                                                                      *
* Stored Procedure Name	: [dbo].[KAAS_FN_ISIMAGE_TYPE]												   *
*                                                                                                      *
* Modification History	:                                                                              *
* 2019-07-15	Ramesh K		Created                                                                *
 *******************************************************************************************************/
BEGIN

	DECLARE @RetVal BIT = 0;

	IF @TYPE IS NULL
	BEGIN
		RETURN @RETvAL;
	END

	SET @TYPE = LOWER(@TYPE);

	SELECT @RetVal = CASE @TYPE
							WHEN '.png' THEN 1
							WHEN 'png' THEN 1
							WHEN 'image/png' THEN 1

							WHEN '.jpg' THEN 1
							WHEN 'jpg' THEN 1
							WHEN 'image/jpg' THEN 1

							WHEN '.jpeg' THEN 1
							WHEN 'jpeg' THEN 1
							WHEN 'image/jpeg' THEN 1

							WHEN '.tif' THEN 1
							WHEN 'tif' THEN 1
							WHEN 'image/tif' THEN 1

							WHEN '.tiff' THEN 1
							WHEN 'tiff' THEN 1
							WHEN 'image/tiff' THEN 1

							WHEN '.gif' THEN 1
							WHEN 'gif' THEN 1
							WHEN 'image/gif' THEN 1

							ELSE 0
						END

	RETURN @RetVal;

    
END
GO
IF EXISTS
  (SELECT *
     FROM SYS.[objects] SO
    WHERE SO.[name] = 'KAAS_FN_MatterClosable'
      AND SO.[type] = 'TF')
BEGIN
  DROP FUNCTION [dbo].[KAAS_FN_MatterClosable]  
END
GO

/*******************************************************************************************************
* Get the parameters to establish whether a matter can be closed at this point.						   *
* Please note that Bal, Rec, Diary and Undertakings should prevent a matter from closing, while		   *
* Time and Charge should lead to a warning and the user can decide to close the matter anyway.         *
*                                                                                                      *
* Stored Procedure Name	: [dbo].[KAAS_FN_MatterClosable]						   					   *
* Copied from			: [dbo].[ky_NETFNMatterClosable]											   *
*                                                                                                      *
* Modification History	:                                                                              *
* 2019-04-24	Vinodhan K		Created                                                                *
*******************************************************************************************************/
GO

IF [dbo].[ISSAM4]() = 1
  BEGIN
    EXEC ('
CREATE FUNCTION
       [dbo].[KAAS_FN_MatterClosable]
      (@MATTER VARCHAR(20))
RETURNS @Closables TABLE
       ([AlreadyClosed]                          int,
        [HasOutstandingBal]                      int,
        [HasOutstandingTimeBal]                  int,
        [HasOutstandingChargeBal]                int,
        [HasOutstandingRec]                      int,
        [HasOutstandingDiary]                    int,
        [HasOutstandingUndertakings]             int)
AS

BEGIN

DECLARE @AlreadyClosed INT
DECLARE @HasOutstandingRec INT
DECLARE @HasOutstandingBal INT
DECLARE @HasOutstandingTimeBal INT
DECLARE @HasOutstandingChargeBal INT
DECLARE @HasOutstandingDiary INT
DECLARE @HasOutstandingUndertakings INT
DECLARE @Year INT

DECLARE @IsSAM4 bit

SET @IsSAM4 = 1

-- Please see also: ky_NETCaseSummaryPageLoad. If you change any of these calculations,
--                                             ensure the ones in there are changed as well.
--                  ky_NETTLFetchTimeEntry. If you change any of these calculations,
--                                          ensure the ones in there are changed as well.
DECLARE @TimeBal      INT
DECLARE @ChargeBal    DECIMAL(19, 2)
DECLARE @TotalCharge  Decimal(19, 2)
DECLARE @TotalTime    INT
DECLARE @TimeEntryTotalCharge         DECIMAL(19, 2)
DECLARE @TimeEntryTotalTime           INT

SELECT @TimeEntryTotalCharge = ISNULL(SUM(ISNULL(TIE.[Charge], 0)), 0),
       @TimeEntryTotalTime = ISNULL(SUM(ISNULL(TIE.[TIME], 0)), 0),
       @TotalCharge = ISNULL(SUM(CASE WHEN ISNULL(TIE.[Rec_Irr], '''') = ''N''
                                      THEN 0
                                 ELSE ISNULL(TIE.[Charge], 0) END), 0),
       @ChargeBal = ISNULL(SUM(CASE WHEN ISNULL(TIE.[Rec_Irr], '''') = ''N''
                                         THEN 0
                                    WHEN @IsSAM4 = 1
                                     AND ISNULL(TIE.[InvoiceNo], 0) <> 0
                                         THEN 0
                                    WHEN @IsSAM4 = 1
                                         THEN ISNULL(TIE.[Charge], 0)
                                    ELSE ISNULL(TIE.[Charge], 0) - ISNULL(TIE.[BilledAmount], 0) END), 0),
       @TimeBal = ISNULL(SUM(CASE WHEN ISNULL(TIE.[Rec_Irr], '''') = ''N''
                                       THEN 0
                                  WHEN @IsSAM4 = 1
                                   AND ISNULL(TIE.[InvoiceNo], 0) <> 0
                                       THEN 0
                                  WHEN @IsSAM4 = 0
                                       THEN CASE WHEN TIE.[Charge] = 0
                                                      THEN 0
                                                 ELSE CONVERT(INT,
                                                              FLOOR(CONVERT(DECIMAL(18, 2), TIE.[TIME])
                                                                    * (  (TIE.[Charge] - TIE.[BilledAmount])
                                                                       /
                                                                       TIE.[Charge]))) END
                                  ELSE ISNULL(TIE.[Time], 0) END), 0),
       @TotalTime = ISNULL(SUM(CASE WHEN ISNULL(TIE.[Rec_Irr], '''') = ''N''
                                         THEN 0
                                    ELSE ISNULL(TIE.[Time], 0) END), 0)
  FROM [dbo].[TimeEntry] TIE
 WHERE TIE.[Matter] = @MATTER
   AND (   (TIE.[TimeOrCharge] = ''T'')
        OR (TIE.[TimeOrCharge] = ''C''))  --**Changed fetch condition JIRA 1081 - arun

DECLARE @DebtBal        DECIMAL(19, 2)
DECLARE @OutlayBal      DECIMAL(19, 2)
DECLARE @DebtBalDate    DATETIME
DECLARE @OutlayBalDate  DATETIME
DECLARE @ClientCurrent  DECIMAL(19, 2)
DECLARE @ClientDeposit  DECIMAL(19, 2)

DECLARE @NCOMMAND NVARCHAR(MAX)
-- To ensure SAM3 will retrieve these from [dbo].[matters]
SET @DebtBal = 0 
SET @OutlayBal = 0
SET @ClientCurrent = 0
SET @ClientDeposit = 0

SELECT @DebtBal = CONVERT(DECIMAL(19, 2), ISNULL(SUM(ISNULL(TRN.[DebtorsValue], 0)), 0)),
       @DebtBalDate = MAX(CASE WHEN ISNULL(TRN.[DebtorsValue], 0) = 0 THEN NULL ELSE TRN.[TransDate] END),
       @OutlayBal = CONVERT(DECIMAL(19, 2), ISNULL(SUM(ISNULL(TRN.[OutlayValue], 0)), 0)),
       @OutlayBalDate = MAX(CASE WHEN ISNULL(TRN.[OutlayValue], 0) = 0 THEN NULL ELSE TRN.[TransDate] END),
       @ClientCurrent = CONVERT(DECIMAL(19, 2), ISNULL(SUM(ISNULL(TRN.[ClientCurrent], 0)), 0)),
       @ClientDeposit = CONVERT(DECIMAL(19, 2), ISNULL(SUM(ISNULL(TRN.[ClientDeposit], 0)), 0))
  FROM [dbo].[Transactions] TRN 
 WHERE TRN.[MatterCode] = @MATTER
   AND TRN.[Posted] = ''Y''
   AND TRN.[RecType] <> ''V''
   AND TRN.[CorrectionInd] = ''L''

SELECT @AlreadyClosed = CASE WHEN MAT.[Closed] = ''Y'' THEN 1 ELSE 0 END,
       @HasOutstandingBal = CASE WHEN @DebtBal <> 0
                                      THEN 1
                                 WHEN ISNULL(MAT.[DebtBal], 0) <> 0
                                      THEN 1
                                 WHEN @OutlayBal <> 0
                                      THEN 1
                                 WHEN ISNULL(MAT.[OutlayBal], 0) <> 0
                                      THEN 1
                                 WHEN ISNULL(MAT.[ClientBal], 0) <> 0 --No need to check @ClientCurBal + @ClientDepBal
                                      THEN 1
                                 WHEN @ClientCurrent <> 0
                                      THEN 1
                                 WHEN ISNULL(MAT.[ClientCurBal], 0) <> 0
                                      THEN 1
                                 WHEN @ClientDeposit <> 0
                                      THEN 1
                                 WHEN ISNULL(MAT.[ClientDepBal], 0) <> 0
                                      THEN 1
                                 ELSE 0 END,
       @HasOutstandingTimeBal = CASE WHEN @TimeBal <> 0 THEN 1 ELSE 0 END,
       @HasOutstandingChargeBal = CASE WHEN @ChargeBal <> 0 THEN 1 ELSE 0 END
  FROM [dbo].[matters] MAT
 WHERE MAT.[Code] = @MATTER

SET @AlreadyClosed = ISNULL(@AlreadyClosed, 0)
SET @HasOutstandingBal = ISNULL(@HasOutstandingBal, 0)
SET @HasOutstandingTimeBal = ISNULL(@HasOutstandingTimeBal, 0)
SET @HasOutstandingChargeBal = ISNULL(@HasOutstandingChargeBal, 0)

SELECT @YEAR = ISNULL(CONVERT(INT, CTL.[NumValue]), DATEPART(YEAR, GetDate()))
  FROM [dbo].[CtrlNum] CTL
 WHERE CTL.[Code] = ''CurYear''

IF (@YEAR IS NULL)
  BEGIN
    SET @YEAR = DATEPART(YEAR, GetDate())
  END

SELECT @HasOutstandingRec = CASE COUNT(1) WHEN 0 THEN 0 ELSE 1 END
  FROM [dbo].[Transactions] TRN
 WHERE TRN.[MatterCode] = @MATTER
   AND TRN.[Posted] = ''Y''
   AND TRN.[RecType] <> ''V''
   AND (   TRN.[CorrectionInd] = ''L''
        OR TRN.[CorrectionInd] = ''C'')
   AND TRN.[PostingYear] = @YEAR
   AND TRN.[Reconciliation] <= 0
    
SET @HasOutstandingRec = ISNULL(@HasOutstandingRec, 0)

SELECT @HasOutstandingDiary = CASE WHEN COUNT(1) = 0 THEN 0 ELSE 1 END
  FROM [dbo].[diary] DIA
 WHERE DIA.[CASECODE] = @MATTER
   AND DIA.[STATUS] <> 1

SELECT @HasOutstandingUndertakings = CASE WHEN COUNT(1) = 0 THEN 0 ELSE 1 END
  FROM [dbo].[Undertakings] UND
 WHERE UND.[MATTER] = @MATTER
   AND UND.[DISCHARGEDATE] IS NULL

INSERT
  INTO @Closables
      ([AlreadyClosed],
       [HasOutstandingBal],
       [HasOutstandingTimeBal],
       [HasOutstandingChargeBal],
       [HasOutstandingRec],
       [HasOutstandingDiary],
       [HasOutstandingUndertakings])
SELECT @AlreadyClosed                       AS [AlreadyClosed],
       @HasOutstandingBal                   AS [HasOutstandingBal],
       @HasOutstandingTimeBal               AS [HasOutstandingTimeBal],
       @HasOutstandingChargeBal             AS [HasOutstandingChargeBal],
       @HasOutstandingRec                   AS [HasOutstandingRec],
       @HasOutstandingDiary                 AS [HasOutstandingDiary],
       @HasOutstandingUndertakings          AS [HasOutstandingUndertakings]

RETURN
END')
  END
ELSE
  BEGIN
    EXEC ('
CREATE FUNCTION
       [dbo].[KAAS_FN_MatterClosable]
      (@MATTER VARCHAR(20))
RETURNS @Closables TABLE
       ([AlreadyClosed]                          int,
        [HasOutstandingBal]                      int,
        [HasOutstandingTimeBal]                  int,
        [HasOutstandingChargeBal]                int,
        [HasOutstandingRec]                      int,
        [HasOutstandingDiary]                    int,
        [HasOutstandingUndertakings]             int)
AS
/***********************************************************************************************
 *
 * KAAS_FN_MatterClosable get the parameters to establish whether a matter can be
 *                        closed at this point. Please note that Bal, Rec, Diary and
 *                        Undertakings should prevent a matter from closing, while
 *                        Time and Charge should lead to a warning and the user can
 *                        decide to close the matter anyway.
 *
 * Modifications: 2015-02-18  Pino  Change calculations to accomodate SAM4, using
 *                                  original calculations from ky_NETSPCaseSummaryPageLoad
 *                                  Added warning that if any of these calculations are to be
 *                                  changed here, then the corresponding calculations in other
 *                                  procedure(s) must be changed accordingly.
 *                2015-03-04  Pino  No functional change but added reference to 
 *                                  ky_NETTLFetchTimeEntry in comment below.
 *
 *                2015-10-29  Pino Carafa
 *                                  Include calculation for @HasOutstandingRec
 *
 *
 ***********************************************************************************************/
BEGIN

DECLARE @AlreadyClosed INT
DECLARE @HasOutstandingRec INT
DECLARE @HasOutstandingBal INT
DECLARE @HasOutstandingTimeBal INT
DECLARE @HasOutstandingChargeBal INT
DECLARE @HasOutstandingDiary INT
DECLARE @HasOutstandingUndertakings INT
DECLARE @Year INT

DECLARE @IsSAM4 bit

SET @IsSAM4 = 0

-- Please see also: ky_NETCaseSummaryPageLoad. If you change any of these calculations,
--                                             ensure the ones in there are changed as well.
--                  ky_NETTLFetchTimeEntry. If you change any of these calculations,
--                                          ensure the ones in there are changed as well.
DECLARE @TimeBal      INT
DECLARE @ChargeBal    DECIMAL(19, 2)
DECLARE @TotalCharge  Decimal(19, 2)
DECLARE @TotalTime    INT
DECLARE @TimeEntryTotalCharge         DECIMAL(19, 2)
DECLARE @TimeEntryTotalTime           INT

SELECT @TimeEntryTotalCharge = ISNULL(SUM(ISNULL(TIE.[Charge], 0)), 0),
       @TimeEntryTotalTime = ISNULL(SUM(ISNULL(TIE.[TIME], 0)), 0),
       @TotalCharge = ISNULL(SUM(CASE WHEN ISNULL(TIE.[Rec_Irr], '''') = ''N''
                                      THEN 0
                                 ELSE ISNULL(TIE.[Charge], 0) END), 0),
       @ChargeBal = ISNULL(SUM(CASE WHEN ISNULL(TIE.[Rec_Irr], '''') = ''N''
                                         THEN 0
                                    WHEN @IsSAM4 = 1
                                     AND ISNULL(TIE.[InvoiceNo], 0) <> 0
                                         THEN 0
                                    WHEN @IsSAM4 = 1
                                         THEN ISNULL(TIE.[Charge], 0)
                                    ELSE ISNULL(TIE.[Charge], 0) - ISNULL(TIE.[BilledAmount], 0) END), 0),
       @TimeBal = ISNULL(SUM(CASE WHEN ISNULL(TIE.[Rec_Irr], '''') = ''N''
                                       THEN 0
                                  WHEN @IsSAM4 = 1
                                   AND ISNULL(TIE.[InvoiceNo], 0) <> 0
                                       THEN 0
                                  WHEN @IsSAM4 = 0
                                       THEN CASE WHEN TIE.[Charge] = 0
                                                      THEN 0
                                                 ELSE CONVERT(INT,
                                                              FLOOR(CONVERT(DECIMAL(18, 2), TIE.[TIME])
                                                                    * (  (TIE.[Charge] - TIE.[BilledAmount])
                                                                       /
                                                                       TIE.[Charge]))) END
                                  ELSE ISNULL(TIE.[Time], 0) END), 0),
       @TotalTime = ISNULL(SUM(CASE WHEN ISNULL(TIE.[Rec_Irr], '''') = ''N''
                                         THEN 0
                                    ELSE ISNULL(TIE.[Time], 0) END), 0)
  FROM [dbo].[TimeEntry] TIE
 WHERE TIE.[Matter] = @MATTER
   AND (   (TIE.[TimeOrCharge] = ''T'')
        OR (TIE.[TimeOrCharge] = ''C''))  --**Changed fetch condition JIRA 1081 - arun

DECLARE @DebtBal        DECIMAL(19, 2)
DECLARE @OutlayBal      DECIMAL(19, 2)
DECLARE @DebtBalDate    DATETIME
DECLARE @OutlayBalDate  DATETIME
DECLARE @ClientCurrent  DECIMAL(19, 2)
DECLARE @ClientDeposit  DECIMAL(19, 2)

DECLARE @NCOMMAND NVARCHAR(MAX)
-- To ensure SAM3 will retrieve these from [dbo].[matters]
SET @DebtBal = 0 
SET @OutlayBal = 0
SET @ClientCurrent = 0
SET @ClientDeposit = 0

SELECT @AlreadyClosed = CASE WHEN MAT.[Closed] = ''Y'' THEN 1 ELSE 0 END,
       @HasOutstandingBal = CASE WHEN @DebtBal <> 0
                                      THEN 1
                                 WHEN ISNULL(MAT.[DebtBal], 0) <> 0
                                      THEN 1
                                 WHEN @OutlayBal <> 0
                                      THEN 1
                                 WHEN ISNULL(MAT.[OutlayBal], 0) <> 0
                                      THEN 1
                                 WHEN ISNULL(MAT.[ClientBal], 0) <> 0 --No need to check @ClientCurBal + @ClientDepBal
                                      THEN 1
                                 WHEN @ClientCurrent <> 0
                                      THEN 1
                                 WHEN ISNULL(MAT.[ClientCurBal], 0) <> 0
                                      THEN 1
                                 WHEN @ClientDeposit <> 0
                                      THEN 1
                                 WHEN ISNULL(MAT.[ClientDepBal], 0) <> 0
                                      THEN 1
                                 ELSE 0 END,
       @HasOutstandingTimeBal = CASE WHEN @TimeBal <> 0 THEN 1 ELSE 0 END,
       @HasOutstandingChargeBal = CASE WHEN @ChargeBal <> 0 THEN 1 ELSE 0 END
  FROM [dbo].[matters] MAT
 WHERE MAT.[Code] = @MATTER

SET @AlreadyClosed = ISNULL(@AlreadyClosed, 0)
SET @HasOutstandingBal = ISNULL(@HasOutstandingBal, 0)
SET @HasOutstandingTimeBal = ISNULL(@HasOutstandingTimeBal, 0)
SET @HasOutstandingChargeBal = ISNULL(@HasOutstandingChargeBal, 0)

SELECT @YEAR = ISNULL(CTL.[YEAR], DATEPART(YEAR, GetDate()))
  FROM [dbo].[Control] CTL

IF (@YEAR IS NULL)
  BEGIN
    SET @YEAR = DATEPART(YEAR, GetDate())
  END
  
SELECT @HasOutstandingRec = CASE COUNT(1) WHEN 0 THEN 0 ELSE 1 END
  FROM [dbo].[MatterLedger] MAL
       INNER JOIN [dbo].[BatchH] BH
                  INNER JOIN [dbo].[BankRecHistory] BHS
                          ON BHS.[BANKAC] = BH.[CODE]
               ON BH.[BATCHNO] = MAL.[BATCHNO]
              AND BH.[RECNO] <= 0
              AND BH.[TYPE] IN (''P'', ''R'', ''T'')
  WHERE MAL.[MATTER] = @MATTER
    AND MAL.[YEAR] = @YEAR
    AND MAL.[REF] <> ''EUROADJ''
    
SET @HasOutstandingRec = ISNULL(@HasOutstandingRec, 0)

SELECT @HasOutstandingDiary = CASE WHEN COUNT(1) = 0 THEN 0 ELSE 1 END
  FROM [dbo].[diary] DIA
 WHERE DIA.[CASECODE] = @MATTER
   AND DIA.[STATUS] <> 1

SELECT @HasOutstandingUndertakings = CASE WHEN COUNT(1) = 0 THEN 0 ELSE 1 END
  FROM [dbo].[Undertakings] UND
 WHERE UND.[MATTER] = @MATTER
   AND UND.[DISCHARGEDATE] IS NULL

INSERT
  INTO @Closables
      ([AlreadyClosed],
       [HasOutstandingBal],
       [HasOutstandingTimeBal],
       [HasOutstandingChargeBal],
       [HasOutstandingRec],
       [HasOutstandingDiary],
       [HasOutstandingUndertakings])
SELECT @AlreadyClosed                       AS [AlreadyClosed],
       @HasOutstandingBal                   AS [HasOutstandingBal],
       @HasOutstandingTimeBal               AS [HasOutstandingTimeBal],
       @HasOutstandingChargeBal             AS [HasOutstandingChargeBal],
       @HasOutstandingRec                   AS [HasOutstandingRec],
       @HasOutstandingDiary                 AS [HasOutstandingDiary],
       @HasOutstandingUndertakings          AS [HasOutstandingUndertakings]

RETURN
END')
  END

GO
IF EXISTS
  (SELECT TOP 1 1
     FROM SYS.[objects] SO
    WHERE SO.[name] = 'KAAS_FN_MatterFECode'
      AND SO.[type] = 'FN')
  BEGIN
    DROP FUNCTION [dbo].[KAAS_FN_MatterFECode]
  END
GO

CREATE FUNCTION
       [dbo].[KAAS_FN_MatterFECode]
      (@matter VARCHAR(20))
RETURNS VARCHAR(10)
AS

/*******************************************************************************************************
* Fetches the FE Code																				   *
*																									   *
*                                                                                                      *
* Stored Procedure Name	: [dbo].[KAAS_FN_MatterFECode]						                           *
* Copied from			: [dbo].[ky_NETFNMatterFECode]						                           *
*                                                                                                      *
* Modification History	:                                                                              *
* 2019-11-13	Dheepin Vijith K		Created                                                        *
 *******************************************************************************************************/

BEGIN
  DECLARE @FeCode VARCHAR(10)

  SELECT @FeCode = MAT.[FECode]
    FROM [dbo].[matters] MAT
   WHERE MAT.[Code] = @matter

  SET @FeCode = RTRIM(ISNULL(@FeCode, ''))

  RETURN @FeCode
END
GO

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

CREATE FUNCTION
       [dbo].[KAAS_FN_MatterHeaderDetails]
      (@matter VARCHAR(20))
RETURNS @MD TABLE
       ([CODE]              VARCHAR(20),
        [ClientCode]        VARCHAR(10),
        [MatterNo]          VARCHAR(10),
        [YourRef]           VARCHAR(100),
        [DESCRIPTION]       VARCHAR(300),
        [FECode]            VARCHAR(10),
        [FEName]            VARCHAR(100),
        [Name]              VARCHAR(256),
        [TelNo]             VARCHAR(50),
        [Address]           VARCHAR(2000),
        [Closed]            CHAR(1),
        [TOOLTIP]           VARCHAR(1000),
        [IsChildcare]       CHAR(1),
        [User1]             VARCHAR(100),
        [User2]             VARCHAR(100),
        [User3]             VARCHAR(100),
        [Partner]           VARCHAR(10),
        [PartnerName]       VARCHAR(100),
        [CompBillingMatter] VARCHAR(20),
        [MatterUniqueID]    INT)
AS
/*******************************************************************************************************
* Returns the details for the current matter, to be displayed in the                                   *
* Client/Case section of the software, ledgers, etc.                                                   *
*                                                                                                      *
* Stored Procedure Name	: [dbo].[KAAS_FN_MatterHeaderDetails]										   *
* Copied from			: [dbo].[ky_NETTFMatterHeaderDetails]										   *
*                                                                                                      *
* Modification History	:                                                                              *
* 2019-04-12	Vinodhan K		Created                                                                *
*******************************************************************************************************/ 
BEGIN

INSERT
  INTO @MD
      ([CODE],
       [ClientCode],
       [MatterNo],
       [YourRef],
       [DESCRIPTION],
       [FECode],
       [FEName],
       [Name],
       [TelNo],
       [Address],
       [Closed],
       [TOOLTIP],
       [IsChildcare],
       [User1],
       [User2],
       [User3],
       [Partner],
       [PartnerName],
       [CompBillingMatter],
       [MatterUniqueID])
SELECT RTRIM(ISNULL([MAT].[Code], ''))                             AS [Code],
       RTRIM(ISNULL([MAT].[ClientCode], ''))                       AS [ClientCode],
       RTRIM(ISNULL([MAT].[Matter], ''))                           AS [MatterNo],
       RTRIM(ISNULL([MAT].[YourRef], ''))                          AS [YourRef],
       RTRIM(ISNULL([MAT].[Description], ''))                      AS [Description],
       RTRIM(ISNULL([MAT].[FECode], ''))                           AS [FECode],
       RTRIM(ISNULL([HAN].[NAME], ''))                             AS [FEName],
       RTRIM(ISNULL([CT].[Name], ''))                              AS [Name],
       RTRIM(ISNULL([CT].[Tel], ''))                               AS [TelNo],
       RTRIM(ISNULL([CT].[Address], ''))                           AS [Address],
       CASE WHEN RTRIM(ISNULL(MAT.[Closed], 'N')) = 'Y'
                 THEN 'Y'
            ELSE 'N' END                                           AS [Closed],
         RTRIM(ISNULL([CL].[CLNAME], ''))
       + CHAR(13)
       + 'W:' + RTRIM(ISNULL([CL].[CLTEL], ' Nil '))
       + CHAR(13)
       + 'M:' + RTRIM(ISNULL([CL].[CLMOBNO], ' Nil '))             AS [TOOLTIP],
       CASE WHEN [WKT].[CODE] IS NULL
                 THEN 'N'
            WHEN [WKT].[ChildCare] IS NULL
                 THEN 'N'
            WHEN [WKT].[ChildCare] = 'Y'
                 THEN 'Y'
            ELSE 'N' END                                           AS [IsChildcare],
       ISNULL([MAT].[User1], '')                                   AS [User1],
       ISNULL([MAT].[User2], '')                                   AS [User2],
       ISNULL([MAT].[User3], '')                                   AS [User3],
       ISNULL([MAT].[Partner], '')                                 AS [Partner],
       ISNULL([PRT].[NAME], '')                                    AS [PartnerName],
       RTRIM(ISNULL([MAT].[CompBillingMatter], ''))                AS [CompBillingMatter],
       [MAT].[UniqueID]                                            AS [MatterUniqueID]
  FROM [dbo].[matters] [MAT]
       LEFT OUTER JOIN [dbo].[Contacts] [CT]
                       INNER JOIN [dbo].[CLIENT] [CL]
                               ON [CL].[CLCODE] =[CT].[Code]
                    ON [CT].[Code] = [MAT].[ClientCode]
       LEFT OUTER JOIN [dbo].[Handlers] [HAN]
                    ON [HAN].[CODE] = [MAT].[FECode]
       LEFT OUTER JOIN [dbo].[WorkTypes] [WKT]
                    ON [WKT].[CODE] = MAT.[WType]
       LEFT OUTER JOIN [dbo].[Handlers] AS [PRT]
                    ON [PRT].[CODE] = [MAT].[Partner]
 WHERE [MAT].[Code] = @matter

RETURN
END

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

CREATE FUNCTION
       [dbo].[KAAS_FN_RemovePatternFromString]
      (@BUFFER VARCHAR(MAX),
       @PATTERN VARCHAR(128))
RETURNS VARCHAR(MAX)
AS
/*******************************************************************************************************
* Removes unwanted characters from a string.														   *
*                                                                                                      *
* Stored Procedure Name	: [dbo].[KAAS_FN_RemovePatternFromString]									   *
* Copied from			: [dbo].[ky_NETFNRemovePatternFromString]									   *
*                                                                                                      *
* Modification History	:                                                                              *
* 2019-04-11	Vinodhan K		Created                                                                *
 *******************************************************************************************************/
BEGIN
    DECLARE @POS INT
    SET @POS = PATINDEX(@PATTERN, @BUFFER)

    WHILE @POS > 0 BEGIN
        SET @BUFFER = STUFF(@BUFFER, @POS, 1, '')
        SET @POS = PATINDEX(@PATTERN, @BUFFER)
    END
    RETURN @BUFFER
END

GO
IF EXISTS
  (SELECT *
     FROM SYS.[objects] SO
    WHERE SO.[name] = 'KAAS_FN_RemoveSpuriousWhitespace'
      AND SO.[type] = 'FN')
BEGIN
  DROP FUNCTION [dbo].[KAAS_FN_RemoveSpuriousWhitespace]
END
GO

CREATE FUNCTION
       [dbo].[KAAS_FN_RemoveSpuriousWhitespace]
      (@original VARCHAR(MAX),
       @maxlen   INT)
RETURNS VARCHAR(5000)
AS
/*******************************************************************************************************
* Removes newlines, line feeds, non-breaking spaces and multiple spaces from the input				   *
*   string in order to produce an output format suitable for display in list.                          *
*                                                                                                      *
* Stored Procedure Name	: [dbo].[KAAS_FN_RemoveSpuriousWhitespace]			                           *
* Copied from			: [dbo].[ky_RemoveSpuriousWhitespace2]				                           *
*                                                                                                      *
* Modification History	:                                                                              *
* 2019-05-31	Vinodhan K		Created                                                                *
 *******************************************************************************************************/
BEGIN
  DECLARE @len int

  SET @original = ISNULL(@original, '')
  IF LEN(@original) > (2 * @maxlen)
    BEGIN
      SET @original = SUBSTRING(@original, 1, (2 * @maxlen))
    END

  SET @original = REPLACE(@original, char(10), ' ')
  SET @original = REPLACE(@original, char(13), ' ')
  SET @original = REPLACE(@original, char(160), ' ')

  SET @original = RTRIM(@original)
  
  SET @len = 0
    WHILE (@len <> LEN(@original))
      BEGIN
        SET @len = LEN(@original)
        SET @original = REPLACE(@original, '  ', ' ')
      END

  RETURN SUBSTRING(@original, 1, @maxlen)
  
END
GO
IF EXISTS
  (SELECT TOP 1 1
     FROM SYS.[objects] SO
    WHERE SO.[name] = N'KAAS_FN_SplitString'
      AND SO.[type] = 'TF')
  BEGIN
    DROP FUNCTION [dbo].[KAAS_FN_SplitString]
  END
GO

CREATE FUNCTION KAAS_FN_SplitString
(    
      @Input NVARCHAR(MAX),
      @Character CHAR(1)
)
RETURNS @Output TABLE (
      Item NVARCHAR(1000)
)
AS
/*******************************************************************************************************
* Splits a string on the character specifed (like ',').												   *
*                                                                                                      *
* Stored Procedure Name	: [dbo].[KAAS_FN_SplitString]												   *
* Copied from			: [dbo].[ky_NETFNSplitString]												   *
*                                                                                                      *
* Modification History	:                                                                              *
* 2019-04-13	Vinodhan K		Created                                                                *
*******************************************************************************************************/
BEGIN
      DECLARE @StartIndex INT, @EndIndex INT
 
      SET @StartIndex = 1
      IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
      BEGIN
            SET @Input = @Input + @Character
      END
 
      WHILE CHARINDEX(@Character, @Input) > 0
      BEGIN
            SET @EndIndex = CHARINDEX(@Character, @Input)
           
            INSERT INTO @Output(Item)
            SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)
           
            SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
      END
 
      RETURN
END
GO
IF EXISTS
  (SELECT TOP 1 1
     FROM SYS.[objects] SO
    WHERE SO.[name] = N'KAAS_FN_SUFFormat'
      AND SO.[type] = 'FN')
  BEGIN
    DROP FUNCTION [dbo].[KAAS_FN_SUFFormat]
  END
GO

CREATE FUNCTION
       [dbo].[KAAS_FN_SUFFormat]
      (@ORIGINAL  VARCHAR(MAX),
       @FORMAT    VARCHAR(200))
RETURNS VARCHAR(MAX)
AS
/*************************************************************************************************************
 *                                                                                                           *
 * Stored Procedure Name : KAAS_FN_SUFFormat                                                                 *
 * Copied From           : [dbo].[ky_SUFFormat]                                                              *
 *                                                                                                           *
 * To be used as Format function for Document Generator codes                                                *
 *                                                                                                           *
 * Compatibility information - PLEASE update older versions if necessary to ensure the compatible software   *
 *                                    remains fully functional                                               *
 *   *****************************************************************************************************   *
 *   *                                                                                                   *   *
 *   * Supersedes:                              -                                                        *   *
 *   * First compatible version:                5.6.4.1                                                  *   *
 *   * Last compatible software version:        -                                                        *   *
 *   * Superseded by:                           -                                                        *   *
 *   *                                                                                                   *   *
 *   *****************************************************************************************************   *
 *                                                                                                           *
 * Modification History                                                                                      *
 * 2019-01-15   Pino Carafa       Created                                                                    *
 * 2019-01-16   Pino Carafa       Handle date formats that SQL doesn't like                                  *
 * 2019-01-18   Pino Carafa       Check the x/y/z pattern before using IsDate as SQL will interpret strings  *
 *                                in xx/yy/zzzz format as mm/dd/yyyy by default regardless of the culture    *
 * 2019-01-18   Pino Carafa       Ensure numerical values with thousand separators are interpreted correctly *
 * 2019-02-05   Pino Carafa       Ensure very large numbers are handled correctly, handle '-' and '+'        *
 * 2019-02-08   Pino Carafa       Handle any reasonable date formats that could be passed in, even things    *
 *                                like "Friday, the 8th day of February, 2019"                               *
 * 2019-02-13   Pino Carafa       KEYD-5744 - It was recognising certain integers as Dates                   *
 * 2019-07-27   Vinodhan          Created KAAS_SUFFormat                                                     *
 *                                                                                                           *
 *************************************************************************************************************/
BEGIN

  DECLARE @ORIGINALDATE DATETIME
  DECLARE @PART1 VARCHAR(10)
  DECLARE @PART2 VARCHAR(10)
  DECLARE @PART3 VARCHAR(10)
  DECLARE @ALTERNATIVE VARCHAR(10)

  IF (@ORIGINAL LIKE '%/%/%') AND (@ORIGINAL NOT LIKE '%/%/%/%')
    BEGIN
      SET @PART1 = SUBSTRING(@ORIGINAL, 1, CHARINDEX('/', @ORIGINAL) - 1)
      SET @PART2 = SUBSTRING(@ORIGINAL, LEN(@PART1) + 2,  CHARINDEX('/', @ORIGINAL, LEN(@PART1) + 2) - LEN(@PART1) - 2)
      SET @PART3 = SUBSTRING(@ORIGINAL, LEN(@PART1) + LEN(@PART2) + 3, LEN(@ORIGINAL) - LEN(@PART1) - LEN(@PART2) - 2)
      IF LEN(@PART1) = 4 --YYYY/MM/DD
        BEGIN
          SET @ALTERNATIVE = CONVERT(VARCHAR(10), @PART1 + @PART2 + @PART3) --YYYYMMDD
          IF ISDATE(@ALTERNATIVE) = 1
            BEGIN
              SET @ORIGINALDATE = CONVERT(DATETIME, @ALTERNATIVE)
            END
        END
      ELSE IF LEN(@PART3) = 4 --DD/MM/YYYY
        BEGIN
          SET @ALTERNATIVE = @PART3 + @PART2 + @PART1 --YYYYMMDD
          IF ISDATE(@ALTERNATIVE) = 1
            BEGIN
              SET @ORIGINALDATE = CONVERT(DATETIME, @ALTERNATIVE)
            END
        END
    END
  ELSE IF (@ORIGINAL LIKE '%-%-%') AND (@ORIGINAL NOT LIKE '%-%-%-%')
    BEGIN
      SET @PART1 = SUBSTRING(@ORIGINAL, 1, CHARINDEX('-', @ORIGINAL) - 1)
      SET @PART2 = SUBSTRING(@ORIGINAL, LEN(@PART1) + 2,  CHARINDEX('-', @ORIGINAL, LEN(@PART1) + 2) - LEN(@PART1) - 2)
      SET @PART3 = SUBSTRING(@ORIGINAL, LEN(@PART1) + LEN(@PART2) + 3, LEN(@ORIGINAL) - LEN(@PART1) - LEN(@PART2) - 2)
      IF LEN(@PART1) = 4 --YYYY-MM-DD
        BEGIN
          SET @ALTERNATIVE = CONVERT(VARCHAR(10), @PART1 + @PART2 + @PART3) --YYYYMMDD
          IF ISDATE(@ALTERNATIVE) = 1
            BEGIN
              SET @ORIGINALDATE = CONVERT(DATETIME, @ALTERNATIVE)
            END
        END
      ELSE IF LEN(@PART3) = 4 --DD-MM-YYYY
        BEGIN
          SET @ALTERNATIVE = @PART3 + @PART2 + @PART1 --YYYYMMDD
          IF ISDATE(@ALTERNATIVE) = 1
            BEGIN
              SET @ORIGINALDATE = CONVERT(DATETIME, @ALTERNATIVE)
            END
        END
    END
  ELSE IF (CHARINDEX(' ', @ORIGINAL) > 0) OR (CHARINDEX('/', @ORIGINAL) > 0) OR (CHARINDEX('-', @ORIGINAL) > 0) --KEYD-5744
    BEGIN
      DECLARE @orgtest VARCHAR(MAX)
      SET @orgtest = @original
      SET @orgtest = REPLACE(@orgtest, 'sunday', '')
      SET @orgtest = REPLACE(@orgtest, 'monday', '')
      SET @orgtest = REPLACE(@orgtest, 'tuesday', '')
      SET @orgtest = REPLACE(@orgtest, 'wednesday', '')
      SET @orgtest = REPLACE(@orgtest, 'thursday', '')
      SET @orgtest = REPLACE(@orgtest, 'friday', '')
      SET @orgtest = REPLACE(@orgtest, 'saturday', '')
      SET @orgtest = REPLACE(@orgtest, 'the', '')
      SET @orgtest = REPLACE(@orgtest, 'day', '')
      SET @orgtest = REPLACE(@orgtest, 'of', '')
      SET @orgtest = REPLACE(@orgtest, ',', '')
      SET @orgtest = REPLACE(REPLACE(@orgtest, 'st', ''), 'augu', 'august') --there's always one, isn't there!
      SET @orgtest = REPLACE(@orgtest, 'nd', '')
      SET @orgtest = REPLACE(@orgtest, 'rd', '')
      SET @orgtest = REPLACE(@orgtest, 'th', '')
      SET @orgtest = REPLACE(@orgtest, '  ', ' ')
      SET @orgtest = REPLACE(@orgtest, '  ', ' ')
      SET @orgtest = REPLACE(@orgtest, '  ', ' ')

      IF ISDATE(@orgtest) = 1
        BEGIN
          SET @ORIGINAL = @orgtest
        END

      IF ISDATE(@ORIGINAL) = 1
        BEGIN
          SET @ORIGINALDATE = CONVERT(DATETIME, @ORIGINAL)
        END
    END
  ELSE IF (@Format LIKE '@d%') OR (@Format LIKE '%DD%') OR (@Format LIKE '%MM%') OR (@Format LIKE '%YY%')
    --KEYD-5744
    BEGIN
      IF ISDATE(@ORIGINAL) = 1
        BEGIN
          SET @ORIGINALDATE = CONVERT(DATETIME, @ORIGINAL)
        END
    END

  IF @ORIGINALDATE IS NOT NULL
    BEGIN
      RETURN [dbo].[KAAS_FN_SUFGetFormattedDate](@ORIGINALDATE, @FORMAT)
    END

  IF @FORMAT LIKE '@d%'
    BEGIN
      RETURN ''
    END

  IF NOT RTRIM(ISNULL(@FORMAT, '')) = ''
    BEGIN
      IF ISNUMERIC(@ORIGINAL) = 1
        BEGIN
          DECLARE @CHECK VARCHAR(8)
          DECLARE @THOUSEP CHAR(1)
          SET @CHECK = CONVERT(VARCHAR, CONVERT(MONEY, 1000), 1)
          SET @THOUSEP = SUBSTRING(@CHECK, 2, 1)

          IF RTRIM(ISNULL(@ORIGINAL, '')) IN ('-', '+')
            BEGIN
              SET @ORIGINAL = '0'
            END

          RETURN [dbo].[KAAS_FN_SUFGetFormattedValue](REPLACE(@ORIGINAL, @THOUSEP, ''), @FORMAT, 1, 0, NULL)
        END
    END

  RETURN CONVERT(VARCHAR(MAX), @ORIGINAL)
END

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

CREATE FUNCTION
       [dbo].[KAAS_FN_SUFGetFormattedDate]
      (@DATE datetime, @FORMAT VARCHAR(200))
RETURNS VARCHAR(MAX)
AS
/*************************************************************************************************************
 *                                                                                                           *
 * Stored Procedure Name : KAAS_FN_SUFGetFormattedDate                                                       *
 * Copied From           : [dbo].[ky_SUFGetFormattedDate]                                                    *
 *                                                                                                           *
 * User Defined Scalar Function to get a Doc Assist date value formatted to the requested format             *
 *                                                                                                           *
 * Compatibility information - PLEASE update older versions if necessary to ensure the compatible software   *
 *                                    remains fully functional                                               *
 *   *****************************************************************************************************   *
 *   *                                                                                                   *   *
 *   * Supersedes:                              -                                                        *   *
 *   * First compatible version:                5.6.4.1                                                  *   *
 *   * Last compatible software version:        -                                                        *   *
 *   * Superseded by:                           -                                                        *   *
 *   *                                                                                                   *   *
 *   *****************************************************************************************************   *
 *                                                                                                           *
 * Modification History                                                                                      *
 * 2018-12-17   Pino Carafa       Created                                                                    *
 * 2019-07-27   Vinodhan K        Created KAAS_FN_SUFGetFormattedDate                                        *
 *                                                                                                           *
 *************************************************************************************************************/
BEGIN

RETURN [dbo].[KAAS_FN_ConvertDate](@DATE, @FORMAT)

END

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

CREATE FUNCTION
       [dbo].[KAAS_FN_SUFGetFormattedValue]
      (@NUMBERV VARCHAR(30), @FORMAT VARCHAR(200), @THOUSANDSEPARATORS BIT, @NEGBRACKETS BIT, @CULTURE VARCHAR(10))
RETURNS VARCHAR(MAX)
AS
/*************************************************************************************************************
 *                                                                                                           *
 * [dbo].[ky_SUFGetFormattedValue]                                                                           *
 *                                                                                                           *
 * User Defined Scalar Function to get a Doc Assist Number value formatted to the requested format           *
 *                                                                                                           *
 * Compatibility information - PLEASE update older versions if necessary to ensure the compatible software   *
 *                                    remains fully functional                                               *
 *   *****************************************************************************************************   *
 *   *                                                                                                   *   *
 *   * Supersedes:                              -                                                        *   *
 *   * First compatible version:                5.6.4.1                                                  *   *
 *   * Last compatible software version:        -                                                        *   *
 *   * Superseded by:                           -                                                        *   *
 *   *                                                                                                   *   *
 *   *****************************************************************************************************   *
 *                                                                                                           *
 * Modification History                                                                                      *
 * 2018-12-04   Pino Carafa       Created                                                                    *
 * 2019-02-05   Pino Carafa       Change number parameter to VARCHAR so this function can then cope with     *
 *                                Thousand Separators                                                        *
 * 2019-07-27   Vinodhan K        Created KAAS_FN_SUFGetFormattedValue                                       *
 *                                                                                                           *
 *************************************************************************************************************/
BEGIN

DECLARE @CHECK VARCHAR(8)
DECLARE @THOUSEP CHAR(1)
SET @CHECK = CONVERT(VARCHAR, CONVERT(MONEY, 1000), 1)
SET @THOUSEP = SUBSTRING(@CHECK, 2, 1)

DECLARE @NUMBER FLOAT

SET @NUMBER = CONVERT(FLOAT, REPLACE(@NUMBERV, @THOUSEP, ''))

RETURN [dbo].[KAAS_FN_ConvertNumber](@NUMBER, @FORMAT, @THOUSANDSEPARATORS, @NEGBRACKETS, @CULTURE)

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

CREATE FUNCTION
       [dbo].[KAAS_FN_UserCanPostTime]
      (@MATTER   VARCHAR(20),
       @HANDLER  VARCHAR(20),
       @RECORDID INT)
RETURNS INT
AS
/*******************************************************************************************************
 *  This function will check if a																	   *
 *  given user can post time a given matter.														   *
 *																									   *
 *  Return Bit Mask:																				   *
 *      0 - User can post																			   *
 *      1 - Matter doesn't exist																	   *
 *      2 - Matter is closed																		   *
 *      4 - Matter charge list enabled but user not present											   *
 *      8 - Client charge list enabled but user not present											   *
 *      16 - Task not specified																		   *
 *      32 - Client is not approved for anti-money laundering (KEYD-4890)							   *
 *																									   *
 *  Flag Bit Mask:																					   *
 *      1 - Matter charge out rate list is enabled													   *
 *      2 - Client charge out rate list is enabled													   *
 *      4 - User is in Matter charge out rate list													   *
 *      8 - User is in Client charge out rate list													   *
*                                                                                                      *
* Stored Procedure Name : [dbo].[KAAS_FN_UserCanPostTime]											   *
* Copied from			: [dbo].[ky_NETFNUserCanPostTime]											   *
*                                                                                                      *
* Modification History:                                                                                *
* 2019-04-24    Vinodhan K        Created                                                              *
 *******************************************************************************************************/
BEGIN
    DECLARE @RETURN      INT -- BitWise value where bits are mapped to errors
    DECLARE @FLAG        INT -- BitWise value for charge out rates
    DECLARE @CHECK       BIT -- Checks if a setting is enabled or not
    DECLARE @CHARGEGROUP VARCHAR(10) -- Charge group of @HANDLER

    SET @RETURN = 0
    SET @FLAG = 0

    SET @MATTER = ISNULL(@MATTER, '')
    SET @HANDLER = ISNULL(@HANDLER, '')

    SELECT @CHARGEGROUP = ISNULL(HAN.[ChargeGroup], '')
      FROM [dbo].[Handlers] AS HAN
     WHERE HAN.[CODE] = @HANDLER

    IF @MATTER <> '~'
    BEGIN
        -- Check if the matter code contains a '/'
        -- If it doesn't then it's probably because the matter is blank
        SET @MATTER = ISNULL(NULLIF(@MATTER, ''), '/')

        IF NOT EXISTS(SELECT * FROM [dbo].[matters] WHERE [Code] = @MATTER)
            SET @RETURN = @RETURN | 1 -- Matter doesn't exist

        IF EXISTS(SELECT * FROM [dbo].[matters] WHERE [Code] = @MATTER AND [Closed] = 'Y')
            SET @RETURN = @RETURN | 2 -- Matter is closed

        SET @CHECK = ISNULL((SELECT CONVERT(BIT, ISNULL([KeyValue], 'False'))
                               FROM [dbo].[Settings]
                              WHERE [KeyName] = 'ChargeOutRateCheck'), 0)

        IF @CHECK = 1
        BEGIN
            /*
                Below is where the charge out rate indicators are handled. There's a hierarchy
                for how a user's rate is calculated and that same hierarchy will be applied here.
                The handler may be in a charge group and this is treated the same as a handler code.

                                  No                          No
            Matter rate active? +-----> Client rate active? +-----> User can't post
                    +                           +
                    |                           |
                    | Yes                       | Yes
                    |                           |
                    v            No             v            No
            Is handler in list? +--+    Is handler in list? +--+
                    +              |            +              |
                    |              |            |              |
                    | Yes          |            | Yes          |
                    |              |            |              |
                    v              |            v              |
              User can post        |      User can post        |
                                   |                           |
                                   v                           v
                            User can't post             User can't post
            */

            SELECT @FLAG = @FLAG |
                           CASE WHEN ISNULL(MAT.[ChargeOutRateInd], 'N') = 'Y'
                                     THEN 1
                                ELSE 0 END
              FROM [dbo].[matters] AS MAT
             WHERE MAT.[Code] = @MATTER

            SELECT @FLAG = @FLAG |
                           CASE WHEN ISNULL(CNT.[ChargeOutRateInd], 'N') = 'Y'
                                     THEN 2
                                ELSE 0 END
              FROM [dbo].[Contacts] AS CNT
             WHERE CNT.[Code] = SUBSTRING(@MATTER, 1, CHARINDEX('/', @MATTER) - 1)

            IF EXISTS
              (SELECT *
                 FROM [dbo].[MatterChargeOutRates] AS MCR
                WHERE MCR.[MatterCode] = @MATTER
                 AND (MCR.[FeCode] = @HANDLER
                   OR MCR.[FeCode] = @CHARGEGROUP))
              BEGIN
                SET @FLAG = @FLAG | 4
              END

            IF EXISTS
              (SELECT *
                 FROM [dbo].[ClientChargeOutRates] AS CCR
                WHERE CCR.[ClientCode] = SUBSTRING(@MATTER, 1, CHARINDEX('/', @MATTER) - 1)
                 AND (CCR.[FeCode] = @HANDLER
                   OR CCR.[FeCode] = @CHARGEGROUP))
              BEGIN
                SET @FLAG = @FLAG | 8
              END

            -- If matter list is enabled and user is not in the list
            IF @FLAG & 1 > 0 AND @FLAG & 4 = 0
                SET @RETURN = @RETURN | 4

            -- If client list is enabled, matter list is disabled and user is not in the list
            IF @FLAG & 2 > 0 AND @FLAG & 8 = 0 AND @FLAG & 1 = 0
                SET @RETURN = @RETURN | 8
        END
    END

    IF @RECORDID IS NOT NULL
    BEGIN
        IF (SELECT TOP 1 CASE WHEN TDB.[Matter] = '~'
                                   THEN ISNULL(TDB.[NCCCode], '')
                              ELSE ISNULL(TDB.[Task], '')
                         END
              FROM [dbo].[TimeDayBook] AS TDB
             WHERE TDB.[RecordID] = @RECORDID) = ''
            SET @RETURN = @RETURN | 16
    END

    -- Check if AML is required and if the contact is approved
    IF RTRIM(ISNULL(@MATTER, '')) NOT IN ('', '~', '/')
      BEGIN
        IF ISNULL((SELECT TOP 1 [KeyValue] FROM [dbo].[Settings] WHERE [KeyName] = 'AMLRequiredForTime'), '') = '1' AND
           ISNULL((SELECT TOP 1 CONVERT(VARCHAR(10), ISNULL(CNT.[Approved], 0))
                     FROM [dbo].[Contacts] AS CNT
                          INNER JOIN [dbo].[matters] AS MAT
                                  ON CNT.[Code] = MAT.[ClientCode]
                    WHERE MAT.[Code] = @MATTER
                      AND ISNULL(CNT.[Approved], 0) = 1), '') <> '1'
        BEGIN
          SELECT @RETURN = @RETURN | 32
        END
      END

    RETURN @RETURN
END

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

CREATE FUNCTION
       [dbo].[KAAS_FN_VerboseDate]
      (@Date       DATETIME,
       @UseThe     BIT,
       @UseDay     BIT,
       @UseOf      BIT,
       @UseComma   BIT,
       @Capitalise BIT)
RETURNS VARCHAR(100)
AS
/*************************************************************************************************************
 *                                                                                                           *
 * Stored Procedure name : KAAS_FN_VerboseDate                                                               *
 * Copied From           : [dbo].[ky_NETFNVerboseDate]                                                       *
 * returns a verbose representation of a date                                                                *
 * Parameters can be set to fine-tune the result as follows. Example date 2018-01-29                         *
 * @UseThe @UseDay @UseOf @UseComma @Capitalise                                                              *
 * 1       1       1      1         1               The 29th Day of January, 2018                            *
 * 0       0       1      0         0               29th of January 2018                                     *
 * 1       0       0      0         0               the 29th of January 2018 - "of" implied by "the"         *
 *                                                                                                           *
 * Compatibility information - PLEASE update older versions if necessary to ensure the compatible software   *
 *                                    remains fully functional                                               *
 *   *****************************************************************************************************   *
 *   *                                                                                                   *   *
 *   * Supersedes:                              -                                                        *   *
 *   * First compatible version:                - 5.5.4.1                                                *   *
 *   * Last compatible software version:        -                                                        *   *
 *   * Superseded by:                           -                                                        *   *
 *   *                                                                                                   *   *
 *   *****************************************************************************************************   *
 *                                                                                                           *
 * Modification History                                                                                      *
 * 2018-01-29   Pino Carafa       Created                                                                    *
 * 2019-07-27   Vinodhan K        Created KAAS_FN_VerboseDate                                                *
 *                                                                                                           *
 *************************************************************************************************************/
BEGIN
  DECLARE @DAY INT
  DECLARE @SUFFIX VARCHAR(2)
  DECLARE @Result VARCHAR(100)

  SET @DAY = DAY(@DATE)

  SET @SUFFIX = CASE WHEN @DAY = 1
                          THEN 'st'
                     WHEN @DAY = 2
                          THEN 'nd'
                     WHEN @DAY = 3
                          THEN 'rd'
                     WHEN @DAY = 21
                          THEN 'st'
                     WHEN @DAY = 22
                          THEN 'nd'
                     WHEN @DAY = 23
                          THEN 'rd'
                     WHEN @DAY = 31
                          THEN 'st'
                     ELSE 'th' END

  IF @UseThe = 1
    BEGIN
      -- If they use "The", the use of "of" is implied
      SET @UseOf = 1
      IF @Capitalise = 1
        BEGIN
          SET @Result = 'T'
        END
      ELSE
        BEGIN
          SET @Result = 't'
        END

      SET @Result = @Result + 'he '
    END
  ELSE
    BEGIN
      SET @Result = ''
    END

  SET @Result = @Result +  CONVERT(VARCHAR(2), @DAY) + @SUFFIX
  
  If @UseDay = 1
    BEGIN
      -- you can't have "The Nth Day January, so if they specify UseDay, UseOf is implied
      SET @UseOf = 1

      If @Capitalise = 1
        BEGIN
          SET @Result = @Result + ' D'
        END
      ELSE
        BEGIN
          SET @Result = @Result + ' d'
        END

      SET @Result = @Result + 'ay'
    END
  
  If @UseOf = 1
    BEGIN
      -- we never capitalise "of"
      SET @Result = @Result + ' of'
    END
  
  SET @Result = @Result + ' ' + DATENAME(MONTH, @DATE)
  
  IF @UseComma = 1
    BEGIN
      SET @Result = @Result + ','
    END

  SET @Result = @Result + ' ' + CONVERT(VARCHAR(4), YEAR(@DATE))

  Return @Result
END

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

CREATE PROCEDURE [dbo].[KAAS_GetTeamLookupList]
AS
/*******************************************************************************************************
* This procedure is used fetch the team list                                          				   *
*                                                                                                      *
* Stored Procedure Name	: [dbo].[KAAS_GetTeamLookupList]									           *
* Copied from			: [dbo].[ky_NETGetTeamLookupList]					  					       *
*                                                                                                      *
* Modification History	:                                                                              *
* 2019-06-04	Vinodhan K		Created                                                                *
 *******************************************************************************************************/
BEGIN
    SELECT
        RTRIM(ISNULL(HAN.[CODE], '')) AS [CODE],
        RTRIM(ISNULL(HAN.[NAME], '')) AS [NAME],
        RTRIM(ISNULL(HAN.[DEPT], '')) AS [DEPT],
        CASE WHEN RTRIM(ISNULL(HAN.[TEAMCODE], 'N')) = 'Y' THEN 'Y' ELSE 'N' END AS [TEAMCODE]
    FROM
        [Handlers] HAN
    WHERE
        RTRIM(ISNULL(HAN.[TEAMCODE], 'N')) = 'Y'
    ORDER BY
        RTRIM(ISNULL(HAN.[NAME], ''))
END

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

 CREATE FUNCTION
        [dbo].[KAAS_TF_GetDocFolders3]
       (@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,
        [foldershared]        BIT                                    NOT NULL,
        [folderisours]        BIT                                    NOT NULL)
AS
/*************************************************************************************************************
 *                                                                                                           *
 * Stored Procedure Name : KAAS_TF_GetDocFolders3                                                            *
 * Copied From           : [dbo].[ky_NETTFGetDocFolders3]                                                    *
 *                                                                                                           *
 * Compatibility information - PLEASE update older versions if necessary to ensure the compatible software   *
 *                                    remains fully functional                                               *
 *   *****************************************************************************************************   *
 *   *                                                                                                   *   *
 *   * Supersedes:                              - ky_NETTFGetDocFolders2                                 *   *
 *   * First compatible version:                - 5.5.1.2                                                *   *
 *   * Last compatible software version:        - 5.5.3.9                                                *   *
 *   * Superseded by:                           - ky_NETTFGetDocFolders4                                 *   *
 *   *                                                                                                   *   *
 *   *****************************************************************************************************   *
 *                                                                                                           *
 * 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:                                                                                     *
 * 2016-04-20    Pino Carafa      Created                                                                    *
 * 2016-05-12    Pino Carafa      Added Folder Icon                                                          *
 * 2017-06-26    Pino Carafa      Add outer join for folder sharing                                          *
 * 2017-07-12    Pino Carafa      Add indicator to show whether the share is ours or we're linking to it     *
 * 2017-09-04    Pino Carafa      Corrected code to check whether a share is ours.                           *
 * 2019-08-27    Vinodhan         Created KAAS_TF_GetDocFolders3                                             *
 *                                                                                                           *
 *************************************************************************************************************/
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,
          [foldershared]        BIT                                    NOT NULL,
          [folderisours]        BIT                                    NOT NULL)

  IF ISNULL(@parentid, 0) = 0
    BEGIN
      INSERT
          INTO @DocFoldersX
              ([id],
               [parent],
               [foldername],
               [foldericon],
               [foldershared],
               [folderisours])
        SELECT [DF].[id],
               0,
               [DF].[foldername],
               [DF].[foldericon],
               0,
               1
          FROM [dbo].[DocFolders] [DF]
         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],
                   [foldershared],
                   [folderisours])
            SELECT [DF].[id],
                   ISNULL([DF].[parent], 0),
                   [DF].[foldername],
                   [DF].[foldericon],
                   0,
                   1
              FROM [dbo].[DocFolders] [DF]
             WHERE [DF].[id] = @parentid
          ORDER BY [DF].[foldername],
                   [DF].[id]
        END
      ELSE
        BEGIN
            INSERT
              INTO @DocFoldersX
                  ([id],
                   [parent],
                   [foldername],
                   [foldericon],
                   [foldershared],
                   [folderisours])
            SELECT [DF].[id],
                   ISNULL([DF].[parent], 0),
                   [DF].[foldername],
                   [DF].[foldericon],
                   0,
                   1
              FROM [dbo].[DocFolders] [DF]
             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 @TOTAL INT
  DECLARE @ITEM INT

  SELECT @TOTAL = COUNT(1) FROM @DocFoldersX

  SET @ITEM = 0

  WHILE @ITEM < @TOTAL
    BEGIN
        DECLARE @id INT
        DECLARE @parent INT
        DECLARE @foldername VARCHAR(500)
        DECLARE @foldericon INT
        DECLARE @foldershared BIT
        DECLARE @folderisours BIT

        SELECT @id = [DFX].[id],
               @parent = [DFX].[parent],
               @foldername = [DFX].[foldername],
               @foldericon = [DFX].[foldericon],
               @foldershared = [DFX].[foldershared],
               @folderisours = [DFX].[folderisours]
          FROM @DocFoldersX [DFX]
         WHERE [DFX].[seq] = @ITEM

        INSERT
          INTO @DocFolders
              ([id],
               [parent],
               [foldername],
               [foldericon],
               [foldershared],
               [folderisours])
        SELECT @id,
               @parent,
               @foldername,
               @foldericon,
               @foldershared,
               @folderisours

        IF @fulltree = 1
          BEGIN
            INSERT
              INTO @DocFolders
                  ([id],
                   [parent],
                   [foldername],
                   [foldericon],
                   [foldershared],
                   [folderisours])
            SELECT [DFC].[id],
                   [DFC].[parent],
                   [DFC].[foldername],
                   [DFC].[foldericon],
                   [DFC].[foldershared],
                   [DFC].[folderisours]
              FROM [dbo].[KAAS_TF_GetDocFolders3](@id, 1, 0) [DFC]
          END

      SET @ITEM = @ITEM + 1
    END

  RETURN
END
GO
