/**
* Given a list of conditions in params generate the required
* where clause
*
* @return void
* @access public
*/
function whereClause()
{
//CRM_Core_Error::debug( 'p', $this->_params );
// domain id is always part of the where clause
$config =& CRM_Core_Config::singleton();
$this->_where[] = 'civicrm_contact.domain_id = ' . $config->domainID();
// check for both id and contact_id
$id = CRM_Utils_Array::value('id', $this->_params);
if (!$id) {
$id = CRM_Utils_Array::value('contact_id', $this->_params);
}
if ($id) {
$this->_where[] = "civicrm_contact.id = {$id}";
}
$this->contactType();
$this->sortName();
$this->sortByCharacter();
$this->locationTypeAndName();
$this->group();
$this->tag();
$this->postalCode();
$this->activity();
$this->includeContactIds();
$this->contribution();
//CRM_Core_Error::debug( 'p', $this->_params );
//CRM_Core_Error::debug( 'f', $this->_fields );
foreach ($this->_fields as $name => $field) {
if (empty($name) || in_array($name, $GLOBALS['_CRM_CONTACT_BAO_QUERY']['skipFields'])) {
continue;
}
$value = CRM_Utils_Array::value($name, $this->_params);
if (!isset($value) || $value == null) {
continue;
}
if (CRM_Core_BAO_CustomField::getKeyID($name)) {
continue;
}
//check if the location type exits for fields
$lType = '';
$locType = array();
$locType = explode('-', $name);
if (is_numeric($locType[1])) {
$this->_params['location_type'] = array($locType[1] => 1);
$lType = $this->locationTypeAndName(true);
}
//add phone type if exists
if ($locType[2]) {
$this->_where[] = "civicrm_phone.phone_type ='" . $locType[2] . "'";
}
// FIXME: the LOWER/strtolower pairs below most probably won't work
// with non-US-ASCII characters, as even if MySQL does the proper
// thing with LOWER-ing them (4.0 almost certainly won't, but then
// we don't officially support 4.0 for non-US-ASCII data), PHP
// won't do the proper thing with strtolower-ing them unless the
// underlying operating system uses an UTF-8 locale for LC_CTYPE
// for the user the webserver runs at (or suEXECs); we should use
// mb_strtolower(), but then we'd require mb_strings support; we
// could wrap this in function_exist(), though
if (substr($name, 0, 14) === 'state_province') {
$states =& CRM_Core_PseudoConstant::stateProvince();
if (is_numeric($value)) {
$value = $states[(int) $value];
}
$this->_where[] = 'LOWER(' . $field['where'] . ') = "' . strtolower(addslashes($value)) . '"';
if (!$lType) {
$this->_qill[] = ts('State - "%1"', array(1 => $value));
} else {
$this->_qill[] = ts('State (%2) - "%1"', array(1 => $value, 2 => $lType));
}
} else {
if (substr($name, 0, 7) === 'country') {
$countries =& CRM_Core_PseudoConstant::country();
if (is_numeric($value)) {
$value = $countries[(int) $value];
}
$this->_where[] = 'LOWER(' . $field['where'] . ') = "' . strtolower(addslashes($value)) . '"';
if (!$lType) {
$this->_qill[] = ts('Country - "%1"', array(1 => $value));
} else {
$this->_qill[] = ts('Country (%2) - "%1"', array(1 => $value, 2 => $lType));
}
} else {
if ($name === 'individual_prefix') {
$individualPrefixs =& CRM_Core_PseudoConstant::individualPrefix();
if (is_numeric($value)) {
$value = $individualPrefixs[(int) $value];
}
$this->_where[] = 'LOWER(' . $field['where'] . ') = "' . strtolower(addslashes($value)) . '"';
$this->_qill[] = ts('Individual Prefix - "%1"', array(1 => $value));
} else {
if ($name === 'individual_suffix') {
$individualSuffixs =& CRM_Core_PseudoConstant::individualsuffix();
if (is_numeric($value)) {
$value = $individualSuffixs[(int) $value];
}
$this->_where[] = 'LOWER(' . $field['where'] . ') = "' . strtolower(addslashes($value)) . '"';
$this->_qill[] = ts('Individual Suffix - "%1"', array(1 => $value));
} else {
if ($name === 'gender') {
$genders =& CRM_Core_PseudoConstant::gender();
if (is_numeric($value)) {
$value = $genders[(int) $value];
}
$this->_where[] = 'LOWER(' . $field['where'] . ') = "' . strtolower(addslashes($value)) . '"';
$this->_qill[] = ts('Gender - "%1"', array(1 => $value));
} else {
if ($name === 'birth_date') {
$date = CRM_Utils_Date::format($value);
if (!$date) {
continue;
}
$this->_where[] = $field['where'] . " = {$date}";
$date = CRM_Utils_Date::customFormat($value);
$this->_qill[] = "{$field['title']} \"{$date}\"";
} else {
if ($name === 'contact_id') {
if (is_int($value)) {
$this->_where[] = $field['where'] . " = {$value}";
$this->_qill[] = ts('%1 is equal to %2', array(1 => $field['title'], 2 => $value));
}
} else {
if ($name === 'name') {
$this->_where[] = 'LOWER(' . $field['where'] . ') LIKE "%' . strtolower(addslashes($value)) . '%"';
$this->_qill[] = ts('%1 like "%2"', array(1 => $field['title'], 2 => $value));
} else {
// sometime the value is an array, need to investigate and fix
if (is_array($value)) {
$value = $value[0];
}
if (!empty($field['where'])) {
if ($this->_strict) {
$this->_where[] = 'LOWER(' . $field['where'] . ') = "' . strtolower(str_replace("\"", "", $value)) . '"';
$this->_qill[] = ts('%1 = "%2"', array(1 => $field['title'], 2 => $value));
} else {
$this->_where[] = 'LOWER(' . $field['where'] . ') LIKE "%' . strtolower(addslashes($value)) . '%"';
$this->_qill[] = ts('%1 like "%2"', array(1 => $field['title'], 2 => $value));
}
}
}
}
}
}
}
}
}
}
list($tableName, $fieldName) = explode('.', $field['where'], 2);
if (isset($tableName)) {
$this->_tables[$tableName] = 1;
$this->_whereTables[$tableName] = 1;
}
// CRM_Core_Error::debug( 'f', $field );
// CRM_Core_Error::debug( $value, $this->_qill );
}
if ($this->_customQuery) {
$this->_where = array_merge($this->_where, $this->_customQuery->_where);
$this->_qill = array_merge($this->_qill, $this->_customQuery->_qill);
}
return implode(' AND ', $this->_where);
}
function restWhere(&$values)
{
list($name, $op, $value, $grouping, $wildcard) = $values;
if (!CRM_Utils_Array::value($grouping, $this->_where)) {
$this->_where[$grouping] = array();
}
$multipleFields = array('url');
//check if the location type exits for fields
$lType = '';
$locType = array();
$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';
if (substr($name, 0, 14) === 'state_province') {
if (isset($locType[1]) && is_numeric($locType[1])) {
$setTables = FALSE;
//get the location name
$locationType = CRM_Core_PseudoConstant::locationType();
list($tName, $fldName) = self::getLocationTableName($field['where'], $locType);
$this->_whereTables[$tName] = $this->_tables[$tName];
$where = "`{$tName}`.{$fldName}";
} else {
$where = $field['where'];
}
$wc = $op != 'LIKE' ? "LOWER({$where})" : $where;
if (is_numeric($value)) {
$where = str_replace('.name', '.id', $where);
$this->_where[$grouping][] = self::buildClause($where, $op, $value, 'Positive');
$states = CRM_Core_PseudoConstant::stateProvince();
$value = $states[(int) $value];
} else {
$wc = $op != 'LIKE' ? "LOWER({$where})" : $where;
$this->_where[$grouping][] = self::buildClause($wc, $op, $value, 'String');
}
if (!$lType) {
$this->_qill[$grouping][] = ts('State') . " {$op} '{$value}'";
} else {
$this->_qill[$grouping][] = ts('State') . " ({$lType}) {$op} '{$value}'";
}
} elseif (substr($name, 0, 7) === 'country') {
if (isset($locType[1]) && is_numeric($locType[1])) {
$setTables = FALSE;
//get the location name
$locationType = CRM_Core_PseudoConstant::locationType();
list($tName, $fldName) = self::getLocationTableName($field['where'], $locType);
$this->_whereTables[$tName] = $this->_tables[$tName];
$where = "`{$tName}`.{$fldName}";
} else {
$where = $field['where'];
}
if (is_numeric($value)) {
$where = str_replace('.name', '.id', $where);
$this->_where[$grouping][] = self::buildClause($where, $op, $value, 'Positive');
$countries = CRM_Core_PseudoConstant::country();
$value = $countries[(int) $value];
} else {
$wc = $op != 'LIKE' ? "LOWER({$where})" : $where;
$this->_where[$grouping][] = self::buildClause($wc, $op, $value, 'String');
}
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;
//get the location name
$locationType = CRM_Core_PseudoConstant::locationType();
list($tName, $fldName) = self::getLocationTableName($field['where'], $locType);
$this->_whereTables[$tName] = $this->_tables[$tName];
$where = "`{$tName}`.{$fldName}";
} else {
$where = $field['where'];
}
if (is_numeric($value)) {
$where = str_replace('.name', '.id', $where);
$this->_where[$grouping][] = self::buildClause($where, $op, $value, 'Positive');
$counties = CRM_Core_PseudoConstant::county();
$value = $counties[(int) $value];
} else {
$wc = $op != 'LIKE' ? "LOWER({$where})" : $where;
$this->_where[$grouping][] = self::buildClause($wc, $op, $value, 'String');
}
if (!$lType) {
$this->_qill[$grouping][] = ts('County') . " {$op} '{$value}'";
} else {
$this->_qill[$grouping][] = ts('County') . " ({$lType}) {$op} '{$value}'";
}
} elseif ($name === 'world_region') {
$worldRegions = CRM_Core_PseudoConstant::worldRegion();
if (is_numeric($value)) {
$value = $worldRegions[(int) $value];
}
$wc = $op != 'LIKE' ? "LOWER({$field['where']})" : "{$field['where']}";
$this->_where[$grouping][] = self::buildClause($wc, $op, $value, 'String');
$this->_qill[$grouping][] = ts('World Region') . " {$op} '{$value}'";
} elseif ($name === 'individual_prefix') {
$individualPrefixs = CRM_Core_PseudoConstant::individualPrefix();
if (is_numeric($value)) {
$value = $individualPrefixs[(int) $value];
}
$wc = $op != 'LIKE' ? "LOWER({$field['where']})" : "{$field['where']}";
$this->_where[$grouping][] = self::buildClause($wc, $op, $value, 'String');
$this->_qill[$grouping][] = ts('Individual Prefix') . " {$op} '{$value}'";
} elseif ($name === 'individual_suffix') {
$individualSuffixs = CRM_Core_PseudoConstant::individualsuffix();
if (is_numeric($value)) {
$value = $individualSuffixs[(int) $value];
}
$wc = $op != 'LIKE' ? "LOWER({$field['where']})" : "{$field['where']}";
$this->_where[$grouping][] = self::buildClause($wc, $op, $value, 'String');
$this->_qill[$grouping][] = ts('Individual Suffix') . " {$op} '{$value}'";
} elseif ($name === 'gender') {
$genders = CRM_Core_PseudoConstant::gender();
if (is_numeric($value)) {
$value = $genders[(int) $value];
}
$wc = $op != 'LIKE' ? "LOWER({$field['where']})" : "{$field['where']}";
$this->_where[$grouping][] = self::buildClause($wc, $op, $value, 'String');
$this->_qill[$grouping][] = ts('Gender') . " {$op} '{$value}'";
self::$_openedPanes['Demographics'] = 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['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['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['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 = $op != 'LIKE' ? "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 = $op != 'LIKE' ? "LOWER(contact_a.organization_name)" : "contact_a.organization_name";
$this->_where[$grouping][] = self::buildClause($wc, $op, "'{$value}' AND contact_a.contact_type ='Individual'");
$this->_qill[$grouping][] = "{$field['title']} {$op} \"{$value}\"";
} elseif ($name === 'email_greeting') {
$filterCondition = array('greeting_type' => 'email_greeting');
$emailGreetings = CRM_Core_PseudoConstant::greeting($filterCondition);
if (is_numeric($value)) {
$value = $emailGreetings[(int) $value];
}
$wc = $op != 'LIKE' ? "LOWER({$field['where']})" : "{$field['where']}";
$this->_where[$grouping][] = self::buildClause($wc, $op, $value, 'String');
$this->_qill[$grouping][] = ts('Email Greeting') . " {$op} '{$value}'";
} elseif ($name === 'postal_greeting') {
$filterCondition = array('greeting_type' => 'postal_greeting');
$postalGreetings = CRM_Core_PseudoConstant::greeting($filterCondition);
if (is_numeric($value)) {
$value = $postalGreetings[(int) $value];
}
$wc = $op != 'LIKE' ? "LOWER({$field['where']})" : "{$field['where']}";
$this->_where[$grouping][] = self::buildClause($wc, $op, $value, 'String');
$this->_qill[$grouping][] = ts('Postal Greeting') . " {$op} '{$value}'";
} elseif ($name === 'addressee') {
$filterCondition = array('greeting_type' => 'addressee');
$addressee = CRM_Core_PseudoConstant::greeting($filterCondition);
if (is_numeric($value)) {
$value = $addressee[(int) $value];
}
$wc = $op != 'LIKE' ? "LOWER({$field['where']})" : "{$field['where']}";
$this->_where[$grouping][] = self::buildClause($wc, $op, $value, 'String');
$this->_qill[$grouping][] = ts('Addressee') . " {$op} '{$value}'";
} 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][] = 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 {
// sometime the value is an array, need to investigate and fix
if (is_array($value)) {
CRM_Core_Error::fatal();
}
if (!empty($field['where'])) {
if ($op != 'IN') {
$value = $strtolower(CRM_Core_DAO::escapeString($value));
}
if ($wildcard) {
$value = "'%{$value}%'";
$op = 'LIKE';
}
if (isset($locType[1]) && is_numeric($locType[1])) {
$setTables = FALSE;
//get the location name
$locationType = CRM_Core_PseudoConstant::locationType();
list($tName, $fldName) = self::getLocationTableName($field['where'], $locType);
$where = "`{$tName}`.{$fldName}";
$this->_where[$grouping][] = self::buildClause("LOWER({$where})", $op, $value);
$this->_whereTables[$tName] = $this->_tables[$tName];
$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 (CRM_Utils_Array::value('type', $field)) {
$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) {
list($tableName, $fieldName) = explode('.', $field['where'], 2);
if (isset($tableName)) {
$this->_tables[$tableName] = 1;
$this->_whereTables[$tableName] = 1;
}
}
}
function restWhere(&$values)
{
list($name, $op, $value, $grouping, $wildcard) = $values;
if (!CRM_Utils_Array::value($grouping, $this->_where)) {
$this->_where[$grouping] = array();
}
//check if the location type exits for fields
$lType = '';
$locType = array();
$locType = explode('-', $name);
//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;
// FIXME: the LOWER/strtolower pairs below most probably won't work
// with non-US-ASCII characters, as even if MySQL does the proper
// thing with LOWER-ing them (4.0 almost certainly won't, but then
// we don't officially support 4.0 for non-US-ASCII data), PHP
// won't do the proper thing with strtolower-ing them unless the
// underlying operating system uses an UTF-8 locale for LC_CTYPE
// for the user the webserver runs at (or suEXECs); we should use
// mb_strtolower(), but then we'd require mb_strings support; we
// could wrap this in function_exist(), though
if (substr($name, 0, 14) === 'state_province') {
if (isset($locType[1]) && is_numeric($locType[1])) {
$setTables = false;
//get the location name
$locationType =& CRM_Core_PseudoConstant::locationType();
list($tName, $fldName) = self::getLocationTableName($field['where'], $locType);
$this->_whereTables[$tName] = $this->_tables[$tName];
$where = "`{$tName}`.{$fldName}";
} else {
$where = $field['where'];
}
$wc = $op != 'LIKE' ? "LOWER({$where})" : $where;
if (is_numeric($value)) {
$where = str_replace('.name', '.id', $where);
$this->_where[$grouping][] = self::buildClause($where, $op, $value, 'Positive');
$states =& CRM_Core_PseudoConstant::stateProvince();
$value = $states[(int) $value];
} else {
$wc = $op != 'LIKE' ? "LOWER({$where})" : $where;
$this->_where[$grouping][] = self::buildClause($wc, $op, $value, 'String');
}
if (!$lType) {
$this->_qill[$grouping][] = ts('State') . " {$op} '{$value}'";
} else {
$this->_qill[$grouping][] = ts('State') . " ({$lType}) {$op} '{$value}'";
}
} else {
if (substr($name, 0, 7) === 'country') {
if (isset($locType[1]) && is_numeric($locType[1])) {
$setTables = false;
//get the location name
$locationType =& CRM_Core_PseudoConstant::locationType();
list($tName, $fldName) = self::getLocationTableName($field['where'], $locType);
$this->_whereTables[$tName] = $this->_tables[$tName];
$where = "`{$tName}`.{$fldName}";
} else {
$where = $field['where'];
}
if (is_numeric($value)) {
$where = str_replace('.name', '.id', $where);
$this->_where[$grouping][] = self::buildClause($where, $op, $value, 'Positive');
$countries =& CRM_Core_PseudoConstant::country();
$value = $countries[(int) $value];
} else {
$wc = $op != 'LIKE' ? "LOWER({$where})" : $where;
$this->_where[$grouping][] = self::buildClause($wc, $op, $value, 'String');
}
if (!$lType) {
$this->_qill[$grouping][] = ts('Country') . " {$op} '{$value}'";
} else {
$this->_qill[$grouping][] = ts('Country') . " ({$lType}) {$op} '{$value}'";
}
} else {
if (substr($name, 0, 6) === 'county') {
if (isset($locType[1]) && is_numeric($locType[1])) {
$setTables = false;
//get the location name
$locationType =& CRM_Core_PseudoConstant::locationType();
list($tName, $fldName) = self::getLocationTableName($field['where'], $locType);
$this->_whereTables[$tName] = $this->_tables[$tName];
$where = "`{$tName}`.{$fldName}";
} else {
$where = $field['where'];
}
if (is_numeric($value)) {
$where = str_replace('.name', '.id', $where);
$this->_where[$grouping][] = self::buildClause($where, $op, $value, 'Positive');
$counties =& CRM_Core_PseudoConstant::county();
$value = $counties[(int) $value];
} else {
$wc = $op != 'LIKE' ? "LOWER({$where})" : $where;
$this->_where[$grouping][] = self::buildClause($wc, $op, $value, 'String');
}
if (!$lType) {
$this->_qill[$grouping][] = ts('County') . " {$op} '{$value}'";
} else {
$this->_qill[$grouping][] = ts('County') . " ({$lType}) {$op} '{$value}'";
}
} else {
if ($name === 'world_region') {
$worldRegions =& CRM_Core_PseudoConstant::worldRegion();
if (is_numeric($value)) {
$value = $worldRegions[(int) $value];
}
$wc = $op != 'LIKE' ? "LOWER({$field['where']})" : "{$field['where']}";
$this->_where[$grouping][] = self::buildClause($wc, $op, $value, 'String');
$this->_qill[$grouping][] = ts('World Region') . " {$op} '{$value}'";
} else {
if ($name === 'individual_prefix') {
$individualPrefixs =& CRM_Core_PseudoConstant::individualPrefix();
if (is_numeric($value)) {
$value = $individualPrefixs[(int) $value];
}
$wc = $op != 'LIKE' ? "LOWER({$field['where']})" : "{$field['where']}";
$this->_where[$grouping][] = self::buildClause($wc, $op, $value, 'String');
$this->_qill[$grouping][] = ts('Individual Prefix') . " {$op} '{$value}'";
} else {
if ($name === 'individual_suffix') {
$individualSuffixs =& CRM_Core_PseudoConstant::individualsuffix();
if (is_numeric($value)) {
$value = $individualSuffixs[(int) $value];
}
$wc = $op != 'LIKE' ? "LOWER({$field['where']})" : "{$field['where']}";
$this->_where[$grouping][] = self::buildClause($wc, $op, $value, 'String');
$this->_qill[$grouping][] = ts('Individual Suffix') . " {$op} '{$value}'";
} else {
if ($name === 'gender') {
$genders =& CRM_Core_PseudoConstant::gender();
if (is_numeric($value)) {
$value = $genders[(int) $value];
}
$wc = $op != 'LIKE' ? "LOWER({$field['where']})" : "{$field['where']}";
$this->_where[$grouping][] = self::buildClause($wc, $op, $value, 'String');
$this->_qill[$grouping][] = ts('Gender') . " {$op} '{$value}'";
} else {
if ($name === 'birth_date') {
$date = CRM_Utils_Date::format($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}";
}
} else {
if ($name === 'deceased_date') {
$date = CRM_Utils_Date::format($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}";
}
} else {
if ($name === 'is_deceased') {
$this->_where[$grouping][] = self::buildClause("contact_a.{$name}", $op, $value);
$this->_qill[$grouping][] = "{$field['title']} {$op} \"{$value}\"";
} else {
if ($name === 'contact_id') {
if (is_int($value)) {
$this->_where[$grouping][] = self::buildClause($field['where'], $op, $value);
$this->_qill[$grouping][] = "{$field['title']} {$op} {$value}";
}
} else {
if ($name === 'name') {
$value = strtolower(CRM_Core_DAO::escapeString($value));
if ($wildcard) {
$value = "%{$value}%";
$op = 'LIKE';
}
$wc = $op != 'LIKE' ? "LOWER({$field['where']})" : "{$field['where']}";
$this->_where[$grouping][] = self::buildClause($wc, $op, "'{$value}'");
$this->_qill[$grouping][] = "{$field['title']} {$op} \"{$value}\"";
} else {
if ($name === 'current_employer') {
$value = strtolower(CRM_Core_DAO::escapeString($value));
if ($wildcard) {
$value = "%{$value}%";
$op = 'LIKE';
}
$wc = $op != 'LIKE' ? "LOWER(contact_a.organization_name)" : "contact_a.organization_name";
$this->_where[$grouping][] = self::buildClause($wc, $op, "'{$value}' AND contact_a.contact_type ='Individual'");
$this->_qill[$grouping][] = "{$field['title']} {$op} \"{$value}\"";
} else {
if ($name === 'email_greeting') {
$filterCondition = array('greeting_type' => 'email_greeting');
$emailGreetings =& CRM_Core_PseudoConstant::greeting($filterCondition);
if (is_numeric($value)) {
$value = $emailGreetings[(int) $value];
}
$wc = $op != 'LIKE' ? "LOWER({$field['where']})" : "{$field['where']}";
$this->_where[$grouping][] = self::buildClause($wc, $op, $value, 'String');
$this->_qill[$grouping][] = ts('Email Greeting') . " {$op} '{$value}'";
} else {
if ($name === 'postal_greeting') {
$filterCondition = array('greeting_type' => 'postal_greeting');
$postalGreetings =& CRM_Core_PseudoConstant::greeting($filterCondition);
if (is_numeric($value)) {
$value = $postalGreetings[(int) $value];
}
$wc = $op != 'LIKE' ? "LOWER({$field['where']})" : "{$field['where']}";
$this->_where[$grouping][] = self::buildClause($wc, $op, $value, 'String');
$this->_qill[$grouping][] = ts('Postal Greeting') . " {$op} '{$value}'";
} else {
if ($name === 'addressee') {
$filterCondition = array('greeting_type' => 'addressee');
$addressee =& CRM_Core_PseudoConstant::greeting($filterCondition);
if (is_numeric($value)) {
$value = $addressee[(int) $value];
}
$wc = $op != 'LIKE' ? "LOWER({$field['where']})" : "{$field['where']}";
$this->_where[$grouping][] = self::buildClause($wc, $op, $value, 'String');
$this->_qill[$grouping][] = ts('Addressee') . " {$op} '{$value}'";
} else {
// sometime the value is an array, need to investigate and fix
if (is_array($value)) {
CRM_Core_Error::fatal();
}
if (!empty($field['where'])) {
if ($op != 'IN') {
$value = strtolower(CRM_Core_DAO::escapeString($value));
}
if ($wildcard) {
$value = "%{$value}%";
$op = 'LIKE';
}
if ($op != 'IN') {
$value = "'{$value}'";
}
if (isset($locType[1]) && is_numeric($locType[1])) {
$setTables = false;
//get the location name
$locationType =& CRM_Core_PseudoConstant::locationType();
list($tName, $fldName) = self::getLocationTableName($field['where'], $locType);
$where = "`{$tName}`.{$fldName}";
$this->_where[$grouping][] = self::buildClause("LOWER({$where})", $op, $value);
$this->_whereTables[$tName] = $this->_tables[$tName];
$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']}";
}
}
$this->_where[$grouping][] = self::buildClause($fieldName, $op, $value);
$this->_qill[$grouping][] = "{$field['title']} {$op} {$value}";
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
if ($setTables) {
list($tableName, $fieldName) = explode('.', $field['where'], 2);
if (isset($tableName)) {
$this->_tables[$tableName] = 1;
$this->_whereTables[$tableName] = 1;
}
}
}
