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

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

  -- Werte der Lagerbewegung ermitteln
  DECLARE @m_Lkz     varchar(2)
  DECLARE @m_Artnr   varchar(18)
  DECLARE @m_Datum   datetime
  DECLARE @m_KZ_ZA   varchar(1)
  DECLARE @m_Menge   float
  DECLARE @m_EkPreis float
  DECLARE @m_Gebinde float
  DECLARE @m_Wert    float
  DECLARE @m_Ktxt1   varchar(42)

  SELECT @m_Lkz = RTRIM(SUBSTRING(ARTNR,1,2)), @m_Artnr = RTRIM(SUBSTRING(ARTNR,3,18)), @m_Datum = CONVERT(datetime,JAHR + MONAT + TAG,112)
       , @m_KZ_ZA = KZ_ZA, @m_Menge = MENGE, @m_EkPreis = EKPREIS, @m_Gebinde = GEBINDE, @m_Ktxt1 = Bez1 
    FROM ut_Lagerbewegungen WHERE RecId = @Parm_RecId
  IF ROUND(@m_Menge, 2) = 0.00 AND @m_KZ_ZA <> '3' BEGIN
    DELETE FROM dbo.ut_Lagerbewegungen WHERE RecId = @Parm_RecId
	RETURN -1
  END
  IF @m_Gebinde <= 0.0001
    SET @m_Gebinde = 1
  IF @m_EkPreis <= 0.001
    SET @m_EkPreis = 0

  IF @m_KZ_ZA = '1' OR @m_KZ_ZA = 'Z' BEGIN
	SET @m_KZ_ZA = '1'
    IF ROUND(@m_Menge, 2) < 0.00 BEGIN
	  SET @m_Menge = ABS(@m_Menge)
	  SET @m_KZ_ZA = '2'
	END
  END ELSE IF @m_KZ_ZA = '2' OR @m_KZ_ZA = 'A' BEGIN
	SET @m_KZ_ZA = '2'
    IF ROUND(@m_Menge, 2) < 0.00 BEGIN
	  SET @m_Menge = ABS(@m_Menge)
	  SET @m_KZ_ZA = '1'
	END
  END

  -- Werte der Stammposition ermitteln
  DECLARE @m_StammEkPreis float
  DECLARE @m_DurchEk      float
  DECLARE @m_StammGebinde float
  DECLARE @m_StammFaktor  float
  DECLARE @m_Bestand      float
  DECLARE @m_LagerWert    float
  DECLARE @m_StammKtxt1   varchar(42)

  SELECT @m_StammEkPreis = Ekmat, @m_DurchEk = Durchek, @m_StammGebinde = Gebinde, @m_StammKtxt1 = Bez1
       , @m_StammFaktor = pe.Faktor, @m_Bestand = Lagbest, @m_LagerWert = Lagerwert
	FROM dbo.ut_Stammpositionen sp LEFT JOIN dbo.ut_Preiseinheiten pe ON pe.Nr = STR(sp.Pe,2) WHERE Lkz = @m_Lkz AND Artnr = @m_Artnr
  IF @m_StammGebinde <= 0.0001
    SET @m_StammGebinde = 1
  IF @m_StammFaktor <= 0.0001
    SET @m_StammFaktor = 1
  IF RTRIM(@m_Ktxt1) = ''
    SET @m_Ktxt1 = @m_StammKtxt1

  -- ------------------------------------------------------------------ Zugangsposten
  IF @m_KZ_ZA = '1' BEGIN
    IF ROUND(@m_EkPreis,2) <= 0.00 BEGIN
      SET @m_EkPreis = @m_StammEkPreis
	  SET @m_Gebinde = @m_StammGebinde * @m_StammFaktor
    END

    -- Bestand und Lagerwert fortschreiben	
	IF ROUND(@m_Bestand, 2) <= 0.00 BEGIN -- Bestand 0 oder negativ, dann neuen Wert bilden
	  SET @m_Bestand = @m_Bestand + @m_Menge
	  SET @m_LagerWert = ROUND(@m_Bestand * @m_EkPreis / @m_Gebinde, 2)
	  SET @m_DurchEk   = ROUND(@m_EkPreis / @m_Gebinde, 6)
	END ELSE BEGIN -- Bestand erhoehen, neuen Wert und Durchschnittberechnen
	  SET @m_Bestand = @m_Bestand + @m_Menge
	  SET @m_LagerWert = ROUND(@m_LagerWert + (@m_Menge * @m_EkPreis / @m_Gebinde), 2) 
	  SET @m_DurchEk   = ROUND(@m_LagerWert / @m_Bestand, 6)
	END

	UPDATE dbo.ut_Stammpositionen SET
      Kzlager = 'J', Datumlager = @m_Datum, Lagbest = @m_Bestand,Lagerwert = @m_LagerWert, Durchek = @m_DurchEk, Wekum = ROUND(Wekum + @m_Menge, 3)
	  WHERE Lkz = @m_Lkz AND Artnr = @m_Artnr
	UPDATE dbo.ut_Lagerbewegungen SET
	  KZ_ZA = '1', ZuBuchen = 0, VERBUCHT = 1, EKPREIS = @m_EkPreis, GEBINDE = @m_Gebinde, Wert = ROUND(@m_Menge * @m_EkPreis / @m_Gebinde, 2), Bez1 = @m_Ktxt1
	  WHERE RecId = @Parm_RecId
    RETURN 1
  END

  -- ------------------------------------------------------------------ Abgangsposten
  IF @m_KZ_ZA = '2' BEGIN
    -- es ist kein Preis vorgegeben
    IF ROUND(@m_EkPreis,2) <= 0.00 BEGIN
      SET @m_EkPreis = @m_DurchEk
	  SET @m_Gebinde = @m_StammGebinde * @m_StammFaktor
    END
    -- es ist kein Preis vorgegeben und auch noch kein Durchschitt vorhanden
    IF ROUND(@m_EkPreis,2) <= 0.00 BEGIN
      SET @m_EkPreis = @m_StammEkPreis
	  SET @m_Gebinde = @m_StammGebinde * @m_StammFaktor
    END

    -- Bestand und Lagerwert veringern
    SET @m_Bestand = @m_Bestand - @m_Menge
	SET @m_LagerWert = ROUND(@m_LagerWert - (@m_Menge * @m_EkPreis / @m_Gebinde), 2) 
	SET @m_DurchEk   = ROUND(@m_LagerWert / @m_Bestand, 6)

	UPDATE dbo.ut_Stammpositionen SET
      Kzlager = 'J', Datumlager = @m_Datum, Lagbest = @m_Bestand,Lagerwert = @m_LagerWert, Durchek = @m_DurchEk, Wakum = ROUND(Wakum + @m_Menge, 3)
	  WHERE Lkz = @m_Lkz AND Artnr = @m_Artnr
	UPDATE dbo.ut_Lagerbewegungen SET
	  KZ_ZA = '2', ZuBuchen = 0, VERBUCHT = 1, EKPREIS = @m_EkPreis, GEBINDE = @m_Gebinde, Wert = ROUND(@m_Menge * @m_EkPreis / @m_Gebinde, 2), Bez1 = @m_Ktxt1
	  WHERE RecId = @Parm_RecId
    RETURN 1
  END

  -- ------------------------------------------------------------------ Reservierungsposten
  IF @m_KZ_ZA = '4' BEGIN
	UPDATE dbo.ut_Stammpositionen SET Kzlager = 'J', Resbest = Resbest + @m_Menge WHERE Lkz = @m_Lkz AND Artnr = @m_Artnr
	UPDATE dbo.ut_Lagerbewegungen SET ZuBuchen = 0, VERBUCHT = 1  WHERE RecId = @Parm_RecId
    RETURN 1
  END

  RETURN 0
GO
----------------------------------------------------------------------------------------------
-- Lagerbewegungssatz verbuchen
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P' and [name] = 'itcproc_Lagerbewegung_Buchen2')
  DROP PROCEDURE [dbo].[itcproc_Lagerbewegung_Buchen2]
GO
CREATE PROCEDURE [dbo].[itcproc_Lagerbewegung_Buchen2]
  @Param_Lkz char(2)
 ,@Param_Artnr varchar(18)
 ,@Param_KzZuAb varchar(1)
 ,@Param_Datum datetime
 ,@Param_Menge float
 ,@Param_EkPreis float
 ,@Param_Gebinde float
 ,@Param_Wert float
 ,@Param_PrjNr varchar(8)
 ,@Param_BelArt varchar(2)
 ,@Param_BelNr varchar(8)
 ,@Param_ExtBelNr varchar(20)
 ,@Param_BuText varchar(50)
AS
  SET NOCOUNT ON

  DECLARE @m_Datum char(8) = convert(char(8), @Param_Datum, 112)
  DECLARE @m_RecId integer = dbo.itcfunc_NextRecId_Lagerbewegungen()
  DECLARE @m_Info varchar(20) = @Param_ExtBelNr
  IF @m_Info = ''
    SET @m_Info = RTRIM(LTRIM(@Param_BelArt)) + '/' + LTRIM(@Param_BelNr)

  INSERT INTO dbo.ut_Lagerbewegungen (RecId, ARTNR, TAG, TAGDUMMY, MONAT, MONDUMMY, JAHR
         , KZ_ZA, ZuBuchen, MENGE, UrsprMenge, EKPREIS, GEBINDE, Wert
         , PrjNr, BelArt, BelNr, INFO, Bemerkung
         )
  VALUES ( @m_RecId, @Param_Lkz + @Param_Artnr, SUBSTRING(@m_Datum, 7, 2), '.', SUBSTRING(@m_Datum, 5, 2), '.', SUBSTRING(@m_Datum, 1, 4)
         , @Param_KzZuAb, 1, @Param_Menge, @Param_Menge, @Param_EkPreis, @Param_Gebinde, @Param_Wert
         , @Param_PrjNr, @Param_BelArt, @Param_BelNr, @m_Info, SUBSTRING(@Param_BuText, 1, 40)
         )

  DECLARE @m_Result integer
  EXECUTE @m_Result = [dbo].[itcproc_Lagerbewegung_Buchen] @m_RecId
  RETURN @m_Result
GO 
----------------------------------------------------------------------------------------------
-- Lagerbewegungssatz in FibuKladde kontieren
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P' and [name] = 'itcproc_Lagerbewegung_Kontieren')
  DROP PROCEDURE [dbo].[itcproc_Lagerbewegung_Kontieren]
GO
CREATE PROCEDURE [dbo].[itcproc_Lagerbewegung_Kontieren]
  @Param_RecId      integer,
  @Param_BuchKladde varchar(20)
AS
  DECLARE @m_KZ_ZA  varchar(1)
  DECLARE @m_Wert   decimal(14,2)
  DECLARE @m_Konto  varchar(8)
  DECLARE @m_GKonto varchar(8)
  DECLARE @m_Datum  datetime

  SELECT @m_KZ_ZA = KZ_ZA, @m_Wert = Wert, @m_Konto = Konto, @m_GKonto = GKonto, 
         @m_Datum = dbo.itcfunc_datetime(JAHR + '-' + MONAT + '-' + TAG)
    FROM dbo.ut_Lagerbewegungen WHERE RecId = @Param_RecId
  IF (NOT @m_KZ_ZA IN ('1','2')) OR (@m_Wert = 0.00)
	RETURN 1
  IF @m_KZ_ZA = '2'
    SET @m_Wert = 0.00 - @m_Wert

  IF @m_Konto = '' BEGIN
    SELECT @m_Konto = RTRIM(Kto) FROM dbo.ut_KladdenKopf WHERE Code = @Param_BuchKladde
    If @m_Konto IS NULL	
	  SET @m_Konto = ''
	IF @m_Konto = '' 
	  SELECT @m_Konto = RTRIM(OptDataC) FROM dbo.ut_Optionen WHERE OptId = ' 6213'
    If @m_Konto IS NULL	
	  SET @m_Konto = ''
	IF @m_Konto = '' 
	  RETURN 0
  END

  IF @m_GKonto = '' BEGIN
    SELECT @m_GKonto = RTRIM(GK_Kto) FROM dbo.ut_KladdenKopf WHERE Code = @Param_BuchKladde
    If @m_GKonto IS NULL	
	  SET @m_GKonto = ''
	IF @m_GKonto = '' BEGIN
	  IF @m_KZ_ZA = '1'
		SELECT @m_Konto = RTRIM(OptDataC) FROM dbo.ut_Optionen WHERE OptId = ' 6117'
	  ELSE
		SELECT @m_Konto = RTRIM(OptDataC) FROM dbo.ut_Optionen WHERE OptId = ' 6214'
      If @m_GKonto IS NULL	
	    SET @m_GKonto = ''
	  IF @m_GKonto = '' 
	    RETURN 0
	END
  END

  IF @m_Konto = @m_GKonto
	RETURN 0

  DECLARE @m_VkzExport integer
  SELECT @m_VkzExport = VkzExport FROM dbo.ut_KladdenKopf WHERE Code = @Param_BuchKladde 

  DECLARE @m_LfdNr  integer
  DECLARE @m_RecId  integer
  SELECT @m_LfdNr = ISNULL(MAX(LfdNr),0) + 1 FROM [dbo].[ut_KladdenZeile] WHERE Code = @Param_BuchKladde
  SELECT @m_RecId = ISNULL(MAX(RecId),0) + 1 FROM [dbo].[ut_KladdenZeile]

  INSERT [dbo].[ut_KladdenZeile] (RecId, Code, LfdNr, VkzExport, Typ, Kto, SchlDatev, GK_Typ, GK_Kto, Betrag, Netto, BelegDatum, BuchDatum)
  VALUES (@m_RecId, @Param_BuchKladde, @m_LfdNr, @m_VkzExport, 'S',@m_Konto, '40', 'S', @m_GKonto, @m_Wert, @m_Wert, @m_Datum, @m_Datum)

  UPDATE k SET k.PrjNr = l.PrjNr, k.BelArt = l.BelArt, k.BelNr = l.BelNr, k.ExtBelNr = l.Kladde, k.BuText = 'Artikelbuchblatt ' + l.ARTNR, k.Vorgang = l.NRHW
  FROM dbo.ut_KladdenZeile k INNER JOIN dbo.ut_Lagerbewegungen l ON k.RecId = @m_RecId AND l.RecId = @Param_RecId

  RETURN 1
GO
----------------------------------------------------------------------------------------------
-- Artikelbuchblatt verbuchen
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P' and [name] = 'itcproc_ArtikelKladde_Buchen')
  DROP PROCEDURE [dbo].[itcproc_ArtikelKladde_Buchen]
GO
CREATE PROCEDURE [dbo].[itcproc_ArtikelKladde_Buchen]
  @Param_Code varchar(20)
AS
  SET NOCOUNT ON
  BEGIN TRANSACTION

  DECLARE @m_BuchKladde varchar(20)
  SELECT @m_BuchKladde = RTRIM(BuchKladde) FROM ut_ArtikelKladdenKopf WHERE Code = @Param_Code

  DECLARE @m_RecId  integer
  DECLARE @m_Result integer

  DECLARE C CURSOR STATIC LOCAL FOR 
	  SELECT RecId FROM dbo.ut_Lagerbewegungen 
		WHERE Kladde = @Param_Code AND VERBUCHT = 0 AND KZ_ZA in ('1','2','4')
		ORDER BY JAHR, MONAT, TAG, ARTNR, KZ_ZA, RecId 
  OPEN C
  FETCH NEXT FROm C INTO @m_RecId
  WHILE @@FETCH_STATUS = 0 BEGIN
    -- Lagerbewegung
    EXECUTE @m_Result = [dbo].[itcproc_Lagerbewegung_Buchen] @m_RecId
	IF @m_Result = 0 BEGIN
	  ROLLBACK TRANSACTION
	  RETURN 0
	END

    -- Fibukladde
	IF @m_BuchKladde <> '' BEGIN
      EXECUTE @m_Result = [dbo].[itcproc_Lagerbewegung_Kontieren] @m_RecId, @m_BuchKladde
  	  IF @m_Result = 0 BEGIN
	    ROLLBACK TRANSACTION
	    RETURN 0
	  END
	END

    FETCH NEXT FROM C INTO @m_RecId
  END
  CLOSE C
  DEALLOCATE C

  COMMIT TRANSACTION
  SELECT 1 AS success
  RETURN 1
GO

/*
SELECT ISNULL(MAX(LfdNr),0) FROM [dbo].[ut_KladdenZeile] WHERE Code = 'LAGER'
SELECT ISNULL(MAX(RecId),0) FROM [dbo].[ut_KladdenZeile] WHERE Code = 'LAGER'

select * from ut_Lagerbewegungen where Kladde = 'LAGEREINGANG' and VERBUCHT = 0
select * from ut_Lagerbewegungen where RecId = 1034
update ut_Lagerbewegungen set VERBUCHT = 0 where RecId = 1034

EXECUTE [dbo].[itcproc_ArtikelKladde_Buchen] 'LAGEREINGANG'
*/
