3 +--------------------------------------------------------------------+
4 | CiviCRM version 4.4 |
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2013 |
7 +--------------------------------------------------------------------+
8 | This file is a part of CiviCRM. |
10 | CiviCRM is free software; you can copy, modify, and distribute it |
11 | under the terms of the GNU Affero General Public License |
12 | Version 3, 19 November 2007 and the CiviCRM Licensing Exception. |
14 | CiviCRM is distributed in the hope that it will be useful, but |
15 | WITHOUT ANY WARRANTY; without even the implied warranty of |
16 | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. |
17 | See the GNU Affero General Public License for more details. |
19 | You should have received a copy of the GNU Affero General Public |
20 | License and the CiviCRM Licensing Exception along |
21 | with this program; if not, contact CiviCRM LLC |
22 | at info[AT]civicrm[DOT]org. If you have questions about the |
23 | GNU Affero General Public License or the licensing of CiviCRM, |
24 | see the CiviCRM license FAQ at http://civicrm.org/licensing |
25 +--------------------------------------------------------------------+
31 * @copyright CiviCRM LLC (c) 2004-2013
35 class CRM_Contact_Form_Search_Custom_ActivitySearch
implements CRM_Contact_Form_Search_Interface
{
37 protected $_formValues;
39 function __construct(&$formValues) {
40 $this->_formValues
= $formValues;
43 * Define the columns for search result rows
45 $this->_columns
= array(
46 ts('Name') => 'sort_name',
47 ts('Status') => 'activity_status',
48 ts('Activity Type') => 'activity_type',
49 ts('Activity Subject') => 'activity_subject',
50 ts('Scheduled By') => 'source_contact',
51 ts('Scheduled Date') => 'activity_date',
52 ts(' ') => 'activity_id',
53 ts(' ') => 'activity_type_id',
55 ts('Location') => 'location',
56 ts('Duration') => 'duration',
57 ts('Details') => 'details',
58 ts('Assignee') => 'assignee',
61 $this->_groupId
= CRM_Core_DAO
::getFieldValue('CRM_Core_DAO_OptionGroup',
67 //Add custom fields to columns array for inclusion in export
68 $groupTree = &CRM_Core_BAO_CustomGroup
::getTree('Activity', $form, NULL,
73 //use simplified formatted groupTree
74 $groupTree = CRM_Core_BAO_CustomGroup
::formatGroupTree($groupTree, 1, $form);
76 //cycle through custom fields and assign to _columns array
77 foreach ($groupTree as $key) {
78 foreach ($key['fields'] as $field) {
79 $fieldlabel = $key['title'] . ": " . $field['label'];
80 $this->_columns
[$fieldlabel] = $field['column_name'];
86 function buildForm(&$form) {
89 * You can define a custom title for the search form
91 $this->setTitle('Find Latest Activities');
94 * Define the search form fields here
96 // Allow user to choose which type of contact to limit search on
97 $form->add('select', 'contact_type', ts('Find...'), CRM_Core_SelectValues
::contactType());
99 // Text box for Activity Subject
102 ts('Activity Subject')
105 // Select box for Activity Type
106 $activityType = array('' => ' - select activity - ') + CRM_Core_PseudoConstant
::activityType();
108 $form->add('select', 'activity_type_id', ts('Activity Type'),
113 // textbox for Activity Status
114 $activityStatus = array('' => ' - select status - ') + CRM_Core_PseudoConstant
::activityStatus();
116 $form->add('select', 'activity_status_id', ts('Activity Status'),
121 // Activity Date range
122 $form->addDate('start_date', ts('Activity Date From'), FALSE, array('formatType' => 'custom'));
123 $form->addDate('end_date', ts('...through'), FALSE, array('formatType' => 'custom'));
126 // Contact Name field
127 $form->add('text', 'sort_name', ts('Contact Name'));
130 * If you are using the sample template, this array tells the template fields to render
131 * for the search form.
133 $form->assign('elements', array(
134 'contact_type', 'activity_subject', 'activity_type_id',
135 'activity_status_id', 'start_date', 'end_date', 'sort_name',
140 * Define the smarty template used to layout the search form and results listings.
142 function templateFile() {
143 return 'CRM/Contact/Form/Search/Custom/ActivitySearch.tpl';
147 * Construct the search query
149 function all($offset = 0, $rowcount = 0, $sort = NULL,
150 $includeContactIDs = FALSE, $justIDs = FALSE
153 // SELECT clause must include contact_id as an alias for civicrm_contact.id
155 $select = 'contact_a.id as contact_id';
159 contact_a.id as contact_id,
160 contact_a.sort_name as sort_name,
161 contact_a.contact_type as contact_type,
162 activity.id as activity_id,
163 activity.activity_type_id as activity_type_id,
164 contact_b.sort_name as source_contact,
165 ov1.label as activity_type,
166 activity.subject as activity_subject,
167 activity.activity_date_time as activity_date,
168 ov2.label as activity_status,
169 cca.case_id as case_id,
170 activity.location as location,
171 activity.duration as duration,
172 activity.details as details,
173 assignment.activity_id as assignment_activity,
174 contact_c.display_name as assignee
178 $from = $this->from();
180 $where = $this->where($includeContactIDs);
182 if (!empty($where)) {
183 $where = "WHERE $where";
186 // add custom group fields to SELECT and FROM clause
187 $groupTree = CRM_Core_BAO_CustomGroup
::getTree('Activity', $form, NULL, NULL, '', NULL);
189 foreach ($groupTree as $key) {
190 if ($key['extends'] == 'Activity') {
191 $select .= ", " . $key['table_name'] . ".*";
192 $from .= " LEFT JOIN " . $key['table_name'] . " ON " . $key['table_name'] . ".entity_id = activity.id";
195 // end custom groups add
197 $sql = " SELECT $select FROM $from $where ";
199 //no need to add order when only contact Ids.
201 // Define ORDER BY for query in $sort, with default value
203 if (is_string($sort)) {
204 $sort = CRM_Utils_Type
::escape($sort, 'String');
205 $sql .= " ORDER BY $sort ";
208 $sql .= ' ORDER BY ' . trim($sort->orderBy());
212 $sql .= 'ORDER BY contact_a.sort_name, activity.activity_date_time DESC, activity.activity_type_id, activity.status_id, activity.subject';
216 if ($rowcount > 0 && $offset >= 0) {
217 $offset = CRM_Utils_Type
::escape($offset, 'Int');
218 $rowcount = CRM_Utils_Type
::escape($rowcount, 'Int');
219 $sql .= " LIMIT $offset, $rowcount ";
224 // Alters the date display in the Activity Date Column. We do this after we already have
225 // the result so that sorting on the date column stays pertinent to the numeric date value
226 function alterRow(&$row) {
227 $row['activity_date'] = CRM_Utils_Date
::customFormat($row['activity_date'], '%B %E%f, %Y %l:%M %P');
230 // Regular JOIN statements here to limit results to contacts who have activities.
232 $activityContacts = CRM_Core_OptionGroup
::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
233 $assigneeID = CRM_Utils_Array
::key('Activity Assignees', $activityContacts);
234 $targetID = CRM_Utils_Array
::key('Activity Targets', $activityContacts);
235 $sourceID = CRM_Utils_Array
::key('Activity Source', $activityContacts);
238 civicrm_activity activity
239 LEFT JOIN civicrm_activity_contact target
240 ON activity.id = target.activity_id AND target.record_type_id = {$targetID}
241 JOIN civicrm_contact contact_a
242 ON contact_a.id = target.contact_id
243 JOIN civicrm_option_value ov1
244 ON activity.activity_type_id = ov1.value AND ov1.option_group_id = 2
245 JOIN civicrm_option_value ov2
246 ON activity.status_id = ov2.value AND ov2.option_group_id = {$this->_groupId}
247 LEFT JOIN civicrm_activity_contact sourceContact
248 ON activity.id = sourceContact.activity_id AND sourceContact.record_type_id = {$sourceID}
249 JOIN civicrm_contact contact_b
250 ON sourceContact.contact_id = contact_b.id
251 LEFT JOIN civicrm_case_activity cca
252 ON activity.id = cca.activity_id
253 LEFT JOIN civicrm_activity_contact assignment
254 ON activity.id = assignment.activity_id AND assignment.record_type_id = {$assigneeID}
255 LEFT JOIN civicrm_contact contact_c
256 ON assignment.contact_id = contact_c.id ";
260 * WHERE clause is an array built from any required JOINS plus conditional filters based on search criteria field values
263 function where($includeContactIDs = FALSE) {
266 // add contact name search; search on primary name, source contact, assignee
267 $contactname = $this->_formValues
['sort_name'];
268 if (!empty($contactname)) {
269 $dao = new CRM_Core_DAO();
270 $contactname = $dao->escape($contactname);
271 $clauses[] = "(contact_a.sort_name LIKE '%{$contactname}%' OR
272 contact_b.sort_name LIKE '%{$contactname}%' OR
273 contact_c.display_name LIKE '%{$contactname}%')";
276 $subject = $this->_formValues
['activity_subject'];
278 if (!empty($this->_formValues
['contact_type'])) {
279 $clauses[] = "contact_a.contact_type LIKE '%{$this->_formValues['contact_type']}%'";
282 if (!empty($subject)) {
283 $dao = new CRM_Core_DAO();
284 $subject = $dao->escape($subject);
285 $clauses[] = "activity.subject LIKE '%{$subject}%'";
288 if (!empty($this->_formValues
['activity_status_id'])) {
289 $clauses[] = "activity.status_id = {$this->_formValues['activity_status_id']}";
292 if (!empty($this->_formValues
['activity_type_id'])) {
293 $clauses[] = "activity.activity_type_id = {$this->_formValues['activity_type_id']}";
296 $startDate = $this->_formValues
['start_date'];
297 if (!empty($startDate)) {
298 $startDate .= '00:00:00';
299 $startDateFormatted = CRM_Utils_Date
::processDate($startDate);
300 if ($startDateFormatted) {
301 $clauses[] = "activity.activity_date_time >= $startDateFormatted";
305 $endDate = $this->_formValues
['end_date'];
306 if (!empty($endDate)) {
307 $endDate .= '23:59:59';
308 $endDateFormatted = CRM_Utils_Date
::processDate($endDate);
309 if ($endDateFormatted) {
310 $clauses[] = "activity.activity_date_time <= $endDateFormatted";
314 if ($includeContactIDs) {
315 $contactIDs = array();
316 foreach ($this->_formValues
as $id => $value) {
318 substr($id, 0, CRM_Core_Form
::CB_PREFIX_LEN
) == CRM_Core_Form
::CB_PREFIX
320 $contactIDs[] = substr($id, CRM_Core_Form
::CB_PREFIX_LEN
);
324 if (!empty($contactIDs)) {
325 $contactIDs = implode(', ', $contactIDs);
326 $clauses[] = "contact_a.id IN ( $contactIDs )";
330 return implode(' AND ', $clauses);
334 * Functions below generally don't need to be modified
339 $dao = CRM_Core_DAO
::executeQuery($sql,
340 CRM_Core_DAO
::$_nullArray
345 function contactIDs($offset = 0, $rowcount = 0, $sort = NULL) {
346 return $this->all($offset, $rowcount, $sort, FALSE, TRUE);
349 function &columns() {
350 return $this->_columns
;
353 function setTitle($title) {
355 CRM_Utils_System
::setTitle($title);
358 CRM_Utils_System
::setTitle(ts('Search'));