/**
* When changing the value of an option this is called to update all corresponding custom data
*
* @param int $optionId
* @param string $newValue
*/
public static function updateValue($optionId, $newValue)
{
$optionValue = new CRM_Core_DAO_OptionValue();
$optionValue->id = $optionId;
$optionValue->find(TRUE);
$oldValue = $optionValue->value;
if ($oldValue == $newValue) {
return;
}
$customField = new CRM_Core_DAO_CustomField();
$customField->option_group_id = $optionValue->option_group_id;
$customField->find();
while ($customField->fetch()) {
$customGroup = new CRM_Core_DAO_CustomGroup();
$customGroup->id = $customField->custom_group_id;
$customGroup->find(TRUE);
if (CRM_Core_BAO_CustomField::isSerialized($customField)) {
$params = array(1 => array(CRM_Utils_Array::implodePadded($oldValue), 'String'), 2 => array(CRM_Utils_Array::implodePadded($newValue), 'String'), 3 => array('%' . CRM_Utils_Array::implodePadded($oldValue) . '%', 'String'));
} else {
$params = array(1 => array($oldValue, 'String'), 2 => array($newValue, 'String'), 3 => array($oldValue, 'String'));
}
$sql = "UPDATE `{$customGroup->table_name}` SET `{$customField->column_name}` = REPLACE(`{$customField->column_name}`, %1, %2) WHERE `{$customField->column_name}` LIKE %3";
$customGroup->free();
CRM_Core_DAO::executeQuery($sql, $params);
}
$customField->free();
}
/**
* Add custom data to the columns.
*
* @param bool $addFields
* @param array $permCustomGroupIds
*/
public function addCustomDataToColumns($addFields = TRUE, $permCustomGroupIds = array())
{
if (empty($this->_customGroupExtends)) {
return;
}
if (!is_array($this->_customGroupExtends)) {
$this->_customGroupExtends = array($this->_customGroupExtends);
}
$customGroupWhere = '';
if (!empty($permCustomGroupIds)) {
$customGroupWhere = "cg.id IN (" . implode(',', $permCustomGroupIds) . ") AND";
}
$sql = "\nSELECT cg.table_name, cg.title, cg.extends, cf.id as cf_id, cf.label,\n cf.column_name, cf.data_type, cf.html_type, cf.option_group_id, cf.time_format\nFROM civicrm_custom_group cg\nINNER JOIN civicrm_custom_field cf ON cg.id = cf.custom_group_id\nWHERE cg.extends IN ('" . implode("','", $this->_customGroupExtends) . "') AND\n {$customGroupWhere}\n cg.is_active = 1 AND\n cf.is_active = 1 AND\n cf.is_searchable = 1\nORDER BY cg.weight, cf.weight";
$customDAO = CRM_Core_DAO::executeQuery($sql);
$curTable = NULL;
while ($customDAO->fetch()) {
if ($customDAO->table_name != $curTable) {
$curTable = $customDAO->table_name;
$curFields = $curFilters = array();
// dummy dao object
$this->_columns[$curTable]['dao'] = 'CRM_Contact_DAO_Contact';
$this->_columns[$curTable]['extends'] = $customDAO->extends;
$this->_columns[$curTable]['grouping'] = $customDAO->table_name;
$this->_columns[$curTable]['group_title'] = $customDAO->title;
foreach (array('fields', 'filters', 'group_bys') as $colKey) {
if (!array_key_exists($colKey, $this->_columns[$curTable])) {
$this->_columns[$curTable][$colKey] = array();
}
}
}
$fieldName = 'custom_' . $customDAO->cf_id;
if ($addFields) {
// this makes aliasing work in favor
$curFields[$fieldName] = array('name' => $customDAO->column_name, 'title' => $customDAO->label, 'dataType' => $customDAO->data_type, 'htmlType' => $customDAO->html_type);
}
if ($this->_customGroupFilters) {
// this makes aliasing work in favor
$curFilters[$fieldName] = array('name' => $customDAO->column_name, 'title' => $customDAO->label, 'dataType' => $customDAO->data_type, 'htmlType' => $customDAO->html_type);
}
switch ($customDAO->data_type) {
case 'Date':
// filters
$curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_DATE;
$curFilters[$fieldName]['type'] = CRM_Utils_Type::T_DATE;
// CRM-6946, show time part for datetime date fields
if ($customDAO->time_format) {
$curFields[$fieldName]['type'] = CRM_Utils_Type::T_TIMESTAMP;
}
break;
case 'Boolean':
$curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_SELECT;
$curFilters[$fieldName]['options'] = array('' => ts('- select -')) + CRM_Core_PseudoConstant::get('CRM_Core_BAO_CustomField', 'custom_' . $customDAO->cf_id, array(), 'search');
$curFilters[$fieldName]['type'] = CRM_Utils_Type::T_INT;
break;
case 'Int':
$curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_INT;
$curFilters[$fieldName]['type'] = CRM_Utils_Type::T_INT;
break;
case 'Money':
$curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_FLOAT;
$curFilters[$fieldName]['type'] = CRM_Utils_Type::T_MONEY;
$curFields[$fieldName]['type'] = CRM_Utils_Type::T_MONEY;
break;
case 'Float':
$curFilters[$fieldName]['operatorType'] = CRM_Report_Form::OP_FLOAT;
$curFilters[$fieldName]['type'] = CRM_Utils_Type::T_FLOAT;
break;
case 'String':
case 'StateProvince':
case 'Country':
$curFilters[$fieldName]['type'] = CRM_Utils_Type::T_STRING;
$options = CRM_Core_PseudoConstant::get('CRM_Core_BAO_CustomField', 'custom_' . $customDAO->cf_id, array(), 'search');
if ($options !== FALSE) {
$curFilters[$fieldName]['operatorType'] = CRM_Core_BAO_CustomField::isSerialized($customDAO) ? CRM_Report_Form::OP_MULTISELECT_SEPARATOR : CRM_Report_Form::OP_MULTISELECT;
$curFilters[$fieldName]['options'] = $options;
}
break;
case 'ContactReference':
$curFilters[$fieldName]['type'] = CRM_Utils_Type::T_STRING;
$curFilters[$fieldName]['name'] = 'display_name';
$curFilters[$fieldName]['alias'] = "contact_{$fieldName}_civireport";
$curFields[$fieldName]['type'] = CRM_Utils_Type::T_STRING;
$curFields[$fieldName]['name'] = 'display_name';
$curFields[$fieldName]['alias'] = "contact_{$fieldName}_civireport";
break;
default:
$curFields[$fieldName]['type'] = CRM_Utils_Type::T_STRING;
$curFilters[$fieldName]['type'] = CRM_Utils_Type::T_STRING;
}
if (!array_key_exists('type', $curFields[$fieldName])) {
$curFields[$fieldName]['type'] = CRM_Utils_Array::value('type', $curFilters[$fieldName], array());
}
if ($addFields) {
$this->_columns[$curTable]['fields'] = array_merge($this->_columns[$curTable]['fields'], $curFields);
}
if ($this->_customGroupFilters) {
$this->_columns[$curTable]['filters'] = array_merge($this->_columns[$curTable]['filters'], $curFilters);
}
if ($this->_customGroupGroupBy) {
$this->_columns[$curTable]['group_bys'] = array_merge($this->_columns[$curTable]['group_bys'], $curFields);
}
}
}
/**
* Generate the where clause and also the english language.
* equivalent
*
* @return void
*/
public function where()
{
foreach ($this->_ids as $id => $values) {
// Fixed for Isuue CRM 607
if (CRM_Utils_Array::value($id, $this->_fields) === NULL || !$values) {
continue;
}
$strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower';
foreach ($values as $tuple) {
list($name, $op, $value, $grouping, $wildcard) = $tuple;
$field = $this->_fields[$id];
$fieldName = "{$field['table_name']}.{$field['column_name']}";
// Autocomplete comes back as a string not an array
if ($field['data_type'] == 'String' && $field['html_type'] == 'Autocomplete-Select' && $op == '=') {
$value = explode(',', $value);
}
$isSerialized = CRM_Core_BAO_CustomField::isSerialized($field);
// fix $value here to escape sql injection attacks
$qillValue = NULL;
if (!is_array($value)) {
$value = CRM_Core_DAO::escapeString(trim($value));
$qillValue = CRM_Core_BAO_CustomField::getDisplayValue($value, $id, $this->_options);
} elseif (count($value) && in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
$op = key($value);
$qillValue = CRM_Core_BAO_CustomField::getDisplayValue($value[$op], $id, $this->_options);
} else {
$op = 'IN';
$qillValue = CRM_Core_BAO_CustomField::getDisplayValue($value, $id, $this->_options);
}
$qillOp = CRM_Utils_Array::value($op, CRM_Core_SelectValues::getSearchBuilderOperators(), $op);
switch ($field['data_type']) {
case 'String':
case 'StateProvince':
case 'Country':
if ($field['is_search_range'] && is_array($value)) {
$this->searchRange($field['id'], $field['label'], $field['data_type'], $fieldName, $value, $grouping);
} else {
// fix $value here to escape sql injection attacks
if (!is_array($value)) {
if ($field['data_type'] == 'String') {
$value = CRM_Utils_Type::escape($strtolower($value), 'String');
} else {
$value = CRM_Utils_Type::escape($value, 'Integer');
}
} elseif ($isSerialized) {
if (in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
$op = key($value);
$value = $value[$op];
}
$value = implode(',', $value);
}
// CRM-14563,CRM-16575 : Special handling of multi-select custom fields
if ($isSerialized && !empty($value)) {
if (strstr($op, 'IN')) {
$value = str_replace(",", "[[:cntrl:]]*|[[:cntrl:]]*", $value);
$value = str_replace('(', '[[.left-parenthesis.]]', $value);
$value = str_replace(')', '[[.right-parenthesis.]]', $value);
}
$op = strstr($op, '!') || strstr($op, 'NOT') ? 'NOT RLIKE' : 'RLIKE';
$value = "[[:cntrl:]]*" . $value . "[[:cntrl:]]*";
if (!$wildcard) {
$value = str_replace("[[:cntrl:]]*|", '', $value);
}
}
//FIX for custom data query fired against no value(NULL/NOT NULL)
$this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'String');
$this->_qill[$grouping][] = "{$field['label']} {$qillOp} {$qillValue}";
}
break;
case 'ContactReference':
$label = $value ? CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_Contact', $value, 'sort_name') : '';
$this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'String');
$this->_qill[$grouping][] = $field['label'] . " {$qillOp} {$label}";
break;
case 'Int':
if ($field['is_search_range'] && is_array($value)) {
$this->searchRange($field['id'], $field['label'], $field['data_type'], $fieldName, $value, $grouping);
} else {
$this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'Integer');
$this->_qill[$grouping][] = ts("%1 %2 %3", array(1 => $field['label'], 2 => $qillOp, 3 => $qillValue));
}
break;
case 'Boolean':
if (!is_array($value)) {
if (strtolower($value) == 'yes' || strtolower($value) == strtolower(ts('Yes'))) {
$value = 1;
} else {
$value = (int) $value;
}
$value = $value == 1 ? 1 : 0;
$qillValue = $value ? 'Yes' : 'No';
}
$this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'Integer');
$this->_qill[$grouping][] = ts("%1 %2 %3", array(1 => $field['label'], 2 => $qillOp, 3 => $qillValue));
break;
case 'Link':
case 'Memo':
$this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'String');
$this->_qill[$grouping][] = ts("%1 %2 %3", array(1 => $field['label'], 2 => $qillOp, 3 => $qillValue));
break;
case 'Money':
if (is_array($value)) {
$value = CRM_Utils_Array::value($op, $value, $value);
foreach ($value as $key => $val) {
$moneyFormat = CRM_Utils_Rule::cleanMoney($value[$key]);
$value[$key] = $moneyFormat;
}
} else {
$value = CRM_Utils_Rule::cleanMoney($value);
}
case 'Float':
if ($field['is_search_range']) {
$this->searchRange($field['id'], $field['label'], $field['data_type'], $fieldName, $value, $grouping);
} else {
$this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'Float');
$this->_qill[$grouping][] = ts("%1 %2 %3", array(1 => $field['label'], 2 => $qillOp, 3 => $qillValue));
}
break;
case 'Date':
$fromValue = CRM_Utils_Array::value('from', $value);
$toValue = CRM_Utils_Array::value('to', $value);
if (!$fromValue && !$toValue) {
if (!CRM_Utils_Date::processDate($value) && !in_array($op, array('IS NULL', 'IS NOT NULL', 'IS EMPTY', 'IS NOT EMPTY'))) {
continue;
}
// hack to handle yy format during search
if (is_numeric($value) && strlen($value) == 4) {
$value = "01-01-{$value}";
}
$date = CRM_Utils_Date::processDate($value);
$this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $date, 'String');
$this->_qill[$grouping][] = $field['label'] . " {$qillOp} " . CRM_Utils_Date::customFormat($date);
} else {
if (is_numeric($fromValue) && strlen($fromValue) == 4) {
$fromValue = "01-01-{$fromValue}";
}
if (is_numeric($toValue) && strlen($toValue) == 4) {
$toValue = "01-01-{$toValue}";
}
// TO DO: add / remove time based on date parts
$fromDate = CRM_Utils_Date::processDate($fromValue);
$toDate = CRM_Utils_Date::processDate($toValue);
if (!$fromDate && !$toDate) {
continue;
}
if ($fromDate) {
$this->_where[$grouping][] = "{$fieldName} >= {$fromDate}";
$this->_qill[$grouping][] = $field['label'] . ' >= ' . CRM_Utils_Date::customFormat($fromDate);
}
if ($toDate) {
$this->_where[$grouping][] = "{$fieldName} <= {$toDate}";
$this->_qill[$grouping][] = $field['label'] . ' <= ' . CRM_Utils_Date::customFormat($toDate);
}
}
break;
case 'File':
if ($op == 'IS NULL' || $op == 'IS NOT NULL' || $op == 'IS EMPTY' || $op == 'IS NOT EMPTY') {
switch ($op) {
case 'IS EMPTY':
$op = 'IS NULL';
break;
case 'IS NOT EMPTY':
$op = 'IS NOT NULL';
break;
}
$this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op);
$this->_qill[$grouping][] = $field['label'] . " {$qillOp} ";
}
break;
}
}
}
}
/**
* generate the where clause and also the english language
* equivalent
*
* @param NULL
*
* @return void
*
* @access public
*/
function where()
{
foreach ($this->_ids as $id => $values) {
// Fixed for Isuue CRM 607
if (CRM_Utils_Array::value($id, $this->_fields) === NULL || !$values) {
continue;
}
$strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower';
foreach ($values as $tuple) {
list($name, $op, $value, $grouping, $wildcard) = $tuple;
$field = $this->_fields[$id];
$fieldName = "{$field['table_name']}.{$field['column_name']}";
// Autocomplete comes back as a string not an array
if ($field['data_type'] == 'String' && $field['html_type'] == 'Autocomplete-Select' && $op == '=') {
$value = explode(',', $value);
}
// Handle multi-select search for any data type
if (is_array($value) && !$field['is_search_range']) {
$isSerialized = CRM_Core_BAO_CustomField::isSerialized($field);
$wildcard = $isSerialized ? $wildcard : TRUE;
$options = CRM_Utils_Array::value('values', civicrm_api3('contact', 'getoptions', array('field' => $name, 'context' => 'search'), array()));
$qillValue = '';
$sqlOP = $wildcard ? ' OR ' : ' AND ';
$sqlValue = array();
foreach ($value as $num => &$v) {
$sep = count($value) > 1 + $num ? ', ' : ' ' . ($wildcard ? ts('OR') : ts('AND')) . ' ';
$qillValue .= ($num ? $sep : '') . $options[$v];
$v = CRM_Core_DAO::escapeString($v);
if ($isSerialized) {
$sqlValue[] = "( {$fieldName} like '%" . CRM_Core_DAO::VALUE_SEPARATOR . $v . CRM_Core_DAO::VALUE_SEPARATOR . "%' ) ";
} else {
$v = "'{$v}'";
}
}
if (!$isSerialized) {
$sqlValue = array("{$fieldName} IN (" . implode(',', $value) . ")");
}
$this->_where[$grouping][] = ' ( ' . implode($sqlOP, $sqlValue) . ' ) ';
$this->_qill[$grouping][] = "{$field['label']} {$op} {$qillValue}";
continue;
}
// fix $value here to escape sql injection attacks
if (!is_array($value)) {
$value = CRM_Core_DAO::escapeString(trim($value));
}
$qillValue = CRM_Core_BAO_CustomField::getDisplayValue($value, $id, $this->_options);
switch ($field['data_type']) {
case 'String':
$sql = "{$fieldName}";
if ($field['is_search_range'] && is_array($value)) {
$this->searchRange($field['id'], $field['label'], $field['data_type'], $fieldName, $value, $grouping);
} else {
$val = CRM_Utils_Type::escape($strtolower(trim($value)), 'String');
if ($wildcard) {
$val = $strtolower(CRM_Core_DAO::escapeString($val));
$val = "%{$val}%";
$op = 'LIKE';
}
//FIX for custom data query fired against no value(NULL/NOT NULL)
$this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($sql, $op, $val, $field['data_type']);
$this->_qill[$grouping][] = "{$field['label']} {$op} {$qillValue}";
}
break;
case 'ContactReference':
$label = $value ? CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_Contact', $value, 'sort_name') : '';
$this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'String');
$this->_qill[$grouping][] = $field['label'] . " {$op} {$label}";
break;
case 'Int':
if ($field['is_search_range'] && is_array($value)) {
$this->searchRange($field['id'], $field['label'], $field['data_type'], $fieldName, $value, $grouping);
} else {
$this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'Integer');
$this->_qill[$grouping][] = $field['label'] . " {$op} {$value}";
}
break;
case 'Boolean':
if (strtolower($value) == 'yes' || strtolower($value) == strtolower(ts('Yes'))) {
$value = 1;
} else {
$value = (int) $value;
}
$value = $value == 1 ? 1 : 0;
$this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'Integer');
$value = $value ? ts('Yes') : ts('No');
$this->_qill[$grouping][] = $field['label'] . " {$op} {$value}";
break;
case 'Link':
$this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'String');
$this->_qill[$grouping][] = $field['label'] . " {$op} {$value}";
break;
case 'Float':
if ($field['is_search_range'] && is_array($value)) {
$this->searchRange($field['id'], $field['label'], $field['data_type'], $fieldName, $value, $grouping);
} else {
$this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'Float');
$this->_qill[$grouping][] = $field['label'] . " {$op} {$value}";
}
break;
case 'Money':
if ($field['is_search_range'] && is_array($value)) {
foreach ($value as $key => $val) {
$moneyFormat = CRM_Utils_Rule::cleanMoney($value[$key]);
$value[$key] = $moneyFormat;
}
$this->searchRange($field['id'], $field['label'], $field['data_type'], $fieldName, $value, $grouping);
} else {
$moneyFormat = CRM_Utils_Rule::cleanMoney($value);
$value = $moneyFormat;
$this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'Float');
$this->_qill[$grouping][] = $field['label'] . " {$op} {$value}";
}
break;
case 'Memo':
$this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'String');
$this->_qill[$grouping][] = "{$field['label']} {$op} {$value}";
break;
case 'Date':
$fromValue = CRM_Utils_Array::value('from', $value);
$toValue = CRM_Utils_Array::value('to', $value);
if (!$fromValue && !$toValue) {
if (!CRM_Utils_Date::processDate($value) && $op != 'IS NULL' && $op != 'IS NOT NULL') {
continue;
}
// hack to handle yy format during search
if (is_numeric($value) && strlen($value) == 4) {
$value = "01-01-{$value}";
}
$date = CRM_Utils_Date::processDate($value);
$this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $date, 'String');
$this->_qill[$grouping][] = $field['label'] . " {$op} " . CRM_Utils_Date::customFormat($date);
} else {
if (is_numeric($fromValue) && strlen($fromValue) == 4) {
$fromValue = "01-01-{$fromValue}";
}
if (is_numeric($toValue) && strlen($toValue) == 4) {
$toValue = "01-01-{$toValue}";
}
// TO DO: add / remove time based on date parts
$fromDate = CRM_Utils_Date::processDate($fromValue);
$toDate = CRM_Utils_Date::processDate($toValue);
if (!$fromDate && !$toDate) {
continue;
}
if ($fromDate) {
$this->_where[$grouping][] = "{$fieldName} >= {$fromDate}";
$this->_qill[$grouping][] = $field['label'] . ' >= ' . CRM_Utils_Date::customFormat($fromDate);
}
if ($toDate) {
$this->_where[$grouping][] = "{$fieldName} <= {$toDate}";
$this->_qill[$grouping][] = $field['label'] . ' <= ' . CRM_Utils_Date::customFormat($toDate);
}
}
break;
case 'StateProvince':
case 'Country':
$this->_where[$grouping][] = "{$fieldName} {$op} " . CRM_Utils_Type::escape($value, 'Int');
$this->_qill[$grouping][] = $field['label'] . " {$op} {$qillValue}";
break;
case 'File':
if ($op == 'IS NULL' || $op == 'IS NOT NULL' || $op == 'IS EMPTY' || $op == 'IS NOT EMPTY') {
switch ($op) {
case 'IS EMPTY':
$op = 'IS NULL';
break;
case 'IS NOT EMPTY':
$op = 'IS NOT NULL';
break;
}
$this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op);
$this->_qill[$grouping][] = $field['label'] . " {$op} ";
}
break;
}
}
}
}
public function _testCustomValue($customField, $sqlOps, $type)
{
$isSerialized = CRM_Core_BAO_CustomField::isSerialized($customField);
$customId = $customField['id'];
$params = array('contact_type' => 'Individual', 'email' => substr(sha1(rand()), 0, 7) . '*****@*****.**');
$result = $this->callAPISuccess('Contact', 'create', $params);
$contactId = $result['id'];
$count = rand(1, 2);
if ($isSerialized) {
$selectedValue = $this->optionGroup[$type]['values'];
$notselectedValue = $selectedValue[$count];
unset($selectedValue[$count]);
} elseif ($customField['html_type'] == 'Link') {
$selectedValue = "http://" . substr(sha1(rand()), 0, 7) . ".com";
$notselectedValue = "http://" . substr(sha1(rand()), 0, 7) . ".com";
} elseif ($type == 'date') {
$selectedValue = date('Ymd');
$notselectedValue = $lesserSelectedValue = date('Ymd', strtotime('yesterday'));
$greaterSelectedValue = date('Ymd', strtotime('+ 1 day'));
} elseif ($type == 'contact') {
$selectedValue = $this->optionGroup[$type]['values'][1];
$notselectedValue = $this->optionGroup[$type]['values'][0];
} elseif ($type == 'boolean') {
$selectedValue = 1;
$notselectedValue = 0;
} else {
$selectedValue = $this->optionGroup[$type]['values'][0];
$notselectedValue = $this->optionGroup[$type]['values'][$count];
if (in_array(">", $sqlOps)) {
$greaterSelectedValue = $selectedValue + 1;
$lesserSelectedValue = $selectedValue - 1;
}
}
$params = array('entity_id' => $contactId, 'custom_' . $customId => $selectedValue);
$this->callAPISuccess('CustomValue', 'create', $params);
foreach ($sqlOps as $op) {
$qillOp = CRM_Utils_Array::value($op, CRM_Core_SelectValues::getSearchBuilderOperators(), $op);
switch ($op) {
case '=':
$result = $this->callAPISuccess('Contact', 'Get', array('custom_' . $customId => is_array($selectedValue) ? implode(CRM_Core_DAO::VALUE_SEPARATOR, $selectedValue) : $selectedValue));
$this->assertEquals($contactId, $result['id']);
break;
case '!=':
$result = $this->callAPISuccess('Contact', 'Get', array('custom_' . $customId => array($op => $notselectedValue)));
$this->assertEquals(TRUE, array_key_exists($contactId, $result['values']));
break;
case '>':
case '<':
case '>=':
case '<=':
if ($isSerialized) {
continue;
}
// To be precise in for these operator we can't just rely on one contact,
// hence creating multiple contact with custom value less/more then $selectedValue respectively
$result = $this->callAPISuccess('Contact', 'create', array('contact_type' => 'Individual', 'email' => substr(sha1(rand()), 0, 7) . '*****@*****.**'));
$contactId2 = $result['id'];
$this->callAPISuccess('CustomValue', 'create', array('entity_id' => $contactId2, 'custom_' . $customId => $lesserSelectedValue));
if ($op == '>') {
$result = $this->callAPISuccess('Contact', 'Get', array('custom_' . $customId => array($op => $lesserSelectedValue)));
$this->assertEquals($contactId, $result['id']);
} elseif ($op == '<') {
$result = $this->callAPISuccess('Contact', 'Get', array('custom_' . $customId => array($op => $selectedValue)));
$this->assertEquals($contactId2, $result['id']);
} else {
$result = $this->callAPISuccess('Contact', 'create', array('contact_type' => 'Individual', 'email' => substr(sha1(rand()), 0, 7) . '*****@*****.**'));
$contactId3 = $result['id'];
$this->callAPISuccess('CustomValue', 'create', array('entity_id' => $contactId3, 'custom_' . $customId => $greaterSelectedValue));
$result = $this->callAPISuccess('Contact', 'Get', array('custom_' . $customId => array($op => $selectedValue)));
$this->assertEquals($contactId, $result['values'][$contactId]['id']);
if ($op == '>=') {
$this->assertEquals($contactId3, $result['values'][$contactId3]['id']);
} else {
$this->assertEquals($contactId2, $result['values'][$contactId2]['id']);
}
$this->callAPISuccess('contact', 'delete', array('id' => $contactId3));
}
$this->callAPISuccess('contact', 'delete', array('id' => $contactId2));
break;
case 'IN':
$result = $this->callAPISuccess('Contact', 'Get', array('custom_' . $customId => array($op => (array) $selectedValue)));
$this->assertEquals($contactId, $result['id']);
break;
case 'NOT IN':
$result = $this->callAPISuccess('Contact', 'Get', array('custom_' . $customId => array($op => (array) $notselectedValue)));
$this->assertEquals($contactId, $result['id']);
break;
case 'LIKE':
$selectedValue = is_array($selectedValue) ? $selectedValue[0] : $selectedValue;
$result = $this->callAPISuccess('Contact', 'Get', array('custom_' . $customId => array($op => "%{$selectedValue}%")));
$this->assertEquals($contactId, $result['id']);
break;
case 'NOT LIKE':
$result = $this->callAPISuccess('Contact', 'Get', array('custom_' . $customId => array($op => $notselectedValue)));
$this->assertEquals($contactId, $result['id']);
break;
case 'IS NULL':
$result = $this->callAPISuccess('Contact', 'Get', array('custom_' . $customId => array($op => 1)));
$this->assertEquals(FALSE, array_key_exists($contactId, $result['values']));
break;
case 'IS NOT NULL':
$result = $this->callAPISuccess('Contact', 'Get', array('custom_' . $customId => array($op => 1)));
$this->assertEquals($contactId, $result['id']);
break;
}
}
$this->callAPISuccess('Contact', 'delete', array('id' => $contactId));
}
/**
* Generate the where clause and also the english language equivalent.
*/
public function where()
{
foreach ($this->_ids as $id => $values) {
// Fixed for Issue CRM 607
if (CRM_Utils_Array::value($id, $this->_fields) === NULL || !$values) {
continue;
}
$strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower';
foreach ($values as $tuple) {
list($name, $op, $value, $grouping, $wildcard) = $tuple;
$field = $this->_fields[$id];
$fieldName = "{$field['table_name']}.{$field['column_name']}";
$isSerialized = CRM_Core_BAO_CustomField::isSerialized($field);
// fix $value here to escape sql injection attacks
$qillValue = NULL;
if (!is_array($value)) {
$value = CRM_Core_DAO::escapeString(trim($value));
$qillValue = CRM_Core_BAO_CustomField::displayValue($value, $id);
} elseif (count($value) && in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
$op = key($value);
$qillValue = strstr($op, 'NULL') ? NULL : CRM_Core_BAO_CustomField::displayValue($value[$op], $id);
} else {
$op = strstr($op, 'IN') ? $op : 'IN';
$qillValue = CRM_Core_BAO_CustomField::displayValue($value, $id);
}
$qillOp = CRM_Utils_Array::value($op, CRM_Core_SelectValues::getSearchBuilderOperators(), $op);
switch ($field['data_type']) {
case 'String':
case 'StateProvince':
case 'Country':
if ($field['is_search_range'] && is_array($value)) {
//didn't found any field under any of these three data-types as searchable by range
} else {
// fix $value here to escape sql injection attacks
if (!is_array($value)) {
if ($field['data_type'] == 'String') {
$value = CRM_Utils_Type::escape($strtolower($value), 'String');
} else {
$value = CRM_Utils_Type::escape($value, 'Integer');
}
} elseif ($isSerialized) {
if (in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
$op = key($value);
$value = $value[$op];
}
$value = implode(',', (array) $value);
}
// CRM-14563,CRM-16575 : Special handling of multi-select custom fields
if ($isSerialized && !empty($value) && !strstr($op, 'NULL') && !strstr($op, 'LIKE')) {
$sp = CRM_Core_DAO::VALUE_SEPARATOR;
if (strstr($op, 'IN')) {
$value = str_replace(",", "{$sp}|{$sp}", $value);
$value = str_replace('(', '[[.left-parenthesis.]]', $value);
$value = str_replace(')', '[[.right-parenthesis.]]', $value);
}
$op = strstr($op, '!') || strstr($op, 'NOT') ? 'NOT RLIKE' : 'RLIKE';
$value = $sp . $value . $sp;
if (!$wildcard) {
foreach (explode("|", $value) as $val) {
$this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $val, 'String');
}
} else {
$this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'String');
}
} else {
//FIX for custom data query fired against no value(NULL/NOT NULL)
$this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'String');
}
$this->_qill[$grouping][] = $field['label'] . " {$qillOp} {$qillValue}";
}
break;
case 'ContactReference':
$label = $value ? CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_Contact', $value, 'sort_name') : '';
$this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'String');
$this->_qill[$grouping][] = $field['label'] . " {$qillOp} {$label}";
break;
case 'Int':
$this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'Integer');
$this->_qill[$grouping][] = ts("%1 %2 %3", array(1 => $field['label'], 2 => $qillOp, 3 => $qillValue));
break;
case 'Boolean':
if (!is_array($value)) {
if (strtolower($value) == 'yes' || strtolower($value) == strtolower(ts('Yes'))) {
$value = 1;
} else {
$value = (int) $value;
}
$value = $value == 1 ? 1 : 0;
$qillValue = $value ? 'Yes' : 'No';
}
$this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'Integer');
$this->_qill[$grouping][] = ts("%1 %2 %3", array(1 => $field['label'], 2 => $qillOp, 3 => $qillValue));
break;
case 'Link':
case 'Memo':
$this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'String');
$this->_qill[$grouping][] = ts("%1 %2 %3", array(1 => $field['label'], 2 => $qillOp, 3 => $qillValue));
break;
case 'Money':
$value = CRM_Utils_Array::value($op, (array) $value, $value);
if (is_array($value)) {
foreach ($value as $key => $val) {
$value[$key] = CRM_Utils_Rule::cleanMoney($value[$key]);
}
} else {
$value = CRM_Utils_Rule::cleanMoney($value);
}
case 'Float':
$this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'Float');
$this->_qill[$grouping][] = ts("%1 %2 %3", array(1 => $field['label'], 2 => $qillOp, 3 => $qillValue));
break;
case 'Date':
$this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'String');
list($qillOp, $qillVal) = CRM_Contact_BAO_Query::buildQillForFieldValue(NULL, $field['label'], $value, $op, array(), CRM_Utils_Type::T_DATE);
$this->_qill[$grouping][] = "{$field['label']} {$qillOp} '{$qillVal}'";
break;
case 'File':
if ($op == 'IS NULL' || $op == 'IS NOT NULL' || $op == 'IS EMPTY' || $op == 'IS NOT EMPTY') {
switch ($op) {
case 'IS EMPTY':
$op = 'IS NULL';
break;
case 'IS NOT EMPTY':
$op = 'IS NOT NULL';
break;
}
$this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op);
$this->_qill[$grouping][] = $field['label'] . " {$qillOp} ";
}
break;
}
}
}
}
