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