Report Statistic Date & Order actual date difference

  • Posts: 332
  • Thank you received: 7
  • Hikamarket Multivendor
7 months 1 day ago #284504

-- HikaShop version -- : 3.2.1
-- Joomla version -- : 3.8.2
-- PHP version -- : 5.4.45

Hi

We found some abnormalities after upgraded to the latest version

Our Global Configuration are using MYT time zone as well as the server Date is MYT

All the order date are display and created nicely

For example, currently we have 1 confirmed order on 15/12 and 14 confirmed order on 14/12

However, when we run the sales this month report or the dashboard report

System capture 7 sales on 15/12 instead of 1 sales

Is it some reconfiguration of us ? we also notice that there is an invoice_created date in order sql table, is it the cause?

Please Log in or Create an account to join the conversation.

  • Posts: 11626
  • Thank you received: 1612
  • MODERATOR
7 months 10 hours ago #284523

Hello,

Checking the value of your "Server Time Zone" Joomla option via "Global Configuration" -> tab "Server" -> section "Location Settings" will probably help.
If it still not working you should check your server configuration.

Please Log in or Create an account to join the conversation.

  • Posts: 332
  • Thank you received: 7
  • Hikamarket Multivendor
6 months 3 weeks ago #284706

May i know what to check and can you specified which is the thing possible to go wrong?

As mentioned everything is correct
Our Global Configuration are using MYT time zone as well as the server Date is MYT

Please Log in or Create an account to join the conversation.

  • Posts: 332
  • Thank you received: 7
  • Hikamarket Multivendor
6 months 3 weeks ago #284707

I would like to highlight, this problem only occur after ugprade to 3.2.1

date on Order listing (administrator) page is correct, however, dashboard and report can not calculate sales bases on the date

Please Log in or Create an account to join the conversation.

  • Posts: 62706
  • Thank you received: 8985
  • MODERATOR
6 months 3 weeks ago #284715

Hi,

Do you have the "dashboard legacy" setting activated or deactivated in the HikaShop configuration ?
If you change that setting, does it change anything for the data on the dashboard ?
Also, which version of HikaShop were you using before updating ?
I've been looking in our SVN for changes to the reports system regarding the dates and I've been up to 2 years and a half in the past and didn't see any change which would affect the date restrictions.

Also, how did you configure the report? Could you provide a screenshot ? Could you also tell us how you configure your timezone in your Joomla configuration and in your user account in Joomla ?

Please Log in or Create an account to join the conversation.

  • Posts: 332
  • Thank you received: 7
  • Hikamarket Multivendor
6 months 3 weeks ago #284781

Both legacy and non legacy dashboard have this problem

we upgrade it from 2.6.5

Let me provide a screen shot for better understanding

Joomla & Server timezone are using +8:00 Kuala Lumpur/Singapore

As you can see in screenshot, there is a big difference of amount between two report, we do not know which is correct when the transaction is big

Attachments:
Last Edit: 6 months 3 weeks ago by ler@singmuiheng.com.

Please Log in or Create an account to join the conversation.

  • Posts: 62706
  • Thank you received: 8985
  • MODERATOR
6 months 3 weeks ago #284783

Hi,

Well, you're not comparing the same data on both screenshots.
On the report, you have many order statuses selected but on the new dashboard, only the "statistics" statuses from the System>Order statuses menu are taken into account. Also, if the "created" order status is enabled as a "statistics" status, then it's the order creation date of orders that is used for the dashboard, and if not, it's the invoice creation date of orders that is used, while in your report, you're using the "last modified" date which is again another date.
The order creation date is the date the order was created in the shop.
The invoice creation date is the date the order was paid.
The last modified date is the date the last time something was changed in th order.
Normally, a customer will pay the order directly after it is created and you won't edit the order afterwards, so all these dates would be almost identical. But it's possible that the order was created two weeks ago, paid last week and that you modified it yesterday.

So because of these, it's totally possible to get results way off between the reports and the dashboard because you're not necessarily comparing the same things and it's highly likely that both are correct for what they are trying to display.

Please Log in or Create an account to join the conversation.

  • Posts: 332
  • Thank you received: 7
  • Hikamarket Multivendor
6 months 3 weeks ago #284865

now i understand

is it possible to let dashboard report to use creation date instead of invoice date?

Please Log in or Create an account to join the conversation.

  • Posts: 332
  • Thank you received: 7
  • Hikamarket Multivendor
6 months 3 weeks ago #284866

i still found problem, i had changed the statistic with created status , here is the screen shot for reference

Attachments:

Please Log in or Create an account to join the conversation.

  • Posts: 62706
  • Thank you received: 8985
  • MODERATOR
6 months 3 weeks ago #284868

Hi,

Well, first if you want the same data as on the reports, you might want to deactivate the "dashboard legacy" setting of the HikaShop configuration.

Otherwise, regarding the new dashboard, as I said in my previous message, if you enable the "created" status in the "statistic" column of the menu System>Order statuses, it will be the order creation date that will be used.

Please Log in or Create an account to join the conversation.

  • Posts: 332
  • Thank you received: 7
  • Hikamarket Multivendor
6 months 3 weeks ago #284901

Dear Nicolas

We had already checked the created status as per in the screen shot in the statistic (you can see it from the screen shot)

However, the data for the order list, report, dashboard are still different (you can see from the screen shot, dash board record the last on the next day date which is not even logic with the order or invoice date)

Last Edit: 6 months 3 weeks ago by ler@singmuiheng.com.

Please Log in or Create an account to join the conversation.

  • Posts: 22519
  • Thank you received: 3509
  • MODERATOR
6 months 3 weeks ago #284970

Hello,

The processing of the date (based on the timestamp) is made directly by MySQL ; so it is possible that you have some timezone in your PHP and that your MySQL do not have the same timezone.
So when MySQL convert the timestamp into a date, due to the different timezone, the result date is different.

And please note that we had to do that task by MySQL because it then aggregate the values depending the date ; it provides better performances (memory and CPU) that getting all the data and process it using PHP.
Regarding the Joomla timezone, it is handle by the statistic system, which add that information in the SQL query

'DATE_FORMAT(FROM_UNIXTIME(CAST(hk_order.order_invoice_created AS SIGNED)'.$this->timeoffsetStr.'),\'{DATE_FORMAT}\') as axis';

Regards,


Jerome - Obsidev.com
HikaMarket & HikaSerial developer / HikaShop core dev team.

Also helping the HikaShop support team when having some time or couldn't sleep.
By the way, do not send me private message, use the "contact us" form instead.

Please Log in or Create an account to join the conversation.

  • Posts: 332
  • Thank you received: 7
  • Hikamarket Multivendor
6 months 2 weeks ago #285036

date.timezone = Asia/Kuala_Lumpur
[root@shutter3 ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1189698
Server version: 5.5.48 MySQL Community Server (GPL) by Remi

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select now();
+


+
| now() |
+
+
| 2017-12-27 08:17:43 |
+
+
1 row in set (0.01 sec)


Both the MYSQL and PHP are in same timezone, Joomla time for all other components are correct, only hikashop provide different timing.....as per earlier screen shot

Please Log in or Create an account to join the conversation.

  • Posts: 62706
  • Thank you received: 8985
  • MODERATOR
6 months 2 weeks ago #285048

Hi,

Well, we don't have the problem on our end.
So we need a concrete example to be able to understand the situation on your end.
Please pick an order and in your phpmyadmin, open the table hikashop_order and provide the order_invoice_created and the order_created values that you have for that order.
Then, run that query:
SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP);
and provide the result. It should display the offset from UTC that is used by your MySQL.
Also, after the line:
$this->timeoffsetStr = '+' . $this->timeoffset ;
in the file administrator/components/com_hikashop/classes/statistics.php pleas add the line:
var_dump($this->timeoffsetStr);
and access your dashboard. You should then see the offset (in seconds) that is used by HikaShop and coming from the configuration of your Joomla at the top of the statistics area. Please provide that value.

Please Log in or Create an account to join the conversation.

  • Posts: 332
  • Thank you received: 7
  • Hikamarket Multivendor
6 months 2 weeks ago #285082

SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP) -> result -> 08:00:00

var_dump result -> string(6) "+28800"

server timezone in Joomla - > Kuala Lumpur

For the order_created & invoice created in the screen shot order list
SF05273 - 1513840307 - 0
SF05274 - 1513841158 - 1514009010
SF05275 - 1513842841 - 1513911458
SF05276 - 1513858081 - 0

Please Log in or Create an account to join the conversation.

  • Posts: 62706
  • Thank you received: 8985
  • MODERATOR
6 months 2 weeks ago #285093

Hi,

Thanks.
So for the order SF05273, the timestamp 1513840307 corresponds to 2017-12-21 07:11:47 UTC ( www.unixtimestamp.com/index.php )
The code of the statistics system is:

'DATE_FORMAT(FROM_UNIXTIME(CAST(hk_order.order_invoice_created AS SIGNED)'.$this->timeoffsetStr.'),\'{DATE_FORMAT}\') as axis';
So that means that the system interprets that 1513840307 with something like that:
SELECT DATE_FORMAT(FROM_UNIXTIME(CAST(1513840307 AS SIGNED)+28800), '%Y-%m-%d %T')
On my end, this MySQL query results in: 2017-12-21 16:11:47

So this means that there is the timezone shift of Joomla plus the one from mysql (UTC+1) as it should normally output 2017-12-21 15:11:47
So let's try this:
Add the code:
$this->db->setQuery('SET @@session.time_zone = \'+00:00\'');
$this->db->query();
before the line:
$this->db->setQuery($query, $offset, $limit);
in the file administrator/components/com_hikashop/classes/statistics.php
That should remove the timezone issue.

Please Log in or Create an account to join the conversation.

Time to create page: 0.119 seconds
Powered by Kunena Forum