CiviCRM Community Forums (archive)

*

News:

Have a question about CiviCRM?
Get it answered quickly at the new
CiviCRM Stack Exchange Q+A site
This forum was archived on 25 November 2017. Learn more.
How to get involved.
What to do if you think you've found a bug.



  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Using CiviCRM »
  • Using CiviReport (Moderator: Dave Greenberg) »
  • Custom Report Generation:
Pages: [1]

Author Topic: Custom Report Generation:  (Read 2953 times)

gdoteof

  • Guest
Custom Report Generation:
January 15, 2010, 02:05:38 pm
I have basically combined and copied from the Contribution/Detail.php and the Contact/Summary.php

I am getting "Database Error Code: Unknown column 'value_ece_business_and_person_1_civireport.county_1' in 'field list', 1054"


One of the tables I have is 'value_ece_business_and_person_1  I don't know why the _civireport is being appeneded.  But what I really don't understand what is going on at all, so I could be doing this wrong.  The relevant part is this top part: especially "'dao' => 'CRM_Core_DAO_CustomGroup',"

Code: [Select]
$query = 'SELECT id, table_name FROM civicrm_custom_group WHERE is_active = 1 AND extends = "Contact"';
            $dao = CRM_Core_DAO::executeQuery( $query );
            while ( $dao->fetch( ) ) {
               
                // Assemble the fields for this custom data group
                $fields = array();
                $query = 'SELECT column_name, label FROM civicrm_custom_field WHERE is_active = 1 AND custom_group_id = ' . $dao->id;
                $dao_column = CRM_Core_DAO::executeQuery( $query );
                while ( $dao_column->fetch( ) ) {
                    $fields[$dao_column->column_name] = array(
                                                              'title' => $dao_column->label,
                                                              );
                }
               
                // Add the custom data table and fields to the report column options
                $this->_columns[$dao->table_name] = array(
                                                          'dao' => 'CRM_Core_DAO_CustomGroup',
                                                          'fields' => $fields,
                                                          );
            }

Code: [Select]
<?php


require_once 'CRM/Report/Form.php';

class CRM_Report_Form_Contact_UPVaft extends CRM_Report_Form {

    protected $_summary      = null;

    protected $_emailField   = false;
    
    protected $_phoneField   = false;
    
    
    function __construct( ) {
        $this->_columns = 
            array( 'civicrm_contact' =>
                   array( 'dao'       => 'CRM_Contact_DAO_Contact',
                          'fields'    =>
                          array( 'display_name' => 
                                 array( 'title'     => ts( 'Contact Name' ),
                                        'required'  => true,
                                        'no_repeat' => true ),

                                 'id'           => 
                                 array( 'no_display'=> true,
                                        'required'  => true, ), ),
                          'filters'   =>             
                          array( 'sort_name'    => 
                                 array( 'title'      => ts( 'Contact Name' )  ),
                                 'source'       => 
                                 array( 'title'      => ts( 'Contact Source' ),
                                        'type'       => CRM_Utils_Type::T_STRING ),
                                 'id'           => 
                                 array( 'title'      => ts( 'Contact ID' ),
                                        'no_display' => true ), ),
                          'grouping'  => 'contact-fields',
                          ),
                   'civicrm_email'   =>
                   array( 'dao'       => 'CRM_Core_DAO_Email',
                          'fields'    =>
                          array( 'email' => 
                                 array( 'title'      => ts( 'Email' ),
                                        'no_repeat'  => true 
                                        ),
                                 ),
                          'grouping'  => 'contact-fields', 
                          ),
                   
                   'civicrm_address' =>
                   array( 'dao'       => 'CRM_Core_DAO_Address',
                          'grouping'  => 'contact-fields',
                          'fields'    =>
                          array( 'street_address'    => 
                                 array( 'default' => true ),
                                 'city'              => 
                                 array( 'default' => true ),
                                 'postal_code'       => null,
                                 'state_province_id' => 
                                 array( 'title'   => ts( 'State/Province' ), ),
                                 'country_id'        => 
                                 array( 'title'   => ts( 'Country' ),  
                                        'default' => true ), 
                                 ),
                          'filters'   =>             
                          array( 'country_id' => 
                                 array( 'title'   => ts( 'Country' ),
                                        'operatorType' => CRM_Report_Form::OP_MULTISELECT,
                                        'options' => CRM_Core_PseudoConstant::country( ),
                                        ), 
                                 'state_province_id' =>  
                                 array( 'title'   => ts( 'State / Province' ), 
                                        'operatorType' => CRM_Report_Form::OP_MULTISELECT,
                                        'options' => CRM_Core_PseudoConstant::stateProvince( ), ), 
                                 ), 
                          ),
                   'civicrm_phone' => 
                   array( 'dao'       => 'CRM_Core_DAO_Phone',
                          'fields'    =>
                          array( 'phone'  => null),
                          'grouping'  => 'contact-fields',
                          ),

                   'civicrm_group' => 
                   array( 'dao'    => 'CRM_Contact_DAO_Group',
                          '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( ) 
                                        ), 
                                 ), 
                          ),
                   );

            
            $query = 'SELECT id, table_name FROM civicrm_custom_group WHERE is_active = 1 AND extends = "Contact"';
            $dao = CRM_Core_DAO::executeQuery( $query );
            while ( $dao->fetch( ) ) {
                
                // Assemble the fields for this custom data group
                $fields = array();
                $query = 'SELECT column_name, label FROM civicrm_custom_field WHERE is_active = 1 AND custom_group_id = ' . $dao->id;
                $dao_column = CRM_Core_DAO::executeQuery( $query );
                while ( $dao_column->fetch( ) ) {
                    $fields[$dao_column->column_name] = array(
                                                              'title' => $dao_column->label,
                                                              );
                }
                
                // Add the custom data table and fields to the report column options
                $this->_columns[$dao->table_name] = array(
                                                          'dao' => 'CRM_Core_DAO_CustomGroup',
                                                          'fields' => $fields,
                                                          );
            }
        
        
        parent::__construct( );
    }
    
    function preProcess( ) {
        parent::preProcess( );
    }
    
    function select( ) {
        $select = array( );
        $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_email' ) {
                            $this->_emailField = true;
                        } else if ( $tableName == 'civicrm_phone' ) {
                            $this->_phoneField = true;
                        }

                        $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
                        $this->_columnHeaders["{$tableName}_{$fieldName}"]['type']  = CRM_Utils_Array::value( 'type', $field );
                        $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $field['title'];
                    }
                }
            }
        }

        $this->_select = "SELECT " . implode( ', ', $select ) . " ";
    }

    static function formRule( &$fields, &$files, $self ) {  
        $errors = $grouping = array( );
        return $errors;
    }

    function from( ) {
        $this->_from = "
        FROM civicrm_contact {$this->_aliases['civicrm_contact']} {$this->_aclFrom}
            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 ) ";
        
        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) ";
        }

        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 ";
        }   
    }

    function where( ) {
        $clauses = array( );
        $this->_having = '';
        foreach ( $this->_columns as $tableName => $table ) {
            if ( array_key_exists('filters', $table) ) {
                foreach ( $table['filters'] as $fieldName => $field ) {
                    $clause = null;
                    $op = CRM_Utils_Array::value( "{$fieldName}_op", $this->_params );
                    if ( $op ) {
                        $clause = 
                            $this->whereClause( $field,
                                                $op,
                                                CRM_Utils_Array::value( "{$fieldName}_value", $this->_params ),
                                                CRM_Utils_Array::value( "{$fieldName}_min", $this->_params ),
                                                CRM_Utils_Array::value( "{$fieldName}_max", $this->_params ) );
                    }
                    
                    if ( ! empty( $clause ) ) {
                        if ( CRM_Utils_Array::value( 'group', $field ) ) {
                            $clauses[ ] = $this->whereGroupClause( $clause );
                        } else {
                            $clauses[ ] = $clause;
                        }
                    }
                }
            }
        }
        
        if ( empty( $clauses ) ) {
            $this->_where = "WHERE ( 1 ) ";
        } else {
            $this->_where = "WHERE " . implode( ' AND ', $clauses );
        }

        if ( $this->_aclWhere ) {
            $this->_where .= " AND {$this->_aclWhere} ";
        }            
        
    }

    function groupBy( ) {
        $this->_groupBy = "GROUP BY {$this->_aliases['civicrm_contact']}.id";
    }
    
    function postProcess( ) {

        $this->beginPostProcess( );

        // get the acl clauses built before we assemble the query
        $this->buildACLClause( $this->_aliases['civicrm_contact'] );

        $sql  = $this->buildQuery( true );
             
        $rows = $graphRows = array();
        $this->buildRows ( $sql, $rows );
        
        $this->formatDisplay( $rows );
        $this->doTemplateAssignment( $rows );
        $this->endPostProcess( $rows );
    }

    function alterDisplay( &$rows ) {
        // custom code to alter rows
        $entryFound = false;
        foreach ( $rows as $rowNum => $row ) {
            // make count columns point to detail report
            // convert display name to links
            if ( array_key_exists('civicrm_contact_display_name', $row) && 
                 array_key_exists('civicrm_contact_id', $row) ) {
                $url = CRM_Report_Utils_Report::getNextUrl( 'contact/detail', 
                                              'reset=1&force=1&id_op=eq&id_value=' . $row['civicrm_contact_id'],
                                              $this->_absoluteUrl, $this->_id );
                $rows[$rowNum]['civicrm_contact_display_name_link' ] = $url;
                $rows[$rowNum]['civicrm_contact_display_name_hover'] = ts("View Contact details for this contact.");
                $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 );
                }
                $entryFound = true;
            }
            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 );
                }
                $entryFound = true;
            }


            // skip looking further in rows, if first row itself doesn't 
            // have the column we need
            if ( !$entryFound ) {
                break;
            }
        }
    }
}

gdoteof

  • Guest
Re: Custom Report Generation:
January 15, 2010, 09:10:15 pm
I am also trying to use drupal's form api with civicrm api.

using civicrm_get_contact or civicrm_search_contacts(array('custom_1'=>'Foo')) both work

however, I don't know how to access custom_1 for the values returned from these.

Code: [Select]
<?php

#upvaft.module 
/**
  * Implementation of hook_menu
  **/
 function upvaft_menu() {
 $items = array();

 $items['datadump'] = array(
 'title' => "All Custom Data",
 'page callback' => 'drupal_get_form',
 'page arguments' => array('_dump_all_data_form'),
 'access callback' => TRUE,
 'type' => MENU_NORMAL_ITEM,
 );

 return $items;
 }


 function _dump_all_data_form(){
 $form['header'] = array(
 '#type' => 'markup',
 '#value' => _header(),
 );
 return $form;
 }

function _header(){
 civicrm_initialize(TRUE);
 global $civicrm_root;
 $toInclude = $civicrm_root . '/api/v2/Contact.php';
 print $toInclude; //correct file
 include_once($toInclude);
 $params = array ('contact_id' =>10);
 $result = &civicrm_contact_get( $params );
 dpm($result);  //fancy print_r from devel module
 return "The HEADer";
 }

This works, but I cannot access custom fields, which I need.
« Last Edit: January 15, 2010, 09:12:13 pm by gdoteof »

Donald Lobo

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 15963
  • Karma: 470
    • CiviCRM site
  • CiviCRM version: 4.2+
  • CMS version: Drupal 7, Joomla 2.5+
  • MySQL version: 5.5.x
  • PHP version: 5.4.x
Re: Custom Report Generation:
January 15, 2010, 11:12:44 pm

how about using:

Code: [Select]
civicrm_search_contacts(array('custom_1'=>'Foo', 'return.custom_2' => 1));

lobo
A new CiviCRM Q&A resource needs YOUR help to get started. Visit our StackExchange proposed site, sign up and vote on 5 questions

gdoteof

  • Guest
Re: Custom Report Generation:
January 16, 2010, 01:35:34 pm
<3

Well that would be *wonderful*.  But, unfortunately it doesn't seem to work, though I may be referencing the custom vars wrong.

So it's actually

civicrm_contact_search

and I do
$params = array('county_1'=>'Chittenden')
civicrm_contact_search($params);

and i get 25 contacts (their basic information)
for example:
Code: [Select]

    *
      contact_id (String, 3 characters ) 124
    *
      contact_type (String, 12 characters ) Organization
    *
      sort_name (String, 12 characters ) Alisha Adams
    *
      display_name (String, 12 characters ) Alisha Adams
    *
      do_not_email (String, 1 characters ) 0
    *
      do_not_phone (String, 1 characters ) 0
    *
      do_not_mail (String, 1 characters ) 0
    *
      do_not_sms (String, 1 characters ) 0
    *
      do_not_trade (String, 1 characters ) 0
    *
      is_opt_out (String, 1 characters ) 0
    *
      preferred_mail_format (String, 4 characters ) Both
    *
      is_deceased (String, 1 characters ) 0
    *
      organization_name (String, 12 characters ) Alisha Adams
    *
      address_id (String, 4 characters ) 1659
    *
      street_address (String, 10 characters ) 2 Pike Dr.
    *
      city (String, 7 characters ) Swanton
    *
      postal_code (String, 4 characters ) 5488
    *
      state_province_id (String, 4 characters ) 1044
    *
      state_province_name (String, 7 characters ) Vermont
    *
      state_province (String, 2 characters ) VT
    *
      country_id (String, 4 characters ) 1228
    *
      country (String, 13 characters ) United States
    *
      phone_id (String, 4 characters ) 1468
    *
      phone_type_id (String, 1 characters ) 1
    *
      phone (String, 14 characters ) (802) 524-5449
    *
      email_id (String, 3 characters ) 427
    *
      email (String, 22 characters ) alishacadams@yahoo.com
    *
      on_hold (String, 1 characters ) 0
    *
      worldregion_id (String, 1 characters ) 2
    *
      world_region (String, 43 characters ) America South, Central, North and Caribbean


if i do
$params = array('county_1'=>'Chittenden', 'return.county_1'=>1);
civicrm_contact_search($params);

I get the same 25 contacts, but only contact_id for each.

Similar results with civicrm_contact_get($params);


edit:  we are using a custom county because it is not necessarily related to their address and is more a licenscing issue, and some organizations have people names, like this organization for example)
« Last Edit: January 16, 2010, 01:37:19 pm by gdoteof »

gdoteof

  • Guest
Re: Custom Report Generation:
January 16, 2010, 01:57:36 pm
on further inspection searching for

'county_1'=>'Whatever' isn't really returning anything releavant.  It is returning a seemingly random (or possibly first) 25 items.

county_1 is the name of the field of the custom datagroup table.  Is this how I should be referencing it?

Donald Lobo

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 15963
  • Karma: 470
    • CiviCRM site
  • CiviCRM version: 4.2+
  • CMS version: Drupal 7, Joomla 2.5+
  • MySQL version: 5.5.x
  • PHP version: 5.4.x
Re: Custom Report Generation:
January 16, 2010, 03:08:48 pm

u need to refer to a custom field as

custom_FIELDID

where FIELDID is the id of that custom field in the civicrm_custom_field table

it has been that way for historical reasons :( its a bit awful (IMO), but that is what we have

lobo
A new CiviCRM Q&A resource needs YOUR help to get started. Visit our StackExchange proposed site, sign up and vote on 5 questions

gdoteof

  • Guest
Re: Custom Report Generation:
January 16, 2010, 04:24:40 pm
thanks, this is just what I was looking for.

I feel it will be easier to create custom 'reports' using this method rather than via civireports, but probably that is because I really don't understand the BAO business.
Pages: [1]
  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Using CiviCRM »
  • Using CiviReport (Moderator: Dave Greenberg) »
  • Custom Report Generation:

This forum was archived on 2017-11-26.