-- ------------------------------------------------------------------------------------
-- ION Data v5.0 Database Upgrade: Step 9
-- 
-- Updates all database objects (procedures, functions, views) affected by the 
-- database schema updates. 
-- ------------------------------------------------------------------------------------
    SELECT dbo.FormatStatusMessage('Step 9: Started.')
GO

--*****************************************************************************
-- Remove redundant functions and procedures.
--*****************************************************************************
IF(dbo.ProcedureExists('DeleteAll') = 1)
    DROP PROCEDURE [dbo].[DeleteAll]
GO
IF(dbo.ProcedureExists('TrimRecordsSQL') = 1)
    DROP PROCEDURE [dbo].[TrimRecordsSQL]
GO
IF(dbo.ProcedureExists('TrimRecordsMSDE') = 1)
    DROP PROCEDURE [dbo].[TrimRecordsMSDE]
GO
IF(dbo.ProcedureExists('InsertDataLogStamp') = 1)
    DROP PROCEDURE [dbo].[InsertDataLogStamp]
GO
IF(dbo.FunctionExists('DateWithMSec') = 1)
    DROP FUNCTION [dbo].[DateWithMSec]
GO
IF(dbo.FunctionExists('DateAndFracAsUnix') = 1)
    DROP FUNCTION [dbo].[DateAndFracAsUnix]
GO
IF(dbo.FunctionExists('DateAndFracAsCOMTime') = 1)
    DROP FUNCTION [dbo].[DateAndFracAsCOMTime]
GO
IF(dbo.ProcedureExists('InsertDataLogStamp') = 1)
    DROP PROCEDURE [dbo].[InsertDataLogStamp]
GO
IF(dbo.ProcedureExists('CompareWaveformLogSamples') = 1)
    DROP PROCEDURE [dbo].[CompareWaveformLogSamples]
GO
IF(dbo.ProcedureExists('CompareWaveformLogSamplesToIMAGE') = 1)
    DROP PROCEDURE [dbo].[CompareWaveformLogSamplesToIMAGE]
GO

IF(dbo.ProcedureExists('IONEEM_GetDataLogData') = 1)
    DROP PROCEDURE IONEEM_GetDataLogData
GO
--*****************************************************************************
-- PROCEDURE: IONEEM_GetDataLogData
--
-- NOTES: Return DataLog2 data based on the criteria.
--
--*****************************************************************************

CREATE PROCEDURE dbo.IONEEM_GetDataLogData
(
    @MaxRowsToReturn INT,
    @aSourceID INT,
    @aQuantityID SMALLINT,
    @aDataLogID BIGINT
)
AS

SELECT TOP (@MaxRowsToReturn) 
    TimestampUTC, 
    Value, 
    ID
FROM 
    dbo.DataLog2
WHERE
    SourceID = @aSourceID 
    AND QuantityID = @aQuantityID
    AND ID > @aDataLogID
ORDER BY 
    ID ASC

RETURN 0

GO

IF(dbo.ProcedureExists('IONEEM_GetMaxDatalogID') = 1)
    DROP PROCEDURE IONEEM_GetMaxDatalogID
GO
--*****************************************************************************
-- PROCEDURE: IONEEM_GetMaxDatalogID
--
-- NOTES: Return the maximum DataLog2.ID, or zero if no waveforms exist.
--
--*****************************************************************************

CREATE PROCEDURE dbo.IONEEM_GetMaxDatalogID
AS

SELECT 
    ISNULL(MAX(ID),0) 
FROM 
    dbo.DataLog2

RETURN 0

GO

IF(dbo.ProcedureExists('IONEEM_GetMaxEventID') = 1)
    DROP PROCEDURE IONEEM_GetMaxEventID
GO

--*****************************************************************************
-- PROCEDURE: IONEEM_GetMaxEventID
--
-- NOTES: Return the maximum EventLog2.ID, or zero if no events exist.
--
--*****************************************************************************

CREATE PROCEDURE dbo.IONEEM_GetMaxEventID
AS

SELECT 
    ISNULL(MAX(ID),0) 
FROM 
    dbo.EventLog2

RETURN 0

GO

IF(dbo.FunctionExists('udfDAL_GetDefaultQuantityIDForLabel') = 1)
    DROP FUNCTION udfDAL_GetDefaultQuantityIDForLabel
GO
/******************************************************************************
    Get the default measurement ID for a label. Returns NULL if no default measurement exists
    This function is used by InsertEventString when inserting an event.
******************************************************************************/

CREATE FUNCTION [dbo].[udfDAL_GetDefaultQuantityIDForLabel]
(
    @Label NVARCHAR(200)
)
RETURNS SMALLINT
AS
BEGIN
DECLARE @QuantityID SMALLINT

-- Returns the quantity mapping if it exists
SELECT @QuantityID=QuantityID FROM DefaultLabelQuantity WHERE [Label]=@Label
-- If there is no mapping, look into the Quantity table and return the ID of a non-custom measurement (if found)
IF @QuantityID IS NULL
BEGIN
    SELECT 
        @QuantityID=[ID]
    FROM
        Quantity
    WHERE 
        [Name] = @Label AND [ID] < 10000
END
RETURN @QuantityID
END
GO


IF(dbo.TableExists('EventString') = 1)
    BEGIN
        ALTER TABLE [dbo].[EventString] ADD [QuantityID] [smallint] NULL
    END
GO

IF(dbo.TableExists('EventString') = 1)
    BEGIN
        UPDATE dbo.EventString SET [QuantityID] = dbo.udfDAL_GetDefaultQuantityIDForLabel([String])
            WHERE [QuantityID] IS NULL
        UPDATE dbo.EventString SET [String] = dbo.udfDAL_ExtractNumberFromIonName([String])
            WHERE CHARINDEX('{0}', [String]) = 0 AND [ID] IN (SELECT DISTINCT ID FROM (SELECT CauseID AS ID FROM [EventLog2] UNION ALL SELECT EffectID AS ID FROM [EventLog2]) CausesAndEffects)
    END
GO

IF(dbo.ConstraintExists('EventString', 'FK_EventString_Quantity') = 0)
    BEGIN
        ALTER TABLE [dbo].[EventString] ADD
            CONSTRAINT [FK_EventString_Quantity] FOREIGN KEY
            ([QuantityID]) REFERENCES [dbo].[Quantity] ([ID])
    END
GO

IF(dbo.ProcedureExists('InsertEventString') = 1)
    DROP PROCEDURE InsertEventString
GO
/*****************************************************************************
    Inserts an event string. This sproc is used by InsertEventLog to
    insert an event.
******************************************************************************/
CREATE PROCEDURE InsertEventString
(
    @String NVARCHAR(3998),
    @IonLabel NVARCHAR(255) = NULL,
    @ID INT OUTPUT
)
AS
SET NOCOUNT ON

-- Check to see if there is already a value for the same string
SELECT @ID=ID FROM EventString WHERE String=@String
IF @ID IS NULL
BEGIN
    -- There is no value for the same string, insert it.
    INSERT INTO EventString (String, QuantityID) VALUES (
        @String,
        CASE WHEN @IonLabel IS NULL THEN NULL
        ELSE dbo.udfDAL_GetDefaultQuantityIDForLabel(@IonLabel)
        END)
    SET @ID=SCOPE_IDENTITY()
END
RETURN 0
GO

GRANT EXECUTE ON [dbo].[InsertEventString] TO [mma_Archiver]
GO

IF(dbo.ViewExists('vEventLog') = 1)
    DROP VIEW [dbo].[vEventLog]
GO

--*****************************************************************************
-- VIEW: vEventLog
--
-- NOTES: Updated 15th July 2008 to add the el.ID column to support Power Quality
-- stored procedures
--
-- $Id: //rdsw/dev/cayman/SW_ION_E/database/Components/ION/Views/02.vEventLog.sql#2 $
--
--*****************************************************************************

CREATE VIEW dbo.vEventLog
AS
SELECT el.ID, s.ID AS SourceID, s.Name AS SourceName, ns.Name AS SourceNamespace, el.TimestampUTC, el.Priority, isnull(el.[Type], convert(tinyint, 0)) AS [Type],
    (SELECT COALESCE(CAST(QuantityID as NVARCHAR(5)), String) From EventString WHERE ID = el.CauseID) as Cause,
    (SELECT String From EventString WHERE ID = el.CauseValueID) as CauseValue,
    (SELECT COALESCE(CAST(QuantityID as NVARCHAR(5)), String) From EventString WHERE ID = el.EffectID) as Effect,
    (SELECT String From EventString WHERE ID = el.EffectValueID) as EffectValue
FROM dbo.EventLog2 el INNER JOIN   
    dbo.Source s ON el.SourceID = s.ID INNER JOIN   
    dbo.Namespace ns ON s.NamespaceID = ns.ID
GO
    GRANT SELECT ON [dbo].[vEventLog] TO [ION_Reader]
    GRANT SELECT ON [dbo].[vEventLog] TO [mma_Archiver]
GO

IF(dbo.ViewExists('vAlarmLog') = 1)
    DROP VIEW [dbo].[vAlarmLog]
GO

--*****************************************************************************
-- VIEW: vAlarmLog
--*****************************************************************************
CREATE 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 d.TranType = 2
		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(dbo.FunctionExists('DateWithoutFraction') = 1)
    DROP FUNCTION [dbo].[DateWithoutFraction]
GO

--*****************************************************************************
-- FUNCTION: DateWithoutFraction
--
-- NOTES: Return the date rounded down to the nearest whole second.
--
--*****************************************************************************

CREATE FUNCTION [dbo].[DateWithoutFraction]
(
    @timestamp AS DATETIME2
)  
RETURNS DATETIME AS  
BEGIN 
    RETURN CONVERT
        (
        DATETIME, 
        CONVERT(NVARCHAR(19), @timestamp, 126)
        )
END

GO

IF(dbo.ProcedureExists('IONEEM_GetQuantityByName') = 1)
    DROP PROCEDURE IONEEM_GetQuantityByName
GO

--*****************************************************************************
-- PROCEDURE: IONEEM_GetQuantityByName
--
-- NOTES: Return the quantity information, based on the name provided.
--
--*****************************************************************************

CREATE PROCEDURE dbo.IONEEM_GetQuantityByName
(
    @QuantityName NVARCHAR(200)
)
AS

SELECT 
    ID,
    Name,
    Base,
    Unit,
    Phase,
    Type,
    Direction,
    Misc,
    Description
FROM 
    dbo.Quantity 
WHERE 
    Name = @QuantityName

RETURN 0

GO

IF(dbo.FunctionExists('MinDate2') = 1)
    DROP FUNCTION [dbo].[MinDate2]
GO

--*****************************************************************************
-- FUNCTION: MinDate2

--
-- NOTES:
--
-- $Id: //rdsw/dev/cayman/SW_ION_E/database/Components/ION/UserDefinedFunctions/02.MinDate.sql#2 $
--
--*****************************************************************************

/****** Object:  User Defined Function dbo.MinDate    Script Date: 21/11/2002 4:13:00 PM ******/
CREATE FUNCTION [dbo].[MinDate2] 
(
    @date1 as datetime2, 
    @date2 as datetime2
)  
RETURNS datetime2 AS  
BEGIN 
    If @date1 < @date2 
        RETURN @date1
    RETURN @date2
END
GO

IF(dbo.FunctionExists('MaxDate2') = 1)
    DROP FUNCTION [dbo].[MaxDate2]
GO

--*****************************************************************************
-- FUNCTION: MaxDate2
--
-- NOTES:
--
-- $Id: //rdsw/dev/cayman/SW_ION_E/database/Components/ION/UserDefinedFunctions/02.MaxDate.sql#2 $
--
--*****************************************************************************

/****** Object:  User Defined Function dbo.MaxDate    Script Date: 21/11/2002 4:13:00 PM ******/
CREATE FUNCTION [dbo].[MaxDate2] 
(
    @date1 as datetime2, 
    @date2 as datetime2
)  
RETURNS datetime2 AS  
BEGIN 
    If @date1 > @date2 
        RETURN @date1
    RETURN @date2
END
GO

IF(dbo.ProcedureExists('InsertEventLog') = 1)
    DROP PROCEDURE InsertEventLog
GO

--*****************************************************************************
-- PROCEDURE: InsertEventLog
--
-- NOTES:
--
-- $Id: //rdsw/dev/cayman/SW_ION_E/database/Components/ION/StoredProcedures/02.InsertEventLog.sql#2 $
--
--*****************************************************************************

/*****************************************************************************
    Inserts an event.
******************************************************************************/
CREATE  PROCEDURE InsertEventLog
(
    @SourceID INT,
    @TimestampUTC DATETIME2,
    @Priority TINYINT=0,
    @Type TINYINT=0,
    @Cause NVARCHAR(3998)=NULL,
    @CauseValue NVARCHAR(3998)=NULL,
    @Effect NVARCHAR(3998)=NULL,
    @EffectValue NVARCHAR(3998)=NULL,
    @ID INT OUTPUT
)
AS
SET NOCOUNT ON

-- Declare some local variables for temp storage of data
DECLARE @CauseID INT, @CauseValueID INT, @EffectID INT, @EffectValueID INT
DECLARE @ProcessedIonString NVARCHAR(3998)
-- Assume that none of the event strings is specified
SET @CauseID=NULL
SET @CauseValueID=NULL
SET @EffectID=NULL
SET @EffectValueID=NULL

-- Determine the cause, causevalue, effect and effectvalue ids
-- If an event related string is specified then execute the InsertEventString stored procedure to 
-- either add a new record, or return the ID of an existing recoord
If @Cause IS NOT NULL
BEGIN
	SET @ProcessedIonString = dbo.udfDAL_ExtractNumberFromIonName(@Cause)
	EXECUTE InsertEventString @String=@ProcessedIonString, @IonLabel=@Cause, @ID=@CauseID OUTPUT
END

If @CauseValue IS NOT NULL
    EXECUTE InsertEventString @String=@CauseValue, @ID=@CauseValueID OUTPUT

If @Effect IS NOT NULL
BEGIN
	SET @ProcessedIonString = dbo.udfDAL_ExtractNumberFromIonName(@Effect)
	EXECUTE InsertEventString @String=@ProcessedIonString, @IonLabel=@Effect, @ID=@EffectID OUTPUT
END

If @EffectValue IS NOT NULL
    EXECUTE InsertEventString @String=@EffectValue, @ID=@EffectValueID OUTPUT

-- Insert the new event to the event log table
INSERT INTO dbo.EventLog2
(
    SourceID
    , TimestampUTC
    , Priority
    , Type
    , CauseID
    , CauseValueID
    , EffectID
    , EffectValueID
)
VALUES 
(
    @SourceID
    , @TimestampUTC
    , @Priority
    , @Type
    , @CauseID
    , @CauseValueID
    , @EffectID
    , @EffectValueID
)

SET @ID = SCOPE_IDENTITY()

RETURN 0
GO
    GRANT EXECUTE ON [dbo].[InsertEventLog] TO [mma_Archiver]
GO



IF(dbo.ProcedureExists('IsExactDuplicate') = 1)
    DROP PROCEDURE IsExactDuplicate
GO

--*****************************************************************************
-- PROCEDURE: IsExactDuplicate
--
-- NOTES:
--
--   Returns 1 if the value is a duplicate, zero otherwise.
--   A NULL value is considered equal to a NULL value.
--*****************************************************************************
CREATE PROCEDURE [dbo].[IsExactDuplicate] 
(
    @SourceID INT,
    @TimestampUTC DATETIME2,
    @QuantityID SMALLINT,
    @Value FLOAT
)
AS

SET NOCOUNT ON

DECLARE @bit BIT
SET @bit = 0

IF EXISTS
(
    SELECT 
        TOP 1 1
    FROM 
        dbo.DataLog2
    WHERE
        SourceID = @SourceID
        AND QuantityID = @QuantityID
        AND TimestampUTC = @TimestampUTC
        AND (Value=@Value OR (Value IS NULL AND @Value IS NULL))
)
BEGIN
    RETURN 1
END
ELSE
BEGIN
    RETURN 0
END
GO
    GRANT EXECUTE ON [dbo].[IsExactDuplicate] TO [mma_Archiver]
GO

IF(dbo.ProcedureExists('IsExactEventDuplicate') = 1)
    DROP PROCEDURE IsExactEventDuplicate
GO

/*************************************************************************************
  Summary:
  -------------- 
  -> Originally developped for use by the MeterM@ilArchiver.
  -> Returns 1 if the value is a duplicate, zero otherwise.
  -> The inputs are the fields from an Event Recorder entery (ER) in a MeterM@il.
  -> The procedure then looks up the corresponding string ids (integers) from
     from the EventString table.
  -> Finally, the comparison is performed.
**************************************************************************************/

CREATE PROCEDURE [dbo].[IsExactEventDuplicate] 
(
    @TimestampUTC DATETIME2,
    @Priority TINYINT=0,
    @Cause NVARCHAR(3998)=NULL,
    @CauseValue NVARCHAR(3998)=NULL,
    @Effect NVARCHAR(3998)=NULL,
    @EffectValue NVARCHAR(3998)=NULL
)
AS
SET NOCOUNT ON


DECLARE @CauseId INT, @CauseValueId INT, @EffectId INT, @EffectValueId INT

SET @CauseId=NULL
SET @CauseValueId = NULL
SET @EffectId = NULL
SET @EffectValueId = NULL

SET @CauseId = (Select ID from EventString where EventString.String = @Cause)
SET @CauseValueID = (Select ID from EventString where EventString.String = @CauseValue)
SET @EffectId = (Select ID from EventString where EventString.String = @Effect)
SET @EffectValueID = (Select ID from EventString where EventString.String = @EffectValue)

IF EXISTS(SELECT * FROM dbo.EventLog2 el
        WHERE el.TimestampUTC = @TimestampUTC
        AND el.Priority = @Priority
        AND el.CauseId=@CauseId
        AND el.CauseValueId=@CauseValueId
        AND el.EffectID=@EffectID
        AND el.EffectValueID=@EffectValueID)
    
    -- duplicate event found
    RETURN 1

ELSE

    RETURN 0

GO

IF(dbo.ProcedureExists('OldSchemaGetPQData') = 1)
    DROP PROCEDURE [dbo].[OldSchemaGetPQData]
GO

--*****************************************************************************
-- FUNCTION: OldSchemaGetPQData
--
-- NOTES: This just calls the existing GetPQData procedure that uses the new
-- schema
--
--*****************************************************************************

CREATE PROCEDURE [dbo].[OldSchemaGetPQData]
(
    @TimestampUTC DATETIME,
    @FractionOfASecond REAL,
    @SourceID INT,
    @QuantityIDs NVARCHAR(MAX)
)
AS

DECLARE @CombinedDate DATETIME2
SET @CombinedDate = dbo.CombineTimestampAndFractionOfASecond(@TimestampUTC, @FractionOfASecond)

-- Create a temp table to hold the data
CREATE TABLE #GetPQData 
    (
    QuantityID SMALLINT, 
    Value FLOAT
    )

-- make sure at least SOME quantity IDs were passed
IF DATALENGTH(@QuantityIDs) = 0
BEGIN
    -- return no rows
    SELECT
        QuantityID,
        Value
    FROM
        #GetPQData
    WHERE
        0=1

    RETURN
END

-- DETERMINE WHETHER THIS IS A GE HARRIS DEVICE. This is commented out
-- for now (so it is optimized), since each customer will have a 
-- different method of determining the GE Harris devices.

/*
-- local var to indicate whether the device we are
-- querying is a GE Harris device, which needs to
-- be handled in a special way.
DECLARE @UseGEHarrisMethod BIT
SET @UseGEHarrisMethod = 0

-- Get the source name in order to determine if it is ION, GE Harris, or Hathaway
DECLARE @SourceName NVARCHAR(250)
SELECT @SourceName = Name FROM Source WHERE ID=@SourceID
IF @SourceName LIKE 'RTU_%'
BEGIN
    @UseGEHarrisMethod = 1
END

IF @UseGEHarrisMethod
BEGIN
    EXEC GetPQDataGEHarris @CombinedDate, @SourceID, @QuantityIDs
END
ELSE
BEGIN
*/
    -- this is the default handler
    EXEC GetPQDataStandard @CombinedDate, @SourceID, @QuantityIDs
/*
END
*/

-- Return the results
SELECT
    QuantityID,
    Value
FROM
    #GetPQData

-- drop the table
DROP TABLE #GetPQData
GO

IF(dbo.ProcedureExists('spDAL_GetTwoValuesWithContextBefore') = 1)
    DROP PROCEDURE spDAL_GetTwoValuesWithContextBefore
GO

CREATE PROCEDURE [dbo].[spDAL_GetTwoValuesWithContextBefore]
(
    @SourceID AS NVARCHAR(400)
    ,@QuantityID AS NVARCHAR(400)
    ,@CurrentDateTime AS DATETIME2  
)
AS

SELECT TOP 2    
    dl.Value
FROM 
    dbo.DataLog2 dl
WHERE
    dl.SourceID = CONVERT(INT, @SourceID)
    AND dl.QuantityID = CONVERT(SMALLINT, @QuantityID)
    AND dl.TimestampUTC < @CurrentDateTime
ORDER BY
    dl.TimeStampUTC DESC

GO


IF(dbo.ProcedureExists('spDAL_GetValueWithContextAfter') = 1)
    DROP PROCEDURE spDAL_GetValueWithContextAfter
GO

CREATE PROCEDURE [dbo].[spDAL_GetValueWithContextAfter]
(
    @SourceID AS NVARCHAR(400)
    ,@QuantityID AS NVARCHAR(400)
    ,@CurrentDateTime AS DATETIME2
)
AS

SELECT TOP 1
    dl.Value
FROM
    dbo.DataLog2 dl
WHERE
    dl.SourceID = CONVERT(INT, @SourceID)
    AND dl.QuantityID = CONVERT(INT, @QuantityID)
    AND dl.TimestampUTC > @CurrentDateTime
ORDER BY
    dl.TimeStampUTC ASC

GO

IF(dbo.ProcedureExists('spDAL_GetValueWithContextBefore') = 1)
    DROP PROCEDURE spDAL_GetValueWithContextBefore
GO

CREATE PROCEDURE dbo.spDAL_GetValueWithContextBefore
(
    @SourceID AS NVARCHAR(400)
    ,@QuantityID AS NVARCHAR(400)
    ,@UpperDateTime AS DATETIME2
)
AS

SELECT TOP 1
    dl.Value
FROM
    dbo.DataLog2 dl
WHERE
    dl.SourceID = CONVERT(INT, @SourceID)
    AND dl.QuantityID = CONVERT(SMALLINT, @QuantityID)
    AND dl.TimestampUTC < @UpperDateTime
ORDER BY
    dl.TimeStampUTC DESC

GO


IF(dbo.ProcedureExists('RemoveDuplicateDataLogs') = 1)
    DROP PROCEDURE RemoveDuplicateDataLogs
GO

--*****************************************************************************
-- PROCEDURE: RemoveDuplicateDataLogs
--
-- NOTES:
--   Removes all the duplicates from the DataLog2 table.
--*****************************************************************************
CREATE PROCEDURE [dbo].[RemoveDuplicateDataLogs] (
@SourceIDFilter INT = NULL,
@DateFromFilter datetime = NULL,
@DateToFilter datetime = NULL
) 
AS

SET NOCOUNT ON

DECLARE dlCursor CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
SELECT SourceID, TimestampUTC, QuantityID, Value, MAX(ID) AS MaxID, COUNT(*) 
FROM dbo.DataLog2
WHERE (@SourceIDFilter IS NULL OR SourceID = @SourceIDFilter)
AND (@DateFromFilter IS NULL OR TimestampUTC >= @DateFromFilter)
AND (@DateToFilter IS NULL OR TimestampUTC <= @DateToFilter)
GROUP BY QuantityID, TimestampUTC, SourceID, Value
HAVING COUNT(*) > 1

DECLARE @SourceID INT
DECLARE @TimestampUTC DATETIME
DECLARE @QuantityID SMALLINT
DECLARE @Value FLOAT
DECLARE @MaxDatalogID BIGINT
DECLARE @Count INT

-- Open the cursor.
OPEN dlCursor
-- Loop through all the duplicates in the datalog2 table
FETCH NEXT FROM dlCursor  INTO @SourceID, @TimestampUTC, @QuantityID, @Value, @MaxDatalogID, @Count
WHILE @@FETCH_STATUS = 0
BEGIN
    DELETE FROM 
        dbo.DataLog2
    WHERE 
        TimestampUTC = @TimestampUTC 
        AND QuantityID=@QuantityID 
        AND SourceID=@SourceID 
        AND (Value=@Value OR (Value IS NULL AND @Value IS NULL))
        AND ID < @MaxDatalogID 
    FETCH NEXT FROM dlCursor INTO @SourceID, @TimestampUTC, @QuantityID, @Value, @MaxDatalogID, @Count      
END
-- Close and deallocate the cursor.
CLOSE dlCursor
DEALLOCATE dlCursor

RETURN 0
GO


IF(dbo.ViewExists('vwEVT_EventLog') = 1)
    DROP VIEW [dbo].[vwEVT_EventLog]
GO

--*****************************************************************************
-- VIEW: vwEVT_EventLog
--
-- NOTES:
--
-- $Id: //rdsw/dev/cayman/SW_ION_E/database/Components/EventWatcherData/Views/01.vwEVT_EventLog.sql#2 $
--
--*****************************************************************************
CREATE VIEW [dbo].[vwEVT_EventLog]
AS
SELECT     dbo.EventLog2.ID, dbo.EventLog2.SourceID, dbo.EventLog2.TimestampUTC, 
                      dbo.EventLog2.Priority, cause.String AS Cause, causeValue.String AS CauseValue, effect.String AS Effect, effectValue.String AS EffectValue
FROM         dbo.EventLog2 INNER JOIN
                      dbo.Source AS src ON dbo.EventLog2.SourceID = src.ID LEFT OUTER JOIN
                      dbo.EventString AS cause ON dbo.EventLog2.CauseID = cause.ID LEFT OUTER JOIN
                      dbo.EventString AS causeValue ON dbo.EventLog2.CauseValueID = causeValue.ID LEFT OUTER JOIN
                      dbo.EventString AS effect ON dbo.EventLog2.EffectID = effect.ID LEFT OUTER JOIN
                      dbo.EventString AS effectValue ON dbo.EventLog2.EffectValueID = effectValue.ID

GO


IF(dbo.ProcedureExists('spEVT_vwEventLog_GetDistinct') = 1)
    DROP PROCEDURE spEVT_vwEventLog_GetDistinct
GO

--*****************************************************************************
--
-- $Id: //rdsw/dev/cayman/SW_ION_E/database/Components/EventWatcherData/StoredProcedures/01.spEVT_vwEventLog_GetDistinct.sql#2 $
-- 
--*****************************************************************************
CREATE PROCEDURE dbo.spEVT_vwEventLog_GetDistinct

AS
    
SELECT
eld.Priority,
(SELECT String From EventString WHERE ID = eld.CauseID) as Cause,
(SELECT String From EventString WHERE ID = eld.CauseValueID) as CauseValue,
(SELECT String From EventString WHERE ID = eld.EffectID) as Effect,
(SELECT String From EventString WHERE ID = eld.EffectValueID) as EffectValue
FROM
(SELECT DISTINCT
Priority,
CauseID,
CauseValueID,
EffectID,
EffectValueID
FROM
dbo.EventLog2) AS eld

RETURN @@ERROR
GO

IF(dbo.ProcedureExists('UpdateRecorderState') = 1)
    DROP PROCEDURE UpdateRecorderState
GO


--*****************************************************************************
-- PROCEDURE: UpdateRecorderState
--
-- NOTES:
--
-- $Id: //rdsw/dev/cayman/SW_ION_E/database/Components/ION/StoredProcedures/02.UpdateRecorderState.sql#2 $
--
--*****************************************************************************

CREATE PROCEDURE UpdateRecorderState
    @SourceID INT,
    @RecorderName NVARCHAR(50),
    @LastKnownPosition INT,
    @LastTimestampUTC DATETIME2,
    @LogRegister INT
AS
SET NOCOUNT ON
DECLARE @RecorderID INT
IF @RecorderName IS NULL -- Call applies to all recorders
    UPDATE RecorderState SET LastKnownPosition=@LastKnownPosition,LastTimestampUTC=@LastTimestampUTC,LogRegister=@LogRegister WHERE RecorderID IN (SELECT RecorderID FROM Recorder WHERE SourceID=@SourceID)
ELSE -- Specific recorder
BEGIN
    SELECT @RecorderID=ID FROM Recorder WHERE SourceID=@SourceID AND Name=@RecorderName
    UPDATE RecorderState SET LastKnownPosition=@LastKnownPosition,LastTimestampUTC=@LastTimestampUTC,LogRegister=@LogRegister WHERE RecorderID=@RecorderID
    IF @@ROWCOUNT = 0
        INSERT INTO RecorderState (RecorderID,LastKnownPosition,LastTimestampUTC,LogRegister) VALUES (@RecorderID,@LastKnownPosition,@LastTimestampUTC,@LogRegister)
END
RETURN 0
GO

IF(dbo.FunctionExists('FractionalSecondsAsFloat') = 1)
    DROP FUNCTION [dbo].[FractionalSecondsAsFloat]
GO

--*****************************************************************************
-- FUNCTION: FractionalSecondsAsFloat
--
-- NOTES:
--
--*****************************************************************************

CREATE FUNCTION dbo.FractionalSecondsAsFloat
(
    @timestamp AS DATETIME2
)  
RETURNS FLOAT AS  
BEGIN 
    RETURN CONVERT(FLOAT,DATEPART(NANOSECOND,@timestamp)) / 1000000000
END
GO


IF(dbo.ProcedureExists('spGetSourceQuantityCount') = 1)
    DROP PROCEDURE spGetSourceQuantityCount
GO

CREATE PROCEDURE spGetSourceQuantityCount
(
    @QuantityName nvarchar(250),
    @SourceName nvarchar(250),
    @Count int OUTPUT
)
AS

DECLARE @SourceID INT
DECLARE @QuantityID SMALLINT

SELECT @SourceID = id from Source Where name = @SourceName
IF @SourceID IS NULL OR @SourceID=0
BEGIN
   RAISERROR('Source unknown ''%s''', 16, 1, @SourceName)
   RETURN -1
END

SELECT @QuantityID = id FROM Quantity WHERE name = @QuantityName
IF @QuantityID IS NULL OR @QuantityID=0
BEGIN
   RAISERROR('Measurement unknown ''%s''', 16, 1, @QuantityName)
   RETURN -1
END

SET @Count = 0

SELECT 
    @Count = count(*)
FROM 
    dbo.DataLog2
WHERE 
    SourceID=@SourceID 
    AND QuantityID=@QuantityID

RETURN 0

GO

IF(dbo.ProcedureExists('spUpdateSourceAndQuantity') = 1)
    DROP PROCEDURE spUpdateSourceAndQuantity
GO

CREATE PROCEDURE spUpdateSourceAndQuantity
(
    @OldQName nvarchar(250),
    @NewQName nvarchar(250),
    @OldSourceName nvarchar(250),
    @NewSourceName nvarchar(250),
    @Count INT OUTPUT
)
AS

DECLARE @OldSourceID INT
DECLARE @OldQuantityID SMALLINT
DECLARE @NewSourceID INT
DECLARE @NewQuantityID SMALLINT
DECLARE @TimestampUTC DATETIME

SELECT @OldSourceID = id from Source Where name = @OldSourceName
IF @OldSourceID IS NULL OR @OldSourceID=0
BEGIN
   RAISERROR('Original source unknown ''%s''', 16, 1, @OldSourceName)
   RETURN -1
END
SELECT @OldQuantityID = id FROM Quantity WHERE name = @OldQName
IF @OldQuantityID IS NULL OR @OldQuantityID=0
BEGIN
   RAISERROR('Original measurement unknown ''%s''', 16, 1, @OldQName)
   RETURN -1
END
SELECT @NewSourceID = id from Source Where name = @NewSourceName
IF @NewSourceID IS NULL OR @NewSourceID=0
BEGIN
   RAISERROR('New source unknown ''%s''', 16, 1, @NewSourceName)
   RETURN -1
END
SELECT @NewQuantityID = id FROM Quantity WHERE name = @NewQName
IF @NewQuantityID IS NULL OR @NewQuantityID=0
BEGIN
   RAISERROR('New measurement unknown ''%s''', 16, 1, @NewQName)
   RETURN -1
END

UPDATE
    dbo.Datalog2
SET
    SourceID = @NewSourceID,
    QuantityID = @NewQuantityID
WHERE
    SourceId = @OldSourceID
    AND QuantityId = @OldQuantityID

SET @Count = @@Rowcount

RETURN @Count

GO

IF(dbo.ProcedureExists('GetSourceID') = 1)
    DROP PROCEDURE GetSourceID
GO

CREATE PROCEDURE [dbo].[GetSourceID]
(
    @Namespace NVARCHAR(250),
    @Name NVARCHAR(200),
    @SourceType NVARCHAR(50),
    @TimeZone NVARCHAR(100),
    @TimeZoneBias SMALLINT,
    @TimeZoneDaylightBias SMALLINT,
    @ID INT OUTPUT,
    @Consistent BIT OUTPUT
)
AS
SET NOCOUNT ON

DECLARE @NamespaceID SMALLINT
DECLARE @SourceTypeID TINYINT
DECLARE @TimeZoneID TINYINT

DECLARE @OldSourceTypeID TINYINT
DECLARE @OldTimeZoneID TINYINT

SET @ID=NULL

EXEC GetNamespaceID @Namespace, @NamespaceID OUTPUT
EXEC GetSourceTypeID @SourceType, @SourceTypeID OUTPUT
EXEC GetTimeZoneID @TimeZone, @TimeZoneBias, @TimeZoneDaylightBias, @TimeZoneID OUTPUT

BEGIN TRANSACTION 

SELECT @ID=ID,@OldSourceTypeID=SourceTypeID,@OldTimeZoneID=TimeZoneID FROM Source WITH (TABLOCKX) WHERE Name=@Name AND NamespaceID=@NamespaceID

IF @ID IS NULL
BEGIN
    INSERT INTO Source (Name,NamespaceID,SourceTypeID,TimeZoneID) VALUES (@Name,@NamespaceID,@SourceTypeID,@TimeZoneID)
    SET @ID=SCOPE_IDENTITY()
    SET @Consistent=1
END
ELSE IF (@SourceTypeID!=@OldSourceTypeID) OR (@TimeZoneID!=@OldTimeZoneID)
BEGIN
    SET @Consistent=0
END
ELSE
BEGIN
    SET @Consistent=1
END

COMMIT TRANSACTION

GO

IF(dbo.FunctionExists('DateAsCOMTime') = 1)
    DROP FUNCTION [dbo].[DateAsCOMTime]
GO

--*****************************************************************************
-- FUNCTION: DateAsCOMTime
--
-- NOTES:
--
--*****************************************************************************
CREATE FUNCTION dbo.DateAsCOMTime
(
    @timestamp AS DATETIME2
)  
RETURNS FLOAT AS  
BEGIN 
    DECLARE @retval FLOAT
    -- For some reason the COM time representation of Jan 1, 1900 is 2 not 0
    -- so the com time equivalent of SQL Server time needs to be offset by 2 days!!! (weird)
    SET @retval = CONVERT(float, dbo.DateWithoutFraction(@timestamp)) + 2 + (DATEPART(NANOSECOND,@timestamp) / 86400000000000.0)
    RETURN @retval
END
GO

IF(dbo.FunctionExists('udf_SplitDateTimeListToTable') = 1)
    DROP FUNCTION [dbo].[udf_SplitDateTimeListToTable]
GO


--*****************************************************************************
-- FUNCTION: udf_SplitDateTimeListToTable
--
-- NOTES:
--   Takes a string representation of a list of DATETIMEs, splits it into 
--   DATETIME values based on the end-of-item char, and returns it as a table. 
--   NULL values are specified by an empty string. The final end-of-item char 
--   is optional if the last value is non-null.
--
--   To guarantee the date is correctly converted independently of the culture
--   settings on the SQL Server computer, you must use the ISO8601  date 
--   format that looks like this: yyyy-MM-ddTHH:mm:ss.fff.  The fractional 
--   part is optional, and can be no more than 3 digits.
--
-- EXAMPLE 1:
--   SELECT * FROM dbo.udf_SplitDateTimeListToTable (N'2010-12-25T00:05:01^1980-02-22T15:03:00.123',N'^')
--      listpos     timestamp
--      1           2010-12-25 00:05:01.000
--      2           1980-02-22 15:03:00.123
--
-- EXAMPLE 2:
--   SELECT * FROM dbo.udf_SplitDateTimeListToTable (N'2010-12-25T00:05:01^^',N'^')
--      listpos     timestamp
--      1           2010-12-25 00:05:01.000
--      2           NULL
--
--*****************************************************************************
CREATE FUNCTION [dbo].[udf_SplitDateTimeListToTable] 
(
    @list NVARCHAR(MAX),
    @endOfItemChar NCHAR(1) = N','
)
RETURNS @tbl TABLE 
(
    listpos INT PRIMARY KEY IDENTITY(1, 1) NOT NULL,
    timestamp DATETIME NULL
)
AS
BEGIN

    -- local vars
    DECLARE @StartDelimiter INT
    DECLARE @EndDelimiter INT
    DECLARE @TempString NVARCHAR(MAX)

    -- quit early.
    if @list IS NULL
        RETURN
        
    -- add the final delimiter, if omitted. (Use DATALENGTH/2, because LEN trims whitespace.)
    IF SUBSTRING(@list,DATALENGTH(@list)/2,1) <> @endOfItemChar
        SET @list = @list + @endOfItemChar
        
    -- get the first delimiter's position
    SET @StartDelimiter = 0
    SET @EndDelimiter = CHARINDEX(@endOfItemChar, @list, @StartDelimiter+1)
    WHILE @EndDelimiter > 0
    BEGIN

        SET @TempString = SUBSTRING(@list,@StartDelimiter+1, @EndDelimiter-@StartDelimiter-1)       
        
        IF @EndDelimiter-@StartDelimiter = 1
            INSERT @tbl (timestamp) VALUES (NULL)
        ELSE
            INSERT @tbl (timestamp) VALUES (CONVERT(DATETIME,@TempString))
        
        SET @StartDelimiter = @EndDelimiter
        SET @EndDelimiter = CHARINDEX(@endOfItemChar, @list, @StartDelimiter+1)

    END

    RETURN
    
END
GO

IF(dbo.ViewExists('vDataLogChannelValue') = 1)
    DROP VIEW [dbo].[vDataLogChannelValue]
GO

--*****************************************************************************
-- VIEW: vDataLogChannelValue
--
-- NOTES:
--
--*****************************************************************************
CREATE VIEW dbo.vDataLogChannelValue
AS
SELECT  s.Name AS SourceName, 
        ns.Name AS SourceNamespace,
        dl.TimestampUTC,
        dl.[Value], 
        q.Name AS Quantity
FROM    dbo.Source s INNER JOIN
        dbo.Namespace ns on s.NamespaceID = ns.ID INNER JOIN
        dbo.DataLog2 dl ON s.ID = dl.SourceID INNER JOIN
        dbo.Quantity q ON dl.QuantityID = q.ID
GO
    GRANT SELECT ON [dbo].[vDataLogChannelValue] TO [ION_Reader]
    GRANT SELECT ON [dbo].[vDataLogChannelValue] TO [mma_Archiver]
GO

IF(dbo.ViewExists('vOldSchemaDatalog') = 1)
    DROP VIEW [dbo].[vOldSchemaDatalog]
GO

--*****************************************************************************
-- VIEW: vOldSchemaDatalog
--
-- NOTES: Mimics the pre-6.1 schema for DataLog.
--
--*****************************************************************************

CREATE VIEW dbo.vOldSchemaDatalog
AS
SELECT
    CONVERT(INT, ID) AS DataLogStampID,
    Value,
    QuantityID
FROM
    dbo.DataLog2
GO

IF(dbo.ViewExists('vQuantity') = 1)
    DROP VIEW [dbo].[vQuantity]
GO


--*****************************************************************************
-- VIEW: vQuantity
--
-- NOTES:
--
--*****************************************************************************

CREATE VIEW [dbo].[vQuantity]
AS
SELECT
    q.ID AS QuantityID,
    q.Name AS QuantityName,
    q.Unit AS QuantityUnit,
    q.Type AS QuantityType,
    q.Direction AS QuantityDirection,
    q.Description AS QuantityDescription,
    ISNULL((SELECT TOP 1 CONVERT(BIT,1) FROM SourceQuantity p WHERE p.QuantityID = q.ID),0) AS HasData
FROM
    dbo.Quantity q
GO

IF(dbo.ViewExists('vSource') = 1)
    DROP VIEW [dbo].[vSource]
GO


--*****************************************************************************
-- VIEW: vSource
--
-- NOTES:
--
--*****************************************************************************

CREATE VIEW [dbo].[vSource]
AS
SELECT
    s.ID as SourceID,
    s.Name as SourceName,
    s.Description AS SourceDescription,
    s.Signature AS SourceSignature,
    n.ID AS NamespaceID,
    n.Name AS [Namespace],
    st.Name AS SourceType,
    tz.DisplayName AS TimeZoneName,
    (SELECT MIN(p.MinTimestampUtc) FROM SourceQuantity p WHERE p.SourceID = s.ID) AS MinTimestampUTC,
    (SELECT MAX(p.MaxTimestampUtc) FROM SourceQuantity p WHERE p.SourceID = s.ID) AS MaxTimestampUTC
FROM
    dbo.Source s
    JOIN dbo.Namespace n on s.NamespaceID = n.ID
    JOIN SourceType st on s.SourceTypeID = st.ID
    LEFT JOIN dbo.TimeZone tz on s.TimeZoneID = tz.ID
GO

IF(dbo.FunctionExists('udf_SplitIntListToTable') = 1)
    DROP FUNCTION [dbo].[udf_SplitIntListToTable]
GO


--*****************************************************************************
-- FUNCTION: udf_SplitIntListToTable
--
-- NOTES:
--   Takes a string representation of a list of INTs, splits it into INT values
--   based on the end-of-item char, and returns it as a table. NULL values are
--   specified by an empty string. The final end-of-item char is optional if
--   the last value is non-null.
--
-- EXAMPLE 1:
--   @list='1:55:3:4:', @endOfItemChar=':'. 
--
--    listpos     number
--    1           1
--    2           55
--    3           3
--    4           4
--
-- EXAMPLE 2:
--   @list=':55:3::', @endOfItemChar=':'. 
--
--    listpos     number
--    1           NULL
--    2           55
--    3           3
--    4           NULL
--
--*****************************************************************************
CREATE FUNCTION [dbo].[udf_SplitIntListToTable] 
(
    @list NVARCHAR(MAX),
    @endOfItemChar NCHAR(1) = N','
)
RETURNS @tbl TABLE 
(
    listpos INT PRIMARY KEY IDENTITY(1, 1) NOT NULL,
    number INT NULL
)
AS
BEGIN

    -- local vars
    DECLARE @StartDelimiter INT
    DECLARE @EndDelimiter INT
    DECLARE @TempString NVARCHAR(MAX)

    -- quit early.
    if @list IS NULL
        RETURN
        
    -- add the final delimiter, if omitted. (Use DATALENGTH/2, because LEN trims whitespace.)
    IF SUBSTRING(@list,DATALENGTH(@list)/2,1) <> @endOfItemChar
        SET @list = @list + @endOfItemChar
        
    -- get the first delimiter's position
    SET @StartDelimiter = 0
    SET @EndDelimiter = CHARINDEX(@endOfItemChar, @list, @StartDelimiter+1)
    WHILE @EndDelimiter > 0
    BEGIN

        SET @TempString = SUBSTRING(@list,@StartDelimiter+1, @EndDelimiter-@StartDelimiter-1)       
        
        IF @EndDelimiter-@StartDelimiter = 1
            INSERT @tbl (number) VALUES (NULL)
        ELSE
            INSERT @tbl (number) VALUES (CONVERT(INT,@TempString))
        
        SET @StartDelimiter = @EndDelimiter
        SET @EndDelimiter = CHARINDEX(@endOfItemChar, @list, @StartDelimiter+1)

    END

    RETURN
    
END
GO

IF(dbo.FunctionExists('udfPQ_GetPQDataLogEntryWithinThreshold') = 1)
    DROP FUNCTION [dbo].[udfPQ_GetPQDataLogEntryWithinThreshold]
GO


/*
* The MeasurementsAsString parameter is a delimited string that contains specific measurement
* names that are characteristic of a Sag, Swell, or Transient.
*/
CREATE FUNCTION [dbo].[udfPQ_GetPQDataLogEntryWithinThreshold]
(
	@SourceId INT	
	, @TimestampUtc DATETIME2
	, @MeasurementsAsString VARCHAR(MAX)
	, @MsThreshold INT
)
RETURNS TABLE
AS

RETURN
(
	SELECT TOP 1 
		dl.ID
	FROM 
		dbo.DataLog2 dl
		JOIN dbo.Quantity q on dl.QuantityID = q.ID
	WHERE
		dl.TimestampUtc BETWEEN DATEADD(MILLISECOND, -@MsThreshold, @TimestampUtc) AND DATEADD(MILLISECOND, @MsThreshold, @TimestampUtc) 
		AND dl.SourceID = @SourceID
		AND q.Name IN (SELECT nstr FROM dbo.udf_SplitStringListToTable(@MeasurementsAsString,CHAR(6)))
	ORDER BY
		ABS(DATEDIFF(MICROSECOND, @TimestampUTC, dl.TimestampUTC))
)

GO

IF(dbo.FunctionExists('udf_SplitSmallIntListToTable') = 1)
    DROP FUNCTION [dbo].[udf_SplitSmallIntListToTable]
GO


--*****************************************************************************
-- FUNCTION: udf_SplitSmallIntListToTable
--
-- NOTES:
--   Takes a string representation of a list of SMALLINTs, splits it into 
--   SMALLINT values based on the end-of-item char, and returns it as a table. 
--   NULL values are specified by an empty string. The final end-of-item char 
--   is optional if the last value is non-null.
--
-- EXAMPLE 1:
--   @list='1:55:3:4:', @endOfItemChar=':'. 
--
--    listpos     number
--    1           1
--    2           55
--    3           3
--    4           4
--
-- EXAMPLE 2:
--   @list=':55:3::', @endOfItemChar=':'. 
--
--    listpos     number
--    1           NULL
--    2           55
--    3           3
--    4           NULL
--
--*****************************************************************************
CREATE FUNCTION [dbo].[udf_SplitSmallIntListToTable] 
(
    @list NVARCHAR(MAX),
    @endOfItemChar NCHAR(1) = N','
)
RETURNS @tbl TABLE 
(
    listpos INT PRIMARY KEY IDENTITY(1, 1) NOT NULL,
    number SMALLINT NULL
)
AS
BEGIN

    -- local vars
    DECLARE @StartDelimiter INT
    DECLARE @EndDelimiter INT
    DECLARE @TempString NVARCHAR(MAX)

    -- quit early.
    if @list IS NULL
        RETURN
        
    -- add the final delimiter, if omitted. (Use DATALENGTH/2, because LEN trims whitespace.)
    IF SUBSTRING(@list,DATALENGTH(@list)/2,1) <> @endOfItemChar
        SET @list = @list + @endOfItemChar
        
    -- get the first delimiter's position
    SET @StartDelimiter = 0
    SET @EndDelimiter = CHARINDEX(@endOfItemChar, @list, @StartDelimiter+1)
    WHILE @EndDelimiter > 0
    BEGIN

        SET @TempString = SUBSTRING(@list,@StartDelimiter+1, @EndDelimiter-@StartDelimiter-1)       
        
        IF @EndDelimiter-@StartDelimiter = 1
            INSERT @tbl (number) VALUES (NULL)
        ELSE
            INSERT @tbl (number) VALUES (CONVERT(SMALLINT,@TempString))
        
        SET @StartDelimiter = @EndDelimiter
        SET @EndDelimiter = CHARINDEX(@endOfItemChar, @list, @StartDelimiter+1)

    END

    RETURN
    
END
GO

IF(dbo.ProcedureExists('GetPQDataGEHarris') = 1)
    DROP PROCEDURE GetPQDataGEHarris
GO


--*****************************************************************************
/* ##BD_BEGIN

<p>This stored procedure is used by the Event Adapter to load PQ data.</p>
<p>This is specific to GE Harris devices through the FUSION package, because
sometimes we do not get ANY values for phases that were nominal. This sproc
returns NULLs for all phases if at least ONE of the phases has some data.
The EventAdapter turns these NULLs into nominal values in its processing.</p>

##BD_END */
--*****************************************************************************
CREATE PROCEDURE [dbo].[GetPQDataGEHarris]
(
    @TimestampUTC DATETIME2,
    @SourceID INT,
    @QuantityIDs NVARCHAR(MAX)
)
AS

-- local variables
DECLARE @QuantityIDCount INT
DECLARE @RowsInReturnTable INT

-- populate the standard stuff
EXEC GetPQDataStandard @TimestampUTC, @SourceID, @QuantityIDs

-- if we have added rows, meaning we have SOME data at least, then
-- return the table. Otherwise return an empty table to indicate
-- we have NO data.
IF EXISTS (SELECT 1 FROM #GetPQData)
BEGIN

    INSERT INTO 
        #GetPQData (QuantityID, Value)
    SELECT DISTINCT 
        q.ID, 
        NULL 
    FROM 
        dbo.Quantity q
    WHERE
        q.ID IN (SELECT number from dbo.udf_SplitSmallIntListToTable(@QuantityIDs, N','))

END
GO

IF(dbo.ProcedureExists('GetPQDataStandard') = 1)
    DROP PROCEDURE GetPQDataStandard
GO

--*****************************************************************************
/* ##BD_BEGIN

<p>This stored procedure is used by the Event Adapter to load PQ data.</p>

<p>The tolerance is to 5 decimal places, which means 10 microseconds (as
seen in the where clause).</p>

##BD_END */
--*****************************************************************************
CREATE PROCEDURE [dbo].[GetPQDataStandard]
(
    @TimestampUTC DATETIME2,
    @SourceID INT,
    @QuantityIDs NVARCHAR(MAX)
)
AS

INSERT INTO #GetPQData (QuantityID, Value)
    SELECT DISTINCT
        QuantityID,
        Value
    FROM
        dbo.Datalog2 d
        JOIN dbo.udf_SplitSmallIntListToTable(@QuantityIDs, ',') q ON d.QuantityID = q.number
    WHERE
        TimestampUTC BETWEEN DATEADD(MICROSECOND, -10, @TimestampUTC) AND DATEADD(MICROSECOND, 10, @TimestampUTC)
        AND SourceID = @SourceID

GO

IF(dbo.ProcedureExists('spPQ_GetMetaDataForIonPQEvent') = 1)
    DROP PROCEDURE spPQ_GetMetaDataForIonPQEvent
GO


CREATE PROCEDURE dbo.spPQ_GetMetaDataForIonPQEvent
(
	@PQEventType AS NVARCHAR(250)
	,@DataLogID AS BIGINT
)
AS

	-- Get the actual timestamp for the datalog entries, and the sourceID
	DECLARE @DataLogTimestampUTC DATETIME2
	DECLARE @SourceID INT
	SELECT 
		@DataLogTimestampUTC = d.TimestampUTC,
		@SourceID = d.SourceID
	FROM 
		dbo.DataLog2 d 
	WHERE 
		d.ID = @DataLogID
	
	IF @PQEventType = N'Transient'
	BEGIN		
		--GET MAXIMUM VOLTAGES BY PHASE FOR EVENT
		SELECT
			Quantity.[Name] AS [PhaseName]
			,DataLog2.Value AS [PhaseVoltage]
			,(SELECT dlnv.[Value] FROM dbo.DataLog2 dlnv INNER JOIN Quantity qnv ON dlnv.QuantityID = qnv.ID WHERE dlnv.SourceID = @SourceID AND dlnv.TimestampUTC = @DataLogTimestampUTC AND qnv.[Name] = N'Voltage Transient Nominal Voltage') AS [Nominal]
		INTO
			#VoltageByPhase
		FROM 
			dbo.DataLog2
		INNER JOIN
			Quantity ON DataLog2.QuantityID = Quantity.ID
		WHERE
			DataLog2.TimestampUTC = @DataLogTimestampUTC
            AND DataLog2.SourceID = @SourceID
			AND Quantity.Name LIKE N'Voltage Transient Maximum Voltage on Input V_'			
			AND DataLog2.Value = 
				--need to ensure only readings for phase(s) with max voltage are chosen
				(SELECT MAX([Value]) 
				 FROM dbo.DataLog2 dl 
				 INNER JOIN Quantity q ON dl.QuantityID = q.ID
				 WHERE dl.SourceID = @SourceID
				 AND dl.TimestampUTC = @DataLogTimestampUTC
				 AND q.Name LIKE N'Voltage Transient Maximum Voltage on Input V_')
		
		--ONCE PHASES WITH MAX VOLTAGE DEVIATION FOR PQ EVENT ARE IDENTIFIED,
		--CALCULATE METADATA & CHOOSE 1 PHASE WITH MAXIMUM DURATION	
		SELECT TOP 1
			MAX(DataLog2.Value) AS [Duration]
			, tmv.[PhaseVoltage] AS [Magnitude]
			, ABS(tmv.PhaseVoltage-100)*MAX(DataLog2.Value) AS [Severity]
			, RIGHT(tmv.PhaseName, 2) AS [WorstPhase]
			, SubType = 'Transient' --always equal to 'Transient' no sub types for transient events (unlike sag/swell)
		FROM
			dbo.DataLog2
		INNER JOIN
			Quantity ON DataLog2.QuantityID = Quantity.ID
		INNER JOIN
			#VoltageByPhase tmv ON LOWER([Name]) = LOWER(N'Voltage Transient Duration on Input ' + RIGHT(tmv.PhaseName, 2))
		WHERE
			DataLog2.SourceID = @SourceID
			AND DataLog2.TimestampUTC = @DataLogTimestampUTC
		GROUP BY
			tmv.PhaseVoltage, tmv.Nominal, RIGHT(tmv.PhaseName, 2)
		ORDER BY
			MAX(DataLog2.Value) DESC
		
		DROP TABLE #VoltageByPhase
	END
	ELSE IF @PQEventType = N'SagSwellEnd'
	BEGIN
		DECLARE @Duration AS FLOAT
		SET @Duration = (SELECT MAX(dlt.[Value]) FROM dbo.DataLog2 dlt INNER JOIN Quantity qt ON dlt.QuantityID = qt.ID WHERE dlt.SourceID = @SourceID AND dlt.TimestampUTC = @DataLogTimestampUTC AND qt.[Name] = N'Voltage Disturbance Duration')

		SELECT TOP 1
			@Duration AS [Duration]			
			,DataLog2.Value AS [Magnitude]
			,ABS(DataLog2.Value-100) * @Duration AS [Severity]
			,RIGHT(Quantity.[Name],2) AS [WorstPhase]
			,SubType =
				(CASE
					WHEN ABS(DataLog2.Value) > 100 THEN N'Swell'
					WHEN ABS(DataLog2.Value) < 100 THEN N'Sag'
				 END)
		FROM 
			dbo.DataLog2
		INNER JOIN
			Quantity ON DataLog2.QuantityID = Quantity.ID
		WHERE
			DataLog2.SourceID = @SourceID
			AND DataLog2.TimestampUTC = @DataLogTimestampUTC
			AND 
				(Quantity.Name LIKE N'Voltage Disturbance Maximum Voltage on Input V_' 
				OR Quantity.Name LIKE N'Voltage Disturbance Minimum Voltage on Input V_')
		ORDER BY
			ABS(DataLog2.Value-100) * @Duration DESC
	END
GO


IF(dbo.ProcedureExists('spPQ_GetReadingsForEvent') = 1)
    DROP PROCEDURE spPQ_GetReadingsForEvent
GO

CREATE PROCEDURE spPQ_GetReadingsForEvent
	@DataLogId AS BIGINT
AS

	-- Convert the datalogID into a timestamp and source. In versions before 7.0, the same
	-- DataLogStampID was shared across all quantity readings (usually), so we were joining
	-- on it. This is no longer the case, so we'll just translate it into Source+TimestampUTC.
	DECLARE @TimestampUTC DATETIME2
	DECLARE @SourceID INT
	SELECT TOP 1
		@TimestampUTC = TimestampUTC,
		@SourceID = SourceID
	FROM 
		dbo.DataLog2 
	WHERE 
		ID = @DataLogID
		
	SELECT
		[QuantityID] AS [ReadingId]
		, [Value] AS [ReadingValue]
		, [Name] AS [ReadingName]
	FROM
		dbo.DataLog2
	INNER JOIN
		dbo.Quantity ON (DataLog2.QuantityID = Quantity.ID)
	WHERE
		Datalog2.SourceID = @SourceID
		AND Datalog2.TimestampUTC = @TimestampUTC
GO

IF(dbo.ProcedureExists('spPQ_GetSources') = 1)
    DROP PROCEDURE spPQ_GetSources
GO

CREATE PROCEDURE spPQ_GetSources
AS
SELECT
    DISTINCT EventLog2.[SourceID] AS [SourceId]
FROM
    dbo.EventLog2
INNER JOIN
    dbo.Source ON (EventLog2.[SourceID] = Source.[ID])
INNER JOIN
    dbo.SourceType ON (Source.[SourceTypeID] = SourceType.[ID])
ORDER BY
    [SourceId]
GO


IF(dbo.ProcedureExists('GetDatalogData') = 1)
    DROP PROCEDURE GetDatalogData
GO

--*****************************************************************************
-- Name: GetDatalogData
--
-- NOTES: Return the readings for the specified criteria. @SourceIDs and 
--        @QuantityIDs are comma-delimited lists.
--
--*****************************************************************************

CREATE PROCEDURE [dbo].[GetDatalogData]
(
    @SourceIDs NVARCHAR(MAX),
    @QuantityIDs NVARCHAR(MAX),
    @MinTimestampUTC DATETIME2,
    @MaxTimestampUTC DATETIME2
)
AS
SELECT
    d.SourceID,
    d.QuantityID,
    d.TimestampUTC,
    d.Value
FROM
    dbo.Datalog2 d
    JOIN dbo.udf_SplitIntListToTable(@SourceIDs, ',') sf ON d.SourceID = sf.number
    JOIN dbo.udf_SplitSmallIntListToTable(@QuantityIDs, ',') qf ON d.QuantityID = qf.number
WHERE
    d.TimestampUTC > @MinTimestampUTC
    AND d.TimestampUTC <= @MaxTimestampUTC
ORDER BY
    d.TimestampUTC, 
    d.SourceID,
    d.QuantityID
GO


IF(dbo.ProcedureExists('GetDatalogDataByName') = 1)
    DROP PROCEDURE GetDatalogDataByName
GO

--*****************************************************************************
-- Name: GetDatalogDataByName
--
-- NOTES: Return the readings for the specified criteria. @SourceNames and 
--        @QuantityNames are comma-delimited lists.
--
--*****************************************************************************
CREATE PROCEDURE [dbo].[GetDatalogDataByName]
(
    @SourceNames NVARCHAR(MAX),
    @QuantityNames NVARCHAR(MAX),
    @MinTimestampUTC DATETIME2,
    @MaxTimestampUTC DATETIME2
)
AS
-- Build temp table variables to hold the values we're joining against.
-- This isn't quite as concise as placing the udf calls in the main 
-- select statement but it reduces the query cost.
DECLARE @sources TABLE ( id INT PRIMARY KEY, name NVARCHAR(250) COLLATE database_default ) 
INSERT INTO @sources ( id, name ) 
SELECT src.ID, list.nstr FROM dbo.udf_SplitStringListToTable(@SourceNames, ',') list
    JOIN dbo.Source src ON src.Name = list.nstr

DECLARE @quantities TABLE ( id SMALLINT PRIMARY KEY, name NVARCHAR(250) COLLATE database_default )
INSERT INTO @quantities (id, name) 
SELECT q.ID, list.nstr FROM dbo.udf_SplitStringListToTable(@QuantityNames, ',') list
    JOIN dbo.Quantity q ON q.Name = list.nstr

-- Now do the main data query       
SELECT
    src.Name as SourceName,
    quant.Name as QuantityName,
    d.TimestampUTC,
    d.Value
FROM
    dbo.Datalog2 d
    JOIN @sources src ON src.id = d.SourceID
    JOIN @quantities quant ON quant.id = d.QuantityID
WHERE
    d.TimestampUTC > @MinTimestampUTC
    AND d.TimestampUTC <= @MaxTimestampUTC
ORDER BY
    d.TimestampUTC, 
    d.SourceID,
    d.QuantityID
GO


IF(dbo.ViewExists('vSourceQuantityName') = 1)
    DROP VIEW vSourceQuantityName
GO

--*****************************************************************************
-- VIEW: vSourceQuantityName
--
-- NOTES:
--  Provides a view of the SourceQuantity table that includes the Source
--  and Quantity names.
--
--*****************************************************************************
CREATE VIEW [dbo].[vSourceQuantityName]
AS
SELECT 
      [SourceID]
    , S.[Name] AS SourceName
    , [QuantityID]
    , Q.[Name] AS QuantityName
    , [MinTimestampUtc]
    , [MaxTimestampUtc]
FROM 
    [dbo].[SourceQuantity] AS SQ
JOIN 
    [dbo].[Source] AS S 
    ON S.ID = SQ.SourceID
JOIN 
    [dbo].[Quantity] AS Q 
    ON Q.ID = SQ.QuantityID
GO


IF(dbo.ProcedureExists('GetValueFromDataLog') = 1)
    DROP PROCEDURE GetValueFromDataLog
GO

--*****************************************************************************
-- PROCEDURE: GetValueFromDataLog
--
-- NOTES:
--   Returns the value (if it exists) for the specified source, time and quantity.
--
--*****************************************************************************
CREATE PROCEDURE [dbo].[GetValueFromDataLog] 
(
    @SourceID INT,
    @TimestampUTC DATETIME2,
    @QuantityID SMALLINT,
    @Value FLOAT OUTPUT
)
AS
SET NOCOUNT ON
SELECT @Value=dl.Value FROM dbo.DataLog2 dl
WHERE dl.TimestampUTC=@TimestampUTC AND
    dl.SourceID=@SourceID AND
    dl.QuantityID=@QuantityID
RETURN 0
GO
GRANT EXECUTE ON [dbo].[GetValueFromDataLog] TO [mma_Archiver]
GO

IF(dbo.ProcedureExists('InsertDataLogBulk') = 1)
    DROP PROCEDURE InsertDataLogBulk
GO

--*****************************************************************************
-- PROCEDURE: InsertDataLogBulk
--
-- NOTES: Insert multiple interval values for a given source and quantity.
--
-- The parameters that are lists (@TimestampUTC, @FractionOfASecond, @Values)
-- must be delimited by the @DelimiterChar. See the dbo.udf_SplitFloatListToTable
-- for more information on how to pass NULLs, etc.
--
-- ION-Enterprise 7.0: this stored procedure should remain in the product! It
-- provides a legacy ETL Package to interact wth 7.0 databases. This stored
-- procedure allows @FractionOfASeconds parameter to be specified.
--
--
--*****************************************************************************
CREATE PROCEDURE [dbo].[InsertDataLogBulk]
(
    @SourceID INT,
    @QuantityID SMALLINT,
    @TimestampUTCs NVARCHAR(MAX),
    @FractionOfASeconds NVARCHAR(MAX),
    @Values NVARCHAR(MAX),
    @DelimiterChar NCHAR(1)  -- the character used to separate values in the preceeding parameters.
)
AS

SET NOCOUNT OFF 

-- validation
IF NOT EXISTS (SELECT 1 FROM dbo.Source WHERE ID=@SourceID)
    RETURN -1
IF NOT EXISTS (SELECT 1 FROM dbo.Quantity WHERE ID=@QuantityID)
    RETURN -2
    
-- parse the timestamps into a table variable, so we don't need to parse it twice
DECLARE @Timestamps TABLE (listpos INT, TimestampUTC DATETIME2)
INSERT INTO 
    @Timestamps
select 
    Timestamps.listpos, 
    dbo.CombineTimestampAndFractionOfASecond(Timestamps.Timestamp, Fractions.number)
FROM 
    dbo.udf_SplitDateTimeListToTable(@TimestampUTCs, @DelimiterChar) Timestamps
    JOIN dbo.udf_SplitRealListToTable(@FractionOfASeconds, @DelimiterChar) Fractions ON Timestamps.listpos = Fractions.listpos

    
SET ANSI_WARNINGS OFF

MERGE
    dbo.DataLog2 AS target
USING
    (
SELECT
    @SourceID,
    @QuantityID,
            Timestamps.TimestampUTC,
            CASE WHEN (ISNULL(MAX(ABS([Values].number)),-1) <= ISNULL(MAX([VALUES].number),-2))
                THEN MAX([Values].number)
                ELSE MIN([VALUES].number)
            END
FROM
            @Timestamps Timestamps
            JOIN dbo.udf_SplitFloatListToTable(@Values, @DelimiterChar) [Values] ON Timestamps.listpos = [Values].listpos
        GROUP BY
            Timestamps.TimestampUTC
    ) 
    AS source (SourceID, QuantityID, TimestampUTC, Value)
    ON (target.SourceID = source.SourceID AND target.QuantityID = source.QuantityID AND target.TimestampUTC = source.TimestampUTC)
WHEN MATCHED AND ISNULL(ABS(target.Value),-1) < ISNULL(ABS(source.value),-2) THEN
    UPDATE SET
        target.Value = source.value
WHEN NOT MATCHED THEN
    INSERT (SourceID, QuantityID, TimestampUTC, Value)
    VALUES (@SourceID, @QuantityID, source.TimestampUTC, source.Value);

SET ANSI_WARNINGS ON

MERGE 
    dbo.SourceQuantity AS target
USING
(
        SELECT 
            @SourceID, 
            @QuantityID,
            MAX(Timestamps.TimestampUTC) AS MaxTimestampUTC,
            MIN(Timestamps.TimestampUTC) AS MinTimestampUTC
        FROM
            @Timestamps Timestamps
)
    AS source (SourceID, QuantityID, MaxTimestampUTC, MinTimestampUTC)
    ON (target.SourceID = source.SourceID AND target.QuantityID = source.QuantityID)
    WHEN MATCHED AND (target.MinTimestampUTC > source.MinTimestampUTC OR target.MaxTimestampUTC < source.MaxTimestampUTC) 
    THEN
    UPDATE SET 
        target.MinTimestampUTC = dbo.MinDate2(source.MinTimestampUTC, target.MinTimestampUTC), 
        target.MaxTimestampUTC = dbo.MaxDate2(source.MaxTimestampUTC, target.MaxTimestampUTC)
    WHEN NOT MATCHED THEN   
        INSERT (SourceID, QuantityID, MinTimestampUTC, MaxTimestampUTC)
        VALUES (SourceID, QuantityID, source.MinTimestampUTC, source.MaxTimestampUTC);

SET NOCOUNT OFF

GO

IF(dbo.ProcedureExists('InsertDataLog2Bulk') = 1)
    DROP PROCEDURE InsertDataLog2Bulk
GO

--*****************************************************************************
-- PROCEDURE: InsertDataLog2Bulk
--
-- NOTES: Insert multiple interval values for a given source and quantity.
--
-- The parameters that are lists (@TimestampUTC, @Values)
-- must be delimited by the @DelimiterChar. See the dbo.udf_SplitFloatListToTable
-- for more information on how to pass NULLs, etc.
--
-- See the comments on the InsertDataLog procedure on duplicate reading handling.
--
--*****************************************************************************
CREATE PROCEDURE [dbo].[InsertDataLog2Bulk]
(
    @SourceID INT,
    @QuantityID SMALLINT,
    @TimestampUTCs NVARCHAR(MAX),
    @Values NVARCHAR(MAX),
    @DelimiterChar NCHAR(1)  -- the character used to separate values in the preceeding parameters.
)
AS

SET NOCOUNT OFF 

-- validation
IF NOT EXISTS (SELECT 1 FROM dbo.Source WHERE ID=@SourceID)
    RETURN -1
IF NOT EXISTS (SELECT 1 FROM dbo.Quantity WHERE ID=@QuantityID)
    RETURN -2
    
-- parse the timestamps into a table variable, so we don't need to parse it twice
DECLARE @Timestamps TABLE (listpos INT, TimestampUTC DATETIME2)
INSERT INTO 
    @Timestamps
select 
    Timestamps.listpos, 
    Timestamps.Timestamp 
FROM 
    dbo.udf_SplitDateTime2ListToTable(@TimestampUTCs, @DelimiterChar) Timestamps

    
SET ANSI_WARNINGS OFF

MERGE
    dbo.DataLog2 AS target
USING
    (
SELECT
    @SourceID,
    @QuantityID,
            Timestamps.TimestampUTC,
            CASE WHEN (ISNULL(MAX(ABS([Values].number)),-1) <= ISNULL(MAX([VALUES].number),-2))
                THEN MAX([Values].number)
                ELSE MIN([VALUES].number)
            END
FROM
            @Timestamps Timestamps
    JOIN dbo.udf_SplitFloatListToTable(@Values, @DelimiterChar) [Values] ON Timestamps.listpos = [Values].listpos
        GROUP BY
            Timestamps.TimestampUTC
)
    AS source (SourceID, QuantityID, TimestampUTC, Value)
    ON (target.SourceID = source.SourceID AND target.QuantityID = source.QuantityID AND target.TimestampUTC = source.TimestampUTC)
WHEN MATCHED AND ISNULL(ABS(target.Value),-1) < ISNULL(ABS(source.value),-2) THEN
    UPDATE SET
        target.Value = source.value
WHEN NOT MATCHED THEN
    INSERT (SourceID, QuantityID, TimestampUTC, Value)
    VALUES (@SourceID, @QuantityID, source.TimestampUTC, source.Value);

SET ANSI_WARNINGS ON

MERGE 
    dbo.SourceQuantity AS target
USING
    ( 
        SELECT 
            @SourceID, 
            @QuantityID,
            MAX(Timestamps.TimestampUTC) AS MaxTimestampUTC,
            MIN(Timestamps.TimestampUTC) AS MinTimestampUTC
        FROM
            @Timestamps Timestamps
    )
    AS source (SourceID, QuantityID, MaxTimestampUTC, MinTimestampUTC)
    ON (target.SourceID = source.SourceID AND target.QuantityID = source.QuantityID)
    WHEN MATCHED AND (target.MinTimestampUTC > source.MinTimestampUTC OR target.MaxTimestampUTC < source.MaxTimestampUTC) 
    THEN
    UPDATE SET 
        target.MinTimestampUTC = dbo.MinDate2(source.MinTimestampUTC, target.MinTimestampUTC), 
        target.MaxTimestampUTC = dbo.MaxDate2(source.MaxTimestampUTC, target.MaxTimestampUTC)
    WHEN NOT MATCHED THEN   
        INSERT (SourceID, QuantityID, MinTimestampUTC, MaxTimestampUTC)
        VALUES (SourceID, QuantityID, source.MinTimestampUTC, source.MaxTimestampUTC);

SET NOCOUNT OFF

GO

IF(dbo.ProcedureExists('IONEEM_GetAllSources') = 1)
    DROP PROCEDURE IONEEM_GetAllSources
GO

--*****************************************************************************
-- PROCEDURE: IONEEM_GetAllSources
--
-- NOTES: Return all the sources in ION_Data
--
--*****************************************************************************

CREATE PROCEDURE dbo.IONEEM_GetAllSources
AS

SELECT 
    s.ID AS 'SourceID', 
    s.Name AS 'SourceName',
    t.Name AS 'SourceTypeName',
    n.Name as 'NamespaceName',
    z.DisplayName AS 'TimeZoneName'
FROM 
    dbo.Source s 
    JOIN dbo.Namespace n on s.NamespaceID = n.ID 
    JOIN dbo.SourceType AS t ON t.ID = s.SourceTypeID
    JOIN dbo.TimeZone AS z ON z.ID = s.TimeZoneID
ORDER BY 
    s.ID

RETURN 0

GO

IF(dbo.ProcedureExists('IONEEM_GetEvents') = 1)
    DROP PROCEDURE IONEEM_GetEvents
GO

--*****************************************************************************
-- PROCEDURE: IONEEM_GetEvents
--
-- NOTES: Return EventLog rows that match the criteria. 
--
-- The Start and End EventIDs are inclusive.
--
-- The Cause/CauseValue/Effect/EffectValue strings are compared using LIKE, so
-- they can contain wildcards. Also, if the value is NULL, then that column's
-- value is ignored.
--
--*****************************************************************************

CREATE PROCEDURE dbo.IONEEM_GetEvents
(
    @SourceID INT,
    @StartEventID INT,
    @EndEventID INT,
    @CauseString NVARCHAR(3998),
    @CauseValueString NVARCHAR(3998),
    @EffectString NVARCHAR(3998),
    @EffectValueString NVARCHAR(3998)
)
AS

SELECT 
    0 AS EventID,
    e.ID AS IONEventID,
    n.name AS SourceNamespace,
    s.name AS SourceName,
    e.SourceID,
    e.TimestampUTC,
    s1.String AS Cause,
    s2.String AS CauseValue,
    s3.String AS Effect,
    s4.String AS EffectValue
FROM 
    dbo.EventLog2 e
    JOIN dbo.Source s ON e.SourceID = s.ID
    JOIN dbo.Namespace n ON s.NamespaceID = n.ID
    LEFT JOIN dbo.EventString s1 ON e.CauseID = s1.ID
    LEFT JOIN dbo.EventString s2 ON e.CauseValueID = s2.ID
    LEFT JOIN dbo.EventString s3 ON e.EffectID = s3.ID
    LEFT JOIN dbo.EventString s4 ON e.EffectValueID = s4.ID
WHERE 
    e.ID BETWEEN @StartEventID AND @EndEventID
    AND (@CauseString IS NULL OR s1.string LIKE @CauseString)
    AND (@CauseValueString IS NULL OR s2.string LIKE @CauseValueString)
    AND (@EffectString IS NULL OR s3.string LIKE @EffectString)
    AND (@EffectValueString IS NULL OR s4.string LIKE @EffectValueString)
    AND SourceID = @SourceID
ORDER BY 
    e.ID

RETURN 0

GO

IF(dbo.ProcedureExists('InsertEventAcknowledgement') = 1)
    DROP PROCEDURE InsertEventAcknowledgement
GO

--*****************************************************************************
-- PROCEDURE: InsertEventAcknowledgement
--*****************************************************************************

CREATE PROCEDURE [dbo].[InsertEventAcknowledgement]
(
    @EventLogID int,
    @UserName NVARCHAR(200),
    @Remark NVARCHAR(2000)
)
AS
    SET NOCOUNT ON

    INSERT INTO EventAcknowledgement(EventLogID,UserName,Remark)
    VALUES (@EventLogID, @UserName, @Remark)
    RETURN 0

GO

IF(dbo.FunctionExists('udf_SplitDateTime2ListToTable') = 1)
    DROP FUNCTION [dbo].[udf_SplitDateTime2ListToTable]
GO

--*****************************************************************************
-- FUNCTION: udf_SplitDateTime2ListToTable
--
-- NOTES:
--   Takes a string representation of a list of DATETIME2s, splits it into 
--   DATETIME2 values based on the end-of-item char, and returns it as a table. 
--   NULL values are specified by an empty string. The final end-of-item char 
--   is optional if the last value is non-null.
--
--   To guarantee the date is correctly converted independently of the culture
--   settings on the SQL Server computer, you must use the ISO8601  date 
--   format that looks like this: yyyy-MM-ddTHH:mm:ss.fff.  The fractional 
--   part is optional.
--
-- EXAMPLE 1:
--   SELECT * FROM dbo.udf_SplitDateTime2ListToTable (N'2010-12-25T00:05:01^1980-02-22T15:03:00.1234567',N'^')
--      listpos     timestamp
--      1           2010-12-25 00:05:01.000
--      2           1980-02-22 15:03:00.123
--
-- EXAMPLE 2:
--   SELECT * FROM dbo.udf_SplitDateTime2ListToTable (N'2010-12-25T00:05:01^^',N'^')
--      listpos     timestamp
--      1           2010-12-25 00:05:01.000
--      2           NULL
--
--*****************************************************************************
CREATE FUNCTION [dbo].[udf_SplitDateTime2ListToTable] 
(
    @list NVARCHAR(MAX),
    @endOfItemChar NCHAR(1) = N','
)
RETURNS @tbl TABLE 
(
    listpos INT PRIMARY KEY IDENTITY(1, 1) NOT NULL,
    timestamp DATETIME2 NULL
)
AS
BEGIN

    -- local vars
    DECLARE @StartDelimiter INT
    DECLARE @EndDelimiter INT
    DECLARE @TempString NVARCHAR(MAX)

    -- quit early.
    if @list IS NULL
        RETURN
        
    -- add the final delimiter, if omitted. (Use DATALENGTH/2, because LEN trims whitespace.)
    IF SUBSTRING(@list,DATALENGTH(@list)/2,1) <> @endOfItemChar
        SET @list = @list + @endOfItemChar
        
    -- get the first delimiter's position
    SET @StartDelimiter = 0
    SET @EndDelimiter = CHARINDEX(@endOfItemChar, @list, @StartDelimiter+1)
    WHILE @EndDelimiter > 0
    BEGIN

        SET @TempString = SUBSTRING(@list,@StartDelimiter+1, @EndDelimiter-@StartDelimiter-1)       
        
        IF @EndDelimiter-@StartDelimiter = 1
            INSERT @tbl (timestamp) VALUES (NULL)
        ELSE
            INSERT @tbl (timestamp) VALUES (CONVERT(DATETIME2,@TempString))
        
        SET @StartDelimiter = @EndDelimiter
        SET @EndDelimiter = CHARINDEX(@endOfItemChar, @list, @StartDelimiter+1)

    END

    RETURN
    
END
GO

IF(dbo.FunctionExists('udf_SplitIntListsToTable') = 1)
    DROP FUNCTION [dbo].[udf_SplitIntListsToTable]
GO

--*****************************************************************************
/* ##FND_BEGIN Name ^^

Based on other udf_Split* functions (which were based on http://www.sommarskog.se/arrays-in-sql.html).

Take an NTEXT representation of two CSV lists int strings, split it based on the
delimiter, and return it in a table.

<P><I>For more information, follow the Source link at the top of the page.</I></P>
##FND_END */
--
-- $Id: //rdsw/dev/cayman/SW_ION_E/database/Components/GEN_General/UserDefinedFunctions/10.udf_SplitIntListsToTable.sql#2 $
--
--*****************************************************************************
CREATE FUNCTION [dbo].[udf_SplitIntListsToTable]
(
    @lowerBounds NVARCHAR(MAX),
    @upperBounds NVARCHAR(MAX),
    @delimiter NCHAR(1) = N','
)
RETURNS @tbl TABLE
(
    listpos INT NOT NULL,
    LowerBound INT,
    UpperBound INT
)
AS
BEGIN

    INSERT INTO @tbl
    (
        listpos,
        LowerBound,
        UpperBound
    )
    SELECT
        l.listpos,
        l.number,
        u.number
    FROM
        dbo.udf_SplitIntListToTable(@lowerBounds, @delimiter) l
        JOIN dbo.udf_SplitIntListToTable(@UpperBounds, @delimiter) u
            ON l.listpos = u.listpos

    RETURN

END

GO

IF(dbo.FunctionExists('udf_SplitDateListsToTable') = 1)
    DROP FUNCTION [dbo].[udf_SplitDateListsToTable]
GO


--*****************************************************************************
/* ##FND_BEGIN Name ^^

Based on other udf_Split* functions (which were based on http://www.sommarskog.se/arrays-in-sql.html).

Take an NTEXT representation of two CSV lists date strings, split it based on the 
delimiter, and return it in a table.

<P><I>For more information, follow the Source link at the top of the page.</I></P>
##FND_END */
--
-- $Id: //rdsw/dev/cayman/SW_ION_E/database/Components/GEN_General/UserDefinedFunctions/10.udf_SplitDateListsToTable.sql#2 $
-- 
--*****************************************************************************
CREATE FUNCTION dbo.udf_SplitDateListsToTable
(
    @StartDates NVARCHAR(MAX),
    @EndDates NVARCHAR(MAX),
    @delimiter NCHAR(1) = N','
)
RETURNS @tbl TABLE 
(
    listpos int NOT NULL,
    StartDate DATETIME,
    EndDate DATETIME
)
AS
BEGIN

    INSERT INTO @tbl
    (
        listpos, 
        StartDate, 
        EndDate
    )
    SELECT
        s.listpos,
        s.timestamp,
        e.timestamp
    FROM
        dbo.udf_SplitDateTimeListToTable(@StartDates, @delimiter) s
        JOIN dbo.udf_SplitDateTimeListToTable(@EndDates, @delimiter) e
            ON s.listpos = e.listpos

    RETURN

END
GO

IF(dbo.FunctionExists('FractionalSecondsAsReal') = 1)
    DROP FUNCTION [dbo].[FractionalSecondsAsReal]
GO


--*****************************************************************************
-- FUNCTION: FractionalSecondsAsReal
--
-- NOTES:
--
--*****************************************************************************

CREATE FUNCTION dbo.FractionalSecondsAsReal
(
    @timestamp AS DATETIME2
)  
RETURNS REAL AS  
BEGIN 
    RETURN CONVERT(REAL, dbo.FractionalSecondsAsFloat(@timestamp))
END
GO



ALTER TABLE [dbo].[WaveformLog2] ALTER COLUMN [SamplingFrequency] [float] NULL
ALTER TABLE [dbo].[WaveformLog2] ALTER COLUMN [Scale] [float] NULL
ALTER TABLE [dbo].[WaveformLog2] ALTER COLUMN [Offset] [int] NULL
GO

IF(dbo.ConstraintExists('WaveformLog2', 'PK_WaveformLog2_ID') = 10)
BEGIN
    ALTER TABLE [dbo].[WaveformLog2] ADD CONSTRAINT [PK_WaveformLog2_ID] PRIMARY KEY NONCLUSTERED  ([ID])
END
GO

IF(dbo.ProcedureExists('IONEEM_GetMaxWaveformID') = 1)
    DROP PROCEDURE IONEEM_GetMaxWaveformID
GO

--*****************************************************************************
-- PROCEDURE: IONEEM_GetMaxWaveformID
--
-- NOTES: Return the maximum WaveformLog2.ID, or zero if no waveforms exist.
--
--*****************************************************************************

CREATE PROCEDURE dbo.IONEEM_GetMaxWaveformID
AS

SELECT 
    ISNULL(MAX(ID),0) 
FROM 
    dbo.WaveformLog2

RETURN 0

GO

ALTER TABLE [dbo].[Channel] ALTER COLUMN [IsCurrentConfiguration] [bit] NOT NULL
GO

IF(dbo.ProcedureExists('IONEEM_GetWaveforms') = 1)
    DROP PROCEDURE IONEEM_GetWaveforms
GO

--*****************************************************************************
-- PROCEDURE: IONEEM_GetWaveforms
--
-- NOTES: Return WaveformLog rows that match the criteria. 
--
-- The Start and End WaveformIDs are inclusive.
--
--*****************************************************************************

CREATE PROCEDURE dbo.IONEEM_GetWaveforms
(
    @SourceID INT,
    @StartWaveformID INT,
    @EndWaveformID INT,
    @MaxRowsToReturn INT
)
AS

SELECT TOP (@MaxRowsToReturn)
    w.ID AS WaveformID,
    w.SourceID,
    s.Name AS SourceName, 
    n.Name AS SourceNamespace,
    w.TimestampUTC, 
    w.QuantityID,
    q.Name AS QuantityName,
    w.TimeOfTriggerUTC, 
    w.TimeOfFirstPointUTC, 
    w.SamplingFrequency, 
    w.Scale, 
    w.Offset, 
    w.DataPointType, 
    w.Samples
FROM
    dbo.WaveformLog2 w
    JOIN dbo.Source s ON w.SourceID = s.ID
    JOIN dbo.Namespace n ON s.NamespaceID = n.ID
    JOIN dbo.Quantity q ON w.QuantityID = q.ID
WHERE 
    w.ID BETWEEN @StartWaveformID AND @EndWaveformID
    AND SourceID = @SourceID
ORDER BY 
    w.ID

RETURN 0

GO

IF(dbo.ProcedureExists('InsertDataLog') = 1)
    DROP PROCEDURE [dbo].[InsertDataLog]
GO


--*****************************************************************************
-- PROCEDURE: InsertDataLog
--
-- NOTES: This inserts or updates the DataLog value for the given SourceID, 
-- QuantityID and TimestampUTC.  The SourceQuantity table is also updated as
-- required.
--
-- NOTE: SQT = Source/Quantity/TimestampUTC triple
-- 
-- For duplicate value handling in DataLog, the logic is:
--
--   if (no data exists for the SQT) then
--     insert a new row with the data
--   else
--     if (new value is not NULL and its absolute value is larger than the existing value)
--       update the value
--     endif
--   endif 
--
-- For the SourceQuantity table, the logic is that the row is only updated
-- if the MinTimestampUTC or MaxTimestampUTC value has changed.
--
--
-- To debug "duplicate value" issues, you can uncomment the block of code below -- the
-- part just below "SET NOCOUNT ON", but first you must create the DataLog2Duplicate 
-- table and index by running the following code:
--CREATE TABLE dbo.DataLog2Duplicate
--(
--  ID INT NOT NULL IDENTITY(1,1),
--  SourceID INT NOT NULL,
--  QuantityID SMALLINT NOT NULL,
--  TimestampUTC DATETIME2 NOT NULL,
--  CreatedDateUTC DATETIME2 NOT NULL,
--  Value FLOAT NULL
--)
--GO

--CREATE CLUSTERED INDEX 
--  [CIX_DataLog2Duplicate_SourceID_QuantityID_TimestampUTC] 
--ON 
--  [dbo].[DataLog2Duplicate] 
--  (
--      [SourceID] ASC,
--      [QuantityID] ASC,
--      [TimestampUTC] ASC,
--      [CreatedDateUTC] ASC
--  )
--*****************************************************************************
CREATE PROCEDURE [dbo].[InsertDataLog]
(
    @SourceID INT,
    @QuantityID SMALLINT,
    @TimestampUTC DATETIME2,
    @Value FLOAT
)
AS

SET NOCOUNT ON

-- -- Uncomment this block if you want to debug "duplicate readings" issues. See the comments in the header block
-- -- of this file/procedure for more information. Caution: this will impact insertion performance significantly.
--IF EXISTS (SELECT 1 FROM dbo.DataLog2 WHERE SourceID=@SourceID AND QuantityID=@QuantityID AND TimestampUTC = @TimestampUTC)
--BEGIN
--  INSERT INTO dbo.DataLog2Duplicate (SourceID, QuantityID, TimestampUTC, CreatedDateUTC, Value)
--  VALUES (@SourceID, @QuantityID, @TimestampUTC, GETUTCDATE(), @Value)
--END


MERGE
    dbo.DataLog2 AS target
USING
    (SELECT @SourceID, @QuantityID, @TimestampUTC) AS source (SourceID, QuantityID, TimestampUTC)
    ON (target.SourceID = source.SourceID AND target.QuantityID = source.QuantityID AND target.TimestampUTC = source.TimestampUTC)
WHEN MATCHED AND ISNULL(ABS(target.Value),-1) < ISNULL(ABS(@Value),-2) THEN
    UPDATE SET
        target.Value = @Value
WHEN NOT MATCHED THEN
    INSERT (SourceID, QuantityID, TimestampUTC, Value)
    VALUES (@SourceID, @QuantityID, @TimestampUTC, @Value);

MERGE 
    dbo.SourceQuantity AS target
USING 
    (SELECT @SourceID, @QuantityID) AS source (SourceID, QuantityID)
    ON (target.SourceID = source.SourceID AND target.QuantityID = source.QuantityID)
WHEN MATCHED AND (target.MinTimestampUTC > @TimestampUTC OR target.MaxTimestampUTC < @TimestampUTC) THEN
    UPDATE SET 
        target.MinTimestampUTC = dbo.MinDate2(@TimestampUTC, target.MinTimestampUTC), 
        target.MaxTimestampUTC = dbo.MaxDate2(@TimestampUTC, target.MaxTimestampUTC)
WHEN NOT MATCHED THEN   
    INSERT (SourceID, QuantityID, MinTimestampUTC, MaxTimestampUTC)
    VALUES (SourceID, QuantityID, @TimestampUTC, @TimestampUTC);

RETURN 0

GO

    GRANT EXECUTE ON [dbo].[InsertDataLog] TO [mma_Archiver]
GO

IF(dbo.ProcedureExists('InsertWaveformLog') = 1)
    DROP PROCEDURE InsertWaveformLog
GO

--*****************************************************************************
-- PROCEDURE: InsertWaveformLog
--
-- NOTES: 
--
-- Sept 2011 - Updated to utilize VARBINARY(MAX) datatype. This datatype, 
-- in conjunction with the HASHBYTES checksum function allows the equality of 
-- binary values to be evaluated.
--
-- Does not check for duplicates unless PreventDuplicates is set to 1.
-- ******************************************************************************/
CREATE  PROCEDURE InsertWaveformLog
(
    @SourceID INT,
    @TimestampUTC DATETIME2,
    @QuantityID SMALLINT,
    @TimeOfTriggerUTC DATETIME2,
    @TimeOfFirstPointUTC DATETIME2,
    @SamplingFrequency FLOAT=0.0,
    @Scale FLOAT=0.0,
    @Offset INT=0,
    @DataPointType NVARCHAR(10)=NULL,
    @Samples VARBINARY(MAX)=NULL,
    @PreventDuplicates BIT=0,
    @ID INT OUTPUT
)
AS
SET NOCOUNT ON

    -- If we need to check for duplicates, check if there is a waveform with the same values from the same source for the same time
    IF (@PreventDuplicates=1)
    BEGIN
        
        SELECT 
            @ID = ID 
        FROM 
            dbo.WaveformLog2 
        WHERE 
            SourceID=@SourceID 
            AND TimestampUTC=@TimestampUTC 
            AND QuantityID=@QuantityID 
            AND TimeOfFirstPointUTC=@TimeOfFirstPointUTC 
            AND SamplingFrequency=@SamplingFrequency
            AND Scale=@Scale
            AND Offset=@Offset 
            AND DataPointType=@DataPointType
            AND ((DATALENGTH(Samples) = DATALENGTH(@Samples)) OR (Samples IS NULL AND @Samples IS NULL))
            AND HASHBYTES('MD5', Samples) = HASHBYTES('MD5', @Samples)
        
        IF(@ID IS NOT NULL)
        BEGIN
            PRINT 'Duplicate waveform already existed with ID:  ' + CAST(@ID AS NVARCHAR(250))
            RETURN 1
        END
    END

    INSERT INTO dbo.WaveformLog2 
    (
        SourceID
        , TimestampUTC
        , QuantityID
        , TimeOfTriggerUTC
        , TimeOfFirstPointUTC
        , SamplingFrequency
        , Scale
        , Offset
        , DataPointType
        , Samples
    )
    VALUES 
    (
        @SourceID
        ,@TimestampUTC
        , @QuantityID
        , @TimeOfTriggerUTC
        , @TimeOfFirstPointUTC
        , @SamplingFrequency
        , @Scale
        , @Offset
        , @DataPointType
        , @Samples
    )

    -- Retrieve the ID of the new record and pass back to the client.
    SET @ID=SCOPE_IDENTITY()
    RETURN 0
GO
    GRANT EXECUTE ON [dbo].[InsertWaveformLog] TO [mma_Archiver]
GO


IF(dbo.ProcedureExists('spDAL_GetLoggedData') = 1)
    DROP PROCEDURE spDAL_GetLoggedData
GO


/******************************************************************************
##BD_BEGIN
Return logged data that matches the input parameters.
##BD_END 

$Id: //rdsw/dev/cayman/SW_ION_E/database/Components/DAL/StoredProcedures/01.spDAL_GetLoggedData.sql#3 $
******************************************************************************/
CREATE PROCEDURE dbo.spDAL_GetLoggedData
(
    @SourceIDs NVARCHAR(MAX),
    @QuantityIDs NVARCHAR(MAX),
    @StartDatesUtc NVARCHAR(MAX),
    @EndDatesUtc NVARCHAR(MAX)
)
AS

DECLARE @DelimiterCharacter NCHAR(1)
DECLARE @SqlEscapeCharacter NCHAR(1)

SET @DelimiterCharacter = CHAR(6)
SET @SqlEscapeCharacter = CHAR(7)

-------------------------------------------------------------------------------
-- Calculate DateRanges table ahead of processing logged data
-- (ensures UDF isn't called multiple times)
DECLARE @DateRanges TABLE
(
    listpos SMALLINT PRIMARY KEY NOT NULL 
    , StartDate DATETIME
    , EndDate DATETIME
)
INSERT INTO @DateRanges
SELECT 
    listpos
    , StartDate
    , EndDate
FROM 
    udf_SplitDateListsToTable(@StartDatesUtc, @EndDatesUtc, @DelimiterCharacter) ts

---------------------------------------------------------------------------------
-- retrieve logged data
SELECT
    dl.SourceID AS SourceID,
    dl.QuantityID AS QuantityID,
    dbo.DateWithoutFraction(dl.TimestampUTC) AS TimestampUTC,
    dbo.FractionalSecondsAsReal(dl.TimestampUTC) AS FractionOfASecond,
    dl.Value
FROM
    dbo.DataLog2 dl
WHERE
    EXISTS
    (
        SELECT
            1
        FROM
            @DateRanges ts
        WHERE
            dl.TimestampUTC >= ts.StartDate 
            AND dl.TimestampUTC <= ts.EndDate
    )
    AND dl.SourceID IN (SELECT ID FROM dbo.udfDAL_GetSources(@SourceIDs))
    AND dl.QuantityID IN (SELECT ID FROM dbo.udfDAL_GetQuantities(@QuantityIDs))    
ORDER BY
    dl.TimestampUTC,
    dl.SourceID,
    dl.QuantityID

GO

EXEC sp_refreshview N'[dbo].[vUnknownQuantity]'
GO

IF(dbo.ProcedureExists('MapUnknownLabelToQuantity') = 1)
    DROP PROCEDURE MapUnknownLabelToQuantity
GO


--*****************************************************************************
-- PROCEDURE: MapUnknownLabelToQuantity
--
-- NOTES:
--
-- $Id: //rdsw/dev/cayman/SW_ION_E/database/Components/ION/StoredProcedures/02.MapUnknownLabelToQuantity.sql#2 $
--
--*****************************************************************************

/*************************************************************************************
    Takes a quantity that was created because of an unknown label and 
    creates a mapping from that label to an official quantity by:
        1. Adding an entry to the DefaultLabelQuantity table
        2. Updating the DataLog table with the offical QuantityID (optional)
        3. Deleting the "unknown" quantity from the Quantity table (optional)
**************************************************************************************/
CREATE PROCEDURE MapUnknownLabelToQuantity
(
    @UnknownQID SMALLINT,
    @OfficialQID SMALLINT,
    @UpdateDataLog BIT=1,
    @DeleteFromQuantity BIT=1
)
AS
SET NOCOUNT ON
-- check to see if the unknown QID exists and whether the associated default 
-- label has been mapped already ...
DECLARE @label NVARCHAR(400)
DECLARE @quantityID SMALLINT
SELECT @label=QuantityName FROM dbo.vUnknownQuantity WHERE QuantityID = @UnknownQID
IF @label IS NULL 
    RETURN -1
    
IF NOT EXISTS (SELECT Label FROM dbo.DefaultLabelQuantity WHERE Label = @label)
BEGIN
    -- create a new entry in the default quantity / label map
    INSERT dbo.DefaultLabelQuantity (Label, QuantityID) VALUES (@label, @OfficialQID)
    -- update Channel table
    UPDATE dbo.Channel SET QuantityID = @OfficialQID WHERE QuantityID = @UnknownQID
    -- update the rows in the DataLog table that reference 
    -- the previously unrecognized label
    IF @UpdateDataLog = 1
        UPDATE dbo.DataLog2 SET QuantityID = @OfficialQID WHERE QuantityID = @UnknownQID
    -- delete the previously unrecognized quantity
    IF @DeleteFromQuantity = 1
        DELETE FROM dbo.Quantity WHERE ID = @UnknownQID
END
GO
    GRANT EXECUTE ON [dbo].[MapUnknownLabelToQuantity] TO [mma_Archiver]
GO

IF(dbo.ProcedureExists('spDAL_GetPowerTriangleData') = 1)
    DROP PROCEDURE spDAL_GetPowerTriangleData
GO


/******************************************************************************
##BD_BEGIN
Return Power Triangle data for the specified sources, date range
and the specified power triangle system measurements
REMARKS: the power triangle measurement names are provided because
different devices use different measurements. The device specific
measurements come from the DAL. This stored procedure is responsible
for retrieving and pivoting power triangle data in a common schema
##BD_END 

$Id: //rdsw/dev/cayman/SW_ION_E/database/Components/DAL/StoredProcedures/01.spDAL_GetPowerTriangleData.sql#3 $
******************************************************************************/

CREATE PROCEDURE dbo.spDAL_GetPowerTriangleData
(
    @SourceIDs          NVARCHAR(MAX)   
    ,@StartDatesUtc     NVARCHAR(MAX)
    ,@EndDatesUtc       NVARCHAR(MAX)
    ,@ActiveMeasurementName NVARCHAR(MAX)
    ,@ReactiveMeasurementName NVARCHAR(MAX)
    ,@ApparentMeasurementName NVARCHAR(MAX)
)
AS

DECLARE @DelimiterCharacter NCHAR(1)
SET @DelimiterCharacter = CHAR(6)

-- Find the IDs of the Demand/Power Measurements so that they can be added to
-- our result table, which will help the associated view provider to get
-- metadata (e.g. names and unit names) for these Measurements.
-- NOTE:  Even though we don't return kVA data from this stored procedure,
-- the view provider needs to get Measurement metadata for kVA.
DECLARE @ActiveMeasurementID INT
SET @ActiveMeasurementID = (SELECT [ID] FROM [dbo].[Quantity] WHERE [Name] = @ActiveMeasurementName)

DECLARE @ReactiveMeasurementID INT
SET @ReactiveMeasurementID = (SELECT [ID] FROM [dbo].[Quantity] WHERE [Name] = @ReactiveMeasurementName)

DECLARE @ApparentMeasurementID INT
SET @ApparentMeasurementID = (SELECT [ID] FROM [dbo].[Quantity] WHERE [Name] = @ApparentMeasurementName)

-- SELECT RAW DEMAND/POWER DATA WHERE DATA IN DATE RANGE
-- FOR SPECIFIED SOURCES. NORMALIZE THE SYSTEM MEASUREMENT
-- NAMES TO 'ACTIVE','REACTIVE','APPARENT'
DECLARE @Readings TABLE
(
    [TimestampUTC] DATETIME2
    ,[QuantityName] NVARCHAR(250)
    ,[Value] FLOAT
    ,[SourceID] INT
)

INSERT INTO @Readings
SELECT 
    DISTINCT dl.TimestampUTC
    , [QuantityName] = 
        CASE q.[Name]
            WHEN @ActiveMeasurementName THEN 'Active'
            WHEN @ReactiveMeasurementName THEN 'Reactive'
            WHEN @ApparentMeasurementName THEN 'Apparent'
        END
    , dl.[Value]
    , dl.[SourceID]
FROM
    [dbo].[DataLog2] dl
INNER JOIN
    [dbo].[Quantity] q ON (dl.QuantityID = q.ID)
WHERE 
    q.[Name] IN 
    (
        @ActiveMeasurementName,
        @ReactiveMeasurementName,
        @ApparentMeasurementName
    )
    AND dl.[SourceID] IN (SELECT ID FROM dbo.udfDAL_GetSources(@SourceIDs))
    AND EXISTS
    (
        SELECT
            1
        FROM
            udf_SplitDateListsToTable(@StartDatesUtc, @EndDatesUtc, @DelimiterCharacter) ts
        WHERE
            dl.TimestampUTC > ts.StartDate 
            AND dl.TimestampUTC <= ts.EndDate
    )

-- SELECT PIVOT'D DATA
SELECT
    [SourceID]
    , dbo.DateWithoutFraction(TimestampUTC) AS TimestampUTC
    , dbo.FractionalSecondsAsReal(TimestampUTC) AS FractionOfASecond
    , @ActiveMeasurementID AS [ActiveMeasurementID]
    , [Active] AS [Active]
    , @ReactiveMeasurementID AS [ReactiveMeasurementID]
    , [Reactive] AS [Reactive]
    , @ApparentMeasurementID AS [ApparentMeasurementID]
    , [Apparent] AS [Apparent]
FROM
    @Readings AS [p]
PIVOT
    (   
        MAX([Value])
        FOR [QuantityName] IN
        (               
            [Active]
            ,[Reactive]
            ,[Apparent]
        )
    ) AS [pvt]
ORDER BY
    [SourceID]
    , [TimestampUTC]

GO

--*****************************************************************************
IF(dbo.ProcedureExists('spDAL_GetSourceQuantities') = 1)
    DROP PROCEDURE spDAL_GetSourceQuantities
GO
--*****************************************************************************
-- Procedure to obtain a list of quantities being logged in the ION_Data database
-- for the sources specified in the list of source IDs. The results of this query 
-- are used to filter the measurement selector list.
--*****************************************************************************
CREATE PROCEDURE dbo.spDAL_GetSourceQuantities
(
    @SourceIDs NVARCHAR(MAX)
)
AS
DECLARE @sources TABLE ( id INT PRIMARY KEY ) 
INSERT INTO @sources ( id ) 
SELECT list.number from dbo.udf_SplitIntListToTable(@SourceIDs, ',') list

SELECT 
    SQ.[QuantityID] AS QuantityID, 
    Q.[Name] AS QuantityName,
    COUNT(SourceID) AS SourceCount  -- this is the number of sources that log the quantity
FROM
    [dbo].[SourceQuantity] SQ
JOIN    @sources S ON S.[id] = SQ.[SourceID]
JOIN    [dbo].[Quantity] Q ON Q.ID = SQ.[QuantityID]
GROUP BY QuantityID, Name
GO

IF(dbo.ProcedureExists('spDAL_GetQuantities') = 1)
    DROP PROCEDURE spDAL_GetQuantities
GO
/******************************************************************************
##BD_BEGIN
Returns a table containing all quantities that match the input parameters.

Remarks: GetQuantities still used by logged data and waveform sql to filter
ids however, measurement metadata now comes from ION Network as 5.5 > 5.6
upgrade switched to measurement store
##BD_END 

$Id: //rdsw/dev/cayman/SW_ION_E/database/Components/DAL/StoredProcedures/01.spDAL_GetQuantities.sql#2 $
******************************************************************************/
CREATE PROCEDURE dbo.spDAL_GetQuantities
(
    @QuantityIDs NVARCHAR(MAX),
    @QuantityNames NVARCHAR(MAX),
    @SourceIDs NVARCHAR(MAX)
)
AS

-- DELIMITER CONSTANTS
DECLARE @DelimiterCharacter NCHAR(1)
DECLARE @SqlEscapeCharacter NCHAR(1)
SET @DelimiterCharacter = CHAR(6)
SET @SqlEscapeCharacter = CHAR(7)

-- the table of data we will return
DECLARE @TblQuantities TABLE
(
    ID INT PRIMARY KEY,
    Name NVARCHAR(200),
    Unit NVARCHAR(200),
    UnitSymbol NVARCHAR(200)
)

-- get all the quantities into a temp table, without any filtering
INSERT INTO @TblQuantities
(
    ID,
    Name,
    Unit,
    UnitSymbol
)
SELECT
    ID,
    Name,
    Unit,
    UnitSymbol
FROM
    dbo.udfDAL_GetQuantities(@QuantityIDs)

-- remove quantities that don't match the @QuantityNames (if not null)
IF @QuantityNames IS NOT NULL
BEGIN
    DELETE FROM
        @TblQuantities
    WHERE
        Name NOT IN
    (
        SELECT
            t.Name
        FROM
            @TblQuantities t
            JOIN dbo.udf_SplitStringListToTable(@QuantityNames, @DelimiterCharacter) d
                ON t.Name LIKE d.nstr ESCAPE @SqlEscapeCharacter
    )
END

-- remove quantities that don't have matching sources (if sources specified)
IF @SourceIDs IS NOT NULL
BEGIN

    DELETE FROM
        @TblQuantities
    WHERE
        ID NOT IN 
        (
            select distinct
                QuantityID
            FROM
                dbo.DataLog2 dl
            WHERE
                dl.SourceID IN (SELECT ID FROM dbo.udfDAL_GetSources(@SourceIDs))
        )

END



-- return the quantities
SELECT
    ID,
    Name,
    Unit,
    UnitSymbol
FROM
    @TblQuantities

-- return messages, if any
IF NOT EXISTS (SELECT 1 FROM @TblQuantities)
BEGIN
    SELECT
        'NODATAFORPARAMETERS' AS MessageID
END

GO

IF(dbo.ProcedureExists('spDAL_GetSources') = 1)
    DROP PROCEDURE spDAL_GetSources
GO


/******************************************************************************
##BD_BEGIN
Returns a table containing all sources that match the input parameters.
##BD_END 

$Id: //rdsw/dev/cayman/SW_ION_E/database/Components/DAL/StoredProcedures/01.spDAL_GetSources.sql#2 $
******************************************************************************/
CREATE PROCEDURE dbo.spDAL_GetSources
(
    @SourceIDs NVARCHAR(MAX),
    @SourceNames NVARCHAR(MAX),
    @QuantityIDs NVARCHAR(MAX)
)
AS

-- DELIMITER CONSTANTS
DECLARE @DelimiterCharacter NCHAR(1)
DECLARE @SqlEscapeCharacter NCHAR(1)
SET @DelimiterCharacter = CHAR(6)
SET @SqlEscapeCharacter = CHAR(7)

-- the table of data we will return
DECLARE @TblSources TABLE
(
    ID INT PRIMARY KEY,
    Name NVARCHAR(250),
    Namespace NVARCHAR(400),
    Description NVARCHAR(2000),
    TimeZoneID SMALLINT
)


-- get all the sources into a temp table, without any filtering other than
-- by SourceID
INSERT INTO @TblSources
(
    ID,
    Name,
    Namespace,
    Description,
    TimeZoneID
)
SELECT
    ID,
    Name,
    Namespace,
    Description,
    TimeZoneID
FROM
    dbo.udfDAL_GetSources(@SourceIDs)


-- remove sources that don't match the @SourceNames (if not null)
IF @SourceNames IS NOT NULL
BEGIN
    DELETE FROM
        @TblSources
    WHERE
        Name NOT IN
    (
        SELECT
            t.Name
        FROM
            @TblSources t
            JOIN dbo.udf_SplitStringListToTable(@SourceNames, @DelimiterCharacter) d
                ON t.Name LIKE d.nstr ESCAPE @SqlEscapeCharacter
    )
END

-- remove sources that don't have matching quantities (if quantities specified)
IF @QuantityIDs IS NOT NULL
BEGIN
    DELETE FROM
        @TblSources
    WHERE
        ID NOT IN
    (
        SELECT
            SourceID
        FROM
            dbo.DataLog2 dl 
            JOIN dbo.udfDAL_GetQuantities(@QuantityIDs) q
                ON dl.QuantityID = q.ID
    )
END


-- return the time zones for sources we have selected
SELECT
    ID, 
    DisplayName
FROM
    dbo.udfDAL_GetTimeZones (@SourceIDs,0)
WHERE
    ID IN (SELECT TimeZoneID FROM @TblSources)

-- return the sources
SELECT
    ID,
    Name,
--  Namespace,
--  Description,
    TimeZoneID
FROM
    @TblSources

-- return messages, if any
IF NOT EXISTS (SELECT 1 FROM @TblSources)
BEGIN
    SELECT
        'NODATAFORPARAMETERS' AS MessageID
END

GO

IF(dbo.ProcedureExists('spDAL_GetWaveforms') = 1)
    DROP PROCEDURE spDAL_GetWaveforms
GO


/******************************************************************************
##BD_BEGIN
Return four tables of data:
 1. All time zones in the system that are associated with a device.
 2. All devices in the system.
 3. All measurements in the system.
 4. Waveforms that match the input parameters.
##BD_END

$Id: //rdsw/dev/cayman/SW_ION_E/database/Components/DAL/StoredProcedures/01.spDAL_GetWaveforms.sql#2 $
******************************************************************************/
CREATE PROCEDURE dbo.spDAL_GetWaveforms
(
    @SourceIDs NVARCHAR(MAX),
    @StartDatesUtc NVARCHAR(MAX),
    @EndDatesUtc NVARCHAR(MAX)
)
AS

DECLARE @DelimiterCharacter NCHAR(1)
DECLARE @SqlEscapeCharacter NCHAR(1)

SET @DelimiterCharacter = CHAR(6)
SET @SqlEscapeCharacter = CHAR(7)


-------------------------------------------------------------------------------
-- get all the sources, filtering if needed
DECLARE @TblSources TABLE
(
    ID INT PRIMARY KEY NOT NULL,
    Name NVARCHAR(250) NOT NULL,
    Namespace NVARCHAR(400) NOT NULL,
    Description NVARCHAR(2000),
    TimeZoneID SMALLINT NOT NULL
)
INSERT INTO @TblSources
(
    ID,
    Name,
    Namespace,
    Description,
    TimeZoneID
)
SELECT
    ID,
    Name,
    Namespace,
    Description,
    TimeZoneID
FROM
    dbo.udfDAL_GetSources(@SourceIDs)

---------------------------------------------------------------------------------
---- get all the quantities, filtering if needed
DECLARE @TblQuantities TABLE
(
    ID SMALLINT PRIMARY KEY NOT NULL
)

INSERT INTO @TblQuantities
(
    ID
)
SELECT
    ID
FROM
    dbo.udfDAL_GetQuantities(NULL)

---------------------------------------------------------------------------------

-- return the time zones for sources we have selected
SELECT
    ID,
    DisplayName
FROM
    dbo.udfDAL_GetTimeZones (@SourceIDs,0)
WHERE
    ID IN (SELECT TimeZoneID FROM @TblSources)

-- return the sources we have selected
SELECT
    ID,
    Name,
    Namespace,
    Description,
    TimeZoneID
FROM
    @TblSources

-- return the waveform data
SELECT
    w.ID as WaveformID,
    s.ID AS SourceID,
    q.ID AS QuantityID,
    dbo.DateWithoutFraction(w.TimestampUTC) AS TimestampUTC,
    dbo.FractionalSecondsAsReal(w.TimestampUTC) AS FractionOfASecond,
    w.Samples,
    w.SamplingFrequency,
    w.Scale,
    w.Offset,
    dbo.DateWithoutFraction(w.TimeOfTriggerUTC) AS TimeOfTriggerUTC,
    dbo.FractionalSecondsAsReal(w.TimeOfTriggerUTC) AS TriggerFracOfASec,
    dbo.DateWithoutFraction(w.TimeOfFirstPointUTC) AS TimeOfFirstPointUTC,
    dbo.FractionalSecondsAsReal(w.TimeOfFirstPointUTC) AS FirstFracOfASec,
    w.DataPointType
FROM
    dbo.WaveformLog2 w
    JOIN @TblSources s on w.SourceID = s.ID
    JOIN @TblQuantities q on w.QuantityID = q.ID
WHERE
    EXISTS
    (
        SELECT
            1
        FROM
            udf_SplitDateListsToTable(@StartDatesUtc, @EndDatesUtc, @DelimiterCharacter) ts
        WHERE
            w.TimestampUTC >= ts.StartDate
            AND w.TimestampUTC <= ts.EndDate
    )
ORDER BY
    TimestampUTC,
    TimeOfTriggerUTC,
    SourceID,
    QuantityID,
    SamplingFrequency,
    Scale,
    Offset


-- return messages, if any
IF NOT EXISTS (SELECT 1 FROM @TblSources)
BEGIN
    SELECT
        'NODATAFORPARAMETERS' AS MessageID
END

GO

IF(dbo.ProcedureExists('RemoveDuplicateWaveforms') = 1)
    DROP PROCEDURE RemoveDuplicateWaveforms
GO

--*****************************************************************************
-- PROCEDURE: RemoveDuplicateWaveforms
--
-- NOTES: Removes all the duplicates from the Waveform table.
--
--*****************************************************************************

CREATE PROCEDURE [dbo].[RemoveDuplicateWaveforms] 
AS
SET NOCOUNT ON

DELETE 
   wf1 
FROM 
   WaveformLog2 wf1, WaveformLog2 wf2 
WHERE 
   wf1.SourceID = wf2.SourceID
   AND wf1.TimestampUTC = wf2.TimestampUTC
   AND wf1.QuantityID = wf2.QuantityID
   AND wf1.TimeOfFirstPointUTC = wf2.TimeOfFirstPointUTC
   AND wf1.SamplingFrequency = wf2.SamplingFrequency
   AND wf1.Scale = wf2.Scale
   AND wf1.Offset = wf2.Offset
   AND wf1.DataPointType = wf2.DataPointType
   AND DATALENGTH(wf1.Samples) = DATALENGTH(wf2.Samples)
   AND HASHBYTES('MD5', wf1.Samples) = HASHBYTES('MD5', wf2.Samples)
   AND wf1.ID > wf2.ID

RETURN 0
GO

IF(dbo.ProcedureExists('UpdateChannel') = 1)
    DROP PROCEDURE UpdateChannel
GO

--*****************************************************************************
-- PROCEDURE: UpdateChannel
--
-- NOTES:
--
-- $Id: //rdsw/dev/cayman/SW_ION_E/database/Components/ION/StoredProcedures/02.UpdateChannel.sql#3 $
--
--*****************************************************************************


/*************************************************************************************
    Processes a channel by inserting it if it does not already exist. 
    Updates the label (and the quantity id) of an existing channel if necessary.
**************************************************************************************/
CREATE PROCEDURE UpdateChannel
(
    @RecorderID INT,
    @Name NVARCHAR(50) ,
    @Label NVARCHAR(200),
    @QuantityID SMALLINT,
    @InputDeviceID INT=NULL,
    @ChannelID INT OUTPUT
)
AS
SET NOCOUNT ON
SET @ChannelID=NULL

DECLARE @CurrentLabel NVARCHAR(200)
DECLARE @CurrentQuantityID SMALLINT

-- The Log Inserter has to deal with Recorders that have remote inputs.
-- For other callers, the input device must be the recorder source.
-- For backward compatibility, retrieve this for them.
IF @InputDeviceID IS NULL
BEGIN
    SELECT @InputDeviceID=SourceID FROM Recorder WHERE ID=@RecorderID
END

-- Find the most recent entry for the register as identified by its name and device.
-- Note that the InputDeviceID in the table could be NULL after an upgrade.
-- Adding label to the query without which iscurrentconfig will not be set in following case
-- Data logs being captured for extrinsic mapping1. Mapping1 deleted.
-- Mapping2 added. Mapping2 deleted. Mapping1 added back.
SELECT @ChannelID=ID,@CurrentLabel=Label,@CurrentQuantityID=QuantityID FROM Channel 
        WHERE ID=(SELECT MAX(ID) FROM Channel WHERE RecorderID=@RecorderID 
        AND Name=@Name 
        AND Label = @Label
        AND (InputDeviceID IS NULL OR InputDeviceID=@InputDeviceID))

-- If there is an existing entry, check to see if the label or quantity has changed.
-- If so, there needs to be a new entry. Mark any previous entries as not current
-- and set @ChannelID to null so that a new entry will be created.
-- If there has not been a change, mark the entry as current and set the InputDeviceID 
-- in case it needs to be filled in (after an upgrade).
IF @ChannelID IS NOT NULL
BEGIN
    IF @Label <> @CurrentLabel OR @QuantityID <> @CurrentQuantityID
    BEGIN
        UPDATE Channel SET IsCurrentConfiguration=0, InputDeviceID=@InputDeviceID
             WHERE RecorderID=@RecorderID AND Name=@Name 
                AND (InputDeviceID IS NULL OR InputDeviceID=@InputDeviceID)
        SET @ChannelID = NULL
    END
    ELSE
    BEGIN
        UPDATE Channel SET IsCurrentConfiguration=1, InputDeviceID=@InputDeviceID WHERE ID=@ChannelID
    END
END

-- If a new channel entry is required, create it marking it as current.
IF @ChannelID IS NULL
BEGIN
    INSERT INTO Channel (RecorderID, [Name], Label, QuantityID, InputDeviceID, IsCurrentConfiguration) 
        VALUES (@RecorderID, @Name, @Label, @QuantityID, @InputDeviceID, 1)
    SET @ChannelID = SCOPE_IDENTITY()
END
RETURN 0
GO

IF(dbo.ViewExists('vOldSchemaDatalogStamp') = 1)
    DROP VIEW [dbo].[vOldSchemaDatalogStamp]
GO

--*****************************************************************************
-- VIEW: vOldSchemaDatalogStamp
--
-- NOTES: Mimics the pre-6.1 schema for DataLogStamp.
--
--*****************************************************************************

CREATE VIEW dbo.vOldSchemaDatalogStamp
AS
SELECT
    CONVERT(INT, ID) AS ID,
    CONVERT(SMALLINT,SourceID) AS SourceID,
    dbo.DateWithoutFraction(TimestampUTC) AS TimestampUTC,
    CONVERT(DATETIME,'1980-01-01T00:00:00') AS TimestampSourceLT,
    dbo.FractionalSecondsAsReal(TimestampUTC) AS FractionOfASecond
FROM
    dbo.DataLog2
GO

IF(dbo.ViewExists('vOldSchemaEventLog') = 1)
    DROP VIEW [dbo].[vOldSchemaEventLog]
GO

--*****************************************************************************
-- VIEW: vOldSchemaEventLog
--
-- NOTES: Mimics the pre-6.1 schema for EventLog.
--
--*****************************************************************************

CREATE VIEW dbo.vOldSchemaEventLog
AS

SELECT ID
      ,CONVERT(SMALLINT,SourceID) AS SourceID
      ,dbo.DateWithoutFraction(TimestampUTC) AS TimestampUTC
      ,CONVERT(DATETIME,'1980-01-01T00:00:00') AS TimestampSourceLT
      ,dbo.FractionalSecondsAsReal(TimestampUTC) AS FractionOfASecond
      ,Priority
      ,CauseID
      ,CauseValueID
      ,EffectID
      ,EffectValueID
  FROM 
    dbo.EventLog2
GO

IF(dbo.ViewExists('vOldSchemaWaveformLog') = 1)
    DROP VIEW [dbo].[vOldSchemaWaveformLog]
GO

--*****************************************************************************
-- VIEW: vOldSchemaWaveformLog
--
-- NOTES: Mimics the pre-6.1 schema for WaveformLog.
--
--*****************************************************************************

CREATE VIEW dbo.vOldSchemaWaveformLog
AS
SELECT ID
      ,CONVERT(SMALLINT,SourceID) AS SourceID
      ,dbo.DateWithoutFraction(TimestampUTC) AS TimestampUTC
      ,CONVERT(DATETIME,'1980-01-01T00:00:00') AS TimestampSourceLT
      ,dbo.FractionalSecondsAsReal(TimestampUTC) AS FractionOfASecond
      ,QuantityID
      ,dbo.DateWithoutFraction(TimeOfTriggerUTC) AS TimeOfTriggerUTC
      ,dbo.FractionalSecondsAsReal(TimeOfTriggerUTC) AS TriggerFracOfASec
      ,dbo.DateWithoutFraction(TimeOfFirstPointUTC) AS TimeOfFirstPointUTC
      ,dbo.FractionalSecondsAsReal(TimeOfFirstPointUTC) AS FirstFracOfASec
      ,SamplingFrequency
      ,Scale
      ,Offset
      ,DataPointType
      ,Samples
FROM
    dbo.WaveformLog2
GO

IF(dbo.ViewExists('vWFLogChannelValue') = 1)
    DROP VIEW [dbo].[vWFLogChannelValue]
GO

--*****************************************************************************
-- VIEW: vWFLogChannelValue
--
-- NOTES:
--
-- $Id: //rdsw/dev/cayman/SW_ION_E/database/Components/ION/Views/02.vWFLogChannelValue.sql#2 $
--
--*****************************************************************************

CREATE VIEW dbo.vWFLogChannelValue
AS
SELECT  s.Name AS SourceName, ns.Name AS SourceNamespace, q.Name AS Quantity, wf.TimestampUTC, 
    wf.TimeOfTriggerUTC, wf.TimeOfFirstPointUTC, wf.SamplingFrequency, wf.Scale, wf.Offset, 
    wf.DataPointType, wf.Samples
FROM    dbo.Quantity q INNER JOIN
    dbo.WaveformLog2 wf ON q.ID = wf.QuantityID INNER JOIN
    dbo.Source s INNER JOIN
    dbo.Namespace ns ON s.NamespaceID = ns.ID ON wf.SourceID = s.ID
GO
    GRANT SELECT ON [dbo].[vDataLogChannelValue] TO [ION_Reader]
    GRANT SELECT ON [dbo].[vDataLogChannelValue] TO [mma_Archiver]
GO

IF(dbo.FunctionExists('udfPQ_TimestampDiff') = 1)
    DROP FUNCTION [dbo].[udfPQ_TimestampDiff]
GO

--
-- Returns the duration of the incident (in seconds as a FLOAT)
-- based on the starting time of the first event to the ending
-- time of the last event in the incident.
CREATE FUNCTION dbo.udfPQ_TimestampDiff
(
	@IncidentID INT,
	@ReportID NVARCHAR(100)
)
RETURNS FLOAT
WITH EXECUTE AS CALLER
AS
BEGIN

	DECLARE @StartTimestampUTC DATETIME2
	DECLARE @EndTimestampUTC DATETIME2
	DECLARE @SecondsDiff FLOAT
	DECLARE @EventCount INT

	SELECT @EventCount = Count(EventID) FROM PQ_IncidentEventMap WHERE IncidentId = @IncidentID AND ReportID = @ReportID

	IF @EventCount = 1
	BEGIN
		SELECT 
			@SecondsDiff = EventDuration
		FROM
			PQ_Events
		WHERE 
			EventId IN (SELECT EventID FROM PQ_IncidentEventMap WHERE IncidentId = @IncidentID AND ReportID = @ReportID)

	END
	ELSE
	BEGIN
		-- get the first event's start information
		SELECT TOP 1
			@StartTimestampUTC = StartTimestampUtc
		FROM 
			PQ_Events 
		WHERE 
			EventId IN (SELECT EventID FROM PQ_IncidentEventMap WHERE IncidentId = @IncidentID AND ReportID = @ReportID)
		ORDER BY
			StartTimestampUTC

		-- get the last event's end information
		SELECT TOP 1
			@EndTimestampUTC = EndTimestampUTC
		FROM 
			PQ_Events 
		WHERE 
			EventId IN (SELECT EventID FROM PQ_IncidentEventMap WHERE IncidentId = @IncidentID AND ReportID = @ReportID)
		ORDER BY
			EndTimestampUTC DESC

		-- if no events were found, simply return zero
		IF (@StartTimestampUTC IS NULL)
		BEGIN
			RETURN 0
		END

		-- calculate the difference in milliseconds
		SET @SecondsDiff = DATEDIFF(ms, @StartTimestampUTC, @EndTimestampUTC)
		-- calculate the difference in seconds
		SET @SecondsDiff = @SecondsDiff / 1000
	END
	RETURN @SecondsDiff

END
GO
IF(dbo.ProcedureExists('spPQ_GetConfigurationForIncident') = 1)
    DROP PROCEDURE spPQ_GetConfigurationForIncident
GO

/******************************************************************************
##BD_BEGIN
Returns meter configuration data associated with a PQ Event
Note: not all data may be available at the time of the event
in this event null values are returned
##BD_END 
******************************************************************************/
CREATE PROCEDURE dbo.spPQ_GetConfigurationForIncident
(
    @ReportId AS NVARCHAR(100)
    , @IncidentId AS INT
)
AS

DECLARE @TblSources TABLE
(
    ID INT PRIMARY KEY NOT NULL 
)
INSERT INTO @TblSources
(
    ID
)
SELECT DISTINCT
    SourceId
FROM 
    PQ_Events evt
INNER JOIN
    dbo.PQ_IncidentEventMap map ON (map.EventId = evt.EventId)
WHERE
    map.IncidentId = @IncidentId
    AND map.ReportId = @ReportId

SELECT
    [SourceID] AS [SourceId]
    ,[Current Transformer Primary Rating] AS [CTPrimaryRating]
    ,[Current Transformer Secondary Rating] AS [CTSecondaryRating]
    ,[Potential Transformer Primary Rating] AS [PTPrimaryRating]
    ,[Potential Transformer Secondary Rating] AS [PTSecondaryRating]
    ,[Voltage Disturbance Sag Limit] AS [SagLimit]
    ,[Voltage Disturbance Swell Limit] AS [SwellLimit]
    ,[Voltage Disturbance Detection Nominal Voltage] AS [Nominal]
    ,[Voltage Transient Threshold] AS [TransientThreshold]
FROM
(
    SELECT DISTINCT
        dl.SourceID
        , q.[Name] AS [QuantityName]
        , dl.[Value]
    FROM
        dbo.DataLog2 dl
    INNER JOIN
        Quantity q ON (dl.QuantityID = q.ID)
    WHERE
        q.Name IN
        (
            'Current Transformer Primary Rating'
            ,'Current Transformer Secondary Rating'
            ,'Potential Transformer Primary Rating'
            ,'Potential Transformer Secondary Rating'
            ,'Voltage Disturbance Sag Limit'
            ,'Voltage Disturbance Swell Limit'
            ,'Voltage Disturbance Detection Nominal Voltage'
            ,'Voltage Transient Threshold'
        )
        AND dl.SourceID IN (SELECT ID FROM @TblSources)
) AS [p]
PIVOT
    (   
        MAX(Value)
        FOR [QuantityName] IN
        (
            [Current Transformer Primary Rating]
            ,[Current Transformer Secondary Rating]
            ,[Potential Transformer Primary Rating]
            ,[Potential Transformer Secondary Rating]
            ,[Voltage Disturbance Sag Limit]
            ,[Voltage Disturbance Swell Limit]
            ,[Voltage Disturbance Detection Nominal Voltage]
            ,[Voltage Transient Threshold]
        )
    ) AS [pvt]
ORDER BY
    SourceID
GO

IF(dbo.ProcedureExists('spPQ_GetEvent') = 1)
    DROP PROCEDURE spPQ_GetEvent
GO


CREATE PROCEDURE spPQ_GetEvent
    @EventId AS INT
AS  
    SELECT
        evt.EventId
        , evt.SourceId
        , evt.StartTimeStampUtc
        , evt.EndTimeStampUtc
        , evt.EndDataLogID AS [DataLogId]
        , evt.SubType AS [Type]
        , evt.WorstPhase AS [Phase]
        , evt.EventDuration AS [Duration]
        , evt.Magnitude
        , evt.Severity
    FROM
        PQ_Events evt
    WHERE
        evt.EventId = @EventId
GO

IF(dbo.ProcedureExists('spPQ_GetEventsForIncident') = 1)
    DROP PROCEDURE spPQ_GetEventsForIncident
GO

CREATE PROCEDURE spPQ_GetEventsForIncident
    @ReportId AS NVARCHAR(100)
    ,@IncidentId AS INT
AS

    SELECT
        evt.EventId
        , evt.SourceId
        , evt.StartTimeStampUtc
        , evt.EndTimeStampUtc
        , evt.EndDataLogID AS [DataLogId]
        , evt.SubType AS [Type]
        , evt.WorstPhase AS [Phase]
        , evt.EventDuration AS [Duration]
        , evt.Magnitude
        , evt.Severity
    FROM
        PQ_Events evt
    INNER JOIN
        PQ_IncidentEventMap map ON (evt.EventId = map.EventId)
    INNER JOIN
        PQ_Incidents inc ON (map.IncidentId = inc.IncidentId AND map.ReportId = inc.ReportId)
    WHERE
        inc.IncidentId = @IncidentId
        AND inc.ReportId = @ReportId
GO

IF(dbo.ProcedureExists('spPQ_GetNewPQEventsForSource') = 1)
    DROP PROCEDURE spPQ_GetNewPQEventsForSource
GO


/*
* The MeasurementsAsString parameter is a delimited string that contains specific measurement
* names that are characteristic of a Sag, Swell, or Transient.
*/
CREATE PROCEDURE [dbo].[spPQ_GetNewPQEventsForSource]
    @SourceId INT
    , @EventId INT
    , @ArchiveMode BIT
    , @CauseString VARCHAR(4000)
    , @CauseValueString VARCHAR(4000)
    , @EffectString VARCHAR(4000)
    , @EffectValueString VARCHAR(4000)
    , @PQEventTypeString VARCHAR(4000)
    , @IEventCollector VARCHAR(4000)
    , @MeasurementsAsString VARCHAR(4000)
    , @MsThreshold INT
    , @LastProcessedEventID INT OUTPUT
    , @LastProcessedTimestampUTC DATETIME2 OUTPUT
AS

-- default OUTPUT parameters
SET @LastProcessedEventID = 0
SET @LastProcessedTimestampUTC = '1980-01-01T00:00:00'

-- this is the number of event rows to query on each pass of the EventLog2 table
DECLARE @EventRangeSize INT
SET @EventRangeSize = 100000

-- this is the maximum number of events we want to return
DECLARE @MaxEventsToReturn INT
SET @MaxEventsToReturn = 1000

-- get the current maximum Eventlog2.ID, so we can make sure we never have race condition issues
-- within this stored procedure
DECLARE @MaxEventLogID INT
SELECT @MaxEventLogID = ISNULL(MAX(ID),0) FROM dbo.EventLog2

-- get filtering dates, based on if we are in archive mode or not
DECLARE @FilterFromDate AS DATETIME
DECLARE @FilterToDate AS DATETIME
IF @ArchiveMode = 0
BEGIN
    --DO NOT include historical data (where TimeStampUtc > ION-E 6.0 install date)
    SELECT @FilterFromDate = dbo.udfPQ_GetUpgradeDate()
    SELECT @FilterToDate = MAX(TimeStampUtc) FROM dbo.EventLog2 WHERE SourceID = @SourceID
END
ELSE
BEGIN
    --DO include historical data (where TimeStampUtc < ION-E 6.0 install date)
    SELECT @FilterFromDate = MIN(TimeStampUtc) FROM dbo.EventLog2 WHERE SourceID = @SourceID
    SELECT @FilterToDate = dbo.udfPQ_GetUpgradeDate()
END

-- Get the MIN and MAX IDs in the EventLog2 table for this source and archive mode
DECLARE @MaxEventID INT
DECLARE @MinEventID INT
SELECT 
    @MaxEventID = ISNULL(MAX(ID),@MaxEventLogID),
    @MinEventID = ISNULL(MIN(ID),0)
FROM 
    dbo.EventLog2
WHERE
    TimestampUTC BETWEEN @FilterFromDate AND @FilterToDate
    AND SourceID = @SourceID
    AND ID <= @MaxEventLogID

-- boost the @EventID up to the @MinEventID, if necessary
IF (@EventID < @MinEventID)
BEGIN
    SET @EventID = @MinEventID-1
END

-- This is the last event we want to query in this run. This helps to make sure we don't
-- time out during the query, by taking just a few events at a time.
DECLARE @MaxEventIDToQuery INT
SET @MaxEventIDToQuery = @EventID + @EventRangeSize
IF (@MaxEventIDToQuery > @MaxEventID)
BEGIN
    SET @MaxEventIDToQuery = @MaxEventID
END

-- this is the table that will be returned by this procedure
DECLARE @Output TABLE
(
    SourceID INT,
    EventID INT,
    TimestampUTC DATETIME2,
    Cause NVARCHAR(3998),
    CauseValue NVARCHAR(3998),
    Effect NVARCHAR(3998),
    EffectValue NVARCHAR(3998),
    DataLogId BIGINT,
    PQEventType NVARCHAR(4000)
)

INSERT INTO @Output
    (
    SourceID,
    EventID,
    TimestampUTC,
    Cause,
    CauseValue,
    Effect,
    EffectValue,
    PQEventType,
    DataLogId
    )
SELECT TOP (@MaxEventsToReturn)
    EventLog2.[SourceId]
    , EventLog2.[ID] AS [EventId]
    , EventLog2.TimestampUTC AS [TimeStampUtc]
    , cause.String AS [Cause]
    , causeValue.String AS [CauseValue]
    , effect.String AS [Effect]
    , effectValue.String AS [EffectValue]
    , conditions.PQEventType
    , 
        (
            SELECT TOP 1 
                ID 
            FROM dbo.udfPQ_GetPQDataLogEntryWithinThreshold(
                    EventLog2.SourceId,
                    EventLog2.TimestampUTC,
                    @MeasurementsAsString,
                    @MsThreshold)
                ) AS [DataLogId]
FROM
    dbo.EventLog2
    JOIN dbo.EventString cause ON EventLog2.CauseID = cause.ID
    JOIN dbo.EventString causeValue ON EventLog2.CauseValueID = causeValue.ID
    JOIN dbo.EventString effect ON EventLog2.EffectID = effect.ID
    JOIN dbo.EventString effectValue ON EventLog2.EffectValueID = effectValue.ID
    CROSS JOIN dbo.udfPQ_GetPQEventConditions(@CauseString, @CauseValueString, @EffectString, @EffectValueString, @PQEventTypeString, @IEventCollector) conditions
WHERE
    EventLog2.SourceId = @SourceId
    AND EventLog2.TimestampUTC BETWEEN @FilterFromDate and @FilterToDate    
    AND EventLog2.ID > @EventId
    AND EventLog2.ID <= @MaxEventIDToQuery
    AND (Cause.String COLLATE SQL_Latin1_General_CP1_CI_AS LIKE conditions.Cause OR conditions.Cause IS NULL)
    AND (CauseValue.String COLLATE SQL_Latin1_General_CP1_CI_AS LIKE conditions.CauseValue OR conditions.CauseValue IS NULL)
    AND (Effect.String COLLATE SQL_Latin1_General_CP1_CI_AS LIKE conditions.Effect OR conditions.Effect IS NULL)
    AND (EffectValue.String COLLATE SQL_Latin1_General_CP1_CI_AS LIKE conditions.EffectValue OR conditions.EffectValue IS NULL)
ORDER BY
    EventLog2.ID

-- Set the last EventID that was processed.
DECLARE @EventCount INT
SELECT @EventCount = COUNT(*) FROM @Output

IF (@EventCount = @MaxEventsToReturn)
BEGIN
    -- we maxed out, so return the ID of the last event we found
    SELECT @LastProcessedEventID = MAX(EventID) FROM @Output
END
ELSE
BEGIN
    SET @LastProcessedEventID = @MaxEventIDToQuery
END

-- Remove any "duplicate" events.  Duplicate events are when you get a transient on more 
-- than one phase at the exact same time for the same source.  We will only return one of 
-- those transients. The stored procedure 'spPQ_GetMetaDataForPQEvent' has logic to 
-- determine the 'dominant' phase for transient event. 
DELETE FROM
    @Output
WHERE
    EventID NOT IN
    (
        SELECT 
            MIN(EventID)
        FROM
            @Output
        GROUP BY
            PQEventType
            , TimestampUTC
    )


SELECT 
    @LastProcessedTimestampUTC = TimestampUTC
FROM 
    dbo.EventLog2 
WHERE 
    ID = @LastProcessedEventID

-- return the result
SELECT
    *
FROM
    @Output

GO

IF(dbo.ProcedureExists('spPQ_GetPQEventsToReprocessForSource') = 1)
    DROP PROCEDURE spPQ_GetPQEventsToReprocessForSource
GO

/*
* The MeasurementsAsString parameter is a delimited string that contains specific measurement
* names that are characteristic of a Sag, Swell, or Transient.
*/
CREATE PROCEDURE [dbo].[spPQ_GetPQEventsToReprocessForSource]
    @SourceId INT
    , @MeasurementsAsString VARCHAR(4000)
    , @MsThreshold INT
AS

SELECT
    EventLog2.[SourceId]
    , EventLog2.[ID] AS [EventId]
    , EventLog2.TimestampUTC AS [TimeStampUtc]
    , cause.String AS [Cause]
    , causeValue.String AS [CauseValue]
    , effect.String AS [Effect]
    , effectValue.String AS [EffectValue]
    , EventLog2.ID AS [EventLogID]
    , (SELECT TOP 1 
                    ID 
                FROM dbo.udfPQ_GetPQDataLogEntryWithinThreshold(
                        EventLog2.SourceId,
                        EventLog2.TimestampUTC,
                        @MeasurementsAsString,
                        @MsThreshold)) AS [DataLogId]
FROM
    dbo.EventLog2
INNER JOIN
    Source src ON (EventLog2.SourceID = src.ID)
INNER JOIN
    dbo.EventString cause ON (EventLog2.CauseID = cause.ID)
INNER JOIN
    dbo.EventString causeValue ON (EventLog2.CauseValueID = causeValue.ID)
INNER JOIN
    dbo.EventString effect ON (EventLog2.EffectID = effect.ID)
INNER JOIN
    dbo.EventString effectValue ON (EventLog2.EffectValueID = effectValue.ID)
WHERE
    EventLog2.SourceID=@SourceID
    AND EventLog2.ID IN 
    (
        SELECT 
            e.EventlogID
        FROM 
            dbo.PQ_Events e
            JOIN dbo.PQ_EventsToReprocess r on r.EventID = e.EventID
        WHERE
            e.SourceID = @SourceID
    )
ORDER BY
    EventLog2.[Id]
GO

IF(dbo.ProcedureExists('DeleteUnusedSource') = 1)
    DROP PROCEDURE DeleteUnusedSource
GO

--*****************************************************************************
-- PROCEDURE: DeleteUnusedSource
--
-- NOTES:
--
--*****************************************************************************

/*************************************************************************************
 Deletes the source specified if there are no data references to it
 This includes entries in DataLog2, Waveform or EventLog2
**************************************************************************************/
CREATE PROCEDURE DeleteUnusedSource
(
    @Namespace NVARCHAR(250),
    @Name NVARCHAR(200),
    @Deleted BIT OUTPUT 
)
AS
SET NOCOUNT ON
SET @Deleted = 0
DECLARE @id INT
Set @id = NULL

SELECT @id = s.Id From Source s INNER JOIN 
Namespace n on n.Id = s.NamespaceId
Where n.Name = @Namespace and s.Name = @Name

IF @id IS NULL
BEGIN
   RAISERROR  ('Unknown Source ''%s@%s''', 16, 1, @Name, @Namespace)
   RETURN -1
END
ELSE
BEGIN
  IF NOT EXISTS (SELECT * FROM dbo.DataLog2 WHERE SourceID=@id)
    AND NOT EXISTS (SELECT * FROM dbo.WaveformLog2 WHERE SourceID=@id)
    AND NOT EXISTS (SELECT * FROM dbo.EventLog2 WHERE SourceID=@id)
  BEGIN
    BEGIN TRANSACTION
    DELETE FROM RecorderState WHERE RecorderId IN (Select ID FROM Recorder WHERE SourceID = @id)
    DELETE FROM Channel WHERE RecorderId IN (Select ID FROM Recorder WHERE SourceID = @id)
    DELETE FROM Recorder WHERE SourceID = @id
    DELETE FROM SourceState WHERE SourceID=@id
    DELETE FROM Device WHERE ID=@id
    DELETE FROM Source WHERE ID = @id
    COMMIT TRANSACTION
  SET @Deleted = 1
  END
END

RETURN

GO

IF(dbo.ProcedureExists('spPQ_GetWaveformsForPQEvents') = 1)
    DROP PROCEDURE spPQ_GetWaveformsForPQEvents
GO

/******************************************************************************
$Id: //rdsw/dev/cayman/SW_ION_E/database/Components/PowerQuality/StoredProcedures/01.spPQ_GetWaveformsForPQEvents.sql#2 $

Return waveforms that are most likely related to the specified eventIDs, within the specified
tolerance.

This is a rather complicated procedure, but I don't see a better way to do it while maintaining fast
performance.

Essentially, we have a temp table that contains a rows of [SourceID and TimestampUTC]. We
populate this with information from the event (one row for start of the event, one row for the end of the event).
We then go through each row in the temp table, using a cursor :(, and find the closest matching timestamp in the 
waveform table for that SourceID/TimestampUTC. The values are left null if there is no match.
At the end, we return the waveforms that match.

What this means is that we return the waveform(s) with a timestamp closest to the event's timestamp, NOT all waveforms
within the threshold.

******************************************************************************/
CREATE PROCEDURE dbo.spPQ_GetWaveformsForPQEvents
(
    @PQEventIDs NVARCHAR(MAX)
    , @MsThreshold INT
)
AS

DECLARE @DelimiterCharacter NCHAR(1)
DECLARE @SqlEscapeCharacter NCHAR(1)

SET @DelimiterCharacter = CHAR(6)
SET @SqlEscapeCharacter = CHAR(7)

-------------------------------------------------------------------------------
-- get source and datetime data for specified PQEventIDs
DECLARE @PQEvent TABLE 
(
    SourceId INT, 
    EventTimestampUTC DATETIME2,
    WaveformTimestampUTC DATETIME2
)

-- insert the start timestamps
INSERT INTO 
    @PQEvent
(
    SourceID,
    EventTimestampUTC,
    WaveformTimestampUTC
)
SELECT DISTINCT
    e.SourceId,
    e.StartTimestampUTC,
    w.TimestampUTC
FROM
    dbo.PQ_Events e
    -- we left join here, in case there happens to be an exact match which is much faster than the cursor method below
    LEFT JOIN dbo.Waveformlog2 w ON e.StartTimestampUTC = w.TimestampUTC
WHERE
    e.EventId IN (SELECT nstr FROM dbo.udfPQ_SplitStringListToTable(@PQEventIDs, CHAR(6)))

-- insert the end timestamps
INSERT INTO 
    @PQEvent
(
    SourceID,
    EventTimestampUTC,
    WaveformTimestampUTC
)
SELECT DISTINCT
    e.SourceId,
    e.EndTimestampUTC,
    w.TimestampUTC
FROM
    dbo.PQ_Events e
    LEFT JOIN dbo.Waveformlog2 w ON e.EndTimestampUTC = w.TimestampUTC
WHERE
    e.EventId IN (SELECT nstr FROM dbo.udfPQ_SplitStringListToTable(@PQEventIDs, CHAR(6)))


-- now go through each row, updating the Waveform timestamp info to match the nearest waveform timestamp
DECLARE EventCursor Cursor
FOR
    Select SourceID, EventTimestampUTC
    From @PQEvent
    where WaveformTimestampUTC IS NULL

OPEN EventCursor 

DECLARE @SourceID INT
DECLARE @TimestampUTC DATETIME2
DECLARE @WaveformTimestampUTC DATETIME2

FETCH NEXT FROM EventCursor INTO @SourceID, @TimestampUTC
WHILE (@@FETCH_STATUS = 0)
BEGIN

    SET @WaveformTimestampUTC = NULL
    SET @WaveformTimestampUTC = NULL

    -- the two larger and statements are similar, but the first one narrows the query down using the
    -- clustered index, and the second one further refines it.
    SELECT TOP 1
        @WaveformTimestampUTC = w.TimestampUTC
    FROM
        dbo.Waveformlog2 w
    WHERE
        w.SourceID = @SourceID
        AND w.SourceID = @SourceID
        AND w.TimestampUTC BETWEEN DATEADD(ms,-@MsThreshold,@TimestampUTC) AND DATEADD(ms,@MsThreshold,@TimestampUTC)
    ORDER BY
        ABS(DATEDIFF(ms, w.TimestampUTC, @TimestampUTC))

    UPDATE 
        @PQEvent
    SET 
        WaveformTimestampUTC = @WaveformTimestampUTC
    WHERE 
        CURRENT OF EventCursor

    FETCH NEXT FROM EventCursor INTO @SourceID, @TimestampUTC
END

CLOSE EventCursor
DEALLOCATE EventCursor


-- return the waveform data
SELECT
    w.ID as WaveformID,
    w.SourceID,
    w.QuantityID,
    dbo.DateWithoutFraction(w.TimestampUTC) AS TimestampUTC,
    dbo.FractionalSecondsAsReal(w.TimestampUTC) AS FractionOfASecond,
    w.Samples,
    w.SamplingFrequency,
    w.Scale,
    w.Offset,       
    dbo.DateWithoutFraction(w.TimeOfTriggerUTC) AS TimeOfTriggerUTC,
    dbo.FractionalSecondsAsReal(w.TimeOfTriggerUTC) AS TriggerFracOfASec,
    dbo.DateWithoutFraction(w.TimeOfFirstPointUTC) AS TimeOfFirstPointUTC,
    dbo.FractionalSecondsAsReal(w.TimeOfFirstPointUTC) AS FirstFracOfASec,
    w.DataPointType
FROM
    dbo.WaveformLog2 w
WHERE
    EXISTS
    (
        SELECT 1 
        FROM @PQEvent e 
        WHERE
            w.SourceID = e.SourceID 
            AND w.TimestampUTC = e.WaveformTimestampUTC 
    )
ORDER BY
    w.TimestampUTC,
    w.SourceID,
    w.QuantityID,
    w.TimeOfFirstPointUTC

GO

IF(dbo.ProcedureExists('spPQ_GetWorstDisturbancesForReport') = 1)
    DROP PROCEDURE spPQ_GetWorstDisturbancesForReport
GO


/******************************************************************************
##BD_BEGIN
Returns Worst Disturbance data for the specified PQ report execution
NOTE: a report ID is provided as events are aggregated into incidents
on an ad-hoc basis
##BD_END 

$Id: //rdsw/dev/cayman/SW_ION_E/database/Components/PowerQuality/StoredProcedures/01.spPQ_GetWorstDisturbancesForReport.sql#2 $
******************************************************************************/

CREATE PROCEDURE dbo.spPQ_GetWorstDisturbancesForReport
(
    @ReportId AS NVARCHAR(100)
)
AS

DECLARE @IncompleteTypeName AS NVARCHAR(50)
SET @IncompleteTypeName = 'Incomplete'

DECLARE @Readings TABLE
(
    [DataLogID] BIGINT PRIMARY KEY NOT NULL
    ,[Nominal] FLOAT
    ,[MaxV1] FLOAT
    ,[MinV1] FLOAT
    ,[MaxV2] FLOAT
    ,[MinV2] FLOAT
    ,[MaxV3] FLOAT
    ,[MinV3] FLOAT
)
INSERT INTO @Readings
(
    [DataLogID]
    ,[Nominal]
    ,[MaxV1]
    ,[MinV1]
    ,[MaxV2]
    ,[MinV2]
    ,[MaxV3]
    ,[MinV3]
)
SELECT
    [ID]
    ,COALESCE([Voltage Disturbance Detection Nominal Voltage],[Voltage Transient Nominal Voltage]) AS [Nominal]
    ,COALESCE([Voltage Disturbance Maximum Voltage on Input V1],[Voltage Transient Maximum Voltage on Input V1]) AS [MaxV1]
    ,[Voltage Disturbance Minimum Voltage on Input V1] AS [MinV1]
    ,COALESCE([Voltage Disturbance Maximum Voltage on Input V2],[Voltage Transient Maximum Voltage on Input V2]) AS [MaxV2]
    ,[Voltage Disturbance Minimum Voltage on Input V2] AS [MinV2]
    ,COALESCE([Voltage Disturbance Maximum Voltage on Input V3],[Voltage Transient Maximum Voltage on Input V3]) AS [MaxV3]
    ,[Voltage Disturbance Minimum Voltage on Input V3] AS [MinV3]
FROM
(
    SELECT 
        dl.ID
        , q.[Name] AS [QuantityName]
        , dl.[Value]
    FROM
        [dbo].[DataLog2] dl
    INNER JOIN
        [dbo].[Quantity] q ON (dl.QuantityID = q.ID)
    WHERE 
        dl.ID IN 
        (
            SELECT 
                DISTINCT EndDataLogID 
            FROM 
                PQ_Events evt
            INNER JOIN 
                PQ_Incidents inc ON (evt.EventId = inc.RepresentativePQEventId)
            WHERE 
                inc.ReportId = @ReportId
        )
) AS [p]
PIVOT
(   
    MAX([Value])
    FOR [QuantityName] IN
    (
        [Voltage Disturbance Detection Nominal Voltage]
        ,[Voltage Disturbance Maximum Voltage on Input V1]
        ,[Voltage Disturbance Minimum Voltage on Input V1]
        ,[Voltage Disturbance Maximum Voltage on Input V2]
        ,[Voltage Disturbance Minimum Voltage on Input V2]
        ,[Voltage Disturbance Maximum Voltage on Input V3]
        ,[Voltage Disturbance Minimum Voltage on Input V3]
        ,[Voltage Transient Nominal Voltage]
        ,[Voltage Transient Maximum Voltage on Input V1]
        ,[Voltage Transient Maximum Voltage on Input V2]
        ,[Voltage Transient Maximum Voltage on Input V3]
    )
) AS [pvt]
ORDER BY
    ID

SELECT 
    inc.IncidentId
    , evt.EventId
    , evt.SourceId
    , evt.StartTimestampUtc AS [TimestampUtc]
    , COALESCE(evt.SubType, @IncompleteTypeName) AS [Type]
    , evt.WorstPhase AS [Phase]
    , evt.EventDuration AS [Duration]
    , evt.Magnitude
    , evt.Severity
    , [V1MaxMagnitude] = 
                (CASE r.Nominal
                    WHEN 0 THEN NULL
                    ELSE (MaxV1/r.Nominal*100)
                 END)
    , [V1MinMagnitude] = 
                (CASE r.Nominal
                    WHEN 0 THEN NULL
                    ELSE (MinV1/r.Nominal*100)
                 END)
    , [V2MaxMagnitude] = 
                (CASE r.Nominal
                    WHEN 0 THEN NULL
                    ELSE (MaxV2/r.Nominal*100)
                 END)
    , [V2MinMagnitude] = 
                (CASE r.Nominal
                    WHEN 0 THEN NULL
                    ELSE (MinV2/r.Nominal*100)
                 END)
    , [V3MaxMagnitude] = 
                (CASE r.Nominal
                    WHEN 0 THEN NULL
                    ELSE (MaxV3/r.Nominal*100)
                 END)
    , [V3MinMagnitude] = 
                (CASE r.Nominal
                    WHEN 0 THEN NULL
                    ELSE (MinV3/r.Nominal*100)
                 END)
FROM
    dbo.PQ_Incidents inc
INNER JOIN
    PQ_Events evt ON (inc.RepresentativePQEventId = evt.EventId)
LEFT OUTER JOIN
    @Readings r ON  (evt.EndDataLogID = r.DataLogID)
WHERE
    inc.ReportId = @ReportId

GO

IF(dbo.ProcedureExists('spPQ_SaveNormalisedEvent') = 1)
    DROP PROCEDURE spPQ_SaveNormalisedEvent
GO


CREATE PROCEDURE dbo.spPQ_SaveNormalisedEvent
    @SourceId INT
    ,@Type NVARCHAR(100)
    ,@StartTimeStampUtc DATETIME2
    ,@EndTimeStampUtc DATETIME2
    ,@StartDataLogId BIGINT
    ,@EndDataLogId BIGINT
    ,@IsOrphan BIT
    ,@CollectorName NVARCHAR(100)
    ,@EventDuration FLOAT
    ,@Magnitude FLOAT
    ,@Severity FLOAT
    ,@WorstPhase NVARCHAR(10)
    ,@SubType NVARCHAR(25)
    ,@EventLogID INT
    ,@DataLogRetryTimeoutMinutes INT
AS

-- check for an existing record
DECLARE @ExistingEventID INT
SELECT
    @ExistingEventID = EventID
FROM
    dbo.PQ_Events
WHERE
    SourceID = @SourceID
    AND Type = @Type
    AND EndTimestampUTC = @EndTimestampUTC
    AND CollectorName = @CollectorName

IF @ExistingEventID IS NULL
BEGIN

    INSERT INTO [dbo].[PQ_Events]
               ([SourceId]
               ,[Type]
               ,[StartTimeStampUtc]
               ,[EndTimeStampUtc]
               ,[StartDataLogId]
               ,[EndDataLogId]
               ,[EventDuration]
               ,[Magnitude]
               ,[Severity]
               ,[WorstPhase]
               ,[SubType]
               ,[CollectorName]
               ,[IsOrphan]
               ,[EventLogID])
    VALUES
               (@SourceId
               ,@Type
               ,@StartTimeStampUtc
               ,@EndTimeStampUtc
               ,@StartDataLogId
               ,@EndDataLogId
               ,@EventDuration
               ,@Magnitude
               ,@Severity
               ,@WorstPhase
               ,@SubType
               ,@CollectorName         
               ,@IsOrphan
               ,@EventLogID)

    SET @ExistingEventID = SCOPE_IDENTITY()
END
ELSE
BEGIN
    UPDATE 
        dbo.PQ_Events
    SET
        EndDataLogId = @EndDataLogId,
        -- INTEGRATION NOTE:  The following two fields were added for SW_CORSICA SCR 18201 
        -- make sure they stay as part of the integration
        StartTimestampUTC = @StartTimestampUTC,
        EventDuration = @EventDuration,
        Magnitude = @Magnitude,
        Severity = @Severity,
        WorstPhase = @WorstPhase,
        SubType = @SubType,
        IsOrphan = @IsOrphan,
        EventLogID = @EventLogID
    WHERE
        SourceID = @SourceID
        AND Type = @Type
        AND EndTimestampUTC = @EndTimestampUTC
        AND CollectorName = @CollectorName
    
END

IF @SubType = 'Incomplete'
BEGIN
    -- we don't look for PQ data for Starts, since they never have PQ data
    IF @DataLogRetryTimeoutMinutes > 0
    BEGIN
        EXEC dbo.spPQ_AddEventToReprocess @ExistingEventID, @DataLogRetryTimeoutMinutes
    END
END
ELSE
BEGIN
    EXEC dbo.spPQ_RemoveEventToReprocess @ExistingEventID
END

GO

IF(dbo.ProcedureExists('spPQ_TimeSliceAggregation') = 1)
    DROP PROCEDURE spPQ_TimeSliceAggregation
GO

CREATE PROCEDURE spPQ_TimeSliceAggregation
    @FilterSourceIds AS NVARCHAR(MAX)
    ,@FilterFromDate AS DATETIME
    ,@FilterToDate AS DATETIME
    ,@AggregatePeriod AS INT
    ,@ReportId AS VARCHAR(100)
AS

--variables
DECLARE @EventId VARCHAR(100)
DECLARE @SourceId AS INT
DECLARE @StartTimeStampUtc AS DATETIME2
DECLARE @EndTimeStampUtc AS DATETIME2
DECLARE @Severity AS FLOAT
--calculated vars
DECLARE @IncidentId AS INT
DECLARE @EventSequence AS INT
DECLARE @IncidentStartTimestampUtc AS DATETIME

--initiate cursor
DECLARE c1 CURSOR LOCAL FAST_FORWARD READ_ONLY
FOR

--get raw dataset: all pq events in the daterange for specified sources
SELECT 
    EventId
    , SourceId
    , StartTimeStampUtc
    , EndTimeStampUtc
    , Severity
FROM 
    dbo.PQ_Events
WHERE 
    SourceId IN (SELECT ID FROM dbo.udfDAL_GetSources(@FilterSourceIds))
    AND StartTimeStampUtc >= @FilterFromDate
    AND EndTimeStampUtc <= @FilterToDate
ORDER BY 
    StartTimeStampUtc ASC

OPEN c1

FETCH NEXT FROM c1
INTO @EventId, @SourceId, @StartTimeStampUtc, @EndTimeStampUtc, @Severity

WHILE @@FETCH_STATUS = 0
BEGIN
    IF(@IncidentId IS NULL)
    BEGIN
        --first event in date range discovered, always create a new incident
        --set the incident start timestamp
        --note: this if branch is ONLY hit for the first event
        SET @IncidentId = 1
        SET @EventSequence = 1      

        INSERT INTO PQ_Incidents(IncidentId, RepresentativePQEventId, ReportId, ExpiryTimeStamp)
        VALUES(@IncidentId, @EventId, @ReportId, DATEADD(hour, 3, GETDATE()))
                
        SET @IncidentStartTimestampUtc = @StartTimeStampUtc
        
        --map this event to incident
        INSERT INTO PQ_IncidentEventMap(ReportId, IncidentId, EventId, EventSequence)
        VALUES(@ReportId, @IncidentId, @EventId, @EventSequence)
    END
    ELSE IF(DATEDIFF(ss,@IncidentStartTimestampUtc, @StartTimeStampUtc) < @AggregatePeriod)
    BEGIN   
        --pq event's timestamp was less than the incident start time + period
        --event in same incident
        SET @EventSequence = @EventSequence + 1     

        INSERT INTO PQ_IncidentEventMap(ReportId, IncidentId, EventId, EventSequence)
        VALUES(@ReportId, @IncidentId, @EventId, @EventSequence)
        
        --determine if this event is the incident's 'worst' event
        IF(
            ISNULL(@Severity,0) > 
                (
                SELECT 
                    ISNULL(Severity,0)
                FROM 
                    PQ_Incidents inc 
                    JOIN PQ_Events evt ON inc.RepresentativePQEventId = evt.EventId
                WHERE 
                    inc.IncidentId = @IncidentId 
                    AND inc.ReportId = @ReportId
                )
            )
        BEGIN
            UPDATE PQ_Incidents
            SET RepresentativePQEventID = @EventId
            WHERE IncidentId = @IncidentId
        END
    END
    ELSE
    BEGIN
        --event was more than the incident start time + period
        --it is unrelated to the previous incident, create a new
        --incident
        SET @IncidentStartTimestampUtc = @StartTimeStampUtc
        SET @EventSequence = 1
        SET @IncidentId = @IncidentId + 1

        --event in new incident
        INSERT INTO PQ_Incidents(IncidentId, RepresentativePQEventId, ReportId, ExpiryTimeStamp)
        VALUES(@IncidentId, @EventId, @ReportId, DATEADD(hour, 3, GETDATE()))

        INSERT INTO PQ_IncidentEventMap(ReportId, IncidentId, EventId, EventSequence)
        VALUES(@ReportId, @IncidentId, @EventId, @EventSequence)
    END

    FETCH NEXT FROM c1
    INTO @EventId, @SourceId, @StartTimeStampUtc, @EndTimeStampUtc, @Severity
END

CLOSE c1
DEALLOCATE c1
SET NOCOUNT OFF

--deletes 'expired' incidents from dbo.PQ_Incidents
--cascades delete to dbo.PQ_IncidentEventMap
DELETE FROM PQ_Incidents WHERE ExpiryTimeStamp < GETDATE()

GO

IF(dbo.ProcedureExists('spPQ_UnityAggregation') = 1)
    DROP PROCEDURE spPQ_UnityAggregation
GO

CREATE PROCEDURE [dbo].[spPQ_UnityAggregation]
    @FilterSourceIds AS NVARCHAR(MAX)
    ,@FilterFromDate AS DATETIME
    ,@FilterToDate AS DATETIME
    ,@AggregatePeriod AS INT
    ,@ReportId AS VARCHAR(100)
AS

--variables
DECLARE @EventId VARCHAR(100)
DECLARE @SourceId AS INT
DECLARE @StartTimeStampUtc AS DATETIME2
DECLARE @EndTimeStampUtc AS DATETIME2
DECLARE @Severity AS FLOAT
--calculated vars
DECLARE @IncidentId AS INT
DECLARE @EventSequence AS INT
DECLARE @IncidentStartTimestampUtc AS DATETIME

--initiate cursor
DECLARE c1 CURSOR LOCAL FAST_FORWARD READ_ONLY
FOR

--get raw dataset: all pq events in the daterange for specified sources
SELECT 
    EventId
    , SourceId
    , StartTimeStampUtc
    , EndTimeStampUtc
    , Severity
FROM 
    dbo.PQ_Events
WHERE 
    SourceId IN (SELECT ID FROM dbo.udfDAL_GetSources(@FilterSourceIds))
    AND StartTimeStampUtc >= @FilterFromDate
    AND EndTimeStampUtc <= @FilterToDate
ORDER BY 
    StartTimeStampUtc ASC
OPEN c1

FETCH NEXT FROM c1
INTO @EventId, @SourceId, @StartTimeStampUtc, @EndTimeStampUtc, @Severity

WHILE @@FETCH_STATUS = 0
BEGIN
    IF(@IncidentId IS NULL)
    BEGIN
        --first event in date range discovered, always create a new incident
        --set the incident start timestamp
        --note: this if branch is ONLY hit for the first event
        SET @IncidentId = 1
        SET @EventSequence = 1      

        INSERT INTO PQ_Incidents(IncidentId, RepresentativePQEventId, ReportId, ExpiryTimeStamp)
        VALUES(@IncidentId, @EventId, @ReportId, DATEADD(hour, 3, GETDATE()))
                
        SET @IncidentStartTimestampUtc = @StartTimeStampUtc
        
        --map this event to incident
        INSERT INTO PQ_IncidentEventMap(ReportId, IncidentId, EventId, EventSequence)
        VALUES(@ReportId, @IncidentId, @EventId, @EventSequence)
    END 
    ELSE
    BEGIN
        --Unity Aggregator always creates a new incident for
        --a new PQ Event
        SET @IncidentStartTimestampUtc = @StartTimeStampUtc
        SET @EventSequence = 1
        SET @IncidentId = @IncidentId + 1

        --event in new incident
        INSERT INTO PQ_Incidents(IncidentId, RepresentativePQEventId, ReportId, ExpiryTimeStamp)
        VALUES(@IncidentId, @EventId, @ReportId, DATEADD(hour, 3, GETDATE()))
        SET @IncidentId = SCOPE_IDENTITY()

        INSERT INTO PQ_IncidentEventMap(ReportId, IncidentId, EventId, EventSequence)
        VALUES(@ReportId, @IncidentId, @EventId, @EventSequence)
    END

    FETCH NEXT FROM c1
    INTO @EventId, @SourceId, @StartTimeStampUtc, @EndTimeStampUtc, @Severity
END

CLOSE c1
DEALLOCATE c1
SET NOCOUNT OFF

--deletes 'expired' incidents from dbo.PQ_Incidents
--cascades delete to dbo.PQ_IncidentEventMap
DELETE FROM PQ_Incidents WHERE ExpiryTimeStamp < GETDATE()

GO

IF(dbo.ProcedureExists('spDAL_GetAlarms') = 1)
    DROP PROCEDURE spDAL_GetAlarms
GO


/******************************************************************************
##BD_BEGIN
Return three tables of data:
 1. All time zones in the system that are associated with a device.
 2. All devices in the system.
 4. Event Log data that matches the input parameters.
##BD_END 

$Id: //rdsw/dev/cayman/SW_ION_E/database/Components/DAL/StoredProcedures/01.spDAL_GetAlarms.sql#2 $
******************************************************************************/
CREATE PROCEDURE [dbo].[spDAL_GetAlarms]
(
    @SourceIDs NVARCHAR(MAX),
    @StartDatesUtc NVARCHAR(MAX),
    @EndDatesUtc NVARCHAR(MAX),
    @LowPriorities NVARCHAR(MAX),
    @HighPriorities NVARCHAR(MAX)
)
AS

DECLARE @DelimiterCharacter NCHAR(1)
DECLARE @SqlEscapeCharacter NCHAR(1)

SET @DelimiterCharacter = CHAR(6)
SET @SqlEscapeCharacter = CHAR(7)

-------------------------------------------------------------------------------
-- get all the sources, filtering if needed
DECLARE @TblSources TABLE
(
    ID INT PRIMARY KEY NOT NULL,
    Name NVARCHAR(250) NOT NULL,
    Namespace NVARCHAR(400) NOT NULL,
    Description NVARCHAR(2000),
    TimeZoneID SMALLINT NOT NULL
)
INSERT INTO @TblSources
(
    ID,
    Name,
    Namespace,
    Description,
    TimeZoneID
)
SELECT
    ID,
    Name,
    Namespace,
    Description,
    TimeZoneID
FROM
    dbo.udfDAL_GetSources(@SourceIDs)

---------------------------------------------------------------------------------
SELECT
    CAST (s.ID as int) as SourceID,
    dbo.DateWithoutFraction(el.TimestampUTC) AS TimestampUTC,
    dbo.FractionalSecondsAsReal(el.TimestampUTC) AS FractionOfASecond,
    CAST(el.Priority as int) as Priority,
    Cause,
    CauseValue,
    Effect,
    EffectValue
FROM
    vEventLog el 
    JOIN @TblSources s on el.SourceID = s.ID
WHERE 
    EXISTS
    (
        SELECT
            1
        FROM
            udf_SplitDateListsToTable(@StartDatesUtc, @EndDatesUtc, @DelimiterCharacter) ts
        WHERE
            el.TimestampUTC >= ts.StartDate 
            AND el.TimestampUTC <= ts.EndDate
    ) AND EXISTS
    (

        SELECT
            1
        FROM
            udf_SplitIntListsToTable(@lowPriorities, @highPriorities, @DelimiterCharacter) p
        WHERE
            el.Priority >= p.LowerBound 
            AND el.Priority <= p.UpperBound
    )
GO

IF(dbo.ProcedureExists('IONEEM_GetSourceQuantityPairs') = 1)
    DROP PROCEDURE IONEEM_GetSourceQuantityPairs
GO


--*****************************************************************************
-- PROCEDURE: IONEEM_GetSourceQuantityPairs
--
-- NOTES: Return a list of all source quantity pairs.
--
--*****************************************************************************

CREATE PROCEDURE dbo.IONEEM_GetSourceQuantityPairs
AS

SELECT DISTINCT
    src.ID AS N'SourceID',
    src.Name AS N'SourceName',
    ns.Name AS N'NamespaceName', 
    qt.ID AS N'QuantityID', 
    qt.[Name] AS N'QuantityName' 
FROM         
    dbo.Source src
    JOIN dbo.Namespace ns ON ns.ID = src.NamespaceID
    JOIN dbo.Recorder rec ON src.ID = rec.SourceID
    JOIN dbo.Channel ch ON ch.RecorderID = rec.ID
    JOIN dbo.Quantity qt ON qt.ID = ch.QuantityID

RETURN 0

GO


IF(dbo.ProcedureExists('RemoveDuplicateEventLogs') = 1)
    DROP PROCEDURE RemoveDuplicateEventLogs
GO

--*****************************************************************************
-- PROCEDURE: RemoveDuplicateEventLogs
--
-- NOTES:
--*****************************************************************************
	SET QUOTED_IDENTIFIER ON
GO
/*****************************************************************************	
	Removes all the duplicates from the EventLog table.
******************************************************************************/
CREATE PROCEDURE [dbo].[RemoveDuplicateEventLogs] (
@SourceIDFilter INT = NULL,
@DateFromFilter datetime = NULL,
@DateToFilter datetime = NULL
)
AS
SET NOCOUNT ON
DECLARE evCursor CURSOR LOCAL FAST_FORWARD READ_ONLY FOR

SELECT SourceID, TimestampUTC, Priority, CauseID, CauseValueID, EffectID, EffectValueID, MAX(ID) As MaxID, COUNT(*)
FROM dbo.EventLog2
WHERE (@SourceIDFilter IS NULL OR SourceID = @SourceIDFilter)
AND (@DateFromFilter IS NULL OR TimestampUTC >= @DateFromFilter)
AND (@DateToFilter IS NULL OR TimestampUTC <= @DateToFilter)
GROUP BY TimestampUTC, SourceID, Priority, CauseID, CauseValueID, EffectID, EffectValueID
HAVING COUNT(*) > 1

DECLARE @SourceID INT
DECLARE @TimestampUTC DATETIME2
DECLARE @Priority SMALLINT
DECLARE @CauseID INT
DECLARE @CauseValueID INT
DECLARE @EffectID INT
DECLARE @EffectValueID INT
DECLARE @MaxID  INT
DECLARE @Count INT

-- Open the cursor.
OPEN evCursor

-- Loop through all the duplicates in the waveform table
FETCH NEXT FROM evCursor  INTO @SourceID, @TimestampUTC, @Priority, 
	@CauseID, @CauseValueID, @EffectID, @EffectValueID, @MaxID, @Count

WHILE @@FETCH_STATUS = 0
BEGIN
	-- update the EventAcknowledgement items that use any of these IDS less than the maxID to MaxID
	UPDATE EventAcknowledgement
	SET EventLogID=@MaxID
	WHERE EventLogID IN (
		SELECT ID
		FROM dbo.EventLog2
		WHERE TimestampUTC = @TimestampUTC AND 
		Priority=@Priority AND SourceID = @SourceID AND 
		(CauseID=@CauseID Or (CauseID IS NULL AND @CauseID IS NULL)) AND 
		(CauseValueID=@CauseValueID Or (CauseValueID IS NULL AND @CauseValueID IS NULL)) AND 
		(EffectID=@EffectID Or (EffectID IS NULL AND @EffectID IS NULL)) AND 
		(EffectValueID=@EffectValueID Or (EffectValueID IS NULL AND @EffectValueID IS NULL)) AND  
		ID < @MaxID
	)
		
	-- delete all the duplicate eventlog entries less than maximum ID
	DELETE FROM dbo.EventLog2 WHERE TimestampUTC = @TimestampUTC AND 
		Priority=@Priority AND SourceID = @SourceID AND 
		(CauseID=@CauseID Or (CauseID IS NULL AND @CauseID IS NULL)) AND 
		(CauseValueID=@CauseValueID Or (CauseValueID IS NULL AND @CauseValueID IS NULL)) AND 
		(EffectID=@EffectID Or (EffectID IS NULL AND @EffectID IS NULL)) AND 
		(EffectValueID=@EffectValueID Or (EffectValueID IS NULL AND @EffectValueID IS NULL)) AND  
		ID < @MaxID

	FETCH NEXT FROM evCursor  INTO @SourceID, @TimestampUTC, @Priority, 
		@CauseID, @CauseValueID, @EffectID, @EffectValueID, @MaxID, @Count
END
-- Close and deallocate the cursor.
CLOSE evCursor
DEALLOCATE evCursor

RETURN 0

GO
	SET QUOTED_IDENTIFIER OFF
GO

IF(dbo.ProcedureExists('spEVT_vwEventLog_GetLatestForSources') = 1)
    DROP PROCEDURE spEVT_vwEventLog_GetLatestForSources
GO

--*****************************************************************************
--
-- $Id: //rdsw/dev/cayman/SW_ION_E/database/Components/EventWatcherData/StoredProcedures/01.spEVT_vwEventLog_GetLatestForSources.sql#2 $
-- 
--*****************************************************************************
CREATE PROCEDURE [dbo].[spEVT_vwEventLog_GetLatestForSources]
(@FirstEntryID int = NULL,
 @LastEntryID int = NULL,
 @SourceNames as nvarchar(MAX) = NULL,
 @DatabaseDSN nvarchar(1000))

AS
    
BEGIN

DECLARE @DelimiterCharacter NCHAR(1)
DECLARE @SqlEscapeCharacter NCHAR(1)

SET @DelimiterCharacter = CHAR(6)
SET @SqlEscapeCharacter = CHAR(7)
-------------------------------------------------------------------------------
-- get all the sources, filtering if needed
DECLARE @TblSources TABLE
(
    ID INT PRIMARY KEY NOT NULL,
    Name NVARCHAR(250) NOT NULL
)

-- '*' means ALL SOURCES
IF (@SourceNames != '*')
BEGIN
    INSERT INTO @TblSources
    (
        ID,
        Name
    )
    SELECT
        ID,
        Name
    FROM
        Source
    WHERE
        ID IN (SELECT ID FROM Source WHERE [Name] IN (SELECT nstr FROM dbo.udf_SplitStringListToTable(@SourceNames,',')))
END

---------------------------------------------------------------------------------

DECLARE @updateDate as datetime

IF (@FirstEntryID is NULL)
    BEGIN
        SET @updateDate = (SELECT Value from Registry WHERE Name = 'LastUpdated')
        IF (@updateDate IS NULL)
            SET @updateDate = CONVERT(DATETIME,'2008-01-01T00:00:00')

        SET @FirstEntryID = (Select TOP 1 ID from dbo.EventLog2 WHERE TimestampUTC > @updateDate order by ID ASC)
    END



IF (@SourceNames = '*')
BEGIN
    SELECT vwEVT_EventLog.ID as EventLogID, CAST(vwEVT_EventLog.SourceID AS INT) as SourceID,  TimestampUTC,
        Priority, Cause, CauseValue, Effect, EffectValue
    FROM vwEVT_EventLog
    WHERE vwEVT_EventLog.ID BETWEEN @FirstEntryID and @LastEntryID
END
ELSE
BEGIN
    SELECT vwEVT_EventLog.ID as EventLogID, sources.ID as SourceID,  TimestampUTC, 
        Priority, Cause, CauseValue, Effect, EffectValue
    FROM vwEVT_EventLog inner join @TblSources sources on vwEVT_EventLog.SourceID = sources.ID 
    WHERE vwEVT_EventLog.ID BETWEEN @FirstEntryID and @LastEntryID
END


END
GO

IF(dbo.FunctionExists('DateAsUnix') = 1)
    DROP FUNCTION [dbo].[DateAsUnix]
GO

--*****************************************************************************
-- FUNCTION: DateAsUnix
--
-- NOTES:
--
--*****************************************************************************

CREATE FUNCTION dbo.DateAsUnix
(
    @Date AS DATETIME2
)
RETURNS FLOAT AS
BEGIN
    RETURN DATEDIFF(second, CONVERT(DATETIME,'1970-01-01T00:00:00'), @Date) + DATEPART(nanosecond, @Date) / 1000000000.0
END
GO
    GRANT SELECT ON [dbo].[vDataLogChannelValue] TO [ION_Reader]
GO

IF(dbo.FunctionExists('UnixAsDate') = 1)
    DROP FUNCTION [dbo].[UnixAsDate]
GO

--*****************************************************************************
-- FUNCTION: UnixAsDate
--
-- NOTES:
--
--*****************************************************************************
CREATE FUNCTION dbo.UnixAsDate 
(
    @Date FLOAT
) 
RETURNS DATETIME2 AS 
BEGIN 
    DECLARE @Seconds FLOAT
    SET @Seconds = FLOOR(@Date)
    RETURN DATEADD(nanosecond,(@Date-@Seconds)*1000000000,DATEADD(s,@Seconds,CONVERT(DATETIME2,'1970-01-01T00:00:00')))
END
GO
    GRANT SELECT ON [dbo].[vDataLogChannelValue] TO [ION_Reader]
GO

IF(dbo.FunctionExists('udf_SplitBigIntListToTable') = 1)
    DROP FUNCTION [dbo].[udf_SplitBigIntListToTable]
GO

--*****************************************************************************
-- FUNCTION: udf_SplitBigIntListToTable
--
-- NOTES:
--   Takes a string representation of a list of BIGINTs, splits it into
--   BIGINT values based on the end-of-item char, and returns it as a table.
--   NULL values are specified by an empty string. The final end-of-item char
--   is optional if the last value is non-null.
--
-- EXAMPLE 1:
--   @list='1:55:3:4:', @endOfItemChar=':'.
--
--    listpos     number
--    1           1
--    2           55
--    3           3
--    4           4
--
-- EXAMPLE 2:
--   @list=':55:3::', @endOfItemChar=':'.
--
--    listpos     number
--    1           NULL
--    2           55
--    3           3
--    4           NULL
--
CREATE FUNCTION [dbo].[udf_SplitBigIntListToTable]
(
    @list NVARCHAR(MAX),
    @endOfItemChar NCHAR(1) = N','
)
RETURNS @tbl TABLE
(
    listpos INT PRIMARY KEY IDENTITY(1, 1) NOT NULL,
    number BIGINT NULL
)
AS
BEGIN

    -- local vars
    DECLARE @StartDelimiter INT
    DECLARE @EndDelimiter INT
    DECLARE @TempString NVARCHAR(MAX)

    -- quit early.
    if @list IS NULL
        RETURN

    -- add the final delimiter, if omitted. (Use DATALENGTH/2, because LEN trims whitespace.)
    IF SUBSTRING(@list,DATALENGTH(@list)/2,1) <> @endOfItemChar
        SET @list = @list + @endOfItemChar

    -- get the first delimiter's position
    SET @StartDelimiter = 0
    SET @EndDelimiter = CHARINDEX(@endOfItemChar, @list, @StartDelimiter+1)
    WHILE @EndDelimiter > 0
    BEGIN

        SET @TempString = SUBSTRING(@list,@StartDelimiter+1, @EndDelimiter-@StartDelimiter-1)

        IF @EndDelimiter-@StartDelimiter = 1
            INSERT @tbl (number) VALUES (NULL)
        ELSE
            INSERT @tbl (number) VALUES (CONVERT(BIGINT,@TempString))

        SET @StartDelimiter = @EndDelimiter
        SET @EndDelimiter = CHARINDEX(@endOfItemChar, @list, @StartDelimiter+1)

    END

    RETURN

END
GO

IF(dbo.FunctionExists('udf_SplitBitListToTable') = 1)
    DROP FUNCTION [dbo].[udf_SplitBitListToTable]
GO


--*****************************************************************************
-- FUNCTION: udf_SplitBitListToTable
--
-- NOTES:
--   Takes a string representation of a list of BITs, splits it into BIT values
--   based on the end-of-item char, and returns it as a table. NULL values are
--   specified by an empty string. The final end-of-item char is optional if
--   the last value is non-null.
--
--   The BIT values must be either 0 or 1, or blank for a NULL. This function
--   does NOT support true/false, T/F, etc.
--
-- EXAMPLE 1:
--   @list='1:0:', @endOfItemChar=':'. 
--
--    listpos     flag
--    1           1
--    2           0
--
-- EXAMPLE 2:
--   @list='1::0:', @endOfItemChar=':'. 
--
--    listpos     flag
--    1           1
--    2           NULL
--    3           0
--
--*****************************************************************************
CREATE FUNCTION [dbo].[udf_SplitBitListToTable] 
(
    @list NVARCHAR(MAX),
    @endOfItemChar NCHAR(1) = N','
)
RETURNS @tbl TABLE 
(
    listpos INT PRIMARY KEY IDENTITY(1, 1) NOT NULL,
    flag BIT NULL
)
AS
BEGIN

    -- local vars
    DECLARE @StartDelimiter INT
    DECLARE @EndDelimiter INT
    DECLARE @TempString NVARCHAR(MAX)

    -- quit early.
    if @list IS NULL
        RETURN
        
    -- add the final delimiter, if omitted. (Use DATALENGTH/2, because LEN trims whitespace.)
    IF SUBSTRING(@list,DATALENGTH(@list)/2,1) <> @endOfItemChar
        SET @list = @list + @endOfItemChar
        
    -- get the first delimiter's position
    SET @StartDelimiter = 0
    SET @EndDelimiter = CHARINDEX(@endOfItemChar, @list, @StartDelimiter+1)
    WHILE @EndDelimiter > 0
    BEGIN

        SET @TempString = SUBSTRING(@list,@StartDelimiter+1, @EndDelimiter-@StartDelimiter-1)       
        
        IF @EndDelimiter-@StartDelimiter = 1
            INSERT @tbl (flag) VALUES (NULL)
        ELSE
            INSERT @tbl (flag) VALUES (CONVERT(BIT,@TempString))
        
        SET @StartDelimiter = @EndDelimiter
        SET @EndDelimiter = CHARINDEX(@endOfItemChar, @list, @StartDelimiter+1)

    END

    RETURN
    
END
GO

IF(dbo.FunctionExists('udf_SplitFloatListToTable') = 1)
    DROP FUNCTION [dbo].[udf_SplitFloatListToTable]
GO


--*****************************************************************************
-- FUNCTION: udf_SplitFloatListToTable
--
-- NOTES:
--   Takes a string representation of a list of FLOATs, splits it into 
--   FLOAT values based on the end-of-item char, and returns it as a table. 
--   NULL values are specified by an empty string. The final end-of-item char 
--   is optional if the last value is non-null.
--
-- EXAMPLE 1:
--   SELECT * FROM dbo.udf_SplitFloatListToTable (N'1.1:55:3.1415:-5:',N':')
--      listpos     number
--      1           1.1
--      2           55.0
--      3           3.1415
--      4           -5
--
-- EXAMPLE 2:
--   SELECT * FROM dbo.udf_SplitFloatListToTable (N':55:3.1415::',N':')
--      listpos     number
--      1           NULL
--      2           55.0
--      3           3.1415
--      4           NULL
--
--*****************************************************************************
CREATE FUNCTION [dbo].[udf_SplitFloatListToTable] 
(
    @list NVARCHAR(MAX),
    @endOfItemChar NCHAR(1) = N','
)
RETURNS @tbl TABLE 
(
    listpos INT PRIMARY KEY IDENTITY(1, 1) NOT NULL,
    number FLOAT NULL
)
AS
BEGIN

    -- local vars
    DECLARE @StartDelimiter INT
    DECLARE @EndDelimiter INT
    DECLARE @TempString NVARCHAR(MAX)

    -- quit early.
    if @list IS NULL
        RETURN
        
    -- add the final delimiter, if omitted. (Use DATALENGTH/2, because LEN trims whitespace.)
    IF SUBSTRING(@list,DATALENGTH(@list)/2,1) <> @endOfItemChar
        SET @list = @list + @endOfItemChar
        
    -- get the first delimiter's position
    SET @StartDelimiter = 0
    SET @EndDelimiter = CHARINDEX(@endOfItemChar, @list, @StartDelimiter+1)
    WHILE @EndDelimiter > 0
    BEGIN

        SET @TempString = SUBSTRING(@list,@StartDelimiter+1, @EndDelimiter-@StartDelimiter-1)       
        
        IF @EndDelimiter-@StartDelimiter = 1
            INSERT @tbl (number) VALUES (NULL)
        ELSE
            INSERT @tbl (number) VALUES (CONVERT(FLOAT,@TempString))
        
        SET @StartDelimiter = @EndDelimiter
        SET @EndDelimiter = CHARINDEX(@endOfItemChar, @list, @StartDelimiter+1)

    END

    RETURN
    
END
GO

IF(dbo.FunctionExists('udf_SplitNVarCharListToTable') = 1)
    DROP FUNCTION [dbo].[udf_SplitNVarCharListToTable]
GO

--*****************************************************************************
-- FUNCTION: udf_SplitNVarCharListToTable
--
-- NOTES:
--   Takes a string representation of a list of strings, splits it into
--   string values based on the end-of-item char, and returns it as a table.
--   NULL values are specified by the special null character. The final
--   end-of-item char is optional.
--
--   NOTE: To specify a NULL, you must specify the null character, with no
--   whitespace around it.
--
-- EXAMPLE 1:
--   SELECT listpos, '"' + string + '"' FROM dbo.udf_SplitNVarCharListToTable (N'Test 1:  test 2  ::',N':',N'+')
--      listpos     number
--      1           "Test 1"
--      2           "  test 2  "
--      3           ""
--
-- EXAMPLE 2:
--   SELECT listpos, '"' + string + '"' FROM dbo.udf_SplitNVarCharListToTable (N'Test 1:  test 2  : :+::',N':',N'+')
--      listpos     number
--      1           "Test 1"
--      2           "  test 2  "
--      3           " "
--      4           NULL
--      5           ""
--
-- $Id: //rdsw/dev/cayman/SW_ION_E/database/Components/ION/UserDefinedFunctions/02.MaxDeviant.sql#2 $
--
--*****************************************************************************
CREATE FUNCTION [dbo].[udf_SplitNVarCharListToTable]
(
    @list NVARCHAR(MAX),
    @endOfItemChar NCHAR(1) = N',',
    @nullChar NCHAR(1)
)
RETURNS @tbl TABLE
(
    listpos INT PRIMARY KEY IDENTITY(1, 1) NOT NULL,
    string NVARCHAR(MAX) NULL
)
AS
BEGIN

    -- local vars
    DECLARE @StartDelimiter INT
    DECLARE @EndDelimiter INT
    DECLARE @TempString NVARCHAR(MAX)

    -- quit early.
    if @list IS NULL
        RETURN
        
    -- add the final delimiter, if omitted. (Use DATALENGTH/2, because LEN trims whitespace.)
    IF SUBSTRING(@list,DATALENGTH(@list)/2,1) <> @endOfItemChar
        SET @list = @list + @endOfItemChar
        
    -- get the first delimiter's position
    SET @StartDelimiter = 0
    SET @EndDelimiter = CHARINDEX(@endOfItemChar, @list, @StartDelimiter+1)
    WHILE @EndDelimiter > 0
    BEGIN

        SET @TempString = SUBSTRING(@list,@StartDelimiter+1, @EndDelimiter-@StartDelimiter-1)       
        
        IF @TempString = @nullChar
            INSERT @tbl (string) VALUES (NULL)
        ELSE
            INSERT @tbl (string) VALUES (@TempString)
        
        SET @StartDelimiter = @EndDelimiter
        SET @EndDelimiter = CHARINDEX(@endOfItemChar, @list, @StartDelimiter+1)

    END

    RETURN

END
GO

IF(dbo.FunctionExists('udf_SplitRealListToTable') = 1)
    DROP FUNCTION [dbo].[udf_SplitRealListToTable]
GO

--*****************************************************************************
-- FUNCTION: udf_SplitRealListToTable
--
-- NOTES:
--   Takes a string representation of a list of REALs, splits it into 
--   REAL values based on the end-of-item char, and returns it as a table. 
--   NULL values are specified by an empty string. The final end-of-item char 
--   is optional if the last value is non-null.
--
-- EXAMPLE 1:
--   SELECT * FROM dbo.udf_SplitRealListToTable (N'1.1:55:3.1415:-5:',N':')
--      listpos     number
--      1           1.1
--      2           55.0
--      3           3.1415
--      4           -5
--
-- EXAMPLE 2:
--   SELECT * FROM dbo.udf_SplitRealListToTable (N':55:3.1415::',N':')
--      listpos     number
--      1           NULL
--      2           55.0
--      3           3.1415
--      4           NULL
--
--*****************************************************************************
CREATE FUNCTION [dbo].[udf_SplitRealListToTable] 
(
    @list NVARCHAR(MAX),
    @endOfItemChar NCHAR(1) = N','
)
RETURNS @tbl TABLE 
(
    listpos INT PRIMARY KEY IDENTITY(1, 1) NOT NULL,
    number REAL NULL
)
AS
BEGIN

    -- local vars
    DECLARE @StartDelimiter INT
    DECLARE @EndDelimiter INT
    DECLARE @TempString NVARCHAR(MAX)

    -- quit early.
    if @list IS NULL
        RETURN
        
    -- add the final delimiter, if omitted. (Use DATALENGTH/2, because LEN trims whitespace.)
    IF SUBSTRING(@list,DATALENGTH(@list)/2,1) <> @endOfItemChar
        SET @list = @list + @endOfItemChar
        
    -- get the first delimiter's position
    SET @StartDelimiter = 0
    SET @EndDelimiter = CHARINDEX(@endOfItemChar, @list, @StartDelimiter+1)
    WHILE @EndDelimiter > 0
    BEGIN

        SET @TempString = SUBSTRING(@list,@StartDelimiter+1, @EndDelimiter-@StartDelimiter-1)       
        
        IF @EndDelimiter-@StartDelimiter = 1
            INSERT @tbl (number) VALUES (NULL)
        ELSE
            INSERT @tbl (number) VALUES (CONVERT(REAL,@TempString))
        
        SET @StartDelimiter = @EndDelimiter
        SET @EndDelimiter = CHARINDEX(@endOfItemChar, @list, @StartDelimiter+1)

    END

    RETURN
    
END
GO

IF(dbo.ProcedureExists('ChkTempSpace') = 1)
    DROP PROCEDURE ChkTempSpace
GO

--*****************************************************************************
-- PROCEDURE: ChkTempSpace
--
-- NOTES:
--
-- $Id: //rdsw/dev/cayman/SW_ION_E/database/Common/StoredProcedures/02.ChkTempSpace.sql#2 $
--
--*****************************************************************************

/*************************************************************************************************
Determines if sufficient drive space remains for tempdb to load the largest
table within an ION_Data, ION_Network or ION_SystemLog database. Use DataLog2 if ION_Data, 
TS_Triples if ION_Network, or Event if ION_SystemLog

Any task that requires loading database tables into SQL system database tempdb
will fail if tempdb cannot get about 10-20% larger than the largest table
tempdb is set to unrestricted size, so the real limit is determined by how large it is
and how much space remains on the hard drive.
*************************************************************************************************/

CREATE PROCEDURE ChkTempSpace
AS
SET NOCOUNT ON

DECLARE @TableSizeInMB NVARCHAR(255)
DECLARE @Drive NVARCHAR(1024)
DECLARE @tempSize NVARCHAR(255)
DECLARE @DriveSpace INT
DECLARE @DrvSp FLOAT

--get largest table size in MB + 15% buffer
CREATE TABLE #T1
(
    tablename varchar (255) COLLATE database_default,
    numRows varchar(255) COLLATE database_default,
    reservedKB varchar(255) COLLATE database_default,
    dataKB varchar(255) COLLATE database_default,
    indexKB varchar(255) COLLATE database_default,
    unused varchar(255) COLLATE database_default
)
-- use check for largest table to make appropriate choice given database
IF EXISTS (SELECT name from sysobjects WHERE name = 'DataLog2')
BEGIN
INSERT INTO #T1 EXEC('sp_spaceused Datalog2')
END
IF EXISTS (SELECT name from sysobjects WHERE name = 'TS_Triples')
BEGIN
INSERT INTO #T1 EXEC('sp_spaceused TS_Triples')
END
IF EXISTS (SELECT name from sysobjects WHERE name = 'Event')
BEGIN
INSERT INTO #T1 EXEC('sp_spaceused Event')
END

SELECT @TableSizeInMB= reservedKB FROM #T1
Set @TableSizeInMB = (REPLACE(@TableSizeInMB,'KB','')/1024)*1.15
DROP TABLE #T1
--PRINT 'tablesize '+@TableSizeInMB

--get drive tempdb resides on and tempdev size
CREATE TABLE #T2
(
    _name varchar (255) COLLATE database_default,
    file_location varchar(255) COLLATE database_default,
    _group varchar(255) COLLATE database_default,
    _size varchar(255) COLLATE database_default,
    _maxSize varchar(255) COLLATE database_default,
    _growth varchar(255) COLLATE database_default,
    usage varchar(255) COLLATE database_default
)
INSERT INTO #T2 EXEC('tempdb..sp_helpfile tempdev')
SELECT @Drive = file_location from #T2
Set @Drive = Left(@Drive,1)
SELECT @tempSize = _size from #T2
Set @tempSize = (REPLACE(@tempSize,'KB','')/1024)
DROP TABLE #T2
--PRINT 'tempdb drive '+@Drive
--PRINT 'tempdb size '+@tempSize

-- Use xp_fixeddrives extended procedure to determine the remaining space on drive, in MB
CREATE TABLE #T3(DrvLetter NVARCHAR(5) COLLATE database_default, DrvSpace INT)
INSERT INTO #T3 EXEC master.dbo.xp_fixeddrives
SELECT @DriveSpace = DrvSpace from #T3 where DrvLetter = @Drive
DROP TABLE #T3
Set @DrvSp = @DriveSpace
--PRINT @DrvSp
--if largest table > than space remaining +tempdb size then indexing and updating statistics will likely fill drive
If (@TableSizeInMB > @DrvSp+@tempSize)
    BEGIN
        DECLARE @msg NVARCHAR(255)
        Set @msg = 'Insufficient space is available on '+ @Drive +': drive to execute the requested task. The system database tempdb must be able to grow to ' +@TableSizeInMB+ ' MB to complete the requested task.'
        RAISERROR (@msg,16, 1)
    END

RETURN 0
GO

IF(dbo.ProcedureExists('GetPQData') = 1)
    DROP PROCEDURE GetPQData
GO

--*****************************************************************************
/* ##BD_BEGIN

<p>This stored procedure is used by the Event Adapter to load PQ data.</p>

##BD_END */
--*****************************************************************************
CREATE PROCEDURE dbo.GetPQData
(
    @TimestampUTC DATETIME2,
    @SourceID INT,
    @QuantityIDs NVARCHAR(MAX)
)
AS

-- Create a temp table to hold the data
CREATE TABLE #GetPQData 
    (
    QuantityID SMALLINT, 
    Value FLOAT
    )

-- make sure at least SOME quantity IDs were passed
IF DATALENGTH(@QuantityIDs) = 0
BEGIN
    -- return no rows
    SELECT
        QuantityID,
        Value
    FROM
        #GetPQData
    WHERE
        0=1

    RETURN
END

-- DETERMINE WHETHER THIS IS A GE HARRIS DEVICE. This is commented out
-- for now (so it is optimized), since each customer will have a 
-- different method of determining the GE Harris devices.

/*
-- local var to indicate whether the device we are
-- querying is a GE Harris device, which needs to
-- be handled in a special way.
DECLARE @UseGEHarrisMethod BIT
SET @UseGEHarrisMethod = 0

-- Get the source name in order to determine if it is ION, GE Harris, or Hathaway
DECLARE @SourceName NVARCHAR(250)
SELECT @SourceName = Name FROM Source WHERE ID=@SourceID
IF @SourceName LIKE 'RTU_%'
BEGIN
    @UseGEHarrisMethod = 1
END

IF @UseGEHarrisMethod
BEGIN
    EXEC GetPQDataGEHarris @TimestampUTC, @SourceID, @QuantityIDs
END
ELSE
BEGIN
*/
    -- this is the default handler
    EXEC GetPQDataStandard @TimestampUTC, @SourceID, @QuantityIDs
/*
END
*/

-- Return the results
SELECT
    QuantityID,
    Value
FROM
    #GetPQData

-- drop the table
DROP TABLE #GetPQData
GO

--*****************************************************************************
-- Channel: IsCurrentConfiguration default value constraint
-- Remove the unnamed constraint from the Channel table, this is poor pratice
-- and makes future upgrades more complex. Replace with a new default value 
-- constraint called 'DF_Channel_IsCurrentConfiguration'.
--*****************************************************************************
DECLARE @defaultValueConstraintName sysname
SELECT @defaultValueConstraintName = object_name(default_object_id)
FROM sys.columns
WHERE object_id = object_id('dbo.Channel')
AND name = 'IsCurrentConfiguration'

IF(dbo.DefaultValueConstraintExists('Channel', @defaultValueConstraintName) = 1)
BEGIN
    EXEC ('ALTER TABLE dbo.Channel DROP CONSTRAINT ' + @defaultValueConstraintName)
END

ALTER TABLE 
    [dbo].[Channel]
ADD CONSTRAINT
    [DF_Channel_IsCurrentConfiguration]
DEFAULT 
    ((1)) 
FOR 
    [IsCurrentConfiguration]


IF(dbo.ProcedureExists('InsertChannel') = 1)
    DROP PROCEDURE InsertChannel
GO
--*****************************************************************************
-- PROCEDURE: InsertChannel
--
-- NOTES:
--
-- $Id: //rdsw/dev/cayman/SW_ION_E/database/Components/ION/StoredProcedures/02.InsertChannel.sql#3 $
--
--*****************************************************************************

/*************************************************************************************
    Inserts a new channel.
**************************************************************************************/
CREATE PROCEDURE [dbo].[InsertChannel]
(
    @RecorderID INT,
    @Name NVARCHAR(50) ,
    @Label NVARCHAR(200),
    @QuantityID SMALLINT,
    @ID INT OUTPUT
)
AS
SET NOCOUNT ON

DECLARE @InputDeviceID INT
SELECT @InputDeviceID=SourceID FROM Recorder WHERE ID=@RecorderID

SET @ID=NULL
-- Check if the channel already exists
SELECT @ID=ID FROM Channel WHERE RecorderID=@RecorderID AND Name=@Name
-- Find the most recent entry for the register as identified by its name and device.
-- Note that the InputDeviceID in the table could be NULL after an upgrade.
SELECT @ID=MAX(ID) FROM Channel WHERE RecorderID=@RecorderID AND Name=@Name 
            AND (InputDeviceID IS NULL OR InputDeviceID=@InputDeviceID)

IF @ID IS NULL
BEGIN
    INSERT INTO Channel (RecorderID, Name, Label, QuantityID, InputDeviceID, IsCurrentConfiguration)
        VALUES (@RecorderID, @Name, @Label, @QuantityID, @InputDeviceID, 1)
    SET @ID = SCOPE_IDENTITY()
END
RETURN 0
GO

IF(dbo.ProcedureExists('GetWagesData') = 1)
    DROP PROCEDURE GetWagesData
GO
--*****************************************************************************
-- PROCEDURE: GetWagesData
--
-- NOTES:
--
-- $Id: //rdsw/dev/cayman/SW_ION_E/database/Components/ION/StoredProcedures/02.GetWagesData.sql $
--
--*****************************************************************************
CREATE Procedure [dbo].[GetWagesData] 
(
    @tablename nvarchar(max)
)
AS
declare 
        @recorderindex int,
        @recordername nvarchar(max),
        @devicename nvarchar(max),
        @recorderanddevice nvarchar(max),
        @at int,
        @sourceID int,
        @quantityIDs nvarchar(max),
        @quantityIDs1 nvarchar(max),
        @labels nvarchar(max) ,
        @utctimediffinminutes int 

set @recorderindex = len('product(LogServer)')+ patindex('%product(LogServer)%' , @tablename)

set @recorderanddevice = substring(@tablename, @recorderindex, len(@tablename))
set @at = patindex('%@%',@recorderanddevice)

set @recordername = ltrim(rtrim(substring(@recorderanddevice,1,@at-1)))
set @devicename = ltrim(rtrim(substring(@recorderanddevice,@at+1,patindex('%*%',@recorderanddevice)-@at-1)))

set @utctimediffinminutes = DATEDIFF (minute, GETUTCDATE(), GETDATE())

set @sourceid = 0
SELECT @sourceid = ID from Source where [Name] = @devicename ; --';' is required because next statement is a Common Table Expression

WITH QuantityIDsAndLables (QuantityID,Label) AS
(
SELECT   convert(nvarchar(max),Channel.QuantityID) AS QuantityID 
        ,CASE WHEN patindex('%Date and Time%',Channel.Label)>0 THEN 
            'convert(Nvarchar(max), dateadd(minute, DATEDIFF (minute, GETUTCDATE(), GETDATE()), dateadd(ss,['+ convert(nvarchar(max),Channel.QuantityID) +'],''1970/01/01'')),101) +''''+ Right(convert(nvarchar(max), dateadd(minute, DATEDIFF (minute, GETUTCDATE(), GETDATE()),dateadd(ss,['+ convert(nvarchar(max),Channel.QuantityID) +'],''1970/01/01'')),9),15) AS [' +  Channel.Label + ']'
         ELSE '['+ convert(nvarchar(max),Channel.QuantityID) + '] AS [' + Channel.Label + ']' END AS Label
    FROM Source 
            INNER JOIN Recorder on Source.ID = Recorder.SourceID 
            INNER JOIN Channel on Recorder.ID = Channel.RecorderID
    WHERE   Source.Name = @devicename 
            AND Recorder.Label = @recordername 
            AND Channel.IsCurrentConfiguration=1
)
SELECT  @quantityIDs = coalesce(@quantityIDs + ', ','')+ '['+ QuantityID +']', 
        @quantityIDs1 = coalesce(@quantityIDs1 + ', ','')+ QuantityID,
        @labels = coalesce(@labels + ', ','')+ Label FROM QuantityIDsAndLables 

declare @Query Nvarchar(max)
set @Query = ' SELECT '''+@devicename+''' as node ,'''+@recordername+''' as log, dbo.DateAsUnix(TimestampUTC) as timestamp, '+ @Labels +'  FROM ( '
set @Query = @Query + ' SELECT [Value], QuantityID, TimestampUTC FROM DataLog2 WHERE QuantityID IN ('+ @quantityIDs1 +') AND SourceId=' + Convert(nvarchar(max),@sourceID)
set @Query = @Query + ' ) Data '
set @Query = @Query + ' PIVOT ( '
set @Query = @Query + ' MAX ([Value]) FOR QuantityID IN ('+ @QuantityIDs +') '
set @Query = @Query + ' ) AS Results order by TimestampUTC desc'

EXEC (@Query)
GO

IF(dbo.ProcedureExists('InsertChannelsBulk') = 1)
    DROP PROCEDURE InsertChannelsBulk
GO

--*****************************************************************************
-- PROCEDURE: InsertChannelsBulk
--
-- NOTES: The 'Label' is used if no label can be found in the DefaultLabelQuantity
--        table for the associated QuantityID.
--
--        No explicit validation of the input parameters is performed, but things
--        will blow up if foreign key constraints or string length maximums are
--        violated.
--
-- The parameters that are lists (@RecorderIDs, @Names, @Lables, etc)
-- must be delimited by the @DelimiterChar. See the dbo.udf_SplitIntListToTable
-- for more information on how to pass NULLs, etc.
--
-- If InputDeviceIDs are null, they will be replaced by the SourceID of the 
-- source associated with the Recorder for the channel.
--
-- The specified labels will only be used there is no default label for the
-- specified quantities.
--
-- $Id: //customsw/rel/etl_egx_ione_v1/CUSTOMSW_UTILS/IONEnterpriseUtilities/DatabaseScripts/IonEnterpriseLoadTaskInstallScript.sql#1 $
--
--*****************************************************************************
CREATE PROCEDURE [dbo].[InsertChannelsBulk]
(
    @RecorderIDs NVARCHAR(MAX), --INT,
    @Names NVARCHAR(MAX), -- NVARCHAR(50)
    @Labels NVARCHAR(MAX), --NVARCHAR(200)
    @QuantityIDs NVARCHAR(MAX), --SMALLINT
    @InputDeviceIDs NVARCHAR(MAX), --INT
    @IsCurrentConfigurations NVARCHAR(MAX), --BIT
    @DelimiterChar NCHAR(1)  -- the character used to separate values in the preceeding parameters.
)
AS
SET NOCOUNT ON

DECLARE @t TABLE
(
    RecorderID INT,
    Name NVARCHAR(50),
    Label NVARCHAR(200),
    QID SMALLINT,
    InputDeviceID INT,
    IsCurrentConfigurations BIT
)

INSERT INTO 
    @t (RecorderID, Name, Label, QID, InputDeviceID, IsCurrentConfigurations)
SELECT
    Recorders.number,
    Names.string,
    Labels.string,
    Quantities.number,
    InputDeviceIDs.number, 
    IsCurrentConfigurations.flag
FROM
    dbo.udf_SplitBigIntListToTable(@RecorderIDs, @DelimiterChar) Recorders
    JOIN dbo.udf_SplitNVarCharListToTable(@Names, @DelimiterChar, CHAR(5)) Names ON recorders.listpos = names.listpos
    JOIN dbo.udf_SplitNVarCharListToTable(@Labels, @DelimiterChar, CHAR(5)) Labels ON recorders.listpos = Labels.listpos
    JOIN dbo.udf_SplitSmallIntListToTable(@QuantityIDs, @DelimiterChar) Quantities ON Recorders.listpos = Quantities.listpos
    JOIN dbo.udf_SplitIntListToTable(@InputDeviceIDs, @DelimiterChar) InputDeviceIDs ON Recorders.listpos = InputDeviceIDs.listpos
    JOIN dbo.udf_SplitBitListToTable(@IsCurrentConfigurations, @DelimiterChar) IsCurrentConfigurations ON Recorders.listpos = IsCurrentConfigurations.listpos
WHERE
    NOT EXISTS (SELECT 1 FROM Channel c WHERE c.RecorderID = Recorders.number and c.Name=Names.string)

UPDATE
    @t
SET
    InputDeviceID = ISNULL(InputDeviceID,(SELECT SourceID FROM Recorder WHERE ID = RecorderID)),
    Label = ISNULL((SELECT TOP 1 Label FROM DefaultLabelQuantity WHERE DefaultLabelQuantity.QuantityID = QID),Label)

INSERT INTO 
    Channel (RecorderID, Name, Label, QuantityID, InputDeviceID, IsCurrentConfiguration)
SELECT
    RecorderID,
    Name,
    Label,
    QID,
    InputDeviceID,
    IsCurrentConfigurations
FROM
    @t

RETURN 0

GO

GRANT SELECT ON  [dbo].[DataLog2] TO [mma_Archiver]
GO

IF OBJECT_ID('dbo.TrimRecords') IS NOT NULL
BEGIN
	DROP PROCEDURE [dbo].[TrimRecords]
END
GO
	SET QUOTED_IDENTIFIER ON
GO
/***********************************************************
    TrimRecords: 
  Optionally trim Datalog,waveforms,
  events.
  Will trim data > StartDate and < EndDate
  Optional Time parameter
  determines time of day to trim to
  If not passed, then midnight local time will be used.
  If passed it must be local time in ODBC format.
  Calls LocalToUTCDate stored procedure  
**********************************************************/

CREATE PROCEDURE TrimRecords
(
 @StartDate DATETIME,
 @EndDate DATETIME,
 @TrimDataLog BIT=1, 
 @TrimWaveForm BIT=1,
 @TrimEvent BIT=1
) 
AS
SET NOCOUNT ON
DECLARE @RetVal INT
SET @RetVal = 0
BEGIN
     Exec @RetVal = LocalToUTCDate @StartDate, @StartDate OUTPUT
     IF (@RetVal <> 0)
           GOTO DONE
     Exec @RetVal = LocalToUTCDate @EndDate, @EndDate OUTPUT
     IF (@RetVal <> 0)
           GOTO DONE
     IF (@TrimWaveForm = 1)
     BEGIN
           DELETE FROM dbo.WaveformLog2 WHERE TimestampUTC >= @StartDate AND TimestampUTC <= @EndDate
     END
     IF (@TrimEvent = 1)
     BEGIN           
           CREATE TABLE #T2(EventId INT)
           INSERT INTO #T2 Select ID FROM dbo.EventLog2 WHERE TimestampUTC >= @StartDate AND TimestampUTC <= @EndDate
           DELETE FROM EventAcknowledgement WHERE EventLogID IN (Select EventId FROM #T2)
         --following NOT IN clause for when user acknowledged event between table deletes
         --would otherwise violate foreign key constraint
           DELETE FROM dbo.EventLog2 WHERE ID IN (Select EventId FROM #T2) AND ID NOT IN (Select EventAcknowledgement.EventLogID from EventAcknowledgement)
           DROP TABLE #T2 
		   --CORSICA SCR#18073: Remove PQ_Events
		   DELETE FROM PQ_Events WHERE StartTimestampUTC >= @StartDate AND StartTimestampUTC <= @EndDate 
     END
     IF (@TrimDataLog = 1)
     BEGIN
        DECLARE @tbl TABLE (DatalogID BIGINT)
        INSERT INTO @tbl (DatalogID)
            SELECT TOP 100000 ID
            FROM dbo.DataLog2
            WHERE TimestampUTC >= @StartDate and TimestampUTC <= @EndDate
    
        WHILE EXISTS (SELECT 1 FROM @tbl)
        BEGIN
            DELETE FROM dbo.DataLog2 WHERE EXISTS (SELECT 1 FROM @tbl WHERE DataLogID = ID)
            DELETE FROM @tbl
            INSERT INTO @tbl (DatalogID)
                SELECT TOP 100000 ID
                FROM dbo.DataLog2
                WHERE TimestampUTC >= @StartDate and TimestampUTC <= @EndDate
        END
    
     END

END
DONE:
RETURN @RetVal
GO
	SET QUOTED_IDENTIFIER OFF
GO
	SELECT dbo.FormatStatusMessage('Step 9: Completed.')
GO

