alex9ufo 聰明人求知心切: 6月 2023

2023年6月27日星期二

ESP32 MQTT傳送DHT11溫溼度

ESP32 AutoConnected to WIFI

#include <WiFiManager.h>         // https://github.com/tzapu/WiFiManager
#include <PubSubClient.h>
//=========================================

// REQUIRES the following Arduino libraries:
// - DHT Sensor Library: https://github.com/adafruit/DHT-sensor-library
// - Adafruit Unified Sensor Lib: https://github.com/adafruit/Adafruit_Sensor

#include <Adafruit_Sensor.h>
#include <DHT.h>
#include <DHT_U.h>

#define DHTPIN 27     // Digital pin connected to the DHT sensor 
// Feather HUZZAH ESP8266 note: use pins 3, 4, 5, 12, 13 or 14 --
// Pin 15 can work but DHT must be disconnected during program upload.

// Uncomment the type of sensor in use:
//#define DHTTYPE    DHT11     // DHT 11
#define DHTTYPE    DHT22     // DHT 22 (AM2302)
//#define DHTTYPE    DHT21     // DHT 21 (AM2301)

// See guide for details on sensor wiring and usage:
//   https://learn.adafruit.com/dht/overview

//=============================================================================

#define BUILTIN_LED 2
// Update these with values suitable for 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;
 
String json = "";    
char jsonChar1[100];  //client.publish("alex9ufo/Esp32/temperature"
char jsonChar2[100];  //client.publish("alex9ufo/Esp32/humidity"
char jsonChar3[100];  //client.publish("alex9ufo/Esp32/output_status",

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

DHT_Unified dht(DHTPIN, DHTTYPE);
uint32_t delayMS;

//=============================================================================
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/Esp32/temperature", jsonChar1, MQTTpubQos); // true means retain
  client.publish("alex9ufo/Esp32/humidity", jsonChar2, MQTTpubQos); // true means retain 
  // Once connected, publish an announcement...
  client.publish("alex9ufo/Esp32/output_status", jsonChar3, MQTTpubQos); // true means retain
  // ... and resubscribe
  client.subscribe("alex9ufo/Esp32/output", 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();
  // 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;
     json ="OFF";
     Send = true ;
    } // if (s == "OFF")
    
   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; 
     json ="ON";
     Send = true ;
   } //if (s == "ON")
   
   if (s == "TOGGLE") {
     digitalWrite(BUILTIN_LED, !digitalRead(BUILTIN_LED));   // Turn the LED toggle 
     // but actually the LED is on; this is because
     Serial.println("Received TOGGLE , Send Toggle(H->L , L->H) TO BuildIn_LED");
     Flash = false; 
     Timer = false; 
     json ="TOGGLE";
     Send = true ;     
   } //if (s == "TOGGLE") 
   
   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;
     json ="FLASH";
     Send = true ;  
    } //if (message[0] == 'FLASH')
   
    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;
     json ="TIMER";
     Send = true ; 
    } //if (message[0] == 'TIMER')
   
}
//======================================================
void autoconnect() {
    // WiFi.mode(WIFI_STA); // explicitly set mode, esp defaults to STA+AP
    // it is a good practice to make sure your code sets wifi mode how you want it.

    // put your setup code here, to run once:
    Serial.begin(115200);
    pinMode(BUILTIN_LED, OUTPUT);

    delay(1000);

    // We start by connecting to a WiFi network

    Serial.println();
    Serial.println();
    Serial.print("Connecting to "); 
    //WiFiManager, Local intialization. Once its business is done, there is no need to keep it around
    WiFiManager wm;

    // reset settings - wipe stored credentials for testing
    // these are stored by the esp library
    // wm.resetSettings();

    // Automatically connect using saved credentials,
    // if connection fails, it starts an access point with the specified name ( "AutoConnectAP"),
    // if empty will auto generate SSID, if password is blank it will be anonymous AP (wm.autoConnect())
    // then goes into a blocking loop awaiting configuration and will return success result

    bool res;
    // res = wm.autoConnect(); // auto generated AP name from chipid
    // res = wm.autoConnect("AutoConnectAP"); // anonymous ap
    res = wm.autoConnect("AutoConnectAP","password"); // password protected ap

    if(!res) {
        Serial.println("Failed to connect");
        // ESP.restart();
        res = wm.autoConnect("AutoConnectAP","password"); // password protected ap
    } 
    else {
        //if you get here you have connected to the WiFi    
        Serial.println("connected...yeey :)");
    }

}

//======================================================
void setup() {
  autoconnect();
  // if you get here you have connected to the WiFi
  Serial.println("Connected.");

  dht.begin();
  Serial.println(F("DHTxx Unified Sensor Example"));
  // Print temperature sensor details.
  sensor_t sensor;
  dht.temperature().getSensor(&sensor);
  Serial.println(F("------------------------------------"));
  Serial.println(F("Temperature Sensor"));
  Serial.print  (F("Sensor Type: ")); Serial.println(sensor.name);
  Serial.print  (F("Driver Ver:  ")); Serial.println(sensor.version);
  Serial.print  (F("Unique ID:   ")); Serial.println(sensor.sensor_id);
  Serial.print  (F("Max Value:   ")); Serial.print(sensor.max_value); Serial.println(F("°C"));
  Serial.print  (F("Min Value:   ")); Serial.print(sensor.min_value); Serial.println(F("°C"));
  Serial.print  (F("Resolution:  ")); Serial.print(sensor.resolution); Serial.println(F("°C"));
  Serial.println(F("------------------------------------"));
  // Print humidity sensor details.
  dht.humidity().getSensor(&sensor);
  Serial.println(F("Humidity Sensor"));
  Serial.print  (F("Sensor Type: ")); Serial.println(sensor.name);
  Serial.print  (F("Driver Ver:  ")); Serial.println(sensor.version);
  Serial.print  (F("Unique ID:   ")); Serial.println(sensor.sensor_id);
  Serial.print  (F("Max Value:   ")); Serial.print(sensor.max_value); Serial.println(F("%"));
  Serial.print  (F("Min Value:   ")); Serial.print(sensor.min_value); Serial.println(F("%"));
  Serial.print  (F("Resolution:  ")); Serial.print(sensor.resolution); Serial.println(F("%"));
  Serial.println(F("------------------------------------"));
  // Set delay between sensor readings based on sensor details.
  delayMS = sensor.min_delay / 1000;


}
//======================================================
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();
  long now = millis();
  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(jsonChar3, json.length()+1);
     if  (client.connected()) {
              Serial.print("Publish message: ");
              Serial.println(json);
              // Publish JSON character array to MQTT topic
             client.publish("alex9ufo/Esp32/output_status",jsonChar3);
         } 
     Send = false;    
   }
  
  if (WiFi.status() == WL_CONNECTED) { 
    //========Auto Connect===============================
     if (now - lastMsg > 10000) {  //等10秒
        lastMsg = now; 
        dht.begin();
        // Delay between measurements.
        delay(delayMS);
        // Get temperature event and print its value.
        sensors_event_t event;
        dht.temperature().getEvent(&event);
        if (isnan(event.temperature)) {
            Serial.println(F("Error reading temperature!"));
        }
        else {
          Serial.print(F("Temperature: "));
          Serial.print(event.temperature);
          Serial.println(F("°C"));

          String json1 =String(event.temperature);
          // Convert JSON string to character array
          json1.toCharArray(jsonChar1, json1.length()+1);
    
          if  (client.connected()) {
              Serial.print("Publish message--> Temperature: ");
              Serial.println(json1);
              // Publish JSON character array to MQTT topic
             client.publish("alex9ufo/Esp32/temperature",jsonChar1);
        } 

        } // (isnan(event.temperature))


        // Get humidity event and print its value.
        dht.humidity().getEvent(&event);
        if (isnan(event.relative_humidity)) {
          Serial.println(F("Error reading humidity!"));
        }
        else {
          Serial.print(F("Humidity: "));
          Serial.print(event.relative_humidity);
          Serial.println(F("%"));
          String json1 =String(event.relative_humidity);
          // Convert JSON string to character array
          json1.toCharArray(jsonChar2, json1.length()+1);
    
          if  (client.connected()) {
              Serial.print("Publish message--> Humidity: ");
              Serial.println(json1);
              // Publish JSON character array to MQTT topic
             client.publish("alex9ufo/Esp32/humidity",jsonChar2);
        }  
        }


      } // if (now - lastMsg > 5000))
    } //========Auto Connect===============================
  //Portal.handleClient();
}   //Loop
//=========================================================

2023年6月25日星期日

Using SQLite with Node-RED on Windows (3)

Recommended Blogs

from ==> https://node-red.blogspot.com/2016/02/recommended-blog.html

I want to show in this post people that are doing great work in this field of IOT and red node.

What they do is inspiring and helpful. I admire those guys for their level of knowledge, hard work, smart ideas and for sharing what they know and what they do.

Andreas Spiess- YouTube Channel (click here)

Following this channel is a must for those who like or work in IOT field. The "guy with Swiss accent" offers insightful videos in a very didactic way.

Primal Cortex´s Weblog (click here)

What amazing blog: extremely well written and easy to understand. Many posts on Node-RED, Odroid, Linux, ESP8266 and their firmware (Sming and node MCU), MQTT and much, much more.

Scargill's Tech Blog (click here)

Another master in the field. His blog is plentiful of information on Node-RED, raspberry, home automation, ESP8266, hardware review, IOT and much more stuff that makers love to do. Peter has also a channel on Youtube (click here).

Node-Red Programming Guide (click here)

By the time of this writing, this is the best tutorial on the web. This is a work in progress, containing seven lectures and other two under development. The tutorial is offered by Sense Tecnic, the creators of FRED environment (click here). I use the lecture to practice in the FRED environment (click here).

2023年6月24日星期六

Using SQLite with Node-RED on Windows (2)

Using SQLite with Node-RED on Windows (2)

(1) Create DataBase

C:\Users\User\.node-red\2023sqlite_db002.db

(Read-Write-Create)

SQL Query :　Fixed statement

CREATE TABLE

dhtreadings

(id INTEGER PRIMARY KEY AUTOINCREMENT,

temperature NUMERIC,

humidity NUMERIC,

currentdate DATE,

currenttime TIME,

device TEXT)

利用 DB Browser檢視 DataBase

(2) INSERT 二種方法

產生2各亂數模擬溫度與濕度

var temp1=msg.payload;

flow.set("tempC", temp1);

儲存亂數1為tempC

var max=100;

var min= 20;

var humi1 = Math.round(Math.random() * (max - min) + min);

產生亂數2

flow. Set("humiD", humi1);

儲存亂數2為humiD

return msg;

產生欄位

id INTEGER PRIMARY KEY AUTOINCREMENT,

temperature NUMERIC,

humidity NUMERIC,

currentdate DATE,

currenttime TIME,

device TEXT)

五個欄位的資料 (id 會自行產生並update)

// Create a Params variable

// with a time and value component

var myTemp = flow.get("tempC");

var myHumi = flow.get("humiD");

var Today = new Date();

var yyyy = Today.getFullYear(); //年

var MM = Today.getMonth()+1; //月

var dd = Today.getDate(); //日

var h = Today.getHours(); //時

var m = Today.getMinutes(); //分

var s = Today.getSeconds(); //秒

if(MM<10)

{

MM = '0'+MM;

}

if(dd<10)

{

dd = '0'+dd;

}

if(h<10)

{

h = '0'+h;

}

if(m<10)

{

m = '0' + m;

}

if(s<10)

{

s = '0' + s;

}

var var_date= yyyy + '/'+ MM + '/'+ dd ;

var var_time = h + ':' + m + ':' + s ;

var max= 40;

var min= 20;

var var_device = 'DEVICE'+ Math.round(Math.random() * (max - min) + min);

msg.params = { $thetempc: myTemp ,$thehumi: myHumi , $thedate: var_date, $thetime: var_time, $thedevice : var_device }

return msg;

C:\Users\User\.node-red\2023sqlite_db002.db

(Read-Write-Create)

SQL Query :　Prepared statement

insert into dhtreadings (temperature, humidity ,currentdate , currenttime , device) values ($thetempc , $thehumi , $thedate , $thetime , $thedevice)

欄位變數1 變數2

temperature	$thetempc	myTemp
humidity	$thehumi	myHumi
currentdate	$thedate	var_date
currenttime	$thetime,	var_time
device	$thedevice	var_device

===============================================================

另一方法

使用msg.topic的方法

msg.topic = "INSERT INTO dhtreadings ( temperature , humidity , currentdate, currenttime ,device ) VALUES ($myTemp, $myHumi, $var_date , $var_time , $var_device) " ;

msg.payload = [myTemp, myHumi, var_date , var_time , var_device]

// Create a Params variable

// with a time and value component

var myTemp = flow.get("tempC");

var myHumi = flow.get("humiD");

var Today = new Date();

var yyyy = Today.getFullYear(); //年

var MM = Today.getMonth()+1; //月

var dd = Today.getDate(); //日

var h = Today.getHours(); //時

var m = Today.getMinutes(); //分

var s = Today.getSeconds(); //秒

if(MM<10)

{

MM = '0'+MM;

}

if(dd<10)

{

dd = '0'+dd;

}

if(h<10)

{

h = '0'+h;

}

if(m<10)

{

m = '0' + m;

}

if(s<10)

{

s = '0' + s;

}

var var_date= yyyy + '/'+ MM + '/'+ dd ;

var var_time = h + ';' + m + ';' + s ;

var max= 40;

var min= 20;

var var_device = 'DEVICE'+ Math.round(Math.random() * (max - min) + min);

//insert into dhtreadings

//(temperature, humidity ,currentdate , currenttime , device)

//msg.topic = 'INSERT INTO sensors (id, name, value, unit, timestamp) VALUES (null, $val1, $val2, $val3,datetime("now"));'

//msg.payload = ['SNSOR_3',23,'KW']

//return msg;

msg.topic = "INSERT INTO dhtreadings ( temperature , humidity , currentdate, currenttime ,device ) VALUES ($myTemp, $myHumi, $var_date , $var_time , $var_device) " ;

msg.payload = [myTemp, myHumi, var_date , var_time , var_device]

return msg;

[{"id":"eefd2e0d.1afec","type":"comment","z":"79f346c61c3c7374","name":"sqlite.org","info":"https://fred.sensetecnic.com/\nhttp://noderedguide.com/tutorial-sqlite-and-node-red/\nhttps://www.sqlite.org/lang.html\nhttps://www.sqlite.org/datatype3.html#expraff\n\nCREATE TABLE RANDOMNUM( TIMESTAMP INT PRIMARY KEY NOT NULL, VALUE INT NOT NULL, BOOL INT NOT NULL)\n\n//INSERT\nvar randomNum = Math.round(Math.random()*100);\nvar largeBool = (randomNum > 50)?1:0;\nvar newMsg = {\n \"topic\": \"INSERT INTO RANDOMNUM VALUES ( \" + msg.payload + \", \" + randomNum + \", \" + largeBool + \")\"\n}\nreturn newMsg;\n\nDELETE FROM RANDOMNUM WHERE TIMESTAMP <= strftime('%s','now', '-30 seconds')*1000\nDELETE FROM RANDOMNUM WHERE TIMESTAMP <= strftime('%s','now', '-24 hours')*1000\nDELETE FROM RANDOMNUM WHERE TIMESTAMP <= strftime('%s','now', '-7 days')*1000\n\nSELECT COUNT(*) FROM RANDOMNUM\n\nUPDATE RANDOMNUM SET BOOL = 1 WHERE VALUE > 80 AND BOOL = 0\n\nSELECT * FROM RANDOMNUM ORDER BY TIMESTAMP DESC LIMIT 100;\n<table style=\"width:100%\">\n <tr>\n <th>Index</th> \n <th>Timestamp</th>\n <th>Value</th> \n <th>Bool</th>\n </tr>\n <tr ng-repeat=\"x in msg.payload | limitTo:20\">\n <td>{{$index}}</td>\n <td>{{msg.payload[$index].TIMESTAMP}}</td>\n <td>{{msg.payload[$index].VALUE}}</td> \n <td>{{msg.payload[$index].BOOL}}</td>\n </tr>\n</table>\n\n","x":100,"y":20,"wires":[]},{"id":"ce813a38.486e28","type":"sqlite","z":"79f346c61c3c7374","mydb":"3cc2697f190f3dee","sqlquery":"fixed","sql":"CREATE TABLE dhtreadings(id INTEGER PRIMARY KEY AUTOINCREMENT, temperature NUMERIC, humidity NUMERIC, currentdate DATE, currenttime TIME, device TEXT)","name":"sqlite_db002","x":290,"y":60,"wires":[["4f3f3858.c6ea58"]]},{"id":"adb5271b.e49118","type":"inject","z":"79f346c61c3c7374","name":"CREATE DB","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":130,"y":60,"wires":[["ce813a38.486e28"]]},{"id":"4f3f3858.c6ea58","type":"debug","z":"79f346c61c3c7374","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":470,"y":60,"wires":[]},{"id":"885a944d.7a7838","type":"debug","z":"79f346c61c3c7374","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":730,"y":240,"wires":[]},{"id":"1edfd08.fcc9c3","type":"sqlite","z":"79f346c61c3c7374","mydb":"3cc2697f190f3dee","sqlquery":"msg.topic","sql":"","name":"sqlite_db002","x":370,"y":360,"wires":[["5b075b30.5b3014","852639d72678c06b"]]},{"id":"5b075b30.5b3014","type":"debug","z":"79f346c61c3c7374","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":570,"y":360,"wires":[]},{"id":"1a6eb281.ee04dd","type":"inject","z":"79f346c61c3c7374","name":"COUNT","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"SELECT COUNT(*) FROM dhtreadings","payload":"","payloadType":"date","x":110,"y":360,"wires":[["1edfd08.fcc9c3"]]},{"id":"4667ba0e.d67e64","type":"inject","z":"79f346c61c3c7374","name":"SELECT","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"SELECT * FROM dhtreadings ORDER BY id DESC LIMIT 50;","payload":"","payloadType":"date","x":120,"y":460,"wires":[["9e2dbd84.ab087"]]},{"id":"9e2dbd84.ab087","type":"sqlite","z":"79f346c61c3c7374","mydb":"3cc2697f190f3dee","sqlquery":"msg.topic","sql":"","name":"sqlite_db002","x":410,"y":460,"wires":[["df51560e.5729a8","95eec7a9.8e2a88"]]},{"id":"df51560e.5729a8","type":"ui_template","z":"79f346c61c3c7374","group":"d40ead3e.334ab","name":"","order":0,"width":"12","height":"10","format":"<table style=\"width:100%\">\n <tr>\n <th>id</th> \n <th>Temperature</th>\n <th>Humidity</th>\n <th>Date</th>\n <th>Times</th>\n <th>DEVICE</th> \n </tr>\n \n <tr ng-repeat=\"x in msg.payload | limitTo:50\">\n <td>{{$index}}</td>\n <td>{{msg.payload[$index].temperature}}</td>\n <td>{{msg.payload[$index].humidity}}</td>\n <td>{{msg.payload[$index].currentdate}}</td>\n <td>{{msg.payload[$index].currenttime}}</td>\n <td>{{msg.payload[$index].device}}</td> \n </tr>\n</table>","storeOutMessages":true,"fwdInMessages":true,"resendOnRefresh":true,"templateScope":"local","className":"","x":620,"y":460,"wires":[[]]},{"id":"95eec7a9.8e2a88","type":"debug","z":"79f346c61c3c7374","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":630,"y":500,"wires":[]},{"id":"a8f6a55b28067bff","type":"inject","z":"79f346c61c3c7374","name":"INSERT 1","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":120,"y":120,"wires":[["98abccc1dd83a597"]]},{"id":"98abccc1dd83a597","type":"random","z":"79f346c61c3c7374","name":"","low":"15","high":"45","inte":"true","property":"payload","x":260,"y":120,"wires":[["27e31fd9170a9eeb"]]},{"id":"ebb3db753f0f5365","type":"function","z":"79f346c61c3c7374","name":"msg.params","func":"// Create a Params variable\n// with a time and value component\n//\nvar myTemp = flow.get(\"tempC\");\nvar myHumi = flow.get(\"humiD\");\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 var_date= yyyy + '/'+ MM + '/'+ dd ;\nvar var_time = h + ':' + m + ':' + s ;\nvar max= 40;\nvar min= 20;\nvar var_device = 'DEVICE'+ Math.round(Math.random() * (max - min) + min);\n\n\nmsg.params = { $thetempc: myTemp ,$thehumi: myHumi , $thedate: var_date, $thetime: var_time, $thedevice : var_device }\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":370,"y":180,"wires":[["1b7940afb0eb5b8b"]]},{"id":"1b7940afb0eb5b8b","type":"sqlite","z":"79f346c61c3c7374","mydb":"3cc2697f190f3dee","sqlquery":"prepared","sql":"\ninsert into dhtreadings (temperature, humidity ,currentdate , currenttime , device) values ($thetempc , $thehumi , $thedate , $thetime , $thedevice)","name":"INSERT INTO SQLITE","x":580,"y":180,"wires":[["466c109ae7e9d1b9"]]},{"id":"466c109ae7e9d1b9","type":"debug","z":"79f346c61c3c7374","name":"debug ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":770,"y":180,"wires":[]},{"id":"cf3063fb14da9cda","type":"comment","z":"79f346c61c3c7374","name":"INSERT ","info":"\n//msg.params = { \n//$thetempc: msg.payload.rand1 \n//,$thehumi: msg.payload.rand2 , \n//$thedate: var_date, \n//$thetime: var_time,\n//$thedevice :'DEVICE1'\n\n//CREATE TABLE dhtreadings(id INTEGER PRIMARY KEY AUTOINCREMENT,\n//temperature NUMERIC, \n//humidity NUMERIC, \n//currentdate DATE, \n//currenttime TIME, \n//device TEXT)","x":720,"y":140,"wires":[]},{"id":"27e31fd9170a9eeb","type":"function","z":"79f346c61c3c7374","name":"Store random 變數","func":"var temp1=msg.payload;\nflow.set(\"tempC\", temp1);\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\nvar max=100;\nvar min= 20;\n//var humi1 = Math.round(Math.random() * (max - min) + min);\nvar humi1 = Math.floor(Math.random()*s);\n\n\nflow.set(\"humiD\", humi1);\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":450,"y":120,"wires":[["ebb3db753f0f5365"]]},{"id":"784f99f25282d04e","type":"sqlite","z":"79f346c61c3c7374","mydb":"3cc2697f190f3dee","sqlquery":"msg.topic","sql":"SELECT * FROM sensors","name":"sqlite_db002","x":550,"y":240,"wires":[["885a944d.7a7838"]]},{"id":"cb106cd4d88195ca","type":"inject","z":"79f346c61c3c7374","name":"INSERT 2","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":120,"y":240,"wires":[["4ea45ad8c602047f"]]},{"id":"4ea45ad8c602047f","type":"function","z":"79f346c61c3c7374","name":"INSERT","func":"// Create a Params variable\n// with a time and value component\n//\n//var myTemp = flow.get(\"tempC\");\n//var myHumi = flow.get(\"humiD\");\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 var_date= yyyy + '/'+ MM + '/'+ dd ;\nvar var_time = h + ';' + m + ';' + s ;\nvar max= 40;\nvar min= 20;\nvar var_device = 'DEVICE'+ Math.round(Math.random() * (max - min) + min);\n\n//insert into dhtreadings \n//(temperature, humidity ,currentdate , currenttime , device) \n\n//msg.topic = 'INSERT INTO sensors (id, name, value, unit, timestamp) VALUES (null, $val1, $val2, $val3,datetime(\"now\"));'\n//msg.payload = ['SNSOR_3',23,'KW']\n//return msg;\nvar myTemp = Math.floor(Math.random()*s);\nvar max=100;\nvar min= 20;\nvar myHumi = Math.round(Math.random() * (max - min) + min);\n\nmsg.topic = \"INSERT INTO dhtreadings ( temperature , humidity , currentdate, currenttime ,device ) VALUES ($myTemp, $myHumi, $var_date , $var_time , $var_device) \" ;\nmsg.payload = [myTemp, myHumi, var_date , var_time , var_device]\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":320,"y":240,"wires":[["784f99f25282d04e"]]},{"id":"780629e1b61b481d","type":"ui_text","z":"79f346c61c3c7374","group":"d40ead3e.334ab","order":1,"width":0,"height":0,"name":"","label":"資料庫目前筆數:","format":"{{msg.payload}}","layout":"row-left","className":"","x":740,"y":320,"wires":[]},{"id":"852639d72678c06b","type":"function","z":"79f346c61c3c7374","name":"function 25","func":"msg.payload=msg.payload[0];\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":570,"y":320,"wires":[["780629e1b61b481d"]]},{"id":"3cc2697f190f3dee","type":"sqlitedb","db":"C:\\Users\\User\\.node-red\\2023sqlite_db002.db","mode":"RWC"},{"id":"d40ead3e.334ab","type":"ui_group","name":"Group 1","tab":"3c770aff.9ba2a6","order":1,"disp":true,"width":"12","collapse":false,"className":""},{"id":"3c770aff.9ba2a6","type":"ui_tab","name":"Main Tab","icon":"dashboard","order":1}]

訂閱：文章 (Atom)

2023年6月27日 星期二

ESP32 MQTT傳送DHT11溫溼度

2023年6月25日 星期日

Using SQLite with Node-RED on Windows (3)

Recommended Blogs

2023年6月24日 星期六

Using SQLite with Node-RED on Windows (2)

2024年4月24日 星期三 Node-Red Dashboard UI Template + AngularJS 參考 AngularJS教學 --2

2023年6月27日星期二

2023年6月25日星期日

2023年6月24日星期六

2024年4月24日星期三 Node-Red Dashboard UI Template + AngularJS 參考 AngularJS教學 --2