CRM/Report/Form/Case/Summary.php

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