jenkins-bot has submitted this change and it was merged. Change subject: Add highest_native_amount to Silverpop export ......................................................................
Add highest_native_amount to Silverpop export Also highest_native_currency and highest_donation_date. May not be exactly the maximum native currency if USD exchange rate has fluctuated wildly. Bug: T135407 Change-Id: I8471d364fbf0f1cb5c6daf786f916fa94d505bb5 --- M silverpop_export/tests/test_update.py M silverpop_export/update_table.sql 2 files changed, 98 insertions(+), 25 deletions(-) Approvals: Cdentinger: 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 37cfd24..ef17da0 100644 --- a/silverpop_export/tests/test_update.py +++ b/silverpop_export/tests/test_update.py @@ -130,6 +130,36 @@ assert cursor.fetchone() == expected +def test_native_amount(): + ''' + Test that we correctly calculate the highest native amount and currency + ''' + + 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, '2016-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'); + """]) + + cursor = conn.db_conn.cursor() + cursor.execute("select highest_usd_amount, highest_native_amount, highest_native_currency from silverpop_export") + expected = (Decimal('10.95'), Decimal('9'), 'GBP') + actual = cursor.fetchone() + assert actual == expected + + 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 2f7742e..e69215b 100755 --- a/silverpop_export/update_table.sql +++ b/silverpop_export/update_table.sql @@ -9,6 +9,7 @@ DROP TABLE IF EXISTS silverpop_export_staging; DROP TABLE IF EXISTS silverpop_export_latest; +DROP TABLE IF EXISTS silverpop_export_highest; DROP TABLE IF EXISTS silverpop_export_dedupe_email; DROP TABLE IF EXISTS silverpop_export_stat; DROP TABLE IF EXISTS silverpop_export_address; @@ -27,6 +28,8 @@ -- Lifetime contribution statistics has_recurred_donation tinyint(1), highest_usd_amount decimal(20,2), + highest_native_amount decimal(20,2), + highest_native_currency varchar(3), lifetime_usd_total decimal(20,2), donation_count int, @@ -35,6 +38,7 @@ latest_native_amount decimal(20,2), latest_usd_amount decimal(20,2), latest_donation datetime, + highest_donation_date datetime, -- Address information city varchar(128), @@ -116,6 +120,36 @@ 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), + highest_native_amount decimal(20,2), + highest_usd_amount decimal(20,2), + highest_donation_date datetime +) COLLATE 'utf8_unicode_ci'; + +INSERT INTO silverpop_export_highest + SELECT + e.email, + ex.original_currency, + ex.original_amount, + ct.total_amount, + ct.receive_date + FROM + silverpop_export_staging e, + civicrm.civicrm_contribution ct, + civicrm.wmf_contribution_extra ex + WHERE + e.contact_id = ct.contact_id AND + ex.entity_id = ct.id AND + ct.receive_date IS NOT NULL AND + ct.total_amount > 0 AND -- Refunds don't count + ct.contribution_status_id = 1 -- 'Completed' + ORDER BY + ct.total_amount DESC, + ct.receive_date DESC +ON DUPLICATE KEY UPDATE highest_native_currency = silverpop_export_highest.highest_native_currency; + -- Deduplicate rows that have the same email address, we will -- have to merge in more data later, but this is ~1.5M rows we're -- getting rid of here which is more better than taking them all the way @@ -169,23 +203,19 @@ -- Create an aggregate table from a full contribution table scan CREATE TABLE silverpop_export_stat ( - id INT PRIMARY KEY AUTO_INCREMENT, - email varchar(255), - exid INT, -- STEP 5 - max_amount_usd decimal(20,2), -- STEP 5 + email varchar(255) PRIMARY KEY, + exid INT, has_recurred_donation tinyint(1), - total_usd decimal(20,2), -- STEP 5 + total_usd decimal(20,2), cnt_total int unsigned, - - INDEX spexs_email (email) + INDEX stat_exid (exid) ) COLLATE 'utf8_unicode_ci'; --- (30 minutes) + INSERT INTO silverpop_export_stat - (email, exid, max_amount_usd, total_usd, cnt_total, has_recurred_donation) + (email, exid, total_usd, cnt_total, has_recurred_donation) SELECT - e.email, MAX(ex.id), MAX(ct.total_amount), SUM(ct.total_amount), - count(*), + e.email, MAX(ex.id), SUM(ct.total_amount), COUNT(*), MAX(IF(SUBSTRING(ct.trxn_id, 1, 9) = 'RECURRING', 1, 0)) FROM civicrm.civicrm_email e FORCE INDEX(UI_email) JOIN silverpop_export_staging ex ON e.email=ex.email @@ -196,24 +226,26 @@ GROUP BY e.email; -- (10 minutes) -UPDATE silverpop_export_staging ex, silverpop_export_stat exs +UPDATE silverpop_export_staging ex + INNER JOIN silverpop_export_stat exs on ex.id = exs.exid SET - ex.highest_usd_amount = exs.max_amount_usd, ex.lifetime_usd_total = exs.total_usd, ex.donation_count = exs.cnt_total, - ex.has_recurred_donation = IF(exs.has_recurred_donation > 0, 1, 0) - WHERE - ex.id = exs.exid; + ex.has_recurred_donation = exs.has_recurred_donation; --- Populate information about the most recent contribution -UPDATE silverpop_export_staging ex, silverpop_export_latest ct +-- Populate information about the latest and greatest contributions +UPDATE silverpop_export_staging ex, silverpop_export_latest lt, silverpop_export_highest hg SET - ex.latest_currency = ct.latest_currency, - ex.latest_native_amount = ct.latest_native_amount, - ex.latest_usd_amount = ct.latest_usd_amount, - ex.latest_donation = ct.latest_donation + ex.latest_currency = lt.latest_currency, + ex.latest_native_amount = lt.latest_native_amount, + ex.latest_usd_amount = lt.latest_usd_amount, + ex.latest_donation = lt.latest_donation, + ex.highest_native_currency = hg.highest_native_currency, + ex.highest_native_amount = hg.highest_native_amount, + ex.highest_usd_amount = hg.highest_usd_amount, + ex.highest_donation_date = hg.highest_donation_date WHERE - ex.email = ct.email; + ex.email = lt.email AND ex.email=hg.email; -- Postal addresses by email CREATE TABLE silverpop_export_address ( @@ -275,6 +307,9 @@ latest_native_amount = 0, latest_usd_amount = 0, latest_donation = NOW(), + highest_native_currency = 'USD', + highest_native_amount = 0, + highest_donation_date = NOW(), has_recurred_donation = 0 WHERE donation_count IS NULL AND opted_out = 0; UPDATE silverpop_export_staging SET country='US' where country IS NULL AND opted_out = 0; @@ -331,6 +366,9 @@ -- Lifetime contribution statistics has_recurred_donation tinyint(1), highest_usd_amount decimal(20,2), + highest_native_amount decimal(20,2), + highest_native_currency varchar(3), + highest_donation_date datetime, lifetime_usd_total decimal(20,2), donation_count int, @@ -359,11 +397,13 @@ -- (12 minutes) INSERT INTO silverpop_export ( id,contact_id,first_name,last_name,preferred_language,email, - has_recurred_donation,highest_usd_amount,lifetime_usd_total,donation_count, + has_recurred_donation,highest_usd_amount,highest_native_amount, + highest_native_currency,highest_donation_date,lifetime_usd_total,donation_count, latest_currency,latest_native_amount,latest_usd_amount,latest_donation, city,country,state,postal_code,timezone ) SELECT id,contact_id,first_name,last_name,preferred_language,email, - has_recurred_donation,highest_usd_amount,lifetime_usd_total,donation_count, + has_recurred_donation,highest_usd_amount,highest_native_amount, + highest_native_currency,highest_donation_date,lifetime_usd_total,donation_count, latest_currency,latest_native_amount,latest_usd_amount,latest_donation, city,country,state,postal_code,timezone FROM silverpop_export_staging @@ -383,6 +423,9 @@ SUBSTRING(preferred_language, 1, 2) IsoLang, IF(has_recurred_donation, 'YES', 'NO') has_recurred_donation, highest_usd_amount, + highest_native_amount, + highest_native_currency, + DATE_FORMAT(highest_donation_date, '%m/%d/%Y') highest_donation_date, lifetime_usd_total, DATE_FORMAT(latest_donation, '%m/%d/%Y') latest_donation_date, latest_usd_amount, -- To view, visit https://gerrit.wikimedia.org/r/318360 To unsubscribe, visit https://gerrit.wikimedia.org/r/settings Gerrit-MessageType: merged Gerrit-Change-Id: I8471d364fbf0f1cb5c6daf786f916fa94d505bb5 Gerrit-PatchSet: 7 Gerrit-Project: wikimedia/fundraising/tools Gerrit-Branch: master Gerrit-Owner: Ejegg <eeggles...@wikimedia.org> Gerrit-Reviewer: Awight <awi...@wikimedia.org> Gerrit-Reviewer: Cdentinger <cdentin...@wikimedia.org> Gerrit-Reviewer: Eileen <emcnaugh...@wikimedia.org> Gerrit-Reviewer: Ejegg <eeggles...@wikimedia.org> Gerrit-Reviewer: jenkins-bot <> _______________________________________________ MediaWiki-commits mailing list MediaWiki-commits@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits