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