server1/SQL/ACASetting.sql

-- Active: 1751960213665@@192.1.2.61@5236@EQM_CESHI




SELECT *
FROM tServiceSetInfo;



#获取所有的对比项信息
SELECT *
FROM tACACompareItems
LEFT JOIN tACACompareItemRoad ON tACACompareItems.ItemID = tACACompareItemRoad.ItemID;

#只获取对比项信息
SELECT *
FROM tACACompareItems;

#获取对比项的通道信息
SELECT *
FROM tACACompareItemRoad;


#插入一条对比项信息
INSERT INTO tACACompareItems 
    (   ItemID, ItemName, ItemEnable, RoadCount, 
        SilentEnable, SilentThreshold, SilentDuration, SilentSensitivity, 
        OverloadEnable, OverloadThreshold, OverloadDuration, OverloadSensitivity, 
        PhaseEnable, PhaseThreshold, PhaseDuration, PhaseSensitivity )
VALUES
    ( @itemID, @itemName, @itemEnable, @roadCount, 
      @silentEnable, @silentThreshold, @silentDuration, @silentSensitivity, 
      @overloadEnable, @overloadThreshold, @overloadDuration, @overloadSensitivity, 
      @phaseEnable, @phaseThreshold, @phaseDuration, @phaseSensitivity );

#插入一条对比项通道信息
INSERT INTO tACACompareItemRoad 
    ( ItemID, RoadNum, RoadName, RoadRecordEnable, SoundCardNum, 
      SoundCardID, SoundCardName, SoundCardRoadNum, ChannelID, ChannelName )
VALUES
    ( @itemID, @roadNum, @roadName, @roadRecordEnable, @soundCardNum, 
      @soundCardID, @soundCardName, @soundCardRoadNum, @channelID, @channelName );



#通过ID更新对比项信息
UPDATE tACACompareItems
SET ItemName = @itemName, 
    ItemEnable = @itemEnable,
    RoadCount = @roadCount,
    SilentEnable = @silentEnable,
    SilentThreshold = @silentThreshold,
    SilentDuration = @silentDuration,
    SilentSensitivity = @silentSensitivity,
    OverloadEnable = @overloadEnable,
    OverloadThreshold = @overloadThreshold,
    OverloadDuration = @overloadDuration,
    OverloadSensitivity = @overloadSensitivity,
    PhaseEnable = @phaseEnable,
    PhaseThreshold = @phaseThreshold,
    PhaseDuration = @phaseDuration,
    PhaseSensitivity = @phaseSensitivity
WHERE ItemID = @itemID;




#通过ItemID和RoadNum更新对比项通道信息
UPDATE tACACompareItemRoad
SET RoadName = @roadName,
    RoadRecordEnable = @roadRecordEnable,
    SoundCardNum = @soundCardNum,
    SoundCardID = @soundCardID,
    SoundCardName = @soundCardName,
    SoundCardRoadNum = @soundCardRoadNum,
    ChannelID = @channelID,
    ChannelName = @channelName
WHERE ItemID = @ItemID AND RoadNum = @roadNim;


#删除一个对比项信息
DELETE FROM tACACompareItems
WHERE ItemID = @itemID;

#删除一个对比项的所有通道信息
DELETE FROM tACACompareItemRoad
WHERE ItemID = @itemID;


#删除一个对比项单个通道信息
DELETE FROM tACACompareItemRoad
WHERE ItemID = @itemID AND RoadNum = @roadNum;



CREATE OR REPLACE PROCEDURE update_compare_item_and_road(
    v_ItemID INT,
    v_ItemName VARCHAR,
    v_ItemEnable BIT,
    v_RoadCount INT,
    v_RoadNum INT,
    v_RoadName VARCHAR
)
AS
BEGIN
    -- 修改主表
    UPDATE tACACompareItems
    SET ItemName = v_ItemName,
        ItemEnable = v_ItemEnable,
        RoadCount = v_RoadCount
    WHERE ItemID = v_ItemID;

    -- 修改子表，新增、修改或删除
    FOR rec IN (SELECT * FROM tACACompareItemRoad WHERE ItemID = v_ItemID)
    LOOP
        IF rec.RoadNum = v_RoadNum THEN
            -- 更新已存在的通道信息
            UPDATE tACACompareItemRoad
            SET RoadName = v_RoadName
            WHERE ItemID = v_ItemID AND RoadNum = v_RoadNum;
            RETURN;
        END IF;
    END LOOP;
    -- 如果没有找到对应的通道，则插入新的通道信息
    INSERT INTO tACACompareItemRoad (ItemID, RoadNum, RoadName)
    VALUES (v_ItemID, v_RoadNum, v_RoadName);
    -- 如果需要删除其他通道，可以在这里添加逻辑
END;
CALL update_compare_item_and_road(1, '新名称', 1, 2, 1, '主通道');


#查看tACASystemConfig表的所有数据
SELECT *
FROM tACASystemConfig;


#插入一个数据到 tACASystemConfig 表
INSERT INTO tACASystemConfig (ConfigKey, ConfigValue, ConfigDesc)
VALUES ('ExampleKey', 'ExampleValue', 'This is an example configuration');

#更新一条数据，没有则插入数据
MERGE INTO tACASystemConfig t
USING (SELECT 1 FROM dual) s
ON (t.ConfigKey = @configKey)
WHEN MATCHED THEN
    UPDATE SET 
        ConfigValue = @configValue,
        ConfigDesc = @configDesc,
        UpdateTime = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN
    INSERT (ConfigKey, ConfigValue, ConfigDesc)
    VALUES (@configKey, @configValue, @configDesc);


#获取系统设置信息
SELECT *
FROM tACASystemConfig;

#删除一条系统设置信息
DELETE FROM tACASystemConfig
WHERE PKID = 1;


#获取检测时段
SELECT *
FROM tACADetectPeriod;

#插入检测时段
INSERT INTO tACADetectPeriod (ItemID, IsDetect, WeekType, CDate, TimeStart, TimeEnd, ApplySlient, ApplyOverload, ApplyPhase, ApplyNoise)
VALUES (@itemID, @isDetect, @weekType, @cDate, @timeStart, @timeEnd, @applySlient, @applyOverload, @applyPhase, @applyNoise);

#删除检测时段
DELETE FROM tACADetectPeriod
WHERE ItemID = @itemID;