Adds options to display totals on the issue list (#1561).

Works for estimated hours, spent hours and any numeric custom field.

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

6 files changed, 109 insertions, 7 deletions

diff --git a/app/helpers/queries_helper.rb b/app/helpers/queries_helper.rb
index ec2b8383f..3c6049acd 100644
--- a/app/helpers/queries_helper.rb
+++ b/app/helpers/queries_helper.rb
@@ -84,6 +84,14 @@ module QueriesHelper
     tags
   end
 
+  def available_totalable_columns_tags(query)
+    tags = ''.html_safe
+    query.available_totalable_columns.each do |column|
+      tags << content_tag('label', check_box_tag('t[]', column.name.to_s, query.totalable_columns.include?(column), :id => nil) + " #{column.caption}", :class => 'inline')
+    end
+    tags
+  end
+
   def query_available_inline_columns_options(query)
     (query.available_inline_columns - query.columns).reject(&:frozen?).collect {|column| [column.caption, column.name]}
   end
@@ -97,6 +105,16 @@ module QueriesHelper
     render :partial => 'queries/columns', :locals => {:query => query, :tag_name => tag_name}
   end
 
+  def render_query_totals(query)
+    return unless query.totalable_columns.present?
+    totals = query.totalable_columns.map do |column|
+      label = content_tag('span', "#{column.caption}:")
+      value = content_tag('span', " #{query.total_for(column)}", :class => 'value')
+      content_tag('span', label + " " + value, :class => "total-for-#{column.name.to_s.dasherize}")
+    end
+    content_tag('p', totals.join(" ").html_safe, :class => "query-totals")
+  end
+
   def column_header(column)
     column.sortable ? sort_header_tag(column.name.to_s, :caption => column.caption,
                                                         :default_order => column.default_order) :
@@ -194,12 +212,12 @@ module QueriesHelper
       @query = IssueQuery.new(:name => "_")
       @query.project = @project
       @query.build_from_params(params)
-      session[:query] = {:project_id => @query.project_id, :filters => @query.filters, :group_by => @query.group_by, :column_names => @query.column_names}
+      session[:query] = {:project_id => @query.project_id, :filters => @query.filters, :group_by => @query.group_by, :column_names => @query.column_names, :totalable_names => @query.totalable_names}
     else
       # retrieve from session
       @query = nil
       @query = IssueQuery.find_by_id(session[:query][:id]) if session[:query][:id]
-      @query ||= IssueQuery.new(:name => "_", :filters => session[:query][:filters], :group_by => session[:query][:group_by], :column_names => session[:query][:column_names])
+      @query ||= IssueQuery.new(:name => "_", :filters => session[:query][:filters], :group_by => session[:query][:group_by], :column_names => session[:query][:column_names], :totalable_names => session[:query][:totalable_names])
       @query.project = @project
     end
   end
@@ -210,7 +228,7 @@ module QueriesHelper
         @query = IssueQuery.find_by_id(session[:query][:id])
         return unless @query
       else
-        @query = IssueQuery.new(:name => "_", :filters => session[:query][:filters], :group_by => session[:query][:group_by], :column_names => session[:query][:column_names])
+        @query = IssueQuery.new(:name => "_", :filters => session[:query][:filters], :group_by => session[:query][:group_by], :column_names => session[:query][:column_names], :totalable_names => session[:query][:totalable_names])
       end
       if session[:query].has_key?(:project_id)
         @query.project_id = session[:query][:project_id]
diff --git a/app/models/issue_query.rb b/app/models/issue_query.rb
index 08e22e966..51a4fda23 100644
--- a/app/models/issue_query.rb
+++ b/app/models/issue_query.rb
@@ -34,7 +34,7 @@ class IssueQuery < Query
     QueryColumn.new(:fixed_version, :sortable => lambda {Version.fields_for_order_statement}, :groupable => true),
     QueryColumn.new(:start_date, :sortable => "#{Issue.table_name}.start_date"),
     QueryColumn.new(:due_date, :sortable => "#{Issue.table_name}.due_date"),
-    QueryColumn.new(:estimated_hours, :sortable => "#{Issue.table_name}.estimated_hours"),
+    QueryColumn.new(:estimated_hours, :sortable => "#{Issue.table_name}.estimated_hours", :totalable => true),
     QueryColumn.new(:total_estimated_hours,
       :sortable => "COALESCE((SELECT SUM(estimated_hours) FROM #{Issue.table_name} subtasks" +
         " WHERE subtasks.root_id = #{Issue.table_name}.root_id AND subtasks.lft >= #{Issue.table_name}.lft AND subtasks.rgt <= #{Issue.table_name}.rgt), 0)",
@@ -268,7 +268,8 @@ class IssueQuery < Query
       @available_columns.insert index, QueryColumn.new(:spent_hours,
         :sortable => "COALESCE((SELECT SUM(hours) FROM #{TimeEntry.table_name} WHERE #{TimeEntry.table_name}.issue_id = #{Issue.table_name}.id), 0)",
         :default_order => 'desc',
-        :caption => :label_spent_time
+        :caption => :label_spent_time,
+        :totalable => true
       )
       @available_columns.insert index+1, QueryColumn.new(:total_spent_hours,
         :sortable => "COALESCE((SELECT SUM(hours) FROM #{TimeEntry.table_name} JOIN #{Issue.table_name} subtasks ON subtasks.id = #{TimeEntry.table_name}.issue_id" +
@@ -299,13 +300,44 @@ class IssueQuery < Query
     end
   end
 
+  def base_scope
+    Issue.visible.joins(:status, :project).where(statement)
+  end
+  private :base_scope
+
   # Returns the issue count
   def issue_count
-    Issue.visible.joins(:status, :project).where(statement).count
+    base_scope.count
   rescue ::ActiveRecord::StatementInvalid => e
     raise StatementInvalid.new(e.message)
   end
 
+  # Returns sum of all the issue's estimated_hours
+  def total_for_estimated_hours
+    base_scope.sum(:estimated_hours)
+  end
+
+  # Returns sum of all the issue's time entries hours
+  def total_for_spent_hours
+    base_scope.joins(:time_entries).sum("#{TimeEntry.table_name}.hours")
+  end
+
+  def total_for_custom_field(custom_field)
+    base_scope.joins(:custom_values).
+      where(:custom_values => {:custom_field_id => custom_field.id}).
+      where.not(:custom_values => {:value => ''}).
+      sum("CAST(#{CustomValue.table_name}.value AS decimal(30,3))")
+  end
+  private :total_for_custom_field
+
+  def total_for_float_custom_field(custom_field)
+    total_for_custom_field(custom_field).to_f
+  end
+
+  def total_for_int_custom_field(custom_field)
+    total_for_custom_field(custom_field).to_i
+  end
+
   # Returns the issue count by group or nil if query is not grouped
   def issue_count_by_group
     r = nil
diff --git a/app/models/query.rb b/app/models/query.rb
index 083b775a6..ac7b7cd1c 100644
--- a/app/models/query.rb
+++ b/app/models/query.rb
@@ -16,7 +16,7 @@
 # Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
 
 class QueryColumn
-  attr_accessor :name, :sortable, :groupable, :default_order
+  attr_accessor :name, :sortable, :groupable, :totalable, :default_order
   include Redmine::I18n
 
   def initialize(name, options={})
@@ -26,6 +26,7 @@ class QueryColumn
     if groupable == true
       self.groupable = name.to_s
     end
+    self.totalable = options[:totalable] || false
     self.default_order = options[:default_order]
     @inline = options.key?(:inline) ? options[:inline] : true
     @caption_key = options[:caption] || "field_#{name}".to_sym
@@ -79,6 +80,7 @@ class QueryCustomFieldColumn < QueryColumn
     self.name = "cf_#{custom_field.id}".to_sym
     self.sortable = custom_field.order_statement || false
     self.groupable = custom_field.group_statement || false
+    self.totalable = ['int', 'float'].include?(custom_field.field_format)
     @inline = true
     @cf = custom_field
   end
@@ -246,6 +248,7 @@ class Query < ActiveRecord::Base
     end
     self.group_by = params[:group_by] || (params[:query] && params[:query][:group_by])
     self.column_names = params[:c] || (params[:query] && params[:query][:column_names])
+    self.totalable_names = params[:t] || (params[:query] && params[:query][:totalable_names])
     self
   end
 
@@ -454,6 +457,10 @@ class Query < ActiveRecord::Base
     available_columns.reject(&:inline?)
   end
 
+  def available_totalable_columns
+    available_columns.select(&:totalable)
+  end
+
   def default_columns_names
     []
   end
@@ -482,6 +489,22 @@ class Query < ActiveRecord::Base
     column_names.nil? || column_names.empty?
   end
 
+  def totalable_columns
+    names = totalable_names
+    available_totalable_columns.select {|column| names.include?(column.name)}
+  end
+
+  def totalable_names=(names)
+    if names
+      names = names.select(&:present?).map {|n| n.is_a?(Symbol) ? n : n.to_sym}
+    end
+    options[:totalable_names] = names
+  end
+
+  def totalable_names
+    options[:totalable_names] || Setting.issue_list_default_totals.map(&:to_sym) || []
+  end
+
   def sort_criteria=(arg)
     c = []
     if arg.is_a?(Hash)
@@ -607,6 +630,22 @@ class Query < ActiveRecord::Base
     filters_clauses.any? ? filters_clauses.join(' AND ') : nil
   end
 
+  # Returns the sum of values for the given column
+  def total_for(column)
+    unless column.is_a?(QueryColumn)
+      column = column.to_sym
+      column = available_totalable_columns.detect {|c| c.name == column}
+    end
+    if column.is_a?(QueryCustomFieldColumn)
+      custom_field = column.custom_field
+      send "total_for_#{custom_field.field_format}_custom_field", custom_field
+    else
+      send "total_for_#{column.name}"
+    end
+  rescue ::ActiveRecord::StatementInvalid => e
+    raise StatementInvalid.new(e.message)
+  end
+
   private
 
   def sql_for_custom_field(field, operator, value, custom_field_id)
diff --git a/app/views/issues/index.html.erb b/app/views/issues/index.html.erb
index 55bfc5647..f08f76d65 100644
--- a/app/views/issues/index.html.erb
+++ b/app/views/issues/index.html.erb
@@ -39,6 +39,10 @@
             <td><%= l(:button_show) %></td>
             <td><%= available_block_columns_tags(@query) %></td>
           </tr>
+          <tr>
+            <td><%= l(:label_total_plural) %></td>
+            <td><%= available_totalable_columns_tags(@query) %></td>
+          </tr>
         </table>
       </div>
     </fieldset>
@@ -60,6 +64,7 @@
 <% if @issues.empty? %>
 <p class="nodata"><%= l(:label_no_data) %></p>
 <% else %>
+<%= render_query_totals(@query) %>
 <%= render :partial => 'issues/list', :locals => {:issues => @issues, :query => @query} %>
 <p class="pagination"><%= pagination_links_full @issue_pages, @issue_count %></p>
 <% end %>
diff --git a/app/views/queries/_form.html.erb b/app/views/queries/_form.html.erb
index 753eaedab..d995777c5 100644
--- a/app/views/queries/_form.html.erb
+++ b/app/views/queries/_form.html.erb
@@ -33,6 +33,9 @@
 
 <p><label><%= l(:button_show) %></label>
 <%= available_block_columns_tags(@query) %></p>
+
+<p><label><%= l(:label_total_plural) %></label>
+<%= available_totalable_columns_tags(@query) %></p>
 </fieldset>
 <% else %>
 <fieldset><legend><%= l(:label_options) %></legend>
diff --git a/app/views/settings/_issues.html.erb b/app/views/settings/_issues.html.erb
index b3533bbd1..f29a6d4ec 100644
--- a/app/views/settings/_issues.html.erb
+++ b/app/views/settings/_issues.html.erb
@@ -38,6 +38,11 @@
   <%= render_query_columns_selection(
         IssueQuery.new(:column_names => Setting.issue_list_default_columns),
         :name => 'settings[issue_list_default_columns]') %>
+
+  <p><%= setting_multiselect :issue_list_default_totals,
+           IssueQuery.new(:totalable_names => Setting.issue_list_default_totals).available_totalable_columns.map {|c| [c.caption, c.name.to_s]},
+           :inline => true,
+           :label => :label_total_plural %></p>
 </fieldset>
 
 <%= submit_tag l(:button_save) %>