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

----------------------------------------------------------------------------------------------
-- Lagerausgangsschein aufsummieren
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P' and [name] = 'itcproc_LagerAb_Calculate')
  DROP PROCEDURE [dbo].[itcproc_LagerAb_Calculate]
GO
CREATE PROCEDURE [dbo].[itcproc_LagerAb_Calculate]
  @Parm_BelNr integer  
AS
  SET NOCOUNT ON

  DECLARE @m_Zu float
  DECLARE @m_Ab float
  select @m_Zu = ISNULL(SUM(NettoGes),0) from dbo.ut_LagerEinPos where BelTyp = 'A' AND BelNr = @Parm_BelNr AND ZuAb = 'Z'
  select @m_Ab = ISNULL(SUM(NettoGes),0) from dbo.ut_LagerEinPos where BelTyp = 'A' AND BelNr = @Parm_BelNr AND ZuAb = 'A'

  DECLARE @m_Summe float = ROUND(@m_Zu - @m_Ab, 2)
  DECLARE @m_Brutto float = @m_Summe

  -- Umsatzsteuer berechnen, wenn Verkauskonto angegeben
  UPDATE dbo.ut_LagerEinUst SET Netto = 0, Ust = 0, Brutto = 0, UstKorrekt = 0 WHERE BelTyp = 'A' AND BelNr = @Parm_BelNr

  DECLARE @m_UstSchl varchar(3)
  DECLARE @m_RecId integer
  DECLARE @m_DebKonto varchar(8)
  DECLARE @m_SachKonto varchar(8)
  select @m_DebKonto  = RTRIM(ISNULL(KredKto,'')) from dbo.ut_LagerEinKopf WHERE Typ = 'A' AND BelNr = @Parm_BelNr
  select @m_SachKonto = RTRIM(ISNULL(OptDataC,'')) from dbo.ut_Optionen WHERE OptId = ' 6218'
  select @m_UstSchl = ISNULL(Schl,'') from dbo.ut_Kontenplan where Typ = 'S' and Konto = @m_SachKonto

  IF @m_DebKonto <> '' AND @m_SachKonto <> '' AND @m_UstSchl <> '' BEGIN
    SET @m_RecId = NULL
    SELECT @m_RecId = RecId FROM dbo.ut_LagerEinUst WHERE BelTyp = 'A' AND BelNr = @Parm_BelNr AND UstSchl = @m_UstSchl
    -- Datensatz einfuegen, falls noch nicht vorhanden
	IF @m_RecId IS NULL BEGIN
	  SET @m_RecId = dbo.itcfunc_NextRecId_LagerEinUst()
	  INSERT INTO dbo.ut_LagerEinUst (RecId, BelTyp, BelNr, UstSchl) VALUES (@m_RecId, 'A', @Parm_BelNr, @m_UstSchl)
	END

    DECLARE @m_VkZu float
    DECLARE @m_VkAb float
    select @m_VkZu = ISNULL(SUM(VkGesamt),0) from dbo.ut_LagerEinPos where BelTyp = 'A' AND BelNr = @Parm_BelNr AND ZuAb = 'Z'
    select @m_VkAb = ISNULL(SUM(VkGesamt),0) from dbo.ut_LagerEinPos where BelTyp = 'A' AND BelNr = @Parm_BelNr AND ZuAb = 'A'
    DECLARE @m_VkSumme float = ROUND(@m_VkZu - @m_VkAb, 2)

    UPDATE lgeu SET 
	      Netto  = @m_VkSumme
		 ,UstVH  = ISNULL(kp.AutoSatz,0)
		 ,Ust    = ROUND(@m_VkSumme * ISNULL(kp.AutoSatz,0) / 100, 2)
		 ,Brutto = @m_VkSumme + ROUND(@m_VkSumme * ISNULL(kp.AutoSatz,0) / 100, 2)
 	    FROM dbo.ut_LagerEinUst lgeu 
	    LEFT JOIN dbo.ut_Kontenplan kp on kp.Typ = 'S' AND kp.KzKasse = 'U' AND kp.KzUst = 'U' AND kp.Schl = lgeu.UstSchl AND lgeu.UstSchl <> ''
	    WHERE lgeu.RecId = @m_RecId

    select @m_Brutto = Brutto from dbo.ut_LagerEinUst where RecId = @m_RecId
  END

  -- Ueberfluessige Umsatzsteuerzeilen entfernen und Summen aktualisieren
  DELETE FROM dbo.ut_LagerEinUst WHERE BelTyp = 'A' AND BelNr = @Parm_BelNr AND Netto = 0
  UPDATE ut_LagerEinKopf SET SummeNetto = @m_Summe, SummeBrutto = @m_Brutto WHERE Typ = 'A' AND BelNr = @Parm_BelNr
  SELECT @m_Summe AS Netto
GO

----------------------------------------------------------------------------------------------
-- Lagerausgangsschein Verbrauch in Kladdenzeilen kontieren
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P' and [name] = 'itcproc_LagerAb_Kontieren_Verbrauch')
  DROP PROCEDURE [dbo].[itcproc_LagerAb_Kontieren_Verbrauch]
GO
CREATE PROCEDURE [dbo].[itcproc_LagerAb_Kontieren_Verbrauch]
  @Param_BelNr       integer  
 ,@Param_KladdenCode varchar(20)
 ,@Param_StartLfdNr  integer
 ,@Param_NextLfdNr   integer
 ,@Param_VkzExport   integer
 ,@Param_BelegDatum  datetime
 ,@Param_BuchDatum   datetime
 ,@Param_ExtBelNr    varchar(20)
 ,@Param_BuchText    varchar(50)
AS
  SET NOCOUNT ON

  DECLARE @m_RecId      integer
  DECLARE @m_NextLfdNr  integer = @Param_NextLfdNr
  DECLARE @m_Joined     integer = 1
  IF @Param_StartLfdNr = @m_NextLfdNr
    SET @m_Joined = 0

  DECLARE @m_KtoVerbrauch varchar(8) = (SELECT OptDataC FROM dbo.ut_Optionen WHERE OptId = ' 6214')
  IF ISNULL(@m_KtoVerbrauch,'') = '' BEGIN
    SELECT 0 as success, 'Das Standardverbrauchskonto ist nicht festgelegt.' as msg
	RETURN 0
  END
  -- nicht ausgefuellte Verbrauchskonten ersetzen
  UPDATE dbo.ut_LagerEinPos SET VerbrKto = @m_KtoVerbrauch where BelTyp = 'A' AND BelNr = @Param_BelNr AND VerbrKto = ''

  DECLARE @m_KtoLager varchar(8) = (SELECT OptDataC FROM dbo.ut_Optionen WHERE OptId = ' 6213')
  IF ISNULL(@m_KtoLager,'') = '' BEGIN
    SELECT 0 as success, 'Das Standardlagerkonto ist nicht festgelegt.' as msg
	RETURN 0
  END
  -- nicht ausgefuellte Lagerkonten ersetzen
  UPDATE dbo.ut_LagerEinPos SET LgKto = @m_KtoLager where BelTyp = 'A' AND BelNr = @Param_BelNr AND LgKto = ''

  -- Positionen ohne Lager zuruecksetzen
  UPDATE dbo.ut_LagerEinPos SET LgKto = '', VerbrKto = '' 
    where BelTyp = 'A' AND BelNr = @Param_BelNr
	AND (0 = (SELECT count(*) FROM dbo.ut_Stammpositionen s WHERE s.Lkz = dbo.ut_LagerEinPos.Lkz AND s.Artnr = dbo.ut_LagerEinPos.Artnr AND s.Kzlager = 'J'))

  -- ----------------------------------------------- Debitorenposten buchen ------------
  DECLARE @m_Prj     varchar(8)
  DECLARE @m_Kst     varchar(8)
  DECLARE @m_Netto   float
  DECLARE @m_Mwst    float
  DECLARE @m_Brutto  float
  DECLARE @m_BruttoGesamt decimal(14,2) = 0
  DECLARE @m_UstSchl varchar(3)
  DECLARE @m_UstSatz float
  DECLARE @m_UstKonto varchar(8)

  DECLARE @m_VerkaufAnKto  varchar(8)
  DECLARE @m_VerkaufAnTyp  varchar(1)
  DECLARE @m_ErloesKto  varchar(8)
  select @m_VerkaufAnKto = RTRIM(KredKto), @m_Prj = PrjNr from dbo.ut_LagerEinKopf where Typ = 'A' and BelNr = @Param_BelNr
  select TOP 1 @m_VerkaufAnTyp = ISNULL(Typ,'S') FROM dbo.ut_Kontenplan where Konto = @m_VerkaufAnKto AND (Typ = 'P' OR Typ = 'S')
  select @m_ErloesKto = OptDataC from dbo.ut_Optionen where OptId = ' 6218'

  IF @m_VerkaufAnKto <> '' BEGIN
    DECLARE CUmsatz CURSOR LOCAL FOR SELECT Netto, UstSchl, Ust, Brutto FROM dbo.ut_LagerEinUst WHERE BelTyp = 'A' AND BelNr = @Param_BelNr
	OPEN CUmsatz
    FETCH NEXT FROM CUmsatz INTO @m_Netto, @m_UstSchl, @m_Mwst, @m_Brutto
    WHILE @@FETCH_STATUS = 0 BEGIN
	  select @m_UstKonto = Konto, @m_UstSatz = AutoSatz from dbo.ut_Kontenplan where Typ = 'S' AND KzKasse = 'U' AND KzUst = 'U' AND Schl = @m_UstSchl

      SET @m_RecId = dbo.itcfunc_NextRecId_KladdenZeile()
      INSERT INTO dbo.ut_KladdenZeile (RecId, Code, LfdNr, Joined, Purpose) VALUES (@m_RecId, @Param_KladdenCode, @m_NextLfdNr, @m_Joined, 0)
      SET @m_NextLfdNr = @m_NextLfdNr + 1
      SET @m_Joined = 1

      UPDATE dbo.ut_KladdenZeile SET
  		  BelArt = '42'
	     ,BelNr = STR(@Param_BelNr,8)
	     ,ExtBelNr = @Param_ExtBelNr
	     ,BelegDatum =  @Param_BelegDatum
	     ,BuchDatum =  @Param_BuchDatum
	     ,VkzExport = @Param_VkzExport
	     --
         ,Betrag = @m_Brutto
		 ,KtoUSt = @m_UstKonto
		 ,UStSatz = @m_UstSatz
	     ,MWSt = @m_Mwst
	     ,Netto = @m_Netto
	     ,Typ = 'S'
	     ,Kto = @m_ErloesKto
	     ,GK_Typ = @m_VerkaufAnTyp
	     ,GK_Kto = @m_VerkaufAnKto
	     -- ,SchlDatev = @m_UstSchl
	     ,PrjNr = @m_Prj
	     ,Kst = @m_Kst
	     ,Ktr = @m_Prj
	     ,BuText = @Param_BuchText
      WHERE dbo.ut_KladdenZeile.RecId = @m_RecId
	  SET @m_BruttoGesamt = @m_BruttoGesamt + @m_Brutto

      FETCH NEXT FROM CUmsatz INTO @m_Netto, @m_UstSchl, @m_Mwst, @m_Brutto
	END
    CLOSE CUmsatz
    DEALLOCATE CUmsatz
  END

  -- ----------------------------------------------- Verbrauchsposten buchen ------------
  DECLARE @m_VonKto  varchar(8)
  DECLARE @m_AufKto  varchar(8)
  DECLARE @m_KeinVerbrauch integer
  select @m_KeinVerbrauch = OptDataI from dbo.ut_Optionen where OptId = ' 6220'

  IF ISNULL(@m_KeinVerbrauch, 0) = 0 BEGIN
	  DECLARE CVerbrauch CURSOR LOCAL  FOR SELECT LgKto, VerbrKto, PrjNr, Kst, SUM(Netto) FROM
		   (select ZuAb, LgKto, VerbrKto, PrjNr, Kst, CASE WHEN ZuAb = 'A' THEN 0.0 - NettoGes ELSE NettoGes END AS Netto from ut_LagerEinPos p
  		      where BelTyp = 'A' AND BelNr = @Param_BelNr AND (1 = (SELECT count(*) FROM dbo.ut_Stammpositionen s WHERE s.Lkz = p.Lkz AND s.Artnr = p.Artnr AND s.Kzlager = 'J'))
		   ) Lagerposten
		group by LgKto, VerbrKto, PrjNr, Kst
	  OPEN CVerbrauch
	  FETCH NEXT FROM CVerbrauch INTO @m_VonKto, @m_AufKto, @m_Prj, @m_Kst, @m_Netto
	  WHILE @@FETCH_STATUS = 0 BEGIN
		------------------------------------------------------- 
		IF @m_VonKto <> @m_AufKto AND ROUND(@m_Netto, 2) <> 0.00 BEGIN
		  SET @m_RecId = dbo.itcfunc_NextRecId_KladdenZeile()
		  INSERT INTO dbo.ut_KladdenZeile (RecId, Code, LfdNr, Joined, Purpose) VALUES (@m_RecId, @Param_KladdenCode, @m_NextLfdNr, @m_Joined, 0)
		  SET @m_NextLfdNr = @m_NextLfdNr + 1
		  SET @m_Joined = 1

		  UPDATE dbo.ut_KladdenZeile SET
  			  BelArt = '42'
			 ,BelNr = STR(@Param_BelNr,8)
			 ,ExtBelNr = @Param_ExtBelNr
			 ,BelegDatum =  @Param_BelegDatum
			 ,BuchDatum =  @Param_BuchDatum
			 ,VkzExport = @Param_VkzExport
			 --
			 ,Betrag = @m_Netto
			 ,MWSt = 0
			 ,Netto = @m_Netto
			 ,Typ = 'S'
			 ,Kto = @m_VonKto
			 ,GK_Typ = 'S'
			 ,GK_Kto = @m_AufKto
			 ,SchlDatev = '40'
			 ,PrjNr = @m_Prj
			 ,Kst = @m_Kst
			 ,Ktr = @m_Prj
			 ,BuText = @Param_BuchText
		  WHERE dbo.ut_KladdenZeile.RecId = @m_RecId
		END 

		FETCH NEXT FROM CVerbrauch INTO @m_VonKto, @m_AufKto, @m_Prj, @m_Kst, @m_Netto
	  END
	  CLOSE CVerbrauch
	  DEALLOCATE CVerbrauch
  END

  -- ----------------------------------------------- Ausgleichsposten buchen ------------
  DECLARE @m_AusgleichsKonto varchar(8)
  select @m_AusgleichsKonto = RTRIM(VerbrKonto) from dbo.ut_LagerEinKopf where Typ = 'A' and BelNr = @Param_BelNr

  IF @m_VerkaufAnKto <> '' AND @m_AusgleichsKonto <> '' BEGIN
      SET @m_RecId = dbo.itcfunc_NextRecId_KladdenZeile()
      INSERT INTO dbo.ut_KladdenZeile (RecId, Code, LfdNr, Joined, Purpose) VALUES (@m_RecId, @Param_KladdenCode, @m_NextLfdNr, @m_Joined, 0)
      SET @m_NextLfdNr = @m_NextLfdNr + 1
      SET @m_Joined = 1

      UPDATE dbo.ut_KladdenZeile SET
  		  BelArt = '42'
	     ,BelNr = STR(@Param_BelNr,8)
	     ,ExtBelNr = @Param_ExtBelNr
	     ,BelegDatum =  @Param_BelegDatum
	     ,BuchDatum =  @Param_BuchDatum
	     ,VkzExport = @Param_VkzExport
	     --
         ,Betrag = -@m_BruttoGesamt
		 --,KtoUSt = ''
		 --,UStSatz = 0
	     --,MWSt = 0
	     ,Netto = -@m_BruttoGesamt
	     ,Typ = 'S'
	     ,Kto = @m_AusgleichsKonto
	     ,GK_Typ = @m_VerkaufAnTyp
	     ,GK_Kto = @m_VerkaufAnKto
	     -- ,SchlDatev = @m_UstSchl
 		 -- ,PrjNr = @m_Prj
		 -- ,Kst = @m_Kst
		 -- ,Ktr = @m_Prj
	     ,BuText = @Param_BuchText
      WHERE dbo.ut_KladdenZeile.RecId = @m_RecId
  END

  RETURN @m_NextLfdNr
GO

----------------------------------------------------------------------------------------------
-- Lagerausgangsschein Lagerbewegungen buchen
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P' and [name] = 'itcproc_LagerAb_Bewegungen_Buchen')
  DROP PROCEDURE [dbo].[itcproc_LagerAb_Bewegungen_Buchen]
GO
CREATE PROCEDURE [dbo].[itcproc_LagerAb_Bewegungen_Buchen]
  @Param_BelNr       integer  
 ,@Param_BuchDatum   datetime
 ,@Param_ExtBelNr    varchar(20)
 ,@Param_BuchText    varchar(50)
AS
  SET NOCOUNT ON

  DECLARE @m_Lkz varchar(2)
  DECLARE @m_Artnr varchar(18)
  DECLARE @m_KzZuAb varchar(1)
  DECLARE @m_Menge float
  DECLARE @m_EkPreis float
  DECLARE @m_Gebinde float
  DECLARE @m_Wert float
  DECLARE @m_PrjNr varchar(8)
  DECLARE @m_BelNr varchar(8) = STR(@Param_BelNr, 8)

  DECLARE CPos CURSOR LOCAL FOR SELECT Lkz, Artnr, ZuAb,  Menge, NettoEk, PreisJe, NettoGes, PrjNr FROM dbo.ut_LagerEinPos 
    WHERE BelTyp = 'A' AND BelNr = @Param_BelNr ORDER BY PosNr, UPosNr
  OPEN CPos
  FETCH NEXT FROM CPos INTO @m_Lkz, @m_Artnr, @m_KzZuAb, @m_Menge, @m_EkPreis, @m_Gebinde, @m_Wert, @m_PrjNr
  WHILE @@FETCH_STATUS = 0 BEGIN
    IF (ROUND(@m_Menge,2) <> 0) AND (1 = (SELECT count(*) FROM dbo.ut_Stammpositionen WHERE Lkz = @m_Lkz AND Artnr = @m_Artnr AND Kzlager = 'J')) BEGIN
        EXECUTE [dbo].[itcproc_Lagerbewegung_Buchen2]  @m_Lkz, @m_Artnr, @m_KzZuAb, @Param_BuchDatum,
          @m_Menge, @m_EkPreis, @m_Gebinde, @m_Wert, @m_PrjNr, '42', @m_BelNr, @Param_ExtBelNr, @Param_BuchText
	    UPDATE dbo.ut_LagerEinPos SET VerbMenge = @m_Menge, VerbNetto = @m_Wert WHERE CURRENT OF CPos
	  END
    FETCH NEXT FROM CPos INTO @m_Lkz, @m_Artnr, @m_KzZuAb, @m_Menge, @m_EkPreis, @m_Gebinde, @m_Wert, @m_PrjNr
  END

  CLOSE CPos
  DEALLOCATE CPos
GO

----------------------------------------------------------------------------------------------
-- Lagerausgangsschein in Kladdenzeilen kontieren
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P' and [name] = 'itcproc_LagerAb_Kontieren')
  DROP PROCEDURE [dbo].[itcproc_LagerAb_Kontieren]
GO
CREATE PROCEDURE [dbo].[itcproc_LagerAb_Kontieren]
  @Param_BelNr integer  
 ,@Param_bNurLager integer = 0
AS
  SET NOCOUNT ON

  -- Test, ob alle Positionen einem gültigem Artikel zugeordnet sind
  --IF 0 < (select count(*) from dbo.ut_LagerEinPos p
  --        left join dbo.ut_Stammpositionen s on s.Lkz = p.Lkz and s.Artnr = p.Artnr
  --        where BelTyp = 'A' AND BelNr = @Param_BelNr and s.Artnr IS NULL) BEGIN
  --  SELECT 0 as success, 'Nicht alle Positionen konnten einem Artikel zugeordnet werden.' as msg
  --  RETURN -1
  --END

  -- Vorlaufdaten ermitteln
  DECLARE @m_ExtBelNr   varchar(20)
  DECLARE @m_Datum       datetime
  DECLARE @m_KladdenCode varchar(20)
  DECLARE @m_BuText varchar(50)
  DECLARE @m_ArtikelPosten integer
  SELECT 
        @m_ExtBelNr = ExtBelnr
	   ,@m_Datum = Datum
	   ,@m_KladdenCode = KladCode 
	   ,@m_BuText = BuText
	   ,@m_ArtikelPosten = ArtPosten
    FROM dbo.ut_LagerEinKopf WHERE Typ = 'A' AND BelNr = @Param_BelNr

  BEGIN TRANSACTION

  -- leeren Kladdencode durch Standard ersetzen
  IF ISNULL(@m_KladdenCode,'') = '' BEGIN
    SET @m_KladdenCode = 'LAGER'
	UPDATE dbo.ut_LagerEinKopf SET KladCode = @m_KladdenCode WHERE BelNr = @Param_BelNr
  END

  -- leeren Buchungstext ersetzen
  IF ISNULL(@m_BuText,'') = '' BEGIN
    SET @m_BuText = 'Lagerbeleg ' + STR(@Param_BelNr)
	UPDATE dbo.ut_LagerEinKopf SET BuText = @m_BuText WHERE BelNr = @Param_BelNr
  END

  -- Exportstatus ermitteln
  DECLARE @m_VkzExport integer
  SELECT @m_VkzExport = VkzExport FROM dbo.ut_KladdenKopf WHERE Code = @m_KladdenCode
  IF @m_VkzExport IS NULL 
    SET @m_VkzExport = 1

  -- Satznummer und laufende Nummer der ersten Kladdenzeile ermitteln
  --DECLARE @m_RecId     integer
  DECLARE @m_LfdNr     integer
  SELECT 
       @m_LfdNr = ISNULL(MAX(LfdNr),0) + 1
	  --,@m_RecId = dbo.itcfunc_NextRecId_KladdenZeile()
    FROM [dbo].[ut_KladdenZeile] WHERE Code = @m_KladdenCode

  -- Startnummer merken
  DECLARE @m_LfdNrStart integer = @m_LfdNr

  -- ----------------------------------------------- Verbrauchsposten buchen ------------
  IF @Param_bNurLager = 0 BEGIN
    EXECUTE @m_LfdNr = [dbo].[itcproc_LagerAb_Kontieren_Verbrauch] @Param_BelNr, @m_KladdenCode, @m_LfdNrStart, @m_LfdNr, @m_VkzExport, @m_Datum, @m_Datum, @m_ExtBelNr, @m_BuText
    IF @m_LfdNr <= 0 BEGIN
	  ROLLBACK TRANSACTION
      RETURN -1
    END
    IF @m_LfdNrStart = @m_LfdNr BEGIN
      ROLLBACK TRANSACTION
      SELECT 0 as success, 'Es wurde nichts gebucht.' as msg
	  RETURN -1
    END
  END
  -- ----------------------------------------------- Lagerposten im Artikelstamm zu-/abtragen
  IF @m_ArtikelPosten = 1
    EXECUTE [itcproc_LagerAb_Bewegungen_Buchen] @Param_BelNr, @m_Datum, @m_ExtBelNr, @m_BuText
  -- ----------------------------------------------- Kladdenreferenz merken
  IF @Param_bNurLager <> 0
    UPDATE ut_LagerEinKopf SET KladCode = '', KladLfdNr = 0, TransNo = '00000000' WHERE Typ = 'A' AND BelNr = @Param_BelNr
  ELSE
    UPDATE ut_LagerEinKopf SET KladCode = @m_KladdenCode, KladLfdNr = @m_LfdNrStart WHERE Typ = 'A' AND BelNr = @Param_BelNr

  COMMIT TRANSACTION
  SELECT 1 as success, '' as msg
  RETURN 0
GO
--SELECT * FROM ut_KladdenZeile WHERE Code = 'LAGER'
--DELETE FROM ut_KladdenZeile WHERE Code = 'LAGER'
--UPDATE ut_LagerEinKopf SET KladLfdNr = 0, TransNo = '' WHERE BelNr = 80000032
--EXECUTE [dbo].[itcproc_LagerAb_Kontieren] '80000041', 0
