USE [Vertragsverwaltung]
GO

/****** Object:  StoredProcedure [dbo].[sp_rptparams_get_OE]    Script Date: 23.04.2013 08:14:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO



-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
Create PROCEDURE [dbo].[sp_rptparams_get_OE]
AS
BEGIN
SELECT     distinct oenr as keyvalue, Bezeichnung
from  dbo.oe
order by bezeichnung
END

GO

/****** Object:  StoredProcedure [dbo].[sp_rpt_fhrlst_Anzahl]    Script Date: 23.04.2013 08:14:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_rpt_fhrlst_Anzahl]
      @von DATETIME = NULL ,
      @bis DATETIME = NULL,
	  @OEStruktur INT = null
AS 
BEGIN
      IF @von IS NULL 
         SET @von = '01.03.2013'
      IF @bis IS NULL 
         SET @bis = '31.03.2013'
      IF @oestruktur IS NULL 
	     SET @oestruktur=24

      --DECLARE @per DATETIME = NULL ,
      --        @von DATETIME = NULL ,
      --        @bis DATETIME = NULL
      --SET @per = '01.03.2013'
      --SET @von = '01.03.2013'
      --SET @bis = '31.03.2013'
	  
	      
      SET NOCOUNT ON;

      DECLARE @tmpa TABLE
(
  [TGNummer] [varchar](50) NULL ,
  oenr INT ,
  sortierung INT ,
  START DATETIME ,
  Ende DATETIME ,
  [AU_offen] [INT] NOT NULL ,
  [AU_Geschlossen] [INT] NOT NULL ,
  [IN_Offen] [INT] NOT NULL ,
  [IN_Geschlossen] [INT] NOT NULL ,
  [IN_Langlaeufer] [INT] NOT NULL ,
  [IN_Aufwand] [float] NOT NULL ,
  [IN_GT_1PT] INT NOT NULL
)    
 
      DECLARE @ansatz FLOAT
      SET @ansatz = 8.5

      --IF @per IS NULL 
      --   SET @per = GETDATE()

				
	  --***************************************************************************
	  -- Relevante Mitarbeiter am Per-Datum
	  --***************************************************************************
      DECLARE @mnr INT
      DECLARE @rc INT    
      DECLARE @mm INT
      DECLARE @yy INT
	      
      DECLARE xm CURSOR
      FOR
      SELECT    *
      FROM      dbo.get_monatstabelle(@von, @bis)
      ORDER BY  start_date
      OPEN xm
      FETCH NEXT FROM xm INTO @von, @bis

      WHILE @@FETCH_STATUS = 0 
            BEGIN
                  PRINT @bis
                  DECLARE xc CURSOR
                  FOR
                  SELECT    manr
                  FROM      ma
                  WHERE     aktiv = 1
                            AND gueltig_bis >= @bis
                  OPEN xc
                  FETCH NEXT FROM xc INTO @mnr
                  WHILE @@FETCH_STATUS = 0 
                        BEGIN
                              SELECT    @rc = COUNT(*)
                              FROM      dbo.MADetails
                              WHERE     manr = @mnr
                                        AND aktiv = 1
                                        AND Gueltig_ab <= @von
							
                              IF @rc > 0 
                                 BEGIN
                           
                                       INSERT   @tmpa
                                                SELECT TOP 1
                                                        dbo.MA.TGNummer, madetails.OENr, ma.sortierung, @von, @bis, 0, 0, 0, 0, 0, 0.00, 0
                                                FROM    dbo.MA
                                                        LEFT OUTER JOIN dbo.MADetails ON dbo.MA.MANr = dbo.MADetails.MANr
                                                WHERE   dbo.MADetails.manr = @mnr
                                                        AND dbo.MADetails.Aktiv = 1
                                                        AND Gueltig_ab <= @von
                                                ORDER BY Gueltig_ab DESC

                                 END       
                              FETCH NEXT FROM xc INTO @mnr
                        END
                  CLOSE xc
                  DEALLOCATE xc      
 
		-- Geschlossene AU
                  DECLARE @anzahl INT
                  DECLARE @aufwand FLOAT                
                  DECLARE @ma VARCHAR(255)                
				                  
                  DECLARE xc CURSOR
                  FOR
                  SELECT    TGNummer, COUNT(TicketId) AS Expr1
                  FROM      dbo.TXP_AU_Geschlossen
                  WHERE     Status_Changedate BETWEEN @von AND @bis
                  GROUP BY  TGNummer                
                  OPEN xc
                  FETCH NEXT FROM xc INTO @ma, @anzahl
                  WHILE @@FETCH_STATUS = 0 
                        BEGIN
                   
                              UPDATE    @tmpa
                              SET       AU_Geschlossen = @anzahl
                              WHERE     tgnummer = @ma
                                        AND start = @von
                                        AND ende = @bis  
                              FETCH NEXT FROM xc INTO @ma, @anzahl
                        END
                  CLOSE xc 
                  DEALLOCATE xc

		-- Offene AU
                  DECLARE xc CURSOR
                  FOR
                  SELECT    TGNummer, COUNT(TicketID) AS Expr1
                  FROM      dbo.TXP_AU_Offen
                  WHERE     status_changedate BETWEEN @von AND @bis
                            AND ticketid NOT IN ( SELECT    ticketid
                                                  FROM      dbo.TXP_AU_Geschlossen
                                                  WHERE     Status_Changedate BETWEEN @von AND @bis )
                  GROUP BY  TGNummer                
                  OPEN xc
                  FETCH NEXT FROM xc INTO @ma, @anzahl
                  WHILE @@FETCH_STATUS = 0 
                        BEGIN
                              UPDATE    @tmpa
                              SET       AU_offen = @anzahl
                              WHERE     tgnummer = @ma
                                        AND start = @von
                                        AND ende = @bis  
                              FETCH NEXT FROM xc INTO @ma, @anzahl
                        END
                  CLOSE xc 
                  DEALLOCATE xc


		-- Geschlossene IN
				                  
                  DECLARE xc CURSOR
                  FOR
                  SELECT    TGNummer, COUNT(TicketId) AS Expr1
                  FROM      dbo.TXP_IN_Geschlossen
                  WHERE     Status_Changedate BETWEEN @von AND @bis
                  GROUP BY  TGNummer                
                  OPEN xc
                  FETCH NEXT FROM xc INTO @ma, @anzahl
                  WHILE @@FETCH_STATUS = 0 
                        BEGIN
                              UPDATE    @tmpa
                              SET       IN_Geschlossen = @anzahl
                              WHERE     tgnummer = @ma
                                        AND start = @von
                                        AND ende = @bis  
                              FETCH NEXT FROM xc INTO @ma, @anzahl
                        END
                  CLOSE xc 
                  DEALLOCATE xc

-- Offene IN
                  DECLARE xc CURSOR
                  FOR
                  SELECT    TGNummer, COUNT(TicketID) AS Expr1
                  FROM      dbo.TXP_IN_Offen
                  WHERE     status_changedate BETWEEN @von AND @bis
                            AND ticketid NOT IN ( SELECT    ticketid
                                                  FROM      dbo.TXP_IN_Geschlossen
                                                  WHERE     Status_Changedate BETWEEN @von AND @bis )
                  GROUP BY  TGNummer                
                  OPEN xc
                  FETCH NEXT FROM xc INTO @ma, @anzahl
                  WHILE @@FETCH_STATUS = 0 
                        BEGIN
                              UPDATE    @tmpa
                              SET       IN_offen = @anzahl
                              WHERE     tgnummer = @ma
                                        AND start = @von
                                        AND ende = @bis  
                              FETCH NEXT FROM xc INTO @ma, @anzahl
                        END
                  CLOSE xc 
                  DEALLOCATE xc


-- Offene IN Langläufer
                  DECLARE xc CURSOR
                  FOR
                  SELECT    TGNummer, COUNT(TicketID) AS Expr1
                  FROM      dbo.TXP_IN_Offene_Langlaeufer
                  GROUP BY  TGNummer                
                  OPEN xc
                  FETCH NEXT FROM xc INTO @ma, @anzahl
                  WHILE @@FETCH_STATUS = 0 
                        BEGIN
                              UPDATE    @tmpa
                              SET       IN_Langlaeufer = @anzahl
                              WHERE     tgnummer = @ma
                                        AND start = @von
                                        AND ende = @bis  
                              FETCH NEXT FROM xc INTO @ma, @anzahl
                        END
                  CLOSE xc 
                  DEALLOCATE xc


-- Geschlossene IN Aufwand
                  DECLARE xc CURSOR
                  FOR
                  SELECT    TGNummer, COUNT(Ticket_ID)
                  FROM      dbo.TXP_IN_Geschlossen_IN_Aufwand
                  WHERE     status_changedate BETWEEN @von AND @bis AND aufwand > @ansatz
                  GROUP BY  TGNummer                
                  OPEN xc
                  FETCH NEXT FROM xc INTO @ma, @aufwand
                  WHILE @@FETCH_STATUS = 0 
                        BEGIN
                              UPDATE    @tmpa
                              SET       IN_gt_1pt = IN_GT_1PT + @aufwand
                              WHERE     tgnummer = @ma
                                        AND start = @von
                                        AND ende = @bis  
                              FETCH NEXT FROM xc INTO @ma, @aufwand
                        END
                  CLOSE xc 
                  DEALLOCATE xc

-- Geschlossene IN Aufwand
                  DECLARE xc CURSOR
                  FOR
                  SELECT    TGNummer, SUM(Aufwand)
                  FROM      dbo.TXP_IN_Geschlossen_Aufwand
                  WHERE     status_changedate BETWEEN @von AND @bis
                  GROUP BY  TGNummer                
                  OPEN xc
                  FETCH NEXT FROM xc INTO @ma, @aufwand
                  WHILE @@FETCH_STATUS = 0 
                        BEGIN
                              UPDATE    @tmpa
                              SET       IN_Aufwand = in_aufwand + @aufwand
                              WHERE     tgnummer = @ma
                                        AND start = @von
                                        AND ende = @bis  
                              FETCH NEXT FROM xc INTO @ma, @aufwand
                        END
                  CLOSE xc 
                  DEALLOCATE xc


                  FETCH NEXT FROM xm INTO @von, @bis
            END

      CLOSE xm
      DEALLOCATE xm
 
 UPDATE @tmpa SET in_aufwand = in_aufwand / @ansatz

      SELECT TOP ( 100 ) PERCENT
                dbo.OE.Bezeichnung AS OE, dbo.OE.Sortierung AS OESort, dbo.MA.Name, dbo.MA.Vorname, dbo.MA.Sortierung, a.TGNummer, a.START, a.ende,
                a.sortierung AS Expr1, a.AU_offen, a.AU_Geschlossen, a.IN_Offen, a.IN_Geschlossen, a.IN_Aufwand, a.IN_GT_1PT, a.in_Langlaeufer
      INTO      #tmpfhr
      FROM      @tmpa a
                INNER JOIN dbo.MA ON a.TGNummer = dbo.MA.TGNummer
                INNER JOIN dbo.OE ON a.oenr = dbo.OE.OeNr
     WHERE     dbo.OE.oenr IN (SELECT id FROM dbo.fnkt_get_kpi_hierarchie(@oestruktur))
      ORDER BY  OESort, dbo.MA.Sortierung, dbo.MA.Name

      SELECT    OE, OESort, Name, Vorname, Sortierung, TGNummer, Expr1 AS Sort, SUM(au_offen) AS au_offen, SUM(AU_Geschlossen) AS au_geschlossen,
                SUM(IN_Offen) AS in_offen, SUM(in_geschlossen) AS in_geschlossen, SUM(in_Aufwand) AS in_Aufwand, SUM(IN_GT_1PT) AS in_get_1pt,
                SUM(IN_Langlaeufer) AS IN_Langlaeufer
      INTO      #tmpfhr1
      FROM      #tmpfhr
      GROUP BY  OE, OESort, Name, Vorname, Sortierung, TGNummer, Expr1


      SELECT    *
      FROM      #tmpfhr1
      ORDER BY  OESort, Sortierung, Name
	  
      DROP TABLE #tmpfhr
      DROP TABLE #tmpfhr1


END


GO

/****** Object:  StoredProcedure [dbo].[sp_get_madetails]    Script Date: 23.04.2013 08:14:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_get_madetails] @manr INT
AS 
BEGIN
      SET NOCOUNT ON;
      SELECT    dbo.MADetails.MADetailNr, dbo.MitarbeiterTyp.MitarbeiterTypNr, dbo.MitarbeiterTyp.Bezeichnung, dbo.MitarbeiterTyp.Fuehrungsanteil, dbo.OE.OeNr,
                dbo.OE.Bezeichnung AS Expr1, dbo.MADetails.Engagement, dbo.MADetails.Gueltig_ab, dbo.MADetails.Bemerkung, dbo.MADetails.Aktiv,
                dbo.MADetails.Erstellt_am, dbo.MADetails.Mutiert_am, dbo.MADetails.Mutierer
      FROM      dbo.MADetails
                INNER JOIN dbo.MitarbeiterTyp ON dbo.MADetails.MitarbeiterTypNr = dbo.MitarbeiterTyp.MitarbeiterTypNr
                INNER JOIN dbo.OE ON dbo.MADetails.OENr = dbo.OE.OeNr
      WHERE     dbo.MADetails.MANr = @manr
END


GO

/****** Object:  StoredProcedure [dbo].[pr_OE_Update]    Script Date: 23.04.2013 08:14:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



---------------------------------------------------------------------------------
-- Stored procedure that will update an existing row in the table 'OE'
-- Gets: @iOeNr int
-- Gets: @iParentId int
-- Gets: @sBezeichnung varchar(50)
-- Gets: @sKuerzel varchar(50)
-- Gets: @sBeschreibung varchar(1024)
-- Gets: @bAktiv bit
-- Gets: @daErstellt_am datetime
-- Gets: @daMutiert_am datetime
-- Gets: @iMutierer int
-- Gets: @iSortierung int
-- Returns: @iErrorCode int
---------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[pr_OE_Update]
	@iOeNr int,
	@iParentId int,
	@sBezeichnung varchar(50),
	@sKuerzel varchar(50),
	@sBeschreibung varchar(1024),
	@bAktiv bit,
	@daErstellt_am datetime,
	@daMutiert_am datetime,
	@iMutierer int,
	@iSortierung int,
	@iErrorCode int OUTPUT
AS
SET NOCOUNT ON
-- UPDATE an existing row in the table.
UPDATE [dbo].[OE]
SET 
	[ParentId] = @iParentId,
	[Bezeichnung] = @sBezeichnung,
	[Kuerzel] = @sKuerzel,
	[Beschreibung] = @sBeschreibung,
	[Aktiv] = @bAktiv,
	[Erstellt_am] = @daErstellt_am,
	[Mutiert_am] = @daMutiert_am,
	[Mutierer] = @iMutierer,
	[Sortierung] = @iSortierung
WHERE
	[OeNr] = @iOeNr
-- Get the Error Code for the statement just executed.
SELECT @iErrorCode=@@ERROR


GO

/****** Object:  StoredProcedure [dbo].[pr_OE_SelectOne]    Script Date: 23.04.2013 08:14:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



---------------------------------------------------------------------------------
-- Stored procedure that will select an existing row from the table 'OE'
-- based on the Primary Key.
-- Gets: @iOeNr int
-- Returns: @iErrorCode int
---------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[pr_OE_SelectOne]
	@iOeNr int,
	@iErrorCode int OUTPUT
AS
SET NOCOUNT ON
-- SELECT an existing row from the table.
SELECT
	[OeNr],
	[ParentId],
	[Bezeichnung],
	[Kuerzel],
	[Beschreibung],
	[Aktiv],
	[Erstellt_am],
	[Mutiert_am],
	[Mutierer],
	[Sortierung]
FROM [dbo].[OE]
WHERE
	[OeNr] = @iOeNr
-- Get the Error Code for the statement just executed.
SELECT @iErrorCode=@@ERROR


GO

/****** Object:  StoredProcedure [dbo].[pr_OE_SelectAll]    Script Date: 23.04.2013 08:14:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



---------------------------------------------------------------------------------
-- Stored procedure that will select all rows from the table 'OE'
-- Returns: @iErrorCode int
---------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[pr_OE_SelectAll]
	@iErrorCode int OUTPUT
AS
SET NOCOUNT ON
-- SELECT all rows from the table.
SELECT
	[OeNr],
	[ParentId],
	[Bezeichnung],
	[Kuerzel],
	[Beschreibung],
	[Aktiv],
	[Erstellt_am],
	[Mutiert_am],
	[Mutierer],
	[Sortierung]
FROM [dbo].[OE]
ORDER BY 
	[OeNr] ASC
-- Get the Error Code for the statement just executed.
SELECT @iErrorCode=@@ERROR


GO

/****** Object:  StoredProcedure [dbo].[pr_OE_Insert]    Script Date: 23.04.2013 08:14:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



---------------------------------------------------------------------------------
-- Stored procedure that will insert 1 row in the table 'OE'
-- Gets: @iOeNr int
-- Gets: @iParentId int
-- Gets: @sBezeichnung varchar(50)
-- Gets: @sKuerzel varchar(50)
-- Gets: @sBeschreibung varchar(1024)
-- Gets: @bAktiv bit
-- Gets: @daErstellt_am datetime
-- Gets: @daMutiert_am datetime
-- Gets: @iMutierer int
-- Gets: @iSortierung int
-- Returns: @iErrorCode int
---------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[pr_OE_Insert]
	@iOeNr int,
	@iParentId int,
	@sBezeichnung varchar(50),
	@sKuerzel varchar(50),
	@sBeschreibung varchar(1024),
	@bAktiv bit,
	@daErstellt_am datetime,
	@daMutiert_am datetime,
	@iMutierer int,
	@iSortierung int,
	@iErrorCode int OUTPUT
AS
SET NOCOUNT ON
-- INSERT a new row in the table.
INSERT [dbo].[OE]
(
	[OeNr],
	[ParentId],
	[Bezeichnung],
	[Kuerzel],
	[Beschreibung],
	[Aktiv],
	[Erstellt_am],
	[Mutiert_am],
	[Mutierer],
	[Sortierung]
)
VALUES
(
	@iOeNr,
	@iParentId,
	@sBezeichnung,
	@sKuerzel,
	@sBeschreibung,
	@bAktiv,
	@daErstellt_am,
	@daMutiert_am,
	@iMutierer,
	@iSortierung
)
-- Get the Error Code for the statement just executed.
SELECT @iErrorCode=@@ERROR


GO

/****** Object:  StoredProcedure [dbo].[pr_OE_Delete]    Script Date: 23.04.2013 08:14:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



---------------------------------------------------------------------------------
-- Stored procedure that will delete an existing row from the table 'OE'
-- using the Primary Key. 
-- Gets: @iOeNr int
-- Returns: @iErrorCode int
---------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[pr_OE_Delete]
	@iOeNr int,
	@iErrorCode int OUTPUT
AS
SET NOCOUNT ON
-- DELETE an existing row from the table.
DELETE FROM [dbo].[OE]
WHERE
	[OeNr] = @iOeNr
-- Get the Error Code for the statement just executed.
SELECT @iErrorCode=@@ERROR


GO

/****** Object:  StoredProcedure [dbo].[pr_MitarbeiterTyp_SelectAll]    Script Date: 23.04.2013 08:14:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


---------------------------------------------------------------------------------
-- Stored procedure that will select all rows from the table 'MitarbeiterTyp'
-- Returns: @iErrorCode int
---------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[pr_MitarbeiterTyp_SelectAll]
	@iErrorCode int OUTPUT
AS
SET NOCOUNT ON
-- SELECT all rows from the table.
SELECT
	[MitarbeiterTypNr],
	[Bezeichnung],
	[Beschreibung],
	[Fuehrungsanteil],
	[Aktiv],
	[Erstellt_am],
	[Mutiert_am],
	[Mutierer]
FROM [dbo].[MitarbeiterTyp]
ORDER BY 
	[MitarbeiterTypNr] ASC
-- Get the Error Code for the statement just executed.
SELECT @iErrorCode=@@ERROR

GO

/****** Object:  StoredProcedure [dbo].[pr_MitarbeiterTyp_SelectOne]    Script Date: 23.04.2013 08:14:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


---------------------------------------------------------------------------------
-- Stored procedure that will select an existing row from the table 'MitarbeiterTyp'
-- based on the Primary Key.
-- Gets: @iMitarbeiterTypNr int
-- Returns: @iErrorCode int
---------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[pr_MitarbeiterTyp_SelectOne]
	@iMitarbeiterTypNr int,
	@iErrorCode int OUTPUT
AS
SET NOCOUNT ON
-- SELECT an existing row from the table.
SELECT
	[MitarbeiterTypNr],
	[Bezeichnung],
	[Beschreibung],
	[Fuehrungsanteil],
	[Aktiv],
	[Erstellt_am],
	[Mutiert_am],
	[Mutierer]
FROM [dbo].[MitarbeiterTyp]
WHERE
	[MitarbeiterTypNr] = @iMitarbeiterTypNr
-- Get the Error Code for the statement just executed.
SELECT @iErrorCode=@@ERROR

GO

/****** Object:  StoredProcedure [dbo].[pr_MitarbeiterTyp_Delete]    Script Date: 23.04.2013 08:14:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


---------------------------------------------------------------------------------
-- Stored procedure that will delete an existing row from the table 'MitarbeiterTyp'
-- using the Primary Key. 
-- Gets: @iMitarbeiterTypNr int
-- Returns: @iErrorCode int
---------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[pr_MitarbeiterTyp_Delete]
	@iMitarbeiterTypNr int,
	@iErrorCode int OUTPUT
AS
SET NOCOUNT ON
-- DELETE an existing row from the table.
DELETE FROM [dbo].[MitarbeiterTyp]
WHERE
	[MitarbeiterTypNr] = @iMitarbeiterTypNr
-- Get the Error Code for the statement just executed.
SELECT @iErrorCode=@@ERROR

GO

/****** Object:  StoredProcedure [dbo].[pr_MitarbeiterTyp_Update]    Script Date: 23.04.2013 08:14:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


---------------------------------------------------------------------------------
-- Stored procedure that will update an existing row in the table 'MitarbeiterTyp'
-- Gets: @iMitarbeiterTypNr int
-- Gets: @sBezeichnung varchar(50)
-- Gets: @sBeschreibung varchar(50)
-- Gets: @iFuehrungsanteil int
-- Gets: @bAktiv bit
-- Gets: @daErstellt_am datetime
-- Gets: @daMutiert_am datetime
-- Gets: @iMutierer int
-- Returns: @iErrorCode int
---------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[pr_MitarbeiterTyp_Update]
	@iMitarbeiterTypNr int,
	@sBezeichnung varchar(50),
	@sBeschreibung varchar(50),
	@iFuehrungsanteil int,
	@bAktiv bit,
	@daErstellt_am datetime,
	@daMutiert_am datetime,
	@iMutierer int,
	@iErrorCode int OUTPUT
AS
SET NOCOUNT ON
-- UPDATE an existing row in the table.
UPDATE [dbo].[MitarbeiterTyp]
SET 
	[Bezeichnung] = @sBezeichnung,
	[Beschreibung] = @sBeschreibung,
	[Fuehrungsanteil] = @iFuehrungsanteil,
	[Aktiv] = @bAktiv,
	[Erstellt_am] = @daErstellt_am,
	[Mutiert_am] = @daMutiert_am,
	[Mutierer] = @iMutierer
WHERE
	[MitarbeiterTypNr] = @iMitarbeiterTypNr
-- Get the Error Code for the statement just executed.
SELECT @iErrorCode=@@ERROR

GO

/****** Object:  StoredProcedure [dbo].[pr_MitarbeiterTyp_Insert]    Script Date: 23.04.2013 08:14:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


---------------------------------------------------------------------------------
-- Stored procedure that will insert 1 row in the table 'MitarbeiterTyp'
-- Gets: @iMitarbeiterTypNr int
-- Gets: @sBezeichnung varchar(50)
-- Gets: @sBeschreibung varchar(50)
-- Gets: @iFuehrungsanteil int
-- Gets: @bAktiv bit
-- Gets: @daErstellt_am datetime
-- Gets: @daMutiert_am datetime
-- Gets: @iMutierer int
-- Returns: @iErrorCode int
---------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[pr_MitarbeiterTyp_Insert]
	@iMitarbeiterTypNr int,
	@sBezeichnung varchar(50),
	@sBeschreibung varchar(50),
	@iFuehrungsanteil int,
	@bAktiv bit,
	@daErstellt_am datetime,
	@daMutiert_am datetime,
	@iMutierer int,
	@iErrorCode int OUTPUT
AS
SET NOCOUNT ON
-- INSERT a new row in the table.
INSERT [dbo].[MitarbeiterTyp]
(
	[MitarbeiterTypNr],
	[Bezeichnung],
	[Beschreibung],
	[Fuehrungsanteil],
	[Aktiv],
	[Erstellt_am],
	[Mutiert_am],
	[Mutierer]
)
VALUES
(
	@iMitarbeiterTypNr,
	@sBezeichnung,
	@sBeschreibung,
	@iFuehrungsanteil,
	@bAktiv,
	@daErstellt_am,
	@daMutiert_am,
	@iMutierer
)
-- Get the Error Code for the statement just executed.
SELECT @iErrorCode=@@ERROR

GO

/****** Object:  StoredProcedure [dbo].[pr_MADetails_SelectAll]    Script Date: 23.04.2013 08:14:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


---------------------------------------------------------------------------------
-- Stored procedure that will select all rows from the table 'MADetails'
-- Returns: @iErrorCode int
---------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[pr_MADetails_SelectAll]
	@iErrorCode int OUTPUT
AS
SET NOCOUNT ON
-- SELECT all rows from the table.
SELECT
	[MADetailNr],
	[MANr],
	[MitarbeiterTypNr],
	[OENr],
	[Engagement],
	[Gueltig_ab],
	[Bemerkung],
	[Aktiv],
	[Erstellt_am],
	[Mutiert_am],
	[Mutierer]
FROM [dbo].[MADetails]
ORDER BY 
	[MADetailNr] ASC
-- Get the Error Code for the statement just executed.
SELECT @iErrorCode=@@ERROR

GO

/****** Object:  StoredProcedure [dbo].[pr_MADetails_SelectOne]    Script Date: 23.04.2013 08:14:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


---------------------------------------------------------------------------------
-- Stored procedure that will select an existing row from the table 'MADetails'
-- based on the Primary Key.
-- Gets: @iMADetailNr int
-- Returns: @iErrorCode int
---------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[pr_MADetails_SelectOne]
	@iMADetailNr int,
	@iErrorCode int OUTPUT
AS
SET NOCOUNT ON
-- SELECT an existing row from the table.
SELECT
	[MADetailNr],
	[MANr],
	[MitarbeiterTypNr],
	[OENr],
	[Engagement],
	[Gueltig_ab],
	[Bemerkung],
	[Aktiv],
	[Erstellt_am],
	[Mutiert_am],
	[Mutierer]
FROM [dbo].[MADetails]
WHERE
	[MADetailNr] = @iMADetailNr
-- Get the Error Code for the statement just executed.
SELECT @iErrorCode=@@ERROR

GO

/****** Object:  StoredProcedure [dbo].[pr_MADetails_Delete]    Script Date: 23.04.2013 08:14:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


---------------------------------------------------------------------------------
-- Stored procedure that will delete an existing row from the table 'MADetails'
-- using the Primary Key. 
-- Gets: @iMADetailNr int
-- Returns: @iErrorCode int
---------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[pr_MADetails_Delete]
	@iMADetailNr int,
	@iErrorCode int OUTPUT
AS
SET NOCOUNT ON
-- DELETE an existing row from the table.
DELETE FROM [dbo].[MADetails]
WHERE
	[MADetailNr] = @iMADetailNr
-- Get the Error Code for the statement just executed.
SELECT @iErrorCode=@@ERROR

GO

/****** Object:  StoredProcedure [dbo].[pr_MADetails_Update]    Script Date: 23.04.2013 08:14:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


---------------------------------------------------------------------------------
-- Stored procedure that will update an existing row in the table 'MADetails'
-- Gets: @iMADetailNr int
-- Gets: @iMANr int
-- Gets: @iMitarbeiterTypNr int
-- Gets: @iOENr int
-- Gets: @iEngagement int
-- Gets: @daGueltig_ab datetime
-- Gets: @sBemerkung varchar(1024)
-- Gets: @bAktiv bit
-- Gets: @daErstellt_am datetime
-- Gets: @daMutiert_am datetime
-- Gets: @iMutierer int
-- Returns: @iErrorCode int
---------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[pr_MADetails_Update]
	@iMADetailNr int,
	@iMANr int,
	@iMitarbeiterTypNr int,
	@iOENr int,
	@iEngagement int,
	@daGueltig_ab datetime,
	@sBemerkung varchar(1024),
	@bAktiv bit,
	@daErstellt_am datetime,
	@daMutiert_am datetime,
	@iMutierer int,
	@iErrorCode int OUTPUT
AS
SET NOCOUNT ON
-- UPDATE an existing row in the table.
UPDATE [dbo].[MADetails]
SET 
	[MANr] = @iMANr,
	[MitarbeiterTypNr] = @iMitarbeiterTypNr,
	[OENr] = @iOENr,
	[Engagement] = @iEngagement,
	[Gueltig_ab] = @daGueltig_ab,
	[Bemerkung] = @sBemerkung,
	[Aktiv] = @bAktiv,
	[Erstellt_am] = @daErstellt_am,
	[Mutiert_am] = @daMutiert_am,
	[Mutierer] = @iMutierer
WHERE
	[MADetailNr] = @iMADetailNr
-- Get the Error Code for the statement just executed.
SELECT @iErrorCode=@@ERROR

GO

/****** Object:  StoredProcedure [dbo].[pr_MADetails_Insert]    Script Date: 23.04.2013 08:14:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


---------------------------------------------------------------------------------
-- Stored procedure that will insert 1 row in the table 'MADetails'
-- Gets: @iMADetailNr int
-- Gets: @iMANr int
-- Gets: @iMitarbeiterTypNr int
-- Gets: @iOENr int
-- Gets: @iEngagement int
-- Gets: @daGueltig_ab datetime
-- Gets: @sBemerkung varchar(1024)
-- Gets: @bAktiv bit
-- Gets: @daErstellt_am datetime
-- Gets: @daMutiert_am datetime
-- Gets: @iMutierer int
-- Returns: @iErrorCode int
---------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[pr_MADetails_Insert]
	@iMADetailNr int,
	@iMANr int,
	@iMitarbeiterTypNr int,
	@iOENr int,
	@iEngagement int,
	@daGueltig_ab datetime,
	@sBemerkung varchar(1024),
	@bAktiv bit,
	@daErstellt_am datetime,
	@daMutiert_am datetime,
	@iMutierer int,
	@iErrorCode int OUTPUT
AS
SET NOCOUNT ON
-- INSERT a new row in the table.
INSERT [dbo].[MADetails]
(
	[MADetailNr],
	[MANr],
	[MitarbeiterTypNr],
	[OENr],
	[Engagement],
	[Gueltig_ab],
	[Bemerkung],
	[Aktiv],
	[Erstellt_am],
	[Mutiert_am],
	[Mutierer]
)
VALUES
(
	@iMADetailNr,
	@iMANr,
	@iMitarbeiterTypNr,
	@iOENr,
	@iEngagement,
	@daGueltig_ab,
	@sBemerkung,
	@bAktiv,
	@daErstellt_am,
	@daMutiert_am,
	@iMutierer
)
-- Get the Error Code for the statement just executed.
SELECT @iErrorCode=@@ERROR

GO

/****** Object:  StoredProcedure [dbo].[pr_MA_SelectAll]    Script Date: 23.04.2013 08:14:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


---------------------------------------------------------------------------------
-- Stored procedure that will select all rows from the table 'MA'
-- Returns: @iErrorCode int
---------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[pr_MA_SelectAll]
	@iErrorCode int OUTPUT
AS
SET NOCOUNT ON
-- SELECT all rows from the table.
SELECT
	[MANr],
	[Name],
	[Vorname],
	[TGNummer],
	[Kurzzeichen],
	[Aktiv],
	[Erstellt_am],
	[Mutiert_am],
	[Mutierer],
	[Gueltig_bis],
	[Sortierung]
FROM [dbo].[MA]
ORDER BY 
	[MANr] ASC
-- Get the Error Code for the statement just executed.
SELECT @iErrorCode=@@ERROR

GO

/****** Object:  StoredProcedure [dbo].[pr_MA_SelectOne]    Script Date: 23.04.2013 08:14:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


---------------------------------------------------------------------------------
-- Stored procedure that will select an existing row from the table 'MA'
-- based on the Primary Key.
-- Gets: @iMANr int
-- Returns: @iErrorCode int
---------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[pr_MA_SelectOne]
	@iMANr int,
	@iErrorCode int OUTPUT
AS
SET NOCOUNT ON
-- SELECT an existing row from the table.
SELECT
	[MANr],
	[Name],
	[Vorname],
	[TGNummer],
	[Kurzzeichen],
	[Aktiv],
	[Erstellt_am],
	[Mutiert_am],
	[Mutierer],
	[Gueltig_bis],
	[Sortierung]
FROM [dbo].[MA]
WHERE
	[MANr] = @iMANr
-- Get the Error Code for the statement just executed.
SELECT @iErrorCode=@@ERROR

GO

/****** Object:  StoredProcedure [dbo].[pr_MA_Delete]    Script Date: 23.04.2013 08:14:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


---------------------------------------------------------------------------------
-- Stored procedure that will delete an existing row from the table 'MA'
-- using the Primary Key. 
-- Gets: @iMANr int
-- Returns: @iErrorCode int
---------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[pr_MA_Delete]
	@iMANr int,
	@iErrorCode int OUTPUT
AS
SET NOCOUNT ON
-- DELETE an existing row from the table.
DELETE FROM [dbo].[MA]
WHERE
	[MANr] = @iMANr
-- Get the Error Code for the statement just executed.
SELECT @iErrorCode=@@ERROR

GO

/****** Object:  StoredProcedure [dbo].[pr_MA_Update]    Script Date: 23.04.2013 08:14:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


---------------------------------------------------------------------------------
-- Stored procedure that will update an existing row in the table 'MA'
-- Gets: @iMANr int
-- Gets: @sName varchar(50)
-- Gets: @sVorname varchar(50)
-- Gets: @sTGNummer varchar(50)
-- Gets: @sKurzzeichen varchar(50)
-- Gets: @bAktiv bit
-- Gets: @daErstellt_am datetime
-- Gets: @daMutiert_am datetime
-- Gets: @iMutierer int
-- Gets: @daGueltig_bis datetime
-- Gets: @iSortierung int
-- Returns: @iErrorCode int
---------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[pr_MA_Update]
	@iMANr int,
	@sName varchar(50),
	@sVorname varchar(50),
	@sTGNummer varchar(50),
	@sKurzzeichen varchar(50),
	@bAktiv bit,
	@daErstellt_am datetime,
	@daMutiert_am datetime,
	@iMutierer int,
	@daGueltig_bis datetime,
	@iSortierung int,
	@iErrorCode int OUTPUT
AS
SET NOCOUNT ON
-- UPDATE an existing row in the table.
UPDATE [dbo].[MA]
SET 
	[Name] = @sName,
	[Vorname] = @sVorname,
	[TGNummer] = @sTGNummer,
	[Kurzzeichen] = @sKurzzeichen,
	[Aktiv] = @bAktiv,
	[Erstellt_am] = @daErstellt_am,
	[Mutiert_am] = @daMutiert_am,
	[Mutierer] = @iMutierer,
	[Gueltig_bis] = @daGueltig_bis,
	[Sortierung] = @iSortierung
WHERE
	[MANr] = @iMANr
-- Get the Error Code for the statement just executed.
SELECT @iErrorCode=@@ERROR

GO

/****** Object:  StoredProcedure [dbo].[pr_MA_Insert]    Script Date: 23.04.2013 08:14:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


---------------------------------------------------------------------------------
-- Stored procedure that will insert 1 row in the table 'MA'
-- Gets: @iMANr int
-- Gets: @sName varchar(50)
-- Gets: @sVorname varchar(50)
-- Gets: @sTGNummer varchar(50)
-- Gets: @sKurzzeichen varchar(50)
-- Gets: @bAktiv bit
-- Gets: @daErstellt_am datetime
-- Gets: @daMutiert_am datetime
-- Gets: @iMutierer int
-- Gets: @daGueltig_bis datetime
-- Gets: @iSortierung int
-- Returns: @iErrorCode int
---------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[pr_MA_Insert]
	@iMANr int,
	@sName varchar(50),
	@sVorname varchar(50),
	@sTGNummer varchar(50),
	@sKurzzeichen varchar(50),
	@bAktiv bit,
	@daErstellt_am datetime,
	@daMutiert_am datetime,
	@iMutierer int,
	@daGueltig_bis datetime,
	@iSortierung int,
	@iErrorCode int OUTPUT
AS
SET NOCOUNT ON
-- INSERT a new row in the table.
INSERT [dbo].[MA]
(
	[MANr],
	[Name],
	[Vorname],
	[TGNummer],
	[Kurzzeichen],
	[Aktiv],
	[Erstellt_am],
	[Mutiert_am],
	[Mutierer],
	[Gueltig_bis],
	[Sortierung]
)
VALUES
(
	@iMANr,
	@sName,
	@sVorname,
	@sTGNummer,
	@sKurzzeichen,
	@bAktiv,
	@daErstellt_am,
	@daMutiert_am,
	@iMutierer,
	@daGueltig_bis,
	@iSortierung
)
-- Get the Error Code for the statement just executed.
SELECT @iErrorCode=@@ERROR

GO

/****** Object:  StoredProcedure [dbo].[pr_Jahrestabelle_SelectAll]    Script Date: 23.04.2013 08:14:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


---------------------------------------------------------------------------------
-- Stored procedure that will select all rows from the table 'Jahrestabelle'
-- Returns: @iErrorCode int
---------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[pr_Jahrestabelle_SelectAll]
	@iErrorCode int OUTPUT
AS
SET NOCOUNT ON
-- SELECT all rows from the table.
SELECT
	[Eintragnr],
	[Jahr],
	[Monat],
	[Plantage],
	[FaktorNr],
	[Arbeitstage],
	[Aktiv],
	[Erstellt_am],
	[Mutiert_am],
	[Mutierer]
FROM [dbo].[Jahrestabelle]
ORDER BY 
	[Eintragnr] ASC
-- Get the Error Code for the statement just executed.
SELECT @iErrorCode=@@ERROR

GO

/****** Object:  StoredProcedure [dbo].[pr_Jahrestabelle_SelectOne]    Script Date: 23.04.2013 08:14:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


---------------------------------------------------------------------------------
-- Stored procedure that will select an existing row from the table 'Jahrestabelle'
-- based on the Primary Key.
-- Gets: @iEintragnr int
-- Returns: @iErrorCode int
---------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[pr_Jahrestabelle_SelectOne]
	@iEintragnr int,
	@iErrorCode int OUTPUT
AS
SET NOCOUNT ON
-- SELECT an existing row from the table.
SELECT
	[Eintragnr],
	[Jahr],
	[Monat],
	[Plantage],
	[FaktorNr],
	[Arbeitstage],
	[Aktiv],
	[Erstellt_am],
	[Mutiert_am],
	[Mutierer]
FROM [dbo].[Jahrestabelle]
WHERE
	[Eintragnr] = @iEintragnr
-- Get the Error Code for the statement just executed.
SELECT @iErrorCode=@@ERROR

GO

/****** Object:  StoredProcedure [dbo].[pr_Jahrestabelle_Delete]    Script Date: 23.04.2013 08:14:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


---------------------------------------------------------------------------------
-- Stored procedure that will delete an existing row from the table 'Jahrestabelle'
-- using the Primary Key. 
-- Gets: @iEintragnr int
-- Returns: @iErrorCode int
---------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[pr_Jahrestabelle_Delete]
	@iEintragnr int,
	@iErrorCode int OUTPUT
AS
SET NOCOUNT ON
-- DELETE an existing row from the table.
DELETE FROM [dbo].[Jahrestabelle]
WHERE
	[Eintragnr] = @iEintragnr
-- Get the Error Code for the statement just executed.
SELECT @iErrorCode=@@ERROR

GO

/****** Object:  StoredProcedure [dbo].[pr_Jahrestabelle_Update]    Script Date: 23.04.2013 08:14:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


---------------------------------------------------------------------------------
-- Stored procedure that will update an existing row in the table 'Jahrestabelle'
-- Gets: @iEintragnr int
-- Gets: @iJahr int
-- Gets: @iMonat int
-- Gets: @iPlantage int
-- Gets: @iFaktorNr int
-- Gets: @iArbeitstage int
-- Gets: @bAktiv bit
-- Gets: @daErstellt_am datetime
-- Gets: @daMutiert_am datetime
-- Gets: @iMutierer int
-- Returns: @iErrorCode int
---------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[pr_Jahrestabelle_Update]
	@iEintragnr int,
	@iJahr int,
	@iMonat int,
	@iPlantage int,
	@iFaktorNr int,
	@iArbeitstage int,
	@bAktiv bit,
	@daErstellt_am datetime,
	@daMutiert_am datetime,
	@iMutierer int,
	@iErrorCode int OUTPUT
AS
SET NOCOUNT ON
-- UPDATE an existing row in the table.
UPDATE [dbo].[Jahrestabelle]
SET 
	[Jahr] = @iJahr,
	[Monat] = @iMonat,
	[Plantage] = @iPlantage,
	[FaktorNr] = @iFaktorNr,
	[Arbeitstage] = @iArbeitstage,
	[Aktiv] = @bAktiv,
	[Erstellt_am] = @daErstellt_am,
	[Mutiert_am] = @daMutiert_am,
	[Mutierer] = @iMutierer
WHERE
	[Eintragnr] = @iEintragnr
-- Get the Error Code for the statement just executed.
SELECT @iErrorCode=@@ERROR

GO

/****** Object:  StoredProcedure [dbo].[pr_Jahrestabelle_Insert]    Script Date: 23.04.2013 08:14:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


---------------------------------------------------------------------------------
-- Stored procedure that will insert 1 row in the table 'Jahrestabelle'
-- Gets: @iEintragnr int
-- Gets: @iJahr int
-- Gets: @iMonat int
-- Gets: @iPlantage int
-- Gets: @iFaktorNr int
-- Gets: @iArbeitstage int
-- Gets: @bAktiv bit
-- Gets: @daErstellt_am datetime
-- Gets: @daMutiert_am datetime
-- Gets: @iMutierer int
-- Returns: @iErrorCode int
---------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[pr_Jahrestabelle_Insert]
	@iEintragnr int,
	@iJahr int,
	@iMonat int,
	@iPlantage int,
	@iFaktorNr int,
	@iArbeitstage int,
	@bAktiv bit,
	@daErstellt_am datetime,
	@daMutiert_am datetime,
	@iMutierer int,
	@iErrorCode int OUTPUT
AS
SET NOCOUNT ON
-- INSERT a new row in the table.
INSERT [dbo].[Jahrestabelle]
(
	[Eintragnr],
	[Jahr],
	[Monat],
	[Plantage],
	[FaktorNr],
	[Arbeitstage],
	[Aktiv],
	[Erstellt_am],
	[Mutiert_am],
	[Mutierer]
)
VALUES
(
	@iEintragnr,
	@iJahr,
	@iMonat,
	@iPlantage,
	@iFaktorNr,
	@iArbeitstage,
	@bAktiv,
	@daErstellt_am,
	@daMutiert_am,
	@iMutierer
)
-- Get the Error Code for the statement just executed.
SELECT @iErrorCode=@@ERROR

GO

/****** Object:  StoredProcedure [dbo].[pr_Faktoren_SelectAll]    Script Date: 23.04.2013 08:14:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


---------------------------------------------------------------------------------
-- Stored procedure that will select all rows from the table 'Faktoren'
-- Returns: @iErrorCode int
---------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[pr_Faktoren_SelectAll]
	@iErrorCode int OUTPUT
AS
SET NOCOUNT ON
-- SELECT all rows from the table.
SELECT
	[Eintragnr],
	[Bezeichnung],
	[Beschreibung],
	[Faktor_Prod_Tage],
	[Fuehrungsanteil],
	[Standard],
	[Aktiv],
	[Erstellt_am],
	[Mutiert_am],
	[Mutierer]
FROM [dbo].[Faktoren]
ORDER BY 
	[Eintragnr] ASC
-- Get the Error Code for the statement just executed.
SELECT @iErrorCode=@@ERROR

GO

/****** Object:  StoredProcedure [dbo].[pr_Faktoren_SelectOne]    Script Date: 23.04.2013 08:14:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


---------------------------------------------------------------------------------
-- Stored procedure that will select an existing row from the table 'Faktoren'
-- based on the Primary Key.
-- Gets: @iEintragnr int
-- Returns: @iErrorCode int
---------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[pr_Faktoren_SelectOne]
	@iEintragnr int,
	@iErrorCode int OUTPUT
AS
SET NOCOUNT ON
-- SELECT an existing row from the table.
SELECT
	[Eintragnr],
	[Bezeichnung],
	[Beschreibung],
	[Faktor_Prod_Tage],
	[Fuehrungsanteil],
	[Standard],
	[Aktiv],
	[Erstellt_am],
	[Mutiert_am],
	[Mutierer]
FROM [dbo].[Faktoren]
WHERE
	[Eintragnr] = @iEintragnr
-- Get the Error Code for the statement just executed.
SELECT @iErrorCode=@@ERROR

GO

/****** Object:  StoredProcedure [dbo].[pr_Faktoren_Delete]    Script Date: 23.04.2013 08:14:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


---------------------------------------------------------------------------------
-- Stored procedure that will delete an existing row from the table 'Faktoren'
-- using the Primary Key. 
-- Gets: @iEintragnr int
-- Returns: @iErrorCode int
---------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[pr_Faktoren_Delete]
	@iEintragnr int,
	@iErrorCode int OUTPUT
AS
SET NOCOUNT ON
-- DELETE an existing row from the table.
DELETE FROM [dbo].[Faktoren]
WHERE
	[Eintragnr] = @iEintragnr
-- Get the Error Code for the statement just executed.
SELECT @iErrorCode=@@ERROR

GO

/****** Object:  StoredProcedure [dbo].[pr_Faktoren_Update]    Script Date: 23.04.2013 08:14:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


---------------------------------------------------------------------------------
-- Stored procedure that will update an existing row in the table 'Faktoren'
-- Gets: @iEintragnr int
-- Gets: @sBezeichnung varchar(50)
-- Gets: @sBeschreibung varchar(255)
-- Gets: @fFaktor_Prod_Tage float(53)
-- Gets: @fFuehrungsanteil float(53)
-- Gets: @bStandard bit
-- Gets: @bAktiv bit
-- Gets: @daErstellt_am datetime
-- Gets: @daMutiert_am datetime
-- Gets: @iMutierer int
-- Returns: @iErrorCode int
---------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[pr_Faktoren_Update]
	@iEintragnr int,
	@sBezeichnung varchar(50),
	@sBeschreibung varchar(255),
	@fFaktor_Prod_Tage float(53),
	@fFuehrungsanteil float(53),
	@bStandard bit,
	@bAktiv bit,
	@daErstellt_am datetime,
	@daMutiert_am datetime,
	@iMutierer int,
	@iErrorCode int OUTPUT
AS
SET NOCOUNT ON
-- UPDATE an existing row in the table.
UPDATE [dbo].[Faktoren]
SET 
	[Bezeichnung] = @sBezeichnung,
	[Beschreibung] = @sBeschreibung,
	[Faktor_Prod_Tage] = @fFaktor_Prod_Tage,
	[Fuehrungsanteil] = @fFuehrungsanteil,
	[Standard] = @bStandard,
	[Aktiv] = @bAktiv,
	[Erstellt_am] = @daErstellt_am,
	[Mutiert_am] = @daMutiert_am,
	[Mutierer] = @iMutierer
WHERE
	[Eintragnr] = @iEintragnr
-- Get the Error Code for the statement just executed.
SELECT @iErrorCode=@@ERROR

GO

/****** Object:  StoredProcedure [dbo].[pr_Faktoren_Insert]    Script Date: 23.04.2013 08:14:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


---------------------------------------------------------------------------------
-- Stored procedure that will insert 1 row in the table 'Faktoren'
-- Gets: @iEintragnr int
-- Gets: @sBezeichnung varchar(50)
-- Gets: @sBeschreibung varchar(255)
-- Gets: @fFaktor_Prod_Tage float(53)
-- Gets: @fFuehrungsanteil float(53)
-- Gets: @bStandard bit
-- Gets: @bAktiv bit
-- Gets: @daErstellt_am datetime
-- Gets: @daMutiert_am datetime
-- Gets: @iMutierer int
-- Returns: @iErrorCode int
---------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[pr_Faktoren_Insert]
	@iEintragnr int,
	@sBezeichnung varchar(50),
	@sBeschreibung varchar(255),
	@fFaktor_Prod_Tage float(53),
	@fFuehrungsanteil float(53),
	@bStandard bit,
	@bAktiv bit,
	@daErstellt_am datetime,
	@daMutiert_am datetime,
	@iMutierer int,
	@iErrorCode int OUTPUT
AS
SET NOCOUNT ON
-- INSERT a new row in the table.
INSERT [dbo].[Faktoren]
(
	[Eintragnr],
	[Bezeichnung],
	[Beschreibung],
	[Faktor_Prod_Tage],
	[Fuehrungsanteil],
	[Standard],
	[Aktiv],
	[Erstellt_am],
	[Mutiert_am],
	[Mutierer]
)
VALUES
(
	@iEintragnr,
	@sBezeichnung,
	@sBeschreibung,
	@fFaktor_Prod_Tage,
	@fFuehrungsanteil,
	@bStandard,
	@bAktiv,
	@daErstellt_am,
	@daMutiert_am,
	@iMutierer
)
-- Get the Error Code for the statement just executed.
SELECT @iErrorCode=@@ERROR

GO

/****** Object:  StoredProcedure [dbo].[sp_rpt_fhrlst_aufwand]    Script Date: 23.04.2013 08:14:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_rpt_fhrlst_aufwand]
      @von DATETIME = NULL ,
      @bis DATETIME = NULL,
	  @oestruktur Integer = null
AS 
BEGIN
      IF @von IS NULL 
         SET @von = '01.03.2013'
      IF @bis IS NULL 
         SET @bis = '31.03.2013'
      IF @oestruktur IS NULL 
	     SET @oestruktur=25


      --DECLARE @per DATETIME = NULL ,
      --        @von DATETIME = NULL ,
      --        @bis DATETIME = NULL
      --SET @per = '01.03.2013'
      --SET @von = '01.03.2013'
      --SET @bis = '31.03.2013'
	  
	      
      SET NOCOUNT ON;

      DECLARE @tmpa TABLE
(
  [TGNummer] [varchar](50) NULL ,
  oenr INT ,
  sortierung INT ,
  START DATETIME ,
  Ende DATETIME ,
  [Paufwand] [float] NOT NULL ,
  [RFC_300] [float] NOT NULL ,
  [CTB] [float] NOT NULL ,
  [RTB] [float] NOT NULL ,
  RFC_200 FLOAT NOT NULL ,
  [Total] [float] NOT NULL ,
  [Soll] [float] NOT NULL ,
  [Prod] [float] NOT NULL ,
  [Anteil_CTB] [float] NOT NULL ,
  [Anteil_RTB] [float] NOT NULL ,
  Engagement_Effektiv FLOAT NOT NULL ,
  Soll_Zeit FLOAT NOT NULL
)    
 
      DECLARE @ansatz FLOAT
      SET @ansatz = 8.5

      --IF @per IS NULL 
      --   SET @per = GETDATE()

				
	  --***************************************************************************
	  -- Relevante Mitarbeiter am Per-Datum
	  --***************************************************************************
      DECLARE @mnr INT
      DECLARE @rc INT    
      DECLARE @mm INT
      DECLARE @yy INT
	      
      DECLARE xm CURSOR
      FOR
      SELECT    *
      FROM      dbo.get_monatstabelle(@von, @bis)
      ORDER BY  start_date
      OPEN xm
      FETCH NEXT FROM xm INTO @von, @bis

      WHILE @@FETCH_STATUS = 0 
            BEGIN
                  DECLARE xc CURSOR
                  FOR
                  SELECT    manr
                  FROM      ma
                  WHERE     aktiv = 1
                            AND gueltig_bis >= @bis
                  OPEN xc
                  FETCH NEXT FROM xc INTO @mnr
                  WHILE @@FETCH_STATUS = 0 
                        BEGIN
                              SELECT    @rc = COUNT(*)
                              FROM      dbo.MADetails
                              WHERE     manr = @mnr
                                        AND aktiv = 1
                                        AND Gueltig_ab <= @von
							
                              IF @rc > 0 
                                 BEGIN
                           
                                       INSERT   @tmpa
                                                SELECT TOP 1
                                                        dbo.MA.TGNummer, madetails.OENr, ma.sortierung, @von, @bis, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00,
                                                        0.00, 0.00, 0.00, dbo.get_anteil(engagement, MitarbeiterTypNr), dbo.get_arbeitstage(2013, 4)
                                                FROM    dbo.MA
                                                        LEFT OUTER JOIN dbo.MADetails ON dbo.MA.MANr = dbo.MADetails.MANr
                                                WHERE   dbo.MADetails.manr = @mnr
                                                        AND dbo.MADetails.Aktiv = 1
                                                        AND Gueltig_ab <= @von
                                                ORDER BY Gueltig_ab DESC

                                 END       
                              FETCH NEXT FROM xc INTO @mnr
                        END
                  CLOSE xc
                  DEALLOCATE xc      


                  DECLARE @anzahl FLOAT
                  DECLARE @ma VARCHAR(255)

	  -- Honris 200
                  DECLARE xc CURSOR
                  FOR
                  SELECT    SUM(ANZAHLINT), MITARBEITER_ID
                  FROM      dbo.Honoris_200
                  WHERE     ( DATUM >= @von
                              AND DATUM <= @bis
                            )
                  GROUP BY  MITARBEITER_ID
                  OPEN xc
                  FETCH NEXT FROM xc INTO @anzahl, @ma    
                  WHILE @@FETCH_STATUS = 0 
                        BEGIN
                              UPDATE    @tmpa
                              SET       rfc_200 = @anzahl
                              WHERE     tgnummer = @ma
                                        AND start = @von
                                        AND ende = @bis
                              FETCH NEXT FROM xc INTO @anzahl, @ma    
                        END
                  CLOSE xc
                  DEALLOCATE xc


	  -- Honris 300
                  DECLARE xc CURSOR
                  FOR
                  SELECT    SUM(ANZAHLINT), MITARBEITER_ID
                  FROM      dbo.Honoris_300
                  WHERE     ( DATUM >= @von
                              AND DATUM <= @bis
                            )
                  GROUP BY  MITARBEITER_ID
                  OPEN xc
                  FETCH NEXT FROM xc INTO @anzahl, @ma    
                  WHILE @@FETCH_STATUS = 0 
                        BEGIN
                              UPDATE    @tmpa
                              SET       rfc_300 = @anzahl
                              WHERE     tgnummer = @ma
                                        AND start = @von
                                        AND ende = @bis
                              FETCH NEXT FROM xc INTO @anzahl, @ma    
                        END
                  CLOSE xc
                  DEALLOCATE xc
	  
	  -- Honoris <> 300
                  DECLARE xc CURSOR
                  FOR
                  SELECT    SUM(ANZAHLINT), MITARBEITER_ID
                  FROM      dbo.Honoris_NE_300
                  WHERE     ( DATUM >= @von )
                            AND ( DATUM <= @bis )
                  GROUP BY  MITARBEITER_ID
                  OPEN xc
                  FETCH NEXT FROM xc INTO @anzahl, @ma    
                  WHILE @@FETCH_STATUS = 0 
                        BEGIN
                              UPDATE    @tmpa
                              SET       Paufwand = @anzahl
                              WHERE     tgnummer = @ma
                                        AND start = @von
                                        AND ende = @bis
                              FETCH NEXT FROM xc INTO @anzahl, @ma    
                        END
                  CLOSE xc
                  DEALLOCATE xc

      	   -- IN
                  DECLARE xc CURSOR
                  FOR
                  SELECT    SUM(CalculatedValue) AS Expr1, ForUserID
                  FROM      dbo.TXP_LVer_IN_AU
                  WHERE     ( ExecutedDateTime >= @von
                              AND ExecutedDateTime <= @bis
                            )
                            AND ( Ticket_ID LIKE N'IN_%' )
                  GROUP BY  ForUserID
                  OPEN xc
                  FETCH NEXT FROM xc INTO @anzahl, @ma    
                  WHILE @@FETCH_STATUS = 0 
                        BEGIN
                              UPDATE    @tmpa
                              SET       rtb = @anzahl
                              WHERE     tgnummer = @ma
                                        AND start = @von
                                        AND ende = @bis
                              FETCH NEXT FROM xc INTO @anzahl, @ma    
                        END
                  CLOSE xc
                  DEALLOCATE xc

	   -- AU
                  DECLARE xc CURSOR
                  FOR
                  SELECT    SUM(CalculatedValue) AS Expr1, ForUserID
                  FROM      dbo.TXP_LVer_IN_AU
                  WHERE     ( ExecutedDateTime >= @von
                              AND ExecutedDateTime <= @bis
                            )
                            AND ( Ticket_ID LIKE N'AU_%' )
                  GROUP BY  ForUserID
                  OPEN xc
                  FETCH NEXT FROM xc INTO @anzahl, @ma    
                  WHILE @@FETCH_STATUS = 0 
                        BEGIN
                              UPDATE    @tmpa
                              SET       ctb = @anzahl
                              WHERE     tgnummer = @ma
                                        AND start = @von
                                        AND ende = @bis
                              FETCH NEXT FROM xc INTO @anzahl, @ma    
                        END
                  CLOSE xc
                  DEALLOCATE xc

                  FETCH NEXT FROM xm INTO @von, @bis
            END

      CLOSE xm
      DEALLOCATE xm
      UPDATE    @tmpa
      SET       total = paufwand + rfc_300 + ctb + rtb + rfc_200
	


	-- Berechnen
      UPDATE    @tmpa
      SET       soll = ( soll_zeit * @ansatz ) / 100 * Engagement_Effektiv

      SELECT TOP ( 100 ) PERCENT
                dbo.OE.Bezeichnung AS OE, dbo.OE.Sortierung AS OESort, dbo.MA.Name, dbo.MA.Vorname, dbo.MA.Sortierung, a.TGNummer, a.START, a.ende,
                a.sortierung AS Expr1, a.Paufwand, a.RFC_300, a.CTB, a.RTB, a.RFC_200, a.Total, a.Soll, a.Prod, a.Anteil_CTB, a.Anteil_RTB,
                a.Engagement_Effektiv, a.Soll_Zeit
      iNTO      #tmpfhr
      FROM      @tmpa a
                INNER JOIN dbo.MA ON a.TGNummer = dbo.MA.TGNummer
                INNER JOIN dbo.OE ON a.oenr = dbo.OE.OeNr
      WHERE     dbo.OE.oenr IN (SELECT id FROM dbo.fnkt_get_kpi_hierarchie(@oestruktur))
      ORDER BY  OESort, dbo.MA.Sortierung, dbo.MA.Name

      SELECT    OE, OESort, Name, Vorname, Sortierung, TGNummer, Expr1 AS Sort, Engagement_Effektiv, SUM(Paufwand) AS PAufwand, SUM(RFC_300) AS RFC_300,
                SUM(CTB) AS CTB, SUM(RTB) AS RTB, SUM(RFC_200) AS RFC_200, SUM(Total) AS Total, SUM(Soll) AS Soll, CONVERT(NUMERIC(18, 2), 0.00) AS Prod,
                CONVERT(NUMERIC(18, 2), 0.00) AS Anteil_CTB, CONVERT(NUMERIC(18, 2), 0.00) AS Anteil_Rtb
      INTO      #tmpfhr1
      FROM      #tmpfhr
      GROUP BY  OE, OESort, Name, Vorname, Sortierung, TGNummer, Expr1, Engagement_Effektiv

      UPDATE    #tmpfhr1
      SET       prod = ROUND(total / soll * 100, 2)
      WHERE     total > 0
                AND soll > 0
      UPDATE    #tmpfhr1
      SET       Anteil_CTB = ROUND(( ( paufwand + rfc_300 + ctb ) / Total ) * 100, 2)
      WHERE     total > 0
                AND ( paufwand + rfc_300 + ctb ) > 0
      UPDATE    #tmpfhr1
      SET       Anteil_rtb = ROUND(( ( rfc_200 + rtb ) / total ) * 100, 2)
      WHERE     total > 0
                AND ( rfc_200 + rtb ) > 0

      SELECT    *
      FROM      #tmpfhr1
      ORDER BY  OESort, Sortierung, Name
	  
      DROP TABLE #tmpfhr
      DROP TABLE #tmpfhr1


END


GO

/****** Object:  StoredProcedure [dbo].[sp_get_struktur]    Script Date: 23.04.2013 08:14:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_get_struktur] @per DATETIME
AS 
BEGIN
      SET NOCOUNT ON;
      SELECT    'OE_' + LTRIM(RTRIM(STR(OeNr))) AS ID, 'OE_' + LTRIM(RTRIM(STR(ParentId))) AS ParentID, Bezeichnung, 0 AS ImageIndex,
                Beschreibung AS ErweiterteSuche, sortierung AS sort
      INTO      #tmp1
      FROM      dbo.OE WHERE aktiv=1
      ORDER BY  Sortierung


      INSERT    #tmp1 ( id, parentid, bezeichnung, imageindex, ErweiterteSuche, sort )
      VALUES    ( 'OE_0', NULL, 'Root', 0, '                    ',1 )
               
      INSERT    #tmp1
                SELECT  'MA_' + LTRIM(RTRIM(STR(dbo.MA.MANr))) AS ID, 'OE_-1'  AS ParentID,
                        dbo.MA.Name + ' ' + dbo.MA.Vorname AS Bezeichnung, 1 AS ImageIndex, dbo.MA.Name + ' ' + dbo.MA.Vorname AS ErweiterteSuche, 0 AS sort
                FROM    dbo.MA
                        LEFT OUTER JOIN dbo.MADetails ON dbo.MA.MANr = dbo.MADetails.MANr
                WHERE   ma.aktiv=1  AND dbo.ma.manr NOT IN ( SELECT manr
                                             FROM   dbo.MADetails
                                             WHERE  aktiv = 1 )

      DECLARE @mnr INT
      DECLARE @rc INT    
      DECLARE xc CURSOR
      FOR
      SELECT    manr
      FROM      ma
      WHERE     aktiv = 1 AND gueltig_bis >=@per
      OPEN xc
      FETCH NEXT FROM xc INTO @mnr
      WHILE @@FETCH_STATUS = 0 
            BEGIN
  
                  SELECT    @rc = COUNT(*)
                  FROM      dbo.MADetails
                  WHERE     manr = @mnr AND aktiv=1 
                            AND Gueltig_ab <= @per
							
                  IF @rc > 0 
                     BEGIN    
                           INSERT   #tmp1
                                    SELECT TOP 1
                                            'MA_' + LTRIM(RTRIM(STR(dbo.MA.MANr))) AS ID,
                                            'OE_' + ISNULL(LTRIM(RTRIM(STR(dbo.MADetails.OENr))), '-1') AS ParentID,
                                            +dbo.MA.Name + ' ' + dbo.MA.Vorname AS Bezeichnung, 1 AS ImageIndex,
                                            dbo.MA.Name + ' ' + dbo.MA.Vorname AS ErweiterteSuche, dbo.ma.sortierung AS sort
                                    FROM    dbo.MA
                                            LEFT OUTER JOIN dbo.MADetails ON dbo.MA.MANr = dbo.MADetails.MANr
                                    WHERE   dbo.MADetails.manr = @mnr AND dbo.MADetails.Aktiv=1
                                            AND Gueltig_ab <= @per 
                                    ORDER BY Gueltig_ab desc
         
                     END       
                           FETCH NEXT FROM xc INTO @mnr
            END
      CLOSE xc
      DEALLOCATE xc       
  

      
      SELECT    *
      FROM      #tmp1
      ORDER BY  sort, Bezeichnung

      DROP TABLE #tmp1
END


GO