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