-- shell for NOM13 logical device support

/******************************************************************************************************************
-- Add new table for logical device
*******************************************************************************************************************/
if not exists (select * from sysobjects where name='LogicalDevice' and  xtype = 'U')
CREATE TABLE [dbo].[LogicalDevice] (
	[ID] [int] IDENTITY (50000, 1) NOT NULL ,
	[Name] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[LogicalDeviceTypeID] [smallint] NOT NULL ,
	[Description] [nvarchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Enabled] [bit] NOT NULL ,
	[LastUpdate] [timestamp] NOT NULL ,
) ON [PRIMARY]
GO

if not exists (select * from sysobjects where name='PK_LogicalDevice' and  xtype = 'PK')
ALTER TABLE [dbo].[LogicalDevice] ADD CONSTRAINT [PK_LogicalDevice] PRIMARY KEY  NONCLUSTERED ([ID]) ON [PRIMARY]
GO

if not exists (select * from sysobjects where name='DF_LogicalDevice_LogicalDeviceTypeID' and  xtype = 'D')
ALTER TABLE [dbo].[LogicalDevice] ADD CONSTRAINT [DF_LogicalDevice_LogicalDeviceTypeID] DEFAULT (12) FOR [LogicalDeviceTypeID]
GO

if not exists (select * from sysobjects where name='DF_LogicalDevice_Enabled' and  xtype = 'D')
ALTER TABLE [dbo].[LogicalDevice] ADD CONSTRAINT [DF_LogicalDevice_Enabled] DEFAULT (1) FOR [Enabled]
GO

if not exists (select name from sysindexes where name = 'IX_Logical_Device_Name')
ALTER TABLE [dbo].[LogicalDevice] ADD CONSTRAINT [IX_Logical_Device_Name] UNIQUE  NONCLUSTERED ([Name]) ON [PRIMARY]
GO

/******************************************************************************************************************
-- Add new table for logical device type measurements
*******************************************************************************************************************/
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,
 CONSTRAINT [PK_SRC_LDTypeMeasurement] PRIMARY KEY CLUSTERED 
(
	[MeasurementID] ASC,
	[SourceTypeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

if not exists (select * from sysobjects where name='FK_SRC_LDTypeMeasurement_MM_MeasurementMap' and  xtype = 'F')
ALTER TABLE [dbo].[SRC_LDTypeMeasurement]  WITH CHECK ADD  CONSTRAINT [FK_SRC_LDTypeMeasurement_MM_MeasurementMap] FOREIGN KEY([MeasurementID])
REFERENCES [dbo].[MM_MeasurementMap] ([ID])
GO

if not exists (select * from sysobjects where name='FK_SRC_LDTypeMeasurement_MM_MeasurementMap' and  xtype = 'F')
ALTER TABLE [dbo].[SRC_LDTypeMeasurement] CHECK CONSTRAINT [FK_SRC_LDTypeMeasurement_MM_MeasurementMap]
GO

if not exists (select * from sysobjects where name='FK_SRC_LDTypeMeasurement_SRC_SourceType' and  xtype = 'F')
ALTER TABLE [dbo].[SRC_LDTypeMeasurement]  WITH CHECK ADD  CONSTRAINT [FK_SRC_LDTypeMeasurement_SRC_SourceType] FOREIGN KEY([SourceTypeID])
REFERENCES [dbo].[SRC_SourceType] ([SourceTypeID])
GO

if not exists (select * from sysobjects where name='FK_SRC_LDTypeMeasurement_SRC_SourceType' and  xtype = 'F')
ALTER TABLE [dbo].[SRC_LDTypeMeasurement] CHECK CONSTRAINT [FK_SRC_LDTypeMeasurement_SRC_SourceType]
GO




/******************************************************************************************************************
-- Create or update stored procedures for logical device
*******************************************************************************************************************/

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

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

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

CREATE PROCEDURE dbo.spNOM_LogicalDevice_Insert
(	
	@aName NVARCHAR(200),
	@aDeviceTypeName NVARCHAR(100),  
	@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

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

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

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

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

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

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

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

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 


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

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

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

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
GO

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

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

GO

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

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


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

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


/******************************************************************************************************************
-- Following two stored procedures updates for data logs for logical devices
*******************************************************************************************************************/
------------------------------------------------------------------------------
-- spMM_UpdateMapEntry
------------------------------------------------------------------------------
if exists (select * from sysobjects where name='spMM_UpdateMapEntry' and  xtype = 'P')
DROP PROCEDURE spMM_UpdateMapEntry
GO

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

------------------------------------------------------------------------------
-- spIAS_UpdateMeasurementAddress
------------------------------------------------------------------------------
if exists (select * from sysobjects where name='spIAS_UpdateMeasurementAddress' and  xtype = 'P')
DROP PROCEDURE 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


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

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

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

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

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

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

------------------------------------------------------------------------------
-- spIAS_GetAllMeasurements
------------------------------------------------------------------------------
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

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

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
------------------------------------------------------------------------------
-- spSRC_SourceType_Add
------------------------------------------------------------------------------
if exists (select * from sysobjects where name='spSRC_SourceType_Add' and  xtype = 'P')
DROP PROCEDURE spSRC_SourceType_Add
GO

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
------------------------------------------------------------------------------
-- spSRC_SourceType_Retrieve
------------------------------------------------------------------------------
if exists (select * from sysobjects where name='spSRC_SourceType_Retrieve' and  xtype = 'P')
DROP PROCEDURE spSRC_SourceType_Retrieve
GO
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
------------------------------------------------------------------------------
-- spSRC_SourceType_Delete
------------------------------------------------------------------------------
if exists (select * from sysobjects where name='spSRC_SourceType_Delete' and  xtype = 'P')
DROP PROCEDURE spSRC_SourceType_Delete
GO
Create PROCEDURE [dbo].[spSRC_SourceType_Delete]
	@theID SMALLINT
AS
DECLARE @retval INT
DELETE 
FROM 
	dbo.SRC_SourceType
WHERE
	SourceTypeID = @theID
SET @retval = @@ERROR
RETURN @retval
GO
------------------------------------------------------------------------------
-- spSRC_SourceType_RetrieveAll
------------------------------------------------------------------------------
if exists (select * from sysobjects where name='spSRC_SourceType_RetrieveAll' and  xtype = 'P')
DROP PROCEDURE spSRC_SourceType_RetrieveAll
GO
Create PROCEDURE [dbo].[spSRC_SourceType_RetrieveAll]
AS
DECLARE @retval INT
SELECT
	SourceTypeID,
	Name,
	Notes
FROM
	dbo.SRC_SourceType
SET @retval = @@ERROR
RETURN @retval
GO
------------------------------------------------------------------------------
-- spSRC_SourceType_RetrieveID
------------------------------------------------------------------------------
if exists (select * from sysobjects where name='spSRC_SourceType_RetrieveID' and  xtype = 'P')
DROP PROCEDURE spSRC_SourceType_RetrieveID
GO
Create PROCEDURE [dbo].[spSRC_SourceType_RetrieveID]
	@aName			NVARCHAR(200),
	@aSourceTypeID	SMALLINT 		OUTPUT
AS
DECLARE @retval INT
SET @aSourceTypeID = NULL
SELECT
	@aSourceTypeID = SourceTypeID
FROM
	dbo.SRC_SourceType
WHERE
	Name = @aName
SET @retval = @@ERROR
RETURN @retval


GO


------------------------------------------------------------------------------
-- spSRC_SourceCategory_Add
------------------------------------------------------------------------------
if exists (select * from sysobjects where name='spSRC_SourceCategory_Add' and  xtype = 'P')
DROP PROCEDURE spSRC_SourceCategory_Add
GO
CREATE PROCEDURE dbo.spSRC_SourceCategory_Add
(
	@aSourceCategoryID	SMALLINT 		= NULL	OUTPUT	,
	@aName 				NVARCHAR(50) 					,
	@aInfoTable 		NVARCHAR(100) 					,
	@aDescription 		NVARCHAR(2000)	= NULL
)
AS

	IF (@aSourceCategoryID IS NOT NULL)
	BEGIN		-- if ID is not null, raise an error if it exists
		IF EXISTS 
		(
			SELECT
				SourceCategoryID 
			FROM 
				dbo.SRC_SourceCategory 
			WHERE 
				SourceCategoryID = @aSourceCategoryID
		)
		BEGIN
			RAISERROR('Cannot add existing SourceCategoryID %d', 16, 1, @aSourceCategoryID)
			RETURN 1
		END
	END
	ELSE
	BEGIN		-- a source cat id was not specified, so generate a new one
		DECLARE @maxID SMALLINT
		SELECT 
			@maxID = MAX(SourceCategoryID)
		FROM
			dbo.SRC_SourceCategory
		IF(@maxID IS NULL)
		BEGIN
			SET @aSourceCategoryID = 1
		END
		ELSE	-- we found a maxID that we can increment
		BEGIN
			SELECT @aSourceCategoryID = @maxID + 1
		END
	END
	
	-- insert the record ...
	INSERT INTO
		dbo.SRC_SourceCategory
		(
			SourceCategoryID,
			Name,
			InfoTable,
			Description
		)
	VALUEs
		(
			@aSourceCategoryID,
			@aName,
			@aInfoTable,
			@aDescription
		)
		
	RETURN @@ERROR
GO

/******************************************************************************************************************
-- Add new column to the SRC_Source type table
--*******************************************************************************************************************/
if NOT Exists(select * from sys.columns where Name = N'Notes' and Object_ID = Object_ID(N'SRC_SourceType')) 
BEGIN
	ALTER TABLE SRC_SourceType
	ADD
	Notes  NVARCHAR(1000)  NULL;
END
GO

/******************************************************************************************************************
-- Add Logical Device Category to the SRC_SourceCategory table
--*******************************************************************************************************************/
EXEC dbo.spSRC_SourceCategory_Add   6, N'Logical',		'', 						    N'Sources that correspond to logical devices.'