/****** Object:  Table [dbo].[TabAttrezzature]    Script Date: 11/12/2018 18:21:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TabAttrezzature](
	[CodAttr] [nvarchar](50) NOT NULL,
	[CodArt_A] [nvarchar](50) NOT NULL,
	[CodArt_B] [nvarchar](50) NOT NULL,
	[PartProgMain] [nvarchar](250) NOT NULL,
	[PartProgAux] [nvarchar](250) NOT NULL,
 CONSTRAINT [PK_TabAttrezzature] PRIMARY KEY CLUSTERED 
(
	[CodAttr] ASC,
	[CodArt_A] ASC,
	[CodArt_B] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TabAttrezzature] ADD  CONSTRAINT [DF_TabAttrezzature_CodArt_A1_1]  DEFAULT (N'ND') FOR [CodAttr]
GO
ALTER TABLE [dbo].[TabAttrezzature] ADD  CONSTRAINT [DF_TabAttrezzature_CodArt_A]  DEFAULT (N'ONLY') FOR [CodArt_A]
GO
ALTER TABLE [dbo].[TabAttrezzature] ADD  CONSTRAINT [DF_TabAttrezzature_CodArt_A1]  DEFAULT (N'ONLY') FOR [CodArt_B]
GO
ALTER TABLE [dbo].[TabAttrezzature] ADD  CONSTRAINT [DF_TabAttrezzature_PartProgMain]  DEFAULT ('') FOR [PartProgMain]
GO
ALTER TABLE [dbo].[TabAttrezzature] ADD  CONSTRAINT [DF_TabAttrezzature_PartProgAux]  DEFAULT ('') FOR [PartProgAux]
GO




-- =============================================
-- Author:		S.E.Locatelli
-- Create date: 2018.11.05
-- Description:	elimina articolo (e programmi)
-- =============================================
alter PROCEDURE [dbo].[stp_AnagArt_deleteQuery]
(
	@CodArt		NVARCHAR(50)	= ''	-- Codice Articolo
)
AS
BEGIN
	
	SET NOCOUNT ON;

	DELETE
	FROM	AnagArticoli	
	WHERE	CodArt			= @CodArt
		
END
GO

/****** Object:  StoredProcedure [dbo].[stp_AnagArt_insertQuery]    Script Date: 11/12/2018 18:21:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		S.E.Locatelli
-- Create date: 2018.11.05
-- Description:	inserisce articolo (e programmi)
-- =============================================
alter PROCEDURE [dbo].[stp_AnagArt_insertQuery]
(
	@CodArt			NVARCHAR(50)	= ''	-- Codice Articolo
	,@Descrizione	NVARCHAR(250)	= ''
)
AS
BEGIN
	
	SET NOCOUNT ON;

	INSERT INTO	AnagArticoli(CodArt, Descrizione)
	VALUES(@CodArt, @Descrizione)
		
END
GO

/****** Object:  StoredProcedure [dbo].[stp_AnagArt_search]    Script Date: 11/12/2018 18:21:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		S.E.Locatelli
-- Create date: 2018.11.05
-- Description:	cerca articolo (e programmi)
-- =============================================
alter PROCEDURE [dbo].[stp_AnagArt_search]
(
	@CodArt		NVARCHAR(50)	= ''	-- Codice Articolo
)
AS
BEGIN
	
	SET NOCOUNT ON;

	SELECT	*
	FROM	AnagArticoli	
	WHERE	CodArt			= @CodArt
		
END
GO


/****** Object:  StoredProcedure [dbo].[stp_AnagArt_updateQuery]    Script Date: 11/12/2018 18:21:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		S.E.Locatelli
-- Create date: 2018.11.05
-- Description:	update articolo (e programmi)
-- =============================================
alter PROCEDURE [dbo].[stp_AnagArt_updateQuery]
(
	@Original_CodArt	NVARCHAR(50)	= ''
	,@CodArt			NVARCHAR(50)	= ''
	,@Descrizione		NVARCHAR(250)	= ''
)
AS
BEGIN
	
	SET NOCOUNT ON;

	UPDATE	AnagArticoli
		SET	CodArt			= @CodArt
		   ,Descrizione		= @Descrizione
	WHERE	CodArt			= @Original_CodArt
		
END
GO

/****** Object:  StoredProcedure [dbo].[stp_AnagOpr_insertQuery]    Script Date: 11/12/2018 18:21:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		S.E.Locatelli
-- Create date: 2018.11.05
-- Description:	insert operatore
-- =============================================
alter PROCEDURE [dbo].[stp_AnagOpr_insertQuery]
(
	 @Barcode	NVARCHAR(50)	= ''
	,@Cognome	NVARCHAR(50)	= ''
	,@Nome		NVARCHAR(50)	= ''
	,@Password	NVARCHAR(50)	= ''
	,@Livello	NVARCHAR(50)	= ''
)
AS
BEGIN
	
	SET NOCOUNT ON;
	INSERT INTO AnagOperatori(Barcode, Cognome, Nome, [Password], Livello)
	VALUES		(@Barcode, @Cognome, @Nome, @Password, @Livello)
		
END
GO


/****** Object:  StoredProcedure [dbo].[stp_AnagOpr_search]    Script Date: 11/12/2018 18:21:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		S.E.Locatelli
-- Create date: 2018.11.05
-- Description:	cerca operatore
-- =============================================
alter PROCEDURE [dbo].[stp_AnagOpr_search]
(
	@Barcode	NVARCHAR(50)	= ''
)
AS
BEGIN
	
	SET NOCOUNT ON;

	SELECT	*
	FROM	AnagOperatori	
	WHERE	Barcode			= @Barcode
		
END
GO


/****** Object:  StoredProcedure [dbo].[stp_AnagOpr_updateQuery]    Script Date: 11/12/2018 18:21:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		S.E.Locatelli
-- Create date: 2018.11.05
-- Description:	update operatore
-- =============================================
alter PROCEDURE [dbo].[stp_AnagOpr_updateQuery]
(
	@Original_Barcode	NVARCHAR(50)	= ''
	,@Barcode	NVARCHAR(50)	= ''
	,@Cognome	NVARCHAR(50)	= ''
	,@Nome		NVARCHAR(50)	= ''
	,@Password	NVARCHAR(50)	= ''
	,@Livello	NVARCHAR(50)	= ''
)
AS
BEGIN
	
	SET NOCOUNT ON;

	UPDATE	AnagOperatori
		SET	Barcode		= @Barcode
			,Cognome	= @Cognome
			,Nome		= @Nome
			,[Password]	= @Password
			,Livello	= @Livello
	WHERE	Barcode		= @Original_Barcode
		
END
GO


/****** Object:  StoredProcedure [dbo].[stp_TabAttr_deleteQuery]    Script Date: 11/12/2018 18:21:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		S.E.Locatelli
-- Create date: 2018.12.11
-- Description:	elimino riga tabella attrezzature (e programmi)
-- =============================================
create PROCEDURE [dbo].[stp_TabAttr_deleteQuery]
(
	 @CodAttr		NVARCHAR(50)	= ''	-- Codice Attrezzatura
	,@CodArt_A		NVARCHAR(50)	= ''	-- Codice Articolo A ('*' per tutti, ONLY per "solo altro")
	,@CodArt_B		NVARCHAR(50)	= ''	-- Codice Articolo B ('*' per tutti, ONLY per "solo altro")
)
AS
BEGIN
	
	SET NOCOUNT ON;

	DELETE
	FROM	TabAttrezzature	
	WHERE	CodAttr		= @CodAttr
		AND CodArt_A	= @CodArt_A
		AND CodArt_B	= @CodArt_B
		
END
GO
/****** Object:  StoredProcedure [dbo].[stp_TabAttr_insertQuery]    Script Date: 11/12/2018 18:21:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		S.E.Locatelli
-- Create date: 2018.12.11
-- Description:	insert riga tabella attrezzature (e programmi)
-- =============================================
create PROCEDURE [dbo].[stp_TabAttr_insertQuery]
(
	 @CodAttr		NVARCHAR(50)	= ''	-- Codice Attrezzatura
	,@CodArt_A		NVARCHAR(50)	= ''	-- Codice Articolo A
	,@CodArt_B		NVARCHAR(50)	= ''	-- Codice Articolo B
	,@PartProgMain	NVARCHAR(250)	= ''	-- PartProgram principale
	,@PartProgAux	NVARCHAR(250)	= ''	-- PartProgram ausiliario
)
AS
BEGIN
	
	SET NOCOUNT ON;
	INSERT INTO	TabAttrezzature(CodAttr, CodArt_A, CodArt_B, PartProgMain, PartProgAux)
	VALUES(@CodAttr, @CodArt_A, @CodArt_B, @PartProgMain, @PartProgAux)
		
END
GO
/****** Object:  StoredProcedure [dbo].[stp_TabAttr_search]    Script Date: 11/12/2018 18:21:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		S.E.Locatelli
-- Create date: 2018.12.11
-- Description:	cerca tabella attrezzature (e programmi)
-- =============================================
create PROCEDURE [dbo].[stp_TabAttr_search]
(
	 @CodAttr		NVARCHAR(50)	= ''	-- Codice Attrezzatura
	,@CodArt_A		NVARCHAR(50)	= ''	-- Codice Articolo A ('*' per tutti, ONLY per "solo altro")
	,@CodArt_B		NVARCHAR(50)	= ''	-- Codice Articolo B ('*' per tutti, ONLY per "solo altro")
)
AS
BEGIN
	
	SET NOCOUNT ON;

	SELECT	*
	FROM	TabAttrezzature	
	WHERE	CodAttr	= @CodAttr
		AND ((CodArt_A = @CodArt_A) OR @CodArt_A = '*')
		AND ((CodArt_B = @CodArt_B) OR @CodArt_B = '*')
		
END
GO
/****** Object:  StoredProcedure [dbo].[stp_TabAttr_updateQuery]    Script Date: 11/12/2018 18:21:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		S.E.Locatelli
-- Create date: 2018.12.11
-- Description:	insert riga tabella attrezzature (e programmi)
-- =============================================
create PROCEDURE [dbo].[stp_TabAttr_updateQuery]
(
	 @Original_CodAttr	NVARCHAR(50)	= ''
	,@CodAttr			NVARCHAR(50)	= ''	-- Codice Attrezzatura
	,@CodArt_A			NVARCHAR(50)	= ''	-- Codice Articolo A
	,@CodArt_B			NVARCHAR(50)	= ''	-- Codice Articolo B
	,@PartProgMain		NVARCHAR(250)	= ''	-- PartProgram principale
	,@PartProgAux		NVARCHAR(250)	= ''	-- PartProgram ausiliario
)
AS
BEGIN
	
	SET NOCOUNT ON;

	
	UPDATE	TabAttrezzature
		SET	CodAttr			= @CodAttr
		   ,CodArt_A		= @CodArt_A
		   ,CodArt_B		= @CodArt_B
		   ,PartProgMain	= @PartProgMain
		   ,PartProgAux		= @PartProgAux
	WHERE	CodAttr			= @Original_CodAttr
			
END
GO