Date range discrepancies between order manager and reports

[KCarlson]

I am sure this has come up before, but I can't seem to find a post for it. In my order manager, if I ask for a date range of 4/19/2010 - 4/19/2010 I get some orders that are from 4/18/2010. If I do a report for 4/19/2010, I get only orders that show 4/19/2010 as the order date. I am sure this must have something to do with the timezone, but I don't understand why one works one way and the other somehow else. Can someone tell me? I thought about setting my server clock to GMT, but that seems like it would then mess up the reports, since they are working correctly.

[mazhar]

There may be one possible issue and that is time part is being considered by the queries. In fact when querying by start and end date time values we should adjust the date time variables to have minimum possible time on start date and maximum possible on end date.

For example before using the start and end dates you should try to do something like

startDate = new DateTime(startDate.Year,startDate.Month,startDate.Day,0,0,0) // start date with 12AM time -- start of the day

endDate = new DateTime(endDate.Year,endDate.Month,endtDate.Day,23,59,59) // end date with 11:59:59 PM time -- end of the day.

Now when queries will use these adjusted values they won't create any problems with respect to time.

[KCarlson]

So do I need to make that modification to the cs code for admin/orders/default.aspx.cs? I just want them to be consistent between that and reports. Plus choosing to show order from 4/19/2010 and then having orders from 4/18/2010 show up at the bottom of the screen is hard to explain to the client.

[mazhar]

Yes you have to make the suggested change in your code behind or page's CS file. Regarding your second question are you asking about sorting on order manager with respect to date. If this is the case then its already there. All you need is to click the Order Date column header to change sort order.

[KCarlson]

OK. Just to be clear, where I need to make the change is actually in your code for the order manager. This is nothing I am customizing. I just want the orders to show for the right date when you pick them in the date search from the starndard Able 7 order manager.

[KCarlson]

Should I report this as a bug instead? It seems like that should not be happening in the order manager. I am not sure I want to alter code in the cs for that admin page, since that might get updated in a later release.

[mazhar]

This is a minor issue and is already under consideration reported some time ago. Please have a look at this thread viewtopic.php?f=42&t=12658

[KCarlson]

Thanks. That reference was helpful. That code doesn't appear now in 7.04 but I see what to do in that cs.

[KCarlson]

I thought this should work, but it does not seem to make any difference.

if (OrderStartDate.SelectedStartDate > DateTime.MinValue)
criteria.OrderDateStart = AlwaysConvert.ToDateTime(OrderStartDate.SelectedStartDate, DateTime.MinValue);
criteria.OrderDateStart = new DateTime(criteria.OrderDateStart.Year, criteria.OrderDateStart.Month, criteria.OrderDateStart.Day, 0, 0, 0);
if (OrderEndDate.SelectedEndDate > DateTime.MinValue && OrderEndDate.SelectedEndDate < DateTime.MaxValue)
criteria.OrderDateEnd = AlwaysConvert.ToDateTime(OrderEndDate.SelectedEndDate, DateTime.MaxValue);
criteria.OrderDateEnd = new DateTime(criteria.OrderDateEnd.Year, criteria.OrderDateEnd.Month, criteria.OrderDateEnd.Day, 23, 59, 59);

[sloDavid]

I was having the same trouble as KCarlson; on a hunch, I checked the DateTime stored in the ac_Orders table of the database. Sure enough, the DateTime stored (which the query runs off of) is the UTC time, not the local server time! BIG mistake, in my opinion, but so be it.

To get this report to run correctly, either
a) AbleCommerce needs to switch to storing the local server time (preferred), or
b) The report needs to run the time zone conversion.

Since we need to get this working without an upgrade, I chose to run the time zone conversion.
In that page's GetOrderSearchCriteria():

Code: Select all

if (OrderStartDate.SelectedStartDate > DateTime.MinValue)
  criteria.OrderDateStart = LocaleHelper.FromLocalTime(OrderStartDate.SelectedStartDate);
if (OrderEndDate.SelectedEndDate > DateTime.MinValue && OrderEndDate.SelectedEndDate < DateTime.MaxValue)
  criteria.OrderDateEnd = LocaleHelper.FromLocalTime(OrderEndDate.SelectedEndDate);

NOTE: You'll have to do the reverse of this at the end of LoadLastSearch():

Code: Select all

if (criteria.OrderDateStart > DateTime.MinValue && criteria.OrderDateStart < DateTime.MaxValue)
  OrderStartDate.SelectedDate = LocaleHelper.ToLocalTime(criteria.OrderDateStart);
if (criteria.OrderDateEnd > DateTime.MinValue && criteria.OrderDateEnd < DateTime.MaxValue)
  OrderEndDate.SelectedDate = LocaleHelper.ToLocalTime(criteria.OrderDateEnd);

[mazhar]

Go to store settings page and set your local time zone. AbleCommerce takes care of your time zone if you have specified one in store settings page.

[sloDavid]

I've already done that. But that's not the time stored in the database -- version 7.0.4 stores the UTC time -- at least my installation does. And, as far as I can tell, this page by default queries based off of whatever's in the database.

[sloDavid]

Re: the solution that I posted above, in 7.0.6 (and possibly later versions), the function GetOrderSearchCriteria() has been changed to GetOrderFilter(), but the effective solution remains the same.