-- before adding additional default data
-- set the seeding back to the default area
-- so new values won't be added as custom
-- some values were added in the 9000 range for EEM
-- we want to add ION E values in the first slots before 9000, not 10000

declare @maxID int
SET @maxID = NULL

select @maxID= max(ID) from Quantity where ID < 9000
DBCC CheckIdent(Quantity, reseed, @maxID)



/******************************************************************************************************************
-- Check for standard name
--*******************************************************************************************************************/
--Creating [dbo].[udfMMS_IsStandardNameOK]'

if exists (select * from sysobjects where name='udfMMS_IsStandardNameOK' and  xtype = 'FN')
	DROP FUNCTION udfMMS_IsStandardNameOK
GO

 
--*****************************************************************************
/* ##FND_BEGIN dbo.udfMM_IsStandardNameOK ^^
This functions checks a Measurement standard name to ensure that it contains
only valid characters that will work with the simple encoding scheme used by 
udfMM_EscapeStandardName. 

Returns 1 if the standard name is OK, or 0 if there is an invalid character
in the name.

<P><I>For more information, follow the Source link at the top of the page.</I></P>
##FND_END */
--
-- $Id: //rdsw/dev/cayman/SW_VB6/business/MSDEUpgrader/scripts/NOM/NOM11pt1_Upgrade.sql#1 $
-- 
--*****************************************************************************
CREATE FUNCTION [dbo].[udfMMS_IsStandardNameOK]
(
	@input NVARCHAR(400)
)
RETURNS BIT
BEGIN
	DECLARE @inputOK BIT
	SET @inputOK = 1
	DECLARE @allowed NVARCHAR(200)
	-- this is the list of characters that we accept in the unescaped standard name
	SET @allowed = 
		N'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789+-_() /.^'
	-- We're going to loop over the input string and inspect each character to 
	-- ensure that it is in our list of allowed characters.
	DECLARE @found INT			-- will tell us if we found the charcter
	DECLARE @char NVARCHAR(1)	-- the currect character we are checking
	DECLARE @idx INT			-- index into the input string
	SET @idx = 1				-- start at first character
	WHILE(@idx <= LEN(@input))	-- loop over input string
	BEGIN
		SET @char = SUBSTRING(@input, @idx, 1)
		SET @found = CHARINDEX(@char, @allowed)
		IF(@found = 0)
		BEGIN
			SET @inputOK = 0
			BREAK
		END	
		SET @idx = @idx + 1
	END
	RETURN @inputOK
END
GO

IF EXISTS(SELECT * FROM sysobjects WHERE name = 'spEntryConflictHandler' AND xtype = 'P')
	DROP PROCEDURE spEntryConflictHandler
GO

CREATE PROCEDURE dbo.spEntryConflictHandler
	@oldID				SMALLINT,
	@ID					SMALLINT,
	@conflictingName	NVARCHAR(250),
	@deleteOld			BIT = 1
AS
	IF @oldID = 0
	BEGIN
		SELECT @oldID=ID FROM Quantity WHERE Name=@conflictingName
		IF @oldID = NULL
			RETURN
	END
	IF @oldID = @ID
		RETURN

	UPDATE dbo.[Channel] SET QuantityID = @ID WHERE QuantityID = @oldID
	UPDATE dbo.[DefaultLabelQuantity] SET QuantityID = @ID WHERE QuantityID = @oldID
	IF exists (select * from sysobjects where name='DataLog' and  xtype = 'U')
	BEGIN
		UPDATE dbo.[DataLog] SET QuantityID = @ID WHERE QuantityID = @oldID
	END
	ELSE
	BEGIN
		UPDATE dbo.[DataLog2] SET QuantityID = @ID WHERE QuantityID = @oldID
--		UPDATE dbo.[EventString] SET QuantityID = @ID WHERE QuantityID = @oldID
	END

	IF exists (select * from sysobjects where name='WaveformLog' and  xtype = 'U')
	BEGIN
		UPDATE dbo.[WaveformLog] SET QuantityID = @ID WHERE QuantityID = @oldID
	END
	ELSE
	BEGIN
		UPDATE dbo.[WaveformLog2] SET QuantityID = @ID WHERE QuantityID = @oldID
		UPDATE dbo.[SourceQuantity] SET QuantityID = @ID WHERE QuantityID = @oldID
	END

	IF(@deleteOld = 1)
		DELETE FROM dbo.Quantity WHERE ID = @oldID

GO

/****************************************************************************************************************
-- Rename standard measurements
****************************************************************************************************************/
if exists (select * from sysobjects where name='tmpRenameStandardEntry' and  xtype = 'P')
DROP PROCEDURE dbo.tmpRenameStandardEntry
GO

CREATE PROCEDURE dbo.tmpRenameStandardEntry
( 
	@ID SMALLINT,
	@oldStandardName NVARCHAR(200),
	@standardName NVARCHAR(200)
)
AS 
	IF EXISTS(SELECT 1 FROM dbo.Quantity WHERE ID= @ID)
	BEGIN
		EXEC spEntryConflictHandler 0, @ID, @standardname
	END
	UPDATE dbo.Quantity SET Name = @standardName  WHERE Name = @oldStandardName
GO

----------------------------------------------
----------------------------------------------
-----------------------------------------------
-- modifying spMM_AddMapEntry_Compatible

IF EXISTS(SELECT * FROM sysobjects WHERE name = 'spMM_AddMapEntry_Compatible' AND xtype = 'P')
	DROP PROCEDURE spMM_AddMapEntry_Compatible
GO

--*****************************************************************************
-- PROCEDURE: spMM_AddMapEntry_Compatible
--
-- NOTES:
-- Adds an entry for which the ID is known.
-- If the specified ID already exists, an error is raised and the entry is not added.
-- If the standard name has a duplicate:
--  1) the entry's ID is reassigned on upgrades.
--  2) or an error is raised on new installs.
-- This still allows compatibility with previous ID-based quantity schemes.
-- This variation uses the Quantity table instead of MM_Measurement map.
-- The Quantity table is being retained to avoid upgrading the indexes in the DataLog table.
--
-- $Id: //rdsw/dev/cayman/SW_ION_E/database/Components/ION/StoredProcedures/02.MM_AddMapEntry_Compatible.sql#2 $
--
--*****************************************************************************
CREATE PROCEDURE [dbo].[spMM_AddMapEntry_Compatible]
	@ID SMALLINT,
	@fullname NVARCHAR(1000),
	@standardname NVARCHAR(1000),
	@debug BIT = 0
AS

SET NOCOUNT ON
IF(@debug = 1)
BEGIN
	PRINT N'Compatible entry:' + CAST(@ID AS NVARCHAR(10)) + N' -> [' + @fullname + N'] ' + @standardname
	SET NOCOUNT OFF
END
ELSE
BEGIN
	SET NOCOUNT ON
END

DECLARE @result INT
DECLARE @oldID SMALLINT
DECLARE @ExistingName NVARCHAR(1000)
SET @result=0

SELECT @ExistingName=Name FROM Quantity WHERE ID=@ID

IF @ExistingName IS NULL 
BEGIN
	SET NOCOUNT ON
	SELECT @oldID=ID FROM Quantity WHERE Name=@standardname
	IF @oldID IS NOT NULL
	BEGIN
		--Upgrade only: rename before delete because of the foreign key constraints with DefaultLabelQuantity and DataLog2
		UPDATE Quantity Set Name = 'tmpForDelete' WHERE ID=@oldID
	END

	SET IDENTITY_INSERT Quantity ON
	INSERT INTO Quantity (ID,Name) VALUES (@ID,@standardname)
	SET @result = @@ERROR
	SET IDENTITY_INSERT Quantity OFF

	IF @oldID IS NOT NULL
	BEGIN
		EXEC spEntryConflictHandler @oldID, @ID, @standardname
	END
END
ELSE IF @ExistingName!=@standardname
BEGIN
	RAISERROR('ID already in use by measurement ''%s''', 16, 1,@ExistingName)
	SET @result=1
END

RETURN @result
GO

--
-- Script for dbo.spIRM_MapLabelToMeasurement
-- Foreign keys etc. will appear at the end
--
if exists (select * from sysobjects where id=object_id('[dbo].[spIRM_MapLabelToMeasurement]') and OBJECTPROPERTY(id, 'IsProcedure')=1)
  drop procedure [dbo].[spIRM_MapLabelToMeasurement]
GO

--*****************************************************************************
-- PROCEDURE: spIRM_MapLabelToMeasurement
--
-- NOTES:
--
-- $Id: //rdsw/dev/cayman/SW_VB6/business/MSDEUpgrader/scripts/ION/IRM_MapLabelToMeasurement.sql#1 $
--
--*****************************************************************************

-- Populates the DefaultLabelQuantity table.
-- Pattern is ignored.
-- For backward compatibility.
CREATE PROCEDURE [dbo].spIRM_MapLabelToMeasurement
	@Label NVARCHAR(1000), 
	@Pattern NVARCHAR(1000),
	@PhaseLabelType NVARCHAR(100),
	@StandardMeasurementName NVARCHAR(1000)
AS

SET NOCOUNT ON

DECLARE @MeasurementID SMALLINT
DECLARE @ExistingQuantityID SMALLINT
DECLARE @PhaseLabelTypeID TINYINT
SET @MeasurementID = NULL
SET @PhaseLabelTypeID  = NULL

EXEC spMM_GetIDByName @StandardMeasurementName, @MeasurementID OUTPUT

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

IF @PhaseLabelType IS NOT NULL
BEGIN
	SELECT @PhaseLabelTypeID=ID FROM PhaseLabelType WHERE PhaseLabels=@PhaseLabelType
END

SELECT @ExistingQuantityID = QuantityID FROM DefaultLabelQuantity 
WHERE Label=@Label AND (PhaseLabelTypeID=@PhaseLabelTypeID OR PhaseLabelTypeID IS NULL AND @PhaseLabelTypeID IS NULL)

IF @ExistingQuantityID IS NULL
BEGIN
	INSERT INTO DefaultLabelQuantity (Label,QuantityID,PhaseLabelTypeID) VALUES (@Label,@MeasurementID,@PhaseLabelTypeID)
END
ELSE IF @ExistingQuantityID != @MeasurementID
BEGIN
	-- no identifier column so we'll use the natural key columns ....
	UPDATE DefaultLabelQuantity Set QuantityID = @MeasurementID WHERE 
	Label=@Label AND (PhaseLabelTypeID=@PhaseLabelTypeID OR PhaseLabelTypeID IS NULL AND @PhaseLabelTypeID IS NULL)
END

RETURN

GO


/******************************************************************************************************************
-- Check for escaped standard name
--*******************************************************************************************************************/
--Creating [dbo].[udfMMS_EscapeStandardName]'


if exists (select * from sysobjects where name='udfMMS_EscapeStandardName' and  xtype = 'FN')
	DROP FUNCTION udfMMS_EscapeStandardName
GO

--*****************************************************************************
/* ##FND_BEGIN Name ^^
Function that escapes a string containing a standard name. 
Checks that the string contains valid characers and performs a simple escaping
scheme by replacing spaces with %20 and ^ with %5E.

If there is a problem with the standard name provided to the function, 
NULL is returned.

<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/MMS_MeasurementMapSupport/UserDefinedFunctions/10.udfMMS_EscapeStandardName.sql#2 $
-- 
--*****************************************************************************
CREATE FUNCTION [dbo].[udfMMS_EscapeStandardName]
( 
	@unescapedStandardName NVARCHAR(200)
)
RETURNS NVARCHAR(200)
BEGIN 
	DECLARE @escapedStandardName NVARCHAR(200)
	SET @escapedStandardName = NULL
	DECLARE @isNameOK INT
	SELECT @isNameOK = dbo.udfMMS_IsStandardNameOK(@unescapedStandardName)
	IF(@isNameOK = 1)
	BEGIN
		-- let's do our simplistic escaping routine
		SET @escapedStandardName = REPLACE(@unescapedStandardName, N' ', N'%20')
		SET @escapedStandardName = REPLACE(@escapedStandardName, N'^', N'%5E')
	END
	RETURN @escapedStandardName
END
GO
