server1/SQL/ACASetting.sql

-- Active: 1752718919967@@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, SoundCardName, SoundCardPCMName)
VALUES
    ( @itemID, @roadNum, @roadName, @roadRecordEnable, @soundCardName, @soundCardPCMName);



#通过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);

#GBase版本
MERGE INTO tACASystemConfig AS t
USING (SELECT @configKey AS ConfigKey, CAST(@configValue AS TEXT) AS ConfigValue, @configDesc AS ConfigDesc) AS s
ON (t.ConfigKey = s.ConfigKey)
WHEN MATCHED THEN
    UPDATE SET 
        t.ConfigValue = s.ConfigValue,
        t.ConfigDesc = s.ConfigDesc,
        t.UpdateTime = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN
    INSERT (ConfigKey, ConfigValue, ConfigDesc)
    VALUES (s.ConfigKey, s.ConfigValue, s.ConfigDesc);

-- 先尝试更新
UPDATE tACASystemConfig 
SET ConfigValue = @configValue,
    ConfigDesc = @configDesc,
    UpdateTime = CURRENT_TIMESTAMP
WHERE ConfigKey = @configKey;

-- 如果没有更新到行，则插入
IF DB_ROWCOUNT() = 0 THEN
    INSERT INTO tACASystemConfig (ConfigKey, ConfigValue, ConfigDesc)
    VALUES (@configKey, @configValue, @configDesc);
END IF;




#获取系统设置信息
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;


UPDATE tACASystemConfig (ConfigKey, ConfigValue, ConfigDesc)
VALUES ('ACAS_SoundCardInfo', 
'[{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Discard all samples (playback) or generate zero samples (capture)","PCMName":"null","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Playback/recording through the PulseAudio sound server","PCMName":"default","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Default Audio Device","PCMName":"sysdefault","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"IEC958 (S/PDIF) Digital Audio Output","PCMName":"iec958","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Rate Converter Plugin Using Libav/FFmpeg Library","PCMName":"lavrate","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Rate Converter Plugin Using Samplerate Library","PCMName":"samplerate","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Rate Converter Plugin Using Speex Resampler","PCMName":"speexrate","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"JACK Audio Connection Kit","PCMName":"jack","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Open Sound System","PCMName":"oss","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"PulseAudio Sound Server","PCMName":"pulse","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Plugin for channel upmix (4,6,8)","PCMName":"upmix","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Plugin for channel downmix (stereo) with a simple spacialization","PCMName":"vdownmix","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Ensoniq AudioPCI, ES1371 DAC2/ADC\nDirect hardware device without any conversions","PCMName":"hw:CARD=AudioPCI,DEV=0","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"Output","PCMDesc":"Ensoniq AudioPCI, ES1371 DAC1\nDirect hardware device without any conversions","PCMName":"hw:CARD=AudioPCI,DEV=1","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Ensoniq AudioPCI, ES1371 DAC2/ADC\nHardware device with all software conversions","PCMName":"plughw:CARD=AudioPCI,DEV=0","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"Output","PCMDesc":"Ensoniq AudioPCI, ES1371 DAC1\nHardware device with all software conversions","PCMName":"plughw:CARD=AudioPCI,DEV=1","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Ensoniq AudioPCI, ES1371 DAC2/ADC\nDefault Audio Device","PCMName":"sysdefault:CARD=AudioPCI","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Ensoniq AudioPCI, ES1371 DAC2/ADC\nFront output / input","PCMName":"front:CARD=AudioPCI,DEV=0","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Ensoniq AudioPCI, ES1371 DAC2/ADC\nRear speakers","PCMName":"rear:CARD=AudioPCI,DEV=0","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"Output","PCMDesc":"Ensoniq AudioPCI, ES1371 DAC2/ADC\n4.0 Surround output to Front and Rear speakers","PCMName":"surround40:CARD=AudioPCI,DEV=0","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"Output","PCMDesc":"Ensoniq AudioPCI, ES1371 DAC2/ADC\nIEC958 (S/PDIF) Digital Audio Output","PCMName":"iec958:CARD=AudioPCI,DEV=0","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"Output","PCMDesc":"Ensoniq AudioPCI, ES1371 DAC2/ADC\nDirect sample mixing device","PCMName":"dmix:CARD=AudioPCI,DEV=0","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"Output","PCMDesc":"Ensoniq AudioPCI, ES1371 DAC1\nDirect sample mixing device","PCMName":"dmix:CARD=AudioPCI,DEV=1","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"Input","PCMDesc":"Ensoniq AudioPCI, ES1371 DAC2/ADC\nDirect sample snooping device","PCMName":"dsnoop:CARD=AudioPCI,DEV=0","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Ensoniq AudioPCI\nUSB Stream Output","PCMName":"usbstream:CARD=AudioPCI","SubDevNum":"未知设备编号"}]',
 '系统的声卡信息');

 UPDATE tACASystemConfig
 SET 
        ConfigValue = '[{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Discard all samples (playback) or generate zero samples (capture)","PCMName":"null","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Playback/recording through the PulseAudio sound server","PCMName":"default","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Default Audio Device","PCMName":"sysdefault","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"IEC958 (S/PDIF) Digital Audio Output","PCMName":"iec958","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Rate Converter Plugin Using Libav/FFmpeg Library","PCMName":"lavrate","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Rate Converter Plugin Using Samplerate Library","PCMName":"samplerate","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Rate Converter Plugin Using Speex Resampler","PCMName":"speexrate","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"JACK Audio Connection Kit","PCMName":"jack","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Open Sound System","PCMName":"oss","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"PulseAudio Sound Server","PCMName":"pulse","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Plugin for channel upmix (4,6,8)","PCMName":"upmix","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Plugin for channel downmix (stereo) with a simple spacialization","PCMName":"vdownmix","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Ensoniq AudioPCI, ES1371 DAC2/ADC\nDirect hardware device without any conversions","PCMName":"hw:CARD=AudioPCI,DEV=0","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"Output","PCMDesc":"Ensoniq AudioPCI, ES1371 DAC1\nDirect hardware device without any conversions","PCMName":"hw:CARD=AudioPCI,DEV=1","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Ensoniq AudioPCI, ES1371 DAC2/ADC\nHardware device with all software conversions","PCMName":"plughw:CARD=AudioPCI,DEV=0","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"Output","PCMDesc":"Ensoniq AudioPCI, ES1371 DAC1\nHardware device with all software conversions","PCMName":"plughw:CARD=AudioPCI,DEV=1","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Ensoniq AudioPCI, ES1371 DAC2/ADC\nDefault Audio Device","PCMName":"sysdefault:CARD=AudioPCI","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Ensoniq AudioPCI, ES1371 DAC2/ADC\nFront output / input","PCMName":"front:CARD=AudioPCI,DEV=0","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Ensoniq AudioPCI, ES1371 DAC2/ADC\nRear speakers","PCMName":"rear:CARD=AudioPCI,DEV=0","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"Output","PCMDesc":"Ensoniq AudioPCI, ES1371 DAC2/ADC\n4.0 Surround output to Front and Rear speakers","PCMName":"surround40:CARD=AudioPCI,DEV=0","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"Output","PCMDesc":"Ensoniq AudioPCI, ES1371 DAC2/ADC\nIEC958 (S/PDIF) Digital Audio Output","PCMName":"iec958:CARD=AudioPCI,DEV=0","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"Output","PCMDesc":"Ensoniq AudioPCI, ES1371 DAC2/ADC\nDirect sample mixing device","PCMName":"dmix:CARD=AudioPCI,DEV=0","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"Output","PCMDesc":"Ensoniq AudioPCI, ES1371 DAC1\nDirect sample mixing device","PCMName":"dmix:CARD=AudioPCI,DEV=1","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"Input","PCMDesc":"Ensoniq AudioPCI, ES1371 DAC2/ADC\nDirect sample snooping device","PCMName":"dsnoop:CARD=AudioPCI,DEV=0","SubDevNum":"未知设备编号"},{"CardNum":"未知声卡","IOID":"未知类型","PCMDesc":"Ensoniq AudioPCI\nUSB Stream Output","PCMName":"usbstream:CARD=AudioPCI","SubDevNum":"未知设备编号"}]',
        ConfigDesc = '系统的声卡信息'
WHERE ConfigKey = 'ACAS_SoundCardInfo';