Using a Raspberry Pi for your own Inventory Management System

coreconduit,

Using a Raspberry Pi for your own inventory management system.

An open-source, inventory management system written in PHP with a MySQL database has no problem operating on a Raspberry Pi. Initially, you’ll only have local network access, but if you want to allow remote web access, you can.

In order to setup a web interface to access, view, and manage the content of your inventory management system you’ll need to set up a LAMP (Linux, Apache, MySQL, PHP) stack on your Raspberry Pi and configure it to work as a web server and set up a basic website which you can access on any device on the same network as your Pi. This is a link to a nice tutorial for setting-up a L.A.M.P. server on your Pi, the WordPress portion is optional, you can stop after installing PHP.

If you plan on having access to your Raspberry Pi through the Internet, you’ll need to configure your router and DDNS settings, or use a service such as dataplicity.io to wormhole to your pi through the web.

Install the Basic Inventory Management System Web Application

Included Features:

  • User Management: Groups, Basic Profile, and Change Password
  • Categories: For organizing products
  • Products: Basic product information, title, quantity, pricing.
  • Sales: Sales transactions are individual by product.
  • Reports: Daily, Monthly, and Ranged Dates Sales/Profit Report

InventoryProducts

After updating the Raspberry Pi and setting up the LAMP stack, installation of the inventory application is relatively painless.

If you haven’t done so, use apt-get to acquire and install the database software:

sudo apt-get install mariadb-server
sudo apt-get install php-mysql

When the installation is complete, run a simple security script that comes pre-installed with MariaDB which will remove some insecure default settings and lock down access to your database system. Start the interactive script by running:

sudo mysql_secure_installation

Download the source-code package and then you’ll need to extract the contents to the folder. Either rename the folder to the base name now or after you move it to the web root for Apache Web Server found at /var/www/html/

example:  sudo cp -R ~/Downloads/inventory-master /var/www/html/inventory

Use the MySQL/MariaDB command line to import the database schema.

Create a database named inventory and import the schema included in the project directory inventory.sql  If you haven’t done so, installation is as easy as:

sudo mysql -uroot

mysql> CREATE DATABASE inventory;
mysql> USE inventory;
mysql> source /var/www/html/inventory/inventory.sql

mysql> CREATE USER 'webuser'@localhost IDENTIFIED BY 'p1r4sp';
mysql> GRANT ALL PRIVILEGES ON inventory.* TO 'webuser'@localhost;
mysql> FLUSH PRIVILEGES;

Edit the /includes/config.php to match the username and password used to access your database.

The directory containing the project, especially, the uploads directory, must have write permissions on the system and let the web application be run using the www-data account by executing the following commands from the project directory:

sudo chmod -R 775 uploads/
sudo chown -R www-data:www-data *

Edit the header.php file lines #19 – #22 to suit the needs of your organization. e.g. change to logo
[project folder on server]/layouts/header.php
Same folder also contains the various menus used by the system.

Edit the CSS at line #85 and #102 to reflect the needs of your organization. e.g. background color
[project folder on server]/libs/css/main.css

InventoryAdmin

Using the Inventory Management System

  • Secure the default accounts with a change password.
  • Create a user account for each person using the system, including yourself.
    Optionally upload a photo for the user.
  • Add Categories – you’ll need to add at least one category before you can add products.
  • Add Media before you Add Product if you want to associate a photo when you Add Product. Otherwise, you can elect to have no image and updated later.
  • Add Order – before you try to Add Sales.

Improvements

These are the improvements I’ve added for my own system

  • Delete confirmation popup before delete actions
  • Description column for products
  • Location column for products
  • View products by category
  • Add sales from list of products – remove selected from list.
  • Add/Edit/Delete Sales also updates product’s quantity available
  • Order Management for all sales – All sales must be associated with an order number
  • View Sales by Order calculate total
  • Delete Order: deletes all sales associated with order AND restores quantity/stock
  • Add/Edit/Delete Order also updates product’s quantity available
  • Stock: Inventory Management for all products – Log of increase/decrease stock
  • Add/Edit/Delete Inventory also updates product’s quantity available

More Improvements

  • Stock Report containing current status of inventory by category
  • Currency Format Support allows for international currencies
  • Add/Edit/Delete Customer Details for relationship management
  • Search by Customer when adding new order
  • Sales Invoice by order
  • Inventory Stock Picklist by order
  • Add Sales by Product SKU number

Improved Version Source-Code

mmexport1472732631987

Published by coreconduit

http://www.coreconduit.com

69 Comments

  1. Hi there,
    thanks to your post I found this little gem of inventory mangement. I see you have done a lot improovements. Are you willing to share your edits? Best Chris

    Like

    Reply

  4. Good afternoon,
    I am very interested in getting this up and running, however I am stuck at a WordPress landing page after I have completed all the steps. Is there a checklist of where to start in my trouble shooting and audit of my work?

    It seems like I dont have any temlpates, but as I read through your guide it should have been included and defaulted in the files I downloaded.

    Thanks for reading!

    Like

    Reply

    1. Hi Florian,
      Thank you for taking the time to share the issue you are having, it’s likely other people have had the same experience. The WordPress installation has over written the default settings of the web server installation. If you try the URL to your inventory management system directory, for example: http://localhost/inventory/index.php and you are redirected to WordPress then you will want to check your apache web server settings found in the /etc/apache2/apache.conf
      Let me know how it goes!

      Like

      Reply

  5. Thank you for taking your time to respond .

    This is my first time but looking at the apache web server settings nothing seems to out of order . When try log in the site with localhost i get wordpress, if i try with ip adress//inventory/index.php i get a not found page .

    If i go on http://localhost/inventory/index.php i get 404

    Thank you !

    Like

    Reply

  6. Mine is setup like this /var/www/html/warehouse-inventory-system , i kept the files in warehouse-inventory-system where they are .

    Thank you

    Like

    Reply

  7. If i try to log in i get Database connection failed:Access denied for user ‘admin’@’localhost’ (using password: YES) where do i choose the default log in ? Should i just go and grand all permission to admin user ?
    Thank you

    Like

    Reply

    1. Check your MySql databases configuration. The access is denied because the default password is set to yes for user admin. Does user admin exist, if so is the password in the conf file inside the inventory management system directory set to that password.

      Like

      Reply

  9. Hi, couple of things i need help with/ask about?:
    – Is there a way to change currency to £?
    – Is there anyway of changing it so prices could be say £1.30 as at the moment it only allows me to input whole numbers?
    – When adding products to a sale is there a way as adding multiple items at once, as if i put in qty’s for severasl items then click add item on one of the items it only adds that one item even though ive put in quantities for several?

    Either way this is a very nice inventory management tool 🙂

    Like

    Reply

  10. Saw you updated some of the files… went to re-downloaded and deleted current folder contents and replaced with new download. Changed config file to my database password and now getting “Error on this Query :
    INSERT INTO log (user_id,remote_ip,action,date) VALUES (”,’::1′,’/’,’2019-11-21 21:51:03′)”… any ideas?

    Like

    Reply

      1. Hi I am getting the same error. I was able to edit the coinfigs.php file with the username and password.

        Like

      3. So I was able to install that missing library. but I am still getting the same error “Failed to select database”.

        https//:localhost takes me to the word press page but localhost/inventory/index.php produces the error.

        Again, Any help is appreciated. And thanks for the tutorial!

        Like

      4. “Failed to select database”
        This error message means that the database being referenced DOES NOT exist.
        Either it has not been created or the one being referenced is named differently than the one expected.

        Like

  12. Hello thanks for a great database application. I am having some problems with the side bar menu not loading the appropriate menu items such as the admin menu. when logging in as admin the admin.php file loads up as it should but the header.php file is not loading the admin side bar.

    Like

    Reply

      2. I have solved the issue. I had a logo image file that was sized “100 x 200” that caused an overlap of the header over the menus frame. Duh…. Thanks for the help really like the application.

        Like

  13. after login below error :
    Error on this Query :
    SHOW TABLES FROM projects_inventory_inventory-master LIKE “users”

    Like

    Reply

    1. The error message is telling you that the database: “projects_inventory_inventory-master” is invalid or doesn’t exist. Your database should be named “inventory”

      Like

      Reply

  14. Hi I tried installing the application locally & I ran into this error. Is there a way around it? I am able to login to the backend just fine.
    “Notice: Undefined index: user_id in C:\xampp\htdocs\inventory-master\includes\load.php on line 24”

    Like

    Reply

  15. The error still pops up sadly. However I’ve found that when I comment out this line. It no longer is there.
    $user_id = $_SESSION[‘user_id’];

    Like

    Reply

      1. It says…

        PHP Fatal error: Call to undefined function mysqli_connect( ) in /var/www/html/inventory/includes/database.php on line 18

        Like

  17. Make sure that you have installed the database connector:
    sudo apt-get install php-mysql

    and then make sure that you have setup the database:

    Create a database named inventory and import the schema included in the project directory inventory.sql If you haven’t done so, installation is as easy as:

    sudo mysql -uroot

    mysql> CREATE DATABASE inventory;
    mysql> USE inventory;
    mysql> source /var/www/html/inventory/inventory.sql

    mysql> CREATE USER ‘webuser’@localhost IDENTIFIED BY ‘p1r4sp’;
    mysql> GRANT ALL PRIVILEGES ON inventory.* TO ‘webuser’@localhost;
    mysql> FLUSH PRIVILEGES;

    Edit the /includes/config.php to match the username and password used to access your database.

    Like

    Reply

    2. I’m new to working with Linux and Raspberry Pi type computers, and have no experience at all with web stuff like this. Is it possible to make this remotely accessible, like being able to pull up and use the system, and could a bar code scanning function be added? If so, how would that work?

      Like

      Reply

  21. Hi, before I ask you one question I want to congrat you with the fabulous well done job.

    I don´t know if this management system can be used in Windows, but I installed in Xampp and have changed the login and is working, but I am having a problem when add products in the section name it gives me this error, and also in the description and the SKU and location.

    The error is Warning: Undefined variable $product in C:\xampp\htdocs\inventory-main\products\add_product.php on line 111Warning: Trying to access array offset on value of type null in C:\xampp\htdocs\inventory-main\products\add_product.php on line 111

    Can you say if this works in Xampp ?

    Awaiting answer.

    Like

    Reply

  22. Hi again, I have seen that you update a few minutes ago repairing my problem 😀 but if you try to add some product in the end it will give you this error

    Fatal error: Uncaught ArgumentCountError: Too few arguments to function logAction(), 1 passed in C:\xampp\htdocs\inventory-main\products\add_product.php on line 62 and exactly 3 expected in C:\xampp\htdocs\inventory-main\includes\sql.php:307 Stack trace: #0 C:\xampp\htdocs\inventory-main\products\add_product.php(62): logAction(‘success’) #1 {main} thrown in C:\xampp\htdocs\inventory-main\includes\sql.php on line 307

    But even with this error the product will be added successfully. Can you see what could it be causing the problem?

    Awaiting answer .

    Best regards 😉

    Like

    Reply

      1. You are awesome, fast response, thank you very very much 😉 I have been search a good management system for years and finally found it thanks to you 😉

        The system is really awesome , very simple and intuitive.

        Just for asking, will you do more improving on the management system?

        Best regards.

        Liked by 1 person

  25. Hello there!

    I recently followed your guide here and set up a inventory system on a Raspberry Pi 3B for my company (despite being a beginner with Linux and servers), but there’s a couple things I was wondering:

    1. How would I add barcode scanning capabilities to the system? What about using a phone camera to add pictures of products or scan codes?

    2. Would it be possible to make the system order things when stock gets too low? For example, the company I work for does a lot of wiring and wifi access point installation, so we are constantly having to remember to buy more wire, APs, switches, etc. Could the ordering be automated?

    Any reply is greatly appreciated,
    Ollie

    Like

    Reply

  26. I’ve almost got this running now, but when I try to log in I get this error: Error on this Query :
    SELECT id,username,password,user_level FROM users WHERE username =’admin’ LIMIT 1

    This happens with any user I try with.

    Like

    Reply

  27. Hi. This is an awesome work that I’d like to try on a live hosting, but after editing “config.php” I just get this error: Database connection failed:Can’t connect to MySQL server on ‘mywebaddress.xx’. I’d appreciate any help or input about it.

    Like

    Reply

  28. Hi. This is an awesome work that I’d like to try on a live hosting, but after editing “config.php” I just get this error: Database connection failed:Can’t connect to MySQL server on ‘mywebaddress.xx’. I’d appreciate any help or input about it.

    Like

    Reply

  29. Hello! I love everything about this inventory system. I have a question, though. I am running this database off a raspberry pi, wondering if there was a way to turn on an LED on a component shelf when the component is selected within the inventory management GUI using a conditional statement, and if so, how would this be possible? I really appreciate any help you can provide.

    Like

    Reply

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.