query.rb 45.9 KB
Newer Older
1
# frozen_string_literal: true
2

3
# Redmine - project management software
jplang's avatar
jplang committed
4
# Copyright (C) 2006-2017  Jean-Philippe Lang
jplang's avatar
jplang committed
5 6 7 8 9
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation; either version 2
# of the License, or (at your option) any later version.
10
#
jplang's avatar
jplang committed
11 12 13 14
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
15
#
jplang's avatar
jplang committed
16 17 18 19
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.

20 21
require 'redmine/sort_criteria'

22
class QueryColumn
23
  attr_accessor :name, :sortable, :groupable, :totalable, :default_order
24
  include Redmine::I18n
25

26 27 28
  def initialize(name, options={})
    self.name = name
    self.sortable = options[:sortable]
jplang's avatar
jplang committed
29
    self.groupable = options[:groupable] || false
30 31 32
    if groupable == true
      self.groupable = name.to_s
    end
33
    self.totalable = options[:totalable] || false
34
    self.default_order = options[:default_order]
35
    @inline = options.key?(:inline) ? options[:inline] : true
36 37
    @caption_key = options[:caption] || "field_#{name}".to_sym
    @frozen = options[:frozen]
38
  end
39

40
  def caption
41 42 43 44 45 46 47 48
    case @caption_key
    when Symbol
      l(@caption_key)
    when Proc
      @caption_key.call
    else
      @caption_key
    end
49
  end
50

51 52
  # Returns true if the column is sortable, otherwise false
  def sortable?
53 54
    !@sortable.nil?
  end
55

56 57
  def sortable
    @sortable.is_a?(Proc) ? @sortable.call : @sortable
58
  end
59

60 61 62 63
  def inline?
    @inline
  end

64 65 66 67
  def frozen?
    @frozen
  end

68 69
  def value(object)
    object.send name
70
  end
71

72 73 74 75
  def value_object(object)
    object.send name
  end

76 77 78 79 80
  # Returns the group that object belongs to when grouping query results
  def group_value(object)
    value(object)
  end

81 82 83
  def css_classes
    name
  end
84 85
end

86 87 88 89 90 91 92 93 94 95 96 97 98 99 100
class TimestampQueryColumn < QueryColumn

  def groupable
    if @groupable
      Redmine::Database.timestamp_to_date(sortable, User.current.time_zone)
    end
  end

  def group_value(object)
    if time = value(object)
      User.current.time_to_date(time)
    end
  end
end

101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120
class QueryAssociationColumn < QueryColumn

  def initialize(association, attribute, options={})
    @association = association
    @attribute = attribute
    name_with_assoc = "#{association}.#{attribute}".to_sym
    super(name_with_assoc, options)
  end

  def value_object(object)
    if assoc = object.send(@association)
      assoc.send @attribute
    end
  end

  def css_classes
    @css_classes ||= "#{@association}-#{@attribute}"
  end
end

121 122
class QueryCustomFieldColumn < QueryColumn

jplang's avatar
jplang committed
123
  def initialize(custom_field, options={})
124
    self.name = "cf_#{custom_field.id}".to_sym
125
    self.sortable = custom_field.order_statement || false
jplang's avatar
jplang committed
126
    self.groupable = custom_field.group_statement || false
jplang's avatar
jplang committed
127
    self.totalable = options.key?(:totalable) ? !!options[:totalable] : custom_field.totalable?
128
    @inline = custom_field.full_width_layout? ? false : true
129 130
    @cf = custom_field
  end
131

132 133 134
  def caption
    @cf.name
  end
135

136 137 138
  def custom_field
    @cf
  end
139

140
  def value_object(object)
141
    if custom_field.visible_by?(object.project, User.current)
142
      cv = object.custom_values.select {|v| v.custom_field_id == @cf.id}
143
      cv.size > 1 ? cv.sort_by {|e| e.value.to_s} : cv.first
144 145 146 147 148 149 150 151 152 153 154
    else
      nil
    end
  end

  def value(object)
    raw = value_object(object)
    if raw.is_a?(Array)
      raw.map {|r| @cf.cast_value(r.value)}
    elsif raw
      @cf.cast_value(raw.value)
155 156 157
    else
      nil
    end
158
  end
159

160 161 162
  def css_classes
    @css_classes ||= "#{name} #{@cf.field_format}"
  end
163 164
end

165 166
class QueryAssociationCustomFieldColumn < QueryCustomFieldColumn

jplang's avatar
jplang committed
167 168
  def initialize(association, custom_field, options={})
    super(custom_field, options)
169 170 171 172 173 174 175
    self.name = "#{association}.cf_#{custom_field.id}".to_sym
    # TODO: support sorting/grouping by association custom field
    self.sortable = false
    self.groupable = false
    @association = association
  end

176
  def value_object(object)
177 178 179 180 181 182 183 184 185 186
    if assoc = object.send(@association)
      super(assoc)
    end
  end

  def css_classes
    @css_classes ||= "#{@association}_cf_#{@cf.id} #{@cf.field_format}"
  end
end

187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220
class QueryFilter
  include Redmine::I18n

  def initialize(field, options)
    @field = field.to_s
    @options = options
    @options[:name] ||= l(options[:label] || "field_#{field}".gsub(/_id$/, ''))
    # Consider filters with a Proc for values as remote by default
    @remote = options.key?(:remote) ? options[:remote] : options[:values].is_a?(Proc)
  end

  def [](arg)
    if arg == :values
      values
    else
      @options[arg]
    end
  end

  def values
    @values ||= begin
      values = @options[:values]
      if values.is_a?(Proc)
        values = values.call
      end
      values
    end
  end

  def remote
    @remote
  end
end

jplang's avatar
jplang committed
221
class Query < ActiveRecord::Base
222 223
  class StatementInvalid < ::ActiveRecord::StatementInvalid
  end
224

225 226
  include Redmine::SubclassFactory

jplang's avatar
jplang committed
227 228 229 230
  VISIBILITY_PRIVATE = 0
  VISIBILITY_ROLES   = 1
  VISIBILITY_PUBLIC  = 2

jplang's avatar
jplang committed
231 232
  belongs_to :project
  belongs_to :user
jplang's avatar
jplang committed
233
  has_and_belongs_to_many :roles, :join_table => "#{table_name_prefix}queries_roles#{table_name_suffix}", :foreign_key => "query_id"
jplang's avatar
jplang committed
234
  serialize :filters
235
  serialize :column_names
236
  serialize :sort_criteria, Array
237
  serialize :options, Hash
238

jplang's avatar
jplang committed
239
  validates_presence_of :name
jplang's avatar
jplang committed
240
  validates_length_of :name, :maximum => 255
jplang's avatar
jplang committed
241
  validates :visibility, :inclusion => { :in => [VISIBILITY_PUBLIC, VISIBILITY_ROLES, VISIBILITY_PRIVATE] }
242
  validate :validate_query_filters
jplang's avatar
jplang committed
243 244 245 246 247
  validate do |query|
    errors.add(:base, l(:label_role_plural) + ' ' + l('activerecord.errors.messages.blank')) if query.visibility == VISIBILITY_ROLES && roles.blank?
  end

  after_save do |query|
jplang's avatar
jplang committed
248
    if query.saved_change_to_visibility? && query.visibility != VISIBILITY_ROLES
249 250
      query.roles.clear
    end
jplang's avatar
jplang committed
251
  end
252

253 254 255 256 257 258 259 260 261 262 263 264 265 266 267
  class_attribute :operators
  self.operators = {
    "="   => :label_equals,
    "!"   => :label_not_equals,
    "o"   => :label_open_issues,
    "c"   => :label_closed_issues,
    "!*"  => :label_none,
    "*"   => :label_any,
    ">="  => :label_greater_or_equal,
    "<="  => :label_less_or_equal,
    "><"  => :label_between,
    "<t+" => :label_in_less_than,
    ">t+" => :label_in_more_than,
    "><t+"=> :label_in_the_next_days,
    "t+"  => :label_in,
268
    "nd"  => :label_tomorrow,
269
    "t"   => :label_today,
270
    "ld"  => :label_yesterday,
271
    "nw"  => :label_next_week,
272
    "w"   => :label_this_week,
273
    "lw"  => :label_last_week,
jplang's avatar
jplang committed
274
    "l2w" => [:label_last_n_weeks, {:count => 2}],
275
    "nm"  => :label_next_month,
276 277 278
    "m"   => :label_this_month,
    "lm"  => :label_last_month,
    "y"   => :label_this_year,
279 280 281 282 283 284 285 286
    ">t-" => :label_less_than_ago,
    "<t-" => :label_more_than_ago,
    "><t-"=> :label_in_the_past_days,
    "t-"  => :label_ago,
    "~"   => :label_contains,
    "!~"  => :label_not_contains,
    "=p"  => :label_any_issues_in_project,
    "=!p" => :label_any_issues_not_in_project,
287 288
    "!p"  => :label_no_issues_in_project,
    "*o"  => :label_any_open_issues,
289
    "!o"  => :label_no_open_issues,
290 291 292 293 294 295 296
  }

  class_attribute :operators_by_filter_type
  self.operators_by_filter_type = {
    :list => [ "=", "!" ],
    :list_status => [ "o", "=", "!", "c", "*" ],
    :list_optional => [ "=", "!", "!*", "*" ],
297
    :list_subprojects => [ "*", "!*", "=", "!" ],
298
    :date => [ "=", ">=", "<=", "><", "<t+", ">t+", "><t+", "t+", "nd", "t", "ld", "nw", "w", "lw", "l2w", "nm", "m", "lm", "y", ">t-", "<t-", "><t-", "t-", "!*", "*" ],
299
    :date_past => [ "=", ">=", "<=", "><", ">t-", "<t-", "><t-", "t-", "t", "ld", "w", "lw", "l2w", "m", "lm", "y", "!*", "*" ],
300
    :string => [ "~", "=", "!~", "!", "!*", "*" ],
301 302 303
    :text => [  "~", "!~", "!*", "*" ],
    :integer => [ "=", ">=", "<=", "><", "!*", "*" ],
    :float => [ "=", ">=", "<=", "><", "!*", "*" ],
304
    :relation => ["=", "=p", "=!p", "!p", "*o", "!o", "!*", "*"],
305
    :tree => ["=", "~", "!*", "*"]
306 307 308
  }

  class_attribute :available_columns
309
  self.available_columns = []
310

311 312
  class_attribute :queried_class

jplang's avatar
jplang committed
313 314 315
  # Permission required to view the queries, set on subclasses.
  class_attribute :view_permission

316 317 318 319 320 321 322
  # Scope of queries that are global or on the given project
  scope :global_or_on_project, lambda {|project|
    where(:project_id => (project.nil? ? nil : [nil, project.id]))
  }

  scope :sorted, lambda {order(:name, :id)}

jplang's avatar
jplang committed
323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346
  # Scope of visible queries, can be used from subclasses only.
  # Unlike other visible scopes, a class methods is used as it
  # let handle inheritance more nicely than scope DSL.
  def self.visible(*args)
    if self == ::Query
      # Visibility depends on permissions for each subclass,
      # raise an error if the scope is called from Query (eg. Query.visible)
      raise Exception.new("Cannot call .visible scope from the base Query class, but from subclasses only.")
    end

    user = args.shift || User.current
    base = Project.allowed_to_condition(user, view_permission, *args)
    scope = joins("LEFT OUTER JOIN #{Project.table_name} ON #{table_name}.project_id = #{Project.table_name}.id").
      where("#{table_name}.project_id IS NULL OR (#{base})")

    if user.admin?
      scope.where("#{table_name}.visibility <> ? OR #{table_name}.user_id = ?", VISIBILITY_PRIVATE, user.id)
    elsif user.memberships.any?
      scope.where("#{table_name}.visibility = ?" +
        " OR (#{table_name}.visibility = ? AND #{table_name}.id IN (" +
          "SELECT DISTINCT q.id FROM #{table_name} q" +
          " INNER JOIN #{table_name_prefix}queries_roles#{table_name_suffix} qr on qr.query_id = q.id" +
          " INNER JOIN #{MemberRole.table_name} mr ON mr.role_id = qr.role_id" +
          " INNER JOIN #{Member.table_name} m ON m.id = mr.member_id AND m.user_id = ?" +
347
          " INNER JOIN #{Project.table_name} p ON p.id = m.project_id AND p.status <> ?" +
jplang's avatar
jplang committed
348 349
          " WHERE q.project_id IS NULL OR q.project_id = m.project_id))" +
        " OR #{table_name}.user_id = ?",
350
        VISIBILITY_PUBLIC, VISIBILITY_ROLES, user.id, Project::STATUS_ARCHIVED, user.id)
jplang's avatar
jplang committed
351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368
    elsif user.logged?
      scope.where("#{table_name}.visibility = ? OR #{table_name}.user_id = ?", VISIBILITY_PUBLIC, user.id)
    else
      scope.where("#{table_name}.visibility = ?", VISIBILITY_PUBLIC)
    end
  end

  # Returns true if the query is visible to +user+ or the current user.
  def visible?(user=User.current)
    return true if user.admin?
    return false unless project.nil? || user.allowed_to?(self.class.view_permission, project)
    case visibility
    when VISIBILITY_PUBLIC
      true
    when VISIBILITY_ROLES
      if project
        (user.roles_for_project(project) & roles).any?
      else
369
        user.memberships.joins(:member_roles).where(:member_roles => {:role_id => roles.map(&:id)}).any?
jplang's avatar
jplang committed
370 371 372 373 374 375 376 377 378 379 380 381 382 383
      end
    else
      user == self.user
    end
  end

  def is_private?
    visibility == VISIBILITY_PRIVATE
  end

  def is_public?
    !is_private?
  end

384 385 386
  # Returns true if the query is available for all projects
  def is_global?
    new_record? ? project_id.nil? : project_id_in_database.nil?
387 388
  end

389 390
  def queried_table_name
    @queried_table_name ||= self.class.queried_class.table_name
391
  end
392

393
  # Builds the query from the given params
394
  def build_from_params(params, defaults={})
395 396 397 398
    if params[:fields] || params[:f]
      self.filters = {}
      add_filters(params[:fields] || params[:f], params[:operators] || params[:op], params[:values] || params[:v])
    else
399
      available_filters.each_key do |field|
400 401 402
        add_short_filter(field, params[field]) if params[field]
      end
    end
403 404

    query_params = params[:query] || defaults || {}
405 406 407 408
    self.group_by = params[:group_by] || query_params[:group_by] || self.group_by
    self.column_names = params[:c] || query_params[:column_names] || self.column_names
    self.totalable_names = params[:t] || query_params[:totalable_names] || self.totalable_names
    self.sort_criteria = params[:sort] || query_params[:sort_criteria] || self.sort_criteria
409 410 411
    self
  end

412 413 414 415 416
  # Builds a new query from the given params and attributes
  def self.build_from_params(params, attributes={})
    new(attributes).build_from_params(params)
  end

417
  def as_params
418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433
    if new_record?
      params = {}
      filters.each do |field, options|
        params[:f] ||= []
        params[:f] << field
        params[:op] ||= {}
        params[:op][field] = options[:operator]
        params[:v] ||= {}
        params[:v][field] = options[:values]
      end
      params[:c] = column_names
      params[:sort] = sort_criteria.to_param
      params[:set_filter] = 1
      params
    else
      {:query_id => id}
434 435 436
    end
  end

437
  def validate_query_filters
jplang's avatar
jplang committed
438
    filters.each_key do |field|
jplang's avatar
jplang committed
439 440
      if values_for(field)
        case type_for(field)
441
        when :integer
jplang's avatar
jplang committed
442
          add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && !v.match(/\A[+-]?\d+(,[+-]?\d+)*\z/) }
443
        when :float
jplang's avatar
jplang committed
444
          add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && !v.match(/\A[+-]?\d+(\.\d*)?\z/) }
jplang's avatar
jplang committed
445 446 447
        when :date, :date_past
          case operator_for(field)
          when "=", ">=", "<=", "><"
448
            add_filter_error(field, :invalid) if values_for(field).detect {|v|
449
              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?)
450
            }
451
          when ">t-", "<t-", "t-", ">t+", "<t+", "t+", "><t+", "><t-"
452
            add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && !v.match(/^\d+$/) }
jplang's avatar
jplang committed
453
          end
jplang's avatar
jplang committed
454
        end
455
      end
456

457
      add_filter_error(field, :blank) unless
jplang's avatar
jplang committed
458
          # filter requires one or more values
459
          (values_for(field) and !values_for(field).first.blank?) or
jplang's avatar
jplang committed
460
          # filter doesn't require any value
461
          ["o", "c", "!*", "*", "nd", "t", "ld", "nw", "w", "lw", "l2w", "nm", "m", "lm", "y", "*o", "!o"].include? operator_for(field)
jplang's avatar
jplang committed
462 463
    end if filters
  end
464

465 466 467 468 469
  def add_filter_error(field, message)
    m = label_for(field) + " " + l(message, :scope => 'activerecord.errors.messages')
    errors.add(:base, m)
  end

jplang's avatar
jplang committed
470 471
  def editable_by?(user)
    return false unless user
472
    # Admin can edit them all and regular users can edit their private queries
jplang's avatar
jplang committed
473
    return true if user.admin? || (is_private? && self.user_id == user.id)
474
    # Members can not edit public queries that are for all project (only admin is allowed to)
475
    is_public? && !is_global? && user.allowed_to?(:manage_public_queries, project)
jplang's avatar
jplang committed
476
  end
477

478
  def trackers
479
    @trackers ||= (project.nil? ? Tracker.all : project.rolled_up_trackers).visible.sorted
480 481
  end

jplang's avatar
jplang committed
482 483
  # Returns a hash of localized labels for all filter operators
  def self.operators_labels
484
    operators.inject({}) {|h, operator| h[operator.first] = l(*operator.last); h}
jplang's avatar
jplang committed
485 486
  end

487
  # Returns a representation of the available filters for JSON serialization
jplang's avatar
jplang committed
488 489
  def available_filters_as_json
    json = {}
490 491 492 493 494 495 496 497 498 499 500
    available_filters.each do |field, filter|
      options = {:type => filter[:type], :name => filter[:name]}
      options[:remote] = true if filter.remote

      if has_filter?(field) || !filter.remote
        options[:values] = filter.values
        if options[:values] && values_for(field)
          missing = Array(values_for(field)).select(&:present?) - options[:values].map(&:last)
          if missing.any? && respond_to?(method = "find_#{field}_filter_values")
            options[:values] += send(method, missing)
          end
501 502 503
        end
      end
      json[field] = options.stringify_keys
jplang's avatar
jplang committed
504 505 506 507
    end
    json
  end

508
  def all_projects
jplang's avatar
jplang committed
509
    @all_projects ||= Project.visible.to_a
510 511 512 513 514 515 516 517 518 519 520 521 522
  end

  def all_projects_values
    return @all_projects_values if @all_projects_values

    values = []
    Project.project_tree(all_projects) do |p, level|
      prefix = (level > 0 ? ('--' * level + ' ') : '')
      values << ["#{prefix}#{p.name}", p.id.to_s]
    end
    @all_projects_values = values
  end

523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539
  def project_values
    project_values = []
    if User.current.logged? && User.current.memberships.any?
      project_values << ["<< #{l(:label_my_projects).downcase} >>", "mine"]
    end
    project_values += all_projects_values
    project_values
  end

  def subproject_values
    project.descendants.visible.collect{|s| [s.name, s.id.to_s] }
  end

  def principals
    @principal ||= begin
      principals = []
      if project
540
        principals += Principal.member_of(project).visible
541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560
        unless project.leaf?
          principals += Principal.member_of(project.descendants.visible).visible
        end
      else
        principals += Principal.member_of(all_projects).visible
      end
      principals.uniq!
      principals.sort!
      principals.reject! {|p| p.is_a?(GroupBuiltin)}
      principals
    end
  end

  def users
    principals.select {|p| p.is_a?(User)}
  end

  def author_values
    author_values = []
    author_values << ["<< #{l(:label_me)} >>", "me"] if User.current.logged?
561
    author_values += users.sort_by(&:status).collect{|s| [s.name, s.id.to_s, l("status_#{User::LABEL_BY_STATUS[s.status]}")] }
562 563 564 565 566 567
    author_values
  end

  def assigned_to_values
    assigned_to_values = []
    assigned_to_values << ["<< #{l(:label_me)} >>", "me"] if User.current.logged?
568
    assigned_to_values += (Setting.issue_group_assignment? ? principals : users).sort_by(&:status).collect{|s| [s.name, s.id.to_s, l("status_#{User::LABEL_BY_STATUS[s.status]}")] }
569 570 571 572 573 574 575 576
    assigned_to_values
  end

  def fixed_version_values
    versions = []
    if project
      versions = project.shared_versions.to_a
    else
577
      versions = Version.visible.to_a
578 579 580 581
    end
    Version.sort_by_status(versions).collect{|s| ["#{s.project.name} - #{s.name}", s.id.to_s, l("version_status_#{s.status}")] }
  end

582
  # Returns a scope of issue statuses that are available as columns for filters
583
  def issue_statuses_values
584 585 586 587 588 589 590 591
    if project
      statuses = project.rolled_up_statuses
    else
      statuses = IssueStatus.all.sorted
    end
    statuses.collect{|s| [s.name, s.id.to_s]}
  end

592 593 594 595 596 597
  def watcher_values
    watcher_values = [["<< #{l(:label_me)} >>", "me"]]
    watcher_values += users.sort_by(&:status).collect{|s| [s.name, s.id.to_s, l("status_#{User::LABEL_BY_STATUS[s.status]}")] } if User.current.allowed_to?(:view_issue_watchers, self.project)
    watcher_values
  end

598 599 600 601 602 603 604 605 606
  # Returns a scope of issue custom fields that are available as columns or filters
  def issue_custom_fields
    if project
      project.rolled_up_custom_fields
    else
      IssueCustomField.all
    end
  end

607 608
  # Returns a scope of project statuses that are available as columns or filters
  def project_statuses_values
609 610 611 612
    [
      [l(:project_status_active), "#{Project::STATUS_ACTIVE}"],
      [l(:project_status_closed), "#{Project::STATUS_CLOSED}"]
    ]
613 614
  end

615 616 617 618 619 620 621 622 623
  # Adds available filters
  def initialize_available_filters
    # implemented by sub-classes
  end
  protected :initialize_available_filters

  # Adds an available filter
  def add_available_filter(field, options)
    @available_filters ||= ActiveSupport::OrderedHash.new
624
    @available_filters[field] = QueryFilter.new(field, options)
625 626 627 628 629 630 631 632 633 634 635 636 637 638
    @available_filters
  end

  # Removes an available filter
  def delete_available_filter(field)
    if @available_filters
      @available_filters.delete(field)
    end
  end

  # Return a hash of available filters
  def available_filters
    unless @available_filters
      initialize_available_filters
639
      @available_filters ||= {}
640 641 642 643
    end
    @available_filters
  end

644
  def add_filter(field, operator, values=nil)
jplang's avatar
jplang committed
645
    # values must be an array
646
    return unless values.nil? || values.is_a?(Array)
jplang's avatar
jplang committed
647 648
    # check if field is defined as an available filter
    if available_filters.has_key? field
649
      filters[field] = {:operator => operator, :values => (values || [''])}
jplang's avatar
jplang committed
650 651
    end
  end
652

jplang's avatar
jplang committed
653
  def add_short_filter(field, expression)
654 655
    return unless expression && available_filters.has_key?(field)
    field_type = available_filters[field][:type]
656
    operators_by_filter_type[field_type].sort.reverse.detect do |operator|
657
      next unless expression =~ /^#{Regexp.escape(operator)}(.*)$/
658 659
      values = $1
      add_filter field, operator, values.present? ? values.split('|') : ['']
660
    end || add_filter(field, '=', expression.to_s.split('|'))
jplang's avatar
jplang committed
661
  end
662 663 664

  # Add multiple filters using +add_filter+
  def add_filters(fields, operators, values)
jplang's avatar
jplang committed
665
    if fields.present? && operators.present?
666
      fields.each do |field|
667
        add_filter(field, operators[field], values && values[field])
668
      end
669 670
    end
  end
671

jplang's avatar
jplang committed
672 673 674
  def has_filter?(field)
    filters and filters[field]
  end
675

676 677 678
  def type_for(field)
    available_filters[field][:type] if available_filters.has_key?(field)
  end
679

jplang's avatar
jplang committed
680 681 682
  def operator_for(field)
    has_filter?(field) ? filters[field][:operator] : nil
  end
683

jplang's avatar
jplang committed
684 685 686
  def values_for(field)
    has_filter?(field) ? filters[field][:values] : nil
  end
687

688 689 690
  def value_for(field, index=0)
    (values_for(field) || [])[index]
  end
691

692
  def label_for(field)
693
    label = available_filters[field][:name] if available_filters.has_key?(field)
jplang's avatar
jplang committed
694
    label ||= queried_class.human_attribute_name(field, :default => field)
695
  end
696

697 698 699
  def self.add_available_column(column)
    self.available_columns << (column) if column.is_a?(QueryColumn)
  end
700

jplang's avatar
jplang committed
701 702 703 704
  # Returns an array of columns that can be used to group the results
  def groupable_columns
    available_columns.select {|c| c.groupable}
  end
705 706 707

  # Returns a Hash of columns and the key for sorting
  def sortable_columns
708 709 710 711
    available_columns.inject({}) {|h, column|
      h[column.name.to_s] = column.sortable
      h
    }
712
  end
713

714
  def columns
715
    # preserve the column_names order
716
    cols = (has_default_columns? ? default_columns_names : column_names).collect do |name|
717 718
       available_columns.find { |col| col.name == name }
    end.compact
719
    available_columns.select(&:frozen?) | cols
720 721
  end

722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737
  def inline_columns
    columns.select(&:inline?)
  end

  def block_columns
    columns.reject(&:inline?)
  end

  def available_inline_columns
    available_columns.select(&:inline?)
  end

  def available_block_columns
    available_columns.reject(&:inline?)
  end

738 739 740 741
  def available_totalable_columns
    available_columns.select(&:totalable)
  end

742
  def default_columns_names
743
    []
744
  end
745

jplang's avatar
jplang committed
746 747 748 749
  def default_totalable_names
    []
  end

750
  def column_names=(names)
751 752 753
    if names
      names = names.select {|n| n.is_a?(Symbol) || !n.blank? }
      names = names.collect {|n| n.is_a?(Symbol) ? n : n.to_sym }
754 755 756
      if names.delete(:all_inline)
        names = available_inline_columns.map(&:name) | names
      end
757
      # Set column_names to nil if default columns
758
      if names == default_columns_names
759 760 761
        names = nil
      end
    end
762 763
    write_attribute(:column_names, names)
  end
764

765
  def has_column?(column)
766 767
    name = column.is_a?(QueryColumn) ? column.name : column
    columns.detect {|c| c.name == name}
768
  end
769

770 771 772 773
  def has_custom_field_column?
    columns.any? {|column| column.is_a? QueryCustomFieldColumn}
  end

774 775 776
  def has_default_columns?
    column_names.nil? || column_names.empty?
  end
777

778 779 780 781 782 783 784 785 786 787 788 789 790
  def totalable_columns
    names = totalable_names
    available_totalable_columns.select {|column| names.include?(column.name)}
  end

  def totalable_names=(names)
    if names
      names = names.select(&:present?).map {|n| n.is_a?(Symbol) ? n : n.to_sym}
    end
    options[:totalable_names] = names
  end

  def totalable_names
jplang's avatar
jplang committed
791
    options[:totalable_names] || default_totalable_names || []
792 793
  end

794 795 796 797
  def default_sort_criteria
    []
  end

798
  def sort_criteria=(arg)
799 800 801
    c = Redmine::SortCriteria.new(arg)
    write_attribute(:sort_criteria, c.to_a)
    c
802
  end
803

804
  def sort_criteria
805 806 807 808 809
    c = read_attribute(:sort_criteria)
    if c.blank?
      c = default_sort_criteria
    end
    Redmine::SortCriteria.new(c)
810
  end
811

812 813
  def sort_criteria_key(index)
    sort_criteria[index].try(:first)
814
  end
815

816 817
  def sort_criteria_order(index)
    sort_criteria[index].try(:last)
818
  end
819

820
  def sort_clause
821 822 823
    if clause = sort_criteria.sort_clause(sortable_columns)
      clause.map {|c| Arel.sql c}
    end
824 825
  end

jplang's avatar
jplang committed
826 827
  # Returns the SQL sort order that should be prepended for grouping
  def group_by_sort_order
jplang's avatar
jplang committed
828
    if column = group_by_column
829
      order = (sort_criteria.order_for(column.name) || column.default_order || 'asc').try(:upcase)
830
      Array(column.sortable).map {|s| Arel.sql("#{s} #{order}")}
jplang's avatar
jplang committed
831 832
    end
  end
833

jplang's avatar
jplang committed
834 835
  # Returns true if the query is a grouped query
  def grouped?
836
    !group_by_column.nil?
jplang's avatar
jplang committed
837
  end
838

jplang's avatar
jplang committed
839
  def group_by_column
840
    groupable_columns.detect {|c| c.groupable && c.name.to_s == group_by}
jplang's avatar
jplang committed
841
  end
842

843
  def group_by_statement
844
    group_by_column.try(:groupable)
845
  end
846

847
  def project_statement
848
    project_clauses = []
849 850 851 852
    active_subprojects_ids = []

    active_subprojects_ids = project.descendants.active.map(&:id) if project
    if active_subprojects_ids.any?
853 854 855 856
      if has_filter?("subproject_id")
        case operator_for("subproject_id")
        when '='
          # include the selected subprojects
857 858 859 860 861
          ids = [project.id] + values_for("subproject_id").map(&:to_i)
          project_clauses << "#{Project.table_name}.id IN (%s)" % ids.join(',')
        when '!'
          # exclude the selected subprojects
          ids = [project.id] + active_subprojects_ids - values_for("subproject_id").map(&:to_i)
862
          project_clauses << "#{Project.table_name}.id IN (%s)" % ids.join(',')
863 864
        when '!*'
          # main project only
865
          project_clauses << "#{Project.table_name}.id = %d" % project.id
866 867
        else
          # all subprojects
868
          project_clauses << "#{Project.table_name}.lft >= #{project.lft} AND #{Project.table_name}.rgt <= #{project.rgt}"
869 870
        end
      elsif Setting.display_subprojects_issues?
871 872 873
        project_clauses << "#{Project.table_name}.lft >= #{project.lft} AND #{Project.table_name}.rgt <= #{project.rgt}"
      else
        project_clauses << "#{Project.table_name}.id = %d" % project.id
874
      end
875
    elsif project
876
      project_clauses << "#{Project.table_name}.id = %d" % project.id
877
    end
878
    project_clauses.any? ? project_clauses.join(' AND ') : nil
879 880 881
  end

  def statement
882 883
    # filters clauses
    filters_clauses = []
jplang's avatar
jplang committed
884
    filters.each_key do |field|
885
      next if field == "subproject_id"
886
      v = values_for(field).clone
887
      next unless v and !v.empty?
888
      operator = operator_for(field)
889

890
      # "me" value substitution
891
      if %w(assigned_to_id author_id user_id watcher_id updated_by last_updated_by).include?(field)
892 893 894 895 896 897 898 899
        if v.delete("me")
          if User.current.logged?
            v.push(User.current.id.to_s)
            v += User.current.group_ids.map(&:to_s) if field == 'assigned_to_id'
          else
            v.push("0")
          end
        end
900
      end
901

902 903 904 905 906 907
      if field == 'project_id'
        if v.delete('mine')
          v += User.current.memberships.map(&:project_id).map(&:to_s)
        end
      end