CRM/Core/BAO/CustomField.php

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