Published February 24, 2019 © CC BY-NC-SA

Makerspace NFC Part Management System

Use NFC stickers to track electronic components with the use of a Particle Photon and a database back-end, along with a webpage interface.

AdvancedFull instructions provided10 hours3,753

Most Creative - US Based Project

2019 China-US Young Maker Competition

Things used in this project

Hardware components

Particle Photon

RC522 NFC Tage Reader

DFRobot 128x128 OLED

Adafruit Capacitive Touch Sensor Breakout - MPR121

NFC Stickers

Software apps and online services

Autodesk Fusion

Particle Build Web IDE

MySQL

NodeJS

Hand tools and fabrication machines

3D Printer (generic)

Soldering iron (generic)

Story

The Purpose

Imagine being the owner of a makerspace, where it is up to you to organize and track what you have in inventory. Sometimes your members might take components and not return them, or they bring their own stuff and then combine it with the space’s. This issue is why I have created an inventory management system that uses NFC and storage bins to organize and track components with the power of IoT.

1 / 3

Demonstration Video

General Layout

Each bin gets assigned a name, description, general type, and an NFC UID that corresponds with its sticker. Parts go into bins, and each part has a name, description, quantity, type, and which bin ID it belongs to. All this data is hosted on a MySQL database. I used a NodeJS webserver to create a cloud API which could then do certain actions to the MySQL database, such as creating new parts or removing a bin. On the hardware side, I used a Particle Photon with a 128x128 OLED screen and NFC reader, along with an MPR121 for virtual buttons.

I have also created a webpage that allows for full access to the API, where it is possible to change almost anything about the bins or parts in an easy and clean way.

Webserver

I decided that using the Express library would be best for my use case, as it allows for the creation of an easy RESTful API, along with easy path creation. I began by using npm to install express, along with cors and mysql. Then I included them in the top with “require(‘package_name’)”. I used the createPool function in the MySQL library to connect to the MySQL database, as it allows for the creation and release of connections easily and safely. Each API path has its own function, where “app.post” is for POST requests, and “app.get” is for GET requests. The string is the path to send the data, such as “/parts/get_all_bins”. The content of the functions varies, but in general, each one establishes a connection to the database, parses the request data, and executes a certain query. Adding a buffer between the database and device creates a layer of security and control.

MySQL Database

So now there is a way to access and change the data, but where or how is the data stored? When I installed the MySQL database, I also installed MySQL Workbench, which allows users to manage their database. I created a new schema called “components” and made two tables: parts and bins.

Each table needed different columns, since bins don’t have a quantity and parts don’t need an NFC UID.

1 / 4

I also created a new user with a password to use with the NodeJS server. Doing this instead of using the root password greatly increases security if someone gains access to the server’s code.

NFC RFID Tags

When I first started this project, I only had a few NFC cards, so I had to find a new way to store and scan data. So then I went onto AliExpress in search for a better medium. I came across NFC stickers, which house a very small antenna and chip, but are paper-thin and small. This allows for them to be stuck onto bins and read from with ease.

1 / 2

Hardware

So how exactly does the bin get scanned, and how are the quantities changed? For this issue, I used an RC522 RFID reader to read the NFC tags. To display the data and instructions, I used a 128x128 OLED screen that uses the SSD1351 controller. Since this project is meant for makerspaces, I wanted to avoid physical buttons, as they can wear out. I opted to use an MPR121 capacitive touch board due to its cheapness and I2C interface.

It can handle up to 12 different channels. In my design for the case, I added 3 places where 1/4-inch copper tape is attached. These pieces of copper act as buttons when touched.

1 / 2

Code for hardware

Since the system relies on a series of steps, I developed a system that uses a state machine to control which pages get displayed. It goes in the following order: search for card -> select bin -> display bin -> display part -> change quantity. In order to parse the responses from the webserver, I used the SparkJson library. At each stage, a request gets generated and sent to the webserver.

Webpage

This was the hardest aspect of the project to get correct. It has three components: HTML, JavaScript, and CSS.

1 / 2

Since I am still new to CSS, I used W3 School’s W3.CSS classes. In order to ensure a clean interface, I made a simple grid of buttons, and each button corresponds to a modal card that pops up. Each modal card also has a close button in the top-right. Buttons such as list bins and list items display a table that updates when clicked. Buttons such as add a new item and add bin contain a form.

1 / 8

The JavaScript code is too complex to explain in this writeup, but it basically contains various functions that get called whenever a form is submitted. Some functions also populate select elements and tables.

Usage

To use the system, someone will first populate the list of bins they have by entering in their names and other data. Then the parts get created in a similar manner. There is a button to create parts in bulk if necessary, such as having a bin full of various values of resistors. Once everything is created, bins can now be scanned with the photon.

Future Plans

In the future, I hope to expand the system to include a log-in and ownership aspect. Users could scan their makerspace RFID card and then have access to their own personal bins.

Schematics

Code

// This #include statement was automatically added by the Particle IDE.
#include <Adafruit_MPR121.h>

// This #include statement was automatically added by the Particle IDE.
#include <MFRC522.h>

// This #include statement was automatically added by the Particle IDE.
#include <SparkJson.h>

#include "application.h"
#include "HttpClient.h"

#include "Particle.h"

#define cs   D4
#define sclk A3
#define mosi A5
#define rst  D5
#define dc   D6
#define SS_PIN A2
#define RST_PIN D2

#define LEFT 0
#define CENTER 2
#define RIGHT 4

// Color definitions
#define	BLACK           0x0000
#define	BLUE            0x001F
#define	RED             0xF800
#define	GREEN           0x07E0
#define CYAN            0x07FF
#define MAGENTA         0xF81F
#define YELLOW          0xFFE0  
#define WHITE           0xFFFF

#include "Adafruit_mfGFX/Adafruit_mfGFX.h"
#include "Adafruit_SSD1351_Photon.h"

// Option 1: Hardware SPI - uses some analog pins, but much faster
Adafruit_SSD1351 tft = Adafruit_SSD1351(cs, dc, rst);
Adafruit_MPR121 cap = Adafruit_MPR121();
MFRC522 mfrc522(SS_PIN, RST_PIN);


HttpClient http;
http_header_t headers[] = {
    { "Content-Type", "application/json" },
    {"Accept", "*/*"},
    {NULL, NULL}
};

http_request_t request;
http_response_t response;

uint16_t lasttouched = 0;
uint16_t currtouched = 0;

char char_buf[500];

enum States {
    SEARCH_FOR_CARD,
    SELECT_BIN,
    DISPLAY_BIN,
    DISPLAY_PART,
    CHANGE_QUANTITY
};

struct card_uid{
    int size;
    int id_digits[7];
    String id_string;
} current_card;

int bin_id = 0, part_id = 0, part_qty = 0;;

States state = SEARCH_FOR_CARD;

void setup(void) {
  StaticJsonBuffer<400> jsonBuffer;
  Serial.begin(9600);
  Serial.print("hello!");
  tft.begin();
  reset_screen();
  mfrc522.setSPIConfig();
  mfrc522.PCD_Init();	// Init MFRC522 card
  cap.begin(0x5A);
  request.hostname = "IP address goes here";
  request.port = 3010;
}

void loop() {
  switch(state){
      case SEARCH_FOR_CARD:
        reset_screen();
        Serial.println("Scan a MIFARE Classic card.");
        tft.setTextSize(2);
        tft.println("Waiting for card to be scanned");
        current_card.id_string = "";
	    wait_for_card();
	    state = SELECT_BIN;
        break;
      case SELECT_BIN:
        select_bin();
        break;
      case DISPLAY_BIN:
        part_id = display_parts();
        //delay(1000);
        break;
      case DISPLAY_PART:
        display_part(part_id);
        //delay(1000);
        break;
      case CHANGE_QUANTITY:
        change_quantity(part_id);
        //delay(1000);
        break;
  }
}

void change_quantity(int item_id){
    request.path = "/parts/change_amount";
    reset_screen();
    int amount = 0, quantity = part_qty;
    String inc_dec = "";
    tft.setTextSize(1);
    tft.println("Change  |  New amount");
    tft.setCursor(0,120);
    tft.println("Increase|Set|Decrease");
    tft.setTextSize(3);
    while(1){
        delay(200);
        Particle.process();
        tft.fillRect(0,40,128,60,BLACK);
        tft.setCursor(20,45);
        tft.print(amount);
        tft.print(" | ");
        tft.print(quantity);
        while(1){
            currtouched = cap.touched();
            Particle.process();
            if((currtouched & _BV(LEFT))){
                amount++;
                quantity++;
                break;
            }
            else if((currtouched & _BV(CENTER))){
                if(amount >= 0){
                    inc_dec = "true";
                }
                else{
                    inc_dec = "false";
                }
                request.body = "{\"id\":"+String(item_id)+",\"inc_dec\":"+inc_dec+",\"amount\":"+String(abs(amount))+"}";
                http.post(request,response, headers);
                reset_screen();
                tft.setTextSize(2);
                tft.println("Success!");
                delay(2000);
                state = SEARCH_FOR_CARD;
                return;
            }
            else if((currtouched & _BV(RIGHT))){
                if(quantity>0){
                    amount--;
                    quantity--;
                    break;
                }
            }
        }
    }
}

void display_part(int item_id){
    reset_screen();
    StaticJsonBuffer<500> jsonBuffer;
    request.path = "/parts/get_part_info";
    request.body = "{\"id\":\""+String(item_id)+"\"}";
    http.post(request,response, headers);
    response.body.toCharArray(char_buf,500);
    JsonObject& root = jsonBuffer.parseObject(char_buf);
    int id = root["id"];
    const char* name = root["name"];
    int quantity = root["quantity"];
    const char* desc = root["description"];
    tft.print("Name: "); tft.println(name);tft.println();
    tft.print("Item id: "); tft.println(id);tft.println();
    tft.print("Quantity: "); tft.println(quantity);tft.println();
    tft.println("Description: ");tft.println(); tft.println(desc);tft.println();
    print_divider();
    tft.setCursor(0,120);
    tft.print("Back     Select");
    //delay(1000);
    while(1){
        currtouched = cap.touched();
        if((currtouched & _BV(LEFT))){
            state = DISPLAY_BIN;
            return;
        }
        else if((currtouched & _BV(CENTER))){
            state = CHANGE_QUANTITY;
            part_qty = quantity;
            return;
        }
        Particle.process();
    }
}

int display_parts(){
    int current_row = 0;
    static char new_char_buf[600];
    StaticJsonBuffer<1000> jsonBuffer;
    request.path = "/parts/get_parts_from_bin";
    request.body = "{\"bin_id\":\""+String(bin_id)+"\"}";
    http.post(request,response, headers);
    response.body.toCharArray(new_char_buf,600);
    Serial.println(new_char_buf);
    JsonObject& root = jsonBuffer.parseObject(const_cast<char*> (response.body.c_str()));
    reset_screen();
    if(!root.success()) Serial.println("couldnt parse");
    int entries = root["entries"];
    Serial.println(entries);
    while(1){
        reset_screen();
        Particle.process();
        //Serial.println(entry_num);
        int entry_num = entries;
        for(int row=0;row<entry_num;row++){
            int id = root["rows"][row]["id"];
            const char* name = root["rows"][row]["name"];
            Serial.println(name);
            if(current_row==row){
                tft.print("> ");
            }
            tft.println(name);
            //print_divider();
        }
        tft.setCursor(0,120);
        tft.print("Up    Select    Down");
        while(1){
            Particle.process();
            currtouched = cap.touched();
            if((currtouched & _BV(LEFT))){
                if(current_row>0){
                current_row--;
                break;
                }
            }
            else if((currtouched & _BV(RIGHT))){
                if(current_row<entry_num-1){
                current_row++;
                break;
                }
            }
            else if((currtouched & _BV(CENTER))){
                int id = root["rows"][current_row]["id"];
                state = DISPLAY_PART;
                return id;
            }
        }
    }
}

void select_bin(){
    StaticJsonBuffer<300> jsonBuffer;
    request.path = "/parts/get_bin_info";
    String rfid = current_card.id_string;
    request.body = "{\"rfid\":\""+rfid+"\"}";
    tft.println(request.body);
    http.post(request,response, headers);
    tft.println(response.body);
    response.body.toCharArray(char_buf,500);
    JsonObject& root = jsonBuffer.parseObject(char_buf);
    reset_screen();
    Serial.println(response.body);
    tft.print("Bin ID: ");
    int id = root["id"];
    bin_id = id;
    tft.println(id);
    tft.print("Bin RFID: ");
    tft.println(rfid);
    tft.println("Bin description: ");
    const char* desc = root["description"];
    tft.println(desc);
    print_divider();
    tft.setCursor(0,100);
    tft.println("Use this bin?");
    tft.println("Yes                No");
    int choice = 0;
    while(!choice){
        currtouched = cap.touched();
        Particle.process();
        if((currtouched & _BV(LEFT))){
            state = DISPLAY_BIN;
            return;
        }
        else if((currtouched & _BV(RIGHT))){
            state = SEARCH_FOR_CARD;
            return;
        }
        lasttouched = currtouched;
    }
}

void testdrawtext(char *text, uint16_t color) {
  tft.setCursor(0,0);
  tft.setTextColor(color);
  tft.print(text);
}

void print_divider(){
    tft.setTextSize(1);
    tft.println("----------");
}

void reset_screen(){
    tft.fillScreen(BLACK);
    tft.setCursor(0,0);
    tft.setTextColor(WHITE);
    tft.setTextSize(1);
}

void wait_for_card(){
    while(1){
        Particle.process();
        if (mfrc522.PICC_IsNewCardPresent()) {
            reset_screen();
            tft.setTextSize(2);
            tft.println("NFC tag found!");
            while(1){
                if (mfrc522.PICC_ReadCardSerial()) {
	            Serial.print("Card UID:");
	            current_card.size = mfrc522.uid.size;
	            for (byte i = 0; i < mfrc522.uid.size; i++) {
		            Serial.print(mfrc522.uid.uidByte[i] < 0x10 ? " 0" : " ");
		            Serial.print(mfrc522.uid.uidByte[i], HEX);
		            current_card.id_string += mfrc522.uid.uidByte[i] < 0x10 ? " 0" : " ";
		            current_card.id_string += String(mfrc522.uid.uidByte[i], HEX);
		            current_card.id_digits[i] = mfrc522.uid.uidByte[i];
	            } 
	            current_card.id_string.trim();
	            Serial.println();
	            delay(1000);
	            return;
	            }
            }
	    }
    }
}

String get_bin_info(String rfid){
  request.path = "/parts/get_bin_info";
  request.body = "{\"rfid\":\""+rfid+"\"}";
  http.post(request,response, headers);
  //tft.print(response.body);
  //delay(2000);
  return String(response.body);
}

const mysql = require('mysql');
var express = require('express');
var parser = require('body-parser');
const cors = require('cors');

var app = express();

app.use(parser.json({extended: true}));
app.use(cors());
app.options('*',cors());

var con = mysql.createPool({
    host: "127.0.0.1",
    user: "username",
    password: "password",
    database: "components"
});

app.post("/parts/get_part_info", function(request, response){
    console.log(request.body);
    con.getConnection(function(err,connection){
        if(err) throw err;
        var query = `SELECT * FROM parts WHERE id = '${request.body.id}'`;
        connection.query(query,function(err, result){
            connection.release();
            if (err) throw err;
            console.log(result[0]);
            response.send(result[0]);
        });
    });
});

app.post("/parts/get_parts_from_bin", function(request, response){
    console.log(request.body);
    con.getConnection(function(err,connection){
        if(err) throw err;
        var query = `SELECT id, name FROM parts WHERE belongs_to = '${request.body.bin_id}'`;
        if(request.body.detailed !== undefined){
            query = `SELECT * FROM parts WHERE belongs_to = '${request.body.bin_id}'`;
        }
        connection.query(query,function(err, result){
            connection.release();
            if (err) throw err;
            res_json = {rows:result,entries:result.length};
            console.log(res_json);
            response.send(res_json);
        });
    });
});

app.post("/parts/get_bin_info", function(request, response){
    console.log(request.body);
    con.getConnection(function(err,connection){
        if(err) throw err;
        var query = `SELECT * FROM bins WHERE rfid = '${request.body.rfid}'`;
        console.log(query);
        connection.query(query,function(err, result){
            connection.release();
            if (err) throw err;
            console.log(result[0]);
            response.send(result[0]);
        });
    });
});

app.get("/parts/get_bins", function(request, response){
    con.getConnection(function(err,connection){
        if(err) throw err;
        var query = "SELECT * FROM bins";
        connection.query(query, function(err, result){
            connection.release();
            if(err) throw err;
            //var response_json = {entries_amount:result.length,rows:result}
            //console.log(result);
            response.send(result);
        });
        
    });
});

app.get("/parts/get_parts", function(request, response){
    con.getConnection(function(err,connection){
        if(err) throw err;
        var query = "SELECT * FROM parts";
        connection.query(query, function(err, result){
            connection.release();
            if(err) throw err;
            //var response_json = {entries_amount:result.length,rows:result}
            //console.log(result);
            response.send(result);
        });
        
    });
});

app.post("/parts/add_part", function(request, response){
    con.getConnection(function(err,connection){
        //console.log(request.body);
        if(err) throw err;
        var body = request.body;
        var query = `INSERT INTO parts (name, value, quantity, description, package, \
            belongs_to, is_SMD) VALUES ('${body.name}','${body.value}', \
            '${body.quantity}','${body.description}','${body.package}','${body.belongs}', \
            '${body.is_SMD}')`;
        connection.query(query, function(err, result){
            connection.release();
            if(err) throw err;
        });
        //response.send_header('Access-Control-Allow-Origin', '*')
        response.send("ok");
    });
});

app.post("/parts/add_bin", function(request, response){
    con.getConnection(function(err,connection){
        //console.log(request.body);
        if(err) throw err;
        var body = request.body;
        var query = `INSERT INTO bins (name, rfid, description, type, is_SMD, \
            is_mixed) VALUES ('${body.name}','${body.rfid}', \
            '${body.description}','${body.type}','${body.is_smd}','${body.is_mixed}')`;
        connection.query(query, function(err, result){
            connection.release();
            if(err) throw err;
        });
        //response.send_header('Access-Control-Allow-Origin', '*')
        response.send("ok");
    });
});

app.post("/parts/remove_bin", function(request, response){
    con.getConnection(function(err,connection){
        //console.log(request.body);
        if(err) throw err;
        var body = request.body;
        var query = `DELETE FROM bins WHERE (id = '${body.bin_id}')`;
        connection.query(query, function(err, result){
            //connection.release();
            if(err) throw err;
        });
        var query = `DELETE FROM parts WHERE (belongs_to = '${body.bin_id}')`;
        connection.query(query, function(err, result){
            connection.release();
            if(err) throw err;
        });
        //response.send_header('Access-Control-Allow-Origin', '*')
        response.send("ok");
    });
});

app.post("/parts/remove_item", function(request, response){
    con.getConnection(function(err,connection){
        if(err) throw err;
        var body = request.body;
        var query = `DELETE FROM parts WHERE (id = '${body.item_id}')`;
        connection.query(query, function(err,result){
            connection.release();
            if(err) throw err;
        });
        response.send("ok");
    });
});

app.post("/parts/change_amount", function(request,response){
    console.log(request.body);
    var amount = request.body.amount;
    var inc_dec = request.body.inc_dec;
    var item_id = request.body.id;
    if(inc_dec === true){
        var query = `UPDATE parts SET quantity = quantity + ${amount} WHERE id = ${item_id} and quantity >= 0`;
    }
     else if(inc_dec === false){
        var query = `UPDATE parts SET quantity = quantity - ${amount} WHERE id = ${item_id} and quantity >= 0+${amount}`;
    }
    console.log(query);
    con.getConnection(function(err,connection){
        if(err) throw err;
        connection.query(query,function(err, result){
            connection.release();
            if (err) throw err;
            console.log(`Successfully changed ${result.changedRows} entries`);
            response.send(`Successfully changed ${result.changedRows} entries`);
        });
    });
});

app.listen(3010);

<html>
<head>
    <title>Part Tracker</title>
    <link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css">
    <script type="text/javascript" src="https://cdn.jsdelivr.net/particle-api-js/5/particle.min.js"></script>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
    <script src="part_tracker_script.js"></script>
    <link rel="stylesheet" href="part_tracker_styles.css">
</head>
<body class="w3-content" style="width: 100%">
    <header class="w3-container w3-blue-gray w3-center" style="margin-bottom: 50px; width: 100%;">
        <h1>Part Tracker Main Page</h1>
    </header>
    <div id="action_container" class="w3-display-container w3-white" style="height:100%;">
        <div id="actions" class="w3-display-top w3-center">
            <div class="w3-row-padding" style="margin-bottom: 50px;">
                <div class="w3-col s4 item" id="add_new_bulk_item">
                    <button class="modal_button w3-button w3-white w3-border w3-round"
                    onclick="document.getElementById('add_bulk_item').style.display='block';">
                        <p>Add new items in bulk</p>
                    </button>
                </div>
                <div class="w3-col s4 item" id="list_bins">
                    <button class="modal_button w3-button w3-white w3-border w3-round"
                    onclick="document.getElementById('list_all_bins').style.display='block';refresh_selects();">
                        <p>List bins</p>
                    </button>
                </div>
                <div class="w3-col s4 item" id="list_items">
                    <button class="modal_button w3-button w3-white w3-border w3-round"
                    onclick="document.getElementById('list_all_items').style.display='block';">
                        <p>List items</p>
                    </button>
                </div>
            </div>
            <div class="w3-row-padding" style="margin-bottom: 50px;">
                    <div class="w3-col s4 item" id="add_new_item">
                        <button class="modal_button w3-button w3-white w3-border w3-round"
                        onclick="document.getElementById('add_one_item').style.display='block';">
                            <p>Add a new item</p>
                        </button>
                    </div>
                    <div class="w3-col s4 item" id="remove_item_button">
                        <button class="modal_button w3-button w3-white w3-border w3-round"
                        onclick="document.getElementById('remove_item').style.display='block';">
                            <p>Remove item</p>
                        </button>
                    </div>
                    <div class="w3-col s4 item" id="list_items_btn">
                        <button class="modal_button w3-button w3-white w3-border w3-round"
                        onclick="document.getElementById('list_items_from_bin').style.display='block';">
                            <p>List items in a bin</p>
                        </button>
                    </div>
            </div>
            <div class="w3-row-padding" style="margin-bottom: 50px;">
                    <div class="w3-col s4 item" id="change_qty_btn">
                        <button class="modal_button w3-button w3-white w3-border w3-round"
                        onclick="document.getElementById('change_qty').style.display='block';">
                            <p>Change item quantity</p>
                        </button>
                    </div>
                    <div class="w3-col s4 item" id="add_bin_btn">
                        <button class="modal_button w3-button w3-white w3-border w3-round"
                        onclick="document.getElementById('add_bin').style.display='block';">
                            <p>Add bin</p>
                        </button>
                    </div>
                    <div class="w3-col s4 item" id="remove_bin_btn">
                        <button class="modal_button w3-button w3-white w3-border w3-round"
                        onclick="document.getElementById('remove_bin').style.display='block';">
                            <p>Remove bin</p>
                        </button>
                    </div>
            </div>
            <div class="w3-row-padding" style="margin-bottom: 25px;">
                <div class="w3-col s4 item w3-center" id="refresh_bins">
                    <button class="w3-button w3-white w3-border w3-round">
                        <p>Refresh bins</p>
                    </button>
                </div> 
            </div>
        </div>

    </div>

<div id="list_all_items" class="w3-modal w3-card-4" style="width:100%;">
    <div class="w3-modal-content w3-animate-top">
        <header class="w3-container w3-blue-gray w3-center" style="margin-bottom: 25px;">
            <h2>View all parts</h2>
        </header>
        <div class="w3-container" style="margin: auto; width: 100%;">
            <span onclick="document.getElementById('list_all_items').style.display='none'" class="w3-button w3-display-topright">&times;</span>
            <table class="w3-table-all w3-hoverable" id="all_part_table">
                <tr class='w3-blue-gray'>
                    <th>Name</th>
                    <th>ID</th>
                    <th>Value</th>
                    <th>Quantity</th>
                    <th>Description</th>
                    <th>Package</th>
                    <th>Bin ID</th>
                    <th>Is SMD?</th>
                </tr>
            </table>
        </div>
    </div>
</div>
<div id="list_all_bins" class="w3-modal w3-card-4" style="width:100%;">
    <div class="w3-modal-content w3-animate-top">
        <header class="w3-container w3-blue-gray w3-center" style="margin-bottom: 25px;">
            <h2>View all bins</h2>
        </header>
        <div class="w3-container" style="margin: auto; width: 100%;">
            <span onclick="document.getElementById('list_all_bins').style.display='none'" class="w3-button w3-display-topright">&times;</span>
            <table class="w3-table-all w3-hoverable" id="all_bin_table">
                <tr class='w3-blue-gray'>
                    <th>Name</th>
                    <th>ID</th>
                    <th>RFID UID</th>
                    <th>Type</th>
                    <th>Description</th>
                    <th>Is SMD?</th>
                    <th>Is mixed?</th>
                </tr>
            </table>
        </div>
    </div>
</div>
<div id="list_items_from_bin" class="w3-modal w3-card-4" style="width:100%;">
    <div class="w3-modal-content w3-animate-top">
        <header class="w3-container w3-blue-gray w3-center" style="margin-bottom: 25px;">
            <h2>View parts from bin</h2>
        </header>
        <div class="w3-container" style="margin: auto; width: 100%;">
            <span onclick="document.getElementById('list_items_from_bin').style.display='none'" class="w3-button w3-display-topright">&times;</span>
            <select class="w3-select bin_select" onchange="populate_bin_item_table();" name="bins" id="list_item_bin_select" style="margin-bottom: 25px;">

            </select>
            <table class="w3-table-all w3-hoverable" id="item_from_bin_table" style="display: none; width: 100%; margin-bottom:40px;">
            </table>
            <label style='display: none; margin-bottom: 28px; font-size: 40px;' class="w3-center" id="no_data_label">No data found!</label>
        </div>
    </div>
</div>
<div id="remove_item" class="w3-modal w3-card-4" style="width:100%;">
    <div class="w3-modal-content w3-animate-top">
        <header class="w3-container w3-blue-gray w3-center" style="margin-bottom: 25px;">
            <h2>Remove an item</h2>
        </header>
        <div class="w3-container" style="margin: auto; width: 100%;">
            <span onclick="document.getElementById('remove_item').style.display='none'; reset_remove_item();" class="w3-button w3-display-topright">&times;</span>
            <div class="w3-container" style="margin: auto; width: 100%;">
                <form id="remove_item_form" class="w3-container"
                onsubmit="remove_item('remove_item_item_select'); reset_remove_item(); return false">
                <label>Select a bin:</label>
                <select class="w3-select bin_select" name="bins" id="remove_item_select" onchange="load_items_from_bin('remove_item_select','remove_item_item_select'); return false">
                </select>
                <label style="margin-top: 40px; display: none;" id="remove_item_item_label">Select an item to remove:</label>
                <select class="w3-select" name="items_sel" id="remove_item_item_select" style="display: none;">
                </select>
                <div class="w3-row-padding" style="margin-top: 30px;">
                    <div class="w3-center">
                        <input type="submit" value="Remove item" 
                        class="w3-button w3-white w3-border">
                    </div>
                </div>
                </form>
            </div>
        </div>
    </div>
</div>
<div id="change_qty" class="w3-modal w3-card-4" style="width:100%;">
    <div class="w3-modal-content w3-animate-top">
        <header class="w3-container w3-blue-gray w3-center" style="margin-bottom: 25px;">
            <h2>Change the quantity of an item</h2>
        </header>
        <div class="w3-container" style="margin: auto; width: 100%;">
            <span onclick="document.getElementById('change_qty').style.display='none';" class="w3-button w3-display-topright">&times;</span>
            <div class="w3-container" style="margin: auto; width: 100%;">
                <form id="change_item_form" class="w3-container"
                onsubmit="change_qty('change_qty_item_select',this.num.value); reset_change_item(); return false">
                <label>Select a bin:</label>
                <select class="w3-select bin_select" name="bins" id="change_qty_bin_select" onchange="load_items_from_bin('change_qty_bin_select','change_qty_item_select'); return false">
                </select>
                <label style="margin-top: 40px; display: none;" id="change_qty_label">Select an item to change:</label>
                <select class="w3-select" name="items_sel" id="change_qty_item_select" style="display: none;">
                </select>
                <div style="width: 25%;" class="w3-center">
                <label style="margin-top: 40px;">Change item quantity by </label>
                <input type="number" class="w3-input" name="num" value="0">
                </div>
                <div class="w3-row-padding" style="margin-top: 30px;">
                    <div class="w3-center">
                        <input type="submit" value="Change item" 
                        class="w3-button w3-white w3-border">
                    </div>
                </div>
                </form>
            </div>
        </div>
    </div>
</div>
<div id="add_bin" class="w3-modal w3-card-4" style="width:100%;">
    <div class="w3-modal-content w3-animate-top">
        <header class="w3-container w3-blue-gray w3-center" style="margin-bottom: 25px;">
            <h2>Add a new bin</h2>
        </header>
        <div class="w3-container" style="margin: auto; width: 100%;">
            <span onclick="document.getElementById('add_bin').style.display='none'" class="w3-button w3-display-topright">&times;</span>
            <div class="w3-container" style="margin: auto; width: 100%;">
                <form id="add_new_bin_form" class="w3-container"
                onsubmit="add_bin(this.bin_name.value,this.rfid.value,
                this.desc.value,this.type.value,this.is_smd.value,
                this.is_mixed.value); return false">
                    <label>New bin name</label>
                    <input type="text" class="w3-input" name="bin_name" style="margin-bottom: 20px;" required>
                    <label>RFID UID (Leave blank if none.)</label>
                    <input type="text" value="" class="w3-input" name="rfid" style="margin-bottom: 20px;">
                    <label>Description</label>
                    <input type="text" class="w3-input" name="desc" style="margin-bottom: 20px;" required>
                    <label>Bin type (RES, CAP, etc.)</label>
                    <input type="text" class="w3-input" name="type" style="margin-bottom: 20px;" required>
                    <label>Is SMD?</label>
                    <input type="radio" name="is_smd" class="w3-radio" value="1">
                    <label>Yes</label>
                    <input type="radio" name="is_smd" class="w3-radio" value="0" checked>
                    <label>No</label>
                    </br>
                    <label>Is it mixed? (SMD and non-SMD)</label>
                    <input type="radio" name="is_mixed" class="w3-radio" value="1">
                    <label>Yes</label>
                    <input type="radio" name="is_mixed" class="w3-radio" value="0" checked>
                    <label>No</label>
                    </br>
                    <div class="w3-row-padding w3-center" style="margin-top: 20px;">
                        <div class="w3-half">
                            <input type="submit" value="Create bin" 
                            class="w3-button w3-white w3-border">
                        </div>
                        <div class="w3-half">
                            <input type="reset" value="Reset values" class="w3-button w3-white w3-border">
                        </div>
                    </div>
                </form>
            </div>
        </div>
    </div>
</div>
<div id="remove_bin" class="w3-modal w3-card-4" style="width:100%;">
    <div class="w3-modal-content w3-animate-top">
        <header class="w3-container w3-blue-gray w3-center" style="margin-bottom: 25px;">
            <h2>Remove a bin (Dangerous!)</h2>
        </header>
        <div class="w3-container" style="margin: auto; width: 100%;">
            <span onclick="document.getElementById('remove_bin').style.display='none'" class="w3-button w3-display-topright">&times;</span>
            <div id="remove_bin_container" class="w3-display-container w3-white">
                <form id="remove_bin_form" class="w3-container"
                onsubmit="remove_bin(); return false">
                    <label>Bin:</label>
                    <select id="bin_remove_sel" name="bin_name" class="w3-select bin_select">
                    </select>
                    </br>
                    <div class="w3-panel w3-red w3-display-container">
                        <span onclick="this.parentElement.style.display='none'"
                        class="w3-button w3-large w3-display-topright">&times;</span>
                        <h3>Danger!</h3>
                        <p>Removing a bin also removes all associated parts!</p>
                    </div>
                    <div class="w3-row-padding" style="margin-top: 30px;">
                        <div class="w3-center">
                            <input type="submit" value="Remove bin" 
                            class="w3-button w3-white w3-border">
                        </div>
                    </div>
                    
                </form>
            </div>
        </div>
    </div>
</div>

<div id="add_bulk_item" class="w3-modal w3-card-4" style="width:100%;">
    <div class="w3-modal-content w3-animate-top">
        <header class="w3-container w3-blue-gray w3-center" style="margin-bottom: 50px;">
            <h2>Add new items in bulk</h2>
        </header>
        <div class="w3-container" style="margin: auto; width: 400px;">
            <span onclick="document.getElementById('add_bulk_item').style.display='none'" class="w3-button w3-display-topright">&times;</span>
            <div id="add_new_set" class="w3-display-container w3-white">
                <form id="add_bulk_form" class="w3-container"
                onsubmit="create_parts('bin_select',this.value_str.value,this.pkg.value,
                this.is_smd.value,this.qty.value); return false">
                    <label>Bin:</label>
                    <select id="bin_select" name="bin_name" class="w3-select bin_select">
                    </select>
                    <label>Enter values separated by a comma:</label>
                    <textarea required id="values" name="value_str" rows="10",cols="40" value=""></textarea><br>
                    <label>Starting quantity of each part</label>
                    <input required type="number" class="w3-input" name="qty" value="0" min="0">
                    <label>Package (Leave blank if none.)</label>
                    <input type="text" name="pkg" class="w3-input"></br>
                    <label>Is this set of parts SMD?</label>
                    <input type="radio" name="is_smd" class="w3-radio" value="1">
                    <label>Yes</label>
                    <input type="radio" name="is_smd" class="w3-radio" value="0" checked>
                    <label>No</label>
                    </br>
                    <div class="w3-row-padding">
                        <div class="w3-half">
                            <input type="submit" value="Create parts" 
                            class="w3-button w3-white w3-border">
                        </div>
                        <div class="w3-half">
                            <input type="reset" value="Reset values" class="w3-button w3-white w3-border">
                        </div>
                    </div>
                    
                </form>
            </div>
        </div>
    </div>
</div>
<div id="add_one_item" class="w3-modal w3-card-4" style="width:100%;">
        <div class="w3-modal-content w3-animate-top">
            <header class="w3-container w3-blue-gray w3-center" style="margin-bottom: 50px;">
                <h2>Add a new item</h2>
            </header>
            <div class="w3-container" style="margin: auto; width: 400px;">
                <span onclick="document.getElementById('add_one_item').style.display='none'" class="w3-button w3-display-topright">&times;</span>
                <div id="add_new_set" class="w3-display-container w3-white">
                    <form id="add_one_form" class="w3-container"
                    onsubmit="create_one_part('bin_add_one_select',this.item_name.value,this.qty.value,
                    this.is_smd.value,this.pkg.value,this.val.value,this.desc.value); return false">
                        <label>Bin:</label>
                        <select id="bin_add_one_select" name="bin_name" class="w3-select bin_select">
                        </select>
                        <label>Name of new item</label>
                        <input type="text" class="w3-input" name="item_name" required>
                        <label>Starting quantity of part</label>
                        <input type="number" class="w3-input" name="qty" value="0" min="0" required>
                        <label>Package (Leave blank if none.)</label>
                        <input type="text" name="pkg" class="w3-input"></br>
                        <label>Value (Leave blank if none.)</label>
                        <input type="text" name="val" class="w3-input"></br>
                        <label>Item Description</label>
                        <input type="text" name="desc" class="w3-input" required></br>
                        <label>Is this part SMD?</label>
                        <input type="radio" name="is_smd" class="w3-radio" value="1">
                        <label>Yes</label>
                        <input type="radio" name="is_smd" class="w3-radio" value="0" checked>
                        <label>No</label>
                        </br>
                        <div class="w3-row-padding">
                            <div class="w3-half">
                                <input type="submit" value="Create part" 
                                class="w3-button w3-white w3-border">
                            </div>
                            <div class="w3-half">
                                <input type="reset" value="Reset values" class="w3-button w3-white w3-border">
                            </div>
                        </div>
                        
                    </form>
                </div>
            </div>
        </div>
</div>
</body>
</html>

const ip = "ip address here"; //Make sure to change this

function create_parts(bin_sel, value_str, pkg,is_smd,qty){
    console.log(bin_sel);
    var sel_elem = document.getElementById(bin_sel);
    var bin_str = sel_elem.options[sel_elem.selectedIndex].value;
    var bin_parsed = JSON.parse(bin_str);
    var id = bin_parsed.id;
    var type = bin_parsed.type;
    var val_array = value_str.split(",");
    console.log(val_array);
    val_array.forEach(element => {
        console.log(element);
        var smd_modify = "";
        if(is_smd==="1"){
            smd_modify = "SMD"
        }
        var modifier = `${pkg} ${smd_modify}`;
        var dataString = {
            name: `${modifier} ${element} ${type}`,
            value: `${element}`,
            quantity: qty,
            description: `${modifier} ${element} ${type}`,
            package: pkg,
            belongs: `${id}`,
            is_SMD: is_smd
        };
        console.log(JSON.stringify(dataString));
        $.ajax({
        method: "POST",
        url: "http://"+ip+":3010/parts/add_part",
        contentType: "application/json; charset=utf-8",
        //dataType: "json",
        data: JSON.stringify(dataString),
        success: function(response){
            console.log("Success");
            document.getElementById("add_bulk_form").reset();
            document.getElementById('add_bulk_item').style.display='none';
        }
        });
    });
}

window.onload=function(){
    refresh_selects();
}

function refresh_selects(){
    $.ajax({
        method: "GET",
        url: "http://"+ip+":3010/parts/get_bins",
        dataType: 'json',
        success: function(response){
            populate_select(response);
        }
    });
}

function populate_select(data){
    var sels = document.getElementsByClassName("bin_select");
    var sel;
    //console.log(sels);
    for(var j=0;j<sels.length;j++){
        sel = sels[j];
        sel.innerHTML = "";
    sel.options.lenth = 0;
    sel.options.add(new Option("Select a bin", "0"));
    sel.options[0].disabled = true;
    for(var i=0; i<data.length;i++){
        var d = data[i];
        var val = JSON.stringify({id: d.id, type: d.type});
        sel.options.add(new Option(d.name, val));
    }
}
}

function populate_bin_table(data){
    //var parsed_bins = JSON.parse(data);
    for(var i=0;i<data.length;i++){
        var row = data[i];
        var rfid = row.rfid;
        if(row.rfid===null){
            rfid="N/A";
        }
        var row_str = `<tr><td>${row.name}</td>
        <td>${row.id}</td><td>${rfid}</td>
        <td>${row.type}</td><td>${row.description}</td>
        <td>${row.is_SMD}</td><td>${row.is_mixed}</td></tr>`;
        //console.log(row_str);
        $('#all_bin_table').append(row_str);
    }
}

function populate_item_table(table_id, data){
    console.log(data);
    for(var i=0;i<data.length;i++){
        var row = data[i];
        var smd = "No";
        var pkg = "N/A";
        var val = row.value;
        //console.log(val);
        if(row.is_SMD==1){
            smd = "Yes";
        }
        if(row.package!==""){
            pkg = row.package;
        }
        if(row.value===null){
            val = "N/A";
        }
        var row_str = `<tr><td>${row.name}</td>
        <td>${row.id}</td><td>${val}</td>
        <td>${row.quantity}</td><td>${row.description}</td>
        <td>${pkg}</td><td>${row.belongs_to}</td>
        <td>${smd}</td></tr>`;
        //console.log(row_str);
        $(`#${table_id}`).append(row_str);
    }
}

$(document).ready(function(){
    $('#list_bins').click(function(){
        document.getElementById("all_bin_table").innerHTML = "<tr class='w3-blue-gray'> \
        <th>Name</th>\
        <th>ID</th>\
        <th style='width: 100px;'>RFID UID</th>\
        <th>Type</th>\
        <th>Description</th>\
        <th>Is SMD?</th>\
        <th>Is mixed?</th>\
        </tr>";
        $.ajax({
            method: "GET",
            url: "http://"+ip+":3010/parts/get_bins",
            dataType: 'json',
            success: function(response){
                populate_bin_table(response);
            }
        });
    });
    $('#list_items').click(function(){
        document.getElementById("all_part_table").innerHTML = "<tr class='w3-blue-gray'> \
        <th>Name</th>\
        <th>ID</th>\
        <th>Value</th>\
        <th>Quantity</th>\
        <th>Description</th>\
        <th>Package</th>\
        <th>Bin ID</th>\
        <th>Is SMD</th>\
        </tr>";
        $.ajax({
            method: "GET",
            url: "http://"+ip+":3010/parts/get_parts",
            dataType: 'json',
            success: function(response){
                populate_item_table('all_part_table',response);
            }
        });
    });
    $('#refresh_bins').click(function(){
        refresh_selects();
    });
});

function populate_bin_item_table(){
    document.getElementById("item_from_bin_table").innerHTML = 
    "<tr class='w3-blue-gray' style='width: 100%;'> \
    <th>Name</th>\
    <th>ID</th>\
    <th>Value</th>\
    <th>Quantity</th>\
    <th>Description</th>\
    <th>Package</th>\
    <th>Bin ID</th>\
    <th>Is SMD</th>\
    </tr>";
    var sel_elem = document.getElementById("list_item_bin_select");
    var bin_str = sel_elem.options[sel_elem.selectedIndex].value;
    var bin_parsed = JSON.parse(bin_str);
    var id = bin_parsed.id;
    //console.log(id);
    $.ajax({
        method: "POST",
        url: "http://"+ip+":3010/parts/get_parts_from_bin",
        contentType: "application/json; charset=utf-8",
        data: JSON.stringify({bin_id: id, detailed: true}),
        dataType: 'json',
        async: false,
        success: function(response){
            //console.log(response);
            if(response.rows.length>0){
                document.getElementById("no_data_label").style.display = 'none';
                populate_item_table('item_from_bin_table',response.rows);
                document.getElementById("item_from_bin_table").style.width = '100%';
                document.getElementById("item_from_bin_table").style.display = 'block';
            }
            else{
                document.getElementById("item_from_bin_table").style.display = 'none';
                document.getElementById("no_data_label").style.display = 'block';
            }
        }
    });
}

function create_one_part(bin_id_name,name,qty,is_smd,package,val,desc){
    var sel_elem = document.getElementById("bin_add_one_select");
    var bin_str = sel_elem.options[sel_elem.selectedIndex].value;
    var bin_parsed = JSON.parse(bin_str);
    var id = bin_parsed.id;
    console.log(id);
    var dataString = {
        name: `${name}`,
        value: `${val}`,
        quantity: qty,
        description: `${desc}`,
        package: package,
        belongs: `${id}`,
        is_SMD: is_smd
    };
    console.log(JSON.stringify(dataString));
    $.ajax({
    method: "POST",
    url: "http://"+ip+":3010/parts/add_part",
    contentType: "application/json; charset=utf-8",
    //dataType: "json",
    data: JSON.stringify(dataString),
    success: function(response){
        console.log("Success");
        document.getElementById("add_one_form").reset();
        document.getElementById('add_one_item').style.display='none';
    }
    });
}

function remove_bin(){
    var sel_elem = document.getElementById("bin_remove_sel");
    var bin_str = sel_elem.options[sel_elem.selectedIndex].value;
    var bin_parsed = JSON.parse(bin_str);
    var id = bin_parsed.id;
    console.log(id);
    $.ajax({
        method: "POST",
        url: "http://"+ip+":3010/parts/remove_bin",
        contentType: "application/json; charset=utf-8",
        //dataType: "json",
        data: JSON.stringify({bin_id: id}),
        success: function(response){
            console.log("Success");
            document.getElementById("remove_bin_form").reset();
            document.getElementById('remove_bin').style.display='none';
            refresh_selects();
        }
    });
}

function load_items_from_bin(bin_sel_id,item_sel_id){
    var bin_sel = document.getElementById(bin_sel_id);
    var bin_str = bin_sel.options[bin_sel.selectedIndex].value;
    var bin_parsed = JSON.parse(bin_str);
    var id = bin_parsed.id;
    var item_sel = document.getElementById(item_sel_id);
    console.log(id);
    console.log("Loading items from ", item_sel_id);
    $.ajax({
        method: "POST",
        url: "http://"+ip+":3010/parts/get_parts_from_bin",
        contentType: "application/json; charset=utf-8",
        data: JSON.stringify({bin_id: id}),
        dataType: "json",
        success: function(response){
            console.log("Success");
            //console.log(response);
            item_sel.innerHTML = "";
            item_sel.options.length = 0;
            item_sel.options.add(new Option("Select an item","0"));
            item_sel.options[0].disabled = true;
            response.rows.forEach(function(item){
                item_sel.options.add(new Option(item.name,item.id));
            });
            document.getElementById("remove_item_item_label").style.display = 'block';
            item_sel.style.display = 'block';
        }
    });
}

function remove_item(item_sel_id){
    var item_sel = document.getElementById(item_sel_id);
    var id = item_sel.options[item_sel.selectedIndex].value;
    $.ajax({
        method: "POST",
        url: "http://"+ip+":3010/parts/remove_item",
        contentType: "application/json; charset=utf-8",
        data: JSON.stringify({item_id: id}),
        dataType: "json",
        async: false,
        success: function(response){
            console.log("Success");
        }
    });
}

function change_qty(item_sel_id, amt){
    var item_sel = document.getElementById(item_sel_id);
    var id = item_sel.options[item_sel.selectedIndex].value;
    var inc_dec_str = true;
    if(amt<0){
        inc_dec_str = false;
    }
    $.ajax({
        method: "POST",
        url: "http://"+ip+":3010/parts/change_amount",
        contentType: "application/json; charset=utf-8",
        data: JSON.stringify({id: id, inc_dec: inc_dec_str,amount: Math.abs(amt)}),
        dataType: "json",
        async: false,
        success: function(response){
            console.log("Success");
        }
    });
}

function reset_remove_item(){
    load_items_from_bin("remove_item_select","remove_item_item_select");
    document.getElementById('remove_item_item_select').style.display = 'none';
    document.getElementById("remove_item_form").reset();
    document.getElementById('remove_item').style.display='none';
    refresh_selects();
}

function reset_change_item(){
    load_items_from_bin("change_qty_bin_select","change_qty_item_select");
    document.getElementById('change_qty_item_select').style.display = 'none';
    document.getElementById("change_item_form").reset();
    document.getElementById('change_qty').style.display='none';
    refresh_selects();
}

function add_bin(name,rfid,desc,type,is_smd,is_mixed){
    var dataString = {
        name: `${name}`,
        rfid: `${rfid}`,
        description: desc,
        type: `${type}`,
        is_smd: is_smd,
        is_mixed: `${is_mixed}`
    };
    $.ajax({
        method: "POST",
        url: "http://"+ip+":3010/parts/add_bin",
        contentType: "application/json; charset=utf-8",
        //dataType: "json",
        data: JSON.stringify(dataString),
        success: function(response){
            console.log("Success");
            document.getElementById("add_new_bin_form").reset();
            document.getElementById('add_bin').style.display='none';
            refresh_selects();
        }
    });
}

Credits

Evan Rust

122 projects • 1087 followers

IoT, web, and embedded systems enthusiast. Contact me for product reviews or custom project requests.

Comments

Awards

Most Creative - US Based Project

2019 China-US Young Maker Competition

Makerspace NFC Part Management System

Things used in this project

Hardware components

Software apps and online services

Hand tools and fabrication machines

Story

The Purpose

Demonstration Video

General Layout

Webserver

MySQL Database

NFC RFID Tags

Hardware

Code for hardware

Webpage

Usage

Future Plans

Custom parts and enclosures

Top Plate

Lower Portion

Schematics

Schematic

Code

Physical Reader

Node Js Webserver

Webpage HTML

Webpage Javascript

Webpage CSS

Credits

Evan Rust

Comments

Awards

Embed the widget on your own site

Makerspace NFC Part Management System

Makerspace NFC Part Management System

Things used in this project

Hardware components

Software apps and online services

Hand tools and fabrication machines

Story

The Purpose

Demonstration Video

General Layout

Webserver

MySQL Database

NFC RFID Tags

Hardware

Code for hardware

Webpage

Usage

Future Plans

Custom parts and enclosures

Top Plate

Lower Portion

Schematics

Schematic

Code

Physical Reader

Node Js Webserver

Webpage HTML

Webpage Javascript

Webpage CSS

Credits

Evan Rust

Comments

Awards

Related channels and tags