Author Topic: Adding Membership Data to Contribution Detail Report (Read 749 times)

CiviTeacher.com · October 25, 2011, 04:44:24 pm

I have an interesting situation that calls for new columns to be added to the Contribution Detail report if the contribution is a membership dues payment with a corresponding entry in the civicrm_membership_payment table

membership type
membership start date
membership id number
3 custom data fields for membership

This is like adding the membership data to the contribution detail report. They don't want to have to look up membership dues contributions separately from memberships, they want a single report that displays them both. They realize that contributions without a membership will have blank in the membership columns, that's ok.

Another approach might be the Constituent Detail report, so long as I could add custom membership data to the results, which it won't let me do, it gives me an error if I add "membership" to the custom fields available in the report.

What do you think is the best approach?

Eileen · October 25, 2011, 07:29:02 pm

I don't think you'll have too much trouble extending the contribution report to add membership detail - here's the code to do the opposite - add contribution data to the membership report. You can see there's not much in it.

Code: [Select]

<?php

/*
 +--------------------------------------------------------------------+
 | CiviCRM version 3.4                                                |
 +--------------------------------------------------------------------+
 | Copyright CiviCRM LLC (c) 2004-2011                                |
 +--------------------------------------------------------------------+
 | This file is a part of CiviCRM.                                    |
 |                                                                    |
 | CiviCRM is free software; you can copy, modify, and distribute it  |
 | under the terms of the GNU Affero General Public License           |
 | Version 3, 19 November 2007 and the CiviCRM Licensing Exception.   |
 |                                                                    |
 | CiviCRM is distributed in the hope that it will be useful, but     |
 | WITHOUT ANY WARRANTY; without even the implied warranty of         |
 | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
 | See the GNU Affero General Public License for more details.        |
 |                                                                    |
 | You should have received a copy of the GNU Affero General Public   |
 | License and the CiviCRM Licensing Exception along                  |
 | with this program; if not, contact CiviCRM LLC                     |
 | at info[AT]civicrm[DOT]org. If you have questions about the        |
 | GNU Affero General Public License or the licensing of CiviCRM,     |
 | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
 +--------------------------------------------------------------------+
*/

/**
 *
 * @package CRM
 * @copyright CiviCRM LLC (c) 2004-2011
 * $Id$
 *
 */

require_once 'CRM/Report/Form/Member/Detail.php';


class CRM_Report_Form_Member_FinancialDetail extends CRM_Report_Form_Member_Detail {
    
    protected $_customGroupExtends = array( 'Membership', 'Contact' );

    function __construct( ) {
    
       parent::__construct( );
       $this->_columns = $this->_columns + getContributionColumns();
    }
    
    function preProcess( ) {
      parent::preProcess( );
      $this->assign( 'reportTitle', ts('Membership Financial Detail Report' ) );

    }
    
    
    function from( ) {
      
      $extraJoin = "LEFT JOIN civicrm_membership_payment mc
                    ON mc.membership_id = {$this->_aliases['civicrm_membership']}.id
                    LEFT JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']}
                    ON mc.contribution_id = {$this->_aliases['civicrm_contribution']}.id";
        parent::from() ;
        $this->_from .= $extraJoin;
        
  
    }
    
    function getContributionColumns(){
      return array( 'civicrm_contribution' =>
                   array( 'dao'     => 'CRM_Contribute_DAO_Contribution',
                          'fields'  =>
                          array(
                                 'contribution_id' => array( 
                                                            'name' => 'id',
                                                            'no_display' => true,
                                                            'required'   => false,
                                                ),
                                 'contribution_type_id' => array( 'title'   => ts('Contribution Type'),
                                                                  'default' => true,
                                                                ),
                                'payment_instrument_id' => array( 'title'   => ts('Payment Type'),
                                                                            ),
                                 'trxn_id'              => null,
                                 'receive_date'         => array( 'default' => true ),
                                 'receipt_date'         => null,
                                 'fee_amount'           => null,
                                 'net_amount'           => null,
                                 'total_amount'         => array( 'title'        => ts( 'Amount' ),
                                                                    'required'     => true,
                                                                    'statistics'   => 
                                                                          array('sum' => ts( 'Amount' )),
                                                                  ),
                                 ),

                          'grouping'=> 'contri-fields',
                          ));
    }
    
}

CiviTeacher.com · October 26, 2011, 05:46:54 pm

Thanks a bunch Eileen. I came up with basically the same join statement as you, but it wasn't working because I forgot to put in the Membership pseudoConstant line at the top of my contribution report. FYI everyone out there, without this line, no membership data can be retrieved in a report.

Code: [Select]

require_once 'CRM/Member/PseudoConstant.php';
This attached file is the complete working report. This is a contribution report that ALSO shows membership data, where available, if the contribution is of type "memership dues". It shows not only regular fields but also custom data of both contribution and membership types.

Note that it is called CustomContribution.php but of course this could be renamed to whatever.

CiviCRM Community Forums (archive)

News:

Author Topic: Adding Membership Data to Contribution Detail Report (Read 749 times)

CiviTeacher.com

Adding Membership Data to Contribution Detail Report

Eileen

Re: Adding Membership Data to Contribution Detail Report

CiviTeacher.com

Re: Adding Membership Data to Contribution Detail Report