3 +--------------------------------------------------------------------+
4 | Copyright CiviCRM LLC. All rights reserved. |
6 | This work is published under the GNU AGPLv3 license with some |
7 | permitted exceptions and without any warranty. For full license |
8 | and copyright information, see https://civicrm.org/licensing |
9 +--------------------------------------------------------------------+
15 * @copyright CiviCRM LLC https://civicrm.org/licensing
19 class CRM_Core_BAO_CustomValueTable
{
22 * @param array $customParams
23 * @param string $parentOperation Operation being taken on the parent entity.
24 * If we know the parent entity is doing an insert we can skip the
25 * ON DUPLICATE UPDATE - which improves performance and reduces deadlocks.
31 public static function create($customParams, $parentOperation = NULL) {
32 if (empty($customParams) ||
33 !is_array($customParams)
38 $paramFieldsExtendContactForEntities = [];
40 foreach ($customParams as $tableName => $tables) {
41 foreach ($tables as $index => $fields) {
50 foreach ($fields as $field) {
52 $entityID = $field['entity_id'];
53 $hookID = $field['custom_group_id'];
54 $isMultiple = $field['is_multiple'];
55 if (array_key_exists('id', $field)) {
56 $sqlOP = "UPDATE $tableName ";
57 $where = " WHERE id = %{$count}";
58 $params[$count] = [$field['id'], 'Integer'];
63 $sqlOP = "INSERT INTO $tableName ";
69 // fix the value before we store it
70 $value = $field['value'];
71 $type = $field['type'];
75 if (is_array($value)) {
76 $value = CRM_Core_DAO
::VALUE_SEPARATOR
. implode(CRM_Core_DAO
::VALUE_SEPARATOR
, $value) . CRM_Core_DAO
::VALUE_SEPARATOR
;
79 elseif (!is_numeric($value) && !strstr($value, CRM_Core_DAO
::VALUE_SEPARATOR
)) {
80 //fix for multi select state, CRM-3437
81 $mulValues = explode(',', $value);
83 foreach ($mulValues as $key => $stateVal) {
85 $states['state_province'] = trim($stateVal);
87 CRM_Utils_Array
::lookupValue($states, 'state_province',
88 CRM_Core_PseudoConstant
::stateProvince(), TRUE
90 if (empty($states['state_province_id'])) {
91 CRM_Utils_Array
::lookupValue($states, 'state_province',
92 CRM_Core_PseudoConstant
::stateProvinceAbbreviation(), TRUE
95 $validStates[] = CRM_Utils_Array
::value('state_province_id', $states);
97 $value = implode(CRM_Core_DAO
::VALUE_SEPARATOR
,
104 // using type of timestamp allows us to sneak in a null into db
105 // gross but effective hack
116 if (is_array($value)) {
117 $value = CRM_Core_DAO
::VALUE_SEPARATOR
. implode(CRM_Core_DAO
::VALUE_SEPARATOR
, $value) . CRM_Core_DAO
::VALUE_SEPARATOR
;
120 elseif (!is_numeric($value) && !strstr($value, CRM_Core_DAO
::VALUE_SEPARATOR
)) {
121 //fix for multi select country, CRM-3437
122 $mulValues = explode(',', $value);
123 $validCountries = [];
124 foreach ($mulValues as $key => $countryVal) {
126 $countries['country'] = trim($countryVal);
127 CRM_Utils_Array
::lookupValue($countries, 'country',
128 CRM_Core_PseudoConstant
::country(), TRUE
130 if (empty($countries['country_id'])) {
131 CRM_Utils_Array
::lookupValue($countries, 'country',
132 CRM_Core_PseudoConstant
::countryIsoCode(), TRUE
135 $validCountries[] = CRM_Utils_Array
::value('country_id', $countries);
137 $value = implode(CRM_Core_DAO
::VALUE_SEPARATOR
,
144 // using type of timestamp allows us to sneak in a null into db
145 // gross but effective hack
155 if (!$field['file_id']) {
156 CRM_Core_Error
::fatal();
159 // need to add/update civicrm_entity_file
160 $entityFileDAO = new CRM_Core_DAO_EntityFile();
161 $entityFileDAO->file_id
= $field['file_id'];
162 $entityFileDAO->find(TRUE);
164 $entityFileDAO->entity_table
= $field['table_name'];
165 $entityFileDAO->entity_id
= $field['entity_id'];
166 $entityFileDAO->file_id
= $field['file_id'];
167 $entityFileDAO->save();
168 $value = $field['file_id'];
173 $value = CRM_Utils_Date
::isoToMysql($value);
177 if (is_numeric($value)) {
185 case 'ContactReference':
186 if ($value == NULL) {
194 case 'RichTextEditor':
200 $value = CRM_Utils_String
::strtoboolstr($value);
201 if ($value === FALSE) {
209 if ($value === 'null') {
210 // when unsetting a value to null, we don't need to validate the type
211 // https://projectllr.atlassian.net/browse/VGQBMP-20
212 $set[$field['column_name']] = $value;
215 $set[$field['column_name']] = "%{$count}";
216 $params[$count] = [$value, $type];
220 $fieldExtends = CRM_Utils_Array
::value('extends', $field);
222 CRM_Utils_Array
::value('entity_table', $field) == 'civicrm_contact'
223 ||
$fieldExtends == 'Contact'
224 ||
$fieldExtends == 'Individual'
225 ||
$fieldExtends == 'Organization'
226 ||
$fieldExtends == 'Household'
228 $paramFieldsExtendContactForEntities[$entityID]['custom_' . CRM_Utils_Array
::value('custom_field_id', $field)] = CRM_Utils_Array
::value('custom_field_id', $field);
234 foreach ($set as $n => $v) {
235 $setClause[] = "$n = $v";
237 $setClause = implode(',', $setClause);
239 // do this only for insert
240 $set['entity_id'] = "%{$count}";
241 $params[$count] = [$entityID, 'Integer'];
244 $fieldNames = implode(',', CRM_Utils_Type
::escapeAll(array_keys($set), 'MysqlColumnNameOrAlias'));
245 $fieldValues = implode(',', array_values($set));
246 $query = "$sqlOP ( $fieldNames ) VALUES ( $fieldValues )";
247 // for multiple values we dont do on duplicate key update
248 if (!$isMultiple && $parentOperation !== 'create') {
249 $query .= " ON DUPLICATE KEY UPDATE $setClause";
253 $query = "$sqlOP SET $setClause $where";
255 $dao = CRM_Core_DAO
::executeQuery($query, $params);
257 CRM_Utils_Hook
::custom($hookOP,
266 if (!empty($paramFieldsExtendContactForEntities)) {
267 CRM_Contact_BAO_Contact
::updateGreetingsOnTokenFieldChange($paramFieldsExtendContactForEntities, ['contact_id' => $entityID]);
272 * Given a field return the mysql data type associated with it.
274 * @param string $type
275 * @param int $maxLength
278 * the mysql data store placeholder
280 public static function fieldToSQLType($type, $maxLength = 255) {
281 if (!isset($maxLength) ||
282 !is_numeric($maxLength) ||
291 return "varchar($maxLength)";
299 // the below three are FK's, and have constraints added to them
301 case 'ContactReference':
302 case 'StateProvince':
305 return 'int unsigned';
311 return 'decimal(20,2)';
314 case 'RichTextEditor':
321 CRM_Core_Error
::fatal();
326 * @param array $params
327 * @param $entityTable
328 * @param int $entityID
329 * @param string $parentOperation Operation being taken on the parent entity.
330 * If we know the parent entity is doing an insert we can skip the
331 * ON DUPLICATE UPDATE - which improves performance and reduces deadlocks.
335 public static function store($params, $entityTable, $entityID, $parentOperation = NULL) {
337 foreach ($params as $fieldID => $param) {
338 foreach ($param as $index => $customValue) {
340 'entity_table' => $entityTable,
341 'entity_id' => $entityID,
342 'value' => $customValue['value'],
343 'type' => $customValue['type'],
344 'custom_field_id' => $customValue['custom_field_id'],
345 'custom_group_id' => $customValue['custom_group_id'],
346 'table_name' => $customValue['table_name'],
347 'column_name' => $customValue['column_name'],
348 'is_multiple' => $customValue['is_multiple'] ??
NULL,
349 'file_id' => $customValue['file_id'],
352 // Fix Date type to be timestamp, since that is how we store in db.
353 if ($cvParam['type'] == 'Date') {
354 $cvParam['type'] = 'Timestamp';
357 if (!empty($customValue['id'])) {
358 $cvParam['id'] = $customValue['id'];
360 if (!array_key_exists($customValue['table_name'], $cvParams)) {
361 $cvParams[$customValue['table_name']] = [];
364 if (!array_key_exists($index, $cvParams[$customValue['table_name']])) {
365 $cvParams[$customValue['table_name']][$index] = [];
368 $cvParams[$customValue['table_name']][$index][] = $cvParam;
371 if (!empty($cvParams)) {
372 self
::create($cvParams, $parentOperation);
377 * Post process function.
379 * @param array $params
380 * @param $entityTable
381 * @param int $entityID
382 * @param $customFieldExtends
384 public static function postProcess(&$params, $entityTable, $entityID, $customFieldExtends) {
385 $customData = CRM_Core_BAO_CustomField
::postProcess($params,
390 if (!empty($customData)) {
391 self
::store($customData, $entityTable, $entityID);
396 * Return an array of all custom values associated with an entity.
398 * @param int $entityID
399 * Identification number of the entity.
400 * @param string $entityType
401 * Type of entity that the entityID corresponds to, specified.
402 * as a string with format "'<EntityName>'". Comma separated
403 * list may be used to specify OR matches. Allowable values
404 * are enumerated types in civicrm_custom_group.extends field.
405 * Optional. Default value assumes entityID references a
407 * @param array $fieldIDs
408 * Optional list of fieldIDs that we want to retrieve. If this.
409 * is set the entityType is ignored
411 * @param bool $formatMultiRecordField
412 * @param array $DTparams - CRM-17810 dataTable params for the multiValued custom fields.
415 * Array of custom values for the entity with key=>value
416 * pairs specified as civicrm_custom_field.id => custom value.
417 * Empty array if no custom values found.
419 public static function &getEntityValues($entityID, $entityType = NULL, $fieldIDs = NULL, $formatMultiRecordField = FALSE, $DTparams = NULL) {
421 // adding this here since an empty contact id could have serious repurcussions
422 // like looping forever
423 CRM_Core_Error
::fatal('Please file an issue with the backtrace');
429 $cond[] = "cg.extends IN ( '$entityType' )";
434 $fieldIDList = implode(',', $fieldIDs);
435 $cond[] = "cf.id IN ( $fieldIDList )";
438 $cond[] = "cg.extends IN ( 'Contact', 'Individual', 'Household', 'Organization' )";
440 $cond = implode(' AND ', $cond);
442 $limit = $orderBy = '';
443 if (!empty($DTparams['rowCount']) && $DTparams['rowCount'] > 0) {
444 $limit = " LIMIT " . CRM_Utils_Type
::escape($DTparams['offset'], 'Integer') . ", " . CRM_Utils_Type
::escape($DTparams['rowCount'], 'Integer');
446 if (!empty($DTparams['sort'])) {
447 $orderBy = ' ORDER BY ' . CRM_Utils_Type
::escape($DTparams['sort'], 'String');
450 // First find all the fields that extend this type of entity.
452 SELECT cg.table_name,
457 cf.data_type as fieldDataType
458 FROM civicrm_custom_group cg,
459 civicrm_custom_field cf
460 WHERE cf.custom_group_id = cg.id
465 $dao = CRM_Core_DAO
::executeQuery($query);
467 $select = $fields = $isMultiple = [];
469 while ($dao->fetch()) {
470 if (!array_key_exists($dao->table_name
, $select)) {
471 $fields[$dao->table_name
] = [];
472 $select[$dao->table_name
] = [];
474 $fields[$dao->table_name
][] = $dao->fieldID
;
475 $select[$dao->table_name
][] = "{$dao->column_name} AS custom_{$dao->fieldID}";
476 $isMultiple[$dao->table_name
] = $dao->is_multiple ?
TRUE : FALSE;
477 $file[$dao->table_name
][$dao->fieldID
] = $dao->fieldDataType
;
480 $result = $sortedResult = [];
481 foreach ($select as $tableName => $clauses) {
482 if (!empty($DTparams['sort'])) {
483 $query = CRM_Core_DAO
::executeQuery("SELECT id FROM {$tableName} WHERE entity_id = {$entityID}");
485 while ($query->fetch()) {
486 $sortedResult["{$query->id}"] = $count;
491 $query = "SELECT SQL_CALC_FOUND_ROWS id, " . implode(', ', $clauses) . " FROM $tableName WHERE entity_id = $entityID {$orderBy} {$limit}";
492 $dao = CRM_Core_DAO
::executeQuery($query);
493 if (!empty($DTparams)) {
494 $result['count'] = CRM_Core_DAO
::singleValueQuery('SELECT FOUND_ROWS()');
496 while ($dao->fetch()) {
497 foreach ($fields[$tableName] as $fieldID) {
498 $fieldName = "custom_{$fieldID}";
499 if ($isMultiple[$tableName]) {
500 if ($formatMultiRecordField) {
501 $result["{$dao->id}"]["{$fieldID}"] = $dao->$fieldName;
504 $result["{$fieldID}_{$dao->id}"] = $dao->$fieldName;
508 $result[$fieldID] = $dao->$fieldName;
513 if (!empty($sortedResult)) {
514 $result['sortedResult'] = $sortedResult;
520 * Take in an array of entityID, custom_XXX => value
521 * and set the value in the appropriate table. Should also be able
522 * to set the value to null. Follows api parameter/return conventions
526 * @param array $params
531 public static function setValues(&$params) {
532 // For legacy reasons, accept this param in either format
533 if (empty($params['entityID']) && !empty($params['entity_id'])) {
534 $params['entityID'] = $params['entity_id'];
537 if (!isset($params['entityID']) ||
!CRM_Utils_Type
::validate($params['entityID'], 'Integer', FALSE)) {
538 return CRM_Core_Error
::createAPIError(ts('entity_id needs to be set and of type Integer'));
541 // first collect all the id/value pairs. The format is:
542 // custom_X => value or custom_X_VALUEID => value (for multiple values), VALUEID == -1, -2 etc for new insertions
544 foreach ($params as $n => $v) {
545 if ($customFieldInfo = CRM_Core_BAO_CustomField
::getKeyID($n, TRUE)) {
546 $fieldID = (int ) $customFieldInfo[0];
547 if (CRM_Utils_Type
::escape($fieldID, 'Integer', FALSE) === NULL) {
548 return CRM_Core_Error
::createAPIError(ts('field ID needs to be of type Integer for index %1',
552 if (!array_key_exists($fieldID, $fieldValues)) {
553 $fieldValues[$fieldID] = [];
556 if ($customFieldInfo[1]) {
557 $id = (int ) $customFieldInfo[1];
559 $fieldValues[$fieldID][] = [
566 $fieldIDList = implode(',', array_keys($fieldValues));
568 // format it so that we can just use create
570 SELECT cg.table_name as table_name ,
572 cg.is_multiple as is_multiple,
573 cg.extends as extends,
574 cf.column_name as column_name,
576 cf.html_type as html_type ,
577 cf.data_type as data_type
578 FROM civicrm_custom_group cg,
579 civicrm_custom_field cf
580 WHERE cf.custom_group_id = cg.id
581 AND cf.id IN ( $fieldIDList )
584 $dao = CRM_Core_DAO
::executeQuery($sql);
587 while ($dao->fetch()) {
588 $dataType = $dao->data_type
== 'Date' ?
'Timestamp' : $dao->data_type
;
589 foreach ($fieldValues[$dao->cf_id
] as $fieldValue) {
590 // Serialize array values
591 if (is_array($fieldValue['value']) && CRM_Core_BAO_CustomField
::isSerialized($dao)) {
592 $fieldValue['value'] = CRM_Utils_Array
::implodePadded($fieldValue['value']);
594 // Format null values correctly
595 if ($fieldValue['value'] === NULL ||
$fieldValue['value'] === '') {
601 $fieldValue['value'] = '';
605 $fieldValue['value'] = NULL;
608 case 'StateProvince':
612 $fieldValue['value'] = (int) 0;
616 // Ensure that value is of the right data type
617 elseif (CRM_Utils_Type
::escape($fieldValue['value'], $dataType, FALSE) === NULL) {
618 return CRM_Core_Error
::createAPIError(ts('value: %1 is not of the right field data type: %2',
620 1 => $fieldValue['value'],
621 2 => $dao->data_type
,
627 'entity_id' => $params['entityID'],
628 'value' => $fieldValue['value'],
630 'custom_field_id' => $dao->cf_id
,
631 'custom_group_id' => $dao->cg_id
,
632 'table_name' => $dao->table_name
,
633 'column_name' => $dao->column_name
,
634 'is_multiple' => $dao->is_multiple
,
635 'extends' => $dao->extends,
638 if (!empty($params['id'])) {
639 $cvParam['id'] = $params['id'];
642 if ($cvParam['type'] == 'File') {
643 $cvParam['file_id'] = $fieldValue['value'];
646 if (!array_key_exists($dao->table_name
, $cvParams)) {
647 $cvParams[$dao->table_name
] = [];
650 if (!array_key_exists($fieldValue['id'], $cvParams[$dao->table_name
])) {
651 $cvParams[$dao->table_name
][$fieldValue['id']] = [];
654 if ($fieldValue['id'] > 0) {
655 $cvParam['id'] = $fieldValue['id'];
657 $cvParams[$dao->table_name
][$fieldValue['id']][] = $cvParam;
661 if (!empty($cvParams)) {
662 self
::create($cvParams);
663 return ['is_error' => 0, 'result' => 1];
666 return CRM_Core_Error
::createAPIError(ts('Unknown error'));
670 * Take in an array of entityID, custom_ID
671 * and gets the value from the appropriate table.
673 * To get the values of custom fields with IDs 13 and 43 for contact ID 1327, use:
674 * $params = array( 'entityID' => 1327, 'custom_13' => 1, 'custom_43' => 1 );
676 * Entity Type will be inferred by the custom fields you request
677 * Specify $params['entityType'] if you do not supply any custom fields to return
678 * and entity type is other than Contact
682 * @param array $params
687 public static function &getValues(&$params) {
688 if (empty($params)) {
691 if (!isset($params['entityID']) ||
692 CRM_Utils_Type
::escape($params['entityID'],
696 return CRM_Core_Error
::createAPIError(ts('entityID needs to be set and of type Integer'));
699 // first collect all the ids. The format is:
702 foreach ($params as $n => $v) {
704 if (substr($n, 0, 7) == 'custom_') {
705 $idx = substr($n, 7);
706 if (CRM_Utils_Type
::escape($idx, 'Integer', FALSE) === NULL) {
707 return CRM_Core_Error
::createAPIError(ts('field ID needs to be of type Integer for index %1',
711 $fieldIDs[] = (int ) $idx;
715 $default = ['Contact', 'Individual', 'Household', 'Organization'];
716 if (!($type = CRM_Utils_Array
::value('entityType', $params)) ||
717 in_array($params['entityType'], $default)
722 $entities = CRM_Core_SelectValues
::customGroupExtends();
723 if (!array_key_exists($type, $entities)) {
724 if (in_array($type, $entities)) {
725 $type = $entities[$type];
726 if (in_array($type, $default)) {
731 return CRM_Core_Error
::createAPIError(ts('Invalid entity type') . ': "' . $type . '"');
736 $values = self
::getEntityValues($params['entityID'],
740 if (empty($values)) {
741 // note that this behaviour is undesirable from an API point of view - it should return an empty array
742 // since this is also called by the merger code & not sure the consequences of changing
743 // are just handling undoing this in the api layer. ie. converting the error back into a success
746 'error_message' => 'No values found for the specified entity ID and custom field(s).',
753 'entityID' => $params['entityID'],
755 foreach ($values as $id => $value) {
756 $result["custom_{$id}"] = $value;