##// END OF EJS Templates
jro_apps » redmine
RSS
Test failure with JRuby 1.7.2 (#12228)....
Jean-Philippe Lang -
r11050:10a38010fbe7
parent child
Show More
Side by Side Unified
Context file:
r11050:10a38010fbe7 Loading diff...:
Show file before | Show file after | Hide comments
@@ -1,791 +1,792
1 1 # Redmine - project management software
2 2 # Copyright (C) 2006-2013 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 @inline = options.key?(:inline) ? options[:inline] : true
31 31 @caption_key = options[:caption] || "field_#{name}"
32 32 end
33 33
34 34 def caption
35 35 l(@caption_key)
36 36 end
37 37
38 38 # Returns true if the column is sortable, otherwise false
39 39 def sortable?
40 40 !@sortable.nil?
41 41 end
42 42
43 43 def sortable
44 44 @sortable.is_a?(Proc) ? @sortable.call : @sortable
45 45 end
46 46
47 47 def inline?
48 48 @inline
49 49 end
50 50
51 51 def value(object)
52 52 object.send name
53 53 end
54 54
55 55 def css_classes
56 56 name
57 57 end
58 58 end
59 59
60 60 class QueryCustomFieldColumn < QueryColumn
61 61
62 62 def initialize(custom_field)
63 63 self.name = "cf_#{custom_field.id}".to_sym
64 64 self.sortable = custom_field.order_statement || false
65 65 self.groupable = custom_field.group_statement || false
66 66 @inline = true
67 67 @cf = custom_field
68 68 end
69 69
70 70 def caption
71 71 @cf.name
72 72 end
73 73
74 74 def custom_field
75 75 @cf
76 76 end
77 77
78 78 def value(object)
79 79 cv = object.custom_values.select {|v| v.custom_field_id == @cf.id}.collect {|v| @cf.cast_value(v.value)}
80 80 cv.size > 1 ? cv.sort {|a,b| a.to_s <=> b.to_s} : cv.first
81 81 end
82 82
83 83 def css_classes
84 84 @css_classes ||= "#{name} #{@cf.field_format}"
85 85 end
86 86 end
87 87
88 88 class QueryAssociationCustomFieldColumn < QueryCustomFieldColumn
89 89
90 90 def initialize(association, custom_field)
91 91 super(custom_field)
92 92 self.name = "#{association}.cf_#{custom_field.id}".to_sym
93 93 # TODO: support sorting/grouping by association custom field
94 94 self.sortable = false
95 95 self.groupable = false
96 96 @association = association
97 97 end
98 98
99 99 def value(object)
100 100 if assoc = object.send(@association)
101 101 super(assoc)
102 102 end
103 103 end
104 104
105 105 def css_classes
106 106 @css_classes ||= "#{@association}_cf_#{@cf.id} #{@cf.field_format}"
107 107 end
108 108 end
109 109
110 110 class Query < ActiveRecord::Base
111 111 class StatementInvalid < ::ActiveRecord::StatementInvalid
112 112 end
113 113
114 114 belongs_to :project
115 115 belongs_to :user
116 116 serialize :filters
117 117 serialize :column_names
118 118 serialize :sort_criteria, Array
119 119
120 120 attr_protected :project_id, :user_id
121 121
122 122 validates_presence_of :name
123 123 validates_length_of :name, :maximum => 255
124 124 validate :validate_query_filters
125 125
126 126 class_attribute :operators
127 127 self.operators = {
128 128 "=" => :label_equals,
129 129 "!" => :label_not_equals,
130 130 "o" => :label_open_issues,
131 131 "c" => :label_closed_issues,
132 132 "!*" => :label_none,
133 133 "*" => :label_any,
134 134 ">=" => :label_greater_or_equal,
135 135 "<=" => :label_less_or_equal,
136 136 "><" => :label_between,
137 137 "<t+" => :label_in_less_than,
138 138 ">t+" => :label_in_more_than,
139 139 "><t+"=> :label_in_the_next_days,
140 140 "t+" => :label_in,
141 141 "t" => :label_today,
142 142 "ld" => :label_yesterday,
143 143 "w" => :label_this_week,
144 144 "lw" => :label_last_week,
145 145 "l2w" => [:label_last_n_weeks, {:count => 2}],
146 146 "m" => :label_this_month,
147 147 "lm" => :label_last_month,
148 148 "y" => :label_this_year,
149 149 ">t-" => :label_less_than_ago,
150 150 "<t-" => :label_more_than_ago,
151 151 "><t-"=> :label_in_the_past_days,
152 152 "t-" => :label_ago,
153 153 "~" => :label_contains,
154 154 "!~" => :label_not_contains,
155 155 "=p" => :label_any_issues_in_project,
156 156 "=!p" => :label_any_issues_not_in_project,
157 157 "!p" => :label_no_issues_in_project
158 158 }
159 159
160 160 class_attribute :operators_by_filter_type
161 161 self.operators_by_filter_type = {
162 162 :list => [ "=", "!" ],
163 163 :list_status => [ "o", "=", "!", "c", "*" ],
164 164 :list_optional => [ "=", "!", "!*", "*" ],
165 165 :list_subprojects => [ "*", "!*", "=" ],
166 166 :date => [ "=", ">=", "<=", "><", "<t+", ">t+", "><t+", "t+", "t", "ld", "w", "lw", "l2w", "m", "lm", "y", ">t-", "<t-", "><t-", "t-", "!*", "*" ],
167 167 :date_past => [ "=", ">=", "<=", "><", ">t-", "<t-", "><t-", "t-", "t", "ld", "w", "lw", "l2w", "m", "lm", "y", "!*", "*" ],
168 168 :string => [ "=", "~", "!", "!~", "!*", "*" ],
169 169 :text => [ "~", "!~", "!*", "*" ],
170 170 :integer => [ "=", ">=", "<=", "><", "!*", "*" ],
171 171 :float => [ "=", ">=", "<=", "><", "!*", "*" ],
172 172 :relation => ["=", "=p", "=!p", "!p", "!*", "*"]
173 173 }
174 174
175 175 class_attribute :available_columns
176 176 self.available_columns = []
177 177
178 178 class_attribute :queried_class
179 179
180 180 def queried_table_name
181 181 @queried_table_name ||= self.class.queried_class.table_name
182 182 end
183 183
184 184 def initialize(attributes=nil, *args)
185 185 super attributes
186 186 @is_for_all = project.nil?
187 187 end
188 188
189 189 # Builds the query from the given params
190 190 def build_from_params(params)
191 191 if params[:fields] || params[:f]
192 192 self.filters = {}
193 193 add_filters(params[:fields] || params[:f], params[:operators] || params[:op], params[:values] || params[:v])
194 194 else
195 195 available_filters.keys.each do |field|
196 196 add_short_filter(field, params[field]) if params[field]
197 197 end
198 198 end
199 199 self.group_by = params[:group_by] || (params[:query] && params[:query][:group_by])
200 200 self.column_names = params[:c] || (params[:query] && params[:query][:column_names])
201 201 self
202 202 end
203 203
204 204 # Builds a new query from the given params and attributes
205 205 def self.build_from_params(params, attributes={})
206 206 new(attributes).build_from_params(params)
207 207 end
208 208
209 209 def validate_query_filters
210 210 filters.each_key do |field|
211 211 if values_for(field)
212 212 case type_for(field)
213 213 when :integer
214 214 add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && !v.match(/^[+-]?\d+$/) }
215 215 when :float
216 216 add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && !v.match(/^[+-]?\d+(\.\d*)?$/) }
217 217 when :date, :date_past
218 218 case operator_for(field)
219 219 when "=", ">=", "<=", "><"
220 220 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?) }
221 221 when ">t-", "<t-", "t-", ">t+", "<t+", "t+", "><t+", "><t-"
222 222 add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && !v.match(/^\d+$/) }
223 223 end
224 224 end
225 225 end
226 226
227 227 add_filter_error(field, :blank) unless
228 228 # filter requires one or more values
229 229 (values_for(field) and !values_for(field).first.blank?) or
230 230 # filter doesn't require any value
231 231 ["o", "c", "!*", "*", "t", "ld", "w", "lw", "l2w", "m", "lm", "y"].include? operator_for(field)
232 232 end if filters
233 233 end
234 234
235 235 def add_filter_error(field, message)
236 236 m = label_for(field) + " " + l(message, :scope => 'activerecord.errors.messages')
237 237 errors.add(:base, m)
238 238 end
239 239
240 240 def editable_by?(user)
241 241 return false unless user
242 242 # Admin can edit them all and regular users can edit their private queries
243 243 return true if user.admin? || (!is_public && self.user_id == user.id)
244 244 # Members can not edit public queries that are for all project (only admin is allowed to)
245 245 is_public && !@is_for_all && user.allowed_to?(:manage_public_queries, project)
246 246 end
247 247
248 248 def trackers
249 249 @trackers ||= project.nil? ? Tracker.sorted.all : project.rolled_up_trackers
250 250 end
251 251
252 252 # Returns a hash of localized labels for all filter operators
253 253 def self.operators_labels
254 254 operators.inject({}) {|h, operator| h[operator.first] = l(*operator.last); h}
255 255 end
256 256
257 257 # Returns a representation of the available filters for JSON serialization
258 258 def available_filters_as_json
259 259 json = {}
260 260 available_filters.each do |field, options|
261 261 json[field] = options.slice(:type, :name, :values).stringify_keys
262 262 end
263 263 json
264 264 end
265 265
266 266 def all_projects
267 267 @all_projects ||= Project.visible.all
268 268 end
269 269
270 270 def all_projects_values
271 271 return @all_projects_values if @all_projects_values
272 272
273 273 values = []
274 274 Project.project_tree(all_projects) do |p, level|
275 275 prefix = (level > 0 ? ('--' * level + ' ') : '')
276 276 values << ["#{prefix}#{p.name}", p.id.to_s]
277 277 end
278 278 @all_projects_values = values
279 279 end
280 280
281 281 def add_filter(field, operator, values=nil)
282 282 # values must be an array
283 283 return unless values.nil? || values.is_a?(Array)
284 284 # check if field is defined as an available filter
285 285 if available_filters.has_key? field
286 286 filter_options = available_filters[field]
287 287 filters[field] = {:operator => operator, :values => (values || [''])}
288 288 end
289 289 end
290 290
291 291 def add_short_filter(field, expression)
292 292 return unless expression && available_filters.has_key?(field)
293 293 field_type = available_filters[field][:type]
294 294 operators_by_filter_type[field_type].sort.reverse.detect do |operator|
295 295 next unless expression =~ /^#{Regexp.escape(operator)}(.*)$/
296 add_filter field, operator, $1.present? ? $1.split('|') : ['']
296 values = $1
297 add_filter field, operator, values.present? ? values.split('|') : ['']
297 298 end || add_filter(field, '=', expression.split('|'))
298 299 end
299 300
300 301 # Add multiple filters using +add_filter+
301 302 def add_filters(fields, operators, values)
302 303 if fields.is_a?(Array) && operators.is_a?(Hash) && (values.nil? || values.is_a?(Hash))
303 304 fields.each do |field|
304 305 add_filter(field, operators[field], values && values[field])
305 306 end
306 307 end
307 308 end
308 309
309 310 def has_filter?(field)
310 311 filters and filters[field]
311 312 end
312 313
313 314 def type_for(field)
314 315 available_filters[field][:type] if available_filters.has_key?(field)
315 316 end
316 317
317 318 def operator_for(field)
318 319 has_filter?(field) ? filters[field][:operator] : nil
319 320 end
320 321
321 322 def values_for(field)
322 323 has_filter?(field) ? filters[field][:values] : nil
323 324 end
324 325
325 326 def value_for(field, index=0)
326 327 (values_for(field) || [])[index]
327 328 end
328 329
329 330 def label_for(field)
330 331 label = available_filters[field][:name] if available_filters.has_key?(field)
331 332 label ||= l("field_#{field.to_s.gsub(/_id$/, '')}", :default => field)
332 333 end
333 334
334 335 def self.add_available_column(column)
335 336 self.available_columns << (column) if column.is_a?(QueryColumn)
336 337 end
337 338
338 339 # Returns an array of columns that can be used to group the results
339 340 def groupable_columns
340 341 available_columns.select {|c| c.groupable}
341 342 end
342 343
343 344 # Returns a Hash of columns and the key for sorting
344 345 def sortable_columns
345 346 available_columns.inject({}) {|h, column|
346 347 h[column.name.to_s] = column.sortable
347 348 h
348 349 }
349 350 end
350 351
351 352 def columns
352 353 # preserve the column_names order
353 354 (has_default_columns? ? default_columns_names : column_names).collect do |name|
354 355 available_columns.find { |col| col.name == name }
355 356 end.compact
356 357 end
357 358
358 359 def inline_columns
359 360 columns.select(&:inline?)
360 361 end
361 362
362 363 def block_columns
363 364 columns.reject(&:inline?)
364 365 end
365 366
366 367 def available_inline_columns
367 368 available_columns.select(&:inline?)
368 369 end
369 370
370 371 def available_block_columns
371 372 available_columns.reject(&:inline?)
372 373 end
373 374
374 375 def default_columns_names
375 376 []
376 377 end
377 378
378 379 def column_names=(names)
379 380 if names
380 381 names = names.select {|n| n.is_a?(Symbol) || !n.blank? }
381 382 names = names.collect {|n| n.is_a?(Symbol) ? n : n.to_sym }
382 383 # Set column_names to nil if default columns
383 384 if names == default_columns_names
384 385 names = nil
385 386 end
386 387 end
387 388 write_attribute(:column_names, names)
388 389 end
389 390
390 391 def has_column?(column)
391 392 column_names && column_names.include?(column.is_a?(QueryColumn) ? column.name : column)
392 393 end
393 394
394 395 def has_default_columns?
395 396 column_names.nil? || column_names.empty?
396 397 end
397 398
398 399 def sort_criteria=(arg)
399 400 c = []
400 401 if arg.is_a?(Hash)
401 402 arg = arg.keys.sort.collect {|k| arg[k]}
402 403 end
403 404 c = arg.select {|k,o| !k.to_s.blank?}.slice(0,3).collect {|k,o| [k.to_s, (o == 'desc' || o == false) ? 'desc' : 'asc']}
404 405 write_attribute(:sort_criteria, c)
405 406 end
406 407
407 408 def sort_criteria
408 409 read_attribute(:sort_criteria) || []
409 410 end
410 411
411 412 def sort_criteria_key(arg)
412 413 sort_criteria && sort_criteria[arg] && sort_criteria[arg].first
413 414 end
414 415
415 416 def sort_criteria_order(arg)
416 417 sort_criteria && sort_criteria[arg] && sort_criteria[arg].last
417 418 end
418 419
419 420 def sort_criteria_order_for(key)
420 421 sort_criteria.detect {|k, order| key.to_s == k}.try(:last)
421 422 end
422 423
423 424 # Returns the SQL sort order that should be prepended for grouping
424 425 def group_by_sort_order
425 426 if grouped? && (column = group_by_column)
426 427 order = sort_criteria_order_for(column.name) || column.default_order
427 428 column.sortable.is_a?(Array) ?
428 429 column.sortable.collect {|s| "#{s} #{order}"}.join(',') :
429 430 "#{column.sortable} #{order}"
430 431 end
431 432 end
432 433
433 434 # Returns true if the query is a grouped query
434 435 def grouped?
435 436 !group_by_column.nil?
436 437 end
437 438
438 439 def group_by_column
439 440 groupable_columns.detect {|c| c.groupable && c.name.to_s == group_by}
440 441 end
441 442
442 443 def group_by_statement
443 444 group_by_column.try(:groupable)
444 445 end
445 446
446 447 def project_statement
447 448 project_clauses = []
448 449 if project && !project.descendants.active.empty?
449 450 ids = [project.id]
450 451 if has_filter?("subproject_id")
451 452 case operator_for("subproject_id")
452 453 when '='
453 454 # include the selected subprojects
454 455 ids += values_for("subproject_id").each(&:to_i)
455 456 when '!*'
456 457 # main project only
457 458 else
458 459 # all subprojects
459 460 ids += project.descendants.collect(&:id)
460 461 end
461 462 elsif Setting.display_subprojects_issues?
462 463 ids += project.descendants.collect(&:id)
463 464 end
464 465 project_clauses << "#{Project.table_name}.id IN (%s)" % ids.join(',')
465 466 elsif project
466 467 project_clauses << "#{Project.table_name}.id = %d" % project.id
467 468 end
468 469 project_clauses.any? ? project_clauses.join(' AND ') : nil
469 470 end
470 471
471 472 def statement
472 473 # filters clauses
473 474 filters_clauses = []
474 475 filters.each_key do |field|
475 476 next if field == "subproject_id"
476 477 v = values_for(field).clone
477 478 next unless v and !v.empty?
478 479 operator = operator_for(field)
479 480
480 481 # "me" value subsitution
481 482 if %w(assigned_to_id author_id user_id watcher_id).include?(field)
482 483 if v.delete("me")
483 484 if User.current.logged?
484 485 v.push(User.current.id.to_s)
485 486 v += User.current.group_ids.map(&:to_s) if field == 'assigned_to_id'
486 487 else
487 488 v.push("0")
488 489 end
489 490 end
490 491 end
491 492
492 493 if field == 'project_id'
493 494 if v.delete('mine')
494 495 v += User.current.memberships.map(&:project_id).map(&:to_s)
495 496 end
496 497 end
497 498
498 499 if field =~ /cf_(\d+)$/
499 500 # custom field
500 501 filters_clauses << sql_for_custom_field(field, operator, v, $1)
501 502 elsif respond_to?("sql_for_#{field}_field")
502 503 # specific statement
503 504 filters_clauses << send("sql_for_#{field}_field", field, operator, v)
504 505 else
505 506 # regular field
506 507 filters_clauses << '(' + sql_for_field(field, operator, v, queried_table_name, field) + ')'
507 508 end
508 509 end if filters and valid?
509 510
510 511 filters_clauses << project_statement
511 512 filters_clauses.reject!(&:blank?)
512 513
513 514 filters_clauses.any? ? filters_clauses.join(' AND ') : nil
514 515 end
515 516
516 517 private
517 518
518 519 def sql_for_custom_field(field, operator, value, custom_field_id)
519 520 db_table = CustomValue.table_name
520 521 db_field = 'value'
521 522 filter = @available_filters[field]
522 523 return nil unless filter
523 524 if filter[:format] == 'user'
524 525 if value.delete('me')
525 526 value.push User.current.id.to_s
526 527 end
527 528 end
528 529 not_in = nil
529 530 if operator == '!'
530 531 # Makes ! operator work for custom fields with multiple values
531 532 operator = '='
532 533 not_in = 'NOT'
533 534 end
534 535 customized_key = "id"
535 536 customized_class = queried_class
536 537 if field =~ /^(.+)\.cf_/
537 538 assoc = $1
538 539 customized_key = "#{assoc}_id"
539 540 customized_class = queried_class.reflect_on_association(assoc.to_sym).klass.base_class rescue nil
540 541 raise "Unknown #{queried_class.name} association #{assoc}" unless customized_class
541 542 end
542 543 "#{queried_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 " +
543 544 sql_for_field(field, operator, value, db_table, db_field, true) + ')'
544 545 end
545 546
546 547 # Helper method to generate the WHERE sql for a +field+, +operator+ and a +value+
547 548 def sql_for_field(field, operator, value, db_table, db_field, is_custom_filter=false)
548 549 sql = ''
549 550 case operator
550 551 when "="
551 552 if value.any?
552 553 case type_for(field)
553 554 when :date, :date_past
554 555 sql = date_clause(db_table, db_field, (Date.parse(value.first) rescue nil), (Date.parse(value.first) rescue nil))
555 556 when :integer
556 557 if is_custom_filter
557 558 sql = "(#{db_table}.#{db_field} <> '' AND CAST(CASE #{db_table}.#{db_field} WHEN '' THEN '0' ELSE #{db_table}.#{db_field} END AS decimal(30,3)) = #{value.first.to_i})"
558 559 else
559 560 sql = "#{db_table}.#{db_field} = #{value.first.to_i}"
560 561 end
561 562 when :float
562 563 if is_custom_filter
563 564 sql = "(#{db_table}.#{db_field} <> '' AND CAST(CASE #{db_table}.#{db_field} WHEN '' THEN '0' ELSE #{db_table}.#{db_field} END AS decimal(30,3)) BETWEEN #{value.first.to_f - 1e-5} AND #{value.first.to_f + 1e-5})"
564 565 else
565 566 sql = "#{db_table}.#{db_field} BETWEEN #{value.first.to_f - 1e-5} AND #{value.first.to_f + 1e-5}"
566 567 end
567 568 else
568 569 sql = "#{db_table}.#{db_field} IN (" + value.collect{|val| "'#{connection.quote_string(val)}'"}.join(",") + ")"
569 570 end
570 571 else
571 572 # IN an empty set
572 573 sql = "1=0"
573 574 end
574 575 when "!"
575 576 if value.any?
576 577 sql = "(#{db_table}.#{db_field} IS NULL OR #{db_table}.#{db_field} NOT IN (" + value.collect{|val| "'#{connection.quote_string(val)}'"}.join(",") + "))"
577 578 else
578 579 # NOT IN an empty set
579 580 sql = "1=1"
580 581 end
581 582 when "!*"
582 583 sql = "#{db_table}.#{db_field} IS NULL"
583 584 sql << " OR #{db_table}.#{db_field} = ''" if is_custom_filter
584 585 when "*"
585 586 sql = "#{db_table}.#{db_field} IS NOT NULL"
586 587 sql << " AND #{db_table}.#{db_field} <> ''" if is_custom_filter
587 588 when ">="
588 589 if [:date, :date_past].include?(type_for(field))
589 590 sql = date_clause(db_table, db_field, (Date.parse(value.first) rescue nil), nil)
590 591 else
591 592 if is_custom_filter
592 593 sql = "(#{db_table}.#{db_field} <> '' AND CAST(CASE #{db_table}.#{db_field} WHEN '' THEN '0' ELSE #{db_table}.#{db_field} END AS decimal(30,3)) >= #{value.first.to_f})"
593 594 else
594 595 sql = "#{db_table}.#{db_field} >= #{value.first.to_f}"
595 596 end
596 597 end
597 598 when "<="
598 599 if [:date, :date_past].include?(type_for(field))
599 600 sql = date_clause(db_table, db_field, nil, (Date.parse(value.first) rescue nil))
600 601 else
601 602 if is_custom_filter
602 603 sql = "(#{db_table}.#{db_field} <> '' AND CAST(CASE #{db_table}.#{db_field} WHEN '' THEN '0' ELSE #{db_table}.#{db_field} END AS decimal(30,3)) <= #{value.first.to_f})"
603 604 else
604 605 sql = "#{db_table}.#{db_field} <= #{value.first.to_f}"
605 606 end
606 607 end
607 608 when "><"
608 609 if [:date, :date_past].include?(type_for(field))
609 610 sql = date_clause(db_table, db_field, (Date.parse(value[0]) rescue nil), (Date.parse(value[1]) rescue nil))
610 611 else
611 612 if is_custom_filter
612 613 sql = "(#{db_table}.#{db_field} <> '' AND CAST(CASE #{db_table}.#{db_field} WHEN '' THEN '0' ELSE #{db_table}.#{db_field} END AS decimal(30,3)) BETWEEN #{value[0].to_f} AND #{value[1].to_f})"
613 614 else
614 615 sql = "#{db_table}.#{db_field} BETWEEN #{value[0].to_f} AND #{value[1].to_f}"
615 616 end
616 617 end
617 618 when "o"
618 619 sql = "#{queried_table_name}.status_id IN (SELECT id FROM #{IssueStatus.table_name} WHERE is_closed=#{connection.quoted_false})" if field == "status_id"
619 620 when "c"
620 621 sql = "#{queried_table_name}.status_id IN (SELECT id FROM #{IssueStatus.table_name} WHERE is_closed=#{connection.quoted_true})" if field == "status_id"
621 622 when "><t-"
622 623 # between today - n days and today
623 624 sql = relative_date_clause(db_table, db_field, - value.first.to_i, 0)
624 625 when ">t-"
625 626 # >= today - n days
626 627 sql = relative_date_clause(db_table, db_field, - value.first.to_i, nil)
627 628 when "<t-"
628 629 # <= today - n days
629 630 sql = relative_date_clause(db_table, db_field, nil, - value.first.to_i)
630 631 when "t-"
631 632 # = n days in past
632 633 sql = relative_date_clause(db_table, db_field, - value.first.to_i, - value.first.to_i)
633 634 when "><t+"
634 635 # between today and today + n days
635 636 sql = relative_date_clause(db_table, db_field, 0, value.first.to_i)
636 637 when ">t+"
637 638 # >= today + n days
638 639 sql = relative_date_clause(db_table, db_field, value.first.to_i, nil)
639 640 when "<t+"
640 641 # <= today + n days
641 642 sql = relative_date_clause(db_table, db_field, nil, value.first.to_i)
642 643 when "t+"
643 644 # = today + n days
644 645 sql = relative_date_clause(db_table, db_field, value.first.to_i, value.first.to_i)
645 646 when "t"
646 647 # = today
647 648 sql = relative_date_clause(db_table, db_field, 0, 0)
648 649 when "ld"
649 650 # = yesterday
650 651 sql = relative_date_clause(db_table, db_field, -1, -1)
651 652 when "w"
652 653 # = this week
653 654 first_day_of_week = l(:general_first_day_of_week).to_i
654 655 day_of_week = Date.today.cwday
655 656 days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)
656 657 sql = relative_date_clause(db_table, db_field, - days_ago, - days_ago + 6)
657 658 when "lw"
658 659 # = last week
659 660 first_day_of_week = l(:general_first_day_of_week).to_i
660 661 day_of_week = Date.today.cwday
661 662 days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)
662 663 sql = relative_date_clause(db_table, db_field, - days_ago - 7, - days_ago - 1)
663 664 when "l2w"
664 665 # = last 2 weeks
665 666 first_day_of_week = l(:general_first_day_of_week).to_i
666 667 day_of_week = Date.today.cwday
667 668 days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)
668 669 sql = relative_date_clause(db_table, db_field, - days_ago - 14, - days_ago - 1)
669 670 when "m"
670 671 # = this month
671 672 date = Date.today
672 673 sql = date_clause(db_table, db_field, date.beginning_of_month, date.end_of_month)
673 674 when "lm"
674 675 # = last month
675 676 date = Date.today.prev_month
676 677 sql = date_clause(db_table, db_field, date.beginning_of_month, date.end_of_month)
677 678 when "y"
678 679 # = this year
679 680 date = Date.today
680 681 sql = date_clause(db_table, db_field, date.beginning_of_year, date.end_of_year)
681 682 when "~"
682 683 sql = "LOWER(#{db_table}.#{db_field}) LIKE '%#{connection.quote_string(value.first.to_s.downcase)}%'"
683 684 when "!~"
684 685 sql = "LOWER(#{db_table}.#{db_field}) NOT LIKE '%#{connection.quote_string(value.first.to_s.downcase)}%'"
685 686 else
686 687 raise "Unknown query operator #{operator}"
687 688 end
688 689
689 690 return sql
690 691 end
691 692
692 693 def add_custom_fields_filters(custom_fields, assoc=nil)
693 694 return unless custom_fields.present?
694 695 @available_filters ||= {}
695 696
696 697 custom_fields.select(&:is_filter?).each do |field|
697 698 case field.field_format
698 699 when "text"
699 700 options = { :type => :text, :order => 20 }
700 701 when "list"
701 702 options = { :type => :list_optional, :values => field.possible_values, :order => 20}
702 703 when "date"
703 704 options = { :type => :date, :order => 20 }
704 705 when "bool"
705 706 options = { :type => :list, :values => [[l(:general_text_yes), "1"], [l(:general_text_no), "0"]], :order => 20 }
706 707 when "int"
707 708 options = { :type => :integer, :order => 20 }
708 709 when "float"
709 710 options = { :type => :float, :order => 20 }
710 711 when "user", "version"
711 712 next unless project
712 713 values = field.possible_values_options(project)
713 714 if User.current.logged? && field.field_format == 'user'
714 715 values.unshift ["<< #{l(:label_me)} >>", "me"]
715 716 end
716 717 options = { :type => :list_optional, :values => values, :order => 20}
717 718 else
718 719 options = { :type => :string, :order => 20 }
719 720 end
720 721 filter_id = "cf_#{field.id}"
721 722 filter_name = field.name
722 723 if assoc.present?
723 724 filter_id = "#{assoc}.#{filter_id}"
724 725 filter_name = l("label_attribute_of_#{assoc}", :name => filter_name)
725 726 end
726 727 @available_filters[filter_id] = options.merge({
727 728 :name => filter_name,
728 729 :format => field.field_format,
729 730 :field => field
730 731 })
731 732 end
732 733 end
733 734
734 735 def add_associations_custom_fields_filters(*associations)
735 736 fields_by_class = CustomField.where(:is_filter => true).group_by(&:class)
736 737 associations.each do |assoc|
737 738 association_klass = queried_class.reflect_on_association(assoc).klass
738 739 fields_by_class.each do |field_class, fields|
739 740 if field_class.customized_class <= association_klass
740 741 add_custom_fields_filters(fields, assoc)
741 742 end
742 743 end
743 744 end
744 745 end
745 746
746 747 # Returns a SQL clause for a date or datetime field.
747 748 def date_clause(table, field, from, to)
748 749 s = []
749 750 if from
750 751 from_yesterday = from - 1
751 752 from_yesterday_time = Time.local(from_yesterday.year, from_yesterday.month, from_yesterday.day)
752 753 if self.class.default_timezone == :utc
753 754 from_yesterday_time = from_yesterday_time.utc
754 755 end
755 756 s << ("#{table}.#{field} > '%s'" % [connection.quoted_date(from_yesterday_time.end_of_day)])
756 757 end
757 758 if to
758 759 to_time = Time.local(to.year, to.month, to.day)
759 760 if self.class.default_timezone == :utc
760 761 to_time = to_time.utc
761 762 end
762 763 s << ("#{table}.#{field} <= '%s'" % [connection.quoted_date(to_time.end_of_day)])
763 764 end
764 765 s.join(' AND ')
765 766 end
766 767
767 768 # Returns a SQL clause for a date or datetime field using relative dates.
768 769 def relative_date_clause(table, field, days_from, days_to)
769 770 date_clause(table, field, (days_from ? Date.today + days_from : nil), (days_to ? Date.today + days_to : nil))
770 771 end
771 772
772 773 # Additional joins required for the given sort options
773 774 def joins_for_order_statement(order_options)
774 775 joins = []
775 776
776 777 if order_options
777 778 if order_options.include?('authors')
778 779 joins << "LEFT OUTER JOIN #{User.table_name} authors ON authors.id = #{queried_table_name}.author_id"
779 780 end
780 781 order_options.scan(/cf_\d+/).uniq.each do |name|
781 782 column = available_columns.detect {|c| c.name.to_s == name}
782 783 join = column && column.custom_field.join_for_order_statement
783 784 if join
784 785 joins << join
785 786 end
786 787 end
787 788 end
788 789
789 790 joins.any? ? joins.join(' ') : nil
790 791 end
791 792 end
General Comments 0
You need to be logged in to leave comments. Login now