3 +--------------------------------------------------------------------+
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2018 |
7 +--------------------------------------------------------------------+
8 | This file is a part of CiviCRM. |
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. |
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. |
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 +--------------------------------------------------------------------+
31 * @copyright CiviCRM LLC (c) 2004-2018
35 class CRM_Core_BAO_CustomValueTable
{
38 * @param array $customParams
42 public static function create(&$customParams) {
43 if (empty($customParams) ||
44 !is_array($customParams)
49 $paramFieldsExtendContactForEntities = array();
51 foreach ($customParams as $tableName => $tables) {
52 foreach ($tables as $index => $fields) {
61 foreach ($fields as $field) {
63 $entityID = $field['entity_id'];
64 $hookID = $field['custom_group_id'];
65 $isMultiple = $field['is_multiple'];
66 if (array_key_exists('id', $field)) {
67 $sqlOP = "UPDATE $tableName ";
68 $where = " WHERE id = %{$count}";
69 $params[$count] = array($field['id'], 'Integer');
74 $sqlOP = "INSERT INTO $tableName ";
80 // fix the value before we store it
81 $value = $field['value'];
82 $type = $field['type'];
86 if (is_array($value)) {
87 $value = CRM_Core_DAO
::VALUE_SEPARATOR
. implode(CRM_Core_DAO
::VALUE_SEPARATOR
, $value) . CRM_Core_DAO
::VALUE_SEPARATOR
;
90 elseif (!is_numeric($value) && !strstr($value, CRM_Core_DAO
::VALUE_SEPARATOR
)) {
91 //fix for multi select state, CRM-3437
92 $mulValues = explode(',', $value);
93 $validStates = array();
94 foreach ($mulValues as $key => $stateVal) {
96 $states['state_province'] = trim($stateVal);
98 CRM_Utils_Array
::lookupValue($states, 'state_province',
99 CRM_Core_PseudoConstant
::stateProvince(), TRUE
101 if (empty($states['state_province_id'])) {
102 CRM_Utils_Array
::lookupValue($states, 'state_province',
103 CRM_Core_PseudoConstant
::stateProvinceAbbreviation(), TRUE
106 $validStates[] = CRM_Utils_Array
::value('state_province_id', $states);
108 $value = implode(CRM_Core_DAO
::VALUE_SEPARATOR
,
115 // using type of timestamp allows us to sneak in a null into db
116 // gross but effective hack
127 $mulValues = explode(',', $value);
128 if (is_array($value)) {
129 $value = CRM_Core_DAO
::VALUE_SEPARATOR
. implode(CRM_Core_DAO
::VALUE_SEPARATOR
, $value) . CRM_Core_DAO
::VALUE_SEPARATOR
;
132 elseif (!is_numeric($value) && !strstr($value, CRM_Core_DAO
::VALUE_SEPARATOR
)) {
133 //fix for multi select country, CRM-3437
134 $mulValues = explode(',', $value);
135 $validCountries = array();
136 foreach ($mulValues as $key => $countryVal) {
137 $countries = array();
138 $countries['country'] = trim($countryVal);
139 CRM_Utils_Array
::lookupValue($countries, 'country',
140 CRM_Core_PseudoConstant
::country(), TRUE
142 if (empty($countries['country_id'])) {
143 CRM_Utils_Array
::lookupValue($countries, 'country',
144 CRM_Core_PseudoConstant
::countryIsoCode(), TRUE
147 $validCountries[] = CRM_Utils_Array
::value('country_id', $countries);
149 $value = implode(CRM_Core_DAO
::VALUE_SEPARATOR
,
156 // using type of timestamp allows us to sneak in a null into db
157 // gross but effective hack
167 if (!$field['file_id']) {
168 CRM_Core_Error
::fatal();
171 // need to add/update civicrm_entity_file
172 $entityFileDAO = new CRM_Core_DAO_EntityFile();
173 $entityFileDAO->file_id
= $field['file_id'];
174 $entityFileDAO->find(TRUE);
176 $entityFileDAO->entity_table
= $field['table_name'];
177 $entityFileDAO->entity_id
= $field['entity_id'];
178 $entityFileDAO->file_id
= $field['file_id'];
179 $entityFileDAO->save();
180 $entityFileDAO->free();
181 $value = $field['file_id'];
186 $value = CRM_Utils_Date
::isoToMysql($value);
190 if (is_numeric($value)) {
198 case 'ContactReference':
199 if ($value == NULL) {
207 case 'RichTextEditor':
213 $value = CRM_Utils_String
::strtoboolstr($value);
214 if ($value === FALSE) {
222 if (strtolower($value) === "null") {
223 // when unsetting a value to null, we don't need to validate the type
224 // https://projectllr.atlassian.net/browse/VGQBMP-20
225 $set[$field['column_name']] = $value;
228 $set[$field['column_name']] = "%{$count}";
229 $params[$count] = array($value, $type);
233 $fieldExtends = CRM_Utils_Array
::value('extends', $field);
235 CRM_Utils_Array
::value('entity_table', $field) == 'civicrm_contact'
236 ||
$fieldExtends == 'Contact'
237 ||
$fieldExtends == 'Individual'
238 ||
$fieldExtends == 'Organization'
239 ||
$fieldExtends == 'Household'
241 $paramFieldsExtendContactForEntities[$entityID]['custom_' . CRM_Utils_Array
::value('custom_field_id', $field)] = CRM_Utils_Array
::value('custom_field_id', $field);
246 $setClause = array();
247 foreach ($set as $n => $v) {
248 $setClause[] = "$n = $v";
250 $setClause = implode(',', $setClause);
252 // do this only for insert
253 $set['entity_id'] = "%{$count}";
254 $params[$count] = array($entityID, 'Integer');
257 $fieldNames = implode(',', CRM_Utils_Type
::escapeAll(array_keys($set), 'MysqlColumnNameOrAlias'));
258 $fieldValues = implode(',', array_values($set));
259 $query = "$sqlOP ( $fieldNames ) VALUES ( $fieldValues )";
260 // for multiple values we dont do on duplicate key update
262 $query .= " ON DUPLICATE KEY UPDATE $setClause";
266 $query = "$sqlOP SET $setClause $where";
268 $dao = CRM_Core_DAO
::executeQuery($query, $params);
270 CRM_Utils_Hook
::custom($hookOP,
279 if (!empty($paramFieldsExtendContactForEntities)) {
280 CRM_Contact_BAO_Contact
::updateGreetingsOnTokenFieldChange($paramFieldsExtendContactForEntities, array('contact_id' => $entityID));
285 * Given a field return the mysql data type associated with it.
287 * @param string $type
288 * @param int $maxLength
291 * the mysql data store placeholder
293 public static function fieldToSQLType($type, $maxLength = 255) {
294 if (!isset($maxLength) ||
295 !is_numeric($maxLength) ||
304 return "varchar($maxLength)";
312 // the below three are FK's, and have constraints added to them
314 case 'ContactReference':
315 case 'StateProvince':
318 return 'int unsigned';
324 return 'decimal(20,2)';
327 case 'RichTextEditor':
334 CRM_Core_Error
::fatal();
339 * @param array $params
340 * @param $entityTable
341 * @param int $entityID
343 public static function store(&$params, $entityTable, $entityID) {
345 foreach ($params as $fieldID => $param) {
346 foreach ($param as $index => $customValue) {
348 'entity_table' => $entityTable,
349 'entity_id' => $entityID,
350 'value' => $customValue['value'],
351 'type' => $customValue['type'],
352 'custom_field_id' => $customValue['custom_field_id'],
353 'custom_group_id' => $customValue['custom_group_id'],
354 'table_name' => $customValue['table_name'],
355 'column_name' => $customValue['column_name'],
356 'is_multiple' => CRM_Utils_Array
::value('is_multiple', $customValue),
357 'file_id' => $customValue['file_id'],
360 // Fix Date type to be timestamp, since that is how we store in db.
361 if ($cvParam['type'] == 'Date') {
362 $cvParam['type'] = 'Timestamp';
365 if (!empty($customValue['id'])) {
366 $cvParam['id'] = $customValue['id'];
368 if (!array_key_exists($customValue['table_name'], $cvParams)) {
369 $cvParams[$customValue['table_name']] = array();
372 if (!array_key_exists($index, $cvParams[$customValue['table_name']])) {
373 $cvParams[$customValue['table_name']][$index] = array();
376 $cvParams[$customValue['table_name']][$index][] = $cvParam;
379 if (!empty($cvParams)) {
380 self
::create($cvParams);
385 * Post process function.
387 * @param array $params
388 * @param $entityTable
389 * @param int $entityID
390 * @param $customFieldExtends
392 public static function postProcess(&$params, $entityTable, $entityID, $customFieldExtends) {
393 $customData = CRM_Core_BAO_CustomField
::postProcess($params,
398 if (!empty($customData)) {
399 self
::store($customData, $entityTable, $entityID);
404 * Return an array of all custom values associated with an entity.
406 * @param int $entityID
407 * Identification number of the entity.
408 * @param string $entityType
409 * Type of entity that the entityID corresponds to, specified.
410 * as a string with format "'<EntityName>'". Comma separated
411 * list may be used to specify OR matches. Allowable values
412 * are enumerated types in civicrm_custom_group.extends field.
413 * Optional. Default value assumes entityID references a
415 * @param array $fieldIDs
416 * Optional list of fieldIDs that we want to retrieve. If this.
417 * is set the entityType is ignored
419 * @param bool $formatMultiRecordField
420 * @param array $DTparams - CRM-17810 dataTable params for the multiValued custom fields.
423 * Array of custom values for the entity with key=>value
424 * pairs specified as civicrm_custom_field.id => custom value.
425 * Empty array if no custom values found.
427 public static function &getEntityValues($entityID, $entityType = NULL, $fieldIDs = NULL, $formatMultiRecordField = FALSE, $DTparams = NULL) {
429 // adding this here since an empty contact id could have serious repurcussions
430 // like looping forever
431 CRM_Core_Error
::fatal('Please file an issue with the backtrace');
437 $cond[] = "cg.extends IN ( '$entityType' )";
442 $fieldIDList = implode(',', $fieldIDs);
443 $cond[] = "cf.id IN ( $fieldIDList )";
446 $cond[] = "cg.extends IN ( 'Contact', 'Individual', 'Household', 'Organization' )";
448 $cond = implode(' AND ', $cond);
450 $limit = $orderBy = '';
451 if (!empty($DTparams['rowCount']) && $DTparams['rowCount'] > 0) {
452 $limit = " LIMIT " . CRM_Utils_Type
::escape($DTparams['offset'], 'Integer') . ", " . CRM_Utils_Type
::escape($DTparams['rowCount'], 'Integer');
454 if (!empty($DTparams['sort'])) {
455 $orderBy = ' ORDER BY ' . CRM_Utils_Type
::escape($DTparams['sort'], 'String');
458 // First find all the fields that extend this type of entity.
460 SELECT cg.table_name,
465 cf.data_type as fieldDataType
466 FROM civicrm_custom_group cg,
467 civicrm_custom_field cf
468 WHERE cf.custom_group_id = cg.id
473 $dao = CRM_Core_DAO
::executeQuery($query);
475 $select = $fields = $isMultiple = array();
477 while ($dao->fetch()) {
478 if (!array_key_exists($dao->table_name
, $select)) {
479 $fields[$dao->table_name
] = array();
480 $select[$dao->table_name
] = array();
482 $fields[$dao->table_name
][] = $dao->fieldID
;
483 $select[$dao->table_name
][] = "{$dao->column_name} AS custom_{$dao->fieldID}";
484 $isMultiple[$dao->table_name
] = $dao->is_multiple ?
TRUE : FALSE;
485 $file[$dao->table_name
][$dao->fieldID
] = $dao->fieldDataType
;
488 $result = $sortedResult = array();
489 foreach ($select as $tableName => $clauses) {
490 if (!empty($DTparams['sort'])) {
491 $query = CRM_Core_DAO
::executeQuery("SELECT id FROM {$tableName} WHERE entity_id = {$entityID}");
493 while ($query->fetch()) {
494 $sortedResult["{$query->id}"] = $count;
499 $query = "SELECT SQL_CALC_FOUND_ROWS id, " . implode(', ', $clauses) . " FROM $tableName WHERE entity_id = $entityID {$orderBy} {$limit}";
500 $dao = CRM_Core_DAO
::executeQuery($query);
501 if (!empty($DTparams)) {
502 $result['count'] = CRM_Core_DAO
::singleValueQuery('SELECT FOUND_ROWS()');
504 while ($dao->fetch()) {
505 foreach ($fields[$tableName] as $fieldID) {
506 $fieldName = "custom_{$fieldID}";
507 if ($isMultiple[$tableName]) {
508 if ($formatMultiRecordField) {
509 $result["{$dao->id}"]["{$fieldID}"] = $dao->$fieldName;
512 $result["{$fieldID}_{$dao->id}"] = $dao->$fieldName;
516 $result[$fieldID] = $dao->$fieldName;
521 if (!empty($sortedResult)) {
522 $result['sortedResult'] = $sortedResult;
528 * Take in an array of entityID, custom_XXX => value
529 * and set the value in the appropriate table. Should also be able
530 * to set the value to null. Follows api parameter/return conventions
534 * @param array $params
539 public static function setValues(&$params) {
541 if (!isset($params['entityID']) ||
542 CRM_Utils_Type
::escape($params['entityID'], 'Integer', FALSE) === NULL
544 return CRM_Core_Error
::createAPIError(ts('entityID needs to be set and of type Integer'));
547 // first collect all the id/value pairs. The format is:
548 // custom_X => value or custom_X_VALUEID => value (for multiple values), VALUEID == -1, -2 etc for new insertions
550 $fieldValues = array();
551 foreach ($params as $n => $v) {
552 if ($customFieldInfo = CRM_Core_BAO_CustomField
::getKeyID($n, TRUE)) {
553 $fieldID = (int ) $customFieldInfo[0];
554 if (CRM_Utils_Type
::escape($fieldID, 'Integer', FALSE) === NULL) {
555 return CRM_Core_Error
::createAPIError(ts('field ID needs to be of type Integer for index %1',
559 if (!array_key_exists($fieldID, $fieldValues)) {
560 $fieldValues[$fieldID] = array();
563 if ($customFieldInfo[1]) {
564 $id = (int ) $customFieldInfo[1];
566 $fieldValues[$fieldID][] = array(
573 $fieldIDList = implode(',', array_keys($fieldValues));
575 // format it so that we can just use create
577 SELECT cg.table_name as table_name ,
579 cg.is_multiple as is_multiple,
580 cg.extends as extends,
581 cf.column_name as column_name,
583 cf.data_type as data_type
584 FROM civicrm_custom_group cg,
585 civicrm_custom_field cf
586 WHERE cf.custom_group_id = cg.id
587 AND cf.id IN ( $fieldIDList )
590 $dao = CRM_Core_DAO
::executeQuery($sql);
593 while ($dao->fetch()) {
594 $dataType = $dao->data_type
== 'Date' ?
'Timestamp' : $dao->data_type
;
595 foreach ($fieldValues[$dao->cf_id
] as $fieldValue) {
596 // Format null values correctly
597 if ($fieldValue['value'] === NULL ||
$fieldValue['value'] === '') {
603 $fieldValue['value'] = '';
607 $fieldValue['value'] = NULL;
610 case 'StateProvince':
614 $fieldValue['value'] = (int) 0;
618 // Ensure that value is of the right data type
619 elseif (CRM_Utils_Type
::escape($fieldValue['value'], $dataType, FALSE) === NULL) {
620 return CRM_Core_Error
::createAPIError(ts('value: %1 is not of the right field data type: %2',
622 1 => $fieldValue['value'],
623 2 => $dao->data_type
,
629 'entity_id' => $params['entityID'],
630 'value' => $fieldValue['value'],
632 'custom_field_id' => $dao->cf_id
,
633 'custom_group_id' => $dao->cg_id
,
634 'table_name' => $dao->table_name
,
635 'column_name' => $dao->column_name
,
636 'is_multiple' => $dao->is_multiple
,
637 'extends' => $dao->extends,
640 if (!empty($params['id'])) {
641 $cvParam['id'] = $params['id'];
644 if ($cvParam['type'] == 'File') {
645 $cvParam['file_id'] = $fieldValue['value'];
648 if (!array_key_exists($dao->table_name
, $cvParams)) {
649 $cvParams[$dao->table_name
] = array();
652 if (!array_key_exists($fieldValue['id'], $cvParams[$dao->table_name
])) {
653 $cvParams[$dao->table_name
][$fieldValue['id']] = array();
656 if ($fieldValue['id'] > 0) {
657 $cvParam['id'] = $fieldValue['id'];
659 $cvParams[$dao->table_name
][$fieldValue['id']][] = $cvParam;
663 if (!empty($cvParams)) {
664 self
::create($cvParams);
665 return array('is_error' => 0, 'result' => 1);
668 return CRM_Core_Error
::createAPIError(ts('Unknown error'));
672 * Take in an array of entityID, custom_ID
673 * and gets the value from the appropriate table.
675 * To get the values of custom fields with IDs 13 and 43 for contact ID 1327, use:
676 * $params = array( 'entityID' => 1327, 'custom_13' => 1, 'custom_43' => 1 );
678 * Entity Type will be inferred by the custom fields you request
679 * Specify $params['entityType'] if you do not supply any custom fields to return
680 * and entity type is other than Contact
684 * @param array $params
689 public static function &getValues(&$params) {
690 if (empty($params)) {
693 if (!isset($params['entityID']) ||
694 CRM_Utils_Type
::escape($params['entityID'],
698 return CRM_Core_Error
::createAPIError(ts('entityID needs to be set and of type Integer'));
701 // first collect all the ids. The format is:
704 foreach ($params as $n => $v) {
706 if (substr($n, 0, 7) == 'custom_') {
707 $idx = substr($n, 7);
708 if (CRM_Utils_Type
::escape($idx, 'Integer', FALSE) === NULL) {
709 return CRM_Core_Error
::createAPIError(ts('field ID needs to be of type Integer for index %1',
713 $fieldIDs[] = (int ) $idx;
717 $default = array('Contact', 'Individual', 'Household', 'Organization');
718 if (!($type = CRM_Utils_Array
::value('entityType', $params)) ||
719 in_array($params['entityType'], $default)
724 $entities = CRM_Core_SelectValues
::customGroupExtends();
725 if (!array_key_exists($type, $entities)) {
726 if (in_array($type, $entities)) {
727 $type = $entities[$type];
728 if (in_array($type, $default)) {
733 return CRM_Core_Error
::createAPIError(ts('Invalid entity type') . ': "' . $type . '"');
738 $values = self
::getEntityValues($params['entityID'],
742 if (empty($values)) {
743 // note that this behaviour is undesirable from an API point of view - it should return an empty array
744 // since this is also called by the merger code & not sure the consequences of changing
745 // are just handling undoing this in the api layer. ie. converting the error back into a success
748 'error_message' => 'No values found for the specified entity ID and custom field(s).',
755 'entityID' => $params['entityID'],
757 foreach ($values as $id => $value) {
758 $result["custom_{$id}"] = $value;