123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236 |
- -- 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";
|