SecurePlay/SQL/SecurePlay.sqlbook

-- SQLBook: Code
-- Active: 1725354130110@@192.1.3.105@1433@EQM_BJ

#算子信息表
SELECT *
FROM "tAction";

#插入信息
INSERT INTO "tAction" ("ActionId","ActionTaskid","ActionName")
VALUES ('OD210_022_001835_029-CYl65jlE', 8, '人员计数');

#插入信息，带有参数
INSERT INTO "tAction" ("ActionId","ActionTaskid","ActionName")
VALUES (:actionID, :actionTaskID, :actionName);

#更新信息
UPDATE "tAction"
SET "ActionId" = 'OD447_022_000070_001'
WHERE "PKID" = '11';

#删除信息
DELETE FROM "tAction"
WHERE "ActionId" = '123';

#带参数的删除
DELETE FROM "tAction"
WHERE "PKID" = :actionID;

#重置自增键
DBCC CHECKIDENT ('tAction', RESEED, 0);
-- SQLBook: Code
-- Active: 1725354130110@@192.1.3.105@1433@EQM_BJ
#算子摄像头信息表，将摄像机和算法关联起来
SELECT *
FROM "tActionCamer";

#根据摄像头ID，从tAction表中查询算法信息
SELECT "tActionCamer"."CamerId", "tActionCamer"."ActionId", "tAction"."ActionName", "tAction"."ActionTaskid"
FROM "tActionCamer"
LEFT JOIN "tAction"
ON "tActionCamer"."ActionId" = "tAction"."ActionId";


SELECT *
FROM "tActionCamer"
LEFT JOIN "tAction"
ON "tActionCamer"."ActionId" = "tAction"."ActionId";

#插入信息
INSERT INTO "tActionCamer" ("ActionId","CamerId")
VALUES (:actionID, :cameraID);

#删除信息
DELETE FROM "tActionCamer"
WHERE "CamerId" = :CameraID;
-- SQLBook: Code
-- Active: 1746948908373@@192.1.3.105@1433@EQM_BJ
#摄像头信息表
SELECT *
FROM "tCamerinfo";

SELECT cam.CamerName AS camerinfo
FROM "tCamerinfo" AS cam;

#插入设备信息
INSERT INTO "tCamerinfo" ("CamerId","CamerName","CamerIp","CamerPort","CamerUsr","CamerPwd","CamerChannel","CamerSerial","CamerType","CamerUrl")
VALUES (:camerID, :camerName, :camerIP, :camerPort, :camerUser, :camerPwd, :camerChannel, :camerSerial, :camerType, :camerUrl);


#更新数据值，根据CmaerID更新
UPDATE "tCamerinfo"
SET "CamerName" = :camerName, "CamerIp" = :camerIP, "CamerPort" = :camerPort, "CamerUsr" = :camerUser, "CamerPwd" = :camerPwd, "CamerChannel" = :camerChannel, "CamerSerial" = :camerSerial, "CamerType" = :camerType, "CamerUrl" = :camerUrl
WHERE "CamerId" = :camerID;

#根据CamerId删除数据
DELETE FROM "tCamerinfo"
WHERE "CamerId" = :camerID;

#删除所有的数据
DELETE FROM "tCamerinfo";
-- SQLBook: Code
#直播间摄像头关联表
SELECT *
FROM "tRoomCamer";

SELECT *
FROM "tRoomInfo";



#关联算法信息，"tRoomInfo"."PKID" = "tRoomCamer"."RoomId"
SELECT "tRoomCamer"."PKID", "tRoomCamer"."RoomId", "tRoomCamer"."CamerId", "tActionCamer"."ActionId", "tRoomInfo"."ChnId", "tRoomInfo"."Rtype", "tRoomInfo"."Rname"
FROM "tRoomCamer"
LEFT JOIN "tActionCamer"
ON "tRoomCamer"."CamerId" = "tActionCamer"."CamerId"
LEFT JOIN "tRoomInfo"
ON "tRoomCamer"."RoomId" = "tRoomInfo"."PKID";


#插入信息
INSERT INTO "tRoomCamer" ("RoomId","CamerId")
VALUES (:roomID, :camerID);
-- SQLBook: Code
-- Active: 1725354130110@@192.1.3.105@1433@EQM_BJ
#报警信息表
SELECT *
FROM "tAlarmInfo";

#倒序显示报警信息
SELECT *
FROM "tAlarmInfo"
ORDER BY "PKID" DESC;

#插入报警信息
INSERT INTO "tAlarmInfo" ("AlarmId","StartTime","CreateTime","EndTime","bBox","PicUrl","Appid","ActionId","ActionDes","CamerId","RoomId","chnID","State","FaceIdList","FaceNameList","OnWork")
VALUES(:AlarmID, :StartTime, :CreateTime, :EndTime, :bBox, :PicUrl, :AppID, :ActionID, :ActionDes, :CamerID, :RoomID, :chnID, :Stat, :FaceIDList, :FaceNameList, :OnWork);

INSERT INTO "tAlarmInfo" ("AlarmId","StartTime","CreateTime","EndTime","bBox","PicUrl","Appid","ActionId","ActionDes","CamerId","RoomId","chnID","State","FaceIdList","FaceNameList","OnWork")
OUTPUT INSERTED."PKID"
VALUES(@alarmID, @startTime, @createTime, @endTime, @bbox, @picUrl, @appID, @actionID, @actionDes, @camerID, @roomID, @chnid, @state, @faceIDList, @faceNameList, @onWork);



#插入报警信息示例
INSERT INTO "tAlarmInfo" ("AlarmId","StartTime","CreateTime","EndTime","bBox","PicUrl","Appid","ActionId","ActionDes","CamerId","RoomId","chnID","State","FaceIdList","FaceNameList","OnWork")
VALUES('0', '2024-5-08 14:00:00', '2024-10-08 14:00:00','','112,112,112,112','PicURL', '1', '2', '报警测试', '3', '4', '5', '6', 'FaceIDList','FaceNameList', '1');
SELECT @@IDENTITY AS PKID;
SELECT SCOPE_IDENTITY() AS PKID;

#删除一行
DELETE FROM "tAlarmInfo"
WHERE "PKID" = :pkid;

#更新结束时间
UPDATE "tAlarmInfo"
SET "EndTime" = :EndTime
WHERE ("chnID" = :ChannelID AND "RoomId" = :RoomID AND "CamerId" = :CamerID AND "ActionId" = :ActionID);

UPDATE "tAlarmInfo"
SET "EndTime" = @EndTime
WHERE ("chnID" = @ChannelID AND "RoomId" = @RoomID AND "CamerId" = @CamerID AND "ActionId" = @ActionID);

#更新结束时间，通过PKID
UPDATE "tAlarmInfo"
SET "EndTime" = :EndTime
WHERE "PKID" = :pkid;


#更新人员列表，通过PKDI
UPDATE "tAlarmInfo"
SET "FaceIdList" = @faceIDList, "FaceNameList" = @faceNameList, "ActionDes" = @actionDes, "CreateTime" = GetDate()
WHERE "PKID" = @pkid;

#更新人员列表，通过PKID
UPDATE "tAlarmInfo"
SET "FaceIdList" = :faceIDList, "FaceNameList" = :faceNameList, "ActionDes" = :actionDes, "CreateTime" = GetDate()
WHERE "PKID" = :pkid;

#获取某个频率的最大PKID
SELECT MAX("PKID") AS MaxPKID
FROM "tAlarmInfo"
WHERE "chnID" = :ChannelID AND "Appid" = :AppID;


#清空在岗离岗报警的在岗人员信息
UPDATE "tAlarmInfo"
SET "FaceIdList" = '', "FaceNameList" = '', "ActionDes" = @actionDes, "CreateTime" = GetDate()
WHERE "PKID" = @pkid AND "OnWork" = @onWork;


#结束人员计数的报警，通过频率ID和应用ID，结束时间等于2000-01-01 00:00:00的报警
UPDATE "tAlarmInfo"
SET "EndTime" = GetDate()
WHERE "chnID" = @channelID AND "Appid" = @appID;

#结束人员计数的报警，通过频率ID和应用ID，结束时间等于2000-01-01 00:00:00的报警
UPDATE "tAlarmInfo"
SET "EndTime" = GetDate()
WHERE "chnID" = :channelID AND "Appid" = :appID AND "EndTime" = '2000-01-01 00:00:00';


#根据开始时间删除一年前的信息
DELETE FROM "tAlarmInfo"
WHERE "StartTime" < DATEADD(YEAR, -1, GETDATE());

-- SQLBook: Code
#报警规则表
SELECT *
FROM "tRuleInfo";

#通过tPeriod表可以找到报警规则的通道号
SELECT *
FROM "tRuleInfo"
LEFT JOIN "tPeriod" ON "tRuleInfo"."PKID" = "tPeriod"."RuleID";

-- SQLBook: Code
#时段详细信息表，从这里获取到开启了哪些检测功能
SELECT *
FROM "tPeriod";

#获取功能信息，同时包含所在的频率信息，和应用信息
SELECT "tPeriod"."ChnId", "tPeriod"."AppType", "tChannel"."ChnName", "tAppInfo"."AppName"
FROM "tPeriod"
LEFT JOIN "tChannel"
ON "tPeriod"."ChnId" = "tChannel"."ChnId"
LEFT JOIN "tAppInfo"
ON "tPeriod"."AppType" = "tAppInfo"."AppType"
GROUP BY "tPeriod"."ChnId", "tPeriod"."AppType", "tChannel"."ChnName", "tAppInfo"."AppName";


SELECT *
FROM "tChannel";


#通过频率和应用ID获取检测时间段
SELECT *
FROM "tPeriod"
WHERE "ChnId" = @channelID AND "AppType" = @appType;
-- SQLBook: Code
#人员在岗信息表
SELECT *
FROM "tWorkOnInfo";

#插入信息
INSERT INTO "tWorkOnInfo" ("nChID", "FaceIdList", "FaceNameList", "StartTime", "EndTime", "MaxNumber", "MinNumber", "CamerId")
VALUES (:nChID, :FaceIdList, :FaceNameList, :StartTime, :EndTime, :MaxNumber, :MinNumber, :CamerId);

INSERT INTO "tWorkOnInfo" ("nChID", "FaceIdList", "FaceNameList", "StartTime", "EndTime", "MaxNumber", "MinNumber", "CamerId")
VALUES (1, '-1;-1', '未知;未知', '2024-10-21 18:03:00', '2024-10-21 18:13:00', 5, 1, 117);
-- SQLBook: Code
#查看通道信息
SELECT *
FROM "tChannel";
-- SQLBook: Code
SELECT *
FROM "tAppInfo";