e-mail reminders for events not send

[fm5000]

Hi,

the e-mail reminder for an event will not be sent.

I've 2 entries in the table calendar_event and also 2 entries in calendar_event_reminder (with email_sent = 0).
The cron/calendar_reminders.php runs but sends no e-mail; the sqlstatement (line 54) returns 2 records.

The debug_msg (line 87) returns: Sent e-mail reminder for event: to
{$row['event_name']} and {$row['user_email']} are empty

Is there something I've forgotten to configure?

Regards,
Frank

[mkoch227]

Couple questions before I start digging into this:

1. What is your current PHP version?
2. Does the cron job set the email_sent flag to 1 after the cron job runs? Or does it stay at 0?

[fm5000]

Hi.

to 1: PHP Version => 5.4.45-0+deb7u2
to 2: the value of email_sent on each record stays 0

Regards,
Frank

[fm5000]

any findings?

Regards,
Frank

[mkoch227]

In cron/calendar_reminders.php, find:

Code: Select all

$rs = hesk_dbQuery($sql);
$reminders_to_flag = array();
$tickets_to_flag = array();

Just before that, add this:

Code: Select all

mfh_log_info($LOCATION, $sql, 'CRON');

Run the cron job once, and then go to "View Message Log" in HESK. The SQL script generated should be there. Check and see if the two rows are still returned (the script is supposed to generate the correct date/time based on your installation's time zone and "offset" value stored in HESK, but I wonder if it's not working exactly as it should be).

[fm5000]

hi,

I've found

Code: Select all

$rs = hesk_dbQuery($sql);
$reminders_to_flag = [];
$tickets_to_flag = [];

starting at row 69

and not your code from the message above. Should I change this snippet to your code?

Code: Select all

...[]; -> ...array();

Regards,
Frank

[mkoch227]

You're using PHP 5.4, so you don't need to change the arrays from [] to array() ([] was added in PHP 5.4). They both do the same thing.

[fm5000]

the log returns following entries:

Code: Select all

13.07.2016 15:10:01 CRON Calendar Reminders Cron Job SELECT `reminder`.`id` AS `reminder_id`, `reminder`.`user_id` AS `user_id`, `reminder`.`event_id` AS `event_id`, `event`.`name` AS `event_name`, `event`.`location` AS `event_location`, `event`.`comments` AS `event_comments`, `category`.`name` AS `event_category`, `event`.`start` AS `event_start`, `event`.`end` AS `event_end`, `event`.`all_day` AS `event_all_day`, `user`.`language` AS `user_language`, `user`.`email` AS `user_email`, CASE WHEN `unit` = '0' THEN DATE_SUB(`event`.`start`, INTERVAL `reminder`.`amount` MINUTE) WHEN `unit` = '1' THEN DATE_SUB(`event`.`start`, INTERVAL `reminder`.`amount` HOUR) WHEN `unit` = '2' THEN DATE_SUB(`event`.`start`, INTERVAL `reminder`.`amount` DAY) WHEN `unit` = '3' THEN DATE_SUB(`event`.`start`, INTERVAL `reminder`.`amount` WEEK) END AS `reminder_date`, 'EVENT' AS `type` FROM `hesk_calendar_event_reminder` AS `reminder` INNER JOIN `hesk_calendar_event` AS `event` ON `reminder`.`event_id` = `event`.`id` INNER JOIN `hesk_categories` AS `category` ON `event`.`category` = `category`.`id` INNER JOIN `hesk_users` AS `user` ON `reminder`.`user_id` = `user`.`id` WHERE (CASE WHEN `unit` = '0' THEN DATE_SUB(`event`.`start`, INTERVAL `reminder`.`amount` MINUTE) WHEN `unit` = '1' THEN DATE_SUB(`event`.`start`, INTERVAL `reminder`.`amount` HOUR) WHEN `unit` = '2' THEN DATE_SUB(`event`.`start`, INTERVAL `reminder`.`amount` DAY) WHEN `unit` = '3' THEN DATE_SUB(`event`.`start`, INTERVAL `reminder`.`amount` WEEK) END) <= '2016-07-13 15:10:01' AND `email_sent` = '0' 

Code: Select all

13.07.2016 15:10:01 CRON Calendar Reminders Cron Job Finished Calendar Reminders. 0 reminder e-mails sent. 0 emails failed to send.  

Code: Select all

13.07.2016 15:10:01 CRON Calendar Reminders Cron Job Finished Overdue Tickets. 0 e-mails sent. 0 emails failed to send.  

The sql-statement returns 2 records, but there is no eMail in my inbox.

Regards,
Frank

[mkoch227]

Since the logs say that 0 were sent and 0 failed to send, it sounds like you're getting different results from the SQL query than the cron job is. Try adding this log:

Find the following line:

Code: Select all

while ($row = hesk_dbFetchAssoc($rs) && !$skip_events) {

Just before that, add this:

Code: Select all

$number_of_rows = hesk_dbNumRows($rs);
mfh_log_info($LOCATION, 'Number of rows returned from query: ' . $number_of_rows, 'CRON');
mfh_log_info($LOCATION, 'Are we skipping events? ' . $skip_events, 'CRON');

I'm curious if the cron job sees 2 or 0 results. Also make sure that the calendar module is enabled in your HESK installation, otherwise calendar reminder emails will be skipped.

[fm5000]

...

Find the following line:

Code: Select all

while ($row = hesk_dbFetchAssoc($rs) && !$skip_events) {

...

there is no line with this code in calendar_reminders.php

Regards,
Frank

[mkoch227]

It should be around line 81 (see https://gitlab.com/mike-koch/Mods-for-H ... rs.php#L81). If it is not there for some reason, I recommend you re-download the calendar_reminders.php file and replace it with your current version

[fm5000]

my mistake. I've found it in line 81

the log returns:

Code: Select all

15.07.2016 15:10:01 CRON Calendar Reminders Cron Job Number of rows returned from query: 2 
15.07.2016 15:10:01 CRON Calendar Reminders Cron Job Are we skipping events? 1 
15.07.2016 15:10:01 CRON Calendar Reminders Cron Job Finished Calendar Reminders. 0 reminder e-mails sent. 0 emails failed to send.  
15.07.2016 15:10:01 CRON Calendar Reminders Cron Job Finished Overdue Tickets. 0 e-mails sent. 0 emails failed to send.  

Regards,
Frank

[mkoch227]

Do you have the calendar module disabled in your HESK install? The log indicates that it should skip sending emails for events because the calendar is disabled. Double-check that the calendar is enabled in your helpdesk settings (as long as one of the "YES" choices are set, the cron job should not skip sending out the event emails).

[fm5000]

Hi,

the calendar option is active (YES to all). The result of the CRON is:

Code: Select all

22.07.2016 13:50:01 CRON Calendar Reminders Cron Job Number of rows returned from query: 2 
22.07.2016 13:50:01 CRON Calendar Reminders Cron Job Are we skipping events?  
22.07.2016 13:50:01 CRON Calendar Reminders Cron Job Sent e-mail reminder for event: to  
22.07.2016 13:50:01 CRON Calendar Reminders Cron Job Sent e-mail reminder for event: to  
22.07.2016 13:50:01 CRON Calendar Reminders Cron Job Finished Calendar Reminders. 2 reminder e-mails sent. 0 emails failed to send.  
22.07.2016 13:50:01 CRON Calendar Reminders Cron Job Finished Overdue Tickets. 0 e-mails sent. 0 emails failed to send.  

Regards,
Frank

[mkoch227]

Ok I finally figured out the issue. Since the first loop had "!$skip_events", the dbFetchAssoc function call returned a boolean instead of the database row. I'm working on a 2.6.3 release right now that will contain a fixed version of the cron job.