Smart Logging of Data Using NodeMCU, MySQL, RFID

#include <NTPClient.h>
#include <WiFiUdp.h>

WiFiUDP ntpUDP;
NTPClient timeClient(ntpUDP, "asia.pool.ntp.org",19800);

#include <SPI.h>
#include <MFRC522.h>


#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>
#include <ESP8266WiFi.h>
#include <WiFiClient.h>

#define SS_PIN D4
#define RST_PIN D3

MFRC522 mfrc522(SS_PIN, RST_PIN);   // Create MFRC522 instance.

char ssid[] = "ssid";                 // Network Name
char pass[] = "pwd";                 // Network Password
byte mac[6];

WiFiClient client;
MySQL_Connection conn((Client *)&client);

String query="";
char query1[128];
char query2[128];
char query3[128];
char query4[128];

String Name="",regno="";
String UID= "";


IPAddress server_addr(192,xxx ,x, xxx);          // MySQL server IP
char user[] = "";           // MySQL user
char password[] = "";       // MySQL password

void setup() 
{
  Serial.begin(9600);   // Initiate a serial communication

  Serial.print("Connecting to ");
  Serial.println(ssid);
  //WiFi.config(ip, gateway, subnet); 
  WiFi.begin(ssid, pass);

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

     

  }

  Serial.println("");
  Serial.println("WiFi Connected");
   timeClient.begin();

  Serial.print("Assigned IP: ");
  Serial.print(WiFi.localIP());
  Serial.println("");

  Serial.println("Connecting to database");

  while (conn.connect(server_addr, 3306, user, password) != true) {
    delay(200);
    Serial.print ( "." );
  }

  Serial.println("");
  Serial.println("Connected to SQL Server!");  

  
  SPI.begin();      // Initiate  SPI bus

  mfrc522.PCD_Init();   // Initiate MFRC522

  Serial.println("Put your card to the reader...");
  Serial.println();
 

  
  
}
void entry()
{
 
  timeClient.update();
  //Serial.println(UID+Name+regno);
  Serial.println("Enter the Barcode");
  while (Serial.available()==0)  {}  
   String bar=Serial.readString();  
   Serial.println("Barcode :"+bar);
   
  String formattedDate=timeClient.getFormattedDate();
  int splitT =  formattedDate.indexOf("T");
  String Date = formattedDate.substring(0, splitT);
 

  String Time = formattedDate.substring(splitT+1, formattedDate.length()-1);

  
    


  query="INSERT INTO `rfid`.`rfid_check` (`UID`, `Name`, `Reg_No.`, `Issue_Date`, `Issue_Time`, `Remote_Code`) VALUES ('"+UID+"','"+Name+"','"+regno+"','"+Date+"','"+Time+"','"+bar+"');";
  

  Serial.println("Recording data.");
  //Serial.println(query);
  strcpy(query3, query.c_str()); 
  
  MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
  
  cur_mem->execute(query3);

  delete cur_mem;

}
void Exit()
{
 
  timeClient.update();
  //Serial.println(UID+Name+regno);
  String formattedDate=timeClient.getFormattedDate();
  int splitT =  formattedDate.indexOf("T");
  String Date = formattedDate.substring(0, splitT);
 

  String Time = formattedDate.substring(splitT+1, formattedDate.length()-1);
    
  

  query="UPDATE `rfid`.`rfid_check` SET `Return_Date`='"+Date+"', `Return_Time`='"+Time+"' WHERE `UID`='"+UID+"';";
  

  Serial.println("Recording data.");
  //Serial.println(query);
  strcpy(query4, query.c_str()); 
  
  MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
  
  cur_mem->execute(query4);

  delete cur_mem;
}
void entry_exit()
{
  int flag=0;
    //Serial.println(UID+Name+regno);
  query="SELECT * FROM rfid.rfid_check where UID=\""+UID+"\"";
  //Serial.println(query);
   strcpy(query2, query.c_str()); 

  MySQL_Cursor *cur_mem1 = new MySQL_Cursor(&conn);
  
  cur_mem1->execute(query2);

  column_names *cols = cur_mem1->get_columns();
   

  // Read the rows and print them
  row_values *row = NULL;
  row=cur_mem1->get_next_row();

     while(row != NULL) {
   
      flag=1;
      
 
     
         row=cur_mem1->get_next_row();
    }
 delete cur_mem1;
  query="";

    if(flag==0)
    {
      Serial.println("Proceed with entry");
      entry();
      Serial.println("Entry Done");
    }
    else
    {
        Serial.println("Proceed with exit");
        Exit();
        Serial.println("Exit Done");
        
    }
}
void check()
{
  int flag=0;
    Serial.println(UID);
  query="SELECT * FROM rfid.rfid_reg where UID=\""+UID+"\"";
  //Serial.println(query);
   strcpy(query1, query.c_str()); 

  MySQL_Cursor *cur_mem1 = new MySQL_Cursor(&conn);
  
  cur_mem1->execute(query1);

  column_names *cols = cur_mem1->get_columns();
   

  // Read the rows and print them
  row_values *row = NULL;
  row=cur_mem1->get_next_row();
  
     while(row != NULL) {
   
      flag=1;
      Name=row->values[1];
  regno=row->values[2];
 
     
         row=cur_mem1->get_next_row();
    }
 delete cur_mem1;
  query="";
  
    if(flag==1)
    {
      // Serial.println("Proceed with entry/exit");
        entry_exit();
    }
    else
    {
      Serial.println("Faculty not registered");
    }


  
}

void loop() 
{
timeClient.update();

  // Look for new cards
  if ( ! mfrc522.PICC_IsNewCardPresent()) 
  {
    return;
  }
  // Select one of the cards
  if ( ! mfrc522.PICC_ReadCardSerial()) 
  {
    return;
  }
  //Show UID on serial monitor
  Serial.print("UID tag :");
  
  for (byte i = 0; i < mfrc522.uid.size; i++) 
  {
     UID.concat(String(mfrc522.uid.uidByte[i] < 0x10 ? "0" : ""));
     UID.concat(String(mfrc522.uid.uidByte[i], HEX));
  }
  UID.toUpperCase();


  if(UID!="" or UID.length()==8)check();

  UID="";
  Name="";
  regno="";
    delay(1000); //10 sec
  Serial.println();
   Serial.println("Put your card to the reader...");
  Serial.println();

 
}