Hello
The problem someone else asked about it and he got a solution
But I have a different version so it did not work for me....
................................................................................................
Hi,
I've done a few test purchases on my site, and that all works fine. However if I cancel the order without marking it as payment received, the values are still included in the 'Total Sales' figure on the dashboard - even though the product has not been sold.
Is there anyway to get this to only include those orders marked as complete - ie the ones that have actually been sold, rather than the total value of orders placed.
Thanks
.............................................
Someone answered him:
.............................................
I checked the function.. It already omits products that have order status of 0
The file is admin/model/sale/order.php
and find:
Code: Select all
$query = $this->db->query("SELECT SUM(total) AS total FROM `" . DB_PREFIX . "order` WHERE order_status_id > '0'");
If you want to ignore additional status ids then you can change it to:
Code: Select all
$query = $this->db->query("SELECT SUM(total) AS total FROM `" . DB_PREFIX . "order` WHERE order_status_id NOT IN ('0', '1', '2')");
where the numbers are the ids of the order statuses you want to exclude from the total sales
viewtopic.php?f=20&t=11613&p=774780#p774780
.........................................................................
I want to get a fix for this issue for version 3.0.3.2
Thanks
you could change that code to:
you can replace the name "complete" with whatever you use as the name for completed order status.
Code: Select all
$query = $this->db->query("SELECT SUM(total) AS total FROM `" . DB_PREFIX . "order` WHERE order_status_id IN (SELECT order_status_id FROM `" . DB_PREFIX . "order_status` WHERE NAME = 'complete')");Crystal Light Centrum Taiwan
Extensions: MailQueue | SUKHR | VBoces
“Data security is paramount at [...], and we are committed to protecting the privacy of anyone who is associated with our [...]. We’ve made a lot of improvements and will continue to make them.”
When you know your life savings are gone.
Thank you for your answer
But here there are a lot of similar codes
With which code to replace it?
But here there are a lot of similar codes
With which code to replace it?
Code: Select all
public function getOrderTotals($order_id) {
$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "order_total WHERE order_id = '" . (int)$order_id . "' ORDER BY sort_order");
return $query->rows;
}
public function getTotalOrders($data = array()) {
$sql = "SELECT COUNT(*) AS total FROM `" . DB_PREFIX . "order`";
if (!empty($data['filter_order_status'])) {
$implode = array();
$order_statuses = explode(',', $data['filter_order_status']);
foreach ($order_statuses as $order_status_id) {
$implode[] = "order_status_id = '" . (int)$order_status_id . "'";
}
if ($implode) {
$sql .= " WHERE (" . implode(" OR ", $implode) . ")";
}
} elseif (isset($data['filter_order_status_id']) && $data['filter_order_status_id'] !== '') {
$sql .= " WHERE order_status_id = '" . (int)$data['filter_order_status_id'] . "'";
} else {
$sql .= " WHERE order_status_id > '0'";
}
if (!empty($data['filter_order_id'])) {
$sql .= " AND order_id = '" . (int)$data['filter_order_id'] . "'";
}
if (!empty($data['filter_customer'])) {
$sql .= " AND CONCAT(firstname, ' ', lastname) LIKE '%" . $this->db->escape($data['filter_customer']) . "%'";
}
if (!empty($data['filter_date_added'])) {
$sql .= " AND DATE(date_added) = DATE('" . $this->db->escape($data['filter_date_added']) . "')";
}
if (!empty($data['filter_date_modified'])) {
$sql .= " AND DATE(date_modified) = DATE('" . $this->db->escape($data['filter_date_modified']) . "')";
}
if (!empty($data['filter_total'])) {
$sql .= " AND total = '" . (float)$data['filter_total'] . "'";
}
$query = $this->db->query($sql);
return $query->row['total'];
}
public function getTotalOrdersByStoreId($store_id) {
$query = $this->db->query("SELECT COUNT(*) AS total FROM `" . DB_PREFIX . "order` WHERE store_id = '" . (int)$store_id . "'");
return $query->row['total'];
}
public function getTotalOrdersByOrderStatusId($order_status_id) {
$query = $this->db->query("SELECT COUNT(*) AS total FROM `" . DB_PREFIX . "order` WHERE order_status_id = '" . (int)$order_status_id . "' AND order_status_id > '0'");
return $query->row['total'];
}
public function getTotalOrdersByProcessingStatus() {
$implode = array();
$order_statuses = $this->config->get('config_processing_status');
foreach ($order_statuses as $order_status_id) {
$implode[] = "order_status_id = '" . (int)$order_status_id . "'";
}
if ($implode) {
$query = $this->db->query("SELECT COUNT(*) AS total FROM `" . DB_PREFIX . "order` WHERE " . implode(" OR ", $implode));
return $query->row['total'];
} else {
return 0;
}
}
public function getTotalOrdersByCompleteStatus() {
$implode = array();
$order_statuses = $this->config->get('config_complete_status');
foreach ($order_statuses as $order_status_id) {
$implode[] = "order_status_id = '" . (int)$order_status_id . "'";
}
if ($implode) {
$query = $this->db->query("SELECT COUNT(*) AS total FROM `" . DB_PREFIX . "order` WHERE " . implode(" OR ", $implode) . "");
return $query->row['total'];
} else {
return 0;
}
}
public function getTotalOrdersByLanguageId($language_id) {
$query = $this->db->query("SELECT COUNT(*) AS total FROM `" . DB_PREFIX . "order` WHERE language_id = '" . (int)$language_id . "' AND order_status_id > '0'");
return $query->row['total'];
}
public function getTotalOrdersByCurrencyId($currency_id) {
$query = $this->db->query("SELECT COUNT(*) AS total FROM `" . DB_PREFIX . "order` WHERE currency_id = '" . (int)$currency_id . "' AND order_status_id > '0'");
return $query->row['total'];
}
in admin/model/extension/dashboard/sale.php
change
to
where x is your order status id for complete
or to
which gets that order status id for you based on the name
or to
which enables you to add more stati
change
Code: Select all
$sql = "SELECT SUM(total) AS total FROM `" . DB_PREFIX . "order` WHERE order_status_id > '0'";Code: Select all
$sql = "SELECT SUM(total) AS total FROM `" . DB_PREFIX . "order` WHERE order_status_id == 'x'";or to
Code: Select all
$sql = "SELECT SUM(total) AS total FROM `" . DB_PREFIX . "order` WHERE order_status_id IN (SELECT order_status_id FROM `" . DB_PREFIX . "order_status` WHERE NAME = 'complete')";or to
Code: Select all
$sql = "SELECT SUM(total) AS total FROM `" . DB_PREFIX . "order` WHERE order_status_id IN (SELECT order_status_id FROM `" . DB_PREFIX . "order_status` WHERE NAME IN ('complete','shipped','canceled'))";Crystal Light Centrum Taiwan
Extensions: MailQueue | SUKHR | VBoces
“Data security is paramount at [...], and we are committed to protecting the privacy of anyone who is associated with our [...]. We’ve made a lot of improvements and will continue to make them.”
When you know your life savings are gone.
Thank you very much
First I got errors when I chose
WHERE order_status_id
But then it worked normally when I chose
WHERE NAME IN
This
Thanks, Regards
First I got errors when I chose
WHERE order_status_id
But then it worked normally when I chose
WHERE NAME IN
This
Code: Select all
$sql = "SELECT SUM(total) AS total FROM `" . DB_PREFIX . "order` WHERE order_status_id IN (SELECT order_status_id FROM `" . DB_PREFIX . "order_status` WHERE NAME IN ('complete','shipped','Pending','Processing','Processed'))";probably because I used == (php for equal) in the query instead of just =
Crystal Light Centrum Taiwan
Extensions: MailQueue | SUKHR | VBoces
“Data security is paramount at [...], and we are committed to protecting the privacy of anyone who is associated with our [...]. We’ve made a lot of improvements and will continue to make them.”
When you know your life savings are gone.
excelent contribuition, thanks.
How can i filter only in a range of date? In the current month for example.
How can i filter only in a range of date? In the current month for example.
Take note that the modifications above will only work for the English language and considering each order status names are lowered case. This lookup will not consider multi-languages as order statuses do support multi-languages.
Dedication and passion goes to those who are able to push and merge a project.
Regards,
Straightlight
Programmer / Opencart Tester
Who is online
Users browsing this forum: No registered users and 15 guests
