-- 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 0 = (SELECT count(*) from [sysobjects] where ([xtype] = 'U' or [xtype] = 'V') and [name] = 'ut_Lohnarten')
BEGIN
CREATE TABLE [dbo].[ut_Lohnarten]
(
  [RecId]  integer NOT NULL
 ,[lohnart] char(3) DEFAULT ''
 ,[abrgruppe] char(1) DEFAULT ''
 ,[lohnarttxt] char(50) DEFAULT ''
 ,[baustunden] integer DEFAULT 0
 ,[bsoll] integer DEFAULT 0
 ,[btagweise] integer DEFAULT 0
 ,[bjahrsamm] integer DEFAULT 0
 ,[bspeicher1] integer DEFAULT 0
 ,[bspeicher2] integer DEFAULT 0
 ,[bspeicher3] integer DEFAULT 0
 ,[bspeicher4] integer DEFAULT 0
 ,[bspeicher5] integer DEFAULT 0
 ,[bspeicher6] integer DEFAULT 0
 ,[bspeicher7] integer DEFAULT 0
 ,[bspeicher8] integer DEFAULT 0
 ,[bspeicher9] integer DEFAULT 0
 ,[bspeicher0] integer DEFAULT 0
 ,[vkz] integer DEFAULT 0
 ,[stdloa] integer DEFAULT 0
 ,[bnurtag] integer DEFAULT 0
 ,[produktive] integer DEFAULT 0
 ,[ekfaktor] float DEFAULT 0
 ,[vkfaktor] float DEFAULT 0
 ,[SachKonto] char(8) DEFAULT ''
 ,[ZeitKonto] char(8) DEFAULT ''
 ,[Betrag] float DEFAULT 0
  CONSTRAINT [Pk_Lohnarten]  PRIMARY KEY NONCLUSTERED ( [RecId] )
)
END
GO

-- drop obsolete triggers and the triggered table with keyvalues
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'TR'   and [name] = 'tr_Lohnarten_KeyDel')
  DROP TRIGGER [dbo].[tr_Lohnarten_KeyDel]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'TR'   and [name] = 'tr_Lohnarten_KeyUpd')
  DROP TRIGGER [dbo].[tr_Lohnarten_KeyUpd]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'U' and [name] = 'ut_Lohnarten_Keys')
  DROP TABLE [dbo].[ut_Lohnarten_Keys]
GO

-- add new fields
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Lohnarten' and sc.[name] = 'lohnart')
BEGIN
  ALTER TABLE [dbo].[ut_Lohnarten] ADD [lohnart] char(3) NOT NULL DEFAULT ''
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Lohnarten' and sc.[name] = 'abrgruppe')
BEGIN
  ALTER TABLE [dbo].[ut_Lohnarten] ADD [abrgruppe] char(1) NOT NULL DEFAULT ''
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Lohnarten' and sc.[name] = 'lohnarttxt')
BEGIN
  ALTER TABLE [dbo].[ut_Lohnarten] ADD [lohnarttxt] char(50) NOT NULL DEFAULT ''
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Lohnarten' and sc.[name] = 'baustunden')
BEGIN
  ALTER TABLE [dbo].[ut_Lohnarten] ADD [baustunden] integer NOT NULL DEFAULT 0
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Lohnarten' and sc.[name] = 'bsoll')
BEGIN
  ALTER TABLE [dbo].[ut_Lohnarten] ADD [bsoll] integer NOT NULL DEFAULT 0
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Lohnarten' and sc.[name] = 'btagweise')
BEGIN
  ALTER TABLE [dbo].[ut_Lohnarten] ADD [btagweise] integer NOT NULL DEFAULT 0
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Lohnarten' and sc.[name] = 'bjahrsamm')
BEGIN
  ALTER TABLE [dbo].[ut_Lohnarten] ADD [bjahrsamm] integer NOT NULL DEFAULT 0
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Lohnarten' and sc.[name] = 'bspeicher1')
BEGIN
  ALTER TABLE [dbo].[ut_Lohnarten] ADD [bspeicher1] integer NOT NULL DEFAULT 0
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Lohnarten' and sc.[name] = 'bspeicher2')
BEGIN
  ALTER TABLE [dbo].[ut_Lohnarten] ADD [bspeicher2] integer NOT NULL DEFAULT 0
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Lohnarten' and sc.[name] = 'bspeicher3')
BEGIN
  ALTER TABLE [dbo].[ut_Lohnarten] ADD [bspeicher3] integer NOT NULL DEFAULT 0
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Lohnarten' and sc.[name] = 'bspeicher4')
BEGIN
  ALTER TABLE [dbo].[ut_Lohnarten] ADD [bspeicher4] integer NOT NULL DEFAULT 0
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Lohnarten' and sc.[name] = 'bspeicher5')
BEGIN
  ALTER TABLE [dbo].[ut_Lohnarten] ADD [bspeicher5] integer NOT NULL DEFAULT 0
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Lohnarten' and sc.[name] = 'bspeicher6')
BEGIN
  ALTER TABLE [dbo].[ut_Lohnarten] ADD [bspeicher6] integer NOT NULL DEFAULT 0
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Lohnarten' and sc.[name] = 'bspeicher7')
BEGIN
  ALTER TABLE [dbo].[ut_Lohnarten] ADD [bspeicher7] integer NOT NULL DEFAULT 0
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Lohnarten' and sc.[name] = 'bspeicher8')
BEGIN
  ALTER TABLE [dbo].[ut_Lohnarten] ADD [bspeicher8] integer NOT NULL DEFAULT 0
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Lohnarten' and sc.[name] = 'bspeicher9')
BEGIN
  ALTER TABLE [dbo].[ut_Lohnarten] ADD [bspeicher9] integer NOT NULL DEFAULT 0
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Lohnarten' and sc.[name] = 'bspeicher0')
BEGIN
  ALTER TABLE [dbo].[ut_Lohnarten] ADD [bspeicher0] integer NOT NULL DEFAULT 0
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Lohnarten' and sc.[name] = 'vkz')
BEGIN
  ALTER TABLE [dbo].[ut_Lohnarten] ADD [vkz] integer NOT NULL DEFAULT 0
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Lohnarten' and sc.[name] = 'stdloa')
BEGIN
  ALTER TABLE [dbo].[ut_Lohnarten] ADD [stdloa] integer NOT NULL DEFAULT 0
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Lohnarten' and sc.[name] = 'bnurtag')
BEGIN
  ALTER TABLE [dbo].[ut_Lohnarten] ADD [bnurtag] integer NOT NULL DEFAULT 0
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Lohnarten' and sc.[name] = 'produktive')
BEGIN
  ALTER TABLE [dbo].[ut_Lohnarten] ADD [produktive] integer NOT NULL DEFAULT 0
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Lohnarten' and sc.[name] = 'ekfaktor')
BEGIN
  ALTER TABLE [dbo].[ut_Lohnarten] ADD [ekfaktor] float NOT NULL DEFAULT 0
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Lohnarten' and sc.[name] = 'vkfaktor')
BEGIN
  ALTER TABLE [dbo].[ut_Lohnarten] ADD [vkfaktor] float NOT NULL DEFAULT 0
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Lohnarten' and sc.[name] = 'SachKonto')
BEGIN
  ALTER TABLE [dbo].[ut_Lohnarten] ADD [SachKonto] char(8) NOT NULL DEFAULT ''
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Lohnarten' and sc.[name] = 'ZeitKonto')
BEGIN
  ALTER TABLE [dbo].[ut_Lohnarten] ADD [ZeitKonto] char(8) NOT NULL DEFAULT ''
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Lohnarten' and sc.[name] = 'Betrag')
BEGIN
  ALTER TABLE [dbo].[ut_Lohnarten] ADD [Betrag] float NOT NULL DEFAULT 0
END
GO

-- check lengths of fields
IF 3 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Lohnarten' and sc.[name] = 'lohnart')
BEGIN
   ALTER TABLE [dbo].[ut_Lohnarten] ALTER COLUMN [lohnart] char(3)
END
GO
IF 1 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Lohnarten' and sc.[name] = 'abrgruppe')
BEGIN
   ALTER TABLE [dbo].[ut_Lohnarten] ALTER COLUMN [abrgruppe] char(1)
END
GO
IF 50 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Lohnarten' and sc.[name] = 'lohnarttxt')
BEGIN
   ALTER TABLE [dbo].[ut_Lohnarten] ALTER COLUMN [lohnarttxt] char(50)
END
GO
IF 8 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Lohnarten' and sc.[name] = 'SachKonto')
BEGIN
   ALTER TABLE [dbo].[ut_Lohnarten] ALTER COLUMN [SachKonto] char(8)
END
GO
IF 8 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Lohnarten' and sc.[name] = 'ZeitKonto')
BEGIN
   ALTER TABLE [dbo].[ut_Lohnarten] ALTER COLUMN [ZeitKonto] char(8)
END
GO

-- create dedicated view
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'V' and [name] = 'Lohnarten')
  DROP VIEW [dbo].[Lohnarten]
GO
CREATE VIEW [dbo].[Lohnarten] AS
  SELECT [RecId]
 ,[lohnart]
 ,[abrgruppe]
 ,[lohnarttxt]
 ,[baustunden]
 ,[bsoll]
 ,[btagweise]
 ,[bjahrsamm]
 ,[bspeicher1]
 ,[bspeicher2]
 ,[bspeicher3]
 ,[bspeicher4]
 ,[bspeicher5]
 ,[bspeicher6]
 ,[bspeicher7]
 ,[bspeicher8]
 ,[bspeicher9]
 ,[bspeicher0]
 ,[vkz]
 ,[stdloa]
 ,[bnurtag]
 ,[produktive]
 ,[ekfaktor]
 ,[vkfaktor]
 ,[SachKonto]
 ,[ZeitKonto]
 ,[Betrag]
  FROM [dbo].[ut_Lohnarten]
GO


-- create function to get the next RecId
IF OBJECT_ID (N'itcfunc_NextRecId_Lohnarten', N'FN') IS NOT NULL
  DROP FUNCTION dbo.itcfunc_NextRecId_Lohnarten
GO
CREATE FUNCTION dbo.itcfunc_NextRecId_Lohnarten() RETURNS integer
AS
BEGIN
  RETURN isnull((SELECT MAX(RecId) FROM [dbo].[ut_Lohnarten] WITH (READUNCOMMITTED)),0) + 1
END
GO

-- select with classic client
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Sel_Lohnarten')
  DROP PROCEDURE [dbo].[up_Sel_Lohnarten]
GO
CREATE PROCEDURE [dbo].[up_Sel_Lohnarten]  @mRI integer
AS
  SELECT
    [lohnart]
   ,[abrgruppe]
   ,[lohnarttxt]
   ,[baustunden]
   ,[bsoll]
   ,[btagweise]
   ,[bjahrsamm]
   ,[bspeicher1]
   ,[bspeicher2]
   ,[bspeicher3]
   ,[bspeicher4]
   ,[bspeicher5]
   ,[bspeicher6]
   ,[bspeicher7]
   ,[bspeicher8]
   ,[bspeicher9]
   ,[bspeicher0]
   ,[vkz]
   ,[stdloa]
   ,[bnurtag]
   ,[produktive]
   ,[ekfaktor]
   ,[vkfaktor]
   ,[SachKonto]
   ,[ZeitKonto]
   ,[Betrag]
  FROM [dbo].[ut_Lohnarten] WHERE RecId = @mRI
GO

-- select with modern clients
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Sel2_Lohnarten')
  DROP PROCEDURE [dbo].[up_Sel2_Lohnarten]
GO
CREATE PROCEDURE [dbo].[up_Sel2_Lohnarten]  @Param_RecId varchar(20)
AS
  DECLARE @mRI integer = dbo.itcfunc_integer(@Param_RecId)
  SELECT * FROM [dbo].[Lohnarten] WHERE RecId = @mRI
GO

-- insert with classic client
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Append_Lohnarten')
  DROP PROCEDURE [dbo].[up_Append_Lohnarten]
GO
CREATE PROCEDURE [dbo].[up_Append_Lohnarten]
   @Param_lohnart char(3)
  ,@Param_abrgruppe char(1)
  ,@Param_lohnarttxt char(50)
  ,@Param_baustunden integer
  ,@Param_bsoll integer
  ,@Param_btagweise integer
  ,@Param_bjahrsamm integer
  ,@Param_bspeicher1 integer
  ,@Param_bspeicher2 integer
  ,@Param_bspeicher3 integer
  ,@Param_bspeicher4 integer
  ,@Param_bspeicher5 integer
  ,@Param_bspeicher6 integer
  ,@Param_bspeicher7 integer
  ,@Param_bspeicher8 integer
  ,@Param_bspeicher9 integer
  ,@Param_bspeicher0 integer
  ,@Param_vkz integer
  ,@Param_stdloa integer
  ,@Param_bnurtag integer
  ,@Param_produktive integer
  ,@Param_ekfaktor float
  ,@Param_vkfaktor float
  ,@Param_SachKonto char(8)
  ,@Param_ZeitKonto char(8)
  ,@Param_Betrag float
AS
  DECLARE @NeueId integer
  DECLARE @m_CountDown integer = 10
  WHILE @m_CountDown > 0 BEGIN
    BEGIN TRANSACTION
    BEGIN TRY
      SET @NeueId = dbo.itcfunc_NextRecId_Lohnarten()
      INSERT INTO [dbo].[ut_Lohnarten] (RecId
        ,[lohnart]
        ,[abrgruppe]
        ,[lohnarttxt]
        ,[baustunden]
        ,[bsoll]
        ,[btagweise]
        ,[bjahrsamm]
        ,[bspeicher1]
        ,[bspeicher2]
        ,[bspeicher3]
        ,[bspeicher4]
        ,[bspeicher5]
        ,[bspeicher6]
        ,[bspeicher7]
        ,[bspeicher8]
        ,[bspeicher9]
        ,[bspeicher0]
        ,[vkz]
        ,[stdloa]
        ,[bnurtag]
        ,[produktive]
        ,[ekfaktor]
        ,[vkfaktor]
        ,[SachKonto]
        ,[ZeitKonto]
        ,[Betrag]
          ) VALUES (@NeueId
        ,UPPER(RTRIM(@Param_lohnart))
        ,UPPER(RTRIM(@Param_abrgruppe))
        ,RTRIM(@Param_lohnarttxt)
        ,@Param_baustunden
        ,@Param_bsoll
        ,@Param_btagweise
        ,@Param_bjahrsamm
        ,@Param_bspeicher1
        ,@Param_bspeicher2
        ,@Param_bspeicher3
        ,@Param_bspeicher4
        ,@Param_bspeicher5
        ,@Param_bspeicher6
        ,@Param_bspeicher7
        ,@Param_bspeicher8
        ,@Param_bspeicher9
        ,@Param_bspeicher0
        ,@Param_vkz
        ,@Param_stdloa
        ,@Param_bnurtag
        ,@Param_produktive
        ,@Param_ekfaktor
        ,@Param_vkfaktor
        ,UPPER(RTRIM(@Param_SachKonto))
        ,UPPER(RTRIM(@Param_ZeitKonto))
        ,@Param_Betrag
          )
      COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
      ROLLBACK TRANSACTION
      SET @NeueId = 0
    END CATCH

    IF @NeueId <> 0
      SET @m_CountDown = 0
    ELSE
      SET @m_CountDown = @m_CountDown - 1

    IF @m_CountDown > 0
      WAITFOR DELAY '00:00:00.050'
  END -- WHILE
  SELECT @NeueId AS RecId
GO

-- insert with obsolete clients
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Add_Lohnarten')
  DROP PROCEDURE [dbo].[up_Add_Lohnarten]
GO
CREATE PROCEDURE [dbo].[up_Add_Lohnarten]
AS
  DECLARE @NeueId integer = dbo.itcfunc_NextRecId_Lohnarten()
  INSERT INTO [dbo].[ut_Lohnarten] (RecId) VALUES( @NeueId )
  IF @@ERROR = 0
    SELECT @NeueId AS RecId
  ELSE
    SELECT 0 AS RecId
GO

-- update with classic client
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Upd_Lohnarten')
  DROP PROCEDURE [dbo].[up_Upd_Lohnarten]
GO
CREATE PROCEDURE [dbo].[up_Upd_Lohnarten] @mRI integer
  ,@Param_lohnart char(3)
  ,@Param_abrgruppe char(1)
  ,@Param_lohnarttxt char(50)
  ,@Param_baustunden integer
  ,@Param_bsoll integer
  ,@Param_btagweise integer
  ,@Param_bjahrsamm integer
  ,@Param_bspeicher1 integer
  ,@Param_bspeicher2 integer
  ,@Param_bspeicher3 integer
  ,@Param_bspeicher4 integer
  ,@Param_bspeicher5 integer
  ,@Param_bspeicher6 integer
  ,@Param_bspeicher7 integer
  ,@Param_bspeicher8 integer
  ,@Param_bspeicher9 integer
  ,@Param_bspeicher0 integer
  ,@Param_vkz integer
  ,@Param_stdloa integer
  ,@Param_bnurtag integer
  ,@Param_produktive integer
  ,@Param_ekfaktor float
  ,@Param_vkfaktor float
  ,@Param_SachKonto char(8)
  ,@Param_ZeitKonto char(8)
  ,@Param_Betrag float
AS
  UPDATE [dbo].[ut_Lohnarten] SET
    [lohnart] = UPPER(RTRIM(@Param_lohnart))
   ,[abrgruppe] = UPPER(RTRIM(@Param_abrgruppe))
   ,[lohnarttxt] = RTRIM(@Param_lohnarttxt)
   ,[baustunden] = @Param_baustunden
   ,[bsoll] = @Param_bsoll
   ,[btagweise] = @Param_btagweise
   ,[bjahrsamm] = @Param_bjahrsamm
   ,[bspeicher1] = @Param_bspeicher1
   ,[bspeicher2] = @Param_bspeicher2
   ,[bspeicher3] = @Param_bspeicher3
   ,[bspeicher4] = @Param_bspeicher4
   ,[bspeicher5] = @Param_bspeicher5
   ,[bspeicher6] = @Param_bspeicher6
   ,[bspeicher7] = @Param_bspeicher7
   ,[bspeicher8] = @Param_bspeicher8
   ,[bspeicher9] = @Param_bspeicher9
   ,[bspeicher0] = @Param_bspeicher0
   ,[vkz] = @Param_vkz
   ,[stdloa] = @Param_stdloa
   ,[bnurtag] = @Param_bnurtag
   ,[produktive] = @Param_produktive
   ,[ekfaktor] = @Param_ekfaktor
   ,[vkfaktor] = @Param_vkfaktor
   ,[SachKonto] = UPPER(RTRIM(@Param_SachKonto))
   ,[ZeitKonto] = UPPER(RTRIM(@Param_ZeitKonto))
   ,[Betrag] = @Param_Betrag
  WHERE RecId = @mRI
GO

-- update with modern clients
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Upd2_Lohnarten')
  DROP PROCEDURE [dbo].[up_Upd2_Lohnarten]
GO
CREATE PROCEDURE [dbo].[up_Upd2_Lohnarten] @Param_RecId varchar(20)
  ,@Param_lohnart varchar(3)
  ,@Param_abrgruppe varchar(1)
  ,@Param_lohnarttxt varchar(50)
  ,@Param_baustunden varchar(1)
  ,@Param_bsoll varchar(1)
  ,@Param_btagweise varchar(1)
  ,@Param_bjahrsamm varchar(1)
  ,@Param_bspeicher1 varchar(1)
  ,@Param_bspeicher2 varchar(1)
  ,@Param_bspeicher3 varchar(1)
  ,@Param_bspeicher4 varchar(1)
  ,@Param_bspeicher5 varchar(1)
  ,@Param_bspeicher6 varchar(1)
  ,@Param_bspeicher7 varchar(1)
  ,@Param_bspeicher8 varchar(1)
  ,@Param_bspeicher9 varchar(1)
  ,@Param_bspeicher0 varchar(1)
  ,@Param_vkz varchar(1)
  ,@Param_stdloa varchar(1)
  ,@Param_bnurtag varchar(1)
  ,@Param_produktive varchar(1)
  ,@Param_ekfaktor varchar(6)
  ,@Param_vkfaktor varchar(6)
  ,@Param_SachKonto varchar(8)
  ,@Param_ZeitKonto varchar(8)
  ,@Param_Betrag varchar(8)
AS
  DECLARE @mRI integer = dbo.itcfunc_integer(@Param_RecId)
  UPDATE [dbo].[ut_Lohnarten] SET
    [lohnart] = UPPER(RTRIM(@Param_lohnart))
   ,[abrgruppe] = UPPER(RTRIM(@Param_abrgruppe))
   ,[lohnarttxt] = RTRIM(@Param_lohnarttxt)
   ,[baustunden] = dbo.itcfunc_integer(@Param_baustunden)
   ,[bsoll] = dbo.itcfunc_integer(@Param_bsoll)
   ,[btagweise] = dbo.itcfunc_integer(@Param_btagweise)
   ,[bjahrsamm] = dbo.itcfunc_integer(@Param_bjahrsamm)
   ,[bspeicher1] = dbo.itcfunc_integer(@Param_bspeicher1)
   ,[bspeicher2] = dbo.itcfunc_integer(@Param_bspeicher2)
   ,[bspeicher3] = dbo.itcfunc_integer(@Param_bspeicher3)
   ,[bspeicher4] = dbo.itcfunc_integer(@Param_bspeicher4)
   ,[bspeicher5] = dbo.itcfunc_integer(@Param_bspeicher5)
   ,[bspeicher6] = dbo.itcfunc_integer(@Param_bspeicher6)
   ,[bspeicher7] = dbo.itcfunc_integer(@Param_bspeicher7)
   ,[bspeicher8] = dbo.itcfunc_integer(@Param_bspeicher8)
   ,[bspeicher9] = dbo.itcfunc_integer(@Param_bspeicher9)
   ,[bspeicher0] = dbo.itcfunc_integer(@Param_bspeicher0)
   ,[vkz] = dbo.itcfunc_integer(@Param_vkz)
   ,[stdloa] = dbo.itcfunc_integer(@Param_stdloa)
   ,[bnurtag] = dbo.itcfunc_integer(@Param_bnurtag)
   ,[produktive] = dbo.itcfunc_integer(@Param_produktive)
   ,[ekfaktor] = dbo.itcfunc_float(@Param_ekfaktor)
   ,[vkfaktor] = dbo.itcfunc_float(@Param_vkfaktor)
   ,[SachKonto] = UPPER(RTRIM(@Param_SachKonto))
   ,[ZeitKonto] = UPPER(RTRIM(@Param_ZeitKonto))
   ,[Betrag] = dbo.itcfunc_float(@Param_Betrag)
  WHERE RecId = @mRI

  EXECUTE [dbo].[up_Sel2_Lohnarten] @mRI
GO

-- delete
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Del_Lohnarten')
  DROP PROCEDURE [dbo].[up_Del_Lohnarten]
GO
CREATE PROCEDURE [dbo].[up_Del_Lohnarten] @mRI integer
AS
  DELETE FROM [dbo].[ut_Lohnarten] WHERE RecId = @mRI
GO

-- text index
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_TrSel_Lohnarten')
  DROP PROCEDURE [dbo].[up_TrSel_Lohnarten]
GO
CREATE PROCEDURE [dbo].[up_TrSel_Lohnarten]
AS
  SELECT RecId,
    LTRIM(RTRIM([lohnart]))
   +LTRIM(RTRIM([abrgruppe]))
   +LTRIM(RTRIM([lohnarttxt]))
   +LTRIM(RTRIM([SachKonto]))
   +LTRIM(RTRIM([ZeitKonto]))
  AS Data FROM [dbo].[ut_Lohnarten] WHERE RecId > 0 ORDER BY RecId
GO

-- reindex
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Reorg_Lohnarten')
  DROP PROCEDURE [dbo].[up_Reorg_Lohnarten]
GO
CREATE PROCEDURE [dbo].[up_Reorg_Lohnarten] @bDbcc integer,@bRecId integer
AS
  IF (@bRecId = 1)
  BEGIN
    DECLARE  C        CURSOR FOR SELECT RecId FROM [dbo].[ut_Lohnarten] ORDER BY RecId
    DECLARE  @CurrId  integer
    DECLARE  @NextId  integer
    --BEGIN TRANSACTION
    OPEN C
    SET @NextId = 1
    FETCH NEXT FROM C INTO @CurrId
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
      IF @CurrId <> @NextId
      UPDATE [dbo].[ut_Lohnarten] SET RecId=@NextId WHERE CURRENT OF C
      SET @NextId = @NextId + 1
      FETCH NEXT FROM C INTO @CurrId
    END
    CLOSE C
    --COMMIT TRANSACTION
    DEALLOCATE C
  END

  IF (@bDbcc = 1)
  BEGIN
    DBCC DBREINDEX ( [ut_Lohnarten] )
  END
GO

-- logical key  ORDER BY lohnart,RecId
IF 0 < (select count(*) from sysindexes si inner join sysobjects so on so.[xtype] = 'U' and so.[name] = 'ut_Lohnarten' and so.[id] = si.[id] and si.[name] = 'Ik_Key0')
  DROP INDEX [Ik_Key0] ON [dbo].[ut_Lohnarten]
GO
IF 0 = (select count(*) from sysindexes si inner join sysobjects so on so.[xtype] = 'U' and so.[name] = 'ut_Lohnarten' and so.[id] = si.[id] and si.[name] = 'Ik_Key0')
  CREATE INDEX [Ik_Key0] ON [dbo].[ut_Lohnarten] (lohnart,RecId)
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_First_Lohnarten_Key0')
  DROP PROCEDURE [dbo].[up_First_Lohnarten_Key0]
GO
CREATE PROCEDURE [dbo].[up_First_Lohnarten_Key0] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_Lohnarten] WITH(INDEX = Ik_Key0) ORDER BY lohnart,RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_First_Lohnarten_Key0')
  EXEC('ALTER PROCEDURE [dbo].[qp_First_Lohnarten_Key0] AS EXECUTE [dbo].[up_First_Lohnarten_Key0]')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Last_Lohnarten_Key0')
  DROP PROCEDURE [dbo].[up_Last_Lohnarten_Key0]
GO
CREATE PROCEDURE [dbo].[up_Last_Lohnarten_Key0] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_Lohnarten] WITH(INDEX = Ik_Key0) ORDER BY lohnart desc,RecId desc
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Last_Lohnarten_Key0')
  EXEC('ALTER PROCEDURE [dbo].[qp_Last_Lohnarten_Key0] AS EXECUTE [dbo].[up_Last_Lohnarten_Key0]')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next_Lohnarten_Key0')
  DROP PROCEDURE [dbo].[up_Next_Lohnarten_Key0]
GO
CREATE PROCEDURE [dbo].[up_Next_Lohnarten_Key0] @oldRecId integer AS
  DECLARE @oldlohnart char(3)
  SELECT @oldlohnart = lohnart FROM [dbo].[ut_Lohnarten] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Lohnarten] WITH(INDEX = Ik_Key0)
    WHERE (lohnart > @oldlohnart)
    ORDER BY lohnart,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT 0 AS RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Next_Lohnarten_Key0')
  EXEC('ALTER PROCEDURE [dbo].[qp_Next_Lohnarten_Key0] @oldRecId integer AS EXECUTE [dbo].[up_Next_Lohnarten_Key0] @oldRecId')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior_Lohnarten_Key0')
  DROP PROCEDURE [dbo].[up_Prior_Lohnarten_Key0]
GO
CREATE PROCEDURE [dbo].[up_Prior_Lohnarten_Key0] @oldRecId integer AS
  DECLARE @oldlohnart char(3)
  SELECT @oldlohnart = lohnart FROM [dbo].[ut_Lohnarten] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Lohnarten] WITH(INDEX = Ik_Key0)
    WHERE (lohnart < @oldlohnart)
    ORDER BY lohnart desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT 0 AS RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Prior_Lohnarten_Key0')
  EXEC('ALTER PROCEDURE [dbo].[qp_Prior_Lohnarten_Key0] @oldRecId integer AS EXECUTE [dbo].[up_Prior_Lohnarten_Key0] @oldRecId')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next50_Lohnarten_Key0')
  DROP PROCEDURE [dbo].[up_Next50_Lohnarten_Key0]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Next50_Lohnarten_Key0')
  DROP PROCEDURE [dbo].[qp_Next50_Lohnarten_Key0]
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior50_Lohnarten_Key0')
  DROP PROCEDURE [dbo].[up_Prior50_Lohnarten_Key0]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Prior50_Lohnarten_Key0')
  DROP PROCEDURE [dbo].[qp_Prior50_Lohnarten_Key0]
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindF_Lohnarten_Key0')
  DROP PROCEDURE [dbo].[up_FindF_Lohnarten_Key0]
GO
CREATE PROCEDURE [dbo].[up_FindF_Lohnarten_Key0] @xKey char(250), @xLen integer AS
  DECLARE @mlohnart char(3) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,1,3)

  -- pass 1 -- Abfrage mit vollständigen Feldern (absteigende Vergleichslänge)

  IF @xLen = 3 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Lohnarten] WITH(INDEX = Ik_Key0) WHERE 
      lohnart = @mlohnart
      ORDER BY lohnart,RecId
    RETURN
  END

  -- pass 2 -- Abfrage mit teilweise unkompletten Felder (aufsteigende Vergleichslänge)

  IF @xLen < 3 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Lohnarten] WITH(INDEX = Ik_Key0) WHERE 
      lohnart >= @mlohnart AND SUBSTRING(lohnart,1,LEN(@mlohnart)) = @mlohnart
      ORDER BY lohnart,RecId
    RETURN
  END

  SELECT 0 AS RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_FindF_Lohnarten_Key0')
  EXEC('ALTER PROCEDURE [dbo].[qp_FindF_Lohnarten_Key0] @xKey char(250), @xLen integer AS EXECUTE [dbo].[up_FindF_Lohnarten_Key0] @xKey, @xLen')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindL_Lohnarten_Key0')
  DROP PROCEDURE [dbo].[up_FindL_Lohnarten_Key0]
GO
CREATE PROCEDURE [dbo].[up_FindL_Lohnarten_Key0] @xKey char(250), @xLen integer AS
  DECLARE @mlohnart char(3) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,1,3)

  -- pass 1 -- Abfrage mit vollständigen Feldern (absteigende Vergleichslänge)

  IF @xLen = 3 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Lohnarten] WITH(INDEX = Ik_Key0) WHERE 
      lohnart = @mlohnart
      ORDER BY lohnart desc,RecId desc
    RETURN
  END

  -- pass 2 -- Abfrage mit teilweise unkompletten Felder (aufsteigende Vergleichslänge)

  IF @xLen < 3 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Lohnarten] WITH(INDEX = Ik_Key0) WHERE 
      SUBSTRING(lohnart,1,LEN(@mlohnart)) = @mlohnart
      ORDER BY lohnart desc,RecId desc
    RETURN
  END

  SELECT 0 AS RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_FindL_Lohnarten_Key0')
  EXEC('ALTER PROCEDURE [dbo].[qp_FindL_Lohnarten_Key0] @xKey char(250), @xLen integer AS EXECUTE [dbo].[up_FindL_Lohnarten_Key0] @xKey, @xLen')
GO


