CRM/Core/OptionGroup.php

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