USE [--name--]
GO
SET NOCOUNT ON
GO
--*****************************************************************************
-- Temporary stored procedure to add entries to the measurement hierarchy tables
-- that define the measurement selection trees.
--
-- $Id: //rdsw/dev/cayman/SW_ION_E/database/DefaultData/MeasurementHierarchy/01.CreateHierarchyInsertProcedures.sql#2 $
-- 
--*****************************************************************************
GO
CREATE PROCEDURE dbo.tmpAddMeasurementNode
(
	@aHierarchy NVARCHAR(50),
	@aParentName NVARCHAR(200),
	@aNodeName NVARCHAR(400),
	@aIsMeasurement BIT
)
AS
-- check the node name
IF (@aNodeName IS NULL OR LEN(@aNodeName) = 0 )
BEGIN
	RAISERROR('Node name cannot be null or empty.', 16, 1)
END
-- find the hierarchy ID
DECLARE @hierarchyID SMALLINT
SELECT @hierarchyID = HierarchyID
FROM	dbo.RPT_MeasurementHierarchy
WHERE HierarchyName = @aHierarchy
IF (@hierarchyID IS NULL)
BEGIN
	RAISERROR('Could not locate hierarchy named ''%s''.', 16, 1, @aHierarchy)
	RETURN -1
END
-- find the ID of the parent node; a null parent name denotes a top-level node
DECLARE @parentID SMALLINT
SET @parentID = NULL
IF (@aParentName IS NOT NULL)
BEGIN
	SELECT @parentID = MIN(TreeNodeID)
	FROM dbo.RPT_MeasurementHierarchyNodes
	WHERE DisplayName = @aParentName
	IF (@parentID IS NULL)
	BEGIN
		RAISERROR('Could not locate a parent node named ''%s''.', 16, 1, @aParentName)
		RETURN -1
	END	
END
-- find the ID of the measurement, if there should be one
DECLARE @measurementID SMALLINT
SET @measurementID = NULL
IF (@aIsMeasurement = 1)
BEGIN 
	SELECT @measurementID = dbo.GetMeasurementID(@aNodeName)
	IF (@measurementID IS NULL OR @measurementID <= 0)
	BEGIN
		RAISERROR('Could not map node name ''%s'' to a measurement ID.', 16, 1, @aNodeName)
		RETURN -1
	END
END

INSERT INTO dbo.RPT_MeasurementHierarchyNodes 
( 
	[ParentID], 
	[HierarchyID], 
	[MeasurementID], 
	[DisplayName], 
	[DisplayNameShort]
) 
VALUES 
(
	@parentID, 
	@hierarchyID, 
	@measurementID, 
	@aNodeName, 
	'' 					-- for ION Enterprise we aren't using the short display name
)

IF (@@ERROR <> 0)
BEGIN
	DECLARE @ismeas INT
	SET @ismeas = CAST(@aIsMeasurement AS BIT)
	RAISERROR('Error adding measurement node: H=%s+%d; P=%s+%d; N=%s; M=%d+%d', 16, 1, 
		@aHierarchy, @hierarchyID, 
		@aParentName, @parentID, 
		@aNodeName, 
		@ismeas, @measurementID)
	RETURN -1
END
GO

--*****************************************************************************
CREATE PROCEDURE dbo.tmpAddMeasurementHierarchy
(
	@aHierarchy NVARCHAR(50),
	@aDescription NVARCHAR(250)
)
AS
IF (
	NOT EXISTS 
		(SELECT HierarchyID FROM dbo.RPT_MeasurementHierarchy 
		WHERE HierarchyName = @aHierarchy)
	)
BEGIN
	INSERT INTO  RPT_MeasurementHierarchy
		([HierarchyName], [HierarchyDescription])
	VALUES
		(@aHierarchy, @aDescription)
END
GO

