CRM/Core/OptionGroup.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.3                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2013                                |
   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-2013
  32  * $Id$
  33  *
  34  */
  35 class CRM_Core_OptionGroup {
  36   static $_values = array();
  37   static $_cache = array();
  38
  39   /*
  40    * $_domainIDGroups array maintains the list of option groups for whom
  41    * domainID is to be considered.
  42    *
  43    */
  44   static $_domainIDGroups = array(
  45     'from_email_address',
  46     'grant_type',
  47   );
  48
  49   static function &valuesCommon(
  50     $dao, $flip = FALSE, $grouping = FALSE,
  51     $localize = FALSE, $valueColumnName = 'label'
  52   ) {
  53     self::$_values = array();
  54
  55     while ($dao->fetch()) {
  56       if ($flip) {
  57         if ($grouping) {
  58           self::$_values[$dao->value] = $dao->grouping;
  59         }
  60         else {
  61           self::$_values[$dao->{$valueColumnName}] = $dao->value;
  62         }
  63       }
  64       else {
  65         if ($grouping) {
  66           self::$_values[$dao->{$valueColumnName}] = $dao->grouping;
  67         }
  68         else {
  69           self::$_values[$dao->value] = $dao->{$valueColumnName};
  70         }
  71       }
  72     }
  73     if ($localize) {
  74       $i18n = CRM_Core_I18n::singleton();
  75       $i18n->localizeArray(self::$_values);
  76     }
  77     return self::$_values;
  78   }
  79
  80   /**
  81    * This function retrieves all the values for the specific option group by name
  82    * this is primarily used to create various html based form elements
  83    * (radio, select, checkbox etc). OptionGroups for most cases have the
  84    * 'label' in the label colum and the 'id' or 'name' in the value column
  85    *
  86    * @param $name       string  name of the option group
  87    * @param $flip       boolean results are return in id => label format if false
  88    *                            if true, the results are reversed
  89    * @param $grouping   boolean if true, return the value in 'grouping' column
  90    * @param $localize   boolean if true, localize the results before returning
  91    * @param $condition  string  add another condition to the sql query
  92    * @param $labelColumnName string the column to use for 'label'
  93    * @param $onlyActive boolean return only the action option values
  94    * @param $fresh      boolean ignore cache entries and go back to DB
  95    *
  96    * @return array      the values as specified by the above params
  97    * @static
  98    * @void
  99    */
 100   static function &values(
 101     $name, $flip = FALSE, $grouping = FALSE,
 102     $localize = FALSE, $condition = NULL,
 103     $labelColumnName = 'label', $onlyActive = TRUE, $fresh = FALSE
 104   ) {
 105     $cacheKey = self::createCacheKey($name, $flip, $grouping, $localize, $condition, $labelColumnName, $onlyActive);
 106
 107     if (array_key_exists($cacheKey, self::$_cache) && !$fresh) {
 108       return self::$_cache[$cacheKey];
 109     }
 110
 111     $cache = CRM_Utils_Cache::singleton();
 112     $var = $cache->get($cacheKey);
 113     if ($var && !$fresh) {
 114       return $var;
 115     }
 116
 117     $query = "
 118 SELECT  v.{$labelColumnName} as {$labelColumnName} ,v.value as value, v.grouping as grouping
 119 FROM   civicrm_option_value v,
 120        civicrm_option_group g
 121 WHERE  v.option_group_id = g.id
 122   AND  g.name            = %1
 123   AND  g.is_active       = 1 ";
 124
 125     if ($onlyActive) {
 126       $query .= " AND  v.is_active = 1 ";
 127     }
 128     if (in_array($name, self::$_domainIDGroups)) {
 129       $query .= " AND v.domain_id = " . CRM_Core_Config::domainID();
 130     }
 131
 132     if ($condition) {
 133       $query .= $condition;
 134     }
 135
 136     $query .= "  ORDER BY v.weight";
 137
 138     $p = array(1 => array($name, 'String'));
 139     $dao = CRM_Core_DAO::executeQuery($query, $p);
 140
 141     $var = self::valuesCommon($dao, $flip, $grouping, $localize, $labelColumnName);
 142
 143     // call option value hook
 144     CRM_Utils_Hook::optionValues($var, $name);
 145
 146     self::$_cache[$cacheKey] = $var;
 147     $cache->set($cacheKey, $var);
 148
 149     return $var;
 150   }
 151
 152   /**
 153    * Counterpart to values() which removes the item from the cache
 154    *
 155    * @param $name
 156    * @param $flip
 157    * @param $grouping
 158    * @param $localize
 159    * @param $condition
 160    * @param $labelColumnName
 161    * @param $onlyActive
 162    */
 163   protected static function flushValues($name, $flip, $grouping, $localize, $condition, $labelColumnName, $onlyActive) {
 164     $cacheKey = self::createCacheKey($name, $flip, $grouping, $localize, $condition, $labelColumnName, $onlyActive);
 165     $cache = CRM_Utils_Cache::singleton();
 166     $cache->delete($cacheKey);
 167     unset(self::$_cache[$cacheKey]);
 168   }
 169
 170   protected static function createCacheKey($name, $flip, $grouping, $localize, $condition, $labelColumnName, $onlyActive) {
 171     $cacheKey = "CRM_OG_{$name}_{$flip}_{$grouping}_{$localize}_{$condition}_{$labelColumnName}_{$onlyActive}";
 172     return $cacheKey;
 173   }
 174
 175   /**
 176    * This function retrieves all the values for the specific option group by id
 177    * this is primarily used to create various html based form elements
 178    * (radio, select, checkbox etc). OptionGroups for most cases have the
 179    * 'label' in the label colum and the 'id' or 'name' in the value column
 180    *
 181    * @param $id         integer id of the option group
 182    * @param $flip       boolean results are return in id => label format if false
 183    *                            if true, the results are reversed
 184    * @param $grouping   boolean if true, return the value in 'grouping' column
 185    * @param $localize   boolean if true, localize the results before returning
 186    * @param $labelColumnName string the column to use for 'label'
 187    *
 188    * @return array      the values as specified by the above params
 189    * @static
 190    * @void
 191    */
 192   static function &valuesByID($id, $flip = FALSE, $grouping = FALSE, $localize = FALSE, $labelColumnName = 'label') {
 193     $cacheKey = "CRM_OG_ID_{$id}_{$flip}_{$grouping}_{$localize}_{$labelColumnName}";
 194
 195     $cache = CRM_Utils_Cache::singleton();
 196     $var = $cache->get($cacheKey);
 197     if ($var) {
 198       return $var;
 199     }
 200     $query = "
 201 SELECT  v.{$labelColumnName} as {$labelColumnName} ,v.value as value, v.grouping as grouping
 202 FROM   civicrm_option_value v,
 203        civicrm_option_group g
 204 WHERE  v.option_group_id = g.id
 205   AND  g.id              = %1
 206   AND  v.is_active       = 1
 207   AND  g.is_active       = 1
 208   ORDER BY v.weight, v.label;
 209 ";
 210     $p = array(1 => array($id, 'Integer'));
 211     $dao = CRM_Core_DAO::executeQuery($query, $p);
 212
 213     $var = self::valuesCommon($dao, $flip, $grouping, $localize, $labelColumnName);
 214     $cache->set($cacheKey, $var);
 215
 216     return $var;
 217   }
 218
 219   /**
 220    * Function to lookup titles OR ids for a set of option_value populated fields. The retrieved value
 221    * is assigned a new fieldname by id or id's by title
 222    * (each within a specificied option_group)
 223    *
 224    * @param  array   $params   Reference array of values submitted by the form. Based on
 225    *                           $flip, creates new elements in $params for each field in
 226    *                           the $names array.
 227    *                           If $flip = false, adds     root field name     => title
 228    *                           If $flip = true, adds      actual field name   => id
 229    *
 230    * @param  array   $names    Reference array of fieldnames we want transformed.
 231    *                           Array key = 'postName' (field name submitted by form in $params).
 232    *                           Array value = array(
 233      'newName' => $newName, 'groupName' => $groupName).
 234    *
 235    *
 236    * @param  boolean $flip
 237    *
 238    * @return void
 239    *
 240    * @access public
 241    * @static
 242    */
 243   static function lookupValues(&$params, &$names, $flip = FALSE) {
 244     foreach ($names as $postName => $value) {
 245       // See if $params field is in $names array (i.e. is a value that we need to lookup)
 246       if ($postalName = CRM_Utils_Array::value($postName, $params)) {
 247         $postValues = array();
 248         // params[$postName] may be a Ctrl+A separated value list
 249         if (is_string($postalName) &&
 250           strpos($postalName, CRM_Core_DAO::VALUE_SEPARATOR) == FALSE
 251         ) {
 252           // eliminate the ^A frm the beginning and end if present
 253           if (substr($postalName, 0, 1) == CRM_Core_DAO::VALUE_SEPARATOR) {
 254             $params[$postName] = substr($params[$postName], 1, -1);
 255           }
 256           $postValues = explode(CRM_Core_DAO::VALUE_SEPARATOR, $params[$postName]);
 257         }
 258         elseif (is_array($postalName)) {
 259           $postValues = $postalName;
 260         }
 261         $newValue = array();
 262         foreach ($postValues as $postValue) {
 263           if (!$postValue) {
 264             continue;
 265           }
 266
 267           if ($flip) {
 268             $p        = array(1 => array($postValue, 'String'));
 269             $lookupBy = 'v.label= %1';
 270             $select   = "v.value";
 271           }
 272           else {
 273             $p        = array(1 => array($postValue, 'Integer'));
 274             $lookupBy = 'v.value = %1';
 275             $select   = "v.label";
 276           }
 277
 278           $p[2] = array($value['groupName'], 'String');
 279           $query = "
 280                         SELECT $select
 281                         FROM   civicrm_option_value v,
 282                                civicrm_option_group g
 283                         WHERE  v.option_group_id = g.id
 284                         AND    g.name            = %2
 285                         AND    $lookupBy";
 286
 287           $newValue[] = CRM_Core_DAO::singleValueQuery($query, $p);
 288           $newValue = str_replace(',', '_', $newValue);
 289         }
 290         $params[$value['newName']] = implode(', ', $newValue);
 291       }
 292     }
 293   }
 294
 295   static function getLabel($groupName, $value, $onlyActiveValue = TRUE) {
 296     if (empty($groupName) ||
 297       empty($value)
 298     ) {
 299       return NULL;
 300     }
 301
 302     $query = "
 303 SELECT  v.label as label ,v.value as value
 304 FROM   civicrm_option_value v,
 305        civicrm_option_group g
 306 WHERE  v.option_group_id = g.id
 307   AND  g.name            = %1
 308   AND  g.is_active       = 1
 309   AND  v.value           = %2
 310 ";
 311     if ($onlyActiveValue) {
 312       $query .= " AND  v.is_active = 1 ";
 313     }
 314     $p = array(1 => array($groupName, 'String'),
 315       2 => array($value, 'Integer'),
 316     );
 317     $dao = CRM_Core_DAO::executeQuery($query, $p);
 318     if ($dao->fetch()) {
 319       return $dao->label;
 320     }
 321     return NULL;
 322   }
 323
 324   static function getValue($groupName,
 325     $label,
 326     $labelField = 'label',
 327     $labelType  = 'String',
 328     $valueField = 'value'
 329   ) {
 330     if (empty($label)) {
 331       return NULL;
 332     }
 333
 334     $query = "
 335 SELECT  v.label as label ,v.{$valueField} as value
 336 FROM   civicrm_option_value v,
 337        civicrm_option_group g
 338 WHERE  v.option_group_id = g.id
 339   AND  g.name            = %1
 340   AND  v.is_active       = 1
 341   AND  g.is_active       = 1
 342   AND  v.$labelField     = %2
 343 ";
 344
 345     $p = array(1 => array($groupName, 'String'),
 346       2 => array($label, $labelType),
 347     );
 348     $dao = CRM_Core_DAO::executeQuery($query, $p);
 349     if ($dao->fetch()) {
 350       $dao->free();
 351       return $dao->value;
 352     }
 353     $dao->free();
 354     return NULL;
 355   }
 356
 357   /**
 358    * Get option_value.value from default option_value row for an option group
 359    *
 360    * @param string $groupName the name of the option group
 361    *
 362    * @access public
 363    * @static
 364    *
 365    * @return string   the value from the row where is_default = true
 366    */
 367   static function getDefaultValue($groupName) {
 368     if (empty($groupName)) {
 369       return NULL;
 370     }
 371     $query = "
 372 SELECT v.value
 373 FROM   civicrm_option_value v,
 374        civicrm_option_group g
 375 WHERE  v.option_group_id = g.id
 376   AND  g.name            = %1
 377   AND  v.is_active       = 1
 378   AND  g.is_active       = 1
 379   AND  v.is_default      = 1
 380 ";
 381     if (in_array($groupName, self::$_domainIDGroups)) {
 382       $query .= " AND v.domain_id = " . CRM_Core_Config::domainID();
 383     }
 384
 385     $p = array(1 => array($groupName, 'String'));
 386     return CRM_Core_DAO::singleValueQuery($query, $p);
 387   }
 388
 389   /**
 390    * Creates a new option group with the passed in values
 391    * @TODO: Should update the group if it already exists intelligently, so multi-lingual is
 392    * not messed up. Currently deletes the old group
 393    *
 394    * @param string $groupName the name of the option group - make sure there is no conflict
 395    * @param array  $values    the associative array that has information on the option values
 396    *                          the keys of this array are:
 397    *                          string 'title'       (required)
 398    *                          string 'value'       (required)
 399    *                          string 'name'        (optional)
 400    *                          string 'description' (optional)
 401    *                          int    'weight'      (optional) - the order in which the value are displayed
 402    *                          bool   'is_default'  (optional) - is this the default one to display when rendered in form
 403    *                          bool   'is_active'   (optional) - should this element be rendered
 404    * @param int    $defaultID (reference) - the option value ID of the default element (if set) is returned else 'null'
 405    * @param string $groupLabel            - the optional label of the option group else set to group name
 406    *
 407    * @access public
 408    * @static
 409    *
 410    * @return int   the option group ID
 411    *
 412    */
 413   static function createAssoc($groupName, &$values, &$defaultID, $groupTitle = NULL) {
 414     self::deleteAssoc($groupName);
 415     if (!empty($values)) {
 416       $group              = new CRM_Core_DAO_OptionGroup();
 417       $group->name        = $groupName;
 418       $group->title       = empty($groupTitle) ? $groupName : $groupTitle;
 419       $group->is_reserved = 1;
 420       $group->is_active   = 1;
 421       $group->save();
 422
 423       foreach ($values as $v) {
 424         $value = new CRM_Core_DAO_OptionValue();
 425         $value->option_group_id = $group->id;
 426         $value->label = $v['label'];
 427         $value->value = $v['value'];
 428         $value->name = CRM_Utils_Array::value('name', $v);
 429         $value->description = CRM_Utils_Array::value('description', $v);
 430         $value->weight = CRM_Utils_Array::value('weight', $v);
 431         $value->is_default = CRM_Utils_Array::value('is_default', $v);
 432         $value->is_active = CRM_Utils_Array::value('is_active', $v);
 433         $value->save();
 434
 435         if ($value->is_default) {
 436           $defaultID = $value->id;
 437         }
 438       }
 439     }
 440     else {
 441       return $defaultID = 'null';
 442     }
 443
 444     return $group->id;
 445   }
 446
 447   static function getAssoc($groupName, &$values, $flip = FALSE, $field = 'name') {
 448     $query = "
 449 SELECT v.id as amount_id, v.value, v.label, v.name, v.description, v.weight
 450   FROM civicrm_option_group g,
 451        civicrm_option_value v
 452  WHERE g.id = v.option_group_id
 453    AND g.$field = %1
 454 ORDER BY v.weight
 455 ";
 456     $params = array(1 => array($groupName, 'String'));
 457     $dao = CRM_Core_DAO::executeQuery($query, $params);
 458
 459     $fields = array('value', 'label', 'name', 'description', 'amount_id', 'weight');
 460     if ($flip) {
 461       $values = array();
 462     }
 463     else {
 464       foreach ($fields as $field) {
 465         $values[$field] = array();
 466       }
 467     }
 468     $index = 1;
 469
 470     while ($dao->fetch()) {
 471       if ($flip) {
 472         $value = array();
 473         foreach ($fields as $field) {
 474           $value[$field] = $dao->$field;
 475         }
 476         $values[$dao->amount_id] = $value;
 477       }
 478       else {
 479         foreach ($fields as $field) {
 480           $values[$field][$index] = $dao->$field;
 481         }
 482         $index++;
 483       }
 484     }
 485   }
 486
 487   static function deleteAssoc($groupName, $operator = "=") {
 488     $query = "
 489 DELETE g, v
 490   FROM civicrm_option_group g,
 491        civicrm_option_value v
 492  WHERE g.id = v.option_group_id
 493    AND g.name {$operator} %1";
 494
 495     $params = array(1 => array($groupName, 'String'));
 496
 497     $dao = CRM_Core_DAO::executeQuery($query, $params);
 498   }
 499
 500   static function optionLabel($groupName, $value) {
 501     $query = "
 502 SELECT v.label
 503   FROM civicrm_option_group g,
 504        civicrm_option_value v
 505  WHERE g.id = v.option_group_id
 506    AND g.name  = %1
 507    AND v.value = %2";
 508     $params = array(1 => array($groupName, 'String'),
 509       2 => array($value, 'String'),
 510     );
 511     return CRM_Core_DAO::singleValueQuery($query, $params);
 512   }
 513
 514   static function getRowValues($groupName, $fieldValue, $field = 'name',
 515     $fieldType = 'String', $active = TRUE
 516   ) {
 517     $query = "
 518 SELECT v.id, v.label, v.value, v.name, v.weight, v.description
 519 FROM   civicrm_option_value v,
 520        civicrm_option_group g
 521 WHERE  v.option_group_id = g.id
 522   AND  g.name            = %1
 523   AND  g.is_active       = 1
 524   AND  v.$field          = %2
 525 ";
 526
 527     if ($active) {
 528       $query .= " AND  v.is_active = 1";
 529     }
 530
 531     $p = array(1 => array($groupName, 'String'),
 532       2 => array($fieldValue, $fieldType),
 533     );
 534     $dao = CRM_Core_DAO::executeQuery($query, $p);
 535     $row = array();
 536
 537     if ($dao->fetch()) {
 538       foreach (array(
 539         'id', 'name', 'value', 'label', 'weight', 'description') as $fld) {
 540         $row[$fld] = $dao->$fld;
 541       }
 542     }
 543     return $row;
 544   }
 545
 546   /*
 547    * Wrapper for calling values with fresh set to true to empty the given value
 548    *
 549    * Since there appears to be some inconsistency
 550    * (@todo remove inconsistency) around the pseudoconstant operations
 551    * (for example CRM_Contribution_Pseudoconstant::paymentInstrument doesn't specify isActive
 552    * which is part of the cache key
 553    * will do a couple of variations & aspire to someone cleaning it up later
 554    */
 555   static function flush($name, $params = array()){
 556     $defaults = array(
 557       'flip' => FALSE,
 558       'grouping' => FALSE,
 559       'localize' => FALSE,
 560       'condition' => NULL,
 561       'labelColumnName' => 'label',
 562     );
 563
 564     $params = array_merge($defaults, $params);
 565     self::flushValues(
 566       $name,
 567       $params['flip'],
 568       $params['grouping'],
 569       $params['localize'],
 570       $params['condition'],
 571       $params['labelColumnName'],
 572       TRUE,
 573       TRUE
 574     );
 575     self::flushValues(
 576       $name,
 577       $params['flip'],
 578       $params['grouping'],
 579       $params['localize'],
 580       $params['condition'],
 581       $params['labelColumnName'],
 582       FALSE,
 583       TRUE
 584     );
 585   }
 586
 587   static function flushAll() {
 588     self::$_values = array();
 589     self::$_cache = array();
 590   }
 591 }
 592