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 1.5.5.1 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

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.

Leave a Reply

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


+ 8 = 9

You may use these HTML tags and attributes: <a href="" title="" rel=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>