USE [Vertragsverwaltung]
GO

/****** Object:  StoredProcedure [dbo].[pr_LC_SQL_SelectAll]    Script Date: 02.05.2015 09:42:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


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

GO

/****** Object:  StoredProcedure [dbo].[pr_LC_SQL_SelectOne]    Script Date: 02.05.2015 09:42:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


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

GO

/****** Object:  StoredProcedure [dbo].[pr_LC_SQL_Delete]    Script Date: 02.05.2015 09:42:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


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

GO

/****** Object:  StoredProcedure [dbo].[pr_LC_SQL_Update]    Script Date: 02.05.2015 09:42:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


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

GO

/****** Object:  StoredProcedure [dbo].[pr_LC_SQL_Insert]    Script Date: 02.05.2015 09:42:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


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

GO

/****** Object:  StoredProcedure [dbo].[pr_LC_Produkttyp_SelectAll]    Script Date: 02.05.2015 09:42:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


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

GO

/****** Object:  StoredProcedure [dbo].[pr_LC_Produkttyp_SelectOne]    Script Date: 02.05.2015 09:42:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


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

GO

/****** Object:  StoredProcedure [dbo].[pr_LC_Produkttyp_Delete]    Script Date: 02.05.2015 09:42:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


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

GO

/****** Object:  StoredProcedure [dbo].[pr_LC_Produkttyp_Update]    Script Date: 02.05.2015 09:42:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


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

GO

/****** Object:  StoredProcedure [dbo].[pr_LC_Produkttyp_Insert]    Script Date: 02.05.2015 09:42:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


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

GO

/****** Object:  StoredProcedure [dbo].[sp_lc_reporting]    Script Date: 02.05.2015 09:42:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_lc_reporting]
	 @fnkt INT,
	 @param1 VARCHAR(4096),
	 @param2 VARCHAR(4096),
	 @param3 VARCHAR(4096),
	 @param4 VARCHAR(4096),
	 @param5 VARCHAR(4096),
	 @param6 VARCHAR(4096)
AS
BEGIN
	-- Abgelaufene Überprüfungen
	IF @fnkt=1 BEGIN
		SELECT lc_produktnr AS ProduktNr, bezeichnung AS Produkt, Nächste_Ueberprüfung AS N_Ueberpruefung FROM lc_produkt WHERE Nächste_Ueberprüfung < GETDATE() AND aktiv=1 AND Nächste_Ueberprüfung<>'01.01.1900' 
	END
	-- Abgelaufenen Produkte in N Tagen  
  	IF @fnkt=2 BEGIN
		SELECT lc_produktnr AS ProduktNr, bezeichnung AS Produkt, Nächste_Ueberprüfung AS N_Ueberpruefung, DATEDIFF(DAY, GETDATE(),Nächste_Ueberprüfung) AS in_Tagen 
		FROM lc_produkt
		WHERE DATEDIFF(DAY,GETDATE(),Nächste_Ueberprüfung) >= @param1 AND aktiv=1 AND Nächste_Ueberprüfung<>'01.01.1900' 
	END  
	-- Abgelaufene Entscheidungen  
    IF @fnkt=3 BEGIN
		SELECT lc_produktnr AS ProduktNr, bezeichnung AS Produkt, Entscheidungstermin AS Termin 
		FROM lc_produkt WHERE Entscheidungstermin < GETDATE() AND aktiv=1 AND Entscheidungstermin<>'01.01.1900' 
	END
    --   
    IF @fnkt=4 BEGIN
		SELECT lc_produktnr AS ProduktNr, bezeichnung AS Produkt, Entscheidungstermin AS Termin, DATEDIFF(DAY, GETDATE(),Entscheidungstermin) AS in_Tagen 
		FROM lc_produkt
		WHERE DATEDIFF(DAY,GETDATE(),Entscheidungstermin) >= @param1 AND aktiv=1 AND Entscheidungstermin<>'01.01.1900' 
	END    

	DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

	-- Roadmap nach Phasen
	IF @fnkt=5 OR @fnkt=7 BEGIN
       IF @fnkt=7 BEGIN
	      SET @param3='31.12.2200'  
		  SET @param2=LEFT(CONVERT(VARCHAR, DATEADD(DAY,CAST(@param2 AS int),GETDATE()), 104), 10)
		  PRINT @param1
		  PRINT @param2
		  PRINT @param3
		  PRINT @param4
	   END

	   select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Phase) from dbo.lc_rpt3(@param1,@param2,@param3,@param4)
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
		PRINT @cols
		PRINT 'aaa'
		set @query = 'SELECT lc_produktnr as ProduktNr, Produkt,' + @cols + ' from 
             (
                select  p.lc_produktnr,p.Produkt, 
                  p.datum,
                  d.phase
                from dbo.lc_rpt4('+CHAR(39)+@param1+CHAR(39)+','+CHAR(39)+@param2+CHAR(39)+','+CHAR(39)+@param3+CHAR(39)+','+@param4+') p
                left join dbo.lc_rpt3('+CHAR(39)+@param1+CHAR(39)+','+CHAR(39)+@param2+CHAR(39)+','+CHAR(39)+@param3+CHAR(39)+','+@param4+') d
                  on p.lc_phasenr = d.lc_phasenr
            ) x
            pivot 
            (
                max(datum)
                for Phase in (' + @cols + ')
            ) p  ' 
			
			PRINT @query
			PRINT 'bbbb'
            execute(@query)     
   end     


	-- Roadmap nach Produkten
	IF @fnkt=6 BEGIN
	   select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Phase) from dbo.lc_rpt1(@param1,@param2,@param3, @param4)
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
		PRINT @cols

		set @query = 'SELECT lc_produktnr as ProduktNr, Produkt,' + @cols + ' from 
             (
                select  p.lc_produktnr,p.Produkt, 
                  p.datum,
                  d.phase
                from dbo.lc_rpt2('+CHAR(39)+@param1+CHAR(39)+','+CHAR(39)+@param2+CHAR(39)+','+CHAR(39)+@param3+CHAR(39)+','+@param4+') p
                left join dbo.lc_rpt1('+CHAR(39)+@param1+CHAR(39)+','+CHAR(39)+@param2+CHAR(39)+','+CHAR(39)+@param3+CHAR(39)+','+@param4+') d
                  on p.lc_phasenr = d.lc_phasenr
            ) x
            pivot 
            (
                max(datum)
                for Phase in (' + @cols + ')
            ) p  ' 

            execute(@query)     
   end     

  

  -- IF @fnkt=7 BEGIN
	 --  select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Phase) from dbo.lc_rpt5(@param1,@param2)
  --          FOR XML PATH(''), TYPE
  --          ).value('.', 'NVARCHAR(MAX)') 
  --      ,1,1,'')
		--PRINT @cols

		--set @query = 'SELECT lc_produktnr as ProduktNr, Produkt,' + @cols + ' from 
  --           (
  --              select  p.lc_produktnr,p.Produkt, 
  --                p.datum,
  --                d.phase
  --              from dbo.lc_rpt6('+CHAR(39)+@param1+CHAR(39)+','+CHAR(39)+@param2+CHAR(39)+') p
  --              left join dbo.lc_rpt5('+CHAR(39)+@param1+CHAR(39)+','+CHAR(39)+@param2+CHAR(39)+') d
  --                on p.lc_phasenr = d.lc_phasenr
  --          ) x
  --          pivot 
  --          (
  --              max(datum)
  --              for Phase in (' + @cols + ')
  --          ) p  ' 

  --          execute(@query)     
  -- end     



















   
			 --IF OBJECT_ID (N'_tmp_lc_rpt1', N'U') IS NOT NULL  DROP TABLE [dbo].[_tmp_lc_rpt1]
	   --IF OBJECT_ID (N'_tmp_lc_rpt2', N'U') IS NOT NULL  DROP TABLE [dbo].[_tmp_lc_rpt2]
	   --SELECT * INTO _tmp_lc_rpt1 FROM  dbo.lc_rpt1(@param1,@param2,@param3)
	   --SELECT * INTO _tmp_lc_rpt2 FROM  dbo.lc_rpt2(@param1,@param2,@param3)
 

			--set @query = 'SELECT lc_produktnr as ProduktNr, Produkt,' + @cols + ' from 
   --          (
   --             select  p.lc_produktnr,p.Produkt, 
   --               p.datum,
   --               d.phase
   --             from _tmp_lc_rpt2 p
   --             left join _tmp_lc_rpt1 d
   --               on p.lc_phasenr = d.lc_phasenr
   --         ) x
   --         pivot 
   --         (
   --             max(datum)
   --             for Phase in (' + @cols + ')
   --         ) p  ' 
END

GO

/****** Object:  StoredProcedure [dbo].[pr_LC_Phase_SelectAll]    Script Date: 02.05.2015 09:42:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


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

GO

/****** Object:  StoredProcedure [dbo].[pr_LC_Phase_SelectOne]    Script Date: 02.05.2015 09:42:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


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

GO

/****** Object:  StoredProcedure [dbo].[pr_LC_Phase_Delete]    Script Date: 02.05.2015 09:42:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


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

GO

/****** Object:  StoredProcedure [dbo].[pr_LC_Phase_Update]    Script Date: 02.05.2015 09:42:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


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

GO

/****** Object:  StoredProcedure [dbo].[pr_LC_Phase_Insert]    Script Date: 02.05.2015 09:42:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


---------------------------------------------------------------------------------
-- Stored procedure that will insert 1 row in the table 'LC_Phase'
-- Gets: @sBezeichnung varchar(255)
-- Gets: @sBeschreibung varchar(1024)
-- Gets: @daErstellt_am datetime
-- Gets: @daMutiert_am datetime
-- Gets: @iMutierer int
-- Gets: @bAktiv bit
-- Returns: @iLC_PhaseNr int
-- Returns: @iErrorCode int
---------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[pr_LC_Phase_Insert]
	@sBezeichnung varchar(255),
	@sBeschreibung varchar(1024),
	@daErstellt_am datetime,
	@daMutiert_am datetime,
	@iMutierer int,
	@bAktiv bit,
	@iLC_PhaseNr int OUTPUT,
	@iErrorCode int OUTPUT
AS
SET NOCOUNT ON
-- INSERT a new row in the table.
INSERT [dbo].[LC_Phase]
(
	[Bezeichnung],
	[Beschreibung],
	[Erstellt_am],
	[Mutiert_am],
	[Mutierer],
	[Aktiv]
)
VALUES
(
	@sBezeichnung,
	@sBeschreibung,
	@daErstellt_am,
	@daMutiert_am,
	@iMutierer,
	@bAktiv
)
-- Get the Error Code for the statement just executed.
SELECT @iErrorCode=@@ERROR
-- Get the IDENTITY value for the row just inserted.
SELECT @iLC_PhaseNr=SCOPE_IDENTITY()

GO

/****** Object:  StoredProcedure [dbo].[sp_lc_check_delete]    Script Date: 02.05.2015 09:42:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_lc_check_delete]
@lc_produktnr INT,
@fnkt int
AS 
BEGIN
  IF @fnkt=1 begin
     SELECT * FROM dbo.LC_Produkt WHERE Abgeloest_durch_produktnr=@lc_produktnr AND aktiv=1 ORDER BY Bezeichnung
  END 
  IF @fnkt=2 BEGIN
     SELECT * FROM dbo.LC_Produkt WHERE LC_ProduktNr_Parent=@lc_produktnr AND aktiv=1 AND IstStruktur=0 ORDER BY Bezeichnung 

  END
END
GO

/****** Object:  StoredProcedure [dbo].[sp_lc_get_produkte]    Script Date: 02.05.2015 09:42:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_lc_get_produkte]
AS 
    BEGIN
        DECLARE @tmp1 TABLE
            (
              id INT ,
              parentid INT ,
              bezeichnung VARCHAR(255) ,
              imageindex INT ,
              beschreibung VARCHAR(1024) ,
              IstStruktur BIT ,
              verweisnr INT ,
              copy INT ,
              originalid INT,
			  Produkttypnr int
            )

        SET NOCOUNT ON;

        UPDATE  lc_produkt
        SET     bezeichnung = ( SELECT  bezeichnung
                                FROM    applikation
                                WHERE   verweisnr = applikation.applikationnr
                              )
        WHERE   verweisnr > 0 
        UPDATE  lc_produkt
        SET     mutiert_am = GETDATE() ,
                aktiv = 0
        WHERE   verweisnr > 0
                AND verweisnr NOT IN ( SELECT   applikationnr
                                       FROM     applikation )
        INSERT  @tmp1
                SELECT  lc_produktnr AS id ,
                        lc_produktnr_parent AS parentid ,
                        bezeichnung ,
                        imageindex AS imageindex ,
                        ISNULL(Beschreibung, '') AS Beschreibung ,
                        IstStruktur ,
                        ISNULL(verweisnr, 0) AS Verweisnr ,
                        0 AS copy ,
                        LC_ProduktNr, 
						ProdukttypNr
                FROM    dbo.LC_Produkt
                WHERE   aktiv = 1 AND IstStruktur=0


      
    END

    SELECT  *
    FROM    @tmp1
    ORDER BY bezeichnung
GO

/****** Object:  StoredProcedure [dbo].[pr_LC_Produkt_SelectAll]    Script Date: 02.05.2015 09:42:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


---------------------------------------------------------------------------------
-- Stored procedure that will select all rows from the table 'LC_Produkt'
-- Returns: @iErrorCode int
---------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[pr_LC_Produkt_SelectAll]
	@iErrorCode int OUTPUT
AS
SET NOCOUNT ON
-- SELECT all rows from the table.
SELECT
	[LC_ProduktNr],
	[LC_ProduktNr_Parent],
	[Bezeichnung],
	[Beschreibung],
	[Erstellt_am],
	[Mutiert_am],
	[Mutierer],
	[Aktiv],
	[Imageindex],
	[ProdukttypNr],
	[Image],
	[Version],
	[StatusNr],
	[Nächste_Ueberprüfung],
	[Entscheidungstermin],
	[Abgeloest_am],
	[Abgeloest_durch],
	[IstStruktur],
	[Verweisnr],
	[TerminBemerkung],
	[Abgeloest_durch_produktnr],
	[Abgeliest_durch_wen]
FROM [dbo].[LC_Produkt]
ORDER BY 
	[LC_ProduktNr] ASC
-- Get the Error Code for the statement just executed.
SELECT @iErrorCode=@@ERROR

GO

/****** Object:  StoredProcedure [dbo].[pr_LC_Produkt_SelectOne]    Script Date: 02.05.2015 09:42:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


---------------------------------------------------------------------------------
-- Stored procedure that will select an existing row from the table 'LC_Produkt'
-- based on the Primary Key.
-- Gets: @iLC_ProduktNr int
-- Returns: @iErrorCode int
---------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[pr_LC_Produkt_SelectOne]
	@iLC_ProduktNr int,
	@iErrorCode int OUTPUT
AS
SET NOCOUNT ON
-- SELECT an existing row from the table.
SELECT
	[LC_ProduktNr],
	[LC_ProduktNr_Parent],
	[Bezeichnung],
	[Beschreibung],
	[Erstellt_am],
	[Mutiert_am],
	[Mutierer],
	[Aktiv],
	[Imageindex],
	[ProdukttypNr],
	[Image],
	[Version],
	[StatusNr],
	[Nächste_Ueberprüfung],
	[Entscheidungstermin],
	[Abgeloest_am],
	[Abgeloest_durch],
	[IstStruktur],
	[Verweisnr],
	[TerminBemerkung],
	[Abgeloest_durch_produktnr],
	[Abgeliest_durch_wen]
FROM [dbo].[LC_Produkt]
WHERE
	[LC_ProduktNr] = @iLC_ProduktNr
-- Get the Error Code for the statement just executed.
SELECT @iErrorCode=@@ERROR

GO

/****** Object:  StoredProcedure [dbo].[pr_LC_Produkt_Delete]    Script Date: 02.05.2015 09:42:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


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

GO

/****** Object:  StoredProcedure [dbo].[pr_LC_Produkt_Update]    Script Date: 02.05.2015 09:42:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


---------------------------------------------------------------------------------
-- Stored procedure that will update an existing row in the table 'LC_Produkt'
-- Gets: @iLC_ProduktNr int
-- Gets: @iLC_ProduktNr_Parent int
-- Gets: @sBezeichnung varchar(255)
-- Gets: @sBeschreibung varchar(1024)
-- Gets: @daErstellt_am datetime
-- Gets: @daMutiert_am datetime
-- Gets: @iMutierer int
-- Gets: @bAktiv bit
-- Gets: @iImageindex int
-- Gets: @iProdukttypNr int
-- Gets: @blobImage image
-- Gets: @sVersion varchar(255)
-- Gets: @iStatusNr int
-- Gets: @daNächste_Ueberprüfung datetime
-- Gets: @daEntscheidungstermin datetime
-- Gets: @daAbgeloest_am datetime
-- Gets: @sAbgeloest_durch varchar(255)
-- Gets: @bIstStruktur bit
-- Gets: @iVerweisnr int
-- Gets: @sTerminBemerkung varchar(1024)
-- Gets: @iAbgeloest_durch_produktnr int
-- Gets: @iAbgeliest_durch_wen int
-- Returns: @iErrorCode int
---------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[pr_LC_Produkt_Update]
	@iLC_ProduktNr int,
	@iLC_ProduktNr_Parent int,
	@sBezeichnung varchar(255),
	@sBeschreibung varchar(1024),
	@daErstellt_am datetime,
	@daMutiert_am datetime,
	@iMutierer int,
	@bAktiv bit,
	@iImageindex int,
	@iProdukttypNr int,
	@blobImage image,
	@sVersion varchar(255),
	@iStatusNr int,
	@daNächste_Ueberprüfung datetime,
	@daEntscheidungstermin datetime,
	@daAbgeloest_am datetime,
	@sAbgeloest_durch varchar(255),
	@bIstStruktur bit,
	@iVerweisnr int,
	@sTerminBemerkung varchar(1024),
	@iAbgeloest_durch_produktnr int,
	@iAbgeliest_durch_wen int,
	@iErrorCode int OUTPUT
AS
SET NOCOUNT ON
-- UPDATE an existing row in the table.
UPDATE [dbo].[LC_Produkt]
SET 
	[LC_ProduktNr_Parent] = @iLC_ProduktNr_Parent,
	[Bezeichnung] = @sBezeichnung,
	[Beschreibung] = @sBeschreibung,
	[Erstellt_am] = @daErstellt_am,
	[Mutiert_am] = @daMutiert_am,
	[Mutierer] = @iMutierer,
	[Aktiv] = @bAktiv,
	[Imageindex] = @iImageindex,
	[ProdukttypNr] = @iProdukttypNr,
	[Image] = @blobImage,
	[Version] = @sVersion,
	[StatusNr] = @iStatusNr,
	[Nächste_Ueberprüfung] = @daNächste_Ueberprüfung,
	[Entscheidungstermin] = @daEntscheidungstermin,
	[Abgeloest_am] = @daAbgeloest_am,
	[Abgeloest_durch] = @sAbgeloest_durch,
	[IstStruktur] = @bIstStruktur,
	[Verweisnr] = @iVerweisnr,
	[TerminBemerkung] = @sTerminBemerkung,
	[Abgeloest_durch_produktnr] = @iAbgeloest_durch_produktnr,
	[Abgeliest_durch_wen] = @iAbgeliest_durch_wen
WHERE
	[LC_ProduktNr] = @iLC_ProduktNr
-- Get the Error Code for the statement just executed.
SELECT @iErrorCode=@@ERROR

GO

/****** Object:  StoredProcedure [dbo].[pr_LC_Produkt_Insert]    Script Date: 02.05.2015 09:42:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


---------------------------------------------------------------------------------
-- Stored procedure that will insert 1 row in the table 'LC_Produkt'
-- Gets: @iLC_ProduktNr int
-- Gets: @iLC_ProduktNr_Parent int
-- Gets: @sBezeichnung varchar(255)
-- Gets: @sBeschreibung varchar(1024)
-- Gets: @daErstellt_am datetime
-- Gets: @daMutiert_am datetime
-- Gets: @iMutierer int
-- Gets: @bAktiv bit
-- Gets: @iImageindex int
-- Gets: @iProdukttypNr int
-- Gets: @blobImage image
-- Gets: @sVersion varchar(255)
-- Gets: @iStatusNr int
-- Gets: @daNächste_Ueberprüfung datetime
-- Gets: @daEntscheidungstermin datetime
-- Gets: @daAbgeloest_am datetime
-- Gets: @sAbgeloest_durch varchar(255)
-- Gets: @bIstStruktur bit
-- Gets: @iVerweisnr int
-- Gets: @sTerminBemerkung varchar(1024)
-- Gets: @iAbgeloest_durch_produktnr int
-- Gets: @iAbgeliest_durch_wen int
-- Returns: @iErrorCode int
---------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[pr_LC_Produkt_Insert]
	@iLC_ProduktNr int,
	@iLC_ProduktNr_Parent int,
	@sBezeichnung varchar(255),
	@sBeschreibung varchar(1024),
	@daErstellt_am datetime,
	@daMutiert_am datetime,
	@iMutierer int,
	@bAktiv bit,
	@iImageindex int,
	@iProdukttypNr int,
	@blobImage image,
	@sVersion varchar(255),
	@iStatusNr int,
	@daNächste_Ueberprüfung datetime,
	@daEntscheidungstermin datetime,
	@daAbgeloest_am datetime,
	@sAbgeloest_durch varchar(255),
	@bIstStruktur bit,
	@iVerweisnr int,
	@sTerminBemerkung varchar(1024),
	@iAbgeloest_durch_produktnr int,
	@iAbgeliest_durch_wen int,
	@iErrorCode int OUTPUT
AS
SET NOCOUNT ON
-- INSERT a new row in the table.
INSERT [dbo].[LC_Produkt]
(
	[LC_ProduktNr],
	[LC_ProduktNr_Parent],
	[Bezeichnung],
	[Beschreibung],
	[Erstellt_am],
	[Mutiert_am],
	[Mutierer],
	[Aktiv],
	[Imageindex],
	[ProdukttypNr],
	[Image],
	[Version],
	[StatusNr],
	[Nächste_Ueberprüfung],
	[Entscheidungstermin],
	[Abgeloest_am],
	[Abgeloest_durch],
	[IstStruktur],
	[Verweisnr],
	[TerminBemerkung],
	[Abgeloest_durch_produktnr],
	[Abgeliest_durch_wen]
)
VALUES
(
	@iLC_ProduktNr,
	@iLC_ProduktNr_Parent,
	@sBezeichnung,
	@sBeschreibung,
	@daErstellt_am,
	@daMutiert_am,
	@iMutierer,
	@bAktiv,
	@iImageindex,
	@iProdukttypNr,
	@blobImage,
	@sVersion,
	@iStatusNr,
	@daNächste_Ueberprüfung,
	@daEntscheidungstermin,
	@daAbgeloest_am,
	@sAbgeloest_durch,
	@bIstStruktur,
	@iVerweisnr,
	@sTerminBemerkung,
	@iAbgeloest_durch_produktnr,
	@iAbgeliest_durch_wen
)
-- Get the Error Code for the statement just executed.
SELECT @iErrorCode=@@ERROR

GO

/****** Object:  StoredProcedure [dbo].[pr_LC_Produkt_Copy_SelectAll]    Script Date: 02.05.2015 09:42:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


---------------------------------------------------------------------------------
-- Stored procedure that will select all rows from the table 'LC_Produkt_Copy'
-- Returns: @iErrorCode int
---------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[pr_LC_Produkt_Copy_SelectAll]
	@iErrorCode int OUTPUT
AS
SET NOCOUNT ON
-- SELECT all rows from the table.
SELECT
	[lc_produkt_copynr],
	[lc_produkt_parentnr],
	[lc_produktnr],
	[erstellt_am],
	[mutiert_am],
	[mutierer],
	[aktiv]
FROM [dbo].[LC_Produkt_Copy]
ORDER BY 
	[lc_produkt_copynr] ASC
-- Get the Error Code for the statement just executed.
SELECT @iErrorCode=@@ERROR

GO

/****** Object:  StoredProcedure [dbo].[pr_LC_Produkt_Copy_SelectOne]    Script Date: 02.05.2015 09:42:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


---------------------------------------------------------------------------------
-- Stored procedure that will select an existing row from the table 'LC_Produkt_Copy'
-- based on the Primary Key.
-- Gets: @ilc_produkt_copynr int
-- Returns: @iErrorCode int
---------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[pr_LC_Produkt_Copy_SelectOne]
	@ilc_produkt_copynr int,
	@iErrorCode int OUTPUT
AS
SET NOCOUNT ON
-- SELECT an existing row from the table.
SELECT
	[lc_produkt_copynr],
	[lc_produkt_parentnr],
	[lc_produktnr],
	[erstellt_am],
	[mutiert_am],
	[mutierer],
	[aktiv]
FROM [dbo].[LC_Produkt_Copy]
WHERE
	[lc_produkt_copynr] = @ilc_produkt_copynr
-- Get the Error Code for the statement just executed.
SELECT @iErrorCode=@@ERROR

GO

/****** Object:  StoredProcedure [dbo].[pr_LC_Produkt_Copy_Delete]    Script Date: 02.05.2015 09:42:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


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

GO

/****** Object:  StoredProcedure [dbo].[pr_LC_Produkt_Copy_Update]    Script Date: 02.05.2015 09:42:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


---------------------------------------------------------------------------------
-- Stored procedure that will update an existing row in the table 'LC_Produkt_Copy'
-- Gets: @ilc_produkt_copynr int
-- Gets: @ilc_produkt_parentnr int
-- Gets: @ilc_produktnr int
-- Gets: @daerstellt_am datetime
-- Gets: @damutiert_am datetime
-- Gets: @imutierer int
-- Gets: @baktiv bit
-- Returns: @iErrorCode int
---------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[pr_LC_Produkt_Copy_Update]
	@ilc_produkt_copynr int,
	@ilc_produkt_parentnr int,
	@ilc_produktnr int,
	@daerstellt_am datetime,
	@damutiert_am datetime,
	@imutierer int,
	@baktiv bit,
	@iErrorCode int OUTPUT
AS
SET NOCOUNT ON
-- UPDATE an existing row in the table.
UPDATE [dbo].[LC_Produkt_Copy]
SET 
	[lc_produkt_parentnr] = @ilc_produkt_parentnr,
	[lc_produktnr] = @ilc_produktnr,
	[erstellt_am] = @daerstellt_am,
	[mutiert_am] = @damutiert_am,
	[mutierer] = @imutierer,
	[aktiv] = @baktiv
WHERE
	[lc_produkt_copynr] = @ilc_produkt_copynr
-- Get the Error Code for the statement just executed.
SELECT @iErrorCode=@@ERROR

GO

/****** Object:  StoredProcedure [dbo].[pr_LC_Produkt_Copy_Insert]    Script Date: 02.05.2015 09:42:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


---------------------------------------------------------------------------------
-- Stored procedure that will insert 1 row in the table 'LC_Produkt_Copy'
-- Gets: @ilc_produkt_copynr int
-- Gets: @ilc_produkt_parentnr int
-- Gets: @ilc_produktnr int
-- Gets: @daerstellt_am datetime
-- Gets: @damutiert_am datetime
-- Gets: @imutierer int
-- Gets: @baktiv bit
-- Returns: @iErrorCode int
---------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[pr_LC_Produkt_Copy_Insert]
	@ilc_produkt_copynr int,
	@ilc_produkt_parentnr int,
	@ilc_produktnr int,
	@daerstellt_am datetime,
	@damutiert_am datetime,
	@imutierer int,
	@baktiv bit,
	@iErrorCode int OUTPUT
AS
SET NOCOUNT ON
-- INSERT a new row in the table.
INSERT [dbo].[LC_Produkt_Copy]
(
	[lc_produkt_copynr],
	[lc_produkt_parentnr],
	[lc_produktnr],
	[erstellt_am],
	[mutiert_am],
	[mutierer],
	[aktiv]
)
VALUES
(
	@ilc_produkt_copynr,
	@ilc_produkt_parentnr,
	@ilc_produktnr,
	@daerstellt_am,
	@damutiert_am,
	@imutierer,
	@baktiv
)
-- Get the Error Code for the statement just executed.
SELECT @iErrorCode=@@ERROR

GO

/****** Object:  StoredProcedure [dbo].[sp_lc_get_Struktur]    Script Date: 02.05.2015 09:42:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_lc_get_Struktur]
AS
BEGIN
declare @tmp1 TABLE (id int, parentid int, bezeichnung varchar(255),imageindex INT,beschreibung VARCHAR(1024), copy int)

	SET NOCOUNT ON;

	INSERT @tmp1 SELECT lc_produktnr AS id, lc_produktnr_parent AS parentid, bezeichnung, imageindex AS imageindex, ISNULL(Beschreibung,'') AS Beschreibung,0  FROM dbo.LC_Produkt WHERE aktiv=1 AND IstStruktur=1

   INSERT   @tmp1 ( id, parentid, bezeichnung, imageindex,beschreibung,copy)
               VALUES   ( 0, NULL, 'Root', 1,'',0)


	UPDATE @tmp1 SET imageindex = imageindex + 0 WHERE id IN (SELECT lc_produktnr FROM lc_produkt WHERE IstStruktur=1)
               SELECT   *
               FROM     @tmp1
               ORDER BY bezeichnung


END

GO

/****** Object:  StoredProcedure [dbo].[sp_LC_get_stammdaten]    Script Date: 02.05.2015 09:42:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO



-- =============================================
-- Author:	Stefan Hutter
-- Create date:    14.4.2009
-- Description:	Liest die Stammdatentabellen
-- Changelog:
-- 15.4.09 Anpassung, dass nur die aktiven Datenelemente ausgelesen werden
-- =============================================
Create PROCEDURE [dbo].[sp_LC_get_stammdaten]
@mitarbeiternr int,
@tabelle varchar(255),
@orderby varchar(255)
as
begin
   declare @xsql varchar(255)
   if upper(@tabelle)='KOSTENART' begin
      select kostenartnr, bezeichnung + ' - ' + beschreibung as Bezeichnung from kostenart where aktiv=1 order by bezeichnung
      return
   end

   if upper(@tabelle)='KONTAKTTYP_GREMIUM' begin
      select kontakttypnr, bezeichnung, applikationsbereichnr from kontakttyp where applikationsbereichnr=3 and aktiv=1 order by bezeichnung
      return
   end

   if upper(@tabelle)='RELEASEART' begin
      select Releaseartnr, bezeichnung as Bezeichnung from Releaseart where aktiv=1 order by bezeichnung
      return
   end

   if upper(@tabelle)='PERSON' begin
      select personnr, Name +' '+Vorname as Bezeichnung from Person where Vertragspartnernr=1 and Aktiv=1 order by Name, Vorname
      return
   end
   if upper(@tabelle)='PERSON1' begin
      select personnr, Name +' '+Vorname as Bezeichnung from Person where Vertragspartnernr=1 and Aktiv=1 order by Name, Vorname
      return
   end
   if upper(@tabelle)='PERSON2' begin
      select personnr, Name +' '+Vorname as Bezeichnung from Person where Vertragspartnernr=1 and Aktiv=1 order by Name, Vorname
      return
   end

   if upper(@tabelle)='INSTALLATIONTYP' begin
      SELECT * from installationtyp ORDER BY sort
      return
   end


   set @xsql='Select * from ' + @tabelle + ' where aktiv=1 order by ' + @orderby 
   exec(@xsql)
end

GO

/****** Object:  StoredProcedure [dbo].[sp_get_LCdokumente]    Script Date: 02.05.2015 09:42:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
Create PROCEDURE [dbo].[sp_get_LCdokumente]
---------------------------------------------------------------------------------
-- Stored procedure that will select all rows from the table 'Kuendigungsfrist'
-- Returns: @iErrorCode int
---------------------------------------------------------------------------------
	@keyvalue int,
	@doktype int,
	@mitarbeiternr int
AS
SELECT     0 as DokIcon, dbo.lc_Dokument.DokumentNr, dbo.lc_Dokument.KeyValue, dbo.lc_Dokumenttyp.Bezeichnung as Dokumenttyp, dbo.lc_Dokument.Bezeichnung AS Bezeichnung, dbo.lc_Dokument.Beschreibung, 
                      dbo.lc_Dokument.Filename, dbo.lc_Dokument.OriginalFilename_incl_Path, dbo.lc_Dokument.Version, dbo.lc_Dokument.VersionsNr, dbo.lc_Dokument.Versionsdatum, 
                      dbo.lc_Dokument.Erstellt_am, dbo.lc_Dokument.Mutiert_am, dbo.lc_Dokument.Mutierer, dbo.lc_Dokument.Aktiv, dbo.lc_Dokument.DocImage, 0 as DokIdon
FROM         dbo.lc_Dokument INNER JOIN
                      dbo.lc_Dokumenttyp ON dbo.lc_Dokument.DokumenttypNr = dbo.lc_Dokumenttyp.Dokumenttypnr
where Keyvalue = @keyvalue and doktype=@doktype
order by dbo.lc_dokument.erstellt_am desc



RETURN


GO

/****** Object:  StoredProcedure [dbo].[pr_LC_Dokumenttyp_SelectAll]    Script Date: 02.05.2015 09:42:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


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

GO

/****** Object:  StoredProcedure [dbo].[pr_LC_Dokumenttyp_SelectOne]    Script Date: 02.05.2015 09:42:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


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

GO

/****** Object:  StoredProcedure [dbo].[pr_LC_Dokumenttyp_Delete]    Script Date: 02.05.2015 09:42:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


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

GO

/****** Object:  StoredProcedure [dbo].[pr_LC_Dokumenttyp_Update]    Script Date: 02.05.2015 09:42:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


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

GO

/****** Object:  StoredProcedure [dbo].[pr_LC_Dokumenttyp_Insert]    Script Date: 02.05.2015 09:42:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


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

GO

/****** Object:  StoredProcedure [dbo].[pr_LC_DokumentAblageTyp_SelectAll]    Script Date: 02.05.2015 09:42:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


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

GO

/****** Object:  StoredProcedure [dbo].[pr_LC_DokumentAblageTyp_SelectOne]    Script Date: 02.05.2015 09:42:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


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

GO

/****** Object:  StoredProcedure [dbo].[pr_LC_DokumentAblageTyp_Delete]    Script Date: 02.05.2015 09:42:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


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

GO

/****** Object:  StoredProcedure [dbo].[pr_LC_DokumentAblageTyp_Update]    Script Date: 02.05.2015 09:42:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


---------------------------------------------------------------------------------
-- Stored procedure that will update an existing row in the table 'LC_DokumentAblageTyp'
-- Gets: @iDokumentAblageTypNr int
-- Gets: @sBezeichnung varchar(255)
-- Gets: @daErstellt_am datetime
-- Gets: @daMutiert_am datetime
-- Gets: @iMutierer int
-- Gets: @bAktiv bit
-- Gets: @iMandantNr int
-- Returns: @iErrorCode int
---------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[pr_LC_DokumentAblageTyp_Update]
	@iDokumentAblageTypNr int,
	@sBezeichnung varchar(255),
	@daErstellt_am datetime,
	@daMutiert_am datetime,
	@iMutierer int,
	@bAktiv bit,
	@iMandantNr int,
	@iErrorCode int OUTPUT
AS
SET NOCOUNT ON
-- UPDATE an existing row in the table.
UPDATE [dbo].[LC_DokumentAblageTyp]
SET 
	[Bezeichnung] = @sBezeichnung,
	[Erstellt_am] = @daErstellt_am,
	[Mutiert_am] = @daMutiert_am,
	[Mutierer] = @iMutierer,
	[Aktiv] = @bAktiv,
	[MandantNr] = @iMandantNr
WHERE
	[DokumentAblageTypNr] = @iDokumentAblageTypNr
-- Get the Error Code for the statement just executed.
SELECT @iErrorCode=@@ERROR

GO

/****** Object:  StoredProcedure [dbo].[pr_LC_DokumentAblageTyp_Insert]    Script Date: 02.05.2015 09:42:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


---------------------------------------------------------------------------------
-- Stored procedure that will insert 1 row in the table 'LC_DokumentAblageTyp'
-- Gets: @iDokumentAblageTypNr int
-- Gets: @sBezeichnung varchar(255)
-- Gets: @daErstellt_am datetime
-- Gets: @daMutiert_am datetime
-- Gets: @iMutierer int
-- Gets: @bAktiv bit
-- Gets: @iMandantNr int
-- Returns: @iErrorCode int
---------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[pr_LC_DokumentAblageTyp_Insert]
	@iDokumentAblageTypNr int,
	@sBezeichnung varchar(255),
	@daErstellt_am datetime,
	@daMutiert_am datetime,
	@iMutierer int,
	@bAktiv bit,
	@iMandantNr int,
	@iErrorCode int OUTPUT
AS
SET NOCOUNT ON
-- INSERT a new row in the table.
INSERT [dbo].[LC_DokumentAblageTyp]
(
	[DokumentAblageTypNr],
	[Bezeichnung],
	[Erstellt_am],
	[Mutiert_am],
	[Mutierer],
	[Aktiv],
	[MandantNr]
)
VALUES
(
	@iDokumentAblageTypNr,
	@sBezeichnung,
	@daErstellt_am,
	@daMutiert_am,
	@iMutierer,
	@bAktiv,
	@iMandantNr
)
-- Get the Error Code for the statement just executed.
SELECT @iErrorCode=@@ERROR

GO

/****** Object:  StoredProcedure [dbo].[pr_LC_DokumentAblageort_SelectAll]    Script Date: 02.05.2015 09:42:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


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

GO

/****** Object:  StoredProcedure [dbo].[pr_LC_DokumentAblageort_SelectOne]    Script Date: 02.05.2015 09:42:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


---------------------------------------------------------------------------------
-- Stored procedure that will select an existing row from the table 'LC_DokumentAblageort'
-- based on the Primary Key.
-- Gets: @iDokumentablageortNr int
-- Returns: @iErrorCode int
---------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[pr_LC_DokumentAblageort_SelectOne]
	@iDokumentablageortNr int,
	@iErrorCode int OUTPUT
AS
SET NOCOUNT ON
-- SELECT an existing row from the table.
SELECT
	[DokumentablageortNr],
	[DokumentablagetypNr],
	[DokumentNr],
	[Ablageort],
	[Beschreibung],
	[Erstellt_am],
	[Mutiert_am],
	[Mutierer],
	[Aktiv],
	[MandantNr]
FROM [dbo].[LC_DokumentAblageort]
WHERE
	[DokumentablageortNr] = @iDokumentablageortNr
-- Get the Error Code for the statement just executed.
SELECT @iErrorCode=@@ERROR

GO

/****** Object:  StoredProcedure [dbo].[pr_LC_DokumentAblageort_Delete]    Script Date: 02.05.2015 09:42:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


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

GO

/****** Object:  StoredProcedure [dbo].[pr_LC_DokumentAblageort_Update]    Script Date: 02.05.2015 09:42:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


---------------------------------------------------------------------------------
-- Stored procedure that will update an existing row in the table 'LC_DokumentAblageort'
-- Gets: @iDokumentablageortNr int
-- Gets: @iDokumentablagetypNr int
-- Gets: @iDokumentNr int
-- Gets: @sAblageort varchar(255)
-- Gets: @sBeschreibung varchar(255)
-- Gets: @daErstellt_am datetime
-- Gets: @daMutiert_am datetime
-- Gets: @iMutierer int
-- Gets: @bAktiv bit
-- Gets: @iMandantNr int
-- Returns: @iErrorCode int
---------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[pr_LC_DokumentAblageort_Update]
	@iDokumentablageortNr int,
	@iDokumentablagetypNr int,
	@iDokumentNr int,
	@sAblageort varchar(255),
	@sBeschreibung varchar(255),
	@daErstellt_am datetime,
	@daMutiert_am datetime,
	@iMutierer int,
	@bAktiv bit,
	@iMandantNr int,
	@iErrorCode int OUTPUT
AS
SET NOCOUNT ON
-- UPDATE an existing row in the table.
UPDATE [dbo].[LC_DokumentAblageort]
SET 
	[DokumentablagetypNr] = @iDokumentablagetypNr,
	[DokumentNr] = @iDokumentNr,
	[Ablageort] = @sAblageort,
	[Beschreibung] = @sBeschreibung,
	[Erstellt_am] = @daErstellt_am,
	[Mutiert_am] = @daMutiert_am,
	[Mutierer] = @iMutierer,
	[Aktiv] = @bAktiv,
	[MandantNr] = @iMandantNr
WHERE
	[DokumentablageortNr] = @iDokumentablageortNr
-- Get the Error Code for the statement just executed.
SELECT @iErrorCode=@@ERROR

GO

/****** Object:  StoredProcedure [dbo].[pr_LC_DokumentAblageort_Insert]    Script Date: 02.05.2015 09:42:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


---------------------------------------------------------------------------------
-- Stored procedure that will insert 1 row in the table 'LC_DokumentAblageort'
-- Gets: @iDokumentablageortNr int
-- Gets: @iDokumentablagetypNr int
-- Gets: @iDokumentNr int
-- Gets: @sAblageort varchar(255)
-- Gets: @sBeschreibung varchar(255)
-- Gets: @daErstellt_am datetime
-- Gets: @daMutiert_am datetime
-- Gets: @iMutierer int
-- Gets: @bAktiv bit
-- Gets: @iMandantNr int
-- Returns: @iErrorCode int
---------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[pr_LC_DokumentAblageort_Insert]
	@iDokumentablageortNr int,
	@iDokumentablagetypNr int,
	@iDokumentNr int,
	@sAblageort varchar(255),
	@sBeschreibung varchar(255),
	@daErstellt_am datetime,
	@daMutiert_am datetime,
	@iMutierer int,
	@bAktiv bit,
	@iMandantNr int,
	@iErrorCode int OUTPUT
AS
SET NOCOUNT ON
-- INSERT a new row in the table.
INSERT [dbo].[LC_DokumentAblageort]
(
	[DokumentablageortNr],
	[DokumentablagetypNr],
	[DokumentNr],
	[Ablageort],
	[Beschreibung],
	[Erstellt_am],
	[Mutiert_am],
	[Mutierer],
	[Aktiv],
	[MandantNr]
)
VALUES
(
	@iDokumentablageortNr,
	@iDokumentablagetypNr,
	@iDokumentNr,
	@sAblageort,
	@sBeschreibung,
	@daErstellt_am,
	@daMutiert_am,
	@iMutierer,
	@bAktiv,
	@iMandantNr
)
-- Get the Error Code for the statement just executed.
SELECT @iErrorCode=@@ERROR

GO

/****** Object:  StoredProcedure [dbo].[pr_LC_Dokument_SelectAll]    Script Date: 02.05.2015 09:42:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


---------------------------------------------------------------------------------
-- Stored procedure that will select all rows from the table 'LC_Dokument'
-- Returns: @iErrorCode int
---------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[pr_LC_Dokument_SelectAll]
	@iErrorCode int OUTPUT
AS
SET NOCOUNT ON
-- SELECT all rows from the table.
SELECT
	[DokumentNr],
	[KeyValue],
	[DokType],
	[DokumenttypNr],
	[Bezeichnung],
	[Beschreibung],
	[Filename],
	[OriginalFilename_incl_Path],
	[Version],
	[VersionsNr],
	[Versionsdatum],
	[Erstellt_am],
	[Mutiert_am],
	[Mutierer],
	[Aktiv],
	[SecurityLevelNr],
	[DocImage]
FROM [dbo].[LC_Dokument]
ORDER BY 
	[DokumentNr] ASC
-- Get the Error Code for the statement just executed.
SELECT @iErrorCode=@@ERROR

GO

/****** Object:  StoredProcedure [dbo].[pr_LC_Dokument_SelectOne]    Script Date: 02.05.2015 09:42:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


---------------------------------------------------------------------------------
-- Stored procedure that will select an existing row from the table 'LC_Dokument'
-- based on the Primary Key.
-- Gets: @iDokumentNr int
-- Returns: @iErrorCode int
---------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[pr_LC_Dokument_SelectOne]
	@iDokumentNr int,
	@iErrorCode int OUTPUT
AS
SET NOCOUNT ON
-- SELECT an existing row from the table.
SELECT
	[DokumentNr],
	[KeyValue],
	[DokType],
	[DokumenttypNr],
	[Bezeichnung],
	[Beschreibung],
	[Filename],
	[OriginalFilename_incl_Path],
	[Version],
	[VersionsNr],
	[Versionsdatum],
	[Erstellt_am],
	[Mutiert_am],
	[Mutierer],
	[Aktiv],
	[SecurityLevelNr],
	[DocImage]
FROM [dbo].[LC_Dokument]
WHERE
	[DokumentNr] = @iDokumentNr
-- Get the Error Code for the statement just executed.
SELECT @iErrorCode=@@ERROR

GO

/****** Object:  StoredProcedure [dbo].[pr_LC_Dokument_Delete]    Script Date: 02.05.2015 09:42:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


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

GO

/****** Object:  StoredProcedure [dbo].[pr_LC_Dokument_Update]    Script Date: 02.05.2015 09:42:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


---------------------------------------------------------------------------------
-- Stored procedure that will update an existing row in the table 'LC_Dokument'
-- Gets: @iDokumentNr int
-- Gets: @iKeyValue int
-- Gets: @iDokType int
-- Gets: @iDokumenttypNr int
-- Gets: @sBezeichnung varchar(50)
-- Gets: @sBeschreibung varchar(2048)
-- Gets: @sFilename varchar(255)
-- Gets: @sOriginalFilename_incl_Path varchar(255)
-- Gets: @sVersion varchar(50)
-- Gets: @sVersionsNr varchar(50)
-- Gets: @daVersionsdatum datetime
-- Gets: @daErstellt_am datetime
-- Gets: @daMutiert_am datetime
-- Gets: @iMutierer int
-- Gets: @bAktiv bit
-- Gets: @iSecurityLevelNr int
-- Gets: @blobDocImage image
-- Returns: @iErrorCode int
---------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[pr_LC_Dokument_Update]
	@iDokumentNr int,
	@iKeyValue int,
	@iDokType int,
	@iDokumenttypNr int,
	@sBezeichnung varchar(50),
	@sBeschreibung varchar(2048),
	@sFilename varchar(255),
	@sOriginalFilename_incl_Path varchar(255),
	@sVersion varchar(50),
	@sVersionsNr varchar(50),
	@daVersionsdatum datetime,
	@daErstellt_am datetime,
	@daMutiert_am datetime,
	@iMutierer int,
	@bAktiv bit,
	@iSecurityLevelNr int,
	@blobDocImage image,
	@iErrorCode int OUTPUT
AS
SET NOCOUNT ON
-- UPDATE an existing row in the table.
UPDATE [dbo].[LC_Dokument]
SET 
	[KeyValue] = @iKeyValue,
	[DokType] = @iDokType,
	[DokumenttypNr] = @iDokumenttypNr,
	[Bezeichnung] = @sBezeichnung,
	[Beschreibung] = @sBeschreibung,
	[Filename] = @sFilename,
	[OriginalFilename_incl_Path] = @sOriginalFilename_incl_Path,
	[Version] = @sVersion,
	[VersionsNr] = @sVersionsNr,
	[Versionsdatum] = @daVersionsdatum,
	[Erstellt_am] = @daErstellt_am,
	[Mutiert_am] = @daMutiert_am,
	[Mutierer] = @iMutierer,
	[Aktiv] = @bAktiv,
	[SecurityLevelNr] = @iSecurityLevelNr,
	[DocImage] = @blobDocImage
WHERE
	[DokumentNr] = @iDokumentNr
-- Get the Error Code for the statement just executed.
SELECT @iErrorCode=@@ERROR

GO

/****** Object:  StoredProcedure [dbo].[pr_LC_Dokument_Insert]    Script Date: 02.05.2015 09:42:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


---------------------------------------------------------------------------------
-- Stored procedure that will insert 1 row in the table 'LC_Dokument'
-- Gets: @iDokumentNr int
-- Gets: @iKeyValue int
-- Gets: @iDokType int
-- Gets: @iDokumenttypNr int
-- Gets: @sBezeichnung varchar(50)
-- Gets: @sBeschreibung varchar(2048)
-- Gets: @sFilename varchar(255)
-- Gets: @sOriginalFilename_incl_Path varchar(255)
-- Gets: @sVersion varchar(50)
-- Gets: @sVersionsNr varchar(50)
-- Gets: @daVersionsdatum datetime
-- Gets: @daErstellt_am datetime
-- Gets: @daMutiert_am datetime
-- Gets: @iMutierer int
-- Gets: @bAktiv bit
-- Gets: @iSecurityLevelNr int
-- Gets: @blobDocImage image
-- Returns: @iErrorCode int
---------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[pr_LC_Dokument_Insert]
	@iDokumentNr int,
	@iKeyValue int,
	@iDokType int,
	@iDokumenttypNr int,
	@sBezeichnung varchar(50),
	@sBeschreibung varchar(2048),
	@sFilename varchar(255),
	@sOriginalFilename_incl_Path varchar(255),
	@sVersion varchar(50),
	@sVersionsNr varchar(50),
	@daVersionsdatum datetime,
	@daErstellt_am datetime,
	@daMutiert_am datetime,
	@iMutierer int,
	@bAktiv bit,
	@iSecurityLevelNr int,
	@blobDocImage image,
	@iErrorCode int OUTPUT
AS
SET NOCOUNT ON
-- INSERT a new row in the table.
INSERT [dbo].[LC_Dokument]
(
	[DokumentNr],
	[KeyValue],
	[DokType],
	[DokumenttypNr],
	[Bezeichnung],
	[Beschreibung],
	[Filename],
	[OriginalFilename_incl_Path],
	[Version],
	[VersionsNr],
	[Versionsdatum],
	[Erstellt_am],
	[Mutiert_am],
	[Mutierer],
	[Aktiv],
	[SecurityLevelNr],
	[DocImage]
)
VALUES
(
	@iDokumentNr,
	@iKeyValue,
	@iDokType,
	@iDokumenttypNr,
	@sBezeichnung,
	@sBeschreibung,
	@sFilename,
	@sOriginalFilename_incl_Path,
	@sVersion,
	@sVersionsNr,
	@daVersionsdatum,
	@daErstellt_am,
	@daMutiert_am,
	@iMutierer,
	@bAktiv,
	@iSecurityLevelNr,
	@blobDocImage
)
-- Get the Error Code for the statement just executed.
SELECT @iErrorCode=@@ERROR

GO

/****** Object:  StoredProcedure [dbo].[pr_LC_Produkt_Phase_SelectAll]    Script Date: 02.05.2015 09:42:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


---------------------------------------------------------------------------------
-- Stored procedure that will select all rows from the table 'LC_Produkt_Phase'
-- Returns: @iErrorCode int
---------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[pr_LC_Produkt_Phase_SelectAll]
	@iErrorCode int OUTPUT
AS
SET NOCOUNT ON
-- SELECT all rows from the table.
SELECT
	[LC_Produkt_PhaseNr],
	[LC_ProduktNr],
	[LC_PhaseNr],
	[Datum],
	[Bemerkung],
	[Erstellt_am],
	[Mutiert_am],
	[Mutierer],
	[Aktiv]
FROM [dbo].[LC_Produkt_Phase]
ORDER BY 
	[LC_Produkt_PhaseNr] ASC
-- Get the Error Code for the statement just executed.
SELECT @iErrorCode=@@ERROR

GO

/****** Object:  StoredProcedure [dbo].[pr_LC_Produkt_Phase_SelectOne]    Script Date: 02.05.2015 09:42:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


---------------------------------------------------------------------------------
-- Stored procedure that will select an existing row from the table 'LC_Produkt_Phase'
-- based on the Primary Key.
-- Gets: @iLC_Produkt_PhaseNr int
-- Returns: @iErrorCode int
---------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[pr_LC_Produkt_Phase_SelectOne]
	@iLC_Produkt_PhaseNr int,
	@iErrorCode int OUTPUT
AS
SET NOCOUNT ON
-- SELECT an existing row from the table.
SELECT
	[LC_Produkt_PhaseNr],
	[LC_ProduktNr],
	[LC_PhaseNr],
	[Datum],
	[Bemerkung],
	[Erstellt_am],
	[Mutiert_am],
	[Mutierer],
	[Aktiv]
FROM [dbo].[LC_Produkt_Phase]
WHERE
	[LC_Produkt_PhaseNr] = @iLC_Produkt_PhaseNr
-- Get the Error Code for the statement just executed.
SELECT @iErrorCode=@@ERROR

GO

/****** Object:  StoredProcedure [dbo].[pr_LC_Produkt_Phase_Delete]    Script Date: 02.05.2015 09:42:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


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

GO

/****** Object:  StoredProcedure [dbo].[pr_LC_Produkt_Phase_Update]    Script Date: 02.05.2015 09:42:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


---------------------------------------------------------------------------------
-- Stored procedure that will update an existing row in the table 'LC_Produkt_Phase'
-- Gets: @iLC_Produkt_PhaseNr int
-- Gets: @iLC_ProduktNr int
-- Gets: @iLC_PhaseNr int
-- Gets: @daDatum datetime
-- Gets: @sBemerkung varchar(255)
-- Gets: @daErstellt_am datetime
-- Gets: @daMutiert_am datetime
-- Gets: @iMutierer int
-- Gets: @bAktiv bit
-- Returns: @iErrorCode int
---------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[pr_LC_Produkt_Phase_Update]
	@iLC_Produkt_PhaseNr int,
	@iLC_ProduktNr int,
	@iLC_PhaseNr int,
	@daDatum datetime,
	@sBemerkung varchar(255),
	@daErstellt_am datetime,
	@daMutiert_am datetime,
	@iMutierer int,
	@bAktiv bit,
	@iErrorCode int OUTPUT
AS
SET NOCOUNT ON
-- UPDATE an existing row in the table.
UPDATE [dbo].[LC_Produkt_Phase]
SET 
	[LC_ProduktNr] = @iLC_ProduktNr,
	[LC_PhaseNr] = @iLC_PhaseNr,
	[Datum] = @daDatum,
	[Bemerkung] = @sBemerkung,
	[Erstellt_am] = @daErstellt_am,
	[Mutiert_am] = @daMutiert_am,
	[Mutierer] = @iMutierer,
	[Aktiv] = @bAktiv
WHERE
	[LC_Produkt_PhaseNr] = @iLC_Produkt_PhaseNr
-- Get the Error Code for the statement just executed.
SELECT @iErrorCode=@@ERROR

GO

/****** Object:  StoredProcedure [dbo].[pr_LC_Produkt_Phase_Insert]    Script Date: 02.05.2015 09:42:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


---------------------------------------------------------------------------------
-- Stored procedure that will insert 1 row in the table 'LC_Produkt_Phase'
-- Gets: @iLC_ProduktNr int
-- Gets: @iLC_PhaseNr int
-- Gets: @daDatum datetime
-- Gets: @sBemerkung varchar(255)
-- Gets: @daErstellt_am datetime
-- Gets: @daMutiert_am datetime
-- Gets: @iMutierer int
-- Gets: @bAktiv bit
-- Returns: @iLC_Produkt_PhaseNr int
-- Returns: @iErrorCode int
---------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[pr_LC_Produkt_Phase_Insert]
	@iLC_ProduktNr int,
	@iLC_PhaseNr int,
	@daDatum datetime,
	@sBemerkung varchar(255),
	@daErstellt_am datetime,
	@daMutiert_am datetime,
	@iMutierer int,
	@bAktiv bit,
	@iLC_Produkt_PhaseNr int OUTPUT,
	@iErrorCode int OUTPUT
AS
SET NOCOUNT ON
-- INSERT a new row in the table.
INSERT [dbo].[LC_Produkt_Phase]
(
	[LC_ProduktNr],
	[LC_PhaseNr],
	[Datum],
	[Bemerkung],
	[Erstellt_am],
	[Mutiert_am],
	[Mutierer],
	[Aktiv]
)
VALUES
(
	@iLC_ProduktNr,
	@iLC_PhaseNr,
	@daDatum,
	@sBemerkung,
	@daErstellt_am,
	@daMutiert_am,
	@iMutierer,
	@bAktiv
)
-- Get the Error Code for the statement just executed.
SELECT @iErrorCode=@@ERROR
-- Get the IDENTITY value for the row just inserted.
SELECT @iLC_Produkt_PhaseNr=SCOPE_IDENTITY()

GO

/****** Object:  StoredProcedure [dbo].[pr_LC_Produkt_Dokument_SelectAll]    Script Date: 02.05.2015 09:42:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


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

GO

/****** Object:  StoredProcedure [dbo].[pr_LC_Produkt_Dokument_SelectOne]    Script Date: 02.05.2015 09:42:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


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

GO

/****** Object:  StoredProcedure [dbo].[pr_LC_Produkt_Dokument_Delete]    Script Date: 02.05.2015 09:42:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


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

GO

/****** Object:  StoredProcedure [dbo].[pr_LC_Produkt_Dokument_Update]    Script Date: 02.05.2015 09:42:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


---------------------------------------------------------------------------------
-- Stored procedure that will update an existing row in the table 'LC_Produkt_Dokument'
-- Gets: @iLC_Produkt_DokumentNr int
-- Gets: @iLC_ProduktNr int
-- Gets: @sBezeichnung varchar(50)
-- Gets: @sbeschreibung varchar(255)
-- Gets: @daErstellt_am datetime
-- Gets: @daMutiert_am datetime
-- Gets: @iMutierer int
-- Gets: @bAktiv bit
-- Gets: @blobDokument image
-- Returns: @iErrorCode int
---------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[pr_LC_Produkt_Dokument_Update]
	@iLC_Produkt_DokumentNr int,
	@iLC_ProduktNr int,
	@sBezeichnung varchar(50),
	@sbeschreibung varchar(255),
	@daErstellt_am datetime,
	@daMutiert_am datetime,
	@iMutierer int,
	@bAktiv bit,
	@blobDokument image,
	@iErrorCode int OUTPUT
AS
SET NOCOUNT ON
-- UPDATE an existing row in the table.
UPDATE [dbo].[LC_Produkt_Dokument]
SET 
	[LC_ProduktNr] = @iLC_ProduktNr,
	[Bezeichnung] = @sBezeichnung,
	[beschreibung] = @sbeschreibung,
	[Erstellt_am] = @daErstellt_am,
	[Mutiert_am] = @daMutiert_am,
	[Mutierer] = @iMutierer,
	[Aktiv] = @bAktiv,
	[Dokument] = @blobDokument
WHERE
	[LC_Produkt_DokumentNr] = @iLC_Produkt_DokumentNr
-- Get the Error Code for the statement just executed.
SELECT @iErrorCode=@@ERROR

GO

/****** Object:  StoredProcedure [dbo].[pr_LC_Produkt_Dokument_Insert]    Script Date: 02.05.2015 09:42:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


---------------------------------------------------------------------------------
-- Stored procedure that will insert 1 row in the table 'LC_Produkt_Dokument'
-- Gets: @iLC_ProduktNr int
-- Gets: @sBezeichnung varchar(50)
-- Gets: @sbeschreibung varchar(255)
-- Gets: @daErstellt_am datetime
-- Gets: @daMutiert_am datetime
-- Gets: @iMutierer int
-- Gets: @bAktiv bit
-- Gets: @blobDokument image
-- Returns: @iLC_Produkt_DokumentNr int
-- Returns: @iErrorCode int
---------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[pr_LC_Produkt_Dokument_Insert]
	@iLC_ProduktNr int,
	@sBezeichnung varchar(50),
	@sbeschreibung varchar(255),
	@daErstellt_am datetime,
	@daMutiert_am datetime,
	@iMutierer int,
	@bAktiv bit,
	@blobDokument image,
	@iLC_Produkt_DokumentNr int OUTPUT,
	@iErrorCode int OUTPUT
AS
SET NOCOUNT ON
-- INSERT a new row in the table.
INSERT [dbo].[LC_Produkt_Dokument]
(
	[LC_ProduktNr],
	[Bezeichnung],
	[beschreibung],
	[Erstellt_am],
	[Mutiert_am],
	[Mutierer],
	[Aktiv],
	[Dokument]
)
VALUES
(
	@iLC_ProduktNr,
	@sBezeichnung,
	@sbeschreibung,
	@daErstellt_am,
	@daMutiert_am,
	@iMutierer,
	@bAktiv,
	@blobDokument
)
-- Get the Error Code for the statement just executed.
SELECT @iErrorCode=@@ERROR
-- Get the IDENTITY value for the row just inserted.
SELECT @iLC_Produkt_DokumentNr=SCOPE_IDENTITY()

GO

/****** Object:  StoredProcedure [dbo].[pr_LC_Einsatzgebiet_SelectAll]    Script Date: 02.05.2015 09:42:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


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

GO

/****** Object:  StoredProcedure [dbo].[pr_LC_Einsatzgebiet_SelectOne]    Script Date: 02.05.2015 09:42:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


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

GO

/****** Object:  StoredProcedure [dbo].[pr_LC_Einsatzgebiet_Delete]    Script Date: 02.05.2015 09:42:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


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

GO

/****** Object:  StoredProcedure [dbo].[pr_LC_Einsatzgebiet_Update]    Script Date: 02.05.2015 09:42:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


---------------------------------------------------------------------------------
-- Stored procedure that will update an existing row in the table 'LC_Einsatzgebiet'
-- Gets: @iLC_EinsatzgebietNr int
-- Gets: @iLC_EinsatzgebietNr_Parent int
-- Gets: @sBezeichnung varchar(50)
-- Gets: @sBeschreibung varchar(255)
-- Gets: @daErstllt_am datetime
-- Gets: @daMutiert_am datetime
-- Gets: @iMutierer int
-- Gets: @bAktiv bit
-- Gets: @iImagenr int
-- Returns: @iErrorCode int
---------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[pr_LC_Einsatzgebiet_Update]
	@iLC_EinsatzgebietNr int,
	@iLC_EinsatzgebietNr_Parent int,
	@sBezeichnung varchar(50),
	@sBeschreibung varchar(255),
	@daErstllt_am datetime,
	@daMutiert_am datetime,
	@iMutierer int,
	@bAktiv bit,
	@iImagenr int,
	@iErrorCode int OUTPUT
AS
SET NOCOUNT ON
-- UPDATE an existing row in the table.
UPDATE [dbo].[LC_Einsatzgebiet]
SET 
	[LC_EinsatzgebietNr_Parent] = @iLC_EinsatzgebietNr_Parent,
	[Bezeichnung] = @sBezeichnung,
	[Beschreibung] = @sBeschreibung,
	[Erstllt_am] = @daErstllt_am,
	[Mutiert_am] = @daMutiert_am,
	[Mutierer] = @iMutierer,
	[Aktiv] = @bAktiv,
	[Imagenr] = @iImagenr
WHERE
	[LC_EinsatzgebietNr] = @iLC_EinsatzgebietNr
-- Get the Error Code for the statement just executed.
SELECT @iErrorCode=@@ERROR

GO

/****** Object:  StoredProcedure [dbo].[pr_LC_Einsatzgebiet_Insert]    Script Date: 02.05.2015 09:42:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


---------------------------------------------------------------------------------
-- Stored procedure that will insert 1 row in the table 'LC_Einsatzgebiet'
-- Gets: @iLC_EinsatzgebietNr_Parent int
-- Gets: @sBezeichnung varchar(50)
-- Gets: @sBeschreibung varchar(255)
-- Gets: @daErstllt_am datetime
-- Gets: @daMutiert_am datetime
-- Gets: @iMutierer int
-- Gets: @bAktiv bit
-- Gets: @iImagenr int
-- Returns: @iLC_EinsatzgebietNr int
-- Returns: @iErrorCode int
---------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[pr_LC_Einsatzgebiet_Insert]
	@iLC_EinsatzgebietNr_Parent int,
	@sBezeichnung varchar(50),
	@sBeschreibung varchar(255),
	@daErstllt_am datetime,
	@daMutiert_am datetime,
	@iMutierer int,
	@bAktiv bit,
	@iImagenr int,
	@iLC_EinsatzgebietNr int OUTPUT,
	@iErrorCode int OUTPUT
AS
SET NOCOUNT ON
-- INSERT a new row in the table.
INSERT [dbo].[LC_Einsatzgebiet]
(
	[LC_EinsatzgebietNr_Parent],
	[Bezeichnung],
	[Beschreibung],
	[Erstllt_am],
	[Mutiert_am],
	[Mutierer],
	[Aktiv],
	[Imagenr]
)
VALUES
(
	@iLC_EinsatzgebietNr_Parent,
	@sBezeichnung,
	@sBeschreibung,
	@daErstllt_am,
	@daMutiert_am,
	@iMutierer,
	@bAktiv,
	@iImagenr
)
-- Get the Error Code for the statement just executed.
SELECT @iErrorCode=@@ERROR
-- Get the IDENTITY value for the row just inserted.
SELECT @iLC_EinsatzgebietNr=SCOPE_IDENTITY()

GO

/****** Object:  StoredProcedure [dbo].[sp_lc_get_Struktur_Auswahl]    Script Date: 02.05.2015 09:42:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_lc_get_Struktur_Auswahl]
@selectiontype AS integer
AS 
    BEGIN
        DECLARE @tmp1 TABLE
            (
              id INT ,
              parentid INT ,
              bezeichnung VARCHAR(255) ,
              imageindex INT ,
              beschreibung VARCHAR(1024) ,
              IstStruktur BIT ,
              verweisnr INT ,
              copy INT ,
              originalid INT,
			  eingesetzt int
            )

        SET NOCOUNT ON;
-- Aktualisierung Produktestamm aus Applikation
        UPDATE  lc_produkt
        SET     bezeichnung = ( SELECT  bezeichnung
                                FROM    applikation
                                WHERE   verweisnr = applikation.applikationnr
                              )
        WHERE   verweisnr > 0 
        UPDATE  lc_produkt
        SET     mutiert_am = GETDATE() ,
                aktiv = 0
        WHERE   verweisnr > 0
                AND verweisnr NOT IN ( SELECT   applikationnr
                                       FROM     applikation )

 
  
-- Alle Produkte

        INSERT  @tmp1
                SELECT  lc_produktnr AS id ,
                        lc_produktnr_parent AS parentid ,
                        bezeichnung ,
                        imageindex AS imageindex ,
                        ISNULL(Beschreibung, '') AS Beschreibung ,
                        IstStruktur ,
                        ISNULL(verweisnr, 0) AS Verweisnr ,
                        0 AS copy ,
                        LC_ProduktNr,
						ISNULL(StatusNr,0)
                FROM    dbo.LC_Produkt
                WHERE   aktiv = 1

        INSERT  @tmp1
                ( id ,
                  parentid ,
                  bezeichnung ,
                  imageindex ,
                  beschreibung ,
                  IstStruktur ,
                  verweisnr ,
                  copy,
				  eingesetzt
                )
        VALUES  ( 0 ,
                  NULL ,
                  'Root' ,
                  1 ,
                  '' ,
                  1 ,
                  0 ,
                  0,
				  0
                )

        UPDATE  @tmp1
        SET     imageindex = imageindex + 0
        WHERE   id IN ( SELECT  lc_produktnr
                        FROM    lc_produkt
                        WHERE   IstStruktur = 1 )

        INSERT  @tmp1
                ( id ,
                  parentid ,
                  bezeichnung ,
                  imageindex ,
                  beschreibung ,
                  IstStruktur ,
                  verweisnr ,
                  copy ,
                  Originalid,
				  eingesetzt
	            )
                SELECT  dbo.LC_Produkt_Copy.lc_produkt_copynr AS id ,
                        dbo.LC_Produkt_Copy.lc_produkt_parentnr AS parentid ,
                        dbo.LC_Produkt.Bezeichnung ,
                        dbo.LC_Produkt.Imageindex ,
                        dbo.LC_Produkt.Beschreibung ,
                        0 ,
                        0 ,
                        1 ,
                        lc_produkt.LC_ProduktNr,
						ISNULL(lc_produkt.StatusNr,0)
                FROM    dbo.LC_Produkt_Copy
                        INNER JOIN dbo.LC_Produkt ON dbo.LC_Produkt_Copy.lc_produktnr = dbo.LC_Produkt.LC_ProduktNr
                WHERE   ( dbo.LC_Produkt_Copy.aktiv = 1 )
                        AND ( dbo.LC_Produkt.Aktiv = 1 )

IF @selectiontype=1 BEGIN
   DELETE FROM @tmp1 WHERE IstStruktur=0 AND eingesetzt=0
   	INSERT @tmp1 
                    ( id ,
                  parentid ,
                  bezeichnung ,
                  imageindex ,
                  beschreibung ,
                  IstStruktur ,
                  verweisnr ,
                  copy ,
                  Originalid,
				  eingesetzt
	            )
               SELECT  lc_produktnr AS id ,
                        lc_produktnr_parent AS parentid ,
                        bezeichnung ,
                        imageindex AS imageindex ,
                        ISNULL(Beschreibung, '') AS Beschreibung ,
                        IstStruktur ,
                        ISNULL(verweisnr, 0) AS Verweisnr ,
                        0 AS copy ,
                        LC_ProduktNr,
						ISNULL(StatusNr,0)
                FROM    dbo.LC_Produkt
                WHERE   aktiv = 1 AND  lc_produktnr NOT IN (SELECT id FROM @tmp1) AND lc_produktnr IN (SELECT parentid FROM @tmp1)

END    
IF @selectiontype=2 BEGIN
DELETE FROM @tmp1 WHERE IstStruktur=0 AND eingesetzt=1
	INSERT @tmp1 
                    ( id ,
                  parentid ,
                  bezeichnung ,
                  imageindex ,
                  beschreibung ,
                  IstStruktur ,
                  verweisnr ,
                  copy ,
                  Originalid,
				  eingesetzt
	            )
               SELECT  lc_produktnr AS id ,
                        lc_produktnr_parent AS parentid ,
                        bezeichnung ,
                        imageindex AS imageindex ,
                        ISNULL(Beschreibung, '') AS Beschreibung ,
                        IstStruktur ,
                        ISNULL(verweisnr, 0) AS Verweisnr ,
                        0 AS copy ,
                        LC_ProduktNr,
						ISNULL(StatusNr,0)
                FROM    dbo.LC_Produkt
                WHERE   aktiv = 1 AND  lc_produktnr NOT IN (SELECT id FROM @tmp1) AND lc_produktnr IN (SELECT parentid FROM @tmp1)
end
    
    SELECT  *
    FROM    @tmp1
    ORDER BY bezeichnung

	end
GO

/****** Object:  StoredProcedure [dbo].[sp_lc_get_Struktur_Einsatzgebiet]    Script Date: 02.05.2015 09:42:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_lc_get_Struktur_Einsatzgebiet]
AS
BEGIN
declare @tmp1 TABLE (id int, parentid int, bezeichnung varchar(255),imageindex int)
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	INSERT  @tmp1
	        SELECT  [LC_EinsatzgebietNr] AS ID
      ,[LC_EinsatzgebietNr_Parent] AS PARENTID
      ,[Bezeichnung] AS BEZEICHNUNG
      ,imagenr AS imageindex
	  
  FROM [dbo].[LC_Einsatzgebiet]
  WHERE    Aktiv = 1

   INSERT   @tmp1 ( id, parentid, bezeichnung, imageindex)
               VALUES   ( 0, NULL, 'Root', 1)


               SELECT   *
               FROM     @tmp1
               ORDER BY bezeichnung


END

GO