3 +--------------------------------------------------------------------+
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2020 |
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-2020
33 class CRM_Report_Form_Activity
extends CRM_Report_Form
{
34 protected $_selectAliasesTotal = [];
36 protected $_customGroupExtends = [
40 protected $_nonDisplayFields = [];
43 * This report has not been optimised for group filtering.
45 * The functionality for group filtering has been improved but not
46 * all reports have been adjusted to take care of it. This report has not
47 * and will run an inefficient query until fixed.
53 protected $groupFilterNotOptimised = TRUE;
58 public function __construct() {
59 // There could be multiple contacts. We not clear on which contact id to display.
60 // Lets hide it for now.
61 $this->_exposeContactID
= FALSE;
62 // if navigated from count link of activity summary reports.
63 $this->_resetDateFilter
= CRM_Utils_Request
::retrieve('resetDateFilter', 'Boolean');
65 $config = CRM_Core_Config
::singleton();
66 $campaignEnabled = in_array("CiviCampaign", $config->enableComponents
);
67 $caseEnabled = in_array("CiviCase", $config->enableComponents
);
68 if ($campaignEnabled) {
69 $this->engagementLevels
= CRM_Campaign_PseudoConstant
::engagementLevel();
72 $components = CRM_Core_Component
::getEnabledComponents();
73 foreach ($components as $componentName => $componentInfo) {
74 // CRM-19201: Add support for reporting CiviCampaign activities
75 // For CiviCase, "access all cases and activities" is required here
76 // rather than "access my cases and activities" to prevent those with
77 // only the later permission from seeing a list of all cases which might
78 // present a privacy issue.
79 if (CRM_Core_Permission
::access($componentName, TRUE, TRUE)) {
80 $accessAllowed[] = $componentInfo->componentID
;
85 if (!empty($accessAllowed)) {
86 $include = 'OR v.component_id IN (' . implode(', ', $accessAllowed) . ')';
88 $condition = " AND ( v.component_id IS NULL {$include} )";
89 $this->activityTypes
= CRM_Core_OptionGroup
::values('activity_type', FALSE, FALSE, FALSE, $condition);
90 asort($this->activityTypes
);
92 // @todo split the 3 different contact tables into their own array items.
93 // this will massively simplify the needs of this report.
95 'civicrm_contact' => [
96 'dao' => 'CRM_Contact_DAO_Contact',
99 'name' => 'sort_name',
100 'title' => ts('Source Name'),
101 'alias' => 'civicrm_contact_source',
104 'contact_assignee' => [
105 'name' => 'sort_name',
106 'title' => ts('Assignee Name'),
107 'alias' => 'civicrm_contact_assignee',
108 'dbAlias' => "civicrm_contact_assignee.sort_name",
111 'contact_target' => [
112 'name' => 'sort_name',
113 'title' => ts('Target Name'),
114 'alias' => 'civicrm_contact_target',
115 'dbAlias' => "civicrm_contact_target.sort_name",
118 'contact_source_id' => [
120 'alias' => 'civicrm_contact_source',
121 'dbAlias' => "civicrm_contact_source.id",
122 'no_display' => TRUE,
126 'contact_assignee_id' => [
128 'alias' => 'civicrm_contact_assignee',
129 'dbAlias' => "civicrm_contact_assignee.id",
130 'no_display' => TRUE,
134 'contact_target_id' => [
136 'alias' => 'civicrm_contact_target',
137 'dbAlias' => "civicrm_contact_target.id",
138 'no_display' => TRUE,
144 'contact_source' => [
145 'name' => 'sort_name',
146 'alias' => 'civicrm_contact_source',
147 'title' => ts('Source Name'),
148 'operator' => 'like',
149 'type' => CRM_Report_Form
::OP_STRING
,
151 'contact_assignee' => [
152 'name' => 'sort_name',
153 'alias' => 'civicrm_contact_assignee',
154 'title' => ts('Assignee Name'),
155 'operator' => 'like',
156 'type' => CRM_Report_Form
::OP_STRING
,
158 'contact_target' => [
159 'name' => 'sort_name',
160 'alias' => 'civicrm_contact_target',
161 'title' => ts('Target Name'),
162 'operator' => 'like',
163 'type' => CRM_Report_Form
::OP_STRING
,
166 'name' => 'current_user',
167 'title' => ts('Limit To Current User'),
168 'type' => CRM_Utils_Type
::T_INT
,
169 'operatorType' => CRM_Report_Form
::OP_SELECT
,
170 'options' => ['0' => ts('No'), '1' => ts('Yes')],
173 'grouping' => 'contact-fields',
176 'dao' => 'CRM_Core_DAO_Email',
178 'contact_source_email' => [
180 'title' => ts('Source Email'),
181 'alias' => 'civicrm_email_source',
183 'contact_assignee_email' => [
185 'title' => ts('Assignee Email'),
186 'alias' => 'civicrm_email_assignee',
188 'contact_target_email' => [
190 'title' => ts('Target Email'),
191 'alias' => 'civicrm_email_target',
195 'source_contact_email' => [
197 'title' => ts('Source Email'),
198 'dbAlias' => 'civicrm_email_contact_source_email',
203 'dao' => 'CRM_Core_DAO_Phone',
205 'contact_source_phone' => [
207 'title' => ts('Source Phone'),
208 'alias' => 'civicrm_phone_source',
210 'contact_assignee_phone' => [
212 'title' => ts('Assignee Phone'),
213 'alias' => 'civicrm_phone_assignee',
215 'contact_target_phone' => [
217 'title' => ts('Target Phone'),
218 'alias' => 'civicrm_phone_target',
222 'civicrm_activity' => [
223 'dao' => 'CRM_Activity_DAO_Activity',
226 'no_display' => TRUE,
227 'title' => ts('Activity ID'),
230 'source_record_id' => [
231 'no_display' => TRUE,
234 'activity_type_id' => [
235 'title' => ts('Activity Type'),
237 'type' => CRM_Utils_Type
::T_STRING
,
239 'activity_subject' => [
240 'title' => ts('Subject'),
243 'activity_date_time' => [
244 'title' => ts('Activity Date'),
248 'title' => ts('Activity Status'),
250 'type' => CRM_Utils_Type
::T_STRING
,
253 'title' => ts('Duration'),
254 'type' => CRM_Utils_Type
::T_INT
,
257 'title' => ts('Location'),
258 'type' => CRM_Utils_Type
::T_STRING
,
261 'title' => ts('Activity Details'),
264 'title' => ts('Priority'),
266 'type' => CRM_Utils_Type
::T_STRING
,
270 'activity_date_time' => [
271 'default' => 'this.month',
272 'operatorType' => CRM_Report_Form
::OP_DATE
,
274 'activity_subject' => ['title' => ts('Activity Subject')],
275 'activity_type_id' => [
276 'title' => ts('Activity Type'),
277 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
278 'options' => $this->activityTypes
,
281 'title' => ts('Activity Status'),
282 'type' => CRM_Utils_Type
::T_STRING
,
283 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
284 'options' => CRM_Core_PseudoConstant
::activityStatus(),
287 'title' => ts('Location'),
288 'type' => CRM_Utils_Type
::T_TEXT
,
291 'title' => ts('Activity Details'),
292 'type' => CRM_Utils_Type
::T_TEXT
,
295 'title' => ts('Activity Priority'),
296 'type' => CRM_Utils_Type
::T_STRING
,
297 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
298 'options' => CRM_Core_PseudoConstant
::get('CRM_Activity_DAO_Activity', 'priority_id'),
302 'activity_date_time' => [
303 'title' => ts('Activity Date'),
304 'default_weight' => '1',
305 'dbAlias' => 'civicrm_activity_activity_date_time',
307 'activity_type_id' => [
308 'title' => ts('Activity Type'),
309 'default_weight' => '2',
310 'dbAlias' => 'field(civicrm_activity_activity_type_id, ' . implode(', ', array_keys($this->activityTypes
)) . ')',
313 'grouping' => 'activity-fields',
314 'alias' => 'activity',
316 // Hack to get $this->_alias populated for the table.
317 'civicrm_activity_contact' => [
318 'dao' => 'CRM_Activity_DAO_ActivityContact',
321 ] +
$this->addressFields(TRUE);
323 if ($caseEnabled && CRM_Core_Permission
::check('access all cases and activities')) {
324 $this->_columns
['civicrm_activity']['filters']['include_case_activities'] = [
325 'name' => 'include_case_activities',
326 'title' => ts('Include Case Activities'),
327 'type' => CRM_Utils_Type
::T_INT
,
328 'operatorType' => CRM_Report_Form
::OP_SELECT
,
329 'options' => ['0' => ts('No'), '1' => ts('Yes')],
333 if ($campaignEnabled) {
334 // Add display column and filter for Survey Results, Campaign and Engagement Index if CiviCampaign is enabled
336 $this->_columns
['civicrm_activity']['fields']['result'] = [
337 'title' => ts('Survey Result'),
338 'default' => 'false',
340 $this->_columns
['civicrm_activity']['filters']['result'] = [
341 'title' => ts('Survey Result'),
342 'operator' => 'like',
343 'type' => CRM_Utils_Type
::T_STRING
,
345 // If we have campaigns enabled, add those elements to both the fields, filters.
346 $this->addCampaignFields('civicrm_activity');
348 if (!empty($this->engagementLevels
)) {
349 $this->_columns
['civicrm_activity']['fields']['engagement_level'] = [
350 'title' => ts('Engagement Index'),
351 'default' => 'false',
353 $this->_columns
['civicrm_activity']['filters']['engagement_level'] = [
354 'title' => ts('Engagement Index'),
355 'type' => CRM_Utils_Type
::T_INT
,
356 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
357 'options' => $this->engagementLevels
,
361 $this->_groupFilter
= TRUE;
362 $this->_tagFilter
= TRUE;
363 $this->_tagFilterTable
= 'civicrm_activity';
364 parent
::__construct();
368 * Adding address fields with dbAlias for order clause.
370 * @param bool $orderBy
375 public function addressFields($orderBy = FALSE) {
376 $address = parent
::addAddressFields(FALSE, TRUE);
378 foreach ($address['civicrm_address']['order_bys'] as $fieldName => $field) {
379 $address['civicrm_address']['order_bys'][$fieldName]['dbAlias'] = "civicrm_address_{$fieldName}";
386 * Build select clause.
388 * @todo get rid of $recordType param. It's only because 3 separate contact tables
389 * are mis-declared as one that we need it.
391 * @param null $recordType deprecated
392 * Parameter to hack around the bad decision made in construct to misrepresent
393 * different tables as the same table.
395 public function select($recordType = 'target') {
396 if (!array_key_exists("contact_{$recordType}", $this->_params
['fields']) &&
397 $recordType != 'final'
399 $this->_nonDisplayFields
[] = "civicrm_contact_contact_{$recordType}";
403 if ($recordType == 'final' && !empty($this->_nonDisplayFields
)) {
404 foreach ($this->_nonDisplayFields
as $fieldName) {
405 unset($this->_columnHeaders
[$fieldName]);
409 if (empty($this->_selectAliasesTotal
)) {
410 $this->_selectAliasesTotal
= $this->_selectAliases
;
414 if ($recordType == 'target') {
415 // @todo - fix up the way the tables are declared in construct & remove this.
416 foreach ($this->_selectClauses
as $key => $clause) {
417 if (strstr($clause, 'civicrm_contact_assignee.') ||
418 strstr($clause, 'civicrm_contact_source.') ||
419 strstr($clause, 'civicrm_email_assignee.') ||
420 strstr($clause, 'civicrm_email_source.') ||
421 strstr($clause, 'civicrm_phone_assignee.') ||
422 strstr($clause, 'civicrm_phone_source.')
424 $removeKeys[] = $key;
425 unset($this->_selectClauses
[$key]);
429 elseif ($recordType == 'assignee') {
430 // @todo - fix up the way the tables are declared in construct & remove this.
431 foreach ($this->_selectClauses
as $key => $clause) {
432 if (strstr($clause, 'civicrm_contact_target.') ||
433 strstr($clause, 'civicrm_contact_source.') ||
434 strstr($clause, 'civicrm_email_target.') ||
435 strstr($clause, 'civicrm_email_source.') ||
436 strstr($clause, 'civicrm_phone_target.') ||
437 strstr($clause, 'civicrm_phone_source.') ||
438 strstr($clause, 'civicrm_address_')
440 $removeKeys[] = $key;
441 unset($this->_selectClauses
[$key]);
445 elseif ($recordType == 'source') {
446 // @todo - fix up the way the tables are declared in construct & remove this.
447 foreach ($this->_selectClauses
as $key => $clause) {
448 if (strstr($clause, 'civicrm_contact_target.') ||
449 strstr($clause, 'civicrm_contact_assignee.') ||
450 strstr($clause, 'civicrm_email_target.') ||
451 strstr($clause, 'civicrm_email_assignee.') ||
452 strstr($clause, 'civicrm_phone_target.') ||
453 strstr($clause, 'civicrm_phone_assignee.') ||
454 strstr($clause, 'civicrm_address_')
456 $removeKeys[] = $key;
457 unset($this->_selectClauses
[$key]);
461 elseif ($recordType == 'final') {
462 $this->_selectClauses
= $this->_selectAliasesTotal
;
463 foreach ($this->_selectClauses
as $key => $clause) {
464 // @todo - fix up the way the tables are declared in construct & remove this.
465 if (strstr($clause, 'civicrm_contact_contact_target') ||
466 strstr($clause, 'civicrm_contact_contact_assignee') ||
467 strstr($clause, 'civicrm_contact_contact_source') ||
468 strstr($clause, 'civicrm_phone_contact_source_phone') ||
469 strstr($clause, 'civicrm_phone_contact_assignee_phone') ||
470 strstr($clause, 'civicrm_email_contact_source_email') ||
471 strstr($clause, 'civicrm_email_contact_assignee_email') ||
472 strstr($clause, 'civicrm_email_contact_target_email') ||
473 strstr($clause, 'civicrm_phone_contact_target_phone') ||
474 strstr($clause, 'civicrm_address_')
476 $this->_selectClauses
[$key] = "GROUP_CONCAT(DISTINCT $clause SEPARATOR ';') as $clause";
482 foreach ($removeKeys as $key) {
483 unset($this->_selectAliases
[$key]);
486 if ($recordType == 'target') {
487 foreach ($this->_columns
['civicrm_address']['order_bys'] as $fieldName => $field) {
488 $orderByFld = $this->_columns
['civicrm_address']['order_bys'][$fieldName];
489 $fldInfo = $this->_columns
['civicrm_address']['fields'][$fieldName];
490 $this->_selectAliases
[] = $orderByFld['dbAlias'];
491 $this->_selectClauses
[] = "{$fldInfo['dbAlias']} as {$orderByFld['dbAlias']}";
493 $this->_selectAliases
= array_unique($this->_selectAliases
);
494 $this->_selectClauses
= array_unique($this->_selectClauses
);
496 $this->_select
= "SELECT " . implode(', ', $this->_selectClauses
) . " ";
502 * @todo remove this function & declare the 3 contact tables separately
504 public function from() {
505 $activityContacts = CRM_Activity_BAO_ActivityContact
::buildOptions('record_type_id', 'validate');
506 $targetID = CRM_Utils_Array
::key('Activity Targets', $activityContacts);
509 FROM civicrm_activity {$this->_aliases['civicrm_activity']}
510 INNER JOIN civicrm_activity_contact {$this->_aliases['civicrm_activity_contact']}
511 ON {$this->_aliases['civicrm_activity']}.id = {$this->_aliases['civicrm_activity_contact']}.activity_id AND
512 {$this->_aliases['civicrm_activity_contact']}.record_type_id = {$targetID}
513 INNER JOIN civicrm_contact civicrm_contact_target
514 ON {$this->_aliases['civicrm_activity_contact']}.contact_id = civicrm_contact_target.id
517 if ($this->isTableSelected('civicrm_email')) {
519 LEFT JOIN civicrm_email civicrm_email_target
520 ON {$this->_aliases['civicrm_activity_contact']}.contact_id = civicrm_email_target.contact_id AND
521 civicrm_email_target.is_primary = 1";
524 if ($this->isTableSelected('civicrm_phone')) {
526 LEFT JOIN civicrm_phone civicrm_phone_target
527 ON {$this->_aliases['civicrm_activity_contact']}.contact_id = civicrm_phone_target.contact_id AND
528 civicrm_phone_target.is_primary = 1 ";
530 $this->_aliases
['civicrm_contact'] = 'civicrm_contact_target';
532 $this->joinAddressFromContact();
536 * Build where clause.
538 * @todo get rid of $recordType param. It's only because 3 separate contact tables
539 * are mis-declared as one that we need it.
541 * @param string $recordType
543 public function where($recordType = NULL) {
544 $this->_where
= " WHERE {$this->_aliases['civicrm_activity']}.is_test = 0 AND
545 {$this->_aliases['civicrm_activity']}.is_deleted = 0 AND
546 {$this->_aliases['civicrm_activity']}.is_current_revision = 1";
549 foreach ($this->_columns
as $tableName => $table) {
550 if (array_key_exists('filters', $table)) {
552 foreach ($table['filters'] as $fieldName => $field) {
554 if ($fieldName != 'contact_' . $recordType &&
555 (strstr($fieldName, '_target') ||
556 strstr($fieldName, '_assignee') ||
557 strstr($fieldName, '_source')
562 if (CRM_Utils_Array
::value('type', $field) & CRM_Utils_Type
::T_DATE
) {
563 $relative = CRM_Utils_Array
::value("{$fieldName}_relative", $this->_params
);
564 $from = CRM_Utils_Array
::value("{$fieldName}_from", $this->_params
);
565 $to = CRM_Utils_Array
::value("{$fieldName}_to", $this->_params
);
567 $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']);
570 $op = CRM_Utils_Array
::value("{$fieldName}_op", $this->_params
);
571 if ($op && ($op != 'nnll' && $op != 'nll')) {
572 $clause = $this->whereClause($field,
574 CRM_Utils_Array
::value("{$fieldName}_value", $this->_params
),
575 CRM_Utils_Array
::value("{$fieldName}_min", $this->_params
),
576 CRM_Utils_Array
::value("{$fieldName}_max", $this->_params
)
578 if ($field['name'] == 'include_case_activities') {
581 if ($fieldName == 'activity_type_id' &&
582 empty($this->_params
['activity_type_id_value'])
584 if (empty($this->_params
['include_case_activities_value'])) {
585 $this->activityTypes
= CRM_Core_PseudoConstant
::activityType(TRUE, FALSE, FALSE, 'label', TRUE);
587 $actTypes = array_flip($this->activityTypes
);
588 $clause = "( {$this->_aliases['civicrm_activity']}.activity_type_id IN (" .
589 implode(',', $actTypes) . ") )";
594 if ($field['name'] == 'current_user') {
595 if (CRM_Utils_Array
::value("{$fieldName}_value", $this->_params
) ==
599 $session = CRM_Core_Session
::singleton();
600 if ($contactID = $session->get('userID')) {
601 $clause = "{$this->_aliases['civicrm_activity_contact']}.activity_id IN
602 (SELECT activity_id FROM civicrm_activity_contact WHERE contact_id = {$contactID})";
612 if (!empty($clause)) {
613 $clauses[] = $clause;
619 if (empty($clauses)) {
620 $this->_where
.= " ";
623 $this->_where
.= " AND " . implode(' AND ', $clauses);
626 if ($this->_aclWhere
) {
627 $this->_where
.= " AND {$this->_aclWhere} ";
632 * Override group by function.
634 public function groupBy() {
635 $this->_groupBy
= CRM_Contact_BAO_Query
::getGroupByFromSelectColumns($this->_selectClauses
, "{$this->_aliases['civicrm_activity']}.id");
641 * @param string $tableAlias
643 public function buildACLClause($tableAlias = 'contact_a') {
644 //override for ACL( Since Contact may be source
645 //contact/assignee or target also it may be null )
647 if (CRM_Core_Permission
::check('view all contacts')) {
648 $this->_aclFrom
= $this->_aclWhere
= NULL;
652 $session = CRM_Core_Session
::singleton();
653 $contactID = $session->get('userID');
657 $contactID = CRM_Utils_Type
::escape($contactID, 'Integer');
659 CRM_Contact_BAO_Contact_Permission
::cache($contactID);
661 foreach ($tableAlias as $k => $alias) {
662 $clauses[] = " INNER JOIN civicrm_acl_contact_cache aclContactCache_{$k} ON ( {$alias}.id = aclContactCache_{$k}.contact_id OR {$alias}.id IS NULL ) AND aclContactCache_{$k}.user_id = $contactID ";
665 $this->_aclFrom
= implode(" ", $clauses);
666 $this->_aclWhere
= NULL;
670 * @param int $groupID
674 public function add2group($groupID) {
675 if (CRM_Utils_Array
::value("contact_target_op", $this->_params
) == 'nll') {
676 CRM_Core_Error
::fatal(ts('Current filter criteria didn\'t have any target contact to add to group'));
679 $new_select = 'AS addtogroup_contact_id';
680 $select = str_ireplace('AS civicrm_contact_contact_target_id', $new_select, $this->_select
);
681 $new_having = ' addtogroup_contact_id';
682 $having = str_ireplace(' civicrm_contact_contact_target_id', $new_having, $this->_having
);
684 FROM {$this->temporaryTables['activity_temp_table']['name']} tar
685 GROUP BY civicrm_activity_id $having {$this->_orderBy}";
686 $select = 'AS addtogroup_contact_id';
687 $query = str_ireplace('AS civicrm_contact_contact_target_id', $select, $query);
688 CRM_Core_DAO
::disableFullGroupByMode();
689 $dao = $this->executeReportQuery($query);
690 CRM_Core_DAO
::reenableFullGroupByMode();
693 // Add resulting contacts to group
694 while ($dao->fetch()) {
695 if ($dao->addtogroup_contact_id
) {
696 $contact_id = explode(';', $dao->addtogroup_contact_id
);
697 if ($contact_id[0]) {
698 $contactIDs[$contact_id[0]] = $contact_id[0];
703 if (!empty($contactIDs)) {
704 CRM_Contact_BAO_GroupContact
::addContactsToGroup($contactIDs, $groupID);
705 CRM_Core_Session
::setStatus(ts("Listed contact(s) have been added to the selected group."), ts('Contacts Added'), 'success');
708 CRM_Core_Session
::setStatus(ts("The listed records(s) cannot be added to the group."));
719 public static function formRule($fields, $files, $self) {
721 $config = CRM_Core_Config
::singleton();
722 if (in_array("CiviCase", $config->enableComponents
)) {
723 $componentId = CRM_Core_Component
::getComponentID('CiviCase');
724 $caseActivityTypes = CRM_Core_OptionGroup
::values('activity_type', TRUE, FALSE, FALSE, " AND v.component_id={$componentId}");
725 if (!empty($fields['activity_type_id_value']) && is_array($fields['activity_type_id_value']) && empty($fields['include_case_activities_value'])) {
726 foreach ($fields['activity_type_id_value'] as $activityTypeId) {
727 if (in_array($activityTypeId, $caseActivityTypes)) {
728 $errors['fields'] = ts("Please enable 'Include Case Activities' to filter with Case Activity types.");
741 public function buildQuery($applyLimit = TRUE) {
742 $activityContacts = CRM_Activity_BAO_ActivityContact
::buildOptions('record_type_id', 'validate');
743 $sourceID = CRM_Utils_Array
::key('Activity Source', $activityContacts);
745 //Assign those recordtype to array which have filter operator as 'Is not empty' or 'Is empty'
747 foreach (['target', 'source', 'assignee'] as $type) {
748 if (CRM_Utils_Array
::value("contact_{$type}_op", $this->_params
) ==
749 'nnll' ||
!empty($this->_params
["contact_{$type}_value"])
751 $nullFilters[] = " civicrm_contact_contact_{$type}_id IS NOT NULL ";
753 elseif (CRM_Utils_Array
::value("contact_{$type}_op", $this->_params
) ==
756 $nullFilters[] = " civicrm_contact_contact_{$type}_id IS NULL ";
760 // @todo - all this temp table stuff is here because pre 4.4 the activity contact
761 // form did not exist.
762 // Fixing the way the construct method declares them will make all this redundant.
763 // 1. fill temp table with target results
764 $this->buildACLClause(['civicrm_contact_target']);
765 $this->select('target');
767 $this->customDataFrom();
768 $this->where('target');
769 $tempTableName = $this->createTemporaryTable('activity_temp_table', "{$this->_select} {$this->_from} {$this->_where}");
771 // 2. add new columns to hold assignee and source results
772 // fixme: add when required
774 ALTER TABLE $tempTableName
775 MODIFY COLUMN civicrm_contact_contact_target_id VARCHAR(128),
776 ADD COLUMN civicrm_contact_contact_assignee VARCHAR(128),
777 ADD COLUMN civicrm_contact_contact_source VARCHAR(128),
778 ADD COLUMN civicrm_contact_contact_assignee_id VARCHAR(128),
779 ADD COLUMN civicrm_contact_contact_source_id VARCHAR(128),
780 ADD COLUMN civicrm_phone_contact_assignee_phone VARCHAR(128),
781 ADD COLUMN civicrm_phone_contact_source_phone VARCHAR(128),
782 ADD COLUMN civicrm_email_contact_assignee_email VARCHAR(128),
783 ADD COLUMN civicrm_email_contact_source_email VARCHAR(128)";
784 $this->executeReportQuery($tempQuery);
786 // 3. fill temp table with assignee results
787 $this->buildACLClause(['civicrm_contact_assignee']);
788 $this->select('assignee');
789 $this->buildAssigneeFrom();
791 $this->customDataFrom();
792 $this->where('assignee');
793 $insertCols = implode(',', $this->_selectAliases
);
794 $tempQuery = "INSERT INTO $tempTableName ({$insertCols})
796 {$this->_from} {$this->_where}";
797 $this->executeReportQuery($tempQuery);
799 // 4. fill temp table with source results
800 $this->buildACLClause(['civicrm_contact_source']);
801 $this->select('source');
802 $this->buildSourceFrom();
803 $this->customDataFrom();
804 $this->where('source');
805 $insertCols = implode(',', $this->_selectAliases
);
806 $tempQuery = "INSERT INTO $tempTableName ({$insertCols})
808 {$this->_from} {$this->_where}";
809 $this->executeReportQuery($tempQuery);
811 // 5. show final result set from temp table
813 $this->select('final');
815 if (!empty($nullFilters)) {
816 $this->_having
= "HAVING " . implode(' AND ', $nullFilters);
819 foreach ($this->_sections
as $alias => $section) {
820 if (!empty($section) && $section['name'] == 'activity_date_time') {
821 $this->alterSectionHeaderForDateTime($tempTableName, $section['tplField']);
829 $groupByFromSelect = CRM_Contact_BAO_Query
::getGroupByFromSelectColumns($this->_selectClauses
, 'civicrm_activity_id');
831 $this->_where
= " WHERE (1)";
832 $this->buildPermissionClause();
833 if ($this->_aclWhere
) {
834 $this->_where
.= " AND {$this->_aclWhere} ";
837 $sql = "{$this->_select}
838 FROM $tempTableName tar
839 INNER JOIN civicrm_activity {$this->_aliases['civicrm_activity']} ON {$this->_aliases['civicrm_activity']}.id = tar.civicrm_activity_id
840 INNER JOIN civicrm_activity_contact {$this->_aliases['civicrm_activity_contact']} ON {$this->_aliases['civicrm_activity_contact']}.activity_id = {$this->_aliases['civicrm_activity']}.id
841 AND {$this->_aliases['civicrm_activity_contact']}.record_type_id = {$sourceID}
842 LEFT JOIN civicrm_contact contact_civireport ON contact_civireport.id = {$this->_aliases['civicrm_activity_contact']}.contact_id
843 {$this->_where} {$groupByFromSelect} {$this->_having} {$this->_orderBy} {$this->_limit}";
845 CRM_Utils_Hook
::alterReportVar('sql', $this, $this);
846 $this->addToDeveloperTab($sql);
851 public function postProcess() {
852 //reset value of activity_date
853 if (!empty($this->_resetDateFilter
)) {
854 $this->_formValues
["activity_date_time_relative"] = NULL;
857 $this->beginPostProcess();
858 $sql = $this->buildQuery(TRUE);
859 $this->buildRows($sql, $rows);
861 // format result set.
862 $this->formatDisplay($rows);
864 // assign variables to templates
865 $this->doTemplateAssignment($rows);
867 // do print / pdf / instance stuff if needed
868 $this->endPostProcess($rows);
872 * Alter display of rows.
874 * Iterate through the rows retrieved via SQL and make changes for display purposes,
875 * such as rendering contacts as links.
878 * Rows generated by SQL, with an array for each row.
880 public function alterDisplay(&$rows) {
882 $activityType = CRM_Core_PseudoConstant
::activityType(TRUE, TRUE, FALSE, 'label', TRUE);
883 $activityStatus = CRM_Core_PseudoConstant
::activityStatus();
884 $priority = CRM_Core_PseudoConstant
::get('CRM_Activity_DAO_Activity', 'priority_id');
887 // Would we ever want to retrieve from the form controller??
888 $form = $this->noController ?
NULL : $this;
889 $context = CRM_Utils_Request
::retrieve('context', 'Alphanumeric', $form, FALSE, 'report');
892 if (CRM_Core_Permission
::check('access CiviCRM')) {
894 $onHover = ts('View Contact Summary for this Contact');
895 $onHoverAct = ts('View Activity Record');
897 foreach ($rows as $rowNum => $row) {
898 // if we have an activity type, format the View Activity link for use in various columns
900 array_key_exists('civicrm_activity_activity_type_id', $row)
902 // Check for target contact id(s) and use the first contact id in that list for view activity link if found,
903 // else use source contact id
904 if (!empty($rows[$rowNum]['civicrm_contact_contact_target_id'])) {
905 $targets = explode(';', $rows[$rowNum]['civicrm_contact_contact_target_id']);
909 $cid = $rows[$rowNum]['civicrm_contact_contact_source_id'];
912 $actActionLinks = CRM_Activity_Selector_Activity
::actionLinks($row['civicrm_activity_activity_type_id'],
913 CRM_Utils_Array
::value('civicrm_activity_source_record_id', $rows[$rowNum]),
915 $rows[$rowNum]['civicrm_activity_id']
919 'id' => $rows[$rowNum]['civicrm_activity_id'],
923 $actUrl = CRM_Utils_System
::url($actActionLinks[CRM_Core_Action
::VIEW
]['url'],
924 CRM_Core_Action
::replace($actActionLinks[CRM_Core_Action
::VIEW
]['qs'], $actLinkValues), TRUE
928 if (array_key_exists('civicrm_contact_contact_source', $row)) {
929 if ($value = $row['civicrm_contact_contact_source_id']) {
931 $url = CRM_Utils_System
::url("civicrm/contact/view",
932 'reset=1&cid=' . $value,
935 $rows[$rowNum]['civicrm_contact_contact_source_link'] = $url;
936 $rows[$rowNum]['civicrm_contact_contact_source_hover'] = $onHover;
942 if (array_key_exists('civicrm_contact_contact_assignee', $row)) {
943 $assigneeNames = explode(';', $row['civicrm_contact_contact_assignee']);
944 if ($value = $row['civicrm_contact_contact_assignee_id']) {
945 $assigneeContactIds = explode(';', $value);
948 foreach ($assigneeContactIds as $id => $value) {
949 if (isset($value) && isset($assigneeNames[$id])) {
950 $url = CRM_Utils_System
::url("civicrm/contact/view",
951 'reset=1&cid=' . $value,
954 $link[] = "<a title='" . $onHover . "' href='" . $url .
955 "'>{$assigneeNames[$id]}</a>";
958 $rows[$rowNum]['civicrm_contact_contact_assignee'] = implode('; ', $link);
964 if (array_key_exists('civicrm_contact_contact_target', $row)) {
965 $targetNames = explode(';', $row['civicrm_contact_contact_target']);
966 if ($value = $row['civicrm_contact_contact_target_id']) {
967 $targetContactIds = explode(';', $value);
970 foreach ($targetContactIds as $id => $value) {
971 if (isset($value) && isset($targetNames[$id])) {
972 $url = CRM_Utils_System
::url("civicrm/contact/view",
973 'reset=1&cid=' . $value,
976 $link[] = "<a title='" . $onHover . "' href='" . $url .
977 "'>{$targetNames[$id]}</a>";
980 $rows[$rowNum]['civicrm_contact_contact_target'] = implode('; ', $link);
986 if (array_key_exists('civicrm_activity_activity_type_id', $row)) {
987 if ($value = $row['civicrm_activity_activity_type_id']) {
988 $rows[$rowNum]['civicrm_activity_activity_type_id'] = $activityType[$value];
990 $rows[$rowNum]['civicrm_activity_activity_type_id_link'] = $actUrl;
991 $rows[$rowNum]['civicrm_activity_activity_type_id_hover'] = $onHoverAct;
997 if (array_key_exists('civicrm_activity_status_id', $row)) {
998 if ($value = $row['civicrm_activity_status_id']) {
999 $rows[$rowNum]['civicrm_activity_status_id'] = $activityStatus[$value];
1004 if (array_key_exists('civicrm_activity_priority_id', $row)) {
1005 if ($value = $row['civicrm_activity_priority_id']) {
1006 $rows[$rowNum]['civicrm_activity_priority_id'] = $priority[$value];
1011 if (array_key_exists('civicrm_activity_details', $row) && $this->_outputMode
== 'html') {
1012 if ($value = $row['civicrm_activity_details']) {
1013 $fullDetails = $rows[$rowNum]['civicrm_activity_details'];
1014 $rows[$rowNum]['civicrm_activity_details'] = substr($fullDetails, 0, strrpos(substr($fullDetails, 0, 80), ' '));
1016 $rows[$rowNum]['civicrm_activity_details'] .= " <a href='{$actUrl}' title='{$onHoverAct}'>(more)</a>";
1022 if (array_key_exists('civicrm_activity_campaign_id', $row)) {
1023 if ($value = $row['civicrm_activity_campaign_id']) {
1024 $rows[$rowNum]['civicrm_activity_campaign_id'] = $this->campaigns
[$value];
1029 if (array_key_exists('civicrm_activity_engagement_level', $row)) {
1030 if ($value = $row['civicrm_activity_engagement_level']) {
1031 $rows[$rowNum]['civicrm_activity_engagement_level'] = $this->engagementLevels
[$value];
1036 if (array_key_exists('civicrm_activity_activity_date_time', $row) &&
1037 array_key_exists('civicrm_activity_status_id', $row)
1039 if (CRM_Utils_Date
::overdue($rows[$rowNum]['civicrm_activity_activity_date_time']) &&
1040 $activityStatus[$row['civicrm_activity_status_id']] != 'Completed'
1042 $rows[$rowNum]['class'] = "status-overdue";
1047 $entryFound = $this->alterDisplayAddressFields($row, $rows, $rowNum, 'activity', 'List all activities for this', ';') ?
TRUE : $entryFound;
1055 public function sectionTotals() {
1056 // Reports using order_bys with sections must populate $this->_selectAliases in select() method.
1057 if (empty($this->_selectAliases
)) {
1061 if (!empty($this->_sections
)) {
1062 // pull section aliases out of $this->_sections
1063 $sectionAliases = array_keys($this->_sections
);
1066 foreach (array_merge($sectionAliases, $this->_selectAliases
) as $alias) {
1067 $ifnulls[] = "ifnull($alias, '') as $alias";
1069 $this->_select
= "SELECT " . implode(", ", $ifnulls);
1070 $this->_select
= CRM_Contact_BAO_Query
::appendAnyValueToSelect($ifnulls, $sectionAliases);
1072 $query = $this->_select
.
1073 ", count(DISTINCT civicrm_activity_id) as ct from {$this->temporaryTables['activity_temp_table']['name']} group by " .
1074 implode(", ", $sectionAliases);
1076 // initialize array of total counts
1078 $dao = $this->executeReportQuery($query);
1079 while ($dao->fetch()) {
1080 // let $this->_alterDisplay translate any integer ids to human-readable values.
1081 $rows[0] = $dao->toArray();
1082 $this->alterDisplay($rows);
1085 // add totals for all permutations of section values
1088 $aliasCount = count($sectionAliases);
1089 foreach ($sectionAliases as $alias) {
1090 $values[] = $row[$alias];
1091 $key = implode(CRM_Core_DAO
::VALUE_SEPARATOR
, $values);
1092 if ($i == $aliasCount) {
1093 // the last alias is the lowest-level section header; use count as-is
1094 $totals[$key] = $dao->ct
;
1097 // other aliases are higher level; roll count into their total
1098 $totals[$key] +
= $dao->ct
;
1102 $this->assign('sectionTotals', $totals);
1107 * @todo remove this function & declare the 3 contact tables separately
1109 * (Currently the construct method incorrectly melds them - this is an interim
1110 * refactor in order to get this under ReportTemplateTests)
1112 protected function buildAssigneeFrom() {
1113 $activityContacts = CRM_Activity_BAO_ActivityContact
::buildOptions('record_type_id', 'validate');
1114 $assigneeID = CRM_Utils_Array
::key('Activity Assignees', $activityContacts);
1116 FROM civicrm_activity {$this->_aliases['civicrm_activity']}
1117 INNER JOIN civicrm_activity_contact {$this->_aliases['civicrm_activity_contact']}
1118 ON {$this->_aliases['civicrm_activity']}.id = {$this->_aliases['civicrm_activity_contact']}.activity_id AND
1119 {$this->_aliases['civicrm_activity_contact']}.record_type_id = {$assigneeID}
1120 INNER JOIN civicrm_contact civicrm_contact_assignee
1121 ON {$this->_aliases['civicrm_activity_contact']}.contact_id = civicrm_contact_assignee.id
1124 if ($this->isTableSelected('civicrm_email')) {
1126 LEFT JOIN civicrm_email civicrm_email_assignee
1127 ON {$this->_aliases['civicrm_activity_contact']}.contact_id = civicrm_email_assignee.contact_id AND
1128 civicrm_email_assignee.is_primary = 1";
1130 if ($this->isTableSelected('civicrm_phone')) {
1132 LEFT JOIN civicrm_phone civicrm_phone_assignee
1133 ON {$this->_aliases['civicrm_activity_contact']}.contact_id = civicrm_phone_assignee.contact_id AND
1134 civicrm_phone_assignee.is_primary = 1 ";
1136 $this->_aliases
['civicrm_contact'] = 'civicrm_contact_assignee';
1137 $this->joinAddressFromContact();
1141 * @todo remove this function & declare the 3 contact tables separately
1143 * (Currently the construct method incorrectly melds them - this is an interim
1144 * refactor in order to get this under ReportTemplateTests)
1146 protected function buildSourceFrom() {
1147 $activityContacts = CRM_Activity_BAO_ActivityContact
::buildOptions('record_type_id', 'validate');
1148 $sourceID = CRM_Utils_Array
::key('Activity Source', $activityContacts);
1150 FROM civicrm_activity {$this->_aliases['civicrm_activity']}
1151 INNER JOIN civicrm_activity_contact {$this->_aliases['civicrm_activity_contact']}
1152 ON {$this->_aliases['civicrm_activity']}.id = {$this->_aliases['civicrm_activity_contact']}.activity_id AND
1153 {$this->_aliases['civicrm_activity_contact']}.record_type_id = {$sourceID}
1154 INNER JOIN civicrm_contact civicrm_contact_source
1155 ON {$this->_aliases['civicrm_activity_contact']}.contact_id = civicrm_contact_source.id
1158 if ($this->isTableSelected('civicrm_email')) {
1160 LEFT JOIN civicrm_email civicrm_email_source
1161 ON {$this->_aliases['civicrm_activity_contact']}.contact_id = civicrm_email_source.contact_id AND
1162 civicrm_email_source.is_primary = 1";
1164 if ($this->isTableSelected('civicrm_phone')) {
1166 LEFT JOIN civicrm_phone civicrm_phone_source
1167 ON {$this->_aliases['civicrm_activity_contact']}.contact_id = civicrm_phone_source.contact_id AND
1168 civicrm_phone_source.is_primary = 1 ";
1170 $this->_aliases
['civicrm_contact'] = 'civicrm_contact_source';
1171 $this->joinAddressFromContact();