[civicrm-core.git] / CRM / Core / BAO / CustomValueTable.php

<?php
/*
 +--------------------------------------------------------------------+
 | CiviCRM version 4.6                                                |
 +--------------------------------------------------------------------+
 | Copyright CiviCRM LLC (c) 2004-2014                                |
 +--------------------------------------------------------------------+
 | This file is a part of CiviCRM.                                    |
 |                                                                    |
 | CiviCRM is free software; you can copy, modify, and distribute it  |
 | under the terms of the GNU Affero General Public License           |
 | Version 3, 19 November 2007 and the CiviCRM Licensing Exception.   |
 |                                                                    |
 | CiviCRM is distributed in the hope that it will be useful, but     |
 | WITHOUT ANY WARRANTY; without even the implied warranty of         |
 | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
 | See the GNU Affero General Public License for more details.        |
 |                                                                    |
 | You should have received a copy of the GNU Affero General Public   |
 | License and the CiviCRM Licensing Exception along                  |
 | with this program; if not, contact CiviCRM LLC                     |
 | at info[AT]civicrm[DOT]org. If you have questions about the        |
 | GNU Affero General Public License or the licensing of CiviCRM,     |
 | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
 +--------------------------------------------------------------------+
 */

/**
 *
 * @package CRM
 * @copyright CiviCRM LLC (c) 2004-2014
 * $Id$
 *
 */
class CRM_Core_BAO_CustomValueTable {

  /**
   * @param array $customParams
   *
   * @throws Exception
   */
  public static function create(&$customParams) {
    if (empty($customParams) ||
      !is_array($customParams)
    ) {
      return;
    }

    foreach ($customParams as $tableName => $tables) {
      foreach ($tables as $index => $fields) {
        $sqlOP = NULL;
        $hookID = NULL;
        $hookOP = NULL;
        $entityID = NULL;
        $isMultiple = FALSE;
        $set = array();
        $params = array();
        $count = 1;
        foreach ($fields as $field) {
          if (!$sqlOP) {
            $entityID = $field['entity_id'];
            $hookID = $field['custom_group_id'];
            $isMultiple = $field['is_multiple'];
            if (array_key_exists('id', $field)) {
              $sqlOP = "UPDATE $tableName ";
              $where = " WHERE  id = %{$count}";
              $params[$count] = array($field['id'], 'Integer');
              $count++;
              $hookOP = 'edit';
            }
            else {
              $sqlOP = "INSERT INTO $tableName ";
              $where = NULL;
              $hookOP = 'create';
            }
          }

          // fix the value before we store it
          $value = $field['value'];
          $type = $field['type'];
          switch ($type) {
            case 'StateProvince':
              $type = 'Integer';
              if (is_array($value)) {
                $value = CRM_Core_DAO::VALUE_SEPARATOR . implode(CRM_Core_DAO::VALUE_SEPARATOR, $value) . CRM_Core_DAO::VALUE_SEPARATOR;
                $type = 'String';
              }
              elseif (!is_numeric($value)) {
                //fix for multi select state, CRM-3437
                $mulValues = explode(',', $value);
                $validStates = array();
                foreach ($mulValues as $key => $stateVal) {
                  $states = array();
                  $states['state_province'] = trim($stateVal);

                  CRM_Utils_Array::lookupValue($states, 'state_province',
                    CRM_Core_PseudoConstant::stateProvince(), TRUE
                  );
                  if (empty($states['state_province_id'])) {
                    CRM_Utils_Array::lookupValue($states, 'state_province',
                      CRM_Core_PseudoConstant::stateProvinceAbbreviation(), TRUE
                    );
                  }
                  $validStates[] = $states['state_province_id'];
                }
                $value = implode(CRM_Core_DAO::VALUE_SEPARATOR,
                  $validStates
                );
                $type = 'String';
              }
              elseif (!$value) {
                // CRM-3415
                // using type of timestamp allows us to sneak in a null into db
                // gross but effective hack
                $value = NULL;
                $type = 'Timestamp';
              }
              break;

            case 'Country':
              $type = 'Integer';
              if (is_array($value)) {
                $value = CRM_Core_DAO::VALUE_SEPARATOR . implode(CRM_Core_DAO::VALUE_SEPARATOR, $value) . CRM_Core_DAO::VALUE_SEPARATOR;
                $type = 'String';
              }
              elseif (!is_numeric($value)) {
                //fix for multi select country, CRM-3437
                $mulValues = explode(',', $value);
                $validCountries = array();
                foreach ($mulValues as $key => $countryVal) {
                  $countries = array();
                  $countries['country'] = trim($countryVal);
                  CRM_Utils_Array::lookupValue($countries, 'country',
                    CRM_Core_PseudoConstant::country(), TRUE
                  );
                  if (empty($countries['country_id'])) {
                    CRM_Utils_Array::lookupValue($countries, 'country',
                      CRM_Core_PseudoConstant::countryIsoCode(), TRUE
                    );
                  }
                  $validCountries[] = $countries['country_id'];
                }
                $value = implode(CRM_Core_DAO::VALUE_SEPARATOR,
                  $validCountries
                );
                $type = 'String';
              }
              elseif (!$value) {
                // CRM-3415
                // using type of timestamp allows us to sneak in a null into db
                // gross but effective hack
                $value = NULL;
                $type = 'Timestamp';
              }
              break;

            case 'File':
              if (!$field['file_id']) {
                CRM_Core_Error::fatal();
              }

              // need to add/update civicrm_entity_file
              $entityFileDAO = new CRM_Core_DAO_EntityFile();
              $entityFileDAO->file_id = $field['file_id'];
              $entityFileDAO->find(TRUE);

              $entityFileDAO->entity_table = $field['table_name'];
              $entityFileDAO->entity_id = $field['entity_id'];
              $entityFileDAO->file_id = $field['file_id'];
              $entityFileDAO->save();
              $entityFileDAO->free();
              $value = $field['file_id'];
              $type = 'String';
              break;

            case 'Date':
              $value = CRM_Utils_Date::isoToMysql($value);
              break;

            case 'Int':
              if (is_numeric($value)) {
                $type = 'Integer';
              }
              else {
                $type = 'Timestamp';
              }
              break;

            case 'ContactReference':
              if ($value == NULL) {
                $type = 'Timestamp';
              }
              else {
                $type = 'Integer';
              }
              break;

            case 'RichTextEditor':
              $type = 'String';
              break;

            case 'Boolean':
              //fix for CRM-3290
              $value = CRM_Utils_String::strtoboolstr($value);
              if ($value === FALSE) {
                $type = 'Timestamp';
              }
              break;

            default:
              break;
          }
          $set[$field['column_name']] = "%{$count}";
          $params[$count] = array($value, $type);
          $count++;
        }

        if (!empty($set)) {
          $setClause = array();
          foreach ($set as $n => $v) {
            $setClause[] = "$n = $v";
          }
          $setClause = implode(',', $setClause);
          if (!$where) {
            // do this only for insert
            $set['entity_id'] = "%{$count}";
            $params[$count] = array($entityID, 'Integer');
            $count++;

            $fieldNames = implode(',', array_keys($set));
            $fieldValues = implode(',', array_values($set));
            $query = "$sqlOP ( $fieldNames ) VALUES ( $fieldValues )";
            // for multiple values we dont do on duplicate key update
            if (!$isMultiple) {
              $query .= " ON DUPLICATE KEY UPDATE $setClause";
            }
          }
          else {
            $query = "$sqlOP SET $setClause $where";
          }
          $dao = CRM_Core_DAO::executeQuery($query, $params);

          CRM_Utils_Hook::custom($hookOP,
            $hookID,
            $entityID,
            $fields
          );
        }
      }
    }
  }

  /**
   * Given a field return the mysql data type associated with it.
   *
   * @param string $type
   * @param int $maxLength
   *
   * @return string
   *   the mysql data store placeholder
   */
  public static function fieldToSQLType($type, $maxLength = 255) {
    if (!isset($maxLength) ||
      !is_numeric($maxLength) ||
      $maxLength <= 0
    ) {
      $maxLength = 255;
    }

    switch ($type) {
      case 'String':
      case 'Link':
        return "varchar($maxLength)";

      case 'Boolean':
        return 'tinyint';

      case 'Int':
        return 'int';

      // the below three are FK's, and have constraints added to them

      case 'ContactReference':
      case 'StateProvince':
      case 'Country':
      case 'File':
        return 'int unsigned';

      case 'Float':
        return 'double';

      case 'Money':
        return 'decimal(20,2)';

      case 'Memo':
      case 'RichTextEditor':
        return 'text';

      case 'Date':
        return 'datetime';

      default:
        CRM_Core_Error::fatal();
    }
  }

  /**
   * @param array $params
   * @param $entityTable
   * @param int $entityID
   */
  public static function store(&$params, $entityTable, $entityID) {
    $cvParams = array();
    foreach ($params as $fieldID => $param) {
      foreach ($param as $index => $customValue) {
        $cvParam = array(
          'entity_table' => $entityTable,
          'entity_id' => $entityID,
          'value' => $customValue['value'],
          'type' => $customValue['type'],
          'custom_field_id' => $customValue['custom_field_id'],
          'custom_group_id' => $customValue['custom_group_id'],
          'table_name' => $customValue['table_name'],
          'column_name' => $customValue['column_name'],
          'is_multiple' => CRM_Utils_Array::value('is_multiple', $customValue),
          'file_id' => $customValue['file_id'],
        );

        // Fix Date type to be timestamp, since that is how we store in db.
        if ($cvParam['type'] == 'Date') {
          $cvParam['type'] = 'Timestamp';
        }

        if (!empty($customValue['id'])) {
          $cvParam['id'] = $customValue['id'];
        }
        if (!array_key_exists($customValue['table_name'], $cvParams)) {
          $cvParams[$customValue['table_name']] = array();
        }

        if (!array_key_exists($index, $cvParams[$customValue['table_name']])) {
          $cvParams[$customValue['table_name']][$index] = array();
        }

        $cvParams[$customValue['table_name']][$index][] = $cvParam;
      }
    }
    if (!empty($cvParams)) {
      self::create($cvParams);
    }
  }

  /**
   * Post process function.
   *
   * @param array $params
   * @param $customFields
   * @param $entityTable
   * @param int $entityID
   * @param $customFieldExtends
   */
  public static function postProcess(&$params, &$customFields, $entityTable, $entityID, $customFieldExtends) {
    $customData = CRM_Core_BAO_CustomField::postProcess($params,
      $customFields,
      $entityID,
      $customFieldExtends
    );

    if (!empty($customData)) {
      self::store($customData, $entityTable, $entityID);
    }
  }

  /**
   * Return an array of all custom values associated with an entity.
   *
   * @param int $entityID
   *   Identification number of the entity.
   * @param string $entityType
   *   Type of entity that the entityID corresponds to, specified.
   *                                   as a string with format "'<EntityName>'". Comma separated
   *                                   list may be used to specify OR matches. Allowable values
   *                                   are enumerated types in civicrm_custom_group.extends field.
   *                                   Optional. Default value assumes entityID references a
   *                                   contact entity.
   * @param array $fieldIDs
   *   Optional list of fieldIDs that we want to retrieve. If this.
   *                                   is set the entityType is ignored
   *
   * @param bool $formatMultiRecordField
   *
   * @return array
   *   Array of custom values for the entity with key=>value
   *                                   pairs specified as civicrm_custom_field.id => custom value.
   *                                   Empty array if no custom values found.
   */
  public static function &getEntityValues($entityID, $entityType = NULL, $fieldIDs = NULL, $formatMultiRecordField = FALSE) {
    if (!$entityID) {
      // adding this here since an empty contact id could have serious repurcussions
      // like looping forever
      CRM_Core_Error::fatal('Please file an issue with the backtrace');
      return NULL;
    }

    $cond = array();
    if ($entityType) {
      $cond[] = "cg.extends IN ( '$entityType' )";
    }
    if ($fieldIDs &&
      is_array($fieldIDs)
    ) {
      $fieldIDList = implode(',', $fieldIDs);
      $cond[] = "cf.id IN ( $fieldIDList )";
    }
    if (empty($cond)) {
      $cond[] = "cg.extends IN ( 'Contact', 'Individual', 'Household', 'Organization' )";
    }
    $cond = implode(' AND ', $cond);

    // First find all the fields that extend this type of entity.
    $query = "
SELECT cg.table_name,
       cg.id as groupID,
       cg.is_multiple,
       cf.column_name,
       cf.id as fieldID,
       cf.data_type as fieldDataType
FROM   civicrm_custom_group cg,
       civicrm_custom_field cf
WHERE  cf.custom_group_id = cg.id
AND    cg.is_active = 1
AND    cf.is_active = 1
AND    $cond
";
    $dao = CRM_Core_DAO::executeQuery($query);

    $select = $fields = $isMultiple = array();

    while ($dao->fetch()) {
      if (!array_key_exists($dao->table_name, $select)) {
        $fields[$dao->table_name] = array();
        $select[$dao->table_name] = array();
      }
      $fields[$dao->table_name][] = $dao->fieldID;
      $select[$dao->table_name][] = "{$dao->column_name} AS custom_{$dao->fieldID}";
      $isMultiple[$dao->table_name] = $dao->is_multiple ? TRUE : FALSE;
      $file[$dao->table_name][$dao->fieldID] = $dao->fieldDataType;
    }

    $result = array();
    foreach ($select as $tableName => $clauses) {
      $query = "SELECT id, " . implode(', ', $clauses) . " FROM $tableName WHERE entity_id = $entityID";
      $dao = CRM_Core_DAO::executeQuery($query);
      while ($dao->fetch()) {
        foreach ($fields[$tableName] as $fieldID) {
          $fieldName = "custom_{$fieldID}";
          if ($isMultiple[$tableName]) {
            if ($formatMultiRecordField) {
              $result["{$dao->id}"]["{$fieldID}"] = $dao->$fieldName;
            }
            else {
              $result["{$fieldID}_{$dao->id}"] = $dao->$fieldName;
            }
          }
          else {
            $result[$fieldID] = $dao->$fieldName;
          }
        }
      }
    }
    return $result;
  }

  /**
   * Take in an array of entityID, custom_XXX => value
   * and set the value in the appropriate table. Should also be able
   * to set the value to null. Follows api parameter/return conventions
   *
   * @array $params
   *
   * @param array $params
   *
   * @throws Exception
   * @return array
   */
  public static function setValues(&$params) {

    if (!isset($params['entityID']) ||
      CRM_Utils_Type::escape($params['entityID'], 'Integer', FALSE) === NULL
    ) {
      return CRM_Core_Error::createAPIError(ts('entityID needs to be set and of type Integer'));
    }

    // first collect all the id/value pairs. The format is:
    // custom_X => value or custom_X_VALUEID => value (for multiple values), VALUEID == -1, -2 etc for new insertions
    $values = array();
    $fieldValues = array();
    foreach ($params as $n => $v) {
      if ($customFieldInfo = CRM_Core_BAO_CustomField::getKeyID($n, TRUE)) {
        $fieldID = (int ) $customFieldInfo[0];
        if (CRM_Utils_Type::escape($fieldID, 'Integer', FALSE) === NULL) {
          return CRM_Core_Error::createAPIError(ts('field ID needs to be of type Integer for index %1',
            array(1 => $fieldID)
          ));
        }
        if (!array_key_exists($fieldID, $fieldValues)) {
          $fieldValues[$fieldID] = array();
        }
        $id = -1;
        if ($customFieldInfo[1]) {
          $id = (int ) $customFieldInfo[1];
        }
        $fieldValues[$fieldID][] = array(
          'value' => $v,
          'id' => $id,
        );
      }
    }

    $fieldIDList = implode(',', array_keys($fieldValues));

    // format it so that we can just use create
    $sql = "
SELECT cg.table_name  as table_name ,
       cg.id          as cg_id      ,
       cg.is_multiple as is_multiple,
       cf.column_name as column_name,
       cf.id          as cf_id      ,
       cf.data_type   as data_type
FROM   civicrm_custom_group cg,
       civicrm_custom_field cf
WHERE  cf.custom_group_id = cg.id
AND    cf.id IN ( $fieldIDList )
";

    $dao = CRM_Core_DAO::executeQuery($sql);
    $cvParams = array();

    while ($dao->fetch()) {
      $dataType = $dao->data_type == 'Date' ? 'Timestamp' : $dao->data_type;
      foreach ($fieldValues[$dao->cf_id] as $fieldValue) {
        // Format null values correctly
        if ($fieldValue['value'] === NULL || $fieldValue['value'] === '') {
          switch ($dataType) {
            case 'String':
            case 'Int':
            case 'Link':
            case 'Boolean':
              $fieldValue['value'] = '';
              break;

            case 'Timestamp':
              $fieldValue['value'] = NULL;
              break;

            case 'StateProvince':
            case 'Country':
            case 'Money':
            case 'Float':
              $fieldValue['value'] = (int) 0;
              break;
          }
        }
        // Ensure that value is of the right data type
        elseif (CRM_Utils_Type::escape($fieldValue['value'], $dataType, FALSE) === NULL) {
          return CRM_Core_Error::createAPIError(ts('value: %1 is not of the right field data type: %2',
            array(
              1 => $fieldValue['value'],
              2 => $dao->data_type,
            )
          ));
        }

        $cvParam = array(
          'entity_id' => $params['entityID'],
          'value' => $fieldValue['value'],
          'type' => $dataType,
          'custom_field_id' => $dao->cf_id,
          'custom_group_id' => $dao->cg_id,
          'table_name' => $dao->table_name,
          'column_name' => $dao->column_name,
          'is_multiple' => $dao->is_multiple,
        );

        if ($cvParam['type'] == 'File') {
          $cvParam['file_id'] = $fieldValue['value'];
        }

        if (!array_key_exists($dao->table_name, $cvParams)) {
          $cvParams[$dao->table_name] = array();
        }

        if (!array_key_exists($fieldValue['id'], $cvParams[$dao->table_name])) {
          $cvParams[$dao->table_name][$fieldValue['id']] = array();
        }

        if ($fieldValue['id'] > 0) {
          $cvParam['id'] = $fieldValue['id'];
        }
        $cvParams[$dao->table_name][$fieldValue['id']][] = $cvParam;
      }
    }

    if (!empty($cvParams)) {
      self::create($cvParams);
      return array('is_error' => 0, 'result' => 1);
    }

    return CRM_Core_Error::createAPIError(ts('Unknown error'));
  }

  /**
   * Take in an array of entityID, custom_ID
   * and gets the value from the appropriate table.
   *
   * To get the values of custom fields with IDs 13 and 43 for contact ID 1327, use:
   * $params = array( 'entityID' => 1327, 'custom_13' => 1, 'custom_43' => 1 );
   *
   * Entity Type will be infered by the custom fields you request
   * Specify $params['entityType'] if you do not supply any custom fields to return
   * and entity type is other than Contact
   *
   * @array $params
   *
   * @param array $params
   *
   * @throws Exception
   * @return array
   */
  public static function &getValues(&$params) {
    if (empty($params)) {
      return NULL;
    }
    if (!isset($params['entityID']) ||
      CRM_Utils_Type::escape($params['entityID'],
        'Integer', FALSE
      ) === NULL
    ) {
      return CRM_Core_Error::createAPIError(ts('entityID needs to be set and of type Integer'));
    }

    // first collect all the ids. The format is:
    // custom_ID
    $fieldIDs = array();
    foreach ($params as $n => $v) {
      $key = $idx = NULL;
      if (substr($n, 0, 7) == 'custom_') {
        $idx = substr($n, 7);
        if (CRM_Utils_Type::escape($idx, 'Integer', FALSE) === NULL) {
          return CRM_Core_Error::createAPIError(ts('field ID needs to be of type Integer for index %1',
            array(1 => $idx)
          ));
        }
        $fieldIDs[] = (int ) $idx;
      }
    }

    $default = array('Contact', 'Individual', 'Household', 'Organization');
    if (!($type = CRM_Utils_Array::value('entityType', $params)) ||
      in_array($params['entityType'], $default)
    ) {
      $type = NULL;
    }
    else {
      $entities = CRM_Core_SelectValues::customGroupExtends();
      if (!array_key_exists($type, $entities)) {
        if (in_array($type, $entities)) {
          $type = $entities[$type];
          if (in_array($type, $default)) {
            $type = NULL;
          }
        }
        else {
          return CRM_Core_Error::createAPIError(ts('Invalid entity type') . ': "' . $type . '"');
        }
      }
    }

    $values = self::getEntityValues($params['entityID'],
      $type,
      $fieldIDs
    );
    if (empty($values)) {
      // note that this behaviour is undesirable from an API point of view - it should return an empty array
      // since this is also called by the merger code & not sure the consequences of changing
      // are just handling undoing this in the api layer. ie. converting the error back into a success
      $result = array(
        'is_error' => 1,
        'error_message' => 'No values found for the specified entity ID and custom field(s).',
      );
      return $result;
    }
    else {
      $result = array(
        'is_error' => 0,
        'entityID' => $params['entityID'],
      );
      foreach ($values as $id => $value) {
        $result["custom_{$id}"] = $value;
      }
      return $result;
    }
  }

}
Commit	Line	Data
6a488035 TO	1	<?php
	2	/*
	3	+--------------------------------------------------------------------+
39de6fd5	4	\| CiviCRM version 4.6 \|
6a488035	5	+--------------------------------------------------------------------+
06b69b18	6	\| Copyright CiviCRM LLC (c) 2004-2014 \|
6a488035 TO	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	+--------------------------------------------------------------------+
d25dd0ee	26	*/
6a488035 TO	27
	28	/**
	29	*
	30	* @package CRM
06b69b18	31	* @copyright CiviCRM LLC (c) 2004-2014
6a488035 TO	32	* $Id$
	33	*
	34	*/
	35	class CRM_Core_BAO_CustomValueTable {
	36
b5c2afd0	37	/**
100fef9d	38	* @param array $customParams
b5c2afd0 EM	39	*
	40	* @throws Exception
	41	*/
00be9182	42	public static function create(&$customParams) {
6a488035 TO	43	if (empty($customParams) \|\|
	44	!is_array($customParams)
	45	) {
	46	return;
	47	}
8ef12e64	48
6a488035 TO	49	foreach ($customParams as $tableName => $tables) {
6a488035 TO	50	foreach ($tables as $index => $fields) {
353ffa53 TO	51	$sqlOP = NULL;
	52	$hookID = NULL;
	53	$hookOP = NULL;
	54	$entityID = NULL;
6a488035	55	$isMultiple = FALSE;
353ffa53 TO	56	$set = array();
	57	$params = array();
	58	$count = 1;
6a488035 TO	59	foreach ($fields as $field) {
6a488035 TO	60	if (!$sqlOP) {
353ffa53 TO	61	$entityID = $field['entity_id'];
353ffa53 TO	62	$hookID = $field['custom_group_id'];
6a488035 TO	63	$isMultiple = $field['is_multiple'];
6a488035 TO	64	if (array_key_exists('id', $field)) {
353ffa53 TO	65	$sqlOP = "UPDATE $tableName ";
353ffa53 TO	66	$where = " WHERE id = %{$count}";
6a488035 TO	67	$params[$count] = array($field['id'], 'Integer');
	68	$count++;
	69	$hookOP = 'edit';
	70	}
	71	else {
353ffa53 TO	72	$sqlOP = "INSERT INTO $tableName ";
353ffa53 TO	73	$where = NULL;
6a488035 TO	74	$hookOP = 'create';
	75	}
	76	}
	77
	78	// fix the value before we store it
	79	$value = $field['value'];
	80	$type = $field['type'];
	81	switch ($type) {
	82	case 'StateProvince':
	83	$type = 'Integer';
	84	if (is_array($value)) {
	85	$value = CRM_Core_DAO::VALUE_SEPARATOR . implode(CRM_Core_DAO::VALUE_SEPARATOR, $value) . CRM_Core_DAO::VALUE_SEPARATOR;
	86	$type = 'String';
	87	}
	88	elseif (!is_numeric($value)) {
	89	//fix for multi select state, CRM-3437
	90	$mulValues = explode(',', $value);
	91	$validStates = array();
	92	foreach ($mulValues as $key => $stateVal) {
	93	$states = array();
	94	$states['state_province'] = trim($stateVal);
	95
	96	CRM_Utils_Array::lookupValue($states, 'state_province',
	97	CRM_Core_PseudoConstant::stateProvince(), TRUE
	98	);
a7488080	99	if (empty($states['state_province_id'])) {
6a488035 TO	100	CRM_Utils_Array::lookupValue($states, 'state_province',
	101	CRM_Core_PseudoConstant::stateProvinceAbbreviation(), TRUE
	102	);
	103	}
	104	$validStates[] = $states['state_province_id'];
	105	}
	106	$value = implode(CRM_Core_DAO::VALUE_SEPARATOR,
	107	$validStates
	108	);
	109	$type = 'String';
	110	}
	111	elseif (!$value) {
	112	// CRM-3415
	113	// using type of timestamp allows us to sneak in a null into db
	114	// gross but effective hack
	115	$value = NULL;
	116	$type = 'Timestamp';
	117	}
	118	break;
	119
	120	case 'Country':
	121	$type = 'Integer';
	122	if (is_array($value)) {
	123	$value = CRM_Core_DAO::VALUE_SEPARATOR . implode(CRM_Core_DAO::VALUE_SEPARATOR, $value) . CRM_Core_DAO::VALUE_SEPARATOR;
	124	$type = 'String';
	125	}
	126	elseif (!is_numeric($value)) {
	127	//fix for multi select country, CRM-3437
	128	$mulValues = explode(',', $value);
	129	$validCountries = array();
	130	foreach ($mulValues as $key => $countryVal) {
	131	$countries = array();
	132	$countries['country'] = trim($countryVal);
	133	CRM_Utils_Array::lookupValue($countries, 'country',
	134	CRM_Core_PseudoConstant::country(), TRUE
	135	);
a7488080	136	if (empty($countries['country_id'])) {
6a488035 TO	137	CRM_Utils_Array::lookupValue($countries, 'country',
	138	CRM_Core_PseudoConstant::countryIsoCode(), TRUE
	139	);
	140	}
	141	$validCountries[] = $countries['country_id'];
	142	}
	143	$value = implode(CRM_Core_DAO::VALUE_SEPARATOR,
	144	$validCountries
	145	);
	146	$type = 'String';
	147	}
	148	elseif (!$value) {
	149	// CRM-3415
	150	// using type of timestamp allows us to sneak in a null into db
	151	// gross but effective hack
	152	$value = NULL;
	153	$type = 'Timestamp';
	154	}
	155	break;
	156
	157	case 'File':
	158	if (!$field['file_id']) {
	159	CRM_Core_Error::fatal();
	160	}
	161
	162	// need to add/update civicrm_entity_file
	163	$entityFileDAO = new CRM_Core_DAO_EntityFile();
	164	$entityFileDAO->file_id = $field['file_id'];
	165	$entityFileDAO->find(TRUE);
	166
	167	$entityFileDAO->entity_table = $field['table_name'];
	168	$entityFileDAO->entity_id = $field['entity_id'];
	169	$entityFileDAO->file_id = $field['file_id'];
	170	$entityFileDAO->save();
	171	$entityFileDAO->free();
	172	$value = $field['file_id'];
	173	$type = 'String';
	174	break;
	175
	176	case 'Date':
	177	$value = CRM_Utils_Date::isoToMysql($value);
	178	break;
	179
	180	case 'Int':
	181	if (is_numeric($value)) {
	182	$type = 'Integer';
	183	}
	184	else {
	185	$type = 'Timestamp';
	186	}
	187	break;
	188
	189	case 'ContactReference':
	190	if ($value == NULL) {
	191	$type = 'Timestamp';
	192	}
	193	else {
	194	$type = 'Integer';
	195	}
	196	break;
	197
	198	case 'RichTextEditor':
	199	$type = 'String';
	200	break;
201
202	case 'Boolean':
203	//fix for CRM-3290
204	$value = CRM_Utils_String::strtoboolstr($value);
205	if ($value === FALSE) {
206	$type = 'Timestamp';
207	}
208	break;
209
210	default:
211	break;
212	}
213	$set[$field['column_name']] = "%{$count}";
214	$params[$count] = array($value, $type);
215	$count++;
216	}
217
218	if (!empty($set)) {
219	$setClause = array();
220	foreach ($set as $n => $v) {
221	$setClause[] = "$n = $v";
222	}
223	$setClause = implode(',', $setClause);
224	if (!$where) {
225	// do this only for insert
226	$set['entity_id'] = "%{$count}";
227	$params[$count] = array($entityID, 'Integer');
228	$count++;
229
353ffa53	230	$fieldNames = implode(',', array_keys($set));
6a488035	231	$fieldValues = implode(',', array_values($set));
353ffa53	232	$query = "$sqlOP ( $fieldNames ) VALUES ( $fieldValues )";
6a488035 TO	233	// for multiple values we dont do on duplicate key update
	234	if (!$isMultiple) {
	235	$query .= " ON DUPLICATE KEY UPDATE $setClause";
	236	}
	237	}
	238	else {
	239	$query = "$sqlOP SET $setClause $where";
	240	}
	241	$dao = CRM_Core_DAO::executeQuery($query, $params);
	242
	243	CRM_Utils_Hook::custom($hookOP,
	244	$hookID,
	245	$entityID,
	246	$fields
	247	);
	248	}
	249	}
	250	}
	251	}
	252
	253	/**
fe482240	254	* Given a field return the mysql data type associated with it.
6a488035	255	*
6a0b768e	256	* @param string $type
fd31fa4c EM	257	* @param int $maxLength
fd31fa4c EM	258	*
72b3a70c CW	259	* @return string
72b3a70c CW	260	* the mysql data store placeholder
6a488035 TO	261	*/
	262	public static function fieldToSQLType($type, $maxLength = 255) {
	263	if (!isset($maxLength) \|\|
	264	!is_numeric($maxLength) \|\|
	265	$maxLength <= 0
	266	) {
	267	$maxLength = 255;
	268	}
	269
	270	switch ($type) {
	271	case 'String':
	272	case 'Link':
	273	return "varchar($maxLength)";
	274
	275	case 'Boolean':
	276	return 'tinyint';
	277
	278	case 'Int':
	279	return 'int';
2aa397bc	280
6a488035 TO	281	// the below three are FK's, and have constraints added to them
	282
	283	case 'ContactReference':
	284	case 'StateProvince':
	285	case 'Country':
	286	case 'File':
	287	return 'int unsigned';
	288
	289	case 'Float':
	290	return 'double';
	291
	292	case 'Money':
	293	return 'decimal(20,2)';
	294
	295	case 'Memo':
	296	case 'RichTextEditor':
	297	return 'text';
	298
	299	case 'Date':
	300	return 'datetime';
	301
	302	default:
	303	CRM_Core_Error::fatal();
	304	}
	305	}
	306
b5c2afd0	307	/**
c490a46a	308	* @param array $params
b5c2afd0	309	* @param $entityTable
100fef9d	310	* @param int $entityID
b5c2afd0	311	*/
00be9182	312	public static function store(&$params, $entityTable, $entityID) {
6a488035 TO	313	$cvParams = array();
	314	foreach ($params as $fieldID => $param) {
	315	foreach ($param as $index => $customValue) {
	316	$cvParam = array(
	317	'entity_table' => $entityTable,
	318	'entity_id' => $entityID,
	319	'value' => $customValue['value'],
	320	'type' => $customValue['type'],
	321	'custom_field_id' => $customValue['custom_field_id'],
	322	'custom_group_id' => $customValue['custom_group_id'],
	323	'table_name' => $customValue['table_name'],
	324	'column_name' => $customValue['column_name'],
	325	'is_multiple' => CRM_Utils_Array::value('is_multiple', $customValue),
	326	'file_id' => $customValue['file_id'],
	327	);
	328
fe482240	329	// Fix Date type to be timestamp, since that is how we store in db.
6a488035 TO	330	if ($cvParam['type'] == 'Date') {
	331	$cvParam['type'] = 'Timestamp';
	332	}
	333
a7488080	334	if (!empty($customValue['id'])) {
6a488035 TO	335	$cvParam['id'] = $customValue['id'];
	336	}
	337	if (!array_key_exists($customValue['table_name'], $cvParams)) {
	338	$cvParams[$customValue['table_name']] = array();
	339	}
	340
	341	if (!array_key_exists($index, $cvParams[$customValue['table_name']])) {
	342	$cvParams[$customValue['table_name']][$index] = array();
	343	}
	344
	345	$cvParams[$customValue['table_name']][$index][] = $cvParam;
	346	}
	347	}
	348	if (!empty($cvParams)) {
	349	self::create($cvParams);
	350	}
	351	}
	352
b5c2afd0	353	/**
fe482240 EM	354	* Post process function.
fe482240 EM	355	*
c490a46a	356	* @param array $params
b5c2afd0 EM	357	* @param $customFields
b5c2afd0 EM	358	* @param $entityTable
100fef9d	359	* @param int $entityID
b5c2afd0 EM	360	* @param $customFieldExtends
b5c2afd0 EM	361	*/
00be9182	362	public static function postProcess(&$params, &$customFields, $entityTable, $entityID, $customFieldExtends) {
6a488035 TO	363	$customData = CRM_Core_BAO_CustomField::postProcess($params,
	364	$customFields,
	365	$entityID,
	366	$customFieldExtends
	367	);
	368
	369	if (!empty($customData)) {
	370	self::store($customData, $entityTable, $entityID);
	371	}
	372	}
	373
	374	/**
	375	* Return an array of all custom values associated with an entity.
	376	*
6a0b768e TO	377	* @param int $entityID
	378	* Identification number of the entity.
	379	* @param string $entityType
	380	* Type of entity that the entityID corresponds to, specified.
6a488035 TO	381	* as a string with format "'<EntityName>'". Comma separated
	382	* list may be used to specify OR matches. Allowable values
	383	* are enumerated types in civicrm_custom_group.extends field.
	384	* Optional. Default value assumes entityID references a
	385	* contact entity.
6a0b768e TO	386	* @param array $fieldIDs
6a0b768e TO	387	* Optional list of fieldIDs that we want to retrieve. If this.
6a488035 TO	388	* is set the entityType is ignored
6a488035 TO	389	*
77b97be7 EM	390	* @param bool $formatMultiRecordField
77b97be7 EM	391	*
a6c01b45 CW	392	* @return array
a6c01b45 CW	393	* Array of custom values for the entity with key=>value
6a488035 TO	394	* pairs specified as civicrm_custom_field.id => custom value.
6a488035 TO	395	* Empty array if no custom values found.
6a488035 TO	396	*/
	397	public static function &getEntityValues($entityID, $entityType = NULL, $fieldIDs = NULL, $formatMultiRecordField = FALSE) {
	398	if (!$entityID) {
	399	// adding this here since an empty contact id could have serious repurcussions
	400	// like looping forever
	401	CRM_Core_Error::fatal('Please file an issue with the backtrace');
	402	return NULL;
	403	}
	404
	405	$cond = array();
	406	if ($entityType) {
	407	$cond[] = "cg.extends IN ( '$entityType' )";
	408	}
	409	if ($fieldIDs &&
	410	is_array($fieldIDs)
	411	) {
	412	$fieldIDList = implode(',', $fieldIDs);
	413	$cond[] = "cf.id IN ( $fieldIDList )";
	414	}
	415	if (empty($cond)) {
	416	$cond[] = "cg.extends IN ( 'Contact', 'Individual', 'Household', 'Organization' )";
	417	}
	418	$cond = implode(' AND ', $cond);
	419
fe482240	420	// First find all the fields that extend this type of entity.
6a488035 TO	421	$query = "
	422	SELECT cg.table_name,
	423	cg.id as groupID,
	424	cg.is_multiple,
	425	cf.column_name,
34f51a07 N	426	cf.id as fieldID,
34f51a07 N	427	cf.data_type as fieldDataType
6a488035 TO	428	FROM civicrm_custom_group cg,
	429	civicrm_custom_field cf
	430	WHERE cf.custom_group_id = cg.id
	431	AND cg.is_active = 1
	432	AND cf.is_active = 1
	433	AND $cond
	434	";
	435	$dao = CRM_Core_DAO::executeQuery($query);
	436
	437	$select = $fields = $isMultiple = array();
	438
	439	while ($dao->fetch()) {
	440	if (!array_key_exists($dao->table_name, $select)) {
	441	$fields[$dao->table_name] = array();
	442	$select[$dao->table_name] = array();
	443	}
	444	$fields[$dao->table_name][] = $dao->fieldID;
	445	$select[$dao->table_name][] = "{$dao->column_name} AS custom_{$dao->fieldID}";
	446	$isMultiple[$dao->table_name] = $dao->is_multiple ? TRUE : FALSE;
77b97be7	447	$file[$dao->table_name][$dao->fieldID] = $dao->fieldDataType;
6a488035 TO	448	}
	449
	450	$result = array();
	451	foreach ($select as $tableName => $clauses) {
	452	$query = "SELECT id, " . implode(', ', $clauses) . " FROM $tableName WHERE entity_id = $entityID";
	453	$dao = CRM_Core_DAO::executeQuery($query);
	454	while ($dao->fetch()) {
	455	foreach ($fields[$tableName] as $fieldID) {
	456	$fieldName = "custom_{$fieldID}";
	457	if ($isMultiple[$tableName]) {
	458	if ($formatMultiRecordField) {
d8f34a6e	459	$result["{$dao->id}"]["{$fieldID}"] = $dao->$fieldName;
fe482240 EM	460	}
fe482240 EM	461	else {
6a488035 TO	462	$result["{$fieldID}_{$dao->id}"] = $dao->$fieldName;
	463	}
	464	}
	465	else {
d8f34a6e	466	$result[$fieldID] = $dao->$fieldName;
6a488035 TO	467	}
	468	}
	469	}
	470	}
	471	return $result;
	472	}
	473
	474	/**
100fef9d	475	* Take in an array of entityID, custom_XXX => value
6a488035 TO	476	* and set the value in the appropriate table. Should also be able
	477	* to set the value to null. Follows api parameter/return conventions
	478	*
	479	* @array $params
	480	*
c490a46a	481	* @param array $params
2a6da8d7 EM	482	*
2a6da8d7 EM	483	* @throws Exception
6a488035	484	* @return array
6a488035	485	*/
00be9182	486	public static function setValues(&$params) {
6a488035 TO	487
	488	if (!isset($params['entityID']) \|\|
	489	CRM_Utils_Type::escape($params['entityID'], 'Integer', FALSE) === NULL
	490	) {
	491	return CRM_Core_Error::createAPIError(ts('entityID needs to be set and of type Integer'));
	492	}
	493
	494	// first collect all the id/value pairs. The format is:
	495	// custom_X => value or custom_X_VALUEID => value (for multiple values), VALUEID == -1, -2 etc for new insertions
	496	$values = array();
	497	$fieldValues = array();
	498	foreach ($params as $n => $v) {
	499	if ($customFieldInfo = CRM_Core_BAO_CustomField::getKeyID($n, TRUE)) {
	500	$fieldID = (int ) $customFieldInfo[0];
	501	if (CRM_Utils_Type::escape($fieldID, 'Integer', FALSE) === NULL) {
	502	return CRM_Core_Error::createAPIError(ts('field ID needs to be of type Integer for index %1',
353ffa53 TO	503	array(1 => $fieldID)
353ffa53 TO	504	));
6a488035 TO	505	}
	506	if (!array_key_exists($fieldID, $fieldValues)) {
	507	$fieldValues[$fieldID] = array();
	508	}
	509	$id = -1;
	510	if ($customFieldInfo[1]) {
	511	$id = (int ) $customFieldInfo[1];
	512	}
	513	$fieldValues[$fieldID][] = array(
	514	'value' => $v,
	515	'id' => $id,
	516	);
	517	}
	518	}
	519
	520	$fieldIDList = implode(',', array_keys($fieldValues));
	521
	522	// format it so that we can just use create
	523	$sql = "
	524	SELECT cg.table_name as table_name ,
	525	cg.id as cg_id ,
	526	cg.is_multiple as is_multiple,
	527	cf.column_name as column_name,
	528	cf.id as cf_id ,
	529	cf.data_type as data_type
	530	FROM civicrm_custom_group cg,
	531	civicrm_custom_field cf
	532	WHERE cf.custom_group_id = cg.id
	533	AND cf.id IN ( $fieldIDList )
	534	";
	535
	536	$dao = CRM_Core_DAO::executeQuery($sql);
	537	$cvParams = array();
	538
	539	while ($dao->fetch()) {
	540	$dataType = $dao->data_type == 'Date' ? 'Timestamp' : $dao->data_type;
	541	foreach ($fieldValues[$dao->cf_id] as $fieldValue) {
	542	// Format null values correctly
	543	if ($fieldValue['value'] === NULL \|\| $fieldValue['value'] === '') {
	544	switch ($dataType) {
	545	case 'String':
	546	case 'Int':
	547	case 'Link':
	548	case 'Boolean':
	549	$fieldValue['value'] = '';
	550	break;
	551
	552	case 'Timestamp':
	553	$fieldValue['value'] = NULL;
	554	break;
	555
	556	case 'StateProvince':
	557	case 'Country':
	558	case 'Money':
	559	case 'Float':
2aa397bc	560	$fieldValue['value'] = (int) 0;
6a488035 TO	561	break;
	562	}
	563	}
	564	// Ensure that value is of the right data type
	565	elseif (CRM_Utils_Type::escape($fieldValue['value'], $dataType, FALSE) === NULL) {
	566	return CRM_Core_Error::createAPIError(ts('value: %1 is not of the right field data type: %2',
353ffa53 TO	567	array(
	568	1 => $fieldValue['value'],
	569	2 => $dao->data_type,
	570	)
	571	));
6a488035 TO	572	}
	573
	574	$cvParam = array(
	575	'entity_id' => $params['entityID'],
	576	'value' => $fieldValue['value'],
	577	'type' => $dataType,
	578	'custom_field_id' => $dao->cf_id,
	579	'custom_group_id' => $dao->cg_id,
	580	'table_name' => $dao->table_name,
	581	'column_name' => $dao->column_name,
	582	'is_multiple' => $dao->is_multiple,
	583	);
	584
e7dcccf0 CW	585	if ($cvParam['type'] == 'File') {
	586	$cvParam['file_id'] = $fieldValue['value'];
	587	}
	588
6a488035 TO	589	if (!array_key_exists($dao->table_name, $cvParams)) {
	590	$cvParams[$dao->table_name] = array();
	591	}
	592
	593	if (!array_key_exists($fieldValue['id'], $cvParams[$dao->table_name])) {
	594	$cvParams[$dao->table_name][$fieldValue['id']] = array();
	595	}
	596
	597	if ($fieldValue['id'] > 0) {
	598	$cvParam['id'] = $fieldValue['id'];
	599	}
	600	$cvParams[$dao->table_name][$fieldValue['id']][] = $cvParam;
	601	}
	602	}
	603
	604	if (!empty($cvParams)) {
	605	self::create($cvParams);
	606	return array('is_error' => 0, 'result' => 1);
	607	}
	608
	609	return CRM_Core_Error::createAPIError(ts('Unknown error'));
	610	}
	611
	612	/**
100fef9d	613	* Take in an array of entityID, custom_ID
6a488035 TO	614	* and gets the value from the appropriate table.
	615	*
	616	* To get the values of custom fields with IDs 13 and 43 for contact ID 1327, use:
	617	* $params = array( 'entityID' => 1327, 'custom_13' => 1, 'custom_43' => 1 );
	618	*
	619	* Entity Type will be infered by the custom fields you request
	620	* Specify $params['entityType'] if you do not supply any custom fields to return
	621	* and entity type is other than Contact
	622	*
	623	* @array $params
	624	*
c490a46a	625	* @param array $params
2a6da8d7 EM	626	*
2a6da8d7 EM	627	* @throws Exception
6a488035	628	* @return array
6a488035	629	*/
00be9182	630	public static function &getValues(&$params) {
6a488035 TO	631	if (empty($params)) {
	632	return NULL;
	633	}
	634	if (!isset($params['entityID']) \|\|
	635	CRM_Utils_Type::escape($params['entityID'],
	636	'Integer', FALSE
	637	) === NULL
	638	) {
	639	return CRM_Core_Error::createAPIError(ts('entityID needs to be set and of type Integer'));
	640	}
	641
	642	// first collect all the ids. The format is:
	643	// custom_ID
	644	$fieldIDs = array();
	645	foreach ($params as $n => $v) {
	646	$key = $idx = NULL;
	647	if (substr($n, 0, 7) == 'custom_') {
	648	$idx = substr($n, 7);
	649	if (CRM_Utils_Type::escape($idx, 'Integer', FALSE) === NULL) {
	650	return CRM_Core_Error::createAPIError(ts('field ID needs to be of type Integer for index %1',
353ffa53 TO	651	array(1 => $idx)
353ffa53 TO	652	));
6a488035 TO	653	}
	654	$fieldIDs[] = (int ) $idx;
	655	}
	656	}
	657
	658	$default = array('Contact', 'Individual', 'Household', 'Organization');
	659	if (!($type = CRM_Utils_Array::value('entityType', $params)) \|\|
	660	in_array($params['entityType'], $default)
	661	) {
	662	$type = NULL;
	663	}
	664	else {
	665	$entities = CRM_Core_SelectValues::customGroupExtends();
	666	if (!array_key_exists($type, $entities)) {
	667	if (in_array($type, $entities)) {
	668	$type = $entities[$type];
	669	if (in_array($type, $default)) {
	670	$type = NULL;
	671	}
	672	}
	673	else {
	674	return CRM_Core_Error::createAPIError(ts('Invalid entity type') . ': "' . $type . '"');
	675	}
	676	}
	677	}
	678
	679	$values = self::getEntityValues($params['entityID'],
	680	$type,
	681	$fieldIDs
	682	);
	683	if (empty($values)) {
	684	// note that this behaviour is undesirable from an API point of view - it should return an empty array
	685	// since this is also called by the merger code & not sure the consequences of changing
	686	// are just handling undoing this in the api layer. ie. converting the error back into a success
	687	$result = array(
	688	'is_error' => 1,
	689	'error_message' => 'No values found for the specified entity ID and custom field(s).',
	690	);
	691	return $result;
	692	}
	693	else {
	694	$result = array(
	695	'is_error' => 0,
	696	'entityID' => $params['entityID'],
	697	);
	698	foreach ($values as $id => $value) {
	699	$result["custom_{$id}"] = $value;
	700	}
	701	return $result;
	702	}
	703	}
96025800	704
6a488035	705	}