Some problems with (scheduled) reports

[Guido1982]

I'm experiencing some problems with reports:

• No matter which type in choose in the sending of scheduled reports, when I re-edit the report it always defaults to 'URL'. I checked the database, the save happens correctly but if you don't pay attention when editing a report your scheduled report will default to URL and send out that way.
  
• Scheduled reports don't use the same filtering as the reports in the application. For instance, we have a report that filters on the statusses for PurchaseOrders (they may NOT be those two statusses). We filter on the translated statusses, which works in the application. When we receive the scheduled report however, we receive many many more purchaseorders, including the ones that have the statusses we filtered out.
  
• We always have to set the time two hours early (one hour during the winter). Even though I set the time zone for the user correctly (UTC +02:00 in august). Where does the scheduled report system get the time it needs to send its reports? From the user or from some global value? I checked the date command through SSH, it displayed the correct time so server time is correct.
  

[joebordes]

1.- Fixed Thanks!!

2.- We fixed this issue on vtigercrm and it was one of the first things we added to coreBOS. I just tried it on my test install with a date between filter and the conditions were applied correclty to the report in the email. You say that you are fltering on picklists. Picklist filters are really special and hard to get right. They are based on the default language of the user creating the report and the one executing it. This is because the values show translated but may be saved in some other language. So maybe you issue is with that. Lorida just finished a feature to get the picklist searches right. It still needs a few tweeks but it is working really well. I accepted the changes a few days ago, so you may want to try that and see how it works out.

3.- The time is calculated here. Since there is no other processing I understand that it is based on the $default_timezone variable in config.inc.php. Be careful chaning this variable as it will affect all the dates in the application, especially the calendar.

[Guido1982]

1. Nice!
   
2. I did fix this issue by filtering on the original English values as well. But this gets complicated when a 'normal' user creates a report and doesn't know this, or when you add picklist values, these are saved in the language you enter them in. I could check the database for the user who created a report, but there is a 99.9% chance all users have their language set to Dutch. As for the 'executing user', what context is that? When is the 'execution'? When the report is generated to send as an e-mail attachment? Who will that user be? And will that user's language be used? As I said, basically all our users have Dutch as their language, but still the values the scheduled report filters on are the original English ones. I'm still not sure what's going wrong here.
   
3. I just issued a php -r "echo date('Y-m-d H : i : s');" from the command line, that gave me a time one hour earlier than it actually is. But since this is a standard date command from php, I don't see how the $default_timezone could have an effect on that. What's even more weird, reports get sent two hours late, but the CLI gives me a one hour difference. Is there some time-offset I could set in PHP? And how would that keep winter and summertime (we have that here) into account? I'm not too smart on how dates and times are handled...
   

[joebordes]

wow, a lot of questions!

• check out the latest changes about picklist search, it should be fixed.
  
• As for the 'executing user', what context is that?  =>  getActiveAdminUser
  
• When is the 'execution'? When the report is generated to send as an e-mail attachment?  =>  Correct, it is generated at the indicated time, when the cron launches
  
• Who will that user be?  =>  getActiveAdminUser
  
• And will that user's language be used?  =>  Yes, the report should use that user's language
  
• As I said, basically all our users have Dutch as their language, but still the values the scheduled report filters on are the original English ones.  =>  The system used to try to translate the values back to their internal values, it tries to work it out but gets a lot of false positives. With Lorida's change, this should work a lot better if not totally correct
  
• I just issued a php -r "echo date('Y-m-d H : i : s');" from the command line, that gave me a time one hour earlier than it actually is. But since this is a standard date command from php, I don't see how the $default_timezone could have an effect on that.  =>  In config.inc.php we launch date_default_timezone_set   that command effectively changes the timezone for the whole lifetime of the execution, in other words, every execution of coreBOS runs in the $default_timezone
  
• dates and times are a REALLY complex issue, there is a lot of literature about it and the procedures to follow are well defined, but there are steps that need to be taken in the proper order to get it working
  

[Guido1982]

I don't understand a lot of this so there are many questions that come to mind.

I should dig into getActiveAdminUser then. I always thought that function returned the first user (lowest ID) that is admin and active, is that correct? So basically with the new change, we should have a better chance of getting the correct scheduled report using only the translated picklist values? That would be cool.

So I could use Europe/Amsterdam in the $default_timezone? We are UTC+1 in winter and UTC+2 in summer so if UTC (which $default_timezone is now) is used by scheduled reports that would explain the 2 hour difference. But would setting the timezone to Europe/Amsterdam take the summer/winter time into account?

[joebordes]

getActiveAdminUser => yes, correct

Yes, correct, I think we solved the picklist translation search issue

PHP takes care of DST for you, that works correctly as long as your server's timezone information is up to date. As I said before, be careful, all your current calendar events are saved in using the default_timezone you have configured, you cannot just change that in the config.inc.php and expect all the dates to magically update to the new timezone.

[Guido1982]

Well apart from my Schedular module no calendar events have been saved at all. The calendar is not being used. If I remember correctly we've been having issues with times in all sorts of ways. I mean, it won't mess up anything in the database I guess. To me it makes sense to set the timezone to the one the system is actuelly being used. Let me try it and get back to you.

[Guido1982]

Just testing some things on my development install:

PHP Code:

php -r "@date_default_timezone_set('Europe/Amsterdam');echo date('d-m-Y H:i:m');" 


On the command line gives me the correct time (I am in Holland).

PHP Code:

php -r "@date_default_timezone_set('UTC');echo date('d-m-Y H:i:m');" 


Gives me a time two hours early.

But I still don't get this. All records I create still have a time set two hours early. I changes the timezone in both config.inc.php and even in php.ini.
EDIT
My bad, didn't set the timezone for the user there.