RFID 實驗2-1 Node-RED , MQTT , ESP32 LED (更新)

RFID 實驗2-1 Node-RED , MQTT , ESP32 LED  (更新)

1) Create TABLE LED 產生資料表

2) 檢視資料庫MQTT_LED.db 的內容

3) 說明載點

http://www.mediafire.com/file/sonwnhm3l32obvg/%25E5%25AF%25A6%25E9%25A9%25972-1-update.pdf/file

4) Node-Red程式載點

http://www.mediafire.com/file/c7mvefgz4pqa0rh/flows_%25E5%25AF%25A6%25E9%25A9%25972-1_update.json/file 

5.  原來 RFID 實驗2-1 Node-RED , MQTT , ESP32 LED 

http://alex9ufoexploer.blogspot.com/2020/10/rfid-2-1-node-red-mqtt-esp32-led.html 

6. 修改成auto connect WiFI 的Arduino程式 載點

http://www.mediafire.com/file/mblc033eux6h94l/AutoConnectWiFi_ESP32_RFID_MQTT_NODE_RED_LED.ino/file

 [{"id":"c678fdd3.80d2e","type":"mqtt in","z":"1b8c50a4.b4ffdf","name":"","topic":"alex9ufo/led/led_status","qos":"1","datatype":"auto","broker":"841df58d.ee5e98","x":120,"y":80,"wires":[["40a66665.ea45b8","15c7548a.5a9ccb","a107873c.21d928"]]},{"id":"4638d138.51ec4","type":"ui_text","z":"1b8c50a4.b4ffdf","group":"ac0f1141.eb50e","order":0,"width":0,"height":0,"name":"","label":"ESP32發行到MQTT的資料","format":"{{msg.payload}}","layout":"col-center","x":520,"y":120,"wires":[]},{"id":"e89774d1.c59888","type":"ui_button","z":"1b8c50a4.b4ffdf","name":"","group":"ac0f1141.eb50e","order":0,"width":"4","height":"2","passthru":false,"label":"LED 開","tooltip":"","color":"white","bgcolor":"","icon":"fa-circle","payload":"ON","payloadType":"str","topic":"","x":80,"y":320,"wires":[["ed3e5d35.5b8e7","b77aa111.18563","b782b30b.315da"]]},{"id":"9f0d7463.a60058","type":"ui_button","z":"1b8c50a4.b4ffdf","name":"","group":"ac0f1141.eb50e","order":0,"width":"4","height":"2","passthru":false,"label":"LED 關","tooltip":"","color":"black","bgcolor":"","icon":"fa-circle-o","payload":"OFF","payloadType":"str","topic":"","x":80,"y":360,"wires":[["ed3e5d35.5b8e7","b77aa111.18563","b782b30b.315da"]]},{"id":"ed3e5d35.5b8e7","type":"mqtt out","z":"1b8c50a4.b4ffdf","name":"","topic":"alex9ufo/inTopic/led/led_event","qos":"1","retain":"true","broker":"841df58d.ee5e98","x":390,"y":420,"wires":[]},{"id":"40a66665.ea45b8","type":"function","z":"1b8c50a4.b4ffdf","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":[["bc155223.5e6a"]]},{"id":"bc155223.5e6a","type":"function","z":"1b8c50a4.b4ffdf","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":[["b92a5ef.384d0a"]]},{"id":"b92a5ef.384d0a","type":"http request","z":"1b8c50a4.b4ffdf","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":[["5aedebdb.79a1b4"]]},{"id":"5aedebdb.79a1b4","type":"debug","z":"1b8c50a4.b4ffdf","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":450,"y":260,"wires":[]},{"id":"15c7548a.5a9ccb","type":"debug","z":"1b8c50a4.b4ffdf","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":370,"y":80,"wires":[]},{"id":"f472b776.bbeca8","type":"ui_audio","z":"1b8c50a4.b4ffdf","name":"","group":"ac0f1141.eb50e","voice":"zh-TW","always":true,"x":460,"y":160,"wires":[]},{"id":"c8945e52.7b063","type":"ui_button","z":"1b8c50a4.b4ffdf","name":"","group":"ac0f1141.eb50e","order":0,"width":"4","height":"2","passthru":false,"label":"LED 開關反向","tooltip":"","color":"blue","bgcolor":"","icon":"fa-circle-o","payload":"TOGGLE","payloadType":"str","topic":"","x":100,"y":400,"wires":[["ed3e5d35.5b8e7","b77aa111.18563","b782b30b.315da"]]},{"id":"a107873c.21d928","type":"function","z":"1b8c50a4.b4ffdf","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":"","x":290,"y":160,"wires":[["f472b776.bbeca8","4638d138.51ec4","ee69d0c7.eac63"]]},{"id":"52e38277.8dfc6c","type":"ui_button","z":"1b8c50a4.b4ffdf","name":"","group":"ac0f1141.eb50e","order":0,"width":"4","height":"2","passthru":false,"label":"LED 開5秒鐘","tooltip":"","color":"purple","bgcolor":"","icon":"fa-circle-o","payload":"TIMER","payloadType":"str","topic":"","x":90,"y":480,"wires":[["ed3e5d35.5b8e7","b77aa111.18563","b782b30b.315da"]]},{"id":"ee69d0c7.eac63","type":"function","z":"1b8c50a4.b4ffdf","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.payload = [hms, msg.payload];\n\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","x":460,"y":200,"wires":[["9353d7f7.b37348"]]},{"id":"9353d7f7.b37348","type":"sqlite","z":"1b8c50a4.b4ffdf","mydb":"19f59ce9.3edc23","sqlquery":"msg.topic","sql":"","name":"LED Status","x":610,"y":200,"wires":[["24cbdab4.befa96"]]},{"id":"24cbdab4.befa96","type":"debug","z":"1b8c50a4.b4ffdf","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":790,"y":200,"wires":[]},{"id":"3feb0d11.a6cc12","type":"comment","z":"1b8c50a4.b4ffdf","name":"Node-Red publish 到 HiveMQ Broker  , ESP32訂閱","info":"將 alex9ufo/inTopic 到(publish)HiveMQ Broker \n給 Arduino 訂閱(Subscribe)","x":210,"y":520,"wires":[]},{"id":"13c54c12.b76704","type":"comment","z":"1b8c50a4.b4ffdf","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":"4fc1cc20.c72bf4","type":"ui_button","z":"1b8c50a4.b4ffdf","name":"","group":"ac0f1141.eb50e","order":0,"width":"4","height":"2","passthru":false,"label":"LED 閃爍","tooltip":"","color":"yellow","bgcolor":"","icon":"fa-circle-o","payload":"FLASH","payloadType":"str","topic":"","x":80,"y":440,"wires":[["ed3e5d35.5b8e7","b77aa111.18563","b782b30b.315da"]]},{"id":"b77aa111.18563","type":"ui_audio","z":"1b8c50a4.b4ffdf","name":"","group":"ac0f1141.eb50e","voice":"zh-TW","always":true,"x":340,"y":480,"wires":[]},{"id":"b782b30b.315da","type":"ui_text","z":"1b8c50a4.b4ffdf","group":"ac0f1141.eb50e","order":0,"width":0,"height":0,"name":"","label":"Node-RED發行到MQTT的資料","format":"{{msg.payload}}","layout":"row-center","x":390,"y":360,"wires":[]},{"id":"370820be.05e2b","type":"function","z":"1b8c50a4.b4ffdf","name":"產生資料庫TABLE","func":"msg.topic = ' CREATE TABLE \"LED\" (\t\"id\"\tINTEGER,\t\"time_led\"\tTEXT,\"led_status\"\tTEXT,PRIMARY KEY(\"id\" AUTOINCREMENT))';\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":910,"y":320,"wires":[["4e0306a4.572ee8"]]},{"id":"3f380fb7.f8cae","type":"inject","z":"1b8c50a4.b4ffdf","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":650,"y":320,"wires":[["370820be.05e2b"]]},{"id":"250c32d4.4b76ce","type":"ui_button","z":"1b8c50a4.b4ffdf","name":"建立資料庫(只能執行一次)","group":"ac0f1141.eb50e","order":66,"width":"4","height":"2","passthru":false,"label":"建立資料庫(只能執行一次)","tooltip":"","color":"","bgcolor":"","icon":"","payload":"","payloadType":"str","topic":"","x":660,"y":360,"wires":[["370820be.05e2b"]]},{"id":"4e0306a4.572ee8","type":"sqlite","z":"1b8c50a4.b4ffdf","mydb":"19f59ce9.3edc23","sqlquery":"msg.topic","sql":"","name":"LED Status","x":890,"y":400,"wires":[["381f91b3.49f44e","dbc22efa.ae609"]]},{"id":"381f91b3.49f44e","type":"debug","z":"1b8c50a4.b4ffdf","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":1070,"y":400,"wires":[]},{"id":"dbc22efa.ae609","type":"ui_template","z":"1b8c50a4.b4ffdf","group":"ac0f1141.eb50e","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].time_led }}</td>\n    <td>{{msg.payload[$index].led_status}}</td> \n  </tr>\n</table>\n","storeOutMessages":true,"fwdInMessages":true,"resendOnRefresh":false,"templateScope":"local","x":920,"y":460,"wires":[[]]},{"id":"e207ffc6.9a995","type":"ui_button","z":"1b8c50a4.b4ffdf","name":"","group":"ac0f1141.eb50e","order":10,"width":"4","height":"2","passthru":false,"label":"檢視資料庫","tooltip":"","color":"","bgcolor":"","icon":"","payload":"檢視資料庫","payloadType":"str","topic":"SELECT * FROM LED ORDER BY  id  DESC LIMIT 100;","x":690,"y":400,"wires":[["4e0306a4.572ee8"]]},{"id":"841df58d.ee5e98","type":"mqtt-broker","z":"","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":"ac0f1141.eb50e","type":"ui_group","z":"","name":"RFID讀取","tab":"e03ab9d0.1a8f08","order":1,"disp":true,"width":"15","collapse":false},{"id":"19f59ce9.3edc23","type":"sqlitedb","z":"","db":"MQTT_LED.db","mode":"RWC"},{"id":"e03ab9d0.1a8f08","type":"ui_tab","z":"","name":"RFID","icon":"dashboard","order":1}]