CiviCRM Community Forums (archive)

I suspect you can accomplish this using a custom template for your custom report (i.e. dont use the default template)

if the group is a column in the DAO, you can basically output it in smarty using smarty commands when the group changes. So if you query gets a list of

contact, email, group name

ordered by group name, you can use smarty to decide when to output the group header

lobo

Thought I would post back some results... a total of 15 hours, and that's only the scripting for the report. Still to do the template!

Took a bit of code from Contact Summary and a bit from Membership Summary to get the required columns (before I figured out how to do that myself!)

Declarations: (yes, a custom php file as well - stored in custom scripts directory)
require_once 'CRM/Core/Custom/Reports.php';

protected $_addressField = false;
protected $_emailField = false;
protected $_phoneField = false;
protected $_groupField = false;

The 'Group' column: (This was very hard to debug to get right - my knowledge of php and HTML_QuickForm is limited)
'civicrm_group' =>
    array(
        'dao' => 'CRM_Contact_DAO_Group',
        'fields' =>
        array(
            'gid' =>
            array(
                'name' => 'id',
                'title' => ts( 'Group Name'), 
                'default' => true
            ),
        ),
        'filters'=>
        array(
            'gid' =>
            array(
                'name' => 'id',
                'title' => ts( 'Group' ),
                'operatorType' => CRM_Report_Form::OP_MULTISELECT,
                'options' => CRM_Core_PseudoConstant::group( )
            ),
        ),
    ),

The 'Select SQL stuff:
function select( ) {
    $select = $this->_columnHeaders = array( );

    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;
                    } else if ( $tableName == 'civicrm_phone' ) {
                        $this->_phoneField = true;
                    // add group field
                    } else if ( $tableName == 'civicrm_group' ) {
                        $this->_groupField = true;
                    }
                   
                    $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
                    $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $field['title'];
                    $this->_columnHeaders["{$tableName}_{$fieldName}"]['type']  = CRM_Utils_Array::value( 'type', $field );
                }
            }
        }
    }
    $this->_select = "SELECT " . implode( ', ', $select ) . " ";
}

The 'From' SQL stuff:
function from( ) {
    $this->_from = null;

    $this->_from = "FROM  civicrm_contact {$this->_aliases['civicrm_contact']} {$this->_aclFrom} ";

    //used when address field is selected
    if ( $this->_addressField ) {
        $this->_from .= "
            LEFT JOIN civicrm_address {$this->_aliases['civicrm_address']}
            ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_address']}.contact_id AND {$this->_aliases['civicrm_address']}.is_primary = 1\n";
    }
   
    //used when group field is selected
    if ( $this->_groupField ) {
        $this->_from .= "
            LEFT JOIN civicrm_group_contact gc ON {$this->_aliases['civicrm_contact']}.id = gc.contact_id AND gc.status = 'Added'
            LEFT JOIN civicrm_group {$this->_aliases['civicrm_group']} ON {$this->_aliases['civicrm_group']}.id = gc.group_id";
       
    }

    //used when email field is selected
    if ( $this->_emailField ) {
        $this->_from .= "
            LEFT JOIN civicrm_email {$this->_aliases['civicrm_email']}
            ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_email']}.contact_id AND {$this->_aliases['civicrm_email']}.is_primary = 1\n";
    }
   
    //used when phone field is selected
    if ( $this->_phoneField ) {
        $this->_from .= "
            LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']}
            ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id AND {$this->_aliases['civicrm_phone']}.is_primary = 1\n";
    }
}

Additions to the 'alterDisplay' function (to replace the group id with the name/title of the group)
if ( array_key_exists('civicrm_group_gid', $row) ) {
    if ( $value = $row['civicrm_group_gid'] ) {
        $rows[$rowNum]['civicrm_group_gid'] = CRM_Core_Custom_Reports::groupGetGroupNameByID( $value );
    }
    $entryFound = true;
}

The 'custom' script:
class CRM_Core_Custom_Reports
{

    private static $groupGetGroupNameByID;

    public static function &groupGetGroupNameByID( $groupID = false ) {
        if ( $groupID != null ) {
            // Specify the single result query to be executed
            $query = "
                SELECT civicrm_group.title title, civicrm_group.id id
                FROM civicrm_group
                WHERE id = %1
                ";   
            $params = array( 1 => array( $groupID, 'Integer' ) );
            return CRM_Core_DAO::singleValueQuery( $query, $params );
        }
    }
}


This report script works well using the default tpl template . Now I just have to create one that summarises and groups the groups, if required. I can attach the full code if required, but I'm assuming this will be better in the documentation section, let me know.