Tested in Prestashop 1.7.5.1, but probably same problem happens on other versions:
Looking at the code, the problem is quite obvious. Prestashop assumes that "orders.invoice_date" is present. So if the invoice date is not set on your orders (let say you have disabled the invoices), the query won't find any records to sum, thus in some cases you get $0, or less than you expect.
I fixed it by replacing invoice_date with "date_add" which is the date when the order is created. Also the query checks if the order is valid.
With this fix there's no need to depend on invoices anymore.
What to do:
You need to update get getTotalSales method in:
controllers/admin/AdminStatsController.php
With:
public static function getTotalSales($date_from, $date_to, $granularity = false)
{
if ($granularity == 'day') {
$sales = array();
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS(
'
SELECT LEFT(`date_add`, 10) AS date, SUM(total_products / o.conversion_rate) AS sales
FROM `' . _DB_PREFIX_ . 'orders` o
LEFT JOIN `' . _DB_PREFIX_ . 'order_state` os ON o.current_state = os.id_order_state
WHERE `date_add` BETWEEN "' . pSQL($date_from) . ' 00:00:00" AND "' . pSQL($date_to) . ' 23:59:59" AND os.logable = 1 AND `valid` = 1
' . Shop::addSqlRestriction(false, 'o') . '
GROUP BY LEFT(`date_add`, 10)'
);
foreach ($result as $row) {
$sales[strtotime($row['date'])] = $row['sales'];
}
return $sales;
} elseif ($granularity == 'month') {
$sales = array();
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS(
'
SELECT LEFT(`date_add`, 7) AS date, SUM(total_products / o.conversion_rate) AS sales
FROM `' . _DB_PREFIX_ . 'orders` o
LEFT JOIN `' . _DB_PREFIX_ . 'order_state` os ON o.current_state = os.id_order_state
WHERE `date_add` BETWEEN "' . pSQL($date_from) . ' 00:00:00" AND "' . pSQL($date_to) . ' 23:59:59" AND os.logable = 1 AND `valid` = 1
' . Shop::addSqlRestriction(false, 'o') . '
GROUP BY LEFT(`date_add`, 7)'
);
foreach ($result as $row) {
$sales[strtotime($row['date'] . '-01')] = $row['sales'];
}
return $sales;
} else {
return Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue(
'
SELECT SUM(total_products / o.conversion_rate)
FROM `' . _DB_PREFIX_ . 'orders` o
LEFT JOIN `' . _DB_PREFIX_ . 'order_state` os ON o.current_state = os.id_order_state
WHERE `date_add` BETWEEN "' . pSQL($date_from) . ' 00:00:00" AND "' . pSQL($date_to) . ' 23:59:59" AND os.logable = 1 AND `valid` = 1
' . Shop::addSqlRestriction(false, 'o')
);
}
}
Same thing happens with # of Orders, so here's the code (also in AdminStatsController.php😞
public static function getOrders($date_from, $date_to, $granularity = false)
{
if ($granularity == 'day') {
$orders = array();
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS(
'
SELECT LEFT(`date_add`, 10) AS date, COUNT(*) AS orders
FROM `' . _DB_PREFIX_ . 'orders` o
LEFT JOIN `' . _DB_PREFIX_ . 'order_state` os ON o.current_state = os.id_order_state
WHERE `date_add` BETWEEN "' . pSQL($date_from) . ' 00:00:00" AND "' . pSQL($date_to) . ' 23:59:59" AND os.logable = 1 AND `valid` = 1
' . Shop::addSqlRestriction(false, 'o') . '
GROUP BY LEFT(`date_add`, 10)'
);
foreach ($result as $row) {
$orders[strtotime($row['date'])] = $row['orders'];
}
return $orders;
} elseif ($granularity == 'month') {
$orders = array();
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS(
'
SELECT LEFT(`date_add`, 7) AS date, COUNT(*) AS orders
FROM `' . _DB_PREFIX_ . 'orders` o
LEFT JOIN `' . _DB_PREFIX_ . 'order_state` os ON o.current_state = os.id_order_state
WHERE `date_add` BETWEEN "' . pSQL($date_from) . ' 00:00:00" AND "' . pSQL($date_to) . ' 23:59:59" AND os.logable = 1 AND `valid` = 1
' . Shop::addSqlRestriction(false, 'o') . '
GROUP BY LEFT(`date_add`, 7)'
);
foreach ($result as $row) {
$orders[strtotime($row['date'] . '-01')] = $row['orders'];
}
return $orders;
} else {
$orders = Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue(
'
SELECT COUNT(*) AS orders
FROM `' . _DB_PREFIX_ . 'orders` o
LEFT JOIN `' . _DB_PREFIX_ . 'order_state` os ON o.current_state = os.id_order_state
WHERE `date_add` BETWEEN "' . pSQL($date_from) . ' 00:00:00" AND "' . pSQL($date_to) . ' 23:59:59" AND os.logable = 1 AND `valid` = 1
' . Shop::addSqlRestriction(false, 'o')
);
}
return $orders;
}
Hope it helps. Good luck.