-- manuelle Erweiterung der Tabelle PosRecherche

IF 0 = (select count(*) from syscolumns sc inner join sysobjects so
        on sc.[id] = so.[id] and so.[name] = 'ut_PosRecherche' and sc.[name] = 'PosText')
BEGIN
  ALTER TABLE [dbo].[ut_PosRecherche] ADD [PosText] varchar(4000) NOT NULL DEFAULT ' '
END
GO

IF 0 = (select count(*) from sysindexes si inner join sysobjects so on
   so.[xtype]    = 'U' and so.[name] = 'ut_BelText' and so.[id] = si.[id] and si.[name] = 'Ik_Key0')
CREATE INDEX [Ik_Key0] ON [dbo].[ut_BelText] ([PrjNr],[BelArt],[BelNr],[RefKey],[TextArt],[LfdNr],[RecId])
GO

IF 0 = (select count(*) from sysindexes si inner join sysobjects so on
   so.[xtype]    = 'U' and so.[name] = 'ut_Belege' and so.[id] = si.[id] and si.[name] = 'Ik_KEY0')
CREATE INDEX [Ik_KEY0] ON [dbo].[ut_Belege] ([PRJNR],[BELEGART],[BELEGNR],[RecId])
GO

/*------------------------------------------------------------*/
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Reorg_PosRecherche')
  DROP PROCEDURE [dbo].[up_Reorg_PosRecherche]
GO
CREATE PROCEDURE [dbo].[up_Reorg_PosRecherche]
  @bDbcc integer,@bRecId integer
AS
  DELETE FROM [dbo].[ut_PosRecherche]

  DECLARE Cpos CURSOR FOR SELECT p.RecId,p.PrjNr,p.BelArt,p.BelNr,p.RefKey,
     RTRIM([ArtNr]) + RTRIM([Ktxt1]) + RTRIM([Ktxt2]) + RTRIM([Ktxt3]) + RTRIM([NAME1]) + RTRIM([ORT])
             + RTRIM(p.[PrjNr]) + LTRIM(RTRIM(p.[BelNr]) + JAHR + '-' + MONAT + '-' + TAG)
     FROM [dbo].[ut_BelPos] p INNER JOIN [dbo].[ut_Belege] b ON (b.PRJNR = p.PrjNr) AND (b.BELEGART = p.BelArt) AND (b.BELEGNR = p.BelNr)
     WHERE (p.[RecId] > 0) AND (p.[Dropped] = 0) AND (p.[SatzArt] > 1) AND (p.[SatzArt] <= 8) ORDER BY p.[RecId]

  DECLARE  @NextId integer
  DECLARE  @CurrRecId integer
  DECLARE  @CurrPrjNr char(8)
  DECLARE  @CurrBelNr char(8)
  DECLARE  @CurrBelArt char(2)
  DECLARE  @CurrRefKey char(6)
  DECLARE  @CurrText varchar(4000)
  DECLARE  @CurrCntChar integer
  DECLARE  @CurrPosText char(55)

    --BEGIN TRANSACTION
  SET @NextId = 1
  OPEN Cpos
  FETCH NEXT FROM Cpos INTO @CurrRecId,@CurrPrjNr,@CurrBelArt,@CurrBelNr,@CurrRefKey,@CurrText
  WHILE (@@FETCH_STATUS = 0) BEGIN
    DECLARE Ctxt CURSOR FOR SELECT CntChar,Daten FROM [dbo].[ut_BelText] 
       WHERE ([PrjNr] = @CurrPrjNr) AND ([BelArt] = @CurrBelArt) AND ([BelNr] = @CurrBelNr) 
             AND ([RefKey] = @CurrRefKey) AND ([TextArt] >= '0') AND ([TextArt] <= '2')
       ORDER BY [TextArt],[LfdNr]
    OPEN Ctxt
    FETCH NEXT FROM Ctxt INTO @CurrCntChar, @CurrPosText
    WHILE (@@FETCH_STATUS = 0) BEGIN
      IF (@CurrCntChar > 0) AND (LEN(@CurrPosText) <= 4945) BEGIN
        SET @CurrText = @CurrText + RTRIM(SUBSTRING(@CurrPosText,1,@CurrCntChar))
      END
      FETCH NEXT FROM Ctxt INTO @CurrCntChar, @CurrPosText
    END
    CLOSE Ctxt
    DEALLOCATE Ctxt

    INSERT INTO [dbo].[ut_PosRecherche] ( [RecId],[PrjNr],[BelArt],[BelNr],[RefKey],[PosRecId],[PosText] )
                     VALUES ( @NextId,@CurrPrjNr,@CurrBelArt,@CurrBelNr,@CurrRefKey,@CurrRecId,@CurrText )
    SET @NextId = @NextId + 1
    FETCH NEXT FROM Cpos INTO @CurrRecId,@CurrPrjNr,@CurrBelArt,@CurrBelNr,@CurrRefKey,@CurrText
  END

  CLOSE Cpos
  DEALLOCATE Cpos
  --DBCC DBREINDEX ( [ut_PosRecherche] )
GO

/*------------------------------------------------------------*/
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_TrSel_PosRecherche')
  DROP PROCEDURE [dbo].[up_TrSel_PosRecherche]
GO
CREATE PROCEDURE [dbo].[up_TrSel_PosRecherche]
AS
  EXECUTE [dbo].[up_Reorg_PosRecherche] 1,1
  SELECT [RecId],[PosText] AS Data FROM [dbo].[ut_PosRecherche] WHERE RecId > 0
  ORDER BY RecId
GO

--EXECUTE [dbo].[up_Reorg_PosRecherche] 1,1
--EXECUTE up_TrSel_PosRecherche
--SELECT TOP 100 * FROM [dbo].[ut_PosRecherche] order by RecId
--SELECT COUNT(*) FROM [dbo].[ut_PosRecherche]
