ts/SwitchOnOff.sql

#查询发射机计划
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),
    "ExecDate" VARCHAR(255),
    "ExecTime" VARCHAR(255),
    "DeviceName" VARCHAR(255),
    "ActionName" VARCHAR(255),
    "ActionID" INT
)


#查询模版
SELECT *
FROM tExecPlanTemplate;

#获取模板名称，去掉重复的
SELECT "TemplateName"
FROM tExecPlanTemplate
GROUP BY "TemplateName";

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

SELECT *
FROM TMONITORPIN;


#插入数据
INSERT INTO tExecPlanTemplate (TemplateName, ExecDate, ExecTime, DeviceName, ActionName, ActionID)
VALUES ('模版1', '2025-02-09', '09:00:00', '发射机1', '开机', 1);
#插入数据，使用参数
INSERT INTO tExecPlanTemplate ("TemplateName", "ExecDate", "ExecTime", "DeviceName", "ActionName", "ActionID")
VALUES (:templateName, :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";