I have a task where I need to clean up payment instrument data. The basic problem was caused by a custom payment instrument of TBD, which is somehow preventing contributions from updating correctly, leaving the to_financial_account static and thus leaving contributions as unpaid. So I was given what seemed to be a straight forward task of purging these TBD's from the database and just replacing them as Check. However, I am having problems correctly updating the civicrm_contribution.payment_instrument_id when there are multiple transactions. I'll walk through what I'm trying to do with some screenshots and show the SQL statements I am using, including the one I would like help in fixing.

Here is my test case:

enter image description here

I need to change the two bottom TBD's to Check (financial_trxn.payment_instrument_id), while still having the contribution.payment_instrument_id reflect the most recent financial_trxn.payment_instrument_id. Which in this case is Credit Card. I realize that they all have the same time stamp, what I am really mean by most recent is the greatest civicrm_financial_trxn.id for that contribution, which I will go into more detail about soon.

Here are my two queries:

Query 1: (update financial_trxn.payment_instrument_id, aka purge TBD)

UPDATE civicrm_financial_trxn
SET civicrm_financial_trxn.payment_instrument_id = 
        WHEN (civicrm_financial_trxn.card_type_id IN (1,2,3,4)) THEN 1
        WHEN ((civicrm_financial_trxn.check_number IS NOT NULL) OR (civicrm_financial_trxn.check_number !='')) THEN 4
        WHEN civicrm_financial_trxn.payment_instrument_id = 6 THEN 4
        ElSE civicrm_financial_trxn.payment_instrument_id
WHERE civicrm_financial_trxn.payment_instrument_id IS NOT NULL

This query basically looks at all civicrm_financial_trxn where I might need to update the payment_instrument_id. If there is a credit card type or a check number, then assign the instrument id for check or credit card. If there is a TBD (id 6), assign it as a Check (id 4). Otherwise, leave it be. From what I can tell, this query works, but things go obviously wrong in the next query.

Query 2: Update contribution payment instrument based on the latest financial trxn payment instrument

UPDATE civicrm_contribution
INNER JOIN civicrm_contact ON civicrm_contact.id = civicrm_contribution.contact_id 
LEFT JOIN civicrm_entity_financial_trxn ON (civicrm_contribution.id = civicrm_entity_financial_trxn.entity_id AND
          civicrm_entity_financial_trxn.entity_table = 'civicrm_contribution')
LEFT JOIN civicrm_financial_trxn ON   

MAX(civicrm_financial_trxn.id) FROM civicrm_financial_trxn 
) =
SET civicrm_contribution.payment_instrument_id =
        WHEN (civicrm_financial_trxn.payment_instrument_id = 1) THEN 1
        WHEN (civicrm_financial_trxn.payment_instrument_id = 2) THEN 2
        WHEN (civicrm_financial_trxn.payment_instrument_id = 3) THEN 3
        WHEN (civicrm_financial_trxn.payment_instrument_id = 4) THEN 4
        WHEN (civicrm_financial_trxn.payment_instrument_id = 5) THEN 5
        WHEN (civicrm_financial_trxn.payment_instrument_id = 6) THEN 4
        WHEN (civicrm_financial_trxn.payment_instrument_id = 7) THEN 7
        WHEN (civicrm_financial_trxn.payment_instrument_id = 8) THEN 8
WHERE civicrm_financial_trxn.payment_instrument_id IS NOT NULL  

In this query I want to update the contribution.payment_instrument_id to match the most recent financial_trxn.payment_instrument_id

When running the two queries back to back, I get this result though:

enter image description here

The problem is that the contribution payment method switched to check, when it should be Credit Card (last financial trxn). Query 2 works when there is a 1 to 1 relationship between financial_trxn.payment_instrument_id and contribution.payment_instrument_id. In this case there are three cases to one, so I am trying to select the most recent financial transaction in the query by using MAX(civicrm_financial_trxn.id) FROM civicrm_financial_trxn in a subquery.

Here is a shot of what I mean by wanting to update based on the greatest financial_trxn.id

enter image description here

The query I used to create that view by the way is:

SELECT civicrm_contact.id, civicrm_contribution.id, civicrm_contact.sort_name, civicrm_contribution.payment_instrument_id AS cont_payment_instrument, 
civicrm_financial_trxn.card_type_id, civicrm_financial_trxn.check_number,
civicrm_financial_trxn.payment_instrument_id AS ft_payment_instrument,civicrm_financial_trxn.id,
civicrm_financial_trxn.trxn_id, civicrm_entity_financial_trxn.amount
FROM civicrm_financial_trxn
INNER JOIN civicrm_entity_financial_trxn ON civicrm_financial_trxn.id = civicrm_entity_financial_trxn.financial_trxn_id
LEFT JOIN civicrm_contribution ON (civicrm_entity_financial_trxn.entity_id = civicrm_contribution.id AND
          civicrm_entity_financial_trxn.entity_table = 'civicrm_contribution')
LEFT JOIN civicrm_contact ON civicrm_contribution.contact_id = civicrm_contact.id
WHERE civicrm_contact.id = 1

Any thoughts on how to fix the second query, or perhaps an idea for a different solution?

  • Not trying to understand it all but in your second query, are you sure you don't need a join between civicrm_financial_trxn to civicrm_entity_financial_trxn ? i.e. civicrm_entity_financial_trxn.financial_trxn_id = civicrm_financial_trxn.id – samuelsov Apr 25 '19 at 19:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.