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