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
#===============================================


#查询模版
SELECT *
FROM tExecPlanTemplate;


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

#获取某个频率的模版名称
SELECT EP."TemplateName", EP.ChannelID
FROM tExecPlanTemplate AS EP
WHERE EP.ChannelID = :channelID
GROUP BY EP."TemplateName", EP."ChannelID";


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

#插入数据
INSERT INTO "tExecPlanTemplate" ("TemplateName", "ChannelID", "OnWeekDay", "OnDate", "OnTime", "OffWeekDay", "OffDate", "OffTime")
VALUES ('模版1', '2', '1', '2025-02-09', '09:00:00', '1', '2025-02-09', '09:00:01');

#插入数据，使用参数
INSERT INTO "tExecPlanTemplate" ("TemplateName", "ChannelID", "ChannelName", "OnWeekDay", "OnDate", "OnTime", "OffWeekDay", "OffDate", "OffTime")
VALUES (:templateName, :channelID, :channelName, :onWeekDay, :onDate, :onTime, :offWeekDay, :offDate, :offTime);

#更新模板名称
UPDATE "tExecPlanTemplate"
SET "TemplateName" = :newTemplateName
WHERE "TemplateName" = :oldTemplateName AND ChannelID = :channelID;






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

SELECT *
FROM TMONITORPIN;

#删除模版
DELETE 
FROM tExecPlanTemplate
WHERE ChannelID = '2' AND TemplateName = '模版1';

#删除一个频率的一个模版
DELETE
FROM tExecPlanTemplate
WHERE ChannelID = :channelID AND TemplateName = :templateName;


#创建模版表格
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
)

#创建执行计划模版，新的UI
CREATE TABLE IF NOT EXISTS "EQM_CESHI"."tExecPlanTemplate"
(
    "TemplateName" VARCHAR(255) NOT NULL,
    "ChannelID" INT NOT NULL,
    "ChannelName" VARCHAR(255),
    "OnWeekDay" INT NOT NULL,
    "OnDate" VARCHAR(255) NOT NULL,
    "OnTime" VARCHAR(255) NOT NULL,
    "OffWeekDay" INT NOT NULL,
    "OffDate" VARCHAR(255) NOT NULL,
    "OffTime" VARCHAR(255) NOT NULL,

    "DeviceName" VARCHAR(255),
    "ActionName" VARCHAR(255),
    "ActionID" INT
)


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


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

#获取所有计划
SELECT *
FROM tExecPlan;

#获取指定频率的计划
SELECT *
FROM tExecPlan
WHERE ChannelID = :ChannelID;

#插入一行到发射机计划表格
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);


#插入一行到发射机计划表格，使用参数，新版本
INSERT INTO "tExecPlan" ("ChannelID", "ChannelName", "OnWeekDay", "OnDate", "OnTime", "OffWeekDay", "OffDate", "OffTime")
VALUES (:channelID, :channelName, :onWeekDay, :onDate, :onTime, :offWeekDay, :offDate, :offTime);


#删除一个频率的计划
DELETE
FROM "tExecPlan"
WHERE ChannelID = :channelID;

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

#创建表格，全新UI
CREATE TABLE IF NOT EXISTS "EQM_CESHI"."tExecPlan" (
    "IncKey" INT PRIMARY KEY AUTO_INCREMENT,
    "ChannelID" INT NOT NULL,
    "ChannelName" VARCHAR(64),
    "OnWeekDay" INT NOT NULL,
    "OnDate" VARCHAR(64) NOT NULL,
    "OnTime" VARCHAR(64) NOT NULL,
    "OffWeekDay" INT NOT NULL,
    "OffDate" VARCHAR(64) NOT NULL,
    "OffTime" VARCHAR(64) NOT NULL,
    "DID" INT,
    "MPID" INT,
    "DeviceName" VARCHAR(64),
    "PTTypeCode" INT,
    "ActionID" INT,
    "ActionName" VARCHAR(64)
);



#根据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;