Description of data flow / data representation for common order and payment interaction under 0.10.2 data model

Online Contribution

User submits online donation via credit card.

Order

• If contact doesn't already exist, create contact
• Create record in order table and retain order.id in memory pending completion of order process
• Insert contribution_item (maybe retain 'contribution' table name, consistent w/ membership, participant ...)
• Insert financial_item for the contribution_item (order_line/quanitity_line not required if only 1 quantity of the financial item in the order) and put "civicrm_contribution_item" in financial_item.entity_table and the contribution_item.id in financial_item.entity_id
  • ? financial_type_id derived from contribution_page? why is it in both records? The type_id stored in the contribution_page will be used to store the type_id in the financial_item, but if the user changes the financial_type for the contribution page - is this replacement for contribution_type_id YES - I thought financial_account table was the replacement? THE PROBLEM WITH THAT APPROACH was that users won't find selecting financial accounts as user friendly / familiar as selecting a contribution type when creating an event/membership/contribution page, and one contribution type may need to be associated with multiple financial accounts for different purposes
• Insert "civicrm_order" in entity_financial_batch.financial_batch_entity_table, the order.id in entity_financial_batch.financial_batch_entity_id and the contribution's financial_item.id in entity_financial_batch.financial_item_id
• Insert a financial_item for the order, which contains the total value of all the quantities of items in the order (in this case just the 1 contribution item) and put "civicrm_order" in financial_item.entity_table and the order.id in financial_item.entity_id
• Complete the order process and commence payment process

Summary from Joe/Andrew: so a pay later contribution creates 5 records: an order record, a contribution_item record, a financial_item record pointing to the contribution_item record, an entity_financial_batch linking the order to the financial_item in the order, and a financial_item record pointing to the order record itself.

Payment (offline or payment processor transaction)

• Create record in financial_trxn (this row could be changed to a combination of a financial_trxn_item extending a financial_item, so all financial items (including payments) are in the one table, but perhaps better not to change this now), and create entity_financial_trxn row (insert "financial_item" in entity_financial_trxn.entity_table, financial_item.id in entity_financial_trxn.entity_id, financial_trxn.id in entity_financial_trxn.financial_trxn_id and the full amount in the entity_financial_trxn.amount)
  • financial_trxn.to_account_id is FK to financial_account. This is derived at time of trxn based on the financial_account associated with the payment processor - e.g. you may have a "PayPal Account" Asset financial account so that money coming into PayPal is recorded to that Asset and can be exported to accounting system for reconciliation.  Need to check that you can already apply a financial_account to a payment processor or add that.
• Set financial_item.status for the individual contribution_item to an integer that reflects a paid status
• Set financial_item.status for the order to an integer that reflects a paid status

Summary from Joe: create 2 records: a financial_trxn recording payment, and an entity_financial_transaction allocating full payment to the order.

Question: in second row, should 'financial_item.id in entity_financial_trxn.entity_id' be changed to 'financial_item.id (that points to the order, not the contribution_item) in entity_financial_trxn.entity_id'? Andrew: No, as you will need to be able to attribute the portion of the payment to individual financial_items so that people using cash accounting systems can record income to the right financial accounts for those financial items.

NEED TO DEAL WITH FEE?

Receipt

Some organisations do receipts annually, others do them at time of payment.  This example assumes the receipt is created and sent at time of the payment processor completing successfully
Traditionally, a receipt is issued for the full amount of a payment, and may list which order the payment was applied to and/or what financial_items
I believe Joe's clients had some use cases where they want an official_receipt that is issued annually only for (Joe: the total of all) payments for specific financial_items that are deductible, which may have been part of a payment, but not for other financial_items covered by that payment which are non-deductible.  The process below is complicated by that.

• Create record in official_receipt table (does this happen now or during receipt batch creation? THIS IS A NEW TABLE, currently the contribution table has just a receipt_date) and retain official_receipt.id in memory pending completion of transaction
• SELECT the financial_trxn.ids and corresponding financial_item.ids for the payments/items to be included in the receipt based on the rule set you are using, and calculate the total for your official_receipt
• (Joe Shouldn't this iterate over all of the selected payments/items? Andrew: I think this does need tidying up for the more complicated use case you have suggested - but I think we should stick to the simple use case for now) EITHER 
  • [The simple use case] Insert "civicrm_official_receipt" in entity_financial_trxn.entity_table and the official_receipt.id in entity_financial_trxn.entity_id and the contribution payment's financial_trxn.id in entity_financial_trxn.financial_trxn_id
  • [If only receipting for some items in a payment also do the following] Insert "civicrm_official_receipt" in entity_financial_batch.financial_batch_entity_table and the official_receipt.id in entity_financial_batch.financial_batch_entity_id and the contribution's financial_item.id in entity_financial_batch.financial_item_id
    • (Joe: Why 'ALSO' do the following, rather than just do the following? Andrew: On reflection, I don't know that the second approach actually achieves its goal - it may be that after doing an iteration over all the selected payments/items you would need to create new financial_items for the lines in the receipt, with each new financial_item being a receipt line of the part of a payment attributed to an item - these are the lines that would be displayed on the receipt)
• Insert a financial_item for the official_receipt, which contains the total value of all the amounts included in the official_receipt (in this case just the 1 payment) and put "civicrm_official_receipt" in financial_item.entity_table and the official_receipt.id in financial_item.entity_id
• If the official_receipt contains a concoction of parts of payments towards particular "deductible items", the official_receipt table will probably need a field to store this concoction so that it can easily be reproduced/audited.  The simple use case above would enable a receipt to be generated at a future date, listing the payment/s receipted, without having to store the actual payment items in the official_receipt table as they are linked through the entity_financial_trxn table.
  • (Joe: More details on this for an event registration are needed.)
• Complete the receipt process by emailing off the receipt and recording that activity.

Event Registration (simple fee)

User submits online registration for 2 participants via credit card.  This simple case assumes no tax applies to this item, otherwise a financial_item for the tax would need to be added for each participant, with the amount calculated and recorded based on the financial_type_id (which would link to the tax_type table through the financial_type.entity_table).

Order

• If contacts don't already exist, create contact
• Create record in order table and retain order.id in memory pending completion of order process
• Insert participant for each contact
• Insert 2 financial_item, 1 for each participant (order_line? Not necessary, as only 1 of each participant)
  • Joe: these financial_item point to participant records?

• *- ? financial_type_id derived from event table? IN SAME WAY AS from contribution_page above. (Joe: detailed account codes requires that financial_account NOT be associated with page but with detailed items. In case of Events, this should be ) is this replacement for contribution_type_id (which is still listed in new schema)  CONTRIBUTION_TYPE_REVISION stores the information for a contribution type that links to a set of accounts, which can be changed from time to time without a financial_item already recorded having the information associated with it at the time of recording lost.
• Insert "civicrm_order" in entity_financial_batch.financial_batch_entity_table, the order.id in entity_financial_batch.financial_batch_entity_id and the 1st participant's financial_item.id in entity_financial_batch.financial_item_id
• Insert "civicrm_order" in entity_financial_batch.financial_batch_entity_table, the order.id in entity_financial_batch.financial_batch_entity_id and the 2nd participant's financial_item.id in entity_financial_batch.financial_item_id
• Insert a financial_item for the order, which contains the total value of all the quantities of items in the order (in this case the 2 participant's items) and put "civicrm_order" in financial_item.entity_table and the order.id in financial_item.entity_id
• Complete the order process and commence payment process

Summary from Joe: if contacts exist: create order, create two participant records, create two financial_item records pointing to the participant records, create two entity_financial_batch records linking order to the 2 financial_items, create financial_item pointing to the order.

Payment processor approves transaction

• Create record in financial_trxn (this row could be changed to a combination of a financial_trxn_item extending a financial_item, so all financial items (including payments) are in the one table, but perhaps better not to change this now), and create entity_financial_transaction row (insert "financial_item" in entity_financial_trxn.entity_table, financial_item.id in entity_financial_trxn.entity_id, financial_trxn.id in entity_financial_trxn.financial_trxn_id and the full amount in the entity_financial_trxn.amount)
  • financial_trxn.to_account_id is FK to financial_account. This is derived at time of trxn based on the financial_account associated with the payment processor - e.g. you may have a "PayPal Account" Asset financial account so that money coming into PayPal is recorded to that Asset and can be exported to accounting system for reconciliation.  Need to check that you can already apply a financial_account to a payment processor or add that.
• Set financial_item.status for the 1st participant's participation to an integer that reflects a paid status

• Set financial_item.status for the 2nd participant's participation to an integer that reflects a paid status

• Set financial_item.status for the order to an integer that reflects a paid status

Summary from Joe: insert financial_trxn, two rows in entity_financial_trxn linking financial_trxn to 2 participant financial_items.

Receipt sent to donor

• As above for Contribution, except that if linking official_receipt to financial_items rather than just payments, you'll need to create two rows in entity_financial_batch, each with the relevant participant's financial_item.id in entity_financial_batch.financial_item_id 

Event Registration - Price Set w/ 2 price fields

TBD