Page MenuHomePhabricator
Paste P10262

T243356 Generate CSV for batch refund script
ActivePublic

Authored by AndyRussG on Jan 24 2020, 5:23 PM.
SELECT
grp.*,
SUBSTRING_INDEX(c.invoice_id, '|', 1) AS order_id,
e.gateway_txn_id AS payment_id,
e.original_currency AS currency,
e.original_amount AS amount,
c.receive_date AS date
FROM (
SELECT
c.contribution_recur_id AS recur_id,
e.gateway AS gateway,
contact.first_name AS first_name,
contact.last_name AS last_name,
email.email AS email_addr,
country_t.iso_code AS country,
MIN(c.id) as min_c_id,
count(*) AS ccount
FROM
civicrm_contribution AS c
LEFT JOIN (
wmf_contribution_extra AS e,
civicrm_address AS addr,
civicrm_email AS email,
civicrm_contact AS contact )
ON
c.id = e.entity_id AND
c.contact_id = addr.contact_id AND
c.contact_id = contact.id AND
c.contact_id = email.contact_id
LEFT JOIN
civicrm_country AS country_t
ON
addr.country_id = country_t.id
WHERE
c.contribution_recur_id > 0 AND
c.receive_date > '2020-01-01' AND
c.contribution_status_id = 1 AND
e.gateway = 'ingenico' AND
addr.is_primary = 1 AND
email.is_primary = 1
GROUP BY
c.contribution_recur_id,
c.contact_id,
e.gateway,
contact.first_name,
contact.last_name,
email.email,
country_t.iso_code
HAVING ccount > 1 ) AS grp
LEFT JOIN (
civicrm_contribution AS c,
wmf_contribution_extra AS e )
ON (
grp.min_c_id = c.id AND
grp.min_c_id = e.entity_id );