USE [Vertragsverwaltung_20160404]
GO
/****** Object:  StoredProcedure [dbo].[sp_rpt_fhrlst_Anzahl_neu_checkliste]    Script Date: 02.12.2016 09:08:53 ******/
DROP PROCEDURE [dbo].[sp_rpt_fhrlst_Anzahl_neu_checkliste]
GO
/****** Object:  StoredProcedure [dbo].[sp_rpt_fhrlst_Anzahl_neu_checkliste]    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_fhrlst_Anzahl_neu_checkliste] @von DATETIME = NULL
       ,@bis DATETIME = NULL
       ,@OEStruktur INT = NULL
       ,@struktur_Ausschluss VARCHAR(255) = NULL
AS
BEGIN
       IF @von IS NULL
             SET @von = convert(DATETIME, '01.01.2014', 104)
       ELSE
             SET @von = CONVERT(DATETIME, @von, 104)

       IF @bis IS NULL
             SET @bis = convert(DATETIME, '31.05.2014', 104)
       ELSE
             SET @bis = CONVERT(DATETIME, @bis, 104)

       IF @oestruktur IS NULL
             SET @oestruktur = 24

       IF @struktur_ausschluss IS NULL
             SET @struktur_ausschluss = '31'

       DECLARE @save_von DATETIME
       DECLARE @save_bis DATETIME
       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 + ' 23:59:59'
       SET @bis = convert(DATETIME, @datum, 20)
       SET @save_von = @von
       SET @save_bis = @bis
       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 @tickettable TABLE (
             tgnummer VARCHAR(255)
             ,NAME VARCHAR(255)
             ,ticketid VARCHAR(255)
             ,statusdatum VARCHAR(255)
             ,STATUS VARCHAR(255)
             )
       DECLARE @ansatz FLOAT

       SET @ansatz = 8.0

       --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 @von AS von
             ,@bis AS bis

       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
                    OR manr IN (
                           SELECT manr
                           FROM dbo.MADetails
                           WHERE Gueltig_ab >= @von
                           )

             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
                                  AND madetails.oenr IN (
                                        SELECT id
                                        FROM dbo.fnkt_get_kpi_hierarchie(@oestruktur)
                                        )
                           ORDER BY Gueltig_ab DESC
                    END

                    FETCH NEXT
                    FROM xc
                    INTO @mnr
             END

             CLOSE xc

             DEALLOCATE xc

         DELETE FROM @tmpa WHERE oenr IN ( SELECT item  FROM   dbo.fnkt_split(@struktur_Ausschluss, ',', 1) )
         DELETE FROM @tmpa WHERE oenr NOT IN  ( SELECT id FROM   dbo.fnkt_get_kpi_hierarchie(@oestruktur) )
      -- select * from @tmpa
      -- return
             -- Geschlossene AU
             DECLARE @anzahl INT
             DECLARE @aufwand FLOAT
             DECLARE @ma VARCHAR(255)
             DECLARE @ticketid VARCHAR(255)
             DECLARE @tgnummer VARCHAR(255)
             DECLARE @statusdatum VARCHAR(255)

             INSERT @tickettable (
                    tgnummer
                    ,NAME
                    ,ticketid
                    ,statusdatum
                    ,STATUS
                    )
             SELECT tgnummer
                    ,''
                    ,ticketid
                    ,status_changedate
                    ,'Geschlossen'
             FROM dbo.txp_au_geschlossen
             WHERE Status_Changedate BETWEEN @von
                           AND @bis
                    AND tgnummer IN (
                           SELECT tgnummer
                           FROM @tmpa
                           )

             INSERT @tickettable (
                    tgnummer
                    ,NAME
                    ,ticketid
                    ,statusdatum
                    ,STATUS
                    )
             SELECT tgnummer
                    ,''
                    ,ticketid
                    ,status_changedate
                    ,'Offen'
             FROM dbo.txp_au_offen
             WHERE Status_Changedate <= @bis
                    AND tgnummer IN (
                           SELECT tgnummer
                           FROM @tmpa
                           )

             INSERT @tickettable (
                    tgnummer
                    ,NAME
                    ,ticketid
                    ,statusdatum
                    ,STATUS
                    )
             SELECT tgnummer
                    ,''
                    ,ticketid
                    ,status_changedate
                    ,'Offen'
             FROM dbo.txp_in_offen
             WHERE Status_Changedate <=@bis
                    AND tgnummer IN (
                           SELECT tgnummer
                           FROM @tmpa
                           )

             INSERT @tickettable (
                    tgnummer
                    ,NAME
                    ,ticketid
                    ,statusdatum
                    ,STATUS
                    )
             SELECT tgnummer
                    ,''
                    ,ticketid
                    ,status_changedate
                    ,'Geschlossen'
             FROM dbo.txp_in_geschlossen
             WHERE Status_Changedate BETWEEN @von
                           AND @bis
                    AND tgnummer IN (
                           SELECT tgnummer
                           FROM @tmpa
                           )

             FETCH NEXT
             FROM xm
             INTO @von
                    ,@bis
       END

       CLOSE xm

       DEALLOCATE xm

       UPDATE @tickettable
       SET a.[NAME] = ma.[NAME]
       FROM @tickettable a
       INNER JOIN ma ON ma.TGNummer = a.tgnummer

       SELECT *
       FROM @tickettable
       ORDER BY tgnummer
             ,ticketid
END


GO