Issue filtering by spent time (#26826).

Patch by Tobias Umbach and Takenori TAKAKI.


git-svn-id: http://svn.redmine.org/redmine/trunk@18112 e93f8b46-1217-0410-a6f0-8f06a7374b81

2 files changed, 43 insertions, 1 deletions

diff --git a/app/models/issue_query.rb b/app/models/issue_query.rb
index 365b13d38..b9457c93c 100644
--- a/app/models/issue_query.rb
+++ b/app/models/issue_query.rb
@@ -137,6 +137,11 @@ class IssueQuery < Query
     add_available_filter "start_date", :type => :date
     add_available_filter "due_date", :type => :date
     add_available_filter "estimated_hours", :type => :float
+
+    if User.current.allowed_to?(:view_time_entries, project, :global => true)
+      add_available_filter "spent_time", :type => :float, :label => :label_spent_time
+    end
+
     add_available_filter "done_ratio", :type => :integer
 
     if User.current.allowed_to?(:set_issues_private, nil, :global => true) ||
@@ -389,6 +394,24 @@ class IssueQuery < Query
     "#{neg} EXISTS (#{subquery})"
   end
 
+  def sql_for_spent_time_field(field, operator, value)
+    first, second = value.first.to_f, value.second.to_f
+
+    sql_op =
+      case operator
+      when "=", ">=", "<=" ;  "#{operator} #{first}"
+      when "><"            ;  "BETWEEN #{first} AND #{second}"
+      when "*"             ;  "> 0"
+      when "!*"            ;  "= 0"
+      else                 ; return nil
+      end
+
+    "COALESCE((" +
+      "SELECT ROUND(CAST(SUM(hours) AS DECIMAL(30,3)), 2) " +
+      "FROM #{TimeEntry.table_name} " +
+      "WHERE issue_id = #{Issue.table_name}.id), 0) #{sql_op}"
+  end
+
   def sql_for_watcher_id_field(field, operator, value)
     db_table = Watcher.table_name
 
diff --git a/test/unit/query_test.rb b/test/unit/query_test.rb
index 287ea7c45..708393250 100644
--- a/test/unit/query_test.rb
+++ b/test/unit/query_test.rb
@@ -30,7 +30,7 @@ class QueryTest < ActiveSupport::TestCase
            :projects_trackers,
            :custom_fields_trackers,
            :workflows, :journals,
-           :attachments
+           :attachments, :time_entries
 
   INTEGER_KLASS = RUBY_VERSION >= "2.4" ? Integer : Fixnum
 
@@ -2310,4 +2310,23 @@ class QueryTest < ActiveSupport::TestCase
     assert_equal query.column_names, new_query.column_names
     assert_equal query.totalable_names, new_query.totalable_names
   end
+
+  def test_issue_query_filter_by_spent_time
+    query = IssueQuery.new(:name => '_')
+
+    query.filters = {'spent_time' => {:operator => '*', :values => ['']}}
+    assert_equal [3, 1], query.issues.pluck(:id)
+
+    query.filters = {'spent_time' => {:operator => '!*', :values => ['']}}
+    assert_equal [13, 12, 11, 8, 7, 5, 2], query.issues.pluck(:id)
+
+    query.filters = {'spent_time' => {:operator => '>=', :values => ['10']}}
+    assert_equal [1], query.issues.pluck(:id)
+
+    query.filters = {'spent_time' => {:operator => '<=', :values => ['10']}}
+    assert_equal [13, 12, 11, 8, 7, 5, 3, 2], query.issues.pluck(:id)
+
+    query.filters = {'spent_time' => {:operator => '><', :values => ['1', '2']}}
+    assert_equal [3], query.issues.pluck(:id)
+  end
 end