query.rb 44.4 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

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
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
      cv = object.custom_values.select {|v| v.custom_field_id == @cf.id}
121
      cv.size > 1 ? cv.sort_by {|e| e.value.to_s} : cv.first
122 123 124 125 126 127 128 129 130 131 132
    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

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
    :string => [ "~", "=", "!~", "!", "!*", "*" ],
276 277 278
    :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
    if params[:fields] || params[:f]
      self.filters = {}
      add_filters(params[:fields] || params[:f], params[:operators] || params[:op], params[:values] || params[:v])
    else
374
      available_filters.each_key do |field|
375 376 377
        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

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}
460 461
  end

462
  # Returns a representation of the available filters for JSON serialization
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
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
  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
515
        principals += Principal.member_of(project).visible
516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535
        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?
536
    author_values += users.sort_by(&:status).collect{|s| [s.name, s.id.to_s, l("status_#{User::LABEL_BY_STATUS[s.status]}")] }
537 538 539 540 541 542
    author_values
  end

  def assigned_to_values
    assigned_to_values = []
    assigned_to_values << ["<< #{l(:label_me)} >>", "me"] if User.current.logged?
543
    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]}")] }
544 545 546 547 548 549 550 551
    assigned_to_values
  end

  def fixed_version_values
    versions = []
    if project
      versions = project.shared_versions.to_a
    else
552
      versions = Version.visible.to_a
553 554 555 556
    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
  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

573 574 575 576 577 578 579 580 581
  # 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

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

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

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

  # Add multiple filters using +add_filter+
  def add_filters(fields, operators, values)
jplang's avatar
jplang committed
633
    if fields.present? && operators.present?
634
      fields.each do |field|
635
        add_filter(field, operators[field], values && values[field])
636
      end
637 638
    end
  end
639

jplang's avatar
jplang committed
640 641 642
  def has_filter?(field)
    filters and filters[field]
  end
643

644 645 646
  def type_for(field)
    available_filters[field][:type] if available_filters.has_key?(field)
  end
647

jplang's avatar
jplang committed
648 649 650
  def operator_for(field)
    has_filter?(field) ? filters[field][:operator] : nil
  end
651

jplang's avatar
jplang committed
652 653 654
  def values_for(field)
    has_filter?(field) ? filters[field][:values] : nil
  end
655

656 657 658
  def value_for(field, index=0)
    (values_for(field) || [])[index]
  end
659

660
  def label_for(field)
661
    label = available_filters[field][:name] if available_filters.has_key?(field)
jplang's avatar
jplang committed
662
    label ||= queried_class.human_attribute_name(field, :default => field)
663
  end
664

665 666 667
  def self.add_available_column(column)
    self.available_columns << (column) if column.is_a?(QueryColumn)
  end
668

jplang's avatar
jplang committed
669 670 671 672
  # Returns an array of columns that can be used to group the results
  def groupable_columns
    available_columns.select {|c| c.groupable}
  end
673 674 675

  # Returns a Hash of columns and the key for sorting
  def sortable_columns
676 677 678 679
    available_columns.inject({}) {|h, column|
      h[column.name.to_s] = column.sortable
      h
    }
680
  end
681

682
  def columns
683
    # preserve the column_names order
684
    cols = (has_default_columns? ? default_columns_names : column_names).collect do |name|
685 686
       available_columns.find { |col| col.name == name }
    end.compact
687
    available_columns.select(&:frozen?) | cols
688 689
  end

690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705
  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

706 707 708 709
  def available_totalable_columns
    available_columns.select(&:totalable)
  end

710
  def default_columns_names
711
    []
712
  end
713

714 715 716 717
  def default_totalable_names
    []
  end

718
  def column_names=(names)
719 720 721
    if names
      names = names.select {|n| n.is_a?(Symbol) || !n.blank? }
      names = names.collect {|n| n.is_a?(Symbol) ? n : n.to_sym }
722 723 724
      if names.delete(:all_inline)
        names = available_inline_columns.map(&:name) | names
      end
725
      # Set column_names to nil if default columns
726
      if names == default_columns_names
727 728 729
        names = nil
      end
    end
730 731
    write_attribute(:column_names, names)
  end
732

733
  def has_column?(column)
734 735
    name = column.is_a?(QueryColumn) ? column.name : column
    columns.detect {|c| c.name == name}
736
  end
737

738 739 740 741
  def has_custom_field_column?
    columns.any? {|column| column.is_a? QueryCustomFieldColumn}
  end

742 743 744
  def has_default_columns?
    column_names.nil? || column_names.empty?
  end
745

746 747 748 749 750 751 752 753 754 755 756 757 758
  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
759
    options[:totalable_names] || default_totalable_names || []
760 761
  end

762 763 764 765
  def default_sort_criteria
    []
  end

766
  def sort_criteria=(arg)
767 768 769
    c = Redmine::SortCriteria.new(arg)
    write_attribute(:sort_criteria, c.to_a)
    c
770
  end
771

772
  def sort_criteria
773 774 775 776 777
    c = read_attribute(:sort_criteria)
    if c.blank?
      c = default_sort_criteria
    end
    Redmine::SortCriteria.new(c)
778
  end
779

780 781
  def sort_criteria_key(index)
    sort_criteria[index].try(:first)
782
  end
783

784 785
  def sort_criteria_order(index)
    sort_criteria[index].try(:last)
786
  end
787

788
  def sort_clause
789 790 791
    if clause = sort_criteria.sort_clause(sortable_columns)
      clause.map {|c| Arel.sql c}
    end
792 793
  end

jplang's avatar
jplang committed
794 795
  # Returns the SQL sort order that should be prepended for grouping
  def group_by_sort_order
jplang's avatar
jplang committed
796
    if column = group_by_column
797
      order = (sort_criteria.order_for(column.name) || column.default_order || 'asc').try(:upcase)
798
      Array(column.sortable).map {|s| Arel.sql("#{s} #{order}")}
jplang's avatar
jplang committed
799 800
    end
  end
801

jplang's avatar
jplang committed
802 803
  # Returns true if the query is a grouped query
  def grouped?
804
    !group_by_column.nil?
jplang's avatar
jplang committed
805
  end
806

jplang's avatar
jplang committed
807
  def group_by_column
808
    groupable_columns.detect {|c| c.groupable && c.name.to_s == group_by}
jplang's avatar
jplang committed
809
  end
810

811
  def group_by_statement
812
    group_by_column.try(:groupable)
813
  end
814

815
  def project_statement
816
    project_clauses = []
817 818 819 820
    active_subprojects_ids = []

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

  def statement
850 851
    # filters clauses
    filters_clauses = []
jplang's avatar
jplang committed
852
    filters.each_key do |field|
853
      next if field == "subproject_id"
854
      v = values_for(field).clone
855
      next unless v and !v.empty?
856
      operator = operator_for(field)
857

858
      # "me" value substitution
859
      if %w(assigned_to_id author_id user_id watcher_id updated_by last_updated_by).include?(field)
860 861 862 863 864 865 866 867
        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
868
      end
869

870 871 872 873 874 875
      if field == 'project_id'
        if v.delete('mine')
          v += User.current.memberships.map(&:project_id).map(&:to_s)
        end
      end

876 877 878
      if field =~ /^cf_(\d+)\.cf_(\d+)$/
        filters_clauses << sql_for_chained_custom_field(field, operator, v, $1, $2)
      elsif field =~ /cf_(\d+)$/
879
        # custom field
880
        filters_clauses << sql_for_custom_field(field, operator, v, $1)
881 882
      elsif field =~ /^cf_(\d+)\.(.+)$/
        filters_clauses << sql_for_custom_field_attribute(field, operator, v, $1, $2)
883
      elsif respond_to?(method = "sql_for_#{field.tr('.','_')}_field")
884
        # specific statement
885
        filters_clauses << send(method, field, operator, v)
886 887
      else
        # regular field
888
        filters_clauses << '(' + sql_for_field(field, operator, v, queried_table_name, field) + ')'
889
      end
jplang's avatar
jplang committed
890
    end if filters and valid?
891

892 893 894 895 896
    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

897 898
    filters_clauses << project_statement
    filters_clauses.reject!(&:blank?)
899

900
    filters_clauses.any? ? filters_clauses.join(' AND ') : nil
jplang's avatar
jplang committed
901
  end
902

903 904 905 906 907 908 909
  # 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

910 911
  # Returns the sum of values for the given column
  def total_for(column)
912 913 914 915 916 917 918 919 920 921 922 923 924 925 926 927 928 929 930 931 932 933 934
    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

935 936 937 938 939 940 941 942
  def css_classes
    s = sort_criteria.first
    if s.present?
      key, asc = s
      "sort-by-#{key.to_s.dasherize} sort-#{asc}"
    end
  end

943 944 945 946 947
  private

  def grouped_query(&block)
    r = nil
    if grouped?
948
      r = yield base_group_scope
949 950 951 952 953 954 955 956 957 958 959
      c = group_by_column
      if c.is_a?(QueryCustomFieldColumn)
        r = r.keys.inject({}) {|h, k| h[c.custom_field.cast_value(k)] = r[k]; h}
      end
    end
    r
  rescue ::ActiveRecord::StatementInvalid => e
    raise StatementInvalid.new(e.message)
  end

  def total_with_scope(column, scope)
960 961 962 963 964 965
    unless column.is_a?(QueryColumn)
      column = column.to_sym
      column = available_totalable_columns.detect {|c| c.name == column}
    end
    if column.is_a?(QueryCustomFieldColumn)
      custom_field = column.custom_field
966
      send "total_for_custom_field", custom_field, scope
967
    else
968
      send "total_for_#{column.name}", scope
969 970 971 972 973
    end
  rescue ::ActiveRecord::StatementInvalid => e
    raise StatementInvalid.new(e.message)
  end

974 975 976 977 978 979 980 981 982 983
  def base_scope
    raise "unimplemented"
  end

  def base_group_scope
    base_scope.
      joins(joins_for_order_statement(group_by_statement)).
      group(group_by_statement)
  end

984
  def total_for_custom_field(custom_field, scope, &block)
985 986
    total = custom_field.format.total_for_scope(custom_field, scope)
    total = map_total(total) {|t| custom_field.format.cast_total_value(custom_field, t)}
987 988
    total
  end
989

990 991
  def map_total(total, &block)
    if total.is_a?(Hash)
992
      total.each_key {|k| total[k] = yield total[k]}
993 994 995
    else
      total = yield total
    end
996 997
    total
  end
998

999 1000 1001
  def sql_for_custom_field(field, operator, value, custom_field_id)
    db_table = CustomValue.table_name
    db_field = 'value'
1002
    filter = @available_filters[field]
1003
    return nil unless filter
1004
    if filter[:field].format.target_class && filter[:field].format.target_class <= User
1005 1006 1007 1008
      if value.delete('me')
        value.push User.current.id.to_s
      end
    end
1009 1010 1011 1012 1013 1014
    not_in = nil
    if operator == '!'
      # Makes ! operator work for custom fields with multiple values
      operator = '='
      not_in = 'NOT'
    end
1015
    customized_key = "id"
1016
    customized_class = queried_class
1017 1018 1019
    if field =~ /^(.+)\.cf_/
      assoc = $1
      customized_key = "#{assoc}_id"
1020 1021
      customized_class = queried_class.reflect_on_association(assoc.to_sym).klass.base_class rescue nil
      raise "Unknown #{queried_class.name} association #{assoc}" unless customized_class
1022
    end
1023 1024 1025 1026
    where = sql_for_field(field, operator, value, db_table, db_field, true)
    if operator =~ /[<>]/
      where = "(#{where}) AND #{db_table}.#{db_field} <> ''"
    end
1027 1028 1029 1030
    "#{queried_table_name}.#{customized_key} #{not_in} IN (" +
      "SELECT #{customized_class.table_name}.id FROM #{customized_class.table_name}" +
      " 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}" +
      " WHERE (#{where}) AND (#{filter[:field].visibility_by_project_condition}))"
1031
  end
1032

1033 1034 1035 1036 1037 1038 1039 1040 1041 1042 1043 1044 1045 1046
  def sql_for_chained_custom_field(field, operator, value, custom_field_id, chained_custom_field_id)
    not_in = nil
    if operator == '!'
      # Makes ! operator work for custom fields with multiple values
      operator = '='
      not_in = 'NOT'
    end

    filter = available_filters[field]
    target_class = filter[:through].format.target_class

    "#{queried_table_name}.id #{not_in} IN (" +
      "SELECT customized_id FROM #{CustomValue.table_name}" +
      " WHERE customized_type='#{queried_class}' AND custom_field_id=#{custom_field_id}" +
1047
      "  AND CAST(CASE value WHEN '' THEN '0' ELSE value END AS decimal(30,0)) IN (" +
1048 1049 1050
      "  SELECT customized_id FROM #{CustomValue.table_name}" +
      "  WHERE customized_type='#{target_class}' AND custom_field_id=#{chained_custom_field_id}" +
      "  AND #{sql_for_field(field, operator, value, CustomValue.table_name, 'value')}))"
1051

1052 1053 1054 1055 1056 1057 1058 1059 1060 1061 1062 1063 1064 1065 1066 1067 1068
  end

  def sql_for_custom_field_attribute(field, operator, value, custom_field_id, attribute)
    attribute = 'effective_date' if attribute == 'due_date'
    not_in = nil
    if operator == '!'
      # Makes ! operator work for custom fields with multiple values
      operator = '='
      not_in = 'NOT'
    end

    filter = available_filters[field]
    target_table_name = filter[:field].format.target_class.table_name

    "#{queried_table_name}.id #{not_in} IN (" +
      "SELECT customized_id FROM #{CustomValue.table_name}" +
      " WHERE customized_type='#{queried_class}' AND custom_field_id=#{custom_field_id}" +
1069
      "  AND CAST(CASE value WHEN '' THEN '0' ELSE value END AS decimal(30,0)) IN (" +
1070 1071 1072
      "  SELECT id FROM #{target_table_name} WHERE #{sql_for_field(field, operator, value, filter[:field].format.target_class.table_name, attribute)}))"
  end

1073 1074
  # Helper method to generate the WHERE sql for a +field+, +operator+ and a +value+
  def sql_for_field(field, operator, value, db_table, db_field, is_custom_filter=false)
1075
    sql = ''
1076
    case operator
1077
    when "="
jplang's avatar
jplang committed
1078 1079 1080
      if value.any?
        case type_for(field)
        when :date, :date_past
1081
          sql = date_clause(db_table, db_field, parse_date(value.first), parse_date(value.first), is_custom_filter)
jplang's avatar
jplang committed
1082
        when :integer
jplang's avatar
jplang committed
1083 1084 1085 1086 1087 1088 1089
          int_values = value.first.to_s.scan(/[+-]?\d+/).map(&:to_i).join(",")
          if int_values.present?
            if is_custom_filter
              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}))"
            else
              sql = "#{db_table}.#{db_field} IN (#{int_values})"
            end
1090
          else
jplang's avatar
jplang committed
1091
            sql = "1=0"
1092
          end
jplang's avatar
jplang committed
1093
        when :float
1094
          if is_custom_filter
1095
            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})"
1096 1097 1098
          else
            sql = "#{db_table}.#{db_field} BETWEEN #{value.first.to_f - 1e-5} AND #{value.first.to_f + 1e-5}"
          end
1099
        else
1100
          sql = queried_class.send(:sanitize_sql_for_conditions, ["#{db_table}.#{db_field} IN (?)", value])
1101
        end