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