CRM/Report/Form/ActivitySummary.php

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