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