$this->selectClause();
$this->_whereClause = $this->whereClause();
+ if (array_key_exists('civicrm_contribution', $this->_whereTables)) {
+ $component = 'contribution';
+ }
+ if (array_key_exists('civicrm_membership', $this->_whereTables)) {
+ $component = 'membership';
+ }
+ if (isset($component)) {
+ CRM_Financial_BAO_FinancialType::buildPermissionedClause($this->_whereClause, $component);
+ }
$this->_fromClause = self::fromClause($this->_tables, NULL, NULL, $this->_primaryLocation, $this->_mode);
$this->_simpleFromClause = self::fromClause($this->_whereTables, NULL, NULL, $this->_primaryLocation, $this->_mode);
$this->_pseudoConstantsSelect[$tName]['select'] = "{$field['where']} as `$name`";
$this->_pseudoConstantsSelect[$tName]['element'] = $name;
}
+ elseif (strpos($name, 'contribution_soft_credit') !== FALSE) {
+ if (CRM_Contribute_BAO_Query::isSoftCreditOptionEnabled($this->_params)) {
+ $this->_select[$name] = "{$field['where']} as `$name`";
+ }
+ }
else {
$this->_select[$name] = "{$field['where']} as `$name`";
}
foreach ($formValues as $id => $values) {
- self::legacyConvertFormValues($id, $values);
-
if (self::isAlreadyProcessedForQueryFormat($values)) {
$params[] = $values;
continue;
}
- if ($id == 'privacy') {
+
+ self::legacyConvertFormValues($id, $values);
+
+ // The form uses 1 field to represent two db fields
+ if ($id == 'contact_type' && $values && (!is_array($values) || !array_intersect(array_keys($values), CRM_Core_DAO::acceptedSQLOperators()))) {
+ $contactType = array();
+ $subType = array();
+ foreach ((array) $values as $key => $type) {
+ $types = explode('__', is_numeric($type) ? $key : $type);
+ $contactType[$types[0]] = $types[0];
+ // Add sub-type if specified
+ if (!empty($types[1])) {
+ $subType[$types[1]] = $types[1];
+ }
+ }
+ $params[] = array('contact_type', 'IN', $contactType, 0, 0);
+ if ($subType) {
+ $params[] = array('contact_sub_type', 'IN', $subType, 0, 0);
+ }
+ }
+ elseif ($id == 'privacy') {
if (is_array($formValues['privacy'])) {
$op = !empty($formValues['privacy']['do_not_toggle']) ? '=' : '!=';
foreach ($formValues['privacy'] as $key => $value) {
*
*/
public static function legacyConvertFormValues($id, &$values) {
- if (in_array($id, array('group', 'tag')) && is_array($values)) {
+ $legacyElements = array(
+ 'group',
+ 'tag',
+ 'contact_tags',
+ 'contact_type',
+ 'membership_type_id',
+ 'membership_status_id',
+ );
+ if (in_array($id, $legacyElements) && is_array($values)) {
// prior to 4.7, formValues for some attributes (e.g. group, tag) are stored in array(id1 => 1, id2 => 1),
// as per the recent Search fixes $values need to be in standard array(id1, id2) format
$ids = array_keys($values, 1);
if (count($ids) > 1 ||
- (count($ids) == 1 && (key($values) > 1 || (key($values) == 1 && $values[1] == 1))) // handle (0 => 4), (1 => 1)
+ (count($ids) == 1 &&
+ (key($values) > 1 ||
+ is_string(key($values)) ||
+ (key($values) == 1 && $values[1] == 1) // handle (0 => 4), (1 => 1)
+ )
+ )
) {
$values = $ids;
}
$this->deletedContacts($values);
return;
- case 'contact_type':
- $this->contactType($values);
- return;
-
case 'contact_sub_type':
$this->contactSubType($values);
return;
return;
case 'state_province':
+ case 'state_province_id':
+ case 'state_province_name':
$this->stateProvince($values);
return;
case 'country':
+ case 'country_id':
$this->country($values, FALSE);
return;
$multipleFields = array('url');
- //check if the location type exits for fields
+ //check if the location type exists for fields
$lType = '';
$locType = explode('-', $name);
$clause = array();
$alias = "contact_a.contact_sub_type";
- $qillOperators = array('NOT LIKE' => ts('Not Like')) + CRM_Core_SelectValues::getSearchBuilderOperators();
+ $qillOperators = CRM_Core_SelectValues::getSearchBuilderOperators();
$op = str_replace('IN', 'LIKE', $op);
$op = str_replace('=', 'LIKE', $op);
// Handle numeric postal code range searches properly by casting the column as numeric
if (is_numeric($value)) {
- $field = 'ROUND(civicrm_address.postal_code)';
+ $field = "IF (civicrm_address.postal_code REGEXP '^[0-9]+$', CAST(civicrm_address.postal_code AS UNSIGNED), 0)";
$val = CRM_Utils_Type::escape($value, 'Integer');
}
else {
}
$countryClause = $countryQill = NULL;
- if (
- $values &&
- !empty($value)
- ) {
-
+ if ($values && !empty($value)) {
$this->_tables['civicrm_address'] = 1;
$this->_whereTables['civicrm_address'] = 1;
public function stateProvince(&$values, $status = NULL) {
list($name, $op, $value, $grouping, $wildcard) = $values;
- // quick escape for IS NULL
- if (in_array($op, array('IS NULL', 'IS NOT NULL', 'IS EMPTY', 'IS NOT EMPTY'))) {
- $value = NULL;
- }
- elseif (!is_array($value)) {
- // force the state to be an array
- // check if its in the mapper format!
- $values = self::parseSearchBuilderString($value);
- if (is_array($values)) {
- $value = $values;
- }
- else {
- $value = array($value);
- }
- }
-
- // check if the values are ids OR names of the states
- $inputFormat = 'id';
- if ($value) {
- foreach ($value as $v) {
- if (!is_numeric($v)) {
- $inputFormat = 'name';
- break;
- }
- }
- }
-
- $names = array();
- if ($op == '=') {
- $op = 'IN';
- }
- elseif ($op == '!=') {
- $op = 'NOT IN';
- }
- else {
- // this converts IS (NOT)? EMPTY to IS (NOT)? NULL
- $op = str_replace('EMPTY', 'NULL', $op);
- }
- if (in_array($op, array('IS NULL', 'IS NOT NULL', 'IS EMPTY', 'IS NOT EMPTY'))) {
- $stateClause = "civicrm_address.state_province_id $op";
- }
- elseif ($inputFormat == 'id') {
- if ($op != 'NOT IN') {
- $op = 'IN';
- }
- $stateClause = "civicrm_address.state_province_id $op (" . implode(',', $value) . ')';
-
- foreach ($value as $id) {
- $names[] = CRM_Core_PseudoConstant::stateProvince($id, FALSE);
- }
- }
- else {
- $inputClause = array();
- $stateProvince = CRM_Core_PseudoConstant::stateProvince();
- foreach ($value as $name) {
- $name = trim($name);
- $inputClause[] = CRM_Utils_Array::key($name, $stateProvince);
- }
- $stateClause = "civicrm_address.state_province_id $op (" . implode(',', $inputClause) . ')';
- $names = $value;
- }
+ $stateClause = self::buildClause('civicrm_address.state_province_id', $op, $value, 'Positive');
$this->_tables['civicrm_address'] = 1;
$this->_whereTables['civicrm_address'] = 1;
$countryValues = $this->getWhereValues('country', $grouping);
list($countryClause, $countryQill) = $this->country($countryValues, TRUE);
-
if ($countryClause) {
$clause = "( $stateClause AND $countryClause )";
}
}
$this->_where[$grouping][] = $clause;
+ list($qillop, $qillVal) = self::buildQillForFieldValue('CRM_Core_DAO_Address', "state_province_id", $value, $op);
if (!$status) {
- $this->_qill[$grouping][] = ts('State/Province') . " $op " . implode(' ' . ts('or') . ' ', $names) . $countryQill;
+ $this->_qill[$grouping][] = ts("State/Province %1 %2 %3", array(1 => $qillop, 2 => $qillVal, 3 => $countryQill));
}
else {
- return implode(' ' . ts('or') . ' ', $names) . $countryQill;
+ return implode(' ' . ts('or') . ' ', $qillVal) . $countryQill;
}
}
*
* @return bool;
*/
- protected static function isAlreadyProcessedForQueryFormat($values) {
+ public static function isAlreadyProcessedForQueryFormat($values) {
if (!is_array($values)) {
return FALSE;
}
if ($context == 'search') {
$where .= " AND contact_a.is_deleted = 0 ";
}
+ CRM_Financial_BAO_FinancialType::getAvailableFinancialTypes($financialTypes);
+ if (!empty($financialTypes)) {
+ $where .= " AND civicrm_contribution.financial_type_id IN (" . implode(',', array_keys($financialTypes)) . ") AND li.id IS NULL";
+ $from .= " LEFT JOIN civicrm_line_item li
+ ON civicrm_contribution.id = li.contribution_id AND
+ li.entity_table = 'civicrm_contribution' AND li.financial_type_id NOT IN (" . implode(',', array_keys($financialTypes)) . ") ";
+ }
+ else {
+ $where .= " AND civicrm_contribution.financial_type_id IN (0)";
+ }
// make sure contribution is completed - CRM-4989
$completedWhere = $where . " AND civicrm_contribution.contribution_status_id = 1 ";
$summary['total']['count'] = $summary['total']['amount'] = $summary['total']['avg'] = "n/a";
$innerQuery = "SELECT civicrm_contribution.total_amount, COUNT(civicrm_contribution.total_amount) as civicrm_contribution_total_amount_count,
civicrm_contribution.currency $from $completedWhere";
-
$query = "$select FROM (
$innerQuery GROUP BY civicrm_contribution.id
) as conts
case 'IN':
case 'NOT IN':
// I feel like this would be escaped properly if passed through $queryString = CRM_Core_DAO::createSqlFilter.
- if (!empty($value) && is_array($value) && !array_key_exists($op, $value)) {
- $value = array($op => $value);
+ if (!empty($value) && (!is_array($value) || !array_key_exists($op, $value))) {
+ $value = array($op => (array) $value);
}
default:
* depends on the operator and who's calling the query builder.
* @param int $grouping
* the index where to place the where clause.
- * @param $selectValues
- * The key value pairs for this element. This allows us to use this function for things besides option-value pairs.
+ * @param string $daoName
+ * DAO Name.
* @param array $field
* an array that contains various properties of the field identified by $name.
* @param string $label