CRM/Report/Form/Activity.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.7                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2015                                |
   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-2015
  32  */
  33 class CRM_Report_Form_Activity extends CRM_Report_Form {
  34   protected $_selectAliasesTotal = array();
  35
  36   protected $_customGroupExtends = array(
  37     'Activity',
  38   );
  39
  40   protected $_nonDisplayFields = array();
  41
  42   /**
  43    * Class constructor.
  44    */
  45   public function __construct() {
  46     // There could be multiple contacts. We not clear on which contact id to display.
  47     // Lets hide it for now.
  48     $this->_exposeContactID = FALSE;
  49
  50     $config = CRM_Core_Config::singleton();
  51     $campaignEnabled = in_array("CiviCampaign", $config->enableComponents);
  52     $caseEnabled = in_array("CiviCase", $config->enableComponents);
  53     if ($campaignEnabled) {
  54       $getCampaigns = CRM_Campaign_BAO_Campaign::getPermissionedCampaigns(NULL, NULL, TRUE, FALSE, TRUE);
  55       $this->activeCampaigns = $getCampaigns['campaigns'];
  56       asort($this->activeCampaigns);
  57       $this->engagementLevels = CRM_Campaign_PseudoConstant::engagementLevel();
  58     }
  59
  60     $components = CRM_Core_Component::getEnabledComponents();
  61     foreach ($components as $componentName => $componentInfo) {
  62       $permission = sprintf("access %s", $componentName == 'CiviCase' ? "all cases and activities" : $componentName);
  63       if (CRM_Core_Permission::check($permission)) {
  64         $accessAllowed[] = $componentInfo->componentID;
  65       }
  66     }
  67
  68     $include = '';
  69     if (!empty($accessAllowed)) {
  70       $include = 'OR v.component_id IN (' . implode(', ', $accessAllowed) . ')';
  71     }
  72     $condition = " AND ( v.component_id IS NULL {$include} )";
  73     $this->activityTypes = CRM_Core_OptionGroup::values('activity_type', FALSE, FALSE, FALSE, $condition);
  74     asort($this->activityTypes);
  75     $this->_columns = array(
  76       'civicrm_contact' => array(
  77         'dao' => 'CRM_Contact_DAO_Contact',
  78         'fields' => array(
  79           'contact_source' => array(
  80             'name' => 'sort_name',
  81             'title' => ts('Source Name'),
  82             'alias' => 'civicrm_contact_source',
  83             'no_repeat' => TRUE,
  84           ),
  85           'contact_assignee' => array(
  86             'name' => 'sort_name',
  87             'title' => ts('Assignee Name'),
  88             'alias' => 'civicrm_contact_assignee',
  89             'dbAlias' => "civicrm_contact_assignee.sort_name",
  90             'default' => TRUE,
  91           ),
  92           'contact_target' => array(
  93             'name' => 'sort_name',
  94             'title' => ts('Target Name'),
  95             'alias' => 'civicrm_contact_target',
  96             'dbAlias' => "civicrm_contact_target.sort_name",
  97             'default' => TRUE,
  98           ),
  99           'contact_source_id' => array(
 100             'name' => 'id',
 101             'alias' => 'civicrm_contact_source',
 102             'dbAlias' => "civicrm_contact_source.id",
 103             'no_display' => TRUE,
 104             'default' => TRUE,
 105             'required' => TRUE,
 106           ),
 107           'contact_assignee_id' => array(
 108             'name' => 'id',
 109             'alias' => 'civicrm_contact_assignee',
 110             'dbAlias' => "civicrm_contact_assignee.id",
 111             'no_display' => TRUE,
 112             'default' => TRUE,
 113             'required' => TRUE,
 114           ),
 115           'contact_target_id' => array(
 116             'name' => 'id',
 117             'alias' => 'civicrm_contact_target',
 118             'dbAlias' => "civicrm_contact_target.id",
 119             'no_display' => TRUE,
 120             'default' => TRUE,
 121             'required' => TRUE,
 122           ),
 123         ),
 124         'filters' => array(
 125           'contact_source' => array(
 126             'name' => 'sort_name',
 127             'alias' => 'civicrm_contact_source',
 128             'title' => ts('Source Name'),
 129             'operator' => 'like',
 130             'type' => CRM_Report_Form::OP_STRING,
 131           ),
 132           'contact_assignee' => array(
 133             'name' => 'sort_name',
 134             'alias' => 'civicrm_contact_assignee',
 135             'title' => ts('Assignee Name'),
 136             'operator' => 'like',
 137             'type' => CRM_Report_Form::OP_STRING,
 138           ),
 139           'contact_target' => array(
 140             'name' => 'sort_name',
 141             'alias' => 'civicrm_contact_target',
 142             'title' => ts('Target Name'),
 143             'operator' => 'like',
 144             'type' => CRM_Report_Form::OP_STRING,
 145           ),
 146           'current_user' => array(
 147             'name' => 'current_user',
 148             'title' => ts('Limit To Current User'),
 149             'type' => CRM_Utils_Type::T_INT,
 150             'operatorType' => CRM_Report_Form::OP_SELECT,
 151             'options' => array('0' => ts('No'), '1' => ts('Yes')),
 152           ),
 153         ),
 154         'grouping' => 'contact-fields',
 155       ),
 156       'civicrm_email' => array(
 157         'dao' => 'CRM_Core_DAO_Email',
 158         'fields' => array(
 159           'contact_source_email' => array(
 160             'name' => 'email',
 161             'title' => ts('Source Email'),
 162             'alias' => 'civicrm_email_source',
 163           ),
 164           'contact_assignee_email' => array(
 165             'name' => 'email',
 166             'title' => ts('Assignee Email'),
 167             'alias' => 'civicrm_email_assignee',
 168           ),
 169           'contact_target_email' => array(
 170             'name' => 'email',
 171             'title' => ts('Target Email'),
 172             'alias' => 'civicrm_email_target',
 173           ),
 174         ),
 175         'order_bys' => array(
 176           'source_contact_email' => array(
 177             'name' => 'email',
 178             'title' => ts('Source Email'),
 179             'dbAlias' => 'civicrm_email_contact_source_email',
 180           ),
 181         ),
 182       ),
 183       'civicrm_phone' => array(
 184         'dao' => 'CRM_Core_DAO_Phone',
 185         'fields' => array(
 186           'contact_source_phone' => array(
 187             'name' => 'phone',
 188             'title' => ts('Source Phone'),
 189             'alias' => 'civicrm_phone_source',
 190           ),
 191           'contact_assignee_phone' => array(
 192             'name' => 'phone',
 193             'title' => ts('Assignee Phone'),
 194             'alias' => 'civicrm_phone_assignee',
 195           ),
 196           'contact_target_phone' => array(
 197             'name' => 'phone',
 198             'title' => ts('Target Phone'),
 199             'alias' => 'civicrm_phone_target',
 200           ),
 201         ),
 202       ),
 203       'civicrm_activity' => array(
 204         'dao' => 'CRM_Activity_DAO_Activity',
 205         'fields' => array(
 206           'id' => array(
 207             'no_display' => TRUE,
 208             'title' => ts('Activity ID'),
 209             'required' => TRUE,
 210           ),
 211           'source_record_id' => array(
 212             'no_display' => TRUE,
 213             'required' => TRUE,
 214           ),
 215           'activity_type_id' => array(
 216             'title' => ts('Activity Type'),
 217             'required' => TRUE,
 218             'type' => CRM_Utils_Type::T_STRING,
 219           ),
 220           'activity_subject' => array(
 221             'title' => ts('Subject'),
 222             'default' => TRUE,
 223           ),
 224           'activity_date_time' => array(
 225             'title' => ts('Activity Date'),
 226             'required' => TRUE,
 227           ),
 228           'status_id' => array(
 229             'title' => ts('Activity Status'),
 230             'default' => TRUE,
 231             'type' => CRM_Utils_Type::T_STRING,
 232           ),
 233           'duration' => array(
 234             'title' => ts('Duration'),
 235             'type' => CRM_Utils_Type::T_INT,
 236           ),
 237           'details' => array(
 238             'title' => ts('Activity Details'),
 239           ),
 240         ),
 241         'filters' => array(
 242           'activity_date_time' => array(
 243             'default' => 'this.month',
 244             'operatorType' => CRM_Report_Form::OP_DATE,
 245           ),
 246           'activity_subject' => array('title' => ts('Activity Subject')),
 247           'activity_type_id' => array(
 248             'title' => ts('Activity Type'),
 249             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 250             'options' => $this->activityTypes,
 251           ),
 252           'status_id' => array(
 253             'title' => ts('Activity Status'),
 254             'type' => CRM_Utils_Type::T_STRING,
 255             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 256             'options' => CRM_Core_PseudoConstant::activityStatus(),
 257           ),
 258           'details' => array(
 259             'title' => ts('Activity Details'),
 260             'type' => CRM_Utils_Type::T_TEXT,
 261           ),
 262         ),
 263         'order_bys' => array(
 264           'activity_date_time' => array(
 265             'title' => ts('Activity Date'),
 266             'default_weight' => '1',
 267             'dbAlias' => 'civicrm_activity_activity_date_time',
 268           ),
 269           'activity_type_id' => array(
 270             'title' => ts('Activity Type'),
 271             'default_weight' => '2',
 272             'dbAlias' => 'field(civicrm_activity_activity_type_id, ' . implode(', ', array_keys($this->activityTypes)) . ')',
 273           ),
 274         ),
 275         'grouping' => 'activity-fields',
 276         'alias' => 'activity',
 277       ),
 278       // Hack to get $this->_alias populated for the table.
 279       'civicrm_activity_contact' => array(
 280         'dao' => 'CRM_Activity_DAO_ActivityContact',
 281         'fields' => array(),
 282       ),
 283     ) + $this->addressFields(TRUE);
 284
 285     if ($caseEnabled && CRM_Core_Permission::check('access all cases and activities')) {
 286       $this->_columns['civicrm_activity']['filters']['include_case_activities'] = array(
 287         'name' => 'include_case_activities',
 288         'title' => ts('Include Case Activities'),
 289         'type' => CRM_Utils_Type::T_INT,
 290         'operatorType' => CRM_Report_Form::OP_SELECT,
 291         'options' => array('0' => ts('No'), '1' => ts('Yes')),
 292       );
 293     }
 294
 295     if ($campaignEnabled) {
 296       // Add display column and filter for Survey Results, Campaign and Engagement Index if CiviCampaign is enabled
 297
 298       $this->_columns['civicrm_activity']['fields']['result'] = array(
 299         'title' => 'Survey Result',
 300         'default' => 'false',
 301       );
 302       $this->_columns['civicrm_activity']['filters']['result'] = array(
 303         'title' => ts('Survey Result'),
 304         'operator' => 'like',
 305         'type' => CRM_Utils_Type::T_STRING,
 306       );
 307       if (!empty($this->activeCampaigns)) {
 308         $this->_columns['civicrm_activity']['fields']['campaign_id'] = array(
 309           'title' => 'Campaign',
 310           'default' => 'false',
 311         );
 312         $this->_columns['civicrm_activity']['filters']['campaign_id'] = array(
 313           'title' => ts('Campaign'),
 314           'type' => CRM_Utils_Type::T_INT,
 315           'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 316           'options' => $this->activeCampaigns,
 317         );
 318       }
 319       if (!empty($this->engagementLevels)) {
 320         $this->_columns['civicrm_activity']['fields']['engagement_level'] = array(
 321           'title' => 'Engagement Index',
 322           'default' => 'false',
 323         );
 324         $this->_columns['civicrm_activity']['filters']['engagement_level'] = array(
 325           'title' => ts('Engagement Index'),
 326           'type' => CRM_Utils_Type::T_INT,
 327           'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 328           'options' => $this->engagementLevels,
 329         );
 330       }
 331     }
 332     $this->_groupFilter = TRUE;
 333     $this->_tagFilter = TRUE;
 334     $this->_tagFilterTable = 'civicrm_activity';
 335     parent::__construct();
 336   }
 337
 338   /**
 339    * Adding address fields with dbAlias for order clause.
 340    *
 341    * @param bool $orderBy
 342    *
 343    * @return array
 344    *   Address fields
 345    */
 346   public function addressFields($orderBy = FALSE) {
 347     $address = parent::addAddressFields(FALSE, TRUE);
 348     if ($orderBy) {
 349       foreach ($address['civicrm_address']['order_bys'] as $fieldName => $field) {
 350         $address['civicrm_address']['order_bys'][$fieldName]['dbAlias'] = "civicrm_address_{$fieldName}";
 351       }
 352     }
 353     return $address;
 354   }
 355
 356   /**
 357    * Build select clause.
 358    *
 359    * @param null $recordType
 360    */
 361   public function select($recordType = NULL) {
 362     if (!array_key_exists("contact_{$recordType}", $this->_params['fields']) &&
 363       $recordType != 'final'
 364     ) {
 365       $this->_nonDisplayFields[] = "civicrm_contact_contact_{$recordType}";
 366     }
 367     parent::select();
 368
 369     if ($recordType == 'final' && !empty($this->_nonDisplayFields)) {
 370       foreach ($this->_nonDisplayFields as $fieldName) {
 371         unset($this->_columnHeaders[$fieldName]);
 372       }
 373     }
 374
 375     if (empty($this->_selectAliasesTotal)) {
 376       $this->_selectAliasesTotal = $this->_selectAliases;
 377     }
 378
 379     $removeKeys = array();
 380     if ($recordType == 'target') {
 381       foreach ($this->_selectClauses as $key => $clause) {
 382         if (strstr($clause, 'civicrm_contact_assignee.') ||
 383           strstr($clause, 'civicrm_contact_source.') ||
 384           strstr($clause, 'civicrm_email_assignee.') ||
 385           strstr($clause, 'civicrm_email_source.') ||
 386           strstr($clause, 'civicrm_phone_assignee.') ||
 387           strstr($clause, 'civicrm_phone_source.')
 388         ) {
 389           $removeKeys[] = $key;
 390           unset($this->_selectClauses[$key]);
 391         }
 392       }
 393     }
 394     elseif ($recordType == 'assignee') {
 395       foreach ($this->_selectClauses as $key => $clause) {
 396         if (strstr($clause, 'civicrm_contact_target.') ||
 397           strstr($clause, 'civicrm_contact_source.') ||
 398           strstr($clause, 'civicrm_email_target.') ||
 399           strstr($clause, 'civicrm_email_source.') ||
 400           strstr($clause, 'civicrm_phone_target.') ||
 401           strstr($clause, 'civicrm_phone_source.')
 402         ) {
 403           $removeKeys[] = $key;
 404           unset($this->_selectClauses[$key]);
 405         }
 406       }
 407     }
 408     elseif ($recordType == 'source') {
 409       foreach ($this->_selectClauses as $key => $clause) {
 410         if (strstr($clause, 'civicrm_contact_target.') ||
 411           strstr($clause, 'civicrm_contact_assignee.') ||
 412           strstr($clause, 'civicrm_email_target.') ||
 413           strstr($clause, 'civicrm_email_assignee.') ||
 414           strstr($clause, 'civicrm_phone_target.') ||
 415           strstr($clause, 'civicrm_phone_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           strstr($clause, 'civicrm_phone_contact_source_phone') ||
 429           strstr($clause, 'civicrm_phone_contact_assignee_phone') ||
 430           strstr($clause, 'civicrm_email_contact_source_email') ||
 431           strstr($clause, 'civicrm_email_contact_assignee_email') ||
 432           strstr($clause, 'civicrm_email_contact_target_email') ||
 433           strstr($clause, 'civicrm_phone_contact_target_phone')
 434         ) {
 435           $this->_selectClauses[$key] = "GROUP_CONCAT($clause SEPARATOR ';') as $clause";
 436         }
 437       }
 438     }
 439
 440     if ($recordType) {
 441       foreach ($removeKeys as $key) {
 442         unset($this->_selectAliases[$key]);
 443       }
 444
 445       if ($recordType != 'final') {
 446         foreach ($this->_columns['civicrm_address']['order_bys'] as $fieldName => $field) {
 447           $orderByFld = $this->_columns['civicrm_address']['order_bys'][$fieldName];
 448           $fldInfo = $this->_columns['civicrm_address']['fields'][$fieldName];
 449           $this->_selectAliases[] = $orderByFld['dbAlias'];
 450           $this->_selectClauses[] = "{$fldInfo['dbAlias']} as {$orderByFld['dbAlias']}";
 451         }
 452         $this->_selectAliases = array_unique($this->_selectAliases);
 453         $this->_selectClauses = array_unique($this->_selectClauses);
 454       }
 455       $this->_select = "SELECT " . implode(', ', $this->_selectClauses) . " ";
 456     }
 457   }
 458
 459   /**
 460    * Build from clause.
 461    *
 462    * @param string $recordType
 463    */
 464   public function from($recordType) {
 465     $activityContacts = CRM_Core_OptionGroup::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
 466     $activityTypeId = CRM_Core_DAO::getFieldValue("CRM_Core_DAO_OptionGroup", 'activity_type', 'id', 'name');
 467     $assigneeID = CRM_Utils_Array::key('Activity Assignees', $activityContacts);
 468     $targetID = CRM_Utils_Array::key('Activity Targets', $activityContacts);
 469     $sourceID = CRM_Utils_Array::key('Activity Source', $activityContacts);
 470
 471     if ($recordType == 'target') {
 472       $this->_from = "
 473         FROM civicrm_activity {$this->_aliases['civicrm_activity']}
 474              INNER JOIN civicrm_activity_contact  {$this->_aliases['civicrm_activity_contact']}
 475                     ON {$this->_aliases['civicrm_activity']}.id = {$this->_aliases['civicrm_activity_contact']}.activity_id AND
 476                        {$this->_aliases['civicrm_activity_contact']}.record_type_id = {$targetID}
 477              INNER JOIN civicrm_contact civicrm_contact_target
 478                     ON {$this->_aliases['civicrm_activity_contact']}.contact_id = civicrm_contact_target.id
 479              {$this->_aclFrom}";
 480
 481       if ($this->isTableSelected('civicrm_email')) {
 482         $this->_from .= "
 483             LEFT JOIN civicrm_email civicrm_email_target
 484                    ON {$this->_aliases['civicrm_activity_contact']}.contact_id = civicrm_email_target.contact_id AND
 485                       civicrm_email_target.is_primary = 1";
 486       }
 487
 488       if ($this->isTableSelected('civicrm_phone')) {
 489         $this->_from .= "
 490             LEFT JOIN civicrm_phone civicrm_phone_target
 491                    ON {$this->_aliases['civicrm_activity_contact']}.contact_id = civicrm_phone_target.contact_id AND
 492                       civicrm_phone_target.is_primary = 1 ";
 493       }
 494       $this->_aliases['civicrm_contact'] = 'civicrm_contact_target';
 495     }
 496
 497     if ($recordType == 'assignee') {
 498       $this->_from = "
 499         FROM civicrm_activity {$this->_aliases['civicrm_activity']}
 500              INNER JOIN civicrm_activity_contact {$this->_aliases['civicrm_activity_contact']}
 501                     ON {$this->_aliases['civicrm_activity']}.id = {$this->_aliases['civicrm_activity_contact']}.activity_id AND
 502                        {$this->_aliases['civicrm_activity_contact']}.record_type_id = {$assigneeID}
 503              INNER JOIN civicrm_contact civicrm_contact_assignee
 504                     ON {$this->_aliases['civicrm_activity_contact']}.contact_id = civicrm_contact_assignee.id
 505              {$this->_aclFrom}";
 506
 507       if ($this->isTableSelected('civicrm_email')) {
 508         $this->_from .= "
 509             LEFT JOIN civicrm_email civicrm_email_assignee
 510                    ON {$this->_aliases['civicrm_activity_contact']}.contact_id = civicrm_email_assignee.contact_id AND
 511                       civicrm_email_assignee.is_primary = 1";
 512       }
 513       if ($this->isTableSelected('civicrm_phone')) {
 514         $this->_from .= "
 515             LEFT JOIN civicrm_phone civicrm_phone_assignee
 516                    ON {$this->_aliases['civicrm_activity_contact']}.contact_id = civicrm_phone_assignee.contact_id AND
 517                       civicrm_phone_assignee.is_primary = 1 ";
 518       }
 519       $this->_aliases['civicrm_contact'] = 'civicrm_contact_assignee';
 520     }
 521
 522     if ($recordType == 'source') {
 523       $this->_from = "
 524         FROM civicrm_activity {$this->_aliases['civicrm_activity']}
 525              INNER JOIN civicrm_activity_contact {$this->_aliases['civicrm_activity_contact']}
 526                     ON {$this->_aliases['civicrm_activity']}.id = {$this->_aliases['civicrm_activity_contact']}.activity_id AND
 527                        {$this->_aliases['civicrm_activity_contact']}.record_type_id = {$sourceID}
 528              INNER JOIN civicrm_contact civicrm_contact_source
 529                     ON {$this->_aliases['civicrm_activity_contact']}.contact_id = civicrm_contact_source.id
 530              {$this->_aclFrom}";
 531
 532       if ($this->isTableSelected('civicrm_email')) {
 533         $this->_from .= "
 534             LEFT JOIN civicrm_email civicrm_email_source
 535                    ON {$this->_aliases['civicrm_activity_contact']}.contact_id = civicrm_email_source.contact_id AND
 536                       civicrm_email_source.is_primary = 1";
 537       }
 538       if ($this->isTableSelected('civicrm_phone')) {
 539         $this->_from .= "
 540             LEFT JOIN civicrm_phone civicrm_phone_source
 541                    ON {$this->_aliases['civicrm_activity_contact']}.contact_id = civicrm_phone_source.contact_id AND
 542                       civicrm_phone_source.is_primary = 1 ";
 543       }
 544       $this->_aliases['civicrm_contact'] = 'civicrm_contact_source';
 545     }
 546
 547     $this->addAddressFromClause();
 548   }
 549
 550   /**
 551    * Build where clause.
 552    *
 553    * @param string $recordType
 554    */
 555   public function where($recordType = NULL) {
 556     $this->_where = " WHERE {$this->_aliases['civicrm_activity']}.is_test = 0 AND
 557                                 {$this->_aliases['civicrm_activity']}.is_deleted = 0 AND
 558                                 {$this->_aliases['civicrm_activity']}.is_current_revision = 1";
 559
 560     $clauses = array();
 561     foreach ($this->_columns as $tableName => $table) {
 562       if (array_key_exists('filters', $table)) {
 563
 564         foreach ($table['filters'] as $fieldName => $field) {
 565           $clause = NULL;
 566           if ($fieldName != 'contact_' . $recordType &&
 567             (strstr($fieldName, '_target') ||
 568               strstr($fieldName, '_assignee') ||
 569               strstr($fieldName, '_source')
 570             )
 571           ) {
 572             continue;
 573           }
 574           if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE) {
 575             $relative = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params);
 576             $from = CRM_Utils_Array::value("{$fieldName}_from", $this->_params);
 577             $to = CRM_Utils_Array::value("{$fieldName}_to", $this->_params);
 578
 579             $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']);
 580           }
 581           else {
 582             $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
 583             if ($op && ($op != 'nnll' || $op != 'nll')) {
 584               $clause = $this->whereClause($field,
 585                 $op,
 586                 CRM_Utils_Array::value("{$fieldName}_value", $this->_params),
 587                 CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
 588                 CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
 589               );
 590               if ($field['name'] == 'include_case_activities') {
 591                 $clause = NULL;
 592               }
 593               if ($fieldName == 'activity_type_id' &&
 594                 empty($this->_params['activity_type_id_value'])
 595               ) {
 596                 if (empty($this->_params['include_case_activities_value'])) {
 597                   $this->activityTypes = CRM_Core_PseudoConstant::activityType(TRUE, FALSE, FALSE, 'label', TRUE);
 598                 }
 599                 $actTypes = array_flip($this->activityTypes);
 600                 $clause = "( {$this->_aliases['civicrm_activity']}.activity_type_id IN (" .
 601                   implode(',', $actTypes) . ") )";
 602               }
 603             }
 604           }
 605
 606           if ($field['name'] == 'current_user') {
 607             if (CRM_Utils_Array::value("{$fieldName}_value", $this->_params) ==
 608               1
 609             ) {
 610               // get current user
 611               $session = CRM_Core_Session::singleton();
 612               if ($contactID = $session->get('userID')) {
 613                 $clause = "{$this->_aliases['civicrm_activity_contact']}.activity_id IN
 614                            (SELECT activity_id FROM civicrm_activity_contact WHERE contact_id = {$contactID})";
 615               }
 616               else {
 617                 $clause = NULL;
 618               }
 619             }
 620             else {
 621               $clause = NULL;
 622             }
 623           }
 624           if (!empty($clause)) {
 625             $clauses[] = $clause;
 626           }
 627         }
 628       }
 629     }
 630
 631     if (empty($clauses)) {
 632       $this->_where .= " ";
 633     }
 634     else {
 635       $this->_where .= " AND " . implode(' AND ', $clauses);
 636     }
 637
 638     if ($this->_aclWhere) {
 639       $this->_where .= " AND {$this->_aclWhere} ";
 640     }
 641   }
 642
 643   /**
 644    * Override group by function.
 645    */
 646   public function groupBy() {
 647     $this->_groupBy = "GROUP BY {$this->_aliases['civicrm_activity']}.id";
 648   }
 649
 650   /**
 651    * Build ACL clause.
 652    *
 653    * @param string $tableAlias
 654    */
 655   public function buildACLClause($tableAlias = 'contact_a') {
 656     //override for ACL( Since Contact may be source
 657     //contact/assignee or target also it may be null )
 658
 659     if (CRM_Core_Permission::check('view all contacts')) {
 660       $this->_aclFrom = $this->_aclWhere = NULL;
 661       return;
 662     }
 663
 664     $session = CRM_Core_Session::singleton();
 665     $contactID = $session->get('userID');
 666     if (!$contactID) {
 667       $contactID = 0;
 668     }
 669     $contactID = CRM_Utils_Type::escape($contactID, 'Integer');
 670
 671     CRM_Contact_BAO_Contact_Permission::cache($contactID);
 672     $clauses = array();
 673     foreach ($tableAlias as $k => $alias) {
 674       $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 ";
 675     }
 676
 677     $this->_aclFrom = implode(" ", $clauses);
 678     $this->_aclWhere = NULL;
 679   }
 680
 681   /**
 682    * @param int $groupID
 683    *
 684    * @throws Exception
 685    */
 686   public function add2group($groupID) {
 687     if (CRM_Utils_Array::value("contact_target_op", $this->_params) == 'nll') {
 688       CRM_Core_Error::fatal(ts('Current filter criteria didn\'t have any target contact to add to group'));
 689     }
 690
 691     $new_select = 'AS addtogroup_contact_id';
 692     $select = str_ireplace('AS civicrm_contact_contact_target_id', $new_select, $this->_select);
 693     $new_having = ' addtogroup_contact_id';
 694     $having = str_ireplace(' civicrm_contact_contact_target_id', $new_having, $this->_having);
 695     $query = "$select
 696 FROM civireport_activity_temp_target tar
 697 GROUP BY civicrm_activity_id $having {$this->_orderBy}";
 698     $select = 'AS addtogroup_contact_id';
 699     $query = str_ireplace('AS civicrm_contact_contact_target_id', $select, $query);
 700     $dao = CRM_Core_DAO::executeQuery($query);
 701
 702     $contactIDs = array();
 703     // Add resulting contacts to group
 704     while ($dao->fetch()) {
 705       if ($dao->addtogroup_contact_id) {
 706         $contact_id = explode(';', $dao->addtogroup_contact_id);
 707         if ($contact_id[0]) {
 708           $contactIDs[$contact_id[0]] = $contact_id[0];
 709         }
 710       }
 711     }
 712
 713     if (!empty($contactIDs)) {
 714       CRM_Contact_BAO_GroupContact::addContactsToGroup($contactIDs, $groupID);
 715       CRM_Core_Session::setStatus(ts("Listed contact(s) have been added to the selected group."), ts('Contacts Added'), 'success');
 716     }
 717     else {
 718       CRM_Core_Session::setStatus(ts("The listed records(s) cannot be added to the group."));
 719     }
 720   }
 721
 722   /**
 723    * @param $fields
 724    * @param $files
 725    * @param $self
 726    *
 727    * @return array
 728    */
 729   public static function formRule($fields, $files, $self) {
 730     $errors = array();
 731     $config = CRM_Core_Config::singleton();
 732     if (in_array("CiviCase", $config->enableComponents)) {
 733       $componentId = CRM_Core_Component::getComponentID('CiviCase');
 734       $caseActivityTypes = CRM_Core_OptionGroup::values('activity_type', TRUE, FALSE, FALSE, " AND v.component_id={$componentId}");
 735       if (!empty($fields['activity_type_id_value']) && is_array($fields['activity_type_id_value']) && empty($fields['include_case_activities_value'])) {
 736         foreach ($fields['activity_type_id_value'] as $activityTypeId) {
 737           if (in_array($activityTypeId, $caseActivityTypes)) {
 738             $errors['fields'] = ts("Please enable 'Include Case Activities' to filter with Case Activity types.");
 739           }
 740         }
 741       }
 742     }
 743     return $errors;
 744   }
 745
 746   public function postProcess() {
 747     //reset value of activity_date
 748     if (!empty($this->_force)) {
 749       $this->_formValues["activity_date_time_relative"] = NULL;
 750     }
 751     $this->beginPostProcess();
 752
 753     //Assign those recordtype to array which have filter operator as 'Is not empty' or 'Is empty'
 754     $nullFilters = array();
 755     foreach (array('target', 'source', 'assignee') as $type) {
 756       if (CRM_Utils_Array::value("contact_{$type}_op", $this->_params) ==
 757         'nnll' || !empty($this->_params["contact_{$type}_value"])
 758       ) {
 759         $nullFilters[] = " civicrm_contact_contact_{$type}_id IS NOT NULL ";
 760       }
 761       elseif (CRM_Utils_Array::value("contact_{$type}_op", $this->_params) ==
 762         'nll'
 763       ) {
 764         $nullFilters[] = " civicrm_contact_contact_{$type}_id IS NULL ";
 765       }
 766     }
 767
 768     // 1. fill temp table with target results
 769     $this->buildACLClause(array('civicrm_contact_target'));
 770     $this->select('target');
 771     $this->from('target');
 772     $this->customDataFrom();
 773     $this->where('target');
 774     $insertCols = implode(',', $this->_selectAliases);
 775     $tempQuery = "CREATE TEMPORARY TABLE civireport_activity_temp_target CHARACTER SET utf8 COLLATE utf8_unicode_ci AS
 776 {$this->_select} {$this->_from} {$this->_where} ";
 777     CRM_Core_DAO::executeQuery($tempQuery);
 778
 779     // 2. add new columns to hold assignee and source results
 780     // fixme: add when required
 781     $tempQuery = "
 782   ALTER TABLE  civireport_activity_temp_target
 783   MODIFY COLUMN civicrm_contact_contact_target_id VARCHAR(128),
 784   ADD COLUMN civicrm_contact_contact_assignee VARCHAR(128),
 785   ADD COLUMN civicrm_contact_contact_source VARCHAR(128),
 786   ADD COLUMN civicrm_contact_contact_assignee_id VARCHAR(128),
 787   ADD COLUMN civicrm_contact_contact_source_id VARCHAR(128),
 788   ADD COLUMN civicrm_phone_contact_assignee_phone VARCHAR(128),
 789   ADD COLUMN civicrm_phone_contact_source_phone VARCHAR(128),
 790   ADD COLUMN civicrm_email_contact_assignee_email VARCHAR(128),
 791   ADD COLUMN civicrm_email_contact_source_email VARCHAR(128)";
 792     CRM_Core_DAO::executeQuery($tempQuery);
 793
 794     // 3. fill temp table with assignee results
 795     $this->buildACLClause(array('civicrm_contact_assignee'));
 796     $this->select('assignee');
 797     $this->from('assignee');
 798     $this->customDataFrom();
 799     $this->where('assignee');
 800     $insertCols = implode(',', $this->_selectAliases);
 801     $tempQuery = "INSERT INTO civireport_activity_temp_target ({$insertCols})
 802 {$this->_select}
 803 {$this->_from} {$this->_where}";
 804     CRM_Core_DAO::executeQuery($tempQuery);
 805
 806     // 4. fill temp table with source results
 807     $this->buildACLClause(array('civicrm_contact_source'));
 808     $this->select('source');
 809     $this->from('source');
 810     $this->customDataFrom();
 811     $this->where('source');
 812     $insertCols = implode(',', $this->_selectAliases);
 813     $tempQuery = "INSERT INTO civireport_activity_temp_target ({$insertCols})
 814 {$this->_select}
 815 {$this->_from} {$this->_where}";
 816     CRM_Core_DAO::executeQuery($tempQuery);
 817
 818     // 5. show final result set from temp table
 819     $rows = array();
 820     $this->select('final');
 821     $this->_having = "";
 822     if (!empty($nullFilters)) {
 823       $this->_having = "HAVING " . implode(' AND ', $nullFilters);
 824     }
 825     $this->orderBy();
 826     foreach ($this->_sections as $alias => $section) {
 827       if (!empty($section) && $section['name'] == 'activity_date_time') {
 828         $this->alterSectionHeaderForDateTime('civireport_activity_temp_target', $section['tplField']);
 829       }
 830     }
 831     $this->limit();
 832     $sql = "{$this->_select}
 833 FROM civireport_activity_temp_target tar
 834 GROUP BY civicrm_activity_id {$this->_having} {$this->_orderBy} {$this->_limit}";
 835     $this->buildRows($sql, $rows);
 836
 837     // format result set.
 838     $this->formatDisplay($rows);
 839
 840     // assign variables to templates
 841     $this->doTemplateAssignment($rows);
 842
 843     // do print / pdf / instance stuff if needed
 844     $this->endPostProcess($rows);
 845   }
 846
 847   /**
 848    * Alter display of rows.
 849    *
 850    * Iterate through the rows retrieved via SQL and make changes for display purposes,
 851    * such as rendering contacts as links.
 852    *
 853    * @param array $rows
 854    *   Rows generated by SQL, with an array for each row.
 855    */
 856   public function alterDisplay(&$rows) {
 857     $entryFound = FALSE;
 858     $activityType = CRM_Core_PseudoConstant::activityType(TRUE, TRUE, FALSE, 'label', TRUE);
 859     $activityStatus = CRM_Core_PseudoConstant::activityStatus();
 860     $viewLinks = FALSE;
 861     $context = CRM_Utils_Request::retrieve('context', 'String', $this, FALSE, 'report');
 862     $actUrl = '';
 863
 864     if (CRM_Core_Permission::check('access CiviCRM')) {
 865       $viewLinks = TRUE;
 866       $onHover = ts('View Contact Summary for this Contact');
 867       $onHoverAct = ts('View Activity Record');
 868     }
 869     foreach ($rows as $rowNum => $row) {
 870       // if we have an activity type, format the View Activity link for use in various columns
 871       if ($viewLinks &&
 872         array_key_exists('civicrm_activity_activity_type_id', $row)
 873       ) {
 874         // Check for target contact id(s) and use the first contact id in that list for view activity link if found,
 875         // else use source contact id
 876         if (!empty($rows[$rowNum]['civicrm_contact_contact_target_id'])) {
 877           $targets = explode(';', $rows[$rowNum]['civicrm_contact_contact_target_id']);
 878           $cid = $targets[0];
 879         }
 880         else {
 881           $cid = $rows[$rowNum]['civicrm_contact_contact_source_id'];
 882         }
 883
 884         $actActionLinks = CRM_Activity_Selector_Activity::actionLinks($row['civicrm_activity_activity_type_id'],
 885           CRM_Utils_Array::value('civicrm_activity_source_record_id', $rows[$rowNum]),
 886           FALSE,
 887           $rows[$rowNum]['civicrm_activity_id']
 888         );
 889
 890         $actLinkValues = array(
 891           'id' => $rows[$rowNum]['civicrm_activity_id'],
 892           'cid' => $cid,
 893           'cxt' => $context,
 894         );
 895         $actUrl = CRM_Utils_System::url($actActionLinks[CRM_Core_Action::VIEW]['url'],
 896           CRM_Core_Action::replace($actActionLinks[CRM_Core_Action::VIEW]['qs'], $actLinkValues), TRUE
 897         );
 898       }
 899
 900       if (array_key_exists('civicrm_contact_contact_source', $row)) {
 901         if ($value = $row['civicrm_contact_contact_source_id']) {
 902           if ($viewLinks) {
 903             $url = CRM_Utils_System::url("civicrm/contact/view",
 904               'reset=1&cid=' . $value,
 905               $this->_absoluteUrl
 906             );
 907             $rows[$rowNum]['civicrm_contact_contact_source_link'] = $url;
 908             $rows[$rowNum]['civicrm_contact_contact_source_hover'] = $onHover;
 909           }
 910           $entryFound = TRUE;
 911         }
 912       }
 913
 914       if (array_key_exists('civicrm_contact_contact_assignee', $row)) {
 915         $assigneeNames = explode(';', $row['civicrm_contact_contact_assignee']);
 916         if ($value = $row['civicrm_contact_contact_assignee_id']) {
 917           $assigneeContactIds = explode(';', $value);
 918           $link = array();
 919           if ($viewLinks) {
 920             foreach ($assigneeContactIds as $id => $value) {
 921               if (isset($value) && isset($assigneeNames[$id])) {
 922                 $url = CRM_Utils_System::url("civicrm/contact/view",
 923                   'reset=1&cid=' . $value,
 924                   $this->_absoluteUrl
 925                 );
 926                 $link[] = "<a title='" . $onHover . "' href='" . $url .
 927                   "'>{$assigneeNames[$id]}</a>";
 928               }
 929             }
 930             $rows[$rowNum]['civicrm_contact_contact_assignee'] = implode('; ', $link);
 931           }
 932           $entryFound = TRUE;
 933         }
 934       }
 935
 936       if (array_key_exists('civicrm_contact_contact_target', $row)) {
 937         $targetNames = explode(';', $row['civicrm_contact_contact_target']);
 938         if ($value = $row['civicrm_contact_contact_target_id']) {
 939           $targetContactIds = explode(';', $value);
 940           $link = array();
 941           if ($viewLinks) {
 942             foreach ($targetContactIds as $id => $value) {
 943               if (isset($value) && isset($targetNames[$id])) {
 944                 $url = CRM_Utils_System::url("civicrm/contact/view",
 945                   'reset=1&cid=' . $value,
 946                   $this->_absoluteUrl
 947                 );
 948                 $link[] = "<a title='" . $onHover . "' href='" . $url .
 949                   "'>{$targetNames[$id]}</a>";
 950               }
 951             }
 952             $rows[$rowNum]['civicrm_contact_contact_target'] = implode('; ', $link);
 953           }
 954           $entryFound = TRUE;
 955         }
 956       }
 957
 958       if (array_key_exists('civicrm_activity_activity_type_id', $row)) {
 959         if ($value = $row['civicrm_activity_activity_type_id']) {
 960           $rows[$rowNum]['civicrm_activity_activity_type_id'] = $activityType[$value];
 961           if ($viewLinks) {
 962             $rows[$rowNum]['civicrm_activity_activity_type_id_link'] = $actUrl;
 963             $rows[$rowNum]['civicrm_activity_activity_type_id_hover'] = $onHoverAct;
 964           }
 965           $entryFound = TRUE;
 966         }
 967       }
 968
 969       if (array_key_exists('civicrm_activity_status_id', $row)) {
 970         if ($value = $row['civicrm_activity_status_id']) {
 971           $rows[$rowNum]['civicrm_activity_status_id'] = $activityStatus[$value];
 972           $entryFound = TRUE;
 973         }
 974       }
 975
 976       if (array_key_exists('civicrm_activity_details', $row) && $this->_outputMode == 'html') {
 977         if ($value = $row['civicrm_activity_details']) {
 978           $fullDetails = $rows[$rowNum]['civicrm_activity_details'];
 979           $rows[$rowNum]['civicrm_activity_details'] = substr($fullDetails, 0, strrpos(substr($fullDetails, 0, 80), ' '));
 980           if ($actUrl) {
 981             $rows[$rowNum]['civicrm_activity_details'] .= " <a href='{$actUrl}' title='{$onHoverAct}'>(more)</a>";
 982           }
 983           $entryFound = TRUE;
 984         }
 985       }
 986
 987       if (array_key_exists('civicrm_activity_campaign_id', $row)) {
 988         if ($value = $row['civicrm_activity_campaign_id']) {
 989           $rows[$rowNum]['civicrm_activity_campaign_id'] = $this->activeCampaigns[$value];
 990           $entryFound = TRUE;
 991         }
 992       }
 993
 994       if (array_key_exists('civicrm_activity_engagement_level', $row)) {
 995         if ($value = $row['civicrm_activity_engagement_level']) {
 996           $rows[$rowNum]['civicrm_activity_engagement_level'] = $this->engagementLevels[$value];
 997           $entryFound = TRUE;
 998         }
 999       }
1000
1001       if (array_key_exists('civicrm_activity_activity_date_time', $row) &&
1002         array_key_exists('civicrm_activity_status_id', $row)
1003       ) {
1004         if (CRM_Utils_Date::overdue($rows[$rowNum]['civicrm_activity_activity_date_time']) &&
1005           $activityStatus[$row['civicrm_activity_status_id']] != 'Completed'
1006         ) {
1007           $rows[$rowNum]['class'] = "status-overdue";
1008           $entryFound = TRUE;
1009         }
1010       }
1011
1012       $entryFound = $this->alterDisplayAddressFields($row, $rows, $rowNum, 'activity', 'List all activities for this ') ? TRUE : $entryFound;
1013
1014       if (!$entryFound) {
1015         break;
1016       }
1017     }
1018   }
1019
1020   public function sectionTotals() {
1021     // Reports using order_bys with sections must populate $this->_selectAliases in select() method.
1022     if (empty($this->_selectAliases)) {
1023       return;
1024     }
1025
1026     if (!empty($this->_sections)) {
1027       // pull section aliases out of $this->_sections
1028       $sectionAliases = array_keys($this->_sections);
1029
1030       $ifnulls = array();
1031       foreach (array_merge($sectionAliases, $this->_selectAliases) as $alias) {
1032         $ifnulls[] = "ifnull($alias, '') as $alias";
1033       }
1034
1035       $query = "select " . implode(", ", $ifnulls) .
1036         ", count(DISTINCT civicrm_activity_id) as ct from civireport_activity_temp_target group by " .
1037         implode(", ", $sectionAliases);
1038
1039       // initialize array of total counts
1040       $totals = array();
1041       $dao = CRM_Core_DAO::executeQuery($query);
1042       while ($dao->fetch()) {
1043         // let $this->_alterDisplay translate any integer ids to human-readable values.
1044         $rows[0] = $dao->toArray();
1045         $this->alterDisplay($rows);
1046         $row = $rows[0];
1047
1048         // add totals for all permutations of section values
1049         $values = array();
1050         $i = 1;
1051         $aliasCount = count($sectionAliases);
1052         foreach ($sectionAliases as $alias) {
1053           $values[] = $row[$alias];
1054           $key = implode(CRM_Core_DAO::VALUE_SEPARATOR, $values);
1055           if ($i == $aliasCount) {
1056             // the last alias is the lowest-level section header; use count as-is
1057             $totals[$key] = $dao->ct;
1058           }
1059           else {
1060             // other aliases are higher level; roll count into their total
1061             $totals[$key] += $dao->ct;
1062           }
1063         }
1064       }
1065       $this->assign('sectionTotals', $totals);
1066     }
1067   }
1068
1069 }