4 +--------------------------------------------------------------------+
5 | CiviCRM version 4.3 |
6 +--------------------------------------------------------------------+
7 | Copyright CiviCRM LLC (c) 2004-2013 |
8 +--------------------------------------------------------------------+
9 | This file is a part of CiviCRM. |
11 | CiviCRM is free software; you can copy, modify, and distribute it |
12 | under the terms of the GNU Affero General Public License |
13 | Version 3, 19 November 2007 and the CiviCRM Licensing Exception. |
15 | CiviCRM is distributed in the hope that it will be useful, but |
16 | WITHOUT ANY WARRANTY; without even the implied warranty of |
17 | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. |
18 | See the GNU Affero General Public License for more details. |
20 | You should have received a copy of the GNU Affero General Public |
21 | License and the CiviCRM Licensing Exception along |
22 | with this program; if not, contact CiviCRM LLC |
23 | at info[AT]civicrm[DOT]org. If you have questions about the |
24 | GNU Affero General Public License or the licensing of CiviCRM, |
25 | see the CiviCRM license FAQ at http://civicrm.org/licensing |
26 +--------------------------------------------------------------------+
32 * @copyright CiviCRM LLC (c) 2004-2013
36 class CRM_Activity_BAO_Query
{
39 * build select for Case
44 static function select(&$query) {
45 if (CRM_Utils_Array
::value('activity_id', $query->_returnProperties
)) {
46 $query->_select
['activity_id'] = "civicrm_activity.id as activity_id";
47 $query->_element
['activity_id'] = 1;
48 $query->_tables
['civicrm_activity'] = $query->_whereTables
['civicrm_activity'] = 1;
51 if (CRM_Utils_Array
::value('activity_type_id', $query->_returnProperties
)) {
52 $query->_select
['activity_type_id'] = "activity_type.id as activity_type_id";
53 $query->_element
['activity_type_id'] = 1;
54 $query->_tables
['civicrm_activity'] = 1;
55 $query->_tables
['activity_type'] = 1;
56 $query->_whereTables
['civicrm_activity'] = 1;
57 $query->_whereTables
['activity_type'] = 1;
60 if (CRM_Utils_Array
::value('activity_type', $query->_returnProperties
)) {
61 $query->_select
['activity_type'] = "activity_type.label as activity_type";
62 $query->_element
['activity_type'] = 1;
63 $query->_tables
['civicrm_activity'] = 1;
64 $query->_tables
['activity_type'] = 1;
65 $query->_whereTables
['civicrm_activity'] = 1;
66 $query->_whereTables
['activity_type'] = 1;
69 if (CRM_Utils_Array
::value('activity_subject', $query->_returnProperties
)) {
70 $query->_select
['activity_subject'] = "civicrm_activity.subject as activity_subject";
71 $query->_element
['activity_subject'] = 1;
72 $query->_tables
['civicrm_activity'] = $query->_whereTables
['civicrm_activity'] = 1;
75 if (CRM_Utils_Array
::value('activity_date_time', $query->_returnProperties
)) {
76 $query->_select
['activity_date_time'] = "civicrm_activity.activity_date_time as activity_date_time";
77 $query->_element
['activity_date_time'] = 1;
78 $query->_tables
['civicrm_activity'] = $query->_whereTables
['civicrm_activity'] = 1;
81 if (CRM_Utils_Array
::value('activity_status_id', $query->_returnProperties
)) {
82 $query->_select
['activity_status_id'] = "activity_status.value as activity_status_id";
83 $query->_element
['activity_status_id'] = 1;
84 $query->_tables
['civicrm_activity'] = 1;
85 $query->_tables
['activity_status'] = 1;
86 $query->_whereTables
['civicrm_activity'] = 1;
87 $query->_whereTables
['activity_status'] = 1;
90 if (CRM_Utils_Array
::value('activity_status', $query->_returnProperties
)) {
91 $query->_select
['activity_status'] = "activity_status.label as activity_status";
92 $query->_element
['activity_status'] = 1;
93 $query->_tables
['civicrm_activity'] = 1;
94 $query->_tables
['activity_status'] = 1;
95 $query->_whereTables
['civicrm_activity'] = 1;
96 $query->_whereTables
['activity_status'] = 1;
99 if (CRM_Utils_Array
::value('activity_duration', $query->_returnProperties
)) {
100 $query->_select
['activity_duration'] = "civicrm_activity.duration as activity_duration";
101 $query->_element
['activity_duration'] = 1;
102 $query->_tables
['civicrm_activity'] = $query->_whereTables
['civicrm_activity'] = 1;
105 if (CRM_Utils_Array
::value('activity_location', $query->_returnProperties
)) {
106 $query->_select
['activity_location'] = "civicrm_activity.location as activity_location";
107 $query->_element
['activity_location'] = 1;
108 $query->_tables
['civicrm_activity'] = $query->_whereTables
['civicrm_activity'] = 1;
111 if (CRM_Utils_Array
::value('activity_details', $query->_returnProperties
)) {
112 $query->_select
['activity_details'] = "civicrm_activity.details as activity_details";
113 $query->_element
['activity_details'] = 1;
114 $query->_tables
['civicrm_activity'] = $query->_whereTables
['civicrm_activity'] = 1;
117 if (CRM_Utils_Array
::value('source_record_id', $query->_returnProperties
)) {
118 $query->_select
['source_record_id'] = "civicrm_activity.source_record_id as source_record_id";
119 $query->_element
['source_record_id'] = 1;
120 $query->_tables
['civicrm_activity'] = $query->_whereTables
['civicrm_activity'] = 1;
123 if (CRM_Utils_Array
::value('activity_is_test', $query->_returnProperties
)) {
124 $query->_select
['activity_is_test'] = "civicrm_activity.is_test as activity_is_test";
125 $query->_element
['activity_is_test'] = 1;
126 $query->_tables
['civicrm_activity'] = $query->_whereTables
['civicrm_activity'] = 1;
129 if (CRM_Utils_Array
::value('activity_campaign_id', $query->_returnProperties
)) {
130 $query->_select
['activity_campaign_id'] = 'civicrm_activity.campaign_id as activity_campaign_id';
131 $query->_element
['activity_campaign_id'] = 1;
132 $query->_tables
['civicrm_activity'] = $query->_whereTables
['civicrm_activity'] = 1;
135 if (CRM_Utils_Array
::value('activity_engagement_level', $query->_returnProperties
)) {
136 $query->_select
['activity_engagement_level'] = 'civicrm_activity.engagement_level as activity_engagement_level';
137 $query->_element
['activity_engagement_level'] = 1;
138 $query->_tables
['civicrm_activity'] = $query->_whereTables
['civicrm_activity'] = 1;
141 if (CRM_Utils_Array
::value('source_contact', $query->_returnProperties
)) {
142 $query->_select
['source_contact'] = 'source_contact.display_name as source_contact';
143 $query->_element
['source_contact'] = 1;
144 $query->_tables
['source_contact'] = $query->_whereTables
['source_contact'] = 1;
149 * Given a list of conditions in query generate the required
155 static function where(&$query) {
156 $grouping = $testCondition = NULL;
157 foreach (array_keys($query->_params
) as $id) {
158 if (substr($query->_params
[$id][0], 0, 9) == 'activity_') {
159 if ($query->_mode
== CRM_Contact_BAO_QUERY
::MODE_CONTACTS
) {
160 $query->_useDistinct
= TRUE;
162 if ($query->_params
[$id][0] == 'activity_test') {
163 $testCondition = $id;
166 $grouping = $query->_params
[$id][3];
167 self
::whereClauseSingle($query->_params
[$id], $query);
170 // Only add test condition if other fields are selected
171 if ($grouping !== NULL && $testCondition &&
172 // we dont want to include all tests for sql OR CRM-7827
173 $query->getOperator() != 'OR'
175 self
::whereClauseSingle($query->_params
[$testCondition], $query);
180 * where clause for a single field
185 static function whereClauseSingle(&$values, &$query) {
186 list($name, $op, $value, $grouping, $wildcard) = $values;
188 $strtolower = function_exists('mb_strtolower') ?
'mb_strtolower' : 'strtolower';
189 $query->_tables
['civicrm_activity'] = $query->_whereTables
['civicrm_activity'] = 1;
190 if ($query->_mode
& CRM_Contact_BAO_Query
::MODE_ACTIVITY
) {
191 $query->_skipDeleteClause
= TRUE;
195 case 'activity_type_id':
196 $types = CRM_Core_PseudoConstant
::activityType(TRUE, TRUE, FALSE, 'label', TRUE);
198 //get the component activity types.
199 $compActTypes = CRM_Core_PseudoConstant
::activityType(TRUE, TRUE, FALSE, 'label', TRUE, TRUE);
202 if (is_array($value)) {
203 foreach ($value as $id => $dontCare) {
204 if (array_key_exists($id, $types) && $dontCare) {
205 $clause[] = "'" . CRM_Utils_Type
::escape($types[$id], 'String') . "'";
206 if (array_key_exists($id, $compActTypes)) {
207 CRM_Contact_BAO_Query
::$_considerCompActivities = TRUE;
211 $activityTypes = implode(',', array_keys($value));
214 $clause[] = "'" . CRM_Utils_Type
::escape($value, 'String') . "'";
215 $activityTypes = $value;
216 if (array_key_exists($value, $compActTypes)) {
217 CRM_Contact_BAO_Query
::$_considerCompActivities = TRUE;
220 $query->_where
[$grouping][] = ' civicrm_activity.activity_type_id IN (' . $activityTypes . ')';
221 $query->_qill
[$grouping][] = ts('Activity Type') . ' ' . implode(' ' . ts('or') . ' ', $clause);
224 case 'activity_survey_id':
228 $value = CRM_Utils_Type
::escape($value, 'Integer');
229 $query->_where
[$grouping][] = " source_record_id = $value";
230 $query->_qill
[$grouping][] = ts('Survey') . ' - ' . CRM_Core_DAO
::getFieldValue('CRM_Campaign_DAO_Survey', $value, 'title');
233 case 'activity_engagement_level':
238 $value = CRM_Utils_Type
::escape($value, 'Integer');
239 $query->_where
[$grouping][] = " engagement_level = $value";
240 $query->_qill
[$grouping][] = ts('Engagement Index') . ' - ' . CRM_Core_OptionGroup
::getLabel('engagement_index', $value);
243 case 'activity_role':
244 CRM_Contact_BAO_Query
::$_activityRole = $values[2];
245 $activityContacts = CRM_Core_PseudoConstant
::activityContacts('name');
246 $sourceID = CRM_Utils_Array
::key('Activity Source', $activityContacts);
247 $assigneeID = CRM_Utils_Array
::key('Activity Assignees', $activityContacts);
248 $targetID = CRM_Utils_Array
::key('Activity Targets', $activityContacts);
251 $query->_tables
['civicrm_activity_contact'] = $query->_whereTables
['civicrm_activity_contact'] = 1;
252 if ($values[2] == 1) {
253 $query->_where
[$grouping][] = " civicrm_activity_contact.record_type_id = $sourceID";
254 $query->_qill
[$grouping][] = ts('Activity created by');
256 else if ($values[2] == 2) {
257 $query->_where
[$grouping][] = " civicrm_activity_contact.record_type_id = $assigneeID";
258 $query->_qill
[$grouping][] = ts('Activity assigned to');
260 else if ($values[2] == 3) {
261 $query->_where
[$grouping][] = " civicrm_activity_contact.record_type_id = $targetID";
262 $query->_qill
[$grouping][] = ts('Activity targeted to');
267 case 'activity_status':
268 $status = CRM_Core_PseudoConstant
::activityStatus();
270 if (is_array($value)) {
271 foreach ($value as $k => $v) {
273 $clause[] = "'" . CRM_Utils_Type
::escape($status[$k], 'String') . "'";
278 $clause[] = "'" . CRM_Utils_Type
::escape($value, 'String') . "'";
280 $query->_where
[$grouping][] = ' civicrm_activity.status_id IN (' . implode(',', array_keys($value)) . ')';
281 $query->_qill
[$grouping][] = ts('Activity Status') . ' - ' . implode(' ' . ts('or') . ' ', $clause);
284 case 'activity_subject':
286 $value = strtolower(CRM_Core_DAO
::escapeString($n));
288 if (strpos($value, '%') !== FALSE) {
289 // only add wild card if not there
293 $value = "'%$value%'";
300 $wc = ($op != 'LIKE') ?
"LOWER(civicrm_activity.subject)" : "civicrm_activity.subject";
301 $query->_where
[$grouping][] = " $wc $op $value";
302 $query->_qill
[$grouping][] = ts('Subject') . " $op - '$n'";
305 case 'activity_test':
306 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("civicrm_activity.is_test", $op, $value, "Boolean");
308 $query->_qill
[$grouping][] = ts('Activity is a Test');
312 case 'activity_date':
313 case 'activity_date_low':
314 case 'activity_date_high':
315 $query->dateQueryBuilder($values,
316 'civicrm_activity', 'activity_date', 'activity_date_time', ts('Activity Date')
325 if (is_array($value)) {
326 foreach ($value as $k => $v) {
327 $value[$k] = CRM_Utils_Type
::escape($v, 'Integer');
331 $value = array(CRM_Utils_Type
::escape($value, 'Integer'));
333 $query->_where
[$grouping][] = "civicrm_activity.id IN (" . implode(",", $value) . ")";
334 $query->_qill
[$grouping][] = ts('Activity Id(s) %1', array(1 => implode($value)));
337 case 'activity_taglist':
340 foreach ($taglist as $val) {
342 $val = explode(',', $val);
343 foreach ($val as $tId) {
344 if (is_numeric($tId)) {
351 case 'activity_tags':
352 $value = array_keys($value);
353 $activityTags = CRM_Core_PseudoConstant
::tag();
357 if (is_array($value)) {
358 foreach ($value as $k => $v) {
359 $names[] = $activityTags[$v];
362 $query->_where
[$grouping][] = "civicrm_activity_tag.tag_id IN (" . implode(",", $value) . ")";
363 $query->_qill
[$grouping][] = ts('Activity Tag %1', array(1 => $op)) . ' ' . implode(' ' . ts('OR') . ' ', $names);
364 $query->_tables
['civicrm_activity_tag'] = $query->_whereTables
['civicrm_activity_tag'] = 1;
367 case 'activity_campaign_id':
370 'campaign' => $value,
371 'grouping' => $grouping,
372 'tableName' => 'civicrm_activity',
374 CRM_Campaign_BAO_Query
::componentSearchClause($campParams, $query);
379 static function from($name, $mode, $side) {
382 case 'civicrm_activity':
383 //CRM-7480 we are going to civicrm_activitty table either
384 //from civicrm_activity_target or civicrm_activity_assignment.
385 //as component specific activities does not have entry in
386 //activity target table so lets consider civicrm_activity_assignment.
387 $from .= " INNER JOIN civicrm_activity_contact
388 ON ( civicrm_activity_contact.contact_id = contact_a.id ) ";
389 $from .= " INNER JOIN civicrm_activity
390 ON ( civicrm_activity.id = civicrm_activity_contact.activity_id
391 AND civicrm_activity.is_deleted = 0 AND civicrm_activity.is_current_revision = 1 )";
393 $activityRole = CRM_Contact_BAO_Query
::$_activityRole;
396 case 'activity_status':
397 $from .= " $side JOIN civicrm_option_group option_group_activity_status ON (option_group_activity_status.name = 'activity_status')";
398 $from .= " $side JOIN civicrm_option_value activity_status ON (civicrm_activity.status_id = activity_status.value
399 AND option_group_activity_status.id = activity_status.option_group_id ) ";
402 case 'activity_type':
403 $from .= " $side JOIN civicrm_option_group option_group_activity_type ON (option_group_activity_type.name = 'activity_type')";
404 $from .= " $side JOIN civicrm_option_value activity_type ON (civicrm_activity.activity_type_id = activity_type.value
405 AND option_group_activity_type.id = activity_type.option_group_id ) ";
408 case 'civicrm_activity_tag':
409 $from .= " $side JOIN civicrm_entity_tag as civicrm_activity_tag ON ( civicrm_activity_tag.entity_table = 'civicrm_activity' AND civicrm_activity_tag.entity_id = civicrm_activity.id ) ";
412 case 'source_contact':
413 $from = " $side JOIN civicrm_contact source_contact ON source_contact.id = civicrm_activity.source_contact_id";
421 * getter for the qill object
427 return (isset($this->_qill
)) ?
$this->_qill
: "";
431 * add all the elements shared between case activity search and advanaced search
438 static function buildSearchForm(&$form) {
439 $activityOptions = CRM_Core_PseudoConstant
::activityType(TRUE, TRUE, FALSE, 'label', TRUE);
440 asort($activityOptions);
441 foreach ($activityOptions as $activityID => $activity) {
442 $form->_activityElement
=&
445 "activity_type_id[$activityID]",
448 array('onClick' => 'showCustomData( this.id );')
452 CRM_Core_Form_Date
::buildDateRange($form, 'activity_date', 1, '_low', '_high', ts('From'), FALSE, FALSE);
454 $activityRoles = array(
455 1 => ts('Created by'),
456 2 => ts('Assigned to'),
457 3 => ts('Targeted to')
459 $form->addRadio('activity_role', NULL, $activityRoles);
461 $activityStatus = CRM_Core_PseudoConstant
::activityStatus();
462 foreach ($activityStatus as $activityStatusID => $activityStatusName) {
463 $activity_status[] = $form->createElement('checkbox', $activityStatusID, NULL, $activityStatusName);
465 $form->addGroup($activity_status, 'activity_status', ts('Activity Status'));
466 $form->setDefaults(array('activity_status[1]' => 1, 'activity_status[2]' => 1));
467 $form->addElement('text', 'activity_subject', ts('Subject'), CRM_Core_DAO
::getAttribute('CRM_Contact_DAO_Contact', 'sort_name'));
468 $form->addYesNo('activity_test', ts('Activity is a Test?'));
469 $activity_tags = CRM_Core_BAO_Tag
::getTags('civicrm_activity');
470 if ($activity_tags) {
471 foreach ($activity_tags as $tagID => $tagName) {
472 $form->_tagElement
= &$form->addElement('checkbox', "activity_tags[$tagID]",
478 $parentNames = CRM_Core_BAO_Tag
::getTagSet('civicrm_activity');
479 CRM_Core_Form_Tag
::buildQuickForm($form, $parentNames, 'civicrm_activity', NULL, TRUE, FALSE, TRUE);
481 $surveys = CRM_Campaign_BAO_Survey
::getSurveys();
482 if ($surveys) $form->add('select', 'activity_survey_id', ts('Survey'),
484 '' => ts('- none -')) +
$surveys, FALSE
487 $extends = array('Activity');
488 $groupDetails = CRM_Core_BAO_CustomGroup
::getGroupDetail(NULL, TRUE, $extends);
490 $form->assign('activityGroupTree', $groupDetails);
491 foreach ($groupDetails as $group) {
492 foreach ($group['fields'] as $field) {
493 $fieldId = $field['id'];
494 $elementName = 'custom_' . $fieldId;
495 CRM_Core_BAO_CustomField
::addQuickFormElement($form, $elementName, $fieldId, FALSE, FALSE, TRUE);
500 CRM_Campaign_BAO_Campaign
::addCampaignInComponentSearch($form, 'activity_campaign_id');
502 //add engagement level CRM-7775
503 $buildEngagementLevel = FALSE;
504 if (CRM_Campaign_BAO_Campaign
::isCampaignEnable() &&
505 CRM_Campaign_BAO_Campaign
::accessCampaign()
507 $buildEngagementLevel = TRUE;
508 $form->add('select', 'activity_engagement_level',
509 ts('Engagement Index'),
510 array('' => ts('- any -')) + CRM_Campaign_PseudoConstant
::engagementLevel()
514 $form->assign('buildEngagementLevel', $buildEngagementLevel);
515 $form->setDefaults(array('activity_test' => 0));
518 static function addShowHide(&$showHide) {
519 $showHide->addHide('caseActivityForm');
520 $showHide->addShow('caseActivityForm_show');
523 static function defaultReturnProperties($mode, $includeCustomFields = TRUE) {
525 if ($mode & CRM_Contact_BAO_Query
::MODE_ACTIVITY
) {
529 'contact_sub_type' => 1,
532 'activity_type' => 1,
533 'activity_subject' => 1,
534 'activity_date_time' => 1,
535 'activity_duration' => 1,
536 'activity_location' => 1,
537 'activity_details' => 1,
538 'activity_status' => 1,
539 'source_contact_id' => 1,
540 'source_record_id' => 1,
541 'activity_is_test' => 1,
542 'activity_campaign_id' => 1,
543 'activity_engagement_level' => 1,
546 if ($includeCustomFields) {
547 // also get all the custom activity properties
548 $fields = CRM_Core_BAO_CustomField
::getFieldsForImport('Activity');
549 if (!empty($fields)) {
550 foreach ($fields as $name => $dontCare) {
551 $properties[$name] = 1;