CRM/Core/OptionGroup.php

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