CiviCRM Community Forums (archive)

The new event handling is looking great - you can at last record multiple payments for an event.  A challenge for the administrator, though, is to keep track of such payments and work out who owes what.  I am fairly ignorant about the underlying data, but have investigated some of the data tables and generated this query that might be used.  However, some of you experts may be able to tell me how it really should be done.  Ideally, it would be in the form of a report.  What are the best tables to use?  I notice that the event fees seem to appear in lots of different places, and I wonder if that is a good idea for the future design of CiviCRM.  The SQL looks like this:

select    e.id,
      e.title_en_GB,
        contact.last_name,
        contact.first_name,
        line_item.line_total as 'Total Cost',
        sum(ifnull(entity_trxn.amount, 0)) as 'Amount paid',
      line_item.line_total - sum(ifnull(entity_trxn.amount, 0)) as 'Amount owed',
        note.note as 'Note'
      from civicrm_event as e
        left join civicrm_participant as participant on e.id = participant.event_id
        left join civicrm_contact as contact on contact.id = participant.contact_id
        left join civicrm_participant_payment as payment on participant.id = payment.participant_id
        left join civicrm_contribution as contribution on contribution.id = payment.contribution_id
        left join civicrm_line_item as line_item on line_item.entity_id = participant.id and line_item.entity_table = 'civicrm_participant'
        left join civicrm_entity_financial_trxn as entity_trxn on entity_trxn.entity_id = contribution.id
      left join civicrm_financial_trxn as trxn on trxn.id = entity_trxn.financial_trxn_id
           Left Join civicrm_note as note On participant.id = note.entity_id and  note.entity_table = 'civicrm_participant'
where e.id = 2 and (trxn.to_financial_account_id = 6 or trxn.to_financial_account_id is null)
group by participant.id;

I just chose one event to test the query - a production version would need to allow one to choose the event in question.  Here is an example of the output.

id	title_en_GB	last_name	first_name	"Total Cost"	"Amount paid"	"Amount owed"	Note
2	"MA Annual Conference 2015"	LastName1	FirstName1	130.00	50.00	80.00	NULL
2	"MA Annual Conference 2015"	LastName2	FirstName2	130.00	20.00	110.00	NULL
2	"MA Annual Conference 2015"	LastName3	FirstName3	160.00	0.00	160.00	NULL