server1/SQL/ACAServer.sql

-- Active: 1752920752747@@192.1.2.61@5236@EQM_CESHI


#对比项
SELECT *
FROM tACACompareItems;


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


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


#对比项一致性对比时段信息
SELECT *
FROM tACADetectPeriod;


#报警信息
SELECT *
FROM tACAAlarmInfo;

#倒叙获取报警信息
SELECT *
FROM tACAAlarmInfo
ORDER BY PKID DESC;



#录音文件信息
SELECT *
FROM tACARecordFile;



#录音文件根据对比项ID归为一组
SELECT ItemID, COUNT(*) AS FileCount
FROM tACARecordFile
GROUP BY ItemID;


#清空报警信息内容
DELETE FROM tACAAlarmInfo;


SELECT *
FROM tSystemConfig;


#插入报警信息
INSERT INTO tACAAlarmInfo (
    ItemID, ItemName, AlarmType, SoundCardNum, SoundCardName, SoundCardRoadNum, CompareRoadNum, CompareRoadName, CompareRoadType, 
    AlarmStartTime, AlarmEndTime, AlarmDuration, AlarmFilePath, FileAlarmStartPos, MainRoadPKID )
VALUES (
    @itemID, @itemName, @alarmType, @soundCardNum, @soundCardName, @soundCardRoadNum, @compareRoadNum, @compareRoadName, @compareRoadType, 
    @alarmStartTime, @alarmEndTime, @alarmDuration, @alarmFilePath, @fileAlarmStartPos, @mainRoadPKID);
SELECT LAST_INSERT_ID() AS pkid;

#同时插入两条报警信息
-- 先插入主通道报警信息, 获取其主键 pkid
BEGIN
INSERT INTO tACAAlarmInfo (
    ItemID, ItemName, AlarmType, SoundCardNum, SoundCardName, SoundCardRoadNum, CompareRoadNum, CompareRoadName, CompareRoadType, 
    AlarmStartTime, AlarmEndTime, AlarmDuration, AlarmFilePath, FileAlarmStartPos)
VALUES (
    @itemID1, @itemName1, @alarmType1, @soundCardNum1, @soundCardName1, @soundCardRoadNum1, @compareRoadNum1, @compareRoadName1, @compareRoadType1, 
    @alarmStartTime1, @alarmEndTime1, @alarmDuration1, @alarmFilePath1, @fileAlarmStartPos1);
-- 获取主通道报警信息的
SELECT MAX(PKID) INTO pkid FROM tACAAlarmInfo;
-- 插入第二条报警信息，MainRoadPKID 就是上面获取的 pkid
INSERT INTO tACAAlarmInfo (
    ItemID, ItemName, AlarmType, SoundCardNum, SoundCardName, SoundCardRoadNum, CompareRoadNum, CompareRoadName, CompareRoadType, 
    AlarmStartTime, AlarmEndTime, AlarmDuration, AlarmFilePath, FileAlarmStartPos, MainRoadPKID)
VALUES (
    @itemID2, @itemName2, @alarmType2, @soundCardNum2, @soundCardName2, @soundCardRoadNum2, @compareRoadNum2, @compareRoadName2, @compareRoadType2,
    @alarmStartTime2, @alarmEndTime2, @alarmDuration2, @alarmFilePath2, @fileAlarmStartPos2, pkid);

END;




#创建达梦数据库存储过程
CREATE OR REPLACE PROCEDURE ACAS_InsertTwoAlarmInfo(
    itemID1 IN INT, itemName1 IN VARCHAR(100), alarmType1 IN INT,
    soundCardNum1 IN INT, soundCardName1 IN VARCHAR(100), soundCardRoadNum1 IN INT,
    compareRoadNum1 IN INT, compareRoadName1 IN VARCHAR(100), compareRoadType1 IN INT,
    alarmStartTime1 IN DATETIME, alarmEndTime1 IN DATETIME, alarmDuration1 IN INT,
    alarmFilePath1 IN VARCHAR(255), fileAlarmStartPos1 IN INT,
    itemID2 IN INT, itemName2 IN VARCHAR(100), alarmType2 IN INT,
    soundCardNum2 IN INT, soundCardName2 IN VARCHAR(100), soundCardRoadNum2 IN INT,
    compareRoadNum2 IN INT, compareRoadName2 IN VARCHAR(100), compareRoadType2 IN INT,
    alarmStartTime2 IN DATETIME, alarmEndTime2 IN DATETIME, alarmDuration2 IN INT,
    alarmFilePath2 IN VARCHAR(255), fileAlarmStartPos2 IN INT
)
AS
    pkid INT 2;
BEGIN
    INSERT INTO tACAAlarmInfo (
        ItemID, ItemName, AlarmType, SoundCardNum, SoundCardName, SoundCardRoadNum, CompareRoadNum, CompareRoadName, CompareRoadType,
        AlarmStartTime, AlarmEndTime, AlarmDuration, AlarmFilePath, FileAlarmStartPos
    ) VALUES (
        itemID1, itemName1, alarmType1, soundCardNum1, soundCardName1, soundCardRoadNum1, compareRoadNum1, compareRoadName1, compareRoadType1,
        alarmStartTime1, alarmEndTime1, alarmDuration1, alarmFilePath1, fileAlarmStartPos1
    );
    pkid := SCOPE_IDENTITY();
    INSERT INTO tACAAlarmInfo (
        ItemID, ItemName, AlarmType, SoundCardNum, SoundCardName, SoundCardRoadNum, CompareRoadNum, CompareRoadName, CompareRoadType,
        AlarmStartTime, AlarmEndTime, AlarmDuration, AlarmFilePath, FileAlarmStartPos, MainRoadPKID
    ) VALUES (
        itemID2, itemName2, alarmType2, soundCardNum2, soundCardName2, soundCardRoadNum2, compareRoadNum2, compareRoadName2, compareRoadType2,
        alarmStartTime2, alarmEndTime2, alarmDuration2, alarmFilePath2, fileAlarmStartPos2, pkid
    );
END;

# 调用存储过程
CALL ACAS_InsertTwoAlarmInfo(
    @itemID1, @itemName1, @alarmType1,
    @soundCardNum1, @soundCardName1, @soundCardRoadNum1,
    @compareRoadNum1, @compareRoadName1, @compareRoadType1,
    @alarmStartTime1, @alarmEndTime1, @alarmDuration1,
    @alarmFilePath1, @fileAlarmStartPos1,
    @itemID2, @itemName2, @alarmType2,
    @soundCardNum2, @soundCardName2, @soundCardRoadNum2,
    @compareRoadNum2, @compareRoadName2, @compareRoadType2,
    @alarmStartTime2, @alarmEndTime2, @alarmDuration2,
    @alarmFilePath2, @fileAlarmStartPos2
);



#插入录音文件信息
INSERT INTO tACARecordFile (
    ItemID, ItemName, ItemRoadNum, ItemRoadName, SoundCardNum, SoundCardRoadNum,
    FileStartTime, FileEndTime, FileDuration, FilePath
) VALUES (
    @itemID, @itemName, @itemRoadNum, @itemRoadName, @soundCardNum, @soundCardRoadNum,
    @fileStartTime, @fileEndTime, @fileDuration, @filePath
);


#更新录音文件信息
UPDATE tACARecordFile
SET FileEndTime = @fileEndTime, FilePath = @filePath, FileDuration = @fileDuration
WHERE ItemID = @itemID AND ItemRoadNum = @itemRoadNum 
    AND SoundCardNum = @soundCardNum AND SoundCardRoadNum = @soundCardRoadNum
    AND FileStartTime = @fileStartTime;