CRM/Report/Form/Case/Summary.php

   1 <?php
   2
   3 /*
   4  +--------------------------------------------------------------------+
   5  | CiviCRM version 4.4                                                |
   6  +--------------------------------------------------------------------+
   7  | Copyright CiviCRM LLC (c) 2004-2013                                |
   8  +--------------------------------------------------------------------+
   9  | This file is a part of CiviCRM.                                    |
  10  |                                                                    |
  11  | CiviCRM is free software; you can copy, modify, and distribute it  |
  12  | under the terms of the GNU Affero General Public License           |
  13  | Version 3, 19 November 2007 and the CiviCRM Licensing Exception.   |
  14  |                                                                    |
  15  | CiviCRM is distributed in the hope that it will be useful, but     |
  16  | WITHOUT ANY WARRANTY; without even the implied warranty of         |
  17  | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
  18  | See the GNU Affero General Public License for more details.        |
  19  |                                                                    |
  20  | You should have received a copy of the GNU Affero General Public   |
  21  | License and the CiviCRM Licensing Exception along                  |
  22  | with this program; if not, contact CiviCRM LLC                     |
  23  | at info[AT]civicrm[DOT]org. If you have questions about the        |
  24  | GNU Affero General Public License or the licensing of CiviCRM,     |
  25  | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
  26  +--------------------------------------------------------------------+
  27 */
  28
  29 /**
  30  *
  31  * @package CRM
  32  * @copyright CiviCRM LLC (c) 2004-2013
  33  * $Id$
  34  *
  35  */
  36 class CRM_Report_Form_Case_Summary extends CRM_Report_Form {
  37
  38   protected $_summary = NULL;
  39   protected $_relField = FALSE;
  40   function __construct() {
  41     $this->case_types    = CRM_Case_PseudoConstant::caseType();
  42     $this->case_statuses = CRM_Case_PseudoConstant::caseStatus();
  43     $rels                = CRM_Core_PseudoConstant::relationshipType();
  44     foreach ($rels as $relid => $v) {
  45       $this->rel_types[$relid] = $v['label_b_a'];
  46     }
  47
  48     $this->deleted_labels = array('' => ts('- select -'), 0 => ts('No'), 1 => ts('Yes'));
  49
  50     $this->_columns = array(
  51       'civicrm_c2' =>
  52       array(
  53         'dao' => 'CRM_Contact_DAO_Contact',
  54         'fields' =>
  55         array(
  56           'client_name' =>
  57           array(
  58             'name' => 'sort_name',
  59             'title' => ts('Client'),
  60             'required' => TRUE,
  61           ),
  62           'id' =>
  63           array(
  64             'no_display' => TRUE,
  65             'required' => TRUE,
  66           ),
  67         ),
  68       ),
  69       'civicrm_case' =>
  70       array(
  71         'dao' => 'CRM_Case_DAO_Case',
  72         'fields' =>
  73         array(
  74           'id' =>
  75           array('title' => ts('Case ID'),
  76             'required' => TRUE,
  77           ),
  78           'subject' => array(
  79             'title' => ts('Case Subject'), 'default' => TRUE,
  80           ),
  81           'status_id' => array(
  82             'title' => ts('Status'), 'default' => TRUE,
  83           ),
  84           'case_type_id' => array(
  85             'title' => ts('Case Type'), 'default' => TRUE,
  86           ),
  87           'start_date' => array(
  88             'title' => ts('Start Date'), 'default' => TRUE,
  89             'type' => CRM_Utils_Type::T_DATE,
  90           ),
  91           'end_date' => array(
  92             'title' => ts('End Date'), 'default' => TRUE,
  93             'type' => CRM_Utils_Type::T_DATE,
  94           ),
  95           'duration' => array(
  96             'title' => ts('Duration (Days)'), 'default' => FALSE,
  97           ),
  98           'is_deleted' => array(
  99             'title' => ts('Deleted?'), 'default' => FALSE, 'type' => CRM_Utils_Type::T_INT,
 100           ),
 101         ),
 102         'filters' =>
 103         array('start_date' => array('title' => ts('Start Date'),
 104             'operatorType' => CRM_Report_Form::OP_DATE,
 105             'type' => CRM_Utils_Type::T_DATE,
 106           ),
 107           'end_date' => array('title' => ts('End Date'),
 108             'operatorType' => CRM_Report_Form::OP_DATE,
 109             'type' => CRM_Utils_Type::T_DATE,
 110           ),
 111           'case_type_id' => array('title' => ts('Case Type'),
 112             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 113             'options' => $this->case_types,
 114           ),
 115           'status_id' => array('title' => ts('Status'),
 116             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 117             'options' => $this->case_statuses,
 118           ),
 119           'is_deleted' => array('title' => ts('Deleted?'),
 120             'type' => CRM_Report_Form::OP_INT,
 121             'operatorType' => CRM_Report_Form::OP_SELECT,
 122             'options' => $this->deleted_labels,
 123             'default' => 0,
 124           ),
 125         ),
 126       ),
 127       'civicrm_contact' =>
 128       array(
 129         'dao' => 'CRM_Contact_DAO_Contact',
 130         'fields' =>
 131         array(
 132           'sort_name' =>
 133           array('title' => ts('Staff Member'),
 134             'default' => TRUE,
 135           ),
 136         ),
 137         'filters' =>
 138         array('sort_name' => array('title' => ts('Staff Member'),
 139           ),
 140         ),
 141       ),
 142       'civicrm_relationship' =>
 143       array(
 144         'dao' => 'CRM_Contact_DAO_Relationship',
 145         'filters' =>
 146         array('relationship_type_id' => array('title' => ts('Staff Relationship'),
 147             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 148             'options' => $this->rel_types,
 149           ),
 150         ),
 151       ),
 152       'civicrm_relationship_type' =>
 153       array(
 154         'dao' => 'CRM_Contact_DAO_RelationshipType',
 155         'fields' =>
 156         array(
 157           'label_b_a' =>
 158           array(
 159             'title' => ts('Relationship'), 'default' => TRUE,
 160           ),
 161         ),
 162       ),
 163       'civicrm_case_contact' =>
 164       array(
 165         'dao' => 'CRM_Case_DAO_CaseContact',
 166       ),
 167     );
 168
 169     parent::__construct();
 170   }
 171
 172   function preProcess() {
 173     parent::preProcess();
 174   }
 175
 176   function select() {
 177     $select = array();
 178     $this->_columnHeaders = array();
 179     foreach ($this->_columns as $tableName => $table) {
 180       if (array_key_exists('fields', $table)) {
 181         foreach ($table['fields'] as $fieldName => $field) {
 182           if (!empty($field['required']) ||
 183             CRM_Utils_Array::value($fieldName, $this->_params['fields'])
 184           ) {
 185
 186             if ($tableName == 'civicrm_relationship_type') {
 187               $this->_relField = TRUE;
 188             }
 189
 190             if ($fieldName == 'duration') {
 191               $select[] = "IF({$table['fields']['end_date']['dbAlias']} Is Null, '', DATEDIFF({$table['fields']['end_date']['dbAlias']}, {$table['fields']['start_date']['dbAlias']})) as {$tableName}_{$fieldName}";
 192             }
 193             else {
 194               $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 195             }
 196             $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 197             $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $field['title'];
 198           }
 199         }
 200       }
 201     }
 202
 203     $this->_select = "SELECT " . implode(', ', $select) . " ";
 204   }
 205
 206   static function formRule($fields, $files, $self) {
 207     $errors = $grouping = array();
 208     if (empty($fields['relationship_type_id_value']) && (array_key_exists('sort_name', $fields['fields']) || array_key_exists('label_b_a', $fields['fields']))) {
 209       $errors['fields'] = ts('Either filter on at least one relationship type, or de-select Staff Member and Relationship from the list of fields.');
 210     }
 211     if ((!empty($fields['relationship_type_id_value']) || !empty($fields['sort_name_value'])) && (!array_key_exists('sort_name', $fields['fields']) || !array_key_exists('label_b_a', $fields['fields']))) {
 212       $errors['fields'] = ts('To filter on Staff Member or Relationship, please also select Staff Member and Relationship from the list of fields.');
 213     }
 214     return $errors;
 215   }
 216
 217   function from() {
 218
 219     $cc  = $this->_aliases['civicrm_case'];
 220     $c   = $this->_aliases['civicrm_contact'];
 221     $c2  = $this->_aliases['civicrm_c2'];
 222     $cr  = $this->_aliases['civicrm_relationship'];
 223     $crt = $this->_aliases['civicrm_relationship_type'];
 224     $ccc = $this->_aliases['civicrm_case_contact'];
 225
 226     if ($this->_relField) {
 227       $this->_from = "
 228             FROM civicrm_contact $c
 229 inner join civicrm_relationship $cr on {$c}.id = ${cr}.contact_id_b
 230 inner join civicrm_case $cc on ${cc}.id = ${cr}.case_id
 231 inner join civicrm_relationship_type $crt on ${crt}.id=${cr}.relationship_type_id
 232 inner join civicrm_case_contact $ccc on ${ccc}.case_id = ${cc}.id
 233 inner join civicrm_contact $c2 on ${c2}.id=${ccc}.contact_id
 234 ";
 235     }
 236     else {
 237       $this->_from = "
 238             FROM civicrm_case $cc
 239 inner join civicrm_case_contact $ccc on ${ccc}.case_id = ${cc}.id
 240 inner join civicrm_contact $c2 on ${c2}.id=${ccc}.contact_id
 241 ";
 242     }
 243   }
 244
 245   function where() {
 246     $clauses = array();
 247     $this->_having = '';
 248     foreach ($this->_columns as $tableName => $table) {
 249       if (array_key_exists('filters', $table)) {
 250         foreach ($table['filters'] as $fieldName => $field) {
 251           $clause = NULL;
 252           if (CRM_Utils_Array::value("operatorType", $field) & CRM_Report_Form::OP_DATE) {
 253             $relative = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params);
 254             $from     = CRM_Utils_Array::value("{$fieldName}_from", $this->_params);
 255             $to       = CRM_Utils_Array::value("{$fieldName}_to", $this->_params);
 256
 257             $clause = $this->dateClause($field['dbAlias'], $relative, $from, $to,
 258               CRM_Utils_Array::value('type', $field)
 259             );
 260           }
 261           else {
 262
 263             $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
 264             if ($fieldName == 'case_type_id') {
 265               $value = CRM_Utils_Array::value("{$fieldName}_value", $this->_params);
 266               if (!empty($value)) {
 267                 $clause = "( {$field['dbAlias']} REGEXP '[[:<:]]" . implode('[[:>:]]|[[:<:]]', $value) . "[[:>:]]' )";
 268               }
 269               $op = NULL;
 270             }
 271
 272             if ($op) {
 273               $clause = $this->whereClause($field,
 274                 $op,
 275                 CRM_Utils_Array::value("{$fieldName}_value", $this->_params),
 276                 CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
 277                 CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
 278               );
 279             }
 280           }
 281
 282           if (!empty($clause)) {
 283             $clauses[] = $clause;
 284           }
 285         }
 286       }
 287     }
 288
 289     if (empty($clauses)) {
 290       $this->_where = "WHERE ( 1 ) ";
 291     }
 292     else {
 293       $this->_where = "WHERE " . implode(' AND ', $clauses);
 294     }
 295   }
 296
 297   function groupBy() {
 298     $this->_groupBy = "";
 299   }
 300
 301   function postProcess() {
 302
 303     $this->beginPostProcess();
 304
 305     $sql = $this->buildQuery(TRUE);
 306
 307     $rows = $graphRows = array();
 308     $this->buildRows($sql, $rows);
 309
 310     $this->formatDisplay($rows);
 311     $this->doTemplateAssignment($rows);
 312     $this->endPostProcess($rows);
 313   }
 314
 315   function alterDisplay(&$rows) {
 316     $entryFound = FALSE;
 317     foreach ($rows as $rowNum => $row) {
 318       if (array_key_exists('civicrm_case_status_id', $row)) {
 319         if ($value = $row['civicrm_case_status_id']) {
 320           $rows[$rowNum]['civicrm_case_status_id'] = $this->case_statuses[$value];
 321           $entryFound = TRUE;
 322         }
 323       }
 324
 325       if (array_key_exists('civicrm_case_case_type_id', $row) &&
 326         CRM_Utils_Array::value('civicrm_case_case_type_id', $rows[$rowNum])
 327       ) {
 328         $value   = $row['civicrm_case_case_type_id'];
 329         $typeIds = explode(CRM_Core_DAO::VALUE_SEPARATOR, $value);
 330         $value   = array();
 331         foreach ($typeIds as $typeId) {
 332           if ($typeId) {
 333             $value[$typeId] = $this->case_types[$typeId];
 334           }
 335         }
 336         $rows[$rowNum]['civicrm_case_case_type_id'] = implode(', ', $value);
 337         $entryFound = TRUE;
 338       }
 339
 340       // convert Case ID and Subject to links to Manage Case
 341       if (array_key_exists('civicrm_case_id', $row) &&
 342         CRM_Utils_Array::value('civicrm_c2_id', $rows[$rowNum])
 343       ) {
 344         $url = CRM_Utils_System::url("civicrm/contact/view/case",
 345           'reset=1&action=view&cid=' . $row['civicrm_c2_id'] . '&id=' . $row['civicrm_case_id'],
 346           $this->_absoluteUrl
 347         );
 348         $rows[$rowNum]['civicrm_case_id_link'] = $url;
 349         $rows[$rowNum]['civicrm_case_id_hover'] = ts("Manage Case");
 350         $entryFound = TRUE;
 351       }
 352       if (array_key_exists('civicrm_case_subject', $row) &&
 353         CRM_Utils_Array::value('civicrm_c2_id', $rows[$rowNum])
 354       ) {
 355         $url = CRM_Utils_System::url("civicrm/contact/view/case",
 356           'reset=1&action=view&cid=' . $row['civicrm_c2_id'] . '&id=' . $row['civicrm_case_id'],
 357           $this->_absoluteUrl
 358         );
 359         $rows[$rowNum]['civicrm_case_subject_link'] = $url;
 360         $rows[$rowNum]['civicrm_case_subject_hover'] = ts("Manage Case");
 361         $entryFound = TRUE;
 362       }
 363
 364       if (array_key_exists('civicrm_case_is_deleted', $row)) {
 365         $value = $row['civicrm_case_is_deleted'];
 366         $rows[$rowNum]['civicrm_case_is_deleted'] = $this->deleted_labels[$value];
 367         $entryFound = TRUE;
 368       }
 369
 370       if (!$entryFound) {
 371         break;
 372       }
 373     }
 374   }
 375 }
 376