CRM/Report/Form/ActivitySummary.php

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