Editing event fees, and handling of processor fees in 4.5.1

[jakecivi]

I have a 4.1 (pre-price-set) module that allows overriding an event fee by changing the corresponding field in the participant table. Now all that does (and really all it ever did without some javascript to help out) is change how the field displays in search results. Now, in price-set-using versions of Civi (4.2 and above; I'm working with 4.5) it seems to work if I change the unit_price and line_total in the line_item table. But then I wonder what do do about the corresponding entry in the financial_item table. Do I need to change amount on that (things seem to appear fine in the participant view if I don't)?

It also seems that if I do this and then add a payment for an event in the past for which the contribution table's fee_amount is non-zero, then two different financial_trxn's are recorded: one for the amount of the fee that I added (and, for the record, I added it as cash but it transferred to the payment processor account, although cash's financial account is set to "deposit bank account"), and then an extraneous transaction transferring the amount in fee_amount from the payment processor account to the bank fees account. This should not have happened because I entered it as cash.

On a side note, it seems that, in addition to line_items being recorded in the financial_items table, financial_trxn's are too, but only when they relate to processor fees, not to the event fees themselves. What is the reason for this?

[jakecivi]

Another note: It seems that the calculation of "total paid" in 4.5.1 is dependent upon participant_payment records. According to dgg, the participant_payment table will be deprecated soon. What will be the future method of doing this? It seems that a more complex web of references, something like participant_id -> line_item_id (via line_item table) -> financial_item_id (via financial_item table) -> trxn_id (via entity_financial_trxn table on the financial_item_id) -> contribution_id (via entity_finanical_trxn on the trxn_id) -> all the rest of the trxn_id's (via entity_financial_trxn on the contribution_id) would be necessary.. and that then all the trxn_id's found based on the contribution_id that were not just payment processor fees transferring from the payment processor account to the bank fees account would need to be totaled up.

Edit: The above could be simplified: contribution_id is in the line_item table, so first two steps of the entity_financial_trxn table are unnecessary.

Another edit: It is not dependent upon participant_payment, but because the entries are there and left joins are used, it will total them up if they're there. This is up because the 4.1 module I'm using allows for multiple participant_payment records per participant.

Here's the salient bit, from CRM/Core/BAO/FinancialTrxn:

Code: [Select]

SELECT SUM(ft.total_amount)
FROM civicrm_financial_trxn ft
  LEFT JOIN civicrm_entity_financial_trxn eft ON (ft.id = eft.financial_trxn_id AND eft.entity_table = 'civicrm_contribution')
  LEFT JOIN civicrm_contribution c ON (eft.entity_id = c.id)
  LEFT JOIN civicrm_participant_payment pp ON (pp.contribution_id = c.id)
WHERE pp.participant_id = {$entityId} AND ft.to_financial_account_id != {$toFinancialAccount} AND ft.to_financial_account_id != {$feeFinancialAccount}
  AND ft.status_id IN ({$statusId}, {$refundStatusId})
It is indeed glued together with participant_payment, although it could be done without.

[jakecivi]

Also noting that getPaymentInfo in Contribute/BAO/Conribution depends on participant_payment.. I imagine many other things do too.. I imagine that many reading this already know that..

[Dave Greenberg]

Jake - This wiki document s/b accurately represent the data processes affecting contribution, line_item, financial_item and financial_trxn tables:
http://wiki.civicrm.org/confluence/display/CRM/CiviAccounts+Data+Flow

If you're seeing something different from what's documented there, it "may" be a bug - so come back here w/ details.

As you noted, there's definitely more refactoring needed before the participant_payment and membership_payment tables can be eliminated. However the changes in 4.5 to  line_item data (e.g. adding contribution_id and ensuring that entity_item/entity_table points to related membership or participant row) are a step in that direction.

[jakecivi]

Thank you for the link.

I can confirm that 1) Registering for an event using Paypal, which results in two financial_items created (one for the processor fee, one for the registration fee) and a value in the fee field of the contribution table; 2) Changing the line_item's unit_price and line_total in the database to the original fee plus 1; 3) adding a payment of $1 in cash to make up for the outstanding $1 of the fee; results in two financial_trxn's being created: one for the $1 payment and one for the (in this case) $7.55 processor fee from the original payment, and one financial_item being created to represent the extraneous fee. These are all linked as expected in the entity_financial_trxn table.

I can also confirm that zeroing out the fee_amount in the contribution table, upping the line_item's price again, and adding another $1 payment results in the $1 trxn and corresponding entry in entity_financial_trxn being created correctly, without the trxn, financial_item, and entries in entity_financial_trxn related to the extraneous fee.

[jakecivi]

I can also confirm that doing it the "right way" (changing the selection of the fee) results in the negative-amount transaction and new line and financial items and corresponding entity_financial_trxn's being created, results in the contribution's "total_amount" being changed to the new amount while its fee_amount, net_amount, and amount_level stay the same, and adding a cash payment in the amount of the difference results in the creation of the correct entires as well as the extraneous fees in the amount of the original payment processor fee.

This must not be the desired behavior..

[Dave Greenberg]

Jake - It sounds like you've identified a bug, but I'm having a hard time following your last post :-(

What does this mean (specific action in the UI) - "changing the selection of the fee" ??
What does this mean  (specific action in the UI) - "adding a cash payment in the amount of the difference" ??

Perhaps a specific example / story w/ screen shots along the way (both the UI screens and if possible dump or phpMyAdmin screenshot of DB records) would help.

[jakecivi]

Sorry - I can give more specifics later, but maybe this will clarify:

"changing the selection of the fee" - on an event with several different price options, clicking the "change selections" button and choosing a different fee level, in this case moving to a $40 option from a $30 option. (that's the "right way" instead of manually editing the line_item's unit_price and line_total)

"adding a cash payment in the amount of the difference" - clicking the record payment button below the "Fees" section and adding a $10 cash payment.

Hope that helps!

p.s. is there anywhere in the interface to see all of the fee-only financial_trxn's, or do I have to do that through the database?

[Dave Greenberg]

Jake - That helps. Part of my confusion is the use of the word 'fee' (processor fee vs. event fee).  I'll check back here when u post specific example.

RE: financial_trxn - take a look at the Book-keeping report, and see whether the output from Accounting Batch exports has what you're looking for.