3 +--------------------------------------------------------------------+
4 | CiviCRM version 4.6 |
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2014 |
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-2014
35 class CRM_Report_Form_Activity
extends CRM_Report_Form
{
36 protected $_selectAliasesTotal = array();
38 protected $_customGroupExtends = array(
42 protected $_nonDisplayFields = array();
48 public function __construct() {
49 // There could be multiple contacts. We not clear on which contact id to display.
50 // Lets hide it for now.
51 $this->_exposeContactID
= FALSE;
53 $config = CRM_Core_Config
::singleton();
54 $campaignEnabled = in_array("CiviCampaign", $config->enableComponents
);
55 if ($campaignEnabled) {
56 $getCampaigns = CRM_Campaign_BAO_Campaign
::getPermissionedCampaigns(NULL, NULL, TRUE, FALSE, TRUE);
57 $this->activeCampaigns
= $getCampaigns['campaigns'];
58 asort($this->activeCampaigns
);
59 $this->engagementLevels
= CRM_Campaign_PseudoConstant
::engagementLevel();
61 $this->activityTypes
= CRM_Core_PseudoConstant
::activityType(TRUE, FALSE, FALSE, 'label', TRUE);
62 asort($this->activityTypes
);
64 $this->_columns
= array(
65 'civicrm_contact' => array(
66 'dao' => 'CRM_Contact_DAO_Contact',
68 'contact_source' => array(
69 'name' => 'sort_name',
70 'title' => ts('Source Contact Name'),
71 'alias' => 'civicrm_contact_source',
74 'contact_assignee' => array(
75 'name' => 'sort_name',
76 'title' => ts('Assignee Contact Name'),
77 'alias' => 'civicrm_contact_assignee',
78 'dbAlias' => "civicrm_contact_assignee.sort_name",
81 'contact_target' => array(
82 'name' => 'sort_name',
83 'title' => ts('Target Contact Name'),
84 'alias' => 'civicrm_contact_target',
85 'dbAlias' => "civicrm_contact_target.sort_name",
88 'contact_source_id' => array(
90 'alias' => 'civicrm_contact_source',
91 'dbAlias' => "civicrm_contact_source.id",
96 'contact_assignee_id' => array(
98 'alias' => 'civicrm_contact_assignee',
99 'dbAlias' => "civicrm_contact_assignee.id",
100 'no_display' => TRUE,
104 'contact_target_id' => array(
106 'alias' => 'civicrm_contact_target',
107 'dbAlias' => "civicrm_contact_target.id",
108 'no_display' => TRUE,
114 'contact_source' => array(
115 'name' => 'sort_name',
116 'alias' => 'civicrm_contact_source',
117 'title' => ts('Source Contact Name'),
118 'operator' => 'like',
119 'type' => CRM_Report_Form
::OP_STRING
,
121 'contact_assignee' => array(
122 'name' => 'sort_name',
123 'alias' => 'civicrm_contact_assignee',
124 'title' => ts('Assignee Contact Name'),
125 'operator' => 'like',
126 'type' => CRM_Report_Form
::OP_STRING
,
128 'contact_target' => array(
129 'name' => 'sort_name',
130 'alias' => 'civicrm_contact_target',
131 'title' => ts('Target Contact Name'),
132 'operator' => 'like',
133 'type' => CRM_Report_Form
::OP_STRING
,
135 'current_user' => array(
136 'name' => 'current_user',
137 'title' => ts('Limit To Current User'),
138 'type' => CRM_Utils_Type
::T_INT
,
139 'operatorType' => CRM_Report_Form
::OP_SELECT
,
140 'options' => array('0' => ts('No'), '1' => ts('Yes')),
143 'grouping' => 'contact-fields',
145 'civicrm_email' => array(
146 'dao' => 'CRM_Core_DAO_Email',
148 'contact_source_email' => array(
150 'title' => ts('Source Contact Email'),
151 'alias' => 'civicrm_email_source',
153 'contact_assignee_email' => array(
155 'title' => ts('Assignee Contact Email'),
156 'alias' => 'civicrm_email_assignee',
158 'contact_target_email' => array(
160 'title' => ts('Target Contact Email'),
161 'alias' => 'civicrm_email_target',
164 'order_bys' => array(
165 'source_contact_email' => array(
167 'title' => ts('Source Contact Email'),
168 'dbAlias' => 'civicrm_email_contact_source_email',
172 'civicrm_activity' => array(
173 'dao' => 'CRM_Activity_DAO_Activity',
176 'no_display' => TRUE,
177 'title' => ts('Activity ID'),
180 'source_record_id' => array(
181 'no_display' => TRUE,
184 'activity_type_id' => array(
185 'title' => ts('Activity Type'),
187 'type' => CRM_Utils_Type
::T_STRING
,
189 'activity_subject' => array(
190 'title' => ts('Subject'),
193 'activity_date_time' => array(
194 'title' => ts('Activity Date'),
197 'status_id' => array(
198 'title' => ts('Activity Status'),
200 'type' => CRM_Utils_Type
::T_STRING
,
203 'title' => ts('Duration'),
204 'type' => CRM_Utils_Type
::T_INT
,
207 'title' => ts('Activity Details'),
211 'activity_date_time' => array(
212 'default' => 'this.month',
213 'operatorType' => CRM_Report_Form
::OP_DATE
,
215 'activity_subject' => array('title' => ts('Activity Subject')),
216 'activity_type_id' => array(
217 'title' => ts('Activity Type'),
218 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
219 'options' => $this->activityTypes
,
221 'status_id' => array(
222 'title' => ts('Activity Status'),
223 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
224 'options' => CRM_Core_PseudoConstant
::activityStatus(),
227 'title' => ts('Activity Details'),
228 'type' => CRM_Utils_Type
::T_TEXT
,
231 'order_bys' => array(
232 'activity_date_time' => array(
233 'title' => ts('Activity Date'),
234 'default_weight' => '1',
235 'dbAlias' => 'civicrm_activity_activity_date_time',
237 'activity_type_id' => array(
238 'title' => ts('Activity Type'),
239 'default_weight' => '2',
240 'dbAlias' => "option_value_civireport",
243 'grouping' => 'activity-fields',
244 'alias' => 'activity',
246 'civicrm_activity_contact' => array(
247 'dao' => 'CRM_Activity_DAO_ActivityContact',
248 'fields' => array(// so we have $this->_alias populated
251 'civicrm_option_value' => array(
252 'dao' => 'CRM_Core_DAO_OptionValue',
253 'fields' => array(// so we have $this->_alias populated
257 'title' => ts('Activity Status'),
259 'type' => CRM_Utils_Type
::T_STRING
,
263 'title' => ts('Duration'),
264 'type' => CRM_Utils_Type
::T_INT
,
268 'title' => ts('Location'),
269 'type' => CRM_Utils_Type
::T_STRING
,
272 'title' => ts('Activity Details'),
276 'activity_date_time' => array(
277 'default' => 'this.month',
278 'operatorType' => CRM_Report_Form
::OP_DATE
,
280 'activity_subject' =>
281 array('title' => ts('Activity Subject')),
284 'title' => ts('Location'),
285 'type' => CRM_Utils_Type
::T_TEXT
,
287 'activity_type_id' =>
289 'title' => ts('Activity Type'),
290 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
291 'options' => $this->activityTypes
,
295 'title' => ts('Activity Status'),
296 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
297 'options' => CRM_Core_PseudoConstant
::activityStatus(),
300 'title' => ts('Activity Details'),
301 'type' => CRM_Utils_Type
::T_TEXT
,
304 ) +
$this->addressFields(TRUE);
306 if ($campaignEnabled) {
307 // Add display column and filter for Survey Results, Campaign and Engagement Index if CiviCampaign is enabled
309 $this->_columns
['civicrm_activity']['fields']['result'] = array(
310 'title' => 'Survey Result',
311 'default' => 'false',
313 $this->_columns
['civicrm_activity']['filters']['result'] = array(
314 'title' => ts('Survey Result'),
315 'operator' => 'like',
316 'type' => CRM_Utils_Type
::T_STRING
,
318 if (!empty($this->activeCampaigns
)) {
319 $this->_columns
['civicrm_activity']['fields']['campaign_id'] = array(
320 'title' => 'Campaign',
321 'default' => 'false',
323 $this->_columns
['civicrm_activity']['filters']['campaign_id'] = array(
324 'title' => ts('Campaign'),
325 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
326 'options' => $this->activeCampaigns
,
329 if (!empty($this->engagementLevels
)) {
330 $this->_columns
['civicrm_activity']['fields']['engagement_level'] = array(
331 'title' => 'Engagement Index',
332 'default' => 'false',
334 $this->_columns
['civicrm_activity']['filters']['engagement_level'] = array(
335 'title' => ts('Engagement Index'),
336 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
337 'options' => $this->engagementLevels
,
341 $this->_groupFilter
= TRUE;
342 $this->_tagFilter
= TRUE;
343 $this->_tagFilterTable
= 'civicrm_activity';
344 parent
::__construct();
347 /** adding address fields with dbAlias for order clause
351 public function addressFields($orderBy = FALSE) {
352 $address = parent
::addAddressFields(FALSE, TRUE);
354 foreach ($address['civicrm_address']['order_bys'] as $fieldName => $field) {
355 $address['civicrm_address']['order_bys'][$fieldName]['dbAlias'] = "civicrm_address_{$fieldName}";
362 * @param null $recordType
364 public function select($recordType = NULL) {
365 if (!array_key_exists("contact_{$recordType}", $this->_params
['fields']) &&
366 $recordType != 'final'
368 $this->_nonDisplayFields
[] = "civicrm_contact_contact_{$recordType}";
372 if ($recordType == 'final' && !empty($this->_nonDisplayFields
)) {
373 foreach ($this->_nonDisplayFields
as $fieldName) {
374 unset($this->_columnHeaders
[$fieldName]);
378 if (empty($this->_selectAliasesTotal
)) {
379 $this->_selectAliasesTotal
= $this->_selectAliases
;
382 $removeKeys = array();
383 if ($recordType == 'target') {
384 foreach ($this->_selectClauses
as $key => $clause) {
385 if (strstr($clause, 'civicrm_contact_assignee.') ||
386 strstr($clause, 'civicrm_contact_source.') ||
387 strstr($clause, 'civicrm_email_assignee.') ||
388 strstr($clause, 'civicrm_email_source.')
390 $removeKeys[] = $key;
391 unset($this->_selectClauses
[$key]);
395 elseif ($recordType == 'assignee') {
396 foreach ($this->_selectClauses
as $key => $clause) {
397 if (strstr($clause, 'civicrm_contact_target.') ||
398 strstr($clause, 'civicrm_contact_source.') ||
399 strstr($clause, 'civicrm_email_target.') ||
400 strstr($clause, 'civicrm_email_source.')
402 $removeKeys[] = $key;
403 unset($this->_selectClauses
[$key]);
407 elseif ($recordType == 'source') {
408 foreach ($this->_selectClauses
as $key => $clause) {
409 if (strstr($clause, 'civicrm_contact_target.') ||
410 strstr($clause, 'civicrm_contact_assignee.') ||
411 strstr($clause, 'civicrm_email_target.') ||
412 strstr($clause, 'civicrm_email_assignee.')
414 $removeKeys[] = $key;
415 unset($this->_selectClauses
[$key]);
419 elseif ($recordType == 'final') {
420 $this->_selectClauses
= $this->_selectAliasesTotal
;
421 foreach ($this->_selectClauses
as $key => $clause) {
422 if (strstr($clause, 'civicrm_contact_contact_target') ||
423 strstr($clause, 'civicrm_contact_contact_assignee') ||
424 strstr($clause, 'civicrm_contact_contact_source')
426 $this->_selectClauses
[$key] = "GROUP_CONCAT($clause SEPARATOR ';') as $clause";
432 foreach ($removeKeys as $key) {
433 unset($this->_selectAliases
[$key]);
436 if ($recordType != 'final') {
437 foreach ($this->_columns
['civicrm_address']['order_bys'] as $fieldName => $field) {
438 $orderByFld = $this->_columns
['civicrm_address']['order_bys'][$fieldName];
439 $fldInfo = $this->_columns
['civicrm_address']['fields'][$fieldName];
440 $this->_selectAliases
[] = $orderByFld['dbAlias'];
441 $this->_selectClauses
[] = "{$fldInfo['dbAlias']} as {$orderByFld['dbAlias']}";
443 $this->_selectAliases
[] = $this->_aliases
['civicrm_option_value'];
444 $this->_selectClauses
[] = "{$this->_aliases['civicrm_option_value']}.label as {$this->_aliases['civicrm_option_value']}";
445 $this->_selectAliases
= array_unique($this->_selectAliases
);
446 $this->_selectClauses
= array_unique($this->_selectClauses
);
448 $this->_select
= "SELECT " . implode(', ', $this->_selectClauses
) . " ";
455 public function from($recordType) {
456 $activityContacts = CRM_Core_OptionGroup
::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
457 $activityTypeId = CRM_Core_DAO
::getFieldValue("CRM_Core_DAO_OptionGroup", 'activity_type', 'id', 'name');
458 $assigneeID = CRM_Utils_Array
::key('Activity Assignees', $activityContacts);
459 $targetID = CRM_Utils_Array
::key('Activity Targets', $activityContacts);
460 $sourceID = CRM_Utils_Array
::key('Activity Source', $activityContacts);
462 if ($recordType == 'target') {
464 FROM civicrm_activity {$this->_aliases['civicrm_activity']}
465 INNER JOIN civicrm_activity_contact {$this->_aliases['civicrm_activity_contact']}
466 ON {$this->_aliases['civicrm_activity']}.id = {$this->_aliases['civicrm_activity_contact']}.activity_id AND
467 {$this->_aliases['civicrm_activity_contact']}.record_type_id = {$targetID}
468 INNER JOIN civicrm_contact civicrm_contact_target
469 ON {$this->_aliases['civicrm_activity_contact']}.contact_id = civicrm_contact_target.id
472 if ($this->isTableSelected('civicrm_email')) {
474 LEFT JOIN civicrm_email civicrm_email_target
475 ON {$this->_aliases['civicrm_activity_contact']}.contact_id = civicrm_email_target.contact_id AND
476 civicrm_email_target.is_primary = 1";
478 $this->_aliases
['civicrm_contact'] = 'civicrm_contact_target';
481 if ($recordType == 'assignee') {
483 FROM civicrm_activity {$this->_aliases['civicrm_activity']}
484 INNER JOIN civicrm_activity_contact {$this->_aliases['civicrm_activity_contact']}
485 ON {$this->_aliases['civicrm_activity']}.id = {$this->_aliases['civicrm_activity_contact']}.activity_id AND
486 {$this->_aliases['civicrm_activity_contact']}.record_type_id = {$assigneeID}
487 INNER JOIN civicrm_contact civicrm_contact_assignee
488 ON {$this->_aliases['civicrm_activity_contact']}.contact_id = civicrm_contact_assignee.id
491 if ($this->isTableSelected('civicrm_email')) {
493 LEFT JOIN civicrm_email civicrm_email_assignee
494 ON {$this->_aliases['civicrm_activity_contact']}.contact_id = civicrm_email_assignee.contact_id AND
495 civicrm_email_assignee.is_primary = 1";
497 $this->_aliases
['civicrm_contact'] = 'civicrm_contact_assignee';
500 if ($recordType == 'source') {
502 FROM civicrm_activity {$this->_aliases['civicrm_activity']}
503 INNER JOIN civicrm_activity_contact {$this->_aliases['civicrm_activity_contact']}
504 ON {$this->_aliases['civicrm_activity']}.id = {$this->_aliases['civicrm_activity_contact']}.activity_id AND
505 {$this->_aliases['civicrm_activity_contact']}.record_type_id = {$sourceID}
506 INNER JOIN civicrm_contact civicrm_contact_source
507 ON {$this->_aliases['civicrm_activity_contact']}.contact_id = civicrm_contact_source.id
510 if ($this->isTableSelected('civicrm_email')) {
512 LEFT JOIN civicrm_email civicrm_email_source
513 ON {$this->_aliases['civicrm_activity_contact']}.contact_id = civicrm_email_source.contact_id AND
514 civicrm_email_source.is_primary = 1";
516 $this->_aliases
['civicrm_contact'] = 'civicrm_contact_source';
518 $this->_from
.= "INNER JOIN civicrm_option_value {$this->_aliases['civicrm_option_value']}
519 ON {$this->_aliases['civicrm_option_value']}.option_group_id = {$activityTypeId}
520 AND {$this->_aliases['civicrm_option_value']}.value = {$this->_aliases['civicrm_activity']}.activity_type_id";
521 $this->addAddressFromClause();
525 * @param null $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 ($fieldName == 'activity_type_id' &&
563 empty($this->_params
['activity_type_id_value'])
565 $actTypes = array_flip(CRM_Core_PseudoConstant
::activityType(TRUE, FALSE, FALSE, 'label', TRUE));
567 "( {$this->_aliases['civicrm_activity']}.activity_type_id IN (" .
568 implode(',', $actTypes) . ") )";
573 if ($field['name'] == 'current_user') {
574 if (CRM_Utils_Array
::value("{$fieldName}_value", $this->_params
) ==
578 $session = CRM_Core_Session
::singleton();
579 if ($contactID = $session->get('userID')) {
580 $clause = "{$this->_aliases['civicrm_activity_contact']}.activity_id IN
581 (SELECT activity_id FROM civicrm_activity_contact WHERE contact_id = {$contactID})";
591 if (!empty($clause)) {
592 $clauses[] = $clause;
598 if (empty($clauses)) {
599 $this->_where
.= " ";
602 $this->_where
.= " AND " . implode(' AND ', $clauses);
605 if ($this->_aclWhere
) {
606 $this->_where
.= " AND {$this->_aclWhere} ";
610 public function groupBy() {
611 $this->_groupBy
= "GROUP BY {$this->_aliases['civicrm_activity']}.id";
615 * @param string $tableAlias
617 public function buildACLClause($tableAlias = 'contact_a') {
618 //override for ACL( Since Contact may be source
619 //contact/assignee or target also it may be null )
621 if (CRM_Core_Permission
::check('view all contacts')) {
622 $this->_aclFrom
= $this->_aclWhere
= NULL;
626 $session = CRM_Core_Session
::singleton();
627 $contactID = $session->get('userID');
631 $contactID = CRM_Utils_Type
::escape($contactID, 'Integer');
633 CRM_Contact_BAO_Contact_Permission
::cache($contactID);
635 foreach ($tableAlias as $k => $alias) {
636 $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 ";
639 $this->_aclFrom
= implode(" ", $clauses);
640 $this->_aclWhere
= NULL;
644 * @param int $groupID
648 public function add2group($groupID) {
649 if (CRM_Utils_Array
::value("contact_target_op", $this->_params
) == 'nll') {
650 CRM_Core_Error
::fatal(ts('Current filter criteria didn\'t have any target contact to add to group'));
653 $query = "{$this->_select}
654 FROM civireport_activity_temp_target tar
655 GROUP BY civicrm_activity_id {$this->_having} {$this->_orderBy}";
656 $select = 'AS addtogroup_contact_id';
657 $query = str_ireplace('AS civicrm_contact_contact_target_id', $select, $query);
658 $dao = CRM_Core_DAO
::executeQuery($query);
660 $contactIDs = array();
661 // Add resulting contacts to group
662 while ($dao->fetch()) {
663 if ($dao->addtogroup_contact_id
) {
664 $contact_id = explode(';', $dao->addtogroup_contact_id
);
665 if ($contact_id[0]) {
666 $contactIDs[$contact_id[0]] = $contact_id[0];
671 if (!empty($contactIDs)) {
672 CRM_Contact_BAO_GroupContact
::addContactsToGroup($contactIDs, $groupID);
673 CRM_Core_Session
::setStatus(ts("Listed contact(s) have been added to the selected group."), ts('Contacts Added'), 'success');
676 CRM_Core_Session
::setStatus(ts("The listed records(s) cannot be added to the group."));
680 public function postProcess() {
681 $this->beginPostProcess();
683 //Assign those recordtype to array which have filter operator as 'Is not empty' or 'Is empty'
684 $nullFilters = array();
685 foreach (array('target', 'source', 'assignee') as $type) {
686 if (CRM_Utils_Array
::value("contact_{$type}_op", $this->_params
) ==
687 'nnll' ||
!empty($this->_params
["contact_{$type}_value"])
689 $nullFilters[] = " civicrm_contact_contact_{$type}_id IS NOT NULL ";
691 elseif (CRM_Utils_Array
::value("contact_{$type}_op", $this->_params
) ==
694 $nullFilters[] = " civicrm_contact_contact_{$type}_id IS NULL ";
698 // 1. fill temp table with target results
699 $this->buildACLClause(array('civicrm_contact_target'));
700 $this->select('target');
701 $this->from('target');
702 $this->customDataFrom();
703 $this->where('target');
704 $insertCols = implode(',', $this->_selectAliases
);
705 $tempQuery = "CREATE TEMPORARY TABLE civireport_activity_temp_target CHARACTER SET utf8 COLLATE utf8_unicode_ci AS
706 {$this->_select} {$this->_from} {$this->_where} ";
707 CRM_Core_DAO
::executeQuery($tempQuery);
709 // 2. add new columns to hold assignee and source results
710 // fixme: add when required
712 ALTER TABLE civireport_activity_temp_target
713 ADD COLUMN civicrm_contact_contact_assignee VARCHAR(128),
714 ADD COLUMN civicrm_contact_contact_source VARCHAR(128),
715 ADD COLUMN civicrm_contact_contact_assignee_id VARCHAR(128),
716 ADD COLUMN civicrm_contact_contact_source_id VARCHAR(128),
717 ADD COLUMN civicrm_email_contact_assignee_email VARCHAR(128),
718 ADD COLUMN civicrm_email_contact_source_email VARCHAR(128)";
719 CRM_Core_DAO
::executeQuery($tempQuery);
721 // 3. fill temp table with assignee results
722 $this->buildACLClause(array('civicrm_contact_assignee'));
723 $this->select('assignee');
724 $this->from('assignee');
725 $this->customDataFrom();
726 $this->where('assignee');
727 $insertCols = implode(',', $this->_selectAliases
);
728 $tempQuery = "INSERT INTO civireport_activity_temp_target ({$insertCols})
730 {$this->_from} {$this->_where}";
731 CRM_Core_DAO
::executeQuery($tempQuery);
733 // 4. fill temp table with source results
734 $this->buildACLClause(array('civicrm_contact_source'));
735 $this->select('source');
736 $this->from('source');
737 $this->customDataFrom();
738 $this->where('source');
739 $insertCols = implode(',', $this->_selectAliases
);
740 $tempQuery = "INSERT INTO civireport_activity_temp_target ({$insertCols})
742 {$this->_from} {$this->_where}";
743 CRM_Core_DAO
::executeQuery($tempQuery);
745 // 5. show final result set from temp table
747 $this->select('final');
749 if (!empty($nullFilters)) {
750 $this->_having
= "HAVING " . implode(' AND ', $nullFilters);
754 $sql = "{$this->_select}
755 FROM civireport_activity_temp_target tar
756 GROUP BY civicrm_activity_id {$this->_having} {$this->_orderBy} {$this->_limit}";
757 $this->buildRows($sql, $rows);
759 // format result set.
760 $this->formatDisplay($rows);
762 // assign variables to templates
763 $this->doTemplateAssignment($rows);
765 // do print / pdf / instance stuff if needed
766 $this->endPostProcess($rows);
772 public function alterDisplay(&$rows) {
773 // custom code to alter rows
776 $activityType = CRM_Core_PseudoConstant
::activityType(TRUE, TRUE, FALSE, 'label', TRUE);
777 $activityStatus = CRM_Core_PseudoConstant
::activityStatus();
779 $seperator = CRM_Core_DAO
::VALUE_SEPARATOR
;
780 $context = CRM_Utils_Request
::retrieve('context', 'String', $this, FALSE, 'report');
783 if (CRM_Core_Permission
::check('access CiviCRM')) {
785 $onHover = ts('View Contact Summary for this Contact');
786 $onHoverAct = ts('View Activity Record');
789 foreach ($rows as $rowNum => $row) {
790 // if we have an activity type, format the View Activity link for use in various columns
792 array_key_exists('civicrm_activity_activity_type_id', $row)
794 // Check for target contact id(s) and use the first contact id in that list for view activity link if found,
795 // else use source contact id
796 if (!empty($rows[$rowNum]['civicrm_contact_contact_target_id'])) {
797 $targets = explode(';', $rows[$rowNum]['civicrm_contact_contact_target_id']);
801 $cid = $rows[$rowNum]['civicrm_contact_contact_source_id'];
804 $actActionLinks = CRM_Activity_Selector_Activity
::actionLinks($row['civicrm_activity_activity_type_id'],
805 CRM_Utils_Array
::value('civicrm_activity_source_record_id', $rows[$rowNum]),
807 $rows[$rowNum]['civicrm_activity_id']
810 $actLinkValues = array(
811 'id' => $rows[$rowNum]['civicrm_activity_id'],
815 $actUrl = CRM_Utils_System
::url($actActionLinks[CRM_Core_Action
::VIEW
]['url'],
816 CRM_Core_Action
::replace($actActionLinks[CRM_Core_Action
::VIEW
]['qs'], $actLinkValues), TRUE
820 if (array_key_exists('civicrm_contact_contact_source', $row)) {
821 if ($value = $row['civicrm_contact_contact_source_id']) {
823 $url = CRM_Utils_System
::url("civicrm/contact/view",
824 'reset=1&cid=' . $value,
827 $rows[$rowNum]['civicrm_contact_contact_source_link'] = $url;
828 $rows[$rowNum]['civicrm_contact_contact_source_hover'] = $onHover;
834 if (array_key_exists('civicrm_contact_contact_assignee', $row)) {
835 $assigneeNames = explode(';', $row['civicrm_contact_contact_assignee']);
836 if ($value = $row['civicrm_contact_contact_assignee_id']) {
837 $assigneeContactIds = explode(';', $value);
840 foreach ($assigneeContactIds as $id => $value) {
841 if (isset($value) && isset($assigneeNames[$id])) {
842 $url = CRM_Utils_System
::url("civicrm/contact/view",
843 'reset=1&cid=' . $value,
846 $link[] = "<a title='" . $onHover . "' href='" . $url .
847 "'>{$assigneeNames[$id]}</a>";
850 $rows[$rowNum]['civicrm_contact_contact_assignee'] = implode('; ', $link);
856 if (array_key_exists('civicrm_contact_contact_target', $row)) {
857 $targetNames = explode(';', $row['civicrm_contact_contact_target']);
858 if ($value = $row['civicrm_contact_contact_target_id']) {
859 $targetContactIds = explode(';', $value);
862 foreach ($targetContactIds as $id => $value) {
863 if (isset($value) && isset($targetNames[$id])) {
864 $url = CRM_Utils_System
::url("civicrm/contact/view",
865 'reset=1&cid=' . $value,
868 $link[] = "<a title='" . $onHover . "' href='" . $url .
869 "'>{$targetNames[$id]}</a>";
872 $rows[$rowNum]['civicrm_contact_contact_target'] = implode('; ', $link);
878 if (array_key_exists('civicrm_activity_activity_type_id', $row)) {
879 if ($value = $row['civicrm_activity_activity_type_id']) {
880 $rows[$rowNum]['civicrm_activity_activity_type_id'] = $activityType[$value];
882 $rows[$rowNum]['civicrm_activity_activity_type_id_link'] = $actUrl;
883 $rows[$rowNum]['civicrm_activity_activity_type_id_hover'] = $onHoverAct;
889 if (array_key_exists('civicrm_activity_status_id', $row)) {
890 if ($value = $row['civicrm_activity_status_id']) {
891 $rows[$rowNum]['civicrm_activity_status_id'] = $activityStatus[$value];
896 if (array_key_exists('civicrm_activity_details', $row)) {
897 if ($value = $row['civicrm_activity_details']) {
898 $fullDetails = $rows[$rowNum]['civicrm_activity_details'];
899 $rows[$rowNum]['civicrm_activity_details'] = substr($fullDetails, 0, strrpos(substr($fullDetails, 0, 80), ' '));
901 $rows[$rowNum]['civicrm_activity_details'] .= " <a href='{$actUrl}' title='{$onHoverAct}'>(more)</a>";
907 if (array_key_exists('civicrm_activity_campaign_id', $row)) {
908 if ($value = $row['civicrm_activity_campaign_id']) {
909 $rows[$rowNum]['civicrm_activity_campaign_id'] = $this->activeCampaigns
[$value];
914 if (array_key_exists('civicrm_activity_engagement_level', $row)) {
915 if ($value = $row['civicrm_activity_engagement_level']) {
916 $rows[$rowNum]['civicrm_activity_engagement_level'] = $this->engagementLevels
[$value];
921 if (array_key_exists('civicrm_activity_activity_date_time', $row) &&
922 array_key_exists('civicrm_activity_status_id', $row)
924 if (CRM_Utils_Date
::overdue($rows[$rowNum]['civicrm_activity_activity_date_time']) &&
925 $activityStatus[$row['civicrm_activity_status_id']] != 'Completed'
927 $rows[$rowNum]['class'] = "status-overdue";
932 $entryFound = $this->alterDisplayAddressFields($row, $rows, $rowNum, 'activity', 'List all activities for this ') ?
TRUE : $entryFound;
940 public function sectionTotals() {
941 // Reports using order_bys with sections must populate $this->_selectAliases in select() method.
942 if (empty($this->_selectAliases
)) {
946 if (!empty($this->_sections
)) {
947 // pull section aliases out of $this->_sections
948 $sectionAliases = array_keys($this->_sections
);
951 foreach (array_merge($sectionAliases, $this->_selectAliases
) as $alias) {
952 $ifnulls[] = "ifnull($alias, '') as $alias";
955 $query = "select " . implode(", ", $ifnulls) .
956 ", count(DISTINCT civicrm_activity_id) as ct from civireport_activity_temp_target group by " .
957 implode(", ", $sectionAliases);
959 // initialize array of total counts
961 $dao = CRM_Core_DAO
::executeQuery($query);
962 while ($dao->fetch()) {
963 // let $this->_alterDisplay translate any integer ids to human-readable values.
964 $rows[0] = $dao->toArray();
965 $this->alterDisplay($rows);
968 // add totals for all permutations of section values
971 $aliasCount = count($sectionAliases);
972 foreach ($sectionAliases as $alias) {
973 $values[] = $row[$alias];
974 $key = implode(CRM_Core_DAO
::VALUE_SEPARATOR
, $values);
975 if ($i == $aliasCount) {
976 // the last alias is the lowest-level section header; use count as-is
977 $totals[$key] = $dao->ct
;
980 // other aliases are higher level; roll count into their total
981 $totals[$key] +
= $dao->ct
;
985 $this->assign('sectionTotals', $totals);