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