CRM/Report/Form/Activity.php

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