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_Report_Form_Activity
extends CRM_Report_Form
{
18 protected $_selectAliasesTotal = [];
20 protected $_customGroupExtends = ['Activity'];
22 protected $_nonDisplayFields = [];
25 * This report has not been optimised for group filtering.
27 * The functionality for group filtering has been improved but not
28 * all reports have been adjusted to take care of it. This report has not
29 * and will run an inefficient query until fixed.
32 * @see https://issues.civicrm.org/jira/browse/CRM-19170
34 protected $groupFilterNotOptimised = TRUE;
39 * @throws \CRM_Core_Exception
41 public function __construct() {
42 // There could be multiple contacts. We not clear on which contact id to display.
43 // Lets hide it for now.
44 $this->_exposeContactID
= FALSE;
45 // if navigated from count link of activity summary reports.
46 $this->_resetDateFilter
= CRM_Utils_Request
::retrieve('resetDateFilter', 'Boolean');
48 $components = CRM_Core_Component
::getEnabledComponents();
49 $campaignEnabled = !empty($components['CiviCampaign']);
50 $caseEnabled = !empty($components['CiviCase']);
52 foreach ($components as $componentName => $componentInfo) {
53 // CRM-19201: Add support for reporting CiviCampaign activities
54 // For CiviCase, "access all cases and activities" is required here
55 // rather than "access my cases and activities" to prevent those with
56 // only the later permission from seeing a list of all cases which might
57 // present a privacy issue.
58 if (CRM_Core_Permission
::access($componentName, TRUE, TRUE)) {
59 $accessAllowed[] = $componentInfo->componentID
;
64 if (!empty($accessAllowed)) {
65 $include = 'OR v.component_id IN (' . implode(', ', $accessAllowed) . ')';
67 $condition = " AND ( v.component_id IS NULL {$include} )";
68 $this->activityTypes
= CRM_Core_OptionGroup
::values('activity_type', FALSE, FALSE, FALSE, $condition);
69 asort($this->activityTypes
);
71 // @todo split the 3 different contact tables into their own array items.
72 // this will massively simplify the needs of this report.
74 'civicrm_contact' => [
75 'dao' => 'CRM_Contact_DAO_Contact',
78 'name' => 'sort_name',
79 'title' => ts('Source Name'),
80 'alias' => 'civicrm_contact_source',
83 'contact_assignee' => [
84 'name' => 'sort_name',
85 'title' => ts('Assignee Name'),
86 'alias' => 'civicrm_contact_assignee',
87 'dbAlias' => "civicrm_contact_assignee.sort_name",
91 'name' => 'sort_name',
92 'title' => ts('Target Name'),
93 'alias' => 'civicrm_contact_target',
94 'dbAlias' => "civicrm_contact_target.sort_name",
97 'contact_target_birth' => [
98 'name' => 'birth_date',
99 'title' => ts('Target Birth Date'),
100 'alias' => 'civicrm_contact_target',
101 'dbAlias' => "civicrm_contact_target.birth_date",
103 'contact_target_gender' => [
104 'name' => 'gender_id',
105 'title' => ts('Target Gender'),
106 'alias' => 'civicrm_contact_target',
107 'dbAlias' => "civicrm_contact_target.gender_id",
110 'contact_source_id' => [
112 'alias' => 'civicrm_contact_source',
113 'dbAlias' => "civicrm_contact_source.id",
114 'no_display' => TRUE,
118 'contact_assignee_id' => [
120 'alias' => 'civicrm_contact_assignee',
121 'dbAlias' => "civicrm_contact_assignee.id",
122 'no_display' => TRUE,
126 'contact_target_id' => [
128 'alias' => 'civicrm_contact_target',
129 'dbAlias' => "civicrm_contact_target.id",
130 'no_display' => TRUE,
136 'contact_source' => [
137 'name' => 'sort_name',
138 'alias' => 'civicrm_contact_source',
139 'title' => ts('Source Name'),
140 'operator' => 'like',
141 'type' => CRM_Report_Form
::OP_STRING
,
143 'contact_assignee' => [
144 'name' => 'sort_name',
145 'alias' => 'civicrm_contact_assignee',
146 'title' => ts('Assignee Name'),
147 'operator' => 'like',
148 'type' => CRM_Report_Form
::OP_STRING
,
150 'contact_target' => [
151 'name' => 'sort_name',
152 'alias' => 'civicrm_contact_target',
153 'title' => ts('Target Name'),
154 'operator' => 'like',
155 'type' => CRM_Report_Form
::OP_STRING
,
158 'name' => 'current_user',
159 'title' => ts('Limit To Current User'),
160 'type' => CRM_Utils_Type
::T_INT
,
161 'operatorType' => CRM_Report_Form
::OP_SELECT
,
162 'options' => ['0' => ts('No'), '1' => ts('Yes')],
165 'grouping' => 'contact-fields',
168 'dao' => 'CRM_Core_DAO_Email',
170 'contact_source_email' => [
172 'title' => ts('Source Email'),
173 'alias' => 'civicrm_email_source',
175 'contact_assignee_email' => [
177 'title' => ts('Assignee Email'),
178 'alias' => 'civicrm_email_assignee',
180 'contact_target_email' => [
182 'title' => ts('Target Email'),
183 'alias' => 'civicrm_email_target',
187 'source_contact_email' => [
189 'title' => ts('Source Email'),
190 'dbAlias' => 'civicrm_email_contact_source_email',
195 'dao' => 'CRM_Core_DAO_Phone',
197 'contact_source_phone' => [
199 'title' => ts('Source Phone'),
200 'alias' => 'civicrm_phone_source',
202 'contact_assignee_phone' => [
204 'title' => ts('Assignee Phone'),
205 'alias' => 'civicrm_phone_assignee',
207 'contact_target_phone' => [
209 'title' => ts('Target Phone'),
210 'alias' => 'civicrm_phone_target',
214 'civicrm_activity' => [
215 'dao' => 'CRM_Activity_DAO_Activity',
218 'no_display' => TRUE,
219 'title' => ts('Activity ID'),
222 'source_record_id' => [
223 'no_display' => TRUE,
226 'activity_type_id' => [
227 'title' => ts('Activity Type'),
229 'type' => CRM_Utils_Type
::T_STRING
,
231 'activity_subject' => [
232 'title' => ts('Subject'),
235 'activity_date_time' => [
236 'title' => ts('Activity Date'),
240 'title' => ts('Activity Status'),
242 'type' => CRM_Utils_Type
::T_STRING
,
245 'title' => ts('Duration'),
246 'type' => CRM_Utils_Type
::T_INT
,
249 'title' => ts('Location'),
250 'type' => CRM_Utils_Type
::T_STRING
,
253 'title' => ts('Activity Details'),
256 'title' => ts('Priority'),
258 'type' => CRM_Utils_Type
::T_STRING
,
262 'activity_date_time' => [
263 'default' => 'this.month',
264 'operatorType' => CRM_Report_Form
::OP_DATE
,
266 'activity_subject' => ['title' => ts('Activity Subject')],
267 'activity_type_id' => [
268 'title' => ts('Activity Type'),
269 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
270 'options' => $this->activityTypes
,
273 'title' => ts('Activity Status'),
274 'type' => CRM_Utils_Type
::T_STRING
,
275 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
276 'options' => CRM_Core_PseudoConstant
::activityStatus(),
279 'title' => ts('Location'),
280 'type' => CRM_Utils_Type
::T_TEXT
,
283 'title' => ts('Activity Details'),
284 'type' => CRM_Utils_Type
::T_TEXT
,
287 'title' => ts('Activity Priority'),
288 'type' => CRM_Utils_Type
::T_STRING
,
289 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
290 'options' => CRM_Core_PseudoConstant
::get('CRM_Activity_DAO_Activity', 'priority_id'),
294 'activity_date_time' => [
295 'title' => ts('Activity Date'),
296 'default_weight' => '1',
297 'dbAlias' => 'civicrm_activity_activity_date_time',
299 'activity_type_id' => [
300 'title' => ts('Activity Type'),
301 'default_weight' => '2',
302 'dbAlias' => 'field(civicrm_activity_activity_type_id, ' . implode(', ', array_keys($this->activityTypes
)) . ')',
305 'grouping' => 'activity-fields',
306 'alias' => 'activity',
308 // Hack to get $this->_alias populated for the table.
309 'civicrm_activity_contact' => [
310 'dao' => 'CRM_Activity_DAO_ActivityContact',
313 ] +
$this->addressFields(TRUE);
315 if ($caseEnabled && CRM_Core_Permission
::check('access all cases and activities')) {
316 $this->_columns
['civicrm_activity']['filters']['include_case_activities'] = [
317 'name' => 'include_case_activities',
318 'title' => ts('Include Case Activities'),
319 'type' => CRM_Utils_Type
::T_INT
,
320 'operatorType' => CRM_Report_Form
::OP_SELECT
,
321 'options' => ['0' => ts('No'), '1' => ts('Yes')],
323 $this->_columns
['civicrm_case_activity'] = [
324 'dao' => 'CRM_Case_DAO_CaseActivity',
329 if ($campaignEnabled) {
330 // Add display column and filter for Survey Results, Campaign and Engagement Index if CiviCampaign is enabled
332 $this->_columns
['civicrm_activity']['fields']['result'] = [
333 'title' => ts('Survey Result'),
334 'default' => 'false',
336 $this->_columns
['civicrm_activity']['filters']['result'] = [
337 'title' => ts('Survey Result'),
338 'operator' => 'like',
339 'type' => CRM_Utils_Type
::T_STRING
,
341 // If we have campaigns enabled, add those elements to both the fields, filters.
342 $this->addCampaignFields('civicrm_activity');
343 $this->engagementLevels
= $campaignEnabled ? CRM_Campaign_PseudoConstant
::engagementLevel() : [];
344 if (!empty($this->engagementLevels
)) {
345 $this->_columns
['civicrm_activity']['fields']['engagement_level'] = [
346 'title' => ts('Engagement Index'),
347 'default' => 'false',
349 $this->_columns
['civicrm_activity']['filters']['engagement_level'] = [
350 'title' => ts('Engagement Index'),
351 'type' => CRM_Utils_Type
::T_INT
,
352 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
353 'options' => $this->engagementLevels
,
357 $this->_groupFilter
= TRUE;
358 $this->_tagFilter
= TRUE;
359 $this->_tagFilterTable
= 'civicrm_activity';
361 parent
::__construct();
364 protected static function addCaseActivityColumns($columns) {
365 $columns['civicrm_case_activity']['fields'] = [
367 'title' => ts('Case ID'),
369 'dbAlias' => $columns['civicrm_case_activity']['alias'] . '.case_id',
370 'type' => CRM_Utils_Type
::T_INT
,
377 * Adding address fields with dbAlias for order clause.
379 * @param bool $orderBy
384 public function addressFields($orderBy = FALSE) {
385 $address = parent
::addAddressFields(FALSE, TRUE);
387 foreach ($address['civicrm_address']['order_bys'] as $fieldName => $field) {
388 $address['civicrm_address']['order_bys'][$fieldName]['dbAlias'] = "civicrm_address_{$fieldName}";
395 * Build select clause.
397 * @todo get rid of $recordType param. It's only because 3 separate contact tables
398 * are mis-declared as one that we need it.
400 * @param null $recordType deprecated
401 * Parameter to hack around the bad decision made in construct to misrepresent
402 * different tables as the same table.
404 public function select($recordType = 'target') {
405 if (!array_key_exists("contact_{$recordType}", $this->_params
['fields']) &&
406 $recordType != 'final'
408 $this->_nonDisplayFields
[] = "civicrm_contact_contact_{$recordType}";
412 if ($recordType == 'final' && !empty($this->_nonDisplayFields
)) {
413 foreach ($this->_nonDisplayFields
as $fieldName) {
414 unset($this->_columnHeaders
[$fieldName]);
418 if (empty($this->_selectAliasesTotal
)) {
419 $this->_selectAliasesTotal
= $this->_selectAliases
;
423 if ($recordType == 'target') {
424 // @todo - fix up the way the tables are declared in construct & remove this.
425 foreach ($this->_selectClauses
as $key => $clause) {
426 if (strstr($clause, 'civicrm_contact_assignee.') ||
427 strstr($clause, 'civicrm_contact_source.') ||
428 strstr($clause, 'civicrm_email_assignee.') ||
429 strstr($clause, 'civicrm_email_source.') ||
430 strstr($clause, 'civicrm_phone_assignee.') ||
431 strstr($clause, 'civicrm_phone_source.')
433 $removeKeys[] = $key;
434 unset($this->_selectClauses
[$key]);
438 elseif ($recordType == 'assignee') {
439 // @todo - fix up the way the tables are declared in construct & remove this.
440 foreach ($this->_selectClauses
as $key => $clause) {
441 if (strstr($clause, 'civicrm_contact_target.') ||
442 strstr($clause, 'civicrm_contact_source.') ||
443 strstr($clause, 'civicrm_email_target.') ||
444 strstr($clause, 'civicrm_email_source.') ||
445 strstr($clause, 'civicrm_phone_target.') ||
446 strstr($clause, 'civicrm_phone_source.') ||
447 strstr($clause, 'civicrm_address_')
449 $removeKeys[] = $key;
450 unset($this->_selectClauses
[$key]);
454 elseif ($recordType == 'source') {
455 // @todo - fix up the way the tables are declared in construct & remove this.
456 foreach ($this->_selectClauses
as $key => $clause) {
457 if (strstr($clause, 'civicrm_contact_target.') ||
458 strstr($clause, 'civicrm_contact_assignee.') ||
459 strstr($clause, 'civicrm_email_target.') ||
460 strstr($clause, 'civicrm_email_assignee.') ||
461 strstr($clause, 'civicrm_phone_target.') ||
462 strstr($clause, 'civicrm_phone_assignee.') ||
463 strstr($clause, 'civicrm_address_')
465 $removeKeys[] = $key;
466 unset($this->_selectClauses
[$key]);
470 elseif ($recordType == 'final') {
471 $this->_selectClauses
= $this->_selectAliasesTotal
;
472 foreach ($this->_selectClauses
as $key => $clause) {
473 // @todo - fix up the way the tables are declared in construct & remove this.
474 if (strstr($clause, 'civicrm_contact_contact_target') ||
475 strstr($clause, 'civicrm_contact_contact_assignee') ||
476 strstr($clause, 'civicrm_contact_contact_source') ||
477 strstr($clause, 'civicrm_phone_contact_source_phone') ||
478 strstr($clause, 'civicrm_phone_contact_assignee_phone') ||
479 strstr($clause, 'civicrm_email_contact_source_email') ||
480 strstr($clause, 'civicrm_email_contact_assignee_email') ||
481 strstr($clause, 'civicrm_email_contact_target_email') ||
482 strstr($clause, 'civicrm_phone_contact_target_phone') ||
483 strstr($clause, 'civicrm_address_')
485 $this->_selectClauses
[$key] = "GROUP_CONCAT(DISTINCT $clause SEPARATOR ';') as $clause";
491 foreach ($removeKeys as $key) {
492 unset($this->_selectAliases
[$key]);
495 if ($recordType == 'target') {
496 foreach ($this->_columns
['civicrm_address']['order_bys'] as $fieldName => $field) {
497 $orderByFld = $this->_columns
['civicrm_address']['order_bys'][$fieldName];
498 $fldInfo = $this->_columns
['civicrm_address']['fields'][$fieldName];
499 $this->_selectAliases
[] = $orderByFld['dbAlias'];
500 $this->_selectClauses
[] = "{$fldInfo['dbAlias']} as {$orderByFld['dbAlias']}";
502 $this->_selectAliases
= array_unique($this->_selectAliases
);
503 $this->_selectClauses
= array_unique($this->_selectClauses
);
505 $this->_select
= "SELECT " . implode(', ', $this->_selectClauses
) . " ";
511 * @todo remove this function & declare the 3 contact tables separately
513 public function from() {
514 $this->buildFrom('target');
518 * Build where clause.
520 * @todo get rid of $recordType param. It's only because 3 separate contact tables
521 * are mis-declared as one that we need it.
523 * @param string $recordType
525 public function where($recordType = NULL) {
526 $this->_where
= " WHERE {$this->_aliases['civicrm_activity']}.is_test = 0 AND
527 {$this->_aliases['civicrm_activity']}.is_deleted = 0 AND
528 {$this->_aliases['civicrm_activity']}.is_current_revision = 1";
531 foreach ($this->_columns
as $tableName => $table) {
532 if (array_key_exists('filters', $table)) {
534 foreach ($table['filters'] as $fieldName => $field) {
536 if ($fieldName != 'contact_' . $recordType &&
537 (strstr($fieldName, '_target') ||
538 strstr($fieldName, '_assignee') ||
539 strstr($fieldName, '_source')
544 if (CRM_Utils_Array
::value('type', $field) & CRM_Utils_Type
::T_DATE
) {
545 $relative = $this->_params
["{$fieldName}_relative"] ??
NULL;
546 $from = $this->_params
["{$fieldName}_from"] ??
NULL;
547 $to = $this->_params
["{$fieldName}_to"] ??
NULL;
549 $clause = $this->dateClause($field['dbAlias'], $relative, $from, $to, $field['type']);
552 $op = $this->_params
["{$fieldName}_op"] ??
NULL;
553 if ($op && !($fieldName === "contact_{$recordType}" && ($op === 'nnll' ||
$op === 'nll'))) {
554 $clause = $this->whereClause($field,
556 CRM_Utils_Array
::value("{$fieldName}_value", $this->_params
),
557 CRM_Utils_Array
::value("{$fieldName}_min", $this->_params
),
558 CRM_Utils_Array
::value("{$fieldName}_max", $this->_params
)
560 if ($field['name'] == 'include_case_activities') {
563 if ($fieldName == 'activity_type_id' &&
564 empty($this->_params
['activity_type_id_value'])
566 if (empty($this->_params
['include_case_activities_value'])) {
567 $this->activityTypes
= CRM_Core_PseudoConstant
::activityType(TRUE, FALSE, FALSE, 'label', TRUE);
569 $actTypes = array_flip($this->activityTypes
);
570 $clause = "( {$this->_aliases['civicrm_activity']}.activity_type_id IN (" .
571 implode(',', $actTypes) . ") )";
576 if ($field['name'] == 'current_user') {
577 if (CRM_Utils_Array
::value("{$fieldName}_value", $this->_params
) ==
581 if ($contactID = CRM_Core_Session
::getLoggedInContactID()) {
582 $clause = "{$this->_aliases['civicrm_activity_contact']}.activity_id IN
583 (SELECT activity_id FROM civicrm_activity_contact WHERE contact_id = {$contactID})";
593 if (!empty($clause)) {
594 $clauses[] = $clause;
600 if (empty($clauses)) {
601 $this->_where
.= " ";
604 $this->_where
.= " AND " . implode(' AND ', $clauses);
607 if ($this->_aclWhere
) {
608 $this->_where
.= " AND {$this->_aclWhere} ";
613 * Override group by function.
615 public function groupBy() {
616 $this->_groupBy
= CRM_Contact_BAO_Query
::getGroupByFromSelectColumns($this->_selectClauses
, "{$this->_aliases['civicrm_activity']}.id");
622 * @param string $tableAlias
624 * @throws \CRM_Core_Exception
626 public function buildACLClause($tableAlias = 'contact_a') {
627 //override for ACL( Since Contact may be source
628 //contact/assignee or target also it may be null )
630 if (CRM_Core_Permission
::check('view all contacts')) {
631 $this->_aclFrom
= $this->_aclWhere
= NULL;
635 $contactID = CRM_Core_Session
::getLoggedInContactID();
639 $contactID = CRM_Utils_Type
::escape($contactID, 'Integer');
641 CRM_Contact_BAO_Contact_Permission
::cache($contactID);
643 foreach ($tableAlias as $k => $alias) {
644 $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 ";
647 $this->_aclFrom
= implode(" ", $clauses);
648 $this->_aclWhere
= NULL;
652 * @param int $groupID
656 public function add2group($groupID) {
657 if (CRM_Utils_Array
::value("contact_target_op", $this->_params
) == 'nll') {
658 CRM_Core_Error
::statusBounce(ts('Current filter criteria didn\'t have any target contact to add to group'));
661 $new_select = 'AS addtogroup_contact_id';
662 $select = str_ireplace('AS civicrm_contact_contact_target_id', $new_select, $this->_select
);
663 $new_having = ' addtogroup_contact_id';
664 $having = str_ireplace(' civicrm_contact_contact_target_id', $new_having, $this->_having
);
666 FROM {$this->temporaryTables['activity_temp_table']['name']} tar
667 GROUP BY civicrm_activity_id $having {$this->_orderBy}";
668 $select = 'AS addtogroup_contact_id';
669 $query = str_ireplace('AS civicrm_contact_contact_target_id', $select, $query);
670 CRM_Core_DAO
::disableFullGroupByMode();
671 $dao = $this->executeReportQuery($query);
672 CRM_Core_DAO
::reenableFullGroupByMode();
675 // Add resulting contacts to group
676 while ($dao->fetch()) {
677 if ($dao->addtogroup_contact_id
) {
678 $contact_id = explode(';', $dao->addtogroup_contact_id
);
679 if ($contact_id[0]) {
680 $contactIDs[$contact_id[0]] = $contact_id[0];
685 if (!empty($contactIDs)) {
686 CRM_Contact_BAO_GroupContact
::addContactsToGroup($contactIDs, $groupID);
687 CRM_Core_Session
::setStatus(ts("Listed contact(s) have been added to the selected group."), ts('Contacts Added'), 'success');
690 CRM_Core_Session
::setStatus(ts("The listed records(s) cannot be added to the group."));
701 public static function formRule($fields, $files, $self) {
703 $config = CRM_Core_Config
::singleton();
704 if (in_array("CiviCase", $config->enableComponents
)) {
705 $componentId = CRM_Core_Component
::getComponentID('CiviCase');
706 $caseActivityTypes = CRM_Core_OptionGroup
::values('activity_type', TRUE, FALSE, FALSE, " AND v.component_id={$componentId}");
707 if (!empty($fields['activity_type_id_value']) && is_array($fields['activity_type_id_value']) && empty($fields['include_case_activities_value'])) {
708 foreach ($fields['activity_type_id_value'] as $activityTypeId) {
709 if (in_array($activityTypeId, $caseActivityTypes)) {
710 $errors['fields'] = ts("Please enable 'Include Case Activities' to filter with Case Activity types.");
723 public function buildQuery($applyLimit = TRUE) {
724 $activityContacts = CRM_Activity_BAO_ActivityContact
::buildOptions('record_type_id', 'validate');
725 $sourceID = CRM_Utils_Array
::key('Activity Source', $activityContacts);
727 //Assign those recordtype to array which have filter operator as 'Is not empty' or 'Is empty'
729 foreach (['target', 'source', 'assignee'] as $type) {
730 if (CRM_Utils_Array
::value("contact_{$type}_op", $this->_params
) ==
731 'nnll' ||
!empty($this->_params
["contact_{$type}_value"])
733 $nullFilters[] = " civicrm_contact_contact_{$type}_id IS NOT NULL ";
735 elseif (CRM_Utils_Array
::value("contact_{$type}_op", $this->_params
) ==
738 $nullFilters[] = " civicrm_contact_contact_{$type}_id IS NULL ";
742 if (!empty($this->_params
['include_case_activities_value'])) {
743 $this->_columns
= self
::addCaseActivityColumns($this->_columns
);
746 // @todo - all this temp table stuff is here because pre 4.4 the activity contact
747 // form did not exist.
748 // Fixing the way the construct method declares them will make all this redundant.
749 // 1. fill temp table with target results
750 $this->buildACLClause(['civicrm_contact_target']);
751 $this->select('target');
753 $this->customDataFrom();
754 $this->where('target');
755 $tempTableName = $this->createTemporaryTable('activity_temp_table', "{$this->_select} {$this->_from} {$this->_where}");
757 // 2. add new columns to hold assignee and source results
758 // fixme: add when required
760 ALTER TABLE $tempTableName
761 MODIFY COLUMN civicrm_contact_contact_target_id VARCHAR(128),
762 ADD COLUMN civicrm_contact_contact_assignee VARCHAR(128),
763 ADD COLUMN civicrm_contact_contact_source VARCHAR(128),
764 ADD COLUMN civicrm_contact_contact_assignee_id VARCHAR(128),
765 ADD COLUMN civicrm_contact_contact_source_id VARCHAR(128),
766 ADD COLUMN civicrm_phone_contact_assignee_phone VARCHAR(128),
767 ADD COLUMN civicrm_phone_contact_source_phone VARCHAR(128),
768 ADD COLUMN civicrm_email_contact_assignee_email VARCHAR(128),
769 ADD COLUMN civicrm_email_contact_source_email VARCHAR(128)";
770 $this->executeReportQuery($tempQuery);
772 // 3. fill temp table with assignee results
773 $this->buildACLClause(['civicrm_contact_assignee']);
774 $this->select('assignee');
775 $this->buildAssigneeFrom();
777 $this->customDataFrom();
778 $this->where('assignee');
779 $insertCols = implode(',', $this->_selectAliases
);
780 $tempQuery = "INSERT INTO $tempTableName ({$insertCols})
782 {$this->_from} {$this->_where}";
783 $this->executeReportQuery($tempQuery);
785 // 4. fill temp table with source results
786 $this->buildACLClause(['civicrm_contact_source']);
787 $this->select('source');
788 $this->buildSourceFrom();
789 $this->customDataFrom();
790 $this->where('source');
791 $insertCols = implode(',', $this->_selectAliases
);
792 $tempQuery = "INSERT INTO $tempTableName ({$insertCols})
794 {$this->_from} {$this->_where}";
795 $this->executeReportQuery($tempQuery);
797 // 5. show final result set from temp table
799 $this->select('final');
801 if (!empty($nullFilters)) {
802 $this->_having
= "HAVING " . implode(' AND ', $nullFilters);
805 foreach ($this->_sections
as $alias => $section) {
806 if (!empty($section) && $section['name'] == 'activity_date_time') {
807 $this->alterSectionHeaderForDateTime($tempTableName, $section['tplField']);
815 $groupByFromSelect = CRM_Contact_BAO_Query
::getGroupByFromSelectColumns($this->_selectClauses
, 'civicrm_activity_id');
817 $this->_where
= " WHERE (1)";
818 $this->buildPermissionClause();
819 if ($this->_aclWhere
) {
820 $this->_where
.= " AND {$this->_aclWhere} ";
824 if (!empty($this->_params
['include_case_activities_value'])) {
825 $caseJoin = "LEFT JOIN civicrm_case_activity {$this->_aliases['civicrm_case_activity']} ON {$this->_aliases['civicrm_activity']}.id = {$this->_aliases['civicrm_case_activity']}.activity_id";
828 $sql = "{$this->_select}
829 FROM $tempTableName tar
830 INNER JOIN civicrm_activity {$this->_aliases['civicrm_activity']} ON {$this->_aliases['civicrm_activity']}.id = tar.civicrm_activity_id
831 INNER JOIN civicrm_activity_contact {$this->_aliases['civicrm_activity_contact']} ON {$this->_aliases['civicrm_activity_contact']}.activity_id = {$this->_aliases['civicrm_activity']}.id
832 AND {$this->_aliases['civicrm_activity_contact']}.record_type_id = {$sourceID}
833 LEFT JOIN civicrm_contact contact_civireport ON contact_civireport.id = {$this->_aliases['civicrm_activity_contact']}.contact_id
835 {$this->_where} {$groupByFromSelect} {$this->_having} {$this->_orderBy} {$this->_limit}";
837 CRM_Utils_Hook
::alterReportVar('sql', $this, $this);
838 $this->addToDeveloperTab($sql);
844 * Override parent to reset value of activity_date.
846 public function beginPostProcessCommon() {
847 if (!empty($this->_resetDateFilter
)) {
848 $this->_formValues
['activity_date_time_relative'] = NULL;
853 * Alter display of rows.
855 * Iterate through the rows retrieved via SQL and make changes for display purposes,
856 * such as rendering contacts as links.
859 * Rows generated by SQL, with an array for each row.
861 * @throws \CRM_Core_Exception
863 public function alterDisplay(&$rows) {
865 $activityType = CRM_Core_PseudoConstant
::activityType(TRUE, TRUE, FALSE, 'label', TRUE);
866 $activityStatus = CRM_Core_PseudoConstant
::activityStatus();
867 $priority = CRM_Core_PseudoConstant
::get('CRM_Activity_DAO_Activity', 'priority_id');
868 $genders = CRM_Core_PseudoConstant
::get('CRM_Contact_DAO_Contact', 'gender_id');
871 // Would we ever want to retrieve from the form controller??
872 $form = $this->noController ?
NULL : $this;
873 $context = CRM_Utils_Request
::retrieve('context', 'Alphanumeric', $form, FALSE, 'report');
876 if (CRM_Core_Permission
::check('access CiviCRM')) {
878 $onHover = ts('View Contact Summary for this Contact');
879 $onHoverAct = ts('View Activity Record');
881 foreach ($rows as $rowNum => $row) {
882 // if we have an activity type, format the View Activity link for use in various columns
884 array_key_exists('civicrm_activity_activity_type_id', $row)
886 // Check for target contact id(s) and use the first contact id in that list for view activity link if found,
887 // else use source contact id
888 if (!empty($rows[$rowNum]['civicrm_contact_contact_target_id'])) {
889 $targets = explode(';', $rows[$rowNum]['civicrm_contact_contact_target_id']);
893 $cid = $rows[$rowNum]['civicrm_contact_contact_source_id'];
896 if (empty($this->_params
['include_case_activities_value']) ||
empty($rows[$rowNum]['civicrm_case_activity_case_id'])) {
897 // Generate a "view activity" link
898 $actActionLinks = CRM_Activity_Selector_Activity
::actionLinks($row['civicrm_activity_activity_type_id'],
899 CRM_Utils_Array
::value('civicrm_activity_source_record_id', $rows[$rowNum]),
901 $rows[$rowNum]['civicrm_activity_id']
905 'id' => $rows[$rowNum]['civicrm_activity_id'],
909 $actUrl = CRM_Utils_System
::url($actActionLinks[CRM_Core_Action
::VIEW
]['url'],
910 CRM_Core_Action
::replace($actActionLinks[CRM_Core_Action
::VIEW
]['qs'], $actLinkValues), TRUE
914 // Generate a "view case activity" link
915 $caseActionLinks = CRM_Case_Selector_Search
::actionLinks();
917 'aid' => $rows[$rowNum]['civicrm_activity_id'],
918 'caseid' => $rows[$rowNum]['civicrm_case_activity_case_id'],
922 $actUrl = CRM_Utils_System
::url($caseActionLinks[CRM_Core_Action
::VIEW
]['url'],
923 CRM_Core_Action
::replace($caseActionLinks[CRM_Core_Action
::VIEW
]['qs'], $caseLinkValues), 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 if (array_key_exists('civicrm_contact_contact_target_gender', $row)) {
1048 if ($value = $row['civicrm_contact_contact_target_gender']) {
1049 $rows[$rowNum]['civicrm_contact_contact_target_gender'] = $genders[$value];
1054 $entryFound = $this->alterDisplayAddressFields($row, $rows, $rowNum, 'activity', 'List all activities for this', ';') ?
TRUE : $entryFound;
1062 public function sectionTotals() {
1063 // Reports using order_bys with sections must populate $this->_selectAliases in select() method.
1064 if (empty($this->_selectAliases
)) {
1068 if (!empty($this->_sections
)) {
1069 // pull section aliases out of $this->_sections
1070 $sectionAliases = array_keys($this->_sections
);
1073 foreach (array_merge($sectionAliases, $this->_selectAliases
) as $alias) {
1074 $ifnulls[] = "ifnull($alias, '') as $alias";
1076 $this->_select
= "SELECT " . implode(", ", $ifnulls);
1077 $this->_select
= CRM_Contact_BAO_Query
::appendAnyValueToSelect($ifnulls, $sectionAliases);
1079 $query = $this->_select
.
1080 ", count(DISTINCT civicrm_activity_id) as ct from {$this->temporaryTables['activity_temp_table']['name']} group by " .
1081 implode(", ", $sectionAliases);
1083 // initialize array of total counts
1085 $dao = $this->executeReportQuery($query);
1086 while ($dao->fetch()) {
1087 // let $this->_alterDisplay translate any integer ids to human-readable values.
1088 $rows[0] = $dao->toArray();
1089 $this->alterDisplay($rows);
1092 // add totals for all permutations of section values
1095 $aliasCount = count($sectionAliases);
1096 foreach ($sectionAliases as $alias) {
1097 $values[] = $row[$alias];
1098 $key = implode(CRM_Core_DAO
::VALUE_SEPARATOR
, $values);
1099 if ($i == $aliasCount) {
1100 // the last alias is the lowest-level section header; use count as-is
1101 $totals[$key] = $dao->ct
;
1104 // other aliases are higher level; roll count into their total
1105 $totals[$key] +
= $dao->ct
;
1109 $this->assign('sectionTotals', $totals);
1114 * @todo remove this function & declare the 3 contact tables separately
1116 * (Currently the construct method incorrectly melds them - this is an interim
1117 * refactor in order to get this under ReportTemplateTests)
1119 protected function buildAssigneeFrom() {
1120 $this->buildFrom('assignee');
1124 * @todo remove this function & declare the 3 contact tables separately
1126 * (Currently the construct method incorrectly melds them - this is an interim
1127 * refactor in order to get this under ReportTemplateTests)
1129 protected function buildSourceFrom() {
1130 $this->buildFrom('source');
1134 * Shared function to build the from clause
1136 * @param string $recordType (one of 'source', 'activity', 'target')
1138 protected function buildFrom($recordType) {
1139 $activityContacts = CRM_Activity_BAO_ActivityContact
::buildOptions('record_type_id', 'validate');
1140 switch ($recordType) {
1142 $recordTypeID = CRM_Utils_Array
::key('Activity Targets', $activityContacts);
1146 $recordTypeID = CRM_Utils_Array
::key('Activity Source', $activityContacts);
1150 $recordTypeID = CRM_Utils_Array
::key('Activity Assignees', $activityContacts);
1156 FROM civicrm_activity {$this->_aliases['civicrm_activity']}
1157 INNER JOIN civicrm_activity_contact {$this->_aliases['civicrm_activity_contact']}
1158 ON {$this->_aliases['civicrm_activity']}.id = {$this->_aliases['civicrm_activity_contact']}.activity_id AND
1159 {$this->_aliases['civicrm_activity_contact']}.record_type_id = {$recordTypeID}
1160 INNER JOIN civicrm_contact civicrm_contact_{$recordType}
1161 ON {$this->_aliases['civicrm_activity_contact']}.contact_id = civicrm_contact_{$recordType}.id
1163 if (!empty($this->_params
['include_case_activities_value'])) {
1165 LEFT JOIN civicrm_case_activity {$this->_aliases['civicrm_case_activity']}
1166 ON {$this->_aliases['civicrm_case_activity']}.activity_id = {$this->_aliases['civicrm_activity']}.id";
1169 if ($this->isTableSelected('civicrm_email')) {
1171 LEFT JOIN civicrm_email civicrm_email_{$recordType}
1172 ON {$this->_aliases['civicrm_activity_contact']}.contact_id = civicrm_email_{$recordType}.contact_id AND
1173 civicrm_email_{$recordType}.is_primary = 1";
1176 if ($this->isTableSelected('civicrm_phone')) {
1178 LEFT JOIN civicrm_phone civicrm_phone_{$recordType}
1179 ON {$this->_aliases['civicrm_activity_contact']}.contact_id = civicrm_phone_{$recordType}.contact_id AND
1180 civicrm_phone_{$recordType}.is_primary = 1 ";
1182 $this->_aliases
['civicrm_contact'] = "civicrm_contact_{$recordType}";
1184 $this->joinAddressFromContact();