CRM/Report/Form/ActivitySummary.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | Copyright CiviCRM LLC. All rights reserved.                        |
   5  |                                                                    |
   6  | This work is published under the GNU AGPLv3 license with some      |
   7  | permitted exceptions and without any warranty. For full license    |
   8  | and copyright information, see https://civicrm.org/licensing       |
   9  +--------------------------------------------------------------------+
  10  */
  11
  12 /**
  13  *
  14  * @package CRM
  15  * @copyright CiviCRM LLC https://civicrm.org/licensing
  16  */
  17 class CRM_Report_Form_ActivitySummary extends CRM_Report_Form {
  18
  19   protected $_emailField = FALSE;
  20   protected $_phoneField = FALSE;
  21   protected $_tempTableName;
  22   protected $_tempDurationSumTableName;
  23
  24   /**
  25    * This report has not been optimised for group filtering.
  26    *
  27    * The functionality for group filtering has been improved but not
  28    * all reports have been adjusted to take care of it. This report has not
  29    * and will run an inefficient query until fixed.
  30    *
  31    * @var bool
  32    * @see https://issues.civicrm.org/jira/browse/CRM-19170
  33    */
  34   protected $groupFilterNotOptimised = TRUE;
  35
  36   /**
  37    * Class constructor.
  38    */
  39   public function __construct() {
  40     $this->_columns = [
  41       'civicrm_contact' => [
  42         'dao' => 'CRM_Contact_DAO_Contact',
  43         'fields' => [
  44           'id' => [
  45             'required' => TRUE,
  46             'no_display' => TRUE,
  47           ],
  48           'sort_name' => [
  49             'title' => ts('Contact Name'),
  50             'no_repeat' => TRUE,
  51           ],
  52         ],
  53         'filters' => [
  54           'sort_name' => [
  55             'title' => ts('Contact Name'),
  56           ],
  57         ],
  58         'group_bys' => [
  59           'sort_name' => [
  60             'name' => 'id',
  61             'title' => ts('Contact'),
  62           ],
  63         ],
  64         'order_bys' => [
  65           'sort_name' => [
  66             'title' => ts('Contact Name'),
  67           ],
  68         ],
  69         'grouping' => 'contact-fields',
  70       ],
  71       'civicrm_email' => [
  72         'dao' => 'CRM_Core_DAO_Email',
  73         'fields' => [
  74           'email' => [
  75             'title' => ts('Email'),
  76           ],
  77         ],
  78         'order_bys' => [
  79           'email' => [
  80             'title' => ts('Email'),
  81           ],
  82         ],
  83         'grouping' => 'contact-fields',
  84       ],
  85       'civicrm_phone' => [
  86         'dao' => 'CRM_Core_DAO_Email',
  87         'fields' => [
  88           'phone' => [
  89             'title' => ts('Phone'),
  90           ],
  91         ],
  92         'grouping' => 'contact-fields',
  93       ],
  94       'civicrm_activity' => [
  95         'dao' => 'CRM_Activity_DAO_Activity',
  96         'fields' => [
  97           'activity_type_id' => [
  98             'title' => ts('Activity Type'),
  99             'required' => TRUE,
 100             'type' => CRM_Utils_Type::T_STRING,
 101           ],
 102           'status_id' => [
 103             'title' => ts('Activity Status'),
 104             'default' => TRUE,
 105             'type' => CRM_Utils_Type::T_STRING,
 106           ],
 107           'duration' => [
 108             'title' => ts('Duration'),
 109             'default' => TRUE,
 110           ],
 111           'priority_id' => [
 112             'title' => ts('Priority'),
 113             'default' => TRUE,
 114             'type' => CRM_Utils_Type::T_STRING,
 115           ],
 116           'id' => [
 117             'title' => ts('Total Activities'),
 118             'required' => TRUE,
 119             'statistics' => [
 120               'count' => ts('Count'),
 121             ],
 122           ],
 123         ],
 124         'filters' => [
 125           'activity_date_time' => [
 126             'operatorType' => CRM_Report_Form::OP_DATE,
 127           ],
 128           'activity_type_id' => [
 129             'title' => ts('Activity Type'),
 130             'default' => 0,
 131             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 132             'options' => CRM_Core_PseudoConstant::activityType(TRUE, TRUE, FALSE, 'label', TRUE),
 133           ],
 134           'status_id' => [
 135             'title' => ts('Activity Status'),
 136             'type' => CRM_Utils_Type::T_STRING,
 137             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 138             'options' => CRM_Core_PseudoConstant::activityStatus(),
 139           ],
 140           'priority_id' => [
 141             'title' => ts('Activity Priority'),
 142             'type' => CRM_Utils_Type::T_INT,
 143             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 144             'options' => CRM_Core_PseudoConstant::get('CRM_Activity_DAO_Activity', 'priority_id'),
 145           ],
 146         ],
 147         'group_bys' => [
 148           'activity_date_time' => [
 149             'title' => ts('Activity Date'),
 150             'frequency' => TRUE,
 151           ],
 152           'activity_type_id' => [
 153             'title' => ts('Activity Type'),
 154             'default' => TRUE,
 155           ],
 156           'status_id' => [
 157             'title' => ts('Activity Status'),
 158             'default' => TRUE,
 159           ],
 160         ],
 161         'order_bys' => [
 162           'activity_date_time' => [
 163             'title' => ts('Activity Date'),
 164           ],
 165           'activity_type_id' => [
 166             'title' => ts('Activity Type'),
 167           ],
 168         ],
 169         'grouping' => 'activity-fields',
 170         'alias' => 'activity',
 171       ],
 172     ];
 173     $this->_groupFilter = TRUE;
 174
 175     parent::__construct();
 176   }
 177
 178   public function select() {
 179     $select = [];
 180     $this->_columnHeaders = [];
 181     foreach ($this->_columns as $tableName => $table) {
 182       if (array_key_exists('group_bys', $table)) {
 183         foreach ($table['group_bys'] as $fieldName => $field) {
 184           if (!empty($this->_params['group_bys'][$fieldName])) {
 185             //include column in report when selected in group by but not in column section.
 186             if (empty($this->_params['fields'][$fieldName])) {
 187               $this->_params['fields'][$fieldName] = TRUE;
 188             }
 189             if (isset($this->_params['group_bys_freq']) && !empty($this->_params['group_bys_freq'][$fieldName])) {
 190               switch ($this->_params['group_bys_freq'][$fieldName]) {
 191                 case 'YEARWEEK':
 192                   $select[] = "DATE_SUB({$field['dbAlias']}, INTERVAL WEEKDAY({$field['dbAlias']}) DAY) AS {$tableName}_{$fieldName}_start";
 193
 194                   $select[] = "YEARWEEK({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
 195                   $select[] = "WEEKOFYEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
 196                   $field['title'] = 'Week';
 197                   break;
 198
 199                 case 'YEAR':
 200                   $select[] = "MAKEDATE(YEAR({$field['dbAlias']}), 1)  AS {$tableName}_{$fieldName}_start";
 201                   $select[] = "YEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
 202                   $select[] = "YEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
 203                   $field['title'] = 'Year';
 204                   break;
 205
 206                 case 'MONTH':
 207                   $select[] = "DATE_SUB({$field['dbAlias']}, INTERVAL (DAYOFMONTH({$field['dbAlias']})-1) DAY) as {$tableName}_{$fieldName}_start";
 208                   $select[] = "MONTH({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
 209                   $select[] = "MONTHNAME({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
 210                   $field['title'] = 'Month';
 211                   break;
 212
 213                 case 'QUARTER':
 214                   $select[] = "STR_TO_DATE(CONCAT( 3 * QUARTER( {$field['dbAlias']} ) -2 , '/', '1', '/', YEAR( {$field['dbAlias']} ) ), '%m/%d/%Y') AS {$tableName}_{$fieldName}_start";
 215                   $select[] = "QUARTER({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
 216                   $select[] = "QUARTER({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
 217                   $field['title'] = 'Quarter';
 218                   break;
 219               }
 220               $this->_interval = $field['title'];
 221               $this->_columnHeaders["{$tableName}_{$fieldName}_start"]['title'] = $field['title'] . ' Beginning';
 222               $this->_columnHeaders["{$tableName}_{$fieldName}_start"]['type'] = $field['type'];
 223               $this->_columnHeaders["{$tableName}_{$fieldName}_start"]['group_by'] = $this->_params['group_bys_freq'][$fieldName];
 224
 225               // just to make sure these values are transferred to rows.
 226               // since we need that for calculation purpose,
 227               // e.g making subtotals look nicer or graphs
 228               $this->_columnHeaders["{$tableName}_{$fieldName}_interval"] = ['no_display' => TRUE];
 229               $this->_columnHeaders["{$tableName}_{$fieldName}_subtotal"] = ['no_display' => TRUE];
 230             }
 231           }
 232         }
 233       }
 234       if (array_key_exists('fields', $table)) {
 235         foreach ($table['fields'] as $fieldName => $field) {
 236           if (!empty($field['required']) || !empty($this->_params['fields'][$fieldName])) {
 237             if ($tableName == 'civicrm_email' || in_array('email', CRM_Utils_Array::collect('column', $this->_params['order_bys']))) {
 238               $this->_emailField = TRUE;
 239             }
 240             if ($tableName == 'civicrm_phone') {
 241               $this->_phoneField = TRUE;
 242             }
 243             if (!empty($field['statistics'])) {
 244               foreach ($field['statistics'] as $stat => $label) {
 245                 switch (strtolower($stat)) {
 246                   case 'count':
 247                     $select[] = "COUNT(DISTINCT({$field['dbAlias']})) as {$tableName}_{$fieldName}_{$stat}";
 248                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = CRM_Utils_Type::T_INT;
 249                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
 250                     $this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}";
 251                     break;
 252                 }
 253               }
 254             }
 255             elseif ($fieldName == 'activity_type_id') {
 256               if (empty($this->_params['group_bys']['activity_type_id'])) {
 257                 $select[] = "GROUP_CONCAT(DISTINCT {$field['dbAlias']}  ORDER BY {$field['dbAlias']} ) as {$tableName}_{$fieldName}";
 258               }
 259               else {
 260                 $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 261               }
 262               $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = $field['type'] ?? NULL;
 263               $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $field['title'] ?? NULL;
 264               $this->_columnHeaders["{$tableName}_{$fieldName}"]['no_display'] = $field['no_display'] ?? NULL;
 265             }
 266             else {
 267               $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 268               $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = $field['type'] ?? NULL;
 269               $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $field['title'] ?? NULL;
 270               $this->_columnHeaders["{$tableName}_{$fieldName}"]['no_display'] = $field['no_display'] ?? NULL;
 271             }
 272           }
 273         }
 274       }
 275     }
 276     $this->_selectClauses = $select;
 277
 278     $this->_select = "SELECT " . implode(', ', $select) . " ";
 279   }
 280
 281   /**
 282    * Generate from clause.
 283    */
 284   public function from() {
 285     $activityContacts = CRM_Activity_BAO_ActivityContact::buildOptions('record_type_id', 'validate');
 286     $assigneeID = CRM_Utils_Array::key('Activity Assignees', $activityContacts);
 287     $targetID = CRM_Utils_Array::key('Activity Targets', $activityContacts);
 288     $sourceID = CRM_Utils_Array::key('Activity Source', $activityContacts);
 289
 290     $this->_from = "
 291         FROM civicrm_activity {$this->_aliases['civicrm_activity']}
 292
 293              LEFT JOIN civicrm_activity_contact target_activity
 294                     ON {$this->_aliases['civicrm_activity']}.id = target_activity.activity_id AND
 295                        target_activity.record_type_id = {$targetID}
 296              LEFT JOIN civicrm_activity_contact assignment_activity
 297                     ON {$this->_aliases['civicrm_activity']}.id = assignment_activity.activity_id AND
 298                        assignment_activity.record_type_id = {$assigneeID}
 299              LEFT JOIN civicrm_activity_contact source_activity
 300                     ON {$this->_aliases['civicrm_activity']}.id = source_activity.activity_id AND
 301                        source_activity.record_type_id = {$sourceID}
 302              LEFT JOIN civicrm_contact contact_civireport
 303                     ON target_activity.contact_id = contact_civireport.id
 304              LEFT JOIN civicrm_contact civicrm_contact_assignee
 305                     ON assignment_activity.contact_id = civicrm_contact_assignee.id
 306              LEFT JOIN civicrm_contact civicrm_contact_source
 307                     ON source_activity.contact_id = civicrm_contact_source.id
 308              {$this->_aclFrom}
 309              LEFT JOIN civicrm_option_value
 310                     ON ( {$this->_aliases['civicrm_activity']}.activity_type_id = civicrm_option_value.value )
 311              LEFT JOIN civicrm_option_group
 312                     ON civicrm_option_group.id = civicrm_option_value.option_group_id
 313              LEFT JOIN civicrm_case_activity
 314                     ON civicrm_case_activity.activity_id = {$this->_aliases['civicrm_activity']}.id
 315              LEFT JOIN civicrm_case
 316                     ON civicrm_case_activity.case_id = civicrm_case.id
 317              LEFT JOIN civicrm_case_contact
 318                     ON civicrm_case_contact.case_id = civicrm_case.id ";
 319
 320     $this->joinPhoneFromContact();
 321
 322     $this->joinEmailFromContact();
 323   }
 324
 325   /**
 326    * Generate from clause for when calculating activity durations.
 327    */
 328   public function activityDurationFrom() {
 329     $activityContacts = CRM_Activity_BAO_ActivityContact::buildOptions('record_type_id', 'validate');
 330     $targetID = CRM_Utils_Array::key('Activity Targets', $activityContacts);
 331     $this->_from = "
 332       FROM civicrm_activity {$this->_aliases['civicrm_activity']}
 333               LEFT JOIN civicrm_activity_contact target_activity
 334                      ON {$this->_aliases['civicrm_activity']}.id = target_activity.activity_id AND
 335                         target_activity.record_type_id = {$targetID}
 336               LEFT JOIN civicrm_contact contact_civireport
 337                      ON target_activity.contact_id = contact_civireport.id
 338               {$this->_aclFrom}";
 339
 340     // Email table is needed if sorting by Email.
 341     $this->joinEmailFromContact();
 342   }
 343
 344   /**
 345    * Generate where clause.
 346    *
 347    * @param bool $durationMode
 348    */
 349   public function where($durationMode = FALSE) {
 350     $optionGroupClause = '';
 351     if (!$durationMode) {
 352       $optionGroupClause = 'civicrm_option_group.name = "activity_type" AND ';
 353     }
 354     $this->_where = " WHERE {$optionGroupClause}
 355                             {$this->_aliases['civicrm_activity']}.is_test = 0 AND
 356                             {$this->_aliases['civicrm_activity']}.is_deleted = 0 AND
 357                             {$this->_aliases['civicrm_activity']}.is_current_revision = 1";
 358
 359     $clauses = [];
 360     foreach ($this->_columns as $tableName => $table) {
 361       if (array_key_exists('filters', $table)) {
 362
 363         foreach ($table['filters'] as $fieldName => $field) {
 364           $clause = NULL;
 365           if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE) {
 366             $relative = $this->_params["{$fieldName}_relative"] ?? NULL;
 367             $from = $this->_params["{$fieldName}_from"] ?? NULL;
 368             $to = $this->_params["{$fieldName}_to"] ?? NULL;
 369
 370             $clause = $this->dateClause($field['dbAlias'], $relative, $from, $to, $field['type']);
 371           }
 372           else {
 373             $op = $this->_params["{$fieldName}_op"] ?? NULL;
 374             if ($op) {
 375               $clause = $this->whereClause($field,
 376                 $op,
 377                 CRM_Utils_Array::value("{$fieldName}_value", $this->_params),
 378                 CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
 379                 CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
 380               );
 381             }
 382           }
 383
 384           if (!empty($clause)) {
 385             $clauses[] = $clause;
 386           }
 387         }
 388       }
 389     }
 390
 391     if (empty($clauses)) {
 392       $this->_where .= " ";
 393     }
 394     else {
 395       $this->_where .= " AND " . implode(' AND ', $clauses);
 396     }
 397
 398     if ($this->_aclWhere && !$durationMode) {
 399       $this->_where .= " AND ({$this->_aclWhere} OR civicrm_contact_source.is_deleted=0 OR civicrm_contact_assignee.is_deleted=0)";
 400     }
 401   }
 402
 403   /**
 404    * Build the report query.
 405    *
 406    * @param bool $applyLimit
 407    *
 408    * @return string
 409    */
 410   public function buildQuery($applyLimit = TRUE) {
 411     $this->buildGroupTempTable();
 412     $this->select();
 413     $this->from();
 414     $this->customDataFrom();
 415     $this->buildPermissionClause();
 416     $this->where();
 417     $this->groupBy();
 418     $this->orderBy();
 419
 420     // Order by & Section columns not selected for display need to be included in SELECT.
 421     $unselectedColumns = array_merge($this->unselectedOrderByColumns(), $this->unselectedSectionColumns());
 422     foreach ($unselectedColumns as $alias => $field) {
 423       $clause = $this->getSelectClauseWithGroupConcatIfNotGroupedBy($field['table_name'], $field['name'], $field);
 424       if (!$clause) {
 425         $clause = "{$field['dbAlias']} as {$alias}";
 426       }
 427       $this->_select .= ", $clause ";
 428     }
 429
 430     if ($applyLimit && empty($this->_params['charts'])) {
 431       $this->limit();
 432     }
 433     CRM_Utils_Hook::alterReportVar('sql', $this, $this);
 434
 435     // build temporary table column names base on column headers of result
 436     $dbColumns = [];
 437     foreach ($this->_columnHeaders as $fieldName => $dontCare) {
 438       $dbColumns[] = $fieldName . ' VARCHAR(128)';
 439     }
 440
 441     // Order by & Section columns not selected for display need to be included in temp table.
 442     foreach ($unselectedColumns as $alias => $section) {
 443       $dbColumns[] = $alias . ' VARCHAR(128)';
 444     }
 445
 446     // create temp table to store main result
 447     $this->_tempTableName = $this->createTemporaryTable('tempTable', "
 448       id int unsigned NOT NULL AUTO_INCREMENT, " . implode(', ', $dbColumns) . ' , PRIMARY KEY (id)',
 449     TRUE);
 450
 451     // build main report query
 452     $sql = "{$this->_select} {$this->_from} {$this->_where} {$this->_groupBy} {$this->_having} {$this->_orderBy} {$this->_limit}";
 453     $this->addToDeveloperTab($sql);
 454
 455     // store the result in temporary table
 456     $insertCols = '';
 457     $insertQuery = "INSERT INTO {$this->_tempTableName} ( " . implode(',', array_merge(array_keys($this->_columnHeaders), array_keys($unselectedColumns))) . " )
 458 {$sql}";
 459     CRM_Core_DAO::disableFullGroupByMode();
 460     CRM_Core_DAO::executeQuery($insertQuery);
 461     CRM_Core_DAO::reenableFullGroupByMode();
 462
 463     // now build the query for duration sum
 464     $this->activityDurationFrom();
 465     $this->where(TRUE);
 466     $this->groupBy(FALSE);
 467
 468     // build the query to calulate duration sum
 469     $sql = "SELECT SUM(activity_civireport.duration) as civicrm_activity_duration_total {$this->_from} {$this->_where} {$this->_groupBy} {$this->_having} {$this->_orderBy} {$this->_limit}";
 470
 471     // create temp table to store duration
 472     $this->_tempDurationSumTableName = $this->createTemporaryTable('tempDurationSumTable', "
 473       id int unsigned NOT NULL AUTO_INCREMENT, civicrm_activity_duration_total VARCHAR(128), PRIMARY KEY (id)",
 474     TRUE);
 475
 476     // store the result in temporary table
 477     $insertQuery = "INSERT INTO {$this->_tempDurationSumTableName} (civicrm_activity_duration_total)
 478     {$sql}";
 479     CRM_Core_DAO::disableFullGroupByMode();
 480     CRM_Core_DAO::executeQuery($insertQuery);
 481     CRM_Core_DAO::reenableFullGroupByMode();
 482
 483     $sql = "SELECT {$this->_tempTableName}.*,  {$this->_tempDurationSumTableName}.civicrm_activity_duration_total
 484     FROM {$this->_tempTableName} INNER JOIN {$this->_tempDurationSumTableName}
 485       ON ({$this->_tempTableName}.id = {$this->_tempDurationSumTableName}.id)";
 486
 487     // finally add duration total to column headers
 488     $this->_columnHeaders['civicrm_activity_duration_total'] = ['no_display' => 1];
 489
 490     // reset the sql building to default, which is used / called during other actions like "add to group"
 491     $this->from();
 492     $this->where();
 493
 494     return $sql;
 495   }
 496
 497   /**
 498    * Group the fields.
 499    *
 500    * @param bool $includeSelectCol
 501    */
 502   public function groupBy($includeSelectCol = TRUE) {
 503     $this->_groupBy = [];
 504     if (!empty($this->_params['group_bys']) &&
 505       is_array($this->_params['group_bys'])) {
 506       foreach ($this->_columns as $tableName => $table) {
 507         if (array_key_exists('group_bys', $table)) {
 508           foreach ($table['group_bys'] as $fieldName => $field) {
 509             if (!empty($this->_params['group_bys'][$fieldName])) {
 510               if (!empty($field['chart'])) {
 511                 $this->assign('chartSupported', TRUE);
 512               }
 513               if (!empty($table['group_bys'][$fieldName]['frequency']) &&
 514                 !empty($this->_params['group_bys_freq'][$fieldName])
 515               ) {
 516
 517                 $append = "YEAR({$field['dbAlias']}),";
 518                 if (in_array(strtolower($this->_params['group_bys_freq'][$fieldName]),
 519                   ['year']
 520                 )) {
 521                   $append = '';
 522                 }
 523                 $this->_groupBy[] = "$append {$this->_params['group_bys_freq'][$fieldName]}({$field['dbAlias']})";
 524                 $append = TRUE;
 525               }
 526               else {
 527                 $this->_groupBy[] = $field['dbAlias'];
 528               }
 529             }
 530           }
 531         }
 532       }
 533       $groupBy = $this->_groupBy;
 534       $this->_groupBy = "GROUP BY " . implode(', ', $this->_groupBy);
 535     }
 536     else {
 537       $groupBy = "{$this->_aliases['civicrm_activity']}.id";
 538       $this->_groupBy = "GROUP BY {$this->_aliases['civicrm_activity']}.id ";
 539     }
 540     if ($includeSelectCol) {
 541       $this->_groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, $groupBy);
 542     }
 543   }
 544
 545   /**
 546    * @param $fields
 547    * @param $files
 548    * @param $self
 549    *
 550    * @return array
 551    */
 552   public static function formRule($fields, $files, $self) {
 553     $errors = [];
 554     $contactFields = ['sort_name', 'email', 'phone'];
 555     if (!empty($fields['group_bys'])) {
 556       if (!empty($fields['group_bys']['activity_date_time'])) {
 557         if (!empty($fields['group_bys']['sort_name'])) {
 558           $errors['fields'] = ts("Please do not select GroupBy 'Activity Date' with GroupBy 'Contact'");
 559         }
 560         else {
 561           foreach ($fields['fields'] as $fieldName => $val) {
 562             if (in_array($fieldName, $contactFields)) {
 563               $errors['fields'] = ts("Please do not select any Contact Fields with GroupBy 'Activity Date'");
 564               break;
 565             }
 566           }
 567         }
 568       }
 569     }
 570
 571     // don't allow add to group action unless contact fields are selected.
 572     if (isset($fields['_qf_ActivitySummary_submit_group'])) {
 573       $contactFieldSelected = FALSE;
 574       foreach ($fields['fields'] as $fieldName => $val) {
 575         if (in_array($fieldName, $contactFields)) {
 576           $contactFieldSelected = TRUE;
 577           break;
 578         }
 579       }
 580
 581       if (!$contactFieldSelected) {
 582         $errors['fields'] = ts('You cannot use "Add Contacts to Group" action unless contacts fields are selected.');
 583       }
 584     }
 585     return $errors;
 586   }
 587
 588   /**
 589    * @param $rows
 590    *
 591    * @return array
 592    */
 593   public function statistics(&$rows) {
 594     $statistics = parent::statistics($rows);
 595     $totalType = $totalActivity = $totalDuration = 0;
 596
 597     $query = "SELECT {$this->_tempTableName}.civicrm_activity_activity_type_id,
 598         {$this->_tempTableName}.civicrm_activity_id_count,
 599         {$this->_tempDurationSumTableName}.civicrm_activity_duration_total
 600     FROM {$this->_tempTableName} INNER JOIN {$this->_tempDurationSumTableName}
 601       ON ({$this->_tempTableName}.id = {$this->_tempDurationSumTableName}.id)";
 602
 603     $actDAO = CRM_Core_DAO::executeQuery($query);
 604
 605     $activityTypesCount = [];
 606     while ($actDAO->fetch()) {
 607       if (!in_array($actDAO->civicrm_activity_activity_type_id, $activityTypesCount)) {
 608         $activityTypesCount[] = $actDAO->civicrm_activity_activity_type_id;
 609       }
 610
 611       $totalActivity += $actDAO->civicrm_activity_id_count;
 612       $totalDuration += $actDAO->civicrm_activity_duration_total;
 613     }
 614
 615     $totalType = count($activityTypesCount);
 616
 617     $statistics['counts']['type'] = [
 618       'title' => ts('Total Types'),
 619       'value' => $totalType,
 620     ];
 621     $statistics['counts']['activities'] = [
 622       'title' => ts('Total Number of Activities'),
 623       'value' => $totalActivity,
 624     ];
 625     $statistics['counts']['duration'] = [
 626       'title' => ts('Total Duration (in Minutes)'),
 627       'value' => $totalDuration,
 628     ];
 629     return $statistics;
 630   }
 631
 632   public function modifyColumnHeaders() {
 633     //CRM-16719 modify name of column
 634     if (!empty($this->_columnHeaders['civicrm_activity_status_id'])) {
 635       $this->_columnHeaders['civicrm_activity_status_id']['title'] = ts('Status');
 636     }
 637   }
 638
 639   /**
 640    * Alter display of rows.
 641    *
 642    * Iterate through the rows retrieved via SQL and make changes for display purposes,
 643    * such as rendering contacts as links.
 644    *
 645    * @param array $rows
 646    *   Rows generated by SQL, with an array for each row.
 647    */
 648   public function alterDisplay(&$rows) {
 649     $entryFound = FALSE;
 650     $activityType = CRM_Core_PseudoConstant::activityType(TRUE, TRUE, FALSE, 'label', TRUE);
 651     $activityStatus = CRM_Core_PseudoConstant::activityStatus();
 652     $priority = CRM_Core_PseudoConstant::get('CRM_Activity_DAO_Activity', 'priority_id');
 653     $onHover = ts('View Contact Summary for this Contact');
 654     foreach ($rows as $rowNum => $row) {
 655       // make count columns point to activity detail report
 656       if (!empty($row['civicrm_activity_id_count'])) {
 657         $url = [];
 658         $urlParams = ['activity_type_id', 'gid', 'status_id', 'contact_id'];
 659         foreach ($urlParams as $field) {
 660           if (!empty($row['civicrm_activity_' . $field])) {
 661             $url[] = "{$field}_op=in&{$field}_value={$row['civicrm_activity_'.$field]}";
 662           }
 663           elseif (!empty($this->_params[$field . '_value'])) {
 664             $val = implode(",", $this->_params[$field . '_value']);
 665             $url[] = "{$field}_op=in&{$field}_value={$val}";
 666           }
 667         }
 668         $date_suffixes = ['relative', 'from', 'to'];
 669         foreach ($date_suffixes as $suffix) {
 670           if (!empty($this->_params['activity_date_time_' . $suffix])) {
 671             list($from, $to)
 672               = $this->getFromTo(
 673                 CRM_Utils_Array::value("activity_date_time_relative", $this->_params),
 674                 CRM_Utils_Array::value("activity_date_time_from", $this->_params),
 675                 CRM_Utils_Array::value("activity_date_time_to", $this->_params)
 676                 );
 677             $url[] = "activity_date_time_from={$from}&activity_date_time_to={$to}";
 678             break;
 679           }
 680         }
 681         // reset date filter on activity reports.
 682         $url[] = "resetDateFilter=1";
 683         $url = implode('&', $url);
 684         $url = CRM_Report_Utils_Report::getNextUrl('activity', "reset=1&force=1&{$url}",
 685                  $this->_absoluteUrl,
 686                  $this->_id,
 687                  $this->_drilldownReport);
 688         $rows[$rowNum]['civicrm_activity_id_count_link'] = $url;
 689         $rows[$rowNum]['civicrm_activity_id_count_hover'] = ts('List all activity(s) for this row.');
 690         $entryFound = TRUE;
 691       }
 692
 693       if (array_key_exists('civicrm_contact_sort_name', $row) && $this->_outputMode != 'csv') {
 694         if ($value = $row['civicrm_contact_id']) {
 695
 696           // unset the name, email and phone fields if the contact is the same as the previous contact
 697           if (isset($previousContact) && $previousContact == $value) {
 698             $rows[$rowNum]['civicrm_contact_sort_name'] = "";
 699
 700             if (array_key_exists('civicrm_email_email', $row)) {
 701               $rows[$rowNum]['civicrm_email_email'] = "";
 702             }
 703             if (array_key_exists('civicrm_phone_phone', $row)) {
 704               $rows[$rowNum]['civicrm_phone_phone'] = "";
 705             }
 706           }
 707           else {
 708             $url = CRM_Utils_System::url('civicrm/contact/view',
 709               'reset=1&cid=' . $value,
 710               $this->_absoluteUrl
 711             );
 712
 713             $rows[$rowNum]['civicrm_contact_sort_name'] = "<a href='$url'>" . $row['civicrm_contact_sort_name'] .
 714               '</a>';
 715           }
 716
 717           // store the contact ID of this contact
 718           $previousContact = $value;
 719           $entryFound = TRUE;
 720         }
 721       }
 722
 723       if (array_key_exists('civicrm_activity_activity_type_id', $row)) {
 724         if ($value = $row['civicrm_activity_activity_type_id']) {
 725
 726           $value = explode(',', $value);
 727           foreach ($value as $key => $id) {
 728             $value[$key] = $activityType[$id];
 729           }
 730
 731           $rows[$rowNum]['civicrm_activity_activity_type_id'] = implode(' , ', $value);
 732           $entryFound = TRUE;
 733         }
 734       }
 735
 736       if (array_key_exists('civicrm_activity_status_id', $row)) {
 737         if ($value = $row['civicrm_activity_status_id']) {
 738           $rows[$rowNum]['civicrm_activity_status_id'] = $activityStatus[$value];
 739           $entryFound = TRUE;
 740         }
 741       }
 742
 743       if (array_key_exists('civicrm_activity_priority_id', $row)) {
 744         if ($value = $row['civicrm_activity_priority_id']) {
 745           $rows[$rowNum]['civicrm_activity_priority_id'] = $priority[$value];
 746           $entryFound = TRUE;
 747         }
 748       }
 749
 750       if (array_key_exists('civicrm_activity_duration', $row)) {
 751         if ($value = $row['civicrm_activity_duration']) {
 752           $rows[$rowNum]['civicrm_activity_duration'] = $rows[$rowNum]['civicrm_activity_duration_total'];
 753           $entryFound = TRUE;
 754         }
 755       }
 756
 757       if (!$entryFound) {
 758         break;
 759       }
 760     }
 761   }
 762
 763 }