alex9ufo 聰明人求知心切: 1月 2024

2024作業2-3 ESP32 +( RFID +LED) + (Node-Red & SQLite ) + Line Notify

(Arduino , Node-Red 程式中的 alex9ufo 全部改成自己的英文命名)

使用Node-Red 第一次一定要先建立資料庫不然看不到資料

資料庫的目錄位置查sqlite節點

每一個sqlite節點都要修改成自己的目錄, alex9ufo MQTT Topic都要修改成自己的命名

Node-Red程式

[{"id":"1df26672f44f85b4","type":"comment","z":"41b4f910767c89fe","name":"TABLE LEDSTATUS","info":"CREATE TABLE LEDSTATUS (\nid INTEGER,\nSTATUS TEXT,\nDate DATE,\nTime TIME,\nPRIMARY KEY (id)\n);","x":110,"y":40,"wires":[]},{"id":"2217429dcc495c1d","type":"function","z":"41b4f910767c89fe","name":"CREATE　DATABASE","func":"//CREATE TABLE LEDSTATUS (\n//id INTEGER,\n//STATUS TEXT,\n//Date DATE,\n//Time TIME,\n//PRIMARY KEY (id)\n//);\n//CREATE TABLE LEDSTATUS (id INTEGER,STATUS TEXT,Date DATE,Time TIME,PRIMARY KEY (id));\nmsg.topic = \"CREATE TABLE LEDSTATUS (id INTEGER,STATUS TEXT,Date DATE,Time TIME,PRIMARY KEY (id))\";\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":300,"y":120,"wires":[["87753bab743780b6"]]},{"id":"ce6821a4e3477f88","type":"ui_button","z":"41b4f910767c89fe","name":"","group":"e7c2632dca7fe97d","order":6,"width":3,"height":1,"passthru":false,"label":"(L)建立資料庫","tooltip":"","color":"","bgcolor":"green","className":"","icon":"","payload":"建立資料庫","payloadType":"str","topic":"topic","topicType":"msg","x":100,"y":120,"wires":[["2217429dcc495c1d","f0786a2cfb254c77"]]},{"id":"f0786a2cfb254c77","type":"ui_audio","z":"41b4f910767c89fe","name":"","group":"11d8514.a44dcaf","voice":"Microsoft Hanhan - Chinese (Traditional, Taiwan)","always":true,"x":215,"y":80,"wires":[],"l":false},{"id":"9367df3eade4d172","type":"ui_button","z":"41b4f910767c89fe","name":"","group":"0487352be5053bfa","order":1,"width":5,"height":1,"passthru":false,"label":"ON","tooltip":"","color":"","bgcolor":"green","className":"","icon":"","payload":"on","payloadType":"str","topic":"topic","topicType":"msg","x":70,"y":160,"wires":[["0ff8eda4c787fbee","c1471930d2d555c1","e827f1ca78ea8c25","9466ed42faee22e9"]]},{"id":"8caa06c44a50c269","type":"ui_button","z":"41b4f910767c89fe","name":"","group":"0487352be5053bfa","order":2,"width":5,"height":1,"passthru":false,"label":"OFF","tooltip":"","color":"","bgcolor":"green","className":"","icon":"","payload":"off","payloadType":"str","topic":"topic","topicType":"msg","x":70,"y":200,"wires":[["0ff8eda4c787fbee","c1471930d2d555c1","e827f1ca78ea8c25","9466ed42faee22e9"]]},{"id":"0cc9b571fcd74045","type":"ui_button","z":"41b4f910767c89fe","name":"","group":"0487352be5053bfa","order":3,"width":5,"height":1,"passthru":false,"label":"TOGGLE","tooltip":"","color":"","bgcolor":"green","className":"","icon":"","payload":"toggle","payloadType":"str","topic":"topic","topicType":"msg","x":80,"y":240,"wires":[["0ff8eda4c787fbee","c1471930d2d555c1","e827f1ca78ea8c25","9466ed42faee22e9"]]},{"id":"a72bb1c208932398","type":"ui_button","z":"41b4f910767c89fe","name":"","group":"0487352be5053bfa","order":4,"width":5,"height":1,"passthru":false,"label":"TIMER","tooltip":"","color":"","bgcolor":"green","className":"","icon":"","payload":"timer","payloadType":"str","topic":"topic","topicType":"msg","x":80,"y":280,"wires":[["0ff8eda4c787fbee","c1471930d2d555c1","e827f1ca78ea8c25","9466ed42faee22e9"]]},{"id":"ec04c0588a755752","type":"ui_button","z":"41b4f910767c89fe","name":"","group":"0487352be5053bfa","order":5,"width":5,"height":1,"passthru":false,"label":"FLASH","tooltip":"","color":"","bgcolor":"green","className":"","icon":"","payload":"flash","payloadType":"str","topic":"topic","topicType":"msg","x":80,"y":320,"wires":[["c1471930d2d555c1","0ff8eda4c787fbee","e827f1ca78ea8c25","9466ed42faee22e9"]]},{"id":"c1471930d2d555c1","type":"function","z":"41b4f910767c89fe","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 myLED = msg.payload;\n\n\nmsg.topic = \"INSERT INTO LEDSTATUS ( STATUS , Date , Time ) VALUES ($myLED, $var_date , $var_time ) \" ;\nmsg.payload = [myLED, var_date , var_time ]\nreturn msg;\n\n\n//INSERT INTO LEDSTATUS (\n//id INTEGER,\n//STATUS TEXT,\n//Date DATE,\n//Time TIME,\n//PRIMARY KEY (id)\n//);\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":280,"y":260,"wires":[["ba1700a943b1bd1b"]]},{"id":"0ff8eda4c787fbee","type":"ui_audio","z":"41b4f910767c89fe","name":"","group":"11d8514.a44dcaf","voice":"Microsoft Hanhan - Chinese (Traditional, Taiwan)","always":true,"x":235,"y":220,"wires":[],"l":false},{"id":"ba1700a943b1bd1b","type":"sqlite","z":"41b4f910767c89fe","mydb":"a4c15c0e38a27cca","sqlquery":"msg.topic","sql":"","name":"LED_STATUS","x":460,"y":260,"wires":[["9b1382a03c037549","c99597aa945f9ef9"]]},{"id":"6e907e9ca9da5039","type":"debug","z":"41b4f910767c89fe","name":"debug ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":670,"y":120,"wires":[]},{"id":"9b1382a03c037549","type":"debug","z":"41b4f910767c89fe","name":"debug ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":670,"y":260,"wires":[]},{"id":"431f902cc11d9e41","type":"ui_button","z":"41b4f910767c89fe","name":"","group":"e7c2632dca7fe97d","order":8,"width":6,"height":1,"passthru":false,"label":"(L)檢視資料庫資料","tooltip":"","color":"","bgcolor":"green","className":"","icon":"","payload":"檢視資料","payloadType":"str","topic":"topic","topicType":"msg","x":110,"y":420,"wires":[["c99597aa945f9ef9","6195eb71c46ac3fa"]]},{"id":"c99597aa945f9ef9","type":"function","z":"41b4f910767c89fe","name":"檢視資料","func":"//INSERT INTO LEDSTATUS (\n//id INTEGER,\n//STATUS TEXT,\n//Date DATE,\n//Time TIME,\n//PRIMARY KEY (id)\n//);\n\n//SELECT * FROM LEDSTATUS ORDER BY id DESC LIMIT 50;\n\nmsg.topic = \"SELECT * FROM LEDSTATUS ORDER BY id DESC LIMIT 50\";\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":320,"y":420,"wires":[["b6e20dd58822d956"]]},{"id":"e8b2de47058199f6","type":"ui_table","z":"41b4f910767c89fe","group":"b0c194b692c58c9c","name":"LED 資料庫","order":1,"width":8,"height":14,"columns":[],"outputs":0,"cts":false,"x":790,"y":420,"wires":[]},{"id":"b6e20dd58822d956","type":"sqlite","z":"41b4f910767c89fe","mydb":"a4c15c0e38a27cca","sqlquery":"msg.topic","sql":"","name":"LED_STATUS","x":500,"y":420,"wires":[["e8b2de47058199f6"]]},{"id":"d192743b813cb3aa","type":"ui_button","z":"41b4f910767c89fe","name":"","group":"e7c2632dca7fe97d","order":5,"width":3,"height":1,"passthru":false,"label":"(L)刪除資料庫 ","tooltip":"","color":"","bgcolor":"green","className":"","icon":"","payload":"刪除資料庫 ","payloadType":"str","topic":"topic","topicType":"msg","x":100,"y":660,"wires":[["106fe02f4b567352","0232d1d03f74ba0f"]]},{"id":"e3e691a9b6f1fce4","type":"function","z":"41b4f910767c89fe","name":"DROP　DATABASE","func":"//CREATE TABLE LEDSTATUS (\n//id INTEGER,\n//STATUS TEXT,\n//Date DATE,\n//Time TIME,\n//PRIMARY KEY (id)\n//);\n//CREATE TABLE LEDSTATUS (id INTEGER,STATUS TEXT,Date DATE,Time TIME,PRIMARY KEY (id));\nmsg.topic = \"DROP TABLE LEDSTATUS\";\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":500,"y":600,"wires":[["0ac15eb28214b6b7"]]},{"id":"0ac15eb28214b6b7","type":"sqlite","z":"41b4f910767c89fe","mydb":"a4c15c0e38a27cca","sqlquery":"msg.topic","sql":"","name":"LED_STATUS","x":720,"y":520,"wires":[["9e29d6efb208526e"]]},{"id":"eee0e0562add3a05","type":"ui_button","z":"41b4f910767c89fe","name":"","group":"e7c2632dca7fe97d","order":4,"width":3,"height":1,"passthru":false,"label":"(L)刪除所有資料 ","tooltip":"","color":"","bgcolor":"green","className":"","icon":"","payload":"刪除所有資料 ","payloadType":"str","topic":"topic","topicType":"msg","x":100,"y":560,"wires":[["0232d1d03f74ba0f","d22fecfb8e37f6b0"]]},{"id":"8c4a79912036e006","type":"function","z":"41b4f910767c89fe","name":"DELETE ALL DATA","func":"//CREATE TABLE LEDSTATUS (\n//id INTEGER,\n//STATUS TEXT,\n//Date DATE,\n//Time TIME,\n//PRIMARY KEY (id)\n//);\n//CREATE TABLE LEDSTATUS (id INTEGER,STATUS TEXT,Date DATE,Time TIME,PRIMARY KEY (id));\nmsg.topic = \"DELETE from LEDSTATUS\";\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":370,"y":500,"wires":[["0ac15eb28214b6b7"]]},{"id":"106fe02f4b567352","type":"ui_toast","z":"41b4f910767c89fe","position":"prompt","displayTime":"3","highlight":"","sendall":true,"outputs":1,"ok":"OK","cancel":"Cancel","raw":true,"className":"","topic":"","name":"","x":290,"y":660,"wires":[["3f8e6d780163b2d8"]]},{"id":"3f8e6d780163b2d8","type":"function","z":"41b4f910767c89fe","name":"function 86","func":"var topic=msg.payload;\nif (topic==\"\"){\n return [msg,null];\n \n}\nif (topic==\"Cancel\"){\n return [null,msg];\n \n}\nreturn msg;","outputs":2,"noerr":0,"initialize":"","finalize":"","libs":[],"x":470,"y":660,"wires":[["e3e691a9b6f1fce4"],[]]},{"id":"0232d1d03f74ba0f","type":"ui_audio","z":"41b4f910767c89fe","name":"","group":"11d8514.a44dcaf","voice":"Microsoft Hanhan - Chinese (Traditional, Taiwan)","always":true,"x":225,"y":600,"wires":[],"l":false},{"id":"d22fecfb8e37f6b0","type":"ui_toast","z":"41b4f910767c89fe","position":"prompt","displayTime":"3","highlight":"","sendall":true,"outputs":1,"ok":"OK","cancel":"Cancel","raw":true,"className":"","topic":"","name":"","x":310,"y":560,"wires":[["30d4f57b5808a46c"]]},{"id":"30d4f57b5808a46c","type":"function","z":"41b4f910767c89fe","name":"function 87","func":"var topic=msg.payload;\nif (topic==\"\"){\n return [msg,null];\n \n}\nif (topic==\"Cancel\"){\n return [null,msg];\n \n}\nreturn msg;","outputs":2,"noerr":0,"initialize":"","finalize":"","libs":[],"x":470,"y":560,"wires":[["8c4a79912036e006"],[]]},{"id":"6195eb71c46ac3fa","type":"ui_audio","z":"41b4f910767c89fe","name":"","group":"11d8514.a44dcaf","voice":"Microsoft Hanhan - Chinese (Traditional, Taiwan)","always":true,"x":175,"y":460,"wires":[],"l":false},{"id":"951f65c5730323f3","type":"ui_button","z":"41b4f910767c89fe","name":"","group":"e7c2632dca7fe97d","order":1,"width":3,"height":1,"passthru":false,"label":"(L)查詢一筆資料","tooltip":"","color":"","bgcolor":"green","className":"","icon":"","payload":"查詢一筆資料","payloadType":"str","topic":"topic","topicType":"msg","x":100,"y":720,"wires":[["0232d1d03f74ba0f","61b61726368812e2"]]},{"id":"6a752e8e7869da47","type":"function","z":"41b4f910767c89fe","name":"查詢一筆資料","func":"//\nvar id = msg.payload.id;\nvar s=global.get(\"SEL1\")\nmsg.topic=\"\";\nvar temp=\"\";\n\nif (s==1)\n{\n temp =\"SELECT * FROM LEDSTATUS\";\n temp=temp+\" WHERE id LIKE '\"+ id +\"'\";\n}\nmsg.topic=temp;\nglobal.set(\"SEL1\",0);\n\nreturn msg;\n\n//SELECT * FROM COMPANY WHERE AGE LIKE 'XXX%';\n//INSERT INTO LEDSTATUS (\n//id INTEGER,\n//STATUS TEXT,\n//Date DATE,\n//Time TIME,\n//PRIMARY KEY (id)\n//);\n\n//SELECT * FROM LEDSTATUS ORDER BY id DESC LIMIT 50;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":640,"y":720,"wires":[["ddd93148d6429320"]]},{"id":"ddd93148d6429320","type":"sqlite","z":"41b4f910767c89fe","mydb":"a4c15c0e38a27cca","sqlquery":"msg.topic","sql":"","name":"LED_STATUS","x":700,"y":640,"wires":[["e8b2de47058199f6"]]},{"id":"9e9a5b3281790118","type":"ui_button","z":"41b4f910767c89fe","name":"","group":"e7c2632dca7fe97d","order":2,"width":3,"height":1,"passthru":false,"label":"(L)刪除一筆資料","tooltip":"","color":"","bgcolor":"green","className":"","icon":"","payload":"刪除一筆資料","payloadType":"str","topic":"topic","topicType":"msg","x":100,"y":780,"wires":[["e29f31c15cade7c8","f7762f80529020e6"]]},{"id":"e29f31c15cade7c8","type":"ui_audio","z":"41b4f910767c89fe","name":"","group":"b0c194b692c58c9c","voice":"Microsoft Hanhan - Chinese (Traditional, Taiwan)","always":true,"x":235,"y":820,"wires":[],"l":false},{"id":"d2653e92fbc79b15","type":"ui_form","z":"41b4f910767c89fe","name":"","label":"(L)輸入id","group":"9bc6b410904f7720","order":1,"width":0,"height":0,"options":[{"label":"ID","value":"id","type":"number","required":true,"rows":null}],"formValue":{"id":""},"payload":"","submit":"Submit","cancel":"Cancle","topic":"Form","topicType":"str","splitLayout":false,"className":"","x":500,"y":780,"wires":[["6a752e8e7869da47","ed7a959cfa88e9ee","76e3d76c4101918d"]]},{"id":"ed7a959cfa88e9ee","type":"function","z":"41b4f910767c89fe","name":"刪除一筆資料","func":"//\nvar id = msg.payload.id;\nvar s=global.get(\"SEL2\")\nmsg.topic=\"\";\nvar temp=\"\";\n\nif (s==2)\n{\n temp =\"DELETE FROM LEDSTATUS\";\n temp=temp+\" WHERE id LIKE '\"+ id +\"'\";\n}\n\nmsg.topic=temp;\nglobal.set(\"SEL2\",0)\nreturn msg;\n\n//SELECT * FROM COMPANY WHERE AGE LIKE 'XXX%';\n//INSERT INTO LEDSTATUS (\n//id INTEGER,\n//STATUS TEXT,\n//Date DATE,\n//Time TIME,\n//PRIMARY KEY (id)\n//);\n\n//DELETE FROM COMPANY WHERE ID = 7;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":660,"y":780,"wires":[["d38a29130e4169af","23844a072264ee4c"]]},{"id":"9e29d6efb208526e","type":"link out","z":"41b4f910767c89fe","name":"link out 46","mode":"link","links":["750899d4bab2211f"],"x":855,"y":520,"wires":[]},{"id":"750899d4bab2211f","type":"link in","z":"41b4f910767c89fe","name":"link in 42","links":["9e29d6efb208526e"],"x":235,"y":460,"wires":[["c99597aa945f9ef9"]]},{"id":"d38a29130e4169af","type":"sqlite","z":"41b4f910767c89fe","mydb":"a4c15c0e38a27cca","sqlquery":"msg.topic","sql":"","name":"LED_STATUS","x":900,"y":780,"wires":[["9e29d6efb208526e"]]},{"id":"e0d610a9833ca3b2","type":"ui_button","z":"41b4f910767c89fe","name":"","group":"e7c2632dca7fe97d","order":3,"width":3,"height":1,"passthru":false,"label":"(L)更正一筆資料","tooltip":"","color":"","bgcolor":"green","className":"","icon":"","payload":"更正一筆資料","payloadType":"str","topic":"topic","topicType":"msg","x":100,"y":860,"wires":[["e29f31c15cade7c8","f466e53bfb6d1cd6"]]},{"id":"f0e90c00060177cb","type":"comment","z":"41b4f910767c89fe","name":"UPDATE查詢的WHERE","info":"UPDATE查詢的WHERE子句的基本語法如下：\n\nUPDATE table_name\nSET column1 = value1, column2 = value2...., columnN = valueN\nWHERE [condition];","x":120,"y":900,"wires":[]},{"id":"4eae369fb77657d4","type":"function","z":"41b4f910767c89fe","name":"更正一筆資料","func":"//\nvar id = global.get(\"ID\");\nvar status = msg.payload.Status;\nvar date = msg.payload.date;\nvar time = msg.payload.time;\n\nvar s=global.get(\"SEL3\")\nmsg.topic=\"\";\nvar temp=\"\";\n\nif (s==3)\n{\n temp =\"update LEDSTATUS set \";\n temp=temp+\" STATUS= '\" + status +\"'\";\n temp=temp+\" , Date= '\" + date +\"'\";\n temp=temp+\" , Time= '\" + time +\"'\";\n temp=temp+\" WHERE id=\" + id;\n \n //msg.topic = \"update LEDSTATUS set ( id , STATUS , Date , Time ) VALUES ($id, $status , $date , $time ) \" ;\n //msg.payload = [id,status,date,time]\n}\nmsg.topic=temp;\n\nreturn msg;\n\n//msg.topic = \"INSERT INTO LEDSTATUS ( STATUS , Date , Time ) VALUES ($myLED, $var_date , $var_time ) \" ;\n//msg.payload = [myLED, var_date , var_time ]\n\n\n//SELECT * FROM COMPANY WHERE AGE LIKE 'XXX%';\n//INSERT INTO LEDSTATUS (\n//id INTEGER,\n//STATUS TEXT,\n//Date DATE,\n//Time TIME,\n//PRIMARY KEY (id)\n//);\n\n//DELETE FROM COMPANY WHERE ID = 7;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":760,"y":900,"wires":[["d38a29130e4169af","6f62af9b2b328d2a"]]},{"id":"61b61726368812e2","type":"function","z":"41b4f910767c89fe","name":"function flow set1","func":"var s1=1;\nglobal.set(\"SEL1\",s1);\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":310,"y":720,"wires":[["d2653e92fbc79b15"]]},{"id":"f7762f80529020e6","type":"function","z":"41b4f910767c89fe","name":"function flow set2","func":"var s1=2;\nglobal.set(\"SEL2\",s1);\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":310,"y":780,"wires":[["d2653e92fbc79b15"]]},{"id":"f466e53bfb6d1cd6","type":"function","z":"41b4f910767c89fe","name":"function flow set3","func":"var s1=3;\nglobal.set(\"SEL3\",s1);\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":310,"y":860,"wires":[["d2653e92fbc79b15"]]},{"id":"ceb3b8b8242a0449","type":"ui_form","z":"41b4f910767c89fe","name":"","label":"(L)更正欄位","group":"e7c2632dca7fe97d","order":7,"width":6,"height":1,"options":[{"label":"STATUS","value":"Status","type":"text","required":true,"rows":null},{"label":"DATE","value":"date","type":"text","required":true,"rows":null},{"label":"TIME","value":"time","type":"text","required":true,"rows":null}],"formValue":{"Status":"","date":"","time":""},"payload":"","submit":"Submit","cancel":"Cancle","topic":"Form","topicType":"str","splitLayout":false,"className":"","x":590,"y":900,"wires":[["4eae369fb77657d4"]]},{"id":"23844a072264ee4c","type":"debug","z":"41b4f910767c89fe","name":"debug 228","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"topic","targetType":"msg","statusVal":"","statusType":"auto","x":870,"y":740,"wires":[]},{"id":"76e3d76c4101918d","type":"function","z":"41b4f910767c89fe","name":"Store ID資料","func":"//\nvar id = msg.payload.id;\nglobal.set(\"ID\",id)\nreturn msg;\n\n//SELECT * FROM COMPANY WHERE AGE LIKE 'XXX%';\n//INSERT INTO LEDSTATUS (\n//id INTEGER,\n//STATUS TEXT,\n//Date DATE,\n//Time TIME,\n//PRIMARY KEY (id)\n//);\n\n//DELETE FROM COMPANY WHERE ID = 7;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":590,"y":840,"wires":[["ceb3b8b8242a0449","4ae99af7403816ef"]]},{"id":"4ae99af7403816ef","type":"function","z":"41b4f910767c89fe","name":"查詢一筆資料","func":"//\nvar id = global.get(\"ID\");\nmsg.topic=\"\";\nvar temp=\"\";\ntemp =\"SELECT * FROM LEDSTATUS\";\ntemp=temp+\" WHERE id LIKE '\"+ id +\"'\";\n\nmsg.topic=temp;\n\nreturn msg;\n\n//SELECT * FROM COMPANY WHERE AGE LIKE 'XXX%';\n//INSERT INTO LEDSTATUS (\n//id INTEGER,\n//STATUS TEXT,\n//Date DATE,\n//Time TIME,\n//PRIMARY KEY (id)\n//);\n\n//SELECT * FROM LEDSTATUS ORDER BY id DESC LIMIT 50;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":780,"y":840,"wires":[["ddd93148d6429320"]]},{"id":"6f62af9b2b328d2a","type":"debug","z":"41b4f910767c89fe","name":"debug 229","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"topic","targetType":"msg","statusVal":"","statusType":"auto","x":950,"y":900,"wires":[]},{"id":"f5bcda37ccbf881e","type":"comment","z":"41b4f910767c89fe","name":"資料庫位置 C:\\Users\\User\\.node-red\\EX2_1.db","info":"","x":420,"y":40,"wires":[]},{"id":"02fe1bbf929e0f65","type":"mqtt in","z":"41b4f910767c89fe","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":1020,"wires":[["5bcbf70cb4867542"]]},{"id":"5bcbf70cb4867542","type":"function","z":"41b4f910767c89fe","name":"function ","func":"msg.payload=\" ---ESP32回來資料---\" +msg.payload;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":260,"y":1020,"wires":[["77c19993f3653948","6b3efba4ba7ecfcb","3da3266b340854f1"]]},{"id":"77c19993f3653948","type":"function","z":"41b4f910767c89fe","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":450,"y":1020,"wires":[["d3242c14d28159cd"]]},{"id":"d3242c14d28159cd","type":"http request","z":"41b4f910767c89fe","name":"","method":"POST","ret":"txt","paytoqs":false,"url":"https://notify-api.line.me/api/notify","tls":"","persist":false,"proxy":"","authType":"","x":620,"y":1020,"wires":[["509f2646937f873e"]]},{"id":"509f2646937f873e","type":"debug","z":"41b4f910767c89fe","name":"debug 230","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":790,"y":1020,"wires":[]},{"id":"c63a387fa7c18e1a","type":"comment","z":"41b4f910767c89fe","name":"Line Notify Message ","info":"","x":470,"y":980,"wires":[]},{"id":"e827f1ca78ea8c25","type":"mqtt out","z":"41b4f910767c89fe","name":"Control LED","topic":"alex9ufo/esp32/led","qos":"1","retain":"true","respTopic":"","contentType":"","userProps":"","correl":"","expiry":"","broker":"841df58d.ee5e98","x":290,"y":160,"wires":[]},{"id":"40af326b1d1a01c5","type":"comment","z":"41b4f910767c89fe","name":"alex9ufo/esp32/led","info":"","x":350,"y":200,"wires":[]},{"id":"9466ed42faee22e9","type":"ui_text","z":"41b4f910767c89fe","group":"0487352be5053bfa","order":6,"width":5,"height":1,"name":"","label":"(L)發行到MQTT的資料 : ","format":"{{msg.payload}}","layout":"row-left","className":"","x":330,"y":300,"wires":[]},{"id":"3da3266b340854f1","type":"ui_text","z":"41b4f910767c89fe","group":"0487352be5053bfa","order":7,"width":5,"height":1,"name":"","label":"(L)訂閱MQTT的資料 : ","format":"{{msg.payload}}","layout":"row-left","className":"","x":480,"y":1100,"wires":[]},{"id":"9be2e5a6dcd3f150","type":"ui_audio","z":"41b4f910767c89fe","name":"","group":"b0c194b692c58c9c","voice":"Microsoft Hanhan - Chinese (Traditional, Taiwan)","always":"","x":620,"y":1060,"wires":[]},{"id":"6b3efba4ba7ecfcb","type":"delay","z":"41b4f910767c89fe","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":1060,"wires":[["9be2e5a6dcd3f150"]]},{"id":"fb6515a4ee731e0e","type":"comment","z":"41b4f910767c89fe","name":"TABLE RFIDtable","info":"\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","x":1200,"y":40,"wires":[]},{"id":"718398f192a85fd5","type":"comment","z":"41b4f910767c89fe","name":"資料庫位置 C:\\Users\\User\\.node-red\\EX2_3RFID.db","info":"","x":1310,"y":80,"wires":[]},{"id":"3ace6e0491e7e5fc","type":"ui_button","z":"41b4f910767c89fe","name":"","group":"e7c2632dca7fe97d","order":14,"width":6,"height":1,"passthru":false,"label":"建立資料庫","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"建立資料庫","payloadType":"str","topic":"topic","topicType":"msg","x":1190,"y":160,"wires":[["8db1a3e5dec7225f","b58b92c38ee4b88a"]]},{"id":"c1d3a810d10370ce","type":"ui_button","z":"41b4f910767c89fe","name":"","group":"e7c2632dca7fe97d","order":15,"width":6,"height":1,"passthru":false,"label":"檢視資料庫資料","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"檢視資料","payloadType":"str","topic":"topic","topicType":"msg","x":1200,"y":600,"wires":[["473241d8156c7b34","309700b2a99ff7a8","9801d4426dcd105a"]]},{"id":"8f47231d580e841e","type":"ui_button","z":"41b4f910767c89fe","name":"","group":"e7c2632dca7fe97d","order":12,"width":3,"height":1,"passthru":false,"label":"刪除所有資料","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"刪除所有資料","payloadType":"str","topic":"topic","topicType":"msg","x":1200,"y":860,"wires":[["25c3a227e72512ca","a7c06c81e81d19fd"]]},{"id":"8db1a3e5dec7225f","type":"function","z":"41b4f910767c89fe","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":1380,"y":160,"wires":[["d0e9c238b0d0d58b"]]},{"id":"381ca230a3d88c06","type":"debug","z":"41b4f910767c89fe","name":"debug","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1990,"y":200,"wires":[]},{"id":"ce41f5e91d2d0cb6","type":"function","z":"41b4f910767c89fe","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":1560,"y":240,"wires":[["444868658020454e","030d26d0f38c2cea"]]},{"id":"bcfb1b613a807615","type":"function","z":"41b4f910767c89fe","name":"刪除所有資料","func":"//DELETE from RFIDtable\nmsg.topic = \"DELETE from RFIDtable\";\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1660,"y":880,"wires":[["b358995020a01ac7"]]},{"id":"97933d63e80cd260","type":"ui_button","z":"41b4f910767c89fe","name":"","group":"e7c2632dca7fe97d","order":10,"width":3,"height":1,"passthru":false,"label":"刪除資料庫","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"刪除資料庫","payloadType":"str","topic":"topic","topicType":"msg","x":1190,"y":960,"wires":[["99a8394d2ff46a49","cba4394c46eb3321"]]},{"id":"f8a95a8f429b74b8","type":"function","z":"41b4f910767c89fe","name":"刪除資料庫","func":"//DROP TABLE RFIDtable\nmsg.topic = \"DROP TABLE RFIDtable\";\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1690,"y":960,"wires":[["80c7ad8656604af2"]]},{"id":"5283bd0e802bc796","type":"debug","z":"41b4f910767c89fe","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":1990,"y":940,"wires":[]},{"id":"9801d4426dcd105a","type":"function","z":"41b4f910767c89fe","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":1840,"y":600,"wires":[["44366554919536d0","1068cb1de6b3570e"]]},{"id":"02dceb2af69bce95","type":"function","z":"41b4f910767c89fe","name":"SELECT ALL","func":"var del_idtemp=msg.payload;\nflow.set(\"idtemp\", del_idtemp);\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1410,"y":800,"wires":[["81e97ca11a0c5df6"]]},{"id":"f4a8410392a5f2a9","type":"function","z":"41b4f910767c89fe","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":1640,"y":740,"wires":[["54b4e21709fe4a24"]]},{"id":"9b7dd8e9b9026f9b","type":"ui_numeric","z":"41b4f910767c89fe","name":"","label":"刪除的database_id","tooltip":"","group":"9bc6b410904f7720","order":4,"width":5,"height":1,"wrap":true,"passthru":true,"topic":"topic","topicType":"msg","format":"{{value}}","min":"1","max":"1000","step":1,"className":"","x":1210,"y":800,"wires":[["02dceb2af69bce95"]]},{"id":"3dd4e0ca9fca2328","type":"ui_button","z":"41b4f910767c89fe","name":"","group":"e7c2632dca7fe97d","order":11,"width":3,"height":1,"passthru":false,"label":"刪除一筆資料","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"刪除一筆資料","payloadType":"str","topic":"topic","topicType":"msg","x":1200,"y":660,"wires":[["473241d8156c7b34","3bd53583c67a8bf7"]]},{"id":"81e97ca11a0c5df6","type":"debug","z":"41b4f910767c89fe","name":"debug 231","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":1590,"y":800,"wires":[]},{"id":"89e45e9fe56b3b2e","type":"mqtt in","z":"41b4f910767c89fe","name":"新增 RFID","topic":"alex9ufo/esp32/RFID","qos":"2","datatype":"auto-detect","broker":"841df58d.ee5e98","nl":false,"rap":true,"rh":0,"inputs":0,"x":1180,"y":240,"wires":[["82c014984d8f3c2f","be255c2203fbcb03"]]},{"id":"446eedfa9db82a0f","type":"ui_audio","z":"41b4f910767c89fe","name":"","group":"2aec78ed5dc0600c","voice":"Microsoft Hanhan - Chinese (Traditional, Taiwan)","always":true,"x":1640,"y":400,"wires":[]},{"id":"50f84f4e09e0a606","type":"function","z":"41b4f910767c89fe","name":"function ","func":"var temp= msg.payload;\nmsg.payload= \"新增一筆資料\" + temp;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1460,"y":320,"wires":[["446eedfa9db82a0f","38740cf4c86d3e8f","8cab418991e66fb4"]]},{"id":"473241d8156c7b34","type":"ui_audio","z":"41b4f910767c89fe","name":"","group":"2aec78ed5dc0600c","voice":"Microsoft Hanhan - Chinese (Traditional, Taiwan)","always":true,"x":1400,"y":640,"wires":[]},{"id":"b58b92c38ee4b88a","type":"ui_audio","z":"41b4f910767c89fe","name":"","group":"2aec78ed5dc0600c","voice":"Microsoft Hanhan - Chinese (Traditional, Taiwan)","always":true,"x":1340,"y":120,"wires":[]},{"id":"6b4194facbcb44b7","type":"function","z":"41b4f910767c89fe","name":"比對 function","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":1430,"y":1040,"wires":[["f463a8efe8a86076"]]},{"id":"8262b5e0a7322c46","type":"ui_button","z":"41b4f910767c89fe","name":"","group":"e7c2632dca7fe97d","order":13,"width":3,"height":1,"passthru":false,"label":"比對資料庫","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"比對資料庫","payloadType":"str","topic":"topic","topicType":"msg","x":1190,"y":1040,"wires":[["6b4194facbcb44b7","99a8394d2ff46a49","56ef5df3a51dffd6"]]},{"id":"096c65eb842945be","type":"function","z":"41b4f910767c89fe","name":"SELECT ALL","func":"var query_uidtemp=msg.payload;\nflow.set(\"uidtemp\", query_uidtemp);\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1650,"y":440,"wires":[["a43c4525b879822d","33dc3daf2b9840fe"]]},{"id":"489197761d536c02","type":"ui_text_input","z":"41b4f910767c89fe","name":"","label":"手動查詢資料的uidname","tooltip":"","group":"9bc6b410904f7720","order":3,"width":5,"height":1,"passthru":true,"mode":"text","delay":300,"topic":"topic","sendOnBlur":true,"className":"","topicType":"msg","x":1430,"y":500,"wires":[["096c65eb842945be"]]},{"id":"939bdbbcab54ede1","type":"function","z":"41b4f910767c89fe","name":"function ","func":"var query=msg.payload;\n\nif (query== '1' )\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":1640,"y":1460,"wires":[["2da05eb9bb42a954","9ecd3819a30aa873"]]},{"id":"2033c3c13e3907c9","type":"ui_switch","z":"41b4f910767c89fe","name":"","label":"新增模式 /自動比對模式 ","tooltip":"","group":"0487352be5053bfa","order":9,"width":5,"height":1,"passthru":true,"decouple":"false","topic":"s1","topicType":"str","style":"","onvalue":"1","onvalueType":"str","onicon":"","oncolor":"","offvalue":"0","offvalueType":"str","officon":"","offcolor":"","animate":false,"className":"","x":1370,"y":1460,"wires":[["939bdbbcab54ede1","c5d4b1070976d640","33094d651e63a3b9"]]},{"id":"2da05eb9bb42a954","type":"debug","z":"41b4f910767c89fe","name":"debug 232","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":1790,"y":1440,"wires":[]},{"id":"acf3dfeb9069ff15","type":"switch","z":"41b4f910767c89fe","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":1230,"y":360,"wires":[["ce41f5e91d2d0cb6","50f84f4e09e0a606"],["1d280c0310f54321"]]},{"id":"82c014984d8f3c2f","type":"function","z":"41b4f910767c89fe","name":"Query or Insert ","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":1340,"y":240,"wires":[["acf3dfeb9069ff15"]]},{"id":"9ecd3819a30aa873","type":"ui_audio","z":"41b4f910767c89fe","name":"","group":"2aec78ed5dc0600c","voice":"Google US English","always":true,"x":1780,"y":1480,"wires":[]},{"id":"a43c4525b879822d","type":"debug","z":"41b4f910767c89fe","name":"debug 233","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":1850,"y":460,"wires":[]},{"id":"33dc3daf2b9840fe","type":"ui_text","z":"41b4f910767c89fe","group":"0487352be5053bfa","order":13,"width":5,"height":2,"name":"","label":"RFID的uid","format":"{{msg.payload}}","layout":"row-left","className":"","x":1850,"y":360,"wires":[]},{"id":"7a2bec3260e95960","type":"function","z":"41b4f910767c89fe","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":1520,"y":1180,"wires":[["edc52a9f4129adc5"]]},{"id":"b9c04bb5d89da57e","type":"function","z":"41b4f910767c89fe","name":"function ","func":"var num=msg.payload[0].總共有幾筆資料;\nmsg.payload=num;\nreturn msg; \n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1800,"y":1180,"wires":[["8bca32233160bfea","65532790a9d4db9e"]]},{"id":"6e044a502b8e43d5","type":"ui_text","z":"41b4f910767c89fe","group":"0487352be5053bfa","order":12,"width":5,"height":1,"name":"","label":"查詢結果","format":"{{msg.payload}}","layout":"row-left","className":"","x":2140,"y":1240,"wires":[]},{"id":"22af289c2874566e","type":"ui_text","z":"41b4f910767c89fe","group":"0487352be5053bfa","order":11,"width":5,"height":1,"name":"","label":"查詢結果:筆數","format":"{{msg.payload}}","layout":"row-left","className":"","x":2160,"y":1180,"wires":[]},{"id":"f40e6501a8833bd9","type":"mqtt in","z":"41b4f910767c89fe","name":"","topic":"alex9ufo/esp32/Starting","qos":"2","datatype":"auto-detect","broker":"841df58d.ee5e98","nl":false,"rap":true,"rh":0,"inputs":0,"x":1220,"y":1300,"wires":[["e0ecdc79566c5bb5","1fad857520e8fe5f"]]},{"id":"e0ecdc79566c5bb5","type":"ui_audio","z":"41b4f910767c89fe","name":"","group":"2aec78ed5dc0600c","voice":"Google US English","always":false,"x":1420,"y":1340,"wires":[]},{"id":"8bca32233160bfea","type":"change","z":"41b4f910767c89fe","name":"","rules":[{"t":"set","p":"fcolor","pt":"msg","to":"red","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":1980,"y":1180,"wires":[["22af289c2874566e"]]},{"id":"60e58b9c401654e7","type":"change","z":"41b4f910767c89fe","name":"","rules":[{"t":"set","p":"fcolor","pt":"msg","to":"red","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":1980,"y":1240,"wires":[["6e044a502b8e43d5"]]},{"id":"56ef5df3a51dffd6","type":"function","z":"41b4f910767c89fe","name":"function","func":"\nvar a= flow.get(\"uidtemp\");\nmsg.payload=a;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1380,"y":1180,"wires":[["7a2bec3260e95960"]]},{"id":"bfe901410b219927","type":"ui_audio","z":"41b4f910767c89fe","name":"","group":"2aec78ed5dc0600c","voice":"Microsoft Hanhan - Chinese (Traditional, Taiwan)","always":true,"x":2140,"y":1280,"wires":[]},{"id":"58f1559efc0fdfc5","type":"delay","z":"41b4f910767c89fe","name":"","pauseType":"delay","timeout":"2","timeoutUnits":"seconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"allowrate":false,"outputs":1,"x":1980,"y":1280,"wires":[["bfe901410b219927"]]},{"id":"65532790a9d4db9e","type":"function","z":"41b4f910767c89fe","name":"function ","func":"var n=msg.payload;\n\nif (n>0)\n msg.payload='RFID符合';\nelse\n msg.payload='RFID錯誤';\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1800,"y":1240,"wires":[["58f1559efc0fdfc5","60e58b9c401654e7","a485ecf426757725"]]},{"id":"38740cf4c86d3e8f","type":"function","z":"41b4f910767c89fe","name":"取得UID號碼","func":"var myRFID = flow.get('uid_temp');\nmsg.payload=myRFID;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1650,"y":360,"wires":[["33dc3daf2b9840fe"]]},{"id":"99a8394d2ff46a49","type":"ui_audio","z":"41b4f910767c89fe","name":"","group":"2aec78ed5dc0600c","voice":"Microsoft Hanhan - Chinese (Traditional, Taiwan)","always":true,"x":1340,"y":1000,"wires":[]},{"id":"25c3a227e72512ca","type":"ui_audio","z":"41b4f910767c89fe","name":"","group":"2aec78ed5dc0600c","voice":"Microsoft Hanhan - Chinese (Traditional, Taiwan)","always":true,"x":1400,"y":840,"wires":[]},{"id":"444868658020454e","type":"function","z":"41b4f910767c89fe","name":"增加日期時間","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":1720,"y":280,"wires":[["d0339b6976ac8c44"]]},{"id":"d0339b6976ac8c44","type":"function","z":"41b4f910767c89fe","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":1890,"y":280,"wires":[["44d606080409fa25"]]},{"id":"44d606080409fa25","type":"http request","z":"41b4f910767c89fe","name":"","method":"POST","ret":"txt","paytoqs":false,"url":"https://notify-api.line.me/api/notify","tls":"","persist":false,"proxy":"","authType":"","x":2040,"y":280,"wires":[["54ac4d90e3eb2827"]]},{"id":"54ac4d90e3eb2827","type":"debug","z":"41b4f910767c89fe","name":"debug 234","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":2190,"y":280,"wires":[]},{"id":"5733c83f86933541","type":"comment","z":"41b4f910767c89fe","name":"Line Notify Message ","info":"","x":1910,"y":320,"wires":[]},{"id":"98ff3f6b2d09e70d","type":"function","z":"41b4f910767c89fe","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,"initialize":"","finalize":"","libs":[],"x":1970,"y":1380,"wires":[["94c9e9772fb6ccbf"]]},{"id":"87b950831ca240c0","type":"comment","z":"41b4f910767c89fe","name":"Line Notify Message ","info":"","x":2130,"y":1440,"wires":[]},{"id":"94c9e9772fb6ccbf","type":"http request","z":"41b4f910767c89fe","name":"","method":"POST","ret":"txt","paytoqs":false,"url":"https://notify-api.line.me/api/notify","tls":"","persist":false,"proxy":"","authType":"","x":2120,"y":1380,"wires":[["5b9b41490b7dd68f"]]},{"id":"5b9b41490b7dd68f","type":"debug","z":"41b4f910767c89fe","name":"debug","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":2250,"y":1380,"wires":[]},{"id":"a485ecf426757725","type":"function","z":"41b4f910767c89fe","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":1800,"y":1340,"wires":[["98ff3f6b2d09e70d","407d716762fd9c9e"]]},{"id":"dac6dad23eddcfb5","type":"ui_table","z":"41b4f910767c89fe","group":"2aec78ed5dc0600c","name":"","order":1,"width":8,"height":14,"columns":[],"outputs":0,"cts":false,"x":2150,"y":600,"wires":[]},{"id":"5f0f6f171556d6ea","type":"inject","z":"41b4f910767c89fe","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":true,"onceDelay":"1","topic":"","payload":"0","payloadType":"str","x":1190,"y":1460,"wires":[["2033c3c13e3907c9"]]},{"id":"c5d4b1070976d640","type":"change","z":"41b4f910767c89fe","name":"","rules":[{"t":"change","p":"payload","pt":"msg","from":"1","fromt":"num","to":"查詢模式","tot":"str"},{"t":"change","p":"payload","pt":"msg","from":"0","fromt":"num","to":"新增模式","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":1460,"y":1600,"wires":[["8f0ace2e671643e8","d89f594e65553e67"]]},{"id":"8f0ace2e671643e8","type":"ui_text_input","z":"41b4f910767c89fe","name":"","label":"State:","tooltip":"","group":"0487352be5053bfa","order":10,"width":5,"height":1,"passthru":true,"mode":"text","delay":300,"topic":"","sendOnBlur":true,"className":"","topicType":"str","x":1630,"y":1620,"wires":[[]]},{"id":"33094d651e63a3b9","type":"debug","z":"41b4f910767c89fe","name":"debug ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1630,"y":1520,"wires":[]},{"id":"ca461cd117e75734","type":"inject","z":"41b4f910767c89fe","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":true,"onceDelay":"1","topic":"","payload":"","payloadType":"date","x":1580,"y":80,"wires":[["3c1b476cc61e1cdb"]]},{"id":"3c1b476cc61e1cdb","type":"function","z":"41b4f910767c89fe","name":"id SET ","func":"var del_idtemp=1;\nflow.set(\"idtemp\", del_idtemp);\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1710,"y":80,"wires":[[]]},{"id":"1fad857520e8fe5f","type":"ui_toast","z":"41b4f910767c89fe","position":"top right","displayTime":"3","highlight":"","sendall":true,"outputs":0,"ok":"OK","cancel":"Cancel","raw":false,"className":"","topic":"","name":"","x":1450,"y":1300,"wires":[]},{"id":"d89f594e65553e67","type":"ui_toast","z":"41b4f910767c89fe","position":"top right","displayTime":"3","highlight":"","sendall":true,"outputs":0,"ok":"OK","cancel":"Cancel","raw":false,"className":"","topic":"","name":"","x":1670,"y":1580,"wires":[]},{"id":"a7c06c81e81d19fd","type":"ui_toast","z":"41b4f910767c89fe","position":"prompt","displayTime":"3","highlight":"","sendall":true,"outputs":1,"ok":"OK","cancel":"Cancel","raw":true,"className":"","topic":"","name":"","x":1310,"y":900,"wires":[["6571dfb9b238d248"]]},{"id":"6571dfb9b238d248","type":"function","z":"41b4f910767c89fe","name":"OK or Cancel","func":"var topic=msg.payload;\nif (topic==\"\"){\n return [msg,null];\n \n}\nif (topic==\"Cancel\"){\n return [null,msg];\n \n}\nreturn msg;","outputs":2,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1480,"y":900,"wires":[["bcfb1b613a807615"],[]]},{"id":"cba4394c46eb3321","type":"ui_toast","z":"41b4f910767c89fe","position":"prompt","displayTime":"3","highlight":"","sendall":true,"outputs":1,"ok":"OK","cancel":"Cancel","raw":true,"className":"","topic":"","name":"","x":1350,"y":960,"wires":[["b28f406e9c01d9e4"]]},{"id":"b28f406e9c01d9e4","type":"function","z":"41b4f910767c89fe","name":"OK or Cancel","func":"var topic=msg.payload;\nif (topic==\"\"){\n return [msg,null];\n \n}\nif (topic==\"Cancel\"){\n return [null,msg];\n \n}\nreturn msg;","outputs":2,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1520,"y":960,"wires":[["f8a95a8f429b74b8"],[]]},{"id":"3bd53583c67a8bf7","type":"ui_toast","z":"41b4f910767c89fe","position":"prompt","displayTime":"3","highlight":"","sendall":true,"outputs":1,"ok":"OK","cancel":"Cancel","raw":true,"className":"","topic":"","name":"","x":1310,"y":740,"wires":[["f54fc64f90922613"]]},{"id":"f54fc64f90922613","type":"function","z":"41b4f910767c89fe","name":"OK or Cancel","func":"var topic=msg.payload;\nif (topic==\"\"){\n return [msg,null];\n \n}\nif (topic==\"Cancel\"){\n return [null,msg];\n \n}\nreturn msg;","outputs":2,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1480,"y":740,"wires":[["f4a8410392a5f2a9"],[]]},{"id":"a4d19d120e05dc33","type":"link out","z":"41b4f910767c89fe","name":"link out 47","mode":"link","links":["6b58ae7f57402aa5"],"x":1945,"y":880,"wires":[]},{"id":"6b58ae7f57402aa5","type":"link in","z":"41b4f910767c89fe","name":"link in 43","links":["a4d19d120e05dc33","88a482f099e394c9","70487decb239f3c4","ce142a687d0b79f1"],"x":1735,"y":560,"wires":[["9801d4426dcd105a"]]},{"id":"88a482f099e394c9","type":"link out","z":"41b4f910767c89fe","name":"link out 48","mode":"link","links":["6b58ae7f57402aa5"],"x":1955,"y":240,"wires":[]},{"id":"5768783b47a7eeba","type":"ui_toast","z":"41b4f910767c89fe","position":"top right","displayTime":"3","highlight":"","sendall":true,"outputs":0,"ok":"OK","cancel":"Cancel","raw":false,"className":"","topic":"","name":"","x":2170,"y":1340,"wires":[]},{"id":"309700b2a99ff7a8","type":"ui_toast","z":"41b4f910767c89fe","position":"top right","displayTime":"3","highlight":"","sendall":true,"outputs":0,"ok":"OK","cancel":"Cancel","raw":false,"className":"","topic":"","name":"","x":1410,"y":560,"wires":[]},{"id":"8cab418991e66fb4","type":"ui_toast","z":"41b4f910767c89fe","position":"top right","displayTime":"3","highlight":"","sendall":true,"outputs":0,"ok":"OK","cancel":"Cancel","raw":false,"className":"","topic":"","name":"","x":1670,"y":320,"wires":[]},{"id":"af48ef899c7c430d","type":"debug","z":"41b4f910767c89fe","name":"debug","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1830,"y":160,"wires":[]},{"id":"1d280c0310f54321","type":"function","z":"41b4f910767c89fe","name":"get UID","func":"\nvar a= flow.get(\"uid_temp\");\nmsg.payload=a;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1420,"y":440,"wires":[["096c65eb842945be","39fee7d7a4037b19"]]},{"id":"407d716762fd9c9e","type":"function","z":"41b4f910767c89fe","name":"function","func":"var b=msg.payload;\nmsg.payload=\"Line Notify --->\"+b;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1980,"y":1340,"wires":[["5768783b47a7eeba"]]},{"id":"be255c2203fbcb03","type":"ui_toast","z":"41b4f910767c89fe","position":"top right","displayTime":"3","highlight":"","sendall":true,"outputs":0,"ok":"OK","cancel":"Cancel","raw":false,"className":"","topic":"","name":"","x":1350,"y":200,"wires":[]},{"id":"39fee7d7a4037b19","type":"link out","z":"41b4f910767c89fe","name":"link out 49","mode":"link","links":["954f2a0914336742"],"x":1515,"y":420,"wires":[]},{"id":"954f2a0914336742","type":"link in","z":"41b4f910767c89fe","name":"link in 44","links":["39fee7d7a4037b19"],"x":1235,"y":1220,"wires":[["56ef5df3a51dffd6"]]},{"id":"1be5d9b672fa39cf","type":"comment","z":"41b4f910767c89fe","name":"Set Default Value","info":"","x":1580,"y":40,"wires":[]},{"id":"d0e9c238b0d0d58b","type":"sqlite","z":"41b4f910767c89fe","mydb":"0357a00ce0e2564b","sqlquery":"msg.topic","sql":"","name":"RFID dB","x":1640,"y":160,"wires":[["af48ef899c7c430d"]]},{"id":"030d26d0f38c2cea","type":"sqlite","z":"41b4f910767c89fe","mydb":"0357a00ce0e2564b","sqlquery":"msg.topic","sql":"","name":"RFID dB","x":1820,"y":240,"wires":[["88a482f099e394c9","381ca230a3d88c06"]]},{"id":"44366554919536d0","type":"sqlite","z":"41b4f910767c89fe","mydb":"0357a00ce0e2564b","sqlquery":"msg.topic","sql":"","name":"RFID dB","x":1980,"y":600,"wires":[["dac6dad23eddcfb5"]]},{"id":"54b4e21709fe4a24","type":"sqlite","z":"41b4f910767c89fe","mydb":"0357a00ce0e2564b","sqlquery":"msg.topic","sql":"","name":"RFID dB","x":1780,"y":740,"wires":[["9801d4426dcd105a"]]},{"id":"b358995020a01ac7","type":"sqlite","z":"41b4f910767c89fe","mydb":"f87c808cc786ae80","sqlquery":"msg.topic","sql":"","name":"RFID dB","x":1820,"y":880,"wires":[["5283bd0e802bc796","a4d19d120e05dc33"]]},{"id":"80c7ad8656604af2","type":"sqlite","z":"41b4f910767c89fe","mydb":"0357a00ce0e2564b","sqlquery":"msg.topic","sql":"","name":"RFID dB","x":1840,"y":960,"wires":[["5283bd0e802bc796"]]},{"id":"f463a8efe8a86076","type":"sqlite","z":"41b4f910767c89fe","mydb":"0357a00ce0e2564b","sqlquery":"msg.topic","sql":"","name":"RFID dB","x":1820,"y":1040,"wires":[["dac6dad23eddcfb5"]]},{"id":"1068cb1de6b3570e","type":"debug","z":"41b4f910767c89fe","name":"debug 235","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":1990,"y":560,"wires":[]},{"id":"edc52a9f4129adc5","type":"sqlite","z":"41b4f910767c89fe","mydb":"0357a00ce0e2564b","sqlquery":"msg.topic","sql":"","name":"RFID dB","x":1660,"y":1180,"wires":[["b9c04bb5d89da57e"]]},{"id":"87753bab743780b6","type":"sqlite","z":"41b4f910767c89fe","mydb":"a4c15c0e38a27cca","sqlquery":"msg.topic","sql":"","name":"LED_STATUS","x":500,"y":120,"wires":[["6e907e9ca9da5039"]]},{"id":"53e08c5e7a0fa712","type":"ui_button","z":"41b4f910767c89fe","name":"","group":"0487352be5053bfa","order":8,"width":0,"height":0,"passthru":false,"label":"RFIＤ區","tooltip":"","color":"","bgcolor":"fuchsia","className":"","icon":"","payload":"","payloadType":"str","topic":"topic","topicType":"msg","x":860,"y":140,"wires":[[]]},{"id":"d22361e93845f13b","type":"ui_button","z":"41b4f910767c89fe","name":"","group":"e7c2632dca7fe97d","order":9,"width":6,"height":1,"passthru":false,"label":"RFIＤ區","tooltip":"","color":"","bgcolor":"fuchsia","className":"","icon":"","payload":"","payloadType":"str","topic":"topic","topicType":"msg","x":860,"y":180,"wires":[[]]},{"id":"9e6097b1322d9e80","type":"ui_button","z":"41b4f910767c89fe","name":"","group":"9bc6b410904f7720","order":2,"width":5,"height":1,"passthru":false,"label":"RFIＤ區","tooltip":"","color":"","bgcolor":"fuchsia","className":"","icon":"","payload":"","payloadType":"str","topic":"topic","topicType":"msg","x":860,"y":220,"wires":[[]]},{"id":"e7c2632dca7fe97d","type":"ui_group","name":"資料庫控制區","tab":"a70850925ff05e71","order":4,"disp":true,"width":6,"collapse":false,"className":""},{"id":"11d8514.a44dcaf","type":"ui_group","name":"新增","tab":"8f1ada5fa4df30e2","order":2,"disp":true,"width":6,"collapse":false,"className":""},{"id":"0487352be5053bfa","type":"ui_group","name":"LED輸入控制區","tab":"a70850925ff05e71","order":1,"disp":true,"width":5,"collapse":false,"className":""},{"id":"a4c15c0e38a27cca","type":"sqlitedb","db":"C:\\Users\\User\\.node-red\\EX2_3_LED.db","mode":"RWC"},{"id":"b0c194b692c58c9c","type":"ui_group","name":"LED資料顯示區","tab":"a70850925ff05e71","order":2,"disp":true,"width":8,"collapse":false,"className":""},{"id":"9bc6b410904f7720","type":"ui_group","name":"單筆控制輸入區","tab":"a70850925ff05e71","order":5,"disp":true,"width":5,"collapse":false,"className":""},{"id":"841df58d.ee5e98","type":"mqtt-broker","name":"","broker":"broker.hivemq.com","port":"1883","clientid":"","autoConnect":true,"usetls":false,"compatmode":false,"protocolVersion":"4","keepalive":"15","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","birthMsg":{},"closeTopic":"","closePayload":"","closeMsg":{},"willTopic":"","willQos":"0","willPayload":"","willMsg":{},"userProps":"","sessionExpiry":""},{"id":"2aec78ed5dc0600c","type":"ui_group","name":"RFID資料顯示區","tab":"a70850925ff05e71","order":3,"disp":true,"width":8,"collapse":false,"className":""},{"id":"0357a00ce0e2564b","type":"sqlitedb","db":"C:\\Users\\User\\.node-red\\EX2_3RFID.db","mode":"RWC"},{"id":"f87c808cc786ae80","type":"sqlitedb","db":"C:\\Users\\User\\.node-red\\EX2_2.db","mode":"RWC"},{"id":"a70850925ff05e71","type":"ui_tab","name":"作業2-3","icon":"dashboard","order":91,"disabled":false,"hidden":false},{"id":"8f1ada5fa4df30e2","type":"ui_tab","name":"作業2-1","icon":"dashboard","disabled":false,"hidden":false}]

#include "arduino_secrets.h"

組譯錯誤代表

缺少 arduino_secrets.h 檔案

裡面內容有2行

#define SECRET_SSID ""

#define SECRET_PASS ""

Arduino程式

//定義MFRC522 RFID read 與 ESP32 介面 接腳連接Pin assign
/* Wiring RFID RC522 module  
==============================================================
GND     = GND   3.3V    = 3.3V
The following table shows the typical pin layout used:
 *             MFRC522      ESP32     
 *             Reader/PCD             
 * Signal      Pin          Pin         
 * -----------------------------------
 * RST/Reset   RST          GPIO27   
 * SPI SS      SDA(SS)      GPIO5     
 * SPI MOSI    MOSI         GPIO23    
 * SPI MISO    MISO         GPIO19    
 * SPI SCK     SCK          GPIO18    
 *
[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
=============================================================
*/
// Wifi 與 MQttClient 程式庫
#include <ArduinoMqttClient.h>
#include <WiFi.h>
#include "arduino_secrets.h"

//MFRC522 程式庫
#include <SPI.h>
#include <MFRC522.h>

//GPIO 2 D1 Build in LED

#define LED 13           //定義LED接腳

///////please enter your sensitive data in the Secret tab/arduino_secrets.h
//char ssid[] = "TOTOLINK_A3002MU";    // your network SSID (name)
//char pass[] = "24063173";    // your network password (use for WPA, or use as key for WEP)
// WiFi SSID password , SSID 和密碼進行Wi-Fi 設定 
//const char ssid[] = "alex9ufo"; // Enter your Wi-Fi name
//const char pass[] = "alex9981";  // Enter Wi-Fi password

char ssid[] = "dlink-103A";    // your network SSID (name)
char pass[] = "bdcce12882";    // your network password (use for WPA, or use as key for WEP)

WiFiClient wifiClient;
MqttClient mqttClient(wifiClient);

//const char broker[] = "test.mosquitto.org";
const char broker[] = "broker.mqtt-dashboard.com";
int        port     = 1883;
String json = "";
const char *topic1 = "alex9ufo/esp32/led";
const char *topic2 = "alex9ufo/esp32/led_status";
const char *topic3 = "alex9ufo/esp32/RFID";

//const char *topic1 = "alex9ufo/RFID/read";
//const char *topic3 = "alex9ufo/RFID/back";
const char willTopic[] = "alex9ufo/esp32/Starting";
//======================================================
#define RST_PIN      27        // 讀卡機的重置腳位
#define SS_PIN       5        // 晶片選擇腳位
MFRC522 mfrc522(SS_PIN, RST_PIN);    // 建立MFRC522物件
MFRC522::MIFARE_Key key;  // 儲存金鑰
MFRC522::StatusCode status;
//===========================================================
//布林代數 LED狀態 是否連上網路ESP32 ready ?
bool ledState = false;
bool atwork = false;
bool Flash = false;  //true
bool Timer = false;  //true
bool Send = false;  //true
String LEDjson = "";
int Count= 0;
//===========================================================
void onMqttMessage(int messageSize) {
  // we received a message, print out the topic and contents
  Serial.print("Received a message with topic '");
  Serial.print(mqttClient.messageTopic());
  String Topic= mqttClient.messageTopic();
  Serial.print("', duplicate = ");
  Serial.print(mqttClient.messageDup() ? "true" : "false");
  Serial.print(", QoS = ");
  Serial.print(mqttClient.messageQoS());
  Serial.print(", retained = ");
  Serial.print(mqttClient.messageRetain() ? "true" : "false");
  Serial.print("', length ");
  Serial.print(messageSize);
  Serial.println(" bytes:");
  String message="";
  // use the Stream interface to print the contents
  while (mqttClient.available()) {
    //Serial.print((char)mqttClient.read());
    message += (char)mqttClient.read();
  }

  Serial.println(message);
  message.trim();
  Topic.trim();

  if (Topic=="alex9ufo/esp32/led") {
  if (message == "on") {
    digitalWrite(LED, LOW);  // Turn on the LED
    ledState = true;  //ledState = ture HIGH
    //設定 各個 旗號
    Flash = false;
    Timer = false;
    LEDjson ="ON";
    Send = true ;
    Serial.print("LED =");
    Serial.println(LEDjson);
    }
  if (message == "off" ) {
    digitalWrite(LED, HIGH); // Turn off the LED
    ledState = false; //ledState = false LOW
    Flash = false;
    Timer = false;
    LEDjson ="OFF";
    Send = true ;
    Serial.print("LED =");
    Serial.println(LEDjson);
  }

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

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

  if (message == "toggle" ) {
    digitalWrite(LED, !digitalRead(LED));   // Turn the LED toggle
    Flash = false;
    Timer = false;
    LEDjson ="TOGGLE";
    Send = true ; 
    Serial.print("LED =");
    Serial.println(LEDjson);       
  }

    Serial.println();
    Serial.println("-----------------------");
  }  
}

//===========================================================
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);
        id +=" ";
      }
      return id;
}
//===========================================================
//副程式  setup wifi
void setup_wifi() {
  delay(10);
  // We start by connecting to a WiFi network
  Serial.println();
  Serial.print("Connecting to ");
  Serial.println(ssid);     //print ssid
  WiFi.begin(ssid, pass);  //初始化WiFi 函式庫並回傳目前的網路狀態
  while (WiFi.status() != WL_CONNECTED) {
    delay(500);
    Serial.print(".");
  }   //假設 wifi 未連接 show ………

  Serial.println("");
  Serial.println("WiFi connected");
  Serial.println("IP address: ");
  Serial.println(WiFi.localIP());
}  
//===========================================================
//判斷 旗號Flash , Timer 是否為真
void LED_Message() {
  if (Flash){
    digitalWrite(LED, !digitalRead(LED));
    delay(500);
    if (digitalRead(LED))
      ledState = true;
    else
      ledState = false;
  } //(Flash)

  if (Timer) {
    digitalWrite(LED,LOW );
    delay(500);
    if (digitalRead(LED))
      ledState = true;
    else
      ledState = false;

    Count=Count-1;
    if (Count == 0 ){
      Timer=false;
      digitalWrite(LED, HIGH);
      ledState = false;
    }
  } //(Timer)

  ////判斷 旗號 Send 是否為真 回傳MQTT訊息到MQTT Broker　
  if (Send) {
    // Convert JSON string to character array
    Serial.print("Publish message: ");
    Serial.println(LEDjson);
    LEDjson.trim();

    bool retained = false;
    int qos = 1;
    bool dup = false;
    
    // Publish JSON character array to MQTT topic
    mqttClient.beginMessage(topic2,  LEDjson.length(), retained, qos, dup);  //LED Status
    mqttClient.print(LEDjson);
    mqttClient.endMessage();
    Send = false;    //處理過後 旗號 Send為假
  }

} 
//=========================================================== 
void setup() {
  pinMode(LED, OUTPUT);
  digitalWrite(LED, HIGH);  // Turn off the LED initially
  //Initialize serial and wait for port to open: 
  Serial.begin(115200);   // Initialize serial communications with the PC
  while (!Serial);    // Do nothing if no serial port is opened (added for Arduinos based on ATMEGA32U4)
  
  setup_wifi();
  Serial.println("You're connected to the network");
  Serial.println();
  
  String willPayload = "ESP32 Start working....!";
  bool willRetain = true;
  int willQos = 1;

  mqttClient.beginWill(willTopic, willPayload.length(), willRetain, willQos);
  mqttClient.print(willPayload);
  mqttClient.endWill();

  Serial.print("Attempting to connect to the MQTT broker: ");
  Serial.println(broker);

  if (!mqttClient.connect(broker, port)) {
    Serial.print("MQTT connection failed! Error code = ");
    Serial.println(mqttClient.connectError());

    while (1);
  }

  Serial.println("You're connected to the MQTT broker!");
  Serial.println();

  // set the message receive callback
  mqttClient.onMessage(onMqttMessage);
  Serial.print("Subscribing to topic: ");
  Serial.println(topic1);
  // subscribe to a topic
  // the second parameter sets the QoS of the subscription,
  // the the library supports subscribing at QoS 0, 1, or 2
  int subscribeQos = 1;
  mqttClient.subscribe(topic1, subscribeQos);


  Serial.println();
  SPI.begin();      // Init SPI bus
  mfrc522.PCD_Init();   // Init MFRC522
  delay(4);       // Optional delay. Some board do need more time after init to be ready, see Readme
  //mfrc522.PCD_DumpVersionToSerial();  // Show details of PCD - MFRC522 Card Reader details
  Serial.println(F("Scan PICC to see UID, SAK, type, and data blocks..."));
}
//===========================================================
void loop() {
  
  // call poll() regularly to allow the library to receive MQTT messages and
  // send MQTT keep alives which avoids being disconnected by the broker
  mqttClient.poll();
  
  LED_Message();
  // to avoid having delays in loop, we'll use the strategy from BlinkWithoutDelay
  // see: File -> Examples -> 02.Digital -> BlinkWithoutDelay for more info
  unsigned long currentMillis = millis();


 if (mfrc522.PICC_IsNewCardPresent() && mfrc522.PICC_ReadCardSerial()) {
    
     Serial.println(F("Please scan MIFARE Classic card..."));
    // 確認是否有新卡片
    
    byte *id = mfrc522.uid.uidByte;   // 取得卡片的UID
    byte idSize = mfrc522.uid.size;   // 取得UID的長度
    String Type;
    Serial.print("PICC type: ");      // 顯示卡片類型
    // 根據卡片回應的SAK值（mfrc522.uid.sak）判斷卡片類型
    MFRC522::PICC_Type piccType = mfrc522.PICC_GetType(mfrc522.uid.sak);
    Type= mfrc522.PICC_GetTypeName(piccType);
    Serial.println(mfrc522.PICC_GetTypeName(piccType));

    Serial.print("UID Size: ");       // 顯示卡片的UID長度值
    Serial.println(idSize);
 
    for (byte i = 0; i < idSize; i++) {  // 逐一顯示UID碼
      Serial.print("id[");
      Serial.print(i);
      Serial.print("]: ");
      Serial.println(id[i], HEX);       // 以16進位顯示UID值
    }
    Serial.println();



    json="";
    //json = json +(" Card UID: ");
    String json1=printHex(mfrc522.uid.uidByte, mfrc522.uid.size);
    json1.toUpperCase();
    json = json + json1;
    json.trim();
    //json = json +(" , PICC type: ");
    //json =  json + Type;
    //json.trim();

    bool retained = false;
    int qos = 1;
    bool dup = false;

    mqttClient.beginMessage(topic3,  json.length(), retained, qos, dup);
    mqttClient.print(json);
    mqttClient.endMessage();

    Serial.println();
    // Dump debug info about the card; PICC_HaltA() is automatically called
    // 令卡片進入停止狀態
    // Dump debug info about the card; PICC_HaltA() is automatically called
    // mfrc522.PICC_DumpToSerial(&(mfrc522.uid));
    
    mfrc522.PICC_HaltA();
    mfrc522.PCD_StopCrypto1(); // stop encryption on PCD
  }
}
////===========================================================

alex9ufo 聰明人求知心切

2024年1月30日星期二

2024作業2-3 ESP32 +( RFID +LED) + (Node-Red & SQLite ) + Line Notify

113 學年度第 1 學期 RFID應用課程 Arduino程式

2024年1月30日 星期二

2024作業2-3 ESP32 +( RFID +LED) + (Node-Red & SQLite ) + Line Notify

113 學年度第 1 學期 RFID應用課程 Arduino程式

2024年1月30日星期二