-- shell for NOM13 upgrade

/*****************************************************************************	
	Sends email using CDO
******************************************************************************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE (id = OBJECT_ID(N'[dbo].[SendMailCDO]')) 
	AND (OBJECTPROPERTY(id, N'IsProcedure') = 1))
DROP PROCEDURE [dbo].[SendMailCDO]
GO
CREATE PROCEDURE SendMailCDO
 @SMTPServer varchar(255),
 @To varchar(255)='', 
 @From varchar(255)='', 
 @Subject varchar(255)='', 
 @Body varchar(8000)='', 
 @CC varchar(255)='', 
 @BCC varchar(255)=NULL 

AS 
SET NOCOUNT ON
/* Declare variables */ 
DECLARE @object int, 
@hr int

/* Create a Message */ 
EXEC @hr = sp_OACreate 'CDO.Message',@object OUT

/* if the body is text only */ 
if len(@Body)>0 
 EXEC @hr = sp_OASetProperty @object, 'TextBody', @Body

/* set other variables */ 
EXEC @hr = sp_OASetProperty @object, 'From',@From 
EXEC @hr = sp_OASetProperty @object, 'Subject', @Subject
EXEC @hr = sp_OASetProperty @object, 'To', @To 
EXEC @hr = sp_OASetProperty @object, 'CC', @CC 
EXEC @hr = sp_OASetProperty @object, 'BCC', @BCC

 /* send through what server */ 
EXEC @hr = sp_OASetProperty @object,
'Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value',
@SMTPServer
EXEC @hr = sp_OASetProperty @object,
'Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing").Value',
'2'
EXEC @hr = sp_OAMethod @object, 'Configuration.Fields.Update', NULL

/* Send the mail */ 
EXEC @hr = sp_OAMethod @object, 'Send', NULL

/* End the message session */ 
EXEC @hr = sp_OADestroy @object

return(0)
GO



/************************************************************************************************	
	Removes backup and restore history based on file location and name
************************************************************************************************/

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE (id = OBJECT_ID(N'[dbo].[DeleteDbHistoryByLocation]')) 
	AND (OBJECTPROPERTY(id, N'IsProcedure') = 1))
DROP PROCEDURE [dbo].[DeleteDbHistoryByLocation]
GO

CREATE PROCEDURE  [dbo].[DeleteDbHistoryByLocation]
--location is to contain comma seperated complete path and file name of
--databases to remove history for
@location NVARCHAR(4000)
--this procedure returns successful on all conditions
--if want failure indicated, use errorflag value to determine state on exit
AS
BEGIN
  declare @bsid int
  declare @msid int
  declare @rows int
  declare @errorflag int

  set nocount on
  set @errorflag = 0
  declare oldbackups insensitive cursor for
    SELECT backup_set_id FROM msdb..backupset,msdb..backupmediafamily
    WHERE backupmediafamily.physical_device_name COLLATE database_default in (Select Value FROM dbo.fn_Split(@location, ','))
    AND backupmediafamily.media_set_id = backupset.media_set_id
    for read only
  open oldbackups
  fetch next from oldbackups into @bsid
  while(@@fetch_status = 0)
  begin
    begin transaction
    set rowcount 1
    set @rows = (select count(*) from msdb..restorehistory where backup_set_id = @bsid)
    set rowcount 0
    if (@rows > 0)
    begin
      delete from msdb..restorefile where restore_history_id in (select restore_history_id from msdb..restorehistory where backup_set_id = @bsid)
      if (@@error <> 0)
      begin
         rollback transaction
         set @errorflag = 1
         break
      end
      delete from msdb..restorefilegroup where restore_history_id in (select restore_history_id from msdb..restorehistory where backup_set_id = @bsid)
      if (@@error <> 0)
      begin
         rollback transaction
         set @errorflag = 1
         break
      end
      delete from msdb..restorehistory where backup_set_id = @bsid
      if (@@error <> 0)
      begin
         rollback transaction
         set @errorflag = 1
         break
      end
    end
	if ((SELECT SERVERPROPERTY('EditionID')) is NOT NULL)
      begin
		delete from msdb..backupfilegroup where backup_set_id = @bsid
      end
    delete from msdb..backupfile where backup_set_id = @bsid
    if (@@error <> 0)
    begin
       rollback transaction
       set @errorflag = 1
       break
    end
    set @msid = (select media_set_id from msdb..backupset where backup_set_id = @bsid)
    delete from msdb..backupset where backup_set_id = @bsid
    if (@@error <> 0)
    begin
       rollback transaction
       set @errorflag = 1
       break
    end
    set rowcount 1
    set @rows = (select count(*) from msdb..backupset where media_set_id = @msid)
    set rowcount 0
    if (@rows = 0)
    begin
      delete from msdb..backupmediafamily where media_set_id = @msid
      if (@@error <> 0)
      begin
         rollback transaction
         set @errorflag = 1
         break
      end
      delete from msdb..backupmediaset where media_set_id = @msid
      if (@@error <> 0)
      begin
         rollback transaction
         set @errorflag = 1
         break
      end
    end
    commit transaction
    fetch next from oldbackups into @bsid
  end
  deallocate oldbackups
  set nocount off
  return(0)

END
GO

/*****************************************************************************************************************************	
	Modify spIAS_ChangeOrAddSourceMeasurementReference to set TimeResolvedUTC column of IAS_MeasurementAddress to UTC time
******************************************************************************************************************************/

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE (id = OBJECT_ID(N'[dbo].[spIAS_ChangeOrAddSourceMeasurementReference]')) 
	AND (OBJECTPROPERTY(id, N'IsProcedure') = 1))
DROP PROCEDURE [dbo].[spIAS_ChangeOrAddSourceMeasurementReference]
GO

CREATE PROCEDURE spIAS_ChangeOrAddSourceMeasurementReference
	@SourceNamespace NVARCHAR(250),
	@OldSource NVARCHAR(200)=NULL,
	@NewSource NVARCHAR(200),
	@OldMeasurement NVARCHAR(1000)=NULL,
	@NewMeasurement NVARCHAR(1000),
	@Scheme NVARCHAR(10),
	@Segment1 NVARCHAR(400),
	@Segment2 NVARCHAR(400),
	@Segment3 NVARCHAR(400),
	@IsManual BIT,
	@IsDefault BIT,
	@UserName NVARCHAR(250),
	@TimeResolvedUTC DATETIME=NULL
AS

SET NOCOUNT ON

DECLARE @OldMeasurementID SMALLINT
DECLARE @NewMeasurementID SMALLINT
DECLARE @OldSourceID INT
DECLARE @NewSourceID INT
DECLARE @ServicesID TINYINT
DECLARE @UserID SMALLINT
SET @OldSourceID=NULL
SET @OldMeasurementID=NULL
SET @ServicesID=NULL
SET @UserID=NULL

IF @OldSource Is NOT NULL
BEGIN
  SELECT @OldSourceID=s.SourceID FROM SRC_Namespace AS n JOIN SRC_Source AS s ON n.Name=@SourceNamespace AND n.NamespaceID=s.NamespaceID AND s.Name=@OldSource
  IF @OldSourceID IS NULL
  BEGIN
   RAISERROR  ('Unknown Original Source ''%s@%s''', 16, 1, @OldSource, @SourceNamespace)
   RETURN -1
  END
END
ELSE
 BEGIN
  IF @OldMeasurement IS NOT NULL
  BEGIN
   RAISERROR ('If Original Source or Measurement is specified, the other must be as well ''%s@%s''',16,2, @OldSource, @OldMeasurement)
   RETURN -1
  END
 END
SELECT @NewSourceID=s.SourceID FROM SRC_Namespace AS n JOIN SRC_Source AS s ON n.Name=@SourceNamespace AND n.NamespaceID=s.NamespaceID AND s.Name=@NewSource

IF @NewSourceID IS NULL
BEGIN
  RAISERROR  ('Unknown New Source ''%s@%s''', 16, 3, @NewSource, @SourceNamespace)
  RETURN -1
END
IF @OldMeasurement IS NOT NULL
BEGIN
  EXEC spMM_GetIDByName_StandardFirst @OldMeasurement, @OldMeasurementID OUTPUT
  IF @OldMeasurementID=0 OR @OldMeasurementID IS NULL
  BEGIN
    RAISERROR  ('Unknown Original Measurement ''%s''', 16, 4, @OldMeasurement)
    RETURN -1
  END
END

EXEC spMM_GetIDByName_StandardFirst @NewMeasurement, @NewMeasurementID OUTPUT
IF @NewMeasurementID=0 OR @NewMeasurementID IS NULL
BEGIN
  RAISERROR  ('Unknown New Measurement ''%s''', 16, 5, @NewMeasurement)
  RETURN -1
END

SELECT @ServicesID=ID FROM IAS_Services WHERE Scheme=@Scheme
IF @ServicesID IS NULL
BEGIN
  RAISERROR  ('Unknown Scheme ''%s''', 16, 6, @Scheme)
  RETURN -1
END

EXEC spIAS_GetUserID @UserName, @UserID OUTPUT

-- Set TimeResolvedUTC only for manual mappings (see SCR 22808 or SCR 23704 for details).
IF @IsManual=1
	SET @TimeResolvedUTC=GETUTCDATE()

--Unique constraint should raise error if source and measurement id's are not unique
IF @OldSource Is NULL AND @OldMeasurement IS NULL
  INSERT INTO IAS_MeasurementAddress (SourceID,MeasurementID,Segment1,Segment2,Segment3,ServicesID,IsManual,IsDefault,UserID,TimeResolvedUTC) 
	VALUES(@NewSourceID,@NewMeasurementID,@Segment1,@Segment2,@Segment3,@ServicesID,@IsManual,@IsDefault,@UserID,@TimeResolvedUTC)
ELSE
BEGIN
 UPDATE IAS_MeasurementAddress SET Segment1=@Segment1, Segment2=@Segment2, Segment3=@Segment3, ServicesID=@ServicesID, IsManual=@IsManual, IsDefault=@IsDefault, UserID=@UserID, TimeResolvedUTC=@TimeResolvedUTC, SourceID=@NewSourceID, MeasurementID=@NewMeasurementID
	WHERE SourceID=@OldSourceID AND MeasurementID=@OldMeasurementID
 IF @@ROWCOUNT = 0
  BEGIN
   RAISERROR ('Entry not found based on Source and Measurement provided ''%s@%s''', 16, 7, @OldMeasurement, @OldSource)
  RETURN -1
 END
END

RETURN 0


GO

/************************************************************************************************************************	
	Modify spIAS_ChangeSourceMeasurementReference to set TimeResolvedUTC column of IAS_MeasurementAddress to UTC time
*************************************************************************************************************************/

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE (id = OBJECT_ID(N'[dbo].[spIAS_ChangeSourceMeasurementReference]')) 
	AND (OBJECTPROPERTY(id, N'IsProcedure') = 1))
DROP PROCEDURE [dbo].[spIAS_ChangeSourceMeasurementReference]
GO

CREATE PROCEDURE spIAS_ChangeSourceMeasurementReference
	@SourceNamespace NVARCHAR(250),
	@OldSource NVARCHAR(200),
	@NewSource NVARCHAR(200),
	@OldMeasurement NVARCHAR(1000),
	@NewMeasurement NVARCHAR(1000),
	@Scheme NVARCHAR(10),
	@UserName NVARCHAR(250)
AS

SET NOCOUNT ON

DECLARE @OldMeasurementID SMALLINT
DECLARE @NewMeasurementID SMALLINT
DECLARE @OldSourceID INT
DECLARE @NewSourceID INT
DECLARE @ServicesID TINYINT
DECLARE @UserID SMALLINT
SET @OldSourceID=NULL
SET @OldMeasurementID=NULL
SET @ServicesID=NULL
SET @UserID=NULL

SELECT @OldSourceID=s.SourceID FROM SRC_Namespace AS n JOIN SRC_Source AS s ON n.Name=@SourceNamespace AND n.NamespaceID=s.NamespaceID AND s.Name=@OldSource
IF @OldSourceID IS NULL
BEGIN
  RAISERROR  ('Unknown Original Source ''%s@%s''', 16, 1, @OldSource, @SourceNamespace)
  RETURN -1
END

SELECT @NewSourceID=s.SourceID FROM SRC_Namespace AS n JOIN SRC_Source AS s ON n.Name=@SourceNamespace AND n.NamespaceID=s.NamespaceID AND s.Name=@NewSource
IF @NewSourceID IS NULL
BEGIN
  RAISERROR  ('Unknown New Source ''%s@%s''', 16, 3, @NewSource, @SourceNamespace)
  RETURN -1
END

EXEC spMM_GetIDByName_StandardFirst @OldMeasurement, @OldMeasurementID OUTPUT
IF @OldMeasurementID=0 OR @OldMeasurementID IS NULL
BEGIN
  RAISERROR  ('Unknown Original Measurement ''%s''', 16, 4, @OldMeasurement)
  RETURN -1
END

EXEC spMM_GetIDByName_StandardFirst @NewMeasurement, @NewMeasurementID OUTPUT
IF @NewMeasurementID=0 OR @NewMeasurementID IS NULL
BEGIN
  RAISERROR  ('Unknown New Measurement ''%s''', 16, 5, @NewMeasurement)
  RETURN -1
END

SELECT @ServicesID=ID FROM IAS_Services WHERE Scheme=@Scheme
IF @ServicesID IS NULL
BEGIN
  RAISERROR  ('Unknown Scheme ''%s''', 16, 6, @Scheme)
  RETURN -1
END

EXEC spIAS_GetUserID @UserName, @UserID OUTPUT

--Unique constraint should raise error if source and measurement id's are not unique
 UPDATE IAS_MeasurementAddress SET TimeResolvedUTC=GETUTCDATE(), SourceID=@NewSourceID, MeasurementID=@NewMeasurementID, UserID=@UserID
	WHERE SourceID=@OldSourceID AND MeasurementID=@OldMeasurementID AND ServicesID=@ServicesID

IF @@ROWCOUNT = 0 AND @@ERROR = 0
BEGIN
 RAISERROR ('Entry not found based on Source and Measurement provided ''%s@%s''', 16, 7, @OldMeasurement, @OldSource)
 RETURN -1
END
ELSE
BEGIN
IF @@ERROR <> 0
RAISERROR('Error number = %s',16,8, @@ERROR)
END
RETURN 0

GO


/****************************************************************************************************************
-- Drop Reporting CLRs & stored procedures that were created in ION E 6.0 to support SQL Express
****************************************************************************************************************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE (id = OBJECT_ID(N'[dbo].[ReportView_SourceListArgumentDescription]')) 
	AND (OBJECTPROPERTY(id, N'IsProcedure') = 1))
DROP PROCEDURE [dbo].[ReportView_SourceListArgumentDescription]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE (id = OBJECT_ID(N'[dbo].[ReportView_ShiftListArgumentDescription]')) 
	AND (OBJECTPROPERTY(id, N'IsProcedure') = 1))
DROP PROCEDURE [dbo].[ReportView_ShiftListArgumentDescription]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE (id = OBJECT_ID(N'[dbo].[ReportView_PeriodOverPeriodArgumentDescription]')) 
	AND (OBJECTPROPERTY(id, N'IsProcedure') = 1))
DROP PROCEDURE [dbo].[ReportView_PeriodOverPeriodArgumentDescription]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE (id = OBJECT_ID(N'[dbo].[ReportView_MeasurementListArgumentDescription]')) 
	AND (OBJECTPROPERTY(id, N'IsProcedure') = 1))
DROP PROCEDURE [dbo].[ReportView_MeasurementListArgumentDescription]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE (id = OBJECT_ID(N'[dbo].[ReportView_EnergyCostArgumentDescription]')) 
	AND (OBJECTPROPERTY(id, N'IsProcedure') = 1))
DROP PROCEDURE [dbo].[ReportView_EnergyCostArgumentDescription]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE (id = OBJECT_ID(N'[dbo].[ReportView_EN50160ArgumentDescription]')) 
	AND (OBJECTPROPERTY(id, N'IsProcedure') = 1))
DROP PROCEDURE [dbo].[ReportView_EN50160ArgumentDescription]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE (id = OBJECT_ID(N'[dbo].[ReportView_DateRangeArgumentDescription]')) 
	AND (OBJECTPROPERTY(id, N'IsProcedure') = 1))
DROP PROCEDURE [dbo].[ReportView_DateRangeArgumentDescription]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE (id = OBJECT_ID(N'[dbo].[ReportView_PLSupplyVoltageUnbalanceData]')) 
	AND (OBJECTPROPERTY(id, N'IsProcedure') = 1))
DROP PROCEDURE [dbo].[ReportView_PLSupplyVoltageUnbalanceData]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE (id = OBJECT_ID(N'[dbo].[ReportView_PLSupplyVoltagesDipsData]')) 
	AND (OBJECTPROPERTY(id, N'IsProcedure') = 1))
DROP PROCEDURE [dbo].[ReportView_PLSupplyVoltagesDipsData]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE (id = OBJECT_ID(N'[dbo].[ReportView_PLSupplyVoltageMagnitudeData]')) 
	AND (OBJECTPROPERTY(id, N'IsProcedure') = 1))
DROP PROCEDURE [dbo].[ReportView_PLSupplyVoltageMagnitudeData]


IF EXISTS (SELECT * FROM dbo.sysobjects WHERE (id = OBJECT_ID(N'[dbo].[ReportView_PLPowerFrequencyData]')) 
	AND (OBJECTPROPERTY(id, N'IsProcedure') = 1))
DROP PROCEDURE [dbo].[ReportView_PLPowerFrequencyData]
GO


IF EXISTS (SELECT * FROM dbo.sysobjects WHERE (id = OBJECT_ID(N'[dbo].[ReportView_PLOvervoltagesData]')) 
	AND (OBJECTPROPERTY(id, N'IsProcedure') = 1))
DROP PROCEDURE [dbo].[ReportView_PLOvervoltagesData]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE (id = OBJECT_ID(N'[dbo].[ReportView_PLInterruptionsData]')) 
	AND (OBJECTPROPERTY(id, N'IsProcedure') = 1))
DROP PROCEDURE [dbo].[ReportView_PLInterruptionsData]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE (id = OBJECT_ID(N'[dbo].[ReportView_PLHarmonicVoltageData]')) 
	AND (OBJECTPROPERTY(id, N'IsProcedure') = 1))
DROP PROCEDURE [dbo].[ReportView_PLHarmonicVoltageData]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE (id = OBJECT_ID(N'[dbo].[ReportView_PLFlickerData]')) 
	AND (OBJECTPROPERTY(id, N'IsProcedure') = 1))
DROP PROCEDURE [dbo].[ReportView_PLFlickerData]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE (id = OBJECT_ID(N'[dbo].[ReportView_IONSupplyVoltageUnbalanceData]')) 
	AND (OBJECTPROPERTY(id, N'IsProcedure') = 1))
DROP PROCEDURE [dbo].[ReportView_IONSupplyVoltageUnbalanceData]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE (id = OBJECT_ID(N'[dbo].[ReportView_IONSupplyVoltagesDipsData]')) 
	AND (OBJECTPROPERTY(id, N'IsProcedure') = 1))
DROP PROCEDURE [dbo].[ReportView_IONSupplyVoltagesDipsData]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE (id = OBJECT_ID(N'[dbo].[ReportView_IONSupplyVoltageMagnitudeData]')) 
	AND (OBJECTPROPERTY(id, N'IsProcedure') = 1))
DROP PROCEDURE [dbo].[ReportView_IONSupplyVoltageMagnitudeData]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE (id = OBJECT_ID(N'[dbo].[ReportView_IONPowerFrequencyData]')) 
	AND (OBJECTPROPERTY(id, N'IsProcedure') = 1))
DROP PROCEDURE [dbo].[ReportView_IONPowerFrequencyData]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE (id = OBJECT_ID(N'[dbo].[ReportView_IONOvervoltagesData]')) 
	AND (OBJECTPROPERTY(id, N'IsProcedure') = 1))
DROP PROCEDURE [dbo].[ReportView_IONOvervoltagesData]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE (id = OBJECT_ID(N'[dbo].[ReportView_IONInterruptionsData]')) 
	AND (OBJECTPROPERTY(id, N'IsProcedure') = 1))
DROP PROCEDURE [dbo].[ReportView_IONInterruptionsData]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE (id = OBJECT_ID(N'[dbo].[ReportView_IONInterharmonicVoltageData]')) 
	AND (OBJECTPROPERTY(id, N'IsProcedure') = 1))
DROP PROCEDURE [dbo].[ReportView_IONInterharmonicVoltageData]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE (id = OBJECT_ID(N'[dbo].[ReportView_IONHarmonicVoltageData]')) 
	AND (OBJECTPROPERTY(id, N'IsProcedure') = 1))
DROP PROCEDURE [dbo].[ReportView_IONHarmonicVoltageData]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE (id = OBJECT_ID(N'[dbo].[ReportView_IONFlickerData]')) 
	AND (OBJECTPROPERTY(id, N'IsProcedure') = 1))
DROP PROCEDURE [dbo].[ReportView_IONFlickerData]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE (id = OBJECT_ID(N'[dbo].[ReportView_PQWorstDisturbance]')) 
	AND (OBJECTPROPERTY(id, N'IsProcedure') = 1))
DROP PROCEDURE [dbo].[ReportView_PQWorstDisturbance]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE (id = OBJECT_ID(N'[dbo].[ReportView_PQWaveformData]')) 
	AND (OBJECTPROPERTY(id, N'IsProcedure') = 1))
DROP PROCEDURE [dbo].[ReportView_PQWaveformData]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE (id = OBJECT_ID(N'[dbo].[ReportView_PQIncidentData]')) 
	AND (OBJECTPROPERTY(id, N'IsProcedure') = 1))
DROP PROCEDURE [dbo].[ReportView_PQIncidentData]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE (id = OBJECT_ID(N'[dbo].[ReportView_PQEventData]')) 
	AND (OBJECTPROPERTY(id, N'IsProcedure') = 1))
DROP PROCEDURE [dbo].[ReportView_PQEventData]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE (id = OBJECT_ID(N'[dbo].[ReportView_PQConfiguration]')) 
	AND (OBJECTPROPERTY(id, N'IsProcedure') = 1))
DROP PROCEDURE [dbo].[ReportView_PQConfiguration]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE (id = OBJECT_ID(N'[dbo].[ReportView_IEC61000_4_30]')) 
	AND (OBJECTPROPERTY(id, N'IsProcedure') = 1))
DROP PROCEDURE [dbo].[ReportView_IEC61000_4_30]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE (id = OBJECT_ID(N'[dbo].[ReportView_EN50160Summary]')) 
	AND (OBJECTPROPERTY(id, N'IsProcedure') = 1))
DROP PROCEDURE [dbo].[ReportView_EN50160Summary]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE (id = OBJECT_ID(N'[dbo].[ReportView_EN50160MainsSignaling]')) 
	AND (OBJECTPROPERTY(id, N'IsProcedure') = 1))
DROP PROCEDURE [dbo].[ReportView_EN50160MainsSignaling]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE (id = OBJECT_ID(N'[dbo].[ReportView_WagesCost]')) 
	AND (OBJECTPROPERTY(id, N'IsProcedure') = 1))
DROP PROCEDURE [dbo].[ReportView_WagesCost]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE (id = OBJECT_ID(N'[dbo].[ReportView_TabularData]')) 
	AND (OBJECTPROPERTY(id, N'IsProcedure') = 1))
DROP PROCEDURE [dbo].[ReportView_TabularData]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE (id = OBJECT_ID(N'[dbo].[ReportView_DeviceStatus]')) 
	AND (OBJECTPROPERTY(id, N'IsProcedure') = 1))
DROP PROCEDURE [dbo].[ReportView_DeviceStatus]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE (id = OBJECT_ID(N'[dbo].[ReportView_ShiftEnergy]')) 
	AND (OBJECTPROPERTY(id, N'IsProcedure') = 1))
DROP PROCEDURE [dbo].[ReportView_ShiftEnergy]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE (id = OBJECT_ID(N'[dbo].[ReportView_PeriodOverPeriod]')) 
	AND (OBJECTPROPERTY(id, N'IsProcedure') = 1))
DROP PROCEDURE [dbo].[ReportView_PeriodOverPeriod]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE (id = OBJECT_ID(N'[dbo].[ReportView_LoadProfileSummary]')) 
	AND (OBJECTPROPERTY(id, N'IsProcedure') = 1))
DROP PROCEDURE [dbo].[ReportView_LoadProfileSummary]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE (id = OBJECT_ID(N'[dbo].[ReportView_LoadProfile]')) 
	AND (OBJECTPROPERTY(id, N'IsProcedure') = 1))
DROP PROCEDURE [dbo].[ReportView_LoadProfile]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE (id = OBJECT_ID(N'[dbo].[ReportView_HundredMs]')) 
	AND (OBJECTPROPERTY(id, N'IsProcedure') = 1))
DROP PROCEDURE [dbo].[ReportView_HundredMs]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE (id = OBJECT_ID(N'[dbo].[ReportView_EnergyAndDemandCost]')) 
	AND (OBJECTPROPERTY(id, N'IsProcedure') = 1))
DROP PROCEDURE [dbo].[ReportView_EnergyAndDemandCost]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE (id = OBJECT_ID(N'[dbo].[ReportView_AlarmHistory]')) 
	AND (OBJECTPROPERTY(id, N'IsProcedure') = 1))
DROP PROCEDURE [dbo].[ReportView_AlarmHistory]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE (id = OBJECT_ID(N'[dbo].[ReportView_Version]')) 
	AND (OBJECTPROPERTY(id, N'IsProcedure') = 1))
DROP PROCEDURE [dbo].[ReportView_Version]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE (id = OBJECT_ID(N'[dbo].[ReportView_SourceHierarchy]')) 
	AND (OBJECTPROPERTY(id, N'IsProcedure') = 1))
DROP PROCEDURE [dbo].[ReportView_SourceHierarchy]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE (id = OBJECT_ID(N'[dbo].[ReportView_SourceHierarchyList]')) 
	AND (OBJECTPROPERTY(id, N'IsProcedure') = 1))
DROP PROCEDURE [dbo].[ReportView_SourceHierarchyList]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE (id = OBJECT_ID(N'[dbo].[ReportView_Messages]')) 
	AND (OBJECTPROPERTY(id, N'IsProcedure') = 1))
DROP PROCEDURE [dbo].[ReportView_Messages]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE (id = OBJECT_ID(N'[dbo].[ReportView_MeasurementHierarchy]')) 
	AND (OBJECTPROPERTY(id, N'IsProcedure') = 1))
DROP PROCEDURE [dbo].[ReportView_MeasurementHierarchy]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE (id = OBJECT_ID(N'[dbo].[ReportView_MeasurementHierarchyList]')) 
	AND (OBJECTPROPERTY(id, N'IsProcedure') = 1))
DROP PROCEDURE [dbo].[ReportView_MeasurementHierarchyList]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE (id = OBJECT_ID(N'[dbo].[ReportView_Culture]')) 
	AND (OBJECTPROPERTY(id, N'IsProcedure') = 1))
DROP PROCEDURE [dbo].[ReportView_Culture]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE (id = OBJECT_ID(N'[dbo].[ReportView_Heartbeat]')) 
	AND (OBJECTPROPERTY(id, N'IsProcedure') = 1))
DROP PROCEDURE [dbo].[ReportView_Heartbeat]
GO


if exists (select name from sys.assemblies where name = 'Reporting.View.ControllerProxy.XmlSerializers')
BEGIN
	BEGIN TRY
		DROP ASSEMBLY [Reporting.View.ControllerProxy.XmlSerializers] 
	END TRY
	BEGIN CATCH
		PRINT 'Warning: Failed to drop an assembly. ' + ERROR_MESSAGE()
	END CATCH
END
GO
if exists (select name from sys.assemblies where name = 'Reporting.PowerQuality.Sql')
BEGIN
	BEGIN TRY
		DROP ASSEMBLY [Reporting.PowerQuality.Sql]
	END TRY
	BEGIN CATCH
		PRINT 'Warning: Failed to drop an assembly. ' + ERROR_MESSAGE()
	END CATCH
END
GO

if exists (select name from sys.assemblies where name = 'Reporting.PowerQuality.Definition') 
BEGIN
	BEGIN TRY
		DROP ASSEMBLY [Reporting.PowerQuality.Definition]
	END TRY
	BEGIN CATCH
		PRINT 'Warning: Failed to drop an assembly. ' + ERROR_MESSAGE()
	END CATCH
END
GO

if exists (select name from sys.assemblies where name = 'Reporting.View.Sql')
BEGIN
	BEGIN TRY
		DROP ASSEMBLY [Reporting.View.Sql] 
	END TRY
	BEGIN CATCH
		PRINT 'Warning: Failed to drop an assembly. ' + ERROR_MESSAGE()
	END CATCH
END
GO

if exists (select name from sys.assemblies where name = 'Reporting.View.Definition')
BEGIN
	BEGIN TRY
		DROP ASSEMBLY [Reporting.View.Definition] 
	END TRY
	BEGIN CATCH
		PRINT 'Warning: Failed to drop an assembly. ' + ERROR_MESSAGE()
	END CATCH
END
GO

if exists (select name from sys.assemblies where name = 'Reporting.Sql')
BEGIN
	BEGIN TRY
		DROP ASSEMBLY [Reporting.Sql] 
	END TRY
	BEGIN CATCH
		PRINT 'Warning: Failed to drop an assembly. ' + ERROR_MESSAGE()
	END CATCH
END
GO

if exists (select name from sys.assemblies where name = 'Reporting.View.ControllerProxy') 
BEGIN
	BEGIN TRY
		DROP ASSEMBLY [Reporting.View.ControllerProxy]
	END TRY
	BEGIN CATCH
		PRINT 'Warning: Failed to drop an assembly. ' + ERROR_MESSAGE()
	END CATCH
END
GO

if exists (select name from sys.assemblies where name = 'Reporting.View.Argument')
BEGIN
	BEGIN TRY
		DROP ASSEMBLY [Reporting.View.Argument]
	END TRY
	BEGIN CATCH
		PRINT 'Warning: Failed to drop an assembly. ' + ERROR_MESSAGE()
	END CATCH
END
GO

if exists (select name from sys.assemblies where name = 'Reporting.Attribute')
BEGIN
	BEGIN TRY
		DROP ASSEMBLY [Reporting.Attribute]
	END TRY
	BEGIN CATCH
		PRINT 'Warning: Failed to drop an assembly. ' + ERROR_MESSAGE()
	END CATCH
END
GO

if exists (select name from sys.assemblies where name = 'Configuration')
BEGIN
	BEGIN TRY
		DROP ASSEMBLY [Configuration]
	END TRY
	BEGIN CATCH
		PRINT 'Warning: Failed to drop an assembly. ' + ERROR_MESSAGE()
	END CATCH
END
GO

/****************************************************************************************************************
-- Change the DeviceType.UnsolicitedTickPeriod_ms from smallint to int
****************************************************************************************************************/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_DeviceType_UnsolicitedTickPeriod_ms]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[DeviceType] DROP CONSTRAINT [DF_DeviceType_UnsolicitedTickPeriod_ms]
END
GO

ALTER TABLE [DeviceType]
ALTER COLUMN [UnsolicitedTickPeriod_ms] int not null

ALTER TABLE [dbo].[DeviceType] ADD  CONSTRAINT [DF_DeviceType_UnsolicitedTickPeriod_ms]  DEFAULT ((0)) FOR [UnsolicitedTickPeriod_ms]
GO

-- existence check for Notes column
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='SRC_SOurceType' AND COLUMN_NAME = 'Notes')
/******************************************************************************************************************
-- Add new column to the SRC_Source type table
*******************************************************************************************************************/
ALTER TABLE SRC_SourceType
ADD
Notes  NVARCHAR(1000)  NULL;

/****************************************************************************************************************
-- Change the Default values of Offline Timeout Period for serial and sepam devices from 30 to 600 seconds scr 22476
****************************************************************************************************************/

UPDATE EntityPropertyMap
SET PegasysDefault = 600000
WHERE DisplayNameKey like '%SerialDeviceOfflineTimeoutPeriod%'

GO

/****************************************************************************************************************
-- Change the values of Offline Timeout Period for existing serial and sepam devices from 30 to 600 seconds scr 22476
****************************************************************************************************************/

UPDATE Device
SET OfflineTimeoutPeriod = 600000
WHERE
(OfflineTimeoutPeriod=30000 and EntityClassID in (1,24))

GO

/****************************************************************************************************************
-- Change the Default values of Offline Timeout Period for serial and sepam devices from 30 to 600 seconds scr 22476
****************************************************************************************************************/

UPDATE EntityPropertyMap
SET PegasysDefault = 600000
WHERE DisplayNameKey like '%SerialDeviceOfflineTimeoutPeriod%'

GO

/****************************************************************************************************************
-- Change the values of Offline Timeout Period for existing serial and sepam devices from 30 to 600 seconds scr 22476
****************************************************************************************************************/

UPDATE Device
SET OfflineTimeoutPeriod = 600000
WHERE
(OfflineTimeoutPeriod=30000 and EntityClassID in (1,24))

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.'

GO
