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