MRP App for Makers

When done, MakerMRP will help me track parts inventory, BOMs, product inventory and sales for all boards I've been building and selling on Tindie.

After using LibreOffice Calc to try to keep track of nine products each with a dozen parts, I committed myself to spending 20+ hours writing a LAMP/AJAX/jQuery application from scratch while learning more about each on the fly.

Writing the app isn't about saving time. It's not motivated by laziness.

The mother of invention is an extreme, gut-wrenching, violent hatred of mind-numbing, boring, busy-work and the willingness to do nearly anything to avoid it.

Functionality

It's still a work in progress, but the tool is intended to manage the flow of parts ordering and of product building and selling.

Products

We start with a list of products. Products can be added, edited, and deleted. And each has an associated BOM.

Parts

Before you can have a BOM you need to be able to track parts. Each part can be added and deleted and each field can be edited.

BOM

Once you have the parts and products, you can add or delete parts to/from the BOM, or edit quantities.

Inventories

With each product, it's BOM, and lists of parts defined, the tool can keep track of inventories and orders for each product and part.

Let's say I want to build 3 more eeZee Propeller boards. I click the "needed" button on the Products page. The tool subtracts the correct number from the parts inventory. If I don't have enough parts, it keeps track of how many more to order.

Let's say I've run out of 5MHz crystals and need 3 to fulfill the needed number of products. The Parts list will show 3 in the needed column for the crystal.

I order them and click the ordered button on the Parts page.  Now the need count is 0 but the ordered count increments by 3. When those parts arrive I click the received button and ordered goes to 0 and inventory increases by 3.

Building and Selling

If I then build those 3 boards, I click the built button on the Products page and the needed count goes to 0, the inventory count goes to 3. Then I sell one, and I click the sold button. Inventory changes to 2 and sold increments 1.

Coding Approach

You have probably noticed that this is a table-based, data-driven application.

Being relatively new to jQuery, inexperienced with PHP, and a newb at mysql, I opted to write this from scratch rather than using convenient tools like jqGrid or jQuery EasyUI DataGrid.

I focused on getting core functionality working first, then refactoring and generalizing, and refining.

Core Functionality

I started by following a tutorial, Creating a Web App from Scratch in 8 parts. It was a good start and I had something working pretty quickly.

The solution consisted of several php classes for database access, and several php pages for displaying the data in tables.

I used MySQL Workbench to do the database design and sync it with my database.

Refactoring and Generalizing

The tutorial above has two flaws.

Don't Repeat Yourself

First, the database-related code is duplicated across every php class. That breaks, in my view, the Don't Repeat Yourself principle. Updating code means more opportunities to make mistakes. Keeping code in sync is harder. Fixing bugs is harder.

I refactored the code to use a single class for all data retrieval. The next step might be to use polymorphism to encapsulate the details of getting data out of each of the tables. For example the Parts table is a type of Data table, but specifies the columns specific to Parts.

Cohesion

The second, far worse problem, was that the class code was a mix of html output and database input. It's better to keep your presentation layer (html, etc) code separate from your database tier code. The two concerns are logically separate. Mixing them means that you have to dig into the guts to change the way the application looks.

I pulled all the html php out of the classes when refactoring them into a single data class. All the presentation stuff lives in presentation php code.

Generalizing

As mentioned, I created a general solution for data-retrieval by refactoring several nearly identical classes into one Data class.

I also found myself duplicating code in the presentation tier. Instead of looping through rows of data and having code for every single column, I created a php function to print out each row driven by data from the database and metadata in a variable.

The metadata is stored in each presentation page (parts.php, products.php, etc.). Now instead of large, complex, duplicated table code, I have one line: row($metadata, $data); that displays all the rows for the table along with whatever editing, buttons, etc., that I need.

But it's not perfect yet. I'm duplicating this metadata across multiple display pages. Worse the list of columns is supplied separately when instantiating the Data class and again in the metadata variable, breaking the DRY principle.

I'm moving towards creating the entire table from a single module driven by a single source of data and metadata. And while we're at it, I could reduce to a single php page that prints out any of the display tables at will. I might not if it turns out to be too complicated versus having several very small php pages.

Complex Data Relationships

Just doing tables is easy. Where it gets hard is when you have data in one table that references another table. Each part can have a type. The name of that type comes out of the Types table. My code generalizes that problem.

As another example, a BOM entry corresponds to a single part and a single product. I've also generalized that problem.

Also, particular part appears multiple times in a Product's BOM. That's similar to the two scenarios above. I'll create a general solution for this problem next.

Inline Editing

I like applications that require few clicks and which flow easily alongside the real life activities they are meant to support. I don't want to have to visit a separate page to edit a record in a table, so inline editing was a must.

I tried jEditable and a couple other approaches, but decided it would be more beneficial to learn how to do this from scratch than it would be to use some particular package.

At the same time I'm using the metadata described above to define a few types of editable data: text that can be clicked on to edit, buttons to move numbers around, and pulldowns to select from existing lists, like selecting a part to add to a BOM or selecting a part's type and subtype (the Subtypes page is shown below for giggles).

I followed a similar path of refactoring and generalization ending up with a single jQuery script that will handle all the various application events. It's not done but the UI functionality is all in place and the events are captured.

CSS

Meanwhile I've been brushing up on my CSS. As much as possible, I've tried to keep the look and feel, placement, sizes, menus, buttons, and other things like that in the styles.css file.

The table's rounded grey borders, the left float placement of image buttons set up as sprites, the navigation menu, the title, all this is handled in CSS.

All unknown to the application, without which, the tables would show up as default with standard form buttons, etc. And in fact I coded without much CSS set up at first to force myself from inserting look-and-feel in the php.

Incidentally, I've done all this on my Linux Mint environment. After my outburst, followed by a nice vacation and some rest, my outlook greatly improved and I've been in the Linux environment for the last 3 weeks straight.