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