broker.mqttgo.io / WOKWI MQTT LED Control / Node-Red SQLITE
const char *mqtt_broker = "broker.mqttgo.io";
const char *topic1 = "alex9ufo/esp32/led";
const char *topic = "alex9ufo/esp32/Starting";
const char *topic3 = "alex9ufo/esp32/led_status";
WOKWI ESP32 LED MQTT SQLITE (broker.mqttgo.io/)
#include <WiFi.h>
#include <PubSubClient.h>
//GPIO 13 D1 Build in LED
#define LED 13
///////please enter your sensitive data in the Secret tab/arduino_secrets.h
const char *ssid = "Wokwi-GUEST"; // your network SSID (name)
const char *password = "" ; // your network password (use for WPA, or use as key for WEP)
// WiFi
//const char *ssid = "alex9ufo"; // Enter your Wi-Fi name
//const char *password = "alex9981"; // Enter Wi-Fi password
// MQTT Broker
//const char *mqtt_broker = "broker.mqtt-dashboard.com";
//const char *mqtt_broker = "test.mosquitto.org";
const char *mqtt_broker = "broker.mqttgo.io";
const char *topic1 = "alex9ufo/esp32/led";
const char *topic = "alex9ufo/esp32/Starting";
const char *topic3 = "alex9ufo/esp32/led_status";
const char *mqtt_username = "alex9ufo";
const char *mqtt_password = "public";
const int mqtt_port = 1883;
bool ledState = false;
bool atwork = false;
WiFiClient espClient;
PubSubClient client(espClient);
long lastMsg = 0;
long lastMsg1= 0;
char msg[50];
String json = "";
bool Flash = false; //true
bool Timer = false; //true
bool Send = false; //true
int Count= 0;
char jsonChar1[50];
TaskHandle_t Task1;
// Wifi reconnect
unsigned long previousMillis = 0;
unsigned long interval = 30000;
//===========================================================
//任務1副程式Task1_senddata
void Task1_senddata(void * pvParameters ) {
//無窮迴圈
for (;;) {
//偵測上傳旗標是否為true
// Process LED message
LED_Message();
//Task1休息，delay(X)不可省略
delay(1000);
}
}
//===========================================================
void setup_wifi() {
delay(10);
// We start by connecting to a WiFi network
Serial.println();
Serial.print("Connecting to ");
Serial.println(ssid);
WiFi.begin(ssid, password);
while (WiFi.status() != WL_CONNECTED) {
delay(500);
Serial.print(".");
}
Serial.println("");
Serial.println("WiFi connected");
Serial.println("IP address: ");
Serial.println(WiFi.localIP());
}
//===========================================================
void callback(char *topic, byte *payload, unsigned int length) {
Serial.print("Message arrived in topic: ");
Serial.println(topic);
Serial.print("Message: ");
String message;
for (int i = 0; i < length; i++) {
message += (char) payload[i]; // Convert *byte to string
}
Serial.print(message);
if (String(topic)=="alex9ufo/esp32/led") {
if (message == "on" ) {
digitalWrite(LED, LOW); // Turn on the LED
ledState = true; //ledState = ture HIGH
Flash = false;
Timer = false;
json ="ON";
Send = true ;
}
if (message == "off") {
digitalWrite(LED, HIGH); // Turn off the LED
ledState = false; //ledState = false LOW
Flash = false;
Timer = false;
json ="OFF";
Send = true ;
}
if (message == "flash" ) {
digitalWrite(LED, LOW); // Turn off the LED
Flash = true;
Timer = false;
json ="FLASH";
Send = true ;
}
if (message == "timer" ) {
digitalWrite(LED, LOW); // Turn off the LED
Flash = false;
Timer = true;
json ="TIMER";
Send = true ;
Count= 11;
}
if (message == "toggle" ) {
digitalWrite(LED, !digitalRead(LED)); // Turn the LED toggle
if (digitalRead(LED))
ledState = true;
else
ledState = false;
Flash = false;
Timer = false;
json ="TOGGLE";
Send = true ;
}
}
Serial.println();
Serial.println("-----------------------");
}
//===========================================================
void reconnect() {
// Loop until we're reconnected
while (!client.connected()) {
Serial.print("Attempting MQTT connection...");
// Attempt to connect
if (client.connect("esp32-client-")) {
Serial.println("connected");
// Subscribe
client.subscribe("alex9ufo/esp32/led");
} else {
Serial.print("failed, rc=");
Serial.print(client.state());
Serial.println(" try again in 5 seconds");
// Wait 5 seconds before retrying
delay(5000);
if (WiFi.status() != WL_CONNECTED) {
Serial.println("Reconnecting to WiFi...");
WiFi.disconnect();
WiFi.reconnect();
}
}
}
}
//===========================================================
void LED_Message() {
if (Flash){
digitalWrite(LED, !digitalRead(LED));
delay(500);
if (digitalRead(LED))
ledState = true;
else
ledState = false;
} //(Flash)
if (Timer) {
digitalWrite(LED, HIGH);
delay(500);
if (digitalRead(LED))
ledState = true;
else
ledState = false;
Count=Count-1;
if (Count == 0 ){
Timer=false;
digitalWrite(LED, LOW);
ledState = false;
}
} //(Timer)
if (client.connected()) {
if (Send) {
// Convert JSON string to character array
json.toCharArray(jsonChar1, json.length()+1);
Serial.print("Publish message: ");
Serial.println(json);
// Publish JSON character array to MQTT topic
client.publish(topic3,jsonChar1);
}
Send = false;
}
else
{
if (WiFi.status() != WL_CONNECTED) {
Serial.println("Reconnecting to WiFi...");
WiFi.disconnect();
WiFi.reconnect();
}
}
}
//===========================================================
void setup() {
// Set software serial baud to 115200;
Serial.begin(115200);
delay(1000); // Delay for stability
//======================================================
// Connecting to a WiFi network
setup_wifi();
// Setting LED pin as output
pinMode(LED, OUTPUT);
digitalWrite(LED, LOW); // Turn off the LED initially
//======================================================
// Connecting to an MQTT broker
client.setServer(mqtt_broker, mqtt_port);
client.setCallback(callback);
while (!client.connected()) {
String client_id = "esp32-client-";
client_id += String(WiFi.macAddress());
Serial.printf("The client %s connects to the public MQTT broker\n", client_id.c_str());
if (client.connect(client_id.c_str(), mqtt_username, mqtt_password)) {
Serial.println("Public HiveMQ MQTT broker (broker.mqtt-dashboard.com) connected");
} else {
Serial.print("Failed with state ");
Serial.print(client.state());
delay(2000);
}
}
// Publish and subscribe
client.subscribe(topic1);
client.publish(topic,"ESP32 at work");
//在核心0啟動任務1
xTaskCreatePinnedToCore(
Task1_senddata, /*任務實際對應的Function*/
"Task1", /*任務名稱*/
10000, /*堆疊空間*/
NULL, /*無輸入值*/
0, /*優先序0*/
&Task1, /*對應的任務變數位址*/
0); /*指定在核心0執行 */
}
//===========================================================
void loop()
{
if (!client.connected()) {
reconnect();
Serial.print(" client not connected reconnect ");
delay(200);
}
client.loop();
unsigned long currentMillis = millis();
// if WiFi is down, try reconnecting
if ((WiFi.status() != WL_CONNECTED) && (currentMillis - previousMillis >=interval)) {
Serial.print(millis());
Serial.println("Reconnecting to WiFi...");
WiFi.disconnect();
WiFi.reconnect();
previousMillis = currentMillis;
client.setCallback(callback);
}
}
//===========================================================
Node-Red作業2-1
[
{
"id": "e6c6a2f4e3f2c78e",
"type": "inject",
"z": "0fce84474a62af64",
"name": "CREATE",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "CREATE TABLE LEDSTATUS (id INTEGER,STATUS TEXT,Date DATE,Time TIME,PRIMARY KEY (id));",
"payload": "",
"payloadType": "date",
"x": 160,
"y": 60,
"wires": [
[
"104fc7ceb848b11c"
]
]
},
{
"id": "b1d786746d0fd4bc",
"type": "inject",
"z": "0fce84474a62af64",
"name": "SELECT",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "SELECT * FROM LEDSTATUS",
"payload": "",
"payloadType": "date",
"x": 160,
"y": 140,
"wires": [
[
"104fc7ceb848b11c"
]
]
},
{
"id": "8f19843b9bb86b41",
"type": "inject",
"z": "0fce84474a62af64",
"name": "INSERT",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "INSERT INTO LEDSTATUS (STATUS , Date , Time )values(\"on\", \"11/01\" , \"21:05\") ",
"payload": "",
"payloadType": "date",
"x": 160,
"y": 100,
"wires": [
[
"104fc7ceb848b11c"
]
]
},
{
"id": "99e44b398b809ed9",
"type": "inject",
"z": "0fce84474a62af64",
"name": "DELETE",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "DELETE from LEDSTATUS",
"payload": "",
"payloadType": "date",
"x": 160,
"y": 180,
"wires": [
[
"104fc7ceb848b11c"
]
]
},
{
"id": "6eac734d14c112ba",
"type": "inject",
"z": "0fce84474a62af64",
"name": "DROP TABLE",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "DROP TABLE LEDSTATUS",
"payload": "",
"payloadType": "date",
"x": 170,
"y": 220,
"wires": [
[
"104fc7ceb848b11c"
]
]
},
{
"id": "104fc7ceb848b11c",
"type": "sqlite",
"z": "0fce84474a62af64",
"mydb": "19f59ce9.3edc23",
"sqlquery": "msg.topic",
"sql": "",
"name": "LED_STATUS",
"x": 440,
"y": 140,
"wires": [
[
"0979147ed72dcfb0"
]
]
},
{
"id": "0979147ed72dcfb0",
"type": "debug",
"z": "0fce84474a62af64",
"name": "",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "false",
"x": 670,
"y": 140,
"wires": []
},
{
"id": "8985248ab988bbfb",
"type": "comment",
"z": "0fce84474a62af64",
"name": "TABLE LEDSTATUS",
"info": "CREATE TABLE LEDSTATUS (\nid INTEGER,\nSTATUS TEXT,\nDate DATE,\nTime TIME,\nPRIMARY KEY (id)\n);",
"x": 170,
"y": 20,
"wires": []
},
{
"id": "ae5ad0762eed0dcf",
"type": "sqlite",
"z": "0fce84474a62af64",
"mydb": "19f59ce9.3edc23",
"sqlquery": "msg.topic",
"sql": "",
"name": "LED_STATUS",
"x": 560,
"y": 300,
"wires": [
[
"148162c6e62150da"
]
]
},
{
"id": "6ca5a584ad49d853",
"type": "function",
"z": "0fce84474a62af64",
"name": "CREATE DATABASE",
"func": "//CREATE TABLE LEDSTATUS (\n//id INTEGER,\n//STATUS TEXT,\n//Date DATE,\n//Time TIME,\n//PRIMARY KEY (id)\n//);\n//CREATE TABLE LEDSTATUS (id INTEGER,STATUS TEXT,Date DATE,Time TIME,PRIMARY KEY (id));\nmsg.topic = \"CREATE TABLE LEDSTATUS (id INTEGER,STATUS TEXT,Date DATE,Time TIME,PRIMARY KEY (id))\";\nreturn msg;\n",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 360,
"y": 300,
"wires": [
[
"ae5ad0762eed0dcf"
]
]
},
{
"id": "3a1e3d3e5ebd0fe3",
"type": "ui_button",
"z": "0fce84474a62af64",
"name": "",
"group": "318666b083f99832",
"order": 7,
"width": 0,
"height": 0,
"passthru": false,
"label": "建立資料庫",
"tooltip": "",
"color": "",
"bgcolor": "",
"className": "",
"icon": "",
"payload": "建立資料庫",
"payloadType": "str",
"topic": "topic",
"topicType": "msg",
"x": 150,
"y": 300,
"wires": [
[
"6ca5a584ad49d853",
"177e37f4ae55b8de"
]
]
},
{
"id": "177e37f4ae55b8de",
"type": "ui_audio",
"z": "0fce84474a62af64",
"name": "",
"group": "11d8514.a44dcaf",
"voice": "Microsoft Hanhan - Chinese (Traditional, Taiwan)",
"always": true,
"x": 275,
"y": 260,
"wires": [],
"l": false
},
{
"id": "45079316e96773de",
"type": "ui_button",
"z": "0fce84474a62af64",
"name": "",
"group": "11d8514.a44dcaf",
"order": 1,
"width": 0,
"height": 0,
"passthru": false,
"label": "ON",
"tooltip": "",
"color": "",
"bgcolor": "",
"className": "",
"icon": "",
"payload": "on",
"payloadType": "str",
"topic": "topic",
"topicType": "msg",
"x": 130,
"y": 340,
"wires": [
[
"ff35d3507187b9a0",
"67dd87c783cb15e4",
"2134c09f9662eb47",
"a78819d1ad7987af"
]
]
},
{
"id": "1c618784ade07262",
"type": "ui_button",
"z": "0fce84474a62af64",
"name": "",
"group": "11d8514.a44dcaf",
"order": 2,
"width": 0,
"height": 0,
"passthru": false,
"label": "OFF",
"tooltip": "",
"color": "",
"bgcolor": "",
"className": "",
"icon": "",
"payload": "off",
"payloadType": "str",
"topic": "topic",
"topicType": "msg",
"x": 130,
"y": 380,
"wires": [
[
"ff35d3507187b9a0",
"67dd87c783cb15e4",
"2134c09f9662eb47",
"a78819d1ad7987af"
]
]
},
{
"id": "3a923bb8b3117c49",
"type": "ui_button",
"z": "0fce84474a62af64",
"name": "",
"group": "11d8514.a44dcaf",
"order": 3,
"width": 0,
"height": 0,
"passthru": false,
"label": "TOGGLE",
"tooltip": "",
"color": "",
"bgcolor": "",
"className": "",
"icon": "",
"payload": "toggle",
"payloadType": "str",
"topic": "topic",
"topicType": "msg",
"x": 140,
"y": 420,
"wires": [
[
"ff35d3507187b9a0",
"67dd87c783cb15e4",
"2134c09f9662eb47",
"a78819d1ad7987af"
]
]
},
{
"id": "655263fb113efa20",
"type": "ui_button",
"z": "0fce84474a62af64",
"name": "",
"group": "11d8514.a44dcaf",
"order": 4,
"width": 0,
"height": 0,
"passthru": false,
"label": "TIMER",
"tooltip": "",
"color": "",
"bgcolor": "",
"className": "",
"icon": "",
"payload": "timer",
"payloadType": "str",
"topic": "topic",
"topicType": "msg",
"x": 140,
"y": 460,
"wires": [
[
"ff35d3507187b9a0",
"67dd87c783cb15e4",
"2134c09f9662eb47",
"a78819d1ad7987af"
]
]
},
{
"id": "8d89d51a29ef010f",
"type": "ui_button",
"z": "0fce84474a62af64",
"name": "",
"group": "11d8514.a44dcaf",
"order": 5,
"width": 0,
"height": 0,
"passthru": false,
"label": "FLASH",
"tooltip": "",
"color": "",
"bgcolor": "",
"className": "",
"icon": "",
"payload": "flash",
"payloadType": "str",
"topic": "topic",
"topicType": "msg",
"x": 140,
"y": 500,
"wires": [
[
"67dd87c783cb15e4",
"ff35d3507187b9a0",
"2134c09f9662eb47",
"a78819d1ad7987af"
]
]
},
{
"id": "67dd87c783cb15e4",
"type": "function",
"z": "0fce84474a62af64",
"name": "INSERT",
"func": "var Today = new Date();\nvar yyyy = Today.getFullYear(); //年\nvar MM = Today.getMonth()+1; //月\nvar dd = Today.getDate(); //日\nvar h = Today.getHours(); //時\nvar m = Today.getMinutes(); //分\nvar s = Today.getSeconds(); //秒\nif(MM<10)\n{\n MM = '0'+MM;\n}\n\nif(dd<10)\n{\n dd = '0'+dd;\n}\n\nif(h<10)\n{\n h = '0'+h;\n}\n\nif(m<10)\n{\n m = '0' + m;\n}\n\nif(s<10)\n{\n s = '0' + s;\n}\nvar var_date = yyyy+'/'+MM+'/'+dd;\nvar var_time = h+':'+m+':'+s;\n\nvar myLED = msg.payload;\n\n\nmsg.topic = \"INSERT INTO LEDSTATUS ( STATUS , Date , Time ) VALUES ($myLED, $var_date , $var_time ) \" ;\nmsg.payload = [myLED, var_date , var_time ]\nreturn msg;\n\n\n//INSERT INTO LEDSTATUS (\n//id INTEGER,\n//STATUS TEXT,\n//Date DATE,\n//Time TIME,\n//PRIMARY KEY (id)\n//);\n",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 340,
"y": 440,
"wires": [
[
"085d26aa3b11ae94"
]
]
},
{
"id": "ff35d3507187b9a0",
"type": "ui_audio",
"z": "0fce84474a62af64",
"name": "",
"group": "11d8514.a44dcaf",
"voice": "Microsoft Hanhan - Chinese (Traditional, Taiwan)",
"always": true,
"x": 295,
"y": 400,
"wires": [],
"l": false
},
{
"id": "085d26aa3b11ae94",
"type": "sqlite",
"z": "0fce84474a62af64",
"mydb": "19f59ce9.3edc23",
"sqlquery": "msg.topic",
"sql": "",
"name": "LED_STATUS",
"x": 520,
"y": 440,
"wires": [
[
"382e14a40f6c3a9a",
"7a7aff9e3b3ee627"
]
]
},
{
"id": "148162c6e62150da",
"type": "debug",
"z": "0fce84474a62af64",
"name": "debug ",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "payload",
"targetType": "msg",
"statusVal": "",
"statusType": "auto",
"x": 730,
"y": 300,
"wires": []
},
{
"id": "382e14a40f6c3a9a",
"type": "debug",
"z": "0fce84474a62af64",
"name": "debug ",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "payload",
"targetType": "msg",
"statusVal": "",
"statusType": "auto",
"x": 730,
"y": 440,
"wires": []
},
{
"id": "9a2169414091d66e",
"type": "ui_button",
"z": "0fce84474a62af64",
"name": "",
"group": "318666b083f99832",
"order": 8,
"width": 0,
"height": 0,
"passthru": false,
"label": "檢視資料庫資料",
"tooltip": "",
"color": "",
"bgcolor": "",
"className": "",
"icon": "",
"payload": "檢視資料",
"payloadType": "str",
"topic": "topic",
"topicType": "msg",
"x": 160,
"y": 600,
"wires": [
[
"7a7aff9e3b3ee627",
"b0ddfb45316192c9"
]
]
},
{
"id": "7a7aff9e3b3ee627",
"type": "function",
"z": "0fce84474a62af64",
"name": "檢視資料",
"func": "//INSERT INTO LEDSTATUS (\n//id INTEGER,\n//STATUS TEXT,\n//Date DATE,\n//Time TIME,\n//PRIMARY KEY (id)\n//);\n\n//SELECT * FROM LEDSTATUS ORDER BY id DESC LIMIT 50;\n\nmsg.topic = \"SELECT * FROM LEDSTATUS ORDER BY id DESC LIMIT 50\";\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 380,
"y": 600,
"wires": [
[
"b5d9bb30e6705218"
]
]
},
{
"id": "1973fd43faf98656",
"type": "ui_table",
"z": "0fce84474a62af64",
"group": "821196f103500796",
"name": "",
"order": 1,
"width": 10,
"height": 10,
"columns": [],
"outputs": 0,
"cts": false,
"x": 830,
"y": 600,
"wires": []
},
{
"id": "b5d9bb30e6705218",
"type": "sqlite",
"z": "0fce84474a62af64",
"mydb": "19f59ce9.3edc23",
"sqlquery": "msg.topic",
"sql": "",
"name": "LED_STATUS",
"x": 560,
"y": 600,
"wires": [
[
"1973fd43faf98656"
]
]
},
{
"id": "9928e4681f99c36b",
"type": "ui_button",
"z": "0fce84474a62af64",
"name": "",
"group": "318666b083f99832",
"order": 1,
"width": 2,
"height": 1,
"passthru": false,
"label": "刪除資料庫 ",
"tooltip": "",
"color": "",
"bgcolor": "",
"className": "",
"icon": "",
"payload": "刪除資料庫 ",
"payloadType": "str",
"topic": "topic",
"topicType": "msg",
"x": 150,
"y": 840,
"wires": [
[
"f937c5aaf577e12f",
"1c267151791ddc2e"
]
]
},
{
"id": "6a515f54dcbe6ce7",
"type": "function",
"z": "0fce84474a62af64",
"name": "DROP DATABASE",
"func": "//CREATE TABLE LEDSTATUS (\n//id INTEGER,\n//STATUS TEXT,\n//Date DATE,\n//Time TIME,\n//PRIMARY KEY (id)\n//);\n//CREATE TABLE LEDSTATUS (id INTEGER,STATUS TEXT,Date DATE,Time TIME,PRIMARY KEY (id));\nmsg.topic = \"DROP TABLE LEDSTATUS\";\nreturn msg;\n",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 560,
"y": 780,
"wires": [
[
"47ea0bd07a43e409"
]
]
},
{
"id": "47ea0bd07a43e409",
"type": "sqlite",
"z": "0fce84474a62af64",
"mydb": "19f59ce9.3edc23",
"sqlquery": "msg.topic",
"sql": "",
"name": "LED_STATUS",
"x": 780,
"y": 700,
"wires": [
[
"9dd34e1574c2ee59"
]
]
},
{
"id": "3d3ec23384954b32",
"type": "ui_button",
"z": "0fce84474a62af64",
"name": "",
"group": "318666b083f99832",
"order": 2,
"width": 2,
"height": 1,
"passthru": false,
"label": "刪除所有資料 ",
"tooltip": "",
"color": "",
"bgcolor": "",
"className": "",
"icon": "",
"payload": "刪除所有資料 ",
"payloadType": "str",
"topic": "topic",
"topicType": "msg",
"x": 160,
"y": 740,
"wires": [
[
"1c267151791ddc2e",
"8eb17d9f877243f7"
]
]
},
{
"id": "ea4cb8d4c0b5a551",
"type": "function",
"z": "0fce84474a62af64",
"name": "DELETE ALL DATA",
"func": "//CREATE TABLE LEDSTATUS (\n//id INTEGER,\n//STATUS TEXT,\n//Date DATE,\n//Time TIME,\n//PRIMARY KEY (id)\n//);\n//CREATE TABLE LEDSTATUS (id INTEGER,STATUS TEXT,Date DATE,Time TIME,PRIMARY KEY (id));\nmsg.topic = \"DELETE from LEDSTATUS\";\nreturn msg;\n",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 430,
"y": 680,
"wires": [
[
"47ea0bd07a43e409"
]
]
},
{
"id": "f937c5aaf577e12f",
"type": "ui_toast",
"z": "0fce84474a62af64",
"position": "prompt",
"displayTime": "3",
"highlight": "",
"sendall": true,
"outputs": 1,
"ok": "OK",
"cancel": "Cancel",
"raw": true,
"className": "",
"topic": "",
"name": "",
"x": 350,
"y": 840,
"wires": [
[
"6a4ff7e2e7692440"
]
]
},
{
"id": "6a4ff7e2e7692440",
"type": "function",
"z": "0fce84474a62af64",
"name": "function 84",
"func": "var topic=msg.payload;\nif (topic==\"\"){\n return [msg,null];\n \n}\nif (topic==\"Cancel\"){\n return [null,msg];\n \n}\nreturn msg;",
"outputs": 2,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 530,
"y": 840,
"wires": [
[
"6a515f54dcbe6ce7"
],
[]
]
},
{
"id": "1c267151791ddc2e",
"type": "ui_audio",
"z": "0fce84474a62af64",
"name": "",
"group": "11d8514.a44dcaf",
"voice": "Microsoft Hanhan - Chinese (Traditional, Taiwan)",
"always": true,
"x": 245,
"y": 780,
"wires": [],
"l": false
},
{
"id": "8eb17d9f877243f7",
"type": "ui_toast",
"z": "0fce84474a62af64",
"position": "prompt",
"displayTime": "3",
"highlight": "",
"sendall": true,
"outputs": 1,
"ok": "OK",
"cancel": "Cancel",
"raw": true,
"className": "",
"topic": "",
"name": "",
"x": 370,
"y": 740,
"wires": [
[
"bf042bda47f3e196"
]
]
},
{
"id": "bf042bda47f3e196",
"type": "function",
"z": "0fce84474a62af64",
"name": "function 85",
"func": "var topic=msg.payload;\nif (topic==\"\"){\n return [msg,null];\n \n}\nif (topic==\"Cancel\"){\n return [null,msg];\n \n}\nreturn msg;",
"outputs": 2,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 530,
"y": 740,
"wires": [
[
"ea4cb8d4c0b5a551"
],
[]
]
},
{
"id": "b0ddfb45316192c9",
"type": "ui_audio",
"z": "0fce84474a62af64",
"name": "",
"group": "11d8514.a44dcaf",
"voice": "Microsoft Hanhan - Chinese (Traditional, Taiwan)",
"always": true,
"x": 235,
"y": 640,
"wires": [],
"l": false
},
{
"id": "f405d2b1e22aa179",
"type": "ui_button",
"z": "0fce84474a62af64",
"name": "",
"group": "318666b083f99832",
"order": 3,
"width": 2,
"height": 1,
"passthru": false,
"label": "查詢一筆資料",
"tooltip": "",
"color": "",
"bgcolor": "",
"className": "",
"icon": "",
"payload": "查詢一筆資料",
"payloadType": "str",
"topic": "topic",
"topicType": "msg",
"x": 160,
"y": 900,
"wires": [
[
"1c267151791ddc2e",
"4287c88984667b40"
]
]
},
{
"id": "5bfa64e0568540a9",
"type": "function",
"z": "0fce84474a62af64",
"name": "查詢一筆資料",
"func": "//\nvar id = msg.payload.id;\nvar s=global.get(\"SEL1\")\nmsg.topic=\"\";\nvar temp=\"\";\n\nif (s==1)\n{\n temp =\"SELECT * FROM LEDSTATUS\";\n temp=temp+\" WHERE id LIKE '\"+ id +\"'\";\n}\nmsg.topic=temp;\nglobal.set(\"SEL1\",0);\n\nreturn msg;\n\n//SELECT * FROM COMPANY WHERE AGE LIKE 'XXX%';\n//INSERT INTO LEDSTATUS (\n//id INTEGER,\n//STATUS TEXT,\n//Date DATE,\n//Time TIME,\n//PRIMARY KEY (id)\n//);\n\n//SELECT * FROM LEDSTATUS ORDER BY id DESC LIMIT 50;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 700,
"y": 900,
"wires": [
[
"7c3ae79a4f4cf1ef"
]
]
},
{
"id": "7c3ae79a4f4cf1ef",
"type": "sqlite",
"z": "0fce84474a62af64",
"mydb": "19f59ce9.3edc23",
"sqlquery": "msg.topic",
"sql": "",
"name": "LED_STATUS",
"x": 760,
"y": 820,
"wires": [
[
"1973fd43faf98656"
]
]
},
{
"id": "44be58433dea5fcc",
"type": "ui_button",
"z": "0fce84474a62af64",
"name": "",
"group": "318666b083f99832",
"order": 4,
"width": 2,
"height": 1,
"passthru": false,
"label": "刪除一筆資料",
"tooltip": "",
"color": "",
"bgcolor": "",
"className": "",
"icon": "",
"payload": "刪除一筆資料",
"payloadType": "str",
"topic": "topic",
"topicType": "msg",
"x": 160,
"y": 960,
"wires": [
[
"fb6ee439cad42ac3",
"75de5ed9cfb84118"
]
]
},
{
"id": "fb6ee439cad42ac3",
"type": "ui_audio",
"z": "0fce84474a62af64",
"name": "",
"group": "11d8514.a44dcaf",
"voice": "Microsoft Hanhan - Chinese (Traditional, Taiwan)",
"always": true,
"x": 295,
"y": 1000,
"wires": [],
"l": false
},
{
"id": "fca01d6c2e71adc8",
"type": "ui_form",
"z": "0fce84474a62af64",
"name": "",
"label": "輸入id",
"group": "e48ffa90611225eb",
"order": 1,
"width": 0,
"height": 0,
"options": [
{
"label": "ID",
"value": "id",
"type": "number",
"required": true,
"rows": null
}
],
"formValue": {
"id": ""
},
"payload": "",
"submit": "Submit",
"cancel": "Cancle",
"topic": "Form",
"topicType": "str",
"splitLayout": false,
"className": "",
"x": 550,
"y": 960,
"wires": [
[
"5bfa64e0568540a9",
"66cd200dcb1a5895",
"433b3c7a7d274776"
]
]
},
{
"id": "66cd200dcb1a5895",
"type": "function",
"z": "0fce84474a62af64",
"name": "刪除一筆資料",
"func": "//\nvar id = msg.payload.id;\nvar s=global.get(\"SEL2\")\nmsg.topic=\"\";\nvar temp=\"\";\n\nif (s==2)\n{\n temp =\"DELETE FROM LEDSTATUS\";\n temp=temp+\" WHERE id LIKE '\"+ id +\"'\";\n}\n\nmsg.topic=temp;\nglobal.set(\"SEL2\",0)\nreturn msg;\n\n//SELECT * FROM COMPANY WHERE AGE LIKE 'XXX%';\n//INSERT INTO LEDSTATUS (\n//id INTEGER,\n//STATUS TEXT,\n//Date DATE,\n//Time TIME,\n//PRIMARY KEY (id)\n//);\n\n//DELETE FROM COMPANY WHERE ID = 7;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 720,
"y": 960,
"wires": [
[
"be0f63d8365b7a87",
"446c83cd171385c5"
]
]
},
{
"id": "9dd34e1574c2ee59",
"type": "link out",
"z": "0fce84474a62af64",
"name": "link out 38",
"mode": "link",
"links": [
"84140b9bc508788d"
],
"x": 915,
"y": 700,
"wires": []
},
{
"id": "84140b9bc508788d",
"type": "link in",
"z": "0fce84474a62af64",
"name": "link in 35",
"links": [
"9dd34e1574c2ee59"
],
"x": 295,
"y": 640,
"wires": [
[
"7a7aff9e3b3ee627"
]
]
},
{
"id": "be0f63d8365b7a87",
"type": "sqlite",
"z": "0fce84474a62af64",
"mydb": "19f59ce9.3edc23",
"sqlquery": "msg.topic",
"sql": "",
"name": "LED_STATUS",
"x": 960,
"y": 960,
"wires": [
[
"9dd34e1574c2ee59"
]
]
},
{
"id": "346f4edbb5f0cead",
"type": "ui_button",
"z": "0fce84474a62af64",
"name": "",
"group": "318666b083f99832",
"order": 5,
"width": 2,
"height": 1,
"passthru": false,
"label": "更正一筆資料",
"tooltip": "",
"color": "",
"bgcolor": "",
"className": "",
"icon": "",
"payload": "更正一筆資料",
"payloadType": "str",
"topic": "topic",
"topicType": "msg",
"x": 160,
"y": 1040,
"wires": [
[
"fb6ee439cad42ac3",
"ca1b1076e137437d"
]
]
},
{
"id": "dd19764ffb02f52c",
"type": "comment",
"z": "0fce84474a62af64",
"name": "UPDATE查詢的WHERE",
"info": "UPDATE查詢的WHERE子句的基本語法如下：\n\nUPDATE table_name\nSET column1 = value1, column2 = value2...., columnN = valueN\nWHERE [condition];",
"x": 180,
"y": 1080,
"wires": []
},
{
"id": "30530bbf6d0f6610",
"type": "function",
"z": "0fce84474a62af64",
"name": "更正一筆資料",
"func": "//\nvar id = global.get(\"ID\");\nvar status = msg.payload.Status;\nvar date = msg.payload.date;\nvar time = msg.payload.time;\n\nvar s=global.get(\"SEL3\")\nmsg.topic=\"\";\nvar temp=\"\";\n\nif (s==3)\n{\n temp =\"update LEDSTATUS set \";\n temp=temp+\" STATUS= '\" + status +\"'\";\n temp=temp+\" , Date= '\" + date +\"'\";\n temp=temp+\" , Time= '\" + time +\"'\";\n temp=temp+\" WHERE id=\" + id;\n \n //msg.topic = \"update LEDSTATUS set ( id , STATUS , Date , Time ) VALUES ($id, $status , $date , $time ) \" ;\n //msg.payload = [id,status,date,time]\n}\nmsg.topic=temp;\n\nreturn msg;\n\n//msg.topic = \"INSERT INTO LEDSTATUS ( STATUS , Date , Time ) VALUES ($myLED, $var_date , $var_time ) \" ;\n//msg.payload = [myLED, var_date , var_time ]\n\n\n//SELECT * FROM COMPANY WHERE AGE LIKE 'XXX%';\n//INSERT INTO LEDSTATUS (\n//id INTEGER,\n//STATUS TEXT,\n//Date DATE,\n//Time TIME,\n//PRIMARY KEY (id)\n//);\n\n//DELETE FROM COMPANY WHERE ID = 7;\n",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 820,
"y": 1080,
"wires": [
[
"be0f63d8365b7a87",
"f6b5171f52726fb3"
]
]
},
{
"id": "4287c88984667b40",
"type": "function",
"z": "0fce84474a62af64",
"name": "function flow set1",
"func": "var s1=1;\nglobal.set(\"SEL1\",s1);\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 370,
"y": 900,
"wires": [
[
"fca01d6c2e71adc8"
]
]
},
{
"id": "75de5ed9cfb84118",
"type": "function",
"z": "0fce84474a62af64",
"name": "function flow set2",
"func": "var s1=2;\nglobal.set(\"SEL2\",s1);\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 370,
"y": 960,
"wires": [
[
"fca01d6c2e71adc8"
]
]
},
{
"id": "ca1b1076e137437d",
"type": "function",
"z": "0fce84474a62af64",
"name": "function flow set3",
"func": "var s1=3;\nglobal.set(\"SEL3\",s1);\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 370,
"y": 1040,
"wires": [
[
"fca01d6c2e71adc8"
]
]
},
{
"id": "a5570ee06a2410f5",
"type": "ui_form",
"z": "0fce84474a62af64",
"name": "",
"label": "更正欄位",
"group": "e48ffa90611225eb",
"order": 2,
"width": 0,
"height": 0,
"options": [
{
"label": "STATUS",
"value": "Status",
"type": "text",
"required": true,
"rows": null
},
{
"label": "DATE",
"value": "date",
"type": "text",
"required": true,
"rows": null
},
{
"label": "TIME",
"value": "time",
"type": "text",
"required": true,
"rows": null
}
],
"formValue": {
"Status": "",
"date": "",
"time": ""
},
"payload": "",
"submit": "Submit",
"cancel": "Cancle",
"topic": "Form",
"topicType": "str",
"splitLayout": false,
"className": "",
"x": 640,
"y": 1080,
"wires": [
[
"30530bbf6d0f6610"
]
]
},
{
"id": "446c83cd171385c5",
"type": "debug",
"z": "0fce84474a62af64",
"name": "debug 213",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "topic",
"targetType": "msg",
"statusVal": "",
"statusType": "auto",
"x": 930,
"y": 920,
"wires": []
},
{
"id": "433b3c7a7d274776",
"type": "function",
"z": "0fce84474a62af64",
"name": "Store ID資料",
"func": "//\nvar id = msg.payload.id;\nglobal.set(\"ID\",id)\nreturn msg;\n\n//SELECT * FROM COMPANY WHERE AGE LIKE 'XXX%';\n//INSERT INTO LEDSTATUS (\n//id INTEGER,\n//STATUS TEXT,\n//Date DATE,\n//Time TIME,\n//PRIMARY KEY (id)\n//);\n\n//DELETE FROM COMPANY WHERE ID = 7;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 650,
"y": 1020,
"wires": [
[
"a5570ee06a2410f5",
"e71d4b82def89b87"
]
]
},
{
"id": "e71d4b82def89b87",
"type": "function",
"z": "0fce84474a62af64",
"name": "查詢一筆資料",
"func": "//\nvar id = global.get(\"ID\");\nmsg.topic=\"\";\nvar temp=\"\";\ntemp =\"SELECT * FROM LEDSTATUS\";\ntemp=temp+\" WHERE id LIKE '\"+ id +\"'\";\n\nmsg.topic=temp;\n\nreturn msg;\n\n//SELECT * FROM COMPANY WHERE AGE LIKE 'XXX%';\n//INSERT INTO LEDSTATUS (\n//id INTEGER,\n//STATUS TEXT,\n//Date DATE,\n//Time TIME,\n//PRIMARY KEY (id)\n//);\n\n//SELECT * FROM LEDSTATUS ORDER BY id DESC LIMIT 50;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 840,
"y": 1020,
"wires": [
[
"7c3ae79a4f4cf1ef"
]
]
},
{
"id": "f6b5171f52726fb3",
"type": "debug",
"z": "0fce84474a62af64",
"name": "debug 214",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "topic",
"targetType": "msg",
"statusVal": "",
"statusType": "auto",
"x": 1010,
"y": 1080,
"wires": []
},
{
"id": "18d98d931bc98ef7",
"type": "mqtt in",
"z": "0fce84474a62af64",
"name": "LED status ",
"topic": "alex9ufo/esp32/led_status",
"qos": "2",
"datatype": "utf8",
"broker": "584db2f88f8050c2",
"nl": false,
"rap": true,
"rh": 0,
"inputs": 0,
"x": 140,
"y": 1200,
"wires": [
[
"b5a91ff8df2e27fa"
]
]
},
{
"id": "b5a91ff8df2e27fa",
"type": "function",
"z": "0fce84474a62af64",
"name": "function ",
"func": "msg.payload=\" ---ESP32回來資料---\" +msg.payload;\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 320,
"y": 1200,
"wires": [
[
"435d767821e7e612",
"eb2e6c45fdbbeca1",
"dd7b32287347a376"
]
]
},
{
"id": "435d767821e7e612",
"type": "function",
"z": "0fce84474a62af64",
"name": "Set Line API ",
"func": "msg.headers = {'content-type':'application/x-www-form-urlencoded','Authorization':'Bearer A4wwPNh2WqB7dlfeQyyIAwtggn1kfZSI5LkkCdia1gB'};\nmsg.payload = {\"message\":msg.payload};\nreturn msg;\n\n//oR7KdXvK1eobRr2sRRgsl4PMq23DjDlhfUs96SyUBZu",
"outputs": 1,
"noerr": 0,
"x": 510,
"y": 1200,
"wires": [
[
"3b30a4b3055dfe32"
]
]
},
{
"id": "3b30a4b3055dfe32",
"type": "http request",
"z": "0fce84474a62af64",
"name": "",
"method": "POST",
"ret": "txt",
"paytoqs": false,
"url": "https://notify-api.line.me/api/notify",
"tls": "",
"persist": false,
"proxy": "",
"authType": "",
"x": 680,
"y": 1200,
"wires": [
[
"8e97dc34895a6846"
]
]
},
{
"id": "8e97dc34895a6846",
"type": "debug",
"z": "0fce84474a62af64",
"name": "debug 216",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "false",
"statusVal": "",
"statusType": "auto",
"x": 850,
"y": 1200,
"wires": []
},
{
"id": "a757e9f9926f150c",
"type": "comment",
"z": "0fce84474a62af64",
"name": "Line Notify Message ",
"info": "",
"x": 530,
"y": 1160,
"wires": []
},
{
"id": "2134c09f9662eb47",
"type": "mqtt out",
"z": "0fce84474a62af64",
"name": "Control LED",
"topic": "alex9ufo/esp32/led",
"qos": "1",
"retain": "true",
"respTopic": "",
"contentType": "",
"userProps": "",
"correl": "",
"expiry": "",
"broker": "584db2f88f8050c2",
"x": 350,
"y": 340,
"wires": []
},
{
"id": "864d37c9866527aa",
"type": "comment",
"z": "0fce84474a62af64",
"name": "alex9ufo/esp32/led",
"info": "",
"x": 410,
"y": 380,
"wires": []
},
{
"id": "a78819d1ad7987af",
"type": "ui_text",
"z": "0fce84474a62af64",
"group": "11d8514.a44dcaf",
"order": 6,
"width": 6,
"height": 1,
"name": "",
"label": "Node-RED發行到MQTT的資料 : ",
"format": "{{msg.payload}}",
"layout": "row-left",
"className": "",
"x": 410,
"y": 480,
"wires": []
},
{
"id": "dd7b32287347a376",
"type": "ui_text",
"z": "0fce84474a62af64",
"group": "318666b083f99832",
"order": 8,
"width": 12,
"height": 1,
"name": "",
"label": "Node-RED 訂閱MQTT的資料 : ",
"format": "{{msg.payload}}",
"layout": "row-left",
"className": "",
"x": 570,
"y": 1280,
"wires": []
},
{
"id": "6f1737d4670a631c",
"type": "ui_audio",
"z": "0fce84474a62af64",
"name": "",
"group": "318666b083f99832",
"voice": "Microsoft Hanhan - Chinese (Traditional, Taiwan)",
"always": "",
"x": 680,
"y": 1240,
"wires": []
},
{
"id": "eb2e6c45fdbbeca1",
"type": "delay",
"z": "0fce84474a62af64",
"name": "",
"pauseType": "delay",
"timeout": "1",
"timeoutUnits": "seconds",
"rate": "1",
"nbRateUnits": "1",
"rateUnits": "second",
"randomFirst": "1",
"randomLast": "5",
"randomUnits": "seconds",
"drop": false,
"allowrate": false,
"outputs": 1,
"x": 500,
"y": 1240,
"wires": [
[
"6f1737d4670a631c"
]
]
},
{
"id": "bd403ae2676faf3a",
"type": "comment",
"z": "0fce84474a62af64",
"name": "資料庫位置 C:\\Users\\User\\.node-red\\LED_STATUS.db",
"info": "",
"x": 660,
"y": 260,
"wires": []
},
{
"id": "47fb3b52b966c1ca",
"type": "comment",
"z": "0fce84474a62af64",
"name": "資料庫位置 C:\\Users\\User\\.node-red\\LED_STATUS.db",
"info": "",
"x": 540,
"y": 100,
"wires": []
},
{
"id": "19f59ce9.3edc23",
"type": "sqlitedb",
"db": "C:\\Users\\User\\.node-red\\LED_STATUS.db",
"mode": "RWC"
},
{
"id": "318666b083f99832",
"type": "ui_group",
"name": "命令",
"tab": "8f1ada5fa4df30e2",
"order": 4,
"disp": true,
"width": "6",
"collapse": false,
"className": ""
},
{
"id": "11d8514.a44dcaf",
"type": "ui_group",
"name": "控制",
"tab": "8f1ada5fa4df30e2",
"order": 2,
"disp": true,
"width": "6",
"collapse": false,
"className": ""
},
{
"id": "821196f103500796",
"type": "ui_group",
"name": "顯示",
"tab": "8f1ada5fa4df30e2",
"order": 2,
"disp": true,
"width": 10,
"collapse": false,
"className": ""
},
{
"id": "e48ffa90611225eb",
"type": "ui_group",
"name": "單筆資料",
"tab": "8f1ada5fa4df30e2",
"order": 4,
"disp": true,
"width": 4,
"collapse": false,
"className": ""
},
{
"id": "584db2f88f8050c2",
"type": "mqtt-broker",
"name": "broker.mqttgo.io",
"broker": "broker.mqttgo.io",
"port": "1883",
"clientid": "",
"autoConnect": true,
"usetls": false,
"protocolVersion": "4",
"keepalive": "60",
"cleansession": true,
"birthTopic": "",
"birthQos": "0",
"birthPayload": "",
"birthMsg": {},
"closeTopic": "",
"closeQos": "0",
"closePayload": "",
"closeMsg": {},
"willTopic": "",
"willQos": "0",
"willPayload": "",
"willMsg": {},
"userProps": "",
"sessionExpiry": ""
},
{
"id": "8f1ada5fa4df30e2",
"type": "ui_tab",
"name": "作業2-1",
"icon": "dashboard",
"disabled": false,
"hidden": false
}
]
沒有留言:
張貼留言