From db9bf8cd7339532054fb6ce2d229c8edf9cc4f31 Mon Sep 17 00:00:00 2001 From: Jean-Philippe Lang Date: Sun, 8 Feb 2015 16:23:16 +0000 Subject: [PATCH] Fixed filtering on date custom field with SQLServer. git-svn-id: http://svn.redmine.org/redmine/trunk@13993 e93f8b46-1217-0410-a6f0-8f06a7374b81 --- app/models/query.rb | 60 ++++++++++++++++++++++++++------------------- 1 file changed, 35 insertions(+), 25 deletions(-) diff --git a/app/models/query.rb b/app/models/query.rb index 847691e72..281346ece 100644 --- a/app/models/query.rb +++ b/app/models/query.rb @@ -648,7 +648,7 @@ class Query < ActiveRecord::Base if value.any? case type_for(field) when :date, :date_past - sql = date_clause(db_table, db_field, parse_date(value.first), parse_date(value.first)) + sql = date_clause(db_table, db_field, parse_date(value.first), parse_date(value.first), is_custom_filter) when :integer if is_custom_filter sql = "(#{db_table}.#{db_field} <> '' AND CAST(CASE #{db_table}.#{db_field} WHEN '' THEN '0' ELSE #{db_table}.#{db_field} END AS decimal(30,3)) = #{value.first.to_i})" @@ -683,7 +683,7 @@ class Query < ActiveRecord::Base sql << " AND #{db_table}.#{db_field} <> ''" if is_custom_filter when ">=" if [:date, :date_past].include?(type_for(field)) - sql = date_clause(db_table, db_field, parse_date(value.first), nil) + sql = date_clause(db_table, db_field, parse_date(value.first), nil, is_custom_filter) else if is_custom_filter sql = "(#{db_table}.#{db_field} <> '' AND CAST(CASE #{db_table}.#{db_field} WHEN '' THEN '0' ELSE #{db_table}.#{db_field} END AS decimal(30,3)) >= #{value.first.to_f})" @@ -693,7 +693,7 @@ class Query < ActiveRecord::Base end when "<=" if [:date, :date_past].include?(type_for(field)) - sql = date_clause(db_table, db_field, nil, parse_date(value.first)) + sql = date_clause(db_table, db_field, nil, parse_date(value.first), is_custom_filter) else if is_custom_filter sql = "(#{db_table}.#{db_field} <> '' AND CAST(CASE #{db_table}.#{db_field} WHEN '' THEN '0' ELSE #{db_table}.#{db_field} END AS decimal(30,3)) <= #{value.first.to_f})" @@ -703,7 +703,7 @@ class Query < ActiveRecord::Base end when "><" if [:date, :date_past].include?(type_for(field)) - sql = date_clause(db_table, db_field, parse_date(value[0]), parse_date(value[1])) + sql = date_clause(db_table, db_field, parse_date(value[0]), parse_date(value[1]), is_custom_filter) else if is_custom_filter sql = "(#{db_table}.#{db_field} <> '' AND CAST(CASE #{db_table}.#{db_field} WHEN '' THEN '0' ELSE #{db_table}.#{db_field} END AS decimal(30,3)) BETWEEN #{value[0].to_f} AND #{value[1].to_f})" @@ -717,64 +717,64 @@ class Query < ActiveRecord::Base sql = "#{queried_table_name}.status_id IN (SELECT id FROM #{IssueStatus.table_name} WHERE is_closed=#{self.class.connection.quoted_true})" if field == "status_id" when ">t-" # >= today - n days - sql = relative_date_clause(db_table, db_field, - value.first.to_i, nil) + sql = relative_date_clause(db_table, db_field, - value.first.to_i, nil, is_custom_filter) when "t+" # >= today + n days - sql = relative_date_clause(db_table, db_field, value.first.to_i, nil) + sql = relative_date_clause(db_table, db_field, value.first.to_i, nil, is_custom_filter) when "= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week) - sql = relative_date_clause(db_table, db_field, - days_ago, - days_ago + 6) + sql = relative_date_clause(db_table, db_field, - days_ago, - days_ago + 6, is_custom_filter) when "lw" # = last week first_day_of_week = l(:general_first_day_of_week).to_i day_of_week = Date.today.cwday days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week) - sql = relative_date_clause(db_table, db_field, - days_ago - 7, - days_ago - 1) + sql = relative_date_clause(db_table, db_field, - days_ago - 7, - days_ago - 1, is_custom_filter) when "l2w" # = last 2 weeks first_day_of_week = l(:general_first_day_of_week).to_i day_of_week = Date.today.cwday days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week) - sql = relative_date_clause(db_table, db_field, - days_ago - 14, - days_ago - 1) + sql = relative_date_clause(db_table, db_field, - days_ago - 14, - days_ago - 1, is_custom_filter) when "m" # = this month date = Date.today - sql = date_clause(db_table, db_field, date.beginning_of_month, date.end_of_month) + sql = date_clause(db_table, db_field, date.beginning_of_month, date.end_of_month, is_custom_filter) when "lm" # = last month date = Date.today.prev_month - sql = date_clause(db_table, db_field, date.beginning_of_month, date.end_of_month) + sql = date_clause(db_table, db_field, date.beginning_of_month, date.end_of_month, is_custom_filter) when "y" # = this year date = Date.today - sql = date_clause(db_table, db_field, date.beginning_of_year, date.end_of_year) + sql = date_clause(db_table, db_field, date.beginning_of_year, date.end_of_year, is_custom_filter) when "~" sql = "LOWER(#{db_table}.#{db_field}) LIKE '%#{self.class.connection.quote_string(value.first.to_s.downcase)}%'" when "!~" @@ -829,8 +829,18 @@ class Query < ActiveRecord::Base end end + def quoted_time(time, is_custom_filter) + if is_custom_filter + # Custom field values are stored as strings in the DB + # using this format that does not depend on DB date representation + time.strftime("%Y-%m-%d %H:%M:%S") + else + self.class.connection.quoted_date(time) + end + end + # Returns a SQL clause for a date or datetime field. - def date_clause(table, field, from, to) + def date_clause(table, field, from, to, is_custom_filter) s = [] if from if from.is_a?(Date) @@ -841,7 +851,7 @@ class Query < ActiveRecord::Base if self.class.default_timezone == :utc from = from.utc end - s << ("#{table}.#{field} > '%s'" % [self.class.connection.quoted_date(from)]) + s << ("#{table}.#{field} > '%s'" % [quoted_time(from, is_custom_filter)]) end if to if to.is_a?(Date) @@ -850,14 +860,14 @@ class Query < ActiveRecord::Base if self.class.default_timezone == :utc to = to.utc end - s << ("#{table}.#{field} <= '%s'" % [self.class.connection.quoted_date(to)]) + s << ("#{table}.#{field} <= '%s'" % [quoted_time(to, is_custom_filter)]) end s.join(' AND ') end # Returns a SQL clause for a date or datetime field using relative dates. - def relative_date_clause(table, field, days_from, days_to) - date_clause(table, field, (days_from ? Date.today + days_from : nil), (days_to ? Date.today + days_to : nil)) + def relative_date_clause(table, field, days_from, days_to, is_custom_filter) + date_clause(table, field, (days_from ? Date.today + days_from : nil), (days_to ? Date.today + days_to : nil), is_custom_filter) end # Returns a Date or Time from the given filter value -- 2.39.5