作業2-1 LED + (Node-Red & SQLite )+Line Notify
1) Arduino 程式
https://alex9ufoexploer.blogspot.com/2023/11/2-arduino-2-1-2-2-2-3.html
2) 系統圖
3) Node-Red 畫面暨程式
4) Node-Red程式
資料庫 名稱 位置 C:\Users\User\.node-red\EX2_1.db
(所有的節點 都要修改)
CREATE TABLE LEDSTATUS (
id INTEGER,
STATUS TEXT,
Date DATE,
Time TIME,
PRIMARY KEY (id)
);
[{"id":"4a40fc27017c4aee","type":"inject","z":"0fce84474a62af64","name":"CREATE","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"CREATE TABLE LEDSTATUS (id INTEGER,STATUS TEXT,Date DATE,Time TIME,PRIMARY KEY (id));","payload":"","payloadType":"date","x":120,"y":80,"wires":[["c0d2ae99a4a73349"]]},{"id":"a4cb50b05f479276","type":"inject","z":"0fce84474a62af64","name":"SELECT","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"SELECT * FROM LEDSTATUS","payload":"","payloadType":"date","x":120,"y":160,"wires":[["c0d2ae99a4a73349"]]},{"id":"e415790f1e84d8a8","type":"inject","z":"0fce84474a62af64","name":"INSERT","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"INSERT INTO LEDSTATUS (STATUS , Date , Time )values(\"on\", \"11/01\" , \"21:05\") ","payload":"","payloadType":"date","x":120,"y":120,"wires":[["c0d2ae99a4a73349"]]},{"id":"d375168ccfdb75e5","type":"inject","z":"0fce84474a62af64","name":"DELETE","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"DELETE from LEDSTATUS","payload":"","payloadType":"date","x":120,"y":200,"wires":[["c0d2ae99a4a73349"]]},{"id":"05a9d9bdea7d1628","type":"inject","z":"0fce84474a62af64","name":"DROP TABLE","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"DROP TABLE LEDSTATUS","payload":"","payloadType":"date","x":130,"y":240,"wires":[["c0d2ae99a4a73349"]]},{"id":"c0d2ae99a4a73349","type":"sqlite","z":"0fce84474a62af64","mydb":"5717b18ae717d72d","sqlquery":"msg.topic","sql":"","name":"LED_STATUS","x":380,"y":160,"wires":[["1956224795d14e15"]]},{"id":"1956224795d14e15","type":"debug","z":"0fce84474a62af64","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":550,"y":160,"wires":[]},{"id":"7c3e8c967f2eb1af","type":"comment","z":"0fce84474a62af64","name":"TABLE LEDSTATUS","info":"CREATE TABLE LEDSTATUS (\nid INTEGER,\nSTATUS TEXT,\nDate DATE,\nTime TIME,\nPRIMARY KEY (id)\n);","x":130,"y":40,"wires":[]},{"id":"60cd47793ea467da","type":"sqlite","z":"0fce84474a62af64","mydb":"4e71ffcc.32ba8","sqlquery":"msg.topic","sql":"","name":"LED_STATUS","x":520,"y":320,"wires":[["3f5d47f9347bd813"]]},{"id":"86890c516abc64f3","type":"function","z":"0fce84474a62af64","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":320,"y":320,"wires":[["60cd47793ea467da"]]},{"id":"b71764bc7d416065","type":"ui_button","z":"0fce84474a62af64","name":"","group":"4392f6e50bbac53e","order":7,"width":0,"height":0,"passthru":false,"label":"建立資料庫","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"建立資料庫","payloadType":"str","topic":"topic","topicType":"msg","x":110,"y":320,"wires":[["86890c516abc64f3","af73b77670b68487"]]},{"id":"af73b77670b68487","type":"ui_audio","z":"0fce84474a62af64","name":"","group":"11d8514.a44dcaf","voice":"Microsoft Hanhan - Chinese (Traditional, Taiwan)","always":true,"x":235,"y":280,"wires":[],"l":false},{"id":"92e3aaf631114a93","type":"ui_button","z":"0fce84474a62af64","name":"","group":"11d8514.a44dcaf","order":1,"width":0,"height":0,"passthru":false,"label":"ON","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"on","payloadType":"str","topic":"topic","topicType":"msg","x":90,"y":360,"wires":[["d1059aaa9c5af665","a23a21b05a85191f","be407a41ce22b543","7e0f729ac9e41697"]]},{"id":"0b6f9a928c5974e3","type":"ui_button","z":"0fce84474a62af64","name":"","group":"11d8514.a44dcaf","order":2,"width":0,"height":0,"passthru":false,"label":"OFF","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"off","payloadType":"str","topic":"topic","topicType":"msg","x":90,"y":400,"wires":[["d1059aaa9c5af665","a23a21b05a85191f","be407a41ce22b543","7e0f729ac9e41697"]]},{"id":"f7c8be64ba8c0a1b","type":"ui_button","z":"0fce84474a62af64","name":"","group":"11d8514.a44dcaf","order":3,"width":0,"height":0,"passthru":false,"label":"TOGGLE","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"toggle","payloadType":"str","topic":"topic","topicType":"msg","x":100,"y":440,"wires":[["d1059aaa9c5af665","a23a21b05a85191f","be407a41ce22b543","7e0f729ac9e41697"]]},{"id":"95883ea8be2abb9b","type":"ui_button","z":"0fce84474a62af64","name":"","group":"11d8514.a44dcaf","order":4,"width":0,"height":0,"passthru":false,"label":"TIMER","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"timer","payloadType":"str","topic":"topic","topicType":"msg","x":100,"y":480,"wires":[["d1059aaa9c5af665","a23a21b05a85191f","be407a41ce22b543","7e0f729ac9e41697"]]},{"id":"c8aa802f0a761174","type":"ui_button","z":"0fce84474a62af64","name":"","group":"11d8514.a44dcaf","order":5,"width":0,"height":0,"passthru":false,"label":"FLASH","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"flash","payloadType":"str","topic":"topic","topicType":"msg","x":100,"y":520,"wires":[["a23a21b05a85191f","d1059aaa9c5af665","be407a41ce22b543","7e0f729ac9e41697"]]},{"id":"a23a21b05a85191f","type":"function","z":"0fce84474a62af64","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":300,"y":460,"wires":[["b9d317cad4523445"]]},{"id":"d1059aaa9c5af665","type":"ui_audio","z":"0fce84474a62af64","name":"","group":"11d8514.a44dcaf","voice":"Microsoft Hanhan - Chinese (Traditional, Taiwan)","always":true,"x":255,"y":420,"wires":[],"l":false},{"id":"b9d317cad4523445","type":"sqlite","z":"0fce84474a62af64","mydb":"4e71ffcc.32ba8","sqlquery":"msg.topic","sql":"","name":"LED_STATUS","x":480,"y":460,"wires":[["1723ef473e3dde7e","d5663f9afbb3fa6d"]]},{"id":"3f5d47f9347bd813","type":"debug","z":"0fce84474a62af64","name":"debug ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":690,"y":320,"wires":[]},{"id":"1723ef473e3dde7e","type":"debug","z":"0fce84474a62af64","name":"debug ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":690,"y":460,"wires":[]},{"id":"d135a6ec97fba847","type":"ui_button","z":"0fce84474a62af64","name":"","group":"4392f6e50bbac53e","order":8,"width":0,"height":0,"passthru":false,"label":"檢視資料庫資料","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"檢視資料","payloadType":"str","topic":"topic","topicType":"msg","x":120,"y":620,"wires":[["d5663f9afbb3fa6d","2e1fb770146e7bd5"]]},{"id":"d5663f9afbb3fa6d","type":"function","z":"0fce84474a62af64","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":340,"y":620,"wires":[["be334eb3c9c72c77"]]},{"id":"c833d46f61a95f22","type":"ui_table","z":"0fce84474a62af64","group":"821196f103500796","name":"","order":1,"width":10,"height":10,"columns":[],"outputs":0,"cts":false,"x":790,"y":620,"wires":[]},{"id":"be334eb3c9c72c77","type":"sqlite","z":"0fce84474a62af64","mydb":"4e71ffcc.32ba8","sqlquery":"msg.topic","sql":"","name":"LED_STATUS","x":520,"y":620,"wires":[["c833d46f61a95f22"]]},{"id":"9244615e88c96c40","type":"ui_button","z":"0fce84474a62af64","name":"","group":"4392f6e50bbac53e","order":1,"width":2,"height":1,"passthru":false,"label":"刪除資料庫 ","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"刪除資料庫 ","payloadType":"str","topic":"topic","topicType":"msg","x":110,"y":860,"wires":[["2d3cc6f5ba1af6f0","7ca5dd04f5e01509"]]},{"id":"cd1d9de75df07cd0","type":"function","z":"0fce84474a62af64","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":520,"y":800,"wires":[["28ecf1d0f6f3982f"]]},{"id":"28ecf1d0f6f3982f","type":"sqlite","z":"0fce84474a62af64","mydb":"4e71ffcc.32ba8","sqlquery":"msg.topic","sql":"","name":"LED_STATUS","x":740,"y":720,"wires":[["3f3c89fd5a478259"]]},{"id":"d71be695ba437ed1","type":"ui_button","z":"0fce84474a62af64","name":"","group":"4392f6e50bbac53e","order":2,"width":2,"height":1,"passthru":false,"label":"刪除所有資料 ","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"刪除所有資料 ","payloadType":"str","topic":"topic","topicType":"msg","x":120,"y":760,"wires":[["7ca5dd04f5e01509","d74709cfd5386eb4"]]},{"id":"6f16cd694e3efb89","type":"function","z":"0fce84474a62af64","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":390,"y":700,"wires":[["28ecf1d0f6f3982f"]]},{"id":"2d3cc6f5ba1af6f0","type":"ui_toast","z":"0fce84474a62af64","position":"prompt","displayTime":"3","highlight":"","sendall":true,"outputs":1,"ok":"OK","cancel":"Cancel","raw":true,"className":"","topic":"","name":"","x":310,"y":860,"wires":[["9acf06a4cf765e75"]]},{"id":"9acf06a4cf765e75","type":"function","z":"0fce84474a62af64","name":"function 84","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":490,"y":860,"wires":[["cd1d9de75df07cd0"],[]]},{"id":"7ca5dd04f5e01509","type":"ui_audio","z":"0fce84474a62af64","name":"","group":"11d8514.a44dcaf","voice":"Microsoft Hanhan - Chinese (Traditional, Taiwan)","always":true,"x":205,"y":800,"wires":[],"l":false},{"id":"d74709cfd5386eb4","type":"ui_toast","z":"0fce84474a62af64","position":"prompt","displayTime":"3","highlight":"","sendall":true,"outputs":1,"ok":"OK","cancel":"Cancel","raw":true,"className":"","topic":"","name":"","x":330,"y":760,"wires":[["eeaa85327128f533"]]},{"id":"eeaa85327128f533","type":"function","z":"0fce84474a62af64","name":"function 85","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":490,"y":760,"wires":[["6f16cd694e3efb89"],[]]},{"id":"2e1fb770146e7bd5","type":"ui_audio","z":"0fce84474a62af64","name":"","group":"11d8514.a44dcaf","voice":"Microsoft Hanhan - Chinese (Traditional, Taiwan)","always":true,"x":195,"y":660,"wires":[],"l":false},{"id":"968be0bad8c6a400","type":"ui_button","z":"0fce84474a62af64","name":"","group":"4392f6e50bbac53e","order":3,"width":2,"height":1,"passthru":false,"label":"查詢一筆資料","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"查詢一筆資料","payloadType":"str","topic":"topic","topicType":"msg","x":120,"y":920,"wires":[["7ca5dd04f5e01509","cc4f8309b006ace3"]]},{"id":"1d68074d89550fe0","type":"function","z":"0fce84474a62af64","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":660,"y":920,"wires":[["a7451617bea651ab"]]},{"id":"a7451617bea651ab","type":"sqlite","z":"0fce84474a62af64","mydb":"4e71ffcc.32ba8","sqlquery":"msg.topic","sql":"","name":"LED_STATUS","x":720,"y":840,"wires":[["c833d46f61a95f22"]]},{"id":"1b74c7a4478ca5ac","type":"ui_button","z":"0fce84474a62af64","name":"","group":"4392f6e50bbac53e","order":4,"width":2,"height":1,"passthru":false,"label":"刪除一筆資料","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"刪除一筆資料","payloadType":"str","topic":"topic","topicType":"msg","x":120,"y":980,"wires":[["b6bffe3d5d1a6d85","c1208026cc2835a5"]]},{"id":"b6bffe3d5d1a6d85","type":"ui_audio","z":"0fce84474a62af64","name":"","group":"11d8514.a44dcaf","voice":"Microsoft Hanhan - Chinese (Traditional, Taiwan)","always":true,"x":255,"y":1020,"wires":[],"l":false},{"id":"d4eb7ebe30fccb1a","type":"ui_form","z":"0fce84474a62af64","name":"","label":"輸入id","group":"e48ffa90611225eb","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":510,"y":980,"wires":[["1d68074d89550fe0","acef5c22c3f96d7c","aa219bb4f9628fd7"]]},{"id":"acef5c22c3f96d7c","type":"function","z":"0fce84474a62af64","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":680,"y":980,"wires":[["8686af22dec010df","7e14c2085b11e5a6"]]},{"id":"3f3c89fd5a478259","type":"link out","z":"0fce84474a62af64","name":"link out 38","mode":"link","links":["c2630df45cb7cf5a"],"x":875,"y":720,"wires":[]},{"id":"c2630df45cb7cf5a","type":"link in","z":"0fce84474a62af64","name":"link in 35","links":["3f3c89fd5a478259"],"x":255,"y":660,"wires":[["d5663f9afbb3fa6d"]]},{"id":"8686af22dec010df","type":"sqlite","z":"0fce84474a62af64","mydb":"4e71ffcc.32ba8","sqlquery":"msg.topic","sql":"","name":"LED_STATUS","x":920,"y":980,"wires":[["3f3c89fd5a478259"]]},{"id":"68efbd423e887bff","type":"ui_button","z":"0fce84474a62af64","name":"","group":"4392f6e50bbac53e","order":5,"width":2,"height":1,"passthru":false,"label":"更正一筆資料","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"更正一筆資料","payloadType":"str","topic":"topic","topicType":"msg","x":120,"y":1060,"wires":[["b6bffe3d5d1a6d85","acb3259d5ea386b4"]]},{"id":"e30ac89122be88d0","type":"comment","z":"0fce84474a62af64","name":"UPDATE查詢的WHERE","info":"UPDATE查詢的WHERE子句的基本語法如下：\n\nUPDATE table_name\nSET column1 = value1, column2 = value2...., columnN = valueN\nWHERE [condition];","x":140,"y":1100,"wires":[]},{"id":"81dc801b7395682e","type":"function","z":"0fce84474a62af64","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":780,"y":1100,"wires":[["8686af22dec010df","4f380e124a31dff3"]]},{"id":"cc4f8309b006ace3","type":"function","z":"0fce84474a62af64","name":"function flow set1","func":"var s1=1;\nglobal.set(\"SEL1\",s1);\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":330,"y":920,"wires":[["d4eb7ebe30fccb1a"]]},{"id":"c1208026cc2835a5","type":"function","z":"0fce84474a62af64","name":"function flow set2","func":"var s1=2;\nglobal.set(\"SEL2\",s1);\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":330,"y":980,"wires":[["d4eb7ebe30fccb1a"]]},{"id":"acb3259d5ea386b4","type":"function","z":"0fce84474a62af64","name":"function flow set3","func":"var s1=3;\nglobal.set(\"SEL3\",s1);\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":330,"y":1060,"wires":[["d4eb7ebe30fccb1a"]]},{"id":"4db9cf0725d987e7","type":"ui_form","z":"0fce84474a62af64","name":"","label":"更正欄位","group":"e48ffa90611225eb","order":2,"width":0,"height":0,"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":600,"y":1100,"wires":[["81dc801b7395682e"]]},{"id":"7e14c2085b11e5a6","type":"debug","z":"0fce84474a62af64","name":"debug 213","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"topic","targetType":"msg","statusVal":"","statusType":"auto","x":890,"y":940,"wires":[]},{"id":"aa219bb4f9628fd7","type":"function","z":"0fce84474a62af64","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":610,"y":1040,"wires":[["4db9cf0725d987e7","2b9eedaea9204c7c"]]},{"id":"2b9eedaea9204c7c","type":"function","z":"0fce84474a62af64","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":800,"y":1040,"wires":[["a7451617bea651ab"]]},{"id":"4f380e124a31dff3","type":"debug","z":"0fce84474a62af64","name":"debug 214","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"topic","targetType":"msg","statusVal":"","statusType":"auto","x":970,"y":1100,"wires":[]},{"id":"c79345e97d930648","type":"comment","z":"0fce84474a62af64","name":"資料庫位置 C:\\Users\\User\\.node-red\\EX2_1.db","info":"","x":460,"y":120,"wires":[]},{"id":"5d641321bfdfbf15","type":"mqtt in","z":"0fce84474a62af64","name":"LED status ","topic":"alex9ufo/esp32/led_status","qos":"2","datatype":"utf8","broker":"70940176.2b2d3","nl":false,"rap":true,"rh":0,"inputs":0,"x":120,"y":1160,"wires":[["fb140769705e273b"]]},{"id":"fb140769705e273b","type":"function","z":"0fce84474a62af64","name":"function ","func":"msg.payload=\" ---ESP32回來資料---\" +msg.payload;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":280,"y":1160,"wires":[["2c387813f211da41","6ae1285801fdfac9","6b9bb575755aa602"]]},{"id":"2c387813f211da41","type":"function","z":"0fce84474a62af64","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":470,"y":1140,"wires":[["ab636fb8db587e31"]]},{"id":"ab636fb8db587e31","type":"http request","z":"0fce84474a62af64","name":"","method":"POST","ret":"txt","paytoqs":false,"url":"https://notify-api.line.me/api/notify","tls":"","persist":false,"proxy":"","authType":"","x":640,"y":1140,"wires":[["518658013844da1f"]]},{"id":"518658013844da1f","type":"debug","z":"0fce84474a62af64","name":"debug 216","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":790,"y":1140,"wires":[]},{"id":"5f285af41714e6b3","type":"comment","z":"0fce84474a62af64","name":"Line Notify Message ","info":"","x":810,"y":1180,"wires":[]},{"id":"be407a41ce22b543","type":"mqtt out","z":"0fce84474a62af64","name":"Control LED","topic":"alex9ufo/esp32/led","qos":"1","retain":"true","respTopic":"","contentType":"","userProps":"","correl":"","expiry":"","broker":"70940176.2b2d3","x":310,"y":360,"wires":[]},{"id":"f7fcccffe983a8bf","type":"comment","z":"0fce84474a62af64","name":"alex9ufo/esp32/led","info":"","x":370,"y":400,"wires":[]},{"id":"7e0f729ac9e41697","type":"ui_text","z":"0fce84474a62af64","group":"11d8514.a44dcaf","order":6,"width":6,"height":1,"name":"","label":"Node-RED發行到MQTT的資料 : ","format":"{{msg.payload}}","layout":"row-left","className":"","x":370,"y":500,"wires":[]},{"id":"6b9bb575755aa602","type":"ui_text","z":"0fce84474a62af64","group":"4392f6e50bbac53e","order":8,"width":12,"height":1,"name":"","label":"Node-RED 訂閱MQTT的資料 : ","format":"{{msg.payload}}","layout":"row-left","className":"","x":530,"y":1220,"wires":[]},{"id":"11b6ff83d92b0791","type":"ui_audio","z":"0fce84474a62af64","name":"","group":"4392f6e50bbac53e","voice":"Microsoft Hanhan - Chinese (Traditional, Taiwan)","always":"","x":620,"y":1180,"wires":[]},{"id":"6ae1285801fdfac9","type":"delay","z":"0fce84474a62af64","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":460,"y":1180,"wires":[["11b6ff83d92b0791"]]},{"id":"5717b18ae717d72d","type":"sqlitedb","db":"C:\\Users\\User\\.node-red\\EX2_1.db","mode":"RWC"},{"id":"4e71ffcc.32ba8","type":"sqlitedb","db":"C:\\Users\\User\\.node-red\\LED_STATUS.db","mode":"RWC"},{"id":"4392f6e50bbac53e","type":"ui_group","name":"命令","tab":"8f1ada5fa4df30e2","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":"821196f103500796","type":"ui_group","name":"顯示","tab":"8f1ada5fa4df30e2","order":2,"disp":true,"width":10,"collapse":false,"className":""},{"id":"e48ffa90611225eb","type":"ui_group","name":"單筆資料","tab":"8f1ada5fa4df30e2","order":4,"disp":true,"width":4,"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":"8f1ada5fa4df30e2","type":"ui_tab","name":"作業2-1","icon":"dashboard","disabled":false,"hidden":false}]
5) SQLite畫面
6) Line Notify (需改成自己的權杖 不然我會收到各位的Line)
msg.payload = {"message":msg.payload};
return msg;
(Set Line API function內)
我的權杖A4wwPNh2WqB7dlfeQyyIAwtggn1kfZSI5LkkCdia1gB
需改成自己的權杖
//oR7KdXvK1eobRr2sRRgsl4PMq23DjDlhfUs96SyUBZu
沒有留言:
張貼留言