-- shell for NOM13.1 upgrade

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[plsp_tou_add_daygroup]') 
   AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[plsp_tou_add_daygroup]
GO
CREATE PROCEDURE plsp_tou_add_daygroup
	@DayGroup_Name nvarchar(256),
	@Season_ID int,
	@IncludeDays int

AS

SET NOCOUNT ON
INSERT INTO PL_TOU_DayGroup (DayGroup_Name, Season_ID, IncludeDays,AllowOverlap)
	VALUES(@DayGroup_Name, @Season_ID, @IncludeDays,0)

SELECT DayGroup_ID FROM PL_TOU_DayGroup WHERE DayGroup_Name = @DayGroup_Name AND Season_ID=@Season_ID

GO




IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[spIRM_FindNOMDeviceInfo]') 
   AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[spIRM_FindNOMDeviceInfo]
GO
CREATE PROCEDURE [dbo].[spIRM_FindNOMDeviceInfo]
	@Device NVARCHAR(200),
	@DeviceType NVARCHAR(50) OUTPUT,
	@Protocol NVARCHAR(100) OUTPUT,
	@Translator NVARCHAR(100) OUTPUT,
	@TreeFile NVARCHAR(200) OUTPUT,
	@IsDeviceEnabled INT OUTPUT
AS

SET @DeviceType=NULL
SET @Protocol=NULL
SET @Translator=NULL
SET @TreeFile=NULL

SELECT @DeviceType=dt.Type, @Protocol=p.Name,@Translator=t.Name, @TreeFile=dt.TreeFile , @IsDeviceEnabled=d.Enabled
	FROM Device AS d JOIN DeviceType AS dt ON d.Name=@Device AND dt.ID=d.DeviceTypeID 
	JOIN Translator AS t ON dt.TranslatorID=t.ID JOIN Protocol AS p ON t.ProtocolID=p.ID

IF @DeviceType IS NULL
BEGIN
	SET @IsDeviceEnabled=1
	SELECT @DeviceType=t.Type, @Protocol=t.Type, @Translator=t.Type
		FROM SoftwareNode AS s JOIN SoftwareNodeType AS t
		ON s.Name=@Device AND t.ID=s.SoftwareNodeTypeID 
END

RETURN 0
GO



IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[spIRM_GetDeviceInfo]') 
   AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[spIRM_GetDeviceInfo]
GO
CREATE PROCEDURE [dbo].[spIRM_GetDeviceInfo]
	@Device NVARCHAR(200),
	@ID SMALLINT OUTPUT,
	@AggregateSetupCount INT OUTPUT,
	@DeviceTypeHandle INT OUTPUT,
	@DeviceType NVARCHAR(50) OUTPUT,
	@SerialNumberHandle INT OUTPUT,
	@SerialNumber NVARCHAR(50) OUTPUT,
	@PhaseLabels NVARCHAR(10) OUTPUT,
	@VoltsMode NVARCHAR(50) OUTPUT,
	@Framework NVARCHAR(500) OUTPUT,
	@Revision NVARCHAR(200) OUTPUT,
	@TimeUpdatedUTC DATETIME OUTPUT,
	@NOMDeviceType NVARCHAR(50) OUTPUT,
	@TreeFile NVARCHAR(200) OUTPUT,
	@IsDeviceEnabled INT OUTPUT
AS

DECLARE @Protocol NVARCHAR(100)
DECLARE @Translator NVARCHAR(100)
SET @NOMDeviceType=NULL
SET @Protocol=NULL
SET @Translator=NULL
SET @ID=NULL
SET @AggregateSetupCount=NULL
SET @DeviceTypeHandle=NULL
SET @DeviceType=NULL
SET @SerialNumberHandle=NULL
SET @SerialNumber=NULL
SET @PhaseLabels=NULL
SET @VoltsMode=NULL
SET @Framework=NULL
SET @Revision=NULL
SET @TimeUpdatedUTC=NULL
SET @TreeFile=NULL
SET @IsDeviceEnabled=NULL

EXEC spIRM_FindNOMDeviceInfo @Device, @NOMDeviceType OUTPUT, @Protocol OUTPUT, @Translator OUTPUT, @TreeFile OUTPUT, @IsDeviceEnabled OUTPUT

IF @NOMDeviceType IS NOT NULL
BEGIN

	BEGIN TRANSACTION

	-- Only insert a record if an actual device or software node exists in NOM.
	SELECT @ID=ID, @AggregateSetupCount=AggregateSetupCount, 
		@DeviceTypeHandle=DeviceTypeHandle, @DeviceType=DeviceType,
		@SerialNumberHandle=SerialNumberHandle, @SerialNumber=SerialNumber, 
		@PhaseLabels=(SELECT PhaseLabels FROM IRM_PhaseLabelType WHERE ID=PhaseLabelTypeID),
		@VoltsMode=(SELECT Name FROM IRM_VoltsMode WHERE ID=VoltsModeID),
		@Framework=Framework, @Revision=Revision, @TimeUpdatedUTC=TimeUpdatedUTC
		FROM IRM_DeviceInfo WITH (TABLOCKX) WHERE Name=@Device
	IF @ID IS NULL
	BEGIN
		INSERT INTO IRM_DeviceInfo (Name) VALUES (@Device)
		SET @ID=SCOPE_IDENTITY()
	END

	COMMIT TRANSACTION
END

RETURN 0
GO


IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[spIRM_UpdateDeviceInfo]') 
   AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[spIRM_UpdateDeviceInfo]
GO
CREATE PROCEDURE [dbo].[spIRM_UpdateDeviceInfo]
	@Device NVARCHAR(200),
	@AggregateSetupCount INT,
	@DeviceTypeHandle INT,
	@DeviceType NVARCHAR(50),
	@SerialNumberHandle INT,
	@SerialNumber NVARCHAR(50),
	@Framework NVARCHAR(200),
	@Revision NVARCHAR(200),
	@PhaseLabels NVARCHAR(10),
	@VoltsMode NVARCHAR(50),
	@NOMDeviceType NVARCHAR(50) OUTPUT,
	@TreeFile NVARCHAR(200) OUTPUT,
	@TimeUpdatedUTC DATETIME OUTPUT
AS

SET NOCOUNT ON 

DECLARE @Protocol NVARCHAR(100)
DECLARE @Translator NVARCHAR(100)
DECLARE @IsDeviceEnabled INT
SET @NOMDeviceType=NULL
SET @Protocol=NULL
SET @Translator=NULL
SET @TreeFile=NULL

EXEC spIRM_FindNOMDeviceInfo @Device, @NOMDeviceType OUTPUT, @Protocol OUTPUT, @Translator OUTPUT, @TreeFile OUTPUT, @IsDeviceEnabled OUTPUT

IF @NOMDeviceType IS NULL
BEGIN
   RAISERROR('Unknown device ''%s''', 16, 1, @Device)
   RETURN -1
END

DECLARE @VoltsModeID TINYINT
DECLARE @PhaseLabelTypeID TINYINT
SELECT @PhaseLabelTypeID=ID FROM IRM_PhaseLabelType WHERE PhaseLabels=@PhaseLabels

SET @VoltsModeID=NULL
IF @VoltsMode IS NOT NULL
BEGIN
	EXEC spIRM_GetVoltsModeID @VoltsMode, @VoltsModeID OUTPUT
END

SET @TimeUpdatedUTC=GetUTCDate()

UPDATE IRM_DeviceInfo 
  SET AggregateSetupCount=@AggregateSetupCount,
	DeviceTypeHandle=@DeviceTypeHandle,
	DeviceType=@DeviceType,
	SerialNumberHandle=@SerialNumberHandle,
	SerialNumber=@SerialNumber,
	Framework=@Framework,
	Revision=@Revision,
	PhaseLabelTypeID=@PhaseLabelTypeID,
	VoltsModeID=@VoltsModeID,
	TimeUpdatedUTC=@TimeUpdatedUTC
  FROM IRM_DeviceInfo
  WHERE Name=@Device

IF @@ROWCOUNT=0
BEGIN
	INSERT INTO IRM_DeviceInfo (AggregateSetupCount,DeviceTypeHandle,DeviceType,
			SerialNumberHandle,SerialNumber,Framework,Revision,PhaseLabelTypeID,VoltsModeID,TimeUpdatedUTC)
		VALUES (@AggregateSetupCount,@DeviceTypeHandle,@DeviceType,
			@SerialNumberHandle,@SerialNumber,@Framework,@Revision,@PhaseLabelTypeID,@VoltsModeID,@TimeUpdatedUTC)
END

RETURN
GO
