Index: branches/5.2.x/core/kernel/utility/formatters/date_formatter.php =================================================================== --- branches/5.2.x/core/kernel/utility/formatters/date_formatter.php (revision 16668) +++ branches/5.2.x/core/kernel/utility/formatters/date_formatter.php (revision 16669) @@ -1,528 +1,532 @@ language = $this->Application->recallObject('lang.current'); } /** * Sets mixed format (date + time) for field if not set directly * * @param Array $field_options options of field * @param Array $format separate formats for date & time * @param string $type destination key in field_options to store mixed format */ function SetMixedFormat(&$field_options, &$format, $type) { if (!isset($field_options[$type])) { // default value is date+separator+time $field_options[$type] = '_regional_DateTimeFormat'; } if ($field_options[$type] == '_regional_DateTimeFormat') { $field_options[$type] = $format['date'].$field_options['date_time_separator'].$format['time']; } else if(preg_match('/_regional_(.*)/', $field_options[$type], $regs)) { $field_options[$type] = $this->language->GetDBField($regs[1]); } $format['mixed'] = $field_options[$type]; } /** * Returns separate formats for date,time,combined for input & display formats * * @param Array $field_options options of field * @param string $type type of requested information = {mixed,date,time} * @return Array display & input formats */ function GetSeparateFormats(&$field_options, $type) { if ($type == 'mixed') { if (!isset($field_options['date_time_separator'])) $field_options['date_time_separator'] = ' '; $display_format = Array (); $input_format = Array (); list ($display_format['date'], $input_format['date']) = $this->GetSeparateFormats($field_options, 'date'); list ($display_format['time'], $input_format['time']) = $this->GetSeparateFormats($field_options, 'time'); $this->SetMixedFormat($field_options, $display_format, 'format'); $this->SetMixedFormat($field_options, $input_format, 'input_format'); return Array ($display_format, $input_format); } else { // 1. set display format if (isset($field_options[$type.'_format'])) { $format = $field_options[$type.'_format']; } else { $format = $this->language->GetDBField(ucfirst($type).'Format'); } // 2. set input format if (isset($field_options['input_'.$type.'_format'])) { $input_format = $field_options['input_'.$type.'_format']; } else { $input_format = $this->language->GetDBField('Input'.ucfirst($type).'Format'); } return Array ($format, $input_format); } } /** * The method is supposed to alter config options or configure object in some way based on its usage of formatters * The methods is called for every field with formatter defined when configuring item. * Could be used for adding additional VirtualFields to an object required by some special Formatter * * @param string $field_name * @param array $field_options * @param kDBBase $object */ function PrepareOptions($field_name, &$field_options, &$object) { list ($display_format, $input_format) = $this->GetSeparateFormats($field_options, 'mixed'); $field_options['sub_fields'] = Array('date' => $field_name.'_date', 'time' => $field_name.'_time'); if (!isset($field_options['use_timezone'])) { // apply timezone from server $field_options['use_timezone'] = true; } // 1. add field to indicate, that date is already combined into one field $add_fields = Array ( $field_name . '_combined' => Array ('type' => 'int', 'default' => 0), ); // 2. add DATE virtual field $opts = Array('master_field' => $field_name, 'formatter' => 'kDateFormatter', 'format' => $display_format['date'], 'input_format' => $input_format['date']); $copy_options = Array ('type', 'default', 'required', 'use_timezone', 'error_msgs'); foreach ($copy_options as $copy_option) { if ( array_key_exists($copy_option, $field_options) ) { $opts[$copy_option] = $field_options[$copy_option]; } } $add_fields[$field_name . '_date'] = $opts; // 3. add TIME virtual field $opts['format'] = $display_format['time']; $opts['input_format'] = $input_format['time']; $add_fields[$field_name . '_time'] = $opts; $filter_type = getArrayValue($field_options, 'filter_type'); if ( $filter_type == 'range' ) { $opts['format'] = $field_options['format']; $add_fields[$field_name . '_rangefrom'] = $opts; $add_fields[$field_name . '_rangeto'] = $opts; } if ( !$object->isVirtualField($field_name) ) { // adding calculated field to format date directly in the query $object->addCalculatedField($field_name . '_date', '%1$s.' . $field_name); $object->addCalculatedField($field_name . '_time', '%1$s.' . $field_name); } $virtual_fields = $object->getVirtualFields(); $add_fields = kUtil::array_merge_recursive($add_fields, $virtual_fields); $object->setVirtualFields($add_fields); } /** * Used for split fields like timestamp -> date, time * Called from DBItem to update sub fields values after loading item * * @param string $field * @param string $value * @param Array $options * @param kDBItem $object * @return void * @access public */ public function UpdateSubFields($field, $value, &$options, &$object) { $sub_fields = getArrayValue($options, 'sub_fields'); if ( !$sub_fields || !isset($value) || !$value ) { return ; } $object->SetDBField($sub_fields['date'], $value); $object->SetDBField($sub_fields['time'], $value); } /** * Used for split fields like timestamp -> date, time * Called from DBItem Validate (before validation) to get back master field value from its sub_fields * * @param string $field * @param mixed $value * @param Array $options * @param kDBItem $object */ function UpdateMasterFields($field, $value, &$options, &$object) { $sub_fields = getArrayValue($options, 'sub_fields'); $master_field = getArrayValue($options, 'master_field'); if ( $master_field ) { // when in one of sub_fields - call update for master_field to update its value from sub_fields [are you following ? :) ] $opt = $object->GetFieldOptions($master_field); $this->UpdateMasterFields($master_field, null, $opt, $object); } elseif ( $sub_fields && !$object->GetDBField($field . '_combined') ) { // when in master field - set own value from sub_fields if ( $object->GetDBField($sub_fields['date']) != '' && $object->GetDBField($sub_fields['time']) == '' ) { // when time is not supplied, then use "midnight" (or unit config override) $empty_time = getArrayValue($options, 'empty_time'); if ( $empty_time === false ) { $empty_time = adodb_mktime(0, 0, 0); } $object->SetDBField($sub_fields['time'], $empty_time); } elseif ( $object->GetDBField($sub_fields['time']) != '' && $object->GetDBField($sub_fields['date']) == '' ) { // when date is not supplied, then use "1970-01-01 00:00:00" instead (or unit config override) $empty_date = getArrayValue($options, 'empty_date'); if ( $empty_date === false ) { $empty_date = adodb_mktime(0, 0, 0, 1, 1, 1970); } $object->SetDBField($sub_fields['date'], $empty_date); } $input_format['date'] = $object->GetFieldOption($sub_fields['date'], 'input_format'); $input_format['time'] = $object->GetFieldOption($sub_fields['time'], 'input_format'); $object->SetField($field, $object->GetField($sub_fields['date'], $input_format['date']) . $options['date_time_separator'] . $object->GetField($sub_fields['time'], $input_format['time'])); } } /** * Formats value of a given field * * @param string $value Value. * @param string $field_name Field name. * @param kDBBase $object Object. * @param string $format Format. * * @return string */ public function Format($value, $field_name, &$object, $format = null) { $options = $object->GetFieldOptions($field_name); if ( is_null($value) ) { if ( $format != 'picker' || !isset($options['picker_default']) ) { return ''; } $value = strtotime($options['picker_default']); } if ( $format == 'picker' ) { $format = '_input_'; } if ( !is_numeric($value) ) { return $value; // for leaving badly formatted date on the form } settype($value, 'int'); if ( !is_int($value) ) { return $value; } if ( isset($format) ) { $options['format'] = $format; } if ( preg_match('/_regional_(.*)/', $options['format'], $regs) ) { // when such type of format is given directly to kDBBase::GetField $options['format'] = $this->language->GetDBField($regs[1]); } if ( $options['format'] == '_input_' ) { // use input format instead of output format $options['format'] = $options['input_format']; } if ( !$options['use_timezone'] ) { return adodb_gmdate($options['format'], $value); } $format = defined($options['format']) ? constant($options['format']) : $options['format']; $dt_separator = getArrayValue($options, 'date_time_separator'); if ( $dt_separator ) { $format = trim($format, $dt_separator); } return adodb_date($format, $value); } function HumanFormat($format) { $patterns = Array('/m/', '/n/', '/d/', '/j/', '/y/', '/Y/', '/h|H/', '/g|G/', '/i/', '/s/', '/a|A/'); $replace = Array( 'mm', 'm', 'dd', 'd', 'yy', 'yyyy', 'hh', 'h', 'mm', 'ss', 'AM'); $res = preg_replace($patterns, $replace, $format); return $res; } function SQLFormat($format) { $mapping = Array( '/%/' => '%%', '/(? '%p', // Lowercase Ante meridiem and Post meridiem => MySQL provides only uppercase '/(? '%p', // Uppercase Ante meridiem and Post meridiem '/(? '%d', // Day of the month, 2 digits with leading zeros '/(? '%a', // A textual representation of a day, three letters '/(? '%M', // A full textual representation of a month, such as January or March '/(? '%l', // 12-hour format of an hour without leading zeros '/(? '%k', // 24-hour format of an hour without leading zeros '/(? '%h', // 12-hour format of an hour with leading zeros '/(? '%H', // 24-hour format of an hour with leading zeros '/(? '%i', // Minutes with leading zeros '/(? 'N/A', // Whether or not the date is in daylights savings time '/(? 'N/A', // English ordinal suffix for the day of the month, 2 characters, see below '/jS/' => '%D', // MySQL can't return separate suffix, but could return date with suffix '/(? '%e', // Day of the month without leading zeros '/(? '%W', // A full textual representation of the day of the week '/(? 'N/A', // Whether it's a leap year '/(? '%m', // Numeric representation of a month, with leading zeros '/(? '%b', // A short textual representation of a month, three letters '/(? '%c', // Numeric representation of a month, without leading zeros '/(? 'N/A', // Difference to Greenwich time (GMT) in hours '/(? 'N/A', // RFC 2822 formatted date '/(? '%s', // Seconds, with leading zeros // S and jS moved before j - see above '/(? 'N/A', // Number of days in the given month '/(? 'N/A', // Timezone setting of this machine '/(? 'N/A', // Seconds since the Unix Epoch (January 1 1970 00:00:00 GMT) '/(? '%w', // Numeric representation of the day of the week '/(? '%v', // ISO-8601 week number of year, weeks starting on Monday (added in PHP 4.1.0) '/(? '%Y', // A full numeric representation of a year, 4 digits '/(? '%y', // A two digit representation of a year '/(? 'N/A', // The day of the year (starting from 0) => MySQL starts from 1 '/(? 'N/A', // Timezone offset in seconds. The offset for timezones west of UTC is always negative, and for those east of UTC is always positive. ); $patterns = array_keys($mapping); $replacements = array_values($mapping); $res = preg_replace($patterns, $replacements, $format); return $res; } /** * Converts formatted date+time to timestamp and validates format * * @param mixed $value * @param string $field_name * @param kDBItem $object + * @param string $format Format. * @return mixed * @access public */ - public function Parse($value, $field_name, &$object) + public function Parse($value, $field_name, &$object, $format = null) { $options = $object->GetFieldOptions($field_name); $dt_separator = getArrayValue($options,'date_time_separator'); if($dt_separator) $value = trim($value, $dt_separator); if($value == '') return NULL; //return strtotime($value); - $format = $options['input_format']; + if ( !isset($format) ) { + $format = $options['input_format']; + } + if ($dt_separator) $format = trim($format, $dt_separator); $error_params = array( 'format' => $this->HumanFormat($format), 'sample' => adodb_date($format), 'value' => $value, ); $hour = 0; $minute = 0; $second = 0; $month = 1; $day = 1; $year = 1970; $patterns['n'] = '([0-9]{1,2})'; $patterns['m'] = '([0-9]{1,2})'; $patterns['d'] = '([0-9]{1,2})'; $patterns['j'] = '([0-9]{1,2})'; $patterns['Y'] = '([0-9]{4})'; $patterns['y'] = '([0-9]{2})'; $patterns['G'] = '([0-9]{1,2})'; $patterns['g'] = '([0-9]{1,2})'; $patterns['H'] = '([0-9]{2})'; $patterns['h'] = '([0-9]{2})'; $patterns['i'] = '([0-9]{2})'; $patterns['s'] = '([0-9]{2})'; $patterns['a'] = '(am|pm)'; $patterns['A'] = '(AM|PM)'; $holders_mask = '/' . preg_replace('/[a-zA-Z]{1}/i', '([a-zA-Z]{1})', preg_quote($format, '/')) . '/'; if (!preg_match($holders_mask, $format, $holders)) { $object->SetError($field_name, 'bad_date_format', null, $error_params); return $value; } $values_mask = '/^' . preg_quote($format, '/') . '$/'; foreach ($patterns as $key => $val) { $values_mask = str_replace($key, $val, $values_mask); } if (!preg_match($values_mask, $value, $values)) { $object->SetError($field_name, 'bad_date_format', null, $error_params); return $value; } for ($i = 1; $i < count($holders); $i++) { switch ($holders[$i]) { case 'n': case 'm': $month = $values[$i]; $month = preg_replace('/^0{1}/', '', $month); break; case 'd': $day = $values[$i]; $day = preg_replace('/^0{1}/', '', $day); break; case 'Y': $year = $values[$i]; break; case 'y': $year = $values[$i] >= 70 ? 1900 + $values[$i] : 2000 + $values[$i]; break; case 'H': case 'h': case 'G': case 'g': $hour = $values[$i]; $hour = preg_replace('/^0{1}/', '', $hour); break; case 'i': $minute = $values[$i]; $minute = preg_replace('/^0{1}/', '', $minute); break; case 's': $second = $values[$i]; $second = preg_replace('/^0{1}/', '', $second); break; case 'a': case 'A': if ($hour <= 12) { // if AM/PM used with 24-hour - could happen :) if ($values[$i] == 'pm' || $values[$i] == 'PM') { $hour += 12; if ($hour == 24) $hour = 12; } elseif ($values[$i] == 'am' || $values[$i] == 'AM') { if ($hour == 12) $hour = 0; } } break; } } //echo "day: $day, month: $month, year: $year, hour: $hour, minute: $minute
"; /*if (!($year >= 1970 && $year <= 2037)) { $object->SetError($field_name, 'bad_date_format', null, $error_params); return $value; }*/ if (!($month >= 1 && $month <= 12)) { $object->SetError($field_name, 'bad_date_format', null, $error_params); return $value; } $months_days = Array ( 1 => 31,2 => 28, 3 => 31, 4 => 30,5 => 31,6 => 30, 7 => 31, 8 => 31,9 => 30,10 => 31,11 => 30,12 => 31); if ($year % 4 == 0) $months_days[2] = 29; if (!($day >=1 && $day <= $months_days[$month])) { $object->SetError($field_name, 'bad_date_format', null, $error_params); return $value; } if (!($hour >=0 && $hour <= 23)) { $object->SetError($field_name, 'bad_date_format', null, $error_params); return $value; } if (!($minute >=0 && $minute <= 59)) { $object->SetError($field_name, 'bad_date_format', null, $error_params); return $value; } if (!($second >=0 && $second <= 59)) { $object->SetError($field_name, 'bad_date_format', null, $error_params); return $value; } if (!$options['use_timezone']) { return adodb_gmmktime($hour, $minute, $second, $month, $day, $year); } return adodb_mktime($hour, $minute, $second, $month, $day, $year); } function GetSample($field, &$options, &$object) { return $this->Format( adodb_mktime(), $field, $object, $options['input_format']); } } Index: branches/5.2.x/core/units/helpers/search_helper.php =================================================================== --- branches/5.2.x/core/units/helpers/search_helper.php (revision 16668) +++ branches/5.2.x/core/units/helpers/search_helper.php (revision 16669) @@ -1,839 +1,858 @@ _performExactSearch = $this->Application->ConfigValue('PerformExactSearch'); } /** * Splits search phrase into keyword using quotes,plus and minus sings and spaces as split criteria * * @param string $keyword * @return Array * @access public */ public function splitKeyword($keyword) { if ( $this->Application->ConfigValue('CheckStopWords') ) { $keyword_after_remove = $this->_removeStopWords($keyword); if ( $keyword_after_remove ) { // allow to search through stop word grid $keyword = $keyword_after_remove; } } $final = Array (); $quotes_re = '/([+\-]?)"(.*?)"/'; $no_quotes_re = '/([+\-]?)([^ ]+)/'; preg_match_all($quotes_re, $keyword, $res); foreach ($res[2] as $index => $kw) { $final[$kw] = $res[1][$index]; } $keyword = preg_replace($quotes_re, '', $keyword); preg_match_all($no_quotes_re, $keyword, $res); foreach ($res[2] as $index => $kw) { $final[$kw] = $res[1][$index]; } if ( $this->_performExactSearch ) { foreach ($final AS $kw => $plus_minus) { if ( !$plus_minus ) { $final[$kw] = '+'; } } } return $final; } function getPositiveKeywords($keyword) { $keywords = $this->splitKeyword($keyword); $ret = Array(); foreach ($keywords as $keyword => $sign) { if ($sign == '+' || $sign == '') { $ret[] = $keyword; } } return $ret; } /** * Replace wildcards to match MySQL * * @param string $keyword * @return string */ function transformWildcards($keyword) { return str_replace(Array ('%', '_', '*', '?') , Array ('\%', '\_', '%', '_'), $keyword); } function buildWhereClause($keyword, $fields) { $keywords = $this->splitKeyword( $this->transformWildcards($keyword) ); $normal_conditions = $plus_conditions = $minus_conditions = Array(); foreach ($keywords as $keyword => $sign) { $keyword = $this->Conn->escape($keyword); switch ($sign) { case '+': $plus_conditions[] = implode(" LIKE '%" . $keyword . "%' OR ", $fields) . " LIKE '%" . $keyword . "%'"; break; case '-': $condition = Array (); foreach ($fields as $field) { $condition[] = $field . " NOT LIKE '%" . $keyword . "%' OR " . $field . ' IS NULL'; } $minus_conditions[] = '(' . implode(') AND (', $condition) . ')'; break; case '': $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 = $object->isVirtualField($field_name) ? 'having' : 'where'; $field_options = $object->GetFieldOptions($field_name); $table_name = ''; $field_type = isset($field_options['type']) ? $field_options['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_type, $table_name, $sql_filter_type); } /** * Removes stop words from keyword * * @param string $keyword * @return string */ function _removeStopWords($keyword) { static $stop_words = Array (); if (!$stop_words) { $sql = 'SELECT StopWord FROM ' . $this->Application->getUnitOption('stop-word', 'TableName') . ' ORDER BY LENGTH(StopWord) DESC, StopWord ASC'; $stop_words = $this->Conn->GetCol($sql); foreach ($stop_words as $index => $stop_word) { $stop_words[$index] = '/(^| )' . preg_quote($stop_word, '/') . '( |$)/'; } } $keyword = preg_replace($stop_words, ' ', $keyword); return trim( preg_replace('/[ ]+/', ' ', $keyword) ); } /** * Performs new search on a given grid * * @param kEvent $event * @return void * @access public */ public function performSearch($event) { /** @var kDBItem $object */ $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); $custom_filter = $this->processCustomFilters($event); if ( !$search_keyword && $custom_filter === false ) { $this->resetSearch($event); return ; } if ( $search_keyword ) { $this->processAutomaticFilters($event, $search_keyword, $custom_filter); } } /** * Creates filtering sql clauses based on given search restrictions * * @param kEvent $event * @param string $search_keyword * @param Array $custom_filter * @return void */ 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(); /** @var kDBList $object */ $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 $field_name * @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) { if ($object->isVirtualField($field_name) && !$object->isCalculatedField($field_name)) { // Virtual field, that is shown in grid, but it doesn't have corresponding calculated field. // Happens, when field value is calculated on the fly (during grid display) and it is not searchable. return ''; } $search_keywords = $this->splitKeyword($search_keyword); list ($field_name, $field_type, $table_name, $sql_filter_type) = $this->_getFieldInformation($object, $field_name); $filter_value = ''; // get field clause by formatter name and/or parameters $field_options = $object->GetFieldOptions($field_name); $formatter = getArrayValue($field_options, '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($field_options, 'use_phrases'); $multiple = array_key_exists('multiple', $field_options) && $field_options['multiple']; foreach ($field_options['options'] as $key => $val) { $match_to = mb_strtolower($use_phrases ? $this->Application->Phrase($val) : $val); foreach ($search_keywords as $keyword => $sign) { // doesn't support wildcards if (strpos($match_to, mb_strtolower($keyword)) === false) { if ($sign == '+') { $filter_value = $table_name.'`'.$field_name.'` = NULL'; break; } else { continue; } } if ($sign == '+' || $sign == '') { // don't add single quotes to found option ids when multiselect (but escape string anyway) $search_keys[$key] = $multiple ? $this->Conn->escape($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) { if ($multiple) { $filter_value = $table_name.'`'.$field_name.'` LIKE "%|' . implode('|%" OR ' . $table_name.'`'.$field_name.'` LIKE "%|', $search_keys) . '|%"'; } else { $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 Array|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/*, ALLOW_DEFAULT_SETTINGS*/); 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; } /** * Checks, that range filters "To" part is defined for given grid * * @param string $prefix_special * @param string $grid_name * @return bool */ function rangeFiltersUsed($prefix_special, $grid_name) { static $cache = Array (); $cache_key = $prefix_special . $grid_name; if (array_key_exists($cache_key, $cache)) { return $cache[$cache_key]; } $view_name = $this->Application->RecallVar($prefix_special . '_current_view'); $custom_filters = $this->Application->RecallPersistentVar($prefix_special . '_custom_filter.' . $view_name/*, ALLOW_DEFAULT_SETTINGS*/); if (!$custom_filters) { // filters not defined for given prefix $cache[$cache_key] = false; return false; } $custom_filters = unserialize($custom_filters); if (!is_array($custom_filters) || !array_key_exists($grid_name, $custom_filters)) { // filters not defined for given grid $cache[$cache_key] = false; return false; } $range_filter_defined = false; $custom_filter = $custom_filters[$grid_name]; foreach ($custom_filter as $field_name => $field_options) { list ($filter_type, $field_options) = each($field_options); if (strpos($filter_type, 'range') === false) { continue; } $to_value = (string)$field_options['submit_value']['to']; if ($to_value !== '') { $range_filter_defined = true; break; } } $cache[$cache_key] = $range_filter_defined; return $range_filter_defined; } /** * Return numeric range filter value + checking that it's number * * @param Array $value array containing range filter value * @return unknown */ function getRangeValue($value) { // fix user typing error, since MySQL only sees "." as decimal separator $value = str_replace(',', '.', $value); return strlen($value) && is_numeric($value) ? $this->Conn->qstr($value) : false; } /** * Returns filter clause * * @param kDBItem $object * @param string $field_name * @param string $filter_type * @param Array $field_options * @return Array */ 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']; } list ($field_name, $field_type, $table_name, $sql_filter_type) = $this->_getFieldInformation($object, $field_name); $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 ( $field_type == 'int' || $field_type == 'integer' ) { if ( $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; } } else { // MySQL can't compare values in "float" type columns using "=" operator if ( $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 + + if ( is_numeric($from) && $to === null && date('H:i:s', $from) == '00:00:00' ) { $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; + elseif ( $from === null && is_numeric($to) && date('H:i:s', $to) == '00:00:00' ) { + $from = $to; + $to += $day_seconds; + } + + if ( is_numeric($from) && $to === null || $from === null && is_numeric($to) ) { + $from = $from === null ? $to : $from; + $to = $from; } - if ($from !== false && $to !== false) { + if ( is_numeric($from) && is_numeric($to) ) { + $from = strtotime(date('Y-m-d H:i', $from) . ':00', $from); + $to = strtotime(date('Y-m-d H:i', $to) . ':59', $to); $filter_value = $table_name.'`'.$field_name.'` >= '.$from.' AND '.$table_name.'`'.$field_name.'` <= '.$to; } + else { + $filter_value = 'FALSE'; + } 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 'picker': $field_value = strlen($field_options['submit_value']) ? $this->Conn->escape($field_options['submit_value']) : false; if ($field_value) { $filter_value = $table_name.'`'.$field_name.'` LIKE "%|'.$field_value.'|%"'; } break; case 'multioptions': $field_value = $field_options['submit_value']; if ( $field_value ) { $field_value = explode('|', substr($field_value, 1, -1)); $multiple = $object->GetFieldOption($field_name, 'multiple'); $field_value = $this->Conn->qstrArray($field_value, $multiple ? 'escape' : 'qstr'); if ( $multiple ) { $filter_value = $table_name . '`' . $field_name . '` LIKE "%|' . implode('|%" OR ' . $table_name . '`' . $field_name . '` LIKE "%|', $field_value) . '|%"'; } else { $filter_value = $table_name . '`' . $field_name . '` IN (' . implode(',', $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 + * @param string $format_option_prefix Format option prefix. */ - function processRangeField(&$object, $search_field, $value, $type) + function processRangeField(&$object, $search_field, $value, $type, $format_option_prefix = '') { - if ( !strlen($value[$type]) ) { - return false; + $value_by_type = $value[$type]; + + if ( !strlen($value_by_type) ) { + return null; } $options = $object->GetFieldOptions($search_field); $dt_separator = array_key_exists('date_time_separator', $options) ? $options['date_time_separator'] : ' '; - $value[$type] = trim($value[$type], $dt_separator); // trim any + $value_by_type = trim($value_by_type, $dt_separator); // trim any - $tmp_value = explode($dt_separator, $value[$type], 2); + $tmp_value = explode($dt_separator, $value_by_type, 2); if ( count($tmp_value) == 1 ) { - $time_format = $this->_getInputTimeFormat($options); + $time_format = $this->_getInputTimeFormat($options, $format_option_prefix . 'time_format'); if ( $time_format ) { // time is missing, but time format available -> guess time and add to date - $time = ($type == 'from') ? adodb_mktime(0, 0, 0) : adodb_mktime(23, 59, 59); + $time = adodb_mktime(0, 0, 0); $time = adodb_date($time_format, $time); - $value[$type] .= $dt_separator . $time; + $value_by_type .= $dt_separator . $time; } } /** @var kFormatter $formatter */ $formatter = $this->Application->recallObject($options['formatter']); - - $value_ts = $formatter->Parse($value[$type], $search_field, $object); + $format = $options[$format_option_prefix . 'format']; + $value_ts = $formatter->Parse($value_by_type, $search_field, $object, $format); if ( $object->GetErrorPseudo($search_field) ) { // invalid format -> ignore this date in search $object->RemoveError($search_field); - return false; + if ( $format_option_prefix == 'input_' ) { + return false; + } + + return $this->processRangeField($object, $search_field, $value, $type, 'input_'); } return $value_ts; } /** * Returns InputTimeFormat using given field options * - * @param Array $field_options + * @param Array $field_options + * @param string $format_option_name Format option name. * @return string */ - function _getInputTimeFormat($field_options) + function _getInputTimeFormat($field_options, $format_option_name = 'input_time_format') { - if ( array_key_exists('input_time_format', $field_options) ) { - return $field_options['input_time_format']; + if ( array_key_exists($format_option_name, $field_options) ) { + return $field_options[$format_option_name]; } /** @var LanguagesItem $lang_current */ $lang_current = $this->Application->recallObject('lang.current'); + $field_name = str_replace(' ', '', ucwords(str_replace('_', ' ', $format_option_name))); - return $lang_current->GetDBField('InputTimeFormat'); + return $lang_current->GetDBField($field_name); } /** * 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) { /** @var kMultipleFilter $includes_or_filter */ $includes_or_filter = $this->Application->makeClass('kMultipleFilter', Array (kDBList::FLT_TYPE_OR)); /** @var kMultipleFilter $excepts_and_filter */ $excepts_and_filter = $this->Application->makeClass('kMultipleFilter', Array (kDBList::FLT_TYPE_AND)); /** @var kMultipleFilter $includes_or_filter_h */ $includes_or_filter_h = $this->Application->makeClass('kMultipleFilter', Array (kDBList::FLT_TYPE_OR)); /** @var kMultipleFilter $excepts_and_filter_h */ $excepts_and_filter_h = $this->Application->makeClass('kMultipleFilter', Array (kDBList::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']); } } } } /** @var kDBList $object */ $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, kDBList::HAVING_FILTER); $object->addFilter('excepts_filter_h', $excepts_and_filter_h, kDBList::HAVING_FILTER); } /** * Ensures empty search table * * @return void */ public function ensureEmptySearchTable() { $search_table = $this->getSearchTable(); $sql = 'CREATE TABLE IF NOT EXISTS ' . $search_table . ' ( `Relevance` decimal(8,5) DEFAULT NULL, `ItemId` int(11) NOT NULL DEFAULT 0, `ResourceId` int(11) DEFAULT NULL, `ItemType` int(1) NOT NULL DEFAULT 0, `EdPick` tinyint(4) NOT NULL DEFAULT 0, KEY `ResourceId` (`ResourceId`), KEY `Relevance` (`Relevance`) ) ENGINE = MEMORY'; $this->Conn->Query($sql); $sql = 'TRUNCATE TABLE ' . $search_table; $this->Conn->Query($sql); } /** * Search table name * * @return string */ public function getSearchTable() { return TABLE_PREFIX . 'ses_' . $this->Application->GetSID() . '_' . TABLE_PREFIX . 'Search'; } }