##// END OF EJS Templates
jro_apps » redmine
RSS
Merged r3409 from trunk....
Jean-Philippe Lang -
r3350:13fb739b5620
parent child
Show More
Side by Side Unified
Context file:
r3350:13fb739b5620 Loading diff...:
Show file before | Show file after | Hide comments
@@ -1,559 +1,563
1 1 # Redmine - project management software
2 2 # Copyright (C) 2006-2008 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 class QueryColumn
19 19 attr_accessor :name, :sortable, :groupable, :default_order
20 20 include Redmine::I18n
21 21
22 22 def initialize(name, options={})
23 23 self.name = name
24 24 self.sortable = options[:sortable]
25 25 self.groupable = options[:groupable] || false
26 26 if groupable == true
27 27 self.groupable = name.to_s
28 28 end
29 29 self.default_order = options[:default_order]
30 30 end
31 31
32 32 def caption
33 33 l("field_#{name}")
34 34 end
35 35
36 36 # Returns true if the column is sortable, otherwise false
37 37 def sortable?
38 38 !sortable.nil?
39 39 end
40 40
41 41 def value(issue)
42 42 issue.send name
43 43 end
44 44 end
45 45
46 46 class QueryCustomFieldColumn < QueryColumn
47 47
48 48 def initialize(custom_field)
49 49 self.name = "cf_#{custom_field.id}".to_sym
50 50 self.sortable = custom_field.order_statement || false
51 51 if %w(list date bool int).include?(custom_field.field_format)
52 52 self.groupable = custom_field.order_statement
53 53 end
54 54 self.groupable ||= false
55 55 @cf = custom_field
56 56 end
57 57
58 58 def caption
59 59 @cf.name
60 60 end
61 61
62 62 def custom_field
63 63 @cf
64 64 end
65 65
66 66 def value(issue)
67 67 cv = issue.custom_values.detect {|v| v.custom_field_id == @cf.id}
68 68 cv && @cf.cast_value(cv.value)
69 69 end
70 70 end
71 71
72 72 class Query < ActiveRecord::Base
73 73 class StatementInvalid < ::ActiveRecord::StatementInvalid
74 74 end
75 75
76 76 belongs_to :project
77 77 belongs_to :user
78 78 serialize :filters
79 79 serialize :column_names
80 80 serialize :sort_criteria, Array
81 81
82 82 attr_protected :project_id, :user_id
83 83
84 84 validates_presence_of :name, :on => :save
85 85 validates_length_of :name, :maximum => 255
86 86
87 87 @@operators = { "=" => :label_equals,
88 88 "!" => :label_not_equals,
89 89 "o" => :label_open_issues,
90 90 "c" => :label_closed_issues,
91 91 "!*" => :label_none,
92 92 "*" => :label_all,
93 93 ">=" => :label_greater_or_equal,
94 94 "<=" => :label_less_or_equal,
95 95 "<t+" => :label_in_less_than,
96 96 ">t+" => :label_in_more_than,
97 97 "t+" => :label_in,
98 98 "t" => :label_today,
99 99 "w" => :label_this_week,
100 100 ">t-" => :label_less_than_ago,
101 101 "<t-" => :label_more_than_ago,
102 102 "t-" => :label_ago,
103 103 "~" => :label_contains,
104 104 "!~" => :label_not_contains }
105 105
106 106 cattr_reader :operators
107 107
108 108 @@operators_by_filter_type = { :list => [ "=", "!" ],
109 109 :list_status => [ "o", "=", "!", "c", "*" ],
110 110 :list_optional => [ "=", "!", "!*", "*" ],
111 111 :list_subprojects => [ "*", "!*", "=" ],
112 112 :date => [ "<t+", ">t+", "t+", "t", "w", ">t-", "<t-", "t-" ],
113 113 :date_past => [ ">t-", "<t-", "t-", "t", "w" ],
114 114 :string => [ "=", "~", "!", "!~" ],
115 115 :text => [ "~", "!~" ],
116 116 :integer => [ "=", ">=", "<=", "!*", "*" ] }
117 117
118 118 cattr_reader :operators_by_filter_type
119 119
120 120 @@available_columns = [
121 121 QueryColumn.new(:project, :sortable => "#{Project.table_name}.name", :groupable => true),
122 122 QueryColumn.new(:tracker, :sortable => "#{Tracker.table_name}.position", :groupable => true),
123 123 QueryColumn.new(:status, :sortable => "#{IssueStatus.table_name}.position", :groupable => true),
124 124 QueryColumn.new(:priority, :sortable => "#{IssuePriority.table_name}.position", :default_order => 'desc', :groupable => true),
125 125 QueryColumn.new(:subject, :sortable => "#{Issue.table_name}.subject"),
126 126 QueryColumn.new(:author),
127 127 QueryColumn.new(:assigned_to, :sortable => ["#{User.table_name}.lastname", "#{User.table_name}.firstname", "#{User.table_name}.id"], :groupable => true),
128 128 QueryColumn.new(:updated_on, :sortable => "#{Issue.table_name}.updated_on", :default_order => 'desc'),
129 129 QueryColumn.new(:category, :sortable => "#{IssueCategory.table_name}.name", :groupable => true),
130 130 QueryColumn.new(:fixed_version, :sortable => ["#{Version.table_name}.effective_date", "#{Version.table_name}.name"], :default_order => 'desc', :groupable => true),
131 131 QueryColumn.new(:start_date, :sortable => "#{Issue.table_name}.start_date"),
132 132 QueryColumn.new(:due_date, :sortable => "#{Issue.table_name}.due_date"),
133 133 QueryColumn.new(:estimated_hours, :sortable => "#{Issue.table_name}.estimated_hours"),
134 134 QueryColumn.new(:done_ratio, :sortable => "#{Issue.table_name}.done_ratio", :groupable => true),
135 135 QueryColumn.new(:created_on, :sortable => "#{Issue.table_name}.created_on", :default_order => 'desc'),
136 136 ]
137 137 cattr_reader :available_columns
138 138
139 139 def initialize(attributes = nil)
140 140 super attributes
141 141 self.filters ||= { 'status_id' => {:operator => "o", :values => [""]} }
142 142 end
143 143
144 144 def after_initialize
145 145 # Store the fact that project is nil (used in #editable_by?)
146 146 @is_for_all = project.nil?
147 147 end
148 148
149 149 def validate
150 150 filters.each_key do |field|
151 151 errors.add label_for(field), :blank unless
152 152 # filter requires one or more values
153 153 (values_for(field) and !values_for(field).first.blank?) or
154 154 # filter doesn't require any value
155 155 ["o", "c", "!*", "*", "t", "w"].include? operator_for(field)
156 156 end if filters
157 157 end
158 158
159 159 def editable_by?(user)
160 160 return false unless user
161 161 # Admin can edit them all and regular users can edit their private queries
162 162 return true if user.admin? || (!is_public && self.user_id == user.id)
163 163 # Members can not edit public queries that are for all project (only admin is allowed to)
164 164 is_public && !@is_for_all && user.allowed_to?(:manage_public_queries, project)
165 165 end
166 166
167 167 def available_filters
168 168 return @available_filters if @available_filters
169 169
170 170 trackers = project.nil? ? Tracker.find(:all, :order => 'position') : project.rolled_up_trackers
171 171
172 172 @available_filters = { "status_id" => { :type => :list_status, :order => 1, :values => IssueStatus.find(:all, :order => 'position').collect{|s| [s.name, s.id.to_s] } },
173 173 "tracker_id" => { :type => :list, :order => 2, :values => trackers.collect{|s| [s.name, s.id.to_s] } },
174 174 "priority_id" => { :type => :list, :order => 3, :values => IssuePriority.all.collect{|s| [s.name, s.id.to_s] } },
175 175 "subject" => { :type => :text, :order => 8 },
176 176 "created_on" => { :type => :date_past, :order => 9 },
177 177 "updated_on" => { :type => :date_past, :order => 10 },
178 178 "start_date" => { :type => :date, :order => 11 },
179 179 "due_date" => { :type => :date, :order => 12 },
180 180 "estimated_hours" => { :type => :integer, :order => 13 },
181 181 "done_ratio" => { :type => :integer, :order => 14 }}
182 182
183 183 user_values = []
184 184 user_values << ["<< #{l(:label_me)} >>", "me"] if User.current.logged?
185 185 if project
186 186 user_values += project.users.sort.collect{|s| [s.name, s.id.to_s] }
187 187 else
188 188 # members of the user's projects
189 189 # OPTIMIZE: Is selecting from users per project (N+1)
190 190 user_values += User.current.projects.collect(&:users).flatten.uniq.sort.collect{|s| [s.name, s.id.to_s] }
191 191 end
192 192 @available_filters["assigned_to_id"] = { :type => :list_optional, :order => 4, :values => user_values } unless user_values.empty?
193 193 @available_filters["author_id"] = { :type => :list, :order => 5, :values => user_values } unless user_values.empty?
194 194
195 195 if User.current.logged?
196 196 @available_filters["watcher_id"] = { :type => :list, :order => 15, :values => [["<< #{l(:label_me)} >>", "me"]] }
197 197 end
198 198
199 199 if project
200 200 # project specific filters
201 201 unless @project.issue_categories.empty?
202 202 @available_filters["category_id"] = { :type => :list_optional, :order => 6, :values => @project.issue_categories.collect{|s| [s.name, s.id.to_s] } }
203 203 end
204 204 unless @project.shared_versions.empty?
205 205 @available_filters["fixed_version_id"] = { :type => :list_optional, :order => 7, :values => @project.shared_versions.sort.collect{|s| ["#{s.project.name} - #{s.name}", s.id.to_s] } }
206 206 end
207 207 unless @project.descendants.active.empty?
208 208 @available_filters["subproject_id"] = { :type => :list_subprojects, :order => 13, :values => @project.descendants.visible.collect{|s| [s.name, s.id.to_s] } }
209 209 end
210 210 add_custom_fields_filters(@project.all_issue_custom_fields)
211 211 else
212 212 # global filters for cross project issue list
213 system_shared_versions = Version.visible.find_all_by_sharing('system')
214 unless system_shared_versions.empty?
215 @available_filters["fixed_version_id"] = { :type => :list_optional, :order => 7, :values => system_shared_versions.sort.collect{|s| ["#{s.project.name} - #{s.name}", s.id.to_s] } }
216 end
213 217 add_custom_fields_filters(IssueCustomField.find(:all, :conditions => {:is_filter => true, :is_for_all => true}))
214 218 end
215 219 @available_filters
216 220 end
217 221
218 222 def add_filter(field, operator, values)
219 223 # values must be an array
220 224 return unless values and values.is_a? Array # and !values.first.empty?
221 225 # check if field is defined as an available filter
222 226 if available_filters.has_key? field
223 227 filter_options = available_filters[field]
224 228 # check if operator is allowed for that filter
225 229 #if @@operators_by_filter_type[filter_options[:type]].include? operator
226 230 # allowed_values = values & ([""] + (filter_options[:values] || []).collect {|val| val[1]})
227 231 # filters[field] = {:operator => operator, :values => allowed_values } if (allowed_values.first and !allowed_values.first.empty?) or ["o", "c", "!*", "*", "t"].include? operator
228 232 #end
229 233 filters[field] = {:operator => operator, :values => values }
230 234 end
231 235 end
232 236
233 237 def add_short_filter(field, expression)
234 238 return unless expression
235 239 parms = expression.scan(/^(o|c|!\*|!|\*)?(.*)$/).first
236 240 add_filter field, (parms[0] || "="), [parms[1] || ""]
237 241 end
238 242
239 243 def has_filter?(field)
240 244 filters and filters[field]
241 245 end
242 246
243 247 def operator_for(field)
244 248 has_filter?(field) ? filters[field][:operator] : nil
245 249 end
246 250
247 251 def values_for(field)
248 252 has_filter?(field) ? filters[field][:values] : nil
249 253 end
250 254
251 255 def label_for(field)
252 256 label = available_filters[field][:name] if available_filters.has_key?(field)
253 257 label ||= field.gsub(/\_id$/, "")
254 258 end
255 259
256 260 def available_columns
257 261 return @available_columns if @available_columns
258 262 @available_columns = Query.available_columns
259 263 @available_columns += (project ?
260 264 project.all_issue_custom_fields :
261 265 IssueCustomField.find(:all)
262 266 ).collect {|cf| QueryCustomFieldColumn.new(cf) }
263 267 end
264 268
265 269 # Returns an array of columns that can be used to group the results
266 270 def groupable_columns
267 271 available_columns.select {|c| c.groupable}
268 272 end
269 273
270 274 def columns
271 275 if has_default_columns?
272 276 available_columns.select do |c|
273 277 # Adds the project column by default for cross-project lists
274 278 Setting.issue_list_default_columns.include?(c.name.to_s) || (c.name == :project && project.nil?)
275 279 end
276 280 else
277 281 # preserve the column_names order
278 282 column_names.collect {|name| available_columns.find {|col| col.name == name}}.compact
279 283 end
280 284 end
281 285
282 286 def column_names=(names)
283 287 if names
284 288 names = names.select {|n| n.is_a?(Symbol) || !n.blank? }
285 289 names = names.collect {|n| n.is_a?(Symbol) ? n : n.to_sym }
286 290 # Set column_names to nil if default columns
287 291 if names.map(&:to_s) == Setting.issue_list_default_columns
288 292 names = nil
289 293 end
290 294 end
291 295 write_attribute(:column_names, names)
292 296 end
293 297
294 298 def has_column?(column)
295 299 column_names && column_names.include?(column.name)
296 300 end
297 301
298 302 def has_default_columns?
299 303 column_names.nil? || column_names.empty?
300 304 end
301 305
302 306 def sort_criteria=(arg)
303 307 c = []
304 308 if arg.is_a?(Hash)
305 309 arg = arg.keys.sort.collect {|k| arg[k]}
306 310 end
307 311 c = arg.select {|k,o| !k.to_s.blank?}.slice(0,3).collect {|k,o| [k.to_s, o == 'desc' ? o : 'asc']}
308 312 write_attribute(:sort_criteria, c)
309 313 end
310 314
311 315 def sort_criteria
312 316 read_attribute(:sort_criteria) || []
313 317 end
314 318
315 319 def sort_criteria_key(arg)
316 320 sort_criteria && sort_criteria[arg] && sort_criteria[arg].first
317 321 end
318 322
319 323 def sort_criteria_order(arg)
320 324 sort_criteria && sort_criteria[arg] && sort_criteria[arg].last
321 325 end
322 326
323 327 # Returns the SQL sort order that should be prepended for grouping
324 328 def group_by_sort_order
325 329 if grouped? && (column = group_by_column)
326 330 column.sortable.is_a?(Array) ?
327 331 column.sortable.collect {|s| "#{s} #{column.default_order}"}.join(',') :
328 332 "#{column.sortable} #{column.default_order}"
329 333 end
330 334 end
331 335
332 336 # Returns true if the query is a grouped query
333 337 def grouped?
334 338 !group_by.blank?
335 339 end
336 340
337 341 def group_by_column
338 342 groupable_columns.detect {|c| c.name.to_s == group_by}
339 343 end
340 344
341 345 def group_by_statement
342 346 group_by_column.groupable
343 347 end
344 348
345 349 def project_statement
346 350 project_clauses = []
347 351 if project && !@project.descendants.active.empty?
348 352 ids = [project.id]
349 353 if has_filter?("subproject_id")
350 354 case operator_for("subproject_id")
351 355 when '='
352 356 # include the selected subprojects
353 357 ids += values_for("subproject_id").each(&:to_i)
354 358 when '!*'
355 359 # main project only
356 360 else
357 361 # all subprojects
358 362 ids += project.descendants.collect(&:id)
359 363 end
360 364 elsif Setting.display_subprojects_issues?
361 365 ids += project.descendants.collect(&:id)
362 366 end
363 367 project_clauses << "#{Project.table_name}.id IN (%s)" % ids.join(',')
364 368 elsif project
365 369 project_clauses << "#{Project.table_name}.id = %d" % project.id
366 370 end
367 371 project_clauses << Project.allowed_to_condition(User.current, :view_issues)
368 372 project_clauses.join(' AND ')
369 373 end
370 374
371 375 def statement
372 376 # filters clauses
373 377 filters_clauses = []
374 378 filters.each_key do |field|
375 379 next if field == "subproject_id"
376 380 v = values_for(field).clone
377 381 next unless v and !v.empty?
378 382 operator = operator_for(field)
379 383
380 384 # "me" value subsitution
381 385 if %w(assigned_to_id author_id watcher_id).include?(field)
382 386 v.push(User.current.logged? ? User.current.id.to_s : "0") if v.delete("me")
383 387 end
384 388
385 389 sql = ''
386 390 if field =~ /^cf_(\d+)$/
387 391 # custom field
388 392 db_table = CustomValue.table_name
389 393 db_field = 'value'
390 394 is_custom_filter = true
391 395 sql << "#{Issue.table_name}.id IN (SELECT #{Issue.table_name}.id FROM #{Issue.table_name} LEFT OUTER JOIN #{db_table} ON #{db_table}.customized_type='Issue' AND #{db_table}.customized_id=#{Issue.table_name}.id AND #{db_table}.custom_field_id=#{$1} WHERE "
392 396 sql << sql_for_field(field, operator, v, db_table, db_field, true) + ')'
393 397 elsif field == 'watcher_id'
394 398 db_table = Watcher.table_name
395 399 db_field = 'user_id'
396 400 sql << "#{Issue.table_name}.id #{ operator == '=' ? 'IN' : 'NOT IN' } (SELECT #{db_table}.watchable_id FROM #{db_table} WHERE #{db_table}.watchable_type='Issue' AND "
397 401 sql << sql_for_field(field, '=', v, db_table, db_field) + ')'
398 402 else
399 403 # regular field
400 404 db_table = Issue.table_name
401 405 db_field = field
402 406 sql << '(' + sql_for_field(field, operator, v, db_table, db_field) + ')'
403 407 end
404 408 filters_clauses << sql
405 409
406 410 end if filters and valid?
407 411
408 412 (filters_clauses << project_statement).join(' AND ')
409 413 end
410 414
411 415 # Returns the issue count
412 416 def issue_count
413 417 Issue.count(:include => [:status, :project], :conditions => statement)
414 418 rescue ::ActiveRecord::StatementInvalid => e
415 419 raise StatementInvalid.new(e.message)
416 420 end
417 421
418 422 # Returns the issue count by group or nil if query is not grouped
419 423 def issue_count_by_group
420 424 r = nil
421 425 if grouped?
422 426 begin
423 427 # Rails will raise an (unexpected) RecordNotFound if there's only a nil group value
424 428 r = Issue.count(:group => group_by_statement, :include => [:status, :project], :conditions => statement)
425 429 rescue ActiveRecord::RecordNotFound
426 430 r = {nil => issue_count}
427 431 end
428 432 c = group_by_column
429 433 if c.is_a?(QueryCustomFieldColumn)
430 434 r = r.keys.inject({}) {|h, k| h[c.custom_field.cast_value(k)] = r[k]; h}
431 435 end
432 436 end
433 437 r
434 438 rescue ::ActiveRecord::StatementInvalid => e
435 439 raise StatementInvalid.new(e.message)
436 440 end
437 441
438 442 # Returns the issues
439 443 # Valid options are :order, :offset, :limit, :include, :conditions
440 444 def issues(options={})
441 445 order_option = [group_by_sort_order, options[:order]].reject {|s| s.blank?}.join(',')
442 446 order_option = nil if order_option.blank?
443 447
444 448 Issue.find :all, :include => ([:status, :project] + (options[:include] || [])).uniq,
445 449 :conditions => Query.merge_conditions(statement, options[:conditions]),
446 450 :order => order_option,
447 451 :limit => options[:limit],
448 452 :offset => options[:offset]
449 453 rescue ::ActiveRecord::StatementInvalid => e
450 454 raise StatementInvalid.new(e.message)
451 455 end
452 456
453 457 # Returns the journals
454 458 # Valid options are :order, :offset, :limit
455 459 def journals(options={})
456 460 Journal.find :all, :include => [:details, :user, {:issue => [:project, :author, :tracker, :status]}],
457 461 :conditions => statement,
458 462 :order => options[:order],
459 463 :limit => options[:limit],
460 464 :offset => options[:offset]
461 465 rescue ::ActiveRecord::StatementInvalid => e
462 466 raise StatementInvalid.new(e.message)
463 467 end
464 468
465 469 # Returns the versions
466 470 # Valid options are :conditions
467 471 def versions(options={})
468 472 Version.find :all, :include => :project,
469 473 :conditions => Query.merge_conditions(project_statement, options[:conditions])
470 474 rescue ::ActiveRecord::StatementInvalid => e
471 475 raise StatementInvalid.new(e.message)
472 476 end
473 477
474 478 private
475 479
476 480 # Helper method to generate the WHERE sql for a +field+, +operator+ and a +value+
477 481 def sql_for_field(field, operator, value, db_table, db_field, is_custom_filter=false)
478 482 sql = ''
479 483 case operator
480 484 when "="
481 485 sql = "#{db_table}.#{db_field} IN (" + value.collect{|val| "'#{connection.quote_string(val)}'"}.join(",") + ")"
482 486 when "!"
483 487 sql = "(#{db_table}.#{db_field} IS NULL OR #{db_table}.#{db_field} NOT IN (" + value.collect{|val| "'#{connection.quote_string(val)}'"}.join(",") + "))"
484 488 when "!*"
485 489 sql = "#{db_table}.#{db_field} IS NULL"
486 490 sql << " OR #{db_table}.#{db_field} = ''" if is_custom_filter
487 491 when "*"
488 492 sql = "#{db_table}.#{db_field} IS NOT NULL"
489 493 sql << " AND #{db_table}.#{db_field} <> ''" if is_custom_filter
490 494 when ">="
491 495 sql = "#{db_table}.#{db_field} >= #{value.first.to_i}"
492 496 when "<="
493 497 sql = "#{db_table}.#{db_field} <= #{value.first.to_i}"
494 498 when "o"
495 499 sql = "#{IssueStatus.table_name}.is_closed=#{connection.quoted_false}" if field == "status_id"
496 500 when "c"
497 501 sql = "#{IssueStatus.table_name}.is_closed=#{connection.quoted_true}" if field == "status_id"
498 502 when ">t-"
499 503 sql = date_range_clause(db_table, db_field, - value.first.to_i, 0)
500 504 when "<t-"
501 505 sql = date_range_clause(db_table, db_field, nil, - value.first.to_i)
502 506 when "t-"
503 507 sql = date_range_clause(db_table, db_field, - value.first.to_i, - value.first.to_i)
504 508 when ">t+"
505 509 sql = date_range_clause(db_table, db_field, value.first.to_i, nil)
506 510 when "<t+"
507 511 sql = date_range_clause(db_table, db_field, 0, value.first.to_i)
508 512 when "t+"
509 513 sql = date_range_clause(db_table, db_field, value.first.to_i, value.first.to_i)
510 514 when "t"
511 515 sql = date_range_clause(db_table, db_field, 0, 0)
512 516 when "w"
513 517 from = l(:general_first_day_of_week) == '7' ?
514 518 # week starts on sunday
515 519 ((Date.today.cwday == 7) ? Time.now.at_beginning_of_day : Time.now.at_beginning_of_week - 1.day) :
516 520 # week starts on monday (Rails default)
517 521 Time.now.at_beginning_of_week
518 522 sql = "#{db_table}.#{db_field} BETWEEN '%s' AND '%s'" % [connection.quoted_date(from), connection.quoted_date(from + 7.days)]
519 523 when "~"
520 524 sql = "LOWER(#{db_table}.#{db_field}) LIKE '%#{connection.quote_string(value.first.to_s.downcase)}%'"
521 525 when "!~"
522 526 sql = "LOWER(#{db_table}.#{db_field}) NOT LIKE '%#{connection.quote_string(value.first.to_s.downcase)}%'"
523 527 end
524 528
525 529 return sql
526 530 end
527 531
528 532 def add_custom_fields_filters(custom_fields)
529 533 @available_filters ||= {}
530 534
531 535 custom_fields.select(&:is_filter?).each do |field|
532 536 case field.field_format
533 537 when "text"
534 538 options = { :type => :text, :order => 20 }
535 539 when "list"
536 540 options = { :type => :list_optional, :values => field.possible_values, :order => 20}
537 541 when "date"
538 542 options = { :type => :date, :order => 20 }
539 543 when "bool"
540 544 options = { :type => :list, :values => [[l(:general_text_yes), "1"], [l(:general_text_no), "0"]], :order => 20 }
541 545 else
542 546 options = { :type => :string, :order => 20 }
543 547 end
544 548 @available_filters["cf_#{field.id}"] = options.merge({ :name => field.name })
545 549 end
546 550 end
547 551
548 552 # Returns a SQL clause for a date or datetime field.
549 553 def date_range_clause(table, field, from, to)
550 554 s = []
551 555 if from
552 556 s << ("#{table}.#{field} > '%s'" % [connection.quoted_date((Date.yesterday + from).to_time.end_of_day)])
553 557 end
554 558 if to
555 559 s << ("#{table}.#{field} <= '%s'" % [connection.quoted_date((Date.today + to).to_time.end_of_day)])
556 560 end
557 561 s.join(' AND ')
558 562 end
559 563 end
Show file before | Show file after | Hide comments
@@ -1,347 +1,354
1 1 # redMine - project management software
2 2 # Copyright (C) 2006-2008 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 require File.dirname(__FILE__) + '/../test_helper'
19 19
20 20 class QueryTest < ActiveSupport::TestCase
21 21 fixtures :projects, :enabled_modules, :users, :members, :member_roles, :roles, :trackers, :issue_statuses, :issue_categories, :enumerations, :issues, :watchers, :custom_fields, :custom_values, :versions, :queries
22 22
23 23 def test_custom_fields_for_all_projects_should_be_available_in_global_queries
24 24 query = Query.new(:project => nil, :name => '_')
25 25 assert query.available_filters.has_key?('cf_1')
26 26 assert !query.available_filters.has_key?('cf_3')
27 27 end
28 28
29 def test_system_shared_versions_should_be_available_in_global_queries
30 Version.find(2).update_attribute :sharing, 'system'
31 query = Query.new(:project => nil, :name => '_')
32 assert query.available_filters.has_key?('fixed_version_id')
33 assert query.available_filters['fixed_version_id'][:values].detect {|v| v.last == '2'}
34 end
35
29 36 def find_issues_with_query(query)
30 37 Issue.find :all,
31 38 :include => [ :assigned_to, :status, :tracker, :project, :priority ],
32 39 :conditions => query.statement
33 40 end
34 41
35 42 def test_query_should_allow_shared_versions_for_a_project_query
36 43 subproject_version = Version.find(4)
37 44 query = Query.new(:project => Project.find(1), :name => '_')
38 45 query.add_filter('fixed_version_id', '=', [subproject_version.id.to_s])
39 46
40 47 assert query.statement.include?("#{Issue.table_name}.fixed_version_id IN ('4')")
41 48 end
42 49
43 50 def test_query_with_multiple_custom_fields
44 51 query = Query.find(1)
45 52 assert query.valid?
46 53 assert query.statement.include?("#{CustomValue.table_name}.value IN ('MySQL')")
47 54 issues = find_issues_with_query(query)
48 55 assert_equal 1, issues.length
49 56 assert_equal Issue.find(3), issues.first
50 57 end
51 58
52 59 def test_operator_none
53 60 query = Query.new(:project => Project.find(1), :name => '_')
54 61 query.add_filter('fixed_version_id', '!*', [''])
55 62 query.add_filter('cf_1', '!*', [''])
56 63 assert query.statement.include?("#{Issue.table_name}.fixed_version_id IS NULL")
57 64 assert query.statement.include?("#{CustomValue.table_name}.value IS NULL OR #{CustomValue.table_name}.value = ''")
58 65 find_issues_with_query(query)
59 66 end
60 67
61 68 def test_operator_none_for_integer
62 69 query = Query.new(:project => Project.find(1), :name => '_')
63 70 query.add_filter('estimated_hours', '!*', [''])
64 71 issues = find_issues_with_query(query)
65 72 assert !issues.empty?
66 73 assert issues.all? {|i| !i.estimated_hours}
67 74 end
68 75
69 76 def test_operator_all
70 77 query = Query.new(:project => Project.find(1), :name => '_')
71 78 query.add_filter('fixed_version_id', '*', [''])
72 79 query.add_filter('cf_1', '*', [''])
73 80 assert query.statement.include?("#{Issue.table_name}.fixed_version_id IS NOT NULL")
74 81 assert query.statement.include?("#{CustomValue.table_name}.value IS NOT NULL AND #{CustomValue.table_name}.value <> ''")
75 82 find_issues_with_query(query)
76 83 end
77 84
78 85 def test_operator_greater_than
79 86 query = Query.new(:project => Project.find(1), :name => '_')
80 87 query.add_filter('done_ratio', '>=', ['40'])
81 88 assert query.statement.include?("#{Issue.table_name}.done_ratio >= 40")
82 89 find_issues_with_query(query)
83 90 end
84 91
85 92 def test_operator_in_more_than
86 93 Issue.find(7).update_attribute(:due_date, (Date.today + 15))
87 94 query = Query.new(:project => Project.find(1), :name => '_')
88 95 query.add_filter('due_date', '>t+', ['15'])
89 96 issues = find_issues_with_query(query)
90 97 assert !issues.empty?
91 98 issues.each {|issue| assert(issue.due_date >= (Date.today + 15))}
92 99 end
93 100
94 101 def test_operator_in_less_than
95 102 query = Query.new(:project => Project.find(1), :name => '_')
96 103 query.add_filter('due_date', '<t+', ['15'])
97 104 issues = find_issues_with_query(query)
98 105 assert !issues.empty?
99 106 issues.each {|issue| assert(issue.due_date >= Date.today && issue.due_date <= (Date.today + 15))}
100 107 end
101 108
102 109 def test_operator_less_than_ago
103 110 Issue.find(7).update_attribute(:due_date, (Date.today - 3))
104 111 query = Query.new(:project => Project.find(1), :name => '_')
105 112 query.add_filter('due_date', '>t-', ['3'])
106 113 issues = find_issues_with_query(query)
107 114 assert !issues.empty?
108 115 issues.each {|issue| assert(issue.due_date >= (Date.today - 3) && issue.due_date <= Date.today)}
109 116 end
110 117
111 118 def test_operator_more_than_ago
112 119 Issue.find(7).update_attribute(:due_date, (Date.today - 10))
113 120 query = Query.new(:project => Project.find(1), :name => '_')
114 121 query.add_filter('due_date', '<t-', ['10'])
115 122 assert query.statement.include?("#{Issue.table_name}.due_date <=")
116 123 issues = find_issues_with_query(query)
117 124 assert !issues.empty?
118 125 issues.each {|issue| assert(issue.due_date <= (Date.today - 10))}
119 126 end
120 127
121 128 def test_operator_in
122 129 Issue.find(7).update_attribute(:due_date, (Date.today + 2))
123 130 query = Query.new(:project => Project.find(1), :name => '_')
124 131 query.add_filter('due_date', 't+', ['2'])
125 132 issues = find_issues_with_query(query)
126 133 assert !issues.empty?
127 134 issues.each {|issue| assert_equal((Date.today + 2), issue.due_date)}
128 135 end
129 136
130 137 def test_operator_ago
131 138 Issue.find(7).update_attribute(:due_date, (Date.today - 3))
132 139 query = Query.new(:project => Project.find(1), :name => '_')
133 140 query.add_filter('due_date', 't-', ['3'])
134 141 issues = find_issues_with_query(query)
135 142 assert !issues.empty?
136 143 issues.each {|issue| assert_equal((Date.today - 3), issue.due_date)}
137 144 end
138 145
139 146 def test_operator_today
140 147 query = Query.new(:project => Project.find(1), :name => '_')
141 148 query.add_filter('due_date', 't', [''])
142 149 issues = find_issues_with_query(query)
143 150 assert !issues.empty?
144 151 issues.each {|issue| assert_equal Date.today, issue.due_date}
145 152 end
146 153
147 154 def test_operator_this_week_on_date
148 155 query = Query.new(:project => Project.find(1), :name => '_')
149 156 query.add_filter('due_date', 'w', [''])
150 157 find_issues_with_query(query)
151 158 end
152 159
153 160 def test_operator_this_week_on_datetime
154 161 query = Query.new(:project => Project.find(1), :name => '_')
155 162 query.add_filter('created_on', 'w', [''])
156 163 find_issues_with_query(query)
157 164 end
158 165
159 166 def test_operator_contains
160 167 query = Query.new(:project => Project.find(1), :name => '_')
161 168 query.add_filter('subject', '~', ['uNable'])
162 169 assert query.statement.include?("LOWER(#{Issue.table_name}.subject) LIKE '%unable%'")
163 170 result = find_issues_with_query(query)
164 171 assert result.empty?
165 172 result.each {|issue| assert issue.subject.downcase.include?('unable') }
166 173 end
167 174
168 175 def test_operator_does_not_contains
169 176 query = Query.new(:project => Project.find(1), :name => '_')
170 177 query.add_filter('subject', '!~', ['uNable'])
171 178 assert query.statement.include?("LOWER(#{Issue.table_name}.subject) NOT LIKE '%unable%'")
172 179 find_issues_with_query(query)
173 180 end
174 181
175 182 def test_filter_watched_issues
176 183 User.current = User.find(1)
177 184 query = Query.new(:name => '_', :filters => { 'watcher_id' => {:operator => '=', :values => ['me']}})
178 185 result = find_issues_with_query(query)
179 186 assert_not_nil result
180 187 assert !result.empty?
181 188 assert_equal Issue.visible.watched_by(User.current).sort_by(&:id), result.sort_by(&:id)
182 189 User.current = nil
183 190 end
184 191
185 192 def test_filter_unwatched_issues
186 193 User.current = User.find(1)
187 194 query = Query.new(:name => '_', :filters => { 'watcher_id' => {:operator => '!', :values => ['me']}})
188 195 result = find_issues_with_query(query)
189 196 assert_not_nil result
190 197 assert !result.empty?
191 198 assert_equal((Issue.visible - Issue.watched_by(User.current)).sort_by(&:id).size, result.sort_by(&:id).size)
192 199 User.current = nil
193 200 end
194 201
195 202 def test_default_columns
196 203 q = Query.new
197 204 assert !q.columns.empty?
198 205 end
199 206
200 207 def test_set_column_names
201 208 q = Query.new
202 209 q.column_names = ['tracker', :subject, '', 'unknonw_column']
203 210 assert_equal [:tracker, :subject], q.columns.collect {|c| c.name}
204 211 c = q.columns.first
205 212 assert q.has_column?(c)
206 213 end
207 214
208 215 def test_groupable_columns_should_include_custom_fields
209 216 q = Query.new
210 217 assert q.groupable_columns.detect {|c| c.is_a? QueryCustomFieldColumn}
211 218 end
212 219
213 220 def test_default_sort
214 221 q = Query.new
215 222 assert_equal [], q.sort_criteria
216 223 end
217 224
218 225 def test_set_sort_criteria_with_hash
219 226 q = Query.new
220 227 q.sort_criteria = {'0' => ['priority', 'desc'], '2' => ['tracker']}
221 228 assert_equal [['priority', 'desc'], ['tracker', 'asc']], q.sort_criteria
222 229 end
223 230
224 231 def test_set_sort_criteria_with_array
225 232 q = Query.new
226 233 q.sort_criteria = [['priority', 'desc'], 'tracker']
227 234 assert_equal [['priority', 'desc'], ['tracker', 'asc']], q.sort_criteria
228 235 end
229 236
230 237 def test_create_query_with_sort
231 238 q = Query.new(:name => 'Sorted')
232 239 q.sort_criteria = [['priority', 'desc'], 'tracker']
233 240 assert q.save
234 241 q.reload
235 242 assert_equal [['priority', 'desc'], ['tracker', 'asc']], q.sort_criteria
236 243 end
237 244
238 245 def test_sort_by_string_custom_field_asc
239 246 q = Query.new
240 247 c = q.available_columns.find {|col| col.is_a?(QueryCustomFieldColumn) && col.custom_field.field_format == 'string' }
241 248 assert c
242 249 assert c.sortable
243 250 issues = Issue.find :all,
244 251 :include => [ :assigned_to, :status, :tracker, :project, :priority ],
245 252 :conditions => q.statement,
246 253 :order => "#{c.sortable} ASC"
247 254 values = issues.collect {|i| i.custom_value_for(c.custom_field).to_s}
248 255 assert !values.empty?
249 256 assert_equal values.sort, values
250 257 end
251 258
252 259 def test_sort_by_string_custom_field_desc
253 260 q = Query.new
254 261 c = q.available_columns.find {|col| col.is_a?(QueryCustomFieldColumn) && col.custom_field.field_format == 'string' }
255 262 assert c
256 263 assert c.sortable
257 264 issues = Issue.find :all,
258 265 :include => [ :assigned_to, :status, :tracker, :project, :priority ],
259 266 :conditions => q.statement,
260 267 :order => "#{c.sortable} DESC"
261 268 values = issues.collect {|i| i.custom_value_for(c.custom_field).to_s}
262 269 assert !values.empty?
263 270 assert_equal values.sort.reverse, values
264 271 end
265 272
266 273 def test_sort_by_float_custom_field_asc
267 274 q = Query.new
268 275 c = q.available_columns.find {|col| col.is_a?(QueryCustomFieldColumn) && col.custom_field.field_format == 'float' }
269 276 assert c
270 277 assert c.sortable
271 278 issues = Issue.find :all,
272 279 :include => [ :assigned_to, :status, :tracker, :project, :priority ],
273 280 :conditions => q.statement,
274 281 :order => "#{c.sortable} ASC"
275 282 values = issues.collect {|i| begin; Kernel.Float(i.custom_value_for(c.custom_field).to_s); rescue; nil; end}.compact
276 283 assert !values.empty?
277 284 assert_equal values.sort, values
278 285 end
279 286
280 287 def test_invalid_query_should_raise_query_statement_invalid_error
281 288 q = Query.new
282 289 assert_raise Query::StatementInvalid do
283 290 q.issues(:conditions => "foo = 1")
284 291 end
285 292 end
286 293
287 294 def test_issue_count_by_association_group
288 295 q = Query.new(:name => '_', :group_by => 'assigned_to')
289 296 count_by_group = q.issue_count_by_group
290 297 assert_kind_of Hash, count_by_group
291 298 assert_equal %w(NilClass User), count_by_group.keys.collect {|k| k.class.name}.uniq.sort
292 299 assert_equal %w(Fixnum), count_by_group.values.collect {|k| k.class.name}.uniq
293 300 assert count_by_group.has_key?(User.find(3))
294 301 end
295 302
296 303 def test_issue_count_by_list_custom_field_group
297 304 q = Query.new(:name => '_', :group_by => 'cf_1')
298 305 count_by_group = q.issue_count_by_group
299 306 assert_kind_of Hash, count_by_group
300 307 assert_equal %w(NilClass String), count_by_group.keys.collect {|k| k.class.name}.uniq.sort
301 308 assert_equal %w(Fixnum), count_by_group.values.collect {|k| k.class.name}.uniq
302 309 assert count_by_group.has_key?('MySQL')
303 310 end
304 311
305 312 def test_issue_count_by_date_custom_field_group
306 313 q = Query.new(:name => '_', :group_by => 'cf_8')
307 314 count_by_group = q.issue_count_by_group
308 315 assert_kind_of Hash, count_by_group
309 316 assert_equal %w(Date NilClass), count_by_group.keys.collect {|k| k.class.name}.uniq.sort
310 317 assert_equal %w(Fixnum), count_by_group.values.collect {|k| k.class.name}.uniq
311 318 end
312 319
313 320 def test_label_for
314 321 q = Query.new
315 322 assert_equal 'assigned_to', q.label_for('assigned_to_id')
316 323 end
317 324
318 325 def test_editable_by
319 326 admin = User.find(1)
320 327 manager = User.find(2)
321 328 developer = User.find(3)
322 329
323 330 # Public query on project 1
324 331 q = Query.find(1)
325 332 assert q.editable_by?(admin)
326 333 assert q.editable_by?(manager)
327 334 assert !q.editable_by?(developer)
328 335
329 336 # Private query on project 1
330 337 q = Query.find(2)
331 338 assert q.editable_by?(admin)
332 339 assert !q.editable_by?(manager)
333 340 assert q.editable_by?(developer)
334 341
335 342 # Private query for all projects
336 343 q = Query.find(3)
337 344 assert q.editable_by?(admin)
338 345 assert !q.editable_by?(manager)
339 346 assert q.editable_by?(developer)
340 347
341 348 # Public query for all projects
342 349 q = Query.find(4)
343 350 assert q.editable_by?(admin)
344 351 assert !q.editable_by?(manager)
345 352 assert !q.editable_by?(developer)
346 353 end
347 354 end
General Comments 0
You need to be logged in to leave comments. Login now