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