-- altering some existing objects

ALTER TABLE [dbo].[Task] DROP CONSTRAINT [CK_Task_Type]
GO

-- Refreshing [dbo].[vPMCPortConnectionTaskProperty]'

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

-- Refreshing [dbo].[vPMCTaskProperty]'

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

-- Refreshing [dbo].[vPMCSiteConnectionBase]'

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

-- Refreshing [dbo].[vPMCSiteConnection]'

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

-- Altering [dbo].[Scheduler]'

UPDATE [dbo].[Scheduler] SET [Name]=('IONNetworkRouterService') WHERE [Name] IS NULL
GO

ALTER TABLE [dbo].[Scheduler] ALTER COLUMN [Name] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
GO

-- Adding constraints to [dbo].[Task]'

ALTER TABLE [dbo].[Task] WITH NOCHECK ADD CONSTRAINT [CK_Task_Type] CHECK NOT FOR REPLICATION (([Type]='Scheduled' OR [Type]='Boundary'))
GO

--Adding constraints to [dbo].[Scheduler]'
if not exists (select * from sysobjects where name='DF__Scheduler__Name' and  xtype = 'D')
ALTER TABLE [dbo].[Scheduler] ADD CONSTRAINT [DF__Scheduler__Name] DEFAULT ('IONNetworkRouterService') FOR [Name]
GO



IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[udf_SplitIntListToTable]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
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 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  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[udf_SplitDateListsToTable]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
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  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[udf_SplitIntListsToTable]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
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 exists (select * from sysobjects where name='spSRC_SourceType_Add' and  xtype = 'P')
DROP PROCEDURE [dbo].[spSRC_SourceType_Add]
GO

--*****************************************************************************
/* ##BD_BEGIN 
	Adds a new SourceType to the database. If the type already exists, the ID
	for the existing entry is returned.

<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/Source/StoredProcedures/01.spSRC_SourceType_Add.sql#2 $
-- 
--*****************************************************************************
CREATE PROCEDURE dbo.spSRC_SourceType_Add
	@aName	NVARCHAR(200),	
	@ID 	SMALLINT 		OUTPUT,
	@aNotes  NVARCHAR(1000)=NULL
	
AS
DECLARE @retval INT
SET @ID = NULL
IF(@aName IS NOT NULL)
BEGIN
	SELECT 
		@ID = SourceTypeID
	FROM
		dbo.SRC_SourceType
	WHERE
		Name = @aName
		
	SET @retval = @@ERROR
	IF(@retval = 0)
	BEGIN
		IF(@ID IS NULL)
		BEGIN
			INSERT INTO 
				dbo.SRC_SourceType
				(Name,Notes)
			VALUES
				(@aName,@aNotes)
			SET @retval = @@ERROR
			IF(@retval = 0)
			BEGIN
				SET @ID = SCOPE_IDENTITY()	-- make sure we get the ID we just added ...
			END -- insert check
		END -- ID is null
	END -- select ID
END -- @aName is not null
RETURN @retval
GO

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

--*****************************************************************************
/* ##BD_BEGIN 
	Retrieves information about the specified SourceType.
	
<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/Source/StoredProcedures/01.spSRC_SourceType_Retrieve.sql#2 $
-- 
--*****************************************************************************
CREATE PROCEDURE dbo.spSRC_SourceType_Retrieve
	@theID SMALLINT
AS
DECLARE @retval INT
SELECT
	SourceTypeID,
	Name,
	Notes
FROM
	dbo.SRC_SourceType
WHERE
	SourceTypeID = @theID
SET @retval = @@ERROR
RETURN @retval
GO

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

--*****************************************************************************
/* ##BD_BEGIN 
	Retrieves information about all SourceTypes.
	
<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/Source/StoredProcedures/01.spSRC_SourceType_RetrieveAll.sql#2 $
-- 
--*****************************************************************************
CREATE PROCEDURE dbo.spSRC_SourceType_RetrieveAll
AS
DECLARE @retval INT
SELECT
	SourceTypeID,
	Name,
	Notes
FROM
	dbo.SRC_SourceType
SET @retval = @@ERROR
RETURN @retval
GO

if exists (select * from sysobjects where name='spSRC_SourceType_Update' and  xtype = 'P')
DROP PROCEDURE [dbo].[spSRC_SourceType_Update]
GO
--*****************************************************************************
/* ##BD_BEGIN 
	Updates the Name of the specified SourceType.
	
<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/Source/StoredProcedures/01.spSRC_SourceType_Update.sql#2 $
-- 
--*****************************************************************************

-- ****************************************************************************
CREATE PROCEDURE dbo.spSRC_SourceType_Update
	@theID SMALLINT,
	@aName NVARCHAR(200),
	@aNotes NVARCHAR(1000)=NULL
AS
DECLARE @retval INT
IF @aNotes IS NOT NULL
BEGIN
	UPDATE 
		dbo.SRC_SourceType
	SET
		Name = @aName,
		Notes =@aNotes			
WHERE
	SourceTypeID = @theID
END
ELSE
BEGIN
	UPDATE 
		dbo.SRC_SourceType
	SET
		Name = @aName	
WHERE
	SourceTypeID = @theID
END
SET @retval = @@ERROR
RETURN @retval
GO

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


--*****************************************************************************
-- PROCEDURE: UsersGroups_GetTabs
--
-- NOTES: This sp returns list of tabs assigned to user group
--
-- $Id: //rdsw/dev/cayman/SW_ION_E/database/Components/Reporting/StoredProcedures/05.UsersGroups_GetTabs.sql#2 $
--
--*****************************************************************************

CREATE PROCEDURE [dbo].[UsersGroups_GetTabs]
(
  @UserGroupID UserID,
  @TabTypeID tinyint=NULL,
  @TabID int = NULL
)
AS

--if no tab is specified
IF @TabID IS NULL
	SELECT UsersGroupsTabsRel.UserGroupID, UsersGroupsTabsRel.TabID, Tabs.MobileTabName, Tabs.ShowMobile, UsersGroupsTabsRel.TabOrder, Tabs.DestURL, UsersGroupsTabsRel.Display, UsersGroupsTabsRel.DisplayName, Tabs.TabTypeID
		FROM UsersGroupsTabsRel 
		INNER JOIN Tabs ON UsersGroupsTabsRel.TabID = Tabs.TabID
		WHERE UsersGroupsTabsRel.UserGroupID=@UserGroupID
		AND UsersGroupsTabsRel.TabTypeID=@TabTypeID
		ORDER BY UsersGroupsTabsRel.TabOrder
-- else, return only specified tab
ELSE
	SELECT UsersGroupsTabsRel.UserGroupID, UsersGroupsTabsRel.TabID, Tabs.MobileTabName, Tabs.ShowMobile, UsersGroupsTabsRel.TabOrder, Tabs.DestURL, UsersGroupsTabsRel.Display, UsersGroupsTabsRel.DisplayName, Tabs.TabTypeID
		FROM UsersGroupsTabsRel 
		INNER JOIN Tabs ON UsersGroupsTabsRel.TabID = Tabs.TabID
		WHERE UsersGroupsTabsRel.UserGroupID=@UserGroupID
		AND UsersGroupsTabsRel.TabID = @TabID
		ORDER BY UsersGroupsTabsRel.TabOrder

GO


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

--*****************************************************************************
-- PROCEDURE: UsersGroups_GetTabsUserPrefs
--
-- NOTES: This sp returns list of tabs information specific to the user groups
--
-- $Id: //rdsw/dev/cayman/SW_ION_E/database/Components/Reporting/StoredProcedures/05.UsersGroups_GetTabsUserPrefs.sql#2 $
--
--*****************************************************************************

CREATE PROCEDURE [dbo].[UsersGroups_GetTabsUserPrefs]
(
  @UserGroupID UserID,
  @TabTypeID tinyint = NULL,
  @TabID int = NULL
)
AS

--if no tab is specified
IF @TabID IS NULL
	SELECT *
		FROM UsersGroupsTabsRel 
		WHERE UserGroupID=@UserGroupID
		AND TabTypeID=@TabTypeID
		ORDER BY TabOrder
-- else, return only specified tab
ELSE
	SELECT *
		FROM UsersGroupsTabsRel 
		WHERE UserGroupID=@UserGroupID
		AND TabID = @TabID
		ORDER BY TabOrder


GO

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


CREATE PROCEDURE spIAS_ChangeOrAddSourceMeasurementReference
	@SourceNamespace NVARCHAR(250),
	@OldSource NVARCHAR(200)=NULL,
	@NewSource NVARCHAR(200),
	@OldMeasurement NVARCHAR(1000)=NULL,
	@NewMeasurement NVARCHAR(1000),
	@Scheme NVARCHAR(10),
	@Segment1 NVARCHAR(400),
	@Segment2 NVARCHAR(400),
	@Segment3 NVARCHAR(400),
	@IsManual BIT,
	@IsDefault BIT,
	@UserName NVARCHAR(250),
	@TimeResolvedUTC DATETIME=NULL
AS

SET NOCOUNT ON

DECLARE @OldMeasurementID SMALLINT
DECLARE @NewMeasurementID SMALLINT
DECLARE @OldSourceID INT
DECLARE @NewSourceID INT
DECLARE @ServicesID TINYINT
DECLARE @UserID SMALLINT
SET @OldSourceID=NULL
SET @OldMeasurementID=NULL
SET @ServicesID=NULL
SET @UserID=NULL

IF @OldSource Is NOT NULL
BEGIN
  SELECT @OldSourceID=s.SourceID FROM SRC_Namespace AS n JOIN SRC_Source AS s ON n.Name=@SourceNamespace AND n.NamespaceID=s.NamespaceID AND s.Name=@OldSource
  IF @OldSourceID IS NULL
  BEGIN
   RAISERROR  ('Unknown Original Source ''%s@%s''', 16, 1, @OldSource, @SourceNamespace)
   RETURN -1
  END
END
ELSE
 BEGIN
  IF @OldMeasurement IS NOT NULL
  BEGIN
   RAISERROR ('If Original Source or Measurement is specified, the other must be as well ''%s@%s''',16,2, @OldSource, @OldMeasurement)
   RETURN -1
  END
 END
SELECT @NewSourceID=s.SourceID FROM SRC_Namespace AS n JOIN SRC_Source AS s ON n.Name=@SourceNamespace AND n.NamespaceID=s.NamespaceID AND s.Name=@NewSource

IF @NewSourceID IS NULL
BEGIN
  RAISERROR  ('Unknown New Source ''%s@%s''', 16, 3, @NewSource, @SourceNamespace)
  RETURN -1
END
IF @OldMeasurement IS NOT NULL
BEGIN
  EXEC spMM_GetIDByName_StandardFirst @OldMeasurement, @OldMeasurementID OUTPUT
  IF @OldMeasurementID=0 OR @OldMeasurementID IS NULL
  BEGIN
    RAISERROR  ('Unknown Original Measurement ''%s''', 16, 4, @OldMeasurement)
    RETURN -1
  END
END

EXEC spMM_GetIDByName_StandardFirst @NewMeasurement, @NewMeasurementID OUTPUT
IF @NewMeasurementID=0 OR @NewMeasurementID IS NULL
BEGIN
  RAISERROR  ('Unknown New Measurement ''%s''', 16, 5, @NewMeasurement)
  RETURN -1
END

SELECT @ServicesID=ID FROM IAS_Services WHERE Scheme=@Scheme
IF @ServicesID IS NULL
BEGIN
  RAISERROR  ('Unknown Scheme ''%s''', 16, 6, @Scheme)
  RETURN -1
END

EXEC spIAS_GetUserID @UserName, @UserID OUTPUT

-- Set TimeResolvedUTC only for manual mappings (see SCR 22808 or SCR 23704 for details).
IF @IsManual=1
	SET @TimeResolvedUTC=GETUTCDATE()

--Unique constraint should raise error if source and measurement id's are not unique
IF @OldSource Is NULL AND @OldMeasurement IS NULL
  INSERT INTO IAS_MeasurementAddress (SourceID,MeasurementID,Segment1,Segment2,Segment3,ServicesID,IsManual,IsDefault,UserID,TimeResolvedUTC) 
	VALUES(@NewSourceID,@NewMeasurementID,@Segment1,@Segment2,@Segment3,@ServicesID,@IsManual,@IsDefault,@UserID,@TimeResolvedUTC)
ELSE
BEGIN
 UPDATE IAS_MeasurementAddress SET Segment1=@Segment1, Segment2=@Segment2, Segment3=@Segment3, ServicesID=@ServicesID, IsManual=@IsManual, IsDefault=@IsDefault, UserID=@UserID, TimeResolvedUTC=@TimeResolvedUTC, SourceID=@NewSourceID, MeasurementID=@NewMeasurementID
	WHERE SourceID=@OldSourceID AND MeasurementID=@OldMeasurementID
 IF @@ROWCOUNT = 0
  BEGIN
   RAISERROR ('Entry not found based on Source and Measurement provided ''%s@%s''', 16, 7, @OldMeasurement, @OldSource)
  RETURN -1
 END
END

RETURN 0


GO

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


CREATE PROCEDURE spIAS_ChangeSourceMeasurementReference
	@SourceNamespace NVARCHAR(250),
	@OldSource NVARCHAR(200),
	@NewSource NVARCHAR(200),
	@OldMeasurement NVARCHAR(1000),
	@NewMeasurement NVARCHAR(1000),
	@Scheme NVARCHAR(10),
	@UserName NVARCHAR(250)
AS

SET NOCOUNT ON

DECLARE @OldMeasurementID SMALLINT
DECLARE @NewMeasurementID SMALLINT
DECLARE @OldSourceID INT
DECLARE @NewSourceID INT
DECLARE @ServicesID TINYINT
DECLARE @UserID SMALLINT
SET @OldSourceID=NULL
SET @OldMeasurementID=NULL
SET @ServicesID=NULL
SET @UserID=NULL

SELECT @OldSourceID=s.SourceID FROM SRC_Namespace AS n JOIN SRC_Source AS s ON n.Name=@SourceNamespace AND n.NamespaceID=s.NamespaceID AND s.Name=@OldSource
IF @OldSourceID IS NULL
BEGIN
  RAISERROR  ('Unknown Original Source ''%s@%s''', 16, 1, @OldSource, @SourceNamespace)
  RETURN -1
END

SELECT @NewSourceID=s.SourceID FROM SRC_Namespace AS n JOIN SRC_Source AS s ON n.Name=@SourceNamespace AND n.NamespaceID=s.NamespaceID AND s.Name=@NewSource
IF @NewSourceID IS NULL
BEGIN
  RAISERROR  ('Unknown New Source ''%s@%s''', 16, 3, @NewSource, @SourceNamespace)
  RETURN -1
END

EXEC spMM_GetIDByName_StandardFirst @OldMeasurement, @OldMeasurementID OUTPUT
IF @OldMeasurementID=0 OR @OldMeasurementID IS NULL
BEGIN
  RAISERROR  ('Unknown Original Measurement ''%s''', 16, 4, @OldMeasurement)
  RETURN -1
END

EXEC spMM_GetIDByName_StandardFirst @NewMeasurement, @NewMeasurementID OUTPUT
IF @NewMeasurementID=0 OR @NewMeasurementID IS NULL
BEGIN
  RAISERROR  ('Unknown New Measurement ''%s''', 16, 5, @NewMeasurement)
  RETURN -1
END

SELECT @ServicesID=ID FROM IAS_Services WHERE Scheme=@Scheme
IF @ServicesID IS NULL
BEGIN
  RAISERROR  ('Unknown Scheme ''%s''', 16, 6, @Scheme)
  RETURN -1
END

EXEC spIAS_GetUserID @UserName, @UserID OUTPUT

--Unique constraint should raise error if source and measurement id's are not unique
 UPDATE IAS_MeasurementAddress SET TimeResolvedUTC=GETUTCDATE(), SourceID=@NewSourceID, MeasurementID=@NewMeasurementID, UserID=@UserID
	WHERE SourceID=@OldSourceID AND MeasurementID=@OldMeasurementID AND ServicesID=@ServicesID

IF @@ROWCOUNT = 0 AND @@ERROR = 0
BEGIN
 RAISERROR ('Entry not found based on Source and Measurement provided ''%s@%s''', 16, 7, @OldMeasurement, @OldSource)
 RETURN -1
END
ELSE
BEGIN
IF @@ERROR <> 0
RAISERROR('Error number = %s',16,8, @@ERROR)
END
RETURN 0

GO

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

CREATE PROCEDURE spIAS_DeleteMeasurementAddress
	@SourceNamespace NVARCHAR(250),
	@Scheme NVARCHAR(10),
	@Source NVARCHAR(200),
	@Measurement NVARCHAR(1000)=NULL

AS

SET NOCOUNT ON

DECLARE @MeasurementID SMALLINT
DECLARE @SourceID INT
DECLARE @ServicesID TINYINT
SET @SourceID=NULL
SET @MeasurementID=NULL
SET @ServicesID=NULL

SELECT @SourceID=s.SourceID FROM SRC_Namespace AS n JOIN SRC_Source AS s ON n.Name=@SourceNamespace AND n.NamespaceID=s.NamespaceID AND s.Name=@Source
 IF @SourceID IS NULL
  BEGIN
   RAISERROR  ('unknown Source ''%s@%s''', 16, 1, @Source, @SourceNamespace)
   RETURN -1
 END

IF @Measurement IS NOT NULL
BEGIN
  EXEC spMM_GetIDByName @Measurement, @MeasurementID OUTPUT
  IF @MeasurementID=0 OR @MeasurementID IS NULL
  BEGIN
    RAISERROR  ('Unknown Measurement ''%s''', 16, 2, @Measurement)
    RETURN -1
  END
END

SELECT @ServicesID=ID FROM IAS_Services WHERE Scheme=@Scheme
IF @ServicesID IS NULL
BEGIN
  RAISERROR  ('Unknown Scheme ''%s''', 16, 3, @Scheme)
  RETURN -1
END

IF @Measurement Is NULL
BEGIN
DELETE FROM IAS_MeasurementAddress 
  WHERE SourceID = @SourceID AND ServicesID = @ServicesID
  IF @@ROWCOUNT = 0
  BEGIN
   RAISERROR ('No entries found based on Source provided ''%s''', 16, 4, @Source)
  RETURN -1
 END
END

ELSE
BEGIN
DELETE FROM IAS_MeasurementAddress 
  WHERE SourceID = @SourceID AND ServicesID = @ServicesID AND MeasurementID = @MeasurementID
  IF @@ROWCOUNT = 0
  BEGIN
   RAISERROR ('No entries found based on Source and Measurement provided ''%s@%s''', 16, 5, @Measurement, @Source)
  RETURN -1
 END
END

RETURN 0

GO

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

--*****************************************************************************
/* ##BD_BEGIN
Deletes a measurement address from the table. If the source, measurement, or scheme
cannot be found it exits without deleting anything and without raising an error. 

<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/01.spIAS_DeleteMeasurementAddressWithFlags.sql#2 $
-- 
--*****************************************************************************
CREATE PROCEDURE spIAS_DeleteMeasurementAddressWithFlags
(
	@SourceNamespace NVARCHAR(250),
	@Scheme NVARCHAR(10),
	@Source NVARCHAR(200),
	@Measurement NVARCHAR(1000),
	@IsManual BIT,
	@IsDefault BIT
)
AS
SET NOCOUNT ON
DECLARE @MeasurementID SMALLINT
DECLARE @SourceID INT
DECLARE @ServicesID TINYINT
DECLARE @NamespaceID SMALLINT	-- only used to call the sproc below
SET @SourceID=NULL
SET @MeasurementID=NULL
SET @ServicesID=NULL
SET @NamespaceID=NULL

EXEC spSRC_Source_Namespace_Find @Source, @SourceNamespace, @SourceID OUTPUT, @NamespaceID OUTPUT
IF (@SourceID IS NOT NULL)
BEGIN
  EXEC spMM_GetIDByName @Measurement, @MeasurementID OUTPUT
  IF (@MeasurementID<>0 AND @MeasurementID IS NOT NULL)
  BEGIN
    SELECT 
    	@ServicesID=ID 
    FROM 
    	IAS_Services 
    WHERE 
    	Scheme=@Scheme
    	
    IF (@ServicesID IS NOT NULL)
    BEGIN
      DELETE FROM 
      	IAS_MeasurementAddress 
      WHERE 
      	SourceID = @SourceID AND 
      	ServicesID = @ServicesID AND 
      	MeasurementID = @MeasurementID AND
      	IsManual=@IsManual AND
      	IsDefault=@IsDefault
	END
  END
END
RETURN 0

GO

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


CREATE PROCEDURE spIAS_UpdateMeasurementAddress
	@SourceNamespace SourceNamespace,
	@Source SourceName,
	@Measurement NVARCHAR(1000),
	@Scheme NVARCHAR(10),
	@Segment1 NVARCHAR(400),
	@Segment2 NVARCHAR(400),
	@Segment3 NVARCHAR(400),
	@IsManual BIT,
	@IsDefault BIT,
	@UserName NVARCHAR(250),
	@Verbatim BIT=0,
	@TimeResolvedUTC DATETIME=NULL
AS

SET NOCOUNT ON

DECLARE @MeasurementID SMALLINT
DECLARE @SourceID SourceID
DECLARE @ServicesID TINYINT
DECLARE @UserID SMALLINT
SET @SourceID=NULL
SET @MeasurementID=NULL
SET @ServicesID=NULL
SET @UserID=NULL

SELECT @SourceID=s.SourceID FROM SRC_Namespace AS n JOIN SRC_Source AS s ON n.Name=@SourceNamespace 
	AND n.NamespaceID=s.NamespaceID AND s.Name=@Source

IF @SourceID IS NULL
BEGIN
  RAISERROR  ('Unknown Source ''%s@%s''', 16, 1, @Source, @SourceNamespace)
  RETURN -1
END

EXEC spMM_GetIDByName_StandardFirst @Measurement, @MeasurementID OUTPUT

IF @MeasurementID=0 OR @MeasurementID IS NULL
BEGIN
  RAISERROR  ('Unknown Measurement ''%s''', 16, 2, @Measurement)
  RETURN -1
END

SELECT @ServicesID=ID FROM IAS_Services WHERE Scheme=@Scheme

IF @ServicesID IS NULL
BEGIN
  RAISERROR  ('Unknown Scheme ''%s''', 16, 3, @Scheme)
  RETURN -1
END

EXEC spIAS_GetUserID @UserName, @UserID OUTPUT

--Commenting the code below as without time resolved column populated
--data logs will not be captured for manual extrinsic mappings
--when log inserter is already capturing them for intrinsic default mappings
--please see categorize mappings phase in Quantities.
--IF @TimeResolvedUTC IS NULL AND (@Verbatim=0 OR @IsManual=0)
	SET @TimeResolvedUTC=GETUTCDATE()

UPDATE IAS_MeasurementAddress SET Segment1=@Segment1,Segment2=@Segment2,Segment3=@Segment3, ServicesID=@ServicesID, 
	IsManual=@IsManual, IsDefault=@IsDefault, UserID=@UserID, TimeResolvedUTC=@TimeResolvedUTC
	WHERE SourceID=@SourceID AND MeasurementID=@MeasurementID AND (@Verbatim=1 OR @IsManual=1 OR (IsManual=0 AND ServicesID=@ServicesID))

IF @@ROWCOUNT=0
BEGIN
  IF @Verbatim=0 AND EXISTS (SELECT * FROM IAS_MeasurementAddress WHERE SourceID=@SourceID AND MeasurementID=@MeasurementID)
  BEGIN
    RAISERROR  ('Cannot update address of ''%s@%s@%s'' because is manual or scheme changed.', 16, 3, @Measurement, @Source, @SourceNamespace)
    RETURN -1
  END
	
  INSERT INTO IAS_MeasurementAddress (SourceID,MeasurementID,Segment1,Segment2,Segment3,ServicesID,IsManual,IsDefault,UserID,TimeResolvedUTC) 
	VALUES(@SourceID,@MeasurementID,@Segment1,@Segment2,@Segment3,@ServicesID,@IsManual,@IsDefault,@UserID,@TimeResolvedUTC)
END

RETURN 0
GO


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

--*****************************************************************************
/* ##BD_BEGIN
Updates the specified MeasurementMap entry.

<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/MM_MeasurementMap/StoredProcedures/10.spMM_UpdateMapEntry.sql#2 $
-- 
--*****************************************************************************
CREATE PROCEDURE dbo.spMM_UpdateMapEntry
(
	@ID 			SMALLINT,
	@fullname		NVARCHAR(400),
	@standardname	NVARCHAR(250)
)
AS
DECLARE @result INT
UPDATE 
	dbo.MM_MeasurementMap
SET 
	FullName		= @fullname, 
	StandardName	= @standardname
WHERE 
	ID = @ID
SET @result = @@ERROR
IF(@result <> 0)
	BEGIN
		-- just add a quick note to the error list to help with debugging ...
		RAISERROR(N'Problem updating ID [%d] with signature [%s] and standard name [%s]', 16, 1, @ID, @fullname, @standardname)
	END
ELSE
	BEGIN		
		-- Whenever measurement is updated, ensure that any mappings against this
		-- measurement is updated in IAS_MeasurementAddress table so that ION Service could refresh 
		-- it's cache. This is to handle case where measurement is renamed.
		UPDATE dbo.IAS_MeasurementAddress SET TimeUpdatedUTC = GETUTCDATE() where MeasurementID = @ID
	END
RETURN @result
GO



-- Refreshing [dbo].[vwSRC_Source]'

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

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

--*****************************************************************************
/* ##BD_BEGIN
	Updates the specified source.
	
<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/Source/StoredProcedures/01.spSRC_Source_Update.sql#2 $
-- 
--*****************************************************************************
CREATE PROCEDURE dbo.spSRC_Source_Update
(
	@aSourceID 			SourceID,
	@aName 				SourceName,
	@aNamespaceID 		SMALLINT,
	@aTimeZoneID 		SMALLINT,
	@aSourceCategoryID	SMALLINT,
	@aUtilityTypeID		SMALLINT,
	@aLoadTypeID 		SMALLINT,
	@aImportEnabled		BIT,
	@aHidden			BIT = 0,
	@aSourceTypeName	NVARCHAR(200)=NULL
)
AS
DECLARE @aSourceTypeId smallint
select @aSourceTypeId = SourceTypeID from SRC_SourceType where Name = @aSourceTypeName
UPDATE 
	dbo.SRC_Source
SET 
	Name 				= @aName, 
	NamespaceID 		= @aNamespaceID, 
	TimezoneID 			= @aTimeZoneID, 
	SourceCategoryID	= @aSourceCategoryID,
	UtilityTypeID 		= @aUtilityTypeID,
	LoadTypeID 			= @aLoadTypeID,
	ImportEnabled 		= @aImportEnabled,
	Hidden				= @aHidden,
	SourceTypeID		= @aSourceTypeId
WHERE 
	SourceID = @aSourceID

RETURN @@ERROR
GO

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


--*****************************************************************************
/* ##BD_BEGIN
Registers a dynamically loaded class. This stored procedure is intended for use
in the build process and in the field to add new entries to the registry.
For this reason, the component is specified as a string.

Note that implicitly creating a component via this procedure will result in
a blank description field for that component entry. It is recommended that 
you create an component entry first and provide a description.

<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/DCR_DynamicClassRegistry/StoredProcedures/15.spDCR_RegisterClass.sql#2 $
-- 
--*****************************************************************************
CREATE PROCEDURE dbo.spDCR_RegisterClass
(
	@aComponentName		NVARCHAR(250),
	@aComponentSubType	INT,
	@anEntryName		NVARCHAR(250),
	@anEntryDescription	NVARCHAR(500),
	@aClassName 		NVARCHAR(250),
	@anAssemblyName 	NVARCHAR(250),
	@anAssemblyFolder	NVARCHAR(1000)	= NULL,
	@anEnabled 			BIT				= 1,
	@aComponentData		NVARCHAR(1000)	= NULL
)
AS
-- try to get the component ID
DECLARE @componentID INT
EXEC dbo.spDCR_Component_GetComponentID @aComponentName, @componentID OUTPUT
IF(@componentID IS NULL)
BEGIN
	-- probably doesn't exist - try to create a new entry
	EXEC dbo.spDCR_Component_Add @aComponentName, NULL, @componentID OUTPUT
	IF(@componentID IS NULL)
	BEGIN
		RAISERROR( 'Could not find or create an entry for component ''%s''', 16, 1, @aComponentName )
		RETURN 1
	END
END
-- OK, now make the main registration entry
DECLARE @regID INT
DECLARE @retval INT
EXEC @retval = dbo.spDCR_DynamicClassRegistry_Add
	@componentID,
	@aComponentSubType,
	@anEntryName,
	@anEntryDescription,
	@aClassName,
	@anAssemblyName,
	@anAssemblyFolder,
	@anEnabled,
	@aComponentData,
	@regID OUTPUT			-- not used, only here to support the call
RETURN @retval
GO

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


--ReportColoursControl

DECLARE @RegistryID int
If not exists (Select Name from DCR_DynamicClassRegistry where Name = 'ReportColours')
EXEC dbo.spDCR_DynamicClassRegistry_Add 3, 0, 'ReportColours', 'Report Colours', 'PowerMeasurement.Reporting.ParameterLogic.EEM.ReportColoursParameterLogic','Reporting.ParameterLogic.EEM.dll', NULL, true,'ReportColours', @RegistryID
GO

--SCR#23444 I18N: PQ Aggregator: Does not process events for CM4 device
UPDATE dbo.EVT_EventString set String = 'Volt%' where String = 'sag/swell*' OR String = 'sag/swell%'
GO
--SCR#24318 - EventLog - The 'cause' string is not what is expected
UPDATE dbo.EVT_EventString set String = 'Sag/Swell%' where String = 'Sag/Swell 1'
UPDATE dbo.EVT_EventString set String = 'Dip/Swell%' where String = 'Dip/Swell 1'
GO

--Update to typo in Event Watcher Priority Condition table update


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

/******************************************************************************
##BD_BEGIN
Updates an existing EventLogCondition. Deletes any existing EVT_PriorityCondition entries
associated with the updated EventLogCondition, and adds new entries to that table if any are specified.
##BD_END 

$Id: //rdsw/dev/cayman/SW_ION_E/database/Components/EventWatcher/StoredProcedures/01.spEVT_EventLogCondition_Update.sql#2 $
******************************************************************************/
CREATE PROCEDURE [dbo].[spEVT_EventLogCondition_Update]
	
	(
	@EventLogConditionID int,
	@PriorityLowerBound int,
	@PriorityUpperBound int,
	@UsePriorityComparison bit = 0,
	@Cause nvarchar(3998),
	@UseCauseComparison bit = 0,
	@CauseValue nvarchar(3998),
	@UseCauseValueComparison bit = 0,
	@Effect nvarchar(3998),
	@UseEffectComparison bit = 0,
	@EffectValue nvarchar(3998),
	@UseEffectValueComparison bit = 0,
	@EventWatcherTemplateID int
	)
	
AS
	DECLARE @OldPriorityConditionID int;
	DECLARE @PriorityConditionID int;
	DECLARE @CauseID int;
	DECLARE @CauseValueID int;
	DECLARE @EffectID int;
	DECLARE @EffectValueID int;

	----------------------------------------------------------------------
	-- retrieve any priorityConditionID that may be associated
	-- with the EventLogCondition to be updated. These will be
	-- deleted once the EventLogCondition has been updated.
	----------------------------------------------------------------------
	SET @OldPriorityConditionID = (SELECT PriorityConditionID 
									FROM dbo.EVT_EventLogCondition
									WHERE EventLogConditionID = @EventLogConditionID)


	----------------------------------------------------------------------
	-- If either of @PriorityLowerBound or @PriorityUpperBound has a value,
	-- insert and entry into the priority comparison table. Otherwise, 
	-- @PriorityComparisonID = null (indicating no priority comparison.)
	----------------------------------------------------------------------
	IF (@PriorityLowerBound IS NOT NULL OR @PriorityUpperBound IS NOT NULL)
		BEGIN
			INSERT INTO dbo.EVT_PriorityCondition(LowerBound, UpperBound)
			VALUES (@PriorityLowerBound, @PriorityUpperBound)

			SET @PriorityConditionID = SCOPE_IDENTITY()
		END

	
	----------------------------------------------------------------------
	-- Determine if the cause, causeValue, effect, and effectValue strings
    -- exist already in the EventString table; if so, retrieve their corresponding
	-- IDs; if not, add the string to the EventString table.
	----------------------------------------------------------------------
	IF (@Cause IS NOT NULL)
		EXEC	@CauseID = [dbo].[spEVT_GetEventStringID] @Cause

	IF (@CauseValue IS NOT NULL)
		EXEC	@CauseValueID = [dbo].[spEVT_GetEventStringID] @CauseValue

	IF (@Effect IS NOT NULL)
		EXEC	@EffectID = [dbo].[spEVT_GetEventStringID] @Effect

	IF (@EffectValue IS NOT NULL)
		EXEC	@EffectValueID = [dbo].[spEVT_GetEventStringID] @EffectValue


	--Update the EventLogCondition
	UPDATE dbo.EVT_EventLogCondition
	SET PriorityConditionID = @PriorityConditionID,
		UsePriorityComparison = @UsePriorityComparison,
		CauseID = @CauseID, 
		UseCauseComparison = @UseCauseComparison,
		CauseValueID = @CauseValueID,
		UseCauseValueComparison = @UseCauseValueComparison,
		EffectID = @EffectID, 
		UseEffectComparison = @UseEffectComparison,
		EffectValueID = @EffectValueID,
		UseEffectValueComparison = @UseEffectValueComparison,
		EventWatcherTemplateID = @EventWatcherTemplateID
	WHERE EventLogConditionID = @EventLogConditionID

	-- Any old priorityCondition should no longer be associated
	-- with the updated EventLogCondition at this point; delete it, if it exists.
	IF (@OldPriorityConditionID IS NOT NULL)
	
		DELETE FROM dbo.EVT_PriorityCondition
			WHERE PriorityConditionID = @OldPriorityConditionID

	RETURN @@ERROR


GO

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

/******************************************************************************
##BD_BEGIN
PROCEDURE: spDAL_GetMeasurementHierarchyNodes
Retrieves a table representing a measurement hierarchy from the network database.

We wanted to include some unit information in the measurement selector, and 
as a performance optimization I moved the triple store lookup into this
stored procedure.

$Id: //rdsw/dev/cayman/SW_ION_E/database/Components/DAL_Network/StoredProcedures/01.spDAL_GetMeasurementHierarchyNodes.sql#3 $
******************************************************************************/
CREATE PROCEDURE dbo.spDAL_GetMeasurementHierarchyNodes
(
	@HierarchyId SMALLINT
)
AS

-- declare 'magic' parent node names
-- these MUST match the groups defined in the default data scripts for 
-- the measurement hierarchy
DECLARE @CustomReadingsGroupName AS NVARCHAR(200)
DECLARE @MiscellaneousReadingsGroupName AS NVARCHAR(200)
SELECT 
	@CustomReadingsGroupName        = 'CDM.MdValue.CUSTOM' ,
	@MiscellaneousReadingsGroupName = 'CDM.MdValue.MISCELLANEOUS'
		
--declare results table
DECLARE @NodeResults TABLE
(
	NodeID INT
	, NodeDisplayName NVARCHAR(200)
	, ParentID INT
	, MeasurementID INT
	, MeasurementPrefix NVARCHAR(100)
	, MeasurementUnit NVARCHAR(100)
	, MeasurementEngineeringName NVARCHAR(200)
	, MeasurementDescriptiveName NVARCHAR(200)
)

-- Determine whether 'magic' parent nodes exist for the specified hierarchy
DECLARE @CustomReadingsNodeID AS INT
SET @CustomReadingsNodeID = 
(
	SELECT 
		TreeNodeID
	FROM 
		dbo.RPT_MeasurementHierarchyNodes
	WHERE
		DisplayName = @CustomReadingsGroupName
		AND HierarchyID = @HierarchyId
)

DECLARE @MiscellaneousNodeID AS INT
SET @MiscellaneousNodeID = 
(
	SELECT 
		TreeNodeID
	FROM 
		dbo.RPT_MeasurementHierarchyNodes
	WHERE
		DisplayName = @MiscellaneousReadingsGroupName
		AND HierarchyID = @HierarchyId
)

--insert all measurements belonging to the specified 
--measurement hierarchy into results table
INSERT INTO 
	@NodeResults
SELECT 
	 TreeNodeID AS [NodeID]
	,DisplayName AS [NodeDisplayName]
	,ParentID AS [ParentID]
	,MeasurementID AS [MeasurementID]
	,prefix.Object
	,unit.Object
	,NULL
	,NULL
FROM
	vwRPT_MeasurementHierarchyNodes_Admin hn
LEFT OUTER JOIN MM_MeasurementMap mm 
	ON mm.ID = hn.MeasurementID
LEFT OUTER JOIN vTS_Triples unit 
	ON unit.SubjectNamespace + unit.Subject = mm.FullName 
	AND unit.Predicate = 'unit'
LEFT OUTER JOIN vTS_Triples prefix 
	ON prefix.SubjectNamespace + prefix.Subject = mm.FullName 
	AND prefix.Predicate = 'prefix'
WHERE
	HierarchyID = @HierarchyId

--insert all custom measurements belonging to specified 
--measurement hierarchy into results table
IF(@CustomReadingsNodeID IS NOT NULL)
BEGIN
	INSERT INTO
		@NodeResults
	SELECT 
		(0- map.ID) AS [NodeID]
		,map.StandardName AS [NodeDisplayName]
		,@CustomReadingsNodeID AS [ParentID]
		,map.ID AS [MeasurementID]
		,prefix.Object
		,unit.Object
		,engineeringName.Object
		,descriptiveName.Object
	FROM
		MM_MeasurementMap map
	LEFT OUTER JOIN vTS_Triples unit 
		ON unit.SubjectNamespace + unit.Subject = map.FullName 
		AND unit.Predicate = 'unit'
	LEFT OUTER JOIN vTS_Triples prefix 
		ON prefix.SubjectNamespace + prefix.Subject = map.FullName 
		AND prefix.Predicate = 'prefix'
	LEFT OUTER JOIN vTS_Triples engineeringName
		ON engineeringName.SubjectNamespace + engineeringName.Subject = map.FullName 
		AND engineeringName.Predicate = 'CDM/engineeringname'
	LEFT OUTER JOIN vTS_Triples descriptiveName
		ON descriptiveName.SubjectNamespace + descriptiveName.Subject = map.FullName 
		AND descriptiveName.Predicate = 'CDM/descriptivename'
	WHERE
		(SELECT COUNT(*) FROM vwRPT_MeasurementHierarchyNodes_Admin node WHERE node.MeasurementID = map.ID) = 0
		AND map.ID > 10000
END

--insert all miscellaneous measurements belonging to 
--specified measurement hierarchy into results table
IF(@MiscellaneousNodeID IS NOT NULL)
BEGIN
	INSERT INTO
		@NodeResults
	SELECT 
		(0 - map.ID) AS [NodeID]
		,map.StandardName AS [NodeDisplayName]
		,@MiscellaneousNodeID AS [ParentID]
		,map.ID AS [MeasurementID]
		,prefix.Object
		,unit.Object
		,NULL
		,NULL
	FROM
		MM_MeasurementMap map
	LEFT OUTER JOIN vTS_Triples unit 
		ON unit.SubjectNamespace + unit.Subject = map.FullName 
		AND unit.Predicate = 'unit'
	LEFT OUTER JOIN vTS_Triples prefix 
		ON prefix.SubjectNamespace + prefix.Subject = map.FullName 
		AND prefix.Predicate = 'prefix'
	WHERE
		(SELECT COUNT(*) FROM vwRPT_MeasurementHierarchyNodes_Admin node WHERE node.MeasurementID = map.ID) = 0
		AND map.ID < 10001
END

--return all results
SELECT
	NodeID
	, NodeDisplayName
	, ParentID
	, MeasurementID
	, MeasurementPrefix
	, MeasurementUnit
	, MeasurementEngineeringName
	, MeasurementDescriptiveName
FROM
	@NodeResults 
ORDER BY
	NodeID ASC

GO

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

---------------------------------------------------------------------------------
--PROCEDURE: spDAL_GetMeasurements
--NOTES: retrieves specified measurements from Measurement Store
--Primary table is the measurement map table - triple store is may
--not contain a metadata entry for all measurements (e.g. custom)
---------------------------------------------------------------------------------

CREATE PROCEDURE dbo.spDAL_GetMeasurements
(
	@MeasurementIDs NVARCHAR(MAX)
)
AS

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

--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] AS [MeasurementID]
	, mm.[StandardName] AS [MeasurementName]	
	, pvt.[unit] AS [MeasurementUnitName]
	, pvt.[phase] AS [MeasurementPhase]
	, pvt.[quantity] AS [MeasurementQuantity]
	, pvt.[type] AS [MeasurementType]
	, pvt.[format] AS [MeasurementFormat]
	, pvt.[prefix] AS [MeasurementPrefix]
	, pvt.[direction] AS [MeasurementDirection]
	, pvt.[cumulative] AS [MeasurementCumulative]	
	, pvt.[CDM/descriptivename] AS [MeasurementDescriptiveName]
	, pvt.[CDM/engineeringname] AS [MeasurementEngineeringName]
FROM
	dbo.MM_MeasurementMap mm
LEFT OUTER JOIN
	(SELECT [Subject], [SubjectNamespace], [Predicate], [Object] FROM dbo.vTS_Triples) AS [TS]
	PIVOT
	(	
		MAX([Object])
		FOR [Predicate] IN
		(
			[unit]
			, [phase]
			, [quantity]
			, [type]
			, [format]
			, [prefix]
			, [direction] 
			, [cumulative]
			, [CDM/descriptivename]
			, [CDM/engineeringname]
		)
	) AS [pvt]
	ON (mm.FullName = [SubjectNamespace]+[Subject])
WHERE
	@MeasurementIDs IS NULL OR mm.[ID] IN (SELECT [number] FROM udf_SplitIntListToTable(@MeasurementIDs,@DelimiterCharacter))
ORDER BY 
	mm.[ID] ASC
	
GO

if exists (select * from sysobjects where name='spIAS_DeletePDMappingsForLogDevices' and  xtype = 'P')
DROP PROCEDURE [dbo].[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_GetAssocLogDevicesForPhyDev' and  xtype = 'P')
DROP PROCEDURE [dbo].[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='spIAS_UpdatePDNameMappingsForLogDevices' and  xtype = 'P')
DROP PROCEDURE [dbo].[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='spMM_AddMapEntry' and  xtype = 'P')
DROP PROCEDURE [dbo].[spMM_AddMapEntry]
GO

--*****************************************************************************
/* ##BD_BEGIN
Creates a new MeasurementMap entry, and returns the newly created ID in 
the @ID output parameter. 

We are effectively implementing our own autoincrement ID here - this is to 
ensure that we can maintain ID compatibility with spMM_AddMapEntry_Compatible
and still secure the database (otherwise SET IDENTITY_INSERT would have to be
used). It is anticipated that there will be few additions, and that a small 
performance penaltly will be acceptable.

<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/MM_MeasurementMap/StoredProcedures/10.spMM_AddMapEntry.sql#2 $
-- 
--*****************************************************************************
CREATE PROCEDURE dbo.spMM_AddMapEntry
(
	@fullname		NVARCHAR(400),
	@standardname	NVARCHAR(250),
	@ID 			SMALLINT			OUTPUT
)
AS
DECLARE @result INT

-- initialize
SET @ID = NULL

-- check the new names ...
EXEC @result = dbo.spMM_CheckNewNames @fullname, @standardname
IF(@result = 0)
BEGIN
	-- first, determine the ID value to insert
	DECLARE @maxID SMALLINT
	SET @maxID = NULL
	SELECT 
		@maxID = MAX(ID) 
	FROM 
		dbo.MM_MeasurementMap
	SET @result = @@ERROR
	IF(@result = 0)
	BEGIN
		-- check our max id result 
		IF(@maxID IS NULL)					-- table is empty so set initial value
		BEGIN
			SET @maxID = 0
		END
		ELSE IF (@maxID < 10000)		-- "custom" measurements start at 10000
		BEGIN
			SET @maxID = 10000
		END
		-- insert the new record
		SET @ID = @maxID + 1
		INSERT INTO 
			[dbo].MM_MeasurementMap
			(
				ID, 
				FullName, 
				StandardName
			)
		VALUES 
			(
				@ID, 
				@fullname, 
				@standardname
			)
		SET @result = @@ERROR
	END
END
ELSE
BEGIN
	RAISERROR(N'Duplicate standard name found for ''%s'': %d', 16, 1, @standardname, @result)
END
-- make sure our output is zero unless we worked
IF (@result <> 0)
BEGIN
	SET @ID = 0
END
RETURN @result
GO

if exists (select * from sysobjects where name='spNOM_RetrieveSitesForDeviceClassification' and  xtype = 'P')
	DROP PROCEDURE [dbo].[spNOM_RetrieveSitesForDeviceClassification]
GO
	--*****************************************************************************
	/* ##BD_BEGIN
	Returns a list of existing Sites that could be associated with a Device of the given
	classification (e.g. "Serial" or "OPC").  For example:
	* Ethernet Gateway, Direct and Modem Sites can be associated with Serial Devices.
	* OPC Sites are the only ones that can be associated with OPC Devices.
	* Ethernet Devices are not associated with Sites.

	NOTE:  An empty result set will be returned if the Device classification that was
	passed into this procedure is either:
	* NULL,
	* Not a recognized classification or
	* Not a classification that can be associated with Sites (e.g. "Ethernet").

	<P><I>For more information, follow the Source link at the top of the page.</I></P>
	##BD_END 
	##PD aDeviceClassification ^^ String representing the kind of Device for which to find appropriate Sites.
	*/
	--
	-- $Id: //rdsw/dev/cayman/SW_ION_E/database/Components/NOM/StoredProcedures/03.spNOM_RetrieveSitesForDeviceClassification.sql#2 $
	-- 
	--*****************************************************************************
	CREATE PROCEDURE dbo.spNOM_RetrieveSitesForDeviceClassification
	(
		@aDeviceClassification NVARCHAR(100)
	)
	AS

	-- Set up a return value.
	DECLARE @aRetVal INT
	SET @aRetVal = 0

	-- It is easier to tell which Sites are compatible with a particular
	-- kind of Device by looking at the the Gate that the Site (i.e. Port)
	-- is connected to.

	-- Define some "constants" that describe the possible Gate types.
	DECLARE @aDirectGateEntityClassName NVARCHAR(100)
	SET @aDirectGateEntityClassName = N'DirectGate'
	DECLARE @anEtherGateEntityClassName NVARCHAR(100)
	SET @anEtherGateEntityClassName = N'EtherGate'
	DECLARE @aModemGateEntityClassName NVARCHAR(100)
	SET @aModemGateEntityClassName = N'ModemGate'
	DECLARE @anOPCGateEntityClassName NVARCHAR(100)
	SET @anOPCGateEntityClassName = N'URIGate'

	-- Create a table variable that contains the entity class names for Gates
	-- that are compatible with the Device classification.
	DECLARE @GateEntityClassNames TABLE
	(
		GateEntityClassName NVARCHAR(100) NOT NULL
	)

	DECLARE @aDeviceClassificationUppercase NVARCHAR(100)
	SET @aDeviceClassificationUppercase = UPPER( @aDeviceClassification )
	IF ( @aDeviceClassificationUppercase = N'OPC' )
	BEGIN
		-- OPC Devices can only be associated with OPC Sites.
		INSERT INTO @GateEntityClassNames ( GateEntityClassName ) VALUES ( @anOPCGateEntityClassName )
	END
	ELSE IF ( @aDeviceClassificationUppercase = N'SERIAL' )
	BEGIN
		-- Serial Devices can be associated with Direct, EtherGate and
		-- Modem Sites.
		INSERT INTO @GateEntityClassNames ( GateEntityClassName ) VALUES ( @aDirectGateEntityClassName )
		INSERT INTO @GateEntityClassNames ( GateEntityClassName ) VALUES ( @anEtherGateEntityClassName )
		INSERT INTO @GateEntityClassNames ( GateEntityClassName ) VALUES ( @aModemGateEntityClassName )
	END
	-- There aren't any other Device classifications that can be
	-- associated with Sites.

	-- Find the Sites that can be associated with this kind of Device.
	-- Tricky part:  We must exclude Ethernet Gateways that are associated
	-- directly with Ethernet Devices.  They have an entity class of 'IPPort',
	-- so the following query will exclude all Ports that have an entity class
	-- of 'IPPort'.
	DECLARE @anEthernetDevicePortEntityClassID INT
	EXEC dbo.spNOM_EntityClass_RetrieveIDByName
		@anEntityClassName = N'IPPort',
		@anEntityClassID = @anEthernetDevicePortEntityClassID OUTPUT

	SELECT
		p.[ID] AS SiteID,
		p.[Name] AS SiteName,
		CASE
			WHEN ec.[Name] = @aDirectGateEntityClassName THEN N'Direct'
			WHEN ec.[Name] = @anEtherGateEntityClassName THEN N'EtherGate'
			WHEN ec.[Name] = @aModemGateEntityClassName THEN N'Modem'
			WHEN ec.[Name] = @anOPCGateEntityClassName THEN N'OPC'
			ELSE NULL 
		END AS SiteClassification,
		p.[Description] AS SiteDescription
	FROM
		dbo.Port p
	INNER JOIN
		dbo.Gate g
	ON
		g.[ID] = p.GateID
	INNER JOIN
		dbo.EntityClass ec
	ON
		ec.[ID] = g.EntityClassID
	WHERE
		ec.[Name] IN
		(
			SELECT
				GateEntityClassName
			FROM
				@GateEntityClassNames
		)
		AND NOT( p.EntityClassID = @anEthernetDevicePortEntityClassID )
	SET @aRetVal = @@ERROR

	RETURN @aRetVal
GO
