CiviCRM Community Forums (archive)

Using CiviCRM 2.0.5 on Drupal, we have a contact. On his Events tab he has 5 events. 2 have no fee but 3 do. Each of those 3 which has a fee is marked as status "Registered."

On his Contributions tab, however, I see only two entries, each an Event Fee. One is $25 and one is $20, for a total of $45.

Above that I see:

Current Year-to-Date - $ 145.00     # Contributions - 3     Avg Amount - $ 48.33
Total Amount - $ 45.00     # Contributions - 2   Avg Amount - $ 22.50

Now that seems definitely wrong, both the summary and the list showing only 2.

If I run

select civicrm_participant.id, display_name,civicrm_contact.id,  event_id, register_date, civicrm_participant.source  from civicrm_contact

left join civicrm_participant on civicrm_participant.contact_id = civicrm_contact.id

where civicrm_contact.id=2421

I indeed get 5 results.

This SQL:
select  total_amount, net_amount, civicrm_contact.id, is_test, contribution_status_id , cancel_date ,receive_date  from civicrm_contact

left join civicrm_contribution on civicrm_contribution.contact_id = civicrm_contact.id

where civicrm_contact.id=2421

yields:

"20.00","20.00","2421","0","1",\N,"2008-01-28 12:51:51"
"25.00","25.00","2421","0","1",\N,"2008-03-27 14:34:54"
"100.00","100.00","2421","0","1",\N,"2008-09-16 17:04:24"

which appears correct.

So it seems to me that the DB is correct, but somehow the Contributions tab is not picking this up.

Also if I go to civicrm/contribute/search/basic?reset=1 and put in the contacts last name, only the 2 smaller contributions show up. The $100 one is missing there also.

Any ideas?

Thanks

I did that and I found what's going on in the DB and the SQL, but not WHY it's happening yet. Regarding this:

Current Year-to-Date - $ 145.00     # Contributions - 3     Avg Amount - $ 48.33
Total Amount - $ 45.00     # Contributions - 2   Avg Amount - $ 22.50

The first line comes from:

SELECT count(*) as count,
       sum(total_amount) as amount
  FROM civicrm_contribution b
 WHERE b.contact_id IN ( 2421 )
   AND b.contribution_status_id = 1
   AND b.is_test = 0
   AND b.receive_date >= 20080101
   AND b.receive_date <  20090101
   
and the second from:

SELECT COUNT( civicrm_contribution.total_amount ) as total_count,
       SUM(   civicrm_contribution.total_amount ) as total_amount,
       AVG(   civicrm_contribution.total_amount ) as total_avg  FROM civicrm_contact contact_a LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id  INNER JOIN civicrm_contribution_type ON civicrm_contribution.contribution_type_id = civicrm_contribution_type.id  LEFT JOIN civicrm_option_group option_group_payment_instrument ON ( option_group_payment_instrument.name = 'payment_instrument' AND option_group_payment_instrument.domain_id = 1 ) LEFT JOIN civicrm_option_value payment_instrument ON (civicrm_contribution.payment_instrument_id = payment_instrument.value
                               AND option_group_payment_instrument.id = payment_instrument.option_group_id )  LEFT JOIN civicrm_option_group option_group_contribution_status ON (option_group_contribution_status.name = 'contribution_status' AND option_group_contribution_status.domain_id = 1 ) LEFT JOIN civicrm_option_value contribution_status ON (civicrm_contribution.contribution_status_id = contribution_status.value
                               AND option_group_contribution_status.id = contribution_status.option_group_id )  WHERE  ( contact_a.id = 2421 AND  civicrm_contribution.is_test = '0' )  AND civicrm_contribution.cancel_date IS NULL

and the relevant difference is this clause:

INNER JOIN civicrm_contribution_type ON civicrm_contribution.contribution_type_id = civicrm_contribution_type.id

I found that, oddly enough, one of my contribution records has no contribution_type. This SQL:

select  total_amount,  civicrm_contact.id,  contribution_type_id from civicrm_contact

left join civicrm_contribution on civicrm_contribution.contact_id = civicrm_contact.id

where civicrm_contact.id=2421

yields this:

20.00 2421 4
25.00 2421 4
100.00 2421 (Null)

Any further thoughts?

No, it was not recurring--it's for a one time event. However, I do see now that we have 357 contributions (out of 1741) which have null for contribution_type_id.

I applied that patch; I suppose we will have to wait and see if any more nulls show up.

The one record in question should be an "Event fee" type which is 4. We have 1294 existing records with that type, so that seems to work. We furthermore only have one payment processor, although it supports credit cards or PayPay account payments.

I don't think it's crucial for us which contribution_type_id I set those 'null' records to, but overall, I am wondering if you have any other ideas how this situation may have come about.

Thank you.