Jump to content

Dashboard stats counting only confirmed orders

Masteries

By Masteries
in General topics

 Share

Recommended Posts

Scully Newbie

Scully

Posted
Posted (edited)

No, it's not a bug. This is standard behavior from PrestaShop and also the same on some other statistics than the one in your dashboard. A change would be doable but not within a second. The data comes from a dash module which internally calls the following controller:

AdminStatsController.php

There in you find a function getTotalSales with the following SQL:

        if ($granularity == 'day') {
            $sales = array();
            $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('
            SELECT LEFT(`invoice_date`, 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 `invoice_date` 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(`invoice_date`, 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(`invoice_date`, 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 `invoice_date` 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(`invoice_date`, 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 `invoice_date` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1
            '.Shop::addSqlRestriction(false, 'o'));
        }

As you can see, PrestaShop uses the invoice_date for the SQL where clause. Invoice date is not set until an order is put in to a corresponding state. This field must be changed to o.date_add which is the creation date. Other changes might be needed as well. Nothing tested but only investigated according existing code.

 

Furthermore the selects would count cancelled orders as well. So it could be necessary to skip them.

Edited by Scully (see edit history)
  • Like 1
  • Thanks 1
Link to comment
Share on other sites
Masteries Newbie

Masteries

Posted
  • Author
Posted (edited)

No, it's not a bug. This is standard behavior from PrestaShop and also the same on some other statistics than the one in your dashboard. A change would be doable but not within a second. The data comes from a dash module which internally calls the following controller:

AdminStatsController.php

There in you find a function getTotalSales with the following SQL:

        if ($granularity == 'day') {
            $sales = array();
            $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('
            SELECT LEFT(`invoice_date`, 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 `invoice_date` 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(`invoice_date`, 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(`invoice_date`, 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 `invoice_date` 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(`invoice_date`, 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 `invoice_date` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1
            '.Shop::addSqlRestriction(false, 'o'));
        }

As you can see, PrestaShop uses the invoice_date for the SQL where clause. Invoice date is not set until an order is put in to a corresponding state. This field must be changed to o.date_add which is the creation date. Other changes might be needed as well. Nothing tested but only investigated according existing code.

 

Furthermore the selects would count cancelled orders as well. So it could be necessary to skip them.

 

Woah, thanks for the detailed response. The old shop was using 1.6.0.6 and the stats difference in 1.6.1.16 is pretty huge. For example, the same date filter shows 50+ orders in 1.6.0.6 while it's 20+ in 1.6.1.16 (also the sales amount). I checked the code in the old shop and it's pretty much the same thing:

	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(`invoice_date`, 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 `invoice_date` 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(`invoice_date`, 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(`invoice_date`, 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 `invoice_date` 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(`invoice_date`, 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 `invoice_date` BETWEEN "'.pSQL($date_from).' 00:00:00" AND "'.pSQL($date_to).' 23:59:59" AND os.logable = 1
			'.Shop::addSqlRestriction(false, 'o'));
	}

I used Beyond Compare 4 to find some differences between AdminStatsController.php from both versions and I found functions such as get8020SalesCatalog & getProductAverageGrossMargin have some. So, there probably is a different solution than changing the field name. I've attached AdminStatsController.php from both versions if you'd like to take a look.

AdminStatsController (1.6.0.6).php

AdminStatsController(1.6.1.16).php

Edited by Masteries (see edit history)
Link to comment
Share on other sites
Masteries Newbie

Masteries

Posted
  • Author
Posted (edited)

Sorry but I am too busy to make a detailled code check. Are we discussing the dashtrends or some other figures?

Maybe post a screenshot.

 

Here you go. Ignore the difference between the visitor stats as I did not move ps_connections over to the new shop from the old one. As you can see the gap between the two stats is huge even if I moved all the tables with the _order prefix (in accordance with NemoPS's list of essential tables: https://www.prestashop.com/forums/topic/623806-need-a-list-of-essential-database-tables-related-to-orders-products-customers/?p=2597152)

 

Quote:

all the ones that start with, after the prefix:
 
address
attribute
category
customer
feature
order
product
Edited by Masteries (see edit history)
Link to comment
Share on other sites
Masteries Newbie

Masteries

Posted
  • Author
Posted

Sorry but I am too busy to make a detailled code check. Are we discussing the dashtrends or some other figures?

Maybe post a screenshot.

 

I tried copying over AdminStatsController from the old shop to the new one, removed class_index.php and cleared cache. This did not make any changes. So perhaps the issue is with the database?

Link to comment
Share on other sites
Scully Newbie

Scully

Posted
Posted

We are probably discussing a complete other issue than mentionned in your title. Run this quiery on both databases and tell us the results:

SELECT LEFT(`invoice_date`, 7) as date, SUM(total_paid_tax_excl / o.conversion_rate) as sales
            FROM `ps_orders` o
            LEFT JOIN `ps_order_state` os ON o.current_state = os.id_order_state
            WHERE `invoice_date` BETWEEN "2017-08-01 00:00:00" AND "2017-08-31 23:59:59" AND os.logable = 1
            GROUP BY LEFT(`invoice_date`, 7);

Furthermore check your order states in terms of logable flag.

  • Thanks 1
Link to comment
Share on other sites
Masteries Newbie

Masteries

Posted
  • Author
Posted

We are probably discussing a complete other issue than mentionned in your title. Run this quiery on both databases and tell us the results:

SELECT LEFT(`invoice_date`, 7) as date, SUM(total_paid_tax_excl / o.conversion_rate) as sales
            FROM `ps_orders` o
            LEFT JOIN `ps_order_state` os ON o.current_state = os.id_order_state
            WHERE `invoice_date` BETWEEN "2017-08-01 00:00:00" AND "2017-08-31 23:59:59" AND os.logable = 1
            GROUP BY LEFT(`invoice_date`, 7);

Furthermore check your order states in terms of logable flag.

 

I ran your query on both databases and got the same result: 

date    sales
2017-08 116500.000000

This is weird. In the screenshots I attached, you can definitely see that the stats (in the old one) is counting all orders and in the new one, only confirmed orders. To be sure of what I'm saying is correct, I manually counted all the confirmed orders for that specific date/period and it's indeed 20 (which is shown in the stats in the new shop).

Link to comment
Share on other sites
Masteries Newbie

Masteries

Posted
  • Author
Posted

So everything is fine. New version does correct counting and summing of the orders. I have never used your old ps version but according to what I see, all from v 1.6.10 and later is okay.

 

Welp, I guess this is indeed just how it counts in the new version. Thanks for your help!

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share
Go to topic listing
×
×
  • Create New...