2024年4月27日 星期六

2024產專班 作業2

 2024產專班 作業2  

1. 系統圖

  




   ESP32+MFRC522 組成RFID Reader 可以將RFID卡片的UID 透過 MQTT協定

   上傳(發行 主題 (:topic) alex9ufo/2024/RFID/RFID_UID  ,, Payload為卡號 )至 

    Hive MQTT Server上 給Node-Red 訂閱 , 

    Node-Red 訂閱 主題:      alex9ufo/2024/RFID/RFID_UID 透過SQlite 節點

    將卡號存入資料庫   C:\Users\User\.node-red\2024EX2_1.db  

   並且 將卡號 發行到Line Notify 提醒用戶有人感應 RFID Reader

   

   LED的控制方式有4種 on 亮 off滅 Timer定時5秒 flash 閃爍

   利用 node-red botton 節點 送至Hive MQTT Server上 給 ESP32 訂閱 ,

   發行的主題 Topic為 alex9ufo/2024/RFID/LED_control , Payload為 on 或 off或 timer

  或 flash  當esp32 訂閱 (Subscribe ) 主題alex9ufo/2024/RFID/LED_control 然後'依據

  payload 收到 on , off , timer ,flash 來控制LED 

  並且發行主題 Topic為 alex9ufo/2024/RFID/LED_status 回應 Node-Red 確實

  已經收到 控制LED的 命令 

  Node-Red訂閱 alex9ufo/2024/RFID/LED_status 將LED的狀態 存入資料庫    C:\Users\User\.node-red\2024EX2.db  保存



 2. Arduino 程式

1) esp32 preferences 設定 

https://github.com/espressif/arduino-esp32/releases/download/2.0.9/package_esp32_index.json

https://hackmd.io/@Robert/Hk1rGMsU3

2) Tools --> Board  (依據購買的esp32 型號設定 )

3) comPort 的設定 (上傳 Hex code 到 ESP32板子)

4) Compile (組譯) , upload (上傳) 







5) ESP32與 MFRC522 LED的連接
 



6) Arduino 程式 (記得 使用到的 libararies 程式庫 要安裝 wifi 的ssid , pass 帳號 密碼 程式中 alex9ufo 改成自己的英文名 )

const char *SubTopic1 = "alex9ufo/2024/RFID/LED_control";
const char *PubTopic2 = "alex9ufo/2024/RFID/LED_status";
const char *PubTopic3 = "alex9ufo/2024/RFID/RFID_UID";
//const char *PubTopic4 = "alex9ufo/2024/RFID/RFID_PICC";

const char willTopic[] = "alex9ufo/2024/RFID/Starting";
//======================================================

  if (Topic=="alex9ufo/2024/RFID/LED_control") {
  if (message == "on") {



程式 


//定義MFRC522 RFID read 與 ESP32 介面 接腳連接Pin assign
/* Wiring RFID RC522 module  
==============================================================
GND     = GND   3.3V    = 3.3V
The following table shows the typical pin layout used:
 *             MFRC522      ESP32    
 *             Reader/PCD            
 * Signal      Pin          Pin        
 * -----------------------------------
 * RST/Reset   RST          GPIO27  
 * SPI SS      SDA(SS)      GPIO5    
 * SPI MOSI    MOSI         GPIO23    
 * SPI MISO    MISO         GPIO19    
 * SPI SCK     SCK          GPIO18    
 *
[1] (1, 2) Configurable, typically defined as RST_PIN in sketch/program.
[2] (1, 2) Configurable, typically defined as SS_PIN in sketch/program.
[3] The SDA pin might be labeled SS on some/older MFRC522 boards
=============================================================
*/
// Wifi 與 MQttClient 程式庫
#include <ArduinoMqttClient.h>
#include <WiFi.h>
//#include "arduino_secrets1.h"

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

//GPIO 2 D1 Build in LED

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

const char ssid[] = "alex9ufo"; // Enter your Wi-Fi name
const char pass[] = "alex9981";  // Enter Wi-Fi password

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

WiFiClient wifiClient;
MqttClient mqttClient(wifiClient);

//const char broker[] = "test.mosquitto.org";
const char broker[] = "broker.mqtt-dashboard.com";
int        port     = 1883;
String json = "";

const char *SubTopic1 = "alex9ufo/2024/RFID/LED_control";
const char *PubTopic2 = "alex9ufo/2024/RFID/LED_status";
const char *PubTopic3 = "alex9ufo/2024/RFID/RFID_UID";
//const char *PubTopic4 = "alex9ufo/2024/RFID/RFID_PICC";

const char willTopic[] = "alex9ufo/2024/RFID/Starting";
//======================================================
#define RST_PIN      27        // 讀卡機的重置腳位
#define SS_PIN       5        // 晶片選擇腳位
MFRC522 mfrc522(SS_PIN, RST_PIN);    // 建立MFRC522物件
MFRC522::MIFARE_Key key;  // 儲存金鑰
MFRC522::StatusCode status;
//===========================================================
//布林代數 LED狀態 是否連上網路ESP32 ready ?
bool ledState = false;
bool atwork = false;
bool Send = false;  //true
String LEDjson = "";
int Count= 0;
bool Flash = false;  //true
bool Timer = false;  //true

//===========================================================
void onMqttMessage(int messageSize) {
  // we received a message, print out the topic and contents
  Serial.print("Received a message with topic '");
  Serial.print(mqttClient.messageTopic());
  String Topic= mqttClient.messageTopic();
  Serial.print("', duplicate = ");
  Serial.print(mqttClient.messageDup() ? "true" : "false");
  Serial.print(", QoS = ");
  Serial.print(mqttClient.messageQoS());
  Serial.print(", retained = ");
  Serial.print(mqttClient.messageRetain() ? "true" : "false");
  Serial.print("', length ");
  Serial.print(messageSize);
  Serial.println(" bytes:");
  String message="";
  // use the Stream interface to print the contents
  while (mqttClient.available()) {
    //Serial.print((char)mqttClient.read());
    message += (char)mqttClient.read();
  }

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

  if (Topic=="alex9ufo/2024/RFID/LED_control") {
  if (message == "on") {
    digitalWrite(LED, LOW);  // Turn on the LED
    //ledState = true;  //ledState = ture HIGH
    //設定 各個 旗號
    LEDjson ="ON";
    Send = true ;
    Serial.print("LED =");
    Serial.println(LEDjson);
  }

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

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

}

//===========================================================
String printHex(byte *buffer, byte bufferSize) {
      String id = "";
      for (byte i = 0; i < bufferSize; i++) {
        id += buffer[i] < 0x10 ? "0" : "";
        id += String(buffer[i], HEX);
        id +=" ";
      }
      return id;
}
//===========================================================
//副程式  setup wifi
void setup_wifi() {
  delay(10);
  // We start by connecting to a WiFi network
  Serial.println();
  Serial.print("Connecting to ");
  Serial.println(ssid);     //print ssid
  WiFi.begin(ssid, pass);  //初始化WiFi 函式庫並回傳目前的網路狀態
  while (WiFi.status() != WL_CONNECTED) {
    delay(500);
    Serial.print(".");
  }   //假設 wifi 未連接 show ………

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

  } //(Flash)

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

  Count=Count-1;
  if (Count == 0 ){
    Timer=false;
    digitalWrite(LED, HIGH);
    ledState = false;
    }
  } //(Timer)
 
 
  ////判斷 旗號 Send 是否為真 回傳MQTT訊息到MQTT Broker 
  if (Send) {
    // Convert JSON string to character array
    Serial.print("Publish message: ");
    Serial.println(LEDjson);
    LEDjson.trim();

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

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

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

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

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

    while (1);
  }

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

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


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


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

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



    json="";
    String json1=printHex(mfrc522.uid.uidByte, mfrc522.uid.size);
    json1.toUpperCase();
    json = json + json1;
    json.trim();

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

    mqttClient.beginMessage(PubTopic3,  json.length(), retained, qos, dup);
    mqttClient.print(json);
    mqttClient.endMessage();
   
    /*
    json="";
    json = "PICC type: ";
    json =  json + Type;
    json.trim();
   
    retained = false;
    qos = 1;
    dup = false;

    mqttClient.beginMessage(PubTopic4,  json.length(), retained, qos, dup);
    mqttClient.print(json);
    mqttClient.endMessage();
    */


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


 3. node-red  程式

1) 需 新安裝sqlite 的節點   node-red-node-sqlite 

2)  修改 mqtt in , mqtt out 裡面的 alex9ufo 變成自己的英文名

3)  修改 發行權杖為自己的 發行權杖

4) 資料庫新使用前 只需要按  建立LED資料庫  建立RFID資料庫 一次 否則資料庫無法開檔




























Node-red 程式

  [{"id":"de94877be821c022","type":"tab","label":"2024產專班 作業2 ","disabled":false,"info":"","env":[]},{"id":"d8886d19ed817b4d","type":"sqlite","z":"de94877be821c022","mydb":"f5c97c74cc496505","sqlquery":"msg.topic","sql":"","name":"LED_STATUS","x":540,"y":380,"wires":[["b693b296bc9dbb05"]]},{"id":"3d78470fe40a253b","type":"function","z":"de94877be821c022","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":380,"wires":[["d8886d19ed817b4d"]]},{"id":"b5aa34b08dd0e6c5","type":"ui_button","z":"de94877be821c022","name":"","group":"1ce7dd030081ad24","order":9,"width":3,"height":1,"passthru":false,"label":"建立LED資料庫","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"建立資料庫","payloadType":"str","topic":"topic","topicType":"msg","x":100,"y":380,"wires":[["3d78470fe40a253b","6403be567fef0c83"]]},{"id":"2ad08ae5e4938728","type":"ui_button","z":"de94877be821c022","name":"","group":"1ce7dd030081ad24","order":1,"width":2,"height":1,"passthru":false,"label":"ON","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"on","payloadType":"str","topic":"topic","topicType":"msg","x":70,"y":20,"wires":[["9260190ef89016b8","3f94a3388af27988","9ea8d029f3defa26"]]},{"id":"8c922987b04504e5","type":"ui_button","z":"de94877be821c022","name":"","group":"1ce7dd030081ad24","order":2,"width":2,"height":1,"passthru":false,"label":"OFF","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"off","payloadType":"str","topic":"topic","topicType":"msg","x":70,"y":60,"wires":[["9260190ef89016b8","3f94a3388af27988","9ea8d029f3defa26"]]},{"id":"028329a10f91c6ba","type":"ui_button","z":"de94877be821c022","name":"","group":"1ce7dd030081ad24","order":4,"width":2,"height":1,"passthru":false,"label":"TIMER","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"timer","payloadType":"str","topic":"topic","topicType":"msg","x":80,"y":100,"wires":[["9260190ef89016b8","3f94a3388af27988","9ea8d029f3defa26"]]},{"id":"39d1fb8a26430fbd","type":"ui_button","z":"de94877be821c022","name":"","group":"1ce7dd030081ad24","order":5,"width":2,"height":1,"passthru":false,"label":"FLASH","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"flash","payloadType":"str","topic":"topic","topicType":"msg","x":80,"y":140,"wires":[["9260190ef89016b8","3f94a3388af27988","9ea8d029f3defa26"]]},{"id":"2289c4addb72da83","type":"function","z":"de94877be821c022","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":240,"wires":[["e88e4e11603904f6"]]},{"id":"9260190ef89016b8","type":"ui_audio","z":"de94877be821c022","name":"","group":"1ce7dd030081ad24","voice":"Microsoft Hanhan - Chinese (Traditional, Taiwan)","always":true,"x":245,"y":80,"wires":[],"l":false},{"id":"e88e4e11603904f6","type":"sqlite","z":"de94877be821c022","mydb":"f5c97c74cc496505","sqlquery":"msg.topic","sql":"","name":"LED_STATUS","x":440,"y":240,"wires":[["589b1aff948b3263","110bac143ce4ff27"]]},{"id":"b693b296bc9dbb05","type":"debug","z":"de94877be821c022","name":"debug ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":690,"y":380,"wires":[]},{"id":"589b1aff948b3263","type":"debug","z":"de94877be821c022","name":"debug ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":630,"y":240,"wires":[]},{"id":"7db7512aa212e480","type":"ui_button","z":"de94877be821c022","name":"","group":"1ce7dd030081ad24","order":8,"width":4,"height":1,"passthru":false,"label":"檢視資料庫資料","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"檢視資料","payloadType":"str","topic":"topic","topicType":"msg","x":100,"y":320,"wires":[["110bac143ce4ff27","6403be567fef0c83"]]},{"id":"110bac143ce4ff27","type":"function","z":"de94877be821c022","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":400,"y":320,"wires":[["f1ef1d5f3e800de8"]]},{"id":"29be5aad6abe5c63","type":"ui_table","z":"de94877be821c022","group":"1ce7dd030081ad24","name":"","order":6,"width":10,"height":10,"columns":[],"outputs":0,"cts":false,"x":730,"y":320,"wires":[]},{"id":"f1ef1d5f3e800de8","type":"sqlite","z":"de94877be821c022","mydb":"f5c97c74cc496505","sqlquery":"msg.topic","sql":"","name":"LED_STATUS","x":580,"y":320,"wires":[["29be5aad6abe5c63"]]},{"id":"39c7f86f462c38f1","type":"sqlite","z":"de94877be821c022","mydb":"f5c97c74cc496505","sqlquery":"msg.topic","sql":"","name":"LED_STATUS","x":860,"y":440,"wires":[["d1bdc104e5232dd8"]]},{"id":"93f502d303c88e81","type":"ui_button","z":"de94877be821c022","name":"","group":"1ce7dd030081ad24","order":7,"width":3,"height":1,"passthru":false,"label":"刪除所有資料 ","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"刪除所有資料 ","payloadType":"str","topic":"topic","topicType":"msg","x":100,"y":440,"wires":[["9559446ac1a4dd97","d26bd8f6c50cf636"]]},{"id":"b4c9b1e0bd818bb8","type":"function","z":"de94877be821c022","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":670,"y":440,"wires":[["39c7f86f462c38f1"]]},{"id":"9559446ac1a4dd97","type":"ui_audio","z":"de94877be821c022","name":"","group":"1ce7dd030081ad24","voice":"Microsoft Hanhan - Chinese (Traditional, Taiwan)","always":true,"x":225,"y":500,"wires":[],"l":false},{"id":"d26bd8f6c50cf636","type":"ui_toast","z":"de94877be821c022","position":"prompt","displayTime":"3","highlight":"","sendall":true,"outputs":1,"ok":"OK","cancel":"Cancel","raw":true,"className":"","topic":"","name":"","x":290,"y":440,"wires":[["6041efbb6dfef689"]]},{"id":"6041efbb6dfef689","type":"function","z":"de94877be821c022","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":460,"y":440,"wires":[["b4c9b1e0bd818bb8"],[]]},{"id":"6403be567fef0c83","type":"ui_audio","z":"de94877be821c022","name":"","group":"1ce7dd030081ad24","voice":"Microsoft Hanhan - Chinese (Traditional, Taiwan)","always":true,"x":245,"y":340,"wires":[],"l":false},{"id":"d1bdc104e5232dd8","type":"link out","z":"de94877be821c022","name":"link out 57","mode":"link","links":["87b1fdf101776a2f"],"x":845,"y":380,"wires":[]},{"id":"87b1fdf101776a2f","type":"link in","z":"de94877be821c022","name":"link in 55","links":["d1bdc104e5232dd8"],"x":915,"y":380,"wires":[["110bac143ce4ff27"]]},{"id":"3f94a3388af27988","type":"mqtt out","z":"de94877be821c022","name":"Control LED out","topic":"alex9ufo/2024/RFID/LED_control","qos":"1","retain":"true","respTopic":"","contentType":"","userProps":"","correl":"","expiry":"","broker":"603bb104.d6134","x":280,"y":20,"wires":[]},{"id":"da7e7c410844086a","type":"mqtt in","z":"de94877be821c022","name":"Control LED in","topic":"alex9ufo/2024/RFID/LED_status","qos":"1","datatype":"auto-detect","broker":"603bb104.d6134","nl":false,"rap":true,"rh":0,"inputs":0,"x":100,"y":240,"wires":[["2289c4addb72da83"]]},{"id":"49b9f4b55227af6b","type":"comment","z":"de94877be821c022","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":80,"y":640,"wires":[]},{"id":"12dc668a45158d33","type":"comment","z":"de94877be821c022","name":"資料庫位置 C:\\Users\\User\\.node-red\\2024EX2_1.db","info":"","x":190,"y":680,"wires":[]},{"id":"2fb1d9aa7e7a322d","type":"ui_button","z":"de94877be821c022","name":"","group":"26999cd202d71546","order":4,"width":3,"height":1,"passthru":false,"label":"建立RFID資料庫","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"建立資料庫","payloadType":"str","topic":"topic","topicType":"msg","x":80,"y":720,"wires":[["20de3f201a4da6c8","7605c0bb7b0637e4"]]},{"id":"f69b58efbd541cfd","type":"ui_button","z":"de94877be821c022","name":"","group":"26999cd202d71546","order":5,"width":4,"height":1,"passthru":false,"label":"檢視資料庫資料","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"檢視資料","payloadType":"str","topic":"topic","topicType":"msg","x":80,"y":1360,"wires":[["08880e70f61a5919","a926822cc24e7e26","1ee1c6e9e0b1ecc7"]]},{"id":"e0428363e518f022","type":"ui_button","z":"de94877be821c022","name":"","group":"26999cd202d71546","order":6,"width":3,"height":1,"passthru":false,"label":"刪除RFID資料庫","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"刪除所有資料","payloadType":"str","topic":"topic","topicType":"msg","x":80,"y":1440,"wires":[["9c472cfd1a059059","08880e70f61a5919"]]},{"id":"20de3f201a4da6c8","type":"function","z":"de94877be821c022","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":300,"y":720,"wires":[["af6f3e3aaa635b0b"]]},{"id":"baab3664986e2095","type":"debug","z":"de94877be821c022","name":"debug","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":630,"y":1000,"wires":[]},{"id":"72ba0c8d6992ed2e","type":"function","z":"de94877be821c022","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":320,"y":1000,"wires":[["821536d470c11949","8f4ae6e4f835aadd"]]},{"id":"ed134d22fa068cce","type":"function","z":"de94877be821c022","name":"DROP DATABASE","func":"//DROP RFIDtable\n\nmsg.topic = \"DROP TABLE RFIDtable\" ;\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":640,"y":1460,"wires":[["572a42cab5fbfb24"]]},{"id":"b27709c0f2739ab1","type":"debug","z":"de94877be821c022","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":1050,"y":1460,"wires":[]},{"id":"1ee1c6e9e0b1ecc7","type":"function","z":"de94877be821c022","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":720,"y":1360,"wires":[["eb7efa9b721cd725","661c5bce45c2a306"]]},{"id":"312bfc883680a182","type":"mqtt in","z":"de94877be821c022","name":"RFID in","topic":"alex9ufo/2024/RFID/RFID_UID","qos":"2","datatype":"auto-detect","broker":"841df58d.ee5e98","nl":false,"rap":true,"rh":0,"inputs":0,"x":50,"y":1080,"wires":[["bd651a4cff859c90","8e7400a11f6417cc"]]},{"id":"6e2abccef79a108d","type":"ui_audio","z":"de94877be821c022","name":"","group":"26999cd202d71546","voice":"Microsoft Hanhan - Chinese (Traditional, Taiwan)","always":true,"x":520,"y":1200,"wires":[]},{"id":"6e9fecbdbdf2efdf","type":"function","z":"de94877be821c022","name":"function ","func":"var temp= msg.payload;\nmsg.payload= \"新增一筆資料\" + temp;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":360,"y":1160,"wires":[["6e2abccef79a108d","68c7efdc1b782bd4","625709eb82e1bba4"]]},{"id":"08880e70f61a5919","type":"ui_audio","z":"de94877be821c022","name":"","group":"26999cd202d71546","voice":"Microsoft Hanhan - Chinese (Traditional, Taiwan)","always":true,"x":280,"y":1400,"wires":[]},{"id":"7605c0bb7b0637e4","type":"ui_audio","z":"de94877be821c022","name":"","group":"26999cd202d71546","voice":"Microsoft Hanhan - Chinese (Traditional, Taiwan)","always":true,"x":260,"y":760,"wires":[]},{"id":"dcb7ea26dfa13c02","type":"ui_text","z":"de94877be821c022","group":"26999cd202d71546","order":2,"width":8,"height":1,"name":"","label":"自動感應RFID查詢的uid","format":"{{msg.payload}}","layout":"row-left","className":"","x":730,"y":1160,"wires":[]},{"id":"68c7efdc1b782bd4","type":"function","z":"de94877be821c022","name":"取得UID號碼","func":"var myRFID = flow.get('uid_temp');\nmsg.payload=myRFID;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":530,"y":1160,"wires":[["dcb7ea26dfa13c02"]]},{"id":"821536d470c11949","type":"function","z":"de94877be821c022","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":520,"y":1060,"wires":[["2cd90c96fda0f25d"]]},{"id":"2cd90c96fda0f25d","type":"function","z":"de94877be821c022","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":690,"y":1060,"wires":[["b948371d0bc949ce"]]},{"id":"b948371d0bc949ce","type":"http request","z":"de94877be821c022","name":"","method":"POST","ret":"txt","paytoqs":false,"url":"https://notify-api.line.me/api/notify","tls":"","persist":false,"proxy":"","authType":"","x":840,"y":1060,"wires":[["cb7871ed5d68fd11"]]},{"id":"cb7871ed5d68fd11","type":"debug","z":"de94877be821c022","name":"debug ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":970,"y":1060,"wires":[]},{"id":"7688ecaf7ac9454e","type":"comment","z":"de94877be821c022","name":"Line Notify Message ","info":"","x":830,"y":1020,"wires":[]},{"id":"84f432e5e44c570e","type":"ui_table","z":"de94877be821c022","group":"26999cd202d71546","name":"","order":3,"width":10,"height":10,"columns":[],"outputs":0,"cts":false,"x":1030,"y":1360,"wires":[]},{"id":"9c472cfd1a059059","type":"ui_toast","z":"de94877be821c022","position":"prompt","displayTime":"3","highlight":"","sendall":true,"outputs":1,"ok":"OK","cancel":"Cancel","raw":true,"className":"","topic":"","name":"","x":270,"y":1460,"wires":[["4c972d2605a20318"]]},{"id":"4c972d2605a20318","type":"function","z":"de94877be821c022","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":440,"y":1460,"wires":[["ed134d22fa068cce"],[]]},{"id":"14396fb8a2b77316","type":"link out","z":"de94877be821c022","name":"link out 58","mode":"link","links":["c19329df615520bf"],"x":605,"y":960,"wires":[]},{"id":"a926822cc24e7e26","type":"ui_toast","z":"de94877be821c022","position":"top right","displayTime":"3","highlight":"","sendall":true,"outputs":0,"ok":"OK","cancel":"Cancel","raw":false,"className":"","topic":"","name":"","x":290,"y":1320,"wires":[]},{"id":"c4f1192fe4e205df","type":"debug","z":"de94877be821c022","name":"debug","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":610,"y":720,"wires":[]},{"id":"bd651a4cff859c90","type":"ui_toast","z":"de94877be821c022","position":"top right","displayTime":"3","highlight":"","sendall":true,"outputs":0,"ok":"OK","cancel":"Cancel","raw":false,"className":"","topic":"","name":"","x":170,"y":1140,"wires":[]},{"id":"af6f3e3aaa635b0b","type":"sqlite","z":"de94877be821c022","mydb":"f87c808cc786ae80","sqlquery":"msg.topic","sql":"","name":"RFID dB","x":480,"y":720,"wires":[["c4f1192fe4e205df"]]},{"id":"8f4ae6e4f835aadd","type":"sqlite","z":"de94877be821c022","mydb":"f87c808cc786ae80","sqlquery":"msg.topic","sql":"","name":"RFID dB","x":500,"y":1000,"wires":[["14396fb8a2b77316","baab3664986e2095"]]},{"id":"eb7efa9b721cd725","type":"sqlite","z":"de94877be821c022","mydb":"f87c808cc786ae80","sqlquery":"msg.topic","sql":"","name":"RFID dB","x":860,"y":1360,"wires":[["84f432e5e44c570e"]]},{"id":"572a42cab5fbfb24","type":"sqlite","z":"de94877be821c022","mydb":"f87c808cc786ae80","sqlquery":"msg.topic","sql":"","name":"RFID dB","x":820,"y":1460,"wires":[["b27709c0f2739ab1","1ee1c6e9e0b1ecc7"]]},{"id":"661c5bce45c2a306","type":"debug","z":"de94877be821c022","name":"debug 299","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":870,"y":1320,"wires":[]},{"id":"100e89592bf1117b","type":"ui_button","z":"de94877be821c022","name":"","group":"26999cd202d71546","order":1,"width":"2","height":1,"passthru":false,"label":"模擬RFID UID(不從ESP32)","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"on","payloadType":"str","topic":"topic","topicType":"msg","x":120,"y":840,"wires":[["dd7d54f37fadf881"]]},{"id":"dd7d54f37fadf881","type":"function","z":"de94877be821c022","name":"模擬RFID UID","func":"const MIN = 0;\nconst MAX = 255;\nvar hex_v=0;\n//The maximum is exclusive and the minimum is inclusive\nfunction getRandomInt(min = MIN, max = MAX) {\n    min = Math.ceil(min);\n    max = Math.floor(max);\n    return Math.floor(Math.random() * (max - min)) + min;\n}\n\nfunction decToHex(dec) {\n  hex_v= dec.toString(16);\n  if(hex_v<10)\n  {\n    hex_v= '0'+hex_v;\n    }\n    return hex_v;\n}\n\nvar no1=getRandomInt(min = MIN, max = MAX);\nvar no2=getRandomInt(min = MIN, max = MAX);\nvar no3=getRandomInt(min = MIN, max = MAX);\nvar no4=getRandomInt(min = MIN, max = MAX);\nvar no5=getRandomInt(min = MIN, max = MAX);\nvar no6=getRandomInt(min = MIN, max = MAX);\nvar no7=getRandomInt(min = MIN, max = MAX);\nvar no8=getRandomInt(min = MIN, max = MAX);\n\nvar var1=decToHex(no1);\nvar var2=decToHex(no2);\nvar var3=decToHex(no3);\nvar var4=decToHex(no4);\nvar var5=decToHex(no5);\nvar var6=decToHex(no6);\nvar var7=decToHex(no7);\nvar var8=decToHex(no8);\n\nmsg.payload=[var1,var2,var3,var4];\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":360,"y":840,"wires":[["b190f9066a21afac"]]},{"id":"b190f9066a21afac","type":"function","z":"de94877be821c022","name":"整理輸出","func":"var temp=\"\";\nfor (var i = 0; i < msg.payload.length; i++) \n{\n    temp=temp+ \" \" + msg.payload[i];\n    temp=temp.toUpperCase();\n}\nmsg.payload=temp;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":580,"y":840,"wires":[["e4d6d5f39fc4941f","35307f3dbaeea2b0"]]},{"id":"35307f3dbaeea2b0","type":"mqtt out","z":"de94877be821c022","name":"RFID  out","topic":"alex9ufo/2024/RFID/RFID_UID","qos":"1","retain":"true","respTopic":"","contentType":"","userProps":"","correl":"","expiry":"","broker":"841df58d.ee5e98","x":800,"y":880,"wires":[]},{"id":"e4d6d5f39fc4941f","type":"debug","z":"de94877be821c022","name":"debug 300","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":730,"y":840,"wires":[]},{"id":"625709eb82e1bba4","type":"ui_toast","z":"de94877be821c022","position":"top right","displayTime":"3","highlight":"","sendall":true,"outputs":0,"ok":"OK","cancel":"Cancel","raw":false,"className":"","topic":"","name":"","x":550,"y":1120,"wires":[]},{"id":"c19329df615520bf","type":"link in","z":"de94877be821c022","name":"link in 56","links":["609e318ca3262939","14396fb8a2b77316","70487decb239f3c4","ce142a687d0b79f1"],"x":615,"y":1320,"wires":[["1ee1c6e9e0b1ecc7"]]},{"id":"8e7400a11f6417cc","type":"function","z":"de94877be821c022","name":"Store UID","func":"flow.set(\"uid_temp\", msg.payload);\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":200,"y":1080,"wires":[["72ba0c8d6992ed2e","6e9fecbdbdf2efdf"]]},{"id":"9ea8d029f3defa26","type":"debug","z":"de94877be821c022","name":"debug ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":310,"y":140,"wires":[]},{"id":"f5c97c74cc496505","type":"sqlitedb","db":"C:\\Users\\User\\.node-red\\2024EX2.db","mode":"RWC"},{"id":"1ce7dd030081ad24","type":"ui_group","name":"LED","tab":"53d4771468c19b49","order":1,"disp":true,"width":"10","collapse":false,"className":""},{"id":"603bb104.d6134","type":"mqtt-broker","name":"","broker":"broker.mqtt-dashboard.com","port":"1883","clientid":"","autoConnect":true,"usetls":false,"compatmode":false,"protocolVersion":"4","keepalive":"15","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","birthMsg":{},"closeTopic":"","closePayload":"","closeMsg":{},"willTopic":"","willQos":"0","willPayload":"","willMsg":{},"userProps":"","sessionExpiry":""},{"id":"26999cd202d71546","type":"ui_group","name":"RFID","tab":"53d4771468c19b49","order":2,"disp":true,"width":"10","collapse":false,"className":""},{"id":"841df58d.ee5e98","type":"mqtt-broker","name":"","broker":"broker.hivemq.com","port":"1883","clientid":"","autoConnect":true,"usetls":false,"compatmode":false,"protocolVersion":"4","keepalive":"15","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","birthMsg":{},"closeTopic":"","closePayload":"","closeMsg":{},"willTopic":"","willQos":"0","willPayload":"","willMsg":{},"userProps":"","sessionExpiry":""},{"id":"f87c808cc786ae80","type":"sqlitedb","db":"C:\\Users\\User\\.node-red\\2024EX2_1.db","mode":"RWC"},{"id":"53d4771468c19b49","type":"ui_tab","name":"2024產專班 作業2 ","icon":"dashboard","order":123,"disabled":false,"hidden":false}]


   4. 利用 sqlite db browser  程式 

    瀏覽 資料庫結構 與 資料庫內容

 







  

   5.  實作後上傳YT

https://www.youtube.com/shorts/xEOjWwzSvPg





2024產專班 作業2

 2024產專班 作業2   1. 系統圖       ESP32+MFRC522 組成RFID Reader 可以將RFID卡片的UID 透過 MQTT協定    上傳(發行 主題 (:topic) alex9ufo/2024/RFID/RFID_UID  ,, Payload...