Tokens aren't working on CiviCRM v. 5.0.1 and higher
Opened this issue · 11 comments
Tokens that are set to only print Yahrtzeit "in x days" are not respecting the "in x days" filter and the token prints information from every available Yahrzeit relationship.
I tested on Civi 5.0.1 and 5.3.1
I will need to do a schema compare on the civicrm_relationship MySQL table, to determine what changed, and in which version of CiviCRM. That way I know which version to check on inside this extension.
Can you look at the data in the MySQL table "civicrm_fountaintribe_yahrzeits_temp" ? Does any of the data there seem invalid? Also, there is a CiviCRM scheduled job that will refresh all the records in that table, please rerun that scheduled job using the CiviCRM Admin area for scheduled jobs.
@sgladstone we've narrowed down the issue here. It's not that all Yahrzeit relationships are displayed by the tokens. When using the exactly 7 days token for a contact, if that contact observes a Yahrzeit in exactly 7 days and also Yahrzeits that fall 1 day before or 1-5 days after the date exactly 7 days from today, the tokens show all of those Yahrzeits.
Could you see if you can reproduce and maybe track this down or give us a direction to look? Thanks!
@sgladstone Can you please help?
In the file "/utils/HebrewCalendar.php" at line 3254, there is a PHP comment that looks like:
// CRM_Core_Error::debug("SQL: ".$yahrzeit_sql, "");
Please uncomment this out, then paste the what you are seeing for "$yahrzeit_sql" here.
I am not able to reproduce this yet, I think it may have to do with your MySQL settings. Once you get the SQL for "$yahrzeit_sql" as mentioned in the previous comment, also try running that SQL directly against your database, such as via PhpMyAdmin.
Hi Yosef,
- Can you describe the exact steps you are referring to when this issue occurs please?
I have run the Calculate job manually and then the custom search using the 'In Exactly 7 Days' and it works fine. My environment is Civicrm 5.1.2 and PHP 7.1 Wordpress 3.9.8. - What is your environment version details please?
I have also used a Mourners Group 'Active Yahrzeits' with the from and to dates of 22-AUG-2018 to 30-AUG-2018 and it returns 3 records which is correct. When I use the Mourners Group 'Active Yahrzeits' and 'In Exactly 7 Days' it displays 1 record currently which is correct as of today(22-AUG-2018).
Regards
Garry
@yosefromano when I create yahrzeit PDF letters using the following template with the 7 day tokens:
Dear {contact.display_name},
A yahrzeit is approaching for {yahrzeit.deceased_name___day_7}
on {yahrzeit.english_date___day_7}
The following SQL statement is used to get the token data:
SELECT mourner_contact_id as contact_id,
mourner_contact_id as id, mourner_name as sort_name, deceased_name as deceased_name,
deceased_contact_table.display_name as deceased_display_name, deceased_contact_id,
date_format( yahrzeit_date , '%M %e, %Y' ) as yahrzeit_date_display,
date_format( deceased_contact_table.deceased_date , '%M %e, %Y' ) as deceased_date,
yahrzeit_date, yahrzeit_hebrew_date_format_english, yahrzeit_hebrew_date_format_hebrew,
date_format( yahrzeit_date_morning , '%M %e, %Y' ) as yahrzeit_date_morning ,
date_format( yahrzeit_erev_shabbat_before, '%M %e, %Y' ) as yah_erev_shabbat_before ,
date_format( yahrzeit_shabbat_morning_before, '%M %e, %Y' ) as yah_shabbat_morning_before,
date_format( yahrzeit_erev_shabbat_after, '%M %e, %Y' ) as yah_erev_shabbat_after ,
date_format( yahrzeit_shabbat_morning_after, '%M %e, %Y' ) as yah_shabbat_morning_after,
contact_b.deceased_date as ddate,
d_before_sunset, hebrew_deceased_date,
concat( year(yahrzeit_date), '-', month(yahrzeit_date), '-', day(yahrzeit_date)) as yahrzeit_date_sort ,
relationship_name_formatted,
shabbat_before_parashat,
shabbat_after_parashat,
shabbat_after_holiday , shabbat_after_holiday_hebrew,
shabbat_before_holiday , shabbat_before_holiday_hebrew,
yahrzeit_type, mourner_observance_preference
FROM civicrm_fountaintribe_yahrzeits_temp contact_b
INNER JOIN civicrm_contact contact_a ON contact_a.id = contact_b.mourner_contact_id
JOIN civicrm_contact deceased_contact_table ON deceased_contact_table.id = contact_b.deceased_contact_id
WHERE contact_b.created_date >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR) AND (yahrzeit_type = mourner_observance_preference)
AND contact_b.mourner_contact_id in ( 56 ) AND yahrzeit_date >= CURDATE()
AND date(yahrzeit_date) = DATE( CURDATE() + INTERVAL 7 DAY ) ORDER BY sort_name asc
Sarah,
The query you posted I ran with a contact_id =2643 and it returned a record as expected. In my case I had to use AND date(yahrzeit_date) = DATE( CURDATE() + INTERVAL 14 DAY ) ORDER BY sort_name asc
as I didn't have data for the 7 day period. Same logic though!
However, when I place the token called {yahrzeit.deceased_name___day_14} in a template and send a test email the token value is empty in the resulting email. I tried the same token creating a PDF and it resolved the value correctly. Looks like it has something to do with how the token gets resolved in the email production. Sarah can you look further into this part of the code. If you need something sing out.
Steps:
- Run custom search using exactly 14 days and active Yahrzeit group
- Click all records (2 rows returned today) then Print/Merge Document under Actions
- Select a template with the token in it
- create PDF and it is correct.
Same steps as above but select 'Send Now (50 or less)' in Step2 above and select template in email with the token in it. Click send and the resulting email token value is empty.
Regards
Garry
Can you try version 4.0.4 and see if this is still occuring?
https://github.com/sgladstone/com.fountaintribe.hebrewcalendarhelper/releases/latest
@sgladstone we figured out what was happening.
We were testing a template that included tokens for "in exactly 7 days" and other tokens that included "everything this month".
Turns out because the "everything this month" token was there, Civi was assuming that all tokens are for "everything this month".
Turns out that the time frame of the tokens need to be consistent or else it breaks the tokens.