USE [Vertragsverwaltung]
GO

/****** Object:  UserDefinedFunction [dbo].[lc_rpt6_sik]    Script Date: 02.05.2015 09:43:18 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
Create FUNCTION [dbo].[lc_rpt6_sik]
(	
	@produkte VARCHAR(255),
	@inTagen VARCHAR(255)
)
RETURNS @tmp1 TABLE (LC_Produktnr INT, Produkt VARCHAR(255), LC_PhaseNr INT, datum DATETIME)
AS
begin
DECLARE @von DATETIME
    DECLARE @it integer  
    DECLARE @bis datetime  
	SET @it=@intagen
	IF @intagen <> 0 BEGIN
       SET @von=DATEADD(DAY,@it,GETDATE())
	   SET @von=DATEADD(dd, DATEDIFF(dd, 0, @von), 0)
    END     

	IF @produkte='' BEGIN
       insert into @tmp1 
       SELECT distinct dbo.LC_Produkt.LC_ProduktNr, dbo.LC_Produkt.Bezeichnung AS Produkt, dbo.LC_Phase.LC_PhaseNr, dbo.LC_Produkt_Phase.Datum
		  FROM  dbo.LC_Produkt INNER JOIN
                dbo.LC_Produkt_Phase ON dbo.LC_Produkt.LC_ProduktNr = dbo.LC_Produkt_Phase.LC_ProduktNr INNER JOIN
                dbo.LC_Phase ON dbo.LC_Produkt_Phase.LC_PhaseNr = dbo.LC_Phase.LC_PhaseNr
          WHERE (dbo.LC_Phase.Aktiv = 1) AND (dbo.LC_Produkt_Phase.Aktiv = 1) AND (dbo.LC_Produkt.Aktiv = 1) AND (dbo.LC_Produkt.StatusNr = 1)
		  		  AND dbo.LC_Produkt_Phase.Datum >=@von
          ORDER BY Produkt

    end
	IF @produkte<>'' BEGIN
       insert into @tmp1 
       SELECT distinct dbo.LC_Produkt.LC_ProduktNr, dbo.LC_Produkt.Bezeichnung AS Produkt, dbo.LC_Phase.LC_PhaseNr, dbo.LC_Produkt_Phase.Datum
		  FROM  dbo.LC_Produkt INNER JOIN
                dbo.LC_Produkt_Phase ON dbo.LC_Produkt.LC_ProduktNr = dbo.LC_Produkt_Phase.LC_ProduktNr INNER JOIN
                dbo.LC_Phase ON dbo.LC_Produkt_Phase.LC_PhaseNr = dbo.LC_Phase.LC_PhaseNr
          WHERE (dbo.LC_Phase.Aktiv = 1) AND (dbo.LC_Produkt_Phase.Aktiv = 1) AND (dbo.LC_Produkt.Aktiv = 1) AND (dbo.LC_Produkt.StatusNr = 1)
          and dbo.lc_phase.lc_phasenr IN (SELECT item FROM dbo.fnkt_split(@produkte,',',1))
		  		  AND dbo.LC_Produkt_Phase.Datum >=@bis
          ORDER BY Produkt
   end

   

RETURN
end


GO

/****** Object:  UserDefinedFunction [dbo].[lc_rpt5_sik]    Script Date: 02.05.2015 09:43:18 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
Create FUNCTION [dbo].[lc_rpt5_sik]
(	
	@produkte VARCHAR(255),
	@inTagen varchar(255)

)
RETURNS @tmp1 TABLE (LC_Phasenr INT, Phase VARCHAR(255))
AS
begin
	DECLARE @von DATETIME
    DECLARE @it integer  
    DECLARE @bis datetime  
	SET @it=@intagen
	IF @intagen <> 0 BEGIN
       SET @von=DATEADD(DAY,@it,GETDATE())
	   SET @von=DATEADD(dd, DATEDIFF(dd, 0, @von), 0)
    END     
	
	IF @produkte='' BEGIN
       insert into @tmp1 SELECT DISTINCT  dbo.LC_Phase.LC_PhaseNr, dbo.LC_Phase.Bezeichnung AS Phase
		  FROM dbo.LC_Produkt INNER JOIN dbo.LC_Produkt_Phase ON dbo.LC_Produkt.LC_ProduktNr = dbo.LC_Produkt_Phase.LC_ProduktNr INNER JOIN
               dbo.LC_Phase ON dbo.LC_Produkt_Phase.LC_PhaseNr = dbo.LC_Phase.LC_PhaseNr
          WHERE  (dbo.LC_Phase.Aktiv = 1) AND (dbo.LC_Produkt_Phase.Aktiv = 1) AND (dbo.LC_Produkt.Aktiv = 1) AND (dbo.LC_Produkt.StatusNr = 1)    
		  AND dbo.LC_Produkt_Phase.Datum >=@von
  
    END
	IF @produkte<>'' BEGIN
       insert into @tmp1 SELECT DISTINCT  dbo.LC_Phase.LC_PhaseNr, dbo.LC_Phase.Bezeichnung AS Phase
		  FROM dbo.LC_Produkt INNER JOIN dbo.LC_Produkt_Phase ON dbo.LC_Produkt.LC_ProduktNr = dbo.LC_Produkt_Phase.LC_ProduktNr INNER JOIN
               dbo.LC_Phase ON dbo.LC_Produkt_Phase.LC_PhaseNr = dbo.LC_Phase.LC_PhaseNr
          WHERE  (dbo.LC_Phase.Aktiv = 1) AND (dbo.LC_Produkt_Phase.Aktiv = 1) AND (dbo.LC_Produkt.Aktiv = 1) AND (dbo.LC_Produkt.StatusNr = 1)    
		  and dbo.lc_phase.lc_phasenr IN (SELECT item FROM dbo.fnkt_split(@produkte,',',1))
		  AND dbo.LC_Produkt_Phase.Datum >=@von
	 end 
    
    

RETURN
end


GO

/****** Object:  UserDefinedFunction [dbo].[lc_rpt6]    Script Date: 02.05.2015 09:43:18 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE FUNCTION [dbo].[lc_rpt6]
(	
	@produkte VARCHAR(255),
	@inTagen VARCHAR(255)
)
RETURNS @tmp1 TABLE (LC_Produktnr INT, Produkt VARCHAR(255), LC_PhaseNr INT, datum DATETIME)
AS
begin
DECLARE @von DATETIME
    DECLARE @it integer  
    DECLARE @bis datetime  
	SET @it=@intagen
	IF @intagen <> 0 BEGIN
       SET @von=DATEADD(DAY,@it,GETDATE())
	   SET @von=DATEADD(dd, DATEDIFF(dd, 0, @von), 0)
    END     

    DECLARE @tmp2 TABLE (lc_Produktnr INT, Produkt VARCHAR(255), lc_phasenr INT, Phase VARCHAR(255), datum DATETIME,  skey INT IDENTITY)
	DECLARE @tmp3 TABLE (lc_Produktnr INT, Produkt VARCHAR(255), lc_phasenr int, phase VARCHAR(255), datumvon DATETIME, datumbis DATETIME, diff INT, skey INT )

	INSERT @tmp2 
	SELECT        TOP (100) PERCENT dbo.LC_Produkt_Phase.LC_ProduktNr, dbo.lc_produkt.bezeichnung AS Produkt, dbo.LC_Produkt_Phase.LC_PhaseNr, dbo.LC_Phase.Bezeichnung AS Phase, dbo.LC_Produkt_Phase.Datum
	FROM            dbo.LC_Produkt_Phase INNER JOIN
                         dbo.LC_Produkt ON dbo.LC_Produkt_Phase.LC_ProduktNr = dbo.LC_Produkt.LC_ProduktNr INNER JOIN
                         dbo.LC_Phase ON dbo.LC_Produkt_Phase.LC_PhaseNr = dbo.LC_Phase.LC_PhaseNr
	WHERE        (dbo.LC_Produkt_Phase.Aktiv = 1) AND (dbo.LC_Produkt.Aktiv = 1) AND (dbo.LC_Produkt.StatusNr = 1)
	ORDER BY dbo.LC_Produkt_Phase.LC_ProduktNr, dbo.LC_Produkt_Phase.Datum DESC
	
	INSERT @tmp3 
	SELECT t1.lc_ProduktNr, t1.produkt, t1.lc_phasenr, t1.Phase , t1.datum AS DatumVon, x.datum AS DatumBis, ISNULL(DATEDIFF(DAY,t1.datum,x.datum),0) AS Diff, t1.skey
	FROM  @tmp2 t1
		OUTER APPLY (
		SELECT TOP 1 datum FROM @tmp2 t2 WHERE t2.skey < t1.skey ORDER BY t2.skey desc) x
		ORDER BY lc_ProduktNr, t1.datum asc

		UPDATE @tmp3 SET diff=0 WHERE diff < 0

		DELETE FROM @tmp3 WHERE diff=0

	IF @produkte='' BEGIN
	   insert into @tmp1 
	   SELECT DISTINCT  LC_Produktnr, Produkt, lc_phasenr, datumvon
	   FROM @tmp3
          WHERE @von BETWEEN datumvon AND datumbis
		  ORDER BY Produkt

    end
	IF @produkte<>'' BEGIN
       insert into @tmp1 
	   SELECT DISTINCT  LC_Produktnr, Produkt, lc_phasenr, datumvon
	   FROM @tmp3
          WHERE @von BETWEEN datumvon AND datumbis

		  AND lc_produktnr IN (SELECT item FROM dbo.fnkt_split(@produkte,',',1))
		  ORDER BY Produkt
          
   end

   

RETURN
end


GO

/****** Object:  UserDefinedFunction [dbo].[lc_rpt5]    Script Date: 02.05.2015 09:43:18 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE FUNCTION [dbo].[lc_rpt5]
(	
	@produkte VARCHAR(255),
	@inTagen varchar(255)

)
RETURNS @tmp1 TABLE (LC_Phasenr INT, Phase VARCHAR(255))
AS
begin
	DECLARE @von DATETIME
    DECLARE @it integer  
    DECLARE @bis datetime  
	SET @it=@intagen
	IF @intagen <> 0 BEGIN
       SET @von=DATEADD(DAY,@it,GETDATE())
	   SET @von=DATEADD(dd, DATEDIFF(dd, 0, @von), 0)
    END     
	
    DECLARE @tmp2 TABLE (lc_Produktnr INT, Produkt VARCHAR(255), lc_phasenr INT, Phase VARCHAR(255), datum DATETIME,  skey INT IDENTITY)
	DECLARE @tmp3 TABLE (lc_Produktnr INT, Produkt VARCHAR(255), lc_phasenr int, phase VARCHAR(255), datumvon DATETIME, datumbis DATETIME, diff INT, skey INT )

	INSERT @tmp2 
	SELECT        TOP (100) PERCENT dbo.LC_Produkt_Phase.LC_ProduktNr, dbo.lc_produkt.bezeichnung AS Produkt, dbo.LC_Produkt_Phase.LC_PhaseNr, dbo.LC_Phase.Bezeichnung AS Phase, dbo.LC_Produkt_Phase.Datum
	FROM            dbo.LC_Produkt_Phase INNER JOIN
                         dbo.LC_Produkt ON dbo.LC_Produkt_Phase.LC_ProduktNr = dbo.LC_Produkt.LC_ProduktNr INNER JOIN
                         dbo.LC_Phase ON dbo.LC_Produkt_Phase.LC_PhaseNr = dbo.LC_Phase.LC_PhaseNr
	WHERE        (dbo.LC_Produkt_Phase.Aktiv = 1) AND (dbo.LC_Produkt.Aktiv = 1) AND (dbo.LC_Produkt.StatusNr = 1)
	ORDER BY dbo.LC_Produkt_Phase.LC_ProduktNr, dbo.LC_Produkt_Phase.Datum DESC
	
	INSERT @tmp3 
	SELECT t1.lc_ProduktNr, t1.produkt, t1.lc_phasenr, t1.Phase , t1.datum AS DatumVon, x.datum AS DatumBis, ISNULL(DATEDIFF(DAY,t1.datum,x.datum),0) AS Diff, t1.skey
	FROM  @tmp2 t1
		OUTER APPLY (
		SELECT TOP 1 datum FROM @tmp2 t2 WHERE t2.skey < t1.skey ORDER BY t2.skey desc) x
		ORDER BY lc_ProduktNr, t1.datum asc

		UPDATE @tmp3 SET diff=0 WHERE diff < 0
		DELETE FROM @tmp3 WHERE diff=0

	IF @produkte='' BEGIN
       insert into @tmp1 
	   SELECT DISTINCT  LC_PhaseNr, Phase AS Phase FROM @tmp3 
          WHERE @von BETWEEN datumvon AND datumbis
    END
	IF @produkte<>'' BEGIN
       insert into @tmp1 
	   SELECT DISTINCT  LC_PhaseNr, Phase AS Phase FROM @tmp3 
		  WHERE @von BETWEEN datumvon AND datumbis AND lc_produktnr IN (SELECT item FROM dbo.fnkt_split(@produkte,',',1))
	 end 
    
		

RETURN
end


GO

/****** Object:  UserDefinedFunction [dbo].[lc_rpt4]    Script Date: 02.05.2015 09:43:18 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE FUNCTION [dbo].[lc_rpt4]
(	
	@produkte VARCHAR(255),
	@von varchar(255),
	@bis varchar(255),
	@produkttypnr int
)
RETURNS @tmp1 TABLE (LC_Produktnr INT, Produkt VARCHAR(255), LC_PhaseNr INT, datum DATETIME)
AS
begin

	IF @von=''  SET @von='01.01.1900 '
	IF @bis=''  SET @bis='31.12.4712'

	IF @produkte='' BEGIN
       insert into @tmp1 
       SELECT distinct dbo.LC_Produkt.LC_ProduktNr, dbo.LC_Produkt.Bezeichnung AS Produkt, dbo.LC_Phase.LC_PhaseNr, dbo.LC_Produkt_Phase.Datum
		  FROM  dbo.LC_Produkt INNER JOIN
                dbo.LC_Produkt_Phase ON dbo.LC_Produkt.LC_ProduktNr = dbo.LC_Produkt_Phase.LC_ProduktNr INNER JOIN
                dbo.LC_Phase ON dbo.LC_Produkt_Phase.LC_PhaseNr = dbo.LC_Phase.LC_PhaseNr
          WHERE (dbo.LC_Phase.Aktiv = 1) AND (dbo.LC_Produkt_Phase.Aktiv = 1) AND (dbo.LC_Produkt.Aktiv = 1) AND (dbo.LC_Produkt.StatusNr = 1)
		  		  AND dbo.LC_Produkt_Phase.Datum BETWEEN @von AND @bis
				  AND (@produkttypnr=-1 OR @produkttypnr = lc_produkt.ProdukttypNr)
				  
          ORDER BY dbo.LC_Produkt_Phase.Datum,Produkt

    end
	IF @produkte<>'' BEGIN
       insert into @tmp1 
       SELECT distinct dbo.LC_Produkt.LC_ProduktNr, dbo.LC_Produkt.Bezeichnung AS Produkt, dbo.LC_Phase.LC_PhaseNr, dbo.LC_Produkt_Phase.Datum
		  FROM  dbo.LC_Produkt INNER JOIN
                dbo.LC_Produkt_Phase ON dbo.LC_Produkt.LC_ProduktNr = dbo.LC_Produkt_Phase.LC_ProduktNr INNER JOIN
                dbo.LC_Phase ON dbo.LC_Produkt_Phase.LC_PhaseNr = dbo.LC_Phase.LC_PhaseNr
          WHERE (dbo.LC_Phase.Aktiv = 1) AND (dbo.LC_Produkt_Phase.Aktiv = 1) AND (dbo.LC_Produkt.Aktiv = 1) AND (dbo.LC_Produkt.StatusNr = 1)
          and dbo.lc_phase.lc_phasenr IN (SELECT item FROM dbo.fnkt_split(@produkte,',',1))
		  		  AND dbo.LC_Produkt_Phase.Datum BETWEEN @von AND @bis
				  AND (@produkttypnr=-1 OR @produkttypnr = lc_produkt.ProdukttypNr)
				  
          ORDER BY dbo.LC_Produkt_Phase.Datum,Produkt
   end

   

RETURN
end


GO

/****** Object:  UserDefinedFunction [dbo].[lc_rpt3]    Script Date: 02.05.2015 09:43:18 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE FUNCTION [dbo].[lc_rpt3]
(	
	@produkte VARCHAR(255),
	@von varchar(255),
	@bis varchar(255),
	@produkttypnr int
	

)
RETURNS @tmp1 TABLE (LC_Phasenr INT, Phase VARCHAR(255))
AS
begin

        declare @tmp2 TABLE (LC_Phasenr INT, Phase VARCHAR(255), datum datetime)
    declare @tmp3 TABLE (LC_Phasenr INT, Phase VARCHAR(255))


	IF @von=''  SET @von='01.01.1900 '
	IF @bis=''  SET @bis='31.12.4712'
	
	IF @produkte='' BEGIN
       insert into @tmp2 SELECT DISTINCT  dbo.LC_Phase.LC_PhaseNr, dbo.LC_Phase.Bezeichnung AS Phase, lc_produkt_phase.datum
		  FROM dbo.LC_Produkt INNER JOIN dbo.LC_Produkt_Phase ON dbo.LC_Produkt.LC_ProduktNr = dbo.LC_Produkt_Phase.LC_ProduktNr INNER JOIN
               dbo.LC_Phase ON dbo.LC_Produkt_Phase.LC_PhaseNr = dbo.LC_Phase.LC_PhaseNr
          WHERE  (dbo.LC_Phase.Aktiv = 1) AND (dbo.LC_Produkt_Phase.Aktiv = 1) AND (dbo.LC_Produkt.Aktiv = 1) AND (dbo.LC_Produkt.StatusNr = 1)    
		  AND dbo.LC_Produkt_Phase.Datum BETWEEN @von AND @bis
		  AND (@produkttypnr=-1 OR @produkttypnr = lc_produkt.ProdukttypNr)
		  ORDER BY lc_produkt_phase.datum	

    END
	IF @produkte<>'' BEGIN
       insert into @tmp2 SELECT DISTINCT  dbo.LC_Phase.LC_PhaseNr, dbo.LC_Phase.Bezeichnung AS Phase, lc_produkt_phase.datum
		  FROM dbo.LC_Produkt INNER JOIN dbo.LC_Produkt_Phase ON dbo.LC_Produkt.LC_ProduktNr = dbo.LC_Produkt_Phase.LC_ProduktNr INNER JOIN
               dbo.LC_Phase ON dbo.LC_Produkt_Phase.LC_PhaseNr = dbo.LC_Phase.LC_PhaseNr
          WHERE  (dbo.LC_Phase.Aktiv = 1) AND (dbo.LC_Produkt_Phase.Aktiv = 1) AND (dbo.LC_Produkt.Aktiv = 1) AND (dbo.LC_Produkt.StatusNr = 1)    
		  and dbo.lc_phase.lc_phasenr IN (SELECT item FROM dbo.fnkt_split(@produkte,',',1))
		  AND dbo.LC_Produkt_Phase.Datum BETWEEN @von AND @bis
		  AND (@produkttypnr=-1 OR @produkttypnr = lc_produkt.ProdukttypNr)
		  ORDER BY lc_produkt_phase.datum	
	 end 

	 INSERT @tmp3 
	 SELECT  TOP (100) PERCENT LC_Phasenr, Phase
     FROM            @tmp2
     GROUP BY LC_Phasenr, Phase
     ORDER BY MIN(datum)
    
	 DECLARE @phase VARCHAR(255)
	 DECLARE @i INT
     DECLARE @zwphase VARCHAR(255)
	 SET @zwphase=''
	 SET @i=0   
	 DECLARE xc CURSOR FOR 
	    SELECT phase FROM @tmp3
     OPEN xc
	 FETCH NEXT FROM xc INTO @phase
	 WHILE @@FETCH_STATUS=0 BEGIN
        IF @zwphase<>@phase BEGIN
		   SET @i=@i+1
		   SET @zwphase=@phase
		END
		UPDATE @tmp3 SET phase=STR(@i)+' '+@phase WHERE phase=@phase
   	    FETCH NEXT FROM xc INTO @phase
     END
     CLOSE xc
	 deallocate xc
	 INSERT @tmp1 SELECT lc_phasenr, phase FROM @tmp3
    

RETURN
end


GO

/****** Object:  UserDefinedFunction [dbo].[lc_rpt2]    Script Date: 02.05.2015 09:43:18 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE FUNCTION [dbo].[lc_rpt2]
(	
	@produkte VARCHAR(255),
	@von varchar(255),
	@bis varchar(255),
	@produkttypnr int
)
RETURNS @tmp1 TABLE (LC_Produktnr INT, Produkt VARCHAR(255), LC_PhaseNr INT, datum DATETIME)
AS
begin

	IF @von=''  SET @von='01.01.1900 '
	IF @bis=''  SET @bis='31.12.4712'

	IF @produkte='' BEGIN
       insert into @tmp1 
       SELECT distinct dbo.LC_Produkt.LC_ProduktNr, dbo.LC_Produkt.Bezeichnung AS Produkt, dbo.LC_Phase.LC_PhaseNr, dbo.LC_Produkt_Phase.Datum
		  FROM  dbo.LC_Produkt INNER JOIN
                dbo.LC_Produkt_Phase ON dbo.LC_Produkt.LC_ProduktNr = dbo.LC_Produkt_Phase.LC_ProduktNr INNER JOIN
                dbo.LC_Phase ON dbo.LC_Produkt_Phase.LC_PhaseNr = dbo.LC_Phase.LC_PhaseNr
          WHERE (dbo.LC_Phase.Aktiv = 1) AND (dbo.LC_Produkt_Phase.Aktiv = 1) AND (dbo.LC_Produkt.Aktiv = 1) AND (dbo.LC_Produkt.StatusNr = 1)
		  		  AND dbo.LC_Produkt_Phase.Datum BETWEEN @von AND @bis
          ORDER BY Produkt

    end
	IF @produkte<>'' BEGIN
       insert into @tmp1 
       SELECT distinct dbo.LC_Produkt.LC_ProduktNr, dbo.LC_Produkt.Bezeichnung AS Produkt, dbo.LC_Phase.LC_PhaseNr, dbo.LC_Produkt_Phase.Datum
		  FROM  dbo.LC_Produkt INNER JOIN
                dbo.LC_Produkt_Phase ON dbo.LC_Produkt.LC_ProduktNr = dbo.LC_Produkt_Phase.LC_ProduktNr INNER JOIN
                dbo.LC_Phase ON dbo.LC_Produkt_Phase.LC_PhaseNr = dbo.LC_Phase.LC_PhaseNr
          WHERE (dbo.LC_Phase.Aktiv = 1) AND (dbo.LC_Produkt_Phase.Aktiv = 1) AND (dbo.LC_Produkt.Aktiv = 1) AND (dbo.LC_Produkt.StatusNr = 1)
          and dbo.lc_produkt.lc_produktnr IN (SELECT item FROM dbo.fnkt_split(@produkte,',',1))
		  		  AND dbo.LC_Produkt_Phase.Datum BETWEEN @von AND @bis
          ORDER BY Produkt
   end

   

RETURN
end


GO

/****** Object:  UserDefinedFunction [dbo].[lc_rpt1]    Script Date: 02.05.2015 09:43:18 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE FUNCTION [dbo].[lc_rpt1]
(	
	@produkte VARCHAR(255),
	@von varchar(255),
	@bis varchar(255),
	@produkttypnr int
	

)
RETURNS @tmp1 TABLE (LC_Phasenr INT, Phase VARCHAR(255))
AS
begin

        declare @tmp2 TABLE (LC_Phasenr INT, Phase VARCHAR(255), datum datetime)
    declare @tmp3 TABLE (LC_Phasenr INT, Phase VARCHAR(255))


	IF @von=''  SET @von='01.01.1900 '
	IF @bis=''  SET @bis='31.12.4712'
	
	IF @produkte='' BEGIN
       insert into @tmp2 SELECT DISTINCT  dbo.LC_Phase.LC_PhaseNr, dbo.LC_Phase.Bezeichnung AS Phase, lc_produkt_phase.datum
		  FROM dbo.LC_Produkt INNER JOIN dbo.LC_Produkt_Phase ON dbo.LC_Produkt.LC_ProduktNr = dbo.LC_Produkt_Phase.LC_ProduktNr INNER JOIN
               dbo.LC_Phase ON dbo.LC_Produkt_Phase.LC_PhaseNr = dbo.LC_Phase.LC_PhaseNr
          WHERE  (dbo.LC_Phase.Aktiv = 1) AND (dbo.LC_Produkt_Phase.Aktiv = 1) AND (dbo.LC_Produkt.Aktiv = 1) AND (dbo.LC_Produkt.StatusNr = 1)    
		  AND dbo.LC_Produkt_Phase.Datum BETWEEN @von AND @bis
		  AND (@produkttypnr=-1 OR @produkttypnr = lc_produkt.ProdukttypNr)
		  ORDER BY lc_produkt_phase.datum	

    END
	IF @produkte<>'' BEGIN
       insert into @tmp2 SELECT DISTINCT  dbo.LC_Phase.LC_PhaseNr, dbo.LC_Phase.Bezeichnung AS Phase, lc_produkt_phase.datum
		  FROM dbo.LC_Produkt INNER JOIN dbo.LC_Produkt_Phase ON dbo.LC_Produkt.LC_ProduktNr = dbo.LC_Produkt_Phase.LC_ProduktNr INNER JOIN
               dbo.LC_Phase ON dbo.LC_Produkt_Phase.LC_PhaseNr = dbo.LC_Phase.LC_PhaseNr
          WHERE  (dbo.LC_Phase.Aktiv = 1) AND (dbo.LC_Produkt_Phase.Aktiv = 1) AND (dbo.LC_Produkt.Aktiv = 1) AND (dbo.LC_Produkt.StatusNr = 1)    
		  and dbo.LC_Produkt_Phase.LC_Produktnr IN (SELECT item FROM dbo.fnkt_split(@produkte,',',1))
		  AND dbo.LC_Produkt_Phase.Datum BETWEEN @von AND @bis
		  AND (@produkttypnr=-1 OR @produkttypnr = lc_produkt.ProdukttypNr)
		  ORDER BY lc_produkt_phase.datum	
	 end 

	 INSERT @tmp3 
	 SELECT  TOP (100) PERCENT LC_Phasenr, Phase
     FROM            @tmp2
     GROUP BY LC_Phasenr, Phase
     ORDER BY MIN(datum)
    
	 DECLARE @phase VARCHAR(255)
	 DECLARE @i INT
     DECLARE @zwphase VARCHAR(255)
	 SET @zwphase=''
	 SET @i=0   
	 DECLARE xc CURSOR FOR 
	    SELECT phase FROM @tmp3
     OPEN xc
	 FETCH NEXT FROM xc INTO @phase
	 WHILE @@FETCH_STATUS=0 BEGIN
        IF @zwphase<>@phase BEGIN
		   SET @i=@i+1
		   SET @zwphase=@phase
		END
		UPDATE @tmp3 SET phase=STR(@i)+' '+@phase WHERE phase=@phase
   	    FETCH NEXT FROM xc INTO @phase
     END
     CLOSE xc
	 deallocate xc
	 INSERT @tmp1 SELECT lc_phasenr, phase FROM @tmp3
    

RETURN
end



GO