
--Dropping constraints from [dbo].[DeviceType]'

ALTER TABLE [dbo].[DeviceType] DROP CONSTRAINT [DF__DeviceTyp__IONBu__08EA5793]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[udf_SplitDateTimeListToTable]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
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 not exists (select * from sysobjects where name='SRC_LDTypeMeasurement' and  xtype = 'U')

CREATE TABLE [dbo].[SRC_LDTypeMeasurement]
(
[MeasurementID] [smallint] NOT NULL,
[SourceTypeID] [smallint] NOT NULL
)
GO

ALTER TABLE [dbo].[SRC_LDTypeMeasurement] ADD CONSTRAINT [PK_SRC_LDTypeMeasurement] PRIMARY KEY CLUSTERED  ([MeasurementID], [SourceTypeID])
GO

if exists (select * from sysobjects where name='spSRC_LDTypeMeasurement_Delete' and  xtype = 'P')
DROP PROCEDURE spSRC_LDTypeMeasurement_Delete
GO

--*****************************************************************************
/* ##BD_BEGIN
Used to delete a measurement association with source types.

<P><I>For more information, follow the Source link at the top of the page.</I></P>
##BD_END */
--
-- $Id: //rdsw/dev/cayman/SW_ION_E/database/Components/LogicalDevice/StoredProcedures/02.spSRC_LDTypeMeasurement_Delete.sql#2 $
-- 
--*****************************************************************************

CREATE PROCEDURE [dbo].[spSRC_LDTypeMeasurement_Delete]
	-- Add the parameters for the stored procedure here
	@MeasurementName nvarchar(250),
	@SourceTypeName nvarchar(250)
AS
--DECLARE @retval INT
BEGIN
	DECLARE @MeasurementID smallint
	DECLARE @SourceTypeID smallint
	SELECT @MeasurementID = MM_MeasurementMap.ID from MM_MeasurementMap where MM_MeasurementMap.StandardName = @MeasurementName
	SELECT @SourceTypeID = SRC_SourceType.SourceTypeID from SRC_SourceType where SRC_SourceType.Name = @SourceTypeName
	IF EXISTS 
		(
			SELECT
				SRC_LDTypeMeasurement.MeasurementID
			FROM 
				dbo.SRC_LDTypeMeasurement 
			WHERE 
				MeasurementID = @MeasurementID
		)
	BEGIN
		DELETE FROM Src_LDTypeMeasurement 
		WHERE MeasurementID = @MeasurementId AND SourceTypeID = @SourceTypeID
	END
	ELSE
	BEGIN
		RAISERROR('Measurement\Source Type is not present is the system ', 16, 1)
	END
END
--SET @retval = @@ERROR
--RETURN @retval

GO

if exists (select * from sysobjects where name='spSRC_LDTypeMeasurement_RetrieveByType' and  xtype = 'P')
DROP PROCEDURE spSRC_LDTypeMeasurement_RetrieveByType
GO

--*****************************************************************************
/* ##BD_BEGIN
Used to retrieve all measurements for a given source type.

<P><I>For more information, follow the Source link at the top of the page.</I></P>
##BD_END */
--
-- $Id: //rdsw/dev/cayman/SW_ION_E/database/Components/LogicalDevice/StoredProcedures/02.spSRC_LDTypeMeasurement_RetrieveByType.sql#2 $
-- 
--*****************************************************************************

CREATE PROCEDURE [dbo].[spSRC_LDTypeMeasurement_RetrieveByType]
	-- Add the parameters for the stored procedure here
	@SourceTypeName nvarchar(250)
AS
BEGIN
	DECLARE @SourceTypeID smallint
	SET @SourceTypeID = -1
	SELECT @SourceTypeID = SRC_SourceType.SourceTypeID from SRC_SourceType where SRC_SourceType.Name = @SourceTypeName
	IF(@SourceTypeID = -1)
	BEGIN
		RAISERROR('Type ''%s'' is not found in the system',16,1,@SourceTypeName)
	END
	ELSE
	BEGIN
		select MM_MeasurementMap.StandardName from MM_MeasurementMap left join SRC_LDTypeMeasurement
		on MM_MeasurementMap.ID = SRC_LDTypeMeasurement.MeasurementID where SRC_LDTypeMeasurement.SourceTypeID=@SourceTypeID
	END
END
GO

--Altering [dbo].[DeviceType]'

ALTER TABLE [dbo].[DeviceType] ALTER COLUMN [IONBufferSize_bytes] [int] NOT NULL
GO

if not exists (select * from sysobjects where name='LogicalDevice' and  xtype = 'U')
CREATE TABLE [dbo].[LogicalDevice]
(
[ID] [int] NOT NULL IDENTITY(50000, 1),
[Name] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[LogicalDeviceTypeID] [smallint] NOT NULL CONSTRAINT [DF_LogicalDevice_LogicalDeviceTypeID] DEFAULT ((12)),
[Description] [nvarchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Enabled] [bit] NOT NULL CONSTRAINT [DF_LogicalDevice_Enabled] DEFAULT ((1)),
[LastUpdate] [timestamp] NOT NULL
)
GO

--Creating primary key [PK_LogicalDevice] on [dbo].[LogicalDevice]'

ALTER TABLE [dbo].[LogicalDevice] ADD CONSTRAINT [PK_LogicalDevice] PRIMARY KEY NONCLUSTERED  ([ID])
GO

if exists (select * from sysobjects where name='spNOM_Device_ValidateName' and  xtype = 'P')
DROP PROCEDURE [dbo].[spNOM_Device_ValidateName]
GO

--*****************************************************************************
/* ##BD_BEGIN
Determines if the given Device name is valid, which means it has to be non-NULL,
non-empty and unique.

Typical use cases:
* Making sure a new Device's name is valid and unique before inserting it into
the database.
* Renaming an existing Device and making sure that the new name is valid and
unique.

This stored procedure will also allow a single Device to be excluded from the
query that attempts to find an existing Device with the same name as the one that
was passed in.  This is meant to handle the case where we're updating the
configuration information for a single Device, but its name is not changing.

<P><I>For more information, follow the Source link at the top of the page.</I></P>
##BD_END 
##PD aDeviceName ^^ Name to be checked for validity.
##PD anExcludedDeviceID ^^ Numeric identifier for a Device to exclude when the procedure is looking for a Device that has the same name as the one that was passed in; can be NULL, meaning that no Device will be excluded.
*/
--
-- $Id: //rdsw/dev/cayman/SW_ION_E/database/Components/NOM/StoredProcedures/03.spNOM_Device_ValidateName.sql#2 $
-- 
--*****************************************************************************
CREATE PROCEDURE dbo.spNOM_Device_ValidateName
(
    @aDeviceName NVARCHAR(100),
    @anExcludedDeviceID INT = NULL,
	@aPhysicalDevice BIT = 1
)
AS

SET NOCOUNT ON

-- Define a "constant" for the generic error integer that we will return from this stored procedure
-- if anything goes wrong.
DECLARE @aGeneralError INT
SET @aGeneralError = 1

-- Check to see if the Device Name is NULL or the empty string.
IF ( (@aDeviceName IS NULL) OR (LEN(@aDeviceName) <= 0) )
BEGIN
	RAISERROR ( 'DB_NOM_Device_InvalidName', 16, 1 )
	RETURN @aGeneralError
END

-- Check to make sure that the Device Name is unique, excluding a particular Device from the search
-- if necessary.
IF ( @anExcludedDeviceID IS NULL )
BEGIN
	IF ( EXISTS(SELECT 1 FROM dbo.Device WHERE [Name] = @aDeviceName) )
	BEGIN
		RAISERROR ( 'DB_NOM_Device_NameAlreadyExists', 16, 1 )
		RETURN @aGeneralError
	END
	
	--Check in logical device name as well.
	IF ( EXISTS(SELECT 1 FROM dbo.LogicalDevice WHERE [Name] = @aDeviceName) )
	BEGIN
		RAISERROR ( 'DB_NOM_Device_NameAlreadyExists', 16, 1 )
		RETURN @aGeneralError
	END
END
ELSE
BEGIN
	IF @aPhysicalDevice = 1
	BEGIN
		IF ( EXISTS(SELECT 1 FROM dbo.Device WHERE ([Name] = @aDeviceName) AND NOT([ID] = @anExcludedDeviceID)) )
		BEGIN
			RAISERROR ( 'DB_NOM_Device_NameAlreadyExists', 16, 1 )
			RETURN @aGeneralError
		END
		IF ( EXISTS(SELECT 1 FROM dbo.LogicalDevice WHERE [Name] = @aDeviceName) )
		BEGIN
			RAISERROR ( 'DB_NOM_Device_NameAlreadyExists', 16, 1 )
			RETURN @aGeneralError
		END	
	END
	ELSE
	BEGIN
		IF ( EXISTS(SELECT 1 FROM dbo.LogicalDevice WHERE ([Name] = @aDeviceName) AND NOT([ID] = @anExcludedDeviceID)) )
			BEGIN
				RAISERROR ( 'DB_NOM_Device_NameAlreadyExists', 16, 1 )
				RETURN @aGeneralError
			END
			IF ( EXISTS(SELECT 1 FROM dbo.Device WHERE [Name] = @aDeviceName) )
			BEGIN
				RAISERROR ( 'DB_NOM_Device_NameAlreadyExists', 16, 1 )
				RETURN @aGeneralError
			END	
		END
	END

-- If we've gotten this far, the Device Name is valid; therefore, we can return 0
-- to indicate success.
RETURN 0

GO

if exists (select * from sysobjects where name='spIAS_GetAllMeasurements' and  xtype = 'P')
DROP PROCEDURE spIAS_GetAllMeasurements
GO

CREATE PROCEDURE [dbo].[spIAS_GetAllMeasurements]
	@Scheme NVARCHAR(10),
	@SourceName NVARCHAR(250)
	
AS

SET NOCOUNT ON

SELECT  n.Name AS Namespace, s.Name AS Source, t.Name AS SourceType, m.StandardName AS Measurement, 
	a.IsManual AS IsManual, a.IsDefault AS IsDefault, u.Name AS [User], a.TimeResolvedUTC AS TimeResolvedUTC,
	a.Segment1 AS Segment1, a.Segment2 AS Segment2, a.Segment3 AS Segment3
	FROM  dbo.IAS_MeasurementAddress a 
		INNER JOIN dbo.SRC_Source s ON a.SourceID = s.SourceID 
			AND (s.Name = @SourceName)
		INNER JOIN dbo.MM_MeasurementMap m ON a.MeasurementID = m.ID
		INNER JOIN dbo.IAS_Services v ON a.ServicesID = v.ID AND v.Scheme=@Scheme
		INNER JOIN dbo.SRC_Namespace n ON s.NamespaceID = n.NamespaceID
		INNER JOIN dbo.SRC_SourceCategory t ON s.SourceCategoryID = t.SourceCategoryID
		INNER JOIN dbo.IAS_User u ON a.UserID = u.ID

RETURN

GO

if exists (select * from sysobjects where name='spNOM_UpdateSerialNumber' and  xtype = 'P')
DROP PROCEDURE spNOM_UpdateSerialNumber
GO

/******************************************************************************
##BD_BEGIN
TODO
##BD_END 

$Id: //rdsw/dev/cayman/SW_ION_E/database/Components/NOM/StoredProcedures/03.spNOM_UpdateSerialNumber.sql#1 $
******************************************************************************/

CREATE PROCEDURE [dbo].[spNOM_UpdateSerialNumber]
(
	@SerialNumber NVARCHAR(2000)
)
AS
	
BEGIN TRANSACTION 
	
IF NOT EXISTS (SELECT * FROM [dbo].[Registry] WITH (UPDLOCK, ROWLOCK, HOLDLOCK) WHERE [Name] = 'SerialNumber' )
	BEGIN
		INSERT INTO [dbo].[Registry] ([Machine], [Name], [Value], [ReadOnly], [Description])
			VALUES ( NULL, 'SerialNumber', @SerialNumber, 'False', NULL ) 
	END
else
	BEGIN
		UPDATE [dbo].[Registry] 
			SET [dbo].[Registry].[Value] = @SerialNumber
			WHERE [Name] = 'SerialNumber'
	END
	
COMMIT

GO

if exists (select * from sysobjects where name='spIAS_DeletePDMappingsForLogDevices' and  xtype = 'P')
DROP PROCEDURE spIAS_DeletePDMappingsForLogDevices
GO

--*****************************************************************************
/* ##BD_BEGIN
Used to delete all measurements of a physical device associated to all logical devices

<P><I>For more information, follow the Source link at the top of the page.</I></P>
##BD_END */
--
-- $Id: //rdsw/dev/cayman/SW_ION_E/database/Components/LogicalDevice/StoredProcedures/15.spIAS_DeletePDMappingsForLogDevices.sql#15$
-- 
--*****************************************************************************



CREATE PROCEDURE [dbo].[spIAS_DeletePDMappingsForLogDevices]
    @DeviceName NVARCHAR(200)
AS
BEGIN
    DELETE FROM IAS_MeasurementAddress WHERE Segment2 LIKE (@DeviceName + '/%') AND SourceID in (SELECT SourceID FROM SRC_Source WHERE SourceCategoryID = 6)
END

GO

if exists (select * from sysobjects where name='spIAS_UpdatePDNameMappingsForLogDevices' and  xtype = 'P')
DROP PROCEDURE spIAS_UpdatePDNameMappingsForLogDevices
GO

--*****************************************************************************
/* ##BD_BEGIN
Used to update all physical device name in mappings for all associated logical devices

<P><I>For more information, follow the Source link at the top of the page.</I></P>
##BD_END */
--
-- $Id: //rdsw/dev/cayman/SW_ION_E/database/Components/IONServices/AddressStore/StoredProcedures/13.spIAS_UpdatePDNameMappingsForLogDevices.sql#13$
-- 
--*****************************************************************************

CREATE PROCEDURE [dbo].[spIAS_UpdatePDNameMappingsForLogDevices]
    @OriginalDeviceName NVARCHAR(200),
    @updatedDeviceName NVARCHAR(200)
AS
BEGIN
    UPDATE IAS_MeasurementAddress  SET Segment2 = REPLACE(segment2, @OriginalDeviceName, @updatedDeviceName)
    WHERE Segment2 like (@OriginalDeviceName + '/%') AND SourceID in (SELECT SourceID FROM SRC_Source WHERE SourceCategoryID = 6)
END
GO

if exists (select * from sysobjects where name='spIAS_GetAssocLogDevicesForPhyDev' and  xtype = 'P')
DROP PROCEDURE spIAS_GetAssocLogDevicesForPhyDev
GO

--*****************************************************************************
/* ##BD_BEGIN
Used to retrieve all measurements for a given source type.

<P><I>For more information, follow the Source link at the top of the page.</I></P>
##BD_END */
--
-- $Id: //rdsw/dev/cayman/SW_ION_E/database/Components/IONServices/AddressStore/StoredProcedures/14.spIAS_GetAssocLogDevicesForPhyDev.sql#14$
-- 
--*****************************************************************************

CREATE PROCEDURE [dbo].[spIAS_GetAssocLogDevicesForPhyDev]
    @DeviceName NVARCHAR(200)
AS
BEGIN
    SELECT DISTINCT S.Name FROM IAS_MeasurementAddress  IAS, SRC_Source S
    WHERE IAS.SourceID = S.SourceID
    AND S.SourceCategoryID=6
    AND IAS.Segment2 LIKE (select name + '%' as name from Device WHERE name=@DeviceName)
END
GO

if exists (select * from sysobjects where name='spNOM_LogicalDevice_Delete' and  xtype = 'P')
DROP PROCEDURE spNOM_LogicalDevice_Delete
GO

--*****************************************************************************
/* ##BD_BEGIN
Modifies a small subset of the configuration information for the Device with the
given ID.  Specifically, it updates the following fields:  Name, Address, Enabled
and PortID.

NOTE:  Consider adding the Description field as well.

<P><I>For more information, follow the Source link at the top of the page.</I></P>
##BD_END 
##PD aDeviceID ^^ Numeric identifier for the Device that will be updated.
##PD aName ^^ New name for the Device.
##PD aDeviceTypeName ^^ Device type name.
##PD anEnabled ^^ New value for the flag that indicates whether or not the Device is active in the network.
##PD aValidateDeviceNameFlag ^^ Flag indicating whether or not the Device's name should be examined to make sure that it is valid.  NOTE:  This flag should only be set to 0 if the name validity check has been done prior to calling this procedure.
*/
--
-- $Id: //rdsw/dev/cayman/SW_ION_E/database/Components/NOM/StoredProcedures/03.spNOM_LogicalDevice_Delete.sql#2 $
-- 
--*****************************************************************************
CREATE PROCEDURE dbo.spNOM_LogicalDevice_Delete
(	
	@aName NVARCHAR(200)
)
AS

-- Define a "constant" for the generic error integer that we will return from this stored procedure
-- if anything goes wrong.
DECLARE @aGeneralError INT
SET @aGeneralError = 1

-- Check to see if the Device Name is NULL or the empty string.
IF ( (@aName IS NULL) OR (LEN(@aName) <= 0) )
BEGIN
	RAISERROR ( 'DB_NOM_LogicalDevice_InvalidName', 16, 1 )
	RETURN @aGeneralError
END

-- Attempt to update the information for the Device.
DELETE FROM
	dbo.LogicalDevice
WHERE
	Name = @aName

RETURN @@ERROR

GO

if exists (select * from sysobjects where name='spNOM_LogicalDevice_Insert' and  xtype = 'P')
DROP PROCEDURE spNOM_LogicalDevice_Insert
GO

--*****************************************************************************
/* ##BD_BEGIN
Inserts a new row into the Device table.

<P><I>For more information, follow the Source link at the top of the page.</I></P>
##BD_END 
##PD aName NVARCHAR(100) ^^ Name of the Device.
##PD aDeviceTypeName ^^ Logical type of the Device (e.g. "Water").
##PD aDescription ^^ More information about the Device; can be NULL.
##PD anEnabled ^^ Flag indicating whether or not this Device is active.
##PD aValidateDeviceNameFlag ^^ Flag indicating whether or not the Device's name should be examined to make sure that it is valid.  NOTE:  This flag should only be set to 0 if the name validity check has been done prior to calling this procedure.
##PD aNewDeviceID ^^ Numeric identifier for the newly-inserted Device; will be NULL if the Device could not be inserted.
*/
--
-- $Id: //rdsw/dev/cayman/SW_ION_E/database/Components/NOM/StoredProcedures/03.spNOM_LogicalDevice_Insert#2 $
-- 
--*****************************************************************************
CREATE PROCEDURE dbo.spNOM_LogicalDevice_Insert
(	
	@aName NVARCHAR(200),
	@aDeviceTypeName NVARCHAR(100),  -- 'ION 8500' by default	
	@aDescription NVARCHAR(1000) = NULL,
	@anEnabled BIT = 1,	
	@aValidateDeviceNameFlag BIT = 1,
	@aNewLogicalDeviceID INT OUTPUT
)
AS

-- Initialize the new Device ID to NULL in case something goes wrong and we need to exit prematurely.
SET @aNewLogicalDeviceID = NULL

-- Define a "constant" for the generic error integer that we will return from this stored procedure
-- if anything goes wrong.
DECLARE @aGeneralError INT
SET @aGeneralError = 1

DECLARE @aDeviceTypeID INT
SET @aDeviceTypeID = NULL

-- Check to see if the entity class is valid before continuing.
SELECT @aDeviceTypeID = SRC_SourceType.SourceTypeID FROM dbo.SRC_SourceType WHERE [NAME] = @aDeviceTypeName
IF @aDeviceTypeID IS NULL
BEGIN
	RAISERROR ( 'DB_NOM_LogicalDevice_InvalidTypeName', 16, 1 )
	RETURN @aGeneralError
END

-- Check to make sure the 'enabled' flag is valid before continuing.
IF ( @anEnabled IS NULL )
BEGIN
	RAISERROR ( 'DB_NOM_LogicalDevice_InvalidEnabledFlag', 16, 1 )
	RETURN @aGeneralError
END

-- If necessary, check to see if the Device name is valid (i.e. non-NULL, non-empty and unique)
-- before continuing.
DECLARE @aRetVal INT
IF ( @aValidateDeviceNameFlag = 1 )
BEGIN
	EXEC @aRetVal = dbo.spNOM_Device_ValidateName
		@aDeviceName = @aName
	
	IF ( NOT(@aRetVal = 0) )
	BEGIN
		-- The Device name is invalid.  An appropriate exception would've already been
		-- thrown to indicate what the problem was, so all we have to do is send the
		-- return value back to the caller.
		RETURN @aRetVal
	END
END

-- Attempt to insert the new Device.
SET @aRetVal = 0

INSERT INTO
	dbo.LogicalDevice
	(		
		Name,	
		LogicalDeviceTypeID,		
		Description,
		Enabled
	)
	VALUES
	(		
		@aName,	
		@aDeviceTypeID,		
		@aDescription,
		@anEnabled
	)

SET @aRetVal = @@ERROR

-- Return the new Device ID if the insertion was successful.
IF ( @@ROWCOUNT > 0 )
BEGIN
	SET @aNewLogicalDeviceID = SCOPE_IDENTITY()
END

RETURN @aRetVal

GO

if exists (select * from sysobjects where name='spNOM_LogicalDevice_Retrieve' and  xtype = 'P')
DROP PROCEDURE spNOM_LogicalDevice_Retrieve
GO

--*****************************************************************************
/* ##BD_BEGIN
Retrieves the logical device with given name.

NOTE:  Consider adding the Description field as well.

<P><I>For more information, follow the Source link at the top of the page.</I></P>
##BD_END 
##PD LogicalDevice ^^ Logical device name to be retrieved.
*/
--
-- $Id: //rdsw/dev/cayman/SW_ION_E/database/Components/NOM/StoredProcedures/03.spNOM_LogicalDevice_Retrieve#2 $
-- 
--*****************************************************************************
CREATE PROCEDURE dbo.spNOM_LogicalDevice_Retrieve
	@LogicalDevice NVARCHAR(200)= null	
AS

IF @LogicalDevice Is Null
BEGIN
	RAISERROR('Device name must be provided', 16, 1)
   	RETURN -1
END
SELECT ldt.Name as LogicalTypeName, ld.Description, ld.Enabled
	FROM LogicalDevice AS ld JOIN SRC_SourceType AS ldt ON ld.Name=@LogicalDevice AND ldt.SourceTypeID = ld.LogicalDeviceTypeID

GO

if exists (select * from sysobjects where name='spNOM_LogicalDevice_RetrieveAll' and  xtype = 'P')
DROP PROCEDURE spNOM_LogicalDevice_RetrieveAll
GO

--*****************************************************************************
/* ##BD_BEGIN
Retrieves all logical devices.

NOTE:  Consider adding the Description field as well.

<P><I>For more information, follow the Source link at the top of the page.</I></P>
##BD_END 
##PD aName ^^ New name for the Device.
*/
--
-- $Id: //rdsw/dev/cayman/SW_ION_E/database/Components/NOM/StoredProcedures/03.spNOM_LogicalDevice_RetrieveAll#2 $
-- 
--*****************************************************************************
CREATE PROCEDURE dbo.spNOM_LogicalDevice_RetrieveAll
AS
SELECT ld.Name as LogicalDeviceName, ldt.Name as LogicalDeviceType, ld.Description, ld.Enabled
FROM LogicalDevice AS ld JOIN SRC_SourceType AS ldt ON ldt.SourceTypeID = ld.LogicalDeviceTypeID

GO

if exists (select * from sysobjects where name='spNOM_LogicalDevice_Update' and  xtype = 'P')
DROP PROCEDURE spNOM_LogicalDevice_Update
GO

--*****************************************************************************
/* ##BD_BEGIN
Modifies a small subset of the configuration information for the logical device with the
given name.  Specifically, it updates the following fields:  Name, type, Enabled.

NOTE:  Consider adding the Description field as well.

<P><I>For more information, follow the Source link at the top of the page.</I></P>
##BD_END 
##PD aName ^^ Name for the Device.
##PD aNewName ^^ Old name for the Device.
##PD aDeviceTypeName ^^ Logical device type.
##PD anEnabled ^^ New value for the flag that indicates whether or not the Device is active in the network.
##PD aValidateDeviceNameFlag ^^ Flag indicating whether or not the Device's name should be examined to make sure that it is valid.  NOTE:  This flag should only be set to 0 if the name validity check has been done prior to calling this procedure.
*/
--
-- $Id: //rdsw/dev/cayman/SW_ION_E/database/Components/NOM/StoredProcedures/03.spNOM_LogicalDevice_Update.sql#2 $
-- 
--*****************************************************************************
CREATE PROCEDURE dbo.spNOM_LogicalDevice_Update
(
	@aName NVARCHAR(200),
	@aNewName NVARCHAR(200),
	@aDeviceTypeName NVARCHAR(100),	
	@anEnabled BIT = 1,
	@aValidateDeviceNameFlag BIT = 1
)
AS

-- Define a "constant" for the generic error integer that we will return from this stored procedure
-- if anything goes wrong.
DECLARE @aGeneralError INT
SET @aGeneralError = 1

DECLARE @aDeviceID INT
SET @aDeviceID = 0

SELECT @aDeviceID = ld.ID FROM dbo.LogicalDevice as ld WHERE NAME =  @aName

-- Check to see if the Device ID is valid (i.e. non-null and greater than 1) before continuing.
IF ( (@aDeviceID IS NULL) OR (@aDeviceID < 1) )
BEGIN
	RAISERROR ( 'DB_NOM_Device_InvalidID', 16, 1 )
	RETURN @aGeneralError
END

DECLARE @aDeviceTypeID INT
SET @aDeviceTypeID = NULL

-- Check to see if the entity class is valid before continuing.
SELECT @aDeviceTypeID = SRC_SourceType.SourceTypeID FROM dbo.SRC_SourceType WHERE [NAME] = @aDeviceTypeName
IF @aDeviceTypeID IS NULL
BEGIN
	RAISERROR ( 'DB_NOM_LogicalDevice_InvalidTypeName', 16, 1 )
	RETURN @aGeneralError
END

-- Check to make sure the 'enabled' flag is valid before continuing.
IF ( @anEnabled IS NULL )
BEGIN
	RAISERROR ( 'DB_NOM_Device_InvalidEnabledFlag', 16, 1 )
	RETURN @aGeneralError
END

-- If necessary, check to see if the Device name is valid (i.e. non-NULL, non-empty and unique)
-- before continuing.
IF ( @aValidateDeviceNameFlag = 1 )
BEGIN
	DECLARE @aRetVal INT
	EXEC @aRetVal = dbo.spNOM_Device_ValidateName
		@aDeviceName = @aNewName,
		@anExcludedDeviceID = @aDeviceID,
		@aPhysicalDevice = 0
	
	IF ( NOT(@aRetVal = 0) )
	BEGIN
		-- The Device name is invalid.  An appropriate exception would've already been
		-- thrown to indicate what the problem was, so all we have to do is send the
		-- return value back to the caller.
		RETURN @aRetVal
	END
END

-- Attempt to update the information for the Device.
UPDATE
	dbo.LogicalDevice
SET
	Name = @aNewName,
	LogicalDeviceTypeId = @aDeviceTypeID,	
	Enabled = @anEnabled
WHERE
	ID = @aDeviceID

RETURN @@ERROR

GO

if exists (select * from sysobjects where name='spSRC_LDTypeMeasurement_Add' and  xtype = 'P')
DROP PROCEDURE spSRC_LDTypeMeasurement_Add
GO

--*****************************************************************************
/* ##BD_BEGIN
Used to add a measurement association with source types.

<P><I>For more information, follow the Source link at the top of the page.</I></P>
##BD_END */
--
-- $Id: //rdsw/dev/cayman/SW_ION_E/database/Components/LogicalDevice/StoredProcedures/02.spSRC_LDTypeMeasurement_Add.sql#2 $
-- 
--*****************************************************************************
CREATE PROCEDURE [dbo].[spSRC_LDTypeMeasurement_Add]
	-- Add the parameters for the stored procedure here
	@MeasurementName nvarchar(250),
	@SourceTypeName nvarchar(250)
AS
IF (@MeasurementName IS NOT NULL AND @SourceTypeName IS NOT NULL)
	BEGIN	-- if ID is not null, raise an error if it exists
		DECLARE @MeasurementID smallint
		DECLARE @SourceTypeID smallint
		SET @MeasurementID = -1
		SET @SourceTypeID = -1
		SELECT @MeasurementID = MM_MeasurementMap.ID from MM_MeasurementMap where MM_MeasurementMap.StandardName = @MeasurementName
		SELECT @SourceTypeID = SRC_SourceType.SourceTypeID from SRC_SourceType where SRC_SourceType.Name = @SourceTypeName
		IF(@MeasurementID = -1)
		BEGIN
		
			RAISERROR('Measurement ''%s'' not Found', 16, 1,@MeasurementName)
			RETURN
		END
		
		IF(@SourceTypeID = -1)
		BEGIN
			RAISERROR('Type ''%s'' not Found', 16, 1, @SourceTypeName)
			RETURN
		END
	
		IF EXISTS 
		(
			SELECT
				SRC_LDTypeMeasurement.MeasurementID
			FROM 
				dbo.SRC_LDTypeMeasurement 
			WHERE 
				MeasurementID = @MeasurementID AND SourceTypeID = @SourceTypeID
		)
		BEGIN
			RAISERROR('Cannot add existing Mapping ', 16, 1)
		END
		ELSE
			BEGIN
			INSERT INTO Src_LDTypeMeasurement 
			Values (@MeasurementID,@SourceTypeId)
			END
		
	END


GO

if exists (select * from sysobjects where name='spSRC_LDType_Delete' and  xtype = 'P')
DROP PROCEDURE spSRC_LDType_Delete
GO

--*****************************************************************************
/* ##BD_BEGIN
Used to delete a source type from database. Deletes all the measurement associations
from the SRC_LDTypeMeasurement table as well.

<P><I>For more information, follow the Source link at the top of the page.</I></P>
##BD_END */
--
-- $Id: //rdsw/dev/cayman/SW_ION_E/database/Components/LogicalDevice/StoredProcedures/02.spSRC_LDType_Delete.sql#2 $
-- 
--*****************************************************************************
CREATE PROCEDURE [dbo].[spSRC_LDType_Delete]
	-- Add the parameters for the stored procedure here	
	@SourceTypeName nvarchar(250)
AS
--DECLARE @retval INT
BEGIN
	DECLARE @SourceTypeID smallint	
	SELECT @SourceTypeID = SRC_SourceType.SourceTypeID from SRC_SourceType where SRC_SourceType.Name = @SourceTypeName
	
	--If there are sources associated with the source type, raise error
	IF EXISTS
	( SELECT LogicalDevice.ID from LogicalDevice JOIN SRC_SourceType 
	  ON LogicalDevice.LogicalDeviceTypeID = SRC_SourceType.SourceTypeID 
	  AND SRC_SourceType.Name = @SourceTypeName)
	BEGIN
	RAISERROR('Cannot delete source type when devices are associated', 16, 1)
	END
	ELSE
	BEGIN
	-- Delete the measurements associations for the type
	DELETE from SRC_LDTypeMeasurement where SourceTypeID = @SourceTypeID
	
	--Delete from the source type table now.
	exec spSRC_SourceType_Delete @SourceTypeID
	END
END
--SET @retval = @@ERROR
--RETURN @retval

GO


if exists (select * from sysobjects where name='spSRC_LDType_RetrieveSourcesFromMeasurement' and  xtype = 'P')
DROP PROCEDURE spSRC_LDType_RetrieveSourcesFromMeasurement
GO

--*****************************************************************************
/* ##BD_BEGIN
Used to retrieve all measurements for a given source type.

<P><I>For more information, follow the Source link at the top of the page.</I></P>
##BD_END */
--
-- $Id: //rdsw/dev/cayman/SW_ION_E/database/Components/LogicalDevice/StoredProcedures/02.spSRC_LDTypeMeasurement_RetrieveByType.sql#2 $
-- 
--*****************************************************************************

CREATE PROCEDURE [dbo].[spSRC_LDType_RetrieveSourcesFromMeasurement]
(
	@LogicalTypeName	nvarchar(250),
	@MeasurementName	nvarchar(250)=NULL
)
AS	
BEGIN
	DECLARE @LogicalTypeID smallint, @MeasurementID smallint
	SET @LogicalTypeID = -1
	SET @MeasurementID = -1
	select @LogicalTypeID = SourceTypeID from SRC_SourceType where Name = @LogicalTypeName
	select @MeasurementID = ID from MM_MeasurementMap where StandardName = @MeasurementName
	IF(@LogicalTypeID <> -1 AND @MeasurementID <> -1)
	BEGIN
		SELECT SRC_Source.Name from SRC_Source 
		left join IAS_MeasurementAddress on IAS_MeasurementAddress.SourceID = SRC_Source.SourceID 
		left join LogicalDevice on LogicalDevice.Name = SRC_Source.Name
		WHERE IAS_MeasurementAddress.MeasurementID =  @MeasurementID
		AND LogicalDevice.LogicalDeviceTypeID = @LogicalTypeID
		RETURN
	END
	IF(@LogicalTypeID <> -1 AND @MeasurementID = -1)
	BEGIN
		SELECT LogicalDevice.Name from LogicalDevice where LogicalDeviceTypeID = @LogicalTypeID
		RETURN
	END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[udf_SplitBitListToTable]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
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 EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[udf_SplitDateTime2ListToTable]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
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 EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[udf_SplitFloatListToTable]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
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 EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[udf_SplitNVarCharListToTable]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
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 EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[udf_SplitRealListToTable]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
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 EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[udf_SplitSmallIntListToTable]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
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

--Adding constraints to [dbo].[LogicalDevice]'
ALTER TABLE [dbo].[LogicalDevice] ADD CONSTRAINT [IX_Logical_Device_Name] UNIQUE NONCLUSTERED  ([Name])
GO

--Adding constraints to [dbo].[DeviceType]'
ALTER TABLE [dbo].[DeviceType] ADD CONSTRAINT [DF__DeviceTyp__IONBu__0AD2A005] DEFAULT ((4096)) FOR [IONBufferSize_bytes]
GO

-- Adding foreign keys to [dbo].[LogicalDevice]'
ALTER TABLE [dbo].[LogicalDevice] WITH NOCHECK ADD
CONSTRAINT [FK_LogicalDevice_DeviceType] FOREIGN KEY ([LogicalDeviceTypeID]) REFERENCES [dbo].[SRC_SourceType] ([SourceTypeID])
GO

--Adding foreign keys to [dbo].[SRC_LDTypeMeasurement]'

ALTER TABLE [dbo].[SRC_LDTypeMeasurement] ADD
CONSTRAINT [FK_SRC_LDTypeMeasurement_MM_MeasurementMap] FOREIGN KEY ([MeasurementID]) REFERENCES [dbo].[MM_MeasurementMap] ([ID]),
CONSTRAINT [FK_SRC_LDTypeMeasurement_SRC_SourceType] FOREIGN KEY ([SourceTypeID]) REFERENCES [dbo].[SRC_SourceType] ([SourceTypeID])
GO

--*****************************************************************************
if exists (select * from sysobjects where id=object_id('[dbo].[GetCdmMeasurementMetadata]') and OBJECTPROPERTY(id, 'IsProcedure')=1)
DROP PROCEDURE [dbo].[GetCdmMeasurementMetadata]
GO
--*****************************************************************************
-- Retrieves information used to convert an ION Enterprise measurement to 
-- a CDM measurement. This data is used to determine how to create a new 
-- item in the Dashboard system and populate its metadata appropriately.
--
-- The @MeasurementNames parameter is an optional string that contains a list
-- of measurements for which we want to retrieve CDM-related information.
-- The strings are delimited by CHAR(6).
--*****************************************************************************
CREATE PROCEDURE [dbo].[GetCdmMeasurementMetadata]
(
    @MeasurementNames NVARCHAR(MAX)
)
AS

--set special characters
DECLARE @DelimiterCharacter NCHAR(1)
SET @DelimiterCharacter = CHAR(6)

--DEBUG
--DECLARE @QuantityIDs NVARCHAR(MAX)
--SET @QuantityIDs = '123459999'

--get quantity metadata for specified quantity id's (if null then select all)
--rotate triples table to return quantity metadata in denormalized form
--INSERT INTO   @TblQuantities
SELECT 
       mm.ID 
    ,  mm.[StandardName] AS [MeasurementName]
    , [grp].GroupName AS [Group]
    , pvt.[unit] AS [UnitCode]
    , pvt.[quantity] AS [QuantityCode]
    , pvt.[prefix] AS [PrefixCode]
    , pvt.[cumulative] AS [Cumulative]
    , pvt.[defaultrollupmethod] AS [Aggregation]
    , pvt.[standardmeasurement] AS [IsStandard]
    , pvt.[cdm/descriptivename] AS [DescriptiveName]
    , pvt.[cdm/engineeringname] AS [EngineeringName]
FROM
    dbo.MM_MeasurementMap mm
LEFT OUTER JOIN 
    (
        -- We'll use the existing measurement hierarchy information to generate
        -- the grouping value used in App Mods selectors.
        SELECT mn.MeasurementID, pn.DisplayName AS [GroupName]
        FROM  
            dbo.RPT_MeasurementHierarchyNodes mn 
        LEFT JOIN dbo.RPT_MeasurementHierarchyNodes pn ON 
            pn.TreeNodeID = mn.ParentID
        WHERE mn.HierarchyID IN 
        (
            SELECT HierarchyID FROM dbo.RPT_MeasurementHierarchy WHERE HierarchyName = 'Default'
        )
    ) AS [grp]
    ON [grp].MeasurementID = mm.ID
LEFT OUTER JOIN
    (SELECT [Subject], [SubjectNamespace], [Predicate], [Object] FROM dbo.vTS_Triples) AS [TS]
    PIVOT
    (   
        MAX([Object])
        FOR [Predicate] IN
        (
            [unit]
            , [quantity]
            , [prefix]
            , [cumulative]
            , [defaultrollupmethod]
            , [standardmeasurement]
            , [cdm/descriptivename]
            , [cdm/engineeringname]
        )
    ) AS [pvt]
    ON (mm.FullName = [SubjectNamespace]+[Subject])
WHERE
    @MeasurementNames IS NULL OR mm.[StandardName] IN 
        (SELECT [str] FROM udf_SplitStringListToTable(@MeasurementNames,@DelimiterCharacter))

GO

-- Create new role for IONE DSD to access store procs, views and/or tables it needs access to in ION_Network
IF not exists (SELECT * FROM sys.database_principals WHERE name = N'NOM_DSD_Reader' AND TYPE='R')
BEGIN
	CREATE ROLE [NOM_DSD_Reader]
	
	GRANT EXECUTE ON [dbo].[GetCdmMeasurementMetadata] TO [NOM_DSD_Reader]
	GRANT SELECT ON [dbo].[MM_MeasurementMap] TO [NOM_DSD_Reader]
	GRANT EXECUTE ON [dbo].[spDAL_GetSourceInfo] TO [NOM_DSD_Reader]
	GRANT EXECUTE ON [dbo].[Users_GetInfoByUsername] TO [NOM_DSD_Reader]
	GRANT EXECUTE ON [dbo].[Users_GetUsersAll] TO [NOM_DSD_Reader]
END
	
GO

