-- Aufbereitung Artikellangtexte für die Druckform
-- Stand 11/2001
-- Autor sa

if 0 < (select count(*) from sysobjects where [xtype] = 'U' and [name] = 'ut_ArtikelTexte')
	DROP TABLE dbo.ut_ArtikelTexte
GO

CREATE TABLE dbo.ut_ArtikelTexte
(
 	 Lkz            char(2)      not null
 	,Artnr          char(18)     not null
	,LfdNr  	char(6)      not null
	,LineData	varchar(160) not null
)
GO

CREATE INDEX IkPrimary ON dbo.ut_ArtikelTexte
(
	Lkz,Artnr,LfdNr
)
GO

if 0 < (select count(*) from sysobjects where [xtype] = 'V' and [name] = 'ArtikelTexte')
	DROP VIEW dbo.ArtikelTexte
GO

CREATE VIEW dbo.ArtikelTexte
--ALTER VIEW dbo.ArtikelTexte
AS
  SELECT * FROM dbo.ut_ArtikelTexte
GO

if 0 < (select count(*) from sysobjects where [xtype] = 'P' and [name] = 'hwp_MakeArtikelText_01')
	DROP PROCEDURE dbo.hwp_MakeArtikelText_01
GO
-- Diese Prozedur stellt eine Langtextblock
-- in die Tabelle ArtikelTexte ab
CREATE PROCEDURE dbo.hwp_MakeArtikelText_01
--ALTER  PROCEDURE hwp_MakeArtikelText_01
        @Parm_ArtLkz   char(2)
       ,@Parm_ArtNr    char(18)
       ,@Parm_TextArt  char(2)
       ,@Parm_TextLkz  char(2)
       ,@Parm_TextNr   char(18)
       ,@Parm_LfdNr    integer
AS
  DECLARE   CT1 CURSOR FOR 
   SELECT   Daten,CntChar,SoftBreak+LineFeeds,Abschluss
   FROM     Langtextzeilen 
   WHERE    Art=@Parm_TextArt AND Lkz=@Parm_TextLkz AND Nr=@Parm_TextNr 
   ORDER BY LfdNr
  OPEN CT1
  
  DECLARE @mDaten  char(55)
  DECLARE @mCnt    integer
  DECLARE @mBreak  integer
  DECLARE @mEnde   integer
  DECLARE @mText   varchar(160)

  SET @mText   = ''
  FETCH NEXT FROM CT1 INTO @mDaten,@mCnt,@mBreak,@mEnde
  WHILE @@FETCH_STATUS = 0
  BEGIN
    IF (LEN(@mText)+@mCnt) > 160
    BEGIN
      INSERT INTO dbo.ArtikelTexte (Lkz,Artnr,LfdNr,LineData) 
           VALUES (@Parm_ArtLkz,@Parm_ArtNr,STR(@Parm_LfdNr,6),@mText)
      SET @Parm_LfdNr  = @Parm_LfdNr + 1
      SET @mText       = ''
    END

    IF (@mCnt > 0)
    BEGIN
      SET @mText = @mText + SUBSTRING(@mDaten,1,@mCnt)
    END	

    WHILE (@mBreak > 0)
    BEGIN
      INSERT INTO dbo.ArtikelTexte (Lkz,Artnr,LfdNr,LineData) 
           VALUES (@Parm_ArtLkz,@Parm_ArtNr,STR(@Parm_LfdNr,6),@mText)
      SET @Parm_LfdNr  = @Parm_LfdNr + 1
      SET @mText       = ''
      SET @mBreak      = @mBreak - 1
    END
    --print @mDaten

    IF @mEnde > 0 BREAK
    FETCH NEXT FROM CT1 INTO @mDaten,@mCnt,@mBreak,@mEnde
  END

  IF LEN(@mText) > 0
  BEGIN
      INSERT INTO dbo.ArtikelTexte (Lkz,Artnr,LfdNr,LineData) 
           VALUES (@Parm_ArtLkz,@Parm_ArtNr,STR(@Parm_LfdNr,6),@mText)
      SET @Parm_LfdNr  = @Parm_LfdNr + 1
      SET @mText       = ''
  END

  CLOSE CT1
  DEALLOCATE CT1
  RETURN @Parm_LfdNr
GO

if 0 < (select count(*) from sysobjects where [xtype] = 'P' and [name] = 'hwp_MakeArtikelText_Replace')
	DROP PROCEDURE dbo.hwp_MakeArtikelText_Replace
GO
-- Diese Prozedur erstzt die Einfügemarken
-- im aufgelösten Artikeltext durch @ParmRepl
CREATE PROCEDURE dbo.hwp_MakeArtikelText_Replace
--ALTER PROCEDURE hwp_MakeArtikelText_Replace
        @Parm_ArtLkz   char(2)
       ,@Parm_ArtNr    char(18)
       ,@Parm_Repl     varchar(2000)
       ,@Parm_VonZeile integer
       ,@Parm_BisZeile integer
AS
  DECLARE   CTD CURSOR FOR 
   SELECT   LineData
   FROM     ArtikelTexte
   WHERE    Lkz=@Parm_ArtLkz AND Artnr=@Parm_ArtNr 
        AND LfdNr >= STR(@Parm_VonZeile,6) 
        AND LfdNr <  STR(@Parm_BisZeile,6) 
   ORDER BY LfdNr
  OPEN CTD
  DECLARE @mSrc   varchar(160)
  DECLARE @mDst   varchar(160)
  DECLARE @mLen   integer
  DECLARE @mLenR  integer
  DECLARE @mPosR  integer
  DECLARE @mRepl  varchar(2000)

  SET @mPosR = 1
  SET @mLenR = LEN(@Parm_Repl)
  SET @mRepl = ''

  -- $ Zeichen aus Replace entfernen
  WHILE @mPosR <= @mLenR
  BEGIN
    IF SUBSTRING(@Parm_Repl,@mPosR,1) <> '$'
        SET @mRepl = @mRepl + SUBSTRING(@Parm_Repl,@mPosR,1)
    SET @mPosR = @mPosR + 1
  END

  --print 'Replace:'+@mRepl

  -- Laenge neues Replace ermitteln
  SET @mPosR = 1
  SET @mLenR = LEN(@mRepl)

  FETCH NEXT FROM CTD INTO @mSrc
  WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @mDst = ''
    SET @mLen = LEN(@mSrc)
    WHILE @mLen > 0
    BEGIN
      IF (SUBSTRING(@mSrc,1,1) = '$') AND (@mPosR <= @mLenR)
        BEGIN
          SET @mDst  = @mDst  + SUBSTRING(@mRepl,@mPosR,1) 
          SET @mPosR = @mPosR + 1
        END
      ELSE
        BEGIN
          SET @mDst = @mDst + SUBSTRING(@mSrc,1,1)
        END

      SET @mLen = @mLen - 1
      IF  @mLen > 0
          SET @mSrc = SUBSTRING(@mSrc,2,@mLen) 
    END
    UPDATE ArtikelTexte SET LineData = @mDst WHERE CURRENT OF CTD
    FETCH NEXT FROM CTD INTO @mSrc
  END

  CLOSE CTD
  DEALLOCATE CTD
GO

if 0 < (select count(*) from sysobjects where [xtype] = 'P' and [name] = 'hwp_MakeArtikelText_D')
	DROP PROCEDURE dbo.hwp_MakeArtikelText_D
GO
-- Diese Prozedur stellt den Dimensionstext in
-- aufgelöster Form in die Tabelle ArtikelTexte ab
CREATE PROCEDURE dbo.hwp_MakeArtikelText_D
--ALTER  PROCEDURE hwp_MakeArtikelText_D
        @Parm_ArtLkz   char(2)
       ,@Parm_ArtNr    char(18)
       ,@Parm_LfdNr    integer
AS
  DECLARE   CT2 CURSOR FOR 
   SELECT   KENNUNG,LKZTEXTNR,TEXTNR,TEXTZEILE
   FROM     Dimensionstexte
   WHERE    LKZ=@Parm_ArtLkz AND ARTNR=@Parm_ArtNr 
   ORDER BY LFDNR
  OPEN CT2

  DECLARE @mKennung   char(1)
  DECLARE @mLkz       char(2)
  DECLARE @mNr        char(18)
  DECLARE @mText      char(42)
  DECLARE @mToFetch   integer

  -- Variablen fuer Auflösung
  DECLARE @mRepl     varchar(2000)
  DECLARE @mTmpLfdNr integer
  DECLARE @mLkz2     char(2)
  DECLARE @mNr2      char(18)
  DECLARE @mStatus   integer
   
  FETCH NEXT FROM CT2 INTO @mKennung,@mLkz,@mNr,@mText
  SET @mStatus = @@FETCH_STATUS
  WHILE @mStatus = 0
  BEGIN
    SET  @mToFetch = 1

    IF @mKennung = 'F'
    BEGIN
      INSERT INTO dbo.ArtikelTexte (Lkz,Artnr,LfdNr,LineData) 
           VALUES (@Parm_ArtLkz,@Parm_ArtNr,STR(@Parm_LfdNr,6),@mText)
      SET @Parm_LfdNr  = @Parm_LfdNr + 1
    END

    IF @mKennung = 'T'
    BEGIN
      EXECUTE @Parm_LfdNr = hwp_MakeArtikelText_01 @Parm_ArtLkz,@Parm_ArtNr,'11',@mLkz,@mNr,@Parm_LfdNr
    END

    -- Auflösen Einfügetexte
    IF @mKennung = 'E'
    BEGIN
      -- Einfügetext holen
      -- Folgesaetze prüfen
      SET @mTmpLfdNr = @Parm_LfdNr
      SET @mToFetch  = 0    
      SET @mLkz2     = @mLkz
      SET @mNr2      = @mNr
      SET @mRepl     = RTRIM(@mText)
      EXECUTE @Parm_LfdNr = hwp_MakeArtikelText_01 @Parm_ArtLkz,@Parm_ArtNr,'14',@mLkz,@mNr,@Parm_LfdNr
      FETCH NEXT FROM CT2 INTO @mKennung,@mLkz,@mNr,@mText
      SET @mStatus = @@FETCH_STATUS
      WHILE (@mStatus = 0) AND (@mKennung = 'E') AND (@mLkz = @mLkz2) AND (@mNr = @mNr2)
      BEGIN
        SET @mRepl   = @mRepl + RTRIM(@mText)
	FETCH NEXT FROM CT2 INTO @mKennung,@mLkz,@mNr,@mText
        SET @mStatus = @@FETCH_STATUS
      END
      -- Ersetzen von LfdNr @mTmpLfdNr .. (@mLfdNr - 1)
      EXECUTE hwp_MakeArtikelText_Replace @Parm_ArtLkz,@Parm_ArtNr,@mRepl,@mTmpLfdNr,@Parm_LfdNr
    END

    IF @mToFetch > 0
    BEGIN
      FETCH NEXT FROM CT2 INTO @mKennung,@mLkz,@mNr,@mText
      SET @mStatus = @@FETCH_STATUS
    END
  END

  CLOSE CT2
  DEALLOCATE CT2
  RETURN @Parm_LfdNr
GO


if 0 < (select count(*) from sysobjects where [xtype] = 'P' and [name] = 'hwp_MakeArtikelText_L')
	DROP PROCEDURE dbo.hwp_MakeArtikelText_L
GO
-- Diese Prozedur stellt den Langtextbaustein 
-- abhängig vom Textkz in die Tabelle ArtikelTexte ab
CREATE PROCEDURE dbo.hwp_MakeArtikelText_L
--ALTER  PROCEDURE hwp_MakeArtikelText_L
        @Parm_Lkz      char(2)
       ,@Parm_Artnr    char(18)
       ,@Parm_LfdNr    integer
AS
  DECLARE  CT_Art CURSOR FOR 
   SELECT  Textkz,Lkztextnr,Textnr
   FROM    Stammpositionen
   WHERE   Lkz = @Parm_Lkz AND Artnr = @Parm_Artnr
  OPEN CT_Art

  DECLARE @mTextkz     integer
  DECLARE @mLkztextnr  varchar(2)
  DECLARE @mTextnr     varchar(18)

  FETCH NEXT FROM CT_Art INTO @mTextkz,@mLkztextnr,@mTextnr 
  IF @@FETCH_STATUS = 0
  BEGIN
    IF @mTextkz = 2
       EXECUTE @Parm_LfdNr = hwp_MakeArtikelText_01
          @Parm_Lkz,@Parm_Artnr,'13',@Parm_Lkz,@Parm_Artnr,@Parm_LfdNr
    ELSE
       EXECUTE @Parm_LfdNr = hwp_MakeArtikelText_01
          @Parm_Lkz,@Parm_Artnr,'11',@mLkztextnr,@mTextnr,@Parm_LfdNr
  END

  CLOSE CT_Art
  DEALLOCATE CT_Art
  RETURN @Parm_LfdNr
GO


if 0 < (select count(*) from sysobjects where [xtype] = 'P' and [name] = 'hwp_MakeArtikelText_K1')
	DROP PROCEDURE dbo.hwp_MakeArtikelText_K1
GO
-- Diese Prozedur stellt Kurztext 1 ind die Artikeltexttabelle
CREATE PROCEDURE dbo.hwp_MakeArtikelText_K1
--ALTER  PROCEDURE hwp_MakeArtikelText_K1
        @Parm_Lkz      char(2)
       ,@Parm_Artnr    char(18)
       ,@Parm_LfdNr    integer
AS
  DECLARE @mText varchar(42)
  SET @mText = (SELECT TOP 1 RTRIM(Bez1) FROM Stammpositionen WHERE Lkz=@Parm_Lkz AND Artnr=@Parm_Artnr)

  IF @mText IS NULL
    RETURN @Parm_LfdNr

  IF LEN(@mText) = 0
    RETURN @Parm_LfdNr

  INSERT INTO dbo.ArtikelTexte (Lkz,Artnr,LfdNr,LineData) 
       VALUES (@Parm_Lkz,@Parm_Artnr,STR(@Parm_LfdNr,6),@mText)
  SET @Parm_LfdNr = @Parm_LfdNr + 1
  RETURN @Parm_LfdNr
GO


if 0 < (select count(*) from sysobjects where [xtype] = 'P' and [name] = 'hwp_MakeArtikelText_K2')
	DROP PROCEDURE dbo.hwp_MakeArtikelText_K2
GO
-- Diese Prozedur stellt Kurztext 2 ind die Artikeltexttabelle
CREATE PROCEDURE dbo.hwp_MakeArtikelText_K2
--ALTER  PROCEDURE hwp_MakeArtikelText_K2
        @Parm_Lkz      char(2)
       ,@Parm_Artnr    char(18)
       ,@Parm_LfdNr    integer
AS
  DECLARE @mText varchar(42)
  SET @mText = (SELECT TOP 1 RTRIM(Bez2) FROM Stammpositionen WHERE Lkz=@Parm_Lkz AND Artnr=@Parm_Artnr)

  IF @mText IS NULL
    RETURN @Parm_LfdNr

  IF LEN(@mText) = 0
    RETURN @Parm_LfdNr

  INSERT INTO dbo.ArtikelTexte (Lkz,Artnr,LfdNr,LineData) 
       VALUES (@Parm_Lkz,@Parm_Artnr,STR(@Parm_LfdNr,6),@mText)
  SET @Parm_LfdNr = @Parm_LfdNr + 1
  RETURN @Parm_LfdNr
GO


if 0 < (select count(*) from sysobjects where [xtype] = 'P' and [name] = 'hwp_MakeArtikelText_K3')
	DROP PROCEDURE dbo.hwp_MakeArtikelText_K3
GO
-- Diese Prozedur stellt Kurztext 3 ind die Artikeltexttabelle
CREATE PROCEDURE dbo.hwp_MakeArtikelText_K3
--ALTER  PROCEDURE hwp_MakeArtikelText_K3
        @Parm_Lkz      char(2)
       ,@Parm_Artnr    char(18)
       ,@Parm_LfdNr    integer
AS
  DECLARE @mText varchar(42)
  SET @mText = (SELECT TOP 1 RTRIM(Merkmal) FROM Stammpositionen WHERE Lkz=@Parm_Lkz AND Artnr=@Parm_Artnr)

  IF @mText IS NULL
    RETURN @Parm_LfdNr

  IF LEN(@mText) = 0
    RETURN @Parm_LfdNr

  INSERT INTO dbo.ArtikelTexte (Lkz,Artnr,LfdNr,LineData) 
       VALUES (@Parm_Lkz,@Parm_Artnr,STR(@Parm_LfdNr,6),@mText)
  SET @Parm_LfdNr = @Parm_LfdNr + 1
  RETURN @Parm_LfdNr
GO


if 0 < (select count(*) from sysobjects where [xtype] = 'P' and [name] = 'hwp_MakeArtikelLangtext')
	DROP PROCEDURE dbo.hwp_MakeArtikelLangtext
GO
-- Diese Prozedur stellt den kompletten
-- Artikellangtext abhängig vom Druckkennzeichen
-- in die Tabelle ArtikelTexte ab
CREATE PROCEDURE dbo.hwp_MakeArtikelLangtext
--ALTER PROCEDURE hwp_MakeArtikelLangtext
        @Parm_Lkz     char(2)
       ,@Parm_Artnr   char(18)
AS
  DECLARE @mKzdrk1     integer
  DECLARE @mLfdNr      integer
  SET     @mLfdNr    = 100000

  SET @mKzdrk1 = (SELECT TOP 1 Kzdrk1 FROM Stammpositionen WHERE Lkz=@Parm_Lkz AND Artnr=@Parm_Artnr)

  DELETE from ArtikelTexte WHERE Lkz=@Parm_Lkz AND Artnr=@Parm_Artnr

  IF @mKzdrk1 IS NULL
  BEGIN
    RETURN
  END

  ------------------------------------------ 1+2
  IF @mKzdrk1 = 0
  BEGIN
    EXECUTE @mLfdNr = hwp_MakeArtikelText_K1 @Parm_Lkz,@Parm_Artnr,@mLfdNr
    EXECUTE @mLfdNr = hwp_MakeArtikelText_K2 @Parm_Lkz,@Parm_Artnr,@mLfdNr
    RETURN
  END

  ------------------------------------------ L
  IF @mKzdrk1 = 1    
  BEGIN
    EXECUTE @mLfdNr = hwp_MakeArtikelText_L  @Parm_Lkz,@Parm_Artnr,@mLfdNr
    RETURN
  END

  ------------------------------------------ 1+D
  IF @mKzdrk1 = 2    
  BEGIN
    EXECUTE @mLfdNr = hwp_MakeArtikelText_K1 @Parm_Lkz,@Parm_Artnr,@mLfdNr
    EXECUTE @mLfdNr = hwp_MakeArtikelText_D  @Parm_Lkz,@Parm_Artnr,@mLfdNr
    RETURN
  END

  ------------------------------------------ L+D
  IF @mKzdrk1 = 3    
  BEGIN
    EXECUTE @mLfdNr = hwp_MakeArtikelText_L  @Parm_Lkz,@Parm_Artnr,@mLfdNr
    EXECUTE @mLfdNr = hwp_MakeArtikelText_D  @Parm_Lkz,@Parm_Artnr,@mLfdNr
    RETURN
  END

  ------------------------------------------ 1+3+L
  IF @mKzdrk1 = 4    
  BEGIN
    EXECUTE @mLfdNr = hwp_MakeArtikelText_K1 @Parm_Lkz,@Parm_Artnr,@mLfdNr
    EXECUTE @mLfdNr = hwp_MakeArtikelText_K3 @Parm_Lkz,@Parm_Artnr,@mLfdNr
    EXECUTE @mLfdNr = hwp_MakeArtikelText_L  @Parm_Lkz,@Parm_Artnr,@mLfdNr
    RETURN
  END

  ------------------------------------------ 1+3+D
  IF @mKzdrk1 = 5
  BEGIN
    EXECUTE @mLfdNr = hwp_MakeArtikelText_K1 @Parm_Lkz,@Parm_Artnr,@mLfdNr
    EXECUTE @mLfdNr = hwp_MakeArtikelText_K3 @Parm_Lkz,@Parm_Artnr,@mLfdNr
    EXECUTE @mLfdNr = hwp_MakeArtikelText_D  @Parm_Lkz,@Parm_Artnr,@mLfdNr
    RETURN
  END

  ------------------------------------------ 1+3+L+D
  IF @mKzdrk1 = 6
  BEGIN
    EXECUTE @mLfdNr = hwp_MakeArtikelText_K1 @Parm_Lkz,@Parm_Artnr,@mLfdNr
    EXECUTE @mLfdNr = hwp_MakeArtikelText_K3 @Parm_Lkz,@Parm_Artnr,@mLfdNr
    EXECUTE @mLfdNr = hwp_MakeArtikelText_L  @Parm_Lkz,@Parm_Artnr,@mLfdNr
    EXECUTE @mLfdNr = hwp_MakeArtikelText_D  @Parm_Lkz,@Parm_Artnr,@mLfdNr
    RETURN
  END

  ------------------------------------------ 1+2+3
  IF @mKzdrk1 = 7
  BEGIN
    EXECUTE @mLfdNr = hwp_MakeArtikelText_K1 @Parm_Lkz,@Parm_Artnr,@mLfdNr
    EXECUTE @mLfdNr = hwp_MakeArtikelText_K2 @Parm_Lkz,@Parm_Artnr,@mLfdNr
    EXECUTE @mLfdNr = hwp_MakeArtikelText_K3 @Parm_Lkz,@Parm_Artnr,@mLfdNr
    RETURN
  END

  ------------------------------------------ 1+2+L+3
  IF @mKzdrk1 = 8
  BEGIN
    EXECUTE @mLfdNr = hwp_MakeArtikelText_K1 @Parm_Lkz,@Parm_Artnr,@mLfdNr
    EXECUTE @mLfdNr = hwp_MakeArtikelText_K2 @Parm_Lkz,@Parm_Artnr,@mLfdNr
    EXECUTE @mLfdNr = hwp_MakeArtikelText_L  @Parm_Lkz,@Parm_Artnr,@mLfdNr
    EXECUTE @mLfdNr = hwp_MakeArtikelText_K3 @Parm_Lkz,@Parm_Artnr,@mLfdNr
    RETURN
  END

  ------------------------------------------ L+3
  IF @mKzdrk1 = 9
  BEGIN
    EXECUTE @mLfdNr = hwp_MakeArtikelText_L  @Parm_Lkz,@Parm_Artnr,@mLfdNr
    EXECUTE @mLfdNr = hwp_MakeArtikelText_K3 @Parm_Lkz,@Parm_Artnr,@mLfdNr
    RETURN
  END
GO


if 0 < (select count(*) from sysobjects where [xtype] = 'P' and [name] = 'hwp_AlleArtikelTexteAufloesen')
	DROP PROCEDURE dbo.hwp_AlleArtikelTexteAufloesen
GO
CREATE PROCEDURE dbo.hwp_AlleArtikelTexteAufloesen
AS
   SET NOCOUNT ON

   DECLARE CAufLoe CURSOR FOR SELECT Lkz,Artnr FROM Stammpositionen
   OPEN    CAufLoe
 
   DECLARE @fCa    integer
   DECLARE @mLkz   char(2)
   DECLARE @mArtnr char(18) 

   FETCH NEXT FROM CAufLoe INTO @mLkz,@mArtnr
   SET @fCa = @@FETCH_STATUS
   WHILE @fCa = 0
   BEGIN
     EXECUTE hwp_MakeArtikelLangtext @mLkz,@mArtnr
     FETCH NEXT FROM CAufLoe INTO @mLkz,@mArtnr
     SET @fCa = @@FETCH_STATUS
   END

   CLOSE      CAufLoe
   DEALLOCATE CAufLoe

   SET NOCOUNT OFF
   SELECT COUNT(*) AS Anzahl FROM ArtikelTexte 
GO




