Author Topic: Custom Search for Events with Custom Fields (Read 2174 times)

lkacenja · July 21, 2010, 09:42:16 am

Hi,

I've been working on creating a custom event search, which allows the inclusion of default fields/default field functionality with custom fields. I thought I'd contribute it for feedback, revision, and possibly as a resource. Thanks to user TwoMice for all the help. You're a real community asset

User case: We have a client that has a lot of events. She also really loves the ability to collect additional information with custom field groups and fields. As a result she has many complicated event registrations with lots of extra stuff going on. She voiced that it would make her life much easier to be able to view these events with or without relevant custom fields in a custom search.

I derived the following code from the example found here: http://wiki.civicrm.org/confluence/display/CRMDOC/Detailed+Contribution+Report+(month+or+year)

Also, I'm not usually involved with OOP PHP so please bear with me. There are several kludgy parts of this that need some work.

Here is the Custom Search:

Code: [Select]

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

class CRM_Contact_Form_Search_Custom_EventsPlus implements CRM_Contact_Form_Search_Interface 
  {

    protected $_formValues;

    function __construct( &$formValues ) 
      {
      /**
       * Define the columns for search result rows
       */
      $this->_formValues = $formValues;
      
      $this->_columns = array ( ts('')                           => 'event_id',
                                 ts('Event')                      => 'event_name',
                                 ts('Type')                       => 'event_type',
                                 ts('Number of<br />Participants') => 'participant_count'
                                );
        if (is_array($this->_formValues)) 
          {
          foreach ($this->_formValues as $key => $value) 
            {
            if(is_numeric($key) && $value > 0)
              {
               $get_additional_columns = 'SELECT label FROM civicrm_custom_field WHERE id = '.$key;
               $dao_additional_columns = CRM_Core_DAO::executeQuery( $get_additional_columns,
                                              CRM_Core_DAO::$_nullArray );
               while ($dao_additional_columns->fetch()) 
                 {
                 $this->_columns[$dao_additional_columns->label] = $key;
                 }
               }
             }
           }// end if is array
    }
    
    function buildForm( &$form ) {
        /**
         * You can define a custom title for the search form
         */
      $this->setTitle('Events Plus');
        
      $i = 0;
      $b = 0;
      $get_group = "SELECT id, title FROM civicrm_custom_group WHERE extends LIKE '%event%'";
      $dao_get_group = CRM_Core_DAO::executeQuery( $get_group,
                                              CRM_Core_DAO::$_nullArray );
       while ($dao_get_group->fetch()) 
         {
         $get_fields = "SELECT id, label FROM civicrm_custom_field WHERE custom_group_id = ".$dao_get_group->id;
         $dao_get_fields = CRM_Core_DAO::executeQuery( $get_fields,
                                              CRM_Core_DAO::$_nullArray );
         while ($dao_get_fields->fetch()) 
           {
           $form->addElement('checkbox', $dao_get_fields->id, '', $dao_get_fields->label);
           $check_boxes[$i] = $dao_get_fields->id;
           $i++; 
           }
           //$form->addElement('group', 'group_'.$dao_get_group->id, 'Hi' ,$check_boxes[0]);
           //$groups[$b] = 'group_'.$dao_get_group->id;
           //$b++;
         }
         $form->assign( 'elements', $check_boxes);
    }
    
    /**
     * Define the smarty template used to layout the search form and results listings.
     */
    function templateFile( ) 
    {
        return 'CRM/Contact/Form/Search/Custom/EventsPlus.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  = "
                        event.id AS event_id, 
                        COUNT( civicrm_participant.id ) AS participant_count, 
                        event.title AS event_name, 
                        event_type.label AS event_type
                        ";
          }
        
        $from   = $this->from( );

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

        $having = $this->having( );
        if ( $having ) 
          {
            $having = "  $having ";
          }
        foreach($this->_formValues as $key => $value)
          {
          if (is_numeric($key) && !empty($key)) 
            {
            $additional_fields = 'SELECT 
                                  civicrm_custom_field.column_name, 
                                  civicrm_custom_group.table_name 
                                  FROM civicrm_custom_field
                                  LEFT JOIN civicrm_custom_group 
                                  ON civicrm_custom_field.custom_group_id = civicrm_custom_group.id
                                  WHERE civicrm_custom_field.id ='.$key;
          
          
            $dao_additional_fields = CRM_Core_DAO::executeQuery( $additional_fields,
                                              CRM_Core_DAO::$_nullArray );
            for ($m = 0; $m < count($dao_additional_fields->fetch( )); $m++) 
              {
                if ($m == 0) 
                  {
                  // things that should only happen once per field
                  $select.= ",";
                  }
              
              if ($m < count($dao_additional_fields)-1) 
                {
                  $select.= ' '.$dao_additional_fields->table_name.'.'.$dao_additional_fields->column_name.', AS "'.$key.'" ';
                } else 
                {
                  $select .= ' '.$dao_additional_fields->table_name.'.'.$dao_additional_fields->column_name.' AS "'.$key.'"';
                }
              if ($current_table_name != $dao_additional_fields->table_name) 
                {
                $from .= " LEFT JOIN ".$dao_additional_fields->table_name. " ON event.id =".$dao_additional_fields->table_name.".entity_id";
                $current_table_name = $dao_additional_fields->table_name;
                }
              }
            }
          } 
       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 from( ) 
      {
        return "
                civicrm_event AS event
                LEFT JOIN civicrm_participant ON event.id = civicrm_participant.event_id
                LEFT JOIN civicrm_option_value AS event_type ON event.event_type_id = event_type.value
               ";
      }

    /*
     * 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( );
        $clauses[] = 'event_type.option_group_id =14'; // event category key = 14
        return implode( ' AND ', $clauses );
      }

    function having( $includeContactIDs = false ) 
      {
        $clauses = array( );
          $clauses[] = "GROUP BY event_name";
        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( ) 
      {
        // this method is required, but can be empty
      }
      
    /*
     * 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:

Code: [Select]

{* Template for "Sample" custom search component. *}
{literal}
<script type="text/javascript">
var EventPlus = EventPlus || {};

EventPlus.init = function(){
  $("#task option").each(
    function(){
      if($(this).text().search('Export') >= 0){
        $(this).text("Export this Event Search");
      }else{
        if($(this).text().search('-') < 0){
        $(this).remove();
        }
      }
    }
    );
}

$("document").ready(EventPlus.init);
</script>
{/literal}
{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 $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/Custom/ResultTasksCustom.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}
                                <td>{$row.$fName}</td>
                        {/foreach}
                        <td><a href="{crmURL p='civicrm/event/info' q="reset=1&id=`$row.event_id`"}">View</a> | <a href="{crmURL p='civicrm/event/manage' q="reset=1&action=update&id=`$row.event_id`&subPage=EventInfo"}">Edit</a> </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>

@To Do

Put possible search items into a fieldset. Not sure how to do that. Any suggestions. I see formElement group, but have not had much luck getting it to work.
Find a better way to fix that kludgy javascript in the template.

Any suggestions as far as revision, or any comments would be greatly appreciated. Thanks again.

petednz · July 21, 2010, 05:46:04 pm

Hi Leo - thanks for putting this on to the forums.

Since we were just having a conversation here http://forum.civicrm.org/index.php/topic,14224.msg62816.html#msg62816 about the usefullness of having Custom Event Fields being searchable in Find Participants and Advanced Search, I thought I would give this a whirl to see if it might be a useful alternative and do some screenshots so others know what to expect.

Following the instructions here http://wiki.civicrm.org/confluence/display/CRMDOC/Custom+Search+Components plus your code, I got myself a new Custom Search

(NOTE: I made an error originally by naming things EventPlus perhaps partially because your variable is called 'var EventPlus' not EventsPlus - just in case that trips anyone else up)

SO having got me a new search I added some Custom Fields for Events - and yes they do show on the Custom Search and they give me the option of including any of the Fields in the Results.

I had the following error user warning: Smarty error: unable to read resource: "CRM/Contact/Form/Search/Custom/ResultTasksCustom.tpl" in /home/www-home/apps/civicrm-3.1.1/civicrm/packages/Smarty/Smarty.class.php on line 1093.

So copied the ResultTasks.tpl in to the custom directory and renamed it - and got the following.

lkacenja · August 03, 2010, 08:02:34 am

Thanks for giving it a spin. Glad you got it to work. I'm still trying to improve this one. There may even be a better way to do this in Civi. Any suggestions as to that or the kludgy parts? Thanks.

CiviCRM Community Forums (archive)

News:

Author Topic: Custom Search for Events with Custom Fields (Read 2174 times)

lkacenja

Custom Search for Events with Custom Fields

petednz

Re: Custom Search for Events with Custom Fields

lkacenja

Re: Custom Search for Events with Custom Fields