CiviCRM Community Forums (archive)

Hi all,

After much searching around i have come up empty handed on the following problem:

I would like to create a search form that allows users to search events based on custom and existing event data.

i.e. I want users to be able to search particular types of CIVICRM events on specific dates.

I am willing to learn and help to get this type of functionality up and running but i am chasing my tail on where to start.

I am running Drupal 5 and CIVICRM 2.0.1

-Adam

HI,

I have had a read through the http://wiki.civicrm.org/confluence/display/CRMDOC/Custom+Search+Components document and would it be easier to use an AJAX table approach to integrate with the CIVI Event Module.

-Adam

After going through the example on "Use the Custom Search Framework to Create New Search Capabilities" I decided that I should attempt to modify the current "SearchEvent.php" file as it already has some of the functionality that I require.

Is this on the right track because I am having some difficulties applying the methodology used in the example I followed?(see link http://wiki.civicrm.org/confluence/display/CRMDOC/Custom+Search+Components )

Hi,

I would like to expose the search as a single page.

I have tried implementing the Events Aggregate page but I can’t seem to navigate to the page, I have followed the link on the tutorial to http://<your site>-/civicrm/contact/search/custom?csid=6&reset=1 but I can’t seem to find the csid in the code to point it to the right location.

Thanks for your assistance
Adam

Hi Thanks for that Deepack,

I have managed to navigate to the page however now it is giving me this error

user warning: Smarty error: unable to read resource: "CRM/Contact/Form/Search/Custom/EventDetails.tpl" in /home/.../public_html/sites/all/modules/civicrm/packages/Smarty/Smarty.class.php on line 1095.

I have checked the permissions and they are all 777 and EventDetails.tpl is in the correct folder(CRM/Contact/Form/Search/Custom)

I think the problem may be in how i am downloading the file from the wiki. when i "save as" it defaults to EventDetails.html

I rename it to EventDetails.tpl but it seems to not make any difference.

Ok,

I got it working thanks for that Deepack

I think this custom search is quite close to what i need to do, the things i need to change are:

1) Remove payment search criteria and results

2) Return multiple Events as a whole listing(or possibly in a calendar) e.g. with all the details and registration information

With regards to task 2 where would i find the information on how to do this?

-Adam

Hi,

I am currently trying to modify the EventAggregate.php file so that it only searches Dates and Event Types and returns the Event Name and the Event Type. And once that is working I hope to be able to build a more comprehensive search.
I have managed to get the search form working but when I click search it gives the following error: Database Error Code: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE civicrm_contribution.is_test = 0 AND civicrm_participant.status_id in ( 1' at line 6, 1064

This is the code as I have modified it below I have also attached a copy of the code.

Any help in resolving these syntax errors would be much appreciated


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

class CRM_Contact_Form_Search_Custom_UserEventSearch
   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('Event')                    => 'event_name',
                                 ts('Type')                     => 'event_type',
            //                    ts('Paid Online') => 'payment_instrument_id',
      );
    }

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

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


        $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);
        }
       
        $form->add( 'date',
                    'start_date',
                    ts('Payments Date From'),
                    CRM_Core_SelectValues::date('custom', 10, 3 ) );
        $form->addRule('start_date', ts('Select a valid date.'), 'qfDate');

        $form->add( 'date',
                   'end_date',
                    ts('...through'),
                   CRM_Core_SelectValues::date('custom', 10, 0 ) );
        $form->addRule('end_date', ts('Select a valid date.'), 'qfDate');

        /**
         * If you are using the sample template, this array tells the template fields to render
         * for the search form.
         */
        $form->assign( 'elements', array('paid_online', '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/EventDetails.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 if you are going to use "tasks" like export etc.
        $select  = "civicrm_participant.event_id as event_id,
      civicrm_event.title as event_name,
      civicrm_event.event_type_id as event_type_id,
        civicrm_option_value.label as event_type";

        $from  = $this->from();

        $where = $this->where();

        $sql = "
SELECT $select
FROM   $from
WHERE  $where
GROUP BY event_id
";
        // 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 event_name desc";
        }

        if ( $rowcount > 0 && $offset >= 0 ) {
            $sql .= " LIMIT $offset, $rowcount ";
        }
       
        // Uncomment the next line to see the actual SQL generated:
         //CRM_Core_Error::debug('sql',$sql); exit();
        return $sql;
    }

    function from( ) {
        return " ";

       
       

    }

     /*
      * 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[] = "civicrm_contribution.is_test = 0";
        $clauses[] = "civicrm_participant.status_id in ( 1 )";
           
       $startDate = CRM_Utils_Date::format( $this->_formValues['start_date'] );
       if ( $startDate ) {
            $clauses[] = "civicrm_contribution.receive_date >= $startDate";
        }

        $endDate = CRM_Utils_Date::format( $this->_formValues['end_date'] );
        if ( $endDate ) {
            $clauses[] = "civicrm_contribution.receive_date <= $endDate";
       }

        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.id IN ( $contactIDs )";
            }
        }

        if ( ! empty($this->_formValues['event_type_id'] ) ) {
            $event_type_ids = implode(',', array_keys($this->_formValues['event_type_id']));
            $clauses[] = "civicrm_event.event_type_id IN ( $event_type_ids )";
        }
        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'));
       }
   }
       
}

?>

Hi Thanks again Deepak,

I got it working but I would like to clarify something before we continue on this approach.

I would like to display this search to non-admin users of the website i.e. visitors. and when I attempt to test this I am getting "Accessed Denied errors".

I am also getting the Find Contacts, Advanced Search, Search Builder tabs are still visible.

Would it be better to approach this via a development of a new module as previously mentioned?

If so where would I get information on how to do this?

Thanks again for your support
Adam