Ejegg has uploaded a new change for review.
https://gerrit.wikimedia.org/r/302290
Change subject: Backfill wmf_contribution_extra
......................................................................
Backfill wmf_contribution_extra
Change-Id: Ide7d8a1db907bbbd8f85820190a0db2615900534
---
A sites/all/modules/wmf_civicrm/scripts/wmf_contribution_extra.sql
1 file changed, 62 insertions(+), 0 deletions(-)
git pull ssh://gerrit.wikimedia.org:29418/wikimedia/fundraising/crm
refs/changes/90/302290/1
diff --git a/sites/all/modules/wmf_civicrm/scripts/wmf_contribution_extra.sql
b/sites/all/modules/wmf_civicrm/scripts/wmf_contribution_extra.sql
new file mode 100644
index 0000000..007dd53
--- /dev/null
+++ b/sites/all/modules/wmf_civicrm/scripts/wmf_contribution_extra.sql
@@ -0,0 +1,62 @@
+CREATE TEMPORARY TABLE missing_extra (
+ entity_id int(10) unsigned PRIMARY KEY,
+ source varchar(255) COLLATE utf8_unicode_ci,
+ trxn_id varchar(255) COLLATE utf8_unicode_ci,
+ total_usd decimal(20,2),
+ gateway varchar(255) COLLATE utf8_unicode_ci,
+ gateway_txn_id varchar(255) COLLATE utf8_unicode_ci,
+ original_amount decimal(20,2),
+ original_currency varchar(255) COLLATE utf8_unicode_ci
+);
+
+INSERT INTO missing_extra ( entity_id, source, trxn_id, total_usd )
+SELECT c.id, source, trxn_id, total_amount
+FROM civicrm_contribution c
+LEFT OUTER JOIN wmf_contribution_extra ex ON ex.entity_id = c.id
+WHERE ex.id IS NULL;
+
+UPDATE missing_extra
+SET
+ original_amount = SUBSTR( source, 4 ),
+ original_currency = UPPER( LEFT( source, 3 ) )
+WHERE source RLIKE '^[a-zA-Z]{3} [0-9.]+$';
+
+UPDATE missing_extra
+SET
+ original_amount = total_usd,
+ original_currency = 'USD'
+WHERE original_currency IS NULL;
+
+UPDATE missing_extra
+SET trxn_id = REPLACE( trxn_id, 'RECURRING ', '' );
+
+UPDATE missing_extra
+SET gateway =
+CASE
+ WHEN trxn_id LIKE 'PAYPAL %' THEN 'paypal'
+ WHEN trxn_id LIKE 'PAYFLOWPRO %' THEN 'payflowpro'
+ WHEN trxn_id LIKE 'GLOBALCOLLECT %' THEN 'globalcollect'
+ WHEN trxn_id RLIKE '^ ?[0-9A-Z]{17} [0-9]{9,10}' THEN 'PAYPAL'
+ELSE 'unknown'
+END;
+
+UPDATE missing_extra
+SET gateway_txn_id =
+CASE
+ WHEN gateway = 'unknown' THEN entity_id
+ WHEN trxn_id rlike '^ [0-9A-Z]{17} [0-9]{9,10}' THEN SUBSTR( trxn_id, 2 )
+ ELSE REPLACE( trxn_id, CONCAT( UPPER( gateway ), ' ' ), '' )
+END;
+
+INSERT INTO wmf_contribution_extra
+ ( entity_id, total_usd, gateway, gateway_txn_id, original_amount,
original_currency, source_name )
+SELECT
+ entity_id,
+ total_usd,
+ gateway,
+ gateway_txn_id,
+ original_amount,
+ original_currency,
+ 'BACKFILL'
+FROM missing_extra;
+
--
To view, visit https://gerrit.wikimedia.org/r/302290
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: newchange
Gerrit-Change-Id: Ide7d8a1db907bbbd8f85820190a0db2615900534
Gerrit-PatchSet: 1
Gerrit-Project: wikimedia/fundraising/crm
Gerrit-Branch: master
Gerrit-Owner: Ejegg <[email protected]>
_______________________________________________
MediaWiki-commits mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
