Author Topic: join between `civicrm_contribution` and `civicrm_payment_processor` (Read 408 times)

Erich Schulz · October 18, 2014, 07:52:12 pm

hi folk - I'm trying to link contributions back to the source payment gateway

(so we can reconcile what the gateway says they processed and what civicrm said they processed)

i cant find the join between `civicrm_contribution` and `civicrm_payment_processor`

anyone got an sql snip handy?

Erich Schulz · October 18, 2014, 08:29:00 pm

ok cracked it:

this is pretty useless but is the simplest version to illustrate the joins I needed:

Code: [Select]

SELECT count(*), sum(ft.total_amount), sum(ccb.total_amount)      
, pp.name, pp.description                                         
FROM civicrm_contribution ccb                                     
LEFT JOIN civicrm_entity_financial_trxn fet                       
 ON fet.entity_table = 'civicrm_contribution'                     
 AND fet.entity_id = ccb.id                                       
LEFT JOIN civicrm_financial_trxn ft                               
 ON ft.id = fet.financial_trxn_id                                 
LEFT JOIN civicrm_payment_processor pp                            
 ON ft.payment_processor_id = pp.id                               
group by pp.id

this is a fuller version:

Code: [Select]

SELECT count(*), sum(ft.total_amount), sum(ccb.total_amount)                  
, ccb.payment_instrument_id                                                   
, csov.name status                                                            
, piov.name payment_instrument                                                
, concat_ws(' ', pp.name, pp.description) processor                           
FROM civicrm_contribution ccb                                                 
LEFT JOIN civicrm_option_group ogpi ON ogpi.name = 'payment_instrument'       
LEFT JOIN civicrm_option_value piov ON ccb.payment_instrument_id = piov.value 
  AND ogpi.id = piov.option_group_id                                          
LEFT JOIN civicrm_option_group csog ON csog.name = 'contribution_status'      
LEFT JOIN civicrm_option_value csov ON ccb.contribution_status_id = csov.value
  AND csog.id = csov.option_group_id                                          
LEFT JOIN civicrm_entity_financial_trxn fet                                   
 ON fet.entity_table = 'civicrm_contribution'                                 
 AND fet.entity_id = ccb.id                                                   
LEFT JOIN civicrm_financial_trxn ft                                           
 ON ft.id = fet.financial_trxn_id                                             
LEFT JOIN civicrm_payment_processor pp                                        
 ON ft.payment_processor_id = pp.id                                           
group by pp.id, ccb.payment_instrument_id, ccb.contribution_status_id

hope that helps someone

e

adixon · October 19, 2014, 06:52:41 am

Thanks for this. But if you're doing bookkeeping reconciliation, I think you don't want to start with the civicrm_contribute table.

The key thing is: a single row in the contribution table could be generating multiple transactions.

So if you're trying to do reconciliation, you care about transactions, and only then do you want those linked back to the source contribution [assuming they all have one, which I wouldn't want to bet on for the future]. If you start with the contribution table and do left joins, you're only going to get at most one transaction.

CiviCRM Community Forums (archive)

News:

Author Topic: join between `civicrm_contribution` and `civicrm_payment_processor` (Read 408 times)

Erich Schulz

join between `civicrm_contribution` and `civicrm_payment_processor`

Erich Schulz

Re: join between `civicrm_contribution` and `civicrm_payment_processor`

adixon

Re: join between `civicrm_contribution` and `civicrm_payment_processor`