query.rb 44 KB
Newer Older
1
# Redmine - project management software
jplang's avatar
jplang committed
2
# Copyright (C) 2006-2017  Jean-Philippe Lang
jplang's avatar
jplang committed
3 4 5 6 7
#
# 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.
8
#
jplang's avatar
jplang committed
9 10 11 12
# 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.
13
#
jplang's avatar
jplang committed
14 15 16 17
# 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.

18 19
require 'redmine/sort_criteria'

20
class QueryColumn
21
  attr_accessor :name, :sortable, :groupable, :totalable, :default_order
22
  include Redmine::I18n
23

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

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

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

54 55
  def sortable
    @sortable.is_a?(Proc) ? @sortable.call : @sortable
56
  end
57

58 59 60 61
  def inline?
    @inline
  end

62 63 64 65
  def frozen?
    @frozen
  end

66 67
  def value(object)
    object.send name
68
  end
69

70 71 72 73
  def value_object(object)
    object.send name
  end

74 75 76
  def css_classes
    name
  end
77 78
end

79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98
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

99 100
class QueryCustomFieldColumn < QueryColumn

jplang's avatar
jplang committed
101
  def initialize(custom_field, options={})
102
    self.name = "cf_#{custom_field.id}".to_sym
103
    self.sortable = custom_field.order_statement || false
jplang's avatar
jplang committed
104
    self.groupable = custom_field.group_statement || false
jplang's avatar
jplang committed
105
    self.totalable = options.key?(:totalable) ? !!options[:totalable] : custom_field.totalable?
106
    @inline = true
107 108
    @cf = custom_field
  end
109

110 111 112
  def caption
    @cf.name
  end
113

114 115 116
  def custom_field
    @cf
  end
117

118
  def value_object(object)
119
    if custom_field.visible_by?(object.project, User.current)
120 121 122 123 124 125 126 127 128 129 130 131 132
      cv = object.custom_values.select {|v| v.custom_field_id == @cf.id}
      cv.size > 1 ? cv.sort {|a,b| a.value.to_s <=> b.value.to_s} : cv.first
    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)
133 134 135
    else
      nil
    end
136
  end
137

138 139 140
  def css_classes
    @css_classes ||= "#{name} #{@cf.field_format}"
  end
141 142
end

143 144
class QueryAssociationCustomFieldColumn < QueryCustomFieldColumn

jplang's avatar
jplang committed
145 146
  def initialize(association, custom_field, options={})
    super(custom_field, options)
147 148 149 150 151 152 153
    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

154
  def value_object(object)
155 156 157 158 159 160 161 162 163 164
    if assoc = object.send(@association)
      super(assoc)
    end
  end

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

165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198
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
199
class Query < ActiveRecord::Base
200 201
  class StatementInvalid < ::ActiveRecord::StatementInvalid
  end
202

203 204
  include Redmine::SubclassFactory

jplang's avatar
jplang committed
205 206 207 208
  VISIBILITY_PRIVATE = 0
  VISIBILITY_ROLES   = 1
  VISIBILITY_PUBLIC  = 2

jplang's avatar
jplang committed
209 210
  belongs_to :project
  belongs_to :user
jplang's avatar
jplang committed
211
  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
212
  serialize :filters
213
  serialize :column_names
214
  serialize :sort_criteria, Array
215
  serialize :options, Hash
216

jplang's avatar
jplang committed
217
  validates_presence_of :name
jplang's avatar
jplang committed
218
  validates_length_of :name, :maximum => 255
jplang's avatar
jplang committed
219
  validates :visibility, :inclusion => { :in => [VISIBILITY_PUBLIC, VISIBILITY_ROLES, VISIBILITY_PRIVATE] }
220
  validate :validate_query_filters
jplang's avatar
jplang committed
221 222 223 224 225
  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
226
    if query.saved_change_to_visibility? && query.visibility != VISIBILITY_ROLES
227 228
      query.roles.clear
    end
jplang's avatar
jplang committed
229
  end
230

231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246
  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,
    "t"   => :label_today,
247
    "ld"  => :label_yesterday,
248
    "w"   => :label_this_week,
249
    "lw"  => :label_last_week,
jplang's avatar
jplang committed
250
    "l2w" => [:label_last_n_weeks, {:count => 2}],
251 252 253
    "m"   => :label_this_month,
    "lm"  => :label_last_month,
    "y"   => :label_this_year,
254 255 256 257 258 259 260 261
    ">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,
262 263 264
    "!p"  => :label_no_issues_in_project,
    "*o"  => :label_any_open_issues,
    "!o"  => :label_no_open_issues
265 266 267 268 269 270 271
  }

  class_attribute :operators_by_filter_type
  self.operators_by_filter_type = {
    :list => [ "=", "!" ],
    :list_status => [ "o", "=", "!", "c", "*" ],
    :list_optional => [ "=", "!", "!*", "*" ],
272
    :list_subprojects => [ "*", "!*", "=", "!" ],
273 274
    :date => [ "=", ">=", "<=", "><", "<t+", ">t+", "><t+", "t+", "t", "ld", "w", "lw", "l2w", "m", "lm", "y", ">t-", "<t-", "><t-", "t-", "!*", "*" ],
    :date_past => [ "=", ">=", "<=", "><", ">t-", "<t-", "><t-", "t-", "t", "ld", "w", "lw", "l2w", "m", "lm", "y", "!*", "*" ],
275 276 277 278
    :string => [ "=", "~", "!", "!~", "!*", "*" ],
    :text => [  "~", "!~", "!*", "*" ],
    :integer => [ "=", ">=", "<=", "><", "!*", "*" ],
    :float => [ "=", ">=", "<=", "><", "!*", "*" ],
279
    :relation => ["=", "=p", "=!p", "!p", "*o", "!o", "!*", "*"],
280
    :tree => ["=", "~", "!*", "*"]
281 282 283
  }

  class_attribute :available_columns
284
  self.available_columns = []
285

286 287
  class_attribute :queried_class

jplang's avatar
jplang committed
288 289 290
  # Permission required to view the queries, set on subclasses.
  class_attribute :view_permission

291 292 293 294 295 296 297
  # 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
298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321
  # 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 = ?" +
322
          " INNER JOIN #{Project.table_name} p ON p.id = m.project_id AND p.status <> ?" +
jplang's avatar
jplang committed
323 324
          " WHERE q.project_id IS NULL OR q.project_id = m.project_id))" +
        " OR #{table_name}.user_id = ?",
325
        VISIBILITY_PUBLIC, VISIBILITY_ROLES, user.id, Project::STATUS_ARCHIVED, user.id)
jplang's avatar
jplang committed
326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343
    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
344
        user.memberships.joins(:member_roles).where(:member_roles => {:role_id => roles.map(&:id)}).any?
jplang's avatar
jplang committed
345 346 347 348 349 350 351 352 353 354 355 356 357 358
      end
    else
      user == self.user
    end
  end

  def is_private?
    visibility == VISIBILITY_PRIVATE
  end

  def is_public?
    !is_private?
  end

359 360 361
  # Returns true if the query is available for all projects
  def is_global?
    new_record? ? project_id.nil? : project_id_in_database.nil?
362 363
  end

364 365
  def queried_table_name
    @queried_table_name ||= self.class.queried_class.table_name
366
  end
367

368
  # Builds the query from the given params
369
  def build_from_params(params, defaults={})
370 371 372 373 374 375 376 377
    if params[:fields] || params[:f]
      self.filters = {}
      add_filters(params[:fields] || params[:f], params[:operators] || params[:op], params[:values] || params[:v])
    else
      available_filters.keys.each do |field|
        add_short_filter(field, params[field]) if params[field]
      end
    end
378 379 380 381 382 383

    query_params = params[:query] || defaults || {}
    self.group_by = params[:group_by] || query_params[:group_by]
    self.column_names = params[:c] || query_params[:column_names]
    self.totalable_names = params[:t] || query_params[:totalable_names]
    self.sort_criteria = params[:sort] || query_params[:sort_criteria]
384 385 386
    self
  end

387 388 389 390 391
  # Builds a new query from the given params and attributes
  def self.build_from_params(params, attributes={})
    new(attributes).build_from_params(params)
  end

392
  def as_params
393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408
    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}
409 410 411
    end
  end

412
  def validate_query_filters
jplang's avatar
jplang committed
413
    filters.each_key do |field|
jplang's avatar
jplang committed
414 415
      if values_for(field)
        case type_for(field)
416
        when :integer
jplang's avatar
jplang committed
417
          add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && !v.match(/\A[+-]?\d+(,[+-]?\d+)*\z/) }
418
        when :float
jplang's avatar
jplang committed
419
          add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && !v.match(/\A[+-]?\d+(\.\d*)?\z/) }
jplang's avatar
jplang committed
420 421 422
        when :date, :date_past
          case operator_for(field)
          when "=", ">=", "<=", "><"
423
            add_filter_error(field, :invalid) if values_for(field).detect {|v|
424
              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?)
425
            }
426
          when ">t-", "<t-", "t-", ">t+", "<t+", "t+", "><t+", "><t-"
427
            add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && !v.match(/^\d+$/) }
jplang's avatar
jplang committed
428
          end
jplang's avatar
jplang committed
429
        end
430
      end
431

432
      add_filter_error(field, :blank) unless
jplang's avatar
jplang committed
433
          # filter requires one or more values
434
          (values_for(field) and !values_for(field).first.blank?) or
jplang's avatar
jplang committed
435
          # filter doesn't require any value
436
          ["o", "c", "!*", "*", "t", "ld", "w", "lw", "l2w", "m", "lm", "y", "*o", "!o"].include? operator_for(field)
jplang's avatar
jplang committed
437 438
    end if filters
  end
439

440 441 442 443 444
  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
445 446
  def editable_by?(user)
    return false unless user
447
    # Admin can edit them all and regular users can edit their private queries
jplang's avatar
jplang committed
448
    return true if user.admin? || (is_private? && self.user_id == user.id)
449
    # Members can not edit public queries that are for all project (only admin is allowed to)
450
    is_public? && !is_global? && user.allowed_to?(:manage_public_queries, project)
jplang's avatar
jplang committed
451
  end
452

453
  def trackers
454
    @trackers ||= (project.nil? ? Tracker.all : project.rolled_up_trackers).visible.sorted
455 456
  end

jplang's avatar
jplang committed
457 458
  # Returns a hash of localized labels for all filter operators
  def self.operators_labels
459
    operators.inject({}) {|h, operator| h[operator.first] = l(*operator.last); h}
jplang's avatar
jplang committed
460 461
  end

462
  # Returns a representation of the available filters for JSON serialization
jplang's avatar
jplang committed
463 464
  def available_filters_as_json
    json = {}
465 466 467 468 469 470 471 472 473 474 475
    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
476 477 478
        end
      end
      json[field] = options.stringify_keys
jplang's avatar
jplang committed
479 480 481 482
    end
    json
  end

483
  def all_projects
jplang's avatar
jplang committed
484
    @all_projects ||= Project.visible.to_a
485 486 487 488 489 490 491 492 493 494 495 496 497
  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

498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556
  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
        principals += project.principals.visible
        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?
    author_values += users.collect{|s| [s.name, s.id.to_s] }
    author_values
  end

  def assigned_to_values
    assigned_to_values = []
    assigned_to_values << ["<< #{l(:label_me)} >>", "me"] if User.current.logged?
    assigned_to_values += (Setting.issue_group_assignment? ? principals : users).collect{|s| [s.name, s.id.to_s] }
    assigned_to_values
  end

  def fixed_version_values
    versions = []
    if project
      versions = project.shared_versions.to_a
    else
      versions = Version.visible.where(:sharing => 'system').to_a
    end
    Version.sort_by_status(versions).collect{|s| ["#{s.project.name} - #{s.name}", s.id.to_s, l("version_status_#{s.status}")] }
  end

557
  # Returns a scope of issue statuses that are available as columns for filters
558
  def issue_statuses_values
559 560 561 562 563 564 565 566
    if project
      statuses = project.rolled_up_statuses
    else
      statuses = IssueStatus.all.sorted
    end
    statuses.collect{|s| [s.name, s.id.to_s]}
  end

567 568 569 570 571 572 573 574 575
  # 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

576 577 578 579 580 581 582 583 584
  # 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
585
    @available_filters[field] = QueryFilter.new(field, options)
586 587 588 589 590 591 592 593 594 595 596 597 598 599
    @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
600
      @available_filters ||= {}
601 602 603 604
    end
    @available_filters
  end

605
  def add_filter(field, operator, values=nil)
jplang's avatar
jplang committed
606
    # values must be an array
607
    return unless values.nil? || values.is_a?(Array)
jplang's avatar
jplang committed
608 609 610
    # check if field is defined as an available filter
    if available_filters.has_key? field
      filter_options = available_filters[field]
611
      filters[field] = {:operator => operator, :values => (values || [''])}
jplang's avatar
jplang committed
612 613
    end
  end
614

jplang's avatar
jplang committed
615
  def add_short_filter(field, expression)
616 617
    return unless expression && available_filters.has_key?(field)
    field_type = available_filters[field][:type]
618
    operators_by_filter_type[field_type].sort.reverse.detect do |operator|
619
      next unless expression =~ /^#{Regexp.escape(operator)}(.*)$/
620 621
      values = $1
      add_filter field, operator, values.present? ? values.split('|') : ['']
622
    end || add_filter(field, '=', expression.to_s.split('|'))
jplang's avatar
jplang committed
623
  end
624 625 626

  # Add multiple filters using +add_filter+
  def add_filters(fields, operators, values)
jplang's avatar
jplang committed
627
    if fields.present? && operators.present?
628
      fields.each do |field|
629
        add_filter(field, operators[field], values && values[field])
630
      end
631 632
    end
  end
633

jplang's avatar
jplang committed
634 635 636
  def has_filter?(field)
    filters and filters[field]
  end
637

638 639 640
  def type_for(field)
    available_filters[field][:type] if available_filters.has_key?(field)
  end
641

jplang's avatar
jplang committed
642 643 644
  def operator_for(field)
    has_filter?(field) ? filters[field][:operator] : nil
  end
645

jplang's avatar
jplang committed
646 647 648
  def values_for(field)
    has_filter?(field) ? filters[field][:values] : nil
  end
649

650 651 652
  def value_for(field, index=0)
    (values_for(field) || [])[index]
  end
653

654
  def label_for(field)
655
    label = available_filters[field][:name] if available_filters.has_key?(field)
jplang's avatar
jplang committed
656
    label ||= queried_class.human_attribute_name(field, :default => field)
657
  end
658

659 660 661
  def self.add_available_column(column)
    self.available_columns << (column) if column.is_a?(QueryColumn)
  end
662

jplang's avatar
jplang committed
663 664 665 666
  # Returns an array of columns that can be used to group the results
  def groupable_columns
    available_columns.select {|c| c.groupable}
  end
667 668 669

  # Returns a Hash of columns and the key for sorting
  def sortable_columns
670 671 672 673
    available_columns.inject({}) {|h, column|
      h[column.name.to_s] = column.sortable
      h
    }
674
  end
675

676
  def columns
677
    # preserve the column_names order
678
    cols = (has_default_columns? ? default_columns_names : column_names).collect do |name|
679 680
       available_columns.find { |col| col.name == name }
    end.compact
681
    available_columns.select(&:frozen?) | cols
682 683
  end

684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699
  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

700 701 702 703
  def available_totalable_columns
    available_columns.select(&:totalable)
  end

704
  def default_columns_names
705
    []
706
  end
707

jplang's avatar
jplang committed
708 709 710 711
  def default_totalable_names
    []
  end

712
  def column_names=(names)
713 714 715
    if names
      names = names.select {|n| n.is_a?(Symbol) || !n.blank? }
      names = names.collect {|n| n.is_a?(Symbol) ? n : n.to_sym }
716 717 718
      if names.delete(:all_inline)
        names = available_inline_columns.map(&:name) | names
      end
719
      # Set column_names to nil if default columns
720
      if names == default_columns_names
721 722 723
        names = nil
      end
    end
724 725
    write_attribute(:column_names, names)
  end
726

727
  def has_column?(column)
728 729
    name = column.is_a?(QueryColumn) ? column.name : column
    columns.detect {|c| c.name == name}
730
  end
731

732 733 734 735
  def has_custom_field_column?
    columns.any? {|column| column.is_a? QueryCustomFieldColumn}
  end

736 737 738
  def has_default_columns?
    column_names.nil? || column_names.empty?
  end
739

740 741 742 743 744 745 746 747 748 749 750 751 752
  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
753
    options[:totalable_names] || default_totalable_names || []
754 755
  end

756 757 758 759
  def default_sort_criteria
    []
  end

760
  def sort_criteria=(arg)
761 762 763
    c = Redmine::SortCriteria.new(arg)
    write_attribute(:sort_criteria, c.to_a)
    c
764
  end
765

766
  def sort_criteria
767 768 769 770 771
    c = read_attribute(:sort_criteria)
    if c.blank?
      c = default_sort_criteria
    end
    Redmine::SortCriteria.new(c)
772
  end
773

774 775
  def sort_criteria_key(index)
    sort_criteria[index].try(:first)
776
  end
777

778 779
  def sort_criteria_order(index)
    sort_criteria[index].try(:last)
780
  end
781

782 783
  def sort_clause
    sort_criteria.sort_clause(sortable_columns)
784 785
  end

jplang's avatar
jplang committed
786 787
  # Returns the SQL sort order that should be prepended for grouping
  def group_by_sort_order
jplang's avatar
jplang committed
788
    if column = group_by_column
789
      order = (sort_criteria.order_for(column.name) || column.default_order || 'asc').try(:upcase)
jplang's avatar
jplang committed
790
      Array(column.sortable).map {|s| "#{s} #{order}"}
jplang's avatar
jplang committed
791 792
    end
  end
793

jplang's avatar
jplang committed
794 795
  # Returns true if the query is a grouped query
  def grouped?
796
    !group_by_column.nil?
jplang's avatar
jplang committed
797
  end
798

jplang's avatar
jplang committed
799
  def group_by_column
800
    groupable_columns.detect {|c| c.groupable && c.name.to_s == group_by}
jplang's avatar
jplang committed
801
  end
802

803
  def group_by_statement
804
    group_by_column.try(:groupable)
805
  end
806

807
  def project_statement
808
    project_clauses = []
809 810 811 812
    active_subprojects_ids = []

    active_subprojects_ids = project.descendants.active.map(&:id) if project
    if active_subprojects_ids.any?
813 814 815 816
      if has_filter?("subproject_id")
        case operator_for("subproject_id")
        when '='
          # include the selected subprojects
817 818 819 820 821
          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)
822
          project_clauses << "#{Project.table_name}.id IN (%s)" % ids.join(',')
823 824
        when '!*'
          # main project only
825
          project_clauses << "#{Project.table_name}.id = %d" % project.id
826 827
        else
          # all subprojects
828
          project_clauses << "#{Project.table_name}.lft >= #{project.lft} AND #{Project.table_name}.rgt <= #{project.rgt}"
829 830
        end
      elsif Setting.display_subprojects_issues?
831 832 833
        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
834
      end
835
    elsif project
836
      project_clauses << "#{Project.table_name}.id = %d" % project.id
837
    end
838
    project_clauses.any? ? project_clauses.join(' AND ') : nil
839 840 841
  end

  def statement
842 843
    # filters clauses
    filters_clauses = []
jplang's avatar
jplang committed
844
    filters.each_key do |field|
845
      next if field == "subproject_id"
846
      v = values_for(field).clone
847
      next unless v and !v.empty?
848
      operator = operator_for(field)
849

850
      # "me" value substitution
851
      if %w(assigned_to_id author_id user_id watcher_id updated_by last_updated_by).include?(field)
852 853 854 855 856 857 858 859
        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
860
      end
861

862 863 864 865 866 867
      if field == 'project_id'
        if v.delete('mine')
          v += User.current.memberships.map(&:project_id).map(&:to_s)
        end
      end

868 869 870
      if field =~ /^cf_(\d+)\.cf_(\d+)$/
        filters_clauses << sql_for_chained_custom_field(field, operator, v, $1, $2)
      elsif field =~ /cf_(\d+)$/
871
        # custom field
872
        filters_clauses << sql_for_custom_field(field, operator, v, $1)
873 874
      elsif field =~ /^cf_(\d+)\.(.+)$/
        filters_clauses << sql_for_custom_field_attribute(field, operator, v, $1, $2)
875
      elsif respond_to?(method = "sql_for_#{field.gsub('.','_')}_field")
876
        # specific statement
877
        filters_clauses << send(method, field, operator, v)
878 879
      else
        # regular field
880
        filters_clauses << '(' + sql_for_field(field, operator, v, queried_table_name, field) + ')'
881
      end
jplang's avatar
jplang committed
882
    end if filters and valid?
883

884 885 886 887 888
    if (c = group_by_column) && c.is_a?(QueryCustomFieldColumn)
      # Excludes results for which the grouped custom field is not visible
      filters_clauses << c.custom_field.visibility_by_project_condition
    end

889 890
    filters_clauses << project_statement
    filters_clauses.reject!(&:blank?)
891

892
    filters_clauses.any? ? filters_clauses.join(' AND ') : nil
jplang's avatar
jplang committed
893
  end
894

895 896 897 898 899 900 901
  # Returns the result count by group or nil if query is not grouped
  def result_count_by_group
    grouped_query do |scope|
      scope.count
    end
  end

902 903
  # Returns the sum of values for the given column
  def total_for(column)
904 905 906 907 908 909 910 911 912 913 914 915 916 917 918 919 920 921 922 923 924 925 926
    total_with_scope(column, base_scope)
  end

  # Returns a hash of the sum of the given column for each group,
  # or nil if the query is not grouped
  def total_by_group_for(column)
    grouped_query do |scope|
      total_with_scope(column, scope)
    end
  end

  def totals
    totals = totalable_columns.map {|column| [column, total_for(column)]}
    yield totals if block_given?
    totals
  end

  def totals_by_group
    totals = totalable_columns.map {|column| [column, total_by_group_for(column)]}
    yield totals if block_given?
    totals
  end

927 928 929 930 931 932 933 934
  def css_classes
    s = sort_criteria.first
    if s.present?
      key, asc = s
      "sort-by-#{key.to_s.dasherize} sort-#{asc}"
    end
  end

935 936 937