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