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 = [
24 protected $_nonDisplayFields = [];
27 * This report has not been optimised for group filtering.
29 * The functionality for group filtering has been improved but not
30 * all reports have been adjusted to take care of it. This report has not
31 * and will run an inefficient query until fixed.
37 protected $groupFilterNotOptimised = TRUE;
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 $config = CRM_Core_Config
::singleton();
50 $campaignEnabled = in_array("CiviCampaign", $config->enableComponents
);
51 $caseEnabled = in_array("CiviCase", $config->enableComponents
);
52 if ($campaignEnabled) {
53 $this->engagementLevels
= CRM_Campaign_PseudoConstant
::engagementLevel();
56 $components = CRM_Core_Component
::getEnabledComponents();
57 foreach ($components as $componentName => $componentInfo) {
58 // CRM-19201: Add support for reporting CiviCampaign activities
59 // For CiviCase, "access all cases and activities" is required here
60 // rather than "access my cases and activities" to prevent those with
61 // only the later permission from seeing a list of all cases which might
62 // present a privacy issue.
63 if (CRM_Core_Permission
::access($componentName, TRUE, TRUE)) {
64 $accessAllowed[] = $componentInfo->componentID
;
69 if (!empty($accessAllowed)) {
70 $include = 'OR v.component_id IN (' . implode(', ', $accessAllowed) . ')';
72 $condition = " AND ( v.component_id IS NULL {$include} )";
73 $this->activityTypes
= CRM_Core_OptionGroup
::values('activity_type', FALSE, FALSE, FALSE, $condition);
74 asort($this->activityTypes
);
76 // @todo split the 3 different contact tables into their own array items.
77 // this will massively simplify the needs of this report.
79 'civicrm_contact' => [
80 'dao' => 'CRM_Contact_DAO_Contact',
83 'name' => 'sort_name',
84 'title' => ts('Source Name'),
85 'alias' => 'civicrm_contact_source',
88 'contact_assignee' => [
89 'name' => 'sort_name',
90 'title' => ts('Assignee Name'),
91 'alias' => 'civicrm_contact_assignee',
92 'dbAlias' => "civicrm_contact_assignee.sort_name",
96 'name' => 'sort_name',
97 'title' => ts('Target Name'),
98 'alias' => 'civicrm_contact_target',
99 'dbAlias' => "civicrm_contact_target.sort_name",
102 'contact_source_id' => [
104 'alias' => 'civicrm_contact_source',
105 'dbAlias' => "civicrm_contact_source.id",
106 'no_display' => TRUE,
110 'contact_assignee_id' => [
112 'alias' => 'civicrm_contact_assignee',
113 'dbAlias' => "civicrm_contact_assignee.id",
114 'no_display' => TRUE,
118 'contact_target_id' => [
120 'alias' => 'civicrm_contact_target',
121 'dbAlias' => "civicrm_contact_target.id",
122 'no_display' => TRUE,
128 'contact_source' => [
129 'name' => 'sort_name',
130 'alias' => 'civicrm_contact_source',
131 'title' => ts('Source Name'),
132 'operator' => 'like',
133 'type' => CRM_Report_Form
::OP_STRING
,
135 'contact_assignee' => [
136 'name' => 'sort_name',
137 'alias' => 'civicrm_contact_assignee',
138 'title' => ts('Assignee Name'),
139 'operator' => 'like',
140 'type' => CRM_Report_Form
::OP_STRING
,
142 'contact_target' => [
143 'name' => 'sort_name',
144 'alias' => 'civicrm_contact_target',
145 'title' => ts('Target Name'),
146 'operator' => 'like',
147 'type' => CRM_Report_Form
::OP_STRING
,
150 'name' => 'current_user',
151 'title' => ts('Limit To Current User'),
152 'type' => CRM_Utils_Type
::T_INT
,
153 'operatorType' => CRM_Report_Form
::OP_SELECT
,
154 'options' => ['0' => ts('No'), '1' => ts('Yes')],
157 'grouping' => 'contact-fields',
160 'dao' => 'CRM_Core_DAO_Email',
162 'contact_source_email' => [
164 'title' => ts('Source Email'),
165 'alias' => 'civicrm_email_source',
167 'contact_assignee_email' => [
169 'title' => ts('Assignee Email'),
170 'alias' => 'civicrm_email_assignee',
172 'contact_target_email' => [
174 'title' => ts('Target Email'),
175 'alias' => 'civicrm_email_target',
179 'source_contact_email' => [
181 'title' => ts('Source Email'),
182 'dbAlias' => 'civicrm_email_contact_source_email',
187 'dao' => 'CRM_Core_DAO_Phone',
189 'contact_source_phone' => [
191 'title' => ts('Source Phone'),
192 'alias' => 'civicrm_phone_source',
194 'contact_assignee_phone' => [
196 'title' => ts('Assignee Phone'),
197 'alias' => 'civicrm_phone_assignee',
199 'contact_target_phone' => [
201 'title' => ts('Target Phone'),
202 'alias' => 'civicrm_phone_target',
206 'civicrm_activity' => [
207 'dao' => 'CRM_Activity_DAO_Activity',
210 'no_display' => TRUE,
211 'title' => ts('Activity ID'),
214 'source_record_id' => [
215 'no_display' => TRUE,
218 'activity_type_id' => [
219 'title' => ts('Activity Type'),
221 'type' => CRM_Utils_Type
::T_STRING
,
223 'activity_subject' => [
224 'title' => ts('Subject'),
227 'activity_date_time' => [
228 'title' => ts('Activity Date'),
232 'title' => ts('Activity Status'),
234 'type' => CRM_Utils_Type
::T_STRING
,
237 'title' => ts('Duration'),
238 'type' => CRM_Utils_Type
::T_INT
,
241 'title' => ts('Location'),
242 'type' => CRM_Utils_Type
::T_STRING
,
245 'title' => ts('Activity Details'),
248 'title' => ts('Priority'),
250 'type' => CRM_Utils_Type
::T_STRING
,
254 'activity_date_time' => [
255 'default' => 'this.month',
256 'operatorType' => CRM_Report_Form
::OP_DATE
,
258 'activity_subject' => ['title' => ts('Activity Subject')],
259 'activity_type_id' => [
260 'title' => ts('Activity Type'),
261 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
262 'options' => $this->activityTypes
,
265 'title' => ts('Activity Status'),
266 'type' => CRM_Utils_Type
::T_STRING
,
267 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
268 'options' => CRM_Core_PseudoConstant
::activityStatus(),
271 'title' => ts('Location'),
272 'type' => CRM_Utils_Type
::T_TEXT
,
275 'title' => ts('Activity Details'),
276 'type' => CRM_Utils_Type
::T_TEXT
,
279 'title' => ts('Activity Priority'),
280 'type' => CRM_Utils_Type
::T_STRING
,
281 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
282 'options' => CRM_Core_PseudoConstant
::get('CRM_Activity_DAO_Activity', 'priority_id'),
286 'activity_date_time' => [
287 'title' => ts('Activity Date'),
288 'default_weight' => '1',
289 'dbAlias' => 'civicrm_activity_activity_date_time',
291 'activity_type_id' => [
292 'title' => ts('Activity Type'),
293 'default_weight' => '2',
294 'dbAlias' => 'field(civicrm_activity_activity_type_id, ' . implode(', ', array_keys($this->activityTypes
)) . ')',
297 'grouping' => 'activity-fields',
298 'alias' => 'activity',
300 // Hack to get $this->_alias populated for the table.
301 'civicrm_activity_contact' => [
302 'dao' => 'CRM_Activity_DAO_ActivityContact',
305 ] +
$this->addressFields(TRUE);
307 if ($caseEnabled && CRM_Core_Permission
::check('access all cases and activities')) {
308 $this->_columns
['civicrm_activity']['filters']['include_case_activities'] = [
309 'name' => 'include_case_activities',
310 'title' => ts('Include Case Activities'),
311 'type' => CRM_Utils_Type
::T_INT
,
312 'operatorType' => CRM_Report_Form
::OP_SELECT
,
313 'options' => ['0' => ts('No'), '1' => ts('Yes')],
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');
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';
348 parent
::__construct();
352 * Adding address fields with dbAlias for order clause.
354 * @param bool $orderBy
359 public function addressFields($orderBy = FALSE) {
360 $address = parent
::addAddressFields(FALSE, TRUE);
362 foreach ($address['civicrm_address']['order_bys'] as $fieldName => $field) {
363 $address['civicrm_address']['order_bys'][$fieldName]['dbAlias'] = "civicrm_address_{$fieldName}";
370 * Build select clause.
372 * @todo get rid of $recordType param. It's only because 3 separate contact tables
373 * are mis-declared as one that we need it.
375 * @param null $recordType deprecated
376 * Parameter to hack around the bad decision made in construct to misrepresent
377 * different tables as the same table.
379 public function select($recordType = 'target') {
380 if (!array_key_exists("contact_{$recordType}", $this->_params
['fields']) &&
381 $recordType != 'final'
383 $this->_nonDisplayFields
[] = "civicrm_contact_contact_{$recordType}";
387 if ($recordType == 'final' && !empty($this->_nonDisplayFields
)) {
388 foreach ($this->_nonDisplayFields
as $fieldName) {
389 unset($this->_columnHeaders
[$fieldName]);
393 if (empty($this->_selectAliasesTotal
)) {
394 $this->_selectAliasesTotal
= $this->_selectAliases
;
398 if ($recordType == 'target') {
399 // @todo - fix up the way the tables are declared in construct & remove this.
400 foreach ($this->_selectClauses
as $key => $clause) {
401 if (strstr($clause, 'civicrm_contact_assignee.') ||
402 strstr($clause, 'civicrm_contact_source.') ||
403 strstr($clause, 'civicrm_email_assignee.') ||
404 strstr($clause, 'civicrm_email_source.') ||
405 strstr($clause, 'civicrm_phone_assignee.') ||
406 strstr($clause, 'civicrm_phone_source.')
408 $removeKeys[] = $key;
409 unset($this->_selectClauses
[$key]);
413 elseif ($recordType == 'assignee') {
414 // @todo - fix up the way the tables are declared in construct & remove this.
415 foreach ($this->_selectClauses
as $key => $clause) {
416 if (strstr($clause, 'civicrm_contact_target.') ||
417 strstr($clause, 'civicrm_contact_source.') ||
418 strstr($clause, 'civicrm_email_target.') ||
419 strstr($clause, 'civicrm_email_source.') ||
420 strstr($clause, 'civicrm_phone_target.') ||
421 strstr($clause, 'civicrm_phone_source.') ||
422 strstr($clause, 'civicrm_address_')
424 $removeKeys[] = $key;
425 unset($this->_selectClauses
[$key]);
429 elseif ($recordType == 'source') {
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_assignee.') ||
434 strstr($clause, 'civicrm_email_target.') ||
435 strstr($clause, 'civicrm_email_assignee.') ||
436 strstr($clause, 'civicrm_phone_target.') ||
437 strstr($clause, 'civicrm_phone_assignee.') ||
438 strstr($clause, 'civicrm_address_')
440 $removeKeys[] = $key;
441 unset($this->_selectClauses
[$key]);
445 elseif ($recordType == 'final') {
446 $this->_selectClauses
= $this->_selectAliasesTotal
;
447 foreach ($this->_selectClauses
as $key => $clause) {
448 // @todo - fix up the way the tables are declared in construct & remove this.
449 if (strstr($clause, 'civicrm_contact_contact_target') ||
450 strstr($clause, 'civicrm_contact_contact_assignee') ||
451 strstr($clause, 'civicrm_contact_contact_source') ||
452 strstr($clause, 'civicrm_phone_contact_source_phone') ||
453 strstr($clause, 'civicrm_phone_contact_assignee_phone') ||
454 strstr($clause, 'civicrm_email_contact_source_email') ||
455 strstr($clause, 'civicrm_email_contact_assignee_email') ||
456 strstr($clause, 'civicrm_email_contact_target_email') ||
457 strstr($clause, 'civicrm_phone_contact_target_phone') ||
458 strstr($clause, 'civicrm_address_')
460 $this->_selectClauses
[$key] = "GROUP_CONCAT(DISTINCT $clause SEPARATOR ';') as $clause";
466 foreach ($removeKeys as $key) {
467 unset($this->_selectAliases
[$key]);
470 if ($recordType == 'target') {
471 foreach ($this->_columns
['civicrm_address']['order_bys'] as $fieldName => $field) {
472 $orderByFld = $this->_columns
['civicrm_address']['order_bys'][$fieldName];
473 $fldInfo = $this->_columns
['civicrm_address']['fields'][$fieldName];
474 $this->_selectAliases
[] = $orderByFld['dbAlias'];
475 $this->_selectClauses
[] = "{$fldInfo['dbAlias']} as {$orderByFld['dbAlias']}";
477 $this->_selectAliases
= array_unique($this->_selectAliases
);
478 $this->_selectClauses
= array_unique($this->_selectClauses
);
480 $this->_select
= "SELECT " . implode(', ', $this->_selectClauses
) . " ";
486 * @todo remove this function & declare the 3 contact tables separately
488 public function from() {
489 $activityContacts = CRM_Activity_BAO_ActivityContact
::buildOptions('record_type_id', 'validate');
490 $targetID = CRM_Utils_Array
::key('Activity Targets', $activityContacts);
493 FROM civicrm_activity {$this->_aliases['civicrm_activity']}
494 INNER JOIN civicrm_activity_contact {$this->_aliases['civicrm_activity_contact']}
495 ON {$this->_aliases['civicrm_activity']}.id = {$this->_aliases['civicrm_activity_contact']}.activity_id AND
496 {$this->_aliases['civicrm_activity_contact']}.record_type_id = {$targetID}
497 INNER JOIN civicrm_contact civicrm_contact_target
498 ON {$this->_aliases['civicrm_activity_contact']}.contact_id = civicrm_contact_target.id
501 if ($this->isTableSelected('civicrm_email')) {
503 LEFT JOIN civicrm_email civicrm_email_target
504 ON {$this->_aliases['civicrm_activity_contact']}.contact_id = civicrm_email_target.contact_id AND
505 civicrm_email_target.is_primary = 1";
508 if ($this->isTableSelected('civicrm_phone')) {
510 LEFT JOIN civicrm_phone civicrm_phone_target
511 ON {$this->_aliases['civicrm_activity_contact']}.contact_id = civicrm_phone_target.contact_id AND
512 civicrm_phone_target.is_primary = 1 ";
514 $this->_aliases
['civicrm_contact'] = 'civicrm_contact_target';
516 $this->joinAddressFromContact();
520 * Build where clause.
522 * @todo get rid of $recordType param. It's only because 3 separate contact tables
523 * are mis-declared as one that we need it.
525 * @param string $recordType
527 public function where($recordType = NULL) {
528 $this->_where
= " WHERE {$this->_aliases['civicrm_activity']}.is_test = 0 AND
529 {$this->_aliases['civicrm_activity']}.is_deleted = 0 AND
530 {$this->_aliases['civicrm_activity']}.is_current_revision = 1";
533 foreach ($this->_columns
as $tableName => $table) {
534 if (array_key_exists('filters', $table)) {
536 foreach ($table['filters'] as $fieldName => $field) {
538 if ($fieldName != 'contact_' . $recordType &&
539 (strstr($fieldName, '_target') ||
540 strstr($fieldName, '_assignee') ||
541 strstr($fieldName, '_source')
546 if (CRM_Utils_Array
::value('type', $field) & CRM_Utils_Type
::T_DATE
) {
547 $relative = CRM_Utils_Array
::value("{$fieldName}_relative", $this->_params
);
548 $from = CRM_Utils_Array
::value("{$fieldName}_from", $this->_params
);
549 $to = CRM_Utils_Array
::value("{$fieldName}_to", $this->_params
);
551 $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']);
554 $op = CRM_Utils_Array
::value("{$fieldName}_op", $this->_params
);
555 if ($op && ($op != 'nnll' && $op != 'nll')) {
556 $clause = $this->whereClause($field,
558 CRM_Utils_Array
::value("{$fieldName}_value", $this->_params
),
559 CRM_Utils_Array
::value("{$fieldName}_min", $this->_params
),
560 CRM_Utils_Array
::value("{$fieldName}_max", $this->_params
)
562 if ($field['name'] == 'include_case_activities') {
565 if ($fieldName == 'activity_type_id' &&
566 empty($this->_params
['activity_type_id_value'])
568 if (empty($this->_params
['include_case_activities_value'])) {
569 $this->activityTypes
= CRM_Core_PseudoConstant
::activityType(TRUE, FALSE, FALSE, 'label', TRUE);
571 $actTypes = array_flip($this->activityTypes
);
572 $clause = "( {$this->_aliases['civicrm_activity']}.activity_type_id IN (" .
573 implode(',', $actTypes) . ") )";
578 if ($field['name'] == 'current_user') {
579 if (CRM_Utils_Array
::value("{$fieldName}_value", $this->_params
) ==
583 $session = CRM_Core_Session
::singleton();
584 if ($contactID = $session->get('userID')) {
585 $clause = "{$this->_aliases['civicrm_activity_contact']}.activity_id IN
586 (SELECT activity_id FROM civicrm_activity_contact WHERE contact_id = {$contactID})";
596 if (!empty($clause)) {
597 $clauses[] = $clause;
603 if (empty($clauses)) {
604 $this->_where
.= " ";
607 $this->_where
.= " AND " . implode(' AND ', $clauses);
610 if ($this->_aclWhere
) {
611 $this->_where
.= " AND {$this->_aclWhere} ";
616 * Override group by function.
618 public function groupBy() {
619 $this->_groupBy
= CRM_Contact_BAO_Query
::getGroupByFromSelectColumns($this->_selectClauses
, "{$this->_aliases['civicrm_activity']}.id");
625 * @param string $tableAlias
627 public function buildACLClause($tableAlias = 'contact_a') {
628 //override for ACL( Since Contact may be source
629 //contact/assignee or target also it may be null )
631 if (CRM_Core_Permission
::check('view all contacts')) {
632 $this->_aclFrom
= $this->_aclWhere
= NULL;
636 $session = CRM_Core_Session
::singleton();
637 $contactID = $session->get('userID');
641 $contactID = CRM_Utils_Type
::escape($contactID, 'Integer');
643 CRM_Contact_BAO_Contact_Permission
::cache($contactID);
645 foreach ($tableAlias as $k => $alias) {
646 $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 ";
649 $this->_aclFrom
= implode(" ", $clauses);
650 $this->_aclWhere
= NULL;
654 * @param int $groupID
658 public function add2group($groupID) {
659 if (CRM_Utils_Array
::value("contact_target_op", $this->_params
) == 'nll') {
660 CRM_Core_Error
::fatal(ts('Current filter criteria didn\'t have any target contact to add to group'));
663 $new_select = 'AS addtogroup_contact_id';
664 $select = str_ireplace('AS civicrm_contact_contact_target_id', $new_select, $this->_select
);
665 $new_having = ' addtogroup_contact_id';
666 $having = str_ireplace(' civicrm_contact_contact_target_id', $new_having, $this->_having
);
668 FROM {$this->temporaryTables['activity_temp_table']['name']} tar
669 GROUP BY civicrm_activity_id $having {$this->_orderBy}";
670 $select = 'AS addtogroup_contact_id';
671 $query = str_ireplace('AS civicrm_contact_contact_target_id', $select, $query);
672 CRM_Core_DAO
::disableFullGroupByMode();
673 $dao = $this->executeReportQuery($query);
674 CRM_Core_DAO
::reenableFullGroupByMode();
677 // Add resulting contacts to group
678 while ($dao->fetch()) {
679 if ($dao->addtogroup_contact_id
) {
680 $contact_id = explode(';', $dao->addtogroup_contact_id
);
681 if ($contact_id[0]) {
682 $contactIDs[$contact_id[0]] = $contact_id[0];
687 if (!empty($contactIDs)) {
688 CRM_Contact_BAO_GroupContact
::addContactsToGroup($contactIDs, $groupID);
689 CRM_Core_Session
::setStatus(ts("Listed contact(s) have been added to the selected group."), ts('Contacts Added'), 'success');
692 CRM_Core_Session
::setStatus(ts("The listed records(s) cannot be added to the group."));
703 public static function formRule($fields, $files, $self) {
705 $config = CRM_Core_Config
::singleton();
706 if (in_array("CiviCase", $config->enableComponents
)) {
707 $componentId = CRM_Core_Component
::getComponentID('CiviCase');
708 $caseActivityTypes = CRM_Core_OptionGroup
::values('activity_type', TRUE, FALSE, FALSE, " AND v.component_id={$componentId}");
709 if (!empty($fields['activity_type_id_value']) && is_array($fields['activity_type_id_value']) && empty($fields['include_case_activities_value'])) {
710 foreach ($fields['activity_type_id_value'] as $activityTypeId) {
711 if (in_array($activityTypeId, $caseActivityTypes)) {
712 $errors['fields'] = ts("Please enable 'Include Case Activities' to filter with Case Activity types.");
725 public function buildQuery($applyLimit = TRUE) {
726 $activityContacts = CRM_Activity_BAO_ActivityContact
::buildOptions('record_type_id', 'validate');
727 $sourceID = CRM_Utils_Array
::key('Activity Source', $activityContacts);
729 //Assign those recordtype to array which have filter operator as 'Is not empty' or 'Is empty'
731 foreach (['target', 'source', 'assignee'] as $type) {
732 if (CRM_Utils_Array
::value("contact_{$type}_op", $this->_params
) ==
733 'nnll' ||
!empty($this->_params
["contact_{$type}_value"])
735 $nullFilters[] = " civicrm_contact_contact_{$type}_id IS NOT NULL ";
737 elseif (CRM_Utils_Array
::value("contact_{$type}_op", $this->_params
) ==
740 $nullFilters[] = " civicrm_contact_contact_{$type}_id IS NULL ";
744 // @todo - all this temp table stuff is here because pre 4.4 the activity contact
745 // form did not exist.
746 // Fixing the way the construct method declares them will make all this redundant.
747 // 1. fill temp table with target results
748 $this->buildACLClause(['civicrm_contact_target']);
749 $this->select('target');
751 $this->customDataFrom();
752 $this->where('target');
753 $tempTableName = $this->createTemporaryTable('activity_temp_table', "{$this->_select} {$this->_from} {$this->_where}");
755 // 2. add new columns to hold assignee and source results
756 // fixme: add when required
758 ALTER TABLE $tempTableName
759 MODIFY COLUMN civicrm_contact_contact_target_id VARCHAR(128),
760 ADD COLUMN civicrm_contact_contact_assignee VARCHAR(128),
761 ADD COLUMN civicrm_contact_contact_source VARCHAR(128),
762 ADD COLUMN civicrm_contact_contact_assignee_id VARCHAR(128),
763 ADD COLUMN civicrm_contact_contact_source_id VARCHAR(128),
764 ADD COLUMN civicrm_phone_contact_assignee_phone VARCHAR(128),
765 ADD COLUMN civicrm_phone_contact_source_phone VARCHAR(128),
766 ADD COLUMN civicrm_email_contact_assignee_email VARCHAR(128),
767 ADD COLUMN civicrm_email_contact_source_email VARCHAR(128)";
768 $this->executeReportQuery($tempQuery);
770 // 3. fill temp table with assignee results
771 $this->buildACLClause(['civicrm_contact_assignee']);
772 $this->select('assignee');
773 $this->buildAssigneeFrom();
775 $this->customDataFrom();
776 $this->where('assignee');
777 $insertCols = implode(',', $this->_selectAliases
);
778 $tempQuery = "INSERT INTO $tempTableName ({$insertCols})
780 {$this->_from} {$this->_where}";
781 $this->executeReportQuery($tempQuery);
783 // 4. fill temp table with source results
784 $this->buildACLClause(['civicrm_contact_source']);
785 $this->select('source');
786 $this->buildSourceFrom();
787 $this->customDataFrom();
788 $this->where('source');
789 $insertCols = implode(',', $this->_selectAliases
);
790 $tempQuery = "INSERT INTO $tempTableName ({$insertCols})
792 {$this->_from} {$this->_where}";
793 $this->executeReportQuery($tempQuery);
795 // 5. show final result set from temp table
797 $this->select('final');
799 if (!empty($nullFilters)) {
800 $this->_having
= "HAVING " . implode(' AND ', $nullFilters);
803 foreach ($this->_sections
as $alias => $section) {
804 if (!empty($section) && $section['name'] == 'activity_date_time') {
805 $this->alterSectionHeaderForDateTime($tempTableName, $section['tplField']);
813 $groupByFromSelect = CRM_Contact_BAO_Query
::getGroupByFromSelectColumns($this->_selectClauses
, 'civicrm_activity_id');
815 $this->_where
= " WHERE (1)";
816 $this->buildPermissionClause();
817 if ($this->_aclWhere
) {
818 $this->_where
.= " AND {$this->_aclWhere} ";
821 $sql = "{$this->_select}
822 FROM $tempTableName tar
823 INNER JOIN civicrm_activity {$this->_aliases['civicrm_activity']} ON {$this->_aliases['civicrm_activity']}.id = tar.civicrm_activity_id
824 INNER JOIN civicrm_activity_contact {$this->_aliases['civicrm_activity_contact']} ON {$this->_aliases['civicrm_activity_contact']}.activity_id = {$this->_aliases['civicrm_activity']}.id
825 AND {$this->_aliases['civicrm_activity_contact']}.record_type_id = {$sourceID}
826 LEFT JOIN civicrm_contact contact_civireport ON contact_civireport.id = {$this->_aliases['civicrm_activity_contact']}.contact_id
827 {$this->_where} {$groupByFromSelect} {$this->_having} {$this->_orderBy} {$this->_limit}";
829 CRM_Utils_Hook
::alterReportVar('sql', $this, $this);
830 $this->addToDeveloperTab($sql);
835 public function postProcess() {
836 //reset value of activity_date
837 if (!empty($this->_resetDateFilter
)) {
838 $this->_formValues
["activity_date_time_relative"] = NULL;
841 $this->beginPostProcess();
842 $sql = $this->buildQuery(TRUE);
843 $this->buildRows($sql, $rows);
845 // format result set.
846 $this->formatDisplay($rows);
848 // assign variables to templates
849 $this->doTemplateAssignment($rows);
851 // do print / pdf / instance stuff if needed
852 $this->endPostProcess($rows);
856 * Alter display of rows.
858 * Iterate through the rows retrieved via SQL and make changes for display purposes,
859 * such as rendering contacts as links.
862 * Rows generated by SQL, with an array for each row.
864 public function alterDisplay(&$rows) {
866 $activityType = CRM_Core_PseudoConstant
::activityType(TRUE, TRUE, FALSE, 'label', TRUE);
867 $activityStatus = CRM_Core_PseudoConstant
::activityStatus();
868 $priority = CRM_Core_PseudoConstant
::get('CRM_Activity_DAO_Activity', 'priority_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 $actActionLinks = CRM_Activity_Selector_Activity
::actionLinks($row['civicrm_activity_activity_type_id'],
897 CRM_Utils_Array
::value('civicrm_activity_source_record_id', $rows[$rowNum]),
899 $rows[$rowNum]['civicrm_activity_id']
903 'id' => $rows[$rowNum]['civicrm_activity_id'],
907 $actUrl = CRM_Utils_System
::url($actActionLinks[CRM_Core_Action
::VIEW
]['url'],
908 CRM_Core_Action
::replace($actActionLinks[CRM_Core_Action
::VIEW
]['qs'], $actLinkValues), TRUE
912 if (array_key_exists('civicrm_contact_contact_source', $row)) {
913 if ($value = $row['civicrm_contact_contact_source_id']) {
915 $url = CRM_Utils_System
::url("civicrm/contact/view",
916 'reset=1&cid=' . $value,
919 $rows[$rowNum]['civicrm_contact_contact_source_link'] = $url;
920 $rows[$rowNum]['civicrm_contact_contact_source_hover'] = $onHover;
926 if (array_key_exists('civicrm_contact_contact_assignee', $row)) {
927 $assigneeNames = explode(';', $row['civicrm_contact_contact_assignee']);
928 if ($value = $row['civicrm_contact_contact_assignee_id']) {
929 $assigneeContactIds = explode(';', $value);
932 foreach ($assigneeContactIds as $id => $value) {
933 if (isset($value) && isset($assigneeNames[$id])) {
934 $url = CRM_Utils_System
::url("civicrm/contact/view",
935 'reset=1&cid=' . $value,
938 $link[] = "<a title='" . $onHover . "' href='" . $url .
939 "'>{$assigneeNames[$id]}</a>";
942 $rows[$rowNum]['civicrm_contact_contact_assignee'] = implode('; ', $link);
948 if (array_key_exists('civicrm_contact_contact_target', $row)) {
949 $targetNames = explode(';', $row['civicrm_contact_contact_target']);
950 if ($value = $row['civicrm_contact_contact_target_id']) {
951 $targetContactIds = explode(';', $value);
954 foreach ($targetContactIds as $id => $value) {
955 if (isset($value) && isset($targetNames[$id])) {
956 $url = CRM_Utils_System
::url("civicrm/contact/view",
957 'reset=1&cid=' . $value,
960 $link[] = "<a title='" . $onHover . "' href='" . $url .
961 "'>{$targetNames[$id]}</a>";
964 $rows[$rowNum]['civicrm_contact_contact_target'] = implode('; ', $link);
970 if (array_key_exists('civicrm_activity_activity_type_id', $row)) {
971 if ($value = $row['civicrm_activity_activity_type_id']) {
972 $rows[$rowNum]['civicrm_activity_activity_type_id'] = $activityType[$value];
974 $rows[$rowNum]['civicrm_activity_activity_type_id_link'] = $actUrl;
975 $rows[$rowNum]['civicrm_activity_activity_type_id_hover'] = $onHoverAct;
981 if (array_key_exists('civicrm_activity_status_id', $row)) {
982 if ($value = $row['civicrm_activity_status_id']) {
983 $rows[$rowNum]['civicrm_activity_status_id'] = $activityStatus[$value];
988 if (array_key_exists('civicrm_activity_priority_id', $row)) {
989 if ($value = $row['civicrm_activity_priority_id']) {
990 $rows[$rowNum]['civicrm_activity_priority_id'] = $priority[$value];
995 if (array_key_exists('civicrm_activity_details', $row) && $this->_outputMode
== 'html') {
996 if ($value = $row['civicrm_activity_details']) {
997 $fullDetails = $rows[$rowNum]['civicrm_activity_details'];
998 $rows[$rowNum]['civicrm_activity_details'] = substr($fullDetails, 0, strrpos(substr($fullDetails, 0, 80), ' '));
1000 $rows[$rowNum]['civicrm_activity_details'] .= " <a href='{$actUrl}' title='{$onHoverAct}'>(more)</a>";
1006 if (array_key_exists('civicrm_activity_campaign_id', $row)) {
1007 if ($value = $row['civicrm_activity_campaign_id']) {
1008 $rows[$rowNum]['civicrm_activity_campaign_id'] = $this->campaigns
[$value];
1013 if (array_key_exists('civicrm_activity_engagement_level', $row)) {
1014 if ($value = $row['civicrm_activity_engagement_level']) {
1015 $rows[$rowNum]['civicrm_activity_engagement_level'] = $this->engagementLevels
[$value];
1020 if (array_key_exists('civicrm_activity_activity_date_time', $row) &&
1021 array_key_exists('civicrm_activity_status_id', $row)
1023 if (CRM_Utils_Date
::overdue($rows[$rowNum]['civicrm_activity_activity_date_time']) &&
1024 $activityStatus[$row['civicrm_activity_status_id']] != 'Completed'
1026 $rows[$rowNum]['class'] = "status-overdue";
1031 $entryFound = $this->alterDisplayAddressFields($row, $rows, $rowNum, 'activity', 'List all activities for this', ';') ?
TRUE : $entryFound;
1039 public function sectionTotals() {
1040 // Reports using order_bys with sections must populate $this->_selectAliases in select() method.
1041 if (empty($this->_selectAliases
)) {
1045 if (!empty($this->_sections
)) {
1046 // pull section aliases out of $this->_sections
1047 $sectionAliases = array_keys($this->_sections
);
1050 foreach (array_merge($sectionAliases, $this->_selectAliases
) as $alias) {
1051 $ifnulls[] = "ifnull($alias, '') as $alias";
1053 $this->_select
= "SELECT " . implode(", ", $ifnulls);
1054 $this->_select
= CRM_Contact_BAO_Query
::appendAnyValueToSelect($ifnulls, $sectionAliases);
1056 $query = $this->_select
.
1057 ", count(DISTINCT civicrm_activity_id) as ct from {$this->temporaryTables['activity_temp_table']['name']} group by " .
1058 implode(", ", $sectionAliases);
1060 // initialize array of total counts
1062 $dao = $this->executeReportQuery($query);
1063 while ($dao->fetch()) {
1064 // let $this->_alterDisplay translate any integer ids to human-readable values.
1065 $rows[0] = $dao->toArray();
1066 $this->alterDisplay($rows);
1069 // add totals for all permutations of section values
1072 $aliasCount = count($sectionAliases);
1073 foreach ($sectionAliases as $alias) {
1074 $values[] = $row[$alias];
1075 $key = implode(CRM_Core_DAO
::VALUE_SEPARATOR
, $values);
1076 if ($i == $aliasCount) {
1077 // the last alias is the lowest-level section header; use count as-is
1078 $totals[$key] = $dao->ct
;
1081 // other aliases are higher level; roll count into their total
1082 $totals[$key] +
= $dao->ct
;
1086 $this->assign('sectionTotals', $totals);
1091 * @todo remove this function & declare the 3 contact tables separately
1093 * (Currently the construct method incorrectly melds them - this is an interim
1094 * refactor in order to get this under ReportTemplateTests)
1096 protected function buildAssigneeFrom() {
1097 $activityContacts = CRM_Activity_BAO_ActivityContact
::buildOptions('record_type_id', 'validate');
1098 $assigneeID = CRM_Utils_Array
::key('Activity Assignees', $activityContacts);
1100 FROM civicrm_activity {$this->_aliases['civicrm_activity']}
1101 INNER JOIN civicrm_activity_contact {$this->_aliases['civicrm_activity_contact']}
1102 ON {$this->_aliases['civicrm_activity']}.id = {$this->_aliases['civicrm_activity_contact']}.activity_id AND
1103 {$this->_aliases['civicrm_activity_contact']}.record_type_id = {$assigneeID}
1104 INNER JOIN civicrm_contact civicrm_contact_assignee
1105 ON {$this->_aliases['civicrm_activity_contact']}.contact_id = civicrm_contact_assignee.id
1108 if ($this->isTableSelected('civicrm_email')) {
1110 LEFT JOIN civicrm_email civicrm_email_assignee
1111 ON {$this->_aliases['civicrm_activity_contact']}.contact_id = civicrm_email_assignee.contact_id AND
1112 civicrm_email_assignee.is_primary = 1";
1114 if ($this->isTableSelected('civicrm_phone')) {
1116 LEFT JOIN civicrm_phone civicrm_phone_assignee
1117 ON {$this->_aliases['civicrm_activity_contact']}.contact_id = civicrm_phone_assignee.contact_id AND
1118 civicrm_phone_assignee.is_primary = 1 ";
1120 $this->_aliases
['civicrm_contact'] = 'civicrm_contact_assignee';
1121 $this->joinAddressFromContact();
1125 * @todo remove this function & declare the 3 contact tables separately
1127 * (Currently the construct method incorrectly melds them - this is an interim
1128 * refactor in order to get this under ReportTemplateTests)
1130 protected function buildSourceFrom() {
1131 $activityContacts = CRM_Activity_BAO_ActivityContact
::buildOptions('record_type_id', 'validate');
1132 $sourceID = CRM_Utils_Array
::key('Activity Source', $activityContacts);
1134 FROM civicrm_activity {$this->_aliases['civicrm_activity']}
1135 INNER JOIN civicrm_activity_contact {$this->_aliases['civicrm_activity_contact']}
1136 ON {$this->_aliases['civicrm_activity']}.id = {$this->_aliases['civicrm_activity_contact']}.activity_id AND
1137 {$this->_aliases['civicrm_activity_contact']}.record_type_id = {$sourceID}
1138 INNER JOIN civicrm_contact civicrm_contact_source
1139 ON {$this->_aliases['civicrm_activity_contact']}.contact_id = civicrm_contact_source.id
1142 if ($this->isTableSelected('civicrm_email')) {
1144 LEFT JOIN civicrm_email civicrm_email_source
1145 ON {$this->_aliases['civicrm_activity_contact']}.contact_id = civicrm_email_source.contact_id AND
1146 civicrm_email_source.is_primary = 1";
1148 if ($this->isTableSelected('civicrm_phone')) {
1150 LEFT JOIN civicrm_phone civicrm_phone_source
1151 ON {$this->_aliases['civicrm_activity_contact']}.contact_id = civicrm_phone_source.contact_id AND
1152 civicrm_phone_source.is_primary = 1 ";
1154 $this->_aliases
['civicrm_contact'] = 'civicrm_contact_source';
1155 $this->joinAddressFromContact();