ALTER TABLE Channel
ADD Adder FLOAT NOT NULL DEFAULT 0
GO

ALTER TABLE Channel
ADD Multiplier FLOAT NOT NULL DEFAULT 1
GO

/* Pull All CapBanks */
SELECT 'INSERT INTO CapacitorBankAttributes(AssetID,NumberOfBanks,CapacitancePerBank,CktSwitcher,MaxKV,UnitKV,UnitKVAr,NegReactanceTol,PosReactanceTol,Nparalell,Nseries,NSeriesGroup,NParalellGroup,Fused,VTratioBus,NumberLVCaps,NumberLVUnits,LVKVAr,LVKV,LVNegReactanceTol,LVPosReactanceTol,UpperXFRRatio,LowerXFRRatio,Nshorted,BlownFuses,BlownGroups,Rv,Rh,Compensated,NLowerGroups,ShortedGroups, Sh,RelayPTRatioPrimary,RelayPTRatioSecondary) SELECT ' +
    COALESCE(CONVERT(VARCHAR(MAX), AssetID) + ')', 'NULL') + ',' +
	COALESCE(CONVERT(VARCHAR(MAX), NumberOfBanks),'NULL') + ',' +
	COALESCE(CONVERT(VARCHAR(MAX), CapacitancePerBank),'NULL') + ',' +
	COALESCE('''' + CONVERT(VARCHAR(MAX), CktSwitcher) + '''','NULL') + ',' +
	COALESCE(CONVERT(VARCHAR(MAX), MaxKV),'NULL') + ',' +
	COALESCE(CONVERT(VARCHAR(MAX), UnitKV),'NULL') + ',' +
	COALESCE(CONVERT(VARCHAR(MAX), UnitKVAr),'NULL') + ',' +
	COALESCE(CONVERT(VARCHAR(MAX), NegReactanceTol),'NULL') + ',' +
	COALESCE(CONVERT(VARCHAR(MAX), PosReactanceTol),'NULL') + ',' +
	COALESCE(CONVERT(VARCHAR(MAX), Nparalell),'NULL') + ',' +
	COALESCE(CONVERT(VARCHAR(MAX), Nseries),'NULL') + ',' +
	COALESCE(CONVERT(VARCHAR(MAX), NSeriesGroup),'NULL') + ',' +
	COALESCE(CONVERT(VARCHAR(MAX), NParalellGroup),'NULL') + ',' +
	COALESCE(CONVERT(VARCHAR(MAX), Fused),'NULL') + ',' +
	COALESCE(CONVERT(VARCHAR(MAX), VTratioBus),'NULL') + ',' +
	COALESCE(CONVERT(VARCHAR(MAX), NumberLVCaps),'NULL') + ',' +
	COALESCE(CONVERT(VARCHAR(MAX), NumberLVUnits),'NULL') + ',' +
	COALESCE(CONVERT(VARCHAR(MAX), LVKVAr),'NULL') + ',' +
	COALESCE(CONVERT(VARCHAR(MAX), LVKV),'NULL') + ',' +
	COALESCE(CONVERT(VARCHAR(MAX), LVNegReactanceTol),'NULL') + ',' +
	COALESCE(CONVERT(VARCHAR(MAX), LVPosReactanceTol),'NULL') + ',' +
	COALESCE(CONVERT(VARCHAR(MAX), UpperXFRRatio),'NULL') + ',' +
	COALESCE(CONVERT(VARCHAR(MAX), LowerXFRRatio),'NULL') + ',' +
	COALESCE(CONVERT(VARCHAR(MAX), Nshorted),'NULL') + ',' +
	COALESCE(CONVERT(VARCHAR(MAX), BlownFuses),'NULL') + ',' +
	COALESCE(CONVERT(VARCHAR(MAX), BlownGroups),'NULL') + ',' +
	COALESCE(CONVERT(VARCHAR(MAX), Rv),'NULL') + ',' +
	COALESCE(CONVERT(VARCHAR(MAX), Rh),'NULL') + ',' +
	COALESCE(CONVERT(VARCHAR(MAX), Compensated),'NULL') + ',' +
	COALESCE(CONVERT(VARCHAR(MAX), NLowerGroups),'NULL') + ',' +
	COALESCE(CONVERT(VARCHAR(MAX), ShortedGroups),'NULL') +
	COALESCE('''' + CONVERT(VARCHAR(MAX), RelayPTRatio) + '''','NULL') + ',' +
	'0' + ';'
FROM #capBankAttributes
Go

/* Update CapBank Table and View */
DROP VIEW CapBank
GO

DROP TABLE CapacitorBankAttributes
GO

CREATE TABLE CapacitorBankAttributes
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    AssetID INT NOT NULL REFERENCES Asset(ID),
    NumberOfBanks INT NOT NULL,
    CapacitancePerBank INT NOT NULL,
    CktSwitcher VARCHAR(20) NOT NULL,
    MaxKV FLOAT NOT NULL,
    UnitKV FLOAT NOT NULL,
    UnitKVAr FLOAT NOT NULL,
    NegReactanceTol FLOAT NOT NULL,
    PosReactanceTol FLOAT NOT NULL,
    Nparalell INT NOT NULL,
    Nseries INT NOT NULL,
    NSeriesGroup INT NOT NULL DEFAULT(0),
    NParalellGroup INT NOT NULL DEFAULT(0),
    Fused BIT NOT NULL,
    VTratioBus FLOAT NOT NULL DEFAULT(0),
    NumberLVCaps INT NOT NULL DEFAULT(0),
    NumberLVUnits INT NOT NULL DEFAULT(0),
    LVKVAr FLOAT NOT NULL DEFAULT(0),
    LVKV FLOAT NOT NULL DEFAULT(0),
    LVNegReactanceTol FLOAT NOT NULL DEFAULT(0),
    LVPosReactanceTol FLOAT NOT NULL DEFAULT(0),
    UpperXFRRatio FLOAT NOT NULL DEFAULT(0),
    LowerXFRRatio FLOAT NOT NULL DEFAULT(0),
    Nshorted FLOAT NOT NULL,
    BlownFuses INT NOT NULL DEFAULT(0),
    BlownGroups INT NOT NULL DEFAULT(0),
    RelayPTRatioPrimary INT NULL DEFAULT(0),
    RelayPTRatioSecondary INT NULL DEFAULT(0),
    Rv FLOAT NOT NULL DEFAULT(0),
    Rh FLOAT NOT NULL DEFAULT(0),
    Compensated BIT NOT NULL,
    NLowerGroups INT NOT NULL,
    ShortedGroups FLOAT NOT NULL DEFAULT(0),
    Sh FLOAT NOT NULL DEFAULT(0)
)
GO

CREATE VIEW CapBank AS
	SELECT 
		AssetID AS ID,
		AssetKey,
		VoltageKV,
		Description,
		AssetName,
		AssetTypeID,
		Spare,
        NumberOfBanks,
        CapacitancePerBank,
        CktSwitcher,
        MaxKV,
        UnitKV,
        UnitKVAr,
        NegReactanceTol,
        PosReactanceTol,
        Nparalell,
        Nseries,
        NSeriesGroup,
        NParalellGroup,
        Fused,
        VTratioBus,
        NumberLVCaps,
        NumberLVUnits,
        LVKVAr,
        LVKV,
        LVNegReactanceTol,
        LVPosReactanceTol,
        UpperXFRRatio,
        LowerXFRRatio,
        Nshorted,
        BlownFuses,
        BlownGroups,
        RelayPTRatioPrimary,
        RelayPTRatioSecondary,
        Sh,
        Rv,
        Rh,
        Compensated,
        NLowerGroups,
        ShortedGroups
	FROM Asset JOIN CapacitorBankAttributes ON Asset.ID = CapacitorBankAttributes.AssetID
GO

CREATE TRIGGER TR_INSERT_CapBank ON CapBank
INSTEAD OF INSERT AS 
BEGIN
	INSERT INTO Asset (AssetKey, AssetTypeID, Description, AssetName, VoltageKV, Spare)
		SELECT 
			AssetKey AS AssetKey,
			(SELECT ID FROM AssetType WHERE Name = 'CapacitorBank') AS AssetTypeID,
			Description AS Description,
			AssetName AS AssetName,
			VoltageKV AS VoltageKV,
			Spare AS Spare
	FROM INSERTED

	INSERT INTO CapacitorBankAttributes (AssetID, CapacitancePerBank, NumberOfBanks, CktSwitcher, MaxKV, UnitKV, UnitKVAr, NegReactanceTol,
        PosReactanceTol, Nparalell, Nseries, NSeriesGroup, NParalellGroup, Fused, VTratioBus, NumberLVCaps, NumberLVUnits, LVKVAr,
        LVKV, LVNegReactanceTol, LVPosReactanceTol, UpperXFRRatio, LowerXFRRatio, Nshorted, BlownFuses, BlownGroups,
        RelayPTRatioPrimary, RelayPTRatioSecondary,Rv, Rh, Compensated, NLowerGroups, ShortedGroups,Sh)
		SELECT 
			(SELECT ID FROM Asset WHERE AssetKey = INSERTED.AssetKey) AS AssetID,
			CapacitancePerBank AS CapacitancePerBank,
            NumberOfBanks AS NumberOfBanks,
            CktSwitcher AS CktSwitcher,
            MaxKV AS MaxKV,
            UnitKV AS UnitKV,
            UnitKVAr AS UnitKVAr,
            NegReactanceTol AS NegReactanceTol,
            PosReactanceTol AS PosReactanceTol,
            Nparalell AS Nparalell,
            Nseries AS Nseries,
            NSeriesGroup AS NSeriesGroup,
            NParalellGroup AS NParalellGroup,
            Fused AS Fused,
            VTratioBus AS VTratioBus,
            NumberLVCaps AS NumberLVCaps,
            NumberLVUnits AS NumberLVUnits,
            LVKVAr AS LVKVAr,
            LVKV AS LVKV,
            LVNegReactanceTol AS LVNegReactanceTol,
            LVPosReactanceTol AS LVPosReactanceTol,
            UpperXFRRatio AS UpperXFRRatio,
            LowerXFRRatio AS LowerXFRRatio,
            Nshorted AS Nshorted,
            BlownFuses AS BlownFuses,
            BlownGroups AS BlownGroups,
            RelayPTRatioPrimary AS RelayPTRatioPrimary,
            RelayPTRatioSecondary AS RelayPTRatioPrimary,
            Rv AS Rv,
            Rh AS Rh,
            Compensated AS Compensated,
            NLowerGroups AS NLowerGroups,
            ShortedGroups AS ShortedGroups,
            Sh as Sh
	    FROM INSERTED
END
GO

CREATE TRIGGER TR_UPDATE_CapBank ON CapBank
INSTEAD OF UPDATE AS
BEGIN
IF (UPDATE(AssetKey) OR UPDATE(Description) OR UPDATE (AssetName) OR UPDATE(VoltageKV) OR Update(Spare) )
	BEGIN
		UPDATE Asset
		SET
			Asset.AssetKey = INSERTED.AssetKey,
			Asset.Description = INSERTED.Description,
			Asset.AssetName = INSERTED.AssetName,
			Asset.VoltageKV = INSERTED.VoltageKV,
			Asset.Spare = INSERTED.Spare
		FROM
			ASSET 
		INNER JOIN
			INSERTED
		ON 
			INSERTED.ID = ASSET.ID;
	END
	UPDATE CapacitorBankAttributes
		SET
			CapacitorBankAttributes.CapacitancePerBank = INSERTED.CapacitancePerBank,
            CapacitorBankAttributes.NumberOfBanks = INSERTED.NumberOfBanks,
            CapacitorBankAttributes.CktSwitcher = INSERTED.CktSwitcher,
            CapacitorBankAttributes.MaxKV = INSERTED.MaxKV,
            CapacitorBankAttributes.UnitKV = INSERTED.UnitKV,
            CapacitorBankAttributes.UnitKVAr = INSERTED.UnitKVAr,
            CapacitorBankAttributes.NegReactanceTol = INSERTED.NegReactanceTol,
            CapacitorBankAttributes.PosReactanceTol = INSERTED.PosReactanceTol,
            CapacitorBankAttributes.Nparalell = INSERTED.Nparalell,
            CapacitorBankAttributes.Nseries = INSERTED.Nseries,
            CapacitorBankAttributes.NSeriesGroup = INSERTED.NSeriesGroup,
            CapacitorBankAttributes.NParalellGroup = INSERTED.NParalellGroup,
            CapacitorBankAttributes.Fused = INSERTED.Fused,
            CapacitorBankAttributes.VTratioBus = INSERTED.VTratioBus,
            CapacitorBankAttributes.NumberLVCaps = INSERTED.NumberLVCaps,
            CapacitorBankAttributes.NumberLVUnits = INSERTED.NumberLVUnits,
            CapacitorBankAttributes.LVKVAr = INSERTED.LVKVAr,
            CapacitorBankAttributes.LVKV = INSERTED.LVKV,
            CapacitorBankAttributes.LVNegReactanceTol = INSERTED.LVNegReactanceTol,
            CapacitorBankAttributes.LVPosReactanceTol = INSERTED.LVPosReactanceTol,
            CapacitorBankAttributes.UpperXFRRatio = INSERTED.UpperXFRRatio,
            CapacitorBankAttributes.LowerXFRRatio = INSERTED.LowerXFRRatio,
            CapacitorBankAttributes.Nshorted = INSERTED.Nshorted,
            CapacitorBankAttributes.BlownFuses = INSERTED.BlownFuses,
            CapacitorBankAttributes.BlownGroups = INSERTED.BlownGroups,
            CapacitorBankAttributes.RelayPTRatioPrimary = INSERTED.RelayPTRatioPrimary,
            CapacitorBankAttributes.RelayPTRatioSecondary = INSERTED.RelayPTRatioSecondary,
            CapacitorBankAttributes.Rv = INSERTED.Rv,
            CapacitorBankAttributes.Rh = INSERTED.Rh,
            CapacitorBankAttributes.Compensated = INSERTED.Compensated,
            CapacitorBankAttributes.NLowerGroups = INSERTED.NLowerGroups,
            CapacitorBankAttributes.ShortedGroups = INSERTED.ShortedGroups,
            CapacitorBankAttributes.Sh = INSERTED.Sh
		FROM
			CapacitorBankAttributes 
	INNER JOIN
		INSERTED
	ON 
		INSERTED.ID = CapacitorBankAttributes.AssetID;
END
GO