Samuele E. Locatelli
362 lines
18 KiB
Transact-SQL
362 lines
18 KiB
Transact-SQL
|
|
/****** 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
|
|
|