-- This file is part of the it-crafts software and is subject to copyright protection.
-- (C) 2024, All rights reserved by Steffen Anders, Elsteraue - Germany
-- Visit also https://www.it-crafts.de.
--

-- -----------------------------------------------------------------------------------
-- Standardrechnungskreis absichern
UPDATE dbo.ut_Belegarten SET Rechnung = 1 WHERE Nr >= 1 AND Nr <= 4
GO

-- ----------------------------------------------------------------------------
-- Tabelle Belege_Rng aus der Tabelle Belege pflegen

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'TR'   and [name] = 'tr_Belege_Rng_Del')
  DROP TRIGGER [dbo].[tr_Belege_Rng_Del]
GO
CREATE TRIGGER [dbo].[tr_Belege_Rng_Del] ON [dbo].[ut_Belege]
FOR DELETE
AS
  SET NOCOUNT ON
  DELETE rng FROM [dbo].[ut_Belege_Rng] AS rng INNER JOIN DELETED del ON rng.RecId = del.RecId
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'TR'   and [name] = 'tr_Belege_Rechnungen_Upd')
  DROP TRIGGER [dbo].[tr_Belege_Rechnungen_Upd]
GO
CREATE TRIGGER [dbo].[tr_Belege_Rechnungen_Upd] ON [dbo].[ut_Belege]
FOR INSERT, UPDATE
AS
  SET NOCOUNT ON
  IF UPDATE([RecId])
  BEGIN
    DELETE rng FROM [dbo].[ut_Belege_Rng] AS rng INNER JOIN DELETED del ON rng.RecId = del.RecId

    INSERT INTO [dbo].[ut_Belege_Rng] (RecId)
	  (SELECT ins.RecId FROM INSERTED ins inner join dbo.ut_Belegarten ba on ba.Nr = CONVERT(int,ins.BELEGART) AND ba.Rechnung <> 0)
  END

  -- Das sollte nicht sein:
  -- Belege, die vorher Rechnungen waren in was anderes umgewandelt
  DELETE rng FROM [dbo].[ut_Belege_Rng] AS rng 
    INNER JOIN INSERTED ins ON rng.RecId = ins.RecId
    INNER JOIN dbo.ut_Belegarten ba on ba.Nr = CONVERT(int,ins.BELEGART) AND ba.Rechnung = 0

  UPDATE rng SET
		[PRJNR]    = ins.PRJNR
	   ,[BELEGART] = ins.BELEGART
	   ,[BELEGNR]  = ins.BELEGNR
	   ,[TAG]      = ins.TAG
	   ,[MONAT]    = ins.MONAT
	   ,[JAHR]     = ins.JAHR
    FROM [dbo].[ut_Belege_Rng] AS rng INNER JOIN INSERTED ins ON rng.RecId = ins.RecId
	INNER JOIN dbo.ut_Belegarten ba on ba.Nr = CONVERT(int,ins.BELEGART) AND ba.Rechnung <> 0
GO

-- -----------------------------------------------------------------------------------
-- Standardrechnungskreis absichern
UPDATE dbo.ut_Belege SET RecId = RecId
GO

-- ------------------------------------------------------------------------------------
-- Indexabfrageprozeduren der Tabelle ut_Belege aendern
-- fuer auf Rechnungen gefilterte Indizes

-- Key10
ALTER PROCEDURE [dbo].[up_First_Belege_Key10] AS
  EXECUTE [dbo].[up_First_Belege_Rng_Key10]
GO
ALTER PROCEDURE [dbo].[up_Last_Belege_Key10] AS
  EXECUTE [dbo].[up_Last_Belege_Rng_Key10]
GO
ALTER PROCEDURE [dbo].[up_Next_Belege_Key10] @oldRecId integer AS
  EXECUTE [dbo].[up_Next_Belege_Rng_Key10] @oldRecId
GO 
ALTER PROCEDURE [dbo].[up_Prior_Belege_Key10] @oldRecId integer AS
  EXECUTE [dbo].[up_Prior_Belege_Rng_Key10] @oldRecId
GO 
ALTER PROCEDURE [dbo].[up_FindF_Belege_Key10] @xKey char(250), @xLen integer AS
  EXECUTE [dbo].[up_FindF_Belege_Rng_Key10] @xKey, @xLen
GO
ALTER PROCEDURE [dbo].[up_FindL_Belege_Key10] @xKey char(250), @xLen integer AS
  EXECUTE [dbo].[up_FindL_Belege_Rng_Key10] @xKey, @xLen
GO

-- Key11
ALTER PROCEDURE [dbo].[up_First_Belege_Key11] AS
  EXECUTE [dbo].[up_First_Belege_Rng_Key11]
GO
ALTER PROCEDURE [dbo].[up_Last_Belege_Key11] AS
  EXECUTE [dbo].[up_Last_Belege_Rng_Key11]
GO
ALTER PROCEDURE [dbo].[up_Next_Belege_Key11] @oldRecId integer AS
  EXECUTE [dbo].[up_Next_Belege_Rng_Key11] @oldRecId
GO 
ALTER PROCEDURE [dbo].[up_Prior_Belege_Key11] @oldRecId integer AS
  EXECUTE [dbo].[up_Prior_Belege_Rng_Key11] @oldRecId
GO 
ALTER PROCEDURE [dbo].[up_FindF_Belege_Key11] @xKey char(250), @xLen integer AS
  EXECUTE [dbo].[up_FindF_Belege_Rng_Key11] @xKey, @xLen
GO
ALTER PROCEDURE [dbo].[up_FindL_Belege_Key11] @xKey char(250), @xLen integer AS
  EXECUTE [dbo].[up_FindL_Belege_Rng_Key11] @xKey, @xLen
GO

-- Key12
ALTER PROCEDURE [dbo].[up_First_Belege_Key12] AS
  EXECUTE [dbo].[up_First_Belege_Rng_Key12]
GO
ALTER PROCEDURE [dbo].[up_Last_Belege_Key12] AS
  EXECUTE [dbo].[up_Last_Belege_Rng_Key12]
GO
ALTER PROCEDURE [dbo].[up_Next_Belege_Key12] @oldRecId integer AS
  EXECUTE [dbo].[up_Next_Belege_Rng_Key12] @oldRecId
GO 
ALTER PROCEDURE [dbo].[up_Prior_Belege_Key12] @oldRecId integer AS
  EXECUTE [dbo].[up_Prior_Belege_Rng_Key12] @oldRecId
GO 
ALTER PROCEDURE [dbo].[up_FindF_Belege_Key12] @xKey char(250), @xLen integer AS
  EXECUTE [dbo].[up_FindF_Belege_Rng_Key12] @xKey, @xLen
GO
ALTER PROCEDURE [dbo].[up_FindL_Belege_Key12] @xKey char(250), @xLen integer AS
  EXECUTE [dbo].[up_FindL_Belege_Rng_Key12] @xKey, @xLen
GO

-- Key13
ALTER PROCEDURE [dbo].[up_First_Belege_Key13] AS
  EXECUTE [dbo].[up_First_Belege_Rng_Key13]
GO
ALTER PROCEDURE [dbo].[up_Last_Belege_Key13] AS
  EXECUTE [dbo].[up_Last_Belege_Rng_Key13]
GO
ALTER PROCEDURE [dbo].[up_Next_Belege_Key13] @oldRecId integer AS
  EXECUTE [dbo].[up_Next_Belege_Rng_Key13] @oldRecId
GO 
ALTER PROCEDURE [dbo].[up_Prior_Belege_Key13] @oldRecId integer AS
  EXECUTE [dbo].[up_Prior_Belege_Rng_Key13] @oldRecId
GO 
ALTER PROCEDURE [dbo].[up_FindF_Belege_Key13] @xKey char(250), @xLen integer AS
  EXECUTE [dbo].[up_FindF_Belege_Rng_Key13] @xKey, @xLen
GO
ALTER PROCEDURE [dbo].[up_FindL_Belege_Key13] @xKey char(250), @xLen integer AS
  EXECUTE [dbo].[up_FindL_Belege_Rng_Key13] @xKey, @xLen
GO

-- ------------------------------------------------------------------------------------
-- Volltextindexaufbau der Tabelle ut_Belege ersetzen

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_TrSel_Belege')
  DROP PROCEDURE [dbo].[up_TrSel_Belege]
GO
CREATE PROCEDURE [dbo].[up_TrSel_Belege]
AS
  SELECT be.RecId,
       (	
         CASE WHEN ba.Rechnung <> 0	THEN 
			'BVRRVB'
		 ELSE 
			'BV'+LTRIM(BELEGART)+'VB'
         END		
       )
      +LTRIM(RTRIM([PRJNR]))
      +LTRIM(RTRIM([BELEGNR]))
      +TAG+'.'+MONAT+'.'+JAHR
      +LTRIM(RTRIM([BELARTTEXT]))
      +LTRIM(RTRIM([KDNR]))
      +LTRIM(RTRIM([BLNR]))
      +LTRIM(RTRIM([ANREDE]))
      +LTRIM(RTRIM([VORNAME]))
      +LTRIM(RTRIM([NAME1]))
      +LTRIM(RTRIM([NAME2]))
      +LTRIM(RTRIM([ZUHAENDEN]))
      +LTRIM(RTRIM([STRASSE]))
      +LTRIM(RTRIM([LAND]))
      +LTRIM(RTRIM([PLZ]))
      +LTRIM(RTRIM([ORT]))
      +LTRIM(RTRIM([BRANREDE]))
      +LTRIM(RTRIM([VORHABEN]))
      +LTRIM(RTRIM([BETREFF1]))
      +LTRIM(RTRIM([BETREFF2]))
      +LTRIM(RTRIM([BETREFF3]))
      +LTRIM(RTRIM([UNSERZEI]))
      +LTRIM(RTRIM([WAEHRUNG]))
      +LTRIM(RTRIM([KST]))
      +LTRIM(RTRIM([KTR]))
      +LTRIM(RTRIM([DEBKONTO]))
      +LTRIM(RTRIM([GAEBNAME]))
    AS Data
  FROM dbo.ut_Belege be INNER JOIN dbo.ut_Belegarten ba on ba.Nr = CONVERT(int,be.BELEGART)
  WHERE    be.RecId > 0
  ORDER BY be.RecId
GO

-- EXECUTE [dbo].[up_TrSel_Belege]
