Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Tokens aren't working on CiviCRM v. 5.0.1 and higher #18

Open
yosefromano opened this issue Jul 26, 2018 · 11 comments
Open

Tokens aren't working on CiviCRM v. 5.0.1 and higher #18

yosefromano opened this issue Jul 26, 2018 · 11 comments

Comments

@yosefromano
Copy link

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

@sgladstone
Copy link
Owner

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.

@sgladstone
Copy link
Owner

sgladstone commented Aug 2, 2018

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 sgladstone changed the title Tokens aren't working Tokens aren't working on CiviCRM v. 5.0.1 and higher Aug 3, 2018
@guyoron
Copy link
Contributor

guyoron commented Aug 7, 2018

@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!

@yosefromano
Copy link
Author

@sgladstone Can you please help?

@sgladstone
Copy link
Owner

sgladstone commented Aug 21, 2018

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.

@sgladstone
Copy link
Owner

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.

@gzh1960
Copy link

gzh1960 commented Aug 22, 2018

Hi Yosef,

  1. 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.
  2. 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

@sgladstone
Copy link
Owner

sgladstone commented Aug 23, 2018

@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

@gzh1960
Copy link

gzh1960 commented Aug 23, 2018

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:

  1. Run custom search using exactly 14 days and active Yahrzeit group
  2. Click all records (2 rows returned today) then Print/Merge Document under Actions
  3. Select a template with the token in it
  4. 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

@sgladstone
Copy link
Owner

Can you try version 4.0.4 and see if this is still occuring?

https://github.com/sgladstone/com.fountaintribe.hebrewcalendarhelper/releases/latest

@yosefromano
Copy link
Author

@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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

4 participants