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:
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 = (CASE 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 END) 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 (SELECT MAX(civicrm_financial_trxn.id) FROM civicrm_financial_trxn ) = civicrm_entity_financial_trxn.financial_trxn_id SET civicrm_contribution.payment_instrument_id = (CASE 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 END) 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
When running the two queries back to back, I get this result though:
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
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
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?