ts/TransmitterSwitch/FromSQL/SqlQuery.h

#ifndef SQLQUERY_H
#define SQLQUERY_H

#include <QString>

/* ====================================================================
 * SQLite查询语句
 * ==================================================================== */

 /* 检查表格是否存在 */
 const QString cmd_CheckTable = R"(
    SELECT name 
    FROM sqlite_master 
    WHERE type='table' AND name='%1';
 )";


/**
 * @brief 创建模版表格
 * 
 */
const QString cmd_CreateExecPlanTamplate = R"(
CREATE TABLE IF NOT EXISTS "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
)
)";

/* 获取模版列表 */
const QString cmd_GetTemplateList = R"(
    SELECT EPT.TemplateName, EPT.ChannelID 
    FROM tExecPlanTemplate AS EPT
    GROUP BY EPT.TemplateName;
)";


/* 插入一条模板数据 */
const QString cmd_InsertTemplate = R"(
    INSERT INTO tExecPlanTemplate (TemplateName, ChannelID, ChannelName, OnWeekDay, OnDate, OnTime, OffWeekDay, OffDate, OffTime)
    VALUES ('%1', %2, '%3', %4, '%5', '%6', %7, '%8', '%9');
)";

/* 删除模版，单机版目前不考虑频率ID */
const QString cmd_DeleteTemplate = R"(
    DELETE FROM tExecPlanTemplate 
    WHERE TemplateName = '%1';
)";


/* 获取一个模版数据 */
const QString cmd_GetTemplateData = R"(
    SELECT * 
    FROM tExecPlanTemplate 
    WHERE TemplateName = '%1';
)";

/* 更新模板名称 */
const QString cmd_UpdateTemplateName = R"(
    UPDATE tExecPlanTemplate 
    SET TemplateName = '%1' 
    WHERE TemplateName = '%2';
)";


#endif // SQLQUERY_H