jro_apps/redmine Commit - r3493:5225fb70f533

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 QueryColumn

18

class QueryColumn

19

attr_accessor :name, :sortable, :groupable, :default_order

19

attr_accessor :name, :sortable, :groupable, :default_order

20

include Redmine::I18n

20

include Redmine::I18n

21

22

def initialize(name, options={})

22

def initialize(name, options={})

23

self.name = name

23

self.name = name

24

self.sortable = options[:sortable]

24

self.sortable = options[:sortable]

25

self.groupable = options[:groupable] || false

25

self.groupable = options[:groupable] || false

26

if groupable == true

26

if groupable == true

27

self.groupable = name.to_s

27

self.groupable = name.to_s

28

end

28

end

29

self.default_order = options[:default_order]

29

self.default_order = options[:default_order]

30

end

30

end

31

32

def caption

32

def caption

33

l("field_#{name}")

33

l("field_#{name}")

34

end

34

end

35

36

# Returns true if the column is sortable, otherwise false

36

# Returns true if the column is sortable, otherwise false

37

def sortable?

37

def sortable?

38

!sortable.nil?

38

!sortable.nil?

39

end

39

end

40

41

def value(issue)

41

def value(issue)

42

issue.send name

42

issue.send name

43

end

43

end

44

end

44

end

45

46

class QueryCustomFieldColumn < QueryColumn

46

class QueryCustomFieldColumn < QueryColumn

47

48

def initialize(custom_field)

48

def initialize(custom_field)

49

self.name = "cf_#{custom_field.id}".to_sym

49

self.name = "cf_#{custom_field.id}".to_sym

50

self.sortable = custom_field.order_statement || false

50

self.sortable = custom_field.order_statement || false

51

if %w(list date bool int).include?(custom_field.field_format)

51

if %w(list date bool int).include?(custom_field.field_format)

52

self.groupable = custom_field.order_statement

52

self.groupable = custom_field.order_statement

53

end

53

end

54

self.groupable ||= false

54

self.groupable ||= false

55

@cf = custom_field

55

@cf = custom_field

56

end

56

end

57

58

def caption

58

def caption

59

@cf.name

59

@cf.name

60

end

60

end

61

62

def custom_field

62

def custom_field

63

@cf

63

@cf

64

end

64

end

65

66

def value(issue)

66

def value(issue)

67

cv = issue.custom_values.detect {|v| v.custom_field_id == @cf.id}

67

cv = issue.custom_values.detect {|v| v.custom_field_id == @cf.id}

68

cv && @cf.cast_value(cv.value)

68

cv && @cf.cast_value(cv.value)

69

end

69

end

70

end

70

end

71

72

class Query < ActiveRecord::Base

72

class Query < ActiveRecord::Base

73

class StatementInvalid < ::ActiveRecord::StatementInvalid

73

class StatementInvalid < ::ActiveRecord::StatementInvalid

74

end

74

end

75

76

belongs_to :project

76

belongs_to :project

77

belongs_to :user

77

belongs_to :user

78

serialize :filters

78

serialize :filters

79

serialize :column_names

79

serialize :column_names

80

serialize :sort_criteria, Array

80

serialize :sort_criteria, Array

81

82

attr_protected :project_id, :user_id

82

attr_protected :project_id, :user_id

83

84

validates_presence_of :name, :on => :save

84

validates_presence_of :name, :on => :save

85

validates_length_of :name, :maximum => 255

85

validates_length_of :name, :maximum => 255

86

87

@@operators = { "=" => :label_equals,

87

@@operators = { "=" => :label_equals,

88

"!" => :label_not_equals,

88

"!" => :label_not_equals,

89

"o" => :label_open_issues,

89

"o" => :label_open_issues,

90

"c" => :label_closed_issues,

90

"c" => :label_closed_issues,

91

"!*" => :label_none,

91

"!*" => :label_none,

92

"*" => :label_all,

92

"*" => :label_all,

93

">=" => :label_greater_or_equal,

93

">=" => :label_greater_or_equal,

94

"<=" => :label_less_or_equal,

94

"<=" => :label_less_or_equal,

95

"<t+" => :label_in_less_than,

95

"<t+" => :label_in_less_than,

96

">t+" => :label_in_more_than,

96

">t+" => :label_in_more_than,

97

"t+" => :label_in,

97

"t+" => :label_in,

98

"t" => :label_today,

98

"t" => :label_today,

99

"w" => :label_this_week,

99

"w" => :label_this_week,

100

">t-" => :label_less_than_ago,

100

">t-" => :label_less_than_ago,

101

"<t-" => :label_more_than_ago,

101

"<t-" => :label_more_than_ago,

102

"t-" => :label_ago,

102

"t-" => :label_ago,

103

"~" => :label_contains,

103

"~" => :label_contains,

104

"!~" => :label_not_contains }

104

"!~" => :label_not_contains }

105

106

cattr_reader :operators

106

cattr_reader :operators

107

108

@@operators_by_filter_type = { :list => [ "=", "!" ],

108

@@operators_by_filter_type = { :list => [ "=", "!" ],

109

:list_status => [ "o", "=", "!", "c", "*" ],

109

:list_status => [ "o", "=", "!", "c", "*" ],

110

:list_optional => [ "=", "!", "!*", "*" ],

110

:list_optional => [ "=", "!", "!*", "*" ],

111

:list_subprojects => [ "*", "!*", "=" ],

111

:list_subprojects => [ "*", "!*", "=" ],

112

:date => [ "<t+", ">t+", "t+", "t", "w", ">t-", "<t-", "t-" ],

112

:date => [ "<t+", ">t+", "t+", "t", "w", ">t-", "<t-", "t-" ],

113

:date_past => [ ">t-", "<t-", "t-", "t", "w" ],

113

:date_past => [ ">t-", "<t-", "t-", "t", "w" ],

114

:string => [ "=", "~", "!", "!~" ],

114

:string => [ "=", "~", "!", "!~" ],

115

:text => [ "~", "!~" ],

115

:text => [ "~", "!~" ],

116

:integer => [ "=", ">=", "<=", "!*", "*" ] }

116

:integer => [ "=", ">=", "<=", "!*", "*" ] }

117

118

cattr_reader :operators_by_filter_type

118

cattr_reader :operators_by_filter_type

119

120

@@available_columns = [

120

@@available_columns = [

121

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

121

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

122

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

122

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

123

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

123

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

124

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

124

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

125

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

125

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

126

QueryColumn.new(:author),

126

QueryColumn.new(:author),

127

QueryColumn.new(:assigned_to, :sortable => ["#{User.table_name}.lastname", "#{User.table_name}.firstname", "#{User.table_name}.id"], :groupable => true),

127

QueryColumn.new(:assigned_to, :sortable => ["#{User.table_name}.lastname", "#{User.table_name}.firstname", "#{User.table_name}.id"], :groupable => true),

128

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

128

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

129

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

129

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

130

QueryColumn.new(:fixed_version, :sortable => ["#{Version.table_name}.effective_date", "#{Version.table_name}.name"], :default_order => 'desc', :groupable => true),

130

QueryColumn.new(:fixed_version, :sortable => ["#{Version.table_name}.effective_date", "#{Version.table_name}.name"], :default_order => 'desc', :groupable => true),

131

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

131

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

132

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

132

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

133

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

133

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

134

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

134

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

135

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

135

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

136

]

136

]

137

cattr_reader :available_columns

137

cattr_reader :available_columns

138

139

def initialize(attributes = nil)

139

def initialize(attributes = nil)

140

super attributes

140

super attributes

141

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

141

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

142

end

142

end

143

144

def after_initialize

144

def after_initialize

145

# Store the fact that project is nil (used in #editable_by?)

145

# Store the fact that project is nil (used in #editable_by?)

146

@is_for_all = project.nil?

146

@is_for_all = project.nil?

147

end

147

end

148

149

def validate

149

def validate

150

filters.each_key do |field|

150

filters.each_key do |field|

151

errors.add label_for(field), :blank unless

151

errors.add label_for(field), :blank unless

152

# filter requires one or more values

152

# filter requires one or more values

153

(values_for(field) and !values_for(field).first.blank?) or

153

(values_for(field) and !values_for(field).first.blank?) or

154

# filter doesn't require any value

154

# filter doesn't require any value

155

["o", "c", "!*", "*", "t", "w"].include? operator_for(field)

155

["o", "c", "!*", "*", "t", "w"].include? operator_for(field)

156

end if filters

156

end if filters

157

end

157

end

158

159

def editable_by?(user)

159

def editable_by?(user)

160

return false unless user

160

return false unless user

161

# Admin can edit them all and regular users can edit their private queries

161

# Admin can edit them all and regular users can edit their private queries

162

return true if user.admin? || (!is_public && self.user_id == user.id)

162

return true if user.admin? || (!is_public && self.user_id == user.id)

163

# Members can not edit public queries that are for all project (only admin is allowed to)

163

# Members can not edit public queries that are for all project (only admin is allowed to)

164

is_public && !@is_for_all && user.allowed_to?(:manage_public_queries, project)

164

is_public && !@is_for_all && user.allowed_to?(:manage_public_queries, project)

165

end

165

end

166

167

def available_filters

167

def available_filters

168

return @available_filters if @available_filters

168

return @available_filters if @available_filters

169

170

trackers = project.nil? ? Tracker.find(:all, :order => 'position') : project.rolled_up_trackers

170

trackers = project.nil? ? Tracker.find(:all, :order => 'position') : project.rolled_up_trackers

171

172

@available_filters = { "status_id" => { :type => :list_status, :order => 1, :values => IssueStatus.find(:all, :order => 'position').collect{|s| [s.name, s.id.to_s] } },

172

@available_filters = { "status_id" => { :type => :list_status, :order => 1, :values => IssueStatus.find(:all, :order => 'position').collect{|s| [s.name, s.id.to_s] } },

173

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

173

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

174

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

174

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

175

"subject" => { :type => :text, :order => 8 },

175

"subject" => { :type => :text, :order => 8 },

176

"created_on" => { :type => :date_past, :order => 9 },

176

"created_on" => { :type => :date_past, :order => 9 },

177

"updated_on" => { :type => :date_past, :order => 10 },

177

"updated_on" => { :type => :date_past, :order => 10 },

178

"start_date" => { :type => :date, :order => 11 },

178

"start_date" => { :type => :date, :order => 11 },

179

"due_date" => { :type => :date, :order => 12 },

179

"due_date" => { :type => :date, :order => 12 },

180

"estimated_hours" => { :type => :integer, :order => 13 },

180

"estimated_hours" => { :type => :integer, :order => 13 },

181

"done_ratio" => { :type => :integer, :order => 14 }}

181

"done_ratio" => { :type => :integer, :order => 14 }}

182

183

user_values = []

183

user_values = []

184

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

184

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

185

if project

185

if project

186

user_values += project.users.sort.collect{|s| [s.name, s.id.to_s] }

186

user_values += project.users.sort.collect{|s| [s.name, s.id.to_s] }

187

else

187

else

188

# members of the user's projects

188

project_ids = User.current.projects.collect(&:id)

189

# OPTIMIZE: Is selecting from users per project (N+1)

189

if project_ids.any?

190

user_values += User.current.projects.collect(&:users).flatten.uniq.sort.collect{|s| [s.name, s.id.to_s] }

190

# members of the user's projects

191

user_values += User.active.find(:all, :conditions => ["#{User.table_name}.id IN (SELECT DISTINCT user_id FROM members WHERE project_id IN (?))", project_ids]).sort.collect{|s| [s.name, s.id.to_s] }

192

end

191

end

193

end

192

@available_filters["assigned_to_id"] = { :type => :list_optional, :order => 4, :values => user_values } unless user_values.empty?

194

@available_filters["assigned_to_id"] = { :type => :list_optional, :order => 4, :values => user_values } unless user_values.empty?

193

@available_filters["author_id"] = { :type => :list, :order => 5, :values => user_values } unless user_values.empty?

195

@available_filters["author_id"] = { :type => :list, :order => 5, :values => user_values } unless user_values.empty?

194

196

195

if User.current.logged?

197

if User.current.logged?

196

@available_filters["watcher_id"] = { :type => :list, :order => 15, :values => [["<< #{l(:label_me)} >>", "me"]] }

198

@available_filters["watcher_id"] = { :type => :list, :order => 15, :values => [["<< #{l(:label_me)} >>", "me"]] }

197

end

199

end

198

200

199

if project

201

if project

200

# project specific filters

202

# project specific filters

201

unless @project.issue_categories.empty?

203

unless @project.issue_categories.empty?

202

@available_filters["category_id"] = { :type => :list_optional, :order => 6, :values => @project.issue_categories.collect{|s| [s.name, s.id.to_s] } }

204

@available_filters["category_id"] = { :type => :list_optional, :order => 6, :values => @project.issue_categories.collect{|s| [s.name, s.id.to_s] } }

203

end

205

end

204

unless @project.shared_versions.empty?

206

unless @project.shared_versions.empty?

205

@available_filters["fixed_version_id"] = { :type => :list_optional, :order => 7, :values => @project.shared_versions.sort.collect{|s| ["#{s.project.name} - #{s.name}", s.id.to_s] } }

207

@available_filters["fixed_version_id"] = { :type => :list_optional, :order => 7, :values => @project.shared_versions.sort.collect{|s| ["#{s.project.name} - #{s.name}", s.id.to_s] } }

206

end

208

end

207

unless @project.descendants.active.empty?

209

unless @project.descendants.active.empty?

208

@available_filters["subproject_id"] = { :type => :list_subprojects, :order => 13, :values => @project.descendants.visible.collect{|s| [s.name, s.id.to_s] } }

210

@available_filters["subproject_id"] = { :type => :list_subprojects, :order => 13, :values => @project.descendants.visible.collect{|s| [s.name, s.id.to_s] } }

209

end

211

end

210

add_custom_fields_filters(@project.all_issue_custom_fields)

212

add_custom_fields_filters(@project.all_issue_custom_fields)

211

else

213

else

212

# global filters for cross project issue list

214

# global filters for cross project issue list

213

system_shared_versions = Version.visible.find_all_by_sharing('system')

215

system_shared_versions = Version.visible.find_all_by_sharing('system')

214

unless system_shared_versions.empty?

216

unless system_shared_versions.empty?

215

@available_filters["fixed_version_id"] = { :type => :list_optional, :order => 7, :values => system_shared_versions.sort.collect{|s| ["#{s.project.name} - #{s.name}", s.id.to_s] } }

217

@available_filters["fixed_version_id"] = { :type => :list_optional, :order => 7, :values => system_shared_versions.sort.collect{|s| ["#{s.project.name} - #{s.name}", s.id.to_s] } }

216

end

218

end

217

add_custom_fields_filters(IssueCustomField.find(:all, :conditions => {:is_filter => true, :is_for_all => true}))

219

add_custom_fields_filters(IssueCustomField.find(:all, :conditions => {:is_filter => true, :is_for_all => true}))

218

end

220

end

219

@available_filters

221

@available_filters

220

end

222

end

221

223

222

def add_filter(field, operator, values)

224

def add_filter(field, operator, values)

223

# values must be an array

225

# values must be an array

224

return unless values and values.is_a? Array # and !values.first.empty?

226

return unless values and values.is_a? Array # and !values.first.empty?

225

# check if field is defined as an available filter

227

# check if field is defined as an available filter

226

if available_filters.has_key? field

228

if available_filters.has_key? field

227

filter_options = available_filters[field]

229

filter_options = available_filters[field]

228

# check if operator is allowed for that filter

230

# check if operator is allowed for that filter

229

#if @@operators_by_filter_type[filter_options[:type]].include? operator

231

#if @@operators_by_filter_type[filter_options[:type]].include? operator

230

# allowed_values = values & ([""] + (filter_options[:values] || []).collect {|val| val[1]})

232

# allowed_values = values & ([""] + (filter_options[:values] || []).collect {|val| val[1]})

231

# filters[field] = {:operator => operator, :values => allowed_values } if (allowed_values.first and !allowed_values.first.empty?) or ["o", "c", "!*", "*", "t"].include? operator

233

# filters[field] = {:operator => operator, :values => allowed_values } if (allowed_values.first and !allowed_values.first.empty?) or ["o", "c", "!*", "*", "t"].include? operator

232

#end

234

#end

233

filters[field] = {:operator => operator, :values => values }

235

filters[field] = {:operator => operator, :values => values }

234

end

236

end

235

end

237

end

236

238

237

def add_short_filter(field, expression)

239

def add_short_filter(field, expression)

238

return unless expression

240

return unless expression

239

parms = expression.scan(/^(o|c|!\*|!|\*)?(.*)$/).first

241

parms = expression.scan(/^(o|c|!\*|!|\*)?(.*)$/).first

240

add_filter field, (parms[0] || "="), [parms[1] || ""]

242

add_filter field, (parms[0] || "="), [parms[1] || ""]

241

end

243

end

242

244

243

def has_filter?(field)

245

def has_filter?(field)

244

filters and filters[field]

246

filters and filters[field]

245

end

247

end

246

248

247

def operator_for(field)

249

def operator_for(field)

248

has_filter?(field) ? filters[field][:operator] : nil

250

has_filter?(field) ? filters[field][:operator] : nil

249

end

251

end

250

252

251

def values_for(field)

253

def values_for(field)

252

has_filter?(field) ? filters[field][:values] : nil

254

has_filter?(field) ? filters[field][:values] : nil

253

end

255

end

254

256

255

def label_for(field)

257

def label_for(field)

256

label = available_filters[field][:name] if available_filters.has_key?(field)

258

label = available_filters[field][:name] if available_filters.has_key?(field)

257

label ||= field.gsub(/\_id$/, "")

259

label ||= field.gsub(/\_id$/, "")

258

end

260

end

259

261

260

def available_columns

262

def available_columns

261

return @available_columns if @available_columns

263

return @available_columns if @available_columns

262

@available_columns = Query.available_columns

264

@available_columns = Query.available_columns

263

@available_columns += (project ?

265

@available_columns += (project ?

264

project.all_issue_custom_fields :

266

project.all_issue_custom_fields :

265

IssueCustomField.find(:all)

267

IssueCustomField.find(:all)

266

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

268

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

267

end

269

end

268

270

269

# Returns an array of columns that can be used to group the results

271

# Returns an array of columns that can be used to group the results

270

def groupable_columns

272

def groupable_columns

271

available_columns.select {|c| c.groupable}

273

available_columns.select {|c| c.groupable}

272

end

274

end

273

275

274

# Returns a Hash of columns and the key for sorting

276

# Returns a Hash of columns and the key for sorting

275

def sortable_columns

277

def sortable_columns

276

{'id' => "#{Issue.table_name}.id"}.merge(available_columns.inject({}) {|h, column|

278

{'id' => "#{Issue.table_name}.id"}.merge(available_columns.inject({}) {|h, column|

277

h[column.name.to_s] = column.sortable

279

h[column.name.to_s] = column.sortable

278

h

280

h

279

})

281

})

280

end

282

end

281

283

282

def columns

284

def columns

283

if has_default_columns?

285

if has_default_columns?

284

available_columns.select do |c|

286

available_columns.select do |c|

285

# Adds the project column by default for cross-project lists

287

# Adds the project column by default for cross-project lists

286

Setting.issue_list_default_columns.include?(c.name.to_s) || (c.name == :project && project.nil?)

288

Setting.issue_list_default_columns.include?(c.name.to_s) || (c.name == :project && project.nil?)

287

end

289

end

288

else

290

else

289

# preserve the column_names order

291

# preserve the column_names order

290

column_names.collect {|name| available_columns.find {|col| col.name == name}}.compact

292

column_names.collect {|name| available_columns.find {|col| col.name == name}}.compact

291

end

293

end

292

end

294

end

293

295

294

def column_names=(names)

296

def column_names=(names)

295

if names

297

if names

296

names = names.select {|n| n.is_a?(Symbol) || !n.blank? }

298

names = names.select {|n| n.is_a?(Symbol) || !n.blank? }

297

names = names.collect {|n| n.is_a?(Symbol) ? n : n.to_sym }

299

names = names.collect {|n| n.is_a?(Symbol) ? n : n.to_sym }

298

# Set column_names to nil if default columns

300

# Set column_names to nil if default columns

299

if names.map(&:to_s) == Setting.issue_list_default_columns

301

if names.map(&:to_s) == Setting.issue_list_default_columns

300

names = nil

302

names = nil

301

end

303

end

302

end

304

end

303

write_attribute(:column_names, names)

305

write_attribute(:column_names, names)

304

end

306

end

305

307

306

def has_column?(column)

308

def has_column?(column)

307

column_names && column_names.include?(column.name)

309

column_names && column_names.include?(column.name)

308

end

310

end

309

311

310

def has_default_columns?

312

def has_default_columns?

311

column_names.nil? || column_names.empty?

313

column_names.nil? || column_names.empty?

312

end

314

end

313

315

314

def sort_criteria=(arg)

316

def sort_criteria=(arg)

315

c = []

317

c = []

316

if arg.is_a?(Hash)

318

if arg.is_a?(Hash)

317

arg = arg.keys.sort.collect {|k| arg[k]}

319

arg = arg.keys.sort.collect {|k| arg[k]}

318

end

320

end

319

c = arg.select {|k,o| !k.to_s.blank?}.slice(0,3).collect {|k,o| [k.to_s, o == 'desc' ? o : 'asc']}

321

c = arg.select {|k,o| !k.to_s.blank?}.slice(0,3).collect {|k,o| [k.to_s, o == 'desc' ? o : 'asc']}

320

write_attribute(:sort_criteria, c)

322

write_attribute(:sort_criteria, c)

321

end

323

end

322

324

323

def sort_criteria

325

def sort_criteria

324

read_attribute(:sort_criteria) || []

326

read_attribute(:sort_criteria) || []

325

end

327

end

326

328

327

def sort_criteria_key(arg)

329

def sort_criteria_key(arg)

328

sort_criteria && sort_criteria[arg] && sort_criteria[arg].first

330

sort_criteria && sort_criteria[arg] && sort_criteria[arg].first

329

end

331

end

330

332

331

def sort_criteria_order(arg)

333

def sort_criteria_order(arg)

332

sort_criteria && sort_criteria[arg] && sort_criteria[arg].last

334

sort_criteria && sort_criteria[arg] && sort_criteria[arg].last

333

end

335

end

334

336

335

# Returns the SQL sort order that should be prepended for grouping

337

# Returns the SQL sort order that should be prepended for grouping

336

def group_by_sort_order

338

def group_by_sort_order

337

if grouped? && (column = group_by_column)

339

if grouped? && (column = group_by_column)

338

column.sortable.is_a?(Array) ?

340

column.sortable.is_a?(Array) ?

339

column.sortable.collect {|s| "#{s} #{column.default_order}"}.join(',') :

341

column.sortable.collect {|s| "#{s} #{column.default_order}"}.join(',') :

340

"#{column.sortable} #{column.default_order}"

342

"#{column.sortable} #{column.default_order}"

341

end

343

end

342

end

344

end

343

345

344

# Returns true if the query is a grouped query

346

# Returns true if the query is a grouped query

345

def grouped?

347

def grouped?

346

!group_by.blank?

348

!group_by.blank?

347

end

349

end

348

350

349

def group_by_column

351

def group_by_column

350

groupable_columns.detect {|c| c.name.to_s == group_by}

352

groupable_columns.detect {|c| c.name.to_s == group_by}

351

end

353

end

352

354

353

def group_by_statement

355

def group_by_statement

354

group_by_column.groupable

356

group_by_column.groupable

355

end

357

end

356

358

357

def project_statement

359

def project_statement

358

project_clauses = []

360

project_clauses = []

359

if project && !@project.descendants.active.empty?

361

if project && !@project.descendants.active.empty?

360

ids = [project.id]

362

ids = [project.id]

361

if has_filter?("subproject_id")

363

if has_filter?("subproject_id")

362

case operator_for("subproject_id")

364

case operator_for("subproject_id")

363

when '='

365

when '='

364

# include the selected subprojects

366

# include the selected subprojects

365

ids += values_for("subproject_id").each(&:to_i)

367

ids += values_for("subproject_id").each(&:to_i)

366

when '!*'

368

when '!*'

367

# main project only

369

# main project only

368

else

370

else

369

# all subprojects

371

# all subprojects

370

ids += project.descendants.collect(&:id)

372

ids += project.descendants.collect(&:id)

371

end

373

end

372

elsif Setting.display_subprojects_issues?

374

elsif Setting.display_subprojects_issues?

373

ids += project.descendants.collect(&:id)

375

ids += project.descendants.collect(&:id)

374

end

376

end

375

project_clauses << "#{Project.table_name}.id IN (%s)" % ids.join(',')

377

project_clauses << "#{Project.table_name}.id IN (%s)" % ids.join(',')

376

elsif project

378

elsif project

377

project_clauses << "#{Project.table_name}.id = %d" % project.id

379

project_clauses << "#{Project.table_name}.id = %d" % project.id

378

end

380

end

379

project_clauses << Project.allowed_to_condition(User.current, :view_issues)

381

project_clauses << Project.allowed_to_condition(User.current, :view_issues)

380

project_clauses.join(' AND ')

382

project_clauses.join(' AND ')

381

end

383

end

382

384

383

def statement

385

def statement

384

# filters clauses

386

# filters clauses

385

filters_clauses = []

387

filters_clauses = []

386

filters.each_key do |field|

388

filters.each_key do |field|

387

next if field == "subproject_id"

389

next if field == "subproject_id"

388

v = values_for(field).clone

390

v = values_for(field).clone

389

next unless v and !v.empty?

391

next unless v and !v.empty?

390

operator = operator_for(field)

392

operator = operator_for(field)

391

393

392

# "me" value subsitution

394

# "me" value subsitution

393

if %w(assigned_to_id author_id watcher_id).include?(field)

395

if %w(assigned_to_id author_id watcher_id).include?(field)

394

v.push(User.current.logged? ? User.current.id.to_s : "0") if v.delete("me")

396

v.push(User.current.logged? ? User.current.id.to_s : "0") if v.delete("me")

395

end

397

end

396

398

397

sql = ''

399

sql = ''

398

if field =~ /^cf_(\d+)$/

400

if field =~ /^cf_(\d+)$/

399

# custom field

401

# custom field

400

db_table = CustomValue.table_name

402

db_table = CustomValue.table_name

401

db_field = 'value'

403

db_field = 'value'

402

is_custom_filter = true

404

is_custom_filter = true

403

sql << "#{Issue.table_name}.id IN (SELECT #{Issue.table_name}.id FROM #{Issue.table_name} LEFT OUTER JOIN #{db_table} ON #{db_table}.customized_type='Issue' AND #{db_table}.customized_id=#{Issue.table_name}.id AND #{db_table}.custom_field_id=#{$1} WHERE "

405

sql << "#{Issue.table_name}.id IN (SELECT #{Issue.table_name}.id FROM #{Issue.table_name} LEFT OUTER JOIN #{db_table} ON #{db_table}.customized_type='Issue' AND #{db_table}.customized_id=#{Issue.table_name}.id AND #{db_table}.custom_field_id=#{$1} WHERE "

404

sql << sql_for_field(field, operator, v, db_table, db_field, true) + ')'

406

sql << sql_for_field(field, operator, v, db_table, db_field, true) + ')'

405

elsif field == 'watcher_id'

407

elsif field == 'watcher_id'

406

db_table = Watcher.table_name

408

db_table = Watcher.table_name

407

db_field = 'user_id'

409

db_field = 'user_id'

408

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

410

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

409

sql << sql_for_field(field, '=', v, db_table, db_field) + ')'

411

sql << sql_for_field(field, '=', v, db_table, db_field) + ')'

410

else

412

else

411

# regular field

413

# regular field

412

db_table = Issue.table_name

414

db_table = Issue.table_name

413

db_field = field

415

db_field = field

414

sql << '(' + sql_for_field(field, operator, v, db_table, db_field) + ')'

416

sql << '(' + sql_for_field(field, operator, v, db_table, db_field) + ')'

415

end

417

end

416

filters_clauses << sql

418

filters_clauses << sql

417

419

418

end if filters and valid?

420

end if filters and valid?

419

421

420

(filters_clauses << project_statement).join(' AND ')

422

(filters_clauses << project_statement).join(' AND ')

421

end

423

end

422

424

423

# Returns the issue count

425

# Returns the issue count

424

def issue_count

426

def issue_count

425

Issue.count(:include => [:status, :project], :conditions => statement)

427

Issue.count(:include => [:status, :project], :conditions => statement)

426

rescue ::ActiveRecord::StatementInvalid => e

428

rescue ::ActiveRecord::StatementInvalid => e

427

raise StatementInvalid.new(e.message)

429

raise StatementInvalid.new(e.message)

428

end

430

end

429

431

430

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

432

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

431

def issue_count_by_group

433

def issue_count_by_group

432

r = nil

434

r = nil

433

if grouped?

435

if grouped?

434

begin

436

begin

435

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

437

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

436

r = Issue.count(:group => group_by_statement, :include => [:status, :project], :conditions => statement)

438

r = Issue.count(:group => group_by_statement, :include => [:status, :project], :conditions => statement)

437

rescue ActiveRecord::RecordNotFound

439

rescue ActiveRecord::RecordNotFound

438

r = {nil => issue_count}

440

r = {nil => issue_count}

439

end

441

end

440

c = group_by_column

442

c = group_by_column

441

if c.is_a?(QueryCustomFieldColumn)

443

if c.is_a?(QueryCustomFieldColumn)

442

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

444

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

443

end

445

end

444

end

446

end

445

r

447

r

446

rescue ::ActiveRecord::StatementInvalid => e

448

rescue ::ActiveRecord::StatementInvalid => e

447

raise StatementInvalid.new(e.message)

449

raise StatementInvalid.new(e.message)

448

end

450

end

449

451

450

# Returns the issues

452

# Returns the issues

451

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

453

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

452

def issues(options={})

454

def issues(options={})

453

order_option = [group_by_sort_order, options[:order]].reject {|s| s.blank?}.join(',')

455

order_option = [group_by_sort_order, options[:order]].reject {|s| s.blank?}.join(',')

454

order_option = nil if order_option.blank?

456

order_option = nil if order_option.blank?

455

457

456

Issue.find :all, :include => ([:status, :project] + (options[:include] || [])).uniq,

458

Issue.find :all, :include => ([:status, :project] + (options[:include] || [])).uniq,

457

:conditions => Query.merge_conditions(statement, options[:conditions]),

459

:conditions => Query.merge_conditions(statement, options[:conditions]),

458

:order => order_option,

460

:order => order_option,

459

:limit => options[:limit],

461

:limit => options[:limit],

460

:offset => options[:offset]

462

:offset => options[:offset]

461

rescue ::ActiveRecord::StatementInvalid => e

463

rescue ::ActiveRecord::StatementInvalid => e

462

raise StatementInvalid.new(e.message)

464

raise StatementInvalid.new(e.message)

463

end

465

end

464

466

465

# Returns the journals

467

# Returns the journals

466

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

468

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

467

def journals(options={})

469

def journals(options={})

468

Journal.find :all, :include => [:details, :user, {:issue => [:project, :author, :tracker, :status]}],

470

Journal.find :all, :include => [:details, :user, {:issue => [:project, :author, :tracker, :status]}],

469

:conditions => statement,

471

:conditions => statement,

470

:order => options[:order],

472

:order => options[:order],

471

:limit => options[:limit],

473

:limit => options[:limit],

472

:offset => options[:offset]

474

:offset => options[:offset]

473

rescue ::ActiveRecord::StatementInvalid => e

475

rescue ::ActiveRecord::StatementInvalid => e

474

raise StatementInvalid.new(e.message)

476

raise StatementInvalid.new(e.message)

475

end

477

end

476

478

477

# Returns the versions

479

# Returns the versions

478

# Valid options are :conditions

480

# Valid options are :conditions

479

def versions(options={})

481

def versions(options={})

480

Version.find :all, :include => :project,

482

Version.find :all, :include => :project,

481

:conditions => Query.merge_conditions(project_statement, options[:conditions])

483

:conditions => Query.merge_conditions(project_statement, options[:conditions])

482

rescue ::ActiveRecord::StatementInvalid => e

484

rescue ::ActiveRecord::StatementInvalid => e

483

raise StatementInvalid.new(e.message)

485

raise StatementInvalid.new(e.message)

484

end

486

end

485

487

486

private

488

private

487

489

488

# Helper method to generate the WHERE sql for a +field+, +operator+ and a +value+

490

# Helper method to generate the WHERE sql for a +field+, +operator+ and a +value+

489

def sql_for_field(field, operator, value, db_table, db_field, is_custom_filter=false)

491

def sql_for_field(field, operator, value, db_table, db_field, is_custom_filter=false)

490

sql = ''

492

sql = ''

491

case operator

493

case operator

492

when "="

494

when "="

493

sql = "#{db_table}.#{db_field} IN (" + value.collect{|val| "'#{connection.quote_string(val)}'"}.join(",") + ")"

495

sql = "#{db_table}.#{db_field} IN (" + value.collect{|val| "'#{connection.quote_string(val)}'"}.join(",") + ")"

494

when "!"

496

when "!"

495

sql = "(#{db_table}.#{db_field} IS NULL OR #{db_table}.#{db_field} NOT IN (" + value.collect{|val| "'#{connection.quote_string(val)}'"}.join(",") + "))"

497

sql = "(#{db_table}.#{db_field} IS NULL OR #{db_table}.#{db_field} NOT IN (" + value.collect{|val| "'#{connection.quote_string(val)}'"}.join(",") + "))"

496

when "!*"

498

when "!*"

497

sql = "#{db_table}.#{db_field} IS NULL"

499

sql = "#{db_table}.#{db_field} IS NULL"

498

sql << " OR #{db_table}.#{db_field} = ''" if is_custom_filter

500

sql << " OR #{db_table}.#{db_field} = ''" if is_custom_filter

499

when "*"

501

when "*"

500

sql = "#{db_table}.#{db_field} IS NOT NULL"

502

sql = "#{db_table}.#{db_field} IS NOT NULL"

501

sql << " AND #{db_table}.#{db_field} <> ''" if is_custom_filter

503

sql << " AND #{db_table}.#{db_field} <> ''" if is_custom_filter

502

when ">="

504

when ">="

503

sql = "#{db_table}.#{db_field} >= #{value.first.to_i}"

505

sql = "#{db_table}.#{db_field} >= #{value.first.to_i}"

504

when "<="

506

when "<="

505

sql = "#{db_table}.#{db_field} <= #{value.first.to_i}"

507

sql = "#{db_table}.#{db_field} <= #{value.first.to_i}"

506

when "o"

508

when "o"

507

sql = "#{IssueStatus.table_name}.is_closed=#{connection.quoted_false}" if field == "status_id"

509

sql = "#{IssueStatus.table_name}.is_closed=#{connection.quoted_false}" if field == "status_id"

508

when "c"

510

when "c"

509

sql = "#{IssueStatus.table_name}.is_closed=#{connection.quoted_true}" if field == "status_id"

511

sql = "#{IssueStatus.table_name}.is_closed=#{connection.quoted_true}" if field == "status_id"

510

when ">t-"

512

when ">t-"

511

sql = date_range_clause(db_table, db_field, - value.first.to_i, 0)

513

sql = date_range_clause(db_table, db_field, - value.first.to_i, 0)

512

when "<t-"

514

when "<t-"

513

sql = date_range_clause(db_table, db_field, nil, - value.first.to_i)

515

sql = date_range_clause(db_table, db_field, nil, - value.first.to_i)

514

when "t-"

516

when "t-"

515

sql = date_range_clause(db_table, db_field, - value.first.to_i, - value.first.to_i)

517

sql = date_range_clause(db_table, db_field, - value.first.to_i, - value.first.to_i)

516

when ">t+"

518

when ">t+"

517

sql = date_range_clause(db_table, db_field, value.first.to_i, nil)

519

sql = date_range_clause(db_table, db_field, value.first.to_i, nil)

518

when "<t+"

520

when "<t+"

519

sql = date_range_clause(db_table, db_field, 0, value.first.to_i)

521

sql = date_range_clause(db_table, db_field, 0, value.first.to_i)

520

when "t+"

522

when "t+"

521

sql = date_range_clause(db_table, db_field, value.first.to_i, value.first.to_i)

523

sql = date_range_clause(db_table, db_field, value.first.to_i, value.first.to_i)

522

when "t"

524

when "t"

523

sql = date_range_clause(db_table, db_field, 0, 0)

525

sql = date_range_clause(db_table, db_field, 0, 0)

524

when "w"

526

when "w"

525

from = l(:general_first_day_of_week) == '7' ?

527

from = l(:general_first_day_of_week) == '7' ?

526

# week starts on sunday

528

# week starts on sunday

527

((Date.today.cwday == 7) ? Time.now.at_beginning_of_day : Time.now.at_beginning_of_week - 1.day) :

529

((Date.today.cwday == 7) ? Time.now.at_beginning_of_day : Time.now.at_beginning_of_week - 1.day) :

528

# week starts on monday (Rails default)

530

# week starts on monday (Rails default)

529

Time.now.at_beginning_of_week

531

Time.now.at_beginning_of_week

530

sql = "#{db_table}.#{db_field} BETWEEN '%s' AND '%s'" % [connection.quoted_date(from), connection.quoted_date(from + 7.days)]

532

sql = "#{db_table}.#{db_field} BETWEEN '%s' AND '%s'" % [connection.quoted_date(from), connection.quoted_date(from + 7.days)]

531

when "~"

533

when "~"

532

sql = "LOWER(#{db_table}.#{db_field}) LIKE '%#{connection.quote_string(value.first.to_s.downcase)}%'"

534

sql = "LOWER(#{db_table}.#{db_field}) LIKE '%#{connection.quote_string(value.first.to_s.downcase)}%'"

533

when "!~"

535

when "!~"

534

sql = "LOWER(#{db_table}.#{db_field}) NOT LIKE '%#{connection.quote_string(value.first.to_s.downcase)}%'"

536

sql = "LOWER(#{db_table}.#{db_field}) NOT LIKE '%#{connection.quote_string(value.first.to_s.downcase)}%'"

535

end

537

end

536

538

537

return sql

539

return sql

538

end

540

end

539

541

540

def add_custom_fields_filters(custom_fields)

542

def add_custom_fields_filters(custom_fields)

541

@available_filters ||= {}

543

@available_filters ||= {}

542

544

543

custom_fields.select(&:is_filter?).each do |field|

545

custom_fields.select(&:is_filter?).each do |field|

544

case field.field_format

546

case field.field_format

545

when "text"

547

when "text"

546

options = { :type => :text, :order => 20 }

548

options = { :type => :text, :order => 20 }

547

when "list"

549

when "list"

548

options = { :type => :list_optional, :values => field.possible_values, :order => 20}

550

options = { :type => :list_optional, :values => field.possible_values, :order => 20}

549

when "date"

551

when "date"

550

options = { :type => :date, :order => 20 }

552

options = { :type => :date, :order => 20 }

551

when "bool"

553

when "bool"

552

options = { :type => :list, :values => [[l(:general_text_yes), "1"], [l(:general_text_no), "0"]], :order => 20 }

554

options = { :type => :list, :values => [[l(:general_text_yes), "1"], [l(:general_text_no), "0"]], :order => 20 }

553

else

555

else

554

options = { :type => :string, :order => 20 }

556

options = { :type => :string, :order => 20 }

555

end

557

end

556

@available_filters["cf_#{field.id}"] = options.merge({ :name => field.name })

558

@available_filters["cf_#{field.id}"] = options.merge({ :name => field.name })

557

end

559

end

558

end

560

end

559

561

560

# Returns a SQL clause for a date or datetime field.

562

# Returns a SQL clause for a date or datetime field.

561

def date_range_clause(table, field, from, to)

563

def date_range_clause(table, field, from, to)

562

s = []

564

s = []

563

if from

565

if from

564

s << ("#{table}.#{field} > '%s'" % [connection.quoted_date((Date.yesterday + from).to_time.end_of_day)])

566

s << ("#{table}.#{field} > '%s'" % [connection.quoted_date((Date.yesterday + from).to_time.end_of_day)])

565

end

567

end

566

if to

568

if to

567

s << ("#{table}.#{field} <= '%s'" % [connection.quoted_date((Date.today + to).to_time.end_of_day)])

569

s << ("#{table}.#{field} <= '%s'" % [connection.quoted_date((Date.today + to).to_time.end_of_day)])

568

end

570

end

569

s.join(' AND ')

571

s.join(' AND ')

570

end

572

end

571

end

573

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-2008  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 QueryColumn
               attr_accessor :name, :sortable, :groupable, :default_order
               include Redmine::I18n
               def initialize(name, options={})
                 self.name = name
                 self.sortable = options[:sortable]
                 self.groupable = options[:groupable] || false
                 if groupable == true
                   self.groupable = name.to_s
                 end
                 self.default_order = options[:default_order]
               end
               def caption
                 l("field_#{name}")
               end
               # Returns true if the column is sortable, otherwise false
               def sortable?
                 !sortable.nil?
               end
               def value(issue)
                 issue.send name
               end
             end
             class QueryCustomFieldColumn < QueryColumn
               def initialize(custom_field)
                 self.name = "cf_#{custom_field.id}".to_sym
                 self.sortable = custom_field.order_statement || false
                 if %w(list date bool int).include?(custom_field.field_format)
                   self.groupable = custom_field.order_statement
                 end
                 self.groupable ||= false
                 @cf = custom_field
               end
               def caption
                 @cf.name
               end
               def custom_field
                 @cf
               end
               def value(issue)
                 cv = issue.custom_values.detect {|v| v.custom_field_id == @cf.id}
                 cv && @cf.cast_value(cv.value)
               end
             end
             class Query < ActiveRecord::Base
               class StatementInvalid < ::ActiveRecord::StatementInvalid
               end
               belongs_to :project
               belongs_to :user
               serialize :filters
               serialize :column_names
               serialize :sort_criteria, Array
               attr_protected :project_id, :user_id
               validates_presence_of :name, :on => :save
               validates_length_of :name, :maximum => 255
               @@operators = { "="   => :label_equals,
                               "!"   => :label_not_equals,
                               "o"   => :label_open_issues,
                               "c"   => :label_closed_issues,
                               "!*"  => :label_none,
                               "*"   => :label_all,
                               ">="  => :label_greater_or_equal,
                               "<="  => :label_less_or_equal,
                               "<t+" => :label_in_less_than,
                               ">t+" => :label_in_more_than,
                               "t+"  => :label_in,
                               "t"   => :label_today,
                               "w"   => :label_this_week,
                               ">t-" => :label_less_than_ago,
                               "<t-" => :label_more_than_ago,
                               "t-"  => :label_ago,
                               "~"   => :label_contains,
                               "!~"  => :label_not_contains }
               cattr_reader :operators
               @@operators_by_filter_type = { :list => [ "=", "!" ],
                                              :list_status => [ "o", "=", "!", "c", "*" ],
                                              :list_optional => [ "=", "!", "!*", "*" ],
                                              :list_subprojects => [ "*", "!*", "=" ],
                                              :date => [ "<t+", ">t+", "t+", "t", "w", ">t-", "<t-", "t-" ],
                                              :date_past => [ ">t-", "<t-", "t-", "t", "w" ],
                                              :string => [ "=", "~", "!", "!~" ],
                                              :text => [  "~", "!~" ],
                                              :integer => [ "=", ">=", "<=", "!*", "*" ] }
               cattr_reader :operators_by_filter_type
               @@available_columns = [
                 QueryColumn.new(:project, :sortable => "#{Project.table_name}.name", :groupable => true),
                 QueryColumn.new(:tracker, :sortable => "#{Tracker.table_name}.position", :groupable => true),
                 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),
                 QueryColumn.new(:assigned_to, :sortable => ["#{User.table_name}.lastname", "#{User.table_name}.firstname", "#{User.table_name}.id"], :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 => ["#{Version.table_name}.effective_date", "#{Version.table_name}.name"], :default_order => 'desc', :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(:done_ratio, :sortable => "#{Issue.table_name}.done_ratio", :groupable => true),
                 QueryColumn.new(:created_on, :sortable => "#{Issue.table_name}.created_on", :default_order => 'desc'),
               ]
               cattr_reader :available_columns
               def initialize(attributes = nil)
                 super attributes
                 self.filters ||= { 'status_id' => {:operator => "o", :values => [""]} }
               end
               def after_initialize
                 # Store the fact that project is nil (used in #editable_by?)
                 @is_for_all = project.nil?
               end
               def validate
                 filters.each_key do |field|
                   errors.add label_for(field), :blank unless
                       # filter requires one or more values
                       (values_for(field) and !values_for(field).first.blank?) or
                       # filter doesn't require any value
                       ["o", "c", "!*", "*", "t", "w"].include? operator_for(field)
                 end if filters
               end
               def editable_by?(user)
                 return false unless user
                 # Admin can edit them all and regular users can edit their private queries
                 return true if user.admin? || (!is_public && self.user_id == user.id)
                 # Members can not edit public queries that are for all project (only admin is allowed to)
                 is_public && !@is_for_all && user.allowed_to?(:manage_public_queries, project)
               end
               def available_filters
                 return @available_filters if @available_filters
                 trackers = project.nil? ? Tracker.find(:all, :order => 'position') : project.rolled_up_trackers
                 @available_filters = { "status_id" => { :type => :list_status, :order => 1, :values => IssueStatus.find(:all, :order => 'position').collect{|s| [s.name, s.id.to_s] } },
                                        "tracker_id" => { :type => :list, :order => 2, :values => trackers.collect{|s| [s.name, s.id.to_s] } },
                                        "priority_id" => { :type => :list, :order => 3, :values => IssuePriority.all.collect{|s| [s.name, s.id.to_s] } },
                                        "subject" => { :type => :text, :order => 8 },
                                        "created_on" => { :type => :date_past, :order => 9 },
                                        "updated_on" => { :type => :date_past, :order => 10 },
                                        "start_date" => { :type => :date, :order => 11 },
                                        "due_date" => { :type => :date, :order => 12 },
                                        "estimated_hours" => { :type => :integer, :order => 13 },
                                        "done_ratio" =>  { :type => :integer, :order => 14 }}
                 user_values = []
                 user_values << ["<< #{l(:label_me)} >>", "me"] if User.current.logged?
                 if project
                   user_values += project.users.sort.collect{|s| [s.name, s.id.to_s] }
                 else
-                  # members of the user's projects
+                  project_ids = User.current.projects.collect(&:id)
-                  # OPTIMIZE: Is selecting from users per project (N+1)
+                  if project_ids.any?
-                  user_values += User.current.projects.collect(&:users).flatten.uniq.sort.collect{|s| [s.name, s.id.to_s] }
+                    # members of the user's projects
+                    user_values += User.active.find(:all, :conditions => ["#{User.table_name}.id IN (SELECT DISTINCT user_id FROM members WHERE project_id IN (?))", project_ids]).sort.collect{|s| [s.name, s.id.to_s] }
+                  end
                 end
                 @available_filters["assigned_to_id"] = { :type => :list_optional, :order => 4, :values => user_values } unless user_values.empty?
                 @available_filters["author_id"] = { :type => :list, :order => 5, :values => user_values } unless user_values.empty?
                 if User.current.logged?
                   @available_filters["watcher_id"] = { :type => :list, :order => 15, :values => [["<< #{l(:label_me)} >>", "me"]] }
                 end
                 if project
                   # project specific filters
                   unless @project.issue_categories.empty?
                     @available_filters["category_id"] = { :type => :list_optional, :order => 6, :values => @project.issue_categories.collect{|s| [s.name, s.id.to_s] } }
                   end
                   unless @project.shared_versions.empty?
                     @available_filters["fixed_version_id"] = { :type => :list_optional, :order => 7, :values => @project.shared_versions.sort.collect{|s| ["#{s.project.name} - #{s.name}", s.id.to_s] } }
                   end
                   unless @project.descendants.active.empty?
                     @available_filters["subproject_id"] = { :type => :list_subprojects, :order => 13, :values => @project.descendants.visible.collect{|s| [s.name, s.id.to_s] } }
                   end
                   add_custom_fields_filters(@project.all_issue_custom_fields)
                 else
                   # global filters for cross project issue list
                   system_shared_versions = Version.visible.find_all_by_sharing('system')
                   unless system_shared_versions.empty?
                     @available_filters["fixed_version_id"] = { :type => :list_optional, :order => 7, :values => system_shared_versions.sort.collect{|s| ["#{s.project.name} - #{s.name}", s.id.to_s] } }
                   end
                   add_custom_fields_filters(IssueCustomField.find(:all, :conditions => {:is_filter => true, :is_for_all => true}))
                 end
                 @available_filters
               end
               def add_filter(field, operator, values)
                 # values must be an array
                 return unless values and values.is_a? Array # and !values.first.empty?
                 # check if field is defined as an available filter
                 if available_filters.has_key? field
                   filter_options = available_filters[field]
                   # check if operator is allowed for that filter
                   #if @@operators_by_filter_type[filter_options[:type]].include? operator
                   #  allowed_values = values & ([""] + (filter_options[:values] || []).collect {|val| val[1]})
                   #  filters[field] = {:operator => operator, :values => allowed_values } if (allowed_values.first and !allowed_values.first.empty?) or ["o", "c", "!*", "*", "t"].include? operator
                   #end
                   filters[field] = {:operator => operator, :values => values }
                 end
               end
               def add_short_filter(field, expression)
                 return unless expression
                 parms = expression.scan(/^(o|c|!\*|!|\*)?(.*)$/).first
                 add_filter field, (parms[0] || "="), [parms[1] || ""]
               end
               def has_filter?(field)
                 filters and filters[field]
               end
               def operator_for(field)
                 has_filter?(field) ? filters[field][:operator] : nil
               end
               def values_for(field)
                 has_filter?(field) ? filters[field][:values] : nil
               end
               def label_for(field)
                 label = available_filters[field][:name] if available_filters.has_key?(field)
                 label ||= field.gsub(/\_id$/, "")
               end
               def available_columns
                 return @available_columns if @available_columns
                 @available_columns = Query.available_columns
                 @available_columns += (project ?
                                         project.all_issue_custom_fields :
                                         IssueCustomField.find(:all)
                                        ).collect {|cf| QueryCustomFieldColumn.new(cf) }
               end
               # Returns an array of columns that can be used to group the results
               def groupable_columns
                 available_columns.select {|c| c.groupable}
               end
               # Returns a Hash of columns and the key for sorting
               def sortable_columns
                 {'id' => "#{Issue.table_name}.id"}.merge(available_columns.inject({}) {|h, column|
                                                            h[column.name.to_s] = column.sortable
                                                            h
                                                          })
               end
               def columns
                 if has_default_columns?
                   available_columns.select do |c|
                     # Adds the project column by default for cross-project lists
                     Setting.issue_list_default_columns.include?(c.name.to_s) || (c.name == :project && project.nil?)
                   end
                 else
                   # preserve the column_names order
                   column_names.collect {|name| available_columns.find {|col| col.name == name}}.compact
                 end
               end
               def column_names=(names)
                 if names
                   names = names.select {|n| n.is_a?(Symbol) || !n.blank? }
                   names = names.collect {|n| n.is_a?(Symbol) ? n : n.to_sym }
                   # Set column_names to nil if default columns
                   if names.map(&:to_s) == Setting.issue_list_default_columns
                     names = nil
                   end
                 end
                 write_attribute(:column_names, names)
               end
               def has_column?(column)
                 column_names && column_names.include?(column.name)
               end
               def has_default_columns?
                 column_names.nil? || column_names.empty?
               end
               def sort_criteria=(arg)
                 c = []
                 if arg.is_a?(Hash)
                   arg = arg.keys.sort.collect {|k| arg[k]}
                 end
                 c = arg.select {|k,o| !k.to_s.blank?}.slice(0,3).collect {|k,o| [k.to_s, o == 'desc' ? o : 'asc']}
                 write_attribute(:sort_criteria, c)
               end
               def sort_criteria
                 read_attribute(:sort_criteria) || []
               end
               def sort_criteria_key(arg)
                 sort_criteria && sort_criteria[arg] && sort_criteria[arg].first
               end
               def sort_criteria_order(arg)
                 sort_criteria && sort_criteria[arg] && sort_criteria[arg].last
               end
               # Returns the SQL sort order that should be prepended for grouping
               def group_by_sort_order
                 if grouped? && (column = group_by_column)
                   column.sortable.is_a?(Array) ?
                     column.sortable.collect {|s| "#{s} #{column.default_order}"}.join(',') :
                     "#{column.sortable} #{column.default_order}"
                 end
               end
               # Returns true if the query is a grouped query
               def grouped?
                 !group_by.blank?
               end
               def group_by_column
                 groupable_columns.detect {|c| c.name.to_s == group_by}
               end
               def group_by_statement
                 group_by_column.groupable
               end
               def project_statement
                 project_clauses = []
                 if project && !@project.descendants.active.empty?
                   ids = [project.id]
                   if has_filter?("subproject_id")
                     case operator_for("subproject_id")
                     when '='
                       # include the selected subprojects
                       ids += values_for("subproject_id").each(&:to_i)
                     when '!*'
                       # main project only
                     else
                       # all subprojects
                       ids += project.descendants.collect(&:id)
                     end
                   elsif Setting.display_subprojects_issues?
                     ids += project.descendants.collect(&:id)
                   end
                   project_clauses << "#{Project.table_name}.id IN (%s)" % ids.join(',')
                 elsif project
                   project_clauses << "#{Project.table_name}.id = %d" % project.id
                 end
                 project_clauses <<  Project.allowed_to_condition(User.current, :view_issues)
                 project_clauses.join(' AND ')
               end
               def statement
                 # filters clauses
                 filters_clauses = []
                 filters.each_key do |field|
                   next if field == "subproject_id"
                   v = values_for(field).clone
                   next unless v and !v.empty?
                   operator = operator_for(field)
                   # "me" value subsitution
                   if %w(assigned_to_id author_id watcher_id).include?(field)
                     v.push(User.current.logged? ? User.current.id.to_s : "0") if v.delete("me")
                   end
                   sql = ''
                   if field =~ /^cf_(\d+)$/
                     # custom field
                     db_table = CustomValue.table_name
                     db_field = 'value'
                     is_custom_filter = true
                     sql << "#{Issue.table_name}.id IN (SELECT #{Issue.table_name}.id FROM #{Issue.table_name} LEFT OUTER JOIN #{db_table} ON #{db_table}.customized_type='Issue' AND #{db_table}.customized_id=#{Issue.table_name}.id AND #{db_table}.custom_field_id=#{$1} WHERE "
                     sql << sql_for_field(field, operator, v, db_table, db_field, true) + ')'
                   elsif field == 'watcher_id'
                     db_table = Watcher.table_name
                     db_field = 'user_id'
                     sql << "#{Issue.table_name}.id #{ operator == '=' ? 'IN' : 'NOT IN' } (SELECT #{db_table}.watchable_id FROM #{db_table} WHERE #{db_table}.watchable_type='Issue' AND "
                     sql << sql_for_field(field, '=', v, db_table, db_field) + ')'
                   else
                     # regular field
                     db_table = Issue.table_name
                     db_field = field
                     sql << '(' + sql_for_field(field, operator, v, db_table, db_field) + ')'
                   end
                   filters_clauses << sql
                 end if filters and valid?
                 (filters_clauses << project_statement).join(' AND ')
               end
               # Returns the issue count
               def issue_count
                 Issue.count(:include => [:status, :project], :conditions => statement)
               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
                     # Rails will raise an (unexpected) RecordNotFound if there's only a nil group value
                     r = Issue.count(:group => group_by_statement, :include => [:status, :project], :conditions => statement)
                   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]].reject {|s| s.blank?}.join(',')
                 order_option = nil if order_option.blank?
                 Issue.find :all, :include => ([:status, :project] + (options[:include] || [])).uniq,
                                  :conditions => Query.merge_conditions(statement, options[:conditions]),
                                  :order => order_option,
                                  :limit  => options[:limit],
                                  :offset => options[:offset]
               rescue ::ActiveRecord::StatementInvalid => e
                 raise StatementInvalid.new(e.message)
               end
               # Returns the journals
               # Valid options are :order, :offset, :limit
               def journals(options={})
                 Journal.find :all, :include => [:details, :user, {:issue => [:project, :author, :tracker, :status]}],
                                    :conditions => statement,
                                    :order => options[:order],
                                    :limit => options[:limit],
                                    :offset => options[:offset]
               rescue ::ActiveRecord::StatementInvalid => e
                 raise StatementInvalid.new(e.message)
               end
               # Returns the versions
               # Valid options are :conditions
               def versions(options={})
                 Version.find :all, :include => :project,
                                    :conditions => Query.merge_conditions(project_statement, options[:conditions])
               rescue ::ActiveRecord::StatementInvalid => e
                 raise StatementInvalid.new(e.message)
               end
               private
               # Helper method to generate the WHERE sql for a +field+, +operator+ and a +value+
               def sql_for_field(field, operator, value, db_table, db_field, is_custom_filter=false)
                 sql = ''
                 case operator
                 when "="
                   sql = "#{db_table}.#{db_field} IN (" + value.collect{|val| "'#{connection.quote_string(val)}'"}.join(",") + ")"
                 when "!"
                   sql = "(#{db_table}.#{db_field} IS NULL OR #{db_table}.#{db_field} NOT IN (" + value.collect{|val| "'#{connection.quote_string(val)}'"}.join(",") + "))"
                 when "!*"
                   sql = "#{db_table}.#{db_field} IS NULL"
                   sql << " OR #{db_table}.#{db_field} = ''" if is_custom_filter
                 when "*"
                   sql = "#{db_table}.#{db_field} IS NOT NULL"
                   sql << " AND #{db_table}.#{db_field} <> ''" if is_custom_filter
                 when ">="
                   sql = "#{db_table}.#{db_field} >= #{value.first.to_i}"
                 when "<="
                   sql = "#{db_table}.#{db_field} <= #{value.first.to_i}"
                 when "o"
                   sql = "#{IssueStatus.table_name}.is_closed=#{connection.quoted_false}" if field == "status_id"
                 when "c"
                   sql = "#{IssueStatus.table_name}.is_closed=#{connection.quoted_true}" if field == "status_id"
                 when ">t-"
                   sql = date_range_clause(db_table, db_field, - value.first.to_i, 0)
                 when "<t-"
                   sql = date_range_clause(db_table, db_field, nil, - value.first.to_i)
                 when "t-"
                   sql = date_range_clause(db_table, db_field, - value.first.to_i, - value.first.to_i)
                 when ">t+"
                   sql = date_range_clause(db_table, db_field, value.first.to_i, nil)
                 when "<t+"
                   sql = date_range_clause(db_table, db_field, 0, value.first.to_i)
                 when "t+"
                   sql = date_range_clause(db_table, db_field, value.first.to_i, value.first.to_i)
                 when "t"
                   sql = date_range_clause(db_table, db_field, 0, 0)
                 when "w"
                   from = l(:general_first_day_of_week) == '7' ?
                   # week starts on sunday
                   ((Date.today.cwday == 7) ? Time.now.at_beginning_of_day : Time.now.at_beginning_of_week - 1.day) :
                     # week starts on monday (Rails default)
                     Time.now.at_beginning_of_week
                   sql = "#{db_table}.#{db_field} BETWEEN '%s' AND '%s'" % [connection.quoted_date(from), connection.quoted_date(from + 7.days)]
                 when "~"
                   sql = "LOWER(#{db_table}.#{db_field}) LIKE '%#{connection.quote_string(value.first.to_s.downcase)}%'"
                 when "!~"
                   sql = "LOWER(#{db_table}.#{db_field}) NOT LIKE '%#{connection.quote_string(value.first.to_s.downcase)}%'"
                 end
                 return sql
               end
               def add_custom_fields_filters(custom_fields)
                 @available_filters ||= {}
                 custom_fields.select(&:is_filter?).each do |field|
                   case field.field_format
                   when "text"
                     options = { :type => :text, :order => 20 }
                   when "list"
                     options = { :type => :list_optional, :values => field.possible_values, :order => 20}
                   when "date"
                     options = { :type => :date, :order => 20 }
                   when "bool"
                     options = { :type => :list, :values => [[l(:general_text_yes), "1"], [l(:general_text_no), "0"]], :order => 20 }
                   else
                     options = { :type => :string, :order => 20 }
                   end
                   @available_filters["cf_#{field.id}"] = options.merge({ :name => field.name })
                 end
               end
               # Returns a SQL clause for a date or datetime field.
               def date_range_clause(table, field, from, to)
                 s = []
                 if from
                   s << ("#{table}.#{field} > '%s'" % [connection.quoted_date((Date.yesterday + from).to_time.end_of_day)])
                 end
                 if to
                   s << ("#{table}.#{field} <= '%s'" % [connection.quoted_date((Date.today + to).to_time.end_of_day)])
                 end
                 s.join(' AND ')
               end
             end