/**
* @return string
*/
function run()
{
CRM_Utils_System::setTitle(ts('API explorer and generator'));
CRM_Core_Resources::singleton()->addScriptFile('civicrm', 'templates/CRM/Admin/Page/APIExplorer.js')->addScriptUrl('//cdnjs.cloudflare.com/ajax/libs/prettify/r298/prettify.min.js', 99)->addStyleUrl('//cdnjs.cloudflare.com/ajax/libs/prettify/r298/prettify.min.css', 99);
$this->assign('operators', CRM_Core_DAO::acceptedSQLOperators());
return parent::run();
}
/**
* @param string $field
* @param string $op
* @param mixed $value
* @return $this
* @throws \API_Exception
*/
public function addWhere($field, $op, $value)
{
if (!in_array($op, \CRM_Core_DAO::acceptedSQLOperators())) {
throw new \API_Exception('Unsupported operator');
}
$this->where[$field] = array($op => $value);
return $this;
}
/**
* Run page.
*
* @return string
*/
public function run()
{
CRM_Core_Resources::singleton()->addScriptFile('civicrm', 'templates/CRM/Admin/Page/APIExplorer.js')->addScriptFile('civicrm', 'bower_components/google-code-prettify/bin/prettify.min.js', 99)->addStyleFile('civicrm', 'bower_components/google-code-prettify/bin/prettify.min.css', 99)->addVars('explorer', array('max_joins' => \Civi\API\SelectQuery::MAX_JOINS));
$this->assign('operators', CRM_Core_DAO::acceptedSQLOperators());
// List example directories
$examples = array();
foreach (scandir(\Civi::paths()->getPath('[civicrm.root]/api/v3/examples')) as $item) {
if ($item && strpos($item, '.') === FALSE) {
$examples[] = $item;
}
}
$this->assign('examples', $examples);
return parent::run();
}
/**
* @return string
*/
public function run()
{
CRM_Core_Resources::singleton()->addScriptFile('civicrm', 'templates/CRM/Admin/Page/APIExplorer.js')->addScriptFile('civicrm', 'bower_components/google-code-prettify/bin/prettify.min.js', 99)->addStyleFile('civicrm', 'bower_components/google-code-prettify/bin/prettify.min.css', 99);
$this->assign('operators', CRM_Core_DAO::acceptedSQLOperators());
// List example directories
global $civicrm_root;
$examples = array();
foreach (scandir(CRM_Utils_file::addTrailingSlash($civicrm_root, '/') . 'api/v3/examples') as $item) {
if ($item && strpos($item, '.') === FALSE) {
$examples[] = $item;
}
}
$this->assign('examples', $examples);
return parent::run();
}
/**
* @param $values
* @param $query
*/
public static function whereClauseSingle(&$values, &$query)
{
list($name, $op, $value, $grouping, $wildcard) = $values;
switch ($name) {
case 'pledge_create_date_low':
case 'pledge_create_date_high':
// process to / from date
$query->dateQueryBuilder($values, 'civicrm_pledge', 'pledge_create_date', 'create_date', 'Pledge Made');
case 'pledge_start_date_low':
case 'pledge_start_date_high':
// process to / from date
$query->dateQueryBuilder($values, 'civicrm_pledge', 'pledge_start_date', 'start_date', 'Pledge Start Date');
return;
case 'pledge_end_date_low':
case 'pledge_end_date_high':
// process to / from date
$query->dateQueryBuilder($values, 'civicrm_pledge', 'pledge_end_date', 'end_date', 'Pledge End Date');
return;
case 'pledge_payment_date_low':
case 'pledge_payment_date_high':
// process to / from date
$query->dateQueryBuilder($values, 'civicrm_pledge_payment', 'pledge_payment_date', 'scheduled_date', 'Payment Scheduled');
return;
case 'pledge_amount':
case 'pledge_amount_low':
case 'pledge_amount_high':
// process min/max amount
$query->numberRangeBuilder($values, 'civicrm_pledge', 'pledge_amount', 'amount', 'Pledge Amount');
return;
case 'pledge_payment_status_id':
case 'pledge_status_id':
if ($name == 'pledge_status_id') {
$tableName = 'civicrm_pledge';
$query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
$label = "Pledge Status";
} else {
$tableName = 'civicrm_pledge_payment';
$query->_tables['civicrm_pledge_payment'] = $query->_whereTables['civicrm_pledge_payment'] = 1;
$label = "Pledge Payment Status";
}
$name = 'status_id';
if (!empty($value) && is_array($value) && !in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
$value = array('IN' => $value);
}
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("{$tableName}.{$name}", $op, $value, 'Integer');
list($qillop, $qillVal) = CRM_Contact_BAO_Query::buildQillForFieldValue('CRM_Contribute_DAO_Contribution', 'contribution_status_id', $value, $op);
$query->_qill[$grouping][] = ts('%1 %2 %3', array(1 => $label, 2 => $qillop, 3 => $qillVal));
return;
case 'pledge_test':
case 'pledge_is_test':
// We dont want to include all tests for sql OR CRM-7827
if (!$value || $query->getOperator() != 'OR') {
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause('civicrm_pledge.is_test', $op, $value, 'Boolean');
if ($value) {
$query->_qill[$grouping][] = ts('Pledge is a Test');
}
$query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
}
return;
case 'pledge_financial_type_id':
$type = CRM_Contribute_PseudoConstant::financialType($value);
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause('civicrm_pledge.financial_type_id', $op, $value, 'Integer');
$query->_qill[$grouping][] = ts('Financial Type - %1', array(1 => $type));
$query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
return;
case 'pledge_contribution_page_id':
$page = CRM_Contribute_PseudoConstant::contributionPage($value);
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause('civicrm_pledge.contribution_page_id', $op, $value, 'Integer');
$query->_qill[$grouping][] = ts('Financial Page - %1', array(1 => $page));
$query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
return;
case 'pledge_id':
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_pledge.id", $op, $value, "Integer");
$query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
return;
case 'pledge_frequency_interval':
$query->_where[$grouping][] = "civicrm_pledge.frequency_interval {$op} {$value}";
$query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
return;
case 'pledge_frequency_unit':
$query->_where[$grouping][] = "civicrm_pledge.frequency_unit {$op} {$value}";
$query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
return;
case 'pledge_campaign_id':
$campParams = array('op' => $op, 'campaign' => $value, 'grouping' => $grouping, 'tableName' => 'civicrm_pledge');
CRM_Campaign_BAO_Query::componentSearchClause($campParams, $query);
return;
case 'pledge_contact_id':
$name = str_replace('pledge_', '', $name);
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_pledge.{$name}", $op, $value, 'Integer');
list($op, $value) = CRM_Contact_BAO_Query::buildQillForFieldValue('CRM_Pledge_DAO_Pledge', $name, $value, $op);
$query->_qill[$grouping][] = ts('Contact ID %1 %2', array(1 => $op, 2 => $value));
$query->_tables['civicrm_pledge'] = $query->_whereTables['civicrm_pledge'] = 1;
return;
}
}
/**
* Build qill for field.
*
* Qill refers to the query detail visible on the UI.
*
* @param $daoName
* @param $fieldName
* @param $fieldValue
* @param $op
* @param array $pseduoExtraParam
*
* @return array
*/
public static function buildQillForFieldValue($daoName = NULL, $fieldName, $fieldValue, $op, $pseduoExtraParam = array())
{
$qillOperators = CRM_Core_SelectValues::getSearchBuilderOperators();
if ($fieldName == 'activity_type_id') {
$pseduoOptions = CRM_Core_PseudoConstant::activityType(TRUE, TRUE, FALSE, 'label', TRUE);
} elseif ($daoName == 'CRM_Event_DAO_Event' && $fieldName == 'id') {
$pseduoOptions = CRM_Event_BAO_Event::getEvents(0, $fieldValue, TRUE, TRUE, TRUE);
} elseif ($fieldName == 'contribution_product_id') {
$pseduoOptions = CRM_Contribute_PseudoConstant::products();
} elseif ($daoName) {
$pseduoOptions = CRM_Core_PseudoConstant::get($daoName, $fieldName, $pseduoExtraParam = array());
}
//API usually have fieldValue format as array(operator => array(values)),
//so we need to separate operator out of fieldValue param
if (is_array($fieldValue) && in_array(key($fieldValue), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
$op = key($fieldValue);
$fieldValue = $fieldValue[$op];
}
if (is_array($fieldValue)) {
$qillString = array();
if (!empty($pseduoOptions)) {
foreach ((array) $fieldValue as $val) {
$qillString[] = $pseduoOptions[$val];
}
$fieldValue = implode(', ', $qillString);
} else {
$fieldValue = implode(', ', $fieldValue);
}
} elseif (!empty($pseduoOptions) && array_key_exists($fieldValue, $pseduoOptions)) {
$fieldValue = $pseduoOptions[$fieldValue];
}
return array(CRM_Utils_Array::value($op, $qillOperators, $op), $fieldValue);
}
/**
* Normalize default values for multiselect plugins.
*
* @param array $defaults
*
* @return array
*/
public function normalizeDefaultValues(&$defaults)
{
if (!is_array($defaults)) {
$defaults = array();
}
if ($this->_ssID && empty($_POST)) {
$specialFields = array('contact_type', 'group', 'contact_tags', 'member_membership_type_id', 'member_status_id');
foreach ($defaults as $element => $value) {
if (!empty($value) && is_array($value)) {
if (in_array($element, $specialFields)) {
$element = str_replace('member_membership_type_id', 'membership_type_id', $element);
$element = str_replace('member_status_id', 'membership_status_id', $element);
$defaults[$element] = array_keys($value);
} elseif (in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
$defaults[$element] = CRM_Utils_Array::value(key($value), $value);
if (is_string($defaults[$element])) {
$defaults[$element] = str_replace("%", '', $defaults[$element]);
}
}
}
if (substr($element, 0, 7) == 'custom_' && (substr($element, -5, 5) == '_from' || substr($element, -3, 3) == '_to')) {
// Ensure the _relative field is set if from or to are set to ensure custom date
// fields with 'from' or 'to' values are displayed when the are set in the smart group
// being loaded. (CRM-17116)
if (!isset($defaults[CRM_Contact_BAO_Query::getCustomFieldName($element) . '_relative'])) {
$defaults[CRM_Contact_BAO_Query::getCustomFieldName($element) . '_relative'] = 0;
}
}
}
}
return $defaults;
}
/**
* Build & execute the query and return results array
*
* @return array
* @throws \API_Exception
* @throws \CRM_Core_Exception
* @throws \Exception
*/
public function run()
{
// $select_fields maps column names to the field names of the result values.
$select_fields = $custom_fields = array();
// populate $select_fields
$return_all_fields = empty($this->options['return']) || !is_array($this->options['return']);
$return = $return_all_fields ? array_fill_keys($this->entityFieldNames, 1) : $this->options['return'];
// core return fields
foreach ($return as $field_name => $include) {
if ($include) {
$field = $this->getField($field_name);
if ($field && in_array($field['name'], $this->entityFieldNames)) {
// 'a.' is an alias for the entity table.
$select_fields["a.{$field['name']}"] = $field['name'];
} elseif ($include && strpos($field_name, '.')) {
$fkField = $this->addFkField($field_name);
if ($fkField) {
$select_fields[implode('.', $fkField)] = $field_name;
}
}
}
}
// Do custom fields IF the params contain the word "custom" or we are returning *
if ($return_all_fields || strpos(json_encode($this->params), 'custom')) {
$custom_fields = _civicrm_api3_custom_fields_for_entity($this->entity);
foreach ($custom_fields as $cf_id => $custom_field) {
$field_name = "custom_{$cf_id}";
if ($return_all_fields || !empty($this->options['return'][$field_name]) || !empty($this->options['return']['custom'])) {
list($table_name, $column_name) = $this->addCustomField($custom_field);
if ($custom_field["data_type"] != "ContactReference") {
// 'ordinary' custom field. We will select the value as custom_XX.
$select_fields["{$table_name}.{$column_name}"] = $field_name;
} else {
// contact reference custom field. The ID will be stored in custom_XX_id.
// custom_XX will contain the sort name of the contact.
$this->query->join("c_{$cf_id}", "LEFT JOIN civicrm_contact c_{$cf_id} ON c_{$cf_id}.id = `{$table_name}`.`{$column_name}`");
$select_fields["{$table_name}.{$column_name}"] = $field_name . "_id";
// We will call the contact table for the join c_XX.
$select_fields["c_{$cf_id}.sort_name"] = $field_name;
}
}
}
}
// Always select the ID.
$select_fields["a.id"] = "id";
// populate where_clauses
foreach ($this->params as $key => $value) {
$table_name = NULL;
$column_name = NULL;
if (substr($key, 0, 7) == 'filter.') {
// Legacy support for old filter syntax per the test contract.
// (Convert the style to the later one & then deal with them).
$filterArray = explode('.', $key);
$value = array($filterArray[1] => $value);
$key = 'filters';
}
// Legacy support for 'filter's construct.
if ($key == 'filters') {
foreach ($value as $filterKey => $filterValue) {
if (substr($filterKey, -4, 4) == 'high') {
$key = substr($filterKey, 0, -5);
$value = array('<=' => $filterValue);
}
if (substr($filterKey, -3, 3) == 'low') {
$key = substr($filterKey, 0, -4);
$value = array('>=' => $filterValue);
}
if ($filterKey == 'is_current' || $filterKey == 'isCurrent') {
// Is current is almost worth creating as a 'sql filter' in the DAO function since several entities have the
// concept.
$todayStart = date('Ymd000000', strtotime('now'));
$todayEnd = date('Ymd235959', strtotime('now'));
$this->query->where(array("(a.start_date <= '{$todayStart}' OR a.start_date IS NULL) AND (a.end_date >= '{$todayEnd}' OR\n a.end_date IS NULL)\n AND a.is_active = 1\n "));
}
}
}
// Ignore the "options" param if it is referring to api options and not a field in this entity
if ($key === 'options' && is_array($value) && !in_array(\CRM_Utils_Array::first(array_keys($value)), \CRM_Core_DAO::acceptedSQLOperators())) {
continue;
}
$field = $this->getField($key);
if ($field) {
$key = $field['name'];
}
if (in_array($key, $this->entityFieldNames)) {
$table_name = 'a';
$column_name = $key;
} elseif (($cf_id = \CRM_Core_BAO_CustomField::getKeyID($key)) != FALSE) {
list($table_name, $column_name) = $this->addCustomField($custom_fields[$cf_id]);
} elseif (strpos($key, '.')) {
$fkInfo = $this->addFkField($key);
if ($fkInfo) {
list($table_name, $column_name) = $fkInfo;
$this->validateNestedInput($key, $value);
}
}
// I don't know why I had to specifically exclude 0 as a key - wouldn't the others have caught it?
// We normally silently ignore null values passed in - if people want IS_NULL they can use acceptedSqlOperator syntax.
if (!$table_name || empty($key) || is_null($value)) {
// No valid filter field. This might be a chained call or something.
// Just ignore this for the $where_clause.
continue;
}
if (!is_array($value)) {
$this->query->where(array("`{$table_name}`.`{$column_name}` = @value"), array("@value" => $value));
} else {
// We expect only one element in the array, of the form
// "operator" => "rhs".
$operator = \CRM_Utils_Array::first(array_keys($value));
if (!in_array($operator, \CRM_Core_DAO::acceptedSQLOperators())) {
$this->query->where(array("{$table_name}.{$column_name} = @value"), array("@value" => $value));
} else {
$this->query->where(\CRM_Core_DAO::createSQLFilter("{$table_name}.{$column_name}", $value));
}
}
}
if (!$this->options['is_count']) {
foreach ($select_fields as $column => $alias) {
$this->query->select("{$column} as `{$alias}`");
}
} else {
$this->query->select("count(*) as c");
}
// order by
if (!empty($this->options['sort'])) {
$sort_fields = array();
foreach (explode(',', $this->options['sort']) as $sort_option) {
$words = preg_split("/[\\s]+/", $sort_option);
if (count($words) > 0 && in_array($words[0], array_values($select_fields))) {
$tmp = $words[0];
if (!empty($words[1]) && strtoupper($words[1]) == 'DESC') {
$tmp .= " DESC";
}
$sort_fields[] = $tmp;
}
}
if (count($sort_fields) > 0) {
$this->query->orderBy(implode(",", $sort_fields));
}
}
// limit
if (!empty($this->options['limit']) || !empty($this->options['offset'])) {
$this->query->limit($this->options['limit'], $this->options['offset']);
}
// ACLs
$this->query->where($this->getAclClause('a'));
$this->bao->free();
$result_entities = array();
$result_dao = \CRM_Core_DAO::executeQuery($this->query->toSQL());
while ($result_dao->fetch()) {
if ($this->options['is_count']) {
$result_dao->free();
return (int) $result_dao->c;
}
$result_entities[$result_dao->id] = array();
foreach ($select_fields as $column => $alias) {
$returnName = $alias;
$alias = str_replace('.', '_', $alias);
if (property_exists($result_dao, $alias) && $result_dao->{$alias} != NULL) {
$result_entities[$result_dao->id][$returnName] = $result_dao->{$alias};
}
// Backward compatibility on fields names.
if ($this->isFillUniqueFields && !empty($this->apiFieldSpec[$alias]['uniqueName'])) {
$result_entities[$result_dao->id][$this->apiFieldSpec[$alias]['uniqueName']] = $result_dao->{$alias};
}
foreach ($this->apiFieldSpec as $returnName => $spec) {
if (empty($result_entities[$result_dao->id][$returnName]) && !empty($result_entities[$result_dao->id][$spec['name']])) {
$result_entities[$result_dao->id][$returnName] = $result_entities[$result_dao->id][$spec['name']];
}
}
}
}
$result_dao->free();
return $result_entities;
}
/**
* 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;
}
}
}
}
/**
* Get the actual field value.
*
* In some case $params[$fieldName] holds Array value in this format Array([operator] => [value])
* So this function returns the actual field value
*
* @param array $params
* @param string $fieldName
*
* @return mixed
*/
function _civicrm_api3_field_value_check(&$params, $fieldName)
{
$fieldValue = CRM_Utils_Array::value($fieldName, $params);
$op = NULL;
if (!empty($fieldValue) && is_array($fieldValue) && array_search(key($fieldValue), CRM_Core_DAO::acceptedSQLOperators())) {
$op = key($fieldValue);
$fieldValue = CRM_Utils_Array::value($op, $fieldValue);
}
return array($fieldValue, $op);
}
/**
* @param array $formValues
* @return null
*/
public static function fixCustomFieldValue(&$formValues)
{
if (empty($formValues)) {
return NULL;
}
foreach (array_keys($formValues) as $key) {
if (substr($key, 0, 7) != 'custom_') {
continue;
} elseif (empty($formValues[$key])) {
continue;
}
$htmlType = CRM_Core_DAO::getFieldValue('CRM_Core_BAO_CustomField', substr($key, 7), 'html_type');
$dataType = CRM_Core_DAO::getFieldValue('CRM_Core_BAO_CustomField', substr($key, 7), 'data_type');
if (is_array($formValues[$key])) {
if (!in_array(key($formValues[$key]), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
$formValues[$key] = array('IN' => $formValues[$key]);
}
} elseif (stristr($formValues[$key], '%') && ($htmlType == 'TextArea' || $htmlType == 'Text' && $dataType == 'String')) {
$formValues[$key] = array('LIKE' => $formValues[$key]);
}
}
}
/**
* Given an id, extract the formValues of the saved search.
*
* @param int $id
* The id of the saved search.
*
* @return array
* the values of the posted saved search used as default values in various Search Form
*/
public static function &getFormValues($id)
{
$fv = CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_SavedSearch', $id, 'form_values');
$result = NULL;
if ($fv) {
// make sure u unserialize - since it's stored in serialized form
$result = unserialize($fv);
}
$specialFields = array('contact_type', 'group', 'contact_tags', 'member_membership_type_id', 'member_status_id');
foreach ($result as $element => $value) {
if (CRM_Contact_BAO_Query::isAlreadyProcessedForQueryFormat($value)) {
$id = CRM_Utils_Array::value(0, $value);
$value = CRM_Utils_Array::value(2, $value);
if (is_array($value) && in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
$value = CRM_Utils_Array::value(key($value), $value);
}
$result[$id] = $value;
unset($result[$element]);
continue;
}
if (!empty($value) && is_array($value)) {
if (in_array($element, $specialFields)) {
$element = str_replace('member_membership_type_id', 'membership_type_id', $element);
$element = str_replace('member_status_id', 'membership_status_id', $element);
CRM_Contact_BAO_Query::legacyConvertFormValues($element, $value);
$result[$element] = $value;
} elseif (in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
$result[$element] = CRM_Utils_Array::value(key($value), $value);
if (is_string($result[$element])) {
$result[$element] = str_replace("%", '', $result[$element]);
}
}
}
if (substr($element, 0, 7) == 'custom_' && (substr($element, -5, 5) == '_from' || substr($element, -3, 3) == '_to')) {
// Ensure the _relative field is set if from or to are set to ensure custom date
// fields with 'from' or 'to' values are displayed when the are set in the smart group
// being loaded. (CRM-17116)
if (!isset($result[CRM_Contact_BAO_Query::getCustomFieldName($element) . '_relative'])) {
$result[CRM_Contact_BAO_Query::getCustomFieldName($element) . '_relative'] = 0;
}
}
// check to see if we need to convert the old privacy array
// CRM-9180
if (!empty($result['privacy'])) {
if (is_array($result['privacy'])) {
$result['privacy_operator'] = 'AND';
$result['privacy_toggle'] = 1;
if (isset($result['privacy']['do_not_toggle'])) {
if ($result['privacy']['do_not_toggle']) {
$result['privacy_toggle'] = 2;
}
unset($result['privacy']['do_not_toggle']);
}
$result['privacy_options'] = array();
foreach ($result['privacy'] as $name => $val) {
if ($val) {
$result['privacy_options'][] = $name;
}
}
}
unset($result['privacy']);
}
}
return $result;
}
/**
* Given an id, extract the formValues of the saved search.
*
* @param int $id
* The id of the saved search.
*
* @return array
* the values of the posted saved search used as default values in various Search Form
*/
public static function getFormValues($id)
{
$fv = CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_SavedSearch', $id, 'form_values');
$result = NULL;
if ($fv) {
// make sure u unserialize - since it's stored in serialized form
$result = unserialize($fv);
}
//CRM-19250: fetch the default date format to format mysql value as per CRM_Core_Error::addDate()
$dateFormat = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_PreferencesDate', 'searchDate', 'date_format', 'name');
$dateFormat = empty($dateFormat) ? CRM_Core_Config::singleton()->dateInputFormat : $dateFormat;
$dateFormat = CRM_Utils_Array::value($dateFormat, CRM_Core_SelectValues::datePluginToPHPFormats());
$specialFields = array('contact_type', 'group', 'contact_tags', 'member_membership_type_id', 'member_status_id');
foreach ($result as $element => $value) {
if (CRM_Contact_BAO_Query::isAlreadyProcessedForQueryFormat($value)) {
$id = CRM_Utils_Array::value(0, $value);
$value = CRM_Utils_Array::value(2, $value);
if (is_array($value) && in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
$value = CRM_Utils_Array::value(key($value), $value);
}
if (strpos($id, '_date_low') !== FALSE || strpos($id, '_date_high') !== FALSE) {
$result[$id] = date($dateFormat, strtotime($value));
$entityName = strstr($id, '_date', TRUE);
$result["{$entityName}_date_relative"] = 0;
} else {
$result[$id] = $value;
}
unset($result[$element]);
continue;
}
if (!empty($value) && is_array($value)) {
if (in_array($element, $specialFields)) {
// Remove the element to minimise support for legacy formats. It is stored in $value
// so will be re-set with the right name.
unset($result[$element]);
$element = str_replace('member_membership_type_id', 'membership_type_id', $element);
$element = str_replace('member_status_id', 'membership_status_id', $element);
CRM_Contact_BAO_Query::legacyConvertFormValues($element, $value);
$result[$element] = $value;
} elseif (in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
$result[$element] = CRM_Utils_Array::value(key($value), $value);
if (is_string($result[$element])) {
$result[$element] = str_replace("%", '', $result[$element]);
}
}
}
if (substr($element, 0, 7) == 'custom_' && (substr($element, -5, 5) == '_from' || substr($element, -3, 3) == '_to')) {
// Ensure the _relative field is set if from or to are set to ensure custom date
// fields with 'from' or 'to' values are displayed when the are set in the smart group
// being loaded. (CRM-17116)
if (!isset($result[CRM_Contact_BAO_Query::getCustomFieldName($element) . '_relative'])) {
$result[CRM_Contact_BAO_Query::getCustomFieldName($element) . '_relative'] = 0;
}
}
// check to see if we need to convert the old privacy array
// CRM-9180
if (!empty($result['privacy'])) {
if (is_array($result['privacy'])) {
$result['privacy_operator'] = 'AND';
$result['privacy_toggle'] = 1;
if (isset($result['privacy']['do_not_toggle'])) {
if ($result['privacy']['do_not_toggle']) {
$result['privacy_toggle'] = 2;
}
unset($result['privacy']['do_not_toggle']);
}
$result['privacy_options'] = array();
foreach ($result['privacy'] as $name => $val) {
if ($val) {
$result['privacy_options'][] = $name;
}
}
}
unset($result['privacy']);
}
}
return $result;
}
/**
* 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;
}
}
}
}
/**
* Build qill for field.
*
* Qill refers to the query detail visible on the UI.
*
* @param string $daoName
* @param string $fieldName
* @param mixed $fieldValue
* @param string $op
* @param array $pseudoExtraParam
* @param int $type
* Type of the field per CRM_Utils_Type
*
* @return array
*/
public static function buildQillForFieldValue($daoName, $fieldName, $fieldValue, $op, $pseudoExtraParam = array(), $type = CRM_Utils_Type::T_STRING)
{
$qillOperators = CRM_Core_SelectValues::getSearchBuilderOperators();
// if Operator chosen is NULL/EMPTY then
if (strpos($op, 'NULL') !== FALSE || strpos($op, 'EMPTY') !== FALSE) {
return array(CRM_Utils_Array::value($op, $qillOperators, $op), '');
}
if ($fieldName == 'activity_type_id') {
$pseudoOptions = CRM_Core_PseudoConstant::activityType(TRUE, TRUE, FALSE, 'label', TRUE);
} elseif ($fieldName == 'country_id') {
$pseduoOptions = CRM_Core_PseudoConstant::country();
} elseif ($fieldName == 'county_id') {
$pseduoOptions = CRM_Core_PseudoConstant::county();
} elseif ($fieldName == 'world_region') {
$pseduoOptions = CRM_Core_PseudoConstant::worldRegion();
} elseif ($daoName == 'CRM_Event_DAO_Event' && $fieldName == 'id') {
$pseudoOptions = CRM_Event_BAO_Event::getEvents(0, $fieldValue, TRUE, TRUE, TRUE);
} elseif ($fieldName == 'contribution_product_id') {
$pseudoOptions = CRM_Contribute_PseudoConstant::products();
} elseif ($daoName == 'CRM_Contact_DAO_Group' && $fieldName == 'id') {
$pseudoOptions = CRM_Core_PseudoConstant::group();
} elseif ($fieldName == 'country_id') {
$pseudoOptions = CRM_Core_PseudoConstant::country();
} elseif ($daoName) {
$pseudoOptions = CRM_Core_PseudoConstant::get($daoName, $fieldName, $pseudoExtraParam);
}
//API usually have fieldValue format as array(operator => array(values)),
//so we need to separate operator out of fieldValue param
if (is_array($fieldValue) && in_array(key($fieldValue), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
$op = key($fieldValue);
$fieldValue = $fieldValue[$op];
}
if (is_array($fieldValue)) {
$qillString = array();
if (!empty($pseudoOptions)) {
foreach ((array) $fieldValue as $val) {
$qillString[] = CRM_Utils_Array::value($val, $pseudoOptions, $val);
}
$fieldValue = implode(', ', $qillString);
} else {
if ($type == CRM_Utils_Type::T_DATE) {
foreach ($fieldValue as $index => $value) {
$fieldValue[$index] = CRM_Utils_Date::customFormat($value);
}
}
$separator = ', ';
// @todo - this is a bit specific (one operator).
// However it is covered by a unit test so can be altered later with
// some confidence.
if ($op == 'BETWEEN') {
$separator = ' AND ';
}
$fieldValue = implode($separator, $fieldValue);
}
} elseif (!empty($pseudoOptions) && array_key_exists($fieldValue, $pseudoOptions)) {
$fieldValue = $pseudoOptions[$fieldValue];
} elseif ($type === CRM_Utils_Type::T_DATE) {
$fieldValue = CRM_Utils_Date::customFormat($fieldValue);
}
return array(CRM_Utils_Array::value($op, $qillOperators, $op), $fieldValue);
}
/**
* Normalize default values for multiselect plugins.
*
* @param array $defaults
*
* @return array
*/
public function normalizeDefaultValues(&$defaults)
{
if (!is_array($defaults)) {
$defaults = array();
}
if ($this->_ssID && empty($_POST)) {
$specialFields = array('contact_type', 'group', 'contact_tags', 'member_membership_type_id', 'member_status_id');
foreach ($defaults as $element => $value) {
if (!empty($value) && is_array($value)) {
if (in_array($element, $specialFields)) {
$element = str_replace('member_membership_type_id', 'membership_type_id', $element);
$element = str_replace('member_status_id', 'membership_status_id', $element);
$defaults[$element] = array_keys($value);
} elseif (in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
$defaults[$element] = CRM_Utils_Array::value(key($value), $value);
if (is_string($defaults[$element])) {
$defaults[$element] = str_replace("%", '', $defaults[$element]);
}
}
}
}
}
return $defaults;
}
/**
* 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);
// Handle multi-select search for any data type
if (is_array($value) && !$field['is_search_range'] && $field['data_type'] != 'String') {
$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']} {$qillOp} {$qillValue}";
continue;
}
// 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);
}
$qillOp = CRM_Utils_Array::value($op, CRM_Core_SelectValues::getSearchBuilderOperators(), $op);
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 {
// fix $value here to escape sql injection attacks
if (!is_array($value)) {
$value = CRM_Utils_Type::escape($strtolower($value), 'String');
} elseif (!empty($value) && in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE) || strstr(key($value), 'EMPTY')) {
$op = key($value);
$qillOp = CRM_Utils_Array::value($op, CRM_Core_SelectValues::getSearchBuilderOperators(), $op);
$value = CRM_Utils_Type::escape($strtolower($value[$op]), 'String');
}
if (strstr($op, 'NULL') || strstr($op, 'EMPTY')) {
$qillValue = $value = NULL;
} elseif ($isSerialized && strstr($op, 'IN')) {
$value = implode(',', $value);
}
// CRM-14563,CRM-16575 : Special handling of multi-select custom fields
if (!empty($value)) {
if ($isSerialized) {
if (strstr($op, 'IN')) {
$value = str_replace(array('(', ')'), '', str_replace(",", "[[:cntrl:]]|[[:cntrl:]]", $value));
}
$op = strstr($op, '!') || strstr($op, 'NOT') ? 'NOT RLIKE' : 'RLIKE';
$value = "[[:cntrl:]]" . $value . "[[:cntrl:]]";
} elseif ($wildcard) {
$value = "[[:cntrl:]]%{$value}%[[:cntrl:]]";
$op = 'LIKE';
}
}
//FIX for custom data query fired against no value(NULL/NOT NULL)
$this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($sql, $op, $value, $field['data_type']);
$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][] = $field['label'] . " {$qillOp} {$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'] . " {$qillOp} {$value}";
break;
case 'Link':
$this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'String');
$this->_qill[$grouping][] = $field['label'] . " {$qillOp} {$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'] . " {$qillOp} {$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'] . " {$qillOp} {$value}";
}
break;
case 'Memo':
$this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'String');
$this->_qill[$grouping][] = "{$field['label']} {$qillOp} {$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) && !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 'StateProvince':
case 'Country':
$this->_where[$grouping][] = "{$fieldName} {$op} " . CRM_Utils_Type::escape($value, 'Int');
$this->_qill[$grouping][] = $field['label'] . " {$qillOp} {$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'] . " {$qillOp} ";
}
break;
}
}
}
}
/**
* Determine whether a value is null-ish.
*
* @param mixed $value
* The value to check for null.
*
* @return bool
*/
public static function isNull($value)
{
// FIXME: remove $value = 'null' string test when we upgrade our DAO code to handle passing null in a better way.
if (!isset($value) || $value === NULL || $value === '' || $value === 'null') {
return TRUE;
}
if (is_array($value)) {
// @todo Reuse of the $value variable = asking for trouble.
foreach ($value as $key => $value) {
if (in_array($key, CRM_Core_DAO::acceptedSQLOperators(), TRUE) || !self::isNull($value)) {
return FALSE;
}
}
return TRUE;
}
return FALSE;
}
/**
* Build qill for field.
*
* Qill refers to the query detail visible on the UI.
*
* @param $daoName
* @param $fieldName
* @param $fieldValue
* @param $op
* @param array $pseduoExtraParam
*
* @return array
*/
public static function buildQillForFieldValue($daoName, $fieldName, $fieldValue, $op, $pseduoExtraParam = array())
{
$qillOperators = CRM_Core_SelectValues::getSearchBuilderOperators();
if ($fieldName == 'activity_type_id') {
$pseduoOptions = CRM_Core_PseudoConstant::activityType(TRUE, TRUE, FALSE, 'label', TRUE);
} elseif ($daoName == 'CRM_Event_DAO_Event' && $fieldName == 'id') {
$pseduoOptions = CRM_Event_BAO_Event::getEvents(0, $fieldValue, TRUE, TRUE, TRUE);
} else {
$pseduoOptions = CRM_Core_PseudoConstant::get($daoName, $fieldName, $pseduoExtraParam = array());
}
//For those $fieldName which don't have any associated pseudoconstant defined
if (empty($pseduoOptions)) {
if (is_array($fieldValue)) {
$op = key($fieldValue);
$fieldValue = $fieldValue[$op];
if (is_array($fieldValue)) {
$fieldValue = implode(', ', $fieldValue);
}
}
} elseif (is_array($fieldValue)) {
$qillString = array();
if (in_array(key($fieldValue), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
$op = key($fieldValue);
$fieldValue = $fieldValue[$op];
}
foreach ((array) $fieldValue as $val) {
$qillString[] = $pseduoOptions[$val];
}
$fieldValue = implode(', ', $qillString);
} else {
if (array_key_exists($fieldValue, $pseduoOptions)) {
$fieldValue = $pseduoOptions[$fieldValue];
}
}
return array(CRM_Utils_Array::value($op, $qillOperators, $op), $fieldValue);
}
/**
* @inheritDoc
*/
protected function buildWhereClause()
{
foreach ($this->where as $key => $value) {
$table_name = NULL;
$column_name = NULL;
if (substr($key, 0, 7) == 'filter.') {
// Legacy support for old filter syntax per the test contract.
// (Convert the style to the later one & then deal with them).
$filterArray = explode('.', $key);
$value = array($filterArray[1] => $value);
$key = 'filters';
}
// Legacy support for 'filter's construct.
if ($key == 'filters') {
foreach ($value as $filterKey => $filterValue) {
if (substr($filterKey, -4, 4) == 'high') {
$key = substr($filterKey, 0, -5);
$value = array('<=' => $filterValue);
}
if (substr($filterKey, -3, 3) == 'low') {
$key = substr($filterKey, 0, -4);
$value = array('>=' => $filterValue);
}
if ($filterKey == 'is_current' || $filterKey == 'isCurrent') {
// Is current is almost worth creating as a 'sql filter' in the DAO function since several entities have the concept.
$todayStart = date('Ymd000000', strtotime('now'));
$todayEnd = date('Ymd235959', strtotime('now'));
$a = self::MAIN_TABLE_ALIAS;
$this->query->where("({$a}.start_date <= '{$todayStart}' OR {$a}.start_date IS NULL)\n AND ({$a}.end_date >= '{$todayEnd}' OR {$a}.end_date IS NULL)\n AND a.is_active = 1");
}
}
}
// Ignore the "options" param if it is referring to api options and not a field in this entity
if ($key === 'options' && is_array($value) && !in_array(\CRM_Utils_Array::first(array_keys($value)), \CRM_Core_DAO::acceptedSQLOperators())) {
continue;
}
$field = $this->getField($key);
if ($field) {
$key = $field['name'];
}
if (in_array($key, $this->entityFieldNames)) {
$table_name = self::MAIN_TABLE_ALIAS;
$column_name = $key;
} elseif (($cf_id = \CRM_Core_BAO_CustomField::getKeyID($key)) != FALSE) {
list($table_name, $column_name) = $this->addCustomField($this->apiFieldSpec['custom_' . $cf_id], 'INNER');
} elseif (strpos($key, '.')) {
$fkInfo = $this->addFkField($key, 'INNER');
if ($fkInfo) {
list($table_name, $column_name) = $fkInfo;
$this->validateNestedInput($key, $value);
}
}
// I don't know why I had to specifically exclude 0 as a key - wouldn't the others have caught it?
// We normally silently ignore null values passed in - if people want IS_NULL they can use acceptedSqlOperator syntax.
if (!$table_name || empty($key) || is_null($value)) {
// No valid filter field. This might be a chained call or something.
// Just ignore this for the $where_clause.
continue;
}
if (!is_array($value)) {
$this->query->where(array("`{$table_name}`.`{$column_name}` = @value"), array("@value" => $value));
} else {
// We expect only one element in the array, of the form
// "operator" => "rhs".
$operator = \CRM_Utils_Array::first(array_keys($value));
if (!in_array($operator, \CRM_Core_DAO::acceptedSQLOperators())) {
$this->query->where(array("{$table_name}.{$column_name} = @value"), array("@value" => $value));
} else {
$this->query->where(\CRM_Core_DAO::createSQLFilter("{$table_name}.{$column_name}", $value));
}
}
}
}
/**
* Takes an associative array and creates a participant object.
*
* the function extract all the params it needs to initialize the create a
* participant object. the params array could contain additional unused name/value
* pairs
*
* @param array $params
* (reference ) an assoc array of name/value pairs.
*
* @return CRM_Event_BAO_Participant
*/
public static function &add(&$params)
{
if (!empty($params['id'])) {
CRM_Utils_Hook::pre('edit', 'Participant', $params['id'], $params);
} else {
CRM_Utils_Hook::pre('create', 'Participant', NULL, $params);
}
// converting dates to mysql format
if (!empty($params['register_date'])) {
$params['register_date'] = CRM_Utils_Date::isoToMysql($params['register_date']);
}
if (!empty($params['participant_fee_amount'])) {
$params['participant_fee_amount'] = CRM_Utils_Rule::cleanMoney($params['participant_fee_amount']);
}
if (!empty($params['fee_amount'])) {
$params['fee_amount'] = CRM_Utils_Rule::cleanMoney($params['fee_amount']);
}
// ensure that role ids are encoded as a string
if (isset($params['role_id']) && is_array($params['role_id'])) {
if (in_array(key($params['role_id']), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
$op = key($params['role_id']);
$params['role_id'] = $params['role_id'][$op];
} else {
$params['role_id'] = implode(CRM_Core_DAO::VALUE_SEPARATOR, $params['role_id']);
}
}
$participantBAO = new CRM_Event_BAO_Participant();
if (!empty($params['id'])) {
$participantBAO->id = CRM_Utils_Array::value('id', $params);
$participantBAO->find(TRUE);
$participantBAO->register_date = CRM_Utils_Date::isoToMysql($participantBAO->register_date);
}
$participantBAO->copyValues($params);
//CRM-6910
//1. If currency present, it should be valid one.
//2. We should have currency when amount is not null.
$currency = $participantBAO->fee_currency;
if ($currency || !CRM_Utils_System::isNull($participantBAO->fee_amount)) {
if (!CRM_Utils_Rule::currencyCode($currency)) {
$config = CRM_Core_Config::singleton();
$currency = $config->defaultCurrency;
}
}
$participantBAO->fee_currency = $currency;
$participantBAO->save();
$session = CRM_Core_Session::singleton();
CRM_Contact_BAO_GroupContactCache::opportunisticCacheFlush();
if (!empty($params['id'])) {
CRM_Utils_Hook::post('edit', 'Participant', $participantBAO->id, $participantBAO);
} else {
CRM_Utils_Hook::post('create', 'Participant', $participantBAO->id, $participantBAO);
}
return $participantBAO;
}
/**
* @param $values
*
* @throws Exception
*/
function restWhere(&$values)
{
$name = CRM_Utils_Array::value(0, $values);
$op = CRM_Utils_Array::value(1, $values);
$value = CRM_Utils_Array::value(2, $values);
$grouping = CRM_Utils_Array::value(3, $values);
$wildcard = CRM_Utils_Array::value(4, $values);
if (isset($grouping) && empty($this->_where[$grouping])) {
$this->_where[$grouping] = array();
}
$multipleFields = array('url');
//check if the location type exits for fields
$lType = '';
$locType = explode('-', $name);
if (!in_array($locType[0], $multipleFields)) {
//add phone type if exists
if (isset($locType[2]) && $locType[2]) {
$locType[2] = CRM_Core_DAO::escapeString($locType[2]);
}
}
$field = CRM_Utils_Array::value($name, $this->_fields);
if (!$field) {
$field = CRM_Utils_Array::value($locType[0], $this->_fields);
if (!$field) {
return;
}
}
$setTables = TRUE;
$strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower';
$locationType = CRM_Core_PseudoConstant::get('CRM_Core_DAO_Address', 'location_type_id');
if (substr($name, 0, 14) === 'state_province') {
if (isset($locType[1]) && is_numeric($locType[1])) {
$setTables = FALSE;
$aName = "{$locationType[$locType[1]]}-address";
$where = "`{$aName}`.state_province_id";
} else {
$where = "civicrm_address.state_province_id";
}
$states = CRM_Core_PseudoConstant::stateProvince();
if (is_numeric($value)) {
$this->_where[$grouping][] = self::buildClause($where, $op, $value, 'Positive');
$value = $states[(int) $value];
} else {
$intVal = CRM_Utils_Array::key($value, $states);
$this->_where[$grouping][] = self::buildClause($where, $op, $intVal, 'Positive');
}
if (!$lType) {
$this->_qill[$grouping][] = ts('State') . " {$op} '{$value}'";
} else {
$this->_qill[$grouping][] = ts('State') . " ({$lType}) {$op} '{$value}'";
}
} elseif (!empty($field['pseudoconstant'])) {
$this->optionValueQuery($name, $op, $value, $grouping, CRM_Core_PseudoConstant::get('CRM_Contact_DAO_Contact', $field['name']), $field, $field['title'], 'String', TRUE);
if ($name == 'gender_id') {
self::$_openedPanes[ts('Demographics')] = TRUE;
}
} elseif (substr($name, 0, 7) === 'country') {
if (isset($locType[1]) && is_numeric($locType[1])) {
$setTables = FALSE;
$aName = "{$locationType[$locType[1]]}-address";
$where = "`{$aName}`.country_id";
} else {
$where = "civicrm_address.country_id";
}
$countries = CRM_Core_PseudoConstant::country();
if (is_numeric($value)) {
$this->_where[$grouping][] = self::buildClause($where, $op, $value, 'Positive');
$value = $countries[(int) $value];
} else {
$intVal = CRM_Utils_Array::key($value, $countries);
$this->_where[$grouping][] = self::buildClause($where, $op, $intVal, 'Positive');
}
if (!$lType) {
$this->_qill[$grouping][] = ts('Country') . " {$op} '{$value}'";
} else {
$this->_qill[$grouping][] = ts('Country') . " ({$lType}) {$op} '{$value}'";
}
} elseif (substr($name, 0, 6) === 'county') {
if (isset($locType[1]) && is_numeric($locType[1])) {
$setTables = FALSE;
$aName = "{$locationType[$locType[1]]}-address";
$where = "`{$aName}`.county_id";
} else {
$where = "civicrm_address.county_id";
}
$counties = CRM_Core_PseudoConstant::county();
if (is_numeric($value)) {
$this->_where[$grouping][] = self::buildClause($where, $op, $value, 'Positive');
$value = $counties[(int) $value];
} else {
$intVal = CRM_Utils_Array::key($value, $counties);
$this->_where[$grouping][] = self::buildClause($where, $op, $intVal, 'Positive');
}
if (!$lType) {
$this->_qill[$grouping][] = ts('County') . " {$op} '{$value}'";
} else {
$this->_qill[$grouping][] = ts('County') . " ({$lType}) {$op} '{$value}'";
}
} elseif ($name === 'world_region') {
$field['where'] = 'civicrm_worldregion.id';
$this->optionValueQuery($name, $op, $value, $grouping, CRM_Core_PseudoConstant::worldRegion(), $field, ts('World Region'), 'Positive', TRUE);
} elseif ($name === 'birth_date') {
$date = CRM_Utils_Date::processDate($value);
$this->_where[$grouping][] = self::buildClause("contact_a.{$name}", $op, $date);
if ($date) {
$date = CRM_Utils_Date::customFormat($date);
$this->_qill[$grouping][] = "{$field['title']} {$op} \"{$date}\"";
} else {
$this->_qill[$grouping][] = "{$field['title']} {$op}";
}
self::$_openedPanes[ts('Demographics')] = TRUE;
} elseif ($name === 'deceased_date') {
$date = CRM_Utils_Date::processDate($value);
$this->_where[$grouping][] = self::buildClause("contact_a.{$name}", $op, $date);
if ($date) {
$date = CRM_Utils_Date::customFormat($date);
$this->_qill[$grouping][] = "{$field['title']} {$op} \"{$date}\"";
} else {
$this->_qill[$grouping][] = "{$field['title']} {$op}";
}
self::$_openedPanes[ts('Demographics')] = TRUE;
} elseif ($name === 'is_deceased') {
$this->_where[$grouping][] = self::buildClause("contact_a.{$name}", $op, $value);
$this->_qill[$grouping][] = "{$field['title']} {$op} \"{$value}\"";
self::$_openedPanes[ts('Demographics')] = TRUE;
} elseif ($name === 'contact_id') {
if (is_int($value)) {
$this->_where[$grouping][] = self::buildClause($field['where'], $op, $value);
$this->_qill[$grouping][] = "{$field['title']} {$op} {$value}";
}
} elseif ($name === 'name') {
$value = $strtolower(CRM_Core_DAO::escapeString($value));
if ($wildcard) {
$value = "%{$value}%";
$op = 'LIKE';
}
$wc = self::caseImportant($op) ? "LOWER({$field['where']})" : "{$field['where']}";
$this->_where[$grouping][] = self::buildClause($wc, $op, "'{$value}'");
$this->_qill[$grouping][] = "{$field['title']} {$op} \"{$value}\"";
} elseif ($name === 'current_employer') {
$value = $strtolower(CRM_Core_DAO::escapeString($value));
if ($wildcard) {
$value = "%{$value}%";
$op = 'LIKE';
}
$wc = self::caseImportant($op) ? "LOWER(contact_a.organization_name)" : "contact_a.organization_name";
$ceWhereClause = self::buildClause($wc, $op, $value);
$ceWhereClause .= " AND contact_a.contact_type = 'Individual'";
$this->_where[$grouping][] = $ceWhereClause;
$this->_qill[$grouping][] = "{$field['title']} {$op} \"{$value}\"";
} elseif ($name === 'email_greeting') {
$filterCondition = array('greeting_type' => 'email_greeting');
$this->optionValueQuery($name, $op, $value, $grouping, CRM_Core_PseudoConstant::greeting($filterCondition), $field, ts('Email Greeting'));
} elseif ($name === 'postal_greeting') {
$filterCondition = array('greeting_type' => 'postal_greeting');
$this->optionValueQuery($name, $op, $value, $grouping, CRM_Core_PseudoConstant::greeting($filterCondition), $field, ts('Postal Greeting'));
} elseif ($name === 'addressee') {
$filterCondition = array('greeting_type' => 'addressee');
$this->optionValueQuery($name, $op, $value, $grouping, CRM_Core_PseudoConstant::greeting($filterCondition), $field, ts('Addressee'));
} elseif (substr($name, 0, 4) === 'url-') {
$tName = 'civicrm_website';
$this->_whereTables[$tName] = $this->_tables[$tName] = "\nLEFT JOIN civicrm_website ON ( civicrm_website.contact_id = contact_a.id )";
$value = $strtolower(CRM_Core_DAO::escapeString($value));
if ($wildcard) {
$value = "%{$value}%";
$op = 'LIKE';
}
$wc = 'civicrm_website.url';
$this->_where[$grouping][] = $d = self::buildClause($wc, $op, $value);
$this->_qill[$grouping][] = "{$field['title']} {$op} \"{$value}\"";
} elseif ($name === 'contact_is_deleted') {
$this->_where[$grouping][] = self::buildClause("contact_a.is_deleted", $op, $value);
$this->_qill[$grouping][] = "{$field['title']} {$op} \"{$value}\"";
} else {
if (is_array($value)) {
// traditionally an array being passed has been a fatal error. We can take advantage of this to add support
// for api style operators for functions that hit this point without worrying about regression
// (the previous comments indicated the condition for hitting this point were unknown
// per CRM-14743 we are adding modified_date & created_date operator support
$operations = array_keys($value);
foreach ($operations as $operator) {
if (!in_array($operator, CRM_Core_DAO::acceptedSQLOperators())) {
// we don't know when this might happen
CRM_Core_Error::fatal();
}
}
$this->_where[$grouping][] = CRM_Core_DAO::createSQLFilter($name, $value, NULL);
//since this is not currently being called by the form layer we can skip worrying about the 'qill' for now
return;
}
if (!empty($field['where'])) {
if ($op != 'IN') {
$value = $strtolower($value);
}
if ($wildcard) {
$value = "%{$value}%";
$op = 'LIKE';
}
if (isset($locType[1]) && is_numeric($locType[1])) {
$setTables = FALSE;
//get the location name
list($tName, $fldName) = self::getLocationTableName($field['where'], $locType);
$where = "`{$tName}`.{$fldName}";
$this->_where[$grouping][] = self::buildClause("LOWER({$where})", $op, $value);
// we set both _tables & whereTables because whereTables doesn't seem to do what the name implies it should
$this->_tables[$tName] = $this->_whereTables[$tName] = 1;
$this->_qill[$grouping][] = "{$field['title']} {$op} '{$value}'";
} else {
list($tableName, $fieldName) = explode('.', $field['where'], 2);
if ($tableName == 'civicrm_contact') {
$fieldName = "LOWER(contact_a.{$fieldName})";
} else {
if ($op != 'IN' && !is_numeric($value)) {
$fieldName = "LOWER({$field['where']})";
} else {
$fieldName = "{$field['where']}";
}
}
$type = NULL;
if (!empty($field['type'])) {
$type = CRM_Utils_Type::typeToString($field['type']);
}
$this->_where[$grouping][] = self::buildClause($fieldName, $op, $value, $type);
$this->_qill[$grouping][] = "{$field['title']} {$op} {$value}";
}
}
}
if ($setTables && isset($field['where'])) {
list($tableName, $fieldName) = explode('.', $field['where'], 2);
if (isset($tableName)) {
$this->_tables[$tableName] = 1;
$this->_whereTables[$tableName] = 1;
}
}
}
/**
* @param $values
* @param $query
*/
public static function whereClauseSingle(&$values, &$query)
{
list($name, $op, $value, $grouping, $wildcard) = $values;
$fields = array_merge(CRM_Event_BAO_Event::fields(), CRM_Event_BAO_Participant::exportableFields());
switch ($name) {
case 'event_start_date_low':
case 'event_start_date_high':
$query->dateQueryBuilder($values, 'civicrm_event', 'event_start_date', 'start_date', 'Start Date');
return;
case 'event_end_date_low':
case 'event_end_date_high':
$query->dateQueryBuilder($values, 'civicrm_event', 'event_end_date', 'end_date', 'End Date');
return;
case 'event_include_repeating_events':
/**
* Include Repeating Events
*/
//Get parent of this event
$exEventId = '';
if ($query->_where[$grouping]) {
foreach ($query->_where[$grouping] as $key => $val) {
if (strstr($val, 'civicrm_event.id =')) {
$exEventId = $val;
$extractEventId = explode(" ", $val);
$value = $extractEventId[2];
$where = $query->_where[$grouping][$key];
} else {
if (strstr($val, 'civicrm_event.id IN')) {
//extract the first event id if multiple events are selected
preg_match('/civicrm_event.id IN \\(\\"(\\d+)/', $val, $matches);
$value = $matches[1];
$where = $query->_where[$grouping][$key];
}
}
}
if ($exEventId) {
$extractEventId = explode(" ", $exEventId);
$value = $extractEventId[2];
} else {
if (!empty($matches[1])) {
$value = $matches[1];
}
}
$where = $query->_where[$grouping][$key];
}
$thisEventHasParent = CRM_Core_BAO_RecurringEntity::getParentFor($value, 'civicrm_event');
if ($thisEventHasParent) {
$getAllConnections = CRM_Core_BAO_RecurringEntity::getEntitiesForParent($thisEventHasParent, 'civicrm_event');
$allEventIds = array();
foreach ($getAllConnections as $key => $val) {
$allEventIds[] = $val['id'];
}
if (!empty($allEventIds)) {
$op = "IN";
$value = "(" . implode(",", $allEventIds) . ")";
}
}
$query->_where[$grouping][] = "{$where} OR civicrm_event.id {$op} {$value}";
$query->_qill[$grouping][] = ts('Include Repeating Events');
$query->_tables['civicrm_event'] = $query->_whereTables['civicrm_event'] = 1;
return;
case 'participant_is_test':
$key = array_search('civicrm_participant.is_test = 0', $query->_where[$grouping]);
if (!empty($key)) {
unset($query->_where[$grouping][$key]);
}
case 'participant_test':
// We dont want to include all tests for sql OR CRM-7827
if (!$value || $query->getOperator() != 'OR') {
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_participant.is_test", $op, $value, "Boolean");
$isTest = $value ? 'a Test' : 'not a Test';
$query->_qill[$grouping][] = ts("Participant is %1", array(1 => $isTest));
$query->_tables['civicrm_participant'] = $query->_whereTables['civicrm_participant'] = 1;
}
return;
case 'participant_fee_id':
foreach ($value as $k => &$val) {
$val = CRM_Core_DAO::getFieldValue('CRM_Price_DAO_PriceFieldValue', $val, 'label');
$val = CRM_Core_DAO::escapeString(trim($val));
}
$feeLabel = implode('|', $value);
$query->_where[$grouping][] = "civicrm_participant.fee_level REGEXP '{$feeLabel}'";
$query->_qill[$grouping][] = ts("Fee level") . " IN " . implode(', ', $value);
$query->_tables['civicrm_participant'] = $query->_whereTables['civicrm_participant'] = 1;
return;
case 'participant_fee_amount_high':
case 'participant_fee_amount_low':
$query->numberRangeBuilder($values, 'civicrm_participant', 'participant_fee_amount', 'fee_amount', 'Fee Amount');
return;
case 'participant_status_id':
if ($value && is_array($value) && strpos($op, 'IN') === FALSE) {
$op = 'IN';
}
case 'participant_status':
case 'participant_source':
case 'participant_id':
case 'participant_contact_id':
case 'participant_is_pay_later':
case 'participant_fee_amount':
case 'participant_fee_level':
case 'participant_campaign_id':
$qillName = $name;
if (in_array($name, array('participant_status_id', 'participant_source', 'participant_id', 'participant_contact_id', 'participant_fee_amount', 'participant_fee_level', 'participant_is_pay_later', 'participant_campaign_id'))) {
$name = str_replace('participant_', '', $name);
if ($name == 'is_pay_later') {
$qillName = $name;
}
} elseif ($name == 'participant_status') {
$name = 'status_id';
}
$dataType = !empty($fields[$qillName]['type']) ? CRM_Utils_Type::typeToString($fields[$qillName]['type']) : 'String';
$tableName = empty($tableName) ? 'civicrm_participant' : $tableName;
if (is_array($value) && in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
$op = key($value);
$value = $value[$op];
}
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("{$tableName}.{$name}", $op, $value, $dataType);
list($op, $value) = CRM_Contact_BAO_Query::buildQillForFieldValue('CRM_Event_DAO_Participant', $name, $value, $op);
$query->_qill[$grouping][] = ts('%1 %2 %3', array(1 => $fields[$qillName]['title'], 2 => $op, 3 => $value));
$query->_tables['civicrm_participant'] = $query->_whereTables['civicrm_participant'] = 1;
return;
case 'participant_role':
case 'participant_role_id':
$qillName = $name;
$name = 'role_id';
if (is_array($value) && in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
$op = key($value);
$value = $value[$op];
}
if (!strstr($op, 'NULL') && !strstr($op, 'EMPTY') && !strstr($op, 'LIKE')) {
$regexOp = strstr($op, '!') || strstr($op, 'NOT') ? 'NOT REGEXP' : 'REGEXP';
$regexp = "[[:cntrl:]]*" . implode('[[:>:]]*|[[:<:]]*', (array) $value) . "[[:cntrl:]]*";
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_participant.{$name}", $regexOp, $regexp, 'String');
} else {
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("{$tableName}.{$name}", $op, $value, $dataType);
}
list($op, $value) = CRM_Contact_BAO_Query::buildQillForFieldValue('CRM_Event_DAO_Participant', $name, $value, $op);
$query->_qill[$grouping][] = ts('%1 %2 %3', array(1 => $fields[$qillName]['title'], 2 => $op, 3 => $value));
$query->_tables['civicrm_participant'] = $query->_whereTables['civicrm_participant'] = 1;
return;
case 'participant_register_date':
case 'participant_register_date_high':
case 'participant_register_date_low':
$query->dateQueryBuilder($values, 'civicrm_participant', 'participant_register_date', 'register_date', 'Register Date');
return;
case 'event_id':
case 'participant_event_id':
$name = str_replace('participant_', '', $name);
case 'event_is_public':
case 'event_type_id':
case 'event_title':
$qillName = $name;
if (in_array($name, array('event_id', 'event_title', 'event_is_public'))) {
$name = str_replace('event_', '', $name);
}
$dataType = !empty($fields[$qillName]['type']) ? CRM_Utils_Type::typeToString($fields[$qillName]['type']) : 'String';
$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_event.{$name}", $op, $value, $dataType);
$query->_tables['civicrm_event'] = $query->_whereTables['civicrm_event'] = 1;
if (!array_key_exists($qillName, $fields)) {
break;
}
list($op, $value) = CRM_Contact_BAO_Query::buildQillForFieldValue('CRM_Event_DAO_Event', $name, $value, $op);
$query->_qill[$grouping][] = ts('%1 %2 %3', array(1 => $fields[$qillName]['title'], 2 => $op, 3 => $value));
return;
}
}
/**
* @param $values
* @param string $tableName
* @param string $fieldName
* @param string $dbFieldName
* @param $fieldTitle
* @param bool $appendTimeStamp
*/
public function dateQueryBuilder(&$values, $tableName, $fieldName, $dbFieldName, $fieldTitle, $appendTimeStamp = TRUE)
{
list($name, $op, $value, $grouping, $wildcard) = $values;
if ($name == "{$fieldName}_low" || $name == "{$fieldName}_high") {
if (isset($this->_rangeCache[$fieldName]) || !$value) {
return;
}
$this->_rangeCache[$fieldName] = 1;
$secondOP = $secondPhrase = $secondValue = $secondDate = $secondDateFormat = NULL;
if ($name == $fieldName . '_low') {
$firstOP = '>=';
$firstPhrase = ts('greater than or equal to');
$firstDate = CRM_Utils_Date::processDate($value);
$secondValues = $this->getWhereValues("{$fieldName}_high", $grouping);
if (!empty($secondValues) && $secondValues[2]) {
$secondOP = '<=';
$secondPhrase = ts('less than or equal to');
$secondValue = $secondValues[2];
if ($appendTimeStamp && strlen($secondValue) == 10) {
$secondValue .= ' 23:59:59';
}
$secondDate = CRM_Utils_Date::processDate($secondValue);
}
} elseif ($name == $fieldName . '_high') {
$firstOP = '<=';
$firstPhrase = ts('less than or equal to');
if ($appendTimeStamp && strlen($value) == 10) {
$value .= ' 23:59:59';
}
$firstDate = CRM_Utils_Date::processDate($value);
$secondValues = $this->getWhereValues("{$fieldName}_low", $grouping);
if (!empty($secondValues) && $secondValues[2]) {
$secondOP = '>=';
$secondPhrase = ts('greater than or equal to');
$secondValue = $secondValues[2];
$secondDate = CRM_Utils_Date::processDate($secondValue);
}
}
if (!$appendTimeStamp) {
$firstDate = substr($firstDate, 0, 8);
}
$firstDateFormat = CRM_Utils_Date::customFormat($firstDate);
if ($secondDate) {
if (!$appendTimeStamp) {
$secondDate = substr($secondDate, 0, 8);
}
$secondDateFormat = CRM_Utils_Date::customFormat($secondDate);
}
$this->_tables[$tableName] = $this->_whereTables[$tableName] = 1;
if ($secondDate) {
$this->_where[$grouping][] = "\n( {$tableName}.{$dbFieldName} {$firstOP} '{$firstDate}' ) AND\n( {$tableName}.{$dbFieldName} {$secondOP} '{$secondDate}' )\n";
$this->_qill[$grouping][] = "{$fieldTitle} - {$firstPhrase} \"{$firstDateFormat}\" " . ts('AND') . " {$secondPhrase} \"{$secondDateFormat}\"";
} else {
$this->_where[$grouping][] = "{$tableName}.{$dbFieldName} {$firstOP} '{$firstDate}'";
$this->_qill[$grouping][] = "{$fieldTitle} - {$firstPhrase} \"{$firstDateFormat}\"";
}
}
if ($name == $fieldName) {
//In Get API, for operators other then '=' the $value is in array(op => value) format
if (is_array($value) && !empty($value) && in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
$op = key($value);
$value = $value[$op];
}
$date = $format = NULL;
if (strstr($op, 'IN')) {
$format = array();
foreach ($value as &$date) {
$date = CRM_Utils_Date::processDate($date);
if (!$appendTimeStamp) {
$date = substr($date, 0, 8);
}
$format[] = CRM_Utils_Date::customFormat($date);
}
$date = "('" . implode("','", $value) . "')";
$format = implode(', ', $format);
} elseif ($value && (!strstr($op, 'NULL') && !strstr($op, 'EMPTY'))) {
$date = CRM_Utils_Date::processDate($value);
if (!$appendTimeStamp) {
$date = substr($date, 0, 8);
}
$format = CRM_Utils_Date::customFormat($date);
$date = "'{$date}'";
}
if ($date) {
$this->_where[$grouping][] = "{$tableName}.{$dbFieldName} {$op} {$date}";
} else {
$this->_where[$grouping][] = "{$tableName}.{$dbFieldName} {$op}";
}
$this->_tables[$tableName] = $this->_whereTables[$tableName] = 1;
$op = CRM_Utils_Array::value($op, CRM_Core_SelectValues::getSearchBuilderOperators(), $op);
$this->_qill[$grouping][] = "{$fieldTitle} {$op} {$format}";
}
}
