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

class IssueQuery < Query

20
  self.queried_class = Issue
jplang's avatar
jplang committed
21
  self.view_permission = :view_issues
22

23
  self.available_columns = [
24
    QueryColumn.new(:id, :sortable => "#{Issue.table_name}.id", :default_order => 'desc', :caption => '#', :frozen => true),
25 26 27 28 29 30 31 32
    QueryColumn.new(:project, :sortable => "#{Project.table_name}.name", :groupable => true),
    QueryColumn.new(:tracker, :sortable => "#{Tracker.table_name}.position", :groupable => true),
    QueryColumn.new(:parent, :sortable => ["#{Issue.table_name}.root_id", "#{Issue.table_name}.lft ASC"], :default_order => 'desc', :caption => :field_parent_issue),
    QueryColumn.new(:status, :sortable => "#{IssueStatus.table_name}.position", :groupable => true),
    QueryColumn.new(:priority, :sortable => "#{IssuePriority.table_name}.position", :default_order => 'desc', :groupable => true),
    QueryColumn.new(:subject, :sortable => "#{Issue.table_name}.subject"),
    QueryColumn.new(:author, :sortable => lambda {User.fields_for_order_statement("authors")}, :groupable => true),
    QueryColumn.new(:assigned_to, :sortable => lambda {User.fields_for_order_statement}, :groupable => true),
33
    TimestampQueryColumn.new(:updated_on, :sortable => "#{Issue.table_name}.updated_on", :default_order => 'desc', :groupable => true),
34 35
    QueryColumn.new(:category, :sortable => "#{IssueCategory.table_name}.name", :groupable => true),
    QueryColumn.new(:fixed_version, :sortable => lambda {Version.fields_for_order_statement}, :groupable => true),
36 37
    QueryColumn.new(:start_date, :sortable => "#{Issue.table_name}.start_date", :groupable => true),
    QueryColumn.new(:due_date, :sortable => "#{Issue.table_name}.due_date", :groupable => true),
38
    QueryColumn.new(:estimated_hours, :sortable => "#{Issue.table_name}.estimated_hours", :totalable => true),
39 40 41 42
    QueryColumn.new(:total_estimated_hours,
      :sortable => "COALESCE((SELECT SUM(estimated_hours) FROM #{Issue.table_name} subtasks" +
        " WHERE subtasks.root_id = #{Issue.table_name}.root_id AND subtasks.lft >= #{Issue.table_name}.lft AND subtasks.rgt <= #{Issue.table_name}.rgt), 0)",
      :default_order => 'desc'),
43
    QueryColumn.new(:done_ratio, :sortable => "#{Issue.table_name}.done_ratio", :groupable => true),
44 45
    TimestampQueryColumn.new(:created_on, :sortable => "#{Issue.table_name}.created_on", :default_order => 'desc', :groupable => true),
    TimestampQueryColumn.new(:closed_on, :sortable => "#{Issue.table_name}.closed_on", :default_order => 'desc', :groupable => true),
46
    QueryColumn.new(:last_updated_by, :sortable => lambda {User.fields_for_order_statement("last_journal_user")}),
47
    QueryColumn.new(:relations, :caption => :label_related_issues),
48
    QueryColumn.new(:attachments, :caption => :label_attachment_plural),
49 50
    QueryColumn.new(:description, :inline => false),
    QueryColumn.new(:last_notes, :caption => :label_last_notes, :inline => false)
51 52 53 54 55 56 57
  ]

  def initialize(attributes=nil, *args)
    super attributes
    self.filters ||= { 'status_id' => {:operator => "o", :values => [""]} }
  end

58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75
  def draw_relations
    r = options[:draw_relations]
    r.nil? || r == '1'
  end

  def draw_relations=(arg)
    options[:draw_relations] = (arg == '0' ? '0' : nil)
  end

  def draw_progress_line
    r = options[:draw_progress_line]
    r == '1'
  end

  def draw_progress_line=(arg)
    options[:draw_progress_line] = (arg == '1' ? '1' : nil)
  end

76
  def build_from_params(params, defaults={})
77
    super
78 79
    self.draw_relations = params[:draw_relations] || (params[:query] && params[:query][:draw_relations]) || options[:draw_relations]
    self.draw_progress_line = params[:draw_progress_line] || (params[:query] && params[:query][:draw_progress_line]) || options[:draw_progress_line]
80 81 82
    self
  end

83 84
  def initialize_available_filters
    add_available_filter "status_id",
85
      :type => :list_status, :values => lambda { issue_statuses_values }
86 87 88 89

    add_available_filter("project_id",
      :type => :list, :values => lambda { project_values }
    ) if project.nil?
90 91 92

    add_available_filter "tracker_id",
      :type => :list, :values => trackers.collect{|s| [s.name, s.id.to_s] }
93

94 95
    add_available_filter "priority_id",
      :type => :list, :values => IssuePriority.all.collect{|s| [s.name, s.id.to_s] }
96

97
    add_available_filter("author_id",
98 99
      :type => :list, :values => lambda { author_values }
    )
100 101

    add_available_filter("assigned_to_id",
102 103
      :type => :list_optional, :values => lambda { assigned_to_values }
    )
104

105
    add_available_filter("member_of_group",
106 107
      :type => :list_optional, :values => lambda { Group.givable.visible.collect {|g| [g.name, g.id.to_s] } }
    )
108

109
    add_available_filter("assigned_to_role",
110 111
      :type => :list_optional, :values => lambda { Role.givable.collect {|r| [r.name, r.id.to_s] } }
    )
112

113
    add_available_filter "fixed_version_id",
114
      :type => :list_optional, :values => lambda { fixed_version_values }
115

116 117 118 119 120 121 122
    add_available_filter "fixed_version.due_date",
      :type => :date,
      :name => l(:label_attribute_of_fixed_version, :name => l(:field_effective_date))

    add_available_filter "fixed_version.status",
      :type => :list,
      :name => l(:label_attribute_of_fixed_version, :name => l(:field_status)),
123
      :values => Version::VERSION_STATUSES.map{|s| [l("version_status_#{s}"), s] }
124

125 126
    add_available_filter "category_id",
      :type => :list_optional,
127
      :values => lambda { project.issue_categories.collect{|s| [s.name, s.id.to_s] } } if project
128 129

    add_available_filter "subject", :type => :text
130
    add_available_filter "description", :type => :text
131 132
    add_available_filter "created_on", :type => :date_past
    add_available_filter "updated_on", :type => :date_past
133
    add_available_filter "closed_on", :type => :date_past
134 135 136 137 138
    add_available_filter "start_date", :type => :date
    add_available_filter "due_date", :type => :date
    add_available_filter "estimated_hours", :type => :float
    add_available_filter "done_ratio", :type => :integer

139 140
    if User.current.allowed_to?(:set_issues_private, nil, :global => true) ||
      User.current.allowed_to?(:set_own_issues_private, nil, :global => true)
141 142
      add_available_filter "is_private",
        :type => :list,
143 144
        :values => [[l(:general_text_yes), "1"], [l(:general_text_no), "0"]]
    end
145

146 147 148
    add_available_filter "attachment",
      :type => :text, :name => l(:label_attachment)

149 150
    if User.current.logged?
      add_available_filter "watcher_id",
151
        :type => :list, :values => lambda { watcher_values }
152 153
    end

jplang's avatar
jplang committed
154 155 156 157 158 159 160 161
    add_available_filter("updated_by",
      :type => :list, :values => lambda { author_values }
    )

    add_available_filter("last_updated_by",
      :type => :list, :values => lambda { author_values }
    )

162
    if project && !project.leaf?
163 164
      add_available_filter "subproject_id",
        :type => :list_subprojects,
165
        :values => lambda { subproject_values }
166 167
    end

168 169 170 171 172 173
    add_available_filter("project.status",
      :type => :list,
      :name => l(:label_attribute_of_project, :name => l(:field_status)),
      :values => lambda { project_statuses_values }
    ) if project.nil? || !project.leaf?

174 175 176 177
    add_custom_fields_filters(issue_custom_fields)
    add_associations_custom_fields_filters :project, :author, :assigned_to, :fixed_version

    IssueRelation::TYPES.each do |relation_type, options|
178
      add_available_filter relation_type, :type => :relation, :label => options[:name], :values => lambda {all_projects_values}
179
    end
180 181
    add_available_filter "parent_id", :type => :tree, :label => :field_parent_issue
    add_available_filter "child_id", :type => :tree, :label => :label_subtask_plural
182

jplang's avatar
jplang committed
183 184
    add_available_filter "issue_id", :type => :integer, :label => :label_issue

185
    Tracker.disabled_core_fields(trackers).each {|field|
186
      delete_available_filter field
187 188 189 190 191 192
    }
  end

  def available_columns
    return @available_columns if @available_columns
    @available_columns = self.class.available_columns.dup
193
    @available_columns += issue_custom_fields.visible.collect {|cf| QueryCustomFieldColumn.new(cf) }
194 195

    if User.current.allowed_to?(:view_time_entries, project, :global => true)
196
      # insert the columns after total_estimated_hours or at the end
197
      index = @available_columns.find_index {|column| column.name == :total_estimated_hours}
198
      index = (index ? index + 1 : -1)
199 200 201 202 203

      subselect = "SELECT SUM(hours) FROM #{TimeEntry.table_name}" +
        " JOIN #{Project.table_name} ON #{Project.table_name}.id = #{TimeEntry.table_name}.project_id" +
        " WHERE (#{TimeEntry.visible_condition(User.current)}) AND #{TimeEntry.table_name}.issue_id = #{Issue.table_name}.id"

204
      @available_columns.insert index, QueryColumn.new(:spent_hours,
205
        :sortable => "COALESCE((#{subselect}), 0)",
206
        :default_order => 'desc',
207 208
        :caption => :label_spent_time,
        :totalable => true
209
      )
210 211 212 213 214 215 216

      subselect = "SELECT SUM(hours) FROM #{TimeEntry.table_name}" +
        " JOIN #{Project.table_name} ON #{Project.table_name}.id = #{TimeEntry.table_name}.project_id" +
        " JOIN #{Issue.table_name} subtasks ON subtasks.id = #{TimeEntry.table_name}.issue_id" +
        " WHERE (#{TimeEntry.visible_condition(User.current)})" +
        " AND subtasks.root_id = #{Issue.table_name}.root_id AND subtasks.lft >= #{Issue.table_name}.lft AND subtasks.rgt <= #{Issue.table_name}.rgt"

217
      @available_columns.insert index+1, QueryColumn.new(:total_spent_hours,
218
        :sortable => "COALESCE((#{subselect}), 0)",
219 220 221
        :default_order => 'desc',
        :caption => :label_total_spent_time
      )
222 223 224 225
    end

    if User.current.allowed_to?(:set_issues_private, nil, :global => true) ||
      User.current.allowed_to?(:set_own_issues_private, nil, :global => true)
226
      @available_columns << QueryColumn.new(:is_private, :sortable => "#{Issue.table_name}.is_private", :groupable => true)
227 228 229 230 231 232 233 234 235 236
    end

    disabled_fields = Tracker.disabled_core_fields(trackers).map {|field| field.sub(/_id$/, '')}
    @available_columns.reject! {|column|
      disabled_fields.include?(column.name.to_s)
    }

    @available_columns
  end

237 238 239 240 241 242 243 244
  def default_columns_names
    @default_columns_names ||= begin
      default_columns = Setting.issue_list_default_columns.map(&:to_sym)

      project.present? ? default_columns : [:project] | default_columns
    end
  end

245 246 247 248
  def default_totalable_names
    Setting.issue_list_default_totals.map(&:to_sym)
  end

249 250 251 252
  def default_sort_criteria
    [['id', 'desc']]
  end

253 254 255 256
  def base_scope
    Issue.visible.joins(:status, :project).where(statement)
  end

257 258
  # Returns the issue count
  def issue_count
259
    base_scope.count
260 261 262 263
  rescue ::ActiveRecord::StatementInvalid => e
    raise StatementInvalid.new(e.message)
  end

264 265
  # Returns sum of all the issue's estimated_hours
  def total_for_estimated_hours(scope)
266
    map_total(scope.sum(:estimated_hours)) {|t| t.to_f.round(2)}
267 268
  end

269 270
  # Returns sum of all the issue's time entries hours
  def total_for_spent_hours(scope)
271 272 273 274
    total = scope.joins(:time_entries).
      where(TimeEntry.visible_condition(User.current)).
      sum("#{TimeEntry.table_name}.hours")

275
    map_total(total) {|t| t.to_f.round(2)}
276 277 278 279 280
  end

  # Returns the issues
  # Valid options are :order, :offset, :limit, :include, :conditions
  def issues(options={})
281
    order_option = [group_by_sort_order, (options[:order] || sort_clause)].flatten.reject(&:blank?)
282

283
    scope = Issue.visible.
284
      joins(:status, :project).
285
      preload(:priority).
286 287 288 289 290 291
      where(statement).
      includes(([:status, :project] + (options[:include] || [])).uniq).
      where(options[:conditions]).
      order(order_option).
      joins(joins_for_order_statement(order_option.join(','))).
      limit(options[:limit]).
292 293
      offset(options[:offset])

294
    scope = scope.preload([:tracker, :author, :assigned_to, :fixed_version, :category, :attachments] & columns.map(&:name))
295 296
    if has_custom_field_column?
      scope = scope.preload(:custom_values)
297
    end
298

jplang's avatar
jplang committed
299
    issues = scope.to_a
300 301 302 303

    if has_column?(:spent_hours)
      Issue.load_visible_spent_hours(issues)
    end
304 305 306
    if has_column?(:total_spent_hours)
      Issue.load_visible_total_spent_hours(issues)
    end
307 308 309
    if has_column?(:last_updated_by)
      Issue.load_visible_last_updated_by(issues)
    end
310 311 312
    if has_column?(:relations)
      Issue.load_visible_relations(issues)
    end
313 314 315
    if has_column?(:last_notes)
      Issue.load_visible_last_notes(issues)
    end
316 317 318 319 320 321 322
    issues
  rescue ::ActiveRecord::StatementInvalid => e
    raise StatementInvalid.new(e.message)
  end

  # Returns the issues ids
  def issue_ids(options={})
323
    order_option = [group_by_sort_order, (options[:order] || sort_clause)].flatten.reject(&:blank?)
324

325 326 327 328
    Issue.visible.
      joins(:status, :project).
      where(statement).
      includes(([:status, :project] + (options[:include] || [])).uniq).
jplang's avatar
jplang committed
329
      references(([:status, :project] + (options[:include] || [])).uniq).
330 331 332 333 334
      where(options[:conditions]).
      order(order_option).
      joins(joins_for_order_statement(order_option.join(','))).
      limit(options[:limit]).
      offset(options[:offset]).
jplang's avatar
jplang committed
335
      pluck(:id)
336 337 338 339 340 341 342
  rescue ::ActiveRecord::StatementInvalid => e
    raise StatementInvalid.new(e.message)
  end

  # Returns the journals
  # Valid options are :order, :offset, :limit
  def journals(options={})
343 344 345 346 347 348 349
    Journal.visible.
      joins(:issue => [:project, :status]).
      where(statement).
      order(options[:order]).
      limit(options[:limit]).
      offset(options[:offset]).
      preload(:details, :user, {:issue => [:project, :author, :tracker, :status]}).
jplang's avatar
jplang committed
350
      to_a
351 352 353 354 355 356 357
  rescue ::ActiveRecord::StatementInvalid => e
    raise StatementInvalid.new(e.message)
  end

  # Returns the versions
  # Valid options are :conditions
  def versions(options={})
358 359 360 361
    Version.visible.
      where(project_statement).
      where(options[:conditions]).
      includes(:project).
jplang's avatar
jplang committed
362 363
      references(:project).
      to_a
364 365 366
  rescue ::ActiveRecord::StatementInvalid => e
    raise StatementInvalid.new(e.message)
  end
367

368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388
  def sql_for_updated_by_field(field, operator, value)
    neg = (operator == '!' ? 'NOT' : '')
    subquery = "SELECT 1 FROM #{Journal.table_name}" +
      " WHERE #{Journal.table_name}.journalized_type='Issue' AND #{Journal.table_name}.journalized_id=#{Issue.table_name}.id" +
      " AND (#{sql_for_field field, '=', value, Journal.table_name, 'user_id'})" +
      " AND (#{Journal.visible_notes_condition(User.current, :skip_pre_condition => true)})"

    "#{neg} EXISTS (#{subquery})"
  end

  def sql_for_last_updated_by_field(field, operator, value)
    neg = (operator == '!' ? 'NOT' : '')
    subquery = "SELECT 1 FROM #{Journal.table_name} sj" +
      " WHERE sj.journalized_type='Issue' AND sj.journalized_id=#{Issue.table_name}.id AND (#{sql_for_field field, '=', value, 'sj', 'user_id'})" +
      " AND sj.id = (SELECT MAX(#{Journal.table_name}.id) FROM #{Journal.table_name}" +
      "   WHERE #{Journal.table_name}.journalized_type='Issue' AND #{Journal.table_name}.journalized_id=#{Issue.table_name}.id" +
      "   AND (#{Journal.visible_notes_condition(User.current, :skip_pre_condition => true)}))"

    "#{neg} EXISTS (#{subquery})"
  end

389 390
  def sql_for_watcher_id_field(field, operator, value)
    db_table = Watcher.table_name
391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410

    me, others = value.partition { |id| ['0', User.current.id.to_s].include?(id) }
    sql = if others.any?
      "SELECT #{Issue.table_name}.id FROM #{Issue.table_name} " +
      "INNER JOIN #{db_table} ON #{Issue.table_name}.id = #{db_table}.watchable_id AND #{db_table}.watchable_type = 'Issue' " +
      "LEFT OUTER JOIN #{Project.table_name} ON #{Project.table_name}.id = #{Issue.table_name}.project_id " +
      "WHERE (" +
        sql_for_field(field, '=', me, db_table, 'user_id') +
      ') OR (' +
        Project.allowed_to_condition(User.current, :view_issue_watchers) +
        ' AND ' +
        sql_for_field(field, '=', others, db_table, 'user_id') +
      ')'
    else
      "SELECT #{db_table}.watchable_id FROM #{db_table} " +
      "WHERE #{db_table}.watchable_type='Issue' AND " +
      sql_for_field(field, '=', me, db_table, 'user_id')
    end

    "#{Issue.table_name}.id #{ operator == '=' ? 'IN' : 'NOT IN' } (#{sql})"
411 412 413 414
  end

  def sql_for_member_of_group_field(field, operator, value)
    if operator == '*' # Any group
415
      groups = Group.givable
416 417
      operator = '=' # Override the operator since we want to find by assigned_to
    elsif operator == "!*"
418
      groups = Group.givable
419 420
      operator = '!' # Override the operator since we want to find by assigned_to
    else
jplang's avatar
jplang committed
421
      groups = Group.where(:id => value).to_a
422 423 424 425
    end
    groups ||= []

    members_of_groups = groups.inject([]) {|user_ids, group|
jplang's avatar
jplang committed
426 427
      user_ids + group.user_ids + [group.id]
    }.uniq.compact.sort.collect(&:to_s)
428 429 430 431 432 433 434 435 436 437 438 439 440

    '(' + sql_for_field("assigned_to_id", operator, members_of_groups, Issue.table_name, "assigned_to_id", false) + ')'
  end

  def sql_for_assigned_to_role_field(field, operator, value)
    case operator
    when "*", "!*" # Member / Not member
      sw = operator == "!*" ? 'NOT' : ''
      nl = operator == "!*" ? "#{Issue.table_name}.assigned_to_id IS NULL OR" : ''
      "(#{nl} #{Issue.table_name}.assigned_to_id #{sw} IN (SELECT DISTINCT #{Member.table_name}.user_id FROM #{Member.table_name}" +
        " WHERE #{Member.table_name}.project_id = #{Issue.table_name}.project_id))"
    when "=", "!"
      role_cond = value.any? ?
jplang's avatar
jplang committed
441
        "#{MemberRole.table_name}.role_id IN (" + value.collect{|val| "'#{self.class.connection.quote_string(val)}'"}.join(",") + ")" :
442 443 444 445 446 447 448 449 450
        "1=0"

      sw = operator == "!" ? 'NOT' : ''
      nl = operator == "!" ? "#{Issue.table_name}.assigned_to_id IS NULL OR" : ''
      "(#{nl} #{Issue.table_name}.assigned_to_id #{sw} IN (SELECT DISTINCT #{Member.table_name}.user_id FROM #{Member.table_name}, #{MemberRole.table_name}" +
        " WHERE #{Member.table_name}.project_id = #{Issue.table_name}.project_id AND #{Member.table_name}.id = #{MemberRole.table_name}.member_id AND #{role_cond}))"
    end
  end

451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466
  def sql_for_fixed_version_status_field(field, operator, value)
    where = sql_for_field(field, operator, value, Version.table_name, "status")
    version_ids = versions(:conditions => [where]).map(&:id)

    nl = operator == "!" ? "#{Issue.table_name}.fixed_version_id IS NULL OR" : ''
    "(#{nl} #{sql_for_field("fixed_version_id", "=", version_ids, Issue.table_name, "fixed_version_id")})"
  end

  def sql_for_fixed_version_due_date_field(field, operator, value)
    where = sql_for_field(field, operator, value, Version.table_name, "effective_date")
    version_ids = versions(:conditions => [where]).map(&:id)

    nl = operator == "!*" ? "#{Issue.table_name}.fixed_version_id IS NULL OR" : ''
    "(#{nl} #{sql_for_field("fixed_version_id", "=", version_ids, Issue.table_name, "fixed_version_id")})"
  end

467 468
  def sql_for_is_private_field(field, operator, value)
    op = (operator == "=" ? 'IN' : 'NOT IN')
jplang's avatar
jplang committed
469
    va = value.map {|v| v == '0' ? self.class.connection.quoted_false : self.class.connection.quoted_true}.uniq.join(',')
470 471 472 473

    "#{Issue.table_name}.is_private #{op} (#{va})"
  end

474 475 476 477 478 479 480 481 482 483 484 485
  def sql_for_attachment_field(field, operator, value)
    case operator
    when "*", "!*"
      e = (operator == "*" ? "EXISTS" : "NOT EXISTS")
      "#{e} (SELECT 1 FROM #{Attachment.table_name} a WHERE a.container_type = 'Issue' AND a.container_id = #{Issue.table_name}.id)"
    when "~", "!~"
      c = sql_contains("a.filename", value.first)
      e = (operator == "~" ? "EXISTS" : "NOT EXISTS")
      "#{e} (SELECT 1 FROM #{Attachment.table_name} a WHERE a.container_type = 'Issue' AND a.container_id = #{Issue.table_name}.id AND #{c})"
    end
  end

486 487 488 489 490 491 492 493 494 495 496 497 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
  def sql_for_parent_id_field(field, operator, value)
    case operator
    when "="
      "#{Issue.table_name}.parent_id = #{value.first.to_i}"
    when "~"
      root_id, lft, rgt = Issue.where(:id => value.first.to_i).pluck(:root_id, :lft, :rgt).first
      if root_id && lft && rgt
        "#{Issue.table_name}.root_id = #{root_id} AND #{Issue.table_name}.lft > #{lft} AND #{Issue.table_name}.rgt < #{rgt}"
      else
        "1=0"
      end
    when "!*"
      "#{Issue.table_name}.parent_id IS NULL"
    when "*"
      "#{Issue.table_name}.parent_id IS NOT NULL"
    end
  end

  def sql_for_child_id_field(field, operator, value)
    case operator
    when "="
      parent_id = Issue.where(:id => value.first.to_i).pluck(:parent_id).first
      if parent_id
        "#{Issue.table_name}.id = #{parent_id}"
      else
        "1=0"
      end
    when "~"
      root_id, lft, rgt = Issue.where(:id => value.first.to_i).pluck(:root_id, :lft, :rgt).first
      if root_id && lft && rgt
        "#{Issue.table_name}.root_id = #{root_id} AND #{Issue.table_name}.lft < #{lft} AND #{Issue.table_name}.rgt > #{rgt}"
      else
        "1=0"
      end
    when "!*"
      "#{Issue.table_name}.rgt - #{Issue.table_name}.lft = 1"
    when "*"
      "#{Issue.table_name}.rgt - #{Issue.table_name}.lft > 1"
    end
  end

527 528 529 530 531 532 533 534 535 536 537
  def sql_for_updated_on_field(field, operator, value)
    case operator
    when "!*"
      "#{Issue.table_name}.updated_on = #{Issue.table_name}.created_on"
    when "*"
      "#{Issue.table_name}.updated_on > #{Issue.table_name}.created_on"
    else
      sql_for_field("updated_on", operator, value, Issue.table_name, "updated_on")
    end
  end

jplang's avatar
jplang committed
538
  def sql_for_issue_id_field(field, operator, value)
539 540 541 542 543 544 545 546
    if operator == "="
      # accepts a comma separated list of ids
      ids = value.first.to_s.scan(/\d+/).map(&:to_i)
      if ids.present?
        "#{Issue.table_name}.id IN (#{ids.join(",")})"
      else
        "1=0"
      end
jplang's avatar
jplang committed
547
    else
548
      sql_for_field("id", operator, value, Issue.table_name, "id")
jplang's avatar
jplang committed
549 550 551
    end
  end

552 553 554 555 556 557 558 559 560 561 562 563 564 565
  def sql_for_relations(field, operator, value, options={})
    relation_options = IssueRelation::TYPES[field]
    return relation_options unless relation_options

    relation_type = field
    join_column, target_join_column = "issue_from_id", "issue_to_id"
    if relation_options[:reverse] || options[:reverse]
      relation_type = relation_options[:reverse] || relation_type
      join_column, target_join_column = target_join_column, join_column
    end

    sql = case operator
      when "*", "!*"
        op = (operator == "*" ? 'IN' : 'NOT IN')
jplang's avatar
jplang committed
566
        "#{Issue.table_name}.id #{op} (SELECT DISTINCT #{IssueRelation.table_name}.#{join_column} FROM #{IssueRelation.table_name} WHERE #{IssueRelation.table_name}.relation_type = '#{self.class.connection.quote_string(relation_type)}')"
567 568
      when "=", "!"
        op = (operator == "=" ? 'IN' : 'NOT IN')
jplang's avatar
jplang committed
569
        "#{Issue.table_name}.id #{op} (SELECT DISTINCT #{IssueRelation.table_name}.#{join_column} FROM #{IssueRelation.table_name} WHERE #{IssueRelation.table_name}.relation_type = '#{self.class.connection.quote_string(relation_type)}' AND #{IssueRelation.table_name}.#{target_join_column} = #{value.first.to_i})"
570 571 572
      when "=p", "=!p", "!p"
        op = (operator == "!p" ? 'NOT IN' : 'IN')
        comp = (operator == "=!p" ? '<>' : '=')
jplang's avatar
jplang committed
573
        "#{Issue.table_name}.id #{op} (SELECT DISTINCT #{IssueRelation.table_name}.#{join_column} FROM #{IssueRelation.table_name}, #{Issue.table_name} relissues WHERE #{IssueRelation.table_name}.relation_type = '#{self.class.connection.quote_string(relation_type)}' AND #{IssueRelation.table_name}.#{target_join_column} = relissues.id AND relissues.project_id #{comp} #{value.first.to_i})"
574 575 576
      when "*o", "!o"
        op = (operator == "!o" ? 'NOT IN' : 'IN')
        "#{Issue.table_name}.id #{op} (SELECT DISTINCT #{IssueRelation.table_name}.#{join_column} FROM #{IssueRelation.table_name}, #{Issue.table_name} relissues WHERE #{IssueRelation.table_name}.relation_type = '#{self.class.connection.quote_string(relation_type)}' AND #{IssueRelation.table_name}.#{target_join_column} = relissues.id AND relissues.status_id IN (SELECT id FROM #{IssueStatus.table_name} WHERE is_closed=#{self.class.connection.quoted_false}))"
577 578 579 580
      end

    if relation_options[:sym] == field && !options[:reverse]
      sqls = [sql, sql_for_relations(field, operator, value, :reverse => true)]
581
      sql = sqls.join(["!", "!*", "!p", '!o'].include?(operator) ? " AND " : " OR ")
582
    end
583
    "(#{sql})"
584 585
  end

586 587 588 589
  def sql_for_project_status_field(field, operator, value, options={})
    sql_for_field(field, operator, value, Project.table_name, "status")
  end

590 591 592 593 594
  def find_assigned_to_id_filter_values(values)
    Principal.visible.where(:id => values).map {|p| [p.name, p.id.to_s]}
  end
  alias :find_author_id_filter_values :find_assigned_to_id_filter_values

595
  IssueRelation::TYPES.each_key do |relation_type|
596 597
    alias_method "sql_for_#{relation_type}_field".to_sym, :sql_for_relations
  end
598 599 600 601 602 603 604 605

  def joins_for_order_statement(order_options)
    joins = [super]

    if order_options
      if order_options.include?('authors')
        joins << "LEFT OUTER JOIN #{User.table_name} authors ON authors.id = #{queried_table_name}.author_id"
      end
606 607 608
      if order_options.include?('users')
        joins << "LEFT OUTER JOIN #{User.table_name} ON #{User.table_name}.id = #{queried_table_name}.assigned_to_id"
      end
609 610 611 612 613
      if order_options.include?('last_journal_user')
        joins << "LEFT OUTER JOIN #{Journal.table_name} ON #{Journal.table_name}.id = (SELECT MAX(#{Journal.table_name}.id) FROM #{Journal.table_name}" +
                " WHERE #{Journal.table_name}.journalized_type='Issue' AND #{Journal.table_name}.journalized_id=#{Issue.table_name}.id AND #{Journal.visible_notes_condition(User.current, :skip_pre_condition => true)})" +
                " LEFT OUTER JOIN #{User.table_name} last_journal_user ON last_journal_user.id = #{Journal.table_name}.user_id";
      end
614
      if order_options.include?('versions')
615 616
        joins << "LEFT OUTER JOIN #{Version.table_name} ON #{Version.table_name}.id = #{queried_table_name}.fixed_version_id"
      end
617
      if order_options.include?('issue_categories')
618 619
        joins << "LEFT OUTER JOIN #{IssueCategory.table_name} ON #{IssueCategory.table_name}.id = #{queried_table_name}.category_id"
      end
620
      if order_options.include?('trackers')
621 622
        joins << "LEFT OUTER JOIN #{Tracker.table_name} ON #{Tracker.table_name}.id = #{queried_table_name}.tracker_id"
      end
623
      if order_options.include?('enumerations')
624 625
        joins << "LEFT OUTER JOIN #{IssuePriority.table_name} ON #{IssuePriority.table_name}.id = #{queried_table_name}.priority_id"
      end
626 627 628 629
    end

    joins.any? ? joins.join(' ') : nil
  end
630
end