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.
35 protected $groupFilterNotOptimised = TRUE;
40 * @throws \CRM_Core_Exception
42 public function __construct() {
43 // There could be multiple contacts. We not clear on which contact id to display.
44 // Lets hide it for now.
45 $this->_exposeContactID
= FALSE;
46 // if navigated from count link of activity summary reports.
47 $this->_resetDateFilter
= CRM_Utils_Request
::retrieve('resetDateFilter', 'Boolean');
49 $components = CRM_Core_Component
::getEnabledComponents();
50 $campaignEnabled = !empty($components['CiviCampaign']);
51 $caseEnabled = !empty($components['CiviCase']);
53 foreach ($components as $componentName => $componentInfo) {
54 // CRM-19201: Add support for reporting CiviCampaign activities
55 // For CiviCase, "access all cases and activities" is required here
56 // rather than "access my cases and activities" to prevent those with
57 // only the later permission from seeing a list of all cases which might
58 // present a privacy issue.
59 if (CRM_Core_Permission
::access($componentName, TRUE, TRUE)) {
60 $accessAllowed[] = $componentInfo->componentID
;
65 if (!empty($accessAllowed)) {
66 $include = 'OR v.component_id IN (' . implode(', ', $accessAllowed) . ')';
68 $condition = " AND ( v.component_id IS NULL {$include} )";
69 $this->activityTypes
= CRM_Core_OptionGroup
::values('activity_type', FALSE, FALSE, FALSE, $condition);
70 asort($this->activityTypes
);
72 // @todo split the 3 different contact tables into their own array items.
73 // this will massively simplify the needs of this report.
75 'civicrm_contact' => [
76 'dao' => 'CRM_Contact_DAO_Contact',
79 'name' => 'sort_name',
80 'title' => ts('Source Name'),
81 'alias' => 'civicrm_contact_source',
84 'contact_assignee' => [
85 'name' => 'sort_name',
86 'title' => ts('Assignee Name'),
87 'alias' => 'civicrm_contact_assignee',
88 'dbAlias' => "civicrm_contact_assignee.sort_name",
92 'name' => 'sort_name',
93 'title' => ts('Target Name'),
94 'alias' => 'civicrm_contact_target',
95 'dbAlias' => "civicrm_contact_target.sort_name",
98 'contact_source_id' => [
100 'alias' => 'civicrm_contact_source',
101 'dbAlias' => "civicrm_contact_source.id",
102 'no_display' => TRUE,
106 'contact_assignee_id' => [
108 'alias' => 'civicrm_contact_assignee',
109 'dbAlias' => "civicrm_contact_assignee.id",
110 'no_display' => TRUE,
114 'contact_target_id' => [
116 'alias' => 'civicrm_contact_target',
117 'dbAlias' => "civicrm_contact_target.id",
118 'no_display' => TRUE,
124 'contact_source' => [
125 'name' => 'sort_name',
126 'alias' => 'civicrm_contact_source',
127 'title' => ts('Source Name'),
128 'operator' => 'like',
129 'type' => CRM_Report_Form
::OP_STRING
,
131 'contact_assignee' => [
132 'name' => 'sort_name',
133 'alias' => 'civicrm_contact_assignee',
134 'title' => ts('Assignee Name'),
135 'operator' => 'like',
136 'type' => CRM_Report_Form
::OP_STRING
,
138 'contact_target' => [
139 'name' => 'sort_name',
140 'alias' => 'civicrm_contact_target',
141 'title' => ts('Target Name'),
142 'operator' => 'like',
143 'type' => CRM_Report_Form
::OP_STRING
,
146 'name' => 'current_user',
147 'title' => ts('Limit To Current User'),
148 'type' => CRM_Utils_Type
::T_INT
,
149 'operatorType' => CRM_Report_Form
::OP_SELECT
,
150 'options' => ['0' => ts('No'), '1' => ts('Yes')],
153 'grouping' => 'contact-fields',
156 'dao' => 'CRM_Core_DAO_Email',
158 'contact_source_email' => [
160 'title' => ts('Source Email'),
161 'alias' => 'civicrm_email_source',
163 'contact_assignee_email' => [
165 'title' => ts('Assignee Email'),
166 'alias' => 'civicrm_email_assignee',
168 'contact_target_email' => [
170 'title' => ts('Target Email'),
171 'alias' => 'civicrm_email_target',
175 'source_contact_email' => [
177 'title' => ts('Source Email'),
178 'dbAlias' => 'civicrm_email_contact_source_email',
183 'dao' => 'CRM_Core_DAO_Phone',
185 'contact_source_phone' => [
187 'title' => ts('Source Phone'),
188 'alias' => 'civicrm_phone_source',
190 'contact_assignee_phone' => [
192 'title' => ts('Assignee Phone'),
193 'alias' => 'civicrm_phone_assignee',
195 'contact_target_phone' => [
197 'title' => ts('Target Phone'),
198 'alias' => 'civicrm_phone_target',
202 'civicrm_activity' => [
203 'dao' => 'CRM_Activity_DAO_Activity',
206 'no_display' => TRUE,
207 'title' => ts('Activity ID'),
210 'source_record_id' => [
211 'no_display' => TRUE,
214 'activity_type_id' => [
215 'title' => ts('Activity Type'),
217 'type' => CRM_Utils_Type
::T_STRING
,
219 'activity_subject' => [
220 'title' => ts('Subject'),
223 'activity_date_time' => [
224 'title' => ts('Activity Date'),
228 'title' => ts('Activity Status'),
230 'type' => CRM_Utils_Type
::T_STRING
,
233 'title' => ts('Duration'),
234 'type' => CRM_Utils_Type
::T_INT
,
237 'title' => ts('Location'),
238 'type' => CRM_Utils_Type
::T_STRING
,
241 'title' => ts('Activity Details'),
244 'title' => ts('Priority'),
246 'type' => CRM_Utils_Type
::T_STRING
,
250 'activity_date_time' => [
251 'default' => 'this.month',
252 'operatorType' => CRM_Report_Form
::OP_DATE
,
254 'activity_subject' => ['title' => ts('Activity Subject')],
255 'activity_type_id' => [
256 'title' => ts('Activity Type'),
257 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
258 'options' => $this->activityTypes
,
261 'title' => ts('Activity Status'),
262 'type' => CRM_Utils_Type
::T_STRING
,
263 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
264 'options' => CRM_Core_PseudoConstant
::activityStatus(),
267 'title' => ts('Location'),
268 'type' => CRM_Utils_Type
::T_TEXT
,
271 'title' => ts('Activity Details'),
272 'type' => CRM_Utils_Type
::T_TEXT
,
275 'title' => ts('Activity Priority'),
276 'type' => CRM_Utils_Type
::T_STRING
,
277 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
278 'options' => CRM_Core_PseudoConstant
::get('CRM_Activity_DAO_Activity', 'priority_id'),
282 'activity_date_time' => [
283 'title' => ts('Activity Date'),
284 'default_weight' => '1',
285 'dbAlias' => 'civicrm_activity_activity_date_time',
287 'activity_type_id' => [
288 'title' => ts('Activity Type'),
289 'default_weight' => '2',
290 'dbAlias' => 'field(civicrm_activity_activity_type_id, ' . implode(', ', array_keys($this->activityTypes
)) . ')',
293 'grouping' => 'activity-fields',
294 'alias' => 'activity',
296 // Hack to get $this->_alias populated for the table.
297 'civicrm_activity_contact' => [
298 'dao' => 'CRM_Activity_DAO_ActivityContact',
301 ] +
$this->addressFields(TRUE);
303 if ($caseEnabled && CRM_Core_Permission
::check('access all cases and activities')) {
304 $this->_columns
['civicrm_activity']['filters']['include_case_activities'] = [
305 'name' => 'include_case_activities',
306 'title' => ts('Include Case Activities'),
307 'type' => CRM_Utils_Type
::T_INT
,
308 'operatorType' => CRM_Report_Form
::OP_SELECT
,
309 'options' => ['0' => ts('No'), '1' => ts('Yes')],
311 $this->_columns
['civicrm_case_activity'] = [
312 'dao' => 'CRM_Case_DAO_CaseActivity',
317 if ($campaignEnabled) {
318 // Add display column and filter for Survey Results, Campaign and Engagement Index if CiviCampaign is enabled
320 $this->_columns
['civicrm_activity']['fields']['result'] = [
321 'title' => ts('Survey Result'),
322 'default' => 'false',
324 $this->_columns
['civicrm_activity']['filters']['result'] = [
325 'title' => ts('Survey Result'),
326 'operator' => 'like',
327 'type' => CRM_Utils_Type
::T_STRING
,
329 // If we have campaigns enabled, add those elements to both the fields, filters.
330 $this->addCampaignFields('civicrm_activity');
331 $this->engagementLevels
= $campaignEnabled ? CRM_Campaign_PseudoConstant
::engagementLevel() : [];
332 if (!empty($this->engagementLevels
)) {
333 $this->_columns
['civicrm_activity']['fields']['engagement_level'] = [
334 'title' => ts('Engagement Index'),
335 'default' => 'false',
337 $this->_columns
['civicrm_activity']['filters']['engagement_level'] = [
338 'title' => ts('Engagement Index'),
339 'type' => CRM_Utils_Type
::T_INT
,
340 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
341 'options' => $this->engagementLevels
,
345 $this->_groupFilter
= TRUE;
346 $this->_tagFilter
= TRUE;
347 $this->_tagFilterTable
= 'civicrm_activity';
349 parent
::__construct();
352 protected static function addCaseActivityColumns($columns) {
353 $columns['civicrm_case_activity']['fields'] = [
355 'title' => ts('Case ID'),
357 'dbAlias' => $columns['civicrm_case_activity']['alias'] . '.case_id',
358 'type' => CRM_Utils_Type
::T_INT
,
365 * Adding address fields with dbAlias for order clause.
367 * @param bool $orderBy
372 public function addressFields($orderBy = FALSE) {
373 $address = parent
::addAddressFields(FALSE, TRUE);
375 foreach ($address['civicrm_address']['order_bys'] as $fieldName => $field) {
376 $address['civicrm_address']['order_bys'][$fieldName]['dbAlias'] = "civicrm_address_{$fieldName}";
383 * Build select clause.
385 * @todo get rid of $recordType param. It's only because 3 separate contact tables
386 * are mis-declared as one that we need it.
388 * @param null $recordType deprecated
389 * Parameter to hack around the bad decision made in construct to misrepresent
390 * different tables as the same table.
392 public function select($recordType = 'target') {
393 if (!array_key_exists("contact_{$recordType}", $this->_params
['fields']) &&
394 $recordType != 'final'
396 $this->_nonDisplayFields
[] = "civicrm_contact_contact_{$recordType}";
400 if ($recordType == 'final' && !empty($this->_nonDisplayFields
)) {
401 foreach ($this->_nonDisplayFields
as $fieldName) {
402 unset($this->_columnHeaders
[$fieldName]);
406 if (empty($this->_selectAliasesTotal
)) {
407 $this->_selectAliasesTotal
= $this->_selectAliases
;
411 if ($recordType == 'target') {
412 // @todo - fix up the way the tables are declared in construct & remove this.
413 foreach ($this->_selectClauses
as $key => $clause) {
414 if (strstr($clause, 'civicrm_contact_assignee.') ||
415 strstr($clause, 'civicrm_contact_source.') ||
416 strstr($clause, 'civicrm_email_assignee.') ||
417 strstr($clause, 'civicrm_email_source.') ||
418 strstr($clause, 'civicrm_phone_assignee.') ||
419 strstr($clause, 'civicrm_phone_source.')
421 $removeKeys[] = $key;
422 unset($this->_selectClauses
[$key]);
426 elseif ($recordType == 'assignee') {
427 // @todo - fix up the way the tables are declared in construct & remove this.
428 foreach ($this->_selectClauses
as $key => $clause) {
429 if (strstr($clause, 'civicrm_contact_target.') ||
430 strstr($clause, 'civicrm_contact_source.') ||
431 strstr($clause, 'civicrm_email_target.') ||
432 strstr($clause, 'civicrm_email_source.') ||
433 strstr($clause, 'civicrm_phone_target.') ||
434 strstr($clause, 'civicrm_phone_source.') ||
435 strstr($clause, 'civicrm_address_')
437 $removeKeys[] = $key;
438 unset($this->_selectClauses
[$key]);
442 elseif ($recordType == 'source') {
443 // @todo - fix up the way the tables are declared in construct & remove this.
444 foreach ($this->_selectClauses
as $key => $clause) {
445 if (strstr($clause, 'civicrm_contact_target.') ||
446 strstr($clause, 'civicrm_contact_assignee.') ||
447 strstr($clause, 'civicrm_email_target.') ||
448 strstr($clause, 'civicrm_email_assignee.') ||
449 strstr($clause, 'civicrm_phone_target.') ||
450 strstr($clause, 'civicrm_phone_assignee.') ||
451 strstr($clause, 'civicrm_address_')
453 $removeKeys[] = $key;
454 unset($this->_selectClauses
[$key]);
458 elseif ($recordType == 'final') {
459 $this->_selectClauses
= $this->_selectAliasesTotal
;
460 foreach ($this->_selectClauses
as $key => $clause) {
461 // @todo - fix up the way the tables are declared in construct & remove this.
462 if (strstr($clause, 'civicrm_contact_contact_target') ||
463 strstr($clause, 'civicrm_contact_contact_assignee') ||
464 strstr($clause, 'civicrm_contact_contact_source') ||
465 strstr($clause, 'civicrm_phone_contact_source_phone') ||
466 strstr($clause, 'civicrm_phone_contact_assignee_phone') ||
467 strstr($clause, 'civicrm_email_contact_source_email') ||
468 strstr($clause, 'civicrm_email_contact_assignee_email') ||
469 strstr($clause, 'civicrm_email_contact_target_email') ||
470 strstr($clause, 'civicrm_phone_contact_target_phone') ||
471 strstr($clause, 'civicrm_address_')
473 $this->_selectClauses
[$key] = "GROUP_CONCAT(DISTINCT $clause SEPARATOR ';') as $clause";
479 foreach ($removeKeys as $key) {
480 unset($this->_selectAliases
[$key]);
483 if ($recordType == 'target') {
484 foreach ($this->_columns
['civicrm_address']['order_bys'] as $fieldName => $field) {
485 $orderByFld = $this->_columns
['civicrm_address']['order_bys'][$fieldName];
486 $fldInfo = $this->_columns
['civicrm_address']['fields'][$fieldName];
487 $this->_selectAliases
[] = $orderByFld['dbAlias'];
488 $this->_selectClauses
[] = "{$fldInfo['dbAlias']} as {$orderByFld['dbAlias']}";
490 $this->_selectAliases
= array_unique($this->_selectAliases
);
491 $this->_selectClauses
= array_unique($this->_selectClauses
);
493 $this->_select
= "SELECT " . implode(', ', $this->_selectClauses
) . " ";
499 * @todo remove this function & declare the 3 contact tables separately
501 public function from() {
502 $this->buildFrom('target');
506 * Build where clause.
508 * @todo get rid of $recordType param. It's only because 3 separate contact tables
509 * are mis-declared as one that we need it.
511 * @param string $recordType
513 public function where($recordType = NULL) {
514 $this->_where
= " WHERE {$this->_aliases['civicrm_activity']}.is_test = 0 AND
515 {$this->_aliases['civicrm_activity']}.is_deleted = 0 AND
516 {$this->_aliases['civicrm_activity']}.is_current_revision = 1";
519 foreach ($this->_columns
as $tableName => $table) {
520 if (array_key_exists('filters', $table)) {
522 foreach ($table['filters'] as $fieldName => $field) {
524 if ($fieldName != 'contact_' . $recordType &&
525 (strstr($fieldName, '_target') ||
526 strstr($fieldName, '_assignee') ||
527 strstr($fieldName, '_source')
532 if (CRM_Utils_Array
::value('type', $field) & CRM_Utils_Type
::T_DATE
) {
533 $relative = $this->_params
["{$fieldName}_relative"] ??
NULL;
534 $from = $this->_params
["{$fieldName}_from"] ??
NULL;
535 $to = $this->_params
["{$fieldName}_to"] ??
NULL;
537 $clause = $this->dateClause($field['dbAlias'], $relative, $from, $to, $field['type']);
540 $op = $this->_params
["{$fieldName}_op"] ??
NULL;
541 if ($op && !($fieldName === "contact_{$recordType}" && ($op === 'nnll' ||
$op === 'nll'))) {
542 $clause = $this->whereClause($field,
544 CRM_Utils_Array
::value("{$fieldName}_value", $this->_params
),
545 CRM_Utils_Array
::value("{$fieldName}_min", $this->_params
),
546 CRM_Utils_Array
::value("{$fieldName}_max", $this->_params
)
548 if ($field['name'] == 'include_case_activities') {
551 if ($fieldName == 'activity_type_id' &&
552 empty($this->_params
['activity_type_id_value'])
554 if (empty($this->_params
['include_case_activities_value'])) {
555 $this->activityTypes
= CRM_Core_PseudoConstant
::activityType(TRUE, FALSE, FALSE, 'label', TRUE);
557 $actTypes = array_flip($this->activityTypes
);
558 $clause = "( {$this->_aliases['civicrm_activity']}.activity_type_id IN (" .
559 implode(',', $actTypes) . ") )";
564 if ($field['name'] == 'current_user') {
565 if (CRM_Utils_Array
::value("{$fieldName}_value", $this->_params
) ==
569 if ($contactID = CRM_Core_Session
::getLoggedInContactID()) {
570 $clause = "{$this->_aliases['civicrm_activity_contact']}.activity_id IN
571 (SELECT activity_id FROM civicrm_activity_contact WHERE contact_id = {$contactID})";
581 if (!empty($clause)) {
582 $clauses[] = $clause;
588 if (empty($clauses)) {
589 $this->_where
.= " ";
592 $this->_where
.= " AND " . implode(' AND ', $clauses);
595 if ($this->_aclWhere
) {
596 $this->_where
.= " AND {$this->_aclWhere} ";
601 * Override group by function.
603 public function groupBy() {
604 $this->_groupBy
= CRM_Contact_BAO_Query
::getGroupByFromSelectColumns($this->_selectClauses
, "{$this->_aliases['civicrm_activity']}.id");
610 * @param string $tableAlias
612 * @throws \CRM_Core_Exception
614 public function buildACLClause($tableAlias = 'contact_a') {
615 //override for ACL( Since Contact may be source
616 //contact/assignee or target also it may be null )
618 if (CRM_Core_Permission
::check('view all contacts')) {
619 $this->_aclFrom
= $this->_aclWhere
= NULL;
623 $contactID = CRM_Core_Session
::getLoggedInContactID();
627 $contactID = CRM_Utils_Type
::escape($contactID, 'Integer');
629 CRM_Contact_BAO_Contact_Permission
::cache($contactID);
631 foreach ($tableAlias as $k => $alias) {
632 $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 ";
635 $this->_aclFrom
= implode(" ", $clauses);
636 $this->_aclWhere
= NULL;
640 * @param int $groupID
644 public function add2group($groupID) {
645 if (CRM_Utils_Array
::value("contact_target_op", $this->_params
) == 'nll') {
646 CRM_Core_Error
::fatal(ts('Current filter criteria didn\'t have any target contact to add to group'));
649 $new_select = 'AS addtogroup_contact_id';
650 $select = str_ireplace('AS civicrm_contact_contact_target_id', $new_select, $this->_select
);
651 $new_having = ' addtogroup_contact_id';
652 $having = str_ireplace(' civicrm_contact_contact_target_id', $new_having, $this->_having
);
654 FROM {$this->temporaryTables['activity_temp_table']['name']} tar
655 GROUP BY civicrm_activity_id $having {$this->_orderBy}";
656 $select = 'AS addtogroup_contact_id';
657 $query = str_ireplace('AS civicrm_contact_contact_target_id', $select, $query);
658 CRM_Core_DAO
::disableFullGroupByMode();
659 $dao = $this->executeReportQuery($query);
660 CRM_Core_DAO
::reenableFullGroupByMode();
663 // Add resulting contacts to group
664 while ($dao->fetch()) {
665 if ($dao->addtogroup_contact_id
) {
666 $contact_id = explode(';', $dao->addtogroup_contact_id
);
667 if ($contact_id[0]) {
668 $contactIDs[$contact_id[0]] = $contact_id[0];
673 if (!empty($contactIDs)) {
674 CRM_Contact_BAO_GroupContact
::addContactsToGroup($contactIDs, $groupID);
675 CRM_Core_Session
::setStatus(ts("Listed contact(s) have been added to the selected group."), ts('Contacts Added'), 'success');
678 CRM_Core_Session
::setStatus(ts("The listed records(s) cannot be added to the group."));
689 public static function formRule($fields, $files, $self) {
691 $config = CRM_Core_Config
::singleton();
692 if (in_array("CiviCase", $config->enableComponents
)) {
693 $componentId = CRM_Core_Component
::getComponentID('CiviCase');
694 $caseActivityTypes = CRM_Core_OptionGroup
::values('activity_type', TRUE, FALSE, FALSE, " AND v.component_id={$componentId}");
695 if (!empty($fields['activity_type_id_value']) && is_array($fields['activity_type_id_value']) && empty($fields['include_case_activities_value'])) {
696 foreach ($fields['activity_type_id_value'] as $activityTypeId) {
697 if (in_array($activityTypeId, $caseActivityTypes)) {
698 $errors['fields'] = ts("Please enable 'Include Case Activities' to filter with Case Activity types.");
711 public function buildQuery($applyLimit = TRUE) {
712 $activityContacts = CRM_Activity_BAO_ActivityContact
::buildOptions('record_type_id', 'validate');
713 $sourceID = CRM_Utils_Array
::key('Activity Source', $activityContacts);
715 //Assign those recordtype to array which have filter operator as 'Is not empty' or 'Is empty'
717 foreach (['target', 'source', 'assignee'] as $type) {
718 if (CRM_Utils_Array
::value("contact_{$type}_op", $this->_params
) ==
719 'nnll' ||
!empty($this->_params
["contact_{$type}_value"])
721 $nullFilters[] = " civicrm_contact_contact_{$type}_id IS NOT NULL ";
723 elseif (CRM_Utils_Array
::value("contact_{$type}_op", $this->_params
) ==
726 $nullFilters[] = " civicrm_contact_contact_{$type}_id IS NULL ";
730 if (!empty($this->_params
['include_case_activities_value'])) {
731 $this->_columns
= self
::addCaseActivityColumns($this->_columns
);
734 // @todo - all this temp table stuff is here because pre 4.4 the activity contact
735 // form did not exist.
736 // Fixing the way the construct method declares them will make all this redundant.
737 // 1. fill temp table with target results
738 $this->buildACLClause(['civicrm_contact_target']);
739 $this->select('target');
741 $this->customDataFrom();
742 $this->where('target');
743 $tempTableName = $this->createTemporaryTable('activity_temp_table', "{$this->_select} {$this->_from} {$this->_where}");
745 // 2. add new columns to hold assignee and source results
746 // fixme: add when required
748 ALTER TABLE $tempTableName
749 MODIFY COLUMN civicrm_contact_contact_target_id VARCHAR(128),
750 ADD COLUMN civicrm_contact_contact_assignee VARCHAR(128),
751 ADD COLUMN civicrm_contact_contact_source VARCHAR(128),
752 ADD COLUMN civicrm_contact_contact_assignee_id VARCHAR(128),
753 ADD COLUMN civicrm_contact_contact_source_id VARCHAR(128),
754 ADD COLUMN civicrm_phone_contact_assignee_phone VARCHAR(128),
755 ADD COLUMN civicrm_phone_contact_source_phone VARCHAR(128),
756 ADD COLUMN civicrm_email_contact_assignee_email VARCHAR(128),
757 ADD COLUMN civicrm_email_contact_source_email VARCHAR(128)";
758 $this->executeReportQuery($tempQuery);
760 // 3. fill temp table with assignee results
761 $this->buildACLClause(['civicrm_contact_assignee']);
762 $this->select('assignee');
763 $this->buildAssigneeFrom();
765 $this->customDataFrom();
766 $this->where('assignee');
767 $insertCols = implode(',', $this->_selectAliases
);
768 $tempQuery = "INSERT INTO $tempTableName ({$insertCols})
770 {$this->_from} {$this->_where}";
771 $this->executeReportQuery($tempQuery);
773 // 4. fill temp table with source results
774 $this->buildACLClause(['civicrm_contact_source']);
775 $this->select('source');
776 $this->buildSourceFrom();
777 $this->customDataFrom();
778 $this->where('source');
779 $insertCols = implode(',', $this->_selectAliases
);
780 $tempQuery = "INSERT INTO $tempTableName ({$insertCols})
782 {$this->_from} {$this->_where}";
783 $this->executeReportQuery($tempQuery);
785 // 5. show final result set from temp table
787 $this->select('final');
789 if (!empty($nullFilters)) {
790 $this->_having
= "HAVING " . implode(' AND ', $nullFilters);
793 foreach ($this->_sections
as $alias => $section) {
794 if (!empty($section) && $section['name'] == 'activity_date_time') {
795 $this->alterSectionHeaderForDateTime($tempTableName, $section['tplField']);
803 $groupByFromSelect = CRM_Contact_BAO_Query
::getGroupByFromSelectColumns($this->_selectClauses
, 'civicrm_activity_id');
805 $this->_where
= " WHERE (1)";
806 $this->buildPermissionClause();
807 if ($this->_aclWhere
) {
808 $this->_where
.= " AND {$this->_aclWhere} ";
812 if (!empty($this->_params
['include_case_activities_value'])) {
813 $caseJoin = "LEFT JOIN civicrm_case_activity {$this->_aliases['civicrm_case_activity']} ON {$this->_aliases['civicrm_activity']}.id = {$this->_aliases['civicrm_case_activity']}.activity_id";
816 $sql = "{$this->_select}
817 FROM $tempTableName tar
818 INNER JOIN civicrm_activity {$this->_aliases['civicrm_activity']} ON {$this->_aliases['civicrm_activity']}.id = tar.civicrm_activity_id
819 INNER JOIN civicrm_activity_contact {$this->_aliases['civicrm_activity_contact']} ON {$this->_aliases['civicrm_activity_contact']}.activity_id = {$this->_aliases['civicrm_activity']}.id
820 AND {$this->_aliases['civicrm_activity_contact']}.record_type_id = {$sourceID}
821 LEFT JOIN civicrm_contact contact_civireport ON contact_civireport.id = {$this->_aliases['civicrm_activity_contact']}.contact_id
823 {$this->_where} {$groupByFromSelect} {$this->_having} {$this->_orderBy} {$this->_limit}";
825 CRM_Utils_Hook
::alterReportVar('sql', $this, $this);
826 $this->addToDeveloperTab($sql);
832 * Override parent to reset value of activity_date.
834 public function beginPostProcessCommon() {
835 if (!empty($this->_resetDateFilter
)) {
836 $this->_formValues
['activity_date_time_relative'] = NULL;
841 * Alter display of rows.
843 * Iterate through the rows retrieved via SQL and make changes for display purposes,
844 * such as rendering contacts as links.
847 * Rows generated by SQL, with an array for each row.
849 * @throws \CRM_Core_Exception
851 public function alterDisplay(&$rows) {
853 $activityType = CRM_Core_PseudoConstant
::activityType(TRUE, TRUE, FALSE, 'label', TRUE);
854 $activityStatus = CRM_Core_PseudoConstant
::activityStatus();
855 $priority = CRM_Core_PseudoConstant
::get('CRM_Activity_DAO_Activity', 'priority_id');
858 // Would we ever want to retrieve from the form controller??
859 $form = $this->noController ?
NULL : $this;
860 $context = CRM_Utils_Request
::retrieve('context', 'Alphanumeric', $form, FALSE, 'report');
863 if (CRM_Core_Permission
::check('access CiviCRM')) {
865 $onHover = ts('View Contact Summary for this Contact');
866 $onHoverAct = ts('View Activity Record');
868 foreach ($rows as $rowNum => $row) {
869 // if we have an activity type, format the View Activity link for use in various columns
871 array_key_exists('civicrm_activity_activity_type_id', $row)
873 // Check for target contact id(s) and use the first contact id in that list for view activity link if found,
874 // else use source contact id
875 if (!empty($rows[$rowNum]['civicrm_contact_contact_target_id'])) {
876 $targets = explode(';', $rows[$rowNum]['civicrm_contact_contact_target_id']);
880 $cid = $rows[$rowNum]['civicrm_contact_contact_source_id'];
883 if (empty($this->_params
['include_case_activities_value']) ||
empty($rows[$rowNum]['civicrm_case_activity_case_id'])) {
884 // Generate a "view activity" link
885 $actActionLinks = CRM_Activity_Selector_Activity
::actionLinks($row['civicrm_activity_activity_type_id'],
886 CRM_Utils_Array
::value('civicrm_activity_source_record_id', $rows[$rowNum]),
888 $rows[$rowNum]['civicrm_activity_id']
892 'id' => $rows[$rowNum]['civicrm_activity_id'],
896 $actUrl = CRM_Utils_System
::url($actActionLinks[CRM_Core_Action
::VIEW
]['url'],
897 CRM_Core_Action
::replace($actActionLinks[CRM_Core_Action
::VIEW
]['qs'], $actLinkValues), TRUE
901 // Generate a "view case activity" link
902 $caseActionLinks = CRM_Case_Selector_Search
::actionLinks();
904 'aid' => $rows[$rowNum]['civicrm_activity_id'],
905 'caseid' => $rows[$rowNum]['civicrm_case_activity_case_id'],
909 $actUrl = CRM_Utils_System
::url($caseActionLinks[CRM_Core_Action
::VIEW
]['url'],
910 CRM_Core_Action
::replace($caseActionLinks[CRM_Core_Action
::VIEW
]['qs'], $caseLinkValues), TRUE
915 if (array_key_exists('civicrm_contact_contact_source', $row)) {
916 if ($value = $row['civicrm_contact_contact_source_id']) {
918 $url = CRM_Utils_System
::url("civicrm/contact/view",
919 'reset=1&cid=' . $value,
922 $rows[$rowNum]['civicrm_contact_contact_source_link'] = $url;
923 $rows[$rowNum]['civicrm_contact_contact_source_hover'] = $onHover;
929 if (array_key_exists('civicrm_contact_contact_assignee', $row)) {
930 $assigneeNames = explode(';', $row['civicrm_contact_contact_assignee']);
931 if ($value = $row['civicrm_contact_contact_assignee_id']) {
932 $assigneeContactIds = explode(';', $value);
935 foreach ($assigneeContactIds as $id => $value) {
936 if (isset($value) && isset($assigneeNames[$id])) {
937 $url = CRM_Utils_System
::url("civicrm/contact/view",
938 'reset=1&cid=' . $value,
941 $link[] = "<a title='" . $onHover . "' href='" . $url .
942 "'>{$assigneeNames[$id]}</a>";
945 $rows[$rowNum]['civicrm_contact_contact_assignee'] = implode('; ', $link);
951 if (array_key_exists('civicrm_contact_contact_target', $row)) {
952 $targetNames = explode(';', $row['civicrm_contact_contact_target']);
953 if ($value = $row['civicrm_contact_contact_target_id']) {
954 $targetContactIds = explode(';', $value);
957 foreach ($targetContactIds as $id => $value) {
958 if (isset($value) && isset($targetNames[$id])) {
959 $url = CRM_Utils_System
::url("civicrm/contact/view",
960 'reset=1&cid=' . $value,
963 $link[] = "<a title='" . $onHover . "' href='" . $url .
964 "'>{$targetNames[$id]}</a>";
967 $rows[$rowNum]['civicrm_contact_contact_target'] = implode('; ', $link);
973 if (array_key_exists('civicrm_activity_activity_type_id', $row)) {
974 if ($value = $row['civicrm_activity_activity_type_id']) {
975 $rows[$rowNum]['civicrm_activity_activity_type_id'] = $activityType[$value];
977 $rows[$rowNum]['civicrm_activity_activity_type_id_link'] = $actUrl;
978 $rows[$rowNum]['civicrm_activity_activity_type_id_hover'] = $onHoverAct;
984 if (array_key_exists('civicrm_activity_status_id', $row)) {
985 if ($value = $row['civicrm_activity_status_id']) {
986 $rows[$rowNum]['civicrm_activity_status_id'] = $activityStatus[$value];
991 if (array_key_exists('civicrm_activity_priority_id', $row)) {
992 if ($value = $row['civicrm_activity_priority_id']) {
993 $rows[$rowNum]['civicrm_activity_priority_id'] = $priority[$value];
998 if (array_key_exists('civicrm_activity_details', $row) && $this->_outputMode
== 'html') {
999 if ($value = $row['civicrm_activity_details']) {
1000 $fullDetails = $rows[$rowNum]['civicrm_activity_details'];
1001 $rows[$rowNum]['civicrm_activity_details'] = substr($fullDetails, 0, strrpos(substr($fullDetails, 0, 80), ' '));
1003 $rows[$rowNum]['civicrm_activity_details'] .= " <a href='{$actUrl}' title='{$onHoverAct}'>(more)</a>";
1009 if (array_key_exists('civicrm_activity_campaign_id', $row)) {
1010 if ($value = $row['civicrm_activity_campaign_id']) {
1011 $rows[$rowNum]['civicrm_activity_campaign_id'] = $this->campaigns
[$value];
1016 if (array_key_exists('civicrm_activity_engagement_level', $row)) {
1017 if ($value = $row['civicrm_activity_engagement_level']) {
1018 $rows[$rowNum]['civicrm_activity_engagement_level'] = $this->engagementLevels
[$value];
1023 if (array_key_exists('civicrm_activity_activity_date_time', $row) &&
1024 array_key_exists('civicrm_activity_status_id', $row)
1026 if (CRM_Utils_Date
::overdue($rows[$rowNum]['civicrm_activity_activity_date_time']) &&
1027 $activityStatus[$row['civicrm_activity_status_id']] != 'Completed'
1029 $rows[$rowNum]['class'] = "status-overdue";
1034 $entryFound = $this->alterDisplayAddressFields($row, $rows, $rowNum, 'activity', 'List all activities for this', ';') ?
TRUE : $entryFound;
1042 public function sectionTotals() {
1043 // Reports using order_bys with sections must populate $this->_selectAliases in select() method.
1044 if (empty($this->_selectAliases
)) {
1048 if (!empty($this->_sections
)) {
1049 // pull section aliases out of $this->_sections
1050 $sectionAliases = array_keys($this->_sections
);
1053 foreach (array_merge($sectionAliases, $this->_selectAliases
) as $alias) {
1054 $ifnulls[] = "ifnull($alias, '') as $alias";
1056 $this->_select
= "SELECT " . implode(", ", $ifnulls);
1057 $this->_select
= CRM_Contact_BAO_Query
::appendAnyValueToSelect($ifnulls, $sectionAliases);
1059 $query = $this->_select
.
1060 ", count(DISTINCT civicrm_activity_id) as ct from {$this->temporaryTables['activity_temp_table']['name']} group by " .
1061 implode(", ", $sectionAliases);
1063 // initialize array of total counts
1065 $dao = $this->executeReportQuery($query);
1066 while ($dao->fetch()) {
1067 // let $this->_alterDisplay translate any integer ids to human-readable values.
1068 $rows[0] = $dao->toArray();
1069 $this->alterDisplay($rows);
1072 // add totals for all permutations of section values
1075 $aliasCount = count($sectionAliases);
1076 foreach ($sectionAliases as $alias) {
1077 $values[] = $row[$alias];
1078 $key = implode(CRM_Core_DAO
::VALUE_SEPARATOR
, $values);
1079 if ($i == $aliasCount) {
1080 // the last alias is the lowest-level section header; use count as-is
1081 $totals[$key] = $dao->ct
;
1084 // other aliases are higher level; roll count into their total
1085 $totals[$key] +
= $dao->ct
;
1089 $this->assign('sectionTotals', $totals);
1094 * @todo remove this function & declare the 3 contact tables separately
1096 * (Currently the construct method incorrectly melds them - this is an interim
1097 * refactor in order to get this under ReportTemplateTests)
1099 protected function buildAssigneeFrom() {
1100 $this->buildFrom('assignee');
1104 * @todo remove this function & declare the 3 contact tables separately
1106 * (Currently the construct method incorrectly melds them - this is an interim
1107 * refactor in order to get this under ReportTemplateTests)
1109 protected function buildSourceFrom() {
1110 $this->buildFrom('source');
1114 * Shared function to build the from clause
1116 * @param string $recordType (one of 'source', 'activity', 'target')
1118 protected function buildFrom($recordType) {
1119 $activityContacts = CRM_Activity_BAO_ActivityContact
::buildOptions('record_type_id', 'validate');
1120 switch ($recordType) {
1122 $recordTypeID = CRM_Utils_Array
::key('Activity Targets', $activityContacts);
1126 $recordTypeID = CRM_Utils_Array
::key('Activity Source', $activityContacts);
1130 $recordTypeID = CRM_Utils_Array
::key('Activity Assignees', $activityContacts);
1136 FROM civicrm_activity {$this->_aliases['civicrm_activity']}
1137 INNER JOIN civicrm_activity_contact {$this->_aliases['civicrm_activity_contact']}
1138 ON {$this->_aliases['civicrm_activity']}.id = {$this->_aliases['civicrm_activity_contact']}.activity_id AND
1139 {$this->_aliases['civicrm_activity_contact']}.record_type_id = {$recordTypeID}
1140 INNER JOIN civicrm_contact civicrm_contact_{$recordType}
1141 ON {$this->_aliases['civicrm_activity_contact']}.contact_id = civicrm_contact_{$recordType}.id
1143 if (!empty($this->_params
['include_case_activities_value'])) {
1145 LEFT JOIN civicrm_case_activity {$this->_aliases['civicrm_case_activity']}
1146 ON {$this->_aliases['civicrm_case_activity']}.activity_id = {$this->_aliases['civicrm_activity']}.id";
1149 if ($this->isTableSelected('civicrm_email')) {
1151 LEFT JOIN civicrm_email civicrm_email_{$recordType}
1152 ON {$this->_aliases['civicrm_activity_contact']}.contact_id = civicrm_email_{$recordType}.contact_id AND
1153 civicrm_email_{$recordType}.is_primary = 1";
1156 if ($this->isTableSelected('civicrm_phone')) {
1158 LEFT JOIN civicrm_phone civicrm_phone_{$recordType}
1159 ON {$this->_aliases['civicrm_activity_contact']}.contact_id = civicrm_phone_{$recordType}.contact_id AND
1160 civicrm_phone_{$recordType}.is_primary = 1 ";
1162 $this->_aliases
['civicrm_contact'] = "civicrm_contact_{$recordType}";
1164 $this->joinAddressFromContact();