-- SQL script file for ION_Data 5.0 to 5.1
-- be sure to include existence checks

IF TYPE_ID(N'SourceQuantityRange') IS NOT NULL DROP TYPE dbo.SourceQuantityRange

-- Used by stored procedure GetDatalogDataMultipleStreams
CREATE TYPE [dbo].[SourceQuantityRange] AS TABLE
(
	[Ordinal] [int] NOT NULL,
	[SourceName] [nvarchar](max) NOT NULL,
	[QuantityName] [nvarchar](max) NOT NULL,
	[StartTime] [datetime2](7) NOT NULL,
	[EndTime] [datetime2](7) NOT NULL,
	PRIMARY KEY CLUSTERED 
	(
		[Ordinal] ASC
	)	
	WITH (IGNORE_DUP_KEY = OFF)
)
GO

GRANT EXECUTE ON TYPE::[dbo].[SourceQuantityRange] TO [ION_DSD_Reader]	

IF EXISTS (SELECT * FROM sysobjects WHERE id=object_id('[dbo].[GetDatalogDataMultipleStreams]') AND OBJECTPROPERTY(id, 'IsProcedure')=1)
  DROP PROCEDURE [dbo].[GetDatalogDataMultipleStreams]
GO
/***********************************************************************************************************
 * Name: GetDatalogDataMultipleStreams
 *
 * Author: Jeff Dodson
 *
 * Date: 03/16/2012
 *
 * Description: "SourceQuantityRange" describes a date range of data for a single source/quantity
 *				(a.k.a. device/topic, a.k.a. "stream") pair. It is used as the input parameter for a stored
 *				procedure ("GetDatalogDataMultipleStreams") which reads up data for multiple streams.
 *
 * Why it is Necessary: Our original stored procedure allowed us to pass in a list of source/quantity names.
 *						These were parsed, indexed to the appropriate identifiers, and then the corresponding
 *						data was returned. However, this only allowed us to read *all* topics for *all*
 *						devices listed, which does not fit our typical use pattern in Dashboards. We created
 *						several test stored procedures to guage relative efficiency of different methods of
 *						specifying which sources and quantities were needed, and of selecting/returning them.
 *						Our tests showed that, when invoked from C#, the most efficient way to specify
 *						multiple sources, each wanting to read in different quantity combinations, was to
 *						pass in a table containing the source/quantity combinations and the date ranges for
 *						each. This user-defined type allows you to specify a source/quantity pair and the
 *						desired date range for its data. Therefore a table of this type allows for the
 *						specification of multiple source/quantity pairs, each with a different date range.
 ***********************************************************************************************************/

CREATE PROCEDURE [dbo].[GetDatalogDataMultipleStreams]
(
	@SourceQuantityRange AS [dbo].[SourceQuantityRange] READONLY
)
AS

/* NOTES: (a) This procedure returns MULTIPLE RESULT SETS!
*		  (b) Start/End times for data are exclusive/inclusive in this stored procedure beause we almost
*			  always deal with interval-ending data in our applications.
*		  (c) The input parameter is a user-defined SQL data type. */

DECLARE @Ordinal INT					-- Order in which a source/quantity pair should appear in the result set list
DECLARE @SourceName NVARCHAR(MAX)		-- Name of the source (device) for a source/quantity pair
DECLARE @QuantityName NVARCHAR(MAX)		-- Name of the quantity (topic) for a source/quantity pair
DECLARE @StartTime DATETIME2			-- Exclusive start time of date range for a source/quantity pair data set
DECLARE @EndTime DATETIME2				-- Inclusive end time of date range for a source/quantity pair data set
DECLARE @Next INT						-- Local counter used to step one-by-one over the source/quantity pairs

-- Get the lowest ordinal number from the input table. Note that the caller could (a) start with an ordinal other
-- than 0 or 1, and (b) duplicate ordinals, either intentionally or accidentally. In that case, the results would
-- be ambiguous, because the stored procedure would skip the second (or third, etc.) repeated ordinal and would not
-- include its data in the result set.
SELECT @Next = MIN(Ordinal) FROM @SourceQuantityRange

-- Running these queries separately in the while loop gives better performance than a join.
-- (We did quite a bit of performance comparisons for different approaches.)
WHILE @Next IS NOT NULL
BEGIN

	-- Fetch the source/quantity names and date range from the input table for this ordinal
	SELECT @SourceName = SourceName, @QuantityName = QuantityName, @StartTime = StartTime, @EndTime = EndTime
	FROM @SourceQuantityRange
	WHERE Ordinal = @Next
	
	DECLARE @SourceId INT
	DECLARE @QuantityId INT

	-- Map the source/quantity names to their corresponding identifiers
	SELECT @SourceId = ID FROM dbo.Source WHERE Name = @SourceName
	SELECT @QuantityId = ID FROM dbo.Quantity WHERE Name = @QuantityName

	-- Note that our filtering is done as "interval-ending," where we exclude the beginning of the range
	-- and include the end of the range. Other methods are possible, but all known Company products
	-- currently use interval-ending.
	SELECT d.TimestampUTC, d.Value
	FROM dbo.Datalog2 d
	WHERE d.SourceID = @SourceId
		AND d.QuantityID = @QuantityId
		AND d.TimestampUTC > @StartTime
		AND d.TimestampUTC <= @EndTime
	ORDER BY d.TimestampUTC

	-- Find the next ordinal that is greater than the one we just processed
	SELECT @Next = MIN(Ordinal) FROM @SourceQuantityRange WHERE Ordinal > @Next

END
GO

GRANT EXECUTE ON [dbo].[GetDatalogDataMultipleStreams] TO [ION_DSD_Reader]


/* Modification of the Alarm Log View to accomodate dropping out individual Alarm pickups
 * Five total changes:
 *  1. Change in the index order for the EventLog2 table 
 *  2. Modification of the vAlarmLog view
 *  3. Addition of a GetActiveAlarms stored procedure
 *  4. Addition of a custom interger id table type
 *  5. Addition of a stored procedure to deactivate individual Alarms that takes a TVP
 */

SET QUOTED_IDENTIFIER ON
GO

CREATE NONCLUSTERED INDEX [IX_EventLog2_Alarms] ON [dbo].[EventLog2] 
(
	[SourceID] ASC,
	[EffectID] ASC,
	[TimestampUTC] ASC,
	[TranType] ASC,
	[ID] ASC        
)
WITH (DROP_EXISTING = ON)
ON [PRIMARY]
GO

SET QUOTED_IDENTIFIER OFF
GO

ALTER VIEW [dbo].[vAlarmLog]
AS
SELECT e.ID
	, e.TranType AS [Type]
	, e.SourceId
	, s.Name AS SourceName
	, st.Name AS SourceType
	, e.EffectId
	, e.TimestampUTC 
	, e.Priority
	, (SELECT COALESCE(CAST(QuantityID as NVARCHAR(5)), String) FROM EventString WHERE ID = e.CauseID) AS Cause
	, (SELECT String FROM EventString WHERE ID = e.CauseValueID) AS CauseValue
	, (SELECT COALESCE(CAST(QuantityID as NVARCHAR(5)), String) FROM EventString WHERE ID = e.EffectID) AS Effect
	, (SELECT String FROM EventString WHERE ID = e.EffectValueID) AS EffectValue
	, d.id AS EndId
	, d.timestamputc AS EndTimestampUTC
	, (SELECT COALESCE(CAST(QuantityID as NVARCHAR(5)), String) FROM EventString WHERE ID = d.CauseID) AS EndCause
	, (SELECT String FROM EventString WHERE ID = d.CauseValueID) AS EndCauseValue
	, (SELECT String FROM EventString WHERE ID = d.EffectValueID) AS EndEffectValue
	, a.AckTimeUTC
	, a.UserName AS AckUserName
	, a.Remark AS AckRemark
	, CASE
		WHEN EXISTS (
			SELECT null FROM dbo.WaveformLog2 w
			WHERE w.SourceID = e.SourceId
			AND w.TimeStampUTC > DateAdd(ss,-1,e.TimestampUTC)
			AND w.TimeStampUTC < DateAdd(ss,1,e.TimestampUTC))
		THEN 1
		ELSE 0
		END AS Waveform
FROM dbo.EventLog2 e
LEFT JOIN dbo.[EventLog2] d ON
	e.TranType = 1
	AND d.id = (
		SELECT top 1 ID
		FROM dbo.[EventLog2] d
		WHERE d.TimestampUTC >= e.TimestampUTC AND d.SourceID = e.SourceID AND d.EffectID = e.EffectId AND ID <> e.ID
		ORDER BY d.TimestampUTC ASC)
LEFT JOIN dbo.EventAcknowledgement a ON a.EventLogID = e.ID
LEFT JOIN dbo.Source s ON e.SourceID = s.ID
LEFT JOIN dbo.SourceType st ON st.ID = s.SourceTypeID
WHERE (e.TranType = 1 OR e.TranType = 0)

GO

IF OBJECT_ID('dbo.GetActiveAlarms') IS NOT NULL
BEGIN
	DROP PROCEDURE dbo.GetActiveAlarms
END
GO
CREATE PROCEDURE [dbo].[GetActiveAlarms] 
AS
BEGIN

	SELECT	e.ID AS EventId, 
			e.TimestampUTC AS PickupUtc, 
			s.Name AS Device, 
			e.Type AS AlarmType,
			cause.String as Cause,
			effect.String as Effect
	FROM EventLog2 AS e
	LEFT JOIN Source AS s ON e.SourceID = s.ID
	LEFT JOIN EventString as cause on e.CauseID = cause.ID
	LEFT JOIN EventString as effect on e.EffectID = effect.ID
	WHERE e.TranType = 1
	AND NOT EXISTS (
		SELECT ID 
		FROM EventLog2 
		WHERE	TimestampUTC >= e.TimestampUTC AND 
				SourceID = e.SourceID AND 
				EffectID = e.EffectId AND 
				ID <> e.ID)
				
END

GO

IF OBJECT_ID('dbo.IntegerIdList') IS NOT NULL
BEGIN
	DROP TYPE dbo.IntegerIdList
END
GO
CREATE TYPE [dbo].[IntegerIdList] AS TABLE
(
	[IntegerId] [int] NOT NULL	
)
GO

IF OBJECT_ID('dbo.DeactivateAlarms') IS NOT NULL
BEGIN
	DROP PROCEDURE [dbo].[DeactivateAlarms]
END
GO

SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[DeactivateAlarms]
(
	@EventIdentifier AS [dbo].[IntegerIdList] READONLY
)
AS

	DECLARE @CauseId AS INT
	-- If this cause string already exists, get its identifier, otherwise add it and return the new identifier  
	EXECUTE InsertEventString 'Administrative Deactivation', NULL, @ID=@CauseId OUTPUT
	-- Insert new entries in the EventLog2 table. One entry will be inserted for each event listed in the
	-- table-valued input parameter.
	INSERT EventLog2 (SourceID, TimestampUTC, Priority, Type, CauseID, CauseValueID, EffectID, EffectValueID)
	SELECT e2.SourceID,
		e2.TimestampUTC AS TimestampUTC,	-- same timestamp as the original event, in order to target specific event
		e2.Priority,						-- Same priority as original event
		e2.Type + 16 AS Type,				-- Pickup events range from 17 through 31; add 16 to get corresponding dropout
		@CauseId AS CauseID,				-- Use cause supplied to this procedure by the caller
		6 AS CauseValueID,					-- "6" corresponds to the value of zero
		e2.EffectID,						-- Same effect as the original event
		6 AS EffectValueID					-- "6" corresponds to the value of zero
	FROM EventLog2 AS e2
	INNER JOIN @EventIdentifier AS ei		-- Produce one row per entry in the table-valued-parameter
	ON ei.IntegerId = e2.ID
GO
SET QUOTED_IDENTIFIER OFF
GO

