CRM/Core/BAO/CustomField.php

   1 <?php
   2 /*
   3   +--------------------------------------------------------------------+
   4   | CiviCRM version 4.7                                                |
   5   +--------------------------------------------------------------------+
   6   | Copyright CiviCRM LLC (c) 2004-2017                                |
   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-2017
  32  */
  33
  34 /**
  35  * Business objects for managing custom data fields.
  36  */
  37 class CRM_Core_BAO_CustomField extends CRM_Core_DAO_CustomField {
  38
  39   /**
  40    * Array for valid combinations of data_type & descriptions.
  41    *
  42    * @var array
  43    */
  44   public static $_dataType = NULL;
  45
  46   /**
  47    * Array for valid combinations of data_type & html_type.
  48    *
  49    * @var array
  50    */
  51   public static $_dataToHtml = NULL;
  52
  53   /**
  54    * Array to hold (formatted) fields for import
  55    *
  56    * @var array
  57    */
  58   public static $_importFields = NULL;
  59
  60   /**
  61    * Build and retrieve the list of data types and descriptions.
  62    *
  63    * @return array
  64    *   Data type => Description
  65    */
  66   public static function &dataType() {
  67     if (!(self::$_dataType)) {
  68       self::$_dataType = array(
  69         'String' => ts('Alphanumeric'),
  70         'Int' => ts('Integer'),
  71         'Float' => ts('Number'),
  72         'Money' => ts('Money'),
  73         'Memo' => ts('Note'),
  74         'Date' => ts('Date'),
  75         'Boolean' => ts('Yes or No'),
  76         'StateProvince' => ts('State/Province'),
  77         'Country' => ts('Country'),
  78         'File' => ts('File'),
  79         'Link' => ts('Link'),
  80         'ContactReference' => ts('Contact Reference'),
  81       );
  82     }
  83     return self::$_dataType;
  84   }
  85
  86   /**
  87    * Get data to html array.
  88    *
  89    * (Does this caching achieve anything?)
  90    *
  91    * @return array
  92    */
  93   public static function dataToHtml() {
  94     if (!self::$_dataToHtml) {
  95       self::$_dataToHtml = array(
  96         array(
  97           'Text' => 'Text',
  98           'Select' => 'Select',
  99           'Radio' => 'Radio',
 100           'CheckBox' => 'CheckBox',
 101           'Multi-Select' => 'Multi-Select',
 102           'AdvMulti-Select' => 'AdvMulti-Select',
 103           'Autocomplete-Select' => 'Autocomplete-Select',
 104         ),
 105         array('Text' => 'Text', 'Select' => 'Select', 'Radio' => 'Radio'),
 106         array('Text' => 'Text', 'Select' => 'Select', 'Radio' => 'Radio'),
 107         array('Text' => 'Text', 'Select' => 'Select', 'Radio' => 'Radio'),
 108         array('TextArea' => 'TextArea', 'RichTextEditor' => 'RichTextEditor'),
 109         array('Date' => 'Select Date'),
 110         array('Radio' => 'Radio'),
 111         array('StateProvince' => 'Select State/Province', 'Multi-Select' => 'Multi-Select State/Province'),
 112         array('Country' => 'Select Country', 'Multi-Select' => 'Multi-Select Country'),
 113         array('File' => 'File'),
 114         array('Link' => 'Link'),
 115         array('ContactReference' => 'Autocomplete-Select'),
 116       );
 117     }
 118     return self::$_dataToHtml;
 119   }
 120
 121   /**
 122    * Takes an associative array and creates a custom field object.
 123    *
 124    * This function is invoked from within the web form layer and also from the api layer
 125    *
 126    * @param array $params
 127    *   (reference) an assoc array of name/value pairs.
 128    *
 129    * @return CRM_Core_DAO_CustomField
 130    */
 131   public static function create(&$params) {
 132     $origParams = array_merge(array(), $params);
 133
 134     $op = empty($params['id']) ? 'create' : 'edit';
 135
 136     CRM_Utils_Hook::pre($op, 'CustomField', CRM_Utils_Array::value('id', $params), $params);
 137
 138     if ($op == 'create') {
 139       if (!isset($params['column_name'])) {
 140         // if add mode & column_name not present, calculate it.
 141         $params['column_name'] = strtolower(CRM_Utils_String::munge($params['label'], '_', 32));
 142       }
 143       if (!isset($params['name'])) {
 144         $params['name'] = CRM_Utils_String::munge($params['label'], '_', 64);
 145       }
 146     }
 147     else {
 148       $params['column_name'] = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_CustomField',
 149         $params['id'],
 150         'column_name'
 151       );
 152     }
 153     $columnName = $params['column_name'];
 154
 155     $indexExist = FALSE;
 156     //as during create if field is_searchable we had created index.
 157     if (!empty($params['id'])) {
 158       $indexExist = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_CustomField', $params['id'], 'is_searchable');
 159     }
 160
 161     switch (CRM_Utils_Array::value('html_type', $params)) {
 162       case 'Select Date':
 163         if (empty($params['date_format'])) {
 164           $config = CRM_Core_Config::singleton();
 165           $params['date_format'] = $config->dateInputFormat;
 166         }
 167         break;
 168
 169       case 'CheckBox':
 170       case 'AdvMulti-Select':
 171       case 'Multi-Select':
 172         if (isset($params['default_checkbox_option'])) {
 173           $tempArray = array_keys($params['default_checkbox_option']);
 174           $defaultArray = array();
 175           foreach ($tempArray as $k => $v) {
 176             if ($params['option_value'][$v]) {
 177               $defaultArray[] = $params['option_value'][$v];
 178             }
 179           }
 180
 181           if (!empty($defaultArray)) {
 182             // also add the separator before and after the value per new convention (CRM-1604)
 183             $params['default_value'] = CRM_Core_DAO::VALUE_SEPARATOR . implode(CRM_Core_DAO::VALUE_SEPARATOR, $defaultArray) . CRM_Core_DAO::VALUE_SEPARATOR;
 184           }
 185         }
 186         else {
 187           if (!empty($params['default_option']) && isset($params['option_value'][$params['default_option']])
 188           ) {
 189             $params['default_value'] = $params['option_value'][$params['default_option']];
 190           }
 191         }
 192         break;
 193     }
 194
 195     $transaction = new CRM_Core_Transaction();
 196     // create any option group & values if required
 197     if ($params['html_type'] != 'Text' &&
 198       in_array($params['data_type'], array(
 199         'String',
 200         'Int',
 201         'Float',
 202         'Money',
 203       ))
 204     ) {
 205
 206       $tableName = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_CustomGroup',
 207         $params['custom_group_id'],
 208         'table_name'
 209       );
 210
 211       //CRM-16659: if option_value then create an option group for this custom field.
 212       if ($params['option_type'] == 1 && (empty($params['option_group_id']) || !empty($params['option_value']))) {
 213         // first create an option group for this custom group
 214         $optionGroup = new CRM_Core_DAO_OptionGroup();
 215         $optionGroup->name = "{$columnName}_" . date('YmdHis');
 216         $optionGroup->title = $params['label'];
 217         $optionGroup->is_active = 1;
 218         $optionGroup->data_type = $params['data_type'];
 219         $optionGroup->save();
 220         $params['option_group_id'] = $optionGroup->id;
 221         if (!empty($params['option_value']) && is_array($params['option_value'])) {
 222           foreach ($params['option_value'] as $k => $v) {
 223             if (strlen(trim($v))) {
 224               $optionValue = new CRM_Core_DAO_OptionValue();
 225               $optionValue->option_group_id = $optionGroup->id;
 226               $optionValue->label = $params['option_label'][$k];
 227               $optionValue->name = CRM_Utils_String::titleToVar($params['option_label'][$k]);
 228               switch ($params['data_type']) {
 229                 case 'Money':
 230                   $optionValue->value = CRM_Utils_Rule::cleanMoney($v);
 231                   break;
 232
 233                 case 'Int':
 234                   $optionValue->value = intval($v);
 235                   break;
 236
 237                 case 'Float':
 238                   $optionValue->value = floatval($v);
 239                   break;
 240
 241                 default:
 242                   $optionValue->value = trim($v);
 243               }
 244
 245               $optionValue->weight = $params['option_weight'][$k];
 246               $optionValue->is_active = CRM_Utils_Array::value($k, $params['option_status'], FALSE);
 247               $optionValue->save();
 248             }
 249           }
 250         }
 251       }
 252     }
 253
 254     // check for orphan option groups
 255     if (!empty($params['option_group_id'])) {
 256       if (!empty($params['id'])) {
 257         self::fixOptionGroups($params['id'], $params['option_group_id']);
 258       }
 259
 260       // if we do not have a default value
 261       // retrieve it from one of the other custom fields which use this option group
 262       if (empty($params['default_value'])) {
 263         //don't insert only value separator as default value, CRM-4579
 264         $defaultValue = self::getOptionGroupDefault($params['option_group_id'],
 265           $params['html_type']
 266         );
 267
 268         if (!CRM_Utils_System::isNull(explode(CRM_Core_DAO::VALUE_SEPARATOR,
 269           $defaultValue
 270         ))
 271         ) {
 272           $params['default_value'] = $defaultValue;
 273         }
 274       }
 275     }
 276
 277     // since we need to save option group id :)
 278     if (!isset($params['attributes']) && strtolower($params['html_type']) == 'textarea') {
 279       $params['attributes'] = 'rows=4, cols=60';
 280     }
 281
 282     $customField = new CRM_Core_DAO_CustomField();
 283     $customField->copyValues($params);
 284     $customField->is_required = CRM_Utils_Array::value('is_required', $params, FALSE);
 285     $customField->is_searchable = CRM_Utils_Array::value('is_searchable', $params, FALSE);
 286     $customField->in_selector = CRM_Utils_Array::value('in_selector', $params, FALSE);
 287     $customField->is_search_range = CRM_Utils_Array::value('is_search_range', $params, FALSE);
 288     //CRM-15792 - Custom field gets disabled if is_active not set
 289     $customField->is_active = CRM_Utils_Array::value('is_active', $params, TRUE);
 290     $customField->is_view = CRM_Utils_Array::value('is_view', $params, FALSE);
 291     $customField->save();
 292
 293     // make sure all values are present in the object for further processing
 294     $customField->find(TRUE);
 295
 296     $triggerRebuild = CRM_Utils_Array::value('triggerRebuild', $params, TRUE);
 297     //create/drop the index when we toggle the is_searchable flag
 298     if ($op == 'edit') {
 299       self::createField($customField, 'modify', $indexExist, $triggerRebuild);
 300     }
 301     else {
 302       if (!isset($origParams['column_name'])) {
 303         $columnName .= "_{$customField->id}";
 304         $params['column_name'] = $columnName;
 305       }
 306       $customField->column_name = $columnName;
 307       $customField->save();
 308       // make sure all values are present in the object
 309       $customField->find(TRUE);
 310
 311       $indexExist = FALSE;
 312       self::createField($customField, 'add', $indexExist, $triggerRebuild);
 313     }
 314
 315     // complete transaction
 316     $transaction->commit();
 317
 318     CRM_Utils_Hook::post($op, 'CustomField', $customField->id, $customField);
 319
 320     CRM_Utils_System::flushCache();
 321
 322     return $customField;
 323   }
 324
 325   /**
 326    * Fetch object based on array of properties.
 327    *
 328    * @param array $params
 329    *   (reference ) an assoc array of name/value pairs.
 330    * @param array $defaults
 331    *   (reference ) an assoc array to hold the flattened values.
 332    *
 333    * @return CRM_Core_DAO_CustomField
 334    */
 335   public static function retrieve(&$params, &$defaults) {
 336     return CRM_Core_DAO::commonRetrieve('CRM_Core_DAO_CustomField', $params, $defaults);
 337   }
 338
 339   /**
 340    * Update the is_active flag in the db.
 341    *
 342    * @param int $id
 343    *   Id of the database record.
 344    * @param bool $is_active
 345    *   Value we want to set the is_active field.
 346    *
 347    * @return Object
 348    *   DAO object on success, null otherwise
 349    */
 350   public static function setIsActive($id, $is_active) {
 351
 352     CRM_Utils_System::flushCache();
 353
 354     //enable-disable CustomField
 355     CRM_Core_BAO_UFField::setUFField($id, $is_active);
 356     return CRM_Core_DAO::setFieldValue('CRM_Core_DAO_CustomField', $id, 'is_active', $is_active);
 357   }
 358
 359   /**
 360    * Get the field title.
 361    *
 362    * @param int $id
 363    *   Id of field.
 364    *
 365    * @return string
 366    *   name
 367    */
 368   public static function getTitle($id) {
 369     return CRM_Core_DAO::getFieldValue('CRM_Core_DAO_CustomField', $id, 'label');
 370   }
 371
 372   /**
 373    * @param string $context
 374    * @return array|bool
 375    */
 376   public function getOptions($context = NULL) {
 377     CRM_Core_DAO::buildOptionsContext($context);
 378
 379     if (!$this->id) {
 380       return FALSE;
 381     }
 382     if (!$this->data_type || !$this->custom_group_id) {
 383       $this->find(TRUE);
 384     }
 385
 386     if (!empty($this->option_group_id)) {
 387       $options = CRM_Core_OptionGroup::valuesByID(
 388         $this->option_group_id,
 389         FALSE,
 390         FALSE,
 391         FALSE,
 392         'label',
 393         !($context == 'validate' || $context == 'get')
 394       );
 395     }
 396     elseif ($this->data_type === 'StateProvince') {
 397       $options = CRM_Core_Pseudoconstant::stateProvince();
 398     }
 399     elseif ($this->data_type === 'Country') {
 400       $options = $context == 'validate' ? CRM_Core_Pseudoconstant::countryIsoCode() : CRM_Core_Pseudoconstant::country();
 401     }
 402     elseif ($this->data_type === 'Boolean') {
 403       $options = $context == 'validate' ? array(0, 1) : CRM_Core_SelectValues::boolean();
 404     }
 405     else {
 406       return FALSE;
 407     }
 408     CRM_Utils_Hook::customFieldOptions($this->id, $options, FALSE);
 409     CRM_Utils_Hook::fieldOptions($this->getEntity(), "custom_{$this->id}", $options, array('context' => $context));
 410     return $options;
 411   }
 412
 413   /**
 414    * Store and return an array of all active custom fields.
 415    *
 416    * @param string $customDataType
 417    *   Type of Custom Data; empty is a synonym for "all contact data types".
 418    * @param bool $showAll
 419    *   If true returns all fields (includes disabled fields).
 420    * @param bool $inline
 421    *   If true returns all inline fields (includes disabled fields).
 422    * @param int $customDataSubType
 423    *   Custom Data sub type value.
 424    * @param int $customDataSubName
 425    *   Custom Data sub name value.
 426    * @param bool $onlyParent
 427    *   Return only top level custom data, for eg, only Participant and ignore subname and subtype.
 428    * @param bool $onlySubType
 429    *   Return only custom data for subtype.
 430    * @param bool $checkPermission
 431    *   If false, do not include permissioning clause.
 432    *
 433    * @return array
 434    *   an array of active custom fields.
 435    */
 436   public static function &getFields(
 437     $customDataType = 'Individual',
 438     $showAll = FALSE,
 439     $inline = FALSE,
 440     $customDataSubType = NULL,
 441     $customDataSubName = NULL,
 442     $onlyParent = FALSE,
 443     $onlySubType = FALSE,
 444     $checkPermission = TRUE
 445   ) {
 446     if (empty($customDataType)) {
 447       $customDataType = array('Contact', 'Individual', 'Organization', 'Household');
 448     }
 449     if ($customDataType && !is_array($customDataType)) {
 450
 451       if (in_array($customDataType, CRM_Contact_BAO_ContactType::subTypes())) {
 452         // This is the case when getFieldsForImport() requires fields
 453         // limited strictly to a subtype.
 454         $customDataSubType = $customDataType;
 455         $customDataType = CRM_Contact_BAO_ContactType::getBasicType($customDataType);
 456         $onlySubType = TRUE;
 457       }
 458
 459       if (in_array($customDataType, array_keys(CRM_Core_SelectValues::customGroupExtends()))) {
 460         // this makes the method flexible to support retrieving fields
 461         // for multiple extends value.
 462         $customDataType = array($customDataType);
 463       }
 464     }
 465
 466     $customDataSubType = CRM_Utils_Array::explodePadded($customDataSubType);
 467
 468     if (is_array($customDataType)) {
 469       $cacheKey = implode('_', $customDataType);
 470     }
 471     else {
 472       $cacheKey = $customDataType;
 473     }
 474
 475     $cacheKey .= !empty($customDataSubType) ? ('_' . implode('_', $customDataSubType)) : '_0';
 476     $cacheKey .= $customDataSubName ? "{$customDataSubName}_" : '_0';
 477     $cacheKey .= $showAll ? '_1' : '_0';
 478     $cacheKey .= $inline ? '_1_' : '_0_';
 479     $cacheKey .= $onlyParent ? '_1_' : '_0_';
 480     $cacheKey .= $onlySubType ? '_1_' : '_0_';
 481     $cacheKey .= $checkPermission ? '_1_' : '_0_';
 482
 483     $cgTable = CRM_Core_DAO_CustomGroup::getTableName();
 484
 485     // also get the permission stuff here
 486     if ($checkPermission) {
 487       $permissionClause = CRM_Core_Permission::customGroupClause(CRM_Core_Permission::VIEW,
 488         "{$cgTable}."
 489       );
 490     }
 491     else {
 492       $permissionClause = '(1)';
 493     }
 494
 495     // lets md5 permission clause and take first 8 characters
 496     $cacheKey .= substr(md5($permissionClause), 0, 8);
 497
 498     if (strlen($cacheKey) > 40) {
 499       $cacheKey = md5($cacheKey);
 500     }
 501
 502     if (!self::$_importFields ||
 503       CRM_Utils_Array::value($cacheKey, self::$_importFields) === NULL
 504     ) {
 505       if (!self::$_importFields) {
 506         self::$_importFields = array();
 507       }
 508
 509       // check if we can retrieve from database cache
 510       $fields = CRM_Core_BAO_Cache::getItem('contact fields', "custom importableFields $cacheKey");
 511
 512       if ($fields === NULL) {
 513         $cfTable = self::getTableName();
 514
 515         $extends = '';
 516         if (is_array($customDataType)) {
 517           $value = NULL;
 518           foreach ($customDataType as $dataType) {
 519             if (in_array($dataType, array_keys(CRM_Core_SelectValues::customGroupExtends()))) {
 520               if (in_array($dataType, array('Individual', 'Household', 'Organization'))) {
 521                 $val = "'" . CRM_Utils_Type::escape($dataType, 'String') . "', 'Contact' ";
 522               }
 523               else {
 524                 $val = "'" . CRM_Utils_Type::escape($dataType, 'String') . "'";
 525               }
 526               $value = $value ? $value . ", {$val}" : $val;
 527             }
 528           }
 529           if ($value) {
 530             $extends = "AND   $cgTable.extends IN ( $value ) ";
 531           }
 532         }
 533
 534         if (!empty($customDataType) && empty($extends)) {
 535           // $customDataType specified a filter, but there is no corresponding SQL ($extends)
 536           self::$_importFields[$cacheKey] = array();
 537           return self::$_importFields[$cacheKey];
 538         }
 539
 540         if ($onlyParent) {
 541           $extends .= " AND $cgTable.extends_entity_column_value IS NULL AND $cgTable.extends_entity_column_id IS NULL ";
 542         }
 543
 544         $query = "SELECT $cfTable.id, $cfTable.label,
 545                             $cgTable.title,
 546                             $cfTable.data_type,
 547                             $cfTable.html_type,
 548                             $cfTable.default_value,
 549                             $cfTable.options_per_line, $cfTable.text_length,
 550                             $cfTable.custom_group_id,
 551                             $cfTable.is_required,
 552                             $cfTable.column_name,
 553                             $cgTable.extends, $cfTable.is_search_range,
 554                             $cgTable.extends_entity_column_value,
 555                             $cgTable.extends_entity_column_id,
 556                             $cfTable.is_view,
 557                             $cfTable.option_group_id,
 558                             $cfTable.date_format,
 559                             $cfTable.time_format,
 560                             $cgTable.is_multiple,
 561                             $cgTable.table_name,
 562                             og.name as option_group_name
 563                      FROM $cfTable
 564                      INNER JOIN $cgTable
 565                        ON $cfTable.custom_group_id = $cgTable.id
 566                      LEFT JOIN civicrm_option_group og
 567                        ON $cfTable.option_group_id = og.id
 568                      WHERE ( 1 ) ";
 569
 570         if (!$showAll) {
 571           $query .= " AND $cfTable.is_active = 1 AND $cgTable.is_active = 1 ";
 572         }
 573
 574         if ($inline) {
 575           $query .= " AND $cgTable.style = 'Inline' ";
 576         }
 577
 578         //get the custom fields for specific type in
 579         //combination with fields those support any type.
 580         if (!empty($customDataSubType)) {
 581           $subtypeClause = array();
 582           foreach ($customDataSubType as $subtype) {
 583             $subtype = CRM_Core_DAO::VALUE_SEPARATOR . $subtype . CRM_Core_DAO::VALUE_SEPARATOR;
 584             $subtypeClause[] = "$cgTable.extends_entity_column_value LIKE '%{$subtype}%'";
 585           }
 586           if (!$onlySubType) {
 587             $subtypeClause[] = "$cgTable.extends_entity_column_value IS NULL";
 588           }
 589           $query .= " AND ( " . implode(' OR ', $subtypeClause) . " )";
 590         }
 591
 592         if ($customDataSubName) {
 593           $query .= " AND ( $cgTable.extends_entity_column_id = $customDataSubName ) ";
 594         }
 595
 596         // also get the permission stuff here
 597         if ($checkPermission) {
 598           $permissionClause = CRM_Core_Permission::customGroupClause(CRM_Core_Permission::VIEW,
 599             "{$cgTable}.", TRUE
 600           );
 601         }
 602         else {
 603           $permissionClause = '(1)';
 604         }
 605
 606         $query .= " $extends AND $permissionClause
 607                         ORDER BY $cgTable.weight, $cgTable.title,
 608                                  $cfTable.weight, $cfTable.label";
 609
 610         $dao = CRM_Core_DAO::executeQuery($query);
 611
 612         $fields = array();
 613         while (($dao->fetch()) != NULL) {
 614           $fields[$dao->id]['label'] = $dao->label;
 615           $fields[$dao->id]['groupTitle'] = $dao->title;
 616           $fields[$dao->id]['data_type'] = $dao->data_type;
 617           $fields[$dao->id]['html_type'] = $dao->html_type;
 618           $fields[$dao->id]['default_value'] = $dao->default_value;
 619           $fields[$dao->id]['text_length'] = $dao->text_length;
 620           $fields[$dao->id]['options_per_line'] = $dao->options_per_line;
 621           $fields[$dao->id]['custom_group_id'] = $dao->custom_group_id;
 622           $fields[$dao->id]['extends'] = $dao->extends;
 623           $fields[$dao->id]['is_search_range'] = $dao->is_search_range;
 624           $fields[$dao->id]['extends_entity_column_value'] = $dao->extends_entity_column_value;
 625           $fields[$dao->id]['extends_entity_column_id'] = $dao->extends_entity_column_id;
 626           $fields[$dao->id]['is_view'] = $dao->is_view;
 627           $fields[$dao->id]['is_multiple'] = $dao->is_multiple;
 628           $fields[$dao->id]['option_group_id'] = $dao->option_group_id;
 629           $fields[$dao->id]['date_format'] = $dao->date_format;
 630           $fields[$dao->id]['time_format'] = $dao->time_format;
 631           $fields[$dao->id]['is_required'] = $dao->is_required;
 632           $fields[$dao->id]['table_name'] = $dao->table_name;
 633           $fields[$dao->id]['column_name'] = $dao->column_name;
 634           self::getOptionsForField($fields[$dao->id], $dao->option_group_name);
 635         }
 636
 637         CRM_Core_BAO_Cache::setItem($fields,
 638           'contact fields',
 639           "custom importableFields $cacheKey"
 640         );
 641       }
 642       self::$_importFields[$cacheKey] = $fields;
 643     }
 644
 645     return self::$_importFields[$cacheKey];
 646   }
 647
 648   /**
 649    * Return the field ids and names (with groups) for import purpose.
 650    *
 651    * @param int|string $contactType Contact type
 652    * @param bool $showAll
 653    *   If true returns all fields (includes disabled fields).
 654    * @param bool $onlyParent
 655    *   Return fields ONLY related to basic types.
 656    * @param bool $search
 657    *   When called from search and multiple records need to be returned.
 658    * @param bool $checkPermission
 659    *   If false, do not include permissioning clause.
 660    *
 661    * @param bool $withMultiple
 662    *
 663    * @return array
 664    */
 665   public static function getFieldsForImport(
 666     $contactType = 'Individual',
 667     $showAll = FALSE,
 668     $onlyParent = FALSE,
 669     $search = FALSE,
 670     $checkPermission = TRUE,
 671     $withMultiple = FALSE
 672   ) {
 673     // Note: there are situations when we want getFieldsForImport() return fields related
 674     // ONLY to basic contact types, but NOT subtypes. And thats where $onlyParent is helpful
 675     $fields = &self::getFields($contactType,
 676       $showAll,
 677       FALSE,
 678       NULL,
 679       NULL,
 680       $onlyParent,
 681       FALSE,
 682       $checkPermission
 683     );
 684
 685     $importableFields = array();
 686     foreach ($fields as $id => $values) {
 687       // for now we should not allow multiple fields in profile / export etc, hence unsetting
 688       if (!$search &&
 689         (!empty($values['is_multiple']) && !$withMultiple)
 690       ) {
 691         continue;
 692       }
 693
 694       /* generate the key for the fields array */
 695
 696       $key = "custom_$id";
 697
 698       $regexp = preg_replace('/[.,;:!?]/', '', CRM_Utils_Array::value(0, $values));
 699       $importableFields[$key] = array(
 700         'name' => $key,
 701         'title' => CRM_Utils_Array::value('label', $values),
 702         'headerPattern' => '/' . preg_quote($regexp, '/') . '/',
 703         'import' => 1,
 704         'custom_field_id' => $id,
 705         'options_per_line' => CRM_Utils_Array::value('options_per_line', $values),
 706         'text_length' => CRM_Utils_Array::value('text_length', $values, 255),
 707         'data_type' => CRM_Utils_Array::value('data_type', $values),
 708         'html_type' => CRM_Utils_Array::value('html_type', $values),
 709         'is_search_range' => CRM_Utils_Array::value('is_search_range', $values),
 710       );
 711
 712       // CRM-6681, pass date and time format when html_type = Select Date
 713       if (CRM_Utils_Array::value('html_type', $values) == 'Select Date') {
 714         $importableFields[$key]['date_format'] = CRM_Utils_Array::value('date_format', $values);
 715         $importableFields[$key]['time_format'] = CRM_Utils_Array::value('time_format', $values);
 716       }
 717     }
 718
 719     return $importableFields;
 720   }
 721
 722   /**
 723    * Get the field id from an import key.
 724    *
 725    * @param string $key
 726    *   The key to parse.
 727    *
 728    * @param bool $all
 729    *
 730    * @return int|null
 731    *   The id (if exists)
 732    */
 733   public static function getKeyID($key, $all = FALSE) {
 734     $match = array();
 735     if (preg_match('/^custom_(\d+)_?(-?\d+)?$/', $key, $match)) {
 736       if (!$all) {
 737         return $match[1];
 738       }
 739       else {
 740         return array(
 741           $match[1],
 742           CRM_Utils_Array::value(2, $match),
 743         );
 744       }
 745     }
 746     return $all ? array(NULL, NULL) : NULL;
 747   }
 748
 749   /**
 750    * Use the cache to get all values of a specific custom field.
 751    *
 752    * @param int $fieldID
 753    *   The custom field ID.
 754    *
 755    * @return CRM_Core_BAO_CustomField
 756    *   The field object.
 757    */
 758   public static function getFieldObject($fieldID) {
 759     $field = new CRM_Core_BAO_CustomField();
 760
 761     // check if we can get the field values from the system cache
 762     $cacheKey = "CRM_Core_DAO_CustomField_{$fieldID}";
 763     $cache = CRM_Utils_Cache::singleton();
 764     $fieldValues = $cache->get($cacheKey);
 765     if (empty($fieldValues)) {
 766       $field->id = $fieldID;
 767       if (!$field->find(TRUE)) {
 768         CRM_Core_Error::fatal();
 769       }
 770
 771       $fieldValues = array();
 772       CRM_Core_DAO::storeValues($field, $fieldValues);
 773
 774       $cache->set($cacheKey, $fieldValues);
 775     }
 776     else {
 777       $field->copyValues($fieldValues);
 778     }
 779
 780     return $field;
 781   }
 782
 783   /**
 784    * Add a custom field to an existing form.
 785    *
 786    * @param CRM_Core_Form $qf
 787    *   Form object (reference).
 788    * @param string $elementName
 789    *   Name of the custom field.
 790    * @param int $fieldId
 791    * @param bool $useRequired
 792    *   True if required else false.
 793    * @param bool $search
 794    *   True if used for search else false.
 795    * @param string $label
 796    *   Label for custom field.
 797    * @return \HTML_QuickForm_Element|null
 798    * @throws \CiviCRM_API3_Exception
 799    */
 800   public static function addQuickFormElement(
 801     $qf, $elementName, $fieldId, $useRequired = TRUE, $search = FALSE, $label = NULL
 802   ) {
 803     $field = self::getFieldObject($fieldId);
 804     $widget = $field->html_type;
 805     $element = NULL;
 806
 807     // Custom field HTML should indicate group+field name
 808     $groupName = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_CustomGroup', $field->custom_group_id);
 809     $dataCrmCustomVal = $groupName . ':' . $field->name;
 810     $dataCrmCustomAttr = 'data-crm-custom="' . $dataCrmCustomVal . '"';
 811     $field->attributes .= $dataCrmCustomAttr;
 812
 813     // Fixed for Issue CRM-2183
 814     if ($widget == 'TextArea' && $search) {
 815       $widget = 'Text';
 816     }
 817
 818     $placeholder = $search ? ts('- any -') : ($useRequired ? ts('- select -') : ts('- none -'));
 819
 820     // FIXME: Why are select state/country separate widget types?
 821     $isSelect = (in_array($widget, array(
 822       'Select',
 823       'Multi-Select',
 824       'Select State/Province',
 825       'Multi-Select State/Province',
 826       'Select Country',
 827       'Multi-Select Country',
 828       'AdvMulti-Select',
 829       'CheckBox',
 830       'Radio',
 831     )));
 832
 833     if ($isSelect) {
 834       $options = $field->getOptions($search ? 'search' : 'create');
 835
 836       // Consolidate widget types to simplify the below switch statement
 837       if ($search || ($widget !== 'AdvMulti-Select' && strpos($widget, 'Select') !== FALSE)) {
 838         $widget = 'Select';
 839       }
 840       $selectAttributes = array(
 841         'data-crm-custom' => $dataCrmCustomVal,
 842         'class' => 'crm-select2',
 843       );
 844       // Search field is always multi-select
 845       if ($search || strpos($field->html_type, 'Multi') !== FALSE) {
 846         $selectAttributes['class'] .= ' huge';
 847         $selectAttributes['multiple'] = 'multiple';
 848         $selectAttributes['placeholder'] = $placeholder;
 849       }
 850       // Add data for popup link. Normally this is handled by CRM_Core_Form->addSelect
 851       if ($field->option_group_id && !$search && $widget == 'Select' && CRM_Core_Permission::check('administer CiviCRM')) {
 852         $selectAttributes += array(
 853           'data-api-entity' => $field->getEntity(),
 854           'data-api-field' => 'custom_' . $field->id,
 855           'data-option-edit-path' => 'civicrm/admin/options/' . CRM_Core_DAO::getFieldValue('CRM_Core_DAO_OptionGroup', $field->option_group_id),
 856         );
 857       }
 858     }
 859
 860     if (!isset($label)) {
 861       $label = $field->label;
 862     }
 863
 864     // at some point in time we might want to split the below into small functions
 865
 866     switch ($widget) {
 867       case 'Text':
 868       case 'Link':
 869         if ($field->is_search_range && $search) {
 870           $qf->add('text', $elementName . '_from', $label . ' ' . ts('From'), $field->attributes);
 871           $qf->add('text', $elementName . '_to', ts('To'), $field->attributes);
 872         }
 873         else {
 874           if ($field->text_length) {
 875             $field->attributes .= ' maxlength=' . $field->text_length;
 876             if ($field->text_length < 20) {
 877               $field->attributes .= ' size=' . $field->text_length;
 878             }
 879           }
 880           $element = $qf->add('text', $elementName, $label,
 881             $field->attributes,
 882             $useRequired && !$search
 883           );
 884         }
 885         break;
 886
 887       case 'TextArea':
 888         $attributes = $dataCrmCustomAttr;
 889         if ($field->note_rows) {
 890           $attributes .= 'rows=' . $field->note_rows;
 891         }
 892         else {
 893           $attributes .= 'rows=4';
 894         }
 895         if ($field->note_columns) {
 896           $attributes .= ' cols=' . $field->note_columns;
 897         }
 898         else {
 899           $attributes .= ' cols=60';
 900         }
 901         if ($field->text_length) {
 902           $attributes .= ' maxlength=' . $field->text_length;
 903         }
 904         $element = $qf->add('textarea',
 905           $elementName,
 906           $label,
 907           $attributes,
 908           $useRequired && !$search
 909         );
 910         break;
 911
 912       case 'Select Date':
 913         $attr = array('data-crm-custom' => $dataCrmCustomVal);
 914         //CRM-18379: Fix for date range of 'Select Date' custom field when include in profile.
 915         $minYear = isset($field->start_date_years) ? (date('Y') - $field->start_date_years) : NULL;
 916         $maxYear = isset($field->end_date_years) ? (date('Y') + $field->end_date_years) : NULL;
 917
 918         $params = array(
 919           'date' => $field->date_format,
 920           'minDate' => isset($minYear) ? $minYear . '-01-01' : NULL,
 921           //CRM-18487 - max date should be the last date of the year.
 922           'maxDate' => isset($maxYear) ? $maxYear . '-12-31' : NULL,
 923           'time' => $field->time_format ? $field->time_format * 12 : FALSE,
 924         );
 925         if ($field->is_search_range && $search) {
 926           $qf->add('datepicker', $elementName . '_from', $label, $attr + array('placeholder' => ts('From')), FALSE, $params);
 927           $qf->add('datepicker', $elementName . '_to', NULL, $attr + array('placeholder' => ts('To')), FALSE, $params);
 928         }
 929         else {
 930           $element = $qf->add('datepicker', $elementName, $label, $attr, $useRequired && !$search, $params);
 931         }
 932         break;
 933
 934       case 'Radio':
 935         $choice = array();
 936         foreach ($options as $v => $l) {
 937           $choice[] = $qf->createElement('radio', NULL, '', $l, (string) $v, $field->attributes);
 938         }
 939         $element = $qf->addGroup($choice, $elementName, $label);
 940         if ($useRequired && !$search) {
 941           $qf->addRule($elementName, ts('%1 is a required field.', array(1 => $label)), 'required');
 942         }
 943         else {
 944           $element->setAttribute('allowClear', TRUE);
 945         }
 946         break;
 947
 948       // For all select elements
 949       case 'Select':
 950         if (empty($selectAttributes['multiple'])) {
 951           $options = array('' => $placeholder) + $options;
 952         }
 953         $element = $qf->add('select', $elementName, $label, $options, $useRequired && !$search, $selectAttributes);
 954
 955         // Add and/or option for fields that store multiple values
 956         if ($search && self::isSerialized($field)) {
 957
 958           $operators = array(
 959             $qf->createElement('radio', NULL, '', ts('Any'), 'or', array('title' => ts('Results may contain any of the selected options'))),
 960             $qf->createElement('radio', NULL, '', ts('All'), 'and', array('title' => ts('Results must have all of the selected options'))),
 961           );
 962           $qf->addGroup($operators, $elementName . '_operator');
 963           $qf->setDefaults(array($elementName . '_operator' => 'or'));
 964         }
 965         break;
 966
 967       case 'AdvMulti-Select':
 968         $element = $qf->addElement(
 969           'advmultiselect',
 970           $elementName,
 971           $label, $options,
 972           array(
 973             'size' => 5,
 974             'style' => '',
 975             'class' => 'advmultiselect',
 976             'data-crm-custom' => $dataCrmCustomVal,
 977           )
 978         );
 979
 980         $element->setButtonAttributes('add', array('value' => ts('Add >>')));
 981         $element->setButtonAttributes('remove', array('value' => ts('<< Remove')));
 982
 983         if ($useRequired && !$search) {
 984           $qf->addRule($elementName, ts('%1 is a required field.', array(1 => $label)), 'required');
 985         }
 986         break;
 987
 988       case 'CheckBox':
 989         $check = array();
 990         foreach ($options as $v => $l) {
 991           $check[] = &$qf->addElement('advcheckbox', $v, NULL, $l, array('data-crm-custom' => $dataCrmCustomVal));
 992         }
 993         $element = $qf->addGroup($check, $elementName, $label);
 994         if ($useRequired && !$search) {
 995           $qf->addRule($elementName, ts('%1 is a required field.', array(1 => $label)), 'required');
 996         }
 997         break;
 998
 999       case 'File':
1000         // we should not build upload file in search mode
1001         if ($search) {
1002           return NULL;
1003         }
1004         $element = $qf->add(
1005           strtolower($field->html_type),
1006           $elementName,
1007           $label,
1008           $field->attributes,
1009           $useRequired && !$search
1010         );
1011         $qf->addUploadElement($elementName);
1012         break;
1013
1014       case 'RichTextEditor':
1015         $attributes = array(
1016           'rows' => $field->note_rows,
1017           'cols' => $field->note_columns,
1018           'data-crm-custom' => $dataCrmCustomVal,
1019         );
1020         if ($field->text_length) {
1021           $attributes['maxlength'] = $field->text_length;
1022         }
1023         $element = $qf->add('wysiwyg', $elementName, $label, $attributes, $useRequired && !$search);
1024         break;
1025
1026       case 'Autocomplete-Select':
1027         static $customUrls = array();
1028         // Fixme: why is this a string in the first place??
1029         $attributes = array();
1030         if ($field->attributes) {
1031           foreach (explode(' ', $field->attributes) as $at) {
1032             if (strpos($at, '=')) {
1033               list($k, $v) = explode('=', $at);
1034               $attributes[$k] = trim($v, ' "');
1035             }
1036           }
1037         }
1038         if ($field->data_type == 'ContactReference') {
1039           // break if contact does not have permission to access ContactReference
1040           if (!CRM_Core_Permission::check('access contact reference fields')) {
1041             break;
1042           }
1043           $attributes['class'] = (isset($attributes['class']) ? $attributes['class'] . ' ' : '') . 'crm-form-contact-reference huge';
1044           $attributes['data-api-entity'] = 'Contact';
1045           $element = $qf->add('text', $elementName, $label, $attributes, $useRequired && !$search);
1046
1047           $urlParams = "context=customfield&id={$field->id}";
1048
1049           $customUrls[$elementName] = CRM_Utils_System::url('civicrm/ajax/contactref',
1050             $urlParams,
1051             FALSE, NULL, FALSE
1052           );
1053
1054         }
1055         else {
1056           // FIXME: This won't work with customFieldOptions hook
1057           $attributes += array(
1058             'entity' => 'option_value',
1059             'placeholder' => $placeholder,
1060             'multiple' => $search,
1061             'api' => array(
1062               'params' => array('option_group_id' => $field->option_group_id),
1063             ),
1064           );
1065           $element = $qf->addEntityRef($elementName, $label, $attributes, $useRequired && !$search);
1066         }
1067
1068         $qf->assign('customUrls', $customUrls);
1069         break;
1070     }
1071
1072     switch ($field->data_type) {
1073       case 'Int':
1074         // integers will have numeric rule applied to them.
1075         if ($field->is_search_range && $search) {
1076           $qf->addRule($elementName . '_from', ts('%1 From must be an integer (whole number).', array(1 => $label)), 'integer');
1077           $qf->addRule($elementName . '_to', ts('%1 To must be an integer (whole number).', array(1 => $label)), 'integer');
1078         }
1079         elseif ($widget == 'Text') {
1080           $qf->addRule($elementName, ts('%1 must be an integer (whole number).', array(1 => $label)), 'integer');
1081         }
1082         break;
1083
1084       case 'Float':
1085         if ($field->is_search_range && $search) {
1086           $qf->addRule($elementName . '_from', ts('%1 From must be a number (with or without decimal point).', array(1 => $label)), 'numeric');
1087           $qf->addRule($elementName . '_to', ts('%1 To must be a number (with or without decimal point).', array(1 => $label)), 'numeric');
1088         }
1089         elseif ($widget == 'Text') {
1090           $qf->addRule($elementName, ts('%1 must be a number (with or without decimal point).', array(1 => $label)), 'numeric');
1091         }
1092         break;
1093
1094       case 'Money':
1095         if ($field->is_search_range && $search) {
1096           $qf->addRule($elementName . '_from', ts('%1 From must in proper money format. (decimal point/comma/space is allowed).', array(1 => $label)), 'money');
1097           $qf->addRule($elementName . '_to', ts('%1 To must in proper money format. (decimal point/comma/space is allowed).', array(1 => $label)), 'money');
1098         }
1099         elseif ($widget == 'Text') {
1100           $qf->addRule($elementName, ts('%1 must be in proper money format. (decimal point/comma/space is allowed).', array(1 => $label)), 'money');
1101         }
1102         break;
1103
1104       case 'Link':
1105         $element->setAttribute('class', "url");
1106         $qf->addRule($elementName, ts('Enter a valid web address beginning with \'http://\' or \'https://\'.'), 'wikiURL');
1107         break;
1108     }
1109     if ($field->is_view && !$search) {
1110       $qf->freeze($elementName);
1111     }
1112     return $element;
1113   }
1114
1115   /**
1116    * Delete the Custom Field.
1117    *
1118    * @param object $field
1119    *   The field object.
1120    */
1121   public static function deleteField($field) {
1122     CRM_Utils_System::flushCache();
1123
1124     // first delete the custom option group and values associated with this field
1125     if ($field->option_group_id) {
1126       //check if option group is related to any other field, if
1127       //not delete the option group and related option values
1128       self::checkOptionGroup($field->option_group_id);
1129     }
1130
1131     // next drop the column from the custom value table
1132     self::createField($field, 'delete');
1133
1134     $field->delete();
1135     CRM_Core_BAO_UFField::delUFField($field->id);
1136     CRM_Utils_Weight::correctDuplicateWeights('CRM_Core_DAO_CustomField');
1137
1138     CRM_Utils_Hook::post('delete', 'CustomField', $field->id, $field);
1139   }
1140
1141   /**
1142    * @param string|int|array|null $value
1143    * @param CRM_Core_BAO_CustomField|int|array|string $field
1144    * @param $contactId
1145    *
1146    * @return string
1147    * @throws \Exception
1148    */
1149   public static function displayValue($value, $field, $contactId = NULL) {
1150     $field = is_array($field) ? $field['id'] : $field;
1151     $fieldId = is_object($field) ? $field->id : (int) str_replace('custom_', '', $field);
1152
1153     if (!$fieldId) {
1154       throw new Exception('CRM_Core_BAO_CustomField::displayValue requires a field id');
1155     }
1156
1157     if (!is_a($field, 'CRM_Core_BAO_CustomField')) {
1158       $field = self::getFieldObject($fieldId);
1159     }
1160
1161     $fieldInfo = array('options' => $field->getOptions()) + (array) $field;
1162
1163     return self::formatDisplayValue($value, $fieldInfo, $contactId);
1164   }
1165
1166   /**
1167    * Lower-level logic for rendering a custom field value
1168    *
1169    * @param string|array $value
1170    * @param array $field
1171    * @param int|null $entityId
1172    *
1173    * @return string
1174    */
1175   private static function formatDisplayValue($value, $field, $entityId = NULL) {
1176
1177     if (self::isSerialized($field) && !is_array($value)) {
1178       $value = CRM_Utils_Array::explodePadded($value);
1179     }
1180     // CRM-12989 fix
1181     if ($field['html_type'] == 'CheckBox') {
1182       CRM_Utils_Array::formatArrayKeys($value);
1183     }
1184
1185     $display = is_array($value) ? implode(', ', $value) : (string) $value;
1186
1187     switch ($field['html_type']) {
1188
1189       case 'Select':
1190       case 'Autocomplete-Select':
1191       case 'Radio':
1192       case 'Select Country':
1193       case 'Select State/Province':
1194       case 'CheckBox':
1195       case 'AdvMulti-Select':
1196       case 'Multi-Select':
1197       case 'Multi-Select State/Province':
1198       case 'Multi-Select Country':
1199         if ($field['data_type'] == 'ContactReference' && $value) {
1200           $display = CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_Contact', $value, 'display_name');
1201         }
1202         elseif (is_array($value)) {
1203           $v = array();
1204           foreach ($value as $key => $val) {
1205             $v[] = CRM_Utils_Array::value($val, $field['options']);
1206           }
1207           $display = implode(', ', $v);
1208         }
1209         else {
1210           $display = CRM_Utils_Array::value($value, $field['options'], '');
1211         }
1212         break;
1213
1214       case 'Select Date':
1215         $customFormat = NULL;
1216
1217         // FIXME: Are there any legitimate reasons why $value would be an array?
1218         // Or should we throw an exception here if it is?
1219         $value = is_array($value) ? CRM_Utils_Array::first($value) : $value;
1220
1221         $actualPHPFormats = CRM_Utils_Date::datePluginToPHPFormats();
1222         $format = CRM_Utils_Array::value('date_format', $field);
1223
1224         if ($format) {
1225           if (array_key_exists($format, $actualPHPFormats)) {
1226             $customTimeFormat = (array) $actualPHPFormats[$format];
1227             switch (CRM_Utils_Array::value('time_format', $field)) {
1228               case 1:
1229                 $customTimeFormat[] = 'g:iA';
1230                 break;
1231
1232               case 2:
1233                 $customTimeFormat[] = 'G:i';
1234                 break;
1235
1236               default:
1237                 // if time is not selected remove time from value
1238                 $value = substr($value, 0, 10);
1239             }
1240             $customFormat = implode(" ", $customTimeFormat);
1241           }
1242         }
1243         $display = CRM_Utils_Date::processDate($value, NULL, FALSE, $customFormat);
1244         break;
1245
1246       case 'File':
1247         // In the context of displaying a profile, show file/image
1248         if ($value) {
1249           if ($entityId) {
1250             $url = self::getFileURL($entityId, $field['id']);
1251             if ($url) {
1252               $display = $url['file_url'];
1253             }
1254           }
1255           else {
1256             // In other contexts show a paperclip icon
1257             if (CRM_Utils_Rule::integer($value)) {
1258               $icons = CRM_Core_BAO_File::paperIconAttachment('*', $value);
1259               $display = $icons[$value];
1260             }
1261             else {
1262               //CRM-18396, if filename is passed instead
1263               $display = $value;
1264             }
1265           }
1266         }
1267         break;
1268
1269       case 'TextArea':
1270         $display = nl2br($display);
1271         break;
1272
1273       case 'Text':
1274         if ($field['data_type'] == 'Money' && isset($value)) {
1275           //$value can also be an array(while using IN operator from search builder or api).
1276           foreach ((array) $value as $val) {
1277             $disp[] = CRM_Utils_Money::format($val, NULL, NULL, TRUE);
1278           }
1279           $display = implode(', ', $disp);
1280         }
1281         break;
1282     }
1283     return $display;
1284   }
1285
1286   /**
1287    * Set default values for custom data used in profile.
1288    *
1289    * @param int $customFieldId
1290    *   Custom field id.
1291    * @param string $elementName
1292    *   Custom field name.
1293    * @param array $defaults
1294    *   Associated array of fields.
1295    * @param int $contactId
1296    *   Contact id.
1297    * @param int $mode
1298    *   Profile mode.
1299    * @param mixed $value
1300    *   If passed - dont fetch value from db,.
1301    *                               just format the given value
1302    */
1303   public static function setProfileDefaults(
1304     $customFieldId,
1305     $elementName,
1306     &$defaults,
1307     $contactId = NULL,
1308     $mode = NULL,
1309     $value = NULL
1310   ) {
1311     //get the type of custom field
1312     $customField = new CRM_Core_BAO_CustomField();
1313     $customField->id = $customFieldId;
1314     $customField->find(TRUE);
1315
1316     if (!$contactId) {
1317       if ($mode == CRM_Profile_Form::MODE_CREATE) {
1318         $value = $customField->default_value;
1319       }
1320     }
1321     else {
1322       if (!isset($value)) {
1323         $info = self::getTableColumnGroup($customFieldId);
1324         $query = "SELECT {$info[0]}.{$info[1]} as value FROM {$info[0]} WHERE {$info[0]}.entity_id = {$contactId}";
1325         $result = CRM_Core_DAO::executeQuery($query);
1326         if ($result->fetch()) {
1327           $value = $result->value;
1328         }
1329       }
1330
1331       if ($customField->data_type == 'Country') {
1332         if (!$value) {
1333           $config = CRM_Core_Config::singleton();
1334           if ($config->defaultContactCountry) {
1335             $value = $config->defaultContactCountry();
1336           }
1337         }
1338       }
1339     }
1340
1341     //set defaults if mode is registration
1342     if (!trim($value) &&
1343       ($value !== 0) &&
1344       (!in_array($mode, array(CRM_Profile_Form::MODE_EDIT, CRM_Profile_Form::MODE_SEARCH)))
1345     ) {
1346       $value = $customField->default_value;
1347     }
1348
1349     if ($customField->data_type == 'Money' && isset($value)) {
1350       $value = number_format($value, 2);
1351     }
1352     switch ($customField->html_type) {
1353       case 'CheckBox':
1354       case 'AdvMulti-Select':
1355       case 'Multi-Select':
1356         $customOption = CRM_Core_BAO_CustomOption::getCustomOption($customFieldId, FALSE);
1357         $defaults[$elementName] = array();
1358         $checkedValue = explode(CRM_Core_DAO::VALUE_SEPARATOR,
1359           substr($value, 1, -1)
1360         );
1361         foreach ($customOption as $val) {
1362           if (in_array($val['value'], $checkedValue)) {
1363             if ($customField->html_type == 'CheckBox') {
1364               $defaults[$elementName][$val['value']] = 1;
1365             }
1366             elseif ($customField->html_type == 'Multi-Select' ||
1367               $customField->html_type == 'AdvMulti-Select'
1368             ) {
1369               $defaults[$elementName][$val['value']] = $val['value'];
1370             }
1371           }
1372         }
1373         break;
1374
1375       case 'Autocomplete-Select':
1376         if ($customField->data_type == 'ContactReference') {
1377           if (is_numeric($value)) {
1378             $defaults[$elementName . '_id'] = $value;
1379             $defaults[$elementName] = CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_Contact', $value, 'sort_name');
1380           }
1381         }
1382         else {
1383           $defaults[$elementName] = $value;
1384         }
1385         break;
1386
1387       default:
1388         $defaults[$elementName] = $value;
1389     }
1390   }
1391
1392   /**
1393    * Get file url.
1394    *
1395    * @param int $contactID
1396    * @param int $cfID
1397    * @param int $fileID
1398    * @param bool $absolute
1399    *
1400    * @param string $multiRecordWhereClause
1401    *
1402    * @return array
1403    */
1404   public static function getFileURL($contactID, $cfID, $fileID = NULL, $absolute = FALSE, $multiRecordWhereClause = NULL) {
1405     if ($contactID) {
1406       if (!$fileID) {
1407         $params = array('id' => $cfID);
1408         $defaults = array();
1409         CRM_Core_DAO::commonRetrieve('CRM_Core_DAO_CustomField', $params, $defaults);
1410         $columnName = $defaults['column_name'];
1411
1412         //table name of custom data
1413         $tableName = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_CustomGroup',
1414           $defaults['custom_group_id'],
1415           'table_name', 'id'
1416         );
1417
1418         //query to fetch id from civicrm_file
1419         if ($multiRecordWhereClause) {
1420           $query = "SELECT {$columnName} FROM {$tableName} where entity_id = {$contactID} AND {$multiRecordWhereClause}";
1421         }
1422         else {
1423           $query = "SELECT {$columnName} FROM {$tableName} where entity_id = {$contactID}";
1424         }
1425         $fileID = CRM_Core_DAO::singleValueQuery($query);
1426       }
1427
1428       $result = array();
1429       if ($fileID) {
1430         $fileType = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_File',
1431           $fileID,
1432           'mime_type',
1433           'id'
1434         );
1435         $result['file_id'] = $fileID;
1436
1437         if ($fileType == 'image/jpeg' ||
1438           $fileType == 'image/pjpeg' ||
1439           $fileType == 'image/gif' ||
1440           $fileType == 'image/x-png' ||
1441           $fileType == 'image/png'
1442         ) {
1443           $entityId = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_EntityFile',
1444             $fileID,
1445             'entity_id',
1446             'file_id'
1447           );
1448           list($path) = CRM_Core_BAO_File::path($fileID, $entityId, NULL, NULL);
1449           $url = CRM_Utils_System::url('civicrm/file',
1450             "reset=1&id=$fileID&eid=$contactID",
1451             $absolute, NULL, TRUE, TRUE
1452           );
1453           $result['file_url'] = CRM_Utils_File::getFileURL($path, $fileType, $url);
1454         }
1455         // for non image files
1456         else {
1457           $uri = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_File',
1458             $fileID,
1459             'uri'
1460           );
1461           $url = CRM_Utils_System::url('civicrm/file',
1462             "reset=1&id=$fileID&eid=$contactID",
1463             $absolute, NULL, TRUE, TRUE
1464           );
1465           $result['file_url'] = CRM_Utils_File::getFileURL($uri, $fileType, $url);
1466         }
1467       }
1468       return $result;
1469     }
1470   }
1471
1472   /**
1473    * Format custom fields before inserting.
1474    *
1475    * @param int $customFieldId
1476    *   Custom field id.
1477    * @param array $customFormatted
1478    *   Formatted array.
1479    * @param mixed $value
1480    *   Value of custom field.
1481    * @param string $customFieldExtend
1482    *   Custom field extends.
1483    * @param int $customValueId
1484    *   Custom option value id.
1485    * @param int $entityId
1486    *   Entity id (contribution, membership...).
1487    * @param bool $inline
1488    *   Consider inline custom groups only.
1489    * @param bool $checkPermission
1490    *   If false, do not include permissioning clause.
1491    * @param bool $includeViewOnly
1492    *   If true, fields marked 'View Only' are included. Required for APIv3.
1493    *
1494    * @return array|NULL
1495    *   formatted custom field array
1496    */
1497   public static function formatCustomField(
1498     $customFieldId, &$customFormatted, $value,
1499     $customFieldExtend, $customValueId = NULL,
1500     $entityId = NULL,
1501     $inline = FALSE,
1502     $checkPermission = TRUE,
1503     $includeViewOnly = FALSE
1504   ) {
1505     //get the custom fields for the entity
1506     //subtype and basic type
1507     $customDataSubType = NULL;
1508     if ($customFieldExtend) {
1509       // This is the case when getFieldsForImport() requires fields
1510       // of subtype and its parent.CRM-5143
1511       // CRM-16065 - Custom field set data not being saved if contact has more than one contact sub type
1512       $customDataSubType = array_intersect(CRM_Contact_BAO_ContactType::subTypes(), (array) $customFieldExtend);
1513       if (!empty($customDataSubType) && is_array($customDataSubType)) {
1514         $customFieldExtend = CRM_Contact_BAO_ContactType::getBasicType($customDataSubType);
1515         if (is_array($customFieldExtend)) {
1516           $customFieldExtend = array_unique(array_values($customFieldExtend));
1517         }
1518       }
1519     }
1520
1521     $customFields = CRM_Core_BAO_CustomField::getFields($customFieldExtend,
1522       FALSE,
1523       $inline,
1524       $customDataSubType,
1525       NULL,
1526       FALSE,
1527       FALSE,
1528       $checkPermission
1529     );
1530
1531     if (!array_key_exists($customFieldId, $customFields)) {
1532       return NULL;
1533     }
1534
1535     // return if field is a 'code' field
1536     if (!$includeViewOnly && !empty($customFields[$customFieldId]['is_view'])) {
1537       return NULL;
1538     }
1539
1540     list($tableName, $columnName, $groupID) = self::getTableColumnGroup($customFieldId);
1541
1542     if (!$customValueId &&
1543       // we always create new entites for is_multiple unless specified
1544       !$customFields[$customFieldId]['is_multiple'] &&
1545       $entityId
1546     ) {
1547       $query = "
1548 SELECT id
1549   FROM $tableName
1550  WHERE entity_id={$entityId}";
1551
1552       $customValueId = CRM_Core_DAO::singleValueQuery($query);
1553     }
1554
1555     //fix checkbox, now check box always submits values
1556     if ($customFields[$customFieldId]['html_type'] == 'CheckBox') {
1557       if ($value) {
1558         // Note that only during merge this is not an array, and you can directly use value
1559         if (is_array($value)) {
1560           $selectedValues = array();
1561           foreach ($value as $selId => $val) {
1562             if ($val) {
1563               $selectedValues[] = $selId;
1564             }
1565           }
1566           if (!empty($selectedValues)) {
1567             $value = CRM_Core_DAO::VALUE_SEPARATOR . implode(CRM_Core_DAO::VALUE_SEPARATOR,
1568                 $selectedValues
1569               ) . CRM_Core_DAO::VALUE_SEPARATOR;
1570           }
1571           else {
1572             $value = '';
1573           }
1574         }
1575       }
1576     }
1577
1578     if ($customFields[$customFieldId]['html_type'] == 'Multi-Select' ||
1579       $customFields[$customFieldId]['html_type'] == 'AdvMulti-Select'
1580     ) {
1581       if ($value) {
1582         $value = CRM_Utils_Array::implodePadded($value);
1583       }
1584       else {
1585         $value = '';
1586       }
1587     }
1588
1589     if (($customFields[$customFieldId]['html_type'] == 'Multi-Select' ||
1590         $customFields[$customFieldId]['html_type'] == 'AdvMulti-Select' ||
1591         $customFields[$customFieldId]['html_type'] == 'CheckBox'
1592       ) &&
1593       $customFields[$customFieldId]['data_type'] == 'String' &&
1594       !empty($customFields[$customFieldId]['text_length']) &&
1595       !empty($value)
1596     ) {
1597       // lets make sure that value is less than the length, else we'll
1598       // be losing some data, CRM-7481
1599       if (strlen($value) >= $customFields[$customFieldId]['text_length']) {
1600         // need to do a few things here
1601
1602         // 1. lets find a new length
1603         $newLength = $customFields[$customFieldId]['text_length'];
1604         $minLength = strlen($value);
1605         while ($newLength < $minLength) {
1606           $newLength = $newLength * 2;
1607         }
1608
1609         // set the custom field meta data to have a length larger than value
1610         // alter the custom value table column to match this length
1611         CRM_Core_BAO_SchemaHandler::alterFieldLength($customFieldId, $tableName, $columnName, $newLength);
1612       }
1613     }
1614
1615     $date = NULL;
1616     if ($customFields[$customFieldId]['data_type'] == 'Date') {
1617       if (!CRM_Utils_System::isNull($value)) {
1618         $format = $customFields[$customFieldId]['date_format'];
1619         $date = CRM_Utils_Date::processDate($value, NULL, FALSE, 'YmdHis', $format);
1620       }
1621       $value = $date;
1622     }
1623
1624     if ($customFields[$customFieldId]['data_type'] == 'Float' ||
1625       $customFields[$customFieldId]['data_type'] == 'Money'
1626     ) {
1627       if (!$value) {
1628         $value = 0;
1629       }
1630
1631       if ($customFields[$customFieldId]['data_type'] == 'Money') {
1632         $value = CRM_Utils_Rule::cleanMoney($value);
1633       }
1634     }
1635
1636     if (($customFields[$customFieldId]['data_type'] == 'StateProvince' ||
1637         $customFields[$customFieldId]['data_type'] == 'Country'
1638       ) &&
1639       empty($value)
1640     ) {
1641       // CRM-3415
1642       $value = 0;
1643     }
1644
1645     $fileId = NULL;
1646
1647     if ($customFields[$customFieldId]['data_type'] == 'File') {
1648       if (empty($value)) {
1649         return;
1650       }
1651
1652       $config = CRM_Core_Config::singleton();
1653
1654       // If we are already passing the file id as a value then retrieve and set the file data
1655       if (CRM_Utils_Rule::integer($value)) {
1656         $fileDAO = new CRM_Core_DAO_File();
1657         $fileDAO->id = $value;
1658         $fileDAO->find(TRUE);
1659         if ($fileDAO->N) {
1660           $fileID = $value;
1661           $fName = $fileDAO->uri;
1662           $mimeType = $fileDAO->mime_type;
1663         }
1664       }
1665       else {
1666         $fName = $value['name'];
1667         $mimeType = $value['type'];
1668       }
1669
1670       $filename = pathinfo($fName, PATHINFO_BASENAME);
1671
1672       // rename this file to go into the secure directory only if
1673       // user has uploaded new file not existing verfied on the basis of $fileID
1674       if (empty($fileID) && !rename($fName, $config->customFileUploadDir . $filename)) {
1675         CRM_Core_Error::statusBounce(ts('Could not move custom file to custom upload directory'));
1676       }
1677
1678       if ($customValueId && empty($fileID)) {
1679         $query = "
1680 SELECT $columnName
1681   FROM $tableName
1682  WHERE id = %1";
1683         $params = array(1 => array($customValueId, 'Integer'));
1684         $fileId = CRM_Core_DAO::singleValueQuery($query, $params);
1685       }
1686
1687       $fileDAO = new CRM_Core_DAO_File();
1688
1689       if ($fileId) {
1690         $fileDAO->id = $fileId;
1691       }
1692
1693       $fileDAO->uri = $filename;
1694       $fileDAO->mime_type = $mimeType;
1695       $fileDAO->upload_date = date('YmdHis');
1696       $fileDAO->save();
1697       $fileId = $fileDAO->id;
1698       $value = $filename;
1699     }
1700
1701     if (!is_array($customFormatted)) {
1702       $customFormatted = array();
1703     }
1704
1705     if (!array_key_exists($customFieldId, $customFormatted)) {
1706       $customFormatted[$customFieldId] = array();
1707     }
1708
1709     $index = -1;
1710     if ($customValueId) {
1711       $index = $customValueId;
1712     }
1713
1714     if (!array_key_exists($index, $customFormatted[$customFieldId])) {
1715       $customFormatted[$customFieldId][$index] = array();
1716     }
1717     $customFormatted[$customFieldId][$index] = array(
1718       'id' => $customValueId > 0 ? $customValueId : NULL,
1719       'value' => $value,
1720       'type' => $customFields[$customFieldId]['data_type'],
1721       'custom_field_id' => $customFieldId,
1722       'custom_group_id' => $groupID,
1723       'table_name' => $tableName,
1724       'column_name' => $columnName,
1725       'file_id' => $fileId,
1726       'is_multiple' => $customFields[$customFieldId]['is_multiple'],
1727     );
1728
1729     //we need to sort so that custom fields are created in the order of entry
1730     krsort($customFormatted[$customFieldId]);
1731     return $customFormatted;
1732   }
1733
1734   /**
1735    * Get default custom table schema.
1736    *
1737    * @param array $params
1738    *
1739    * @return array
1740    */
1741   public static function defaultCustomTableSchema($params) {
1742     // add the id and extends_id
1743     $table = array(
1744       'name' => $params['name'],
1745       'is_multiple' => $params['is_multiple'],
1746       'attributes' => "ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci",
1747       'fields' => array(
1748         array(
1749           'name' => 'id',
1750           'type' => 'int unsigned',
1751           'primary' => TRUE,
1752           'required' => TRUE,
1753           'attributes' => 'AUTO_INCREMENT',
1754           'comment' => 'Default MySQL primary key',
1755         ),
1756         array(
1757           'name' => 'entity_id',
1758           'type' => 'int unsigned',
1759           'required' => TRUE,
1760           'comment' => 'Table that this extends',
1761           'fk_table_name' => $params['extends_name'],
1762           'fk_field_name' => 'id',
1763           'fk_attributes' => 'ON DELETE CASCADE',
1764         ),
1765       ),
1766     );
1767
1768     if (!$params['is_multiple']) {
1769       $table['indexes'] = array(
1770         array(
1771           'unique' => TRUE,
1772           'field_name_1' => 'entity_id',
1773         ),
1774       );
1775     }
1776     return $table;
1777   }
1778
1779   /**
1780    * Create custom field.
1781    *
1782    * @param CRM_Core_DAO_CustomField $field
1783    * @param string $operation
1784    * @param bool $indexExist
1785    * @param bool $triggerRebuild
1786    */
1787   public static function createField($field, $operation, $indexExist = FALSE, $triggerRebuild = TRUE) {
1788     $tableName = CRM_Core_DAO::getFieldValue(
1789       'CRM_Core_DAO_CustomGroup',
1790       $field->custom_group_id,
1791       'table_name'
1792     );
1793
1794     $params = array(
1795       'table_name' => $tableName,
1796       'operation' => $operation,
1797       'name' => $field->column_name,
1798       'type' => CRM_Core_BAO_CustomValueTable::fieldToSQLType(
1799         $field->data_type,
1800         $field->text_length
1801       ),
1802       'required' => $field->is_required,
1803       'searchable' => $field->is_searchable,
1804     );
1805
1806     if ($operation == 'delete') {
1807       $fkName = "{$tableName}_{$field->column_name}";
1808       if (strlen($fkName) >= 48) {
1809         $fkName = substr($fkName, 0, 32) . '_' . substr(md5($fkName), 0, 16);
1810       }
1811       $params['fkName'] = $fkName;
1812     }
1813     if ($field->data_type == 'Country' && $field->html_type == 'Select Country') {
1814       $params['fk_table_name'] = 'civicrm_country';
1815       $params['fk_field_name'] = 'id';
1816       $params['fk_attributes'] = 'ON DELETE SET NULL';
1817     }
1818     elseif ($field->data_type == 'Country' && $field->html_type == 'Multi-Select Country') {
1819       $params['type'] = 'varchar(255)';
1820     }
1821     elseif ($field->data_type == 'StateProvince' && $field->html_type == 'Select State/Province') {
1822       $params['fk_table_name'] = 'civicrm_state_province';
1823       $params['fk_field_name'] = 'id';
1824       $params['fk_attributes'] = 'ON DELETE SET NULL';
1825     }
1826     elseif ($field->data_type == 'StateProvince' && $field->html_type == 'Multi-Select State/Province') {
1827       $params['type'] = 'varchar(255)';
1828     }
1829     elseif ($field->data_type == 'File') {
1830       $params['fk_table_name'] = 'civicrm_file';
1831       $params['fk_field_name'] = 'id';
1832       $params['fk_attributes'] = 'ON DELETE SET NULL';
1833     }
1834     elseif ($field->data_type == 'ContactReference') {
1835       $params['fk_table_name'] = 'civicrm_contact';
1836       $params['fk_field_name'] = 'id';
1837       $params['fk_attributes'] = 'ON DELETE SET NULL';
1838     }
1839     if (isset($field->default_value)) {
1840       $params['default'] = "'{$field->default_value}'";
1841     }
1842
1843     CRM_Core_BAO_SchemaHandler::alterFieldSQL($params, $indexExist, $triggerRebuild);
1844   }
1845
1846   /**
1847    * Determine whether it would be safe to move a field.
1848    *
1849    * @param int $fieldID
1850    *   FK to civicrm_custom_field.
1851    * @param int $newGroupID
1852    *   FK to civicrm_custom_group.
1853    *
1854    * @return array
1855    *   array(string) or TRUE
1856    */
1857   public static function _moveFieldValidate($fieldID, $newGroupID) {
1858     $errors = array();
1859
1860     $field = new CRM_Core_DAO_CustomField();
1861     $field->id = $fieldID;
1862     if (!$field->find(TRUE)) {
1863       $errors['fieldID'] = 'Invalid ID for custom field';
1864       return $errors;
1865     }
1866
1867     $oldGroup = new CRM_Core_DAO_CustomGroup();
1868     $oldGroup->id = $field->custom_group_id;
1869     if (!$oldGroup->find(TRUE)) {
1870       $errors['fieldID'] = 'Invalid ID for old custom group';
1871       return $errors;
1872     }
1873
1874     $newGroup = new CRM_Core_DAO_CustomGroup();
1875     $newGroup->id = $newGroupID;
1876     if (!$newGroup->find(TRUE)) {
1877       $errors['newGroupID'] = 'Invalid ID for new custom group';
1878       return $errors;
1879     }
1880
1881     $query = "
1882 SELECT     b.id
1883 FROM       civicrm_custom_field a
1884 INNER JOIN civicrm_custom_field b
1885 WHERE      a.id = %1
1886 AND        a.label = b.label
1887 AND        b.custom_group_id = %2
1888 ";
1889     $params = array(
1890       1 => array($field->id, 'Integer'),
1891       2 => array($newGroup->id, 'Integer'),
1892     );
1893     $count = CRM_Core_DAO::singleValueQuery($query, $params);
1894     if ($count > 0) {
1895       $errors['newGroupID'] = ts('A field of the same label exists in the destination group');
1896     }
1897
1898     $tableName = $oldGroup->table_name;
1899     $columnName = $field->column_name;
1900
1901     $query = "
1902 SELECT count(*)
1903 FROM   $tableName
1904 WHERE  $columnName is not null
1905 ";
1906     $count = CRM_Core_DAO::singleValueQuery($query,
1907       CRM_Core_DAO::$_nullArray
1908     );
1909     if ($count > 0) {
1910       $query = "
1911 SELECT extends
1912 FROM   civicrm_custom_group
1913 WHERE  id IN ( %1, %2 )
1914 ";
1915       $params = array(
1916         1 => array($oldGroup->id, 'Integer'),
1917         2 => array($newGroup->id, 'Integer'),
1918       );
1919
1920       $dao = CRM_Core_DAO::executeQuery($query, $params);
1921       $extends = array();
1922       while ($dao->fetch()) {
1923         $extends[] = $dao->extends;
1924       }
1925       if ($extends[0] != $extends[1]) {
1926         $errors['newGroupID'] = ts('The destination group extends a different entity type.');
1927       }
1928     }
1929
1930     return empty($errors) ? TRUE : $errors;
1931   }
1932
1933   /**
1934    * Move a custom data field from one group (table) to another.
1935    *
1936    * @param int $fieldID
1937    *   FK to civicrm_custom_field.
1938    * @param int $newGroupID
1939    *   FK to civicrm_custom_group.
1940    */
1941   public static function moveField($fieldID, $newGroupID) {
1942     $validation = self::_moveFieldValidate($fieldID, $newGroupID);
1943     if (TRUE !== $validation) {
1944       CRM_Core_Error::fatal(implode(' ', $validation));
1945     }
1946     $field = new CRM_Core_DAO_CustomField();
1947     $field->id = $fieldID;
1948     $field->find(TRUE);
1949
1950     $newGroup = new CRM_Core_DAO_CustomGroup();
1951     $newGroup->id = $newGroupID;
1952     $newGroup->find(TRUE);
1953
1954     $oldGroup = new CRM_Core_DAO_CustomGroup();
1955     $oldGroup->id = $field->custom_group_id;
1956     $oldGroup->find(TRUE);
1957
1958     $add = clone$field;
1959     $add->custom_group_id = $newGroup->id;
1960     self::createField($add, 'add');
1961
1962     $sql = "INSERT INTO {$newGroup->table_name} (entity_id, {$field->column_name})
1963             SELECT entity_id, {$field->column_name} FROM {$oldGroup->table_name}
1964             ON DUPLICATE KEY UPDATE {$field->column_name} = {$oldGroup->table_name}.{$field->column_name}
1965             ";
1966     CRM_Core_DAO::executeQuery($sql);
1967
1968     $del = clone$field;
1969     $del->custom_group_id = $oldGroup->id;
1970     self::createField($del, 'delete');
1971
1972     $add->save();
1973
1974     CRM_Utils_System::flushCache();
1975   }
1976
1977   /**
1978    * Get the database table name and column name for a custom field.
1979    *
1980    * @param int $fieldID
1981    *   The fieldID of the custom field.
1982    * @param bool $force
1983    *   Force the sql to be run again (primarily used for tests).
1984    *
1985    * @return array
1986    *   fatal is fieldID does not exists, else array of tableName, columnName
1987    */
1988   public static function getTableColumnGroup($fieldID, $force = FALSE) {
1989     $cacheKey = "CRM_Core_DAO_CustomField_CustomGroup_TableColumn_{$fieldID}";
1990     $cache = CRM_Utils_Cache::singleton();
1991     $fieldValues = $cache->get($cacheKey);
1992     if (empty($fieldValues) || $force) {
1993       $query = "
1994 SELECT cg.table_name, cf.column_name, cg.id
1995 FROM   civicrm_custom_group cg,
1996        civicrm_custom_field cf
1997 WHERE  cf.custom_group_id = cg.id
1998 AND    cf.id = %1";
1999       $params = array(1 => array($fieldID, 'Integer'));
2000       $dao = CRM_Core_DAO::executeQuery($query, $params);
2001
2002       if (!$dao->fetch()) {
2003         CRM_Core_Error::fatal();
2004       }
2005       $dao->free();
2006       $fieldValues = array($dao->table_name, $dao->column_name, $dao->id);
2007       $cache->set($cacheKey, $fieldValues);
2008     }
2009     return $fieldValues;
2010   }
2011
2012   /**
2013    * Get custom option groups.
2014    *
2015    * @param array $includeFieldIds
2016    *   Ids of custom fields for which option groups must be included.
2017    *
2018    * Currently this is required in the cases where option groups are to be included
2019    * for inactive fields : CRM-5369
2020    *
2021    * @return mixed
2022    */
2023   public static function customOptionGroup($includeFieldIds = NULL) {
2024     static $customOptionGroup = NULL;
2025
2026     $cacheKey = (empty($includeFieldIds)) ? 'onlyActive' : 'force';
2027     if ($cacheKey == 'force') {
2028       $customOptionGroup[$cacheKey] = NULL;
2029     }
2030
2031     if (empty($customOptionGroup[$cacheKey])) {
2032       $whereClause = '( g.is_active = 1 AND f.is_active = 1 )';
2033
2034       //support for single as well as array format.
2035       if (!empty($includeFieldIds)) {
2036         if (is_array($includeFieldIds)) {
2037           $includeFieldIds = implode(',', $includeFieldIds);
2038         }
2039         $whereClause .= "OR f.id IN ( $includeFieldIds )";
2040       }
2041
2042       $query = "
2043     SELECT  g.id, g.title
2044       FROM  civicrm_option_group g
2045 INNER JOIN  civicrm_custom_field f ON ( g.id = f.option_group_id )
2046      WHERE  {$whereClause}";
2047
2048       $dao = CRM_Core_DAO::executeQuery($query);
2049       while ($dao->fetch()) {
2050         $customOptionGroup[$cacheKey][$dao->id] = $dao->title;
2051       }
2052     }
2053
2054     return $customOptionGroup[$cacheKey];
2055   }
2056
2057   /**
2058    * Fix orphan groups.
2059    *
2060    * @param int $customFieldId
2061    *   Custom field id.
2062    * @param int $optionGroupId
2063    *   Option group id.
2064    */
2065   public static function fixOptionGroups($customFieldId, $optionGroupId) {
2066     // check if option group belongs to any custom Field else delete
2067     // get the current option group
2068     $currentOptionGroupId = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_CustomField',
2069       $customFieldId,
2070       'option_group_id'
2071     );
2072     // get the updated option group
2073     // if both are same return
2074     if ($currentOptionGroupId == $optionGroupId) {
2075       return;
2076     }
2077
2078     // check if option group is related to any other field
2079     self::checkOptionGroup($currentOptionGroupId);
2080   }
2081
2082   /**
2083    * Check if option group is related to more than one custom field.
2084    *
2085    * @param int $optionGroupId
2086    *   Option group id.
2087    */
2088   public static function checkOptionGroup($optionGroupId) {
2089     $query = "
2090 SELECT count(*)
2091 FROM   civicrm_custom_field
2092 WHERE  option_group_id = {$optionGroupId}";
2093
2094     $count = CRM_Core_DAO::singleValueQuery($query);
2095
2096     if ($count < 2) {
2097       //delete the option group
2098       CRM_Core_BAO_OptionGroup::del($optionGroupId);
2099     }
2100   }
2101
2102   /**
2103    * Get option group default.
2104    *
2105    * @param int $optionGroupId
2106    * @param string $htmlType
2107    *
2108    * @return null|string
2109    */
2110   public static function getOptionGroupDefault($optionGroupId, $htmlType) {
2111     $query = "
2112 SELECT   default_value, html_type
2113 FROM     civicrm_custom_field
2114 WHERE    option_group_id = {$optionGroupId}
2115 AND      default_value IS NOT NULL
2116 ORDER BY html_type";
2117
2118     $dao = CRM_Core_DAO::executeQuery($query);
2119     $defaultValue = NULL;
2120     $defaultHTMLType = NULL;
2121     while ($dao->fetch()) {
2122       if ($dao->html_type == $htmlType) {
2123         return $dao->default_value;
2124       }
2125       if ($defaultValue == NULL) {
2126         $defaultValue = $dao->default_value;
2127         $defaultHTMLType = $dao->html_type;
2128       }
2129     }
2130
2131     // some conversions are needed if either the old or new has a html type which has potential
2132     // multiple default values.
2133     if (($htmlType == 'CheckBox' || $htmlType == 'Multi-Select') &&
2134       ($defaultHTMLType != 'CheckBox' && $defaultHTMLType != 'Multi-Select')
2135     ) {
2136       $defaultValue = CRM_Core_DAO::VALUE_SEPARATOR . $defaultValue . CRM_Core_DAO::VALUE_SEPARATOR;
2137     }
2138     elseif (($defaultHTMLType == 'CheckBox' || $defaultHTMLType == 'Multi-Select') &&
2139       ($htmlType != 'CheckBox' && $htmlType != 'Multi-Select')
2140     ) {
2141       $defaultValue = substr($defaultValue, 1, -1);
2142       $values = explode(CRM_Core_DAO::VALUE_SEPARATOR,
2143         substr($defaultValue, 1, -1)
2144       );
2145       $defaultValue = $values[0];
2146     }
2147
2148     return $defaultValue;
2149   }
2150
2151   /**
2152    * Post process function.
2153    *
2154    * @param array $params
2155    * @param int $entityID
2156    * @param string $customFieldExtends
2157    * @param bool $inline
2158    * @param bool $checkPermissions
2159    *
2160    * @return array
2161    */
2162   public static function postProcess(
2163     &$params,
2164     $entityID,
2165     $customFieldExtends,
2166     $inline = FALSE,
2167     $checkPermissions = TRUE
2168   ) {
2169     $customData = array();
2170
2171     foreach ($params as $key => $value) {
2172       if ($customFieldInfo = CRM_Core_BAO_CustomField::getKeyID($key, TRUE)) {
2173
2174         // for autocomplete transfer hidden value instead of label
2175         if ($params[$key] && isset($params[$key . '_id'])) {
2176           $value = $params[$key . '_id'];
2177         }
2178
2179         // we need to append time with date
2180         if ($params[$key] && isset($params[$key . '_time'])) {
2181           $value .= ' ' . $params[$key . '_time'];
2182         }
2183
2184         CRM_Core_BAO_CustomField::formatCustomField($customFieldInfo[0],
2185           $customData,
2186           $value,
2187           $customFieldExtends,
2188           $customFieldInfo[1],
2189           $entityID,
2190           $inline,
2191           $checkPermissions
2192         );
2193       }
2194     }
2195     return $customData;
2196   }
2197
2198   /**
2199    *
2200    */
2201
2202   /**
2203    * Get custom field ID.
2204    *
2205    * @param string $fieldLabel
2206    * @param null $groupTitle
2207    *
2208    * @return int|null
2209    */
2210   public static function getCustomFieldID($fieldLabel, $groupTitle = NULL) {
2211     $params = array(1 => array($fieldLabel, 'String'));
2212     if ($groupTitle) {
2213       $params[2] = array($groupTitle, 'String');
2214       $sql = "
2215 SELECT     f.id
2216 FROM       civicrm_custom_field f
2217 INNER JOIN civicrm_custom_group g ON f.custom_group_id = g.id
2218 WHERE      ( f.label = %1 OR f.name  = %1 )
2219 AND        ( g.title = %2 OR g.name = %2 )
2220 ";
2221     }
2222     else {
2223       $sql = "
2224 SELECT     f.id
2225 FROM       civicrm_custom_field f
2226 WHERE      ( f.label = %1 OR f.name = %1 )
2227 ";
2228     }
2229
2230     $dao = CRM_Core_DAO::executeQuery($sql, $params);
2231     if ($dao->fetch() &&
2232       $dao->N == 1
2233     ) {
2234       return $dao->id;
2235     }
2236     else {
2237       return NULL;
2238     }
2239   }
2240
2241   /**
2242    * Given ID of a custom field, return its name as well as the name of the custom group it belongs to.
2243    *
2244    * @param array $ids
2245    *
2246    * @return array
2247    */
2248   public static function getNameFromID($ids) {
2249     if (is_array($ids)) {
2250       $ids = implode(',', $ids);
2251     }
2252     $sql = "
2253 SELECT     f.id, f.name AS field_name, f.label AS field_label, g.name AS group_name, g.title AS group_title
2254 FROM       civicrm_custom_field f
2255 INNER JOIN civicrm_custom_group g ON f.custom_group_id = g.id
2256 WHERE      f.id IN ($ids)";
2257
2258     $dao = CRM_Core_DAO::executeQuery($sql);
2259     $result = array();
2260     while ($dao->fetch()) {
2261       $result[$dao->id] = array(
2262         'field_name' => $dao->field_name,
2263         'field_label' => $dao->field_label,
2264         'group_name' => $dao->group_name,
2265         'group_title' => $dao->group_title,
2266       );
2267     }
2268     return $result;
2269   }
2270
2271   /**
2272    * Validate custom data.
2273    *
2274    * @param array $params
2275    *   Custom data submitted.
2276    *   ie array( 'custom_1' => 'validate me' );
2277    *
2278    * @return array
2279    *   validation errors.
2280    */
2281   public static function validateCustomData($params) {
2282     $errors = array();
2283     if (!is_array($params) || empty($params)) {
2284       return $errors;
2285     }
2286
2287     //pick up profile fields.
2288     $profileFields = array();
2289     $ufGroupId = CRM_Utils_Array::value('ufGroupId', $params);
2290     if ($ufGroupId) {
2291       $profileFields = CRM_Core_BAO_UFGroup::getFields($ufGroupId,
2292         FALSE,
2293         CRM_Core_Action::VIEW
2294       );
2295     }
2296
2297     //lets start w/ params.
2298     foreach ($params as $key => $value) {
2299       $customFieldID = self::getKeyID($key);
2300       if (!$customFieldID) {
2301         continue;
2302       }
2303
2304       //load the structural info for given field.
2305       $field = new CRM_Core_DAO_CustomField();
2306       $field->id = $customFieldID;
2307       if (!$field->find(TRUE)) {
2308         continue;
2309       }
2310       $dataType = $field->data_type;
2311
2312       $profileField = CRM_Utils_Array::value($key, $profileFields, array());
2313       $fieldTitle = CRM_Utils_Array::value('title', $profileField);
2314       $isRequired = CRM_Utils_Array::value('is_required', $profileField);
2315       if (!$fieldTitle) {
2316         $fieldTitle = $field->label;
2317       }
2318
2319       //no need to validate.
2320       if (CRM_Utils_System::isNull($value) && !$isRequired) {
2321         continue;
2322       }
2323
2324       //lets validate first for required field.
2325       if ($isRequired && CRM_Utils_System::isNull($value)) {
2326         $errors[$key] = ts('%1 is a required field.', array(1 => $fieldTitle));
2327         continue;
2328       }
2329
2330       //now time to take care of custom field form rules.
2331       $ruleName = $errorMsg = NULL;
2332       switch ($dataType) {
2333         case 'Int':
2334           $ruleName = 'integer';
2335           $errorMsg = ts('%1 must be an integer (whole number).',
2336             array(1 => $fieldTitle)
2337           );
2338           break;
2339
2340         case 'Money':
2341           $ruleName = 'money';
2342           $errorMsg = ts('%1 must in proper money format. (decimal point/comma/space is allowed).',
2343             array(1 => $fieldTitle)
2344           );
2345           break;
2346
2347         case 'Float':
2348           $ruleName = 'numeric';
2349           $errorMsg = ts('%1 must be a number (with or without decimal point).',
2350             array(1 => $fieldTitle)
2351           );
2352           break;
2353
2354         case 'Link':
2355           $ruleName = 'wikiURL';
2356           $errorMsg = ts('%1 must be valid Website.',
2357             array(1 => $fieldTitle)
2358           );
2359           break;
2360       }
2361
2362       if ($ruleName && !CRM_Utils_System::isNull($value)) {
2363         $valid = FALSE;
2364         $funName = "CRM_Utils_Rule::{$ruleName}";
2365         if (is_callable($funName)) {
2366           $valid = call_user_func($funName, $value);
2367         }
2368         if (!$valid) {
2369           $errors[$key] = $errorMsg;
2370         }
2371       }
2372     }
2373
2374     return $errors;
2375   }
2376
2377   /**
2378    * Is this field a multi record field.
2379    *
2380    * @param int $customId
2381    *
2382    * @return bool
2383    */
2384   public static function isMultiRecordField($customId) {
2385     $isMultipleWithGid = FALSE;
2386     if (!is_numeric($customId)) {
2387       $customId = self::getKeyID($customId);
2388     }
2389     if (is_numeric($customId)) {
2390       $sql = "SELECT cg.id cgId
2391  FROM civicrm_custom_group cg
2392  INNER JOIN civicrm_custom_field cf
2393  ON cg.id = cf.custom_group_id
2394 WHERE cf.id = %1 AND cg.is_multiple = 1";
2395       $params[1] = array($customId, 'Integer');
2396       $dao = CRM_Core_DAO::executeQuery($sql, $params);
2397       if ($dao->fetch()) {
2398         if ($dao->cgId) {
2399           $isMultipleWithGid = $dao->cgId;
2400         }
2401       }
2402     }
2403
2404     return $isMultipleWithGid;
2405   }
2406
2407   /**
2408    * Does this field store a serialized string?
2409    *
2410    * @param array $field
2411    *
2412    * @return bool
2413    */
2414   public static function isSerialized($field) {
2415     // Fields retrieved via api are an array, or from the dao are an object. We'll accept either.
2416     $field = (array) $field;
2417     // FIXME: Currently the only way to know if data is serialized is by looking at the html_type. It would be cleaner to decouple this.
2418     return ($field['html_type'] == 'CheckBox' || strpos($field['html_type'], 'Multi') !== FALSE);
2419   }
2420
2421   /**
2422    * Get api entity for this field
2423    *
2424    * @return string
2425    */
2426   public function getEntity() {
2427     $entity = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_CustomGroup', $this->custom_group_id, 'extends');
2428     return in_array($entity, array('Individual', 'Household', 'Organization')) ? 'Contact' : $entity;
2429   }
2430
2431   /**
2432    * Set pseudoconstant properties for field metadata.
2433    *
2434    * @param array $field
2435    * @param string|null $optionGroupName
2436    */
2437   private static function getOptionsForField(&$field, $optionGroupName) {
2438     if ($optionGroupName) {
2439       $field['pseudoconstant'] = array(
2440         'optionGroupName' => $optionGroupName,
2441         'optionEditPath' => 'civicrm/admin/options/' . $optionGroupName,
2442       );
2443     }
2444     elseif ($field['data_type'] == 'Boolean') {
2445       $field['pseudoconstant'] = array(
2446         'callback' => 'CRM_Core_SelectValues::boolean',
2447       );
2448     }
2449     elseif ($field['data_type'] == 'Country') {
2450       $field['pseudoconstant'] = array(
2451         'table' => 'civicrm_country',
2452         'keyColumn' => 'id',
2453         'labelColumn' => 'name',
2454         'nameColumn' => 'iso_code',
2455       );
2456     }
2457     elseif ($field['data_type'] == 'StateProvince') {
2458       $field['pseudoconstant'] = array(
2459         'table' => 'civicrm_state_province',
2460         'keyColumn' => 'id',
2461         'labelColumn' => 'name',
2462       );
2463     }
2464   }
2465
2466 }