CRM/Core/BAO/CustomField.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | Copyright CiviCRM LLC. All rights reserved.                        |
   5  |                                                                    |
   6  | This work is published under the GNU AGPLv3 license with some      |
   7  | permitted exceptions and without any warranty. For full license    |
   8  | and copyright information, see https://civicrm.org/licensing       |
   9  +--------------------------------------------------------------------+
  10  */
  11
  12 /**
  13  *
  14  * @package CRM
  15  * @copyright CiviCRM LLC https://civicrm.org/licensing
  16  */
  17
  18 /**
  19  * Business objects for managing custom data fields.
  20  */
  21 class CRM_Core_BAO_CustomField extends CRM_Core_DAO_CustomField {
  22
  23   /**
  24    * Array to hold (formatted) fields for import
  25    *
  26    * @var array
  27    */
  28   public static $_importFields = NULL;
  29
  30   /**
  31    * Build and retrieve the list of data types and descriptions.
  32    *
  33    * @return array
  34    *   Data type => Description
  35    */
  36   public static function dataType() {
  37     return [
  38       [
  39         'id' => 'String',
  40         'name' => 'Alphanumeric',
  41         'label' => ts('Alphanumeric'),
  42       ],
  43       [
  44         'id' => 'Int',
  45         'name' => 'Integer',
  46         'label' => ts('Integer'),
  47       ],
  48       [
  49         'id' => 'Float',
  50         'name' => 'Number',
  51         'label' => ts('Number'),
  52       ],
  53       [
  54         'id' => 'Money',
  55         'name' => 'Money',
  56         'label' => ts('Money'),
  57       ],
  58       [
  59         'id' => 'Memo',
  60         'name' => 'Note',
  61         'label' => ts('Note'),
  62       ],
  63       [
  64         'id' => 'Date',
  65         'name' => 'Date',
  66         'label' => ts('Date'),
  67       ],
  68       [
  69         'id' => 'Boolean',
  70         'name' => 'Yes or No',
  71         'label' => ts('Yes or No'),
  72       ],
  73       [
  74         'id' => 'StateProvince',
  75         'name' => 'State/Province',
  76         'label' => ts('State/Province'),
  77       ],
  78       [
  79         'id' => 'Country',
  80         'name' => 'Country',
  81         'label' => ts('Country'),
  82       ],
  83       [
  84         'id' => 'File',
  85         'name' => 'File',
  86         'label' => ts('File'),
  87       ],
  88       [
  89         'id' => 'Link',
  90         'name' => 'Link',
  91         'label' => ts('Link'),
  92       ],
  93       [
  94         'id' => 'ContactReference',
  95         'name' => 'Contact Reference',
  96         'label' => ts('Contact Reference'),
  97       ],
  98     ];
  99   }
 100
 101   /**
 102    * Build the map of custom field's data types and there respective Util type
 103    *
 104    * @return array
 105    *   Data data-type => CRM_Utils_Type
 106    */
 107   public static function dataToType() {
 108     return [
 109       'String' => CRM_Utils_Type::T_STRING,
 110       'Int' => CRM_Utils_Type::T_INT,
 111       'Money' => CRM_Utils_Type::T_MONEY,
 112       'Memo' => CRM_Utils_Type::T_LONGTEXT,
 113       'Float' => CRM_Utils_Type::T_FLOAT,
 114       'Date' => CRM_Utils_Type::T_DATE,
 115       'DateTime' => CRM_Utils_Type::T_DATE + CRM_Utils_Type::T_TIME,
 116       'Boolean' => CRM_Utils_Type::T_BOOLEAN,
 117       'StateProvince' => CRM_Utils_Type::T_INT,
 118       'File' => CRM_Utils_Type::T_STRING,
 119       'Link' => CRM_Utils_Type::T_STRING,
 120       'ContactReference' => CRM_Utils_Type::T_INT,
 121       'Country' => CRM_Utils_Type::T_INT,
 122     ];
 123   }
 124
 125   /**
 126    * Deprecated in favor of writeRecords & APIv4
 127    *
 128    * @deprecated
 129    * @param array $params
 130    * @return CRM_Core_DAO_CustomField
 131    */
 132   public static function create($params) {
 133     $changeSerialize = self::getChangeSerialize($params);
 134     $customField = self::createCustomFieldRecord($params);
 135     // When deserializing a field, the update needs to run before the schema change
 136     if ($changeSerialize === 0) {
 137       CRM_Core_DAO::singleValueQuery(self::getAlterSerializeSQL($customField));
 138     }
 139     $op = empty($params['id']) ? 'add' : 'modify';
 140     self::createField($customField, $op);
 141     // When serializing a field, the update needs to run after the schema change
 142     if ($changeSerialize === 1) {
 143       CRM_Core_DAO::singleValueQuery(self::getAlterSerializeSQL($customField));
 144     }
 145
 146     CRM_Utils_Hook::post(($op === 'add' ? 'create' : 'edit'), 'CustomField', $customField->id, $customField);
 147
 148     CRM_Utils_System::flushCache();
 149     // Flush caches is not aggressive about clearing the specific cache we know we want to clear
 150     // so do it manually. Ideally we wouldn't need to clear others...
 151     Civi::cache('metadata')->clear();
 152
 153     return $customField;
 154   }
 155
 156   /**
 157    * Save multiple fields, now deprecated in favor of self::writeRecords.
 158    * https://lab.civicrm.org/dev/core/issues/1093
 159    * @deprecated
 160    *
 161    * @param array $bulkParams
 162    *   Array of arrays as would be passed into create
 163    * @param array $defaults
 164    *  Default parameters to be be merged into each of the params.
 165    *
 166    * @throws \CiviCRM_API3_Exception
 167    */
 168   public static function bulkSave($bulkParams, $defaults = []) {
 169     CRM_Core_Error::deprecatedFunctionWarning(__CLASS__ . '::writeRecords');
 170     foreach ($bulkParams as $index => $fieldParams) {
 171       $bulkParams[$index] = array_merge($defaults, $fieldParams);
 172     }
 173     self::writeRecords($bulkParams);
 174   }
 175
 176   /**
 177    * Create/update several fields at once in a mysql efficient way.
 178    *
 179    * @param array $records
 180    * @return CRM_Core_DAO_CustomField[]
 181    * @throws CRM_Core_Exception
 182    */
 183   public static function writeRecords(array $records): array {
 184     $addedColumns = $sql = $customFields = $pre = $post = [];
 185     foreach ($records as $index => $params) {
 186       CRM_Utils_Hook::pre(empty($params['id']) ? 'create' : 'edit', 'CustomField', $params['id'] ?? NULL, $params);
 187
 188       $changeSerialize = self::getChangeSerialize($params);
 189       $customField = self::createCustomFieldRecord($params);
 190       // Serialize/deserialize sql must run after/before the table is altered
 191       if ($changeSerialize === 0) {
 192         $pre[] = self::getAlterSerializeSQL($customField);
 193       }
 194       if ($changeSerialize === 1) {
 195         $post[] = self::getAlterSerializeSQL($customField);
 196       }
 197       $fieldSQL = self::getAlterFieldSQL($customField, empty($params['id']) ? 'add' : 'modify');
 198
 199       $tableName = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_CustomGroup', $customField->custom_group_id, 'table_name');
 200       $sql[$tableName][] = $fieldSQL;
 201       $addedColumns[$tableName][] = $customField->column_name;
 202       $customFields[$index] = $customField;
 203     }
 204
 205     foreach ($pre as $query) {
 206       CRM_Core_DAO::executeQuery($query);
 207     }
 208
 209     foreach ($sql as $tableName => $statements) {
 210       // CRM-7007: do not i18n-rewrite this query
 211       CRM_Core_DAO::executeQuery("ALTER TABLE $tableName " . implode(', ', $statements), [], TRUE, NULL, FALSE, FALSE);
 212
 213       if (CRM_Core_Config::singleton()->logging) {
 214         $logging = new CRM_Logging_Schema();
 215         $logging->fixSchemaDifferencesFor($tableName, ['ADD' => $addedColumns[$tableName]]);
 216       }
 217
 218       Civi::service('sql_triggers')->rebuild($tableName, TRUE);
 219     }
 220
 221     foreach ($post as $query) {
 222       CRM_Core_DAO::executeQuery($query);
 223     }
 224
 225     CRM_Utils_System::flushCache();
 226     Civi::cache('metadata')->clear();
 227
 228     foreach ($customFields as $index => $customField) {
 229       $op = empty($records[$index]['id']) ? 'create' : 'edit';
 230       // Theoretically a custom field could have custom fields! Trippy...
 231       if (!empty($records[$index]['custom']) && is_array($records[$index]['custom'])) {
 232         CRM_Core_BAO_CustomValueTable::store($records[$index]['custom'], static::$_tableName, $customField->id, $op);
 233       }
 234       CRM_Utils_Hook::post($op, 'CustomField', $customField->id, $customField);
 235     }
 236     return $customFields;
 237   }
 238
 239   /**
 240    * Retrieve DB object and copy to defaults array.
 241    *
 242    * @param array $params
 243    *   Array of criteria values.
 244    * @param array $defaults
 245    *   Array to be populated with found values.
 246    *
 247    * @return self|null
 248    *   The DAO object, if found.
 249    *
 250    * @deprecated
 251    */
 252   public static function retrieve($params, &$defaults) {
 253     return self::commonRetrieve(self::class, $params, $defaults);
 254   }
 255
 256   /**
 257    * Update the is_active flag in the db.
 258    *
 259    * @param int $id
 260    *   Id of the database record.
 261    * @param bool $is_active
 262    *   Value we want to set the is_active field.
 263    *
 264    * @return bool
 265    *   true if we found and updated the object, else false
 266    */
 267   public static function setIsActive($id, $is_active) {
 268
 269     CRM_Utils_System::flushCache();
 270
 271     //enable-disable CustomField
 272     CRM_Core_BAO_UFField::setUFField($id, $is_active);
 273     return CRM_Core_DAO::setFieldValue('CRM_Core_DAO_CustomField', $id, 'is_active', $is_active);
 274   }
 275
 276   /**
 277    * Get the field title.
 278    *
 279    * @param int $id
 280    *   Id of field.
 281    *
 282    * @return string
 283    *   name
 284    */
 285   public static function getTitle($id) {
 286     return CRM_Core_DAO::getFieldValue('CRM_Core_DAO_CustomField', $id, 'label');
 287   }
 288
 289   /**
 290    * @param string $context
 291    * @return array|bool
 292    */
 293   public function getOptions($context = NULL) {
 294     CRM_Core_DAO::buildOptionsContext($context);
 295
 296     if (!$this->id) {
 297       return FALSE;
 298     }
 299     $cacheKey = "CRM_Core_BAO_CustomField_getOptions_{$this->id}_$context";
 300     $cache = CRM_Utils_Cache::singleton();
 301     $options = $cache->get($cacheKey);
 302     if (!isset($options)) {
 303       if (!$this->data_type || !$this->custom_group_id) {
 304         $this->find(TRUE);
 305       }
 306
 307       // This will hold the list of options in format key => label
 308       $options = [];
 309
 310       if (!empty($this->option_group_id)) {
 311         $options = CRM_Core_OptionGroup::valuesByID(
 312         $this->option_group_id, FALSE, FALSE, FALSE, $context == 'validate' ? 'name' : 'label', !($context == 'validate' || $context == 'get')
 313         );
 314       }
 315       elseif ($this->data_type === 'StateProvince') {
 316         $options = CRM_Core_PseudoConstant::stateProvince();
 317       }
 318       elseif ($this->data_type === 'Country') {
 319         $options = $context == 'validate' ? CRM_Core_PseudoConstant::countryIsoCode() : CRM_Core_PseudoConstant::country();
 320       }
 321       elseif ($this->data_type === 'Boolean') {
 322         $options = $context == 'validate' ? [0, 1] : CRM_Core_SelectValues::boolean();
 323       }
 324       CRM_Utils_Hook::customFieldOptions($this->id, $options, FALSE);
 325       CRM_Utils_Hook::fieldOptions($this->getEntity(), "custom_{$this->id}", $options, ['context' => $context]);
 326       $cache->set($cacheKey, $options);
 327     }
 328     return $options;
 329   }
 330
 331   /**
 332    * @inheritDoc
 333    */
 334   public static function buildOptions($fieldName, $context = NULL, $props = []) {
 335     $options = parent::buildOptions($fieldName, $context, $props);
 336     // This provides legacy support for APIv3, allowing no-longer-existent html types
 337     if ($fieldName == 'html_type' && isset($props['version']) && $props['version'] == 3) {
 338       $options['Multi-Select'] = 'Multi-Select';
 339       $options['Select Country'] = 'Select Country';
 340       $options['Multi-Select Country'] = 'Multi-Select Country';
 341       $options['Select State/Province'] = 'Select State/Province';
 342       $options['Multi-Select State/Province'] = 'Multi-Select State/Province';
 343     }
 344     return $options;
 345   }
 346
 347   /**
 348    * Store and return an array of all active custom fields.
 349    *
 350    * @param string $customDataType
 351    *   Type of Custom Data; 'ANY' is a synonym for "all contact data types".
 352    * @param bool $showAll
 353    *   If true returns all fields (includes disabled fields).
 354    * @param bool $inline
 355    *   If true returns all inline fields (includes disabled fields).
 356    * @param int $customDataSubType
 357    *   Custom Data sub type value.
 358    * @param int $customDataSubName
 359    *   Custom Data sub name value.
 360    * @param bool $onlyParent
 361    *   Return only top level custom data, for eg, only Participant and ignore subname and subtype.
 362    * @param bool $onlySubType
 363    *   Return only custom data for subtype.
 364    * @param bool|int $checkPermission
 365    *   Either a CRM_Core_Permission constant or FALSE to disable checks
 366    *
 367    * @return array
 368    *   an array of active custom fields.
 369    * @throws \CRM_Core_Exception
 370    */
 371   public static function &getFields(
 372     $customDataType = 'Individual',
 373     $showAll = FALSE,
 374     $inline = FALSE,
 375     $customDataSubType = NULL,
 376     $customDataSubName = NULL,
 377     $onlyParent = FALSE,
 378     $onlySubType = FALSE,
 379     $checkPermission = CRM_Core_Permission::EDIT
 380   ) {
 381     if ($checkPermission === TRUE) {
 382       CRM_Core_Error::deprecatedWarning('Unexpected TRUE passed to CustomField::getFields $checkPermission param.');
 383       $checkPermission = CRM_Core_Permission::EDIT;
 384     }
 385     if (empty($customDataType)) {
 386       $customDataType = array_merge(['Contact'], CRM_Contact_BAO_ContactType::basicTypes());
 387     }
 388     if ($customDataType === 'ANY') {
 389       // NULL should have been respected but the line above broke that.
 390       // boo for us not having enough unit tests back them.
 391       $customDataType = NULL;
 392     }
 393     if ($customDataType && !is_array($customDataType)) {
 394
 395       if (in_array($customDataType, CRM_Contact_BAO_ContactType::subTypes(), TRUE)) {
 396         // This is the case when getFieldsForImport() requires fields
 397         // limited strictly to a subtype.
 398         $customDataSubType = $customDataType;
 399         $customDataType = CRM_Contact_BAO_ContactType::getBasicType($customDataType);
 400         $onlySubType = TRUE;
 401       }
 402
 403       if (array_key_exists($customDataType, CRM_Core_SelectValues::customGroupExtends())) {
 404         // this makes the method flexible to support retrieving fields
 405         // for multiple extends value.
 406         $customDataType = [$customDataType];
 407       }
 408     }
 409
 410     $customDataSubType = CRM_Utils_Array::explodePadded($customDataSubType);
 411
 412     if (is_array($customDataType)) {
 413       $cacheKey = implode('_', $customDataType);
 414     }
 415     else {
 416       $cacheKey = $customDataType;
 417     }
 418
 419     $cacheKey .= !empty($customDataSubType) ? ('_' . implode('_', $customDataSubType)) : '_0';
 420     $cacheKey .= $customDataSubName ? "{$customDataSubName}_" : '_0';
 421     $cacheKey .= $showAll ? '_1' : '_0';
 422     $cacheKey .= $inline ? '_1_' : '_0_';
 423     $cacheKey .= $onlyParent ? '_1_' : '_0_';
 424     $cacheKey .= $onlySubType ? '_1_' : '_0_';
 425     $cacheKey .= $checkPermission ? $checkPermission . CRM_Core_Session::getLoggedInContactID() . '_' : '_0_0_';
 426     $cacheKey .= '_' . CRM_Core_Config::domainID() . '_';
 427
 428     $cgTable = CRM_Core_DAO_CustomGroup::getTableName();
 429
 430     // also get the permission stuff here
 431     if ($checkPermission) {
 432       $permissionClause = CRM_Core_Permission::customGroupClause($checkPermission,
 433         "{$cgTable}."
 434       );
 435     }
 436     else {
 437       $permissionClause = '(1)';
 438     }
 439
 440     // lets md5 permission clause and take first 8 characters
 441     $cacheKey .= substr(md5($permissionClause), 0, 8);
 442
 443     if (strlen($cacheKey) > 40) {
 444       $cacheKey = md5($cacheKey);
 445     }
 446
 447     if (!self::$_importFields ||
 448       CRM_Utils_Array::value($cacheKey, self::$_importFields) === NULL
 449     ) {
 450       if (!self::$_importFields) {
 451         self::$_importFields = [];
 452       }
 453
 454       // check if we can retrieve from database cache
 455       $fields = Civi::Cache('fields')->get("custom importableFields $cacheKey");
 456
 457       if ($fields === NULL) {
 458
 459         $extends = '';
 460         if (is_array($customDataType)) {
 461           $value = NULL;
 462           foreach ($customDataType as $dataType) {
 463             if (array_key_exists($dataType, CRM_Core_SelectValues::customGroupExtends())) {
 464               if (in_array($dataType, CRM_Contact_BAO_ContactType::basicTypes(TRUE), TRUE)) {
 465                 $val = "'" . CRM_Utils_Type::escape($dataType, 'String') . "', 'Contact' ";
 466               }
 467               else {
 468                 $val = "'" . CRM_Utils_Type::escape($dataType, 'String') . "'";
 469               }
 470               $value = $value ? $value . ", {$val}" : $val;
 471             }
 472           }
 473           if ($value) {
 474             $extends = "AND   $cgTable.extends IN ( $value ) ";
 475           }
 476         }
 477
 478         if (!empty($customDataType) && empty($extends)) {
 479           // $customDataType specified a filter, but there is no corresponding SQL ($extends)
 480           self::$_importFields[$cacheKey] = [];
 481           return self::$_importFields[$cacheKey];
 482         }
 483
 484         if ($onlyParent) {
 485           $extends .= " AND $cgTable.extends_entity_column_value IS NULL AND $cgTable.extends_entity_column_id IS NULL ";
 486         }
 487         // Temporary hack - in 5.27 a new field is added to civicrm_custom_field. There is a high
 488         // risk this function is called before the upgrade page can be reached and if
 489         // so it will potentially result in fatal error.
 490         $serializeField = CRM_Core_BAO_Domain::isDBVersionAtLeast('5.27.alpha1') ? "custom_field.serialize," : '';
 491
 492         $query = "SELECT custom_field.id, custom_field.label,
 493                             $cgTable.title,
 494                             custom_field.data_type,
 495                             custom_field.html_type,
 496                             custom_field.default_value,
 497                             custom_field.options_per_line, custom_field.text_length,
 498                             custom_field.custom_group_id,
 499                             custom_field.is_required,
 500                             custom_field.column_name,
 501                             $cgTable.extends, custom_field.is_search_range,
 502                             $cgTable.extends_entity_column_value,
 503                             $cgTable.extends_entity_column_id,
 504                             custom_field.is_view,
 505                             custom_field.option_group_id,
 506                             custom_field.date_format,
 507                             custom_field.time_format,
 508                             $cgTable.is_multiple,
 509                             $serializeField
 510                             $cgTable.table_name,
 511                             og.name as option_group_name
 512                      FROM civicrm_custom_field custom_field
 513                      INNER JOIN $cgTable
 514                        ON custom_field.custom_group_id = $cgTable.id
 515                      LEFT JOIN civicrm_option_group og
 516                        ON custom_field.option_group_id = og.id
 517                      WHERE ( 1 ) ";
 518
 519         if (!$showAll) {
 520           $query .= " AND custom_field.is_active = 1 AND $cgTable.is_active = 1 ";
 521         }
 522
 523         if ($inline) {
 524           $query .= " AND $cgTable.style = 'Inline' ";
 525         }
 526
 527         //get the custom fields for specific type in
 528         //combination with fields those support any type.
 529         if (!empty($customDataSubType)) {
 530           $subtypeClause = [];
 531           foreach ($customDataSubType as $subtype) {
 532             $subtype = CRM_Core_DAO::VALUE_SEPARATOR . CRM_Utils_Type::escape($subtype, 'String') . CRM_Core_DAO::VALUE_SEPARATOR;
 533             $subtypeClause[] = "$cgTable.extends_entity_column_value LIKE '%{$subtype}%'";
 534           }
 535           if (!$onlySubType) {
 536             $subtypeClause[] = "$cgTable.extends_entity_column_value IS NULL";
 537           }
 538           $query .= " AND ( " . implode(' OR ', $subtypeClause) . " )";
 539         }
 540
 541         if ($customDataSubName) {
 542           $query .= " AND ( $cgTable.extends_entity_column_id = $customDataSubName ) ";
 543         }
 544
 545         // also get the permission stuff here
 546         if ($checkPermission) {
 547           $permissionClause = CRM_Core_Permission::customGroupClause($checkPermission,
 548             "{$cgTable}.", TRUE
 549           );
 550         }
 551         else {
 552           $permissionClause = '(1)';
 553         }
 554
 555         $query .= " $extends AND $permissionClause
 556                         ORDER BY $cgTable.weight, $cgTable.title,
 557                                  custom_field.weight, custom_field.label";
 558
 559         $dao = CRM_Core_DAO::executeQuery($query);
 560
 561         $fields = [];
 562         while (($dao->fetch()) != NULL) {
 563           $regexp = preg_replace('/[.,;:!?]/', '', '');
 564           $fields[$dao->id]['id'] = $dao->id;
 565           $fields[$dao->id]['label'] = $dao->label;
 566           // This seems broken, but not in a new way.
 567           $fields[$dao->id]['headerPattern'] = '/' . preg_quote($regexp, '/') . '/';
 568           // To support the consolidation of various functions & their expectations.
 569           $fields[$dao->id]['title'] = $dao->label;
 570           $fields[$dao->id]['custom_field_id']  = $dao->id;
 571           $fields[$dao->id]['groupTitle'] = $dao->title;
 572           $fields[$dao->id]['data_type'] = $dao->data_type;
 573           $fields[$dao->id]['name'] = 'custom_' . $dao->id;
 574           $fields[$dao->id]['type'] = CRM_Utils_Array::value($dao->data_type, self::dataToType());
 575           $fields[$dao->id]['html_type'] = $dao->html_type;
 576           $fields[$dao->id]['default_value'] = $dao->default_value;
 577           $fields[$dao->id]['text_length'] = $dao->text_length;
 578           $fields[$dao->id]['options_per_line'] = $dao->options_per_line;
 579           $fields[$dao->id]['custom_group_id'] = $dao->custom_group_id;
 580           $fields[$dao->id]['extends'] = $dao->extends;
 581           $fields[$dao->id]['is_search_range'] = $dao->is_search_range;
 582           $fields[$dao->id]['extends_entity_column_value'] = $dao->extends_entity_column_value;
 583           $fields[$dao->id]['extends_entity_column_id'] = $dao->extends_entity_column_id;
 584           $fields[$dao->id]['is_view'] = $dao->is_view;
 585           $fields[$dao->id]['is_multiple'] = $dao->is_multiple;
 586           $fields[$dao->id]['option_group_id'] = $dao->option_group_id;
 587           $fields[$dao->id]['date_format'] = $dao->date_format;
 588           $fields[$dao->id]['time_format'] = $dao->time_format;
 589           $fields[$dao->id]['is_required'] = $dao->is_required;
 590           $fields[$dao->id]['table_name'] = $dao->table_name;
 591           $fields[$dao->id]['column_name'] = $dao->column_name;
 592           $fields[$dao->id]['serialize'] = $serializeField ? $dao->serialize : (int) self::isSerialized($dao);
 593           $fields[$dao->id]['where'] = $dao->table_name . '.' . $dao->column_name;
 594           // Probably we should use a different fn to get the extends tables but this is a refactor so not changing that now.
 595           $fields[$dao->id]['extends_table'] = array_key_exists($dao->extends, CRM_Core_BAO_CustomQuery::$extendsMap) ? CRM_Core_BAO_CustomQuery::$extendsMap[$dao->extends] : '';
 596           if (in_array($dao->extends, CRM_Contact_BAO_ContactType::subTypes())) {
 597             // if $extends is a subtype, refer contact table
 598             $fields[$dao->id]['extends_table'] = 'civicrm_contact';
 599           }
 600           // Search table is used by query object searches..
 601           $fields[$dao->id]['search_table'] = ($fields[$dao->id]['extends_table'] == 'civicrm_contact') ? 'contact_a' : $fields[$dao->id]['extends_table'];
 602           self::getOptionsForField($fields[$dao->id], $dao->option_group_name);
 603         }
 604
 605         Civi::cache('fields')->set("custom importableFields $cacheKey", $fields);
 606       }
 607       self::$_importFields[$cacheKey] = $fields;
 608     }
 609
 610     return self::$_importFields[$cacheKey];
 611   }
 612
 613   /**
 614    * Return field ids and names (with groups).
 615    *
 616    * NOTE: Despite this function's name, it is used both for IMPORT and EXPORT.
 617    * The $checkPermission variable should be set to VIEW for export and EDIT for import.
 618    *
 619    * @param int|string $contactType Contact type
 620    * @param bool $showAll
 621    *   If true returns all fields (includes disabled fields).
 622    * @param bool $onlyParent
 623    *   Return fields ONLY related to basic types.
 624    * @param bool $search
 625    *   When called from search and multiple records need to be returned.
 626    * @param bool|int $checkPermission
 627    *   Either a CRM_Core_Permission constant or FALSE to disable checks
 628    *
 629    * @param bool $withMultiple
 630    *
 631    * @return array
 632    */
 633   public static function getFieldsForImport(
 634     $contactType = 'Individual',
 635     $showAll = FALSE,
 636     $onlyParent = FALSE,
 637     $search = FALSE,
 638     $checkPermission = TRUE,
 639     $withMultiple = FALSE
 640   ) {
 641     if ($checkPermission === TRUE) {
 642       // TODO: Trigger deprecation notice for passing TRUE
 643       $checkPermission = CRM_Core_Permission::EDIT;
 644     }
 645     // Note: there are situations when we want getFieldsForImport() return fields related
 646     // ONLY to basic contact types, but NOT subtypes. And thats where $onlyParent is helpful
 647     $fields = &self::getFields($contactType,
 648       $showAll,
 649       FALSE,
 650       NULL,
 651       NULL,
 652       $onlyParent,
 653       FALSE,
 654       $checkPermission
 655     );
 656
 657     $importableFields = [];
 658     foreach ($fields as $id => $values) {
 659       // for now we should not allow multiple fields in profile / export etc, hence unsetting
 660       if (!$search &&
 661         (!empty($values['is_multiple']) && !$withMultiple)
 662       ) {
 663         continue;
 664       }
 665       if (!empty($values['text_length'])) {
 666         $values['maxlength'] = (int) $values['text_length'];
 667       }
 668
 669       /* generate the key for the fields array */
 670
 671       $key = "custom_$id";
 672       $importableFields[$key] = $values;
 673       $importableFields[$key]['import'] = 1;
 674     }
 675
 676     return $importableFields;
 677   }
 678
 679   /**
 680    * Get the field id from an import key.
 681    *
 682    * @param string $key
 683    *   The key to parse.
 684    *
 685    * @param bool $all
 686    *
 687    * @return int|null
 688    *   The id (if exists)
 689    */
 690   public static function getKeyID($key, $all = FALSE) {
 691     $match = [];
 692     if (preg_match('/^custom_(\d+)_?(-?\d+)?$/', $key, $match)) {
 693       if (!$all) {
 694         return $match[1];
 695       }
 696       else {
 697         return [
 698           $match[1],
 699           CRM_Utils_Array::value(2, $match),
 700         ];
 701       }
 702     }
 703     return $all ? [NULL, NULL] : NULL;
 704   }
 705
 706   /**
 707    * Use the cache to get all values of a specific custom field.
 708    *
 709    * @param int $fieldID
 710    *   The custom field ID.
 711    *
 712    * @return CRM_Core_BAO_CustomField
 713    *   The field object.
 714    * @throws CRM_Core_Exception
 715    */
 716   public static function getFieldObject($fieldID) {
 717     $field = new CRM_Core_BAO_CustomField();
 718
 719     // check if we can get the field values from the system cache
 720     $cacheKey = "CRM_Core_DAO_CustomField_{$fieldID}";
 721     $cache = CRM_Utils_Cache::singleton();
 722     $fieldValues = $cache->get($cacheKey);
 723     if (empty($fieldValues)) {
 724       $field->id = $fieldID;
 725       if (!$field->find(TRUE)) {
 726         throw new CRM_Core_Exception('Cannot find Custom Field');
 727       }
 728
 729       $fieldValues = [];
 730       CRM_Core_DAO::storeValues($field, $fieldValues);
 731
 732       $cache->set($cacheKey, $fieldValues);
 733     }
 734     else {
 735       $field->copyValues($fieldValues);
 736     }
 737
 738     return $field;
 739   }
 740
 741   /**
 742    * Add a custom field to an existing form.
 743    *
 744    * @param CRM_Core_Form $qf
 745    *   Form object (reference).
 746    * @param string $elementName
 747    *   Name of the custom field.
 748    * @param int $fieldId
 749    * @param bool $useRequired
 750    *   True if required else false.
 751    * @param bool $search
 752    *   True if used for search else false.
 753    * @param string $label
 754    *   Label for custom field.
 755    * @return \HTML_QuickForm_Element|null
 756    * @throws \CiviCRM_API3_Exception
 757    */
 758   public static function addQuickFormElement(
 759     $qf, $elementName, $fieldId, $useRequired = TRUE, $search = FALSE, $label = NULL
 760   ) {
 761     $field = self::getFieldObject($fieldId);
 762     $widget = $field->html_type;
 763     $element = NULL;
 764     $customFieldAttributes = [];
 765
 766     if (!isset($label)) {
 767       $label = $field->label;
 768     }
 769
 770     // DAO stores attributes as a string, but it's hard to manipulate and
 771     // CRM_Core_Form::add() wants them as an array.
 772     $fieldAttributes = self::attributesFromString($field->attributes ?? '');
 773
 774     // Custom field HTML should indicate group+field name
 775     $groupName = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_CustomGroup', $field->custom_group_id);
 776     $fieldAttributes['data-crm-custom'] = $groupName . ':' . $field->name;
 777
 778     // Fixed for Issue CRM-2183
 779     if ($widget == 'TextArea' && $search) {
 780       $widget = 'Text';
 781     }
 782
 783     $placeholder = $search ? ts('- any %1 -', [1 => $label]) : ts('- select %1 -', [1 => $label]);
 784
 785     if (in_array($widget, [
 786       'Select',
 787       'CheckBox',
 788       'Radio',
 789     ])) {
 790       $options = $field->getOptions($search ? 'search' : 'create');
 791
 792       // Consolidate widget types to simplify the below switch statement
 793       if ($search) {
 794         $widget = 'Select';
 795       }
 796
 797       // Search field is always multi-select
 798       if ($search || (self::isSerialized($field) && $widget === 'Select')) {
 799         $fieldAttributes['class'] = ltrim(($fieldAttributes['class'] ?? '') . ' huge');
 800         $fieldAttributes['multiple'] = 'multiple';
 801         $fieldAttributes['placeholder'] = $placeholder;
 802       }
 803
 804       // Add data for popup link. Normally this is handled by CRM_Core_Form->addSelect
 805       $canEditOptions = CRM_Core_Permission::check('administer CiviCRM');
 806       if ($field->option_group_id && !$search && $canEditOptions) {
 807         $customFieldAttributes += [
 808           'data-api-entity' => $field->getEntity(),
 809           'data-api-field' => 'custom_' . $field->id,
 810           'data-option-edit-path' => 'civicrm/admin/options/' . CRM_Core_DAO::getFieldValue('CRM_Core_DAO_OptionGroup', $field->option_group_id),
 811         ];
 812         $fieldAttributes = array_merge($fieldAttributes, $customFieldAttributes);
 813       }
 814     }
 815
 816     $rangeDataTypes = ['Int', 'Float', 'Money'];
 817
 818     // at some point in time we might want to split the below into small functions
 819
 820     switch ($widget) {
 821       case 'Text':
 822       case 'Link':
 823         if ($field->is_search_range && $search && in_array($field->data_type, $rangeDataTypes)) {
 824           $qf->add('text', $elementName . '_from', $label . ' ' . ts('From'), $fieldAttributes);
 825           $qf->add('text', $elementName . '_to', ts('To'), $fieldAttributes);
 826         }
 827         else {
 828           if ($field->text_length) {
 829             $fieldAttributes['maxlength'] = $field->text_length;
 830             if ($field->text_length < 20) {
 831               $fieldAttributes['size'] = $field->text_length;
 832             }
 833           }
 834           $element = $qf->add('text', $elementName, $label,
 835             $fieldAttributes,
 836             $useRequired && !$search
 837           );
 838         }
 839         break;
 840
 841       case 'TextArea':
 842         $fieldAttributes['rows'] = $field->note_rows ?? 4;
 843         $fieldAttributes['cols'] = $field->note_columns ?? 60;
 844
 845         if ($field->text_length) {
 846           $fieldAttributes['maxlength'] = $field->text_length;
 847         }
 848         $element = $qf->add('textarea',
 849           $elementName,
 850           $label,
 851           $fieldAttributes,
 852           $useRequired && !$search
 853         );
 854         break;
 855
 856       case 'Select Date':
 857         //CRM-18379: Fix for date range of 'Select Date' custom field when include in profile.
 858         $minYear = isset($field->start_date_years) ? (date('Y') - $field->start_date_years) : NULL;
 859         $maxYear = isset($field->end_date_years) ? (date('Y') + $field->end_date_years) : NULL;
 860
 861         $params = [
 862           'date' => $field->date_format,
 863           'minDate' => isset($minYear) ? $minYear . '-01-01' : NULL,
 864           //CRM-18487 - max date should be the last date of the year.
 865           'maxDate' => isset($maxYear) ? $maxYear . '-12-31' : NULL,
 866           'time' => $field->time_format ? $field->time_format * 12 : FALSE,
 867         ];
 868         if ($field->is_search_range && $search) {
 869           $qf->add('datepicker', $elementName . '_from', $label, $fieldAttributes + array('placeholder' => ts('From')), FALSE, $params);
 870           $qf->add('datepicker', $elementName . '_to', NULL, $fieldAttributes + array('placeholder' => ts('To')), FALSE, $params);
 871         }
 872         else {
 873           $element = $qf->add('datepicker', $elementName, $label, $fieldAttributes, $useRequired && !$search, $params);
 874         }
 875         break;
 876
 877       case 'Radio':
 878         if ($field->is_search_range && $search && in_array($field->data_type, $rangeDataTypes)) {
 879           $qf->add('text', $elementName . '_from', $label . ' ' . ts('From'), $fieldAttributes);
 880           $qf->add('text', $elementName . '_to', ts('To'), $fieldAttributes);
 881         }
 882         else {
 883           $fieldAttributes = array_merge($fieldAttributes, $customFieldAttributes);
 884           if ($search || empty($useRequired)) {
 885             $fieldAttributes['allowClear'] = TRUE;
 886           }
 887           $qf->addRadio($elementName, $label, $options, $fieldAttributes, NULL, $useRequired);
 888         }
 889         break;
 890
 891       // For all select elements
 892       case 'Select':
 893         $fieldAttributes['class'] = ltrim(($fieldAttributes['class'] ?? '') . ' crm-select2');
 894
 895         if (empty($fieldAttributes['multiple'])) {
 896           $options = ['' => $placeholder] + $options;
 897         }
 898         $element = $qf->add('select', $elementName, $label, $options, $useRequired && !$search, $fieldAttributes);
 899
 900         // Add and/or option for fields that store multiple values
 901         if ($search && self::isSerialized($field)) {
 902           $qf->addRadio($elementName . '_operator', '', [
 903             'or' => ts('Any'),
 904             'and' => ts('All'),
 905           ], [], NULL, FALSE, [
 906             'or' => ['title' => ts('Results may contain any of the selected options')],
 907             'and' => ['title' => ts('Results must have all of the selected options')],
 908           ]);
 909           $qf->setDefaults([$elementName . '_operator' => 'or']);
 910         }
 911         break;
 912
 913       case 'CheckBox':
 914         $check = [];
 915         foreach ($options as $v => $l) {
 916           // TODO: I'm not sure if this is supposed to exclude whatever might be
 917           // in $field->attributes (available in array format as
 918           // $fieldAttributes).  Leaving as-is for now.
 919           $check[] = &$qf->addElement('advcheckbox', $v, NULL, $l, $customFieldAttributes);
 920         }
 921
 922         $group = $element = $qf->addGroup($check, $elementName, $label);
 923         $optionEditKey = 'data-option-edit-path';
 924         if (isset($customFieldAttributes[$optionEditKey])) {
 925           $group->setAttribute($optionEditKey, $customFieldAttributes[$optionEditKey]);
 926         }
 927
 928         if ($useRequired && !$search) {
 929           $qf->addRule($elementName, ts('%1 is a required field.', [1 => $label]), 'required');
 930         }
 931         break;
 932
 933       case 'File':
 934         // we should not build upload file in search mode
 935         if ($search) {
 936           return NULL;
 937         }
 938         $element = $qf->add(
 939           strtolower($field->html_type),
 940           $elementName,
 941           $label,
 942           $fieldAttributes,
 943           $useRequired && !$search
 944         );
 945         $qf->addUploadElement($elementName);
 946         break;
 947
 948       case 'RichTextEditor':
 949         $fieldAttributes['rows'] = $field->note_rows;
 950         $fieldAttributes['cols'] = $field->note_columns;
 951         if ($field->text_length) {
 952           $fieldAttributes['maxlength'] = $field->text_length;
 953         }
 954         $element = $qf->add('wysiwyg', $elementName, $label, $fieldAttributes, $useRequired && !$search);
 955         break;
 956
 957       case 'Autocomplete-Select':
 958         static $customUrls = [];
 959         if ($field->data_type == 'ContactReference') {
 960           // break if contact does not have permission to access ContactReference
 961           if (!CRM_Core_Permission::check('access contact reference fields')) {
 962             break;
 963           }
 964           $fieldAttributes['class'] = ltrim(($fieldAttributes['class'] ?? '') . ' crm-form-contact-reference huge');
 965           $fieldAttributes['data-api-entity'] = 'Contact';
 966           if (!empty($field->serialize) || $search) {
 967             $fieldAttributes['multiple'] = TRUE;
 968           }
 969           $element = $qf->add('text', $elementName, $label, $fieldAttributes, $useRequired && !$search);
 970
 971           $urlParams = "context=customfield&id={$field->id}";
 972           $idOfelement = $elementName;
 973           // dev/core#362 if in an onbehalf profile clean up the name to get rid of square brackets that break the select 2 js
 974           // However this caused regression https://lab.civicrm.org/dev/core/issues/619 so it has been hacked back to
 975           // only affecting on behalf - next time someone looks at this code it should be with a view to overhauling it
 976           // rather than layering on more hacks.
 977           if (substr($elementName, 0, 8) === 'onbehalf' && strpos($elementName, '[') && strpos($elementName, ']')) {
 978             $idOfelement = substr(substr($elementName, (strpos($elementName, '[') + 1)), 0, -1);
 979           }
 980           $customUrls[$idOfelement] = CRM_Utils_System::url('civicrm/ajax/contactref',
 981             $urlParams,
 982             FALSE, NULL, FALSE
 983           );
 984
 985         }
 986         else {
 987           // FIXME: This won't work with customFieldOptions hook
 988           $fieldAttributes += [
 989             'entity' => 'OptionValue',
 990             'placeholder' => $placeholder,
 991             'multiple' => $search ? TRUE : !empty($field->serialize),
 992             'api' => [
 993               'params' => ['option_group_id' => $field->option_group_id, 'is_active' => 1],
 994             ],
 995           ];
 996           $element = $qf->addEntityRef($elementName, $label, $fieldAttributes, $useRequired && !$search);
 997         }
 998
 999         $qf->assign('customUrls', $customUrls);
1000         break;
1001     }
1002
1003     switch ($field->data_type) {
1004       case 'Int':
1005         // integers will have numeric rule applied to them.
1006         if ($field->is_search_range && $search && $widget != 'Select') {
1007           $qf->addRule($elementName . '_from', ts('%1 From must be an integer (whole number).', [1 => $label]), 'integer');
1008           $qf->addRule($elementName . '_to', ts('%1 To must be an integer (whole number).', [1 => $label]), 'integer');
1009         }
1010         elseif ($widget == 'Text') {
1011           $qf->addRule($elementName, ts('%1 must be an integer (whole number).', [1 => $label]), 'integer');
1012         }
1013         break;
1014
1015       case 'Float':
1016         if ($field->is_search_range && $search) {
1017           $qf->addRule($elementName . '_from', ts('%1 From must be a number (with or without decimal point).', [1 => $label]), 'numeric');
1018           $qf->addRule($elementName . '_to', ts('%1 To must be a number (with or without decimal point).', [1 => $label]), 'numeric');
1019         }
1020         elseif ($widget == 'Text') {
1021           $qf->addRule($elementName, ts('%1 must be a number (with or without decimal point).', [1 => $label]), 'numeric');
1022         }
1023         break;
1024
1025       case 'Money':
1026         if ($field->is_search_range && $search) {
1027           $qf->addRule($elementName . '_from', ts('%1 From must in proper money format. (decimal point/comma/space is allowed).', [1 => $label]), 'money');
1028           $qf->addRule($elementName . '_to', ts('%1 To must in proper money format. (decimal point/comma/space is allowed).', [1 => $label]), 'money');
1029         }
1030         elseif ($widget == 'Text') {
1031           $qf->addRule($elementName, ts('%1 must be in proper money format. (decimal point/comma/space is allowed).', [1 => $label]), 'money');
1032         }
1033         break;
1034
1035       case 'Link':
1036         $element->setAttribute('class', "url");
1037         $qf->addRule($elementName, ts('Enter a valid web address beginning with \'http://\' or \'https://\'.'), 'wikiURL');
1038         break;
1039     }
1040     if ($field->is_view && !$search) {
1041       $qf->freeze($elementName);
1042     }
1043     return $element;
1044   }
1045
1046   /**
1047    * Take a string of HTML element attributes and turn it into an associative
1048    * array.
1049    *
1050    * @param string $attrString
1051    *   The attributes as a string, e.g. `rows=3 cols=40`.
1052    *
1053    * @return array
1054    *   The attributes as an array, e.g. `['rows' => 3, 'cols' => 40]`.
1055    */
1056   public static function attributesFromString($attrString) {
1057     $attributes = [];
1058     foreach (explode(' ', $attrString) as $at) {
1059       if (strpos($at, '=')) {
1060         list($k, $v) = explode('=', $at);
1061         $attributes[$k] = trim($v, ' "');
1062       }
1063     }
1064     return $attributes;
1065   }
1066
1067   /**
1068    * Delete the Custom Field.
1069    *
1070    * @param object $field
1071    *   The field object.
1072    */
1073   public static function deleteField($field) {
1074     CRM_Utils_System::flushCache();
1075
1076     // first delete the custom option group and values associated with this field
1077     if ($field->option_group_id) {
1078       //check if option group is related to any other field, if
1079       //not delete the option group and related option values
1080       self::checkOptionGroup($field->option_group_id);
1081     }
1082     // next drop the column from the custom value table
1083     self::createField($field, 'delete');
1084
1085     $field->delete();
1086     CRM_Core_BAO_UFField::delUFField($field->id);
1087     CRM_Core_BAO_Mapping::removeFieldFromMapping('custom_' . $field->id);
1088     CRM_Utils_Weight::correctDuplicateWeights('CRM_Core_DAO_CustomField');
1089
1090     CRM_Utils_Hook::post('delete', 'CustomField', $field->id, $field);
1091   }
1092
1093   /**
1094    * @param string|int|array|null $value
1095    * @param CRM_Core_BAO_CustomField|int|array|string $field
1096    * @param int $entityId
1097    *
1098    * @return string
1099    *
1100    * @throws \CRM_Core_Exception
1101    */
1102   public static function displayValue($value, $field, $entityId = NULL) {
1103     $field = is_array($field) ? $field['id'] : $field;
1104     $fieldId = is_object($field) ? $field->id : (int) str_replace('custom_', '', $field);
1105
1106     if (!$fieldId) {
1107       throw new CRM_Core_Exception('CRM_Core_BAO_CustomField::displayValue requires a field id');
1108     }
1109
1110     if (!is_a($field, 'CRM_Core_BAO_CustomField')) {
1111       $field = self::getFieldObject($fieldId);
1112     }
1113
1114     $fieldInfo = ['options' => $field->getOptions()] + (array) $field;
1115
1116     $displayValue = self::formatDisplayValue($value, $fieldInfo, $entityId);
1117
1118     // Call hook to alter display value.
1119     CRM_Utils_Hook::alterCustomFieldDisplayValue($displayValue, $value, $entityId, $fieldInfo);
1120
1121     return $displayValue;
1122   }
1123
1124   /**
1125    * Lower-level logic for rendering a custom field value
1126    *
1127    * @param string|array $value
1128    * @param array $field
1129    * @param int|null $entityId
1130    *
1131    * @return string
1132    */
1133   private static function formatDisplayValue($value, $field, $entityId = NULL) {
1134
1135     if (self::isSerialized($field) && !is_array($value)) {
1136       $value = CRM_Utils_Array::explodePadded($value);
1137     }
1138     // CRM-12989 fix
1139     if ($field['html_type'] == 'CheckBox' && $value) {
1140       $value = CRM_Utils_Array::convertCheckboxFormatToArray($value);
1141     }
1142
1143     $display = is_array($value) ? implode(', ', $value) : (string) $value;
1144
1145     switch ($field['html_type']) {
1146
1147       case 'Select':
1148       case 'Autocomplete-Select':
1149       case 'Radio':
1150       case 'CheckBox':
1151         if ($field['data_type'] == 'ContactReference' && (is_array($value) || is_numeric($value))) {
1152           // Issue #2939 - guard against passing empty values to CRM_Core_DAO::getFieldValue(), which would throw an exception
1153           if (empty($value)) {
1154             $display = '';
1155           }
1156           else {
1157             $displayNames = [];
1158             foreach ((array) $value as $contactId) {
1159               $displayNames[] = CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_Contact', $contactId, 'display_name');
1160             }
1161             $display = implode(', ', $displayNames);
1162           }
1163         }
1164         elseif ($field['data_type'] == 'ContactReference') {
1165           $display = $value;
1166         }
1167         elseif (is_array($value)) {
1168           $v = [];
1169           foreach ($value as $key => $val) {
1170             $v[] = $field['options'][$val] ?? NULL;
1171           }
1172           $display = implode(', ', $v);
1173         }
1174         else {
1175           $display = CRM_Utils_Array::value($value, $field['options'], '');
1176           // For float type (see Number and Money) $value would be decimal like
1177           // 1.00 (because it is stored in db as decimal), while options array
1178           // key would be integer like 1. In this case expression on line above
1179           // would return empty string (default value), despite the fact that
1180           // matching option exists in the array.
1181           // In such cases we could just get intval($value) and fetch matching
1182           // option again, but this would not work if key is float like 5.6.
1183           // So we need to truncate trailing zeros to make it work as expected.
1184           if ($display === '' && strpos(($value ?? ''), '.') !== FALSE) {
1185             // Use round() to truncate trailing zeros, e.g:
1186             // 10.00 -> 10, 10.60 -> 10.6, 10.69 -> 10.69.
1187             $value = (string) round($value, 5);
1188             $display = $field['options'][$value] ?? '';
1189           }
1190         }
1191         break;
1192
1193       case 'Select Date':
1194         $customFormat = NULL;
1195
1196         // FIXME: Are there any legitimate reasons why $value would be an array?
1197         // Or should we throw an exception here if it is?
1198         $value = is_array($value) ? CRM_Utils_Array::first($value) : $value;
1199
1200         $actualPHPFormats = CRM_Utils_Date::datePluginToPHPFormats();
1201         $format = $field['date_format'] ?? NULL;
1202
1203         if ($format) {
1204           if (array_key_exists($format, $actualPHPFormats)) {
1205             $customTimeFormat = (array) $actualPHPFormats[$format];
1206             switch (CRM_Utils_Array::value('time_format', $field)) {
1207               case 1:
1208                 $customTimeFormat[] = 'g:iA';
1209                 break;
1210
1211               case 2:
1212                 $customTimeFormat[] = 'G:i';
1213                 break;
1214
1215               default:
1216                 //If time is not selected remove time from value.
1217                 $value = $value ? date('Y-m-d', strtotime($value)) : '';
1218             }
1219             $customFormat = implode(" ", $customTimeFormat);
1220           }
1221         }
1222         $display = CRM_Utils_Date::processDate($value, NULL, FALSE, $customFormat);
1223         break;
1224
1225       case 'File':
1226         // In the context of displaying a profile, show file/image
1227         if ($value) {
1228           if ($entityId) {
1229             if (CRM_Utils_Rule::positiveInteger($value)) {
1230               $fileId = $value;
1231             }
1232             else {
1233               $fileId = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_File', $value, 'id', 'uri');
1234             }
1235             $url = self::getFileURL($entityId, $field['id'], $fileId);
1236             if ($url) {
1237               $display = $url['file_url'];
1238             }
1239           }
1240           else {
1241             // In other contexts show a paperclip icon
1242             if (CRM_Utils_Rule::integer($value)) {
1243               $icons = CRM_Core_BAO_File::paperIconAttachment('*', $value);
1244
1245               $file_description = '';
1246               try {
1247                 $file_description = civicrm_api3('File', 'getvalue', ['return' => "description", 'id' => $value]);
1248               }
1249               catch (CiviCRM_API3_Exception $dontcare) {
1250                 // don't care
1251               }
1252
1253               $display = "{$icons[$value]}{$file_description}";
1254             }
1255             else {
1256               //CRM-18396, if filename is passed instead
1257               $display = $value;
1258             }
1259           }
1260         }
1261         break;
1262
1263       case 'Link':
1264         $display = $display ? "<a href=\"$display\" target=\"_blank\">$display</a>" : $display;
1265         break;
1266
1267       case 'TextArea':
1268         $display = nl2br($display);
1269         break;
1270
1271       case 'Text':
1272         if ($field['data_type'] == 'Money' && isset($value)) {
1273           // $value can also be an array(while using IN operator from search builder or api).
1274           foreach ((array) $value as $val) {
1275             $disp[] = CRM_Utils_Money::formatLocaleNumericRoundedForDefaultCurrency($val);
1276           }
1277           $display = implode(', ', $disp);
1278         }
1279         elseif ($field['data_type'] == 'Float' && isset($value)) {
1280           // $value can also be an array(while using IN operator from search builder or api).
1281           foreach ((array) $value as $val) {
1282             $disp[] = CRM_Utils_Number::formatLocaleNumeric($val);
1283           }
1284           $display = implode(', ', $disp);
1285         }
1286         break;
1287     }
1288     return $display;
1289   }
1290
1291   /**
1292    * Set default values for custom data used in profile.
1293    *
1294    * @param int $customFieldId
1295    *   Custom field id.
1296    * @param string $elementName
1297    *   Custom field name.
1298    * @param array $defaults
1299    *   Associated array of fields.
1300    * @param int $contactId
1301    *   Contact id.
1302    * @param int $mode
1303    *   Profile mode.
1304    * @param mixed $value
1305    *   If passed - dont fetch value from db,.
1306    *                               just format the given value
1307    */
1308   public static function setProfileDefaults(
1309     $customFieldId,
1310     $elementName,
1311     &$defaults,
1312     $contactId = NULL,
1313     $mode = NULL,
1314     $value = NULL
1315   ) {
1316     //get the type of custom field
1317     $customField = new CRM_Core_BAO_CustomField();
1318     $customField->id = $customFieldId;
1319     $customField->find(TRUE);
1320
1321     if (!$contactId) {
1322       if ($mode == CRM_Profile_Form::MODE_CREATE) {
1323         $value = $customField->default_value;
1324       }
1325     }
1326     else {
1327       if (!isset($value)) {
1328         $info = self::getTableColumnGroup($customFieldId);
1329         $query = "SELECT {$info[0]}.{$info[1]} as value FROM {$info[0]} WHERE {$info[0]}.entity_id = {$contactId}";
1330         $result = CRM_Core_DAO::executeQuery($query);
1331         if ($result->fetch()) {
1332           $value = $result->value;
1333         }
1334       }
1335
1336       if ($customField->data_type == 'Country') {
1337         if (!$value) {
1338           $config = CRM_Core_Config::singleton();
1339           if ($config->defaultContactCountry) {
1340             $value = CRM_Core_BAO_Country::defaultContactCountry();
1341           }
1342         }
1343       }
1344     }
1345
1346     //set defaults if mode is registration
1347     if (!trim($value) &&
1348       ($value !== 0) &&
1349       (!in_array($mode, [CRM_Profile_Form::MODE_EDIT, CRM_Profile_Form::MODE_SEARCH]))
1350     ) {
1351       $value = $customField->default_value;
1352     }
1353
1354     if ($customField->data_type == 'Money' && isset($value)) {
1355       $value = number_format($value, 2);
1356     }
1357     if (self::isSerialized($customField) && $value) {
1358       $customOption = CRM_Core_BAO_CustomOption::getCustomOption($customFieldId, FALSE);
1359       $defaults[$elementName] = [];
1360       $checkedValue = CRM_Utils_Array::explodePadded($value);
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           else {
1367             $defaults[$elementName][$val['value']] = $val['value'];
1368           }
1369         }
1370       }
1371     }
1372     else {
1373       $defaults[$elementName] = $value;
1374     }
1375   }
1376
1377   /**
1378    * Get file url.
1379    *
1380    * @param int $contactID
1381    * @param int $cfID
1382    * @param int $fileID
1383    * @param bool $absolute
1384    *
1385    * @param string $multiRecordWhereClause
1386    *
1387    * @return array
1388    */
1389   public static function getFileURL($contactID, $cfID, $fileID = NULL, $absolute = FALSE, $multiRecordWhereClause = NULL) {
1390     if ($contactID) {
1391       if (!$fileID) {
1392         $params = ['id' => $cfID];
1393         $defaults = [];
1394         CRM_Core_DAO::commonRetrieve('CRM_Core_DAO_CustomField', $params, $defaults);
1395         $columnName = $defaults['column_name'];
1396
1397         //table name of custom data
1398         $tableName = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_CustomGroup',
1399           $defaults['custom_group_id'],
1400           'table_name', 'id'
1401         );
1402
1403         //query to fetch id from civicrm_file
1404         if ($multiRecordWhereClause) {
1405           $query = "SELECT {$columnName} FROM {$tableName} where entity_id = {$contactID} AND {$multiRecordWhereClause}";
1406         }
1407         else {
1408           $query = "SELECT {$columnName} FROM {$tableName} where entity_id = {$contactID}";
1409         }
1410         $fileID = CRM_Core_DAO::singleValueQuery($query);
1411       }
1412
1413       $result = [];
1414       if ($fileID) {
1415         $fileType = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_File',
1416           $fileID,
1417           'mime_type',
1418           'id'
1419         );
1420         $result['file_id'] = $fileID;
1421
1422         if ($fileType == 'image/jpeg' ||
1423           $fileType == 'image/pjpeg' ||
1424           $fileType == 'image/gif' ||
1425           $fileType == 'image/x-png' ||
1426           $fileType == 'image/png'
1427         ) {
1428           $entityId = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_EntityFile',
1429             $fileID,
1430             'entity_id',
1431             'file_id'
1432           );
1433           list($path) = CRM_Core_BAO_File::path($fileID, $entityId);
1434           $fileHash = CRM_Core_BAO_File::generateFileHash($entityId, $fileID);
1435           $url = CRM_Utils_System::url('civicrm/file',
1436             "reset=1&id=$fileID&eid=$entityId&fcs=$fileHash",
1437             $absolute, NULL, TRUE, TRUE
1438           );
1439           $result['file_url'] = CRM_Utils_File::getFileURL($path, $fileType, $url);
1440         }
1441         // for non image files
1442         else {
1443           $uri = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_File',
1444             $fileID,
1445             'uri'
1446           );
1447           $fileHash = CRM_Core_BAO_File::generateFileHash($contactID, $fileID);
1448           $url = CRM_Utils_System::url('civicrm/file',
1449             "reset=1&id=$fileID&eid=$contactID&fcs=$fileHash",
1450             $absolute, NULL, TRUE, TRUE
1451           );
1452           $result['file_url'] = CRM_Utils_File::getFileURL($uri, $fileType, $url);
1453         }
1454       }
1455       return $result;
1456     }
1457   }
1458
1459   /**
1460    * Format custom fields before inserting.
1461    *
1462    * @param int $customFieldId
1463    *   Custom field id.
1464    * @param array $customFormatted
1465    *   Formatted array.
1466    * @param mixed $value
1467    *   Value of custom field.
1468    * @param string $customFieldExtend
1469    *   Custom field extends.
1470    * @param int $customValueId
1471    *   Custom option value id.
1472    * @param int $entityId
1473    *   Entity id (contribution, membership...).
1474    * @param bool $inline
1475    *   Consider inline custom groups only.
1476    * @param bool $checkPermission
1477    *   If false, do not include permissioning clause.
1478    * @param bool $includeViewOnly
1479    *   If true, fields marked 'View Only' are included. Required for APIv3.
1480    *
1481    * @return array|NULL
1482    *   formatted custom field array
1483    */
1484   public static function formatCustomField(
1485     $customFieldId, &$customFormatted, $value,
1486     $customFieldExtend, $customValueId = NULL,
1487     $entityId = NULL,
1488     $inline = FALSE,
1489     $checkPermission = TRUE,
1490     $includeViewOnly = FALSE
1491   ) {
1492     //get the custom fields for the entity
1493     //subtype and basic type
1494     $customDataSubType = NULL;
1495     if ($customFieldExtend) {
1496       // This is the case when getFieldsForImport() requires fields
1497       // of subtype and its parent.CRM-5143
1498       // CRM-16065 - Custom field set data not being saved if contact has more than one contact sub type
1499       $customDataSubType = array_intersect(CRM_Contact_BAO_ContactType::subTypes(), (array) $customFieldExtend);
1500       if (!empty($customDataSubType) && is_array($customDataSubType)) {
1501         $customFieldExtend = CRM_Contact_BAO_ContactType::getBasicType($customDataSubType);
1502         if (is_array($customFieldExtend)) {
1503           $customFieldExtend = array_unique(array_values($customFieldExtend));
1504         }
1505       }
1506     }
1507
1508     $customFields = CRM_Core_BAO_CustomField::getFields($customFieldExtend,
1509       FALSE,
1510       $inline,
1511       $customDataSubType,
1512       NULL,
1513       FALSE,
1514       FALSE,
1515       $checkPermission ? CRM_Core_Permission::EDIT : FALSE
1516     );
1517
1518     if (!array_key_exists($customFieldId, $customFields)) {
1519       return NULL;
1520     }
1521
1522     // return if field is a 'code' field
1523     if (!$includeViewOnly && !empty($customFields[$customFieldId]['is_view'])) {
1524       return NULL;
1525     }
1526
1527     list($tableName, $columnName, $groupID) = self::getTableColumnGroup($customFieldId);
1528
1529     if (!$customValueId &&
1530       // we always create new entites for is_multiple unless specified
1531       !$customFields[$customFieldId]['is_multiple'] &&
1532       $entityId
1533     ) {
1534       $query = "
1535 SELECT id
1536   FROM $tableName
1537  WHERE entity_id={$entityId}";
1538
1539       $customValueId = CRM_Core_DAO::singleValueQuery($query);
1540     }
1541
1542     //fix checkbox, now check box always submits values
1543     if ($customFields[$customFieldId]['html_type'] == 'CheckBox') {
1544       if ($value) {
1545         // Note that only during merge this is not an array, and you can directly use value
1546         if (is_array($value)) {
1547           $selectedValues = [];
1548           foreach ($value as $selId => $val) {
1549             if ($val) {
1550               $selectedValues[] = $selId;
1551             }
1552           }
1553           if (!empty($selectedValues)) {
1554             $value = CRM_Core_DAO::VALUE_SEPARATOR . implode(CRM_Core_DAO::VALUE_SEPARATOR,
1555                 $selectedValues
1556               ) . CRM_Core_DAO::VALUE_SEPARATOR;
1557           }
1558           else {
1559             $value = '';
1560           }
1561         }
1562       }
1563     }
1564     elseif (self::isSerialized($customFields[$customFieldId])) {
1565       // Select2 v3 returns a comma-separated string.
1566       if ($customFields[$customFieldId]['html_type'] == 'Autocomplete-Select' && is_string($value)) {
1567         $value = explode(',', $value);
1568       }
1569
1570       $value = $value ? CRM_Utils_Array::implodePadded($value) : '';
1571     }
1572
1573     if (self::isSerialized($customFields[$customFieldId]) &&
1574       $customFields[$customFieldId]['data_type'] == 'String' &&
1575       !empty($customFields[$customFieldId]['text_length']) &&
1576       !empty($value)
1577     ) {
1578       // lets make sure that value is less than the length, else we'll
1579       // be losing some data, CRM-7481
1580       if (strlen($value) >= $customFields[$customFieldId]['text_length']) {
1581         // need to do a few things here
1582
1583         // 1. lets find a new length
1584         $newLength = $customFields[$customFieldId]['text_length'];
1585         $minLength = strlen($value);
1586         while ($newLength < $minLength) {
1587           $newLength = $newLength * 2;
1588         }
1589
1590         // set the custom field meta data to have a length larger than value
1591         // alter the custom value table column to match this length
1592         CRM_Core_BAO_SchemaHandler::alterFieldLength($customFieldId, $tableName, $columnName, $newLength);
1593       }
1594     }
1595
1596     $date = NULL;
1597     if ($customFields[$customFieldId]['data_type'] == 'Date') {
1598       if (!CRM_Utils_System::isNull($value)) {
1599         $format = $customFields[$customFieldId]['date_format'];
1600         $date = CRM_Utils_Date::processDate($value, NULL, FALSE, 'YmdHis', $format);
1601       }
1602       $value = $date;
1603     }
1604
1605     if ($customFields[$customFieldId]['data_type'] == 'Float' ||
1606       $customFields[$customFieldId]['data_type'] == 'Money'
1607     ) {
1608       if (!$value) {
1609         $value = 0;
1610       }
1611
1612       if ($customFields[$customFieldId]['data_type'] == 'Money') {
1613         $value = CRM_Utils_Rule::cleanMoney($value);
1614       }
1615     }
1616
1617     if (($customFields[$customFieldId]['data_type'] == 'StateProvince' ||
1618         $customFields[$customFieldId]['data_type'] == 'Country'
1619       ) &&
1620       empty($value)
1621     ) {
1622       // CRM-3415
1623       $value = 0;
1624     }
1625
1626     $fileID = NULL;
1627
1628     if ($customFields[$customFieldId]['data_type'] == 'File') {
1629       if (empty($value)) {
1630         return;
1631       }
1632
1633       $config = CRM_Core_Config::singleton();
1634
1635       // If we are already passing the file id as a value then retrieve and set the file data
1636       if (CRM_Utils_Rule::integer($value)) {
1637         $fileDAO = new CRM_Core_DAO_File();
1638         $fileDAO->id = $value;
1639         $fileDAO->find(TRUE);
1640         if ($fileDAO->N) {
1641           $fileID = $value;
1642           $fName = $fileDAO->uri;
1643           $mimeType = $fileDAO->mime_type;
1644         }
1645       }
1646       elseif (empty($value['name'])) {
1647         // Happens when calling the API to update custom fields values, but the filename
1648         // is empty, for an existing entity (in a specific case, was from a d7-webform
1649         // that was updating a relationship with a File customfield, so $value['id'] was
1650         // not empty, but the filename was empty.
1651         return;
1652       }
1653       else {
1654         $fName = $value['name'];
1655         $mimeType = $value['type'];
1656       }
1657
1658       $filename = pathinfo($fName, PATHINFO_BASENAME);
1659
1660       // rename this file to go into the secure directory only if
1661       // user has uploaded new file not existing verfied on the basis of $fileID
1662       if (empty($fileID) && !rename($fName, $config->customFileUploadDir . $filename)) {
1663         CRM_Core_Error::statusBounce(ts('Could not move custom file to custom upload directory'));
1664       }
1665
1666       if ($customValueId && empty($fileID)) {
1667         $query = "
1668 SELECT $columnName
1669   FROM $tableName
1670  WHERE id = %1";
1671         $params = [1 => [$customValueId, 'Integer']];
1672         $fileID = CRM_Core_DAO::singleValueQuery($query, $params);
1673       }
1674
1675       $fileDAO = new CRM_Core_DAO_File();
1676
1677       if ($fileID) {
1678         $fileDAO->id = $fileID;
1679       }
1680
1681       $fileDAO->uri = $filename;
1682       $fileDAO->mime_type = $mimeType;
1683       $fileDAO->upload_date = date('YmdHis');
1684       $fileDAO->save();
1685       $fileID = $fileDAO->id;
1686       $value = $filename;
1687     }
1688
1689     if (!is_array($customFormatted)) {
1690       $customFormatted = [];
1691     }
1692
1693     if (!array_key_exists($customFieldId, $customFormatted)) {
1694       $customFormatted[$customFieldId] = [];
1695     }
1696
1697     $index = -1;
1698     if ($customValueId) {
1699       $index = $customValueId;
1700     }
1701
1702     if (!array_key_exists($index, $customFormatted[$customFieldId])) {
1703       $customFormatted[$customFieldId][$index] = [];
1704     }
1705     $customFormatted[$customFieldId][$index] = [
1706       'id' => $customValueId > 0 ? $customValueId : NULL,
1707       'value' => $value,
1708       'type' => $customFields[$customFieldId]['data_type'],
1709       'custom_field_id' => $customFieldId,
1710       'custom_group_id' => $groupID,
1711       'table_name' => $tableName,
1712       'column_name' => $columnName,
1713       'file_id' => $fileID,
1714       // is_multiple refers to the custom group, serialize refers to the field.
1715       'is_multiple' => $customFields[$customFieldId]['is_multiple'],
1716       'serialize' => $customFields[$customFieldId]['serialize'],
1717     ];
1718
1719     //we need to sort so that custom fields are created in the order of entry
1720     krsort($customFormatted[$customFieldId]);
1721     return $customFormatted;
1722   }
1723
1724   /**
1725    * Get default custom table schema.
1726    *
1727    * @param array $params
1728    *
1729    * @return array
1730    */
1731   public static function defaultCustomTableSchema($params) {
1732     // add the id and extends_id
1733     $collation = CRM_Core_BAO_SchemaHandler::getInUseCollation();
1734     $characterSet = 'utf8';
1735     if (stripos($collation, 'utf8mb4') !== FALSE) {
1736       $characterSet = 'utf8mb4';
1737     }
1738     $table = [
1739       'name' => $params['name'],
1740       'is_multiple' => $params['is_multiple'],
1741       'attributes' => "ENGINE=InnoDB DEFAULT CHARACTER SET {$characterSet} COLLATE {$collation}",
1742       'fields' => [
1743         [
1744           'name' => 'id',
1745           'type' => 'int unsigned',
1746           'primary' => TRUE,
1747           'required' => TRUE,
1748           'attributes' => 'AUTO_INCREMENT',
1749           'comment' => 'Default MySQL primary key',
1750         ],
1751         [
1752           'name' => 'entity_id',
1753           'type' => 'int unsigned',
1754           'required' => TRUE,
1755           'comment' => 'Table that this extends',
1756           'fk_table_name' => $params['extends_name'],
1757           'fk_field_name' => 'id',
1758           'fk_attributes' => 'ON DELETE CASCADE',
1759         ],
1760       ],
1761     ];
1762
1763     // If on MySQL 5.6 include ROW_FORMAT=DYNAMIC to fix unit tests
1764     $databaseVersion = CRM_Utils_SQL::getDatabaseVersion();
1765     if (version_compare($databaseVersion, '5.7', '<') && version_compare($databaseVersion, '5.6', '>=')) {
1766       $table['attributes'] = $table['attributes'] . ' ROW_FORMAT=DYNAMIC';
1767     }
1768
1769     if (!$params['is_multiple']) {
1770       $table['indexes'] = [
1771         [
1772           'unique' => TRUE,
1773           'field_name_1' => 'entity_id',
1774         ],
1775       ];
1776     }
1777     return $table;
1778   }
1779
1780   /**
1781    * Create custom field.
1782    *
1783    * @param CRM_Core_DAO_CustomField $field
1784    * @param string $operation
1785    */
1786   public static function createField($field, $operation) {
1787     $sql = str_repeat(' ', 8);
1788     $tableName = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_CustomGroup', $field->custom_group_id, 'table_name');
1789     $sql .= "ALTER TABLE " . $tableName;
1790     $sql .= self::getAlterFieldSQL($field, $operation);
1791
1792     // CRM-7007: do not i18n-rewrite this query
1793     CRM_Core_DAO::executeQuery($sql, [], TRUE, NULL, FALSE, FALSE);
1794
1795     $config = CRM_Core_Config::singleton();
1796     if ($config->logging) {
1797       // CRM-16717 not sure why this was originally limited to add.
1798       // For example custom tables can have field length changes - which need to flow through to logging.
1799       // Are there any modifies we DON'T was to call this function for (& shouldn't it be clever enough to cope?)
1800       if ($operation === 'add' || $operation === 'modify') {
1801         $logging = new CRM_Logging_Schema();
1802         $logging->fixSchemaDifferencesFor($tableName, [trim(strtoupper($operation)) => [$field->column_name]]);
1803       }
1804     }
1805
1806     Civi::service('sql_triggers')->rebuild($tableName, TRUE);
1807   }
1808
1809   /**
1810    * @param CRM_Core_DAO_CustomField $field
1811    * @param string $operation add|modify|delete
1812    *
1813    * @return bool
1814    */
1815   public static function getAlterFieldSQL($field, $operation) {
1816     $params = self::prepareCreateParams($field, $operation);
1817     // Let's suppress the required flag, since that can cause an sql issue... for unknown reasons since we are calling
1818     // a function only used by Custom Field creation...
1819     $params['required'] = FALSE;
1820     return CRM_Core_BAO_SchemaHandler::getFieldAlterSQL($params);
1821   }
1822
1823   /**
1824    * Get query to reformat existing values for a field when changing its serialize attribute
1825    *
1826    * @param CRM_Core_DAO_CustomField $field
1827    * @return string
1828    */
1829   private static function getAlterSerializeSQL($field) {
1830     $table = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_CustomGroup', $field->custom_group_id, 'table_name');
1831     $col = $field->column_name;
1832     $sp = CRM_Core_DAO::VALUE_SEPARATOR;
1833     if ($field->serialize) {
1834       return "UPDATE `$table` SET `$col` = CONCAT('$sp', `$col`, '$sp') WHERE `$col` IS NOT NULL AND `$col` NOT LIKE '$sp%' AND `$col` != ''";
1835     }
1836     else {
1837       return "UPDATE `$table` SET `$col` = SUBSTRING_INDEX(SUBSTRING(`$col`, 2), '$sp', 1) WHERE `$col` LIKE '$sp%'";
1838     }
1839   }
1840
1841   /**
1842    * Determine whether it would be safe to move a field.
1843    *
1844    * @param int $fieldID
1845    *   FK to civicrm_custom_field.
1846    * @param int $newGroupID
1847    *   FK to civicrm_custom_group.
1848    *
1849    * @return array
1850    *   array(string) or TRUE
1851    */
1852   public static function _moveFieldValidate($fieldID, $newGroupID) {
1853     $errors = [];
1854
1855     $field = new CRM_Core_DAO_CustomField();
1856     $field->id = $fieldID;
1857     if (!$field->find(TRUE)) {
1858       $errors['fieldID'] = ts('Invalid ID for custom field');
1859       return $errors;
1860     }
1861
1862     $oldGroup = new CRM_Core_DAO_CustomGroup();
1863     $oldGroup->id = $field->custom_group_id;
1864     if (!$oldGroup->find(TRUE)) {
1865       $errors['fieldID'] = ts('Invalid ID for old custom group');
1866       return $errors;
1867     }
1868
1869     $newGroup = new CRM_Core_DAO_CustomGroup();
1870     $newGroup->id = $newGroupID;
1871     if (!$newGroup->find(TRUE)) {
1872       $errors['newGroupID'] = ts('Invalid ID for new custom group');
1873       return $errors;
1874     }
1875
1876     $query = "
1877 SELECT     b.id
1878 FROM       civicrm_custom_field a
1879 INNER JOIN civicrm_custom_field b
1880 WHERE      a.id = %1
1881 AND        a.label = b.label
1882 AND        b.custom_group_id = %2
1883 ";
1884     $params = [
1885       1 => [$field->id, 'Integer'],
1886       2 => [$newGroup->id, 'Integer'],
1887     ];
1888     $count = CRM_Core_DAO::singleValueQuery($query, $params);
1889     if ($count > 0) {
1890       $errors['newGroupID'] = ts('A field of the same label exists in the destination group');
1891     }
1892
1893     $tableName = $oldGroup->table_name;
1894     $columnName = $field->column_name;
1895
1896     $query = "
1897 SELECT count(*)
1898 FROM   $tableName
1899 WHERE  $columnName is not null
1900 ";
1901     $count = CRM_Core_DAO::singleValueQuery($query);
1902     if ($count > 0) {
1903       $query = "
1904 SELECT extends
1905 FROM   civicrm_custom_group
1906 WHERE  id IN ( %1, %2 )
1907 ";
1908       $params = [
1909         1 => [$oldGroup->id, 'Integer'],
1910         2 => [$newGroup->id, 'Integer'],
1911       ];
1912
1913       $dao = CRM_Core_DAO::executeQuery($query, $params);
1914       $extends = [];
1915       while ($dao->fetch()) {
1916         $extends[] = $dao->extends;
1917       }
1918       if ($extends[0] != $extends[1]) {
1919         $errors['newGroupID'] = ts('The destination group extends a different entity type.');
1920       }
1921     }
1922
1923     return empty($errors) ? TRUE : $errors;
1924   }
1925
1926   /**
1927    * Move a custom data field from one group (table) to another.
1928    *
1929    * @param int $fieldID
1930    *   FK to civicrm_custom_field.
1931    * @param int $newGroupID
1932    *   FK to civicrm_custom_group.
1933    *
1934    * @throws CRM_Core_Exception
1935    */
1936   public static function moveField($fieldID, $newGroupID) {
1937     $validation = self::_moveFieldValidate($fieldID, $newGroupID);
1938     if (TRUE !== $validation) {
1939       throw new CRM_Core_Exception(implode(' ', $validation));
1940     }
1941     $field = new CRM_Core_DAO_CustomField();
1942     $field->id = $fieldID;
1943     $field->find(TRUE);
1944
1945     $newGroup = new CRM_Core_DAO_CustomGroup();
1946     $newGroup->id = $newGroupID;
1947     $newGroup->find(TRUE);
1948
1949     $oldGroup = new CRM_Core_DAO_CustomGroup();
1950     $oldGroup->id = $field->custom_group_id;
1951     $oldGroup->find(TRUE);
1952
1953     $add = clone$field;
1954     $add->custom_group_id = $newGroup->id;
1955     self::createField($add, 'add');
1956
1957     $sql = "INSERT INTO {$newGroup->table_name} (entity_id, `{$field->column_name}`)
1958             SELECT entity_id, `{$field->column_name}` FROM {$oldGroup->table_name}
1959             ON DUPLICATE KEY UPDATE `{$field->column_name}` = {$oldGroup->table_name}.`{$field->column_name}`
1960             ";
1961     CRM_Core_DAO::executeQuery($sql);
1962
1963     $del = clone$field;
1964     $del->custom_group_id = $oldGroup->id;
1965     self::createField($del, 'delete');
1966
1967     $add->save();
1968
1969     CRM_Utils_System::flushCache();
1970   }
1971
1972   /**
1973    * Create an option value for a custom field option group ID.
1974    *
1975    * @param array $params
1976    * @param string $value
1977    * @param \CRM_Core_DAO_OptionGroup $optionGroup
1978    * @param string $index
1979    * @param string $dataType
1980    */
1981   protected static function createOptionValue(&$params, $value, CRM_Core_DAO_OptionGroup $optionGroup, $index, $dataType) {
1982     if (strlen(trim($value))) {
1983       $optionValue = new CRM_Core_DAO_OptionValue();
1984       $optionValue->option_group_id = $optionGroup->id;
1985       $optionValue->label = $params['option_label'][$index];
1986       $optionValue->name = $params['option_name'][$index] ?? CRM_Utils_String::titleToVar($params['option_label'][$index]);
1987       switch ($dataType) {
1988         case 'Money':
1989           $optionValue->value = CRM_Utils_Rule::cleanMoney($value);
1990           break;
1991
1992         case 'Int':
1993           $optionValue->value = intval($value);
1994           break;
1995
1996         case 'Float':
1997           $optionValue->value = floatval($value);
1998           break;
1999
2000         default:
2001           $optionValue->value = trim($value);
2002       }
2003
2004       $optionValue->weight = $params['option_weight'][$index];
2005       $optionValue->is_active = $params['option_status'][$index] ?? FALSE;
2006       $optionValue->description = $params['option_description'][$index] ?? NULL;
2007       $optionValue->color = $params['option_color'][$index] ?? NULL;
2008       $optionValue->icon = $params['option_icon'][$index] ?? NULL;
2009       $optionValue->save();
2010     }
2011   }
2012
2013   /**
2014    * Prepare for the create operation.
2015    *
2016    * Munge params, create the option values if needed.
2017    *
2018    * This could be called by a single create or a batchCreate.
2019    *
2020    * @param array $params
2021    *
2022    * @return array
2023    */
2024   protected static function prepareCreate($params) {
2025     $op = empty($params['id']) ? 'create' : 'edit';
2026     CRM_Utils_Hook::pre($op, 'CustomField', CRM_Utils_Array::value('id', $params), $params);
2027     $params['is_append_field_id_to_column_name'] = !isset($params['column_name']);
2028     if ($op === 'create') {
2029       CRM_Core_DAO::setCreateDefaults($params, self::getDefaults());
2030       if (!isset($params['column_name'])) {
2031         // if add mode & column_name not present, calculate it.
2032         $params['column_name'] = strtolower(CRM_Utils_String::munge($params['label'], '_', 32));
2033       }
2034       if (!isset($params['name'])) {
2035         $params['name'] = CRM_Utils_String::munge($params['label'], '_', 64);
2036       }
2037     }
2038     else {
2039       $params['column_name'] = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_CustomField', $params['id'], 'column_name');
2040     }
2041
2042     $htmlType = $params['html_type'] ?? NULL;
2043     $dataType = $params['data_type'] ?? NULL;
2044
2045     if ($htmlType === 'Select Date' && empty($params['date_format'])) {
2046       $params['date_format'] = Civi::settings()->get('dateInputFormat');
2047     }
2048
2049     // Checkboxes are always serialized in current schema
2050     if ($htmlType == 'CheckBox') {
2051       $params['serialize'] = CRM_Core_DAO::SERIALIZE_SEPARATOR_BOOKEND;
2052     }
2053
2054     if (!empty($params['serialize'])) {
2055       if (isset($params['default_checkbox_option'])) {
2056         $defaultArray = [];
2057         foreach (array_keys($params['default_checkbox_option']) as $k => $v) {
2058           if ($params['option_value'][$v]) {
2059             $defaultArray[] = $params['option_value'][$v];
2060           }
2061         }
2062
2063         if (!empty($defaultArray)) {
2064           // also add the separator before and after the value per new convention (CRM-1604)
2065           $params['default_value'] = CRM_Utils_Array::implodePadded($defaultArray);
2066         }
2067       }
2068       else {
2069         if (!empty($params['default_option']) && isset($params['option_value'][$params['default_option']])) {
2070           $params['default_value'] = $params['option_value'][$params['default_option']];
2071         }
2072       }
2073     }
2074
2075     // create any option group & values if required
2076     $allowedOptionTypes = ['String', 'Int', 'Float', 'Money'];
2077     if ($htmlType !== 'Text' && in_array($dataType, $allowedOptionTypes, TRUE)) {
2078       //CRM-16659: if option_value then create an option group for this custom field.
2079       // An option_type of 2 would be a 'message' from the form layer not to handle
2080       // the option_values key. If not set then it is not ignored.
2081       $optionsType = (int) ($params['option_type'] ?? 0);
2082       if (($optionsType !== 2 && empty($params['id']))
2083         && (empty($params['option_group_id']) && !empty($params['option_value'])
2084         )
2085       ) {
2086         // first create an option group for this custom group
2087         $customGroupTitle = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_CustomGroup', $params['custom_group_id'], 'title');
2088         $optionGroup = CRM_Core_DAO_OptionGroup::writeRecord([
2089           'title' => "$customGroupTitle :: {$params['label']}",
2090           // Don't set reserved as it's not a built-in option group and may be useful for other custom fields.
2091           'is_reserved' => 0,
2092           'data_type' => $dataType,
2093         ]);
2094         $params['option_group_id'] = $optionGroup->id;
2095         if (!empty($params['option_value']) && is_array($params['option_value'])) {
2096           foreach ($params['option_value'] as $k => $v) {
2097             self::createOptionValue($params, $v, $optionGroup, $k, $dataType);
2098           }
2099         }
2100       }
2101     }
2102
2103     // Remove option group IDs from fields changed to Text html_type.
2104     if ($htmlType == 'Text') {
2105       $params['option_group_id'] = '';
2106     }
2107
2108     // check for orphan option groups
2109     if (!empty($params['option_group_id'])) {
2110       if (!empty($params['id'])) {
2111         self::fixOptionGroups($params['id'], $params['option_group_id']);
2112       }
2113
2114       // if we do not have a default value
2115       // retrieve it from one of the other custom fields which use this option group
2116       if (empty($params['default_value'])) {
2117         //don't insert only value separator as default value, CRM-4579
2118         $defaultValue = self::getOptionGroupDefault($params['option_group_id'], !empty($params['serialize']));
2119
2120         if ($defaultValue !== NULL && !CRM_Utils_System::isNull(explode(CRM_Core_DAO::VALUE_SEPARATOR, $defaultValue))) {
2121           $params['default_value'] = $defaultValue;
2122         }
2123       }
2124     }
2125
2126     // Set default textarea attributes
2127     if ($op == 'create' && !isset($params['attributes']) && $htmlType == 'TextArea') {
2128       $params['attributes'] = 'rows=4, cols=60';
2129     }
2130     return $params;
2131   }
2132
2133   /**
2134    * Create database entry for custom field and related option groups.
2135    *
2136    * @param array $params
2137    *
2138    * @return CRM_Core_DAO_CustomField
2139    */
2140   protected static function createCustomFieldRecord($params) {
2141     $transaction = new CRM_Core_Transaction();
2142     $params = self::prepareCreate($params);
2143
2144     $customField = new CRM_Core_DAO_CustomField();
2145     $customField->copyValues($params);
2146     $customField->save();
2147
2148     //create/drop the index when we toggle the is_searchable flag
2149     $op = empty($params['id']) ? 'add' : 'modify';
2150     if ($op !== 'modify') {
2151       if ($params['is_append_field_id_to_column_name']) {
2152         $params['column_name'] .= "_{$customField->id}";
2153       }
2154       $customField->column_name = $params['column_name'];
2155       $customField->save();
2156     }
2157
2158     // complete transaction - note that any table alterations include an implicit commit so this is largely meaningless.
2159     $transaction->commit();
2160
2161     // make sure all values are present in the object for further processing
2162     $customField->find(TRUE);
2163
2164     return $customField;
2165   }
2166
2167   /**
2168    * @param $params
2169    * @return int|null
2170    */
2171   protected static function getChangeSerialize($params) {
2172     if (isset($params['serialize']) && !empty($params['id'])) {
2173       if ($params['serialize'] != CRM_Core_DAO::getFieldValue('CRM_Core_DAO_CustomField', $params['id'], 'serialize')) {
2174         return (int) $params['serialize'];
2175       }
2176     }
2177     return NULL;
2178   }
2179
2180   /**
2181    * Move custom data from one contact to another.
2182    *
2183    * This is currently the start of a refactoring. The theory is each
2184    * entity could have a 'move' function with a DAO default one to fall back on.
2185    *
2186    * At the moment this only does a small part of the process - ie deleting a file field that
2187    * is about to be overwritten. However, the goal is the whole process around the move for
2188    * custom data should be in here.
2189    *
2190    * This is currently called by the merge class but it makes sense that api could
2191    * expose move actions as moving (e.g) contributions feels like a common
2192    * ask that should be handled by the form layer.
2193    *
2194    * @param int $oldContactID
2195    * @param int $newContactID
2196    * @param int[] $fieldIDs
2197    *   Optional list field ids to move.
2198    *
2199    * @throws \CiviCRM_API3_Exception
2200    * @throws \Exception
2201    */
2202   public function move($oldContactID, $newContactID, $fieldIDs) {
2203     if (empty($fieldIDs)) {
2204       return;
2205     }
2206     $fields = civicrm_api3('CustomField', 'get', ['id' => ['IN' => $fieldIDs], 'return' => ['custom_group_id.is_multiple', 'custom_group_id.table_name', 'column_name', 'data_type'], 'options' => ['limit' => 0]])['values'];
2207     $return = [];
2208     foreach ($fieldIDs as $fieldID) {
2209       $return[] = 'custom_' . $fieldID;
2210     }
2211     $oldContact = civicrm_api3('Contact', 'getsingle', ['id' => $oldContactID, 'return' => $return]);
2212     $newContact = civicrm_api3('Contact', 'getsingle', ['id' => $newContactID, 'return' => $return]);
2213
2214     // The moveAllBelongings function has functionality to move custom fields. It doesn't work very well...
2215     // @todo handle all fields here but more immediately Country since that is broken at the moment.
2216     $fieldTypesNotHandledInMergeAttempt = ['File'];
2217     foreach ($fields as $field) {
2218       $isMultiple = !empty($field['custom_group_id.is_multiple']);
2219       if ($field['data_type'] === 'File' && !$isMultiple) {
2220         if (!empty($oldContact['custom_' . $field['id']]) && !empty($newContact['custom_' . $field['id']])) {
2221           CRM_Core_BAO_File::deleteFileReferences($oldContact['custom_' . $field['id']], $oldContactID, $field['id']);
2222         }
2223         if (!empty($oldContact['custom_' . $field['id']])) {
2224           CRM_Core_DAO::executeQuery("
2225             UPDATE civicrm_entity_file
2226             SET entity_id = $newContactID
2227             WHERE file_id = {$oldContact['custom_' . $field['id']]}"
2228           );
2229         }
2230       }
2231       if (in_array($field['data_type'], $fieldTypesNotHandledInMergeAttempt) && !$isMultiple) {
2232         CRM_Core_DAO::executeQuery(
2233           "INSERT INTO {$field['custom_group_id.table_name']} (entity_id, `{$field['column_name']}`)
2234           VALUES ($newContactID, {$oldContact['custom_' . $field['id']]})
2235           ON DUPLICATE KEY UPDATE
2236           `{$field['column_name']}` = {$oldContact['custom_' . $field['id']]}
2237         ");
2238       }
2239     }
2240   }
2241
2242   /**
2243    * Get the database table name and column name for a custom field.
2244    *
2245    * @param int $fieldID
2246    *   The fieldID of the custom field.
2247    * @param bool $force
2248    *   Force the sql to be run again (primarily used for tests).
2249    *
2250    * @return array
2251    *   fatal is fieldID does not exists, else array of tableName, columnName
2252    * @throws \CRM_Core_Exception
2253    */
2254   public static function getTableColumnGroup($fieldID, $force = FALSE) {
2255     $cacheKey = "CRM_Core_DAO_CustomField_CustomGroup_TableColumn_{$fieldID}";
2256     $cache = CRM_Utils_Cache::singleton();
2257     $fieldValues = $cache->get($cacheKey);
2258     if (empty($fieldValues) || $force) {
2259       $query = "
2260 SELECT cg.table_name, cf.column_name, cg.id
2261 FROM   civicrm_custom_group cg,
2262        civicrm_custom_field cf
2263 WHERE  cf.custom_group_id = cg.id
2264 AND    cf.id = %1";
2265       $params = [1 => [$fieldID, 'Integer']];
2266       $dao = CRM_Core_DAO::executeQuery($query, $params);
2267
2268       if (!$dao->fetch()) {
2269         throw new CRM_Core_Exception("Cannot find table and column information for Custom Field " . $fieldID);
2270       }
2271       $fieldValues = [$dao->table_name, $dao->column_name, $dao->id];
2272       $cache->set($cacheKey, $fieldValues);
2273     }
2274     return $fieldValues;
2275   }
2276
2277   /**
2278    * Get custom option groups.
2279    *
2280    * @deprecated Use the API OptionGroup.get
2281    *
2282    * @param array $includeFieldIds
2283    *   Ids of custom fields for which option groups must be included.
2284    *
2285    * Currently this is required in the cases where option groups are to be included
2286    * for inactive fields : CRM-5369
2287    *
2288    * @return mixed
2289    */
2290   public static function customOptionGroup($includeFieldIds = NULL) {
2291     static $customOptionGroup = NULL;
2292
2293     $cacheKey = (empty($includeFieldIds)) ? 'onlyActive' : 'force';
2294     if ($cacheKey == 'force') {
2295       $customOptionGroup[$cacheKey] = NULL;
2296     }
2297
2298     if (empty($customOptionGroup[$cacheKey])) {
2299       $whereClause = '( g.is_active = 1 AND f.is_active = 1 )';
2300
2301       //support for single as well as array format.
2302       if (!empty($includeFieldIds)) {
2303         if (is_array($includeFieldIds)) {
2304           $includeFieldIds = implode(',', $includeFieldIds);
2305         }
2306         $whereClause .= "OR f.id IN ( $includeFieldIds )";
2307       }
2308
2309       $query = "
2310     SELECT  g.id, g.title
2311       FROM  civicrm_option_group g
2312 INNER JOIN  civicrm_custom_field f ON ( g.id = f.option_group_id )
2313      WHERE  {$whereClause}";
2314
2315       $dao = CRM_Core_DAO::executeQuery($query);
2316       while ($dao->fetch()) {
2317         $customOptionGroup[$cacheKey][$dao->id] = $dao->title;
2318       }
2319     }
2320
2321     return $customOptionGroup[$cacheKey];
2322   }
2323
2324   /**
2325    * Get defaults for new entity.
2326    *
2327    * @return array
2328    */
2329   public static function getDefaults() {
2330     return [
2331       'is_required' => FALSE,
2332       'is_searchable' => FALSE,
2333       'in_selector' => FALSE,
2334       'is_search_range' => FALSE,
2335       //CRM-15792 - Custom field gets disabled if is_active not set
2336       // this would ideally be a mysql default.
2337       'is_active' => TRUE,
2338       'is_view' => FALSE,
2339     ];
2340   }
2341
2342   /**
2343    * Fix orphan groups.
2344    *
2345    * @param int $customFieldId
2346    *   Custom field id.
2347    * @param int $optionGroupId
2348    *   Option group id.
2349    */
2350   public static function fixOptionGroups($customFieldId, $optionGroupId) {
2351     // check if option group belongs to any custom Field else delete
2352     // get the current option group
2353     $currentOptionGroupId = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_CustomField',
2354       $customFieldId,
2355       'option_group_id'
2356     );
2357     // get the updated option group
2358     // if both are same return
2359     if (!$currentOptionGroupId || $currentOptionGroupId == $optionGroupId) {
2360       return;
2361     }
2362
2363     // check if option group is related to any other field
2364     self::checkOptionGroup($currentOptionGroupId);
2365   }
2366
2367   /**
2368    * Check if option group is related to more than one custom field.
2369    *
2370    * @param int $optionGroupId
2371    *   Option group id.
2372    */
2373   public static function checkOptionGroup($optionGroupId) {
2374     $query = "
2375 SELECT count(*)
2376 FROM   civicrm_custom_field
2377 WHERE  option_group_id = {$optionGroupId}";
2378
2379     $count = CRM_Core_DAO::singleValueQuery($query);
2380
2381     if ($count < 2) {
2382       //delete the option group
2383       CRM_Core_BAO_OptionGroup::del($optionGroupId);
2384     }
2385   }
2386
2387   /**
2388    * Get option group default.
2389    *
2390    * @param int $optionGroupId
2391    * @param bool $serialize
2392    *
2393    * @return null|string
2394    */
2395   public static function getOptionGroupDefault($optionGroupId, $serialize) {
2396     $query = "
2397 SELECT   default_value, serialize
2398 FROM     civicrm_custom_field
2399 WHERE    option_group_id = {$optionGroupId}
2400 AND      default_value IS NOT NULL";
2401
2402     $dao = CRM_Core_DAO::executeQuery($query);
2403     while ($dao->fetch()) {
2404       if ($dao->serialize == $serialize) {
2405         return $dao->default_value;
2406       }
2407       $defaultValue = $dao->default_value;
2408     }
2409
2410     // Convert serialization
2411     if (isset($defaultValue) && $serialize) {
2412       return CRM_Utils_Array::implodePadded([$defaultValue]);
2413     }
2414     elseif (isset($defaultValue)) {
2415       return CRM_Utils_Array::explodePadded($defaultValue)[0];
2416     }
2417     return NULL;
2418   }
2419
2420   /**
2421    * Post process function.
2422    *
2423    * @param array $params
2424    * @param int $entityID
2425    * @param string $customFieldExtends
2426    * @param bool $inline
2427    * @param bool $checkPermissions
2428    *
2429    * @return array
2430    */
2431   public static function postProcess(
2432     &$params,
2433     $entityID,
2434     $customFieldExtends,
2435     $inline = FALSE,
2436     $checkPermissions = TRUE
2437   ) {
2438     $customData = [];
2439
2440     foreach ($params as $key => $value) {
2441       if ($customFieldInfo = CRM_Core_BAO_CustomField::getKeyID($key, TRUE)) {
2442
2443         // for autocomplete transfer hidden value instead of label
2444         if ($params[$key] && isset($params[$key . '_id'])) {
2445           $value = $params[$key . '_id'];
2446         }
2447
2448         // we need to append time with date
2449         if ($params[$key] && isset($params[$key . '_time'])) {
2450           $value .= ' ' . $params[$key . '_time'];
2451         }
2452
2453         CRM_Core_BAO_CustomField::formatCustomField($customFieldInfo[0],
2454           $customData,
2455           $value,
2456           $customFieldExtends,
2457           $customFieldInfo[1],
2458           $entityID,
2459           $inline,
2460           $checkPermissions
2461         );
2462       }
2463     }
2464     return $customData;
2465   }
2466
2467   /**
2468    * Get custom field ID from field/group name/title.
2469    *
2470    * @param string $fieldName Field name or label
2471    * @param string|null $groupName (Optional) Group name or label
2472    * @param bool $fullString Whether to return "custom_123" or "123"
2473    *
2474    * @return string|int|null
2475    * @throws \CiviCRM_API3_Exception
2476    */
2477   public static function getCustomFieldID($fieldName, $groupName = NULL, $fullString = FALSE) {
2478     $cacheKey = $groupName . '.' . $fieldName;
2479     if (!isset(Civi::$statics['CRM_Core_BAO_CustomField'][$cacheKey])) {
2480       $customFieldParams = [
2481         'name' => $fieldName,
2482         'label' => $fieldName,
2483         'options' => ['or' => [["name", "label"]]],
2484       ];
2485
2486       if ($groupName) {
2487         $customFieldParams['custom_group_id.name'] = $groupName;
2488         $customFieldParams['custom_group_id.title'] = $groupName;
2489         $customFieldParams['options'] = ['or' => [["name", "label"], ["custom_group_id.name", "custom_group_id.title"]]];
2490       }
2491
2492       $field = civicrm_api3('CustomField', 'get', $customFieldParams);
2493
2494       if (empty($field['id'])) {
2495         Civi::$statics['CRM_Core_BAO_CustomField'][$cacheKey]['id'] = NULL;
2496         Civi::$statics['CRM_Core_BAO_CustomField'][$cacheKey]['string'] = NULL;
2497       }
2498       else {
2499         Civi::$statics['CRM_Core_BAO_CustomField'][$cacheKey]['id'] = $field['id'];
2500         Civi::$statics['CRM_Core_BAO_CustomField'][$cacheKey]['string'] = 'custom_' . $field['id'];
2501       }
2502     }
2503
2504     if ($fullString) {
2505       return Civi::$statics['CRM_Core_BAO_CustomField'][$cacheKey]['string'];
2506     }
2507     return Civi::$statics['CRM_Core_BAO_CustomField'][$cacheKey]['id'];
2508   }
2509
2510   /**
2511    * Given ID of a custom field, return its name as well as the name of the custom group it belongs to.
2512    *
2513    * @param array $ids
2514    *
2515    * @return array
2516    */
2517   public static function getNameFromID($ids) {
2518     if (is_array($ids)) {
2519       $ids = implode(',', $ids);
2520     }
2521     $sql = "
2522 SELECT     f.id, f.name AS field_name, f.label AS field_label, g.name AS group_name, g.title AS group_title
2523 FROM       civicrm_custom_field f
2524 INNER JOIN civicrm_custom_group g ON f.custom_group_id = g.id
2525 WHERE      f.id IN ($ids)";
2526
2527     $dao = CRM_Core_DAO::executeQuery($sql);
2528     $result = [];
2529     while ($dao->fetch()) {
2530       $result[$dao->id] = [
2531         'field_name' => $dao->field_name,
2532         'field_label' => $dao->field_label,
2533         'group_name' => $dao->group_name,
2534         'group_title' => $dao->group_title,
2535       ];
2536     }
2537     return $result;
2538   }
2539
2540   /**
2541    * Validate custom data.
2542    *
2543    * @param array $params
2544    *   Custom data submitted.
2545    *   ie array( 'custom_1' => 'validate me' );
2546    *
2547    * @return array
2548    *   validation errors.
2549    */
2550   public static function validateCustomData($params) {
2551     $errors = [];
2552     if (!is_array($params) || empty($params)) {
2553       return $errors;
2554     }
2555
2556     //pick up profile fields.
2557     $profileFields = [];
2558     $ufGroupId = $params['ufGroupId'] ?? NULL;
2559     if ($ufGroupId) {
2560       $profileFields = CRM_Core_BAO_UFGroup::getFields($ufGroupId,
2561         FALSE,
2562         CRM_Core_Action::VIEW
2563       );
2564     }
2565
2566     //lets start w/ params.
2567     foreach ($params as $key => $value) {
2568       $customFieldID = self::getKeyID($key);
2569       if (!$customFieldID) {
2570         continue;
2571       }
2572
2573       //load the structural info for given field.
2574       $field = new CRM_Core_DAO_CustomField();
2575       $field->id = $customFieldID;
2576       if (!$field->find(TRUE)) {
2577         continue;
2578       }
2579       $dataType = $field->data_type;
2580
2581       $profileField = CRM_Utils_Array::value($key, $profileFields, []);
2582       $fieldTitle = $profileField['title'] ?? NULL;
2583       $isRequired = $profileField['is_required'] ?? NULL;
2584       if (!$fieldTitle) {
2585         $fieldTitle = $field->label;
2586       }
2587
2588       //no need to validate.
2589       if (CRM_Utils_System::isNull($value) && !$isRequired) {
2590         continue;
2591       }
2592
2593       //lets validate first for required field.
2594       if ($isRequired && CRM_Utils_System::isNull($value)) {
2595         $errors[$key] = ts('%1 is a required field.', [1 => $fieldTitle]);
2596         continue;
2597       }
2598
2599       //now time to take care of custom field form rules.
2600       $ruleName = $errorMsg = NULL;
2601       switch ($dataType) {
2602         case 'Int':
2603           $ruleName = 'integer';
2604           $errorMsg = ts('%1 must be an integer (whole number).',
2605             array(1 => $fieldTitle)
2606           );
2607           break;
2608
2609         case 'Money':
2610           $ruleName = 'money';
2611           $errorMsg = ts('%1 must in proper money format. (decimal point/comma/space is allowed).',
2612             array(1 => $fieldTitle)
2613           );
2614           break;
2615
2616         case 'Float':
2617           $ruleName = 'numeric';
2618           $errorMsg = ts('%1 must be a number (with or without decimal point).',
2619             array(1 => $fieldTitle)
2620           );
2621           break;
2622
2623         case 'Link':
2624           $ruleName = 'wikiURL';
2625           $errorMsg = ts('%1 must be valid Website.',
2626             array(1 => $fieldTitle)
2627           );
2628           break;
2629       }
2630
2631       if ($ruleName && !CRM_Utils_System::isNull($value)) {
2632         $valid = FALSE;
2633         $funName = "CRM_Utils_Rule::{$ruleName}";
2634         if (is_callable($funName)) {
2635           $valid = call_user_func($funName, $value);
2636         }
2637         if (!$valid) {
2638           $errors[$key] = $errorMsg;
2639         }
2640       }
2641     }
2642
2643     return $errors;
2644   }
2645
2646   /**
2647    * Is this field a multi record field.
2648    *
2649    * @param int $customId
2650    *
2651    * @return bool
2652    */
2653   public static function isMultiRecordField($customId) {
2654     $isMultipleWithGid = FALSE;
2655     if (!is_numeric($customId)) {
2656       $customId = self::getKeyID($customId);
2657     }
2658     if (is_numeric($customId)) {
2659       $sql = "SELECT cg.id cgId
2660  FROM civicrm_custom_group cg
2661  INNER JOIN civicrm_custom_field cf
2662  ON cg.id = cf.custom_group_id
2663 WHERE cf.id = %1 AND cg.is_multiple = 1";
2664       $params[1] = [$customId, 'Integer'];
2665       $dao = CRM_Core_DAO::executeQuery($sql, $params);
2666       if ($dao->fetch()) {
2667         if ($dao->cgId) {
2668           $isMultipleWithGid = $dao->cgId;
2669         }
2670       }
2671     }
2672
2673     return $isMultipleWithGid;
2674   }
2675
2676   /**
2677    * Does this field type have any select options?
2678    *
2679    * @param array $field
2680    *
2681    * @return bool
2682    */
2683   public static function hasOptions($field) {
2684     // Fields retrieved via api are an array, or from the dao are an object. We'll accept either.
2685     $field = (array) $field;
2686     // This will include boolean fields with Yes/No options.
2687     if (in_array($field['html_type'], ['Radio', 'CheckBox'])) {
2688       return TRUE;
2689     }
2690     // Do this before the "Select" string search because date fields have a "Select Date" html_type
2691     // and contactRef fields have an "Autocomplete-Select" html_type - contacts are an FK not an option list.
2692     if (in_array($field['data_type'], ['ContactReference', 'Date'])) {
2693       return FALSE;
2694     }
2695     if (strpos($field['html_type'], 'Select') !== FALSE) {
2696       return TRUE;
2697     }
2698     return !empty($field['option_group_id']);
2699   }
2700
2701   /**
2702    * Does this field store a serialized string?
2703    *
2704    * @param CRM_Core_DAO_CustomField|array $field
2705    *
2706    * @return bool
2707    */
2708   public static function isSerialized($field) {
2709     // Fields retrieved via api are an array, or from the dao are an object. We'll accept either.
2710     $html_type = is_object($field) ? $field->html_type : $field['html_type'];
2711     // APIv3 has a "legacy" mode where it returns old-style html_type of "Multi-Select"
2712     // If anyone is using this function in conjunction with legacy api output, we'll accomodate:
2713     if ($html_type === 'CheckBox' || strpos($html_type, 'Multi') !== FALSE) {
2714       return TRUE;
2715     }
2716     // Otherwise this is the new standard as of 5.27
2717     return is_object($field) ? !empty($field->serialize) : !empty($field['serialize']);
2718   }
2719
2720   /**
2721    * Get api entity for this field
2722    *
2723    * @return string
2724    */
2725   public function getEntity() {
2726     $entity = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_CustomGroup', $this->custom_group_id, 'extends');
2727     return in_array($entity, CRM_Contact_BAO_ContactType::basicTypes(TRUE), TRUE) ? 'Contact' : $entity;
2728   }
2729
2730   /**
2731    * Set pseudoconstant properties for field metadata.
2732    *
2733    * @param array $field
2734    * @param string|null $optionGroupName
2735    */
2736   private static function getOptionsForField(&$field, $optionGroupName) {
2737     if ($optionGroupName) {
2738       $field['pseudoconstant'] = [
2739         'optionGroupName' => $optionGroupName,
2740         'optionEditPath' => 'civicrm/admin/options/' . $optionGroupName,
2741       ];
2742     }
2743     elseif ($field['data_type'] == 'Boolean') {
2744       $field['pseudoconstant'] = [
2745         'callback' => 'CRM_Core_SelectValues::boolean',
2746       ];
2747     }
2748     elseif ($field['data_type'] == 'Country') {
2749       $field['pseudoconstant'] = [
2750         'table' => 'civicrm_country',
2751         'keyColumn' => 'id',
2752         'labelColumn' => 'name',
2753         'nameColumn' => 'iso_code',
2754       ];
2755     }
2756     elseif ($field['data_type'] == 'StateProvince') {
2757       $field['pseudoconstant'] = [
2758         'table' => 'civicrm_state_province',
2759         'keyColumn' => 'id',
2760         'labelColumn' => 'name',
2761       ];
2762     }
2763   }
2764
2765   /**
2766    * Prepare params for the create operation.
2767    *
2768    * @param CRM_Core_DAO_CustomField $field
2769    * @param string $operation
2770    *   add|modify|delete
2771    *
2772    * @return array
2773    * @throws \CRM_Core_Exception
2774    */
2775   protected static function prepareCreateParams($field, $operation): array {
2776     $tableName = CRM_Core_DAO::getFieldValue(
2777       'CRM_Core_DAO_CustomGroup',
2778       $field->custom_group_id,
2779       'table_name'
2780     );
2781
2782     $params = [
2783       'table_name' => $tableName,
2784       'operation' => $operation,
2785       'name' => $field->column_name,
2786       'type' => CRM_Core_BAO_CustomValueTable::fieldToSQLType(
2787         $field->data_type,
2788         $field->text_length
2789       ),
2790       'required' => $field->is_required,
2791       'searchable' => $field->is_searchable,
2792     ];
2793
2794     // For adding/dropping FK constraints
2795     $params['fkName'] = CRM_Core_BAO_SchemaHandler::getIndexName($tableName, $field->column_name);
2796
2797     $fkFields = [
2798       'Country' => 'civicrm_country',
2799       'StateProvince' => 'civicrm_state_province',
2800       'ContactReference' => 'civicrm_contact',
2801       'File' => 'civicrm_file',
2802     ];
2803     if (isset($fkFields[$field->data_type])) {
2804       // Serialized fields store value-separated strings which are incompatible with FK constraints
2805       if (!$field->serialize) {
2806         $params['fk_table_name'] = $fkFields[$field->data_type];
2807         $params['fk_field_name'] = 'id';
2808         $params['fk_attributes'] = 'ON DELETE SET NULL';
2809       }
2810     }
2811     if ($field->serialize) {
2812       $params['type'] = 'varchar(255)';
2813     }
2814     if (isset($field->default_value)) {
2815       $params['default'] = "'{$field->default_value}'";
2816     }
2817     return $params;
2818   }
2819
2820 }