##// END OF EJS Templates
jro_apps » redmine
RSS
Fixed that time report raises a SQL error if there are multiple CustomValue for a time entry (#11160)....
Jean-Philippe Lang -
r9648:76eeb64d1aa8
parent child
Show More
Side by Side Unified
Context file:
r9648:76eeb64d1aa8 Loading diff...:
Show file before | Show file after | Hide comments
@@ -1,164 +1,164
1 1 # Redmine - project management software
2 2 # Copyright (C) 2006-2012 Jean-Philippe Lang
3 3 #
4 4 # This program is free software; you can redistribute it and/or
5 5 # modify it under the terms of the GNU General Public License
6 6 # as published by the Free Software Foundation; either version 2
7 7 # of the License, or (at your option) any later version.
8 8 #
9 9 # This program is distributed in the hope that it will be useful,
10 10 # but WITHOUT ANY WARRANTY; without even the implied warranty of
11 11 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
12 12 # GNU General Public License for more details.
13 13 #
14 14 # You should have received a copy of the GNU General Public License
15 15 # along with this program; if not, write to the Free Software
16 16 # Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
17 17
18 18 module Redmine
19 19 module Helpers
20 20 class TimeReport
21 21 attr_reader :criteria, :columns, :from, :to, :hours, :total_hours, :periods
22 22
23 23 def initialize(project, issue, criteria, columns, from, to)
24 24 @project = project
25 25 @issue = issue
26 26
27 27 @criteria = criteria || []
28 28 @criteria = @criteria.select{|criteria| available_criteria.has_key? criteria}
29 29 @criteria.uniq!
30 30 @criteria = @criteria[0,3]
31 31
32 32 @columns = (columns && %w(year month week day).include?(columns)) ? columns : 'month'
33 33 @from = from
34 34 @to = to
35 35
36 36 run
37 37 end
38 38
39 39 def available_criteria
40 40 @available_criteria || load_available_criteria
41 41 end
42 42
43 43 private
44 44
45 45 def run
46 46 unless @criteria.empty?
47 47 scope = TimeEntry.visible.spent_between(@from, @to)
48 48 if @issue
49 49 scope = scope.on_issue(@issue)
50 50 elsif @project
51 51 scope = scope.on_project(@project, Setting.display_subprojects_issues?)
52 52 end
53 53 time_columns = %w(tyear tmonth tweek spent_on)
54 54 @hours = []
55 55 scope.sum(:hours, :include => :issue, :group => @criteria.collect{|criteria| @available_criteria[criteria][:sql]} + time_columns).each do |hash, hours|
56 56 h = {'hours' => hours}
57 57 (@criteria + time_columns).each_with_index do |name, i|
58 58 h[name] = hash[i]
59 59 end
60 60 @hours << h
61 61 end
62 62
63 63 @hours.each do |row|
64 64 case @columns
65 65 when 'year'
66 66 row['year'] = row['tyear']
67 67 when 'month'
68 68 row['month'] = "#{row['tyear']}-#{row['tmonth']}"
69 69 when 'week'
70 70 row['week'] = "#{row['tyear']}-#{row['tweek']}"
71 71 when 'day'
72 72 row['day'] = "#{row['spent_on']}"
73 73 end
74 74 end
75 75
76 76 if @from.nil?
77 77 min = @hours.collect {|row| row['spent_on']}.min
78 78 @from = min ? min.to_date : Date.today
79 79 end
80 80
81 81 if @to.nil?
82 82 max = @hours.collect {|row| row['spent_on']}.max
83 83 @to = max ? max.to_date : Date.today
84 84 end
85 85
86 86 @total_hours = @hours.inject(0) {|s,k| s = s + k['hours'].to_f}
87 87
88 88 @periods = []
89 89 # Date#at_beginning_of_ not supported in Rails 1.2.x
90 90 date_from = @from.to_time
91 91 # 100 columns max
92 92 while date_from <= @to.to_time && @periods.length < 100
93 93 case @columns
94 94 when 'year'
95 95 @periods << "#{date_from.year}"
96 96 date_from = (date_from + 1.year).at_beginning_of_year
97 97 when 'month'
98 98 @periods << "#{date_from.year}-#{date_from.month}"
99 99 date_from = (date_from + 1.month).at_beginning_of_month
100 100 when 'week'
101 101 @periods << "#{date_from.year}-#{date_from.to_date.cweek}"
102 102 date_from = (date_from + 7.day).at_beginning_of_week
103 103 when 'day'
104 104 @periods << "#{date_from.to_date}"
105 105 date_from = date_from + 1.day
106 106 end
107 107 end
108 108 end
109 109 end
110 110
111 111 def load_available_criteria
112 112 @available_criteria = { 'project' => {:sql => "#{TimeEntry.table_name}.project_id",
113 113 :klass => Project,
114 114 :label => :label_project},
115 115 'status' => {:sql => "#{Issue.table_name}.status_id",
116 116 :klass => IssueStatus,
117 117 :label => :field_status},
118 118 'version' => {:sql => "#{Issue.table_name}.fixed_version_id",
119 119 :klass => Version,
120 120 :label => :label_version},
121 121 'category' => {:sql => "#{Issue.table_name}.category_id",
122 122 :klass => IssueCategory,
123 123 :label => :field_category},
124 124 'member' => {:sql => "#{TimeEntry.table_name}.user_id",
125 125 :klass => User,
126 126 :label => :label_member},
127 127 'tracker' => {:sql => "#{Issue.table_name}.tracker_id",
128 128 :klass => Tracker,
129 129 :label => :label_tracker},
130 130 'activity' => {:sql => "#{TimeEntry.table_name}.activity_id",
131 131 :klass => TimeEntryActivity,
132 132 :label => :label_activity},
133 133 'issue' => {:sql => "#{TimeEntry.table_name}.issue_id",
134 134 :klass => Issue,
135 135 :label => :label_issue}
136 136 }
137 137
138 138 # Add list and boolean custom fields as available criteria
139 139 custom_fields = (@project.nil? ? IssueCustomField.for_all : @project.all_issue_custom_fields)
140 140 custom_fields.select {|cf| %w(list bool).include? cf.field_format }.each do |cf|
141 @available_criteria["cf_#{cf.id}"] = {:sql => "(SELECT c.value FROM #{CustomValue.table_name} c WHERE c.custom_field_id = #{cf.id} AND c.customized_type = 'Issue' AND c.customized_id = #{Issue.table_name}.id)",
141 @available_criteria["cf_#{cf.id}"] = {:sql => "(SELECT c.value FROM #{CustomValue.table_name} c WHERE c.custom_field_id = #{cf.id} AND c.customized_type = 'Issue' AND c.customized_id = #{Issue.table_name}.id ORDER BY c.value LIMIT 1)",
142 142 :format => cf.field_format,
143 143 :label => cf.name}
144 144 end if @project
145 145
146 146 # Add list and boolean time entry custom fields
147 147 TimeEntryCustomField.find(:all).select {|cf| %w(list bool).include? cf.field_format }.each do |cf|
148 @available_criteria["cf_#{cf.id}"] = {:sql => "(SELECT c.value FROM #{CustomValue.table_name} c WHERE c.custom_field_id = #{cf.id} AND c.customized_type = 'TimeEntry' AND c.customized_id = #{TimeEntry.table_name}.id)",
148 @available_criteria["cf_#{cf.id}"] = {:sql => "(SELECT c.value FROM #{CustomValue.table_name} c WHERE c.custom_field_id = #{cf.id} AND c.customized_type = 'TimeEntry' AND c.customized_id = #{TimeEntry.table_name}.id ORDER BY c.value LIMIT 1)",
149 149 :format => cf.field_format,
150 150 :label => cf.name}
151 151 end
152 152
153 153 # Add list and boolean time entry activity custom fields
154 154 TimeEntryActivityCustomField.find(:all).select {|cf| %w(list bool).include? cf.field_format }.each do |cf|
155 @available_criteria["cf_#{cf.id}"] = {:sql => "(SELECT c.value FROM #{CustomValue.table_name} c WHERE c.custom_field_id = #{cf.id} AND c.customized_type = 'Enumeration' AND c.customized_id = #{TimeEntry.table_name}.activity_id)",
155 @available_criteria["cf_#{cf.id}"] = {:sql => "(SELECT c.value FROM #{CustomValue.table_name} c WHERE c.custom_field_id = #{cf.id} AND c.customized_type = 'Enumeration' AND c.customized_id = #{TimeEntry.table_name}.activity_id ORDER BY c.value LIMIT 1)",
156 156 :format => cf.field_format,
157 157 :label => cf.name}
158 158 end
159 159
160 160 @available_criteria
161 161 end
162 162 end
163 163 end
164 164 end
Show file before | Show file after | Hide comments
@@ -1,307 +1,317
1 1 # -*- coding: utf-8 -*-
2 2 require File.expand_path('../../test_helper', __FILE__)
3 3
4 4 class TimeEntryReportsControllerTest < ActionController::TestCase
5 5 tests TimelogController
6 6
7 7 fixtures :projects, :enabled_modules, :roles, :members, :member_roles,
8 8 :issues, :time_entries, :users, :trackers, :enumerations,
9 9 :issue_statuses, :custom_fields, :custom_values
10 10
11 11 include Redmine::I18n
12 12
13 13 def setup
14 14 Setting.default_language = "en"
15 15 end
16 16
17 17 def test_report_at_project_level
18 18 get :report, :project_id => 'ecookbook'
19 19 assert_response :success
20 20 assert_template 'report'
21 21 assert_tag :form,
22 22 :attributes => {:action => "/projects/ecookbook/time_entries/report", :id => 'query_form'}
23 23 end
24 24
25 25 def test_report_all_projects
26 26 get :report
27 27 assert_response :success
28 28 assert_template 'report'
29 29 assert_tag :form,
30 30 :attributes => {:action => "/time_entries/report", :id => 'query_form'}
31 31 end
32 32
33 33 def test_report_all_projects_denied
34 34 r = Role.anonymous
35 35 r.permissions.delete(:view_time_entries)
36 36 r.permissions_will_change!
37 37 r.save
38 38 get :report
39 39 assert_redirected_to '/login?back_url=http%3A%2F%2Ftest.host%2Ftime_entries%2Freport'
40 40 end
41 41
42 42 def test_report_all_projects_one_criteria
43 43 get :report, :columns => 'week', :from => "2007-04-01", :to => "2007-04-30", :criteria => ['project']
44 44 assert_response :success
45 45 assert_template 'report'
46 46 assert_not_nil assigns(:report)
47 47 assert_equal "8.65", "%.2f" % assigns(:report).total_hours
48 48 end
49 49
50 50 def test_report_all_time
51 51 get :report, :project_id => 1, :criteria => ['project', 'issue']
52 52 assert_response :success
53 53 assert_template 'report'
54 54 assert_not_nil assigns(:report)
55 55 assert_equal "162.90", "%.2f" % assigns(:report).total_hours
56 56 end
57 57
58 58 def test_report_all_time_by_day
59 59 get :report, :project_id => 1, :criteria => ['project', 'issue'], :columns => 'day'
60 60 assert_response :success
61 61 assert_template 'report'
62 62 assert_not_nil assigns(:report)
63 63 assert_equal "162.90", "%.2f" % assigns(:report).total_hours
64 64 assert_tag :tag => 'th', :content => '2007-03-12'
65 65 end
66 66
67 67 def test_report_one_criteria
68 68 get :report, :project_id => 1, :columns => 'week', :from => "2007-04-01", :to => "2007-04-30", :criteria => ['project']
69 69 assert_response :success
70 70 assert_template 'report'
71 71 assert_not_nil assigns(:report)
72 72 assert_equal "8.65", "%.2f" % assigns(:report).total_hours
73 73 end
74 74
75 75 def test_report_two_criteria
76 76 get :report, :project_id => 1, :columns => 'month', :from => "2007-01-01", :to => "2007-12-31", :criteria => ["member", "activity"]
77 77 assert_response :success
78 78 assert_template 'report'
79 79 assert_not_nil assigns(:report)
80 80 assert_equal "162.90", "%.2f" % assigns(:report).total_hours
81 81 end
82 82
83 def test_report_custom_field_criteria_with_multiple_values
84 field = TimeEntryCustomField.create!(:name => 'multi', :field_format => 'list', :possible_values => ['value1', 'value2'])
85 entry = TimeEntry.create!(:project => Project.find(1), :hours => 1, :activity_id => 10, :user => User.find(2), :spent_on => Date.today)
86 CustomValue.create!(:customized => entry, :custom_field => field, :value => 'value1')
87 CustomValue.create!(:customized => entry, :custom_field => field, :value => 'value2')
88
89 get :report, :project_id => 1, :columns => 'day', :criteria => ["cf_#{field.id}"]
90 assert_response :success
91 end
92
83 93 def test_report_one_day
84 94 get :report, :project_id => 1, :columns => 'day', :from => "2007-03-23", :to => "2007-03-23", :criteria => ["member", "activity"]
85 95 assert_response :success
86 96 assert_template 'report'
87 97 assert_not_nil assigns(:report)
88 98 assert_equal "4.25", "%.2f" % assigns(:report).total_hours
89 99 end
90 100
91 101 def test_report_at_issue_level
92 102 get :report, :project_id => 1, :issue_id => 1, :columns => 'month', :from => "2007-01-01", :to => "2007-12-31", :criteria => ["member", "activity"]
93 103 assert_response :success
94 104 assert_template 'report'
95 105 assert_not_nil assigns(:report)
96 106 assert_equal "154.25", "%.2f" % assigns(:report).total_hours
97 107 assert_tag :form,
98 108 :attributes => {:action => "/projects/ecookbook/issues/1/time_entries/report", :id => 'query_form'}
99 109 end
100 110
101 111 def test_report_custom_field_criteria
102 112 get :report, :project_id => 1, :criteria => ['project', 'cf_1', 'cf_7']
103 113 assert_response :success
104 114 assert_template 'report'
105 115 assert_not_nil assigns(:report)
106 116 assert_equal 3, assigns(:report).criteria.size
107 117 assert_equal "162.90", "%.2f" % assigns(:report).total_hours
108 118 # Custom field column
109 119 assert_tag :tag => 'th', :content => 'Database'
110 120 # Custom field row
111 121 assert_tag :tag => 'td', :content => 'MySQL',
112 122 :sibling => { :tag => 'td', :attributes => { :class => 'hours' },
113 123 :child => { :tag => 'span', :attributes => { :class => 'hours hours-int' },
114 124 :content => '1' }}
115 125 # Second custom field column
116 126 assert_tag :tag => 'th', :content => 'Billable'
117 127 end
118 128
119 129 def test_report_one_criteria_no_result
120 130 get :report, :project_id => 1, :columns => 'week', :from => "1998-04-01", :to => "1998-04-30", :criteria => ['project']
121 131 assert_response :success
122 132 assert_template 'report'
123 133 assert_not_nil assigns(:report)
124 134 assert_equal "0.00", "%.2f" % assigns(:report).total_hours
125 135 end
126 136
127 137 def test_report_status_criterion
128 138 get :report, :project_id => 1, :criteria => ['status']
129 139 assert_response :success
130 140 assert_template 'report'
131 141 assert_tag :tag => 'th', :content => 'Status'
132 142 assert_tag :tag => 'td', :content => 'New'
133 143 end
134 144
135 145 def test_report_all_projects_csv_export
136 146 get :report, :columns => 'month', :from => "2007-01-01", :to => "2007-06-30",
137 147 :criteria => ["project", "member", "activity"], :format => "csv"
138 148 assert_response :success
139 149 assert_equal 'text/csv; header=present', @response.content_type
140 150 lines = @response.body.chomp.split("\n")
141 151 # Headers
142 152 assert_equal 'Project,Member,Activity,2007-1,2007-2,2007-3,2007-4,2007-5,2007-6,Total',
143 153 lines.first
144 154 # Total row
145 155 assert_equal 'Total,"","","","",154.25,8.65,"","",162.90', lines.last
146 156 end
147 157
148 158 def test_report_csv_export
149 159 get :report, :project_id => 1, :columns => 'month',
150 160 :from => "2007-01-01", :to => "2007-06-30",
151 161 :criteria => ["project", "member", "activity"], :format => "csv"
152 162 assert_response :success
153 163 assert_equal 'text/csv; header=present', @response.content_type
154 164 lines = @response.body.chomp.split("\n")
155 165 # Headers
156 166 assert_equal 'Project,Member,Activity,2007-1,2007-2,2007-3,2007-4,2007-5,2007-6,Total',
157 167 lines.first
158 168 # Total row
159 169 assert_equal 'Total,"","","","",154.25,8.65,"","",162.90', lines.last
160 170 end
161 171
162 172 def test_csv_big_5
163 173 Setting.default_language = "zh-TW"
164 174 str_utf8 = "\xe4\xb8\x80\xe6\x9c\x88"
165 175 str_big5 = "\xa4@\xa4\xeb"
166 176 if str_utf8.respond_to?(:force_encoding)
167 177 str_utf8.force_encoding('UTF-8')
168 178 str_big5.force_encoding('Big5')
169 179 end
170 180 user = User.find_by_id(3)
171 181 user.firstname = str_utf8
172 182 user.lastname = "test-lastname"
173 183 assert user.save
174 184 comments = "test_csv_big_5"
175 185 te1 = TimeEntry.create(:spent_on => '2011-11-11',
176 186 :hours => 7.3,
177 187 :project => Project.find(1),
178 188 :user => user,
179 189 :activity => TimeEntryActivity.find_by_name('Design'),
180 190 :comments => comments)
181 191
182 192 te2 = TimeEntry.find_by_comments(comments)
183 193 assert_not_nil te2
184 194 assert_equal 7.3, te2.hours
185 195 assert_equal 3, te2.user_id
186 196
187 197 get :report, :project_id => 1, :columns => 'day',
188 198 :from => "2011-11-11", :to => "2011-11-11",
189 199 :criteria => ["member"], :format => "csv"
190 200 assert_response :success
191 201 assert_equal 'text/csv; header=present', @response.content_type
192 202 lines = @response.body.chomp.split("\n")
193 203 # Headers
194 204 s1 = "\xa6\xa8\xad\xfb,2011-11-11,\xc1`\xadp"
195 205 s2 = "\xc1`\xadp"
196 206 if s1.respond_to?(:force_encoding)
197 207 s1.force_encoding('Big5')
198 208 s2.force_encoding('Big5')
199 209 end
200 210 assert_equal s1, lines.first
201 211 # Total row
202 212 assert_equal "#{str_big5} #{user.lastname},7.30,7.30", lines[1]
203 213 assert_equal "#{s2},7.30,7.30", lines[2]
204 214
205 215 str_tw = "Traditional Chinese (\xe7\xb9\x81\xe9\xab\x94\xe4\xb8\xad\xe6\x96\x87)"
206 216 if str_tw.respond_to?(:force_encoding)
207 217 str_tw.force_encoding('UTF-8')
208 218 end
209 219 assert_equal str_tw, l(:general_lang_name)
210 220 assert_equal 'Big5', l(:general_csv_encoding)
211 221 assert_equal ',', l(:general_csv_separator)
212 222 assert_equal '.', l(:general_csv_decimal_separator)
213 223 end
214 224
215 225 def test_csv_cannot_convert_should_be_replaced_big_5
216 226 Setting.default_language = "zh-TW"
217 227 str_utf8 = "\xe4\xbb\xa5\xe5\x86\x85"
218 228 if str_utf8.respond_to?(:force_encoding)
219 229 str_utf8.force_encoding('UTF-8')
220 230 end
221 231 user = User.find_by_id(3)
222 232 user.firstname = str_utf8
223 233 user.lastname = "test-lastname"
224 234 assert user.save
225 235 comments = "test_replaced"
226 236 te1 = TimeEntry.create(:spent_on => '2011-11-11',
227 237 :hours => 7.3,
228 238 :project => Project.find(1),
229 239 :user => user,
230 240 :activity => TimeEntryActivity.find_by_name('Design'),
231 241 :comments => comments)
232 242
233 243 te2 = TimeEntry.find_by_comments(comments)
234 244 assert_not_nil te2
235 245 assert_equal 7.3, te2.hours
236 246 assert_equal 3, te2.user_id
237 247
238 248 get :report, :project_id => 1, :columns => 'day',
239 249 :from => "2011-11-11", :to => "2011-11-11",
240 250 :criteria => ["member"], :format => "csv"
241 251 assert_response :success
242 252 assert_equal 'text/csv; header=present', @response.content_type
243 253 lines = @response.body.chomp.split("\n")
244 254 # Headers
245 255 s1 = "\xa6\xa8\xad\xfb,2011-11-11,\xc1`\xadp"
246 256 if s1.respond_to?(:force_encoding)
247 257 s1.force_encoding('Big5')
248 258 end
249 259 assert_equal s1, lines.first
250 260 # Total row
251 261 s2 = ""
252 262 if s2.respond_to?(:force_encoding)
253 263 s2 = "\xa5H?"
254 264 s2.force_encoding('Big5')
255 265 elsif RUBY_PLATFORM == 'java'
256 266 s2 = "??"
257 267 else
258 268 s2 = "\xa5H???"
259 269 end
260 270 assert_equal "#{s2} #{user.lastname},7.30,7.30", lines[1]
261 271 end
262 272
263 273 def test_csv_fr
264 274 with_settings :default_language => "fr" do
265 275 str1 = "test_csv_fr"
266 276 user = User.find_by_id(3)
267 277 te1 = TimeEntry.create(:spent_on => '2011-11-11',
268 278 :hours => 7.3,
269 279 :project => Project.find(1),
270 280 :user => user,
271 281 :activity => TimeEntryActivity.find_by_name('Design'),
272 282 :comments => str1)
273 283
274 284 te2 = TimeEntry.find_by_comments(str1)
275 285 assert_not_nil te2
276 286 assert_equal 7.3, te2.hours
277 287 assert_equal 3, te2.user_id
278 288
279 289 get :report, :project_id => 1, :columns => 'day',
280 290 :from => "2011-11-11", :to => "2011-11-11",
281 291 :criteria => ["member"], :format => "csv"
282 292 assert_response :success
283 293 assert_equal 'text/csv; header=present', @response.content_type
284 294 lines = @response.body.chomp.split("\n")
285 295 # Headers
286 296 s1 = "Membre;2011-11-11;Total"
287 297 s2 = "Total"
288 298 if s1.respond_to?(:force_encoding)
289 299 s1.force_encoding('ISO-8859-1')
290 300 s2.force_encoding('ISO-8859-1')
291 301 end
292 302 assert_equal s1, lines.first
293 303 # Total row
294 304 assert_equal "#{user.firstname} #{user.lastname};7,30;7,30", lines[1]
295 305 assert_equal "#{s2};7,30;7,30", lines[2]
296 306
297 307 str_fr = "Fran\xc3\xa7ais"
298 308 if str_fr.respond_to?(:force_encoding)
299 309 str_fr.force_encoding('UTF-8')
300 310 end
301 311 assert_equal str_fr, l(:general_lang_name)
302 312 assert_equal 'ISO-8859-1', l(:general_csv_encoding)
303 313 assert_equal ';', l(:general_csv_separator)
304 314 assert_equal ',', l(:general_csv_decimal_separator)
305 315 end
306 316 end
307 317 end
General Comments 0
You need to be logged in to leave comments. Login now