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_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 KAAS_FN_ConvertDateToClarion END GO CREATE FUNCTION KAAS_FN_ConvertDateToClarion (@datetime DATETIME) RETURNS INT AS /******************************************************************************************************* * This fucntion is used to Converts a date/time value to a Clarion number. * * * * Stored Procedure Name : [dbo].[KAAS_FN_ConvertDateToClarion] * * Copied from : [dbo].[ky_NETFNConvertDateToClarion] * * * * Modification History : * * 2019-07-10 Vinodhan K Created * *******************************************************************************************************/ 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] = 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 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] = 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