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

2024年11月28日星期四

2024_09 作業3 以Node-Red 為主

2024_09 作業3 (以Node-Red 為主 Arduino 可能需要配合修改 )

Arduino 可能需要修改的部分

1)mqtt broker

2) 主題Topic (發行接收)

3) WIFI ssid , password

const char broker[] = "test.mosquitto.org";
//const char broker[] = "broker.mqtt-dashboard.com";
int        port     = 1883;
const char *SubTopic1 = "alex9ufo/esp32/led";
const char *PubTopic2 = "alex9ufo/esp32/led_status";
const char *PubTopic3 = "alex9ufo/esp32/RFID";
const char willTopic[] = "alex9ufo/esp32/Starting";

=============================================

Node-Red LED 控制及資料庫1

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

//CREATE TABLE LEDSTATUS (

//id INTEGER,

//STATUS TEXT,

//Date DATE,

//Time TIME,

//PRIMARY KEY (id)

//);

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

msg.topic = "CREATE TABLE LEDSTATUS (id INTEGER,STATUS TEXT,Date DATE,Time TIME,PRIMARY KEY (id))";

return msg;

=============================================

MQTT Out節點發行到 Broker

Broker test.mosquitto.org :1883

Topic主題 : alex9ufo/esp32/led

=============================================

Insert funtion 新增

var Today = new Date();

var yyyy = Today.getFullYear(); //年

var MM = Today.getMonth()+1; //月

var dd = Today.getDate(); //日

var h = Today.getHours(); //時

var m = Today.getMinutes(); //分

var s = Today.getSeconds(); //秒

if(MM<10)

{

MM = '0'+MM;

}

if(dd<10)

{

dd = '0'+dd;

}

if(h<10)

{

h = '0'+h;

}

if(m<10)

{

m = '0' + m;

}

if(s<10)

{

s = '0' + s;

}

var var_date = yyyy+'/'+MM+'/'+dd;

var var_time = h+':'+m+':'+s;

var myLED = msg.payload;

msg.topic = "INSERT INTO LEDSTATUS ( STATUS , Date , Time ) VALUES ($myLED, $var_date , $var_time ) " ;

msg.payload = [myLED, var_date , var_time ]

return msg;

//INSERT INTO LEDSTATUS (

//id INTEGER,

//STATUS TEXT,

//Date DATE,

//Time TIME,

//PRIMARY KEY (id)

//);

=============================================

檢視資料 (最新50筆資料)

msg.topic = "SELECT * FROM LEDSTATUS ORDER BY id DESC LIMIT 50";

return msg;

=============================================

確認或取消

var topic=msg.payload;

if (topic==""){

return [msg,null];

}

if (topic=="Cancel"){

return [null,msg];

}

return msg;

=============================================

(L)刪除所有資料

msg.topic = "DELETE from LEDSTATUS";

return msg;

=============================================

刪除資料庫

msg.topic = "DROP TABLE LEDSTATUS";

return msg;

=============================================

查詢一筆資料SELECT * FROM WHERE id LIKE

var id = msg.payload.id;

var s=global.get("SEL1")

msg.topic="";

var temp="";

if (s==1)

{

temp ="SELECT * FROM LEDSTATUS";

temp=temp+" WHERE id LIKE '"+ id +"'";

}

msg.topic=temp;

global.set("SEL1",0);

return msg;

=============================================

刪除一筆資料DELETE FROM WHERE id LIKE

var id = msg.payload.id;

var s=global.get("SEL2")

msg.topic="";

var temp="";

if (s==2)

{

temp ="DELETE FROM LEDSTATUS";

temp=temp+" WHERE id LIKE '"+ id +"'";

}

msg.topic=temp;

global.set("SEL2",0)

return msg;

=============================================

更正一筆資料 update LEDSTATUS set

var id = global.get("ID");

var status = msg.payload.Status;

var date = msg.payload.date;

var time = msg.payload.time;

var s=global.get("SEL3")

msg.topic="";

var temp="";

if (s==3)

{

temp ="update LEDSTATUS set ";

temp=temp+" STATUS= '" + status +"'";

temp=temp+" , Date= '" + date +"'";

temp=temp+" , Time= '" + time +"'";

temp=temp+" WHERE id=" + id;

//msg.topic = "update LEDSTATUS set ( id , STATUS , Date , Time ) VALUES ($id, $status , $date , $time ) " ;

//msg.payload = [id,status,date,time]

}

msg.topic=temp;

return msg;

=============================================

MQTT In節點發行到 Broker

Broker test.mosquitto.org :1883

Topic主題 : alex9ufo/esp32/led_status

=============================================

Set Line API Function

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

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

return msg;

發行權杖請參考發行權杖的取得

Bearer

與

A4wwPNh2WqB7dlfeQyyIAwtggn1kfZ1SI5LkkCdia1gB

中央有一空白: Bearer A4wwPNh

Node-Red RFID UID 資料庫2

=============================================

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

//CREATE TABLE "RFIDtable" (

// "id" INT NOT NULL,

// "uidname" TEXT,

// "currentdate" DATE,

// "currenttime" TIME

// PRIMARY KEY("id")

//);

msg.topic = "CREATE TABLE RFIDtable(id INTEGER PRIMARY KEY AUTOINCREMENT, uidname TEXT, currentdate DATE, currenttime TIME)";

return msg;

=============================================

id SET function

var del_idtemp=1;

flow.set("idtemp", del_idtemp);

return msg;

=============================================

MQTT In節點發行到 Broker

Broker test.mosquitto.org :1883

Topic主題 : alex9ufo/esp32/RFID

==================================

Query or Insert Function

var query = flow.get("query_temp");

flow.set("uid_temp", msg.payload);

if (query==='NotQuery')

msg.payload='Create'

else

msg.payload='Query'

return msg;

==================================

Insert funtion 新增一筆資料

var Today = new Date();

var yyyy = Today.getFullYear(); //年

var MM = Today.getMonth()+1; //月

var dd = Today.getDate(); //日

var h = Today.getHours(); //時

var m = Today.getMinutes(); //分

var s = Today.getSeconds(); //秒

if(MM<10)

{

MM = '0'+MM;

}

if(dd<10)

{

dd = '0'+dd;

}

if(h<10)

{

h = '0'+h;

}

if(m<10)

{

m = '0' + m;

}

if(s<10)

{

s = '0' + s;

}

var var_date = yyyy+'/'+MM+'/'+dd;

var var_time = h+':'+m+':'+s;

var myRFID = flow.get('uid_temp');

msg.topic = "INSERT INTO RFIDtable ( uidname , currentdate, currenttime ) VALUES ($myRFID, $var_date , $var_time ) " ;

msg.payload = [myRFID, var_date , var_time ]

return msg;

//CREATE TABLE "RFIDtable" (

// "id" INT NOT NULL,

// "uidname" TEXT,

// "currentdate" DATE,

// "currenttime" TIME

// PRIMARY KEY("id")

//);

==================================

增加日期時間

var ms1=msg.payload[0];

var ms2=msg.payload[1];

var ms3=msg.payload[2];

msg.payload="新增一筆:"+ms1+", 日期: "+ms2+", 時間:"+ms3;

return msg;

==================================

Set Line API

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

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

return msg;

==================================

檢視資料

msg.topic = "SELECT * FROM RFIDtable ORDER BY id DESC LIMIT 50";

return msg;

==================================

確認刪除

var del_id = flow.get("idtemp");

msg.topic = "DELETE FROM RFIDtable WHERE id= ($del_id) " ;

msg.payload = [del_id]

return msg;

==================================

刪除所有資料

//DELETE from RFIDtable

msg.topic = "DELETE from RFIDtable";

return msg;

==================================

刪除資料庫

//DROP TABLE RFIDtable

msg.topic = "DROP TABLE RFIDtable";

return msg;

==================================

比對 function

var query_uid = flow.get("uidtemp");

msg.topic = "SELECT id,uidname , currentdate,currenttime FROM RFIDtable WHERE uidname LIKE ($query_uid) ";

msg.payload = [query_uid]

return msg;

==================================

Function

var tmp=msg.payload;

msg.topic = "select count( * ) as 總共有幾筆資料 from RFIDtable where uidname=($tmp)";

msg.payload=[tmp];

return msg;

==================================

Function

var query=msg.payload;

if (query== '1' )

msg.payload='Query';

else

msg.payload='NotQuery';

flow.set("query_temp", msg.payload);

return msg;

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":330,"y":80,"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":"0487352be5053bfa","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":"0487352be5053bfa","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":7,"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":"b0c194b692c58c9c","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":"0487352be5053bfa","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":5,"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":"9bc6b410904f7720","order":4,"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_3_LED.db","info":"","x":430,"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\n//Line Notify 權杖\n\n// A4wwPNh2WqB7dlfeQyyIAwtggn1kfZSI5LkkCdia1gB","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"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":"Line Notify 權杖\n\nA4wwPNh2WqB7dlfeQyyIAwtggn1kfZSI5LkkCdia1gB","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":220,"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":1140,"y":40,"wires":[]},{"id":"718398f192a85fd5","type":"comment","z":"41b4f910767c89fe","name":"資料庫位置 C:\\Users\\User\\.node-red\\EX2_3RFID.db","info":"","x":1250,"y":80,"wires":[]},{"id":"3ace6e0491e7e5fc","type":"ui_button","z":"41b4f910767c89fe","name":"","group":"e7c2632dca7fe97d","order":12,"width":6,"height":1,"passthru":false,"label":"建立資料庫","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"建立資料庫","payloadType":"str","topic":"topic","topicType":"msg","x":1130,"y":160,"wires":[["8db1a3e5dec7225f","b58b92c38ee4b88a"]]},{"id":"c1d3a810d10370ce","type":"ui_button","z":"41b4f910767c89fe","name":"","group":"e7c2632dca7fe97d","order":13,"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":10,"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":8,"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 ","func":"var del_idtemp=msg.payload;\nflow.set(\"idtemp\", del_idtemp);\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1400,"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":1,"width":6,"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":9,"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":"70940176.2b2d3","nl":false,"rap":true,"rh":0,"inputs":0,"x":1120,"y":240,"wires":[["be255c2203fbcb03","2f4f4b4b3317a24c","acf3dfeb9069ff15"]]},{"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":11,"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":"取得UID號碼2","func":"var query_uidtemp=msg.payload;\nflow.set(\"uidtemp\", query_uidtemp);\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1660,"y":440,"wires":[["a43c4525b879822d","33dc3daf2b9840fe","7c924092d1530d33"]]},{"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='Create';\n\nflow.set(\"query_temp\", msg.payload);\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1620,"y":1460,"wires":[["2da05eb9bb42a954","f2d62406fe7e9a20"]]},{"id":"2033c3c13e3907c9","type":"ui_switch","z":"41b4f910767c89fe","name":"","label":"新增模式 /自動比對模式 ","tooltip":"","group":"0487352be5053bfa","order":8,"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":1270,"y":280,"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":1140,"y":380,"wires":[[]]},{"id":"9ecd3819a30aa873","type":"ui_audio","z":"41b4f910767c89fe","name":"","group":"2aec78ed5dc0600c","voice":"Microsoft Zhiwei - Chinese (Traditional, Taiwan)","always":true,"x":2000,"y":1500,"wires":[]},{"id":"a43c4525b879822d","type":"debug","z":"41b4f910767c89fe","name":"debug 233","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":1910,"y":460,"wires":[]},{"id":"33dc3daf2b9840fe","type":"ui_text","z":"41b4f910767c89fe","group":"0487352be5053bfa","order":12,"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":11,"width":5,"height":1,"name":"","label":"查詢結果","format":"<font face='arial'><font size=3><font color={{fcolor}}>{{msg.payload}}","layout":"row-left","className":"","x":2140,"y":1240,"wires":[]},{"id":"22af289c2874566e","type":"ui_text","z":"41b4f910767c89fe","group":"0487352be5053bfa","order":10,"width":5,"height":1,"name":"","label":"查詢結果:筆數","format":"<font face='arial'><font size=6><font color={{fcolor}}>{{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":"70940176.2b2d3","nl":false,"rap":true,"rh":0,"inputs":0,"x":1220,"y":1300,"wires":[["e0ecdc79566c5bb5","1fad857520e8fe5f"]]},{"id":"e0ecdc79566c5bb5","type":"ui_audio","z":"41b4f910767c89fe","name":"","group":"2aec78ed5dc0600c","voice":"Google US English","always":false,"x":1420,"y":1340,"wires":[]},{"id":"8bca32233160bfea","type":"change","z":"41b4f910767c89fe","name":"","rules":[{"t":"set","p":"fcolor","pt":"msg","to":"red","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":1980,"y":1180,"wires":[["22af289c2874566e"]]},{"id":"60e58b9c401654e7","type":"change","z":"41b4f910767c89fe","name":"","rules":[{"t":"set","p":"fcolor","pt":"msg","to":"red","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":1980,"y":1240,"wires":[["6e044a502b8e43d5"]]},{"id":"56ef5df3a51dffd6","type":"function","z":"41b4f910767c89fe","name":"function","func":"\nvar a= flow.get(\"uidtemp\");\nmsg.payload=a;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1380,"y":1180,"wires":[["7a2bec3260e95960"]]},{"id":"bfe901410b219927","type":"ui_audio","z":"41b4f910767c89fe","name":"","group":"2aec78ed5dc0600c","voice":"Microsoft Hanhan - Chinese (Traditional, Taiwan)","always":true,"x":2140,"y":1280,"wires":[]},{"id":"58f1559efc0fdfc5","type":"delay","z":"41b4f910767c89fe","name":"","pauseType":"delay","timeout":"2","timeoutUnits":"seconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"allowrate":false,"outputs":1,"x":1980,"y":1280,"wires":[["bfe901410b219927"]]},{"id":"65532790a9d4db9e","type":"function","z":"41b4f910767c89fe","name":"function ","func":"var n=msg.payload;\n\nif (n>0)\n msg.payload='RFID符合';\nelse\n msg.payload='RFID錯誤';\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1800,"y":1240,"wires":[["58f1559efc0fdfc5","60e58b9c401654e7","a485ecf426757725"]]},{"id":"38740cf4c86d3e8f","type":"function","z":"41b4f910767c89fe","name":"取得UID號碼","func":"var myRFID = flow.get('uid_temp');\nmsg.payload=myRFID;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1650,"y":360,"wires":[["33dc3daf2b9840fe"]]},{"id":"99a8394d2ff46a49","type":"ui_audio","z":"41b4f910767c89fe","name":"","group":"2aec78ed5dc0600c","voice":"Microsoft Hanhan - Chinese (Traditional, Taiwan)","always":true,"x":1340,"y":1000,"wires":[]},{"id":"25c3a227e72512ca","type":"ui_audio","z":"41b4f910767c89fe","name":"","group":"2aec78ed5dc0600c","voice":"Microsoft Hanhan - Chinese (Traditional, Taiwan)","always":true,"x":1400,"y":840,"wires":[]},{"id":"444868658020454e","type":"function","z":"41b4f910767c89fe","name":"增加日期時間","func":"var ms1=msg.payload[0];\nvar ms2=msg.payload[1];\nvar ms3=msg.payload[2];\n\nmsg.payload=\"新增一筆:\"+ms1+\", 日期: \"+ms2+\", 時間:\"+ms3;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1720,"y":280,"wires":[["d0339b6976ac8c44"]]},{"id":"d0339b6976ac8c44","type":"function","z":"41b4f910767c89fe","name":"Set Line API ","func":"msg.headers = {'content-type':'application/x-www-form-urlencoded','Authorization':'Bearer A4wwPNh2WqB7dlfeQyyIAwtggn1kfZSI5LkkCdia1gB'};\nmsg.payload = {\"message\":msg.payload};\nreturn msg;\n\n//oR7KdXvK1eobRr2sRRgsl4PMq23DjDlhfUs96SyUBZu","outputs":1,"noerr":0,"x":1890,"y":280,"wires":[["44d606080409fa25"]]},{"id":"44d606080409fa25","type":"http request","z":"41b4f910767c89fe","name":"","method":"POST","ret":"txt","paytoqs":false,"url":"https://notify-api.line.me/api/notify","tls":"","persist":false,"proxy":"","authType":"","x":2040,"y":280,"wires":[["54ac4d90e3eb2827"]]},{"id":"54ac4d90e3eb2827","type":"debug","z":"41b4f910767c89fe","name":"debug 234","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":2190,"y":280,"wires":[]},{"id":"5733c83f86933541","type":"comment","z":"41b4f910767c89fe","name":"Line Notify Message ","info":"","x":1910,"y":320,"wires":[]},{"id":"98ff3f6b2d09e70d","type":"function","z":"41b4f910767c89fe","name":"Set Line API ","func":"msg.headers = {'content-type':'application/x-www-form-urlencoded','Authorization':'Bearer A4wwPNh2WqB7dlfeQyyIAwtggn1kfZSI5LkkCdia1gB'};\nmsg.payload = {\"message\":msg.payload};\nreturn msg;\n\n//oR7KdXvK1eobRr2sRRgsl4PMq23DjDlhfUs96SyUBZu","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1970,"y":1380,"wires":[["94c9e9772fb6ccbf"]]},{"id":"87b950831ca240c0","type":"comment","z":"41b4f910767c89fe","name":"Line Notify Message ","info":"","x":2030,"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":9,"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":1620,"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":1750,"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":1300,"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":1620,"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":"0357a00ce0e2564b","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":"1a82064f252094bc","type":"comment","z":"41b4f910767c89fe","name":"alex9ufo/esp32/led","info":"alex9ufo/esp32/led","x":450,"y":180,"wires":[]},{"id":"67cfea3383275cb0","type":"comment","z":"41b4f910767c89fe","name":"alex9ufo/esp32/led_status","info":"alex9ufo/esp32/led_status","x":130,"y":980,"wires":[]},{"id":"e01fc7dab7ee2e86","type":"comment","z":"41b4f910767c89fe","name":"alex9ufo/esp32/RFID","info":"alex9ufo/esp32/RFID","x":1160,"y":340,"wires":[]},{"id":"2f4f4b4b3317a24c","type":"debug","z":"41b4f910767c89fe","name":"debug 330","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":1290,"y":240,"wires":[]},{"id":"01ea8d0d581d6a7c","type":"link in","z":"41b4f910767c89fe","name":"link in 62","links":["7c924092d1530d33"],"x":1375,"y":1100,"wires":[["56ef5df3a51dffd6"]]},{"id":"7c924092d1530d33","type":"link out","z":"41b4f910767c89fe","name":"link out 64","mode":"link","links":["01ea8d0d581d6a7c"],"x":1845,"y":520,"wires":[]},{"id":"f2d62406fe7e9a20","type":"function","z":"41b4f910767c89fe","name":"function 106","func":"var query = msg.payload;\n\n\nif (query==='Create')\n msg.payload='新增模式'\nelse\n msg.payload='查詢模式'\n \nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1810,"y":1500,"wires":[["9ecd3819a30aa873"]]},{"id":"ad5eab406bf6ac05","type":"ui_template","z":"41b4f910767c89fe","group":"9bc6b410904f7720","name":"user input validate","order":3,"width":6,"height":2,"format":"<md-input-container style=\"overflow:hidden;\">\n <span> \n <input ng-model=\"user.input\" style=\"width:150px\" placeholder={{msg.topic}}><button class=\"bluebutton\" ng-click=\"msg.payload = user.input; send(msg)\" ng-keypress=\"($event.charCode==13)?msg.payload = user.input; send(msg)\" ngstyle=\"{background-color: #008CBA;}\" style=\"\n color: #fff;\n background-color: #5bc0de;\n border-color: #46b8da;\">Ok</button></span>\n</md-input-container>\n ","storeOutMessages":true,"fwdInMessages":false,"resendOnRefresh":false,"templateScope":"local","className":"","x":1430,"y":500,"wires":[["096c65eb842945be"]]},{"id":"af13c2768cf65c2a","type":"ui_button","z":"41b4f910767c89fe","name":"","group":"9bc6b410904f7720","order":2,"width":0,"height":0,"passthru":false,"label":"手動比對資料輸入","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"","payloadType":"str","topic":"topic","topicType":"msg","x":1230,"y":500,"wires":[["ad5eab406bf6ac05"]]},{"id":"e7c2632dca7fe97d","type":"ui_group","name":"資料庫控制區","tab":"a70850925ff05e71","order":4,"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":6,"collapse":false,"className":""},{"id":"70940176.2b2d3","type":"mqtt-broker","name":"","broker":"test.mosquitto.org","port":"1883","clientid":"","autoConnect":true,"usetls":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":"a70850925ff05e71","type":"ui_tab","name":"作業2-3","icon":"dashboard","order":91,"disabled":false,"hidden":false}]

2024年11月24日星期日

WOKWI LED + MQTT Node-Red SQLite

const char *mqtt_broker = "broker.mqtt-dashboard.com";

const char *topic1 = "alex9ufo/esp32/led";

const char *topic = "alex9ufo/esp32/Starting";

const char *topic3 = "alex9ufo/esp32/led_status";

//wifi &  MQTT
#include <WiFi.h>
#include <PubSubClient.h>

//GPIO 13 D1 Build in LED
#define LED 13


///////please enter your sensitive data in the Secret tab/arduino_secrets.h
const char *ssid =  "Wokwi-GUEST"; // your network SSID (name)
const char *password =  "" ;           // your network password (use for WPA, or use as key for WEP)
// WiFi
//const char *ssid = "alex9ufo"; // Enter your Wi-Fi name
//const char *password = "alex9981";  // Enter Wi-Fi password

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

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

bool ledState = false;
bool atwork = false;


WiFiClient espClient;
PubSubClient client(espClient);


long lastMsg = 0;
long lastMsg1= 0;

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

char jsonChar1[50];
TaskHandle_t Task1;

// Wifi reconnect
unsigned long previousMillis = 0;
unsigned long interval = 30000;
//===========================================================
//任務1副程式Task1_senddata
void Task1_senddata(void * pvParameters ) {
  //無窮迴圈
  for (;;) {
    //偵測上傳旗標是否為true
      // Process LED message
    LED_Message();   
    //Task1休息，delay(X)不可省略
    delay(1000);
  }
}
//===========================================================

void setup_wifi() {
  delay(10);
  // We start by connecting to a WiFi network
  Serial.println();
  Serial.print("Connecting to ");
  Serial.println(ssid);

  WiFi.begin(ssid, password);

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

  Serial.println("");
  Serial.println("WiFi connected");
  Serial.println("IP address: ");
  Serial.println(WiFi.localIP());
}    

//===========================================================
void callback(char *topic, byte *payload, unsigned int length) {
    Serial.print("Message arrived in topic: ");
    Serial.println(topic);
    Serial.print("Message: ");
    String message;
    for (int i = 0; i < length; i++) {
        message += (char) payload[i];  // Convert *byte to string
    }
    Serial.print(message);
    if (String(topic)=="alex9ufo/esp32/led") {

    if (message == "on" ) {
        digitalWrite(LED, LOW);  // Turn on the LED
        ledState = true;  //ledState = ture HIGH
        Flash = false;
        Timer = false;
        json ="ON";
        Send = true ;
    }
    if (message == "off") {
        digitalWrite(LED, HIGH); // Turn off the LED
        ledState = false; //ledState = false LOW
        Flash = false;
        Timer = false;
        json ="OFF";
        Send = true ;

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

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

    if (message == "toggle" ) {
        digitalWrite(LED, !digitalRead(LED));   // Turn the LED toggle 
        if (digitalRead(LED)) 
            ledState = true;
        else
            ledState = false; 
        
        Flash = false;
        Timer = false;
        json ="TOGGLE";
        Send = true ;        
    }
    }
    Serial.println();
    Serial.println("-----------------------");
}
//===========================================================
void reconnect() {
  // Loop until we're reconnected
  while (!client.connected()) {
    Serial.print("Attempting MQTT connection...");
    // Attempt to connect

    if (client.connect("esp32-client-")) {
      Serial.println("connected");
      // Subscribe
      client.subscribe("alex9ufo/esp32/led");
      
    } else {
      Serial.print("failed, rc=");
      Serial.print(client.state());
      Serial.println(" try again in 5 seconds");
      // Wait 5 seconds before retrying
      delay(5000);
      if (WiFi.status() != WL_CONNECTED)  {
        Serial.println("Reconnecting to WiFi...");
        WiFi.disconnect();
        WiFi.reconnect();
      }
    }
  }
}
//===========================================================

void LED_Message() {
    if (Flash){
        digitalWrite(LED, !digitalRead(LED));
        delay(500);
        if (digitalRead(LED)) 
            ledState = true;
        else
            ledState = false; 
    } //(Flash)
        
    if (Timer) {
        digitalWrite(LED, HIGH);
        delay(500);
        if (digitalRead(LED)) 
            ledState = true;
        else
            ledState = false; 

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

}

//===========================================================
void setup() {
    // Set software serial baud to 115200;
    Serial.begin(115200);
    delay(1000); // Delay for stability
    //======================================================
    // Connecting to a WiFi network
    setup_wifi();
    // Setting LED pin as output
    pinMode(LED, OUTPUT);
    digitalWrite(LED, LOW);  // Turn off the LED initially
    //======================================================
    // Connecting to an MQTT broker
    client.setServer(mqtt_broker, mqtt_port);
    client.setCallback(callback);
    while (!client.connected()) {
        String client_id = "esp32-client-";
        client_id += String(WiFi.macAddress());
        Serial.printf("The client %s connects to the public MQTT broker\n", client_id.c_str());
        if (client.connect(client_id.c_str(), mqtt_username, mqtt_password)) {
            Serial.println("Public HiveMQ MQTT broker (broker.mqtt-dashboard.com) connected");
        } else {
            Serial.print("Failed with state ");
            Serial.print(client.state());
            delay(2000);
        }
    }

    // Publish and subscribe
    client.subscribe(topic1);
    client.publish(topic,"ESP32 at work");

    //在核心0啟動任務1
    xTaskCreatePinnedToCore(
    Task1_senddata, /*任務實際對應的Function*/
      "Task1",        /*任務名稱*/
      10000,          /*堆疊空間*/
      NULL,           /*無輸入值*/
      0,              /*優先序0*/
      &Task1,         /*對應的任務變數位址*/
      0);             /*指定在核心0執行 */
}
//===========================================================
void loop() 
{
 if (!client.connected()) {
      reconnect();
      Serial.print(" client not connected  reconnect ");
      delay(200);
    }
  client.loop();


  unsigned long currentMillis = millis();
  // if WiFi is down, try reconnecting
  if ((WiFi.status() != WL_CONNECTED) && (currentMillis - previousMillis >=interval)) {
    Serial.print(millis());
    Serial.println("Reconnecting to WiFi...");
    WiFi.disconnect();
    WiFi.reconnect();
    previousMillis = currentMillis;

    client.setCallback(callback);
  }

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

訂閱：文章 (Atom)

2024年11月28日 星期四

2024_09 作業3 以Node-Red 為主

2024年11月24日 星期日

WOKWI LED + MQTT Node-Red SQLite

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

2024年11月28日星期四

2024年11月24日星期日