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.
235 lines
20 KiB
235 lines
20 KiB
USE [Vertragsverwaltung_20160404]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[sp_rpt_AN_4] Script Date: 02.12.2016 09:08:53 ******/
|
|
DROP PROCEDURE [dbo].[sp_rpt_AN_4]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[sp_rpt_AN_4] Script Date: 02.12.2016 09:08: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_AN_4]
|
|
@von DATETIME = NULL ,
|
|
@bis DATETIME = NULL ,
|
|
@oestruktur INTEGER = NULL ,
|
|
@struktur_Ausschluss VARCHAR(255) = NULL ,
|
|
@Honoris_Ausschluss VARCHAR(255) = NULL
|
|
AS
|
|
BEGIN
|
|
IF @von IS NULL
|
|
SET @von = '2014-03-01 00:00:01'
|
|
IF @bis IS NULL
|
|
SET @bis = '2014-03-31 23:59:59'
|
|
IF @oestruktur IS NULL
|
|
SET @oestruktur = 42
|
|
IF @struktur_ausschluss IS NULL
|
|
SET @struktur_ausschluss = ''
|
|
IF @honoris_ausschluss IS NULL
|
|
SET @honoris_ausschluss = ''
|
|
|
|
declare @dd1 varchar(2)
|
|
declare @mm1 varchar(2)
|
|
declare @yy1 varchar(4)
|
|
declare @datum varchar(255)
|
|
set @dd1=DAY(@von)
|
|
set @mm1=MONTH(@von)
|
|
set @yy1=YEAR(@von)
|
|
if LEN(@dd1)<2 set @dd1='0'+@dd1
|
|
if LEN(@mm1)<2 set @mm1='0'+@mm1
|
|
set @datum=@yy1+'-'+@mm1+'-'+@dd1 +' 00:00:00'
|
|
set @von=convert(datetime,@datum,20)
|
|
set @dd1=DAY(@bis)
|
|
set @mm1=MONTH(@bis)
|
|
set @yy1=YEAR(@bis)
|
|
if LEN(@dd1)<2 set @dd1='0'+@dd1
|
|
if LEN(@mm1)<2 set @mm1='0'+@mm1
|
|
set @datum=@yy1+'-'+@mm1+'-'+@dd1 +' 22:48:45'
|
|
set @bis=convert(datetime,@datum,20)
|
|
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE @tmpa TABLE
|
|
(
|
|
[TGNummer] [varchar](50) NULL ,
|
|
oenr INT ,
|
|
sortierung INT ,
|
|
START DATETIME ,
|
|
Ende DATETIME ,
|
|
Vorhaben VARCHAR(255),
|
|
Std [float] NOT NULL ,
|
|
Engagement_Effektiv FLOAT NOT NULL ,
|
|
Soll_Zeit FLOAT NOT NULL,
|
|
JJMM VARCHAR(255)
|
|
)
|
|
|
|
DECLARE @ansatz FLOAT
|
|
SET @ansatz = 8.0
|
|
|
|
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,
|
|
dbo.get_anteil(engagement, MitarbeiterTypNr), dbo.get_plantage(YEAR(@von),MONTH(@von)),''
|
|
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)
|
|
|
|
|
|
FETCH NEXT FROM xm INTO @von, @bis
|
|
END
|
|
|
|
CLOSE xm
|
|
DEALLOCATE xm
|
|
|
|
UPDATE @tmpa SET jjmm = LTRIM(STR(YEAR(start)))+''+LTRIM(STR(MONTH(start)))
|
|
|
|
|
|
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.vorhaben, a.Std, a.Engagement_Effektiv, a.Soll_Zeit,a.JJMM
|
|
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) )
|
|
AND dbo.oe.oenr NOT IN ( SELECT item
|
|
FROM dbo.fnkt_split(@struktur_Ausschluss, ',', 1) )
|
|
ORDER BY OESort, dbo.MA.Sortierung, dbo.MA.Name
|
|
|
|
--select * from #tmpfhr where Name='stalder'
|
|
|
|
SELECT TGNummer, oe, sortierung, START, Ende, Vorhaben, Std, Engagement_Effektiv, Soll_Zeit, LTRIM(STR(YEAR(start)))+''+LTRIM(STR(MONTH(start))) AS JJMM,
|
|
dbo.AN_Summe_Ticket_User.Ticket_ID, dbo.AN_Summe_Ticket_User.Expr1 AS Summe_Std, ROUND(dbo.AN_Summe_Ticket_User.Expr1 / 8, 2) AS Summe_Tage,
|
|
dbo.AN_Summe_Ticket_User.Kurzbeschreibung
|
|
INTO #tmpb
|
|
FROM #tmpfhr INNER JOIN
|
|
dbo.AN_Summe_Ticket_User ON TGNummer = dbo.AN_Summe_Ticket_User.ForUserID AND LTRIM(STR(YEAR(start)))+''+LTRIM(STR(MONTH(start))) = dbo.AN_Summe_Ticket_User.jjmm
|
|
|
|
|
|
SELECT DISTINCT
|
|
#tmpb.TGNummer, #tmpb.oe, #tmpb.sortierung, #tmpb.START,
|
|
#tmpb.Ende, #tmpb.Vorhaben, #tmpb.Std, #tmpb.Engagement_Effektiv,
|
|
#tmpb.Soll_Zeit, #tmpb.JJMM, #tmpb.Ticket_ID, #tmpb.Summe_Std,
|
|
#tmpb.Summe_Tage, #tmpb.Kurzbeschreibung, dbo.MA.TGNummer AS Expr1, dbo.MA.Name, dbo.MA.Vorname
|
|
|
|
INTO #tmpx
|
|
FROM #tmpb INNER JOIN
|
|
dbo.shu_view_an_excelexport_lst ON #tmpb.Ticket_ID = dbo.shu_view_an_excelexport_lst.FullId INNER JOIN
|
|
dbo.MA ON #tmpb.TGNummer = dbo.MA.TGNummer
|
|
|
|
|
|
|
|
|
|
-- Honoris
|
|
-- SELECT TGNummer, oenr, sortierung, START, Ende,
|
|
-- Vorhaben, Std, Engagement_Effektiv, Soll_Zeit, JJMM AS JJMM, dbo.honoris_projekt.PROJEKT_NR, dbo.honoris_journal.ANZAHLINT AS Summe_Std,
|
|
-- dbo.honoris_projekt.KURZNAME
|
|
-- into #tmpc
|
|
-- FROM dbo.honoris_journal INNER JOIN
|
|
-- dbo.honoris_projekt ON dbo.honoris_journal.PROJEKT_NR = dbo.honoris_projekt.PROJEKT_NR INNER JOIN
|
|
-- dbo.Honoris_Mitarbeiter ON dbo.honoris_journal.MITARBEITER_NR = dbo.Honoris_Mitarbeiter.MITARBEITER_NR INNER JOIN
|
|
-- @tmpa ON dbo.Honoris_Mitarbeiter.MITARBEITER_ID = TGNummer AND LTRIM(STR(YEAR(dbo.honoris_journal.DATUM))) + LTRIM(STR(MONTH(dbo.honoris_journal.DATUM))) = JJMM
|
|
|
|
-- SELECT TGNummer, oenr, sortierung, START, Ende, Vorhaben, Std, Engagement_Effektiv, Soll_Zeit, JJMM, PROJEKT_NR, SUM(Summe_Std) AS Summe_Std, ROUND(SUM(ROUND(Summe_Std / 8, 2)),2) AS Summe_Tage,
|
|
-- KURZNAME
|
|
-- INTO #tmpd
|
|
-- FROM #tmpc
|
|
-- GROUP BY TGNummer, oenr, sortierung, START, Ende, Vorhaben, Std, Engagement_Effektiv, Soll_Zeit, JJMM, PROJEKT_NR, KURZNAME
|
|
|
|
|
|
|
|
-- 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.vorhaben, a.Std, a.Engagement_Effektiv, a.Soll_Zeit,a.JJMM, a.PROJEKT_NR AS Ticket_ID, a.Summe_Std,ROUND(a.Summe_Tage,2) AS Summe_Tage,a.KURZNAME AS kurzbeschreibung
|
|
-- INTO #tmpe
|
|
-- FROM #tmpd 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) )
|
|
-- AND dbo.oe.oenr NOT IN ( SELECT item
|
|
-- FROM dbo.fnkt_split(@struktur_Ausschluss, ',', 1) )
|
|
-- ORDER BY OESort, dbo.MA.Sortierung, dbo.MA.Name
|
|
|
|
--INSERT #tmpx
|
|
-- SELECT DISTINCT
|
|
-- #tmpe.TGNummer, #tmpe.oe, #tmpe.sortierung, #tmpe.START,
|
|
-- #tmpe.Ende, #tmpe.Vorhaben, #tmpe.Std, #tmpe.Engagement_Effektiv,
|
|
-- #tmpe.Soll_Zeit, #tmpe.JJMM, #tmpe.Ticket_ID, #tmpe.Summe_Std,
|
|
-- ROUND(#tmpe.Summe_Tage,2) AS Summe_Tage, #tmpe.Kurzbeschreibung, dbo.MA.TGNummer AS Expr1, dbo.MA.Name, dbo.MA.Vorname
|
|
|
|
-- FROM #tmpe INNER JOIN
|
|
|
|
-- dbo.MA ON #tmpe.TGNummer = dbo.MA.TGNummer
|
|
|
|
|
|
-- DROP TABLE dbo._an_04
|
|
SELECT *
|
|
--INTO _an_04
|
|
FROM #tmpx
|
|
RETURN
|
|
|
|
drop table #tmpb
|
|
drop table #tmpx
|
|
drop table #tmpfhr
|
|
|
|
END
|
|
|
|
|
|
GO
|