CRM/Report/Form/Case/TimeSpent.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.7                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2016                                |
   7  +--------------------------------------------------------------------+
   8  | This file is a part of CiviCRM.                                    |
   9  |                                                                    |
  10  | CiviCRM is free software; you can copy, modify, and distribute it  |
  11  | under the terms of the GNU Affero General Public License           |
  12  | Version 3, 19 November 2007 and the CiviCRM Licensing Exception.   |
  13  |                                                                    |
  14  | CiviCRM is distributed in the hope that it will be useful, but     |
  15  | WITHOUT ANY WARRANTY; without even the implied warranty of         |
  16  | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
  17  | See the GNU Affero General Public License for more details.        |
  18  |                                                                    |
  19  | You should have received a copy of the GNU Affero General Public   |
  20  | License and the CiviCRM Licensing Exception along                  |
  21  | with this program; if not, contact CiviCRM LLC                     |
  22  | at info[AT]civicrm[DOT]org. If you have questions about the        |
  23  | GNU Affero General Public License or the licensing of CiviCRM,     |
  24  | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
  25  +--------------------------------------------------------------------+
  26  */
  27
  28 /**
  29  *
  30  * @package CRM
  31  * @copyright CiviCRM LLC (c) 2004-2016
  32  * $Id$
  33  *
  34  */
  35 class CRM_Report_Form_Case_TimeSpent extends CRM_Report_Form {
  36   /**
  37    */
  38   /**
  39    */
  40   public function __construct() {
  41
  42     $this->activityTypes = CRM_Core_PseudoConstant::activityType(TRUE, TRUE);
  43     asort($this->activityTypes);
  44     $this->activityStatuses = CRM_Core_PseudoConstant::activityStatus();
  45
  46     $this->_columns = array(
  47       'civicrm_contact' => array(
  48         'dao' => 'CRM_Contact_DAO_Contact',
  49         'fields' => array(
  50           'id' => array(
  51             'title' => ts('Contact ID'),
  52             'no_display' => TRUE,
  53             'required' => TRUE,
  54           ),
  55           'sort_name' => array(
  56             'title' => ts('Contact Name'),
  57             'required' => TRUE,
  58             'no_repeat' => TRUE,
  59           ),
  60         ),
  61         'filters' => array(
  62           'sort_name' => array(
  63             'title' => ts('Contact Name'),
  64             'operator' => 'like',
  65             'type' => CRM_Report_Form::OP_STRING,
  66           ),
  67         ),
  68       ),
  69       'civicrm_activity' => array(
  70         'dao' => 'CRM_Activity_DAO_Activity',
  71         'fields' => array(
  72           'activity_type_id' => array(
  73             'title' => ts('Activity Type'),
  74             'default' => TRUE,
  75             'type' => CRM_Utils_Type::T_STRING,
  76           ),
  77           'activity_date_time' => array(
  78             'title' => ts('Activity Date'),
  79             'default' => TRUE,
  80           ),
  81           'status_id' => array(
  82             'title' => ts('Activity Status'),
  83             'default' => FALSE,
  84             'type' => CRM_Utils_Type::T_STRING,
  85           ),
  86           'id' => array(
  87             'title' => ts('Activity ID'),
  88             'default' => TRUE,
  89           ),
  90           'duration' => array(
  91             'title' => ts('Duration'),
  92             'default' => TRUE,
  93             'type' => CRM_Utils_Type::T_INT,
  94           ),
  95           'subject' => array(
  96             'title' => ts('Activity Subject'),
  97             'default' => FALSE,
  98           ),
  99         ),
 100         'filters' => array(
 101           'activity_date_time' => array(
 102             'operatorType' => CRM_Report_Form::OP_DATE,
 103           ),
 104           'subject' => array(
 105             'title' => ts('Activity Subject'),
 106             'type' => CRM_Utils_Type::T_STRING,
 107             'operator' => 'like',
 108           ),
 109           'activity_type_id' => array(
 110             'title' => ts('Activity Type'),
 111             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 112             'options' => $this->activityTypes,
 113           ),
 114           'status_id' => array(
 115             'title' => ts('Activity Status'),
 116             'type' => CRM_Utils_Type::T_INT,
 117             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 118             'options' => $this->activityStatuses,
 119           ),
 120         ),
 121       ),
 122       'civicrm_activity_source' => array(
 123         'dao' => 'CRM_Activity_DAO_ActivityContact',
 124         'fields' => array(
 125           'contact_id' => array(
 126             'title' => ts('Contact ID'),
 127             'default' => TRUE,
 128             'no_display' => TRUE,
 129           ),
 130         ),
 131         'group_bys' => array(
 132           'contact_id' => array(
 133             'title' => ts('Totals Only'),
 134             'default' => TRUE,
 135           ),
 136         ),
 137         'grouping' => 'activity-fields',
 138       ),
 139       'civicrm_case_activity' => array(
 140         'dao' => 'CRM_Case_DAO_CaseActivity',
 141         'fields' => array(
 142           'case_id' => array(
 143             'title' => ts('Case ID'),
 144             'default' => FALSE,
 145           ),
 146         ),
 147         'filters' => array(
 148           'case_id_filter' => array(
 149             'name' => 'case_id',
 150             'title' => ts('Cases?'),
 151             'operatorType' => CRM_Report_Form::OP_SELECT,
 152             'options' => array(
 153               1 => ts('Exclude non-case'),
 154               2 => ts('Exclude cases'),
 155               3 => ts('Include Both'),
 156             ),
 157             'default' => 3,
 158           ),
 159         ),
 160       ),
 161     );
 162
 163     parent::__construct();
 164   }
 165
 166   public function select() {
 167     $select = array();
 168     $this->_columnHeaders = array();
 169
 170     $this->has_grouping = !empty($this->_params['group_bys']);
 171     $this->has_activity_type = FALSE;
 172
 173     foreach ($this->_columns as $tableName => $table) {
 174       if (array_key_exists('fields', $table)) {
 175         foreach ($table['fields'] as $fieldName => $field) {
 176           if (!empty($field['required']) ||
 177             (!empty($this->_params['fields'][$fieldName]) &&
 178               ((!$this->has_grouping) ||
 179                 !in_array($fieldName, array('case_id', 'subject', 'status_id')))
 180             )
 181           ) {
 182
 183             $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 184             $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
 185             $this->_columnHeaders["{$tableName}_{$fieldName}"]['no_display'] = CRM_Utils_Array::value('no_display', $field);
 186
 187             if ($fieldName == 'activity_type_id') {
 188               $this->has_activity_type = TRUE;
 189             }
 190
 191             if ($fieldName == 'duration' && $this->has_grouping) {
 192               $select[] = "SUM({$field['dbAlias']}) as {$tableName}_{$fieldName}";
 193             }
 194             elseif ($fieldName == 'activity_date_time' && $this->has_grouping) {
 195               $select[] = "EXTRACT(YEAR_MONTH FROM {$field['dbAlias']}) AS {$tableName}_{$fieldName}";
 196               $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = ts('Month/Year');
 197             }
 198             elseif ($tableName == 'civicrm_activity' && $fieldName == 'id' &&
 199               $this->has_grouping
 200             ) {
 201               $select[] = "COUNT({$field['dbAlias']}) AS {$tableName}_{$fieldName}";
 202               $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = ts('# Activities');
 203             }
 204             else {
 205               $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 206             }
 207           }
 208         }
 209       }
 210     }
 211     $this->_selectClauses = $select;
 212
 213     $this->_select = "SELECT " . implode(', ', $select) . " ";
 214   }
 215
 216   public function from() {
 217
 218     $this->_from = "
 219         FROM civicrm_activity {$this->_aliases['civicrm_activity']}
 220              LEFT JOIN civicrm_activity_contact {$this->_aliases['civicrm_activity_source']}
 221                     ON {$this->_aliases['civicrm_activity']}.id = {$this->_aliases['civicrm_activity_source']}.activity_id
 222              LEFT JOIN civicrm_contact {$this->_aliases['civicrm_contact']}
 223                     ON {$this->_aliases['civicrm_activity_source']}.contact_id = {$this->_aliases['civicrm_contact']}.id
 224              LEFT JOIN civicrm_case_activity {$this->_aliases['civicrm_case_activity']}
 225                     ON {$this->_aliases['civicrm_case_activity']}.activity_id = {$this->_aliases['civicrm_activity']}.id
 226 ";
 227   }
 228
 229   public function where() {
 230     $this->_where = " WHERE {$this->_aliases['civicrm_activity']}.is_current_revision = 1 AND
 231                                 {$this->_aliases['civicrm_activity']}.is_deleted = 0 AND
 232                                 {$this->_aliases['civicrm_activity']}.is_test = 0";
 233     $clauses = array();
 234     foreach ($this->_columns as $tableName => $table) {
 235       if (array_key_exists('filters', $table)) {
 236
 237         foreach ($table['filters'] as $fieldName => $field) {
 238           $clause = NULL;
 239           if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE) {
 240             $relative = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params);
 241             $from = CRM_Utils_Array::value("{$fieldName}_from", $this->_params);
 242             $to = CRM_Utils_Array::value("{$fieldName}_to", $this->_params);
 243
 244             $clause = $this->dateClause($field['dbAlias'], $relative, $from, $to);
 245           }
 246           else {
 247             $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
 248             if ($op) {
 249               // handle special case
 250               if ($fieldName == 'case_id_filter') {
 251                 $choice = CRM_Utils_Array::value("{$fieldName}_value", $this->_params);
 252                 if ($choice == 1) {
 253                   $clause = "({$this->_aliases['civicrm_case_activity']}.id Is Not Null)";
 254                 }
 255                 elseif ($choice == 2) {
 256                   $clause = "({$this->_aliases['civicrm_case_activity']}.id Is Null)";
 257                 }
 258               }
 259               else {
 260                 $clause = $this->whereClause($field,
 261                   $op,
 262                   CRM_Utils_Array::value("{$fieldName}_value", $this->_params),
 263                   CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
 264                   CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
 265                 );
 266               }
 267             }
 268           }
 269
 270           if (!empty($clause)) {
 271             $clauses[] = $clause;
 272           }
 273         }
 274       }
 275     }
 276
 277     if (empty($clauses)) {
 278       $this->_where .= " ";
 279     }
 280     else {
 281       $this->_where .= " AND " . implode(' AND ', $clauses);
 282     }
 283   }
 284
 285   public function groupBy() {
 286     $this->_groupBy = '';
 287     if ($this->has_grouping) {
 288       $groupBy = array(
 289         "{$this->_aliases['civicrm_contact']}.id",
 290         "civicrm_activity_activity_date_time"
 291       );
 292       if ($this->has_activity_type) {
 293         $groupBy[] = "{$this->_aliases['civicrm_activity']}.activity_type_id";
 294       }
 295
 296       $this->_groupBy = "GROUP BY " . implode(', ', $groupBy);
 297       $this->_groupBy .= CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, $groupBy);
 298     }
 299   }
 300
 301   public function orderBy() {
 302     $this->_orderBy = "ORDER BY {$this->_aliases['civicrm_contact']}.sort_name, {$this->_aliases['civicrm_contact']}.id";
 303   }
 304
 305   public function postProcess() {
 306     parent::postProcess();
 307   }
 308
 309   /**
 310    * @param $fields
 311    * @param $files
 312    * @param $self
 313    *
 314    * @return array
 315    */
 316   public static function formRule($fields, $files, $self) {
 317     $errors = array();
 318     if (!empty($fields['group_bys']) &&
 319       (!array_key_exists('id', $fields['fields']) ||
 320         !array_key_exists('activity_date_time', $fields['fields']) ||
 321         !array_key_exists('duration', $fields['fields']))
 322     ) {
 323       $errors['fields'] = ts('To view totals please select all of activity id, date and duration.');
 324     }
 325     return $errors;
 326   }
 327
 328   /**
 329    * Alter display of rows.
 330    *
 331    * Iterate through the rows retrieved via SQL and make changes for display purposes,
 332    * such as rendering contacts as links.
 333    *
 334    * @param array $rows
 335    *   Rows generated by SQL, with an array for each row.
 336    */
 337   public function alterDisplay(&$rows) {
 338
 339     $entryFound = FALSE;
 340     foreach ($rows as $rowNum => $row) {
 341
 342       if (isset($row['civicrm_activity_activity_type_id'])) {
 343         $entryFound = TRUE;
 344         $val = $row['civicrm_activity_activity_type_id'];
 345         $rows[$rowNum]['civicrm_activity_activity_type_id'] = isset($this->activityTypes[$val]) ? $this->activityTypes[$val] : '';
 346       }
 347
 348       if (isset($row['civicrm_activity_status_id'])) {
 349         $entryFound = TRUE;
 350         $val = $row['civicrm_activity_status_id'];
 351         $rows[$rowNum]['civicrm_activity_status_id'] = isset($this->activityStatuses[$val]) ? $this->activityStatuses[$val] : '';
 352       }
 353
 354       // The next two make it easier to make pivot tables after exporting to Excel
 355       if (isset($row['civicrm_activity_duration'])) {
 356         $entryFound = TRUE;
 357         $rows[$rowNum]['civicrm_activity_duration'] = (int) $row['civicrm_activity_duration'];
 358       }
 359
 360       if (isset($row['civicrm_case_activity_case_id'])) {
 361         $entryFound = TRUE;
 362         $rows[$rowNum]['civicrm_case_activity_case_id'] = (int) $row['civicrm_case_activity_case_id'];
 363       }
 364
 365       if (!$entryFound) {
 366         break;
 367       }
 368     }
 369   }
 370
 371 }