Redmine

      elsif field =~ /_id_was\z/

        group = :label_history

    add_available_filter "status_id_was",

      :type => :list, :values => IssueStatus.sorted.collect{|s| [s.name, s.id.to_s] }

    add_available_filter "priority_id_was",

      :type => :list, :values => IssuePriority.all.collect{|s| [s.name, s.id.to_s] }

    add_available_filter "tracker_id_was",

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

    add_available_filter "assigned_to_id_was",

      :type => :list, :values => assigned_to_values unless assigned_to_values.empty?

  def history_filters

    filters.select { |k,_| k =~ /_was\z/ }

  end

  def history_join_clause

    return nil if history_filters.blank?

    history_filters.keys.map do |f|

      "INNER JOIN journals AS j_#{f} ON j_#{f}.journalized_id = issues.id AND j_#{f}.journalized_type = 'Issue'" +

      "INNER JOIN journal_details AS jd_#{f} ON jd_#{f}.journal_id = j_#{f}.id"

    end.join(' ')

  end

    Issue.visible.

      joins(:status, :project).

      joins(history_join_clause).

      where(statement).

      count

          joins(history_join_clause).

      joins(history_join_clause).

      joins(history_join_clause).

  # works for status_id_was & priority_id_was

  def sql_for_history_field(field, operator, values)

    field_name = field.sub('_was', '')

    select_values = values[0..-3]

    select_values = substitute_me_in_history_values(select_values) if field_name == 'assigned_to_id'

    date_from, date_to = values[-2..-1]

    journal_queries = []

    journal_queries << "jd_#{field}.prop_key = '#{field_name}'"

    journal_queries << sql_for_field(field_name, operator, select_values, "jd_#{field}", "old_value")

    date_range_query = sql_for_history_date_range(field, date_from, date_to)

    journal_queries << date_range_query if date_range_query

    journal_queries.join(" AND ")

  end

  def substitute_me_in_history_values(values)

    idx = values.index {|v| v == 'me'}

    return values unless idx

    values[idx] = User.current.logged? ? User.current.id.to_s : '0'

    values

  end

  def sql_for_history_date_range(field, from, to)

    from, to = parse_date(from), parse_date(to)

    from || to ? date_clause("j_#{field}", 'created_on', from, to, false) : nil

  end

      elsif field =~ /_was\z/ && respond_to?("sql_for_history_field")

        filters_clauses << sql_for_history_field(field, operator, v)

      sql = "#{db_table}.#{db_field} IN (SELECT id FROM #{IssueStatus.table_name} WHERE is_closed=#{self.class.connection.quoted_false})" if field == "status_id"

      sql = "#{db_table}.#{db_field} IN (SELECT id FROM #{IssueStatus.table_name} WHERE is_closed=#{self.class.connection.quoted_true})" if field == "status_id"

  field_status_id_was: Previous status

  field_tracker_id_was: Previous tracker

  field_assigned_to_id_was: Previous assignee

  field_priority_id_was: Previous priority

  field_assigned_to_id_was: Poprzednio przypisany do

  field_priority_id_was: Poprzedni priorytet

  field_status_id_was: Poprzedni status

  field_tracker_id_was: Poprzedni typ zagadnienia

    if ( isHistoryFilter(field) ) {

      // history filters have 2 additional values in array

      // corresponding to `from` and `to` dates

      if (values.length > 3) { select.attr('multiple', true); }

    } else {

      if (values.length > 1) { select.attr('multiple', true); }

    }

  if ( isHistoryFilter(fieldId) ) {

    tr.find('td.values').append(

      '<a href="#" id="enable_daterange_'+field+'" class="enable_daterange"><img src="/images/calendar.png" alt="select daterange" title="select daterange"></a>' +

      '<span style="display:none;"><input type="text" name="v['+field+'][]" id="values_'+fieldId+'_from" size="10" class="value date_value previous_date_range" /></span>' +

      '<span style="display:none;"><input type="text" name="v['+field+'][]" id="values_'+fieldId+'_to" size="10" class="value date_value previous_date_range" /></span>' +

      '<a style="display:none;" class="disable_daterange icon icon-cancel" title="disable daterange" href="#" />'

    );

    date_values = values.slice(Math.max(values.length - 2, 1));

    $('#values_'+fieldId+'_from').val(date_values[0]).datepicker(datepickerOptions);

    $('#values_'+fieldId+'_to').val(date_values[1]).datepicker(datepickerOptions);

  }

    if ( isHistoryFilter(field) ) {

      $('#values_'+fieldId+'_from').parent().hide();

      $('#values_'+fieldId+'_to').parent().hide();

      $('#values_'+fieldId+'_to').parent().siblings('a').hide();

    }

    if ( isHistoryFilter(this.id) ) return;

  showHistoryFiltersDateRange();

function isHistoryFilter(filter) {

  return filter.match(/_was$|_was_from$|_was_to$/);

}

function showHistoryFiltersDateRange() {

  $('a.enable_daterange').each(function(){

    var date_inputs = $(this).siblings().find('.previous_date_range');

    var date_values = date_inputs.map(function(){

                        return $(this).val();

                      }).get();

    var matches = $.grep(date_values, function(val){

      return val.match(/^\d{4}-\d{2}-\d{2}$/);

    });

    if ( matches.length > 0 ) {

      $(this).hide();

      date_inputs.closest('span').show();

      $(this).siblings('a.disable_daterange').show();

    } else {

      $(this).show();

      $(this).siblings('a.disable_daterange').hide();

    }

  });

}

  $(document).on('click', 'a.enable_daterange', function(){

    $(this).siblings().find('.previous_date_range').closest('span').show();

    $(this).siblings('a.disable_daterange').show();

    $(this).hide();

  });

  $(document).on('click', 'a.disable_daterange', function(){

    $(this).siblings().find('.previous_date_range').val('').closest('span').hide();

    $(this).siblings('a.enable_daterange').show();

    $(this).hide();

  });

});

$(function(){

.previous_date_range {

  margin-left:15px;

}

a.enable_daterange {

  margin-left:10px;

}

a.enable_daterange img {

  cursor: pointer;

  vertical-align: middle;

  margin-left: 4px;

}

a.disable_daterange {

  margin-left:5px;

}

---

issues_001:

  fixed_version_id:

  assigned_to_id:

issues_002:

  category_id:

  due_date:

issues_003:

  fixed_version_id:

  category_id:

issues_004:

  fixed_version_id:

  category_id:

issues_005:

  fixed_version_id:

  category_id:

  assigned_to_id:

issues_006:

  fixed_version_id:

  category_id:

  assigned_to_id:

issues_007:

  fixed_version_id:

  category_id:

  assigned_to_id:

issues_008:

  fixed_version_id:

  category_id:

  assigned_to_id:

  start_date:

  due_date:

issues_009:

  fixed_version_id:

  category_id:

  assigned_to_id:

issues_010:

  fixed_version_id:

  category_id:

  assigned_to_id:

issues_011:

  fixed_version_id: 1

  assigned_to_id:

issues_012:

  fixed_version_id: 2

  assigned_to_id:

  fixed_version_id:

  category_id:

  assigned_to_id:

  fixed_version_id:

  category_id:

  description: This is a private issue

  tracker_id: 1

  assigned_to_id:

  author_id: 2

  status_id: 1

  is_private: true

  root_id: 14

  lft: 1

  rgt: 2

issues_015:

  id: 15

  created_on: <%= 15.days.ago.to_s(:db) %>

  project_id: 1

  updated_on: <%= 15.days.ago.to_s(:db) %>

  priority_id: 5

  subject: Private issue on public project

  fixed_version_id:

  category_id:

  description: This is a private issue

  tracker_id: 1

  assigned_to_id:

  author_id: 2

  status_id: 1

  is_private: true

  root_id: 14

  lft: 1

  rgt: 2

issues_016:

  id: 16

  created_on: <%= 15.days.ago.to_s(:db) %>

  project_id: 1

  updated_on: <%= 15.days.ago.to_s(:db) %>

  priority_id: 5

  subject: Private issue on public project

  fixed_version_id:

  category_id:

  description: This is a private issue

  tracker_id: 1

  assigned_to_id:

  author_id: 2

  status_id: 1

  is_private: true

  root_id: 14

  lft: 1

  rgt: 2

issues_015:

  id: 15

  created_on: <%= 15.days.ago.to_s(:db) %>

  project_id: 1

  updated_on: <%= 15.days.ago.to_s(:db) %>

  priority_id: 5

  subject: Private issue on public project

  fixed_version_id:

  category_id:

  description: This is a private issue

  tracker_id: 1

  assigned_to_id:

  author_id: 2

  status_id: 1

  is_private: true

  root_id: 14

  lft: 1

  rgt: 2

issues_016:

  id: 16

  created_on: <%= 15.days.ago.to_s(:db) %>

  project_id: 1

  updated_on: <%= 15.days.ago.to_s(:db) %>

  priority_id: 5

  subject: Private issue on public project

  fixed_version_id:

  category_id:

  assigned_to_id:

---

journal_details_001:

journal_details_002:

journal_details_007:

  old_value: '1'

  value: '4'

  property: attr

  id: 7

  prop_key: status_id

  journal_id: 6

journal_details_008:

  old_value: '4'

  value: '3'

  property: attr

  id: 8

  prop_key: status_id

  journal_id: 7

journal_details_009:

  old_value: '4'

  value: '1'

  property: attr

  id: 9

  prop_key: priority_id

  journal_id: 8

journal_details_010:

  old_value: '4'

  value: '1'

  property: attr

  id: 10

  prop_key: priority_id

  journal_id: 9

journal_details_011:

  old_value: ''

  value: '2'

  property: attr

  id: 11

  prop_key: assigned_to_id

  journal_id: 10

journal_details_012:

  old_value: '2'

  value: '3'

  property: attr

  id: 12

  prop_key: assigned_to_id

  journal_id: 11

journal_details_013:

  old_value: '1'

  value: '2'

  property: attr

  id: 13

  prop_key: tracker_id

  journal_id: 12

---

journals_002:

journals_006:

  id: 6

  created_on: <%= 14.days.ago.to_date.to_s(:db) %>

  notes: "Change status"

  user_id: 2

  journalized_type: Issue

  journalized_id: 15

journals_007:

  id: 7

  created_on: <%= 13.days.ago.to_date.to_s(:db) %>

  notes: "Change status"

  user_id: 2

  journalized_type: Issue

  journalized_id: 15

journals_008:

  id: 8

  created_on: <%= 12.days.ago.to_date.to_s(:db) %>

  notes: "Change priority"

  user_id: 2

  journalized_type: Issue

  journalized_id: 15

journals_009:

  id: 9

  created_on: <%= 5.days.ago.to_date.to_s(:db) %>

  notes: "Change priority"

  user_id: 2

  journalized_type: Issue

  journalized_id: 16

journals_010:

  id: 10

  created_on: <%= 8.days.ago.to_date.to_s(:db) %>

  notes: "Change asignee"

  user_id: 2

  journalized_type: Issue

  journalized_id: 16

journals_011:

  id: 11

  created_on: <%= 6.days.ago.to_date.to_s(:db) %>

  notes: "Change asignee"

  user_id: 2

  journalized_type: Issue

  journalized_id: 16

journals_012:

  id: 12

  created_on: <%= 4.days.ago.to_date.to_s(:db) %>

  notes: "Change tracker"

  user_id: 2

  journalized_type: Issue

  journalized_id: 16

# Redmine - project management software

# Copyright (C) 2006-2015  Jean-Philippe Lang

#

# 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.

require File.expand_path('../../test_helper', __FILE__)

class QueryTest < ActiveSupport::TestCase

  include Redmine::I18n

  fixtures :projects, :enabled_modules, :users, :members,

           :member_roles, :roles, :trackers, :issue_statuses,

           :issue_categories, :enumerations, :issues,

           :watchers, :custom_fields, :custom_values, :versions,

           :queries,

           :projects_trackers,

           :custom_fields_trackers,

           :workflows,

           :journals,

           :journal_details

  def setup

    User.current = nil

  end

  def test_available_filters_includes_status_id_was

    query = IssueQuery.new(:project => Project.find(1), :name => '_')

    assert query.available_filters.has_key? 'status_id_was'

    assert_equal :list, query.available_filters['status_id_was'][:type]

    assert_equal query.available_filters['status_id'][:values], query.available_filters['status_id_was'][:values]

  end

  def test_available_filters_includes_priority_id_was

    query = IssueQuery.new(:project => Project.find(1), :name => '_')

    assert query.available_filters.has_key? 'priority_id_was'

    assert_equal :list, query.available_filters['priority_id_was'][:type]

    assert_equal query.available_filters['priority_id'][:values], query.available_filters['priority_id_was'][:values]

  end

  def test_status_id_was_is_added_as_filter

    add_filter_to_query('status_id_was', '=', ['2', '', ''])

    assert @query.available_filters.has_key? @field

    assert @query.filters['status_id_was'] == { operator: @operator, values: @values }

  end

  def test_priority_id_was_is_added_as_filter

    add_filter_to_query('priority_id_was', '=', ['1', '', ''])

    assert @query.available_filters.has_key? @field

    assert @query.filters['priority_id_was'] == { operator: @operator, values: @values }

  end

  def test_status_id_was_with_dates_is_added_as_filter

    add_filter_to_query('status_id_was', '=', ['2', '2012-02-02', '2015-03-03'])

    assert @query.available_filters.has_key? @field

    assert @query.filters['status_id_was'] == { operator: @operator, values: @values }

  end

  def test_priority_id_was_with_dates_is_added_as_filter

    add_filter_to_query('priority_id_was', '=', ['2', '2012-02-02', '2015-03-03'])

    assert @query.available_filters.has_key? @field

    assert @query.filters['priority_id_was'] == { operator: @operator, values: @values }

  end

  def test_status_id_was_gets_proper_sql_query_with_no_dates

    add_filter_to_query('status_id_was', '=', ['2', '', ''])

    generated_query = @query.sql_for_history_field(@field, @operator, @values)

    expected_query = "jd_status_id_was.prop_key = 'status_id' AND jd_status_id_was.old_value IN ('2')"

    assert_equal expected_query, generated_query

    assert_match expected_query, @query.statement, 'query not included in statement'

  end

  def test_priority_id_was_gets_proper_sql_query_with_no_dates

    add_filter_to_query('priority_id_was', '=', ['2', '', ''])

    generated_query = @query.sql_for_history_field(@field, @operator, @values)

    expected_query = "jd_priority_id_was.prop_key = 'priority_id' AND jd_priority_id_was.old_value IN ('2')"

    assert_equal expected_query, generated_query

    assert_match expected_query, @query.statement, 'query not included in statement'

  end

  def test_status_id_was_gets_proper_sql_query_with_both_dates

    add_filter_to_query('status_id_was', '=', ['2', '2012-02-02', '2015-03-03'])

    generated_query = @query.sql_for_history_field(@field, @operator, @values)

    expected_query = "jd_status_id_was.prop_key = 'status_id' AND jd_status_id_was.old_value IN ('2') AND j_status_id_was.created_on > '2012-02-01 23:59:59.999999' AND j_status_id_was.created_on <= '2015-03-03 23:59:59.999999'"

    assert_equal expected_query, generated_query

    assert_match expected_query, @query.statement, 'query not included in statement'

  end

  def test_status_id_was_gets_proper_sql_query_with_both_dates

    add_filter_to_query('priority_id_was', '=', ['2', '2012-02-02', '2015-03-03'])

    generated_query = @query.sql_for_history_field(@field, @operator, @values)

    expected_query = "jd_priority_id_was.prop_key = 'priority_id' AND jd_priority_id_was.old_value IN ('2') AND j_priority_id_was.created_on > '2012-02-01 23:59:59.999999' AND j_priority_id_was.created_on <= '2015-03-03 23:59:59.999999'"

    assert_equal expected_query, generated_query

    assert_match expected_query, @query.statement, 'query not included in statement'

  end

  def test_status_id_was_gets_proper_sql_query_with_from_date

    add_filter_to_query('status_id_was', '=', ['2', '2012-02-02', ''])

    generated_query = @query.sql_for_history_field(@field, @operator, @values)

    expected_query = "jd_status_id_was.prop_key = 'status_id' AND jd_status_id_was.old_value IN ('2') AND j_status_id_was.created_on > '2012-02-01 23:59:59.999999'"

    assert_equal expected_query, generated_query

    assert_match expected_query, @query.statement, 'query not included in statement'

  end

  def test_priority_id_was_gets_proper_sql_query_with_from_date

    add_filter_to_query('priority_id_was', '=', ['2', '2012-02-02', ''])

    generated_query = @query.sql_for_history_field(@field, @operator, @values)

    expected_query = "jd_priority_id_was.prop_key = 'priority_id' AND jd_priority_id_was.old_value IN ('2') AND j_priority_id_was.created_on > '2012-02-01 23:59:59.999999'"

    assert_equal expected_query, generated_query

    assert_match expected_query, @query.statement, 'query not included in statement'

  end

  def test_status_id_was_gets_proper_sql_query_with_to_date

    add_filter_to_query('status_id_was', '=', ['2', '', '2012-02-02'])

    generated_query = @query.sql_for_history_field(@field, @operator, @values)

    expected_query = "jd_status_id_was.prop_key = 'status_id' AND jd_status_id_was.old_value IN ('2') AND j_status_id_was.created_on <= '2012-02-02 23:59:59.999999'"

    assert_equal expected_query, generated_query

    assert_match expected_query, @query.statement, 'query not included in statement'

  end

  def test_status_id_was_gets_proper_sql_query_with_to_date

    add_filter_to_query('priority_id_was', '=', ['2', '', '2012-02-02'])

    generated_query = @query.sql_for_history_field(@field, @operator, @values)

    expected_query = "jd_priority_id_was.prop_key = 'priority_id' AND jd_priority_id_was.old_value IN ('2') AND j_priority_id_was.created_on <= '2012-02-02 23:59:59.999999'"

    assert_equal expected_query, generated_query

    assert_match expected_query, @query.statement, 'query not included in statement'

  end

  def test_status_id_issues_return_proper_issue_without_date

    add_filter_to_query('status_id_was', '=', ['4', '', ''])

    assert_include Issue.find(15), @query.issues

    assert_equal 1, @query.issues.count

  end

  def test_status_id_issues_return_proper_issue_with_date

    add_filter_to_query('status_id_was', '=', ['4', (Date.today-15.days).to_s, (Date.today-10.days).to_s])

    assert_include Issue.find(15), @query.issues

    assert_equal 1, @query.issues.count

  end

  def test_status_id_shouldnt_return_any_issue_for_a_specific_range

    add_filter_to_query('status_id_was', '=', ['4', (Date.today-8.days).to_s, (Date.today-7.days).to_s])

    assert_not_include Issue.find(15), @query.issues

  end

  def test_status_id_issues_return_proper_issues_without_date

    add_filter_to_query('priority_id_was', '=', ['4', '', ''])

    assert_include Issue.find(15), @query.issues

    assert_include Issue.find(16), @query.issues

    assert_equal 2, @query.issues.count

  end

  def test_status_id_issues_return_proper_issues_without_date

    add_filter_to_query('priority_id_was', '=', ['4', (Date.today-7.days).to_s, (Date.today-3.days).to_s])

    assert_not_include Issue.find(15), @query.issues

    assert_include Issue.find(16), @query.issues

  end

  def test_assigned_to_id_issues_return_previous_asignee_with_dates

    add_filter_to_query('assigned_to_id_was', '=', ['2', (Date.today-10.days).to_s, (Date.today-3.days).to_s])

    assert_include Issue.find(16), @query.issues

    assert_equal 1, @query.issues.count

  end

  def test_assigned_to_id_issues_return_previous_asignee_without_dates

    add_filter_to_query('assigned_to_id_was', '=', ['2', '', ''])

    assert_include Issue.find(16), @query.issues

    assert_equal 1, @query.issues.count

  end

  def test_assigned_to_id_issues_return_proper_issues_for_me_value

    User.current = User.find(2)

    add_filter_to_query('assigned_to_id_was', '=', ['me', (Date.today-10.days).to_s, (Date.today-3.days).to_s])

    assert_include Issue.find(16), @query.issues

    assert_equal 1, @query.issues.count

  end

  def test_tracker_id_issues_return_proper_issue_with_dates

    add_filter_to_query('tracker_id_was', '=', ['1', (Date.today-10.days).to_s, (Date.today-3.days).to_s])

    assert_include Issue.find(16), @query.issues

    assert_equal 1, @query.issues.count

  end

  def test_filters_return_issue_for_a_combined_history_query

    add_filter_to_query('priority_id_was', '=', ['4', (Date.today-7.days).to_s, (Date.today-3.days).to_s])

    add_filter_to_query('tracker_id_was', '=', ['1', (Date.today-10.days).to_s, (Date.today-3.days).to_s])

    add_filter_to_query('assigned_to_id_was', '=', ['2', (Date.today-10.days).to_s, (Date.today-3.days).to_s])

    assert_include Issue.find(16), @query.issues

    assert_equal 1, @query.issues.count

  end

  private

  def add_filter_to_query(field, operator, values)

    @query ||= IssueQuery.new(:project => Project.find(1), :name => '_')

    @field, @operator, @values = field, operator, values

    @query.add_filter(field, operator, values)

    assert @query.valid?

  end

end