CRM/Report/Form/ActivitySummary.php

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