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