Samuele E. Locatelli
971 lines
59 KiB
Transact-SQL
971 lines
59 KiB
Transact-SQL
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
|