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