CRM/Core/OptionGroup.php

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