-- 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_WartungsDimensionsMatrix')
  DROP VIEW [dbo].[itcview_WartungsDimensionsMatrix]
GO
CREATE VIEW [dbo].[itcview_WartungsDimensionsMatrix] AS
select wp.RecId, wp.AnlagenId, wp.PosNr, wp.Menge, me.[Name] AS MeBez, wp.Bez1, wp.Bez2, wp.Merkmal,
	wa.Dimension1, isnull(wd1.Wert,'') as Wert1, 
	wa.Dimension2, isnull(wd2.Wert,'') as Wert2, 
	wa.Dimension3, isnull(wd3.Wert,'') as Wert3, 
	wa.Dimension4, isnull(wd4.Wert,'') as Wert4, 
	wa.Dimension5, isnull(wd5.Wert,'') as Wert5
  from ut_WartungsPositionen wp
  left join ut_WartungsAnlagen wa WITH(INDEX(Ik_Key00)) on wa.Ident = wp.AnlagenId
  left join ut_WartungsDimensionen wd1 WITH(INDEX(Ik_Key01)) on wd1.AnlagenId = wp.AnlagenId and wd1.PosNr = wp.PosNr and wd1.Bez = wa.Dimension1
  left join ut_WartungsDimensionen wd2 WITH(INDEX(Ik_Key01)) on wd2.AnlagenId = wp.AnlagenId and wd2.PosNr = wp.PosNr and wd2.Bez = wa.Dimension2
  left join ut_WartungsDimensionen wd3 WITH(INDEX(Ik_Key01)) on wd3.AnlagenId = wp.AnlagenId and wd3.PosNr = wp.PosNr and wd3.Bez = wa.Dimension3
  left join ut_WartungsDimensionen wd4 WITH(INDEX(Ik_Key01)) on wd4.AnlagenId = wp.AnlagenId and wd4.PosNr = wp.PosNr and wd4.Bez = wa.Dimension4
  left join ut_WartungsDimensionen wd5 WITH(INDEX(Ik_Key01)) on wd5.AnlagenId = wp.AnlagenId and wd5.PosNr = wp.PosNr and wd5.Bez = wa.Dimension5
  left join ut_Mengeneinheiten me on me.Nr = STR(wp.Einheit,4)
GO
-- select * from [dbo].[itcview_WartungsDimensionsMatrix] where AnlagenId = 3
-- select * from [dbo].[itcview_WartungsDimensionsMatrix] where RecId = 13
--------------------------------------------------------------------------------------------------------
-- WartungsDimensionsMatrix abrufen
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P' and [name] = 'itcproc_WartungsDimensionsMatrix_list')
  DROP PROCEDURE [dbo].[itcproc_WartungsDimensionsMatrix_list]
GO
CREATE PROCEDURE [dbo].[itcproc_WartungsDimensionsMatrix_list]
  @Parm_Ident varchar(15)  -- Anlagen-Ident
AS
  DECLARE @m_Ident integer = dbo.itcfunc_integer(@Parm_Ident)
  SELECT * FROM [dbo].[itcview_WartungsDimensionsMatrix] WHERE AnlagenId = @m_Ident ORDER BY PosNr
GO
-- EXECUTE [dbo].[itcproc_WartungsDimensionsMatrix_list] '2'
--------------------------------------------------------------------------------------------------------
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P' and [name] = 'itcproc_WartungsDimensionen_setWert')
  DROP PROCEDURE [dbo].[itcproc_WartungsDimensionen_setWert]
GO
CREATE PROCEDURE [dbo].[itcproc_WartungsDimensionen_setWert] 
    @Param_AnlagenId integer
  , @Param_PosNr     integer
  , @Param_Bez       varchar(40)
  , @Param_Value     varchar(40)
AS
  IF (@Param_AnlagenId IS NULL) OR (@Param_PosNr IS NULL) OR (@Param_Bez IS NULL) OR (@Param_Value IS NULL)
    RETURN 0

  DECLARE @m_LfdNr integer
  DECLARE @m_RecId integer
  SELECT @m_RecId = ISNULL(RecId,0) FROM [dbo].[ut_WartungsDimensionen] WHERE AnlagenId = @Param_AnlagenId AND PosNr = @Param_PosNr AND Bez = @Param_Bez

  IF @m_RecId > 0 BEGIN
    UPDATE [dbo].[ut_WartungsDimensionen] SET Wert = @Param_Value WHERE RecId = @m_RecId  
  END ELSE BEGIN
    SET @m_RecId = dbo.itcfunc_NextRecId_WartungsDimensionen()
	SELECT @m_LfdNr = ISNULL(MAX(LfdNr),0) + 1 FROM [dbo].[ut_WartungsDimensionen] WHERE AnlagenId = @Param_AnlagenId AND PosNr = @Param_PosNr
	INSERT INTO [dbo].[ut_WartungsDimensionen] (RecId, AnlagenId, PosNr, LfdNr, Bez, Wert) VALUES (@m_RecId, @Param_AnlagenId, @Param_PosNr, @m_LfdNr, @Param_Bez, @Param_Value)
  END

  IF @m_RecId > 0
    RETURN 1
  ELSE
	RETURN 0
GO
-- EXECUTE [dbo].[itcproc_WartungsDimensionen_setWert] 2, 2, 'Dummy', 'Hallo'
--------------------------------------------------------------------------------------------------------
-- Dinmensionswert eintragen, Feld nach Name validieren
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P' and [name] = 'itcproc_WartungsDimensionsMatrix_validate')
  DROP PROCEDURE [dbo].[itcproc_WartungsDimensionsMatrix_validate]
GO
CREATE PROCEDURE [dbo].[itcproc_WartungsDimensionsMatrix_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 @mAnlagenId   integer
  DECLARE @mPosNr       integer
  DECLARE @mDimension   varchar(40)

  select @mAnlagenId = AnlagenId, @mPosNr = PosNr from ut_WartungsPositionen where RecId = @m_RecId
  IF (@mAnlagenId IS NULL) OR (@mPosNr IS NULL) BEGIN
    IF dbo.itcfunc_integer(@Parm_WithResult) <> 0
      SELECT 'Positionssatz nicht vorhanden.' AS error
    RETURN @m_Success
  END

  ------------------------------------------------------------------------------------
  IF @Parm_FieldName = 'Wert1' BEGIN
    select @mDimension = Dimension1 from ut_WartungsAnlagen where Ident = @mAnlagenId
    EXEC @m_Success = [dbo].[itcproc_WartungsDimensionen_setWert] @mAnlagenId, @mPosNr, @mDimension, @Parm_Value
  END ELSE IF @Parm_FieldName = 'Wert2' BEGIN
    select @mDimension = Dimension2 from ut_WartungsAnlagen where Ident = @mAnlagenId
    EXEC @m_Success = [dbo].[itcproc_WartungsDimensionen_setWert] @mAnlagenId, @mPosNr, @mDimension, @Parm_Value
  END ELSE IF @Parm_FieldName = 'Wert3' BEGIN
    select @mDimension = Dimension3 from ut_WartungsAnlagen where Ident = @mAnlagenId
    EXEC @m_Success = [dbo].[itcproc_WartungsDimensionen_setWert] @mAnlagenId, @mPosNr, @mDimension, @Parm_Value
  END ELSE IF @Parm_FieldName = 'Wert4' BEGIN
    select @mDimension = Dimension4 from ut_WartungsAnlagen where Ident = @mAnlagenId
    EXEC @m_Success = [dbo].[itcproc_WartungsDimensionen_setWert] @mAnlagenId, @mPosNr, @mDimension, @Parm_Value
  END ELSE IF @Parm_FieldName = 'Wert5' BEGIN
    select @mDimension = Dimension5 from ut_WartungsAnlagen where Ident = @mAnlagenId
    EXEC @m_Success = [dbo].[itcproc_WartungsDimensionen_setWert] @mAnlagenId, @mPosNr, @mDimension, @Parm_Value
  END

  IF dbo.itcfunc_integer(@Parm_WithResult) <> 0 BEGIN
    IF @m_Success <> 0 
      SELECT * FROM [dbo].[itcview_WartungsDimensionsMatrix] 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
-- EXECUTE [dbo].[itcproc_WartungsDimensionsMatrix_validate] 93,'Wert1','Vailant/XC-2'
-- EXECUTE [dbo].[itcproc_WartungsDimensionsMatrix_list] 2
-- EXECUTE [dbo].[itcproc_WartungsDimensionsMatrix_validate] 8,'Wert1','Vailant/XC'
-- SELECT * FROM ut_WartungsPositionen WHERE AnlagenId = 2
