shu
/
ITSM
0
0
Fork 0
Code Issues 1 Pull Requests Projects Releases Wiki Activity
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
Branches Tags
${ item.name }
Create tag ${ searchTerm }
Create branch ${ searchTerm }
from 'master'
${ noResults }
ITSM/Implementierung/201304/Neue_SPs.sql

2121 lines
59 KiB
Raw Permalink Blame History Escape

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<67>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
Reference in new issue View Git Blame Copy Permalink