Author Topic: Search by last name, first name returns no results (Read 455 times)

theMusician · April 27, 2011, 11:44:28 am

Hi,

I have written a custom search that finds results when you search by a person's last name or first name but not as a combined string last name, first name. The custom search is based on one that I wrote a few days ago that works properly.

Any ideas as to why that might be? I have compared the code and cannot find any differences except for the sql data.

Thanks for any tips.

Code: [Select]

<?php

/*
 +--------------------------------------------------------------------+
 | CiviCRM version 4.0                                                |
 +--------------------------------------------------------------------+
 | Copyright CiviCRM LLC (c) 2004-2011                                |
 +--------------------------------------------------------------------+
 | 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 and the CiviCRM Licensing Exception.   |
 |                                                                    |
 | 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 and the CiviCRM Licensing Exception 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-2011
 * $Id$
 *
 */

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

class CRM_Contact_Form_Search_Custom_LeaveHourSummary
   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('Individual Name')   => 'display_name' ,
									ts('FMLA Used')			=> 'fmla_leave_hours_27' ,
									ts('FMLA/MIL Used')		=> 'fmla_mil_31' ,
									ts('OFLA CFCM EXT Used') => 'ofla_cfcm_ext_32' ,
									ts('OFLA Used')			=> 'ofla_hours_28',
									ts('FMLA Hours Remaining') => 'fmla_hours_remaining',
									ts('OFLA Hours Remaining') => 'ofla_hours_remaining' );
		 }

    function buildForm( &$form ) {
        /**
         * You can define a custom title for the search form
         */
         //Show the date range in the title using php date functions
         //http://www.php.net/manual/en/function.date.php#102713
        $this->setTitle('Leave Hour Summary '. DATE('Y') . ' - ' . DATE('Y', strtotime("-1 year")));

        /**
         * Define the search form fields here
         */
         $form->add( 'text',
                    'display_name',
                    ts( 'Individual\'s Name (last, first)' ) );
		
        /**
         * If you are using the sample template, this array tells the template fields to render
         * for the search form.
         */
        $form->assign( 'elements', array( 'display_name') );
		}
		
		// Setting default search state to California
		//function setDefaultValues( ) {
		//return array( 'state_province_id' => 1004, );

    

    /**
     * Define the smarty template used to layout the search form and results listings.
     */
    function templateFile( ) {
       return 'CRM/Contact/Form/Search/Custom.tpl';
    }
       
    /**
      * Construct the search query
      */       
    function all( $offset = 0, $rowcount = 0, $sort = null,
                  $includeContactIDs = false) {
        
		// SELECT clause must include contact_id as an alias for civicrm_contact.id
		 
		$select  = "
	     DISTINCT c.contact_id, civicrm_contact.display_name, sum(fmla_leave_hours_27) as fmla_leave_hours_27, sum(fmla_mil_31) as fmla_mil_31 , sum(ofla_cfcm_ext_32) as ofla_cfcm_ext_32 , sum(ofla_hours_28) as ofla_hours_28, (480*e.fte_1 - sum(fmla_leave_hours_27)) as fmla_hours_remaining , (480*e.fte_1 - sum(ofla_hours_28)) as ofla_hours_remaining
            ";
		$from  = $this->from( );

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

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

		  // Define GROUP BY here if needed.
        $grouping = "";
		
        $sql = "
SELECT $select
FROM   $from
WHERE  $where
$grouping
$having
";
       
            // 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 display_name asc";
            }
       

        /* Uncomment the next 2 lines to see the exact query you're generating */
        // CRM_Core_Error::debug('sql',$sql);
        // exit();
		
        return $sql;
    }
    
    function from( ) {
    return "
    civicrm_case_activity a
    INNER JOIN  civicrm_value_fmla_hours_used_4 as fmla ON fmla.entity_id = a.activity_id INNER JOIN civicrm_case_contact as c ON c.case_id = a.case_id INNER JOIN civicrm_activity as h ON h.id = a.activity_id AND is_current_revision = 1 AND h.activity_date_time > DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR) INNER JOIN civicrm_value_employment_information_1 as e ON e.entity_id = c.contact_id INNER JOIN civicrm_contact ON civicrm_contact.id = c.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 = array( );

    // These are required filters for our query.
    //$clauses[] = "civicrm_contact.contact_type = 'Individual'";

        // These are conditional filters based on user input
    $name   = CRM_Utils_Array::value( 'display_name',
                                     $this->_formValues );
    if ( $name != null ) {
        if ( strpos( $name, '%' ) === false ) {
            $name = "%{$name}%";
        }
        $clauses[] = "civicrm_contact.display_name LIKE '$name'";
    }

   /*     $state = CRM_Utils_Array::value( 'state_province_id',
                                         $this->_formValues );
        if ( $state ) {
            $clauses[] = "indSP.id = $state";
        }
  */          
        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[] = "c.contact_id IN ( $contactIDs )";
            }
        }
            
        return implode( ' AND ', $clauses );
    }


    function having( $includeContactIDs = false ) {
        $clauses = array( );
        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);
    }
       
    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;
   }

}

Donald Lobo · April 27, 2011, 08:32:44 pm

In general adding a file as an attachment in the forum is a lot easier than scrolling thru code in the browser

could not see the error easily. Your best bet might be to enable mysql logging and see the query that is generated

aaah, while typing this response realized that display name is of the format

PREFIX FIRST_NAME LAST_NAME SUFFIX

so you might want to use sort_name in your query rather than display_name

lobo

theMusician · April 28, 2011, 10:37:41 am

I'll try to remember to upload files, rather than pasting them between the code tags in the future.

Thanks for seeing the sort_name issue. I didn't realize that it was an actual column in the database. That resolved the problem. I changed all display_name occurrences to sort_name.

Thanks again.

CiviCRM Community Forums (archive)

News:

Author Topic: Search by last name, first name returns no results (Read 455 times)

theMusician

Search by last name, first name returns no results

Donald Lobo

Re: Search by last name, first name returns no results

theMusician

Re: Search by last name, first name returns no results