3 +--------------------------------------------------------------------+
4 | Copyright CiviCRM LLC. All rights reserved. |
6 | This work is published under the GNU AGPLv3 license with some |
7 | permitted exceptions and without any warranty. For full license |
8 | and copyright information, see https://civicrm.org/licensing |
9 +--------------------------------------------------------------------+
15 * @copyright CiviCRM LLC https://civicrm.org/licensing
17 class CRM_Activity_BAO_Query
{
20 * Build select for Case.
22 * @param CRM_Contact_BAO_Query $query
24 public static function select(&$query) {
25 if (!empty($query->_returnProperties
['activity_id'])) {
26 $query->_select
['activity_id'] = 'civicrm_activity.id as activity_id';
27 $query->_element
['activity_id'] = 1;
28 $query->_tables
['civicrm_activity'] = $query->_whereTables
['civicrm_activity'] = 1;
31 if (!empty($query->_returnProperties
['activity_type_id'])
32 ||
!empty($query->_returnProperties
['activity_type'])
34 $query->_select
['activity_type_id'] = 'civicrm_activity.activity_type_id';
35 $query->_element
['activity_type_id'] = 1;
36 $query->_tables
['civicrm_activity'] = 1;
37 $query->_whereTables
['civicrm_activity'] = 1;
40 if (!empty($query->_returnProperties
['activity_subject'])) {
41 $query->_select
['activity_subject'] = 'civicrm_activity.subject as activity_subject';
42 $query->_element
['activity_subject'] = 1;
43 $query->_tables
['civicrm_activity'] = $query->_whereTables
['civicrm_activity'] = 1;
46 if (!empty($query->_returnProperties
['activity_date_time'])) {
47 $query->_select
['activity_date_time'] = 'civicrm_activity.activity_date_time as activity_date_time';
48 $query->_element
['activity_date_time'] = 1;
49 $query->_tables
['civicrm_activity'] = $query->_whereTables
['civicrm_activity'] = 1;
52 if (!empty($query->_returnProperties
['activity_status_id'])) {
53 $query->_select
['activity_status_id'] = 'civicrm_activity.status_id as activity_status_id';
54 $query->_element
['activity_status_id'] = 1;
55 $query->_tables
['civicrm_activity'] = 1;
56 $query->_whereTables
['civicrm_activity'] = 1;
59 if (!empty($query->_returnProperties
['activity_status'])) {
60 $query->_select
['activity_status_id'] = 1;
61 $query->_element
['activity_status'] = 1;
62 $query->_tables
['civicrm_activity'] = 1;
63 $query->_whereTables
['civicrm_activity'] = 1;
66 if (!empty($query->_returnProperties
['activity_duration'])) {
67 $query->_select
['activity_duration'] = 'civicrm_activity.duration as activity_duration';
68 $query->_element
['activity_duration'] = 1;
69 $query->_tables
['civicrm_activity'] = $query->_whereTables
['civicrm_activity'] = 1;
72 if (!empty($query->_returnProperties
['activity_location'])) {
73 $query->_select
['activity_location'] = 'civicrm_activity.location as activity_location';
74 $query->_element
['activity_location'] = 1;
75 $query->_tables
['civicrm_activity'] = $query->_whereTables
['civicrm_activity'] = 1;
78 if (!empty($query->_returnProperties
['activity_details'])) {
79 $query->_select
['activity_details'] = 'civicrm_activity.details as activity_details';
80 $query->_element
['activity_details'] = 1;
81 $query->_tables
['civicrm_activity'] = $query->_whereTables
['civicrm_activity'] = 1;
84 if (!empty($query->_returnProperties
['source_record_id'])) {
85 $query->_select
['source_record_id'] = 'civicrm_activity.source_record_id as source_record_id';
86 $query->_element
['source_record_id'] = 1;
87 $query->_tables
['civicrm_activity'] = $query->_whereTables
['civicrm_activity'] = 1;
90 if (!empty($query->_returnProperties
['activity_is_test'])) {
91 $query->_select
['activity_is_test'] = 'civicrm_activity.is_test as activity_is_test';
92 $query->_element
['activity_is_test'] = 1;
93 $query->_tables
['civicrm_activity'] = $query->_whereTables
['civicrm_activity'] = 1;
96 if (!empty($query->_returnProperties
['activity_campaign_id'])) {
97 $query->_select
['activity_campaign_id'] = 'civicrm_activity.campaign_id as activity_campaign_id';
98 $query->_element
['activity_campaign_id'] = 1;
99 $query->_tables
['civicrm_activity'] = $query->_whereTables
['civicrm_activity'] = 1;
102 if (!empty($query->_returnProperties
['activity_engagement_level'])) {
103 $query->_select
['activity_engagement_level'] = 'civicrm_activity.engagement_level as activity_engagement_level';
104 $query->_element
['activity_engagement_level'] = 1;
105 $query->_tables
['civicrm_activity'] = $query->_whereTables
['civicrm_activity'] = 1;
108 if (!empty($query->_returnProperties
['source_contact'])) {
109 $query->_select
['source_contact'] = 'source_contact.sort_name as source_contact';
110 $query->_element
['source_contact'] = 1;
111 $query->_tables
['civicrm_activity'] = $query->_tables
['source_contact'] = $query->_whereTables
['source_contact'] = 1;
114 if (!empty($query->_returnProperties
['source_contact_id'])) {
115 $query->_select
['source_contact_id'] = 'source_contact.id as source_contact_id';
116 $query->_element
['source_contact_id'] = 1;
117 $query->_tables
['civicrm_activity'] = $query->_tables
['source_contact'] = $query->_whereTables
['source_contact'] = 1;
120 if (!empty($query->_returnProperties
['activity_result'])) {
121 $query->_select
['activity_result'] = 'civicrm_activity.result as activity_result';
122 $query->_element
['result'] = 1;
123 $query->_tables
['civicrm_activity'] = $query->_whereTables
['civicrm_activity'] = 1;
126 if (!empty($query->_returnProperties
['parent_id'])) {
127 $query->_tables
['parent_id'] = 1;
128 $query->_whereTables
['parent_id'] = 1;
129 $query->_element
['parent_id'] = 1;
132 if (!empty($query->_returnProperties
['activity_priority'])) {
133 $query->_select
['activity_priority'] = 'activity_priority.label as activity_priority,
134 civicrm_activity.priority_id as priority_id';
135 $query->_element
['activity_priority'] = 1;
136 $query->_tables
['activity_priority'] = 1;
137 $query->_whereTables
['activity_priority'] = 1;
138 $query->_tables
['civicrm_activity'] = $query->_whereTables
['civicrm_activity'] = 1;
143 * Given a list of conditions in query generate the required where clause.
145 * @param \CRM_Contact_BAO_Query $query
147 * @throws \CRM_Core_Exception
149 public static function where(&$query) {
150 foreach (array_keys($query->_params
) as $id) {
151 if (substr($query->_params
[$id][0], 0, 9) === 'activity_') {
152 if ($query->_mode
== CRM_Contact_BAO_Query
::MODE_CONTACTS
) {
153 $query->_useDistinct
= TRUE;
155 $query->_params
[$id][3];
156 self
::whereClauseSingle($query->_params
[$id], $query);
162 * Where clause for a single field.
164 * @param array $values
165 * @param CRM_Contact_BAO_Query $query
167 * @throws \CRM_Core_Exception
169 public static function whereClauseSingle(&$values, &$query) {
170 list($name, $op, $value, $grouping) = $values;
172 $fields = CRM_Activity_BAO_Activity
::exportableFields();
173 $fieldSpec = $query->getFieldSpec($name);
175 $query->_tables
['civicrm_activity'] = $query->_whereTables
['civicrm_activity'] = 1;
176 if ($query->_mode
& CRM_Contact_BAO_Query
::MODE_ACTIVITY
) {
177 $query->_skipDeleteClause
= TRUE;
181 case 'activity_type':
182 case 'activity_type_id':
183 case 'activity_status':
184 case 'activity_status_id':
185 case 'activity_engagement_level':
187 case 'activity_campaign_id':
188 case 'activity_priority_id':
189 // We no longer expect "subject" as a specific criteria (as of CRM-19447),
190 // but we still use activity_subject in Activity.Get API
191 case 'activity_subject':
192 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause($fieldSpec['where'], $op, $value, $query->getDataTypeForRealField($name));
193 $query->_qill
[$grouping][] = $query->getQillForField($fieldSpec['is_pseudofield_for'] ??
$fieldSpec['name'], $value, $op, $fieldSpec);
196 case 'activity_text':
197 self
::whereClauseSingleActivityText($values, $query);
200 case 'activity_priority':
201 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("$name.label", $op, $value, 'String');
202 list($op, $value) = CRM_Contact_BAO_Query
::buildQillForFieldValue('CRM_Activity_DAO_Activity', $name, $value, $op);
203 $query->_qill
[$grouping][] = ts('%1 %2 %3', [
204 1 => $fields[$name]['title'],
208 $query->_tables
[$name] = $query->_whereTables
[$name] = 1;
211 case 'activity_survey_id':
215 $value = CRM_Utils_Type
::escape($value, 'Integer');
216 $query->_where
[$grouping][] = " civicrm_activity.source_record_id = $value";
217 $query->_qill
[$grouping][] = ts('Survey') . ' - ' . CRM_Core_DAO
::getFieldValue('CRM_Campaign_DAO_Survey', $value, 'title');
220 case 'activity_role':
221 CRM_Contact_BAO_Query
::$_activityRole = $values[2];
222 $activityContacts = CRM_Activity_BAO_ActivityContact
::buildOptions('record_type_id', 'validate');
223 $sourceID = CRM_Utils_Array
::key('Activity Source', $activityContacts);
224 $assigneeID = CRM_Utils_Array
::key('Activity Assignees', $activityContacts);
225 $targetID = CRM_Utils_Array
::key('Activity Targets', $activityContacts);
228 $query->_tables
['civicrm_activity_contact'] = $query->_whereTables
['civicrm_activity_contact'] = 1;
229 if ($values[2] == 1) {
230 $query->_where
[$grouping][] = " civicrm_activity_contact.record_type_id = $sourceID";
231 $query->_qill
[$grouping][] = ts('Activity created by');
233 elseif ($values[2] == 2) {
234 $query->_where
[$grouping][] = " civicrm_activity_contact.record_type_id = $assigneeID";
235 $query->_qill
[$grouping][] = ts('Activity assigned to');
237 elseif ($values[2] == 3) {
238 $query->_where
[$grouping][] = " civicrm_activity_contact.record_type_id = $targetID";
239 $query->_qill
[$grouping][] = ts('Activity targeted to');
244 case 'activity_test':
245 // We don't want to include all tests for sql OR CRM-7827
246 if (!$value ||
$query->getOperator() != 'OR') {
247 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("civicrm_activity.is_test", $op, $value, "Boolean");
249 $query->_qill
[$grouping][] = ts('Activity is a Test');
254 case 'activity_date':
255 case 'activity_date_low':
256 case 'activity_date_high':
257 case 'activity_date_time_low':
258 case 'activity_date_time_high':
259 $query->dateQueryBuilder($values,
260 'civicrm_activity', str_replace([
263 ], '', $name), 'activity_date_time', ts('Activity Date')
267 case 'activity_taglist':
270 foreach ($taglist as $val) {
272 $val = explode(',', $val);
273 foreach ($val as $tId) {
274 if (is_numeric($tId)) {
281 case 'activity_tags':
282 $activityTags = CRM_Core_PseudoConstant
::get('CRM_Core_DAO_EntityTag', 'tag_id', ['onlyActive' => FALSE]);
284 if (!is_array($value)) {
285 $value = explode(',', $value);
289 foreach ($value as $k => $v) {
290 $names[] = $activityTags[$v];
293 $query->_where
[$grouping][] = "civicrm_activity_tag.tag_id IN (" . implode(",", $value) . ")";
294 $query->_qill
[$grouping][] = ts('Activity Tag %1', [1 => $op]) . ' ' . implode(' ' . ts('OR') . ' ', $names);
295 $query->_tables
['civicrm_activity_tag'] = $query->_whereTables
['civicrm_activity_tag'] = 1;
298 case 'activity_result':
299 if (is_array($value)) {
301 foreach ($value as $id => $k) {
302 $safe[] = "'" . CRM_Utils_Type
::escape($k, 'String') . "'";
304 $query->_where
[$grouping][] = "civicrm_activity.result IN (" . implode(',', $safe) . ")";
305 $query->_qill
[$grouping][] = ts("Activity Result - %1", [1 => implode(' or ', $safe)]);
311 $query->_where
[$grouping][] = "civicrm_activity.parent_id IS NOT NULL";
312 $query->_qill
[$grouping][] = ts('Activities which have Followup Activities');
314 elseif ($value == 2) {
315 $query->_where
[$grouping][] = "civicrm_activity.parent_id IS NULL";
316 $query->_qill
[$grouping][] = ts('Activities without Followup Activities');
320 case 'followup_parent_id':
322 $query->_where
[$grouping][] = "civicrm_activity.parent_id IS NOT NULL";
323 $query->_qill
[$grouping][] = ts('Activities which are Followup Activities');
325 elseif ($value == 2) {
326 $query->_where
[$grouping][] = "civicrm_activity.parent_id IS NULL";
327 $query->_qill
[$grouping][] = ts('Activities which are not Followup Activities');
331 case 'source_contact':
332 case 'source_contact_id':
333 $columnName = strstr($name, '_id') ?
'id' : 'sort_name';
334 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("source_contact.{$columnName}", $op, $value, CRM_Utils_Type
::typeToString($fields[$name]['type']));
335 list($op, $value) = CRM_Contact_BAO_Query
::buildQillForFieldValue('CRM_Contact_DAO_Contact', $columnName, $value, $op);
336 $query->_qill
[$grouping][] = ts('%1 %2 %3', [
337 1 => $fields[$name]['title'],
346 * @param string $name
350 * @return null|string
352 public static function from($name, $mode, $side) {
355 case 'civicrm_activity':
356 //CRM-7480 we are going to civicrm_activity table either
357 //from civicrm_activity_target or civicrm_activity_assignment.
358 //as component specific activities does not have entry in
359 //activity target table so lets consider civicrm_activity_assignment.
360 $from .= " $side JOIN civicrm_activity_contact
361 ON ( civicrm_activity_contact.contact_id = contact_a.id ) ";
362 $from .= " $side JOIN civicrm_activity
363 ON ( civicrm_activity.id = civicrm_activity_contact.activity_id
364 AND civicrm_activity.is_deleted = 0 AND civicrm_activity.is_current_revision = 1 )";
365 // Do not show deleted contact's activity
366 $from .= " INNER JOIN civicrm_contact
367 ON ( civicrm_activity_contact.contact_id = civicrm_contact.id and civicrm_contact.is_deleted != 1 )";
370 case 'activity_type':
371 $from .= " $side JOIN civicrm_option_group option_group_activity_type ON (option_group_activity_type.name = 'activity_type')";
372 $from .= " $side JOIN civicrm_option_value activity_type ON (civicrm_activity.activity_type_id = activity_type.value
373 AND option_group_activity_type.id = activity_type.option_group_id ) ";
376 case 'activity_priority':
377 $from .= " $side JOIN civicrm_option_group option_group_activity_priority ON (option_group_activity_priority.name = 'priority')";
378 $from .= " $side JOIN civicrm_option_value activity_priority ON (civicrm_activity.priority_id = activity_priority.value
379 AND option_group_activity_priority.id = activity_priority.option_group_id ) ";
382 case 'civicrm_activity_tag':
383 $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 ) ";
386 case 'source_contact':
387 $sourceID = CRM_Core_PseudoConstant
::getKey(
388 'CRM_Activity_BAO_ActivityContact',
393 LEFT JOIN civicrm_activity_contact source_activity
394 ON (source_activity.activity_id = civicrm_activity_contact.activity_id
395 AND source_activity.record_type_id = {$sourceID})
396 LEFT JOIN civicrm_contact source_contact ON (source_activity.contact_id = source_contact.id)";
400 $from = "$side JOIN civicrm_activity AS parent_id ON civicrm_activity.id = parent_id.parent_id";
408 * Get the metadata for fields to be included on the activity search form.
410 * @throws \CiviCRM_API3_Exception
411 * @todo ideally this would be a trait included on the activity search & advanced search
412 * rather than a static function.
414 public static function getSearchFieldMetadata() {
415 $fields = ['activity_type_id', 'activity_date_time', 'priority_id', 'activity_location', 'activity_status_id'];
416 $metadata = civicrm_api3('Activity', 'getfields', [])['values'];
417 $metadata = array_intersect_key($metadata, array_flip($fields));
418 $metadata['activity_text'] = [
419 'title' => ts('Activity Text'),
420 'type' => CRM_Utils_Type
::T_STRING
,
421 'is_pseudofield' => TRUE,
430 * Add all the elements shared between case activity search and advanced search.
432 * @param CRM_Core_Form_Search $form
434 * @throws \CiviCRM_API3_Exception
435 * @throws \CRM_Core_Exception
437 public static function buildSearchForm(&$form) {
438 $form->addSearchFieldMetadata(['Activity' => self
::getSearchFieldMetadata()]);
439 $form->addFormFieldsFromMetadata();
441 $followUpActivity = [
445 $form->addRadio('parent_id', NULL, $followUpActivity, ['allowClear' => TRUE]);
446 $form->addRadio('followup_parent_id', NULL, $followUpActivity, ['allowClear' => TRUE]);
449 2 => ts('Assigned to'),
452 $form->addRadio('activity_role', NULL, $activityRoles, ['allowClear' => TRUE]);
453 $form->setDefaults(['activity_role' => 3]);
454 $activityStatus = CRM_Core_PseudoConstant
::get('CRM_Activity_DAO_Activity', 'status_id', [
456 'labelColumn' => 'name',
458 $ssID = $form->get('ssID');
459 $status = [$activityStatus['Completed'], $activityStatus['Scheduled']];
460 //If status is saved in smart group.
461 if (!empty($ssID) && !empty($form->_formValues
['activity_status_id'])) {
462 $status = $form->_formValues
['activity_status_id'];
464 $form->setDefaults(['activity_status_id' => $status]);
466 $form->addElement('text', 'activity_text', ts('Activity Text'), CRM_Core_DAO
::getAttribute('CRM_Contact_DAO_Contact', 'sort_name'));
468 $form->addRadio('activity_option', '', CRM_Core_SelectValues
::activityTextOptions());
469 $form->setDefaults(['activity_option' => 'both']);
471 $form->addYesNo('activity_test', ts('Activity is a Test?'));
472 $activity_tags = CRM_Core_BAO_Tag
::getColorTags('civicrm_activity');
474 if ($activity_tags) {
475 $form->add('select2', 'activity_tags', ts('Activity Tag(s)'),
476 $activity_tags, FALSE, [
477 'id' => 'activity_tags',
480 'class' => 'crm-select2',
481 'placeholder' => ts('- select tags -'),
486 $parentNames = CRM_Core_BAO_Tag
::getTagSet('civicrm_activity');
487 CRM_Core_Form_Tag
::buildQuickForm($form, $parentNames, 'civicrm_activity', NULL, TRUE, TRUE);
489 $surveys = CRM_Campaign_BAO_Survey
::getSurveys(TRUE, FALSE, FALSE, TRUE);
491 $form->add('select', 'activity_survey_id', ts('Survey / Petition'),
492 ['' => ts('- none -')] +
$surveys, FALSE,
493 ['class' => 'crm-select2']
497 CRM_Core_BAO_Query
::addCustomFormFields($form, ['Activity']);
499 CRM_Campaign_BAO_Campaign
::addCampaignInComponentSearch($form, 'activity_campaign_id');
501 // Add engagement level CRM-7775.
502 $buildEngagementLevel = FALSE;
503 $buildSurveyResult = FALSE;
504 if (CRM_Campaign_BAO_Campaign
::isCampaignEnable() &&
505 CRM_Campaign_BAO_Campaign
::accessCampaign()
507 $buildEngagementLevel = TRUE;
508 $form->addSelect('activity_engagement_level', [
509 'entity' => 'activity',
510 'context' => 'search',
513 // Add survey result field.
514 $optionGroups = CRM_Campaign_BAO_Survey
::getResultSets('name');
516 foreach ($optionGroups as $gid => $name) {
518 $value = CRM_Core_OptionGroup
::values($name);
519 if (!empty($value)) {
520 foreach ($value as $k => $v) {
521 $resultOptions[$v] = $v;
526 // If no survey result options have been created, don't build
527 // the field to avoid clutter.
528 if (count($resultOptions) > 0) {
529 $buildSurveyResult = TRUE;
530 asort($resultOptions);
531 $form->add('select', 'activity_result', ts("Survey Result"),
532 $resultOptions, FALSE,
534 'id' => 'activity_result',
535 'multiple' => 'multiple',
536 'class' => 'crm-select2',
542 $form->assign('buildEngagementLevel', $buildEngagementLevel);
543 $form->assign('buildSurveyResult', $buildSurveyResult);
544 $form->setDefaults(['activity_test' => 0]);
549 * @param bool $includeCustomFields
553 public static function defaultReturnProperties($mode, $includeCustomFields = TRUE) {
555 if ($mode & CRM_Contact_BAO_Query
::MODE_ACTIVITY
) {
559 'contact_sub_type' => 1,
562 'activity_type' => 1,
563 'activity_type_id' => 1,
564 'activity_subject' => 1,
565 'activity_date_time' => 1,
566 'activity_duration' => 1,
567 'activity_location' => 1,
568 'activity_details' => 1,
569 'activity_status' => 1,
570 'activity_priority' => 1,
571 'source_contact' => 1,
572 'source_record_id' => 1,
573 'activity_is_test' => 1,
574 'activity_campaign_id' => 1,
576 'activity_engagement_level' => 1,
580 if ($includeCustomFields) {
581 // also get all the custom activity properties
582 $fields = CRM_Core_BAO_CustomField
::getFieldsForImport('Activity');
583 if (!empty($fields)) {
584 foreach ($fields as $name => $dontCare) {
585 $properties[$name] = 1;
595 * Get the list of fields required to populate the selector.
597 * The default return properties array returns far too many fields for 'everyday use. Every field you add to this array
598 * kills a small kitten so add carefully.
600 public static function selectorReturnProperties() {
604 'contact_sub_type' => 1,
607 'activity_type_id' => 1,
608 'activity_subject' => 1,
609 'activity_date_time' => 1,
610 'activity_status_id' => 1,
611 'source_contact' => 1,
612 'source_record_id' => 1,
613 'activity_is_test' => 1,
614 'activity_campaign_id' => 1,
615 'activity_engagement_level' => 1,
622 * Where/qill clause for notes
624 * @param array $values
625 * @param CRM_Contact_BAO_Query $query
627 * @throws \CRM_Core_Exception
629 public static function whereClauseSingleActivityText(&$values, &$query) {
630 list($name, $op, $value, $grouping, $wildcard) = $values;
631 $activityOptionValues = $query->getWhereValues('activity_option', $grouping);
632 $activityOption = CRM_Utils_Array
::value(2, $activityOptionValues, 6);
634 $query->_useDistinct
= TRUE;
636 $label = ts('Activity Text (%1)', [1 => CRM_Utils_Array
::value($activityOption, CRM_Core_SelectValues
::activityTextOptions())]);
638 if ($activityOption %
2 == 0) {
639 $clauses[] = $query->buildClause('civicrm_activity.details', $op, $value, 'String');
641 if ($activityOption %
3 == 0) {
642 $clauses[] = $query->buildClause('civicrm_activity.subject', $op, $value, 'String');
645 $query->_where
[$grouping][] = "( " . implode(' OR ', $clauses) . " )";
646 list($qillOp, $qillVal) = $query->buildQillForFieldValue(NULL, $name, $value, $op);
647 $query->_qill
[$grouping][] = ts("%1 %2 '%3'", [