Backfill missing wmf_contribution_extra
ActivePublic
Actions

Authored by Ejegg on Jul 29 2016, 6:13 PM.

Tags

Referenced Files

	F4318800: Backfill missing wmf_contribution_extra
	Jul 29 2016, 6:57 PM

	F4318796: Backfill missing wmf_contribution_extra
	Jul 29 2016, 6:55 PM

	F4318729: Backfill missing wmf_contribution_extra
	Jul 29 2016, 6:17 PM

	F4318722: Backfill missing wmf_contribution_extra
	Jul 29 2016, 6:13 PM

Subscribers

None

	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 SUBSTRING(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;

Event Timeline

Ejegg created this paste.Jul 29 2016, 6:13 PM

Ejegg edited the content of this paste. (Show Details)Jul 29 2016, 6:17 PM

Ejegg mentioned this in T139102: Major Donors on email suppression list who should be opted in.Jul 29 2016, 6:20 PM

Ejegg edited the content of this paste. (Show Details)Jul 29 2016, 6:55 PM

Ejegg edited the content of this paste. (Show Details)

Salgo60 mentioned this in T236769: Create a Shape Expressions (ShEx) for Swedish Parliament members in Wikidata.Oct 29 2019, 10:15 AM

Backfill missing wmf_contribution_extraActivePublicActions

Event Timeline

Backfill missing wmf_contribution_extra
ActivePublic
Actions