Building an Online Retail Store – Update 4

The store is almost in a functional, usable state. I still have several extensions to install, which deal with the following;

– SEO Module: SEO friendly links, auto meta data generation, and more
– Extended Coupons: More functionality, time limits, auto-generated banners
– Extended searching: AJAX auto-suggestions in the search bar, ala Google
– Enhanced Shipping – Custom creatable shipping methods, based on multiple rules/options

With these in place, the store will be in a state in which I’ll be happy with using. Before installing these final modules, however, there is one more extension I need. Unfortunately, despite extensive searching, I’m unable to find what I’m looking for. The built-in order display system in OpenCart is extremely basic. You have to go into each order manually to see what has been ordered, and navigate multiple tabs to see products, customer address, order status, and so on. This is extremely slow and inefficient, not to mention very difficult to manage. When dealing with the shipping of customer orders, this is going to take up an inordinate amount of time. With no way to filter orders by status, or a quick way to pull customer data or product purchases, it’s just not feasible to use for shipping orders.

Because of this, I need to develop a custom order picking script. It doesn’t need to be overly complex at first, but it does need to be well-designed enough to be extensible in future. It needs to display orders sorted by date, and filtered by order status. Additionally, each order needs its own “complete order” button, which will change an order’s status from “processed” to “complete”, thereby removing it from the processed orders list. A timestamp may need to be placed in the order notes of when the order was shipped/completed.

The order picking list needs to display the customer’s order number, their name, address, purchases, including product name, model number, and purchase price. The available stock quantity needs to be displayed next to each product. The data needs to be formatted into tables, as to be easy to read.

One task will be in writing the necessary MySQL queries to retrieve all the data. For ease, this will comprise of three queries; the first will obtain the customer orders, based on order status, and auto-arranged by date. The second query will obtain the product information for each of these orders. The third query will update each order’s status. The queries will need to be written in such a way that any part of the query that is likely to change will be inserted as a variable, and then later linked to any forms or buttons that may be added at a later stage for filtering on orders or updating them. A drop-down list to filter orders by status is highly likely, but this can be added later. Beyond that, being able to filter orders by date range or overall product availability may be advantageous, but not a priority initially.

The table formatting code could be written as procedural inline code, but it makes much more sense to create a table-generating utility class, which will be easier to manage, and flexible enough to be re-used in future. This class can be generalised enough so that I can determine the number of columns, the number of rows, and insert the data row by row, building up the table until I’m finished with it, then move on to the next order. Most of scripting time will be spent in creating the table utility class, but this doesn’t require the database data beforehand, so I’ll likely work on this first, and create some mock orders through testing before I create the main picking list script, as I want to be able to work on it from start to finish without having to take time away from it to work on the table class.

Leave a Reply

Your email address will not be published. Required fields are marked *