Author Topic: Adding contribution source to donor (retail) report (Read 770 times)

fen · December 05, 2011, 08:09:40 am

My client would like to be able to choose (one or more) contribution source options when creating a donor (detail) report. I created the following DnowDetail.php which shows the column and created the source column and enables choosing from the source options, but `source` is ignored in the query. It appears to me that Form.php would pull in the filter (and it shows up in smartyDebug) but it's not working. Do I need to add an additional WHERE clause (not sure of format yet)? Or is there something simpler I'm missing?

Code: [Select]

class CRM_Report_Form_Contribute_DnowDetail extends CRM_Report_Form_Contribute_Detail {

    function __construct( ) {
        parent::__construct( );
        $this->_columns['civicrm_contribution']['fields'] = $this->_columns['civicrm_contribution']['fields'] + $this->getSourceField();
        $this->_columns['civicrm_contribution']['filters'] = $this->_columns['civicrm_contribution']['filters'] + $this->getSourceFilter();
    }

    function preProcess( ) {
        parent::preProcess( );
        $this->assign( 'reportTitle', ts('DNow Donor Report (Detail)' ) );
        $this->_sourceList = $this->getSourceList();
    }

    function getSourceField() {
        return array('source' =>
            array('title' => ts('Source'), 'default' => null));
    }

    function getSourceFilter() {
        return array(
            'source' =>
            array(
                'title'        => ts( 'Source' ),
                'operatorType' => CRM_Report_Form::OP_MULTISELECT,
                'options'      => $this->getSourceList() ) );
    }

    // There's probably a better way to cache the source list
    // For one thing, it's not clear how and when this list can be refreshed
    function getSourceList() {
        static $list = array();
        if (! $list) {
            $query = "SELECT DISTINCT source FROM civicrm_contribution ORDER BY source DESC";
            $dao = CRM_Core_DAO::executeQuery($query);
            while ($source = $dao->fetch()) {
                $list[] = $dao->source;
            }
        }
        return $list;
    }
}

Thanks!
=Fen

fen · December 05, 2011, 01:22:49 pm

Solved (sort of, anyway). It turns out that $this->_where contains:

Code: [Select]

WHERE ( contribution_civireport.contribution_status_id IN (1) ) AND ( contribution_civireport.source IN (1) ) AND contact_civireport.is_deleted = 0

The "contribution_civireport.source IN (1)" part is the troublesome part, as it's using the index number of the source choices when it should be using the actual source name. So I hacked a fix by building my own WHERE clause - pretty ugly but functional:

Code: [Select]

    // Replace source field indexes with their values
    // Super hackish, but hey! it works
    function where( ) {
        parent::where( );
        if (preg_match('/^(.* contribution_civireport.source IN \()([^\)]*)(\).*)$/', $this->_where, $matches)) {
            $sources = array();
            $list  = $this->getSourceList();
            $where = $matches[1];
            $nums  = explode(',',$matches[2]);
            $rest  = $matches[3];
            foreach ($nums as $num) {
                $sources[] = $list[$num];
            }
            $where .= "'" . implode("','", $sources) . "'" . $rest;
            $this->_where = $where;
        }
    }

If there's a cleaner way to do all this, I'd love to know it.

Thanks!
=Fen

Deepak Srivastava · December 05, 2011, 11:07:50 pm

Its the index of array that is considered in the where clause. So if the $list array is changed to something like :

Code: [Select]

            while ($source = $dao->fetch()) {
                $list[$dao->source] = $dao->source;
            }

contribution_civireport.source IN clause would consider the text and not number. You might have to take care of escaping.

CiviCRM Community Forums (archive)

News:

Author Topic: Adding contribution source to donor (retail) report (Read 770 times)

fen

Adding contribution source to donor (retail) report

fen

Re: Adding contribution source to donor (retail) report

Deepak Srivastava

Re: Adding contribution source to donor (retail) report