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

-- Belegpositionen nach laufender Nummer
IF 0 = (select count(*) from sysindexes si inner join sysobjects so on so.[xtype] = 'U' and so.[name] = 'ut_BelPos' and so.[id] = si.[id] and si.[name] = 'Ik_Key00')
  CREATE INDEX [Ik_Key00] ON [dbo].[ut_BelPos] (PrjNr,BelArt,BelNr,LfdNr,RecId)
GO

-- Belegpositionen nach Artikelnummer
IF 0 = (select count(*) from sysindexes si inner join sysobjects so on so.[xtype] = 'U' and so.[name] = 'ut_BelPos' and so.[id] = si.[id] and si.[name] = 'Ik_Key09')
  CREATE INDEX [Ik_Key09] ON [dbo].[ut_BelPos] (ArtNr,BelNr,LfdNr,RecId)
GO

-- Belegpositionstexte
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_Key00')
  CREATE INDEX [Ik_Key00] ON [dbo].[ut_BelText] (PrjNr,BelArt,BelNr,TextArt,RefKey,LfdNr,RecId)
GO

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

IF 0 = (select count(*) from sysindexes si inner join sysobjects so 
   on  so.[xtype] = 'U'  and so.[name] = 'ut_BelAufmasse' 
   and so.[id] = si.[id] and si.[name] = 'Ik_Optimize')
CREATE INDEX [Ik_Optimize] ON [dbo].[ut_BelAufmasse] 
(
   [PrjNr],[BelArt],[BelNr],[AmNr]
)
GO

IF 0 = (select count(*) from sysindexes si inner join sysobjects so 
   on  so.[xtype] = 'U'  and so.[name] = 'ut_BelBauabschnitte' 
   and so.[id] = si.[id] and si.[name] = 'Ik_Optimize')
CREATE INDEX [Ik_Optimize] ON [dbo].[ut_BelBauabschnitte]
(
   [PrjNr],[BelArt],[BelNr],[BaRef]
)
GO

IF 0 = (select count(*) from sysindexes si inner join sysobjects so 
   on  so.[xtype] = 'U'  and so.[name] = 'ut_BelMassRef' 
   and so.[id] = si.[id] and si.[name] = 'Ik_Optimize')
CREATE INDEX [Ik_Optimize] ON [dbo].[ut_BelMassRef]
(
   [PrjNr],[BelArt],[BelNr],[AmRefKey]
)
GO

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

IF 0 = (select count(*) from sysindexes si inner join sysobjects so 
   on  so.[xtype] = 'U'  and so.[name] = 'ut_BelRaum' 
   and so.[id] = si.[id] and si.[name] = 'Ik_Optimize')
CREATE INDEX [Ik_Optimize] ON [dbo].[ut_BelRaum]
(
   [PrjNr],[BelArt],[BelNr],[RaumNr]
)
GO

IF 0 = (select count(*) from sysindexes si inner join sysobjects so 
   on  so.[xtype] = 'U'  and so.[name] = 'ut_BelSummenSpeicher' 
   and so.[id] = si.[id] and si.[name] = 'Ik_Optimize')
CREATE INDEX [Ik_Optimize] ON [dbo].[ut_BelSummenSpeicher]
(
   [PrjNr],[BelArt],[BelNr],[SuSpNr]
)
GO

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

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

IF 0 = (select count(*) from sysindexes si inner join sysobjects so 
   on  so.[xtype] = 'U'  and so.[name] = 'ut_BelTransaction' 
   and so.[id] = si.[id] and si.[name] = 'Ik_Optimize')
CREATE INDEX [Ik_Optimize] ON [dbo].[ut_BelTransaction]
(
   [PrjNr],[BelArt],[BelNr],[TransNo]
)
GO

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

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

IF 0 = (select count(*) from sysindexes si inner join sysobjects so 
   on  so.[xtype] = 'U'  and so.[name] = 'ut_WartungsMeldungen' 
   and so.[id] = si.[id] and si.[name] = 'Ik_Optimize')
CREATE INDEX [Ik_Optimize] ON [dbo].[ut_WartungsMeldungen]
(
   [AuftragId], [KzMark]
)
GO

IF 0 = (select count(*) from sysindexes si inner join sysobjects so 
   on  so.[xtype] = 'U'  and so.[name] = 'ut_WartungsMessDaten' 
   and so.[id] = si.[id] and si.[name] = 'Ik_Optimize')
CREATE INDEX [Ik_Optimize] ON [dbo].[ut_WartungsMessDaten]
(
   [AnlagenId]
)
GO

IF 0 = (select count(*) from sysindexes si inner join sysobjects so 
   on  so.[xtype] = 'U'  and so.[name] = 'ut_WartungsTodo' 
   and so.[id] = si.[id] and si.[name] = 'Ik_Optimize')
CREATE INDEX [Ik_Optimize] ON [dbo].[ut_WartungsTodo]
(
   [AnlagenId],[PosNr]
)
GO

IF 0 = (select count(*) from sysindexes si inner join sysobjects so 
   on  so.[xtype] = 'U'  and so.[name] = 'ut_WartungsTurnus' 
   and so.[id] = si.[id] and si.[name] = 'Ik_Optimize')
CREATE INDEX [Ik_Optimize] ON [dbo].[ut_WartungsTurnus]
(
   [AnlagenId]
)
GO

IF 0 = (select count(*) from sysindexes si inner join sysobjects so 
   on  so.[xtype] = 'U'  and so.[name] = 'ut_WartungsTypen' 
   and so.[id] = si.[id] and si.[name] = 'Ik_Optimize')
CREATE INDEX [Ik_Optimize] ON [dbo].[ut_WartungsTypen]
(
   [Ident]
)
GO

IF 0 = (select count(*) from sysindexes si inner join sysobjects so 
   on  so.[xtype] = 'U'  and so.[name] = 'ut_WartungsTypenDaten' 
   and so.[id] = si.[id] and si.[name] = 'Ik_Optimize')
CREATE INDEX [Ik_Optimize] ON [dbo].[ut_WartungsTypenDaten]
(
   [Ident],[LfdNr]
)
GO

-- Belegarchiv kürzen -------------------------------------------------------------------------

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'hwp_Clear_Belegarchiv')
  DROP PROCEDURE [dbo].[hwp_Clear_Belegarchiv]
GO
CREATE PROCEDURE [dbo].[hwp_Clear_Belegarchiv]
AS
  DELETE FROM [dbo].[ut_BelegArchiv] 
    WHERE  0 = (SELECT COUNT(*) FROM [dbo].[ut_Belege] B 
                  WHERE B.PRJNR    = [ut_BelegArchiv].PrjNr
                    AND B.BELEGART = [ut_BelegArchiv].BelArt
                    AND B.BELEGNR  = [ut_BelegArchiv].BelNr)
GO


IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'hwp_Drop_BelegJahr')
  DROP PROCEDURE [dbo].[hwp_Drop_BelegJahr]
GO
CREATE PROCEDURE [dbo].[hwp_Drop_BelegJahr] @paramJahrMin char(4), @paramJahrMax char(4)
AS
  DELETE FROM [dbo].[ut_Belege] WHERE JAHR >= @paramJahrMin AND JAHR <= @paramJahrMax
GO


