Ejegg has submitted this change and it was merged. Change subject: is_deleted is another way to opt out ;) ......................................................................
is_deleted is another way to opt out ;) Add deleted contacts to the unsubscribe list, unless there's an active contact still using the same email address. Bug: T130660 Change-Id: Ia0a5ea1b5e95a0c3d392c388c4100070373386f9 --- M silverpop_export/update_table.sql 1 file changed, 27 insertions(+), 0 deletions(-) Approvals: Ejegg: Verified; Looks good to me, approved Awight: Checked diff --git a/silverpop_export/update_table.sql b/silverpop_export/update_table.sql index 1cbceb1..244992e 100755 --- a/silverpop_export/update_table.sql +++ b/silverpop_export/update_table.sql @@ -274,6 +274,33 @@ WHERE donation_count IS NULL AND opted_out = 0; UPDATE silverpop_export_staging SET country='US' where country IS NULL AND opted_out = 0; +-- Unsubscribe anyone we lost during a merge +DROP TABLE IF EXISTS silverpop_deleted; + +CREATE TABLE IF NOT EXISTS silverpop_deleted( + email_id int unsigned, + contact_id int unsigned, + email varchar(255) +); + +INSERT INTO silverpop_deleted + (email_id, contact_id, email) + SELECT e.id, c.id, e.email + FROM civicrm.civicrm_contact c + JOIN civicrm.civicrm_email e + ON c.id = e.contact_id + GROUP BY + e.email + HAVING + MIN(c.is_deleted) = 1; + +-- Copy remaining is_deleted emails to the export as opted out. +INSERT INTO silverpop_export_staging + (id, contact_id, email, opted_out) + SELECT email_id, contact_id, email, 1 + FROM silverpop_deleted; + +-- Prepare the persistent export table. DROP TABLE IF EXISTS silverpop_export; CREATE TABLE IF NOT EXISTS silverpop_export( -- To view, visit https://gerrit.wikimedia.org/r/280784 To unsubscribe, visit https://gerrit.wikimedia.org/r/settings Gerrit-MessageType: merged Gerrit-Change-Id: Ia0a5ea1b5e95a0c3d392c388c4100070373386f9 Gerrit-PatchSet: 3 Gerrit-Project: wikimedia/fundraising/tools Gerrit-Branch: master Gerrit-Owner: Awight <[email protected]> Gerrit-Reviewer: Awight <[email protected]> Gerrit-Reviewer: Ejegg <[email protected]> Gerrit-Reviewer: Springle <[email protected]> _______________________________________________ MediaWiki-commits mailing list [email protected] https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
