-- 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] = 'P'   and [name] = 'up_Append_Langtextzeilen_Silent')
  DROP PROCEDURE [dbo].[up_Append_Langtextzeilen_Silent]
GO
CREATE PROCEDURE [dbo].[up_Append_Langtextzeilen_Silent]
   @Param_Art char(2)
  ,@Param_Lkz char(2)
  ,@Param_Nr char(18)
  ,@Param_LfdNr char(3)
  ,@Param_Abschluss tinyint
  ,@Param_SoftBreak tinyint
  ,@Param_LineFeeds integer
  ,@Param_CntChar integer
  ,@Param_Daten char(55)
AS
  SET NOCOUNT ON
  DECLARE @NeueId integer
  DECLARE @m_CountDown integer = 10
  WHILE @m_CountDown > 0 BEGIN
    BEGIN TRANSACTION
    BEGIN TRY
      SET @NeueId = dbo.itcfunc_NextRecId_Langtextzeilen()
      INSERT INTO [dbo].[ut_Langtextzeilen] (RecId
        ,[Art]
        ,[Lkz]
        ,[Nr]
        ,[LfdNr]
        ,[Abschluss]
        ,[SoftBreak]
        ,[LineFeeds]
        ,[CntChar]
        ,[Daten]
          ) VALUES (@NeueId
        ,UPPER(RTRIM(@Param_Art))
        ,UPPER(RTRIM(@Param_Lkz))
        ,UPPER(RTRIM(@Param_Nr))
        ,UPPER(RTRIM(@Param_LfdNr))
        ,@Param_Abschluss
        ,@Param_SoftBreak
        ,@Param_LineFeeds
        ,@Param_CntChar
        ,RTRIM(@Param_Daten)
          )
      COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
      ROLLBACK TRANSACTION
      SET @NeueId = 0
    END CATCH

    IF @NeueId <> 0
      SET @m_CountDown = 0
    ELSE
      SET @m_CountDown = @m_CountDown - 1

    IF @m_CountDown > 0
      WAITFOR DELAY '00:00:00.050'
  END -- WHILE
  SET NOCOUNT OFF
GO

--------------------------------------------------------------------------------------------------------
-- Langtext abrufen (Webseite)
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P' and [name] = 'itcproc_Langtextzeilen_get')
  DROP PROCEDURE [dbo].[itcproc_Langtextzeilen_get]
GO
CREATE PROCEDURE [dbo].[itcproc_Langtextzeilen_get]
  @Param_Art varchar(2)  
 ,@Param_Lkz varchar(2)
 ,@Param_Nr  varchar(18)
AS
  SET NOCOUNT ON
  SELECT Art, Lkz, Nr, LfdNr, Abschluss, SoftBreak, LineFeeds, CntChar, Daten FROM ut_Langtextzeilen 
  WHERE Art = @Param_Art AND Lkz = @Param_Lkz AND Nr = @Param_Nr
  ORDER BY LfdNr
  SET NOCOUNT OFF
GO

--------------------------------------------------------------------------------------------------------
-- Langtext abrufen mit RecId (Datanorm)
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P' and [name] = 'itcproc_Langtextzeilen_get2')
  DROP PROCEDURE [dbo].[itcproc_Langtextzeilen_get2]
GO
CREATE PROCEDURE [dbo].[itcproc_Langtextzeilen_get2]
  @Param_Art varchar(2)  
 ,@Param_Lkz varchar(2)
 ,@Param_Nr  varchar(18)
AS
  SET NOCOUNT ON
  SELECT RecId, Art, Lkz, Nr, LfdNr, Abschluss, SoftBreak, LineFeeds, CntChar, Daten FROM ut_Langtextzeilen 
  WHERE Art = @Param_Art AND Lkz = @Param_Lkz AND Nr = @Param_Nr
  ORDER BY LfdNr
  SET NOCOUNT OFF
GO

--------------------------------------------------------------------------------------------------------
-- Langtext speichern (Webseite)
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P' and [name] = 'itcproc_Langtextzeilen_put')
  DROP PROCEDURE [dbo].[itcproc_Langtextzeilen_put]
GO
CREATE PROCEDURE [dbo].[itcproc_Langtextzeilen_put]
  @Param_Art       varchar(2)  
 ,@Param_Lkz       varchar(2)
 ,@Param_Nr        varchar(18)
 ,@Param_LfdNr     varchar(10)
 ,@Param_Abschluss varchar(10)
 ,@Param_SoftBreak varchar(10)
 ,@Param_LineFeeds varchar(10)
 ,@Param_CntChar   varchar(10)
 ,@Param_Daten     varchar(55)
AS
  DECLARE @m_LfdNr char(3) = RIGHT('00' + LTRIM(STR(dbo.itcfunc_integer(@Param_LfdNr),3)),3)
  DECLARE @m_Abschluss integer = dbo.itcfunc_integer(@Param_Abschluss)
  DECLARE @m_SoftBreak integer = dbo.itcfunc_integer(@Param_SoftBreak)
  DECLARE @m_LineFeeds integer = dbo.itcfunc_integer(@Param_LineFeeds)
  DECLARE @m_CntChar integer = dbo.itcfunc_integer(@Param_CntChar)

  SET NOCOUNT ON

  IF 0 = (SELECT COUNT(*) FROM dbo.ut_Langtextzeilen WHERE Art = @Param_Art AND Lkz = @Param_Lkz AND Nr = @Param_Nr AND LfdNr = @m_LfdNr) BEGIN
    DECLARE @m_RecIdZeile integer = dbo.itcfunc_NextRecId_Langtextzeilen()
	INSERT INTO dbo.ut_Langtextzeilen (RecId, Art, Lkz, Nr, LfdNr, Abschluss, SoftBreak, LineFeeds, CntChar, Daten)
	  VALUES(@m_RecIdZeile, @Param_Art, @Param_Lkz ,@Param_Nr, @m_LfdNr, @m_Abschluss, @m_SoftBreak, @m_LineFeeds, @m_CntChar, @Param_Daten)
  END ELSE
    UPDATE dbo.ut_Langtextzeilen SET Abschluss = @m_Abschluss, SoftBreak = @m_SoftBreak, LineFeeds = @m_LineFeeds, CntChar = @m_CntChar, Daten = @Param_Daten
	WHERE Art = @Param_Art AND Lkz = @Param_Lkz AND Nr = @Param_Nr AND LfdNr = @m_LfdNr

  DECLARE @m_Suchen char(20) = LEFT(UPPER(@Param_Daten),20)
  IF @m_LfdNr = '001' BEGIN
    DELETE FROM dbo.ut_Langtextzeilen WHERE Art = @Param_Art AND Lkz = @Param_Lkz AND Nr = @Param_Nr AND LfdNr < @m_LfdNr

    IF 0 = (SELECT COUNT(*) FROM dbo.ut_Langtexte WHERE Art = @Param_Art AND Lkz = @Param_Lkz AND Nr = @Param_Nr) BEGIN
      DECLARE @m_RecIdKopf integer = dbo.itcfunc_NextRecId_Langtexte()
	  INSERT INTO dbo.ut_Langtexte (RecId, Art, Lkz, Nr, Suchen)
	    VALUES(@m_RecIdKopf, @Param_Art, @Param_Lkz ,@Param_Nr, @m_Suchen)
    END ELSE
	  UPDATE dbo.ut_Langtexte SET Suchen = @m_Suchen WHERE Art = @Param_Art AND Lkz = @Param_Lkz AND Nr = @Param_Nr
  END ELSE BEGIN
	  UPDATE dbo.ut_Langtexte SET Suchen = @m_Suchen WHERE Art = @Param_Art AND Lkz = @Param_Lkz AND Nr = @Param_Nr AND Suchen = ''
  END

  IF @m_Abschluss = 1
    DELETE FROM dbo.ut_Langtextzeilen WHERE Art = @Param_Art AND Lkz = @Param_Lkz AND Nr = @Param_Nr AND LfdNr > @m_LfdNr

  SET NOCOUNT OFF
GO

--------------------------------------------------------------------------------------------------------
-- Langtext speichern mit RecId (Datanorm)
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P' and [name] = 'itcproc_Langtextzeilen_put2')
  DROP PROCEDURE [dbo].[itcproc_Langtextzeilen_put2]
GO
CREATE PROCEDURE [dbo].[itcproc_Langtextzeilen_put2]
  @Param_RecId	   varchar(10)
 ,@Param_Art       varchar(2)  
 ,@Param_Lkz       varchar(2)
 ,@Param_Nr        varchar(18)
 ,@Param_LfdNr     varchar(10)
 ,@Param_Abschluss varchar(10)
 ,@Param_SoftBreak varchar(10)
 ,@Param_LineFeeds varchar(10)
 ,@Param_CntChar   varchar(10)
 ,@Param_Daten     varchar(55)
AS
  DECLARE @m_RecId integer = dbo.itcfunc_integer(@Param_RecId)
  DECLARE @m_LfdNr char(3) = RIGHT('00' + LTRIM(STR(dbo.itcfunc_integer(@Param_LfdNr),3)),3)
  DECLARE @m_Abschluss integer = dbo.itcfunc_integer(@Param_Abschluss)
  DECLARE @m_SoftBreak integer = dbo.itcfunc_integer(@Param_SoftBreak)
  DECLARE @m_LineFeeds integer = dbo.itcfunc_integer(@Param_LineFeeds)
  DECLARE @m_CntChar integer = dbo.itcfunc_integer(@Param_CntChar)

  SET NOCOUNT ON

  If @m_RecId = 0
    EXECUTE [dbo].[up_Append_Langtextzeilen_Silent] @Param_Art, @Param_Lkz ,@Param_Nr, @m_LfdNr, @m_Abschluss, @m_SoftBreak, @m_LineFeeds, @m_CntChar, @Param_Daten
  else
    EXECUTE [dbo].[up_Upd_Langtextzeilen] @m_RecId, @Param_Art, @Param_Lkz ,@Param_Nr, @m_LfdNr, @m_Abschluss, @m_SoftBreak, @m_LineFeeds, @m_CntChar, @Param_Daten

  DECLARE @m_Suchen char(20) = LEFT(UPPER(@Param_Daten),20)
  IF @m_LfdNr = '001' BEGIN
    DELETE FROM dbo.ut_Langtextzeilen WHERE Art = @Param_Art AND Lkz = @Param_Lkz AND Nr = @Param_Nr AND LfdNr < @m_LfdNr

    IF 0 = (SELECT COUNT(*) FROM dbo.ut_Langtexte WHERE Art = @Param_Art AND Lkz = @Param_Lkz AND Nr = @Param_Nr) BEGIN
      DECLARE @m_RecIdKopf integer = dbo.itcfunc_NextRecId_Langtexte()
	  INSERT INTO dbo.ut_Langtexte (RecId, Art, Lkz, Nr, Suchen)
	    VALUES(@m_RecIdKopf, @Param_Art, @Param_Lkz ,@Param_Nr, @m_Suchen)
    END ELSE
	  UPDATE dbo.ut_Langtexte SET Suchen = @m_Suchen WHERE Art = @Param_Art AND Lkz = @Param_Lkz AND Nr = @Param_Nr
  END ELSE BEGIN
	  UPDATE dbo.ut_Langtexte SET Suchen = @m_Suchen WHERE Art = @Param_Art AND Lkz = @Param_Lkz AND Nr = @Param_Nr AND Suchen = ''
  END

  IF @m_Abschluss = 1
    DELETE FROM dbo.ut_Langtextzeilen WHERE Art = @Param_Art AND Lkz = @Param_Lkz AND Nr = @Param_Nr AND LfdNr > @m_LfdNr

  SET NOCOUNT OFF
GO

--------------------------------------------------------------------------------------------------------
-- Langtext loeschen
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P' and [name] = 'itcproc_Langtext_delete')
  DROP PROCEDURE [dbo].[itcproc_Langtext_delete]
GO
CREATE PROCEDURE [dbo].[itcproc_Langtext_delete]
  @Param_Art varchar(2)  
 ,@Param_Lkz varchar(2)
 ,@Param_Nr  varchar(18)
AS
  SET NOCOUNT ON
  DELETE FROM ut_Langtexte WHERE Art = @Param_Art AND Lkz = @Param_Lkz AND Nr = @Param_Nr
  SET NOCOUNT OFF
GO

-- SELECT RIGHT('00' + LTRIM(STR(dbo.itcfunc_integer(1),3)),3)
-- SELECT DISTINCT LfdNr from ut_Langtextzeilen
-- SELECT * FROM ut_Langtexte WHERE Art = '25'
-- SELECT * FROM ut_Langtextzeilen WHERE Art = '25'
-- EXECUTE [dbo].[itcproc_Langtextzeilen_put] '25', 'AU','AUF              4','1',1,0,1,50,'Achtung bissiger Hund'


