jenkins-bot has submitted this change and it was merged. (
https://gerrit.wikimedia.org/r/393816 )
Change subject: Prefer Civi address to contribution_tracking for country
......................................................................
Prefer Civi address to contribution_tracking for country
Also, always use the latest, whether or not it has a postal_code.
Finally, default unknown countries to 'XX' not 'US'.
Bug: T181424
Change-Id: Iec7d2d4f9cf575c379c952a3c10ebac4c5f8c2a3
---
M silverpop_export/tests/test_update.py
M silverpop_export/update_table.sql
2 files changed, 55 insertions(+), 31 deletions(-)
Approvals:
XenoRyet: Looks good to me, approved
jenkins-bot: Verified
diff --git a/silverpop_export/tests/test_update.py
b/silverpop_export/tests/test_update.py
index 8db6bca..64033dc 100644
--- a/silverpop_export/tests/test_update.py
+++ b/silverpop_export/tests/test_update.py
@@ -268,6 +268,39 @@
assert cursor.fetchone() == ('abfe829234baa87s76d',)
+def test_bad_ct_country():
+ '''
+ Test that we use the Civi address in place of XX contribution_tracking
+ '''
+
+ run_update_with_fixtures(fixture_queries=["""
+ insert into civicrm_email (contact_id, email, is_primary, on_hold) values
+ (1, 'person1@localhost', 1, 0);
+ """, """
+ insert into civicrm_contact (id) values
+ (1);
+ """, """
+ insert into civicrm_contribution (id, contact_id, receive_date,
total_amount, trxn_id, contribution_status_id) values
+ (1, 1, '2015-01-03', 9.50, 'xyz123', 1);
+ """, """
+ insert into wmf_contribution_extra (entity_id, original_amount,
original_currency) values
+ (1, 1000, 'JPY');
+ """, """
+ insert into contribution_tracking (contribution_id, country) values
+ (1, 'XX');
+ """, """
+ insert into civicrm_country (id, iso_code) values
+ (1, 'PE');
+ """, """
+ insert into civicrm_address (contact_id, is_primary, country_id) values
+ (1, 1, 1);
+ """])
+
+ cursor = conn.db_conn.cursor()
+ cursor.execute("select country from silverpop_export")
+ assert cursor.fetchone() == ('PE',)
+
+
def run_update_with_fixtures(fixture_path=None, fixture_queries=None):
with mock.patch("database.db.Connection") as MockConnection:
diff --git a/silverpop_export/update_table.sql
b/silverpop_export/update_table.sql
index 4ca34ae..3a9bf92 100755
--- a/silverpop_export/update_table.sql
+++ b/silverpop_export/update_table.sql
@@ -115,18 +115,6 @@
ct.total_amount DESC
ON DUPLICATE KEY UPDATE latest_currency =
silverpop_export_latest.latest_currency;
--- (15 minutes)
-UPDATE
- silverpop_export_staging ex,
- civicrm.civicrm_contribution ct,
- drupal.contribution_tracking dct
- SET
- ex.country = dct.country
- WHERE
- ex.contact_id = ct.contact_id AND
- dct.contribution_id = ct.id AND
- dct.country IS NOT NULL;
-
CREATE TABLE silverpop_export_highest(
email varchar(255) PRIMARY KEY,
highest_native_currency varchar(3),
@@ -166,7 +154,6 @@
email varchar(255),
maxid int,
preferred_language varchar(12),
- country varchar(2),
opted_out tinyint(1),
INDEX spexde_email (email)
@@ -179,21 +166,14 @@
GROUP BY email
HAVING count(*) > 1;
--- We pull in language/country from the parent table so that we
--- can preserve them and not propogate nulls
+-- We pull in language from the parent table so that we
+-- can preserve it and not propagate nulls
UPDATE silverpop_export_dedupe_email exde, silverpop_export_staging ex
SET
exde.preferred_language = ex.preferred_language
WHERE
ex.email = exde.email AND
ex.preferred_language IS NOT NULL;
-
-UPDATE silverpop_export_dedupe_email exde, silverpop_export_staging ex
- SET
- exde.country = ex.country
- WHERE
- ex.email = exde.email AND
- ex.country IS NOT NULL;
DELETE silverpop_export_staging FROM silverpop_export_staging,
silverpop_export_dedupe_email
WHERE
@@ -203,8 +183,7 @@
UPDATE silverpop_export_staging ex, silverpop_export_dedupe_email exde
SET
ex.opted_out = exde.opted_out,
- ex.preferred_language = exde.preferred_language,
- ex.country = exde.country
+ ex.preferred_language = exde.preferred_language
WHERE
exde.maxid = ex.id;
@@ -245,8 +224,7 @@
) COLLATE 'utf8_unicode_ci';
-- (16 minutes)
--- Get address for each email matching latest contribution_tracking country.
--- Prefer more complete information.
+-- Get latest address for each email.
INSERT INTO silverpop_export_address
SELECT e.email, a.city, ctry.iso_code, st.name, a.postal_code, a.timezone
FROM civicrm.civicrm_email e
@@ -259,8 +237,7 @@
LEFT JOIN civicrm.civicrm_state_province st
ON a.state_province_id = st.id
WHERE ex.opted_out = 0
- AND (ex.country IS NULL OR ex.country = ctry.iso_code)
- ORDER BY isnull(a.postal_code) ASC, a.id DESC
+ ORDER BY a.id DESC
ON DUPLICATE KEY UPDATE email = e.email;
-- Pull in address and latest/greatest/cumulative stats from intermediate
tables
@@ -289,6 +266,21 @@
ex.state = addr.state,
ex.timezone = addr.timezone;
+-- Fill in missing addresses from contribution_tracking
+-- (15 minutes)
+UPDATE
+ silverpop_export_staging ex,
+ civicrm.civicrm_contribution ct,
+ drupal.contribution_tracking dct
+ SET
+ ex.country = dct.country
+ WHERE
+ ex.country IS NULL AND
+ ex.contact_id = ct.contact_id AND
+ dct.contribution_id = ct.id AND
+ dct.country IS NOT NULL AND
+ ex.opted_out = 0;
+
-- Reconstruct the donors likely language from their country if it
-- exists from a table of major language to country.
UPDATE silverpop_export_staging ex, silverpop_countrylangs cl
@@ -299,9 +291,8 @@
ex.country = cl.country AND
ex.opted_out = 0;
--- Normalize the data prior to final export
+-- Still no language? Default 'em to English
UPDATE silverpop_export_staging SET preferred_language='en' WHERE
preferred_language IS NULL;
-UPDATE silverpop_export_staging SET country='US' where country IS NULL AND
opted_out = 0;
--
-- Collect email addresses which should be excluded for various reasons, such
as:
@@ -409,7 +400,7 @@
email,
IFNULL(first_name, '') firstname,
IFNULL(last_name, '') lastname,
- country,
+ IFNULL(country, 'XX') country,
state,
postal_code,
timezone,
--
To view, visit https://gerrit.wikimedia.org/r/393816
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: merged
Gerrit-Change-Id: Iec7d2d4f9cf575c379c952a3c10ebac4c5f8c2a3
Gerrit-PatchSet: 3
Gerrit-Project: wikimedia/fundraising/tools
Gerrit-Branch: master
Gerrit-Owner: Ejegg <[email protected]>
Gerrit-Reviewer: XenoRyet <[email protected]>
Gerrit-Reviewer: jenkins-bot <>
_______________________________________________
MediaWiki-commits mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
