jenkins-bot has submitted this change and it was merged. (
https://gerrit.wikimedia.org/r/394406 )
Change subject: Better c_t country backfill
......................................................................
Better c_t country backfill
Filter out all the bad codes, then look in all c_t rows associated
with the email address newest to oldest.
Bug: T181424
Change-Id: I85542c92606aa26f9cb3ba52132d41d28f5802d8
---
M silverpop_export/tests/test_update.py
M silverpop_export/update_table.sql
2 files changed, 83 insertions(+), 15 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 64033dc..6cea3d2 100644
--- a/silverpop_export/tests/test_update.py
+++ b/silverpop_export/tests/test_update.py
@@ -301,6 +301,73 @@
assert cursor.fetchone() == ('PE',)
+def test_good_ct_country():
+ '''
+ Test that we use the contribution_tracking country when no Civi address
exists
+ '''
+
+ 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),
+ (2, 1, '2016-07-07', 10.95, 'nnn777', 1),
+ (3, 1, '2017-05-05', 10.00, 'abc456', 1);
+ """, """
+ insert into wmf_contribution_extra (entity_id, original_amount,
original_currency) values
+ (1, 1000, 'JPY'),
+ (2, 9.00, 'GBP'),
+ (3, 10.00, 'USD');
+ """, """
+ insert into contribution_tracking (contribution_id, country) values
+ (1, 'PE'),
+ (2, 'SE'),
+ (3, 'XX');
+ """, """
+ insert into civicrm_country (id, iso_code) values
+ (1, 'PE'),
+ (2, 'SE');
+ """])
+
+ cursor = conn.db_conn.cursor()
+ cursor.execute("select country from silverpop_export")
+ assert cursor.fetchone() == ('SE',)
+
+
+def test_multiple_ct_country():
+ '''
+ Test that we use the latest valid contribution_tracking country when no
Civi address exists
+ '''
+
+ 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, 'SE');
+ """, """
+ insert into civicrm_country (id, iso_code) values
+ (1, 'SE');
+ """])
+
+ cursor = conn.db_conn.cursor()
+ cursor.execute("select country from silverpop_export")
+ assert cursor.fetchone() == ('SE',)
+
+
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 3a9bf92..5f9c12d 100755
--- a/silverpop_export/update_table.sql
+++ b/silverpop_export/update_table.sql
@@ -240,6 +240,22 @@
ORDER BY a.id DESC
ON DUPLICATE KEY UPDATE email = e.email;
+-- Fill in missing countries from contribution_tracking
+INSERT INTO silverpop_export_address (email, country)
+SELECT e.email, ct.country
+ FROM civicrm.civicrm_email e
+ JOIN silverpop_export_staging ex
+ ON e.email = ex.email
+ JOIN civicrm.civicrm_contribution cc
+ ON cc.contact_id = e.contact_id
+ JOIN drupal.contribution_tracking ct
+ ON ct.contribution_id = cc.id
+ JOIN civicrm.civicrm_country ctry
+ ON ct.country = ctry.iso_code # filter out invalid c_t countries
+ WHERE ex.opted_out = 0
+ ORDER BY cc.id DESC
+ON DUPLICATE KEY UPDATE email = e.email;
+
-- Pull in address and latest/greatest/cumulative stats from intermediate
tables
UPDATE silverpop_export_staging ex
LEFT JOIN silverpop_export_stat exs ON ex.id = exs.exid
@@ -265,21 +281,6 @@
ex.postal_code = addr.postal_code,
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.
--
To view, visit https://gerrit.wikimedia.org/r/394406
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: merged
Gerrit-Change-Id: I85542c92606aa26f9cb3ba52132d41d28f5802d8
Gerrit-PatchSet: 2
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
