CRM/Report/Form/Case/Summary.php

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