The Summary Fields extension creates read-only custom data fields that extend Contact and are automatically populated with up-to-date totals such as total lifetime contributions, last contribution amount, last attended event, etc.
Other
8
stars
29
forks
source link
use of civicrm_line_items table misses updated transactions #66
If you create a contribution for $1000.00, then a record is entered in:
civicrm_contribution: the main contribution record
civicrm_line_item: one contribution can be split into different line items - for example - a portion of an event fee could go to the event fee, and a portion could go as a donation (different financial_type_ids).
civicrm_financial_item: every civicrm_line_item record might have one more more civicrm_financial_items which seem to adjustments made to the line items
civicrm_entity_financial_trxn: this seems to simply link contributions and line items to civicrm_financial_trxn
civicrm_financial_trxn: this table seems to serve as a double entry accounting system - recording which financial account each transaction goes in and out of
If you realize you made a mistake and edit the amount of the contribution, changing it to $100, then:
civicrm_contribution: the amount is updated with the new amount
civicrm_line_item: the original amount of $1,000 remains untouched, I suppose as an original record of the original line item.
civicrm_financial_item: the original record remains un-touched, but a new record is added for -900.00
civicrm_financial_trxn: the original record remains un-touched, but a new record is added for -900.00
Since we recently changed summary fields to calculate the totals from the civicrm_line_item table (to properly capture contributions made that are split between different financial types), we are now getting inaccurate results for contributions that have been updated since the civicrm_line_item table is not updated when a change is made.
To get the accurate results, we should be totalling the civicrm_financial_item's amount field instead of qty * unit_price in civicrm_line_item.
Here's an example query that would get a more accurate result:
SELECT SUM(fi.amount) FROM civicrm_financial_item fi JOIN civicrm_line_item li ON li.id = fi.entity_id AND fi.entity_table = 'civicrm_line_item' AND contact_id = 54708;
If you create a contribution for $1000.00, then a record is entered in:
If you realize you made a mistake and edit the amount of the contribution, changing it to $100, then:
Since we recently changed summary fields to calculate the totals from the civicrm_line_item table (to properly capture contributions made that are split between different financial types), we are now getting inaccurate results for contributions that have been updated since the civicrm_line_item table is not updated when a change is made.
To get the accurate results, we should be totalling the civicrm_financial_item's
amountfield instead ofqty * unit_pricein civicrm_line_item.Here's an example query that would get a more accurate result:
SELECT SUM(fi.amount) FROM civicrm_financial_item fi JOIN civicrm_line_item li ON li.id = fi.entity_id AND fi.entity_table = 'civicrm_line_item' AND contact_id = 54708;