CiviCRM Community Forums (archive)

Hi everyone,

I just thought I'd post another simple custom search I've been working on. This one is not super crazy like the other (http://forum.civicrm.org/index.php/topic,14725.0.html), but I thought it would be nice to add a more normal iteration. Once again, I'm  working off of the example search found here: http://wiki.civicrm.org/confluence/display/CRMDOC32/Detailed+Contribution+Report+(month+or+year)

The following search returns a bunch of contact and contribution data via contribution type, user group, and date. It has some custom data manipulation, which may prove useful for someone, so I left it in. Once again, bare with me I'm not useually into OOPHP, but I'm learning  . Here is the search file:

<?php
require_once 'CRM/Contact/Form/Search/Interface.php';
require_once 'CRM/Contact/Form/Search/Custom/Base.php';

class CRM_Contact_Form_Search_Custom_ContributionsByMonth implements CRM_Contact_Form_Search_Interface {

    protected $_formValues;

    function __construct( &$formValues ) {
        $this->_formValues = $formValues;

        /**
         * Define the columns for search result rows
         */
        $this->_columns = array( ts('')        => 'contact_type'  ,
                                 ts('Name'   ) => 'sort_name'     ,
                                 ts('Address') => 'street_address',
                                 ts('City'   ) => 'city'          ,
                                 ts('State'  ) => 'state_province',
                                 ts('Postal' ) => 'postal_code'   ,
                                 ts('Country') => 'country'       ,
                                 ts('Email'  ) => 'email'         ,
                                 ts('Phone'  ) => 'phone'           ,
                                 ts('Membership Type') => 'membership_type',
                                 ts('Amount'  ) => 'amount'         ,
                                 ts('Contribution Type'  ) => 'contribution_type',
                                 ts('Receive Date'  ) => 'receive_date');
}

    function buildForm( &$form ) {
        /**
         * You can define a custom title for the search form
         */
        $this->setTitle('Contributions by Contribution and Membership Type');

        /**
         * Define the search form fields here
         */
        $yearsInPast   = 0;
        $yearsInFuture = 1;
        $dateParts     = implode( CRM_Core_DAO::VALUE_SEPARATOR, array( 'Y', 'M' ) );
        $form->add( 'date', 'contribution_date',
                    ts('Contribution Report for'),
                    CRM_Core_SelectValues::date('custom', $yearsInPast, $yearsInFuture, $dateParts ) );
        
        $membership_type = array('Any', 'cpa_member', 'cpc_member');
        $form->add('select', 'membership_type', ts('Membership type'), $membership_type);
        
        $contribution_type = array('Any','Donation', 'Member Dues', 'Campaign Contribution', 'Event Fee', 'Databank', 'CPA Donation', 'Monthly Contribution', 'Quarterly Contribution', 'Renewal');
        $form->add('select', 'contribution_type', ts('Contribution Type'), $contribution_type);
        
        $form->add( 'date',
                    'start_date',
                    ts('Start Date'),
                    CRM_Core_SelectValues::date( 'custom', 10, 0 ) );
        $form->addRule('start_date', ts('Select a valid date.'), 'qfDate');
        
        $form->add( 'date',
                    'end_date',
                    ts('End Date'),
                    CRM_Core_SelectValues::date( 'custom', 10, 0 ) );
        $form->addRule('end_date', ts('Select a valid date.'), 'qfDate');   
        
        $form->assign( 'elements', array( 'membership_type', 'contribution_type', 'start_date', 'end_date') );
        
    }

    /**
     * Define the smarty template used to layout the search form and results listings.
     */
    function templateFile( ) {
        return 'CRM/Contact/Form/Search/Custom/ContributionsByMonthSimple.tpl';
    }

    /**
     * Construct the search query
     */
    function all( $offset = 0, $rowcount = 0, $sort = null,
                  $includeContactIDs = false, $onlyIDs = false ) {

        // SELECT clause must include contact_id as an alias for civicrm_contact.id
        if ( $onlyIDs ) {
            $select  = "DISTINCT contact_a.id as contact_id";
        } else {
            $select  = "DISTINCT contact.id as contact_id,
                       contribution.id AS id,
                   contact.display_name AS sort_name,
                       email.email AS email,
                       phone.phone AS phone,
                       address.street_address AS street_address,
                       address.postal_code AS postal_code,
                       address.state_province_id AS state_province,
                       address.city AS city,
                       address.country_id AS country,
                       contribution.total_amount AS amount,
                       contribution.receive_date AS receive_date,
                       contribution.contribution_type_id AS contribution_type,
                       membership.membership_17 AS membership_type
                   ";

            /*
         SUM(cont.total_amount) as money_total,
         COUNT(cont.total_amount) as contributions_total,
         AVG(cont.total_amount) as average_contribution
            */

        }
        $from   = $this->from( );

        $where  = $this->where( $includeContactIDs );

        $having = $this->having( );
        if ( $having ) {
            $having = "  $having ";
        }
       if(!empty($where)){
        $sql = " SELECT $select  FROM   $from WHERE  $where $having ";
        }else{
        $sql = " SELECT $select  FROM   $from $having ";
        }
        //for only contact ids ignore order.
        if ( !$onlyIDs ) {
            // Define ORDER BY for query in $sort, with default value
            if ( ! empty( $sort ) ) {
                if ( is_string( $sort ) ) {
                    $sql .= " ORDER BY $sort ";
                } else {
                    $sql .= " ORDER BY " . trim( $sort->orderBy() );
                }
            } else {
                $sql .= " ";
            }
        }
          
          //CRM_Core_Error::debug('sql',$sql);
          //exit();
        return $sql;
    }
    
    function alterRow( &$row ) {
    switch($row['contribution_type']){
        case 1:
           $row['contribution_type'] = 'Donation';
        break;
        case 2:
           $row['contribution_type'] = 'Member Dues';
        break;
        case 3:
           $row['contribution_type'] = 'Campaign Contributions';
        break;
        case 4:
           $row['contribution_type'] = 'Event Fee';
        break;
        case 5:
           $row['contribution_type'] = 'Databank';
        break;
        case 6:
           $row['contribution_type'] = 'CPA Donation';
        break;
        case 7:
           $row['contribution_type'] = 'Monthly Contribution';
        break;
        case 8:
           $row['contribution_type'] = 'Quarterly Contribution';
        break;
        case 9:
           $row['contribution_type'] = 'Renewal';
        break;
    }
    
    if(!empty($row['state_province']) && !empty($row['country'])){
    $find_state = 'SELECT name FROM civicrm_state_province WHERE id = '.$row['state_province'];
    
    $dao = CRM_Core_DAO::executeQuery( $find_state, CRM_Core_DAO::$_nullArray );
    while ( $dao->fetch( ) ) {
    $row['state_province'] =  $dao->name;
    }
    
    $find_country = 'SELECT name FROM civicrm_country WHERE id = '.$row['country'];
    
    $dao = CRM_Core_DAO::executeQuery( $find_country, CRM_Core_DAO::$_nullArray );
    while ( $dao->fetch( ) ) {
    $row['country'] =  $dao->name;
    }
    }
}


    function from( ) {
        return "
            civicrm_contribution AS contribution
                LEFT JOIN civicrm_value_additional_individual_informatio_9 AS membership 
                  ON contribution.contact_id = membership.entity_id
                LEFT JOIN civicrm_contact AS contact 
                  ON contribution.contact_id = contact.id 
                LEFT JOIN civicrm_email AS email 
                  ON  contribution.contact_id = email.contact_id 
                LEFT JOIN civicrm_phone AS phone 
                  ON contribution.contact_id = phone.contact_id 
                LEFT JOIN civicrm_address as address 
                  ON contribution.contact_id = address.contact_id 
            ";
    }

    /*
     * WHERE clause is an array built from any required JOINS plus conditional filters based on search criteria field values
     *
     */
    function where( $includeContactIDs = false ) {
        $clauses   = $tempArray = array( );
        
        //$month     = $this->_formValues['contribution_date']['M'];
        //$year      = $this->_formValues['contribution_date']['Y'];
        $contribution_type = $this->_formValues['contribution_type'];
        $membership_type   = $this->_formValues['membership_type'];
        if($membership_type != 0){
          if($membership_type == 1){
            $membership_type = 'cpa_member';
          }else{
            $membership_type = 'cpc_member';
          }
          
          $clauses[] = 'membership.membership_17 LIKE "%'. $membership_type.'%"';
        }
        if ($contribution_type > '0'){
          $clauses[] = 'contribution.contribution_type_id = "'.$contribution_type.'"';
        }
        
        $start_date = $this->_formValues['start_date']['Y'].'-'.$this->_formValues['start_date']['M'].'-'.$this->_formValues['start_date']['d'];
        $end_date = $this->_formValues['end_date']['Y'].'-'.$this->_formValues['end_date']['M'].'-'.$this->_formValues['end_date']['d'];
        
        if ($this->_formValues['start_date']['Y']) {
          $clauses[] = 'contribution.receive_date > "'.$start_date.'"';
        }
        if ($this->_formValues['end_date']['Y']) {
          $clauses[] = 'contribution.receive_date < "' . $end_date . '"';
        }
        return implode( ' AND ', $clauses );
    }

    function having( $includeContactIDs = false ) {
        $clauses = array( );

        //$clauses[] = " GROUP BY contribution_id WITH ROLLUP";
          $clauses[] = "GROUP BY id";
        return implode( ' AND ', $clauses );
    }

    /* This function does a query to get totals for some of the search result columns and returns a totals array. */
    function summary( ) {
    }

    /*
     * Functions below generally don't need to be modified
     */
    function count( ) {
        $sql = $this->all( );

        $dao = CRM_Core_DAO::executeQuery( $sql,
                                           CRM_Core_DAO::$_nullArray );
        return $dao->N;
    }

    function contactIDs( $offset = 0, $rowcount = 0, $sort = null) {
        return $this->all( $offset, $rowcount, $sort, false, true );
    }

    function &columns( ) {
        return $this->_columns;
    }

    function setTitle( $title ) {
        if ( $title ) {
            CRM_Utils_System::setTitle( $title );
        } else {
            CRM_Utils_System::setTitle(ts('Search'));
        }
    } 
}

Here is the template file almost verbatim to the example:

{* Template for "Sample" custom search component. *}
{assign var="showBlock" value="'searchForm'"}
{assign var="hideBlock" value="'searchForm_show','searchForm_hide'"}
<div id="searchForm_show" class="form-item">
    <a href="#" onclick="hide('searchForm_show'); show('searchForm'); return false;">
<img src="{$config->resourceBase}i/TreePlus.gif" class="action-icon" alt="{ts}open section{/ts}" /></a>
    <label>{ts}Edit Search Criteria{/ts}</label>
</div>

<div id="searchForm" class="form-item">
    <fieldset>
        <legend>
    <span id="searchForm_hide">
<a href="#" onclick="hide('searchForm','searchForm_hide'); show('searchForm_show'); return false;">
    <img src="{$config->resourceBase}i/TreeMinus.gif" class="action-icon" alt="{ts}close section{/ts}" /></a>
    </span>{ts}Search Criteria{/ts}
</legend>

        <table class="form-layout-compressed">
            {* Loop through all defined search criteria fields (defined in the buildForm() function). *}
            {foreach from=$elements item=element}
<tr>
    <td class="label">{$form.$element.label}</td>
    <td>{$form.$element.html}</td>
</tr>
            {/foreach}
            <tr>
                <td>&nbsp;</td><td>{$form.buttons.html}</td>
            </tr>
        </table>
    </fieldset>
</div>

{if $rowsEmpty}
    {include file="CRM/Contact/Form/Search/Custom/EmptyResults.tpl"}
{/if}

{if $summary}

    <h3>Summary for {if $summary.month}<strong>Month</strong>: {$summary.month}{/if}
       {if $summary.year}<strong> Year</strong>: {$summary.year}{/if}{if !$summary.month && !$summary.year}All Years{/if}</h3>
Total Number of Contributions: {$summary.contributions_total}<br>
Money Received: {$summary.money_total|crmMoney}<br>
Average Contribution: {$summary.average_contribution|crmMoney}<br>
<p>
{/if}

{if $rows}
    {* Search request has returned 1 or more matching rows. Display results and collapse the search criteria fieldset. *}
    {assign var="showBlock" value="'searchForm_show'"}
    {assign var="hideBlock" value="'searchForm'"}

    <fieldset>

        {* This section handles form elements for action task select and submit *}
        {include file="CRM/Contact/Form/Search/ResultTasks.tpl"}

        {* This section displays the rows along and includes the paging controls *}
        <p>

            {include file="CRM/common/pager.tpl" location="top"}

            {include file="CRM/common/pagerAToZ.tpl"}

            {strip}
            <table summary="{ts}Search results listings.{/ts}">
                <tr class="columnheader">
                    <th scope="col" title="Select All Rows">{$form.toggleSelect.html}</th>
    {foreach from=$columnHeaders item=header}
<th scope="col">
    {if $header.sort}
        {assign var='key' value=$header.sort}
        {$sort->_response.$key.link}
    {else}
{$header.name}
    {/if}
</th>
    {/foreach}
                    <th>&nbsp;</th>
                </tr>

                {counter start=0 skip=1 print=false}
                {foreach from=$rows item=row}
                    <tr id='rowid{counter}' class="{cycle values="odd-row,even-row"}">
                        {assign var=cbName value=$row.checkbox}
                        <td>{$form.$cbName.html}</td>
                        {foreach from=$columnHeaders item=header}
                            {assign var=fName value=$header.sort}
                            {if $fName eq 'sort_name'}
                                <td><a href="{crmURL p='civicrm/contact/view' q="reset=1&cid=`$row.contact_id`"}">{$row.sort_name}</a></td>
                            {else}
                                <td>{$row.$fName}</td>
                            {/if}
                        {/foreach}
                        <td>{$row.action}</td>
                    </tr>
                {/foreach}
            </table>
            {/strip}

            <script type="text/javascript">
            {* this function is called to change the color of selected row(s) *}
            var fname = "{$form.formName}";
            on_load_init_checkboxes(fname);
            </script>

            {include file="CRM/common/pager.tpl" location="bottom"}
        </p>

    </fieldset>
    {* END Actions/Results section *}

{/if}

<script type="text/javascript">
var showBlock = new Array({$showBlock});
var hideBlock = new Array({$hideBlock});

{* hide and display the appropriate blocks *}
on_load_init_blocks( showBlock, hideBlock );
</script>

Still working on the more complex search (see link above). I appreciate any suggestions for improvement. Thanks.

okay - so i see this error reported elsewhere on forum "Rule 'qfDate' is not registered" and see it has been picked up as an issue between 3.0 and 3.1

http://wiki.civicrm.org/confluence/display/CRMDOC32/Custom+search+date+fields+in+CiviCRM+3.1

Leo - what version is yours working on?

Anyone - have Date fields changed between 3.1 and 3.2 as well?

I assume the offending bit is
        $form->add( 'date',
                    'start_date',
                    ts('Start Date'),
                    CRM_Core_SelectValues::date( 'custom', 10, 0 ) );
        $form->addRule('start_date', ts('Select a valid date.'), 'qfDate');
       
        $form->add( 'date',
                    'end_date',
                    ts('End Date'),
                    CRM_Core_SelectValues::date( 'custom', 10, 0 ) );
        $form->addRule('end_date', ts('Select a valid date.'), 'qfDate');