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