Uploading sensor data to ThingSpeak with Node-RED
利用亂數產生 溫度與濕度 並發行至MQTT
從MQTT 訂閱溫度與濕度數據並Show 在Chart節點上 及 發行至ThinkSpeak Channel 上 儲存於SQLite DataBase C:\Users\User\.node-red\Temp_and_Humidity.db
TEMPERATURE TABLE
msg.topic = CREATE TABLE TEMPERATURE(
ID INTEGER PRIMARY KEY AUTOINCREMENT,
TIMESTAMP TEXT , VALUE INT )
HUMIDITY TABLE
msg.topic = CREATE TABLE HUMIDITY(
ID INTEGER PRIMARY KEY AUTOINCREMENT,
TIMESTAMP TEXT , VALUE INT )
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+' '+h+':'+m+':'+s;
var randomNum = msg.payload;
msg.topic = "INSERT INTO TEMPERATURE (TIMESTAMP , VALUE ) VALUES ($var_date , $randomNum ) " ;
msg.payload = [var_date, randomNum ]
return msg;
[{"id":"85b43f335fa700a8","type":"function","z":"16576ce1f4418417","name":"function random","func":"function between(min, max) { \n return Math.floor(\n (Math.random() * (max - min) + min)\n )\n}\nvar min=20;\nvar max=90;\n\nmsg.payload = between(min, max);\nmsg.color=\"red\";\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":360,"y":120,"wires":[["89047e7d460ad319","de285b62e9de7ef5","a1c51aafbf4d017d"]]},{"id":"3d8d25203e3cca07","type":"inject","z":"16576ce1f4418417","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"120","crontab":"","once":true,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":180,"y":120,"wires":[["85b43f335fa700a8"]]},{"id":"ee7a2a72cc3de903","type":"ui_chart","z":"16576ce1f4418417","name":"","group":"44c23a63b5a5e055","order":1,"width":"6","height":"6","label":"訂閱humidity的濕度","chartType":"line","legend":"false","xformat":"HH:mm:ss","interpolate":"linear","nodata":"","dot":false,"ymin":"0","ymax":"99","removeOlder":1,"removeOlderPoints":"","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":1,"useDifferentColor":false,"className":"","x":430,"y":560,"wires":[[]]},{"id":"89047e7d460ad319","type":"mqtt out","z":"16576ce1f4418417","name":"humidity","topic":"alex9ufo/thinkspeak/humidity","qos":"1","retain":"true","respTopic":"","contentType":"","userProps":"","correl":"","expiry":"","broker":"70940176.2b2d3","x":540,"y":100,"wires":[]},{"id":"a3f56c29f9364612","type":"inject","z":"16576ce1f4418417","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"120","crontab":"","once":true,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":180,"y":220,"wires":[["eec0766dce83a895"]]},{"id":"eec0766dce83a895","type":"function","z":"16576ce1f4418417","name":"function random","func":"function between(min, max) { \n return Math.floor(\n (Math.random() * (max - min) + min)\n )\n}\nvar min=15;\nvar max=55;\n\nmsg.payload = between(min, max);\nmsg.color=\"red\";\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":360,"y":220,"wires":[["63e9401ab13a0cb6","8ea516b321a815e2","6a9a1b8e4a783808"]]},{"id":"ef70fad26d41f6c0","type":"mqtt in","z":"16576ce1f4418417","name":"humidity","topic":"alex9ufo/thinkspeak/humidity","qos":"1","datatype":"auto-detect","broker":"70940176.2b2d3","nl":false,"rap":true,"rh":0,"inputs":0,"x":160,"y":520,"wires":[["ee7a2a72cc3de903","623e5a6222a1c0b1","d3dfc2eeb416833f","56794064f36fd93c"]]},{"id":"b08c1ec1f7a5319c","type":"mqtt in","z":"16576ce1f4418417","name":"temperature","topic":"alex9ufo/thinkspeak/temperature","qos":"1","datatype":"auto-detect","broker":"70940176.2b2d3","nl":false,"rap":true,"rh":0,"inputs":0,"x":170,"y":360,"wires":[["ee39943bcd7da84b","376c28ad739ee796","546eb1a30978b1cd","8ec6249da1f0d12d"]]},{"id":"ee39943bcd7da84b","type":"ui_chart","z":"16576ce1f4418417","name":"","group":"21f342b756626a6b","order":2,"width":"6","height":"6","label":"訂閱temperature的溫度","chartType":"line","legend":"false","xformat":"HH:mm:ss","interpolate":"linear","nodata":"","dot":false,"ymin":"15","ymax":"55","removeOlder":1,"removeOlderPoints":"","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":1,"useDifferentColor":false,"className":"","x":450,"y":320,"wires":[[]]},{"id":"de285b62e9de7ef5","type":"ui_text","z":"16576ce1f4418417","group":"6321a2b9a7a07dbe","order":3,"width":"6","height":"3","name":"","label":"dumidity發行的溼度","format":"<font color= {{msg.color}} > {{msg.payload}} </font>","layout":"row-left","className":"","x":570,"y":60,"wires":[]},{"id":"63e9401ab13a0cb6","type":"ui_text","z":"16576ce1f4418417","group":"6321a2b9a7a07dbe","order":4,"width":"6","height":"3","name":"","label":"temperature發行的溫度","format":"<font color= {{msg.color}} > {{msg.payload}} </font>","layout":"row-left","className":"","x":590,"y":220,"wires":[]},{"id":"69d20429e68c51dd","type":"comment","z":"16576ce1f4418417","name":"發行 room/humidity","info":"","x":190,"y":80,"wires":[]},{"id":"2a9dc05e9e5e4309","type":"comment","z":"16576ce1f4418417","name":"發行 room/temperature","info":"","x":200,"y":180,"wires":[]},{"id":"59863940bf9c6959","type":"comment","z":"16576ce1f4418417","name":"訂閱 alex9ufo/thinkspeakroom/temperature","info":"","x":200,"y":640,"wires":[]},{"id":"8466e2229899dfe2","type":"comment","z":"16576ce1f4418417","name":"訂閱 alex9ufo/thinkspeak/humidity","info":"","x":180,"y":680,"wires":[]},{"id":"376c28ad739ee796","type":"ThingspeakSendSimple","z":"16576ce1f4418417","name":"Send to Thinkspeak 溫度","writekey":"8U1H2OD8L4P9L718","fieldid":"1","x":450,"y":360,"wires":[]},{"id":"623e5a6222a1c0b1","type":"ThingspeakSendSimple","z":"16576ce1f4418417","name":"Send to Thinkspeak 濕度","writekey":"8U1H2OD8L4P9L718","fieldid":"2","x":450,"y":520,"wires":[]},{"id":"8ea516b321a815e2","type":"mqtt out","z":"16576ce1f4418417","name":"temperature","topic":"alex9ufo/thinkspeak/temperature","qos":"1","retain":"true","respTopic":"","contentType":"","userProps":"","correl":"","expiry":"","broker":"70940176.2b2d3","x":550,"y":260,"wires":[]},{"id":"546eb1a30978b1cd","type":"function","z":"16576ce1f4418417","name":"function INSERT Temperature ","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(); //秒\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+' '+h+':'+m+':'+s;\nvar randomNum = msg.payload;\n\nmsg.topic = \"INSERT INTO TEMPERATURE (TIMESTAMP , VALUE ) VALUES ($var_date , $randomNum ) \" ;\nmsg.payload = [var_date, randomNum ]\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":470,"y":400,"wires":[["666106e4be9de7e1"]]},{"id":"666106e4be9de7e1","type":"sqlite","z":"16576ce1f4418417","mydb":"987979dcb5a4d733","sqlquery":"msg.topic","sql":"","name":"Temp_and_Humidity","x":820,"y":440,"wires":[["e7d968de1837f23d"]]},{"id":"e7d968de1837f23d","type":"debug","z":"16576ce1f4418417","name":"debug ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1010,"y":440,"wires":[]},{"id":"d3dfc2eeb416833f","type":"function","z":"16576ce1f4418417","name":"function INSERT HUMIDITY","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(); //秒\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+' '+h+':'+m+':'+s;\nvar randomNum = msg.payload;\n\nmsg.topic = \"INSERT INTO HUMIDITY (TIMESTAMP , VALUE ) VALUES ($var_date , $randomNum ) \" ;\nmsg.payload = [var_date, randomNum ]\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":460,"y":480,"wires":[["666106e4be9de7e1"]]},{"id":"b23e6980cd8ae0ee","type":"inject","z":"16576ce1f4418417","name":"Create Humidity Table","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"CREATE TABLE HUMIDITY( ID INTEGER PRIMARY KEY AUTOINCREMENT, TIMESTAMP TEXT , VALUE INT )","payload":"","payloadType":"date","x":520,"y":680,"wires":[["666106e4be9de7e1","c072af6adfbcc426"]]},{"id":"a399984def27c6cf","type":"inject","z":"16576ce1f4418417","name":"Create Temperature Table","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"CREATE TABLE TEMPERATURE( ID INTEGER PRIMARY KEY AUTOINCREMENT, TIMESTAMP TEXT , VALUE INT )","payload":"","payloadType":"date","x":530,"y":640,"wires":[["666106e4be9de7e1","c072af6adfbcc426"]]},{"id":"1619047eda217c3e","type":"comment","z":"16576ce1f4418417","name":"TEMPERATURE TABLE","info":"msg.topic = CREATE TABLE TEMPERATURE( \nID INTEGER PRIMARY KEY AUTOINCREMENT, \nTIMESTAMP TEXT , VALUE INT )","x":830,"y":400,"wires":[]},{"id":"295bc734a38d31b8","type":"comment","z":"16576ce1f4418417","name":"HUMIDITY TABLE","info":"msg.topic = CREATE TABLE HUMIDITY( \nID INTEGER PRIMARY KEY AUTOINCREMENT, \nTIMESTAMP TEXT , VALUE INT )","x":810,"y":500,"wires":[]},{"id":"c072af6adfbcc426","type":"debug","z":"16576ce1f4418417","name":"debug topic","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"topic","targetType":"msg","statusVal":"","statusType":"auto","x":770,"y":620,"wires":[]},{"id":"a1c51aafbf4d017d","type":"debug","z":"16576ce1f4418417","name":"debug 236","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":690,"y":140,"wires":[]},{"id":"8ec6249da1f0d12d","type":"debug","z":"16576ce1f4418417","name":"debug 237","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":410,"y":440,"wires":[]},{"id":"6a9a1b8e4a783808","type":"debug","z":"16576ce1f4418417","name":"debug 238","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":690,"y":180,"wires":[]},{"id":"56794064f36fd93c","type":"debug","z":"16576ce1f4418417","name":"debug 239","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":410,"y":600,"wires":[]},{"id":"44c23a63b5a5e055","type":"ui_group","name":"MQTT HUMIDITY","tab":"564e3c93eeb0489d","order":3,"disp":true,"width":"6","collapse":false,"className":""},{"id":"70940176.2b2d3","type":"mqtt-broker","name":"","broker":"broker.mqtt-dashboard.com","port":"1883","clientid":"","autoConnect":true,"usetls":false,"protocolVersion":4,"keepalive":"15","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closePayload":"","willTopic":"","willQos":"0","willPayload":""},{"id":"21f342b756626a6b","type":"ui_group","name":"MQTT TEMPERATURE","tab":"564e3c93eeb0489d","order":2,"disp":true,"width":"6","collapse":false,"className":""},{"id":"6321a2b9a7a07dbe","type":"ui_group","name":"MQTT out","tab":"564e3c93eeb0489d","order":1,"disp":true,"width":"6","collapse":false,"className":""},{"id":"987979dcb5a4d733","type":"sqlitedb","db":"C:\\Users\\User\\.node-red\\Temp_and_Humidity.db","mode":"RWC"},{"id":"564e3c93eeb0489d","type":"ui_tab","name":"ThinkSpeak","icon":"dashboard","order":93,"disabled":false,"hidden":false}]
沒有留言:
張貼留言