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
ORDER BY PKID DESC;




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

#删除报警信息表格
DROP TABLE IF EXISTS tACAAlarmInfo;


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

#删除早于某个时间点的报警信息
DELETE FROM tACAAlarmInfo
WHERE AlarmStartTime < @timePoint;

DELETE FROM tACAAlarmInfo
WHERE AlarmStartTime < :timePoint;


#获取早于某个时间点并且报警文件未删除的报警信息
SELECT *
FROM tACAAlarmInfo
WHERE AlarmStartTime < @timePoint AND FileState != 3;

#更新报警文件状态
UPDATE tACAAlarmInfo
SET FileState = @fileState
WHERE PKID = @pkid;





#录音文件信息
SELECT *
FROM tACARecordFile
ORDER BY PKID DESC;


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



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


#将所有的录音文件状态修改为已删除
UPDATE tACARecordFile
SET FileState = 3
WHERE FileState != 3;

#删除一个时间点之前的所有录音文件信息
DELETE FROM tACARecordFile
WHERE FileStartTime < @timePoint;


DELETE FROM tACARecordFile
WHERE FileStartTime < :timePoint;

#获取早于某个时间点并且未被删除的的录音文件信息
SELECT *
FROM tACARecordFile
WHERE FileStartTime < @timePoint AND FileState != 3;