Jump to content
VajdaShark

Dashboard stats are empty after upgrading to 1.6.0.9

Recommended Posts

Hi folks,

 

after upgrading from 1.6 to 1.6.0.9 all dashboard stats stopped working. No data are shown, only zeroes. Everything else works flawlessly. Resetting the dashboard modules did not help.

 

Any ideas?

 

Thanks

Share this post


Link to post
Share on other sites

Hi folks,

 

after upgrading from 1.6 to 1.6.0.9 all dashboard stats stopped working. No data are shown, only zeroes. Everything else works flawlessly. Resetting the dashboard modules did not help.

 

Any ideas?

 

Thanks

same for me: dashboard page is empty and also the statistics are 0 since the upgrade... (not the old ones, they are still showing their numbers...

 

If anyone ia having a hint, please let me know!

Share this post


Link to post
Share on other sites

Do you have demo modus on? Have you looked if you got the dashboard modules on?

Share this post


Link to post
Share on other sites

screenshot of the empty dashboard is here

:

http://scr.hu/2sps/9bdrl

This was also my problem, but I solved it by resetting the corresponding modules (dashboard stats, goals etc.)

 

The real problem is that data are not being collected in my dashboard, see screens:

 

screen_1.jpg

 

screen_2.jpg

Share this post


Link to post
Share on other sites

Do you have demo modus on? Have you looked if you got the dashboard modules on?

yes, of course i checked all the modules and also the demo mode is not on

Share this post


Link to post
Share on other sites

This was also my problem, but I solved it by resetting the corresponding modules (dashboard stats, goals etc.)

 

The real problem is that data are not being collected in my dashboard, see screens:

 

 

 

so, we are having both the same problems, what do you mean "by resetting" the modules... uninstall and reinstalling again?

Share this post


Link to post
Share on other sites

so, we are having both the same problems, what do you mean "by resetting" the modules... uninstall and reinstalling again?

Exactly. You can check which modules to reset if you look on modules positions (hooks). All modules in Dashboard sections. But it solves only the visibility of the modules, not the fact they are shut down.

Share this post


Link to post
Share on other sites

Hi VajdaShark,

 

did you find any solutions for this till now? ... in the meantime i fixes also the modules, they are showing up now, but also the stats are just 0

Share this post


Link to post
Share on other sites

Hi VajdaShark,

 

did you find any solutions for this till now? ... in the meantime i fixes also the modules, they are showing up now, but also the stats are just 0

 

Hi,

 

no solution yet, maybe Magento :)

Share this post


Link to post
Share on other sites

Hi

 

Same problem here after upgrading from 1.4.0.7

 

I have partial stats : most of stats linked to orders and customers are filled with 0, except newsletter subscription. But visits, online visitor, order quantity and amount, order and turnover have only some 0.

Share this post


Link to post
Share on other sites

I have the same problem with 1.6.0.9.

 

Old orders shows statistics, new orders/customers do not.

 

Online customers shows always 0

Visits and Visitors shows always 0

 

Shoppingcarts for last 30 minutes on adminpanel show correct

 

Hope someone from prestashop can pay attention to this, as the stats really are crucial for planning ahead.

And with the great panel that comes with 1.6, it's a pitty to not be able to use it..

Share this post


Link to post
Share on other sites

I have the same problem with 1.6.0.9.

 

Old orders shows statistics, new orders/customers do not.

 

Online customers shows always 0

Visits and Visitors shows always 0

 

Shoppingcarts for last 30 minutes on adminpanel show correct

 

Hope someone from prestashop can pay attention to this, as the stats really are crucial for planning ahead.

And with the great panel that comes with 1.6, it's a pitty to not be able to use it..

 

I will second this.  We are having the same problem in the dashboard stats. "Sales", "Orders", "Cart Value", "Conversion Rate", and "Net Profit" are all zero, however, "Visits" are showing correctly.  Nice dashboard graph and layout - too bad it doesn't work at all. It seems like this worked before, so I'm thinking some module update broke it.   We are using 1.6.0.9 with all modules up to date. We have made some changes to the order statuses, so maybe the stats are looking for a default order completed status that it cannot find? We also have invoices turned off, I saw some mention in older versions that invoices had to be turned on.  Does anybody have an answer to this yet?

Edited by elorac (see edit history)

Share this post


Link to post
Share on other sites

I will second this.  We are having the same problem in the dashboard stats. "Sales", "Orders", "Cart Value", "Conversion Rate", and "Net Profit" are all zero, however, "Visits" are showing correctly.  Nice dashboard graph and layout - too bad it doesn't work at all. It seems like this worked before, so I'm thinking some module update broke it.   We are using 1.6.0.9 with all modules up to date. We have made some changes to the order statuses, so maybe the stats are looking for a default order completed status that it cannot find? We also have invoices turned off, I saw some mention in older versions that invoices had to be turned on.  Does anybody have an answer to this yet?

 

 

I figured it out partially.  Use this fix ONLY if you do not have invoices enabled! The default files should work fine if invoices are enabled in Orders -> Invoices.

 

This fix works for the Stats section in backoffice.  You need to replace "invoice_date" with "date_add" in a bunch of module files in order to get this to work. With invoices turned off, the date is always 0, thus, the stats need to pull a real date. I chose to use "date_add" (the date it was added).  So get out your find and replace text editor.  Be careful and be sure to have a backup before editing these files, and don't blame me if you mess it up.  This change will need to be re-applied if these modules get updated, assuming Presta doesn't fix it.

 

Here are the files to replace "invoice_date" with "date_add" in...

/modules/statssales/statssales.php (14 occurrences)

/modules/statsforecast/statsforecast.php (14 occurrences)

/modules/statscheckup/statscheckup.php (1 occurrence)

/modules/statsbestvouchers/statsbestvouchers.php (1 occurrence)

/modules/statsbestsuppliers/statsbestsuppliers.php (2 occurrences)

/modules/statsbestproducts/statsbestproducts.php (1 occurrence)

/modules/statsbestmanufacturers/statsbestmanufacturers.php (2 occurrences)

/modules/statsbestcustomers/statsbestcustomers.php (2 occurrences)

/modules/statsbestcategories/statsbestcategories.php (1 occurrence)

 

I'm going to take a look and see if I can figure out why the stats still do not show up on the dashboard.  At least I got the stats working as they should be in the stats section now...  If anyone else knows the main dashboard graph fix, please post.  I'm assuming it's a similar fix..

 

Thanks

Edited by elorac (see edit history)
  • Like 5

Share this post


Link to post
Share on other sites

I'm going to take a look and see if I can figure out why the stats still do not show up on the dashboard.

 

And now for the fix on the Dashboard page with the nice graphs...

 

First, copy the file

/controllers/admin/AdminStatsController.php 

to

/override/controllers/admin/AdminStatsController.php

 

Next, open up /override/controllers/admin/AdminStatsController.php and replace all instances of "invoice_date" with "date_add".  There should be 25 occurrences.

 

You will also need to replace "invoice_date" with "date_add" in

/modules/dashproducts/dashproducts.php (1 occurrence)

 

Hopefully this and my post above solves this issue for good!

Edited by elorac (see edit history)
  • Like 6

Share this post


Link to post
Share on other sites

I have the same problem.

 

It seems crazy that so many files need to be edited to get the Dashboard working properly.

Share this post


Link to post
Share on other sites

I thought the same thing, but I went through file by file and made the changes. My install was not upgrade, but a fresh install. I guess you just have to decide if you want the dashboard to work or not.

  • Like 1

Share this post


Link to post
Share on other sites

I've follwed all elorac (#15 and #16) steps but my BO don't show graphic stats. I've an upgraded prestashop version 1.6.0.9 from 1.3.6.0

Share this post


Link to post
Share on other sites

Tested #15 and #16 on 1.6.0.14 and it worked! I have disabled the invoices as the other people who had this problem and the #15/#16 fixed it!

 

Share this post


Link to post
Share on other sites

In case of somebody has the same problem as I had....

 

For me it was the conversion rate which was set a long time before to "0", it results as an empty stats on the dashboard for sales! (Chart).

 

So to fix that, Go to "Localization=>Currencies, and SET A CONVERSION RATE", to put back the conversion rate on previous orders, go to your phpmyadmin and execute this request : 

 

"UPDATE ps_orders set conversion_rate=1.000000 where conversion_rate=0.000000"

 

Hope it helps.

 

Cheers.

  • Like 1

Share this post


Link to post
Share on other sites

I experienced similar problem with Prestashop 1.6.1.1

I did little research in code, and found out that:

1) only orders that have invoice counts so if you don't invoice order (e.g. you use external software to generate PDF invoice)

2) you only need to put this override to  override/controllers/admin/AdminStatsController.php :

<?php
class AdminStatsController extends AdminStatsControllerCore
{
		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_paid_tax_excl / 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
			'.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_paid_tax_excl / 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
			'.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_paid_tax_excl / 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
			'.Shop::addSqlRestriction(false, 'o'));
        }
    }
    
    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
			'.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
			'.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
			'.Shop::addSqlRestriction(false, 'o'));
        }

        return $orders;
    }
    
    public static function getPurchases($date_from, $date_to, $granularity = false)
    {
        if ($granularity == 'day') {
            $purchases = array();
            $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('
			SELECT
				LEFT(`date_add`, 10) as date,
				SUM(od.`product_quantity` * IF(
					od.`purchase_supplier_price` > 0,
					od.`purchase_supplier_price` / `conversion_rate`,
					od.`original_product_price` * '.(int)Configuration::get('CONF_AVERAGE_PRODUCT_MARGIN').' / 100
				)) as total_purchase_price
			FROM `'._DB_PREFIX_.'orders` o
			LEFT JOIN `'._DB_PREFIX_.'order_detail` od ON o.id_order = od.id_order
			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
			'.Shop::addSqlRestriction(false, 'o').'
			GROUP BY LEFT(`date_add`, 10)');
            foreach ($result as $row) {
                $purchases[strtotime($row['date'])] = $row['total_purchase_price'];
            }
            return $purchases;
        } else {
            return Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('
			SELECT SUM(od.`product_quantity` * IF(
				od.`purchase_supplier_price` > 0,
				od.`purchase_supplier_price` / `conversion_rate`,
				od.`original_product_price` * '.(int)Configuration::get('CONF_AVERAGE_PRODUCT_MARGIN').' / 100
			)) as total_purchase_price
			FROM `'._DB_PREFIX_.'orders` o
			LEFT JOIN `'._DB_PREFIX_.'order_detail` od ON o.id_order = od.id_order
			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
			'.Shop::addSqlRestriction(false, 'o'));
        }
    }

    public static function getExpenses($date_from, $date_to, $granularity = false)
    {
        $expenses = ($granularity == 'day' ? array() : 0);

        $orders = Db::getInstance()->ExecuteS('
		SELECT
			LEFT(`date_add`, 10) as date,
			total_paid_tax_incl / o.conversion_rate as total_paid_tax_incl,
			total_shipping_tax_excl / o.conversion_rate as total_shipping_tax_excl,
			o.module,
			a.id_country,
			o.id_currency,
			c.id_reference as carrier_reference
		FROM `'._DB_PREFIX_.'orders` o
		LEFT JOIN `'._DB_PREFIX_.'address` a ON o.id_address_delivery = a.id_address
		LEFT JOIN `'._DB_PREFIX_.'carrier` c ON o.id_carrier = c.id_carrier
		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
		'.Shop::addSqlRestriction(false, 'o'));
        foreach ($orders as $order) {
            // Add flat fees for this order
            $flat_fees = Configuration::get('CONF_ORDER_FIXED') + (
                $order['id_currency'] == Configuration::get('PS_CURRENCY_DEFAULT')
                    ? Configuration::get('CONF_'.strtoupper($order['module']).'_FIXED')
                    : Configuration::get('CONF_'.strtoupper($order['module']).'_FIXED_FOREIGN')
                );

            // Add variable fees for this order
            $var_fees = $order['total_paid_tax_incl'] * (
                $order['id_currency'] == Configuration::get('PS_CURRENCY_DEFAULT')
                    ? Configuration::get('CONF_'.strtoupper($order['module']).'_VAR')
                    : Configuration::get('CONF_'.strtoupper($order['module']).'_VAR_FOREIGN')
                ) / 100;

            // Add shipping fees for this order
            $shipping_fees = $order['total_shipping_tax_excl'] * (
                $order['id_country'] == Configuration::get('PS_COUNTRY_DEFAULT')
                    ? Configuration::get('CONF_'.strtoupper($order['carrier_reference']).'_SHIP')
                    : Configuration::get('CONF_'.strtoupper($order['carrier_reference']).'_SHIP_OVERSEAS')
                ) / 100;

            // Tally up these fees
            if ($granularity == 'day') {
                if (!isset($expenses[strtotime($order['date'])])) {
                    $expenses[strtotime($order['date'])] = 0;
                }
                $expenses[strtotime($order['date'])] += $flat_fees + $var_fees + $shipping_fees;
            } else {
                $expenses += $flat_fees + $var_fees + $shipping_fees;
            }
        }
        return $expenses;
    }
}

And remember to delete /cache/class_index.php. That fix is cheking stats based on order add date and not invoice date.

  • Like 6

Share this post


Link to post
Share on other sites

I had the same problem after updating from 1.4. I just set all the invoice_date rows with date_add value. Like this:

UPDATE ps_orders SET invoice_date = date_add

The problem was instantly fixed and new orders are registering properly.

  • Like 1

Share this post


Link to post
Share on other sites

I experienced similar problem with Prestashop 1.6.1.1

I did little research in code, and found out that:

1) only orders that have invoice counts so if you don't invoice order (e.g. you use external software to generate PDF invoice)

2) you only need to put this override to  override/controllers/admin/AdminStatsController.php :

<?php
class AdminStatsController extends AdminStatsControllerCore
{
		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_paid_tax_excl / 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
			'.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_paid_tax_excl / 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
			'.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_paid_tax_excl / 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
			'.Shop::addSqlRestriction(false, 'o'));
        }
    }
    
    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
			'.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
			'.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
			'.Shop::addSqlRestriction(false, 'o'));
        }

        return $orders;
    }
    
    public static function getPurchases($date_from, $date_to, $granularity = false)
    {
        if ($granularity == 'day') {
            $purchases = array();
            $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('
			SELECT
				LEFT(`date_add`, 10) as date,
				SUM(od.`product_quantity` * IF(
					od.`purchase_supplier_price` > 0,
					od.`purchase_supplier_price` / `conversion_rate`,
					od.`original_product_price` * '.(int)Configuration::get('CONF_AVERAGE_PRODUCT_MARGIN').' / 100
				)) as total_purchase_price
			FROM `'._DB_PREFIX_.'orders` o
			LEFT JOIN `'._DB_PREFIX_.'order_detail` od ON o.id_order = od.id_order
			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
			'.Shop::addSqlRestriction(false, 'o').'
			GROUP BY LEFT(`date_add`, 10)');
            foreach ($result as $row) {
                $purchases[strtotime($row['date'])] = $row['total_purchase_price'];
            }
            return $purchases;
        } else {
            return Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('
			SELECT SUM(od.`product_quantity` * IF(
				od.`purchase_supplier_price` > 0,
				od.`purchase_supplier_price` / `conversion_rate`,
				od.`original_product_price` * '.(int)Configuration::get('CONF_AVERAGE_PRODUCT_MARGIN').' / 100
			)) as total_purchase_price
			FROM `'._DB_PREFIX_.'orders` o
			LEFT JOIN `'._DB_PREFIX_.'order_detail` od ON o.id_order = od.id_order
			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
			'.Shop::addSqlRestriction(false, 'o'));
        }
    }

    public static function getExpenses($date_from, $date_to, $granularity = false)
    {
        $expenses = ($granularity == 'day' ? array() : 0);

        $orders = Db::getInstance()->ExecuteS('
		SELECT
			LEFT(`date_add`, 10) as date,
			total_paid_tax_incl / o.conversion_rate as total_paid_tax_incl,
			total_shipping_tax_excl / o.conversion_rate as total_shipping_tax_excl,
			o.module,
			a.id_country,
			o.id_currency,
			c.id_reference as carrier_reference
		FROM `'._DB_PREFIX_.'orders` o
		LEFT JOIN `'._DB_PREFIX_.'address` a ON o.id_address_delivery = a.id_address
		LEFT JOIN `'._DB_PREFIX_.'carrier` c ON o.id_carrier = c.id_carrier
		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
		'.Shop::addSqlRestriction(false, 'o'));
        foreach ($orders as $order) {
            // Add flat fees for this order
            $flat_fees = Configuration::get('CONF_ORDER_FIXED') + (
                $order['id_currency'] == Configuration::get('PS_CURRENCY_DEFAULT')
                    ? Configuration::get('CONF_'.strtoupper($order['module']).'_FIXED')
                    : Configuration::get('CONF_'.strtoupper($order['module']).'_FIXED_FOREIGN')
                );

            // Add variable fees for this order
            $var_fees = $order['total_paid_tax_incl'] * (
                $order['id_currency'] == Configuration::get('PS_CURRENCY_DEFAULT')
                    ? Configuration::get('CONF_'.strtoupper($order['module']).'_VAR')
                    : Configuration::get('CONF_'.strtoupper($order['module']).'_VAR_FOREIGN')
                ) / 100;

            // Add shipping fees for this order
            $shipping_fees = $order['total_shipping_tax_excl'] * (
                $order['id_country'] == Configuration::get('PS_COUNTRY_DEFAULT')
                    ? Configuration::get('CONF_'.strtoupper($order['carrier_reference']).'_SHIP')
                    : Configuration::get('CONF_'.strtoupper($order['carrier_reference']).'_SHIP_OVERSEAS')
                ) / 100;

            // Tally up these fees
            if ($granularity == 'day') {
                if (!isset($expenses[strtotime($order['date'])])) {
                    $expenses[strtotime($order['date'])] = 0;
                }
                $expenses[strtotime($order['date'])] += $flat_fees + $var_fees + $shipping_fees;
            } else {
                $expenses += $flat_fees + $var_fees + $shipping_fees;
            }
        }
        return $expenses;
    }
}

And remember to delete /cache/class_index.php. That fix is cheking stats based on order add date and not invoice date.

 

Do I understand it correctly if this causes all orders to be counted in stats even if it has no invoice created?

Sounds great, do I need to add all the text to the file or just some of it? Does it matter if I add it on top or bottom?

Big thanks!

Share this post


Link to post
Share on other sites

Yes, you have to paste whole coude, couse it is override. It won't change any core files and thanks to that it will work after upgrade.

 

One thing to remember it will only change statistics on Dashboard in backoffice. Our client didn't is using external invoice app with precise selling stats and thouse are used only as "quick view".

 

Hope it will help @chienandalu

Share this post


Link to post
Share on other sites

I experienced similar problem with Prestashop 1.6.1.1

I did little research in code, and found out that:

1) only orders that have invoice counts so if you don't invoice order (e.g. you use external software to generate PDF invoice)

2) you only need to put this override to  override/controllers/admin/AdminStatsController.php :

<?php
class AdminStatsController extends AdminStatsControllerCore
{
		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_paid_tax_excl / 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
			'.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_paid_tax_excl / 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
			'.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_paid_tax_excl / 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
			'.Shop::addSqlRestriction(false, 'o'));
        }
    }
    
    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
			'.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
			'.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
			'.Shop::addSqlRestriction(false, 'o'));
        }

        return $orders;
    }
    
    public static function getPurchases($date_from, $date_to, $granularity = false)
    {
        if ($granularity == 'day') {
            $purchases = array();
            $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('
			SELECT
				LEFT(`date_add`, 10) as date,
				SUM(od.`product_quantity` * IF(
					od.`purchase_supplier_price` > 0,
					od.`purchase_supplier_price` / `conversion_rate`,
					od.`original_product_price` * '.(int)Configuration::get('CONF_AVERAGE_PRODUCT_MARGIN').' / 100
				)) as total_purchase_price
			FROM `'._DB_PREFIX_.'orders` o
			LEFT JOIN `'._DB_PREFIX_.'order_detail` od ON o.id_order = od.id_order
			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
			'.Shop::addSqlRestriction(false, 'o').'
			GROUP BY LEFT(`date_add`, 10)');
            foreach ($result as $row) {
                $purchases[strtotime($row['date'])] = $row['total_purchase_price'];
            }
            return $purchases;
        } else {
            return Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('
			SELECT SUM(od.`product_quantity` * IF(
				od.`purchase_supplier_price` > 0,
				od.`purchase_supplier_price` / `conversion_rate`,
				od.`original_product_price` * '.(int)Configuration::get('CONF_AVERAGE_PRODUCT_MARGIN').' / 100
			)) as total_purchase_price
			FROM `'._DB_PREFIX_.'orders` o
			LEFT JOIN `'._DB_PREFIX_.'order_detail` od ON o.id_order = od.id_order
			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
			'.Shop::addSqlRestriction(false, 'o'));
        }
    }

    public static function getExpenses($date_from, $date_to, $granularity = false)
    {
        $expenses = ($granularity == 'day' ? array() : 0);

        $orders = Db::getInstance()->ExecuteS('
		SELECT
			LEFT(`date_add`, 10) as date,
			total_paid_tax_incl / o.conversion_rate as total_paid_tax_incl,
			total_shipping_tax_excl / o.conversion_rate as total_shipping_tax_excl,
			o.module,
			a.id_country,
			o.id_currency,
			c.id_reference as carrier_reference
		FROM `'._DB_PREFIX_.'orders` o
		LEFT JOIN `'._DB_PREFIX_.'address` a ON o.id_address_delivery = a.id_address
		LEFT JOIN `'._DB_PREFIX_.'carrier` c ON o.id_carrier = c.id_carrier
		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
		'.Shop::addSqlRestriction(false, 'o'));
        foreach ($orders as $order) {
            // Add flat fees for this order
            $flat_fees = Configuration::get('CONF_ORDER_FIXED') + (
                $order['id_currency'] == Configuration::get('PS_CURRENCY_DEFAULT')
                    ? Configuration::get('CONF_'.strtoupper($order['module']).'_FIXED')
                    : Configuration::get('CONF_'.strtoupper($order['module']).'_FIXED_FOREIGN')
                );

            // Add variable fees for this order
            $var_fees = $order['total_paid_tax_incl'] * (
                $order['id_currency'] == Configuration::get('PS_CURRENCY_DEFAULT')
                    ? Configuration::get('CONF_'.strtoupper($order['module']).'_VAR')
                    : Configuration::get('CONF_'.strtoupper($order['module']).'_VAR_FOREIGN')
                ) / 100;

            // Add shipping fees for this order
            $shipping_fees = $order['total_shipping_tax_excl'] * (
                $order['id_country'] == Configuration::get('PS_COUNTRY_DEFAULT')
                    ? Configuration::get('CONF_'.strtoupper($order['carrier_reference']).'_SHIP')
                    : Configuration::get('CONF_'.strtoupper($order['carrier_reference']).'_SHIP_OVERSEAS')
                ) / 100;

            // Tally up these fees
            if ($granularity == 'day') {
                if (!isset($expenses[strtotime($order['date'])])) {
                    $expenses[strtotime($order['date'])] = 0;
                }
                $expenses[strtotime($order['date'])] += $flat_fees + $var_fees + $shipping_fees;
            } else {
                $expenses += $flat_fees + $var_fees + $shipping_fees;
            }
        }
        return $expenses;
    }
}

And remember to delete /cache/class_index.php. That fix is cheking stats based on order add date and not invoice date.

 

Exactly ! It was beacuse the client was using an external invoice software and so invoicing was desactivated... Can't belive it.

Thank you so much for pointing it up, and your override works great !

Share this post


Link to post
Share on other sites

I experienced similar problem with Prestashop 1.6.1.1

I did little research in code, and found out that:

1) only orders that have invoice counts so if you don't invoice order (e.g. you use external software to generate PDF invoice)

2) you only need to put this override to  override/controllers/admin/AdminStatsController.php :

 

And remember to delete /cache/class_index.php. That fix is cheking stats based on order add date and not invoice date.

 

Thanks a lot for this simple fix for those not using prestashop invoice system. Working great on 1.6.1.2!

Share this post


Link to post
Share on other sites

I figured it out partially.  Use this fix ONLY if you do not have invoices enabled! The default files should work fine if invoices are enabled in Orders -> Invoices.

 

This fix works for the Stats section in backoffice.  You need to replace "invoice_date" with "date_add" in a bunch of module files in order to get this to work. With invoices turned off, the date is always 0, thus, the stats need to pull a real date. I chose to use "date_add" (the date it was added).  So get out your find and replace text editor.  Be careful and be sure to have a backup before editing these files, and don't blame me if you mess it up.  This change will need to be re-applied if these modules get updated, assuming Presta doesn't fix it.

 

Here are the files to replace "invoice_date" with "date_add" in...

/modules/statssales/statssales.php (14 occurrences)

/modules/statsforecast/statsforecast.php (14 occurrences)

/modules/statscheckup/statscheckup.php (1 occurrence)

/modules/statsbestvouchers/statsbestvouchers.php (1 occurrence)

/modules/statsbestsuppliers/statsbestsuppliers.php (2 occurrences)

/modules/statsbestproducts/statsbestproducts.php (1 occurrence)

/modules/statsbestmanufacturers/statsbestmanufacturers.php (2 occurrences)

/modules/statsbestcustomers/statsbestcustomers.php (2 occurrences)

/modules/statsbestcategories/statsbestcategories.php (1 occurrence)

 

I'm going to take a look and see if I can figure out why the stats still do not show up on the dashboard.  At least I got the stats working as they should be in the stats section now...  If anyone else knows the main dashboard graph fix, please post.  I'm assuming it's a similar fix..

 

Thanks

Hola, esto me funciono, con la mayoria de las estadisticas, menos la de bestcustomers, puede haber otro problema asociado? 

Share this post


Link to post
Share on other sites

I had the same problem after updating from 1.4. I just set all the invoice_date rows with date_add value. Like this:

UPDATE ps_orders SET invoice_date = date_add

The problem was instantly fixed and new orders are registering properly.

 

Simple and effective. Added skorupa's override too. Dashboard now shows all stats correctly. (prestashop 1.6.1.4)

 

Thank you..!!!!!!!

Edited by pattihis (see edit history)

Share this post


Link to post
Share on other sites

No need this complicated sollutions... Easy solution is for me:

In PaymentModule.php change this line

$order->invoice_date = '0000-00-00 00:00:00';

to

$order->invoice_date = date('Y-m-d H:i:s');

It's working for me and I don't need to override or edited many files...

 

for data before this mod you need execute this query

UPDATE ps_orders SET invoice_date = date_add
Edited by xpumpa (see edit history)
  • Like 4

Share this post


Link to post
Share on other sites

 

No need this complicated sollutions... Easy solution is for me:

In PaymentModule.php change this line

$order->invoice_date = '0000-00-00 00:00:00';

to

$order->invoice_date = date('Y-m-d H:i:s');

It's working for me and I don't need to override or edited many files...

 

for data before this mod you need execute this query

UPDATE ps_orders SET invoice_date = date_add

 

This is good solution, but remeber to put it in override so Prestashop update won't change it.

 

Other thing is that I override only one file. Where you will have to check every payment module you use to check if your solution have to be applied or not.

Share this post


Link to post
Share on other sites

This is independent on payments modules. This mod is in classes/PaymentModule.php function validateOrder. Every order call this function i think.

Share this post


Link to post
Share on other sites

 

No need this complicated sollutions... Easy solution is for me:

In PaymentModule.php change this line

$order->invoice_date = '0000-00-00 00:00:00';

to

$order->invoice_date = date('Y-m-d H:i:s');

It's working for me and I don't need to override or edited many files...

 

for data before this mod you need execute this query

UPDATE ps_orders SET invoice_date = date_add

 

THANKS!!! THIS WORK FOR ME TOO :) PS 1.4.9

Share this post


Link to post
Share on other sites

I experienced similar problem with Prestashop 1.6.1.1

I did little research in code, and found out that:

1) only orders that have invoice counts so if you don't invoice order (e.g. you use external software to generate PDF invoice)

2) you only need to put this override to  override/controllers/admin/AdminStatsController.php :

<?php
class AdminStatsController extends AdminStatsControllerCore
{
		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_paid_tax_excl / 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
			'.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_paid_tax_excl / 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
			'.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_paid_tax_excl / 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
			'.Shop::addSqlRestriction(false, 'o'));
        }
    }
    
    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
			'.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
			'.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
			'.Shop::addSqlRestriction(false, 'o'));
        }

        return $orders;
    }
    
    public static function getPurchases($date_from, $date_to, $granularity = false)
    {
        if ($granularity == 'day') {
            $purchases = array();
            $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('
			SELECT
				LEFT(`date_add`, 10) as date,
				SUM(od.`product_quantity` * IF(
					od.`purchase_supplier_price` > 0,
					od.`purchase_supplier_price` / `conversion_rate`,
					od.`original_product_price` * '.(int)Configuration::get('CONF_AVERAGE_PRODUCT_MARGIN').' / 100
				)) as total_purchase_price
			FROM `'._DB_PREFIX_.'orders` o
			LEFT JOIN `'._DB_PREFIX_.'order_detail` od ON o.id_order = od.id_order
			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
			'.Shop::addSqlRestriction(false, 'o').'
			GROUP BY LEFT(`date_add`, 10)');
            foreach ($result as $row) {
                $purchases[strtotime($row['date'])] = $row['total_purchase_price'];
            }
            return $purchases;
        } else {
            return Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('
			SELECT SUM(od.`product_quantity` * IF(
				od.`purchase_supplier_price` > 0,
				od.`purchase_supplier_price` / `conversion_rate`,
				od.`original_product_price` * '.(int)Configuration::get('CONF_AVERAGE_PRODUCT_MARGIN').' / 100
			)) as total_purchase_price
			FROM `'._DB_PREFIX_.'orders` o
			LEFT JOIN `'._DB_PREFIX_.'order_detail` od ON o.id_order = od.id_order
			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
			'.Shop::addSqlRestriction(false, 'o'));
        }
    }

    public static function getExpenses($date_from, $date_to, $granularity = false)
    {
        $expenses = ($granularity == 'day' ? array() : 0);

        $orders = Db::getInstance()->ExecuteS('
		SELECT
			LEFT(`date_add`, 10) as date,
			total_paid_tax_incl / o.conversion_rate as total_paid_tax_incl,
			total_shipping_tax_excl / o.conversion_rate as total_shipping_tax_excl,
			o.module,
			a.id_country,
			o.id_currency,
			c.id_reference as carrier_reference
		FROM `'._DB_PREFIX_.'orders` o
		LEFT JOIN `'._DB_PREFIX_.'address` a ON o.id_address_delivery = a.id_address
		LEFT JOIN `'._DB_PREFIX_.'carrier` c ON o.id_carrier = c.id_carrier
		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
		'.Shop::addSqlRestriction(false, 'o'));
        foreach ($orders as $order) {
            // Add flat fees for this order
            $flat_fees = Configuration::get('CONF_ORDER_FIXED') + (
                $order['id_currency'] == Configuration::get('PS_CURRENCY_DEFAULT')
                    ? Configuration::get('CONF_'.strtoupper($order['module']).'_FIXED')
                    : Configuration::get('CONF_'.strtoupper($order['module']).'_FIXED_FOREIGN')
                );

            // Add variable fees for this order
            $var_fees = $order['total_paid_tax_incl'] * (
                $order['id_currency'] == Configuration::get('PS_CURRENCY_DEFAULT')
                    ? Configuration::get('CONF_'.strtoupper($order['module']).'_VAR')
                    : Configuration::get('CONF_'.strtoupper($order['module']).'_VAR_FOREIGN')
                ) / 100;

            // Add shipping fees for this order
            $shipping_fees = $order['total_shipping_tax_excl'] * (
                $order['id_country'] == Configuration::get('PS_COUNTRY_DEFAULT')
                    ? Configuration::get('CONF_'.strtoupper($order['carrier_reference']).'_SHIP')
                    : Configuration::get('CONF_'.strtoupper($order['carrier_reference']).'_SHIP_OVERSEAS')
                ) / 100;

            // Tally up these fees
            if ($granularity == 'day') {
                if (!isset($expenses[strtotime($order['date'])])) {
                    $expenses[strtotime($order['date'])] = 0;
                }
                $expenses[strtotime($order['date'])] += $flat_fees + $var_fees + $shipping_fees;
            } else {
                $expenses += $flat_fees + $var_fees + $shipping_fees;
            }
        }
        return $expenses;
    }
}

And remember to delete /cache/class_index.php. That fix is cheking stats based on order add date and not invoice date.

 

Any idea how to use this to fix "traffic" dashboard for visits, unique visitors etc.? I'm currently have zeroes in the dashboard, but stats module is working fine (also upgraded from 1.4.8.2 to 1.6.1.5.

Share this post


Link to post
Share on other sites

I had this problem too, and the override solution worked perfect on Prestashop 1.6.1.4, but since i updated to 1.6.1.10, the override stopped working and the dashboard stats were empty, not even 0 value.

 

So i discovered a solution for stats to show again, but i must tell you that the old stats(for orders before update) are not displayed. Here is what i did:

 

1. Go to Order > Invoices and CHECK "Enable invoices".

2. Go to Order > Statuses and for status "Processing in progress" choose to generate Invoice(the green check mark should appear).

3. Place a test order from the front of your site. After doing that, the order should be shown in the dashboard stats and the invoice should have been generated.

4. Go back to step Nr. 1 and access Order > Invoices and UNCHECK "Enable invoices". This way you will avoid generating Invoices for new orders and customers will not receive any invoice on e-mail when placing the order.

5. Place a new test order and you`ll see that it will be registered and showed in dashboard stats.

 

That`s all.

Share this post


Link to post
Share on other sites

Hi,

actually I updated shops where I use override solution to 1.6.1.10 and on both everything is working fine.

 

Your enable -> disable solution will not work without override couse to generate statistics in dashboard Prestashop is checking what is invoice date. If there is none than it will not include such transaction in statistics :-[

Maby some other override changed statistics code in a way that prevent it from working.

 

Regards.

Share this post


Link to post
Share on other sites

Hi,

actually I updated shops where I use override solution to 1.6.1.10 and on both everything is working fine.

 

Your enable -> disable solution will not work without override couse to generate statistics in dashboard Prestashop is checking what is invoice date. If there is none than it will not include such transaction in statistics :-[

Maby some other override changed statistics code in a way that prevent it from working.

 

Regards.

 

 

Maybe(as you say some other override... but you have nor arguments on that, is just what you suppose), but you should not say that the solution i gave it`s not working as it is working for me with no override. So when you say that, other people that read this topic will probably not try it so they will not know if it will ever work for them as it worked for me. I thought that the purpose here on this forum is to help people and to give them our best experience on Prestashop problems we faced and we somehow solved.

 

Have you at least tried the solution i gave before answering this topic? If it doesn`t work for you, it doesn`t mean that it will not work for anybody else. Let pleople try, they have no reason not to do it.

 

Thank you.

  • Like 1

Share this post


Link to post
Share on other sites

Actually I can :-D

 

I programm in PHP & MySQL daily, and when I read AdminStatsController.php, I see everywhere parts of code that looks like that

WHERE `invoice_date` BETWEEN ...

You don't have to be a programmer to know that this stat will be based on invoice date :-D

 

So new transactions will not be taken in to account in stats if generating invoice is turned off.

Share this post


Link to post
Share on other sites

Actually I can :-D

 

I programm in PHP & MySQL daily, and when I read AdminStatsController.php, I see everywhere parts of code that looks like that

WHERE `invoice_date` BETWEEN ...

You don't have to be a programmer to know that this stat will be based on invoice date :-D

 

So new transactions will not be taken in to account in stats if generating invoice is turned off.

 

I know you code and i also don`t want to make this a dispute.

I just tried the solution again on a Prestashop 1.6.1.7 this time(without any override), I followed the steps i mentioned and IT WORKS.

I cannot explain how, but it works on 1.6.1.7 and 1.6.1.10(at least this is what i tested).

 

The solution is simple, first you have to place a test order(or an order) with Enabled invoices from Order > Invoices and with status "Processing in progress" allowing to generate invoice.

After you do that, you can uncheck "Enable invoices" form Order > Invoices and the orders will be counted.

 

Maybe it is a Prestashop bug, but for now, at least for me, this solution works great, so you should try it on a new prestashop install and see if it works.(of course if you want to convince yourself)

 

I just hope it helps other peoples on this topic.

  • Like 1

Share this post


Link to post
Share on other sites

I know you code and i also don`t want to make this a dispute.

I just tried the solution again on a Prestashop 1.6.1.7 this time(without any override), I followed the steps i mentioned and IT WORKS.

I cannot explain how, but it works on 1.6.1.7 and 1.6.1.10(at least this is what i tested).

 

The solution is simple, first you have to place a test order(or an order) with Enabled invoices from Order > Invoices and with status "Processing in progress" allowing to generate invoice.

After you do that, you can uncheck "Enable invoices" form Order > Invoices and the orders will be counted.

 

Maybe it is a Prestashop bug, but for now, at least for me, this solution works great, so you should try it on a new prestashop install and see if it works.(of course if you want to convince yourself)

 

I just hope it helps other peoples on this topic.

 

I had PS version 1.6.1.1 and used the override, it worked great. I upgraded to version 1.6.1.11 and I can confirm the override no longer works like gabriello states. My Dashboard showed nothing (the modules are there but have no contents about sales).

 

I have also fixed it by enabling invoices under Order statusses (I didn't even have to make a test order) and then disabling invoices again. Now I have the graph in Dashboard stats with data again.

Share this post


Link to post
Share on other sites

And now for the fix on the Dashboard page with the nice graphs...

 

First, copy the file

/controllers/admin/AdminStatsController.php 

to

/override/controllers/admin/AdminStatsController.php

 

Next, open up /override/controllers/admin/AdminStatsController.php and replace all instances of "invoice_date" with "date_add".  There should be 25 occurrences.

 

You will also need to replace "invoice_date" with "date_add" in

/modules/dashproducts/dashproducts.php (1 occurrence)

 

Hopefully this and my post above solves this issue for good!

I confirm, that this override works fine for PrestaShop v. 1.6.1.7. Great Job elorac!

Share this post


Link to post
Share on other sites

I experienced similar problem with Prestashop 1.6.1.1

I did little research in code, and found out that:

1) only orders that have invoice counts so if you don't invoice order (e.g. you use external software to generate PDF invoice)

2) you only need to put this override to  override/controllers/admin/AdminStatsController.php :

<?php
class AdminStatsController extends AdminStatsControllerCore
{
		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_paid_tax_excl / 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
			'.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_paid_tax_excl / 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
			'.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_paid_tax_excl / 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
			'.Shop::addSqlRestriction(false, 'o'));
        }
    }
    
    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
			'.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
			'.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
			'.Shop::addSqlRestriction(false, 'o'));
        }

        return $orders;
    }
    
    public static function getPurchases($date_from, $date_to, $granularity = false)
    {
        if ($granularity == 'day') {
            $purchases = array();
            $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('
			SELECT
				LEFT(`date_add`, 10) as date,
				SUM(od.`product_quantity` * IF(
					od.`purchase_supplier_price` > 0,
					od.`purchase_supplier_price` / `conversion_rate`,
					od.`original_product_price` * '.(int)Configuration::get('CONF_AVERAGE_PRODUCT_MARGIN').' / 100
				)) as total_purchase_price
			FROM `'._DB_PREFIX_.'orders` o
			LEFT JOIN `'._DB_PREFIX_.'order_detail` od ON o.id_order = od.id_order
			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
			'.Shop::addSqlRestriction(false, 'o').'
			GROUP BY LEFT(`date_add`, 10)');
            foreach ($result as $row) {
                $purchases[strtotime($row['date'])] = $row['total_purchase_price'];
            }
            return $purchases;
        } else {
            return Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('
			SELECT SUM(od.`product_quantity` * IF(
				od.`purchase_supplier_price` > 0,
				od.`purchase_supplier_price` / `conversion_rate`,
				od.`original_product_price` * '.(int)Configuration::get('CONF_AVERAGE_PRODUCT_MARGIN').' / 100
			)) as total_purchase_price
			FROM `'._DB_PREFIX_.'orders` o
			LEFT JOIN `'._DB_PREFIX_.'order_detail` od ON o.id_order = od.id_order
			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
			'.Shop::addSqlRestriction(false, 'o'));
        }
    }

    public static function getExpenses($date_from, $date_to, $granularity = false)
    {
        $expenses = ($granularity == 'day' ? array() : 0);

        $orders = Db::getInstance()->ExecuteS('
		SELECT
			LEFT(`date_add`, 10) as date,
			total_paid_tax_incl / o.conversion_rate as total_paid_tax_incl,
			total_shipping_tax_excl / o.conversion_rate as total_shipping_tax_excl,
			o.module,
			a.id_country,
			o.id_currency,
			c.id_reference as carrier_reference
		FROM `'._DB_PREFIX_.'orders` o
		LEFT JOIN `'._DB_PREFIX_.'address` a ON o.id_address_delivery = a.id_address
		LEFT JOIN `'._DB_PREFIX_.'carrier` c ON o.id_carrier = c.id_carrier
		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
		'.Shop::addSqlRestriction(false, 'o'));
        foreach ($orders as $order) {
            // Add flat fees for this order
            $flat_fees = Configuration::get('CONF_ORDER_FIXED') + (
                $order['id_currency'] == Configuration::get('PS_CURRENCY_DEFAULT')
                    ? Configuration::get('CONF_'.strtoupper($order['module']).'_FIXED')
                    : Configuration::get('CONF_'.strtoupper($order['module']).'_FIXED_FOREIGN')
                );

            // Add variable fees for this order
            $var_fees = $order['total_paid_tax_incl'] * (
                $order['id_currency'] == Configuration::get('PS_CURRENCY_DEFAULT')
                    ? Configuration::get('CONF_'.strtoupper($order['module']).'_VAR')
                    : Configuration::get('CONF_'.strtoupper($order['module']).'_VAR_FOREIGN')
                ) / 100;

            // Add shipping fees for this order
            $shipping_fees = $order['total_shipping_tax_excl'] * (
                $order['id_country'] == Configuration::get('PS_COUNTRY_DEFAULT')
                    ? Configuration::get('CONF_'.strtoupper($order['carrier_reference']).'_SHIP')
                    : Configuration::get('CONF_'.strtoupper($order['carrier_reference']).'_SHIP_OVERSEAS')
                ) / 100;

            // Tally up these fees
            if ($granularity == 'day') {
                if (!isset($expenses[strtotime($order['date'])])) {
                    $expenses[strtotime($order['date'])] = 0;
                }
                $expenses[strtotime($order['date'])] += $flat_fees + $var_fees + $shipping_fees;
            } else {
                $expenses += $flat_fees + $var_fees + $shipping_fees;
            }
        }
        return $expenses;
    }
}

And remember to delete /cache/class_index.php. That fix is cheking stats based on order add date and not invoice date.

 

Works like a charm! Can't believe Prestashop doesn't have this feature by default. Many people generate their own invoice using the accounting program rather than using the website.

Share this post


Link to post
Share on other sites

I experienced similar problem with Prestashop 1.6.1.1

I did little research in code, and found out that:

1) only orders that have invoice counts so if you don't invoice order (e.g. you use external software to generate PDF invoice)

2) you only need to put this override to  override/controllers/admin/AdminStatsController.php :

<?php
class AdminStatsController extends AdminStatsControllerCore
{
		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_paid_tax_excl / 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
			'.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_paid_tax_excl / 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
			'.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_paid_tax_excl / 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
			'.Shop::addSqlRestriction(false, 'o'));
        }
    }
    
    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
			'.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
			'.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
			'.Shop::addSqlRestriction(false, 'o'));
        }

        return $orders;
    }
    
    public static function getPurchases($date_from, $date_to, $granularity = false)
    {
        if ($granularity == 'day') {
            $purchases = array();
            $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('
			SELECT
				LEFT(`date_add`, 10) as date,
				SUM(od.`product_quantity` * IF(
					od.`purchase_supplier_price` > 0,
					od.`purchase_supplier_price` / `conversion_rate`,
					od.`original_product_price` * '.(int)Configuration::get('CONF_AVERAGE_PRODUCT_MARGIN').' / 100
				)) as total_purchase_price
			FROM `'._DB_PREFIX_.'orders` o
			LEFT JOIN `'._DB_PREFIX_.'order_detail` od ON o.id_order = od.id_order
			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
			'.Shop::addSqlRestriction(false, 'o').'
			GROUP BY LEFT(`date_add`, 10)');
            foreach ($result as $row) {
                $purchases[strtotime($row['date'])] = $row['total_purchase_price'];
            }
            return $purchases;
        } else {
            return Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('
			SELECT SUM(od.`product_quantity` * IF(
				od.`purchase_supplier_price` > 0,
				od.`purchase_supplier_price` / `conversion_rate`,
				od.`original_product_price` * '.(int)Configuration::get('CONF_AVERAGE_PRODUCT_MARGIN').' / 100
			)) as total_purchase_price
			FROM `'._DB_PREFIX_.'orders` o
			LEFT JOIN `'._DB_PREFIX_.'order_detail` od ON o.id_order = od.id_order
			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
			'.Shop::addSqlRestriction(false, 'o'));
        }
    }

    public static function getExpenses($date_from, $date_to, $granularity = false)
    {
        $expenses = ($granularity == 'day' ? array() : 0);

        $orders = Db::getInstance()->ExecuteS('
		SELECT
			LEFT(`date_add`, 10) as date,
			total_paid_tax_incl / o.conversion_rate as total_paid_tax_incl,
			total_shipping_tax_excl / o.conversion_rate as total_shipping_tax_excl,
			o.module,
			a.id_country,
			o.id_currency,
			c.id_reference as carrier_reference
		FROM `'._DB_PREFIX_.'orders` o
		LEFT JOIN `'._DB_PREFIX_.'address` a ON o.id_address_delivery = a.id_address
		LEFT JOIN `'._DB_PREFIX_.'carrier` c ON o.id_carrier = c.id_carrier
		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
		'.Shop::addSqlRestriction(false, 'o'));
        foreach ($orders as $order) {
            // Add flat fees for this order
            $flat_fees = Configuration::get('CONF_ORDER_FIXED') + (
                $order['id_currency'] == Configuration::get('PS_CURRENCY_DEFAULT')
                    ? Configuration::get('CONF_'.strtoupper($order['module']).'_FIXED')
                    : Configuration::get('CONF_'.strtoupper($order['module']).'_FIXED_FOREIGN')
                );

            // Add variable fees for this order
            $var_fees = $order['total_paid_tax_incl'] * (
                $order['id_currency'] == Configuration::get('PS_CURRENCY_DEFAULT')
                    ? Configuration::get('CONF_'.strtoupper($order['module']).'_VAR')
                    : Configuration::get('CONF_'.strtoupper($order['module']).'_VAR_FOREIGN')
                ) / 100;

            // Add shipping fees for this order
            $shipping_fees = $order['total_shipping_tax_excl'] * (
                $order['id_country'] == Configuration::get('PS_COUNTRY_DEFAULT')
                    ? Configuration::get('CONF_'.strtoupper($order['carrier_reference']).'_SHIP')
                    : Configuration::get('CONF_'.strtoupper($order['carrier_reference']).'_SHIP_OVERSEAS')
                ) / 100;

            // Tally up these fees
            if ($granularity == 'day') {
                if (!isset($expenses[strtotime($order['date'])])) {
                    $expenses[strtotime($order['date'])] = 0;
                }
                $expenses[strtotime($order['date'])] += $flat_fees + $var_fees + $shipping_fees;
            } else {
                $expenses += $flat_fees + $var_fees + $shipping_fees;
            }
        }
        return $expenses;
    }
}

And remember to delete /cache/class_index.php. That fix is cheking stats based on order add date and not invoice date.

 

Works like charm on 1.6.1.11. Thank you very much.

 

Also your order status has to have "order confirmed" checked. Otherwise it won't count them.

Edited by danny019 (see edit history)

Share this post


Link to post
Share on other sites

×
×
  • Create New...

Important Information

Cookies ensure the smooth running of our services. Using these, you accept the use of cookies. Learn More