From 9e1192a54de63492f9400a56d0e6a131f95a00d8 Mon Sep 17 00:00:00 2001 From: Jean-Philippe Lang Date: Sun, 23 Nov 2008 16:40:35 +0000 Subject: [PATCH] Fixed date filters accuracy with SQLite (#2221). git-svn-id: svn+ssh://rubyforge.org/var/svn/redmine/trunk@2054 e93f8b46-1217-0410-a6f0-8f06a7374b81 --- app/models/query.rb | 26 +++++++++++++----- test/fixtures/issues.yml | 17 ++++++++++++ test/unit/query_test.rb | 57 +++++++++++++++++++++++++++++++++------- 3 files changed, 84 insertions(+), 16 deletions(-) diff --git a/app/models/query.rb b/app/models/query.rb index ac8777721..5ad0dd804 100644 --- a/app/models/query.rb +++ b/app/models/query.rb @@ -329,19 +329,19 @@ class Query < ActiveRecord::Base when "c" sql = sql + "#{IssueStatus.table_name}.is_closed=#{connection.quoted_true}" if field == "status_id" when ">t-" - sql = sql + "#{db_table}.#{db_field} BETWEEN '%s' AND '%s'" % [connection.quoted_date((Date.today - v.first.to_i).to_time), connection.quoted_date((Date.today + 1).to_time)] + sql = sql + date_range_clause(db_table, db_field, - v.first.to_i, 0) when "t+" - sql = sql + "#{db_table}.#{db_field} >= '%s'" % connection.quoted_date((Date.today + v.first.to_i).to_time) + sql = sql + date_range_clause(db_table, db_field, v.first.to_i, nil) when " field.name }) end end + + # Returns a SQL clause for a date or datetime field. + def date_range_clause(table, field, from, to) + s = [] + if from + s << ("#{table}.#{field} > '%s'" % [connection.quoted_date((Date.yesterday + from).to_time.end_of_day)]) + end + if to + s << ("#{table}.#{field} <= '%s'" % [connection.quoted_date((Date.today + to).to_time.end_of_day)]) + end + s.join(' AND ') + end end diff --git a/test/fixtures/issues.yml b/test/fixtures/issues.yml index 9d3287c6f..4e1416378 100644 --- a/test/fixtures/issues.yml +++ b/test/fixtures/issues.yml @@ -91,4 +91,21 @@ issues_006: status_id: 1 start_date: <%= Date.today.to_s(:db) %> due_date: <%= 1.days.from_now.to_date.to_s(:db) %> +issues_007: + created_on: <%= 10.days.ago.to_date.to_s(:db) %> + project_id: 1 + updated_on: <%= 10.days.ago.to_date.to_s(:db) %> + priority_id: 3 + subject: Issue due today + id: 7 + fixed_version_id: + category_id: + description: This is an issue that is due today + tracker_id: 1 + assigned_to_id: + author_id: 2 + status_id: 1 + start_date: <%= 10.days.ago.to_s(:db) %> + due_date: <%= Date.today.to_s(:db) %> + lock_version: 0 \ No newline at end of file diff --git a/test/unit/query_test.rb b/test/unit/query_test.rb index c243dfbad..ac3e3cadc 100644 --- a/test/unit/query_test.rb +++ b/test/unit/query_test.rb @@ -18,7 +18,7 @@ require File.dirname(__FILE__) + '/../test_helper' class QueryTest < Test::Unit::TestCase - fixtures :projects, :users, :members, :roles, :trackers, :issue_statuses, :issue_categories, :enumerations, :issues, :custom_fields, :custom_values, :queries + fixtures :projects, :enabled_modules, :users, :members, :roles, :trackers, :issue_statuses, :issue_categories, :enumerations, :issues, :custom_fields, :custom_values, :versions, :queries def test_custom_fields_for_all_projects_should_be_available_in_global_queries query = Query.new(:project => nil, :name => '_') @@ -75,37 +75,76 @@ class QueryTest < Test::Unit::TestCase 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 => '_') query.add_filter('due_date', '>t+', ['15']) - assert query.statement.include?("#{Issue.table_name}.due_date >=") - find_issues_with_query(query) + issues = find_issues_with_query(query) + assert !issues.empty? + issues.each {|issue| assert(issue.due_date >= (Date.today + 15))} end def test_operator_in_less_than query = Query.new(:project => Project.find(1), :name => '_') query.add_filter('due_date', '= Date.today && issue.due_date <= (Date.today + 15))} + end + + def test_operator_less_than_ago + Issue.find(7).update_attribute(:due_date, (Date.today - 3)) + query = Query.new(:project => Project.find(1), :name => '_') + query.add_filter('due_date', '>t-', ['3']) + issues = find_issues_with_query(query) + assert !issues.empty? + issues.each {|issue| assert(issue.due_date >= (Date.today - 3) && issue.due_date <= Date.today)} + end + + def test_operator_more_than_ago + Issue.find(7).update_attribute(:due_date, (Date.today - 10)) + query = Query.new(:project => Project.find(1), :name => '_') + query.add_filter('due_date', ' Project.find(1), :name => '_') query.add_filter('due_date', 'w', ['']) - assert query.statement.include?("#{Issue.table_name}.due_date BETWEEN") find_issues_with_query(query) end def test_operator_this_week_on_datetime query = Query.new(:project => Project.find(1), :name => '_') query.add_filter('created_on', 'w', ['']) - assert query.statement.include?("#{Issue.table_name}.created_on BETWEEN") find_issues_with_query(query) end -- 2.39.5