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