CiviCRM Community Forums (archive)

I'm trying to get a custom field to sum() in a report and it's almost there but not quite....Any help would be appreciated.

The column heading is displaying as Array and the total is coming up as 400 but it should only be 100.

The report is attached. Around line 139 and then line 214 I think is the lines that's having issues.

(3:05:55 PM) eileen: Hi frtommy - I did a report that calculated on custom fields but to be honest I didn't realise you could add them into the columns array - I think I got an error, so I added them as sql into the select clause & group by
(3:07:17 PM) frtommy left the room (quit: Quit: Page closed).
(3:07:18 PM) eileen: But not quite sure I understand what you have in your DB - do you have 2 records & each has a value of 100

I'm not quite sure! Perhaps you could see what is in this value

        $this->_select

right before the function ends

When I did a custom report showing sums for custom fields I didn't think (rightly or wrongly) I could do it the way you are trying to. Here's my code - In this case it's a commission field which is aggregated for each worker


<?php

/*
 +--------------------------------------------------------------------+
 | CiviCRM version 3.1                                                |

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

require_once 'CRM/Report/Form.php';
require_once 'CRM/Contribute/PseudoConstant.php';

class CRM_Report_Form_Contribute_Commission extends CRM_Report_Form {
    protected $_addressField = false;

    protected $_emailField   = false;

    protected $_summary      = null;
  //The Entity defined in the line determines what custom fields are added to the report
//protected $_customGroupExtends = array( 'Contribution' );

    function __construct( ) {
        $this->_columns = 
            array( 'civicrm_contact'      =>
                   array( 'dao'     => 'CRM_Contact_DAO_Contact',
                          'fields'  =>
                          array(),
                          'filters' =>             
                          array('sort_name'    => 
                                array( 'title'      => ts( 'Contact Name' ),
                                       'operator'   => 'like' ),
                                'id'    => 
                                array( 'title'      => ts( 'Contact ID' ),
                                       'no_display' => true ), ),
                          'grouping'=> 'contact-fields',
                          ),
 
  
  

                   'civicrm_contribution' =>
                   array( 'dao'     => 'CRM_Contribute_DAO_Contribution',
                          'fields'  =>
                          array(
                                 'contribution_type_id' => array( 'title'   => ts('Contribution Type'),
                                                                  'default' => true,
                                                                ),
                                 'trxn_id'              => null,
                                 'receive_date'         => array( 'default' => true ),
                                 'receipt_date'         => null,
                                 'total_amount'         => array( 'title'        => ts( 'Amount' ),
                                                                    'required'     => true,
                                                                    'statistics'   => 
                                                                          array('sum' => ts( 'Donations' )),
                                                                  ),
                                 ),
                          'filters' =>             
                          array( 'receive_date'           => 
                                    array( 'operatorType' => CRM_Report_Form::OP_DATE ),
                                 'contribution_type_id'   =>
                                    array( 'title'        => ts( 'Contribution Type' ), 
                                           'operatorType' => CRM_Report_Form::OP_MULTISELECT,
                                           'options'      => CRM_Contribute_PseudoConstant::contributionType( )
                                         ),
                                'contribution_status_id' => 
                                    array( 'title'        => ts( 'Contribution Status' ), 
                                        'operatorType' => CRM_Report_Form::OP_MULTISELECT,
                                        'options'      => CRM_Contribute_PseudoConstant::contributionStatus( ),
                                        'default'      => array( 1 ),
                                        ),
                                 'total_amount'           => 
                                    array( 'title'        => ts( 'Contribution Amount' ) ),
                                 ),
                          'grouping'=> 'contri-fields',
                          ),
                   
                   'civicrm_group' => 
                   array( 'dao'    => 'CRM_Contact_DAO_GroupContact',
                          'alias'  => 'cgroup',
                          'filters' =>             
                          array( 'gid' => 
                                 array( 'name'         => 'group_id',
                                        'title'        => ts( 'Group' ),
                                        'operatorType' => CRM_Report_Form::OP_MULTISELECT,
                                        'group'        => true,
                                        'options'      => CRM_Core_PseudoConstant::group( ) ), ), ),
                   
                   'civicrm_contribution_ordinality' =>                    
                   array( 'dao'    => 'CRM_Contribute_DAO_Contribution',
                          'alias'  => 'cordinality',
                          'filters' =>             
                          array( 'ordinality' => 
                                 array( 'title'   => ts( 'Contribution Ordinality' ),
                                        'operatorType' => CRM_Report_Form::OP_MULTISELECT,
                                        'options'      => array( 0 => 'First by Contributor', 
                                                                 1 => 'Second or Later by Contributor') ), ), ),
                                                                 

                                                                
                                                                 );

        $this->_tagFilter = true;
        parent::__construct( );
    }

    function preProcess( ) {
        parent::preProcess( );
    }

    function select( ) {
        $select = array( );

        $this->_columnHeaders = array( );
        // add worker first so it comes first
        $this->_columnHeaders['civicrm_value_contribution_data_3_worker_select_50'] =   
            array ('title' => 'Worker',
                  
        );
        foreach ( $this->_columns as $tableName => $table ) {
            if ( array_key_exists('fields', $table) ) {
                foreach ( $table['fields'] as $fieldName => $field ) {
                    if ( CRM_Utils_Array::value( 'required', $field ) ||
                         CRM_Utils_Array::value( $fieldName, $this->_params['fields'] ) ) {
                        if ( $tableName == 'civicrm_address' ) {
                            $this->_addressField = true;
                        } else if ( $tableName == 'civicrm_email' ) {
                            $this->_emailField = true;
                        }
                        
                        // only include statistics columns if set
                        if ( CRM_Utils_Array::value('statistics', $field) ) {
                            foreach ( $field['statistics'] as $stat => $label ) {
                                switch (strtolower($stat)) {
                                case 'sum':
                                    $select[] = "SUM({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}";
                                    $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
                                    $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type']  = 
                                        $field['type'];
                                    $this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}";
                                    break;
                                case 'count':
                                    $select[] = "COUNT({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}";
                                    $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
                                    $this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}";
                                    break;
                                case 'avg':
                                    $select[] = "ROUND(AVG({$field['dbAlias']}),2) as {$tableName}_{$fieldName}_{$stat}";
                                    $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type']  =  
                                        $field['type'];
                                    $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
                                    $this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}";
                                    break;
                                }
                            }   
                            
                        } else {
                            $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
                            $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $field['title'];
                            $this->_columnHeaders["{$tableName}_{$fieldName}"]['type']  = CRM_Utils_Array::value( 'type', $field );

                        }
                    }
                }
            }
        }
        // add custom fields manually here
        $this->_columnHeaders['civicrm_value_contribution_data_3_commission__dollars__183_sum'] = 
                  array ('title' => 'Commission',
                    'type' => 1024,
        );
        $select[] = "value_contribution_data_3_civireport.worker_select_50 as civicrm_value_contribution_data_3_worker_select_50";
        $select[]  ="SUM(value_contribution_data_3_civireport.commission__dollars__183) as civicrm_value_contribution_data_3_commission__dollars__183_sum";
        $this->_select = "SELECT " . implode( ', ', $select ) . " ";


 

    }

    function from( ) {
        $this->_from = null;

        $this->_from = "
        FROM  civicrm_contact      {$this->_aliases['civicrm_contact']} {$this->_aclFrom}
              INNER JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']} 
                      ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_contribution']}.contact_id AND {$this->_aliases['civicrm_contribution']}.is_test = 0
              INNER JOIN (SELECT c.id, IF(COUNT(oc.id) = 0, 0, 1) AS ordinality FROM civicrm_contribution c LEFT JOIN civicrm_contribution oc ON c.contact_id = oc.contact_id AND oc.receive_date < c.receive_date GROUP BY c.id) {$this->_aliases['civicrm_contribution_ordinality']} 
                      ON {$this->_aliases['civicrm_contribution_ordinality']}.id = {$this->_aliases['civicrm_contribution']}.id
              INNER JOIN civicrm_value_contribution_data_3 value_contribution_data_3_civireport 
                      ON contribution_civireport.id = value_contribution_data_3_civireport.entity_id
                      
";
        
        
    }


    function groupBy( ) {
        $this->_groupBy = " GROUP BY value_contribution_data_3_civireport.worker_select_50";
    }

    function orderBy( ) {
        $this->_orderBy = " ORDER BY value_contribution_data_3_civireport.worker_select_50 ";
    }

    function statistics( &$rows ) {
        $statistics = parent::statistics( $rows );

        $select = "
        SELECT COUNT(value_contribution_data_3_civireport .commission__dollars__183  ) as count,
               SUM( value_contribution_data_3_civireport .commission__dollars__183 ) as amount,
               ROUND(AVG(value_contribution_data_3_civireport.commission__dollars__183), 2) as avg
        ";

        $sql = "{$select} {$this->_from} {$this->_where}";
        $dao = CRM_Core_DAO::executeQuery( $sql );

        if ( $dao->fetch( ) ) {
            $statistics['counts']['amount']    = array( 'value' => $dao->amount,
                                                        'title' => 'Total Amount',
                                                        'type'  => CRM_Utils_Type::T_MONEY );
            $statistics['counts']['avg']       = array( 'value' => $dao->avg,
                                                        'title' => 'Average',
                                                        'type'  => CRM_Utils_Type::T_MONEY );
        }

        return $statistics;
    }

    function postProcess( ) {
        // get the acl clauses built before we assemble the query
        $this->buildACLClause( $this->_aliases['civicrm_contact'] );
        parent::postProcess( );
    }

    function alterDisplay( &$rows ) {
        // custom code to alter rows
        $checkList  = array();
        $entryFound = false;
        $display_flag = $prev_cid = $cid =  0;
        $contributionTypes = CRM_Contribute_PseudoConstant::contributionType( );
           //     [_where] => WHERE ( receive_date >= 20100101000000 ) AND ( receive_date <= 20101231235959 )
           // drupal_set_message("<pre>".print_r($this->_where,true )."</pre>");
           if (preg_match('/receive_date >= ([0-9]{8})/',$this->_where,$matches)){
           $startdate = $matches[1];
       $startClause = "&receive_date_from={$startdate}";
       }   

           if (preg_match('/receive_date <= ([0-9]{8})/',$this->_where,$matches)){
           $enddate = $matches[1];
       $endClause = "&receive_date_to={$enddate}";
       }       
        foreach ( $rows as $rowNum => $row ) {
      
            if ( !empty($this->_noRepeats) && $this->_outputMode != 'csv' ) {
                // don't repeat contact details if its same as the previous row
                if ( array_key_exists('civicrm_contact_id', $row ) ) {
                    if ( $cid =  $row['civicrm_contact_id'] ) {
                        if ( $rowNum == 0 ) {
                            $prev_cid = $cid;
                        } else {
                            if( $prev_cid == $cid ) {
                                $display_flag = 1;
                                $prev_cid = $cid;
                            } else {
                                $display_flag = 0;
                                $prev_cid = $cid;
                            }
                        }
                        
                        if ( $display_flag ) {
                            foreach ( $row as $colName => $colVal ) {
                                if ( in_array($colName, $this->_noRepeats) ) {
                                    unset($rows[$rowNum][$colName]);          
                                }
                            }
                        }
                        $entryFound = true;
                    }
                }
            }
            
            // handle state province
            if ( array_key_exists('civicrm_address_state_province_id', $row) ) {
                if ( $value = $row['civicrm_address_state_province_id'] ) {
                    $rows[$rowNum]['civicrm_address_state_province_id'] = 
                        CRM_Core_PseudoConstant::stateProvince( $value, false );

                    $url = CRM_Report_Utils_Report::getNextUrl( 'contribute/detail',
                                                                "reset=1&force=1&" . 
                                                                "state_province_id_op=in&state_province_id_value={$value}",
                                                                $this->_absoluteUrl, $this->_id );
                    $rows[$rowNum]['civicrm_address_state_province_id_link' ] = $url;
                    $rows[$rowNum]['civicrm_address_state_province_id_hover'] =
                        ts("List all contribution(s) for this State.");
                }
                $entryFound = true;
            }

            // handle country
            if ( array_key_exists('civicrm_address_country_id', $row) ) {
                if ( $value = $row['civicrm_address_country_id'] ) {
                    $rows[$rowNum]['civicrm_address_country_id'] = 
                        CRM_Core_PseudoConstant::country( $value, false );

                    $url = CRM_Report_Utils_Report::getNextUrl( 'contribute/detail',
                                                                "reset=1&force=1&" . 
                                                                "country_id_op=in&country_id_value={$value}",
                                                                $this->_absoluteUrl, $this->_id );
                    $rows[$rowNum]['civicrm_address_country_id_link' ] = $url;
                    $rows[$rowNum]['civicrm_address_country_id_hover'] = 
                        ts("List all contribution(s) for this Country.");
                }
                
                $entryFound = true;
            }

            // convert display name to links
            if ( array_key_exists('civicrm_contact_display_name', $row) && 
                 CRM_Utils_Array::value( 'civicrm_contact_display_name', $rows[$rowNum] ) && 
                 array_key_exists('civicrm_contact_id', $row) ) {
                $url = CRM_Utils_System::url( "civicrm/contact/view"  , 
                                              'reset=1&cid=' . $row['civicrm_contact_id'],
                                              $this->_absoluteUrl );
                $rows[$rowNum]['civicrm_contact_display_name_link' ] = $url;
                $rows[$rowNum]['civicrm_contact_display_name_hover'] =  
                    ts("View Contact Summary for this Contact.");
            }
            

                    // convert display name to links
                    //. "receive_date_from={$dateStart}&receive_date_to={$dateEnd}",
                    
            if ( array_key_exists('civicrm_value_contribution_data_3_worker_select_50', $row) && 
                 CRM_Utils_Array::value( 'civicrm_value_contribution_data_3_worker_select_50', $rows[$rowNum] ) ) {
                $url = CRM_Utils_System::url( "civicrm/report/instance/26"  , 
                                              'force=1&worker=' . $row['civicrm_value_contribution_data_3_worker_select_50'].$startClause .$endClause,
                                              $this->_absoluteUrl );
                $rows[$rowNum]['civicrm_value_contribution_data_3_worker_select_50_link' ] = $url;
                $rows[$rowNum]['civicrm_value_contribution_data_3_worker_select_50_hover'] =  
                    ts("View details for this Worker.");
            }
            
            if ( $value = CRM_Utils_Array::value( 'civicrm_contribution_contribution_type_id', $row ) ) {
                $rows[$rowNum]['civicrm_contribution_contribution_type_id'] = $contributionTypes[$value];
                $entryFound = true;
            }

            // skip looking further in rows, if first row itself doesn't 
            // have the column we need
            if ( !$entryFound ) {
              //don't break as most likely first entry is null as not all have workers
                //break;
            }
            $lastKey = $rowNum;
        }
    }

}


You might be able to know a join out here

             LEFT JOIN civicrm_contact {$this->_aliases['civicrm_contact']}
                    ON ({$this->_aliases['civicrm_activity']}.source_contact_id = {$this->_aliases['civicrm_contact']}.id OR
                         target_activity.target_contact_id = {$this->_aliases['civicrm_contact']}.id                      OR
                         assignment_activity.assignee_contact_id = {$this->_aliases['civicrm_contact']}.id )

Yeah - so it's joining to the activity table on the source & the assigned contact I think - I'd try cutting out clauses around the 'OR' - perhaps after the first OR

Looks like there are 3 contacts - source, target & assigned & the activity could show for each

& the extra 100 is your test?

This is the most recent version of the report. If anyone can help figure out how to prevent the report from duplicating an activity when there are 2 contacts entered in the With Contact field.

To sum up the issue: I have an Activity type called Marriage. I have a custom data field that is included in certain activities to enter the number of people in attendance at that Activity. For the Marriage Activity I enter the 2 spouses in the With Contact and the clergy that officiated the ceremony in the Assigned To field. The issue is that the report is seeing each Activity as being doubled because there are 2 contacts in the With Contact field. I thought about hard coding the report to just divide by 2 but that won't work because I have other Activities that will be included on this report that will also have Number in Attendance and will need to be totaled.

I'm clueless when it comes to SQL Joins.

SELECT SQL_CALC_FOUND_ROWS contact_civireport.id as civicrm_contact_id, DATE_SUB(activity_civireport.activity_date_time, INTERVAL (DAYOFMONTH(activity_civireport.activity_date_time)-1) DAY) as civicrm_activity_activity_date_time_start, MONTH(activity_civireport.activity_date_time) AS civicrm_activity_activity_date_time_subtotal, MONTHNAME(activity_civireport.activity_date_time) AS civicrm_activity_activity_date_time_interval, activity_civireport.activity_type_id as civicrm_activity_activity_type_id, COUNT(DISTINCT(activity_civireport.id)) as civicrm_activity_id_count, IFNULL(SUM(activity_civireport.duration), 0) as civicrm_activity_duration_sum, IFNULL(SUM(value_further_info_3_civireport.number_attending_19), 0) as civicrm_value_further_info_3_custom_19_sum 
        FROM civicrm_activity activity_civireport
       
             LEFT JOIN civicrm_activity_target target_activity
                    ON activity_civireport.id = target_activity.activity_id
             LEFT JOIN civicrm_activity_assignment assignment_activity
                    ON activity_civireport.id = assignment_activity.activity_id
             LEFT JOIN civicrm_contact contact_civireport
                    ON (activity_civireport.source_contact_id = contact_civireport.id )
             
             LEFT JOIN civicrm_option_value
                    ON ( activity_civireport.activity_type_id = civicrm_option_value.value )
             LEFT JOIN civicrm_option_group
                    ON civicrm_option_group.id = civicrm_option_value.option_group_id
             LEFT JOIN civicrm_case_activity
                    ON civicrm_case_activity.activity_id = activity_civireport.id
             LEFT JOIN civicrm_case
                    ON civicrm_case_activity.case_id = civicrm_case.id
             LEFT JOIN civicrm_case_contact
                    ON civicrm_case_contact.case_id = civicrm_case.id
                     
LEFT JOIN civicrm_value_further_info_3 value_further_info_3_civireport ON value_further_info_3_civireport.entity_id = activity_civireport.id  WHERE civicrm_option_group.name = 'activity_type' AND
                                activity_civireport.is_test = 0 AND
                                activity_civireport.is_deleted = 0 AND
                                activity_civireport.is_current_revision = 1 AND ( activity_date_time >= 20100801000000 ) AND ( activity_date_time <= 20100831235959 ) AND ( activity_civireport.activity_type_id IN (34, 35, 36, 42, 43, 44, 45, 46, 47, 48, 49, 50) ) AND ( activity_civireport.status_id IN (2) ) AND contact_civireport.is_deleted = 0  GROUP BY YEAR(activity_civireport.activity_date_time), MONTH(activity_civireport.activity_date_time), activity_civireport.activity_type_id      LIMIT 0, 50

I do have access to phpmyadmin