-- This file is part of the it-crafts software and is subject to copyright protection.
-- (C) 2022..2024, All rights reserved by Steffen Anders, Elsteraue - Germany
-- Visit also https://www.it-crafts.de.
--

-------------------------------------------------------------------------------------
--
IF OBJECT_ID (N'itcfunc_StringHash', N'FN') IS NOT NULL
    DROP FUNCTION dbo.itcfunc_StringHash;
GO
CREATE FUNCTION dbo.itcfunc_StringHash(@Param_s varchar(128)) RETURNS integer
AS
BEGIN
  DECLARE @s varchar(128)
  DECLARE @l integer
  DECLARE @i integer
  DECLARE @w integer
  DECLARE @r integer

  SET @s = RTRIM(@Param_s)
  SET @l = LEN(@s)
  SET @i = 1
  SET @w = 1
  SET @r = 0
  WHILE @i <= @l BEGIN
    SET @r = @r + (@w * ASCII(SUBSTRING(@s,@i,1)))
    SET @i = @i + 1
	SET @w = @w + 1
	IF @w > 9 BEGIN
	  SET @w = 1
	END
  END
  RETURN @r
END
GO

-------------------------------------------------------------------------------------
--
IF OBJECT_ID (N'itcfunc_IncString', N'FN') IS NOT NULL
    DROP FUNCTION dbo.itcfunc_IncString;
GO
CREATE FUNCTION dbo.itcfunc_IncString(@Param_s varchar(128)) RETURNS varchar(128)
AS
BEGIN
  DECLARE @s varchar(128)
  DECLARE @r varchar(128)
  DECLARE @l integer
  DECLARE @i integer
  DECLARE @c char(1)
  DECLARE @u integer

  SET @s = RTRIM(@Param_s)
  SET @l = LEN(@s)
  SET @i = @l
  SET @r = ''
  SET @u = 1

  WHILE @i > 0 BEGIN
    SET @c = SUBSTRING(@s,@i,1)

	IF  @u = 1 BEGIN
	  IF @c >= '0' AND @c < '9' BEGIN
	    SET @c = CHAR(ASCII(@c) + 1)
	    SET @u = 0
	  END ELSE IF @c = '9' BEGIN
	    SET @c = '0'
	  END
	END

    SET @r = @c + @r
    SET @i = @i - 1
  END

  RETURN @r
END
GO

-------------------------------------------------------------------------------------
--
IF OBJECT_ID (N'itcfunc_IncStringHex', N'FN') IS NOT NULL
    DROP FUNCTION dbo.itcfunc_IncStringHex;
GO
CREATE FUNCTION dbo.itcfunc_IncStringHex(@Param_s varchar(128)) RETURNS varchar(128)
AS
BEGIN
  DECLARE @s varchar(128)
  DECLARE @r varchar(128)
  DECLARE @l integer
  DECLARE @i integer
  DECLARE @c char(1)
  DECLARE @u integer

  SET @s = RTRIM(@Param_s)
  SET @l = LEN(@s)
  SET @i = @l
  SET @r = ''
  SET @u = 1

  WHILE @i > 0 BEGIN
    SET @c = SUBSTRING(@s,@i,1)

	IF  @u = 1 BEGIN
	  IF (@c >= '0' AND @c < '9') OR (@c >= 'A' AND @c < 'F') BEGIN
	    SET @c = CHAR(ASCII(@c) + 1)
	    SET @u = 0
	  END ELSE IF @c = '9' BEGIN
	    SET @c = 'A'
	    SET @u = 0
	  END ELSE IF @c = 'F' BEGIN
	    SET @c = '0'
	  END
	END

    SET @r = @c + @r
    SET @i = @i - 1
  END

  RETURN @r
END
GO

-------------------------------------------------------------------------------------
-- Ersetzt alle Zeichen, die kleiner als Space sind durch Space
IF OBJECT_ID (N'itcfunc_CleanString', N'FN') IS NOT NULL
    DROP FUNCTION dbo.itcfunc_CleanString;
GO
CREATE FUNCTION dbo.itcfunc_CleanString(@Param varchar(max)) RETURNS varchar(max)
AS
BEGIN
  DECLARE @mLen integer = LEN(@Param)
  DECLARE @mPos integer = 1
  DECLARE @mChr char(1)
  DECLARE @mRet varchar(max) = '' 

  WHILE @mPos <= @mLen BEGIN
    SET @mChr = SUBSTRING(@Param, @mPos, 1)
	IF @mChr < ' ' SET @mChr = ' '
	SET @mRet = @mRet + @mChr
	SET @mPos = @mPos + 1
  END
  RETURN @mRet
END
GO

-------------------------------------------------------------------------------------
--
IF OBJECT_ID (N'itcfunc_integer', N'FN') IS NOT NULL
    DROP FUNCTION dbo.itcfunc_integer
GO
CREATE FUNCTION dbo.itcfunc_integer(@Parm_Value varchar(100)) RETURNS integer
AS
BEGIN
	SET @Parm_Value = ISNULL(@Parm_Value,'0')
	IF @Parm_Value = '' SET @Parm_Value = '0'
    RETURN convert(integer,@Parm_Value)
END
GO

-------------------------------------------------------------------------------------
--
IF OBJECT_ID (N'itcfunc_float', N'FN') IS NOT NULL
    DROP FUNCTION dbo.itcfunc_float
GO
CREATE FUNCTION dbo.itcfunc_float(@Parm_Value varchar(100)) RETURNS float
AS
BEGIN
	SET @Parm_Value = ISNULL(@Parm_Value,'0')
	IF @Parm_Value = '' SET @Parm_Value = '0'
    RETURN convert(float,@Parm_Value)
END
GO

-------------------------------------------------------------------------------------
--
IF OBJECT_ID (N'itcfunc_floatToString', N'FN') IS NOT NULL
    DROP FUNCTION dbo.itcfunc_floatToString
GO
CREATE FUNCTION dbo.itcfunc_floatToString(@Param float) RETURNS varchar(20)
AS
BEGIN
    DECLARE @Result varchar(20) = LTRIM(STR(@Param,20,6))
	DECLARE @Len integer = LEN(@Result)

	WHILE SUBSTRING(@Result, @Len, 1) = '0' 
	  SET @Len = @Len - 1
	IF SUBSTRING(@Result, @Len, 1) = '.' 
	  SET @Len = @Len - 1

    RETURN SUBSTRING(@Result,1,@Len)
END
GO

-------------------------------------------------------------------------------------
--
IF OBJECT_ID (N'itcfunc_datetime', N'FN') IS NOT NULL
    DROP FUNCTION dbo.itcfunc_datetime
GO
CREATE FUNCTION dbo.itcfunc_datetime(@Parm_Value varchar(100)) RETURNS datetime
AS
BEGIN
	SET @Parm_Value = ISNULL(@Parm_Value,'1900-01-01')
	IF @Parm_Value = '' SET @Parm_Value = '1900-01-01'
	IF @Parm_Value = 'H' SET @Parm_Value = CONVERT(varchar(10),GETDATE(),120)
    RETURN convert(datetime,@Parm_Value,120)
END
GO

-------------------------------------------------------------------------------------
--
IF OBJECT_ID (N'itcfunc_formatTime', N'FN') IS NOT NULL
    DROP FUNCTION dbo.itcfunc_formatTime
GO
CREATE FUNCTION dbo.itcfunc_formatTime(@Parm_Value varchar(100)) RETURNS varchar(5)
AS
BEGIN
    DECLARE @m_Value varchar(100) = REPLACE(ISNULL(@Parm_Value,''),',',':')
	IF @m_Value = '' RETURN ''

	DECLARE @m_std integer = 0
	DECLARE @m_min integer = 0
	DECLARE @m_DelimiterPos integer = CHARINDEX(':', @m_Value)

	IF @m_DelimiterPos > 0 BEGIN
	  SET @m_std = dbo.itcfunc_integer(SUBSTRING(@m_Value,1,@m_DelimiterPos - 1)) 
	  SET @m_min = dbo.itcfunc_integer(SUBSTRING(@m_Value,@m_DelimiterPos + 1, LEN(@m_Value) - @m_DelimiterPos)) 
	END ELSE
	  SET @m_std = dbo.itcfunc_integer(@m_Value) 

	IF @m_std < 0  RETURN '00:00'
	IF @m_std > 23 RETURN '24:00'
	IF @m_min < 0  SET @m_min = 0
	IF @m_min > 59 SET @m_min = 59
	RETURN RIGHT('0' + LTRIM(STR(@m_std,2)), 2) + ':' + RIGHT('0' + LTRIM(STR(@m_min,2)), 2)
END
GO

-------------------------------------------------------------------------------------
--
IF OBJECT_ID (N'itcfunc_addMinutesToTime', N'FN') IS NOT NULL
    DROP FUNCTION dbo.itcfunc_addMinutesToTime
GO
CREATE FUNCTION dbo.itcfunc_addMinutesToTime(@Param_TimeValue varchar(100), @Param_MinutesToAdd integer) RETURNS varchar(5)
AS
BEGIN
  DECLARE @m_Beginn  varchar(5) = dbo.itcfunc_formatTime(@Param_TimeValue)
  DECLARE @m_Minutes integer = (dbo.itcfunc_integer(SUBSTRING(@m_Beginn,1,2)) * 60) 
                             + dbo.itcfunc_integer(SUBSTRING(@m_Beginn,4,2))
							 + @Param_MinutesToAdd
  DECLARE @m_Hours integer = @m_Minutes / 60
  IF @m_Hours >= 24
    SELECT @m_Hours = 24, @m_Minutes = 0
  ELSE
    SELECT @m_Minutes = @m_Minutes % 60

  RETURN  dbo.itcfunc_formatTime(STR(@m_Hours,2) + ':' + STR(@m_Minutes,2))
END
GO

-------------------------------------------------------------------------------------
--
IF OBJECT_ID (N'itcfunc_Duration_Minutes', N'FN') IS NOT NULL
    DROP FUNCTION dbo.itcfunc_Duration_Minutes
GO
CREATE FUNCTION dbo.itcfunc_Duration_Minutes(@mcSS varchar(10), @mcSM varchar(10), @mcES varchar(10), @mcEM varchar(10)) RETURNS integer
AS
BEGIN
  DECLARE @mSS integer = dbo.itcfunc_integer(@mcSS)
  DECLARE @mSM integer = dbo.itcfunc_integer(@mcSM)
  DECLARE @mES integer = dbo.itcfunc_integer(@mcES)
  DECLARE @mEM integer = dbo.itcfunc_integer(@mcEM)
  DECLARE @m_Result integer = 0

  IF @mES > 0 AND @mEM >= 0 BEGIN
    SET @m_Result = ((@mES * 60) + @mEM) - ((@mSS * 60) + @mSM)
	IF @m_Result < 0 OR @m_Result > (24 * 60) BEGIN
      SET @m_Result = 0
	END
  END
  RETURN @m_Result
END
GO


-------------------------------------------------------------------------------------
--
IF OBJECT_ID (N'itcfunc_KeyToken_varchar', N'FN') IS NOT NULL
    DROP FUNCTION dbo.itcfunc_KeyToken_varchar
GO
CREATE FUNCTION dbo.itcfunc_KeyToken_varchar(@xKey varchar(250), @xLen integer, @xOffset integer, @xTokenLen integer) RETURNS varchar(250)
AS
BEGIN
  RETURN SUBSTRING(@xKey,@xOffset,@xTokenLen)
END
GO

-------------------------------------------------------------------------------------
--
IF OBJECT_ID (N'itcfunc_KeyToken_integer', N'FN') IS NOT NULL
    DROP FUNCTION dbo.itcfunc_KeyToken_integer
GO
CREATE FUNCTION dbo.itcfunc_KeyToken_integer(@xKey varchar(250), @xLen integer, @xOffset integer, @xTokenLen integer) RETURNS integer
AS
BEGIN
  RETURN dbo.itcfunc_integer(SUBSTRING(@xKey,@xOffset,@xTokenLen))
END
GO

-------------------------------------------------------------------------------------
--
IF OBJECT_ID (N'itcfunc_KeyToken_float', N'FN') IS NOT NULL
    DROP FUNCTION dbo.itcfunc_KeyToken_float
GO
CREATE FUNCTION dbo.itcfunc_KeyToken_float(@xKey varchar(250), @xLen integer, @xOffset integer, @xTokenLen integer) RETURNS float
AS
BEGIN
  RETURN dbo.itcfunc_float(SUBSTRING(@xKey,@xOffset,@xTokenLen))
END
GO

-------------------------------------------------------------------------------------
--
IF OBJECT_ID (N'itcfunc_KeyToken_datetime', N'FN') IS NOT NULL
    DROP FUNCTION dbo.itcfunc_KeyToken_datetime
GO
CREATE FUNCTION dbo.itcfunc_KeyToken_datetime(@xKey varchar(250), @xLen integer, @xOffset integer, @xTokenLen integer) RETURNS datetime
AS
BEGIN
  DECLARE @m_tmp varchar(10) = SUBSTRING(@xKey,@xOffset,@xTokenLen)
  DECLARE @m_len integer = LEN(@m_tmp)
  IF @m_len < 10 SET @m_tmp = @m_tmp + SUBSTRING('1900-01-01' , @m_len + 1, 10 - @m_len)
  RETURN convert(datetime,@m_tmp,120)
END
GO

-------------------------------------------------------------------------------------
-- Alle Token einer Suchfeldeingabe, die mindestens 3 Zeichen lang sind, in eine neue Zeichenkette kopieren
IF OBJECT_ID (N'itcfunc_SearchTokenString', N'FN') IS NOT NULL
    DROP FUNCTION dbo.itcfunc_SearchTokenString
GO
CREATE FUNCTION dbo.itcfunc_SearchTokenString(@Param_ToSearch varchar(128)) RETURNS varchar(128)
AS
BEGIN
	DECLARE @mToken varchar(128)
	DECLARE @mResult varchar(128) = ''
	DECLARE @mStartPos integer = 1
	DECLARE @mDelimiterPos integer = CHARINDEX(' ', @Param_ToSearch, @mStartPos)
	WHILE @mDelimiterPos > 0 BEGIN
	  If @mDelimiterPos > 1 BEGIN
	    SET @mToken = LTRIM(SUBSTRING(@Param_ToSearch, @mStartPos, @mDelimiterPos - @mStartPos))
	    IF LEN(@mToken) >= 3
		  SET @mResult = @mResult + @mToken + ' '
	    END
	  SET @mStartPos = @mDelimiterPos + 1
	  SET @mDelimiterPos = CHARINDEX(' ', @Param_ToSearch, @mStartPos)
	END
	IF @mStartPos < LEN(@Param_ToSearch) BEGIN
	  SET @mToken = LTRIM(SUBSTRING(@Param_ToSearch, @mStartPos, LEN(@Param_ToSearch) - @mStartPos + 1))
	  IF LEN(@mToken) >= 3
	    SET @mResult = @mResult + @mToken
	END
	RETURN UPPER(RTRIM(@mResult))
END
GO

-------------------------------------------------------------------------------------
-- Tabelle zum Zwischenspeichern von Suchergebnissen
IF 0 = (SELECT count(*) from [sysobjects] where ([xtype] = 'U' or [xtype] = 'V') and [name] = 'hwt_SearchQueryResult')
	CREATE TABLE [dbo].[hwt_SearchQueryResult]
	(
	  [QueryId]    varchar(36) NOT NULL
	 ,[QueryTime]  datetime
	 ,[DataRecId]  integer     NOT NULL
	)
GO

IF 0 = (select count(*) from sysindexes si inner join sysobjects so on so.[xtype] = 'U' and so.[name] = 'hwt_SearchQueryResult' and so.[id] = si.[id] and si.[name] = 'Ik_Key00')
  CREATE INDEX [Ik_Key00] ON [dbo].[hwt_SearchQueryResult] (QueryId, DataRecId)
GO

-------------------------------------------------------------------------------------
-- Prozedur zum selectieren von Datensaetzen unter Zuhilfenahme der Indextabelle

IF OBJECT_ID (N'itcfunc_generate_select', N'FN') IS NOT NULL
    DROP FUNCTION dbo.itcfunc_generate_select
GO
CREATE FUNCTION dbo.itcfunc_generate_select(
   @Param_TableName varchar(50)
  ,@Param_KeyName varchar(50)
  ,@Param_FieldList varchar(50)
  ,@Param_Count integer
  ,@Param_From integer
  ,@Param_desc tinyint
  ,@Param_withFilter tinyint
  ,@Param_additional_Filter varchar(1000)
  ) RETURNS varchar(2000)
AS
BEGIN
  DECLARE @mStmt varchar(2000)
  DECLARE @mCond varchar(500)
  DECLARE @mOrder varchar(500)

  SET @mStmt = 'select top ' + convert(varchar(10),@Param_Count) + ' ' + @Param_FieldList + ' from [dbo].[ut_' + @Param_TableName + '_Keys] k'
             + ' inner join [dbo].[ut_' + @Param_TableName + '] t on t.RecId = k.DataRecId'
  SET @mCond = ''
  SET @mOrder = ''

  IF @Param_desc <> 0 BEGIN
    SET @mOrder = 'order by ' + @Param_KeyName + '_Value desc'
	IF @Param_From <> 0	BEGIN
		SET @mCond = 'where k.' + @Param_KeyName + '_Value < (select ' + @Param_KeyName + '_Value from [dbo].[ut_' + @Param_TableName + '_Keys] where DataRecId = ' + convert(varchar(10),@Param_From) + ')'
	END
  END ELSE BEGIN
	IF @Param_From <> 0	BEGIN
		SET @mCond = 'where k.' + @Param_KeyName + '_Value > (select ' + @Param_KeyName + '_Value from [dbo].[ut_' + @Param_TableName + '_Keys] where DataRecId = ' + convert(varchar(10),@Param_From) + ')'
	END
    SET @mOrder = 'order by ' + @Param_KeyName + '_Value'
  END

  IF @Param_withFilter <> 0 BEGIN
    IF LEN(@mCond) > 0 BEGIN
	  SET @mCond = @mCond + ' and k.' + @Param_KeyName + '_Filter = 1'
	END	ELSE BEGIN
	  SET @mCond = 'where k.' + @Param_KeyName + '_Filter = 1'
	END
  END

  IF LEN(@Param_additional_Filter) <> 0 BEGIN
    IF LEN(@mCond) > 0 BEGIN
	  SET @mCond = @mCond + ' and (' + @Param_additional_Filter + ')'
	END	ELSE BEGIN
	  SET @mCond = @mCond + ' where (' + @Param_additional_Filter + ')'
	END
  END

  SET @mStmt = @mStmt + char(10) + @mCond + char(10) + @mOrder
  RETURN @mStmt
END
GO

IF OBJECT_ID (N'itcproc_select', N'P') IS NOT NULL
    DROP PROCEDURE dbo.itcproc_select
GO
CREATE PROCEDURE dbo.itcproc_select 
   @Param_TableName varchar(50)
  ,@Param_KeyName varchar(50)
  ,@Param_FieldList varchar(50)
  ,@Param_Count integer
  ,@Param_From integer
  ,@Param_desc tinyint
  ,@Param_withFilter tinyint
  ,@Param_additional_Filter varchar(1000)
AS
  DECLARE @mStmt varchar(2000)
  SET @mStmt = dbo.itcfunc_generate_select(@Param_TableName,@Param_KeyName,@Param_FieldList,@Param_Count,@Param_From,@Param_desc,@Param_withFilter,@Param_additional_Filter)
  EXECUTE(@mStmt)
GO

