if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EDKB05_DelJournale]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[EDKB05_DelJournale]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EDKB05_GetTableData]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[EDKB05_GetTableData]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EDKB05_InsJournal]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[EDKB05_InsJournal]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EDKB05_InsJournalDetail]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[EDKB05_InsJournalDetail]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EDKB05_ListJournalSaveTabellen]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[EDKB05_ListJournalSaveTabellen]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EDKB05_UpdJournalSetVollstaendig]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[EDKB05_UpdJournalSetVollstaendig]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].EDKB05_DelJournale
as
-- *********************************************************************************************************
-- * Sicherungsjob EDOKA_Journale - Daten lschen
-- * Stefan Hutter / HGI / 2004-09-14
-- * msdtc MUSS AKTIVIERT SEIN
-- *
-- * History:
-- * Date:      Who:				What:
-- * 2006-11-20 rsteimen, Z&P			renamed from [sp_journale_save_delete] to EDKB05_DelJournale
-- *********************************************************************************************************



-- Deklarationen
declare @xsql varchar(255)
declare @tblname varchar(255)
declare @datefield varchar(255)
declare @mon int
declare @yea int
declare @pk int

declare xc cursor for
   select eintragnr, left(sicherungsbestand,2) as mon, substring(sicherungsbestand,4,4) as jahr from journale_save_journal where daten_loeschen=1 and daten_geloescht=0
open xc
fetch next from xc into @pk,@mon,@yea
while @@fetch_status=0 begin
   declare xx cursor for
       select distinct tabellenname, datumfeld from journale_save_tabellen
   open xx
   fetch next from xx into @tblname, @datefield
   while @@fetch_status=0 begin
      set @xsql= ' delete from dbo.' + @tblname
      set @xsql=@xsql + ' where month('+@datefield+')='+ltrim(str(@mon))+' and year('+@datefield+')='+ltrim(str(@yea))
      SET ANSI_NULLS  ON
      SET ANSI_WARNINGS ON
      execute (@xsql)
      fetch next from xx into @tblname, @datefield
   end
   close xx
   deallocate xx

   update journale_save_journal set daten_geloescht=1, daten_geloescht_am=getdate() where eintragnr=@pk
   fetch next from xc into @pk,@mon,@yea
end
close xc
deallocate xc
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

/*-----------------------------------------------------------------------------------------
Projekt:      EDKB05
Beschreibung: Gibt alle zu Daten in einer Tabelle zurck

History:
Date:      Employee:                                    What:
2006-11-20 RS, Z&P                                      initial coding
-----------------------------------------------------------------------------------------*/
CREATE PROCEDURE dbo.EDKB05_GetTableData
(
  @LastMonth AS VARCHAR(7), -- ex. 08.2006
  @DateField AS VARCHAR(255), -- ex. JournalTS (jup, it a datetime not a timestamp)
  @TableName AS VARCHAR(255) -- ex. RevisionJournal
)
AS

DECLARE @sql VARCHAR(1024)

SET @sql = 'SELECT * FROM ' + @TableName + ' 
  WHERE DATEPART(MONTH, ' + @DateField + ') = SUBSTRING(''' + @LastMonth + ''', 0, 3)
    AND DATEPART(YEAR, ' + @DateField + ') = SUBSTRING(''' + @LastMonth + ''', 4, 5)'

--print(@sql)
EXEC(@sql)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

/*-----------------------------------------------------------------------------------------
Projekt:      EDKB05
Beschreibung: Erstellt einen Eintrag in der Journal Tabelle

History:
Date:      Employee:                                    What:
2006-11-20 RS, Z&P                                      initial coding
-----------------------------------------------------------------------------------------*/
CREATE PROCEDURE dbo.EDKB05_InsJournal
(
  @FileName VARCHAR(255),
  @Month VARCHAR(7), -- ex. 08.2006
  @EintragNr INT OUTPUT
)
AS

INSERT Journale_Save_Journal([Filename], Erstellt_am, Sicherungsbestand, Daten_Loeschen, Daten_vollstaendig,
    Daten_Geloescht, Daten_Geloescht_am)
  VALUES(@FileName, GETDATE(), @Month, 0, 0, 0, NULL)

IF @@ROWCOUNT > 0
  SET @EintragNr = SCOPE_IDENTITY()
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

/*-----------------------------------------------------------------------------------------
Projekt:      EDKB05
Beschreibung: Erstellt einen Eintrag in der JournalDetail Tabelle

History:
Date:      Employee:                                    What:
2006-11-20 RS, Z&P                                      initial coding
-----------------------------------------------------------------------------------------*/
CREATE PROCEDURE dbo.EDKB05_InsJournalDetail
(
  @JournalNr INT,
  @TableName VARCHAR(255),
  @RowCount INT
)
AS

--Note: RowCountSource and RowCountDest machen nme wkrli Senn...
INSERT Journale_Save_JournalDetail(Journale_Save_Journal, Tabelle, Anzahl_Rows_Source, Anzahl_Rows_Dest)
  VALUES(@JournalNr, @TableName, @RowCount, @RowCount)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

/*-----------------------------------------------------------------------------------------
Projekt:      EDKB05
Beschreibung: Gibt alle zu backupenden Tabellen zurck

History:
Date:      Employee:                                    What:
2006-11-20 RS, Z&P                                      initial coding
-----------------------------------------------------------------------------------------*/
CREATE PROCEDURE dbo.EDKB05_ListJournalSaveTabellen
AS

SELECT jst.NrEintrag,
       jst.TabellenName,
       jst.DatumFeld
  FROM Journale_Save_Tabellen jst
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

/*-----------------------------------------------------------------------------------------
Projekt:      EDKB05
Beschreibung: Setzt einen Journal Eintrag als vollstndig

History:
Date:      Employee:                                    What:
2006-11-21 RS, Z&P                                      initial coding
-----------------------------------------------------------------------------------------*/
CREATE PROCEDURE dbo.EDKB05_UpdJournalSetVollstaendig
(
  @JournalNr INT
)
AS

UPDATE Journale_Save_Journal
  SET Daten_vollstaendig = 1
  WHERE EintragNr = @JournalNr
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

