Author Topic: Editing contribution created an additional line item instead of editing the old. (Read 5508 times)

joanne · November 04, 2012, 09:13:50 pm

I edited an off-line contribution using the standard 'Edit Contribution' of Civi.

The original contribution was made without using a specifically configured price set so when I edited it I was able to set the total amount based on a price set.

Everything looks OK from the Civi contibution side, however, instead of editing the existing entry in the civicrm_line_item table a completely new entry was created.

That was certainly not what I expected as I now have one contibution for $5 linked with two line items each for $5.

I would have thought this was a bug, but perhaps this is expected behaviour.

Has anyone else had this happen or like to comment on bug vs expected behaviour?

Yashodha Chaku · November 22, 2012, 05:40:42 am

jchester :

Can you confirm the same on our demo (http://demo.civicrm.org/) and if it does replicate file an issue with JIRA(http://issues.civicrm.org/) preferably with a patch.

-Yashodha

Eileen · November 22, 2012, 03:45:28 pm

Yashi, Joanne is on leave this week - but when she gets back I'll look at it with her since dgg couldn't replicate it on demo, but of course demo has a very simple dataset.

Yashodha Chaku · November 23, 2012, 01:31:24 am

Eileen:

I tried but couldn't replicate it on my local install as well.

-Yashodha

flug · December 20, 2012, 02:20:48 pm

I'm getting this on my local install as well, which is in Drupal 6 & CCRM 4.2.2.

A possible variable is the use of price sets.

Besides the replication of the line item, I also noticed this behavior: Changing the VALUE of the donation in the line item entry.

So for example in one case, the civicrm_contribution total_amount was $2000 but in line_item it was $2. (In fact, that wasn't one case but two--both the same).

In another case, a $5000 in civicrm_contribution became $5 in line_item after editing it.

In other cases, $10, $20, $25, $30, and other similar small amounts all became $1 in line_item after editing the contribution.

Notice that right now the line items are not really exposed in CiviCRM so there may be many such errors and no one would really notice them.

And for example I can't even figure out how to replicate this on the demo because there is no place to see the line item entries. (FYI I'm using a modded version of Eileen's line item extended report module - we need it to print our reports etc which depend on knowing the line item values.)

flug · December 20, 2012, 02:23:22 pm

OK, I came up with this SQL query which will detect whether you have any line item entries which are different from the corresponding contribution entries. It will detect either the double entries noted by Joanne or the strangely changed values I noted above.

Code: [Select]

SELECT * from (
SELECT cc.id as ccid,cli.id as clid, cc.contact_id, cc.total_amount as contribution_total, SUM(cli.line_total) as line_item_total, COUNT(cli.line_total) as line_item_count, cct.display_name, cc.receive_date, cc.source FROM 
civicrm_contribution cc inner join
`civicrm_line_item` cli on cc.id=cli.entity_id 
left join civicrm_contact cct on cc.contact_id=cct.id

WHERE cli.entity_table="civicrm_contribution"
group by cc.id
) as temp

where temp.contribution_total != temp.line_item_total

Note that it only looks at contributions not event payments, which might (?) also have this problem.

It seems like it would be helpful if a few people could run this on their databases and see if they have these discrepancies as Joanne and I do.

Dave Greenberg · December 20, 2012, 03:49:55 pm

I tried to replicate this on my local 4.2 sandbox.
- Create 2,000 contribution via New Contribution (back office form)
- Check line_item table - unit_price and line_total are both 2000.00
- Edit same contribution and change amount to 5,000
- Check line_item table - unit_price and line_total are both 5000.00, no duplicate line_item created

There's some unclear info in this thread about usage of price sets though. I'm testing a simple contribution (no price set). If the problems you're seeing involve using a price set and then editing - please detail out the exact steps and the DB state at each point.

Also wondering if you're using a different thousands separator or decimal delimiter in your localization settings (or > 1 currency or some other config different from defaults)??

flug · December 20, 2012, 10:30:03 pm

Quote from: Dave Greenberg on December 20, 2012, 03:49:55 pm

I tried to replicate this on my local 4.2 sandbox.

Ok, here is how I was able to replicate this. Some other very similar steps did not replicate, so there is something quite particular about this:

1. Go to the contact page
2. Click on the 'contribution' tab
3. Click 'record contribution (cash, check, eft . . . )
4. Select our first contribution type in the list (we have 10-12 in the list), USD, $2000 entered, "Choose price set" is an option but we don't select it.
5. Paid by check & enter check number 1000
6. Leave everything else as default.
7. Click save.
8. Check line_item table, the row in civicrm_line_item for this contribution (entity_id) lists qty=1, unit_price=2000.00, line_total=2000.00.
9. Now (still in contact screen under the contributions tab) click 'edit' that contribution.
10. Don't change anything, just click 'save'.
11. Now re-check the line_item table and it shows qty=1, unit_price=2.00, line_total=2.00

This is pretty persnickety as to details. Amount =$100 instead of $2000, there is no problem. Edit the contribution from the 'advance search results' screen rather than the contact summary page, there is no problem.

I'm not sure what other of the details may or may not be important!

Quote

There's some unclear info in this thread about usage of price sets though. I'm testing a simple contribution (no price set). If the problems you're seeing involve using a price set and then editing - please detail out the exact steps and the DB state at each point.

One of my ideas is just having price sets active **might** have something to do with the problem. However in the step-by-step outline above you can see that I didn't use the price set, although several are available.

However, here is the way I have seen the duplicate line_items appear. Follow the same routine as above, steps 1-11. Then continue:

12. While editing the contribution, which was created without a price set, select a price set and enter an amount (I selected $2500, which was one of the options within the price set).
13. On the contribution page the total contribution is now shown as $2500 and there is no sign of the original $2000 contribution.
14. Don't change any other options. Click save.
15. Now check the line_item table. You will see an entry for both the initial contribution, made without the price set (and changed to $2 after the procedure above), and also you'll see the new entry for $2500 that you just edited with the price set.

Correct behavior (IMHO) would be to delete the previous line item, as it is now superseded by the line item(s) from the price set.

Quote

Also wondering if you're using a different thousands separator or decimal delimiter in your localization settings (or > 1 currency or some other config different from defaults)??

I think I'm just using the standard defaults of the installation. It is USD, only one currency in use, decimal delimiter of "." (period) and thousands separator of "," (comma). Default language is set to nothing (there are no choices in that drop-down box)--not sure what's going on there.

The fact that 2,000 is converted to 2.00 makes me think of decimal delimiter problems as well--perhaps it is saved as 2.000 instead of 2,000?

Thanks for being willing to take a look at this. I think it is quite mysterious and it definitely happens only under very particular circumstances. But (after checking with my SQL code above) we did have more than a dozen examples of this discrepancy in our DB that have happened over the past several months, maybe 1000 transactions or so. So it does actually occur from time to time.

Yashodha Chaku · December 28, 2012, 03:52:09 am

flug :

We'll investigate this and back to you soon.

-Yashodha

Yashodha Chaku · December 28, 2012, 04:12:38 am

flug :
Confirmed the bug , here is the issue :
http://issues.civicrm.org/jira/browse/CRM-11552

Thanks for catching this!
-Yashodha

flug · December 30, 2012, 12:04:33 pm

Quote from: flug on December 20, 2012, 02:23:22 pm

OK, I came up with this SQL query which will detect whether you have any line item entries which are different from the corresponding contribution entries. It will detect either the double entries noted by Joanne or the strangely changed values I noted above.

Here is an SQL query that will do the same for event registrations--determine if there are any problems where the line item total does not match the contribution total:

Code: [Select]

SELECT * 
FROM (

SELECT ccid, clid, cppid, participant_id, contact_id, contribution_total, SUM(line_item_total) as line_item_total, SUM(line_item_count) AS line_item_count, display_name, receive_date, source, contribution_id 
FROM (

SELECT cc.id as ccid,cli.id as clid, cpp.id as cppid, cpp.participant_id as participant_id, cc.contact_id, cc.total_amount as contribution_total, SUM(cli.line_total) as line_item_total, COUNT(cli.line_total) as line_item_count, cct.display_name, cc.receive_date, cc.source,cpp.contribution_id 
FROM 
`civicrm_line_item` cli 
left join civicrm_participant_payment cpp on cpp.participant_id=cli.entity_id AND cli.entity_table="civicrm_participant"
left join civicrm_contribution cc on cc.id=cpp.contribution_id
left join civicrm_contact cct on cc.contact_id=cct.id

WHERE 1

group by cpp.contribution_id

UNION

SELECT cc.id as ccid,cli.id as clid, cpp.id as cppid, cp1.id as participant_id, cc.contact_id, cc.total_amount as contribution_total, SUM(cli.line_total) as line_item_total, COUNT(cli.line_total) as line_item_count, cct.display_name, cc.receive_date, cc.source, cpp.contribution_id FROM 
`civicrm_line_item` cli 
inner join civicrm_participant cp1 on cp1.id=cli.entity_id AND  cli.entity_table="civicrm_participant" AND cp1.registered_by_id IS NOT NULL
left join civicrm_participant cp on cp1.registered_by_id=cp.id
left join civicrm_participant_payment cpp on cpp.participant_id=cp.id
left join civicrm_contribution cc on cc.id=cpp.contribution_id
left join civicrm_contact cct on cc.contact_id=cct.id
where cc.id IS NOT NULL
group by cpp.contribution_id


) as temp

WHERE 1
GROUP BY  ccid
) as temp2

where contribution_total != line_item_total
ORDER BY receive_date

Like the contribution/line item SQL code above, this is very interesting to run, to see if there are any anomalies between your contributions & line item totals.

The only ones I found our DB seem to relate to event registrations that were entered manually and where we manually overrode the event fee.

flug · December 30, 2012, 12:35:59 pm

Quote from: Yashodha Chaku on December 28, 2012, 04:12:38 am

flug :
Confirmed the bug , here is the issue :
http://issues.civicrm.org/jira/browse/CRM-11552

Thanks for catching this!
-Yashodha

Thanks for looking into this and submitting the bug report.

Has anyone had any further thoughts on the issue of $5000 turning into $5 and $2000 turning into $2?

Yashodha Chaku · December 31, 2012, 09:22:10 pm

flug :

Here is the patch for the issue CRM-11552 which will be available w/ 4.2.7 shortly :
https://fisheye2.atlassian.com/changelog/CiviCRM?cs=44509

Quote

Has anyone had any further thoughts on the issue of $5000 turning into $5 and $2000 turning into $2?

Can you try to replicate this on our demo (http://demo.civicrm.org/)

-Yashodha

Dave Greenberg · January 01, 2013, 08:54:57 am

I don't think there's a way for "flug" to see whether line_item amounts have been mangled (2,000 => 2.00) on the demo. :-(
Flug = can you give repeatable exact steps for recreating the line_total amount bug? If so, we'll try to recreate locally.

Else, may need to look at instrumenting some debug output to log file in the code on your site.

flug · January 01, 2013, 12:30:06 pm

Quote from: Dave Greenberg on January 01, 2013, 08:54:57 am

I don't think there's a way for "flug" to see whether line_item amounts have been mangled (2,000 => 2.00) on the demo. :-(

Yes, that's the problem.

Quote

Flug = can you give repeatable exact steps for recreating the line_total amount bug? If so, we'll try to recreate locally

1. Go to the contact page
2. Click on the 'contribution' tab
3. Click 'record contribution (cash, check, eft . . . )
4. Select our first contribution type in the list (we have 10-12 in the list), USD, $2000 entered, "Choose price set" is an option but we don't select it.
5. Paid by check & enter check number 1000
6. Leave everything else as default.
7. Click save.
8. Check line_item table, the row in civicrm_line_item for this contribution (entity_id) lists qty=1, unit_price=2000.00, line_total=2000.00.
9. Now (still in contact screen under the contributions tab) click 'edit' that contribution.
10. Don't change anything, just click 'save'.
11. Now re-check the line_item table and it shows qty=1, unit_price=2.00, line_total=2.00

That's quite persnickety as to details--like if the amount is $100 instead of $2000 there is no bug. However, it definitely holds for all thousand type amounts--$1000 becomes $1, $2000 becomes $2, $5000 becomes $5, $20,000 becomes $20, etc. I don't know exactly which other details are important, but following that sequence exactly gives the same bug every time on our system.

CiviCRM Community Forums (archive)

News:

Author Topic: Editing contribution created an additional line item instead of editing the old. (Read 5508 times)

joanne

Editing contribution created an additional line item instead of editing the old.

Yashodha Chaku

Re: Editing contribution created an additional line item instead of editing the old.

Eileen

Re: Editing contribution created an additional line item instead of editing the old.

Yashodha Chaku

Re: Editing contribution created an additional line item instead of editing the old.

flug

Re: Editing contribution created an additional line item instead of editing the old.

flug

Re: Editing contribution created an additional line item instead of editing the old.

Dave Greenberg

Re: Editing contribution created an additional line item instead of editing the old.

flug

Re: Editing contribution created an additional line item instead of editing the old.

Yashodha Chaku

Re: Editing contribution created an additional line item instead of editing the old.

Yashodha Chaku

Re: Editing contribution created an additional line item instead of editing the old.

flug

Re: Editing contribution created an additional line item instead of editing the old.

flug

Re: Editing contribution created an additional line item instead of editing the old.

Yashodha Chaku

Re: Editing contribution created an additional line item instead of editing the old.

Dave Greenberg

Re: Editing contribution created an additional line item instead of editing the old.

flug

Re: Editing contribution created an additional line item instead of editing the old.