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