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