Node-Red 一週定時器
Node-Red程式
[{"id":"2f8a4f238e4fb4b8","type":"ui_dropdown","z":"c4415dc7a5e4d922","name":"週選擇","label":"星期","tooltip":"","place":"","group":"7a977e3045843f01","order":1,"width":3,"height":1,"passthru":true,"multiple":false,"options":[{"label":"Monday","value":"Monday","type":"str"},{"label":"Tuesday","value":"Tuesday","type":"str"},{"label":"Wednesday","value":"Wednesday","type":"str"},{"label":"Thursday","value":"Thursday","type":"str"},{"label":"Friday","value":"Friday","type":"str"},{"label":"Saturday","value":"Saturday","type":"str"},{"label":"Sunday","value":"Sunday","type":"str"}],"payload":"","topic":"week","topicType":"str","className":"","x":70,"y":40,"wires":[["c1df939e90109295"]]},{"id":"ee5f9d7ca8083a3c","type":"ui_text_input","z":"c4415dc7a5e4d922","name":"起始時間","label":"起始時間 (HH:mm:ss)","tooltip":"","group":"7a977e3045843f01","order":2,"width":3,"height":1,"passthru":true,"mode":"text","delay":"","topic":"start_time","sendOnBlur":true,"className":"","topicType":"str","x":80,"y":80,"wires":[["c1df939e90109295"]]},{"id":"2e7fe482d57c8e37","type":"ui_text_input","z":"c4415dc7a5e4d922","name":"結束時間","label":"結束時間 (HH:mm:ss)","tooltip":"","group":"7a977e3045843f01","order":3,"width":3,"height":1,"passthru":true,"mode":"text","delay":"","topic":"end_time","sendOnBlur":true,"className":"","topicType":"str","x":80,"y":120,"wires":[["c1df939e90109295"]]},{"id":"a5dee6f906cf9420","type":"ui_button","z":"c4415dc7a5e4d922","name":"新增","group":"7a977e3045843f01","order":4,"width":2,"height":1,"label":"新增設定","x":70,"y":260,"wires":[["71c2be76dc301288"]]},{"id":"1054a75c54b24a7c","type":"ui_text_input","z":"c4415dc7a5e4d922","name":"刪除ID","label":"欲刪除 ID","tooltip":"","group":"7a977e3045843f01","order":5,"width":2,"height":1,"passthru":true,"mode":"text","delay":"","topic":"delete_id","sendOnBlur":true,"className":"","topicType":"str","x":70,"y":320,"wires":[["f889ef6f7f18ee8b"]]},{"id":"56bb7cc9076f3945","type":"ui_button","z":"c4415dc7a5e4d922","name":"刪除","group":"7a977e3045843f01","order":6,"width":2,"height":1,"label":"刪除設定","x":70,"y":360,"wires":[["f889ef6f7f18ee8b"]]},{"id":"0aa61dfeb742e413","type":"ui_button","z":"c4415dc7a5e4d922","name":"顯示全部","group":"7a977e3045843f01","order":7,"width":3,"height":1,"passthru":false,"label":"顯示全部設定","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"","payloadType":"str","topic":"","topicType":"str","x":80,"y":400,"wires":[["42ec6cae24919434"]]},{"id":"f79911b3be40aa0f","type":"ui_dropdown","z":"c4415dc7a5e4d922","name":"模式選擇","label":"控制模式","tooltip":"","place":"","group":"7a977e3045843f01","order":8,"width":3,"height":1,"passthru":true,"multiple":false,"options":[{"label":"自動","value":"auto","type":"str"},{"label":"手動 ON","value":"manual_on","type":"str"},{"label":"手動 OFF","value":"manual_off","type":"str"}],"payload":"","topic":"mode","topicType":"str","className":"","x":80,"y":460,"wires":[["3f05c19f87227f82"]]},{"id":"184e1bf5056bfe48","type":"ui_table","z":"c4415dc7a5e4d922","group":"7a977e3045843f01","name":"設定資料表","order":15,"width":9,"height":6,"columns":[{"field":"id","title":"ID"},{"field":"week","title":"星期"},{"field":"start_time","title":"起始時間"},{"field":"end_time","title":"結束時間"}],"outputs":0,"x":630,"y":400,"wires":[]},{"id":"66cedff677931381","type":"ui_text","z":"c4415dc7a5e4d922","group":"7a977e3045843f01","order":9,"width":4,"height":1,"name":"目前狀態","label":"目前狀態 (MQTT)","format":"{{msg.payload}}","layout":"row-left","className":"","style":false,"font":"","fontSize":"","color":"#000000","x":240,"y":160,"wires":[]},{"id":"a8b53b8d55d2562a","type":"ui_text","z":"c4415dc7a5e4d922","group":"7a977e3045843f01","order":11,"width":7,"height":1,"name":"目前時間","label":"目前時間","format":"{{msg.payload}}","layout":"row-left","className":"","style":false,"font":"","fontSize":"","color":"#000000","x":480,"y":580,"wires":[]},{"id":"571b09b0b3db832d","type":"ui_text","z":"c4415dc7a5e4d922","group":"7a977e3045843f01","order":14,"width":4,"height":1,"name":"目前模式","label":"目前模式","format":"<font face='arial'><font size=12><font color='red'>{{msg.payload}}","layout":"","className":"","style":false,"font":"","fontSize":"","color":"#000000","x":980,"y":520,"wires":[]},{"id":"c1df939e90109295","type":"function","z":"c4415dc7a5e4d922","name":"暫存輸入","func":"// 將 UI 輸入暫存到 flow context\nflow.set(msg.topic, msg.payload);\nreturn null;","outputs":1,"x":240,"y":60,"wires":[["1f2cc6b31240a4f1"]]},{"id":"71c2be76dc301288","type":"function","z":"c4415dc7a5e4d922","name":"組合新增 SQL","func":"var week = flow.get('week');\nvar start = flow.get('start_time');\nvar end = flow.get('end_time');\nif(!week || !start || !end) return null;\nmsg.topic = `INSERT INTO timer_control (week, start_time, end_time) VALUES ($week, $start, $end)`;\nmsg.payload = [week, start, end];\nreturn msg;\n","outputs":1,"timeout":"","noerr":0,"initialize":"","finalize":"","libs":[],"x":240,"y":260,"wires":[["19c1a5efcb75fd17"]]},{"id":"19c1a5efcb75fd17","type":"sqlite","z":"c4415dc7a5e4d922","mydb":"60fe3b864ac60d5e","sqlquery":"msg.topic","sql":"","name":"新增資料","x":440,"y":260,"wires":[["6ca7b1ce50422889","42ec6cae24919434"]]},{"id":"f889ef6f7f18ee8b","type":"function","z":"c4415dc7a5e4d922","name":"組合刪除 SQL","func":"var id = flow.get('delete_id') || msg.payload;\nif(!id) return null;\nmsg.topic = `DELETE FROM timer_control WHERE id= $id`;\nmsg.payload = [id];\nreturn msg;\n","outputs":1,"timeout":"","noerr":0,"initialize":"","finalize":"","libs":[],"x":220,"y":320,"wires":[["64ba4651775daf50","42ec6cae24919434"]]},{"id":"64ba4651775daf50","type":"sqlite","z":"c4415dc7a5e4d922","mydb":"60fe3b864ac60d5e","sqlquery":"msg.topic","sql":"","name":"刪除資料","x":440,"y":320,"wires":[["ab631e9a4487717f"]]},{"id":"250eb18fd922f88a","type":"sqlite","z":"c4415dc7a5e4d922","mydb":"60fe3b864ac60d5e","sqlquery":"msg.topic","sql":"","name":"查詢全部","x":460,"y":400,"wires":[["184e1bf5056bfe48","f0feae93389a5fb4"]]},{"id":"3f05c19f87227f82","type":"function","z":"c4415dc7a5e4d922","name":"暫存模式","func":"flow.set('mode', msg.payload);\nreturn null;","outputs":1,"timeout":"","noerr":0,"initialize":"","finalize":"","libs":[],"x":260,"y":460,"wires":[["6d15b794be697a93"]]},{"id":"e91c95c84987059e","type":"inject","z":"c4415dc7a5e4d922","name":"每秒觸發","props":[],"repeat":"1","x":110,"y":520,"wires":[["40cadefb01a65302"]]},{"id":"40cadefb01a65302","type":"function","z":"c4415dc7a5e4d922","name":"查詢本日設定 SQL","func":"var now = new Date();\nvar week = [\"Sunday\",\"Monday\",\"Tuesday\",\"Wednesday\",\"Thursday\",\"Friday\",\"Saturday\"][now.getDay()];\nmsg.week = week;\nmsg.now = now;\nmsg.topic = `SELECT * FROM timer_control WHERE week= $week`;\nmsg.payload = [week];\nreturn msg;\n","outputs":1,"timeout":"","noerr":0,"initialize":"","finalize":"","libs":[],"x":290,"y":520,"wires":[["e9628c883df28251"]]},{"id":"e9628c883df28251","type":"sqlite","z":"c4415dc7a5e4d922","mydb":"60fe3b864ac60d5e","sqlquery":"msg.topic","sql":"","name":"查詢本日設定","x":480,"y":520,"wires":[["9bd27450e2660fef"]]},{"id":"3bc8db6bdf77996d","type":"inject","z":"c4415dc7a5e4d922","name":"每秒時間顯示","props":[],"repeat":"1","x":120,"y":580,"wires":[["8f1e906b226f5e6c"]]},{"id":"8f1e906b226f5e6c","type":"function","z":"c4415dc7a5e4d922","name":"取得目前時間","func":"var now = new Date();\nmsg.payload = now.toLocaleString();\nreturn msg;","outputs":1,"timeout":"","noerr":0,"initialize":"","finalize":"","libs":[],"x":300,"y":580,"wires":[["a8b53b8d55d2562a"]]},{"id":"9972978af947f727","type":"mqtt in","z":"c4415dc7a5e4d922","name":"訂閱狀態","topic":"alex9ufo/timer/status","qos":"1","datatype":"auto-detect","broker":"192c2b20bef1e71a","nl":false,"rap":false,"inputs":0,"x":80,"y":160,"wires":[["66cedff677931381","101ffa61bc76bcb5"]]},{"id":"1f2cc6b31240a4f1","type":"debug","z":"c4415dc7a5e4d922","name":"debug 352","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":410,"y":80,"wires":[]},{"id":"cb1849ab86a4c322","type":"inject","z":"c4415dc7a5e4d922","name":"建立資料庫","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":100,"y":820,"wires":[["792eb99c88269294"]]},{"id":"6d15b794be697a93","type":"debug","z":"c4415dc7a5e4d922","name":"debug 354","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":430,"y":460,"wires":[]},{"id":"6ca7b1ce50422889","type":"debug","z":"c4415dc7a5e4d922","name":"debug ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":570,"y":260,"wires":[]},{"id":"ab631e9a4487717f","type":"debug","z":"c4415dc7a5e4d922","name":"debug ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":570,"y":320,"wires":[]},{"id":"f0feae93389a5fb4","type":"debug","z":"c4415dc7a5e4d922","name":"debug ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":610,"y":360,"wires":[]},{"id":"42ec6cae24919434","type":"function","z":"c4415dc7a5e4d922","name":"組合顯示所有SQL","func":"\nmsg.topic = \"SELECT id, week, start_time, end_time FROM timer_control ORDER BY id DESC\";\nreturn msg;","outputs":1,"timeout":"","noerr":0,"initialize":"","finalize":"","libs":[],"x":270,"y":400,"wires":[["250eb18fd922f88a"]]},{"id":"8c5db1b88878fe13","type":"ui_text","z":"c4415dc7a5e4d922","group":"7a977e3045843f01","order":13,"width":5,"height":1,"name":"剩餘秒數","label":"計時剩餘秒數","format":"<font face='arial'><font size=12><font color='red'>{{msg.payload}}","layout":"","className":"","style":false,"font":"","fontSize":"","color":"#000000","x":980,"y":560,"wires":[]},{"id":"9bd27450e2660fef","type":"function","z":"c4415dc7a5e4d922","name":"判斷 ON/OFF + 模式/剩餘秒數","func":"// 取得目前模式\nvar mode = flow.get('mode') || 'auto';\nvar now = msg.now || new Date();\nvar now_sec = now.getHours()*3600 + now.getMinutes()*60 + now.getSeconds();\nvar on = false;\nvar remain_sec = 0;\n\nif(mode === 'auto') {\n (msg.payload || []).forEach(row => {\n let s = row.start_time.split(':');\n let e = row.end_time.split(':');\n let start_sec = parseInt(s[0])*3600 + parseInt(s[1])*60 + parseInt(s[2]);\n let end_sec = parseInt(e[0])*3600 + parseInt(e[1])*60 + parseInt(e[2]);\n if(now_sec >= start_sec && now_sec <= end_sec) {\n on = true;\n remain_sec = end_sec - now_sec;\n }\n });\n msg.status = on ? 'on' : 'off';\n msg.remain_sec = on ? remain_sec : 0;\n} else if(mode === 'manual_on') {\n msg.status = 'on';\n msg.remain_sec = 0;\n} else if(mode === 'manual_off') {\n msg.status = 'off';\n msg.remain_sec = 0;\n}\nmsg.mode = mode;\n// 狀態改變才送MQTT\nvar last_status = flow.get('last_status') || '';\nif(msg.status !== last_status) {\n flow.set('last_status', msg.status);\n msg.to_mqtt = true;\n} else {\n msg.to_mqtt = false;\n}\nreturn [msg, {payload: mode}, {payload: msg.remain_sec}];","outputs":3,"x":710,"y":520,"wires":[["b9195278c3e76d35"],["571b09b0b3db832d"],["8c5db1b88878fe13"]]},{"id":"b9195278c3e76d35","type":"function","z":"c4415dc7a5e4d922","name":"狀態改變才送MQTT","func":"if (msg.to_mqtt) {\n msg.payload = msg.status;\n return msg;\n}\nreturn null;","outputs":1,"timeout":"","noerr":0,"initialize":"","finalize":"","libs":[],"x":880,"y":460,"wires":[["352f4822673f45c3"]]},{"id":"352f4822673f45c3","type":"mqtt out","z":"c4415dc7a5e4d922","name":"MQTT ON/OFF","topic":"alex9ufo/timer/control","qos":"1","retain":"true","respTopic":"","contentType":"","userProps":"","correl":"","expiry":"","broker":"192c2b20bef1e71a","x":1080,"y":460,"wires":[]},{"id":"101ffa61bc76bcb5","type":"debug","z":"c4415dc7a5e4d922","name":"debug 361","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":250,"y":200,"wires":[]},{"id":"78842043a91baa5b","type":"ui_button","z":"c4415dc7a5e4d922","name":"建立 資料庫","group":"7a977e3045843f01","order":12,"width":2,"height":1,"passthru":false,"label":"建立 資料庫","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"","payloadType":"str","topic":"","topicType":"str","x":90,"y":780,"wires":[["792eb99c88269294"]]},{"id":"b2e850303c6ab27c","type":"ui_toast","z":"c4415dc7a5e4d922","position":"prompt","displayTime":"3","highlight":"","sendall":true,"outputs":1,"ok":"OK","cancel":"Cancel","raw":true,"className":"","topic":"","name":"刪除確認","x":240,"y":700,"wires":[["7e1b7e9e98c8b3f4","e5da439739d6d507"]]},{"id":"7e1b7e9e98c8b3f4","type":"function","z":"c4415dc7a5e4d922","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,"timeout":"","noerr":0,"initialize":"","finalize":"","libs":[],"x":400,"y":720,"wires":[["27b7ded81acc027b","7bcb8178f33ee364"],[]]},{"id":"a1504d8a77dd0f44","type":"ui_toast","z":"c4415dc7a5e4d922","position":"top right","displayTime":"3","outputs":0,"name":"已刪除提示","x":1090,"y":740,"wires":[]},{"id":"792eb99c88269294","type":"function","z":"c4415dc7a5e4d922","name":"建立資料庫 SQL","func":"msg.topic = `CREATE TABLE IF NOT EXISTS timer_control (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n week TEXT,\n start_time TEXT,\n end_time TEXT\n)`;\nreturn msg;","outputs":1,"timeout":"","noerr":0,"initialize":"","finalize":"","libs":[],"x":260,"y":820,"wires":[["3b07dc31bdda5675"]]},{"id":"3b07dc31bdda5675","type":"sqlite","z":"c4415dc7a5e4d922","mydb":"60fe3b864ac60d5e","sqlquery":"msg.topic","sql":"","name":"建立DB","x":420,"y":820,"wires":[["a1256a27f69eec9b","89279da15a38f1bb"]]},{"id":"a1256a27f69eec9b","type":"ui_toast","z":"c4415dc7a5e4d922","position":"bottom right","displayTime":"5","highlight":"","sendall":true,"outputs":0,"ok":"","cancel":"","raw":false,"className":"","topic":"","name":"已建立提示","x":590,"y":800,"wires":[]},{"id":"27b7ded81acc027b","type":"function","z":"c4415dc7a5e4d922","name":"刪除資料庫","func":"//DROP TABLE timer_control\nmsg.topic = \"DROP TABLE timer_control\";\nreturn msg;\n","outputs":1,"timeout":"","noerr":0,"initialize":"","finalize":"","libs":[],"x":570,"y":760,"wires":[["4ca4ec289f26ed33"]]},{"id":"8758159f09d52183","type":"ui_button","z":"c4415dc7a5e4d922","name":"刪除資料庫","group":"7a977e3045843f01","order":10,"width":2,"height":1,"passthru":false,"label":"刪除 資料庫","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"刪除資料庫","payloadType":"str","topic":"","topicType":"str","x":90,"y":720,"wires":[["b2e850303c6ab27c"]]},{"id":"89279da15a38f1bb","type":"debug","z":"c4415dc7a5e4d922","name":"debug 3","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":580,"y":840,"wires":[]},{"id":"6e83962705b1f13d","type":"sqlite","z":"c4415dc7a5e4d922","mydb":"60fe3b864ac60d5e","sqlquery":"msg.topic","sql":"","name":"刪除DB","x":920,"y":760,"wires":[["a1504d8a77dd0f44"]]},{"id":"035328ac659d30b1","type":"inject","z":"c4415dc7a5e4d922","name":"刪除資料庫","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":100,"y":680,"wires":[["b2e850303c6ab27c"]]},{"id":"e5da439739d6d507","type":"debug","z":"c4415dc7a5e4d922","name":"debug 4","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":380,"y":680,"wires":[]},{"id":"7bcb8178f33ee364","type":"function","z":"c4415dc7a5e4d922","name":"刪除所有資料","func":"\nmsg.topic =\"DELETE FROM timer_control\";\nreturn msg;\n\n\n//msg.topic = `CREATE TABLE IF NOT EXISTS timer_control (\n// id INTEGER PRIMARY KEY AUTOINCREMENT,\n// week TEXT,\n// start_time TEXT,\n// end_time TEXT\n//)`;\n//return msg;","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":580,"y":700,"wires":[["8da66eab9a3070ae"]]},{"id":"8da66eab9a3070ae","type":"sqlite","z":"c4415dc7a5e4d922","mydb":"60fe3b864ac60d5e","sqlquery":"msg.topic","sql":"","name":"刪除所有資料","x":760,"y":700,"wires":[["42ec6cae24919434"]]},{"id":"4ca4ec289f26ed33","type":"delay","z":"c4415dc7a5e4d922","name":"","pauseType":"delay","timeout":"3","timeoutUnits":"seconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"allowrate":false,"outputs":1,"x":740,"y":760,"wires":[["6e83962705b1f13d"]]},{"id":"7a977e3045843f01","type":"ui_group","name":"Timer 控制","tab":"c3792c2daa03c3d4","order":1,"disp":true,"width":"9","collapse":false,"className":""},{"id":"60fe3b864ac60d5e","type":"sqlitedb","db":"weekly_timer.db","mode":"RWC"},{"id":"192c2b20bef1e71a","type":"mqtt-broker","name":"mqttgo","broker":"broker.mqttgo.io","port":"1883","clientid":"","autoConnect":true,"usetls":false,"protocolVersion":"4","keepalive":"60","cleansession":true,"autoUnsubscribe":true,"birthTopic":"","birthQos":"0","birthRetain":"false","birthPayload":"","birthMsg":{},"closeTopic":"","closeQos":"0","closeRetain":"false","closePayload":"","closeMsg":{},"willTopic":"","willQos":"0","willRetain":"false","willPayload":"","willMsg":{},"userProps":"","sessionExpiry":""},{"id":"c3792c2daa03c3d4","type":"ui_tab","name":"儀表板測試","icon":"dashboard","order":1},{"id":"c1829d473e8bd4fd","type":"global-config","env":[],"modules":{"node-red-dashboard":"3.6.6","node-red-node-ui-table":"0.4.3","node-red-node-sqlite":"1.1.0"}}]
WOKWI程式
沒有留言:
張貼留言