--------------------------------------------------------------------------
--
-- PME - Measurement Edit.sql
-- Script Version 1.2 - Updated Dec 24, 2018
-- PME 9.0
--
--------------------------------------------------------------------------


-- This script is used to add/update PME measurement metadata.

-- With it, you can:
--   - Add/update metadata triplestore predicate objects in ION_Network
--   - Remove a metadata triplestore entry in ION_Network

-- Use cases:
--   - A measurement's 'aggregation' predicate can be set to 'average' 
--     (default behavior is 'sum'); this can be used to fix undesired
--     report, dashboard, and trend behavior.
--   - A measurement's 'unit' predicate can be set.  The unit value must
--     already exist (for an existing measurement) in PME for this to work.
--   - A measurement can be flagged with the 'cumulative' predicate
--     so PME treats it as a "running total" like it does for measurements
--     such as 'Active Energy Delivered'.


-- As of PME 9.0, the ApplicationModules database measurements will be 
-- updated automatically in a 'live' system, within several minutes.

-- Earlier versions of PME will require additional steps to be followed.



--------------------------------------------------------------------------
-- Set this variable to 1 to output a report only; will not modify any metadata
-- Set this variable to 0 to execute the measurement metadata addition/update 
DECLARE @REPORT_MODE bit = 0;



--------------------------------------------------------------------------
-- We populate the table variable below with our desired measurement and
-- metadata, to add/update predicates such as aggregation, unit, cumulative, etc.

-- Can include the same measurement more than once, to set different predicates.
-- Don't worry, you won't get duplicate measurements!
-- Set the object to NULL for a given predicate if you want to remove the predicate.

DECLARE @Measurements TABLE
(Name nvarchar(200),
 MetadataPredicate nvarchar(400),
 MetadataObject nvarchar(400)
)

-- It is important to understand the relationship between predicates and objects 
-- For a complete view of the relationship between existing measurements, predicates, and objects, run the following select statement:
-- 		(SELECT * FROM [ION_Network].[dbo].[vTS_Triples])
-- 		Note that the measurement name in [vTS_Triples] is in the "Subject" column, and includes spaces as %20 entries

-- List of the most frequently used Predicates: 
-- aggregation
-- cumulative
-- defaultrollupmethod
-- demand
-- direction
-- powerfactor
-- quantity
-- unit

-- For a complete list of available Predicates, run the following select statement:
-- 		(SELECT distinct predicate FROM [ION_Network].[dbo].[vTS_Triples])


-- The next section is where you must enter the details for the measurement name, the predicate, and the object
-- i.e.  INSERT INTO @Measurements VALUES('<measurment name>','<predicate>','<object>')

-- EXAMPLE USER CODE - EDIT/COPY/PASTE THE BELOW AS NEEDED
--------------------------------------------------------------------------
INSERT INTO @Measurements VALUES('MD_kVA','defaultrollupmethod','rollupmethod/sum')
--INSERT INTO @Measurements VALUES('Humidity','aggregation','average')
--INSERT INTO @Measurements VALUES('Humidity','unit','unit/percent')
--INSERT INTO @Measurements VALUES('Humidity','quantity','quantity/relativehumidity')
--INSERT INTO @Measurements VALUES('Humidity','CDM/descriptivename','Humidity')
--INSERT INTO @Measurements VALUES('Humidity','CDM/engineeringname','Humidity')
--INSERT INTO @Measurements VALUES('Widgets','cumulative','Widgets This Interval')
--INSERT INTO @Measurements VALUES('MD_kVA','cumulative',NULL)  --removes the 'cumulative' predicate for 'Widgets'

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



--NON-USER CODE - DO NOT EDIT THE CODE BELOW
--------------------------------------------------------------------------
-- The rest of this code is driven by the data configured above by the user,
-- in the @Measurments table and with the @REPORT_MODE variable.

DECLARE
	@ValidPredicates bit,
    @Measurement nvarchar(200),
    @MetadataPredicate nvarchar(400),
    @MetadataObject nvarchar(400),
    @FullName nvarchar(400),
    @FixedName nvarchar(400),
    @Model nvarchar(400),
    @PredicateNamespace nvarchar(400),
    @SubjectNamespace nvarchar(400),
	@ObjectNamespace nvarchar(400),
    @m_id int,
    @result int;

SET @Model = 'http://rddl.xmlinside.net/PowerMeasurement/configuration/measurement/1/#'
SET @PredicateNamespace = 'http://rddl.xmlinside.net/PowerMeasurement/configuration/measurement/qualifier/1/#'
SET @SubjectNamespace = 'http://rddl.xmlinside.net/PowerMeasurement/configuration/measurement/1/#'
SET @ValidPredicates = 1

--Validate the predicate and object pairs, in either mode
--For most predicates, applicable combinations should already be in the database, 
-- so we want to flag invalid pairings.  'cumulative' is the lone exception
-- where we allow any object value for it.
PRINT 'Validating measurement predicate/object combinations ...'
PRINT '--------------------------------------------------------'
DECLARE measurement_cursor CURSOR FOR
SELECT Name, MetadataPredicate, MetadataObject FROM @Measurements ORDER BY Name, MetadataPredicate

OPEN measurement_cursor

FETCH NEXT FROM measurement_cursor
INTO @Measurement,@MetadataPredicate,@MetadataObject

WHILE @@FETCH_STATUS = 0
BEGIN
	--If either/both predicate/object are set to NULL, we don't validate 
	-- because NULL is used as predicate to simply add measurements, or
	-- for object to remove a predicate
	IF (@MetadataPredicate IS NOT NULL AND @MetadataObject IS NOT NULL)
	BEGIN
		SET @result = (SELECT COUNT(*)
		FROM [ION_Network].[dbo].[vTS_Triples]
		WHERE Predicate = @MetadataPredicate AND Object = @MetadataObject)

		IF (@result = 0 AND @MetadataPredicate <> 'cumulative' AND @MetadataPredicate <> 'CDM/descriptivename' AND @MetadataPredicate <> 'CDM/engineeringname' )
		BEGIN
			SET @ValidPredicates = 0
			PRINT 'Invalid Predicate / Object combination for measurement: "' + @Measurement + '"'
			PRINT 'Predicate: ' + @MetadataPredicate
			PRINT 'Object: ' + @MetadataObject
			PRINT 'This issue must be corrected to allow add/update operations.'
			PRINT ''
		END
	END
	ELSE IF (@MetadataPredicate IS NOT NULL AND @MetadataObject IS NULL)
	BEGIN
		PRINT 'Will remove predicate "' + @MetadataPredicate + '" for measurement "' + @Measurement + '".'
		PRINT ''
	END

	FETCH NEXT FROM measurement_cursor
	INTO @Measurement,@MetadataPredicate,@MetadataObject
END
CLOSE measurement_cursor
DEALLOCATE measurement_cursor
PRINT ''
PRINT 'Validation of measurement predicate/object combinations completed.'
PRINT '--------------------------------------------------------'
PRINT ''

--Report only check
IF (@REPORT_MODE = 1)
BEGIN
	--for each measurement show the existing ION_Network metadata that AppMods will see in a scan
	PRINT 'Running in measurement report mode ...'
	DECLARE measurement_cursor CURSOR FOR
	SELECT DISTINCT Name FROM @Measurements ORDER BY Name

	OPEN measurement_cursor

	FETCH NEXT FROM measurement_cursor
	INTO @Measurement

	WHILE @@FETCH_STATUS = 0
	BEGIN
		EXEC ION_Network.[dbo].GetCdmMeasurementMetadata @Measurement
		FETCH NEXT FROM measurement_cursor
		INTO @Measurement
	END
	CLOSE measurement_cursor
	DEALLOCATE measurement_cursor
	PRINT 'Measurement report mode completed.'
END
ELSE IF (@ValidPredicates = 1)
BEGIN
	PRINT 'Running in measurement update mode ...'
	DECLARE measurement_cursor CURSOR FOR
	SELECT Name, MetadataPredicate, MetadataObject FROM @Measurements ORDER BY Name, MetadataPredicate

	OPEN measurement_cursor

	FETCH NEXT FROM measurement_cursor
	INTO @Measurement,@MetadataPredicate,@MetadataObject

	-- Loop over all mapping definitions, creating sources and measurements as needed and then copying data where specified
	WHILE @@FETCH_STATUS = 0
	BEGIN
		--Ensure target measurement is present in ION_Data and ION_Network; also set metadata if applicable
		SET @FixedName = [ION_Network].[dbo].[udfMMS_EscapeStandardName](@Measurement)
		SET @FullName = @Model + @FixedName
		EXEC @result = ION_Network.[dbo].[spMM_CheckNewNames] @FullName, @Measurement
		IF (@result = 0)
		BEGIN
			EXEC ION_Network.[dbo].[spMM_AddMapEntry] @FullName, @Measurement, @ID=@m_id OUTPUT
		END
		IF @MetadataPredicate IS NOT NULL
		BEGIN
			SET @ObjectNamespace = (SELECT TOP 1 ObjectNamespace FROM [ION_Network].[dbo].[vTS_Triples] WHERE Predicate = @MetadataPredicate)
			IF @MetadataObject IS NOT NULL
			BEGIN
				EXEC ION_Network.dbo.spTS_Triples_Create @Model, @SubjectNamespace, @FixedName, @PredicateNamespace, @MetadataPredicate, @ObjectNamespace, @MetadataObject
			END
			ELSE	--the @MetadataObject is NULL, which means we want to remove this @MetadataPredicate
			BEGIN
				SET @MetadataObject = (SELECT TOP 1 Object FROM vts_triples WHERE SubjectNamespace = @SubjectNamespace AND Subject = @FixedName AND PredicateNamespace = @PredicateNamespace AND Predicate = @MetadataPredicate)
				IF @MetadataObject IS NOT NULL
				BEGIN
					EXEC ION_Network.dbo.spTS_Triples_Delete @Model, @SubjectNamespace, @FixedName, @PredicateNamespace, @MetadataPredicate, @ObjectNamespace, @MetadataObject
					PRINT 'Removed predicate "' + @MetadataPredicate + '" for measurement "' + @Measurement + '".'
				END
				ELSE
				BEGIN
					PRINT 'Predicate "' + @MetadataPredicate + '" was previously removed for measurement "' + @Measurement + '".'
				END
			END
		END
	
		EXEC ION_Data.[dbo].[InsertQuantity] @Measurement, @ID=@m_id OUTPUT

		FETCH NEXT FROM measurement_cursor
		INTO @Measurement,@MetadataPredicate,@MetadataObject
	END
	CLOSE measurement_cursor
	DEALLOCATE measurement_cursor
	PRINT 'Measurement update operations completed.'
END