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