"Sales summary" report. User time of dates

[RomanovM]

1. NOP Commerce version: 4.50.2

2. Expected behaviour: The numbers of orders listed in the two reports should match

3. Actual behaviour: Sales > Orders and Reports reflects server time, whereas Reports > Sales Summary uses UTC time (and also includes a line for the day before the selected start date if there were orders when the order date/time UTC was before midnight and server time was the next day.

Example:

In the UK we are now on summer time (UTC+1 hour) which is also the server time.

The CreatedOnUtc time on the Order table is UTC(+0) as expected.

Sales > Orders shows the summer time against each order, and when filtering on the Start and End dates, will include orders based on summer time.

However, Reports > Sales Summary uses the UTC time.

Therefore, when there are orders placed between UTC 23:00:00 and 23:59:59, there is a discrepancy in order numbers between the above two facilities because the orders are included/counted on different days.

In addition, in Reports > Sales Summary, when selecting a date range, if on the day before the "Start date" there were orders placed between UTC 23:00:00 and 23:59:59, the report will include a line for that date including the totals only for any late-night orders, when that date was not required.

E.g. in Reports > Sales Summary I ran a report with date range 20/06/2022 to 22/06/2022, but it also shows 4 orders on 19/06/2022 (all 4 were raised UTC 23:00:00-23:59:59 on that date - it has not counted other orders from earlier that day, so that day's figures are also incorrect).

Reports > Sales Summary should base its results on the server time as the orders page does this.

NB. In System > System Information, our "Server time zone" is "GMT Standard Time", and the "Server local time" and "Current user time" correctly reflects this (i.e. UTC+1), and "Coordinated Universal Time (UTC)" correctly shows the UTC time. In Configuration > Settings > Customer Settings, we are on UTC+00:00

Source: https://www.nopcommerce.com/boards/topic/94524/discrepancy-between-order-numbers-in-sales-orders-and-reports-sales-summary

On the order list page: get user date → convert to UTC date → get data from db by UTC date → convert data date to user date → display data. This smoothly works since the date is only used here for display.

In reports: get user date → convert to UTC date → use UTC date in db query (grouping, calculations, etc) → get data from db → display data. In this case, the data is returned and displayed without reverse conversion of the date.

So, should we change queries and use user dates or leave it as is and maybe show a warning that reports are generated for UTC dates?

[RomanovM]

See also: https://www.nopcommerce.com/boards/topic/94609/reports-sales-summary-order-of-rows-in-report-incorrect-and-date-formatting-not-system-format

[AndreiMaz]

@exileDev Also check https://www.nopcommerce.com/en/boards/topic/94703/sales-totals-in-4404-am-i-missing-something

[exileDev]

Closed #6296