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}]
沒有留言:
張貼留言