server1/SQL/ACAServer.sql

-- Active: 1756343447531@@192.1.2.49@32124@eqm


#对比项
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
LIMIT 100;


#获取声卡信息
SELECT *
FROM tACASoundCardPCMChannels;



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

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


#插入报警信息
INSERT INTO tACAAlarmInfo (
    ItemID, ItemName, AlarmType, SoundCardName, SoundCardPCMName, CompareRoadNum, CompareRoadName, CompareRoadType, 
    AlarmStartTime, AlarmEndTime, AlarmDuration, AlarmFilePath, FileAlarmStartPos, FileState )
VALUES (
    @itemID, @itemName, @alarmType, @soundCardName, @soundCardPCMName, @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, SoundCardName, SoundCardPCMName,
    FileStartTime, FileEndTime, FileDuration, FilePath, FileState
) VALUES (
    @itemID, @itemName, @itemRoadNum, @itemRoadName, @soundCardName, @soundCardPCMName,
    @fileStartTime, @fileEndTime, @fileDuration, @filePath, @fileState
);



#更新录音文件信息
UPDATE tACARecordFile
SET FileEndTime = @fileEndTime, FilePath = @filePath, FileDuration = @fileDuration, FileState = @fileState
WHERE ItemID = @itemID AND ItemRoadNum = @itemRoadNum 
    AND SoundCardName = @soundCardName AND SoundCardPCMName = @soundCardPCMName
    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;

#插入声卡通道信息
INSERT INTO tACASoundCardPCMChannels (
    SoundCardName, PCMName, PCMDesc, IOID, SoundCardNum, SoundCardRoadNum
) VALUES (
    @soundCardName, @pcmName, @pcmDesc, @ioid, @soundCardNum, @soundCardRoadNum
);


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



#删除PCM通道信息
DELETE FROM tACASoundCardPCMChannels
WHERE PKID = @pkid;

#删除所有的PCM通道信息
DELETE FROM tACASoundCardPCMChannels;

#获取PCM通道信息
SELECT *
FROM tACASoundCardPCMChannels;


#更新声卡通道信息
UPDATE tACASoundCardPCMChannels
SET SoundCardDriver = @soundCardDriver, PCMName = @pcmName, PCMDesc = @pcmDesc, SoundCardNum = @soundCardNum, SoundCardName = @soundCardName, SoundCardRoadNum = @soundCardRoadNum
WHERE PKID = @pkid;

DELETE FROM tACACompareItems
WHERE ItemID = :itemID;