CRM-19186 Subsequent Installments of Recurring Credit Card Contributions Indicate a Payment Instrument of Check

Hi guys,

Closing this ticket in favor of reporting a new one with additional information that proves my initial report not quite accurate.

Keep your eyes peeled!

Tamar Meir I'm seeing this with Paypal Pro in 4.7.18. If the default Payment Method is 'Check' which is appropriate for most back-office work, then the subsequent recurring Paypal IPN records as Check which in fact Paypal = Credit Card.

Could you review whether the first and/or most recent contributions in series have payment method of check?

The first AND subsequent recurring contributions have a payment method of 'check'. Check is the default payment method in this instance of Civi, but clearly a paypal recurring payment is not check. The version of the site is 4.7.18, but the problem has occurred since 4.7.17. We have not yet updated to 4.7.19 to avoid known bugs in 4.719.

Pradeep, could you schedule working on this mid-June, starting with an attempt to reproduce on dmaster. If unable to reproduce, reach out to Stoob and Tamar.

Stoob or Tamar Meir if you have funding we could likely get this scheduled sooner.

Stoob I believe this has been fixed in 4.7.20 and this is the fix https://github.com/civicrm/civicrm-core/pull/10144 . I tested on my local with test processor against master which is currently pointing to 4.7.22 and ensured that it's been fixed and payment_instrument is set to CC for subsequent recurring payments.

Can you check on latest released Civi 4.7.20 if it's been fixed for other payment processor like Paypal/IATs ?

Monish Deb thank you I have installed 4.7.20 today on production for this site. We will watchas more transactions come in and let you know if it is fixed. I'll get back to you next week.

Monish Deb, an update. While using 4.7.20 recurring PaypalPro transactions are still being assigned a Payment Method of "Check". this is true of the 2nd and subsequent payments in the recurring series. so it appears this patch does not fix the issue

Stoob Monish Deb will try to get this on Friday as he is off tomorrow.

Stoob and Tamar Meir if you want to test then check out the PR over on CRM-20959, it seems to have resolved this issue for us. (The affected site was 4.7.21 so would already have had the fix from PR10144 above.)

However, there's also some cleanup to do. If you check the civicrm_financial_trxn table you may see additional duplicated rows.

Before being affected, the site would record a pair of rows like the top two in this image (one row with $10 donation and showing 0.55 fee, a second row with $0.55 fee). Both would share the same trxn_id.

After this bug kicks in, we see an additional pair of rows (four total), eg 363208-363211 in this image. These negate each other (plus and minus the full value of the transaction) but they also upset how CiviCRM identifies the payment instrument (null and Check). That seems to be where Civi gets the "Check" from.

So, to complete the solution for this issue, we also need to identify and remove those "bad" rows. I'll work on a process to identify and zap them.

Note also that we somehow got a negative transaction value with a positive fee value, suggesting that Civi's method for calculating the transaction value and the fee value generate inconsistent results in some (peculiar) circumstances.

OK, here's an approach:

DROP TABLE IF EXISTS tmp_duplicate_transactions;

CREATE temporary TABLE tmp_duplicate_transactions
  SELECT civicrm_financial_trxn.trxn_id,
         COUNT(id) AS count
  FROM   civicrm_financial_trxn
  WHERE  trxn_id IS NOT NULL
  GROUP  BY trxn_id
  HAVING COUNT(id) > 1
  ORDER  BY COUNT(id) DESC;

DELETE 
FROM   civicrm_financial_trxn
WHERE  ( payment_instrument_id IS NULL
          OR payment_instrument_id = 4 )
       AND trxn_id IN (SELECT trxn_id
                       FROM   tmp_duplicate_transactions
                       WHERE  count = 4)
ORDER  BY id DESC; 

I'd like to understand why payment_instrument_id=4 is special, and this is a bit "voodoo" in that it looks for transactions where there are four entries with the same trxn_id. I thought this ID being duplicated would not be OK, but we also see "fee" rows which duplicate that ID. So I am concerned this might affect "good" data on sites which do complicated accounting things.

OK, here's a cleanup script we can throw into an upgrader function, or which you can run via *cv php:script*

Please read, then give it a whirl and let me know if it does what you'd want ... it will either print out DELETE sql, or actually run the delete if you change the $action variable.

Happy to PR this as updater step if it works on your affected sites.

CRM-19186-trxn_cleanup-USE_THIS_ONE.php

Like it says - use this script to test. First one had a typo, second one defaults to deleting. Be handy if we could remove attachments!

Chris Burgess I've used your script, and it gave me an opportunity to try 'cv' for the first time, thank you very much. I can confirm:

1. The patch works to ensure all recurring contributions are recorded as 'credit card'

2. Your script works to repair/remove the redundant rows in the civicrm_financial_trxn table

I've closed this since the patch was merged. Any clean up script should get it's own ticket IMHO

The merged fix only seems to apply to paypal. I've opened a new issue addressing auth.net:

https://lab.civicrm.org/dev/core/issues/7