ts/SwitchOnOff.sql

-- Active: 1730791096610@@192.1.2.61@5236@EQM_CESHI

#查询发射机计划
SELECT *
FROM tExecPlan;


#删除发射机计划
DELETE 
FROM tExecPlan
WHERE PTTypeCode = 955;

DELETE 
FROM tExecPlan
WHERE PTTypeCode = :PTTypeCode;


#===============================================
# 发射机计划模版表格-tExecPlanTemplate
#===============================================


#删除表格
DROP TABLE IF EXISTS "EQM_CESHI"."tExecPlanTemplate";

#创建模版表格
CREATE TABLE IF NOT EXISTS "EQM_CESHI"."tExecPlanTemplate"
(
    "TemplateName" VARCHAR(255),
    "TemplateType" INT,
    "ExecDate" VARCHAR(255),
    "ExecTime" VARCHAR(255),
    "DeviceName" VARCHAR(255),
    "ActionName" VARCHAR(255),
    "ActionID" INT
)


#查询模版
SELECT *
FROM tExecPlanTemplate;

#获取模板名称和模版类型，去掉重复的
SELECT EP."TemplateName", EP."TemplateType"
FROM tExecPlanTemplate AS EP
GROUP BY EP."TemplateName", EP."TemplateType";

#通过模板名称获取模板数据
SELECT *
FROM tExecPlanTemplate
WHERE "TemplateName" = :templateName;

#删除表格中的所有数据
DELETE
FROM tExecPlanTemplate;

SELECT *
FROM TMONITORPIN;


#插入数据
INSERT INTO tExecPlanTemplate (TemplateName, TemplateType, ExecDate, ExecTime, DeviceName, ActionName, ActionID)
VALUES ('模版1', '0', '2025-02-09', '09:00:00', '发射机1', '开机', 1);
#插入数据，使用参数
INSERT INTO tExecPlanTemplate ("TemplateName", "TemplateType", "ExecDate", "ExecTime", "DeviceName", "ActionName", "ActionID")
VALUES (:templateName, :templateType, :execDate, :execTime, :deviceName, :actionName, :actionID);


#删除模版
DELETE 
FROM tExecPlanTemplate
WHERE TemplateName = '模版3';

DELETE
FROM tExecPlanTemplate
WHERE TemplateName = :templateName;


#===============================================
# 发射机计划表格-tExecPlan
#===============================================


SELECT *
FROM tExecPlan;

#插入一行到发射机计划表格
INSERT INTO "tExecPlan" ("ExecType", "WeekDay", "Date", "ExecTime", "DID", "MPID", "DeviceName", "PTTypeCode", "ActionID", "ActionName", "ChannelID")
VALUES (:ExecType, :WeekDay, :Date, :ExecTime, :DID, :MPID, :DeviceName, :PTTypeCode, :ActionID, :ActionName, :ChannelID);


#获取设备信息
SELECT Dev.CHNID, Dev.DID, Dev.DTID, Dev.DNAME, Dev.DClass, Chn.CHNNAME, MP.MADDRESS, MP.MNAME, MP.MType, PT.PTTYPECODE, MP.MPID
FROM "tDevice" AS Dev
LEFT JOIN "tDeviceType" AS DT ON "Dev"."DTID" = "DT"."DTID"
LEFT JOIN "tChannel" AS Chn ON "Dev"."CHNID" = "Chn"."CHNID"
LEFT JOIN "tMonitorPin" AS MP ON "MP"."MPID" IN (
    SELECT "MPID"
    FROM "tDevicePin" AS DevPin
    WHERE "DevPin"."DID" = "Dev"."DID"
)
LEFT JOIN "tPinType" AS PT ON "MP"."MType" = "PT"."PTID";


#创建表格
CREATE TABLE IF NOT EXISTS "EQM_CESHI"."tExecPlan" (
    "IncKey" INT PRIMARY KEY AUTO_INCREMENT,
    "ExecType" INT NOT NULL,
    "WeekDay" INT,
    "Date" VARCHAR(64),
    "ExecTime" VARCHAR(64) NOT NULL,
    "DID" INT,
    "MPID" INT,
    "DeviceName" VARCHAR(64),
    "PTTypeCode" INT,
    "ActionID" INT,
    "ActionName" VARCHAR(64),
    "ChannelID" INT
)



#根据PTTypeCode删除发射机计划
DELETE
FROM tExecPlan
WHERE PTTypeCode = :PTTypeCode;


#删除表格
DROP TABLE IF EXISTS "EQM_CESHI"."tExecPlan";


#===============================================
# 执行计划页面全局配置表格-tExecPlanConfig
#===============================================

#查询表格
SELECT *
FROM tExecPlanConfig;

#清空表格
DELETE
FROM tExecPlanConfig;

#创建表格
CREATE TABLE IF NOT EXISTS "EQM_CESHI"."tExecPlanConfig" (
    "KeyWord" INT PRIMARY KEY,
    "Value1" INT,
    "UpdateTime" VARCHAR(64),
    "Notes" VARCHAR(255)
)

#删除表格
DROP TABLE IF EXISTS "EQM_CESHI"."tExecPlanConfig";

#插入数据
INSERT INTO "tExecPlanConfig" ("KeyWord", "Value1", "UpdateTime", "Notes")
VALUES (:keyWord, :value1, :updateTime, :notes);

#更新数据
UPDATE "tExecPlanConfig"
SET "Value1" = :value1, "UpdateTime" = :updateTime
WHERE "KeyWord" = :keyWord;


#根据关键字删除数据
DELETE
FROM tExecPlanConfig
WHERE KeyWord = :keyWord;


#================================================================
# 执行计划页面全局配置表格-tExecPlanConfig，单独获取某个关键字的数据
#================================================================

SELECT *
FROM tExecPlanConfig
WHERE KeyWord = :keyWord;


#================================================================
# 获取设备信息
#================================================================

SELECT Dev.CHNID, Dev.DID, Dev.DTID, Dev.DNAME, Dev.DClass, Chn.CHNNAME, MP.MADDRESS, MP.MNAME, MP.MType, PT.PTTYPECODE, MP.MPID
FROM "tDevice" AS Dev
LEFT JOIN "tDeviceType" AS DT ON "Dev"."DTID" = "DT"."DTID"
LEFT JOIN "tChannel" AS Chn ON "Dev"."CHNID" = "Chn"."CHNID"
LEFT JOIN "tMonitorPin" AS MP ON "MP"."MPID" IN (
    SELECT "MPID"
    FROM "tDevicePin" AS DevPin
    WHERE "DevPin"."DID" = "Dev"."DID"
)
LEFT JOIN "tPinType" AS PT ON "MP"."MType" = "PT"."PTID";



#================================================================
# 获取频率信息
#================================================================

SELECT chn.CHNID, chn.CHNNAME
FROM tChannel AS chn;