Better OpenCart Admin Overview Reports
Written by Jack Davis in Tutorials on September 4, 2012 | 16 Comments
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.
16 Comments on "Better OpenCart Admin Overview Reports"
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
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
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?
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
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
Jack, we would like to use “shipped” regarding the total sales. What’ s the order_status_ID for that? thanks for your help.
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
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!
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
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.
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
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
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.
Weird, I think your blog is stripping the Greater-than/Less-than because it completely omitted it in my 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!
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