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

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

#插入信息
INSERT INTO "tAction" ("ActionId","ActionTaskid","ActionName")
VALUES ('1',1,'test');

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

#删除信息
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
#摄像头信息表
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;
-- 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
#报警信息表
SELECT *
FROM "tAlarmInfo";

#插入报警信息
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")
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")
VALUES('0', '2024-10-08 14:00:00', '2024-10-08 14:00:00','','112,112,112,112','PicURL', '1', '2', '报警测试', '3', '4', '5', '6', 'FaceIDList','FaceNameList', '1');

#删除一行
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);
-- SQLBook: Code
#报警规则表
SELECT *
FROM "tRuleInfo";

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