jro_apps/redmine Commit - r14429:495be400a181

1

# Redmine - project management software

1

# Redmine - project management software

2

3

#

3

#

4

# This program is free software; you can redistribute it and/or

4

# This program is free software; you can redistribute it and/or

5

# modify it under the terms of the GNU General Public License

5

# modify it under the terms of the GNU General Public License

6

# as published by the Free Software Foundation; either version 2

6

# as published by the Free Software Foundation; either version 2

7

# of the License, or (at your option) any later version.

7

# of the License, or (at your option) any later version.

8

#

8

#

9

# This program is distributed in the hope that it will be useful,

9

# This program is distributed in the hope that it will be useful,

10

# but WITHOUT ANY WARRANTY; without even the implied warranty of

10

# but WITHOUT ANY WARRANTY; without even the implied warranty of

11

# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the

11

# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the

12

# GNU General Public License for more details.

12

# GNU General Public License for more details.

13

#

13

#

14

# You should have received a copy of the GNU General Public License

14

# You should have received a copy of the GNU General Public License

15

# along with this program; if not, write to the Free Software

15

# along with this program; if not, write to the Free Software

16

# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.

16

# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.

17

18

class IssueQuery < Query

18

class IssueQuery < Query

19

20

self.queried_class = Issue

20

self.queried_class = Issue

21

22

self.available_columns = [

22

self.available_columns = [

23

QueryColumn.new(:id, :sortable => "#{Issue.table_name}.id", :default_order => 'desc', :caption => '#', :frozen => true),

23

QueryColumn.new(:id, :sortable => "#{Issue.table_name}.id", :default_order => 'desc', :caption => '#', :frozen => true),

24

QueryColumn.new(:project, :sortable => "#{Project.table_name}.name", :groupable => true),

24

QueryColumn.new(:project, :sortable => "#{Project.table_name}.name", :groupable => true),

25

QueryColumn.new(:tracker, :sortable => "#{Tracker.table_name}.position", :groupable => true),

25

QueryColumn.new(:tracker, :sortable => "#{Tracker.table_name}.position", :groupable => true),

26

QueryColumn.new(:parent, :sortable => ["#{Issue.table_name}.root_id", "#{Issue.table_name}.lft ASC"], :default_order => 'desc', :caption => :field_parent_issue),

26

QueryColumn.new(:parent, :sortable => ["#{Issue.table_name}.root_id", "#{Issue.table_name}.lft ASC"], :default_order => 'desc', :caption => :field_parent_issue),

27

QueryColumn.new(:status, :sortable => "#{IssueStatus.table_name}.position", :groupable => true),

27

QueryColumn.new(:status, :sortable => "#{IssueStatus.table_name}.position", :groupable => true),

28

QueryColumn.new(:priority, :sortable => "#{IssuePriority.table_name}.position", :default_order => 'desc', :groupable => true),

28

QueryColumn.new(:priority, :sortable => "#{IssuePriority.table_name}.position", :default_order => 'desc', :groupable => true),

29

QueryColumn.new(:subject, :sortable => "#{Issue.table_name}.subject"),

29

QueryColumn.new(:subject, :sortable => "#{Issue.table_name}.subject"),

30

QueryColumn.new(:author, :sortable => lambda {User.fields_for_order_statement("authors")}, :groupable => true),

30

QueryColumn.new(:author, :sortable => lambda {User.fields_for_order_statement("authors")}, :groupable => true),

31

QueryColumn.new(:assigned_to, :sortable => lambda {User.fields_for_order_statement}, :groupable => true),

31

QueryColumn.new(:assigned_to, :sortable => lambda {User.fields_for_order_statement}, :groupable => true),

32

QueryColumn.new(:updated_on, :sortable => "#{Issue.table_name}.updated_on", :default_order => 'desc'),

32

QueryColumn.new(:updated_on, :sortable => "#{Issue.table_name}.updated_on", :default_order => 'desc'),

33

QueryColumn.new(:category, :sortable => "#{IssueCategory.table_name}.name", :groupable => true),

33

QueryColumn.new(:category, :sortable => "#{IssueCategory.table_name}.name", :groupable => true),

34

QueryColumn.new(:fixed_version, :sortable => lambda {Version.fields_for_order_statement}, :groupable => true),

34

QueryColumn.new(:fixed_version, :sortable => lambda {Version.fields_for_order_statement}, :groupable => true),

35

QueryColumn.new(:start_date, :sortable => "#{Issue.table_name}.start_date"),

35

QueryColumn.new(:start_date, :sortable => "#{Issue.table_name}.start_date"),

36

QueryColumn.new(:due_date, :sortable => "#{Issue.table_name}.due_date"),

36

QueryColumn.new(:due_date, :sortable => "#{Issue.table_name}.due_date"),

37

QueryColumn.new(:estimated_hours, :sortable => "#{Issue.table_name}.estimated_hours", :totalable => true),

37

QueryColumn.new(:estimated_hours, :sortable => "#{Issue.table_name}.estimated_hours", :totalable => true),

38

QueryColumn.new(:total_estimated_hours,

38

QueryColumn.new(:total_estimated_hours,

39

:sortable => "COALESCE((SELECT SUM(estimated_hours) FROM #{Issue.table_name} subtasks" +

39

:sortable => "COALESCE((SELECT SUM(estimated_hours) FROM #{Issue.table_name} subtasks" +

40

" WHERE subtasks.root_id = #{Issue.table_name}.root_id AND subtasks.lft >= #{Issue.table_name}.lft AND subtasks.rgt <= #{Issue.table_name}.rgt), 0)",

40

" WHERE subtasks.root_id = #{Issue.table_name}.root_id AND subtasks.lft >= #{Issue.table_name}.lft AND subtasks.rgt <= #{Issue.table_name}.rgt), 0)",

41

:default_order => 'desc'),

41

:default_order => 'desc'),

42

QueryColumn.new(:done_ratio, :sortable => "#{Issue.table_name}.done_ratio", :groupable => true),

42

QueryColumn.new(:done_ratio, :sortable => "#{Issue.table_name}.done_ratio", :groupable => true),

43

QueryColumn.new(:created_on, :sortable => "#{Issue.table_name}.created_on", :default_order => 'desc'),

43

QueryColumn.new(:created_on, :sortable => "#{Issue.table_name}.created_on", :default_order => 'desc'),

44

QueryColumn.new(:closed_on, :sortable => "#{Issue.table_name}.closed_on", :default_order => 'desc'),

44

QueryColumn.new(:closed_on, :sortable => "#{Issue.table_name}.closed_on", :default_order => 'desc'),

45

QueryColumn.new(:relations, :caption => :label_related_issues),

45

QueryColumn.new(:relations, :caption => :label_related_issues),

46

QueryColumn.new(:description, :inline => false)

46

QueryColumn.new(:description, :inline => false)

47

]

47

]

48

49

scope :visible, lambda {|*args|

49

scope :visible, lambda {|*args|

50

user = args.shift || User.current

50

user = args.shift || User.current

51

base = Project.allowed_to_condition(user, :view_issues, *args)

51

base = Project.allowed_to_condition(user, :view_issues, *args)

52

scope = joins("LEFT OUTER JOIN #{Project.table_name} ON #{table_name}.project_id = #{Project.table_name}.id").

52

scope = joins("LEFT OUTER JOIN #{Project.table_name} ON #{table_name}.project_id = #{Project.table_name}.id").

53

where("#{table_name}.project_id IS NULL OR (#{base})")

53

where("#{table_name}.project_id IS NULL OR (#{base})")

54

55

if user.admin?

55

if user.admin?

56

scope.where("#{table_name}.visibility <> ? OR #{table_name}.user_id = ?", VISIBILITY_PRIVATE, user.id)

56

scope.where("#{table_name}.visibility <> ? OR #{table_name}.user_id = ?", VISIBILITY_PRIVATE, user.id)

57

elsif user.memberships.any?

57

elsif user.memberships.any?

58

scope.where("#{table_name}.visibility = ?" +

58

scope.where("#{table_name}.visibility = ?" +

59

" OR (#{table_name}.visibility = ? AND #{table_name}.id IN (" +

59

" OR (#{table_name}.visibility = ? AND #{table_name}.id IN (" +

60

"SELECT DISTINCT q.id FROM #{table_name} q" +

60

"SELECT DISTINCT q.id FROM #{table_name} q" +

61

" INNER JOIN #{table_name_prefix}queries_roles#{table_name_suffix} qr on qr.query_id = q.id" +

61

" INNER JOIN #{table_name_prefix}queries_roles#{table_name_suffix} qr on qr.query_id = q.id" +

62

" INNER JOIN #{MemberRole.table_name} mr ON mr.role_id = qr.role_id" +

62

" INNER JOIN #{MemberRole.table_name} mr ON mr.role_id = qr.role_id" +

63

" INNER JOIN #{Member.table_name} m ON m.id = mr.member_id AND m.user_id = ?" +

63

" INNER JOIN #{Member.table_name} m ON m.id = mr.member_id AND m.user_id = ?" +

64

" WHERE q.project_id IS NULL OR q.project_id = m.project_id))" +

64

" WHERE q.project_id IS NULL OR q.project_id = m.project_id))" +

65

" OR #{table_name}.user_id = ?",

65

" OR #{table_name}.user_id = ?",

66

VISIBILITY_PUBLIC, VISIBILITY_ROLES, user.id, user.id)

66

VISIBILITY_PUBLIC, VISIBILITY_ROLES, user.id, user.id)

67

elsif user.logged?

67

elsif user.logged?

68

scope.where("#{table_name}.visibility = ? OR #{table_name}.user_id = ?", VISIBILITY_PUBLIC, user.id)

68

scope.where("#{table_name}.visibility = ? OR #{table_name}.user_id = ?", VISIBILITY_PUBLIC, user.id)

69

else

69

else

70

scope.where("#{table_name}.visibility = ?", VISIBILITY_PUBLIC)

70

scope.where("#{table_name}.visibility = ?", VISIBILITY_PUBLIC)

71

end

71

end

72

}

72

}

73

74

def initialize(attributes=nil, *args)

74

def initialize(attributes=nil, *args)

75

super attributes

75

super attributes

76

self.filters ||= { 'status_id' => {:operator => "o", :values => [""]} }

76

self.filters ||= { 'status_id' => {:operator => "o", :values => [""]} }

77

end

77

end

78

79

# Returns true if the query is visible to +user+ or the current user.

79

# Returns true if the query is visible to +user+ or the current user.

80

def visible?(user=User.current)

80

def visible?(user=User.current)

81

return true if user.admin?

81

return true if user.admin?

82

return false unless project.nil? || user.allowed_to?(:view_issues, project)

82

return false unless project.nil? || user.allowed_to?(:view_issues, project)

83

case visibility

83

case visibility

84

when VISIBILITY_PUBLIC

84

when VISIBILITY_PUBLIC

85

true

85

true

86

when VISIBILITY_ROLES

86

when VISIBILITY_ROLES

87

if project

87

if project

88

(user.roles_for_project(project) & roles).any?

88

(user.roles_for_project(project) & roles).any?

89

else

89

else

90

Member.where(:user_id => user.id).joins(:roles).where(:member_roles => {:role_id => roles.map(&:id)}).any?

90

Member.where(:user_id => user.id).joins(:roles).where(:member_roles => {:role_id => roles.map(&:id)}).any?

91

end

91

end

92

else

92

else

93

user == self.user

93

user == self.user

94

end

94

end

95

end

95

end

96

97

def is_private?

97

def is_private?

98

visibility == VISIBILITY_PRIVATE

98

visibility == VISIBILITY_PRIVATE

99

end

99

end

100

101

def is_public?

101

def is_public?

102

!is_private?

102

!is_private?

103

end

103

end

104

105

def draw_relations

105

def draw_relations

106

r = options[:draw_relations]

106

r = options[:draw_relations]

107

r.nil? || r == '1'

107

r.nil? || r == '1'

108

end

108

end

109

110

def draw_relations=(arg)

110

def draw_relations=(arg)

111

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

111

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

112

end

112

end

113

114

def draw_progress_line

114

def draw_progress_line

115

r = options[:draw_progress_line]

115

r = options[:draw_progress_line]

116

r == '1'

116

r == '1'

117

end

117

end

118

119

def draw_progress_line=(arg)

119

def draw_progress_line=(arg)

120

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

120

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

121

end

121

end

122

123

def build_from_params(params)

123

def build_from_params(params)

124

super

124

super

125

self.draw_relations = params[:draw_relations] || (params[:query] && params[:query][:draw_relations])

125

self.draw_relations = params[:draw_relations] || (params[:query] && params[:query][:draw_relations])

126

self.draw_progress_line = params[:draw_progress_line] || (params[:query] && params[:query][:draw_progress_line])

126

self.draw_progress_line = params[:draw_progress_line] || (params[:query] && params[:query][:draw_progress_line])

127

self

127

self

128

end

128

end

129

130

def initialize_available_filters

130

def initialize_available_filters

131

principals = []

131

principals = []

132

subprojects = []

132

subprojects = []

133

versions = []

133

versions = []

134

categories = []

134

categories = []

135

issue_custom_fields = []

135

issue_custom_fields = []

136

137

if project

137

if project

138

principals += project.principals.visible

138

principals += project.principals.visible

139

unless project.leaf?

139

unless project.leaf?

140

subprojects = project.descendants.visible.to_a

140

subprojects = project.descendants.visible.to_a

141

principals += Principal.member_of(subprojects).visible

141

principals += Principal.member_of(subprojects).visible

142

end

142

end

143

versions = project.shared_versions.to_a

143

versions = project.shared_versions.to_a

144

categories = project.issue_categories.to_a

144

categories = project.issue_categories.to_a

145

issue_custom_fields = project.all_issue_custom_fields

145

issue_custom_fields = project.all_issue_custom_fields

146

else

146

else

147

if all_projects.any?

147

if all_projects.any?

148

principals += Principal.member_of(all_projects).visible

148

principals += Principal.member_of(all_projects).visible

149

end

149

end

150

versions = Version.visible.where(:sharing => 'system').to_a

150

versions = Version.visible.where(:sharing => 'system').to_a

151

issue_custom_fields = IssueCustomField.where(:is_for_all => true)

151

issue_custom_fields = IssueCustomField.where(:is_for_all => true)

152

end

152

end

153

principals.uniq!

153

principals.uniq!

154

principals.sort!

154

principals.sort!

155

principals.reject! {|p| p.is_a?(GroupBuiltin)}

155

principals.reject! {|p| p.is_a?(GroupBuiltin)}

156

users = principals.select {|p| p.is_a?(User)}

156

users = principals.select {|p| p.is_a?(User)}

157

158

add_available_filter "status_id",

158

add_available_filter "status_id",

159

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

159

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

160

161

if project.nil?

161

if project.nil?

162

project_values = []

162

project_values = []

163

if User.current.logged? && User.current.memberships.any?

163

if User.current.logged? && User.current.memberships.any?

164

project_values << ["<< #{l(:label_my_projects).downcase} >>", "mine"]

164

project_values << ["<< #{l(:label_my_projects).downcase} >>", "mine"]

165

end

165

end

166

project_values += all_projects_values

166

project_values += all_projects_values

167

add_available_filter("project_id",

167

add_available_filter("project_id",

168

:type => :list, :values => project_values

168

:type => :list, :values => project_values

169

) unless project_values.empty?

169

) unless project_values.empty?

170

end

170

end

171

172

add_available_filter "tracker_id",

172

add_available_filter "tracker_id",

173

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

173

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

174

add_available_filter "priority_id",

174

add_available_filter "priority_id",

175

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

175

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

176

177

author_values = []

177

author_values = []

178

author_values << ["<< #{l(:label_me)} >>", "me"] if User.current.logged?

178

author_values << ["<< #{l(:label_me)} >>", "me"] if User.current.logged?

179

author_values += users.collect{|s| [s.name, s.id.to_s] }

179

author_values += users.collect{|s| [s.name, s.id.to_s] }

180

add_available_filter("author_id",

180

add_available_filter("author_id",

181

:type => :list, :values => author_values

181

:type => :list, :values => author_values

182

) unless author_values.empty?

182

) unless author_values.empty?

183

184

assigned_to_values = []

184

assigned_to_values = []

185

assigned_to_values << ["<< #{l(:label_me)} >>", "me"] if User.current.logged?

185

assigned_to_values << ["<< #{l(:label_me)} >>", "me"] if User.current.logged?

186

assigned_to_values += (Setting.issue_group_assignment? ?

186

assigned_to_values += (Setting.issue_group_assignment? ?

187

principals : users).collect{|s| [s.name, s.id.to_s] }

187

principals : users).collect{|s| [s.name, s.id.to_s] }

188

add_available_filter("assigned_to_id",

188

add_available_filter("assigned_to_id",

189

:type => :list_optional, :values => assigned_to_values

189

:type => :list_optional, :values => assigned_to_values

190

) unless assigned_to_values.empty?

190

) unless assigned_to_values.empty?

191

192

group_values = Group.givable.visible.collect {|g| [g.name, g.id.to_s] }

192

group_values = Group.givable.visible.collect {|g| [g.name, g.id.to_s] }

193

add_available_filter("member_of_group",

193

add_available_filter("member_of_group",

194

:type => :list_optional, :values => group_values

194

:type => :list_optional, :values => group_values

195

) unless group_values.empty?

195

) unless group_values.empty?

196

197

role_values = Role.givable.collect {|r| [r.name, r.id.to_s] }

197

role_values = Role.givable.collect {|r| [r.name, r.id.to_s] }

198

add_available_filter("assigned_to_role",

198

add_available_filter("assigned_to_role",

199

:type => :list_optional, :values => role_values

199

:type => :list_optional, :values => role_values

200

) unless role_values.empty?

200

) unless role_values.empty?

201

202

if versions.any?

202

add_available_filter "fixed_version_id",

203

add_available_filter "fixed_version_id",

203

:type => :list_optional,

204

:type => :list_optional,

204

:values => versions.sort.collect{|s| ["#{s.project.name} - #{s.name}", s.id.to_s] }

205

:values => versions.sort.collect{|s| ["#{s.project.name} - #{s.name}", s.id.to_s] }

206

end

207

205

208

if categories.any?

206

add_available_filter "category_id",

209

add_available_filter "category_id",

207

:type => :list_optional,

210

:type => :list_optional,

208

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

211

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

212

end

213

209

214

add_available_filter "subject", :type => :text

210

add_available_filter "subject", :type => :text

215

add_available_filter "description", :type => :text

211

add_available_filter "description", :type => :text

216

add_available_filter "created_on", :type => :date_past

212

add_available_filter "created_on", :type => :date_past

217

add_available_filter "updated_on", :type => :date_past

213

add_available_filter "updated_on", :type => :date_past

218

add_available_filter "closed_on", :type => :date_past

214

add_available_filter "closed_on", :type => :date_past

219

add_available_filter "start_date", :type => :date

215

add_available_filter "start_date", :type => :date

220

add_available_filter "due_date", :type => :date

216

add_available_filter "due_date", :type => :date

221

add_available_filter "estimated_hours", :type => :float

217

add_available_filter "estimated_hours", :type => :float

222

add_available_filter "done_ratio", :type => :integer

218

add_available_filter "done_ratio", :type => :integer

223

219

224

if User.current.allowed_to?(:set_issues_private, nil, :global => true) ||

220

if User.current.allowed_to?(:set_issues_private, nil, :global => true) ||

225

User.current.allowed_to?(:set_own_issues_private, nil, :global => true)

221

User.current.allowed_to?(:set_own_issues_private, nil, :global => true)

226

add_available_filter "is_private",

222

add_available_filter "is_private",

227

:type => :list,

223

:type => :list,

228

:values => [[l(:general_text_yes), "1"], [l(:general_text_no), "0"]]

224

:values => [[l(:general_text_yes), "1"], [l(:general_text_no), "0"]]

229

end

225

end

230

226

231

if User.current.logged?

227

if User.current.logged?

232

add_available_filter "watcher_id",

228

add_available_filter "watcher_id",

233

:type => :list, :values => [["<< #{l(:label_me)} >>", "me"]]

229

:type => :list, :values => [["<< #{l(:label_me)} >>", "me"]]

234

end

230

end

235

231

236

if subprojects.any?

232

if subprojects.any?

237

add_available_filter "subproject_id",

233

add_available_filter "subproject_id",

238

:type => :list_subprojects,

234

:type => :list_subprojects,

239

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

235

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

240

end

236

end

241

237

242

add_custom_fields_filters(issue_custom_fields)

238

add_custom_fields_filters(issue_custom_fields)

243

239

244

add_associations_custom_fields_filters :project, :author, :assigned_to, :fixed_version

240

add_associations_custom_fields_filters :project, :author, :assigned_to, :fixed_version

245

241

246

IssueRelation::TYPES.each do |relation_type, options|

242

IssueRelation::TYPES.each do |relation_type, options|

247

add_available_filter relation_type, :type => :relation, :label => options[:name]

243

add_available_filter relation_type, :type => :relation, :label => options[:name]

248

end

244

end

249

add_available_filter "parent_id", :type => :tree, :label => :field_parent_issue

245

add_available_filter "parent_id", :type => :tree, :label => :field_parent_issue

250

add_available_filter "child_id", :type => :tree, :label => :label_subtask_plural

246

add_available_filter "child_id", :type => :tree, :label => :label_subtask_plural

251

247

252

Tracker.disabled_core_fields(trackers).each {|field|

248

Tracker.disabled_core_fields(trackers).each {|field|

253

delete_available_filter field

249

delete_available_filter field

254

}

250

}

255

end

251

end

256

252

257

def available_columns

253

def available_columns

258

return @available_columns if @available_columns

254

return @available_columns if @available_columns

259

@available_columns = self.class.available_columns.dup

255

@available_columns = self.class.available_columns.dup

260

@available_columns += (project ?

256

@available_columns += (project ?

261

project.all_issue_custom_fields :

257

project.all_issue_custom_fields :

262

IssueCustomField

258

IssueCustomField

263

).visible.collect {|cf| QueryCustomFieldColumn.new(cf) }

259

).visible.collect {|cf| QueryCustomFieldColumn.new(cf) }

264

260

265

if User.current.allowed_to?(:view_time_entries, project, :global => true)

261

if User.current.allowed_to?(:view_time_entries, project, :global => true)

266

index = @available_columns.find_index {|column| column.name == :total_estimated_hours}

262

index = @available_columns.find_index {|column| column.name == :total_estimated_hours}

267

index = (index ? index + 1 : -1)

263

index = (index ? index + 1 : -1)

268

# insert the column after total_estimated_hours or at the end

264

# insert the column after total_estimated_hours or at the end

269

@available_columns.insert index, QueryColumn.new(:spent_hours,

265

@available_columns.insert index, QueryColumn.new(:spent_hours,

270

:sortable => "COALESCE((SELECT SUM(hours) FROM #{TimeEntry.table_name} WHERE #{TimeEntry.table_name}.issue_id = #{Issue.table_name}.id), 0)",

266

:sortable => "COALESCE((SELECT SUM(hours) FROM #{TimeEntry.table_name} WHERE #{TimeEntry.table_name}.issue_id = #{Issue.table_name}.id), 0)",

271

:default_order => 'desc',

267

:default_order => 'desc',

272

:caption => :label_spent_time,

268

:caption => :label_spent_time,

273

:totalable => true

269

:totalable => true

274

)

270

)

275

@available_columns.insert index+1, QueryColumn.new(:total_spent_hours,

271

@available_columns.insert index+1, QueryColumn.new(:total_spent_hours,

276

:sortable => "COALESCE((SELECT SUM(hours) FROM #{TimeEntry.table_name} JOIN #{Issue.table_name} subtasks ON subtasks.id = #{TimeEntry.table_name}.issue_id" +

272

:sortable => "COALESCE((SELECT SUM(hours) FROM #{TimeEntry.table_name} JOIN #{Issue.table_name} subtasks ON subtasks.id = #{TimeEntry.table_name}.issue_id" +

277

" WHERE subtasks.root_id = #{Issue.table_name}.root_id AND subtasks.lft >= #{Issue.table_name}.lft AND subtasks.rgt <= #{Issue.table_name}.rgt), 0)",

273

" WHERE subtasks.root_id = #{Issue.table_name}.root_id AND subtasks.lft >= #{Issue.table_name}.lft AND subtasks.rgt <= #{Issue.table_name}.rgt), 0)",

278

:default_order => 'desc',

274

:default_order => 'desc',

279

:caption => :label_total_spent_time

275

:caption => :label_total_spent_time

280

)

276

)

281

end

277

end

282

278

283

if User.current.allowed_to?(:set_issues_private, nil, :global => true) ||

279

if User.current.allowed_to?(:set_issues_private, nil, :global => true) ||

284

User.current.allowed_to?(:set_own_issues_private, nil, :global => true)

280

User.current.allowed_to?(:set_own_issues_private, nil, :global => true)

285

@available_columns << QueryColumn.new(:is_private, :sortable => "#{Issue.table_name}.is_private")

281

@available_columns << QueryColumn.new(:is_private, :sortable => "#{Issue.table_name}.is_private")

286

end

282

end

287

283

288

disabled_fields = Tracker.disabled_core_fields(trackers).map {|field| field.sub(/_id$/, '')}

284

disabled_fields = Tracker.disabled_core_fields(trackers).map {|field| field.sub(/_id$/, '')}

289

@available_columns.reject! {|column|

285

@available_columns.reject! {|column|

290

disabled_fields.include?(column.name.to_s)

286

disabled_fields.include?(column.name.to_s)

291

}

287

}

292

288

293

@available_columns

289

@available_columns

294

end

290

end

295

291

296

def default_columns_names

292

def default_columns_names

297

@default_columns_names ||= begin

293

@default_columns_names ||= begin

298

default_columns = Setting.issue_list_default_columns.map(&:to_sym)

294

default_columns = Setting.issue_list_default_columns.map(&:to_sym)

299

295

300

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

296

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

301

end

297

end

302

end

298

end

303

299

304

def base_scope

300

def base_scope

305

Issue.visible.joins(:status, :project).where(statement)

301

Issue.visible.joins(:status, :project).where(statement)

306

end

302

end

307

303

308

# Returns the issue count

304

# Returns the issue count

309

def issue_count

305

def issue_count

310

base_scope.count

306

base_scope.count

311

rescue ::ActiveRecord::StatementInvalid => e

307

rescue ::ActiveRecord::StatementInvalid => e

312

raise StatementInvalid.new(e.message)

308

raise StatementInvalid.new(e.message)

313

end

309

end

314

310

315

# Returns the issue count by group or nil if query is not grouped

311

# Returns the issue count by group or nil if query is not grouped

316

def issue_count_by_group

312

def issue_count_by_group

317

grouped_query do |scope|

313

grouped_query do |scope|

318

scope.count

314

scope.count

319

end

315

end

320

end

316

end

321

317

322

# Returns sum of all the issue's estimated_hours

318

# Returns sum of all the issue's estimated_hours

323

def total_for_estimated_hours(scope)

319

def total_for_estimated_hours(scope)

324

map_total(scope.sum(:estimated_hours)) {|t| t.to_f.round(2)}

320

map_total(scope.sum(:estimated_hours)) {|t| t.to_f.round(2)}

325

end

321

end

326

322

327

# Returns sum of all the issue's time entries hours

323

# Returns sum of all the issue's time entries hours

328

def total_for_spent_hours(scope)

324

def total_for_spent_hours(scope)

329

total = if group_by_column.try(:name) == :project

325

total = if group_by_column.try(:name) == :project

330

# TODO: remove this when https://github.com/rails/rails/issues/21922 is fixed

326

# TODO: remove this when https://github.com/rails/rails/issues/21922 is fixed

331

# We have to do a custom join without the time_entries.project_id column

327

# We have to do a custom join without the time_entries.project_id column

332

# that would trigger a ambiguous column name error

328

# that would trigger a ambiguous column name error

333

scope.joins("JOIN (SELECT issue_id, hours FROM #{TimeEntry.table_name}) AS joined_time_entries ON joined_time_entries.issue_id = #{Issue.table_name}.id").

329

scope.joins("JOIN (SELECT issue_id, hours FROM #{TimeEntry.table_name}) AS joined_time_entries ON joined_time_entries.issue_id = #{Issue.table_name}.id").

334

sum("joined_time_entries.hours")

330

sum("joined_time_entries.hours")

335

else

331

else

336

scope.joins(:time_entries).sum("#{TimeEntry.table_name}.hours")

332

scope.joins(:time_entries).sum("#{TimeEntry.table_name}.hours")

337

end

333

end

338

map_total(total) {|t| t.to_f.round(2)}

334

map_total(total) {|t| t.to_f.round(2)}

339

end

335

end

340

336

341

# Returns the issues

337

# Returns the issues

342

# Valid options are :order, :offset, :limit, :include, :conditions

338

# Valid options are :order, :offset, :limit, :include, :conditions

343

def issues(options={})

339

def issues(options={})

344

order_option = [group_by_sort_order, options[:order]].flatten.reject(&:blank?)

340

order_option = [group_by_sort_order, options[:order]].flatten.reject(&:blank?)

345

341

346

scope = Issue.visible.

342

scope = Issue.visible.

347

joins(:status, :project).

343

joins(:status, :project).

348

where(statement).

344

where(statement).

349

includes(([:status, :project] + (options[:include] || [])).uniq).

345

includes(([:status, :project] + (options[:include] || [])).uniq).

350

where(options[:conditions]).

346

where(options[:conditions]).

351

order(order_option).

347

order(order_option).

352

joins(joins_for_order_statement(order_option.join(','))).

348

joins(joins_for_order_statement(order_option.join(','))).

353

limit(options[:limit]).

349

limit(options[:limit]).

354

offset(options[:offset])

350

offset(options[:offset])

355

351

356

scope = scope.preload(:custom_values)

352

scope = scope.preload(:custom_values)

357

if has_column?(:author)

353

if has_column?(:author)

358

scope = scope.preload(:author)

354

scope = scope.preload(:author)

359

end

355

end

360

356

361

issues = scope.to_a

357

issues = scope.to_a

362

358

363

if has_column?(:spent_hours)

359

if has_column?(:spent_hours)

364

Issue.load_visible_spent_hours(issues)

360

Issue.load_visible_spent_hours(issues)

365

end

361

end

366

if has_column?(:total_spent_hours)

362

if has_column?(:total_spent_hours)

367

Issue.load_visible_total_spent_hours(issues)

363

Issue.load_visible_total_spent_hours(issues)

368

end

364

end

369

if has_column?(:relations)

365

if has_column?(:relations)

370

Issue.load_visible_relations(issues)

366

Issue.load_visible_relations(issues)

371

end

367

end

372

issues

368

issues

373

rescue ::ActiveRecord::StatementInvalid => e

369

rescue ::ActiveRecord::StatementInvalid => e

374

raise StatementInvalid.new(e.message)

370

raise StatementInvalid.new(e.message)

375

end

371

end

376

372

377

# Returns the issues ids

373

# Returns the issues ids

378

def issue_ids(options={})

374

def issue_ids(options={})

379

order_option = [group_by_sort_order, options[:order]].flatten.reject(&:blank?)

375

order_option = [group_by_sort_order, options[:order]].flatten.reject(&:blank?)

380

376

381

Issue.visible.

377

Issue.visible.

382

joins(:status, :project).

378

joins(:status, :project).

383

where(statement).

379

where(statement).

384

includes(([:status, :project] + (options[:include] || [])).uniq).

380

includes(([:status, :project] + (options[:include] || [])).uniq).

385

references(([:status, :project] + (options[:include] || [])).uniq).

381

references(([:status, :project] + (options[:include] || [])).uniq).

386

where(options[:conditions]).

382

where(options[:conditions]).

387

order(order_option).

383

order(order_option).

388

joins(joins_for_order_statement(order_option.join(','))).

384

joins(joins_for_order_statement(order_option.join(','))).

389

limit(options[:limit]).

385

limit(options[:limit]).

390

offset(options[:offset]).

386

offset(options[:offset]).

391

pluck(:id)

387

pluck(:id)

392

rescue ::ActiveRecord::StatementInvalid => e

388

rescue ::ActiveRecord::StatementInvalid => e

393

raise StatementInvalid.new(e.message)

389

raise StatementInvalid.new(e.message)

394

end

390

end

395

391

396

# Returns the journals

392

# Returns the journals

397

# Valid options are :order, :offset, :limit

393

# Valid options are :order, :offset, :limit

398

def journals(options={})

394

def journals(options={})

399

Journal.visible.

395

Journal.visible.

400

joins(:issue => [:project, :status]).

396

joins(:issue => [:project, :status]).

401

where(statement).

397

where(statement).

402

order(options[:order]).

398

order(options[:order]).

403

limit(options[:limit]).

399

limit(options[:limit]).

404

offset(options[:offset]).

400

offset(options[:offset]).

405

preload(:details, :user, {:issue => [:project, :author, :tracker, :status]}).

401

preload(:details, :user, {:issue => [:project, :author, :tracker, :status]}).

406

to_a

402

to_a

407

rescue ::ActiveRecord::StatementInvalid => e

403

rescue ::ActiveRecord::StatementInvalid => e

408

raise StatementInvalid.new(e.message)

404

raise StatementInvalid.new(e.message)

409

end

405

end

410

406

411

# Returns the versions

407

# Returns the versions

412

# Valid options are :conditions

408

# Valid options are :conditions

413

def versions(options={})

409

def versions(options={})

414

Version.visible.

410

Version.visible.

415

where(project_statement).

411

where(project_statement).

416

where(options[:conditions]).

412

where(options[:conditions]).

417

includes(:project).

413

includes(:project).

418

references(:project).

414

references(:project).

419

to_a

415

to_a

420

rescue ::ActiveRecord::StatementInvalid => e

416

rescue ::ActiveRecord::StatementInvalid => e

421

raise StatementInvalid.new(e.message)

417

raise StatementInvalid.new(e.message)

422

end

418

end

423

419

424

def sql_for_watcher_id_field(field, operator, value)

420

def sql_for_watcher_id_field(field, operator, value)

425

db_table = Watcher.table_name

421

db_table = Watcher.table_name

426

"#{Issue.table_name}.id #{ operator == '=' ? 'IN' : 'NOT IN' } (SELECT #{db_table}.watchable_id FROM #{db_table} WHERE #{db_table}.watchable_type='Issue' AND " +

422

"#{Issue.table_name}.id #{ operator == '=' ? 'IN' : 'NOT IN' } (SELECT #{db_table}.watchable_id FROM #{db_table} WHERE #{db_table}.watchable_type='Issue' AND " +

427

sql_for_field(field, '=', value, db_table, 'user_id') + ')'

423

sql_for_field(field, '=', value, db_table, 'user_id') + ')'

428

end

424

end

429

425

430

def sql_for_member_of_group_field(field, operator, value)

426

def sql_for_member_of_group_field(field, operator, value)

431

if operator == '*' # Any group

427

if operator == '*' # Any group

432

groups = Group.givable

428

groups = Group.givable

433

operator = '=' # Override the operator since we want to find by assigned_to

429

operator = '=' # Override the operator since we want to find by assigned_to

434

elsif operator == "!*"

430

elsif operator == "!*"

435

groups = Group.givable

431

groups = Group.givable

436

operator = '!' # Override the operator since we want to find by assigned_to

432

operator = '!' # Override the operator since we want to find by assigned_to

437

else

433

else

438

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

434

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

439

end

435

end

440

groups ||= []

436

groups ||= []

441

437

442

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

438

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

443

user_ids + group.user_ids + [group.id]

439

user_ids + group.user_ids + [group.id]

444

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

440

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

445

441

446

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

442

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

447

end

443

end

448

444

449

def sql_for_assigned_to_role_field(field, operator, value)

445

def sql_for_assigned_to_role_field(field, operator, value)

450

case operator

446

case operator

451

when "*", "!*" # Member / Not member

447

when "*", "!*" # Member / Not member

452

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

448

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

453

nl = operator == "!*" ? "#{Issue.table_name}.assigned_to_id IS NULL OR" : ''

449

nl = operator == "!*" ? "#{Issue.table_name}.assigned_to_id IS NULL OR" : ''

454

"(#{nl} #{Issue.table_name}.assigned_to_id #{sw} IN (SELECT DISTINCT #{Member.table_name}.user_id FROM #{Member.table_name}" +

450

"(#{nl} #{Issue.table_name}.assigned_to_id #{sw} IN (SELECT DISTINCT #{Member.table_name}.user_id FROM #{Member.table_name}" +

455

" WHERE #{Member.table_name}.project_id = #{Issue.table_name}.project_id))"

451

" WHERE #{Member.table_name}.project_id = #{Issue.table_name}.project_id))"

456

when "=", "!"

452

when "=", "!"

457

role_cond = value.any? ?

453

role_cond = value.any? ?

458

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

454

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

459

"1=0"

455

"1=0"

460

456

461

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

457

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

462

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

458

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

463

"(#{nl} #{Issue.table_name}.assigned_to_id #{sw} IN (SELECT DISTINCT #{Member.table_name}.user_id FROM #{Member.table_name}, #{MemberRole.table_name}" +

459

"(#{nl} #{Issue.table_name}.assigned_to_id #{sw} IN (SELECT DISTINCT #{Member.table_name}.user_id FROM #{Member.table_name}, #{MemberRole.table_name}" +

464

" WHERE #{Member.table_name}.project_id = #{Issue.table_name}.project_id AND #{Member.table_name}.id = #{MemberRole.table_name}.member_id AND #{role_cond}))"

460

" WHERE #{Member.table_name}.project_id = #{Issue.table_name}.project_id AND #{Member.table_name}.id = #{MemberRole.table_name}.member_id AND #{role_cond}))"

465

end

461

end

466

end

462

end

467

463

468

def sql_for_is_private_field(field, operator, value)

464

def sql_for_is_private_field(field, operator, value)

469

op = (operator == "=" ? 'IN' : 'NOT IN')

465

op = (operator == "=" ? 'IN' : 'NOT IN')

470

va = value.map {|v| v == '0' ? self.class.connection.quoted_false : self.class.connection.quoted_true}.uniq.join(',')

466

va = value.map {|v| v == '0' ? self.class.connection.quoted_false : self.class.connection.quoted_true}.uniq.join(',')

471

467

472

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

468

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

473

end

469

end

474

470

475

def sql_for_parent_id_field(field, operator, value)

471

def sql_for_parent_id_field(field, operator, value)

476

case operator

472

case operator

477

when "="

473

when "="

478

"#{Issue.table_name}.parent_id = #{value.first.to_i}"

474

"#{Issue.table_name}.parent_id = #{value.first.to_i}"

479

when "~"

475

when "~"

480

root_id, lft, rgt = Issue.where(:id => value.first.to_i).pluck(:root_id, :lft, :rgt).first

476

root_id, lft, rgt = Issue.where(:id => value.first.to_i).pluck(:root_id, :lft, :rgt).first

481

if root_id && lft && rgt

477

if root_id && lft && rgt

482

"#{Issue.table_name}.root_id = #{root_id} AND #{Issue.table_name}.lft > #{lft} AND #{Issue.table_name}.rgt < #{rgt}"

478

"#{Issue.table_name}.root_id = #{root_id} AND #{Issue.table_name}.lft > #{lft} AND #{Issue.table_name}.rgt < #{rgt}"

483

else

479

else

484

"1=0"

480

"1=0"

485

end

481

end

486

when "!*"

482

when "!*"

487

"#{Issue.table_name}.parent_id IS NULL"

483

"#{Issue.table_name}.parent_id IS NULL"

488

when "*"

484

when "*"

489

"#{Issue.table_name}.parent_id IS NOT NULL"

485

"#{Issue.table_name}.parent_id IS NOT NULL"

490

end

486

end

491

end

487

end

492

488

493

def sql_for_child_id_field(field, operator, value)

489

def sql_for_child_id_field(field, operator, value)

494

case operator

490

case operator

495

when "="

491

when "="

496

parent_id = Issue.where(:id => value.first.to_i).pluck(:parent_id).first

492

parent_id = Issue.where(:id => value.first.to_i).pluck(:parent_id).first

497

if parent_id

493

if parent_id

498

"#{Issue.table_name}.id = #{parent_id}"

494

"#{Issue.table_name}.id = #{parent_id}"

499

else

495

else

500

"1=0"

496

"1=0"

501

end

497

end

502

when "~"

498

when "~"

503

root_id, lft, rgt = Issue.where(:id => value.first.to_i).pluck(:root_id, :lft, :rgt).first

499

root_id, lft, rgt = Issue.where(:id => value.first.to_i).pluck(:root_id, :lft, :rgt).first

504

if root_id && lft && rgt

500

if root_id && lft && rgt

505

"#{Issue.table_name}.root_id = #{root_id} AND #{Issue.table_name}.lft < #{lft} AND #{Issue.table_name}.rgt > #{rgt}"

501

"#{Issue.table_name}.root_id = #{root_id} AND #{Issue.table_name}.lft < #{lft} AND #{Issue.table_name}.rgt > #{rgt}"

506

else

502

else

507

"1=0"

503

"1=0"

508

end

504

end

509

when "!*"

505

when "!*"

510

"#{Issue.table_name}.rgt - #{Issue.table_name}.lft = 1"

506

"#{Issue.table_name}.rgt - #{Issue.table_name}.lft = 1"

511

when "*"

507

when "*"

512

"#{Issue.table_name}.rgt - #{Issue.table_name}.lft > 1"

508

"#{Issue.table_name}.rgt - #{Issue.table_name}.lft > 1"

513

end

509

end

514

end

510

end

515

511

516

def sql_for_relations(field, operator, value, options={})

512

def sql_for_relations(field, operator, value, options={})

517

relation_options = IssueRelation::TYPES[field]

513

relation_options = IssueRelation::TYPES[field]

518

return relation_options unless relation_options

514

return relation_options unless relation_options

519

515

520

relation_type = field

516

relation_type = field

521

join_column, target_join_column = "issue_from_id", "issue_to_id"

517

join_column, target_join_column = "issue_from_id", "issue_to_id"

522

if relation_options[:reverse] || options[:reverse]

518

if relation_options[:reverse] || options[:reverse]

523

relation_type = relation_options[:reverse] || relation_type

519

relation_type = relation_options[:reverse] || relation_type

524

join_column, target_join_column = target_join_column, join_column

520

join_column, target_join_column = target_join_column, join_column

525

end

521

end

526

522

527

sql = case operator

523

sql = case operator

528

when "*", "!*"

524

when "*", "!*"

529

op = (operator == "*" ? 'IN' : 'NOT IN')

525

op = (operator == "*" ? 'IN' : 'NOT IN')

530

"#{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)}')"

526

"#{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)}')"

531

when "=", "!"

527

when "=", "!"

532

op = (operator == "=" ? 'IN' : 'NOT IN')

528

op = (operator == "=" ? 'IN' : 'NOT IN')

533

"#{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})"

529

"#{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})"

534

when "=p", "=!p", "!p"

530

when "=p", "=!p", "!p"

535

op = (operator == "!p" ? 'NOT IN' : 'IN')

531

op = (operator == "!p" ? 'NOT IN' : 'IN')

536

comp = (operator == "=!p" ? '<>' : '=')

532

comp = (operator == "=!p" ? '<>' : '=')

537

"#{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})"

533

"#{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})"

538

when "*o", "!o"

534

when "*o", "!o"

539

op = (operator == "!o" ? 'NOT IN' : 'IN')

535

op = (operator == "!o" ? 'NOT IN' : 'IN')

540

"#{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}))"

536

"#{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}))"

541

end

537

end

542

538

543

if relation_options[:sym] == field && !options[:reverse]

539

if relation_options[:sym] == field && !options[:reverse]

544

sqls = [sql, sql_for_relations(field, operator, value, :reverse => true)]

540

sqls = [sql, sql_for_relations(field, operator, value, :reverse => true)]

545

sql = sqls.join(["!", "!*", "!p"].include?(operator) ? " AND " : " OR ")

541

sql = sqls.join(["!", "!*", "!p"].include?(operator) ? " AND " : " OR ")

546

end

542

end

547

"(#{sql})"

543

"(#{sql})"

548

end

544

end

549

545

550

def find_assigned_to_id_filter_values(values)

546

def find_assigned_to_id_filter_values(values)

551

Principal.visible.where(:id => values).map {|p| [p.name, p.id.to_s]}

547

Principal.visible.where(:id => values).map {|p| [p.name, p.id.to_s]}

552

end

548

end

553

alias :find_author_id_filter_values :find_assigned_to_id_filter_values

549

alias :find_author_id_filter_values :find_assigned_to_id_filter_values

554

550

555

IssueRelation::TYPES.keys.each do |relation_type|

551

IssueRelation::TYPES.keys.each do |relation_type|

556

alias_method "sql_for_#{relation_type}_field".to_sym, :sql_for_relations

552

alias_method "sql_for_#{relation_type}_field".to_sym, :sql_for_relations

557

end

553

end

558

end

554

end

	Site-wide shortcuts
/	Use quick search box
g h	Goto home page
g g	Goto my private gists page
g G	Goto my public gists page
g 0-9	Goto bookmarked items from 0-9
n r	New repository page
n g	New gist page

	Repositories
g s	Goto summary page
g c	Goto changelog page
g f	Goto files page
g F	Goto files page with file search activated
g p	Goto pull requests page
g o	Goto repository settings
g O	Goto repository access permissions settings
t s	Toggle sidebar on some pages

             # 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.
             class IssueQuery < Query
               self.queried_class = Issue
               self.available_columns = [
                 QueryColumn.new(:id, :sortable => "#{Issue.table_name}.id", :default_order => 'desc', :caption => '#', :frozen => true),
                 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),
                 QueryColumn.new(:updated_on, :sortable => "#{Issue.table_name}.updated_on", :default_order => 'desc'),
                 QueryColumn.new(:category, :sortable => "#{IssueCategory.table_name}.name", :groupable => true),
                 QueryColumn.new(:fixed_version, :sortable => lambda {Version.fields_for_order_statement}, :groupable => true),
                 QueryColumn.new(:start_date, :sortable => "#{Issue.table_name}.start_date"),
                 QueryColumn.new(:due_date, :sortable => "#{Issue.table_name}.due_date"),
                 QueryColumn.new(:estimated_hours, :sortable => "#{Issue.table_name}.estimated_hours", :totalable => true),
                 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'),
                 QueryColumn.new(:done_ratio, :sortable => "#{Issue.table_name}.done_ratio", :groupable => true),
                 QueryColumn.new(:created_on, :sortable => "#{Issue.table_name}.created_on", :default_order => 'desc'),
                 QueryColumn.new(:closed_on, :sortable => "#{Issue.table_name}.closed_on", :default_order => 'desc'),
                 QueryColumn.new(:relations, :caption => :label_related_issues),
                 QueryColumn.new(:description, :inline => false)
               ]
               scope :visible, lambda {|*args|
                 user = args.shift || User.current
                 base = Project.allowed_to_condition(user, :view_issues, *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 = ?" +
                       " WHERE q.project_id IS NULL OR q.project_id = m.project_id))" +
                     " OR #{table_name}.user_id = ?",
                     VISIBILITY_PUBLIC, VISIBILITY_ROLES, user.id, user.id)
                 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
               }
               def initialize(attributes=nil, *args)
                 super attributes
                 self.filters ||= { 'status_id' => {:operator => "o", :values => [""]} }
               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?(:view_issues, project)
                 case visibility
                 when VISIBILITY_PUBLIC
                   true
                 when VISIBILITY_ROLES
                   if project
                     (user.roles_for_project(project) & roles).any?
                   else
                     Member.where(:user_id => user.id).joins(:roles).where(:member_roles => {:role_id => roles.map(&:id)}).any?
                   end
                 else
                   user == self.user
                 end
               end
               def is_private?
                 visibility == VISIBILITY_PRIVATE
               end
               def is_public?
                 !is_private?
               end
               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
               def build_from_params(params)
                 super
                 self.draw_relations = params[:draw_relations] || (params[:query] && params[:query][:draw_relations])
                 self.draw_progress_line = params[:draw_progress_line] || (params[:query] && params[:query][:draw_progress_line])
                 self
               end
               def initialize_available_filters
                 principals = []
                 subprojects = []
                 versions = []
                 categories = []
                 issue_custom_fields = []
                 if project
                   principals += project.principals.visible
                   unless project.leaf?
                     subprojects = project.descendants.visible.to_a
                     principals += Principal.member_of(subprojects).visible
                   end
                   versions = project.shared_versions.to_a
                   categories = project.issue_categories.to_a
                   issue_custom_fields = project.all_issue_custom_fields
                 else
                   if all_projects.any?
                     principals += Principal.member_of(all_projects).visible
                   end
                   versions = Version.visible.where(:sharing => 'system').to_a
                   issue_custom_fields = IssueCustomField.where(:is_for_all => true)
                 end
                 principals.uniq!
                 principals.sort!
                 principals.reject! {|p| p.is_a?(GroupBuiltin)}
                 users = principals.select {|p| p.is_a?(User)}
                 add_available_filter "status_id",
                   :type => :list_status, :values => IssueStatus.sorted.collect{|s| [s.name, s.id.to_s] }
                 if project.nil?
                   project_values = []
                   if User.current.logged? && User.current.memberships.any?
                     project_values << ["<< #{l(:label_my_projects).downcase} >>", "mine"]
                   end
                   project_values += all_projects_values
                   add_available_filter("project_id",
                     :type => :list, :values => project_values
                   ) unless project_values.empty?
                 end
                 add_available_filter "tracker_id",
                   :type => :list, :values => trackers.collect{|s| [s.name, s.id.to_s] }
                 add_available_filter "priority_id",
                   :type => :list, :values => IssuePriority.all.collect{|s| [s.name, s.id.to_s] }
                 author_values = []
                 author_values << ["<< #{l(:label_me)} >>", "me"] if User.current.logged?
                 author_values += users.collect{|s| [s.name, s.id.to_s] }
                 add_available_filter("author_id",
                   :type => :list, :values => author_values
                 ) unless author_values.empty?
                 assigned_to_values = []
                 assigned_to_values << ["<< #{l(:label_me)} >>", "me"] if User.current.logged?
                 assigned_to_values += (Setting.issue_group_assignment? ?
                                           principals : users).collect{|s| [s.name, s.id.to_s] }
                 add_available_filter("assigned_to_id",
                   :type => :list_optional, :values => assigned_to_values
                 ) unless assigned_to_values.empty?
                 group_values = Group.givable.visible.collect {|g| [g.name, g.id.to_s] }
                 add_available_filter("member_of_group",
                   :type => :list_optional, :values => group_values
                 ) unless group_values.empty?
                 role_values = Role.givable.collect {|r| [r.name, r.id.to_s] }
                 add_available_filter("assigned_to_role",
                   :type => :list_optional, :values => role_values
                 ) unless role_values.empty?
-                if versions.any?
+                add_available_filter "fixed_version_id",
-                  add_available_filter "fixed_version_id",
+                  :type => :list_optional,
-                    :type => :list_optional,
+                  :values => versions.sort.collect{|s| ["#{s.project.name} - #{s.name}", s.id.to_s] }
-                    :values => versions.sort.collect{|s| ["#{s.project.name} - #{s.name}", s.id.to_s] }
-                end
-                if categories.any?
+                add_available_filter "category_id",
-                  add_available_filter "category_id",
+                  :type => :list_optional,
-                    :type => :list_optional,
+                  :values => categories.collect{|s| [s.name, s.id.to_s] }
-                    :values => categories.collect{|s| [s.name, s.id.to_s] }
-                end
                 add_available_filter "subject", :type => :text
                 add_available_filter "description", :type => :text
                 add_available_filter "created_on", :type => :date_past
                 add_available_filter "updated_on", :type => :date_past
                 add_available_filter "closed_on", :type => :date_past
                 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
                 if User.current.allowed_to?(:set_issues_private, nil, :global => true) ||
                   User.current.allowed_to?(:set_own_issues_private, nil, :global => true)
                   add_available_filter "is_private",
                     :type => :list,
                     :values => [[l(:general_text_yes), "1"], [l(:general_text_no), "0"]]
                 end
                 if User.current.logged?
                   add_available_filter "watcher_id",
                     :type => :list, :values => [["<< #{l(:label_me)} >>", "me"]]
                 end
                 if subprojects.any?
                   add_available_filter "subproject_id",
                     :type => :list_subprojects,
                     :values => subprojects.collect{|s| [s.name, s.id.to_s] }
                 end
                 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|
                   add_available_filter relation_type, :type => :relation, :label => options[:name]
                 end
                 add_available_filter "parent_id", :type => :tree, :label => :field_parent_issue
                 add_available_filter "child_id", :type => :tree, :label => :label_subtask_plural
                 Tracker.disabled_core_fields(trackers).each {|field|
                   delete_available_filter field
                 }
               end
               def available_columns
                 return @available_columns if @available_columns
                 @available_columns = self.class.available_columns.dup
                 @available_columns += (project ?
                                         project.all_issue_custom_fields :
                                         IssueCustomField
                                        ).visible.collect {|cf| QueryCustomFieldColumn.new(cf) }
                 if User.current.allowed_to?(:view_time_entries, project, :global => true)
                   index = @available_columns.find_index {|column| column.name == :total_estimated_hours}
                   index = (index ? index + 1 : -1)
                   # insert the column after total_estimated_hours or at the end
                   @available_columns.insert index, QueryColumn.new(:spent_hours,
                     :sortable => "COALESCE((SELECT SUM(hours) FROM #{TimeEntry.table_name} WHERE #{TimeEntry.table_name}.issue_id = #{Issue.table_name}.id), 0)",
                     :default_order => 'desc',
                     :caption => :label_spent_time,
                     :totalable => true
                   )
                   @available_columns.insert index+1, QueryColumn.new(:total_spent_hours,
                     :sortable => "COALESCE((SELECT SUM(hours) FROM #{TimeEntry.table_name} JOIN #{Issue.table_name} subtasks ON subtasks.id = #{TimeEntry.table_name}.issue_id" +
                       " 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',
                     :caption => :label_total_spent_time
                   )
                 end
                 if User.current.allowed_to?(:set_issues_private, nil, :global => true) ||
                   User.current.allowed_to?(:set_own_issues_private, nil, :global => true)
                   @available_columns << QueryColumn.new(:is_private, :sortable => "#{Issue.table_name}.is_private")
                 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
               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
               def base_scope
                 Issue.visible.joins(:status, :project).where(statement)
               end
               # Returns the issue count
               def issue_count
                 base_scope.count
               rescue ::ActiveRecord::StatementInvalid => e
                 raise StatementInvalid.new(e.message)
               end
               # Returns the issue count by group or nil if query is not grouped
               def issue_count_by_group
                 grouped_query do |scope|
                   scope.count
                 end
               end
               # Returns sum of all the issue's estimated_hours
               def total_for_estimated_hours(scope)
                 map_total(scope.sum(:estimated_hours)) {|t| t.to_f.round(2)}
               end
               # Returns sum of all the issue's time entries hours
               def total_for_spent_hours(scope)
                 total = if group_by_column.try(:name) == :project
                   # TODO: remove this when https://github.com/rails/rails/issues/21922 is fixed
                   # We have to do a custom join without the time_entries.project_id column
                   # that would trigger a ambiguous column name error
                   scope.joins("JOIN (SELECT issue_id, hours FROM #{TimeEntry.table_name}) AS joined_time_entries ON joined_time_entries.issue_id = #{Issue.table_name}.id").
                     sum("joined_time_entries.hours")
                 else
                   scope.joins(:time_entries).sum("#{TimeEntry.table_name}.hours")
                 end
                 map_total(total) {|t| t.to_f.round(2)}
               end
               # Returns the issues
               # Valid options are :order, :offset, :limit, :include, :conditions
               def issues(options={})
                 order_option = [group_by_sort_order, options[:order]].flatten.reject(&:blank?)
                 scope = Issue.visible.
                   joins(:status, :project).
                   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]).
                   offset(options[:offset])
                 scope = scope.preload(:custom_values)
                 if has_column?(:author)
                   scope = scope.preload(:author)
                 end
                 issues = scope.to_a
                 if has_column?(:spent_hours)
                   Issue.load_visible_spent_hours(issues)
                 end
                 if has_column?(:total_spent_hours)
                   Issue.load_visible_total_spent_hours(issues)
                 end
                 if has_column?(:relations)
                   Issue.load_visible_relations(issues)
                 end
                 issues
               rescue ::ActiveRecord::StatementInvalid => e
                 raise StatementInvalid.new(e.message)
               end
               # Returns the issues ids
               def issue_ids(options={})
                 order_option = [group_by_sort_order, options[:order]].flatten.reject(&:blank?)
                 Issue.visible.
                   joins(:status, :project).
                   where(statement).
                   includes(([:status, :project] + (options[:include] || [])).uniq).
                   references(([:status, :project] + (options[:include] || [])).uniq).
                   where(options[:conditions]).
                   order(order_option).
                   joins(joins_for_order_statement(order_option.join(','))).
                   limit(options[:limit]).
                   offset(options[:offset]).
                   pluck(:id)
               rescue ::ActiveRecord::StatementInvalid => e
                 raise StatementInvalid.new(e.message)
               end
               # Returns the journals
               # Valid options are :order, :offset, :limit
               def journals(options={})
                 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]}).
                   to_a
               rescue ::ActiveRecord::StatementInvalid => e
                 raise StatementInvalid.new(e.message)
               end
               # Returns the versions
               # Valid options are :conditions
               def versions(options={})
                 Version.visible.
                   where(project_statement).
                   where(options[:conditions]).
                   includes(:project).
                   references(:project).
                   to_a
               rescue ::ActiveRecord::StatementInvalid => e
                 raise StatementInvalid.new(e.message)
               end
               def sql_for_watcher_id_field(field, operator, value)
                 db_table = Watcher.table_name
                 "#{Issue.table_name}.id #{ operator == '=' ? 'IN' : 'NOT IN' } (SELECT #{db_table}.watchable_id FROM #{db_table} WHERE #{db_table}.watchable_type='Issue' AND " +
                   sql_for_field(field, '=', value, db_table, 'user_id') + ')'
               end
               def sql_for_member_of_group_field(field, operator, value)
                 if operator == '*' # Any group
                   groups = Group.givable
                   operator = '=' # Override the operator since we want to find by assigned_to
                 elsif operator == "!*"
                   groups = Group.givable
                   operator = '!' # Override the operator since we want to find by assigned_to
                 else
                   groups = Group.where(:id => value).to_a
                 end
                 groups ||= []
                 members_of_groups = groups.inject([]) {|user_ids, group|
                   user_ids + group.user_ids + [group.id]
                 }.uniq.compact.sort.collect(&:to_s)
                 '(' + 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? ?
                     "#{MemberRole.table_name}.role_id IN (" + value.collect{|val| "'#{self.class.connection.quote_string(val)}'"}.join(",") + ")" :
                     "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
               def sql_for_is_private_field(field, operator, value)
                 op = (operator == "=" ? 'IN' : 'NOT IN')
                 va = value.map {|v| v == '0' ? self.class.connection.quoted_false : self.class.connection.quoted_true}.uniq.join(',')
                 "#{Issue.table_name}.is_private #{op} (#{va})"
               end
               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
               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')
                     "#{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)}')"
                   when "=", "!"
                     op = (operator == "=" ? 'IN' : 'NOT IN')
                     "#{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})"
                   when "=p", "=!p", "!p"
                     op = (operator == "!p" ? 'NOT IN' : 'IN')
                     comp = (operator == "=!p" ? '<>' : '=')
                     "#{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})"
                   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}))"
                   end
                 if relation_options[:sym] == field && !options[:reverse]
                   sqls = [sql, sql_for_relations(field, operator, value, :reverse => true)]
                   sql = sqls.join(["!", "!*", "!p"].include?(operator) ? " AND " : " OR ")
                 end
                 "(#{sql})"
               end
               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
               IssueRelation::TYPES.keys.each do |relation_type|
                 alias_method "sql_for_#{relation_type}_field".to_sym, :sql_for_relations
               end
             end