From 4507aa5014184081f2bdce175bac7a9c20fcd8b4 Mon Sep 17 00:00:00 2001 From: Jean-Philippe Lang Date: Sun, 10 Jul 2011 17:29:29 +0000 Subject: [PATCH] Adds date based filters (#4729) and date range filter (#6954). git-svn-id: svn+ssh://rubyforge.org/var/svn/redmine/trunk@6226 e93f8b46-1217-0410-a6f0-8f06a7374b81 --- app/models/query.rb | 87 ++++++++++++++--------- app/views/queries/_filters.rhtml | 116 ++++++++++++++++++------------- test/unit/query_test.rb | 28 ++++++++ 3 files changed, 153 insertions(+), 78 deletions(-) diff --git a/app/models/query.rb b/app/models/query.rb index fa8a449c5..7746af88e 100644 --- a/app/models/query.rb +++ b/app/models/query.rb @@ -119,8 +119,8 @@ class Query < ActiveRecord::Base :list_status => [ "o", "=", "!", "c", "*" ], :list_optional => [ "=", "!", "!*", "*" ], :list_subprojects => [ "*", "!*", "=" ], - :date => [ "t+", "t+", "t", "w", ">t-", " [ ">t-", " [ "=", ">=", "<=", "><", "t+", "t+", "t", "w", ">t-", " [ "=", ">=", "<=", "><", ">t-", " [ "=", "~", "!", "!~" ], :text => [ "~", "!~" ], :integer => [ "=", ">=", "<=", "><", "!*", "*" ] } @@ -268,7 +268,7 @@ class Query < ActiveRecord::Base def add_filter(field, operator, values) # values must be an array - return unless values and values.is_a? Array # and !values.first.empty? + return unless values.nil? || values.is_a?(Array) # check if field is defined as an available filter if available_filters.has_key? field filter_options = available_filters[field] @@ -277,7 +277,7 @@ class Query < ActiveRecord::Base # allowed_values = values & ([""] + (filter_options[:values] || []).collect {|val| val[1]}) # filters[field] = {:operator => operator, :values => allowed_values } if (allowed_values.first and !allowed_values.first.empty?) or ["o", "c", "!*", "*", "t"].include? operator #end - filters[field] = {:operator => operator, :values => values } + filters[field] = {:operator => operator, :values => (values || ['']) } end end @@ -289,9 +289,9 @@ class Query < ActiveRecord::Base # Add multiple filters using +add_filter+ def add_filters(fields, operators, values) - if fields.is_a?(Array) && operators.is_a?(Hash) && values.is_a?(Hash) + if fields.is_a?(Array) && operators.is_a?(Hash) && (values.nil? || values.is_a?(Hash)) fields.each do |field| - add_filter(field, operators[field], values[field]) + add_filter(field, operators[field], values && values[field]) end end end @@ -299,6 +299,10 @@ class Query < ActiveRecord::Base def has_filter?(field) filters and filters[field] end + + def type_for(field) + available_filters[field][:type] if available_filters.has_key?(field) + end def operator_for(field) has_filter?(field) ? filters[field][:operator] : nil @@ -601,11 +605,15 @@ class Query < ActiveRecord::Base sql = '' case operator when "=" - if value.any? - sql = "#{db_table}.#{db_field} IN (" + value.collect{|val| "'#{connection.quote_string(val)}'"}.join(",") + ")" + if [:date, :date_past].include?(type_for(field)) + sql = date_clause(db_table, db_field, (Date.parse(value.first) rescue nil), (Date.parse(value.first) rescue nil)) else - # IN an empty set - sql = "1=0" + if value.any? + sql = "#{db_table}.#{db_field} IN (" + value.collect{|val| "'#{connection.quote_string(val)}'"}.join(",") + ")" + else + # IN an empty set + sql = "1=0" + end end when "!" if value.any? @@ -621,46 +629,58 @@ class Query < ActiveRecord::Base sql = "#{db_table}.#{db_field} IS NOT NULL" sql << " AND #{db_table}.#{db_field} <> ''" if is_custom_filter when ">=" - if is_custom_filter - sql = "CAST(#{db_table}.#{db_field} AS decimal(60,3)) >= #{value.first.to_i}" + if [:date, :date_past].include?(type_for(field)) + sql = date_clause(db_table, db_field, (Date.parse(value.first) rescue nil), nil) else - sql = "#{db_table}.#{db_field} >= #{value.first.to_i}" + if is_custom_filter + sql = "CAST(#{db_table}.#{db_field} AS decimal(60,3)) >= #{value.first.to_i}" + else + sql = "#{db_table}.#{db_field} >= #{value.first.to_i}" + end end when "<=" - if is_custom_filter - sql = "CAST(#{db_table}.#{db_field} AS decimal(60,3)) <= #{value.first.to_i}" + if [:date, :date_past].include?(type_for(field)) + sql = date_clause(db_table, db_field, nil, (Date.parse(value.first) rescue nil)) else - sql = "#{db_table}.#{db_field} <= #{value.first.to_i}" + if is_custom_filter + sql = "CAST(#{db_table}.#{db_field} AS decimal(60,3)) <= #{value.first.to_i}" + else + sql = "#{db_table}.#{db_field} <= #{value.first.to_i}" + end end when "><" - if is_custom_filter - sql = "CAST(#{db_table}.#{db_field} AS decimal(60,3)) BETWEEN #{value[0].to_i} AND #{value[1].to_i}" + if [:date, :date_past].include?(type_for(field)) + sql = date_clause(db_table, db_field, (Date.parse(value[0]) rescue nil), (Date.parse(value[1]) rescue nil)) else - sql = "#{db_table}.#{db_field} BETWEEN #{value[0].to_i} AND #{value[1].to_i}" + if is_custom_filter + sql = "CAST(#{db_table}.#{db_field} AS decimal(60,3)) BETWEEN #{value[0].to_i} AND #{value[1].to_i}" + else + sql = "#{db_table}.#{db_field} BETWEEN #{value[0].to_i} AND #{value[1].to_i}" + end end when "o" sql = "#{IssueStatus.table_name}.is_closed=#{connection.quoted_false}" if field == "status_id" when "c" sql = "#{IssueStatus.table_name}.is_closed=#{connection.quoted_true}" if field == "status_id" when ">t-" - sql = date_range_clause(db_table, db_field, - value.first.to_i, 0) + sql = relative_date_clause(db_table, db_field, - value.first.to_i, 0) when "t+" - sql = date_range_clause(db_table, db_field, value.first.to_i, nil) + sql = relative_date_clause(db_table, db_field, value.first.to_i, nil) when "= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week) - sql = date_range_clause(db_table, db_field, - days_ago, - days_ago + 6) + sql = relative_date_clause(db_table, db_field, - days_ago, - days_ago + 6) when "~" sql = "LOWER(#{db_table}.#{db_field}) LIKE '%#{connection.quote_string(value.first.to_s.downcase)}%'" when "!~" @@ -696,16 +716,21 @@ class Query < ActiveRecord::Base @available_filters["cf_#{field.id}"] = options.merge({ :name => field.name }) end end - + # Returns a SQL clause for a date or datetime field. - def date_range_clause(table, field, from, to) + def date_clause(table, field, from, to) s = [] if from - s << ("#{table}.#{field} > '%s'" % [connection.quoted_date((Date.yesterday + from).to_time.end_of_day)]) + s << ("#{table}.#{field} > '%s'" % [connection.quoted_date((from - 1).to_time.end_of_day)]) end if to - s << ("#{table}.#{field} <= '%s'" % [connection.quoted_date((Date.today + to).to_time.end_of_day)]) + s << ("#{table}.#{field} <= '%s'" % [connection.quoted_date(to.to_time.end_of_day)]) 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)) + end end diff --git a/app/views/queries/_filters.rhtml b/app/views/queries/_filters.rhtml index e9c7502e1..8942c8b60 100644 --- a/app/views/queries/_filters.rhtml +++ b/app/views/queries/_filters.rhtml @@ -1,37 +1,53 @@ diff --git a/test/unit/query_test.rb b/test/unit/query_test.rb index 633edebb1..31dfa488e 100644 --- a/test/unit/query_test.rb +++ b/test/unit/query_test.rb @@ -146,6 +146,34 @@ class QueryTest < ActiveSupport::TestCase find_issues_with_query(query) end + def test_operator_date_equals + query = Query.new(:name => '_') + query.add_filter('due_date', '=', ['2011-07-10']) + assert_match /issues\.due_date > '2011-07-09 23:59:59(\.9+)?' AND issues\.due_date <= '2011-07-10 23:59:59(\.9+)?/, query.statement + find_issues_with_query(query) + end + + def test_operator_date_lesser_than + query = Query.new(:name => '_') + query.add_filter('due_date', '<=', ['2011-07-10']) + assert_match /issues\.due_date <= '2011-07-10 23:59:59(\.9+)?/, query.statement + find_issues_with_query(query) + end + + def test_operator_date_greater_than + query = Query.new(:name => '_') + query.add_filter('due_date', '>=', ['2011-07-10']) + assert_match /issues\.due_date > '2011-07-09 23:59:59(\.9+)?'/, query.statement + find_issues_with_query(query) + end + + def test_operator_date_between + query = Query.new(:name => '_') + query.add_filter('due_date', '><', ['2011-06-23', '2011-07-10']) + assert_match /issues\.due_date > '2011-06-22 23:59:59(\.9+)?' AND issues\.due_date <= '2011-07-10 23:59:59(\.9+)?/, query.statement + find_issues_with_query(query) + end + def test_operator_in_more_than Issue.find(7).update_attribute(:due_date, (Date.today + 15)) query = Query.new(:project => Project.find(1), :name => '_') -- 2.39.5