CRM/Report/Form/ActivitySummary.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 5                                                  |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2018                                |
   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-2018
  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 = array(
  58       'civicrm_contact' => array(
  59         'dao' => 'CRM_Contact_DAO_Contact',
  60         'fields' => array(
  61           'id' => array(
  62             'required' => TRUE,
  63             'no_display' => TRUE,
  64           ),
  65           'sort_name' => array(
  66             'title' => ts('Contact Name'),
  67             'no_repeat' => TRUE,
  68           ),
  69         ),
  70         'filters' => array(
  71           'sort_name' => array(
  72             'title' => ts('Contact Name'),
  73           ),
  74         ),
  75         'group_bys' => array(
  76           'sort_name' => array(
  77             'name' => 'id',
  78             'title' => ts('Contact'),
  79           ),
  80         ),
  81         'order_bys' => array(
  82           'sort_name' => array(
  83             'title' => ts('Contact Name'),
  84           ),
  85         ),
  86         'grouping' => 'contact-fields',
  87       ),
  88       'civicrm_email' => array(
  89         'dao' => 'CRM_Core_DAO_Email',
  90         'fields' => array(
  91           'email' => array(
  92             'title' => ts('Email'),
  93           ),
  94         ),
  95         'order_bys' => array(
  96           'email' => array(
  97             'title' => ts('Email'),
  98           ),
  99         ),
 100         'grouping' => 'contact-fields',
 101       ),
 102       'civicrm_phone' => array(
 103         'dao' => 'CRM_Core_DAO_Email',
 104         'fields' => array(
 105           'phone' => array(
 106             'title' => ts('Phone'),
 107           ),
 108         ),
 109         'grouping' => 'contact-fields',
 110       ),
 111       'civicrm_activity' => array(
 112         'dao' => 'CRM_Activity_DAO_Activity',
 113         'fields' => array(
 114           'activity_type_id' => array(
 115             'title' => ts('Activity Type'),
 116             'required' => TRUE,
 117             'type' => CRM_Utils_Type::T_STRING,
 118           ),
 119           'status_id' => array(
 120             'title' => ts('Activity Status'),
 121             'default' => TRUE,
 122             'type' => CRM_Utils_Type::T_STRING,
 123           ),
 124           'duration' => array(
 125             'title' => ts('Duration'),
 126             'default' => TRUE,
 127           ),
 128           'priority_id' => array(
 129             'title' => ts('Priority'),
 130             'default' => TRUE,
 131             'type' => CRM_Utils_Type::T_STRING,
 132           ),
 133           'id' => array(
 134             'title' => ts('Total Activities'),
 135             'required' => TRUE,
 136             'statistics' => array(
 137               'count' => ts('Count'),
 138             ),
 139           ),
 140         ),
 141         'filters' => array(
 142           'activity_date_time' => array(
 143             'operatorType' => CRM_Report_Form::OP_DATE,
 144           ),
 145           'activity_type_id' => array(
 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' => array(
 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' => array(
 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' => array(
 165           'activity_date_time' => array(
 166             'title' => ts('Activity Date'),
 167             'frequency' => TRUE,
 168           ),
 169           'activity_type_id' => array(
 170             'title' => ts('Activity Type'),
 171             'default' => TRUE,
 172           ),
 173           'status_id' => array(
 174             'title' => ts('Activity Status'),
 175             'default' => TRUE,
 176           ),
 177         ),
 178         'order_bys' => array(
 179           'activity_date_time' => array(
 180             'title' => ts('Activity Date'),
 181           ),
 182           'activity_type_id' => array(
 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 = array();
 197     $this->_columnHeaders = array();
 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"] = array('no_display' => TRUE);
 246               $this->_columnHeaders["{$tableName}_{$fieldName}_subtotal"] = array('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 = array();
 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 = array();
 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                   array('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 = array();
 472     $contactFields = array('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     // store the duration count in temp table
 536     $this->_tempTableName = CRM_Core_DAO::createTempTableName('civicrm_activity');
 537
 538     // build temporary table column names base on column headers of result
 539     $dbColumns = array();
 540     foreach ($this->_columnHeaders as $fieldName => $dontCare) {
 541       $dbColumns[] = $fieldName . ' VARCHAR(128)';
 542     }
 543
 544     // create temp table to store main result
 545     $tempQuery = "CREATE TEMPORARY TABLE {$this->_tempTableName} (
 546       id int unsigned NOT NULL AUTO_INCREMENT, " . implode(', ', $dbColumns) . ' , PRIMARY KEY (id))'
 547       . $this->_databaseAttributes;
 548     CRM_Core_DAO::executeQuery($tempQuery);
 549
 550     // build main report query
 551     $sql = "{$this->_select} {$this->_from} {$this->_where} {$this->_groupBy} {$this->_having} {$this->_orderBy} {$this->_limit}";
 552
 553     // store the result in temporary table
 554     $insertCols = '';
 555     $insertQuery = "INSERT INTO {$this->_tempTableName} ( " . implode(',', array_keys($this->_columnHeaders)) . " )
 556 {$sql}";
 557     CRM_Core_DAO::executeQuery($insertQuery);
 558
 559     // now build the query for duration sum
 560     $this->from(TRUE);
 561     $this->where(TRUE);
 562     $this->groupBy(FALSE);
 563
 564     // build the query to calulate duration sum
 565     $sql = "SELECT SUM(activity_civireport.duration) as civicrm_activity_duration_total {$this->_from} {$this->_where} {$this->_groupBy} {$this->_having} {$this->_orderBy} {$this->_limit}";
 566
 567     // create temp table to store duration
 568     $this->_tempDurationSumTableName = CRM_Core_DAO::createTempTableName('civicrm_activity');
 569     $tempQuery = "CREATE TEMPORARY TABLE {$this->_tempDurationSumTableName} (
 570       id int unsigned NOT NULL AUTO_INCREMENT, civicrm_activity_duration_total VARCHAR(128), PRIMARY KEY (id))"
 571       . $this->_databaseAttributes;
 572     CRM_Core_DAO::executeQuery($tempQuery);
 573
 574     // store the result in temporary table
 575     $insertQuery = "INSERT INTO {$this->_tempDurationSumTableName} (civicrm_activity_duration_total)
 576     {$sql}";
 577     CRM_Core_DAO::executeQuery($insertQuery);
 578
 579     // build array of result based on column headers. This method also allows
 580     // modifying column headers before using it to build result set i.e $rows.
 581     $rows = array();
 582     $query = "SELECT {$this->_tempTableName}.*,  {$this->_tempDurationSumTableName}.civicrm_activity_duration_total
 583     FROM {$this->_tempTableName} INNER JOIN {$this->_tempDurationSumTableName}
 584       ON ({$this->_tempTableName}.id = {$this->_tempDurationSumTableName}.id)";
 585
 586     // finally add duration total to column headers
 587     $this->_columnHeaders['civicrm_activity_duration_total'] = array('no_display' => 1);
 588
 589     $this->buildRows($query, $rows);
 590
 591     // format result set.
 592     $this->formatDisplay($rows);
 593
 594     // assign variables to templates
 595     $this->doTemplateAssignment($rows);
 596
 597     //reset the sql building to default, which is used / called during other actions like "add to group"
 598     // now build the query for duration sum
 599     $this->from();
 600     $this->where();
 601
 602     // do print / pdf / instance stuff if needed
 603     $this->endPostProcess($rows);
 604   }
 605
 606   /**
 607    * @param $rows
 608    *
 609    * @return array
 610    */
 611   public function statistics(&$rows) {
 612     $statistics = parent::statistics($rows);
 613     $totalType = $totalActivity = $totalDuration = 0;
 614
 615     $query = "SELECT {$this->_tempTableName}.civicrm_activity_activity_type_id,
 616         {$this->_tempTableName}.civicrm_activity_id_count,
 617         {$this->_tempDurationSumTableName}.civicrm_activity_duration_total
 618     FROM {$this->_tempTableName} INNER JOIN {$this->_tempDurationSumTableName}
 619       ON ({$this->_tempTableName}.id = {$this->_tempDurationSumTableName}.id)";
 620
 621     $actDAO = CRM_Core_DAO::executeQuery($query);
 622
 623     $activityTypesCount = array();
 624     while ($actDAO->fetch()) {
 625       if (!in_array($actDAO->civicrm_activity_activity_type_id, $activityTypesCount)) {
 626         $activityTypesCount[] = $actDAO->civicrm_activity_activity_type_id;
 627       }
 628
 629       $totalActivity += $actDAO->civicrm_activity_id_count;
 630       $totalDuration += $actDAO->civicrm_activity_duration_total;
 631     }
 632
 633     $totalType = count($activityTypesCount);
 634
 635     $statistics['counts']['type'] = array(
 636       'title' => ts('Total Types'),
 637       'value' => $totalType,
 638     );
 639     $statistics['counts']['activities'] = array(
 640       'title' => ts('Total Number of Activities'),
 641       'value' => $totalActivity,
 642     );
 643     $statistics['counts']['duration'] = array(
 644       'title' => ts('Total Duration (in Minutes)'),
 645       'value' => $totalDuration,
 646     );
 647     return $statistics;
 648   }
 649
 650   public function modifyColumnHeaders() {
 651     //CRM-16719 modify name of column
 652     if (!empty($this->_columnHeaders['civicrm_activity_status_id'])) {
 653       $this->_columnHeaders['civicrm_activity_status_id']['title'] = ts('Status');
 654     }
 655   }
 656
 657   /**
 658    * Alter display of rows.
 659    *
 660    * Iterate through the rows retrieved via SQL and make changes for display purposes,
 661    * such as rendering contacts as links.
 662    *
 663    * @param array $rows
 664    *   Rows generated by SQL, with an array for each row.
 665    */
 666   public function alterDisplay(&$rows) {
 667     $entryFound = FALSE;
 668     $activityType = CRM_Core_PseudoConstant::activityType(TRUE, TRUE, FALSE, 'label', TRUE);
 669     $activityStatus = CRM_Core_PseudoConstant::activityStatus();
 670     $priority = CRM_Core_PseudoConstant::get('CRM_Activity_DAO_Activity', 'priority_id');
 671     $onHover = ts('View Contact Summary for this Contact');
 672     foreach ($rows as $rowNum => $row) {
 673       // make count columns point to activity detail report
 674       if (!empty($row['civicrm_activity_id_count'])) {
 675         $url = array();
 676         $urlParams = array('activity_type_id', 'gid', 'status_id', 'contact_id');
 677         foreach ($urlParams as $field) {
 678           if (!empty($row['civicrm_activity_' . $field])) {
 679             $url[] = "{$field}_op=in&{$field}_value={$row['civicrm_activity_'.$field]}";
 680           }
 681           elseif (!empty($this->_params[$field . '_value'])) {
 682             $val = implode(",", $this->_params[$field . '_value']);
 683             $url[] = "{$field}_op=in&{$field}_value={$val}";
 684           }
 685         }
 686         $date_suffixes = array('relative', 'from', 'to');
 687         while (list(, $suffix) = each($date_suffixes)) {
 688           if (!empty($this->_params['activity_date_time_' . $suffix])) {
 689             list($from, $to)
 690               = $this->getFromTo(
 691                 CRM_Utils_Array::value("activity_date_time_relative", $this->_params),
 692                 CRM_Utils_Array::value("activity_date_time_from", $this->_params),
 693                 CRM_Utils_Array::value("activity_date_time_to", $this->_params)
 694                 );
 695             $url[] = "activity_date_time_from={$from}&activity_date_time_to={$to}";
 696             break;
 697           }
 698         }
 699         // reset date filter on activity reports.
 700         $url[] = "resetDateFilter=1";
 701         $url = implode('&', $url);
 702         $url = CRM_Report_Utils_Report::getNextUrl('activity', "reset=1&force=1&{$url}",
 703                  $this->_absoluteUrl,
 704                  $this->_id,
 705                  $this->_drilldownReport);
 706         $rows[$rowNum]['civicrm_activity_id_count_link'] = $url;
 707         $rows[$rowNum]['civicrm_activity_id_count_hover'] = ts('List all activity(s) for this row.');
 708         $entryFound = TRUE;
 709       }
 710
 711       if (array_key_exists('civicrm_contact_sort_name', $row) && $this->_outputMode != 'csv') {
 712         if ($value = $row['civicrm_contact_id']) {
 713
 714           // unset the name, email and phone fields if the contact is the same as the previous contact
 715           if (isset($previousContact) && $previousContact == $value) {
 716             $rows[$rowNum]['civicrm_contact_sort_name'] = "";
 717
 718             if (array_key_exists('civicrm_email_email', $row)) {
 719               $rows[$rowNum]['civicrm_email_email'] = "";
 720             }
 721             if (array_key_exists('civicrm_phone_phone', $row)) {
 722               $rows[$rowNum]['civicrm_phone_phone'] = "";
 723             }
 724           }
 725           else {
 726             $url = CRM_Utils_System::url('civicrm/contact/view',
 727               'reset=1&cid=' . $value,
 728               $this->_absoluteUrl
 729             );
 730
 731             $rows[$rowNum]['civicrm_contact_sort_name'] = "<a href='$url'>" . $row['civicrm_contact_sort_name'] .
 732               '</a>';
 733           }
 734
 735           // store the contact ID of this contact
 736           $previousContact = $value;
 737           $entryFound = TRUE;
 738         }
 739       }
 740
 741       if (array_key_exists('civicrm_activity_activity_type_id', $row)) {
 742         if ($value = $row['civicrm_activity_activity_type_id']) {
 743
 744           $value = explode(',', $value);
 745           foreach ($value as $key => $id) {
 746             $value[$key] = $activityType[$id];
 747           }
 748
 749           $rows[$rowNum]['civicrm_activity_activity_type_id'] = implode(' , ', $value);
 750           $entryFound = TRUE;
 751         }
 752       }
 753
 754       if (array_key_exists('civicrm_activity_status_id', $row)) {
 755         if ($value = $row['civicrm_activity_status_id']) {
 756           $rows[$rowNum]['civicrm_activity_status_id'] = $activityStatus[$value];
 757           $entryFound = TRUE;
 758         }
 759       }
 760
 761       if (array_key_exists('civicrm_activity_priority_id', $row)) {
 762         if ($value = $row['civicrm_activity_priority_id']) {
 763           $rows[$rowNum]['civicrm_activity_priority_id'] = $priority[$value];
 764           $entryFound = TRUE;
 765         }
 766       }
 767
 768       if (array_key_exists('civicrm_activity_duration', $row)) {
 769         if ($value = $row['civicrm_activity_duration']) {
 770           $rows[$rowNum]['civicrm_activity_duration'] = $rows[$rowNum]['civicrm_activity_duration_total'];
 771           $entryFound = TRUE;
 772         }
 773       }
 774
 775       if (!$entryFound) {
 776         break;
 777       }
 778     }
 779   }
 780
 781 }