Automated Inventory Management

Project Report

Chao Li
Spring 2006

Abstract

With rapid growth of human-computer interaction, more and more useful software are replacing human efforts. The system we propose in this report integrates the idea to automated , instead of manually, manage inventory of a restaurant’s liquor, meanwhile it can generate sales report, inventory report, etc, which all require human efforts previously. As a result, this new system can reduce possible human errors and provide accurate information of inventory at any point.

Introduction

Nowadays, more and more companies tend to use any available software to maintain information over a long term. We design the Automated Inventory Management System to approach this goal with features that help improve data consistency, maintain necessary inventory level. With these goals in mind, we decide to incorporate design philosophy as well as user friendly interface into the system, meanwhile to have powerful functions that manifest all the users’ requirements and needs. One concern is how to reflect inventory level as quick as possible, since the sales of liquor occupy a large proportion compared with the daily transactions of the entire restaurant. Therefore, liquor’s inventory level changes constantly. In order to monitor these changes over periods, the Automated Inventory Management System can efficiently and accurately accomplish it while provides other services to the restaurant such as recording daily liquor sales. To users, the system can provide instant information about all inventory items. Therefore users who manage stock level can immediately solve any shortage problems, since the system provides easy-to-use interface for users to see inventory levels. By recording daily sales, the system updates inventory constantly with the help of database management system running as the back end. During the process of development, we are able to gather all the necessary requirements over small intervals. Consequently, more and more services are added to the system to provide better management solutions. By separating different user’s privilege, the system can assure different level of security to the inventory information. For example, manager’s privileges are added to the system when non-operation errors occur. After log in to the system with uniquely assigned password, manager will be able to modify all lists of orders which have been processed and stored. The system not only provides static services to restaurant, but initializes dynamic extensible inventory lists. Manager with advanced privileges can also modify the display to reflect newly added items. The accompanying screen shot demonstrate this convenient inventory management system.

[pic]
Fig. 1 The visual design of the AIM system

The previous inventory management method is done manually. Person who is in charge of inventory needs to spend a great deal of time each week to count inventory items. Obviously this method was prone to human errors. If an order was placed for certain item which actually had enough stock, redundant ordering became unnecessary. Furthermore, it became inefficient sometimes when the person needs to check it again to maintain certain levels for each item, especially after a busy weekend. Since AIM keeps track of every transaction, it provides concurrent information of each item’s number, while at the same time greatly improves efficiency of inventory management.

Technological Background

The Automated Inventory Management System is implemented with the latest Java technology utilizing extended swing library which makes layout easy to use and eliminate much of the tedious code to generate swing form.

6 Java 2 Platform, Standard Edition

Java is a so-called Object Oriented Programming language which every thing in it can be a class of its own type. OOP allows programmers to define any complex variable types to fit any programming requirements. Therefore, Java Platform, Standard Edition provides a complete environment for developing and deploying application running on desktops and servers, and can be executed on any operating system which has a Java Virtual Machine implementation. As part of the effort to enhance the development of Java UI, Sun Microsystems introduced the Java Foundation Classes. Swing is a powerful collection of UI components and an extension to the Lightweight UI framework that is included in JDK Version 1.1. Using Swing, programmers can create UI with pure Java code that contains a rich collection of UI elements.

7 MySQL and JDBC

Java programmers can write application in the Java programming language to access any database, using standard SQL statements, while still following Java language conventions. As part of the release of Java 2 in 1998, a second version of JDBC was issued as well.[1] MySQL is preferred database system nowadays since it is free, platform and independent, open source and fast. A typical 3-tier application is shown in Fig.2. [pic] Fig. 2 A three-tier application

System Architecture

The Automated Inventory Management System is a typical example of 3-tier application, which has a couple of advantages. It separates visual presentation from the business logic and the raw data. Therefore, it becomes possible to access the same data and the same business rules from multiple clients, such as a Java application.

9 Client Tier

The visual part is implemented using all kinds of Swing components, which does not make database calls. The main function of this tier is to display information to the users upon user’s request generated by user’s inputs such as firing button events. Meanwhile, this tier convert users’ request to in order to get information to be displayed. For example, inventory list will display when user clicks ‘display’ button if he or she wants to know current inventory list. Client tier also distinguish two kinds of users for AIM, regular and manager. The latter will have privileged rights such as to add inventory items, delete or edit order, etc.

10 Business Tier

The middle tier, business logic, is called by the client tier to make database queries. It provides core functionalities of the system as well as connectivity to the data tier, which simplify tasks that were done by client tier.

11 Data Tier

The database backend stores information which can be retrieved by using Java Database Connectivity. JDBC is used to manage the communication between the middle tier and the back-end database by issuing complex database queries.

Design and Implementing Details

13 GUI Design

The UI is designed to be easy-to-use and simple. The start page is composed of several Jpanels, including SalesPanel and ClockPanel, and a Jtabbedpane which includes other functional Jpanels such as inventoryPanel, orderSummaryPanel, etc. The SalesPanel contains information of inventory list and also allows users of AIM to input orders. The inventory list is a list of buttons and when users click ‘done’ button, summary of that order will display on the text field. Orders will not be executed until user click ‘order’ button, which stores the detail of the order into database. When the AIM system receives events of ordering from users, first thing it will check is to make sure there are enough stock level. If certain items exceed available amount, an error message will pop up to warn the user meanwhile that order will not proceed. In the SalesPanel, there are three other buttons, which are ‘manager’, ‘help’ and ‘logout’. ‘Manager’ button will allow manager to login in order to delete or edit orders. After log in, manager can also place order to those low stock level inventory. ‘Help’ button can help manager retrieve password by sending correct password to his cell phone. Another function that SalesPanel performs is when users click on any beer or wine, AIM will automatically check database to see whether the ordered item is insufficient or in low stock level. When any of the two situations happens, AIM will pop up a window to warn users to take action immediately. For example, when an item is insufficient, AIM will tell user that that item has fewer amounts and will cancel that item. When an item’s stocking level is below twenty, AIM will ask user whether to send email to vendor to order that item. However, this requires manager to login in order to send email to vendors. The ClockPanel is an attachment to the start page which displays current time. It uses swing built-in timer class that implements the ActionListener interface and the delay between timer alerts, in milliseconds. Then, the actionPerformed method of the listener is called whenvever a timer interval has elapsed, 1000 milliseconds. The actionPerformed method is automatically called on the event dispatch thread, not the timer thread; so that we can freely invoke Swing methods, repaint, in the callback. The inventoryPanel is implemented independently and added to the JTabbedPane. Inside the inventoryPanel, there is a JScrolledPane which contains a JTable to display current inventory and their current numbers. JTable in Swing is a powerful object for displaying tabular data. We do not have to spend a lot of effort customizing the existing table object. JTable retrieve their data from a TableModel which has methods to return information about the data stored in the table. The JTable that we use in the AIM system is called inventTable which is constructed by two vector parameters called data and name. Data vector stores the data that needs to be displayed, while name vector stores the name of each columns of inventTable. A JButton called ‘Display’ will display the inventory by connecting to the database. The orderSummaryPanel is another component of the JTabbedPane. It will display information of all the orders including the orderID, items included in each order and time of ordering. Similar to the inventoryPanel, orderSummaryPanel uses JTable to display information about orders as well. Fig. 3 shows another UI of the AIM system. By having a calendar on orderSummaryPanel, users can choose specific date to view orders placed in that day, or users can choose to view orders in any year or month in that year. After a user placed an order, it will be displayed immediately on today’s order list and be highlighted. The homePanel is also a component consisted in the JTabbedPane. This panel will allow manager, after login, to view sales data that happened in the past. With this information handy, manager will be able to adjust liquor sales strategy accordingly and to maximize sales overall. Sales history can help restaurant to recognize popularity of certain beers and wine in each season, which manager can efficiently maintain inventory levels during each season. Meanwhile, manager will be able to foresee sales trend with similar sales history to prepare for the trends. The restockPanel which shows the current orders that has been sent to vendor and has not been delivered yet. This panel is only visible to managers with login, and provides information of which item has been ordered and when. Such information help manager to keep track of ordering date, ordered items and ordered numbers. When manager decides to order some items, he can first check the inventory panel of low stocking items and go through the restockPanel to see whether that low stocking item has been ordered.

[pic]

Fig. 3 Another pictures of AIM

14 Business Logic

GUI design serves as the first tier of the AIM system, while business logic of the AIM serves as the connectivity between the first tier and the database, the third tier. One of the class, called InventoryUpdator running behind inventoryPanel, will be instantiated when users try to display inventory info through the inventoryPanel. This method gathers all the required data from the database and returns it to the front tier. Another class, called OrderProcessor running behind SalesPanel, will be instantiated when users click on certain inventory items. The class can also check whether numbers of ordering of certain item will fall in the total number of that item. After each item adds to the order list, OrderProcessor will update the database and store the order information to the database at the same time. Since Java is an object-oriented programming language, the business logic of AIM is implemented in plain Java objects and regular references. The business logic is completely separate from presentation tier and the database tier. Fig. 4 show the diagram of business logic implemented in AIM.

[pic]

Fig. 4 Business Logic of AIM

19 Database Schema

At this of design, only two database schemas was created, one is called ‘inventory’, the other one is called ‘orderdetail’. ‘Inventory’ table is to store the entire inventory for the AIM system. The corresponding attributes include id, name, type, sum and price. ID whose field type is of integer, can uniquely identify each inventory item, so is name attribute whose field type is string, since no two inventory items has the same name. Type attribute whose field type is of character specify types of the item: B and W. ‘B’ stands for beer and ‘W’ stands for wine. Sum attribute whose field type is of integer record current stock number of this item and its minimum value is 1. Finally price attribute whose field type is of real represent the price of the item sold in the restaurant. Second table, ‘orderdetail’, will store order information. It has following attributes: id, name, total and time. Id attribute is of type integer uniquely identifies each valid order. Name attribute is of type string, which records each item’s name. In the ‘orderdetail’ table, no two order has the same order id as well as name since the primary key is id and name. Total attribute is of type integer and store the number of items ordered by users, whose default value should at least be 1. Last attribute, time, is of type timestamp which store when certain order was placed. This information is especially useful when later user needs to generate sales report based on certain period of time, so we need to store it as well as others order details. Third table, ‘outgoingOrder’, stores the information that manager send out to vendor to order certain inventory items. This information includes id, ordered items, numbers of ordering, send out time and vendor’s email address that it was send to. This table will help manager to keep track of all the sent-out-order and prevent double order, late delivery, etc.

System Evaluation

21 Results Produced

After accomplishing the AIM implementation, we find out that the system is easy to use and user centered. AIM successfully finishes user’s requirement by providing instant information of inventory. It can generate sales report, ordering reminder, etc.

22 Comparison with Other System

However, there still exists a great deal of improvement space for AIM such as networking with main cashier’s machine so that another machine will be able to use AIM as well.

23 Load Testing

At this point of designing, we haven’t provided any test module to AIM. Hopefully, at the end of quarter, we will be able to test AIM thoroughly.

Conclusion and Future Works

By accomplishing AIM, we, the designers, obtained many valued experience of dealing with Java Foundation Class (JFC). Furthermore, we also experience Java’s object oriented paradigm throughout the entire designing process. This paradigm not only applies to business logic tier which contains plain Java code, but also to presentation tier by separating different interface components. There should be a lot more features that can add to the Automated Inventory System. One of them is networking which allow several other computers to access database simultaneously without interrupting each other. Therefore the database should include the features of ACID which stands for Atomicity, Consistency, Isolated and Durability. Furthermore, the sales report can be exported to excel file which can be saved independently. When users of AIM generate sales report, they have choices of either printing out directly or export to generate extra excel file that can be viewed using MS Excel. Nevertheless, AIM is just the beginning of inventory management system. [pic]

Fig. 5 Limited menu for users to place orders

One limitation of AIM is that it only contains nine sales item listed on the interface as shown in Fig. 5. However, with the expansion of business, more and more beers and wines could be added to the restaurant’s menu. Even with login, manager still can not change the interface of added inventory; only the designer of AIM can re-implement it and add more items to the interface. A completed AIM should allow manager to have the ways to add intended items been placed on that menu. We can re-design the menu so that items are categorized into groups or tabs, such as one tab for beer, one tabbed for wine. Thus the interface will have enough room for new items to be placed on.

-----------------------
[1] Cay S. Horstmann and Gary Cornell, Core Java Volume II-Advanced Features, 2001

-----------------------
Client
(Visual Presentation)

Database
Server

Business tier
(Business Logic)

JDBC

Inventory

InventoryPanel

OrderProcessor

SalesPanel

OrderSummary

HomePanel

OrderAndStock

RestockPanel

OrderSummaryPanel

JCalendar