From 446f70f584320aa8da62b72a7116dce298eca07e Mon Sep 17 00:00:00 2001 From: Jean-Philippe Lang Date: Sun, 4 Oct 2015 19:42:37 +0000 Subject: [PATCH] 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 --- app/helpers/queries_helper.rb | 24 ++++++- app/models/issue_query.rb | 38 +++++++++++- app/models/query.rb | 41 +++++++++++- app/views/issues/index.html.erb | 5 ++ app/views/queries/_form.html.erb | 3 + app/views/settings/_issues.html.erb | 5 ++ config/locales/en.yml | 1 + config/locales/fr.yml | 1 + config/settings.yml | 3 + public/stylesheets/application.css | 3 + test/functional/issues_controller_test.rb | 32 ++++++++++ test/unit/query_test.rb | 76 +++++++++++++++++++++++ 12 files changed, 225 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 @@ <%= l(:button_show) %> <%= available_block_columns_tags(@query) %> + + <%= l(:label_total_plural) %> + <%= available_totalable_columns_tags(@query) %> + @@ -60,6 +64,7 @@ <% if @issues.empty? %>

<%= l(:label_no_data) %>

<% else %> +<%= render_query_totals(@query) %> <%= render :partial => 'issues/list', :locals => {:issues => @issues, :query => @query} %>

<%= pagination_links_full @issue_pages, @issue_count %>

<% 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 @@

<%= available_block_columns_tags(@query) %>

+ +

+<%= available_totalable_columns_tags(@query) %>

<% else %>
<%= l(:label_options) %> 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]') %> + +

<%= 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 %>

<%= submit_tag l(:button_save) %> diff --git a/config/locales/en.yml b/config/locales/en.yml index 4a3cac92a..90dcb9212 100644 --- a/config/locales/en.yml +++ b/config/locales/en.yml @@ -646,6 +646,7 @@ en: one: 1 issue other: "%{count} issues" label_total: Total + label_total_plural: Totals label_total_time: Total time label_permissions: Permissions label_current_status: Current status diff --git a/config/locales/fr.yml b/config/locales/fr.yml index 29e3a0727..d3dc55608 100644 --- a/config/locales/fr.yml +++ b/config/locales/fr.yml @@ -666,6 +666,7 @@ fr: one: 1 demande other: "%{count} demandes" label_total: Total + label_total_plural: Totaux label_total_time: Temps total label_permissions: Permissions label_current_status: Statut actuel diff --git a/config/settings.yml b/config/settings.yml index ffb6f97b1..fa5e0be88 100644 --- a/config/settings.yml +++ b/config/settings.yml @@ -180,6 +180,9 @@ issue_list_default_columns: - subject - assigned_to - updated_on +issue_list_default_totals: + serialized: true + default: [] display_subprojects_issues: default: 1 issue_done_ratio: diff --git a/public/stylesheets/application.css b/public/stylesheets/application.css index 4a66774a2..b5df98a90 100644 --- a/public/stylesheets/application.css +++ b/public/stylesheets/application.css @@ -271,6 +271,9 @@ table.query-columns td.buttons { text-align: center; } table.query-columns td.buttons input[type=button] {width:35px;} +.query-totals {text-align:right; margin-top:-2.3em;} +.query-totals>span {margin-left:0.6em;} +.query-totals .value {font-weight:bold;} td.center {text-align:center;} diff --git a/test/functional/issues_controller_test.rb b/test/functional/issues_controller_test.rb index 432693a56..18c82aef1 100644 --- a/test/functional/issues_controller_test.rb +++ b/test/functional/issues_controller_test.rb @@ -945,6 +945,38 @@ class IssuesControllerTest < ActionController::TestCase assert_select 'td.parent a[title=?]', parent.subject end + def test_index_with_estimated_hours_total + Issue.delete_all + Issue.generate!(:estimated_hours => 5.5) + Issue.generate!(:estimated_hours => 1.1) + + get :index, :t => %w(estimated_hours) + assert_response :success + assert_select '.query-totals' + assert_select '.total-for-estimated-hours span.value', :text => '6.6' + assert_select 'input[type=checkbox][name=?][value=estimated_hours][checked=checked]', 't[]' + end + + def test_index_with_int_custom_field_total + field = IssueCustomField.generate!(:field_format => 'int', :is_for_all => true) + CustomValue.create!(:customized => Issue.find(1), :custom_field => field, :value => '2') + CustomValue.create!(:customized => Issue.find(2), :custom_field => field, :value => '7') + + get :index, :t => ["cf_#{field.id}"] + assert_response :success + assert_select '.query-totals' + assert_select ".total-for-cf-#{field.id} span.value", :text => '9' + end + + def test_index_totals_should_default_to_settings + with_settings :issue_list_default_totals => ['estimated_hours'] do + get :index + assert_response :success + assert_select '.total-for-estimated-hours span.value' + assert_select '.query-totals>span', 1 + end + end + def test_index_send_html_if_query_is_invalid get :index, :f => ['start_date'], :op => {:start_date => '='} assert_equal 'text/html', @response.content_type diff --git a/test/unit/query_test.rb b/test/unit/query_test.rb index aa45b5beb..b69bec740 100644 --- a/test/unit/query_test.rb +++ b/test/unit/query_test.rb @@ -1136,6 +1136,82 @@ class QueryTest < ActiveSupport::TestCase assert_equal values.sort, values end + def test_set_totalable_names + q = IssueQuery.new + q.totalable_names = ['estimated_hours', :spent_hours, ''] + assert_equal [:estimated_hours, :spent_hours], q.totalable_columns.map(&:name) + end + + def test_totalable_columns_should_default_to_settings + with_settings :issue_list_default_totals => ['estimated_hours'] do + q = IssueQuery.new + assert_equal [:estimated_hours], q.totalable_columns.map(&:name) + end + end + + def test_available_totalable_columns_should_include_estimated_hours + q = IssueQuery.new + assert_include :estimated_hours, q.available_totalable_columns.map(&:name) + end + + def test_available_totalable_columns_should_include_spent_hours + User.current = User.find(1) + + q = IssueQuery.new + assert_include :spent_hours, q.available_totalable_columns.map(&:name) + end + + def test_available_totalable_columns_should_include_int_custom_field + field = IssueCustomField.generate!(:field_format => 'int', :is_for_all => true) + q = IssueQuery.new + assert_include "cf_#{field.id}".to_sym, q.available_totalable_columns.map(&:name) + end + + def test_available_totalable_columns_should_include_float_custom_field + field = IssueCustomField.generate!(:field_format => 'float', :is_for_all => true) + q = IssueQuery.new + assert_include "cf_#{field.id}".to_sym, q.available_totalable_columns.map(&:name) + end + + def test_total_for_estimated_hours + Issue.delete_all + Issue.generate!(:estimated_hours => 5.5) + Issue.generate!(:estimated_hours => 1.1) + Issue.generate! + + q = IssueQuery.new + assert_equal 6.6, q.total_for(:estimated_hours) + end + + def test_total_for_spent_hours + TimeEntry.delete_all + TimeEntry.generate!(:hours => 5.5) + TimeEntry.generate!(:hours => 1.1) + + q = IssueQuery.new + assert_equal 6.6, q.total_for(:spent_hours) + end + + def test_total_for_int_custom_field + field = IssueCustomField.generate!(:field_format => 'int', :is_for_all => true) + CustomValue.create!(:customized => Issue.find(1), :custom_field => field, :value => '2') + CustomValue.create!(:customized => Issue.find(2), :custom_field => field, :value => '7') + CustomValue.create!(:customized => Issue.find(3), :custom_field => field, :value => '') + + q = IssueQuery.new + assert_equal 9, q.total_for("cf_#{field.id}") + end + + def test_total_for_float_custom_field + field = IssueCustomField.generate!(:field_format => 'float', :is_for_all => true) + CustomValue.create!(:customized => Issue.find(1), :custom_field => field, :value => '2.3') + CustomValue.create!(:customized => Issue.find(2), :custom_field => field, :value => '7') + CustomValue.create!(:customized => Issue.find(3), :custom_field => field, :value => '') + + q = IssueQuery.new + assert_equal 9.3, q.total_for("cf_#{field.id}") + end + def test_invalid_query_should_raise_query_statement_invalid_error q = IssueQuery.new assert_raise Query::StatementInvalid do -- 2.39.5