2023年7月31日 星期一

實驗 ESP32 RFID /LED Control +Node-Red +Line Notify + Sqlite

 實驗 ESP32 RFID /LED Control  +Node-Red +Line Notify + Sqlite 







[{"id":"84c5b49449614692","type":"ui_button","z":"4911fe2890b9eb92","name":"","group":"85b1efd8d6d7c0dd","order":27,"width":3,"height":1,"passthru":false,"label":"建立資料庫","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"建立資料庫","payloadType":"str","topic":"topic","topicType":"msg","x":110,"y":440,"wires":[["3768d2ceadd97a7b","d594f15371bfe40c"]]},{"id":"382bb5846395bf63","type":"ui_button","z":"4911fe2890b9eb92","name":"","group":"85b1efd8d6d7c0dd","order":30,"width":5,"height":2,"passthru":false,"label":"檢視資料庫資料","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"檢視資料","payloadType":"str","topic":"topic","topicType":"msg","x":120,"y":760,"wires":[["3cedd51f30721ecf","cdcf3d57ea190a69"]]},{"id":"aa7404213db0d11d","type":"ui_button","z":"4911fe2890b9eb92","name":"","group":"85b1efd8d6d7c0dd","order":25,"width":3,"height":1,"passthru":false,"label":"刪除所有資料","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"刪除所有資料","payloadType":"str","topic":"topic","topicType":"msg","x":120,"y":1000,"wires":[["3c705fe4671b0305","6c673e8d726be5c8"]]},{"id":"83093d49254c6f9b","type":"sqlite","z":"4911fe2890b9eb92","mydb":"dd7f8134e015a1cb","sqlquery":"msg.topic","sql":"","name":"My_RFID_database","x":560,"y":440,"wires":[["95ccdcecdbbfdbe5","3cedd51f30721ecf"]]},{"id":"3768d2ceadd97a7b","type":"function","z":"4911fe2890b9eb92","name":"CREATE DATABASE","func":"\n\n//CREATE TABLE \"RFIDtable\" (\n//\t\"id\"\tINT NOT NULL,\n//  \"uidname\"  TEXT,\n//  \"currentdate\" DATE, \n//  \"currenttime\" TIME\n//\tPRIMARY KEY(\"id\")\n//);\nmsg.topic = \"CREATE TABLE RFIDtable(id INTEGER PRIMARY KEY AUTOINCREMENT, uidname TEXT, currentdate DATE, currenttime TIME)\";\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":320,"y":440,"wires":[["83093d49254c6f9b"]]},{"id":"95ccdcecdbbfdbe5","type":"debug","z":"4911fe2890b9eb92","name":"debug","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":770,"y":440,"wires":[]},{"id":"11dfab3f777eb877","type":"function","z":"4911fe2890b9eb92","name":"INSERT","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();     //秒\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}\nvar var_date = yyyy+'/'+MM+'/'+dd;\nvar var_time = h+':'+m+':'+s;\n\nvar myRFID = flow.get('uid_temp');\n\n\nmsg.topic = \"INSERT INTO RFIDtable ( uidname , currentdate, currenttime ) VALUES ($myRFID,  $var_date ,  $var_time ) \" ;\nmsg.payload = [myRFID, var_date , var_time ]\nreturn msg;\n\n//CREATE TABLE \"RFIDtable\" (\n//\t\"id\"\tINT NOT NULL,\n//  \"uidname\"  TEXT,\n//  \"currentdate\" DATE, \n//  \"currenttime\" TIME\n//\tPRIMARY KEY(\"id\")\n//);","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":340,"y":480,"wires":[["aae81142c03dd010","83093d49254c6f9b","0bebf0b047e5d8d3"]]},{"id":"3c705fe4671b0305","type":"function","z":"4911fe2890b9eb92","name":"刪除所有資料","func":"//DELETE from RFIDtable\nmsg.topic = \"DELETE from RFIDtable\";\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":340,"y":1000,"wires":[["2cb9e557a52a9515"]]},{"id":"2cb9e557a52a9515","type":"sqlite","z":"4911fe2890b9eb92","mydb":"dd7f8134e015a1cb","sqlquery":"msg.topic","sql":"","name":"My_RFID_database","x":560,"y":1000,"wires":[["b8a94866f235cbdb"]]},{"id":"d1c71010edbe95eb","type":"ui_button","z":"4911fe2890b9eb92","name":"","group":"85b1efd8d6d7c0dd","order":23,"width":3,"height":1,"passthru":false,"label":"刪除資料庫","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"刪除資料庫","payloadType":"str","topic":"topic","topicType":"msg","x":110,"y":1080,"wires":[["93b48cd33a4c46a2","84978357b2b53bc6"]]},{"id":"93b48cd33a4c46a2","type":"function","z":"4911fe2890b9eb92","name":"刪除資料庫","func":"//DROP TABLE RFIDtable\nmsg.topic = \"DROP TABLE RFIDtable\";\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":330,"y":1080,"wires":[["2cb9e557a52a9515"]]},{"id":"d04adada628e0469","type":"ui_template","z":"4911fe2890b9eb92","group":"85b1efd8d6d7c0dd","name":"","order":13,"width":12,"height":8,"format":"<table style=\"width:100%\">\n  <tr>\n    <th>idex</th> \n    <th>database_id</th>\n    <th>UID </th>\n    <th>Date</th>\n    <th>Times</th>\n  </tr>\n  \n  <tr ng-repeat=\"x in msg.payload | limitTo:50\">\n    <td>{{$index}}</td>\n    <td>{{msg.payload[$index].id}}</td>\n    <td>{{msg.payload[$index].uidname}}</td>\n    <td>{{msg.payload[$index].currentdate}}</td>\n    <td>{{msg.payload[$index].currenttime}}</td>\n  </tr>\n</table>","storeOutMessages":true,"fwdInMessages":true,"resendOnRefresh":true,"templateScope":"local","className":"","x":980,"y":1160,"wires":[[]]},{"id":"b8a94866f235cbdb","type":"debug","z":"4911fe2890b9eb92","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":750,"y":1000,"wires":[]},{"id":"3cedd51f30721ecf","type":"function","z":"4911fe2890b9eb92","name":"檢視資料","func":"\n//CREATE TABLE \"RFIDtable\" (\n//\t\"id\"\tINT NOT NULL,\n//  \"uidname\"  TEXT,\n//  \"currentdate\" DATE, \n//  \"currenttime\" TIME\n//\tPRIMARY KEY(\"id\")\n//);\n\n//SELECT * FROM RFIDtable ORDER BY  id DESC LIMIT 50;\n\nmsg.topic = \"SELECT * FROM RFIDtable ORDER BY id DESC LIMIT 50\";\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":580,"y":760,"wires":[["25abe838fe89cb80"]]},{"id":"25abe838fe89cb80","type":"sqlite","z":"4911fe2890b9eb92","mydb":"dd7f8134e015a1cb","sqlquery":"msg.topic","sql":"","name":"My_RFID_database","x":800.9005737304688,"y":893.5454711914062,"wires":[["d04adada628e0469"]]},{"id":"6bc6d7272a647e6e","type":"function","z":"4911fe2890b9eb92","name":"SELECT ALL","func":"var del_idtemp=msg.payload;\nflow.set(\"idtemp\", del_idtemp);\n\nmsg.topic = \"SELECT * FROM RFIDtable \";\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":330,"y":920,"wires":[["2cb9e557a52a9515","3d4ba3e708e02d20"]]},{"id":"e875e8fb94eb76c8","type":"function","z":"4911fe2890b9eb92","name":"確認 刪除","func":"var del_id = flow.get(\"idtemp\");\n\n\nmsg.topic = \"DELETE FROM RFIDtable WHERE id= ($del_id) \" ;\nmsg.payload = [del_id]\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":320,"y":820,"wires":[["4499ff1983ec4751"]]},{"id":"cf119a4f01b67f0e","type":"ui_numeric","z":"4911fe2890b9eb92","name":"","label":"刪除的database_id","tooltip":"","group":"85b1efd8d6d7c0dd","order":26,"width":3,"height":1,"wrap":false,"passthru":true,"topic":"topic","topicType":"msg","format":"{{value}}","min":"1","max":"100","step":1,"className":"","x":130,"y":920,"wires":[["6bc6d7272a647e6e","6c673e8d726be5c8"]]},{"id":"4499ff1983ec4751","type":"sqlite","z":"4911fe2890b9eb92","mydb":"dd7f8134e015a1cb","sqlquery":"msg.topic","sql":"DELETE FROM dhtreadings WHERE id =  VALUES ($theid)","name":"My_RFID_database","x":520,"y":820,"wires":[["3cedd51f30721ecf"]]},{"id":"eb9a84fdf9433c0b","type":"ui_button","z":"4911fe2890b9eb92","name":"","group":"85b1efd8d6d7c0dd","order":24,"width":3,"height":1,"passthru":false,"label":"刪除一筆資料","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"刪除一筆資料","payloadType":"str","topic":"topic","topicType":"msg","x":120,"y":820,"wires":[["e875e8fb94eb76c8","cdcf3d57ea190a69"]]},{"id":"3d4ba3e708e02d20","type":"debug","z":"4911fe2890b9eb92","name":"debug 130","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":530,"y":920,"wires":[]},{"id":"c1d07a5d7979271a","type":"mqtt in","z":"4911fe2890b9eb92","name":"新增 RFID","topic":"alex9ufo/esp32/RFID","qos":"2","datatype":"auto-detect","broker":"841df58d.ee5e98","nl":false,"rap":true,"rh":0,"inputs":0,"x":100,"y":480,"wires":[["6b70e1b922fbb8f1"]]},{"id":"aae81142c03dd010","type":"debug","z":"4911fe2890b9eb92","name":"debug 131","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":550,"y":480,"wires":[]},{"id":"8f7d650eafc7c493","type":"ui_text_input","z":"4911fe2890b9eb92","name":"","label":"新增一筆資料","tooltip":"","group":"85b1efd8d6d7c0dd","order":28,"width":3,"height":1,"passthru":true,"mode":"text","delay":300,"topic":"topic","sendOnBlur":true,"className":"","topicType":"msg","x":340,"y":560,"wires":[[]]},{"id":"9dcbc409eb4b3470","type":"ui_audio","z":"4911fe2890b9eb92","name":"","group":"85b1efd8d6d7c0dd","voice":"Microsoft Hanhan - Chinese (Traditional, Taiwan)","always":true,"x":520,"y":640,"wires":[]},{"id":"d329d88b9649595f","type":"function","z":"4911fe2890b9eb92","name":"function ","func":"var temp= msg.payload;\nmsg.payload= \"新增一筆資料\" + temp;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":320,"y":600,"wires":[["9dcbc409eb4b3470","0d33954ed3e764cd"]]},{"id":"cdcf3d57ea190a69","type":"ui_audio","z":"4911fe2890b9eb92","name":"","group":"85b1efd8d6d7c0dd","voice":"Microsoft Hanhan - Chinese (Traditional, Taiwan)","always":true,"x":320,"y":780,"wires":[]},{"id":"d594f15371bfe40c","type":"ui_audio","z":"4911fe2890b9eb92","name":"","group":"85b1efd8d6d7c0dd","voice":"Microsoft Hanhan - Chinese (Traditional, Taiwan)","always":true,"x":280,"y":400,"wires":[]},{"id":"f92e5ee564a88937","type":"function","z":"4911fe2890b9eb92","name":"function 50","func":"//SELECT trackid,name FROM \ttracks WHERE name LIKE 'Wild%'\n//Query\n//CREATE TABLE \"RFIDtable\" (\n//\t\"id\"\tINT NOT NULL,\n//  \"uidname\"  TEXT,\n//  \"currentdate\" DATE, \n//  \"currenttime\" TIME\n//\tPRIMARY KEY(\"id\")\n//);\n//msg.topic = \"DELETE FROM RFIDtable WHERE id= ($del_id) \" ;\n\nvar query_uid = flow.get(\"uidtemp\");\nmsg.topic = \"SELECT id,uidname , currentdate,currenttime FROM RFIDtable WHERE uidname LIKE ($query_uid) \";\nmsg.payload = [query_uid]\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":350,"y":1160,"wires":[["4f36c7681231859f","ae7ee34d0d2ecc71"]]},{"id":"c94a37b67d2a3106","type":"ui_button","z":"4911fe2890b9eb92","name":"","group":"85b1efd8d6d7c0dd","order":17,"width":3,"height":1,"passthru":false,"label":"比對資料庫","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"比對資料庫","payloadType":"str","topic":"topic","topicType":"msg","x":110,"y":1160,"wires":[["f92e5ee564a88937","84978357b2b53bc6","c9386f3b40cf18d2"]]},{"id":"4f36c7681231859f","type":"sqlite","z":"4911fe2890b9eb92","mydb":"dd7f8134e015a1cb","sqlquery":"msg.topic","sql":"","name":"My_RFID_database","x":740,"y":1160,"wires":[["d04adada628e0469"]]},{"id":"77f09a925ed5d3f8","type":"function","z":"4911fe2890b9eb92","name":"SELECT ALL","func":"var query_uidtemp=msg.payload;\nflow.set(\"uidtemp\", query_uidtemp);\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":510,"y":700,"wires":[["c39ec9a999838412","3ea049f45947cf06","ac1fc48e834a543c"]]},{"id":"5b36f5ddb217c40a","type":"ui_text_input","z":"4911fe2890b9eb92","name":"","label":"手動查詢資料的uidname","tooltip":"","group":"85b1efd8d6d7c0dd","order":19,"width":3,"height":1,"passthru":true,"mode":"text","delay":300,"topic":"topic","sendOnBlur":true,"className":"","topicType":"msg","x":150,"y":700,"wires":[["77f09a925ed5d3f8"]]},{"id":"d0dd4e0382b7fa01","type":"function","z":"4911fe2890b9eb92","name":"function ","func":"var query=msg.payload;\n\nif (query===true)\n    msg.payload='Query';\nelse\n    msg.payload='NotQuery';\n\nflow.set(\"query_temp\", msg.payload);\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":540,"y":1320,"wires":[["b22d526626577d12","042b262a0e889f51"]]},{"id":"f47a3b01381d21dc","type":"ui_switch","z":"4911fe2890b9eb92","name":"","label":"新增模式  /自動比對模式 ","tooltip":"","group":"85b1efd8d6d7c0dd","order":16,"width":"6","height":1,"passthru":true,"decouple":"false","topic":"topic","topicType":"msg","style":"","onvalue":"true","onvalueType":"bool","onicon":"","oncolor":"","offvalue":"false","offvalueType":"bool","officon":"","offcolor":"","animate":false,"className":"","x":330,"y":1320,"wires":[["d0dd4e0382b7fa01"]]},{"id":"b22d526626577d12","type":"debug","z":"4911fe2890b9eb92","name":"debug 132","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":690,"y":1280,"wires":[]},{"id":"43e0206b5f4878fe","type":"switch","z":"4911fe2890b9eb92","name":"","property":"payload","propertyType":"msg","rules":[{"t":"eq","v":"Create","vt":"str"},{"t":"eq","v":"Query","vt":"str"}],"checkall":"true","repair":false,"outputs":2,"x":130,"y":620,"wires":[["11dfab3f777eb877","8f7d650eafc7c493","d329d88b9649595f","b18f49985b97c40b"],["142f4115dd46079a","8f7d650eafc7c493","eed385d0f4b46046"]]},{"id":"6b70e1b922fbb8f1","type":"function","z":"4911fe2890b9eb92","name":"function ","func":"var query = flow.get(\"query_temp\");\n\nflow.set(\"uid_temp\", msg.payload);\n\nif (query==='NotQuery')\n    msg.payload='Create'\nelse\n    msg.payload='Query'\n    \nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":120,"y":540,"wires":[["43e0206b5f4878fe"]]},{"id":"b18f49985b97c40b","type":"debug","z":"4911fe2890b9eb92","name":"debug 133","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":330,"y":520,"wires":[]},{"id":"042b262a0e889f51","type":"ui_audio","z":"4911fe2890b9eb92","name":"","group":"fe68da70e8d7a3c8","voice":"Microsoft Hanhan - Chinese (Traditional, Taiwan)","always":true,"x":700,"y":1320,"wires":[]},{"id":"ae7ee34d0d2ecc71","type":"debug","z":"4911fe2890b9eb92","name":"debug ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":470,"y":1200,"wires":[]},{"id":"c39ec9a999838412","type":"debug","z":"4911fe2890b9eb92","name":"debug 134","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":750,"y":680,"wires":[]},{"id":"142f4115dd46079a","type":"function","z":"4911fe2890b9eb92","name":"function","func":"\nvar a= flow.get(\"uid_temp\");\nmsg.payload=a;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":320,"y":640,"wires":[["77f09a925ed5d3f8","9dcbc409eb4b3470"]]},{"id":"3ea049f45947cf06","type":"ui_text","z":"4911fe2890b9eb92","group":"85b1efd8d6d7c0dd","order":31,"width":6,"height":1,"name":"","label":"自動感應RFID查詢的uid","format":"{{msg.payload}}","layout":"row-left","className":"","x":770,"y":600,"wires":[]},{"id":"ac1fc48e834a543c","type":"link out","z":"4911fe2890b9eb92","name":"link out 12","mode":"link","links":[],"x":695,"y":720,"wires":[]},{"id":"11126d920e4fa323","type":"link in","z":"4911fe2890b9eb92","name":"link in 14","links":["eed385d0f4b46046","f316a7347a7ff85a"],"x":235,"y":1200,"wires":[["f92e5ee564a88937"]]},{"id":"718af4ae09a1e283","type":"function","z":"4911fe2890b9eb92","name":"function ","func":"//CREATE TABLE \"RFIDtable\" (\n//\t\"id\"\tINT NOT NULL,\n//  \"uidname\"  TEXT,\n//  \"currentdate\" DATE, \n//  \"currenttime\" TIME\n//\tPRIMARY KEY(\"id\")\n//);\n\n//msg.topic = \"INSERT INTO RFIDtable ( uidname , currentdate, currenttime ) VALUES ($myRFID,  $var_date ,  $var_time ) \" ;\n//msg.payload = [myRFID, var_date , var_time ]\n//return msg;\n\n\nvar tmp=msg.payload;\nmsg.topic = \"select count( * ) as 總共有幾筆資料 from RFIDtable where uidname=($tmp)\";\nmsg.payload=[tmp];\nreturn msg;\n\n// select count( * ) as 總共有幾筆資料 from Customers where address='Japan'","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":460,"y":1420,"wires":[["c27120970394efe4","5f08cc434a7f4a65"]]},{"id":"c27120970394efe4","type":"sqlite","z":"4911fe2890b9eb92","mydb":"dd7f8134e015a1cb","sqlquery":"msg.topic","sql":"","name":"My_RFID_database","x":660,"y":1420,"wires":[["f785d6c52481bd32"]]},{"id":"f785d6c52481bd32","type":"function","z":"4911fe2890b9eb92","name":"function ","func":"var num=msg.payload[0].總共有幾筆資料;\nmsg.payload=num;\nreturn msg; \n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":780,"y":1460,"wires":[["e3f4f6848ab41b16","3e425575de8b29e7","38cbe35979c84ef2"]]},{"id":"379a5ed11d9969ec","type":"ui_text","z":"4911fe2890b9eb92","group":"85b1efd8d6d7c0dd","order":21,"width":4,"height":1,"name":"","label":"查詢結果","format":"<font face='arial'><font size=3><font color={{fcolor}}>{{msg.payload}}","layout":"row-left","className":"","x":1140,"y":1480,"wires":[]},{"id":"01074a1557922882","type":"ui_text","z":"4911fe2890b9eb92","group":"85b1efd8d6d7c0dd","order":20,"width":4,"height":1,"name":"","label":"查詢結果:筆數","format":"<font face='arial'><font size=6><font color={{fcolor}}>{{msg.payload}}","layout":"row-left","className":"","x":1160,"y":1440,"wires":[]},{"id":"3e8413d5fcd319fd","type":"mqtt in","z":"4911fe2890b9eb92","name":"","topic":"alex9ufo/esp32/Starting","qos":"2","datatype":"auto-detect","broker":"841df58d.ee5e98","nl":false,"rap":true,"rh":0,"inputs":0,"x":140,"y":1560,"wires":[["f06cd8f545d850ad","28bec2583fd63be8","bba8d9cf66c8696e"]]},{"id":"f06cd8f545d850ad","type":"ui_audio","z":"4911fe2890b9eb92","name":"","group":"85b1efd8d6d7c0dd","voice":"Microsoft Hanhan - Chinese (Traditional, Taiwan)","always":true,"x":380,"y":1600,"wires":[]},{"id":"28bec2583fd63be8","type":"debug","z":"4911fe2890b9eb92","name":"debug 135","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":390,"y":1560,"wires":[]},{"id":"bba8d9cf66c8696e","type":"change","z":"4911fe2890b9eb92","name":"","rules":[{"t":"set","p":"fcolor","pt":"msg","to":"red","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":400,"y":1520,"wires":[["38544c6ac54f3327"]]},{"id":"38544c6ac54f3327","type":"ui_text","z":"4911fe2890b9eb92","group":"85b1efd8d6d7c0dd","order":22,"width":4,"height":1,"name":"","label":"","format":"<font face='arial'><font size=4><font color={{fcolor}}>{{msg.payload}}","layout":"row-left","className":"","x":570,"y":1520,"wires":[]},{"id":"5f08cc434a7f4a65","type":"debug","z":"4911fe2890b9eb92","name":"debug","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":610,"y":1380,"wires":[]},{"id":"e3f4f6848ab41b16","type":"change","z":"4911fe2890b9eb92","name":"","rules":[{"t":"set","p":"fcolor","pt":"msg","to":"red","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":980,"y":1440,"wires":[["01074a1557922882","ef672c1fc731d682"]]},{"id":"15115f73ed8ec3fa","type":"change","z":"4911fe2890b9eb92","name":"","rules":[{"t":"set","p":"fcolor","pt":"msg","to":"red","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":980,"y":1480,"wires":[["379a5ed11d9969ec"]]},{"id":"c9386f3b40cf18d2","type":"function","z":"4911fe2890b9eb92","name":"function","func":"\nvar a= flow.get(\"uidtemp\");\nmsg.payload=a;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":300,"y":1420,"wires":[["718af4ae09a1e283"]]},{"id":"eed385d0f4b46046","type":"link out","z":"4911fe2890b9eb92","name":"link out 13","mode":"link","links":["abf37a6bec449339","11126d920e4fa323"],"x":215,"y":660,"wires":[]},{"id":"abf37a6bec449339","type":"link in","z":"4911fe2890b9eb92","name":"link in 15","links":["eed385d0f4b46046","f316a7347a7ff85a"],"x":175,"y":1380,"wires":[["c9386f3b40cf18d2"]]},{"id":"34e0cb821ff0bf43","type":"inject","z":"4911fe2890b9eb92","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":true,"onceDelay":"2","topic":"false","payload":"false","payloadType":"str","x":130,"y":1320,"wires":[["f47a3b01381d21dc"]]},{"id":"e125b41c694c4900","type":"ui_audio","z":"4911fe2890b9eb92","name":"","group":"85b1efd8d6d7c0dd","voice":"Microsoft Hanhan - Chinese (Traditional, Taiwan)","always":true,"x":1140,"y":1520,"wires":[]},{"id":"ef672c1fc731d682","type":"debug","z":"4911fe2890b9eb92","name":"debug","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1130,"y":1400,"wires":[]},{"id":"79c0c38cadb94aa5","type":"delay","z":"4911fe2890b9eb92","name":"","pauseType":"delay","timeout":"3","timeoutUnits":"seconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"allowrate":false,"outputs":1,"x":960,"y":1520,"wires":[["e125b41c694c4900"]]},{"id":"3e425575de8b29e7","type":"function","z":"4911fe2890b9eb92","name":"function ","func":"var n=msg.payload;\nif (n>0)\n    msg.payload='RFID符合';\nelse\n    msg.payload='RFID錯誤';\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":780,"y":1520,"wires":[["79c0c38cadb94aa5","15115f73ed8ec3fa","c9ea048a141c3604"]]},{"id":"26feb9493ff2c2f7","type":"ui_button","z":"4911fe2890b9eb92","name":"","group":"85b1efd8d6d7c0dd","order":6,"width":2,"height":1,"passthru":false,"label":"LED 開","tooltip":"","color":"white","bgcolor":"","className":"","icon":"fa-circle","payload":"on","payloadType":"str","topic":"","topicType":"str","x":100,"y":80,"wires":[["1a906f690450d437","6ef225bc141c760b","a0a7c0ced976d432"]]},{"id":"a90d0c9936cfe74c","type":"ui_button","z":"4911fe2890b9eb92","name":"","group":"85b1efd8d6d7c0dd","order":7,"width":2,"height":1,"passthru":false,"label":"LED 關","tooltip":"","color":"black","bgcolor":"","className":"","icon":"fa-circle-o","payload":"off","payloadType":"str","topic":"","topicType":"str","x":100,"y":120,"wires":[["1a906f690450d437","6ef225bc141c760b","a0a7c0ced976d432"]]},{"id":"1a906f690450d437","type":"mqtt out","z":"4911fe2890b9eb92","name":"Control LED","topic":"alex9ufo/esp32/led","qos":"1","retain":"true","respTopic":"","contentType":"","userProps":"","correl":"","expiry":"","broker":"70940176.2b2d3","x":350,"y":80,"wires":[]},{"id":"10e82a96f9ec2e7b","type":"comment","z":"4911fe2890b9eb92","name":"Node-Red publish 到 HiveMQ Broker  , ESP32訂閱","info":"將 alex9ufo/inTopic 到(publish)HiveMQ Broker \n給 Arduino 訂閱(Subscribe)","x":230,"y":40,"wires":[]},{"id":"6ef225bc141c760b","type":"ui_audio","z":"4911fe2890b9eb92","name":"","group":"85b1efd8d6d7c0dd","voice":"zh-TW","always":true,"x":340,"y":220,"wires":[]},{"id":"a0a7c0ced976d432","type":"ui_text","z":"4911fe2890b9eb92","group":"85b1efd8d6d7c0dd","order":14,"width":0,"height":0,"name":"","label":"Node-RED發行到MQTT的資料","format":"{{msg.payload}}","layout":"row-left","className":"","x":410,"y":160,"wires":[]},{"id":"8d8868766384608b","type":"ui_audio","z":"4911fe2890b9eb92","name":"","group":"85b1efd8d6d7c0dd","voice":"en-US","always":"","x":580,"y":320,"wires":[]},{"id":"340bd87e50aa2e0e","type":"mqtt in","z":"4911fe2890b9eb92","name":"LED status ","topic":"alex9ufo/esp32/led_status","qos":"2","datatype":"utf8","broker":"841df58d.ee5e98","nl":false,"rap":true,"rh":0,"inputs":0,"x":100,"y":320,"wires":[["541b74118a464b97"]]},{"id":"80b73d2c5ccb5619","type":"comment","z":"4911fe2890b9eb92","name":"Node-Red  subscribe HiveMQ Broker  , ESP32發行","info":"將 alex9ufo/inTopic 到(publish)HiveMQ Broker \n給 Arduino 訂閱(Subscribe)","x":220,"y":280,"wires":[]},{"id":"b1ba4f6cb851cb26","type":"ui_text","z":"4911fe2890b9eb92","group":"85b1efd8d6d7c0dd","order":15,"width":0,"height":0,"name":"","label":"Node-RED 訂閱MQTT的資料","format":"{{msg.payload}}","layout":"row-left","className":"","x":500,"y":360,"wires":[]},{"id":"fdec2950f8926fda","type":"ui_button","z":"4911fe2890b9eb92","name":"","group":"85b1efd8d6d7c0dd","order":8,"width":3,"height":1,"passthru":false,"label":"LED 閃爍","tooltip":"","color":"black","bgcolor":"","className":"","icon":"fa-circle-o","payload":"flash","payloadType":"str","topic":"","topicType":"str","x":100,"y":160,"wires":[["1a906f690450d437","a0a7c0ced976d432","6ef225bc141c760b"]]},{"id":"a7944307b8dca79a","type":"ui_button","z":"4911fe2890b9eb92","name":"","group":"85b1efd8d6d7c0dd","order":9,"width":2,"height":1,"passthru":false,"label":"LED反向","tooltip":"","color":"black","bgcolor":"","className":"","icon":"fa-circle-o","payload":"toggle","payloadType":"str","topic":"","topicType":"str","x":100,"y":200,"wires":[["1a906f690450d437","a0a7c0ced976d432","6ef225bc141c760b"]]},{"id":"2ae7817b65869c18","type":"ui_button","z":"4911fe2890b9eb92","name":"","group":"85b1efd8d6d7c0dd","order":10,"width":3,"height":1,"passthru":false,"label":"LED 定時5秒","tooltip":"","color":"black","bgcolor":"","className":"","icon":"fa-circle-o","payload":"timer","payloadType":"str","topic":"","topicType":"str","x":110,"y":240,"wires":[["1a906f690450d437","a0a7c0ced976d432","6ef225bc141c760b"]]},{"id":"541b74118a464b97","type":"function","z":"4911fe2890b9eb92","name":"function 51","func":"msg.payload=\" ---ESP32回來資料---\" +msg.payload;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":270,"y":320,"wires":[["b1ba4f6cb851cb26","4f1db8eb470fbe3d","03b7622dc4f1bd4d"]]},{"id":"4f1db8eb470fbe3d","type":"delay","z":"4911fe2890b9eb92","name":"","pauseType":"delay","timeout":"1","timeoutUnits":"seconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"allowrate":false,"outputs":1,"x":440,"y":320,"wires":[["8d8868766384608b"]]},{"id":"0d33954ed3e764cd","type":"function","z":"4911fe2890b9eb92","name":"function 52","func":"var myRFID = flow.get('uid_temp');\nmsg.payload=myRFID;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":530,"y":600,"wires":[["3ea049f45947cf06"]]},{"id":"84978357b2b53bc6","type":"ui_audio","z":"4911fe2890b9eb92","name":"","group":"85b1efd8d6d7c0dd","voice":"Microsoft Hanhan - Chinese (Traditional, Taiwan)","always":true,"x":320,"y":1120,"wires":[]},{"id":"6c673e8d726be5c8","type":"ui_audio","z":"4911fe2890b9eb92","name":"","group":"85b1efd8d6d7c0dd","voice":"Microsoft Hanhan - Chinese (Traditional, Taiwan)","always":true,"x":320,"y":960,"wires":[]},{"id":"38cbe35979c84ef2","type":"debug","z":"4911fe2890b9eb92","name":"debug 136","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":910,"y":1340,"wires":[]},{"id":"03b7622dc4f1bd4d","type":"function","z":"4911fe2890b9eb92","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":550,"y":260,"wires":[["e0b0e890252a38a2"]]},{"id":"e0b0e890252a38a2","type":"http request","z":"4911fe2890b9eb92","name":"","method":"POST","ret":"txt","paytoqs":false,"url":"https://notify-api.line.me/api/notify","tls":"","persist":false,"proxy":"","authType":"","x":720,"y":260,"wires":[["eda286b04d95abf8"]]},{"id":"eda286b04d95abf8","type":"debug","z":"4911fe2890b9eb92","name":"debug 137","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":890,"y":260,"wires":[]},{"id":"e3ba592186b142d4","type":"comment","z":"4911fe2890b9eb92","name":"Line Notify Message ","info":"","x":730,"y":220,"wires":[]},{"id":"0bebf0b047e5d8d3","type":"function","z":"4911fe2890b9eb92","name":"function 53","func":"var ms1=msg.payload[0];\nvar ms2=msg.payload[1];\nvar ms3=msg.payload[2];\n\nmsg.payload=\"新增一筆:\"+ms1+\", 日期: \"+ms2+\", 時間:\"+ms3;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":550,"y":400,"wires":[["12ce157bd820e804"]]},{"id":"12ce157bd820e804","type":"function","z":"4911fe2890b9eb92","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":770,"y":400,"wires":[["475c1b0634617f9e"]]},{"id":"475c1b0634617f9e","type":"http request","z":"4911fe2890b9eb92","name":"","method":"POST","ret":"txt","paytoqs":false,"url":"https://notify-api.line.me/api/notify","tls":"","persist":false,"proxy":"","authType":"","x":920,"y":400,"wires":[["0d05b9f35f70490e"]]},{"id":"0d05b9f35f70490e","type":"debug","z":"4911fe2890b9eb92","name":"debug 138","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":1070,"y":400,"wires":[]},{"id":"8a5d960612fd14e5","type":"comment","z":"4911fe2890b9eb92","name":"Line Notify Message ","info":"","x":830,"y":360,"wires":[]},{"id":"e362af0df87936ee","type":"function","z":"4911fe2890b9eb92","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":930,"y":1620,"wires":[["8f1805c88f21ab24"]]},{"id":"576c1847150a48ae","type":"comment","z":"4911fe2890b9eb92","name":"Line Notify Message ","info":"","x":950,"y":1580,"wires":[]},{"id":"8f1805c88f21ab24","type":"http request","z":"4911fe2890b9eb92","name":"","method":"POST","ret":"txt","paytoqs":false,"url":"https://notify-api.line.me/api/notify","tls":"","persist":false,"proxy":"","authType":"","x":1080,"y":1620,"wires":[["5e5c4bdeadbd2d64"]]},{"id":"5e5c4bdeadbd2d64","type":"debug","z":"4911fe2890b9eb92","name":"debug 139","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":1230,"y":1620,"wires":[]},{"id":"c9ea048a141c3604","type":"function","z":"4911fe2890b9eb92","name":"function","func":"\nvar a= flow.get(\"uidtemp\");\nvar b=msg.payload;\nmsg.payload=a+\"--->\"+b;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":740,"y":1620,"wires":[["e362af0df87936ee"]]},{"id":"85b1efd8d6d7c0dd","type":"ui_group","name":"ESP32","tab":"eeb8e179.a47a4","order":2,"disp":true,"width":"13","collapse":false,"className":""},{"id":"dd7f8134e015a1cb","type":"sqlitedb","db":"C:\\Users\\User\\.node-red\\my_2023rfid.db","mode":"RWC"},{"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":"fe68da70e8d7a3c8","type":"ui_group","name":"2023 RFID Home","tab":"9c33f04edb058032","order":3,"disp":true,"width":"14","collapse":false,"className":""},{"id":"70940176.2b2d3","type":"mqtt-broker","name":"","broker":"broker.mqtt-dashboard.com","port":"1883","clientid":"","usetls":false,"compatmode":true,"keepalive":"15","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closePayload":"","willTopic":"","willQos":"0","willPayload":""},{"id":"eeb8e179.a47a4","type":"ui_tab","name":"2023 RFID Database","icon":"dashboard","disabled":false,"hidden":false},{"id":"9c33f04edb058032","type":"ui_tab","name":"2023-07 Home","icon":"dashboard","disabled":false,"hidden":false}]

Node-Red 擷取open data CWB氣象資料

 Node-Red 擷取open data CWB氣象資料




[{"id":"29524fb9ae25eac2","type":"inject","z":"be36ae0c358ba0e7","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":true,"onceDelay":"2","topic":"","payload":"","payloadType":"date","x":100,"y":40,"wires":[["67b10f612aeba21d"]]},{"id":"67b10f612aeba21d","type":"http request","z":"be36ae0c358ba0e7","name":"","method":"GET","ret":"obj","paytoqs":"ignore","url":"https://opendata.cwb.gov.tw/api/v1/rest/datastore/O-A0003-001?Authorization=CWB-40C25FFF-1224-4250-B9D9-3735AAE17DBF&elementName=TEMP,HUMD,PRES,Weather","tls":"","persist":false,"proxy":"","insecureHTTPParser":false,"authType":"","senderr":false,"headers":[],"x":240,"y":40,"wires":[["3a247ad8571a9d43","ebfcca1381b1cb9d","f579543ae7bb2f2d"]]},{"id":"3a247ad8571a9d43","type":"debug","z":"be36ae0c358ba0e7","name":"debug 119","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":450,"y":20,"wires":[]},{"id":"ebfcca1381b1cb9d","type":"function","z":"be36ae0c358ba0e7","name":"function 42","func":"// 範例程式碼\nmsg.payload = {\n    temperature: msg.payload.records.location[0].weatherElement[0].elementValue,\n    humidity: msg.payload.records.location[0].weatherElement[1].elementValue,\n    pressure: msg.payload.records.location[0].weatherElement[2].elementValue,\n    weather: msg.payload.records.location[0].weatherElement[3].elementValue\n};\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":450,"y":60,"wires":[["ed8ee569c17ccd6a"]]},{"id":"ed8ee569c17ccd6a","type":"debug","z":"be36ae0c358ba0e7","name":"debug 120","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":610,"y":60,"wires":[]},{"id":"f579543ae7bb2f2d","type":"function","z":"be36ae0c358ba0e7","name":"function 56","func":"var number=msg.payload.records.location.length;\nvar records_array=[];\nvar i;\nfor (i=0; i<number;i++)\n{\n    records_array[i] = {\n        Name : msg.payload.records.location[i].locationName,\n        TEMP : msg.payload.records.location[i].weatherElement[0].elementValue,\n        HUMD : msg.payload.records.location[i].weatherElement[1].elementValue,\n        PRES : msg.payload.records.location[i].weatherElement[2].elementValue,   \n        Weather:msg.payload.records.location[i].weatherElement[3].elementValue  \n    }\n}\n\nmsg.payload=records_array;\nflow.set(\"RECORDS\",records_array);\n\nreturn msg;\n\n//flow.set('YourVariable', value);    // to store a variable (YourVariable)\n//var x = flow.get('YourVariable'); //to retrieve a variable (YourVariable)","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":250,"y":100,"wires":[["6c406be5e8d24135","8d7f937d81990ed0","972fb65a7d34bf3d"]]},{"id":"6c406be5e8d24135","type":"debug","z":"be36ae0c358ba0e7","name":"debug 145","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":450,"y":100,"wires":[]},{"id":"8d7f937d81990ed0","type":"function","z":"be36ae0c358ba0e7","name":"function 57","func":"flow.set(\"temp\",msg.payload);\nvar rawdata = flow.get(\"temp\");\n\nvar number=msg.payload.length;\nvar records_array=[];\nvar i;\nfor (i=0; i<number;i++)\n{\n    records_array[i] = msg.payload[i].Name\n \n}\n\nmsg.payload=records_array;\nflow.set(\"NAME_RECORDS\",records_array);\n\nreturn msg;\n\n//flow.set('YourVariable', value);    // to store a variable (YourVariable)\n//var x = flow.get('YourVariable'); //to retrieve a variable (YourVariable)","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":250,"y":180,"wires":[["dc6a54fc2b9b1532","67210cdb4382a327"]]},{"id":"dc6a54fc2b9b1532","type":"debug","z":"be36ae0c358ba0e7","name":"debug 146","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":450,"y":180,"wires":[]},{"id":"972fb65a7d34bf3d","type":"ui_table","z":"be36ae0c358ba0e7","group":"b8630066.5d446","name":"","order":37,"width":"10","height":"8","columns":[],"outputs":0,"cts":false,"x":430,"y":140,"wires":[]},{"id":"67210cdb4382a327","type":"function","z":"be36ae0c358ba0e7","name":"transform data to correct format","func":"flow.set(\"weather_temp\",msg.payload);\n\nmsg.options = [];//create empty array\n\nfor(let i = 0; i < msg.payload.length; i++){\n    let row = msg.payload[i]; //get the row\n    let opt = {};//make new opt object\n    opt[row] = i; //add a propery to object called row[0] & set its value to row[1]\n    msg.options.push(opt);//add the opt to array    \n//      msg.options.push(row);\n}\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":310,"y":220,"wires":[["e37e688e1efd4a37"]]},{"id":"498fde9063a6124a","type":"ui_toast","z":"be36ae0c358ba0e7","position":"top right","displayTime":"3","highlight":"","sendall":true,"outputs":0,"ok":"OK","cancel":"","raw":false,"className":"","topic":"","name":"","x":730,"y":220,"wires":[]},{"id":"e37e688e1efd4a37","type":"ui_dropdown","z":"be36ae0c358ba0e7","name":"","label":"","tooltip":"","place":"請選地名","group":"b8630066.5d446","order":0,"width":0,"height":0,"passthru":true,"multiple":false,"options":[{"label":"","value":"","type":"str"}],"payload":"","topic":"","topicType":"str","className":"","x":540,"y":220,"wires":[["498fde9063a6124a","847811fc15df1e67"]]},{"id":"847811fc15df1e67","type":"function","z":"be36ae0c358ba0e7","name":"function 58","func":"var i=msg.payload;\nvar rawdata = flow.get(\"temp\");\nvar array=[];\narray[0] = {\n Name : rawdata[i].Name,\n TEMP : rawdata[i].TEMP,\n HUMD : rawdata[i].HUMD,\n PRES : rawdata[i].PRES,\n Weather:rawdata[i].Weather   \n}\n//array[1] = {\n// Name : rawdata[i].Name,\n// TEMP : rawdata[i].TEMP,\n// HUMD : rawdata[i].HUMD,\n// PRES : rawdata[i].PRES,\n// Weather:rawdata[i].Weather   \n//}\n//msg.payload=rawdata[i];\nmsg.payload=array;\n\nreturn msg;\n\n\n//flow.set('YourVariable', value);    // to store a variable (YourVariable)\n//var x = flow.get('YourVariable'); //to retrieve a variable (YourVariable)","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":550,"y":280,"wires":[["e72573bb57f3c97f","2c81b478206b9837"]]},{"id":"e72573bb57f3c97f","type":"debug","z":"be36ae0c358ba0e7","name":"debug 147","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":770,"y":260,"wires":[]},{"id":"2c81b478206b9837","type":"ui_table","z":"be36ae0c358ba0e7","group":"4afff518723dbafb","name":"","order":37,"width":"0","height":"0","columns":[],"outputs":0,"cts":false,"x":750,"y":300,"wires":[]},{"id":"b8630066.5d446","type":"ui_group","name":"選定地點的天候狀況","tab":"f9ac9e91.20e588","order":1,"disp":true,"width":"12","collapse":false,"className":""},{"id":"4afff518723dbafb","type":"ui_group","name":"天候狀況","tab":"f9ac9e91.20e588","order":2,"disp":true,"width":"10","collapse":false,"className":""},{"id":"f9ac9e91.20e588","type":"ui_tab","name":"Table Examples","icon":"dashboard","order":1,"disabled":false,"hidden":false}]

2023年7月28日 星期五

實驗 ESP32 + MFRC522 RFID + SQLite + Node-RED 新增模式 自動比對模式

實驗 ESP32 + MFRC522 RFID + SQLite + Node-RED 新增模式 自動比對模式





 




#include <WiFi.h>
#include <PubSubClient.h>
#include <SPI.h>
#include <MFRC522.h>
// GPIO 2 D1
#define LED 2
/* Wiring RFID RC522 module
=============================================================================
GND     = GND   3.3V    = 3.3V
The following table shows the typical pin layout used:
 *             MFRC522      ESP32     Arduino       Arduino   Arduino    Arduino          Arduino
 *             Reader/PCD             Uno/101       Mega      Nano v3    Leonardo/Micro   Pro Micro
 * Signal      Pin          Pin       Pin           Pin       Pin        Pin              Pin
 * -----------------------------------------------------------------------------------------
 * RST/Reset   RST          GPIO27    9             5         D9         RESET/ICSP-5     RST
 * SPI SS      SDA(SS)      GPIO5     10            53        D10        10               10
 * SPI MOSI    MOSI         GPIO23    11 / ICSP-4   51        D11        ICSP-4           16
 * SPI MISO    MISO         GPIO19    12 / ICSP-1   50        D12        ICSP-1           14
 * SPI SCK     SCK          GPIO18    13 / ICSP-3   52        D13        ICSP-3           15
 *
[1] (1, 2) Configurable, typically defined as RST_PIN in sketch/program.
[2] (1, 2) Configurable, typically defined as SS_PIN in sketch/program.
[3] The SDA pin might be labeled SS on some/older MFRC522 boards
=============================================================================
*/

#define SS_PIN  5  // ESP32 pin GIOP5
#define RST_PIN 27 // ESP32 pin GIOP27


// WiFi
const char *ssid = "alex9ufo"; // Enter your Wi-Fi name
const char *password = "alex9981";  // Enter Wi-Fi password

// MQTT Broker
const char *mqtt_broker = "broker.mqtt-dashboard.com";
const char *topic1 = "alex9ufo/esp32/led";
const char *topic2 = "alex9ufo/esp32/RFID";
const char *topic = "alex9ufo/esp32/Starting";
const char *topic3 = "alex9ufo/esp32/led_status";



const char *mqtt_username = "alex9ufo";
const char *mqtt_password = "public";
const int mqtt_port = 1883;

bool ledState = false;
bool atwork = false;

WiFiClient espClient;
PubSubClient client(espClient);
MFRC522 rfid(SS_PIN, RST_PIN); // Create MFRC522 instance

long lastMsg = 0;
long lastMsg1= 0;

char msg[50];
String json = "";
bool Flash = false;  //true
bool Timer = false;  //true
bool Send = false;  //true
int Count= 0;

char jsonChar1[100];  //client.publish("alex9ufo/Esp32/RFID"
//===========================================================
String printHex(byte *buffer, byte bufferSize) {
      String id = "";
      for (byte i = 0; i < bufferSize; i++) {
        id += buffer[i] < 0x10 ? "0" : "";
        id += String(buffer[i], HEX);
      }
      return id;
    }
//===========================================================
void setup_wifi() {
  delay(10);
  // We start by connecting to a WiFi network
  Serial.println();
  Serial.print("Connecting to ");
  Serial.println(ssid);

  WiFi.begin(ssid, password);

  while (WiFi.status() != WL_CONNECTED) {
    delay(500);
    Serial.print(".");
  }

  Serial.println("");
  Serial.println("WiFi connected");
  Serial.println("IP address: ");
  Serial.println(WiFi.localIP());
}    
//===========================================================
void setup() {
    // Set software serial baud to 115200;
    Serial.begin(115200);
    delay(1000); // Delay for stability
    //======================================================
    // Connecting to a WiFi network
    setup_wifi();
    // Setting LED pin as output
    pinMode(LED, OUTPUT);
    digitalWrite(LED, LOW);  // Turn off the LED initially
    //======================================================
    SPI.begin();           // Init SPI bus
    rfid.PCD_Init();    // Init MFRC522

    Serial.println(F("Ready!"));
    Serial.println(F("======================================================"));
    Serial.println("Tap an RFID/NFC tag on the RFID-RC522 reader");


    // Connecting to an MQTT broker
    client.setServer(mqtt_broker, mqtt_port);
    client.setCallback(callback);
    while (!client.connected()) {
        String client_id = "esp32-client-";
        client_id += String(WiFi.macAddress());
        Serial.printf("The client %s connects to the public MQTT broker\n", client_id.c_str());
        if (client.connect(client_id.c_str(), mqtt_username, mqtt_password)) {
            Serial.println("Public HiveMQ MQTT broker (broker.mqtt-dashboard.com) connected");
        } else {
            Serial.print("Failed with state ");
            Serial.print(client.state());
            delay(2000);
        }
    }

    // Publish and subscribe
    client.subscribe(topic1);
    client.publish(topic,"ESP32 at work");
}
//===========================================================
void callback(char *topic, byte *payload, unsigned int length) {
    Serial.print("Message arrived in topic: ");
    Serial.println(topic);
    Serial.print("Message: ");
    String message;
    for (int i = 0; i < length; i++) {
        message += (char) payload[i];  // Convert *byte to string
    }
    Serial.print(message);
    if (message == "on" && !ledState) {
        digitalWrite(LED, HIGH);  // Turn on the LED
        ledState = true;  //ledState = ture HIGH
        Flash = false;
        Timer = false;
        json ="ON";
        Send = true ;
    }
    if (message == "off" && ledState) {
        digitalWrite(LED, LOW); // Turn off the LED
        ledState = false; //ledState = false LOW
        Flash = false;
        Timer = false;
        json ="OFF";
        Send = true ;

    }
    if (message == "flash" ) {
        digitalWrite(LED, LOW); // Turn off the LED
        Flash = true;
        Timer = false;
        json ="FLASH";
        Send = true ;        

    }
    if (message == "timer" ) {
        digitalWrite(LED, LOW); // Turn off the LED
        Flash = false;
        Timer = true;
        json ="TIMER";
        Send = true ;
        Count= 11;
    }

    if (message == "toggle" ) {
        digitalWrite(LED, !digitalRead(LED));   // Turn the LED toggle
        if (digitalRead(LED))
            ledState = true;
        else
            ledState = false;
       
        Flash = false;
        Timer = false;
        json ="TOGGLE";
        Send = true ;        
    }

    Serial.println();
    Serial.println("-----------------------");
}
//===========================================================
void loop() {
    client.loop();
    delay(100); // Delay for a short period in each loop iteration
    if (Flash){
        digitalWrite(LED, !digitalRead(LED));
        delay(500);
        if (digitalRead(LED))
            ledState = true;
        else
            ledState = false;
    } //(Flash)
       
    if (Timer) {
        digitalWrite(LED, HIGH);
        delay(500);
        if (digitalRead(LED))
            ledState = true;
        else
            ledState = false;

        Count=Count-1;
        if (Count == 0 ){
            Timer=false;
            digitalWrite(LED, LOW);
            ledState = false;
        }
    } //(Timer)
   
    if (client.connected()) {        
        if (Send) {
          // Convert JSON string to character array
          json.toCharArray(jsonChar1, json.length()+1);
          Serial.print("Publish message: ");
          Serial.println(json);
          // Publish JSON character array to MQTT topic
          client.publish(topic3,jsonChar1);
        }
        Send = false;    
    }

    long now = millis();
    long now1 = millis();
    if (now1 - lastMsg1 > 80000) {
        lastMsg1 = now1;
        if (atwork) {
            client.publish(topic,"ESP32 at work");
            atwork=false;
        } else {
            client.publish(topic,"             ");
            atwork=true;
        }
     }
     
    if (now - lastMsg > 5000) {
      Serial.println("Reading RFIF card");
      lastMsg = now;
      if (rfid.PICC_IsNewCardPresent()) { // new tag is available
        if (rfid.PICC_ReadCardSerial()) { // NUID has been readed
          MFRC522::PICC_Type piccType = rfid.PICC_GetType(rfid.uid.sak);
          Serial.print("RFID/NFC Tag Type: ");
          Serial.println(rfid.PICC_GetTypeName(piccType));

          // print UID in Serial Monitor in the hex format
          Serial.print("UID:");
          for (int i = 0; i < rfid.uid.size; i++) {
            Serial.print(rfid.uid.uidByte[i] < 0x10 ? " 0" : " ");
            Serial.print(rfid.uid.uidByte[i], HEX);
          }
          Serial.println();

          String json = printHex(rfid.uid.uidByte, rfid.uid.size);
          // Convert JSON string to character array
          json.toCharArray(jsonChar1, json.length()+1);
   
          if  (client.connected()) {
            Serial.print("Publish message: ");
            Serial.println(json);
            // Publish JSON character array to MQTT topic
            client.publish(topic2,jsonChar1);
          }    
          rfid.PICC_HaltA(); // halt PICC
          rfid.PCD_StopCrypto1(); // stop encryption on PCD
         
         
        }
      }
       
  }

}
//===========================================================

[{"id":"706b6e9b757aebb8","type":"ui_button","z":"e00ab2f59ea60e48","name":"","group":"85b1efd8d6d7c0dd","order":27,"width":3,"height":1,"passthru":false,"label":"建立資料庫","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"建立資料庫","payloadType":"str","topic":"topic","topicType":"msg","x":90,"y":420,"wires":[["67f9addd869291c7","2b2cdf27a461f498"]]},{"id":"2103b06fa10ff814","type":"ui_button","z":"e00ab2f59ea60e48","name":"","group":"85b1efd8d6d7c0dd","order":30,"width":5,"height":2,"passthru":false,"label":"檢視資料庫資料","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"檢視資料","payloadType":"str","topic":"topic","topicType":"msg","x":100,"y":740,"wires":[["476e761995ad05a1","46fe8b21023dbd8f"]]},{"id":"57e3c58826303f74","type":"ui_button","z":"e00ab2f59ea60e48","name":"","group":"85b1efd8d6d7c0dd","order":25,"width":3,"height":1,"passthru":false,"label":"刪除所有資料","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"刪除所有資料","payloadType":"str","topic":"topic","topicType":"msg","x":100,"y":980,"wires":[["9dc987951e71577d","4886988bd55a3ed0"]]},{"id":"68645501e5220a45","type":"sqlite","z":"e00ab2f59ea60e48","mydb":"dd7f8134e015a1cb","sqlquery":"msg.topic","sql":"","name":"My_RFID_database","x":540,"y":420,"wires":[["bf962fae1f621539","476e761995ad05a1"]]},{"id":"67f9addd869291c7","type":"function","z":"e00ab2f59ea60e48","name":"CREATE DATABASE","func":"\n\n//CREATE TABLE \"RFIDtable\" (\n//\t\"id\"\tINT NOT NULL,\n//  \"uidname\"  TEXT,\n//  \"currentdate\" DATE, \n//  \"currenttime\" TIME\n//\tPRIMARY KEY(\"id\")\n//);\nmsg.topic = \"CREATE TABLE RFIDtable(id INTEGER PRIMARY KEY AUTOINCREMENT, uidname TEXT, currentdate DATE, currenttime TIME)\";\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":300,"y":420,"wires":[["68645501e5220a45"]]},{"id":"bf962fae1f621539","type":"debug","z":"e00ab2f59ea60e48","name":"debug","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":790,"y":420,"wires":[]},{"id":"ec1d54d7d41770f1","type":"function","z":"e00ab2f59ea60e48","name":"INSERT","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();     //秒\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}\nvar var_date = yyyy+'/'+MM+'/'+dd;\nvar var_time = h+':'+m+':'+s;\n\nvar myRFID = flow.get('uid_temp');\n\n\nmsg.topic = \"INSERT INTO RFIDtable ( uidname , currentdate, currenttime ) VALUES ($myRFID,  $var_date ,  $var_time ) \" ;\nmsg.payload = [myRFID, var_date , var_time ]\nreturn msg;\n\n//CREATE TABLE \"RFIDtable\" (\n//\t\"id\"\tINT NOT NULL,\n//  \"uidname\"  TEXT,\n//  \"currentdate\" DATE, \n//  \"currenttime\" TIME\n//\tPRIMARY KEY(\"id\")\n//);","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":320,"y":460,"wires":[["9f604fc4cad58ee9","68645501e5220a45"]]},{"id":"9dc987951e71577d","type":"function","z":"e00ab2f59ea60e48","name":"刪除所有資料","func":"//DELETE from RFIDtable\nmsg.topic = \"DELETE from RFIDtable\";\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":320,"y":980,"wires":[["11c361088aa44a65"]]},{"id":"11c361088aa44a65","type":"sqlite","z":"e00ab2f59ea60e48","mydb":"dd7f8134e015a1cb","sqlquery":"msg.topic","sql":"","name":"My_RFID_database","x":540,"y":980,"wires":[["1420491c3111a4b9"]]},{"id":"5d4b1f1cbc6fce11","type":"ui_button","z":"e00ab2f59ea60e48","name":"","group":"85b1efd8d6d7c0dd","order":23,"width":3,"height":1,"passthru":false,"label":"刪除資料庫","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"刪除資料庫","payloadType":"str","topic":"topic","topicType":"msg","x":90,"y":1060,"wires":[["0015e46940ed050b","e2d7e93ee5805748"]]},{"id":"0015e46940ed050b","type":"function","z":"e00ab2f59ea60e48","name":"刪除資料庫","func":"//DROP TABLE RFIDtable\nmsg.topic = \"DROP TABLE RFIDtable\";\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":310,"y":1060,"wires":[["11c361088aa44a65"]]},{"id":"1d9ee8b4e6190689","type":"ui_template","z":"e00ab2f59ea60e48","group":"85b1efd8d6d7c0dd","name":"","order":13,"width":12,"height":8,"format":"<table style=\"width:100%\">\n  <tr>\n    <th>idex</th> \n    <th>database_id</th>\n    <th>UID </th>\n    <th>Date</th>\n    <th>Times</th>\n  </tr>\n  \n  <tr ng-repeat=\"x in msg.payload | limitTo:50\">\n    <td>{{$index}}</td>\n    <td>{{msg.payload[$index].id}}</td>\n    <td>{{msg.payload[$index].uidname}}</td>\n    <td>{{msg.payload[$index].currentdate}}</td>\n    <td>{{msg.payload[$index].currenttime}}</td>\n  </tr>\n</table>","storeOutMessages":true,"fwdInMessages":true,"resendOnRefresh":true,"templateScope":"local","className":"","x":960,"y":1140,"wires":[[]]},{"id":"1420491c3111a4b9","type":"debug","z":"e00ab2f59ea60e48","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":730,"y":980,"wires":[]},{"id":"476e761995ad05a1","type":"function","z":"e00ab2f59ea60e48","name":"檢視資料","func":"\n//CREATE TABLE \"RFIDtable\" (\n//\t\"id\"\tINT NOT NULL,\n//  \"uidname\"  TEXT,\n//  \"currentdate\" DATE, \n//  \"currenttime\" TIME\n//\tPRIMARY KEY(\"id\")\n//);\n\n//SELECT * FROM RFIDtable ORDER BY  id DESC LIMIT 50;\n\nmsg.topic = \"SELECT * FROM RFIDtable ORDER BY id DESC LIMIT 50\";\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":560,"y":740,"wires":[["ee4f554f4ec77f7e"]]},{"id":"ee4f554f4ec77f7e","type":"sqlite","z":"e00ab2f59ea60e48","mydb":"dd7f8134e015a1cb","sqlquery":"msg.topic","sql":"","name":"My_RFID_database","x":780.9005737304688,"y":873.5454711914062,"wires":[["1d9ee8b4e6190689"]]},{"id":"3a85e89d3ef728a4","type":"function","z":"e00ab2f59ea60e48","name":"SELECT ALL","func":"var del_idtemp=msg.payload;\nflow.set(\"idtemp\", del_idtemp);\n\nmsg.topic = \"SELECT * FROM RFIDtable \";\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":310,"y":900,"wires":[["11c361088aa44a65","6f162682a89dbd5d"]]},{"id":"3c17f4cb6a2d344c","type":"function","z":"e00ab2f59ea60e48","name":"確認 刪除","func":"var del_id = flow.get(\"idtemp\");\n\n\nmsg.topic = \"DELETE FROM RFIDtable WHERE id= ($del_id) \" ;\nmsg.payload = [del_id]\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":300,"y":800,"wires":[["caaf5693e66f95e0"]]},{"id":"2fde96e5cbd18edf","type":"ui_numeric","z":"e00ab2f59ea60e48","name":"","label":"刪除的database_id","tooltip":"","group":"85b1efd8d6d7c0dd","order":26,"width":3,"height":1,"wrap":false,"passthru":true,"topic":"topic","topicType":"msg","format":"{{value}}","min":"1","max":"100","step":1,"className":"","x":110,"y":900,"wires":[["3a85e89d3ef728a4","4886988bd55a3ed0"]]},{"id":"caaf5693e66f95e0","type":"sqlite","z":"e00ab2f59ea60e48","mydb":"dd7f8134e015a1cb","sqlquery":"msg.topic","sql":"DELETE FROM dhtreadings WHERE id =  VALUES ($theid)","name":"My_RFID_database","x":500,"y":800,"wires":[["476e761995ad05a1"]]},{"id":"152668e1be54de6a","type":"ui_button","z":"e00ab2f59ea60e48","name":"","group":"85b1efd8d6d7c0dd","order":24,"width":3,"height":1,"passthru":false,"label":"刪除一筆資料","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"刪除一筆資料","payloadType":"str","topic":"topic","topicType":"msg","x":100,"y":800,"wires":[["3c17f4cb6a2d344c","46fe8b21023dbd8f"]]},{"id":"6f162682a89dbd5d","type":"debug","z":"e00ab2f59ea60e48","name":"debug 122","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":510,"y":900,"wires":[]},{"id":"03942cf08ba0659d","type":"mqtt in","z":"e00ab2f59ea60e48","name":"新增 RFID","topic":"alex9ufo/esp32/RFID","qos":"2","datatype":"auto-detect","broker":"841df58d.ee5e98","nl":false,"rap":true,"rh":0,"inputs":0,"x":80,"y":460,"wires":[["80332a8adf5e0a72"]]},{"id":"9f604fc4cad58ee9","type":"debug","z":"e00ab2f59ea60e48","name":"debug 123","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":510,"y":460,"wires":[]},{"id":"d874433685ecdedf","type":"ui_text_input","z":"e00ab2f59ea60e48","name":"","label":"新增一筆資料","tooltip":"","group":"85b1efd8d6d7c0dd","order":28,"width":3,"height":1,"passthru":true,"mode":"text","delay":300,"topic":"topic","sendOnBlur":true,"className":"","topicType":"msg","x":320,"y":540,"wires":[[]]},{"id":"d8cb65223db72c0f","type":"ui_audio","z":"e00ab2f59ea60e48","name":"","group":"85b1efd8d6d7c0dd","voice":"Microsoft Hanhan - Chinese (Traditional, Taiwan)","always":true,"x":500,"y":620,"wires":[]},{"id":"e3c8ef8b8272c8a1","type":"function","z":"e00ab2f59ea60e48","name":"function ","func":"var temp= msg.payload;\nmsg.payload= \"新增一筆資料\" + temp;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":300,"y":580,"wires":[["d8cb65223db72c0f","d41ee8d192e9b212"]]},{"id":"46fe8b21023dbd8f","type":"ui_audio","z":"e00ab2f59ea60e48","name":"","group":"85b1efd8d6d7c0dd","voice":"Microsoft Hanhan - Chinese (Traditional, Taiwan)","always":true,"x":300,"y":760,"wires":[]},{"id":"2b2cdf27a461f498","type":"ui_audio","z":"e00ab2f59ea60e48","name":"","group":"85b1efd8d6d7c0dd","voice":"Microsoft Hanhan - Chinese (Traditional, Taiwan)","always":true,"x":260,"y":380,"wires":[]},{"id":"fe992f6b585971a6","type":"function","z":"e00ab2f59ea60e48","name":"function 43","func":"//SELECT trackid,name FROM \ttracks WHERE name LIKE 'Wild%'\n//Query\n//CREATE TABLE \"RFIDtable\" (\n//\t\"id\"\tINT NOT NULL,\n//  \"uidname\"  TEXT,\n//  \"currentdate\" DATE, \n//  \"currenttime\" TIME\n//\tPRIMARY KEY(\"id\")\n//);\n//msg.topic = \"DELETE FROM RFIDtable WHERE id= ($del_id) \" ;\n\nvar query_uid = flow.get(\"uidtemp\");\nmsg.topic = \"SELECT id,uidname , currentdate,currenttime FROM RFIDtable WHERE uidname LIKE ($query_uid) \";\nmsg.payload = [query_uid]\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":330,"y":1140,"wires":[["5d39ee95b604a2ab","8b19fdfc7527fb1c"]]},{"id":"9dd72eea835f79da","type":"ui_button","z":"e00ab2f59ea60e48","name":"","group":"85b1efd8d6d7c0dd","order":17,"width":3,"height":1,"passthru":false,"label":"比對資料庫","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"比對資料庫","payloadType":"str","topic":"topic","topicType":"msg","x":90,"y":1140,"wires":[["fe992f6b585971a6","e2d7e93ee5805748","68f3d055ed3502fc"]]},{"id":"5d39ee95b604a2ab","type":"sqlite","z":"e00ab2f59ea60e48","mydb":"dd7f8134e015a1cb","sqlquery":"msg.topic","sql":"","name":"My_RFID_database","x":720,"y":1140,"wires":[["1d9ee8b4e6190689"]]},{"id":"6acf78a7006769bf","type":"function","z":"e00ab2f59ea60e48","name":"SELECT ALL","func":"var query_uidtemp=msg.payload;\nflow.set(\"uidtemp\", query_uidtemp);\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":490,"y":680,"wires":[["41bf094d339beef6","2eb3dcef5af1360a","9c0209342f6a354d"]]},{"id":"f9ba799cb07e77a8","type":"ui_text_input","z":"e00ab2f59ea60e48","name":"","label":"手動查詢資料的uidname","tooltip":"","group":"85b1efd8d6d7c0dd","order":19,"width":3,"height":1,"passthru":true,"mode":"text","delay":300,"topic":"topic","sendOnBlur":true,"className":"","topicType":"msg","x":130,"y":680,"wires":[["6acf78a7006769bf"]]},{"id":"9463c7ddf98042e2","type":"function","z":"e00ab2f59ea60e48","name":"function ","func":"var query=msg.payload;\n\nif (query===true)\n    msg.payload='Query';\nelse\n    msg.payload='NotQuery';\n\nflow.set(\"query_temp\", msg.payload);\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":520,"y":1300,"wires":[["e31e80a35e801f90","96c8124f4b81f99b"]]},{"id":"4ce345d0b714527d","type":"ui_switch","z":"e00ab2f59ea60e48","name":"","label":"新增模式  /自動比對模式 ","tooltip":"","group":"85b1efd8d6d7c0dd","order":16,"width":"6","height":1,"passthru":true,"decouple":"false","topic":"topic","topicType":"msg","style":"","onvalue":"true","onvalueType":"bool","onicon":"","oncolor":"","offvalue":"false","offvalueType":"bool","officon":"","offcolor":"","animate":false,"className":"","x":310,"y":1300,"wires":[["9463c7ddf98042e2"]]},{"id":"e31e80a35e801f90","type":"debug","z":"e00ab2f59ea60e48","name":"debug 124","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":670,"y":1260,"wires":[]},{"id":"0f4d4ba0dfe9870a","type":"switch","z":"e00ab2f59ea60e48","name":"","property":"payload","propertyType":"msg","rules":[{"t":"eq","v":"Create","vt":"str"},{"t":"eq","v":"Query","vt":"str"}],"checkall":"true","repair":false,"outputs":2,"x":110,"y":600,"wires":[["ec1d54d7d41770f1","d874433685ecdedf","e3c8ef8b8272c8a1","45d2e33fcc4622ee"],["bbdf90e0d7d6353e","d874433685ecdedf","97a43889c254084c"]]},{"id":"80332a8adf5e0a72","type":"function","z":"e00ab2f59ea60e48","name":"function ","func":"var query = flow.get(\"query_temp\");\n\nflow.set(\"uid_temp\", msg.payload);\n\nif (query==='NotQuery')\n    msg.payload='Create'\nelse\n    msg.payload='Query'\n    \nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":100,"y":520,"wires":[["0f4d4ba0dfe9870a"]]},{"id":"45d2e33fcc4622ee","type":"debug","z":"e00ab2f59ea60e48","name":"debug 125","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":310,"y":500,"wires":[]},{"id":"96c8124f4b81f99b","type":"ui_audio","z":"e00ab2f59ea60e48","name":"","group":"fe68da70e8d7a3c8","voice":"Microsoft Hanhan - Chinese (Traditional, Taiwan)","always":true,"x":680,"y":1300,"wires":[]},{"id":"8b19fdfc7527fb1c","type":"debug","z":"e00ab2f59ea60e48","name":"debug ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":450,"y":1180,"wires":[]},{"id":"41bf094d339beef6","type":"debug","z":"e00ab2f59ea60e48","name":"debug 127","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":730,"y":660,"wires":[]},{"id":"bbdf90e0d7d6353e","type":"function","z":"e00ab2f59ea60e48","name":"function","func":"\nvar a= flow.get(\"uid_temp\");\nmsg.payload=a;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":300,"y":620,"wires":[["6acf78a7006769bf","d8cb65223db72c0f"]]},{"id":"2eb3dcef5af1360a","type":"ui_text","z":"e00ab2f59ea60e48","group":"85b1efd8d6d7c0dd","order":31,"width":6,"height":1,"name":"","label":"自動感應RFID查詢的uid","format":"{{msg.payload}}","layout":"row-left","className":"","x":750,"y":580,"wires":[]},{"id":"9c0209342f6a354d","type":"link out","z":"e00ab2f59ea60e48","name":"link out 10","mode":"link","links":[],"x":675,"y":700,"wires":[]},{"id":"02b54d1571610eec","type":"link in","z":"e00ab2f59ea60e48","name":"link in 12","links":["97a43889c254084c","f316a7347a7ff85a"],"x":215,"y":1180,"wires":[["fe992f6b585971a6"]]},{"id":"d2c8ad4c6ece28af","type":"function","z":"e00ab2f59ea60e48","name":"function ","func":"//CREATE TABLE \"RFIDtable\" (\n//\t\"id\"\tINT NOT NULL,\n//  \"uidname\"  TEXT,\n//  \"currentdate\" DATE, \n//  \"currenttime\" TIME\n//\tPRIMARY KEY(\"id\")\n//);\n\n//msg.topic = \"INSERT INTO RFIDtable ( uidname , currentdate, currenttime ) VALUES ($myRFID,  $var_date ,  $var_time ) \" ;\n//msg.payload = [myRFID, var_date , var_time ]\n//return msg;\n\n\nvar tmp=msg.payload;\nmsg.topic = \"select count( * ) as 總共有幾筆資料 from RFIDtable where uidname=($tmp)\";\nmsg.payload=[tmp];\nreturn msg;\n\n// select count( * ) as 總共有幾筆資料 from Customers where address='Japan'","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":440,"y":1400,"wires":[["c56638cf7f3edd8f","654f3988cdcc3be6"]]},{"id":"c56638cf7f3edd8f","type":"sqlite","z":"e00ab2f59ea60e48","mydb":"dd7f8134e015a1cb","sqlquery":"msg.topic","sql":"","name":"My_RFID_database","x":640,"y":1400,"wires":[["7c76c34681ca43c6"]]},{"id":"7c76c34681ca43c6","type":"function","z":"e00ab2f59ea60e48","name":"function ","func":"var num=msg.payload[0].總共有幾筆資料;\nmsg.payload=num;\nreturn msg; \n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":760,"y":1440,"wires":[["4666a044c6ade98a","7b7d10207fb5be1c","5d88cef1f333a38a"]]},{"id":"8d339037aa892ef5","type":"ui_text","z":"e00ab2f59ea60e48","group":"85b1efd8d6d7c0dd","order":21,"width":4,"height":1,"name":"","label":"查詢結果","format":"<font face='arial'><font size=3><font color={{fcolor}}>{{msg.payload}}","layout":"row-left","className":"","x":1120,"y":1460,"wires":[]},{"id":"5bde43f098b82330","type":"ui_text","z":"e00ab2f59ea60e48","group":"85b1efd8d6d7c0dd","order":20,"width":4,"height":1,"name":"","label":"查詢結果:筆數","format":"<font face='arial'><font size=6><font color={{fcolor}}>{{msg.payload}}","layout":"row-left","className":"","x":1140,"y":1420,"wires":[]},{"id":"cbc9d4f59cf85636","type":"mqtt in","z":"e00ab2f59ea60e48","name":"","topic":"alex9ufo/esp32/Starting","qos":"2","datatype":"auto-detect","broker":"841df58d.ee5e98","nl":false,"rap":true,"rh":0,"inputs":0,"x":120,"y":1540,"wires":[["e395422ed7b58f1f","b58074554cf5347b","21d604f1ead7e925"]]},{"id":"e395422ed7b58f1f","type":"ui_audio","z":"e00ab2f59ea60e48","name":"","group":"85b1efd8d6d7c0dd","voice":"Microsoft Hanhan - Chinese (Traditional, Taiwan)","always":true,"x":360,"y":1580,"wires":[]},{"id":"b58074554cf5347b","type":"debug","z":"e00ab2f59ea60e48","name":"debug 128","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":370,"y":1540,"wires":[]},{"id":"21d604f1ead7e925","type":"change","z":"e00ab2f59ea60e48","name":"","rules":[{"t":"set","p":"fcolor","pt":"msg","to":"red","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":380,"y":1500,"wires":[["39b3d03a5d07d92b"]]},{"id":"39b3d03a5d07d92b","type":"ui_text","z":"e00ab2f59ea60e48","group":"85b1efd8d6d7c0dd","order":22,"width":4,"height":1,"name":"","label":"","format":"<font face='arial'><font size=4><font color={{fcolor}}>{{msg.payload}}","layout":"row-left","className":"","x":550,"y":1500,"wires":[]},{"id":"654f3988cdcc3be6","type":"debug","z":"e00ab2f59ea60e48","name":"debug","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":590,"y":1360,"wires":[]},{"id":"4666a044c6ade98a","type":"change","z":"e00ab2f59ea60e48","name":"","rules":[{"t":"set","p":"fcolor","pt":"msg","to":"red","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":960,"y":1420,"wires":[["5bde43f098b82330","c87837829cdb2764"]]},{"id":"3966baf94ebdfbe9","type":"change","z":"e00ab2f59ea60e48","name":"","rules":[{"t":"set","p":"fcolor","pt":"msg","to":"red","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":960,"y":1460,"wires":[["8d339037aa892ef5"]]},{"id":"68f3d055ed3502fc","type":"function","z":"e00ab2f59ea60e48","name":"function","func":"\nvar a= flow.get(\"uidtemp\");\nmsg.payload=a;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":280,"y":1400,"wires":[["d2c8ad4c6ece28af"]]},{"id":"97a43889c254084c","type":"link out","z":"e00ab2f59ea60e48","name":"link out 11","mode":"link","links":["fcae8d564a286774","02b54d1571610eec"],"x":195,"y":640,"wires":[]},{"id":"fcae8d564a286774","type":"link in","z":"e00ab2f59ea60e48","name":"link in 13","links":["97a43889c254084c","f316a7347a7ff85a"],"x":155,"y":1360,"wires":[["68f3d055ed3502fc"]]},{"id":"d7c26606738a42a8","type":"inject","z":"e00ab2f59ea60e48","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":true,"onceDelay":"2","topic":"false","payload":"false","payloadType":"str","x":110,"y":1300,"wires":[["4ce345d0b714527d"]]},{"id":"a3e482a6dab94145","type":"ui_audio","z":"e00ab2f59ea60e48","name":"","group":"85b1efd8d6d7c0dd","voice":"Microsoft Hanhan - Chinese (Traditional, Taiwan)","always":true,"x":1120,"y":1500,"wires":[]},{"id":"c87837829cdb2764","type":"debug","z":"e00ab2f59ea60e48","name":"debug","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1110,"y":1380,"wires":[]},{"id":"0af0e51043722d4d","type":"delay","z":"e00ab2f59ea60e48","name":"","pauseType":"delay","timeout":"3","timeoutUnits":"seconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"allowrate":false,"outputs":1,"x":940,"y":1500,"wires":[["a3e482a6dab94145"]]},{"id":"7b7d10207fb5be1c","type":"function","z":"e00ab2f59ea60e48","name":"function ","func":"var n=msg.payload;\nif (n>0)\n    msg.payload='RFID符合';\nelse\n    msg.payload='RFID錯誤';\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":760,"y":1500,"wires":[["0af0e51043722d4d","3966baf94ebdfbe9"]]},{"id":"408e1cdc787670bc","type":"ui_button","z":"e00ab2f59ea60e48","name":"","group":"85b1efd8d6d7c0dd","order":6,"width":2,"height":1,"passthru":false,"label":"LED 開","tooltip":"","color":"white","bgcolor":"","className":"","icon":"fa-circle","payload":"on","payloadType":"str","topic":"","topicType":"str","x":80,"y":60,"wires":[["12e722168f45dfbd","5ea3c8758d0c7e22","462f5e9a0ccb3cc4"]]},{"id":"2f29aeee52ae7b68","type":"ui_button","z":"e00ab2f59ea60e48","name":"","group":"85b1efd8d6d7c0dd","order":7,"width":2,"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":100,"wires":[["12e722168f45dfbd","5ea3c8758d0c7e22","462f5e9a0ccb3cc4"]]},{"id":"12e722168f45dfbd","type":"mqtt out","z":"e00ab2f59ea60e48","name":"Control LED","topic":"alex9ufo/esp32/led","qos":"1","retain":"true","respTopic":"","contentType":"","userProps":"","correl":"","expiry":"","broker":"70940176.2b2d3","x":330,"y":60,"wires":[]},{"id":"fdffa055cd552c2e","type":"comment","z":"e00ab2f59ea60e48","name":"Node-Red publish 到 HiveMQ Broker  , ESP32訂閱","info":"將 alex9ufo/inTopic 到(publish)HiveMQ Broker \n給 Arduino 訂閱(Subscribe)","x":210,"y":20,"wires":[]},{"id":"5ea3c8758d0c7e22","type":"ui_audio","z":"e00ab2f59ea60e48","name":"","group":"85b1efd8d6d7c0dd","voice":"zh-TW","always":true,"x":320,"y":200,"wires":[]},{"id":"462f5e9a0ccb3cc4","type":"ui_text","z":"e00ab2f59ea60e48","group":"85b1efd8d6d7c0dd","order":14,"width":0,"height":0,"name":"","label":"Node-RED發行到MQTT的資料","format":"{{msg.payload}}","layout":"row-left","className":"","x":390,"y":140,"wires":[]},{"id":"1af992d593a7d9b3","type":"ui_audio","z":"e00ab2f59ea60e48","name":"","group":"85b1efd8d6d7c0dd","voice":"en-US","always":"","x":560,"y":300,"wires":[]},{"id":"681c30b4a71aab24","type":"mqtt in","z":"e00ab2f59ea60e48","name":"LED status ","topic":"alex9ufo/esp32/led_status","qos":"2","datatype":"utf8","broker":"841df58d.ee5e98","nl":false,"rap":true,"rh":0,"inputs":0,"x":80,"y":300,"wires":[["84006085b93c7fa5"]]},{"id":"0a435887a60c37d1","type":"comment","z":"e00ab2f59ea60e48","name":"Node-Red  subscribe HiveMQ Broker  , ESP32發行","info":"將 alex9ufo/inTopic 到(publish)HiveMQ Broker \n給 Arduino 訂閱(Subscribe)","x":200,"y":260,"wires":[]},{"id":"211c5c17a18eeca3","type":"ui_text","z":"e00ab2f59ea60e48","group":"85b1efd8d6d7c0dd","order":15,"width":0,"height":0,"name":"","label":"Node-RED 訂閱MQTT的資料","format":"{{msg.payload}}","layout":"row-left","className":"","x":480,"y":340,"wires":[]},{"id":"b941d45014c54970","type":"ui_button","z":"e00ab2f59ea60e48","name":"","group":"85b1efd8d6d7c0dd","order":8,"width":3,"height":1,"passthru":false,"label":"LED 閃爍","tooltip":"","color":"black","bgcolor":"","className":"","icon":"fa-circle-o","payload":"flash","payloadType":"str","topic":"","topicType":"str","x":80,"y":140,"wires":[["12e722168f45dfbd","462f5e9a0ccb3cc4","5ea3c8758d0c7e22"]]},{"id":"ca019f3e91991a28","type":"ui_button","z":"e00ab2f59ea60e48","name":"","group":"85b1efd8d6d7c0dd","order":9,"width":2,"height":1,"passthru":false,"label":"LED反向","tooltip":"","color":"black","bgcolor":"","className":"","icon":"fa-circle-o","payload":"toggle","payloadType":"str","topic":"","topicType":"str","x":80,"y":180,"wires":[["12e722168f45dfbd","462f5e9a0ccb3cc4","5ea3c8758d0c7e22"]]},{"id":"2a9ec4a94bc9a22d","type":"ui_button","z":"e00ab2f59ea60e48","name":"","group":"85b1efd8d6d7c0dd","order":10,"width":3,"height":1,"passthru":false,"label":"LED 定時5秒","tooltip":"","color":"black","bgcolor":"","className":"","icon":"fa-circle-o","payload":"timer","payloadType":"str","topic":"","topicType":"str","x":90,"y":220,"wires":[["12e722168f45dfbd","462f5e9a0ccb3cc4","5ea3c8758d0c7e22"]]},{"id":"84006085b93c7fa5","type":"function","z":"e00ab2f59ea60e48","name":"function 48","func":"msg.payload=\" ---ESP32回來資料---\" +msg.payload;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":250,"y":300,"wires":[["211c5c17a18eeca3","30e1509a90e5f6ea"]]},{"id":"30e1509a90e5f6ea","type":"delay","z":"e00ab2f59ea60e48","name":"","pauseType":"delay","timeout":"1","timeoutUnits":"seconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"allowrate":false,"outputs":1,"x":420,"y":300,"wires":[["1af992d593a7d9b3"]]},{"id":"d41ee8d192e9b212","type":"function","z":"e00ab2f59ea60e48","name":"function 49","func":"var myRFID = flow.get('uid_temp');\nmsg.payload=myRFID;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":510,"y":580,"wires":[["2eb3dcef5af1360a"]]},{"id":"e2d7e93ee5805748","type":"ui_audio","z":"e00ab2f59ea60e48","name":"","group":"85b1efd8d6d7c0dd","voice":"Microsoft Hanhan - Chinese (Traditional, Taiwan)","always":true,"x":300,"y":1100,"wires":[]},{"id":"4886988bd55a3ed0","type":"ui_audio","z":"e00ab2f59ea60e48","name":"","group":"85b1efd8d6d7c0dd","voice":"Microsoft Hanhan - Chinese (Traditional, Taiwan)","always":true,"x":300,"y":940,"wires":[]},{"id":"5d88cef1f333a38a","type":"debug","z":"e00ab2f59ea60e48","name":"debug 129","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":890,"y":1320,"wires":[]},{"id":"85b1efd8d6d7c0dd","type":"ui_group","name":"ESP32","tab":"eeb8e179.a47a4","order":2,"disp":true,"width":"13","collapse":false,"className":""},{"id":"dd7f8134e015a1cb","type":"sqlitedb","db":"C:\\Users\\User\\.node-red\\my_2023rfid.db","mode":"RWC"},{"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":"fe68da70e8d7a3c8","type":"ui_group","name":"2023 RFID Home","tab":"9c33f04edb058032","order":3,"disp":true,"width":"14","collapse":false,"className":""},{"id":"70940176.2b2d3","type":"mqtt-broker","name":"","broker":"broker.mqtt-dashboard.com","port":"1883","clientid":"","usetls":false,"compatmode":true,"keepalive":"15","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closePayload":"","willTopic":"","willQos":"0","willPayload":""},{"id":"eeb8e179.a47a4","type":"ui_tab","name":"2023 RFID Database","icon":"dashboard","disabled":false,"hidden":false},{"id":"9c33f04edb058032","type":"ui_tab","name":"2023-07 Home","icon":"dashboard","disabled":false,"hidden":false}]

2024_09 作業3 以Node-Red 為主

 2024_09 作業3  (以Node-Red 為主  Arduino 可能需要配合修改 ) Arduino 可能需要修改的部分 1)mqtt broker  2) 主題Topic (發行 接收) 3) WIFI ssid , password const char br...