CRM/Report/Form/Activity.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.6                                                |
   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    * Class constructor.
  46    */
  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;
  51
  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();
  59     }
  60     $this->activityTypes = CRM_Core_PseudoConstant::activityType(TRUE, FALSE, FALSE, 'label', TRUE);
  61     asort($this->activityTypes);
  62
  63     $this->_columns = array(
  64       'civicrm_contact' => array(
  65         'dao' => 'CRM_Contact_DAO_Contact',
  66         'fields' => array(
  67           'contact_source' => array(
  68             'name' => 'sort_name',
  69             'title' => ts('Source Contact Name'),
  70             'alias' => 'civicrm_contact_source',
  71             'no_repeat' => TRUE,
  72           ),
  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",
  78             'default' => TRUE,
  79           ),
  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",
  85             'default' => TRUE,
  86           ),
  87           'contact_source_id' => array(
  88             'name' => 'id',
  89             'alias' => 'civicrm_contact_source',
  90             'dbAlias' => "civicrm_contact_source.id",
  91             'no_display' => TRUE,
  92             'default' => TRUE,
  93             'required' => TRUE,
  94           ),
  95           'contact_assignee_id' => array(
  96             'name' => 'id',
  97             'alias' => 'civicrm_contact_assignee',
  98             'dbAlias' => "civicrm_contact_assignee.id",
  99             'no_display' => TRUE,
 100             'default' => TRUE,
 101             'required' => TRUE,
 102           ),
 103           'contact_target_id' => array(
 104             'name' => 'id',
 105             'alias' => 'civicrm_contact_target',
 106             'dbAlias' => "civicrm_contact_target.id",
 107             'no_display' => TRUE,
 108             'default' => TRUE,
 109             'required' => TRUE,
 110           ),
 111         ),
 112         'filters' => array(
 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,
 119           ),
 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,
 126           ),
 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,
 133           ),
 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')),
 140           ),
 141         ),
 142         'grouping' => 'contact-fields',
 143       ),
 144       'civicrm_email' => array(
 145         'dao' => 'CRM_Core_DAO_Email',
 146         'fields' => array(
 147           'contact_source_email' => array(
 148             'name' => 'email',
 149             'title' => ts('Source Contact Email'),
 150             'alias' => 'civicrm_email_source',
 151           ),
 152           'contact_assignee_email' => array(
 153             'name' => 'email',
 154             'title' => ts('Assignee Contact Email'),
 155             'alias' => 'civicrm_email_assignee',
 156           ),
 157           'contact_target_email' => array(
 158             'name' => 'email',
 159             'title' => ts('Target Contact Email'),
 160             'alias' => 'civicrm_email_target',
 161           ),
 162         ),
 163         'order_bys' => array(
 164           'source_contact_email' => array(
 165             'name' => 'email',
 166             'title' => ts('Source Contact Email'),
 167             'dbAlias' => 'civicrm_email_contact_source_email',
 168           ),
 169         ),
 170       ),
 171       'civicrm_activity' => array(
 172         'dao' => 'CRM_Activity_DAO_Activity',
 173         'fields' => array(
 174           'id' => array(
 175             'no_display' => TRUE,
 176             'title' => ts('Activity ID'),
 177             'required' => TRUE,
 178           ),
 179           'source_record_id' => array(
 180             'no_display' => TRUE,
 181             'required' => TRUE,
 182           ),
 183           'activity_type_id' => array(
 184             'title' => ts('Activity Type'),
 185             'required' => TRUE,
 186             'type' => CRM_Utils_Type::T_STRING,
 187           ),
 188           'activity_subject' => array(
 189             'title' => ts('Subject'),
 190             'default' => TRUE,
 191           ),
 192           'activity_date_time' => array(
 193             'title' => ts('Activity Date'),
 194             'required' => TRUE,
 195           ),
 196           'status_id' => array(
 197             'title' => ts('Activity Status'),
 198             'default' => TRUE,
 199             'type' => CRM_Utils_Type::T_STRING,
 200           ),
 201           'duration' => array(
 202             'title' => ts('Duration'),
 203             'type' => CRM_Utils_Type::T_INT,
 204           ),
 205           'details' => array(
 206             'title' => ts('Activity Details'),
 207           ),
 208         ),
 209         'filters' => array(
 210           'activity_date_time' => array(
 211             'default' => 'this.month',
 212             'operatorType' => CRM_Report_Form::OP_DATE,
 213           ),
 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,
 219           ),
 220           'status_id' => array(
 221             'title' => ts('Activity Status'),
 222             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 223             'options' => CRM_Core_PseudoConstant::activityStatus(),
 224           ),
 225           'details' => array(
 226             'title' => ts('Activity Details'),
 227             'type' => CRM_Utils_Type::T_TEXT,
 228           ),
 229         ),
 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',
 235           ),
 236           'activity_type_id' => array(
 237             'title' => ts('Activity Type'),
 238             'default_weight' => '2',
 239             'dbAlias' => "option_value_civireport",
 240           ),
 241         ),
 242         'grouping' => 'activity-fields',
 243         'alias' => 'activity',
 244       ),
 245       'civicrm_activity_contact' => array(
 246         'dao' => 'CRM_Activity_DAO_ActivityContact',
 247         'fields' => array(// so we have $this->_alias populated
 248         ),
 249       ),
 250       'civicrm_option_value' => array(
 251         'dao' => 'CRM_Core_DAO_OptionValue',
 252         'fields' => array(// so we have $this->_alias populated
 253         ),
 254         'status_id' =>
 255         array(
 256           'title' => ts('Activity Status'),
 257           'default' => TRUE,
 258           'type' => CRM_Utils_Type::T_STRING,
 259         ),
 260         'duration' =>
 261         array(
 262           'title' => ts('Duration'),
 263           'type' => CRM_Utils_Type::T_INT,
 264         ),
 265         'location' =>
 266         array(
 267           'title' => ts('Location'),
 268           'type' => CRM_Utils_Type::T_STRING,
 269         ),
 270         'details' => array(
 271           'title' => ts('Activity Details'),
 272         ),
 273       ),
 274       'filters' => array(
 275         'activity_date_time' => array(
 276           'default' => 'this.month',
 277           'operatorType' => CRM_Report_Form::OP_DATE,
 278         ),
 279         'activity_subject' =>
 280         array('title' => ts('Activity Subject')),
 281         'location' =>
 282         array(
 283           'title' => ts('Location'),
 284           'type' => CRM_Utils_Type::T_TEXT,
 285         ),
 286         'activity_type_id' =>
 287         array(
 288           'title' => ts('Activity Type'),
 289           'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 290           'options' => $this->activityTypes,
 291         ),
 292         'status_id' =>
 293         array(
 294           'title' => ts('Activity Status'),
 295           'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 296           'options' => CRM_Core_PseudoConstant::activityStatus(),
 297         ),
 298         'details' => array(
 299           'title' => ts('Activity Details'),
 300           'type' => CRM_Utils_Type::T_TEXT,
 301         ),
 302       ),
 303     ) + $this->addressFields(TRUE);
 304
 305     if ($campaignEnabled) {
 306       // Add display column and filter for Survey Results, Campaign and Engagement Index if CiviCampaign is enabled
 307
 308       $this->_columns['civicrm_activity']['fields']['result'] = array(
 309         'title' => 'Survey Result',
 310         'default' => 'false',
 311       );
 312       $this->_columns['civicrm_activity']['filters']['result'] = array(
 313         'title' => ts('Survey Result'),
 314         'operator' => 'like',
 315         'type' => CRM_Utils_Type::T_STRING,
 316       );
 317       if (!empty($this->activeCampaigns)) {
 318         $this->_columns['civicrm_activity']['fields']['campaign_id'] = array(
 319           'title' => 'Campaign',
 320           'default' => 'false',
 321         );
 322         $this->_columns['civicrm_activity']['filters']['campaign_id'] = array(
 323           'title' => ts('Campaign'),
 324           'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 325           'options' => $this->activeCampaigns,
 326         );
 327       }
 328       if (!empty($this->engagementLevels)) {
 329         $this->_columns['civicrm_activity']['fields']['engagement_level'] = array(
 330           'title' => 'Engagement Index',
 331           'default' => 'false',
 332         );
 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,
 337         );
 338       }
 339     }
 340     $this->_groupFilter = TRUE;
 341     $this->_tagFilter = TRUE;
 342     $this->_tagFilterTable = 'civicrm_activity';
 343     parent::__construct();
 344   }
 345
 346   /**
 347    * Adding address fields with dbAlias for order clause.
 348    *
 349    * @param bool $orderBy
 350    *
 351    * @return array
 352    *   Address fields
 353    */
 354   public function addressFields($orderBy = FALSE) {
 355     $address = parent::addAddressFields(FALSE, TRUE);
 356     if ($orderBy) {
 357       foreach ($address['civicrm_address']['order_bys'] as $fieldName => $field) {
 358         $address['civicrm_address']['order_bys'][$fieldName]['dbAlias'] = "civicrm_address_{$fieldName}";
 359       }
 360     }
 361     return $address;
 362   }
 363
 364   /**
 365    * @param null $recordType
 366    */
 367   public function select($recordType = NULL) {
 368     if (!array_key_exists("contact_{$recordType}", $this->_params['fields']) &&
 369       $recordType != 'final'
 370     ) {
 371       $this->_nonDisplayFields[] = "civicrm_contact_contact_{$recordType}";
 372     }
 373     parent::select();
 374
 375     if ($recordType == 'final' && !empty($this->_nonDisplayFields)) {
 376       foreach ($this->_nonDisplayFields as $fieldName) {
 377         unset($this->_columnHeaders[$fieldName]);
 378       }
 379     }
 380
 381     if (empty($this->_selectAliasesTotal)) {
 382       $this->_selectAliasesTotal = $this->_selectAliases;
 383     }
 384
 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.')
 392         ) {
 393           $removeKeys[] = $key;
 394           unset($this->_selectClauses[$key]);
 395         }
 396       }
 397     }
 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.')
 404         ) {
 405           $removeKeys[] = $key;
 406           unset($this->_selectClauses[$key]);
 407         }
 408       }
 409     }
 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.')
 416         ) {
 417           $removeKeys[] = $key;
 418           unset($this->_selectClauses[$key]);
 419         }
 420       }
 421     }
 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')
 428         ) {
 429           $this->_selectClauses[$key] = "GROUP_CONCAT($clause SEPARATOR ';') as $clause";
 430         }
 431       }
 432     }
 433
 434     if ($recordType) {
 435       foreach ($removeKeys as $key) {
 436         unset($this->_selectAliases[$key]);
 437       }
 438
 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']}";
 445         }
 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);
 450       }
 451       $this->_select = "SELECT " . implode(', ', $this->_selectClauses) . " ";
 452     }
 453   }
 454
 455   /**
 456    * @param $recordType
 457    */
 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);
 464
 465     if ($recordType == 'target') {
 466       $this->_from = "
 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
 473              {$this->_aclFrom}";
 474
 475       if ($this->isTableSelected('civicrm_email')) {
 476         $this->_from .= "
 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";
 480       }
 481       $this->_aliases['civicrm_contact'] = 'civicrm_contact_target';
 482     }
 483
 484     if ($recordType == 'assignee') {
 485       $this->_from = "
 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
 492              {$this->_aclFrom}";
 493
 494       if ($this->isTableSelected('civicrm_email')) {
 495         $this->_from .= "
 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";
 499       }
 500       $this->_aliases['civicrm_contact'] = 'civicrm_contact_assignee';
 501     }
 502
 503     if ($recordType == 'source') {
 504       $this->_from = "
 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
 511              {$this->_aclFrom}";
 512
 513       if ($this->isTableSelected('civicrm_email')) {
 514         $this->_from .= "
 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";
 518       }
 519       $this->_aliases['civicrm_contact'] = 'civicrm_contact_source';
 520     }
 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();
 525   }
 526
 527   /**
 528    * @param null $recordType
 529    */
 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";
 534
 535     $clauses = array();
 536     foreach ($this->_columns as $tableName => $table) {
 537       if (array_key_exists('filters', $table)) {
 538
 539         foreach ($table['filters'] as $fieldName => $field) {
 540           $clause = NULL;
 541           if ($fieldName != 'contact_' . $recordType &&
 542             (strstr($fieldName, '_target') ||
 543               strstr($fieldName, '_assignee') ||
 544               strstr($fieldName, '_source')
 545             )
 546           ) {
 547             continue;
 548           }
 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);
 553
 554             $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']);
 555           }
 556           else {
 557             $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
 558             if ($op && ($op != 'nnll' || $op != 'nll')) {
 559               $clause = $this->whereClause($field,
 560                 $op,
 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)
 564               );
 565               if ($fieldName == 'activity_type_id' &&
 566                 empty($this->_params['activity_type_id_value'])
 567               ) {
 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) . ") )";
 571               }
 572             }
 573           }
 574
 575           if ($field['name'] == 'current_user') {
 576             if (CRM_Utils_Array::value("{$fieldName}_value", $this->_params) ==
 577               1
 578             ) {
 579               // get current user
 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})";
 584               }
 585               else {
 586                 $clause = NULL;
 587               }
 588             }
 589             else {
 590               $clause = NULL;
 591             }
 592           }
 593           if (!empty($clause)) {
 594             $clauses[] = $clause;
 595           }
 596         }
 597       }
 598     }
 599
 600     if (empty($clauses)) {
 601       $this->_where .= " ";
 602     }
 603     else {
 604       $this->_where .= " AND " . implode(' AND ', $clauses);
 605     }
 606
 607     if ($this->_aclWhere) {
 608       $this->_where .= " AND {$this->_aclWhere} ";
 609     }
 610   }
 611
 612   public function groupBy() {
 613     $this->_groupBy = "GROUP BY {$this->_aliases['civicrm_activity']}.id";
 614   }
 615
 616   /**
 617    * @param string $tableAlias
 618    */
 619   public function buildACLClause($tableAlias = 'contact_a') {
 620     //override for ACL( Since Contact may be source
 621     //contact/assignee or target also it may be null )
 622
 623     if (CRM_Core_Permission::check('view all contacts')) {
 624       $this->_aclFrom = $this->_aclWhere = NULL;
 625       return;
 626     }
 627
 628     $session = CRM_Core_Session::singleton();
 629     $contactID = $session->get('userID');
 630     if (!$contactID) {
 631       $contactID = 0;
 632     }
 633     $contactID = CRM_Utils_Type::escape($contactID, 'Integer');
 634
 635     CRM_Contact_BAO_Contact_Permission::cache($contactID);
 636     $clauses = array();
 637     foreach ($tableAlias as $k => $alias) {
 638       $clauses[] = " INNER JOIN civicrm_acl_contact_cache aclContactCache_{$k} ON ( {$alias}.id = aclContactCache_{$k}.contact_id OR {$alias}.id IS NULL ) AND aclContactCache_{$k}.user_id = $contactID ";
 639     }
 640
 641     $this->_aclFrom = implode(" ", $clauses);
 642     $this->_aclWhere = NULL;
 643   }
 644
 645   /**
 646    * @param int $groupID
 647    *
 648    * @throws Exception
 649    */
 650   public function add2group($groupID) {
 651     if (CRM_Utils_Array::value("contact_target_op", $this->_params) == 'nll') {
 652       CRM_Core_Error::fatal(ts('Current filter criteria didn\'t have any target contact to add to group'));
 653     }
 654
 655     $query = "{$this->_select}
 656 FROM civireport_activity_temp_target tar
 657 GROUP BY civicrm_activity_id {$this->_having} {$this->_orderBy}";
 658     $select = 'AS addtogroup_contact_id';
 659     $query = str_ireplace('AS civicrm_contact_contact_target_id', $select, $query);
 660     $dao = CRM_Core_DAO::executeQuery($query);
 661
 662     $contactIDs = array();
 663     // Add resulting contacts to group
 664     while ($dao->fetch()) {
 665       if ($dao->addtogroup_contact_id) {
 666         $contact_id = explode(';', $dao->addtogroup_contact_id);
 667         if ($contact_id[0]) {
 668           $contactIDs[$contact_id[0]] = $contact_id[0];
 669         }
 670       }
 671     }
 672
 673     if (!empty($contactIDs)) {
 674       CRM_Contact_BAO_GroupContact::addContactsToGroup($contactIDs, $groupID);
 675       CRM_Core_Session::setStatus(ts("Listed contact(s) have been added to the selected group."), ts('Contacts Added'), 'success');
 676     }
 677     else {
 678       CRM_Core_Session::setStatus(ts("The listed records(s) cannot be added to the group."));
 679     }
 680   }
 681
 682   public function postProcess() {
 683     $this->beginPostProcess();
 684
 685     //Assign those recordtype to array which have filter operator as 'Is not empty' or 'Is empty'
 686     $nullFilters = array();
 687     foreach (array('target', 'source', 'assignee') as $type) {
 688       if (CRM_Utils_Array::value("contact_{$type}_op", $this->_params) ==
 689         'nnll' || !empty($this->_params["contact_{$type}_value"])
 690       ) {
 691         $nullFilters[] = " civicrm_contact_contact_{$type}_id IS NOT NULL ";
 692       }
 693       elseif (CRM_Utils_Array::value("contact_{$type}_op", $this->_params) ==
 694         'nll'
 695       ) {
 696         $nullFilters[] = " civicrm_contact_contact_{$type}_id IS NULL ";
 697       }
 698     }
 699
 700     // 1. fill temp table with target results
 701     $this->buildACLClause(array('civicrm_contact_target'));
 702     $this->select('target');
 703     $this->from('target');
 704     $this->customDataFrom();
 705     $this->where('target');
 706     $insertCols = implode(',', $this->_selectAliases);
 707     $tempQuery = "CREATE TEMPORARY TABLE civireport_activity_temp_target CHARACTER SET utf8 COLLATE utf8_unicode_ci AS
 708 {$this->_select} {$this->_from} {$this->_where} ";
 709     CRM_Core_DAO::executeQuery($tempQuery);
 710
 711     // 2. add new columns to hold assignee and source results
 712     // fixme: add when required
 713     $tempQuery = "
 714   ALTER TABLE  civireport_activity_temp_target
 715   ADD COLUMN civicrm_contact_contact_assignee VARCHAR(128),
 716   ADD COLUMN civicrm_contact_contact_source VARCHAR(128),
 717   ADD COLUMN civicrm_contact_contact_assignee_id VARCHAR(128),
 718   ADD COLUMN civicrm_contact_contact_source_id VARCHAR(128),
 719   ADD COLUMN civicrm_email_contact_assignee_email VARCHAR(128),
 720   ADD COLUMN civicrm_email_contact_source_email VARCHAR(128)";
 721     CRM_Core_DAO::executeQuery($tempQuery);
 722
 723     // 3. fill temp table with assignee results
 724     $this->buildACLClause(array('civicrm_contact_assignee'));
 725     $this->select('assignee');
 726     $this->from('assignee');
 727     $this->customDataFrom();
 728     $this->where('assignee');
 729     $insertCols = implode(',', $this->_selectAliases);
 730     $tempQuery = "INSERT INTO civireport_activity_temp_target ({$insertCols})
 731 {$this->_select}
 732 {$this->_from} {$this->_where}";
 733     CRM_Core_DAO::executeQuery($tempQuery);
 734
 735     // 4. fill temp table with source results
 736     $this->buildACLClause(array('civicrm_contact_source'));
 737     $this->select('source');
 738     $this->from('source');
 739     $this->customDataFrom();
 740     $this->where('source');
 741     $insertCols = implode(',', $this->_selectAliases);
 742     $tempQuery = "INSERT INTO civireport_activity_temp_target ({$insertCols})
 743 {$this->_select}
 744 {$this->_from} {$this->_where}";
 745     CRM_Core_DAO::executeQuery($tempQuery);
 746
 747     // 5. show final result set from temp table
 748     $rows = array();
 749     $this->select('final');
 750     $this->_having = "";
 751     if (!empty($nullFilters)) {
 752       $this->_having = "HAVING " . implode(' AND ', $nullFilters);
 753     }
 754     $this->orderBy();
 755     $this->limit();
 756     $sql = "{$this->_select}
 757 FROM civireport_activity_temp_target tar
 758 GROUP BY civicrm_activity_id {$this->_having} {$this->_orderBy} {$this->_limit}";
 759     $this->buildRows($sql, $rows);
 760
 761     // format result set.
 762     $this->formatDisplay($rows);
 763
 764     // assign variables to templates
 765     $this->doTemplateAssignment($rows);
 766
 767     // do print / pdf / instance stuff if needed
 768     $this->endPostProcess($rows);
 769   }
 770
 771   /**
 772    * Alter display of rows.
 773    *
 774    * Iterate through the rows retrieved via SQL and make changes for display purposes,
 775    * such as rendering contacts as links.
 776    *
 777    * @param array $rows
 778    *   Rows generated by SQL, with an array for each row.
 779    */
 780   public function alterDisplay(&$rows) {
 781     $entryFound = FALSE;
 782     $activityType = CRM_Core_PseudoConstant::activityType(TRUE, TRUE, FALSE, 'label', TRUE);
 783     $activityStatus = CRM_Core_PseudoConstant::activityStatus();
 784     $viewLinks = FALSE;
 785     $context = CRM_Utils_Request::retrieve('context', 'String', $this, FALSE, 'report');
 786     $actUrl = '';
 787
 788     if (CRM_Core_Permission::check('access CiviCRM')) {
 789       $viewLinks = TRUE;
 790       $onHover = ts('View Contact Summary for this Contact');
 791       $onHoverAct = ts('View Activity Record');
 792     }
 793
 794     foreach ($rows as $rowNum => $row) {
 795       // if we have an activity type, format the View Activity link for use in various columns
 796       if ($viewLinks &&
 797         array_key_exists('civicrm_activity_activity_type_id', $row)
 798       ) {
 799         // Check for target contact id(s) and use the first contact id in that list for view activity link if found,
 800         // else use source contact id
 801         if (!empty($rows[$rowNum]['civicrm_contact_contact_target_id'])) {
 802           $targets = explode(';', $rows[$rowNum]['civicrm_contact_contact_target_id']);
 803           $cid = $targets[0];
 804         }
 805         else {
 806           $cid = $rows[$rowNum]['civicrm_contact_contact_source_id'];
 807         }
 808
 809         $actActionLinks = CRM_Activity_Selector_Activity::actionLinks($row['civicrm_activity_activity_type_id'],
 810           CRM_Utils_Array::value('civicrm_activity_source_record_id', $rows[$rowNum]),
 811           FALSE,
 812           $rows[$rowNum]['civicrm_activity_id']
 813         );
 814
 815         $actLinkValues = array(
 816           'id' => $rows[$rowNum]['civicrm_activity_id'],
 817           'cid' => $cid,
 818           'cxt' => $context,
 819         );
 820         $actUrl = CRM_Utils_System::url($actActionLinks[CRM_Core_Action::VIEW]['url'],
 821           CRM_Core_Action::replace($actActionLinks[CRM_Core_Action::VIEW]['qs'], $actLinkValues), TRUE
 822         );
 823       }
 824
 825       if (array_key_exists('civicrm_contact_contact_source', $row)) {
 826         if ($value = $row['civicrm_contact_contact_source_id']) {
 827           if ($viewLinks) {
 828             $url = CRM_Utils_System::url("civicrm/contact/view",
 829               'reset=1&cid=' . $value,
 830               $this->_absoluteUrl
 831             );
 832             $rows[$rowNum]['civicrm_contact_contact_source_link'] = $url;
 833             $rows[$rowNum]['civicrm_contact_contact_source_hover'] = $onHover;
 834           }
 835           $entryFound = TRUE;
 836         }
 837       }
 838
 839       if (array_key_exists('civicrm_contact_contact_assignee', $row)) {
 840         $assigneeNames = explode(';', $row['civicrm_contact_contact_assignee']);
 841         if ($value = $row['civicrm_contact_contact_assignee_id']) {
 842           $assigneeContactIds = explode(';', $value);
 843           $link = array();
 844           if ($viewLinks) {
 845             foreach ($assigneeContactIds as $id => $value) {
 846               if (isset($value) && isset($assigneeNames[$id])) {
 847                 $url = CRM_Utils_System::url("civicrm/contact/view",
 848                   'reset=1&cid=' . $value,
 849                   $this->_absoluteUrl
 850                 );
 851                 $link[] = "<a title='" . $onHover . "' href='" . $url .
 852                   "'>{$assigneeNames[$id]}</a>";
 853               }
 854             }
 855             $rows[$rowNum]['civicrm_contact_contact_assignee'] = implode('; ', $link);
 856           }
 857           $entryFound = TRUE;
 858         }
 859       }
 860
 861       if (array_key_exists('civicrm_contact_contact_target', $row)) {
 862         $targetNames = explode(';', $row['civicrm_contact_contact_target']);
 863         if ($value = $row['civicrm_contact_contact_target_id']) {
 864           $targetContactIds = explode(';', $value);
 865           $link = array();
 866           if ($viewLinks) {
 867             foreach ($targetContactIds as $id => $value) {
 868               if (isset($value) && isset($targetNames[$id])) {
 869                 $url = CRM_Utils_System::url("civicrm/contact/view",
 870                   'reset=1&cid=' . $value,
 871                   $this->_absoluteUrl
 872                 );
 873                 $link[] = "<a title='" . $onHover . "' href='" . $url .
 874                   "'>{$targetNames[$id]}</a>";
 875               }
 876             }
 877             $rows[$rowNum]['civicrm_contact_contact_target'] = implode('; ', $link);
 878           }
 879           $entryFound = TRUE;
 880         }
 881       }
 882
 883       if (array_key_exists('civicrm_activity_activity_type_id', $row)) {
 884         if ($value = $row['civicrm_activity_activity_type_id']) {
 885           $rows[$rowNum]['civicrm_activity_activity_type_id'] = $activityType[$value];
 886           if ($viewLinks) {
 887             $rows[$rowNum]['civicrm_activity_activity_type_id_link'] = $actUrl;
 888             $rows[$rowNum]['civicrm_activity_activity_type_id_hover'] = $onHoverAct;
 889           }
 890           $entryFound = TRUE;
 891         }
 892       }
 893
 894       if (array_key_exists('civicrm_activity_status_id', $row)) {
 895         if ($value = $row['civicrm_activity_status_id']) {
 896           $rows[$rowNum]['civicrm_activity_status_id'] = $activityStatus[$value];
 897           $entryFound = TRUE;
 898         }
 899       }
 900
 901       if (array_key_exists('civicrm_activity_details', $row)) {
 902         if ($value = $row['civicrm_activity_details']) {
 903           $fullDetails = $rows[$rowNum]['civicrm_activity_details'];
 904           $rows[$rowNum]['civicrm_activity_details'] = substr($fullDetails, 0, strrpos(substr($fullDetails, 0, 80), ' '));
 905           if ($actUrl) {
 906             $rows[$rowNum]['civicrm_activity_details'] .= " <a href='{$actUrl}' title='{$onHoverAct}'>(more)</a>";
 907           }
 908           $entryFound = TRUE;
 909         }
 910       }
 911
 912       if (array_key_exists('civicrm_activity_campaign_id', $row)) {
 913         if ($value = $row['civicrm_activity_campaign_id']) {
 914           $rows[$rowNum]['civicrm_activity_campaign_id'] = $this->activeCampaigns[$value];
 915           $entryFound = TRUE;
 916         }
 917       }
 918
 919       if (array_key_exists('civicrm_activity_engagement_level', $row)) {
 920         if ($value = $row['civicrm_activity_engagement_level']) {
 921           $rows[$rowNum]['civicrm_activity_engagement_level'] = $this->engagementLevels[$value];
 922           $entryFound = TRUE;
 923         }
 924       }
 925
 926       if (array_key_exists('civicrm_activity_activity_date_time', $row) &&
 927         array_key_exists('civicrm_activity_status_id', $row)
 928       ) {
 929         if (CRM_Utils_Date::overdue($rows[$rowNum]['civicrm_activity_activity_date_time']) &&
 930           $activityStatus[$row['civicrm_activity_status_id']] != 'Completed'
 931         ) {
 932           $rows[$rowNum]['class'] = "status-overdue";
 933           $entryFound = TRUE;
 934         }
 935       }
 936
 937       $entryFound = $this->alterDisplayAddressFields($row, $rows, $rowNum, 'activity', 'List all activities for this ') ? TRUE : $entryFound;
 938
 939       if (!$entryFound) {
 940         break;
 941       }
 942     }
 943   }
 944
 945   public function sectionTotals() {
 946     // Reports using order_bys with sections must populate $this->_selectAliases in select() method.
 947     if (empty($this->_selectAliases)) {
 948       return;
 949     }
 950
 951     if (!empty($this->_sections)) {
 952       // pull section aliases out of $this->_sections
 953       $sectionAliases = array_keys($this->_sections);
 954
 955       $ifnulls = array();
 956       foreach (array_merge($sectionAliases, $this->_selectAliases) as $alias) {
 957         $ifnulls[] = "ifnull($alias, '') as $alias";
 958       }
 959
 960       $query = "select " . implode(", ", $ifnulls) .
 961         ", count(DISTINCT civicrm_activity_id) as ct from civireport_activity_temp_target group by " .
 962         implode(", ", $sectionAliases);
 963
 964       // initialize array of total counts
 965       $totals = array();
 966       $dao = CRM_Core_DAO::executeQuery($query);
 967       while ($dao->fetch()) {
 968         // let $this->_alterDisplay translate any integer ids to human-readable values.
 969         $rows[0] = $dao->toArray();
 970         $this->alterDisplay($rows);
 971         $row = $rows[0];
 972
 973         // add totals for all permutations of section values
 974         $values = array();
 975         $i = 1;
 976         $aliasCount = count($sectionAliases);
 977         foreach ($sectionAliases as $alias) {
 978           $values[] = $row[$alias];
 979           $key = implode(CRM_Core_DAO::VALUE_SEPARATOR, $values);
 980           if ($i == $aliasCount) {
 981             // the last alias is the lowest-level section header; use count as-is
 982             $totals[$key] = $dao->ct;
 983           }
 984           else {
 985             // other aliases are higher level; roll count into their total
 986             $totals[$key] += $dao->ct;
 987           }
 988         }
 989       }
 990       $this->assign('sectionTotals', $totals);
 991     }
 992   }
 993
 994 }