Page MenuHomePhabricator
Paste P10296

(An Untitled Masterwork)
ActivePublic

Authored by jgleeson on Sat, Feb 1, 1:35 AM.
SELECT
SUBSTRING_INDEX(c_outer.invoice_id, '|', 1) AS order_id,
x.gateway_txn_id AS payment_id,
cpt.token AS subscription_id,
x.original_currency AS currency,
x.original_amount AS amount
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
INNER JOIN
civicrm_contribution_recur cr ON cr.id = c_outer.contribution_recur_id
INNER JOIN
civicrm_payment_token cpt ON cpt.id = cr.payment_token_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 LIKE '%paypal%'
ORDER BY grp.email;