alex9ufo 聰明人求知心切: 2023 實驗2-1 ESP32 Control Node-RED , MQTT , SQLite database , Line Notify

2023 實驗2-1 ESP32 Control Node-RED , MQTT , SQLite database , Line Notify

[{"id":"b2b2b7ac0717289d","type":"mqtt in","z":"b1b390fdaae2d3e3","name":"led_status","topic":"alex9ufo/led/led_status","qos":"1","datatype":"auto","broker":"841df58d.ee5e98","nl":false,"rap":false,"inputs":0,"x":80,"y":80,"wires":[["6c1a98ebb61b3485","66e5f116ea7b4f58","f7f0c86be6176434"]]},{"id":"e2c435e28260bcba","type":"ui_text","z":"b1b390fdaae2d3e3","group":"86e80a6f76a46e3f","order":0,"width":0,"height":0,"name":"","label":"ESP32發行到MQTT的資料","format":"{{msg.payload}}","layout":"col-center","className":"","x":520,"y":120,"wires":[]},{"id":"871d77f614a65e7d","type":"ui_button","z":"b1b390fdaae2d3e3","name":"","group":"86e80a6f76a46e3f","order":0,"width":"3","height":"1","passthru":false,"label":"LED 開","tooltip":"","color":"white","bgcolor":"","className":"","icon":"fa-circle","payload":"on","payloadType":"str","topic":"","topicType":"str","x":80,"y":320,"wires":[["af700ef9cff41c22","8504b99d47d7370e","90d1c3cbe38315d2"]]},{"id":"fd1a6f1acb8a4c3f","type":"ui_button","z":"b1b390fdaae2d3e3","name":"","group":"86e80a6f76a46e3f","order":0,"width":"3","height":"1","passthru":false,"label":"LED 關","tooltip":"","color":"black","bgcolor":"","className":"","icon":"fa-circle-o","payload":"off","payloadType":"str","topic":"","topicType":"str","x":80,"y":360,"wires":[["af700ef9cff41c22","8504b99d47d7370e","90d1c3cbe38315d2"]]},{"id":"af700ef9cff41c22","type":"mqtt out","z":"b1b390fdaae2d3e3","name":"","topic":"alex9ufo/inTopic/led/led_event","qos":"1","retain":"true","broker":"841df58d.ee5e98","x":390,"y":420,"wires":[]},{"id":"6c1a98ebb61b3485","type":"function","z":"b1b390fdaae2d3e3","name":"Format timestamp","func":"var date = new Date();\nvar h = date.getHours()+8;\nvar m = date.getMinutes();\nvar s = date.getSeconds();\nif(h<10){\n h = '0'+h;\n}\nif(m<10){\n m = '0' + m;\n}\nif(s<10){\n s = '0' + s;\n}\nmsg.payload = msg.payload + ' --> Time:(' + h + ':' + m + ':' + s + ')' ;\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":170,"y":200,"wires":[["e12e90bf90b9be24"]]},{"id":"e12e90bf90b9be24","type":"function","z":"b1b390fdaae2d3e3","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":130,"y":260,"wires":[["1a1530f4b8861aa4"]]},{"id":"1a1530f4b8861aa4","type":"http request","z":"b1b390fdaae2d3e3","name":"","method":"POST","ret":"txt","paytoqs":false,"url":"https://notify-api.line.me/api/notify","tls":"","persist":false,"proxy":"","authType":"","x":300,"y":260,"wires":[["6454e80aad6d5344"]]},{"id":"6454e80aad6d5344","type":"debug","z":"b1b390fdaae2d3e3","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":450,"y":260,"wires":[]},{"id":"66e5f116ea7b4f58","type":"debug","z":"b1b390fdaae2d3e3","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":370,"y":80,"wires":[]},{"id":"69a860a1c0bad4ce","type":"ui_audio","z":"b1b390fdaae2d3e3","name":"","group":"86e80a6f76a46e3f","voice":"zh-TW","always":true,"x":460,"y":160,"wires":[]},{"id":"72d4b4c4cdfc3119","type":"ui_button","z":"b1b390fdaae2d3e3","name":"","group":"86e80a6f76a46e3f","order":0,"width":"3","height":"1","passthru":false,"label":"LED 開關反向","tooltip":"","color":"blue","bgcolor":"","className":"","icon":"fa-circle-o","payload":"toggle","payloadType":"str","topic":"","topicType":"str","x":100,"y":400,"wires":[["af700ef9cff41c22","8504b99d47d7370e","90d1c3cbe38315d2"]]},{"id":"f7f0c86be6176434","type":"function","z":"b1b390fdaae2d3e3","name":"","func":"var st1;\nif (msg.payload === \"on\") {\n st1=\"LED開\"; \n} \nelse if (msg.payload === \"off\") {\n st1=\"LED關\";\n}\nelse if (msg.payload === \"flash\") {\n st1=\"LED閃爍\";\n}\nelse if (msg.payload === \"timer\") {\n st1=\"LED開五秒鐘\";\n}\nelse if (msg.payload === \"toggle now=off\") {\n st1=\"LED ON OFF 交換目前= off\";\n}\nelse if (msg.payload === \"toggle now=on\") {\n st1=\"LED ON OFF 交換目前= on\";\n}\n\nmsg.payload=st1;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":290,"y":160,"wires":[["69a860a1c0bad4ce","e2c435e28260bcba","fac5a5b09fa1a36d"]]},{"id":"e74061f97b12d04e","type":"ui_button","z":"b1b390fdaae2d3e3","name":"","group":"86e80a6f76a46e3f","order":0,"width":"3","height":"1","passthru":false,"label":"LED 開5秒鐘","tooltip":"","color":"purple","bgcolor":"","className":"","icon":"fa-circle-o","payload":"timer","payloadType":"str","topic":"","topicType":"str","x":90,"y":480,"wires":[["af700ef9cff41c22","8504b99d47d7370e","90d1c3cbe38315d2"]]},{"id":"3006db040f96d5e1","type":"debug","z":"b1b390fdaae2d3e3","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":830,"y":200,"wires":[]},{"id":"17db33e30c4f2e8f","type":"comment","z":"b1b390fdaae2d3e3","name":"Node-Red publish 到 HiveMQ Broker , ESP32訂閱","info":"將 alex9ufo/inTopic 到(publish)HiveMQ Broker \n給 Arduino 訂閱(Subscribe)","x":210,"y":520,"wires":[]},{"id":"2f2053cc18f0442d","type":"comment","z":"b1b390fdaae2d3e3","name":"ESP32向 HiveMQ Broker發行 Node-Red 訂閱subscribe","info":"將 Arduino 發行到(publish)HiveMQ Broker alex9ufo/led/led_status \n給 Node-red 或 MQTTB-Box 訂閱(Subscribe)","x":220,"y":40,"wires":[]},{"id":"9c969d6471e7995b","type":"ui_button","z":"b1b390fdaae2d3e3","name":"","group":"86e80a6f76a46e3f","order":0,"width":"3","height":"1","passthru":false,"label":"LED 閃爍","tooltip":"","color":"yellow","bgcolor":"","className":"","icon":"fa-circle-o","payload":"flash","payloadType":"str","topic":"","topicType":"str","x":80,"y":440,"wires":[["af700ef9cff41c22","8504b99d47d7370e","90d1c3cbe38315d2"]]},{"id":"8504b99d47d7370e","type":"ui_audio","z":"b1b390fdaae2d3e3","name":"","group":"86e80a6f76a46e3f","voice":"zh-TW","always":true,"x":340,"y":480,"wires":[]},{"id":"8582272f17b486f9","type":"ui_text","z":"b1b390fdaae2d3e3","group":"86e80a6f76a46e3f","order":0,"width":0,"height":0,"name":"","label":"Node-RED發行到MQTT的資料","format":"{{msg.payload}}","layout":"row-center","x":410,"y":380,"wires":[]},{"id":"23cf72b9e8ccde8a","type":"inject","z":"b1b390fdaae2d3e3","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":650,"y":400,"wires":[["2dc7858f21b14764"]]},{"id":"03385bb36b6c0ceb","type":"ui_button","z":"b1b390fdaae2d3e3","name":"建立LED資料庫(只能執行一次)","group":"86e80a6f76a46e3f","order":66,"width":"3","height":"1","passthru":false,"label":"建立LED資料庫(只能執行一次)","tooltip":"","color":"","bgcolor":" purple","className":"","icon":"","payload":"","payloadType":"str","topic":"","topicType":"str","x":690,"y":340,"wires":[["2dc7858f21b14764"]]},{"id":"2dc7858f21b14764","type":"sqlite","z":"b1b390fdaae2d3e3","mydb":"19f59ce9.3edc23","sqlquery":"fixed","sql":"CREATE TABLE LEDS (id INTEGER, timeled TEXT,ledstatus TEXT, PRIMARY KEY(id AUTOINCREMENT))","name":"LED Status","x":830,"y":400,"wires":[["dcb0fe93e8b49030"]]},{"id":"dcb0fe93e8b49030","type":"debug","z":"b1b390fdaae2d3e3","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":1010,"y":400,"wires":[]},{"id":"a214fa3ebc300e8b","type":"ui_template","z":"b1b390fdaae2d3e3","group":"86e80a6f76a46e3f","name":"","order":0,"width":"12","height":"4","format":"<table style=\"width:100%\">\n <tr>\n <th>Index</th> \n <th>Time</th> \n <th>Status</th>\n </tr>\n <tr ng-repeat=\"x in msg.payload | limitTo:20\">\n <td>{{$index}}</td>\n <td>{{msg.payload[$index].timeled }}</td>\n <td>{{msg.payload[$index].ledstatus}}</td> \n </tr>\n</table>\n","storeOutMessages":true,"fwdInMessages":true,"resendOnRefresh":false,"templateScope":"local","className":"","x":980,"y":460,"wires":[[]]},{"id":"fed683d8d275ab80","type":"ui_button","z":"b1b390fdaae2d3e3","name":"","group":"86e80a6f76a46e3f","order":10,"width":"3","height":"1","passthru":false,"label":"檢視LED資料庫","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"檢視LED資料庫","payloadType":"str","topic":"SELECT * FROM LEDS ORDER BY id DESC LIMIT 100;","topicType":"str","x":640,"y":460,"wires":[["a1e931339b04da29"]]},{"id":"90d1c3cbe38315d2","type":"function","z":"b1b390fdaae2d3e3","name":"function 27","func":"msg.payload=\" ---到 ESP32的資料---\" +msg.payload;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":290,"y":320,"wires":[["8582272f17b486f9"]]},{"id":"d5f28e15099f35b9","type":"debug","z":"b1b390fdaae2d3e3","name":"debug 96","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":640,"y":160,"wires":[]},{"id":"f62546ee363c90cb","type":"sqlite","z":"b1b390fdaae2d3e3","mydb":"19f59ce9.3edc23","sqlquery":"prepared","sql":"insert into LEDS (timeled ,ledstatus) values ($thetime , $thevalue)\n","name":"LED","x":630,"y":200,"wires":[["3006db040f96d5e1"]]},{"id":"fac5a5b09fa1a36d","type":"function","z":"b1b390fdaae2d3e3","name":"INSERT","func":"msg.topic = \"INSERT INTO LED (time_led, led_status) VALUES (?,?)\";\n//msg.topic = \"INSERT INTO LED (id, time_led, led_status) VALUES (?,?,?)\";\n\n\nvar 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(); //秒\n\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}\n\nvar hms= yyyy + '/'+ MM + '/'+ dd + ' ' + h + ':' + m + ':' + s ;\n//var id= Date.now() ;\n//msg.payload = [id ,hms, msg.payload];\nmsg.params = { $thetime:hms, $thevalue:msg.payload }\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":460,"y":200,"wires":[["f62546ee363c90cb","d5f28e15099f35b9"]]},{"id":"a1e931339b04da29","type":"sqlite","z":"b1b390fdaae2d3e3","mydb":"19f59ce9.3edc23","sqlquery":"msg.topic","sql":"CREATE TABLE LEDS (id INTEGER, timeled TEXT,ledstatus TEXT, PRIMARY KEY(id AUTOINCREMENT))","name":"LED Status","x":830,"y":460,"wires":[["a214fa3ebc300e8b"]]},{"id":"841df58d.ee5e98","type":"mqtt-broker","name":"","broker":"broker.mqtt-dashboard.com","port":"1883","clientid":"","usetls":false,"compatmode":false,"keepalive":"15","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closePayload":"","willTopic":"","willQos":"0","willPayload":""},{"id":"86e80a6f76a46e3f","type":"ui_group","name":"LED讀取","tab":"14d52ac953acc78d","order":1,"disp":true,"width":"15","collapse":false},{"id":"19f59ce9.3edc23","type":"sqlitedb","db":"C:\\Users\\User\\.node-red\\EX2_1_2023.db","mode":"RWC"},{"id":"14d52ac953acc78d","type":"ui_tab","name":"LED","icon":"dashboard","order":1,"disabled":false,"hidden":false}]

#include <WiFi.h>
#include <PubSubClient.h>

// Replace the next variables with your SSID/Password combination
//const char* ssid = "XXXXXXXXX";
//const char* password = "XXXXXXXXX";
const char* ssid = "alex9ufo"; // Enter your Wi-Fi name
const char* password = "alex9981";  // Enter Wi-Fi password


// Add your MQTT Broker IP address, example:
//const char* mqtt_server = "192.168.1.144";
const char* mqtt_server = "broker.mqtt-dashboard.com";
const char *mqtt_username = "alex9ufo";
const char *mqtt_password = "public";
const int mqtt_port = 1883;


WiFiClient espClient;
PubSubClient client(espClient);
char msg[50];
const char *topic1 = "alex9ufo/inTopic/led/led_event";  //SUBSCRIBE FROM mqtt (broker.mqtt-dashboard.com)
const char *topic2 = "alex9ufo/led/led_status";  //PUBLISH TO mqtt (broker.mqtt-dashboard.com)

// LED Pin
#define BUILTIN_LED 2
String json = "";     //client.publish("alex9ufo/led/led_status",
char jsonChar2[20];
bool Flash = false;  //true
bool Timer = false;  //true
bool Send = false;  //true
int Count= 0;
//==========================================
void setup() {
  Serial.begin(115200);
  // default settings
  delay(500);
  setup_wifi();
  client.setServer(mqtt_server, 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);

  pinMode(BUILTIN_LED, OUTPUT);
}
//==========================================
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("ESP32 WiFi connected to ");
  Serial.println("IP address: ");
  Serial.println(WiFi.localIP());
}
//==========================================
void callback(char* topic, byte* message, unsigned int length) {
  Serial.print("Message arrived on topic: ");
  Serial.print(topic);
  Serial.print(". Message: ");
  String messageTemp;
  
  for (int i = 0; i < length; i++) {
    Serial.print((char)message[i]);
    messageTemp += (char)message[i];
  }
  Serial.println();

  // Feel free to add more if statements to control more GPIOs with MQTT

  // If a message is received on the topic esp32/output, you check if the message is either "on" or "off". 
  // Changes the output state according to the message
  if (String(topic) == "alex9ufo/inTopic/led/led_event") {
    Serial.print("Changing output to ");
    if(messageTemp == "on"){
      Serial.println("on");
      digitalWrite(BUILTIN_LED, HIGH);
      Flash = false;
      Timer = false;
      json ="on";
      Send = true ;
    }
    else if(messageTemp == "off"){
      Serial.println("off");
      digitalWrite(BUILTIN_LED, LOW);
      Flash = false;
      Timer = false;
      json ="off";
      Send = true ;
    }
    else if(messageTemp == "toggle"){
      digitalWrite(BUILTIN_LED, !digitalRead(BUILTIN_LED));   // Turn the LED toggle 
      bool val = digitalRead (BUILTIN_LED);
      Serial.println("toggle");
      Flash = false; 
      Timer = false;
      Send = true ;     
      json ="toggle now=off"; 
      if (val) {
        json ="toggle now=on";
      }

    }
    else if(messageTemp == "flash"){
      digitalWrite(BUILTIN_LED, HIGH);   // Turn the LED off (Note that HIGH is the voltage level
      Serial.println("flash");
      Flash = true;
      Timer = false;
      json ="flash";
      Send = true ;  
    }
    else if(messageTemp == "timer"){
      digitalWrite(BUILTIN_LED, HIGH);   // Turn the LED off (Note that HIGH is the voltage level
      Serial.println("timer");
      Flash = false;
      Timer = true;
      Count= 10;
      json ="timer";
      Send = true ; 
    }
  }
}
//==========================================
void reconnect() {
  // Loop until we're reconnected
  while (!client.connected()) {
    Serial.print("Attempting MQTT connection...");
    // Attempt to connect
    if (client.connect("ESP32Client")) {
      Serial.println("connected");
      // Subscribe
      client.subscribe(topic1);
    } else {
      Serial.print("failed, rc=");
      Serial.print(client.state());
      Serial.println(" try again in 5 seconds");
      // Wait 5 seconds before retrying
      delay(5000);
    }
  }
}
//==========================================
void loop() {
  if (!client.connected()) {
    reconnect();
  }
  client.loop();
  //----------------------------------
  if (Flash)
  {
    digitalWrite(BUILTIN_LED, !digitalRead(BUILTIN_LED));
    delay(500);
  }
  if (Timer) 
  {
    digitalWrite(BUILTIN_LED, HIGH);
    delay(500);
    Count=Count-1;
    if (Count == 0 ){
       Timer=false;
       digitalWrite(BUILTIN_LED, LOW);
    }
  }
  if (Send) {
    // Convert JSON string to character array

    json.toCharArray(jsonChar2, json.length()+1);
    if  (client.connected()) {
         Serial.print(topic2);
         Serial.print("---Publish message: ");
         Serial.println(json);
         // Publish JSON character array to MQTT topic
         client.publish(topic2,jsonChar2);
    } 
    Send = false;    
  }
  //----------------------------------

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

alex9ufo 聰明人求知心切

2023年7月13日星期四

2023 實驗2-1 ESP32 Control Node-RED , MQTT , SQLite database , Line Notify

沒有留言:

張貼留言

113 學年度第 1 學期 RFID應用課程 Arduino程式

2023年7月13日 星期四

2023 實驗2-1 ESP32 Control Node-RED , MQTT , SQLite database , Line Notify

沒有留言:

張貼留言

113 學年度第 1 學期 RFID應用課程 Arduino程式

2023年7月13日星期四