API for creating contributions with line items results in bad data

[SarahG (FountainTribe)]

I am trying to figure out when/if the issue described at: https://issues.civicrm.org/jira/browse/CRM-12983  is going to be addressed.  (Per the JIRA, this issue is supplanted by issue https://issues.civicrm.org/jira/browse/CRM-13067, which is supplanted by https://issues.civicrm.org/jira/browse/CRM-13073. Issue 13073 has no clear connection to issue 12983)

This issue is becoming increasingly urgent as it renders the "Accounting Batch" area to export to general ledger unusable, as well as the bookkeeping transaction report template as unusable for the impacted contributions.  (For example: when using a payment processor extension that offers recurring contributions, the contributions are created by the payment processor extension via API. )

I have attempted an ugly work-around (I directly manipulate core CiviCRM financial tables using SQL)  in the extension at: https://github.com/sgladstone/com.pogstone.paymentprocessorhelper  which helps with Authorize.net and PayPalPro recurring contributions.  Perhaps the SQL in my extension could be used to fix the API (which is over my head)

[adixon]

Sarah: I wonder if you could summarize what you discovered and solved with the processor helper?

Specifically - when using the api now, we're getting line items, but not the right accounting records in one of those civicrm_financial_<something> tables. Can you fill in the details, and maybe provide an example?

Anyone: I assume that somewhere in core, those tables get written correctly for regular contributions, so I'd guess there's some piece of code in there that needs to get triggered by the API.

Or is there perhaps a good reason why the accounting stuff is not getting triggerred? i.e. is this issue more complicated?

[SarahG (FountainTribe)]

What I  have found is that 2 civicrm core tables are not getting updated when the contribution API is used. Specifically, the tables "civicrm_financial_item" and the table "civicrm_entity_financial_trxn" are not getting the records they should for each  line item when using the contribution API. 

When using the  user interface to create one contribution with 3 line items (each with a different financial type), then the table "civicrm_financial_item" gets 3 new records (one record per line item), and the table "civicrm_entity_financial_trxn" also gets 3 new records (one record per line item)

(The core table "civicrm_line_item" gets updated correctly from the API and the user interface)  what makes this tricky is the table "civicrm_entity_financial_trxn" has 2 foreign keys in each record: It has the id field from the table "civicrm_financial_item" and the id from table "civicrm_financial_trxn"

[SarahG (FountainTribe)]

Here is a code sample of calling the CiviCRM contribution and line item API (which correctly creates records in the civicrm_contribution table and civicrm_line_item table, but incorrect data is in tables "civicrm_financial_item" and the table "civicrm_entity_financial_trxn" )

Code: [Select]


 $skipLineItem_parm = "1";


$new_contrib_params = array( 'version' => 3,
  'sequential' => 1,
  'financial_type_id' =>  $new_contrib_tmp['financial_type_id'],
  'contact_id' => $new_contrib_tmp['contact_id'],
  'skipLineItem' => $skipLineItem_parm,
  'payment_instrument_id' => 1,
  'total_amount' => $new_contrib_tmp['total_amount'] ,
  'trxn_id' => $trxn_id ,
  'contribution_recur_id' => $new_contrib_tmp['contribution_recur_id'] ,
  'currency' => $new_contrib_tmp['currency'] ,
  //'fee_amount' => $new_contrib_tmp['fee_amount'],
  //'net_amount' => $new_contrib_tmp['net_amount'],
  'contribution_campaign_id' => $new_contrib_tmp['contribution_campaign_id'],
  'non_deductible_amount' => $new_contrib_tmp['non_deductible_amount'],
  'contribution_page_id' => $new_contrib_tmp['contribution_page_id'],
  'source' => $source_tmp , 
  'honor_contact_id' => $new_contrib_tmp['honor_contact_id'],
  'honor_type_id' => $new_contrib_tmp['honor_type_id'],
  'contribution_status_id' => 1,
  'receive_date' => $trxn_receive_date  ) ;


$new_contrib_result = civicrm_api('Contribution', 'create', $new_contrib_params ) ;


$new_contrib_id = $new_contrib_result['id'];

$params = array(
  'version' => 3,
  'sequential' => 1,
  'entity_table' => 'civicrm_contribution',
  'entity_id' => $new_contrib_id ,
  'price_field_id' => $original_line_item['price_field_id'],
  'label' => $original_line_item['label'],
  'qty' => $original_line_item['qty'],
  'unit_price' =>  $original_line_item['unit_price'],
  'line_total' => $original_line_item['line_total'],
  'participant_count' => $original_line_item['participant_count'],
  'price_field_value_id' => $original_line_item['price_field_value_id'],
  'financial_type_id' => $original_line_item['financial_type_id'],
  'deductible_amount' => $original_line_item['deductible_amount'],
 
);

                                             $li_result = civicrm_api('LineItem', 'create', $params);

[drastik]

I was asked to comment here regarding whether this was impacting the Stripe payment processor.

Honestly I have no idea

SarahG: are you using Stripe in this instance?

[SarahG (FountainTribe)]

I am not currently using Stripe at this time. I am concerned that if the Stripe extension is using the Contribution and Line Item APIs, then anyone using your extension for recurring contributions (with pricesets) will be facing this serious bug. (ie unable to use the Bookkeeping Transaction Report template for their Stripe recurring contributions, and unable to export their Stripe recurring contributions to their general ledger)

In short, recurring priceset contributions from any payment processor that uses this API have bad accounting data.

[KarinG]

@Drastik! I'm afraid Stripe may be affected as well - indirectly - this is not a Payment Processor bug. I think this affects everyone who would like to extract Financial/Bookkeeping details from CiviCRM installs that are processing Recurring Contributions with Price sets.

Time for screenshot! (attached):
Left top: View Contribution (ID 354) - this is the first of a recurring series. It is created by a user via a Public Contribution page. Total amount is $185 - it is split up over 4 different components. $100 went to Fund 1. All good.

Left bottom: View Contribution (ID 375) - this is a subsequent contribution - part of the same recurring series. At the payment processor level the contribution record and line_items are created using the CiviCRM - Core APIs. As you can see the APIs correctly create the contribution record - line items are identical to what they were in the first installment. $100 went to Fund 1. Perfect.

Right side: it all goes bananas when pulling the core Bookkeeping Transactions Report. I'm trying to figure out how much went to Fund 1 -> The $100 from ID 354 that was created via the Contribution page makes it into the bookkeeping report. But the $100 from ID 375 does not.

The CiviCRM Core line item API should really write to the financial tables as well - but it doesn't. I agree it's a bug - as I could see how there would be a reasonable expectation that financial reports/batches work with other common CiviCRM features like price sets and recurring contributions.

[KarinG]

While testing [with 2 of our clients' accountants] I've experienced other issues/limitations with the Financial/Bookkeeping bits in CiviCRM Core in other sections - so none of our projects are actually using any of the CiviCRM Bookkeeping functionality yet - [to date I use CSV/custom reports - which can easily be made to extract exactly what we need] - else I could justify to try dig into this one.

Anyone other than Sarah using CiviCRM's Financial/Bookkeeping functionality with their clients - and accepting recurring contributions (either credit card of direct debit) with any payment processor - PayPal Pro/Authorize.net/Stripe/iATS Payments etc? Sarah is looking for help to fix this. We should be thankful that she found this bug and come up with a plan to help her out.

[JoeMurray]

Several points quickly:

- 4.4 was made to provide backwards compatibility to 4.3- calls to create a contribution. If you add the parameter to not create the line_item, then you need to call not one but 6 API create functions, one for each of the records documented at http://wiki.civicrm.org/confluence/display/CRM/CiviAccounts+Data+Flow#CiviAccountsDataFlow-Offlinecompletedcontribution("basedataflow") or elsewhere on that page.

- This is not a payment processor specific issue. Payment processor code should not be creating these records.

- The Bookkeeping report is not the best way to be exporting bookkeeping information. It would be better to focus on improving the .csv and .iif outputs from Batch Export. Maybe we should deprecate or remove the report so we don't have to maintain similar code in two places.

- There was a bug at one point that has been fixed IIRC for recurring donation price sets and the creation of relevant records in these tables. I would be interested in reviewing a database with the problems you mention, KarinG, so we can determine if there is something else wrong in addition.

- See https://issues.civicrm.org/jira/browse/CRM-12983?focusedCommentId=70753&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-70753

[JoeMurray]

Sarah, can you confirm that the problems that you are experiencing are all related to recurring contributions or contributions that you created via your code calling into the API? If so, then since we know that code should be doing inserts into several more tables, I think we have found the cause of your errors, or at least one of them.

[KarinG]

Yes to improving .csv and .iif outputs

Problems I was referring to were some of the issues described in: http://forum.civicrm.org/index.php?topic=29727.0 - please note it's not related to pricesets at all. I'd be happy to review this again - if you think some progress has been made on this? At the time you mentioned some enhancement to support this may be possible.

Thanks Joe,

[JoeMurray]

Thanks, Karin.

It would be good to provide support for subaccounts related to contacts. I imagine a process that would export the subaccounts for clients from QB and import them into an extension's field for contacts in CiviCRM. We could extend the financial account with a flag indicating that it should take the subaccount information when available from the contact's record. When it came time to export an .iif, the relevant subaccount would be added.

Previously I believe I had considered making a separate account for each sub-account, but I don't think that makes sense in this context where the subaccount is by contact. The other type of 'sub-account' that I recall being recommended was for projects. I was thinking we could do something that linked up campaigns in CiviCRM to projects in QB.

Can you try to get a transaction with a sub-account to go into QB, and then share the format required - was it 'xxxxx-yyyy' or something, where the '-' is a lexical divider of account and subaccount? With that crucial piece of information and proof of concept, we could look at setting up an MIH for this.

[Eileen]

A few points

1) supplemented not supplanted - ie. not replaced by. Also I removed the link that you said was not related as I think it was to the wrong issue.
2) I think there are 3 issues here
 - missing api functionality for 'thick' api functions - this is the meta issue we keep working around this missing functionality
 - unbalanced api transactions in general
 - unbalanced recurring api transactions

Note that all the recurring IPN classes in core are relying on the BASEIPN::completetransaction function to create subsequent payments (which seems like it's being overloaded a bit as it was intended to 'complete' rather than duplicate.

The api contribution.completetransaction is also relying on this function (personally I tend to use this api for IPNs rather than have a full IPN class because I know it's tested & generally the only processor-specific thing going on is determining the outcome.)

So, as far as recurring transactions are concerning I think the answer lies in tackling the BaseIPN function (refactor, improve, fix, ensure that either in that function of the api we have a contribution_recur.createpayment api that will basically take a small set of parameters & take care of everything).

This doesn't resolve the generic - api-unbalanced issue

[KarinG]

Joe: happy to have another look at civiaccounts - and try what you described above - will definitively aim to show you at CiviCON.

Eileen: Right - the specific issue here is:
$contributionResult = civicrm_api('contribution','create', $contribution);
creates unbalanced transactions

Right now it creates the contribution - and you can get it to create line items (Alan does that) - but it's not creating balanced transactions in the Financial tables.

So this issue is not specific to recurring contributions. It's just that it surfaces during recurring contributions - because you need to create the contribution via the API.

How would you go about this? Take Financial table bits that currently (correctly) happen when a contribution is created off/online and to add that to the api/v3/Contribution.php -> create? I'd be happy to try start this. If you think that would be useful?

[Eileen]

OK - I guess it depends for recurring - the built in ones - Authorize.net, paypal & eway recurring are failing due to the BASEIPN.

My expectation is that we would add an api contribution.repeat_transaction that creates a repeat transaction with all the various parts cloned. That doesn't fix the api issue - but it's why I see there as being 2 separate problems