Find donors with recur fail mails and more than one recurring
ActivePublic
Actions

Authored by Ejegg on Dec 12 2020, 1:01 AM.

Tags

Referenced Files

	F33945539: Find donors with recur fail mails and more than one recurring
	Dec 14 2020, 2:44 PM

	F33943295: Find donors with recur fail mails and more than one recurring
	Dec 12 2020, 1:01 AM

Subscribers

None

	create temporary table ejegg.recurfails as (
	select contact_id, count(*) as fail_email_count
	from civicrm_activity_contact ac
	inner join civicrm_activity a on ac.activity_id = a.id
	where activity_date_time BETWEEN '2020-11-24' AND '2020-12-10'
	and activity_type_id=3
	and record_type_id=3
	and subject like 'Recur fail %'
	group by contact_id
	);

	CREATE table ejegg.T269372 AS (
	select
	rf.contact_id,
	e.email,
	c.first_name,
	c.last_name,
	c.preferred_language,
	rf.fail_email_count,
	count(cr3.id) as cancelled,
	count(cr5.id) as inprogress,
	count(cr15.id) as failing,
	max(cr5.create_date) as last_signup
	from ejegg.recurfails rf
	left join civicrm.civicrm_contribution_recur cr3 on rf.contact_id = cr3.contact_id and cr3.contribution_status_id=3 and cr3.cancel_date > '2020-11-24'
	left join civicrm.civicrm_contribution_recur cr5 on rf.contact_id=cr5.contact_id and cr5.contribution_status_id=5
	left join civicrm.civicrm_contribution_recur cr15 on rf.contact_id=cr15.contact_id and cr15.contribution_status_id=15
	left join civicrm.civicrm_email e on rf.contact_id = e.contact_id and e.is_primary=1
	left join civicrm.civicrm_contact c on rf.contact_id = c.id
	group by rf.contact_id, e.email, c.first_name, c.last_name, c.preferred_language
	);

	select * from ejegg.T269372 where last_signup > '2020-11-24' and inprogress > 1;

Find donors with recur fail mails and more than one recurringActivePublicActions