Generate refund CSV
ActivePublic
Actions

Authored by Ejegg on Jan 30 2020, 2:05 AM.

Tags

None

Referenced Files

	F31540448: raw.txt
	Jan 30 2020, 7:28 PM

	F31539435: raw.txt
	Jan 30 2020, 2:05 AM

Subscribers

None

	SELECT grp.email,
	cnt_outer.first_name,
	cnt_outer.last_name,
	country_t.iso_code AS country,
	SUBSTRING_INDEX(c_outer.invoice_id, '\|', 1) AS order_id,
	x.gateway_txn_id AS payment_id,
	x.original_currency AS currency,
	x.original_amount AS amount,
	c_outer.receive_date AS date
	FROM (
	SELECT e.email,
	day(receive_date),
	min(c.id) AS first_c_id,
	count(*) AS num_donations
	FROM civicrm_contribution c
	INNER JOIN civicrm_contact cnt ON cnt.id = c.contact_id
	INNER JOIN civicrm_email e ON cnt.id = e.contact_id AND e.is_primary = 1
	WHERE receive_date BETWEEN '2020-01-26' AND '2020-01-28'
	AND contribution_status_id = 1
	AND cnt.contact_type = 'Individual'
	GROUP BY e.email, day(receive_date)
	HAVING count(*) > 1
	) AS grp
	INNER JOIN civicrm_email e_outer ON e_outer.email = grp.email
	INNER JOIN civicrm_contact cnt_outer ON cnt_outer.id = e_outer.contact_id
	INNER JOIN civicrm_contribution c_outer ON c_outer.contact_id = e_outer.contact_id
	INNER JOIN wmf_contribution_extra x ON x.entity_id = c_outer.id
	INNER JOIN civicrm_address addr ON addr.contact_id = c_outer.contact_id
	INNER JOIN civicrm_country country_t ON country_t.id = addr.country_id
	WHERE c_outer.receive_date BETWEEN '2020-01-26' AND '2020-01-28'
	AND c_outer.id > grp.first_c_id
	AND c_outer.contribution_status_id = 1
	AND e_outer.is_primary = 1
	AND x.gateway = 'ingenico'
	ORDER BY grp.email;

Generate refund CSVActivePublicActions