From 498a429a41bc47fc7e3980926901cc60863afe9a Mon Sep 17 00:00:00 2001 From: Jean-Philippe Lang Date: Fri, 9 Oct 2015 09:02:11 +0000 Subject: [PATCH] Display totals for each group on grouped queries (#1561). git-svn-id: http://svn.redmine.org/redmine/trunk@14665 e93f8b46-1217-0410-a6f0-8f06a7374b81 --- app/helpers/issues_helper.rb | 7 +- app/helpers/queries_helper.rb | 10 ++- app/models/issue_query.rb | 57 +++------------ app/models/query.rb | 84 ++++++++++++++++++++++- app/views/issues/_list.html.erb | 4 +- public/stylesheets/application.css | 9 ++- test/functional/issues_controller_test.rb | 24 ++++++- test/unit/query_test.rb | 41 +++++++++++ 8 files changed, 177 insertions(+), 59 deletions(-) diff --git a/app/helpers/issues_helper.rb b/app/helpers/issues_helper.rb index d803df0cc..06e5f87bc 100644 --- a/app/helpers/issues_helper.rb +++ b/app/helpers/issues_helper.rb @@ -34,6 +34,10 @@ module IssuesHelper def grouped_issue_list(issues, query, issue_count_by_group, &block) previous_group, first = false, true + totals_by_group = query.totalable_columns.inject({}) do |h, column| + h[column] = query.total_by_group_for(column) + h + end issue_list(issues) do |issue, level| group_name = group_count = nil if query.grouped? && ((group = query.group_by_column.value(issue)) != previous_group || first) @@ -44,8 +48,9 @@ module IssuesHelper end group_name ||= "" group_count = issue_count_by_group[group] + group_totals = totals_by_group.map {|column, t| total_tag(column, t[group] || 0)}.join(" ").html_safe end - yield issue, level, group_name, group_count + yield issue, level, group_name, group_count, group_totals previous_group, first = group, false end end diff --git a/app/helpers/queries_helper.rb b/app/helpers/queries_helper.rb index 3c6049acd..f817265dd 100644 --- a/app/helpers/queries_helper.rb +++ b/app/helpers/queries_helper.rb @@ -108,13 +108,17 @@ module QueriesHelper 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}") + total_tag(column, query.total_for(column)) end content_tag('p', totals.join(" ").html_safe, :class => "query-totals") end + def total_tag(column, value) + label = content_tag('span', "#{column.caption}:") + value = content_tag('span', format_object(value), :class => 'value') + content_tag('span', label + " " + value, :class => "total-for-#{column.name.to_s.dasherize}") + end + def column_header(column) column.sortable ? sort_header_tag(column.name.to_s, :caption => column.caption, :default_order => column.default_order) : diff --git a/app/models/issue_query.rb b/app/models/issue_query.rb index 36f47ef0e..45aacdffa 100644 --- a/app/models/issue_query.rb +++ b/app/models/issue_query.rb @@ -303,7 +303,6 @@ class IssueQuery < Query def base_scope Issue.visible.joins(:status, :project).where(statement) end - private :base_scope # Returns the issue count def issue_count @@ -312,55 +311,21 @@ class IssueQuery < Query 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).to_f.round(2) - 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").to_f.round(2) - 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 + # Returns the issue count by group or nil if query is not grouped + def issue_count_by_group + grouped_query do |scope| + scope.count + end end - def total_for_int_custom_field(custom_field) - total_for_custom_field(custom_field).to_i + # Returns sum of all the issue's estimated_hours + def total_for_estimated_hours(scope) + scope.sum(:estimated_hours) end - # Returns the issue count by group or nil if query is not grouped - def issue_count_by_group - r = nil - if grouped? - begin - # Rails3 will raise an (unexpected) RecordNotFound if there's only a nil group value - r = Issue.visible. - joins(:status, :project). - where(statement). - joins(joins_for_order_statement(group_by_statement)). - group(group_by_statement). - count - rescue ActiveRecord::RecordNotFound - r = {nil => issue_count} - end - c = group_by_column - if c.is_a?(QueryCustomFieldColumn) - r = r.keys.inject({}) {|h, k| h[c.custom_field.cast_value(k)] = r[k]; h} - end - end - r - rescue ::ActiveRecord::StatementInvalid => e - raise StatementInvalid.new(e.message) + # Returns sum of all the issue's time entries hours + def total_for_spent_hours(scope) + scope.joins(:time_entries).sum("#{TimeEntry.table_name}.hours") end # Returns the issues diff --git a/app/models/query.rb b/app/models/query.rb index ac7b7cd1c..452b6d00c 100644 --- a/app/models/query.rb +++ b/app/models/query.rb @@ -632,21 +632,99 @@ class Query < ActiveRecord::Base # Returns the sum of values for the given column def total_for(column) + total_with_scope(column, base_scope) + end + + # Returns a hash of the sum of the given column for each group, + # or nil if the query is not grouped + def total_by_group_for(column) + grouped_query do |scope| + total_with_scope(column, scope) + end + end + + def totals + totals = totalable_columns.map {|column| [column, total_for(column)]} + yield totals if block_given? + totals + end + + def totals_by_group + totals = totalable_columns.map {|column| [column, total_by_group_for(column)]} + yield totals if block_given? + totals + end + + private + + def grouped_query(&block) + r = nil + if grouped? + begin + # Rails3 will raise an (unexpected) RecordNotFound if there's only a nil group value + r = yield base_group_scope + rescue ActiveRecord::RecordNotFound + r = {nil => yield(base_scope)} + end + c = group_by_column + if c.is_a?(QueryCustomFieldColumn) + r = r.keys.inject({}) {|h, k| h[c.custom_field.cast_value(k)] = r[k]; h} + end + end + r + rescue ::ActiveRecord::StatementInvalid => e + raise StatementInvalid.new(e.message) + end + + def total_with_scope(column, scope) 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 + send "total_for_#{custom_field.field_format}_custom_field", custom_field, scope else - send "total_for_#{column.name}" + send "total_for_#{column.name}", scope end rescue ::ActiveRecord::StatementInvalid => e raise StatementInvalid.new(e.message) end - private + def base_scope + raise "unimplemented" + end + + def base_group_scope + base_scope. + joins(joins_for_order_statement(group_by_statement)). + group(group_by_statement) + end + + def total_for_float_custom_field(custom_field, scope) + total_for_custom_field(custom_field, scope) {|t| t.to_f.round(2)} + end + + def total_for_int_custom_field(custom_field, scope) + total_for_custom_field(custom_field, scope) {|t| t.to_i} + end + + def total_for_custom_field(custom_field, scope) + total = 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))") + + if block_given? + if total.is_a?(Hash) + total.keys.each {|k| total[k] = yield total[k]} + else + total = yield total + end + end + + total + end def sql_for_custom_field(field, operator, value, custom_field_id) db_table = CustomValue.table_name diff --git a/app/views/issues/_list.html.erb b/app/views/issues/_list.html.erb index c6c7613b4..57ae6b2bc 100644 --- a/app/views/issues/_list.html.erb +++ b/app/views/issues/_list.html.erb @@ -15,13 +15,13 @@ - <% grouped_issue_list(issues, @query, @issue_count_by_group) do |issue, level, group_name, group_count| -%> + <% grouped_issue_list(issues, @query, @issue_count_by_group) do |issue, level, group_name, group_count, group_totals| -%> <% if group_name %> <% reset_cycle %>   - <%= group_name %> <%= group_count %> + <%= group_name %> <%= group_count %> <%= group_totals %> <%= link_to_function("#{l(:button_collapse_all)}/#{l(:button_expand_all)}", "toggleAllRowGroups(this)", :class => 'toggle-all') %> diff --git a/public/stylesheets/application.css b/public/stylesheets/application.css index b5df98a90..f66b96b1b 100644 --- a/public/stylesheets/application.css +++ b/public/stylesheets/application.css @@ -231,9 +231,12 @@ table.plugins span.name { font-weight: bold; display: block; margin-bottom: 6px; table.plugins span.description { display: block; font-size: 0.9em; } table.plugins span.url { display: block; font-size: 0.9em; } -table.list tbody tr.group td { padding: 0.8em 0 0.5em 0.3em; font-weight: bold; border-bottom: 1px solid #ccc; text-align:left; } -table.list tbody tr.group span.count {position:relative; top:-1px; color:#fff; font-size:10px; background:#9DB9D5; padding:0px 6px 1px 6px; border-radius:3px; margin-left:4px;} -tr.group a.toggle-all { color: #aaa; font-size: 80%; font-weight: normal; display:none;} +tr.group td { padding: 0.8em 0 0.5em 0.3em; border-bottom: 1px solid #ccc; text-align:left; } +tr.group span.name {font-weight:bold;} +tr.group span.count {font-weight:bold; position:relative; top:-1px; color:#fff; font-size:10px; background:#9DB9D5; padding:0px 6px 1px 6px; border-radius:3px; margin-left:4px;} +tr.group span.totals {color: #aaa; font-size: 80%;} +tr.group span.totals .value {font-weight:bold; color:#777;} +tr.group a.toggle-all { color: #aaa; font-size: 80%; display:none; float:right; margin-right:4px;} tr.group:hover a.toggle-all { display:inline;} a.toggle-all:hover {text-decoration:none;} diff --git a/test/functional/issues_controller_test.rb b/test/functional/issues_controller_test.rb index 18c82aef1..158ba4316 100644 --- a/test/functional/issues_controller_test.rb +++ b/test/functional/issues_controller_test.rb @@ -953,10 +953,32 @@ class IssuesControllerTest < ActionController::TestCase 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 '.total-for-estimated-hours span.value', :text => '6.60' assert_select 'input[type=checkbox][name=?][value=estimated_hours][checked=checked]', 't[]' end + def test_index_with_grouped_query_and_estimated_hours_total + Issue.delete_all + Issue.generate!(:estimated_hours => 5.5, :category_id => 1) + Issue.generate!(:estimated_hours => 2.3, :category_id => 1) + Issue.generate!(:estimated_hours => 1.1, :category_id => 2) + Issue.generate!(:estimated_hours => 4.6) + + get :index, :t => %w(estimated_hours), :group_by => 'category' + assert_response :success + assert_select '.query-totals' + assert_select '.query-totals .total-for-estimated-hours span.value', :text => '13.50' + assert_select 'tr.group', :text => /Printing/ do + assert_select '.total-for-estimated-hours span.value', :text => '7.80' + end + assert_select 'tr.group', :text => /Recipes/ do + assert_select '.total-for-estimated-hours span.value', :text => '1.10' + end + assert_select 'tr.group', :text => /blank/ do + assert_select '.total-for-estimated-hours span.value', :text => '4.60' + end + 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') diff --git a/test/unit/query_test.rb b/test/unit/query_test.rb index b69bec740..679fc772d 100644 --- a/test/unit/query_test.rb +++ b/test/unit/query_test.rb @@ -1183,6 +1183,19 @@ class QueryTest < ActiveSupport::TestCase assert_equal 6.6, q.total_for(:estimated_hours) end + def test_total_by_group_for_estimated_hours + Issue.delete_all + Issue.generate!(:estimated_hours => 5.5, :assigned_to_id => 2) + Issue.generate!(:estimated_hours => 1.1, :assigned_to_id => 3) + Issue.generate!(:estimated_hours => 3.5) + + q = IssueQuery.new(:group_by => 'assigned_to') + assert_equal( + {nil => 3.5, User.find(2) => 5.5, User.find(3) => 1.1}, + q.total_by_group_for(:estimated_hours) + ) + end + def test_total_for_spent_hours TimeEntry.delete_all TimeEntry.generate!(:hours => 5.5) @@ -1192,6 +1205,20 @@ class QueryTest < ActiveSupport::TestCase assert_equal 6.6, q.total_for(:spent_hours) end + def test_total_by_group_for_spent_hours + TimeEntry.delete_all + TimeEntry.generate!(:hours => 5.5, :issue_id => 1) + TimeEntry.generate!(:hours => 1.1, :issue_id => 2) + Issue.where(:id => 1).update_all(:assigned_to_id => 2) + Issue.where(:id => 2).update_all(:assigned_to_id => 3) + + q = IssueQuery.new(:group_by => 'assigned_to') + assert_equal( + {User.find(2) => 5.5, User.find(3) => 1.1}, + q.total_by_group_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') @@ -1202,6 +1229,20 @@ class QueryTest < ActiveSupport::TestCase assert_equal 9, q.total_for("cf_#{field.id}") end + def test_total_by_group_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') + Issue.where(:id => 1).update_all(:assigned_to_id => 2) + Issue.where(:id => 2).update_all(:assigned_to_id => 3) + + q = IssueQuery.new(:group_by => 'assigned_to') + assert_equal( + {User.find(2) => 2, User.find(3) => 7}, + q.total_by_group_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') -- 2.39.5