Jump to content

Edit History

Mistrz Yoda

Mistrz Yoda

On 8/25/2019 at 4:39 AM, Mercader Virtual said:

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.

 

Dashtrends worked! PS 1.7.6.6 Tnx!

Dashgoals not working :(.

edit: found a solution :D here: https://github.com/PrestaShop/PrestaShop/issues/9662#issuecomment-475696541

Mistrz Yoda

Mistrz Yoda

On 8/25/2019 at 4:39 AM, Mercader Virtual said:

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.

 

Dashtrends worked! PS 1.7.6.6 Tnx!

Dashgoals not working :(.

×
×
  • Create New...