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