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