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   public function from($durationMode = FALSE) {
 281     $activityContacts = CRM_Core_OptionGroup::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
 282     $assigneeID = CRM_Utils_Array::key('Activity Assignees', $activityContacts);
 283     $targetID = CRM_Utils_Array::key('Activity Targets', $activityContacts);
 284     $sourceID = CRM_Utils_Array::key('Activity Source', $activityContacts);
 285
 286     if (!$durationMode) {
 287       $this->_from = "
 288           FROM civicrm_activity {$this->_aliases['civicrm_activity']}
 289
 290                LEFT JOIN civicrm_activity_contact target_activity
 291                       ON {$this->_aliases['civicrm_activity']}.id = target_activity.activity_id AND
 292                          target_activity.record_type_id = {$targetID}
 293                LEFT JOIN civicrm_activity_contact assignment_activity
 294                       ON {$this->_aliases['civicrm_activity']}.id = assignment_activity.activity_id AND
 295                          assignment_activity.record_type_id = {$assigneeID}
 296                LEFT JOIN civicrm_activity_contact source_activity
 297                       ON {$this->_aliases['civicrm_activity']}.id = source_activity.activity_id AND
 298                          source_activity.record_type_id = {$sourceID}
 299                LEFT JOIN civicrm_contact contact_civireport
 300                       ON target_activity.contact_id = contact_civireport.id
 301                LEFT JOIN civicrm_contact civicrm_contact_assignee
 302                       ON assignment_activity.contact_id = civicrm_contact_assignee.id
 303                LEFT JOIN civicrm_contact civicrm_contact_source
 304                       ON source_activity.contact_id = civicrm_contact_source.id
 305                {$this->_aclFrom}
 306                LEFT JOIN civicrm_option_value
 307                       ON ( {$this->_aliases['civicrm_activity']}.activity_type_id = civicrm_option_value.value )
 308                LEFT JOIN civicrm_option_group
 309                       ON civicrm_option_group.id = civicrm_option_value.option_group_id
 310                LEFT JOIN civicrm_case_activity
 311                       ON civicrm_case_activity.activity_id = {$this->_aliases['civicrm_activity']}.id
 312                LEFT JOIN civicrm_case
 313                       ON civicrm_case_activity.case_id = civicrm_case.id
 314                LEFT JOIN civicrm_case_contact
 315                       ON civicrm_case_contact.case_id = civicrm_case.id ";
 316
 317       if ($this->_emailField) {
 318         $this->_from .= "
 319               LEFT JOIN civicrm_email  {$this->_aliases['civicrm_email']}
 320                      ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_email']}.contact_id AND
 321                        {$this->_aliases['civicrm_email']}.is_primary = 1 ";
 322       }
 323
 324       if ($this->_phoneField) {
 325         $this->_from .= "
 326               LEFT JOIN civicrm_phone  {$this->_aliases['civicrm_phone']}
 327                      ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id AND
 328                        {$this->_aliases['civicrm_phone']}.is_primary = 1 ";
 329       }
 330     }
 331     else {
 332       $this->_from = "
 333       FROM civicrm_activity {$this->_aliases['civicrm_activity']} {$this->_aclFrom} ";
 334     }
 335   }
 336
 337   public function where($durationMode = FALSE) {
 338     $optionGroupClause = '';
 339     if (!$durationMode) {
 340       $optionGroupClause = 'civicrm_option_group.name = "activity_type" AND ';
 341     }
 342     $this->_where = " WHERE {$optionGroupClause}
 343                             {$this->_aliases['civicrm_activity']}.is_test = 0 AND
 344                             {$this->_aliases['civicrm_activity']}.is_deleted = 0 AND
 345                             {$this->_aliases['civicrm_activity']}.is_current_revision = 1";
 346
 347     $clauses = array();
 348     foreach ($this->_columns as $tableName => $table) {
 349       if (array_key_exists('filters', $table)) {
 350
 351         foreach ($table['filters'] as $fieldName => $field) {
 352           $clause = NULL;
 353           if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE) {
 354             $relative = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params);
 355             $from = CRM_Utils_Array::value("{$fieldName}_from", $this->_params);
 356             $to = CRM_Utils_Array::value("{$fieldName}_to", $this->_params);
 357
 358             $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']);
 359           }
 360           else {
 361             $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
 362             if ($op) {
 363               $clause = $this->whereClause($field,
 364                 $op,
 365                 CRM_Utils_Array::value("{$fieldName}_value", $this->_params),
 366                 CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
 367                 CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
 368               );
 369             }
 370           }
 371
 372           if (!empty($clause)) {
 373             $clauses[] = $clause;
 374           }
 375         }
 376       }
 377     }
 378
 379     if (empty($clauses)) {
 380       $this->_where .= " ";
 381     }
 382     else {
 383       $this->_where .= " AND " . implode(' AND ', $clauses);
 384     }
 385
 386     if ($this->_aclWhere && !$durationMode) {
 387       $this->_where .= " AND ({$this->_aclWhere} OR civicrm_contact_source.is_deleted=0 OR civicrm_contact_assignee.is_deleted=0)";
 388     }
 389   }
 390
 391   public function groupBy() {
 392     $this->_groupBy = array();
 393     if (!empty($this->_params['group_bys']) &&
 394       is_array($this->_params['group_bys'])) {
 395       foreach ($this->_columns as $tableName => $table) {
 396         if (array_key_exists('group_bys', $table)) {
 397           foreach ($table['group_bys'] as $fieldName => $field) {
 398             if (!empty($this->_params['group_bys'][$fieldName])) {
 399               if (!empty($field['chart'])) {
 400                 $this->assign('chartSupported', TRUE);
 401               }
 402               if (!empty($table['group_bys'][$fieldName]['frequency']) &&
 403                 !empty($this->_params['group_bys_freq'][$fieldName])
 404               ) {
 405
 406                 $append = "YEAR({$field['dbAlias']}),";
 407                 if (in_array(strtolower($this->_params['group_bys_freq'][$fieldName]),
 408                   array('year')
 409                 )) {
 410                   $append = '';
 411                 }
 412                 $this->_groupBy[] = "$append {$this->_params['group_bys_freq'][$fieldName]}({$field['dbAlias']})";
 413                 $append = TRUE;
 414               }
 415               else {
 416                 $this->_groupBy[] = $field['dbAlias'];
 417               }
 418             }
 419           }
 420         }
 421       }
 422
 423       $this->_groupBy = "GROUP BY " . implode(', ', $this->_groupBy);
 424     }
 425     else {
 426       $this->_groupBy = "GROUP BY {$this->_aliases['civicrm_activity']}.id ";
 427     }
 428   }
 429
 430   /**
 431    * @param $fields
 432    * @param $files
 433    * @param $self
 434    *
 435    * @return array
 436    */
 437   public static function formRule($fields, $files, $self) {
 438     $errors = array();
 439     $contactFields = array('sort_name', 'email', 'phone');
 440     if (!empty($fields['group_bys'])) {
 441       if (!empty($fields['group_bys']['activity_date_time'])) {
 442         if (!empty($fields['group_bys']['sort_name'])) {
 443           $errors['fields'] = ts("Please do not select GroupBy 'Activity Date' with GroupBy 'Contact'");
 444         }
 445         else {
 446           foreach ($fields['fields'] as $fieldName => $val) {
 447             if (in_array($fieldName, $contactFields)) {
 448               $errors['fields'] = ts("Please do not select any Contact Fields with GroupBy 'Activity Date'");
 449               break;
 450             }
 451           }
 452         }
 453       }
 454     }
 455
 456     // don't allow add to group action unless contact fields are selected.
 457     if (isset($fields['_qf_ActivitySummary_submit_group'])) {
 458       $contactFieldSelected = FALSE;
 459       foreach ($fields['fields'] as $fieldName => $val) {
 460         if (in_array($fieldName, $contactFields)) {
 461           $contactFieldSelected = TRUE;
 462           break;
 463         }
 464       }
 465
 466       if (!$contactFieldSelected) {
 467         $errors['fields'] = ts('You cannot use "Add Contacts to Group" action unless contacts fields are selected.');
 468       }
 469     }
 470     return $errors;
 471   }
 472
 473   public function postProcess() {
 474     // get the acl clauses built before we assemble the query
 475     $this->buildACLClause($this->_aliases['civicrm_contact']);
 476
 477     // get ready with post process params
 478     $this->beginPostProcess();
 479
 480     // build query
 481     $sql = $this->buildQuery();
 482
 483     // main sql statement
 484     $this->select();
 485     $this->from();
 486     $this->customDataFrom();
 487     $this->where();
 488     $this->groupBy();
 489     $this->orderBy();
 490
 491     // order_by columns not selected for display need to be included in SELECT
 492     $unselectedSectionColumns = $this->unselectedSectionColumns();
 493     foreach ($unselectedSectionColumns as $alias => $section) {
 494       $this->_select .= ", {$section['dbAlias']} as {$alias}";
 495     }
 496
 497     if (!empty($applyLimit) && empty($this->_params['charts'])) {
 498       $this->limit();
 499     }
 500     CRM_Utils_Hook::alterReportVar('sql', $this, $this);
 501
 502     // store the duration count in temp table
 503     $this->_tempTableName = CRM_Core_DAO::createTempTableName('civicrm_activity');
 504
 505     // build temporary table column names base on column headers of result
 506     $dbColumns = array();
 507     foreach ($this->_columnHeaders as $fieldName => $dontCare) {
 508       $dbColumns[] = $fieldName . ' VARCHAR(128)';
 509     }
 510
 511     // create temp table to store main result
 512     $tempQuery = "CREATE TEMPORARY TABLE {$this->_tempTableName} (
 513       id int unsigned NOT NULL AUTO_INCREMENT, " . implode(', ', $dbColumns) . ' , PRIMARY KEY (id))';
 514     CRM_Core_DAO::executeQuery($tempQuery);
 515
 516     // build main report query
 517     $sql = "{$this->_select} {$this->_from} {$this->_where} {$this->_groupBy} {$this->_having} {$this->_orderBy} {$this->_limit}";
 518
 519     // store the result in temporary table
 520     $insertCols = '';
 521     $insertQuery = "INSERT INTO {$this->_tempTableName} ( " . implode(',', array_keys($this->_columnHeaders)) . " )
 522 {$sql}";
 523     CRM_Core_DAO::executeQuery($insertQuery);
 524
 525     // now build the query for duration sum
 526     $this->from(TRUE);
 527     $this->where(TRUE);
 528
 529     // build the query to calulate duration sum
 530     $sql = "SELECT SUM(activity_civireport.duration) as civicrm_activity_duration_total {$this->_from} {$this->_where} {$this->_groupBy} {$this->_having} {$this->_orderBy} {$this->_limit}";
 531
 532     // create temp table to store duration
 533     $this->_tempDurationSumTableName = CRM_Core_DAO::createTempTableName('civicrm_activity');
 534     $tempQuery = "CREATE TEMPORARY TABLE {$this->_tempDurationSumTableName} (
 535       id int unsigned NOT NULL AUTO_INCREMENT, civicrm_activity_duration_total VARCHAR(128), PRIMARY KEY (id))";
 536     CRM_Core_DAO::executeQuery($tempQuery);
 537
 538     // store the result in temporary table
 539     $insertQuery = "INSERT INTO {$this->_tempDurationSumTableName} (civicrm_activity_duration_total)
 540     {$sql}";
 541     CRM_Core_DAO::executeQuery($insertQuery);
 542
 543     // build array of result based on column headers. This method also allows
 544     // modifying column headers before using it to build result set i.e $rows.
 545     $rows = array();
 546     $query = "SELECT {$this->_tempTableName}.*,  {$this->_tempDurationSumTableName}.civicrm_activity_duration_total
 547     FROM {$this->_tempTableName} INNER JOIN {$this->_tempDurationSumTableName}
 548       ON ({$this->_tempTableName}.id = {$this->_tempDurationSumTableName}.id)";
 549
 550     // finally add duration total to column headers
 551     $this->_columnHeaders['civicrm_activity_duration_total'] = array('no_display' => 1);
 552
 553     $this->buildRows($query, $rows);
 554
 555     // format result set.
 556     $this->formatDisplay($rows);
 557
 558     // assign variables to templates
 559     $this->doTemplateAssignment($rows);
 560
 561     //reset the sql building to default, which is used / called during other actions like "add to group"
 562     // now build the query for duration sum
 563     $this->from();
 564     $this->where();
 565
 566     // do print / pdf / instance stuff if needed
 567     $this->endPostProcess($rows);
 568   }
 569
 570   /**
 571    * @param $rows
 572    *
 573    * @return array
 574    */
 575   public function statistics(&$rows) {
 576     $statistics = parent::statistics($rows);
 577     $totalType = $totalActivity = $totalDuration = 0;
 578
 579     $query = "SELECT {$this->_tempTableName}.civicrm_activity_activity_type_id,
 580         {$this->_tempTableName}.civicrm_activity_id_count,
 581         {$this->_tempDurationSumTableName}.civicrm_activity_duration_total
 582     FROM {$this->_tempTableName} INNER JOIN {$this->_tempDurationSumTableName}
 583       ON ({$this->_tempTableName}.id = {$this->_tempDurationSumTableName}.id)";
 584
 585     $actDAO = CRM_Core_DAO::executeQuery($query);
 586
 587     $activityTypesCount = array();
 588     while ($actDAO->fetch()) {
 589       if (!in_array($actDAO->civicrm_activity_activity_type_id, $activityTypesCount)) {
 590         $activityTypesCount[] = $actDAO->civicrm_activity_activity_type_id;
 591       }
 592
 593       $totalActivity += $actDAO->civicrm_activity_id_count;
 594       $totalDuration += $actDAO->civicrm_activity_duration_total;
 595     }
 596
 597     $totalType = count($activityTypesCount);
 598
 599     $statistics['counts']['type'] = array(
 600       'title' => ts('Total Types'),
 601       'value' => $totalType,
 602     );
 603     $statistics['counts']['activities'] = array(
 604       'title' => ts('Total Number of Activities'),
 605       'value' => $totalActivity,
 606     );
 607     $statistics['counts']['duration'] = array(
 608       'title' => ts('Total Duration (in Minutes)'),
 609       'value' => $totalDuration,
 610     );
 611     return $statistics;
 612   }
 613
 614   public function modifyColumnHeaders() {
 615     //CRM-16719 modify name of column
 616     if (!empty($this->_columnHeaders['civicrm_activity_status_id'])) {
 617       $this->_columnHeaders['civicrm_activity_status_id']['title'] = "Status";
 618     }
 619   }
 620
 621   /**
 622    * Alter display of rows.
 623    *
 624    * Iterate through the rows retrieved via SQL and make changes for display purposes,
 625    * such as rendering contacts as links.
 626    *
 627    * @param array $rows
 628    *   Rows generated by SQL, with an array for each row.
 629    */
 630   public function alterDisplay(&$rows) {
 631     $entryFound = FALSE;
 632     $activityType = CRM_Core_PseudoConstant::activityType(TRUE, TRUE, FALSE, 'label', TRUE);
 633     $activityStatus = CRM_Core_PseudoConstant::activityStatus();
 634     $flagContact = 0;
 635     $onHover = ts('View Contact Summary for this Contact');
 636     foreach ($rows as $rowNum => $row) {
 637       // make count columns point to activity detail report
 638       if (!empty($row['civicrm_activity_id_count'])) {
 639         $url = array();
 640         $urlParams = array('activity_type_id', 'gid', 'status_id', 'contact_id');
 641         foreach ($urlParams as $field) {
 642           if (!empty($row['civicrm_activity_' . $field])) {
 643             $url[] = "{$field}_op=in&{$field}_value={$row['civicrm_activity_'.$field]}";
 644           }
 645           elseif (!empty($this->_params[$field . '_value'])) {
 646             $val = implode(",", $this->_params[$field . '_value']);
 647             $url[] = "{$field}_op=in&{$field}_value={$val}";
 648           }
 649         }
 650         $date_suffixes = array('relative', 'from', 'to');
 651         while (list(, $suffix) = each($date_suffixes)) {
 652           if (!empty($this->_params['activity_date_time_' . $suffix])) {
 653             list($from, $to)
 654               = $this->getFromTo(
 655                 CRM_Utils_Array::value("activity_date_time_relative", $this->_params),
 656                 CRM_Utils_Array::value("activity_date_time_from", $this->_params),
 657                 CRM_Utils_Array::value("activity_date_time_to", $this->_params)
 658                 );
 659             $url[] = "activity_date_time_from={$from}&activity_date_time_to={$to}";
 660             break;
 661           }
 662         }
 663         $url = implode('&', $url);
 664         $url = CRM_Report_Utils_Report::getNextUrl('activity', "reset=1&force=1&{$url}",
 665                  $this->_absoluteUrl,
 666                  $this->_id,
 667                  $this->_drilldownReport);
 668         $rows[$rowNum]['civicrm_activity_id_count_link'] = $url;
 669         $rows[$rowNum]['civicrm_activity_id_count_hover'] = ts('List all activity(s) for this row.');
 670         $entryFound = TRUE;
 671       }
 672
 673       if (array_key_exists('civicrm_contact_sort_name', $row) && $this->_outputMode != 'csv') {
 674         if ($value = $row['civicrm_contact_id']) {
 675
 676           if ($rowNum == 0) {
 677             $priviousContact = $value;
 678           }
 679           else {
 680             if ($priviousContact == $value) {
 681               $flagContact = 1;
 682               $priviousContact = $value;
 683             }
 684             else {
 685               $flagContact = 0;
 686               $priviousContact = $value;
 687             }
 688           }
 689
 690           if ($flagContact == 1) {
 691             $rows[$rowNum]['civicrm_contact_sort_name'] = "";
 692
 693             if (array_key_exists('civicrm_email_email', $row)) {
 694               $rows[$rowNum]['civicrm_email_email'] = "";
 695             }
 696             if (array_key_exists('civicrm_phone_phone', $row)) {
 697               $rows[$rowNum]['civicrm_phone_phone'] = "";
 698             }
 699           }
 700           else {
 701             $url = CRM_Utils_System::url('civicrm/contact/view',
 702               'reset=1&cid=' . $value,
 703               $this->_absoluteUrl
 704             );
 705
 706             $rows[$rowNum]['civicrm_contact_sort_name'] = "<a href='$url'>" . $row['civicrm_contact_sort_name'] .
 707               '</a>';
 708           }
 709           $entryFound = TRUE;
 710         }
 711       }
 712
 713       if (array_key_exists('civicrm_activity_activity_type_id', $row)) {
 714         if ($value = $row['civicrm_activity_activity_type_id']) {
 715
 716           $value = explode(',', $value);
 717           foreach ($value as $key => $id) {
 718             $value[$key] = $activityType[$id];
 719           }
 720
 721           $rows[$rowNum]['civicrm_activity_activity_type_id'] = implode(' , ', $value);
 722           $entryFound = TRUE;
 723         }
 724       }
 725
 726       if (array_key_exists('civicrm_activity_status_id', $row)) {
 727         if ($value = $row['civicrm_activity_status_id']) {
 728           $rows[$rowNum]['civicrm_activity_status_id'] = $activityStatus[$value];
 729           $entryFound = TRUE;
 730         }
 731       }
 732
 733       if (array_key_exists('civicrm_activity_duration', $row)) {
 734         if ($value = $row['civicrm_activity_duration']) {
 735           $rows[$rowNum]['civicrm_activity_duration'] = $rows[$rowNum]['civicrm_activity_duration_total'];
 736           $entryFound = TRUE;
 737         }
 738       }
 739
 740       if (!$entryFound) {
 741         break;
 742       }
 743     }
 744   }
 745
 746 }