List of missing Stripe charges
The snippet can be accessed without any authentication.
Authored by
capo
missing_charges.sql 1.21 KiB
SELECT
rc.entity_id AS `trxn_id`,
from_unixtime( json_extract( rc.data, '$.created' ) ) AS `receive_date`,
cr.id AS contribution_recur_id,
COALESCE( cr.financial_type_id, ft.id ) AS financial_type_id,
json_extract( rc.data, '$.amount' ) / 100 AS `total_amount`,
cu.contact_id
FROM civicrm_striperemote_data AS rc
LEFT JOIN (
SELECT id FROM civicrm_financial_type
WHERE is_active = 1
ORDER BY id
LIMIT 1
) AS ft ON 1=1
LEFT JOIN civicrm_striperemote_data AS ri ON
ri.entity_name = 'Invoice' AND
ri.entity_id = json_unquote( json_extract( rc.data, '$.invoice' ) )
LEFT JOIN civicrm_contribution AS c ON
c.trxn_id = rc.entity_id
LEFT JOIN civicrm_financial_trxn AS p ON
p.trxn_id = rc.entity_id
LEFT JOIN civicrm_stripe_customers AS cu ON
cu.id = json_unquote( json_extract( rc.data, '$.customer' ) )
LEFT JOIN civicrm_contribution_recur AS cr ON
cr.trxn_id = json_unquote( json_extract( ri.data, '$.subscription' ) )
WHERE
rc.entity_name = 'Charge' AND
c.id IS NULL AND p.id IS NULL AND
json_extract( rc.data, '$.paid' ) = true AND
json_unquote( json_extract( rc.data, '$.customer' ) ) <> 'null'
ORDER BY
from_unixtime( json_extract( rc.data, '$.created' ) );
Please register or sign in to comment