CiviCRM Community Forums (archive)

We have a very common use case:  backend registration of users at prices different than the Fee / price set (often "Free", for speakers or staff).  We may make exceptions for various reasons and cannot rely on published fees.  It appears there is a provision for "Recording Payment" in the "Register New Participant" option of the Event drop down.  When recording a payment, however, if the payment is less different than the fee, then the Income report does not show the actual revenue of the event.  In fact, I can't figure out a way to generate the correct report that only pulls contribution records associated with a participant of an event.  The problem is that the contribution must be associated with the participant of a specific event, and not the contact.

I'm concerned about a few things here:
1. A way to satisfy this common use case (where the payment does not match the fee)
2. There is no warning about this when creating a contribution record (BTW - it should fill in the fee amount as default.)
3. The "Income Report" (which I suggest changing to "revenue" since it does not include costs) is misleading (Perhaps "Fee report") unless it counts contributions.
4. I see in the database civicrm_participant_payment which links participants to payments, but I can't see how to integrate that in any of the UI query tools. I would go so far as to say that the all the Find Participant fields (total amount, etc.) should be based on actual contributions, rather than published fees.

Ideally, our organization would fund this development, but unfortunately, I'm (as usual) forced to cry poverty, however I wanted to see if others face this same scenario or have a workaround.

Best,
S.

Check this post from Lobo for our thoughts about some ways to ameliorate some of this:
http://forum.civicrm.org/index.php/topic,11926.msg51205.html#msg51205

Here's a good query for that that I've come up with.  Note that it's for a specific event (id=8).  I'm implementing it in a Custom Search right now.  This is an old topic, but maybe someone's still wondering.  Also planning on making this into a Report, too, just to gain the PDF export option.

SELECT participant.event_id AS event_id,
contact.id AS contact_id,
contact.display_name AS contact_name,
participant.id AS participant_id,
contrib.id AS contrib_id,
participant.fee_amount AS fee_amount,
sum(if(contrib.contribution_status_id = 1,contrib.total_amount,0)) AS contrib_amount,
(participant.fee_amount - sum(if(contrib.contribution_status_id = 1,contrib.total_amount,0))) AS outstanding_amount,
participant.status_id AS participant_status,
participant.register_date AS register_date,
participant.registered_by_id AS registered_by,
contrib.is_test AS contrib_is_test,
contrib.is_pay_later AS contrib_pay_later,
contrib.contribution_status_id AS contrib_status

FROM
civicrm_participant AS participant
LEFT JOIN civicrm_contact AS contact ON contact.id = participant.contact_id
LEFT JOIN civicrm_participant_payment AS payment ON payment.participant_id = participant.id
LEFT JOIN civicrm_contribution AS contrib ON contrib.id = payment.contribution_id

WHERE participant.event_id = 8
GROUP BY participant.id


I have a couple of reports that do stuff a bit like this - but they all have some hard-coding in
1) budgetting report - calculate income by event & total based on values in custom fields
2) actual against budget - similar to above but with actual for term
3) comparing the same class in different terms (using a campaign id to link them)

I'll try to share them at some point - but there's a bit of work involved.