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

----------------------------------------------------------------------------------------------
-- naechste Transaktionsnummer fuer die Buchungszeilen ermitteln
IF OBJECT_ID (N'itcfunc_Buchungszeilen_NextTransNo', N'FN') IS NOT NULL
    DROP FUNCTION dbo.itcfunc_Buchungszeilen_NextTransNo
GO
CREATE FUNCTION dbo.itcfunc_Buchungszeilen_NextTransNo() RETURNS char(8)
AS
BEGIN
  DECLARE @mMaxTransNo char(8)
  SELECT @mMaxTransNo = ISNULL(MAX(TransNo),'00000000') FROM [dbo].[ut_Buchungszeilen]
  IF @mMaxTransNo = '' SET @mMaxTransNo = '00000000'
  RETURN dbo.itcfunc_IncStringHex(@mMaxTransNo)
END
GO

----------------------------------------------------------------------------------------------
-- Beide Transaktionen als ausgeglichen kennzeichenen
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'itcproc_Trn_Ausgleichen')
  DROP PROCEDURE [dbo].[itcproc_Trn_Ausgleichen]
GO
CREATE PROCEDURE [dbo].[itcproc_Trn_Ausgleichen] @param_TransNo1 char(8), @param_TransNo2 char(8)
AS
  UPDATE ut_Buchungszeilen SET AusglTRN = @param_TransNo2 WHERE TransNo = @param_TransNo1 AND AusglTRN = ''
  IF @param_TransNo1 <> @param_TransNo2
    UPDATE ut_Buchungszeilen SET AusglTRN = @param_TransNo1 WHERE TransNo = @param_TransNo2 AND AusglTRN = ''
GO

----------------------------------------------------------------------------------------------
-- Eine Transaktion verdichten
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'itcproc_Trn_Verdichten')
  DROP PROCEDURE [dbo].[itcproc_Trn_Verdichten]
GO
CREATE PROCEDURE [dbo].[itcproc_Trn_Verdichten] @param_TransNo char(8), @param_NormalizeSH integer = 0, @param_IsBilanz integer = 0
AS
  DECLARE @m_RecId   integer
  DECLARE @m_Typ     char(1)
  DECLARE @m_Konto   char(8)
  DECLARE @m_PrjNr   char(8)
  DECLARE @m_Ktr     char(20)
  DECLARE @m_Kst     char(20)
  DECLARE @m_Betrag  decimal(14,2)
  DECLARE @m_Soll    decimal(14,2)
  DECLARE @m_Haben   decimal(14,2)

  DECLARE @m_CurrRecId   integer
  DECLARE @m_CurrTyp     char(1)
  DECLARE @m_CurrKonto   char(8)
  DECLARE @m_CurrPrjNr   char(8)
  DECLARE @m_CurrKtr     char(20)
  DECLARE @m_CurrKst     char(20)
  DECLARE @m_CurrBetrag  decimal(14,2)
  DECLARE @m_CurrSoll    decimal(14,2)
  DECLARE @m_CurrHaben   decimal(14,2)

  -- Datum merken fuer ev. leere Buchungszeile
  DECLARE @m_Tag     char(2)
  DECLARE @m_Monat   char(2)
  DECLARE @m_Jahr    char(4)
  SELECT TOP 1 @m_Tag = Tag, @m_Monat = Monat, @m_Jahr = Jahr FROM ut_Buchungszeilen where TransNo = @param_TransNo

  -- Verdichtungslauf  
  DECLARE CVD CURSOR LOCAL FOR select RecId, Typ, Konto, PrjNr, Ktr, Kst, BtrOrg, Soll, Haben  
     from ut_Buchungszeilen where TransNo = @param_TransNo order by Typ, Konto, PrjNr, Ktr, Kst, RecId
  OPEN CVD

  SELECT @m_RecId = 0, @m_Typ = '', @m_Konto = '', @m_PrjNr = '', @m_Ktr = '', @m_Kst = '',  @m_Betrag = 0.00, @m_Soll = 0.00, @m_Haben = 0.00
  FETCH NEXT FROM CVD INTO @m_CurrRecId, @m_CurrTyp, @m_CurrKonto, @m_CurrPrjNr, @m_CurrKtr, @m_CurrKst,  @m_CurrBetrag, @m_CurrSoll, @m_CurrHaben
  WHILE (@@FETCH_STATUS = 0) BEGIN
    -- nicht verdichtbar? --> diesen als Verdichtungssatz merken
    IF (@m_CurrTyp <> @m_Typ OR @m_CurrKonto <> @m_Konto) OR
	   ((@m_CurrTyp <> 'P') AND (@m_CurrPrjNr <> @m_PrjNr OR @m_CurrKtr <> @m_Ktr OR @m_CurrKst <> @m_Kst)) BEGIN
	  SELECT @m_RecId = @m_CurrRecId, @m_Typ = @m_CurrTyp, @m_Konto = @m_CurrKonto, 
	         @m_PrjNr = @m_CurrPrjNr, @m_Ktr = @m_CurrKtr, @m_Kst = @m_CurrKst, 
			 @m_Betrag = @m_CurrBetrag, @m_Soll = @m_CurrSoll, @m_Haben = @m_CurrHaben
	END ELSE BEGIN
	  SELECT @m_Betrag = @m_Betrag + @m_CurrBetrag, @m_Soll = @m_Soll + @m_CurrSoll, @m_Haben = @m_Haben + @m_CurrHaben
	  UPDATE ut_Buchungszeilen SET BtrOrg = 0, Soll = 0, Haben = 0 WHERE RecId = @m_CurrRecId
	  UPDATE ut_Buchungszeilen SET BtrOrg = @m_Betrag, Soll = @m_Soll, Haben = @m_Haben WHERE RecId = @m_RecId
	END

    FETCH NEXT FROM CVD INTO @m_CurrRecId, @m_CurrTyp, @m_CurrKonto, @m_CurrPrjNr, @m_CurrKtr, @m_CurrKst, @m_CurrBetrag, @m_CurrSoll, @m_CurrHaben
  END
  CLOSE CVD
  DEALLOCATE CVD

  -- Soll Haben normalisieren
  IF @param_NormalizeSH <> 0 BEGIN
    -- Achtung Vorzeichen ist invers
	DECLARE CNSH CURSOR LOCAL FOR select Haben - Soll from ut_Buchungszeilen where TransNo = @param_TransNo
    OPEN CNSH
	FETCH NEXT FROM CNSH INTO @m_Betrag
    WHILE (@@FETCH_STATUS = 0) BEGIN
	  IF @m_Betrag < 0
	    UPDATE ut_Buchungszeilen SET BtrOrg = @m_Betrag, Soll = 0 - @m_Betrag, Haben = 0 WHERE CURRENT OF CNSH
	  ELSE
	    UPDATE ut_Buchungszeilen SET BtrOrg = @m_Betrag, Soll = 0, Haben = @m_Betrag WHERE CURRENT OF CNSH
  	  FETCH NEXT FROM CNSH INTO @m_Betrag
    END
    CLOSE CNSH
    DEALLOCATE CNSH
  END

  -- entfallende Zeilen loeschen
  IF @param_IsBilanz <> 0
    DELETE FROM ut_Buchungszeilen WHERE TransNo = @param_TransNo AND BtrOrg = 0 AND Soll = 0 AND Haben = 0
  ELSE
    DELETE FROM ut_Buchungszeilen WHERE TransNo = @param_TransNo AND BtrOrg = 0 AND Round(Soll,2) = Round(Haben,2)

  -- dummy-Zeile fuer vollstaendig ausgeglichene Transaktion (leere Transaktion)
  DECLARE @m_NewRecId integer
  IF 0 = (SELECT count(*) FROM ut_Buchungszeilen WHERE TransNo = @param_TransNo) BEGIN
    SET @m_NewRecId = dbo.itcfunc_NextRecId_Buchungszeilen()
	SET @m_Konto = (SELECT OptDataC FROM ut_Optionen WHERE OptId = ' 6115')
    INSERT ut_Buchungszeilen (RecId,TransNo,Typ,Konto,Jahr,Monat,Tag,[Text]) 
	VALUES(@m_NewRecId,@param_TransNo,'S',ISNULL(@m_Konto,'9999'),@m_Jahr,@m_Monat,@m_Tag,'leere Transaktion')
  END
GO

-- -------------------------------------------------------------------------------------------------------------
-- Transaktion fuer ein Konto saldieren
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'itcproc_Trn_SaldierenFuerKonto')
  DROP PROCEDURE [dbo].[itcproc_Trn_SaldierenFuerKonto]
GO
CREATE PROCEDURE [dbo].[itcproc_Trn_SaldierenFuerKonto]  @param_TransNo char(8), @param_Typ char(1), @param_Konto char(8)
AS
	SET NOCOUNT ON

	-- ersten Datensatz der Transaktion fuer dieses Konto ermitteln ---------------------------------------------
	DECLARE @m_RecId integer
	DECLARE @m_Jahr  varchar(4)
	DECLARE @m_Monat varchar(2)
	DECLARE @m_Tag   varchar(2)
	SELECT TOP 1 @m_RecId = RecId, @m_Jahr = Jahr, @m_Monat = Monat, @m_Tag = Tag FROM ut_Buchungszeilen 
	WHERE TransNo = @param_TransNo AND Typ = @param_Typ AND Konto = @param_Konto ORDER BY RecId
	-- SELECT @m_RecId, @m_Jahr, @m_Monat, @m_Tag

	-- Werte des vorangehenden Datensatzes ----------------------------------------------------------------------
	--DECLARE @m_PriorTyp   char(1)
	--DECLARE @m_PriorKonto char(8)
	DECLARE @m_AlterSaldo  decimal(14,2)
	DECLARE @m_AlterUmsatz decimal(14,2)
    SELECT TOP 1 @m_AlterSaldo = SaldoNeu, @m_AlterUmsatz = SaldoUms FROM ut_Buchungszeilen 
	  WHERE Typ = @param_Typ AND Konto = @param_Konto AND
	    (  (Jahr = @m_Jahr AND Monat = @m_Monat AND Tag = @m_Tag AND TransNo < @param_TransNo)
		OR (Jahr = @m_Jahr AND Monat = @m_Monat AND Tag < @m_Tag)
		OR (Jahr = @m_Jahr AND Monat < @m_Monat)
		OR (Jahr < @m_Jahr)
		)
	  ORDER BY Jahr desc, Monat desc, Tag desc, TransNo desc, RecId desc
	IF @m_AlterSaldo IS NULL SELECT @m_AlterSaldo = 0, @m_AlterUmsatz = 0 
	-- SELECT @m_AlterSaldo, @m_AlterUmsatz

	-- Zeilen ab hier als Cursor oeffnen und aktualisieren -----------------------------------------------------
	DECLARE @m_Soll      decimal(14,2)
	DECLARE @m_Haben     decimal(14,2)
	DECLARE @m_SaldoNeu  decimal(14,2)
	DECLARE @m_SaldoUms  decimal(14,2)

	DECLARE C_Kto CURSOR LOCAL FOR SELECT Soll, Haben FROM ut_Buchungszeilen
	  WHERE Typ = @param_Typ AND Konto = @param_Konto AND
	    (  (Jahr = @m_Jahr AND Monat = @m_Monat AND Tag = @m_Tag AND TransNo >= @param_TransNo)
		OR (Jahr = @m_Jahr AND Monat = @m_Monat AND Tag > @m_Tag)
		OR (Jahr = @m_Jahr AND Monat > @m_Monat)
		OR (Jahr > @m_Jahr)
		)
	  ORDER BY Jahr, Monat, Tag, TransNo, RecId

	OPEN C_Kto
	FETCH NEXT FROM C_Kto INTO @m_Soll, @m_Haben
	WHILE (@@FETCH_STATUS = 0) BEGIN
		SELECT @m_SaldoNeu = @m_AlterSaldo + @m_Haben - @m_Soll, @m_SaldoUms = @m_AlterUmsatz + @m_Haben
		UPDATE ut_Buchungszeilen SET SaldoAlt = @m_AlterSaldo, SaldoNeu = @m_SaldoNeu, SaldoUms = @m_SaldoUms, Saldiert = 1	WHERE CURRENT OF C_Kto
		SELECT @m_AlterSaldo = @m_SaldoNeu, @m_AlterUmsatz = @m_SaldoUms
		FETCH NEXT FROM C_Kto INTO @m_Soll, @m_Haben
	END
	CLOSE C_Kto
	DEALLOCATE C_Kto
GO

----------------------------------------------------------------------------------------------
-- Eine Transaktion erstmalig oder neu saldieren
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'itcproc_Trn_Saldieren')
  DROP PROCEDURE [dbo].[itcproc_Trn_Saldieren]
GO
CREATE PROCEDURE [dbo].[itcproc_Trn_Saldieren] @param_TransNo char(8)
AS
  DECLARE @m_Typ   char(1)
  DECLARE @m_Konto char(8)

  DECLARE C_TRN CURSOR LOCAL FOR SELECT DISTINCT Typ, Konto FROM ut_Buchungszeilen WHERE TransNo = @param_TransNo
  OPEN C_TRN

  FETCH NEXT FROM C_TRN INTO @m_Typ, @m_Konto
  WHILE (@@FETCH_STATUS = 0) BEGIN
	EXECUTE [dbo].[itcproc_Trn_SaldierenFuerKonto] @param_TransNo, @m_Typ, @m_Konto
    FETCH NEXT FROM C_TRN INTO @m_Typ, @m_Konto
  END

  CLOSE C_TRN
  DEALLOCATE C_TRN
GO

----------------------------------------------------------------------------------------------
-- Ein Konto komplett neu Saldieren
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'itcproc_Konto_Saldieren')
  DROP PROCEDURE [dbo].[itcproc_Konto_Saldieren]
GO
CREATE PROCEDURE [dbo].[itcproc_Konto_Saldieren] @param_Typ char(1), @param_Konto char(8)
AS
  DECLARE @m_TransNo char(8)

  SELECT TOP 1 @m_TransNo = TransNo FROM ut_Buchungszeilen WHERE Typ = @param_Typ AND Konto = @param_Konto
  ORDER BY Jahr, Monat, Tag, TransNo

  IF NOT @m_TransNo IS NULL BEGIN
	EXECUTE [dbo].[itcproc_Trn_SaldierenFuerKonto] @m_TransNo, @param_Typ, @param_Konto
  END
GO

----------------------------------------------------------------------------------------------
-- Alle Konten erstmalig oder neu saldieren
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'itcproc_Alles_Saldieren')
  DROP PROCEDURE [dbo].[itcproc_Alles_Saldieren]
GO
CREATE PROCEDURE [dbo].[itcproc_Alles_Saldieren]
AS
  BEGIN TRANSACTION
  SET NOCOUNT ON

  DECLARE @m_Typ   char(1)
  DECLARE @m_Konto char(8)

  DECLARE C_TRN CURSOR LOCAL FOR SELECT DISTINCT Typ, Konto FROM ut_Buchungszeilen
  OPEN C_TRN

  FETCH NEXT FROM C_TRN INTO @m_Typ, @m_Konto
  WHILE (@@FETCH_STATUS = 0) BEGIN
	EXECUTE [dbo].[itcproc_Konto_Saldieren] @m_Typ, @m_Konto
    FETCH NEXT FROM C_TRN INTO @m_Typ, @m_Konto
  END

  CLOSE C_TRN
  DEALLOCATE C_TRN
  SET NOCOUNT OFF
  COMMIT TRANSACTION
  SELECT 1 as Success
GO

----------------------------------------------------------------------------------------------
-- Stornieren einer Transaktion
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'itcproc_Trn_Stornieren')
  DROP PROCEDURE [dbo].[itcproc_Trn_Stornieren]
GO
CREATE PROCEDURE [dbo].[itcproc_Trn_Stornieren] @param_TransNo char(8), @param_NewTransNo char(8) = ''
AS
  IF RTRIM(@param_NewTransNo) = '' SET @param_NewTransNo = dbo.itcfunc_Buchungszeilen_NextTransNo()

  DECLARE @m_RecId integer
  DECLARE @m_NewRecId integer

  DECLARE C_TRN CURSOR LOCAL FOR SELECT RecId FROM ut_Buchungszeilen WHERE TransNo = @param_TransNo
  OPEN C_TRN

  FETCH NEXT FROM C_TRN INTO @m_RecId
  WHILE (@@FETCH_STATUS = 0) BEGIN

    SET @m_NewRecId = dbo.itcfunc_NextRecId_Buchungszeilen()
    INSERT ut_Buchungszeilen (RecId,TransNo,RefTRN,Typ,Konto,Jahr,Monat,Tag,Soll,Haben,BtrOrg,BtrWae,[Text],Kst,Ktr,PrjNr,BelNr,BelNrExt,BelDatum,Vorgang)
    SELECT @m_NewRecId,@param_NewTransNo,TransNo,Typ,Konto,Jahr,Monat,Tag,-Soll,-Haben,-BtrOrg,BtrWae,[Text],Kst,Ktr,PrjNr,BelNr,BelNrExt,BelDatum,Vorgang from ut_Buchungszeilen WHERE RecId = @m_RecId

	UPDATE ut_Buchungszeilen SET AusglTRN = @param_NewTransNo WHERE CURRENT OF C_TRN

    FETCH NEXT FROM C_TRN INTO @m_RecId
  END

  CLOSE C_TRN
  DEALLOCATE C_TRN

  EXECUTE [dbo].[itcproc_Trn_Saldieren] @param_NewTransNo
GO

----------------------------------------------------------------------------------------------
-- Transaktion zum Beleg stornieren
-- fruehere gegenbuchen (Status = 0 in Status = 2 ueberführen, neue (Storno-)Transaktion hat Status = 3)
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'itcproc_BelTransactions_Stornieren')
  DROP PROCEDURE [dbo].[itcproc_BelTransactions_Stornieren]
GO
CREATE PROCEDURE [dbo].[itcproc_BelTransactions_Stornieren] @m_PrjNr char(8), @m_BelArt char(2), @m_BelNr char(8)
AS
  DECLARE @m_TransNo    char(8)
  DECLARE @m_NewTransNo char(8)
  DECLARE @m_BTrnRecId  integer

  DECLARE C_BelTrn CURSOR STATIC LOCAL FOR 
  SELECT TransNo from ut_BelTransaction where PrjNr = @m_PrjNr AND BelArt = @m_BelArt AND BelNr = @m_BelNr AND Status = '0'
  OPEN C_BelTrn

  FETCH NEXT FROM C_BelTrn INTO @m_TransNo
  WHILE (@@FETCH_STATUS = 0) BEGIN
    SET @m_NewTransNo = dbo.itcfunc_Buchungszeilen_NextTransNo()
    EXECUTE [dbo].[itcproc_Trn_Stornieren] @m_TransNo, @m_NewTransNo

	IF 0 < (SELECT count(*) FROM ut_Buchungszeilen WHERE TransNo = @m_NewTransNo) BEGIN
	  UPDATE ut_BelTransaction SET Status = '2' WHERE TransNo = @m_TransNo
      SET @m_BTrnRecId = dbo.itcfunc_NextRecId_BelTransaction()
	  INSERT ut_BelTransaction (RecId, PrjNr, BelArt, BelNr, TransNo, [Status]) VALUES(@m_BTrnRecId, @m_PrjNr, @m_BelArt, @m_BelNr, @m_NewTransNo, '0')
	  UPDATE ut_BelTransaction SET Status = '3' WHERE TransNo = @m_NewTransNo
	END

	FETCH NEXT FROM C_BelTrn INTO @m_TransNo
  END
  CLOSE C_BelTrn
  DEALLOCATE C_BelTrn
GO

----------------------------------------------------------------------------------------------

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'itcproc_KreditorenPostenAusgleichVonKladdenZeile')
  DROP PROCEDURE [dbo].[itcproc_KreditorenPostenAusgleichVonKladdenZeile]
GO
CREATE PROCEDURE [dbo].[itcproc_KreditorenPostenAusgleichVonKladdenZeile] @param_RecIdKladde integer, @param_TransNo char(8)
AS
  DECLARE @m_AusglFuer char(8)
  DECLARE @m_AusglBetr float
  DECLARE @m_AusglSkon float
  DECLARE @m_AusglKto  char(8)
  DECLARE @m_AusglSKto char(8)
  DECLARE @m_GKto      char(8)
  SELECT @m_GKto = GK_Kto, @m_AusglFuer = AusglFuer, @m_AusglBetr = AusglBetr, @m_AusglSkon = AusglSkon, @m_AusglKto = AusglKto, @m_AusglSKto = AusglSKto  FROM ut_KladdenZeile WHERE RecId = @param_RecIdKladde

  UPDATE ut_Buchungszeilen Set AusglBtr = ROUND(AusglBtr + @m_AusglBetr + @m_AusglSkon,2) WHERE TransNo = @m_AusglFuer AND Typ = 'P' AND Status1 = 1
  UPDATE ut_Buchungszeilen Set AusglTRN = @param_TransNo WHERE TransNo = @m_AusglFuer AND Typ = 'P' AND Status1 = 1 AND ROUND(Haben - Soll, 2) = ROUND(AusglBtr, 2)

  DECLARE @m_RecIdAZ integer
  SET @m_RecIdAZ = dbo.itcfunc_NextRecId_Ausgleichszeilen()

  INSERT INTO ut_Ausgleichszeilen (RecId,[Status],[Typ],Konto,TransNo,DstTyp,DstKonto,DstBetrag,SkoKonto,SkoBetrag)
  VALUES (@m_RecIdAZ,'G','P',@m_GKto,@m_AusglFuer,'S',@m_AusglKto,@m_AusglBetr,@m_AusglSKto,@m_AusglSkon)
GO

----------------------------------------------------------------------------------------------
-- Kladde pruefen und die RecId des Fehlersatzes zurückgeben
IF OBJECT_ID (N'itcfunc_Kladde_Pruefen', N'FN') IS NOT NULL
    DROP FUNCTION dbo.itcfunc_Kladde_Pruefen
GO
CREATE FUNCTION dbo.itcfunc_Kladde_Pruefen(@param_Code char(20), @param_LfdNr integer = 0) RETURNS integer
AS
BEGIN
  DECLARE @m_RecId        integer
  DECLARE @m_Joined       integer 
  DECLARE @m_Typ          char(1) 
  DECLARE @m_Kto          char(8)
  DECLARE @m_GK_Typ       char(1)
  DECLARE @m_GK_Kto       char(8)
  DECLARE @m_Betrag       float
  DECLARE @m_Datum	      datetime
  DECLARE @m_DatumsTyp    integer
  DECLARE @m_Saldo        float
  DECLARE @m_RecIdStart   integer
  DECLARE @m_minDatum	  datetime
  DECLARE @m_trnDatum     datetime
  DECLARE @m_trnDatumsTyp integer

  DECLARE C_Kladde CURSOR STATIC LOCAL FOR 
  SELECT RecId, Joined, Typ, Kto, GK_Typ, GK_Kto, Betrag, BuchDatum, DatumsTyp from ut_KladdenZeile where Code = @param_Code and LfdNr >= @param_LfdNr and TransNo = '' order by LfdNr
  OPEN C_Kladde

  SET @m_Saldo = 0
  SET @m_RecIdStart = 0
  SET @m_minDatum   = CONVERT(datetime, '1970-01-01', 120)
  FETCH NEXT FROM C_Kladde INTO @m_RecId, @m_Joined, @m_Typ, @m_Kto, @m_GK_Typ, @m_GK_Kto, @m_Betrag, @m_Datum, @m_DatumsTyp
  -- Die erste Zeile kann nicht verbunden sein
  IF @m_Joined <> 0 BEGIN
    CLOSE C_Kladde
    DEALLOCATE C_Kladde
	RETURN @m_RecId
  END

  WHILE (@@FETCH_STATUS = 0) BEGIN
    -- Beginn einer Transaktion
    IF @m_Joined = 0 BEGIN
	  IF @m_Saldo <> 0 BEGIN
        CLOSE C_Kladde
        DEALLOCATE C_Kladde
		RETURN @m_RecIdStart
	  END
	  SET @m_RecIdStart = @m_RecId
	  SET @m_trnDatum = @m_Datum
	  SET @m_trnDatumsTyp = @m_DatumsTyp
	END

	-- fehlende Angaben in dieser Zeile
	IF (@m_GK_Typ = '' AND @m_GK_Kto <> '') OR (@m_Typ = '' AND @m_Kto <> '') OR (@m_Betrag = 0) OR (@m_GK_Typ = '' AND @m_Typ = '')
	   OR (@m_Datum < @m_minDatum) OR (@m_Datum <> @m_trnDatum) OR (@m_DatumsTyp <> @m_trnDatumsTyp) BEGIN
      CLOSE C_Kladde
      DEALLOCATE C_Kladde
      RETURN @m_RecId
	END

    IF @m_GK_Typ <> '' BEGIN
	  IF @m_GK_Kto = '' OR 0 = (SELECT count(*) FROM ut_Kontenplan WHERE Typ = @m_GK_Typ AND Konto = @m_GK_Kto) BEGIN
        CLOSE C_Kladde
        DEALLOCATE C_Kladde
        RETURN @m_RecId
	  END
	  SET @m_Saldo = ROUND(@m_Saldo - @m_Betrag, 2)
    END

    IF @m_Typ <> '' BEGIN
	  IF @m_Kto = '' OR 0 = (SELECT count(*) FROM ut_Kontenplan WHERE Typ = @m_Typ AND Konto = @m_Kto) BEGIN
        CLOSE C_Kladde
        DEALLOCATE C_Kladde
        RETURN @m_RecId
	  END
	  SET @m_Saldo = ROUND(@m_Saldo + @m_Betrag, 2)
	END

    FETCH NEXT FROM C_Kladde INTO @m_RecId, @m_Joined, @m_Typ, @m_Kto, @m_GK_Typ, @m_GK_Kto, @m_Betrag, @m_Datum, @m_DatumsTyp
  END
  CLOSE C_Kladde
  DEALLOCATE C_Kladde

  -- letzte Transaktion geht nicht auf
  IF @m_Saldo <> 0 BEGIN
    RETURN @m_RecIdStart
  END
  RETURN 0
END
GO

----------------------------------------------------------------------------------------------
-- Buchungszeile aus Kladde hinzufuegen
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'itcproc_Kladde_Buchen_Add_Detail')
  DROP PROCEDURE [dbo].[itcproc_Kladde_Buchen_Add_Detail]
GO
CREATE PROCEDURE [dbo].[itcproc_Kladde_Buchen_Add_Detail]  
  @m_TransNo char(8), @m_Typ char(1),  @m_Kto char(8), @m_Betrag float, @m_Invert integer, @m_Purpose integer, @m_RecIdKladde integer
AS
  IF @m_Invert <> 0 SET @m_Betrag = 0.00 - @m_Betrag

  DECLARE @m_Soll   float
  DECLARE @m_Haben  float
  DECLARE @m_BtrOrg float

  -- Achtung, die alte Buchungszeilen sind invers, d.h. Betrag < 0 --> Soll, daher hier alles nochmal umdrehen
  IF @m_Betrag < 0.00 BEGIN
    SELECT @m_Soll = 0.00, @m_Haben = 0.00 - @m_Betrag,  @m_BtrOrg = 0.00 - @m_Betrag
  END ELSE BEGIN
    SELECT @m_Soll = @m_Betrag, @m_Haben = 0.00,  @m_BtrOrg = 0.00 - @m_Betrag
  END

  DECLARE @m_BuchDatum  datetime
  DECLARE @m_DatumsTyp  integer
  DECLARE @m_BelegDatum datetime
  DECLARE @m_FaellDatum datetime
  DECLARE @m_Faellig    integer
  DECLARE @m_SkoFrist   integer
  DECLARE @m_SkoSatz    integer
  DECLARE @m_Text       varchar(50)
  DECLARE @m_Vorgang    varchar(20)
  DECLARE @m_Kst        varchar(20)
  DECLARE @m_Ktr        varchar(20)
  DECLARE @m_PrjNr      char(8)
  DECLARE @m_BelNr      char(8)
  DECLARE @m_ExtBelNr   varchar(20)

  SELECT @m_BuchDatum = BuchDatum, @m_BelegDatum = BelegDatum, @m_Faellig = Faellig, @m_Text = BuText, @m_SkoSatz = SkoSatz, @m_SkoFrist = SkoFrist,
         @m_Kst = Kst, @m_Ktr = Ktr, @m_PrjNr = PrjNr, @m_BelNr = BelNr , @m_ExtBelNr = ExtBelNr, @m_Vorgang = Vorgang, @m_DatumsTyp = DatumsTyp
  FROM dbo.ut_KladdenZeile WHERE RecId = @m_RecIdKladde
  -- verdichten der Personenposten zulassen, auch wenn mehrere Attribute vergeben werden
  IF @m_Typ <> 'S' BEGIN
    SET @m_Kst   = ''
    SET @m_Ktr   = ''
    SET @m_PrjNr = ''
  END

  DECLARE @m_NewRecId integer
  DECLARE @m_Jahr     char(4)
  DECLARE @m_Monat    char(2)
  DECLARE @m_Tag      char(2)
  SET @m_NewRecId = dbo.itcfunc_NextRecId_Buchungszeilen()
  SET @m_Jahr     = substring(convert(char(10),@m_BuchDatum,120),1,4)
  SET @m_Monat    = substring(convert(char(10),@m_BuchDatum,120),6,2)
  SET @m_Tag      = substring(convert(char(10),@m_BuchDatum,120),9,2)
  IF @m_DatumsTyp = 1 SET @m_Tag = '00'
  IF @m_DatumsTyp = 2 SET @m_Tag = '32'

  DECLARE @m_FJahr     char(4) = ''
  DECLARE @m_FMonat    char(2) = ''
  DECLARE @m_FTag      char(2) = ''
  IF convert(char(10),@m_BelegDatum,120) > '2000-01-01' BEGIN
    SET @m_FaellDatum = DATEADD(day,@m_Faellig,@m_BelegDatum)
    SET @m_FJahr     = substring(convert(char(10),@m_FaellDatum,120),1,4)
    SET @m_FMonat    = substring(convert(char(10),@m_FaellDatum,120),6,2)
    SET @m_FTag      = substring(convert(char(10),@m_FaellDatum,120),9,2)
  END

  DECLARE @m_Status1 integer
  SET @m_Status1 = 0
  IF @m_Purpose = 2 AND @m_Typ = 'P' SET @m_Status1 = 1 -- Kreditorenbuchung

  INSERT ut_Buchungszeilen (RecId,TransNo,Typ,Konto,Jahr,Monat,Tag,Soll,Haben,BtrOrg,BtrWae,FJahr,FMonat,FTag,SkoSatz,SkoFrist,
         [Text]  ,Kst,Ktr,PrjNr,BelNr,BelNrExt,BelDatum,StatusDate,Status1,Vorgang)
  VALUES(@m_NewRecId,@m_TransNo,@m_Typ,@m_Kto,@m_Jahr,@m_Monat,@m_Tag,@m_Soll,@m_Haben,@m_BtrOrg,'EUR',@m_FJahr,@m_FMonat,@m_FTag,@m_SkoSatz,@m_SkoFrist,
         @m_Text,@m_Kst,@m_Ktr,@m_PrjNr,@m_BelNr,@m_ExtBelNr,@m_BelegDatum,@m_BelegDatum,@m_Status1,@m_Vorgang)
GO

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

  DECLARE @m_RecId      integer
  DECLARE @m_LfdNr      integer
  DECLARE @m_Joined     integer 
  DECLARE @m_Purpose    integer 
  DECLARE @m_PurposeTRN integer -- Purpose des Startsatzes der letzten Transaktion
  DECLARE @m_Typ        char(1) 
  DECLARE @m_Kto        char(8)
  DECLARE @m_KtoUSt     char(8)
  DECLARE @m_GK_Typ     char(1)
  DECLARE @m_GK_Kto     char(8)
  DECLARE @m_GK_KtoUSt  char(8)
  DECLARE @m_Betrag     float
  DECLARE @m_MWSt       float
  DECLARE @m_Netto      float
  DECLARE @m_TransNo    char(8)
  DECLARE @m_PrjNr      char(8)
  DECLARE @m_BelArt     char(2)
  DECLARE @m_BelNr      char(8)
  DECLARE @m_BTrnRecId  integer

  DECLARE C_Kladde CURSOR STATIC LOCAL FOR 
  SELECT RecId, LfdNr, Joined, Purpose, Typ, Kto, KtoUSt, GK_Typ, GK_Kto, GK_KtoUSt, Betrag, MWSt, Netto from ut_KladdenZeile where Code = @param_Code and LfdNr >= @param_LfdNr and TransNo = '' order by LfdNr
  OPEN C_Kladde

  SET @m_TransNo = ''
  FETCH NEXT FROM C_Kladde INTO @m_RecId, @m_LfdNr, @m_Joined, @m_Purpose, @m_Typ, @m_Kto, @m_KtoUSt, @m_GK_Typ, @m_GK_Kto, @m_GK_KtoUSt, @m_Betrag, @m_MWSt, @m_Netto
  WHILE (@@FETCH_STATUS = 0) BEGIN
    -- Beginn einer Transaktion
    IF @m_Joined = 0 BEGIN
	  -- vorherige Transaktion abschliesen
      IF @m_TransNo <> '' BEGIN
		-- Ausgleichstransaktionen fuer Debitoren (3) oder Kreditoren (4) nicht verdichten
        IF (@m_PurposeTRN <> 3) AND (@m_PurposeTRN <> 4) AND (@param_Verdichten <> 0)
		  EXECUTE [dbo].[itcproc_Trn_Verdichten] @m_TransNo, 1
        EXECUTE [dbo].[itcproc_Trn_Saldieren] @m_TransNo
      END
	  SET @m_TransNo = ''

	  -- alle oder Filter auf eine bestimmte Transaktion
	  IF @param_LfdNr = 0 OR @param_LfdNr = @m_LfdNr BEGIN
        -- Belegtransaktion? dann fruehere gegenbuchen (Status = 0 in Status = 2 ueberführen, neue Transaktion hat Status = 3)
	    IF @m_Purpose = 1 BEGIN
	      SELECT @m_PrjNr = PrjNr, @m_BelArt = BelArt, @m_BelNr = BelNr from ut_KladdenZeile where RecId = @m_RecId
	      EXECUTE [dbo].[itcproc_BelTransactions_Stornieren] @m_PrjNr, @m_BelArt, @m_BelNr
		  -- print @m_PrjNr + ' ' + @m_BelArt + ' ' + @m_BelNr + ' alte stornieren'
	    END

        SET @m_TransNo    = dbo.itcfunc_Buchungszeilen_NextTransNo()
		SET @m_PurposeTRN = @m_Purpose

  	    -- neue Belegtransaktion registrieren
	    IF @m_Purpose = 1 BEGIN
		  SET @m_BTrnRecId = dbo.itcfunc_NextRecId_BelTransaction()
		  INSERT ut_BelTransaction (RecId, PrjNr, BelArt, BelNr, TransNo, [Status]) VALUES(@m_BTrnRecId, @m_PrjNr, @m_BelArt, @m_BelNr, @m_TransNo, '0')
		  -- print 'Belegtransaktion ' + @m_PrjNr + ' ' + @m_BelArt + ' ' + @m_BelNr + ' neue registrieren'
	    END
	  END -- IF @param_LfdNr = 0 OR @param_LfdNr = @m_LfdNr
	END -- IF @m_Joined = 0

    IF @m_TransNo <> '' BEGIN
	  -- Zahlungsausgleich fuer Projektbeleg
	  IF @m_Purpose = 3 BEGIN
	    SELECT @m_PrjNr = PrjNr, @m_BelArt = BelArt, @m_BelNr = BelNr from ut_KladdenZeile where RecId = @m_RecId
	    IF 0 = (SELECT count(*) FROM ut_BelTransaction WHERE PrjNr = @m_PrjNr AND BelArt = @m_BelArt AND BelNr = @m_BelNr AND TransNo = @m_TransNo AND [Status] = '1') BEGIN
		  SET @m_BTrnRecId = dbo.itcfunc_NextRecId_BelTransaction()
		  INSERT ut_BelTransaction (RecId, PrjNr, BelArt, BelNr, TransNo, [Status]) VALUES(@m_BTrnRecId, @m_PrjNr, @m_BelArt, @m_BelNr, @m_TransNo, '1')
		  -- print 'Zahlungsausgleich ' + @m_PrjNr + ' ' + @m_BelArt + ' ' + @m_BelNr + ' neue registrieren'
		END
	  END
	  -- Kreditorenpostenausgleich
	  IF @m_Purpose = 4 BEGIN
		EXECUTE [dbo].[itcproc_KreditorenPostenAusgleichVonKladdenZeile] @m_RecId, @m_TransNo
	  END

      IF @m_GK_Typ <> '' BEGIN
        IF @m_GK_KtoUSt <> '' AND @m_MWSt <> 0 AND @m_GK_Typ <> 'P'  BEGIN
		  EXECUTE [dbo].[itcproc_Kladde_Buchen_Add_Detail]  @m_TransNo, @m_GK_Typ, @m_GK_Kto,    @m_Netto,  1, @m_Purpose, @m_RecId
		  EXECUTE [dbo].[itcproc_Kladde_Buchen_Add_Detail]  @m_TransNo, @m_GK_Typ, @m_GK_KtoUSt, @m_MWSt,   1, @m_Purpose, @m_RecId
	    END ELSE BEGIN
		  EXECUTE [dbo].[itcproc_Kladde_Buchen_Add_Detail]  @m_TransNo, @m_GK_Typ, @m_GK_Kto,    @m_Betrag, 1, @m_Purpose, @m_RecId
	    END
	  END

      IF @m_Typ <> '' BEGIN
  	    IF @m_KtoUSt <> '' AND @m_MWSt <> 0 AND @m_Typ <> 'P' BEGIN
		  EXECUTE [dbo].[itcproc_Kladde_Buchen_Add_Detail]  @m_TransNo, @m_Typ, @m_Kto,    @m_Netto,  0, @m_Purpose, @m_RecId
		  EXECUTE [dbo].[itcproc_Kladde_Buchen_Add_Detail]  @m_TransNo, @m_Typ, @m_KtoUSt, @m_MWSt,   0, @m_Purpose, @m_RecId
	    END ELSE BEGIN
		  EXECUTE [dbo].[itcproc_Kladde_Buchen_Add_Detail]  @m_TransNo, @m_Typ, @m_Kto,    @m_Betrag, 0, @m_Purpose, @m_RecId
	    END
	  END

	  UPDATE ut_KladdenZeile SET TransNo = @m_TransNo WHERE RecId = @m_RecId
	END

    FETCH NEXT FROM C_Kladde INTO @m_RecId, @m_LfdNr, @m_Joined, @m_Purpose, @m_Typ, @m_Kto, @m_KtoUSt, @m_GK_Typ, @m_GK_Kto, @m_GK_KtoUSt, @m_Betrag, @m_MWSt, @m_Netto
  END
  -- letzte Transaktion abschliesen
  IF @m_TransNo <> '' BEGIN
    -- Ausgleichstransaktionen fuer Debitoren (3) oder Kreditoren (4) nicht verdichten
    IF (@m_PurposeTRN <> 3) AND (@m_PurposeTRN <> 4) AND (@param_Verdichten <> 0)
 	  EXECUTE [dbo].[itcproc_Trn_Verdichten] @m_TransNo, 1
    EXECUTE [dbo].[itcproc_Trn_Saldieren] @m_TransNo
  END

  CLOSE C_Kladde
  DEALLOCATE C_Kladde

  SET NOCOUNT OFF
  COMMIT TRANSACTION
GO

----------------------------------------------------------------------------------------------
-- Kladde abschliessen ohne weitere Buchungen
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'itcproc_Kladde_Abschliessen')
  DROP PROCEDURE [dbo].[itcproc_Kladde_Abschliessen]
GO
CREATE PROCEDURE [dbo].[itcproc_Kladde_Abschliessen] @param_Code char(20)
AS
  BEGIN TRANSACTION
  UPDATE ut_KladdenZeile SET TransNo = '00000000' WHERE Code = @param_Code AND TransNo = ''
  UPDATE ut_KladdenZeile SET VkzExport = 0        WHERE Code = @param_Code AND VkzExport = 1
  COMMIT TRANSACTION
GO

----------------------------------------------------------------------------------------------
-- Abstimmsumme fuer ein Konto kalkulieren
-- beinhaltet den letzten Saldo und alle nicht gebuchten Kladdenzeilen
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'itcproc_GetAbstimmSumme')
  DROP PROCEDURE [dbo].[itcproc_GetAbstimmSumme]
GO
CREATE PROCEDURE [dbo].[itcproc_GetAbstimmSumme] @param_Konto char(8)
AS
  DECLARE @mSaldo decimal(14,2) = ISNULL(
       (SELECT TOP 1 -SaldoNeu FROM dbo.ut_Buchungszeilen WHERE Typ = 'S' AND Konto = @param_Konto 
       ORDER BY Jahr desc, Monat desc, Tag desc, TransNo desc, RecId desc)
	 , 0)

  DECLARE @mToAdd decimal(14,2) = ISNULL(
      (SELECT SUM(Betrag) AS AbstimmSumme FROM dbo.ut_KladdenZeile WHERE Typ = 'S' AND Kto = @param_Konto AND TransNo = '')
	 , 0)

  DECLARE @mToSub decimal(14,2) = ISNULL(
	  (SELECT SUM(Betrag) AS AbstimmSumme FROM dbo.ut_KladdenZeile WHERE GK_Typ = 'S' AND GK_Kto = @param_Konto AND TransNo = '')
	 , 0)

  SELECT @mSaldo + @mToAdd - @mToSub AS AbstimmSumme
GO

----------------------------------------------------------------------------------------------
