CRM/Report/Form/Case/Demographics.php

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