Redmine

    add_available_filter(

      "user.group",

      :type => :list_optional,

      :name => l("label_attribute_of_user", :name => l(:label_group)),

      :values => lambda {Group.givable.visible.pluck(:name, :id).map {|name, id| [name, id.to_s]}}

    )

    add_available_filter(

      "user.role",

      :type => :list_optional,

      :name => l("label_attribute_of_user", :name => l(:field_role)),

      :values => lambda {Role.givable.pluck(:name, :id).map {|name, id| [name, id.to_s]}}

    )

  def sql_for_user_group_field(field, operator, value)

    if operator == '*' # Any group

      groups = Group.givable

      operator = '='

    elsif operator == '!*'

      groups = Group.givable

      operator = '!'

    else

      groups = Group.where(:id => value).to_a

    end

    groups ||= []

    members_of_groups = groups.inject([]) do |user_ids, group|

      user_ids + group.user_ids

    end.uniq.compact.sort.collect(&:to_s)

    '(' + sql_for_field('user_id', operator, members_of_groups, TimeEntry.table_name, "user_id", false) + ')'

  end

  def sql_for_user_role_field(field, operator, value)

    case operator

    when "*", "!*"

      sw = operator == "!*" ? "NOT" : ""

      nl = operator == "!*" ? "#{TimeEntry.table_name}.user_id IS NULL OR" : ""

      subquery =

        "SELECT 1" +

        " FROM #{Member.table_name}" +

        " WHERE #{TimeEntry.table_name}.project_id = #{Member.table_name}.project_id AND #{Member.table_name}.user_id = #{TimeEntry.table_name}.user_id"

      "(#{nl} #{sw} EXISTS (#{subquery}))"

    when "=", "!"

      role_cond =

        if value.any?

          "#{MemberRole.table_name}.role_id IN (" + value.collect{|val| "'#{self.class.connection.quote_string(val)}'"}.join(",") + ")"

        else

          "1=0"

        end

      sw = operator == "!" ? 'NOT' : ''

      nl = operator == "!" ? "#{TimeEntry.table_name}.user_id IS NULL OR" : ''

      subquery =

        "SELECT 1" +

        " FROM #{Member.table_name} inner join #{MemberRole.table_name} on members.id = member_roles.member_id" +

        " WHERE #{TimeEntry.table_name}.project_id = #{Member.table_name}.project_id AND #{Member.table_name}.user_id = #{TimeEntry.table_name}.user_id AND #{role_cond}"

      "(#{nl} #{sw} EXISTS (#{subquery}))"

    end

  end

  def test_user_group_filter_should_consider_spacified_groups_time_entries

    Group.find(10).users << User.find(2)

    Group.find(11).users << User.find(3)

    TimeEntry.delete_all

    t1 = TimeEntry.generate!(:hours => 1.0, :user_id => 2)

    t2 = TimeEntry.generate!(:hours => 2.0, :user_id => 2)

    t3 = TimeEntry.generate!(:hours => 4.0, :user_id => 3)

    query = TimeEntryQuery.new(:name => '_')

    result = query.base_scope.to_a

    assert result.include?(t1)

    assert result.include?(t2)

    assert result.include?(t3)

    assert_equal 7.0, query.results_scope.sum(:hours)

    query.add_filter('user.group', '=', ['10'])

    result = query.base_scope.to_a

    assert result.include?(t1)

    assert result.include?(t2)

    assert_not result.include?(t3)

    assert_equal 3.0, query.results_scope.sum(:hours)

    query.add_filter('user.group', '=', ['10', '11'])

    result = query.base_scope.to_a

    assert result.include?(t1)

    assert result.include?(t2)

    assert result.include?(t3)

    assert_equal 7.0, query.results_scope.sum(:hours)

  end

  def test_user_role_filter_should_consider_spacified_roles_time_entries

    project = Project.find(1)

    project.members << Member.new(:user_id => 2, :roles => [Role.find(1)])

    project.members << Member.new(:user_id => 3, :roles => [Role.find(2)])

    TimeEntry.delete_all

    t1 = TimeEntry.generate!(:project => project, :hours => 1.0, :user_id => 2)

    t2 = TimeEntry.generate!(:project => project, :hours => 2.0, :user_id => 2)

    t3 = TimeEntry.generate!(:project => project, :hours => 4.0, :user_id => 3)

    query = TimeEntryQuery.new(:project => project, :name => '_')

    result = query.base_scope.to_a

    assert result.include?(t1)

    assert result.include?(t2)

    assert result.include?(t3)

    assert_equal 7.0, query.results_scope.sum(:hours)

    query.add_filter('user.role', '=', ['1'])

    result = query.base_scope.to_a

    assert result.include?(t1)

    assert result.include?(t2)

    assert_not result.include?(t3)

    assert_equal 3.0, query.results_scope.sum(:hours)

    query.add_filter('user.role', '=', ['1', '2'])

    result = query.base_scope.to_a

    assert result.include?(t1)

    assert result.include?(t2)

    assert result.include?(t3)

    assert_equal 7.0, query.results_scope.sum(:hours)

  end