USE [master]
GO
/****** Object:  Database [CMS-OMR]    Script Date: 09/11/2018 15:13:41 ******/
CREATE DATABASE [CMS-OMR]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'CMS-OMR', FILENAME = N'G:\SQL2016Data\CMS-OMR.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'CMS-OMR_log', FILENAME = N'F:\SQL2016Log\CMS-OMR_log.ldf' , SIZE = 73728KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
GO
ALTER DATABASE [CMS-OMR] SET COMPATIBILITY_LEVEL = 110
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [CMS-OMR].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [CMS-OMR] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [CMS-OMR] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [CMS-OMR] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [CMS-OMR] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [CMS-OMR] SET ARITHABORT OFF 
GO
ALTER DATABASE [CMS-OMR] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [CMS-OMR] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [CMS-OMR] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [CMS-OMR] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [CMS-OMR] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [CMS-OMR] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [CMS-OMR] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [CMS-OMR] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [CMS-OMR] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [CMS-OMR] SET  DISABLE_BROKER 
GO
ALTER DATABASE [CMS-OMR] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [CMS-OMR] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [CMS-OMR] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [CMS-OMR] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [CMS-OMR] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [CMS-OMR] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [CMS-OMR] SET HONOR_BROKER_PRIORITY OFF 
GO
ALTER DATABASE [CMS-OMR] SET RECOVERY FULL 
GO
ALTER DATABASE [CMS-OMR] SET  MULTI_USER 
GO
ALTER DATABASE [CMS-OMR] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [CMS-OMR] SET DB_CHAINING OFF 
GO
ALTER DATABASE [CMS-OMR] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
GO
ALTER DATABASE [CMS-OMR] SET TARGET_RECOVERY_TIME = 60 SECONDS 
GO
ALTER DATABASE [CMS-OMR] SET DELAYED_DURABILITY = DISABLED 
GO
EXEC sys.sp_db_vardecimal_storage_format N'CMS-OMR', N'ON'
GO
ALTER DATABASE [CMS-OMR] SET QUERY_STORE = OFF
GO
USE [CMS-OMR]
GO
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = OFF;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = PRIMARY;
GO
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = PRIMARY;
GO
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = ON;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = PRIMARY;
GO
ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = OFF;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET QUERY_OPTIMIZER_HOTFIXES = PRIMARY;
GO
USE [CMS-OMR]
GO
/****** Object:  Table [dbo].[AnagAllarmi]    Script Date: 09/11/2018 15:13:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AnagAllarmi](
	[CodAllarme] [nvarchar](50) NOT NULL,
	[Descrizione] [nvarchar](250) NOT NULL,
	[ReportAttivo] [bit] NOT NULL,
	[LastStart] [datetime] NOT NULL,
	[LastEnd] [datetime] NOT NULL,
	[CurrStatus] [bit] NOT NULL,
 CONSTRAINT [PK_AnagAllarmi] PRIMARY KEY CLUSTERED 
(
	[CodAllarme] 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
/****** Object:  Table [dbo].[StoricoAllarmi]    Script Date: 09/11/2018 15:13:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[StoricoAllarmi](
	[DtEvento] [datetime] NOT NULL,
	[CodAllarme] [nvarchar](50) NOT NULL,
	[Attivo] [bit] NOT NULL,
 CONSTRAINT [PK_StoricoAllarmi] PRIMARY KEY CLUSTERED 
(
	[DtEvento] ASC,
	[CodAllarme] 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
/****** Object:  View [dbo].[vStoricoAllarmiExp]    Script Date: 09/11/2018 15:13:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vStoricoAllarmiExp]
AS
SELECT        dbo.StoricoAllarmi.*, dbo.AnagAllarmi.Descrizione
FROM            dbo.AnagAllarmi INNER JOIN
                         dbo.StoricoAllarmi ON dbo.AnagAllarmi.CodAllarme = dbo.StoricoAllarmi.CodAllarme
GO
/****** Object:  UserDefinedFunction [dbo].[SplitStrings]    Script Date: 09/11/2018 15:13:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		S.E.Locatelli
-- Create date: 2018.11.09
-- Description:	Splitter stringhe
-- rif https://sqlperformance.com/2012/07/t-sql-queries/split-strings
-- =============================================
CREATE FUNCTION [dbo].[SplitStrings]
(
   @List NVARCHAR(MAX),
   @Delimiter NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING AS
RETURN
  WITH E1(N)        AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 
                         UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 
                         UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),
       E2(N)        AS (SELECT 1 FROM E1 a, E1 b),
       E4(N)        AS (SELECT 1 FROM E2 a, E2 b),
       E42(N)       AS (SELECT 1 FROM E4 a, E2 b),
       cteTally(N)  AS (SELECT 0 UNION ALL SELECT TOP (DATALENGTH(ISNULL(@List,1))) 
                         ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E42),
       cteStart(N1) AS (SELECT t.N+1 FROM cteTally t
                         WHERE (SUBSTRING(@List,t.N,1) = @Delimiter OR t.N = 0))
  SELECT Item = SUBSTRING(@List, s.N1, ISNULL(NULLIF(CHARINDEX(@Delimiter,@List,s.N1),0)-s.N1,8000))
    FROM cteStart s;
GO
/****** Object:  Table [dbo].[AnagArticoli]    Script Date: 09/11/2018 15:13:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AnagArticoli](
	[CodArt] [nvarchar](50) NOT NULL,
	[PartProgMain] [nvarchar](250) NOT NULL,
	[PartProgAux] [nvarchar](250) NOT NULL,
 CONSTRAINT [PK_AnagArticoli] PRIMARY KEY CLUSTERED 
(
	[CodArt] 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
/****** Object:  Table [dbo].[AnagOperatori]    Script Date: 09/11/2018 15:13:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AnagOperatori](
	[Barcode] [nvarchar](50) NOT NULL,
	[Cognome] [nvarchar](50) NOT NULL,
	[Nome] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_AnagOperatori] PRIMARY KEY CLUSTERED 
(
	[Barcode] 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
/****** Object:  Table [dbo].[TabOperazioni]    Script Date: 09/11/2018 15:13:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TabOperazioni](
	[IdxRec] [int] IDENTITY(1,1) NOT NULL,
	[DtEvento] [datetime] NOT NULL,
	[CodStazione] [nvarchar](50) NOT NULL,
	[CodSottostaz] [nvarchar](50) NOT NULL,
	[CodOperaz] [nvarchar](50) NOT NULL,
	[CodStato] [nvarchar](50) NOT NULL,
	[Datamatrix] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_TabOperazioni] PRIMARY KEY CLUSTERED 
(
	[IdxRec] 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
/****** Object:  Table [dbo].[TabPezzi]    Script Date: 09/11/2018 15:13:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TabPezzi](
	[Datamatrix] [nvarchar](50) NOT NULL,
	[CodArt] [nvarchar](50) NOT NULL,
	[CodStazione] [nvarchar](50) NOT NULL,
	[CodStato] [nvarchar](50) NOT NULL,
	[CodOperazDone] [nvarchar](50) NOT NULL,
	[CodOperazCurr] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_TabPezzi] PRIMARY KEY CLUSTERED 
(
	[Datamatrix] 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].[AnagAllarmi] ADD  CONSTRAINT [DF_AnagAllarmi_Descrizione]  DEFAULT ('') FOR [Descrizione]
GO
ALTER TABLE [dbo].[AnagAllarmi] ADD  CONSTRAINT [DF_AnagAllarmi_Attivo]  DEFAULT ((1)) FOR [ReportAttivo]
GO
ALTER TABLE [dbo].[AnagAllarmi] ADD  CONSTRAINT [DF_AnagAllarmi_LastStart]  DEFAULT (getdate()) FOR [LastStart]
GO
ALTER TABLE [dbo].[AnagAllarmi] ADD  CONSTRAINT [DF_AnagAllarmi_LastEnd]  DEFAULT (getdate()) FOR [LastEnd]
GO
ALTER TABLE [dbo].[AnagAllarmi] ADD  CONSTRAINT [DF_AnagAllarmi_CurrStatus]  DEFAULT ((0)) FOR [CurrStatus]
GO
ALTER TABLE [dbo].[AnagArticoli] ADD  CONSTRAINT [DF_AnagArticoli_CodPartProgram]  DEFAULT ('') FOR [PartProgMain]
GO
ALTER TABLE [dbo].[AnagArticoli] ADD  CONSTRAINT [DF_AnagArticoli_PartProgramMain1]  DEFAULT ('') FOR [PartProgAux]
GO
ALTER TABLE [dbo].[AnagOperatori] ADD  CONSTRAINT [DF_AnagOperatori_Cognome]  DEFAULT (N'Cognome') FOR [Cognome]
GO
ALTER TABLE [dbo].[AnagOperatori] ADD  CONSTRAINT [DF_AnagOperatori_Nome]  DEFAULT (N'Nome') FOR [Nome]
GO
ALTER TABLE [dbo].[StoricoAllarmi] ADD  CONSTRAINT [DF_StoricoAllarmi_Attivo]  DEFAULT ((1)) FOR [Attivo]
GO
ALTER TABLE [dbo].[TabOperazioni] ADD  CONSTRAINT [DF_TabOperazioni_DtOpr]  DEFAULT (getdate()) FOR [DtEvento]
GO
ALTER TABLE [dbo].[TabOperazioni] ADD  CONSTRAINT [DF_TabOperazioni_CodStazione]  DEFAULT ('') FOR [CodStazione]
GO
ALTER TABLE [dbo].[TabOperazioni] ADD  CONSTRAINT [DF_TabOperazioni_CodSottostaz]  DEFAULT ('') FOR [CodSottostaz]
GO
ALTER TABLE [dbo].[TabOperazioni] ADD  CONSTRAINT [DF_TabOperazioni_CodOperaz]  DEFAULT ('') FOR [CodOperaz]
GO
ALTER TABLE [dbo].[TabOperazioni] ADD  CONSTRAINT [DF_TabOperazioni_IdxStato]  DEFAULT ('') FOR [CodStato]
GO
ALTER TABLE [dbo].[TabOperazioni] ADD  CONSTRAINT [DF_TabOperazioni_Datamatrix]  DEFAULT ('') FOR [Datamatrix]
GO
ALTER TABLE [dbo].[TabPezzi] ADD  CONSTRAINT [DF_TabPezzi_Datamatrix]  DEFAULT ('') FOR [Datamatrix]
GO
ALTER TABLE [dbo].[TabPezzi] ADD  CONSTRAINT [DF_TabPezzi_CodArt]  DEFAULT ('') FOR [CodArt]
GO
ALTER TABLE [dbo].[TabPezzi] ADD  CONSTRAINT [DF_TabPezzi_CodStazione]  DEFAULT ('') FOR [CodStazione]
GO
ALTER TABLE [dbo].[TabPezzi] ADD  CONSTRAINT [DF_TabPezzi_CodStato]  DEFAULT ('') FOR [CodStato]
GO
ALTER TABLE [dbo].[TabPezzi] ADD  CONSTRAINT [DF_TabPezzi_CodOperazDone]  DEFAULT ('') FOR [CodOperazDone]
GO
ALTER TABLE [dbo].[TabPezzi] ADD  CONSTRAINT [DF_TabPezzi_CodOperazCurr]  DEFAULT ('') FOR [CodOperazCurr]
GO
ALTER TABLE [dbo].[StoricoAllarmi]  WITH CHECK ADD  CONSTRAINT [FK_StoricoAllarmi_AnagAllarmi] FOREIGN KEY([CodAllarme])
REFERENCES [dbo].[AnagAllarmi] ([CodAllarme])
ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[StoricoAllarmi] CHECK CONSTRAINT [FK_StoricoAllarmi_AnagAllarmi]
GO
ALTER TABLE [dbo].[TabOperazioni]  WITH CHECK ADD  CONSTRAINT [FK_TabOperazioni_TabPezzi1] FOREIGN KEY([Datamatrix])
REFERENCES [dbo].[TabPezzi] ([Datamatrix])
ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[TabOperazioni] CHECK CONSTRAINT [FK_TabOperazioni_TabPezzi1]
GO
ALTER TABLE [dbo].[TabPezzi]  WITH CHECK ADD  CONSTRAINT [FK_TabPezzi_AnagArticoli] FOREIGN KEY([CodArt])
REFERENCES [dbo].[AnagArticoli] ([CodArt])
ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[TabPezzi] CHECK CONSTRAINT [FK_TabPezzi_AnagArticoli]
GO
/****** Object:  StoredProcedure [dbo].[stp_Allarmi_checkStatus]    Script Date: 09/11/2018 15:13:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		S.E.Locatelli
-- Create date: 2018.10.15
-- Description:	Verifica stringa Allarmi attivi (delta)
--				e processa di conseguenza registrando history
-- =============================================
CREATE PROCEDURE [dbo].[stp_Allarmi_checkStatus]
(
	@charSep	CHAR(1)			-- separatore elenco, default è ","
	,@alarmList	NVARCHAR(MAX)	-- elenco allarmi attivi con separatore indicato
)
AS
BEGIN

	BEGIN tran
		SET NOCOUNT ON;

		DECLARE	@adesso DATETIME = GETDATE()

		-- splitta elenco allarmi da stringa + separatore e salva in temp table...
		SELECT	*
		INTO	#CurrAlarm
		FROM
		(
			SELECT * FROM dbo.SplitStrings(@alarmList,@charSep)
			---- vers sql2016
			--SELECT value FROM STRING_SPLIT(@alarmList, @charSep)
		) as tblStatus

		--SELECT * from #CurrAlarm

		-- effettua check tra allarmi attivi e nuova lista, --> registro allarmi cessati
		SELECT	*
		INTO	#NewAlarms
		FROM
		(
			SELECT	*, 'NEW' as stato
			FROM	AnagAllarmi AAl
				INNER JOIN	#CurrAlarm CAl ON AAl.CodAllarme = CAl.value
			WHERE	AAl.ReportAttivo	= 1
				AND	AAl.CurrStatus		= 0
		) as tblNewAarms	

		-- effettua check tra allarmi attivi e nuova lista, --> registro allarmi NUOVI
		SELECT	*
		INTO	#CeasedAlarms
		FROM
		(
			SELECT	*, 'CEASED' as stato
			FROM	AnagAllarmi AAl
				LEFT OUTER JOIN	#CurrAlarm CAl ON AAl.CodAllarme = CAl.value
			WHERE	AAl.ReportAttivo	= 1
				AND	AAl.CurrStatus		= 1
				AND	CAl.value			IS NULL
		) as tblCeasedAlarms

		----------------------------------
		-- processo allarmi NUOVI
		----------------------------------
		-- ora aggiorno in anagrafica
		UPDATE	AnagAllarmi
			SET	CurrStatus	= 1
				,LastStart	= @adesso
		from	AnagAllarmi AAl
		INNER JOIN #NewAlarms nAl ON AAl.CodAllarme = nAl.CodAllarme
		-- riporto in history...
		INSERT INTO StoricoAllarmi(DtEvento, CodAllarme, Attivo)
		SELECT	@adesso, CodAllarme, 1
		FROM	#NewAlarms
		
		----------------------------------
		-- processo allarmi CESSATI
		----------------------------------
		-- ora aggiorno in anagrafica
		UPDATE	AnagAllarmi
			SET	CurrStatus	= 0
				,LastEnd	= @adesso
		from	AnagAllarmi AAl
		INNER JOIN #CeasedAlarms nAl ON AAl.CodAllarme = nAl.CodAllarme

		-- riporto in history...
		INSERT INTO StoricoAllarmi(DtEvento, CodAllarme, Attivo)
		SELECT	@adesso, CodAllarme, 0
		FROM	#CeasedAlarms
		
	COMMIT tran
END
GO
/****** Object:  StoredProcedure [dbo].[stp_Allarmi_getLast]    Script Date: 09/11/2018 15:13:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		S.E.Locatelli
-- Create date: 2018.10.15
-- Description:	Recupera ultimi record dallo storico allarmi
-- =============================================
CREATE PROCEDURE [dbo].[stp_Allarmi_getLast]
(
	@maxNum	INT	= 100	-- default mostra 100 records
)
AS
BEGIN

	SET NOCOUNT ON;

	SELECT	TOP (@maxNum) *
	FROM	vStoricoAllarmiExp
	ORDER BY DtEvento DESC

END
GO
/****** Object:  StoredProcedure [dbo].[stp_AnagArt_deleteQuery]    Script Date: 09/11/2018 15:13:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		S.E.Locatelli
-- Create date: 2018.11.05
-- Description:	elimina articolo (e programmi)
-- =============================================
create 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: 09/11/2018 15:13:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

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

	INSERT INTO	AnagArticoli(CodArt, PartProgMain, PartProgAux)
	VALUES(@CodArt, @PartProgMain, @PartProgAux)
		
END
GO
/****** Object:  StoredProcedure [dbo].[stp_AnagArt_search]    Script Date: 09/11/2018 15:13:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		S.E.Locatelli
-- Create date: 2018.11.05
-- Description:	cerca articolo (e programmi)
-- =============================================
CREATE 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: 09/11/2018 15:13:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

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

	UPDATE	AnagArticoli
		SET	CodArt			= @CodArt
			,PartProgMain	= @PartProgMain
			,PartProgAux	= @PartProgAux
	WHERE	CodArt			= @Original_CodArt
		
END
GO
/****** Object:  StoredProcedure [dbo].[stp_AnagOpr_deleteQuery]    Script Date: 09/11/2018 15:13:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

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

	DELETE FROM	AnagOperatori
	WHERE		Barcode		= @Barcode
		
END
GO
/****** Object:  StoredProcedure [dbo].[stp_AnagOpr_insertQuery]    Script Date: 09/11/2018 15:13:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		S.E.Locatelli
-- Create date: 2018.11.05
-- Description:	insert operatore
-- =============================================
CREATE PROCEDURE [dbo].[stp_AnagOpr_insertQuery]
(
	@Barcode	NVARCHAR(50)	= ''
	,@Cognome	NVARCHAR(50)	= ''
	,@Nome		NVARCHAR(50)	= ''
)
AS
BEGIN
	
	SET NOCOUNT ON;

	INSERT INTO AnagOperatori(Barcode, Cognome, Nome)
	VALUES(@Barcode, @Cognome, @Nome)
		
END
GO
/****** Object:  StoredProcedure [dbo].[stp_AnagOpr_search]    Script Date: 09/11/2018 15:13:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		S.E.Locatelli
-- Create date: 2018.11.05
-- Description:	cerca operatore
-- =============================================
create 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: 09/11/2018 15:13:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

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

	UPDATE	AnagOperatori
		SET	Barcode		= @Barcode
			,Cognome	= @Cognome
			,Nome		= @Nome
	WHERE	Barcode		= @Original_Barcode
		
END
GO
/****** Object:  StoredProcedure [dbo].[stp_TabOpr_deleteAll]    Script Date: 09/11/2018 15:13:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		S.E.Locatelli
-- Create date: 2018.10.15
-- Description:	Effettua uno svuotamento completo della tab operazioni
-- =============================================
CREATE PROCEDURE [dbo].[stp_TabOpr_deleteAll]
(
	@authKey	NVARCHAR(50) = '' -- chiave x verifica auth a cancellare...
)
AS
BEGIN
	BEGIN tran
		SET NOCOUNT ON;

		-- verifico comando
		IF(@authKey ='digheDelBu') 
			BEGIN
				TRUNCATE TABLE	TabOperazioni
				TRUNCATE TABLE	StoricoAllarmi
				DELETE	 FROM	TabPezzi
				--TRUNCATE TABLE	TabPezzi
			END
	COMMIT tran
END
GO
/****** Object:  StoredProcedure [dbo].[stp_TabOpr_getLast]    Script Date: 09/11/2018 15:13:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		S.E.Locatelli
-- Create date: 2018.10.15
-- Description:	Recupera elenco ultimi record inseriti in TabOperazioni
-- =============================================
CREATE PROCEDURE [dbo].[stp_TabOpr_getLast]
(
	@maxNum	INT	= 100	-- default mostra 100 records
)
AS
BEGIN

	SET NOCOUNT ON;

	SELECT	TOP (@maxNum) *
	FROM	TabOperazioni
	ORDER BY DtEvento DESC

END
GO
/****** Object:  StoredProcedure [dbo].[stp_TabOpr_insertQuery]    Script Date: 09/11/2018 15:13:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		S.E.Locatelli
-- Create date: 2018.10.15
-- Description:	Effettua registrazione di un operazione su un pezzo:
--				- SE NON CI FOSSE CREA nuovo pezzo 
--				- registra nuova riga operazione
--				- effettua eventuale update riga PEZZO
-- =============================================
CREATE PROCEDURE [dbo].[stp_TabOpr_insertQuery]
(
	@Datamatrix		NVARCHAR(50)	= ''	-- identificativo telaio letto
	,@CodArt		NVARCHAR(50)	= ''	-- Codice Articolo
	,@CodStazione	NVARCHAR(50)	= ''	-- codice della stazione 
	,@CodSottostaz	NVARCHAR(50)	= ''	-- codice della sottostazione
	,@CodOperaz		NVARCHAR(50)	= ''	-- codice dell'operazione
	,@CodStato		NVARCHAR(50)	= ''	-- codice dello stato dell'attivita
)
AS
BEGIN
	BEGIN tran
		SET NOCOUNT ON;

		-- variabili
		DECLARE	@Fatto			INT				= 0
		DECLARE	@Trovati		INT				= 0
		DECLARE	@IdxPezzo		INT				= 0
		DECLARE	@CodOperazDone	NVARCHAR(50)	= ''
		
		-- verifico se ci sia articolo (sennò MI FERMO...)
		SELECT	@Trovati		= COUNT(*)
		FROM	AnagArticoli	
		WHERE	CodArt			= @CodArt
		
		IF(@Trovati = 1)
			BEGIN
				-- verifico se ci sia già il pezzo
				SELECT	@Trovati	= COUNT(*)
				FROM	TabPezzi 
				WHERE	Datamatrix	= @Datamatrix
				-- recupero ULTIMA operazione (se c'è)
				SELECT	@CodOperazDone	= ISNULL(CodOperazCurr,'')
				FROM	TabPezzi 
				WHERE	Datamatrix	= @Datamatrix
		
				-- verifico IN PRIMIS se va creato nuovo record con quel datamatrix
				IF(@Trovati = 0)
					BEGIN
						INSERT INTO TabPezzi (Datamatrix, CodArt, CodStazione, CodStato)
						VALUES	(@Datamatrix, @CodArt, @CodStazione, @CodStato)
					END

				-- registro operazione
				INSERT INTO TabOperazioni (CodStazione, CodSottostaz, CodOperaz, CodStato, Datamatrix)
				VALUES	(@CodStazione, @CodSottostaz, @CodOperaz, @CodStato, @Datamatrix)

				-- aggiorno record pezzo
				UPDATE	TabPezzi	
				SET		CodStazione		= @CodStazione
						,CodStato		= @CodStato
						,CodOperazDone	= @CodOperazDone
						,CodOperazCurr	= @CodOperaz
				WHERE	Datamatrix		= @Datamatrix

				-- indico che ho fatto (>0)
				SELECT	@Fatto = 1
			END

		-- retitusico risultato
		SELECT	@Fatto as fatto

	COMMIT tran
END
GO
/****** Object:  StoredProcedure [dbo].[stp_TabOpr_purgeOld]    Script Date: 09/11/2018 15:13:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		S.E.Locatelli
-- Create date: 2018.10.15
-- Description:	Effettua uno svecchiamento della tab operazioni
-- =============================================
create PROCEDURE [dbo].[stp_TabOpr_purgeOld]
(
	@days2keep	INT	= 365	-- default tiene ultimi 365gg
)
AS
BEGIN
	BEGIN tran
		SET NOCOUNT ON;

		-- variabili
		DECLARE	@Fatto			INT				= 0
		-- normalizzo valore come  > 0
		IF(@days2keep < 0)
			SELECT @days2keep = -@days2keep

		-- elimina dati OPR piu vecchi dei gg indicati
		DELETE
		FROM	TabOperazioni
		WHERE	DtEvento < DATEADD(DAY, -@days2keep, GETDATE())

		-- indico che ho fatto (>0)
		SELECT	@Fatto = 1
		
		-- retitusico risultato
		SELECT	@Fatto as fatto

	COMMIT tran
END
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Attivo per reporting in storico' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AnagAllarmi', @level2type=N'COLUMN',@level2name=N'ReportAttivo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Codice Datamatrix' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'TabOperazioni', @level2type=N'COLUMN',@level2name=N'Datamatrix'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Codice Datamatrix' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'TabPezzi', @level2type=N'COLUMN',@level2name=N'Datamatrix'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Cod Articolo (prime 11 cifre del datamatrix, numDisegno)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'TabPezzi', @level2type=N'COLUMN',@level2name=N'CodArt'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Stazione ultimo aggiornamento' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'TabPezzi', @level2type=N'COLUMN',@level2name=N'CodStazione'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Ultima fase eseguita' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'TabPezzi', @level2type=N'COLUMN',@level2name=N'CodOperazDone'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Fase in esecuzione' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'TabPezzi', @level2type=N'COLUMN',@level2name=N'CodOperazCurr'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[40] 4[20] 2[20] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "AnagAllarmi"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 178
               Right = 208
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "StoricoAllarmi"
            Begin Extent = 
               Top = 5
               Left = 295
               Bottom = 123
               Right = 465
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 9
         Width = 284
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 1440
         Alias = 900
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vStoricoAllarmiExp'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vStoricoAllarmiExp'
GO
USE [master]
GO
ALTER DATABASE [CMS-OMR] SET  READ_WRITE 
GO