Author Topic: Creating a Custom Search (Read 4175 times)

CiviTeacher.com · May 07, 2009, 07:41:51 pm

Hi there, I had a wish list given to me yesterday and one of the items is a simple way to search for Contributions and/or Registrations by month. Rather than go to the CiviContribute area and select:
a date, month, and year to start and...
a date, month, year to END the search
...it would be more convenient to simply select a Month and Year such as "June 2009" from a couple of drop-downs, press Go, and BAM there are your results.

So I found out about Custom Searches and confidently breezed over to this page:
http://wiki.civicrm.org/confluence/display/CRMDOC/Custom+Search+Components

Wow! What a document, I'm impressed with the thoroughness of the instructions but also flabbergasted at the complexity of this seemingly simple task. I've been programming PHP for about 6ish years now and MySQL for nearly that long, so I started bravely opening pages and exploring /CRM as well as packages/HTML/QuickForm.php

90 minutes later here I am, I couldn't even figure out how to make a date dropdown that had only Month and Year (with no date)...much less how to turn a good hard-coded query into a functioning Custom Search. What I thought would be simple is definitely not. To add a little humor to the situation, I looked at the Contributed Custom Searches page and I'll save you a click it's blank:
http://wiki.civicrm.org/confluence/display/CRMDOC/Community+Contributed+Custom+Searches

It's not hard to guess why it's blank since Oct 2008, this is difficult s#%*!

Anyone know of a less time consuming way of achieving my goal? As of now I am thinking I have more important and less time consuming things to do, this wishlist item can wait (indefinitely).

Donald Lobo · May 07, 2009, 10:33:22 pm

1. there are quite a fair number of contributed searches. they are attached as child pages to the main page (scroll to the bottom)

2. check:

CRM/Admin/Form/Setting/Date.php
http://drupal.demo.civicrm.org/civicrm/admin/setting/date?reset=1

for an example of how to set a month-day QF date. you can modify that concept for a year-month only

lobo

CiviTeacher.com · May 08, 2009, 09:37:06 am

Ah yes my mistake, there are 4 searches contributed. Thanks, I've been informed this is a must-have so I'll be making my own custom search. I'll post it when I'm done.

Philip Small · May 08, 2009, 07:01:01 pm

Stoob: I caught a fleeting glimpse of some custom search choices on nscss a few minutes ago and .. I liked what I saw. Phil

CiviTeacher.com · May 12, 2009, 11:45:51 am

@Philip Bruce Campbell icon alert! Hi Phil fancy meeting you here!

Hey Lobo, I was able to create a custom search that returns the fields that I want along with aggregate summary. I call it my "Monthly Contribution Report" custom search. Just FYI, assuming I am a programmer of slightly below-average skill, it took me about 5 hours to accomplish this Custom Report, so that's what other programmers doing this for the first time who are not geniuses can expect.

However, I am a bit confused by the HTML interface drop downs for the date. I used the example you gave, and it seems that even though I have no "day" drop down the functions of this included form library does two things automatically:

1) Assumes if no day is entered (in my example no Day drop down is offered) that Day = 01
2) Concatenates the month day and year into a single string such as 20090101 rather than keeping each day, month, year separate

Because of #2 I was forced to use substr($date,0,4) to get the information I really wanted, which is a bit strange.

I would like to ideally be able to have the month left blank and just select a year, and the logic of the custom search would then provide a yearly report, not a monthly report.

Questions please:
1) Where are the docs on this Form creation library CiviCRM is using and how can I get it to behave more flexibly?
2) Currently only 2009 in year dropdown....Will it populate this year drop down with 2010 automatically when it becomes 2010?
3) Do you want me to upload the custom search to the Demo site so you can see how it works?
4) Currently I feel the search is in beta stages, when could/should it be included in the contributed searches?

Thanks!

Donald Lobo · May 12, 2009, 01:48:27 pm

1. http://pear.php.net/manual/en/package.html.html-quickform.php

2. You can tell QF how many years back you want to go etc. Check the docs and/or see how we do it in other files

3. When you are done and happy with the search, i recommend you create a forum topic and add a description of what you have done etc along with the code. Based on commuity feedback we'll include it as a custom search for the next release

lobo

CiviTeacher.com · May 12, 2009, 02:40:33 pm

Thanks. I will look into QuickForm and see if I can make it work how I want.

Also I think I may have found a bug. On this page
civicrm/admin/options/custom_search?group=custom_search&reset=1
I have moved my new custom search to the top of the "order".

However on this page
civicrm/contact/search/custom/list
it still shows up last.

Is this the expected behavior?

Kiran Jagtap · May 12, 2009, 10:12:33 pm

hi,

thanks for catching, fixed has been done and you will get in next stable release v2.2.3
patch is here : http://fisheye.civicrm.org/changelog/CiviCRM?cs=21144

kiran

CiviTeacher.com · May 15, 2009, 04:34:43 pm

@Lobo -

Sorry to bother you again but I have followed your leads and failed to find the appropriate information to achieve my goal. I have now spent about 2 hours examining files to no avail, so at least that's "due diligence" so to speak.

My goals remain:
1) Have years more than present year appear in the drop down.

2) Find a way to separate month and the year the posted "start_date" value, so as not to have a string such as "20090101" sent on post

This is my current code:

Code: [Select]

CRM_Core_SelectValues::date( 'custom', null, null, "M\001Y" )  );

Started with PEAR this was the best page I could find, explained what "M" and "Y" are. No clues as to what the "\001" in "M\001Y" performs.

http://pear.php.net/package/HTML_QuickForm/docs/latest/HTML_QuickForm/HTML_QuickForm_date.html

Continued exploring the CRM directory, DAO, BAO etc. "SelectValues.php", "CustomOption.php", and "Date.php" In the Date_Utils class. No clues or examples on what the numbers in the \001 mean but I know that if I remove them or change them the form doesn't work any longer.

I finished by trying to find examples on other CiviCRM pages where QuickForm dates go back more than one year. I looked primarily at the "Find Contributions Page" and found template civicrm/templates/CRM/Contribute/Form/Search/Common.tpl and the corresponding Search.php file but was flummoxed by the PHP, and saw no insight into how to customize my code.

Any further documentation or examples would be extremely helpful finishing my Custom Search, I am trying to get it functioning appropriately so I can contribute to the project in a meaningful way.

Thanks!

Kiran Jagtap · May 16, 2009, 08:32:51 am

http://forum.civicrm.org/index.php/topic,307.msg33373.html#msg33373

this thread might help you

kiran

CiviTeacher.com · May 18, 2009, 11:39:36 am

Thank you Kiran, that was very helpful. Once I was able to edit the number of years in the past and future without editing the

Code: [Select]

 M\001Y

Then I realized that the \001 must be some kind of QuickForm hack (I could not find any documentation regarding \001 and QuickForm) to remove the "day of month" SELECT from the Date form element.

Code: [Select]

\001\001Y

Produces a form element with Year only.

CiviTeacher.com · May 18, 2009, 02:55:33 pm

Almost there. Another bit of help please? I would like to display at the top of my results page a statement such as:

"This is a report for: 04 2009"

The important part of the functionality is that the user is reminded which month's results they are actually looking at. Despite my best efforts, I am unable to get any data from the $_POST values on the page, which seems the simplest way to accomplish this.

Simple methods like

Code: [Select]

{$smarty.post.year}

don't seem to work, probably because the $_POST itself is empty, at least according to print_r($_POST) that is the case.

I have tried other methods such as defining in my CustomSearch.php PHP file things like this:

Code: [Select]

$smarty->assign('month', $this->_formValues['the_month']['M']);
$smarty->assign('month', $this->_formValues['the_month']);
followed by a 
{$month} in the .tpl

None of these basic methods that work with Smarty normally seem to function in the CiviCRM context, so I am wondering if there is another layer of abstraction that is obscuring my path. Again, I've put about 2 hours into this now and am blocked and frustrated, any help would be great.

This is my code if interested.

Code: [Select]

<?php

/*
 +--------------------------------------------------------------------+
 | CiviCRM version 2.2                                                |
 +--------------------------------------------------------------------+
 | Copyright CiviCRM LLC (c) 2004-2009                                |
 +--------------------------------------------------------------------+
 | This file is a part of CiviCRM.                                    |
 |                                                                    |
 | CiviCRM is free software; you can copy, modify, and distribute it  |
 | under the terms of the GNU Affero General Public License           |
 | Version 3, 19 November 2007.                                       |
 |                                                                    |
 | CiviCRM is distributed in the hope that it will be useful, but     |
 | WITHOUT ANY WARRANTY; without even the implied warranty of         |
 | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
 | See the GNU Affero General Public License for more details.        |
 |                                                                    |
 | You should have received a copy of the GNU Affero General Public   |
 | License along with this program; if not, contact CiviCRM LLC       |
 | at info[AT]civicrm[DOT]org. If you have questions about the        |
 | GNU Affero General Public License or the licensing of CiviCRM,     |
 | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
 +--------------------------------------------------------------------+
*/

/**
 *
 * @package CRM
 * @copyright CiviCRM LLC (c) 2004-2009
 * $Id$
 *
 */

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

class CRM_Contact_Form_Search_Custom_ContributionsByMonth
 //extends    CRM_Contact_Form_Search_Custom_Base
   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('#') => 'contribution_id',
			ts('Full Date') => 'date',			
			ts('Amount') => 'total',
			ts('Source') => 'source',
			ts('Accomodation') => 'amountlevel',
			ts('First Name') => 'first_name',
			ts('Last Name') => 'last_name',
			ts('Street') => 'street',
			ts('Street 2') => 'street2',
			ts('City') => 'city',
			ts('State') => 'state',
			ts('Postal Code') => 'postal_code',
			ts('Country') => 'country',
			ts('Contact Id')   => 'contact_id'  ,
			ts('Month') => 'month',
			ts('Day of Month') => 'day',
			ts('Year') => 'year'
		);
	}

    function buildForm( &$form ) {
        /**
         * You can define a custom title for the search form
         */
        $this->setTitle('Monthly Contribution Report');

        /**
         * Define the search form fields here
         */

        $form->add( 'date',
                    'the_month',
                    ts('Select a month: '),
                    CRM_Core_SelectValues::date( 'custom', null, null, "M\001\001" )  );
        $form->addRule('the_month', ts('Select a month.'), 'qfDate');

		$yearsInPast = null; 
		$yearsInFuture = 1;
        $form->add( 'date',
                    'the_year',
                    ts('Select a year: '),
                    CRM_Core_SelectValues::date( 'custom', $yearsInPast, $yearsInFuture, "\001\001Y" )  );
        $form->addRule('the_year', ts('Select a year.'), 'qfDate');
		
        $form->assign( 'elements', array( 'the_month', 'the_year') );
    }

    /**
     * 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(cInd.id) as contact_id,
	cont.id as contribution_id,
	cInd.first_name as first_name,
	cInd.last_name as last_name,
	contactAddy.street_address as street,
	contactAddy.supplemental_address_1 as street2,
	contactAddy.city as city,
	SP.name as state,
	contactAddy.postal_code as postal_code,
	contactAddy.name as country,
	cont.source as source,
	cont.amount_level as amountlevel,
	cont.total_amount as total,
	cont.receive_date as date,
	MONTH(cont.receive_date) as month,
	DAY(cont.receive_date) as day,
	YEAR(cont.receive_date) as year
";

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

        $sql = "
SELECT $select 
FROM   $from 
WHERE  $where 
$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 .= " ";
            }
        }
        return $sql;
    }
    
    function from( ) {
        return "
	civicrm_contact cInd
	
	LEFT JOIN civicrm_address contactAddy ON ( contactAddy.contact_id = cInd.id AND
	contactAddy.is_primary = 1 )
	
	LEFT JOIN civicrm_state_province SP ON SP.id = contactAddy.state_province_id 
	
	LEFT JOIN civicrm_country country ON country.id = contactAddy.country_id 
	
	LEFT JOIN civicrm_contribution cont ON cont.contact_id = cInd.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 = array( );

		$clauses[] = "cont.total_amount > 0";
		
        $theYear = CRM_Utils_Date::format( $this->_formValues['the_year'] );
		$theMonth = CRM_Utils_Date::format( $this->_formValues['the_month'] );
        if ( $theMonth ) {
			$twoDigitMonth = substr($theMonth,4,2);
			$clauses[] = "MONTH(cont.receive_date) = " . $twoDigitMonth;
			//attempted this:
			//$smarty->assign('month', $this->_formValues['the_month']['M']);
        }
        if ( $theYear ) {
			$fourDigitYear = substr($theYear,0,4);
            $clauses[] = "YEAR(cont.receive_date) = " . $fourDigitYear;
			//attempted this:
			//$smarty->assign('year', $theYear);
        }

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

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

	//	$clauses[] = " GROUP BY contribution_id WITH ROLLUP";

        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( ) {
        $totalSelect = "
	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();
        
        $sql = "
        SELECT  $totalSelect
        FROM    $from
        WHERE   $where
        ";

        //CRM_Core_Error::debug('sql',$sql);       
        $dao = CRM_Core_DAO::executeQuery( $sql,
                                           CRM_Core_DAO::$_nullArray );
        $totals = array();
        while ( $dao->fetch( ) ) {
            $totals['money_total'] = $dao->money_total;
            $totals['contributions_total'] = $dao->contributions_total;
            $totals['average_contribution'] = $dao->average_contribution;
        }
		
        return $totals;
    }
	
    /* 
     * 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'));
       }
   }


}

print_r($_POST);

This is the .tpl file

Code: [Select]

{* 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 {$month} {$year} {$smarty.post.the_year}  {$smarty.post.the_year[Y]} </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>

Donald Lobo · May 18, 2009, 05:02:54 pm

Hey stuart:

we'll take a look at the below custom search and tweak it in the next 24 hours or so

lobo

Sunil · May 19, 2009, 03:07:42 am

Hi stuart:

Added your custom search code on wiki, with some modification
http://wiki.civicrm.org/confluence/display/CRMDOC/Detailed+Contribution+Report+(month+or+year)

Quote

"This is a report for: 04 2009"

Now it's showing in Summary.

sunil

CiviTeacher.com · May 19, 2009, 11:41:23 am

Sunil thanks so much. I was finding it very difficult to understand how all the different frameworks fit together, it is much more complex to understand than regular PHP/Smarty for me.

I have made a couple of small text changes to the search forms and added a description to the wiki page.

CiviCRM Community Forums (archive)

News:

Author Topic: Creating a Custom Search (Read 4175 times)

CiviTeacher.com

Creating a Custom Search

Donald Lobo

Re: Creating a Custom Search

CiviTeacher.com

Re: Creating a Custom Search

Philip Small

Re: Creating a Custom Search

CiviTeacher.com

Re: Creating a Custom Search

Donald Lobo

Re: Creating a Custom Search

CiviTeacher.com

Re: Creating a Custom Search

Kiran Jagtap

Re: Creating a Custom Search

CiviTeacher.com

Re: Creating a Custom Search

Kiran Jagtap

Re: Creating a Custom Search

CiviTeacher.com

Re: Creating a Custom Search

CiviTeacher.com

Re: Creating a Custom Search

Donald Lobo

Re: Creating a Custom Search

Sunil

Re: Creating a Custom Search

CiviTeacher.com

Re: Creating a Custom Search