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

<?php
/*
 +--------------------------------------------------------------------+
 | CiviCRM version 4.5                                                |
 +--------------------------------------------------------------------+
 | 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
   */
  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 the civicrm type string
   *
   * @param int $maxLength
   *
   * @return the mysql data store placeholder
   * @access public
   * @static
   */
  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
   */
  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);
    }
  }

  /**
   * @param array $params
   * @param $customFields
   * @param $entityTable
   * @param int $entityID
   * @param $customFieldExtends
   */
  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      $fields        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.
   * @access public
   * @static
   */
  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) {
              if($file[$tableName][$fieldID] == 'File') {
                if($fileid = $dao->$fieldName) {
                  $fileurl = CRM_Core_BAO_File::paperIconAttachment($tableName,$entityID);
                  $result["{$dao->id}"]["{$fieldID}"] = $fileurl[$dao->$fieldName];
                }
              }
              else {
                $result["{$dao->id}"]["{$fieldID}"] = $dao->$fieldName;
              }
            } else {
              $result["{$fieldID}_{$dao->id}"] = $dao->$fieldName;
            }
          }
          else {
            if($file[$tableName][$fieldID] == 'File') {
              if($fileid = $dao->$fieldName) {
                $fileurl = CRM_Core_BAO_File::paperIconAttachment($tableName,$entityID);
                $result[$fieldID] = $fileurl[$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
   * @static
   */
  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
   * @static
   */
  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	+--------------------------------------------------------------------+
06b69b18	4	\| CiviCRM version 4.5 \|
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	+--------------------------------------------------------------------+
	26	*/
	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	*/
6a488035 TO	42	static function create(&$customParams) {
	43	if (empty($customParams) \|\|
	44	!is_array($customParams)
	45	) {
	46	return;
	47	}
8ef12e64	48
6a488035 TO	49	foreach ($customParams as $tableName => $tables) {
	50	foreach ($tables as $index => $fields) {
	51	$sqlOP = NULL;
	52	$hookID = NULL;
	53	$hookOP = NULL;
	54	$entityID = NULL;
	55	$isMultiple = FALSE;
	56	$set = array();
	57	$params = array();
	58	$count = 1;
	59	foreach ($fields as $field) {
	60	if (!$sqlOP) {
	61	$entityID = $field['entity_id'];
	62	$hookID = $field['custom_group_id'];
	63	$isMultiple = $field['is_multiple'];
	64	if (array_key_exists('id', $field)) {
	65	$sqlOP = "UPDATE $tableName ";
	66	$where = " WHERE id = %{$count}";
	67	$params[$count] = array($field['id'], 'Integer');
	68	$count++;
	69	$hookOP = 'edit';
	70	}
	71	else {
	72	$sqlOP = "INSERT INTO $tableName ";
	73	$where = NULL;
	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
230	$fieldNames = implode(',', array_keys($set));
231	$fieldValues = implode(',', array_values($set));
232	$query = "$sqlOP ( $fieldNames ) VALUES ( $fieldValues )";
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	/**
100fef9d	254	* Given a field return the mysql data type associated with it
6a488035 TO	255	*
	256	* @param string $type the civicrm type string
	257	*
fd31fa4c EM	258	* @param int $maxLength
fd31fa4c EM	259	*
6a488035 TO	260	* @return the mysql data store placeholder
	261	* @access public
	262	* @static
	263	*/
	264	public static function fieldToSQLType($type, $maxLength = 255) {
	265	if (!isset($maxLength) \|\|
	266	!is_numeric($maxLength) \|\|
	267	$maxLength <= 0
	268	) {
	269	$maxLength = 255;
	270	}
	271
	272	switch ($type) {
	273	case 'String':
	274	case 'Link':
	275	return "varchar($maxLength)";
	276
	277	case 'Boolean':
	278	return 'tinyint';
	279
	280	case 'Int':
	281	return 'int';
	282	// the below three are FK's, and have constraints added to them
	283
	284	case 'ContactReference':
	285	case 'StateProvince':
	286	case 'Country':
	287	case 'File':
	288	return 'int unsigned';
	289
	290	case 'Float':
	291	return 'double';
	292
	293	case 'Money':
	294	return 'decimal(20,2)';
	295
	296	case 'Memo':
	297	case 'RichTextEditor':
	298	return 'text';
	299
	300	case 'Date':
	301	return 'datetime';
	302
	303	default:
	304	CRM_Core_Error::fatal();
	305	}
	306	}
	307
b5c2afd0	308	/**
c490a46a	309	* @param array $params
b5c2afd0	310	* @param $entityTable
100fef9d	311	* @param int $entityID
b5c2afd0	312	*/
6a488035 TO	313	static function store(&$params, $entityTable, $entityID) {
	314	$cvParams = array();
	315	foreach ($params as $fieldID => $param) {
	316	foreach ($param as $index => $customValue) {
	317	$cvParam = array(
	318	'entity_table' => $entityTable,
	319	'entity_id' => $entityID,
	320	'value' => $customValue['value'],
	321	'type' => $customValue['type'],
	322	'custom_field_id' => $customValue['custom_field_id'],
	323	'custom_group_id' => $customValue['custom_group_id'],
	324	'table_name' => $customValue['table_name'],
	325	'column_name' => $customValue['column_name'],
	326	'is_multiple' => CRM_Utils_Array::value('is_multiple', $customValue),
	327	'file_id' => $customValue['file_id'],
	328	);
	329
	330	// fix Date type to be timestamp, since that is how we store in db
	331	if ($cvParam['type'] == 'Date') {
	332	$cvParam['type'] = 'Timestamp';
	333	}
	334
a7488080	335	if (!empty($customValue['id'])) {
6a488035 TO	336	$cvParam['id'] = $customValue['id'];
	337	}
	338	if (!array_key_exists($customValue['table_name'], $cvParams)) {
	339	$cvParams[$customValue['table_name']] = array();
	340	}
	341
	342	if (!array_key_exists($index, $cvParams[$customValue['table_name']])) {
	343	$cvParams[$customValue['table_name']][$index] = array();
	344	}
	345
	346	$cvParams[$customValue['table_name']][$index][] = $cvParam;
	347	}
	348	}
	349	if (!empty($cvParams)) {
	350	self::create($cvParams);
	351	}
	352	}
	353
b5c2afd0	354	/**
c490a46a	355	* @param array $params
b5c2afd0 EM	356	* @param $customFields
b5c2afd0 EM	357	* @param $entityTable
100fef9d	358	* @param int $entityID
b5c2afd0 EM	359	* @param $customFieldExtends
b5c2afd0 EM	360	*/
6a488035 TO	361	static function postProcess(&$params, &$customFields, $entityTable, $entityID, $customFieldExtends) {
	362	$customData = CRM_Core_BAO_CustomField::postProcess($params,
	363	$customFields,
	364	$entityID,
	365	$customFieldExtends
	366	);
	367
	368	if (!empty($customData)) {
	369	self::store($customData, $entityTable, $entityID);
	370	}
	371	}
	372
	373	/**
	374	* Return an array of all custom values associated with an entity.
	375	*
77b97be7 EM	376	* @param int $entityID Identification number of the entity
77b97be7 EM	377	* @param string $entityType Type of entity that the entityID corresponds to, specified
6a488035 TO	378	* as a string with format "'<EntityName>'". Comma separated
	379	* list may be used to specify OR matches. Allowable values
	380	* are enumerated types in civicrm_custom_group.extends field.
	381	* Optional. Default value assumes entityID references a
	382	* contact entity.
77b97be7	383	* @param array $fieldIDs optional list of fieldIDs that we want to retrieve. If this
6a488035 TO	384	* is set the entityType is ignored
6a488035 TO	385	*
77b97be7 EM	386	* @param bool $formatMultiRecordField
77b97be7 EM	387	*
6a488035 TO	388	* @return array $fields Array of custom values for the entity with key=>value
	389	* pairs specified as civicrm_custom_field.id => custom value.
	390	* Empty array if no custom values found.
	391	* @access public
	392	* @static
	393	*/
	394	public static function &getEntityValues($entityID, $entityType = NULL, $fieldIDs = NULL, $formatMultiRecordField = FALSE) {
	395	if (!$entityID) {
	396	// adding this here since an empty contact id could have serious repurcussions
	397	// like looping forever
	398	CRM_Core_Error::fatal('Please file an issue with the backtrace');
	399	return NULL;
	400	}
	401
	402	$cond = array();
	403	if ($entityType) {
	404	$cond[] = "cg.extends IN ( '$entityType' )";
	405	}
	406	if ($fieldIDs &&
	407	is_array($fieldIDs)
	408	) {
	409	$fieldIDList = implode(',', $fieldIDs);
	410	$cond[] = "cf.id IN ( $fieldIDList )";
	411	}
	412	if (empty($cond)) {
	413	$cond[] = "cg.extends IN ( 'Contact', 'Individual', 'Household', 'Organization' )";
	414	}
	415	$cond = implode(' AND ', $cond);
	416
	417	// first find all the fields that extend this type of entity
	418	$query = "
	419	SELECT cg.table_name,
	420	cg.id as groupID,
	421	cg.is_multiple,
	422	cf.column_name,
34f51a07 N	423	cf.id as fieldID,
34f51a07 N	424	cf.data_type as fieldDataType
6a488035 TO	425	FROM civicrm_custom_group cg,
	426	civicrm_custom_field cf
	427	WHERE cf.custom_group_id = cg.id
	428	AND cg.is_active = 1
	429	AND cf.is_active = 1
	430	AND $cond
	431	";
	432	$dao = CRM_Core_DAO::executeQuery($query);
	433
	434	$select = $fields = $isMultiple = array();
	435
	436	while ($dao->fetch()) {
	437	if (!array_key_exists($dao->table_name, $select)) {
	438	$fields[$dao->table_name] = array();
	439	$select[$dao->table_name] = array();
	440	}
	441	$fields[$dao->table_name][] = $dao->fieldID;
	442	$select[$dao->table_name][] = "{$dao->column_name} AS custom_{$dao->fieldID}";
	443	$isMultiple[$dao->table_name] = $dao->is_multiple ? TRUE : FALSE;
77b97be7	444	$file[$dao->table_name][$dao->fieldID] = $dao->fieldDataType;
6a488035 TO	445	}
	446
	447	$result = array();
	448	foreach ($select as $tableName => $clauses) {
	449	$query = "SELECT id, " . implode(', ', $clauses) . " FROM $tableName WHERE entity_id = $entityID";
	450	$dao = CRM_Core_DAO::executeQuery($query);
	451	while ($dao->fetch()) {
	452	foreach ($fields[$tableName] as $fieldID) {
	453	$fieldName = "custom_{$fieldID}";
	454	if ($isMultiple[$tableName]) {
	455	if ($formatMultiRecordField) {
34f51a07 N	456	if($file[$tableName][$fieldID] == 'File') {
	457	if($fileid = $dao->$fieldName) {
	458	$fileurl = CRM_Core_BAO_File::paperIconAttachment($tableName,$entityID);
	459	$result["{$dao->id}"]["{$fieldID}"] = $fileurl[$dao->$fieldName];
	460	}
	461	}
	462	else {
	463	$result["{$dao->id}"]["{$fieldID}"] = $dao->$fieldName;
	464	}
6a488035 TO	465	} else {
	466	$result["{$fieldID}_{$dao->id}"] = $dao->$fieldName;
	467	}
	468	}
	469	else {
34f51a07 N	470	if($file[$tableName][$fieldID] == 'File') {
	471	if($fileid = $dao->$fieldName) {
	472	$fileurl = CRM_Core_BAO_File::paperIconAttachment($tableName,$entityID);
	473	$result[$fieldID] = $fileurl[$dao->$fieldName];
	474	}
	475	}
	476	else {
77b97be7	477	$result[$fieldID] = $dao->$fieldName;
34f51a07	478	}
6a488035 TO	479	}
	480	}
	481	}
	482	}
	483	return $result;
	484	}
	485
	486	/**
100fef9d	487	* Take in an array of entityID, custom_XXX => value
6a488035 TO	488	* and set the value in the appropriate table. Should also be able
	489	* to set the value to null. Follows api parameter/return conventions
	490	*
	491	* @array $params
	492	*
c490a46a	493	* @param array $params
2a6da8d7 EM	494	*
2a6da8d7 EM	495	* @throws Exception
6a488035 TO	496	* @return array
	497	* @static
	498	*/
	499	static function setValues(&$params) {
	500
	501	if (!isset($params['entityID']) \|\|
	502	CRM_Utils_Type::escape($params['entityID'], 'Integer', FALSE) === NULL
	503	) {
	504	return CRM_Core_Error::createAPIError(ts('entityID needs to be set and of type Integer'));
	505	}
	506
	507	// first collect all the id/value pairs. The format is:
	508	// custom_X => value or custom_X_VALUEID => value (for multiple values), VALUEID == -1, -2 etc for new insertions
	509	$values = array();
	510	$fieldValues = array();
	511	foreach ($params as $n => $v) {
	512	if ($customFieldInfo = CRM_Core_BAO_CustomField::getKeyID($n, TRUE)) {
	513	$fieldID = (int ) $customFieldInfo[0];
	514	if (CRM_Utils_Type::escape($fieldID, 'Integer', FALSE) === NULL) {
	515	return CRM_Core_Error::createAPIError(ts('field ID needs to be of type Integer for index %1',
	516	array(1 => $fieldID)
	517	));
	518	}
	519	if (!array_key_exists($fieldID, $fieldValues)) {
	520	$fieldValues[$fieldID] = array();
	521	}
	522	$id = -1;
	523	if ($customFieldInfo[1]) {
	524	$id = (int ) $customFieldInfo[1];
	525	}
	526	$fieldValues[$fieldID][] = array(
	527	'value' => $v,
	528	'id' => $id,
	529	);
	530	}
	531	}
	532
	533	$fieldIDList = implode(',', array_keys($fieldValues));
	534
	535	// format it so that we can just use create
	536	$sql = "
	537	SELECT cg.table_name as table_name ,
	538	cg.id as cg_id ,
	539	cg.is_multiple as is_multiple,
	540	cf.column_name as column_name,
	541	cf.id as cf_id ,
	542	cf.data_type as data_type
	543	FROM civicrm_custom_group cg,
	544	civicrm_custom_field cf
	545	WHERE cf.custom_group_id = cg.id
	546	AND cf.id IN ( $fieldIDList )
	547	";
	548
	549	$dao = CRM_Core_DAO::executeQuery($sql);
	550	$cvParams = array();
	551
	552	while ($dao->fetch()) {
	553	$dataType = $dao->data_type == 'Date' ? 'Timestamp' : $dao->data_type;
	554	foreach ($fieldValues[$dao->cf_id] as $fieldValue) {
	555	// Format null values correctly
	556	if ($fieldValue['value'] === NULL \|\| $fieldValue['value'] === '') {
	557	switch ($dataType) {
	558	case 'String':
	559	case 'Int':
560	case 'Link':
561	case 'Boolean':
562	$fieldValue['value'] = '';
563	break;
564
565	case 'Timestamp':
566	$fieldValue['value'] = NULL;
567	break;
568
569	case 'StateProvince':
570	case 'Country':
571	case 'Money':
572	case 'Float':
573	$fieldValue['value'] = (int)0;
574	break;
575	}
576	}
577	// Ensure that value is of the right data type
578	elseif (CRM_Utils_Type::escape($fieldValue['value'], $dataType, FALSE) === NULL) {
579	return CRM_Core_Error::createAPIError(ts('value: %1 is not of the right field data type: %2',
580	array(
581	1 => $fieldValue['value'],
582	2 => $dao->data_type
583	)
584	));
585	}
586
587	$cvParam = array(
588	'entity_id' => $params['entityID'],
589	'value' => $fieldValue['value'],
590	'type' => $dataType,
591	'custom_field_id' => $dao->cf_id,
592	'custom_group_id' => $dao->cg_id,
593	'table_name' => $dao->table_name,
594	'column_name' => $dao->column_name,
595	'is_multiple' => $dao->is_multiple,
596	);
597
e7dcccf0 CW	598	if ($cvParam['type'] == 'File') {
	599	$cvParam['file_id'] = $fieldValue['value'];
	600	}
	601
6a488035 TO	602	if (!array_key_exists($dao->table_name, $cvParams)) {
	603	$cvParams[$dao->table_name] = array();
	604	}
	605
	606	if (!array_key_exists($fieldValue['id'], $cvParams[$dao->table_name])) {
	607	$cvParams[$dao->table_name][$fieldValue['id']] = array();
	608	}
	609
	610	if ($fieldValue['id'] > 0) {
	611	$cvParam['id'] = $fieldValue['id'];
	612	}
	613	$cvParams[$dao->table_name][$fieldValue['id']][] = $cvParam;
	614	}
	615	}
	616
	617	if (!empty($cvParams)) {
	618	self::create($cvParams);
	619	return array('is_error' => 0, 'result' => 1);
	620	}
	621
	622	return CRM_Core_Error::createAPIError(ts('Unknown error'));
	623	}
	624
	625	/**
100fef9d	626	* Take in an array of entityID, custom_ID
6a488035 TO	627	* and gets the value from the appropriate table.
	628	*
	629	* To get the values of custom fields with IDs 13 and 43 for contact ID 1327, use:
	630	* $params = array( 'entityID' => 1327, 'custom_13' => 1, 'custom_43' => 1 );
	631	*
	632	* Entity Type will be infered by the custom fields you request
	633	* Specify $params['entityType'] if you do not supply any custom fields to return
	634	* and entity type is other than Contact
	635	*
	636	* @array $params
	637	*
c490a46a	638	* @param array $params
2a6da8d7 EM	639	*
2a6da8d7 EM	640	* @throws Exception
6a488035 TO	641	* @return array
	642	* @static
	643	*/
	644	static function &getValues(&$params) {
	645	if (empty($params)) {
	646	return NULL;
	647	}
	648	if (!isset($params['entityID']) \|\|
	649	CRM_Utils_Type::escape($params['entityID'],
	650	'Integer', FALSE
	651	) === NULL
	652	) {
	653	return CRM_Core_Error::createAPIError(ts('entityID needs to be set and of type Integer'));
	654	}
	655
	656	// first collect all the ids. The format is:
	657	// custom_ID
	658	$fieldIDs = array();
	659	foreach ($params as $n => $v) {
	660	$key = $idx = NULL;
	661	if (substr($n, 0, 7) == 'custom_') {
	662	$idx = substr($n, 7);
	663	if (CRM_Utils_Type::escape($idx, 'Integer', FALSE) === NULL) {
	664	return CRM_Core_Error::createAPIError(ts('field ID needs to be of type Integer for index %1',
	665	array(1 => $idx)
	666	));
	667	}
	668	$fieldIDs[] = (int ) $idx;
	669	}
	670	}
	671
	672	$default = array('Contact', 'Individual', 'Household', 'Organization');
	673	if (!($type = CRM_Utils_Array::value('entityType', $params)) \|\|
	674	in_array($params['entityType'], $default)
	675	) {
	676	$type = NULL;
	677	}
	678	else {
	679	$entities = CRM_Core_SelectValues::customGroupExtends();
	680	if (!array_key_exists($type, $entities)) {
	681	if (in_array($type, $entities)) {
	682	$type = $entities[$type];
	683	if (in_array($type, $default)) {
	684	$type = NULL;
	685	}
	686	}
	687	else {
	688	return CRM_Core_Error::createAPIError(ts('Invalid entity type') . ': "' . $type . '"');
	689	}
	690	}
	691	}
	692
	693	$values = self::getEntityValues($params['entityID'],
	694	$type,
	695	$fieldIDs
	696	);
	697	if (empty($values)) {
	698	// note that this behaviour is undesirable from an API point of view - it should return an empty array
	699	// since this is also called by the merger code & not sure the consequences of changing
	700	// are just handling undoing this in the api layer. ie. converting the error back into a success
	701	$result = array(
	702	'is_error' => 1,
	703	'error_message' => 'No values found for the specified entity ID and custom field(s).',
	704	);
705	return $result;
706	}
707	else {
708	$result = array(
709	'is_error' => 0,
710	'entityID' => $params['entityID'],
711	);
712	foreach ($values as $id => $value) {
713	$result["custom_{$id}"] = $value;
714	}
715	return $result;
716	}
717	}
718	}
719