Index: trunk/core/units/general/helpers/search_helper.php =================================================================== diff -u -N --- trunk/core/units/general/helpers/search_helper.php (revision 8067) +++ trunk/core/units/general/helpers/search_helper.php (revision 0) @@ -1,568 +0,0 @@ - $kw) $final[$kw] = $res[1][$index]; - $keyword = preg_replace($quotes_re, '', $keyword); - $not_quoted_kw = preg_match_all($no_quotes_re, $keyword, $res); - foreach ($res[2] as $index => $kw) $final[$kw] = $res[1][$index]; - - return $final; - } - - function getPositiveKeywords($keyword) - { - $keywords = $this->splitKeyword($keyword); - - $ret = Array(); - foreach ($keywords as $keyword => $sign) { - if ($sign == '+' || $sign == '') { - $ret[] = $keyword; - } - } - return $ret; - } - - function buildWhereClause($keyword, $fields) - { - $keywords = $this->splitKeyword($keyword); - - $normal_conditions = Array(); - $plus_conditions = Array(); - $minus_conditions = Array(); - - foreach ($keywords as $keyword => $sign) { - switch ($sign) { - case '+': - $plus_conditions[] = implode(' LIKE "%'.$keyword.'%" OR ', $fields).' LIKE "%'.$keyword.'%"'; - break; - - case '-': - foreach ($fields as $field) { - $condition[] = $field.' NOT LIKE "%'.$keyword.'%" OR '.$field.' IS NULL'; - } - $minus_conditions[] = '('.implode(') AND (', $condition).')'; - break; - - case '': - $keyword = str_replace('"', '\"', $keyword); - $normal_conditions[] = implode(' LIKE "%'.$keyword.'%" OR ', $fields).' LIKE "%'.$keyword.'%"'; - break; - } - } - - // building where clause - if ($normal_conditions) { - $where_clause = '('.implode(') OR (', $normal_conditions).')'; - } - else { - $where_clause = '1'; - } - - if ($plus_conditions) { - $where_clause = '('.$where_clause.') AND ('.implode(') AND (', $plus_conditions).')'; - } - - if ($minus_conditions) { - $where_clause = '('.$where_clause.') AND ('.implode(') AND (', $minus_conditions).')'; - } - - return $where_clause; - } - - /** - * Returns additional information about search field - * - * @param kDBList $object - * @param string $field_name - * @return Array - */ - function getFieldInformation(&$object, $field_name) - { - $sql_filter_type = isset($object->VirtualFields[$field_name]) ? 'having' : 'where'; - $field_type = isset($object->Fields[$field_name]['type']) ? $object->Fields[$field_name]['type'] : 'string'; - - if (preg_match('/(.*)\.(.*)/', $field_name, $regs)) { - $table_name = '`'.$regs[1].'`.'; // field from external table - $field_name = $regs[2]; - } - elseif ($sql_filter_type == 'where') { - $table_name = '`'.$object->TableName.'`.'; // field from local table - } - - $table_name = ($sql_filter_type == 'where') ? $table_name : ''; - - // replace wid inside table name to WID_MARK constant value - $is_temp_table = preg_match('/(.*)'.TABLE_PREFIX.'ses_'.$this->Application->GetSID().'(_[\d]+){0,1}_edit_(.*)/', $table_name, $regs); - if ($is_temp_table) { - $table_name = $regs[1].TABLE_PREFIX.'ses_'.EDIT_MARK.'_edit_'.$regs[3]; // edit_mark will be replaced with sid[_main_wid] in AddFilters - } - - return Array ('field_name' => $field_name, 'field_type' => $field_type, 'table_name' => $table_name, 'sql_filter_type' => $sql_filter_type); - } - - /** - * Enter description here... - * - * @param kEvent $event - */ - function performSearch(&$event) - { - $object =& $event->getObject(); - - // process search keyword - $search_keyword = $this->Application->GetVar( $event->getPrefixSpecial(true).'_search_keyword'); - $this->Application->StoreVar( $event->getPrefixSpecial().'_search_keyword', $search_keyword); - $search_keyword = str_replace('*', '%', $search_keyword); - - $custom_filter = $this->processCustomFilters($event); - - if(!$search_keyword && $custom_filter === false) { - $this->resetSearch($event); - return true; - } - - if ($search_keyword) { - $this->processAutomaticFilters($event, $search_keyword, $custom_filter); - } - } - - - function processAutomaticFilters(&$event, $search_keyword, $custom_filter) - { - $grid_name = $this->Application->GetVar('grid_name'); - $grids = $this->Application->getUnitOption($event->Prefix, 'Grids'); - $search_fields = array_keys($grids[$grid_name]['Fields']); - - $search_filter = Array(); - $object =& $event->getObject(); - - foreach ($search_fields as $search_field) { - $custom_search = isset($custom_filter[$search_field]); - - $filter_data = $this->getSearchClause($object, $search_field, $search_keyword, $custom_search); - - if ($filter_data) { - $search_filter[$search_field] = $filter_data; - } - else { - unset($search_filter[$search_field]); - } - } - $this->Application->StoreVar($event->getPrefixSpecial().'_search_filter', serialize($search_filter) ); - } - - /** - * Returns search clause for any particular field - * - * @param kDBList $object - * @param string $search_field - * @param string $search_keyword what we are searching (false, when building custom filter clause) - * @param string $custom_search already found using custom filter - * @return Array - */ - function getSearchClause(&$object, $field_name, $search_keyword, $custom_search) - { - $search_keywords = $this->splitKeyword($search_keyword); - - extract( $this->getFieldInformation($object, $field_name) ); // see getFieldInformation for more details - - $filter_value = ''; - - // get field clause by formatter name and/or parameters - $formatter = getArrayValue($object->Fields[$field_name], 'formatter'); - switch ($formatter) { - case 'kOptionsFormatter': - $search_keys = Array(); - - if ($custom_search === false) { - // if keywords passed through simple search filter (on each grid) - $use_phrases = getArrayValue($object->Fields[$field_name], 'use_phrases'); - $field_options = $object->GetFieldOptions($field_name); - foreach ($field_options['options'] as $key => $val) { - foreach ($search_keywords as $keyword => $sign) { - $pattern = '#'.$keyword.'#i'; - if (!preg_match($pattern, $use_phrases ? $this->Application->Phrase($val) : $val)) { - if ($sign == '+') { - $filter_value = $table_name.'`'.$field_name.'` = NULL'; - break; - } - else { - continue; - } - } - - if ($sign == '+' || $sign == '') { - $search_keys[$key] = $this->Conn->qstr($key); - } - elseif($sign == '-') { - // if same value if found as exclusive too, then remove from search result - unset($search_keys[$key]); - } - } - } - } - - if ($search_keys) { - $filter_value = $table_name.'`'.$field_name.'` IN ('.implode(',', $search_keys).')'; - } - - $field_processed = true; - break; - - case 'kDateFormatter': - // if date is searched using direct filter, then do nothing here, otherwise search using LIKE clause - $field_processed = ($custom_search !== false) ? true : false; - break; - - default: - $field_processed = false; - break; - } - - // if not already processed by formatter, then get clause by field type - if (!$field_processed && $search_keywords) { - switch($field_type) - { - case 'int': - case 'integer': - case 'numeric': - $search_keys = Array(); - foreach ($search_keywords as $keyword => $sign) { - if (!is_numeric($keyword) || ($sign == '-')) continue; - $search_keys[] = $this->Conn->qstr($keyword); - } - - if ($search_keys) { - $filter_value = $table_name.'`'.$field_name.'` IN ('.implode(',', $search_keys).')'; - } - break; - - case 'double': - case 'float': - case 'real': - $search_keys = Array(); - foreach ($search_keywords as $keyword => $sign) { - $keyword = str_replace(',', '.', $keyword); - if (!is_numeric($keyword) || ($sign == '-')) continue; - $search_keys[] = 'ABS('.$table_name.'`'.$field_name.'` - '.$this->Conn->qstr($keyword).') <= 0.0001'; - } - - if ($search_keys) { - $filter_value = '('.implode(') OR (', $search_keys).')'; - } - break; - - case 'string': - $filter_value = $this->buildWhereClause($search_keyword, Array($table_name.'`'.$field_name.'`')); - break; - } - } - - if ($filter_value) { - return Array('type' => $sql_filter_type, 'value' => $filter_value); - } - - return false; - } - - /** - * Processes custom filters from submit - * - * @param KEvent $event - * @return bool - */ - function processCustomFilters(&$event) - { - $grid_name = $this->Application->GetVar('grid_name'); - - // update "custom filter" with values from submit: begin - $view_name = $this->Application->RecallVar($event->getPrefixSpecial().'_current_view'); - $custom_filters = $this->Application->RecallPersistentVar($event->getPrefixSpecial().'_custom_filter.'.$view_name); - if ($custom_filters) { - $custom_filters = unserialize($custom_filters); - $custom_filter = isset($custom_filters[$grid_name]) ? $custom_filters[$grid_name] : Array (); - } - else { - $custom_filter = Array (); - } - - // submit format custom_filters[prefix_special][field] - $submit_filters = $this->Application->GetVar('custom_filters'); - if ($submit_filters) { - $submit_filters = getArrayValue($submit_filters, $event->getPrefixSpecial(), $grid_name); - if ($submit_filters) { - foreach ($submit_filters as $field_name => $field_options) { - list ($filter_type, $field_value) = each($field_options); - $is_empty = strlen(is_array($field_value) ? implode('', $field_value) : $field_value) == 0; - if ($is_empty) { - if (isset($custom_filter[$field_name])) { - // use isset, because non-existing key will cause "php notice"! - unset($custom_filter[$field_name][$filter_type]); // remove filter - - if (!$custom_filter[$field_name]) { - // if no filters left for field, then delete record at all - unset($custom_filter[$field_name]); - } - } - } - else { - $custom_filter[$field_name][$filter_type]['submit_value'] = $field_value; - } - } - } - } - - if ($custom_filter) { - $custom_filters[$grid_name] = $custom_filter; - } - else { - unset($custom_filters[$grid_name]); - } - // update "custom filter" with values from submit: end - - if (!$custom_filter) { - // in case when no filters specified, there are nothing to process - $this->Application->StorePersistentVar($event->getPrefixSpecial().'_custom_filter.'.$view_name, serialize($custom_filters) ); - return false; - } - - $object =& $event->getObject(); // don't recall it each time in getCustomFilterSearchClause - $grid_info = $this->Application->getUnitOption($event->Prefix.'.'.$grid_name, 'Grids'); - foreach ($custom_filter as $field_name => $field_options) { - list ($filter_type, $field_options) = each($field_options); - $field_options['grid_options'] = $grid_info['Fields'][$field_name]; - $field_options = $this->getCustomFilterSearchClause($object, $field_name, $filter_type, $field_options); - if ($field_options['value']) { - unset($field_options['grid_options']); - $custom_filter[$field_name][$filter_type] = $field_options; - } - } - - $custom_filters[$grid_name] = $custom_filter; - $this->Application->StorePersistentVar($event->getPrefixSpecial().'_custom_filter.'.$view_name, serialize($custom_filters) ); - return $custom_filter; - } - - /** - * Return numeric range filter value + checking that it's number - * - * @param Array $value array containing range filter value - * @return unknown - */ - function getRangeValue($value) - { - return strlen($value) && is_numeric($value) ? $this->Conn->qstr($value) : false; - } - - function getCustomFilterSearchClause(&$object, $field_name, $filter_type, $field_options) - { - // this is usually used for mutlilingual fields and date fields - if (isset($field_options['grid_options']['sort_field'])) { - $field_name = $field_options['grid_options']['sort_field']; - } - extract( $this->getFieldInformation($object, $field_name) ); // see getFieldInformation for more details - - $filter_value = ''; - - switch ($filter_type) { - case 'range': - $from = $this->getRangeValue($field_options['submit_value']['from']); - $to = $this->getRangeValue($field_options['submit_value']['to']); - - if ($from !== false && $to !== false) { - // add range filter - $filter_value = $table_name.'`'.$field_name.'` >= '.$from.' AND '.$table_name.'`'.$field_name.'` <= '.$to; - } - elseif ($from !== false) { - // add equals filter on $from - $filter_value = $table_name.'`'.$field_name.'` = '.$from; - } - elseif ($to !== false) { - // add equals filter on $to - $filter_value = $table_name.'`'.$field_name.'` = '.$to; - } - break; - - case 'float_range': - // MySQL can't compare values in "float" type columns using "=" operator - $from = $this->getRangeValue($field_options['submit_value']['from']); - $to = $this->getRangeValue($field_options['submit_value']['to']); - - if ($from !== false && $to !== false) { - // add range filter - $filter_value = $table_name.'`'.$field_name.'` >= '.$from.' AND '.$table_name.'`'.$field_name.'` <= '.$to; - } - elseif ($from !== false) { - // add equals filter on $from - $filter_value = 'ABS('.$table_name.'`'.$field_name.'` - '.$from.') <= 0.0001'; - } - elseif ($to !== false) { - // add equals filter on $to - $filter_value = 'ABS('.$table_name.'`'.$field_name.'` - '.$to.') <= 0.0001'; - } - break; - - case 'date_range': - $from = $this->processRangeField($object, $field_name, $field_options['submit_value'], 'from'); - $to = $this->processRangeField($object, $field_name, $field_options['submit_value'], 'to'); - - $day_seconds = 23 * 60 * 60 + 59 * 60 + 59; - if ($from !== false && $to === false) { - $from = strtotime(date('Y-m-d', $from).' 00:00:00', $from); // reset to morning - $to = $from + $day_seconds; - } - elseif ($from === false && $to !== false) { - $to = strtotime(date('Y-m-d', $to).' 23:59:59', $to); // reset to evening - $from = $to - $day_seconds; - } - - if ($from !== false && $to !== false) { - $filter_value = $table_name.'`'.$field_name.'` >= '.$from.' AND '.$table_name.'`'.$field_name.'` <= '.$to; - } - break; - - case 'equals': - case 'options': - $field_value = strlen($field_options['submit_value']) ? $this->Conn->qstr($field_options['submit_value']) : false; - if ($field_value) { - $filter_value = $table_name.'`'.$field_name.'` = '.$field_value; - } - break; - - case 'like': - $filter_value = $this->buildWhereClause($field_options['submit_value'], Array($table_name.'`'.$field_name.'`')); - break; - - default: - break; - } - - $field_options['sql_filter_type'] = $sql_filter_type; - $field_options['value'] = $filter_value; - - return $field_options; - } - - /** - * Enter description here... - * - * @param kdbItem $object - * @param string $search_field - * @param string $value - * @param string $type - */ - function processRangeField(&$object, $search_field, $value, $type) - { - if (!strlen($value[$type])) { - return false; - } - - $lang_current =& $this->Application->recallObject('lang.current'); - $options = $object->GetFieldOptions($search_field); - $dt_separator = isset($options['date_time_separator']) ? $options['date_time_separator'] : ' '; - - $tmp_value = explode($dt_separator, $value[$type], 2); - if (count($tmp_value) == 1) { - // time is missing, then guess it and add to date - $time = ($type == 'from') ? adodb_mktime(0, 0, 0) : adodb_mktime(23, 59, 59); - $time = adodb_date($lang_current->GetDBField('InputTimeFormat'), $time); - $value[$type] .= $dt_separator.$time; - } - - $formatter =& $this->Application->recallObject($object->Fields[$search_field]['formatter']); - - $value_ts = $formatter->Parse($value[$type], $search_field, $object); - $pseudo = getArrayValue($object->FieldErrors, $search_field, 'pseudo'); - if ($pseudo) { - unset($object->FieldErrors[$search_field]); // remove error! - // invalid format -> ignore this date in search - return false; - } - return $value_ts; - } - - /** - * Resets current search - * - * @param kEvent $event - */ - function resetSearch(&$event) - { - $this->Application->RemoveVar($event->getPrefixSpecial().'_search_filter'); - $this->Application->RemoveVar($event->getPrefixSpecial().'_search_keyword'); - - $view_name = $this->Application->RecallVar($event->getPrefixSpecial().'_current_view'); - $this->Application->RemovePersistentVar($event->getPrefixSpecial().'_custom_filter.'.$view_name); - } - - /** - * Creates filters based on "types" & "except" parameters from PrintList - * - * @param kEvent $event - * @param Array $type_clauses - * @param string $types - * @param string $except_types - */ - function SetComplexFilter(&$event, &$type_clauses, $types, $except_types) - { - $includes_or_filter =& $this->Application->makeClass('kMultipleFilter', FLT_TYPE_OR); - $excepts_and_filter =& $this->Application->makeClass('kMultipleFilter', FLT_TYPE_AND); - - $includes_or_filter_h =& $this->Application->makeClass('kMultipleFilter', FLT_TYPE_OR); - $excepts_and_filter_h =& $this->Application->makeClass('kMultipleFilter', FLT_TYPE_AND); - - if ($types) { - $types = explode(',', $types); - foreach ($types as $type) { - $type = trim($type); - - if (isset($type_clauses[$type])) { - if ($type_clauses[$type]['having_filter']) { - $includes_or_filter_h->addFilter('filter_'.$type, $type_clauses[$type]['include']); - } else { - $includes_or_filter->addFilter('filter_'.$type, $type_clauses[$type]['include']); - } - } - } - } - - if ($except_types) { - $except_types = explode(',', $except_types); - foreach ($except_types as $type) { - $type = trim($type); - - if (isset($type_clauses[$type])) { - if ($type_clauses[$type]['having_filter']) { - $excepts_and_filter_h->addFilter('filter_'.$type, $type_clauses[$type]['except']); - } else { - $excepts_and_filter->addFilter('filter_'.$type, $type_clauses[$type]['except']); - } - } - } - } - - $object =& $event->getObject(); - - $object->addFilter('includes_filter', $includes_or_filter); - $object->addFilter('excepts_filter', $excepts_and_filter); - - $object->addFilter('includes_filter_h', $includes_or_filter_h, HAVING_FILTER); - $object->addFilter('excepts_filter_h', $excepts_and_filter_h, HAVING_FILTER); - } - } - -?> \ No newline at end of file