OpenCart – Create Admin Overview Reports

If someone is looking at the Total Sales and Total Sales By Year, one would be led to think that it included the total figure which the store had earned, but this isn’t so. It returns the value of all the orders regardless of what the order status is. Simply put, this Total Sales figure actually shows the total from sales which have been completed successfully AND all those which were cancelled, returned, refunded or voided which means the total figure actually isn’t representative of what the store has earned!

In this little article we will be fixing this issue and adding in a new line which shows the total money lost from cancelled, returned, refunded or voided orders so you can have a better idea of how you’re performing.

Show Correct Total Sales

The first thing to do is to make sure that the admin overview dashboard in your OpenCart store is showing the correct figures for the total sales and total sales by year! First, we need to get in the middle of the function which collects this data from your database and make sure it only counts orders with a specific order status, in this case “Completed”. Before I start however, please MAKE SURE YOU BACK UP A COPY OF THIS FILE! If you do something wrong, then you WILL mess up your site so be careful and follow this step by step. I am running a test site on and this should work fine with other versions but no guarantees. So have a copy saved somewhere safe.

Open up the file:

  • admin/model/sale/order.php
  • and run a search for “getTotalSales” which should bring to around line 564. In this very small PHP function you should see the database query which finds the total from all orders which have an order status. it will look something like:
  • public function getTotalSales() {
            $query = $this->db->query("SELECT SUM(total) AS total FROM `" . DB_PREFIX . "order` WHERE order_status_id > '0'");
        return $query->row['total'];

Obviously we know now that this query is not accurate for what we need as store owners so we are just going to make a small change to make the store look ONLY for orders with an Order Status of “5″ which means “Completed”! See the code below:

public function getTotalSales() {
        $query = $this->db->query("SELECT SUM(total) AS total FROM `" . DB_PREFIX . "order` WHERE order_status_id = '5'");

    return $query->row['total'];

And there we go! Easy as pie, you will need to do the same for the function below which should be “getTotalSalesByYear” just change the > sign for an equals and change the 0 for a 5!

Once you’ve uploaded the edited file back into its folder and refreshed your admin page then you should have a new figure which shows all the completed orders your store has welcomed!

Now, it might be a bit depressing to actually see all of those orders which have been lost but if you want to run your store well and see where you can improve then you are going to want to have that information visible in the admin overview dashboard.

Show Cancelled, Refunded, Returned & Voided Transactions

So what we will do now is add in our own functions and template instructions to show the info on our store overview dashboard. We’ll just keep editing the sale file as before, so open up:

  • admin/model/sale/order.php

and underneath the “getTotalSalesByYear” function we will add this bit of code:

public function getLostSales() {
        $query = $this->db->query("SELECT SUM(total) AS total FROM `" . DB_PREFIX . "order` WHERE order_status_id <> '5'");

    return $query->row['total'];

Below it add this code, and you are done!

$this->data['total_sale'] = $this->currency->format($this->model_sale_order->getTotalSales(), $this->config->get('config_currency'));
$this->data['total_sale_year'] = $this->currency->format($this->model_sale_order->getTotalSalesByYear(date('Y')), $this->config->get('config_currency'));
$this->data['total_order'] = $this->model_sale_order->getTotalOrders();

// This gets all lost orders */
$this->data['total_lost_sales'] = $this->currency->format($this->model_sale_order->getLostSales());
// End get lost orders */

Looking for quality OpenCart Hosting? Look no further than Arvixe Web Hosting!

Tags: , , , , , , , | Posted under OpenCart | RSS 2.0

Author Spotlight

Joe Stenhouse

I am a web application developer that specializes in PHP, JAVASCRIPT, MYSQL, HTML, and CSS. We manifest exciting potentials within the world wide web through means of innovation.

2 Comments on OpenCart – Create Admin Overview Reports

  1. swed says:

    Thanks a lot Joe! This was a very useful “lecture” on OpenCart Reports. I didn’t even know that the result of Total Sales and Total Sales By Year were summed up in such a misleading way. You call your fix “Easy as pie…” I call it amazing and beautiful as a pearl.
    I’m getting more and more puzzled and confused as I work myself down layer by layer, function by function in the anatomy of OpenCart. OpenCart isn’t what I would call an open source project. Calling it a scam is maybe a bit harsh but I’m convinced that this cart is build with the sole intention to make money. Almost all modules and functions lack just that last step that will make them efficient and functional. It’s all very clever disguised coz it’s not before you have made all the preparations to go online that the glitches and shortcomings turn up. At this stage most people buys extensions rather than migrate to another cart. This stuff doesn’t work out of the box. One needs to be skilled like you or pay serious money to turn OpenCart into a decent e-commerce tool.

    • Opencart actually does work out of the box but of course you have to often purchase themes and extensions to customize it to your liking. This cost actually pays other developers to make Open Cart better so it’s not a scam. 🙂 Thanks for the words though.

Leave a Reply to Joe Stenhouse Cancel reply

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