Best Customers Statistics - wrong data when order entered in backoffice

[Scully]

When an order is entered by backoffice (customer calls by phone instead of directly entering an order), the best customers statistics does not show this order at all. The reason is found in the join of the connections table.

The order given by phone does not imperatively result in a connection in the given time range.

 

Prestashop 1.5.6 in use. What we did to fix: change statsbestcustomers.php around line 163.

 

original file:

        LEFT JOIN `'._DB_PREFIX_.'connections` co ON g.`id_guest` = co.`id_guest`
        WHERE co.date_add BETWEEN '.$this->getDate()
            .Shop::addSqlRestriction(Shop::SHARE_CUSTOMER, 'c').
        'GROUP BY c.`id_customer`, c.`lastname`, c.`firstname`, c.`email`';

 

modification:

        LEFT JOIN `'._DB_PREFIX_.'connections` co ON g.`id_guest` = co.`id_guest`
        WHERE ( co.date_add BETWEEN '.$this->getDate()
            .Shop::addSqlRestriction(Shop::SHARE_CUSTOMER, 'c').' or co.date_add is NULL )
        GROUP BY c.`id_customer`, c.`lastname`, c.`firstname`, c.`email`';

 

Any better solution is appreciated.

Thank you and best regards, Scully

 

www.lofthome.ch/wirshop/

Edited April 16, 2014 by Scully (see edit history)

[wakabayashi]

Was this never changed in the release? My best customer statistics is completly wrong, because of this.

[KevinNash]

Hello, thank you very much for the tip, works nice on PS 1.5.4.1

 

I purge my shop connections monthly so my best customers stats was a mess before your fix

[Scully]

Thanks for this feedback. Always good prestashopping. Scully

[yay]

Thanks for the tip, seems to work on 1.6 as well!

[Scully]

Thanks for your feedback.

For anybody whos is interested in:

SQL alias "co" refers to table connections. If a customer did not enter an order by himself, he would never show up in the statistics, since there is no connection for the respective orders.

[wakabayashi]

Thanks Scully!

 

Reported a half year on forge. Still no change: http://forge.prestashop.com/browse/NM-204?filter=-2

 

Thats why I stop reporting something there. Where is the sense always to report new stuff and to see that obvious bugs aren't eliminated which were reported a half year ago?

[Docki]

Hello,

 

I have prestashop 1.5.6.1 and it seems that it registers the orders made by phone. 

I have updated the statsbestcustomers.php nevertheless.

There are still different numbers in the stats best customers - order numbers and the customer account, where it says how many orders he has made.

 

Regards!