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