crm.fsf.org/20131203/files/sites/all/modules-old/civicrm/CRM/Report/Form/Case/Summary.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.6                                                |
   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   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' => $this->case_types,
 130           ),
 131           'status_id' => array(
 132             'title' => ts('Status'),
 133             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 134             'options' => $this->case_statuses,
 135           ),
 136           'is_deleted' => array(
 137             'title' => ts('Deleted?'),
 138             'type' => CRM_Report_Form::OP_INT,
 139             'operatorType' => CRM_Report_Form::OP_SELECT,
 140             'options' => $this->deleted_labels,
 141             'default' => 0,
 142           ),
 143         ),
 144       ),
 145       'civicrm_contact' => array(
 146         'dao' => 'CRM_Contact_DAO_Contact',
 147         'fields' => array(
 148           'sort_name' => array(
 149             'title' => ts('Staff Member'),
 150             'default' => TRUE,
 151           ),
 152         ),
 153         'filters' => array(
 154           'sort_name' => array(
 155             'title' => ts('Staff Member'),
 156           ),
 157         ),
 158       ),
 159       'civicrm_relationship' => array(
 160         'dao' => 'CRM_Contact_DAO_Relationship',
 161         'filters' => array(
 162           'relationship_type_id' => array(
 163             'title' => ts('Staff Relationship'),
 164             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 165             'options' => $this->rel_types,
 166           ),
 167         ),
 168       ),
 169       'civicrm_relationship_type' => array(
 170         'dao' => 'CRM_Contact_DAO_RelationshipType',
 171         'fields' => array(
 172           'label_b_a' => array(
 173             'title' => ts('Relationship'),
 174             'default' => TRUE,
 175           ),
 176         ),
 177       ),
 178       'civicrm_case_contact' => array(
 179         'dao' => 'CRM_Case_DAO_CaseContact',
 180       ),
 181     );
 182
 183     parent::__construct();
 184   }
 185
 186   public function preProcess() {
 187     parent::preProcess();
 188   }
 189
 190   public function select() {
 191     $select = array();
 192     $this->_columnHeaders = array();
 193     foreach ($this->_columns as $tableName => $table) {
 194       if (array_key_exists('fields', $table)) {
 195         foreach ($table['fields'] as $fieldName => $field) {
 196           if (!empty($field['required']) ||
 197             !empty($this->_params['fields'][$fieldName])
 198           ) {
 199
 200             if ($tableName == 'civicrm_relationship_type') {
 201               $this->_relField = TRUE;
 202             }
 203
 204             if ($fieldName == 'duration') {
 205               $select[] = "IF({$table['fields']['end_date']['dbAlias']} Is Null, '', DATEDIFF({$table['fields']['end_date']['dbAlias']}, {$table['fields']['start_date']['dbAlias']})) as {$tableName}_{$fieldName}";
 206             }
 207             else {
 208               $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 209             }
 210             $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 211             $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $field['title'];
 212           }
 213         }
 214       }
 215     }
 216
 217     $this->_select = "SELECT " . implode(', ', $select) . " ";
 218   }
 219
 220   /**
 221    * @param $fields
 222    * @param $files
 223    * @param $self
 224    *
 225    * @return array
 226    */
 227   public static function formRule($fields, $files, $self) {
 228     $errors = $grouping = array();
 229     if (empty($fields['relationship_type_id_value']) &&
 230       (array_key_exists('sort_name', $fields['fields']) ||
 231         array_key_exists('label_b_a', $fields['fields']))
 232     ) {
 233       $errors['fields'] = ts('Either filter on at least one relationship type, or de-select Staff Member and Relationship from the list of fields.');
 234     }
 235     if ((!empty($fields['relationship_type_id_value']) ||
 236         !empty($fields['sort_name_value'])) &&
 237       (!array_key_exists('sort_name', $fields['fields']) ||
 238         !array_key_exists('label_b_a', $fields['fields']))
 239     ) {
 240       $errors['fields'] = ts('To filter on Staff Member or Relationship, please also select Staff Member and Relationship from the list of fields.');
 241     }
 242     return $errors;
 243   }
 244
 245   public function from() {
 246
 247     $cc = $this->_aliases['civicrm_case'];
 248     $c = $this->_aliases['civicrm_contact'];
 249     $c2 = $this->_aliases['civicrm_c2'];
 250     $cr = $this->_aliases['civicrm_relationship'];
 251     $crt = $this->_aliases['civicrm_relationship_type'];
 252     $ccc = $this->_aliases['civicrm_case_contact'];
 253
 254     if ($this->_relField) {
 255       $this->_from = "
 256             FROM civicrm_contact $c
 257 inner join civicrm_relationship $cr on {$c}.id = ${cr}.contact_id_b
 258 inner join civicrm_case $cc on ${cc}.id = ${cr}.case_id
 259 inner join civicrm_relationship_type $crt on ${crt}.id=${cr}.relationship_type_id
 260 inner join civicrm_case_contact $ccc on ${ccc}.case_id = ${cc}.id
 261 inner join civicrm_contact $c2 on ${c2}.id=${ccc}.contact_id
 262 ";
 263     }
 264     else {
 265       $this->_from = "
 266             FROM civicrm_contact $c, civicrm_case $cc
 267 inner join civicrm_case_contact $ccc on ${ccc}.case_id = ${cc}.id
 268 inner join civicrm_contact $c2 on ${c2}.id=${ccc}.contact_id
 269 ";
 270     }
 271   }
 272
 273   public function where() {
 274     $clauses = array();
 275     $this->_having = '';
 276     foreach ($this->_columns as $tableName => $table) {
 277       if (array_key_exists('filters', $table)) {
 278         foreach ($table['filters'] as $fieldName => $field) {
 279           $clause = NULL;
 280           if (CRM_Utils_Array::value("operatorType", $field) & CRM_Report_Form::OP_DATE
 281           ) {
 282             $relative = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params);
 283             $from = CRM_Utils_Array::value("{$fieldName}_from", $this->_params);
 284             $to = CRM_Utils_Array::value("{$fieldName}_to", $this->_params);
 285
 286             $clause = $this->dateClause($field['dbAlias'], $relative, $from, $to,
 287               CRM_Utils_Array::value('type', $field)
 288             );
 289           }
 290           else {
 291
 292             $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
 293             if ($fieldName == 'case_type_id') {
 294               $value = CRM_Utils_Array::value("{$fieldName}_value", $this->_params);
 295               if (!empty($value)) {
 296                 $operator = '';
 297                 if ($op == 'notin') {
 298                   $operator = 'NOT';
 299                 }
 300
 301                 $regexp = "[[:cntrl:]]*" . implode('[[:>:]]*|[[:<:]]*', $value) . "[[:cntrl:]]*";
 302                 $clause = "{$field['dbAlias']} {$operator} REGEXP '{$regexp}'";
 303               }
 304               $op = NULL;
 305             }
 306
 307             if ($op) {
 308               $clause = $this->whereClause($field,
 309                 $op,
 310                 CRM_Utils_Array::value("{$fieldName}_value", $this->_params),
 311                 CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
 312                 CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
 313               );
 314             }
 315           }
 316
 317           if (!empty($clause)) {
 318             $clauses[] = $clause;
 319           }
 320         }
 321       }
 322     }
 323
 324     if (empty($clauses)) {
 325       $this->_where = "WHERE ( 1 ) ";
 326     }
 327     else {
 328       $this->_where = "WHERE " . implode(' AND ', $clauses);
 329     }
 330   }
 331
 332   public function groupBy() {
 333     $this->_groupBy = "GROUP BY {$this->_aliases['civicrm_c2']}.id";
 334   }
 335
 336   public function postProcess() {
 337
 338     $this->beginPostProcess();
 339
 340     $sql = $this->buildQuery(TRUE);
 341
 342     $rows = $graphRows = array();
 343     $this->buildRows($sql, $rows);
 344
 345     $this->formatDisplay($rows);
 346     $this->doTemplateAssignment($rows);
 347     $this->endPostProcess($rows);
 348   }
 349
 350   /**
 351    * Alter display of rows.
 352    *
 353    * Iterate through the rows retrieved via SQL and make changes for display purposes,
 354    * such as rendering contacts as links.
 355    *
 356    * @param array $rows
 357    *   Rows generated by SQL, with an array for each row.
 358    */
 359   public function alterDisplay(&$rows) {
 360     $entryFound = FALSE;
 361     foreach ($rows as $rowNum => $row) {
 362       if (array_key_exists('civicrm_case_status_id', $row)) {
 363         if ($value = $row['civicrm_case_status_id']) {
 364           $rows[$rowNum]['civicrm_case_status_id'] = $this->case_statuses[$value];
 365           $entryFound = TRUE;
 366         }
 367       }
 368
 369       if (array_key_exists('civicrm_case_case_type_id', $row) &&
 370         !empty($rows[$rowNum]['civicrm_case_case_type_id'])
 371       ) {
 372         $value = $row['civicrm_case_case_type_id'];
 373         $typeIds = explode(CRM_Core_DAO::VALUE_SEPARATOR, $value);
 374         $value = array();
 375         foreach ($typeIds as $typeId) {
 376           if ($typeId) {
 377             $value[$typeId] = $this->case_types[$typeId];
 378           }
 379         }
 380         $rows[$rowNum]['civicrm_case_case_type_id'] = implode(', ', $value);
 381         $entryFound = TRUE;
 382       }
 383
 384       // convert Case ID and Subject to links to Manage Case
 385       if (array_key_exists('civicrm_case_id', $row) &&
 386         !empty($rows[$rowNum]['civicrm_c2_id'])
 387       ) {
 388         $url = CRM_Utils_System::url("civicrm/contact/view/case",
 389           'reset=1&action=view&cid=' . $row['civicrm_c2_id'] . '&id=' .
 390           $row['civicrm_case_id'],
 391           $this->_absoluteUrl
 392         );
 393         $rows[$rowNum]['civicrm_case_id_link'] = $url;
 394         $rows[$rowNum]['civicrm_case_id_hover'] = ts("Manage Case");
 395         $entryFound = TRUE;
 396       }
 397       if (array_key_exists('civicrm_case_subject', $row) &&
 398         !empty($rows[$rowNum]['civicrm_c2_id'])
 399       ) {
 400         $url = CRM_Utils_System::url("civicrm/contact/view/case",
 401           'reset=1&action=view&cid=' . $row['civicrm_c2_id'] . '&id=' .
 402           $row['civicrm_case_id'],
 403           $this->_absoluteUrl
 404         );
 405         $rows[$rowNum]['civicrm_case_subject_link'] = $url;
 406         $rows[$rowNum]['civicrm_case_subject_hover'] = ts("Manage Case");
 407         $entryFound = TRUE;
 408       }
 409
 410       if (array_key_exists('civicrm_case_is_deleted', $row)) {
 411         $value = $row['civicrm_case_is_deleted'];
 412         $rows[$rowNum]['civicrm_case_is_deleted'] = $this->deleted_labels[$value];
 413         $entryFound = TRUE;
 414       }
 415
 416       if (!$entryFound) {
 417         break;
 418       }
 419     }
 420   }
 421
 422 }