------------------------------------------------------------------------------
--
-- This file is part of the ActiveFactory Software.
--
-- Copyright(c) 2005 Invensys Systems Inc. All rights reserved.
--
-- This source code is intended only as a supplement to ActiveFactory Software
-- and/or on-line documentation. See these other
-- materials for detailed information regarding ActiveFactory Software.
--
-- THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
-- KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
-- IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
-- PARTICULAR PURPOSE.
------------------------------------------------------------------------------
--
-- This script is intended for use with a clean InSQL 9.0 database.
--
-- NOTE: This is not a native T-SQL script!
-- Caller must search/replace '<<SourceDB>>' with old InSQL database name
-- and '<<DestinationDB>>' with new database before executing this script

USE [<<DestinationDB>>]

-- Ensure tables do not exist in the destination.
if exists (select * from [<<DestinationDB>>].dbo.sysobjects where name = N'aaHistClientReport'
            and xtype='U')
  DROP TABLE [<<DestinationDB>>].dbo.[aaHistClientReport]

if exists (select * from [<<DestinationDB>>].dbo.sysobjects where name = N'aaHistClientReportsFolder'
            and xtype='U')
  DROP TABLE [<<DestinationDB>>].dbo.[aaHistClientReportsFolder]

if exists (select * from [<<DestinationDB>>].dbo.sysobjects where name = N'aaHistClientReportSite'
            and xtype='U')
  DROP TABLE [<<DestinationDB>>].dbo.[aaHistClientReportSite]


/**********  dbo.aaHistClientReportSite **********/
DECLARE @aaHistClientSchemaVersion int
SELECT @aaHistClientSchemaVersion = CONVERT(int, Value) FROM [<<SourceDB>>].dbo.[SystemParameter]
	WHERE	Name = 'aaHistClientSchemaVersion'

if exists (select * from [<<SourceDB>>].dbo.sysobjects where name = N'aaHistClientReportSite'
            and xtype='U' and @aaHistClientSchemaVersion = 3)
BEGIN
	CREATE TABLE dbo.[aaHistClientReportSite] (
		[ReportSiteKey] [int] IDENTITY (1, 1) NOT NULL ,
		[SiteURL] [nvarchar] (255) NOT NULL ,
		[SitePath] [nvarchar] (255) NOT NULL ,
		[Description] [nvarchar] (255) NULL ,
		CONSTRAINT [PK_aaHistClientReportSite] PRIMARY KEY  NONCLUSTERED 
		(
			[ReportSiteKey]
		)  ON [PRIMARY] ,
		CONSTRAINT [IX_aaHistClientReportSite] UNIQUE  CLUSTERED 
		(
			[ReportSiteKey]
		)  ON [PRIMARY] 
	) ON [PRIMARY]

	GRANT SELECT,INSERT,UPDATE,DELETE ON dbo.[aaHistClientReportSite] TO aaAdministrators, aaPowerUsers
	GRANT SELECT ON dbo.[aaHistClientReportSite] TO public
	GRANT SELECT ON dbo.[aaHistClientReportSite] TO aaUsers

	SET IDENTITY_INSERT dbo.[aaHistClientReportSite] ON

	INSERT INTO dbo.[aaHistClientReportSite]
	(
		[ReportSiteKey],
		[SiteURL],
		[SitePath],
		[Description]
	)
	SELECT	[ReportSiteKey],
			[SiteURL],
			[SitePath],
			[Description]
		FROM	[<<SourceDB>>].dbo.[aaHistClientReportSite]

	SET IDENTITY_INSERT dbo.[aaHistClientReportSite] OFF

END
GO


/************* dbo.aaHistClientReportsFolder *****************/
DECLARE @aaHistClientSchemaVersion int
SELECT @aaHistClientSchemaVersion = CONVERT(int, Value) FROM [<<SourceDB>>].dbo.[SystemParameter]
	WHERE	Name = 'aaHistClientSchemaVersion'

if exists (select * from [<<SourceDB>>].dbo.sysobjects where name = N'aaHistClientReportsFolder'
            and xtype='U' and @aaHistClientSchemaVersion = 3)
BEGIN
	CREATE TABLE dbo.[aaHistClientReportsFolder] (
		[ReportSiteKey] [int] NOT NULL ,
		[ReportFolderKey] [int] IDENTITY (1, 1) NOT NULL ,
		[ReportFolderName] [nvarchar] (255) NULL ,
		[Description] [nvarchar] (255) NULL ,
		[ReportType] [tinyint] NOT NULL ,
		CONSTRAINT [PK_aaHistClientReportsFolder] PRIMARY KEY  NONCLUSTERED 
		(
			[ReportFolderKey]
		)  ON [PRIMARY] ,
		CONSTRAINT [IX_aaHistClientReportsFolder] UNIQUE  CLUSTERED 
		(
			[ReportFolderKey]
		)  ON [PRIMARY] ,
		CONSTRAINT [FK_aaHistClientReportsFolder_aaHistClientReportSite] FOREIGN KEY 
		(
			[ReportSiteKey]
		) REFERENCES dbo.[aaHistClientReportSite] (
			[ReportSiteKey]
		) ON DELETE CASCADE  ON UPDATE CASCADE 
	) ON [PRIMARY]

	GRANT SELECT,INSERT,UPDATE,DELETE ON dbo.[aaHistClientReportsFolder] TO aaAdministrators, aaPowerUsers, aaUsers
	GRANT SELECT ON dbo.[aaHistClientReportsFolder] TO public

	SET IDENTITY_INSERT dbo.[aaHistClientReportsFolder] ON

	INSERT INTO dbo.[aaHistClientReportsFolder]
	(
		[ReportSiteKey],
		[ReportFolderKey],
		[ReportFolderName],
		[Description],
		[ReportType]
	)
	SELECT	[ReportSiteKey],
			[ReportFolderKey],
			[ReportFolderName],
			[Description],
			[ReportType]
		FROM	[<<SourceDB>>].dbo.[aaHistClientReportsFolder]

	SET IDENTITY_INSERT dbo.[aaHistClientReportsFolder] OFF

END
GO

/************* dbo.aaHistClientReport *****************/
DECLARE @aaHistClientSchemaVersion int
SELECT @aaHistClientSchemaVersion = CONVERT(int, Value) FROM [<<SourceDB>>].dbo.[SystemParameter]
	WHERE	Name = 'aaHistClientSchemaVersion'

if exists (select * from [<<SourceDB>>].dbo.sysobjects where name = N'aaHistClientReport'
            and xtype='U' and @aaHistClientSchemaVersion = 3)
BEGIN
  CREATE TABLE dbo.[aaHistClientReport] (
  	[ReportKey] [int] IDENTITY (1, 1) NOT NULL ,
	  [ReportSiteKey] [int] NOT NULL ,
  	[ReportFolderKey] [int] NOT NULL ,
	  [Name] [nvarchar] (255) NOT NULL ,
	  [ReportType] [tinyint] NOT NULL CONSTRAINT [DF_aaHistClientReport_ReportType] DEFAULT (0),
  	[FileName] [nvarchar] (255) NOT NULL ,
  	[Description] [nvarchar] (255) NULL ,
	  [ApplicationType] [int] NULL ,
  	[LastRun] [datetime] NOT NULL CONSTRAINT [DF_aaHistClientReport_LastRun] DEFAULT (0),
	  [ReportData] [image] NOT NULL ,
  	[Published] [int] NOT NULL ,
	  [LockdownOptions] [nvarchar] (1024) NULL ,
  	CONSTRAINT [PK_aaHistClientReport] PRIMARY KEY  CLUSTERED 
	  (
  		[ReportKey]
	  )  ON [PRIMARY] ,
  	CONSTRAINT [FK_aaHistClientReport_aaHistClientReportsFolder] FOREIGN KEY 
	  (
  		[ReportFolderKey]
	  ) REFERENCES dbo.[aaHistClientReportsFolder] (
  		[ReportFolderKey]
  	),
  	CONSTRAINT [FK_aaHistClientReport_aaHistClientReportSite] FOREIGN KEY 
	  (
  		[ReportSiteKey]
	  ) REFERENCES dbo.[aaHistClientReportSite] (
  		[ReportSiteKey]
	  ) ON DELETE CASCADE  ON UPDATE CASCADE 
	) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

	GRANT SELECT,INSERT,UPDATE,DELETE ON dbo.[aaHistClientReport] TO aaAdministrators, aaPowerUsers, aaUsers
	GRANT SELECT ON dbo.[aaHistClientReport] TO public

	SET IDENTITY_INSERT dbo.[aaHistClientReport] ON

	INSERT INTO dbo.[aaHistClientReport]
	  (
		  [ReportKey],
  		[ReportSiteKey],
	  	[ReportFolderKey],
		  [Name],
  		[ReportType],
	  	[FileName],
		  [Description],
  		[ApplicationType],
	  	[LastRun],
		  [ReportData],
  		[Published],
	  	[LockdownOptions]
  	)
  	SELECT	[ReportKey],
	  		[ReportSiteKey],
		  	[ReportFolderKey],
			  [Name],
  			[ReportType],
	  		[FileName],
		  	[Description],
			  [ApplicationType],
  			[LastRun],
	  		[ReportData],
		  	[Published],
			  [LockdownOptions]
  		FROM	[<<SourceDB>>].dbo.[aaHistClientReport]

	SET IDENTITY_INSERT dbo.[aaHistClientReport] OFF

END
GO

/* additional procedures needed for the Reporting Web Site */

/*************************************************/
/**********  dbo.aaDiscreteTimeInState  **********/
IF EXISTS (SELECT * FROM [<<DestinationDB>>].dbo.sysobjects WHERE name= N'aaDiscreteTimeInState'
            AND xtype='P')
  DROP PROCEDURE dbo.aaDiscreteTimeInState
GO

CREATE PROCEDURE dbo.[aaDiscreteTimeInState]
  @Start DateTime, 
  @End DateTime, 
  @TagName varchar(256)
AS

SET NOCOUNT ON

DECLARE @OffOccurrences int
DECLARE @OnOccurrences int
DECLARE @LatestValue int
DECLARE @LatestDateTime DateTime

--Create a table of the history data
CREATE TABLE #DiscreteHistoryData (dt datetime, value int)
INSERT #DiscreteHistoryData
 SELECT DateTime, Value
  FROM (SELECT DateTime, Value
  	FROM v_DiscreteHistory dh
  	WHERE dh.TagName in (@TagName)
  	AND wwRetrievalMode = 'Delta'
	AND wwRowCount = 100000
  	AND DateTime >= @Start
  	AND DateTime <= @End   	
  	) dhd
  ORDER BY DateTime

SELECT @OffOccurrences = COUNT(*) FROM #DiscreteHistoryData WHERE value = 0
SELECT @OnOccurrences = COUNT(*) FROM #DiscreteHistoryData WHERE value = 1

SELECT top 1 @LatestDateTime=dt,@LatestValue=value FROM #DiscreteHistoryData WHERE dt <= @End ORDER BY dt DESC
IF (SELECT @LatestDateTime)<@End
BEGIN
	INSERT #DiscreteHistoryData VALUES(
	@End,
	case @LatestValue
	when 1 then 0
	when 0 then 1
	end)
END

--Calculate time in On state
CREATE TABLE #OnState (Duration real)
INSERT #OnState
SELECT 
	(SELECT top 1 min(datediff(ms, a.dt, v.dt))
	FROM #DiscreteHistoryData v cross join #DiscreteHistoryData a 
	WHERE (a.dt between v.dt-1 AND v.dt)
	AND v.value = 0
	AND datediff(s, a.dt, v.dt) <> 0
	AND v.dt = b.dt
	GROUP BY v.dt, a.dt 
	ORDER BY v.dt, a.dt DESC) AS 'Time in State 1 (s)'
FROM #DiscreteHistoryData b 
WHERE b.value = 0

--Calculate time in Off state
CREATE TABLE #OffState (Duration real)
INSERT #OffState
SELECT 
	(SELECT top 1 min(datediff(ms, a.dt, v.dt))
	FROM #DiscreteHistoryData v cross join #DiscreteHistoryData a 
	WHERE (a.dt between v.dt-1 AND v.dt)
	AND v.value = 1
	AND datediff(s, a.dt, v.dt) <> 0
	AND v.dt = b.dt
	GROUP BY v.dt, a.dt 
	ORDER BY v.dt, a.dt DESC) AS 'Time in State 0 (s)'
FROM #DiscreteHistoryData b 
WHERE b.value = 1

--Number of rows affected by a Transact-SQL statement is returned by setting NOCOUNT to off
SET NOCOUNT OFF

SELECT State, Duration1 AS 'Time In States', Duration2 AS 'Time In State Percent ', Changes AS 'Number Of State Changes'
FROM (
	Select '0' AS State, isnull(convert(decimal(38,2),Sum(Duration)/1000),0.0) AS Duration1, isnull(convert(decimal(38, 2),Sum(Duration)/10/datediff(s,@Start, @End)),0.0) AS Duration2, @OffOccurrences AS 'Changes'
	FROM #OffState
	WHERE Duration is not Null
UNION ALL
	Select '1' AS State, isnull(convert(decimal(38,2),Sum(Duration)/1000),0.0) AS Duration1, isnull(convert(decimal(38, 2),Sum(Duration)/10/datediff(s,@Start, @End)),0.0) AS Duration2, @OnOccurrences AS 'Changes'
	FROM #OnState
	WHERE Duration is not Null
) p

drop table #DiscreteHistoryData
drop table #OnState
drop table #OffState
GO
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON

GRANT EXEC ON dbo.[aaDiscreteTimeInState] TO aaAdministrators, aaPowerUsers, aaUsers
GO

IF NOT EXISTS (SELECT * FROM [<<SourceDB>>].dbo.sysobjects WHERE name= N'aaDiscreteTimeInState'
            AND xtype='P')
  DROP PROCEDURE dbo.aaDiscreteTimeInState
GO

/*************************************************/
/************  dbo.aaManualTagInsert  ************/
IF EXISTS (SELECT * FROM [<<DestinationDB>>].dbo.sysobjects WHERE name= N'aaManualTagInsert'
            AND xtype='P')
  DROP PROCEDURE dbo.aaManualTagInsert
GO

/*************************************************
 The return code will be zero if the tag already
 exists with the given type and so was updated
 or wwTagKey if the tag was created.
 It may have existed with a different type
 and so was deleted and recreated.
**************************************************/
CREATE PROCEDURE dbo.aaManualTagInsert 
  @TagName       nvarchar(256),
  @TagType       int = 1
AS
  DECLARE @TagKey int
  IF @TagType = 1
    EXEC @TagKey=aaInternalMDASAnalogTagInsert @TagName=@TagName
  ELSE IF @TagType = 2
    EXEC @TagKey=aaInternalMDASDiscreteTagInsert @TagName=@TagName
  ELSE IF @TagType = 3
    EXEC @TagKey=aaInternalMDASStringTagInsert @TagName=@TagName
  ELSE BEGIN
    SET @TagKey=0
    RAISERROR('TagType must be 1, 2, or 3',16,1)
  END
  RETURN @TagKey
GO

GRANT  EXECUTE  ON dbo.aaManualTagInsert  TO aaPowerUsers
GO
GRANT  EXECUTE  ON dbo.aaManualTagInsert  TO aaAdministrators
GO
 
IF NOT EXISTS (SELECT * FROM [<<SourceDB>>].dbo.sysobjects WHERE name= N'aaManualTagInsert'
            AND xtype='P')
  DROP PROCEDURE dbo.aaManualTagInsert
GO

/********************************************************/
/**********  dbo.aaManualTagSingleValueInsert  **********/
IF EXISTS (SELECT * FROM [<<DestinationDB>>].dbo.sysobjects WHERE name= N'aaManualTagSingleValueInsert'
            AND xtype='P')
  DROP PROCEDURE dbo.aaManualTagSingleValueInsert
GO

/*********************************************
 The error code is returned.  In practice this
 is zero because any error will terminate the
 procedure.
**********************************************/
CREATE PROCEDURE dbo.aaManualTagSingleValueInsert 
  @TagName         nvarchar(256),
  @Value           nvarchar(512),
  @DateTime        datetime = NULL,
  @QualityDetail   int = 192,
  @Version         varchar(16) = 'latest',
  @TagType         int = 1   

AS
  IF @DateTime IS NULL SET @DateTime=GetDate()

  IF @TagType = 1
    INSERT AnalogHistory (TagName, Value, DateTime, QualityDetail, wwVersion) 
      VALUES (@TagName, CONVERT(float, @Value), @DateTime, @QualityDetail, @Version)
  ELSE IF @TagType=2
    INSERT DiscreteHistory (TagName, Value, DateTime, QualityDetail, wwVersion) 
      VALUES (@TagName, CONVERT(float, @Value), @DateTime, @QualityDetail, @Version)
  ELSE IF @TagType=3
    INSERT StringHistory (TagName, Value, DateTime, QualityDetail, wwVersion) 
      VALUES (@TagName, @Value, @DateTime, @QualityDetail, @Version)
  ELSE
    RAISERROR('TagType must be 1, 2, or 3',16,1)

  RETURN @@ERROR
GO

GRANT  EXECUTE  ON dbo.aaManualTagSingleValueInsert  TO aaPowerUsers
GO
GRANT  EXECUTE  ON dbo.aaManualTagSingleValueInsert  TO aaAdministrators
GO

IF NOT EXISTS (SELECT * FROM [<<SourceDB>>].dbo.sysobjects WHERE name= N'aaManualTagSingleValueInsert'
            AND xtype='P')
  DROP PROCEDURE dbo.aaManualTagSingleValueInsert
GO


if not exists (SELECT * FROM dbo.[SystemParameter] WHERE Name = 'aaHistClientSchemaVersion')
  INSERT INTO dbo.SystemParameter (Name, Value, Description)
    SELECT Name, CONVERT(int, Value), Description FROM [<<SourceDB>>].dbo.[SystemParameter]
      WHERE Name = 'aaHistClientSchemaVersion'
GO

