Ejegg has uploaded a new change for review. (
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, 46 insertions(+), 15 deletions(-)
git pull ssh://gerrit.wikimedia.org:29418/wikimedia/fundraising/tools
refs/changes/06/394406/1
diff --git a/silverpop_export/tests/test_update.py
b/silverpop_export/tests/test_update.py
index 64033dc..7151394 100644
--- a/silverpop_export/tests/test_update.py
+++ b/silverpop_export/tests/test_update.py
@@ -301,6 +301,36 @@
assert cursor.fetchone() == ('PE',)
+def test_good_ct_country():
+ '''
+ Test that we use the contribution_tracking country when no Civi address
+ '''
+
+ 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..f6f8a20 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 ct.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: newchange
Gerrit-Change-Id: I85542c92606aa26f9cb3ba52132d41d28f5802d8
Gerrit-PatchSet: 1
Gerrit-Project: wikimedia/fundraising/tools
Gerrit-Branch: master
Gerrit-Owner: Ejegg <[email protected]>
_______________________________________________
MediaWiki-commits mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
