-- change index on IAS_MeasurementAddress
-- drop original index
if exists (select * from sysindexes where name='ixIAS_MeasurementAddress_SIDMIDSvID')
DROP INDEX IAS_MeasurementAddress.ixIAS_MeasurementAddress_SIDMIDSvID
GO

if not exists (select * from sysindexes where name='ixIAS_MeasurementAddress_SIDMID')
CREATE  UNIQUE  INDEX [ixIAS_MeasurementAddress_SIDMID] ON [dbo].[IAS_MeasurementAddress]([SourceID], [MeasurementID]) ON [PRIMARY]
GO



-- update spIAS_FindMeasurementsByAddress

if exists (select * from sysobjects where name='spIAS_FindMeasurementsByAddress' and  xtype = 'P')
DROP PROCEDURE spIAS_FindMeasurementsByAddress
GO

/****** Object:  StoredProcedure [dbo].[spIAS_FindMeasurementsByAddress]    Script Date: 08/12/2009 15:30:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[spIAS_FindMeasurementsByAddress]
	@Scheme NVARCHAR(10),
	@Segment1 NVARCHAR(400),
	@Segment2 NVARCHAR(400),
	@Segment3 NVARCHAR(400)
AS

SET NOCOUNT ON

SELECT  n.Name AS Namespace, s.Name AS Source, t.Name AS SourceType, m.StandardName AS Measurement, 
	a.IsManual AS IsManual, a.IsDefault AS IsDefault, u.Name AS [User], a.TimeResolvedUTC AS TimeResolvedUTC,
	a.Segment1 AS Segment1, a.Segment2 AS Segment2, a.Segment3 AS Segment3
	FROM  dbo.IAS_MeasurementAddress a 
		INNER JOIN dbo.SRC_Source s ON a.SourceID = s.SourceID 
			AND (@Segment1 IS NULL OR a.Segment1=@Segment1)
			AND (@Segment2 IS NULL OR a.Segment2=@Segment2)
			AND (@Segment3 IS NULL OR a.Segment3=@Segment3)
		INNER JOIN dbo.MM_MeasurementMap m ON a.MeasurementID = m.ID
		INNER JOIN dbo.IAS_Services v ON a.ServicesID = v.ID AND v.Scheme=@Scheme
		INNER JOIN dbo.SRC_Namespace n ON s.NamespaceID = n.NamespaceID
		INNER JOIN dbo.SRC_SourceCategory t ON s.SourceCategoryID = t.SourceCategoryID
		INNER JOIN dbo.IAS_User u ON a.UserID = u.ID

RETURN
GO

-- update to devicetype table to change buffersize for 6200/9200
UPDATE DeviceType SET IONBufferSize_bytes = 4096
WHERE Type = '6200' OR Type = '9200'
GO
        

/******************************************************************************
--20391 - SW_ION_Enterprise: 'Reports: can't save reports with the same name to 
--a different directory in Web Reporter'
******************************************************************************/


if exists (select * from sysobjects where name='spRPT_Report_GetReportID' and  xtype = 'P')
DROP PROCEDURE spRPT_Report_GetReportID
GO

--*****************************************************************************
/* ##BD_BEGIN

<P><I>For more information, follow the Source link at the top of the page.</I></P>
##BD_END */
--
-- $Id: //rdsw/dev/cayman/SW_VB6/business/MSDEUpgrader/scripts/NOM/NOM12pt1_Upgrade.sql#1 $
-- 
--*****************************************************************************
CREATE PROCEDURE dbo.spRPT_Report_GetReportID
(
	@ReportName nvarchar (50),
	@Subfolder nvarchar(30),
	@aUserID    UserID,
	@aReportType TINYINT
)
AS
Select
	ReportID
FROM 
	RPT_Report
WHERE
	DisplayName = @ReportName 
AND 
	SubFolder = @Subfolder
AND
	dbo.udfRPT_GetOwnerUserID(ReportID) = @aUserID
AND
	ReportType = @aReportType

GO
/******************************************************************************
--END: 20391
******************************************************************************/

/******************************************************************************
-- SCR#20432 - SW_ION_Enterprise: 'PQ Report: Waveform detection threshold is hard-coded'
******************************************************************************/
IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[tmpAddSysPropItem]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) 
DROP PROCEDURE [dbo].[tmpAddSysPropItem]
GO
--*****************************************************************************
CREATE PROCEDURE dbo.tmpAddSysPropItem
( 
	@module 				NVARCHAR(500),
	@category 				NVARCHAR(500),
	@item 					NVARCHAR(50),
	@value 					NVARCHAR(1000),
	@question 				NVARCHAR(500),
	@typeCode 				INT,
	@description 			TEXT,
	@listID 				INT = NULL,
	@sortOrder 				INT = NULL,
	@accessorNamespace 		NVARCHAR(500) = NULL,
	@accessorClassName  	NVARCHAR(500) = NULL,
	@accessorAssemblyName	NVARCHAR(500) = NULL,
	@userControlName  		NVARCHAR(500) = NULL
)
AS 
-- Determine module ID
DECLARE @moduleID BIGINT
SELECT @moduleID = ModuleID FROM dbo.CFG_Module
WHERE Description = @module
IF (@moduleID IS NULL) 
BEGIN
	INSERT INTO dbo.CFG_Module 
		(Description, Enabled) 
	VALUES 
		(@module, 1)	-- the '1' enables the module
	SET @moduleID = SCOPE_IDENTITY()
END
-- Determine categoryID
DECLARE @categoryID BIGINT
SELECT @categoryID = CategoryID FROM dbo.CFG_Category
WHERE ModuleID = @moduleID AND Description = @category
IF (@categoryID IS NULL)
BEGIN
	INSERT INTO dbo.CFG_Category 
		(ModuleID, Description, SortOrder) 
	VALUES 
		(@moduleID, @category, 1)	-- use a fixed value of '1' for sort order
	SET @categoryID = SCOPE_IDENTITY()
END
-- Make sure we have a sort order value; assume order of insertion is correct
IF (@sortOrder IS NULL)
BEGIN
	SELECT @sortOrder = ISNULL(MAX(SortOrder),0) + 1
	FROM dbo.CFG_Item
	WHERE CategoryID = @categoryID
END
-- Add item definition
DECLARE @itemID BIGINT
SELECT @itemID = ItemID FROM dbo.CFG_ITEM WHERE ItemName = @item
IF NOT (@itemID IS NULL) BEGIN
UPDATE dbo.CFG_Item
SET CategoryID=@categoryID,
	Question=@question,
	TypeCode=@typeCode,
	Description=@description,
	ListID=@listID,
	SortOrder=@sortOrder,
	AccessorNamespace=@accessorNamespace,
	AccessorClassName=@accessorClassName,
	AccessorAssemblyName=@accessorAssemblyName,
	UserControlName=@userControlName
WHERE ItemID = @itemID
END
ELSE BEGIN
INSERT INTO dbo.CFG_Item 
	(CategoryID, ItemName, Question, TypeCode, Description, ListID, SortOrder, AccessorNamespace, AccessorClassName, AccessorAssemblyName, UserControlName)
VALUES 
	(@categoryID, @item, @question, @typeCode, @description, @listID, @sortOrder, @accessorNamespace, @accessorClassName, @accessorAssemblyName, @userControlName)
SET @itemID = SCOPE_IDENTITY()
END
-- Add item value
IF exists(SELECT * FROM dbo.CFG_ItemValue WHERE ItemID=@itemID)
BEGIN
UPDATE dbo.CFG_ItemValue
SET [Value]=@value,
	DefaultValue=@value
WHERE ItemID=@itemID
END
ELSE BEGIN
INSERT INTO dbo.CFG_ItemValue 
	(ItemID, [Value], DefaultValue) 
VALUES 
	(@itemID, @value, @value)
END

IF (@itemID IS NULL OR @categoryID IS NULL OR @moduleID IS NULL)
BEGIN
	RAISERROR('Failed to insert item value.', 16, 1)
END

GO

-- Local variables
DECLARE @module NVARCHAR(500)
DECLARE @category NVARCHAR(500)
DECLARE @yesNoList INT
DECLARE @stringType INT
DECLARE @int32Type INT

--*****************************************************************************
-- Common settings
SET @category = 'General Settings'
SET @yesNoList = 1
SET @stringType = 18
SET @int32Type = 9

--*****************************************************************************
SET @module = 'Reporting'

-- add new entry

EXEC dbo.tmpAddSysPropItem @module, @category, 'WaveformThresholdInMilliseconds', '500', 
	'Maximum difference in milliseconds that a waveform can be away from an event and still be logically associated.', @int32Type, 'Maximum difference in milliseconds that a waveform can be away from an event and still be logically associated.'
GO

-- delete temp sproc
IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[tmpAddSysPropItem]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) 
DROP PROCEDURE [dbo].[tmpAddSysPropItem]
GO

/******************************************************************************
-- END: SCR#20432
******************************************************************************/

/******************************************************************************
-- SCR#20691 - SW_ION_Enterprise: 'Web Reporter Source Selector - error if same group name has different casing'
******************************************************************************/
IF OBJECT_ID('dbo.spDAL_GetSourceInfo') IS NOT NULL
BEGIN
	DROP PROCEDURE dbo.spDAL_GetSourceInfo
END
GO

/******************************************************************************
##BD_BEGIN
Return a single table of data:
 1. Source Info data that matches the input parameters
##BD_END 
$Id: //rdsw/dev/cayman/SW_VB6/business/MSDEUpgrader/scripts/NOM/NOM12pt1_Upgrade.sql#1 $
******************************************************************************/
CREATE PROCEDURE [dbo].[spDAL_GetSourceInfo]
(
	@SourceIDs NVARCHAR(MAX)
)
AS

-- DELIMITER CONSTANTS
DECLARE @DelimiterCharacter NCHAR(1)
SET @DelimiterCharacter = CHAR(6)

--- return the network summary information for the sources we have selected
SELECT
	s.[SourceID] AS SourceID
	, vwd.[ID] AS DeviceID
	, s.[Name] AS SourceName
	, [SourceGroupName] = 
		CASE CHARINDEX('.', s.[Name]) 		
			WHEN 0 THEN NULL 
			ELSE UPPER(SUBSTRING(s.[Name], 1,CHARINDEX('.', s.[Name]) - 1))
		END
	, vwd.[Protocol] AS ConnectionType
	, vwd.[Type] AS SourceType
	, vwd.[Site] AS Site
	, tz.[RegistryKey] AS SourceTimeZoneKey
FROM
	dbo.SRC_Source s
LEFT JOIN
	dbo.vPMCDevice vwd
ON
	s.[Name] = vwd.[Name]
LEFT JOIN
	dbo.SRC_Timezone tz
ON
	tz.[TimezoneID] = s.[TimezoneID]
WHERE
	(@SourceIDs IS NULL OR
		s.[SourceID] IN (SELECT DISTINCT number FROM dbo.udf_SplitIntListToTable(@SourceIDs, @DelimiterCharacter)))
	AND UPPER(s.[Name]) NOT LIKE 'LOGINSERTER%' 
	AND UPPER(s.[Name]) NOT LIKE 'QUERYSERVER%'
GO
/******************************************************************************
-- END: SCR#20691 --
******************************************************************************/



/******************************************************************************
-- SCR#21036 - SW_ION_Enterprise: 'Load Profile report needs to use configurable
-- list of system measurements (enables 37xx Device Support)'
******************************************************************************/
-- Create temporary stored procedure to add configuration items
IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[tmpAddSysPropItem]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) 
DROP PROCEDURE [dbo].[tmpAddSysPropItem]
GO
CREATE PROCEDURE dbo.tmpAddSysPropItem
( 
	@module 				NVARCHAR(500),
	@category 				NVARCHAR(500),
	@item 					NVARCHAR(50),
	@value 					NVARCHAR(1000),
	@question 				NVARCHAR(500),
	@typeCode 				INT,
	@description 			TEXT,
	@listID 				INT = NULL,
	@sortOrder 				INT = NULL,
	@accessorNamespace 		NVARCHAR(500) = NULL,
	@accessorClassName  	NVARCHAR(500) = NULL,
	@accessorAssemblyName	NVARCHAR(500) = NULL,
	@userControlName  		NVARCHAR(500) = NULL
)
AS 
-- Determine module ID
DECLARE @moduleID BIGINT
SELECT @moduleID = ModuleID FROM dbo.CFG_Module
WHERE Description = @module
IF (@moduleID IS NULL) 
BEGIN
	INSERT INTO dbo.CFG_Module 
		(Description, Enabled) 
	VALUES 
		(@module, 1)	-- the '1' enables the module
	SET @moduleID = SCOPE_IDENTITY()
END
-- Determine categoryID
DECLARE @categoryID BIGINT
SELECT @categoryID = CategoryID FROM dbo.CFG_Category
WHERE ModuleID = @moduleID AND Description = @category
IF (@categoryID IS NULL)
BEGIN
	INSERT INTO dbo.CFG_Category 
		(ModuleID, Description, SortOrder) 
	VALUES 
		(@moduleID, @category, 1)	-- use a fixed value of '1' for sort order
	SET @categoryID = SCOPE_IDENTITY()
END
-- Make sure we have a sort order value; assume order of insertion is correct
IF (@sortOrder IS NULL)
BEGIN
	SELECT @sortOrder = ISNULL(MAX(SortOrder),0) + 1
	FROM dbo.CFG_Item
	WHERE CategoryID = @categoryID
END
-- Add item definition
DECLARE @itemID BIGINT
SELECT @itemID = ItemID FROM dbo.CFG_ITEM WHERE ItemName = @item
IF NOT (@itemID IS NULL) BEGIN
UPDATE dbo.CFG_Item
SET CategoryID=@categoryID,
	Question=@question,
	TypeCode=@typeCode,
	Description=@description,
	ListID=@listID,
	SortOrder=@sortOrder,
	AccessorNamespace=@accessorNamespace,
	AccessorClassName=@accessorClassName,
	AccessorAssemblyName=@accessorAssemblyName,
	UserControlName=@userControlName
WHERE ItemID = @itemID
END
ELSE BEGIN
INSERT INTO dbo.CFG_Item 
	(CategoryID, ItemName, Question, TypeCode, Description, ListID, SortOrder, AccessorNamespace, AccessorClassName, AccessorAssemblyName, UserControlName)
VALUES 
	(@categoryID, @item, @question, @typeCode, @description, @listID, @sortOrder, @accessorNamespace, @accessorClassName, @accessorAssemblyName, @userControlName)
SET @itemID = SCOPE_IDENTITY()
END
-- Add item value
IF exists(SELECT * FROM dbo.CFG_ItemValue WHERE ItemID=@itemID)
BEGIN
UPDATE dbo.CFG_ItemValue
SET [Value]=@value,
	DefaultValue=@value
WHERE ItemID=@itemID
END
ELSE BEGIN
INSERT INTO dbo.CFG_ItemValue 
	(ItemID, [Value], DefaultValue) 
VALUES 
	(@itemID, @value, @value)
END

IF (@itemID IS NULL OR @categoryID IS NULL OR @moduleID IS NULL)
BEGIN
	RAISERROR('Failed to insert item value.', 16, 1)
END

GO

-- Local variables
DECLARE @module NVARCHAR(500)
DECLARE @category NVARCHAR(500)
DECLARE @yesNoList INT
DECLARE @stringType INT
DECLARE @int32Type INT

-- Common settings
SET @category = 'General Settings'
SET @yesNoList = 1
SET @stringType = 18
SET @int32Type = 9

SET @module = 'Reporting'

-- add new system properties
-- Logical measurements associated with Load Profile report
-- Demand Measurement Set
EXEC dbo.tmpAddSysPropItem @module, @category, 'LoadProfileDemand_Active', 'Active Power Sliding Window Demand Delivered-ReceivedActive Power Sliding Window Demand Delivered+ReceivedActive Power Sliding Window Demand DeliveredActive Power Thermal Demand Delivered-ReceivedActive Power Sliding Window Demand Received',
	'Prioritized list of system measurements that map to the Active Demand logical measurement', @stringType, 'Prioritized list of system measurements that map to the Active Demand logical measurement'

EXEC dbo.tmpAddSysPropItem @module, @category, 'LoadProfileDemand_Reactive', 'Reactive Power Sliding Window Demand Delivered - ReceivedReactive Power Sliding Window Demand Delivered+ReceivedReactive Power Sliding Window Demand DeliveredReactive Power Thermal Demand Delivered-ReceivedReactive Power Sliding Window Demand Received',
	'Prioritized list of system measurements that map to the Reactive Demand logical measurement', @stringType, 'Prioritized list of system measurements that map to the Reactive Demand logical measurement'

EXEC dbo.tmpAddSysPropItem @module, @category, 'LoadProfileDemand_Apparent', 'Apparent Power Sliding Window Demand Delivered+ReceivedApparent Power Sliding Window Demand Delivered-ReceivedApparent Power Sliding Window Demand DeliveredApparent Power Thermal Demand Delivered+Received', 
	'Prioritized list of system measurements that map to the Apparent Demand logical measurement', @stringType, 'Prioritized list of system measurements that map to the Demand Energy logical measurement'
-- Power Measurement Set
EXEC dbo.tmpAddSysPropItem @module, @category, 'LoadProfilePower_Active', 'Active Power',
	'Prioritized list of system measurements that map to the Active Demand logical measurement', @stringType, 'Prioritized list of system measurements that map to the Active Demand logical measurement'

EXEC dbo.tmpAddSysPropItem @module, @category, 'LoadProfilePower_Reactive', 'Reactive Power',
	'Prioritized list of system measurements that map to the Reactive Demand logical measurement', @stringType, 'Prioritized list of system measurements that map to the Reactive Demand logical measurement'

EXEC dbo.tmpAddSysPropItem @module, @category, 'LoadProfilePower_Apparent', 'Apparent Power', 
	'Prioritized list of system measurements that map to the Apparent Demand logical measurement', @stringType, 'Prioritized list of system measurements that map to the Demand Energy logical measurement'
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[tmpAddSysPropItem]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) 
DROP PROCEDURE [dbo].[tmpAddSysPropItem]
GO
/******************************************************************************
-- END: SCR#21036 --
******************************************************************************/

/******************************************************************************
-- SCR#20643 - SW_ION_Enterprise: 'Web Reporter - Energy Cost report does not 
-- work when run against PAC devices'
******************************************************************************/

-- Current configuration value for energy keys
UPDATE 
	dbo.[CFG_ItemValue]
SET
	[Value] = Value + char(6) + 'Active Energy Received Rate A'
WHERE
	[ItemID] = (SELECT ItemID FROM CFG_Item WHERE ItemName = 'EnergyCost_kWh')
UPDATE 
	dbo.[CFG_ItemValue]
SET
	[Value] = Value + char(6) + 'Reactive Energy Received Rate A'
WHERE
	[ItemID] = (SELECT ItemID FROM CFG_Item WHERE ItemName = 'EnergyCost_kVARh')
UPDATE 
	dbo.[CFG_ItemValue]
SET
	[Value] = Value + char(6) + 'Apparent Energy Delivered + Received Rate A'
WHERE
	[ItemID] = (SELECT ItemID FROM CFG_Item WHERE ItemName = 'EnergyCost_kVAh')

GO
-- Default configuration value for energy keys
UPDATE 
	dbo.[CFG_ItemValue]
SET
	[DefaultValue] = DefaultValue + char(6) + 'Active Energy Received Rate A'
WHERE
	[ItemID] = (SELECT ItemID FROM CFG_Item WHERE ItemName = 'EnergyCost_kWh')
UPDATE 
	dbo.[CFG_ItemValue]
SET
	[DefaultValue] = DefaultValue + char(6) + 'Reactive Energy Received Rate A'
WHERE
	[ItemID] = (SELECT ItemID FROM CFG_Item WHERE ItemName = 'EnergyCost_kVARh')
UPDATE 
	dbo.[CFG_ItemValue]
SET
	[DefaultValue] = DefaultValue + char(6) + 'Apparent Energy Delivered + Received Rate A'
WHERE
	[ItemID] = (SELECT ItemID FROM CFG_Item WHERE ItemName = 'EnergyCost_kVAh')
GO

/******************************************************************************
-- END: SCR#20643 --
******************************************************************************/

/******************************************************************************
-- SCR#20868 - SW_ION_Enterprise: 'Reports: EnergyCost & LoadProfile need to 
-- support EM5600 energy and demand measurements'
******************************************************************************/

-- Current configuration value for energy keys
UPDATE 
	dbo.[CFG_ItemValue]
SET
	[Value] = Value + CHAR(6) + 'Active Energy Delivered Interval' + CHAR(6) + 'Active Energy Received Interval'
WHERE
	[ItemID] = (SELECT ItemID FROM CFG_Item WHERE ItemName = 'EnergyCost_kWh')

UPDATE 
	dbo.[CFG_ItemValue]
SET
	[Value] = Value + CHAR(6) + 'Reactive Energy Delivered Interval' + CHAR(6) + 'Reactive Energy Received Interval'
WHERE
	[ItemID] = (SELECT ItemID FROM CFG_Item WHERE ItemName = 'EnergyCost_kVARh')

GO
-- Default configuration value for energy keys
UPDATE 
	dbo.[CFG_ItemValue]
SET
	[DefaultValue] = DefaultValue + + CHAR(6) + 'Active Energy Delivered Interval' + CHAR(6) + 'Active Energy Received Interval'
WHERE
	[ItemID] = (SELECT ItemID FROM CFG_Item WHERE ItemName = 'EnergyCost_kWh')
UPDATE 
	dbo.[CFG_ItemValue]
SET
	[DefaultValue] = DefaultValue + CHAR(6) + 'Reactive Energy Delivered Interval' + CHAR(6) + 'Reactive Energy Received Interval'
WHERE
	[ItemID] = (SELECT ItemID FROM CFG_Item WHERE ItemName = 'EnergyCost_kVARh')
GO

/******************************************************************************
-- END: SCR#20868 --
******************************************************************************/

/******************************************************************************
-- SCR#200455 - SW_ION_Enterprise: 'Web Reporter - Enhanced Report Pack Support'
******************************************************************************/

--Default Data: Asset Installer Component
DECLARE @InstallerComponentName AS NVARCHAR(250)
SET @InstallerComponentName = 'ReportPackAssetInstaller'

DECLARE @ComponentID INT

IF NOT EXISTS(SELECT ComponentID FROM dbo.DCR_Component WHERE [Name] = @InstallerComponentName)
BEGIN
 
 EXEC dbo.spDCR_Component_Add 
  @InstallerComponentName, 
  'ReportPack Asset Installer', 
  @ComponentID OUT

END
ELSE
BEGIN
 SELECT @ComponentID = ComponentID FROM dbo.DCR_Component WHERE [Name] = @InstallerComponentName
END

IF (@ComponentID IS NULL)
BEGIN
 RAISERROR('ReportPack asset installer does not exist, cannot add DCR entries', 16, 1)
 ROLLBACK TRAN
END

DECLARE @RegistryID INT

DECLARE @ReportPackAsset INT
SET @ReportPackAsset = 1 

-- ReportTemplateInstaller
IF NOT EXISTS(SELECT * FROM dbo.DCR_DynamicClassRegistry WHERE [Name] = N'ReportTemplate' AND [ComponentID] = @ComponentID)
BEGIN
 EXEC dbo.spDCR_DynamicClassRegistry_Add
  @ComponentID,
  @ReportPackAsset,
  N'ReportTemplate',
  N'ReportTemplate Asset Installer',
  N'PowerMeasurement.Reporting.ReportPacks.AssetInstallers.ReportTemplateAssetInstaller',
  N'Reporting.ReportPacks.dll',
  NULL,
  1,
  NULL,
  @RegistryID OUTPUT
END

-- SqlAssemblyInstaller
IF NOT EXISTS(SELECT * FROM dbo.DCR_DynamicClassRegistry WHERE [Name] = N'SqlAssembly' AND [ComponentID] = @ComponentID)
BEGIN
 EXEC dbo.spDCR_DynamicClassRegistry_Add
  @ComponentID,
  @ReportPackAsset,
  N'SqlAssembly',
  N'SQL Assembly Asset Installer',
  N'PowerMeasurement.Reporting.ReportPacks.AssetInstallers.SqlAssemblyAssetInstaller',
  N'Reporting.ReportPacks.dll',
  NULL,
  1,
  NULL,
  @RegistryID OUTPUT
END

-- SqlAssemblyScriptInstaller
IF NOT EXISTS(SELECT * FROM dbo.DCR_DynamicClassRegistry WHERE [Name] = N'SqlAssemblyScript' AND [ComponentID] = @ComponentID)
BEGIN
 EXEC dbo.spDCR_DynamicClassRegistry_Add
  @ComponentID,
  @ReportPackAsset,
  N'SqlAssemblyScript',
  N'Sql Assebmly Script Asset Installer',
  N'PowerMeasurement.Reporting.ReportPacks.AssetInstallers.SqlAssemblyScriptAssetInstaller',
  N'Reporting.ReportPacks.dll',
  NULL,
  1,
  NULL,
  @RegistryID OUTPUT
END

-- ResourceInstaller
IF NOT EXISTS(SELECT * FROM dbo.DCR_DynamicClassRegistry WHERE [Name] = N'Resource' AND [ComponentID] = @ComponentID)
BEGIN
 EXEC dbo.spDCR_DynamicClassRegistry_Add
  @ComponentID,
  @ReportPackAsset,
  N'Resource',
  N'Resource Asset Installer',
  N'PowerMeasurement.Reporting.ReportPacks.AssetInstallers.ResourceAssetInstaller',
  N'Reporting.ReportPacks.dll',
  NULL,
  1,
  NULL,
  @RegistryID OUTPUT
END

-- SqlScriptInstaller
IF NOT EXISTS(SELECT * FROM dbo.DCR_DynamicClassRegistry WHERE [Name] = N'SqlScript' AND [ComponentID] = @ComponentID)
BEGIN
 EXEC dbo.spDCR_DynamicClassRegistry_Add
  @ComponentID,
  @ReportPackAsset,
  N'SqlScript',
  N'SqlScript Asset Installer',
  N'PowerMeasurement.Reporting.ReportPacks.AssetInstallers.SqlScriptAssetInstaller',
  N'Reporting.ReportPacks.dll',
  NULL,
  1,
  NULL,
  @RegistryID OUTPUT
END

-- FileInstaller
IF NOT EXISTS(SELECT * FROM dbo.DCR_DynamicClassRegistry WHERE [Name] = N'File' AND [ComponentID] = @ComponentID)
BEGIN
 EXEC dbo.spDCR_DynamicClassRegistry_Add
  @ComponentID,
  @ReportPackAsset,
  N'File',
  N'File Asset Installer',
  N'PowerMeasurement.Reporting.ReportPacks.AssetInstallers.FileAssetInstaller',
  N'Reporting.ReportPacks.dll',
  NULL,
  1,
  NULL,
  @RegistryID OUTPUT
END
 
-- AssemblyInstaller
IF NOT EXISTS(SELECT * FROM dbo.DCR_DynamicClassRegistry WHERE [Name] = N'Assembly' AND [ComponentID] = @ComponentID)
BEGIN
 EXEC dbo.spDCR_DynamicClassRegistry_Add
  @ComponentID,
  @ReportPackAsset,

  N'Assembly',
  N'Assebmly Asset Installer',
  N'PowerMeasurement.Reporting.ReportPacks.AssetInstallers.AssemblyAssetInstaller',
  N'Reporting.ReportPacks.dll',
  NULL,
  1,
  NULL,
  @RegistryID OUTPUT
END

-- JavaScriptAssetInstaller
IF NOT EXISTS(SELECT * FROM dbo.DCR_DynamicClassRegistry WHERE [Name] = N'JavaScript' AND [ComponentID] = @ComponentID)
BEGIN
 EXEC dbo.spDCR_DynamicClassRegistry_Add
  @ComponentID,
  @ReportPackAsset,
  N'JavaScript',
  N'JavaScript Asset Installer',
  N'PowerMeasurement.Reporting.ReportPacks.AssetInstallers.JavaScriptAssetInstaller',
  N'Reporting.ReportPacks.dll',
  NULL,
  1,
  NULL,
  @RegistryID OUTPUT
END

-- CssAssetInstaller
IF NOT EXISTS(SELECT * FROM dbo.DCR_DynamicClassRegistry WHERE [Name] = N'Css' AND [ComponentID] = @ComponentID)
BEGIN
 EXEC dbo.spDCR_DynamicClassRegistry_Add
  @ComponentID,
  @ReportPackAsset,
  N'Css',
  N'Css Asset Installer',
  N'PowerMeasurement.Reporting.ReportPacks.AssetInstallers.CssAssetInstaller',
  N'Reporting.ReportPacks.dll',
  NULL,
  1,
  NULL,
  @RegistryID OUTPUT
END

-- ResxAssetInstaller
IF NOT EXISTS(SELECT * FROM dbo.DCR_DynamicClassRegistry WHERE [Name] = N'Resx' AND [ComponentID] = @ComponentID)
BEGIN
 EXEC dbo.spDCR_DynamicClassRegistry_Add
  @ComponentID,
  @ReportPackAsset,
  N'Resx',
  N'Resx Asset Installer',
  N'PowerMeasurement.Reporting.ReportPacks.AssetInstallers.ResxAssetInstaller',
  N'Reporting.ReportPacks.dll',
  NULL,
  1,
  NULL,
  @RegistryID OUTPUT
END

-- ControlAssetInstaller
IF NOT EXISTS(SELECT * FROM dbo.DCR_DynamicClassRegistry WHERE [Name] = N'Control' AND [ComponentID] = @ComponentID)
BEGIN
 EXEC dbo.spDCR_DynamicClassRegistry_Add
  @ComponentID,
  @ReportPackAsset,
  N'Control',
  N'Control Asset Installer',
  N'PowerMeasurement.Reporting.ReportPacks.AssetInstallers.ControlAssetInstaller',
  N'Reporting.ReportPacks.dll',
  NULL,
  1,
  NULL,
  @RegistryID OUTPUT
END

-- ReportPack Uploader Logging
-- Create Table RPT_ReportPackAssets
IF NOT EXISTS(SELECT name FROM sysobjects WHERE xtype='u' AND name='RPT_ReportPackAssets')
BEGIN
	CREATE TABLE dbo.RPT_ReportPackAssets
	(
		InstallDate		datetime not null,
		AssetType		VARCHAR(50) not null,
		AssetFileName	NVARCHAR(250) not null,
		ReportPackName	NVARCHAR(50) not null,
		InstallAction	VARCHAR(50) not null,
		LastActionDate	datetime not null,
		PRIMARY KEY		(ReportPackName, AssetFileName, AssetType)
	)
END

IF EXISTS(SELECT name FROM sysobjects WHERE xtype='P' AND name='spRPT_ReportPack_RetrieveAsset')
	DROP PROCEDURE dbo.spRPT_ReportPack_RetrieveAsset
GO
CREATE PROCEDURE dbo.spRPT_ReportPack_RetrieveAsset
(
	@AssetType		VARCHAR(50),
	@AssetFileName	NVARCHAR(250),
	@ReportPackName	NVARCHAR(50)
)
AS
BEGIN
	SELECT * 
	FROM 
		RPT_ReportPackAssets
	WHERE 
		AssetType = @AssetType AND 
		AssetFileName = @AssetFileName AND 
		ReportPackName = @ReportPackName
END
GO

IF EXISTS(SELECT * FROM sysobjects WHERE xtype='P' AND name='spRPT_ReportPack_AddAsset')
	DROP PROCEDURE dbo.spRPT_ReportPack_AddAsset
GO
CREATE PROCEDURE dbo.spRPT_ReportPack_AddAsset
	@InstallDate	datetime,
	@AssetType		VARCHAR(50),
	@AssetFileName	NVARCHAR(250),
	@ReportPackName	NVARCHAR(50),
	@InstallAction	VARCHAR(50)
AS
BEGIN
	DECLARE @retval INT
	SET @retval = 0
	IF NOT EXISTS (	
		SELECT AssetFileName 
		FROM RPT_ReportPackAssets
		WHERE AssetType = @AssetType AND AssetFileName = @AssetFileName AND ReportPackName = @ReportPackName)
	BEGIN	
		INSERT INTO RPT_ReportPackAssets
		(
			InstallDate,
			AssetType,
			AssetFileName,
			ReportPackName,
			InstallAction,
			LastActionDate
		)
		VALUES
		(
			@InstallDate,
			@AssetType,
			@AssetFileName,
			@ReportPackName,
			@InstallAction,
			@InstallDate
		)
	END
	ELSE
	BEGIN
		UPDATE RPT_ReportPackAssets
		SET 
			InstallAction = @InstallAction,
			LastActionDate = @InstallDate
		WHERE
			AssetType = @AssetType AND 
			AssetFileName = @AssetFileName AND 
			ReportPackName = @ReportPackName
	END
	SET @retval = @@ERROR
	RETURN @retval
END
GO