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