##// END OF EJS Templates
jro_apps » redmine
RSS
Rails3: replace deprecated 'validate' method at Query model...
Toshi MARUYAMA -
r7305:5c46cede2b94
parent child
Show More
Side by Side Unified
Context file:
r7305:5c46cede2b94 Loading diff...:
Show file before | Show file after | Hide comments
@@ -1,780 +1,781
1 1 # Redmine - project management software
2 2 # Copyright (C) 2006-2011 Jean-Philippe Lang
3 3 #
4 4 # This program is free software; you can redistribute it and/or
5 5 # modify it under the terms of the GNU General Public License
6 6 # as published by the Free Software Foundation; either version 2
7 7 # of the License, or (at your option) any later version.
8 8 #
9 9 # This program is distributed in the hope that it will be useful,
10 10 # but WITHOUT ANY WARRANTY; without even the implied warranty of
11 11 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
12 12 # GNU General Public License for more details.
13 13 #
14 14 # You should have received a copy of the GNU General Public License
15 15 # along with this program; if not, write to the Free Software
16 16 # Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
17 17
18 18 class QueryColumn
19 19 attr_accessor :name, :sortable, :groupable, :default_order
20 20 include Redmine::I18n
21 21
22 22 def initialize(name, options={})
23 23 self.name = name
24 24 self.sortable = options[:sortable]
25 25 self.groupable = options[:groupable] || false
26 26 if groupable == true
27 27 self.groupable = name.to_s
28 28 end
29 29 self.default_order = options[:default_order]
30 30 @caption_key = options[:caption] || "field_#{name}"
31 31 end
32 32
33 33 def caption
34 34 l(@caption_key)
35 35 end
36 36
37 37 # Returns true if the column is sortable, otherwise false
38 38 def sortable?
39 39 !sortable.nil?
40 40 end
41 41
42 42 def value(issue)
43 43 issue.send name
44 44 end
45 45
46 46 def css_classes
47 47 name
48 48 end
49 49 end
50 50
51 51 class QueryCustomFieldColumn < QueryColumn
52 52
53 53 def initialize(custom_field)
54 54 self.name = "cf_#{custom_field.id}".to_sym
55 55 self.sortable = custom_field.order_statement || false
56 56 if %w(list date bool int).include?(custom_field.field_format)
57 57 self.groupable = custom_field.order_statement
58 58 end
59 59 self.groupable ||= false
60 60 @cf = custom_field
61 61 end
62 62
63 63 def caption
64 64 @cf.name
65 65 end
66 66
67 67 def custom_field
68 68 @cf
69 69 end
70 70
71 71 def value(issue)
72 72 cv = issue.custom_values.detect {|v| v.custom_field_id == @cf.id}
73 73 cv && @cf.cast_value(cv.value)
74 74 end
75 75
76 76 def css_classes
77 77 @css_classes ||= "#{name} #{@cf.field_format}"
78 78 end
79 79 end
80 80
81 81 class Query < ActiveRecord::Base
82 82 class StatementInvalid < ::ActiveRecord::StatementInvalid
83 83 end
84 84
85 85 belongs_to :project
86 86 belongs_to :user
87 87 serialize :filters
88 88 serialize :column_names
89 89 serialize :sort_criteria, Array
90 90
91 91 attr_protected :project_id, :user_id
92 92
93 93 validates_presence_of :name, :on => :save
94 94 validates_length_of :name, :maximum => 255
95 validate :validate_query_filters
95 96
96 97 @@operators = { "=" => :label_equals,
97 98 "!" => :label_not_equals,
98 99 "o" => :label_open_issues,
99 100 "c" => :label_closed_issues,
100 101 "!*" => :label_none,
101 102 "*" => :label_all,
102 103 ">=" => :label_greater_or_equal,
103 104 "<=" => :label_less_or_equal,
104 105 "><" => :label_between,
105 106 "<t+" => :label_in_less_than,
106 107 ">t+" => :label_in_more_than,
107 108 "t+" => :label_in,
108 109 "t" => :label_today,
109 110 "w" => :label_this_week,
110 111 ">t-" => :label_less_than_ago,
111 112 "<t-" => :label_more_than_ago,
112 113 "t-" => :label_ago,
113 114 "~" => :label_contains,
114 115 "!~" => :label_not_contains }
115 116
116 117 cattr_reader :operators
117 118
118 119 @@operators_by_filter_type = { :list => [ "=", "!" ],
119 120 :list_status => [ "o", "=", "!", "c", "*" ],
120 121 :list_optional => [ "=", "!", "!*", "*" ],
121 122 :list_subprojects => [ "*", "!*", "=" ],
122 123 :date => [ "=", ">=", "<=", "><", "<t+", ">t+", "t+", "t", "w", ">t-", "<t-", "t-" ],
123 124 :date_past => [ "=", ">=", "<=", "><", ">t-", "<t-", "t-", "t", "w" ],
124 125 :string => [ "=", "~", "!", "!~" ],
125 126 :text => [ "~", "!~" ],
126 127 :integer => [ "=", ">=", "<=", "><", "!*", "*" ],
127 128 :float => [ "=", ">=", "<=", "><", "!*", "*" ] }
128 129
129 130 cattr_reader :operators_by_filter_type
130 131
131 132 @@available_columns = [
132 133 QueryColumn.new(:project, :sortable => "#{Project.table_name}.name", :groupable => true),
133 134 QueryColumn.new(:tracker, :sortable => "#{Tracker.table_name}.position", :groupable => true),
134 135 QueryColumn.new(:parent, :sortable => ["#{Issue.table_name}.root_id", "#{Issue.table_name}.lft ASC"], :default_order => 'desc', :caption => :field_parent_issue),
135 136 QueryColumn.new(:status, :sortable => "#{IssueStatus.table_name}.position", :groupable => true),
136 137 QueryColumn.new(:priority, :sortable => "#{IssuePriority.table_name}.position", :default_order => 'desc', :groupable => true),
137 138 QueryColumn.new(:subject, :sortable => "#{Issue.table_name}.subject"),
138 139 QueryColumn.new(:author),
139 140 QueryColumn.new(:assigned_to, :sortable => ["#{User.table_name}.lastname", "#{User.table_name}.firstname", "#{User.table_name}.id"], :groupable => true),
140 141 QueryColumn.new(:updated_on, :sortable => "#{Issue.table_name}.updated_on", :default_order => 'desc'),
141 142 QueryColumn.new(:category, :sortable => "#{IssueCategory.table_name}.name", :groupable => true),
142 143 QueryColumn.new(:fixed_version, :sortable => ["#{Version.table_name}.effective_date", "#{Version.table_name}.name"], :default_order => 'desc', :groupable => true),
143 144 QueryColumn.new(:start_date, :sortable => "#{Issue.table_name}.start_date"),
144 145 QueryColumn.new(:due_date, :sortable => "#{Issue.table_name}.due_date"),
145 146 QueryColumn.new(:estimated_hours, :sortable => "#{Issue.table_name}.estimated_hours"),
146 147 QueryColumn.new(:done_ratio, :sortable => "#{Issue.table_name}.done_ratio", :groupable => true),
147 148 QueryColumn.new(:created_on, :sortable => "#{Issue.table_name}.created_on", :default_order => 'desc'),
148 149 ]
149 150 cattr_reader :available_columns
150 151
151 152 named_scope :visible, lambda {|*args|
152 153 user = args.shift || User.current
153 154 base = Project.allowed_to_condition(user, :view_issues, *args)
154 155 user_id = user.logged? ? user.id : 0
155 156 {
156 157 :conditions => ["(#{table_name}.project_id IS NULL OR (#{base})) AND (#{table_name}.is_public = ? OR #{table_name}.user_id = ?)", true, user_id],
157 158 :include => :project
158 159 }
159 160 }
160 161
161 162 def initialize(attributes = nil)
162 163 super attributes
163 164 self.filters ||= { 'status_id' => {:operator => "o", :values => [""]} }
164 165 end
165 166
166 167 def after_initialize
167 168 # Store the fact that project is nil (used in #editable_by?)
168 169 @is_for_all = project.nil?
169 170 end
170 171
171 def validate
172 def validate_query_filters
172 173 filters.each_key do |field|
173 174 if values_for(field)
174 175 case type_for(field)
175 176 when :integer
176 177 errors.add(label_for(field), :invalid) if values_for(field).detect {|v| v.present? && !v.match(/^\d+$/) }
177 178 when :float
178 179 errors.add(label_for(field), :invalid) if values_for(field).detect {|v| v.present? && !v.match(/^\d+(\.\d*)?$/) }
179 180 when :date, :date_past
180 181 case operator_for(field)
181 182 when "=", ">=", "<=", "><"
182 183 errors.add(label_for(field), :invalid) if values_for(field).detect {|v| v.present? && (!v.match(/^\d{4}-\d{2}-\d{2}$/) || (Date.parse(v) rescue nil).nil?) }
183 184 when ">t-", "<t-", "t-"
184 185 errors.add(label_for(field), :invalid) if values_for(field).detect {|v| v.present? && !v.match(/^\d+$/) }
185 186 end
186 187 end
187 188 end
188 189
189 190 errors.add label_for(field), :blank unless
190 191 # filter requires one or more values
191 192 (values_for(field) and !values_for(field).first.blank?) or
192 193 # filter doesn't require any value
193 194 ["o", "c", "!*", "*", "t", "w"].include? operator_for(field)
194 195 end if filters
195 196 end
196 197
197 198 # Returns true if the query is visible to +user+ or the current user.
198 199 def visible?(user=User.current)
199 200 (project.nil? || user.allowed_to?(:view_issues, project)) && (self.is_public? || self.user_id == user.id)
200 201 end
201 202
202 203 def editable_by?(user)
203 204 return false unless user
204 205 # Admin can edit them all and regular users can edit their private queries
205 206 return true if user.admin? || (!is_public && self.user_id == user.id)
206 207 # Members can not edit public queries that are for all project (only admin is allowed to)
207 208 is_public && !@is_for_all && user.allowed_to?(:manage_public_queries, project)
208 209 end
209 210
210 211 def available_filters
211 212 return @available_filters if @available_filters
212 213
213 214 trackers = project.nil? ? Tracker.find(:all, :order => 'position') : project.rolled_up_trackers
214 215
215 216 @available_filters = { "status_id" => { :type => :list_status, :order => 1, :values => IssueStatus.find(:all, :order => 'position').collect{|s| [s.name, s.id.to_s] } },
216 217 "tracker_id" => { :type => :list, :order => 2, :values => trackers.collect{|s| [s.name, s.id.to_s] } },
217 218 "priority_id" => { :type => :list, :order => 3, :values => IssuePriority.all.collect{|s| [s.name, s.id.to_s] } },
218 219 "subject" => { :type => :text, :order => 8 },
219 220 "created_on" => { :type => :date_past, :order => 9 },
220 221 "updated_on" => { :type => :date_past, :order => 10 },
221 222 "start_date" => { :type => :date, :order => 11 },
222 223 "due_date" => { :type => :date, :order => 12 },
223 224 "estimated_hours" => { :type => :float, :order => 13 },
224 225 "done_ratio" => { :type => :integer, :order => 14 }}
225 226
226 227 principals = []
227 228 if project
228 229 principals += project.principals.sort
229 230 else
230 231 all_projects = Project.visible.all
231 232 if all_projects.any?
232 233 # members of visible projects
233 234 principals += Principal.active.find(:all, :conditions => ["#{User.table_name}.id IN (SELECT DISTINCT user_id FROM members WHERE project_id IN (?))", all_projects.collect(&:id)]).sort
234 235
235 236 # project filter
236 237 project_values = []
237 238 Project.project_tree(all_projects) do |p, level|
238 239 prefix = (level > 0 ? ('--' * level + ' ') : '')
239 240 project_values << ["#{prefix}#{p.name}", p.id.to_s]
240 241 end
241 242 @available_filters["project_id"] = { :type => :list, :order => 1, :values => project_values} unless project_values.empty?
242 243 end
243 244 end
244 245 users = principals.select {|p| p.is_a?(User)}
245 246
246 247 assigned_to_values = []
247 248 assigned_to_values << ["<< #{l(:label_me)} >>", "me"] if User.current.logged?
248 249 assigned_to_values += (Setting.issue_group_assignment? ? principals : users).collect{|s| [s.name, s.id.to_s] }
249 250 @available_filters["assigned_to_id"] = { :type => :list_optional, :order => 4, :values => assigned_to_values } unless assigned_to_values.empty?
250 251
251 252 author_values = []
252 253 author_values << ["<< #{l(:label_me)} >>", "me"] if User.current.logged?
253 254 author_values += users.collect{|s| [s.name, s.id.to_s] }
254 255 @available_filters["author_id"] = { :type => :list, :order => 5, :values => author_values } unless author_values.empty?
255 256
256 257 group_values = Group.all.collect {|g| [g.name, g.id.to_s] }
257 258 @available_filters["member_of_group"] = { :type => :list_optional, :order => 6, :values => group_values } unless group_values.empty?
258 259
259 260 role_values = Role.givable.collect {|r| [r.name, r.id.to_s] }
260 261 @available_filters["assigned_to_role"] = { :type => :list_optional, :order => 7, :values => role_values } unless role_values.empty?
261 262
262 263 if User.current.logged?
263 264 @available_filters["watcher_id"] = { :type => :list, :order => 15, :values => [["<< #{l(:label_me)} >>", "me"]] }
264 265 end
265 266
266 267 if project
267 268 # project specific filters
268 269 categories = @project.issue_categories.all
269 270 unless categories.empty?
270 271 @available_filters["category_id"] = { :type => :list_optional, :order => 6, :values => categories.collect{|s| [s.name, s.id.to_s] } }
271 272 end
272 273 versions = @project.shared_versions.all
273 274 unless versions.empty?
274 275 @available_filters["fixed_version_id"] = { :type => :list_optional, :order => 7, :values => versions.sort.collect{|s| ["#{s.project.name} - #{s.name}", s.id.to_s] } }
275 276 end
276 277 unless @project.leaf?
277 278 subprojects = @project.descendants.visible.all
278 279 unless subprojects.empty?
279 280 @available_filters["subproject_id"] = { :type => :list_subprojects, :order => 13, :values => subprojects.collect{|s| [s.name, s.id.to_s] } }
280 281 end
281 282 end
282 283 add_custom_fields_filters(@project.all_issue_custom_fields)
283 284 else
284 285 # global filters for cross project issue list
285 286 system_shared_versions = Version.visible.find_all_by_sharing('system')
286 287 unless system_shared_versions.empty?
287 288 @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] } }
288 289 end
289 290 add_custom_fields_filters(IssueCustomField.find(:all, :conditions => {:is_filter => true, :is_for_all => true}))
290 291 end
291 292 @available_filters
292 293 end
293 294
294 295 def add_filter(field, operator, values)
295 296 # values must be an array
296 297 return unless values.nil? || values.is_a?(Array)
297 298 # check if field is defined as an available filter
298 299 if available_filters.has_key? field
299 300 filter_options = available_filters[field]
300 301 # check if operator is allowed for that filter
301 302 #if @@operators_by_filter_type[filter_options[:type]].include? operator
302 303 # allowed_values = values & ([""] + (filter_options[:values] || []).collect {|val| val[1]})
303 304 # filters[field] = {:operator => operator, :values => allowed_values } if (allowed_values.first and !allowed_values.first.empty?) or ["o", "c", "!*", "*", "t"].include? operator
304 305 #end
305 306 filters[field] = {:operator => operator, :values => (values || [''])}
306 307 end
307 308 end
308 309
309 310 def add_short_filter(field, expression)
310 311 return unless expression
311 312 parms = expression.scan(/^(o|c|!\*|!|\*)?(.*)$/).first
312 313 add_filter field, (parms[0] || "="), [parms[1] || ""]
313 314 end
314 315
315 316 # Add multiple filters using +add_filter+
316 317 def add_filters(fields, operators, values)
317 318 if fields.is_a?(Array) && operators.is_a?(Hash) && (values.nil? || values.is_a?(Hash))
318 319 fields.each do |field|
319 320 add_filter(field, operators[field], values && values[field])
320 321 end
321 322 end
322 323 end
323 324
324 325 def has_filter?(field)
325 326 filters and filters[field]
326 327 end
327 328
328 329 def type_for(field)
329 330 available_filters[field][:type] if available_filters.has_key?(field)
330 331 end
331 332
332 333 def operator_for(field)
333 334 has_filter?(field) ? filters[field][:operator] : nil
334 335 end
335 336
336 337 def values_for(field)
337 338 has_filter?(field) ? filters[field][:values] : nil
338 339 end
339 340
340 341 def value_for(field, index=0)
341 342 (values_for(field) || [])[index]
342 343 end
343 344
344 345 def label_for(field)
345 346 label = available_filters[field][:name] if available_filters.has_key?(field)
346 347 label ||= field.gsub(/\_id$/, "")
347 348 end
348 349
349 350 def available_columns
350 351 return @available_columns if @available_columns
351 352 @available_columns = Query.available_columns
352 353 @available_columns += (project ?
353 354 project.all_issue_custom_fields :
354 355 IssueCustomField.find(:all)
355 356 ).collect {|cf| QueryCustomFieldColumn.new(cf) }
356 357 end
357 358
358 359 def self.available_columns=(v)
359 360 self.available_columns = (v)
360 361 end
361 362
362 363 def self.add_available_column(column)
363 364 self.available_columns << (column) if column.is_a?(QueryColumn)
364 365 end
365 366
366 367 # Returns an array of columns that can be used to group the results
367 368 def groupable_columns
368 369 available_columns.select {|c| c.groupable}
369 370 end
370 371
371 372 # Returns a Hash of columns and the key for sorting
372 373 def sortable_columns
373 374 {'id' => "#{Issue.table_name}.id"}.merge(available_columns.inject({}) {|h, column|
374 375 h[column.name.to_s] = column.sortable
375 376 h
376 377 })
377 378 end
378 379
379 380 def columns
380 381 if has_default_columns?
381 382 available_columns.select do |c|
382 383 # Adds the project column by default for cross-project lists
383 384 Setting.issue_list_default_columns.include?(c.name.to_s) || (c.name == :project && project.nil?)
384 385 end
385 386 else
386 387 # preserve the column_names order
387 388 column_names.collect {|name| available_columns.find {|col| col.name == name}}.compact
388 389 end
389 390 end
390 391
391 392 def column_names=(names)
392 393 if names
393 394 names = names.select {|n| n.is_a?(Symbol) || !n.blank? }
394 395 names = names.collect {|n| n.is_a?(Symbol) ? n : n.to_sym }
395 396 # Set column_names to nil if default columns
396 397 if names.map(&:to_s) == Setting.issue_list_default_columns
397 398 names = nil
398 399 end
399 400 end
400 401 write_attribute(:column_names, names)
401 402 end
402 403
403 404 def has_column?(column)
404 405 column_names && column_names.include?(column.name)
405 406 end
406 407
407 408 def has_default_columns?
408 409 column_names.nil? || column_names.empty?
409 410 end
410 411
411 412 def sort_criteria=(arg)
412 413 c = []
413 414 if arg.is_a?(Hash)
414 415 arg = arg.keys.sort.collect {|k| arg[k]}
415 416 end
416 417 c = arg.select {|k,o| !k.to_s.blank?}.slice(0,3).collect {|k,o| [k.to_s, o == 'desc' ? o : 'asc']}
417 418 write_attribute(:sort_criteria, c)
418 419 end
419 420
420 421 def sort_criteria
421 422 read_attribute(:sort_criteria) || []
422 423 end
423 424
424 425 def sort_criteria_key(arg)
425 426 sort_criteria && sort_criteria[arg] && sort_criteria[arg].first
426 427 end
427 428
428 429 def sort_criteria_order(arg)
429 430 sort_criteria && sort_criteria[arg] && sort_criteria[arg].last
430 431 end
431 432
432 433 # Returns the SQL sort order that should be prepended for grouping
433 434 def group_by_sort_order
434 435 if grouped? && (column = group_by_column)
435 436 column.sortable.is_a?(Array) ?
436 437 column.sortable.collect {|s| "#{s} #{column.default_order}"}.join(',') :
437 438 "#{column.sortable} #{column.default_order}"
438 439 end
439 440 end
440 441
441 442 # Returns true if the query is a grouped query
442 443 def grouped?
443 444 !group_by_column.nil?
444 445 end
445 446
446 447 def group_by_column
447 448 groupable_columns.detect {|c| c.groupable && c.name.to_s == group_by}
448 449 end
449 450
450 451 def group_by_statement
451 452 group_by_column.try(:groupable)
452 453 end
453 454
454 455 def project_statement
455 456 project_clauses = []
456 457 if project && !@project.descendants.active.empty?
457 458 ids = [project.id]
458 459 if has_filter?("subproject_id")
459 460 case operator_for("subproject_id")
460 461 when '='
461 462 # include the selected subprojects
462 463 ids += values_for("subproject_id").each(&:to_i)
463 464 when '!*'
464 465 # main project only
465 466 else
466 467 # all subprojects
467 468 ids += project.descendants.collect(&:id)
468 469 end
469 470 elsif Setting.display_subprojects_issues?
470 471 ids += project.descendants.collect(&:id)
471 472 end
472 473 project_clauses << "#{Project.table_name}.id IN (%s)" % ids.join(',')
473 474 elsif project
474 475 project_clauses << "#{Project.table_name}.id = %d" % project.id
475 476 end
476 477 project_clauses.any? ? project_clauses.join(' AND ') : nil
477 478 end
478 479
479 480 def statement
480 481 # filters clauses
481 482 filters_clauses = []
482 483 filters.each_key do |field|
483 484 next if field == "subproject_id"
484 485 v = values_for(field).clone
485 486 next unless v and !v.empty?
486 487 operator = operator_for(field)
487 488
488 489 # "me" value subsitution
489 490 if %w(assigned_to_id author_id watcher_id).include?(field)
490 491 if v.delete("me")
491 492 if User.current.logged?
492 493 v.push(User.current.id.to_s)
493 494 v += User.current.group_ids.map(&:to_s) if field == 'assigned_to_id'
494 495 else
495 496 v.push("0")
496 497 end
497 498 end
498 499 end
499 500
500 501 if field =~ /^cf_(\d+)$/
501 502 # custom field
502 503 filters_clauses << sql_for_custom_field(field, operator, v, $1)
503 504 elsif respond_to?("sql_for_#{field}_field")
504 505 # specific statement
505 506 filters_clauses << send("sql_for_#{field}_field", field, operator, v)
506 507 else
507 508 # regular field
508 509 filters_clauses << '(' + sql_for_field(field, operator, v, Issue.table_name, field) + ')'
509 510 end
510 511 end if filters and valid?
511 512
512 513 filters_clauses << project_statement
513 514 filters_clauses.reject!(&:blank?)
514 515
515 516 filters_clauses.any? ? filters_clauses.join(' AND ') : nil
516 517 end
517 518
518 519 # Returns the issue count
519 520 def issue_count
520 521 Issue.visible.count(:include => [:status, :project], :conditions => statement)
521 522 rescue ::ActiveRecord::StatementInvalid => e
522 523 raise StatementInvalid.new(e.message)
523 524 end
524 525
525 526 # Returns the issue count by group or nil if query is not grouped
526 527 def issue_count_by_group
527 528 r = nil
528 529 if grouped?
529 530 begin
530 531 # Rails will raise an (unexpected) RecordNotFound if there's only a nil group value
531 532 r = Issue.visible.count(:group => group_by_statement, :include => [:status, :project], :conditions => statement)
532 533 rescue ActiveRecord::RecordNotFound
533 534 r = {nil => issue_count}
534 535 end
535 536 c = group_by_column
536 537 if c.is_a?(QueryCustomFieldColumn)
537 538 r = r.keys.inject({}) {|h, k| h[c.custom_field.cast_value(k)] = r[k]; h}
538 539 end
539 540 end
540 541 r
541 542 rescue ::ActiveRecord::StatementInvalid => e
542 543 raise StatementInvalid.new(e.message)
543 544 end
544 545
545 546 # Returns the issues
546 547 # Valid options are :order, :offset, :limit, :include, :conditions
547 548 def issues(options={})
548 549 order_option = [group_by_sort_order, options[:order]].reject {|s| s.blank?}.join(',')
549 550 order_option = nil if order_option.blank?
550 551
551 552 Issue.visible.find :all, :include => ([:status, :project] + (options[:include] || [])).uniq,
552 553 :conditions => Query.merge_conditions(statement, options[:conditions]),
553 554 :order => order_option,
554 555 :limit => options[:limit],
555 556 :offset => options[:offset]
556 557 rescue ::ActiveRecord::StatementInvalid => e
557 558 raise StatementInvalid.new(e.message)
558 559 end
559 560
560 561 # Returns the journals
561 562 # Valid options are :order, :offset, :limit
562 563 def journals(options={})
563 564 Journal.visible.find :all, :include => [:details, :user, {:issue => [:project, :author, :tracker, :status]}],
564 565 :conditions => statement,
565 566 :order => options[:order],
566 567 :limit => options[:limit],
567 568 :offset => options[:offset]
568 569 rescue ::ActiveRecord::StatementInvalid => e
569 570 raise StatementInvalid.new(e.message)
570 571 end
571 572
572 573 # Returns the versions
573 574 # Valid options are :conditions
574 575 def versions(options={})
575 576 Version.visible.find :all, :include => :project,
576 577 :conditions => Query.merge_conditions(project_statement, options[:conditions])
577 578 rescue ::ActiveRecord::StatementInvalid => e
578 579 raise StatementInvalid.new(e.message)
579 580 end
580 581
581 582 def sql_for_watcher_id_field(field, operator, value)
582 583 db_table = Watcher.table_name
583 584 "#{Issue.table_name}.id #{ operator == '=' ? 'IN' : 'NOT IN' } (SELECT #{db_table}.watchable_id FROM #{db_table} WHERE #{db_table}.watchable_type='Issue' AND " +
584 585 sql_for_field(field, '=', value, db_table, 'user_id') + ')'
585 586 end
586 587
587 588 def sql_for_member_of_group_field(field, operator, value)
588 589 if operator == '*' # Any group
589 590 groups = Group.all
590 591 operator = '=' # Override the operator since we want to find by assigned_to
591 592 elsif operator == "!*"
592 593 groups = Group.all
593 594 operator = '!' # Override the operator since we want to find by assigned_to
594 595 else
595 596 groups = Group.find_all_by_id(value)
596 597 end
597 598 groups ||= []
598 599
599 600 members_of_groups = groups.inject([]) {|user_ids, group|
600 601 if group && group.user_ids.present?
601 602 user_ids << group.user_ids
602 603 end
603 604 user_ids.flatten.uniq.compact
604 605 }.sort.collect(&:to_s)
605 606
606 607 '(' + sql_for_field("assigned_to_id", operator, members_of_groups, Issue.table_name, "assigned_to_id", false) + ')'
607 608 end
608 609
609 610 def sql_for_assigned_to_role_field(field, operator, value)
610 611 if operator == "*" # Any Role
611 612 roles = Role.givable
612 613 operator = '=' # Override the operator since we want to find by assigned_to
613 614 elsif operator == "!*" # No role
614 615 roles = Role.givable
615 616 operator = '!' # Override the operator since we want to find by assigned_to
616 617 else
617 618 roles = Role.givable.find_all_by_id(value)
618 619 end
619 620 roles ||= []
620 621
621 622 members_of_roles = roles.inject([]) {|user_ids, role|
622 623 if role && role.members
623 624 user_ids << role.members.collect(&:user_id)
624 625 end
625 626 user_ids.flatten.uniq.compact
626 627 }.sort.collect(&:to_s)
627 628
628 629 '(' + sql_for_field("assigned_to_id", operator, members_of_roles, Issue.table_name, "assigned_to_id", false) + ')'
629 630 end
630 631
631 632 private
632 633
633 634 def sql_for_custom_field(field, operator, value, custom_field_id)
634 635 db_table = CustomValue.table_name
635 636 db_field = 'value'
636 637 "#{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=#{custom_field_id} WHERE " +
637 638 sql_for_field(field, operator, value, db_table, db_field, true) + ')'
638 639 end
639 640
640 641 # Helper method to generate the WHERE sql for a +field+, +operator+ and a +value+
641 642 def sql_for_field(field, operator, value, db_table, db_field, is_custom_filter=false)
642 643 sql = ''
643 644 case operator
644 645 when "="
645 646 if value.any?
646 647 case type_for(field)
647 648 when :date, :date_past
648 649 sql = date_clause(db_table, db_field, (Date.parse(value.first) rescue nil), (Date.parse(value.first) rescue nil))
649 650 when :integer
650 651 sql = "#{db_table}.#{db_field} = #{value.first.to_i}"
651 652 when :float
652 653 sql = "#{db_table}.#{db_field} BETWEEN #{value.first.to_f - 1e-5} AND #{value.first.to_f + 1e-5}"
653 654 else
654 655 sql = "#{db_table}.#{db_field} IN (" + value.collect{|val| "'#{connection.quote_string(val)}'"}.join(",") + ")"
655 656 end
656 657 else
657 658 # IN an empty set
658 659 sql = "1=0"
659 660 end
660 661 when "!"
661 662 if value.any?
662 663 sql = "(#{db_table}.#{db_field} IS NULL OR #{db_table}.#{db_field} NOT IN (" + value.collect{|val| "'#{connection.quote_string(val)}'"}.join(",") + "))"
663 664 else
664 665 # NOT IN an empty set
665 666 sql = "1=1"
666 667 end
667 668 when "!*"
668 669 sql = "#{db_table}.#{db_field} IS NULL"
669 670 sql << " OR #{db_table}.#{db_field} = ''" if is_custom_filter
670 671 when "*"
671 672 sql = "#{db_table}.#{db_field} IS NOT NULL"
672 673 sql << " AND #{db_table}.#{db_field} <> ''" if is_custom_filter
673 674 when ">="
674 675 if [:date, :date_past].include?(type_for(field))
675 676 sql = date_clause(db_table, db_field, (Date.parse(value.first) rescue nil), nil)
676 677 else
677 678 if is_custom_filter
678 679 sql = "CAST(#{db_table}.#{db_field} AS decimal(60,3)) >= #{value.first.to_f}"
679 680 else
680 681 sql = "#{db_table}.#{db_field} >= #{value.first.to_f}"
681 682 end
682 683 end
683 684 when "<="
684 685 if [:date, :date_past].include?(type_for(field))
685 686 sql = date_clause(db_table, db_field, nil, (Date.parse(value.first) rescue nil))
686 687 else
687 688 if is_custom_filter
688 689 sql = "CAST(#{db_table}.#{db_field} AS decimal(60,3)) <= #{value.first.to_f}"
689 690 else
690 691 sql = "#{db_table}.#{db_field} <= #{value.first.to_f}"
691 692 end
692 693 end
693 694 when "><"
694 695 if [:date, :date_past].include?(type_for(field))
695 696 sql = date_clause(db_table, db_field, (Date.parse(value[0]) rescue nil), (Date.parse(value[1]) rescue nil))
696 697 else
697 698 if is_custom_filter
698 699 sql = "CAST(#{db_table}.#{db_field} AS decimal(60,3)) BETWEEN #{value[0].to_f} AND #{value[1].to_f}"
699 700 else
700 701 sql = "#{db_table}.#{db_field} BETWEEN #{value[0].to_f} AND #{value[1].to_f}"
701 702 end
702 703 end
703 704 when "o"
704 705 sql = "#{IssueStatus.table_name}.is_closed=#{connection.quoted_false}" if field == "status_id"
705 706 when "c"
706 707 sql = "#{IssueStatus.table_name}.is_closed=#{connection.quoted_true}" if field == "status_id"
707 708 when ">t-"
708 709 sql = relative_date_clause(db_table, db_field, - value.first.to_i, 0)
709 710 when "<t-"
710 711 sql = relative_date_clause(db_table, db_field, nil, - value.first.to_i)
711 712 when "t-"
712 713 sql = relative_date_clause(db_table, db_field, - value.first.to_i, - value.first.to_i)
713 714 when ">t+"
714 715 sql = relative_date_clause(db_table, db_field, value.first.to_i, nil)
715 716 when "<t+"
716 717 sql = relative_date_clause(db_table, db_field, 0, value.first.to_i)
717 718 when "t+"
718 719 sql = relative_date_clause(db_table, db_field, value.first.to_i, value.first.to_i)
719 720 when "t"
720 721 sql = relative_date_clause(db_table, db_field, 0, 0)
721 722 when "w"
722 723 first_day_of_week = l(:general_first_day_of_week).to_i
723 724 day_of_week = Date.today.cwday
724 725 days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)
725 726 sql = relative_date_clause(db_table, db_field, - days_ago, - days_ago + 6)
726 727 when "~"
727 728 sql = "LOWER(#{db_table}.#{db_field}) LIKE '%#{connection.quote_string(value.first.to_s.downcase)}%'"
728 729 when "!~"
729 730 sql = "LOWER(#{db_table}.#{db_field}) NOT LIKE '%#{connection.quote_string(value.first.to_s.downcase)}%'"
730 731 else
731 732 raise "Unknown query operator #{operator}"
732 733 end
733 734
734 735 return sql
735 736 end
736 737
737 738 def add_custom_fields_filters(custom_fields)
738 739 @available_filters ||= {}
739 740
740 741 custom_fields.select(&:is_filter?).each do |field|
741 742 case field.field_format
742 743 when "text"
743 744 options = { :type => :text, :order => 20 }
744 745 when "list"
745 746 options = { :type => :list_optional, :values => field.possible_values, :order => 20}
746 747 when "date"
747 748 options = { :type => :date, :order => 20 }
748 749 when "bool"
749 750 options = { :type => :list, :values => [[l(:general_text_yes), "1"], [l(:general_text_no), "0"]], :order => 20 }
750 751 when "int"
751 752 options = { :type => :integer, :order => 20 }
752 753 when "float"
753 754 options = { :type => :float, :order => 20 }
754 755 when "user", "version"
755 756 next unless project
756 757 options = { :type => :list_optional, :values => field.possible_values_options(project), :order => 20}
757 758 else
758 759 options = { :type => :string, :order => 20 }
759 760 end
760 761 @available_filters["cf_#{field.id}"] = options.merge({ :name => field.name })
761 762 end
762 763 end
763 764
764 765 # Returns a SQL clause for a date or datetime field.
765 766 def date_clause(table, field, from, to)
766 767 s = []
767 768 if from
768 769 s << ("#{table}.#{field} > '%s'" % [connection.quoted_date((from - 1).to_time.end_of_day)])
769 770 end
770 771 if to
771 772 s << ("#{table}.#{field} <= '%s'" % [connection.quoted_date(to.to_time.end_of_day)])
772 773 end
773 774 s.join(' AND ')
774 775 end
775 776
776 777 # Returns a SQL clause for a date or datetime field using relative dates.
777 778 def relative_date_clause(table, field, days_from, days_to)
778 779 date_clause(table, field, (days_from ? Date.today + days_from : nil), (days_to ? Date.today + days_to : nil))
779 780 end
780 781 end
General Comments 0
You need to be logged in to leave comments. Login now