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