Better OpenCart Admin Overview Reports

Written by in Tutorials on September 4, 2012  |  16 Comments

better-opencart-admin-overview-reports

Anybody looking at the Total Sales and Total Sales By Year would be led to think that it included the total figure which the store had earns but this isn’t true. 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 earns!

In this blog 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 Accurate 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! So basically, 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.2.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'];
}

So, that code has just added a bit of code to find all of the lost sales and sales NOT equalling “Complete”. We now need to add in the other bits to make it show on the admin panel!

Open up the controller file for the admin homepage which is:

  • admin/controller/common/home.php

and find the following code:

$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();

Below this add a line which calls our “getLostSales” function which should look like:

$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 */

From here we just need to echo our statement. I will be putting this information just below the “Total Sales By Year” as I believe this is fairly high level information which is very essential for business owners as it can be crucial in structuring business development plans, marketing campaigns and sales figures. So just open up the admin homepage template file at:

  • admin/view/template/common/home.tpl

and run a search for “text_total_sale_year” and you will see the following code:

<tr>
    <td><?php echo $text_total_sale; ?></td>
    <td><?php echo $total_sale; ?></td>
</tr>
<tr>
    <td><?php echo $text_total_sale_year; ?></td>
    <td><?php echo $total_sale_year; ?></td>
</tr>
<tr>
    <td><?php echo $text_total_order; ?></td>
    <td><?php echo $total_order; ?></td>
</tr>

For those of you who have a grasp of HTML you can see that the stats are actually shown as within a table. So all we have to do is insert a new row and include the data we want underneath it! See my edited code below:

<tr>
    <td><?php echo $text_total_sale; ?></td>
    <td><?php echo $total_sale; ?></td>
</tr>
<tr>
    <td><?php echo $text_total_sale_year; ?></td>
    <td><?php echo $total_sale_year; ?></td>
</tr>
<tr>
    <td>Lost Orders</td>
    <td><?php echo $total_lost_sales; ?></td>
</tr>
<tr>
    <td><?php echo $text_total_order; ?></td>
    <td><?php echo $total_order; ?></td>
</tr>

And thats all there is to it! The admin overview dashboard in OpenCart now shows the Total Lost Sales, including the value of all orders which have a status other than “Complete”. This gives more accurate data and an extra figure which gives you an indication of how happy your customers are!

Below is a screenshot of a test website I have made up with some fake transactions to show you how the new field looks:

I hope you’ve enjoyed this and as always, feel free to leave me a comment.

Tags: ,

About the Author

Jack Davis is a freelance eCommerce Developer and Marketing/SEO consultant from Lincoln, UK. He writes regularly on OpenCart customisations at www.jackwdavis.com and runs his own freelance OpenCart Development company Destrove at www.destrove.com.

View all posts by

16 Comments on "Better OpenCart Admin Overview Reports"

  1. Luis March 2, 2014 at 11:04 am · Reply

    Hi Jack,
    Thanks for your Blog’s contribution for Opencarts. I have just tryed to add the modifications you suggest and apparently “5″ is not the value for the finished sales in Openecart Europe 1.5.4.1 i am actually running. I have tryed all other numbers and they were turning back real figures but not the “5″. do you by any means any idea wher i can confirm if for the version i am running the “5″ is correcto or if there is a different velue for it?

    Thanks

    Regards
    Luis

  2. Jes August 4, 2013 at 1:54 am · Reply

    Thanks for the post. It inspired me to add more to the the overview:

    Total Orders Shipped: (ID=3)
    Total Orders Shipped This Year: (ID=3)
    Total Orders Completed: (ID=5)
    Total Orders Processing: (ID=2)
    Total Orders Pending: (ID=1)

    Best regards,
    Jes

  3. Berlon Burch July 25, 2013 at 10:51 pm · Reply

    public function getTotalSalesByMonth($month) {
    $query = $this->db->query(“SELECT SUM(total) AS total FROM `” . DB_PREFIX . “order` WHERE order_status_id > ’0′ AND MONTH(date_added) = MONTH(NOW()) AND YEAR(date_added) = YEAR(NOW())”);
    return $query->row['total'];
    }

    Would this be correct to get a montly sales total?

  4. David May 12, 2013 at 9:48 pm · Reply

    Hi, I found this blog which is great, but I’m getting a problem when I implement your lost sales mod. I get the error:

    Fatal error: Call to undefined method ModelSaleOrder::getLostSales() in /home/m4estr0/public_html/admin/controller/common/home.php on line 145

    I’d made a similar mod myself before I found your blog, but I got a similar error in that too! I’m using OpenCart 1.5.4. Do you have any ideas?

    Thanks
    Dave

    • Jack Davis July 20, 2013 at 1:07 pm ·

      Hi David,

      The error is telling you that the file cannot find the correct function in the order.php file. Make sure that it is definitely loading the right model file by putting:

      $this->load->model(‘sale/order’);

      right before calling the function and ensure that you have definitely named the function getTotalSales() ensuring that there are no spelling mistakes or missing capitals.

      Let me know if it gets resolved :)

      Cheers,

      Jack

  5. Carol Y. March 28, 2013 at 4:43 pm · Reply

    Jack, we would like to use “shipped” regarding the total sales. What’ s the order_status_ID for that? thanks for your help.

    • Jack Davis July 20, 2013 at 1:09 pm ·

      Hi Carol,

      It depends on how your database is set up. If you have access to PHPMyAdmin in your web hosting account you can go into the database and find the table called “order_status_id”. From there you can see all of the different statuses and what ID number is associated with it :)

      Cheers,

      Jack

  6. Anuj February 14, 2013 at 6:49 am · Reply

    Hi Jack! Great article…
    Actually I am very new to Opencart & Beginner to PHP. I am developing a Store using Opencart-1.5.4.1.
    But after knowing these serious issues I am really worried. Also there is same issue regarding STOCK. Stock quantity do not changes even after Cancelling or deleting an Order.
    Please post a solution to this bug too…
    Thanks a lot!

    • Jack Davis July 20, 2013 at 1:11 pm ·

      Hmm it seems strange that no one has posted a solution to this and updated the OpenCart software. As it is such a localised problem I will find a solution and recommend it to be merged with the version of OpenCart. Keep an eye out and I will let everyone know how to get around it.

      Cheers,

      Jack

  7. Rob Allport February 3, 2013 at 7:24 pm · Reply

    Nice post. However, wouldn;t it be more efficient to write a single MySQL query that uses group by? That’s a single query to the database, as opposed to multiple queries.

  8. Chris October 16, 2012 at 1:47 am · Reply

    Hi Jack, thanks for this writeup. Unfortunately, I couldn’t get it to work with v5.2.1 running a pretty stock/default setup. I followed everything as specified, even re-did it twice with the same result. It would give an error (I wish I saved it now), something along the lines of ‘unspecified t-variable in line 609 of vq2-admin_model_sale_order.php’. I think it was the following line of code throwing the error because in Dreamweaver it gave a syntax error as well:

    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'];
    }

    Any ideas? Or can you make this a VQmod and make it available to download? I’d really love to have this type of reporting, even if it’s basic.

    Thanks!
    Chris

    • Jack Davis October 16, 2012 at 7:44 am ·

      Hi Chris,

      Make sure that you add the “=” (equals) to the query. At the moment your query isn’t specifying the relationship between the MySQL Database row and the number 5, so if you change it to:

      $query = $this->db->query(“SELECT SUM(total) AS total FROM `” . DB_PREFIX . “order` WHERE order_status_id = ’5′”);

      Then it should correctly collect all of the rows which have the status ID of 5.

      Let me know how you get on!

      Cheers,

      Jack

    • Chris October 16, 2012 at 5:34 pm ·

      Hi Jack. Something just isn’t working right. I’ve gone back through, copied your code exactly as it is and still the same problem. I see in the code I posted above, somehow I did omit the 5 part during the copy/paste for my posting. I think this line of code is the cause of the problem:

      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'];
      }

      Should it have the 5 or should it be =5? I would think would pull all order status ids except for #5, correct? But that one line of code seems to be throwing the errors. Any ideas?

      Thx.

    • Chris October 16, 2012 at 5:35 pm ·

      Weird, I think your blog is stripping the Greater-than/Less-than because it completely omitted it in my reply. ( )

  9. Riny van Tiggelen September 4, 2012 at 11:51 am · Reply

    Instead of using the number 5 as order status in the query, you could use the setting ‘config_complete_status_id’. Apart from that, great addition to the overview!

    • Jack Davis September 4, 2012 at 12:24 pm ·

      Hi,
      You’re absolutely right but I thought I would keep it simple. I may update the article to show which numbers correlate to which order status.
      Glad you liked the blog though!
      Cheers,
      Jack

Leave a Comment

comm comm comm