4 +--------------------------------------------------------------------+
5 | CiviCRM version 4.5 |
6 +--------------------------------------------------------------------+
7 | Copyright CiviCRM LLC (c) 2004-2014 |
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-2014
36 class CRM_Activity_BAO_Query
{
39 * build select for Case
46 static function select(&$query) {
47 if (!empty($query->_returnProperties
['activity_id'])) {
48 $query->_select
['activity_id'] = "civicrm_activity.id as activity_id";
49 $query->_element
['activity_id'] = 1;
50 $query->_tables
['civicrm_activity'] = $query->_whereTables
['civicrm_activity'] = 1;
53 if (!empty($query->_returnProperties
['activity_type_id'])) {
54 $query->_select
['activity_type_id'] = "activity_type.id as activity_type_id";
55 $query->_element
['activity_type_id'] = 1;
56 $query->_tables
['civicrm_activity'] = 1;
57 $query->_tables
['activity_type'] = 1;
58 $query->_whereTables
['civicrm_activity'] = 1;
59 $query->_whereTables
['activity_type'] = 1;
62 if (!empty($query->_returnProperties
['activity_type'])) {
63 $query->_select
['activity_type'] = "activity_type.label as activity_type,
64 civicrm_activity.activity_type_id as activity_type_id";
65 $query->_element
['activity_type'] = 1;
66 $query->_tables
['civicrm_activity'] = 1;
67 $query->_tables
['activity_type'] = 1;
68 $query->_whereTables
['civicrm_activity'] = 1;
69 $query->_whereTables
['activity_type'] = 1;
72 if (!empty($query->_returnProperties
['activity_subject'])) {
73 $query->_select
['activity_subject'] = "civicrm_activity.subject as activity_subject";
74 $query->_element
['activity_subject'] = 1;
75 $query->_tables
['civicrm_activity'] = $query->_whereTables
['civicrm_activity'] = 1;
78 if (!empty($query->_returnProperties
['activity_date_time'])) {
79 $query->_select
['activity_date_time'] = "civicrm_activity.activity_date_time as activity_date_time";
80 $query->_element
['activity_date_time'] = 1;
81 $query->_tables
['civicrm_activity'] = $query->_whereTables
['civicrm_activity'] = 1;
84 if (!empty($query->_returnProperties
['activity_status_id'])) {
85 $query->_select
['activity_status_id'] = "activity_status.value as activity_status_id";
86 $query->_element
['activity_status_id'] = 1;
87 $query->_tables
['civicrm_activity'] = 1;
88 $query->_tables
['activity_status'] = 1;
89 $query->_whereTables
['civicrm_activity'] = 1;
90 $query->_whereTables
['activity_status'] = 1;
93 if (!empty($query->_returnProperties
['activity_status'])) {
94 $query->_select
['activity_status'] = "activity_status.label as activity_status";
95 $query->_element
['activity_status'] = 1;
96 $query->_tables
['civicrm_activity'] = 1;
97 $query->_tables
['activity_status'] = 1;
98 $query->_whereTables
['civicrm_activity'] = 1;
99 $query->_whereTables
['activity_status'] = 1;
102 if (!empty($query->_returnProperties
['activity_duration'])) {
103 $query->_select
['activity_duration'] = "civicrm_activity.duration as activity_duration";
104 $query->_element
['activity_duration'] = 1;
105 $query->_tables
['civicrm_activity'] = $query->_whereTables
['civicrm_activity'] = 1;
108 if (!empty($query->_returnProperties
['activity_location'])) {
109 $query->_select
['activity_location'] = "civicrm_activity.location as activity_location";
110 $query->_element
['activity_location'] = 1;
111 $query->_tables
['civicrm_activity'] = $query->_whereTables
['civicrm_activity'] = 1;
114 if (!empty($query->_returnProperties
['activity_details'])) {
115 $query->_select
['activity_details'] = "civicrm_activity.details as activity_details";
116 $query->_element
['activity_details'] = 1;
117 $query->_tables
['civicrm_activity'] = $query->_whereTables
['civicrm_activity'] = 1;
120 if (!empty($query->_returnProperties
['source_record_id'])) {
121 $query->_select
['source_record_id'] = "civicrm_activity.source_record_id as source_record_id";
122 $query->_element
['source_record_id'] = 1;
123 $query->_tables
['civicrm_activity'] = $query->_whereTables
['civicrm_activity'] = 1;
126 if (!empty($query->_returnProperties
['activity_is_test'])) {
127 $query->_select
['activity_is_test'] = "civicrm_activity.is_test as activity_is_test";
128 $query->_element
['activity_is_test'] = 1;
129 $query->_tables
['civicrm_activity'] = $query->_whereTables
['civicrm_activity'] = 1;
132 if (!empty($query->_returnProperties
['activity_campaign_id'])) {
133 $query->_select
['activity_campaign_id'] = 'civicrm_activity.campaign_id as activity_campaign_id';
134 $query->_element
['activity_campaign_id'] = 1;
135 $query->_tables
['civicrm_activity'] = $query->_whereTables
['civicrm_activity'] = 1;
138 if (!empty($query->_returnProperties
['activity_engagement_level'])) {
139 $query->_select
['activity_engagement_level'] = 'civicrm_activity.engagement_level as activity_engagement_level';
140 $query->_element
['activity_engagement_level'] = 1;
141 $query->_tables
['civicrm_activity'] = $query->_whereTables
['civicrm_activity'] = 1;
144 if (!empty($query->_returnProperties
['source_contact'])) {
145 $query->_select
['source_contact'] = 'source_contact.sort_name as source_contact';
146 $query->_element
['source_contact'] = 1;
147 $query->_tables
['source_contact'] = $query->_whereTables
['source_contact'] = 1;
150 if (!empty($query->_returnProperties
['activity_result'])) {
151 $query->_select
['activity_result'] = 'civicrm_activity.result as activity_result';
152 $query->_element
['result'] = 1;
153 $query->_tables
['civicrm_activity'] = $query->_whereTables
['civicrm_activity'] = 1;
156 if (CRM_Utils_Array
::value('parent_id', $query->_returnProperties
)) {
157 $query->_tables
['parent_id'] = 1;
158 $query->_whereTables
['parent_id'] = 1;
159 $query->_element
['parent_id'] = 1;
164 * Given a list of conditions in query generate the required
172 static function where(&$query) {
174 foreach (array_keys($query->_params
) as $id) {
175 if (substr($query->_params
[$id][0], 0, 9) == 'activity_') {
176 if ($query->_mode
== CRM_Contact_BAO_QUERY
::MODE_CONTACTS
) {
177 $query->_useDistinct
= TRUE;
179 $grouping = $query->_params
[$id][3];
180 self
::whereClauseSingle($query->_params
[$id], $query);
186 * where clause for a single field
193 static function whereClauseSingle(&$values, &$query) {
194 list($name, $op, $value, $grouping, $wildcard) = $values;
196 $strtolower = function_exists('mb_strtolower') ?
'mb_strtolower' : 'strtolower';
197 $query->_tables
['civicrm_activity'] = $query->_whereTables
['civicrm_activity'] = 1;
198 if ($query->_mode
& CRM_Contact_BAO_Query
::MODE_ACTIVITY
) {
199 $query->_skipDeleteClause
= TRUE;
203 case 'activity_type_id':
204 case 'activity_type':
205 $types = CRM_Core_PseudoConstant
::activityType(TRUE, TRUE, FALSE, 'label', TRUE);
206 //get the component activity types.
207 $compActTypes = CRM_Core_PseudoConstant
::activityType(TRUE, TRUE, FALSE, 'label', TRUE, TRUE);
208 $activityTypeIds = self
::buildWhereAndQill($query, $value, $types, $op, $grouping, array('Activity Type', 'civicrm_activity.activity_type_id'));
210 foreach ($activityTypeIds as $activityTypeId) {
211 if (array_key_exists($activityTypeId, $compActTypes)) {
212 CRM_Contact_BAO_Query
::$_considerCompActivities = TRUE;
218 case 'activity_survey_id':
222 $value = CRM_Utils_Type
::escape($value, 'Integer');
223 $query->_where
[$grouping][] = " source_record_id = $value";
224 $query->_qill
[$grouping][] = ts('Survey') . ' - ' . CRM_Core_DAO
::getFieldValue('CRM_Campaign_DAO_Survey', $value, 'title');
227 case 'activity_engagement_level':
232 $value = CRM_Utils_Type
::escape($value, 'Integer');
233 $query->_where
[$grouping][] = " engagement_level = $value";
234 $query->_qill
[$grouping][] = ts('Engagement Index') . ' - ' . CRM_Core_OptionGroup
::getLabel('engagement_index', $value);
237 case 'activity_role':
238 CRM_Contact_BAO_Query
::$_activityRole = $values[2];
239 $activityContacts = CRM_Core_OptionGroup
::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
240 $sourceID = CRM_Utils_Array
::key('Activity Source', $activityContacts);
241 $assigneeID = CRM_Utils_Array
::key('Activity Assignees', $activityContacts);
242 $targetID = CRM_Utils_Array
::key('Activity Targets', $activityContacts);
245 $query->_tables
['civicrm_activity_contact'] = $query->_whereTables
['civicrm_activity_contact'] = 1;
246 if ($values[2] == 1) {
247 $query->_where
[$grouping][] = " civicrm_activity_contact.record_type_id = $sourceID";
248 $query->_qill
[$grouping][] = ts('Activity created by');
250 else if ($values[2] == 2) {
251 $query->_where
[$grouping][] = " civicrm_activity_contact.record_type_id = $assigneeID";
252 $query->_qill
[$grouping][] = ts('Activity assigned to');
254 else if ($values[2] == 3) {
255 $query->_where
[$grouping][] = " civicrm_activity_contact.record_type_id = $targetID";
256 $query->_qill
[$grouping][] = ts('Activity targeted to');
261 case 'activity_status':
262 $status = CRM_Core_PseudoConstant
::activityStatus();
264 $activityTypeIds = self
::buildWhereAndQill($query, $value, $status, $op, $grouping, array('Activity Status', 'civicrm_activity.status_id'));
267 case 'activity_subject':
269 $value = strtolower(CRM_Core_DAO
::escapeString($n));
271 if (strpos($value, '%') !== FALSE) {
272 // only add wild card if not there
276 $value = "'%$value%'";
283 $wc = ($op != 'LIKE') ?
"LOWER(civicrm_activity.subject)" : "civicrm_activity.subject";
284 $query->_where
[$grouping][] = " $wc $op $value";
285 $query->_qill
[$grouping][] = ts('Subject') . " $op - '$n'";
288 case 'activity_test':
289 // We dont want to include all tests for sql OR CRM-7827
290 if (!$value ||
$query->getOperator() != 'OR') {
291 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("civicrm_activity.is_test", $op, $value, "Boolean");
293 $query->_qill
[$grouping][] = ts('Activity is a Test');
298 case 'activity_date':
299 case 'activity_date_low':
300 case 'activity_date_high':
301 $query->dateQueryBuilder($values,
302 'civicrm_activity', 'activity_date', 'activity_date_time', ts('Activity Date')
311 if (is_array($value)) {
312 foreach ($value as $k => $v) {
313 $value[$k] = CRM_Utils_Type
::escape($v, 'Integer');
317 $value = array(CRM_Utils_Type
::escape($value, 'Integer'));
319 $query->_where
[$grouping][] = "civicrm_activity.id IN (" . implode(",", $value) . ")";
320 $query->_qill
[$grouping][] = ts('Activity Id(s) %1', array(1 => implode($value)));
323 case 'activity_taglist':
326 foreach ($taglist as $val) {
328 $val = explode(',', $val);
329 foreach ($val as $tId) {
330 if (is_numeric($tId)) {
337 case 'activity_tags':
338 $value = array_keys($value);
339 $activityTags = CRM_Core_PseudoConstant
::get('CRM_Core_DAO_EntityTag', 'tag_id', array('onlyActive' => FALSE));
343 if (is_array($value)) {
344 foreach ($value as $k => $v) {
345 $names[] = $activityTags[$v];
348 $query->_where
[$grouping][] = "civicrm_activity_tag.tag_id IN (" . implode(",", $value) . ")";
349 $query->_qill
[$grouping][] = ts('Activity Tag %1', array(1 => $op)) . ' ' . implode(' ' . ts('OR') . ' ', $names);
350 $query->_tables
['civicrm_activity_tag'] = $query->_whereTables
['civicrm_activity_tag'] = 1;
353 case 'activity_campaign_id':
356 'campaign' => $value,
357 'grouping' => $grouping,
358 'tableName' => 'civicrm_activity',
360 CRM_Campaign_BAO_Query
::componentSearchClause($campParams, $query);
362 case 'activity_result':
363 if(is_array($value)) {
365 while(list(,$k) = each($value)) {
366 $safe[] = "'" . CRM_Utils_Type
::escape($k, 'String') . "'";
368 $query->_where
[$grouping][] = "civicrm_activity.result IN (" . implode(',', $safe) . ")";
369 $query->_qill
[$grouping][] = ts("Activity Result - %1", array(1 => implode(' or ', $safe)));
375 $query->_where
[$grouping][] = "parent_id.parent_id IS NOT NULL";
376 $query->_qill
[$grouping][] = ts('Activities which have Followup Activities');
378 elseif ($value == 2) {
379 $query->_where
[$grouping][] = "parent_id.parent_id IS NULL";
380 $query->_qill
[$grouping][] = ts('Activities without Followup Activities');
384 case 'followup_parent_id':
386 $query->_where
[$grouping][] = "civicrm_activity.parent_id IS NOT NULL";
387 $query->_qill
[$grouping][] = ts('Activities which are Followup Activities');
389 elseif ($value == 2) {
390 $query->_where
[$grouping][] = "civicrm_activity.parent_id IS NULL";
391 $query->_qill
[$grouping][] = ts('Activities which are not Followup Activities');
397 static function from($name, $mode, $side) {
400 case 'civicrm_activity':
401 //CRM-7480 we are going to civicrm_activity table either
402 //from civicrm_activity_target or civicrm_activity_assignment.
403 //as component specific activities does not have entry in
404 //activity target table so lets consider civicrm_activity_assignment.
405 $from .= " INNER JOIN civicrm_activity_contact
406 ON ( civicrm_activity_contact.contact_id = contact_a.id ) ";
407 $from .= " INNER JOIN civicrm_activity
408 ON ( civicrm_activity.id = civicrm_activity_contact.activity_id
409 AND civicrm_activity.is_deleted = 0 AND civicrm_activity.is_current_revision = 1 )";
411 $activityRole = CRM_Contact_BAO_Query
::$_activityRole;
414 case 'activity_status':
415 $from .= " $side JOIN civicrm_option_group option_group_activity_status ON (option_group_activity_status.name = 'activity_status')";
416 $from .= " $side JOIN civicrm_option_value activity_status ON (civicrm_activity.status_id = activity_status.value
417 AND option_group_activity_status.id = activity_status.option_group_id ) ";
420 case 'activity_type':
421 $from .= " $side JOIN civicrm_option_group option_group_activity_type ON (option_group_activity_type.name = 'activity_type')";
422 $from .= " $side JOIN civicrm_option_value activity_type ON (civicrm_activity.activity_type_id = activity_type.value
423 AND option_group_activity_type.id = activity_type.option_group_id ) ";
426 case 'civicrm_activity_tag':
427 $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 ) ";
430 case 'source_contact':
431 $activityContacts = CRM_Core_OptionGroup
::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
432 $sourceID = CRM_Utils_Array
::key('Activity Source', $activityContacts);
434 LEFT JOIN civicrm_activity_contact ac
435 ON ( ac.activity_id = civicrm_activity_contact.activity_id AND ac.record_type_id = {$sourceID})
436 INNER JOIN civicrm_contact source_contact ON (ac.contact_id = source_contact.id)";
440 $from = "$side JOIN civicrm_activity AS parent_id ON civicrm_activity.id = parent_id.parent_id";
448 * getter for the qill object
454 return (isset($this->_qill
)) ?
$this->_qill
: "";
458 * add all the elements shared between case activity search and advanaced search
462 * @param $form CRM_Core_Form
466 static function buildSearchForm(&$form) {
467 $activityOptions = CRM_Core_PseudoConstant
::activityType(TRUE, TRUE, FALSE, 'label', TRUE);
468 asort($activityOptions);
469 foreach ($activityOptions as $activityID => $activity) {
470 $form->_activityElement
=&
473 "activity_type_id[$activityID]",
476 array('onClick' => 'showCustomData( this.id );')
480 CRM_Core_Form_Date
::buildDateRange($form, 'activity_date', 1, '_low', '_high', ts('From'), FALSE, FALSE);
481 $followUpActivity = array(
485 $form->addRadio('parent_id', NULL, $followUpActivity, array('allowClear' => TRUE));
486 $form->addRadio('followup_parent_id', NULL, $followUpActivity, array('allowClear' => TRUE));
487 $activityRoles = array(
489 2 => ts('Assigned to'),
492 $form->addRadio('activity_role', NULL, $activityRoles, array('allowClear' => TRUE));
493 $form->setDefaults(array('activity_role' => 3));
494 $activityStatus = CRM_Core_PseudoConstant
::activityStatus();
495 foreach ($activityStatus as $activityStatusID => $activityStatusName) {
496 $activity_status[] = $form->createElement('checkbox', $activityStatusID, NULL, $activityStatusName);
498 $form->addGroup($activity_status, 'activity_status', ts('Activity Status'));
499 $form->setDefaults(array('activity_status[1]' => 1, 'activity_status[2]' => 1));
500 $form->addElement('text', 'activity_subject', ts('Subject'), CRM_Core_DAO
::getAttribute('CRM_Contact_DAO_Contact', 'sort_name'));
501 $form->addYesNo('activity_test', ts('Activity is a Test?'));
502 $activity_tags = CRM_Core_BAO_Tag
::getTags('civicrm_activity');
503 if ($activity_tags) {
504 foreach ($activity_tags as $tagID => $tagName) {
505 $form->_tagElement
= &$form->addElement('checkbox', "activity_tags[$tagID]",
511 $parentNames = CRM_Core_BAO_Tag
::getTagSet('civicrm_activity');
512 CRM_Core_Form_Tag
::buildQuickForm($form, $parentNames, 'civicrm_activity', NULL, TRUE, TRUE);
514 $surveys = CRM_Campaign_BAO_Survey
::getSurveys(TRUE, FALSE, FALSE, TRUE);
515 if ($surveys) $form->add('select', 'activity_survey_id', ts('Survey / Petition'),
516 array('' => ts('- none -')) +
$surveys, FALSE,
517 array('class' => 'crm-select2')
520 $extends = array('Activity');
521 $groupDetails = CRM_Core_BAO_CustomGroup
::getGroupDetail(NULL, TRUE, $extends);
523 $form->assign('activityGroupTree', $groupDetails);
524 foreach ($groupDetails as $group) {
525 foreach ($group['fields'] as $field) {
526 $fieldId = $field['id'];
527 $elementName = 'custom_' . $fieldId;
528 CRM_Core_BAO_CustomField
::addQuickFormElement($form, $elementName, $fieldId, FALSE, FALSE, TRUE);
533 CRM_Campaign_BAO_Campaign
::addCampaignInComponentSearch($form, 'activity_campaign_id');
535 //add engagement level CRM-7775
536 $buildEngagementLevel = FALSE;
537 $buildSurveyResult = FALSE;
538 if (CRM_Campaign_BAO_Campaign
::isCampaignEnable() &&
539 CRM_Campaign_BAO_Campaign
::accessCampaign()
541 $buildEngagementLevel = TRUE;
542 $form->addSelect('activity_engagement_level', array('entity' => 'activity', 'option_url' => NULL));
544 // Add survey result field.
545 $optionGroups = CRM_Campaign_BAO_Survey
::getResultSets( 'name' );
546 $resultOptions = array();
547 foreach ( $optionGroups as $gid => $name ) {
550 $value = CRM_Core_OptionGroup
::values($name);
551 if (!empty($value)) {
552 while(list($k,$v) = each($value)) {
553 $resultOptions[$v] = $v;
558 // If no survey result options have been created, don't build
559 // the field to avoid clutter.
560 if(count($resultOptions) > 0) {
561 $buildSurveyResult = TRUE;
562 asort($resultOptions);
563 $form->add('select', 'activity_result', ts("Survey Result"),
564 $resultOptions, FALSE,
565 array('id' => 'activity_result', 'multiple' => 'multiple', 'class' => 'crm-select2')
570 $form->assign('buildEngagementLevel', $buildEngagementLevel);
571 $form->assign('buildSurveyResult', $buildSurveyResult);
572 $form->setDefaults(array('activity_test' => 0));
575 static function defaultReturnProperties($mode, $includeCustomFields = TRUE) {
577 if ($mode & CRM_Contact_BAO_Query
::MODE_ACTIVITY
) {
581 'contact_sub_type' => 1,
584 'activity_type' => 1,
585 'activity_type_id' => 1,
586 'activity_subject' => 1,
587 'activity_date_time' => 1,
588 'activity_duration' => 1,
589 'activity_location' => 1,
590 'activity_details' => 1,
591 'activity_status' => 1,
592 'source_contact' => 1,
593 'source_record_id' => 1,
594 'activity_is_test' => 1,
595 'activity_campaign_id' => 1,
597 'activity_engagement_level' => 1,
601 if ($includeCustomFields) {
602 // also get all the custom activity properties
603 $fields = CRM_Core_BAO_CustomField
::getFieldsForImport('Activity');
604 if (!empty($fields)) {
605 foreach ($fields as $name => $dontCare) {
606 $properties[$name] = 1;
615 static function buildWhereAndQill(&$query, $value, $pseudoconstantType, $op, $grouping, $params) {
616 $matches = $val = $clause = array();
618 if (is_array($value)) {
619 foreach ($value as $k => $v) {
625 if (count($val) > 0) {
626 // Overwrite $op so it works with an IN where statement.
630 // If we somehow have an empty array, just return
633 $value = $matches[0] = $val;
636 preg_match_all('/\d+/', $value, $matches);
638 foreach ($matches[0] as $qill) {
639 $clause[] = CRM_Utils_Array
::value($qill, $pseudoconstantType);
642 $query->_qill
[$grouping][] = ts($params[0] . ' %1 ', array(1 => $op)) . implode(' ' . ts('or') . ' ', $clause);
643 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause($params[1],
644 $op, $value, "Integer"