CRM/Report/Form/Activity.php

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