-- 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_WartungsPosten')
  DROP VIEW [dbo].[itcview_WartungsPosten]
GO
CREATE VIEW [dbo].[itcview_WartungsPosten] AS
	SELECT wp.*, me.[Name] AS MeBez 
	  , wp.VkMat + wp.VkLohn AS EP
	  , ROUND(wp.Menge * (wp.VkMat + wp.VkLohn),2) AS GP
	  , ROUND(wp.Menge * (wp.VkMat + wp.VkLohn),2) - wp.RabattEUR AS Preis
	FROM [dbo].[WartungsPosten] wp --WHERE [RecId] = @m_RecId ORDER BY LfdNr
	left join ut_Mengeneinheiten me on me.Nr = STR(wp.Einheit,4)
GO

--------------------------------------------------------------------------------------------------------
-- Wartungsposten abrufen (Positionen der Auftraege)
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P' and [name] = 'itcproc_WartungsPosten_list')
  DROP PROCEDURE [dbo].[itcproc_WartungsPosten_list]
GO
CREATE PROCEDURE [dbo].[itcproc_WartungsPosten_list]
  @Parm_Ident varchar(15)
AS
  DECLARE @m_Ident integer = dbo.itcfunc_integer(@Parm_Ident)
  SELECT * FROM [dbo].[itcview_WartungsPosten] WHERE [AuftragId] = @m_Ident ORDER BY LfdNr
GO

--------------------------------------------------------------------------------------------------------
-- Wartungsposten abrufen (Positionen der Auftraege)
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P' and [name] = 'itcproc_WartungsPosten_get')
  DROP PROCEDURE [dbo].[itcproc_WartungsPosten_get]
GO
CREATE PROCEDURE [dbo].[itcproc_WartungsPosten_get]
  @Parm_RecId varchar(15)
AS
  DECLARE @m_RecId integer = dbo.itcfunc_integer(@Parm_RecId)
  SELECT * FROM [dbo].[itcview_WartungsPosten] WHERE [RecId] = @m_RecId ORDER BY LfdNr
GO

--------------------------------------------------------------------------------------------------------
-- Wartungsposten kalkulieren
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P' and [name] = 'itcproc_WartungsPosten_calculate')
  DROP PROCEDURE [dbo].[itcproc_WartungsPosten_calculate]
GO
CREATE PROCEDURE [dbo].[itcproc_WartungsPosten_calculate] @Parm_RecId integer, @Parm_ReadStamm integer = 0
AS
  DECLARE @mLkz   varchar(2)
  DECLARE @mArtNr varchar(18)
  DECLARE @mKzpos varchar(1) 
  DECLARE @mEkmat float
  DECLARE @mZubehoerdm float
  DECLARE @mAufMat float 
  DECLARE @mVkMat float 
  DECLARE @mPreisJe float
  DECLARE @mNeAnteil float = 0
  DECLARE @mZeit float
  DECLARE @mEklohnh float
  DECLARE @mEklohn float
  DECLARE @mAufLohn float 
  DECLARE @mVkLohn float 
  DECLARE @mVk2JN integer
  DECLARE @mLohnJN integer
  DECLARE @mMatJN integer
  DECLARE @mEinheit integer
  DECLARE @mBez1 varchar(42)
  DECLARE @mBez2 varchar(42)
  DECLARE @mMerkmal varchar(42)

  IF @Parm_ReadStamm <> 0 BEGIN
    SELECT @mLkz = ISNULL(Lkz,''), @mArtNr = ISNULL(ArtNr,'')  FROM dbo.ut_WartungsPosten WHERE RecId = @Parm_RecId
	IF @mArtNr <> '' BEGIN
	  -- Daten der Stammposition
	  SELECT @mKzpos = sp.Kzpos, @mEkmat = sp.Ekmat, @mZubehoerdm = sp.Zubehoerdm, @mAufMat = kgm.VhMat, @mVkMat = sp.Vkm2, 
			 @mPreisJe = dbo.itcfunc_PreisEinheit(sp.Pe, sp.Gebinde), @mVk2JN = sp.KzVk2JN,
	         @mZeit = sp.Zeit, @mEklohnh = Eklohnh, @mAufLohn = kgl.VhLohn, @mVkLohn = sp.Vkl2,  
			 @mEinheit = Einheit, @mBez1 = Bez1, @mBez2 = Bez2, @mMerkmal = Merkmal FROM dbo.ut_Stammpositionen sp
        left join dbo.Kalkulationsgruppen kgm on kgm.Nr = STR(sp.Kgmat,2)
        left join dbo.Kalkulationsgruppen kgl on kgl.Nr = STR(sp.Kglohn,2)
	    WHERE Lkz = @mLkz AND Artnr = @mArtNr

	  IF NOT (@mKzpos IS NULL) BEGIN
		-- Einkauf kalkulieren
		SET @mEkMat = ROUND((@mEkMat / @mPreisJe) + @mZubehoerdm + @mNeAnteil, 2)
		IF @mEklohnh <> 0.0
		  SET @mEklohn = ROUND((@mZeit / 60.0) * @mEklohnh, 2)
		ELSE
		  SET @mEklohn = ROUND(@mZeit * ISNULL((SELECT OptDataD FROM dbo.ut_Optionen WHERE OptId = ' 7006'), 0.5), 2)

	    -- Verkauf kalkulieren
		SET @mVkMat = ROUND((@mVkMat / @mPreisJe), 2)
		-- Lohn ist bereits auf Vk2 eingestellt

        IF 0 <> ISNULL((SELECT OptDataI FROM dbo.ut_Optionen WHERE OptId = ' 9903'),0) 
	      SET @mVk2JN = 1

		-- VK1 berechnen
		IF @mVk2JN = 0 OR @mVkMat  = 0   SET @mVkMat  = ROUND(@mEkmat +  (@mEkmat *  (@mAufMat  / 100)), 2)
		IF @mVk2JN = 0 OR @mVkLohn = 0   SET @mVkLohn = ROUND(@mEkLohn + (@mEkLohn * (@mAufLohn / 100)), 2)

		-- Kennzeichen kalkulieren
		If @mKzpos <> 'A' SET @mLohnJN = 1 ELSE SET @mLohnJN = 0
		If @mKzpos <> 'L' SET @mMatJN = 1  ELSE SET @mMatJN = 0

		UPDATE dbo.ut_WartungsPosten SET
		   Einheit = @mEinheit
		  ,Bez1 = @mBez1
		  ,Bez2 = @mBez2
		  ,Merkmal = @mMerkmal
		  ,LohnJN = @mLohnJN
		  ,Minuten = @mZeit
		  ,VkLohn = @mVkLohn
		  ,MatJN = @mMatJN
		  ,EkMat = @mEkmat
		  ,VkMat = @mVkMat
		WHERE RecId = @Parm_RecId
	  END -- IF NOT (@mKzpos IS NULL)
	END -- IF @mArtNr <> ''
  END -- IF @Parm_ReadStamm <> 0

  UPDATE dbo.ut_WartungsPosten SET
    RabattEUR = ROUND((ROUND(((VkMat * MatJN) + (VkLohn * LohnJN)) * Menge, 2) * RabattVH) / 100.0, 2)
  WHERE RecId = @Parm_RecId
GO

--------------------------------------------------------------------------------------------------------
-- Wartungsposten Feld nach Name validieren
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P' and [name] = 'itcproc_WartungsPosten_validate')
  DROP PROCEDURE [dbo].[itcproc_WartungsPosten_validate]
GO
CREATE PROCEDURE [dbo].[itcproc_WartungsPosten_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_AuftragId  integer = (SELECT AuftragId FROM ut_WartungsPosten WHERE RecId = @m_RecId)
  DECLARE @m_Success    integer = 0
  DECLARE @m_intValue   integer
  DECLARE @m_floatValue float

  IF dbo.itcfunc_WartungsAuftraege_editable(@m_AuftragId) = 0 BEGIN
    SELECT 'Dieser Auftrag kann hier nicht mehr bearbeitet werden.' AS error
    RETURN @m_Success
  END

  ------------------------------------------------------------------------------------
  IF @Parm_FieldName = 'MfrDone' BEGIN
    SET @m_intValue = dbo.itcfunc_integer(@Parm_Value)
    UPDATE ut_WartungsPosten SET MfrDone = @m_intValue WHERE RecId = @m_RecId
	IF @m_intValue <> 0 UPDATE ut_WartungsPosten SET MfrFail = 0 WHERE RecId = @m_RecId
	SET @m_Success = 1
  END ELSE IF @Parm_FieldName = 'MfrFail' BEGIN
    SET @m_intValue = dbo.itcfunc_integer(@Parm_Value)
    UPDATE ut_WartungsPosten SET MfrFail = @m_intValue WHERE RecId = @m_RecId
	IF @m_intValue <> 0 UPDATE ut_WartungsPosten SET MfrDone = 0 WHERE RecId = @m_RecId
	SET @m_Success = 1
  END ELSE IF @Parm_FieldName = 'Lkz' BEGIN
    UPDATE ut_WartungsPosten SET Lkz = UPPER(@Parm_Value), ArtNr = '' WHERE RecId = @m_RecId
	SET @m_Success = 1
  END ELSE IF @Parm_FieldName = 'ArtNr' BEGIN
    UPDATE ut_WartungsPosten SET ArtNr = UPPER(@Parm_Value) WHERE RecId = @m_RecId
    EXECUTE [dbo].[itcproc_WartungsPosten_calculate] @m_RecId, 1
    SET @m_Success = 1
  END ELSE IF @Parm_FieldName = 'Menge' BEGIN
    SET @m_floatValue = dbo.itcfunc_float(@Parm_Value)
    IF @m_floatValue >= 0 BEGIN
	  UPDATE ut_WartungsPosten SET Menge = @m_floatValue WHERE RecId = @m_RecId
      EXECUTE [dbo].[itcproc_WartungsPosten_calculate] @m_RecId, 1
      SET @m_Success = 1
	END
  END ELSE IF @Parm_FieldName = 'MeBez' BEGIN
    SET @m_intValue = dbo.itcfunc_MengenEinheitVonText(@Parm_Value)
    UPDATE ut_WartungsPosten SET Einheit = @m_intValue WHERE RecId = @m_RecId
	SET @m_Success = 1
  END ELSE IF @Parm_FieldName = 'Bez1' BEGIN
    UPDATE ut_WartungsPosten SET Bez1 = @Parm_Value WHERE RecId = @m_RecId
	SET @m_Success = 1
  END ELSE IF @Parm_FieldName = 'Bez2' BEGIN
    UPDATE ut_WartungsPosten SET Bez2 = @Parm_Value WHERE RecId = @m_RecId
	SET @m_Success = 1
  END ELSE IF @Parm_FieldName = 'Merkmal' BEGIN
    UPDATE ut_WartungsPosten SET Merkmal = @Parm_Value 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_WartungsPosten] 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

--------------------------------------------------------------------------------------------------------
-- Wartungsposten partiale Validierung
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P' and [name] = 'itcproc_WartungsPosten_validate2')
  DROP PROCEDURE [dbo].[itcproc_WartungsPosten_validate2]
GO
CREATE PROCEDURE [dbo].[itcproc_WartungsPosten_validate2]
  @Parm_RecId      varchar(10)
 ,@Parm_Menge      varchar(20)
 ,@Parm_MeBez      varchar(10)
 ,@Parm_Bez1       varchar(42)
 ,@Parm_Bez2       varchar(42)
 ,@Parm_Merkmal    varchar(42)
 ,@Parm_WithResult varchar(10)  = '1'
AS
  DECLARE @m_RecId   integer = dbo.itcfunc_integer(@Parm_RecId)
  DECLARE @m_AuftragId  integer = (SELECT AuftragId FROM ut_WartungsPosten WHERE RecId = @m_RecId)
  DECLARE @m_Menge   float   = dbo.itcfunc_float(@Parm_Menge)
  DECLARE @m_Einheit integer = dbo.itcfunc_MengenEinheitVonText(@Parm_MeBez)

  IF dbo.itcfunc_WartungsAuftraege_editable(@m_AuftragId) = 0 BEGIN
    SELECT 'Dieser Auftrag kann hier nicht mehr bearbeitet werden.' AS error
    RETURN 0
  END

  ------------------------------------------------------------------------------------
  UPDATE [dbo].[ut_WartungsPosten] SET 
     Menge = @m_Menge
	,Einheit = @m_Einheit
	,Bez1 = @Parm_Bez1
	,Bez2 = @Parm_Bez2
	,Merkmal = @Parm_Merkmal
  WHERE RecId = @m_RecId

  EXECUTE [dbo].[itcproc_WartungsPosten_calculate] @m_RecId
  IF dbo.itcfunc_integer(@Parm_WithResult) <> 0 BEGIN
    SELECT * FROM [dbo].[itcview_WartungsPosten] WHERE RecId = @m_RecId
  END
  RETURN 1
GO

--------------------------------------------------------------------------------------------------------
-- Wartungsposten zum Auftrag hinzufuegen
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P' and [name] = 'itcproc_WartungsPosten_add')
  DROP PROCEDURE [dbo].[itcproc_WartungsPosten_add]
GO
CREATE PROCEDURE [dbo].[itcproc_WartungsPosten_add]
  @Parm_AuftragId  varchar(10)
 ,@Parm_Menge      varchar(20) = '1'
 ,@Parm_Lkz        varchar(2) = ''
 ,@Parm_Artnr      varchar(18) = ''
 ,@Parm_MfrAdded   varchar(10) = '1'  -- vom WEB-Monteurbericht aus angelegt
 ,@Parm_Done       varchar(10) = '0'  -- als erledigt kennzeichen
 ,@Parm_WithResult varchar(10)  = '1'
AS
  DECLARE @m_AuftragId integer = dbo.itcfunc_integer(@Parm_AuftragId)
  DECLARE @m_Menge     float   = dbo.itcfunc_float(@Parm_Menge)
  DECLARE @m_MfrAdded  integer = dbo.itcfunc_integer(@Parm_MfrAdded)
  DECLARE @m_Done      integer = dbo.itcfunc_integer(@Parm_Done)
  DECLARE @m_RecId     integer = 0
  DECLARE @m_AnlagenId integer
  DECLARE @m_LfdNr     integer

  IF dbo.itcfunc_WartungsAuftraege_editable(@m_AuftragId) = 0 BEGIN
    SELECT 'Dieser Auftrag kann hier nicht mehr bearbeitet werden.' AS error
    RETURN 0
  END

  ------------------------------------------------------------------------------------
  SELECT @m_AnlagenId = AnlagenId FROM ut_WartungsAuftraege WITH(INDEX = Ik_Key00) WHERE Ident = @m_AuftragId
  IF NOT (@m_AnlagenId IS NULL) BEGIN
	SELECT @m_LfdNr = ISNULL(MAX(LfdNr),0) +  1 FROM ut_WartungsPosten WITH(INDEX = Ik_Key00) WHERE AuftragId = @m_AuftragId
    SET @m_RecId = dbo.itcfunc_NextRecId_WartungsPosten()
	INSERT INTO [dbo].[ut_WartungsPosten] 
	           (RecId,AuftragId,AnlagenId,LfdNr,Menge,LohnJN,MatJN,[Check],Lkz,ArtNr,MfrAdded,MfrDone)
         VALUES(@m_RecId,@m_AuftragId,@m_AnlagenId,@m_LfdNr,@m_Menge,1,1,1,@Parm_Lkz,@Parm_Artnr,@m_MfrAdded,@m_Done)

	EXECUTE [dbo].[itcproc_WartungsPosten_calculate] @m_RecId, 1
  END

  IF dbo.itcfunc_integer(@Parm_WithResult) <> 0 BEGIN
    SELECT * FROM [dbo].[itcview_WartungsPosten] WHERE RecId = @m_RecId
  END
  RETURN @m_RecId
GO

--------------------------------------------------------------------------------------------------------
-- Wartungsposten zum Auftrag aus Paket hinzufuegen
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P' and [name] = 'itcproc_WartungsPosten_add2')
  DROP PROCEDURE [dbo].[itcproc_WartungsPosten_add2]
GO
CREATE PROCEDURE [dbo].[itcproc_WartungsPosten_add2]
  @Parm_AuftragId  varchar(10)
 ,@Parm_PktNr      varchar(17)
 ,@Parm_MfrAdded   varchar(10) = '1'  -- vom WEB-Monteurbericht aus angelegt
 ,@Parm_Done       varchar(10) = '0'  -- als erledigt kennzeichen
 ,@Parm_WithResult varchar(10)  = '1'
AS
  DECLARE @m_AuftragId integer = dbo.itcfunc_integer(@Parm_AuftragId)
  DECLARE @m_MfrAdded  integer = dbo.itcfunc_integer(@Parm_MfrAdded)
  DECLARE @m_Done      integer = dbo.itcfunc_integer(@Parm_Done)
  DECLARE @m_RecId     integer = 0
  DECLARE @m_AnlagenId integer
  DECLARE @m_LfdNr     integer
  DECLARE @m_Guid	   varchar(36) = NEWID()
  DECLARE @m_Menge     float
  DECLARE @m_Lkz       varchar(2)
  DECLARE @m_Artnr     varchar(18)

  IF dbo.itcfunc_WartungsAuftraege_editable(@m_AuftragId) = 0 BEGIN
    SELECT 'Dieser Auftrag kann hier nicht mehr bearbeitet werden.' AS error
    RETURN 0
  END

  ------------------------------------------------------------------------------------
  SELECT @m_AnlagenId = AnlagenId FROM ut_WartungsAuftraege WITH(INDEX = Ik_Key00) WHERE Ident = @m_AuftragId
  IF NOT (@m_AnlagenId IS NULL) BEGIN
    DECLARE CPkt CURSOR LOCAL FOR SELECT Aufwand, ArtLkz, ArtNr FROM dbo.ut_StammPakete WHERE PktNr = @Parm_PktNr AND LfdNr > '000' ORDER BY LfdNr
	OPEN CPkt
    FETCH NEXT FROM CPkt INTO @m_Menge, @m_Lkz, @m_Artnr
	WHILE @@FETCH_STATUS = 0 BEGIN
	  EXECUTE @m_RecId = [dbo].[itcproc_WartungsPosten_add] @Parm_AuftragId, @m_Menge, @m_Lkz, @m_Artnr, @Parm_MfrAdded, @Parm_Done, '0'
	  IF @m_RecId <> 0 
	    INSERT INTO ut_GuidAndRecId (RecId,mGuid,mRecId) VALUES(dbo.itcfunc_NextRecId_GuidAndRecId(),@m_Guid,@m_RecId)
	  FETCH NEXT FROM CPkt INTO @m_Menge, @m_Lkz, @m_Artnr
	END
	CLOSE CPkt
	DEALLOCATE CPkt
  END

  IF dbo.itcfunc_integer(@Parm_WithResult) <> 0 BEGIN
    select wp.* from ut_GuidAndRecId g inner join [dbo].[itcview_WartungsPosten] wp on wp.RecId = g.mRecId where mGuid = @m_Guid
  END
  delete from ut_GuidAndRecId where mGuid = @m_Guid
  RETURN 1
GO

--------------------------------------------------------------------------------------------------------
-- Wartungsposten loeschen
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P' and [name] = 'itcproc_WartungsPosten_delete')
  DROP PROCEDURE [dbo].[itcproc_WartungsPosten_delete]
GO
CREATE PROCEDURE [dbo].[itcproc_WartungsPosten_delete]
  @Parm_RecId      varchar(10)
AS
  DECLARE @m_RecId   integer = dbo.itcfunc_integer(@Parm_RecId)
  DECLARE @m_AuftragId  integer = (SELECT AuftragId FROM ut_WartungsPosten WHERE RecId = @m_RecId)

  IF dbo.itcfunc_WartungsAuftraege_editable(@m_AuftragId) = 0 BEGIN
    SELECT 'Dieser Auftrag kann hier nicht mehr bearbeitet werden.' AS error
    RETURN 0
  END

  DELETE FROM ut_WartungsPosten WHERE RecId = @m_RecId
  RETURN 1
GO
