jro_apps/redmine Commit - r14220:d1d1f81e9468

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

37

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

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

if versions.any?

203

add_available_filter "fixed_version_id",

203

add_available_filter "fixed_version_id",

204

:type => :list_optional,

204

:type => :list_optional,

205

: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

206

end

207

208

if categories.any?

208

if categories.any?

209

add_available_filter "category_id",

209

add_available_filter "category_id",

210

:type => :list_optional,

210

:type => :list_optional,

211

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

211

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

212

end

212

end

213

214

add_available_filter "subject", :type => :text

214

add_available_filter "subject", :type => :text

215

add_available_filter "created_on", :type => :date_past

215

add_available_filter "created_on", :type => :date_past

216

add_available_filter "updated_on", :type => :date_past

216

add_available_filter "updated_on", :type => :date_past

217

add_available_filter "closed_on", :type => :date_past

217

add_available_filter "closed_on", :type => :date_past

218

add_available_filter "start_date", :type => :date

218

add_available_filter "start_date", :type => :date

219

add_available_filter "due_date", :type => :date

219

add_available_filter "due_date", :type => :date

220

add_available_filter "estimated_hours", :type => :float

220

add_available_filter "estimated_hours", :type => :float

221

add_available_filter "done_ratio", :type => :integer

221

add_available_filter "done_ratio", :type => :integer

222

223

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

223

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

224

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

224

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

225

add_available_filter "is_private",

225

add_available_filter "is_private",

226

:type => :list,

226

:type => :list,

227

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

227

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

228

end

228

end

229

230

if User.current.logged?

230

if User.current.logged?

231

add_available_filter "watcher_id",

231

add_available_filter "watcher_id",

232

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

232

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

233

end

233

end

234

235

if subprojects.any?

235

if subprojects.any?

236

add_available_filter "subproject_id",

236

add_available_filter "subproject_id",

237

:type => :list_subprojects,

237

:type => :list_subprojects,

238

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

238

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

239

end

239

end

240

241

add_custom_fields_filters(issue_custom_fields)

241

add_custom_fields_filters(issue_custom_fields)

242

243

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

243

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

244

245

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

245

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

246

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

246

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

247

end

247

end

248

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

248

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

249

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

249

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

250

251

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

251

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

252

delete_available_filter field

252

delete_available_filter field

253

}

253

}

254

end

254

end

255

256

def available_columns

256

def available_columns

257

return @available_columns if @available_columns

257

return @available_columns if @available_columns

258

@available_columns = self.class.available_columns.dup

258

@available_columns = self.class.available_columns.dup

259

@available_columns += (project ?

259

@available_columns += (project ?

260

project.all_issue_custom_fields :

260

project.all_issue_custom_fields :

261

IssueCustomField

261

IssueCustomField

262

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

262

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

263

264

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

264

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

265

index = nil

265

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

266

@available_columns.each_with_index {|column, i| index = i if column.name == :estimated_hours}

267

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

266

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

268

# insert the column after estimated_hours or at the end

267

# insert the column after total_estimated_hours or at the end

269

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

268

@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)",

269

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

271

:default_order => 'desc',

270

:default_order => 'desc',

272

:caption => :label_spent_time

271

:caption => :label_spent_time

273

)

272

)

274

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

273

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

275

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

274

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

276

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

275

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

277

:default_order => 'desc',

276

:default_order => 'desc',

278

:caption => :label_total_spent_time

277

:caption => :label_total_spent_time

279

)

278

)

280

end

279

end

281

280

282

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

281

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

283

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

282

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

284

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

283

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

285

end

284

end

286

285

287

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

286

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

288

@available_columns.reject! {|column|

287

@available_columns.reject! {|column|

289

disabled_fields.include?(column.name.to_s)

288

disabled_fields.include?(column.name.to_s)

290

}

289

}

291

290

292

@available_columns

291

@available_columns

293

end

292

end

294

293

295

def default_columns_names

294

def default_columns_names

296

@default_columns_names ||= begin

295

@default_columns_names ||= begin

297

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

296

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

298

297

299

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

298

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

300

end

299

end

301

end

300

end

302

301

303

# Returns the issue count

302

# Returns the issue count

304

def issue_count

303

def issue_count

305

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

304

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

306

rescue ::ActiveRecord::StatementInvalid => e

305

rescue ::ActiveRecord::StatementInvalid => e

307

raise StatementInvalid.new(e.message)

306

raise StatementInvalid.new(e.message)

308

end

307

end

309

308

310

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

309

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

311

def issue_count_by_group

310

def issue_count_by_group

312

r = nil

311

r = nil

313

if grouped?

312

if grouped?

314

begin

313

begin

315

# Rails3 will raise an (unexpected) RecordNotFound if there's only a nil group value

314

# Rails3 will raise an (unexpected) RecordNotFound if there's only a nil group value

316

r = Issue.visible.

315

r = Issue.visible.

317

joins(:status, :project).

316

joins(:status, :project).

318

where(statement).

317

where(statement).

319

joins(joins_for_order_statement(group_by_statement)).

318

joins(joins_for_order_statement(group_by_statement)).

320

group(group_by_statement).

319

group(group_by_statement).

321

count

320

count

322

rescue ActiveRecord::RecordNotFound

321

rescue ActiveRecord::RecordNotFound

323

r = {nil => issue_count}

322

r = {nil => issue_count}

324

end

323

end

325

c = group_by_column

324

c = group_by_column

326

if c.is_a?(QueryCustomFieldColumn)

325

if c.is_a?(QueryCustomFieldColumn)

327

r = r.keys.inject({}) {|h, k| h[c.custom_field.cast_value(k)] = r[k]; h}

326

r = r.keys.inject({}) {|h, k| h[c.custom_field.cast_value(k)] = r[k]; h}

328

end

327

end

329

end

328

end

330

r

329

r

331

rescue ::ActiveRecord::StatementInvalid => e

330

rescue ::ActiveRecord::StatementInvalid => e

332

raise StatementInvalid.new(e.message)

331

raise StatementInvalid.new(e.message)

333

end

332

end

334

333

335

# Returns the issues

334

# Returns the issues

336

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

335

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

337

def issues(options={})

336

def issues(options={})

338

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

337

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

339

338

340

scope = Issue.visible.

339

scope = Issue.visible.

341

joins(:status, :project).

340

joins(:status, :project).

342

where(statement).

341

where(statement).

343

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

342

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

344

where(options[:conditions]).

343

where(options[:conditions]).

345

order(order_option).

344

order(order_option).

346

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

345

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

347

limit(options[:limit]).

346

limit(options[:limit]).

348

offset(options[:offset])

347

offset(options[:offset])

349

348

350

scope = scope.preload(:custom_values)

349

scope = scope.preload(:custom_values)

351

if has_column?(:author)

350

if has_column?(:author)

352

scope = scope.preload(:author)

351

scope = scope.preload(:author)

353

end

352

end

354

353

355

issues = scope.to_a

354

issues = scope.to_a

356

355

357

if has_column?(:spent_hours)

356

if has_column?(:spent_hours)

358

Issue.load_visible_spent_hours(issues)

357

Issue.load_visible_spent_hours(issues)

359

end

358

end

360

if has_column?(:total_spent_hours)

359

if has_column?(:total_spent_hours)

361

Issue.load_visible_total_spent_hours(issues)

360

Issue.load_visible_total_spent_hours(issues)

362

end

361

end

363

if has_column?(:relations)

362

if has_column?(:relations)

364

Issue.load_visible_relations(issues)

363

Issue.load_visible_relations(issues)

365

end

364

end

366

issues

365

issues

367

rescue ::ActiveRecord::StatementInvalid => e

366

rescue ::ActiveRecord::StatementInvalid => e

368

raise StatementInvalid.new(e.message)

367

raise StatementInvalid.new(e.message)

369

end

368

end

370

369

371

# Returns the issues ids

370

# Returns the issues ids

372

def issue_ids(options={})

371

def issue_ids(options={})

373

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

372

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

374

373

375

Issue.visible.

374

Issue.visible.

376

joins(:status, :project).

375

joins(:status, :project).

377

where(statement).

376

where(statement).

378

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

377

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

379

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

378

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

380

where(options[:conditions]).

379

where(options[:conditions]).

381

order(order_option).

380

order(order_option).

382

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

381

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

383

limit(options[:limit]).

382

limit(options[:limit]).

384

offset(options[:offset]).

383

offset(options[:offset]).

385

pluck(:id)

384

pluck(:id)

386

rescue ::ActiveRecord::StatementInvalid => e

385

rescue ::ActiveRecord::StatementInvalid => e

387

raise StatementInvalid.new(e.message)

386

raise StatementInvalid.new(e.message)

388

end

387

end

389

388

390

# Returns the journals

389

# Returns the journals

391

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

390

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

392

def journals(options={})

391

def journals(options={})

393

Journal.visible.

392

Journal.visible.

394

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

393

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

395

where(statement).

394

where(statement).

396

order(options[:order]).

395

order(options[:order]).

397

limit(options[:limit]).

396

limit(options[:limit]).

398

offset(options[:offset]).

397

offset(options[:offset]).

399

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

398

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

400

to_a

399

to_a

401

rescue ::ActiveRecord::StatementInvalid => e

400

rescue ::ActiveRecord::StatementInvalid => e

402

raise StatementInvalid.new(e.message)

401

raise StatementInvalid.new(e.message)

403

end

402

end

404

403

405

# Returns the versions

404

# Returns the versions

406

# Valid options are :conditions

405

# Valid options are :conditions

407

def versions(options={})

406

def versions(options={})

408

Version.visible.

407

Version.visible.

409

where(project_statement).

408

where(project_statement).

410

where(options[:conditions]).

409

where(options[:conditions]).

411

includes(:project).

410

includes(:project).

412

references(:project).

411

references(:project).

413

to_a

412

to_a

414

rescue ::ActiveRecord::StatementInvalid => e

413

rescue ::ActiveRecord::StatementInvalid => e

415

raise StatementInvalid.new(e.message)

414

raise StatementInvalid.new(e.message)

416

end

415

end

417

416

418

def sql_for_watcher_id_field(field, operator, value)

417

def sql_for_watcher_id_field(field, operator, value)

419

db_table = Watcher.table_name

418

db_table = Watcher.table_name

420

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

419

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

421

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

420

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

422

end

421

end

423

422

424

def sql_for_member_of_group_field(field, operator, value)

423

def sql_for_member_of_group_field(field, operator, value)

425

if operator == '*' # Any group

424

if operator == '*' # Any group

426

groups = Group.givable

425

groups = Group.givable

427

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

426

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

428

elsif operator == "!*"

427

elsif operator == "!*"

429

groups = Group.givable

428

groups = Group.givable

430

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

431

else

430

else

432

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

431

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

433

end

432

end

434

groups ||= []

433

groups ||= []

435

434

436

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

435

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

437

user_ids + group.user_ids + [group.id]

436

user_ids + group.user_ids + [group.id]

438

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

437

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

439

438

440

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

439

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

441

end

440

end

442

441

443

def sql_for_assigned_to_role_field(field, operator, value)

442

def sql_for_assigned_to_role_field(field, operator, value)

444

case operator

443

case operator

445

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

444

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

446

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

445

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

447

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

446

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

448

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

447

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

449

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

448

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

450

when "=", "!"

449

when "=", "!"

451

role_cond = value.any? ?

450

role_cond = value.any? ?

452

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

451

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

453

"1=0"

452

"1=0"

454

453

455

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

454

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

456

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

455

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

457

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

456

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

458

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

457

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

459

end

458

end

460

end

459

end

461

460

462

def sql_for_is_private_field(field, operator, value)

461

def sql_for_is_private_field(field, operator, value)

463

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

462

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

464

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

463

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

465

464

466

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

465

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

467

end

466

end

468

467

469

def sql_for_parent_id_field(field, operator, value)

468

def sql_for_parent_id_field(field, operator, value)

470

case operator

469

case operator

471

when "="

470

when "="

472

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

471

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

473

when "~"

472

when "~"

474

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

473

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

475

if root_id && lft && rgt

474

if root_id && lft && rgt

476

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

475

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

477

else

476

else

478

"1=0"

477

"1=0"

479

end

478

end

480

when "!*"

479

when "!*"

481

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

480

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

482

when "*"

481

when "*"

483

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

482

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

484

end

483

end

485

end

484

end

486

485

487

def sql_for_child_id_field(field, operator, value)

486

def sql_for_child_id_field(field, operator, value)

488

case operator

487

case operator

489

when "="

488

when "="

490

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

489

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

491

if parent_id

490

if parent_id

492

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

491

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

493

else

492

else

494

"1=0"

493

"1=0"

495

end

494

end

496

when "~"

495

when "~"

497

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

496

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

498

if root_id && lft && rgt

497

if root_id && lft && rgt

499

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

498

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

500

else

499

else

501

"1=0"

500

"1=0"

502

end

501

end

503

when "!*"

502

when "!*"

504

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

503

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

505

when "*"

504

when "*"

506

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

505

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

507

end

506

end

508

end

507

end

509

508

510

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

509

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

511

relation_options = IssueRelation::TYPES[field]

510

relation_options = IssueRelation::TYPES[field]

512

return relation_options unless relation_options

511

return relation_options unless relation_options

513

512

514

relation_type = field

513

relation_type = field

515

join_column, target_join_column = "issue_from_id", "issue_to_id"

514

join_column, target_join_column = "issue_from_id", "issue_to_id"

516

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

515

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

517

relation_type = relation_options[:reverse] || relation_type

516

relation_type = relation_options[:reverse] || relation_type

518

join_column, target_join_column = target_join_column, join_column

517

join_column, target_join_column = target_join_column, join_column

519

end

518

end

520

519

521

sql = case operator

520

sql = case operator

522

when "*", "!*"

521

when "*", "!*"

523

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

522

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

524

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

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

525

when "=", "!"

524

when "=", "!"

526

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

525

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

527

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

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)}' AND #{IssueRelation.table_name}.#{target_join_column} = #{value.first.to_i})"

528

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

527

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

529

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

528

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

530

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

529

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

531

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

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.project_id #{comp} #{value.first.to_i})"

532

end

531

end

533

532

534

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

533

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

535

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

534

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

536

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

535

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

537

end

536

end

538

"(#{sql})"

537

"(#{sql})"

539

end

538

end

540

539

541

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

540

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

542

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

541

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

543

end

542

end

544

end

543

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"),
                 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",
                     :type => :list_optional,
                     :values => versions.sort.collect{|s| ["#{s.project.name} - #{s.name}", s.id.to_s] }
                 end
                 if categories.any?
                   add_available_filter "category_id",
                     :type => :list_optional,
                     :values => categories.collect{|s| [s.name, s.id.to_s] }
                 end
                 add_available_filter "subject", :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 = nil
+                  index = @available_columns.find_index {|column| column.name == :total_estimated_hours}
-                  @available_columns.each_with_index {|column, i| index = i if column.name == :estimated_hours}
                   index = (index ? index + 1 : -1)
-                  # insert the column after estimated_hours or at the end
+                  # 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
                   )
                   @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
               # Returns the issue count
               def issue_count
                 Issue.visible.joins(:status, :project).where(statement).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
                 r = nil
                 if grouped?
                   begin
                     # Rails3 will raise an (unexpected) RecordNotFound if there's only a nil group value
                     r = Issue.visible.
                       joins(:status, :project).
                       where(statement).
                       joins(joins_for_order_statement(group_by_statement)).
                       group(group_by_statement).
                       count
                   rescue ActiveRecord::RecordNotFound
                     r = {nil => issue_count}
                   end
                   c = group_by_column
                   if c.is_a?(QueryCustomFieldColumn)
                     r = r.keys.inject({}) {|h, k| h[c.custom_field.cast_value(k)] = r[k]; h}
                   end
                 end
                 r
               rescue ::ActiveRecord::StatementInvalid => e
                 raise StatementInvalid.new(e.message)
               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})"
                   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
               IssueRelation::TYPES.keys.each do |relation_type|
                 alias_method "sql_for_#{relation_type}_field".to_sym, :sql_for_relations
               end
             end