alex9ufo 聰明人求知心切: 作業2-3 LED+RFID & (Node-red + SQLite)+ Line Notify

1)Arduino 程式 (ESP32)

https://alex9ufoexploer.blogspot.com/2023/11/2-arduino-2-1-2-2-2-3.html

2) Line Notify (需改成自己的權杖不然我會收到各位的Line) 修改3處

msg.headers = {'content-type':'application/x-www-form-urlencoded','Authorization':'Bearer A4wwPNh2WqB7dlfeQyyIAwtggn1kfZSI5LkkCdia1gB'};

msg.payload = {"message":msg.payload};

return msg;

3) Line 權杖的取得

https://alex9ufoexploer.blogspot.com/2020/10/line-notify.html

4) Node-Red 匯入程式後先將所有的 SQLite 節點資料庫儲存路徑位置修改

LED的資料庫

目前資料庫位置 C:\Users\User\.node-red\EX2_3_LED.db

CREATE TABLE LEDSTATUS (id INTEGER,STATUS TEXT,Date DATE,Time TIME,PRIMARY KEY (id))

RFIDD的資料庫

目前資料庫位置 C:\Users\User\.node-red\EX2_3RFID.db

CREATE TABLE RFIDtable(id INTEGER PRIMARY KEY AUTOINCREMENT, uidname TEXT, currentdate DATE, currenttime TIME)

5) 資料庫動作前須先建立資料庫

6) 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":"70940176.2b2d3","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":"70940176.2b2d3","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_1.db","info":"","x":1300,"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,"x":1970,"y":1380,"wires":[["94c9e9772fb6ccbf"]]},{"id":"87b950831ca240c0","type":"comment","z":"41b4f910767c89fe","name":"Line Notify Message ","info":"","x":2150,"y":1420,"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":"70940176.2b2d3","type":"mqtt-broker","name":"","broker":"broker.mqtt-dashboard.com","port":"1883","clientid":"","autoConnect":true,"usetls":false,"protocolVersion":4,"keepalive":"15","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closePayload":"","willTopic":"","willQos":"0","willPayload":""},{"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}]

alex9ufo 聰明人求知心切

2023年11月16日星期四

作業2-3 LED+RFID & (Node-red + SQLite)+ Line Notify

沒有留言:

張貼留言

WOKWI DHT22 & LED , Node-Red + SQLite database

2023年11月16日 星期四

作業2-3 LED+RFID & (Node-red + SQLite)+ Line Notify

沒有留言:

張貼留言

WOKWI DHT22 & LED , Node-Red + SQLite database

2023年11月16日星期四