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