2024年11月24日 星期日

WOKWI LED + MQTT Node-Red SQLite

WOKWI LED + MQTT Node-Red SQLite




const char *mqtt_broker = "broker.mqtt-dashboard.com";

const char *topic1 = "alex9ufo/esp32/led";

const char *topic = "alex9ufo/esp32/Starting";

const char *topic3 = "alex9ufo/esp32/led_status";








//wifi &  MQTT
#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 *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);
  }

}
//===========================================================


[{"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":"4e71ffcc.32ba8","sqlquery":"msg.topic","sql":"","name":"LED_STATUS","x":420,"y":140,"wires":[["0979147ed72dcfb0"]]},{"id":"0979147ed72dcfb0","type":"debug","z":"0fce84474a62af64","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":590,"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":"4e71ffcc.32ba8","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":"4e71ffcc.32ba8","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":"4e71ffcc.32ba8","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":"4e71ffcc.32ba8","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":"4e71ffcc.32ba8","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":"4e71ffcc.32ba8","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":"841df58d.ee5e98","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":"841df58d.ee5e98","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":"4e71ffcc.32ba8","type":"sqlitedb","db":"C:\\Users\\User\\.node-red\\202409_DHT22.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":"841df58d.ee5e98","type":"mqtt-broker","name":"","broker":"broker.mqtt-dashboard.com","port":"1883","clientid":"","autoConnect":true,"usetls":false,"compatmode":false,"protocolVersion":"4","keepalive":"15","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","birthMsg":{},"closeTopic":"","closePayload":"","closeMsg":{},"willTopic":"","willQos":"0","willPayload":"","willMsg":{},"userProps":"","sessionExpiry":""},{"id":"8f1ada5fa4df30e2","type":"ui_tab","name":"作業2-1","icon":"dashboard","disabled":false,"hidden":false}]

沒有留言:

張貼留言

2024_09 作業3 以Node-Red 為主

 2024_09 作業3  (以Node-Red 為主  Arduino 可能需要配合修改 ) Arduino 可能需要修改的部分 1)mqtt broker  2) 主題Topic (發行 接收) 3) WIFI ssid , password const char br...