Skip to content
Snippets Groups Projects

List of missing Stripe charges

  • Clone with SSH
  • Clone with HTTPS
  • Embed
  • Share
    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' ) );
    0% Loading or .
    You are about to add 0 people to the discussion. Proceed with caution.
    Finish editing this message first!
    Please register or to comment