CRM/Report/Form/ActivitySummary.php

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