jro_apps/redmine Commit - r15866:1eee6b3a30df

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

self.view_permission = :view_issues

21

self.view_permission = :view_issues

22

23

self.available_columns = [

23

self.available_columns = [

24

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

24

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

25

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

25

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

26

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

26

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

27

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

27

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

28

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

28

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

29

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

29

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

30

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

30

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

31

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

31

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

32

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

32

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

33

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

33

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

34

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

34

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

35

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

35

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

36

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

36

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

37

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

37

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

38

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

38

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

39

QueryColumn.new(:total_estimated_hours,

39

QueryColumn.new(:total_estimated_hours,

40

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

40

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

41

" 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

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

42

:default_order => 'desc'),

42

:default_order => 'desc'),

43

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

43

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

44

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

44

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

45

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

45

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

46

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

46

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

47

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

47

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

48

]

48

]

49

50

def initialize(attributes=nil, *args)

50

def initialize(attributes=nil, *args)

51

super attributes

51

super attributes

52

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

52

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

53

end

53

end

54

55

def draw_relations

55

def draw_relations

56

r = options[:draw_relations]

56

r = options[:draw_relations]

57

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

57

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

58

end

58

end

59

60

def draw_relations=(arg)

60

def draw_relations=(arg)

61

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

61

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

62

end

62

end

63

64

def draw_progress_line

64

def draw_progress_line

65

r = options[:draw_progress_line]

65

r = options[:draw_progress_line]

66

r == '1'

66

r == '1'

67

end

67

end

68

69

def draw_progress_line=(arg)

69

def draw_progress_line=(arg)

70

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

70

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

71

end

71

end

72

73

def build_from_params(params)

73

def build_from_params(params)

74

super

74

super

75

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

75

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

76

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

76

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

77

self

77

self

78

end

78

end

79

80

def initialize_available_filters

80

def initialize_available_filters

81

add_available_filter "status_id",

81

add_available_filter "status_id",

82

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

82

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

83

84

add_available_filter("project_id",

84

add_available_filter("project_id",

85

:type => :list, :values => lambda { project_values }

85

:type => :list, :values => lambda { project_values }

86

) if project.nil?

86

) if project.nil?

87

88

add_available_filter "tracker_id",

88

add_available_filter "tracker_id",

89

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

89

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

90

91

add_available_filter "priority_id",

91

add_available_filter "priority_id",

92

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

92

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

93

94

add_available_filter("author_id",

94

add_available_filter("author_id",

95

:type => :list, :values => lambda { author_values }

95

:type => :list, :values => lambda { author_values }

96

)

96

)

97

98

add_available_filter("assigned_to_id",

98

add_available_filter("assigned_to_id",

99

:type => :list_optional, :values => lambda { assigned_to_values }

99

:type => :list_optional, :values => lambda { assigned_to_values }

100

)

100

)

101

102

add_available_filter("member_of_group",

102

add_available_filter("member_of_group",

103

:type => :list_optional, :values => lambda { Group.givable.visible.collect {|g| [g.name, g.id.to_s] } }

103

:type => :list_optional, :values => lambda { Group.givable.visible.collect {|g| [g.name, g.id.to_s] } }

104

)

104

)

105

106

add_available_filter("assigned_to_role",

106

add_available_filter("assigned_to_role",

107

:type => :list_optional, :values => lambda { Role.givable.collect {|r| [r.name, r.id.to_s] } }

107

:type => :list_optional, :values => lambda { Role.givable.collect {|r| [r.name, r.id.to_s] } }

108

)

108

)

109

110

add_available_filter "fixed_version_id",

110

add_available_filter "fixed_version_id",

111

:type => :list_optional, :values => lambda { fixed_version_values }

111

:type => :list_optional, :values => lambda { fixed_version_values }

112

113

add_available_filter "fixed_version.due_date",

113

add_available_filter "fixed_version.due_date",

114

:type => :date,

114

:type => :date,

115

:name => l(:label_attribute_of_fixed_version, :name => l(:field_effective_date))

115

:name => l(:label_attribute_of_fixed_version, :name => l(:field_effective_date))

116

117

add_available_filter "fixed_version.status",

117

add_available_filter "fixed_version.status",

118

:type => :list,

118

:type => :list,

119

:name => l(:label_attribute_of_fixed_version, :name => l(:field_status)),

119

:name => l(:label_attribute_of_fixed_version, :name => l(:field_status)),

120

:values => Version::VERSION_STATUSES.map{|s| [l("version_status_#{s}"), s] }

120

:values => Version::VERSION_STATUSES.map{|s| [l("version_status_#{s}"), s] }

121

122

add_available_filter "category_id",

122

add_available_filter "category_id",

123

:type => :list_optional,

123

:type => :list_optional,

124

:values => lambda { project.issue_categories.collect{|s| [s.name, s.id.to_s] } } if project

124

:values => lambda { project.issue_categories.collect{|s| [s.name, s.id.to_s] } } if project

125

126

add_available_filter "subject", :type => :text

126

add_available_filter "subject", :type => :text

127

add_available_filter "description", :type => :text

127

add_available_filter "description", :type => :text

128

add_available_filter "created_on", :type => :date_past

128

add_available_filter "created_on", :type => :date_past

129

add_available_filter "updated_on", :type => :date_past

129

add_available_filter "updated_on", :type => :date_past

130

add_available_filter "closed_on", :type => :date_past

130

add_available_filter "closed_on", :type => :date_past

131

add_available_filter "start_date", :type => :date

131

add_available_filter "start_date", :type => :date

132

add_available_filter "due_date", :type => :date

132

add_available_filter "due_date", :type => :date

133

add_available_filter "estimated_hours", :type => :float

133

add_available_filter "estimated_hours", :type => :float

134

add_available_filter "done_ratio", :type => :integer

134

add_available_filter "done_ratio", :type => :integer

135

136

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

136

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

137

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

137

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

138

add_available_filter "is_private",

138

add_available_filter "is_private",

139

:type => :list,

139

:type => :list,

140

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

140

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

141

end

141

end

142

143

if User.current.logged?

143

if User.current.logged?

144

add_available_filter "watcher_id",

144

add_available_filter "watcher_id",

145

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

145

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

146

end

146

end

147

148

add_available_filter("updated_by",

149

:type => :list, :values => lambda { author_values }

150

)

151

152

add_available_filter("last_updated_by",

153

:type => :list, :values => lambda { author_values }

154

)

155

148

if project && !project.leaf?

156

if project && !project.leaf?

149

add_available_filter "subproject_id",

157

add_available_filter "subproject_id",

150

:type => :list_subprojects,

158

:type => :list_subprojects,

151

:values => lambda { subproject_values }

159

:values => lambda { subproject_values }

152

end

160

end

153

161

154

162

155

issue_custom_fields = project ? project.all_issue_custom_fields : IssueCustomField.where(:is_for_all => true)

163

issue_custom_fields = project ? project.all_issue_custom_fields : IssueCustomField.where(:is_for_all => true)

156

add_custom_fields_filters(issue_custom_fields)

164

add_custom_fields_filters(issue_custom_fields)

157

165

158

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

166

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

159

167

160

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

168

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

161

add_available_filter relation_type, :type => :relation, :label => options[:name], :values => lambda {all_projects_values}

169

add_available_filter relation_type, :type => :relation, :label => options[:name], :values => lambda {all_projects_values}

162

end

170

end

163

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

171

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

164

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

172

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

165

173

166

add_available_filter "issue_id", :type => :integer, :label => :label_issue

174

add_available_filter "issue_id", :type => :integer, :label => :label_issue

167

175

168

add_available_filter("updated_by",

169

:type => :list, :values => lambda { author_values }

170

)

171

172

add_available_filter("last_updated_by",

173

:type => :list, :values => lambda { author_values }

174

)

175

176

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

176

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

177

delete_available_filter field

177

delete_available_filter field

178

}

178

}

179

end

179

end

180

181

def available_columns

181

def available_columns

182

return @available_columns if @available_columns

182

return @available_columns if @available_columns

183

@available_columns = self.class.available_columns.dup

183

@available_columns = self.class.available_columns.dup

184

@available_columns += (project ?

184

@available_columns += (project ?

185

project.all_issue_custom_fields :

185

project.all_issue_custom_fields :

186

IssueCustomField

186

IssueCustomField

187

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

187

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

188

189

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

189

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

190

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

190

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

191

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

191

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

192

# insert the column after total_estimated_hours or at the end

192

# insert the column after total_estimated_hours or at the end

193

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

193

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

194

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

194

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

195

:default_order => 'desc',

195

:default_order => 'desc',

196

:caption => :label_spent_time,

196

:caption => :label_spent_time,

197

:totalable => true

197

:totalable => true

198

)

198

)

199

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

199

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

200

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

200

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

201

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

201

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

202

:default_order => 'desc',

202

:default_order => 'desc',

203

:caption => :label_total_spent_time

203

:caption => :label_total_spent_time

204

)

204

)

205

end

205

end

206

207

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

207

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

208

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

208

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

209

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

209

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

210

end

210

end

211

212

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

212

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

213

@available_columns.reject! {|column|

213

@available_columns.reject! {|column|

214

disabled_fields.include?(column.name.to_s)

214

disabled_fields.include?(column.name.to_s)

215

}

215

}

216

217

@available_columns

217

@available_columns

218

end

218

end

219

220

def default_columns_names

220

def default_columns_names

221

@default_columns_names ||= begin

221

@default_columns_names ||= begin

222

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

222

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

223

224

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

224

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

225

end

225

end

226

end

226

end

227

228

def default_totalable_names

228

def default_totalable_names

229

Setting.issue_list_default_totals.map(&:to_sym)

229

Setting.issue_list_default_totals.map(&:to_sym)

230

end

230

end

231

232

def base_scope

232

def base_scope

233

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

233

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

234

end

234

end

235

236

# Returns the issue count

236

# Returns the issue count

237

def issue_count

237

def issue_count

238

base_scope.count

238

base_scope.count

239

rescue ::ActiveRecord::StatementInvalid => e

239

rescue ::ActiveRecord::StatementInvalid => e

240

raise StatementInvalid.new(e.message)

240

raise StatementInvalid.new(e.message)

241

end

241

end

242

243

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

243

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

244

def issue_count_by_group

244

def issue_count_by_group

245

grouped_query do |scope|

245

grouped_query do |scope|

246

scope.count

246

scope.count

247

end

247

end

248

end

248

end

249

250

# Returns sum of all the issue's estimated_hours

250

# Returns sum of all the issue's estimated_hours

251

def total_for_estimated_hours(scope)

251

def total_for_estimated_hours(scope)

252

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

252

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

253

end

253

end

254

255

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

255

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

256

def total_for_spent_hours(scope)

256

def total_for_spent_hours(scope)

257

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

257

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

258

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

258

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

259

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

259

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

260

# that would trigger a ambiguous column name error

260

# that would trigger a ambiguous column name error

261

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").

261

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").

262

sum("joined_time_entries.hours")

262

sum("joined_time_entries.hours")

263

else

263

else

264

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

264

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

265

end

265

end

266

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

266

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

267

end

267

end

268

269

# Returns the issues

269

# Returns the issues

270

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

270

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

271

def issues(options={})

271

def issues(options={})

272

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

272

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

273

274

scope = Issue.visible.

274

scope = Issue.visible.

275

joins(:status, :project).

275

joins(:status, :project).

276

where(statement).

276

where(statement).

277

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

277

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

278

where(options[:conditions]).

278

where(options[:conditions]).

279

order(order_option).

279

order(order_option).

280

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

280

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

281

limit(options[:limit]).

281

limit(options[:limit]).

282

offset(options[:offset])

282

offset(options[:offset])

283

284

scope = scope.preload([:tracker, :priority, :author, :assigned_to, :fixed_version, :category] & columns.map(&:name))

284

scope = scope.preload([:tracker, :priority, :author, :assigned_to, :fixed_version, :category] & columns.map(&:name))

285

if has_custom_field_column?

285

if has_custom_field_column?

286

scope = scope.preload(:custom_values)

286

scope = scope.preload(:custom_values)

287

end

287

end

288

289

issues = scope.to_a

289

issues = scope.to_a

290

291

if has_column?(:spent_hours)

291

if has_column?(:spent_hours)

292

Issue.load_visible_spent_hours(issues)

292

Issue.load_visible_spent_hours(issues)

293

end

293

end

294

if has_column?(:total_spent_hours)

294

if has_column?(:total_spent_hours)

295

Issue.load_visible_total_spent_hours(issues)

295

Issue.load_visible_total_spent_hours(issues)

296

end

296

end

297

if has_column?(:relations)

297

if has_column?(:relations)

298

Issue.load_visible_relations(issues)

298

Issue.load_visible_relations(issues)

299

end

299

end

300

issues

300

issues

301

rescue ::ActiveRecord::StatementInvalid => e

301

rescue ::ActiveRecord::StatementInvalid => e

302

raise StatementInvalid.new(e.message)

302

raise StatementInvalid.new(e.message)

303

end

303

end

304

305

# Returns the issues ids

305

# Returns the issues ids

306

def issue_ids(options={})

306

def issue_ids(options={})

307

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

307

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

308

309

Issue.visible.

309

Issue.visible.

310

joins(:status, :project).

310

joins(:status, :project).

311

where(statement).

311

where(statement).

312

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

312

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

313

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

313

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

314

where(options[:conditions]).

314

where(options[:conditions]).

315

order(order_option).

315

order(order_option).

316

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

316

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

317

limit(options[:limit]).

317

limit(options[:limit]).

318

offset(options[:offset]).

318

offset(options[:offset]).

319

pluck(:id)

319

pluck(:id)

320

rescue ::ActiveRecord::StatementInvalid => e

320

rescue ::ActiveRecord::StatementInvalid => e

321

raise StatementInvalid.new(e.message)

321

raise StatementInvalid.new(e.message)

322

end

322

end

323

324

# Returns the journals

324

# Returns the journals

325

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

325

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

326

def journals(options={})

326

def journals(options={})

327

Journal.visible.

327

Journal.visible.

328

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

328

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

329

where(statement).

329

where(statement).

330

order(options[:order]).

330

order(options[:order]).

331

limit(options[:limit]).

331

limit(options[:limit]).

332

offset(options[:offset]).

332

offset(options[:offset]).

333

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

333

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

334

to_a

334

to_a

335

rescue ::ActiveRecord::StatementInvalid => e

335

rescue ::ActiveRecord::StatementInvalid => e

336

raise StatementInvalid.new(e.message)

336

raise StatementInvalid.new(e.message)

337

end

337

end

338

339

# Returns the versions

339

# Returns the versions

340

# Valid options are :conditions

340

# Valid options are :conditions

341

def versions(options={})

341

def versions(options={})

342

Version.visible.

342

Version.visible.

343

where(project_statement).

343

where(project_statement).

344

where(options[:conditions]).

344

where(options[:conditions]).

345

includes(:project).

345

includes(:project).

346

references(:project).

346

references(:project).

347

to_a

347

to_a

348

rescue ::ActiveRecord::StatementInvalid => e

348

rescue ::ActiveRecord::StatementInvalid => e

349

raise StatementInvalid.new(e.message)

349

raise StatementInvalid.new(e.message)

350

end

350

end

351

352

def sql_for_updated_by_field(field, operator, value)

352

def sql_for_updated_by_field(field, operator, value)

353

neg = (operator == '!' ? 'NOT' : '')

353

neg = (operator == '!' ? 'NOT' : '')

354

subquery = "SELECT 1 FROM #{Journal.table_name}" +

354

subquery = "SELECT 1 FROM #{Journal.table_name}" +

355

" WHERE #{Journal.table_name}.journalized_type='Issue' AND #{Journal.table_name}.journalized_id=#{Issue.table_name}.id" +

355

" WHERE #{Journal.table_name}.journalized_type='Issue' AND #{Journal.table_name}.journalized_id=#{Issue.table_name}.id" +

356

" AND (#{sql_for_field field, '=', value, Journal.table_name, 'user_id'})" +

356

" AND (#{sql_for_field field, '=', value, Journal.table_name, 'user_id'})" +

357

" AND (#{Journal.visible_notes_condition(User.current, :skip_pre_condition => true)})"

357

" AND (#{Journal.visible_notes_condition(User.current, :skip_pre_condition => true)})"

358

359

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

359

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

360

end

360

end

361

362

def sql_for_last_updated_by_field(field, operator, value)

362

def sql_for_last_updated_by_field(field, operator, value)

363

neg = (operator == '!' ? 'NOT' : '')

363

neg = (operator == '!' ? 'NOT' : '')

364

subquery = "SELECT 1 FROM #{Journal.table_name} sj" +

364

subquery = "SELECT 1 FROM #{Journal.table_name} sj" +

365

" WHERE sj.journalized_type='Issue' AND sj.journalized_id=#{Issue.table_name}.id AND (#{sql_for_field field, '=', value, 'sj', 'user_id'})" +

365

" WHERE sj.journalized_type='Issue' AND sj.journalized_id=#{Issue.table_name}.id AND (#{sql_for_field field, '=', value, 'sj', 'user_id'})" +

366

" AND sj.id = (SELECT MAX(#{Journal.table_name}.id) FROM #{Journal.table_name}" +

366

" AND sj.id = (SELECT MAX(#{Journal.table_name}.id) FROM #{Journal.table_name}" +

367

" WHERE #{Journal.table_name}.journalized_type='Issue' AND #{Journal.table_name}.journalized_id=#{Issue.table_name}.id" +

367

" WHERE #{Journal.table_name}.journalized_type='Issue' AND #{Journal.table_name}.journalized_id=#{Issue.table_name}.id" +

368

" AND (#{Journal.visible_notes_condition(User.current, :skip_pre_condition => true)}))"

368

" AND (#{Journal.visible_notes_condition(User.current, :skip_pre_condition => true)}))"

369

370

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

370

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

371

end

371

end

372

373

def sql_for_watcher_id_field(field, operator, value)

373

def sql_for_watcher_id_field(field, operator, value)

374

db_table = Watcher.table_name

374

db_table = Watcher.table_name

375

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

375

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

376

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

376

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

377

end

377

end

378

379

def sql_for_member_of_group_field(field, operator, value)

379

def sql_for_member_of_group_field(field, operator, value)

380

if operator == '*' # Any group

380

if operator == '*' # Any group

381

groups = Group.givable

381

groups = Group.givable

382

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

382

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

383

elsif operator == "!*"

383

elsif operator == "!*"

384

groups = Group.givable

384

groups = Group.givable

385

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

385

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

386

else

386

else

387

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

387

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

388

end

388

end

389

groups ||= []

389

groups ||= []

390

391

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

391

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

392

user_ids + group.user_ids + [group.id]

392

user_ids + group.user_ids + [group.id]

393

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

393

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

394

395

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

395

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

396

end

396

end

397

398

def sql_for_assigned_to_role_field(field, operator, value)

398

def sql_for_assigned_to_role_field(field, operator, value)

399

case operator

399

case operator

400

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

400

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

401

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

401

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

402

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

402

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

403

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

403

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

404

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

404

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

405

when "=", "!"

405

when "=", "!"

406

role_cond = value.any? ?

406

role_cond = value.any? ?

407

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

407

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

408

"1=0"

408

"1=0"

409

410

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

410

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

411

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

411

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

412

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

412

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

413

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

413

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

414

end

414

end

415

end

415

end

416

417

def sql_for_fixed_version_status_field(field, operator, value)

417

def sql_for_fixed_version_status_field(field, operator, value)

418

where = sql_for_field(field, operator, value, Version.table_name, "status")

418

where = sql_for_field(field, operator, value, Version.table_name, "status")

419

version_ids = versions(:conditions => [where]).map(&:id)

419

version_ids = versions(:conditions => [where]).map(&:id)

420

421

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

421

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

422

"(#{nl} #{sql_for_field("fixed_version_id", "=", version_ids, Issue.table_name, "fixed_version_id")})"

422

"(#{nl} #{sql_for_field("fixed_version_id", "=", version_ids, Issue.table_name, "fixed_version_id")})"

423

end

423

end

424

425

def sql_for_fixed_version_due_date_field(field, operator, value)

425

def sql_for_fixed_version_due_date_field(field, operator, value)

426

where = sql_for_field(field, operator, value, Version.table_name, "effective_date")

426

where = sql_for_field(field, operator, value, Version.table_name, "effective_date")

427

version_ids = versions(:conditions => [where]).map(&:id)

427

version_ids = versions(:conditions => [where]).map(&:id)

428

429

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

429

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

430

"(#{nl} #{sql_for_field("fixed_version_id", "=", version_ids, Issue.table_name, "fixed_version_id")})"

430

"(#{nl} #{sql_for_field("fixed_version_id", "=", version_ids, Issue.table_name, "fixed_version_id")})"

431

end

431

end

432

433

def sql_for_is_private_field(field, operator, value)

433

def sql_for_is_private_field(field, operator, value)

434

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

434

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

435

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

435

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

436

437

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

437

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

438

end

438

end

439

440

def sql_for_parent_id_field(field, operator, value)

440

def sql_for_parent_id_field(field, operator, value)

441

case operator

441

case operator

442

when "="

442

when "="

443

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

443

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

444

when "~"

444

when "~"

445

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

445

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

446

if root_id && lft && rgt

446

if root_id && lft && rgt

447

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

447

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

448

else

448

else

449

"1=0"

449

"1=0"

450

end

450

end

451

when "!*"

451

when "!*"

452

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

452

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

453

when "*"

453

when "*"

454

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

454

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

455

end

455

end

456

end

456

end

457

458

def sql_for_child_id_field(field, operator, value)

458

def sql_for_child_id_field(field, operator, value)

459

case operator

459

case operator

460

when "="

460

when "="

461

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

461

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

462

if parent_id

462

if parent_id

463

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

463

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

464

else

464

else

465

"1=0"

465

"1=0"

466

end

466

end

467

when "~"

467

when "~"

468

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

468

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

469

if root_id && lft && rgt

469

if root_id && lft && rgt

470

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

470

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

471

else

471

else

472

"1=0"

472

"1=0"

473

end

473

end

474

when "!*"

474

when "!*"

475

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

475

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

476

when "*"

476

when "*"

477

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

477

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

478

end

478

end

479

end

479

end

480

481

def sql_for_updated_on_field(field, operator, value)

481

def sql_for_updated_on_field(field, operator, value)

482

case operator

482

case operator

483

when "!*"

483

when "!*"

484

"#{Issue.table_name}.updated_on = #{Issue.table_name}.created_on"

484

"#{Issue.table_name}.updated_on = #{Issue.table_name}.created_on"

485

when "*"

485

when "*"

486

"#{Issue.table_name}.updated_on > #{Issue.table_name}.created_on"

486

"#{Issue.table_name}.updated_on > #{Issue.table_name}.created_on"

487

else

487

else

488

sql_for_field("updated_on", operator, value, Issue.table_name, "updated_on")

488

sql_for_field("updated_on", operator, value, Issue.table_name, "updated_on")

489

end

489

end

490

end

490

end

491

492

def sql_for_issue_id_field(field, operator, value)

492

def sql_for_issue_id_field(field, operator, value)

493

if operator == "="

493

if operator == "="

494

# accepts a comma separated list of ids

494

# accepts a comma separated list of ids

495

ids = value.first.to_s.scan(/\d+/).map(&:to_i)

495

ids = value.first.to_s.scan(/\d+/).map(&:to_i)

496

if ids.present?

496

if ids.present?

497

"#{Issue.table_name}.id IN (#{ids.join(",")})"

497

"#{Issue.table_name}.id IN (#{ids.join(",")})"

498

else

498

else

499

"1=0"

499

"1=0"

500

end

500

end

501

else

501

else

502

sql_for_field("id", operator, value, Issue.table_name, "id")

502

sql_for_field("id", operator, value, Issue.table_name, "id")

503

end

503

end

504

end

504

end

505

506

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

506

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

507

relation_options = IssueRelation::TYPES[field]

507

relation_options = IssueRelation::TYPES[field]

508

return relation_options unless relation_options

508

return relation_options unless relation_options

509

510

relation_type = field

510

relation_type = field

511

join_column, target_join_column = "issue_from_id", "issue_to_id"

511

join_column, target_join_column = "issue_from_id", "issue_to_id"

512

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

512

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

513

relation_type = relation_options[:reverse] || relation_type

513

relation_type = relation_options[:reverse] || relation_type

514

join_column, target_join_column = target_join_column, join_column

514

join_column, target_join_column = target_join_column, join_column

515

end

515

end

516

517

sql = case operator

517

sql = case operator

518

when "*", "!*"

518

when "*", "!*"

519

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

519

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

520

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

520

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

521

when "=", "!"

521

when "=", "!"

522

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

522

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

523

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

523

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

524

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

524

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

525

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

525

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

526

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

526

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

527

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

527

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

528

when "*o", "!o"

528

when "*o", "!o"

529

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

529

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

530

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

530

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

531

end

531

end

532

533

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

533

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

534

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

534

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

535

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

535

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

536

end

536

end

537

"(#{sql})"

537

"(#{sql})"

538

end

538

end

539

540

def find_assigned_to_id_filter_values(values)

540

def find_assigned_to_id_filter_values(values)

541

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

541

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

542

end

542

end

543

alias :find_author_id_filter_values :find_assigned_to_id_filter_values

543

alias :find_author_id_filter_values :find_assigned_to_id_filter_values

544

545

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

545

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

546

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

546

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

547

end

547

end

548

549

def joins_for_order_statement(order_options)

549

def joins_for_order_statement(order_options)

550

joins = [super]

550

joins = [super]

551

552

if order_options

552

if order_options

553

if order_options.include?('authors')

553

if order_options.include?('authors')

554

joins << "LEFT OUTER JOIN #{User.table_name} authors ON authors.id = #{queried_table_name}.author_id"

554

joins << "LEFT OUTER JOIN #{User.table_name} authors ON authors.id = #{queried_table_name}.author_id"

555

end

555

end

556

if order_options.include?('users')

556

if order_options.include?('users')

557

joins << "LEFT OUTER JOIN #{User.table_name} ON #{User.table_name}.id = #{queried_table_name}.assigned_to_id"

557

joins << "LEFT OUTER JOIN #{User.table_name} ON #{User.table_name}.id = #{queried_table_name}.assigned_to_id"

558

end

558

end

559

if order_options.include?('versions')

559

if order_options.include?('versions')

560

joins << "LEFT OUTER JOIN #{Version.table_name} ON #{Version.table_name}.id = #{queried_table_name}.fixed_version_id"

560

joins << "LEFT OUTER JOIN #{Version.table_name} ON #{Version.table_name}.id = #{queried_table_name}.fixed_version_id"

561

end

561

end

562

if order_options.include?('issue_categories')

562

if order_options.include?('issue_categories')

563

joins << "LEFT OUTER JOIN #{IssueCategory.table_name} ON #{IssueCategory.table_name}.id = #{queried_table_name}.category_id"

563

joins << "LEFT OUTER JOIN #{IssueCategory.table_name} ON #{IssueCategory.table_name}.id = #{queried_table_name}.category_id"

564

end

564

end

565

if order_options.include?('trackers')

565

if order_options.include?('trackers')

566

joins << "LEFT OUTER JOIN #{Tracker.table_name} ON #{Tracker.table_name}.id = #{queried_table_name}.tracker_id"

566

joins << "LEFT OUTER JOIN #{Tracker.table_name} ON #{Tracker.table_name}.id = #{queried_table_name}.tracker_id"

567

end

567

end

568

if order_options.include?('enumerations')

568

if order_options.include?('enumerations')

569

joins << "LEFT OUTER JOIN #{IssuePriority.table_name} ON #{IssuePriority.table_name}.id = #{queried_table_name}.priority_id"

569

joins << "LEFT OUTER JOIN #{IssuePriority.table_name} ON #{IssuePriority.table_name}.id = #{queried_table_name}.priority_id"

570

end

570

end

571

end

571

end

572

573

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

573

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

574

end

574

end

575

end

575

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-2016  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.view_permission = :view_issues
               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)
               ]
               def initialize(attributes=nil, *args)
                 super attributes
                 self.filters ||= { 'status_id' => {:operator => "o", :values => [""]} }
               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
                 add_available_filter "status_id",
                   :type => :list_status, :values => lambda { IssueStatus.sorted.collect{|s| [s.name, s.id.to_s] } }
                 add_available_filter("project_id",
                   :type => :list, :values => lambda { project_values }
                 ) if project.nil?
                 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] }
                 add_available_filter("author_id",
                   :type => :list, :values => lambda { author_values }
                 )
                 add_available_filter("assigned_to_id",
                   :type => :list_optional, :values => lambda { assigned_to_values }
                 )
                 add_available_filter("member_of_group",
                   :type => :list_optional, :values => lambda { Group.givable.visible.collect {|g| [g.name, g.id.to_s] } }
                 )
                 add_available_filter("assigned_to_role",
                   :type => :list_optional, :values => lambda { Role.givable.collect {|r| [r.name, r.id.to_s] } }
                 )
                 add_available_filter "fixed_version_id",
                   :type => :list_optional, :values => lambda { fixed_version_values }
                 add_available_filter "fixed_version.due_date",
                   :type => :date,
                   :name => l(:label_attribute_of_fixed_version, :name => l(:field_effective_date))
                 add_available_filter "fixed_version.status",
                   :type => :list,
                   :name => l(:label_attribute_of_fixed_version, :name => l(:field_status)),
                   :values => Version::VERSION_STATUSES.map{|s| [l("version_status_#{s}"), s] }
                 add_available_filter "category_id",
                   :type => :list_optional,
                   :values => lambda { project.issue_categories.collect{|s| [s.name, s.id.to_s] } } if project
                 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
+                add_available_filter("updated_by",
+                  :type => :list, :values => lambda { author_values }
+                )
+                add_available_filter("last_updated_by",
+                  :type => :list, :values => lambda { author_values }
+                )
                 if project && !project.leaf?
                   add_available_filter "subproject_id",
                     :type => :list_subprojects,
                     :values => lambda { subproject_values }
                 end
                 issue_custom_fields = project ? project.all_issue_custom_fields : IssueCustomField.where(:is_for_all => true)
                 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], :values => lambda {all_projects_values}
                 end
                 add_available_filter "parent_id", :type => :tree, :label => :field_parent_issue
                 add_available_filter "child_id", :type => :tree, :label => :label_subtask_plural
                 add_available_filter "issue_id", :type => :integer, :label => :label_issue
-                add_available_filter("updated_by",
-                  :type => :list, :values => lambda { author_values }
-                add_available_filter("last_updated_by",
-                  :type => :list, :values => lambda { author_values }
                 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 default_totalable_names
                 Setting.issue_list_default_totals.map(&:to_sym)
               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([:tracker, :priority, :author, :assigned_to, :fixed_version, :category] & columns.map(&:name))
                 if has_custom_field_column?
                   scope = scope.preload(:custom_values)
                 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_updated_by_field(field, operator, value)
                 neg = (operator == '!' ? 'NOT' : '')
                 subquery = "SELECT 1 FROM #{Journal.table_name}" +
                   " WHERE #{Journal.table_name}.journalized_type='Issue' AND #{Journal.table_name}.journalized_id=#{Issue.table_name}.id" +
                   " AND (#{sql_for_field field, '=', value, Journal.table_name, 'user_id'})" +
                   " AND (#{Journal.visible_notes_condition(User.current, :skip_pre_condition => true)})"
                 "#{neg} EXISTS (#{subquery})"
               end
               def sql_for_last_updated_by_field(field, operator, value)
                 neg = (operator == '!' ? 'NOT' : '')
                 subquery = "SELECT 1 FROM #{Journal.table_name} sj" +
                   " WHERE sj.journalized_type='Issue' AND sj.journalized_id=#{Issue.table_name}.id AND (#{sql_for_field field, '=', value, 'sj', 'user_id'})" +
                   " AND sj.id = (SELECT MAX(#{Journal.table_name}.id) FROM #{Journal.table_name}" +
                   "   WHERE #{Journal.table_name}.journalized_type='Issue' AND #{Journal.table_name}.journalized_id=#{Issue.table_name}.id" +
                   "   AND (#{Journal.visible_notes_condition(User.current, :skip_pre_condition => true)}))"
                 "#{neg} EXISTS (#{subquery})"
               end
               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_fixed_version_status_field(field, operator, value)
                 where = sql_for_field(field, operator, value, Version.table_name, "status")
                 version_ids = versions(:conditions => [where]).map(&:id)
                 nl = operator == "!" ? "#{Issue.table_name}.fixed_version_id IS NULL OR" : ''
                 "(#{nl} #{sql_for_field("fixed_version_id", "=", version_ids, Issue.table_name, "fixed_version_id")})"
               end
               def sql_for_fixed_version_due_date_field(field, operator, value)
                 where = sql_for_field(field, operator, value, Version.table_name, "effective_date")
                 version_ids = versions(:conditions => [where]).map(&:id)
                 nl = operator == "!*" ? "#{Issue.table_name}.fixed_version_id IS NULL OR" : ''
                 "(#{nl} #{sql_for_field("fixed_version_id", "=", version_ids, Issue.table_name, "fixed_version_id")})"
               end
               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_updated_on_field(field, operator, value)
                 case operator
                 when "!*"
                   "#{Issue.table_name}.updated_on = #{Issue.table_name}.created_on"
                 when "*"
                   "#{Issue.table_name}.updated_on > #{Issue.table_name}.created_on"
                 else
                   sql_for_field("updated_on", operator, value, Issue.table_name, "updated_on")
                 end
               end
               def sql_for_issue_id_field(field, operator, value)
                 if operator == "="
                   # accepts a comma separated list of ids
                   ids = value.first.to_s.scan(/\d+/).map(&:to_i)
                   if ids.present?
                     "#{Issue.table_name}.id IN (#{ids.join(",")})"
                   else
                     "1=0"
                   end
                 else
                   sql_for_field("id", operator, value, Issue.table_name, "id")
                 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
               def joins_for_order_statement(order_options)
                 joins = [super]
                 if order_options
                   if order_options.include?('authors')
                     joins << "LEFT OUTER JOIN #{User.table_name} authors ON authors.id = #{queried_table_name}.author_id"
                   end
                   if order_options.include?('users')
                     joins << "LEFT OUTER JOIN #{User.table_name} ON #{User.table_name}.id = #{queried_table_name}.assigned_to_id"
                   end
                   if order_options.include?('versions')
                     joins << "LEFT OUTER JOIN #{Version.table_name} ON #{Version.table_name}.id = #{queried_table_name}.fixed_version_id"
                   end
                   if order_options.include?('issue_categories')
                     joins << "LEFT OUTER JOIN #{IssueCategory.table_name} ON #{IssueCategory.table_name}.id = #{queried_table_name}.category_id"
                   end
                   if order_options.include?('trackers')
                     joins << "LEFT OUTER JOIN #{Tracker.table_name} ON #{Tracker.table_name}.id = #{queried_table_name}.tracker_id"
                   end
                   if order_options.include?('enumerations')
                     joins << "LEFT OUTER JOIN #{IssuePriority.table_name} ON #{IssuePriority.table_name}.id = #{queried_table_name}.priority_id"
                   end
                 end
                 joins.any? ? joins.join(' ') : nil
               end
             end