Samuele E. Locatelli
148 lines
4.3 KiB
Transact-SQL
148 lines
4.3 KiB
Transact-SQL
/*************************************************************
|
|
-- AGGIUNTA FUNZIONI
|
|
**************************************************************/
|
|
|
|
/****** Object: UserDefinedFunction [dbo].[f_padLeft] Script Date: 17/11/2018 16:12:15 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
/***************************************
|
|
* FUNCTION f_padLeft
|
|
*
|
|
* fornisce una stringa della lunghezza desiderata aggiungendo a sx il carattere richiesto alla @string originale
|
|
*
|
|
* Steamware, S.E.L.
|
|
* mod: 2010.03.19
|
|
*
|
|
****************************************/
|
|
CREATE FUNCTION [dbo].[f_padLeft] (@string VARCHAR(255), @desired_length INTEGER, @pad_character CHAR(1))
|
|
RETURNS VARCHAR(255) AS
|
|
BEGIN
|
|
|
|
-- Prefix the required number of spaces to bulk up the string and then replace the spaces with the desired character
|
|
RETURN CASE
|
|
WHEN LEN(@string) < @desired_length
|
|
THEN REPLACE(SPACE(@desired_length - LEN(@string)), ' ', @pad_character) + @string
|
|
ELSE @string
|
|
END
|
|
|
|
END
|
|
GO
|
|
|
|
|
|
|
|
-- =============================================
|
|
-- Author: S.E.Locatelli
|
|
-- Create date: 2018.10.15
|
|
-- Description: Verifica stringa Allarmi attivi (delta)
|
|
-- e processa di conseguenza registrando history
|
|
-- =============================================
|
|
ALTER 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
|
|
|
|
-- per PRIMA COSA inserisco EVENTUALI NUOVI allarmi mai visti...
|
|
;WITH cte2Ins AS
|
|
(
|
|
-- allarmi padded a 8 char
|
|
SELECT dbo.f_padLeft(CAl.Item, 9, '0') AS CodAllarme, 'ND' AS Descrizione, 1 as ReportAttivo, '1900-01-01' AS LastStart, '1900-01-01' AS LastEnd, 0 CurrStatus
|
|
FROM AnagAllarmi AS AAl
|
|
RIGHT OUTER JOIN #CurrAlarm CAl ON dbo.f_padLeft(AAl.CodAllarme, 9, '0') = dbo.f_padLeft(CAl.Item, 9, '0')
|
|
WHERE AAl.CodAllarme IS NULL
|
|
)
|
|
INSERT INTO AnagAllarmi
|
|
SELECT * FROM cte2Ins
|
|
|
|
-- effettua check tra allarmi attivi e nuova lista, --> registro allarmi cessati
|
|
SELECT *
|
|
INTO #NewAlarms
|
|
FROM
|
|
(
|
|
-- allarmi padded a 8 char
|
|
SELECT *, 'NEW' as stato
|
|
FROM AnagAllarmi AAl
|
|
--INNER JOIN #CurrAlarm CAl ON AAl.CodAllarme = CAl.value
|
|
INNER JOIN #CurrAlarm CAl ON dbo.f_padLeft(AAl.CodAllarme, 9, '0') = dbo.f_padLeft(CAl.Item, 9, '0')
|
|
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
|
|
LEFT OUTER JOIN #CurrAlarm CAl ON dbo.f_padLeft(AAl.CodAllarme, 9, '0') = dbo.f_padLeft(CAl.Item, 9, '0')
|
|
WHERE AAl.ReportAttivo = 1
|
|
AND AAl.CurrStatus = 1
|
|
--AND CAl.value IS NULL
|
|
AND CAl.Item 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 dbo.f_padLeft(AAl.CodAllarme, 9, '0') = dbo.f_padLeft(nAl.CodAllarme, 9, '0')
|
|
-- 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 dbo.f_padLeft(AAl.CodAllarme, 9, '0') = dbo.f_padLeft(nAl.CodAllarme, 9, '0')
|
|
-- riporto in history...
|
|
INSERT INTO StoricoAllarmi(DtEvento, CodAllarme, Attivo)
|
|
SELECT @adesso, CodAllarme, 0
|
|
FROM #CeasedAlarms
|
|
|
|
COMMIT tran
|
|
END
|
|
|
|
|
|
|
|
-- UPDATE
|
|
update AnagAllarmi set CodAllarme = dbo.f_padLeft(CodAllarme, 9, '0')
|
|
|
|
select * from AnagAllarmi |