CRM/Report/Form/Activity.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.5                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2014                                |
   7  +--------------------------------------------------------------------+
   8  | This file is a part of CiviCRM.                                    |
   9  |                                                                    |
  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.   |
  13  |                                                                    |
  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.        |
  18  |                                                                    |
  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  +--------------------------------------------------------------------+
  26 */
  27
  28 /**
  29  *
  30  * @package CRM
  31  * @copyright CiviCRM LLC (c) 2004-2014
  32  * $Id$
  33  *
  34  */
  35 class CRM_Report_Form_Activity extends CRM_Report_Form {
  36   protected $_selectAliasesTotal = array();
  37
  38   protected $_customGroupExtends = array(
  39     'Activity'
  40   );
  41
  42   protected $_nonDisplayFields = array();
  43
  44   /**
  45    *
  46    */
  47   /**
  48    *
  49    */
  50   function __construct() {
  51     // There could be multiple contacts. We not clear on which contact id to display.
  52     // Lets hide it for now.
  53     $this->_exposeContactID = FALSE;
  54
  55     $config = CRM_Core_Config::singleton();
  56     $campaignEnabled = in_array("CiviCampaign", $config->enableComponents);
  57     if ($campaignEnabled) {
  58       $getCampaigns = CRM_Campaign_BAO_Campaign::getPermissionedCampaigns(NULL, NULL, TRUE, FALSE, TRUE);
  59       $this->activeCampaigns = $getCampaigns['campaigns'];
  60       asort($this->activeCampaigns);
  61       $this->engagementLevels = CRM_Campaign_PseudoConstant::engagementLevel();
  62     }
  63     $this->activityTypes = CRM_Core_PseudoConstant::activityType(TRUE, FALSE, FALSE, 'label', TRUE);
  64     asort($this->activityTypes);
  65
  66     $this->_columns = array(
  67       'civicrm_contact' =>
  68       array(
  69         'dao' => 'CRM_Contact_DAO_Contact',
  70         'fields' =>
  71         array(
  72           'contact_source' =>
  73           array(
  74             'name' => 'sort_name',
  75             'title' => ts('Source Contact Name'),
  76             'alias' => 'civicrm_contact_source',
  77             'no_repeat' => TRUE,
  78           ),
  79           'contact_assignee' =>
  80           array(
  81             'name' => 'sort_name',
  82             'title' => ts('Assignee Contact Name'),
  83             'alias' => 'civicrm_contact_assignee',
  84             'dbAlias' => "civicrm_contact_assignee.sort_name",
  85             'default' => TRUE,
  86           ),
  87           'contact_target' =>
  88           array(
  89             'name' => 'sort_name',
  90             'title' => ts('Target Contact Name'),
  91             'alias' => 'civicrm_contact_target',
  92             'dbAlias' => "civicrm_contact_target.sort_name",
  93             'default' => TRUE,
  94           ),
  95          'contact_source_id' =>
  96           array(
  97             'name' => 'id',
  98             'alias' => 'civicrm_contact_source',
  99             'dbAlias' => "civicrm_contact_source.id",
 100             'no_display' => TRUE,
 101             'default' => TRUE,
 102             'required' => TRUE,
 103           ),
 104           'contact_assignee_id' =>
 105           array(
 106             'name' => 'id',
 107             'alias' => 'civicrm_contact_assignee',
 108             'dbAlias' => "civicrm_contact_assignee.id",
 109             'no_display' => TRUE,
 110             'default' => TRUE,
 111             'required' => TRUE,
 112           ),
 113           'contact_target_id' =>
 114           array(
 115             'name' => 'id',
 116             'alias' => 'civicrm_contact_target',
 117             'dbAlias' => "civicrm_contact_target.id",
 118             'no_display' => TRUE,
 119             'default' => TRUE,
 120             'required' => TRUE,
 121           ),
 122         ),
 123         'filters' =>
 124         array(
 125           'contact_source' =>
 126           array(
 127             'name' => 'sort_name',
 128             'alias' => 'civicrm_contact_source',
 129             'title' => ts('Source Contact Name'),
 130             'operator' => 'like',
 131             'type' => CRM_Report_Form::OP_STRING,
 132           ),
 133           'contact_assignee' =>
 134           array(
 135             'name' => 'sort_name',
 136             'alias' => 'civicrm_contact_assignee',
 137             'title' => ts('Assignee Contact Name'),
 138             'operator' => 'like',
 139             'type' => CRM_Report_Form::OP_STRING,
 140           ),
 141           'contact_target' =>
 142           array(
 143             'name' => 'sort_name',
 144             'alias' => 'civicrm_contact_target',
 145             'title' => ts('Target Contact Name'),
 146             'operator' => 'like',
 147             'type' => CRM_Report_Form::OP_STRING,
 148           ),
 149           'current_user' =>
 150           array(
 151             'name' => 'current_user',
 152             'title' => ts('Limit To Current User'),
 153             'type' => CRM_Utils_Type::T_INT,
 154             'operatorType' => CRM_Report_Form::OP_SELECT,
 155             'options' => array('0' => ts('No'), '1' => ts('Yes')),
 156           ),
 157         ),
 158         'grouping' => 'contact-fields',
 159       ),
 160       'civicrm_email' =>
 161       array(
 162         'dao' => 'CRM_Core_DAO_Email',
 163         'fields' =>
 164         array(
 165           'contact_source_email' =>
 166           array(
 167             'name' => 'email',
 168             'title' => ts('Source Contact Email'),
 169             'alias' => 'civicrm_email_source',
 170           ),
 171           'contact_assignee_email' =>
 172           array(
 173             'name' => 'email',
 174             'title' => ts('Assignee Contact Email'),
 175             'alias' => 'civicrm_email_assignee',
 176           ),
 177           'contact_target_email' =>
 178           array(
 179             'name' => 'email',
 180             'title' => ts('Target Contact Email'),
 181             'alias' => 'civicrm_email_target',
 182           ),
 183         ),
 184         'order_bys' =>
 185         array(
 186           'source_contact_email' =>
 187           array(
 188             'name' => 'email',
 189             'title' => ts('Source Contact Email'),
 190             'dbAlias' => 'civicrm_email_contact_source_email',
 191           ),
 192         ),
 193       ),
 194       'civicrm_activity' =>
 195       array(
 196         'dao' => 'CRM_Activity_DAO_Activity',
 197         'fields' =>
 198         array(
 199           'id' =>
 200           array(
 201             'no_display' => TRUE,
 202             'title' => ts('Activity ID'),
 203             'required' => TRUE,
 204           ),
 205           'source_record_id' =>
 206           array(
 207             'no_display' => TRUE,
 208             'required' => TRUE,
 209           ),
 210           'activity_type_id' =>
 211           array('title' => ts('Activity Type'),
 212             'required' => TRUE,
 213             'type' => CRM_Utils_Type::T_STRING,
 214           ),
 215           'activity_subject' =>
 216           array('title' => ts('Subject'),
 217             'default' => TRUE,
 218           ),
 219           'activity_date_time' =>
 220           array('title' => ts('Activity Date'),
 221             'required' => TRUE,
 222           ),
 223           'status_id' =>
 224           array('title' => ts('Activity Status'),
 225             'default' => TRUE,
 226             'type' => CRM_Utils_Type::T_STRING,
 227           ),
 228           'duration' =>
 229           array('title' => ts('Duration'),
 230             'type' => CRM_Utils_Type::T_INT,
 231           ),
 232           'details' => array(
 233             'title' => ts('Activity Details'),
 234           )
 235         ),
 236         'filters' => array(
 237           'activity_date_time' => array(
 238             'default' => 'this.month',
 239             'operatorType' => CRM_Report_Form::OP_DATE,
 240           ),
 241           'activity_subject' =>
 242           array('title' => ts('Activity Subject')),
 243           'activity_type_id' =>
 244           array('title' => ts('Activity Type'),
 245             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 246             'options' => $this->activityTypes,
 247           ),
 248           'status_id' =>
 249           array('title' => ts('Activity Status'),
 250             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 251             'options' => CRM_Core_PseudoConstant::activityStatus(),
 252           ),
 253           'details' => array(
 254             'title' => ts('Activity Details'),
 255             'type' => CRM_Utils_Type::T_TEXT,
 256           )
 257         ),
 258         'order_bys' => array(
 259           'activity_date_time' =>
 260           array('title' => ts('Activity Date'), 'default_weight' => '1', 'dbAlias' => 'civicrm_activity_activity_date_time'),
 261           'activity_type_id' =>
 262           array('title' => ts('Activity Type'), 'default_weight' => '2', 'dbAlias' => 'civicrm_activity_activity_type_id'),
 263         ),
 264         'grouping' => 'activity-fields',
 265         'alias' => 'activity',
 266       ),
 267       'civicrm_activity_contact' =>
 268       array(
 269         'dao' => 'CRM_Activity_DAO_ActivityContact',
 270         'fields' =>
 271         array(
 272           // so we have $this->_alias populated
 273         ),
 274       ),
 275     ) + $this->addAddressFields(FALSE, TRUE);
 276
 277     if ($campaignEnabled) {
 278       // Add display column and filter for Survey Results, Campaign and Engagement Index if CiviCampaign is enabled
 279
 280       $this->_columns['civicrm_activity']['fields']['result'] = array(
 281         'title' => 'Survey Result',
 282         'default' => 'false',
 283       );
 284       $this->_columns['civicrm_activity']['filters']['result'] = array('title' => ts('Survey Result'),
 285         'operator' => 'like',
 286         'type' => CRM_Utils_Type::T_STRING,
 287       );
 288       if (!empty($this->activeCampaigns)) {
 289         $this->_columns['civicrm_activity']['fields']['campaign_id'] = array(
 290           'title' => 'Campaign',
 291           'default' => 'false',
 292         );
 293         $this->_columns['civicrm_activity']['filters']['campaign_id'] = array('title' => ts('Campaign'),
 294           'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 295           'options' => $this->activeCampaigns,
 296         );
 297       }
 298       if (!empty($this->engagementLevels)) {
 299         $this->_columns['civicrm_activity']['fields']['engagement_level'] = array(
 300           'title' => 'Engagement Index',
 301           'default' => 'false',
 302         );
 303         $this->_columns['civicrm_activity']['filters']['engagement_level'] = array('title' => ts('Engagement Index'),
 304           'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 305           'options' => $this->engagementLevels,
 306         );
 307       }
 308     }
 309     $this->_groupFilter = TRUE;
 310     $this->_tagFilter = TRUE;
 311     parent::__construct();
 312   }
 313
 314   /**
 315    * @param null $recordType
 316    */
 317   function select($recordType = NULL) {
 318     if (!array_key_exists("contact_{$recordType}", $this->_params['fields']) && $recordType != 'final') {
 319       $this->_nonDisplayFields[] = "civicrm_contact_contact_{$recordType}";
 320     }
 321     parent::select();
 322
 323     if ($recordType == 'final' && !empty($this->_nonDisplayFields)) {
 324       foreach ($this->_nonDisplayFields as $fieldName) {
 325         unset($this->_columnHeaders[$fieldName]);
 326       }
 327     }
 328
 329     if (empty($this->_selectAliasesTotal)) {
 330       $this->_selectAliasesTotal = $this->_selectAliases;
 331     }
 332
 333     $removeKeys = array();
 334     if ($recordType == 'target') {
 335       foreach ($this->_selectClauses as $key => $clause) {
 336         if (strstr($clause, 'civicrm_contact_assignee.') ||
 337           strstr($clause, 'civicrm_contact_source.') ||
 338           strstr($clause, 'civicrm_email_assignee.') ||
 339           strstr($clause, 'civicrm_email_source.')
 340         ) {
 341           $removeKeys[] = $key;
 342           unset($this->_selectClauses[$key]);
 343         }
 344       }
 345     } else if ($recordType == 'assignee') {
 346       foreach ($this->_selectClauses as $key => $clause) {
 347         if (strstr($clause, 'civicrm_contact_target.') ||
 348           strstr($clause, 'civicrm_contact_source.') ||
 349           strstr($clause, 'civicrm_email_target.') ||
 350           strstr($clause, 'civicrm_email_source.')
 351         ) {
 352           $removeKeys[] = $key;
 353           unset($this->_selectClauses[$key]);
 354         }
 355       }
 356     } else if ($recordType == 'source') {
 357       foreach ($this->_selectClauses as $key => $clause) {
 358         if (strstr($clause, 'civicrm_contact_target.') ||
 359           strstr($clause, 'civicrm_contact_assignee.') ||
 360           strstr($clause, 'civicrm_email_target.') ||
 361           strstr($clause, 'civicrm_email_assignee.')
 362         ) {
 363           $removeKeys[] = $key;
 364           unset($this->_selectClauses[$key]);
 365         }
 366       }
 367     } else if ($recordType == 'final') {
 368       $this->_selectClauses = $this->_selectAliasesTotal;
 369       foreach ($this->_selectClauses as $key => $clause) {
 370         if (strstr($clause, 'civicrm_contact_contact_target') ||
 371           strstr($clause, 'civicrm_contact_contact_assignee') ||
 372           strstr($clause, 'civicrm_contact_contact_source') ) {
 373           $this->_selectClauses[$key] = "GROUP_CONCAT($clause SEPARATOR ';') as $clause";
 374         }
 375       }
 376     }
 377
 378     if ($recordType) {
 379       foreach ($removeKeys as $key) {
 380         unset($this->_selectAliases[$key]);
 381       }
 382
 383       $this->_select = "SELECT " . implode(', ', $this->_selectClauses) . " ";
 384     }
 385   }
 386
 387   /**
 388    * @param $recordType
 389    */
 390   function from($recordType) {
 391     $activityContacts = CRM_Core_OptionGroup::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
 392     $assigneeID = CRM_Utils_Array::key('Activity Assignees', $activityContacts);
 393     $targetID   = CRM_Utils_Array::key('Activity Targets', $activityContacts);
 394     $sourceID   = CRM_Utils_Array::key('Activity Source', $activityContacts);
 395
 396     if ($recordType == 'target') {
 397       $this->_from = "
 398         FROM civicrm_activity {$this->_aliases['civicrm_activity']}
 399              INNER JOIN civicrm_activity_contact  {$this->_aliases['civicrm_activity_contact']}
 400                     ON {$this->_aliases['civicrm_activity']}.id = {$this->_aliases['civicrm_activity_contact']}.activity_id AND
 401                        {$this->_aliases['civicrm_activity_contact']}.record_type_id = {$targetID}
 402              INNER JOIN civicrm_contact civicrm_contact_target
 403                     ON {$this->_aliases['civicrm_activity_contact']}.contact_id = civicrm_contact_target.id
 404              {$this->_aclFrom}";
 405
 406       if ($this->isTableSelected('civicrm_email')) {
 407         $this->_from .= "
 408             LEFT JOIN civicrm_email civicrm_email_target
 409                    ON {$this->_aliases['civicrm_activity_contact']}.contact_id = civicrm_email_target.contact_id AND
 410                       civicrm_email_target.is_primary = 1";
 411       }
 412       $this->_aliases['civicrm_contact'] = 'civicrm_contact_target';
 413     }
 414
 415     if ($recordType == 'assignee') {
 416       $this->_from = "
 417         FROM civicrm_activity {$this->_aliases['civicrm_activity']}
 418              INNER JOIN civicrm_activity_contact {$this->_aliases['civicrm_activity_contact']}
 419                     ON {$this->_aliases['civicrm_activity']}.id = {$this->_aliases['civicrm_activity_contact']}.activity_id AND
 420                        {$this->_aliases['civicrm_activity_contact']}.record_type_id = {$assigneeID}
 421              INNER JOIN civicrm_contact civicrm_contact_assignee
 422                     ON {$this->_aliases['civicrm_activity_contact']}.contact_id = civicrm_contact_assignee.id
 423              {$this->_aclFrom}";
 424
 425       if ($this->isTableSelected('civicrm_email')) {
 426         $this->_from .= "
 427             LEFT JOIN civicrm_email civicrm_email_assignee
 428                    ON {$this->_aliases['civicrm_activity_contact']}.contact_id = civicrm_email_assignee.contact_id AND
 429                       civicrm_email_assignee.is_primary = 1";
 430       }
 431       $this->_aliases['civicrm_contact'] = 'civicrm_contact_assignee';
 432     }
 433
 434     if ($recordType == 'source') {
 435       $this->_from = "
 436         FROM civicrm_activity {$this->_aliases['civicrm_activity']}
 437              INNER JOIN civicrm_activity_contact {$this->_aliases['civicrm_activity_contact']}
 438                     ON {$this->_aliases['civicrm_activity']}.id = {$this->_aliases['civicrm_activity_contact']}.activity_id AND
 439                        {$this->_aliases['civicrm_activity_contact']}.record_type_id = {$sourceID}
 440              INNER JOIN civicrm_contact civicrm_contact_source
 441                     ON {$this->_aliases['civicrm_activity_contact']}.contact_id = civicrm_contact_source.id
 442              {$this->_aclFrom}";
 443
 444       if ($this->isTableSelected('civicrm_email')) {
 445         $this->_from .= "
 446             LEFT JOIN civicrm_email civicrm_email_source
 447                    ON {$this->_aliases['civicrm_activity_contact']}.contact_id = civicrm_email_source.contact_id AND
 448                       civicrm_email_source.is_primary = 1";
 449       }
 450       $this->_aliases['civicrm_contact'] = 'civicrm_contact_source';
 451     }
 452     $this->addAddressFromClause();
 453   }
 454
 455   /**
 456    * @param null $recordType
 457    */
 458   function where($recordType = NULL) {
 459     $this->_where = " WHERE {$this->_aliases['civicrm_activity']}.is_test = 0 AND
 460                                 {$this->_aliases['civicrm_activity']}.is_deleted = 0 AND
 461                                 {$this->_aliases['civicrm_activity']}.is_current_revision = 1";
 462
 463     $clauses = array();
 464     foreach ($this->_columns as $tableName => $table) {
 465       if (array_key_exists('filters', $table)) {
 466
 467         foreach ($table['filters'] as $fieldName => $field) {
 468           $clause = NULL;
 469           if ($fieldName != 'contact_' . $recordType &&
 470             (strstr($fieldName, '_target') ||
 471               strstr($fieldName, '_assignee') ||
 472               strstr($fieldName, '_source')
 473             )
 474           ) {
 475             continue;
 476           }
 477           if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE) {
 478             $relative = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params);
 479             $from     = CRM_Utils_Array::value("{$fieldName}_from", $this->_params);
 480             $to       = CRM_Utils_Array::value("{$fieldName}_to", $this->_params);
 481
 482             $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']);
 483           }
 484           else {
 485             $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
 486             if ($op && ($op != 'nnll' || $op != 'nll')) {
 487               $clause = $this->whereClause($field,
 488                 $op,
 489                 CRM_Utils_Array::value("{$fieldName}_value", $this->_params),
 490                 CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
 491                 CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
 492               );
 493             }
 494           }
 495
 496           if ($field['name'] == 'current_user') {
 497             if (CRM_Utils_Array::value("{$fieldName}_value", $this->_params) == 1) {
 498               // get current user
 499               $session = CRM_Core_Session::singleton();
 500               if ($contactID = $session->get('userID')) {
 501                 $clause = "{$this->_aliases['civicrm_activity_contact']}.activity_id IN
 502                            (SELECT activity_id FROM civicrm_activity_contact WHERE contact_id = {$contactID})";
 503               }
 504               else {
 505                 $clause = NULL;
 506               }
 507             }
 508             else {
 509               $clause = NULL;
 510             }
 511           }
 512           if (!empty($clause)) {
 513             $clauses[] = $clause;
 514           }
 515         }
 516       }
 517     }
 518
 519     if (empty($clauses)) {
 520       $this->_where .= " ";
 521     }
 522     else {
 523       $this->_where .= " AND " . implode(' AND ', $clauses);
 524     }
 525
 526     if ($this->_aclWhere) {
 527       $this->_where .= " AND {$this->_aclWhere} ";
 528     }
 529   }
 530
 531   function groupBy() {
 532     $this->_groupBy = "GROUP BY {$this->_aliases['civicrm_activity']}.id";
 533   }
 534
 535   /**
 536    * @param string $tableAlias
 537    */
 538   function buildACLClause($tableAlias = 'contact_a') {
 539     //override for ACL( Since Contact may be source
 540     //contact/assignee or target also it may be null )
 541
 542     if (CRM_Core_Permission::check('view all contacts')) {
 543       $this->_aclFrom = $this->_aclWhere = NULL;
 544       return;
 545     }
 546
 547     $session = CRM_Core_Session::singleton();
 548     $contactID = $session->get('userID');
 549     if (!$contactID) {
 550       $contactID = 0;
 551     }
 552     $contactID = CRM_Utils_Type::escape($contactID, 'Integer');
 553
 554     CRM_Contact_BAO_Contact_Permission::cache($contactID);
 555     $clauses = array();
 556     foreach ($tableAlias as $k => $alias) {
 557       $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 ";
 558     }
 559
 560     $this->_aclFrom = implode(" ", $clauses);
 561     $this->_aclWhere = NULL;
 562   }
 563
 564   /**
 565    * @param $groupID
 566    *
 567    * @throws Exception
 568    */
 569   function add2group($groupID) {
 570     if (CRM_Utils_Array::value("contact_target_op", $this->_params) == 'nll') {
 571       CRM_Core_Error::fatal(ts('Current filter criteria didn\'t have any target contact to add to group'));
 572     }
 573
 574     $query = "{$this->_select}
 575 FROM civireport_activity_temp_target tar
 576 GROUP BY civicrm_activity_id {$this->_having} {$this->_orderBy}";
 577     $select = 'AS addtogroup_contact_id';
 578     $query = str_ireplace('AS civicrm_contact_contact_target_id', $select, $query);
 579     $dao = CRM_Core_DAO::executeQuery($query);
 580
 581     $contactIDs = array();
 582     // Add resulting contacts to group
 583     while ($dao->fetch()) {
 584       if ($dao->addtogroup_contact_id) {
 585         $contact_id = explode(';', $dao->addtogroup_contact_id);
 586         if ($contact_id[0]) {
 587           $contactIDs[$contact_id[0]] = $contact_id[0];
 588         }
 589       }
 590     }
 591
 592     if ( !empty($contactIDs) ) {
 593       CRM_Contact_BAO_GroupContact::addContactsToGroup($contactIDs, $groupID);
 594       CRM_Core_Session::setStatus(ts("Listed contact(s) have been added to the selected group."), ts('Contacts Added'), 'success');
 595     }
 596     else {
 597       CRM_Core_Session::setStatus(ts("The listed records(s) cannot be added to the group."));
 598     }
 599   }
 600
 601   function postProcess() {
 602     $this->beginPostProcess();
 603
 604     //Assign those recordtype to array which have filter operator as 'Is not empty' or 'Is empty'
 605     $nullFilters = array();
 606     foreach (array('target', 'source', 'assignee') as $type) {
 607       if (CRM_Utils_Array::value("contact_{$type}_op", $this->_params) == 'nnll' || !empty($this->_params["contact_{$type}_value"])) {
 608         $nullFilters[] = " civicrm_contact_contact_{$type}_id IS NOT NULL ";
 609       }
 610       else if (CRM_Utils_Array::value("contact_{$type}_op", $this->_params) == 'nll') {
 611         $nullFilters[] = " civicrm_contact_contact_{$type}_id IS NULL ";
 612       }
 613     }
 614
 615       // 1. fill temp table with target results
 616     $this->buildACLClause(array('civicrm_contact_target'));
 617     $this->select('target');
 618     $this->from('target');
 619     $this->customDataFrom();
 620     $this->where('target');
 621     $insertCols = implode(',', $this->_selectAliases);
 622     $tempQuery  = "CREATE TEMPORARY TABLE civireport_activity_temp_target CHARACTER SET utf8 COLLATE utf8_unicode_ci AS
 623 {$this->_select} {$this->_from} {$this->_where} ";
 624     CRM_Core_DAO::executeQuery($tempQuery);
 625
 626     // 2. add new columns to hold assignee and source results
 627     // fixme: add when required
 628     $tempQuery = "
 629   ALTER TABLE  civireport_activity_temp_target
 630   ADD COLUMN civicrm_contact_contact_assignee VARCHAR(128),
 631   ADD COLUMN civicrm_contact_contact_source VARCHAR(128),
 632   ADD COLUMN civicrm_contact_contact_assignee_id VARCHAR(128),
 633   ADD COLUMN civicrm_contact_contact_source_id VARCHAR(128),
 634   ADD COLUMN civicrm_email_contact_assignee_email VARCHAR(128),
 635   ADD COLUMN civicrm_email_contact_source_email VARCHAR(128)";
 636     CRM_Core_DAO::executeQuery($tempQuery);
 637
 638     // 3. fill temp table with assignee results
 639     $this->buildACLClause(array('civicrm_contact_assignee'));
 640     $this->select('assignee');
 641     $this->from('assignee');
 642     $this->customDataFrom();
 643     $this->where('assignee');
 644     $insertCols = implode(',', $this->_selectAliases);
 645     $tempQuery  = "INSERT INTO civireport_activity_temp_target ({$insertCols})
 646 {$this->_select}
 647 {$this->_from} {$this->_where}";
 648     CRM_Core_DAO::executeQuery($tempQuery);
 649
 650     // 4. fill temp table with source results
 651     $this->buildACLClause(array('civicrm_contact_source'));
 652     $this->select('source');
 653     $this->from('source');
 654     $this->customDataFrom();
 655     $this->where('source');
 656     $insertCols = implode(',', $this->_selectAliases);
 657     $tempQuery  = "INSERT INTO civireport_activity_temp_target ({$insertCols})
 658 {$this->_select}
 659 {$this->_from} {$this->_where}";
 660     CRM_Core_DAO::executeQuery($tempQuery);
 661
 662     // 5. show final result set from temp table
 663     $rows = array();
 664     $this->select('final');
 665     $this->_having = "";
 666     if (!empty($nullFilters)) {
 667       $this->_having = "HAVING " . implode(' AND ', $nullFilters);
 668     }
 669     $this->orderBy();
 670     $this->limit();
 671     $sql = "{$this->_select}
 672 FROM civireport_activity_temp_target tar
 673 GROUP BY civicrm_activity_id {$this->_having} {$this->_orderBy} {$this->_limit}";
 674     $this->buildRows($sql, $rows);
 675
 676     // format result set.
 677     $this->formatDisplay($rows);
 678
 679     // assign variables to templates
 680     $this->doTemplateAssignment($rows);
 681
 682     // do print / pdf / instance stuff if needed
 683     $this->endPostProcess($rows);
 684   }
 685
 686   /**
 687    * @param $rows
 688    */
 689   function alterDisplay(&$rows) {
 690     // custom code to alter rows
 691
 692     $entryFound     = FALSE;
 693     $activityType   = CRM_Core_PseudoConstant::activityType(TRUE, TRUE, FALSE, 'label', TRUE);
 694     $activityStatus = CRM_Core_PseudoConstant::activityStatus();
 695     $viewLinks      = FALSE;
 696     $seperator      = CRM_Core_DAO::VALUE_SEPARATOR;
 697     $context        = CRM_Utils_Request::retrieve('context', 'String', $this, FALSE, 'report');
 698     $actUrl         = '';
 699
 700     if (CRM_Core_Permission::check('access CiviCRM')) {
 701       $viewLinks  = TRUE;
 702       $onHover    = ts('View Contact Summary for this Contact');
 703       $onHoverAct = ts('View Activity Record');
 704     }
 705
 706     foreach ($rows as $rowNum => $row) {
 707       // if we have an activity type, format the View Activity link for use in various columns
 708       if ($viewLinks && array_key_exists('civicrm_activity_activity_type_id', $row)) {
 709         // Check for target contact id(s) and use the first contact id in that list for view activity link if found,
 710         // else use source contact id
 711         if (!empty($rows[$rowNum]['civicrm_contact_contact_target_id'])) {
 712           $targets = explode(';', $rows[$rowNum]['civicrm_contact_contact_target_id']);
 713           $cid = $targets[0];
 714         }
 715         else {
 716           $cid = $rows[$rowNum]['civicrm_contact_contact_source_id'];
 717         }
 718
 719         $actActionLinks = CRM_Activity_Selector_Activity::actionLinks($row['civicrm_activity_activity_type_id'],
 720           CRM_Utils_Array::value('civicrm_activity_source_record_id', $rows[$rowNum]),
 721           FALSE,
 722           $rows[$rowNum]['civicrm_activity_id']
 723         );
 724
 725         $actLinkValues = array(
 726           'id' => $rows[$rowNum]['civicrm_activity_id'],
 727           'cid' => $cid,
 728           'cxt' => $context,
 729         );
 730         $actUrl = CRM_Utils_System::url($actActionLinks[CRM_Core_Action::VIEW]['url'],
 731           CRM_Core_Action::replace($actActionLinks[CRM_Core_Action::VIEW]['qs'], $actLinkValues), TRUE
 732         );
 733       }
 734
 735       if (array_key_exists('civicrm_contact_contact_source', $row)) {
 736         if ($value = $row['civicrm_contact_contact_source_id']) {
 737           if ($viewLinks) {
 738             $url = CRM_Utils_System::url("civicrm/contact/view",
 739               'reset=1&cid=' . $value,
 740               $this->_absoluteUrl
 741             );
 742             $rows[$rowNum]['civicrm_contact_contact_source_link'] = $url;
 743             $rows[$rowNum]['civicrm_contact_contact_source_hover'] = $onHover;
 744           }
 745           $entryFound = TRUE;
 746         }
 747       }
 748
 749       if (array_key_exists('civicrm_contact_contact_assignee', $row)) {
 750         $assigneeNames = explode(';', $row['civicrm_contact_contact_assignee']);
 751         if ($value = $row['civicrm_contact_contact_assignee_id']) {
 752           $assigneeContactIds = explode(';', $value);
 753           $link = array();
 754           if ($viewLinks) {
 755             foreach ($assigneeContactIds as $id => $value) {
 756               if (isset($value) && isset($assigneeNames[$id])) {
 757                 $url = CRM_Utils_System::url("civicrm/contact/view",
 758                   'reset=1&cid=' . $value,
 759                   $this->_absoluteUrl
 760                 );
 761                 $link[] = "<a title='" . $onHover . "' href='" . $url . "'>{$assigneeNames[$id]}</a>";
 762               }
 763             }
 764             $rows[$rowNum]['civicrm_contact_contact_assignee'] = implode('; ', $link);
 765           }
 766           $entryFound = TRUE;
 767         }
 768       }
 769
 770       if (array_key_exists('civicrm_contact_contact_target', $row)) {
 771         $targetNames = explode(';', $row['civicrm_contact_contact_target']);
 772         if ($value = $row['civicrm_contact_contact_target_id']) {
 773           $targetContactIds = explode(';', $value);
 774           $link = array();
 775           if ($viewLinks) {
 776             foreach ($targetContactIds as $id => $value) {
 777               if (isset($value) && isset($targetNames[$id])) {
 778                 $url = CRM_Utils_System::url("civicrm/contact/view",
 779                   'reset=1&cid=' . $value,
 780                   $this->_absoluteUrl
 781                 );
 782                 $link[] = "<a title='" . $onHover . "' href='" . $url . "'>{$targetNames[$id]}</a>";
 783               }
 784             }
 785             $rows[$rowNum]['civicrm_contact_contact_target'] = implode('; ', $link);
 786           }
 787           $entryFound = TRUE;
 788         }
 789       }
 790
 791       if (array_key_exists('civicrm_activity_activity_type_id', $row)) {
 792         if ($value = $row['civicrm_activity_activity_type_id']) {
 793           $rows[$rowNum]['civicrm_activity_activity_type_id'] = $activityType[$value];
 794           if ($viewLinks) {
 795             $rows[$rowNum]['civicrm_activity_activity_type_id_link'] = $actUrl;
 796             $rows[$rowNum]['civicrm_activity_activity_type_id_hover'] = $onHoverAct;
 797           }
 798           $entryFound = TRUE;
 799         }
 800       }
 801
 802       if (array_key_exists('civicrm_activity_status_id', $row)) {
 803         if ($value = $row['civicrm_activity_status_id']) {
 804           $rows[$rowNum]['civicrm_activity_status_id'] = $activityStatus[$value];
 805           $entryFound = TRUE;
 806         }
 807       }
 808
 809       if (array_key_exists('civicrm_activity_details', $row)) {
 810         if ($value = $row['civicrm_activity_details']) {
 811           $fullDetails = $rows[$rowNum]['civicrm_activity_details'];
 812           $rows[$rowNum]['civicrm_activity_details'] = substr($fullDetails, 0, strrpos(substr($fullDetails, 0, 80), ' '));
 813           if ($actUrl) {
 814             $rows[$rowNum]['civicrm_activity_details'] .= " <a href='{$actUrl}' title='{$onHoverAct}'>(more)</a>";
 815           }
 816           $entryFound = TRUE;
 817         }
 818       }
 819
 820       if (array_key_exists('civicrm_activity_campaign_id', $row)) {
 821         if ($value = $row['civicrm_activity_campaign_id']) {
 822           $rows[$rowNum]['civicrm_activity_campaign_id'] = $this->activeCampaigns[$value];
 823           $entryFound = TRUE;
 824         }
 825       }
 826
 827       if (array_key_exists('civicrm_activity_engagement_level', $row)) {
 828         if ($value = $row['civicrm_activity_engagement_level']) {
 829           $rows[$rowNum]['civicrm_activity_engagement_level'] = $this->engagementLevels[$value];
 830           $entryFound = TRUE;
 831         }
 832       }
 833
 834       if (array_key_exists('civicrm_activity_activity_date_time', $row) && array_key_exists('civicrm_activity_status_id', $row)) {
 835         if (CRM_Utils_Date::overdue($rows[$rowNum]['civicrm_activity_activity_date_time']) &&
 836           $activityStatus[$row['civicrm_activity_status_id']] != 'Completed'
 837         ) {
 838           $rows[$rowNum]['class'] = "status-overdue";
 839           $entryFound = TRUE;
 840         }
 841       }
 842
 843       $entryFound = $this->alterDisplayAddressFields($row, $rows, $rowNum, 'activity', 'List all activities for this ') ? TRUE : $entryFound;
 844
 845       if (!$entryFound) {
 846         break;
 847       }
 848     }
 849   }
 850 }
 851