##// END OF EJS Templates
jro_apps » redmine
RSS
replace tab to space at app/models/query.rb...
Toshi MARUYAMA -
r10261:95fe4d61c96a
parent child
Show More
Side by Side Unified
Context file:
r10261:95fe4d61c96a Loading diff...:
Show file before | Show file after | Hide comments
@@ -1,962 +1,962
1 1 # Redmine - project management software
2 2 # Copyright (C) 2006-2012 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 sortable
43 43 @sortable.is_a?(Proc) ? @sortable.call : @sortable
44 44 end
45 45
46 46 def value(issue)
47 47 issue.send name
48 48 end
49 49
50 50 def css_classes
51 51 name
52 52 end
53 53 end
54 54
55 55 class QueryCustomFieldColumn < QueryColumn
56 56
57 57 def initialize(custom_field)
58 58 self.name = "cf_#{custom_field.id}".to_sym
59 59 self.sortable = custom_field.order_statement || false
60 60 self.groupable = custom_field.group_statement || false
61 61 @cf = custom_field
62 62 end
63 63
64 64 def caption
65 65 @cf.name
66 66 end
67 67
68 68 def custom_field
69 69 @cf
70 70 end
71 71
72 72 def value(issue)
73 73 cv = issue.custom_values.select {|v| v.custom_field_id == @cf.id}.collect {|v| @cf.cast_value(v.value)}
74 74 cv.size > 1 ? cv : cv.first
75 75 end
76 76
77 77 def css_classes
78 78 @css_classes ||= "#{name} #{@cf.field_format}"
79 79 end
80 80 end
81 81
82 82 class Query < ActiveRecord::Base
83 83 class StatementInvalid < ::ActiveRecord::StatementInvalid
84 84 end
85 85
86 86 belongs_to :project
87 87 belongs_to :user
88 88 serialize :filters
89 89 serialize :column_names
90 90 serialize :sort_criteria, Array
91 91
92 92 attr_protected :project_id, :user_id
93 93
94 94 validates_presence_of :name
95 95 validates_length_of :name, :maximum => 255
96 96 validate :validate_query_filters
97 97
98 98 @@operators = { "=" => :label_equals,
99 99 "!" => :label_not_equals,
100 100 "o" => :label_open_issues,
101 101 "c" => :label_closed_issues,
102 102 "!*" => :label_none,
103 103 "*" => :label_all,
104 104 ">=" => :label_greater_or_equal,
105 105 "<=" => :label_less_or_equal,
106 106 "><" => :label_between,
107 107 "<t+" => :label_in_less_than,
108 108 ">t+" => :label_in_more_than,
109 109 "t+" => :label_in,
110 110 "t" => :label_today,
111 111 "w" => :label_this_week,
112 112 ">t-" => :label_less_than_ago,
113 113 "<t-" => :label_more_than_ago,
114 114 "t-" => :label_ago,
115 115 "~" => :label_contains,
116 116 "!~" => :label_not_contains }
117 117
118 118 cattr_reader :operators
119 119
120 120 @@operators_by_filter_type = { :list => [ "=", "!" ],
121 121 :list_status => [ "o", "=", "!", "c", "*" ],
122 122 :list_optional => [ "=", "!", "!*", "*" ],
123 123 :list_subprojects => [ "*", "!*", "=" ],
124 124 :date => [ "=", ">=", "<=", "><", "<t+", ">t+", "t+", "t", "w", ">t-", "<t-", "t-", "!*", "*" ],
125 125 :date_past => [ "=", ">=", "<=", "><", ">t-", "<t-", "t-", "t", "w", "!*", "*" ],
126 126 :string => [ "=", "~", "!", "!~", "!*", "*" ],
127 127 :text => [ "~", "!~", "!*", "*" ],
128 128 :integer => [ "=", ">=", "<=", "><", "!*", "*" ],
129 129 :float => [ "=", ">=", "<=", "><", "!*", "*" ] }
130 130
131 131 cattr_reader :operators_by_filter_type
132 132
133 133 @@available_columns = [
134 134 QueryColumn.new(:project, :sortable => "#{Project.table_name}.name", :groupable => true),
135 135 QueryColumn.new(:tracker, :sortable => "#{Tracker.table_name}.position", :groupable => true),
136 136 QueryColumn.new(:parent, :sortable => ["#{Issue.table_name}.root_id", "#{Issue.table_name}.lft ASC"], :default_order => 'desc', :caption => :field_parent_issue),
137 137 QueryColumn.new(:status, :sortable => "#{IssueStatus.table_name}.position", :groupable => true),
138 138 QueryColumn.new(:priority, :sortable => "#{IssuePriority.table_name}.position", :default_order => 'desc', :groupable => true),
139 139 QueryColumn.new(:subject, :sortable => "#{Issue.table_name}.subject"),
140 140 QueryColumn.new(:author, :sortable => lambda {User.fields_for_order_statement("authors")}, :groupable => true),
141 141 QueryColumn.new(:assigned_to, :sortable => lambda {User.fields_for_order_statement}, :groupable => true),
142 142 QueryColumn.new(:updated_on, :sortable => "#{Issue.table_name}.updated_on", :default_order => 'desc'),
143 143 QueryColumn.new(:category, :sortable => "#{IssueCategory.table_name}.name", :groupable => true),
144 144 QueryColumn.new(:fixed_version, :sortable => lambda {Version.fields_for_order_statement}, :groupable => true),
145 145 QueryColumn.new(:start_date, :sortable => "#{Issue.table_name}.start_date"),
146 146 QueryColumn.new(:due_date, :sortable => "#{Issue.table_name}.due_date"),
147 147 QueryColumn.new(:estimated_hours, :sortable => "#{Issue.table_name}.estimated_hours"),
148 148 QueryColumn.new(:done_ratio, :sortable => "#{Issue.table_name}.done_ratio", :groupable => true),
149 149 QueryColumn.new(:created_on, :sortable => "#{Issue.table_name}.created_on", :default_order => 'desc'),
150 150 ]
151 151 cattr_reader :available_columns
152 152
153 153 scope :visible, lambda {|*args|
154 154 user = args.shift || User.current
155 155 base = Project.allowed_to_condition(user, :view_issues, *args)
156 156 user_id = user.logged? ? user.id : 0
157 157 {
158 158 :conditions => ["(#{table_name}.project_id IS NULL OR (#{base})) AND (#{table_name}.is_public = ? OR #{table_name}.user_id = ?)", true, user_id],
159 159 :include => :project
160 160 }
161 161 }
162 162
163 163 def initialize(attributes=nil, *args)
164 164 super attributes
165 165 self.filters ||= { 'status_id' => {:operator => "o", :values => [""]} }
166 166 @is_for_all = project.nil?
167 167 end
168 168
169 169 def validate_query_filters
170 170 filters.each_key do |field|
171 171 if values_for(field)
172 172 case type_for(field)
173 173 when :integer
174 174 add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && !v.match(/^[+-]?\d+$/) }
175 175 when :float
176 176 add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && !v.match(/^[+-]?\d+(\.\d*)?$/) }
177 177 when :date, :date_past
178 178 case operator_for(field)
179 179 when "=", ">=", "<=", "><"
180 180 add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && (!v.match(/^\d{4}-\d{2}-\d{2}$/) || (Date.parse(v) rescue nil).nil?) }
181 181 when ">t-", "<t-", "t-"
182 182 add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && !v.match(/^\d+$/) }
183 183 end
184 184 end
185 185 end
186 186
187 187 add_filter_error(field, :blank) unless
188 188 # filter requires one or more values
189 189 (values_for(field) and !values_for(field).first.blank?) or
190 190 # filter doesn't require any value
191 191 ["o", "c", "!*", "*", "t", "w"].include? operator_for(field)
192 192 end if filters
193 193 end
194 194
195 195 def add_filter_error(field, message)
196 196 m = label_for(field) + " " + l(message, :scope => 'activerecord.errors.messages')
197 197 errors.add(:base, m)
198 198 end
199 199
200 200 # Returns true if the query is visible to +user+ or the current user.
201 201 def visible?(user=User.current)
202 202 (project.nil? || user.allowed_to?(:view_issues, project)) && (self.is_public? || self.user_id == user.id)
203 203 end
204 204
205 205 def editable_by?(user)
206 206 return false unless user
207 207 # Admin can edit them all and regular users can edit their private queries
208 208 return true if user.admin? || (!is_public && self.user_id == user.id)
209 209 # Members can not edit public queries that are for all project (only admin is allowed to)
210 210 is_public && !@is_for_all && user.allowed_to?(:manage_public_queries, project)
211 211 end
212 212
213 213 def trackers
214 214 @trackers ||= project.nil? ? Tracker.find(:all, :order => 'position') : project.rolled_up_trackers
215 215 end
216 216
217 217 # Returns a hash of localized labels for all filter operators
218 218 def self.operators_labels
219 219 operators.inject({}) {|h, operator| h[operator.first] = l(operator.last); h}
220 220 end
221 221
222 222 def available_filters
223 223 return @available_filters if @available_filters
224 224
225 225 @available_filters = { "status_id" => { :type => :list_status, :order => 1, :values => IssueStatus.find(:all, :order => 'position').collect{|s| [s.name, s.id.to_s] } },
226 226 "tracker_id" => { :type => :list, :order => 2, :values => trackers.collect{|s| [s.name, s.id.to_s] } },
227 227 "priority_id" => { :type => :list, :order => 3, :values => IssuePriority.all.collect{|s| [s.name, s.id.to_s] } },
228 228 "subject" => { :type => :text, :order => 8 },
229 229 "created_on" => { :type => :date_past, :order => 9 },
230 230 "updated_on" => { :type => :date_past, :order => 10 },
231 231 "start_date" => { :type => :date, :order => 11 },
232 232 "due_date" => { :type => :date, :order => 12 },
233 233 "estimated_hours" => { :type => :float, :order => 13 },
234 234 "done_ratio" => { :type => :integer, :order => 14 }}
235 235
236 236 principals = []
237 237 if project
238 238 principals += project.principals.sort
239 239 unless project.leaf?
240 240 subprojects = project.descendants.visible.all
241 241 if subprojects.any?
242 242 @available_filters["subproject_id"] = { :type => :list_subprojects, :order => 13, :values => subprojects.collect{|s| [s.name, s.id.to_s] } }
243 243 principals += Principal.member_of(subprojects)
244 244 end
245 245 end
246 246 else
247 247 all_projects = Project.visible.all
248 248 if all_projects.any?
249 249 # members of visible projects
250 250 principals += Principal.member_of(all_projects)
251 251
252 252 # project filter
253 253 project_values = []
254 254 if User.current.logged? && User.current.memberships.any?
255 255 project_values << ["<< #{l(:label_my_projects).downcase} >>", "mine"]
256 256 end
257 257 Project.project_tree(all_projects) do |p, level|
258 258 prefix = (level > 0 ? ('--' * level + ' ') : '')
259 259 project_values << ["#{prefix}#{p.name}", p.id.to_s]
260 260 end
261 261 @available_filters["project_id"] = { :type => :list, :order => 1, :values => project_values} unless project_values.empty?
262 262 end
263 263 end
264 264 principals.uniq!
265 265 principals.sort!
266 266 users = principals.select {|p| p.is_a?(User)}
267 267
268 268 assigned_to_values = []
269 269 assigned_to_values << ["<< #{l(:label_me)} >>", "me"] if User.current.logged?
270 270 assigned_to_values += (Setting.issue_group_assignment? ? principals : users).collect{|s| [s.name, s.id.to_s] }
271 271 @available_filters["assigned_to_id"] = { :type => :list_optional, :order => 4, :values => assigned_to_values } unless assigned_to_values.empty?
272 272
273 273 author_values = []
274 274 author_values << ["<< #{l(:label_me)} >>", "me"] if User.current.logged?
275 275 author_values += users.collect{|s| [s.name, s.id.to_s] }
276 276 @available_filters["author_id"] = { :type => :list, :order => 5, :values => author_values } unless author_values.empty?
277 277
278 278 group_values = Group.all.collect {|g| [g.name, g.id.to_s] }
279 279 @available_filters["member_of_group"] = { :type => :list_optional, :order => 6, :values => group_values } unless group_values.empty?
280 280
281 281 role_values = Role.givable.collect {|r| [r.name, r.id.to_s] }
282 282 @available_filters["assigned_to_role"] = { :type => :list_optional, :order => 7, :values => role_values } unless role_values.empty?
283 283
284 284 if User.current.logged?
285 285 @available_filters["watcher_id"] = { :type => :list, :order => 15, :values => [["<< #{l(:label_me)} >>", "me"]] }
286 286 end
287 287
288 288 if project
289 289 # project specific filters
290 290 categories = project.issue_categories.all
291 291 unless categories.empty?
292 292 @available_filters["category_id"] = { :type => :list_optional, :order => 6, :values => categories.collect{|s| [s.name, s.id.to_s] } }
293 293 end
294 294 versions = project.shared_versions.all
295 295 unless versions.empty?
296 296 @available_filters["fixed_version_id"] = { :type => :list_optional, :order => 7, :values => versions.sort.collect{|s| ["#{s.project.name} - #{s.name}", s.id.to_s] } }
297 297 end
298 298 add_custom_fields_filters(project.all_issue_custom_fields)
299 299 else
300 300 # global filters for cross project issue list
301 301 system_shared_versions = Version.visible.find_all_by_sharing('system')
302 302 unless system_shared_versions.empty?
303 303 @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] } }
304 304 end
305 305 add_custom_fields_filters(IssueCustomField.find(:all, :conditions => {:is_filter => true, :is_for_all => true}))
306 306 end
307 307
308 308 add_associations_custom_fields_filters :project, :author, :assigned_to, :fixed_version
309 309
310 310 if User.current.allowed_to?(:set_issues_private, nil, :global => true) ||
311 311 User.current.allowed_to?(:set_own_issues_private, nil, :global => true)
312 312 @available_filters["is_private"] = { :type => :list, :order => 15, :values => [[l(:general_text_yes), "1"], [l(:general_text_no), "0"]] }
313 313 end
314 314
315 315 Tracker.disabled_core_fields(trackers).each {|field|
316 316 @available_filters.delete field
317 317 }
318 318
319 319 @available_filters.each do |field, options|
320 320 options[:name] ||= l("field_#{field}".gsub(/_id$/, ''))
321 321 end
322 322
323 323 @available_filters
324 324 end
325 325
326 # Returns a representation of the available filters for JSON serialization
326 # Returns a representation of the available filters for JSON serialization
327 327 def available_filters_as_json
328 328 json = {}
329 329 available_filters.each do |field, options|
330 330 json[field] = options.slice(:type, :name, :values).stringify_keys
331 331 end
332 332 json
333 333 end
334 334
335 335 def add_filter(field, operator, values)
336 336 # values must be an array
337 337 return unless values.nil? || values.is_a?(Array)
338 338 # check if field is defined as an available filter
339 339 if available_filters.has_key? field
340 340 filter_options = available_filters[field]
341 341 # check if operator is allowed for that filter
342 342 #if @@operators_by_filter_type[filter_options[:type]].include? operator
343 343 # allowed_values = values & ([""] + (filter_options[:values] || []).collect {|val| val[1]})
344 344 # filters[field] = {:operator => operator, :values => allowed_values } if (allowed_values.first and !allowed_values.first.empty?) or ["o", "c", "!*", "*", "t"].include? operator
345 345 #end
346 346 filters[field] = {:operator => operator, :values => (values || [''])}
347 347 end
348 348 end
349 349
350 350 def add_short_filter(field, expression)
351 351 return unless expression && available_filters.has_key?(field)
352 352 field_type = available_filters[field][:type]
353 353 @@operators_by_filter_type[field_type].sort.reverse.detect do |operator|
354 354 next unless expression =~ /^#{Regexp.escape(operator)}(.*)$/
355 355 add_filter field, operator, $1.present? ? $1.split('|') : ['']
356 356 end || add_filter(field, '=', expression.split('|'))
357 357 end
358 358
359 359 # Add multiple filters using +add_filter+
360 360 def add_filters(fields, operators, values)
361 361 if fields.is_a?(Array) && operators.is_a?(Hash) && (values.nil? || values.is_a?(Hash))
362 362 fields.each do |field|
363 363 add_filter(field, operators[field], values && values[field])
364 364 end
365 365 end
366 366 end
367 367
368 368 def has_filter?(field)
369 369 filters and filters[field]
370 370 end
371 371
372 372 def type_for(field)
373 373 available_filters[field][:type] if available_filters.has_key?(field)
374 374 end
375 375
376 376 def operator_for(field)
377 377 has_filter?(field) ? filters[field][:operator] : nil
378 378 end
379 379
380 380 def values_for(field)
381 381 has_filter?(field) ? filters[field][:values] : nil
382 382 end
383 383
384 384 def value_for(field, index=0)
385 385 (values_for(field) || [])[index]
386 386 end
387 387
388 388 def label_for(field)
389 389 label = available_filters[field][:name] if available_filters.has_key?(field)
390 390 label ||= l("field_#{field.to_s.gsub(/_id$/, '')}", :default => field)
391 391 end
392 392
393 393 def available_columns
394 394 return @available_columns if @available_columns
395 395 @available_columns = ::Query.available_columns.dup
396 396 @available_columns += (project ?
397 397 project.all_issue_custom_fields :
398 398 IssueCustomField.find(:all)
399 399 ).collect {|cf| QueryCustomFieldColumn.new(cf) }
400 400
401 401 if User.current.allowed_to?(:view_time_entries, project, :global => true)
402 402 index = nil
403 403 @available_columns.each_with_index {|column, i| index = i if column.name == :estimated_hours}
404 404 index = (index ? index + 1 : -1)
405 405 # insert the column after estimated_hours or at the end
406 406 @available_columns.insert index, QueryColumn.new(:spent_hours,
407 407 :sortable => "(SELECT COALESCE(SUM(hours), 0) FROM #{TimeEntry.table_name} WHERE #{TimeEntry.table_name}.issue_id = #{Issue.table_name}.id)",
408 408 :default_order => 'desc',
409 409 :caption => :label_spent_time
410 410 )
411 411 end
412 412
413 413 if User.current.allowed_to?(:set_issues_private, nil, :global => true) ||
414 414 User.current.allowed_to?(:set_own_issues_private, nil, :global => true)
415 415 @available_columns << QueryColumn.new(:is_private, :sortable => "#{Issue.table_name}.is_private")
416 416 end
417 417
418 418 disabled_fields = Tracker.disabled_core_fields(trackers).map {|field| field.sub(/_id$/, '')}
419 419 @available_columns.reject! {|column|
420 420 disabled_fields.include?(column.name.to_s)
421 421 }
422 422
423 423 @available_columns
424 424 end
425 425
426 426 def self.available_columns=(v)
427 427 self.available_columns = (v)
428 428 end
429 429
430 430 def self.add_available_column(column)
431 431 self.available_columns << (column) if column.is_a?(QueryColumn)
432 432 end
433 433
434 434 # Returns an array of columns that can be used to group the results
435 435 def groupable_columns
436 436 available_columns.select {|c| c.groupable}
437 437 end
438 438
439 439 # Returns a Hash of columns and the key for sorting
440 440 def sortable_columns
441 441 {'id' => "#{Issue.table_name}.id"}.merge(available_columns.inject({}) {|h, column|
442 442 h[column.name.to_s] = column.sortable
443 443 h
444 444 })
445 445 end
446 446
447 447 def columns
448 448 # preserve the column_names order
449 449 (has_default_columns? ? default_columns_names : column_names).collect do |name|
450 450 available_columns.find { |col| col.name == name }
451 451 end.compact
452 452 end
453 453
454 454 def default_columns_names
455 455 @default_columns_names ||= begin
456 456 default_columns = Setting.issue_list_default_columns.map(&:to_sym)
457 457
458 458 project.present? ? default_columns : [:project] | default_columns
459 459 end
460 460 end
461 461
462 462 def column_names=(names)
463 463 if names
464 464 names = names.select {|n| n.is_a?(Symbol) || !n.blank? }
465 465 names = names.collect {|n| n.is_a?(Symbol) ? n : n.to_sym }
466 466 # Set column_names to nil if default columns
467 467 if names == default_columns_names
468 468 names = nil
469 469 end
470 470 end
471 471 write_attribute(:column_names, names)
472 472 end
473 473
474 474 def has_column?(column)
475 475 column_names && column_names.include?(column.is_a?(QueryColumn) ? column.name : column)
476 476 end
477 477
478 478 def has_default_columns?
479 479 column_names.nil? || column_names.empty?
480 480 end
481 481
482 482 def sort_criteria=(arg)
483 483 c = []
484 484 if arg.is_a?(Hash)
485 485 arg = arg.keys.sort.collect {|k| arg[k]}
486 486 end
487 487 c = arg.select {|k,o| !k.to_s.blank?}.slice(0,3).collect {|k,o| [k.to_s, o == 'desc' ? o : 'asc']}
488 488 write_attribute(:sort_criteria, c)
489 489 end
490 490
491 491 def sort_criteria
492 492 read_attribute(:sort_criteria) || []
493 493 end
494 494
495 495 def sort_criteria_key(arg)
496 496 sort_criteria && sort_criteria[arg] && sort_criteria[arg].first
497 497 end
498 498
499 499 def sort_criteria_order(arg)
500 500 sort_criteria && sort_criteria[arg] && sort_criteria[arg].last
501 501 end
502 502
503 503 # Returns the SQL sort order that should be prepended for grouping
504 504 def group_by_sort_order
505 505 if grouped? && (column = group_by_column)
506 506 column.sortable.is_a?(Array) ?
507 507 column.sortable.collect {|s| "#{s} #{column.default_order}"}.join(',') :
508 508 "#{column.sortable} #{column.default_order}"
509 509 end
510 510 end
511 511
512 512 # Returns true if the query is a grouped query
513 513 def grouped?
514 514 !group_by_column.nil?
515 515 end
516 516
517 517 def group_by_column
518 518 groupable_columns.detect {|c| c.groupable && c.name.to_s == group_by}
519 519 end
520 520
521 521 def group_by_statement
522 522 group_by_column.try(:groupable)
523 523 end
524 524
525 525 def project_statement
526 526 project_clauses = []
527 527 if project && !project.descendants.active.empty?
528 528 ids = [project.id]
529 529 if has_filter?("subproject_id")
530 530 case operator_for("subproject_id")
531 531 when '='
532 532 # include the selected subprojects
533 533 ids += values_for("subproject_id").each(&:to_i)
534 534 when '!*'
535 535 # main project only
536 536 else
537 537 # all subprojects
538 538 ids += project.descendants.collect(&:id)
539 539 end
540 540 elsif Setting.display_subprojects_issues?
541 541 ids += project.descendants.collect(&:id)
542 542 end
543 543 project_clauses << "#{Project.table_name}.id IN (%s)" % ids.join(',')
544 544 elsif project
545 545 project_clauses << "#{Project.table_name}.id = %d" % project.id
546 546 end
547 547 project_clauses.any? ? project_clauses.join(' AND ') : nil
548 548 end
549 549
550 550 def statement
551 551 # filters clauses
552 552 filters_clauses = []
553 553 filters.each_key do |field|
554 554 next if field == "subproject_id"
555 555 v = values_for(field).clone
556 556 next unless v and !v.empty?
557 557 operator = operator_for(field)
558 558
559 559 # "me" value subsitution
560 560 if %w(assigned_to_id author_id watcher_id).include?(field)
561 561 if v.delete("me")
562 562 if User.current.logged?
563 563 v.push(User.current.id.to_s)
564 564 v += User.current.group_ids.map(&:to_s) if field == 'assigned_to_id'
565 565 else
566 566 v.push("0")
567 567 end
568 568 end
569 569 end
570 570
571 571 if field == 'project_id'
572 572 if v.delete('mine')
573 573 v += User.current.memberships.map(&:project_id).map(&:to_s)
574 574 end
575 575 end
576 576
577 577 if field =~ /cf_(\d+)$/
578 578 # custom field
579 579 filters_clauses << sql_for_custom_field(field, operator, v, $1)
580 580 elsif respond_to?("sql_for_#{field}_field")
581 581 # specific statement
582 582 filters_clauses << send("sql_for_#{field}_field", field, operator, v)
583 583 else
584 584 # regular field
585 585 filters_clauses << '(' + sql_for_field(field, operator, v, Issue.table_name, field) + ')'
586 586 end
587 587 end if filters and valid?
588 588
589 589 filters_clauses << project_statement
590 590 filters_clauses.reject!(&:blank?)
591 591
592 592 filters_clauses.any? ? filters_clauses.join(' AND ') : nil
593 593 end
594 594
595 595 # Returns the issue count
596 596 def issue_count
597 597 Issue.visible.count(:include => [:status, :project], :conditions => statement)
598 598 rescue ::ActiveRecord::StatementInvalid => e
599 599 raise StatementInvalid.new(e.message)
600 600 end
601 601
602 602 # Returns the issue count by group or nil if query is not grouped
603 603 def issue_count_by_group
604 604 r = nil
605 605 if grouped?
606 606 begin
607 607 # Rails3 will raise an (unexpected) RecordNotFound if there's only a nil group value
608 608 r = Issue.visible.count(:group => group_by_statement, :include => [:status, :project], :conditions => statement)
609 609 rescue ActiveRecord::RecordNotFound
610 610 r = {nil => issue_count}
611 611 end
612 612 c = group_by_column
613 613 if c.is_a?(QueryCustomFieldColumn)
614 614 r = r.keys.inject({}) {|h, k| h[c.custom_field.cast_value(k)] = r[k]; h}
615 615 end
616 616 end
617 617 r
618 618 rescue ::ActiveRecord::StatementInvalid => e
619 619 raise StatementInvalid.new(e.message)
620 620 end
621 621
622 622 # Returns the issues
623 623 # Valid options are :order, :offset, :limit, :include, :conditions
624 624 def issues(options={})
625 625 order_option = [group_by_sort_order, options[:order]].reject {|s| s.blank?}.join(',')
626 626 order_option = nil if order_option.blank?
627 627
628 628 issues = Issue.visible.scoped(:conditions => options[:conditions]).find :all, :include => ([:status, :project] + (options[:include] || [])).uniq,
629 629 :conditions => statement,
630 630 :order => order_option,
631 631 :joins => joins_for_order_statement(order_option),
632 632 :limit => options[:limit],
633 633 :offset => options[:offset]
634 634
635 635 if has_column?(:spent_hours)
636 636 Issue.load_visible_spent_hours(issues)
637 637 end
638 638 issues
639 639 rescue ::ActiveRecord::StatementInvalid => e
640 640 raise StatementInvalid.new(e.message)
641 641 end
642 642
643 643 # Returns the issues ids
644 644 def issue_ids(options={})
645 645 order_option = [group_by_sort_order, options[:order]].reject {|s| s.blank?}.join(',')
646 646 order_option = nil if order_option.blank?
647 647
648 648 Issue.visible.scoped(:conditions => options[:conditions]).scoped(:include => ([:status, :project] + (options[:include] || [])).uniq,
649 649 :conditions => statement,
650 650 :order => order_option,
651 651 :joins => joins_for_order_statement(order_option),
652 652 :limit => options[:limit],
653 653 :offset => options[:offset]).find_ids
654 654 rescue ::ActiveRecord::StatementInvalid => e
655 655 raise StatementInvalid.new(e.message)
656 656 end
657 657
658 658 # Returns the journals
659 659 # Valid options are :order, :offset, :limit
660 660 def journals(options={})
661 661 Journal.visible.find :all, :include => [:details, :user, {:issue => [:project, :author, :tracker, :status]}],
662 662 :conditions => statement,
663 663 :order => options[:order],
664 664 :limit => options[:limit],
665 665 :offset => options[:offset]
666 666 rescue ::ActiveRecord::StatementInvalid => e
667 667 raise StatementInvalid.new(e.message)
668 668 end
669 669
670 670 # Returns the versions
671 671 # Valid options are :conditions
672 672 def versions(options={})
673 673 Version.visible.scoped(:conditions => options[:conditions]).find :all, :include => :project, :conditions => project_statement
674 674 rescue ::ActiveRecord::StatementInvalid => e
675 675 raise StatementInvalid.new(e.message)
676 676 end
677 677
678 678 def sql_for_watcher_id_field(field, operator, value)
679 679 db_table = Watcher.table_name
680 680 "#{Issue.table_name}.id #{ operator == '=' ? 'IN' : 'NOT IN' } (SELECT #{db_table}.watchable_id FROM #{db_table} WHERE #{db_table}.watchable_type='Issue' AND " +
681 681 sql_for_field(field, '=', value, db_table, 'user_id') + ')'
682 682 end
683 683
684 684 def sql_for_member_of_group_field(field, operator, value)
685 685 if operator == '*' # Any group
686 686 groups = Group.all
687 687 operator = '=' # Override the operator since we want to find by assigned_to
688 688 elsif operator == "!*"
689 689 groups = Group.all
690 690 operator = '!' # Override the operator since we want to find by assigned_to
691 691 else
692 692 groups = Group.find_all_by_id(value)
693 693 end
694 694 groups ||= []
695 695
696 696 members_of_groups = groups.inject([]) {|user_ids, group|
697 697 if group && group.user_ids.present?
698 698 user_ids << group.user_ids
699 699 end
700 700 user_ids.flatten.uniq.compact
701 701 }.sort.collect(&:to_s)
702 702
703 703 '(' + sql_for_field("assigned_to_id", operator, members_of_groups, Issue.table_name, "assigned_to_id", false) + ')'
704 704 end
705 705
706 706 def sql_for_assigned_to_role_field(field, operator, value)
707 707 case operator
708 708 when "*", "!*" # Member / Not member
709 709 sw = operator == "!*" ? 'NOT' : ''
710 710 nl = operator == "!*" ? "#{Issue.table_name}.assigned_to_id IS NULL OR" : ''
711 711 "(#{nl} #{Issue.table_name}.assigned_to_id #{sw} IN (SELECT DISTINCT #{Member.table_name}.user_id FROM #{Member.table_name}" +
712 712 " WHERE #{Member.table_name}.project_id = #{Issue.table_name}.project_id))"
713 713 when "=", "!"
714 714 role_cond = value.any? ?
715 715 "#{MemberRole.table_name}.role_id IN (" + value.collect{|val| "'#{connection.quote_string(val)}'"}.join(",") + ")" :
716 716 "1=0"
717 717
718 718 sw = operator == "!" ? 'NOT' : ''
719 719 nl = operator == "!" ? "#{Issue.table_name}.assigned_to_id IS NULL OR" : ''
720 720 "(#{nl} #{Issue.table_name}.assigned_to_id #{sw} IN (SELECT DISTINCT #{Member.table_name}.user_id FROM #{Member.table_name}, #{MemberRole.table_name}" +
721 721 " WHERE #{Member.table_name}.project_id = #{Issue.table_name}.project_id AND #{Member.table_name}.id = #{MemberRole.table_name}.member_id AND #{role_cond}))"
722 722 end
723 723 end
724 724
725 725 def sql_for_is_private_field(field, operator, value)
726 726 op = (operator == "=" ? 'IN' : 'NOT IN')
727 727 va = value.map {|v| v == '0' ? connection.quoted_false : connection.quoted_true}.uniq.join(',')
728 728
729 729 "#{Issue.table_name}.is_private #{op} (#{va})"
730 730 end
731 731
732 732 private
733 733
734 734 def sql_for_custom_field(field, operator, value, custom_field_id)
735 735 db_table = CustomValue.table_name
736 736 db_field = 'value'
737 737 filter = @available_filters[field]
738 738 return nil unless filter
739 739 if filter[:format] == 'user'
740 740 if value.delete('me')
741 741 value.push User.current.id.to_s
742 742 end
743 743 end
744 744 not_in = nil
745 745 if operator == '!'
746 746 # Makes ! operator work for custom fields with multiple values
747 747 operator = '='
748 748 not_in = 'NOT'
749 749 end
750 750 customized_key = "id"
751 751 customized_class = Issue
752 752 if field =~ /^(.+)\.cf_/
753 753 assoc = $1
754 754 customized_key = "#{assoc}_id"
755 755 customized_class = Issue.reflect_on_association(assoc.to_sym).klass.base_class rescue nil
756 756 raise "Unknown Issue association #{assoc}" unless customized_class
757 757 end
758 758 "#{Issue.table_name}.#{customized_key} #{not_in} IN (SELECT #{customized_class.table_name}.id FROM #{customized_class.table_name} LEFT OUTER JOIN #{db_table} ON #{db_table}.customized_type='#{customized_class}' AND #{db_table}.customized_id=#{customized_class.table_name}.id AND #{db_table}.custom_field_id=#{custom_field_id} WHERE " +
759 759 sql_for_field(field, operator, value, db_table, db_field, true) + ')'
760 760 end
761 761
762 762 # Helper method to generate the WHERE sql for a +field+, +operator+ and a +value+
763 763 def sql_for_field(field, operator, value, db_table, db_field, is_custom_filter=false)
764 764 sql = ''
765 765 case operator
766 766 when "="
767 767 if value.any?
768 768 case type_for(field)
769 769 when :date, :date_past
770 770 sql = date_clause(db_table, db_field, (Date.parse(value.first) rescue nil), (Date.parse(value.first) rescue nil))
771 771 when :integer
772 772 if is_custom_filter
773 773 sql = "(#{db_table}.#{db_field} <> '' AND CAST(#{db_table}.#{db_field} AS decimal(60,3)) = #{value.first.to_i})"
774 774 else
775 775 sql = "#{db_table}.#{db_field} = #{value.first.to_i}"
776 776 end
777 777 when :float
778 778 if is_custom_filter
779 779 sql = "(#{db_table}.#{db_field} <> '' AND CAST(#{db_table}.#{db_field} AS decimal(60,3)) BETWEEN #{value.first.to_f - 1e-5} AND #{value.first.to_f + 1e-5})"
780 780 else
781 781 sql = "#{db_table}.#{db_field} BETWEEN #{value.first.to_f - 1e-5} AND #{value.first.to_f + 1e-5}"
782 782 end
783 783 else
784 784 sql = "#{db_table}.#{db_field} IN (" + value.collect{|val| "'#{connection.quote_string(val)}'"}.join(",") + ")"
785 785 end
786 786 else
787 787 # IN an empty set
788 788 sql = "1=0"
789 789 end
790 790 when "!"
791 791 if value.any?
792 792 sql = "(#{db_table}.#{db_field} IS NULL OR #{db_table}.#{db_field} NOT IN (" + value.collect{|val| "'#{connection.quote_string(val)}'"}.join(",") + "))"
793 793 else
794 794 # NOT IN an empty set
795 795 sql = "1=1"
796 796 end
797 797 when "!*"
798 798 sql = "#{db_table}.#{db_field} IS NULL"
799 799 sql << " OR #{db_table}.#{db_field} = ''" if is_custom_filter
800 800 when "*"
801 801 sql = "#{db_table}.#{db_field} IS NOT NULL"
802 802 sql << " AND #{db_table}.#{db_field} <> ''" if is_custom_filter
803 803 when ">="
804 804 if [:date, :date_past].include?(type_for(field))
805 805 sql = date_clause(db_table, db_field, (Date.parse(value.first) rescue nil), nil)
806 806 else
807 807 if is_custom_filter
808 808 sql = "(#{db_table}.#{db_field} <> '' AND CAST(#{db_table}.#{db_field} AS decimal(60,3)) >= #{value.first.to_f})"
809 809 else
810 810 sql = "#{db_table}.#{db_field} >= #{value.first.to_f}"
811 811 end
812 812 end
813 813 when "<="
814 814 if [:date, :date_past].include?(type_for(field))
815 815 sql = date_clause(db_table, db_field, nil, (Date.parse(value.first) rescue nil))
816 816 else
817 817 if is_custom_filter
818 818 sql = "(#{db_table}.#{db_field} <> '' AND CAST(#{db_table}.#{db_field} AS decimal(60,3)) <= #{value.first.to_f})"
819 819 else
820 820 sql = "#{db_table}.#{db_field} <= #{value.first.to_f}"
821 821 end
822 822 end
823 823 when "><"
824 824 if [:date, :date_past].include?(type_for(field))
825 825 sql = date_clause(db_table, db_field, (Date.parse(value[0]) rescue nil), (Date.parse(value[1]) rescue nil))
826 826 else
827 827 if is_custom_filter
828 828 sql = "(#{db_table}.#{db_field} <> '' AND CAST(#{db_table}.#{db_field} AS decimal(60,3)) BETWEEN #{value[0].to_f} AND #{value[1].to_f})"
829 829 else
830 830 sql = "#{db_table}.#{db_field} BETWEEN #{value[0].to_f} AND #{value[1].to_f}"
831 831 end
832 832 end
833 833 when "o"
834 834 sql = "#{Issue.table_name}.status_id IN (SELECT id FROM #{IssueStatus.table_name} WHERE is_closed=#{connection.quoted_false})" if field == "status_id"
835 835 when "c"
836 836 sql = "#{Issue.table_name}.status_id IN (SELECT id FROM #{IssueStatus.table_name} WHERE is_closed=#{connection.quoted_true})" if field == "status_id"
837 837 when ">t-"
838 838 sql = relative_date_clause(db_table, db_field, - value.first.to_i, 0)
839 839 when "<t-"
840 840 sql = relative_date_clause(db_table, db_field, nil, - value.first.to_i)
841 841 when "t-"
842 842 sql = relative_date_clause(db_table, db_field, - value.first.to_i, - value.first.to_i)
843 843 when ">t+"
844 844 sql = relative_date_clause(db_table, db_field, value.first.to_i, nil)
845 845 when "<t+"
846 846 sql = relative_date_clause(db_table, db_field, 0, value.first.to_i)
847 847 when "t+"
848 848 sql = relative_date_clause(db_table, db_field, value.first.to_i, value.first.to_i)
849 849 when "t"
850 850 sql = relative_date_clause(db_table, db_field, 0, 0)
851 851 when "w"
852 852 first_day_of_week = l(:general_first_day_of_week).to_i
853 853 day_of_week = Date.today.cwday
854 854 days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)
855 855 sql = relative_date_clause(db_table, db_field, - days_ago, - days_ago + 6)
856 856 when "~"
857 857 sql = "LOWER(#{db_table}.#{db_field}) LIKE '%#{connection.quote_string(value.first.to_s.downcase)}%'"
858 858 when "!~"
859 859 sql = "LOWER(#{db_table}.#{db_field}) NOT LIKE '%#{connection.quote_string(value.first.to_s.downcase)}%'"
860 860 else
861 861 raise "Unknown query operator #{operator}"
862 862 end
863 863
864 864 return sql
865 865 end
866 866
867 867 def add_custom_fields_filters(custom_fields, assoc=nil)
868 868 return unless custom_fields.present?
869 869 @available_filters ||= {}
870 870
871 871 custom_fields.select(&:is_filter?).each do |field|
872 872 case field.field_format
873 873 when "text"
874 874 options = { :type => :text, :order => 20 }
875 875 when "list"
876 876 options = { :type => :list_optional, :values => field.possible_values, :order => 20}
877 877 when "date"
878 878 options = { :type => :date, :order => 20 }
879 879 when "bool"
880 880 options = { :type => :list, :values => [[l(:general_text_yes), "1"], [l(:general_text_no), "0"]], :order => 20 }
881 881 when "int"
882 882 options = { :type => :integer, :order => 20 }
883 883 when "float"
884 884 options = { :type => :float, :order => 20 }
885 885 when "user", "version"
886 886 next unless project
887 887 values = field.possible_values_options(project)
888 888 if User.current.logged? && field.field_format == 'user'
889 889 values.unshift ["<< #{l(:label_me)} >>", "me"]
890 890 end
891 891 options = { :type => :list_optional, :values => values, :order => 20}
892 892 else
893 893 options = { :type => :string, :order => 20 }
894 894 end
895 895 filter_id = "cf_#{field.id}"
896 896 filter_name = field.name
897 897 if assoc.present?
898 898 filter_id = "#{assoc}.#{filter_id}"
899 899 filter_name = l("label_attribute_of_#{assoc}", :name => filter_name)
900 900 end
901 901 @available_filters[filter_id] = options.merge({ :name => filter_name, :format => field.field_format })
902 902 end
903 903 end
904 904
905 905 def add_associations_custom_fields_filters(*associations)
906 906 fields_by_class = CustomField.where(:is_filter => true).group_by(&:class)
907 907 associations.each do |assoc|
908 908 association_klass = Issue.reflect_on_association(assoc).klass
909 909 fields_by_class.each do |field_class, fields|
910 910 if field_class.customized_class <= association_klass
911 911 add_custom_fields_filters(fields, assoc)
912 912 end
913 913 end
914 914 end
915 915 end
916 916
917 917 # Returns a SQL clause for a date or datetime field.
918 918 def date_clause(table, field, from, to)
919 919 s = []
920 920 if from
921 921 from_yesterday = from - 1
922 922 from_yesterday_time = Time.local(from_yesterday.year, from_yesterday.month, from_yesterday.day)
923 923 if self.class.default_timezone == :utc
924 924 from_yesterday_time = from_yesterday_time.utc
925 925 end
926 926 s << ("#{table}.#{field} > '%s'" % [connection.quoted_date(from_yesterday_time.end_of_day)])
927 927 end
928 928 if to
929 929 to_time = Time.local(to.year, to.month, to.day)
930 930 if self.class.default_timezone == :utc
931 931 to_time = to_time.utc
932 932 end
933 933 s << ("#{table}.#{field} <= '%s'" % [connection.quoted_date(to_time.end_of_day)])
934 934 end
935 935 s.join(' AND ')
936 936 end
937 937
938 938 # Returns a SQL clause for a date or datetime field using relative dates.
939 939 def relative_date_clause(table, field, days_from, days_to)
940 940 date_clause(table, field, (days_from ? Date.today + days_from : nil), (days_to ? Date.today + days_to : nil))
941 941 end
942 942
943 943 # Additional joins required for the given sort options
944 944 def joins_for_order_statement(order_options)
945 945 joins = []
946 946
947 947 if order_options
948 948 if order_options.include?('authors')
949 949 joins << "LEFT OUTER JOIN #{User.table_name} authors ON authors.id = #{Issue.table_name}.author_id"
950 950 end
951 951 order_options.scan(/cf_\d+/).uniq.each do |name|
952 952 column = available_columns.detect {|c| c.name.to_s == name}
953 953 join = column && column.custom_field.join_for_order_statement
954 954 if join
955 955 joins << join
956 956 end
957 957 end
958 958 end
959 959
960 960 joins.any? ? joins.join(' ') : nil
961 961 end
962 962 end
General Comments 0
You need to be logged in to leave comments. Login now