CiviCRM Community Forums (archive)

A couple of our groups have been asking for the ability to bring up a list of people that have attended a minimum number of events over time.

i decided to hack one of the custom searches that already exists. i started with the Event Aggregate custom search (which does not seem to work) but then switched to the Contribution Aggregate custom search.

i ended up with a new custom search: Count events attended. this custom search has a php file (AttendEventCount.php) and a tpl file (AttendEventCount.tpl).

you have to register the custom search (Administer -> Customize Data and Screens -> Manage Custom Searches) and put in the class of the new search (CRM_Contact_Form_Search_Custom_AttendEventCount).

feedback most welcome!

--josue

AttendEventCount.php

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

class CRM_Contact_Form_Search_Custom_AttendEventCount
   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 Id')   => 'contact_id'  ,
                                 ts('Name'      )   => 'sort_name',
                                 ts('How many times') => 'participant_count',
                                 ts('Events') => 'event_name' );
    }

    function buildForm( &$form ) {
        /**
         * You can define a custom title for the search form
         */
        $this->setTitle('Find People Who Have Attended Events Multiple Times');

        /**
         * Define the search form fields here
         */
        $form->add( 'text',
                    'min_amount',
                    ts( 'At least how many events attended' ) );
        $form->addRule( 'min_amount', ts( 'Please enter a valid amount (numbers and decimal point only).' ), 'money' );

        $form->addDate( 'start_date', ts('Event Date From'), false, array( 'formatType' => 'custom') );
        $form->addDate( 'end_date', ts('...through'), false, array( 'formatType' => 'custom') );

  $event_type = CRM_Core_OptionGroup::values( 'event_type', false );
        foreach($event_type as $eventId => $eventName) {
            $form->addElement('checkbox', "event_type_id[$eventId]", 'Event Type', $eventName);
  }
        /**
         * If you are using the sample template, this array tells the template fields to render
         * for the search form.
         */
        $form->assign( 'elements', array( 'min_amount', 'start_date', 'end_date', 'event_type_id') );
    }

    /**
     * Define the smarty template used to layout the search form and results listings.
     */
    function templateFile( ) {
       return 'CRM/Contact/Form/Search/Custom/AttendEventCount.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_a.id as contact_id,
contact_a.sort_name as sort_name,
COUNT(participant.contact_id) as participant_count,
GROUP_CONCAT(DISTINCT(event.title)) as event_name

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

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

        $having = $this->having( );
        if ( $having ) {
            $having = " HAVING $having ";
        }

        $sql = "
SELECT $select
FROM   $from
WHERE  $where
GROUP BY contact_a.id
$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 .= "ORDER BY participant_count desc";
            }
        }

        if ( $rowcount > 0 && $offset >= 0 ) {
            $sql .= " LIMIT $offset, $rowcount ";
        }
        return $sql;
    }

    function from( ) {
        return "
civicrm_participant as participant,
civicrm_event as event,
civicrm_contact AS contact_a
";
    }

     /*
      * WHERE clause is an array built from any required JOINS plus conditional filters based on search criteria field values
      *
      */
    function where( $includeContactIDs = false ) {
        $clauses = array( );

        $clauses[] = "participant.contact_id = contact_a.id";
        $clauses[] = "participant.event_id = event.id";
        $clauses[] = "participant.status_id in ( 2 )";

        $startDate = CRM_Utils_Date::processDate( $this->_formValues['start_date'] );
        if ( $startDate ) {
            $clauses[] = "event.start_date >= $startDate";
        }

        $endDate = CRM_Utils_Date::processDate( $this->_formValues['end_date'] );
        if ( $endDate ) {
            $clauses[] = "event.start_date <= $endDate";
        }

        if ( !empty( $this->_formValues['event_id'] ) ) {
            $clauses[] = "civicrm_event.id = {$this->_formValues['event_id']}";
        }

        if ( $includeContactIDs ) {
            $contactIDs = array( );
            foreach ( $this->_formValues as $id => $value ) {
                if ( $value &&
                     substr( $id, 0, CRM_Core_Form::CB_PREFIX_LEN ) == CRM_Core_Form::CB_PREFIX ) {
                    $contactIDs[] = substr( $id, CRM_Core_Form::CB_PREFIX_LEN );
                }
            }

            if ( ! empty( $contactIDs ) ) {
                $contactIDs = implode( ', ', $contactIDs );
                $clauses[] = "contact_a.id IN ( $contactIDs )";
            }
        }

 if ( ! empty($this->_formValues['event_type_id'] ) ) {
            $event_type_ids = implode(',', array_keys($this->_formValues['event_type_id']));
            $clauses[] = "event.event_type_id IN ( $event_type_ids )";
        }

        return implode( ' AND ', $clauses );
    }

    function having( $includeContactIDs = false ) {
        $clauses = array( );
        $min = CRM_Utils_Array::value( 'min_amount', $this->_formValues );
        if ( $min ) {
            $min = CRM_Utils_Rule::cleanMoney( $min );
            $clauses[] = "COUNT(participant.contact_id) >= $min";
        }

        return implode( ' AND ', $clauses );
    }

    /* 
     * 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'));
       }
   }

   function summary( ) {
       return null;
   }

}

AttendEventCount.tpl

{* Default template custom searches. This template is used automatically if templateFile() function not defined in
   custom search .php file. If you want a different layout, clone and customize this file and point to new file using
   templateFile() function.*}
<div class="crm-block crm-form-block crm-contact-custom-search-form-block">
<div class="crm-accordion-wrapper crm-custom_search_form-accordion {if $rows}crm-accordion-closed{else}crm-accordion-open{/if}">
    <div class="crm-accordion-header crm-master-accordion-header">
      <div class="icon crm-accordion-pointer"></div>
      {ts}Edit Search Criteria{/ts}
    </div><!-- /.crm-accordion-header -->
    <div class="crm-accordion-body">
        <div class="crm-submit-buttons">{include file="CRM/common/formButtons.tpl" location="top"}</div>
        <table class="form-layout-compressed">
            {* Loop through all defined search criteria fields (defined in the buildForm() function). *}
            {foreach from=$elements item=element}
                <tr class="crm-contact-custom-search-form-row-{$element}">
                    <td class="label">{$form.$element.label}</td>
                    {if $element eq 'start_date'}
                        <td>{include file="CRM/common/jcalendar.tpl" elementName=start_date}</td>
                    {elseif $element eq 'end_date'}
                        <td>{include file="CRM/common/jcalendar.tpl" elementName=end_date}</td>
                    {else}
                        <td>{$form.$element.html}</td>
                    {/if}
                </tr>
            {/foreach}
            <tr class="crm-contact-custom-search-eventDetails-form-block-event_type">
                <td class="label">{ts}Event Type{/ts}</td>
                <td>
                    <div class="listing-box">
                        {foreach from=$form.event_type_id item="event_val"}
                            <div class="{cycle values="odd-row,even-row"}">
                                {$event_val.html}
                            </div>
                        {/foreach}
                    </div>
                    <div class="spacer"></div>
                </td>
            </tr>
        </table>
        <div class="crm-submit-buttons">{include file="CRM/common/formButtons.tpl" location="bottom"}</div>
    </div><!-- /.crm-accordion-body -->
</div><!-- /.crm-accordion-wrapper -->
</div><!-- /.crm-form-block -->

{if $rowsEmpty || $rows}
<div class="crm-content-block">
{if $rowsEmpty}
    {include file="CRM/Contact/Form/Search/Custom/EmptyResults.tpl"}
{/if}

{if $summary}
    {$summary.summary}: {$summary.total}
{/if}

{if $rows}
        <div class="crm-results-block">
    {* Search request has returned 1 or more matching rows. Display results and collapse the search criteria fieldset. *}
        {* This section handles form elements for action task select and submit *}
       <div class="crm-search-tasks">       
        {include file="CRM/Contact/Form/Search/ResultTasks.tpl"}
                </div>
        {* This section displays the rows along and includes the paging controls *}
            <div class="crm-search-results">

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

        {* Include alpha pager if defined. *}
        {if $atoZ}
            {include file="CRM/common/pagerAToZ.tpl"}
        {/if}
       
        {strip}
        <table class="selector" summary="{ts}Search results listings.{/ts}">
            <thead class="sticky">
                <tr>
                <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>
            </thead>

            {counter start=0 skip=1 print=false}
            {foreach from=$rows item=row}
                <tr id='rowid{$row.contact_id}' 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>
    {* END Actions/Results section *}
    </div>
    </div>
{/if}
</div>
{/if}
{literal}
<script type="text/javascript">
cj(function() {
   cj().crmaccordions();
});
</script>
{/literal}



Forgive me for my ignorance, but where do I need to place the files? I know where to put custom TPL files (but haven't ever created ones that didn't previously exist).

Do I need to just drop these in the actual core directories?

Edit: I was able to get the search to work properly, however it required me putting the PHP file under the core. Should this be okay for upgrades, considering the name should be unique anyways?