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