CRM/Report/Form/Case/Demographics.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | Copyright CiviCRM LLC. All rights reserved.                        |
   5  |                                                                    |
   6  | This work is published under the GNU AGPLv3 license with some      |
   7  | permitted exceptions and without any warranty. For full license    |
   8  | and copyright information, see https://civicrm.org/licensing       |
   9  +--------------------------------------------------------------------+
  10  */
  11
  12 /**
  13  *
  14  * @package CRM
  15  * @copyright CiviCRM LLC https://civicrm.org/licensing
  16  */
  17 class CRM_Report_Form_Case_Demographics extends CRM_Report_Form {
  18
  19   protected $_summary = NULL;
  20
  21   protected $_emailField = FALSE;
  22
  23   protected $_phoneField = FALSE;
  24   /**
  25    * This report has not been optimised for group filtering.
  26    *
  27    * The functionality for group filtering has been improved but not
  28    * all reports have been adjusted to take care of it. This report has not
  29    * and will run an inefficient query until fixed.
  30    *
  31    * CRM-19170
  32    *
  33    * @var bool
  34    */
  35   protected $groupFilterNotOptimised = TRUE;
  36
  37   /**
  38    * Class constructor.
  39    */
  40   public function __construct() {
  41     $this->_columns = [
  42       'civicrm_contact' => [
  43         'dao' => 'CRM_Contact_DAO_Contact',
  44         'fields' => [
  45           'sort_name' => [
  46             'title' => ts('Contact Name'),
  47             'required' => TRUE,
  48             'no_repeat' => TRUE,
  49           ],
  50           'gender_id' => [
  51             'title' => ts('Gender'),
  52             'default' => TRUE,
  53           ],
  54           'birth_date' => [
  55             'title' => ts('Birthdate'),
  56             'default' => FALSE,
  57           ],
  58           'id' => [
  59             'no_display' => TRUE,
  60             'required' => TRUE,
  61           ],
  62         ],
  63         'filters' => [
  64           'sort_name' => [
  65             'title' => ts('Contact Name'),
  66             'operatorType' => CRM_Report_Form::OP_STRING,
  67           ],
  68           'contact_type' => [
  69             'title' => ts('Contact Type'),
  70             'operatorType' => CRM_Report_Form::OP_SELECT,
  71             'options' => [
  72               '' => ts('-select-'),
  73               'Individual' => ts('Individual'),
  74               'Organization' => ts('Organization'),
  75               'Household' => ts('Household'),
  76             ],
  77             'default' => 'Individual',
  78           ],
  79           'id' => [
  80             'title' => ts('Contact ID'),
  81             'no_display' => TRUE,
  82           ],
  83         ],
  84         'order_bys' => [
  85           'sort_name' => [
  86             'title' => ts('Contact Name'),
  87             'default_weight' => '1',
  88             'dbAlias' => 'civicrm_contact_sort_name',
  89           ],
  90         ],
  91         'grouping' => 'contact-fields',
  92       ],
  93       'civicrm_email' => [
  94         'dao' => 'CRM_Core_DAO_Email',
  95         'fields' => [
  96           'email' => [
  97             'title' => ts('Email'),
  98             'no_repeat' => TRUE,
  99           ],
 100         ],
 101         'grouping' => 'contact-fields',
 102       ],
 103       'civicrm_address' => [
 104         'dao' => 'CRM_Core_DAO_Address',
 105         'grouping' => 'contact-fields',
 106         'fields' => [
 107           'street_address' => ['default' => FALSE],
 108           'city' => ['default' => TRUE],
 109           'postal_code' => NULL,
 110           'state_province_id' => [
 111             'title' => ts('State/Province'),
 112           ],
 113           'country_id' => [
 114             'title' => ts('Country'),
 115             'default' => FALSE,
 116           ],
 117         ],
 118         /*
 119                           'filters'   => array(
 120                              'country_id' =>                                 array( 'title'   => ts( 'Country' ),
 121                                         'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 122                                         'options' => CRM_Core_PseudoConstant::country( ),
 123                                         ),
 124                                  'state_province_id' =>                                 array( 'title'   => ts( 'State/Province' ),
 125                                         'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 126                                         'options' => CRM_Core_PseudoConstant::stateProvince( ), ),
 127                                  ),
 128          */
 129       ],
 130       'civicrm_phone' => [
 131         'dao' => 'CRM_Core_DAO_Phone',
 132         'fields' => ['phone' => NULL],
 133         'grouping' => 'contact-fields',
 134       ],
 135       'civicrm_activity' => [
 136         'dao' => 'CRM_Activity_DAO_Activity',
 137         'fields' => [
 138           'id' => [
 139             'title' => ts('Activity ID'),
 140             'no_display' => TRUE,
 141             'required' => TRUE,
 142           ],
 143         ],
 144       ],
 145       'civicrm_case' => [
 146         'dao' => 'CRM_Case_DAO_Case',
 147         'fields' => [
 148           'id' => [
 149             'title' => ts('Case ID'),
 150             'required' => TRUE,
 151           ],
 152           'start_date' => [
 153             'title' => ts('Case Start'),
 154             'required' => TRUE,
 155           ],
 156           'end_date' => [
 157             'title' => ts('Case End'),
 158             'required' => TRUE,
 159           ],
 160         ],
 161         'filters' => [
 162           'case_id_filter' => [
 163             'name' => 'id',
 164             'title' => ts('Cases?'),
 165             'operatorType' => CRM_Report_Form::OP_SELECT,
 166             'options' => [
 167               1 => ts('Exclude non-case'),
 168               2 => ts('Exclude cases'),
 169               3 => ts('Include Both'),
 170             ],
 171             'default' => 3,
 172           ],
 173           'start_date' => [
 174             'title' => ts('Case Start'),
 175             'operatorType' => CRM_Report_Form::OP_DATE,
 176           ],
 177           'end_date' => [
 178             'title' => ts('Case End'),
 179             'operatorType' => CRM_Report_Form::OP_DATE,
 180           ],
 181         ],
 182         'order_bys' => [
 183           'id' => [
 184             'title' => ts('Case ID'),
 185             'default_weight' => '2',
 186             'dbAlias' => 'civicrm_case_id',
 187           ],
 188         ],
 189       ],
 190     ];
 191
 192     $this->_groupFilter = TRUE;
 193     $this->_tagFilter = TRUE;
 194
 195     $open_case_val = CRM_Core_PseudoConstant::getKey('CRM_Activity_BAO_Activity', 'activity_type_id', 'Open Case');
 196     $crmDAO = &CRM_Core_DAO::executeQuery("SELECT cg.table_name, cg.extends AS ext, cf.label, cf.column_name FROM civicrm_custom_group cg INNER JOIN civicrm_custom_field cf ON cg.id = cf.custom_group_id
 197 where (cg.extends='Contact' OR cg.extends='Individual' OR cg.extends_entity_column_value='$open_case_val') AND cg.is_active=1 AND cf.is_active=1 ORDER BY cg.table_name");
 198     $curTable = '';
 199     $curExt = '';
 200     $curFields = [];
 201     while ($crmDAO->fetch()) {
 202       if ($curTable == '') {
 203         $curTable = $crmDAO->table_name;
 204         $curExt = $crmDAO->ext;
 205       }
 206       elseif ($curTable != $crmDAO->table_name) {
 207         // dummy DAO
 208         $this->_columns[$curTable] = [
 209           'dao' => 'CRM_Contact_DAO_Contact',
 210           'fields' => $curFields,
 211           'ext' => $curExt,
 212         ];
 213         $curTable = $crmDAO->table_name;
 214         $curExt = $crmDAO->ext;
 215         $curFields = [];
 216       }
 217
 218       $curFields[$crmDAO->column_name] = ['title' => $crmDAO->label];
 219     }
 220     if (!empty($curFields)) {
 221       // dummy DAO
 222       $this->_columns[$curTable] = [
 223         'dao' => 'CRM_Contact_DAO_Contact',
 224         'fields' => $curFields,
 225         'ext' => $curExt,
 226       ];
 227     }
 228
 229     parent::__construct();
 230   }
 231
 232   public function preProcess() {
 233     parent::preProcess();
 234   }
 235
 236   public function select() {
 237     $select = [];
 238     $this->_columnHeaders = [];
 239     foreach ($this->_columns as $tableName => $table) {
 240       if (array_key_exists('fields', $table)) {
 241         foreach ($table['fields'] as $fieldName => $field) {
 242           if (!empty($field['required']) ||
 243             !empty($this->_params['fields'][$fieldName])
 244           ) {
 245             if ($tableName == 'civicrm_email') {
 246               $this->_emailField = TRUE;
 247             }
 248             elseif ($tableName == 'civicrm_phone') {
 249               $this->_phoneField = TRUE;
 250             }
 251
 252             $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 253             $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = $field['type'] ?? NULL;
 254             $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $field['title'];
 255           }
 256         }
 257       }
 258     }
 259     $this->_selectClauses = $select;
 260
 261     $this->_select = "SELECT " . implode(', ', $select) . " ";
 262   }
 263
 264   /**
 265    * @param $fields
 266    * @param $files
 267    * @param $self
 268    *
 269    * @return array
 270    */
 271   public static function formRule($fields, $files, $self) {
 272     $errors = $grouping = [];
 273     return $errors;
 274   }
 275
 276   public function from() {
 277     $this->_from = "
 278         FROM civicrm_contact {$this->_aliases['civicrm_contact']}
 279             LEFT JOIN civicrm_address {$this->_aliases['civicrm_address']}
 280                    ON ({$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_address']}.contact_id AND
 281                       {$this->_aliases['civicrm_address']}.is_primary = 1 )
 282             LEFT JOIN civicrm_case_contact ccc ON ccc.contact_id = {$this->_aliases['civicrm_contact']}.id
 283             LEFT JOIN civicrm_case {$this->_aliases['civicrm_case']} ON {$this->_aliases['civicrm_case']}.id = ccc.case_id
 284             LEFT JOIN civicrm_case_activity cca ON cca.case_id = {$this->_aliases['civicrm_case']}.id
 285             LEFT JOIN civicrm_activity {$this->_aliases['civicrm_activity']} ON {$this->_aliases['civicrm_activity']}.id = cca.activity_id
 286         ";
 287
 288     foreach ($this->_columns as $t => $c) {
 289       if (substr($t, 0, 13) == 'civicrm_value' ||
 290         substr($t, 0, 12) == 'custom_value'
 291       ) {
 292         $this->_from .= " LEFT JOIN $t {$this->_aliases[$t]} ON {$this->_aliases[$t]}.entity_id = ";
 293         $this->_from .= ($c['ext'] ==
 294           'Activity') ? "{$this->_aliases['civicrm_activity']}.id" : "{$this->_aliases['civicrm_contact']}.id";
 295       }
 296     }
 297
 298     $this->joinPhoneFromContact();
 299     $this->joinEmailFromContact();
 300
 301   }
 302
 303   public function where() {
 304     $clauses = [];
 305     $this->_having = '';
 306     foreach ($this->_columns as $tableName => $table) {
 307       if (array_key_exists('filters', $table)) {
 308         foreach ($table['filters'] as $fieldName => $field) {
 309           $clause = NULL;
 310           if ($field['operatorType'] & CRM_Report_Form::OP_DATE) {
 311             $relative = $this->_params["{$fieldName}_relative"] ?? NULL;
 312             $from = $this->_params["{$fieldName}_from"] ?? NULL;
 313             $to = $this->_params["{$fieldName}_to"] ?? NULL;
 314
 315             $clause = $this->dateClause($field['dbAlias'], $relative, $from, $to, CRM_Utils_Type::T_DATE);
 316           }
 317           else {
 318             $op = $this->_params["{$fieldName}_op"] ?? NULL;
 319             if ($op) {
 320               // handle special case
 321               if ($fieldName == 'case_id_filter') {
 322                 $choice = $this->_params["{$fieldName}_value"] ?? NULL;
 323                 if ($choice == 1) {
 324                   $clause = "({$this->_aliases['civicrm_case']}.id Is Not Null)";
 325                 }
 326                 elseif ($choice == 2) {
 327                   $clause = "({$this->_aliases['civicrm_case']}.id Is Null)";
 328                 }
 329               }
 330               else {
 331                 $clause = $this->whereClause($field,
 332                   $op,
 333                   CRM_Utils_Array::value("{$fieldName}_value", $this->_params),
 334                   CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
 335                   CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
 336                 );
 337               }
 338             }
 339           }
 340
 341           if (!empty($clause)) {
 342             $clauses[] = $clause;
 343           }
 344         }
 345       }
 346     }
 347
 348     $clauses[] = "(({$this->_aliases['civicrm_case']}.is_deleted = 0) OR ({$this->_aliases['civicrm_case']}.is_deleted Is Null))";
 349     $clauses[] = "(({$this->_aliases['civicrm_activity']}.is_deleted = 0) OR ({$this->_aliases['civicrm_activity']}.is_deleted Is Null))";
 350     $clauses[] = "(({$this->_aliases['civicrm_activity']}.is_current_revision = 1) OR ({$this->_aliases['civicrm_activity']}.is_deleted Is Null))";
 351
 352     $this->_where = "WHERE " . implode(' AND ', $clauses);
 353   }
 354
 355   public function groupBy() {
 356     $groupBy = ["{$this->_aliases['civicrm_contact']}.id", "{$this->_aliases['civicrm_case']}.id"];
 357     $this->_groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, $groupBy);
 358   }
 359
 360   public function postProcess() {
 361
 362     $this->beginPostProcess();
 363
 364     $sql = $this->buildQuery(TRUE);
 365     $rows = $graphRows = [];
 366     $this->buildRows($sql, $rows);
 367
 368     $this->formatDisplay($rows);
 369     $this->doTemplateAssignment($rows);
 370     $this->endPostProcess($rows);
 371   }
 372
 373   /**
 374    * Alter display of rows.
 375    *
 376    * Iterate through the rows retrieved via SQL and make changes for display purposes,
 377    * such as rendering contacts as links.
 378    *
 379    * @param array $rows
 380    *   Rows generated by SQL, with an array for each row.
 381    */
 382   public function alterDisplay(&$rows) {
 383     $entryFound = FALSE;
 384     foreach ($rows as $rowNum => $row) {
 385       // make count columns point to detail report
 386       // convert display name to links
 387       if (array_key_exists('civicrm_contact_sort_name', $row) &&
 388         array_key_exists('civicrm_contact_id', $row)
 389       ) {
 390         $url = CRM_Utils_System::url('civicrm/contact/view',
 391           'reset=1&cid=' . $row['civicrm_contact_id'],
 392           $this->_absoluteUrl
 393         );
 394         $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
 395         $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Contact details for this contact.");
 396         $entryFound = TRUE;
 397       }
 398
 399       // handle custom fields
 400       foreach ($row as $k => $r) {
 401         if (substr($k, 0, 13) == 'civicrm_value' ||
 402           substr($k, 0, 12) == 'custom_value'
 403         ) {
 404           if ($r || $r == '0') {
 405             if ($newval = $this->getCustomFieldLabel($k, $r)) {
 406               $rows[$rowNum][$k] = $newval;
 407             }
 408           }
 409           $entryFound = TRUE;
 410         }
 411       }
 412
 413       $entryFound = $this->alterDisplayContactFields($row, $rows, $rowNum, NULL, NULL) ? TRUE : $entryFound;
 414       $entryFound = $this->alterDisplayAddressFields($row, $rows, $rowNum, NULL, NULL) ? TRUE : $entryFound;
 415
 416       // skip looking further in rows, if first row itself doesn't
 417       // have the column we need
 418       if (!$entryFound) {
 419         break;
 420       }
 421     }
 422   }
 423
 424   /**
 425    * @param string $fname
 426    * @param $val
 427    *
 428    * @return null|string
 429    */
 430   public function getCustomFieldLabel($fname, $val) {
 431     $query = "
 432 SELECT v.label
 433   FROM civicrm_custom_group cg INNER JOIN civicrm_custom_field cf ON cg.id = cf.custom_group_id
 434   INNER JOIN civicrm_option_group g ON cf.option_group_id = g.id
 435   INNER JOIN civicrm_option_value v ON g.id = v.option_group_id
 436   WHERE CONCAT(cg.table_name, '_', cf.column_name) = %1 AND v.value = %2";
 437     $params = [
 438       1 => [$fname, 'String'],
 439       2 => [$val, 'String'],
 440     ];
 441     return CRM_Core_DAO::singleValueQuery($query, $params);
 442   }
 443
 444 }