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;
601 if (! CRM_Core_Permission
::check('access CiviContribute')) {
602 $clauses[] = " ({$this->_aliases['civicrm_option_value']}.component_id IS NULL OR {$this->_aliases['civicrm_option_value']}.component_id <> 2) ";
605 if (empty($clauses)) {
606 $this->_where
.= " ";
609 $this->_where
.= " AND " . implode(' AND ', $clauses);
612 if ($this->_aclWhere
) {
613 $this->_where
.= " AND {$this->_aclWhere} ";
617 public function groupBy() {
618 $this->_groupBy
= "GROUP BY {$this->_aliases['civicrm_activity']}.id";
622 * @param string $tableAlias
624 public function buildACLClause($tableAlias = 'contact_a') {
625 //override for ACL( Since Contact may be source
626 //contact/assignee or target also it may be null )
628 if (CRM_Core_Permission
::check('view all contacts')) {
629 $this->_aclFrom
= $this->_aclWhere
= NULL;
633 $session = CRM_Core_Session
::singleton();
634 $contactID = $session->get('userID');
638 $contactID = CRM_Utils_Type
::escape($contactID, 'Integer');
640 CRM_Contact_BAO_Contact_Permission
::cache($contactID);
642 foreach ($tableAlias as $k => $alias) {
643 $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 ";
646 $this->_aclFrom
= implode(" ", $clauses);
647 $this->_aclWhere
= NULL;
651 * @param int $groupID
655 public function add2group($groupID) {
656 if (CRM_Utils_Array
::value("contact_target_op", $this->_params
) == 'nll') {
657 CRM_Core_Error
::fatal(ts('Current filter criteria didn\'t have any target contact to add to group'));
660 $new_select = 'AS addtogroup_contact_id';
661 $select = str_ireplace('AS civicrm_contact_contact_target_id', $new_select, $this->_select
);
662 $new_having = ' addtogroup_contact_id';
663 $having = str_ireplace(' civicrm_contact_contact_target_id', $new_having, $this->_having
);
665 FROM civireport_activity_temp_target tar
666 GROUP BY civicrm_activity_id $having {$this->_orderBy}";
667 $select = 'AS addtogroup_contact_id';
668 $query = str_ireplace('AS civicrm_contact_contact_target_id', $select, $query);
669 $dao = CRM_Core_DAO
::executeQuery($query);
671 $contactIDs = array();
672 // Add resulting contacts to group
673 while ($dao->fetch()) {
674 if ($dao->addtogroup_contact_id
) {
675 $contact_id = explode(';', $dao->addtogroup_contact_id
);
676 if ($contact_id[0]) {
677 $contactIDs[$contact_id[0]] = $contact_id[0];
682 if (!empty($contactIDs)) {
683 CRM_Contact_BAO_GroupContact
::addContactsToGroup($contactIDs, $groupID);
684 CRM_Core_Session
::setStatus(ts("Listed contact(s) have been added to the selected group."), ts('Contacts Added'), 'success');
687 CRM_Core_Session
::setStatus(ts("The listed records(s) cannot be added to the group."));
691 public function postProcess() {
692 $this->beginPostProcess();
694 //Assign those recordtype to array which have filter operator as 'Is not empty' or 'Is empty'
695 $nullFilters = array();
696 foreach (array('target', 'source', 'assignee') as $type) {
697 if (CRM_Utils_Array
::value("contact_{$type}_op", $this->_params
) ==
698 'nnll' ||
!empty($this->_params
["contact_{$type}_value"])
700 $nullFilters[] = " civicrm_contact_contact_{$type}_id IS NOT NULL ";
702 elseif (CRM_Utils_Array
::value("contact_{$type}_op", $this->_params
) ==
705 $nullFilters[] = " civicrm_contact_contact_{$type}_id IS NULL ";
709 // 1. fill temp table with target results
710 $this->buildACLClause(array('civicrm_contact_target'));
711 $this->select('target');
712 $this->from('target');
713 $this->customDataFrom();
714 $this->where('target');
715 $insertCols = implode(',', $this->_selectAliases
);
716 $tempQuery = "CREATE TEMPORARY TABLE civireport_activity_temp_target CHARACTER SET utf8 COLLATE utf8_unicode_ci AS
717 {$this->_select} {$this->_from} {$this->_where} ";
718 CRM_Core_DAO
::executeQuery($tempQuery);
720 // 2. add new columns to hold assignee and source results
721 // fixme: add when required
723 ALTER TABLE civireport_activity_temp_target
724 ADD COLUMN civicrm_contact_contact_assignee VARCHAR(128),
725 ADD COLUMN civicrm_contact_contact_source VARCHAR(128),
726 ADD COLUMN civicrm_contact_contact_assignee_id VARCHAR(128),
727 ADD COLUMN civicrm_contact_contact_source_id VARCHAR(128),
728 ADD COLUMN civicrm_email_contact_assignee_email VARCHAR(128),
729 ADD COLUMN civicrm_email_contact_source_email VARCHAR(128)";
730 CRM_Core_DAO
::executeQuery($tempQuery);
732 // 3. fill temp table with assignee results
733 $this->buildACLClause(array('civicrm_contact_assignee'));
734 $this->select('assignee');
735 $this->from('assignee');
736 $this->customDataFrom();
737 $this->where('assignee');
738 $insertCols = implode(',', $this->_selectAliases
);
739 $tempQuery = "INSERT INTO civireport_activity_temp_target ({$insertCols})
741 {$this->_from} {$this->_where}";
742 CRM_Core_DAO
::executeQuery($tempQuery);
744 // 4. fill temp table with source results
745 $this->buildACLClause(array('civicrm_contact_source'));
746 $this->select('source');
747 $this->from('source');
748 $this->customDataFrom();
749 $this->where('source');
750 $insertCols = implode(',', $this->_selectAliases
);
751 $tempQuery = "INSERT INTO civireport_activity_temp_target ({$insertCols})
753 {$this->_from} {$this->_where}";
754 CRM_Core_DAO
::executeQuery($tempQuery);
756 // 5. show final result set from temp table
758 $this->select('final');
760 if (!empty($nullFilters)) {
761 $this->_having
= "HAVING " . implode(' AND ', $nullFilters);
765 $sql = "{$this->_select}
766 FROM civireport_activity_temp_target tar
767 GROUP BY civicrm_activity_id {$this->_having} {$this->_orderBy} {$this->_limit}";
768 $this->buildRows($sql, $rows);
770 // format result set.
771 $this->formatDisplay($rows);
773 // assign variables to templates
774 $this->doTemplateAssignment($rows);
776 // do print / pdf / instance stuff if needed
777 $this->endPostProcess($rows);
781 * Alter display of rows.
783 * Iterate through the rows retrieved via SQL and make changes for display purposes,
784 * such as rendering contacts as links.
787 * Rows generated by SQL, with an array for each row.
789 public function alterDisplay(&$rows) {
791 $activityType = CRM_Core_PseudoConstant
::activityType(TRUE, TRUE, FALSE, 'label', TRUE);
792 $activityStatus = CRM_Core_PseudoConstant
::activityStatus();
794 $context = CRM_Utils_Request
::retrieve('context', 'String', $this, FALSE, 'report');
797 if (CRM_Core_Permission
::check('access CiviCRM')) {
799 $onHover = ts('View Contact Summary for this Contact');
800 $onHoverAct = ts('View Activity Record');
803 foreach ($rows as $rowNum => $row) {
804 // if we have an activity type, format the View Activity link for use in various columns
806 array_key_exists('civicrm_activity_activity_type_id', $row)
808 // Check for target contact id(s) and use the first contact id in that list for view activity link if found,
809 // else use source contact id
810 if (!empty($rows[$rowNum]['civicrm_contact_contact_target_id'])) {
811 $targets = explode(';', $rows[$rowNum]['civicrm_contact_contact_target_id']);
815 $cid = $rows[$rowNum]['civicrm_contact_contact_source_id'];
818 $actActionLinks = CRM_Activity_Selector_Activity
::actionLinks($row['civicrm_activity_activity_type_id'],
819 CRM_Utils_Array
::value('civicrm_activity_source_record_id', $rows[$rowNum]),
821 $rows[$rowNum]['civicrm_activity_id']
824 $actLinkValues = array(
825 'id' => $rows[$rowNum]['civicrm_activity_id'],
829 $actUrl = CRM_Utils_System
::url($actActionLinks[CRM_Core_Action
::VIEW
]['url'],
830 CRM_Core_Action
::replace($actActionLinks[CRM_Core_Action
::VIEW
]['qs'], $actLinkValues), TRUE
834 if (array_key_exists('civicrm_contact_contact_source', $row)) {
835 if ($value = $row['civicrm_contact_contact_source_id']) {
837 $url = CRM_Utils_System
::url("civicrm/contact/view",
838 'reset=1&cid=' . $value,
841 $rows[$rowNum]['civicrm_contact_contact_source_link'] = $url;
842 $rows[$rowNum]['civicrm_contact_contact_source_hover'] = $onHover;
848 if (array_key_exists('civicrm_contact_contact_assignee', $row)) {
849 $assigneeNames = explode(';', $row['civicrm_contact_contact_assignee']);
850 if ($value = $row['civicrm_contact_contact_assignee_id']) {
851 $assigneeContactIds = explode(';', $value);
854 foreach ($assigneeContactIds as $id => $value) {
855 if (isset($value) && isset($assigneeNames[$id])) {
856 $url = CRM_Utils_System
::url("civicrm/contact/view",
857 'reset=1&cid=' . $value,
860 $link[] = "<a title='" . $onHover . "' href='" . $url .
861 "'>{$assigneeNames[$id]}</a>";
864 $rows[$rowNum]['civicrm_contact_contact_assignee'] = implode('; ', $link);
870 if (array_key_exists('civicrm_contact_contact_target', $row)) {
871 $targetNames = explode(';', $row['civicrm_contact_contact_target']);
872 if ($value = $row['civicrm_contact_contact_target_id']) {
873 $targetContactIds = explode(';', $value);
876 foreach ($targetContactIds as $id => $value) {
877 if (isset($value) && isset($targetNames[$id])) {
878 $url = CRM_Utils_System
::url("civicrm/contact/view",
879 'reset=1&cid=' . $value,
882 $link[] = "<a title='" . $onHover . "' href='" . $url .
883 "'>{$targetNames[$id]}</a>";
886 $rows[$rowNum]['civicrm_contact_contact_target'] = implode('; ', $link);
892 if (array_key_exists('civicrm_activity_activity_type_id', $row)) {
893 if ($value = $row['civicrm_activity_activity_type_id']) {
894 $rows[$rowNum]['civicrm_activity_activity_type_id'] = $activityType[$value];
896 $rows[$rowNum]['civicrm_activity_activity_type_id_link'] = $actUrl;
897 $rows[$rowNum]['civicrm_activity_activity_type_id_hover'] = $onHoverAct;
903 if (array_key_exists('civicrm_activity_status_id', $row)) {
904 if ($value = $row['civicrm_activity_status_id']) {
905 $rows[$rowNum]['civicrm_activity_status_id'] = $activityStatus[$value];
910 if (array_key_exists('civicrm_activity_details', $row) && $this->_outputMode
== 'html') {
911 if ($value = $row['civicrm_activity_details']) {
912 $fullDetails = $rows[$rowNum]['civicrm_activity_details'];
913 $rows[$rowNum]['civicrm_activity_details'] = substr($fullDetails, 0, strrpos(substr($fullDetails, 0, 80), ' '));
915 $rows[$rowNum]['civicrm_activity_details'] .= " <a href='{$actUrl}' title='{$onHoverAct}'>(more)</a>";
921 if (array_key_exists('civicrm_activity_campaign_id', $row)) {
922 if ($value = $row['civicrm_activity_campaign_id']) {
923 $rows[$rowNum]['civicrm_activity_campaign_id'] = $this->activeCampaigns
[$value];
928 if (array_key_exists('civicrm_activity_engagement_level', $row)) {
929 if ($value = $row['civicrm_activity_engagement_level']) {
930 $rows[$rowNum]['civicrm_activity_engagement_level'] = $this->engagementLevels
[$value];
935 if (array_key_exists('civicrm_activity_activity_date_time', $row) &&
936 array_key_exists('civicrm_activity_status_id', $row)
938 if (CRM_Utils_Date
::overdue($rows[$rowNum]['civicrm_activity_activity_date_time']) &&
939 $activityStatus[$row['civicrm_activity_status_id']] != 'Completed'
941 $rows[$rowNum]['class'] = "status-overdue";
946 $entryFound = $this->alterDisplayAddressFields($row, $rows, $rowNum, 'activity', 'List all activities for this ') ?
TRUE : $entryFound;
954 public function sectionTotals() {
955 // Reports using order_bys with sections must populate $this->_selectAliases in select() method.
956 if (empty($this->_selectAliases
)) {
960 if (!empty($this->_sections
)) {
961 // pull section aliases out of $this->_sections
962 $sectionAliases = array_keys($this->_sections
);
965 foreach (array_merge($sectionAliases, $this->_selectAliases
) as $alias) {
966 $ifnulls[] = "ifnull($alias, '') as $alias";
969 $query = "select " . implode(", ", $ifnulls) .
970 ", count(DISTINCT civicrm_activity_id) as ct from civireport_activity_temp_target group by " .
971 implode(", ", $sectionAliases);
973 // initialize array of total counts
975 $dao = CRM_Core_DAO
::executeQuery($query);
976 while ($dao->fetch()) {
977 // let $this->_alterDisplay translate any integer ids to human-readable values.
978 $rows[0] = $dao->toArray();
979 $this->alterDisplay($rows);
982 // add totals for all permutations of section values
985 $aliasCount = count($sectionAliases);
986 foreach ($sectionAliases as $alias) {
987 $values[] = $row[$alias];
988 $key = implode(CRM_Core_DAO
::VALUE_SEPARATOR
, $values);
989 if ($i == $aliasCount) {
990 // the last alias is the lowest-level section header; use count as-is
991 $totals[$key] = $dao->ct
;
994 // other aliases are higher level; roll count into their total
995 $totals[$key] +
= $dao->ct
;
999 $this->assign('sectionTotals', $totals);