RFID 第二次作業 程式 說明 (2)
(在 127.0.0.1:1880 執行Node-RED)
在 筆電 或 桌電 上執行 NODE_RED 程式
1) NODE_RED 程式下載
http://www.mediafire.com/file/5rhzqv92jl3atpw/RFID_flows.json/file
2) 與 https://fred.sensetecnic.com/ 上執行時資料庫不同
https://fred.sensetecnic.com -->litedb
127.0.0.1:1880 -->sqlite
node-red-node-sqlite
3) 利用 DB Browser for SQLite 檢視 database
先解壓安裝 DB Browser for SQLite
放D碟中 解壓縮 然後 執行 DB Browser for SQLite.exe
先將Node-RED 停止執行 不然database 是被 Locked
存放目錄C:\Users\alex\.node-red
Node-RED 上的設定
DB Browser瀏覽器
在Node-RED
CREATE TABLE RFIDtable( id INT PRIMARY KEY NOT NULL, currenttime TEXT , uidname TEXT)
建立資料庫(只能執行一次)
|
CREATE TABLE RFIDtable( id INT PRIMARY KEY NOT NULL, currenttime TEXT , uidname TEXT)
|
TABLE名稱 RFIDtable
欄位 1:
名稱 id 整數
主索引 不可以空
欄位 2:
名稱 currenttime
本文 不可以空
欄位 3:
名稱 uidname
本文 不可以空
1) 檢視 database structure
2) Browse Data 瀏覽你感應卡的紀錄
====================127.0.0.1:1880========================
[{"id":"6c154c07.2e4334","type":"mqtt in","z":"2bf3dd21.762ab2","name":"MQTT","topic":"alex9ufo/outTopic/RFID/json","qos":"2","broker":"81c048e3.19d648","x":70,"y":200,"wires":[["1b6cddd0.411892","ad60d83c.592798","937f970c.e92718","b79290a4.a2845","ea77f63d.d4d4f8"]]},{"id":"f47b5e3d.1e05a","type":"mqtt out","z":"2bf3dd21.762ab2","name":"","topic":"alex9ufo/inTopic","qos":"1","retain":"false","broker":"81c048e3.19d648","x":480,"y":620,"wires":[]},{"id":"340a1559.948cba","type":"ui_button","z":"2bf3dd21.762ab2","name":"","group":"e9bf7012.1da7","order":0,"width":"4","height":"4","passthru":false,"label":"on_LED","color":"white","bgcolor":"","icon":"fa-circle","payload":"#on","payloadType":"str","topic":"","x":80,"y":620,"wires":[["f47b5e3d.1e05a","54cdf945.5ff4c8","bb745bf7.e2d1c8","4b7eaf56.080bf"]]},{"id":"c29df606.171ca8","type":"ui_button","z":"2bf3dd21.762ab2","name":"","group":"e9bf7012.1da7","order":0,"width":"4","height":"4","passthru":false,"label":"off_LED","color":"black","bgcolor":"","icon":"fa-circle-o","payload":"#off","payloadType":"str","topic":"","x":80,"y":720,"wires":[["f47b5e3d.1e05a","54cdf945.5ff4c8","4b7eaf56.080bf","bb745bf7.e2d1c8"]]},{"id":"54cdf945.5ff4c8","type":"debug","z":"2bf3dd21.762ab2","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":470,"y":680,"wires":[]},{"id":"a80f187d.16a8e8","type":"ui_button","z":"2bf3dd21.762ab2","name":"","group":"e9bf7012.1da7","order":0,"width":"4","height":"4","passthru":false,"label":"flash_LED","color":"black","bgcolor":"","icon":"fa-circle-o","payload":"#flash","payloadType":"str","topic":"","x":90,"y":824,"wires":[["f47b5e3d.1e05a","54cdf945.5ff4c8","4b7eaf56.080bf","bb745bf7.e2d1c8"]]},{"id":"1b6cddd0.411892","type":"function","z":"2bf3dd21.762ab2","name":"json+時分秒","func":"//var date = new Date().toLocaleString();\nvar date = new Date();\n\nvar h = 8+ 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 = 'Time:(' + h + ':' + m + ':' + s + ')'+ msg.payload ;\nreturn msg;\n","outputs":1,"noerr":0,"x":270,"y":100,"wires":[["510fddef.316394"]]},{"id":"ad60d83c.592798","type":"switch","z":"2bf3dd21.762ab2","name":"","property":"payload","propertyType":"msg","rules":[{"t":"cont","v":"Alex9ufo","vt":"str"},{"t":"cont","v":"RaspberryPi","vt":"str"},{"t":"empty"}],"checkall":"true","repair":false,"outputs":3,"x":230,"y":200,"wires":[["dd05b4d6.36ce58","ebba791a.a7e2a8"],["dd05b4d6.36ce58","b6d45d4e.f4c11"],["dd05b4d6.36ce58","c1a64690.ed1cf8"]]},{"id":"dd05b4d6.36ce58","type":"debug","z":"2bf3dd21.762ab2","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":410,"y":180,"wires":[]},{"id":"ad5437ba.b57268","type":"ui_audio","z":"2bf3dd21.762ab2","name":"","group":"e9bf7012.1da7","voice":"en-US","always":"","x":840,"y":260,"wires":[]},{"id":"edef6f3b.8fb93","type":"ui_audio","z":"2bf3dd21.762ab2","name":"","group":"e9bf7012.1da7","voice":"en-US","always":"","x":560,"y":820,"wires":[]},{"id":"bb745bf7.e2d1c8","type":"function","z":"2bf3dd21.762ab2","name":"Format timestamp","func":"var date = new Date();\nvar h = 8+ 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,"x":390,"y":760,"wires":[["8f1977a5.5fe178"]]},{"id":"6a7ec87a.6130d8","type":"debug","z":"2bf3dd21.762ab2","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":530,"y":380,"wires":[]},{"id":"8ba9d2b5.baa97","type":"inject","z":"2bf3dd21.762ab2","name":"CREATE DB","topic":"CREATE TABLE RFIDtable( id INT PRIMARY KEY NOT NULL, currenttime TEXT , uidname TEXT)","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":170,"y":480,"wires":[["3b834a9d.ece256","baba51d7.ff34"]]},{"id":"4b7eaf56.080bf","type":"ui_text_input","z":"2bf3dd21.762ab2","name":"","label":"","group":"e9bf7012.1da7","order":9,"width":"12","height":"2","passthru":true,"mode":"text","delay":"0","topic":"","x":380,"y":824,"wires":[["edef6f3b.8fb93","287f4a27.0e3b46"]]},{"id":"ab8fc2d7.062ed","type":"ui_text_input","z":"2bf3dd21.762ab2","name":"","label":"","group":"e9bf7012.1da7","order":9,"width":"12","height":"1","passthru":true,"mode":"text","delay":"0","topic":"","x":660,"y":260,"wires":[["ad5437ba.b57268","1b020bf8.cb2a34"]]},{"id":"1b020bf8.cb2a34","type":"delay","z":"2bf3dd21.762ab2","name":"","pauseType":"delay","timeout":"80","timeoutUnits":"milliseconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"x":630,"y":340,"wires":[["20660122.b6ba7e"]]},{"id":"20660122.b6ba7e","type":"function","z":"2bf3dd21.762ab2","name":"Check Text","func":"text=msg.payload;\nif(text !== \"\"){\n msg.payload = \"\";\n return msg;\n}","outputs":1,"noerr":0,"x":790,"y":340,"wires":[["ab8fc2d7.062ed"]]},{"id":"287f4a27.0e3b46","type":"delay","z":"2bf3dd21.762ab2","name":"","pauseType":"delay","timeout":"80","timeoutUnits":"milliseconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"x":310,"y":880,"wires":[["84a16b19.eba6c8"]]},{"id":"84a16b19.eba6c8","type":"function","z":"2bf3dd21.762ab2","name":"Check Text","func":"text=msg.payload;\nif(text !== \"\"){\n msg.payload = \"\";\n return msg;\n}","outputs":1,"noerr":0,"x":470,"y":880,"wires":[["4b7eaf56.080bf"]]},{"id":"265fff15.dcb0c","type":"ui_button","z":"2bf3dd21.762ab2","name":"","group":"e9bf7012.1da7","order":0,"width":"12","height":"2","passthru":false,"label":"Testing......","color":"white","bgcolor":"","icon":"fa-circle","payload":"Testing","payloadType":"str","topic":"","x":410,"y":340,"wires":[["ab8fc2d7.062ed"]]},{"id":"80114846.5f0928","type":"ui_template","z":"2bf3dd21.762ab2","group":"e9bf7012.1da7","name":"","order":0,"width":"15","height":"10","format":"<table style=\"width:100%\">\n <tr>\n <th>Index</th> \n <th>Date</th> \n <th>RFID</th>\n </tr>\n <tr ng-repeat=\"x in msg.payload | limitTo:100\">\n <td>{{$index}}</td>\n <td>{{msg.payload[$index].currenttime}}</td>\n <td>{{msg.payload[$index].uidname}}</td> \n </tr>\n</table>","storeOutMessages":true,"fwdInMessages":true,"templateScope":"local","x":660,"y":440,"wires":[[]]},{"id":"510fddef.316394","type":"ui_text","z":"2bf3dd21.762ab2","group":"e9bf7012.1da7","order":0,"width":"0","height":"0","name":"","label":"MQTT_send_Message","format":"{{msg.payload}}","layout":"row-left","x":480,"y":100,"wires":[]},{"id":"d8febe13.e7878","type":"function","z":"2bf3dd21.762ab2","name":"INSERT","func":"msg.topic = \"INSERT INTO RFIDtable (id,currenttime, uidname) VALUES (?,?,?)\";\n\n\nvar date = new Date();\n\nvar h = 8+ date.getHours();\n\nvar m = date.getMinutes();\n\nvar s = date.getSeconds();\n\nif(h<10)\n{\n \n h = '0'+h;\n\n}\n\nif(m<10)\n{\n \n m = '0' + m;\n\n}\n\nif(s<10)\n{\n \n s = '0' + s;\n\n}\n\nvar hms= h + ':' + m + ':' + s ;\nvar id= Date.now() ;\n\nmsg.payload = [id ,hms, msg.payload];\nreturn msg;\n","outputs":1,"noerr":0,"x":320,"y":380,"wires":[["6a7ec87a.6130d8","3b834a9d.ece256","baba51d7.ff34"]]},{"id":"c1a64690.ed1cf8","type":"function","z":"2bf3dd21.762ab2","name":"不合法卡片","func":"msg.payload = 'illegal Card'\nreturn msg;","outputs":1,"noerr":0,"x":410,"y":300,"wires":[["ab8fc2d7.062ed"]]},{"id":"d027d993.2e3338","type":"inject","z":"2bf3dd21.762ab2","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":220,"y":300,"wires":[["c1a64690.ed1cf8"]]},{"id":"b6d45d4e.f4c11","type":"function","z":"2bf3dd21.762ab2","name":"RaspberryPi卡片","func":"msg.payload = 'RaspberryPi Card'\nreturn msg;","outputs":1,"noerr":0,"x":430,"y":260,"wires":[["ab8fc2d7.062ed"]]},{"id":"ebba791a.a7e2a8","type":"function","z":"2bf3dd21.762ab2","name":"Alex9ufo卡片","func":"msg.payload = 'Alex9ufo Card'\nreturn msg;","outputs":1,"noerr":0,"x":410,"y":220,"wires":[["ab8fc2d7.062ed"]]},{"id":"8f1977a5.5fe178","type":"function","z":"2bf3dd21.762ab2","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":570,"y":760,"wires":[["b9e12df.6ebe0d"]]},{"id":"b9e12df.6ebe0d","type":"http request","z":"2bf3dd21.762ab2","name":"","method":"POST","ret":"txt","url":"https://notify-api.line.me/api/notify","tls":"","x":730,"y":760,"wires":[["ff3638f7.630958"]]},{"id":"ff3638f7.630958","type":"debug","z":"2bf3dd21.762ab2","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":890,"y":760,"wires":[]},{"id":"ea77f63d.d4d4f8","type":"debug","z":"2bf3dd21.762ab2","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":270,"y":60,"wires":[]},{"id":"937f970c.e92718","type":"json","z":"2bf3dd21.762ab2","name":"","property":"payload","action":"str","pretty":false,"x":150,"y":380,"wires":[["d8febe13.e7878"]]},{"id":"76c07e20.19869","type":"ui_button","z":"2bf3dd21.762ab2","name":"","group":"e9bf7012.1da7","order":10,"width":"4","height":"4","passthru":false,"label":"View Data","color":"","bgcolor":"","icon":"","payload":"","payloadType":"str","topic":"SELECT * FROM RFIDtable ORDER BY id DESC LIMIT 100;","x":350,"y":540,"wires":[["3b834a9d.ece256","baba51d7.ff34"]]},{"id":"b79290a4.a2845","type":"function","z":"2bf3dd21.762ab2","name":"Format timestamp","func":"var date = new Date();\nvar h = 8+ 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,"x":290,"y":140,"wires":[["442a7b0b.adbfe4"]]},{"id":"442a7b0b.adbfe4","type":"function","z":"2bf3dd21.762ab2","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":470,"y":140,"wires":[["91a8e129.94f43"]]},{"id":"91a8e129.94f43","type":"http request","z":"2bf3dd21.762ab2","name":"","method":"POST","ret":"txt","url":"https://notify-api.line.me/api/notify","tls":"","x":630,"y":140,"wires":[["2ce8d9e9.bf8786"]]},{"id":"2ce8d9e9.bf8786","type":"debug","z":"2bf3dd21.762ab2","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":790,"y":140,"wires":[]},{"id":"1200d15f.6f6d8f","type":"comment","z":"2bf3dd21.762ab2","name":"CREATE TABLE RFIDtable","info":"CREATE TABLE RFIDtable( id INT PRIMARY KEY NOT NULL, currenttime TEXT , rfid TEXT)","x":150,"y":520,"wires":[]},{"id":"94e23354.7f66f","type":"ui_button","z":"2bf3dd21.762ab2","name":"Alex9ufo","group":"e9bf7012.1da7","order":2,"width":"4","height":"4","passthru":false,"label":"手動插入測試資料","color":"","bgcolor":"","icon":"","payload":"{\"RFIDNo\":\"Alex9ufo測試\"}","payloadType":"json","topic":"","x":500,"y":560,"wires":[["1025e32e.7e5efd"]]},{"id":"1025e32e.7e5efd","type":"mqtt out","z":"2bf3dd21.762ab2","name":"","topic":"alex9ufo/outTopic/RFID/json","qos":"","retain":"","broker":"81c048e3.19d648","x":720,"y":560,"wires":[]},{"id":"2a5cf0d.c21451","type":"ui_button","z":"2bf3dd21.762ab2","name":"","group":"e9bf7012.1da7","order":11,"width":"4","height":"4","passthru":false,"label":"建立資料庫(只能執行一次)","color":"","bgcolor":"","icon":"","payload":"","payloadType":"str","topic":"CREATE TABLE RFIDtable( id INT PRIMARY KEY NOT NULL, currenttime TEXT , uidname TEXT)","x":180,"y":440,"wires":[["3b834a9d.ece256","baba51d7.ff34"]]},{"id":"baba51d7.ff34","type":"sqlite","z":"2bf3dd21.762ab2","mydb":"3b834a9d.ece256","sqlquery":"msg.topic","sql":"","name":"RFID_DB","x":500,"y":440,"wires":[["80114846.5f0928"]]},{"id":"81c048e3.19d648","type":"mqtt-broker","broker":"broker.mqtt-dashboard.com","port":"1883","clientid":"","usetls":false,"compatmode":true,"keepalive":15,"cleansession":true,"willQos":"0","birthQos":"0"},{"id":"e9bf7012.1da7","type":"ui_group","z":"","name":"Control led ESP8266 (ESP32)","tab":"86e727dd.a96f28","order":1,"disp":true,"width":"15","collapse":false},{"id":"3b834a9d.ece256","type":"sqlitedb","z":"2bf3dd21.762ab2","db":"RFID.db","mode":"RWC"},{"id":"86e727dd.a96f28","type":"ui_tab","z":"","name":"RFID","icon":"dashboard","order":1}]
沒有留言:
張貼留言