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

----------------------------------------------------------------------------------------------
--
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'V' and [name] = 'itcview_ZeitKladdenZeile')
  DROP VIEW [dbo].[itcview_ZeitKladdenZeile]
GO
CREATE VIEW [dbo].[itcview_ZeitKladdenZeile] AS
  select z.RecId,z.Code,z.LfdNr,z.Annr,
         convert(varchar(10),z.BuchDatum,120) as BuchDatum,convert(varchar(10),z.ErfDatum,120) as ErfDatum,z.Beginn,z.Ende,z.AbzugStd,z.Stunden,z.Betrag,z.EkLohn,z.Brutto,
         z.PrjNr,z.BelArt,z.BelNr,z.ExtBelNr,z.Kst,z.Ktr,z.BuText,z.Herkunft,z.Gesperrt,z.GebZeitw,z.Loa,z.Vorgang,z.ServiceBer, 
		 ISNULL(a.Name1,'') AS Name1,
		 ISNULL(l.lohnarttxt,'') AS Lohnart
    from ut_ZeitKladdenZeile z 
    left join ut_Adressen  a WITH(INDEX(Ik_Key6)) on a.DESTTYP = ' 4' AND a.Annr = z.Annr
    left join ut_Lohnarten l WITH(INDEX(Ik_Key0)) on l.lohnart = z.Loa
GO

----------------------------------------------------------------------------------------------
-- ZeitKladde in Zeitwirtschaft verbuchen
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'itcproc_ZeitKladde_Buchen')
  DROP PROCEDURE [dbo].[itcproc_ZeitKladde_Buchen]
GO
CREATE PROCEDURE [dbo].[itcproc_ZeitKladde_Buchen] @param_Code char(20), @param_LfdNr integer = 0
AS
  BEGIN TRANSACTION
  SET NOCOUNT ON

  DECLARE @m_RecId      integer
  DECLARE @m_BuchDatum  datetime
  DECLARE @m_Tag        char(2)
  DECLARE @m_Monat      char(2)
  DECLARE @m_Jahr       char(4)

  DECLARE C_Kladde CURSOR STATIC LOCAL FOR SELECT RecId, BuchDatum from ut_ZeitKladdenZeile 
    WHERE Code = @param_Code and GebZeitw = 0 and (LfdNr = @param_LfdNr OR @param_LfdNr = 0) and Annr <> '' AND Loa <> '' AND (Stunden <> 0 OR Betrag <> 0)
	ORDER by LfdNr

  OPEN C_Kladde
  FETCH NEXT FROM C_Kladde INTO @m_RecId, @m_BuchDatum
  WHILE (@@FETCH_STATUS = 0) BEGIN
    SELECT @m_Tag = SUBSTRING(CONVERT(char(10), @m_BuchDatum, 120), 9, 2), @m_Monat = SUBSTRING(CONVERT(char(10), @m_BuchDatum, 120), 6, 2), @m_Jahr = SUBSTRING(CONVERT(char(10), @m_BuchDatum, 120), 1, 4)
    --print @m_Jahr + @m_Monat + @m_Tag
    INSERT INTO ut_Zeiterfassung (RecId, arbtnr, lohnart, tag, monat, jahr, stunden, betrag, wkz, eurokurs, eklohn, bruttolohn, bemerkung, PrjNr, art, belegnr, kstnr)
    SELECT dbo.itcfunc_NextRecId_Zeiterfassung(), Annr, Loa, @m_Tag, @m_Monat, @m_Jahr, Stunden, Betrag, 'EUR', 1, EkLohn, Brutto, BuText, PrjNr, BelArt, BelNr, Kst FROM ut_ZeitKladdenZeile WHERE RecId = @m_RecId
	UPDATE ut_ZeitKladdenZeile SET GebZeitw = 1, Gesperrt = 1 WHERE RecId = @m_RecId

    FETCH NEXT FROM C_Kladde INTO @m_RecId, @m_BuchDatum
  END

  CLOSE C_Kladde
  DEALLOCATE C_Kladde

  SET NOCOUNT OFF
  COMMIT TRANSACTION
GO

--------------------------------------------------------------------------------------------------------
-- Zeitbuchblattzeilen abrufen
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P' and [name] = 'itcproc_ZeitKladdenZeile_get')
  DROP PROCEDURE [dbo].[itcproc_ZeitKladdenZeile_get]
GO
CREATE PROCEDURE [dbo].[itcproc_ZeitKladdenZeile_get]
  @Parm_Code  varchar(20) = ''
 ,@Parm_LfdNr varchar(10) = '0'
 ,@Parm_All   varchar(10) = '0'        -- inclusive verbuchte Zeilen
 ,@Parm_ServiceBer varchar(20) = ''    -- Servicebericht
AS
  DECLARE @m_LfdNr integer = dbo.itcfunc_integer(@Parm_LfdNr)
  DECLARE @m_All   integer = dbo.itcfunc_integer(@Parm_All)

  -- nach Code + LfdNr
  If @m_LfdNr <> 0
    SELECT * FROM itcview_ZeitKladdenZeile WHERE Code = @Parm_Code AND LfdNr = @m_LfdNr ORDER BY Code, LfdNr
  -- Alle fuer einen Servicebericht
  ELSE If @Parm_ServiceBer <> ''
    SELECT * FROM itcview_ZeitKladdenZeile WHERE ServiceBer = @Parm_ServiceBer ORDER BY Annr, Code, LfdNr
  -- nach Code
  ELSE
    SELECT * FROM itcview_ZeitKladdenZeile WHERE Code = @Parm_Code AND Gesperrt <= @m_All ORDER BY Code, LfdNr
GO

--------------------------------------------------------------------------------------------------------
-- Zeitbuchblattzeile Kalkulieren
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P' and [name] = 'itcproc_ZeitKladdenZeile_calculate')
  DROP PROCEDURE [dbo].[itcproc_ZeitKladdenZeile_calculate]
GO
CREATE PROCEDURE [dbo].[itcproc_ZeitKladdenZeile_calculate]
  @Parm_RecId       integer
 ,@Parm_CalcStunden tinyint = 0
AS
  DECLARE @m_stdloa   integer
  DECLARE @m_Stunden  float
  DECLARE @m_AbzugStd float

  SELECT @m_stdloa = ISNULL(l.stdloa,0), @m_Stunden = ISNULL(z.Stunden,0), @m_AbzugStd = ISNULL(z.AbzugStd,0) FROM [dbo].[ut_ZeitKladdenZeile] z
    INNER JOIN [dbo].[ut_Lohnarten] l ON l.lohnart = z.Loa
	WHERE z.RecId = @Parm_RecId

  IF @m_stdloa = 0 BEGIN
    UPDATE [dbo].[ut_ZeitKladdenZeile] SET Stunden = 0, AbzugStd = 0 WHERE RecId = @Parm_RecId
	RETURN
  END ELSE BEGIN
    UPDATE [dbo].[ut_ZeitKladdenZeile] SET Betrag = 0 WHERE RecId = @Parm_RecId
	IF @m_Stunden = 0
	  SET @Parm_CalcStunden = 1
  END
  IF @Parm_CalcStunden = 0
    RETURN

  -- Beginn und Endezeit ermitteln
  DECLARE @m_Beginn varchar(5)
  DECLARE @m_Ende   varchar(5)
  SELECT @m_Beginn = ISNULL(Beginn,''), @m_Ende = ISNULL(Ende,'') FROM [dbo].[ut_ZeitKladdenZeile] WHERE RecId = @Parm_RecId
  IF @m_Ende <= @m_Beginn OR @m_Beginn = '' OR @m_Ende = '' BEGIN
    UPDATE [dbo].[ut_ZeitKladdenZeile] SET Stunden = 0, Ende = '' WHERE RecId = @Parm_RecId
    RETURN
  END

  DECLARE @m_h1 integer
  DECLARE @m_h2 integer
  DECLARE @m_m1 integer
  DECLARE @m_m2 integer
  SELECT @m_h1 = dbo.itcfunc_integer(SUBSTRING(@m_Beginn,1,2)), @m_m1 = dbo.itcfunc_integer(SUBSTRING(@m_Beginn,4,2))
        ,@m_h2 = dbo.itcfunc_integer(SUBSTRING(@m_Ende,1,2)), @m_m2 = dbo.itcfunc_integer(SUBSTRING(@m_Ende,4,2))
  SELECT @m_Stunden = ((60.00 * (@m_h2 - @m_h1)) + (@m_m2 - @m_m1)) / 60.00
  UPDATE [dbo].[ut_ZeitKladdenZeile] SET Stunden = @m_Stunden - @m_AbzugStd WHERE RecId = @Parm_RecId
GO

--------------------------------------------------------------------------------------------------------
-- Zeitbuchblattzeile Annr validieren
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P' and [name] = 'itcproc_ZeitKladdenZeile_validateAnnr')
  DROP PROCEDURE [dbo].[itcproc_ZeitKladdenZeile_validateAnnr]
GO
CREATE PROCEDURE [dbo].[itcproc_ZeitKladdenZeile_validateAnnr]
  @Parm_RecId integer
 ,@Parm_Annr varchar(4)
AS
  DECLARE @m_EkLohn     float
  DECLARE @m_BruttoLohn float
  DECLARE @m_Annr       varchar(4)
  SELECT @m_Annr = Annr, @m_EkLohn = EkLohn, @m_BruttoLohn = BruttoLohn FROM [dbo].[ut_Adressen] WHERE DESTTYP = ' 4' AND Annr = @Parm_Annr

  UPDATE [dbo].[ut_ZeitKladdenZeile] SET Annr = ISNULL(@m_Annr,''), EkLohn = ISNULL(@m_EkLohn,0), Brutto = ISNULL(@m_BruttoLohn,0) WHERE RecId = @Parm_RecId
  EXECUTE [dbo].[itcproc_ZeitKladdenZeile_calculate] @Parm_RecId
GO

--------------------------------------------------------------------------------------------------------
-- Zeitbuchblattzeile Loa validieren
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P' and [name] = 'itcproc_ZeitKladdenZeile_validateLoa')
  DROP PROCEDURE [dbo].[itcproc_ZeitKladdenZeile_validateLoa]
GO
CREATE PROCEDURE [dbo].[itcproc_ZeitKladdenZeile_validateLoa]
  @Parm_RecId integer
 ,@Parm_Loa   varchar(3)
AS
  DECLARE @m_Loa varchar(3)
  DECLARE @m_Betrag float
  DECLARE @m_stdloa integer
  SELECT @m_Loa = ISNULL(lohnart,''), @m_Betrag = Betrag, @m_stdloa = stdloa  FROM [dbo].[ut_Lohnarten] WHERE lohnart = @Parm_Loa

  IF RTRIM(@m_Loa) = '' BEGIN
    UPDATE [dbo].[ut_ZeitKladdenZeile] SET Loa = '', Betrag = 0 WHERE RecId = @Parm_RecId
  END ELSE IF @m_stdloa <> 0 BEGIN
    UPDATE [dbo].[ut_ZeitKladdenZeile] SET Loa = @m_Loa, Betrag = 0 WHERE RecId = @Parm_RecId
  END ELSE BEGIN
    UPDATE [dbo].[ut_ZeitKladdenZeile] SET Loa = @m_Loa, Betrag = @m_Betrag WHERE RecId = @Parm_RecId
  END

  EXECUTE [dbo].[itcproc_ZeitKladdenZeile_calculate] @Parm_RecId
GO

--------------------------------------------------------------------------------------------------------
-- Zeitbuchblattzeile Stunden validieren
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P' and [name] = 'itcproc_ZeitKladdenZeile_validateStunden')
  DROP PROCEDURE [dbo].[itcproc_ZeitKladdenZeile_validateStunden]
GO
CREATE PROCEDURE [dbo].[itcproc_ZeitKladdenZeile_validateStunden]
  @Parm_RecId   integer
 ,@Parm_Stunden varchar(20)
AS
  DECLARE @m_Stunden float = dbo.itcfunc_float(@Parm_Stunden)
  DECLARE @m_EnStunden tinyint
  DECLARE @m_stdloa    tinyint

  SELECT @m_EnStunden = ISNULL(k.EnStunden,0), @m_stdloa = ISNULL(l.stdloa,0) FROM [dbo].[ut_ZeitKladdenZeile] z 
     LEFT JOIN [dbo].[ut_ZeitKladdenKopf] k   ON k.Code = z.Code 
	 LEFT JOIN [dbo].[ut_Lohnarten] l ON l.lohnart = z.Loa
	 WHERE z.RecId = @Parm_RecId
  IF @m_EnStunden = 0 OR @m_stdloa = 0
    RETURN 0

  IF @m_Stunden <  0 SET @m_Stunden =  0
  IF @m_Stunden > 24 SET @m_Stunden = 24
  UPDATE [dbo].[ut_ZeitKladdenZeile] SET Stunden = @m_Stunden, AbzugStd = 0 WHERE RecId = @Parm_RecId
  EXECUTE [dbo].[itcproc_ZeitKladdenZeile_calculate] @Parm_RecId
  RETURN 1
GO

--------------------------------------------------------------------------------------------------------
-- Zeitbuchblattzeile AbzugStd validieren
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P' and [name] = 'itcproc_ZeitKladdenZeile_validateAbzugStd')
  DROP PROCEDURE [dbo].[itcproc_ZeitKladdenZeile_validateAbzugStd]
GO
CREATE PROCEDURE [dbo].[itcproc_ZeitKladdenZeile_validateAbzugStd]
  @Parm_RecId    integer
 ,@Parm_AbzugStd varchar(20)
AS
  DECLARE @m_AbzugStd  float = dbo.itcfunc_float(@Parm_AbzugStd)
  DECLARE @m_EnStunden tinyint
  DECLARE @m_stdloa    tinyint

  SELECT @m_EnStunden = ISNULL(k.EnStunden,0), @m_stdloa = ISNULL(l.stdloa,0) FROM [dbo].[ut_ZeitKladdenZeile] z 
     LEFT JOIN [dbo].[ut_ZeitKladdenKopf] k   ON k.Code = z.Code 
	 LEFT JOIN [dbo].[ut_Lohnarten] l ON l.lohnart = z.Loa
	 WHERE z.RecId = @Parm_RecId
  IF @m_EnStunden = 0 OR @m_stdloa = 0
    RETURN 0

  IF @m_AbzugStd <  0 SET @m_AbzugStd =  0
  IF @m_AbzugStd > 24 SET @m_AbzugStd = 24
  UPDATE [dbo].[ut_ZeitKladdenZeile] SET AbzugStd = @m_AbzugStd WHERE RecId = @Parm_RecId
  EXECUTE [dbo].[itcproc_ZeitKladdenZeile_calculate] @Parm_RecId, 1
  RETURN 1
GO

--------------------------------------------------------------------------------------------------------
-- Zeitbuchblattzeile Betrag validieren
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P' and [name] = 'itcproc_ZeitKladdenZeile_validateBetrag')
  DROP PROCEDURE [dbo].[itcproc_ZeitKladdenZeile_validateBetrag]
GO
CREATE PROCEDURE [dbo].[itcproc_ZeitKladdenZeile_validateBetrag]
  @Parm_RecId   integer
 ,@Parm_Betrag varchar(20)
AS
  DECLARE @m_Betrag   float = dbo.itcfunc_float(@Parm_Betrag)
  DECLARE @m_EnBetrag tinyint
  DECLARE @m_stdloa   tinyint

  SELECT @m_EnBetrag = ISNULL(k.EnBetrag,0), @m_stdloa = ISNULL(l.stdloa,0) FROM [dbo].[ut_ZeitKladdenZeile] z 
     LEFT JOIN [dbo].[ut_ZeitKladdenKopf] k   ON k.Code = z.Code 
	 LEFT JOIN [dbo].[ut_Lohnarten] l ON l.lohnart = z.Loa
	 WHERE z.RecId = @Parm_RecId
  IF @m_EnBetrag = 0 OR @m_stdloa <> 0
    RETURN 0

  UPDATE [dbo].[ut_ZeitKladdenZeile] SET Betrag = @m_Betrag WHERE RecId = @Parm_RecId
  EXECUTE [dbo].[itcproc_ZeitKladdenZeile_calculate] @Parm_RecId
  RETURN 1
GO

--------------------------------------------------------------------------------------------------------
-- Zeitbuchblattzeile Beginn validieren
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P' and [name] = 'itcproc_ZeitKladdenZeile_validateBeginn')
  DROP PROCEDURE [dbo].[itcproc_ZeitKladdenZeile_validateBeginn]
GO
CREATE PROCEDURE [dbo].[itcproc_ZeitKladdenZeile_validateBeginn]
  @Parm_RecId   integer
 ,@Parm_Beginn  varchar(20)
AS
  UPDATE [dbo].[ut_ZeitKladdenZeile] SET Beginn = dbo.itcfunc_formatTime(@Parm_Beginn) WHERE RecId = @Parm_RecId
  EXECUTE [dbo].[itcproc_ZeitKladdenZeile_calculate] @Parm_RecId, 1
  RETURN 1
GO

--------------------------------------------------------------------------------------------------------
-- Zeitbuchblattzeile Ende validieren
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P' and [name] = 'itcproc_ZeitKladdenZeile_validateEnde')
  DROP PROCEDURE [dbo].[itcproc_ZeitKladdenZeile_validateEnde]
GO
CREATE PROCEDURE [dbo].[itcproc_ZeitKladdenZeile_validateEnde]
  @Parm_RecId   integer
 ,@Parm_Beginn  varchar(20)
AS
  UPDATE [dbo].[ut_ZeitKladdenZeile] SET Ende = dbo.itcfunc_formatTime(@Parm_Beginn) WHERE RecId = @Parm_RecId
  EXECUTE [dbo].[itcproc_ZeitKladdenZeile_calculate] @Parm_RecId, 1
  RETURN 1
GO

--------------------------------------------------------------------------------------------------------
-- Zeitbuchblattzeile anfügen
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P' and [name] = 'itcproc_ZeitKladdenZeile_add')
  DROP PROCEDURE [dbo].[itcproc_ZeitKladdenZeile_add]
GO
CREATE PROCEDURE [dbo].[itcproc_ZeitKladdenZeile_add]
  @Parm_Code       varchar(20)
 ,@Parm_RecIdFrom  varchar(10) = '0'
 ,@Parm_ServiceBer varchar(20) = ''
AS
  DECLARE @m_RecIdFrom integer = dbo.itcfunc_integer(@Parm_RecIdFrom)
  IF @m_RecIdFrom = 0
    SELECT TOP 1 @m_RecIdFrom = ISNULL(RecId,0) FROM ut_ZeitKladdenZeile WHERE Code = @Parm_Code ORDER BY LfdNr DESC

  -- Basisdatensatz einfügen
  DECLARE @m_RecId     integer
  DECLARE @m_LfdNr     integer
  DECLARE @m_ErfDatum  datetime
  DECLARE @m_BuchDatum datetime
  SELECT @m_LfdNr     = ISNULL(MAX(LfdNr),0) + 1, 
         @m_RecId     = dbo.itcfunc_NextRecId_ZeitKladdenZeile(), 
		 @m_ErfDatum  = dbo.itcfunc_datetime('H'),
		 @m_BuchDatum = dbo.itcfunc_datetime('') 
    FROM [dbo].[ut_ZeitKladdenZeile] WHERE Code = @Parm_Code
  INSERT INTO [dbo].[ut_ZeitKladdenZeile] (RecId, Code, LfdNr, ErfDatum, BuchDatum) VALUES (@m_RecId, @Parm_Code, @m_LfdNr, @m_ErfDatum, @m_BuchDatum)

  -- Variablen fuer zu kopierende Daten
  DECLARE @m_Annr       varchar(4)
  DECLARE @m_CpyAnnr    tinyint
  DECLARE @m_Loa        varchar(3)
  DECLARE @m_CpyLoa     tinyint
  DECLARE @m_PrjNr      varchar(8)
  DECLARE @m_BelArt     varchar(2)
  DECLARE @m_BelNr      varchar(8)
  DECLARE @m_CpyBelNr   tinyint
  DECLARE @m_Kst        varchar(20)
  DECLARE @m_CpyKst     tinyint
  DECLARE @m_Ktr        varchar(20)
  DECLARE @m_CpyKtr     tinyint
  DECLARE @m_ExtBelNr   varchar(20)
  DECLARE @m_CpyExtBelN tinyint
  DECLARE @m_Vorgang    varchar(20)
  DECLARE @m_CpyVorgang tinyint
  DECLARE @m_CpyBuchDat tinyint
  DECLARE @m_CpyErfDat  tinyint

 -- Kopfsatz auslesen und Annr übernehmen
  SELECT @m_Annr = Annr, @m_CpyAnnr = CpyAnnr, @m_CpyLoa = CpyLoa, @m_CpyBelNr = CpyBelNr, 
         @m_CpyKst = CpyKst, @m_CpyKtr = CpyKtr, @m_CpyExtBelN = CpyExtBelN, @m_CpyVorgang = CpyVorgang
    FROM ut_ZeitKladdenKopf WHERE Code = @Parm_Code
  IF @m_Annr <> ''
    EXECUTE [dbo].[itcproc_ZeitKladdenZeile_validateAnnr] @m_RecId, @m_Annr

  IF @m_RecIdFrom <> 0 BEGIN
    SELECT @m_Annr = Annr, @m_Loa = Loa, @m_PrjNr = PrjNr, @m_BelArt = BelArt, @m_BelNr = BelNr, @m_Kst = Kst, @m_Ktr = Ktr, @m_ExtBelNr = ExtBelNr, @m_Vorgang = Vorgang
      FROM ut_ZeitKladdenZeile WHERE RecId = @m_RecIdFrom

    IF @m_CpyAnnr <> 0 AND @m_Annr <> ''
      EXECUTE [dbo].[itcproc_ZeitKladdenZeile_validateAnnr] @m_RecId, @m_Annr
    IF @m_CpyLoa <> 0 AND @m_Loa <> ''
      EXECUTE [dbo].[itcproc_ZeitKladdenZeile_validateLoa] @m_RecId, @m_Loa

    IF @m_CpyBelNr <> 0
	  UPDATE [dbo].[ut_ZeitKladdenZeile] SET PrjNr = ISNULL(@m_PrjNr,''), BelArt = ISNULL(@m_BelArt,''), BelNr = ISNULL(@m_BelNr,'') WHERE RecId = @m_RecId
    IF @m_CpyKst <> 0
	  UPDATE [dbo].[ut_ZeitKladdenZeile] SET Kst = ISNULL(@m_Kst,'') WHERE RecId = @m_RecId
    IF @m_CpyKtr <> 0
	  UPDATE [dbo].[ut_ZeitKladdenZeile] SET Ktr = ISNULL(@m_Ktr,'') WHERE RecId = @m_RecId
    IF @m_CpyExtBelN <> 0
	  UPDATE [dbo].[ut_ZeitKladdenZeile] SET ExtBelNr = ISNULL(@m_ExtBelNr,'') WHERE RecId = @m_RecId
    IF @m_CpyVorgang <> 0
	  UPDATE [dbo].[ut_ZeitKladdenZeile] SET Vorgang = ISNULL(@m_Vorgang,'') WHERE RecId = @m_RecId
    IF @m_CpyBuchDat <> 0
	  UPDATE [dbo].[ut_ZeitKladdenZeile] SET BuchDatum = @m_BuchDatum WHERE RecId = @m_RecId
    IF @m_CpyErfDat <> 0
	  UPDATE [dbo].[ut_ZeitKladdenZeile] SET ErfDatum = @m_ErfDatum WHERE RecId = @m_RecId
  END

  -- Buchungsdatum korrigieren ?
  SELECT @m_BuchDatum = BuchDatum FROM [dbo].[ut_ZeitKladdenZeile] WHERE RecId = @m_RecId
  IF @m_BuchDatum = dbo.itcfunc_datetime('')
    UPDATE [dbo].[ut_ZeitKladdenZeile] SET BuchDatum = ErfDatum WHERE RecId = @m_RecId

  IF @Parm_ServiceBer <> '' BEGIN
    SELECT @m_Annr = Monteur, @m_PrjNr = PrjNr, @m_BelArt = BelArt, @m_BelNr = BelNr FROM ut_WartungsAuftraege WHERE Nr = @Parm_ServiceBer
	UPDATE [dbo].[ut_ZeitKladdenZeile] SET ServiceBer = UPPER(@Parm_ServiceBer), PrjNr = ISNULL(@m_PrjNr,''), BelArt = ISNULL(@m_BelArt,''), BelNr = ISNULL(@m_BelNr,'') WHERE RecId = @m_RecId
    IF @m_Annr <> ''
      EXECUTE [dbo].[itcproc_ZeitKladdenZeile_validateAnnr] @m_RecId, @m_Annr
  END

  -- Datensatz auswaehlen
  SELECT * FROM [dbo].[itcview_ZeitKladdenZeile] WHERE RecId = @m_RecId
GO

--------------------------------------------------------------------------------------------------------
-- Zeitbuchblattzeile löschen
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P' and [name] = 'itcproc_ZeitKladdenZeile_del')
  DROP PROCEDURE [dbo].[itcproc_ZeitKladdenZeile_del]
GO
CREATE PROCEDURE [dbo].[itcproc_ZeitKladdenZeile_del]
  @Parm_RecId varchar(10)
AS
  DECLARE @m_RecId    integer = dbo.itcfunc_integer(@Parm_RecId)
  DECLARE @m_Gesperrt integer

  SELECT @m_Gesperrt = ISNULL(Gesperrt,1) FROM ut_ZeitKladdenZeile WHERE RecId = @m_RecId 
  If @m_Gesperrt = 0 BEGIN
    DELETE FROM ut_ZeitKladdenZeile WHERE RecId = @m_RecId 
    SELECT 1 AS success
  END ELSE
    SELECT 'Dieser Datensatz kann nicht gelöscht werden.' AS error
GO

--------------------------------------------------------------------------------------------------------
-- Zeitbuchblattzeile Feld nach Name validieren
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P' and [name] = 'itcproc_ZeitKladdenZeile_validate')
  DROP PROCEDURE [dbo].[itcproc_ZeitKladdenZeile_validate]
GO
CREATE PROCEDURE [dbo].[itcproc_ZeitKladdenZeile_validate]
  @Parm_RecId      varchar(10)
 ,@Parm_FieldName  varchar(30)
 ,@Parm_Value      varchar(200) = ''
 ,@Parm_WithResult varchar(10)  = '1'
AS
  DECLARE @m_RecId      integer = dbo.itcfunc_integer(@Parm_RecId)
  DECLARE @m_Success    integer = 0
  DECLARE @m_Code       varchar(20)
  DECLARE @m_Gesperrt   tinyint
  DECLARE @m_floatValue float
  DECLARE @m_intValue   integer
  DECLARE @m_PrjNr      varchar(8)
  DECLARE @m_BelArt     varchar(2)
  DECLARE @m_BelNr      varchar(8)

  SELECT @m_Code = Code, @m_Gesperrt = Gesperrt FROM ut_ZeitKladdenZeile WHERE RecId = @m_RecId
  IF 0 <> ISNULL(@m_Gesperrt,1) BEGIN
    IF dbo.itcfunc_integer(@Parm_WithResult) <> 0
      SELECT 'Dieser Datensatz ist gesperrt.' AS error
    RETURN @m_Success
  END

  ------------------------------------------------------------------------------------
  IF @Parm_FieldName = 'Annr' BEGIN
    EXECUTE [dbo].[itcproc_ZeitKladdenZeile_validateAnnr] @m_RecId, @Parm_Value
	SET @m_Success = 1
  ------------------------------------------------------------------------------------
  END ELSE IF @Parm_FieldName = 'Loa' BEGIN
    EXECUTE [dbo].[itcproc_ZeitKladdenZeile_validateLoa] @m_RecId, @Parm_Value
	SET @m_Success = 1
  ------------------------------------------------------------------------------------
  END ELSE IF @Parm_FieldName = 'BuchDatum' BEGIN
    UPDATE ut_ZeitKladdenZeile SET BuchDatum = convert(datetime,@Parm_Value,120) WHERE RecId = @m_RecId
    IF 1 = (SELECT DupBuchDat FROM ut_ZeitKladdenKopf WHERE Code = @m_Code)
	  UPDATE ut_ZeitKladdenZeile SET ErfDatum = BuchDatum WHERE RecId = @m_RecId
	SET @m_Success = 1
  ------------------------------------------------------------------------------------
  END ELSE IF @Parm_FieldName = 'ErfDatum' BEGIN
    IF 1 = (SELECT CpyErfDat FROM ut_ZeitKladdenKopf WHERE Code = @m_Code) BEGIN
      UPDATE ut_ZeitKladdenZeile SET ErfDatum = convert(datetime,@Parm_Value,120) WHERE RecId = @m_RecId
      IF 1 = (SELECT DupErfDat FROM ut_ZeitKladdenKopf WHERE Code = @m_Code)
	    UPDATE ut_ZeitKladdenZeile SET BuchDatum = ErfDatum WHERE RecId = @m_RecId
	  SET @m_Success = 1
	END
  ------------------------------------------------------------------------------------
  END ELSE IF @Parm_FieldName = 'Stunden' BEGIN
    SET @m_floatValue = dbo.itcfunc_float(@Parm_Value)
    EXEC @m_Success = [dbo].[itcproc_ZeitKladdenZeile_validateStunden] @m_RecId, @m_floatValue
  ------------------------------------------------------------------------------------
  END ELSE IF @Parm_FieldName = 'AbzugStd' BEGIN
    SET @m_floatValue = dbo.itcfunc_float(@Parm_Value)
    EXEC @m_Success = [dbo].[itcproc_ZeitKladdenZeile_validateAbzugStd] @m_RecId, @m_floatValue
  ------------------------------------------------------------------------------------
  END ELSE IF @Parm_FieldName = 'Betrag' BEGIN
    SET @m_floatValue = dbo.itcfunc_float(@Parm_Value)
    EXEC @m_Success = [dbo].[itcproc_ZeitKladdenZeile_validateBetrag] @m_RecId, @m_floatValue
  ------------------------------------------------------------------------------------
  END ELSE IF @Parm_FieldName = 'Beginn' BEGIN
    EXEC @m_Success = [dbo].[itcproc_ZeitKladdenZeile_validateBeginn] @m_RecId, @Parm_Value
  ------------------------------------------------------------------------------------
  END ELSE IF @Parm_FieldName = 'Ende' BEGIN
    EXEC @m_Success = [dbo].[itcproc_ZeitKladdenZeile_validateEnde] @m_RecId, @Parm_Value
  ------------------------------------------------------------------------------------
  END ELSE IF @Parm_FieldName = 'BuText' BEGIN
    UPDATE ut_ZeitKladdenZeile SET BuText = @Parm_Value WHERE RecId = @m_RecId
	SET @m_Success = 1
  ------------------------------------------------------------------------------------
  END ELSE IF @Parm_FieldName = 'PrjNr' BEGIN
    IF 0 < (SELECT COUNT(*) FROM ut_Belege WHERE PRJNR = UPPER(@Parm_Value) AND BELEGART = ' 7')
      UPDATE ut_ZeitKladdenZeile SET PrjNr = UPPER(@Parm_Value), BelArt = ' 7', BelNr = '' WHERE RecId = @m_RecId
	ELSE
      UPDATE ut_ZeitKladdenZeile SET PrjNr = UPPER(@Parm_Value), BelArt = '', BelNr = '' WHERE RecId = @m_RecId
	SET @m_Success = 1
  ------------------------------------------------------------------------------------
  END ELSE IF @Parm_FieldName = 'BelArt' BEGIN
    SET @m_intValue = dbo.itcfunc_integer(@Parm_Value)
    IF 0 < (SELECT COUNT(*) FROM ut_Belegarten WHERE Nr = @m_intValue)
      UPDATE ut_ZeitKladdenZeile SET BelArt = STR(@m_intValue,2), BelNr = '' WHERE RecId = @m_RecId
	ELSE
      UPDATE ut_ZeitKladdenZeile SET BelArt = '', BelNr = '' WHERE RecId = @m_RecId
	SET @m_Success = 1
  ------------------------------------------------------------------------------------
  END ELSE IF @Parm_FieldName = 'BelNr' BEGIN
    UPDATE ut_ZeitKladdenZeile SET BelNr = UPPER(@Parm_Value) WHERE RecId = @m_RecId
	IF 0 = (SELECT COUNT(*) FROM ut_ZeitKladdenZeile z INNER JOIN ut_Belege b ON z.PrjNr = b.PRJNR AND z.BelArt = b.BELEGART AND z.BelNr = b.BELEGNR WHERE z.RecId = @m_RecId)
      UPDATE ut_ZeitKladdenZeile SET BelArt = '' WHERE RecId = @m_RecId
	SET @m_Success = 1
  END ELSE IF @Parm_FieldName = 'Belege:RecId' BEGIN
    SET @m_intValue = dbo.itcfunc_integer(@Parm_Value)
    SELECT @m_Prjnr = ISNULL(PRJNR,''), @m_BelArt = ISNULL(BELEGART,''), @m_BelNr = ISNULL(BELEGNR,'') FROM ut_Belege WHERE RecId = @m_intValue
    UPDATE ut_ZeitKladdenZeile SET PrjNr = @m_PrjNr, BelArt = @m_BelArt, BelNr = @m_BelNr WHERE RecId = @m_RecId
	SET @m_Success = 1
  END

  IF dbo.itcfunc_integer(@Parm_WithResult) <> 0 BEGIN
    IF @m_Success <> 0 
      SELECT * FROM [dbo].[itcview_ZeitKladdenZeile] WHERE RecId = @m_RecId
	ELSE
      SELECT 'Der Wert "' + @Parm_Value + '" ist hier im Feld [' + @Parm_FieldName + '] nicht erlaubt.' AS error
  END
  RETURN @m_Success
GO
