USE [master]
GO

CREATE DATABASE [Demo_MeterChangeMgmt]
GO

USE [Demo_MeterChangeMgmt]
GO

CREATE TABLE dbo.[Meters] (
	EditID UNIQUEIDENTIFIER NOT NULL,
	StationName VARCHAR(50) NULL,
	CustomerName VARCHAR(50) NULL,
	PhaseType VARCHAR(50) NULL,
	StationNo VARCHAR(50) NULL,
	PBACustomerID VARCHAR(50) NULL,
	PBACustomerIDext VARCHAR(50) NULL,
	TSC VARCHAR(50) NULL,
	MeteringCircuit VARCHAR(50) NULL,
	CustomerType VARCHAR(50) NULL,
	MeterSector VARCHAR(50) NULL,
	ServiceRendered VARCHAR(50) NULL,
	ServiceRenderedDate DATETIME NULL,
	ServiceRenderedReason VARCHAR(200) NULL,
	MeterMKnum VARCHAR(50) NULL,
	MeterManuf VARCHAR(50) NULL,
	MeterModel VARCHAR(50) NULL,
	MeterSN VARCHAR(50) NULL,
	MeterType VARCHAR(50) NULL,
	VoltageType VARCHAR(50) NULL,
	VoltageNomPrimary VARCHAR(50) NULL,
	VoltageNomSecondary VARCHAR(50) NULL,
	CommPhone VARCHAR(50) NULL,
	CommAddress VARCHAR(200) NULL
)
GO

CREATE TABLE dbo.[MeterCircuits] (
	EditID UNIQUEIDENTIFIER NOT NULL,
	RatioPrimary VARCHAR(50) NULL,
	RatioSecondary VARCHAR(50) NULL,
	ConnUsed VARCHAR(50) NULL
)
GO

CREATE TABLE dbo.[CommPowerQualitySpecifications] (
	EditID UNIQUEIDENTIFIER NOT NULL,
	ModelNumber VARCHAR(50) NULL,
	MeteringFWRevision VARCHAR(50) NULL,
	Template VARCHAR(50) NULL,
	NominalVoltage VARCHAR(50) NULL,
	IPAddress VARCHAR(50) NULL,
	SubnetMask VARCHAR(50) NULL,
	DefaultGateway VARCHAR(50) NULL,
	MACAddress VARCHAR(50) NULL
)
GO

CREATE TABLE dbo.[RegisterListings] (
	EditID UNIQUEIDENTIFIER NOT NULL,
	Quantity VARCHAR(50) NULL
)
GO

USE [Demo_XDA]
GO

INSERT INTO ExternalDatabases (Name,ConnectionString,DataProviderString,Encrypted)
VALUES ('MCM','','',0)
GO

INSERT INTO extDBTables (TableName,Query,ExternalDBID)
VALUES ('Meters','(
SELECT
	StationName AS "Location (MCM)",
	CustomerName AS "Customer (MCM)",
	PhaseType AS "Phase Type (MCM)",
	StationNo AS "STA No (MCM)",
	PBACustomerID AS "PBA ID (MCM)",
	(PBACustomerID + PBACustomerIDext) AS "Circuit ID No (MCM)",
	TSC AS "TSC (MCM)",
	MeteringCircuit AS "Circuit (MCM)",
	CustomerType AS "Customer Type (MCM)",
	MeterSector AS "Meter Sector (MCM)",
	ServiceRendered AS "Service Rendered",
	CONVERT(DATE,ServiceRenderedDate) AS "Service Date (MCM)",
	CONVERT(TIME,ServiceRenderedDate) AS "Service System Time (MCM)",
	ServiceRenderedReason AS "Service Reason (MCM)",
	MeterMKnum AS "Current TVA No (MCM)",
	MeterManuf AS "Manufacturer (MCM)",
	MeterModel AS "Model (MCM)",
	MeterSN AS "MFR. S/N (MCM)",
	MeterType AS "Type (MCM)",
	VoltageType AS "Type Voltage Recorded (MCM)",
	VoltageNomPrimary AS "Operating Voltage (Nominal) (MCM)",
	VoltageNomSecondary AS "Secondary (MCM)",
	CommPhone AS "Comm No (MCM)",
	CommAddress AS "Comm Address(Hex) (MCM)"
FROM Meters
WHERE EditID LIKE {key} OR {key} IS NULL
)',(SELECT ID FROM ExternalDatabases WHERE Name = 'MCM'))
GO

INSERT INTO AdditionalField (ParentTable,FieldName,Type,IsSecure,Searchable,ExternalDBTableID,IsKey,IsInfo) VALUES ('Meter','Edit ID (MCM)','string',0,0,(SELECT ID FROM extDBTables WHERE TableName = 'Meters' AND ExtDBID = (SELECT ID FROM ExternalDatabases WHERE Name = 'MCM')),1,0)
GO

INSERT INTO AdditionalField (ParentTable,FieldName,Type,IsSecure,Searchable,ExternalDBTableID,IsKey,IsInfo) VALUES ('Meter','Location (MCM)','string',0,0,(SELECT ID FROM extDBTables WHERE TableName = 'Meters' AND ExtDBID = (SELECT ID FROM ExternalDatabases WHERE Name = 'MCM')),0,0)
GO
INSERT INTO AdditionalField (ParentTable,FieldName,Type,IsSecure,Searchable,ExternalDBTableID,IsKey,IsInfo) VALUES ('Meter','Customer (MCM)','string',0,0,(SELECT ID FROM extDBTables WHERE TableName = 'Meters' AND ExtDBID = (SELECT ID FROM ExternalDatabases WHERE Name = 'MCM')),0,0)
GO
INSERT INTO AdditionalField (ParentTable,FieldName,Type,IsSecure,Searchable,ExternalDBTableID,IsKey,IsInfo) VALUES ('Meter','Phase Type (MCM)','string',0,0,(SELECT ID FROM extDBTables WHERE TableName = 'Meters' AND ExtDBID = (SELECT ID FROM ExternalDatabases WHERE Name = 'MCM')),0,0)
GO
INSERT INTO AdditionalField (ParentTable,FieldName,Type,IsSecure,Searchable,ExternalDBTableID,IsKey,IsInfo) VALUES ('Meter','STA No (MCM)','string',0,0,(SELECT ID FROM extDBTables WHERE TableName = 'Meters' AND ExtDBID = (SELECT ID FROM ExternalDatabases WHERE Name = 'MCM')),0,0)
GO
INSERT INTO AdditionalField (ParentTable,FieldName,Type,IsSecure,Searchable,ExternalDBTableID,IsKey,IsInfo) VALUES ('Meter','PBA ID (MCM)','string',0,0,(SELECT ID FROM extDBTables WHERE TableName = 'Meters' AND ExtDBID = (SELECT ID FROM ExternalDatabases WHERE Name = 'MCM')),0,0)
GO
INSERT INTO AdditionalField (ParentTable,FieldName,Type,IsSecure,Searchable,ExternalDBTableID,IsKey,IsInfo) VALUES ('Meter','Circuit ID No (MCM)','string',0,0,(SELECT ID FROM extDBTables WHERE TableName = 'Meters' AND ExtDBID = (SELECT ID FROM ExternalDatabases WHERE Name = 'MCM')),0,0)
GO
INSERT INTO AdditionalField (ParentTable,FieldName,Type,IsSecure,Searchable,ExternalDBTableID,IsKey,IsInfo) VALUES ('Meter','TSC (MCM)','string',0,0,(SELECT ID FROM extDBTables WHERE TableName = 'Meters' AND ExtDBID = (SELECT ID FROM ExternalDatabases WHERE Name = 'MCM')),0,0)
GO
INSERT INTO AdditionalField (ParentTable,FieldName,Type,IsSecure,Searchable,ExternalDBTableID,IsKey,IsInfo) VALUES ('Meter','Circuit (MCM)','string',0,0,(SELECT ID FROM extDBTables WHERE TableName = 'Meters' AND ExtDBID = (SELECT ID FROM ExternalDatabases WHERE Name = 'MCM')),0,0)
GO
INSERT INTO AdditionalField (ParentTable,FieldName,Type,IsSecure,Searchable,ExternalDBTableID,IsKey,IsInfo) VALUES ('Meter','Customer Type (MCM)','string',0,0,(SELECT ID FROM extDBTables WHERE TableName = 'Meters' AND ExtDBID = (SELECT ID FROM ExternalDatabases WHERE Name = 'MCM')),0,0)
GO
INSERT INTO AdditionalField (ParentTable,FieldName,Type,IsSecure,Searchable,ExternalDBTableID,IsKey,IsInfo) VALUES ('Meter','Meter Sector (MCM)','string',0,0,(SELECT ID FROM extDBTables WHERE TableName = 'Meters' AND ExtDBID = (SELECT ID FROM ExternalDatabases WHERE Name = 'MCM')),0,0)
GO
INSERT INTO AdditionalField (ParentTable,FieldName,Type,IsSecure,Searchable,ExternalDBTableID,IsKey,IsInfo) VALUES ('Meter','Service Rendered (MCM)','string',0,0,(SELECT ID FROM extDBTables WHERE TableName = 'Meters' AND ExtDBID = (SELECT ID FROM ExternalDatabases WHERE Name = 'MCM')),0,0)
GO
INSERT INTO AdditionalField (ParentTable,FieldName,Type,IsSecure,Searchable,ExternalDBTableID,IsKey,IsInfo) VALUES ('Meter','Service Date (MCM)','string',0,0,(SELECT ID FROM extDBTables WHERE TableName = 'Meters' AND ExtDBID = (SELECT ID FROM ExternalDatabases WHERE Name = 'MCM')),0,0)
GO
INSERT INTO AdditionalField (ParentTable,FieldName,Type,IsSecure,Searchable,ExternalDBTableID,IsKey,IsInfo) VALUES ('Meter','Service System Time (MCM)','string',0,0,(SELECT ID FROM extDBTables WHERE TableName = 'Meters' AND ExtDBID = (SELECT ID FROM ExternalDatabases WHERE Name = 'MCM')),0,0)
GO
INSERT INTO AdditionalField (ParentTable,FieldName,Type,IsSecure,Searchable,ExternalDBTableID,IsKey,IsInfo) VALUES ('Meter','Service Reason (MCM)','string',0,0,(SELECT ID FROM extDBTables WHERE TableName = 'Meters' AND ExtDBID = (SELECT ID FROM ExternalDatabases WHERE Name = 'MCM')),0,0)
GO
INSERT INTO AdditionalField (ParentTable,FieldName,Type,IsSecure,Searchable,ExternalDBTableID,IsKey,IsInfo) VALUES ('Meter','Current TVA No (MCM)','string',0,0,(SELECT ID FROM extDBTables WHERE TableName = 'Meters' AND ExtDBID = (SELECT ID FROM ExternalDatabases WHERE Name = 'MCM')),0,0)
GO
INSERT INTO AdditionalField (ParentTable,FieldName,Type,IsSecure,Searchable,ExternalDBTableID,IsKey,IsInfo) VALUES ('Meter','Manufacturer (MCM)','string',0,0,(SELECT ID FROM extDBTables WHERE TableName = 'Meters' AND ExtDBID = (SELECT ID FROM ExternalDatabases WHERE Name = 'MCM')),0,0)
GO
INSERT INTO AdditionalField (ParentTable,FieldName,Type,IsSecure,Searchable,ExternalDBTableID,IsKey,IsInfo) VALUES ('Meter','Model (MCM)','string',0,0,(SELECT ID FROM extDBTables WHERE TableName = 'Meters' AND ExtDBID = (SELECT ID FROM ExternalDatabases WHERE Name = 'MCM')),0,0)
GO
INSERT INTO AdditionalField (ParentTable,FieldName,Type,IsSecure,Searchable,ExternalDBTableID,IsKey,IsInfo) VALUES ('Meter','MFR. S/N (MCM)','string',0,0,(SELECT ID FROM extDBTables WHERE TableName = 'Meters' AND ExtDBID = (SELECT ID FROM ExternalDatabases WHERE Name = 'MCM')),0,0)
GO
INSERT INTO AdditionalField (ParentTable,FieldName,Type,IsSecure,Searchable,ExternalDBTableID,IsKey,IsInfo) VALUES ('Meter','Type (MCM)','string',0,0,(SELECT ID FROM extDBTables WHERE TableName = 'Meters' AND ExtDBID = (SELECT ID FROM ExternalDatabases WHERE Name = 'MCM')),0,0)
GO
INSERT INTO AdditionalField (ParentTable,FieldName,Type,IsSecure,Searchable,ExternalDBTableID,IsKey,IsInfo) VALUES ('Meter','Type Voltage Recorded (MCM)','string',0,0,(SELECT ID FROM extDBTables WHERE TableName = 'Meters' AND ExtDBID = (SELECT ID FROM ExternalDatabases WHERE Name = 'MCM')),0,0)
GO
INSERT INTO AdditionalField (ParentTable,FieldName,Type,IsSecure,Searchable,ExternalDBTableID,IsKey,IsInfo) VALUES ('Meter','Operating Voltage (Nominal) (MCM)','string',0,0,(SELECT ID FROM extDBTables WHERE TableName = 'Meters' AND ExtDBID = (SELECT ID FROM ExternalDatabases WHERE Name = 'MCM')),0,0)
GO
INSERT INTO AdditionalField (ParentTable,FieldName,Type,IsSecure,Searchable,ExternalDBTableID,IsKey,IsInfo) VALUES ('Meter','Secondary (MCM)','string',0,0,(SELECT ID FROM extDBTables WHERE TableName = 'Meters' AND ExtDBID = (SELECT ID FROM ExternalDatabases WHERE Name = 'MCM')),0,0)
GO
INSERT INTO AdditionalField (ParentTable,FieldName,Type,IsSecure,Searchable,ExternalDBTableID,IsKey,IsInfo) VALUES ('Meter','Comm No (MCM)','string',0,0,(SELECT ID FROM extDBTables WHERE TableName = 'Meters' AND ExtDBID = (SELECT ID FROM ExternalDatabases WHERE Name = 'MCM')),0,0)
GO
INSERT INTO AdditionalField (ParentTable,FieldName,Type,IsSecure,Searchable,ExternalDBTableID,IsKey,IsInfo) VALUES ('Meter','Comm Address(Hex) (MCM)','string',0,0,(SELECT ID FROM extDBTables WHERE TableName = 'Meters' AND ExtDBID = (SELECT ID FROM ExternalDatabases WHERE Name = 'MCM')),0,0)
GO

INSERT INTO extDBTables (TableName,Query,ExternalDBID)
VALUES ('MeterCircuits','(
SELECT
	RatioPrimary AS "Ratio Primary (MCM)",
	RatioSecondary AS "Ratio Secondary (MCM)",
	ConnUsed AS "Conn Used (MCM)"
FROM MeterCircuits
WHERE EditID LIKE {key} OR {key} IS NULL
)',(SELECT ID FROM ExternalDatabases WHERE Name = 'MCM'))
GO

INSERT INTO AdditionalField (ParentTable,FieldName,Type,IsSecure,Searchable,ExternalDBTableID,IsKey,IsInfo) VALUES ('Meter','Ratio Primary (MCM)','string',0,0,(SELECT ID FROM extDBTables WHERE TableName = 'MeterCircuits' AND ExtDBID = (SELECT ID FROM ExternalDatabases WHERE Name = 'MCM')),0,0)
GO
INSERT INTO AdditionalField (ParentTable,FieldName,Type,IsSecure,Searchable,ExternalDBTableID,IsKey,IsInfo) VALUES ('Meter','Ratio Secondary (MCM)','string',0,0,(SELECT ID FROM extDBTables WHERE TableName = 'MeterCircuits' AND ExtDBID = (SELECT ID FROM ExternalDatabases WHERE Name = 'MCM')),0,0)
GO
INSERT INTO AdditionalField (ParentTable,FieldName,Type,IsSecure,Searchable,ExternalDBTableID,IsKey,IsInfo) VALUES ('Meter','Conn Used (MCM)','string',0,0,(SELECT ID FROM extDBTables WHERE TableName = 'MeterCircuits' AND ExtDBID = (SELECT ID FROM ExternalDatabases WHERE Name = 'MCM')),0,0)
GO

INSERT INTO extDBTables (TableName,Query,ExternalDBID)
VALUES ('CommPowerQualitySpecifications','(
SELECT
	ModelNumber AS "Model(Product) Number (MCM)",
	MeteringFWRevision AS "Metering FW Revision (MCM)",
	Template AS "Template (MCM)",
	NominalVoltage AS "Nominal Voltage (MCM)",
	IPAddress AS "IP Address (MCM)",
	SubnetMask AS "Subnet Mask (MCM)",
	DefaultGateway AS "Default Gateway (MCM)",
	MACAddress AS "MAC Address (MCM)"
FROM CommPowerQualitySpecifications
WHERE EditID LIKE {key} OR {key} IS NULL
)',(SELECT ID FROM ExternalDatabases WHERE Name = 'MCM'))
GO

INSERT INTO AdditionalField (ParentTable,FieldName,Type,IsSecure,Searchable,ExternalDBTableID,IsKey,IsInfo) VALUES ('Meter','Model(Product) Number (MCM)','string',0,0,(SELECT ID FROM extDBTables WHERE TableName = 'CommPowerQualitySpecifications' AND ExtDBID = (SELECT ID FROM ExternalDatabases WHERE Name = 'MCM')),0,0)
GO
INSERT INTO AdditionalField (ParentTable,FieldName,Type,IsSecure,Searchable,ExternalDBTableID,IsKey,IsInfo) VALUES ('Meter','Metering FW Revision (MCM)','string',0,0,(SELECT ID FROM extDBTables WHERE TableName = 'CommPowerQualitySpecifications' AND ExtDBID = (SELECT ID FROM ExternalDatabases WHERE Name = 'MCM')),0,0)
GO
INSERT INTO AdditionalField (ParentTable,FieldName,Type,IsSecure,Searchable,ExternalDBTableID,IsKey,IsInfo) VALUES ('Meter','Template (MCM)','string',0,0,(SELECT ID FROM extDBTables WHERE TableName = 'CommPowerQualitySpecifications' AND ExtDBID = (SELECT ID FROM ExternalDatabases WHERE Name = 'MCM')),0,0)
GO
INSERT INTO AdditionalField (ParentTable,FieldName,Type,IsSecure,Searchable,ExternalDBTableID,IsKey,IsInfo) VALUES ('Meter','Nominal Voltage (MCM)','string',0,0,(SELECT ID FROM extDBTables WHERE TableName = 'CommPowerQualitySpecifications' AND ExtDBID = (SELECT ID FROM ExternalDatabases WHERE Name = 'MCM')),0,0)
GO
INSERT INTO AdditionalField (ParentTable,FieldName,Type,IsSecure,Searchable,ExternalDBTableID,IsKey,IsInfo) VALUES ('Meter','IP Address (MCM)','string',0,0,(SELECT ID FROM extDBTables WHERE TableName = 'CommPowerQualitySpecifications' AND ExtDBID = (SELECT ID FROM ExternalDatabases WHERE Name = 'MCM')),0,0)
GO
INSERT INTO AdditionalField (ParentTable,FieldName,Type,IsSecure,Searchable,ExternalDBTableID,IsKey,IsInfo) VALUES ('Meter','Subnet Mask (MCM)','string',0,0,(SELECT ID FROM extDBTables WHERE TableName = 'CommPowerQualitySpecifications' AND ExtDBID = (SELECT ID FROM ExternalDatabases WHERE Name = 'MCM')),0,0)
GO
INSERT INTO AdditionalField (ParentTable,FieldName,Type,IsSecure,Searchable,ExternalDBTableID,IsKey,IsInfo) VALUES ('Meter','Default Gateway (MCM)','string',0,0,(SELECT ID FROM extDBTables WHERE TableName = 'CommPowerQualitySpecifications' AND ExtDBID = (SELECT ID FROM ExternalDatabases WHERE Name = 'MCM')),0,0)
GO
INSERT INTO AdditionalField (ParentTable,FieldName,Type,IsSecure,Searchable,ExternalDBTableID,IsKey,IsInfo) VALUES ('Meter','MAC Address (MCM)','string',0,0,(SELECT ID FROM extDBTables WHERE TableName = 'CommPowerQualitySpecifications' AND ExtDBID = (SELECT ID FROM ExternalDatabases WHERE Name = 'MCM')),0,0)
GO

INSERT INTO extDBTables (TableName,Query,ExternalDBID)
VALUES ('RegisterListings','(
SELECT
	Quantity AS "FW Rev. Feature Set (MCM)"
FROM RegisterListings
WHERE EditID LIKE {key} OR {key} IS NULL
)',(SELECT ID FROM ExternalDatabases WHERE Name = 'MCM'))
GO

INSERT INTO AdditionalField (ParentTable,FieldName,Type,IsSecure,Searchable,ExternalDBTableID,IsKey,IsInfo) VALUES ('Meter','FW Rev. Feature Set (MCM)','string',0,0,(SELECT ID FROM extDBTables WHERE TableName = 'RegisterListings' AND ExtDBID = (SELECT ID FROM ExternalDatabases WHERE Name = 'MCM')),0,0)
GO