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