2023年6月12日 星期一

ESP32 LED + MQTT+Node-Red + SQLite

 ESP32 LED + MQTT+Node-Red + SQLite












[{"id":"1d043f992e594832","type":"mqtt in","z":"abeb9ac07c0f42bf","name":"Receive From ESP32","topic":"alex9ufo/outTopic/led/led_event","qos":"1","datatype":"auto","broker":"841df58d.ee5e98","nl":false,"rap":false,"inputs":0,"x":200,"y":80,"wires":[["9f5ab78624a00a9c","21f8d8c05955cf9b","1e137239477d0f8a"]]},{"id":"d096f1714e74a828","type":"ui_text","z":"abeb9ac07c0f42bf","group":"6c9116b.b62d4e8","order":0,"width":0,"height":0,"name":"","label":"ESP32發行到MQTT的資料","format":"{{msg.payload}}","layout":"col-center","x":600,"y":120,"wires":[]},{"id":"4efd05adca200baa","type":"ui_button","z":"abeb9ac07c0f42bf","name":"","group":"6c9116b.b62d4e8","order":0,"width":0,"height":0,"passthru":false,"label":"LED 開","tooltip":"","color":"white","bgcolor":"","icon":"fa-circle","payload":"ON","payloadType":"str","topic":"","x":160,"y":400,"wires":[["6865b15d3b8b9e2b","ae99c1672227ca4b","84196883d2018e01"]]},{"id":"795e06d959339717","type":"ui_button","z":"abeb9ac07c0f42bf","name":"","group":"6c9116b.b62d4e8","order":0,"width":0,"height":0,"passthru":false,"label":"LED 關","tooltip":"","color":"black","bgcolor":"","className":"","icon":"fa-circle-o","payload":"OFF","payloadType":"str","topic":"","topicType":"str","x":160,"y":440,"wires":[["6865b15d3b8b9e2b","ae99c1672227ca4b","84196883d2018e01"]]},{"id":"6865b15d3b8b9e2b","type":"mqtt out","z":"abeb9ac07c0f42bf","name":"Control ESP32 LED","topic":"alex9ufo/inTopic/led/led_event","qos":"1","retain":"true","respTopic":"","contentType":"","userProps":"","correl":"","expiry":"","broker":"841df58d.ee5e98","x":440,"y":500,"wires":[]},{"id":"9f5ab78624a00a9c","type":"debug","z":"abeb9ac07c0f42bf","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":450,"y":80,"wires":[]},{"id":"bcefc879d1be48a2","type":"ui_audio","z":"abeb9ac07c0f42bf","name":"","group":"6c9116b.b62d4e8","voice":"zh-TW","always":true,"x":540,"y":160,"wires":[]},{"id":"70576e3fca5ec868","type":"ui_button","z":"abeb9ac07c0f42bf","name":"","group":"6c9116b.b62d4e8","order":0,"width":0,"height":0,"passthru":false,"label":"LED 開關反向","tooltip":"","color":"blue","bgcolor":"","className":"","icon":"fa-circle-o","payload":"TOGGLE","payloadType":"str","topic":"","topicType":"str","x":180,"y":480,"wires":[["6865b15d3b8b9e2b","ae99c1672227ca4b","84196883d2018e01"]]},{"id":"21f8d8c05955cf9b","type":"function","z":"abeb9ac07c0f42bf","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\") {\n  st1=\"LED ON OFF 交換\";\n}\n\nmsg.payload=st1;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":370,"y":160,"wires":[["bcefc879d1be48a2","d096f1714e74a828","8d29182a82b398f8"]]},{"id":"6118254f24394b62","type":"ui_button","z":"abeb9ac07c0f42bf","name":"","group":"6c9116b.b62d4e8","order":0,"width":0,"height":0,"passthru":false,"label":"LED 開5秒鐘","tooltip":"","color":"purple","bgcolor":"","icon":"fa-circle-o","payload":"TIMER","payloadType":"str","topic":"","x":170,"y":560,"wires":[["6865b15d3b8b9e2b","ae99c1672227ca4b","84196883d2018e01"]]},{"id":"d80c61b5bf8278a5","type":"comment","z":"abeb9ac07c0f42bf","name":"Node-Red publish 到 HiveMQ Broker  , ESP32訂閱","info":"將 alex9ufo/inTopic 到(publish)HiveMQ Broker \n給 Arduino 訂閱(Subscribe)","x":290,"y":600,"wires":[]},{"id":"12da4e5307be0c00","type":"comment","z":"abeb9ac07c0f42bf","name":"ESP32向 HiveMQ Broker發行 Node-Red 訂閱subscribe","info":"將  Arduino 發行到(publish)HiveMQ Broker alex9ufo/led/led_status \n給 Node-red 或 MQTTB-Box 訂閱(Subscribe)","x":300,"y":40,"wires":[]},{"id":"392701ef3a2dfdf8","type":"ui_button","z":"abeb9ac07c0f42bf","name":"","group":"6c9116b.b62d4e8","order":0,"width":0,"height":0,"passthru":false,"label":"LED 閃爍","tooltip":"","color":"yellow","bgcolor":"","icon":"fa-circle-o","payload":"FLASH","payloadType":"str","topic":"","x":160,"y":520,"wires":[["6865b15d3b8b9e2b","ae99c1672227ca4b","84196883d2018e01"]]},{"id":"ae99c1672227ca4b","type":"ui_audio","z":"abeb9ac07c0f42bf","name":"","group":"6c9116b.b62d4e8","voice":"zh-TW","always":true,"x":420,"y":560,"wires":[]},{"id":"84196883d2018e01","type":"ui_text","z":"abeb9ac07c0f42bf","group":"6c9116b.b62d4e8","order":0,"width":0,"height":0,"name":"","label":"Node-RED發行到MQTT的資料","format":"{{msg.payload}}","layout":"row-center","x":470,"y":440,"wires":[]},{"id":"1faac91f7a51160e","type":"function","z":"abeb9ac07c0f42bf","name":"Set Line API ","func":"msg.headers = {'content-type':'application/x-www-form-urlencoded','Authorization':'Bearer cEI4hx24xyopKGAArgZcKJNHE1V7KeeIi4Lzny3dDNO'};\nmsg.payload = {\"message\":msg.payload};\nreturn msg;","outputs":1,"noerr":0,"x":490,"y":320,"wires":[["dd5171fe4158b3fe"]]},{"id":"1e137239477d0f8a","type":"function","z":"abeb9ac07c0f42bf","name":"Format timestamp","func":"var date = new Date();\nvar h = date.getHours();\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":290,"y":320,"wires":[["1faac91f7a51160e"]]},{"id":"dd5171fe4158b3fe","type":"http request","z":"abeb9ac07c0f42bf","name":"","method":"POST","ret":"txt","url":"https://notify-api.line.me/api/notify","tls":"","x":640,"y":320,"wires":[["bb5995536de3a60c"]]},{"id":"bb5995536de3a60c","type":"debug","z":"abeb9ac07c0f42bf","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":810,"y":320,"wires":[]},{"id":"d451ec203b4a49a9","type":"inject","z":"abeb9ac07c0f42bf","name":"CREATE","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":440,"y":360,"wires":[["d4c4ccc4101355ce"]]},{"id":"d4c4ccc4101355ce","type":"sqlite","z":"abeb9ac07c0f42bf","mydb":"af927aba.0dc108","sqlquery":"fixed","sql":"create table LED (id INTEGER, timeled TEXT,ledstatus TEXT, PRIMARY KEY(id AUTOINCREMENT))","name":"CREATE TABLE","x":680,"y":360,"wires":[["bf02f94aad11b1fa"]]},{"id":"bf02f94aad11b1fa","type":"debug","z":"abeb9ac07c0f42bf","name":"debug  ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":850,"y":360,"wires":[]},{"id":"8a746b01d0af0c94","type":"ui_button","z":"abeb9ac07c0f42bf","name":"CREATE SQLite db","group":"6c9116b.b62d4e8","order":36,"width":0,"height":0,"passthru":false,"label":"CREATE SQLite db","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"","payloadType":"str","topic":"topic","topicType":"msg","x":470,"y":400,"wires":[["d4c4ccc4101355ce"]]},{"id":"8d29182a82b398f8","type":"function","z":"abeb9ac07c0f42bf","name":"msg.params","func":"// Create a Params variable\n// with a time and value component\n//msg.topic = \"INSERT INTO LED (id, timeled, ledstatus) VALUES (?,?,?)\";\n\nvar date = new Date();\nvar h = date.getHours();\nvar m = date.getMinutes();\nvar s = date.getSeconds();\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= h + '-' + m + '-' + s ;\n\nmsg.params = { $thetime:hms, $thevalue:msg.payload }\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":470,"y":220,"wires":[["e7d88062a14ef078"]]},{"id":"e7d88062a14ef078","type":"sqlite","z":"abeb9ac07c0f42bf","mydb":"af927aba.0dc108","sqlquery":"prepared","sql":"insert into LED (timeled, ledstatus) values ($thetime , $thevalue)","name":"INSERT INTO SQLITE","x":660,"y":220,"wires":[["6ce775442c95cfb0"]]},{"id":"6ce775442c95cfb0","type":"debug","z":"abeb9ac07c0f42bf","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":830,"y":260,"wires":[]},{"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":"6c9116b.b62d4e8","type":"ui_group","name":"Storing IOT Data ","tab":"eeb8e179.a47a4","order":1,"disp":true,"width":"6","collapse":true},{"id":"af927aba.0dc108","type":"sqlitedb","db":"C:\\Users\\User\\.node-red\\db_0611_led.db","mode":"RWC"},{"id":"eeb8e179.a47a4","type":"ui_tab","name":"eWeLink","icon":"dashboard","disabled":false,"hidden":false}]



#include <WiFi.h>
#include <PubSubClient.h>
#include <SPI.h>
#define BUILTIN_LED 2
// Update these with values suitable for your network.
//const char *ssid = "PTS-2F";
//const char *pass = "PTS6662594";
//const char *ssid = "alex9ufo";
//const char *pass = "alex9981";

//const char *ssid = "WBR-2200";
//const char *pass = "0226452362";
const char *ssid = "TOTOLINK_A3002MU";
const char *pass = "24063173";
//const char *ssid =  "yourSSID";     // change according to your Network - cannot be longer than 32 characters!
//const char *pass =  "yourPASSWORD"; // change according to your Network

#define MQTTid              ""                           //id of this mqtt client
#define MQTTip              "broker.mqtt-dashboard.com"  //ip address or hostname of the mqtt broker
#define MQTTport            1883                         //port of the mqtt broker
#define MQTTuser            "alex9ufo"                   //username of this mqtt client
#define MQTTpsw             "alex1234"                   //password of this mqtt client
//#define MQTTuser          "your_username"              //username of this mqtt client
//#define MQTTpsw           "your_password"              //password of this mqtt client
#define MQTTpubQos          2                            //qos of publish (see README)
#define MQTTsubQos          1                            //qos of subscribe

//Variables
long lastMsg = 0;
char jsonChar[100];
String Json_buf="";
String json="";

bool Flash = false;  //true
bool Timer = false;  //true
int Count= 0;

//=============================================================================
boolean pendingDisconnect = false;
void mqttConnectedCb(); // on connect callback
void mqttDisconnectedCb(); // on disconnect callback
void mqttDataCb(char* topic, byte* payload, unsigned int length); // on new message callback

WiFiClient wclient;
PubSubClient client(MQTTip, MQTTport, mqttDataCb, wclient);
//=============================================================================
void mqttConnectedCb() {
  Serial.println("connected");
 
  // Once connected, publish an announcement...
  client.publish("alex9ufo/outTopic/led/led_event", jsonChar, MQTTpubQos); // true means retain
  // ... and resubscribe
  client.subscribe("alex9ufo/inTopic/led/led_event", MQTTsubQos);
}
//=============================================================================
void mqttDisconnectedCb() {
  Serial.println("disconnected");
}
//=============================================================================
void mqttDataCb(char* topic, byte* payload, unsigned int length) {

  /*
  you can convert payload to a C string appending a null terminator to it;
  this is possible when the message (including protocol overhead) doesn't
  exceeds the MQTT_MAX_PACKET_SIZE defined in the library header.
  you can consider safe to do so when the length of topic plus the length of
  message doesn't exceeds 115 characters
  */
  char* message = (char *) payload;
  message[length] = 0;

  Serial.print("Message arrived [");
  Serial.print(topic);
  Serial.print("] ");
  Serial.println(message);
  String s = message;
  s.trim();

  Json_buf=s;
  // Switch on the LED if an 1 was received as first character
  if (s == "OFF") {
     digitalWrite(BUILTIN_LED, LOW);   // Turn the LED on (Note that LOW is the voltage level
     // but actually the LED is on; this is because
     Serial.println("Received OFF , Send LOW TO BuildIn_LED");
     Flash = false;
     Timer = false;
    }
   if (s == "ON") {
     digitalWrite(BUILTIN_LED, HIGH);   // Turn the LED off (Note that HIGH is the voltage level
     // but actually the LED is on; this is because
     Serial.println("Received ON , Send HIGH TO BuildIn_LED");
     Flash = false;
     Timer = false;
   }
    if (s == "FLASH") {
     digitalWrite(BUILTIN_LED, HIGH);   // Turn the LED off (Note that HIGH is the voltage level
     // but actually the LED is on; this is because
     Serial.println("Received FLASH , Flashing BuildIn_LED ");
     Flash = true;
     Timer = false;
   } //if (message[0] == '2')
   
    if (s == "TIMER") {
     digitalWrite(BUILTIN_LED, HIGH);   // Turn the LED off (Note that HIGH is the voltage level
     // but actually the LED is on; this is because
     Serial.println("Received TIMER ,  BuildIn_LED ON 5 SEC");
     Flash = false;
     Timer = true;
     Count= 10;
   }
    if (s == "TOGGLE") {
     int currState = digitalRead(BUILTIN_LED);
     digitalWrite(BUILTIN_LED, !currState);  // sets the LED to the Toggle value
     // but actually the LED is on; this is because
     Serial.println("Received TOGGLE ,  BuildIn_LED TOGGLE");
     Flash = false;
     Timer = false;
   }  
}
//======================================================
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, pass);

  while (WiFi.status() != WL_CONNECTED) {
    delay(500);
    Serial.print(".");
  }

  Serial.println("");
  Serial.println("WiFi connected");
  Serial.println("IP address: ");
  Serial.println(WiFi.localIP());
 
}
//======================================================
void setup() {
  Serial.begin(115200);
  setup_wifi();
  pinMode(BUILTIN_LED, OUTPUT);
  Serial.println(F("Booting...."));
 }
//======================================================
void process_mqtt() {
  if (WiFi.status() == WL_CONNECTED) {
    if (client.connected()) {
      client.loop();
    } else {
    // client id, client username, client password, last will topic, last will qos, last will retain, last will message
      if (client.connect(MQTTid, MQTTuser, MQTTpsw, MQTTid "/status", 2, true, "0")) {
          pendingDisconnect = false;
          mqttConnectedCb();
      }
    }
  } else {
    if (client.connected())
      client.disconnect();
  }
  if (!client.connected() && !pendingDisconnect) {
    pendingDisconnect = true;
    mqttDisconnectedCb();
  }
}
//======================================================
void loop() {
  process_mqtt();
 
  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);
    }
     
  }

  // Convert data to JSON string
  if (json != Json_buf) {
    json = Json_buf ;
    json.toCharArray(jsonChar, json.length()+1);
 
    if  (client.connected()) {
          Serial.print("Publish message: alex9ufo/outTopic/led/led_event ");
          Serial.println(json);
          // Publish JSON character array to MQTT topic
          client.publish("alex9ufo/outTopic/led/led_event",jsonChar);
        }
  }

}   //Loop
//======================================================

沒有留言:

張貼留言

WOKWI DHT22 & LED , Node-Red + SQLite database

 WOKWI DHT22 & LED , Node-Red + SQLite database Node-Red程式 [{"id":"6f0240353e534bbd","type":"comment&...