Refactor: Moved the raw SQL finders from ReportsController to Issue.

git-svn-id: svn+ssh://rubyforge.org/var/svn/redmine/trunk@3362 e93f8b46-1217-0410-a6f0-8f06a7374b81

2 files changed, 107 insertions, 83 deletions

diff --git a/app/controllers/reports_controller.rb b/app/controllers/reports_controller.rb
index 804bdd32f..e8acdc661 100644
--- a/app/controllers/reports_controller.rb
+++ b/app/controllers/reports_controller.rb
@@ -94,107 +94,31 @@ private
   end
 
   def issues_by_tracker
-    @issues_by_tracker ||= 
-        ActiveRecord::Base.connection.select_all("select    s.id as status_id, 
-                                                  s.is_closed as closed, 
-                                                  t.id as tracker_id,
-                                                  count(i.id) as total 
-                                                from 
-                                                  #{Issue.table_name} i, #{IssueStatus.table_name} s, #{Tracker.table_name} t
-                                                where 
-                                                  i.status_id=s.id 
-                                                  and i.tracker_id=t.id
-                                                  and i.project_id=#{@project.id}
-                                                group by s.id, s.is_closed, t.id")	
+    @issues_by_tracker ||= Issue.by_tracker(@project)
   end
 
   def issues_by_version
-    @issues_by_version ||= 
-        ActiveRecord::Base.connection.select_all("select    s.id as status_id, 
-                                                  s.is_closed as closed, 
-                                                  v.id as fixed_version_id,
-                                                  count(i.id) as total 
-                                                from 
-                                                  #{Issue.table_name} i, #{IssueStatus.table_name} s, #{Version.table_name} v
-                                                where 
-                                                  i.status_id=s.id 
-                                                  and i.fixed_version_id=v.id
-                                                  and i.project_id=#{@project.id}
-                                                group by s.id, s.is_closed, v.id")	
+    @issues_by_version ||= Issue.by_version(@project)
   end
   	
   def issues_by_priority    
-    @issues_by_priority ||= 
-      ActiveRecord::Base.connection.select_all("select    s.id as status_id, 
-                                                  s.is_closed as closed, 
-                                                  p.id as priority_id,
-                                                  count(i.id) as total 
-                                                from 
-                                                  #{Issue.table_name} i, #{IssueStatus.table_name} s, #{IssuePriority.table_name} p
-                                                where 
-                                                  i.status_id=s.id 
-                                                  and i.priority_id=p.id
-                                                  and i.project_id=#{@project.id}
-                                                group by s.id, s.is_closed, p.id")	
+    @issues_by_priority ||= Issue.by_priority(@project)
   end
 	
   def issues_by_category   
-    @issues_by_category ||= 
-      ActiveRecord::Base.connection.select_all("select    s.id as status_id, 
-                                                  s.is_closed as closed, 
-                                                  c.id as category_id,
-                                                  count(i.id) as total 
-                                                from 
-                                                  #{Issue.table_name} i, #{IssueStatus.table_name} s, #{IssueCategory.table_name} c
-                                                where 
-                                                  i.status_id=s.id 
-                                                  and i.category_id=c.id
-                                                  and i.project_id=#{@project.id}
-                                                group by s.id, s.is_closed, c.id")	
+    @issues_by_category ||= Issue.by_category(@project)
   end
   
   def issues_by_assigned_to
-    @issues_by_assigned_to ||= 
-      ActiveRecord::Base.connection.select_all("select    s.id as status_id, 
-                                                  s.is_closed as closed, 
-                                                  a.id as assigned_to_id,
-                                                  count(i.id) as total 
-                                                from 
-                                                  #{Issue.table_name} i, #{IssueStatus.table_name} s, #{User.table_name} a
-                                                where 
-                                                  i.status_id=s.id 
-                                                  and i.assigned_to_id=a.id
-                                                  and i.project_id=#{@project.id}
-                                                group by s.id, s.is_closed, a.id")
+    @issues_by_assigned_to ||= Issue.by_assigned_to(@project)
   end
   
   def issues_by_author
-    @issues_by_author ||= 
-      ActiveRecord::Base.connection.select_all("select    s.id as status_id, 
-                                                  s.is_closed as closed, 
-                                                  a.id as author_id,
-                                                  count(i.id) as total 
-                                                from 
-                                                  #{Issue.table_name} i, #{IssueStatus.table_name} s, #{User.table_name} a
-                                                where 
-                                                  i.status_id=s.id 
-                                                  and i.author_id=a.id
-                                                  and i.project_id=#{@project.id}
-                                                group by s.id, s.is_closed, a.id")	
+    @issues_by_author ||= Issue.by_author(@project)
   end
   
   def issues_by_subproject
-    @issues_by_subproject ||= 
-      ActiveRecord::Base.connection.select_all("select    s.id as status_id, 
-                                                  s.is_closed as closed, 
-                                                  i.project_id as project_id,
-                                                  count(i.id) as total 
-                                                from 
-                                                  #{Issue.table_name} i, #{IssueStatus.table_name} s
-                                                where 
-                                                  i.status_id=s.id 
-                                                  and i.project_id IN (#{@project.descendants.active.collect{|p| p.id}.join(',')})
-                                                group by s.id, s.is_closed, i.project_id") if @project.descendants.active.any?
+    @issues_by_subproject ||= Issue.by_subproject(@project)
     @issues_by_subproject ||= []
   end
 end
diff --git a/app/models/issue.rb b/app/models/issue.rb
index 2780fd4c5..af9086157 100644
--- a/app/models/issue.rb
+++ b/app/models/issue.rb
@@ -414,6 +414,106 @@ class Issue < ActiveRecord::Base
     Issue.update_versions(["#{Version.table_name}.project_id IN (?) OR #{Issue.table_name}.project_id IN (?)", moved_project_ids, moved_project_ids])
   end
 
+  # Extracted from the ReportsController.
+  # TODO: refactor into a common factory or named scopes
+  def self.by_tracker(project)
+    ActiveRecord::Base.connection.select_all("select    s.id as status_id, 
+                                                s.is_closed as closed, 
+                                                t.id as tracker_id,
+                                                count(i.id) as total 
+                                              from 
+                                                  #{Issue.table_name} i, #{IssueStatus.table_name} s, #{Tracker.table_name} t
+                                              where 
+                                                i.status_id=s.id 
+                                                and i.tracker_id=t.id
+                                                and i.project_id=#{project.id}
+                                              group by s.id, s.is_closed, t.id")
+  end
+
+  def self.by_version(project)
+    ActiveRecord::Base.connection.select_all("select    s.id as status_id, 
+                                                s.is_closed as closed, 
+                                                v.id as fixed_version_id,
+                                                count(i.id) as total 
+                                              from 
+                                                #{Issue.table_name} i, #{IssueStatus.table_name} s, #{Version.table_name} v
+                                              where 
+                                                i.status_id=s.id 
+                                                and i.fixed_version_id=v.id
+                                                and i.project_id=#{project.id}
+                                              group by s.id, s.is_closed, v.id")
+  end
+
+  def self.by_priority(project)
+    ActiveRecord::Base.connection.select_all("select    s.id as status_id, 
+                                                s.is_closed as closed, 
+                                                p.id as priority_id,
+                                                count(i.id) as total 
+                                              from 
+                                                #{Issue.table_name} i, #{IssueStatus.table_name} s, #{IssuePriority.table_name} p
+                                              where 
+                                                i.status_id=s.id 
+                                                and i.priority_id=p.id
+                                                and i.project_id=#{project.id}
+                                              group by s.id, s.is_closed, p.id")
+  end
+
+  def self.by_category(project)
+    ActiveRecord::Base.connection.select_all("select    s.id as status_id, 
+                                                s.is_closed as closed, 
+                                                c.id as category_id,
+                                                count(i.id) as total 
+                                              from 
+                                                #{Issue.table_name} i, #{IssueStatus.table_name} s, #{IssueCategory.table_name} c
+                                              where 
+                                                i.status_id=s.id 
+                                                and i.category_id=c.id
+                                                and i.project_id=#{project.id}
+                                              group by s.id, s.is_closed, c.id")
+  end
+
+  def self.by_assigned_to(project)
+    ActiveRecord::Base.connection.select_all("select    s.id as status_id, 
+                                                s.is_closed as closed, 
+                                                a.id as assigned_to_id,
+                                                count(i.id) as total 
+                                              from 
+                                                #{Issue.table_name} i, #{IssueStatus.table_name} s, #{User.table_name} a
+                                              where 
+                                                i.status_id=s.id 
+                                                and i.assigned_to_id=a.id
+                                                and i.project_id=#{project.id}
+                                              group by s.id, s.is_closed, a.id")
+  end
+
+  def self.by_author(project)
+    ActiveRecord::Base.connection.select_all("select    s.id as status_id, 
+                                                s.is_closed as closed, 
+                                                a.id as author_id,
+                                                count(i.id) as total 
+                                              from 
+                                                #{Issue.table_name} i, #{IssueStatus.table_name} s, #{User.table_name} a
+                                              where 
+                                                i.status_id=s.id 
+                                                and i.author_id=a.id
+                                                and i.project_id=#{project.id}
+                                              group by s.id, s.is_closed, a.id")	
+  end
+
+  def self.by_subproject(project)
+    ActiveRecord::Base.connection.select_all("select    s.id as status_id, 
+                                                s.is_closed as closed, 
+                                                i.project_id as project_id,
+                                                count(i.id) as total 
+                                              from 
+                                                #{Issue.table_name} i, #{IssueStatus.table_name} s
+                                              where 
+                                                i.status_id=s.id 
+                                                and i.project_id IN (#{project.descendants.active.collect{|p| p.id}.join(',')})
+                                              group by s.id, s.is_closed, i.project_id") if project.descendants.active.any?
+  end
+  # End ReportsController extraction
+  
   private
   
   # Update issues so their versions are not pointing to a