/*************************************************************
-- 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