/** * @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}"; } }