Awight has uploaded a new change for review.
https://gerrit.wikimedia.org/r/282106
Change subject: [WIP] Unsubscribe old emails; fix is_primary bug
......................................................................
[WIP] Unsubscribe old emails; fix is_primary bug
Pull addresses from the logging table and unsubscribe.
Note that the is_primary code was wrong, it would have failed to unsubscribe
an address which was primary for a deleted contact, but secondary for all
active contacts.
FIXME:
* Need to use the production logging database name.
Bug: T122411
Change-Id: I37b1f5dbd660fa6ecc0b9f1227b12fbf6f555c88
---
M database/db.py
M silverpop_export/export.py
M silverpop_export/update_table.sql
3 files changed, 20 insertions(+), 21 deletions(-)
git pull ssh://gerrit.wikimedia.org:29418/wikimedia/fundraising/tools
refs/changes/06/282106/1
diff --git a/database/db.py b/database/db.py
index 159bb9f..2009890 100644
--- a/database/db.py
+++ b/database/db.py
@@ -1,5 +1,5 @@
'''
-Mysql wrapper which allows query composition
+Mysql wrapper providing query composition
'''
import MySQLdb as Dbi
import atexit
diff --git a/silverpop_export/export.py b/silverpop_export/export.py
index cd3ab48..b02c21a 100644
--- a/silverpop_export/export.py
+++ b/silverpop_export/export.py
@@ -88,10 +88,9 @@
log.info("Starting unsubscribe data export")
exportq = DbQuery()
- exportq.tables.append('silverpop_export')
+ exportq.tables.append('silverpop_excluded')
exportq.columns.append('contact_id')
exportq.columns.append('email')
- exportq.where.append('opted_out=1')
run_export_query(
db=db,
query=exportq,
diff --git a/silverpop_export/update_table.sql
b/silverpop_export/update_table.sql
index 08c443f..e90ba91 100755
--- a/silverpop_export/update_table.sql
+++ b/silverpop_export/update_table.sql
@@ -278,33 +278,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;
--- Exclude non-primary addresses, or anyone whose old email address was deleted
--- during a merge.
+--
+-- Collect email addresses which should be excluded for various reasons, such
as:
+-- * Exclude non-primary addresses
+-- * Exclude any "former residence" email addresses.
+-- * Exclude addresses dropped during contact merge.
+--
DROP TABLE IF EXISTS silverpop_excluded;
CREATE TABLE IF NOT EXISTS silverpop_excluded(
- email_id int unsigned,
contact_id int unsigned,
email varchar(255)
);
+-- Grab logged old addresses and exclude. FIXME: We take a long shortcut and
+-- include all current addresses as well, because we'll have to subtract the
+-- active addresses either way.
INSERT INTO silverpop_excluded
- (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
- OR MAX(e.is_primary) = 0;
+ SELECT contact_id, email
+-- XXX logging
+ FROM civicrm.log_civicrm_email e;
--- Copy remaining excluded email addresses 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_excluded;
+DELETE silverpop_excluded
+ FROM silverpop_excluded
+ JOIN silverpop_export_staging s
+ ON s.email = silverpop_excluded.email
+ WHERE
+ s.opted_out = 0;
-- Prepare the persistent export table.
DROP TABLE IF EXISTS silverpop_export;
--
To view, visit https://gerrit.wikimedia.org/r/282106
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: newchange
Gerrit-Change-Id: I37b1f5dbd660fa6ecc0b9f1227b12fbf6f555c88
Gerrit-PatchSet: 1
Gerrit-Project: wikimedia/fundraising/tools
Gerrit-Branch: master
Gerrit-Owner: Awight <[email protected]>
_______________________________________________
MediaWiki-commits mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
