3 +--------------------------------------------------------------------+
4 | CiviCRM version 4.6 |
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2015 |
7 +--------------------------------------------------------------------+
8 | This file is a part of CiviCRM. |
10 | CiviCRM is free software; you can copy, modify, and distribute it |
11 | under the terms of the GNU Affero General Public License |
12 | Version 3, 19 November 2007 and the CiviCRM Licensing Exception. |
14 | CiviCRM is distributed in the hope that it will be useful, but |
15 | WITHOUT ANY WARRANTY; without even the implied warranty of |
16 | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. |
17 | See the GNU Affero General Public License for more details. |
19 | You should have received a copy of the GNU Affero General Public |
20 | License and the CiviCRM Licensing Exception along |
21 | with this program; if not, contact CiviCRM LLC |
22 | at info[AT]civicrm[DOT]org. If you have questions about the |
23 | GNU Affero General Public License or the licensing of CiviCRM, |
24 | see the CiviCRM license FAQ at http://civicrm.org/licensing |
25 +--------------------------------------------------------------------+
31 * @copyright CiviCRM LLC (c) 2004-2015
35 class CRM_Report_Form_Activity
extends CRM_Report_Form
{
36 protected $_selectAliasesTotal = array();
38 protected $_customGroupExtends = array(
42 protected $_nonDisplayFields = array();
47 public function __construct() {
48 // There could be multiple contacts. We not clear on which contact id to display.
49 // Lets hide it for now.
50 $this->_exposeContactID
= FALSE;
52 $config = CRM_Core_Config
::singleton();
53 $campaignEnabled = in_array("CiviCampaign", $config->enableComponents
);
54 if ($campaignEnabled) {
55 $getCampaigns = CRM_Campaign_BAO_Campaign
::getPermissionedCampaigns(NULL, NULL, TRUE, FALSE, TRUE);
56 $this->activeCampaigns
= $getCampaigns['campaigns'];
57 asort($this->activeCampaigns
);
58 $this->engagementLevels
= CRM_Campaign_PseudoConstant
::engagementLevel();
60 $this->activityTypes
= CRM_Core_PseudoConstant
::activityType(TRUE, FALSE, FALSE, 'label', TRUE);
61 asort($this->activityTypes
);
63 $this->_columns
= array(
64 'civicrm_contact' => array(
65 'dao' => 'CRM_Contact_DAO_Contact',
67 'contact_source' => array(
68 'name' => 'sort_name',
69 'title' => ts('Source Contact Name'),
70 'alias' => 'civicrm_contact_source',
73 'contact_assignee' => array(
74 'name' => 'sort_name',
75 'title' => ts('Assignee Contact Name'),
76 'alias' => 'civicrm_contact_assignee',
77 'dbAlias' => "civicrm_contact_assignee.sort_name",
80 'contact_target' => array(
81 'name' => 'sort_name',
82 'title' => ts('Target Contact Name'),
83 'alias' => 'civicrm_contact_target',
84 'dbAlias' => "civicrm_contact_target.sort_name",
87 'contact_source_id' => array(
89 'alias' => 'civicrm_contact_source',
90 'dbAlias' => "civicrm_contact_source.id",
95 'contact_assignee_id' => array(
97 'alias' => 'civicrm_contact_assignee',
98 'dbAlias' => "civicrm_contact_assignee.id",
103 'contact_target_id' => array(
105 'alias' => 'civicrm_contact_target',
106 'dbAlias' => "civicrm_contact_target.id",
107 'no_display' => TRUE,
113 'contact_source' => array(
114 'name' => 'sort_name',
115 'alias' => 'civicrm_contact_source',
116 'title' => ts('Source Contact Name'),
117 'operator' => 'like',
118 'type' => CRM_Report_Form
::OP_STRING
,
120 'contact_assignee' => array(
121 'name' => 'sort_name',
122 'alias' => 'civicrm_contact_assignee',
123 'title' => ts('Assignee Contact Name'),
124 'operator' => 'like',
125 'type' => CRM_Report_Form
::OP_STRING
,
127 'contact_target' => array(
128 'name' => 'sort_name',
129 'alias' => 'civicrm_contact_target',
130 'title' => ts('Target Contact Name'),
131 'operator' => 'like',
132 'type' => CRM_Report_Form
::OP_STRING
,
134 'current_user' => array(
135 'name' => 'current_user',
136 'title' => ts('Limit To Current User'),
137 'type' => CRM_Utils_Type
::T_INT
,
138 'operatorType' => CRM_Report_Form
::OP_SELECT
,
139 'options' => array('0' => ts('No'), '1' => ts('Yes')),
142 'grouping' => 'contact-fields',
144 'civicrm_email' => array(
145 'dao' => 'CRM_Core_DAO_Email',
147 'contact_source_email' => array(
149 'title' => ts('Source Contact Email'),
150 'alias' => 'civicrm_email_source',
152 'contact_assignee_email' => array(
154 'title' => ts('Assignee Contact Email'),
155 'alias' => 'civicrm_email_assignee',
157 'contact_target_email' => array(
159 'title' => ts('Target Contact Email'),
160 'alias' => 'civicrm_email_target',
163 'order_bys' => array(
164 'source_contact_email' => array(
166 'title' => ts('Source Contact Email'),
167 'dbAlias' => 'civicrm_email_contact_source_email',
171 'civicrm_activity' => array(
172 'dao' => 'CRM_Activity_DAO_Activity',
175 'no_display' => TRUE,
176 'title' => ts('Activity ID'),
179 'source_record_id' => array(
180 'no_display' => TRUE,
183 'activity_type_id' => array(
184 'title' => ts('Activity Type'),
186 'type' => CRM_Utils_Type
::T_STRING
,
188 'activity_subject' => array(
189 'title' => ts('Subject'),
192 'activity_date_time' => array(
193 'title' => ts('Activity Date'),
196 'status_id' => array(
197 'title' => ts('Activity Status'),
199 'type' => CRM_Utils_Type
::T_STRING
,
202 'title' => ts('Duration'),
203 'type' => CRM_Utils_Type
::T_INT
,
206 'title' => ts('Activity Details'),
210 'activity_date_time' => array(
211 'default' => 'this.month',
212 'operatorType' => CRM_Report_Form
::OP_DATE
,
214 'activity_subject' => array('title' => ts('Activity Subject')),
215 'activity_type_id' => array(
216 'title' => ts('Activity Type'),
217 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
218 'options' => $this->activityTypes
,
220 'status_id' => array(
221 'title' => ts('Activity Status'),
222 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
223 'options' => CRM_Core_PseudoConstant
::activityStatus(),
226 'title' => ts('Activity Details'),
227 'type' => CRM_Utils_Type
::T_TEXT
,
230 'order_bys' => array(
231 'activity_date_time' => array(
232 'title' => ts('Activity Date'),
233 'default_weight' => '1',
234 'dbAlias' => 'civicrm_activity_activity_date_time',
236 'activity_type_id' => array(
237 'title' => ts('Activity Type'),
238 'default_weight' => '2',
239 'dbAlias' => "option_value_civireport",
242 'grouping' => 'activity-fields',
243 'alias' => 'activity',
245 'civicrm_activity_contact' => array(
246 'dao' => 'CRM_Activity_DAO_ActivityContact',
247 'fields' => array(// so we have $this->_alias populated
250 'civicrm_option_value' => array(
251 'dao' => 'CRM_Core_DAO_OptionValue',
252 'fields' => array(// so we have $this->_alias populated
256 'title' => ts('Activity Status'),
258 'type' => CRM_Utils_Type
::T_STRING
,
262 'title' => ts('Duration'),
263 'type' => CRM_Utils_Type
::T_INT
,
267 'title' => ts('Location'),
268 'type' => CRM_Utils_Type
::T_STRING
,
271 'title' => ts('Activity Details'),
275 'activity_date_time' => array(
276 'default' => 'this.month',
277 'operatorType' => CRM_Report_Form
::OP_DATE
,
279 'activity_subject' =>
280 array('title' => ts('Activity Subject')),
283 'title' => ts('Location'),
284 'type' => CRM_Utils_Type
::T_TEXT
,
286 'activity_type_id' =>
288 'title' => ts('Activity Type'),
289 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
290 'options' => $this->activityTypes
,
294 'title' => ts('Activity Status'),
295 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
296 'options' => CRM_Core_PseudoConstant
::activityStatus(),
299 'title' => ts('Activity Details'),
300 'type' => CRM_Utils_Type
::T_TEXT
,
303 ) +
$this->addressFields(TRUE);
305 if ($campaignEnabled) {
306 // Add display column and filter for Survey Results, Campaign and Engagement Index if CiviCampaign is enabled
308 $this->_columns
['civicrm_activity']['fields']['result'] = array(
309 'title' => 'Survey Result',
310 'default' => 'false',
312 $this->_columns
['civicrm_activity']['filters']['result'] = array(
313 'title' => ts('Survey Result'),
314 'operator' => 'like',
315 'type' => CRM_Utils_Type
::T_STRING
,
317 if (!empty($this->activeCampaigns
)) {
318 $this->_columns
['civicrm_activity']['fields']['campaign_id'] = array(
319 'title' => 'Campaign',
320 'default' => 'false',
322 $this->_columns
['civicrm_activity']['filters']['campaign_id'] = array(
323 'title' => ts('Campaign'),
324 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
325 'options' => $this->activeCampaigns
,
328 if (!empty($this->engagementLevels
)) {
329 $this->_columns
['civicrm_activity']['fields']['engagement_level'] = array(
330 'title' => 'Engagement Index',
331 'default' => 'false',
333 $this->_columns
['civicrm_activity']['filters']['engagement_level'] = array(
334 'title' => ts('Engagement Index'),
335 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
336 'options' => $this->engagementLevels
,
340 $this->_groupFilter
= TRUE;
341 $this->_tagFilter
= TRUE;
342 $this->_tagFilterTable
= 'civicrm_activity';
343 parent
::__construct();
347 * Adding address fields with dbAlias for order clause.
349 * @param bool $orderBy
354 public function addressFields($orderBy = FALSE) {
355 $address = parent
::addAddressFields(FALSE, TRUE);
357 foreach ($address['civicrm_address']['order_bys'] as $fieldName => $field) {
358 $address['civicrm_address']['order_bys'][$fieldName]['dbAlias'] = "civicrm_address_{$fieldName}";
365 * @param null $recordType
367 public function select($recordType = NULL) {
368 if (!array_key_exists("contact_{$recordType}", $this->_params
['fields']) &&
369 $recordType != 'final'
371 $this->_nonDisplayFields
[] = "civicrm_contact_contact_{$recordType}";
375 if ($recordType == 'final' && !empty($this->_nonDisplayFields
)) {
376 foreach ($this->_nonDisplayFields
as $fieldName) {
377 unset($this->_columnHeaders
[$fieldName]);
381 if (empty($this->_selectAliasesTotal
)) {
382 $this->_selectAliasesTotal
= $this->_selectAliases
;
385 $removeKeys = array();
386 if ($recordType == 'target') {
387 foreach ($this->_selectClauses
as $key => $clause) {
388 if (strstr($clause, 'civicrm_contact_assignee.') ||
389 strstr($clause, 'civicrm_contact_source.') ||
390 strstr($clause, 'civicrm_email_assignee.') ||
391 strstr($clause, 'civicrm_email_source.')
393 $removeKeys[] = $key;
394 unset($this->_selectClauses
[$key]);
398 elseif ($recordType == 'assignee') {
399 foreach ($this->_selectClauses
as $key => $clause) {
400 if (strstr($clause, 'civicrm_contact_target.') ||
401 strstr($clause, 'civicrm_contact_source.') ||
402 strstr($clause, 'civicrm_email_target.') ||
403 strstr($clause, 'civicrm_email_source.')
405 $removeKeys[] = $key;
406 unset($this->_selectClauses
[$key]);
410 elseif ($recordType == 'source') {
411 foreach ($this->_selectClauses
as $key => $clause) {
412 if (strstr($clause, 'civicrm_contact_target.') ||
413 strstr($clause, 'civicrm_contact_assignee.') ||
414 strstr($clause, 'civicrm_email_target.') ||
415 strstr($clause, 'civicrm_email_assignee.')
417 $removeKeys[] = $key;
418 unset($this->_selectClauses
[$key]);
422 elseif ($recordType == 'final') {
423 $this->_selectClauses
= $this->_selectAliasesTotal
;
424 foreach ($this->_selectClauses
as $key => $clause) {
425 if (strstr($clause, 'civicrm_contact_contact_target') ||
426 strstr($clause, 'civicrm_contact_contact_assignee') ||
427 strstr($clause, 'civicrm_contact_contact_source')
429 $this->_selectClauses
[$key] = "GROUP_CONCAT($clause SEPARATOR ';') as $clause";
435 foreach ($removeKeys as $key) {
436 unset($this->_selectAliases
[$key]);
439 if ($recordType != 'final') {
440 foreach ($this->_columns
['civicrm_address']['order_bys'] as $fieldName => $field) {
441 $orderByFld = $this->_columns
['civicrm_address']['order_bys'][$fieldName];
442 $fldInfo = $this->_columns
['civicrm_address']['fields'][$fieldName];
443 $this->_selectAliases
[] = $orderByFld['dbAlias'];
444 $this->_selectClauses
[] = "{$fldInfo['dbAlias']} as {$orderByFld['dbAlias']}";
446 $this->_selectAliases
[] = $this->_aliases
['civicrm_option_value'];
447 $this->_selectClauses
[] = "{$this->_aliases['civicrm_option_value']}.label as {$this->_aliases['civicrm_option_value']}";
448 $this->_selectAliases
= array_unique($this->_selectAliases
);
449 $this->_selectClauses
= array_unique($this->_selectClauses
);
451 $this->_select
= "SELECT " . implode(', ', $this->_selectClauses
) . " ";
458 public function from($recordType) {
459 $activityContacts = CRM_Core_OptionGroup
::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
460 $activityTypeId = CRM_Core_DAO
::getFieldValue("CRM_Core_DAO_OptionGroup", 'activity_type', 'id', 'name');
461 $assigneeID = CRM_Utils_Array
::key('Activity Assignees', $activityContacts);
462 $targetID = CRM_Utils_Array
::key('Activity Targets', $activityContacts);
463 $sourceID = CRM_Utils_Array
::key('Activity Source', $activityContacts);
465 if ($recordType == 'target') {
467 FROM civicrm_activity {$this->_aliases['civicrm_activity']}
468 INNER JOIN civicrm_activity_contact {$this->_aliases['civicrm_activity_contact']}
469 ON {$this->_aliases['civicrm_activity']}.id = {$this->_aliases['civicrm_activity_contact']}.activity_id AND
470 {$this->_aliases['civicrm_activity_contact']}.record_type_id = {$targetID}
471 INNER JOIN civicrm_contact civicrm_contact_target
472 ON {$this->_aliases['civicrm_activity_contact']}.contact_id = civicrm_contact_target.id
475 if ($this->isTableSelected('civicrm_email')) {
477 LEFT JOIN civicrm_email civicrm_email_target
478 ON {$this->_aliases['civicrm_activity_contact']}.contact_id = civicrm_email_target.contact_id AND
479 civicrm_email_target.is_primary = 1";
481 $this->_aliases
['civicrm_contact'] = 'civicrm_contact_target';
484 if ($recordType == 'assignee') {
486 FROM civicrm_activity {$this->_aliases['civicrm_activity']}
487 INNER JOIN civicrm_activity_contact {$this->_aliases['civicrm_activity_contact']}
488 ON {$this->_aliases['civicrm_activity']}.id = {$this->_aliases['civicrm_activity_contact']}.activity_id AND
489 {$this->_aliases['civicrm_activity_contact']}.record_type_id = {$assigneeID}
490 INNER JOIN civicrm_contact civicrm_contact_assignee
491 ON {$this->_aliases['civicrm_activity_contact']}.contact_id = civicrm_contact_assignee.id
494 if ($this->isTableSelected('civicrm_email')) {
496 LEFT JOIN civicrm_email civicrm_email_assignee
497 ON {$this->_aliases['civicrm_activity_contact']}.contact_id = civicrm_email_assignee.contact_id AND
498 civicrm_email_assignee.is_primary = 1";
500 $this->_aliases
['civicrm_contact'] = 'civicrm_contact_assignee';
503 if ($recordType == 'source') {
505 FROM civicrm_activity {$this->_aliases['civicrm_activity']}
506 INNER JOIN civicrm_activity_contact {$this->_aliases['civicrm_activity_contact']}
507 ON {$this->_aliases['civicrm_activity']}.id = {$this->_aliases['civicrm_activity_contact']}.activity_id AND
508 {$this->_aliases['civicrm_activity_contact']}.record_type_id = {$sourceID}
509 INNER JOIN civicrm_contact civicrm_contact_source
510 ON {$this->_aliases['civicrm_activity_contact']}.contact_id = civicrm_contact_source.id
513 if ($this->isTableSelected('civicrm_email')) {
515 LEFT JOIN civicrm_email civicrm_email_source
516 ON {$this->_aliases['civicrm_activity_contact']}.contact_id = civicrm_email_source.contact_id AND
517 civicrm_email_source.is_primary = 1";
519 $this->_aliases
['civicrm_contact'] = 'civicrm_contact_source';
521 $this->_from
.= "INNER JOIN civicrm_option_value {$this->_aliases['civicrm_option_value']}
522 ON {$this->_aliases['civicrm_option_value']}.option_group_id = {$activityTypeId}
523 AND {$this->_aliases['civicrm_option_value']}.value = {$this->_aliases['civicrm_activity']}.activity_type_id";
524 $this->addAddressFromClause();
528 * @param null $recordType
530 public function where($recordType = NULL) {
531 $this->_where
= " WHERE {$this->_aliases['civicrm_activity']}.is_test = 0 AND
532 {$this->_aliases['civicrm_activity']}.is_deleted = 0 AND
533 {$this->_aliases['civicrm_activity']}.is_current_revision = 1";
536 foreach ($this->_columns
as $tableName => $table) {
537 if (array_key_exists('filters', $table)) {
539 foreach ($table['filters'] as $fieldName => $field) {
541 if ($fieldName != 'contact_' . $recordType &&
542 (strstr($fieldName, '_target') ||
543 strstr($fieldName, '_assignee') ||
544 strstr($fieldName, '_source')
549 if (CRM_Utils_Array
::value('type', $field) & CRM_Utils_Type
::T_DATE
) {
550 $relative = CRM_Utils_Array
::value("{$fieldName}_relative", $this->_params
);
551 $from = CRM_Utils_Array
::value("{$fieldName}_from", $this->_params
);
552 $to = CRM_Utils_Array
::value("{$fieldName}_to", $this->_params
);
554 $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']);
557 $op = CRM_Utils_Array
::value("{$fieldName}_op", $this->_params
);
558 if ($op && ($op != 'nnll' ||
$op != 'nll')) {
559 $clause = $this->whereClause($field,
561 CRM_Utils_Array
::value("{$fieldName}_value", $this->_params
),
562 CRM_Utils_Array
::value("{$fieldName}_min", $this->_params
),
563 CRM_Utils_Array
::value("{$fieldName}_max", $this->_params
)
565 if ($fieldName == 'activity_type_id' &&
566 empty($this->_params
['activity_type_id_value'])
568 $actTypes = array_flip(CRM_Core_PseudoConstant
::activityType(TRUE, FALSE, FALSE, 'label', TRUE));
569 $clause = "( {$this->_aliases['civicrm_activity']}.activity_type_id IN (" .
570 implode(',', $actTypes) . ") )";
575 if ($field['name'] == 'current_user') {
576 if (CRM_Utils_Array
::value("{$fieldName}_value", $this->_params
) ==
580 $session = CRM_Core_Session
::singleton();
581 if ($contactID = $session->get('userID')) {
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} ";
612 public function groupBy() {
613 $this->_groupBy
= "GROUP BY {$this->_aliases['civicrm_activity']}.id";
617 * @param string $tableAlias
619 public function buildACLClause($tableAlias = 'contact_a') {
620 //override for ACL( Since Contact may be source
621 //contact/assignee or target also it may be null )
623 if (CRM_Core_Permission
::check('view all contacts')) {
624 $this->_aclFrom
= $this->_aclWhere
= NULL;
628 $session = CRM_Core_Session
::singleton();
629 $contactID = $session->get('userID');
633 $contactID = CRM_Utils_Type
::escape($contactID, 'Integer');
635 CRM_Contact_BAO_Contact_Permission
::cache($contactID);
637 foreach ($tableAlias as $k => $alias) {
638 $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 ";
641 $this->_aclFrom
= implode(" ", $clauses);
642 $this->_aclWhere
= NULL;
646 * @param int $groupID
650 public function add2group($groupID) {
651 if (CRM_Utils_Array
::value("contact_target_op", $this->_params
) == 'nll') {
652 CRM_Core_Error
::fatal(ts('Current filter criteria didn\'t have any target contact to add to group'));
655 $query = "{$this->_select}
656 FROM civireport_activity_temp_target tar
657 GROUP BY civicrm_activity_id {$this->_having} {$this->_orderBy}";
658 $select = 'AS addtogroup_contact_id';
659 $query = str_ireplace('AS civicrm_contact_contact_target_id', $select, $query);
660 $dao = CRM_Core_DAO
::executeQuery($query);
662 $contactIDs = array();
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."));
682 public function postProcess() {
683 $this->beginPostProcess();
685 //Assign those recordtype to array which have filter operator as 'Is not empty' or 'Is empty'
686 $nullFilters = array();
687 foreach (array('target', 'source', 'assignee') as $type) {
688 if (CRM_Utils_Array
::value("contact_{$type}_op", $this->_params
) ==
689 'nnll' ||
!empty($this->_params
["contact_{$type}_value"])
691 $nullFilters[] = " civicrm_contact_contact_{$type}_id IS NOT NULL ";
693 elseif (CRM_Utils_Array
::value("contact_{$type}_op", $this->_params
) ==
696 $nullFilters[] = " civicrm_contact_contact_{$type}_id IS NULL ";
700 // 1. fill temp table with target results
701 $this->buildACLClause(array('civicrm_contact_target'));
702 $this->select('target');
703 $this->from('target');
704 $this->customDataFrom();
705 $this->where('target');
706 $insertCols = implode(',', $this->_selectAliases
);
707 $tempQuery = "CREATE TEMPORARY TABLE civireport_activity_temp_target CHARACTER SET utf8 COLLATE utf8_unicode_ci AS
708 {$this->_select} {$this->_from} {$this->_where} ";
709 CRM_Core_DAO
::executeQuery($tempQuery);
711 // 2. add new columns to hold assignee and source results
712 // fixme: add when required
714 ALTER TABLE civireport_activity_temp_target
715 ADD COLUMN civicrm_contact_contact_assignee VARCHAR(128),
716 ADD COLUMN civicrm_contact_contact_source VARCHAR(128),
717 ADD COLUMN civicrm_contact_contact_assignee_id VARCHAR(128),
718 ADD COLUMN civicrm_contact_contact_source_id VARCHAR(128),
719 ADD COLUMN civicrm_email_contact_assignee_email VARCHAR(128),
720 ADD COLUMN civicrm_email_contact_source_email VARCHAR(128)";
721 CRM_Core_DAO
::executeQuery($tempQuery);
723 // 3. fill temp table with assignee results
724 $this->buildACLClause(array('civicrm_contact_assignee'));
725 $this->select('assignee');
726 $this->from('assignee');
727 $this->customDataFrom();
728 $this->where('assignee');
729 $insertCols = implode(',', $this->_selectAliases
);
730 $tempQuery = "INSERT INTO civireport_activity_temp_target ({$insertCols})
732 {$this->_from} {$this->_where}";
733 CRM_Core_DAO
::executeQuery($tempQuery);
735 // 4. fill temp table with source results
736 $this->buildACLClause(array('civicrm_contact_source'));
737 $this->select('source');
738 $this->from('source');
739 $this->customDataFrom();
740 $this->where('source');
741 $insertCols = implode(',', $this->_selectAliases
);
742 $tempQuery = "INSERT INTO civireport_activity_temp_target ({$insertCols})
744 {$this->_from} {$this->_where}";
745 CRM_Core_DAO
::executeQuery($tempQuery);
747 // 5. show final result set from temp table
749 $this->select('final');
751 if (!empty($nullFilters)) {
752 $this->_having
= "HAVING " . implode(' AND ', $nullFilters);
756 $sql = "{$this->_select}
757 FROM civireport_activity_temp_target tar
758 GROUP BY civicrm_activity_id {$this->_having} {$this->_orderBy} {$this->_limit}";
759 $this->buildRows($sql, $rows);
761 // format result set.
762 $this->formatDisplay($rows);
764 // assign variables to templates
765 $this->doTemplateAssignment($rows);
767 // do print / pdf / instance stuff if needed
768 $this->endPostProcess($rows);
772 * Alter display of rows.
774 * Iterate through the rows retrieved via SQL and make changes for display purposes,
775 * such as rendering contacts as links.
778 * Rows generated by SQL, with an array for each row.
780 public function alterDisplay(&$rows) {
782 $activityType = CRM_Core_PseudoConstant
::activityType(TRUE, TRUE, FALSE, 'label', TRUE);
783 $activityStatus = CRM_Core_PseudoConstant
::activityStatus();
785 $context = CRM_Utils_Request
::retrieve('context', 'String', $this, FALSE, 'report');
788 if (CRM_Core_Permission
::check('access CiviCRM')) {
790 $onHover = ts('View Contact Summary for this Contact');
791 $onHoverAct = ts('View Activity Record');
794 foreach ($rows as $rowNum => $row) {
795 // if we have an activity type, format the View Activity link for use in various columns
797 array_key_exists('civicrm_activity_activity_type_id', $row)
799 // Check for target contact id(s) and use the first contact id in that list for view activity link if found,
800 // else use source contact id
801 if (!empty($rows[$rowNum]['civicrm_contact_contact_target_id'])) {
802 $targets = explode(';', $rows[$rowNum]['civicrm_contact_contact_target_id']);
806 $cid = $rows[$rowNum]['civicrm_contact_contact_source_id'];
809 $actActionLinks = CRM_Activity_Selector_Activity
::actionLinks($row['civicrm_activity_activity_type_id'],
810 CRM_Utils_Array
::value('civicrm_activity_source_record_id', $rows[$rowNum]),
812 $rows[$rowNum]['civicrm_activity_id']
815 $actLinkValues = array(
816 'id' => $rows[$rowNum]['civicrm_activity_id'],
820 $actUrl = CRM_Utils_System
::url($actActionLinks[CRM_Core_Action
::VIEW
]['url'],
821 CRM_Core_Action
::replace($actActionLinks[CRM_Core_Action
::VIEW
]['qs'], $actLinkValues), TRUE
825 if (array_key_exists('civicrm_contact_contact_source', $row)) {
826 if ($value = $row['civicrm_contact_contact_source_id']) {
828 $url = CRM_Utils_System
::url("civicrm/contact/view",
829 'reset=1&cid=' . $value,
832 $rows[$rowNum]['civicrm_contact_contact_source_link'] = $url;
833 $rows[$rowNum]['civicrm_contact_contact_source_hover'] = $onHover;
839 if (array_key_exists('civicrm_contact_contact_assignee', $row)) {
840 $assigneeNames = explode(';', $row['civicrm_contact_contact_assignee']);
841 if ($value = $row['civicrm_contact_contact_assignee_id']) {
842 $assigneeContactIds = explode(';', $value);
845 foreach ($assigneeContactIds as $id => $value) {
846 if (isset($value) && isset($assigneeNames[$id])) {
847 $url = CRM_Utils_System
::url("civicrm/contact/view",
848 'reset=1&cid=' . $value,
851 $link[] = "<a title='" . $onHover . "' href='" . $url .
852 "'>{$assigneeNames[$id]}</a>";
855 $rows[$rowNum]['civicrm_contact_contact_assignee'] = implode('; ', $link);
861 if (array_key_exists('civicrm_contact_contact_target', $row)) {
862 $targetNames = explode(';', $row['civicrm_contact_contact_target']);
863 if ($value = $row['civicrm_contact_contact_target_id']) {
864 $targetContactIds = explode(';', $value);
867 foreach ($targetContactIds as $id => $value) {
868 if (isset($value) && isset($targetNames[$id])) {
869 $url = CRM_Utils_System
::url("civicrm/contact/view",
870 'reset=1&cid=' . $value,
873 $link[] = "<a title='" . $onHover . "' href='" . $url .
874 "'>{$targetNames[$id]}</a>";
877 $rows[$rowNum]['civicrm_contact_contact_target'] = implode('; ', $link);
883 if (array_key_exists('civicrm_activity_activity_type_id', $row)) {
884 if ($value = $row['civicrm_activity_activity_type_id']) {
885 $rows[$rowNum]['civicrm_activity_activity_type_id'] = $activityType[$value];
887 $rows[$rowNum]['civicrm_activity_activity_type_id_link'] = $actUrl;
888 $rows[$rowNum]['civicrm_activity_activity_type_id_hover'] = $onHoverAct;
894 if (array_key_exists('civicrm_activity_status_id', $row)) {
895 if ($value = $row['civicrm_activity_status_id']) {
896 $rows[$rowNum]['civicrm_activity_status_id'] = $activityStatus[$value];
901 if (array_key_exists('civicrm_activity_details', $row) && $this->_outputMode
== 'html') {
902 if ($value = $row['civicrm_activity_details']) {
903 $fullDetails = $rows[$rowNum]['civicrm_activity_details'];
904 $rows[$rowNum]['civicrm_activity_details'] = substr($fullDetails, 0, strrpos(substr($fullDetails, 0, 80), ' '));
906 $rows[$rowNum]['civicrm_activity_details'] .= " <a href='{$actUrl}' title='{$onHoverAct}'>(more)</a>";
912 if (array_key_exists('civicrm_activity_campaign_id', $row)) {
913 if ($value = $row['civicrm_activity_campaign_id']) {
914 $rows[$rowNum]['civicrm_activity_campaign_id'] = $this->activeCampaigns
[$value];
919 if (array_key_exists('civicrm_activity_engagement_level', $row)) {
920 if ($value = $row['civicrm_activity_engagement_level']) {
921 $rows[$rowNum]['civicrm_activity_engagement_level'] = $this->engagementLevels
[$value];
926 if (array_key_exists('civicrm_activity_activity_date_time', $row) &&
927 array_key_exists('civicrm_activity_status_id', $row)
929 if (CRM_Utils_Date
::overdue($rows[$rowNum]['civicrm_activity_activity_date_time']) &&
930 $activityStatus[$row['civicrm_activity_status_id']] != 'Completed'
932 $rows[$rowNum]['class'] = "status-overdue";
937 $entryFound = $this->alterDisplayAddressFields($row, $rows, $rowNum, 'activity', 'List all activities for this ') ?
TRUE : $entryFound;
945 public function sectionTotals() {
946 // Reports using order_bys with sections must populate $this->_selectAliases in select() method.
947 if (empty($this->_selectAliases
)) {
951 if (!empty($this->_sections
)) {
952 // pull section aliases out of $this->_sections
953 $sectionAliases = array_keys($this->_sections
);
956 foreach (array_merge($sectionAliases, $this->_selectAliases
) as $alias) {
957 $ifnulls[] = "ifnull($alias, '') as $alias";
960 $query = "select " . implode(", ", $ifnulls) .
961 ", count(DISTINCT civicrm_activity_id) as ct from civireport_activity_temp_target group by " .
962 implode(", ", $sectionAliases);
964 // initialize array of total counts
966 $dao = CRM_Core_DAO
::executeQuery($query);
967 while ($dao->fetch()) {
968 // let $this->_alterDisplay translate any integer ids to human-readable values.
969 $rows[0] = $dao->toArray();
970 $this->alterDisplay($rows);
973 // add totals for all permutations of section values
976 $aliasCount = count($sectionAliases);
977 foreach ($sectionAliases as $alias) {
978 $values[] = $row[$alias];
979 $key = implode(CRM_Core_DAO
::VALUE_SEPARATOR
, $values);
980 if ($i == $aliasCount) {
981 // the last alias is the lowest-level section header; use count as-is
982 $totals[$key] = $dao->ct
;
985 // other aliases are higher level; roll count into their total
986 $totals[$key] +
= $dao->ct
;
990 $this->assign('sectionTotals', $totals);