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